public Map getReportTableData(final Map targetTableViewMapObj,final List targetTableList,final String tableName,final String filteredSkus, Map scoreCardRollUpSkuMapObj,
final List actualWeeksList,final List forecastWeeksList,final String actualWeeksBuffer,final String weeksForMAxWeekBuffer,final String allWeeksBuffer,
final String forecastweeksBuffer,final Integer planningCycleId,final String nextFilterVariable,final String selectedTypeValue,final String business,
final String whereClauseStr,final Integer week,final Integer year,final Integer range,final Integer firstIndex,final Integer switchForTotalSelectedTypeValues,
final String btbValue,final String targetValue,final String selectedPeriod,final Map targetTablecolorRangeMapObj){
try{
logger.debug("Entered into getReportTableData");
Object object = hibernateTemplate.execute(new HibernateCallback() {
public Object doInHibernate(Session session)
throws HibernateException, SQLException {
String sql = "";
String sql1="";
String orderWeek = PropertiesUtil.getProperty("orderWeek");
String productIdColumn =PropertiesUtil.getProperty("skuListId");
String unitsColumn = PropertiesUtil.getProperty("quantity");
String aspColumn = PropertiesUtil.getProperty("ASP");
String escColumn =PropertiesUtil.getProperty("ESC");
String pmColumn =PropertiesUtil.getProperty("productMargin");
String businessColumn= PropertiesUtil.getProperty("business");
String numberOfForecastsStr= PropertiesUtil.getProperty("numberOfForecasts");
StringBuffer weeksBuffer = new StringBuffer();
int numberOfForecasts=0;
if(!ApplicationUtil.isEmptyOrNull(numberOfForecastsStr)){
numberOfForecasts=Integer.parseInt(numberOfForecastsStr);
}
String numberOfActualsStr= PropertiesUtil.getProperty("numberOfActuals");
int numberOfActuals=0;
if(!ApplicationUtil.isEmptyOrNull(numberOfActualsStr)){
numberOfActuals=Integer.parseInt(numberOfActualsStr);
}
int tableColorRange=0;
String categoryColumn = PropertiesUtil.getProperty(ApplicationConstants.CATEGORY);
try {
List dataList = new ArrayList();
Map catSubcategoryDataMap = new HashMap();
Object[] rowList=null;
List weekList= new ArrayList();
weekList.add(" ");
List monthlyOrQuarterlyList = new ArrayList();
List monthlyOrQuarterlyForecastsList= new ArrayList();
List monthlyOrQuarterlyActualsList =new ArrayList();
monthlyOrQuarterlyForecastsList.add("");
monthlyOrQuarterlyActualsList.add("");
List valuesList= new ArrayList();
List percentageOfTargetsList= new ArrayList();
if(switchForTotalSelectedTypeValues==1 || switchForTotalSelectedTypeValues==3){
if(!ApplicationUtil.isEmptyOrNull(btbValue)){
valuesList.add(selectedTypeValue+" Wt BTB");
} else{
valuesList.add(selectedTypeValue);
}
} else if(switchForTotalSelectedTypeValues==0) {
if(!ApplicationUtil.isEmptyOrNull(btbValue)){
valuesList.add("Total Wt BTB");
} else{
valuesList.add("Total");
}
//valuesList.add("Total");
} else{
valuesList.add("BTB");
}
percentageOfTargetsList.add("% to Target");
String businessTypeCriteria= "";
String businessTypeCriteria1= "";
String businessTypeCriteria2= "";
String businessTypeCriteria3=" ";
String businessTypeCriteria4=" ";
String eolWhereClause=" ";
if(week!=null && year!=null){
eolWhereClause="(sl.eolWeek is null or ("+week+"<=sl.eolWeek and "+year+"<=sl.eolYear)) ";
}else{
eolWhereClause="1=1";
}
if(!ApplicationUtil.isEmptyOrNull(business)){
businessTypeCriteria= "and sm.business = '"+business+"'";
businessTypeCriteria1= businessColumn+" = '"+business+"' and ";
businessTypeCriteria3= " and d."+businessColumn+" = '"+business+"'";
businessTypeCriteria4= " and business = '"+business+"'";
businessTypeCriteria2= " and f.business = '"+business+"'";
}
String filterCondition="select d."+productIdColumn+" from Data d where d."+nextFilterVariable+" in ( "+selectedTypeValue+") "+whereClauseStr;
String sqlSkuFilterCondition ="SELECT distinct prod FROM (select "+productIdColumn+" as prod,"+businessColumn+" as max_business FROM Data data"
+" inner join( SELECT "+productIdColumn+" as prod1, MAX("+orderWeek+") AS max_week, "+businessColumn+" as max_business FROM data "
+" where "+businessTypeCriteria1+orderWeek+" IN ("+weeksForMAxWeekBuffer+") GROUP BY "+productIdColumn+" , "+businessColumn+") p"
+" on data."+productIdColumn+" = p.prod1 and data."+orderWeek+" = p.max_week and data."+businessColumn+" = max_business where "+nextFilterVariable+" in ( "+selectedTypeValue+") "+whereClauseStr+" AND "+orderWeek+" IN ("+actualWeeksBuffer+")) d1"
+" inner join (Sku_List sl) on sl.productId = d1.prod and "+eolWhereClause
+" and sl.id in (select skuListId from Sku_User_Mapping sm where sl.id = sm.skuListId "+businessTypeCriteria+")";
String monthlyOrQuarterly="";
if(!ApplicationUtil.isEmptyOrNull(selectedPeriod) && selectedPeriod.equalsIgnoreCase("Monthly")){
monthlyOrQuarterly="calendarMonth";
}
if(!ApplicationUtil.isEmptyOrNull(selectedPeriod) && selectedPeriod.equalsIgnoreCase("Quarterly")){
monthlyOrQuarterly="fiscalQuarter";
}
if(!ApplicationUtil.isEmptyOrNull(filteredSkus)){
if(tableName.equalsIgnoreCase("ForecastingUnits")){
if(!ApplicationUtil.isEmptyOrNull(selectedPeriod) && !selectedPeriod.equalsIgnoreCase("Weekly")){
sql="select mfc."+monthlyOrQuarterly+",cast(round(sum(totalData.val)) as char(30)),year from ( ";
sql =sql+" SELECT d."+orderWeek+" week, SUM(d."+unitsColumn+") val"
+" FROM Data d where d."+productIdColumn+" in ("+filteredSkus+") "+businessTypeCriteria3+" AND d."+orderWeek+" IN ("+actualWeeksBuffer+") GROUP BY d."+orderWeek;
// Group By should be there here
sql=sql+ " union all ";
sql = sql + "select t.forecastPeriod week, round(sum(t.OriginalForecast)) val from ("
+" SELECT f.*,case when b.overrideValue is null then f.forecastValue else b.overrideValue end as OriginalForecast FROM "
+" forecasting_units f inner join ( "+sqlSkuFilterCondition+" ) p on f.productid = p.prod and f.planningCycleId = "+planningCycleId+businessTypeCriteria2+" and f.forecastPeriod IN ("+forecastweeksBuffer+") "
//+" select * from forecasting_units where planningCycleId = "+planningCycleId+businessTypeCriteria4+" and forecastPeriod IN ("+forecastweeksBuffer+") and productId in ("+filteredSkus+"))f"
+" LEFT JOIN (SELECT a.* FROM override_units_log a JOIN (SELECT forecastingUnitsId, MAX(createdDate) AS mdate FROM override_units_log"
+" GROUP BY forecastingUnitsId) b ON a.forecastingUnitsId = b.forecastingUnitsId AND a.createdDate = b.mdate) b ON f.id = b.forecastingUnitsId) t GROUP BY t.forecastPeriod";
sql=sql+ " ) totalData join master_fiscal_calender mfc on totalData.week=mfc.fiscalWeek group by mfc."+monthlyOrQuarterly+",mfc.year order by mfc.id ";
}else {
sql = "SELECT d."+orderWeek+", SUM(d."+unitsColumn+")"
+" FROM Data d where d."+productIdColumn+" in ("+filteredSkus+") "+businessTypeCriteria3+" AND d."+orderWeek+" IN ("+actualWeeksBuffer+") GROUP BY d."+orderWeek;
sql1 = "select t.forecastPeriod, cast(round(sum(t.OriginalForecast)) as char(30)) from ("
+" SELECT f.*,case when b.overrideValue is null then f.forecastValue else b.overrideValue end as OriginalForecast FROM "
+" forecasting_units f inner join ( "+sqlSkuFilterCondition+" ) p on f.productid = p.prod and f.planningCycleId = "+planningCycleId+businessTypeCriteria2+" and f.forecastPeriod IN ("+forecastweeksBuffer+") "
//+" select * from forecasting_units where planningCycleId = "+planningCycleId+businessTypeCriteria4+" and forecastPeriod IN ("+forecastweeksBuffer+") and productId in ("+filteredSkus+"))f"
+" LEFT JOIN (SELECT a.* FROM override_units_log a JOIN (SELECT forecastingUnitsId, MAX(createdDate) AS mdate FROM override_units_log"
+" GROUP BY forecastingUnitsId) b ON a.forecastingUnitsId = b.forecastingUnitsId AND a.createdDate = b.mdate) b ON f.id = b.forecastingUnitsId) t GROUP BY t.forecastPeriod";
}
} else if (tableName.equalsIgnoreCase("ForecastingASP")){
if(!ApplicationUtil.isEmptyOrNull(selectedPeriod) && !selectedPeriod.equalsIgnoreCase("Weekly")){
sql="select mfc."+monthlyOrQuarterly+",round(sum(totalData.quantity * totalData.asp)/sum(totalData.quantity),2),year from ( ";
sql = sql+"select d."+orderWeek+" week,d."+unitsColumn+" quantity, d."+aspColumn+" asp"
+" FROM Data d where d."+productIdColumn+" in ("+filteredSkus+") "+businessTypeCriteria3+" AND d."+orderWeek+" IN ("+actualWeeksBuffer+") ";
sql=sql+ " union all ";
sql = sql+"select fu.forecastPeriod week , fu.OriginalForecast quantity, fa.OriginalForecast asp from ("
+" SELECT f.*,case when b.overrideValue is null then f.forecastValue else b.overrideValue end as OriginalForecast FROM "
+" forecasting_units f inner join ( "+sqlSkuFilterCondition+" ) p on f.productid = p.prod and f.planningCycleId = "+planningCycleId+businessTypeCriteria2+" and f.forecastPeriod IN ("+forecastweeksBuffer+") "
//+" select * from forecasting_units where planningCycleId = "+planningCycleId+businessTypeCriteria4+" and forecastPeriod IN ("+forecastweeksBuffer+") and productId in ("+filteredSkus+"))f"
+" LEFT JOIN (SELECT a.* FROM override_units_log a JOIN (SELECT forecastingUnitsId, MAX(createdDate) AS mdate FROM override_units_log"
+" GROUP BY forecastingUnitsId) b ON a.forecastingUnitsId = b.forecastingUnitsId AND a.createdDate = b.mdate) b ON f.id = b.forecastingUnitsId"
+") fu inner join ("
+" SELECT f.*, CASE WHEN b.overrideValue IS NULL THEN f.forecastValue ELSE b.overrideValue END AS OriginalForecast"
+" FROM forecasting_asp f inner join ( "+sqlSkuFilterCondition+" ) p on f.productid = p.prod and f.planningCycleId = "+planningCycleId+businessTypeCriteria2+" and f.forecastPeriod IN ("+forecastweeksBuffer+") "
//+" FROM (SELECT * FROM forecasting_asp WHERE planningCycleId = "+planningCycleId+businessTypeCriteria4+" and forecastPeriod IN ("+forecastweeksBuffer+") and productId in ("+filteredSkus+"))f"
+" LEFT JOIN (SELECT a.* FROM override_asp_log a JOIN (SELECT forecastingAspId, MAX(createdDate) AS mdate FROM override_asp_log "
+" GROUP BY forecastingAspId) b ON a.forecastingAspId = b.forecastingAspId AND a.createdDate = b.mdate) b ON f.id = b.forecastingAspId"
+" ) fa on fu.forecastPeriod = fa.forecastPeriod AND fu.productId = fa.productId";
sql=sql+ " ) totalData join master_fiscal_calender mfc on totalData.week=mfc.fiscalWeek group by mfc."+monthlyOrQuarterly+",mfc.year order by mfc.id ";
} else{
sql = "SELECT d."+orderWeek+", round(sum(d."+unitsColumn+" * d."+aspColumn+")/sum(d."+unitsColumn+"),2)"
+" FROM Data d where d."+productIdColumn+" in ("+filteredSkus+") "+businessTypeCriteria3+" AND d."+orderWeek+" IN ("+actualWeeksBuffer+") "
+" GROUP BY d."+orderWeek;
sql1 = "select fu.forecastPeriod , round(sum(fu.OriginalForecast * fa.OriginalForecast)/sum(fu.OriginalForecast),2) from ("
+" SELECT f.*,case when b.overrideValue is null then f.forecastValue else b.overrideValue end as OriginalForecast FROM "
+" forecasting_units f inner join ( "+sqlSkuFilterCondition+" ) p on f.productid = p.prod and f.planningCycleId = "+planningCycleId+businessTypeCriteria2+" and f.forecastPeriod IN ("+forecastweeksBuffer+") "
//+" select * from forecasting_units where planningCycleId = "+planningCycleId+businessTypeCriteria4+" and forecastPeriod IN ("+forecastweeksBuffer+") and productId in ("+filteredSkus+"))f"
+" LEFT JOIN (SELECT a.* FROM override_units_log a JOIN (SELECT forecastingUnitsId, MAX(createdDate) AS mdate FROM override_units_log"
+" GROUP BY forecastingUnitsId) b ON a.forecastingUnitsId = b.forecastingUnitsId AND a.createdDate = b.mdate) b ON f.id = b.forecastingUnitsId"
+") fu inner join ("
+" SELECT f.*, CASE WHEN b.overrideValue IS NULL THEN f.forecastValue ELSE b.overrideValue END AS OriginalForecast"
+" FROM forecasting_asp f inner join ( "+sqlSkuFilterCondition+" ) p on f.productid = p.prod and f.planningCycleId = "+planningCycleId+businessTypeCriteria2+" and f.forecastPeriod IN ("+forecastweeksBuffer+") "
//+" FROM (SELECT * FROM forecasting_asp WHERE planningCycleId = "+planningCycleId+businessTypeCriteria4+" and forecastPeriod IN ("+forecastweeksBuffer+") and productId in ("+filteredSkus+"))f"
+" LEFT JOIN (SELECT a.* FROM override_asp_log a JOIN (SELECT forecastingAspId, MAX(createdDate) AS mdate FROM override_asp_log "
+" GROUP BY forecastingAspId) b ON a.forecastingAspId = b.forecastingAspId AND a.createdDate = b.mdate) b ON f.id = b.forecastingAspId"
+" ) fa on fu.forecastPeriod = fa.forecastPeriod AND fu.productId = fa.productId "
+" GROUP BY fu.forecastPeriod";
}
} else if (tableName.equalsIgnoreCase("ForecastingRevenue")){
if(!ApplicationUtil.isEmptyOrNull(selectedPeriod) && !selectedPeriod.equalsIgnoreCase("Weekly")){
sql="select mfc."+monthlyOrQuarterly+",round(sum(totalData.quantity * totalData.asp),2),year from ( ";
sql =sql+"select d."+orderWeek+" week,d."+unitsColumn+" quantity, d."+aspColumn+" asp "
+" FROM Data d where d."+productIdColumn+" in ("+filteredSkus+") "+businessTypeCriteria3+" AND d."+orderWeek+" IN ("+actualWeeksBuffer+") ";
sql=sql+ " union all ";
sql = sql+"select fu.forecastPeriod week , fu.OriginalForecast quantity, fa.OriginalForecast asp from ("
+" SELECT f.*,case when b.overrideValue is null then f.forecastValue else b.overrideValue end as OriginalForecast FROM "
+" forecasting_units f inner join ( "+sqlSkuFilterCondition+" ) p on f.productid = p.prod and f.planningCycleId = "+planningCycleId+businessTypeCriteria2+" and f.forecastPeriod IN ("+forecastweeksBuffer+") "
//+" select * from forecasting_units where planningCycleId = "+planningCycleId+businessTypeCriteria4+" and forecastPeriod IN ("+forecastweeksBuffer+") and productId in ("+filteredSkus+"))f"
+" LEFT JOIN (SELECT a.* FROM override_units_log a JOIN (SELECT forecastingUnitsId, MAX(createdDate) AS mdate FROM override_units_log"
+" GROUP BY forecastingUnitsId) b ON a.forecastingUnitsId = b.forecastingUnitsId AND a.createdDate = b.mdate) b ON f.id = b.forecastingUnitsId"
+") fu inner join ("
+" SELECT f.*, CASE WHEN b.overrideValue IS NULL THEN f.forecastValue ELSE b.overrideValue END AS OriginalForecast"
+" FROM forecasting_asp f inner join ( "+sqlSkuFilterCondition+" ) p on f.productid = p.prod and f.planningCycleId = "+planningCycleId+businessTypeCriteria2+" and f.forecastPeriod IN ("+forecastweeksBuffer+") "
//+" FROM (SELECT * FROM forecasting_asp WHERE planningCycleId = "+planningCycleId+businessTypeCriteria4+" and forecastPeriod IN ("+forecastweeksBuffer+") and productId in ("+filteredSkus+"))f"
+" LEFT JOIN (SELECT a.* FROM override_asp_log a JOIN (SELECT forecastingAspId, MAX(createdDate) AS mdate FROM override_asp_log "
+" GROUP BY forecastingAspId) b ON a.forecastingAspId = b.forecastingAspId AND a.createdDate = b.mdate) b ON f.id = b.forecastingAspId"
+" ) fa on fu.forecastPeriod = fa.forecastPeriod AND fu.productId = fa.productId";
sql=sql+ " ) totalData join master_fiscal_calender mfc on totalData.week=mfc.fiscalWeek group by mfc."+monthlyOrQuarterly+",mfc.year order by mfc.id ";
} else{
sql = "SELECT d."+orderWeek+", round(sum(d."+unitsColumn+") * (sum(d."+unitsColumn+" * d."+aspColumn+")/sum(d."+unitsColumn+")),2) "
+" FROM Data d where d."+productIdColumn+" in ("+filteredSkus+") "+businessTypeCriteria3+" AND d."+orderWeek+" IN ("+actualWeeksBuffer+") "
+" GROUP BY d."+orderWeek;
sql1 = "select fu.forecastPeriod , round(sum(fu.OriginalForecast * fa.OriginalForecast),2) from ("
+" SELECT f.*,case when b.overrideValue is null then f.forecastValue else b.overrideValue end as OriginalForecast FROM "
+" forecasting_units f inner join ( "+sqlSkuFilterCondition+" ) p on f.productid = p.prod and f.planningCycleId = "+planningCycleId+businessTypeCriteria2+" and f.forecastPeriod IN ("+forecastweeksBuffer+") "
//+" select * from forecasting_units where planningCycleId = "+planningCycleId+businessTypeCriteria4+" and forecastPeriod IN ("+forecastweeksBuffer+") and productId in ("+filteredSkus+"))f"
+" LEFT JOIN (SELECT a.* FROM override_units_log a JOIN (SELECT forecastingUnitsId, MAX(createdDate) AS mdate FROM override_units_log"
+" GROUP BY forecastingUnitsId) b ON a.forecastingUnitsId = b.forecastingUnitsId AND a.createdDate = b.mdate) b ON f.id = b.forecastingUnitsId"
+") fu inner join ("
+" SELECT f.*, CASE WHEN b.overrideValue IS NULL THEN f.forecastValue ELSE b.overrideValue END AS OriginalForecast"
+" FROM forecasting_asp f inner join ( "+sqlSkuFilterCondition+" ) p on f.productid = p.prod and f.planningCycleId = "+planningCycleId+businessTypeCriteria2+" and f.forecastPeriod IN ("+forecastweeksBuffer+") "
//+" FROM (SELECT * FROM forecasting_asp WHERE planningCycleId = "+planningCycleId+businessTypeCriteria4+" and forecastPeriod IN ("+forecastweeksBuffer+") and productId in ("+filteredSkus+"))f"
+" LEFT JOIN (SELECT a.* FROM override_asp_log a JOIN (SELECT forecastingAspId, MAX(createdDate) AS mdate FROM override_asp_log "
+" GROUP BY forecastingAspId) b ON a.forecastingAspId = b.forecastingAspId AND a.createdDate = b.mdate) b ON f.id = b.forecastingAspId"
+" ) fa on fu.forecastPeriod = fa.forecastPeriod AND fu.productId = fa.productId "
+" GROUP BY fu.forecastPeriod";
}
} else if (tableName.equalsIgnoreCase("ForecastingESC")){
if(!ApplicationUtil.isEmptyOrNull(selectedPeriod) && !selectedPeriod.equalsIgnoreCase("Weekly")){
sql="select mfc."+monthlyOrQuarterly+",round(sum(totalData.esc * totalData.quantity)/sum(totalData.quantity),2),year from ( ";
sql = sql+ "select d."+orderWeek+" week,d."+unitsColumn+" quantity, d."+escColumn+" esc "
+" FROM Data d where d."+productIdColumn+" in ("+filteredSkus+") "+businessTypeCriteria3+" AND d."+orderWeek+" IN ("+actualWeeksBuffer+") ";
sql=sql+ " union all ";
sql =sql+ "select fu.forecastPeriod week, fu.OriginalForecast quantity, fe.forecastValue esc from ("
+" SELECT f.*,case when b.overrideValue is null then f.forecastValue else b.overrideValue end as OriginalForecast FROM "
+" forecasting_units f inner join ( "+sqlSkuFilterCondition+" ) p on f.productid = p.prod and f.planningCycleId = "+planningCycleId+businessTypeCriteria2+" and f.forecastPeriod IN ("+forecastweeksBuffer+") "
//+" select * from forecasting_units where planningCycleId = "+planningCycleId+businessTypeCriteria4+" and forecastPeriod IN ("+forecastweeksBuffer+") and productId in ("+filteredSkus+"))f"
+" LEFT JOIN (SELECT a.* FROM override_units_log a JOIN (SELECT forecastingUnitsId, MAX(createdDate) AS mdate FROM override_units_log"
+" GROUP BY forecastingUnitsId) b ON a.forecastingUnitsId = b.forecastingUnitsId AND a.createdDate = b.mdate) b ON f.id = b.forecastingUnitsId"
+" ) fu inner join ("
+" select * from forecasting_esc f inner join ( "+sqlSkuFilterCondition+" ) p on f.productid = p.prod and f.planningCycleId = "+planningCycleId+businessTypeCriteria2+" and f.forecastPeriod IN ("+forecastweeksBuffer+") )fe"
//+" select * from forecasting_esc where planningCycleId = "+planningCycleId+businessTypeCriteria4+" and forecastPeriod IN ("+forecastweeksBuffer+") AND productId IN ("+filteredSkus+")) fe"
+" on fu.forecastPeriod =fe.forecastPeriod and fu.productId =fe.productId";
sql=sql+ " ) totalData join master_fiscal_calender mfc on totalData.week=mfc.fiscalWeek group by mfc."+monthlyOrQuarterly+",mfc.year order by mfc.id ";
} else{
sql = "SELECT d."+orderWeek+",round(sum(d."+escColumn+" * d."+unitsColumn+")/sum(d."+unitsColumn+"),2) "
+"FROM Data d where d."+productIdColumn+" in ("+filteredSkus+") "+businessTypeCriteria3+" AND d."+orderWeek+" IN ("+actualWeeksBuffer+") "
+" GROUP BY d."+orderWeek;
sql1 = "select fu.forecastPeriod , round(sum(fu.OriginalForecast * fe.forecastValue) / sum(fu.OriginalForecast),2) from ("
+" SELECT f.*,case when b.overrideValue is null then f.forecastValue else b.overrideValue end as OriginalForecast FROM "
+" forecasting_units f inner join ( "+sqlSkuFilterCondition+" ) p on f.productid = p.prod and f.planningCycleId = "+planningCycleId+businessTypeCriteria2+" and f.forecastPeriod IN ("+forecastweeksBuffer+") "
//+" select * from forecasting_units where planningCycleId = "+planningCycleId+businessTypeCriteria4+" and forecastPeriod IN ("+forecastweeksBuffer+") and productId in ("+filteredSkus+"))f"
+" LEFT JOIN (SELECT a.* FROM override_units_log a JOIN (SELECT forecastingUnitsId, MAX(createdDate) AS mdate FROM override_units_log"
+" GROUP BY forecastingUnitsId) b ON a.forecastingUnitsId = b.forecastingUnitsId AND a.createdDate = b.mdate) b ON f.id = b.forecastingUnitsId"
+" ) fu inner join ("
+" select * from forecasting_esc f inner join ( "+sqlSkuFilterCondition+" ) p on f.productid = p.prod and f.planningCycleId = "+planningCycleId+businessTypeCriteria2+" and f.forecastPeriod IN ("+forecastweeksBuffer+")"
//+" select * from forecasting_esc where planningCycleId = "+planningCycleId+businessTypeCriteria4+" and forecastPeriod IN ("+forecastweeksBuffer+") AND productId IN ("+filteredSkus+")) fe"
+" ) fe on fu.forecastPeriod =fe.forecastPeriod and fu.productId =fe.productId "
+" group by fu.forecastPeriod";
}
} else {
if(!ApplicationUtil.isEmptyOrNull(selectedPeriod) && !selectedPeriod.equalsIgnoreCase("Weekly")){
sql="select mfc."+monthlyOrQuarterly+",round(((sum(totalData.asp) - sum(totalData.esc))/sum(totalData.asp))*100,2),year from ( ";
sql = sql +"select d."+orderWeek+" week,d."+aspColumn+" asp,d."+escColumn+" esc "
+" FROM Data d where d."+productIdColumn+" in ("+filteredSkus+") "+businessTypeCriteria3+" AND d."+orderWeek+" IN ("+actualWeeksBuffer+") ";
sql=sql+ " union all ";
sql =sql+"select fe.forecastPeriod week,fa.OriginalForecast asp,fe.forecastValue esc from ("
+" select * from forecasting_esc f inner join ( "+sqlSkuFilterCondition+" ) p on f.productid = p.prod and f.planningCycleId = "+planningCycleId+businessTypeCriteria2+" and f.forecastPeriod IN ("+forecastweeksBuffer+") "
//+" select * from forecasting_esc where planningCycleId = "+planningCycleId+businessTypeCriteria4+" and forecastPeriod IN ("+forecastweeksBuffer+") AND productId IN ("+filteredSkus+")"
+" ) fe inner join ("
+" SELECT f.*, CASE WHEN b.overrideValue IS NULL THEN f.forecastValue ELSE b.overrideValue END AS OriginalForecast "
+" FROM forecasting_asp f inner join ( "+sqlSkuFilterCondition+" ) p on f.productid = p.prod and f.planningCycleId = "+planningCycleId+businessTypeCriteria2+" and f.forecastPeriod IN ("+forecastweeksBuffer+") "
//+" FROM (SELECT * FROM forecasting_asp WHERE planningCycleId = "+planningCycleId+businessTypeCriteria4+" and forecastPeriod IN ("+forecastweeksBuffer+") and productId in ("+filteredSkus+"))f"
+" LEFT JOIN (SELECT a.* FROM override_asp_log a JOIN (SELECT forecastingAspId, MAX(createdDate) AS mdate FROM override_asp_log "
+" GROUP BY forecastingAspId) b ON a.forecastingAspId = b.forecastingAspId AND a.createdDate = b.mdate) b ON f.id = b.forecastingAspId"
+" ) fa on fe.forecastPeriod = fa.forecastPeriod AND fe.productId = fa.productId";
sql=sql+ " ) totalData ,master_fiscal_calender mfc where totalData.week=mfc.fiscalWeek group by mfc."+monthlyOrQuarterly+",mfc.year order by mfc.id ";
} else{
sql = "SELECT d."+orderWeek+",round(((sum(d."+aspColumn+") - sum( d."+escColumn+"))/sum(d."+aspColumn+"))*100,2) "
+" FROM Data d where d."+productIdColumn+" in ("+filteredSkus+") "+businessTypeCriteria3+" AND d."+orderWeek+" IN ("+actualWeeksBuffer+") "
+ "GROUP BY d."+orderWeek;
sql1 = "select fe.forecastPeriod ,round(((sum(fa.OriginalForecast)-sum(fe.forecastValue))/sum(fa.OriginalForecast))*100,2) from ("
+" select * from forecasting_esc f inner join ( "+sqlSkuFilterCondition+" ) p on f.productid = p.prod and f.planningCycleId = "+planningCycleId+businessTypeCriteria2+" and f.forecastPeriod IN ("+forecastweeksBuffer+") "
//+" select * from forecasting_esc where planningCycleId = "+planningCycleId+businessTypeCriteria4+" and forecastPeriod IN ("+forecastweeksBuffer+") AND productId IN ("+filteredSkus+")"
+" ) fe inner join ("
+" SELECT f.*, CASE WHEN b.overrideValue IS NULL THEN f.forecastValue ELSE b.overrideValue END AS OriginalForecast"
+" FROM forecasting_asp f inner join ( "+sqlSkuFilterCondition+" ) p on f.productid = p.prod and f.planningCycleId = "+planningCycleId+businessTypeCriteria2+" and f.forecastPeriod IN ("+forecastweeksBuffer+") "
//+" FROM (SELECT * FROM forecasting_asp WHERE planningCycleId = "+planningCycleId+businessTypeCriteria4+" and forecastPeriod IN ("+forecastweeksBuffer+") and productId in ("+filteredSkus+"))f"
+" LEFT JOIN (SELECT a.* FROM override_asp_log a JOIN (SELECT forecastingAspId, MAX(createdDate) AS mdate FROM override_asp_log "
+" GROUP BY forecastingAspId) b ON a.forecastingAspId = b.forecastingAspId AND a.createdDate = b.mdate) b ON f.id = b.forecastingAspId"
+" ) fa on fe.forecastPeriod = fa.forecastPeriod AND fe.productId = fa.productId "
+" GROUP BY fe.forecastPeriod";
}
}
}
//removed weekly condition because its same for weekly and non weekly
List ActualsList=null;
if(!ApplicationUtil.isEmptyOrNull(selectedPeriod) && !ApplicationUtil.isEmptyOrNull(sql)){
SQLQuery q = session.createSQLQuery(sql);
ActualsList = q.list();
}
Map actualsListMap = new HashMap();
if(ActualsList != null){
int size =ActualsList.size();
for(int j=0;j<size;j++){
rowList = (Object[])ActualsList.get(j);
String actualPeriod="";
String actualValue="";
if(rowList[0]!=null){
actualPeriod = rowList[0].toString();
}
if(rowList[1]!=null){
actualValue = rowList[1].toString();
}
if(!ApplicationUtil.isEmptyOrNull(selectedPeriod) && selectedPeriod.equalsIgnoreCase("Monthly")){
String year="";
if(rowList[2]!=null){
year = rowList[2].toString();
}
actualsListMap.put(actualPeriod+"-"+year, actualValue);
}else{
actualsListMap.put(actualPeriod, actualValue);
}
/*if(!ApplicationUtil.isEmptyOrNull(selectedPeriod) && !selectedPeriod.equalsIgnoreCase("Weekly")){
monthlyOrQuarterlyList.add(actualPeriod);
if(!ApplicationUtil.isEmptyOrNull(btbValue)){
if(switchForTotalSelectedTypeValues==0 || switchForTotalSelectedTypeValues==3){
double actual=Double.parseDouble(actualValue);
double btb =Double.parseDouble(btbValue);
valuesList.add(actual+btb+"");
}else{
valuesList.add(btbValue);
}
}else{
valuesList.add(actualValue);
}
}*/
}
}
if(!ApplicationUtil.isEmptyOrNull(selectedPeriod) && !selectedPeriod.equalsIgnoreCase("Weekly")){
monthlyOrQuarterlyList.add("");
List monthlyQuarterlyList = getMonthlyQuarterlyNamesWithYear(actualWeeksBuffer.toString(),selectedPeriod);
int listSize1=monthlyQuarterlyList.size();
tableColorRange=listSize1;
if(!ApplicationUtil.isEmptyOrNull(selectedPeriod) && selectedPeriod.equalsIgnoreCase("Monthly")){
monthlyQuarterlyList=getMonthlyQuarterlyNamesWithYear(allWeeksBuffer.toString(),selectedPeriod);
}else{
monthlyQuarterlyList=getMonthlyQuarterlyNames(allWeeksBuffer.toString(),selectedPeriod);
}
listSize1=monthlyQuarterlyList.size();
for(int l=0;l<listSize1;l++){
String monthQuarter=(String) monthlyQuarterlyList.get(l);
String actualValue="";
if(actualsListMap.containsKey(monthQuarter)){
actualValue= (String) actualsListMap.get(monthQuarter);
} else{
actualValue="0";
}
monthlyOrQuarterlyList.add(monthQuarter);
monthlyOrQuarterlyActualsList.add(monthQuarter);
if(!ApplicationUtil.isEmptyOrNull(btbValue)){
if(switchForTotalSelectedTypeValues==0 || switchForTotalSelectedTypeValues==3){
double actual=Double.parseDouble(actualValue);
double btb =Double.parseDouble(btbValue);
valuesList.add(actual+btb+"");
}else{
valuesList.add(btbValue);
}
}else{
valuesList.add(actualValue);
}
}
}
if(!ApplicationUtil.isEmptyOrNull(selectedPeriod) && !ApplicationUtil.isEmptyOrNull(actualWeeksList) && !selectedPeriod.equalsIgnoreCase("Monthly") && !selectedPeriod.equalsIgnoreCase("Quarterly")){
List actualsWeekList= actualWeeksList;
int size =actualsWeekList.size();
tableColorRange=size;
for(int k=size-1;k>=0;k--){
String actualPeriod=(String) actualsWeekList.get(k);
String actualValue="";
if(actualsListMap.containsKey(actualPeriod)){
actualValue= (String) actualsListMap.get(actualPeriod);
//percentageOfTargetsList.add(" ");
} else{
actualValue="0";
}
weekList.add(actualPeriod);
if(!ApplicationUtil.isEmptyOrNull(btbValue)){
if(switchForTotalSelectedTypeValues==0 || switchForTotalSelectedTypeValues==3){
double actual=Double.parseDouble(actualValue);
double btb =Double.parseDouble(btbValue);
valuesList.add(actual+btb+"");
}else{
valuesList.add(btbValue);
}
}else{
valuesList.add(actualValue);
}
}
}
if(!ApplicationUtil.isEmptyOrNull(selectedPeriod) && !ApplicationUtil.isEmptyOrNull(sql1) && selectedPeriod.equalsIgnoreCase("Weekly")){
SQLQuery query = session.createSQLQuery(sql1);
List ForecastingValuesList = (List)query.list();
if(ForecastingValuesList != null){
int size =ForecastingValuesList.size();
List overrideForecastUnitsList = new ArrayList();
List overrideForecastAspList = new ArrayList();
for(int l=0;l<size;l++){
rowList = (Object[])ForecastingValuesList.get(l);
String forecastPeriod="";
String forecastValue="";
List forecastTable = new ArrayList();
if(rowList[0]!=null){
forecastPeriod = rowList[0].toString();
}
if(rowList[1]!=null){
forecastValue = rowList[1].toString();
}
weekList.add(forecastPeriod);
if(!ApplicationUtil.isEmptyOrNull(btbValue)){
if(switchForTotalSelectedTypeValues==0 || switchForTotalSelectedTypeValues==3){
double forecast=Double.parseDouble(forecastValue);
double btb =Double.parseDouble(btbValue);
valuesList.add(forecast+btb+"");
}else{
valuesList.add(btbValue);
}
}else{
valuesList.add(forecastValue);
}
//percentageOfTargetsList.add(" ");
}
} else {
}
} else if(!ApplicationUtil.isEmptyOrNull(selectedPeriod) && ApplicationUtil.isEmptyOrNull(sql1) && selectedPeriod.equalsIgnoreCase("Weekly")){
int size =forecastWeeksList.size();
for(int l=0;l<size;l++){
String forecastPeriod="";
String forecastValue= "0";
List forecastTable = new ArrayList();
if(forecastWeeksList.get(l)!=null){
forecastPeriod = forecastWeeksList.get(l).toString();
}
weekList.add(forecastPeriod);
if(!ApplicationUtil.isEmptyOrNull(btbValue)){
if(switchForTotalSelectedTypeValues==0 || switchForTotalSelectedTypeValues==3){
double forecast=Double.parseDouble(forecastValue);
double btb =Double.parseDouble(btbValue);
valuesList.add(forecast+btb+"");
}else{
valuesList.add(btbValue);
}
}else{
valuesList.add(forecastValue);
}
}
}
if(!ApplicationUtil.isEmptyOrNull(selectedPeriod) && !selectedPeriod.equalsIgnoreCase("Weekly")){
dataList.add(monthlyOrQuarterlyList);
}else{
dataList.add(weekList);
}
/*if(firstIndex==0 && switchForTotalSelectedTypeValues!=0){
dataList.add(weekList);
}*/
dataList.add(valuesList);
int weekListSize=weekList.size();
List targetData= new ArrayList();
if(selectedPeriod.equalsIgnoreCase("Weekly")){
for(int m=0;m<weekListSize;m++){
if(m!=0){
weeksBuffer.append("'"+weekList.get(m)+"'");
if(m<weekListSize-1){
weeksBuffer.append(",");
}
}
if(!ApplicationUtil.isEmptyOrNull(targetValue) && switchForTotalSelectedTypeValues==2){
if(m==0){
targetData.add("Target");
}else{
targetData.add(targetValue);
}
}
}
}
if(!ApplicationUtil.isEmptyOrNull(targetValue) && switchForTotalSelectedTypeValues==2 && !ApplicationUtil.isEmptyOrNull(selectedPeriod) && !selectedPeriod.equalsIgnoreCase("Weekly")){
int monthlyListSize=monthlyOrQuarterlyList.size();
for(int m=0;m<monthlyListSize;m++){
if(m==0){
targetData.add("Target");
}else{
targetData.add(targetValue);
}
}
}
if(!ApplicationUtil.isEmptyOrNull(selectedPeriod) && !selectedPeriod.equalsIgnoreCase("Weekly") && switchForTotalSelectedTypeValues!=2){
targetData.add("Target");
List targetData1 = getTargetTableData(tableName,selectedTypeValue, business, allWeeksBuffer.toString(),weekList,nextFilterVariable,whereClauseStr,targetValue,selectedPeriod,monthlyOrQuarterlyList );
targetData.addAll(targetData1);
/*List targetData2 = getTargetTableData(tableName,selectedTypeValue, business, forecastWeeksBuffer.toString(),weekList,nextFilterVariable,whereClauseStr,targetValue,selectedPeriod,monthlyOrQuarterlyForecastsList );
targetData.addAll(targetData2);*/
}
if(switchForTotalSelectedTypeValues!=2 && selectedPeriod.equalsIgnoreCase("Weekly")){
targetData.add("Target");
List targetData1 = getTargetTableData(tableName,selectedTypeValue, business, weeksBuffer.toString(),weekList,nextFilterVariable,whereClauseStr,targetValue,selectedPeriod,monthlyOrQuarterlyList );
targetData.addAll(targetData1);
}
dataList.add(targetData);
int listSize=valuesList.size();
int listSize1=targetData.size();
int SizeOfList=0;
if(listSize==listSize1){
SizeOfList=listSize;
} else if(listSize<listSize1){
SizeOfList=listSize;
}
if(listSize>listSize1){
SizeOfList=listSize1;
}
for(int i=1;i<SizeOfList;i++){
String actualValueStr=(String) valuesList.get(i);
String targetValueStr=(String) targetData.get(i);
if(!ApplicationUtil.isEmptyOrNull(targetValueStr) && !targetValueStr.equalsIgnoreCase("--") && !ApplicationUtil.isEmptyOrNull(actualValueStr) ){
double actualValue =Double.parseDouble(actualValueStr);
double targetValue =Double.parseDouble(targetValueStr);
if(targetValue!=0){
double percentageValue=((actualValue/(double)targetValue)*100);
percentageOfTargetsList.add(Math.round(percentageValue)+"%");
}else{
percentageOfTargetsList.add("--");
}
}else{
percentageOfTargetsList.add("--");
}
}
dataList.add(percentageOfTargetsList);
if(switchForTotalSelectedTypeValues!=0){
catSubcategoryDataMap.put(selectedTypeValue, dataList);
} else{
catSubcategoryDataMap.put("total", dataList);
}
targetTableList.add(catSubcategoryDataMap);
targetTablecolorRangeMapObj.put("tableColorRange", tableColorRange);
} catch (HibernateException he) {
throw he;
} catch (ApplicationException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
return targetTableViewMapObj;
}
});
logger.debug("Leaving from getReportTableData");
return (Map) object;
}catch (Exception e) {
logger.error("Exception Occured :" + e + " at Line no :"+e.getStackTrace()[0].getLineNumber()+" in File: "+e.getStackTrace()[0].getFileName());
throw e;
}
}
Be the first to comment
You can use [html][/html], [css][/css], [php][/php] and more to embed the code. Urls are automatically hyperlinked. Line breaks and paragraphs are automatically generated.