public static DataTable GetAgendamentoDiaArea(
decimal pNUC_ID,
string pMES,
string pANO,
decimal pARATUA_ID,
ConnectionInfo pInfo
)
{
string lQuery = "";
DataTable lTable = new DataTable();
//Atualização do dia 28/02/2012
//Pega apenas os primeiros atendimentos agendamento do dia, para verificar se ainda ha vagas
lQuery = AgendamentoAtendimentoQD.qAgendamentoDia;
lQuery += string.Format(" AND {0} = >>{0} AND {1} = >>{1} AND TO_CHAR(AGAT_DATAAGENDADA, 'MM') = '{2}' AND TO_CHAR(AGAT_DATAAGENDADA, 'yyyy') = '{3}' AND DOC_TIPO = 1", AgendamentoAtendimentoQD._NUC_ID.Name, AgendamentoAtendimentoQD._ARATUA_ID.Name, pMES, pANO);
lQuery += " GROUP BY AGAT_DATAAGENDADA";
SelectCommand lSelect = new SelectCommand(lQuery);
lSelect.Fields.Add(AgendamentoAtendimentoQD._NUC_ID.Name, pNUC_ID, (ItemType)AgendamentoAtendimentoQD._NUC_ID.DBType);
lSelect.Fields.Add(AgendamentoAtendimentoQD._ARATUA_ID.Name, pARATUA_ID, (ItemType)AgendamentoAtendimentoQD._ARATUA_ID.DBType);
lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));
return lTable;
}
public static DataTable GetCountProcessoTamitacaoPedente(
decimal pCategoria,
string pLoginName,
ConnectionInfo pInfo
)
{
string lQuery = "";
DataTable lTable = new DataTable();
if (pCategoria == (decimal)Categoria.Tramitação)
lQuery = DocumentoTramitacaoQD.qCountTramitacaoProcesso;
else if (pCategoria == (decimal)Categoria.TramitaçãoPetição)
lQuery = DocumentoTramitacaoQD.qCountTramitacaoPeticao;
else if (pCategoria == (decimal)Categoria.TramitaçãoProcessoExterno)
lQuery = DocumentoTramitacaoQD.qCountTramitacaoProcessoExterno;
else if (pCategoria == (decimal)Categoria.TramitaçãoOfício)
lQuery = DocumentoTramitacaoQD.qCountTramitacaoOficio;
lQuery += " AND DOC.DOC_REGUSER = '******' ";
lQuery += " AND DOCTRAM.nuc_dest_id is null ";
//SelectCommand lSelect = new SelectCommand(lQuery);
//lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));
OracleDo lOra = new OracleDo();
lTable = lOra.Consulta(lQuery, pInfo.ConnectionString);
return lTable;
}
public static DataTable GetAtendimentosRealizadosDia(
string pDOCMOV_REGUSER,
string pDOCMOV_REGDATE,
decimal pNUC_ID,
ConnectionInfo pInfo
)
{
string lQuery = "";
DataTable lTable = new DataTable();
lQuery = DocumentoMovimentoQD.qDocumentoMovimentoCount;
lQuery += string.Format(" WHERE DOCMOV.DOCMOV_STATUS NOT IN ('I','E') AND DOCMOV.CAT_ID IN ({0},{1}) AND DOCMOV.{2} = >>{2} AND TO_CHAR(DOCMOV.{3},'dd/MM/yyyy') = >>{3}", (decimal)Categoria.Atendido, (decimal)Categoria.AtendidoRetorno, DocumentoMovimentoQD._DOCMOV_REGUSER.Name,
DocumentoMovimentoQD._DOCMOV_REGDATE.Name);
lQuery += string.Format(" AND EXISTS (SELECT 0 FROM DOCUMENTO DOC WHERE DOC.DOC_ID = DOCMOV.DOC_ID AND DOC.{0} = >>{0})", DocumentoQD._NUC_ID.Name);
SelectCommand lSelect = new SelectCommand(lQuery);
lSelect.Fields.Add(DocumentoMovimentoQD._DOCMOV_REGUSER.Name, pDOCMOV_REGUSER, (ItemType)DocumentoMovimentoQD._DOCMOV_REGUSER.DBType);
lSelect.Fields.Add(DocumentoMovimentoQD._DOCMOV_REGDATE.Name, pDOCMOV_REGDATE, (ItemType)DocumentoMovimentoQD._DOCMOV_REGDATE.DBType);
lSelect.Fields.Add(DocumentoQD._NUC_ID.Name, pNUC_ID, (ItemType)DocumentoQD._NUC_ID.DBType);
lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));
return lTable;
}
public static DataTable GetDeterminacaoSecretariaByNUC_ID(
decimal pNUC_ID,
decimal pPESF_ID,
decimal pDTMSEC_SITUACAO,
ConnectionInfo pInfo
)
{
string lQuery = "";
DataTable lTable = new DataTable();
lQuery = DeterminacaoSecretariaQD.qDeterminacaoSecretariaList;
lQuery += string.Format(" WHERE DTMSEC_STATUS='A' AND {0} = >>{0}", DeterminacaoSecretariaQD._NUC_ID.Name);
if (pPESF_ID != 0)
lQuery += string.Format(" AND DTMSEC_PESF_PEDIDO = {0}", pPESF_ID);
if (pDTMSEC_SITUACAO != 0)
lQuery += string.Format(" AND DTMSEC_SITUACAO = {0}", pDTMSEC_SITUACAO);
lQuery += " ORDER BY DTMSEC_ID";
SelectCommand lSelect = new SelectCommand(lQuery);
lSelect.Fields.Add(DeterminacaoSecretariaQD._NUC_ID.Name, pNUC_ID, (ItemType)DeterminacaoSecretariaQD._NUC_ID.DBType);
lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));
return lTable;
}
public static LoginUserDo VerifyLogin(string pEUSR, string pEusr_Password, string pEusr_Type, ConnectionInfo pConnectionInfo)
{
bool lReturn;
string lQuery;
DataTable lTable;
lQuery = UsersQD.qUsersList;
lTable = new DataTable();
if (pEusr_Type == "1")//Portal Garagem
{
lQuery += String.Format(" WHERE {0} = >>{0} AND {1} = >>{1} ", UsersQD._USR_LOGIN.Name, UsersQD._USR_PASSWORD.Name);
lQuery += " AND USR_STATUS='A' ";
SelectCommand lSelect = new SelectCommand(lQuery);
pEusr_Password = APB.Framework.Encryption.Crypto.Encode(pEusr_Password);
lSelect.Fields.Add(UsersQD._USR_LOGIN.Name, pEUSR, (ItemType)UsersQD._USR_LOGIN.DBType);
lSelect.Fields.Add(UsersQD._USR_PASSWORD.Name, pEusr_Password, (ItemType)UsersQD._USR_PASSWORD.DBType);
lTable = lSelect.ReturnData(Instance.CreateDatabase(pConnectionInfo));
}
lReturn = (lTable.Rows.Count > 0) ? true : false;
// User Accept, Check TP_ID
if (lReturn)
{
return new LoginUserDo((decimal)lTable.Rows[0]["USR_ID"], (string)lTable.Rows[0]["USR_LOGIN"]);
}
return null;
}
public static DataTable GetAtendimentoByNucleoAreaDataInicioDataFim(
decimal pNUC_ID,
decimal pARATUA_ID,
string pDataInicio,
string pDataFim,
ConnectionInfo pInfo
)
{
string lQuery = "";
DataTable lTable = new DataTable();
lQuery = AtendimentoEstatisticoQD.qAllAtendimentoEstatistico;
if(pNUC_ID != 0)
lQuery += string.Format(" AND NUC.NUC_ID = {0} ", pNUC_ID);
if(pARATUA_ID != 0)
lQuery += string.Format(" AND ARATUA.ARATUA_ID = {0} ", pARATUA_ID);
if(pDataInicio != "")
lQuery += string.Format(" AND ATES.ATES_DATA >= '{0}' ", pDataInicio);
if (pDataFim != "")
lQuery += string.Format(" AND ATES.ATES_DATA <= '{0}' ", pDataFim);
lQuery += " ORDER BY ATES.ATES_DATA";
OracleDo lOra = new OracleDo();
lTable = lOra.Consulta(lQuery, pInfo.ConnectionString);
return lTable;
}
public static DataTable GetAllModulosUsuario(ConnectionInfo pInfo, string id_usuario, string id_sistema)
{
string lQuery = "";
DataTable lTable = new DataTable();
lQuery = "select distinct t5.* from ";
lQuery += " SEG_USUARIOXGRUPO t1";
lQuery += " join SEG_GRUPO t2";
lQuery += " on (t1.GRP_ID = t2.GRP_ID";
lQuery += " and t1.SUSR_ID = "+id_usuario;
lQuery += " and t1.susr_regstatus='A')";
lQuery += " join SEG_GRUPOXPROGRAMA t3 ";
lQuery += " on (t2.GRP_ID = t3.GRP_ID";
lQuery += " and t3.grp_regstatus = 'A')";
lQuery += " join SEG_PROGRAMA t4";
lQuery += " on (t3.PRO_ID = t4.PRO_ID";
lQuery += " and t4.pro_regstatus = 'A')";
lQuery += " join SEG_MODULO t5";
lQuery += " on (t4.MOD_ID = t5.MOD_ID";
lQuery += " and t5.mod_regstatus = 'A' and t5.sis_id=" + id_sistema + ")";
SelectCommand lSelect = new SelectCommand(lQuery);
lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));
return lTable;
}
public static DataTable GetAllEscalaMesAtuacao(
ConnectionInfo pInfo,
String pESC_DIA,
decimal pATUA_ID
)
{
string lQuery = "";
DataTable lTable = new DataTable();
lQuery = EscalaQD.qEscalaAtividadeList;
lQuery += " WHERE ESC_STATUS NOT IN ('I','E') ";
lQuery += " AND A.ATDEF_ID = B.ATDEF_ID ";
lQuery += string.Format(" AND A.{0} = >>{0} ", EscalaQD._ATUA_ID.Name);
lQuery += string.Format(" AND to_char(A.{0}, 'mm') = to_char(>>{0}, 'mm')", EscalaQD._ESC_DIA.Name);
lQuery += string.Format(" AND to_char(A.{0}, 'yyyy') = to_char(>>{0}, 'yyyy')", EscalaQD._ESC_DIA.Name);
lQuery += "Order By ESC_DIA ";
SelectCommand lSelect = new SelectCommand(lQuery);
lSelect.Fields.Add(EscalaQD._ATUA_ID.Name, pATUA_ID, (ItemType)EscalaQD._ATUA_ID.DBType);
lSelect.Fields.Add(EscalaQD._ESC_DIA.Name, pESC_DIA, (ItemType)EscalaQD._ESC_DIA.DBType);
lSelect.Fields.Add(EscalaQD._ESC_DIA.Name, pESC_DIA, (ItemType)EscalaQD._ESC_DIA.DBType);
lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));
return lTable;
}
public static DataTable GetRelatorioQuantitativoNpj(ConnectionInfo pInfo, string pQuery)
{
DataTable lTable = new DataTable();
SelectCommand lSelect = new SelectCommand(pQuery);
lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));
return lTable;
}
public static void LoadConnectionInfo()
{
_ActiveConnection = ConfigurationManager.ConnectionStrings[ConfigurationManager.AppSettings["ActiveConnectionString"]];
_ConnectionInfo = new ConnectionInfo(
_ActiveConnection.ConnectionString,
(_ActiveConnection.ProviderName.ToLower() == "oracle") ? 0 : 1,
(ConfigurationManager.AppSettings["ApplicationServerMode"].ToLower() == "web") ? 0 : 1
);
}
public static DataTable GetAllNPJ_ALUNOXATENDIMENTO(ConnectionInfo pInfo)
{
string lQuery = "";
DataTable lTable = new DataTable();
lQuery = NPJ_ALUNOXATENDIMENTOQD.qNPJ_ALUNOXATENDIMENTOList;
lQuery += " WHERE STATUS='A'";
SelectCommand lSelect = new SelectCommand(lQuery);
lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));
return lTable;
}
public static DataTable GetAllSEG_MODULO(ConnectionInfo pInfo)
{
string lQuery = "";
DataTable lTable = new DataTable();
lQuery = SEG_MODULOQD.qSEG_MODULOList;
lQuery += " WHERE STATUS='A'";
SelectCommand lSelect = new SelectCommand(lQuery);
lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));
return lTable;
}
public static OperationResult Insert(DataFieldCollection pValues, ConnectionInfo pInfo)
{
Transaction lTransaction;
lTransaction = new Transaction(Instance.CreateDatabase(pInfo));
bool lLocalTransaction = (lTransaction != null);
InsertCommand lInsert;
OperationResult lReturn = new OperationResult(NPJ_ALUNOXATENDIMENTOQD.TableName, NPJ_ALUNOXATENDIMENTOQD.TableName);
if (!lReturn.HasError){
try{
if (lLocalTransaction){
lReturn.Trace("Transação local, instanciando banco...");
}
lInsert = new InsertCommand(NPJ_ALUNOXATENDIMENTOQD.TableName);
lReturn.Trace("Adicionando campos ao objeto de insert");
foreach (DataField lField in pValues.Keys){
lInsert.Fields.Add(lField.Name, pValues[lField], (ItemType)lField.DBType);
}
decimal lSequence;
lReturn.Trace("Executando o Insert");
lInsert.Execute(lTransaction);
if (!lReturn.HasError){
if (lLocalTransaction){
if (!lReturn.HasError){
lReturn.Trace("Insert finalizado, executando commit");
lTransaction.Commit();
}
else{
lTransaction.Rollback();
}
}
}
else{
if (lLocalTransaction)
lTransaction.Rollback();
}
}
catch (Exception ex){
lReturn.OperationException = new SerializableException(ex);
if (lLocalTransaction)
lTransaction.Rollback();
}
}
return lReturn;
}
public static DataTable GetAllNucleoxLocalidade(
ConnectionInfo pInfo
)
{
string lQuery = "";
DataTable lTable = new DataTable();
lQuery = NucleoxLocalidadeQD.qNucleoxLocalidadeList;
SelectCommand lSelect = new SelectCommand(lQuery);
lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));
return lTable;
}
public static DataTable GetAllModeloPeticao(
ConnectionInfo pInfo
)
{
string lQuery = "";
DataTable lTable = new DataTable();
lQuery = ModeloPeticaoQD.qModeloPeticaoTipo;
SelectCommand lSelect = new SelectCommand(lQuery);
lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));
return lTable;
}
public static DataTable GetAllTable(ConnectionInfo pInfo)
{
string lQuery = "";
DataTable lTable;
lQuery = InformationSchemaQd.qAllTables;
lQuery += " ORDER BY TABLE_NAME";
SelectCommand lSelect = new SelectCommand(lQuery);
lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));
return lTable;
}
public static DataTable GetAllColumn(string pTABLE_NAME, ConnectionInfo pInfo)
{
string lQuery = "";
DataTable lTable;
lQuery = string.Format(InformationSchemaQd.qAllColumn, pTABLE_NAME);
lQuery += " ORDER BY COLUMN_NAME";
SelectCommand lSelect = new SelectCommand(lQuery);
lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));
return lTable;
}
public static DataTable GetAllPerfil(
ConnectionInfo pInfo
)
{
string lQuery = "";
DataTable lTable = new DataTable();
lQuery = PessoaFuncaoQD.qPerfil;
SelectCommand lSelect = new SelectCommand(lQuery);
lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));
return lTable;
}
public static DataTable GetAllAssunto(
ConnectionInfo pInfo
)
{
string lQuery = "";
DataTable lTable = new DataTable();
lQuery = AssuntoQD.qAssuntoAll;
SelectCommand lSelect = new SelectCommand(lQuery);
lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));
return lTable;
}
public static DataTable GetAllTipoEndereco(
ConnectionInfo pInfo
)
{
string lQuery = "";
DataTable lTable = new DataTable();
lQuery = EnderecoQD.qTipoEndereco;
SelectCommand lSelect = new SelectCommand(lQuery);
lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));
return lTable;
}
public static DataTable GetAllTipoInteressado(
ConnectionInfo pInfo
)
{
string lQuery = "";
DataTable lTable = new DataTable();
lQuery = InteressadoQD.qTipoInteressado;
lQuery += " WHERE TINT_STATUS='A' ORDER BY TINT_DESCRICAO";
SelectCommand lSelect = new SelectCommand(lQuery);
lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));
return lTable;
}
public static DataTable GetAllPessoa(
ConnectionInfo pInfo
)
{
string lQuery = "";
DataTable lTable = new DataTable();
lQuery = InteressadoQD.qPessoa;
lQuery += " WHERE PES_STATUS NOT IN ('I','E') ";
SelectCommand lSelect = new SelectCommand(lQuery);
lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));
return lTable;
}
public static DataTable GetAllTipoOficio(
ConnectionInfo pInfo
)
{
string lQuery = "";
DataTable lTable = new DataTable();
lQuery = ModeloOficioQD.qTipoOficio;
lQuery += " WHERE TPO_STATUS='A'";
SelectCommand lSelect = new SelectCommand(lQuery);
lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));
return lTable;
}
public static DataTable GetAllParametrosModeloPeticao(
ConnectionInfo pInfo
)
{
string lQuery = "";
DataTable lTable = new DataTable();
lQuery = ParametrosModeloPeticaoQD.qParametrosModeloPeticaoList;
lQuery += " ORDER BY PRMPT_DESCRICAO";
SelectCommand lSelect = new SelectCommand(lQuery);
lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));
return lTable;
}
public static DataTable GetAllVara(
ConnectionInfo pInfo
)
{
string lQuery = "";
DataTable lTable = new DataTable();
lQuery = VaraQD.qVaraList;
lQuery += " WHERE VAR_STATUS='A' ORDER BY VAR_DESC";
SelectCommand lSelect = new SelectCommand(lQuery);
lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));
return lTable;
}
public static DataTable GetAtendimentoListByDOC_ID(decimal pDOC_ID, ConnectionInfo pInfo)
{
string lQuery = "";
DataTable lTable = new DataTable();
lQuery = NPJ_ALUNOXATENDIMENTOQD.qTramitacaoAtendimentoAluno;
lQuery += string.Format(" AND DOC.DOC_ID = {0} ORDER BY NAA.NAA_REGDATE DESC", pDOC_ID);
//OracleDo lOra = new OracleDo();
//lTable = lOra.Consulta(lQuery, pInfo.ConnectionString);
SelectCommand lSelect = new SelectCommand(lQuery);
lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));
return lTable;
}
public static DataTable GetAllFuncao(
ConnectionInfo pInfo
)
{
string lQuery = "";
DataTable lTable = new DataTable();
lQuery = PessoaFuncaoQD.qFuncao;
lQuery += " WHERE FUNC_STATUS='A' ORDER BY FUNC_DESC";
SelectCommand lSelect = new SelectCommand(lQuery);
lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));
return lTable;
}
public static DataTable GetAllEndereco(
ConnectionInfo pInfo
)
{
string lQuery = "";
DataTable lTable = new DataTable();
lQuery = EnderecoQD.qEnderecoList;
lQuery += " WHERE END_STATUS='A'";
SelectCommand lSelect = new SelectCommand(lQuery);
lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));
return lTable;
}
public static DataTable GetAllDelegacia(
ConnectionInfo pInfo
)
{
string lQuery = "";
DataTable lTable = new DataTable();
lQuery = DelegaciaQD.qDelegaciaList;
lQuery += " WHERE DLG_STATUS='A' ORDER BY DLG_DESCRICAO";
SelectCommand lSelect = new SelectCommand(lQuery);
lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));
return lTable;
}
public static DataTable GetAllMedidasCriminal(
ConnectionInfo pInfo
)
{
string lQuery = "";
DataTable lTable = new DataTable();
lQuery = MedidasCriminalQD.qMedidasCriminalList;
lQuery += " WHERE MDCRM_STATUS='A'";
SelectCommand lSelect = new SelectCommand(lQuery);
lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));
return lTable;
}