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 GetAllBibliotecapendenciasdevolucao3(
              ConnectionInfo pInfo,
             decimal ept_pesf_id,
             string PES_NOME,
             string item
          )
        {
            string lQuery = "";
            DataTable lTable = new DataTable();

            lQuery += Biblioteca_EmprestimoQD.GetAllBibliotecapendenciasdevolucao3;
            lQuery += @"  where e.ept_id=b.ept_id
                          and e.ept_pesf_id=" + ept_pesf_id + @"
                          and e.ept_status='A'
                          AND B.IEPT_STATUS='A'
                          and ti.ttab_id=b.ttab_id
                          and l.lvr_id = v.lvr_id
                          and v.il_id = b.cod_item
                          and  UPPER(p.pes_nome) LIKE UPPER('%{0}%')
                          and ti.ttab_id='{1}' ";

            lQuery = string.Format(lQuery, PES_NOME, item);

            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(PORT_BANNERQD.TableName, PORT_BANNERQD.TableName);

             if (!lReturn.HasError){
             try{
             if (lLocalTransaction){
                 lReturn.Trace("Transação local, instanciando banco...");
             }

             lInsert = new InsertCommand(PORT_BANNERQD.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;
             lSequence = DataBaseSequenceControl.GetNext(pInfo, "BAN_ID");
             lInsert.Fields.Add(PORT_BANNERQD._BAN_ID.Name, lSequence, (ItemType)PORT_BANNERQD._BAN_ID.DBType);

             lReturn.Trace("Executando o Insert");

             lInsert.Execute(lTransaction, false);

             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 int Delete(
          ConnectionInfo pInfo,
          string pTRA_ID
       )
        {
            string lQuery = "";
            int lResult;

            DataTable lTable = new DataTable();

            lQuery = " DELETE FROM " + TRAMITACAOQD.TableName;
            lQuery += " WHERE " + TRAMITACAOQD._TRA_ID.Name + " = " + pTRA_ID;

            SelectCommand lSelect = new SelectCommand(lQuery);
            try
            {
                lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));
                lResult = 1;
            }
            catch (Exception Err)
            {
                lResult = 0;
            }

            return lResult;
        }

        public static DataTable getCargoSetorNucleo(
            ConnectionInfo pInfo,
            string pPesf_ID
            )
        {
            string lQuery = "";
            DataTable lTable = new DataTable();

            lQuery = SETORQD.qSetorNucleoCargo;
            lQuery += @" WHERE PF.PES_ID = P.PES_ID
                         AND PF.FUNC_ID = F.FUNC_ID
                         AND C.PES_ID = P.PES_ID
                         AND C.AGE_ID = A.AGE_ID
                         AND A.BAN_ID = B.BAN_ID
                         AND P.PES_CPF IS NOT NULL
                         AND PF.SET_ID=S.SET_ID
                         AND S.NUC_ID = N.NUC_ID
                         AND PF.PESF_STATUS = 'A'
                         AND P.PES_STATUS = 'A'
                         AND C.CONT_STATUS = 'A'
                         AND A.AGE_STATUS = 'A'
                         AND B.BAN_STATUS = 'A'
                         AND PF.PESF_ID = " + pPesf_ID;

            SelectCommand lSelect = new SelectCommand(lQuery);
            lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));

            return lTable;
        }

        public static DataTable GetAllMEIADIARIABUSCAID(
           ConnectionInfo pInfo, string pCID_DESC
       )
        {
            string lQuery = "";
            DataTable lTable = new DataTable();

            lQuery = MEIADIARIAQD.qMeiaDiariaListCompleteBuscaID;
            lQuery += @"  WHERE M.DIACID_ID_ORIGEM = C1.CID_ID
                        AND M.DIACID_ID_DESTINO = C2.CID_ID
                        AND M.TVD_ID = T.TVD_ID
                        AND C1.UF_ID = U1.UF_ID
                        AND C2.UF_ID = U2.UF_ID
                        AND C1.CID_DESC LIKE '%{0}%'
                        AND C1.CID_STATUS='A'
                        AND T.TVD_STATUS='A'
                        AND C2.CID_STATUS='A'
                        AND M.MEIA_STATUS='A'
                        AND U1.UF_STATUS='A'
                        AND U2.UF_STATUS='A' ";
            lQuery = string.Format(lQuery, pCID_DESC);

            SelectCommand lSelect = new SelectCommand(lQuery);
            lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));

            return lTable;
        }

        public static int Delete(
           ConnectionInfo pInfo,
           decimal pMATERIAL_ID
        )
        {
            string lQuery = "";
             int lResult;

             DataTable lTable = new DataTable();

             lQuery = " DELETE FROM " + MATDETXFORNECEDORQD.TableName;
             lQuery += " WHERE MATERIAL_ID = " + pMATERIAL_ID;

             SelectCommand lSelect = new SelectCommand(lQuery);
             try
             {
                 lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));
                 lResult = 1;
             }
             catch (Exception Err)
             {
                 lResult = 0;
             }

             return lResult;
        }

        public static DataTable GetAllRelatorioPendente(
             ConnectionInfo pInfo, string pPESF_proposto, string pDATA
         )
        {
            string lQuery = "";
             DataTable lTable = new DataTable();

             lQuery = RELATORIOVIAGEMQD.qRELATORIOVIAGEMPendente;
             lQuery += @" WHERE D.DIA_STATUS='A'
                            AND P.PES_STATUS='A'
                            AND PC.PRES_STATUS='A'
                            AND PF.PESF_STATUS='A'
                            AND DP.DIAPRO_STATUS='A'
                            AND DOC.DDOC_STATUS='A'
                            and D.PRES_ID = PC.PRES_ID
                            AND D.DOC_ID = DOC.DOC_ID
                            AND D.DIA_ID = DP.DIA_ID
                            AND D.PESF_PROPONENTE = PF.PESF_ID
                            AND P.PES_ID = PF.PES_ID
                            AND (SELECT COUNT(*) FROM RELATORIOVIAGEM WHERE DIAPRO_ID = DP.DIAPRO_ID) = 0
                            AND DP.PESF_PROPOSTO = {0}
                            AND PC.PRES_DATAPRESTACAO <= '{1}'
                            ORDER BY PC.PRES_DATAPRESTACAO";
             lQuery = string.Format(lQuery, pPESF_proposto, pDATA);

             SelectCommand lSelect = new SelectCommand(lQuery);
             lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));

             return lTable;
        }

        public static DataSet From(ConnectionInfo pInfo, string pSelectQuery, DataFieldCollection pConditionalParameters)
        {
            DataBase lDB;
            Transaction lTransaction = null;
            DataSet lReturn = new DataSet();

            try
            {

                lDB = Instance.CreateDatabase(pInfo);

                lTransaction = new Transaction(lDB);

                lReturn = From(lTransaction, pSelectQuery, pConditionalParameters);

                lTransaction.Commit();
            }
            catch (Exception lException)
            {

                if (lTransaction != null)
                    lTransaction.Rollback();

                throw lException;
            }

            return lReturn;
        }

        public static DataTable GetTRAMITACAOByDIARIA(
            ConnectionInfo pInfo, string pDIA_ID, string pSET_ID
        )
        {
            string lQuery = "";
            DataTable lTable = new DataTable();

            //lQuery = TRAMITACAOQD.qtraMITACAOList;
            lQuery += @" SELECT T.TRA_ID, D.DIA_ID, DD.DOC_ID, t.sped_id
                            FROM TRAMITACAO T, DIARIA D, DEFDOCUMENTO DD, SEQUENCIAORGANOGRAMA S1, SEQUENCIAORGANOGRAMA S2
                            WHERE T.TRA_STATUS='A'
                            AND D.DIA_STATUS='A'
                            AND DD.DDOC_STATUS='A'
                            AND T.SORG_ID = S1.SORG_ID
                            AND S1.SORG_IDCORRESPONDENTE=S2.SORG_ID
                            AND T.DOC_ID=DD.DOC_ID
                            AND DD.DOC_ID=D.DOC_ID
                            AND D.DIA_ID={0}
                            AND S2.SET_ID={1}";
            lQuery = string.Format(lQuery, pDIA_ID, pSET_ID);

            SelectCommand lSelect = new SelectCommand(lQuery);
            lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));

            return lTable;
        }

		public static DataTable GetPORT_BANNERByID(string pNOT_ID,ConnectionInfo pInfo){
			 DataTable lReturn = new DataTable();

			 lReturn = PORT_BANNERDo.GetPORT_BANNERByID(pNOT_ID, pInfo);

			 return lReturn;
		}

        public static DataTable GetALLDESTINACAOXSUPRIMENTOFUNDOALLbyID2(
            ConnectionInfo pInfo, string pSUPFUN_ID
        )
        {
            string lQuery = "";
            DataTable lTable = new DataTable();

            lQuery = DESTINACAOXSUPRIMENTOFUNDOQD.qDESTINACAOXSUPRIMENTOFUNDOALL2;
            lQuery += @"  where ds.destfundo_status='A'
                        and g.gpp_status='A'
                        and fp.funcpro_status='A'
                        and df.detfont_status='A'
                        and nd.natdes_status='A'
                        and pl.plani_status='A'
                        and d.dest_status='A'
                        and ds.dest_id = d.dest_id
                        AND PF.PES_ID = P.PES_ID
                        AND PF.SET_ID = SE.SET_ID
                        AND S.pesf_proposto = PF.PESF_ID
                        AND PF.FUNC_ID = F.FUNC_ID
                        and pl.plani_id =  ds.plani_id
                        and ds.gpp_id = g.gpp_id
                        and g.funcpro_id = fp.funcpro_id
                        and ds.detfont_id = df.detfont_id
                        and df.natdes_id = nd.natdes_id
                        and ds.supfun_id = " + pSUPFUN_ID;

            SelectCommand lSelect = new SelectCommand(lQuery);
            lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));

            return lTable;
        }

        public static DataTable GetPEDIDOCOMPRAITEM_MATERIALFORNECEDOR(
                      ConnectionInfo pInfo,
                      string pPEDCOMP_ID
                  )
        {
            string lQuery = "";
             DataTable lTable = new DataTable();

             lQuery = PEDIDOCOMPRAITEMQD.qPEDIDOCOMPRAITEM_MaterialFornecedor;
             lQuery += @" WHERE MF.MATFORN_STATUS='A'
                          AND M.MATERIAL_STATUS='A'
                          AND PI.PEDITEM_STATUS='A'
                          AND F.FORN_STATUS='A'
                          AND PI.MATFORN_ID=MF.MATFORN_ID
                          AND MF.MATERIAL_ID=M.MATERIAL_ID
                          AND PI.PEDCOMP_ID={0}
                          AND MF.FORN_ID=F.FORN_ID ";

             lQuery = string.Format(lQuery, pPEDCOMP_ID);

             SelectCommand lSelect = new SelectCommand(lQuery);
             lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));

             return lTable;
        }

        public static DataTable GetExtratoPortaria(
             ConnectionInfo pInfo, string pPORT_ID
         )
        {
            string lQuery = "";
             DataTable lTable = new DataTable();

             lQuery += @" SELECT DIAP.DIA_ID,PORT.PORT_NUMERO PORTARIA, TO_CHAR(PORT.PORT_REGDATE, 'DD/MM/YYYY')PORT_REGDATE
                        ,DIAQ.DIAQT_DIARIATOTAL TOTAL,TO_CHAR(DIAQ.DIAQT_DATADEINICIO, 'DD/MM/YYYY') INI, TO_CHAR(DIAQ.DIAQT_DATADETERMINO, 'DD/MM/YYYY') FIM
                        ,(SELECT UPPER(CID.CID_DESC) FROM CIDADE CID WHERE CID.CID_ID = DIAQ.CID_ID_LOCALDEPARTIDA) ORIGEM
                        ,(SELECT UPPER(CID.CID_DESC) FROM CIDADE CID WHERE CID.CID_ID = DIAQ.CID_ID_LOCALDEDESTINO) DESTINO
                        FROM PORTARIA PORT LEFT JOIN DIARIAXPORTARIA DIAP
                        ON PORT.PORT_ID = DIAP.PORT_ID
                        LEFT JOIN DIARIAQUANTIDADE DIAQ
                        ON DIAP.DIA_ID = DIAQ.DIA_ID
                        WHERE PORT.PORT_ID = {0}
                        ORDER BY INI ";

             lQuery = string.Format(lQuery, pPORT_ID);

             SelectCommand lSelect = new SelectCommand(lQuery);
             lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));

             return lTable;
        }

        public static DataTable GetAllPERIODOXSETORNOME(
          ConnectionInfo pInfo, Decimal pSET_ID
      )
        {
            string lQuery = "";
            DataTable lTable = new DataTable();

            // lQuery = PERIODOXSETORQD.qPERIODOXSETORList;
            //            lQuery += @" WHERE PERQ_STATUS='A'
            //                         AND S.SET_ID = P.SET_ID
            //                         AND S.SET_STATUS='A'
            //                         AND P.PERQ_DIAINICIO <= SYSDATE
            //                         AND P.PERQ_DATAFIM >= SYSDATE
            //                         AND S.SET_ID = " + pSET_ID;

            lQuery += @"  SELECT S.*,P.*
                          from PERIODOXSETOR P , SETOR S WHERE PERQ_STATUS='A'
                          AND S.SET_ID = P.SET_ID
                          AND S.SET_STATUS='A'
                          AND S.SET_ID = " + pSET_ID;

            SelectCommand lSelect = new SelectCommand(lQuery);
            lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));

            return lTable;
        }

        public static DataTable GetDIARIAXTIPODESLOCAMENTObyID(
             ConnectionInfo pInfo, string pDIA_ID
         )
        {
            string lQuery = "";
             DataTable lTable = new DataTable();

             lQuery += " SELECT DISTINCT DT.DIA_ID, TD.DESLOC_ID, TD.DESLOC_NOME ";
             lQuery += " FROM DIARIAXTIPODESLOCAMENTO DT, TIPODESLOCAMENTO TD ";
             lQuery += " WHERE DIATIPO_STATUS='A' ";
             lQuery += " AND TD.DESLOC_STATUS='A' ";
             lQuery += " AND DT.DESLOC_ID = TD.DESLOC_ID ";
             lQuery += " AND DT.DESLOC_ID NOT IN (8) ";
             lQuery += " AND DIA_ID = " + pDIA_ID + " ";
             lQuery += " UNION ";
             lQuery += " SELECT DISTINCT DT.DIA_ID, TD.DESLOC_ID, TD.DESLOC_NOME || ' - ' || DT.DIATIPO_OUTROS AS DESLOC_NOME ";
             lQuery += " FROM DIARIAXTIPODESLOCAMENTO DT, TIPODESLOCAMENTO TD ";
             lQuery += " WHERE DIATIPO_STATUS='A' ";
             lQuery += " AND TD.DESLOC_STATUS='A' ";
             lQuery += " AND DT.DESLOC_ID = TD.DESLOC_ID ";
             lQuery += " AND DT.DESLOC_ID = 8 ";
             lQuery += " AND DIA_ID = " + pDIA_ID + " ";

             SelectCommand lSelect = new SelectCommand(lQuery);
             lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));

             return lTable;
        }

        public static DataTable GetAllBibliotecapendenciasdevolucao2(
             ConnectionInfo pInfo,
            decimal ept_pesf_id,
             string PES_NOME,
            string data
         )
        {
            string lQuery = "";
            DataTable lTable = new DataTable();

            lQuery += Biblioteca_EmprestimoQD.GetAllBibliotecapendenciasdevolucao2;
            lQuery += @" WHERE e.ept_pesf_id=" + ept_pesf_id + @"
                         AND e.ept_id=b.ept_id
                         and e.ept_status='A'
                          AND B.IEPT_STATUS='A'
                        ),e.ept_pesf_id,P.PES_NOME";
            lQuery += Biblioteca_EmprestimoQD.GetAllBibliotecapendenciasdevolucaosub2;
            lQuery += @" where e.ept_id=b.ept_id
                          AND e.ept_pesf_id=" + ept_pesf_id + @"
                          and e.ept_status='A'
                          AND B.IEPT_STATUS='A'
                          AND UPPER(PES_NOME) LIKE UPPER('%{0}%')
                          and f.pes_id= P.PES_ID
                          and  e.ept_data_fim > to_date('" + data + "')";
            lQuery = string.Format(lQuery, PES_NOME);

            SelectCommand lSelect = new SelectCommand(lQuery);
            lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));

            return lTable;
        }

        public static DataTable GetMeusTRECHOS(
       ConnectionInfo pInfo, decimal pDIA_ID
   )
        {
            string lQuery = "";
             DataTable lTable = new DataTable();

             lQuery += @"  select A.DIATRE_ID,A.DIA_ID,F.UF_DESC AS UFORIGEM,B.CID_DESC AS ORIGEM ,F.UF_DESC AS UFDESTINO, E.CID_DESC AS DESTINO,A.DIATRE_DATA
                                                                        from DIARIATRECHO A, CIDADE B ,CIDADE E,
                                                                        DIARIA C,UNIDADEFEDERATIVA F,UNIDADEFEDERATIVA G
                                                                        WHERE A.DIA_ID = C.DIA_ID
                                                                        AND C.DIA_ID = A.DIA_ID
                                                                        AND B.cid_id = A.cid_id_localorigem
                                                                        AND E.cid_id = A.cid_id_localdestino
                                                                        AND B.CID_STATUS='A'
                                                                        AND E.CID_STATUS='A'
                                                                        AND F.UF_STATUS='A'
                                                                        AND A.DIATRE_STATUS = 'A'
                                                                        AND F.UF_ID = B.UF_ID
                                                                        AND G.UF_ID = B.UF_ID
                                                                        AND A.DIA_ID = {0}";
             lQuery = string.Format(lQuery, pDIA_ID);

             SelectCommand lSelect = new SelectCommand(lQuery);
             lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));

             return lTable;
        }

        public static DataTable GetAllBIBLIOTECA_ITEMREVISTAID1(
       ConnectionInfo pInfo,
        string LV_ID,
        decimal JRNL_REVISTA_ID

   )
        {
            string lQuery = "";
            DataTable lTable = new DataTable();

            lQuery += BIBLIOTECA_ITEMJRNL_REVISTAQD.GetAllBIBLIOTECA_ITEMLIVROID1;
            lQuery += @" where il.JRNL_REVISTA_ID=lvr.JRNL_REVISTA_ID
                        and il.JRNL_REVISTA_ID=" + JRNL_REVISTA_ID + @"
                        and not exists(";
            lQuery += BIBLIOTECA_ITEMJRNL_REVISTAQD.GetAllBIBLIOTECA_ITEMLIVROID1SUB;
            lQuery += @"where ie.cod_item=il.ITEMJRNL_REVISTA_ID
                        and ie.ttab_id='{0}'
                        )";
            lQuery = string.Format(lQuery, LV_ID);

            SelectCommand lSelect = new SelectCommand(lQuery);
            lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));

            return lTable;
        }

        /// <summary>
        /// Altera Session NLS_COMP e NLS_SORT, para iguinorar acentos na pesquisa
        /// </summary>
        /// <returns></returns>
        public static void AlterSessionBinary(ConnectionInfo pInfo)
        {
            SqlQuery lSqlQuery;
            int i;
            lSqlQuery = new SqlQuery(Instance.CreateDatabase(pInfo), "ALTER SESSION SET NLS_COMP=LINGUISTIC");
            i = lSqlQuery.ExecuteNonQuery();

            lSqlQuery = new SqlQuery(Instance.CreateDatabase(pInfo), "ALTER SESSION SET NLS_SORT=BINARY_AI");
            i = lSqlQuery.ExecuteNonQuery();
        }