Ejemplo n.º 1
0
        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 string GetPassword(string pSusr_Login, ConnectionInfo pConnectionInfo)
        {
            bool lReturn;
            string lQuery = "";
            string lPassWord = "";
            DataTable lTable;

            lQuery = SystemUserQD.qSystemUserList;
            lQuery += String.Format(" WHERE {0} = >>{0} ", SystemUserQD._SUSR_LOGIN.Name);
            lQuery += " AND SUSR_STATUS='A' ";

            SelectCommand lSelect = new SelectCommand(lQuery);

            lSelect.Fields.Add(SystemUserQD._SUSR_LOGIN.Name, pSusr_Login, (ItemType)SystemUserQD._SUSR_LOGIN.DBType);

            lTable = lSelect.ReturnData(Instance.CreateDatabase(pConnectionInfo));

            lReturn = (lTable.Rows.Count > 0) ? true : false;

            // User Accept, Get Password
            if (lReturn)
            {
                // Decodificar Senha para Envio
                lPassWord = APB.Framework.Encryption.Crypto.Decode(lTable.Rows[0]["SUSR_PASSWORD"].ToString());
            }

            return lPassWord;
        }
Ejemplo n.º 3
0
        /// <summary>
        /// Retorna o count, espera já uma query de count
        /// </summary>
        /// <param name="pConnectionString">String de conexão com o banco</param>
        /// <param name="pSelectQuery">Query para rodar (ex.: SELECT Count(PRV_ID) FROM Providers)</param>
        /// <returns></returns>
        public static decimal Count(ConnectionInfo pInfo, string pSelectQuery)
        {
            //TODO: Depois vai ter que criptografar pConnectionString e pSelectQuery

            SelectCommand lSelect = new SelectCommand(pSelectQuery);

            object lValue = lSelect.ReturnScalar(Instance.CreateDatabase(pInfo));

            return lValue.DBToDecimal();
        }
Ejemplo n.º 4
0
        /// <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();
        }
        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 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 GetSystemUser(ConnectionInfo pInfo, string pUsuario, string pSenha)
        {
            string lQuery = "";
            DataTable lTable = new DataTable();
            pSenha = APB.Framework.Encryption.Crypto.Encode(pSenha);

            lQuery = SystemUserQD.qSystemUserList;
            lQuery += " WHERE SUSR_STATUS = 'A' AND SUSR_LOGIN = '******' AND SUSR_PASSWORD='******' ";

            SelectCommand lSelect = new SelectCommand(lQuery);

            lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));

            return lTable;
        }
        public static DataTable GetAllTIPOMATERIAXNAAP(
             ConnectionInfo pInfo
         )
        {
            string lQuery = "";
             DataTable lTable = new DataTable();

             lQuery = TIPOMATERIAXNAAPQD.qTIPOPETICAOXNAAPList;
             lQuery += " WHERE TPMN_STATUS='A' ORDER BY TPMN_DESCRICAO ";

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

             return lTable;
        }
        public static DataTable GetAllTipoPeticao(
            ConnectionInfo pInfo
        )
        {
            string lQuery = "";
            DataTable lTable = new DataTable();

            lQuery = ModeloPeticaoQD.qTipoPeticao;
            lQuery += " WHERE TPPT_STATUS='A' ORDER BY TPPT_DESCRICAO ";

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

            return lTable;
        }
Ejemplo n.º 10
0
        public static DataTable GetConsultaPecaGeral(ConnectionInfo pInfo, string pCondicao)
        {
            string lQuery = "";
                    DataTable lTable = new DataTable();

                    lQuery = PECASXNAAPQD.qConsultaPeticao;
                    lQuery += " WHERE PECN_STATUS='A' ";
                    lQuery += pCondicao;
                    lQuery += " ORDER BY PECN_ID DESC ";

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

                    return lTable;
        }
Ejemplo n.º 11
0
        public static string GetConsultaPecaEnviada(
                    ConnectionInfo pInfo
                )
        {
            string lQuery = "";
                    DataTable lTable = new DataTable();

                    lQuery = PECASXNAAPQD.qPETICAOXNAAPCount;
                    lQuery += " WHERE PECN_STATUS='E' ";

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

                    return lTable.Rows[0]["CONT"].ToString();
        }
Ejemplo n.º 12
0
        public static DataTable GetAllPETICAOXNAAP(                                                           
                    ConnectionInfo pInfo                                    
                )
        {
            string lQuery = "";
                    DataTable lTable = new DataTable();

                    lQuery = PECASXNAAPQD.qLoadPETICAOXNAAP;
                    lQuery += " WHERE PECN_STATUS='A'";

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

                    return lTable;
        }
        public static DataTable GetModeloPeticaoByTipo(
            decimal pTPPT_ID,
            ConnectionInfo pInfo
        )
        {
            string lQuery = "";
            DataTable lTable = new DataTable();

            lQuery = ModeloPeticaoQD.qModeloPeticaoTexto;
            lQuery += string.Format(" WHERE MDPT_STATUS='A' AND {0} = >>{0}", ModeloPeticaoQD._TPPT_ID.Name);

            SelectCommand lSelect = new SelectCommand(lQuery);

            lSelect.Fields.Add(ModeloPeticaoQD._TPPT_ID.Name, pTPPT_ID, (ItemType)ModeloPeticaoQD._TPPT_ID.DBType);

            lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));

            return lTable;
        }
Ejemplo n.º 14
0
        public static DataTable GetConsultaPeticaoporTags(
                    ConnectionInfo pInfo
                    , string pCondicao
                    , int pTipo
                )
        {
            string lQuery = "";
                    DataTable lTable = new DataTable();

                    lQuery = PECASXNAAPQD.qConsultaPeticao;
                    lQuery += " WHERE PECN_STATUS='A' ";
                    if (pTipo == 0)
                        lQuery += " AND PECN_TAGS LIKE '%" + pCondicao + "%' ";
                    else
                        lQuery += " AND PECN_TAGS LIKE '" + pCondicao + "' ";
                    lQuery += " ORDER BY PECN_ID DESC ";

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

                    return lTable;
        }
Ejemplo n.º 15
0
		public static APB.Framework.DataBase.DataBase CreateDatabase(ConnectionInfo pInfo)
		{
			//TODO: Fazer direito, a connstring tem que ser criptografada
			return new APB.Framework.DataBase.DataBase((DataBaseType)pInfo.DataBaseType, pInfo.ConnectionString);
		}
Ejemplo n.º 16
0
        public static string InsertPeticao(ConnectionInfo pInfo, string pTPN_ID, string pPETN_TAGS, string pPETN_EMENTA, string pPETN_TEXTO, string pPETN_USUARIO)
        {
            string lSql = "";
                    string constr = "Data Source=desenv2; User ID=desenv; Password=desenv";

                    try
                    {
                        OracleConnection con = new OracleConnection(constr);

                        lSql = " INSERT INTO PETICAOXNAAP(PETN_ID,TPN_ID,PETN_TEXTO,PETN_TAGS,PETN_EMENTA,PETN_STATUS,PETN_REGDATE,PETN_USUARIO) ";
                        lSql += " VALUES(:PETN_ID,:TPN_ID,:PETN_TEXTO,:PETN_TAGS,:PETN_EMENTA,'A',SYSDATE,:PETN_USUARIO) ";

                        OracleCommand cmd = new OracleCommand();
                        cmd.CommandText = lSql;
                        cmd.Connection = con;

                        decimal lSequence;
                        lSequence = DataBaseSequenceControl.GetNext(pInfo, "PETN_ID");

                        OracleParameter OraParameter1 = new OracleParameter();
                        OracleParameter OraParameter2 = new OracleParameter();
                        OracleParameter OraParameter3 = new OracleParameter();
                        OracleParameter OraParameter4 = new OracleParameter();
                        OracleParameter OraParameter5 = new OracleParameter();
                        OracleParameter OraParameter6 = new OracleParameter();

                        OraParameter1.OracleType = OracleType.Number;
                        OraParameter1.ParameterName = ":PETN_ID";
                        OraParameter1.Value = lSequence;

                        OraParameter2.OracleType = OracleType.Number;
                        OraParameter2.ParameterName = ":TPN_ID";
                        OraParameter2.Value = Convert.ToDecimal(pTPN_ID);

                        OraParameter3.OracleType = OracleType.Clob;
                        OraParameter3.ParameterName = ":PETN_TEXTO";
                        OraParameter3.Value = pPETN_TEXTO;

                        OraParameter4.OracleType = OracleType.VarChar;
                        OraParameter4.ParameterName = ":PETN_TAGS";
                        OraParameter4.Value = pPETN_TAGS;

                        OraParameter5.OracleType = OracleType.VarChar;
                        OraParameter5.ParameterName = ":PETN_EMENTA";
                        OraParameter5.Value = pPETN_EMENTA;

                        OraParameter6.OracleType = OracleType.VarChar;
                        OraParameter6.ParameterName = ":PETN_USUARIO";
                        OraParameter6.Value = pPETN_USUARIO;

                        cmd = new OracleCommand(lSql, con);
                        cmd.Parameters.Add(OraParameter1);
                        cmd.Parameters.Add(OraParameter2);
                        cmd.Parameters.Add(OraParameter3);
                        cmd.Parameters.Add(OraParameter4);
                        cmd.Parameters.Add(OraParameter5);
                        cmd.Parameters.Add(OraParameter6);

                        con.Open();
                        cmd.ExecuteNonQuery();

                        cmd.Dispose();
                        con.Close();

                        return lSequence.ToString();
                    }
                    catch (Exception err)
                    {
                        return "ERRO: " + err.ToString();
                    }
        }
Ejemplo n.º 17
0
        public static DataTable TableFrom(string pSelectQuery, DataFieldCollection pConditionalParameters, ConnectionInfo pInfo)
        {
            DataSet lSet = From(pInfo, pSelectQuery, pConditionalParameters);

            return lSet.Tables[0];
        }
Ejemplo n.º 18
0
        public static DataTable GetPecasEnviadasHomolog(
                    ConnectionInfo pInfo
                    ,string pData
                )
        {
            string lQuery = "";
                    DataTable lTable = new DataTable();

                    lQuery = PECASXNAAPQD.qConsultaPeticao;
                    lQuery += " WHERE PECN_STATUS='E' ";
                    if (pData != "")
                        lQuery += " AND PECN_REGDATE = TO_DATE('" + pData + "','DD/MM/YYYY') ";
                    lQuery += " ORDER BY PECN_REGDATE DESC ";

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

                    return lTable;
        }
        public static decimal GetNext(ConnectionInfo pInfo, string pIDFieldValue)
        {
            decimal lID = 1;
            Transaction lTransaction;
            DataBase lDataBase;

            lDataBase = Instance.CreateDatabase(pInfo);

            lTransaction = new Transaction(lDataBase);

            try
            {

                // Inicializa operação
                OperationResult lReturn = new OperationResult(QueryDictionaries.SequencesQD.TableName, QueryDictionaries.SequencesQD.TableName);

                // Recupera Valor
                SelectCommand lSelectNext;

                string lSelectQuery = QueryDictionaries.SequencesQD.qSequencesMax;
                lSelectQuery += String.Format("WHERE {0} = >>{0}", QueryDictionaries.SequencesQD._SEQ_NAME.Name);

                object lScalarReturn;

                lSelectNext = new SelectCommand(lSelectQuery);

                // Passagem dos Valores de Parametros para a Clausula WHERE [comando SELECT]
                lSelectNext.Fields.Add(QueryDictionaries.SequencesQD._SEQ_NAME.Name, pIDFieldValue, ItemType.String);

                // Recupera Valor do Select (Seq_Value)
                lScalarReturn = lSelectNext.ReturnScalar(lTransaction);

                if (lScalarReturn == null || lScalarReturn == DBNull.Value) lScalarReturn = 1;
                lID = Convert.ToDecimal(lScalarReturn);

                // Altera Valor da Sequence
                UpdateCommand lUpdate;
                lUpdate = new UpdateCommand(QueryDictionaries.SequencesQD.TableName);

                // Identificação dos Campos a serem Alterados
                lUpdate.Fields.Add(QueryDictionaries.SequencesQD._SEQ_VALUE.Name, lID, (ItemType)QueryDictionaries.SequencesQD._SEQ_VALUE.DBType);

                string lUpdateQuery;

                lUpdateQuery = String.Format("WHERE {0} = >>{0}", QueryDictionaries.SequencesQD._SEQ_NAME.Name);
                lUpdate.Condition = lUpdateQuery;

                // Passagem dos Valores para a Condição Where do Update
                lUpdate.Conditions.Add(QueryDictionaries.SequencesQD._SEQ_NAME.Name, pIDFieldValue);

                // Execução do UPDATE
                lUpdate.Execute(lTransaction);

                lTransaction.Commit();
            }
            catch (Exception ex)
            {
                lTransaction.Rollback();
            }

            // Retorna novo valor da chave [SEQUENCE VALUE]
            return lID;
        }
Ejemplo n.º 20
0
        public static DataSet From(ConnectionInfo pInfo, string pSelectQuery)
        {
            SqlQuery lSelect = new SqlQuery(Instance.CreateDatabase(pInfo), pSelectQuery);

            DataSet lReturn = lSelect.ExecuteDataSet();

            return lReturn;
        }
Ejemplo n.º 21
0
        public static OperationResult Update(
            DataFieldCollection pValues,
            ConnectionInfo pInfo
        )
        {
            Transaction pTransaction;

            pTransaction = new Transaction(Instance.CreateDatabase(pInfo));

            bool lLocalTransaction = (pTransaction != null);

            UpdateCommand lUpdate;

            OperationResult lReturn = new OperationResult(SystemUserQD.TableName, SystemUserQD.TableName);

            ValidateUpdate(pValues, lReturn);

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

                    lUpdate = new UpdateCommand(SystemUserQD.TableName);

                    lReturn.Trace("Adicionando campos ao objeto de update");

                    foreach (DataField lField in pValues.Keys)
                    {
                        lUpdate.Fields.Add(lField.Name, pValues[lField], (ItemType)lField.DBType);
                    }

                    string lSql = "";
                    lSql = String.Format("WHERE {0} = <<{0}", SystemUserQD._SUSR_ID.Name);

                    lUpdate.Condition = lSql;

                    lUpdate.Conditions.Add(SystemUserQD._SUSR_ID.Name, pValues[SystemUserQD._SUSR_ID].DBToDecimal());

                    lReturn.Trace("Executando o Update");

                    lUpdate.Execute(pTransaction);

                    if (!lReturn.HasError)
                    {
                        if (lLocalTransaction)
                        {
                            if (!lReturn.HasError)
                            {
                                lReturn.Trace("Update finalizado, executando commit");

                                pTransaction.Commit();
                            }
                            else
                            {
                                pTransaction.Rollback();
                            }
                        }
                    }
                    else
                    {
                        if (lLocalTransaction)
                            pTransaction.Rollback();
                    }
                }
                catch (Exception ex)
                {
                    lReturn.OperationException = new SerializableException(ex);

                    if (lLocalTransaction)
                        pTransaction.Rollback();
                }
            }

            return lReturn;
        }
Ejemplo n.º 22
0
        public static DataTable From(ConnectionInfo pInfo, string pSelectQuery, int pPage, int pRowCount)
        {
            //TODO: Depois vai ter que criptografar pConnectionString e pSelectQuery

            SelectCommand lSelect = new SelectCommand(pSelectQuery);

            DataTable lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo), pPage, pRowCount).dataTable;

            return lTable;
        }
Ejemplo n.º 23
0
        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(SystemUserQD.TableName, SystemUserQD.TableName);

            ValidateInsert(pValues, lReturn);

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

                    lInsert = new InsertCommand(SystemUserQD.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, "SUSR_ID");

                    lInsert.Fields.Add(SystemUserQD._SUSR_ID.Name, lSequence, (ItemType)SystemUserQD._SUSR_ID.DBType);

                    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;
        }
Ejemplo n.º 24
0
        public static string GetConsultaPeticaoTotalPorDia(
                    ConnectionInfo pInfo
                    , string pData
                )
        {
            string lQuery = "";
                    DataTable lTable = new DataTable();

                    lQuery = PECASXNAAPQD.qPETICAOXNAAPCount;
                    lQuery += " WHERE PECN_STATUS IN ('A','E') AND PECN_REGDATE BETWEEN TO_DATE('" + pData + "','DD/MM/YYYY') ";
                    lQuery += " AND TO_DATE('" + pData + " 23:59:59','DD/MM/YYYY HH24:MI:SS') ";

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

                    return lTable.Rows[0]["CONT"].ToString();
        }