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