public IEnumerable <MRFechaMetros> Recepcionados()
{
tr = new Transacciones();
IEnumerable <MRFechaMetros> resultado = tr.fechaMetros("SELECT B.FECHA,ISNULL(sum(A.TOTAL) over (order by CAST(A.FECHA as DATE)),0) TOTAL FROM (SELECT SUM(ENDDEPTH - STARTDEPTH)TOTAL, CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, CAST(DRILLINGDATE AS DATE)), 0) AS DATE) FECHA FROM COREDRILLINGSTATISTICS C WHERE PROJECTCODE IN('IN-FILL', 'CP') AND C.HOLEID IN(SELECT HOLEID FROM COLLAR WHERE FISCAL_YEAR = '2019') GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, CAST(DRILLINGDATE AS DATE)), 0))B LEFT JOIN (SELECT SUM(GEOLTO - GEOLFROM)TOTAL, CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, CAST(VALUE AS DATE)), 0) AS DATE) FECHA FROM GEODETAILS C WHERE PROJECTCODE IN('IN-FILL', 'CP') AND C.HOLEID IN(SELECT HOLEID FROM COLLAR WHERE FISCAL_YEAR = '2019') AND NAME = 'BanFechaRecepcion' GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, CAST(VALUE AS DATE)), 0) )A ON B.FECHA = A.FECHA ORDER BY B.FECHA ");
return(resultado);
}
public IEnumerable <MRFechaMetros> BackLogChemical()
{
tr = new Transacciones();
IEnumerable <MRFechaMetros> resultado = tr.fechaMetros("SELECT c.FECHA,isnull((sum(A.TOTAL) over (order by CAST(A.FECHA as DATE))-ISNULL(sum(B.TOTAL) over (order by CAST(B.FECHA as DATE)),0)),0)TOTAL FROM(SELECT SUM(ENDDEPTH - STARTDEPTH)TOTAL, CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, CAST(DRILLINGDATE AS DATE)), 0) AS DATE) FECHA FROM COREDRILLINGSTATISTICS C WHERE PROJECTCODE IN('IN-FILL', 'CP') AND C.HOLEID IN(SELECT HOLEID FROM COLLAR WHERE FISCAL_YEAR = '2019') GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, CAST(DRILLINGDATE AS DATE)), 0))c LEFT JOIN ( SELECT SUM(SAMPTO - SAMPFROM)TOTAL, CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, CAST(SENDDATE AS DATE)), 0) AS DATE) FECHA FROM SAMPLE S INNER JOIN SAMPLEDESPATCH SD ON S.SAMPLEID = SD.SAMPLEID INNER JOIN DESPATCHSEND DS ON SD.DESPATCHNO = DS.DESPATCHNO WHERE S.SAMPLETYPE = '2M' AND DS.ANALYSISSUITE in ('SUPC_2015', 'OX_2015', 'OX_2017') AND S.HOLEID IN(SELECT HOLEID FROM COLLAR WHERE FISCAL_YEAR = '2019' AND PROJECTCODE IN('IN-FILL', 'CP')) AND SENDDATE IS NOT NULL GROUP BY CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, CAST(SENDDATE AS DATE)), 0) AS DATE))A ON c.FECHA = A.FECHA left JOIN ( SELECT SUM(SAMPTO - SAMPFROM)TOTAL, CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, CAST(FECHA_VALIDACION AS DATE)), 0) AS DATE) FECHA FROM SAMPLE S INNER JOIN SAMPLEDETAILS SD ON S.SAMPLEID = SD.SAMPLEID INNER JOIN COLLAR HD ON HD.HOLEID = S.HOLEID WHERE S.SAMPLETYPE = '2M' AND NAME = 'SA_STATE' AND VALUE = 'Analizada' and S.HOLEID IN(SELECT HOLEID FROM COLLAR WHERE FISCAL_YEAR = '2019' AND PROJECTCODE IN('IN-FILL', 'CP') AND STATUS IN('Modelable', 'Extraible', 'Reproceso')) GROUP BY CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, CAST(FECHA_VALIDACION AS DATE)), 0) AS DATE))B ON A.FECHA = B.FECHA ORDER BY c.FECHA ");
return(resultado);
}
public IEnumerable <MRFechaMetros> ReleasedDataBase()
{
tr = new Transacciones();
IEnumerable <MRFechaMetros> resultado = tr.fechaMetros("SELECT A.FECHA,ISNULL(sum(B.TOTAL) over (order by CAST(B.FECHA as DATE)),0) TOTAL FROM (SELECT SUM(ENDDEPTH - STARTDEPTH)TOTAL,CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, CAST(DRILLINGDATE AS DATE)), 0) AS DATE) FECHA FROM COREDRILLINGSTATISTICS C WHERE PROJECTCODE IN('IN-FILL', 'CP') AND C.HOLEID IN(SELECT HOLEID FROM COLLAR WHERE FISCAL_YEAR = '2019') GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, CAST(DRILLINGDATE AS DATE)), 0) )A LEFT JOIN (SELECT SUM(DEPTH)TOTAL, CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, CAST(FECHA_VALIDACION AS DATE)), 0) AS DATE) FECHA FROM COLLAR WHERE FISCAL_YEAR = '2019' AND STATUS IN('Modelable', 'Extraible', 'Reproceso', 'Recodificacion', 'Remapeo') AND PROJECTCODE IN('IN-FILL', 'CP') and FECHA_VALIDACION is not null GROUP BY CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, CAST(FECHA_VALIDACION AS DATE)), 0) AS DATE))B ON A.FECHA = B.FECHA ORDER BY FECHA");
return(resultado);
}
public IEnumerable <MRFechaMetros> NoProcessDataBase()
{
tr = new Transacciones();
IEnumerable <MRFechaMetros> resultado = tr.fechaMetros("SELECT B.FECHA,ISNULL(sum(A.TOTAL) over (order by CAST(A.FECHA as DATE)),0) TOTAL FROM (SELECT SUM(ENDDEPTH - STARTDEPTH)TOTAL, CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, CAST(DRILLINGDATE AS DATE)), 0) AS DATE) FECHA FROM COREDRILLINGSTATISTICS C WHERE PROJECTCODE IN('IN-FILL', 'CP') AND C.HOLEID IN(SELECT HOLEID FROM COLLAR WHERE FISCAL_YEAR = '2019') GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, CAST(DRILLINGDATE AS DATE)), 0))B LEFT JOIN (SELECT SUM(DEPTH - EN_EM_ALL)TOTAL, CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, CAST(FECHA_ESQUEMA AS DATE)), 0) AS DATE) FECHA FROM EN_ESQUEMA WHERE HOLEID IN(SELECT HOLEID FROM COLLAR WHERE FISCAL_YEAR = '2019' AND PROJECTCODE IN('IN-FILL', 'CP') AND STATUS not IN('Perdido', 'Propuesto')) GROUP BY CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, CAST(FECHA_ESQUEMA AS DATE)), 0) AS DATE))A ON B.FECHA = A.FECHA ORDER BY B.FECHA");
return(resultado);
}
//View INDEX - RESUMEN BACKLOG
public IEnumerable <MRFechaMetros> CumulatedDrilling()
{
tr = new Transacciones();
IEnumerable <MRFechaMetros> resultado = tr.fechaMetros("SELECT FECHA,sum(TOTAL) over (order by CAST(FECHA as DATE)) TOTAL FROM (SELECT SUM(ENDDEPTH - STARTDEPTH)TOTAL,CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, CAST(DRILLINGDATE AS DATE)), 0) AS DATE) FECHA FROM COREDRILLINGSTATISTICS C WHERE PROJECTCODE IN('IN-FILL', 'CP') AND C.HOLEID IN(SELECT HOLEID FROM COLLAR WHERE FISCAL_YEAR = '2019') GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, CAST(DRILLINGDATE AS DATE)), 0) )A ORDER BY FECHA ");
return(resultado);
}
