package com.napa.pulse.dao.impl; import com.napa.pulse.dao.interfaces.HubSpokeNetworkDAO; import com.napa.pulse.dao.interfaces.ParametersDAO; import com.napa.pulse.dao.interfaces.ProductGroupDAO; import com.napa.pulse.dto.*; import com.napa.pulse.entity.pulseui.HierarchyItem; import com.napa.pulse.entity.security.User; import com.napa.pulse.exception.PulseException; import com.napa.pulse.rowmappers.StructVarrayRowMapper; import com.napa.pulse.utils.HierarchyUtils; import com.napa.pulse.utils.ROLE; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.support.rowset.SqlRowSet; import org.springframework.stereotype.Repository; import java.sql.PreparedStatement; import java.text.DecimalFormat; import java.util.*; import java.util.stream.Collectors; @Repository public class HubSpokeNetworkDAOImpl implements HubSpokeNetworkDAO { private static final Logger LOGGER = LoggerFactory.getLogger ( HubSpokeNetworkDAOImpl.class ); @Autowired private JdbcTemplate jdbcTemplate; @Autowired ParametersDAO parametersDAO; @Autowired private ReviewDAOImpl reviewDAO; @Autowired ProductGroupDAO productGroupDAO; @Override public Map> createNewProduct ( String productName , Integer hubId , Integer networkId , Integer userId , Integer roleId ) { Map> map = new HashMap<> ( ); List list = new ArrayList<> ( ); try { Long hsn_product_map_id = jdbcTemplate.queryForObject ( "SELECT HS_NETWORK_PRODUCT_MAP_SEQ.NEXTVAL FROM DUAL" , Long.class ); String sql = "INSERT INTO HS_NETWORK_PRODUCT_MAP (HSN_PRODUCT_MAP_ID, HSN_PRODUCT_MAP_NAME, NETWORK_HUB_ID, IS_ACTIVE," + " CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE) VALUES(?,?,?,?,?,?,?,?) "; jdbcTemplate.update ( sql , hsn_product_map_id , productName , hubId , 1 , userId , java.sql.Date.valueOf ( java.time.LocalDate.now ( ) ) , userId , java.sql.Date.valueOf ( java.time.LocalDate.now ( ) ) ); list = getNetworkById ( networkId , null ); map.put ( hsn_product_map_id , list ); } catch ( Exception e ) { LOGGER.error ( "Exception in CreateNewProduct for userId: " + userId + "-Product Name-" + productName ); LOGGER.error ( "Exception message is: {}" , e.getMessage ( ) ); } return map; } @Override public HubSpokeNetworkActionDTO createNewNetwork ( HubSpokeNetworkDTO hubSpokeNetworkDTO , Integer userId ) { HubSpokeNetworkActionDTO dto = new HubSpokeNetworkActionDTO ( ); try { Long newNetworkId = jdbcTemplate.queryForObject ( "SELECT HS_NETWORK_SEQ.NEXTVAL FROM DUAL" , Long.class ); int row = jdbcTemplate.update ( "INSERT INTO HUB_SPOKE_NETWORK (HS_NETWORK_ID, HS_NETWORK_NAME, HS_NETWORK_DESC, CREATED_BY, CREATED_DATE, IS_ACTIVE, HS_NETWORK_TYPE_ID) VALUES(?,?,?,?,?,?,?) " , newNetworkId , hubSpokeNetworkDTO.getNetworkName ( ) , hubSpokeNetworkDTO.getNetworkName ( ) , userId , java.sql.Date.valueOf ( java.time.LocalDate.now ( ) ) , 1 , hubSpokeNetworkDTO.getNetworkType ( ) ); dto.setNetworkName ( hubSpokeNetworkDTO.getNetworkName ( ) ); if ( row > 0 ) { dto.setSuccess ( true ); LOGGER.info ( "CreateNewNetworkDAO Succeeded. 1 Map created with id : " + newNetworkId ); } else { dto.setSuccess ( false ); LOGGER.error ( "Create New Network Failed With Network Name=" + hubSpokeNetworkDTO.getNetworkName ( ) ); } } catch ( Exception ex ) { LOGGER.error ( "Exception in HubSpokeNetworkDAO for userId: " + userId + "-Network Name-" + hubSpokeNetworkDTO.getNetworkName ( ) ); LOGGER.error ( "Exception message is: " + ex.getMessage ( ) ); LOGGER.error ( "Exception stacktrace is: " + ex.getStackTrace ( ) ); } return dto; } public ActionResult updateNetwork ( HubSpokeCreateRequestDto createRequestDto , Integer userId ) { ActionResult result = new ActionResult ( ); String inSql = String.join ( "," , Collections.nCopies ( createRequestDto.getSites ( ).size ( ) , "?" ) ); try { final Integer networkId = jdbcTemplate.queryForObject ( "select HS_NETWORK_ID from HUB_SPOKE_NETWORK WHERE HS_NETWORK_NAME=? and CREATED_BY=?" , new Object[]{createRequestDto.getNetworkName ( ) , userId} , Integer.class ); /*For Inserting Unique Item Count Original For First Time When SiteIds are Inserted in Network */ if ( createRequestDto.getFlagUICOriginal ( ) ) { SqlRowSet rsCurrent = jdbcTemplate.queryForRowSet ( String.format ( "SELECT COUNT(DISTINCT SP.PRODUCT_ID) \"originalUIC\" ,SUM(NVL(min_stocking_qty,0) * NVL(price_wt_core,0)) \"originalMin\" , SUM(NVL(SOLD_LAST_12_MONTHS,0) * NVL(price_wt_core,0)) \"originalCOGS\", SUM(NVL(ON_HAND,0) * NVL(price_wt_core,0)) \"originalOnHand\" FROM SITE_PRODUCT SP WHERE SP.SITE_ID IN " + "(%s) and NVL(sp.MIN_STOCKING_QTY,0) > 0" , inSql ) , createRequestDto.getSites ( ).toArray ( ) ); Integer UIC = null; Float min = null; Float COGS = null; Float onHand = null; if ( rsCurrent.next ( ) ) { UIC = rsCurrent.getInt ( "originalUIC" ); min = rsCurrent.getFloat ( "originalMin" ); COGS = rsCurrent.getFloat ( "originalCOGS" ); onHand = rsCurrent.getFloat ( "originalOnHand" ); } Float originalTurnover = ( min != null && COGS != null ) ? ( COGS ) / ( min ) : 0; originalTurnover = Float.isNaN ( originalTurnover ) ? 0 : originalTurnover; jdbcTemplate.update ( "UPDATE HUB_SPOKE_NETWORK SET UNIQUE_ITEM_COUNT_ORIG=? ,MIN_GT_ZERO_ORIG=? ,COGS_ORIG=?,TURNOVER_ORIG=?,ON_HAND_ORIG=? WHERE HS_NETWORK_ID = ? AND IS_ACTIVE = 1" , UIC , min , COGS , originalTurnover , onHand , networkId ); /*Inserting Sites For First Time In The Network*/ insertSitesInNetwork ( createRequestDto.getSites ( ) , userId , networkId ); } else { /*Updating The Sites In The Network-->Removing Sites and Inserting New Sites As Well*/ List siteIdsList = createRequestDto.getSites ( ); /*Removing Sites From Network*/ String siteIds = siteIdsList.stream ( ).map ( String :: valueOf ).collect ( Collectors.joining ( "," ) ); SqlRowSet sitesToRemove = jdbcTemplate.queryForRowSet ( "SELECT HSN_SITE_ID \"hsnSiteId\" FROM HUB_SPOKE_NETWORK_MAPPING WHERE HSN_SITE_ID NOT IN (" + siteIds + ") " + "AND HSN_NETWORK_ID=? AND IS_ACTIVE=1" , networkId ); while (sitesToRemove.next ( )) { Integer siteId = sitesToRemove.getInt ( "hsnSiteId" ); deleteSiteFromNetwork ( siteId , networkId , userId ); } /*Updating New Sites Inside Network*/ SqlRowSet sitesPresent = jdbcTemplate.queryForRowSet ( "SELECT HSN_SITE_ID \"hsnSiteId\" FROM HUB_SPOKE_NETWORK_MAPPING WHERE HSN_SITE_ID IN (" + siteIds + ") " + "AND HSN_NETWORK_ID=? AND IS_ACTIVE=1" , networkId ); while (sitesPresent.next ( )) { Integer finalSiteId = sitesPresent.getInt ( "hsnSiteId" ); siteIdsList.removeIf ( id -> ( id.equals ( finalSiteId ) ) ); } insertSitesInNetwork ( siteIdsList , userId , networkId ); } result.setSuccess ( true ); } catch ( Exception ex ) { LOGGER.error ( "Exception in updateHubSpokeNetwork for userId: " + userId + "-Network Name-" + createRequestDto.getNetworkName ( ) ); LOGGER.error ( "Exception message is: " + ex.getMessage ( ) ); LOGGER.error ( "Exception stacktrace is: " + ex.getStackTrace ( ) ); //result.setMessage ( "Cannot Update Other's User Data" ); throw new RuntimeException ( ex.getMessage ( ) ); } return result; } @Override public List getNetworkIdHavingSites ( String selectedSites ) { List networkIds = new ArrayList<> ( ); try { if ( selectedSites == null || selectedSites.equals ( "" ) ) return networkIds; String sql = "select distinct hsnm.HSN_NETWORK_ID from HUB_SPOKE_NETWORK hs, HUB_SPOKE_NETWORK_MAPPING hsnm, PULSE_USER pu " + " where hs.HS_NETWORK_ID = hsnm.HSN_NETWORK_ID and hsnm.IS_ACTIVE = 1 and hs.is_active = 1 " + " and hsnm.HSN_SITE_ID in (" + selectedSites + ") " + " and CASE WHEN HS.MODIFIED_BY IS NULL THEN HS.CREATED_BY ELSE HS.MODIFIED_BY END = pu.user_id " + " order by hsnm.HSN_NETWORK_ID"; networkIds = jdbcTemplate.queryForList ( sql , Integer.class ); return networkIds; } catch ( Exception e ) { LOGGER.error ( "Error in getNetworkIdHavingSites()" ); LOGGER.error ( e.getMessage ( ) ); return networkIds; } } public List getNetworkIdHavingNoSites ( ) { List networkIds = new ArrayList<> ( ); try { String sql = "select distinct hs.HS_NETWORK_ID from HUB_SPOKE_NETWORK hs, PULSE_USER pu " + " where hs.is_active = 1 and CASE WHEN HS.MODIFIED_BY IS NULL THEN HS.CREATED_BY ELSE HS.MODIFIED_BY END = pu.USER_ID" + " and hs.HS_NETWORK_ID not in " + " ( " + "select distinct hs.HS_NETWORK_ID from HUB_SPOKE_NETWORK hs, HUB_SPOKE_NETWORK_MAPPING hsnm, PULSE_USER pu " + " where hs.HS_NETWORK_ID = hsnm.HSN_NETWORK_ID and hsnm.IS_ACTIVE = 1 and hs.is_active = 1 " + "and CASE WHEN HS.MODIFIED_BY IS NULL THEN HS.CREATED_BY ELSE HS.MODIFIED_BY END = pu.USER_ID " + " ) "; networkIds = jdbcTemplate.queryForList ( sql , Integer.class ); return networkIds; } catch ( Exception e ) { LOGGER.error ( "Error in getNetworkIdHavingNoSites()" ); LOGGER.error ( e.getMessage ( ) ); return networkIds; } } @Override public String getActiveNetworkIdString ( HubSpokeWidgetData hubSpokeWidgetData ) { StringJoiner sj = new StringJoiner ( "," ); try { TreeSet set = new TreeSet<> ( ); if ( hubSpokeWidgetData.getSelectedHSFilterLoad ( ).equals ( "showSelectedSitesNetwork" ) ) { set.addAll ( getNetworkIdHavingSites ( hubSpokeWidgetData.getSelectedSites ( ) ) ); set.addAll ( getNetworkIdHavingNoSites ( ) ); } for (Integer networkId : set) { sj.add ( String.valueOf ( networkId ) ); } return sj.length ( ) == 0 ? null : sj.toString ( ); } catch ( Exception e ) { LOGGER.error ( "Error in getActiveNetworkIdString()" ); LOGGER.error ( e.getMessage ( ) ); return null; } } @Override public String getNetworkQueryByNetworkId ( Integer networkId ) { return "select hs.hs_network_id \"networkId\", hs.hs_network_name \"networkName\", hs.hs_network_type_id as \"networkType\", " + " (case when hs.hs_network_type_id = 1 then 'HS-DT' when hs.hs_network_type_id = 2 then 'HS-SP' else 'HS-DT' end) as \"networkTypeLabel\", " + " PU.first_name || ' ' || PU.last_name \"netCreatedBy\", CASE WHEN HS.MODIFIED_DATE IS NULL THEN HS.CREATED_DATE ELSE HS.MODIFIED_DATE END \"createdDate\", " + "(case when hs.created_date <= '24-AUG-22' then 1 ELSE 0 END ) as \"redFlag\"," + "hs.unique_item_count_orig \"originalUIC\" ,hs.MIN_GT_ZERO_ORIG \"originalMin\"" + ",hs.COGS_ORIG \"originalCOGS\",hs.TURNOVER_ORIG \"originalTurnover\"," + "hs.ON_HAND_ORIG \"originalOnHand\" from HUB_SPOKE_NETWORK HS " + "INNER JOIN PULSE_USER PU ON PU.USER_ID=CASE WHEN HS.MODIFIED_BY IS NULL THEN HS.CREATED_BY " + "ELSE HS.MODIFIED_BY END WHERE HS.HS_NETWORK_ID =" + networkId + " AND HS.IS_ACTIVE=1"; } @Override public String getNetworkQueryByRoleAndSelectedSites ( HubSpokeWidgetData hubSpokeWidgetData ) { String networkCondition = " "; String netIds = null; if ( hubSpokeWidgetData.getSelectedHSFilterLoad ( ).equals ( "showSelectedSitesNetwork" ) ) netIds = getActiveNetworkIdString ( hubSpokeWidgetData ); String sortingCondition = " order by \"" + hubSpokeWidgetData.getSortByColumn ( ) + "\" " + hubSpokeWidgetData.getSortOrder ( ) + " "; if ( "desc".equalsIgnoreCase ( hubSpokeWidgetData.getSortOrder ( ) ) ) sortingCondition += " nulls last "; if ( hubSpokeWidgetData.getUser ( ).getRoleId ( ) == ROLE.ADMIN.getNumVal ( ) ) { networkCondition = ( ! hubSpokeWidgetData.getSelectedHSFilterLoad ( ).equals ( "showAllHSNetwork" ) ) ? " and hs.hs_network_id in (" + netIds + ") " : " "; return "select hs.hs_network_id \"networkId\", hs.hs_network_name \"networkName\",hs.hs_network_type_id as \"networkType\", " + " (case when hs.hs_network_type_id = 1 then 'HS-DT' when hs.hs_network_type_id = 2 then 'HS-SP' else 'HS-DT' end) as \"networkTypeLabel\", " + " PU.first_name || ' ' || PU.last_name \"netCreatedBy\", CASE WHEN HS.MODIFIED_DATE IS NULL THEN HS.CREATED_DATE ELSE HS.MODIFIED_DATE END \"createdDate\", " + "(case when hs.created_date <= '24-AUG-22' then 1 ELSE 0 END) as \"redFlag\"," + " hs.unique_item_count_orig \"originalUIC\" ,hs.MIN_GT_ZERO_ORIG \"originalMin\",hs.COGS_ORIG \"originalCOGS\",hs.TURNOVER_ORIG \"originalTurnover\",hs.ON_HAND_ORIG \"originalOnHand\" " + " from PULSE_USER PU,HUB_SPOKE_NETWORK HS where CASE WHEN HS.MODIFIED_BY IS NULL THEN HS.CREATED_BY ELSE HS.MODIFIED_BY END = PU.user_id and hs.is_active=1 " + networkCondition + sortingCondition; } networkCondition = ( ! hubSpokeWidgetData.getSelectedHSFilterLoad ( ).equals ( "showAllHSNetwork" ) ) ? " where tbl.HS_NETWORK_ID in (" + netIds + ") " : " "; return "select hs.hs_network_id \"networkId\", hs.hs_network_name \"networkName\",hs.hs_network_type_id as \"networkType\", " + " (case when hs.hs_network_type_id = 1 then 'HS-DT' when hs.hs_network_type_id = 2 then 'HS-SP' else 'HS-DT' end) as \"networkTypeLabel\", " + " PU.first_name || ' ' || PU.last_name \"netCreatedBy\", CASE WHEN HS.MODIFIED_DATE IS NULL THEN HS.CREATED_DATE ELSE HS.MODIFIED_DATE END \"createdDate\", " + "(case when hs.created_date <= '24-AUG-22' then 1 ELSE 0 END) as \"redFlag\"," + "hs.unique_item_count_orig \"originalUIC\" ,hs.MIN_GT_ZERO_ORIG \"originalMin\",hs.COGS_ORIG \"originalCOGS\",hs.TURNOVER_ORIG \"originalTurnover\",hs.ON_HAND_ORIG \"originalOnHand\" " + " from (SELECT tbl.* from (SELECT * FROM hub_spoke_network hsn WHERE is_active = 1 AND NOT EXISTS (SELECT 1 FROM (SELECT hsn_network_id FROM " + "hub_spoke_network_mapping hsm WHERE hsm.is_active = 1 AND NOT EXISTS (SELECT access_id FROM user_site_access usa WHERE usa.access_id = hsm.hsn_site_id AND " + "usa.user_id = " + hubSpokeWidgetData.getUser ( ).getUserId ( ) + ")) a WHERE hsn.hs_network_id = a.hsn_network_id) AND EXISTS (SELECT 1 FROM hub_spoke_network_mapping hsm WHERE hsn.hs_network_id = hsm.hsn_network_id) " + "UNION SELECT * FROM hub_spoke_network hsn WHERE created_by = " + hubSpokeWidgetData.getUser ( ).getUserId ( ) + " AND is_active = 1 ) tbl " + networkCondition + " ) hs " + "left join PULSE_USER pu on CASE WHEN HS.MODIFIED_BY IS NULL THEN HS.CREATED_BY ELSE HS.MODIFIED_BY END = pu.USER_ID " + sortingCondition; } @Override public ActionResult updateReturnToHub ( ReturntoHubDTO dto ) { ActionResult result = new ActionResult ( ); int row = 0; try { if ( dto.getReturnToHubValue ( ) != null ) { row = jdbcTemplate.update ( connection -> { PreparedStatement ps = connection.prepareStatement ( "update hs_network_hierarchy_map set return_to_hub=? " + " where is_active = 1 and network_hub_id in (select network_hub_id from hs_network_hub where HUB_SITE_ID=? and is_active=1) and heirarchy_id=? " ); ps.setInt ( 1 , dto.getReturnToHubValue ( ) ); ps.setInt ( 2 , dto.getSiteId ( ) ); ps.setInt ( 3 , dto.getHierarchyId ( ) ); return ps; } ); updateChildrenForReturnToHub ( dto ); } if ( row > 0 ) { result.setSuccess ( true ); } } catch ( Exception ex ) { LOGGER.error ( "Exception in Return to Hub value for siteId : " + dto.getSiteId ( ) ); LOGGER.error ( "Exception message is: " + ex.getMessage ( ) ); LOGGER.error ( "Exception stacktrace is: " + Arrays.toString ( ex.getStackTrace ( ) ) ); result.setSuccess ( false ); } return result; } @Override public List getReturnToHubValues ( ) { List ReturnToHubValues = new ArrayList<> ( ); try { SqlRowSet rs = jdbcTemplate.queryForRowSet ( "select CONFIG_VALUE from HS_CONFIGURATION where IS_ACTIVE=1" ); while (rs.next ( )) { ReturnToHubValues.add ( Integer.parseInt ( rs.getString ( "CONFIG_VALUE" ) ) ); } } catch ( Exception ex ) { LOGGER.error ( "Exception in getReturnToHubValues" ); LOGGER.error ( "Exception message is: " + ex.getMessage ( ) ); LOGGER.error ( "Exception stacktrace is: " + Arrays.toString ( ex.getStackTrace ( ) ) ); } return ReturnToHubValues; } @Override public List getNetworkById ( Integer networkId , User user ) { HubSpokeWidgetData hubSpokeWidgetData = new HubSpokeWidgetData ( ); hubSpokeWidgetData.setNetworkId ( networkId ); hubSpokeWidgetData.setUser ( user ); return getNetwork ( hubSpokeWidgetData ); } @Override public List getNetwork ( HubSpokeWidgetData hubSpokeWidgetData ) { List dtoList = new ArrayList<> ( ); try { String sql; if ( hubSpokeWidgetData.getNetworkId ( ) != null ) sql = getNetworkQueryByNetworkId ( hubSpokeWidgetData.getNetworkId ( ) ); //gettingQuery by networkID else sql = getNetworkQueryByRoleAndSelectedSites ( hubSpokeWidgetData ); //getting Query by selected sites SqlRowSet rsHubSpokeNetworks = jdbcTemplate.queryForRowSet ( sql ); while (rsHubSpokeNetworks.next ( )) { HubSpokeNetworkDTO networkDTO = new HubSpokeNetworkDTO ( ); double changeUIC = 0.0; double changeMin = 0.0; double changeCOGS = 0.0; double changeTurnover = 0.0; double changeOnHand = 0.0; Float currentMin = 0f; Float currentCOGS = 0f; Float currentTurnOver = 0f; Float currentOnHand = 0f; Integer currentUIC = 0; networkDTO.setNetworkId ( rsHubSpokeNetworks.getInt ( "networkId" ) ); networkDTO.setNetworkName ( rsHubSpokeNetworks.getString ( "networkName" ) ); networkDTO.setNetworkType ( rsHubSpokeNetworks.getInt ( "networkType" ) ); networkDTO.setNetworkTypeLabel ( rsHubSpokeNetworks.getString ( "networkTypeLabel" ) ); networkDTO.setNetworkCreatedBy ( rsHubSpokeNetworks.getString ( "netCreatedBy" ) ); networkDTO.setNetworkCreatedDate ( rsHubSpokeNetworks.getDate ( "createdDate" ) ); networkDTO.setRedFlag ( rsHubSpokeNetworks.getBoolean ( "redFlag" ) ); networkDTO.setOriginalUIC ( rsHubSpokeNetworks.getInt ( "originalUIC" ) ); networkDTO.setNetworkType ( rsHubSpokeNetworks.getInt ( "networkType" ) ); networkDTO.setOriginalMin ( rsHubSpokeNetworks.getFloat ( "originalMin" ) ); networkDTO.setOriginalCOGS ( rsHubSpokeNetworks.getFloat ( "originalCOGS" ) ); networkDTO.setOriginalTurnover ( rsHubSpokeNetworks.getFloat ( "originalTurnover" ) ); networkDTO.setOriginalOnHand ( rsHubSpokeNetworks.getFloat ( "originalOnHand" ) ); SqlRowSet rsCurrent = jdbcTemplate.queryForRowSet ( "SELECT COUNT(DISTINCT SP.PRODUCT_ID) \"UIC\" ,NVL(SUM(NVL(min_stocking_qty,0) * NVL(price_wt_core,0)),0) \"min\" , NVL(SUM(NVL(SOLD_LAST_12_MONTHS,0) * NVL(price_wt_core,0)),0) \"COGS\", NVL(SUM(NVL(ON_HAND,0) * NVL(price_wt_core,0)),0) \"onHand\" ," + "(CASE WHEN round(NVL(SUM(NVL(min_stocking_qty,0) * NVL(price_wt_core,0)),0),2) > 0" + " THEN SUM(NVL(SOLD_LAST_12_MONTHS,0) * NVL(price_wt_core,0))/SUM(NVL(min_stocking_qty,0) * NVL(price_wt_core,0))" + "ELSE 0 END) \"turnOver\" FROM SITE_PRODUCT SP WHERE SP.SITE_ID IN " + "(SELECT HSN_SITE_ID FROM HUB_SPOKE_NETWORK_MAPPING WHERE HSN_NETWORK_ID= " + networkDTO.getNetworkId ( ) + " AND IS_ACTIVE=1) and NVL(sp.MIN_STOCKING_QTY,0) > 0" ); if ( rsCurrent.next ( ) ) { networkDTO.setCurrentUIC ( rsCurrent.getInt ( "UIC" ) ); networkDTO.setCurrentMin ( rsCurrent.getFloat ( "min" ) ); networkDTO.setCurrentCOGS ( rsCurrent.getFloat ( "COGS" ) ); networkDTO.setCurrentOnHand ( rsCurrent.getFloat ( "onHand" ) ); networkDTO.setCurrentTurnover ( rsCurrent.getFloat ( "turnOver" ) ); } currentUIC = networkDTO.getCurrentUIC ( ); currentMin = networkDTO.getCurrentMin ( ); currentCOGS = networkDTO.getCurrentCOGS ( ); currentOnHand = networkDTO.getCurrentOnHand ( ); currentTurnOver = networkDTO.getCurrentTurnover ( ); if ( currentUIC == 0 ) { changeUIC = 0.0; } else { changeUIC = currentUIC - networkDTO.getOriginalUIC ( ); changeUIC = ( changeUIC * 100 ) / currentUIC; changeUIC = Math.round ( changeUIC * Math.pow ( 10 , 1 ) ) / Math.pow ( 10 , 1 ); } networkDTO.setChangeUIC ( changeUIC ); if ( currentMin == 0f ) { changeMin = 0.0; } else { changeMin = currentMin - networkDTO.getOriginalMin ( ); changeMin = ( changeMin * 100 ) / currentMin; changeMin = Math.round ( changeMin * Math.pow ( 10 , 1 ) ) / Math.pow ( 10 , 1 ); } networkDTO.setChangeMin ( changeMin ); if ( currentCOGS == 0f ) { changeCOGS = 0.0; } else { changeCOGS = currentCOGS - networkDTO.getOriginalCOGS ( ); changeCOGS = ( changeCOGS * 100 ) / currentCOGS; changeCOGS = Math.round ( changeCOGS * Math.pow ( 10 , 1 ) ) / Math.pow ( 10 , 1 ); } networkDTO.setChangeCOGS ( changeCOGS ); if ( currentTurnOver == 0f ) { changeTurnover = 0.0; } else { changeTurnover = currentTurnOver - networkDTO.getOriginalTurnover ( ); changeTurnover = ( changeTurnover * 100 ) / currentTurnOver; changeTurnover = Math.round ( changeTurnover * Math.pow ( 10 , 1 ) ) / Math.pow ( 10 , 1 ); } networkDTO.setChangeTurnover ( changeTurnover ); if ( currentOnHand == 0f ) { changeOnHand = 0.0; } else { changeOnHand = currentOnHand - networkDTO.getOriginalOnHand ( ); changeOnHand = ( changeOnHand * 100 ) / currentOnHand; changeOnHand = Math.round ( changeOnHand * Math.pow ( 10 , 1 ) ) / Math.pow ( 10 , 1 ); } networkDTO.setChangeOnHand ( changeOnHand ); networkDTO.setExpanded ( false ); if ( hubSpokeWidgetData.getSelectedInventoryDropdown ( ) != null ) { if ( hubSpokeWidgetData.getSelectedInventoryDropdown ( ).equalsIgnoreCase ( "Original" ) ) { networkDTO.setUIC ( Double.valueOf ( networkDTO.getOriginalUIC ( ) ) ); networkDTO.setMin ( (double) ( networkDTO.getOriginalMin ( ) ) ); networkDTO.setCOGS ( (double) ( networkDTO.getOriginalCOGS ( ) ) ); networkDTO.setTurnOver ( Double.parseDouble ( df.format ( networkDTO.getOriginalTurnover ( ) ) ) ); networkDTO.setOnHand ( (double) ( networkDTO.getOriginalOnHand ( ) ) ); } else if ( hubSpokeWidgetData.getSelectedInventoryDropdown ( ).equalsIgnoreCase ( "current" ) ) { networkDTO.setUIC ( Double.valueOf ( networkDTO.getCurrentUIC ( ) ) ); networkDTO.setMin ( (double) ( networkDTO.getCurrentMin ( ) ) ); networkDTO.setCOGS ( (double) ( networkDTO.getCurrentCOGS ( ) ) ); networkDTO.setTurnOver ( Double.parseDouble ( df.format ( networkDTO.getCurrentTurnover ( ) ) ) ); networkDTO.setOnHand ( (double) ( networkDTO.getCurrentOnHand ( ) ) ); } else { networkDTO.setUIC ( Double.parseDouble ( dfp.format ( networkDTO.getChangeUIC ( ) ) ) ); networkDTO.setMin ( Double.parseDouble ( dfp.format ( networkDTO.getChangeMin ( ) ) ) ); networkDTO.setCOGS ( Double.parseDouble ( dfp.format ( networkDTO.getChangeCOGS ( ) ) ) ); networkDTO.setTurnOver ( Double.parseDouble ( dfp.format ( networkDTO.getChangeTurnover ( ) ) ) ); networkDTO.setOnHand ( Double.parseDouble ( dfp.format ( networkDTO.getChangeOnHand ( ) ) ) ); } } List spokeSiteList = new ArrayList<> ( ); SqlRowSet siteData = jdbcTemplate.queryForRowSet ( "SELECT HSNM.HSN_SITE_ID \"siteId\", S.SITE_NAME \"siteName\",S.SITE_SHORT_NAME \"siteShortName\", HNH.NETWORK_HUB_ID \"netHubId\" FROM HUB_SPOKE_NETWORK_MAPPING HSNM LEFT JOIN " + "SITE S ON HSNM.HSN_SITE_ID = S.SITE_ID LEFT JOIN HS_NETWORK_HUB HNH ON HNH.HUB_SITE_ID = HSNM.HSN_SITE_ID AND HNH.NETWORK_ID = HSNM.HSN_NETWORK_ID" + " AND HNH.IS_ACTIVE=HSNM.IS_ACTIVE WHERE HSNM.HSN_NETWORK_ID = ? AND " + "HSNM.IS_ACTIVE=1 ORDER BY S.SITE_NAME ASC" , networkDTO.getNetworkId ( ) ); while (siteData.next ( )) { SpokeSiteDto spokeSite = new SpokeSiteDto ( ); spokeSite.setSiteId ( siteData.getInt ( "siteId" ) ); spokeSite.setSiteName ( siteData.getString ( "siteName" ) ); spokeSite.setHubSiteId ( siteData.getInt ( "netHubId" ) ); spokeSite.setSiteShortName ( siteData.getString ( "siteShortName" ) ); spokeSite.setExpanded ( false ); List productMapList = new ArrayList<> ( ); SqlRowSet prodMapData = jdbcTemplate.queryForRowSet ( "select distinct vpm.SUPPLIER_DESC \"suppDesc\", hnpm.HSN_PRODUCT_MAP_ID \"prodId\", hnpm.HSN_PRODUCT_MAP_NAME \"prodName\", pu.FIRST_NAME || ' ' || pu.LAST_NAME \"createdBy\", " + "hnpm.UPDATED_DATE \"createdDate\" from HS_NETWORK_PRODUCT_MAP hnpm inner join PULSE_USER pu on hnpm.UPDATED_BY=pu.USER_ID " + "left join VENDOR_PART_MAPPING vpm on hnpm.SUPPLIER_NUMBER = vpm.SUPPLIER_NUMBER where " + "hnpm.NETWORK_HUB_ID=? and IS_ACTIVE=1 ORDER BY hnpm.HSN_PRODUCT_MAP_NAME ASC" , spokeSite.getHubSiteId ( ) ); while (prodMapData.next ( )) { ProductMapDto productMap = new ProductMapDto ( ); productMap.setProductId ( prodMapData.getInt ( "prodId" ) ); productMap.setProductName ( prodMapData.getString ( "prodName" ) ); productMap.setCreatedBy ( prodMapData.getString ( "createdBy" ) ); productMap.setCreatedDate ( prodMapData.getDate ( "createdDate" ) ); productMap.setSuppDesc ( prodMapData.getString ( "suppDesc" ) != null ? prodMapData.getString ( "suppDesc" ) : "" ); productMap.setDemandThresholdDropdownVisible ( networkDTO.getNetworkType ( ) != 2 ); productMapList.add ( productMap ); } spokeSite.setProductDetails ( productMapList ); spokeSiteList.add ( spokeSite ); } networkDTO.setSiteDetails ( spokeSiteList ); dtoList.add ( networkDTO ); } } catch ( Exception ex ) { LOGGER.error ( "Exception in getNetwork() : {}" , ex.getMessage ( ) ); } return dtoList; } private static final DecimalFormat df = new DecimalFormat ( "0.00" ); private static final DecimalFormat dfp = new DecimalFormat ( "0.0" ); public ActionResult deleteNetwork ( Integer networkId , Integer userId ) { ActionResult result = new ActionResult ( ); try { //revertData holds hierarchyIds that to be reverted to original position List revertData = prepareRevertObject ( networkId , null , userId ); jdbcTemplate.update ( "UPDATE HS_NETWORK_HIERARCHY_MAP SET IS_ACTIVE=0 WHERE NETWORK_HUB_ID IN (SELECT NETWORK_HUB_ID FROM HS_NETWORK_HUB WHERE NETWORK_ID=?" + " AND IS_ACTIVE=1) AND IS_ACTIVE=1" , networkId ); jdbcTemplate.update ( "UPDATE HS_NETWORK_PRODUCT_MAP SET IS_ACTIVE=0, UPDATED_BY=?, UPDATED_DATE=? WHERE NETWORK_HUB_ID IN (SELECT NETWORK_HUB_ID FROM " + "HS_NETWORK_HUB WHERE NETWORK_ID=? AND IS_ACTIVE=1) AND IS_ACTIVE=1" , userId , java.sql.Date.valueOf ( java.time.LocalDate.now ( ) ) , networkId ); jdbcTemplate.update ( "UPDATE HS_NETWORK_SPOKE SET IS_ACTIVE=0 WHERE NETWORK_HUB_ID IN (SELECT NETWORK_HUB_ID FROM HS_NETWORK_HUB WHERE NETWORK_ID=? AND " + "IS_ACTIVE=1)" , networkId ); jdbcTemplate.update ( "UPDATE HS_NETWORK_HUB SET IS_ACTIVE=0 WHERE NETWORK_ID=? AND IS_ACTIVE=1" , networkId ); jdbcTemplate.update ( "UPDATE HUB_SPOKE_NETWORK_MAPPING SET MODIFIED_BY=?, MODIFIED_DATE=?, IS_ACTIVE=0 WHERE HSN_NETWORK_ID=? AND IS_ACTIVE=1" , userId , java.sql.Date.valueOf ( java.time.LocalDate.now ( ) ) , networkId ); jdbcTemplate.update ( "UPDATE HUB_SPOKE_NETWORK SET IS_ACTIVE=0, MODIFIED_BY=?, MODIFIED_DATE=? WHERE HS_NETWORK_ID=? AND IS_ACTIVE=1" , userId , java.sql.Date.valueOf ( java.time.LocalDate.now ( ) ) , networkId ); revertSelectedIdListToRecommended ( revertData , userId ); result.setSuccess ( true ); } catch ( Exception ex ) { LOGGER.error ( "Exception in deleteNetwork for networkName:" + networkId ); LOGGER.error ( "Exception message is: " + ex.getMessage ( ) ); LOGGER.error ( "Exception stacktrace is: " + ex.getStackTrace ( ) ); result.setMessage ( "Cannot Delete Others User Network" ); result.setSuccess ( false ); } return result; } public ActionResult renameNetwork ( HubSpokeCreateRequestDto createRequestDto , Integer userId ) { ActionResult result = new ActionResult ( ); try { if ( createRequestDto.getNewName ( ) == null || createRequestDto.getOldName ( ).isEmpty ( ) || createRequestDto.getOldName ( ) == null || createRequestDto.getNewName ( ).isEmpty ( ) ) { result.setSuccess ( false ); } else { Integer hsNetworkId = jdbcTemplate.queryForObject ( "SELECT HS_NETWORK_ID FROM HUB_SPOKE_NETWORK WHERE HS_NETWORK_NAME=? AND CREATED_BY=? AND IS_ACTIVE=1" , new Object[]{createRequestDto.getOldName ( ) , userId} , Integer.class ); jdbcTemplate.update ( "UPDATE HUB_SPOKE_NETWORK SET HS_NETWORK_NAME=?, HS_NETWORK_DESC=?, MODIFIED_BY=?, MODIFIED_DATE=? WHERE HS_NETWORK_ID=?" , createRequestDto.getNewName ( ) , createRequestDto.getNewName ( ) , userId , java.sql.Date.valueOf ( java.time.LocalDate.now ( ) ) , hsNetworkId ); result.setSuccess ( true ); } } catch ( Exception ex ) { LOGGER.error ( "Exception in renameNetwork for Network : " + createRequestDto.getOldName ( ) ); LOGGER.error ( "Exception message is: " + ex.getMessage ( ) ); LOGGER.error ( "Exception stacktrace is: " + ex.getStackTrace ( ) ); result.setMessage ( "Cannot Update Other User's Network" ); result.setSuccess ( false ); } return result; } public ProductHubDTO createHubSpoke ( Integer networkID , Integer siteID ) { ProductHubDTO productHubDTO = new ProductHubDTO ( ); try { String checkNetworkID = "select NETWORK_HUB_ID \"hubId\" from HS_NETWORK_HUB WHERE NETWORK_ID = " + networkID + " and HUB_SITE_ID=" + siteID + " and is_active=1"; SqlRowSet networkPresent = jdbcTemplate.queryForRowSet ( checkNetworkID ); if ( ! networkPresent.next ( ) ) { Long networkHubId = jdbcTemplate.queryForObject ( "SELECT HS_NETWORK_HUB_SEQ.NEXTVAL FROM DUAL" , Long.class ); int row = jdbcTemplate.update ( "INSERT INTO HS_NETWORK_HUB (NETWORK_HUB_ID, NETWORK_ID, HUB_SITE_ID, IS_ACTIVE) VALUES(?,?,?,?)" , networkHubId , networkID , siteID , 1 ); if ( row > 0 ) { productHubDTO.setHubID ( networkHubId ); productHubDTO.setNetworkID ( networkID ); productHubDTO.setSiteID ( siteID ); productHubDTO.setProduct ( new ArrayList<> ( ) ); } String sql = "select h.HSN_SITE_ID \"hsnSiteID\" from HUB_SPOKE_NETWORK_MAPPING h WHERE HSN_NETWORK_ID = " + networkID + " and IS_ACTIVE=1 and HSN_SITE_ID!= " + siteID; SqlRowSet hsnSiteIDs = jdbcTemplate.queryForRowSet ( sql ); while (hsnSiteIDs.next ( )) { jdbcTemplate.update ( "INSERT INTO HS_NETWORK_SPOKE (NETWORK_HUB_ID,SPOKE_SITE_ID,IS_ACTIVE) VALUES(?,?,?)" , networkHubId , hsnSiteIDs.getInt ( "hsnSiteID" ) , 1 ); } } else { String sql = " select heirarchy_id,threshold_value,heirarchy_level,demand_threshold from HS_NETWORK_HIERARCHY_MAP where network_hub_id=" + networkPresent.getInt ( "hubId" ) + " and is_active=1"; SqlRowSet productSet = jdbcTemplate.queryForRowSet ( sql ); List productDTOList = new ArrayList<> ( ); while (productSet.next ( )) { ProductDTO dto = new ProductDTO ( ); dto.setHierarchyId ( productSet.getInt ( "heirarchy_id" ) ); dto.setHierarchyLevel ( productSet.getInt ( "heirarchy_level" ) ); dto.setThresholdValue ( productSet.getInt ( "threshold_value" ) ); dto.setDemandThreshold ( productSet.getString ( "demand_threshold" ) ); productDTOList.add ( dto ); } productHubDTO.setNetworkID ( networkID ); productHubDTO.setSiteID ( siteID ); productHubDTO.setProduct ( productDTOList ); productHubDTO.setHubID ( networkPresent.getLong ( "hubId" ) ); } return productHubDTO; } catch ( Exception ex ) { LOGGER.error ( "Exception in createHubSpoke for networkID: " + networkID + "and siteID-" + siteID ); LOGGER.error ( "Exception message is: " + ex.getMessage ( ) ); LOGGER.error ( "Exception stacktrace is: " + ex.getStackTrace ( ) ); return productHubDTO; } } /*Method UpdateHubProduct Not Being Used Currently. Remove In Future If Not Used*/ @Override public ActionResult createHubProduct ( ProductHubDTO dto ) { ActionResult result = new ActionResult ( ); try { for (ProductDTO productDTO : dto.getProduct ( )) { jdbcTemplate.update ( "INSERT INTO HS_NETWORK_HIERARCHY_MAP (NETWORK_HUB_ID,HEIRARCHY_ID,THRESHOLD_VALUE,HEIRARCHY_LEVEL,STOCK_STATUS,DEMAND_THRESHOLD," + "IS_ACTIVE) VALUES(?,?,?,?,?,?)" , dto.getHubID ( ) , productDTO.getHierarchyId ( ) , productDTO.getThresholdValue ( ) , productDTO.getHierarchyLevel ( ) , productDTO.getDemandThreshold ( ) , 1 ); } result.setSuccess ( true ); } catch ( Exception ex ) { LOGGER.error ( "Exception in createHubProduct for hubId: " + dto.getHubID ( ) ); LOGGER.error ( "Exception message is: " + ex.getMessage ( ) ); LOGGER.error ( "Exception stacktrace is: " + ex.getStackTrace ( ) ); } return result; } /*Method UpdateHubProduct Not Being Used Currently. Remove In Future If Not Used*/ @Override public ActionResult updateHubProduct ( ProductHubDTO hubDTO ) { ActionResult result = new ActionResult ( ); try { jdbcTemplate.update ( "update HS_NETWORK_HIERARCHY_MAP set is_active = 0 where is_active = 1 and NETWORK_HUB_ID=?" , hubDTO.getHubID ( ) ); for (ProductDTO productDTO : hubDTO.getProduct ( )) { jdbcTemplate.update ( connection -> { PreparedStatement ps = connection.prepareStatement ( "INSERT INTO HS_NETWORK_HIERARCHY_MAP (NETWORK_HUB_ID,HEIRARCHY_ID,THRESHOLD_VALUE,HEIRARCHY_LEVEL,DEMAND_THRESHOLD,IS_ACTIVE) VALUES(?,?,?,?,?,?)" ); ps.setLong ( 1 , hubDTO.getHubID ( ) ); ps.setInt ( 2 , productDTO.getHierarchyId ( ) ); ps.setInt ( 3 , productDTO.getThresholdValue ( ) ); ps.setInt ( 4 , productDTO.getHierarchyLevel ( ) ); ps.setString ( 5 , productDTO.getDemandThreshold ( ) ); ps.setInt ( 6 , 1 ); return ps; } ); } result.setSuccess ( true ); } catch ( Exception ex ) { LOGGER.error ( "Exception in updateHubProduct for hubId: " + hubDTO.getHubID ( ) ); LOGGER.error ( "Exception message is: " + ex.getMessage ( ) ); LOGGER.error ( "Exception stacktrace is: " + ex.getStackTrace ( ) ); } return result; } @Override public Integer getThresholdValue ( Integer hubSiteId , Integer productMapId ) { try { List> list = jdbcTemplate.queryForList ( "select distinct(NVL(threshold_value,0)) as val from hs_network_hierarchy_map where is_active = 1 and network_hub_id = " + hubSiteId + " and hsn_product_map_id = " + productMapId ); if ( list.size ( ) > 1 ) { return - 1; } else { Integer val = jdbcTemplate.queryForObject ( "select distinct(NVL(threshold_value,0)) as val from hs_network_hierarchy_map where is_active = 1 and network_hub_id = " + hubSiteId + " and hsn_product_map_id = " + productMapId , Integer.class ); if ( val == null ) return 4; return val; } } catch ( Exception e ) { LOGGER.error ( "Error in getThresholdValue()" ); return 4; } } @Override public List getHierarchyId ( Integer hubSiteId , Integer productMapId ) { List heirarchyIdsList = new ArrayList<> ( ); try { if ( hubSiteId != null ) { String sql = " select heirarchy_id \"heirarchyId\" from HS_NETWORK_HIERARCHY_MAP where network_hub_id = ? and is_active=1 and is_selected = 1" + " and hsn_product_map_id = ?"; SqlRowSet heirarchyIds = jdbcTemplate.queryForRowSet ( sql , hubSiteId , productMapId ); while (heirarchyIds.next ( )) { heirarchyIdsList.add ( heirarchyIds.getInt ( "heirarchyId" ) ); } } return heirarchyIdsList; } catch ( Exception ex ) { LOGGER.error ( "Exception in getHierarchyIdList()" ); LOGGER.error ( "Exception message is: " + ex.getMessage ( ) ); LOGGER.error ( "Exception stacktrace is: " + ex.getStackTrace ( ) ); return heirarchyIdsList; } } @Override public List getChildHierarchyIds ( Integer hierarchyId ) { List list = new ArrayList<> ( ); try { SqlRowSet rs = jdbcTemplate.queryForRowSet ( "select distinct HIERARCHY_ID_L2 hid from HIERARCHY_LEVELS where HIERARCHY_ID_L2 is not null and HIERARCHY_ID_L1 = ?" , hierarchyId ); while (rs.next ( )) { list.add ( rs.getInt ( "hid" ) ); } rs = jdbcTemplate.queryForRowSet ( "select distinct HIERARCHY_ID_L3 hid from HIERARCHY_LEVELS where HIERARCHY_ID_L3 is not null and (HIERARCHY_ID_L1 = ? or HIERARCHY_ID_L2 = ?)" , hierarchyId , hierarchyId ); while (rs.next ( )) { list.add ( rs.getInt ( "hid" ) ); } rs = jdbcTemplate.queryForRowSet ( "select distinct HIERARCHY_ID_L4 hid from HIERARCHY_LEVELS where HIERARCHY_ID_L4 is not null and (HIERARCHY_ID_L1 = ? or HIERARCHY_ID_L2 = ? or HIERARCHY_ID_L3 = ?)" , hierarchyId , hierarchyId , hierarchyId ); while (rs.next ( )) { list.add ( rs.getInt ( "hid" ) ); } return list; } catch ( Exception e ) { LOGGER.error ( "Error in getChildHierarchyIds( {} )" , hierarchyId ); LOGGER.error ( e.getMessage ( ) ); return list; } } @Override public Map getDemandThresholdOfSavedIds ( Integer networkHubId , Integer productMapId , List newlyAddedIds , Integer defaultDemandThreshold ) { Map map = new HashMap<> ( ); try { String sql = "select HEIRARCHY_ID as hid, NVL(THRESHOLD_VALUE, 4) as dt from HS_NETWORK_HIERARCHY_MAP where NETWORK_HUB_ID = " + networkHubId + " and HSN_PRODUCT_MAP_ID = " + productMapId + " and IS_ACTIVE = 1"; SqlRowSet rs = jdbcTemplate.queryForRowSet ( sql ); while (rs.next ( )) { if ( newlyAddedIds.contains ( rs.getInt ( "hid" ) ) ) map.put ( rs.getInt ( "hid" ) , defaultDemandThreshold ); else map.put ( rs.getInt ( "hid" ) , rs.getInt ( "dt" ) ); } for (Integer pId : newlyAddedIds) { for (Integer childHierarchyIds : getChildHierarchyIds ( pId )) { if ( map.containsKey ( childHierarchyIds ) ) { map.replace ( childHierarchyIds , map.getOrDefault ( pId , defaultDemandThreshold ) ); } } } return map; } catch ( Exception e ) { LOGGER.error ( "Error in getDemandThresholdOfSavedIds() for networkHubId : {} and productMapId : {}" , networkHubId , productMapId ); LOGGER.error ( e.getMessage ( ) ); return map; } } @Override public ActionResult updateHierarchyId ( Integer networkId , Integer siteId , Integer hubSiteId , Integer productMapId , Integer supplierNo , Integer userId , List hierarchyIdsList , List newlyAddedIds , Integer selectedThresholdValue , Boolean isDiffDemThre ) { ActionResult result = new ActionResult ( ); try { Integer defaultDemandThreshold = 4; // fetching old selected product ids for reverting them to recommended later in the code List oldProductIds = getSelectedProductIds ( hubSiteId , productMapId ); Map demThreMap = isDiffDemThre ? getDemandThresholdOfSavedIds ( hubSiteId , productMapId , newlyAddedIds , defaultDemandThreshold ) : new HashMap<> ( ); // this will mark respective hierarchy to their boolean selected status List hierarchyList = new ArrayList<> ( ); if ( hierarchyIdsList != null ) { for (String j : hierarchyIdsList) { if ( j.contains ( "s_" ) ) { hierarchyList.add ( new ProductGroupHierarchy ( Integer.valueOf ( j.substring ( "s_".length ( ) ) ) , true ) ); } else { hierarchyList.add ( new ProductGroupHierarchy ( Integer.valueOf ( j ) , false ) ); } } } // deleting old hierarchies from product map jdbcTemplate.update ( "update HS_NETWORK_HIERARCHY_MAP set IS_ACTIVE = 0 where IS_ACTIVE = 1 and NETWORK_HUB_ID = ? and HSN_PRODUCT_MAP_ID = ?" , hubSiteId , productMapId ); // inserting new hierarchies in product map for (ProductGroupHierarchy h : hierarchyList) { Integer hierarchyLvl = jdbcTemplate.queryForObject ( "select HIERARCHY_LEVEL \"hierarchyLevel\" from PRODUCT_HIERARCHY where HIERARCHY_ID = " + h.getHierarchyId ( ) , Integer.class ); if ( hierarchyLvl != null ) { String sql = "INSERT INTO HS_NETWORK_HIERARCHY_MAP (NETWORK_HUB_ID,HEIRARCHY_ID,THRESHOLD_VALUE,HEIRARCHY_LEVEL,DEMAND_THRESHOLD," + "IS_ACTIVE,IS_SELECTED,HSN_PRODUCT_MAP_ID) VALUES(?,?,?,?,?,?,?,?)"; Integer demThre = isDiffDemThre ? demThreMap.getOrDefault ( h.getHierarchyId ( ) , defaultDemandThreshold ) : selectedThresholdValue; jdbcTemplate.update ( sql , hubSiteId , h.getHierarchyId ( ) , demThre , hierarchyLvl , "S" , 1 , h.getIsSelected ( ) , productMapId ); } } // fetching new updated product ids for reverting them to recommended List newProductIds = getSelectedProductIds ( hubSiteId , productMapId ); // filtering unmatched hierarchy ids that are no more part of product map List filteredIds = oldProductIds.stream ( ).filter ( x -> ! newProductIds.contains ( x ) ).collect ( Collectors.toList ( ) ); // reverting hierarchy ids to recommended after deletion revertProductsToRecommended ( siteId , filteredIds , null , userId ); // setting slider position for selected hierarchy ids to hub/spoke logic if ( hierarchyIdsList != null ) { for (String j : hierarchyIdsList) { if ( j.contains ( "s_" ) ) { parametersDAO.saveSliderValue ( siteId , Integer.parseInt ( j.substring ( "s_".length ( ) ) ) , null , - 0.3f , userId ); } } } if ( supplierNo == 0 ) { jdbcTemplate.update ( "update hs_network_product_map set supplier_number = ?, updated_by = ?, updated_date = ? " + "where hsn_product_map_id = ? and network_hub_id = ? and is_active = 1" , null , userId , java.sql.Date.valueOf ( java.time.LocalDate.now ( ) ) , productMapId , hubSiteId ); } else { jdbcTemplate.update ( "update hs_network_product_map set supplier_number = ?, updated_by = ?, updated_date = ? " + "where hsn_product_map_id = ? and network_hub_id = ? and is_active = 1" , supplierNo , userId , java.sql.Date.valueOf ( java.time.LocalDate.now ( ) ) , productMapId , hubSiteId ); } String query = "Select site.site_short_name ||'_'||hsn_product_map_name||'('||hs_network_name||')' result from \n" + "(select hs_network_name from hub_spoke_network where hs_network_id =" + networkId + ") network,\n" + "(select hsn_product_map_name from hs_network_product_map where hsn_product_map_id =" + productMapId + ") product,\n" + "(select site_short_name from site where site_id = " + siteId + ") site "; String productGroupName = jdbcTemplate.queryForObject ( query , String.class ); Integer count = jdbcTemplate.queryForObject ( "select count(*) from product_group where hsn_product_map_id =" + productMapId + "" , Integer.class ); if ( count != null && count > 0 ) { jdbcTemplate.update ( "update product_group set group_name = ? where hsn_product_map_id = ?" , productGroupName , productMapId ); Integer productGroupID = jdbcTemplate.queryForObject ( "select product_group_id from product_group where hsn_product_map_id= " + productMapId , Integer.class ); productGroupDAO.updateProductGroupMapping ( productGroupID , hierarchyList ); } else { productGroupDAO.createForHs ( userId , productGroupName , 1 , hierarchyList , productMapId ); } result.setSuccess ( true ); } catch ( Exception ex ) { result.setSuccess ( false ); LOGGER.error ( "Exception in updateHierarchyIds for networkID:" + networkId + "-siteID-" + siteId ); LOGGER.error ( "Exception message is:" + ex.getMessage ( ) ); } return result; } @Override public Map getSelectedProductTree ( Integer networkHubId , Integer productMapId , User user ) { Map hierarchyTree = new LinkedHashMap<> ( ); try { Object[] hierarchyItemList = jdbcTemplate.queryForObject ( " SELECT CAST (MULTISET(select h.hierarchy_id, HIERARCHY_LONG_NAME , parent_id, HIERARCHY_LEVEL, IS_SELECTED, NVL(aco_enabled,0) aco_enabled \n" + "from hs_network_hierarchy_map m inner join PRODUCT_HIERARCHY h on h.hierarchy_id = m.heirarchy_id \n" + "and m.network_hub_id = ? and m.hsn_product_map_id = ? and m.is_active = 1 order by hierarchy_level,HIERARCHY_LONG_NAME) AS HIERARCHY_VA) FROM DUAL" , new Object[]{networkHubId , productMapId} , new StructVarrayRowMapper ( ) ); hierarchyTree = HierarchyUtils.getHierarchyTreeFromHierarchyVarray ( hierarchyItemList ); } catch ( Exception ex ) { LOGGER.error ( "Exception in GetSelectedProductTree For NetworkHubId=" + networkHubId ); LOGGER.error ( "Exception message is: " + ex.getMessage ( ) ); LOGGER.error ( "Exception stacktrace is: " + Arrays.toString ( ex.getStackTrace ( ) ) ); } return hierarchyTree; } @Override public Map getSelectedProductTreeForOtherSites ( Integer networkId , Integer productMapId , String hubSiteId , User user ) { Map hierarchyTree = new LinkedHashMap<> ( ); hubSiteId = hubSiteId.substring ( 1 , hubSiteId.length ( ) - 1 ); try { Object[] hierarchyItemList = jdbcTemplate.queryForObject ( " SELECT CAST (MULTISET(select h.hierarchy_id, HIERARCHY_LONG_NAME , parent_id, HIERARCHY_LEVEL," + " CASE WHEN IS_SELECTED = 1 THEN 0 ELSE 0 END IS_SELECTED, NVL(aco_enabled,0) aco_enabled \n" + "from hs_network_hierarchy_map m inner join PRODUCT_HIERARCHY h on h.hierarchy_id = m.heirarchy_id \n" + "and m.network_hub_id in (" + hubSiteId + ") and m.is_active = 1 and m.hsn_product_map_id <> ? " + "order by hierarchy_level,HIERARCHY_LONG_NAME) AS HIERARCHY_VA) FROM DUAL" , new Object[]{productMapId} , new StructVarrayRowMapper ( ) ); hierarchyTree = HierarchyUtils.getHierarchyTreeFromHierarchyVarray ( hierarchyItemList ); } catch ( Exception ex ) { LOGGER.error ( "Exception in GetSelectedProductTreeForOtherSites For NetworkId=" + networkId ); LOGGER.error ( "Exception message is: " + ex.getMessage ( ) ); LOGGER.error ( "Exception stacktrace is: " + Arrays.toString ( ex.getStackTrace ( ) ) ); } return hierarchyTree; } @Override public List getThresholdValues ( ) { List thresholdValues = new ArrayList<> ( ); try { SqlRowSet rs = jdbcTemplate.queryForRowSet ( "select HS_THRESHOLD_VALUE from HS_THRESHOLD_VALUE order by hs_threshold_value" ); while (rs.next ( )) { thresholdValues.add ( Integer.parseInt ( rs.getString ( "HS_THRESHOLD_VALUE" ) ) ); } } catch ( Exception ex ) { LOGGER.error ( "Exception in GetThresholdValues" ); LOGGER.error ( "Exception message is: " + ex.getMessage ( ) ); LOGGER.error ( "Exception stacktrace is: " + Arrays.toString ( ex.getStackTrace ( ) ) ); } return thresholdValues; } @Override public HubSliderDto getProductHierarchies ( Integer siteId ) { HubSliderDto hubSliderDto = new HubSliderDto ( ); List productIds = new ArrayList<> ( ); int[][] parentValues; HashMap hsMap = new HashMap ( ); List demandThresholds = new ArrayList<> ( ); List thresholdValues = new ArrayList<> ( ); List isHubFlag = new ArrayList<> ( ); int rowSize, i = 0; try { SqlRowSet hubRowSet = jdbcTemplate.queryForRowSet ( "select distinct network_hub_id,hub_site_id,spoke_site_id " + "from hs_network_hierarchy_v where ( hub_site_id=? or spoke_site_id=?) order by network_hub_id " , siteId , siteId ); while (hubRowSet.next ( )) { if ( hsMap.get ( hubRowSet.getInt ( "network_hub_id" ) ) == null ) { if ( hubRowSet.getInt ( "hub_site_id" ) == siteId ) { hsMap.put ( hubRowSet.getInt ( "network_hub_id" ) , "hub" ); } else { hsMap.put ( hubRowSet.getInt ( "network_hub_id" ) , "spoke" ); } } } for (Map.Entry entry : hsMap.entrySet ( )) { SqlRowSet rs = jdbcTemplate.queryForRowSet ( "select network_hub_id,heirarchy_id,heirarchy_level,threshold_value,demand_threshold " + "from hs_network_hierarchy_map where network_hub_id in (?) and is_selected=1 and is_active=1" , entry.getKey ( ) ); if ( rs.last ( ) ) { rowSize = rs.getRow ( ); parentValues = new int[rowSize][3]; positionRowSet ( parentValues , rs ); productIds = findChildren ( rs , productIds , rowSize , i , parentValues , thresholdValues , demandThresholds , isHubFlag , entry.getValue ( ) ); setValuesForChildren ( productIds , rs , thresholdValues , demandThresholds ); hubSliderDto.setHub ( true ); } } hubSliderDto.setProductIds ( productIds ); hubSliderDto.setThresholdValues ( thresholdValues ); hubSliderDto.setDemandThresholds ( demandThresholds ); hubSliderDto.setHubSpokeFlags ( isHubFlag ); } catch ( Exception ex ) { LOGGER.error ( "Exception in GetProductHierarchy for siteId : " + siteId ); LOGGER.error ( "Exception message is: " + ex.getMessage ( ) ); LOGGER.error ( "Exception stacktrace is: " + Arrays.toString ( ex.getStackTrace ( ) ) ); } return hubSliderDto; } private void positionRowSet ( int[][] parentValues , SqlRowSet rowSet ) { rowSet.beforeFirst ( ); setParentIds ( rowSet , parentValues ); rowSet.beforeFirst ( ); } private void setParentIds ( SqlRowSet rs , int[][] parentThresholds ) { int i = 0; while (rs.next ( )) { parentThresholds[i][0] = rs.getInt ( "heirarchy_id" ); parentThresholds[i][1] = rs.getInt ( "threshold_value" ); parentThresholds[i][2] = rs.getString ( "demand_threshold" ).equals ( "S" ) ? 0 : 1; i++; } } private List findChildren ( SqlRowSet rs , List productIds , Integer rowSize , int i , int[][] parentThresholds , List thresholdValues , List demandThresholds , List hubSpokeFlags , String isHub ) { while (rs.next ( )) { productIds.add ( rs.getInt ( "heirarchy_id" ) ); if ( i < rowSize && parentThresholds[i][0] == rs.getInt ( "heirarchy_id" ) ) { i++; } thresholdValues.add ( parentThresholds[i - 1][1] ); demandThresholds.add ( parentThresholds[i - 1][2] == 0 ? "Sell Down" : "Return" ); hubSpokeFlags.add ( isHub ); if ( rs.getInt ( "heirarchy_level" ) >= 4 ) { continue; } else { SqlRowSet childSet = jdbcTemplate.queryForRowSet ( "select hierarchy_level\"heirarchy_level\" ,hierarchy_id \"heirarchy_id\" from product_hierarchy where " + " parent_id=? " , rs.getInt ( "heirarchy_id" ) ); if ( childSet.last ( ) ) { childSet.beforeFirst ( ); findChildren ( childSet , productIds , rowSize , i , parentThresholds , thresholdValues , demandThresholds , hubSpokeFlags , isHub ); } } } rs.beforeFirst ( ); return productIds; } private void setValuesForChildren ( List productIds , SqlRowSet rowSet , List thresholdValues , List demandThresholds ) { if ( rowSet.next ( ) ) { for (int j = 0; j < productIds.size ( ); j++) { SqlRowSet rs = jdbcTemplate.queryForRowSet ( "select threshold_value,demand_threshold" + " from hs_network_hierarchy_map where network_hub_id=? and is_active=1 and heirarchy_id=? " , rowSet.getInt ( "network_hub_id" ) , productIds.get ( j ) ); if ( rs.next ( ) ) { thresholdValues.set ( j , rs.getInt ( "threshold_value" ) ); demandThresholds.set ( j , rs.getString ( "demand_threshold" ).equals ( "S" ) ? "Sell Down" : "Return" ); } } } } @Override public ActionResult updateThresholdValues ( DemandthresholdDTO dto ) { ActionResult result = new ActionResult ( ); int row = 0; try { if ( dto.getThresholdValue ( ) != null ) { row = jdbcTemplate.update ( connection -> { PreparedStatement ps = connection.prepareStatement ( "update hs_network_hierarchy_map set threshold_value=? " + " where is_active = 1 and network_hub_id in (select network_hub_id from hs_network_hub where HUB_SITE_ID=? and is_active=1) and heirarchy_id=? " ); ps.setInt ( 1 , dto.getThresholdValue ( ) ); ps.setInt ( 2 , dto.getSiteId ( ) ); ps.setInt ( 3 , dto.getHierarchyId ( ) ); return ps; } ); updateChildrenThreshold ( dto ); } else if ( dto.getDemandThreshold ( ) != null ) { row = jdbcTemplate.update ( connection -> { PreparedStatement ps = connection.prepareStatement ( "update hs_network_hierarchy_map set demand_threshold=? " + " where is_active = 1 and network_hub_id in (select network_hub_id from hs_network_hub where HUB_SITE_ID=? and is_active=1) and heirarchy_id=? " ); ps.setString ( 1 , dto.getDemandThreshold ( ) ); ps.setInt ( 2 , dto.getSiteId ( ) ); ps.setInt ( 3 , dto.getHierarchyId ( ) ); return ps; } ); updateChildrenThreshold ( dto ); } if ( row > 0 ) { result.setSuccess ( true ); } } catch ( Exception ex ) { LOGGER.error ( "Exception in UpdateThresholdValue for siteId : " + dto.getSiteId ( ) ); LOGGER.error ( "Exception message is: " + ex.getMessage ( ) ); LOGGER.error ( "Exception stacktrace is: " + Arrays.toString ( ex.getStackTrace ( ) ) ); result.setSuccess ( false ); } return result; } private void updateChildrenThreshold ( DemandthresholdDTO dto ) { try { SqlRowSet sqlRowSet = jdbcTemplate.queryForRowSet ( "select heirarchy_level from hs_network_hierarchy_map " + " where is_active = 1 and network_hub_id in (select network_hub_id from hs_network_hub " + " where HUB_SITE_ID=? and is_active=1) and heirarchy_id=?" , dto.getSiteId ( ) , dto.getHierarchyId ( ) ); if ( dto.getDemandThreshold ( ) != null && sqlRowSet.next ( ) ) { jdbcTemplate.update ( "update hs_network_hierarchy_map set demand_threshold=? " + " where is_active = 1 and network_hub_id in (select network_hub_id from hs_network_hub where HUB_SITE_ID=? and is_active=1) and heirarchy_id=? " , dto.getDemandThreshold ( ) , dto.getSiteId ( ) , dto.getHierarchyId ( ) ); } else if ( dto.getThresholdValue ( ) != null && sqlRowSet.next ( ) ) { jdbcTemplate.update ( "update hs_network_hierarchy_map set threshold_value=? " + " where is_active = 1 and network_hub_id in (select network_hub_id from hs_network_hub where HUB_SITE_ID=? and is_active=1) and heirarchy_id=? " , dto.getThresholdValue ( ) , dto.getSiteId ( ) , dto.getHierarchyId ( ) ); } if ( sqlRowSet.getInt ( "heirarchy_level" ) < 4 ) { SqlRowSet rs = jdbcTemplate.queryForRowSet ( "select hierarchy_id \"heirarchy_id\" from product_hierarchy where " + " parent_id=? " , dto.getHierarchyId ( ) ); while (rs.next ( )) { dto.setHierarchyId ( rs.getInt ( "heirarchy_id" ) ); updateChildrenThreshold ( dto ); } } } catch ( Exception ex ) { LOGGER.error ( "Exception in UpdateChildrenThreshold for siteId : " + dto.getSiteId ( ) ); LOGGER.error ( "Exception message is: " + ex.getMessage ( ) ); LOGGER.error ( "Exception stacktrace is: " + Arrays.toString ( ex.getStackTrace ( ) ) ); } } private void updateChildrenForReturnToHub ( ReturntoHubDTO dto ) { try { SqlRowSet sqlRowSet = jdbcTemplate.queryForRowSet ( "select heirarchy_level from hs_network_hierarchy_map " + " where is_active = 1 and network_hub_id in (select network_hub_id from hs_network_hub " + " where HUB_SITE_ID=? and is_active=1) and heirarchy_id=?" , dto.getSiteId ( ) , dto.getHierarchyId ( ) ); if ( dto.getReturnToHubValue ( ) != null && sqlRowSet.next ( ) ) { jdbcTemplate.update ( "update hs_network_hierarchy_map set return_to_hub=? " + " where is_active = 1 and network_hub_id in (select network_hub_id from hs_network_hub where HUB_SITE_ID=? and is_active=1) and heirarchy_id=? " , dto.getReturnToHubValue ( ) , dto.getSiteId ( ) , dto.getHierarchyId ( ) ); } // else if (dto.getThresholdValue() != null && sqlRowSet.next()) { // jdbcTemplate.update("update hs_network_hierarchy_map set threshold_value=? " + // " where is_active = 1 and network_hub_id in (select network_hub_id from hs_network_hub where HUB_SITE_ID=? and is_active=1) and heirarchy_id=? ", dto.getThresholdValue(), dto.getSiteId(), dto.getHierarchyId()); // // } if ( sqlRowSet.getInt ( "heirarchy_level" ) < 4 ) { SqlRowSet rs = jdbcTemplate.queryForRowSet ( "select hierarchy_id \"heirarchy_id\" from product_hierarchy where " + " parent_id=? " , dto.getHierarchyId ( ) ); while (rs.next ( )) { dto.setHierarchyId ( rs.getInt ( "heirarchy_id" ) ); updateChildrenForReturnToHub ( dto ); } } } catch ( Exception ex ) { LOGGER.error ( "Exception in updateChildrenForReturnToHub for siteId : " + dto.getSiteId ( ) ); LOGGER.error ( "Exception message is: " + ex.getMessage ( ) ); LOGGER.error ( "Exception stacktrace is: " + Arrays.toString ( ex.getStackTrace ( ) ) ); } } @Override public List distinctVendorList ( ) { List vendorDatas = new ArrayList<> ( ); VendorDataDTO venData; try { SqlRowSet vendorData = jdbcTemplate.queryForRowSet ( "Select SUPPLIER_NUMBER \"supNum\" ,SUPPLIER_DESC \"supDesc\" from vendor_data_v" ); while (vendorData.next ( )) { venData = new VendorDataDTO ( ); venData.setSupplierNumber ( vendorData.getInt ( "supNum" ) ); venData.setSupplierDesc ( vendorData.getString ( "supDesc" ) ); vendorDatas.add ( venData ); } return vendorDatas; } catch ( Exception ex ) { LOGGER.error ( "Exception in Vendor Data" ); LOGGER.error ( "Exception message:" + ex.getMessage ( ) ); LOGGER.error ( "Exception stacktrace:" + ex.getStackTrace ( ) ); } return vendorDatas; } @Override public List productIdsList ( Integer supplierNumber , Integer siteId ) { ArrayList list = new ArrayList<> ( ); try { SqlRowSet resultSet = jdbcTemplate.queryForRowSet ( "select distinct sp.LEVEL4_PARENT_ID \"lvl4Id\" from\n" + " product p inner join VENDOR_PART_MAPPING vp on vp.PART_NUMBER = p.PART_NUMBER and vp.hq_abbr = p.hq_abbr \n" + " inner join SITE_PRODUCT sp on sp.product_id = p.product_id \n" + " where vp.SUPPLIER_NUMBER = ? and sp.site_id = ? order by sp.LEVEL4_PARENT_ID asc" , supplierNumber , siteId ); while (resultSet.next ( )) { list.add ( resultSet.getLong ( "lvl4Id" ) ); } } catch ( Exception ex ) { LOGGER.error ( "Exception in Product ID List For supplierNumber=" + supplierNumber + " and SiteId=" + siteId ); LOGGER.error ( "Exception message is:" + ex.getMessage ( ) ); LOGGER.error ( "Exception stacktrace is:" + ex.getStackTrace ( ) ); } return list; } @Override public List getHierarchyIdForOtherSites ( Integer networkID , List hubSiteIdList , Integer productMapId ) { List heirarchyIdsList = new ArrayList<> ( ); try { for (String i : hubSiteIdList) { String sql = " SELECT HEIRARCHY_ID \"HEIRARCHYID\" FROM HS_NETWORK_HIERARCHY_MAP WHERE NETWORK_HUB_ID = " + i + " AND IS_SELECTED = 1 AND IS_ACTIVE = 1 AND HSN_PRODUCT_MAP_ID <> ?"; SqlRowSet heirarchyIds = jdbcTemplate.queryForRowSet ( sql , productMapId ); while (heirarchyIds.next ( )) { heirarchyIdsList.add ( heirarchyIds.getInt ( "HEIRARCHYID" ) ); } } return heirarchyIdsList; } catch ( Exception ex ) { LOGGER.error ( "Exception message is: " + ex.getMessage ( ) ); LOGGER.error ( "Exception stacktrace is: " + ex.getStackTrace ( ) ); } return heirarchyIdsList; } @Override public HubSpokeSitesDTO getHubSpokeFlags ( Integer sessionId , Integer productId, Integer userId ) { HubSpokeSitesDTO dto = new HubSpokeSitesDTO ( ); List sites = new ArrayList<> ( ); List siteFlags = new ArrayList<> ( ); List hubspokeFlag = new ArrayList<>(); List i8Flag = new ArrayList<> ( ); try { boolean isFinalized = reviewDAO.isSessionFinalized ( sessionId ); SqlRowSet rs = jdbcTemplate.queryForRowSet ( "SELECT " + "ss.site_id,ssp.level4_parent_id, " + " case " + "when (select count(*) from hs_network_hierarchy_v where hierarchy_id=ssp.level4_parent_id and hub_site_id=ss.site_id)>0 then 'hub' " + "when (select count(*) from hs_network_hierarchy_v where hierarchy_id=ssp.level4_parent_id and spoke_site_id=ss.site_id)>0 then 'spoke' " + "else 'nil' end as ishubspoke FROM " + "session_site ss, " + ( isFinalized ? " SESSION_SITE_PRODUCT_FINALIZED ssp " : " SESSION_SITE_PRODUCT ssp " ) + "WHERE ss.session_id=? AND ssp.session_id=? AND ssp.product_id=?" , sessionId , sessionId , productId ); SqlRowSet rs1 = jdbcTemplate.queryForRowSet ( "SELECT " + "ss.site_id, " + "case " + "when (select i8_check from session_site_product where site_id = ss.site_id and session_id= "+sessionId+" and product_id = "+productId+")='Y' then 'Y' " + "else 'N' end as i8Flag FROM " + "session_site ss " + " WHERE ss.session_id=? " , sessionId ); SqlRowSet rs2 = jdbcTemplate.queryForRowSet ( "SELECT " + "ss.site_id, "+ " case " + "when (select count(1) from hub_spoke_network_mapping where hsn_site_id = ss.site_id and is_active=1)>0 then 'Y' " + "else 'N' end as flagofhubspoke FROM " + "session_site ss " + "WHERE ss.session_id= "+sessionId ); while (rs.next ( )) { sites.add ( rs.getInt ( "site_id" ) ); siteFlags.add ( rs.getString ( "ishubspoke" ) ); } while (rs1.next ( )) { i8Flag.add ( rs1.getString ( "i8Flag" ) ); } while (rs2.next ( )) { hubspokeFlag.add(rs2.getString("flagofhubspoke")); } dto.setSiteList ( sites ); dto.setSiteFlags ( siteFlags ); dto.setHubspokeFlag(hubspokeFlag); dto.setI8Flag(i8Flag); } catch ( Exception ex ) { LOGGER.error ( "Exception in GetHubSpokeFlags for sessionId : " + sessionId + " and ProductId : " + productId ); LOGGER.error ( "Exception message is: " + ex.getMessage ( ) ); LOGGER.error ( "Exception stacktrace is: " + Arrays.toString ( ex.getStackTrace ( ) ) ); } return dto; } @Override public HashMap hubSiteDataExport ( Long networkId ) { HashMap map = new HashMap<> ( ); try { SqlRowSet siteIds = jdbcTemplate.queryForRowSet ( "SELECT HSN_SITE_ID \"siteId\" FROM HUB_SPOKE_NETWORK_MAPPING WHERE HSN_NETWORK_ID=? AND IS_ACTIVE=1" , networkId ); while (siteIds.next ( )) { Integer siteId = siteIds.getInt ( "siteId" ); String sheetName = jdbcTemplate.queryForObject ( "SELECT SITE_SHORT_NAME FROM SITE WHERE SITE_ID = ?" , new Object[]{siteId} , String.class ); SqlRowSet rS = jdbcTemplate.queryForRowSet ( "select hnpm.HSN_PRODUCT_MAP_NAME \"productMap\",vdv.SUPPLIER_DESC \"vendor\",pu.FIRST_NAME|| ' ' || pu.LAST_NAME as \"createdBy\"," + "hnpm.UPDATED_DATE \"createdDate\",hld.LEVEL1_HIERARCHY \"l1\",hld.LEVEL2_HIERARCHY \"l2\",hld.LEVEL3_HIERARCHY \"l3\",SUBSTR(hld.LEVEL4_HIERARCHY,1, INSTR(hld.LEVEL4_HIERARCHY ,'-')-2) \"l4\"," + "SUBSTR(hld.LEVEL4_HIERARCHY, INSTR(hld.LEVEL4_HIERARCHY ,'-')+1) \"l4des\",hnhm.THRESHOLD_VALUE \"demandThreshold\" from HS_NETWORK_HUB hnh left join HS_NETWORK_PRODUCT_MAP " + "hnpm on hnh.NETWORK_HUB_ID=hnpm.NETWORK_HUB_ID and hnpm.IS_ACTIVE=1 left join HS_NETWORK_HIERARCHY_MAP hnhm on hnpm.HSN_PRODUCT_MAP_ID=hnhm.HSN_PRODUCT_MAP_ID and hnhm.IS_SELECTED=1 and hnhm.IS_ACTIVE=1 left join PULSE_USER pu on " + "hnpm.UPDATED_BY=pu.USER_ID left join HIERARCHY_LEVELS_DESC hld on hnhm.HEIRARCHY_ID=hld.HIERARCHY_ID left join VENDOR_DATA_V vdv on hnpm.SUPPLIER_NUMBER=vdv.SUPPLIER_NUMBER " + "where hnh.HUB_SITE_ID=? and hnh.IS_ACTIVE=1 ORDER BY hnpm.HSN_PRODUCT_MAP_NAME ASC" , siteId ); map.put ( sheetName , rS ); } } catch ( Exception ex ) { LOGGER.error ( "Exception in Getting HubSiteData for networkId=" + networkId ); LOGGER.error ( "Exception message is:" + ex.getMessage ( ) ); LOGGER.error ( "Exception stacktrace is:" + ex.getStackTrace ( ) ); } return map; } @Override public List deleteProductMap ( Integer networkId , Integer hubSiteId , Integer productMapId , Integer siteId , Integer userId , Integer roleId ) { List networkDetail = null; try { // fetching old selected product ids for reverting them to recommended List productIds = getSelectedProductIds ( hubSiteId , productMapId ); String sql1 = "UPDATE HS_NETWORK_HIERARCHY_MAP SET IS_ACTIVE = ? WHERE HSN_PRODUCT_MAP_ID = ? AND NETWORK_HUB_ID = ? AND IS_ACTIVE = ?"; jdbcTemplate.update ( sql1 , 0 , productMapId , hubSiteId , 1 ); String sql2 = "UPDATE HS_NETWORK_PRODUCT_MAP SET IS_ACTIVE = ?, UPDATED_BY = ?, UPDATED_DATE = ? WHERE HSN_PRODUCT_MAP_ID = ? AND NETWORK_HUB_ID = ? AND IS_ACTIVE = ?"; jdbcTemplate.update ( sql2 , 0 , userId , java.sql.Date.valueOf ( java.time.LocalDate.now ( ) ) , productMapId , hubSiteId , 1 ); networkDetail = getNetworkById ( networkId , null ); //reverting old product ids to recommended after deletion revertProductsToRecommended ( siteId , productIds , null , userId ); } catch ( Exception ex ) { LOGGER.error ( "Exception in deleting product map for ProductMapId {}" , productMapId ); LOGGER.error ( "Exception message is:" + ex.getMessage ( ) ); } return networkDetail; } @Override public List renameProductMap ( Integer networkId , Integer hubSiteId , Integer productMapId , String newProductMapName , Integer userId , Integer roleId ) { List networkDetail = null; try { String sql = "UPDATE HS_NETWORK_PRODUCT_MAP SET HSN_PRODUCT_MAP_NAME = ?, UPDATED_BY = ?, UPDATED_DATE = ? WHERE HSN_PRODUCT_MAP_ID = ? AND NETWORK_HUB_ID = ? AND IS_ACTIVE = ?"; jdbcTemplate.update ( sql , newProductMapName , userId , java.sql.Date.valueOf ( java.time.LocalDate.now ( ) ) , productMapId , hubSiteId , 1 ); HubSpokeWidgetData hubSpokeWidgetData = new HubSpokeWidgetData ( ); hubSpokeWidgetData.setNetworkId ( networkId ); networkDetail = getNetwork ( hubSpokeWidgetData ); } catch ( Exception ex ) { LOGGER.error ( "Exception in Renaming product map for ProductMapId =" + productMapId ); LOGGER.error ( "Exception message is: {}" , ex.getMessage ( ) ); } return networkDetail; } @Override public List getAllNetworkSites ( ) { List list = new ArrayList<> ( ); try { String sql = "SELECT DISTINCT HSN_SITE_ID FROM HUB_SPOKE_NETWORK_MAPPING WHERE IS_ACTIVE = 1"; SqlRowSet rs = jdbcTemplate.queryForRowSet ( sql ); while (rs.next ( )) { list.add ( rs.getInt ( 1 ) ); } } catch ( Exception ex ) { LOGGER.error ( "Exception in GetAllNetworkSites" ); LOGGER.error ( "Exception message is:" + ex.getMessage ( ) ); LOGGER.error ( "Exception stacktrace is:" + ex.getStackTrace ( ) ); } return list; } private void deleteSiteFromNetwork ( Integer siteId , Integer networkId , Integer userId ) { try { //revertData holds hierarchyIds that to be reverted to original position List revertData = prepareRevertObject ( networkId , siteId , userId ); jdbcTemplate.update ( "UPDATE HS_NETWORK_HIERARCHY_MAP SET IS_ACTIVE=0 WHERE NETWORK_HUB_ID IN (SELECT DISTINCT NETWORK_HUB_ID FROM HS_NETWORK_HIERARCHY_V " + "WHERE HUB_SITE_ID=? OR SPOKE_SITE_ID=?) AND IS_ACTIVE=1" , siteId , siteId ); jdbcTemplate.update ( "UPDATE HS_NETWORK_PRODUCT_MAP SET IS_ACTIVE=0, UPDATED_BY=?, UPDATED_DATE=? WHERE NETWORK_HUB_ID IN (SELECT NETWORK_HUB_ID FROM " + "HS_NETWORK_HUB WHERE NETWORK_ID=? AND HUB_SITE_ID=? AND IS_ACTIVE=1) AND IS_ACTIVE=1" , userId , java.sql.Date.valueOf ( java.time.LocalDate.now ( ) ) , networkId , siteId ); jdbcTemplate.update ( "UPDATE HS_NETWORK_SPOKE SET IS_ACTIVE=0 WHERE SPOKE_SITE_ID=? AND IS_ACTIVE=1" , siteId ); jdbcTemplate.update ( "UPDATE HS_NETWORK_HUB SET IS_ACTIVE=0 WHERE NETWORK_ID=? AND HUB_SITE_ID=? AND IS_ACTIVE=1" , networkId , siteId ); jdbcTemplate.update ( "UPDATE HUB_SPOKE_NETWORK_MAPPING SET MODIFIED_BY=?, MODIFIED_DATE=?, IS_ACTIVE=0 WHERE HSN_NETWORK_ID=? AND HSN_SITE_ID=? AND IS_ACTIVE=1" , userId , java.sql.Date.valueOf ( java.time.LocalDate.now ( ) ) , networkId , siteId ); jdbcTemplate.update ( "UPDATE HUB_SPOKE_NETWORK SET MODIFIED_BY=?, MODIFIED_DATE=? WHERE HS_NETWORK_ID=? AND IS_ACTIVE=1" , userId , java.sql.Date.valueOf ( java.time.LocalDate.now ( ) ) , networkId ); revertSelectedIdListToRecommended ( revertData , userId ); } catch ( Exception ex ) { LOGGER.error ( "Exception in deleteSiteFromNetwork for siteId: " + siteId ); LOGGER.error ( "Exception message is: " + ex.getMessage ( ) ); LOGGER.error ( "Exception stacktrace is: " + ex.getStackTrace ( ) ); throw new RuntimeException ( ex.getMessage ( ) ); } } private void insertSitesInNetwork ( List siteIds , Integer userId , Integer networkId ) { for (Integer siteId : siteIds) { int isNetworkMapExist = checkExistingNetworkMapping ( siteId , networkId ); if ( isNetworkMapExist == 0 ) { jdbcTemplate.update ( "INSERT INTO hub_spoke_network_mapping (HSN_NETWORK_ID,HSN_SITE_ID,CREATED_BY,CREATED_DATE,MODIFIED_BY,MODIFIED_DATE,IS_ACTIVE) " + "VALUES(?,?,?,?,?,?,?)" , networkId , siteId , userId , java.sql.Date.valueOf ( java.time.LocalDate.now ( ) ) , 0 , null , 1 ); } else { String dateFormat = "'yyyy/mm/dd'"; String sql = "update hub_spoke_network_mapping set CREATED_BY = " + userId + ", MODIFIED_BY = " + userId + ", MODIFIED_DATE = TO_DATE('" + java.sql.Date.valueOf ( java.time.LocalDate.now ( ) ) + "'," + dateFormat + "), IS_ACTIVE=" + 1 + " where HSN_NETWORK_ID = " + networkId + " and HSN_SITE_ID = " + siteId + ""; jdbcTemplate.update ( sql ); } } } private int checkExistingNetworkMapping ( Integer siteId , Integer networkId ) { try { SqlRowSet rs = jdbcTemplate.queryForRowSet ( "select HSN_SITE_ID from hub_spoke_network_mapping where HSN_SITE_ID = " + siteId + " and HSN_NETWORK_ID = " + networkId + "" ); if ( rs.next ( ) ) return rs.getInt ( "HSN_SITE_ID" ); } catch ( Exception e ) { LOGGER.error ( "checkExistingNetworkMapping() for siteId {} " , siteId ); } return 0; } @Override public void revertSelectedIdListToRecommended ( List list , Integer userId ) { try { for (HubSiteProducts obj : list) { revertProductsToRecommended ( obj.getHubSiteId ( ) , obj.getHierarchyIds ( ) , null , userId ); } } catch ( Exception e ) { LOGGER.error ( "Error in revertSelectedIdListToRecommended(), Reverting Hierarchy Ids failed" ); throw new RuntimeException ( e.getMessage ( ) ); } } @Override public List getSelectedProductIds ( Integer networkHubId , Integer productMapId ) { try { String sql = "select HEIRARCHY_ID from HS_NETWORK_HIERARCHY_MAP where IS_ACTIVE = 1 and IS_SELECTED = 1 and network_hub_id = " + networkHubId; if ( productMapId != null ) sql += " and HSN_PRODUCT_MAP_ID = " + productMapId; return jdbcTemplate.queryForList ( sql , Integer.class ); } catch ( Exception e ) { LOGGER.error ( "Error in getSelectedProductIds(), networkHubId : {}, productMapId : {}" , networkHubId , productMapId ); return new ArrayList<> ( ); } } @Override public void revertProductsToRecommended ( Integer siteId , List productIdList , Integer sessionId , Integer userId ) { productIdList.forEach ( productId -> { try { parametersDAO.revertToRecommended ( siteId , productId , sessionId , userId );// productId & nodeId are same term } catch ( PulseException e ) { LOGGER.error ( "Error in revertProductsToRecommended()" , e ); throw new RuntimeException ( e.getMessage ( ) ); } } ); } @Override public List prepareRevertObject ( Integer networkId , Integer siteId , Integer userId ) { List list = new ArrayList<> ( ); try { String sql = "SELECT NETWORK_HUB_ID, HUB_SITE_ID FROM HS_NETWORK_HUB WHERE IS_ACTIVE = 1 AND NETWORK_ID = " + networkId; if ( siteId != null ) sql += " AND HUB_SITE_ID = " + siteId; SqlRowSet rs = jdbcTemplate.queryForRowSet ( sql ); while (rs.next ( )) { HubSiteProducts obj = new HubSiteProducts ( ); obj.setHubSiteId ( rs.getInt ( "HUB_SITE_ID" ) ); obj.setNetworkHubId ( rs.getInt ( "NETWORK_HUB_ID" ) ); obj.setNetworkId ( networkId ); obj.setHierarchyIds ( getSelectedProductIds ( obj.getNetworkHubId ( ) , null ) ); list.add ( obj ); } } catch ( Exception e ) { LOGGER.error ( "Error in prepareRevertObject() networkId : {}, siteId : {}" , networkId , siteId ); throw new RuntimeException ( e.getMessage ( ) ); } return list; } @Override public int getNetworkType ( Integer siteId ) { try { SqlRowSet rs = jdbcTemplate.queryForRowSet ( "select hsn.HS_NETWORK_TYPE_ID as \"networkType\" from HUB_SPOKE_NETWORK hsn, " + "hub_spoke_network_mapping hsnm where hsnm.IS_ACTIVE = 1 and hsn.IS_ACTIVE = 1 \n" + "and hsnm.HSN_NETWORK_ID = hsn.HS_NETWORK_ID and hsnm.HSN_SITE_ID = " + siteId ); if ( rs.next ( ) ) return rs.getInt ( "networkType" ); } catch ( Exception e ) { LOGGER.error ( "getNetworkType() for siteId {} " , siteId ); } return 0; } @Override public List getAllNetworkNames ( ) { List list = new ArrayList<> ( ); try { String sql = "SELECT HS_NETWORK_NAME FROM HUB_SPOKE_NETWORK"; SqlRowSet rs = jdbcTemplate.queryForRowSet ( sql ); while (rs.next ( )) { list.add ( rs.getString ( 1 ) ); } } catch ( Exception ex ) { LOGGER.error ( "Exception in GetAllNetworkNames" ); LOGGER.error ( "Exception message is:" + ex.getMessage ( ) ); LOGGER.error ( "Exception stacktrace is:" + ex.getStackTrace ( ) ); throw new RuntimeException ( ex.getMessage ( ) ); } return list; } @Override public List>> getSpokeSiteIds ( List Ids , String type ) { List>> mapList = new ArrayList<> ( ); try { String sql = ""; for (Integer Id :Ids) { HashMap> map = new HashMap<> ( ); List list = new ArrayList<> ( ); if (type.equals ("network")) { sql = " select HUB_SITE_ID FROM HS_NETWORK_HUB WHERE NETWORK_ID = " + Id + " and is_active = 1 "; SqlRowSet rs = jdbcTemplate.queryForRowSet ( sql ); IterationLoop ( map , list , rs , Id ); } mapList.add ( map ); } } catch ( Exception ex ) { LOGGER.error ( "Exception in GetSpokeSiteIds" ); LOGGER.error ( "Exception message is:" + ex.getMessage ( ) ); } return mapList; } private void IterationLoop ( HashMap> map , List list , SqlRowSet rs , Integer Id ) { while (rs.next ( )) { list.add ( rs.getInt ( 1 ) ); map.put ( Id , list ); } } }