//Methods... public void MoverCeros(string pautaId, SqlTransaction tran) { DAOHelper.EjecutarNonQuery( string.Format( "UPDATE [EstimadoDet] SET Costo=0, CostoOP=0, CostoUni=0, CostoOPUni=0 WHERE PautaId = '{0}' AND DatareaId = {1}", pautaId, DAOHelper.DatareaId), tran); }
public void MoverCeros(string pautaId, SqlTransaction tran) { DAOHelper.EjecutarNonQuery( string.Format( "UPDATE [CertificadoSKU] SET Costo=0, CostoOP=0, CostoUni=0, CostoOPUni=0 WHERE PautaId = '{0}'", pautaId), tran); }
//Methods... public DataTable GetSKUByPiezasArte(List <EstimadoDetDTO> lineas) { var dt = new DataTable(); string strIn = ""; string last = ""; string qry = @"SELECT *, 0 as CantSalidas FROM SKU JOIN PiezasArteSKU ON SKU.IdentifSKU=PiezasArteSKU.IdentifSKU JOIN Avisos ON PiezasArteSKU.IdentifPieza=Avisos.IdentifPieza WHERE PiezasArteSKU.TipoProd = 'PRIMARIO' AND IdentifAviso IN ({0})"; //'av1', 'AV_1' //Si no tengo linas de detalle, tampoco productos. if (lineas.Count == 0) { return(dt); } lineas.Sort((x, y) => x.IdentifAviso.CompareTo(y.IdentifAviso)); lineas.ForEach((x) => { if (x.IdentifAviso != last) { last = x.IdentifAviso; strIn += string.Format("'{0}',", x.IdentifAviso); } }); qry = string.Format(qry, strIn.Substring(0, strIn.Length - 1)) + " AND SKU.DatareaId = " + DAOHelper.DatareaId; DAOHelper.LlenarDataTable(ref dt, qry); return(dt); }
//Methods... public DataView VistaCertificados() { DataTable dt = new DataTable(); string sQuery = @"SELECT oc.RecId , oc.PautaId , oc.IdentifOrigen , oc.IdentifEspacio , IdentifFrecuencia , IdentifIntervalo , CONVERT(VARCHAR,oc.AnoMes) AS AnoMes , SUBSTRING((CAST(oc.AnoMes AS VARCHAR(6))),1,4) AS 'año' , SUBSTRING((CAST(oc.AnoMes AS VARCHAR(6))),5,2) AS 'mes' , oc.HoraInicio,oc.HoraFin,oc.VigDesde , oc.VigHasta , oc.UsuCosto , oc.CantSalidas , oc.CertValido , oc.FecCertValido FROM dbo.CertificadoCab oc WHERE oc.IdentifOrigen != 'NULL' ORDER BY oc.RecId DESC"; DAOHelper.LlenarDataTable(ref dt, sQuery); return(dt.DefaultView); }
public DataView VistaCostosConfirmados() { DataTable dtconf = new DataTable(); DAOHelper.LlenarDataTable(ref dtconf, "SELECT DISTINCT ct.RecId ,ct.IdentifEspacio ,ct.IdentifFrecuencia ,SUBSTRING(( CAST(ct.VigDesde AS VARCHAR(8)) ), 1, 4) AS 'año' , SUBSTRING(( CAST(ct.VigDesde AS VARCHAR(8)) ), 6, 2) AS 'mes' ,ct.VigDesde ,ct.VigHasta ,ct.Confirmado ,ct.FecConfirmado FROM dbo.Costos ct , dbo.OrdenadoCab oc WHERE ct.Confirmado != '' AND ( CONVERT(VARCHAR, ct.IdentifEspacio) + CONVERT(VARCHAR, ct.VigDesde) + CONVERT(VARCHAR, ct.VigHasta) ) NOT IN ( SELECT ( CONVERT(VARCHAR, oc.IdentifEspacio) + CONVERT(VARCHAR, oc.VigDesde) + CONVERT(VARCHAR, oc.VigHasta) ) FROM dbo.OrdenadoCab oc , Costos ct WHERE ct.VigDesde = oc.VigDesde AND ct.VigHasta = oc.VigHasta AND ct.IdentifEspacio = oc.IdentifEspacio )"); return(dtconf.DefaultView); }
public DataView VistaOrdenados() { DataTable dt = new DataTable(); DAOHelper.LlenarDataTable(ref dt, "SELECT oc.RecId ,oc.PautaId ,oc.IdentifEspacio ,IdentifFrecuencia,IdentifIntervalo,CONVERT(VARCHAR,oc.AnoMes) AS AnoMes ,SUBSTRING((CAST(oc.AnoMes AS VARCHAR(6))),1,4) AS 'año',SUBSTRING((CAST(oc.AnoMes AS VARCHAR(6))),5,2) AS 'mes',oc.HoraInicio,oc.HoraFin,oc.VigDesde,oc.VigHasta,oc.UsuCosto,oc.CantSalidas,oc.UsuCierre,oc.FecCierre FROM dbo.OrdenadoCab oc ORDER BY oc.RecId DESC"); return(dt.DefaultView); }
public bool ExisteAvisoEnVigencia(string IdentifIdentAtencion) { string qry = @"SELECT COUNT(1) FROM AvisosIdAten JOIN Avisos ON AvisosIdAten.IdentifAviso = Avisos.IdentifAviso WHERE AvisosIdAten.IdentifIdentAte = '{0}' AND Avisos.VigHasta > GETDATE() AND Avisos.DatareaId = " + DAOHelper.DatareaId; qry = string.Format(qry, IdentifIdentAtencion); return(Convert.ToInt32(DAOHelper.EjecutarScalar(qry)) > 0); }
public int GetLastRecId() { object aux = DAOHelper.EjecutarScalar("SELECT MAX(RECID) FROM ESTIMADODET"); if (aux is DBNull) { return(0); } else { return(Convert.ToInt32(aux)); } }
//Methods... public DataTable GetSKUByPiezasArte(List <CertificadoDetDTO> lineas) { try { var dt = new DataTable(); string strIn = ""; string last = ""; string qry = @"SELECT *, 0 as CantSalidas FROM SKU JOIN PiezasArteSKU ON SKU.IdentifSKU=PiezasArteSKU.IdentifSKU JOIN Avisos ON PiezasArteSKU.IdentifPieza=Avisos.IdentifPieza WHERE PiezasArteSKU.TipoProd = 'PRIMARIO' AND IdentifAviso IN ({0});"; //'av1', 'AV_1' //Si no tengo linas de detalle, tampoco productos. if (lineas.Count == 0) { return(dt); } lineas.Sort((x, y) => x.IdentifAviso.CompareTo(y.IdentifAviso)); lineas.ForEach((x) => { if (x.IdentifAviso != last) { last = x.IdentifAviso; strIn += string.Format("'{0}',", x.IdentifAviso); } }); qry = string.Format(qry, strIn.Substring(0, strIn.Length - 1)); DAOHelper.LlenarDataTable(ref dt, qry); return(dt); } catch (Exception ex) { string a = ex.ToString(); DataTable dtx = new DataTable(); return(dtx); //throw new Exception("Debe seleccionar un aviso"); } }
//Methods... public List <EmpresaDTO> GetEmpresas() { List <EmpresaDTO> empresas = new List <EmpresaDTO>(); EmpresaDTO empresa; var dt = new DataTable(); string qry = @"SELECT * FROM Empresa"; DAOHelper.LlenarDataTable(ref dt, qry); foreach (DataRow dr in dt.Rows) { empresa = new EmpresaDTO(); DAOHelper.PoblarObjetoDesdeDataRow(empresa, dr); empresas.Add(empresa); } return(empresas); }
public DataTable FindValue(string IdentifIdentAtencion) { string qry = @"SELECT [IdentifIdentAte] , [Name] , IsNull((SELECT COUNT(1) FROM AvisosIdAten JOIN Avisos ON AvisosIdAten.IdentifAviso = Avisos.IdentifAviso WHERE AvisosIdAten.IdentifIdentAte = IdentAtencion.IdentifIdentAte AND VigHasta > GETDATE() ), 0) as [Asignado] FROM [IdentAtencion] where [IdentifIdentAte] = '{0}' AND Estado = 1 AND IdentAtencion.DatareaId = " + DAOHelper.DatareaId; var dt = new DataTable(); qry = string.Format(qry, IdentifIdentAtencion); DAOHelper.LlenarDataTable(ref dt, qry); return(dt); }
//Methods... public DataTable FindPaginado(string filter, Int64 startIndex, Int64 endIndex) { string qry = @"SELECT [IdentifIdentAte] , [Name] , IsNull((SELECT COUNT(1) FROM AvisosIdAten JOIN Avisos ON AvisosIdAten.IdentifAviso = Avisos.IdentifAviso WHERE AvisosIdAten.IdentifIdentAte = st.IdentifIdentAte AND VigHasta > GETDATE() AND Avisos.DatareaId = " + DAOHelper.DatareaId + @" ), 0) as [Asignado] FROM (select [IdentifIdentAte], [Name], row_number()over(order by t.[IdentifIdentAte]) as [rn] from [IdentAtencion] as t where (([IdentifIdentAte] + ' ' + [Name]) LIKE '%{0}%' AND Estado = 1 AND t.DatareaId = " + DAOHelper.DatareaId + @")) as st WHERE st.[rn] between {1} and {2}"; var dt = new DataTable(); qry = string.Format(qry, filter, startIndex, endIndex); DAOHelper.LlenarDataTable(ref dt, qry); return(dt); }
public string GetSqlVersion() { return(Convert.ToString(DAOHelper.EjecutarScalar("SELECT SERVERPROPERTY('productversion')"))); }
public void LlenarTabla(DataTable Tabla, string sQuery) { DAOHelper.LlenarDataTable(ref Tabla, sQuery); }
public string AnoMesCierreOrd() { return(Convert.ToString(DAOHelper.EjecutarScalar("SELECT AnoMesCierreOrd FROM dbo.SetUp "))); }