/// <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();
}
public static decimal GetNext(Transaction pTransaction, string pIDFieldValue)
{
decimal lID;
// 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(pTransaction);
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(pTransaction);
// Retorna novo valor da chave [SEQUENCE VALUE]
return lID;
}
public static int COUNT_GetAllPEDIDOCOMPRAById(
string iIndice,
ConnectionInfo pInfo
)
{
string lQuery = "";
int lCount = 0;
lQuery = "Select Count(*) from (";
lQuery += PEDIDOCOMPRAQD.qPEDIDOCOMPRAList;
lQuery += @" WHERE PEDCOMP_STATUS='A' AND SPED_STATUS='A' AND PF.PESF_STATUS = 'A' AND P.PES_STATUS = 'A'
AND PC.SPED_ID = S.SPED_ID
AND PC.PESF_SOLICITANTE = PF.PESF_ID
AND PF.PES_ID = P.PES_ID ";
lQuery += " AND PC.SPED_ID = '{0}' )";
lQuery = string.Format(lQuery, iIndice);
SelectCommand lSelect = new SelectCommand(lQuery);
lCount = int.Parse(lSelect.ReturnScalar(Instance.CreateDatabase(pInfo)).ToString());
return lCount;
}
public static decimal GetNext(Transaction pTransaction, string pTableName, string pIDFieldName, string pWhereClause)
{
SelectCommand lSelectNext;
string lSelectQuery = "SELECT Max({0}) + 1 FROM {1} WHERE {2}";
decimal lID;
object lScalarReturn;
lSelectQuery = string.Format(lSelectQuery, pIDFieldName, pTableName, pWhereClause);
lSelectNext = new SelectCommand(lSelectQuery);
lScalarReturn = lSelectNext.ReturnScalar(pTransaction);
if (lScalarReturn == null || lScalarReturn == DBNull.Value) lScalarReturn = 1;
lID = Convert.ToDecimal(lScalarReturn);
return lID;
}
public static int Delete_proposto(
ConnectionInfo pInfo,
string pPESF_ID
)
{
string lQuery = "";
int lResult;
Transaction lTransaction;
lTransaction = new Transaction(Instance.CreateDatabase(pInfo));
bool lLocalTransaction = (lTransaction != null);
object lTable;
lQuery = " DELETE FROM " + DIARIA_PROPOSTOQD.TableName;
lQuery += " WHERE PESF_ID = " + pPESF_ID;
SelectCommand lSelect = new SelectCommand(lQuery);
try
{
lTable = lSelect.ReturnScalar(Instance.CreateDatabase(pInfo));
lResult = 1;
}
catch (Exception Err)
{
lResult = 0;
if (lLocalTransaction)
lTransaction.Rollback();
}
return lResult;
}
public static int GetStatusMidDig(
ConnectionInfo pInfo,
string pMidDig
)
{
string lQuery = "";
int qtd = 0;
lQuery = BIBLIOTECA_MIDIADIGITALQD.GetStatusMidDig;
lQuery += " and imd.middig_id={0} AND middig_STATUS='A' and not exists(select * from biblioteca_itememprestimo ie ";
lQuery += "where ie.cod_item=imd.imd_id and ie.ttab_id=3)";
lQuery = string.Format(lQuery, pMidDig);
SelectCommand lSelect = new SelectCommand(lQuery);
qtd = int.Parse(lSelect.ReturnScalar(Instance.CreateDatabase(pInfo)).ToString());
return qtd;
}
public static decimal GetNext(ConnectionInfo pInfo, string pIDFieldValue)
{
Transaction lTransaction;
DataBase lDataBase;
lDataBase = Instance.CreateDatabase(pInfo);
lTransaction = new Transaction(lDataBase);
try
{
decimal lID;
// Inicializa operação
OperationResult lReturn = new OperationResult(QueryDictionaries.SequencesControlQD.TableName, QueryDictionaries.SequencesControlQD.TableName);
// Recupera Valor
SelectCommand lSelectNext;
string lSelectQuery = QueryDictionaries.SequencesControlQD.qSequencesControlMax;
lSelectQuery += String.Format("WHERE {0} = >>{0}", QueryDictionaries.SequencesControlQD._CONTROLNAME.Name);
object lScalarReturn;
//APB.Framework.Math.Module11
lSelectNext = new SelectCommand(lSelectQuery);
// Passagem dos Valores de Parametros para a Clausula WHERE [comando SELECT]
lSelectNext.Fields.Add(QueryDictionaries.SequencesControlQD._CONTROLNAME.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.SequencesControlQD.TableName);
// Identificação dos Campos a serem Alterados
lUpdate.Fields.Add(QueryDictionaries.SequencesControlQD._CONTROLVALUE.Name, lID, (ItemType)QueryDictionaries.SequencesControlQD._CONTROLVALUE.DBType);
string lUpdateQuery;
lUpdateQuery = String.Format("WHERE {0} = >>{0}", QueryDictionaries.SequencesControlQD._CONTROLNAME.Name);
lUpdate.Condition = lUpdateQuery;
// Passagem dos Valores para a Condição Where do Update
lUpdate.Conditions.Add(QueryDictionaries.SequencesControlQD._CONTROLNAME.Name, pIDFieldValue);
// Execução do UPDATE
lUpdate.Execute(lTransaction);
lTransaction.Commit();
// Retorna novo valor da chave [CONTROL VALUE]
return lID;
}
catch (Exception ex)
{
lTransaction.Rollback();
throw new UnknownException(ex);
}
}
public static int GetStatusJrnlRevista(
ConnectionInfo pInfo,
string pJR
)
{
string lQuery = "";
int qtd = 0;
lQuery = BIBLIOTECA_JRNL_REVISTAQD.GetStatusJrnlRevista;
lQuery += " and ijr.jrnl_revista_id={0} AND jrnl_revista_STATUS='A' and not exists(select * from biblioteca_itememprestimo ie ";
lQuery += "where ie.cod_item=ijr.itemjrnl_revista_id and ie.ttab_id=2)";
lQuery = string.Format(lQuery, pJR);
SelectCommand lSelect = new SelectCommand(lQuery);
qtd = int.Parse(lSelect.ReturnScalar(Instance.CreateDatabase(pInfo)).ToString());
return qtd;
}
public static int COUNT_GetAllPedidosAlmoxarifado(
ConnectionInfo pInfo, decimal pSPED_ID
)
{
int lReturn = 0;
string lQuery = "Select count(*) from (";
lQuery += AcompanharQD.qAllPedidosAlmoxarifado;
lQuery += @" where pm.pedido_status='A'
and dd.ddoc_status='A'
and pf.pesf_status='A'
and p.pes_status='A'
and setor.set_status='A'
and s.sped_status='A'
and pm.pesf_solicitante = pf.pesf_id
and pf.pes_id=p.pes_id
and pf.set_id=setor.set_id
and pm.doc_id = dd.doc_id
and s.sped_id = pm.sped_id
AND PM.SPED_ID = {0}
order by pm.PEDIDO_DATASOLICITACAO desc )";
lQuery = string.Format(lQuery, pSPED_ID);
SelectCommand lSelect = new SelectCommand(lQuery);
lReturn = int.Parse(lSelect.ReturnScalar(Instance.CreateDatabase(pInfo)).ToString());
return lReturn;
}
