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 GetItensRelatorioMensalByTipo(
decimal pTRM_ID,
ConnectionInfo pInfo
)
{
string lQuery = "";
DataTable lTable = new DataTable();
lQuery = string.Format(ItensRelatorioMensalQD.qItensRelatorioMensalConnectBy, pTRM_ID);
OracleDo lOra = new OracleDo();
lTable = lOra.Consulta(lQuery, pInfo.ConnectionString);
return lTable;
}
public static DataTable GetNucleoByBairroCidadePessoa129(
decimal pPES_ID,
ConnectionInfo pInfo
)
{
string lQuery = "";
DataTable lTable = new DataTable();
lQuery = string.Format(NucleoQD.qNucleoLocalidade129, pPES_ID);
OracleDo lOra = new OracleDo();
lTable = lOra.Consulta(lQuery, pInfo.ConnectionString);
return lTable;
}
public static DataTable GetSituacaoFamiliarByPessoa(
decimal pPES_ID,
ConnectionInfo pInfo
)
{
string lQuery = "";
DataTable lTable = new DataTable();
lQuery = SituacaoFamiliarQD.qSituacaoFamiliarList;
lQuery += string.Format(" WHERE STFAM_STATUS='A' AND PES_ID = {0}", pPES_ID);
OracleDo lOra = new OracleDo();
lTable = lOra.Consulta(lQuery, pInfo.ConnectionString);
return lTable;
}
public static DataTable GetUnidadeVaraInfracionalByID(
decimal pUVI_ID,
ConnectionInfo pInfo
)
{
string lQuery = "";
DataTable lTable = new DataTable();
lQuery = UnidadeVaraInfracionalQD.qUnidadeVaraInfracionalList;
lQuery += string.Format(" WHERE UVI_STATUS='A' and UVI_ID = {0}", pUVI_ID);
OracleDo lOra = new OracleDo();
lTable = lOra.Consulta(lQuery, pInfo.ConnectionString);
return lTable;
}
public static DataTable GetHistoricoViolenciaByPessoa(
decimal pPES_ID,
ConnectionInfo pInfo
)
{
string lQuery = "";
DataTable lTable = new DataTable();
lQuery = HistoricoViolenciaQD.qHistoricoViolenciaList;
lQuery += string.Format(" WHERE HISTVIO_STATUS='A' AND PES_ID = {0}", pPES_ID);
OracleDo lOra = new OracleDo();
lTable = lOra.Consulta(lQuery, pInfo.ConnectionString);
return lTable;
}
public static DataTable GetVisitasSocioEducativasByDOC_ID(
decimal pDOC_ID,
ConnectionInfo pInfo
)
{
string lQuery = "";
DataTable lTable = new DataTable();
lQuery = VisitasSocioEducativasQD.qVisitasSocioEducativasList;
lQuery += string.Format(" AND VTSCE.VTSCE_STATUS='A' AND VTSCE.DOC_ID = {0}", pDOC_ID);
OracleDo lOra = new OracleDo();
lTable = lOra.Consulta(lQuery, pInfo.ConnectionString);
return lTable;
}
public static DataTable GetItensByTipoSeq(
decimal pTRM_ID,
decimal pIRM_ID,
decimal pTRMIRM_SEQ,
ConnectionInfo pInfo
)
{
string lQuery = "";
DataTable lTable = new DataTable();
lQuery = TrmxIrmQD.qListTrmxIrm;
lQuery += " WHERE TRMIRM_STATUS='A'";
lQuery += string.Format(" AND TRM_ID = {0} AND IRM_ID != {1} AND TRMIRM_SEQ = {2}", pTRM_ID, pIRM_ID, pTRMIRM_SEQ);
OracleDo lOra = new OracleDo();
lTable = lOra.Consulta(lQuery,pInfo.ConnectionString);
return lTable;
}
public static DataTable GetDocumentoAnexoByDOC_ID(
decimal pDOC_ID,
ConnectionInfo pInfo
)
{
string lQuery = "";
DataTable lTable = new DataTable();
lQuery = string.Format(DocumentoAnexoQD.qDocumentoAnexosAndDocumentosAnexadosToMe, pDOC_ID);
//SelectCommand lSelect = new SelectCommand(lQuery);
//lSelect.Fields.Add(DocumentoAnexoQD._DOC_ID.Name, pDOC_ID, (ItemType)DocumentoAnexoQD._DOC_ID.DBType);
//lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));
OracleDo lOra = new OracleDo();
lTable = lOra.Consulta(lQuery, pInfo.ConnectionString);
return lTable;
}
public static DataTable GetArquivoAnexoByPTC_ID(
decimal pPTC_ID,
ConnectionInfo pInfo
)
{
string lQuery = "";
DataTable lTable = new DataTable();
lQuery = ArquivoAnexoQD.qArquivoAnexoList;
lQuery += string.Format(" WHERE ARQANX_STATUS NOT IN ('I','E') AND PTC_ID = {0}", pPTC_ID);
//SelectCommand lSelect = new SelectCommand(lQuery);
//lSelect.Fields.Add(ArquivoAnexoQD._PTC_ID.Name, pPTC_ID, (ItemType)ArquivoAnexoQD._PTC_ID.DBType);
//lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));
OracleDo lOra = new OracleDo();
lTable = lOra.Consulta(lQuery, pInfo.ConnectionString);
return lTable;
}
public static DataTable GetPeticaoListByDOC_ID(
decimal pDOC_ID,
ConnectionInfo pInfo
)
{
string lQuery = "";
DataTable lTable = new DataTable();
lQuery = PeticaoQD.qAllPeticao;
lQuery += string.Format(" AND PTC.DOC_ID = {0} ORDER BY PTC.PTC_ID ", pDOC_ID);
//SelectCommand lSelect = new SelectCommand(lQuery);
//lSelect.Fields.Add(PeticaoQD._DOC_ID.Name, pDOC_ID, (ItemType)PeticaoQD._DOC_ID.DBType);
//lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));
OracleDo lOra = new OracleDo();
lTable = lOra.Consulta(lQuery, pInfo.ConnectionString);
return lTable;
}
public static DataTable GetProcessoExternoByCondicao(
string lCondicao,
ConnectionInfo pInfo
)
{
string lQuery = "";
DataTable lTable = new DataTable();
lQuery = DocumentoAnexoQD.qDocumentoAnexoProcessoExterno;
lQuery += lCondicao;
//04/12/2012 - Ricardo Almeida
//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 GetSentencaByDOC_ID(
decimal pDOC_ID,
ConnectionInfo pInfo
)
{
string lQuery = "";
DataTable lTable = new DataTable();
lQuery = SentencaQD.qSentencaList;
lQuery += string.Format(" WHERE STC_STATUS NOT IN ('I','E') AND DOC_ID = {0}", pDOC_ID);
lQuery += " ORDER BY STC_DATASENTENCA";
//SelectCommand lSelect = new SelectCommand(lQuery);
//lSelect.Fields.Add(SentencaQD._DOC_ID.Name, pDOC_ID, (ItemType)SentencaQD._DOC_ID.DBType);
//lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));
OracleDo lOra = new OracleDo();
lTable = lOra.Consulta(lQuery, pInfo.ConnectionString);
return lTable;
}
public static DataTable GetDocumentoxClasseByDOC_ID(
decimal pDOC_ID,
ConnectionInfo pInfo
)
{
string lQuery = "";
DataTable lTable = new DataTable();
lQuery = DocumentoxClasseQD.qDocumentoxClasseList;
lQuery += string.Format(" WHERE DOCCLS_STATUS NOT IN ('I','E') AND DOC_ID = {0}", pDOC_ID);
lQuery += " ORDER BY DOCCLS_ID";
//SelectCommand lSelect = new SelectCommand(lQuery);
//lSelect.Fields.Add(DocumentoxClasseQD._DOC_ID.Name, pDOC_ID, (ItemType)DocumentoxClasseQD._DOC_ID.DBType);
//lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));
OracleDo lOra = new OracleDo();
lTable = lOra.Consulta(lQuery, pInfo.ConnectionString);
return lTable;
}
public static DataTable GetAssuntoProcessualByDOC_ID(
decimal pDOC_ID,
ConnectionInfo pInfo
)
{
string lQuery = "";
DataTable lTable = new DataTable();
lQuery = AssuntoProcessualQD.qAssuntoProcessualList;
lQuery += string.Format(" WHERE ASSPRC_STATUS NOT IN ('I','E') AND DOC_ID = {0}", pDOC_ID);
lQuery += " ORDER BY ASSPRC_ID";
//SelectCommand lSelect = new SelectCommand(lQuery);
//lSelect.Fields.Add(AssuntoProcessualQD._DOC_ID.Name, pDOC_ID, (ItemType)AssuntoProcessualQD._DOC_ID.DBType);
//lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));
OracleDo lOra = new OracleDo();
lTable = lOra.Consulta(lQuery, pInfo.ConnectionString);
return lTable;
}
public static DataTable GetDeterminacaoSecretariaByDOC_ID(
decimal pDOC_ID,
ConnectionInfo pInfo
)
{
string lQuery = "";
DataTable lTable = new DataTable();
lQuery = DeterminacaoSecretariaQD.qDeterminacaoSecretaria;
lQuery += string.Format(" WHERE DTMSEC_STATUS='A' AND DOC_ID = {0}", pDOC_ID);
lQuery += " ORDER BY DTMSEC_ID";
//SelectCommand lSelect = new SelectCommand(lQuery);
//lSelect.Fields.Add(DeterminacaoSecretariaQD._DOC_ID.Name, pDOC_ID, (ItemType)DeterminacaoSecretariaQD._DOC_ID.DBType);
//lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));
OracleDo lOra = new OracleDo();
lTable = lOra.Consulta(lQuery, pInfo.ConnectionString);
return lTable;
}
public static DataTable GetRegistroMensalAtividadeByDefensorCompetencia(
decimal pPESF_ID,
string pRMA_COMPETENCIA,
ConnectionInfo pInfo
)
{
string lQuery = "";
DataTable lTable = new DataTable();
lQuery = RegistroMensalAtividadeQD.qRegistroMensalAtividadeList;
lQuery += string.Format(" AND PESF.PESF_ID = {0} ", pPESF_ID);
if(pRMA_COMPETENCIA != "")
lQuery += string.Format(" AND RMA.RMA_COMPETENCIA = '{0}' ", pRMA_COMPETENCIA);
lQuery += "ORDER BY RMA.RMA_COMPETENCIA, RMA.RMA_ID";
OracleDo lOra = new OracleDo();
lTable = lOra.Consulta(lQuery, pInfo.ConnectionString);
return lTable;
}
//21/11/2012 - Ricardo Almeida
//pesquisa na AgendamentoAtendimento diferente do status F
public static DataTable GetAgendamentoByCondicaoeFaltou(
string pCondicao,
ConnectionInfo pInfo
)
{
string lQuery = "";
DataTable lTable = new DataTable();
lQuery = AgendamentoAtendimentoQD.qAllAgendamentoAtendimentoFaltou;
lQuery += " AND AGAT.DOC_TIPO = 1"; //Agendamento de 1ª vez
lQuery += pCondicao;
lQuery += " ORDER BY AGAT.AGAT_HORA, AGAT.AGAT_ID";
//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 GetAudienciaByDOC_ID(
decimal pDOC_ID,
ConnectionInfo pInfo
)
{
string lQuery = "";
DataTable lTable = new DataTable();
lQuery = AudienciaQD.qAudienciaList;
lQuery += " WHERE AUD_STATUS='A'";
lQuery += string.Format(" AND DOC_ID = {0}", pDOC_ID);
lQuery += " ORDER BY AUD_ID";
//SelectCommand lSelect = new SelectCommand(lQuery);
//lSelect.Fields.Add(AudienciaQD._DOC_ID.Name, pDOC_ID, (ItemType)AudienciaQD._DOC_ID.DBType);
//lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));
OracleDo lOra = new OracleDo();
lTable = lOra.Consulta(lQuery, pInfo.ConnectionString);
return lTable;
}
public static DataTable GetVaraByDOC_ID(
decimal pDOC_ID,
ConnectionInfo pInfo
)
{
Select.AlterSessionBinary(pInfo);
string lQuery = "";
DataTable lTable = new DataTable();
lQuery = VaraQD.qVaraDocumento;
lQuery += string.Format(" AND DOC.DOC_ID = {0}", pDOC_ID);
//SelectCommand lSelect = new SelectCommand(lQuery);
//lSelect.Fields.Add(DocumentoQD._DOC_ID.Name, pDOC_ID, (ItemType)DocumentoQD._DOC_ID.DBType);
//lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));
OracleDo lOra = new OracleDo();
lTable = lOra.Consulta(lQuery, pInfo.ConnectionString);
return lTable;
}
public static DataTable GetAgendamentoByDOC_ID(
decimal pDOC_ID,
ConnectionInfo pInfo
)
{
string lQuery = "";
DataTable lTable = new DataTable();
lQuery = AgendamentoAtendimentoQD.qAgendamentoAtendimentoRetorno;
lQuery += string.Format(" AND DOCMOV.DOC_ID = {0}", pDOC_ID);
lQuery += " ORDER BY AGAT.AGAT_HORA, AGAT.AGAT_ID";
//SelectCommand lSelect = new SelectCommand(lQuery);
//lSelect.Fields.Add(DocumentoMovimentoQD._DOC_ID.Name, pDOC_ID, (ItemType)DocumentoMovimentoQD._DOC_ID.DBType);
//lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));
OracleDo lOra = new OracleDo();
lTable = lOra.Consulta(lQuery, pInfo.ConnectionString);
return lTable;
}
public static DataTable GetAgendamentoByCondicao(
string pCondicao,
ConnectionInfo pInfo
)
{
string lQuery = "";
DataTable lTable = new DataTable();
lQuery = AgendamentoAtendimentoQD.qAllAgendamentoAtendimento;
lQuery += pCondicao;
lQuery += " ORDER BY AGAT.AGAT_HORA, AGAT.AGAT_ID";
//SelectCommand lSelect = new SelectCommand(lQuery);
//lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));
//13/11/2012 - Ricardo Almeida
OracleDo lOra = new OracleDo();
lTable = lOra.Consulta(lQuery, pInfo.ConnectionString);
return lTable;
}
//public static DataTable GetDocumentoByID
//(
// decimal pDOC_ID,
// ConnectionInfo pInfo
//)
//{
// string lQuery = "";
// DataTable lTable = new DataTable();
// lQuery = DocumentoQD.qDocumentoAssuntoSituacao;
// lQuery += string.Format(" AND DOC.{0}= >>{0}", DocumentoQD._DOC_ID.Name);
// SelectCommand lSelect = new SelectCommand(lQuery);
// lSelect.Fields.Add(DocumentoQD._DOC_ID.Name, pDOC_ID, (ItemType)DocumentoQD._DOC_ID.DBType);
// lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));
// return lTable;
//}
public static DataTable GetProcessoByID(
decimal pDOC_ID,
ConnectionInfo pInfo
)
{
string lQuery = "";
DataTable lTable = new DataTable();
lQuery = DocumentoQD.qDocumentoSituacaoInteressadoCategoria;
lQuery += string.Format(" AND DOC.DOC_ID= {0} AND DOC.TDOC_ID = 2 ", pDOC_ID);
//06/11/2012 - Ricardo Almeida - Substituição do método de consulta ao oracle
//SelectCommand lSelect = new SelectCommand(lQuery);
//lSelect.Fields.Add(DocumentoQD._DOC_ID.Name, pDOC_ID, (ItemType)DocumentoQD._DOC_ID.DBType);
//lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));
OracleDo lOra = new OracleDo();
lTable = lOra.Consulta(lQuery, pInfo.ConnectionString);
return lTable;
}
//10/01/2013 - Ricardo Almeida - Inclusão do método para ser utilizado na página ConsultaAtendimento.aspx
public static decimal GetQtdAtendimentosPendentesDefensorByNucleoAreaData(
decimal pNUC_ID,
decimal pARATUA_ID,
string pAGDF_DATAAGENDADA,
decimal pPESF_ID,
ConnectionInfo pInfo
)
{
string lQuery = "";
decimal lReturn = 0;
DataTable lTable = new DataTable();
lQuery = DocumentoQD.qGetQtdAtendimentosPendentesDefensor;
lQuery += string.Format(" AND DOC.NUC_ID = {0}", pNUC_ID);
if (pARATUA_ID != 0)
lQuery += string.Format(" AND DOC.ARATUA_ID = {0}", pARATUA_ID);
lQuery += string.Format(" AND TO_CHAR(AGDF.AGDF_DATAAGENDADA,'dd/MM/yyyy') = '{0}'", pAGDF_DATAAGENDADA);
lQuery += string.Format(" AND AGDF.PESF_ID = {0}", pPESF_ID);
OracleDo lOra = new OracleDo();
lTable = lOra.Consulta(lQuery, pInfo.ConnectionString);
if (lTable.Rows.Count > 0)
lReturn = lTable.Rows[0]["QTDPENDENTE"].DBToDecimal();
return lReturn;
}
public static DataTable GetPessoaAtendimentoByCondicao(
string pCondicao,
ConnectionInfo pInfo
)
{
Select.AlterSessionBinary(pInfo);
string lQuery = "";
DataTable lTable = new DataTable();
lQuery = PessoaQD.qPessoaAtendimentoList;
lQuery += pCondicao;
lQuery += " ORDER BY PES.PES_NOME";
//08/11/2012 - Ricardo Almeida
//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 GetParentePessoaByID(
decimal pPES_ID,
ConnectionInfo pInfo
)
{
string lQuery = "";
DataTable lTable = new DataTable();
lQuery = string.Format(PessoaQD.qPessoaParente, pPES_ID);
OracleDo lOra = new OracleDo();
lTable = lOra.Consulta(lQuery, pInfo.ConnectionString);
return lTable;
}
public static DataTable GetItensRegistroMensalAtividadeByRMA_Id(
decimal pRMA_ID,
ConnectionInfo pInfo
)
{
string lQuery = "";
DataTable lTable = new DataTable();
lQuery = string.Format(ItensRegistroMensalAtividadeQD.qItensRegistroMensalAtividadeConnectBy, pRMA_ID);
OracleDo lOra = new OracleDo();
lTable = lOra.Consulta(lQuery, pInfo.ConnectionString);
return lTable;
}
public static DataTable GetPessoaFuncaoServidorByCondicao(
string lCondicao,
ConnectionInfo pInfo
)
{
string lQuery = "";
DataTable lTable = new DataTable();
lQuery = PessoaFuncaoQD.qPessoaFuncaoServidores;
lQuery += lCondicao;
lQuery += " ORDER BY PES.PES_NOME";
//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 GetEscalaByNucleoDia(
decimal pNUC_ID,
string pESC_DIA,
decimal pARATUA_ID,
ConnectionInfo pInfo
)
{
string lQuery = "";
DataTable lTable = new DataTable();
lQuery = EscalaQD.qEscalaAll;
lQuery += string.Format(" AND ESC.ESC_DIA = TO_DATE('{0}','DD/MM/YYYY')", pESC_DIA);
lQuery += string.Format(" AND NUCARATUA.NUC_ID = {0}", pNUC_ID);
lQuery += string.Format(" AND NUCARATUA.ARATUA_ID = {0}", pARATUA_ID);
lQuery += " ORDER BY PES.PES_NOME";
//SelectCommand lSelect = new SelectCommand(lQuery);
//lSelect.Fields.Add(EscalaQD._ESC_DIA.Name, pESC_DIA, (ItemType)EscalaQD._ESC_DIA.DBType);
//lSelect.Fields.Add(NucleoxAreaAtuacaoQD._NUC_ID.Name, pNUC_ID, (ItemType)NucleoxAreaAtuacaoQD._NUC_ID.DBType);
//lSelect.Fields.Add(NucleoxAreaAtuacaoQD._ARATUA_ID.Name, pARATUA_ID, (ItemType)NucleoxAreaAtuacaoQD._ARATUA_ID.DBType);
//lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));
OracleDo lOra = new OracleDo();
lTable = lOra.Consulta(lQuery, pInfo.ConnectionString);
return lTable;
}
public static DataTable GetSequenciaEscalaAtendimentoByNucleoAreaData(
decimal pNUC_ID,
decimal pATUA_ID,
string pData,
ConnectionInfo pInfo
)
{
string lQuery = "";
DataTable lTable = new DataTable();
lQuery = EscalaQD.qEscalaDefensor;
lQuery += string.Format(" AND NUCARATUA.NUC_ID = {0} AND ESC.ESC_DIA = '{1}'", pNUC_ID, pData);
lQuery += string.Format(@" AND NUCARATUA.ARATUA_ID = (SELECT NA.ARATUA_ID FROM NUCLEOXAREAATUACAO NA, ATUACAO ATU
WHERE NA.NUCARATUA_ID = ATU.NUCARATUA_ID
AND NA.NUCARATUA_STATUS = 'A'
AND ATU.ATUA_ID = {0})", pATUA_ID);
lQuery += " ORDER BY ESC.ESC_SEQUENCIA";
OracleDo lOra = new OracleDo();
lTable = lOra.Consulta(lQuery, pInfo.ConnectionString);
return lTable;
}
