maximoDataSet = MXReportDataSetProvider.create(this.getDataSource().getName(), this.getName());
maximoDataSet.open();

importPackage( Packages.java.text );
var sqlText = new String();
var myDF1 = new SimpleDateFormat("MM/dd/yyyy", reportContext.getLocale());
var cMonth = new String();
var cMonth2 = new String();
var cMonth3 = new String();
var cMonth6 = new String();
var cMonth8 = new String();

var nDate1 = new Date();
var nDate2 = new Date();
var nDate3 = new Date();

nDate1=BirtDateTime.addMonth(BirtDateTime.now(),-params["MonthAgo"].value);
nDate1=BirtDateTime.addDay(nDate1,1-BirtDateTime.day(nDate1));

if(params["MonthAgo"].value==0) {
	nDate2=BirtDateTime.addDay(BirtDateTime.now(),1);
	cMonth3=" ";
	cMonth6=" AND (1=2) ";
	cMonth8="CURRENT TIMESTAMP";
} else {
	nDate2=BirtDateTime.addMonth(BirtDateTime.now(),2-params["MonthAgo"].value);
	nDate2=BirtDateTime.addDay(nDate2,1-BirtDateTime.day(nDate2));
	nDate3=BirtDateTime.addDay(nDate2,-BirtDateTime.day(nDate2));
	cMonth3=" AND (READINGDATE<DATE(TIMESTAMP_FORMAT('"+myDF1.format(nDate2)+"','MM/DD/YYYY'))) ";
	cMonth6=" AND (READINGDATE>=DATE(TIMESTAMP_FORMAT('"+myDF1.format(nDate2)+"','MM/DD/YYYY'))) ";
	cMonth8="DATE(TIMESTAMP_FORMAT('"+myDF1.format(nDate3)+" 23:59:59','MM/DD/YYYY HH24:MI:SS'))";
};

cMonth= " (REPORTDATE>=DATE(TIMESTAMP_FORMAT('"+myDF1.format(nDate1)+"','MM/DD/YYYY'))) AND (REPORTDATE<DATE(TIMESTAMP_FORMAT('"+myDF1.format(nDate2)+"','MM/DD/YYYY'))) ";
cMonth2=" (ft.DATEFLT>=DATE(TIMESTAMP_FORMAT('"+myDF1.format(nDate1)+"','MM/DD/YYYY'))) AND (ft.DATEFLT<DATE(TIMESTAMP_FORMAT('"+myDF1.format(nDate2)+"','MM/DD/YYYY'))) ";

sqlText="SELECT a.assetnum as tail, a.serialnum, null as site, null as contact, null as contact2, null as reporg, "
+" DATE(ds.REPORTDATE) as REPORTDATE, SUM(ds.NMCS) AS NMCS, SUM(ds.NMCM) AS NMCM, SUM(ds.PMCS) AS PMCS, SUM(ds.PMCM) AS PMCM, SUM(ds.FMC) AS FMC, SUM(ds.NRT) AS NRT, null as ACFLIGHTHRS, null as CREWTIME, null as FULLSTOP, null as TOUCHGO, "
+"null as HRS, "
+"null as FLTS, "
+"null as TCT, "
+"null as landings, "
+"null as l_serialnum, "
+"null as l_HRS, "
+"null as l_ECYC, "
+"null as r_serialnum, "
+"null as r_HRS, "
+"null as r_ECYC, "
+"MAX(ds.NOTES) AS NOTES, "
+"null as HDSR, null as ONTIME, null as l_mpi, null as l_czi, null as r_mpi, null as r_czi, null as curr_hrs  "
+"FROM asset a "
+"LEFT OUTER JOIN (SELECT ASSETNUM, REPORTDATE, FMC, NMCS, PMCM, PMCS, (NMCM_S+NMCM_U) NMCM, NRT, NOTES FROM M1_ACFTDAILYSTAT WHERE "+cMonth+") ds ON (a.ASSETNUM=ds.ASSETNUM) "
+"WHERE (a.PLUSAREG IS NOT NULL) and (a.status<>'DECOMMISSIONED') ";
if(params["Tail"].value) {sqlText+=" AND ("+MXReportSqlFormat.createParamWhereClause("a.assetnum",params["Tail"])+") "};
sqlText+="GROUP BY a.assetnum, a.serialnum, DATE(ds.REPORTDATE) ";

sqlText+="UNION ALL SELECT a.assetnum, a.serialnum, null, null, null, null, "
+" DATE(ds.DATEFLT), null, null, null, null, null, null, SUM(ds.M1_ACFLIGHTHRS), SUM(ds.M1_CREWTIME), SUM(ds.M1_FULLSTOP), SUM(ds.M1_TOUCHGO), "
+"null, null, null, null, null, null, null, null, null, null, null, SUM(ds.M1_HSDR), SUM(ds.M1_ONTIME), null, null, null, null, null "
+"FROM asset a "
+"LEFT OUTER JOIN (SELECT fb.assetnum, fb.siteid, ft.DATEFLT, ft.M1_ACFLIGHTHRS, ft.M1_CREWTIME, ft.M1_FULLSTOP, ft.M1_TOUCHGO, ft.M1_HSDR, ft.M1_ONTIME from PLUSAFLB fb, PLUSAFLTDATA ft where (fb.FLBNUM=ft.FLBNUM) and (fb.siteid=ft.siteid) and "+cMonth2+") ds ON (a.assetnum=ds.assetnum) "
+"WHERE (a.PLUSAREG IS NOT NULL) and (a.status<>'DECOMMISSIONED') ";
if(params["Tail"].value) {sqlText+=" AND ("+MXReportSqlFormat.createParamWhereClause("a.assetnum",params["Tail"])+") "};
sqlText+="GROUP BY a.assetnum, a.serialnum, DATE(ds.DATEFLT) ";

sqlText+="UNION ALL SELECT a.assetnum, a.serialnum, (select DESCRIPTION from LOCATIONS where LOCATION=A.LOCATION and siteid=a.siteid) as site, "
+"(select CONTACT1 from LOCATIONS where (LOCATION=A.LOCATION and siteid=a.siteid)), "
+"(select CONTACT2 from LOCATIONS where (LOCATION=A.LOCATION and siteid=a.siteid)), "
+"(select M1_REPORTINGORG from LOCATIONS where (LOCATION=A.LOCATION and siteid=a.siteid)), "
+" null, null, null, null, null, null, null, null, null, null, null, "
//+"(select am.plusaanchorcount+(select COALESCE(sum(delta),0) from METERREADING where (metername='HRS') and (assetnum = am.assetnum) and (READINGDATE>=am.PLUSAANCHORDATE) "+cMonth3+")-(select COALESCE(sum(delta),0) from METERREADING where (metername='HRS') and (assetnum = am.assetnum) and (READINGDATE<am.PLUSAANCHORDATE) "+cMonth6+") from ASSETMETER AM where (am.metername='HRS') and (am.assetnum = a.assetnum) and (am.siteid=a.siteid)), "
//+"(select am.plusaanchorcount+(select COALESCE(sum(delta),0) from METERREADING where (metername='FLTS') and (assetnum = am.assetnum) and (READINGDATE>=am.PLUSAANCHORDATE) "+cMonth3+")-(select COALESCE(sum(delta),0) from METERREADING where (metername='FLTS') and (assetnum = am.assetnum) and (READINGDATE<am.PLUSAANCHORDATE) "+cMonth6+") from ASSETMETER AM where (am.metername='FLTS') and (am.assetnum = a.assetnum) and (am.siteid=a.siteid)), "
//+"(select am.plusaanchorcount+(select COALESCE(sum(delta),0) from METERREADING where (metername='TCT') and (assetnum = am.assetnum) and (READINGDATE>=am.PLUSAANCHORDATE) "+cMonth3+")-(select COALESCE(sum(delta),0) from METERREADING where (metername='TCT') and (assetnum = am.assetnum) and (READINGDATE<am.PLUSAANCHORDATE) "+cMonth6+") from ASSETMETER AM where (am.metername='TCT') and (am.assetnum = a.assetnum) and (am.siteid=a.siteid)), "
//+"(select am.plusaanchorcount+(select COALESCE(sum(delta),0) from METERREADING where (metername='LNGS') and (assetnum = am.assetnum) and (READINGDATE>=am.PLUSAANCHORDATE) "+cMonth3+")-(select COALESCE(sum(delta),0) from METERREADING where (metername='LNGS') and (assetnum = am.assetnum) and (READINGDATE<am.PLUSAANCHORDATE) "+cMonth6+") from ASSETMETER AM where (am.metername='LNGS') and (am.assetnum = a.assetnum) and (am.siteid=a.siteid)), "
+"calclife(a.assetid,a.siteid,'HRS',"+cMonth8+"), "
+"calclife(a.assetid,a.siteid,'FLTS',"+cMonth8+"), "
+"calclife(a.assetid,a.siteid,'TCT',"+cMonth8+"), "
+"calclife(a.assetid,a.siteid,'LNGS',"+cMonth8+"), "

+"null, null, null, null, null, null, null, null, null, null, null, null, null, "
//+"(select am.plusaanchorcount+(select COALESCE(sum(delta),0) from METERREADING where (metername='HRS') and (assetnum = am.assetnum) and (READINGDATE>=am.PLUSAANCHORDATE)) from ASSETMETER AM where (am.metername='HRS') and (am.assetnum = a.assetnum) and (am.siteid=a.siteid)) "
+"calclife(a.assetid,a.siteid,'HRS',CURRENT TIMESTAMP) "
+"FROM asset a WHERE (a.PLUSAREG IS NOT NULL) and (a.status<>'DECOMMISSIONED') ";
if(params["Tail"].value) {sqlText+=" AND ("+MXReportSqlFormat.createParamWhereClause("a.assetnum",params["Tail"])+") "};

sqlText+="UNION ALL SELECT a.assetnum, a.serialnum, null, null, null, null, "
+"null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, "
+"le.serialnum, " 
//+"(select am.plusaanchorcount+(select COALESCE(sum(delta),0) from METERREADING where (metername='HRS') and (assetnum=a.assetnum) AND (READINGDATE>=am.PLUSAANCHORDATE) "+cMonth3+")+(select COALESCE(sum(delta),0) from METERREADING where (metername='HRS') and (assetnum=le.assetnum) and (READINGDATE>=am.PLUSAANCHORDATE) "+cMonth3+")-(select COALESCE(sum(delta),0) from METERREADING where (metername='HRS') and (assetnum=a.assetnum) AND (READINGDATE<am.PLUSAANCHORDATE) "+cMonth6+")-(select COALESCE(sum(delta),0) from METERREADING where (metername='HRS') and (assetnum=le.assetnum) and (READINGDATE<am.PLUSAANCHORDATE) "+cMonth6+") from ASSETMETER am where (am.metername='HRS') and (am.assetnum = le.assetnum) and (am.siteid=a.siteid)), "
//+"(select am.plusaanchorcount+(select COALESCE(sum(delta),0) from METERREADING where (metername='ECYC') and (assetnum=a.assetnum) AND (READINGDATE>=am.PLUSAANCHORDATE) "+cMonth3+")+(select COALESCE(sum(delta),0) from METERREADING where (metername='ECYC') and (assetnum=le.assetnum) and (READINGDATE>=am.PLUSAANCHORDATE) "+cMonth3+")-(select COALESCE(sum(delta),0) from METERREADING where (metername='ECYC') and (assetnum=a.assetnum) AND (READINGDATE<am.PLUSAANCHORDATE) "+cMonth6+")-(select COALESCE(sum(delta),0) from METERREADING where (metername='ECYC') and (assetnum=le.assetnum) and (READINGDATE<am.PLUSAANCHORDATE) "+cMonth6+") from ASSETMETER am where (am.metername='ECYC') and (am.assetnum = le.assetnum) and (am.siteid=a.siteid)), "
+"CALCLIFE(le.assetid, le.siteid, 'HRS',  "+cMonth8+"), "
+"CALCLIFE(le.assetid, le.siteid, 'ECYC',  "+cMonth8+"), "
+"re.serialnum, "
//+"(select am.plusaanchorcount+(select COALESCE(sum(delta),0) from METERREADING where (metername='HRS') and (assetnum=a.assetnum) AND (READINGDATE>=am.PLUSAANCHORDATE) "+cMonth3+")+(select COALESCE(sum(delta),0) from METERREADING where (metername='HRS') and (assetnum=re.assetnum) and (READINGDATE>=am.PLUSAANCHORDATE) "+cMonth3+")-(select COALESCE(sum(delta),0) from METERREADING where (metername='HRS') and (assetnum=a.assetnum) AND (READINGDATE<am.PLUSAANCHORDATE) "+cMonth6+")-(select COALESCE(sum(delta),0) from METERREADING where (metername='HRS') and (assetnum=re.assetnum) and (READINGDATE<am.PLUSAANCHORDATE) "+cMonth6+") from ASSETMETER am where (am.metername='HRS') and (am.assetnum = re.assetnum) and (am.siteid=a.siteid)), "
//+"(select am.plusaanchorcount+(select COALESCE(sum(delta),0) from METERREADING where (metername='ECYC') and (assetnum=a.assetnum) AND (READINGDATE>=am.PLUSAANCHORDATE) "+cMonth3+")+(select COALESCE(sum(delta),0) from METERREADING where (metername='ECYC') and (assetnum=re.assetnum) and (READINGDATE>=am.PLUSAANCHORDATE) "+cMonth3+")-(select COALESCE(sum(delta),0) from METERREADING where (metername='ECYC') and (assetnum=a.assetnum) AND (READINGDATE<am.PLUSAANCHORDATE) "+cMonth6+")-(select COALESCE(sum(delta),0) from METERREADING where (metername='ECYC') and (assetnum=re.assetnum) and (READINGDATE<am.PLUSAANCHORDATE) "+cMonth6+") from ASSETMETER am where (am.metername='ECYC') and (am.assetnum = re.assetnum) and (am.siteid=a.siteid)), "
+"CALCLIFE(re.assetid, re.siteid, 'HRS',  "+cMonth8+"), "
+"CALCLIFE(re.assetid, re.siteid, 'ECYC',  "+cMonth8+"), "
+"null, null, null, "
//+"(select READINGATNEXTWO from pmmeter where (siteid=a.siteid) and (pmnum=(select pmnum from pm where (MASTERPM='024') and (siteid=a.siteid) and (assetnum=le.assetnum)))), "
//+"(select READINGATNEXTWO from pmmeter where (siteid=a.siteid) and (pmnum=(select pmnum from pm where (MASTERPM='025') and (siteid=a.siteid) and (assetnum=le.assetnum)))), "
//+"(select READINGATNEXTWO from pmmeter where (siteid=a.siteid) and (pmnum=(select pmnum from pm where (MASTERPM='024') and (siteid=a.siteid) and (assetnum=re.assetnum)))), "
//+"(select READINGATNEXTWO from pmmeter where (siteid=a.siteid) and (pmnum=(select pmnum from pm where (MASTERPM='025') and (siteid=a.siteid) and (assetnum=re.assetnum)))) "
//+"(select pm.FREQUENCY - COALESCE(pa.DUECOUNTOFFSET,0) - (select COALESCE(sum(delta),0) from METERREADING where (metername='HRS') and (assetnum=a.assetnum) and (siteid=a.siteid) AND (READINGDATE>=pa.ACTIVEDATE)) - (select COALESCE(sum(delta),0) from METERREADING where (metername='HRS') and (assetnum=le.assetnum) and (siteid=a.siteid) AND (READINGDATE>=pa.ACTIVEDATE)) from pmmeter pm, PLUSALFEVENT pa where (pm.siteid=a.siteid) and (pm.pmnum in(select pmnum from pm where (MASTERPM='024') and (siteid=a.siteid) and (assetnum=le.assetnum) and (status='ACTIVE'))) and (pm.PMMETERID=pa.PMMETERID) and (pa.INACTIVECODE IS NULL)), "
//+"(select pm.FREQUENCY - COALESCE(pa.DUECOUNTOFFSET,0) - (select COALESCE(sum(delta),0) from METERREADING where (metername='HRS') and (assetnum=a.assetnum) and (siteid=a.siteid) AND (READINGDATE>=pa.ACTIVEDATE)) - (select COALESCE(sum(delta),0) from METERREADING where (metername='HRS') and (assetnum=le.assetnum) and (siteid=a.siteid) AND (READINGDATE>=pa.ACTIVEDATE)) from pmmeter pm, PLUSALFEVENT pa where (pm.siteid=a.siteid) and (pm.pmnum in(select pmnum from pm where (MASTERPM='025') and (siteid=a.siteid) and (assetnum=le.assetnum) and (status='ACTIVE'))) and (pm.PMMETERID=pa.PMMETERID) and (pa.INACTIVECODE IS NULL)), "
//+"(select pm.FREQUENCY - COALESCE(pa.DUECOUNTOFFSET,0) - (select COALESCE(sum(delta),0) from METERREADING where (metername='HRS') and (assetnum=a.assetnum) and (siteid=a.siteid) AND (READINGDATE>=pa.ACTIVEDATE)) - (select COALESCE(sum(delta),0) from METERREADING where (metername='HRS') and (assetnum=re.assetnum) and (siteid=a.siteid) AND (READINGDATE>=pa.ACTIVEDATE)) from pmmeter pm, PLUSALFEVENT pa where (pm.siteid=a.siteid) and (pm.pmnum in(select pmnum from pm where (MASTERPM='024') and (siteid=a.siteid) and (assetnum=re.assetnum) and (status='ACTIVE'))) and (pm.PMMETERID=pa.PMMETERID) and (pa.INACTIVECODE IS NULL)), "
//+"(select pm.FREQUENCY - COALESCE(pa.DUECOUNTOFFSET,0) - (select COALESCE(sum(delta),0) from METERREADING where (metername='HRS') and (assetnum=a.assetnum) and (siteid=a.siteid) AND (READINGDATE>=pa.ACTIVEDATE)) - (select COALESCE(sum(delta),0) from METERREADING where (metername='HRS') and (assetnum=re.assetnum) and (siteid=a.siteid) AND (READINGDATE>=pa.ACTIVEDATE)) from pmmeter pm, PLUSALFEVENT pa where (pm.siteid=a.siteid) and (pm.pmnum in(select pmnum from pm where (MASTERPM='025') and (siteid=a.siteid) and (assetnum=re.assetnum) and (status='ACTIVE'))) and (pm.PMMETERID=pa.PMMETERID) and (pa.INACTIVECODE IS NULL)), 
+"(select pm.FREQUENCY-COALESCE(pa.DUECOUNTOFFSET,0)+CALCLIFE(le.assetid, le.siteid, 'HRS', pa.ACTIVEDATE)-CALCLIFE(le.assetid, le.siteid, 'HRS', CURRENT TIMESTAMP) from pmmeter pm, PLUSALFEVENT pa where (pm.siteid=a.siteid) and (pm.pmnum in(select pmnum from pm where (MASTERPM='024') and (siteid=le.siteid) and (assetnum=le.assetnum) and (status='ACTIVE'))) and (pm.PMMETERID=pa.PMMETERID) and (pa.INACTIVECODE IS NULL)), " 
+"(select pm.FREQUENCY-COALESCE(pa.DUECOUNTOFFSET,0)+CALCLIFE(le.assetid, le.siteid, 'HRS', pa.ACTIVEDATE)-CALCLIFE(le.assetid, le.siteid, 'HRS', CURRENT TIMESTAMP) from pmmeter pm, PLUSALFEVENT pa where (pm.siteid=a.siteid) and (pm.pmnum in(select pmnum from pm where (MASTERPM='025') and (siteid=le.siteid) and (assetnum=le.assetnum) and (status='ACTIVE'))) and (pm.PMMETERID=pa.PMMETERID) and (pa.INACTIVECODE IS NULL)), " 
+"(select pm.FREQUENCY-COALESCE(pa.DUECOUNTOFFSET,0)+CALCLIFE(re.assetid, re.siteid, 'HRS', pa.ACTIVEDATE)-CALCLIFE(re.assetid, re.siteid, 'HRS', CURRENT TIMESTAMP) from pmmeter pm, PLUSALFEVENT pa where (pm.siteid=a.siteid) and (pm.pmnum in(select pmnum from pm where (MASTERPM='024') and (siteid=re.siteid) and (assetnum=re.assetnum) and (status='ACTIVE'))) and (pm.PMMETERID=pa.PMMETERID) and (pa.INACTIVECODE IS NULL)), " 
+"(select pm.FREQUENCY-COALESCE(pa.DUECOUNTOFFSET,0)+CALCLIFE(re.assetid, re.siteid, 'HRS', pa.ACTIVEDATE)-CALCLIFE(re.assetid, re.siteid, 'HRS', CURRENT TIMESTAMP) from pmmeter pm, PLUSALFEVENT pa where (pm.siteid=a.siteid) and (pm.pmnum in(select pmnum from pm where (MASTERPM='025') and (siteid=re.siteid) and (assetnum=re.assetnum) and (status='ACTIVE'))) and (pm.PMMETERID=pa.PMMETERID) and (pa.INACTIVECODE IS NULL)), " 
+"null "
+"FROM asset a "
+"LEFT OUTER JOIN (SELECT e.ANCESTOR, e.siteid, e.assetnum, e.assetid, e.serialnum FROM asset e, PLUSASAONOFF p2 WHERE (e.plusacatid='52') and (e.status<>'DECOMMISSIONED') and (e.assetid=p2.assetid) and (e.siteid=p2.siteid) and (p2.position like '%L') and (p2.type='N')) le ON (a.ASSETNUM=le.ancestor) and (a.siteid=le.siteid) "
+"LEFT OUTER JOIN (SELECT e.ANCESTOR, e.siteid, e.assetnum, e.assetid, e.serialnum FROM asset e, PLUSASAONOFF p2 WHERE (e.plusacatid='52') and (e.status<>'DECOMMISSIONED') and (e.assetid=p2.assetid) and (e.siteid=p2.siteid) and (p2.position like '%R') and (p2.type='N')) re ON (a.ASSETNUM=re.ancestor) and (a.siteid=re.siteid) "
+"WHERE (a.PLUSAREG IS NOT NULL) and (a.status<>'DECOMMISSIONED') ";
if(params["Tail"].value) {sqlText+=" AND ("+MXReportSqlFormat.createParamWhereClause("a.assetnum",params["Tail"])+") "};

maximoDataSet.setQuery(sqlText);