/// <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; }