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;
}
/// <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();
}
/// <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;
}
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;
}
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();
}
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;
}
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;
}
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);
}
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();
}
}
public static DataTable TableFrom(string pSelectQuery, DataFieldCollection pConditionalParameters, ConnectionInfo pInfo)
{
DataSet lSet = From(pInfo, pSelectQuery, pConditionalParameters);
return lSet.Tables[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;
}
public static DataSet From(ConnectionInfo pInfo, string pSelectQuery)
{
SqlQuery lSelect = new SqlQuery(Instance.CreateDatabase(pInfo), pSelectQuery);
DataSet lReturn = lSelect.ExecuteDataSet();
return lReturn;
}
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;
}
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;
}
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;
}
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();
}