Index: src/main/java/com/napa/pulse/dao/impl/ReviewDAOImpl.java =================================================================== diff -u -r5abc4d6ebfe182ceed275d7f21d09900329101d3 -r9ffbafd9a6c2fba110441f7a50b87ff64cf013ee --- src/main/java/com/napa/pulse/dao/impl/ReviewDAOImpl.java (.../ReviewDAOImpl.java) (revision 5abc4d6ebfe182ceed275d7f21d09900329101d3) +++ src/main/java/com/napa/pulse/dao/impl/ReviewDAOImpl.java (.../ReviewDAOImpl.java) (revision 9ffbafd9a6c2fba110441f7a50b87ff64cf013ee) @@ -400,7 +400,6 @@ } else if (("grt0excludeMin").equals(skuTableWidgetData.getSelectedStoreInventoryOption().trim())) { sql += " and NOT NVL(ssp.MIN_STOCKING_QTY, 0) > 0"; } - // System.out.println("its storeInvfilter value" + storeInvFilter); /* On Hand > 0 ----- ssp.on_hand > 0 On Hand > 0 with no Min Max --- ssp.on_hand > 0 and min_stocking_qty & max_stocking_qty = null Min > 0 ---- ssp.min_stocking_qty > 0 @@ -574,9 +573,43 @@ } + // HubSiteCountData + if (skuTableWidgetData.getHubSiteCountMin () != null && skuTableWidgetData.getHubSiteCountMax () != null && skuTableWidgetData.getHubSiteCountMax () >= skuTableWidgetData.getHubSiteCountMin ()) { + sql+= " and ssp.product_id in (select product_id from network_product where network_id in (" + + " SELECT DISTINCT hsn_network_id FROM hub_spoke_network_mapping WHERE hsn_site_id IN ( SELECT ss.site_id " + + " FROM session_site ss" + + " WHERE ss.session_id = "+skuTableWidgetData.getSessionId ()+" )" + + " AND is_active = 1" + + " )" + + " and CNT_MIN_GTR_0 BETWEEN "+skuTableWidgetData.getHubSiteCountMin ()+" and "+skuTableWidgetData.getHubSiteCountMax ()+") "; + } else if (skuTableWidgetData.getHubSiteCountMax () != null && skuTableWidgetData.getHubSiteCountMin () == null) { + sql+= " and ssp.product_id in (select product_id from network_product where network_id in (" + + " SELECT DISTINCT hsn_network_id FROM hub_spoke_network_mapping WHERE hsn_site_id IN ( SELECT ss.site_id " + + " FROM session_site ss" + + " WHERE ss.session_id = "+skuTableWidgetData.getSessionId ()+" )" + + " AND is_active = 1" + + " )" + + " and CNT_MIN_GTR_0 <= "+skuTableWidgetData.getHubSiteCountMax ()+") "; + + } else if (skuTableWidgetData.getHubSiteCountMin () != null && skuTableWidgetData.getHubSiteCountMax () == null) { + + sql+= " and ssp.product_id in (select product_id from network_product where network_id in (" + + " SELECT DISTINCT hsn_network_id FROM hub_spoke_network_mapping WHERE hsn_site_id IN ( SELECT ss.site_id " + + " FROM session_site ss" + + " WHERE ss.session_id = "+skuTableWidgetData.getSessionId ()+" )" + + " AND is_active = 1" + + " )" + + " and CNT_MIN_GTR_0 >= "+skuTableWidgetData.getHubSiteCountMin ()+") "; + + + } + + + + if (skuTableWidgetData.getHighSkuEdit() != null) { sql += " and ssp.PRODUCT_ID IN (select product_id " + " from session_site_product " + " where session_id = " + skuTableWidgetData.getSessionId() @@ -676,13 +709,11 @@ + (("en".equals(user.getLanguage()) || user.getLanguage() == null) ? "P.PRODUCT_DESC, " : "P.PRODUCT_DESC_FR, ") + " P.PER_CAR, P.INTRO_DT, P.TOP_APP, P.JBR_STD_PKG, P.AVG_AGE, P.GROUP_CODE, " - + "P.NATIONAL_VIO, P.NATIONAL_SALES_12, P.NATIONAL_SALES_24, NVL(REVIEWED,0), P.PRODUCT_CUBIC "; - sql += " ) WHERE RN BETWEEN " +startRowNumber+ " AND " +endRowNumber+ " ORDER BY RN "; + + "P.NATIONAL_VIO, P.NATIONAL_SALES_12, P.NATIONAL_SALES_24, NVL(REVIEWED,0), P.PRODUCT_CUBIC )"; + sql += " WHERE RN BETWEEN " +startRowNumber+ " AND " +endRowNumber+ " ORDER BY RN "; -// System.out.println ("SQL for ON HAND "+sql ); long start = System.currentTimeMillis(); skuTableData = jdbcTemplate.queryForList(sql); - LOGGER.debug("getSKUTableData request for sessionId " + skuTableWidgetData.getSessionId() + " took: " + (System.currentTimeMillis() - start)); return skuTableData; @@ -711,8 +742,8 @@ public List> getSKUTableDataCount ( SKUTableWidgetData skuTableWidgetData, User user ) { boolean isFinalized = isSessionFinalized(skuTableWidgetData.getSessionId()); - String sql = " SELECT distinct P.PRODUCT_ID AS \"productId\" " + " FROM " - + (isFinalized ? " SESSION_SITE_PRODUCT_FINALIZED " : " SESSION_SITE_PRODUCT ") + " SSP, PRODUCT P "; + String sql = " SELECT distinct P.PRODUCT_ID AS \"productId\" " + " FROM ( select * from" + + (isFinalized ? " SESSION_SITE_PRODUCT_FINALIZED " : " SESSION_SITE_PRODUCT ") + " where session_id = "+skuTableWidgetData.getSessionId ()+" ) SSP, PRODUCT P "; // lost sales filter if (skuTableWidgetData.isSoldNotStocked()) { @@ -761,7 +792,7 @@ sql += ", SESSION_SITE_FLEET_PRODUCTS_V flt "; } - sql += " WHERE SSP.PRODUCT_ID=p.PRODUCT_ID and ssp.SESSION_ID = " + skuTableWidgetData.getSessionId() + " and ssp.site_id in (select site_id from session_site where session_id = " + skuTableWidgetData.getSessionId() + " ) "; + sql += " WHERE SSP.PRODUCT_ID=p.PRODUCT_ID "; // New Parts filter if (skuTableWidgetData.isProspectParts()) { @@ -1054,7 +1085,41 @@ } + // HubSiteCountData + if (skuTableWidgetData.getHubSiteCountMin () != null && skuTableWidgetData.getHubSiteCountMax () != null && skuTableWidgetData.getHubSiteCountMax () >= skuTableWidgetData.getHubSiteCountMin ()) { + + sql+= " and ssp.product_id in (select product_id from network_product where network_id in (" + + " SELECT DISTINCT hsn_network_id FROM hub_spoke_network_mapping WHERE hsn_site_id IN ( SELECT ss.site_id " + + " FROM session_site ss" + + " WHERE ss.session_id = "+skuTableWidgetData.getSessionId ()+" )" + + " AND is_active = 1" + + " )" + + " and CNT_MIN_GTR_0 BETWEEN "+skuTableWidgetData.getHubSiteCountMin ()+" and "+skuTableWidgetData.getHubSiteCountMax ()+") "; + + } else if (skuTableWidgetData.getHubSiteCountMax () != null && skuTableWidgetData.getHubSiteCountMin () == null) { + sql+= " and ssp.product_id in (select product_id from network_product where network_id in (" + + " SELECT DISTINCT hsn_network_id FROM hub_spoke_network_mapping WHERE hsn_site_id IN ( SELECT ss.site_id " + + " FROM session_site ss" + + " WHERE ss.session_id = "+skuTableWidgetData.getSessionId ()+" )" + + " AND is_active = 1" + + " )" + + " and CNT_MIN_GTR_0 <= "+skuTableWidgetData.getHubSiteCountMax ()+") "; + + + } else if (skuTableWidgetData.getHubSiteCountMin () != null && skuTableWidgetData.getHubSiteCountMax () == null) { + + sql+= " and ssp.product_id in (select product_id from network_product where network_id in (" + + " SELECT DISTINCT hsn_network_id FROM hub_spoke_network_mapping WHERE hsn_site_id IN ( SELECT ss.site_id " + + " FROM session_site ss" + + " WHERE ss.session_id = "+skuTableWidgetData.getSessionId ()+" )" + + " AND is_active = 1" + + " )" + + " and CNT_MIN_GTR_0 >= "+skuTableWidgetData.getHubSiteCountMin ()+") "; + + + } + // 24 and Out filter if (skuTableWidgetData.isTwentyFourAndOut()) { sql += " and ssp.ON_HAND>0 and ssp.RECOMMENDED_RETURN_QTY>0 and ssp.MIN_STOCKING_QTY>0 and ssp.INITIAL_STOCKING_DT is not null and ssp.class != 'O' "; @@ -1227,7 +1292,6 @@ long start = System.currentTimeMillis(); List> productIds; - productIds = jdbcTemplate.queryForList(sql); LOGGER.debug("getSKUTableDataCount request for sessionId " + skuTableWidgetData.getSessionId() + " took: " @@ -1241,7 +1305,6 @@ Map productIdMap = new HashMap<>(); productIdMap.put("allProductIds", productIds.stream().map(x -> Integer.parseInt(x.get("productId").toString())).collect(Collectors.toList())); result.add(productIdMap); - return result; } @@ -2714,7 +2777,7 @@ + " nvl(hub.sim_hub_part_ct,0) as \"hubSimPartsCount\", SSP.DECLINE_ORDER as \"declineOrder\", SSP.DECLINE_RETURN as \"declineReturn\", " + " TO_CHAR(CAST(ssp.last_sale_dt AS TIMESTAMP WITH TIME ZONE), 'YYYY-MM-DD\"T\"HH24:MI:SS.FF3TZHTZM') as \"lastSaleDate\", " + " REGEXP_REPLACE(ssp.BUSINESS_RULES_APPLIED, '(.)(.)(.)', '\\1,\\2,\\3') as \"ruleInfo\", " - + " (CASE WHEN REASON_ID = 8 THEN 1 ELSE 0 END) as \"isNewNumber\",dcWideSales12, dcWideSales24 , hubDemand12 as \"hubDemand12\", hubDemand24 as \"hubDemand24\" " + " FROM " + + " (CASE WHEN REASON_ID = 8 THEN 1 ELSE 0 END) as \"isNewNumber\",dcWideSales12, dcWideSales24 , hubDemand12 as \"hubDemand12\", hubDemand24 as \"hubDemand24\" ,hub_min_gtr_0 as \"hub_min_gtr_0\"" + " FROM " + (isFinalized ? " SESSION_SITE_PRODUCT_FINALIZED " : " SESSION_SITE_PRODUCT ") + " SSP INNER JOIN PRODUCT P ON SSP.PRODUCT_ID = P.PRODUCT_ID " + " INNER JOIN SITE S ON S.SITE_ID = SSP.SITE_ID " @@ -2765,7 +2828,7 @@ + " on ssp.session_id=hub.session_id and ssp.SITE_ID=hub.SITE_ID and SSP.product_id=hub.product_id " + " left join ( SELECT DC_SAL.site_id ,DC_SAL.product_id ,DC_SAL.dcSales12 ,DC_SAL.dcSales24 ,nullif(SSRN.SALES_LAST_12_MONTHS, 0) AS dcWideSales12 ,nullif(SSRN.SALES_LAST_24_MONTHS, 0) AS dcWideSales24, " - + " nullif(hdrn.DEMAND_12, 0) AS hubDemand12 ,nullif(hdrn.DEMAND_24, 0) AS hubDemand24 FROM " + + + " nullif(hdrn.DEMAND_12, 0) AS hubDemand12 ,nullif(hdrn.DEMAND_24, 0) AS hubDemand24 , NP.CNT_MIN_GTR_0 AS hub_min_gtr_0 FROM " + "(SELECT /*+ INDEX (sp SITE_PRODUCT_PRODUCT_IDX) */ ss.site_id, sp.product_id, dc_id , nullif(sp.dc_sales_last_12_months,0) as dcSales12, nullif(sp.dc_sales_last_24_months,0) as dcSales24 ,dc.HSN_NETWORK_ID" + " FROM session_site ss " + " JOIN ( " @@ -2780,7 +2843,8 @@ + " ) dc ON ss.site_id= dc.site_id " + " JOIN site_product sp ON (sp.site_id = dc.dc_id) " + " WHERE session_id = :sessionId) DC_SAL LEFT OUTER JOIN STORE_SALES_ROLLUP_NEW SSRN ON DC_SAL.DC_ID=SSRN.DC_SITE_ID AND DC_SAL.PRODUCT_ID=SSRN.PRODUCT_ID " - +" LEFT OUTER JOIN HSN_DEMAND_ROLLUP HDRN on DC_SAL.HSN_NETWORK_ID = HDRN.HSN_NETWORK_ID AND DC_SAL.PRODUCT_ID =HDRN.PRODUCT_ID) " + +" LEFT OUTER JOIN HSN_DEMAND_ROLLUP HDRN on DC_SAL.HSN_NETWORK_ID = HDRN.HSN_NETWORK_ID AND DC_SAL.PRODUCT_ID =HDRN.PRODUCT_ID" + +" LEFT OUTER JOIN NETWORK_PRODUCT NP ON DC_SAL.HSN_NETWORK_ID = NP.NETWORK_ID AND DC_SAL.PRODUCT_ID = NP.PRODUCT_ID ) " +" dc_sp on ssp.product_id = dc_sp.product_id and ssp.site_id = dc_sp.site_id " + " WHERE SSP.SESSION_ID = :sessionId and ssp.product_id = :productId " @@ -2837,7 +2901,7 @@ + " (case when fh.isFleet is not null then 1 else null end) as \"isFleetSku\", nvl(sim.sim_part_ct,0) as \"siteSimPartsCount\", " + " nvl(hub.sim_hub_part_ct,0) as \"hubSimPartsCount\",0 as \"declineOrder\", 0 as \"declineReturn\", " + " TO_CHAR(CAST(last_sale_dt AS TIMESTAMP WITH TIME ZONE), 'YYYY-MM-DD\"T\"HH24:MI:SS.FF3TZHTZM') as \"lastSaleDate\", " - + " REGEXP_REPLACE(BUSINESS_RULES_APPLIED, '(.)(.)(.)', '\\1,\\2,\\3') as \"ruleInfo\", 0 as \"isNewNumber\" , NULL as dcWideSales12, NULL as dcWideSales24 , NULL as \"hubDemand12\", NULL as \"hubDemand24\" " + + " REGEXP_REPLACE(BUSINESS_RULES_APPLIED, '(.)(.)(.)', '\\1,\\2,\\3') as \"ruleInfo\", 0 as \"isNewNumber\" , NULL as dcWideSales12, NULL as dcWideSales24 , NULL as \"hubDemand12\", NULL as \"hubDemand24\" , 0 as \"hub_min_gtr_0\"" + " FROM (select site_id, product_id, level4_parent_id, SOLD_LAST_12_MONTHS, SOLD_LAST_24_MONTHS, LOST_SALES_12_MONTHS, LOST_SALES_24_MONTHS, DC_SALES_LAST_12_MONTHS, DC_SALES_LAST_24_MONTHS, " + " ON_HAND, MIN_STOCKING_QTY, MAX_STOCKING_QTY, INITIAL_STOCK_AMOUNT, VIO, PCT_MARKET, INITIAL_STOCKING_DT, OCCURANCE_12_MONTHS, OCCURANCE_24_MONTHS, DISPLAY_QTY, KEEP_FLAG, CLASS, " + " CLUSTER_PCT_SALE, TRANSFER_12_MONTHS, TRANSFER_24_MONTHS, SUPERSEDE_12_MONTHS, SUPERSEDE_24_MONTHS, PEER_SALES_12, PTS, block_order, 0 as DECLINE_ORDER, 0 as DECLINE_RETURN, " @@ -2917,7 +2981,6 @@ parameters.addValue("siteId", siteId); } siteSKUTableData = namedParameterJdbcTemplate.queryForList(siteSKUTableDataSQL, parameters); - } catch (Exception e) { LOGGER.error("Exception in Site/SKU Details: " + sessionId); LOGGER.error("Exception message is: " + e.getMessage());