package com.napa.pulse.dao.impl; import com.google.gson.Gson; import com.google.gson.GsonBuilder; import com.itextpdf.text.*; import com.itextpdf.text.pdf.PdfPCell; import com.itextpdf.text.pdf.PdfPTable; import com.itextpdf.text.pdf.PdfWriter; import com.napa.pulse.dao.interfaces.AcoDAO; import com.napa.pulse.dao.interfaces.ProductGroupDAO; import com.napa.pulse.dao.interfaces.SessionDAO; import com.napa.pulse.dao.interfaces.SiteGroupDAO; import com.napa.pulse.dto.*; import com.napa.pulse.entity.pulseui.*; import com.napa.pulse.entity.security.User; import com.napa.pulse.enums.AccessItem; import com.napa.pulse.exception.PulseException; import com.napa.pulse.rowmappers.HierarchyItemMapper; import com.napa.pulse.rowmappers.IntegerRowMapper; import com.napa.pulse.rowmappers.SessionSiteParameterDTOMapper; import com.napa.pulse.rowmappers.StructVarrayRowMapper; import com.napa.pulse.service.interfaces.AdminService; import com.napa.pulse.service.interfaces.CommonService; import com.napa.pulse.service.interfaces.ExportService; import com.napa.pulse.utils.*; import com.napa.pulse.utils.messages.MessageHandler; import org.apache.commons.lang.StringUtils; import org.joda.time.DateTime; import org.joda.time.Days; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.beans.factory.annotation.Value; import org.springframework.boot.json.GsonJsonParser; import org.springframework.dao.EmptyResultDataAccessException; import org.springframework.jdbc.core.BatchPreparedStatementSetter; import org.springframework.jdbc.core.BeanPropertyRowMapper; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.namedparam.MapSqlParameterSource; import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate; import org.springframework.jdbc.core.namedparam.SqlParameterSource; import org.springframework.jdbc.core.simple.SimpleJdbcCall; import org.springframework.jdbc.support.GeneratedKeyHolder; import org.springframework.jdbc.support.KeyHolder; import org.springframework.jdbc.support.rowset.SqlRowSet; import org.springframework.scheduling.annotation.Async; import org.springframework.stereotype.Repository; import javax.servlet.http.HttpServletResponse; import java.io.IOException; import java.io.PrintWriter; import java.math.BigDecimal; import java.net.URL; import java.sql.*; import java.text.*; import java.util.Date; import java.util.List; import java.util.*; import java.util.concurrent.*; import java.util.stream.Collectors; import java.util.stream.IntStream; /** * @author PCTR98154 */ @Repository public class SessionDAOImpl implements SessionDAO { private static final Logger LOGGER = LoggerFactory.getLogger ( SessionDAOImpl.class ); @Autowired private JdbcTemplate jdbcTemplate; @Autowired private MessageHandler messageHandler; @Autowired private CommonService commonService; @Autowired private ExportService excelExporter; @Autowired private SiteQueueMap siteQueueMap; @Autowired private SiteGroupDAO siteGroupDAO; @Autowired private AcoDAO acoDAO; @Autowired private AdminService adminService; @Autowired private ProductGroupDAO productGroupDAO; private final SimpleDateFormat sdf = new SimpleDateFormat ( "yyyy-MM-dd'T'HH:mm:ss.SSSZ" ); @Value ( "${defer.session.site.limit}" ) private int deferSiteLimit; @Override public List> getSessionList ( String[] statusTypes , Integer[] siteIds , Integer[] siteGroups , Integer[] statusIds , String[] createdDates , String[] ExpirationDates, Integer[] createdByIds , User user , String userAccessSites , String[] finalizeDateArray ) throws Exception { try { StringBuilder dynamicQuery = new StringBuilder ( "SELECT TBL.SESSION_ID AS \"sessionId\", " + "CASE WHEN STATUS_CD = 'O' THEN 'false' ELSE 'true' END AS \"finalizeStatus\"," + "STATUS." + ( ( "en".equals ( user.getLanguage ( ) ) || user.getLanguage ( ) == null ) ? "STATUS_DESCRIPTION" : "STATUS_DESCRIPTION_FR" ) + " AS \"statusdesc\", STATUS.STATUS_ID as \"statusId\", " + " TBL.CREATED_DATE AS \"createdDate\", " + "to_char( TBL.EXPIRATION_DATE , 'MM/DD/YYYY') AS \"expirationDate\"," + " TRIM(MU.LAST_NAME || ',' || MU.FIRST_NAME) AS \"createdUser\", " + " TBL.SESSION_DESC AS \"siteName\", " + " TBL.SESSION_TYPE_CODE AS \"sessionTypeCode\", " + " (SELECT COUNT(SITE_ID) FROM SESSION_SITE SS WHERE TBL.SESSION_ID = SS.SESSION_ID) AS \"siteCount\", " + " TBL.SESSION_COMMENT AS \"sessionComment\", " + " TBL.FINALIZE_DATE AS \"finalizeDate\" " + " FROM PULSE_SESSION TBL " + " INNER JOIN SESSION_STATUS STATUS ON STATUS.STATUS_ID = TBL.STATUS_ID " ); if ( createdDates != null && createdDates.length == 2 ) { if ( createdDates[0] == null && createdDates[1] != null ) { String date1 = createdDates[1].substring ( 0 , 10 ) + " 23:59:59"; dynamicQuery.append ( " AND TBL.CREATED_DATE < TO_DATE('" + date1 + "', 'mm-dd-yyyy HH24:MI:SS') " ); } else if ( createdDates[0] != null && createdDates[1] == null ) { String date0 = createdDates[0].substring ( 0 , 10 ) + " 00:00:00"; dynamicQuery.append ( " AND TBL.CREATED_DATE > TO_DATE('" + date0 + "', 'mm-dd-yyyy HH24:MI:SS') " ); } else if ( createdDates[0] != null && createdDates[1] != null ) { String date0 = createdDates[0].substring ( 0 , 10 ) + " 00:00:00"; String date1 = createdDates[1].substring ( 0 , 10 ) + " 23:59:59"; dynamicQuery.append ( " AND TBL.CREATED_DATE BETWEEN TO_DATE('" + date0 + "', 'mm-dd-yyyy HH24:MI:SS') AND TO_DATE('" + date1 + "', 'mm-dd-yyyy HH24:MI:SS') " ); } } if ( statusIds != null && statusIds.length > 0 ) { dynamicQuery.append ( " AND STATUS.STATUS_ID IN (" ); for (Integer statusId : statusIds) { dynamicQuery.append ( statusId + "," ); } dynamicQuery.append ( PulseConstants.DYNAMIC_QUERY_END_INTEGER ); } if ( createdByIds != null && createdByIds.length > 0 ) { dynamicQuery.append ( " AND TBL.CREATED_USER_ID IN ( " ); for (Integer createdById : createdByIds) { dynamicQuery.append ( createdById + "," ); } dynamicQuery.append ( PulseConstants.DYNAMIC_QUERY_END_INTEGER ); } if ( statusTypes != null && statusTypes.length > 0 ) { dynamicQuery.append ( " AND STATUS.STATUS_CD IN ( " ); for (String statusType : statusTypes) { dynamicQuery.append ( "'" + statusType + "'," ); } dynamicQuery.append ( "'@')" ); } // Anything that is has been finalized, only show for 120 days. dynamicQuery.append ( " AND TBL.CREATED_DATE > (SYSDATE-120) " ); dynamicQuery.append ( " INNER JOIN SESSION_SITE SS ON TBL.SESSION_ID = SS.SESSION_ID " ); // There can be three conditions for the site and site groups // combinations // Condition 1: Both Sites and Site Groups are present in search if ( siteIds != null && siteIds.length > 0 && siteGroups != null && siteGroups.length > 0 ) { dynamicQuery.append ( " AND (SS.SITE_ID IN (" ); for (Integer siteId : siteIds) { dynamicQuery.append ( siteId + "," ); } dynamicQuery.append ( "-1) AND SS.SITE_ID in (" + "SELECT SITE_ID FROM SITE_GROUP_MAPPING WHERE SITE_GROUP_ID IN (" ); for (Integer siteGroup : siteGroups) { dynamicQuery.append ( siteGroup + "," ); } dynamicQuery.append ( "-1))) " ); } // Condition 2: Only sites no siteGroups else if ( siteIds != null && siteIds.length > 0 ) { dynamicQuery.append ( " AND SS.SITE_ID IN (" ); for (Integer siteId : siteIds) { dynamicQuery.append ( siteId + "," ); } dynamicQuery.append ( PulseConstants.DYNAMIC_QUERY_END_INTEGER ); } // Condition 3: Only siteGroups no sites else if ( siteGroups != null && siteGroups.length > 0 ) { dynamicQuery .append ( " AND SS.SITE_ID in (" + "SELECT SITE_ID FROM SITE_GROUP_MAPPING WHERE SITE_GROUP_ID IN (" ); for (Integer siteGroup : siteGroups) { dynamicQuery.append ( siteGroup + "," ); } dynamicQuery.append ( "-1)) " ); } dynamicQuery.append ( " INNER JOIN PULSE_USER MU ON MU.USER_ID = TBL.CREATED_USER_ID " + " INNER JOIN SITE S ON S.SITE_ID = SS.SITE_ID " ); List> updatedSessionList = new ArrayList<> ( ); if ( user.getRoleId ( ) != ROLE.ADMIN.getNumVal ( ) && user.getRoleId ( ) != ROLE.XO.getNumVal ( ) ) // if // the // user // is // not // Admin { if ( userAccessSites.length ( ) > 0 ) { dynamicQuery.append ( " AND (S.SITE_ID, 0) IN (" + userAccessSites + ") " ); } else { return updatedSessionList; } String accessQuery = "SELECT DISTINCT(ROLE_ID) FROM ROLE_ACCESS WHERE ROLE_ID = ? AND ACCESS_ITEM_ID = ?"; SqlRowSet minMaxAccess = jdbcTemplate.queryForRowSet ( accessQuery , user.getRoleId ( ) , 131 ); // 131 Access_item - min/max sessions visibility if ( ! minMaxAccess.next ( ) ) dynamicQuery.append ( " AND TBL.SESSION_TYPE_CODE != 'MM' " ); // for those roles who are not having 131 access SqlRowSet standardAccess = jdbcTemplate.queryForRowSet ( accessQuery , user.getRoleId ( ) , 143 ); // 143 Access_item - standard sessions visibility if ( ! standardAccess.next ( ) ) dynamicQuery.append ( " AND TBL.SESSION_TYPE_CODE != 'SS' " ); // for those roles who are not having 143 access SqlRowSet storeClosingAccess = jdbcTemplate.queryForRowSet ( accessQuery , user.getRoleId ( ) , 144 ); // 144 Access_item - store closing sessions visibility if ( ! storeClosingAccess.next ( ) ) dynamicQuery.append ( " AND TBL.SESSION_TYPE_CODE != 'SC' " ); // for those roles who are not having 144 access SqlRowSet acoAccess = jdbcTemplate.queryForRowSet ( accessQuery , user.getRoleId ( ) , 145 ); // 145 Access_item - ACO sessions visibility if ( ! acoAccess.next ( ) ) dynamicQuery.append ( " AND TBL.SESSION_TYPE_CODE != 'AC' " ); // for those roles who are not having 145 access SqlRowSet dollarLimitAccess = jdbcTemplate.queryForRowSet ( accessQuery , user.getRoleId ( ) , 146 ); // 146 Access_item - dollar limit sessions visibility if ( ! dollarLimitAccess.next ( ) ) dynamicQuery.append ( " AND TBL.SESSION_TYPE_CODE != 'DL' " ); // for those roles who are not having 146 access SqlRowSet invAccess = jdbcTemplate.queryForRowSet ( accessQuery , user.getRoleId ( ) , 147 ); // 147 Access_item - Inventory seeding sessions visibility if ( ! invAccess.next ( ) ) dynamicQuery.append ( " AND TBL.SESSION_TYPE_CODE != 'IS' " ); // for those roles who are not having 147 access SqlRowSet customSkuAccess = jdbcTemplate.queryForRowSet ( accessQuery , user.getRoleId ( ) , 148 ); // 148 Access_item - custom sku limit sessions visibility if ( ! customSkuAccess.next ( ) ) dynamicQuery.append ( " AND TBL.SESSION_TYPE_CODE != 'PS' " ); // for those roles who are not having 148 access } if ( finalizeDateArray != null && finalizeDateArray.length == 2 ) { if ( finalizeDateArray[0] != null && finalizeDateArray[1] == null ) { dynamicQuery.append ( " where FINALIZE_DATE >= to_date('" + finalizeDateArray[0] + "', 'MM-DD-YYYY') " ); } else if ( finalizeDateArray[0] == null && finalizeDateArray[1] != null ) { dynamicQuery.append ( " where FINALIZE_DATE <= to_date('" + finalizeDateArray[1] + " 23:59:59', 'MM-DD-YYYY HH24:MI:SS') " ); } else { dynamicQuery.append ( " where FINALIZE_DATE >= to_date('" + finalizeDateArray[0] + " 00:00:00', 'MM-DD-YYYY HH24:MI:SS') " + "and FINALIZE_DATE <= to_date('" + finalizeDateArray[1] + " 23:59:59', 'MM-DD-YYYY HH24:MI:SS') " ); } } dynamicQuery.append ( " GROUP BY " + " TBL.SESSION_ID, " + " TBL.SESSION_TYPE_CODE," + " STATUS_CD, " + " STATUS." + ( ( "en".equals ( user.getLanguage ( ) ) || user.getLanguage ( ) == null ) ? "STATUS_DESCRIPTION" : "STATUS_DESCRIPTION_FR" ) + ", " + " STATUS.STATUS_ID, TBL.CREATED_DATE, TBL.EXPIRATION_DATE , " + " TBL.SESSION_COMMENT, " + " TBL.SESSION_DESC, " + " MU.FIRST_NAME, " + " MU.LAST_NAME, TBL.FINALIZE_DATE " ); List> sessionDetailsList = jdbcTemplate.queryForList ( dynamicQuery.toString ( ) ); for (Map map : sessionDetailsList) { Date createdDate = (Date) map.get ( "createdDate" ); map.put ( "createdDate" , sdf.format ( createdDate ) ); Date finalizeDate = (Date) map.get ( "finalizeDate" ); if ( finalizeDate != null ) { map.put ( "finalizeDate" , sdf.format ( finalizeDate ) ); } } if ( user.getRoleId ( ) != ROLE.ADMIN.getNumVal ( ) && user.getRoleId ( ) != ROLE.XO.getNumVal ( ) ) { for (Map map : sessionDetailsList) { int sessionId = Integer.parseInt ( map.get ( "sessionId" ).toString ( ) ); if ( validateUserSessionAccess ( sessionId , user.getUserId ( ) , userAccessSites ) ) { map.put ( "isEnable" , true ); } else { map.put ( "isEnable" , false ); } updatedSessionList.add ( map ); } } else { for (Map map : sessionDetailsList) { map.put ( "isEnable" , true ); updatedSessionList.add ( map ); } } return updatedSessionList; } catch ( Exception e ) { LOGGER.error ( "getSessionList" , e ); throw new RuntimeException ( e ); } } @Override public List> getAllSessions ( User user , String userAccessSites ) { try { StringBuilder dynamicQuery = new StringBuilder ( "SELECT TBL.SESSION_ID AS \"sessionId\", " + "CASE WHEN STATUS_CD = 'O' THEN 'false' ELSE 'true' END AS \"finalizeStatus\"," + "STATUS." + ( ( "en".equals ( user.getLanguage ( ) ) || user.getLanguage ( ) == null ) ? "STATUS_DESCRIPTION" : "STATUS_DESCRIPTION_FR" ) + " AS \"statusdesc\", STATUS.STATUS_ID as \"statusId\", " + " TBL.CREATED_DATE AS \"createdDate\", " + " TRIM(MU.LAST_NAME || ',' || MU.FIRST_NAME) AS \"createdUser\", " + " TBL.SESSION_DESC AS \"siteName\", " + " (SELECT COUNT(SITE_ID) FROM SESSION_SITE SS WHERE TBL.SESSION_ID = SS.SESSION_ID) AS \"siteCount\", " + " TBL.SESSION_COMMENT AS \"sessionComment\", " + " TBL.FINALIZE_DATE AS \"finalizeDate\" " + " FROM PULSE_SESSION TBL " + " INNER JOIN SESSION_STATUS STATUS ON STATUS.STATUS_ID = TBL.STATUS_ID " ); dynamicQuery.append ( " INNER JOIN SESSION_SITE SS ON TBL.SESSION_ID = SS.SESSION_ID " ); dynamicQuery.append ( " INNER JOIN PULSE_USER MU ON MU.USER_ID = TBL.CREATED_USER_ID " + " INNER JOIN SITE S ON S.SITE_ID = SS.SITE_ID " ); dynamicQuery.append ( " WHERE CREATED_DATE > SYSDATE - 90 " ); List> updatedSessionList = new ArrayList<> ( ); if ( user.getRoleId ( ) != ROLE.ADMIN.getNumVal ( ) && user.getRoleId ( ) != ROLE.XO.getNumVal ( ) ) // if // the // user // is // not // Admin { if ( userAccessSites.length ( ) > 0 ) { dynamicQuery.append ( " AND (S.SITE_ID, 0) IN (" + userAccessSites + ") " ); } else { return updatedSessionList; } if ( user.getRoleId ( ) == ROLE.MIN_MAX_REVIEW.getNumVal ( ) ) { dynamicQuery.append ( " AND TBL.SESSION_TYPE_CODE = 'MM'" ); } else if ( user.getRoleId ( ) != ROLE.TRAINED_OWNER_WITH_MINMAX.getNumVal ( ) && user.getRoleId ( ) != ROLE.CERTIFIED_OWNER_WITH_MINMAX.getNumVal ( ) ) { dynamicQuery.append ( " AND TBL.SESSION_TYPE_CODE != 'MM'" ); } } dynamicQuery.append ( " GROUP BY " + " TBL.SESSION_ID, " + " STATUS_CD, " + " STATUS." + ( ( "en".equals ( user.getLanguage ( ) ) || user.getLanguage ( ) == null ) ? "STATUS_DESCRIPTION" : "STATUS_DESCRIPTION_FR" ) + ", " + " STATUS.STATUS_ID, TBL.CREATED_DATE, " + " TBL.SESSION_COMMENT, " + " TBL.SESSION_DESC, " + " MU.FIRST_NAME, " + " MU.LAST_NAME, TBL.FINALIZE_DATE " ); List> sessionDetailsList = jdbcTemplate.queryForList ( dynamicQuery.toString ( ) ); if ( user.getRoleId ( ) != ROLE.ADMIN.getNumVal ( ) && user.getRoleId ( ) != ROLE.XO.getNumVal ( ) ) { for (Map map : sessionDetailsList) { int sessionId = Integer.parseInt ( map.get ( "sessionId" ).toString ( ) ); if ( validateUserSessionAccess ( sessionId , user.getUserId ( ) , userAccessSites ) ) { map.put ( "isEnable" , true ); } else { map.put ( "isEnable" , false ); } updatedSessionList.add ( map ); } } else { for (Map map : sessionDetailsList) { map.put ( "isEnable" , true ); updatedSessionList.add ( map ); } } return updatedSessionList; } catch ( Exception e ) { LOGGER.error ( "getAllSessions" , e ); throw new RuntimeException ( e ); } } @Override public boolean validateUserSessionAccess ( Integer sessionId , Integer userId , String userAccessSites ) { if ( userAccessSites == null || userAccessSites.length ( ) == 0 ) { return false; } else { String sql = "SELECT SITE_ID FROM session_site WHERE (SITE_ID,0) not in (" + userAccessSites + ") and SESSION_ID = ?"; // Check if the user has access to al l the sites inside a session // (No data will // be fetched if true else there will be data) List> siteList = jdbcTemplate.queryForList ( sql , sessionId ); return siteList.size ( ) == 0; } } @Override public ActionResult updateSessionTransmissionStatusId ( Integer transmissionId , String type , String newStatus ) { try { SqlRowSet rs = jdbcTemplate.queryForRowSet ( "select TRANSMISSION_STATUS_ID from " + "TRANSMISSION_STATUS where trim(TRANSMISSION_STATUS_CD)=?" , newStatus ); if ( rs.next ( ) ) { Integer status = rs.getInt ( "TRANSMISSION_STATUS_ID" ); if ( "return".equals ( type ) ) { jdbcTemplate.update ( "UPDATE TRANSMISSION SET RETURN_STATUS_ID = ? where TRANSMISSION_ID=?" , status , transmissionId ); } else { jdbcTemplate.update ( "UPDATE TRANSMISSION SET ORDER_STATUS_ID = ? where TRANSMISSION_ID=?" , status , transmissionId ); } return new ActionResult ( true ); } else { return new ActionResult ( false ); } } catch ( Exception e ) { LOGGER.error ( "updateSessionTransimissionStatusId" , e ); throw new RuntimeException ( e ); } } @Override public List getSessionDetails ( User user , Integer sessionId ) { try { SessionDetailsDTO sessionDetailsDTO = new SessionDetailsDTO ( ); // returning object in list to not break UI code from previous response List sessionDetailsList = new ArrayList<> ( ); SqlRowSet rs = jdbcTemplate.queryForRowSet ( "SELECT TBL.SESSION_ID AS \"sessionId\",TBL.EXCLUDE_DISPLAY_ITEM AS \"displayQty1\", TBL.NEW_NUMBER_DAYS as \"dayCount\", TBL.INCLUDE_HS_ORDER as \"includeHSOrder\",TBL.INCLUDE_HS_RETURN as \"includeHSReturn\", TBL.MIN_MAX_SALES_SETTING as \"minMaxSales\" ,TBL.MIN_MAX_MIN_CALC_SETTING as \"minMaxMinSetting\", STATUS.STATUS_ID as \"statusId\", " + "CASE WHEN " + ( ( "en".equals ( user.getLanguage ( ) ) || user.getLanguage ( ) == null ) ? "HIGH_LEVEL_DESCRIPTION = 'Open'" : "HIGH_LEVEL_DESCRIPTION_FR = 'Ouvert'" ) + " THEN 'false' ELSE 'true' END AS \"finalizeStatus\", " + "STATUS." + ( ( "en".equals ( user.getLanguage ( ) ) || user.getLanguage ( ) == null ) ? "STATUS_DESCRIPTION" : "STATUS_DESCRIPTION_FR" ) + " AS \"statusDesc\", " + "TO_CHAR(TBL.CREATED_DATE , 'MM/DD/YY HH:MI AM') AS \"createdDate\", " + "(case when (TBL. SESSION_TYPE_CODE = 'SS') then 'standard' else 'custom' end) as \"sessionType\", " + "TRIM(PU.FIRST_NAME || ' ' || PU.LAST_NAME) AS \"createdBy\", PU.USER_ID AS \"createdById\", " + "TBL.SESSION_COMMENT AS \"sessionComment\", EXPIRATION_DATE as \"expirationDate\", SESSION_TYPE_CODE as \"sessionTypeCode\", " + "(select TRIM(FIRST_NAME || ' ' || LAST_NAME) from pulse_user where user_id=TBL.SUBMITTAL_USER_ID) as \"submittalUser\", " + "(case when b.SITE_TYPE_CODE='DC' then 1 else 0 end) as \"includesDCs\", DOLLAR_LIMIT as \"dollarLimit\", " + "(select TRIM(FIRST_NAME || ' ' || LAST_NAME) from pulse_user where user_id=TBL.FINALIZE_USER_ID) as \"finalizedBy\", " + "FINALIZE_DATE as \"finalizedDate\", FINALIZE_USER_ID as \"finalizedById\" , " + " nvl(is_group_code,0) as \"isGroupCode\" " + " FROM PULSE_SESSION TBL " + " INNER JOIN SESSION_STATUS STATUS ON STATUS.STATUS_ID = TBL.STATUS_ID " + " INNER JOIN PULSE_USER PU ON PU.USER_ID = TBL.CREATED_USER_ID " + " left join (select ss.SESSION_ID, s.SITE_TYPE_CODE from SESSION_SITE ss, site s " + " where ss.SITE_ID=s.site_ID and ss.SESSION_ID =? group by ss.SESSION_ID, s.SITE_TYPE_CODE) B on b.SITE_TYPE_CODE='DC' " + " WHERE TBL.SESSION_ID = ?" , sessionId , sessionId ); while (rs.next ( )) { sessionDetailsDTO.setSessionId ( rs.getInt ( "sessionId" ) ); sessionDetailsDTO.setFinalizeStatus ( rs.getString ( "finalizeStatus" ) ); sessionDetailsDTO.setStatusId ( rs.getInt ( "statusId" ) ); sessionDetailsDTO.setStatusDesc ( rs.getString ( "statusDesc" ) ); sessionDetailsDTO.setCreatedDate ( rs.getString ( "createdDate" ) ); sessionDetailsDTO.setSessionType ( rs.getString ( "sessionType" ) ); sessionDetailsDTO.setCreatedBy ( rs.getString ( "createdBy" ) ); sessionDetailsDTO.setCreatedById ( rs.getInt ( "createdById" ) ); sessionDetailsDTO.setSubmittalUser ( rs.getString ( "submittalUser" ) ); sessionDetailsDTO.setExpirationDate ( sdf.format ( rs.getDate ( "expirationDate" ) ) ); sessionDetailsDTO.setIncludesDCs ( rs.getInt ( "includesDCs" ) ); sessionDetailsDTO.setFinalizedById ( rs.getInt ( "finalizedById" ) ); sessionDetailsDTO.setFinalizedBy ( rs.getString ( "finalizedBy" ) ); sessionDetailsDTO.setDollarLimit ( rs.getInt ( "dollarLimit" ) ); sessionDetailsDTO.setSessionTypeCode ( rs.getString ( "sessionTypeCode" ) ); sessionDetailsDTO.setDayCount ( rs.getInt ( "dayCount" ) ); sessionDetailsDTO.setMinMaxSales ( rs.getString ( "minMaxSales" ) ); sessionDetailsDTO.setMinMaxMinSetting ( rs.getInt ( "minMaxMinSetting" ) ); sessionDetailsDTO.setIncludeHubSpokeOrderOption ( rs.getString ( "includeHSOrder" ) == null ? null : rs.getString ( "includeHSOrder" ) ); sessionDetailsDTO.setIncludeHubSpokeReturnOption ( rs.getString ( "includeHSReturn" ) == null ? null : rs.getString ( "includeHSReturn" ) ); sessionDetailsDTO.setDisplayQty1 ( rs.getString ( "displayQty1" ) ); sessionDetailsDTO.setGroupCode ( rs.getBoolean ( "isGroupCode" ) ); Date fd = rs.getDate ( "finalizedDate" ); if ( fd != null ) { sessionDetailsDTO.setFinalizedDate ( sdf.format ( fd ) ); } sessionDetailsList.add ( sessionDetailsDTO ); } return sessionDetailsList; } catch ( Exception e ) { LOGGER.error ( "getSessionDetails" , e ); throw new RuntimeException ( e ); } } @Override public String getSiteGroupName ( int siteGroupId ) { String sql = "select SITE_GROUP_NAME from SITE_GROUP where SITE_GROUP_ID=?"; return jdbcTemplate.queryForObject ( sql , String.class , siteGroupId ); } @Override public String getSiteName ( int siteId ) { String sql = "select SITE_NAME from SITE where SITE_ID=?"; return jdbcTemplate.queryForObject ( sql , String.class , siteId ); } private void rollbackSession ( BigDecimal sessionId ) { if ( sessionId != null ) { jdbcTemplate.update ( "DELETE FROM SESSION_SITE_PARAMETER WHERE SESSION_ID = ? " , sessionId ); jdbcTemplate.update ( "DELETE FROM SESSION_SITE_PRODUCT WHERE SESSION_ID = ? " , sessionId ); jdbcTemplate.update ( "DELETE FROM SESSION_SITE_SYSTEM WHERE SESSION_ID = ? " , sessionId ); jdbcTemplate.update ( "DELETE FROM SESSION_SITE WHERE SESSION_ID = ? " , sessionId ); jdbcTemplate.update ( "DELETE FROM SESSION_HIERARCHY WHERE SESSION_ID = ? " , sessionId ); jdbcTemplate.update ( "DELETE FROM SESSION_HIERARCHY_DETAIL WHERE SESSION_ID = ? " , sessionId ); jdbcTemplate.update ( "DELETE FROM SESSION_MARKET_POSTAL WHERE SESSION_ID = ? " , sessionId ); jdbcTemplate.update ( "DELETE FROM SESSION_SITE_TOPAPP WHERE SESSION_ID = ? " , sessionId ); jdbcTemplate.update ( "DELETE FROM SESSION_PRODUCT WHERE SESSION_ID = ? " , sessionId ); jdbcTemplate.update ( "DELETE FROM SESSION_GROUP_CODE_DETAIL WHERE SESSION_ID = ? " , sessionId ); jdbcTemplate.update ( "DELETE FROM SESSION_GROUP_CODE WHERE SESSION_ID = ? " , sessionId ); jdbcTemplate.update ( "DELETE FROM SESSION_FLEET WHERE SESSION_ID = ? " , sessionId ); jdbcTemplate.update ( "DELETE FROM PULSE_SESSION WHERE SESSION_ID = ? " , sessionId ); } LOGGER.info ( "Create new session| Rolled back sessionId " + sessionId ); } @Override public BigDecimal createNewSession ( User user , Boolean perCarOrder , Boolean includeFleets , Boolean includeSystems , Boolean includeSpecials , Boolean includeObsoletes , Boolean includeOverstocks , Boolean includeSupersede , Boolean includeKeylinesOrders , Boolean includeKeylinesReturns , Boolean includeLessThanStdPkg , Double includeWeekSupplyOption,String weekSupplyOptionName,Boolean useInvestmentCollections , Boolean provideStoreDepth , Boolean includeSoldNotStocked , Boolean includeNewNumbers , Boolean orderAllSkus , Boolean returnAllSKUs , Integer dollarLimit , String comment , Integer siteGroupId , Integer[] sitesArray , ProductGroupOrderReturn[] productGroupsArray , ProductGroupOrderReturn[] groupCodeArray , Integer[] fleetGroupsIds , CustomProductGroup customProductGroup , CustomGroupCodeProductGroup customGroupCodeProductGroupObj , Boolean isGroupCode , String savedGroup , String userAccessSites , Integer[] skuArray , ProductLinesData[] productLineIdArray , ProductLinesData[] allProductLines , Integer includeOrders , Integer includeReturns , Date expirationDate , String includeDaysCount , Integer perCarMinMaxValue , String salesHistoryValue , String displayQty , String sessionType , String includeHubSpokeOrderOption , String includeHubSpokeReturnOption , Boolean applicationPartsOnly ) throws Exception { return createNewSession ( user , perCarOrder , includeFleets , includeSystems , includeSpecials , includeObsoletes , includeOverstocks , includeSupersede , includeKeylinesOrders , includeKeylinesReturns , includeLessThanStdPkg ,includeWeekSupplyOption,weekSupplyOptionName, useInvestmentCollections , provideStoreDepth , includeSoldNotStocked , includeNewNumbers , orderAllSkus , returnAllSKUs , dollarLimit , comment , siteGroupId , "Custom Group" , sitesArray , productGroupsArray , groupCodeArray , fleetGroupsIds , customProductGroup , customGroupCodeProductGroupObj , isGroupCode , savedGroup , userAccessSites , skuArray , productLineIdArray , allProductLines , includeOrders , includeReturns , expirationDate , includeDaysCount , perCarMinMaxValue , salesHistoryValue , displayQty , sessionType , includeHubSpokeOrderOption , includeHubSpokeReturnOption , null , applicationPartsOnly ); } private BigDecimal createNewSession ( User user , Boolean perCarOrder , Boolean includeFleets , Boolean includeSystems , Boolean includeSpecials , Boolean includeObsoletes , Boolean includeOverstocks , Boolean includeSupersede , Boolean includeKeylinesOrders , Boolean includeKeylinesReturns , Boolean includeLessThanStdPkg ,Double includeWeekSupplyOption,String weekSupplyOptionName, Boolean useInvestmentCollections , Boolean provideStoreDepth , Boolean includeSoldNotStocked , Boolean includeNewNumbers , Boolean orderAllSkus , Boolean returnAllSKUs , Integer dollarLimit , String comment , Integer siteGroupId , String siteGroupName , Integer[] sitesArray , ProductGroupOrderReturn[] productGroupsArray , ProductGroupOrderReturn[] groupCodeArray , Integer[] fleetGroupsIds , CustomProductGroup customProductGroup , CustomGroupCodeProductGroup customGroupCodeProductGroupObj , Boolean isGroupCode , String savedGroup , String userAccessSites , Integer[] skuArray , ProductLinesData[] productLineIdArray , ProductLinesData[] allProductLines , Integer includeOrders , Integer includeReturns , Date expirationDate , String includeDaysCount , Integer perCarMinMaxValue , String salesHistoryValue , String displayQty , String sessionType , String includeHubSpokeOrderOption , String includeHubSpokeReturnOption , Long createdFromSessionId , Boolean applicationPartsOnly ) throws Exception { boolean shouldBeDeferred = false; if ( user.getAccessItemIds ( ) != null && user.getAccessItemIds ( ).contains ( AccessItem.DELAY_SESSION_CREATION.getNumVal ( ) ) ) { if ( sitesArray != null ) { shouldBeDeferred = sitesArray.length > deferSiteLimit; } else if ( siteGroupId != null ) { shouldBeDeferred = siteGroupDAO.getSiteCount ( siteGroupId , user , userAccessSites , sessionType ) > deferSiteLimit; } } boolean deferCreation = shouldBeDeferred; long start = System.currentTimeMillis ( ); NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate ( jdbcTemplate ); int perCarMinMaxCode = 0; if ( ( "MM" ).equals ( sessionType ) && perCarMinMaxValue == 1 ) { Integer val = jdbcTemplate.queryForObject ( "select SETTING_ID from MIN_MAX_SESSION_SETTING where SETTING_CODE = 'MINGTPERCAR' AND SETTING_TYPE = 'MIN' " , Integer.class ); perCarMinMaxCode = val != null ? val : 0; } if ( isGroupCode == null ) isGroupCode = false; Boolean finalIsGroupCode = isGroupCode; String salesSettingCode = ( "MM" ).equals ( sessionType ) ? jdbcTemplate.queryForObject ( "select SETTING_CODE from MIN_MAX_SESSION_SETTING where SETTING_TYPE = 'Sales' and SETTING_ID = " + salesHistoryValue , String.class ) : null; KeyHolder keyHolder = new GeneratedKeyHolder ( ); int finalPerCarMinMaxCode = perCarMinMaxCode; Boolean finalIsGroupCode1 = isGroupCode; try { LOGGER.info ( "Insert sql parameters are printed" + " deferCreation " + deferCreation + " user " + user + " comment " + comment + " savedGroup " + savedGroup + " includeOverstocks " + includeOverstocks + " includeSpecials " + includeSpecials + " includeObsoletes " + includeObsoletes + " includeOrders " + includeOrders + " includeReturns " + includeReturns + " includeLessThanStdPkg " + includeLessThanStdPkg + "sessionType" + sessionType + " expirationDate " + expirationDate + " includeFleets " + includeFleets + " includeSystems " + includeSystems + " useInvestmentCollections " + useInvestmentCollections + " perCarOrder " + perCarOrder + " provideStoreDepth " + provideStoreDepth + " createdFromSessionId " + createdFromSessionId + "includeSoldNotStocked " + includeSoldNotStocked + " includeNewNumbers " + includeNewNumbers + " orderAllSkus " + orderAllSkus + " dollarLimit " + dollarLimit + " includeDaysCount " + includeDaysCount + " salesSettingCode " + salesSettingCode + " includeHubSpokeOrderOption " + includeHubSpokeOrderOption + " includeHubSpokeReturnOption " + includeHubSpokeReturnOption + " applicationPartsOnly " + applicationPartsOnly + " displayQty " + displayQty + " finalIsGroupCode " + finalIsGroupCode + " salesSettingCode " + salesSettingCode + " includeSupersede " + includeSupersede + " finalIsGroupCode1 " + finalIsGroupCode1 + " returnAllSKUs " + returnAllSKUs + "includeWeekSupplyOption" +includeWeekSupplyOption); int row = jdbcTemplate.update ( connection -> { PreparedStatement ps = connection.prepareStatement ( " INSERT INTO PULSE_SESSION " + "(STATUS_ID, CREATED_DATE, CREATED_USER_ID, SESSION_COMMENT, LAST_MODIFIED_DATE, SESSION_DESC, " + "INCLUDE_OVERSTOCK, INCLUDE_SPECIALS, INCLUDE_OBSOLETES, INCLUDE_ORDERS, INCLUDE_RETURNS, INCLUDE_LT_STD_PKG, SESSION_TYPE_CODE, " + "CREATED_ROLE_ID, EXPIRATION_DATE, INCLUDE_FLEETS, INCLUDE_SYSTEMS, USE_INV_COLL, PER_CAR_ORDER, PROVIDE_STORE_DEPTH, " + "CREATED_FROM_SESSION_ID, INCLUDE_SALES_DEMAND, INCLUDE_NEW_NUMBERS, INCLUDE_ORDER_ALL,DOLLAR_LIMIT,NEW_NUMBER_DAYS,MIN_MAX_SALES_SETTING,INCLUDE_HS_ORDER,INCLUDE_HS_RETURN, APPLICATION_PARTS_ONLY, EXCLUDE_DISPLAY_ITEM,INCLUDE_SELL_THRU,IS_GROUP_CODE,INCLUDE_RETURN_ALL,NSD_WEEK_SUPPLY_OPTION) " + "VALUES (?, sysdate, ?, ?, sysdate, ?, " + "?, ?, ?, ?, ?, ?, ?, ?, least(?, sysdate+30), ?,?, ?, ?, ?, ?, ?, ?, ?,?, ?,?,?,?,?,?,?,?,?,?)" , new String[]{"SESSION_ID"} ); ps.setInt ( 1 , deferCreation ? 14 : 2 ); ps.setInt ( 2 , user.getUserId ( ) ); ps.setString ( 3 , comment ); ps.setString ( 4 , savedGroup ); ps.setBoolean ( 5 , includeOverstocks ); ps.setBoolean ( 6 , includeSpecials ); ps.setBoolean ( 7 , includeObsoletes ); ps.setInt ( 8 , includeOrders ); ps.setInt ( 9 , includeReturns ); ps.setBoolean ( 10 , includeLessThanStdPkg ); ps.setString ( 11 , sessionType ); ps.setInt ( 12 , user.getRoleId ( ) ); ps.setTimestamp ( 13 , new Timestamp ( expirationDate.getTime ( ) ) ); ps.setBoolean ( 14 , includeFleets ); ps.setBoolean ( 15 , includeSystems ); ps.setBoolean ( 16 , useInvestmentCollections ); ps.setBoolean ( 17 , perCarOrder ); ps.setBoolean ( 18 , provideStoreDepth ); if ( createdFromSessionId == null ) { ps.setNull ( 19 , Types.NULL ); } else { ps.setLong ( 19 , createdFromSessionId ); } ps.setBoolean ( 20 , includeSoldNotStocked ); ps.setBoolean ( 21 , includeNewNumbers ); ps.setBoolean ( 22 , orderAllSkus ); ps.setObject ( 23 , dollarLimit ); ps.setString ( 24 , includeDaysCount ); //ps.setInt(25, finalPerCarMinMaxCode); ps.setString ( 25 , salesSettingCode ); ps.setString ( 26 , includeHubSpokeOrderOption ); ps.setString ( 27 , includeHubSpokeReturnOption ); ps.setBoolean ( 28 , applicationPartsOnly ); ps.setString ( 29 , displayQty ); ps.setBoolean ( 30 , finalIsGroupCode ); ps.setString ( 25 , salesSettingCode ); ps.setString ( 26 , includeHubSpokeOrderOption ); ps.setString ( 27 , includeHubSpokeReturnOption ); ps.setBoolean ( 28 , applicationPartsOnly ); ps.setString ( 29 , displayQty ); // ps.setBoolean ( 30,includeSupersede ); if ( includeSupersede == null ) { ps.setNull ( 30 , Types.NULL ); } else { ps.setBoolean ( 30 , includeSupersede ); } ps.setBoolean ( 31 , finalIsGroupCode1 ); ps.setBoolean ( 32 , returnAllSKUs ); ps.setString(33,weekSupplyOptionName); return ps; } , keyHolder ); LOGGER.info ( "Create new session| the row is created " + row ); if ( row > 0 ) { final BigDecimal sessionId = (BigDecimal) keyHolder.getKey ( ); LOGGER.info ( "Create new session|keyholder for sessionId " + sessionId + "keyHolder " + keyHolder ); try { if ( sitesArray != null && sitesArray.length > 0 ) { MapSqlParameterSource sessionSitesParameters = new MapSqlParameterSource ( ); sessionSitesParameters.addValue ( "sessionId" , sessionId ); sessionSitesParameters.addValue ( "list" , new HashSet<> ( Arrays.asList ( sitesArray ) ) ); sessionSitesParameters.addValue ( "siteGroupName" , siteGroupName ); namedParameterJdbcTemplate .update ( "INSERT INTO SESSION_SITE (SESSION_ID, SITE_GROUP_NAME, SITE_ID) SELECT :sessionId, :siteGroupName, SITE_ID FROM SITE " + "WHERE SITE_ID IN (:list) and ACTIVE_FLAG = 'Y' " , sessionSitesParameters ); } if ( siteGroupId != null ) { MapSqlParameterSource sessionSiteGroupsParameters = new MapSqlParameterSource ( ); sessionSiteGroupsParameters.addValue ( "sessionId" , sessionId ); sessionSiteGroupsParameters.addValue ( "siteGroupId" , siteGroupId ); String jasStoreOnly = ""; if ( ( "MM" ).equals ( sessionType ) || ( "minMax" ).equals ( sessionType ) ) jasStoreOnly = " AND S.JAS_STORE = 'Y' "; if ( user.getRoleId ( ) == ROLE.ADMIN.getNumVal ( ) || user.getRoleId ( ) == ROLE.XO.getNumVal ( ) ) { namedParameterJdbcTemplate.update ( "MERGE INTO SESSION_SITE SS " + " USING (SELECT DISTINCT :sessionId as session_id, SGM.SITE_ID, sg.SITE_GROUP_NAME FROM SITE_GROUP_MAPPING SGM, SITE S, SITE_GROUP sg " + " WHERE sg.SITE_GROUP_ID = SGM.SITE_GROUP_ID and SGM.SITE_ID = S.SITE_ID AND sg.SITE_GROUP_ID = :siteGroupId AND S.ACTIVE_FLAG = 'Y' " + jasStoreOnly + ") B " + " ON (SS.SESSION_ID = B.SESSION_ID AND SS.SITE_ID = B.SITE_ID) " + " WHEN NOT MATCHED THEN INSERT (SS.SESSION_ID, SS.SITE_ID, SS.SITE_GROUP_NAME) VALUES (B.SESSION_ID, B.SITE_ID, B.SITE_GROUP_NAME) " , sessionSiteGroupsParameters ); } else { String sql = "MERGE INTO SESSION_SITE SS " + " USING (SELECT DISTINCT :sessionId as session_id, sgm.SITE_ID, sg.SITE_GROUP_NAME FROM SITE_GROUP_MAPPING SGM, SITE S, SITE_GROUP sg " + " WHERE sg.SITE_GROUP_ID = SGM.SITE_GROUP_ID and sgm.site_id = s.site_id " + jasStoreOnly; if ( userAccessSites.length ( ) > 0 ) { sql += "and (sgm.SITE_ID,0) in (" + userAccessSites + ") "; } else { sql += "and (sgm.SITE_ID,0) in ((-999,0)) "; } sql += "and sg.SITE_GROUP_ID = :siteGroupId and s.active_flag = 'Y') B " + " ON (SS.SESSION_ID = B.SESSION_ID AND SS.SITE_ID = B.SITE_ID) " + " WHEN NOT MATCHED THEN INSERT (SS.SESSION_ID, SS.SITE_ID, SS.SITE_GROUP_NAME) VALUES (B.SESSION_ID, B.SITE_ID, B.SITE_GROUP_NAME) "; sessionSiteGroupsParameters.addValue ( "userId" , user.getUserId ( ) ); namedParameterJdbcTemplate.update ( sql , sessionSiteGroupsParameters ); } } if ( includeKeylinesOrders || includeKeylinesReturns && ! ( "MM" ).equals ( sessionType ) ) { jdbcTemplate.update ( "insert into session_hierarchy " + " (select distinct session_id, hierarchy_id, ?, ?, null from session_site ss, keyline k, keyline_hierarchy kh " + " where ss.session_id = ? and ss.site_id = k.SITE_ID and kh.KEYLINE_ID = k.KEYLINE_ID)" , includeKeylinesReturns , includeKeylinesOrders , sessionId ); jdbcTemplate.update ( "INSERT INTO SESSION_HIERARCHY_DETAIL (SESSION_ID, HIERARCHY_ID, " + " INCLUDE_RETURN, INCLUDE_ORDER, PRODUCT_GROUP_NAME, PRODUCT_GROUP_ID) " + " (select session_id, hierarchy_id, include_return, include_order, 'Key Lines', ? " + " from SESSION_HIERARCHY where session_id = ?) " , PRODUCT_GROUP.KEY_LINE.getNumVal ( ) , sessionId ); } if ( ( "SC" ).equals ( sessionType ) ) { List siteIds = null; if ( sitesArray != null ) { siteIds = Arrays.stream ( sitesArray ).collect ( Collectors.toList ( ) ); } else { siteIds = jdbcTemplate.query ( "SELECT SITE_ID FROM SITE_GROUP_MAPPING WHERE SITE_GROUP_ID = ?" , new IntegerRowMapper ( ) , siteGroupId ); } String flagList = "displayQty,firstStockDate,keepLock,lastSaleDate,lostSaleQty,minMaxQty,salesQty,salesOccurrences"; String[] flagsToClear = flagList.split ( "," ); adminService.clearFlags ( user.getUserId ( ) , siteIds , Arrays.asList ( flagsToClear ) ); } if ( customProductGroup != null && ! ( "MM" ).equals ( sessionType ) ) { String[] hierarchyIds = customProductGroup.getHierarchyIds ( ); if ( hierarchyIds != null ) { StringBuilder list = new StringBuilder ( ); List hierarchyList = new ArrayList<> ( ); for (String j : hierarchyIds) { list.append ( "(1," ).append ( j.contains ( "s_" ) ? j.substring ( "s_".length ( ) ) : j ).append ( ")," ); if ( j.contains ( "s_" ) ) { hierarchyList.add ( new ProductGroupHierarchy ( Integer.valueOf ( j.substring ( "s_".length ( ) ) ) , true ) ); } else { hierarchyList.add ( new ProductGroupHierarchy ( Integer.valueOf ( j ) , false ) ); } } list.append ( "(1,-1)" ); jdbcTemplate.update ( "MERGE INTO SESSION_HIERARCHY SH " + " USING (SELECT ? as session_id, HIERARCHY_ID, ? AS INCLUDE_RETURN, ? AS INCLUDE_ORDER " + " FROM PRODUCT_HIERARCHY WHERE (1,HIERARCHY_ID) IN (" + list.toString ( ) + ")) B " + " ON (SH.SESSION_ID = B.SESSION_ID AND SH.HIERARCHY_ID = B.HIERARCHY_ID) " + " WHEN NOT MATCHED THEN INSERT (SH.SESSION_ID, SH.HIERARCHY_ID, SH.INCLUDE_RETURN, SH.INCLUDE_ORDER) " + " VALUES (B.SESSION_ID, B.HIERARCHY_ID, B.INCLUDE_RETURN, B.INCLUDE_ORDER) " + " WHEN MATCHED THEN UPDATE SET " + " SH.INCLUDE_RETURN = GREATEST(B.INCLUDE_RETURN,SH.INCLUDE_RETURN) , " + " SH.INCLUDE_ORDER = GREATEST(B.INCLUDE_ORDER,SH.INCLUDE_ORDER) " , ps -> { ps.setBigDecimal ( 1 , sessionId ); ps.setBoolean ( 2 , customProductGroup.getReturns ( ) ); ps.setBoolean ( 3 , customProductGroup.getOrders ( ) ); } ); // inserting details into session_hierarchy_details to // show it in session view // page jdbcTemplate.batchUpdate ( "INSERT INTO SESSION_HIERARCHY_DETAIL (SESSION_ID, HIERARCHY_ID, " + "INCLUDE_RETURN, INCLUDE_ORDER, PRODUCT_GROUP_NAME, PRODUCT_GROUP_ID, IS_SELECTED) VALUES " + "(?,?,?,?, 'Custom Group', (select PRODUCT_GROUP_ID from PRODUCT_GROUP where lower(GROUP_NAME) = 'custom group'), ?)" , new BatchPreparedStatementSetter ( ) { @Override public void setValues ( PreparedStatement ps , int i ) throws SQLException { ProductGroupHierarchy h = hierarchyList.get ( i ); ps.setBigDecimal ( 1 , sessionId ); ps.setInt ( 2 , h.getHierarchyId ( ) ); ps.setBoolean ( 3 , customProductGroup.getReturns ( ) ); ps.setBoolean ( 4 , customProductGroup.getOrders ( ) ); ps.setBoolean ( 5 , h.getIsSelected ( ) ); } @Override public int getBatchSize ( ) { return hierarchyIds.length; } } ); } } if ( productGroupsArray != null && productGroupsArray.length > 0 && ! ( "MM" ).equals ( sessionType ) ) { jdbcTemplate.batchUpdate ( "MERGE INTO SESSION_HIERARCHY SH " + " USING (SELECT ? as SESSION_ID, HIERARCHY_ID, " + " ? AS INCLUDE_RETURN, ? AS INCLUDE_ORDER " + " FROM PRODUCT_GROUP_MAPPING PGM " + " WHERE PGM.PRODUCT_GROUP_ID = ?) B " + " ON (SH.SESSION_ID = B.SESSION_ID AND SH.HIERARCHY_ID = B.HIERARCHY_ID) " + " WHEN NOT MATCHED THEN INSERT (SH.SESSION_ID, SH.HIERARCHY_ID, SH.INCLUDE_RETURN, SH.INCLUDE_ORDER) " + " VALUES (B.SESSION_ID, B.HIERARCHY_ID, B.INCLUDE_RETURN, B.INCLUDE_ORDER)" + " WHEN MATCHED THEN UPDATE SET " + " SH.INCLUDE_RETURN = GREATEST(B.INCLUDE_RETURN,SH.INCLUDE_RETURN) , " + " SH.INCLUDE_ORDER = GREATEST(B.INCLUDE_ORDER,SH.INCLUDE_ORDER) " , new BatchPreparedStatementSetter ( ) { @Override public void setValues ( PreparedStatement ps , int i ) throws SQLException { ProductGroupOrderReturn pgor = productGroupsArray[i]; ps.setBigDecimal ( 1 , sessionId ); ps.setBoolean ( 2 , pgor.getReturns ( ) ); ps.setBoolean ( 3 , pgor.getOrders ( ) ); ps.setInt ( 4 , pgor.getProductGroupId ( ) ); } @Override public int getBatchSize ( ) { return productGroupsArray.length; } } ); // inserting details into session_hierarchy_details to show // it in session view // page for (ProductGroupOrderReturn pgor : productGroupsArray) { jdbcTemplate.update ( "INSERT INTO SESSION_HIERARCHY_DETAIL (SESSION_ID, HIERARCHY_ID, INCLUDE_RETURN, " + " INCLUDE_ORDER, PRODUCT_GROUP_NAME, PRODUCT_GROUP_ID) " + " (SELECT ?, HIERARCHY_ID, ?, ?, GROUP_NAME, PGM.PRODUCT_GROUP_ID FROM " + " PRODUCT_GROUP_MAPPING PGM, PRODUCT_GROUP PG " + " WHERE PG.PRODUCT_GROUP_ID = PGM.PRODUCT_GROUP_ID AND PG.PRODUCT_GROUP_ID = ?)" , sessionId , pgor.getReturns ( ) , pgor.getOrders ( ) , pgor.getProductGroupId ( ) ); } } if ( customGroupCodeProductGroupObj != null && ! ( "MM" ).equals ( sessionType ) && isGroupCode ) { List newListOfGroupCodes = customGroupCodeProductGroupObj.getGroupCodes ( ); jdbcTemplate.update ( "INSERT INTO SESSION_GROUP_CODE_DETAIL (SESSION_ID,INCLUDE_RETURN,INCLUDE_ORDER,PRODUCT_GROUP_NAME,PRODUCT_GROUP_ID)" + " VALUES (" + sessionId + "," + includeReturns + "," + includeOrders + ",'CustomGroup Codes',-1)" ); jdbcTemplate.batchUpdate ( "MERGE INTO SESSION_GROUP_CODE SGC USING " + "(SELECT ? AS SESSION_ID, ? AS GROUP_CODE ,? AS FIELD_ABBR FROM DUAL) A " + "ON (SGC.SESSION_ID=A.SESSION_ID AND SGC.FIELD_ABBR=A.FIELD_ABBR AND SGC.GROUP_CODE=A.GROUP_CODE) " + "WHEN NOT MATCHED THEN INSERT (SESSION_ID,FIELD_ABBR,GROUP_CODE) VALUES (A.SESSION_ID,A.FIELD_ABBR,A.GROUP_CODE) " , new BatchPreparedStatementSetter ( ) { @Override public void setValues ( PreparedStatement ps , int i ) throws SQLException { GroupCodeProducts gp = newListOfGroupCodes.get ( i ); ps.setBigDecimal ( 1 , sessionId ); ps.setInt ( 2 , gp.getGroup_code ( ) ); ps.setString ( 3 , gp.getField_abbr ( ) ); } @Override public int getBatchSize ( ) { return newListOfGroupCodes.size ( ); } } ); jdbcTemplate.update ( "MERGE INTO SESSION_PRODUCT SP USING (" + " SELECT DISTINCT SPR.PRODUCT_iD AS PRODUCT_ID,? AS SESSION_ID FROM SITE_PRODUCT SPR " + " INNER JOIN PRODUCT P ON (P.PRODUCT_ID=SPR.PRODUCT_ID )" + " INNER JOIN SESSION_GROUP_CODE SGC ON (SGC.SESSION_ID=? AND SGC.FIELD_ABBR=P.FIELD_ABBR AND SGC.GROUP_CODE=P.GROUP_CODE)" + " WHERE SPR.SITE_ID IN (SELECT SITE_ID FROM SESSION_SITE SS WHERE SS.SESSION_ID= ?))B " + " ON (SP.SESSION_ID=B.SESSION_ID AND SP.PRODUCT_ID=B.PRODUCT_ID)" + " WHEN NOT MATCHED THEN INSERT (SESSION_ID,PRODUCT_ID) VALUES (B.SESSION_ID,B.PRODUCT_ID)" , sessionId , sessionId , sessionId ); // jdbcTemplate.batchUpdate ( "MERGE INTO SESSION_PRODUCT SP USING " + // "(SELECT spr.PRODUCT_ID as PRODUCT_ID,? AS SESSION_ID FROM SITE_PRODUCT spr " + // " Inner join PRODUCT p on (p.product_id=spr.product_id and P.GROUP_CODE=? AND P.FIELD_ABBR=?)" + // " WHERE spr.site_id in (select site_id from session_site ss where ss.session_id =?)) B " + // " ON (SP.SESSION_ID=B.SESSION_ID AND SP.PRODUCT_ID=B.PRODUCT_ID) " + // " WHEN NOT MATCHED THEN INSERT (SESSION_ID,PRODUCT_ID) VALUES (B.SESSION_ID,B.PRODUCT_ID) " , // new BatchPreparedStatementSetter ( ) { // @Override // public void setValues ( PreparedStatement ps , int i ) throws SQLException { // GroupCodeProducts gp = newListOfGroupCodes.get ( i ); // ps.setBigDecimal ( 1 , sessionId ); // ps.setInt ( 2 , gp.getGroup_code ( ) ); // ps.setString ( 3 , gp.getField_abbr ( ) ); // ps.setBigDecimal ( 4 , sessionId ); // } // // @Override // public int getBatchSize ( ) { // return newListOfGroupCodes.size ( ); // } // } ); } if ( groupCodeArray != null && groupCodeArray.length > 0 && ! ( "MM" ).equals ( sessionType ) && isGroupCode ) { start = System.currentTimeMillis ( ); jdbcTemplate.batchUpdate ( "\n" + "merge into SESSION_PRODUCT sp using(\n" + " select distinct (spr.product_id) as PRODUCT_ID,? as sessionid from PRODUCT p\n" + " inner join site_product spr on (spr.product_id=p.product_id and spr.site_id in (select site_id from session_site" + " where session_id = ?))" + " inner join GROUPCode_group_mapping ggm on\n" + " (p.GROUP_CODE=ggm.group_code and p.FIELD_ABBR=ggm.field_abbr and ggm.groupcode_group_id=?)) b \n" + " on (sp.PRODUCT_ID=b.PRODUCT_ID and sp.SESSION_ID=b.sessionid)\n" + "when not matched then insert (session_id, product_id) VALUES ( b.sessionid,b.PRODUCT_ID )" , new BatchPreparedStatementSetter ( ) { @Override public void setValues ( PreparedStatement ps , int i ) throws SQLException { ProductGroupOrderReturn pgor = groupCodeArray[i]; ps.setBigDecimal ( 1 , sessionId ); ps.setBigDecimal ( 2 , sessionId ); ps.setInt ( 3 , pgor.getProductGroupId ( ) ); } @Override public int getBatchSize ( ) { return groupCodeArray.length; } } ); jdbcTemplate.batchUpdate ( "INSERT INTO SESSION_GROUP_CODE_DETAIL (SESSION_ID,INCLUDE_RETURN,INCLUDE_ORDER,PRODUCT_GROUP_NAME,PRODUCT_GROUP_ID)" + "VALUES (?," + includeReturns + "," + includeOrders + ",?,?)" , new BatchPreparedStatementSetter ( ) { @Override public void setValues ( PreparedStatement ps , int i ) throws SQLException { ProductGroupOrderReturn pgor = groupCodeArray[i]; ps.setBigDecimal ( 1 , sessionId ); ps.setString ( 2 , pgor.getProductGroupName ( ) ); ps.setInt ( 3 , pgor.getProductGroupId ( ) ); } @Override public int getBatchSize ( ) { return groupCodeArray.length; } } ); LOGGER.info ( "Create new session|Insert Group Codes for sessionId " + sessionId + ": " + ( System.currentTimeMillis ( ) - start ) ); } if ( fleetGroupsIds != null && fleetGroupsIds.length > 0 && ! ( "MM" ).equals ( sessionType ) ) { jdbcTemplate.batchUpdate ( "INSERT INTO SESSION_FLEET (SESSION_ID, FLEET_ID) " + "VALUES (?,?)" , new BatchPreparedStatementSetter ( ) { @Override public void setValues ( PreparedStatement ps , int i ) throws SQLException { ps.setBigDecimal ( 1 , sessionId ); ps.setInt ( 2 , fleetGroupsIds[i] ); } @Override public int getBatchSize ( ) { return fleetGroupsIds.length; } } ); } LOGGER.info ( "Create new session| Overture duration for sessionId " + sessionId + ": " + ( System.currentTimeMillis ( ) - start ) ); final int DB_THREADS = 8; ExecutorService threadPool = Executors.newFixedThreadPool ( DB_THREADS ); CompletionService pool = new ExecutorCompletionService<> ( threadPool ); List siteList = jdbcTemplate.query ( "SELECT SITE_ID FROM session_SITE WHERE session_id = ?" , new IntegerRowMapper ( ) , sessionId ); if ( skuArray != null ) { start = System.currentTimeMillis ( ); jdbcTemplate.batchUpdate ( "insert into SESSION_PRODUCT(session_id, product_id) values(?,?)" , new BatchPreparedStatementSetter ( ) { @Override public void setValues ( PreparedStatement ps , int i ) throws SQLException { int productId = skuArray[i]; ps.setBigDecimal ( 1 , sessionId ); ps.setInt ( 2 , productId ); } @Override public int getBatchSize ( ) { return skuArray.length; } } ); LOGGER.info ( "Create new session|Insert custom skus for sessionId " + sessionId + ": " + ( System.currentTimeMillis ( ) - start ) ); } if ( allProductLines != null ) { for (Integer siteId : siteList) { start = System.currentTimeMillis ( ); jdbcTemplate.batchUpdate ( "merge into MIN_MAX_SITE_PREFERENCES d " + " using (select ? site_id,? product_line_id from dual) s " + " ON (d.site_id = s.site_id AND d.PRODUCT_LINE_ID = s.product_line_id)" + " when NOT MATCHED then insert (d.SITE_ID, d.PRODUCT_LINE_ID, d.PER_CAR, d.LEAD_TIME, d.ORDER_CYCLE,d.IS_SELECTED, d.USER_CREATED, d.USER_CREATED_DATE)" + " values(s.site_id, s.product_line_id,DECODE(?,'-1',1,?) ,DECODE(?,'-1',7,?) , DECODE(?,'-1',14,?), DECODE(?,'-1',1,?), ?, SYSDATE) " + " when MATCHED then " + "update SET d.PER_CAR=DECODE(?,'-1',d.PER_CAR,?), d.LEAD_TIME=DECODE(?,'-1',d.LEAD_TIME,?), d.ORDER_CYCLE=DECODE(?,'-1',d.ORDER_CYCLE,?),d.IS_SELECTED=DECODE(?,'-1',d.IS_SELECTED,?),d.USER_UPDATED=?, d.USER_UPDATED_DATE=SYSDATE " , new BatchPreparedStatementSetter ( ) { @Override public void setValues ( PreparedStatement ps , int i ) throws SQLException { ProductLinesData obj = allProductLines[i]; ps.setInt ( 1 , siteId ); ps.setInt ( 2 , obj.getId ( ) ); ps.setInt ( 3 , obj.isPerCar ( ) ); ps.setInt ( 4 , obj.isPerCar ( ) ); ps.setInt ( 5 , obj.getLeadTime ( ) ); ps.setInt ( 6 , obj.getLeadTime ( ) ); ps.setInt ( 7 , obj.getOrderCycle ( ) ); ps.setInt ( 8 , obj.getOrderCycle ( ) ); ps.setInt ( 9 , obj.getIsSelected ( ) ); ps.setInt ( 10 , obj.getIsSelected ( ) ); ps.setInt ( 11 , user.getUserId ( ) ); ps.setInt ( 12 , obj.isPerCar ( ) ); ps.setInt ( 13 , obj.isPerCar ( ) ); ps.setInt ( 14 , obj.getLeadTime ( ) ); ps.setInt ( 15 , obj.getLeadTime ( ) ); ps.setInt ( 16 , obj.getOrderCycle ( ) ); ps.setInt ( 17 , obj.getOrderCycle ( ) ); ps.setInt ( 18 , obj.getIsSelected ( ) ); ps.setInt ( 19 , obj.getIsSelected ( ) ); ps.setInt ( 20 , user.getUserId ( ) ); } @Override public int getBatchSize ( ) { return allProductLines.length; } } ); LOGGER.info ( "Create new session|Insert into min_max_site_preferences : " + ( System.currentTimeMillis ( ) - start ) ); } } if ( productLineIdArray != null ) { start = System.currentTimeMillis ( ); jdbcTemplate.batchUpdate ( "insert into min_max_session_prod_line(session_id, product_line_id, safety_stock, per_car,lead_time,order_cycle) values(?,?,?,DECODE(?,'-1',null,?),DECODE(?,'-1',null,?),DECODE(?,'-1',null,?))" , new BatchPreparedStatementSetter ( ) { @Override public void setValues ( PreparedStatement ps , int i ) throws SQLException { ProductLinesData obj = productLineIdArray[i]; ps.setBigDecimal ( 1 , sessionId ); ps.setInt ( 2 , obj.getId ( ) ); ps.setInt ( 3 , obj.getOrderCycle ( ) + obj.getLeadTime ( ) ); ps.setInt ( 4 , obj.isPerCar ( ) ); ps.setInt ( 5 , obj.isPerCar ( ) ); ps.setInt ( 6 , obj.getLeadTime ( ) ); ps.setInt ( 7 , obj.getLeadTime ( ) ); ps.setInt ( 8 , obj.getOrderCycle ( ) ); ps.setInt ( 9 , obj.getOrderCycle ( ) ); } @Override public int getBatchSize ( ) { return productLineIdArray.length; } } ); LOGGER.info ( "Create new session|Insert product lines for sessionId " + sessionId + ": " + ( System.currentTimeMillis ( ) - start ) ); } if ( deferCreation ) { LOGGER.debug ( "deferCreation = {}" , deferCreation ); return sessionId; } for (Integer i : siteList) { Callable worker; if ( ( "PS" ).equals ( sessionType ) || isGroupCode ) { worker = new CreateNewSkusSessionCallable ( i , sessionId.intValue ( ) , skuArray , includeOrders , includeReturns , includeLessThanStdPkg , perCarOrder , provideStoreDepth , orderAllSkus , returnAllSKUs , jdbcTemplate ); } else if ( ( "IS" ).equals ( sessionType ) ) { worker = new CreateNewInventorySeedingCallable ( i , sessionId.intValue ( ) , skuArray , includeOrders , includeReturns , jdbcTemplate ); } else if ( ( "AC" ).equals ( sessionType ) ) { worker = new CreateNewACOSessionCallable ( sessionId.intValue ( ) , i , jdbcTemplate ); } else if ( ( "DL" ).equals ( sessionType ) ) { worker = new CreateNewDollarLimitSessionCallable ( sessionId.intValue ( ) , i , dollarLimit , perCarOrder , provideStoreDepth , applicationPartsOnly ,includeWeekSupplyOption,jdbcTemplate ); } else if ( ( "MM" ).equals ( sessionType ) ) { worker = new CreateNewMinMaxSessionCallable ( sessionId.intValue ( ) , i , displayQty , productLineIdArray , jdbcTemplate ); } else if ( ( "SC" ).equals ( sessionType ) ) { worker = new CreateNewStoreClosingSessionCallable ( sessionId.intValue ( ) , i , jdbcTemplate ); } else { worker = new CreateNewSessionCallable ( sessionId.intValue ( ) , i , includeSpecials , includeObsoletes , includeOverstocks , includeSupersede , includeLessThanStdPkg ,includeWeekSupplyOption, useInvestmentCollections , includeFleets , includeSystems , perCarOrder , provideStoreDepth , includeSoldNotStocked , includeNewNumbers , jdbcTemplate ); } LOGGER.info ( "Create new session| Submitting worker for sessionId " + sessionId + " and siteId " + i ); pool.submit ( worker ); } for (int i = 0; i < siteList.size ( ); i++) { Future future = pool.take ( ); Exception result = future.get ( ); LOGGER.info ( "Create new session| Got result back for sessionId " + sessionId + ". Thread " + i ); if ( result != null ) { List remaining = threadPool.shutdownNow ( ); LOGGER.info ( "Create new session| Shutting down pool. Remaining: " + remaining.size ( ) ); threadPool.awaitTermination ( 60 , TimeUnit.SECONDS ); LOGGER.debug ( "Blocks until all tasks have completed execution after a shutdown request, " + "or the timeout occurs, or the current thread is interrupted, whichever happens first" ); throw result; } } if ( ( "SS" ).equals ( sessionType ) ) { LOGGER.info ( "Call CROSS_POPULATE_SNS_PARTS| Execute CROSS_POPULATE_SNS_PARTS " + sessionId ); try { long startProc = System.currentTimeMillis ( ); jdbcTemplate.update ( "call CROSS_POPULATE_SNS_PARTS(?)" , sessionId ); LOGGER.info ( "Call CROSS_POPULATE_SNS_PARTS| Execute CROSS_POPULATE_SNS_PARTS duration for sessionId " + sessionId + " " + ( System.currentTimeMillis ( ) - startProc ) ); // call is made to the procedure to recalculate the totals. for (Integer i : siteList) { reCalculateSessionTotals ( sessionId.intValue ( ) , i ); } } catch ( Exception e ) { LOGGER.error ( "Call CROSS_POPULATE_SNS_PARTS| Execute CROSS_POPULATE_SNS_PARTS: " + e.getMessage ( ) ); } } //Run Blending on AC Session Creation in addition to creating the session if ( ( "AC" ).equals ( sessionType ) ) { LOGGER.info ( "Running Blending for ACO Session " + sessionId ); SqlRowSet rs = jdbcTemplate.queryForRowSet ( "select sh.HIERARCHY_ID " + "from SESSION_HIERARCHY sh, product_hierarchy ph " + "where session_id=? and sh.hierarchy_id=ph.hierarchy_id " + "and ACO_ENABLED=1" , sessionId ); while (rs.next ( )) { int hierarchyId = rs.getInt ( 1 ); SqlRowSet siteIdRS = jdbcTemplate.queryForRowSet ( "select site_id from session_site where session_id=?" , sessionId ); while (siteIdRS.next ( )) { jdbcTemplate.update ( "UPDATE PULSE_SESSION SET STATUS_ID = 8, LAST_MODIFIED_DATE = sysdate WHERE SESSION_ID = ? AND STATUS_ID = 2" , sessionId ); int siteId = siteIdRS.getInt ( 1 ); acoDAO.xferBlendingData ( sessionId.intValue ( ) , siteId , hierarchyId ); acoDAO.createAcoSiteHierarchy ( siteId , hierarchyId , user.getUserId ( ) ); } } jdbcTemplate.update ( "UPDATE PULSE_SESSION SET STATUS_ID = 2, LAST_MODIFIED_DATE = sysdate WHERE SESSION_ID = ? AND STATUS_ID = 8" , sessionId ); } jdbcTemplate.update ( "UPDATE PULSE_SESSION SET STATUS_ID = 4, LAST_MODIFIED_DATE = sysdate WHERE SESSION_ID = ? AND STATUS_ID = 2" , sessionId ); LOGGER.info ( "Updating Session | SESSION_CUBIC_MEASUREMENT for sessionId " + sessionId ); jdbcTemplate.update ( "call SESSION_CUBIC_MEASUREMENT (?)" , sessionId ); LOGGER.info ( "Updating Market Selection | Submitting marketSelectionWorker for sessionId " + sessionId ); new UpdateMarketSelectionForSessionThread ( sessionId.intValue ( ) , siteList , jdbcTemplate ).start ( ); long end = System.currentTimeMillis ( ); LOGGER.info ( "Create new session| Session creation duration for sessionId " + sessionId + ": " + ( end - start ) ); LOGGER.debug ( "createNewSession done sessionId = {}" , sessionId ); return sessionId; } catch ( Exception e ) { LOGGER.error ( "Create new session| Failed for sessionId " ); LOGGER.error ( "Error Message before rollBack()" + e.getMessage ( ) ); LOGGER.error("Parameters received before rollback -> " + "includeFleets : {}, perCarOrder : {}, includeSystems : {}, includeSpecials : {}, includeObsoletes : {}, " + "includeOverstocks : {}, includeSupersede : {}, includeSoldNotStocked : {}, includeNewNumbers : {}, orderAllSkus : {}, dollarLimit : {}, " + "keyLinesOrders : {}, keyLinesReturns : {}, includeLessThanStdPkg : {}, useInvColls : {}, addDepth : {}, " + "comment : {}, siteGroup : {}, sites : {}, productGroups : {},fleetGroups : {}, " + "customProductGroup : {}, skus : {}, productLines : {}, includeOrders : {}, includeReturns : {}, " + "expirationDate : {}, includeHubSpokeOrder : {}, includeHubSpokeReturn : {}, includeHubSpokeOrderOption : {}, includeHubSpokeReturnOption : {}, " + "includeDaysCount : {}, salesHistoryValue : {},displayQty :{} ,sessionType : {}, applicationPartsOnly : {}, " + "expirationDate : {}, isGroupCode : {},sessionId :{}", includeFleets, perCarOrder, includeSystems, includeSpecials, includeObsoletes, includeOverstocks, includeSupersede, includeSoldNotStocked, includeNewNumbers, orderAllSkus, dollarLimit, includeKeylinesOrders, includeKeylinesOrders, includeLessThanStdPkg, useInvestmentCollections, provideStoreDepth, comment, siteGroupName, sitesArray, productGroupsArray, fleetGroupsIds, customProductGroup, skuArray, productLineIdArray, includeOrders, includeReturns, expirationDate, includeHubSpokeOrderOption, includeHubSpokeReturnOption, includeDaysCount, salesHistoryValue,displayQty, sessionType, applicationPartsOnly, expirationDate,isGroupCode,(BigDecimal) keyHolder.getKey ( )); rollbackSession ( (BigDecimal) keyHolder.getKey ( ) ); LOGGER.error ( "rollbackSession() called for sessionId: {}" , (BigDecimal) keyHolder.getKey ( ) ); throw e; } } else { LOGGER.info ( "returned after insert into PULSE_SESSION tbl and sessionId" + (BigDecimal) keyHolder.getKey ( ) ); return null; } } catch ( Exception e ) { if ( keyHolder.getKey ( ) == null ) { LOGGER.error ( "Create new session| Failed for sessionId " ); LOGGER.error ( "Error Message before rollBack()" + e.getMessage ( ) ); } throw e; } } private static class CreateNewSkusSessionCallable implements Callable { private final int siteId; private final int sessionId; private final JdbcTemplate jdbcTemplate; private final Integer[] skuArray; private final int includeOrders; private final int includeReturns; private final boolean includeLessThanStdPkg; private final boolean perCarOrder; private final boolean provideStoreDepth; private final boolean orderAllSkus; private final boolean returnAllSKUs; CreateNewSkusSessionCallable ( int siteId , int sessionId , Integer[] skuArray , int includeOrders , int includeReturns , boolean includeLessThanStdPkg , boolean perCarOrder , boolean provideStoreDepth , boolean orderAllSkus , boolean returnAllSKUs ,JdbcTemplate jdbcTemplate ) { this.siteId = siteId; this.sessionId = sessionId; this.skuArray = skuArray; this.jdbcTemplate = jdbcTemplate; this.includeOrders = includeOrders; this.includeReturns = includeReturns; this.includeLessThanStdPkg = includeLessThanStdPkg; this.perCarOrder = perCarOrder; this.provideStoreDepth = provideStoreDepth; this.orderAllSkus = orderAllSkus; this.returnAllSKUs = returnAllSKUs; } public Exception call ( ) { LOGGER.info ( "Create new sku session| Executing createSession for sessionId " + sessionId + " and siteId " + siteId ); try { long startproc = System.currentTimeMillis ( ); jdbcTemplate.update ( "call CREATE_NEW_SKU_SESSION (?,?,?,?,?,?,?,?,?)" , sessionId , siteId , includeOrders , includeReturns , ( includeLessThanStdPkg ) ? "1" : "0" , ( perCarOrder ) ? "1" : "0" , ( provideStoreDepth ) ? "1" : "0" , ( orderAllSkus ) ? "1" : "0" , ( returnAllSKUs ) ? "1" : "0" ); LOGGER.info ( "Create new sku session| Execute CreateNewSkusSessionCallable duration for sessionId " + sessionId + " and siteId " + siteId + ": " + ( System.currentTimeMillis ( ) - startproc ) ); } catch ( Exception e ) { LOGGER.error ( "Create new sku session| CreateNewSkusSessionCallable: " + e ); return e; } LOGGER.debug ( "CREATE_NEW_SKU_SESSION SP parameters: {},{},{},{},{},{},{},{},{}" , sessionId , siteId , includeOrders , includeReturns , ( includeLessThanStdPkg ) ? "1" : "0" , ( perCarOrder ) ? "1" : "0" , ( provideStoreDepth ) ? "1" : "0" , ( orderAllSkus ) ? "1" : "0" , ( returnAllSKUs ) ? "1" : "0" ); return null; } } private static class CreateNewInventorySeedingCallable implements Callable { private final int siteId; private final int sessionId; private final JdbcTemplate jdbcTemplate; private final Integer[] skuArray; private final int includeOrders; private final int includeReturns; CreateNewInventorySeedingCallable ( int siteId , int sessionId , Integer[] skuArray , int includeOrders , int includeReturns , JdbcTemplate jdbcTemplate ) { this.siteId = siteId; this.sessionId = sessionId; this.skuArray = skuArray; this.jdbcTemplate = jdbcTemplate; this.includeOrders = includeOrders; this.includeReturns = includeReturns; } public Exception call ( ) { LOGGER.info ( "Create new Inventory seeding session| Executing CREATE_NEW_INV_SEEDING_SESSION for sessionId " + sessionId + " and siteId " + siteId ); try { long startproc = System.currentTimeMillis ( ); jdbcTemplate.update ( "call CREATE_NEW_INV_SEEDING_SESSION (?,?)" , sessionId , siteId ); LOGGER.info ( "Create new sku session| Execute CREATE_NEW_INV_SEEDING_SESSION duration for sessionId " + sessionId + " and siteId " + siteId + ": " + ( System.currentTimeMillis ( ) - startproc ) ); } catch ( Exception e ) { LOGGER.error ( "Create new sku session| CREATE_NEW_INV_SEEDING_SESSION: " + e ); return e; } LOGGER.debug ( "CREATE_NEW_INV_SEEDING_SESSION SP parameters: {}, {}" , sessionId , siteId ); return null; } } private static class CreateNewACOSessionCallable implements Callable { private final int sessionId; private final int siteId; private final JdbcTemplate jdbcTemplate; CreateNewACOSessionCallable ( int sessionId , int siteId , JdbcTemplate jdbcTemplate ) { this.sessionId = sessionId; this.siteId = siteId; this.jdbcTemplate = jdbcTemplate; } public Exception call ( ) { LOGGER.info ( "Create new ACO session| Executing createACOSession for sessionId " + sessionId + " and siteId " + siteId ); try { long startproc = System.currentTimeMillis ( ); jdbcTemplate.update ( "call CREATE_NEW_ACO_SESSION (?,?)" , sessionId , siteId ); LOGGER.info ( "Create new ACO session| Execute CreateNewACOSessionCallable duration for sessionId " + sessionId + " and siteId " + siteId + ": " + ( System.currentTimeMillis ( ) - startproc ) ); } catch ( Exception e ) { LOGGER.error ( "Create new ACO session| CreateNewACOSessionCallable: " + e ); return e; } LOGGER.debug ( "CREATE_NEW_ACO_SESSION SP parameters: {}, {}" , sessionId , siteId ); return null; } } private static class CreateNewDollarLimitSessionCallable implements Callable { private final int sessionId; private final int siteId; private final int dollarLimit; private final boolean perCarOrder; private final boolean provideStoreDepth; private final boolean applicationPartsOnly; private final Double includeWeekSupplyOption; private final JdbcTemplate jdbcTemplate; CreateNewDollarLimitSessionCallable ( int sessionId , int siteId , int dollarLimit , boolean perCarOrder , boolean provideStoreDepth , boolean applicationPartsOnly , Double includeWeekSupplyOption, JdbcTemplate jdbcTemplate ) { this.sessionId = sessionId; this.siteId = siteId; this.dollarLimit = dollarLimit; this.perCarOrder = perCarOrder; this.provideStoreDepth = provideStoreDepth; this.applicationPartsOnly = applicationPartsOnly; this.includeWeekSupplyOption = includeWeekSupplyOption; this.jdbcTemplate = jdbcTemplate; } public Exception call ( ) { LOGGER.info ( "Create new dollar limit session| Executing createSession for sessionId " + sessionId + " and siteId " + siteId ); try { long startproc = System.currentTimeMillis ( ); jdbcTemplate.update ( "call CREATE_NEW_DOLLAR_SESSION (?,?,?,?,?,?,?)" , sessionId , siteId , dollarLimit , ( perCarOrder ) ? "1" : "0" , ( provideStoreDepth ) ? "1" : "0" , ( applicationPartsOnly ) ? "1" : "0", includeWeekSupplyOption ); LOGGER.info ( "Create new session| Execute CreateNewSessionCallable duration for sessionId " + sessionId + " and siteId " + siteId + " and dollarLimit " + dollarLimit + ": " + ( System.currentTimeMillis ( ) - startproc ) ); } catch ( Exception e ) { LOGGER.error ( "Create new session| CreateNewSessionCallable: " + e.getMessage ( ) ); return e; } LOGGER.debug ( "CREATE_NEW_DOLLAR_SESSION SP parameters: {}, {}, {}, {}, {}, {},{}" , sessionId , siteId , dollarLimit , ( perCarOrder ) ? "1" : "0" , ( provideStoreDepth ) ? "1" : "0" , ( applicationPartsOnly ) ? "1" : "0", includeWeekSupplyOption ); return null; } } private static class CreateNewMinMaxSessionCallable implements Callable { private final int sessionId; private final int siteId; private final String displayQty; private final ProductLinesData[] productLineIdArray; private final JdbcTemplate jdbcTemplate; CreateNewMinMaxSessionCallable ( int sessionId , int siteId , String displayQty , ProductLinesData[] productLineIdArray , JdbcTemplate jdbcTemplate ) { this.sessionId = sessionId; this.siteId = siteId; this.displayQty = displayQty; this.productLineIdArray = productLineIdArray; this.jdbcTemplate = jdbcTemplate; } public Exception call ( ) { LOGGER.info ( "Create new min max session| Executing createSession for sessionId " + sessionId + " and siteId " + siteId ); try { long startproc = System.currentTimeMillis ( ); jdbcTemplate.update ( "call CREATE_NEW_MINMAX_SESSION (?,?,?)" , sessionId , siteId , displayQty ); LOGGER.info ( "Create new session| Execute CreateNewMinMaxSessionCallable duration for sessionId " + sessionId + " and siteId " + siteId + ( System.currentTimeMillis ( ) - startproc ) ); } catch ( Exception e ) { LOGGER.error ( "Create new session| CreateNewMinMaxSessionCallable: " + e ); return e; } LOGGER.debug ( "CREATE_NEW_MINMAX_SESSION SP parameters: {}, {}, {}" , sessionId , siteId , displayQty ); return null; } } private static class CreateNewSessionCallable implements Callable { private final int siteId; private final int sessionId; private final boolean includeSpecials; private final boolean includeObsoletes; private final boolean includeOverstocks; private final Boolean includeSupersede; private final boolean includeLessThanStdPkg; private final boolean includeFleets; private final boolean includeSystems; private final boolean useInvestmentCollections; private final boolean perCarOrder; private final boolean provideStoreDepth; private final boolean includeSoldNotStocked; private final boolean includeNewNumbers; private final Double includeWeekSupplyOption; private final JdbcTemplate jdbcTemplate; CreateNewSessionCallable ( int sessionId , int siteId , boolean includeSpecials , boolean includeObsoletes , boolean includeOverstocks , Boolean includeSupersede , boolean includeLessThanStdPkg ,Double includeWeekSupplyOption, boolean useInvestmentCollections , boolean includeFleets , boolean includeSystems , boolean perCarOrder , boolean provideStoreDepth , boolean includeSoldNotStocked , boolean includeNewNumbers , JdbcTemplate jdbcTemplate ) { this.siteId = siteId; this.sessionId = sessionId; this.includeSpecials = includeSpecials; this.includeObsoletes = includeObsoletes; this.includeOverstocks = includeOverstocks; this.includeSupersede = includeSupersede; this.includeLessThanStdPkg = includeLessThanStdPkg; this.useInvestmentCollections = useInvestmentCollections; this.includeFleets = includeFleets; this.includeSystems = includeSystems; this.perCarOrder = perCarOrder; this.provideStoreDepth = provideStoreDepth; this.includeSoldNotStocked = includeSoldNotStocked; this.includeNewNumbers = includeNewNumbers; this.includeWeekSupplyOption = includeWeekSupplyOption; this.jdbcTemplate = jdbcTemplate; } public Exception call ( ) { LOGGER.info ( "Create new session| Executing createSession for sessionId " + sessionId + " and siteId " + siteId ); Boolean includeSupersedeNotNull = false; try { Boolean includesuperSedeNull = includeSupersede; if ( includeSupersede != null ) includeSupersedeNotNull = includeSupersede; long startproc = System.currentTimeMillis ( ); jdbcTemplate.update ( "call CREATE_NEW_SESSION (?,?,?,?,?,?,?,?,?,?,?,?,?,?)" , sessionId , siteId , ( includeSpecials ) ? "1" : "0" , ( includeObsoletes ) ? "1" : "0" , ( includeOverstocks ) ? "1" : "0" , ( includeLessThanStdPkg ) ? "1" : "0" , ( includeFleets ) ? "1" : "0" , ( useInvestmentCollections ) ? "1" : "0" , ( perCarOrder ) ? "1" : "0" , ( provideStoreDepth ) ? "1" : "0" , ( includeSoldNotStocked ) ? "1" : "0" , ( includeNewNumbers ) ? "1" : "0" , ( includesuperSedeNull == null ? null : includeSupersede ? "1" : "0" ), includeWeekSupplyOption); LOGGER.info ( "Create new session| Execute CreateNewSessionCallable duration for sessionId " + sessionId + " and siteId " + siteId + ": " + ( System.currentTimeMillis ( ) - startproc ) ); } catch ( Exception e ) { LOGGER.error ( "Create new session| CreateNewSessionCallable: " , e ); return e; } LOGGER.debug ( "CREATE_NEW_SESSION SP parameters: {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {},{}" , sessionId , siteId , ( includeSpecials ) ? "1" : "0" , ( includeObsoletes ) ? "1" : "0" , ( includeOverstocks ) ? "1" : "0" , ( includeLessThanStdPkg ) ? "1" : "0" , ( includeFleets ) ? "1" : "0" , ( useInvestmentCollections ) ? "1" : "0" , ( perCarOrder ) ? "1" : "0" , ( provideStoreDepth ) ? "1" : "0" , ( includeSoldNotStocked ) ? "1" : "0" , ( includeNewNumbers ) ? "1" : "0" , ( includeSupersedeNotNull ) ? "1" : "0", includeWeekSupplyOption); return null; } } private static class CreateNewStoreClosingSessionCallable implements Callable { private final int siteId; private final int sessionId; private final JdbcTemplate jdbcTemplate; CreateNewStoreClosingSessionCallable ( int sessionId , int siteId , JdbcTemplate jdbcTemplate ) { this.sessionId = sessionId; this.siteId = siteId; this.jdbcTemplate = jdbcTemplate; } public Exception call ( ) { LOGGER.info ( "Create new store closing session| Executing createSession for sessionId " + sessionId + " and siteId " + siteId ); try { long startproc = System.currentTimeMillis ( ); jdbcTemplate.update ( "call CREATE_NEW_STORECLOSE_SESSION (?,?)" , sessionId , siteId ); LOGGER.info ( "Create new store closing session| Execute CreateNewSessionCallable duration for sessionId " + sessionId + " and siteId " + siteId + ": " + ( System.currentTimeMillis ( ) - startproc ) ); } catch ( Exception e ) { LOGGER.error ( "Create new store closing session| CreateNewStoreClosingSessionCallable: " , e ); return e; } LOGGER.debug ( "CREATE_NEW_STORECLOSE_SESSION SP parameters: {}, {}" , sessionId , siteId ); return null; } } private static class UpdateMarketSelectionForSessionThread extends Thread { private final List siteList; private final int sessionId; private final JdbcTemplate jdbcTemplate; UpdateMarketSelectionForSessionThread ( int sessionId , List siteList , JdbcTemplate jdbcTemplate ) { this.sessionId = sessionId; this.siteList = siteList; this.jdbcTemplate = jdbcTemplate; } public void run ( ) { try { jdbcTemplate.batchUpdate ( "insert into SESSION_MARKET_POSTAL using select ?, ?, mp.MARKET_POSTAL, mp.sum_vehicle_count " + "from MARKET_POSTAL mp where mp.MARKET_POSTAL in (select MARKET_POSTAL || '' from SITE_MARKET WHERE SITE_ID = ?) " , new BatchPreparedStatementSetter ( ) { @Override public void setValues ( PreparedStatement ps , int i ) throws SQLException { Integer siteId = siteList.get ( i ); ps.setInt ( 1 , sessionId ); ps.setInt ( 2 , siteId ); ps.setInt ( 3 , siteId ); } @Override public int getBatchSize ( ) { return siteList.size ( ); } } ); jdbcTemplate .batchUpdate ( "insert into SESSION_SITE_TOPAPP using select ?, ?, MAKE_NAME, MODEL_NAME, VEHICLE_YEAR, VEHICLE_COUNT, RN from (" + "select MAKE_NAME, MODEL_NAME, VEHICLE_YEAR, sum(SUM_VEHICLE_COUNT) as VEHICLE_COUNT, ROW_NUMBER() OVER (order by sum(SUM_VEHICLE_COUNT) desc, " + "MAKE_NAME, MODEL_NAME, VEHICLE_YEAR) RN from VEHICLE_COUNT_T where MARKET_POSTAL in " + "(select MARKET_POSTAL from SITE_MARKET where SITE_ID=?) group by MAKE_NAME, MODEL_NAME, VEHICLE_YEAR, SUM_VEHICLE_COUNT) " + "WHERE RN BETWEEN 1 AND 100 ORDER BY RN" , new BatchPreparedStatementSetter ( ) { @Override public void setValues ( PreparedStatement ps , int i ) throws SQLException { Integer siteId = siteList.get ( i ); ps.setInt ( 1 , sessionId ); ps.setInt ( 2 , siteId ); ps.setInt ( 3 , siteId ); } @Override public int getBatchSize ( ) { return siteList.size ( ); } } ); jdbcTemplate.update ( "UPDATE PULSE_SESSION SET STATUS_ID = 1, LAST_MODIFIED_DATE = sysdate WHERE SESSION_ID = ? AND STATUS_ID = 4" , sessionId ); } catch ( Exception e ) { LOGGER.error ( "Create new session| UpdateMarketSelectionForSessionThread: " + e.getMessage ( ) ); jdbcTemplate.update ( "UPDATE PULSE_SESSION SET STATUS_ID = 5, LAST_MODIFIED_DATE = sysdate WHERE SESSION_ID = ? AND STATUS_ID = 4" , sessionId ); } } } private static class UpdateMarketSelectionForSessionCallable implements Callable { private final List siteList; private final int sessionId; private final JdbcTemplate jdbcTemplate; UpdateMarketSelectionForSessionCallable ( int sessionId , List siteList , JdbcTemplate jdbcTemplate ) { this.sessionId = sessionId; this.siteList = siteList; this.jdbcTemplate = jdbcTemplate; } /* * Made the method transactional to back out the following if an error * occurs. NOTE!!!! As Dayo stated, the first query is a performance * issue since it reads so much data based on the sum (i.e. large IO on * the DB disk). There is no way around this as we cannot pre-calculate * the data since the user can add/remove zip codes from the site. * Please don't remove this comment so we won't forget the reason when * Venkat, and others start screaming. */ @Override public Exception call ( ) throws Exception { try { jdbcTemplate.batchUpdate ( "insert into SESSION_MARKET_POSTAL using select ?, ?, mp.MARKET_POSTAL, mp.sum_vehicle_count " + "from MARKET_POSTAL mp where mp.MARKET_POSTAL in (select MARKET_POSTAL || '' from SITE_MARKET WHERE SITE_ID = ?) " , new BatchPreparedStatementSetter ( ) { @Override public void setValues ( PreparedStatement ps , int i ) throws SQLException { Integer siteId = siteList.get ( i ); ps.setInt ( 1 , sessionId ); ps.setInt ( 2 , siteId ); ps.setInt ( 3 , siteId ); } @Override public int getBatchSize ( ) { return siteList.size ( ); } } ); jdbcTemplate .batchUpdate ( "insert into SESSION_SITE_TOPAPP using select ?, ?, MAKE_NAME, MODEL_NAME, VEHICLE_YEAR, VEHICLE_COUNT, RN from (" + "select MAKE_NAME, MODEL_NAME, VEHICLE_YEAR, sum(SUM_VEHICLE_COUNT) as VEHICLE_COUNT, ROW_NUMBER() OVER (order by sum(SUM_VEHICLE_COUNT) desc, " + "MAKE_NAME, MODEL_NAME, VEHICLE_YEAR) RN from VEHICLE_COUNT_T where MARKET_POSTAL in " + "(select MARKET_POSTAL from SITE_MARKET where SITE_ID=?) group by MAKE_NAME, MODEL_NAME, VEHICLE_YEAR, SUM_VEHICLE_COUNT) " + "WHERE RN BETWEEN 1 AND 100 ORDER BY RN" , new BatchPreparedStatementSetter ( ) { @Override public void setValues ( PreparedStatement ps , int i ) throws SQLException { Integer siteId = siteList.get ( i ); ps.setInt ( 1 , sessionId ); ps.setInt ( 2 , siteId ); ps.setInt ( 3 , siteId ); } @Override public int getBatchSize ( ) { return siteList.size ( ); } } ); } catch ( Exception e ) { LOGGER.error ( "Create new session| UpdateMarketSelectionForSessionCallable: " + e.getMessage ( ) ); return e; } return null; } } @Override public void finalizeSession ( Integer userId , Integer sessionId , Transmission[] transmissionArray ) { long start = System.currentTimeMillis ( ); java.sql.Date now = new java.sql.Date ( new java.util.Date ( ).getTime ( ) ); jdbcTemplate.batchUpdate ( "INSERT INTO TRANSMISSION (SESSION_ID, SITE_ID, " + " ORDER_STATUS_ID, ORDER_ON_DATE, ORDER_STATUS_DATE, " + " RETURN_STATUS_ID, RETURN_ON_DATE, RETURN_STATUS_DATE) " + " SELECT ?, SITE_ID, " + " CASE WHEN 'Pending' = ? THEN TRANSMISSION_STATUS_ID " + " ELSE null " + " END CASE, " + " ?, ?, " + " CASE WHEN 'Pending' = ? THEN TRANSMISSION_STATUS_ID " + " ELSE null " + " END CASE, " + " ?, ? " + " FROM TRANSMISSION_STATUS TS, SESSION_SITE SS " + " WHERE TS.TRANSMISSION_STATUS_CD = 'PP' " + " AND SS.SESSION_ID = ? AND SS.SITE_ID = ? " , new BatchPreparedStatementSetter ( ) { @Override public void setValues ( PreparedStatement ps , int i ) throws SQLException { Transmission t = transmissionArray[i]; ps.setInt ( 1 , sessionId ); java.util.Date orderDate = t.getOrderDate ( ); if ( orderDate != null && now.before ( orderDate ) ) { ps.setString ( 2 , "Pending" ); ps.setTimestamp ( 3 , new java.sql.Timestamp ( orderDate.getTime ( ) ) ); ps.setTimestamp ( 4 , new java.sql.Timestamp ( now.getTime ( ) ) ); } else if ( orderDate != null ) { ps.setString ( 2 , "Pending" ); ps.setTimestamp ( 3 , new java.sql.Timestamp ( now.getTime ( ) ) ); ps.setTimestamp ( 4 , new java.sql.Timestamp ( now.getTime ( ) ) ); } else { ps.setString ( 2 , null ); ps.setDate ( 3 , null ); ps.setDate ( 4 , null ); } java.util.Date returnDate = t.getReturnDate ( ); if ( returnDate != null && now.before ( returnDate ) ) { ps.setString ( 5 , "Pending" ); ps.setTimestamp ( 6 , new java.sql.Timestamp ( returnDate.getTime ( ) ) ); ps.setTimestamp ( 7 , new java.sql.Timestamp ( now.getTime ( ) ) ); } else if ( returnDate != null ) { ps.setString ( 5 , "Pending" ); ps.setTimestamp ( 6 , new java.sql.Timestamp ( now.getTime ( ) ) ); ps.setTimestamp ( 7 , new java.sql.Timestamp ( now.getTime ( ) ) ); } else { ps.setString ( 5 , null ); ps.setDate ( 6 , null ); ps.setDate ( 7 , null ); } ps.setInt ( 8 , sessionId ); ps.setInt ( 9 , t.getSiteId ( ) ); } @Override public int getBatchSize ( ) { return transmissionArray.length; } } ); jdbcTemplate .update ( "UPDATE PULSE_SESSION SET STATUS_ID = 3, LAST_MODIFIED_DATE = sysdate, FINALIZE_USER_ID=?, FINALIZE_DATE = sysdate" + " WHERE SESSION_ID = ? " , userId , sessionId ); List enHeaders = excelExporter.getHeaders ( "U" , "en" ); List frHeaders = excelExporter.getHeaders ( "U" , "fr" ); String insertTransmissionSQL = "INSERT INTO /*+ append*/ TRANSMISSION_DETAIL " + " (TRANSMISSION_ID, PRODUCT_ID, ORDER_QTY, KEEP_QTY, REASON_ID, HQ_ABBR, FIELD_ABBR, PART_NUMBER, " + " PRODUCT_DESC, PRODUCT_DESC_FR, PER_CAR, GROUP_CODE, JBR_STD_PKG, RETURN_QTY, RECOMMENDED_RETURN_QTY, RECOMMENDED_ORDER_QTY, " + " USER_ID, ON_HAND, PRICE, CORE_PRICE, MIN_STOCKING_QTY, PTS, EFFECTIVE_PTS, INITIAL_STOCKING_DT, MAX_STOCKING_QTY, " + " SOLD_LAST_12_MONTHS, SOLD_LAST_24_MONTHS, LEVEL4_PARENT_ID, FORECAST_MODEL_ID, RETURN_DESC, RETURN_DESC_FR, " + " EDIT_LINKED_SIM_PROD_ID, SIMILAR_PRODUCT_EDIT, SYSTEM_RECOMMENDED, SYSTEM_FLAG, LAST_SALE_DT, BUSINESS_RULES_APPLIED, HQ_ORDER_QTY, HQ_KEEP_QTY, " + " TOP_APP, DC_PRICE, VIO, TRANSFER_12_MONTHS, TRANSFER_24_MONTHS, LOST_SALES_24_MONTHS) " + " (SELECT TRANSMISSION_ID, P.PRODUCT_ID, ORDER_QTY, KEEP_QTY, " + " reason_id AS reason_code, P.HQ_ABBR, P.FIELD_ABBR, P.PART_NUMBER, P.PRODUCT_DESC, P.PRODUCT_DESC_FR, P.PER_CAR, P.GROUP_CODE, " + " P.JBR_STD_PKG, SSP.RETURN_QTY, SSP.RECOMMENDED_RETURN_QTY, SSP.RECOMMENDED_ORDER_QTY, PS.CREATED_USER_ID, SSP.ON_HAND, " + " (CASE WHEN ST.COST_COLUMN IN ('DC_PRICE') THEN P.DC_PRICE ELSE P.GOLDEN_PRICE END) AS PRICE, " + " (CASE WHEN ST.COST_COLUMN IN ('DC_PRICE') THEN P.DC_CORE_PRICE ELSE P.GOLDEN_CORE_PRICE END) AS CORE_PRICE,(CASE WHEN NVL(SSP.STD_PKG, 0) > 11 THEN ROUND(SSP.STD_PKG / 2)" + " ELSE SSP.MIN_STOCKING_QTY END) AS MIN_STOCKING_QTY, " + " SSP.PTS, SSP.EFFECTIVE_PTS, SSP.INITIAL_STOCKING_DT, (CASE WHEN NVL(SSP.STD_PKG, 0) > 11 THEN ROUND(SSP.STD_PKG / 2)" + " ELSE SSP.MAX_STOCKING_QTY END ) AS MAX_STOCKING_QTY, SSP.SOLD_LAST_12_MONTHS, SSP.SOLD_LAST_24_MONTHS, SSP.LEVEL4_PARENT_ID, SSP.FORECAST_MODEL_ID, " + " case when SSP.RETURN_QTY>0 and SSP.ON_HAND>0 and SSP.class='O' then '" + enHeaders.get ( 0 ) + "' " + " when SSP.RETURN_QTY>0 AND SSP.return_qty < SSP.on_hand then '" + enHeaders.get ( 1 ) + "' " + " when SSP.RETURN_QTY>0 and SSP.ON_HAND>0 and MIN_STOCKING_QTY>0 and SSP.INITIAL_STOCKING_DT is not null and SSP.class != 'O' then '" + enHeaders.get ( 2 ) + "' " + " when SSP.RETURN_QTY>0 and SSP.ON_HAND>0 and nvl(MIN_STOCKING_QTY,0)=0 and SSP.class != 'O' then '" + enHeaders.get ( 3 ) + "' " + " else null end as return_desc, " + " case when SSP.RETURN_QTY>0 and SSP.ON_HAND>0 and SSP.class='O' then '" + frHeaders.get ( 0 ) + "' " + " when SSP.RETURN_QTY>0 AND SSP.return_qty < SSP.on_hand then '" + frHeaders.get ( 1 ) + "' " + " when SSP.RETURN_QTY>0 and SSP.ON_HAND>0 and MIN_STOCKING_QTY>0 and SSP.INITIAL_STOCKING_DT is not null and SSP.class != 'O' then '" + frHeaders.get ( 2 ) + "' " + " when SSP.RETURN_QTY>0 and SSP.ON_HAND>0 and nvl(MIN_STOCKING_QTY,0)=0 and SSP.class != 'O' then '" + frHeaders.get ( 3 ) + "' " + " else null end as return_desc_fr, " + " EDIT_LINKED_SIM_PROD_ID, SIMILAR_PRODUCT_EDIT, SYSTEM_RECOMMENDED, SYSTEM_FLAG, LAST_SALE_DT, BUSINESS_RULES_APPLIED, HQ_ORDER_QTY, HQ_KEEP_QTY, " + " TOP_APP, DC_PRICE, VIO, TRANSFER_12_MONTHS, TRANSFER_24_MONTHS, LOST_SALES_24_MONTHS" + " FROM TRANSMISSION T " + " INNER JOIN SESSION_SITE_PRODUCT SSP ON T.SESSION_ID = SSP.SESSION_ID AND T.SITE_ID = SSP.SITE_ID " + " INNER JOIN PRODUCT P ON SSP.PRODUCT_ID = P.PRODUCT_ID " + " INNER JOIN SITE_PARAMETER S_PARAM ON S_PARAM.SITE_ID = SSP.SITE_ID AND S_PARAM.HIERARCHY_ID = SSP.LEVEL4_PARENT_ID " + " INNER JOIN PULSE_SESSION PS ON PS.SESSION_ID = SSP.SESSION_ID " + " INNER JOIN SITE S ON S.SITE_ID = T.SITE_ID " + " INNER JOIN SITE_TYPE ST ON ST.SITE_TYPE_CODE = S.SITE_TYPE_CODE " + " WHERE SSP.SESSION_ID = ? AND (ORDER_QTY > 0 OR RETURN_QTY>0 )) "; jdbcTemplate.update ( insertTransmissionSQL , sessionId ); // System.out.println("Transmission" +insertTransmissionSQL ); String insertSurvivingParts = "merge INTO /*+ append*/ TRANSMISSION_DETAIL td using\n" + "(\n" + " SELECT TRANSMISSION_ID\n" + " ,sp.PRODUCT_ID\n" + " ,P.HQ_ABBR\n" + " ,P.FIELD_ABBR\n" + " ,P.PART_NUMBER\n" + " ,P.PRODUCT_DESC\n" + " ,P.PRODUCT_DESC_FR\n" + " ,P.PER_CAR\n" + " ,P.GROUP_CODE\n" + " ,P.JBR_STD_PKG\n" + " ,PS.CREATED_USER_ID\n" + " ,sp.ON_HAND\n" + " ,(\n" + " CASE\n" + " WHEN ST.COST_COLUMN IN ('DC_PRICE')\n" + " THEN P.DC_PRICE\n" + " ELSE P.GOLDEN_PRICE\n" + " END\n" + " ) AS PRICE\n" + " ,(\n" + " CASE\n" + " WHEN ST.COST_COLUMN IN ('DC_PRICE')\n" + " THEN P.DC_CORE_PRICE\n" + " ELSE P.GOLDEN_CORE_PRICE\n" + " END\n" + " ) AS CORE_PRICE\n" + " ,sp.PTS\n" + " ,sp.EFFECTIVE_PTS\n" + " ,sp.INITIAL_STOCKING_DT\n" + " ,sp.SOLD_LAST_12_MONTHS\n" + " ,sp.SOLD_LAST_24_MONTHS\n" + " ,sp.LEVEL4_PARENT_ID\n" + " ,sp.FORECAST_MODEL_ID\n" + " ,CASE\n" + " WHEN\n" + " sp.ON_HAND > 0\n" + " AND sp.class = 'O'\n" + " THEN '" + enHeaders.get ( 0 ) + "'\n" + "\n" + " WHEN\n" + " sp.ON_HAND > 0\n" + " AND MIN_STOCKING_QTY > 0\n" + " AND sp.INITIAL_STOCKING_DT IS NOT NULL\n" + " AND sp.class != 'O'\n" + " THEN '" + enHeaders.get ( 2 ) + "'\n" + " WHEN\n" + "\n" + " sp.ON_HAND > 0\n" + " AND nvl(MIN_STOCKING_QTY,0) = 0\n" + " AND sp.class != 'O'\n" + " THEN '" + enHeaders.get ( 3 ) + "'\n" + " ELSE NULL\n" + " END AS return_desc\n" + " ,CASE\n" + " WHEN\n" + "\n" + " sp.ON_HAND > 0\n" + " AND sp.class = 'O'\n" + " THEN '" + frHeaders.get ( 0 ) + "'\n" + "\n" + " WHEN\n" + "\n" + " sp.ON_HAND > 0\n" + " AND MIN_STOCKING_QTY > 0\n" + " AND sp.INITIAL_STOCKING_DT IS NOT NULL\n" + " AND sp.class != 'O'\n" + " THEN '" + frHeaders.get ( 2 ) + "'\n" + " WHEN\n" + " sp.ON_HAND > 0\n" + " AND nvl(MIN_STOCKING_QTY,0) = 0\n" + " AND sp.class != 'O'\n" + " THEN '" + frHeaders.get ( 3 ) + "'\n" + " ELSE NULL\n" + " END AS return_desc_fr\n" + " ,LAST_SALE_DT\n" + " ,BUSINESS_RULES_APPLIED\n" + " ,TOP_APP\n" + " ,DC_PRICE\n" + " ,VIO\n" + " ,TRANSFER_12_MONTHS\n" + " ,TRANSFER_24_MONTHS\n" + " ,LOST_SALES_24_MONTHS\n" + " from\n" + " (select sp.SURVIVING_PRODUCT_ID ,ssp.SITE_ID,ssp.SESSION_ID from SUPERSEDE_PRODUCT sp\n" + " inner join SESSION_SITE_PRODUCT ssp on\n" + " (sp.SUPERSEDED_PRODUCT_ID=ssp.PRODUCT_ID and ssp.SESSION_ID=? and ssp.RECOMMENDED_RETURN_QTY>0 and NVL(ssp.KEEP_QTY,0)=0 ))sssp\n" + " INNER JOIN SITE_PRODUCT sp ON (sp.SITE_ID=sssp.SITE_ID and " + " sp.PRODUCT_ID=sssp.SURVIVING_PRODUCT_ID and NVL(sp.DEMAND_12_MONTHS,0)=0 and sp.INITIAL_STOCKING_DT < ADD_MONTHS(sysdate,-24)" + " and (nvl(sp.min_stocking_qty,0)>0 or nvl(sp.on_hand,0)>0) and nvl(sp.display_qty,0)=0)\n" + " INNER JOIN PRODUCT P ON sssp.SURVIVING_PRODUCT_ID = P.PRODUCT_ID\n" + " INNER JOIN SITE_PARAMETER S_PARAM ON S_PARAM.SITE_ID = sssp.SITE_ID\n" + " AND S_PARAM.HIERARCHY_ID = sp.LEVEL4_PARENT_ID\n" + " INNER JOIN PULSE_SESSION PS ON PS.SESSION_ID = sssp.SESSION_ID\n" + " INNER JOIN SITE S ON S.SITE_ID = sp.SITE_ID\n" + " INNER JOIN SITE_TYPE ST ON ST.SITE_TYPE_CODE = S.SITE_TYPE_CODE\n" + " Inner JOIN TRANSMISSION T ON (T.SESSION_ID=sssp.SESSION_ID AND T.SITE_ID = sssp.SITE_ID )\n" + ") spt on ( td.TRANSMISSION_ID=spt.TRANSMISSION_ID and td.PRODUCT_ID=spt.PRODUCT_ID)\n" + "when not matched then insert\n" + " (td.TRANSMISSION_ID, td.PRODUCT_ID, td.ORDER_QTY, td.KEEP_QTY, td.REASON_ID, td.HQ_ABBR, td.FIELD_ABBR, td.PART_NUMBER,\n" + " td.PRODUCT_DESC, td.PRODUCT_DESC_FR, td.PER_CAR, td.GROUP_CODE, td.JBR_STD_PKG, td.RETURN_QTY, td.RECOMMENDED_RETURN_QTY, td.RECOMMENDED_ORDER_QTY,\n" + " td.USER_ID, td.ON_HAND, td.PRICE, td.CORE_PRICE, td.MIN_STOCKING_QTY, td.PTS, td.EFFECTIVE_PTS, td.INITIAL_STOCKING_DT, td.MAX_STOCKING_QTY,\n" + " td.SOLD_LAST_12_MONTHS, td.SOLD_LAST_24_MONTHS, td.LEVEL4_PARENT_ID, td.FORECAST_MODEL_ID, td.RETURN_DESC, td.RETURN_DESC_FR,\n" + " td.EDIT_LINKED_SIM_PROD_ID, td.SIMILAR_PRODUCT_EDIT, td.SYSTEM_RECOMMENDED, td.SYSTEM_FLAG, td.LAST_SALE_DT, td.BUSINESS_RULES_APPLIED, td.HQ_ORDER_QTY, td.HQ_KEEP_QTY,\n" + " td.TOP_APP, td.DC_PRICE, td.VIO, td.TRANSFER_12_MONTHS, td.TRANSFER_24_MONTHS, td.LOST_SALES_24_MONTHS)\n" + "values ( spt.TRANSMISSION_ID,spt.PRODUCT_ID,0,0,null,spt.HQ_ABBR,spt.FIELD_ABBR,spt.PART_NUMBER,spt.PRODUCT_DESC,spt.PRODUCT_DESC_FR,\n" + " spt.PER_CAR,spt.GROUP_CODE,spt.JBR_STD_PKG,spt.ON_HAND,null,null,spt.CREATED_USER_ID,spt.ON_HAND,spt.PRICE,spt.CORE_PRICE,0,spt.pts,spt.EFFECTIVE_PTS,\n" + " spt.INITIAL_STOCKING_DT,0,spt.SOLD_LAST_12_MONTHS,spt.SOLD_LAST_24_MONTHS,spt.LEVEL4_PARENT_ID,spt.FORECAST_MODEL_ID,spt.return_desc,spt.return_desc_fr,\n" + " null,null,null,null,spt.LAST_SALE_DT,spt.BUSINESS_RULES_APPLIED,null,null,spt.TOP_APP\n" + " ,spt.DC_PRICE,spt.VIO,spt.TRANSFER_12_MONTHS,spt.TRANSFER_24_MONTHS,spt.LOST_SALES_24_MONTHS)"; //jdbcTemplate.update ( insertSurvivingParts , sessionId ); String insertParaArchivedSQL = "insert into /*+ append*/ SESSION_SITE_PARA_ARCHIVED " + "(select SESSION_ID, ssp.SITE_ID, ssp.HIERARCHY_ID, sp.OFFSET, IGNORE_STOCK, STOCK_IT, IS_LINKED " + "from SESSION_SITE_PARAMETER ssp, SITE_PARAMETER sp " + "where SESSION_ID=? and sp.SITE_ID=ssp.SITE_ID and sp.HIERARCHY_ID=ssp.HIERARCHY_ID)"; jdbcTemplate.update ( insertParaArchivedSQL , sessionId ); jdbcTemplate.update ( "MERGE INTO PULSE_SESSION PS " + " USING (select SUM(NVL(ORDER_TOTAL,0)) AS ORDER_TOTAL, " + " SUM(NVL(STOCKING_RETURN,0)) AS STOCKING_RETURN, " + " SUM(NVL(NON_STOCKING_RETURN,0)) AS NON_STOCKING_RETURN " + " from session_site_parameter SSP " + " where session_id = ? and site_id in (select site_id from session_site where session_id = ?) " + " and HIERARCHY_LEVEL=1) B " + " ON (ps.session_id = ?) " + " WHEN MATCHED THEN " + " UPDATE SET PS.ORDER_TOTAL=B.ORDER_TOTAL, " + " PS.STOCKING_RETURN_TOTAL=B.STOCKING_RETURN, " + " PS.NON_STOCKING_RETURN_TOTAL=B.NON_STOCKING_RETURN" , sessionId , sessionId , sessionId ); List sites = jdbcTemplate.queryForList ( "SELECT SITE_ID FROM SESSION_SITE WHERE SESSION_ID = ? " , new Object[]{sessionId} , Integer.class ); for (Integer s : sites) { jdbcTemplate .update ( "insert into session_site_product_finalized (SESSION_ID, SITE_ID, PRODUCT_ID, EFFECTIVE_PTS, " + " ON_HAND, SOLD_LAST_12_MONTHS, MIN_STOCKING_QTY, MAX_STOCKING_QTY, INITIAL_STOCK_AMOUNT, LEVEL4_PARENT_ID, " + " PRICE_WT_CORE, INITIAL_STOCKING_DT, DEMAND_12_MONTHS, OCCURANCE_12_MONTHS, OCCURANCE_18_MONTHS, OCCURANCE_24_MONTHS, " + " CLASS, KEEP_FLAG, DISPLAY_QTY, SOLD_LAST_24_MONTHS, VIO, LOST_SALES_12_MONTHS, LOST_SALES_24_MONTHS, " + " ORDER_QTY, KEEP_QTY, PCT_MARKET, RETURN_QTY, PTS, STD_PKG, RECOMMENDED_RETURN_QTY, RECOMMENDED_ORDER_QTY, " + " BLOCK_ORDER, BLOCK_RETURN, USER_ID, REASON_ID, FORECAST_MODEL_ID, CLUSTER_PCT_SALE, DC_SALES_LAST_12_MONTHS, " + " DC_SALES_LAST_24_MONTHS, TRANSFER_12_MONTHS, TRANSFER_24_MONTHS, PEER_SALES_12, REVIEWED, FLEET_FLAG, SYSTEM_FLAG, " + "SYSTEM_RECOMMENDED, EDIT_LINKED_SIM_PROD_ID, SIMILAR_PRODUCT_EDIT, DECLINE_ORDER, DECLINE_RETURN, LAST_SALE_DT, BUSINESS_RULES_APPLIED, HQ_ORDER_QTY, HQ_KEEP_QTY,ORG_MIN_STOCKING_QTY,ORG_MAX_STOCKING_QTY) " + " (select SESSION_ID, SITE_ID, PRODUCT_ID, EFFECTIVE_PTS, " + " ON_HAND, SOLD_LAST_12_MONTHS, MIN_STOCKING_QTY, MAX_STOCKING_QTY, INITIAL_STOCK_AMOUNT, LEVEL4_PARENT_ID, " + " PRICE_WT_CORE, INITIAL_STOCKING_DT, DEMAND_12_MONTHS, OCCURANCE_12_MONTHS, OCCURANCE_18_MONTHS, OCCURANCE_24_MONTHS, " + " CLASS, KEEP_FLAG, DISPLAY_QTY, SOLD_LAST_24_MONTHS, VIO, LOST_SALES_12_MONTHS, LOST_SALES_24_MONTHS, " + " ORDER_QTY, KEEP_QTY, PCT_MARKET, RETURN_QTY, PTS, STD_PKG, RECOMMENDED_RETURN_QTY, RECOMMENDED_ORDER_QTY, " + " BLOCK_ORDER, BLOCK_RETURN, USER_ID, REASON_ID, FORECAST_MODEL_ID, CLUSTER_PCT_SALE, DC_SALES_LAST_12_MONTHS, " + " DC_SALES_LAST_24_MONTHS, TRANSFER_12_MONTHS, TRANSFER_24_MONTHS, PEER_SALES_12, REVIEWED, FLEET_FLAG, SYSTEM_FLAG, SYSTEM_RECOMMENDED, " + " EDIT_LINKED_SIM_PROD_ID, SIMILAR_PRODUCT_EDIT, DECLINE_ORDER, DECLINE_RETURN, LAST_SALE_DT, BUSINESS_RULES_APPLIED, HQ_ORDER_QTY, HQ_KEEP_QTY ,ORG_MIN_STOCKING_QTY,ORG_MAX_STOCKING_QTY" + " from session_site_product where site_Id = ? and session_id = ?)" , s , sessionId ); jdbcTemplate .update ( "insert into session_site_para_finalized (SESSION_ID, SITE_ID, HIERARCHY_ID, CRITICALITY, " + " ORDER_TOTAL, RETURN_TOTAL, NON_STOCKING_RETURN, STOCKING_RETURN, RETURN_COUNT, ORDER_COUNT, " + " NET_COUNT, MIN_GREATER_ZERO, COGS, HIERARCHY_LEVEL, NET_TOTAL, HIERARCHY_ISENABLED, FORECAST_CRITICALITY, DC_COGS, " + " DECLINE_ORDER, DECLINE_RETURN)" + " (select SESSION_ID, SITE_ID, HIERARCHY_ID, CRITICALITY, " + " ORDER_TOTAL, RETURN_TOTAL, NON_STOCKING_RETURN, STOCKING_RETURN, RETURN_COUNT, ORDER_COUNT, " + " NET_COUNT, MIN_GREATER_ZERO, COGS, HIERARCHY_LEVEL, NET_TOTAL, HIERARCHY_ISENABLED, FORECAST_CRITICALITY, DC_COGS, " + " DECLINE_ORDER, DECLINE_RETURN" + " from session_site_parameter where site_Id = ? and session_id = ?)" , s , sessionId ); } jdbcTemplate.update ( "delete from SESSION_SEND_DATE where SESSION_ID=? " , sessionId ); LOGGER.info ( "finalizeSession| Finalize duration for sessionId " + sessionId + ": " + ( System.currentTimeMillis ( ) - start ) ); updateFollowerSiteProduct ( sessionId ); } @Override public void finalizeMinMaxSession (Integer userId , Integer sessionId , Transmission[] transmissionArray , Boolean reviewOnly, int siteId , boolean onlyForSingleSite ) { long start = System.currentTimeMillis ( ); java.sql.Date now = new java.sql.Date ( new java.util.Date ( ).getTime ( ) ); jdbcTemplate.batchUpdate ( "INSERT INTO TRANSMISSION_MIN_MAX (SESSION_ID, SITE_ID, " + " TRANSMISSION_STATUS_ID, TRANSMISSION_DATE, " + " I8_FILE_NAME ) " + " SELECT ?, SITE_ID, " + " CASE WHEN 'Pending' = ? THEN TRANSMISSION_STATUS_ID " + " ELSE null " + " END CASE, " + " ?, ? FROM TRANSMISSION_STATUS TS, SESSION_SITE SS " + " WHERE SS.SESSION_ID = ? AND SS.SITE_ID = ? AND TS.TRANSMISSION_STATUS_CD = 'PP'" , new BatchPreparedStatementSetter ( ) { @Override public void setValues ( PreparedStatement ps , int i ) throws SQLException { Transmission t = transmissionArray[i]; ps.setInt ( 1 , sessionId ); ps.setString ( 2 , "Pending" ); ps.setDate ( 3 , now ); ps.setString ( 4 , null ); ps.setInt ( 5 , sessionId ); ps.setInt ( 6 , t.getSiteId ( ) ); } @Override public int getBatchSize ( ) { return transmissionArray.length; } } ); List> orangeBarTotals = getMinMaxSessionTotals ( sessionId );//sessionDAO.getMinMaxSessionOrderReturnTotals(sessionId, siteId, true); if ( orangeBarTotals.size ( ) > 0 ) { Map obj = orangeBarTotals.get ( 0 ); jdbcTemplate.update ( "UPDATE pulse_session SET CURRENT_INVENTORY_TOTAL = ?, RECOMMENDED_INVENTORY = ?, " + "NET_CHANGE = ?, DEPTH_INC = ?, DEPTH_DEC = ? where SESSION_ID = ? " , obj.get ( "currentInvTotal" ) , obj.get ( "recommendedTotal" ) , obj.get ( "netChange" ) , obj.get ( "depthInc" ) , obj.get ( "depthDec" ) , sessionId ); jdbcTemplate.update ( "MERGE into session_site ss using \n" + "(select sum(currentInvTotal) as currentInvTotal,\n" + " sum(recommendedTotal) as recommendedTotal,\n" + " sum(netChange) as netChange,\n" + " sum(depthInc) as depthInc,\n" + " sum(depthDec)as depthDec,\n" + " site_id,\n" + " site_short_name,\n" + " session_id \n" + "from\n" + "(SELECT\n" + "a.site_id,(select distinct s.site_short_name from site s where s.site_id=a.site_id and s.active_flag='Y') site_short_name, a.session_id,\n" + "NVL(a.curr_max,0) * NVL(c.golden_price,0) AS currentInvTotal ,\n" + "NVL(a.max_stocking_qty,0) * NVL(c.golden_price,0) AS recommendedTotal ,\n" + "NVL(a.max_stocking_qty,0) * NVL(c.golden_price,0) - NVL(a.curr_max, 0) * NVL(c.golden_price, 0) as netChange,\n" + "case when ((a.max_stocking_qty * c.golden_price)-(a.curr_max * c.golden_price))>=0 then \n" + " NVL(a.max_stocking_qty,0) * NVL(c.golden_price,0) - NVL(a.curr_max, 0) * NVL(c.golden_price, 0) else 0 end depthInc,\n" + "case when ((a.max_stocking_qty * c.golden_price)-(a.curr_max * c.golden_price))<0 then \n" + " NVL(a.max_stocking_qty,0) * NVL(c.golden_price,0) - NVL(a.curr_max, 0) * NVL(c.golden_price, 0) else 0 end depthDec\n" + "FROM session_site_product a, product c\n" + "WHERE session_id = ? AND a.site_id " + ( onlyForSingleSite ? ( " = " + siteId ) : ( " IN (SELECT site_id FROM session_site WHERE session_Id = " + sessionId + " )" ) ) + "\n" + "AND (a.reviewed " + (reviewOnly ? ("=1)") : ("IN (select reviewed from session_site_product where session_id =" +sessionId + ") or reviewed is null or reviewed =0 or reviewed=1 and session_id = "+sessionId + " )")) +" \n" + "AND a.product_id = c.product_id)\n" + "group by site_id,site_short_name,session_id) s \n" + "on (ss.session_id=s.session_id and ss.site_id=s.site_id)\n" + "when matched then update set ss.CURRENT_INVENTORY_TOTAL=s.currentInvTotal,ss.RECOMMENDED_INVENTORY=s.recommendedTotal,ss.NET_CHANGE=s.netChange,ss.depth_Inc=s.depthInc,ss.depth_dec= s.depthDec" , sessionId ); } jdbcTemplate .update ( "UPDATE PULSE_SESSION SET STATUS_ID = 3, LAST_MODIFIED_DATE = sysdate, FINALIZE_USER_ID=?, FINALIZE_DATE = sysdate" + " WHERE SESSION_ID = ? " , userId , sessionId ); String insertTransmissionSQL = "INSERT INTO TRANSMISSION_DETAIL_MIN_MAX\n" + " (TRANSMISSION_ID, PRODUCT_ID, HQ_ABBR, FIELD_ABBR, PART_NUMBER,\n" + " PRODUCT_DESC, MIN_STOCKING_QTY, MAX_STOCKING_QTY, SOLD_LAST_12_MONTHS, SOLD_LAST_24_MONTHS, PER_CAR, FORMULA_ID," + " LEAD_TIME )\n" + " (SELECT TRANSMISSION_ID, P.PRODUCT_ID, P.HQ_ABBR, P.FIELD_ABBR, P.PART_NUMBER, P.PRODUCT_DESC,\n" + " MIN_STOCKING_QTY, SSP.MAX_STOCKING_QTY, SSP.SOLD_LAST_12_MONTHS,SSP.SOLD_LAST_24_MONTHS, PER_CAR,\n" + "cl.calc_formula_id as FORMULA_ID, ( CASE WHEN cl.calc_formula_id = 2 THEN pc.config_value ELSE NULL END ) lead_time\n" + "FROM TRANSMISSION_MIN_MAX T\n" + "INNER JOIN SESSION_SITE_PRODUCT SSP ON T.SESSION_ID = SSP.SESSION_ID AND T.SITE_ID = SSP.SITE_ID\n" + "INNER JOIN PRODUCT P ON SSP.PRODUCT_ID = P.PRODUCT_ID\n" + "INNER JOIN min_max_calc_line cl ON cl.field_abbr = p.field_abbr\n" + "INNER JOIN pulse_config pc ON pc.config_code = 'FILTERS_LEAD_TIME'\n" + "WHERE SSP.SESSION_ID = ? and (SSP.reviewed "+ (reviewOnly ? ( "=1))" ) : ("IN (select ssp.reviewed from session_site_product ssp where ssp.session_id = "+sessionId + ") or ssp.reviewed is null or ssp.reviewed =0 or ssp.reviewed=1 and ssp.session_id = "+sessionId + " ))")); jdbcTemplate.update ( insertTransmissionSQL , sessionId ); // List sites = jdbcTemplate.queryForList ( "SELECT SITE_ID FROM SESSION_SITE WHERE SESSION_ID = ? " , new Object[]{sessionId} , Integer.class ); for (Integer s : sites) { jdbcTemplate .update ( "insert into session_site_product_finalized (SESSION_ID, SITE_ID, PRODUCT_ID, EFFECTIVE_PTS, " + " ON_HAND, SOLD_LAST_12_MONTHS, MIN_STOCKING_QTY, MAX_STOCKING_QTY, INITIAL_STOCK_AMOUNT, LEVEL4_PARENT_ID, " + " PRICE_WT_CORE, INITIAL_STOCKING_DT, DEMAND_12_MONTHS, OCCURANCE_12_MONTHS, OCCURANCE_18_MONTHS, OCCURANCE_24_MONTHS, " + " CLASS, KEEP_FLAG, DISPLAY_QTY, SOLD_LAST_24_MONTHS, VIO, LOST_SALES_12_MONTHS, LOST_SALES_24_MONTHS, " + " ORDER_QTY, KEEP_QTY, PCT_MARKET, RETURN_QTY, PTS, STD_PKG, RECOMMENDED_RETURN_QTY, RECOMMENDED_ORDER_QTY, " + " BLOCK_ORDER, BLOCK_RETURN, USER_ID, REASON_ID, FORECAST_MODEL_ID, CLUSTER_PCT_SALE, DC_SALES_LAST_12_MONTHS, " + " DC_SALES_LAST_24_MONTHS, TRANSFER_12_MONTHS, TRANSFER_24_MONTHS, PEER_SALES_12, REVIEWED, FLEET_FLAG, SYSTEM_FLAG, " + "SYSTEM_RECOMMENDED, EDIT_LINKED_SIM_PROD_ID, SIMILAR_PRODUCT_EDIT, DECLINE_ORDER, DECLINE_RETURN, LAST_SALE_DT, BUSINESS_RULES_APPLIED, HQ_ORDER_QTY, HQ_KEEP_QTY,ORG_MIN_STOCKING_QTY,ORG_MAX_STOCKING_QTY) " + " (select SESSION_ID, SITE_ID, PRODUCT_ID, EFFECTIVE_PTS, " + " ON_HAND, SOLD_LAST_12_MONTHS, MIN_STOCKING_QTY, MAX_STOCKING_QTY, INITIAL_STOCK_AMOUNT, LEVEL4_PARENT_ID, " + " PRICE_WT_CORE, INITIAL_STOCKING_DT, DEMAND_12_MONTHS, OCCURANCE_12_MONTHS, OCCURANCE_18_MONTHS, OCCURANCE_24_MONTHS, " + " CLASS, KEEP_FLAG, DISPLAY_QTY, SOLD_LAST_24_MONTHS, VIO, LOST_SALES_12_MONTHS, LOST_SALES_24_MONTHS, " + " ORDER_QTY, KEEP_QTY, PCT_MARKET, RETURN_QTY, PTS, STD_PKG, RECOMMENDED_RETURN_QTY, RECOMMENDED_ORDER_QTY, " + " BLOCK_ORDER, BLOCK_RETURN, USER_ID, REASON_ID, FORECAST_MODEL_ID, CLUSTER_PCT_SALE, DC_SALES_LAST_12_MONTHS, " + " DC_SALES_LAST_24_MONTHS, TRANSFER_12_MONTHS, TRANSFER_24_MONTHS, PEER_SALES_12, REVIEWED, FLEET_FLAG, SYSTEM_FLAG, SYSTEM_RECOMMENDED, " + " EDIT_LINKED_SIM_PROD_ID, SIMILAR_PRODUCT_EDIT, DECLINE_ORDER, DECLINE_RETURN, LAST_SALE_DT, BUSINESS_RULES_APPLIED, HQ_ORDER_QTY, HQ_KEEP_QTY,ORG_MIN_STOCKING_QTY,ORG_MAX_STOCKING_QTY " + " from session_site_product where site_Id = ? and session_id = ? and (reviewed "+ (reviewOnly ? (" =1)) ") : ("IN (select reviewed from session_site_product where session_id =" +sessionId + ") or reviewed is null or reviewed =0 or reviewed=1 and session_id = "+sessionId + " ))") ), s , sessionId ) ; } jdbcTemplate.update ( "delete from SESSION_SEND_DATE where SESSION_ID=? " , sessionId ); LOGGER.info ( "finalizeMinMaxSession| Finalize duration for sessionId " + sessionId + ": " + ( System.currentTimeMillis ( ) - start ) ); updateFollowerSiteProduct ( sessionId ); } @Override public void finalizeACOSession ( Integer userId , Integer sessionId , Transmission[] transmissionArray ) { long start = System.currentTimeMillis ( ); java.sql.Date now = new java.sql.Date ( new java.util.Date ( ).getTime ( ) ); jdbcTemplate.batchUpdate ( "INSERT INTO TRANSMISSION (SESSION_ID, SITE_ID, ORDER_STATUS_ID, ORDER_ON_DATE, ORDER_STATUS_DATE, " + " RETURN_STATUS_ID, RETURN_ON_DATE, RETURN_STATUS_DATE) SELECT ?, SITE_ID, " + " CASE WHEN 'Pending' = ? THEN TRANSMISSION_STATUS_ID ELSE null END CASE, " + " ?, ?, CASE WHEN 'Pending' = ? THEN TRANSMISSION_STATUS_ID ELSE null " + " END CASE, ?, ? FROM TRANSMISSION_STATUS TS, SESSION_SITE SS " + " WHERE TS.TRANSMISSION_STATUS_CD = 'PP' AND SS.SESSION_ID = ? AND SS.SITE_ID = ? " , new BatchPreparedStatementSetter ( ) { @Override public void setValues ( PreparedStatement ps , int i ) throws SQLException { Transmission t = transmissionArray[i]; ps.setInt ( 1 , sessionId ); java.util.Date orderDate = t.getOrderDate ( ); if ( orderDate != null && now.before ( orderDate ) ) { ps.setString ( 2 , "Pending" ); ps.setTimestamp ( 3 , new java.sql.Timestamp ( orderDate.getTime ( ) ) ); ps.setTimestamp ( 4 , new java.sql.Timestamp ( now.getTime ( ) ) ); } else if ( orderDate != null ) { ps.setString ( 2 , "Pending" ); ps.setTimestamp ( 3 , new java.sql.Timestamp ( now.getTime ( ) ) ); ps.setTimestamp ( 4 , new java.sql.Timestamp ( now.getTime ( ) ) ); } else { ps.setString ( 2 , null ); ps.setDate ( 3 , null ); ps.setDate ( 4 , null ); } java.util.Date returnDate = t.getReturnDate ( ); if ( returnDate != null && now.before ( returnDate ) ) { ps.setString ( 5 , "Pending" ); ps.setTimestamp ( 6 , new java.sql.Timestamp ( returnDate.getTime ( ) ) ); ps.setTimestamp ( 7 , new java.sql.Timestamp ( now.getTime ( ) ) ); } else if ( returnDate != null ) { ps.setString ( 5 , "Pending" ); ps.setTimestamp ( 6 , new java.sql.Timestamp ( now.getTime ( ) ) ); ps.setTimestamp ( 7 , new java.sql.Timestamp ( now.getTime ( ) ) ); } else { ps.setString ( 5 , null ); ps.setDate ( 6 , null ); ps.setDate ( 7 , null ); } ps.setInt ( 8 , sessionId ); ps.setInt ( 9 , t.getSiteId ( ) ); } @Override public int getBatchSize ( ) { return transmissionArray.length; } } ); jdbcTemplate.update ( "UPDATE PULSE_SESSION SET STATUS_ID = 3, LAST_MODIFIED_DATE = sysdate, FINALIZE_USER_ID=?, FINALIZE_DATE = sysdate" + " WHERE SESSION_ID = ? " , userId , sessionId ); List enHeaders = excelExporter.getHeaders ( "U" , "en" ); List frHeaders = excelExporter.getHeaders ( "U" , "fr" ); String insertTransmissionSQL = "INSERT INTO /*+ append*/ TRANSMISSION_DETAIL " + " (TRANSMISSION_ID, PRODUCT_ID, ORDER_QTY, KEEP_QTY, REASON_ID, HQ_ABBR, FIELD_ABBR, PART_NUMBER, " + " PRODUCT_DESC, PRODUCT_DESC_FR, PER_CAR, GROUP_CODE, JBR_STD_PKG, RETURN_QTY, RECOMMENDED_RETURN_QTY, RECOMMENDED_ORDER_QTY, " + " USER_ID, ON_HAND, PRICE, CORE_PRICE, MIN_STOCKING_QTY, PTS, EFFECTIVE_PTS, INITIAL_STOCKING_DT, MAX_STOCKING_QTY, " + " SOLD_LAST_12_MONTHS, SOLD_LAST_24_MONTHS, LEVEL4_PARENT_ID, FORECAST_MODEL_ID, RETURN_DESC, RETURN_DESC_FR, " + " EDIT_LINKED_SIM_PROD_ID, SIMILAR_PRODUCT_EDIT, SYSTEM_RECOMMENDED, SYSTEM_FLAG, LAST_SALE_DT, BUSINESS_RULES_APPLIED, HQ_ORDER_QTY, HQ_KEEP_QTY, " + " TOP_APP, DC_PRICE, VIO, TRANSFER_12_MONTHS, TRANSFER_24_MONTHS, LOST_SALES_24_MONTHS) " + " (SELECT TRANSMISSION_ID, P.PRODUCT_ID, c.ORDER_QTY, c.KEEP_QTY, " + " reason_id AS reason_code, P.HQ_ABBR, P.FIELD_ABBR, P.PART_NUMBER, P.PRODUCT_DESC, P.PRODUCT_DESC_FR, P.PER_CAR, P.GROUP_CODE, " + " P.JBR_STD_PKG, c.RETURN_QTY, SSP.RECOMMENDED_RETURN_QTY, SSP.RECOMMENDED_ORDER_QTY, PS.CREATED_USER_ID, c.ON_HAND, " + " (CASE WHEN ST.COST_COLUMN IN ('DC_PRICE') THEN P.DC_PRICE ELSE P.GOLDEN_PRICE END) AS PRICE, " + " (CASE WHEN ST.COST_COLUMN IN ('DC_PRICE') THEN P.DC_CORE_PRICE ELSE P.GOLDEN_CORE_PRICE END) AS CORE_PRICE, c.MIN_STOCKING_QTY, " + " SSP.PTS, SSP.EFFECTIVE_PTS, SSP.INITIAL_STOCKING_DT, SSP.MAX_STOCKING_QTY, SSP.SOLD_LAST_12_MONTHS, SSP.SOLD_LAST_24_MONTHS, SSP.LEVEL4_PARENT_ID, SSP.FORECAST_MODEL_ID, " + " case when c.RETURN_QTY>0 and c.ON_HAND>0 and SSP.class='O' then '" + enHeaders.get ( 0 ) + "' " + " when c.RETURN_QTY>0 AND c.return_qty < c.on_hand then '" + enHeaders.get ( 1 ) + "' " + " when c.RETURN_QTY>0 and c.ON_HAND>0 and c.MIN_STOCKING_QTY>0 and SSP.INITIAL_STOCKING_DT is not null and SSP.class != 'O' then '" + enHeaders.get ( 2 ) + "' " + " when c.RETURN_QTY>0 and c.ON_HAND>0 and nvl(c.MIN_STOCKING_QTY,0)=0 and SSP.class != 'O' then '" + enHeaders.get ( 3 ) + "' " + " else null end as return_desc, " + " case when c.RETURN_QTY>0 and c.ON_HAND>0 and SSP.class='O' then '" + frHeaders.get ( 0 ) + "' " + " when c.RETURN_QTY>0 AND c.return_qty < c.on_hand then '" + frHeaders.get ( 1 ) + "' " + " when c.RETURN_QTY>0 and c.ON_HAND>0 and c.MIN_STOCKING_QTY>0 and SSP.INITIAL_STOCKING_DT is not null and SSP.class != 'O' then '" + frHeaders.get ( 2 ) + "' " + " when SSP.RETURN_QTY>0 and SSP.ON_HAND>0 and nvl(c.MIN_STOCKING_QTY,0)=0 and SSP.class != 'O' then '" + frHeaders.get ( 3 ) + "' " + " else null end as return_desc_fr, " + " EDIT_LINKED_SIM_PROD_ID, SIMILAR_PRODUCT_EDIT, SYSTEM_RECOMMENDED, SYSTEM_FLAG, LAST_SALE_DT, c.BUSINESS_RULES_APPLIED, HQ_ORDER_QTY, HQ_KEEP_QTY, " + " TOP_APP, DC_PRICE, VIO, TRANSFER_12_MONTHS, TRANSFER_24_MONTHS, LOST_SALES_24_MONTHS" + " FROM TRANSMISSION T " + " INNER JOIN SESSION_SITE_PRODUCT_COVERAGE c ON T.SESSION_ID = c.SESSION_ID AND T.SITE_ID = c.SITE_ID " + " INNER JOIN SESSION_SITE_PRODUCT SSP ON c.SESSION_ID = SSP.SESSION_ID AND c.SITE_ID = SSP.SITE_ID AND c.PRODUCT_ID = SSP.PRODUCT_ID " + " INNER JOIN PRODUCT P ON SSP.PRODUCT_ID = P.PRODUCT_ID " + " INNER JOIN SITE_PARAMETER S_PARAM ON S_PARAM.SITE_ID = SSP.SITE_ID AND S_PARAM.HIERARCHY_ID = SSP.LEVEL4_PARENT_ID " + " INNER JOIN PULSE_SESSION PS ON PS.SESSION_ID = SSP.SESSION_ID " + " INNER JOIN SITE S ON S.SITE_ID = T.SITE_ID " + " INNER JOIN SITE_TYPE ST ON ST.SITE_TYPE_CODE = S.SITE_TYPE_CODE " + " WHERE SSP.SESSION_ID = ? AND (c.ORDER_QTY > 0 OR c.KEEP_QTY < greatest(NVL(c.ON_HAND,0), NVL(c.MIN_STOCKING_QTY,0)))) "; jdbcTemplate.update ( insertTransmissionSQL , sessionId ); //TODO: THIS NEEDS TO CHANGE // String insertParaArchivedSQL = "insert into /*+ append*/ SESSION_SITE_PARA_ARCHIVED " // + "(select SESSION_ID, ssp.SITE_ID, ssp.HIERARCHY_ID, sp.OFFSET, IGNORE_STOCK, STOCK_IT, IS_LINKED " // + "from SESSION_SITE_PARAMETER ssp, SITE_PARAMETER sp " // + "where SESSION_ID=? and sp.SITE_ID=ssp.SITE_ID and sp.HIERARCHY_ID=ssp.HIERARCHY_ID)"; // // jdbcTemplate.update(insertParaArchivedSQL, new Object[] { sessionId }); jdbcTemplate.update ( "MERGE INTO PULSE_SESSION PS USING " + "(select ORDER_TOTAL, STOCKING_RETURN, NON_STOCKING_RETURN from " + "(select session_id, site_id, sum(order_total) as order_total, sum(non_stocking_return) as non_stocking_return, sum(STOCKING_RETURN) as STOCKING_RETURN from " + "(select a.session_id, a.site_id, sh.hierarchy_id, " + "SUM (CASE WHEN (on_hand > 0 AND order_qty > on_hand) THEN NVL(CEIL((order_qty-on_hand)/std_pkg) * std_pkg * price_wt_core,0) " + "WHEN (on_hand > 0 AND NVL(order_qty,0) <= on_hand ) THEN 0 " + "ELSE NVL(CEIL(order_qty/std_pkg) * std_pkg * price_wt_core,0) END) order_total, " + "SUM (CASE WHEN NVL(ssp.min_stocking_qty,0) <= 0 THEN NVL(return_qty*price_wt_core,0) ELSE 0 END) AS non_stocking_return, " + "SUM (CASE WHEN ssp.min_stocking_qty > 0 THEN NVL(return_qty*price_wt_core,0) ELSE 0 END) AS stocking_return " + "from ACO_DUP_REPORT_PIVOT_FINAL a " + "join SESSION_SITE_PRODUCT_COVERAGE ssp on a.session_id=ssp.session_id and a.site_id=ssp.site_id and a.product_id=ssp.product_id " + "join session_hierarchy sh on a.session_id=sh.session_id and ssp.level4_parent_id=sh.hierarchy_id " + "join site_parameter spa on spa.site_id = a.site_id AND sh.hierarchy_id = spa.hierarchy_id " + "left join product_hierarchy ph on sh.hierarchy_id=ph.hierarchy_id " + "where a.session_id=? " + "group by a.session_id, a.site_id, sh.hierarchy_id) " + "group by session_id, site_id)) B " + "ON (ps.session_id = ?) " + "WHEN MATCHED THEN UPDATE SET PS.ORDER_TOTAL=B.ORDER_TOTAL, PS.STOCKING_RETURN_TOTAL=B.STOCKING_RETURN, " + "PS.NON_STOCKING_RETURN_TOTAL=B.NON_STOCKING_RETURN" , sessionId , sessionId ); jdbcTemplate.update ( "delete from SESSION_SEND_DATE where SESSION_ID=? " , sessionId ); for (Integer siteId : getSitesFromSession ( sessionId )) { for (HierarchyNode node : acoDAO.getAcoHierarchiesFromSession ( sessionId )) { acoDAO.finalizeAcoSiteHierarchy ( siteId , node.getNodeId ( ) , sessionId ); } } LOGGER.info ( "finalizeACOSession| Finalize duration for sessionId " + sessionId + ": " + ( System.currentTimeMillis ( ) - start ) ); // TODO: DONT THINK THIS IS NEEDED FOR ACO: updateFollowerSiteProduct(sessionId); } private void updateFollowerSiteProduct ( int sessionId ) { jdbcTemplate.update ( "MERGE INTO FOLLOWER_SITE_PRODUCT FSP USING (" + " SELECT SITE_ID, PRODUCT_ID FROM SESSION_SITE_PRODUCT_FINALIZED WHERE SESSION_ID = ? AND SITE_ID IN (SELECT SITE_ID FROM SESSION_SITE WHERE SESSION_ID = ?) AND SYSTEM_RECOMMENDED = 'Y') SSP" + " ON (FSP.SITE_ID = SSP.SITE_ID AND FSP.PRODUCT_ID = SSP.SITE_ID)" + " WHEN NOT MATCHED THEN INSERT (SITE_ID, PRODUCT_ID, CREATED_DATE) VALUES (SSP.SITE_ID, SSP.PRODUCT_ID, SYSDATE)" , sessionId , sessionId ); } @Override public List getSessionTransmissions ( User user , Integer sessionId ) { List transmissions = jdbcTemplate.query ( "SELECT transmission_id AS transmissionId, session_id AS sessionId, " + " t.site_id AS siteId, site_name AS siteName, s.pos_type as posType, " + " tso." + ( ( "en".equals ( user.getLanguage ( ) ) || user.getLanguage ( ) == null ) ? "TRANSMISSION_STATUS_DESC" : "TRANSMISSION_STATUS_DESC_FR" ) + " AS orderStatusDesc, tso.TRANSMISSION_STATUS_ID as orderTransmisssionStatusId, tso.TRANSMISSION_STATUS_CD as orderTransmissionStatusCode, t.ORDER_ON_DATE AS orderDate, t.ORDER_STATUS_DATE AS orderLastStatusChange, t.ORDER_FILE_NAME as orderFileName, " + " tsr." + ( ( "en".equals ( user.getLanguage ( ) ) || user.getLanguage ( ) == null ) ? "TRANSMISSION_STATUS_DESC" : "TRANSMISSION_STATUS_DESC_FR" ) + " AS returnStatusDesc, tsr.TRANSMISSION_STATUS_ID as returnTransmisssionStatusId, tsr.TRANSMISSION_STATUS_CD as returnTransmissionStatusCode, t.RETURN_ON_DATE AS returnDate, t.RETURN_STATUS_DATE AS returnLastStatusChange, t.RETURN_FILE_NAME as returnFileName, " + " SITE_TYPE_CODE AS siteTypeCode, MAJOR_ACCOUNT_ID AS majorAccountId " + " FROM transmission t " + " INNER JOIN site s ON t.site_id = s.site_id AND t.session_id = ? " + " LEFT JOIN transmission_status tso ON t.order_status_id = tso.transmission_status_id " + " LEFT JOIN transmission_status tsr ON t.return_status_id = tsr.transmission_status_id " + " order by siteName" , new Object[]{sessionId} , new BeanPropertyRowMapper<> ( Transmission.class ) ); return transmissions; } @Override public List getSessionTransmissionsMinMax ( User user , Integer sessionId ) { List transmissions = jdbcTemplate.query ( "SELECT transmission_id AS transmissionId, session_id AS sessionId, " + " t.site_id AS siteId, s.site_name AS siteName, " + " tso." + ( ( "en".equals ( user.getLanguage ( ) ) || user.getLanguage ( ) == null ) ? "TRANSMISSION_STATUS_DESC" : "TRANSMISSION_STATUS_DESC_FR" ) + " AS minmaxStatusDesc, tso.TRANSMISSION_STATUS_ID as minmaxTransmisssionStatusId, tso.TRANSMISSION_STATUS_CD as minmaxTransmissionStatusCode, t.TRANSMISSION_DATE AS minmaxDate, t.I8_FILE_NAME as minmaxFileName " + " FROM TRANSMISSION_MIN_MAX t " + " INNER JOIN site s ON t.site_id = s.site_id AND t.session_id = ? " + " LEFT JOIN transmission_status tso ON t.TRANSMISSION_STATUS_ID = tso.transmission_status_id " + " order by siteName" , new Object[]{sessionId} , new BeanPropertyRowMapper<> ( Transmission.class ) ); return transmissions; } @Override public List getFailedTransmissions ( User user ) { List transmissions = jdbcTemplate.query ( "SELECT transmission_id AS transmissionId, session_id AS sessionId, t.site_id as siteId," + " CASE WHEN tso.TRANSMISSION_STATUS_CD IN ('MFR', 'MFP', 'MFL') OR tsr.TRANSMISSION_STATUS_CD IN ('MFR', 'MFP', 'MFL') THEN 'MIO' ELSE 'TAMS' END AS system, " + " CASE WHEN (tso.TRANSMISSION_STATUS_CD IN ('MFR', 'MFP', 'MFL') OR (tso.TRANSMISSION_STATUS_CD IN ('PP', 'S') and order_status_date < sysdate-1)) AND order_notification_date is null THEN 1 ELSE 0 END AS isOrder, " + " CASE WHEN (tsr.TRANSMISSION_STATUS_CD IN ('MFR', 'MFP', 'MFL') OR (tsr.TRANSMISSION_STATUS_CD IN ('PP', 'S') and return_status_date < sysdate -1)) AND return_notification_date is null THEN 1 ELSE 0 END AS isReturn, " + " SITE_SHORT_NAME as siteName, ID_NUMBER as macsId, SITE_TYPE_CODE as siteOwnership, POS_TYPE as posSystem " + " FROM transmission t " + " INNER JOIN site s ON t.site_id = s.site_id " + " LEFT JOIN transmission_status tso ON t.order_status_id = tso.transmission_status_id " + " LEFT JOIN transmission_status tsr ON t.return_status_id = tsr.transmission_status_id " + " WHERE (((tso.TRANSMISSION_STATUS_CD IN ('MFR', 'MFP', 'MFL') AND order_notification_date is null) OR (tsr.TRANSMISSION_STATUS_CD IN ('MFR', 'MFP', 'MFL') AND return_notification_date is null)) " + " OR ((tso.TRANSMISSION_STATUS_CD IN ('PP', 'S') and order_status_date < sysdate-1 and order_notification_date is null and order_on_date < sysdate-1) or (tsr.TRANSMISSION_STATUS_CD IN ('PP', 'S') and return_status_date < sysdate -1 and return_notification_date is null and return_on_date < sysdate-1))) and s.site_type_code != 'MA'" , ( rs , rowNum ) -> { FailedTransmission transmission = new FailedTransmission ( ); transmission.setTransmissionId ( rs.getInt ( "transmissionId" ) ); transmission.setSessionId ( rs.getInt ( "sessionId" ) ); transmission.setSiteId (rs.getInt ( "siteId" ) ); transmission.setOrder ( rs.getInt ( "isOrder" ) == 1 ); transmission.setReturn ( rs.getInt ( "isReturn" ) == 1 ); transmission.setSystem ( rs.getString ( "system" ) ); transmission.setSiteName ( rs.getString ( "siteName" ) ); transmission.setMacsId ( rs.getString ( "macsId" ) ); transmission.setSiteOwnership ( rs.getString ( "siteOwnership" ) ); transmission.setPosSystem ( rs.getString ( "posSystem" ) ); return transmission; } ); return transmissions; } @Override public List getExpiringSessions ( User user ) { List sessionIds = jdbcTemplate.queryForList ( "SELECT session_id from pulse_session where finalize_date is null and expiration_date > sysdate and expiration_date - sysdate < 4 and created_user_id = 913" , Integer.class ); return sessionIds; } @Override public ActionResult resendTransmission ( String orderReturn , int transmissionId ) { jdbcTemplate.update ( "CALL RESEND_TRANSMISSION (?,?)" , transmissionId , orderReturn ); LOGGER.info ( "Resend Transmission Called: Order|Return " + orderReturn + ", transmissionId " + transmissionId ); return new ActionResult ( true ); } @Override public ActionResult resendTransmissionMinmax ( int transmissionId ) { jdbcTemplate.update ( "UPDATE TRANSMISSION_MIN_MAX SET TRANSMISSION_STATUS_ID = ? where TRANSMISSION_ID = ?" , 6 , transmissionId ); LOGGER.info ( "Resend TRANSMISSION_MIN_MAX Called: " + ", transmissionId " + transmissionId ); return new ActionResult ( true ); } @Override public List> getSessionSitesWithOrderReturn ( Integer userId , Integer sessionId ) { boolean isFinalized = isSessionFinalized ( sessionId ); List> sites = jdbcTemplate.queryForList ( "select ss.site_id \"siteId\", s.site_short_name \"siteShortName\", s.site_name \"siteName\", " + " s.POS_TYPE as \"posType\", " + " max(NVL(ssp.return_qty,0)) as \"maxReturn\", " + " max(NVL(ssp.order_qty,0)) as \"maxOrder\", " + " s.VIRTUAL_SITE_FLAG as \"virtualFlag\", s.CREATED_DT as \"createdDt\", ssd.SEND_RETURN_DT as \"returnDate\", ssd.SEND_ORDER_DT as \"orderDate\" , s.SITE_TYPE_CODE as \"siteTypeCode\", MAJOR_ACCOUNT_ID as \"majorAccountId\" " + " from session_site ss " + " inner join site s on ss.site_id = s.site_id and ss.session_id = ? " + " left join SESSION_SEND_DATE ssd on ssd.session_id = ? and s.site_id=ssd.site_id " + " left join " + ( isFinalized ? " SESSION_SITE_PRODUCT_FINALIZED " : " SESSION_SITE_PRODUCT " ) + " ssp on ss.site_id = ssp.site_id and ssp.session_id = ? " + " group by ss.site_id, s.site_short_name, s.site_name, s.pos_type, s.VIRTUAL_SITE_FLAG, s.CREATED_DT, ssd.SEND_RETURN_DT, ssd.SEND_ORDER_DT,s.SITE_TYPE_CODE, s.MAJOR_ACCOUNT_ID " + " order by s.site_short_name" , sessionId , sessionId , sessionId ); return sites; } @Override public List> getMinMaxSessionSitesWithOrderReturn ( Integer userId , Integer sessionId ) { boolean isFinalized = isSessionFinalized ( sessionId ); List> sites = jdbcTemplate.queryForList ( "select ss.site_id \"siteId\", s.site_short_name \"siteShortName\", s.site_name \"siteName\", " + " s.POS_TYPE as \"posType\", " + " s.VIRTUAL_SITE_FLAG as \"virtualFlag\", s.CREATED_DT as \"createdDt\"," + " s.SITE_TYPE_CODE as \"siteTypeCode\", MAJOR_ACCOUNT_ID as \"majorAccountId\" " + " from session_site ss " + " inner join site s on ss.site_id = s.site_id and ss.session_id = ? " + " left join SESSION_SEND_DATE ssd on ssd.session_id = ? and s.site_id=ssd.site_id " + " left join " + ( isFinalized ? " SESSION_SITE_PRODUCT_FINALIZED " : " SESSION_SITE_PRODUCT " ) + " ssp on ss.site_id = ssp.site_id and ssp.session_id = ? " + " group by ss.site_id, s.site_short_name, s.site_name, s.pos_type, s.VIRTUAL_SITE_FLAG, s.CREATED_DT, s.SITE_TYPE_CODE, s.MAJOR_ACCOUNT_ID " + " order by s.site_short_name" , sessionId , sessionId , sessionId ); return sites; } @Override public List> getACOSessionSitesWithOrderReturn ( Integer userId , Integer sessionId ) { boolean isFinalized = isSessionFinalized ( sessionId ); List> sites = jdbcTemplate.queryForList ( "select ss.site_id \"siteId\", s.site_short_name \"siteShortName\", s.site_name \"siteName\", " + " s.POS_TYPE as \"posType\", " + " max(NVL(ssp.return_qty,0)) as \"maxReturn\", " + " max(NVL(ssp.order_qty,0)) as \"maxOrder\", " + " s.VIRTUAL_SITE_FLAG as \"virtualFlag\", s.CREATED_DT as \"createdDt\", ssd.SEND_RETURN_DT as \"returnDate\", ssd.SEND_ORDER_DT as \"orderDate\" , s.SITE_TYPE_CODE as \"siteTypeCode\", MAJOR_ACCOUNT_ID as \"majorAccountId\" " + " from session_site ss " + " inner join site s on ss.site_id = s.site_id and ss.session_id = ? " + " left join SESSION_SEND_DATE ssd on ssd.session_id = ? and s.site_id=ssd.site_id " + " left join " + ( isFinalized ? " SESSION_SITE_PRODUCT_FINALIZED " : " SESSION_SITE_PRODUCT_COVERAGE " ) + " ssp on ss.site_id = ssp.site_id and ssp.session_id = ? " + " group by ss.site_id, s.site_short_name, s.site_name, s.pos_type, s.VIRTUAL_SITE_FLAG, s.CREATED_DT, ssd.SEND_RETURN_DT, ssd.SEND_ORDER_DT,s.SITE_TYPE_CODE, s.MAJOR_ACCOUNT_ID " + " order by s.site_short_name" , sessionId , sessionId , sessionId ); return sites; } @Override public void updateSessionTransmissions ( Integer userId , Integer sessionId , Transmission[] transmissionArray ) { long start = System.currentTimeMillis ( ); List abortedOrders = new LinkedList<> ( ); List abortedReturns = new LinkedList<> ( ); List returns = new LinkedList<> ( ); List orders = new LinkedList<> ( ); for (Transmission t : transmissionArray) { if ( t.getOrderDate ( ) == null && t.getOrderLastStatusChange ( ) != null ) abortedOrders.add ( t ); if ( t.getReturnDate ( ) == null && t.getReturnLastStatusChange ( ) != null ) abortedReturns.add ( t ); if ( t.getOrderDate ( ) != null && t.getOrderLastStatusChange ( ) != null ) orders.add ( t ); if ( t.getReturnDate ( ) != null && t.getReturnLastStatusChange ( ) != null ) returns.add ( t ); } jdbcTemplate.batchUpdate ( "UPDATE TRANSMISSION SET ORDER_ON_DATE = ? " + " WHERE TRANSMISSION_ID = ? AND " + " (ORDER_STATUS_ID = (SELECT TRANSMISSION_STATUS_ID FROM TRANSMISSION_STATUS WHERE TRANSMISSION_STATUS_CD = 'PP') " + " AND ORDER_ON_DATE > SYSDATE " + " AND ? < ORDER_STATUS_DATE + 30)" , new BatchPreparedStatementSetter ( ) { @Override public void setValues ( PreparedStatement ps , int i ) throws SQLException { Transmission t = orders.get ( i ); ps.setTimestamp ( 1 , new java.sql.Timestamp ( t.getOrderDate ( ).getTime ( ) ) ); ps.setInt ( 2 , t.getTransmissionId ( ) ); ps.setTimestamp ( 3 , new java.sql.Timestamp ( t.getOrderDate ( ).getTime ( ) ) ); } @Override public int getBatchSize ( ) { return orders.size ( ); } } ); jdbcTemplate.batchUpdate ( "UPDATE TRANSMISSION SET RETURN_ON_DATE = ? " + " WHERE TRANSMISSION_ID = ? AND " + " (RETURN_STATUS_ID = (SELECT TRANSMISSION_STATUS_ID FROM TRANSMISSION_STATUS WHERE TRANSMISSION_STATUS_CD = 'PP') " + " AND RETURN_ON_DATE > SYSDATE " + " AND ? < RETURN_STATUS_DATE + 30)" , new BatchPreparedStatementSetter ( ) { @Override public void setValues ( PreparedStatement ps , int i ) throws SQLException { Transmission t = returns.get ( i ); ps.setTimestamp ( 1 , new java.sql.Timestamp ( t.getReturnDate ( ).getTime ( ) ) ); ps.setInt ( 2 , t.getTransmissionId ( ) ); ps.setTimestamp ( 3 , new java.sql.Timestamp ( t.getReturnDate ( ).getTime ( ) ) ); } @Override public int getBatchSize ( ) { return returns.size ( ); } } ); jdbcTemplate.batchUpdate ( "UPDATE TRANSMISSION SET ORDER_ON_DATE = null, " + " ORDER_STATUS_ID = (SELECT TRANSMISSION_STATUS_ID FROM TRANSMISSION_STATUS WHERE TRANSMISSION_STATUS_CD = 'PA'), " + " ORDER_STATUS_DATE = sysdate " + " WHERE TRANSMISSION_ID = ? AND " + " (ORDER_STATUS_ID = (SELECT TRANSMISSION_STATUS_ID FROM TRANSMISSION_STATUS WHERE TRANSMISSION_STATUS_CD = 'PP') " + " AND ORDER_ON_DATE > SYSDATE) " , new BatchPreparedStatementSetter ( ) { @Override public void setValues ( PreparedStatement ps , int i ) throws SQLException { Transmission t = abortedOrders.get ( i ); ps.setInt ( 1 , t.getTransmissionId ( ) ); } @Override public int getBatchSize ( ) { return abortedOrders.size ( ); } } ); jdbcTemplate.batchUpdate ( "UPDATE TRANSMISSION SET RETURN_ON_DATE = null, " + " RETURN_STATUS_ID = (SELECT TRANSMISSION_STATUS_ID FROM TRANSMISSION_STATUS WHERE TRANSMISSION_STATUS_CD = 'PA'), " + " RETURN_STATUS_DATE = sysdate " + " WHERE TRANSMISSION_ID = ? AND " + " (RETURN_STATUS_ID = (SELECT TRANSMISSION_STATUS_ID FROM TRANSMISSION_STATUS WHERE TRANSMISSION_STATUS_CD = 'PP') " + " AND RETURN_ON_DATE > SYSDATE) " , new BatchPreparedStatementSetter ( ) { @Override public void setValues ( PreparedStatement ps , int i ) throws SQLException { Transmission t = abortedReturns.get ( i ); ps.setInt ( 1 , t.getTransmissionId ( ) ); } @Override public int getBatchSize ( ) { return abortedReturns.size ( ); } } ); LOGGER.info ( "updateSessionTransmissions| Duration for sessionId " + sessionId + ": " + ( System.currentTimeMillis ( ) - start ) ); } @Override public List> getSessionSites ( int sessionId ) { LOGGER.debug ( "sessionId in DAO impl :::: " + sessionId ); String sql = "SELECT S.SITE_ID AS \"siteId\", S.SITE_NAME AS \"siteName\", S.SITE_TYPE_CODE AS \"siteType\", ICT.MA_INV_COLL_TEMPLATE_ID \"invCollTemplateId\", " + "MA_INV_COLL_TEMPLATE_NAME \"invCollTemplateName\", SITE_GROUP_NAME \"siteGroupName\", LATITUDE as \"latitude\", LONGITUDE as \"longitude\" " + "FROM SESSION_SITE SS " + "JOIN SITE S ON S.SITE_ID = SS.SITE_ID " + "LEFT JOIN MAJOR_ACCOUNT MA ON S.MAJOR_ACCOUNT_ID = MA.MAJOR_ACCOUNT_ID " + "LEFT JOIN MA_INV_COLL_TEMPLATE ICT ON ICT.MA_INV_COLL_TEMPLATE_ID = NVL(S.MA_INV_COLL_TEMPLATE_ID, MA.DEFAULT_INV_COLL_TEMPLATE_ID) " + "WHERE SS.SESSION_ID = ? order by S.SITE_NAME"; List> sessionSitesList = jdbcTemplate.queryForList ( sql , sessionId ); return sessionSitesList; } @Override public Map getHierarchyTreeForSession ( int sessionId , int siteId , User user ) { boolean isFinalized = isSessionFinalized ( sessionId ); List sessionHierarchy = jdbcTemplate.query ( "SELECT " + " ph.HIERARCHY_ID, ph." + ( ( "en".equals ( user.getLanguage ( ) ) || user.getLanguage ( ) == null ) ? "HIERARCHY_LONG_NAME" : "HIERARCHY_LONG_NAME_FR" ) + ", ph.PARENT_ID, " + " ph.HIERARCHY_LEVEL, ssp.HIERARCHY_ISENABLED " + " FROM " + ( isFinalized ? " session_site_para_finalized " : " SESSION_SITE_PARAMETER " ) + " ssp, PRODUCT_HIERARCHY PH, SESSION_HIERARCHY SH " + " WHERE SSP.SESSION_ID = ? and SSP.site_id = ? and PH.HIERARCHY_ID = ssp.HIERARCHY_ID " + " AND SH.SESSION_ID = SSP.SESSION_ID AND SH.HIERARCHY_ID = SSP.HIERARCHY_ID " + " ORDER BY PH.HIERARCHY_LEVEL, ph." + ( ( "en".equals ( user.getLanguage ( ) ) || user.getLanguage ( ) == null ) ? "HIERARCHY_LONG_NAME" : "HIERARCHY_LONG_NAME_FR" ) , new Object[]{sessionId , siteId} , new HierarchyItemMapper ( user ) ); Map hierarchyTree = new LinkedHashMap<> ( ); HierarchyItem root = new HierarchyItem ( HierarchyUtils.HIERARCHY_ROOT_ID , "Root" , null , 1 ); hierarchyTree.put ( HierarchyUtils.HIERARCHY_ROOT_ID , root ); for (HierarchyItem hi : sessionHierarchy) { hierarchyTree.put ( hi.getHierarchyId ( ) , hi ); if ( hi.getParentId ( ) == null ) { hierarchyTree.get ( HierarchyUtils.HIERARCHY_ROOT_ID ).getChildren ( ).add ( hi ); } else { HierarchyItem parent = hierarchyTree.get ( hi.getParentId ( ) ); if ( parent != null ) { parent.getChildren ( ).add ( hi ); } else { LOGGER.error ( "getHierarchyTreeForSession| Could not find parent_id " + hi.getParentId ( ) + " for HI " + hi.getHierarchyId ( ) ); } } } return hierarchyTree; } @Override public Map getSessionTypesAndCount ( User user , String userAccessSites ) { String sql = "SELECT STS.STATUS_CD AS TYPE, " + "COUNT(DISTINCT(TBL.SESSION_ID)) AS COUNT " + " FROM PULSE_SESSION TBL INNER JOIN SESSION_STATUS STS ON STS.STATUS_ID = TBL.STATUS_ID AND " + " (STS.STATUS_CD IN ('O', 'A') AND TBL.CREATED_DATE > (SYSDATE - 120)) " + " INNER JOIN SESSION_SITE SS ON TBL.SESSION_ID = SS.SESSION_ID "; if ( user.getRoleId ( ) != ROLE.ADMIN.getNumVal ( ) && user.getRoleId ( ) != ROLE.XO.getNumVal ( ) ) { if ( userAccessSites != null && userAccessSites.length ( ) > 0 ) { sql += "AND (SS.SITE_ID,0) IN (" + userAccessSites + " ) "; } else { return null; } } sql += "GROUP BY STS.STATUS_CD ORDER BY TYPE DESC"; List> sessionTypesAndCount = jdbcTemplate.queryForList ( sql ); Map totals = new LinkedHashMap<> ( ); for (Map s : sessionTypesAndCount) { totals.put ( (String) s.get ( "TYPE" ) , ( (BigDecimal) s.get ( "COUNT" ) ).intValue ( ) ); } return totals; } @Override public List> getCreatedUserList ( ) { List> sessionCreatedUsers = jdbcTemplate.queryForList ( "SELECT " + "DISTINCT(MU.USER_ID) AS \"userId\", " + "TRIM(MU.LAST_NAME || ',' || MU.FIRST_NAME) AS \"userName\" " + "FROM " + "PULSE_SESSION ST " + "INNER JOIN PULSE_USER MU ON ST.CREATED_USER_ID = MU.USER_ID " + " ORDER BY TRIM(MU.LAST_NAME || ',' || MU.FIRST_NAME)" ); return sessionCreatedUsers; } @Override public List> getSessionStatusList ( User user ) { List> sessionStatusList = jdbcTemplate .queryForList ( "SELECT " + "DISTINCT(ST.STATUS_ID) AS \"statusId\", " + "ST." + ( ( "en".equals ( user.getLanguage ( ) ) || user.getLanguage ( ) == null ) ? "STATUS_DESCRIPTION" : "STATUS_DESCRIPTION_FR" ) + " AS \"statusDesc\" " + "FROM " + "SESSION_STATUS ST, " + "PULSE_SESSION STBL " + "WHERE " + "ST.STATUS_ID = STBL.STATUS_ID " + "ORDER BY ST." + ( ( "en".equals ( user.getLanguage ( ) ) || user.getLanguage ( ) == null ) ? "STATUS_DESCRIPTION" : "STATUS_DESCRIPTION_FR" ) + "" ); return sessionStatusList; } @Override public List> getSessionSiteList ( User user , String userAccessSites ) { if ( user.getRoleId ( ) == ROLE.ADMIN.getNumVal ( ) || user.getRoleId ( ) == ROLE.XO.getNumVal ( ) ) { return jdbcTemplate.queryForList ( "SELECT " + "DISTINCT(SITE.SITE_ID) AS \"siteId\", " + "SITE.SITE_NAME AS \"siteName\" " + "FROM " + "SITE, " + "SESSION_SITE SS " + "WHERE " + "SITE.SITE_ID = SS.SITE_ID " + "ORDER BY LOWER(SITE.SITE_NAME) ASC" ); } else { if ( userAccessSites.length ( ) > 0 ) { return jdbcTemplate .queryForList ( "SELECT DISTINCT(SITE.SITE_ID) AS \"siteId\", SITE.SITE_NAME AS \"siteName\" " + "FROM SITE SITE, SESSION_SITE SS WHERE SITE.SITE_ID = SS.SITE_ID " + "AND (SS.SITE_ID,0) IN (" + userAccessSites + " ) ORDER BY LOWER(SITE.SITE_NAME) ASC" ); } else { return null; } } } @Override public List> getSessionSiteGroupList ( Integer userId ) { String sql = "SELECT SG.SITE_GROUP_ID as \"siteGroupId\", SG.SITE_GROUP_NAME as \"name\" " + "FROM SITE_GROUP SG WHERE SG.GROUP_OWNER_USER_ID IS NULL OR SG.CREATED_USER_ID = ? " + "ORDER BY LOWER(SG.SITE_GROUP_NAME) ASC"; List> sessionSiteGroupList = jdbcTemplate.queryForList ( sql , userId ); return sessionSiteGroupList; } /* * (non-Javadoc) * * @see * com.napa.pulse.dao.interfaces.ReviewDAO#saveOrderReturn(com.napa.pulse.entity * .security.User, java.lang.Integer, java.lang.Integer, String, * java.lang.Integer, java.lang.Integer,String) */ @Override public ActionResultItem>> saveMinMax ( User user , Integer sessionId , Integer siteId , String partNumber , Integer minQty , Integer maxQty , String fieldAbbr ) throws PulseException { ActionResultItem>> actionItem = new ActionResultItem<> ( ); List> product = jdbcTemplate .queryForList ( "SELECT PRODUCT_ID FROM SESSION_SITE_PRODUCT WHERE SESSION_ID = ? AND SITE_ID = ? \n" + "AND PRODUCT_ID IN (select product_id from product where part_number = ? and field_abbr = ?) AND CLASS <> 'O' " , sessionId , siteId , partNumber , fieldAbbr ); Map map = product.get ( 0 ); int pId = Integer.parseInt ( map.get ( "PRODUCT_ID" ).toString ( ) ); //boolean returnCode = siteQueueMap.canStart(siteId, "saveMinMax"); boolean returnCode = true; if ( ! returnCode ) { throw new PulseException ( PulseConstants.OPERATION_TIMEOUT ); } else { try { String sql = "UPDATE SESSION_SITE_PRODUCT SET MIN_STOCKING_QTY = ?" + ", MAX_STOCKING_QTY = ?" + " WHERE SESSION_ID = ?" + " AND SITE_ID = ?" + " AND PRODUCT_ID = ?"; if ( product.size ( ) > 0 ) { jdbcTemplate.update ( sql , minQty , maxQty , sessionId , siteId , pId ); actionItem.setSuccess ( true ); } else { actionItem.setMessage ( "Row not allowed to be edited" ); } } finally { // siteQueueMap.removeHead(siteId, "saveOrderReturn"); } } return actionItem; } @Override public void getSessionDashboardData ( Integer sessionId , Integer pageNumber , Integer numberOfRecordsPerPage , String sortBy , String sortingOrder , Boolean isReturns , Boolean isOrders , Boolean isInSession , Integer siteId , Integer level , Integer hierarchyId , String exportType , Boolean isUnReviewed , Boolean aggressiveLeft , Boolean aggressiveRight , Boolean dnsCount , Boolean sjCount , Boolean highSkuEdit , Integer highSkuEditHierarchyId , Boolean isDeclineOrder , Boolean isDeclineReturn , Integer hierarchyLevel , HttpServletResponse response , User user ) throws Exception { long start = System.currentTimeMillis ( ); LOGGER.info ( "getSessionDashboardData - sessionId: " + sessionId + " siteId: " + siteId ); /* Null checks and setting default values */ if ( exportType == null ) { exportType = PulseConstants.BLANK; } Map ptsRecommendations = commonService.getPTSRecommendations ( ); Map data = new HashMap ( ) { private static final long serialVersionUID = 1L; { put ( "ignoreStock" , "NVL(sp.IGNORE_STOCK,0)" ); put ( "minGreaterThan0" , "NVL(ssp.MIN_GREATER_ZERO,0)" ); put ( "cogs" , "ssp.COGS" ); put ( "dcCogs" , "ssp.DC_COGS" ); put ( "criticality" , "NVL(ssp.CRITICALITY,0)" ); put ( "netTotal" , "NVL(ssp.NET_TOTAL,0)" ); put ( "turnover" , "NVL(sp.TURNOVER,0)" ); put ( "nonStockingReturn" , "NVL(ssp.NON_STOCKING_RETURN,0)" ); put ( "stockingReturn" , "NVL(ssp.STOCKING_RETURN,0)" ); put ( "returnTotal" , "NVL(ssp.RETURN_TOTAL,0)" ); put ( "returnCount" , "NVL(ssp.RETURN_COUNT,0)" ); put ( "orderTotal" , "NVL(ssp.ORDER_TOTAL,0)" ); put ( "orderCount" , "NVL(ssp.ORDER_COUNT,0)" ); put ( "netCount" , "NVL(ssp.NET_COUNT,0)" ); put ( "hsnBefore" , "NVL(ssp.HS_NETWORK_UIC_BEFORE,0)" ); put ( "hsnAfter" , "NVL(ssp.HS_NETWORK_UIC_AFTER,0)" ); put ( "site" , "s.SITE_SHORT_NAME" ); put ( "hierarchy" , "ph." + ( ( "en".equals ( user.getLanguage ( ) ) || user.getLanguage ( ) == null ) ? "HIERARCHY_LONG_NAME" : "HIERARCHY_LONG_NAME_FR" ) + "" ); put ( "reco" , "reco" ); } }; // new style hierarchical querying using Recursive Subquery Factoring String sql = "with ph(HIERARCHY_ID, " + ( ( "en".equals ( user.getLanguage ( ) ) || user.getLanguage ( ) == null ) ? "HIERARCHY_LONG_NAME" : "HIERARCHY_LONG_NAME_FR" ) + ", PARENT_ID, HIERARCHY_LEVEL) as (" + "select HIERARCHY_ID, " + ( ( "en".equals ( user.getLanguage ( ) ) || user.getLanguage ( ) == null ) ? "HIERARCHY_LONG_NAME" : "HIERARCHY_LONG_NAME_FR" ) + ", PARENT_ID, HIERARCHY_LEVEL " + "from PRODUCT_HIERARCHY where "; // Filter setting to return a hierarchy subset, else return everything if ( level != null && level < 4 && hierarchyId != null && hierarchyLevel == null ) { sql += "PARENT_ID = :hierarchyId "; } else if ( level != null && level == 4 && hierarchyId != null && hierarchyLevel == null ) { sql += "HIERARCHY_ID = :hierarchyId "; } else { sql += "PARENT_ID is null "; } // continuation of hierarchical querying using Recursive Subquery // Factoring sql += "union all " + "select ph2.HIERARCHY_ID, ph2." + ( ( "en".equals ( user.getLanguage ( ) ) || user.getLanguage ( ) == null ) ? "HIERARCHY_LONG_NAME" : "HIERARCHY_LONG_NAME_FR" ) + ", " + "ph2.PARENT_ID, ph2.HIERARCHY_LEVEL " + "from PRODUCT_HIERARCHY ph2, ph where ph2.PARENT_ID=ph.HIERARCHY_ID) " + "search depth first by " + ( ( "en".equals ( user.getLanguage ( ) ) || user.getLanguage ( ) == null ) ? "HIERARCHY_LONG_NAME" : "HIERARCHY_LONG_NAME_FR" ) + " set hierarchyName "; // Main session dashboard query if ( exportType.length ( ) == 0 ) { sql += "SELECT SITE_ID as \"siteId\", SITE_SHORT_NAME as \"site\", HIERARCHY_ID as \"hierarchyId\", REVIEWED as \"isReviewed\", HIERARCHY as \"hierarchy\", HIERARCHY_LEVEL as \"hierarchyLevel\", RECO as \"reco\", " + "IGNORE_STOCK as \"ignoreStock\", MIN_GREATER_ZERO as \"minGreaterThan0\", cogs as \"cogs\", DC_COGS as \"dcCogs\", CRITICALITY as \"criticality\", FORECAST_CRITICALITY as \"forecastCriticality\", ORDER_TOTAL as \"orderTotal\"," + " RETURN_TOTAL as \"returnTotal\", NET_TOTAL as \"netTotal\", turnover as \"turnover\", NON_STOCKING_RETURN as \"nonStockingReturn\", " + "STOCKING_RETURN as \"stockingReturn\", RETURN_COUNT as \"returnCount\", ORDER_COUNT as \"orderCount\", NET_COUNT as \"netCount\", HIERARCHY_ISENABLED, " + "DECLINE_ORDER as \"declineOrder\", DECLINE_RETURN as \"declineReturn\", HS_NETWORK_UIC_BEFORE as \"hsnBefore\",HS_NETWORK_UIC_AFTER as \"hsnAfter\", RN FROM ( "; } sql += "select /*+ FIRST_ROWS (100) */ ssp.SITE_ID, s.SITE_SHORT_NAME, ph.HIERARCHY_ID, " + "NVL(sp.IS_REVIEWED,0) as REVIEWED, " + "((case ph.HIERARCHY_LEVEL " + "when 2 then '> ' " + "when 3 then '>> ' " + "when 4 then '>>> ' end) || " + "ph." + ( ( "en".equals ( user.getLanguage ( ) ) || user.getLanguage ( ) == null ) ? "HIERARCHY_LONG_NAME" : "HIERARCHY_LONG_NAME_FR" ) + ") as HIERARCHY, ph.HIERARCHY_LEVEL, " + "( case when (sh.INCLUDE_RETURN is null and sh.INCLUDE_ORDER is null and sp.STOCK_IT = 'N') or (sdp.STOCK_IT = 'N') then 'not_stocking' " + " when (sh.INCLUDE_RETURN is null and sh.INCLUDE_ORDER is null and 0.5 + sp.OFFSET <= 0.3 and sp.IS_LINKED = 'Y' ) " + " or (0.5 + sdp.OFFSET <= 0.3 and sdp.IS_LINKED = 'Y' ) then 'red_right_linked' " + " when (sh.INCLUDE_RETURN is null and sh.INCLUDE_ORDER is null and 0.5 + sp.OFFSET <= 0.3 and sp.IS_LINKED = 'N' ) " + " or (0.5 + sdp.OFFSET <= 0.3 and sdp.IS_LINKED = 'N') then 'red_right_unlinked' " + " when (sh.INCLUDE_RETURN is null and sh.INCLUDE_ORDER is null and 0.5 + sp.OFFSET > 0.3 and 0.5 + sp.OFFSET <= 0.4 and sp.IS_LINKED = 'Y' ) " + " or (0.5 + sdp.OFFSET > 0.3 and 0.5 + sdp.OFFSET <= 0.4 and sdp.IS_LINKED = 'Y' ) then 'yellow_right_linked' " + " when (sh.INCLUDE_RETURN is null and sh.INCLUDE_ORDER is null and 0.5 + sp.OFFSET > 0.3 and 0.5 + sp.OFFSET <= 0.4 and sp.IS_LINKED = 'N' ) " + " or (0.5 + sdp.OFFSET > 0.3 and 0.5+ sdp.OFFSET <= 0.4 and sdp.IS_LINKED = 'N') then 'yellow_right_unlinked' " + " when (sh.INCLUDE_RETURN is null and sh.INCLUDE_ORDER is null and 0.5 + sp.OFFSET > 0.4 and sp.OFFSET < 0 and sp.IS_LINKED = 'Y' ) " + " or (0.5 + sdp.OFFSET > 0.4 and sdp.OFFSET < 0 and sdp.IS_LINKED = 'Y' ) then 'green_right_linked' " + " when (sh.INCLUDE_RETURN is null and sh.INCLUDE_ORDER is null and 0.5 + sp.OFFSET > 0.4 and sp.OFFSET < 0 and sp.IS_LINKED = 'N' ) " + " or (0.5 + sdp.OFFSET > 0.4 and sdp.OFFSET < 0 and sdp.IS_LINKED = 'N') then 'green_right_unlinked' " + " when (sh.INCLUDE_RETURN is null and sh.INCLUDE_ORDER is null and sp.OFFSET = 0 and sp.IS_LINKED = 'Y' ) " + " or (sdp.OFFSET = 0 and sdp.IS_LINKED = 'Y' ) then 'green_linked' " + " when (sh.INCLUDE_RETURN is null and sh.INCLUDE_ORDER is null and sp.OFFSET = 0 and sp.IS_LINKED = 'N' ) " + " or (sdp.OFFSET = 0 and sdp.IS_LINKED = 'N' ) then 'green_unlinked' " + " when (sh.INCLUDE_RETURN is null and sh.INCLUDE_ORDER is null and sp.OFFSET > 0 and 0.5 + sp.OFFSET < 0.6 and sp.IS_LINKED = 'Y' ) " + " or (sdp.OFFSET > 0 and 0.5 + sdp.OFFSET < 0.6 and sdp.IS_LINKED = 'Y' ) then 'green_left_linked' " + " when (sh.INCLUDE_RETURN is null and sh.INCLUDE_ORDER is null and sp.OFFSET > 0 and 0.5 + sp.OFFSET < 0.6 and sp.IS_LINKED = 'N' ) " + " or (sdp.OFFSET > 0 and 0.5 + sdp.OFFSET < 0.6 and sdp.IS_LINKED = 'N') then 'green_left_unlinked' " + " when (sh.INCLUDE_RETURN is null and sh.INCLUDE_ORDER is null and 0.5 + sp.OFFSET >= 0.6 and 0.5 + sp.OFFSET < 0.7 and sp.IS_LINKED = 'Y' ) " + " or (0.5 + sdp.OFFSET >= 0.6 and 0.5 + sdp.OFFSET < 0.7 and sdp.IS_LINKED = 'Y' ) then 'yellow_left_linked' " + " when (sh.INCLUDE_RETURN is null and sh.INCLUDE_ORDER is null and 0.5 + sp.OFFSET >= 0.6 and 0.5 + sp.OFFSET < 0.7 and sp.IS_LINKED = 'N' ) " + " or (0.5 + sdp.OFFSET >= 0.6 and 0.5 + sdp.OFFSET < 0.7 and sdp.IS_LINKED = 'N') then 'yellow_left_unlinked' " + " when (sh.INCLUDE_RETURN is null and sh.INCLUDE_ORDER is null and 0.5 + sp.OFFSET < 1 and 0.5 + sp.OFFSET >= 0.7 and sp.IS_LINKED = 'Y' ) " + " or (0.5 + sdp.OFFSET < 1 and 0.5 + sdp.OFFSET >= 0.7 and sdp.IS_LINKED = 'Y' ) then 'red_left_linked' " + " when (sh.INCLUDE_RETURN is null and sh.INCLUDE_ORDER is null and 0.5 + sp.OFFSET < 1 and 0.5 + sp.OFFSET >= 0.7 and sp.IS_LINKED = 'N' ) " + " or (0.5 + sdp.OFFSET < 1 and 0.5 + sdp.OFFSET >= 0.7 and sdp.IS_LINKED = 'N') then 'red_left_unlinked' " + " when (sh.INCLUDE_RETURN is null and sh.INCLUDE_ORDER is null and 0.5 + sp.OFFSET = 1 and sp.IS_LINKED = 'Y' ) " + " or (0.5 + sdp.OFFSET = 1 and sdp.IS_LINKED = 'Y') then 'blue_linked' " + " when (sh.INCLUDE_RETURN is null and sh.INCLUDE_ORDER is null and 0.5 + sp.OFFSET = 1 and sp.IS_LINKED = 'N' ) " + " or (0.5 + sdp.OFFSET = 1 and sdp.IS_LINKED = 'N') then 'blue_unlinked' end )as RECO ," + "sp.IGNORE_STOCK, ssp.MIN_GREATER_ZERO, ssp.COGS, ssp.DC_COGS, round(NVL(ssp.CRITICALITY,0),3) as CRITICALITY, round(NVL(ssp.FORECAST_CRITICALITY,0),3) as FORECAST_CRITICALITY, " + "ssp.ORDER_TOTAL, ssp.RETURN_TOTAL, ssp.NET_TOTAL, sp.TURNOVER, " + "ssp.NON_STOCKING_RETURN, ssp.STOCKING_RETURN, ssp.RETURN_COUNT, " + "ssp.ORDER_COUNT, ssp.NET_COUNT, ssp.hierarchy_isenabled, ssp.DECLINE_ORDER, ssp.DECLINE_RETURN, ssp.HS_NETWORK_UIC_BEFORE, ssp.HS_NETWORK_UIC_AFTER,"; // sort by hierarchy else sort by a different column if ( "hierarchy".equals ( sortBy ) && hierarchyLevel == null ) { sql += "ROW_NUMBER() OVER (order by hierarchyName " + sortingOrder + ", s.SITE_SHORT_NAME " + sortingOrder + ") RN"; } else if ( "hierarchy".equals ( sortBy ) && hierarchyLevel != null ) { sql += "ROW_NUMBER() OVER (order by HIERARCHY_NAME " + sortingOrder + ", hierarchy_long_name " + sortingOrder + ", s.SITE_SHORT_NAME " + sortingOrder + ") RN"; } else if ( "site".equals ( sortBy ) && hierarchyLevel != null ) { sql += "ROW_NUMBER() OVER (order by s.SITE_SHORT_NAME " + sortingOrder + ", hierarchy_long_name asc) RN"; } else if ( "site".equals ( sortBy ) && hierarchyLevel == null ) { sql += "ROW_NUMBER() OVER (order by s.SITE_SHORT_NAME " + sortingOrder + ", hierarchyName asc) RN"; } else if ( "reco".equals ( sortBy ) && hierarchyLevel == null ) { if ( sortingOrder.equals ( "desc" ) ) { sql += "ROW_NUMBER() OVER ( order by " + "case when (sh.INCLUDE_RETURN is null and sh.INCLUDE_ORDER is null and sp.STOCK_IT = 'N') " + " or (sdp.STOCK_IT = 'N') then 10 " + "when (sh.INCLUDE_RETURN is null and sh.INCLUDE_ORDER is null and (0.5 + sp.OFFSET) = 1) " + " or ((0.5 + sdp.OFFSET) = 1) then 9 " + "when (sh.INCLUDE_RETURN is null and sh.INCLUDE_ORDER is null and (0.5 + sp.OFFSET) < 1 and (0.5 + sp.OFFSET) >= 0.8) " + " or ((0.5 + sdp.OFFSET) < 1 and (0.5 + sdp.OFFSET) >= 0.8 ) then 8 " + "when (sh.INCLUDE_RETURN is null and sh.INCLUDE_ORDER is null and (0.5 + sp.OFFSET) >= 0.7) " + " or ((0.5 + sdp.OFFSET) >= 0.7) then 7 " + "when (sh.INCLUDE_RETURN is null and sh.INCLUDE_ORDER is null and (0.5 + sp.OFFSET) < 0.7 and (0.5 + sp.OFFSET) >= 0.6) " + " or ((0.5 + sdp.OFFSET) < 0.7 and (0.5 + sdp.OFFSET) >= 0.6 ) then 6 " + "when (sh.INCLUDE_RETURN is null and sh.INCLUDE_ORDER is null and (0.5 + sp.OFFSET) < 0.6 and sp.OFFSET > 0) " + " or ((0.5 + sdp.OFFSET) < 0.6 and sdp.OFFSET > 0) then 5 " + "when (sh.INCLUDE_RETURN is null and sh.INCLUDE_ORDER is null and sp.OFFSET = 0) " + " or (sdp.OFFSET = 0) then 4 " + "when (sh.INCLUDE_RETURN is null and sh.INCLUDE_ORDER is null and sp.OFFSET < 0 and (0.5 + sp.OFFSET) > 0.4) " + " or (sdp.OFFSET < 0 and (0.5 + sdp.OFFSET) > 0.4 ) then 3 " + "when (sh.INCLUDE_RETURN is null and sh.INCLUDE_ORDER is null and (0.5 + sp.OFFSET) <= 0.4 and (0.5 + sp.OFFSET) > 0.3) " + " or ((0.5 + sdp.OFFSET) <= 0.4 and (0.5 + sdp.OFFSET) > 0.3 ) then 2 " + "when (sh.INCLUDE_RETURN is null and sh.INCLUDE_ORDER is null and (0.5 + sp.OFFSET) <= 0.3) " + " or ((0.5 + sdp.OFFSET) <= 0.3) then 1 end, hierarchyName asc ) RN "; } else { sql += "ROW_NUMBER() OVER ( order by " + "case when (sh.INCLUDE_RETURN is null and sh.INCLUDE_ORDER is null and sp.STOCK_IT = 'N') " + " or (sdp.STOCK_IT = 'N') then 1 " + "when (sh.INCLUDE_RETURN is null and sh.INCLUDE_ORDER is null and (0.5 + sp.OFFSET) = 1) " + " or ((0.5 + sdp.OFFSET) = 1) then 2 " + "when (sh.INCLUDE_RETURN is null and sh.INCLUDE_ORDER is null and (0.5 + sp.OFFSET) < 1 and (0.5 + sp.OFFSET) >= 0.8) " + " or ((0.5 + sdp.OFFSET) < 1 and (0.5 + sdp.OFFSET) >= 0.8 ) then 3 " + "when (sh.INCLUDE_RETURN is null and sh.INCLUDE_ORDER is null and (0.5 + sp.OFFSET) >= 0.7) " + " or ((0.5 + sdp.OFFSET) >= 0.7) then 4 " + "when (sh.INCLUDE_RETURN is null and sh.INCLUDE_ORDER is null and (0.5 + sp.OFFSET) < 0.7 and (0.5 + sp.OFFSET) >= 0.6) " + " or ((0.5 + sdp.OFFSET) < 0.7 and (0.5 + sdp.OFFSET) >= 0.6 ) then 5 " + "when (sh.INCLUDE_RETURN is null and sh.INCLUDE_ORDER is null and (0.5 + sp.OFFSET) < 0.6 and sp.OFFSET > 0) " + " or ((0.5 + sdp.OFFSET) < 0.6 and sdp.OFFSET > 0) then 6 " + "when (sh.INCLUDE_RETURN is null and sh.INCLUDE_ORDER is null and sp.OFFSET = 0) " + " or (sdp.OFFSET = 0) then 7 " + "when (sh.INCLUDE_RETURN is null and sh.INCLUDE_ORDER is null and sp.OFFSET < 0 and (0.5 + sp.OFFSET) > 0.4) " + " or (sdp.OFFSET < 0 and (0.5 + sdp.OFFSET) > 0.4 ) then 8 " + "when (sh.INCLUDE_RETURN is null and sh.INCLUDE_ORDER is null and (0.5 + sp.OFFSET) <= 0.4 and (0.5 + sp.OFFSET) > 0.3 ) " + " or ((0.5 + sdp.OFFSET) <= 0.4 and (0.5 + sdp.OFFSET) > 0.3 ) then 9 " + "when (sh.INCLUDE_RETURN is null and sh.INCLUDE_ORDER is null and (0.5 + sp.OFFSET) <= 0.3) " + " or ((0.5 + sdp.OFFSET) <= 0.3) then 10 end, hierarchyName asc ) RN"; } } else if ( "reco".equals ( sortBy ) && hierarchyLevel != null ) { if ( sortingOrder.equals ( "desc" ) ) { sql += "ROW_NUMBER() OVER ( order by " + "case when (sh.INCLUDE_RETURN is null and sh.INCLUDE_ORDER is null and sp.STOCK_IT = 'N') " + " or (sdp.STOCK_IT = 'N') then 10 " + "when (sh.INCLUDE_RETURN is null and sh.INCLUDE_ORDER is null and (0.5 + sp.OFFSET) = 1) " + " or ((0.5 + sdp.OFFSET) = 1) then 9 " + "when (sh.INCLUDE_RETURN is null and sh.INCLUDE_ORDER is null and (0.5 + sp.OFFSET) < 1 and (0.5 + sp.OFFSET) >= 0.8) " + " or ((0.5 + sdp.OFFSET) < 1 and (0.5 + sdp.OFFSET) >= 0.8 ) then 8 " + "when (sh.INCLUDE_RETURN is null and sh.INCLUDE_ORDER is null and (0.5 + sp.OFFSET) >= 0.7) " + " or ((0.5 + sdp.OFFSET) >= 0.7) then 7 " + "when (sh.INCLUDE_RETURN is null and sh.INCLUDE_ORDER is null and (0.5 + sp.OFFSET) < 0.7 and (0.5 + sp.OFFSET) >= 0.6) " + " or ((0.5 + sdp.OFFSET) < 0.7 and (0.5 + sdp.OFFSET) >= 0.6 ) then 6 " + "when (sh.INCLUDE_RETURN is null and sh.INCLUDE_ORDER is null and (0.5 + sp.OFFSET) < 0.6 and sp.OFFSET > 0) " + " or ((0.5 + sdp.OFFSET) < 0.6 and sdp.OFFSET > 0) then 5 " + "when (sh.INCLUDE_RETURN is null and sh.INCLUDE_ORDER is null and sp.OFFSET = 0) " + " or (sdp.OFFSET = 0) then 4 " + "when (sh.INCLUDE_RETURN is null and sh.INCLUDE_ORDER is null and sp.OFFSET < 0 and (0.5 + sp.OFFSET) > 0.4) " + " or (sdp.OFFSET < 0 and (0.5 + sdp.OFFSET) > 0.4 ) then 3 " + "when (sh.INCLUDE_RETURN is null and sh.INCLUDE_ORDER is null and (0.5 + sp.OFFSET) <= 0.4 and (0.5 + sp.OFFSET) > 0.3) " + " or ((0.5 + sdp.OFFSET) <= 0.4 and (0.5 + sdp.OFFSET) > 0.3 ) then 2 " + "when (sh.INCLUDE_RETURN is null and sh.INCLUDE_ORDER is null and (0.5 + sp.OFFSET) <= 0.3) " + " or ((0.5 + sdp.OFFSET) <= 0.3) then 1 end, hierarchy_long_name asc ) RN "; } else { sql += "ROW_NUMBER() OVER ( order by " + "case when (sh.INCLUDE_RETURN is null and sh.INCLUDE_ORDER is null and sp.STOCK_IT = 'N') " + " or (sdp.STOCK_IT = 'N') then 1 " + "when (sh.INCLUDE_RETURN is null and sh.INCLUDE_ORDER is null and (0.5 + sp.OFFSET) = 1) " + " or ((0.5 + sdp.OFFSET) = 1) then 2 " + "when (sh.INCLUDE_RETURN is null and sh.INCLUDE_ORDER is null and (0.5 + sp.OFFSET) < 1 and (0.5 + sp.OFFSET) >= 0.8) " + " or ((0.5 + sdp.OFFSET) < 1 and (0.5 + sdp.OFFSET) >= 0.8 ) then 3 " + "when (sh.INCLUDE_RETURN is null and sh.INCLUDE_ORDER is null and (0.5 + sp.OFFSET) >= 0.7) " + " or ((0.5 + sdp.OFFSET) >= 0.7) then 4 " + "when (sh.INCLUDE_RETURN is null and sh.INCLUDE_ORDER is null and (0.5 + sp.OFFSET) < 0.7 and (0.5 + sp.OFFSET) >= 0.6) " + " or ((0.5 + sdp.OFFSET) < 0.7 and (0.5 + sdp.OFFSET) >= 0.6 ) then 5 " + "when (sh.INCLUDE_RETURN is null and sh.INCLUDE_ORDER is null and (0.5 + sp.OFFSET) < 0.6 and sp.OFFSET > 0) " + " or ((0.5 + sdp.OFFSET) < 0.6 and sdp.OFFSET > 0) then 6 " + "when (sh.INCLUDE_RETURN is null and sh.INCLUDE_ORDER is null and sp.OFFSET = 0) " + " or (sdp.OFFSET = 0) then 7 " + "when (sh.INCLUDE_RETURN is null and sh.INCLUDE_ORDER is null and sp.OFFSET < 0 and (0.5 + sp.OFFSET) > 0.4) " + " or (sdp.OFFSET < 0 and (0.5 + sdp.OFFSET) > 0.4 ) then 8 " + "when (sh.INCLUDE_RETURN is null and sh.INCLUDE_ORDER is null and (0.5 + sp.OFFSET) <= 0.4 and (0.5 + sp.OFFSET) > 0.3 ) " + " or ((0.5 + sdp.OFFSET) <= 0.4 and (0.5 + sdp.OFFSET) > 0.3 ) then 9 " + "when (sh.INCLUDE_RETURN is null and sh.INCLUDE_ORDER is null and (0.5 + sp.OFFSET) <= 0.3) " + " or ((0.5 + sdp.OFFSET) <= 0.3) then 10 end, hierarchy_long_name asc ) RN"; } } else if ( hierarchyLevel != null ) { sql += "ROW_NUMBER() OVER (ORDER BY " + data.get ( sortBy ) + " " + sortingOrder + " , hierarchy_long_name asc ) RN "; } else { sql += "ROW_NUMBER() OVER (ORDER BY " + data.get ( sortBy ) + " " + sortingOrder + " , hierarchyName asc ) RN "; } boolean isFinalized = isSessionFinalized ( sessionId ); // continuation of main query sql += " from " + ( isFinalized ? " session_site_para_finalized " : " SESSION_SITE_PARAMETER " ) + "ssp " + " inner join SITE s on ssp.SITE_ID=s.SITE_ID "; if ( hierarchyLevel != null && hierarchyId == null ) { sql += " inner join product_hierarchy ph on ssp.HIERARCHY_ID=ph.HIERARCHY_ID "; } else if ( hierarchyLevel != null && hierarchyId != null ) { sql += " inner join product_hierarchy ph on ssp.HIERARCHY_ID=ph.HIERARCHY_ID " + " inner join (select HIERARCHY_ID " + "from product_hierarchy start with HIERARCHY_ID = :hierarchyId " + "connect by prior HIERARCHY_ID = PARENT_ID) A on A.hierarchy_id=ph.hierarchy_id "; } else { sql += " inner join ph on ssp.HIERARCHY_ID=ph.HIERARCHY_ID "; } sql += " left join SESSION_DECOUPLED_PARAMETER sdp on sdp.session_id=ssp.session_id and ssp.SITE_ID=sdp.SITE_ID and ssp.HIERARCHY_ID=sdp.HIERARCHY_ID " + " left join SITE_PARAMETER sp on ssp.SITE_ID=sp.SITE_ID and ssp.HIERARCHY_ID=sp.HIERARCHY_ID " + " left join SESSION_HIERARCHY sh on ssp.session_id=sh.session_id and ssp.hierarchy_id=sh.hierarchy_id " + " where ssp.session_id=:sessionId "; // Filter setting to return a subset of data by site if ( siteId != null ) { sql += " and ssp.SITE_ID = :siteId "; } // Orders and Returns filters if ( isReturns && isOrders ) { sql += " and (ssp.RETURN_COUNT > 0 OR ssp.ORDER_COUNT > 0) "; } else if ( isReturns ) { sql += " and ssp.RETURN_COUNT > 0 "; } else if ( isOrders ) { sql += " and ssp.ORDER_COUNT > 0 "; } /* * Agressive Red Right or Red Left Slider Filter */ if ( Boolean.TRUE.equals ( aggressiveLeft ) ) { sql += " and (0.5 + sp.OFFSET < 1 and 0.5 + sp.OFFSET >= 0.7 and (sp.IS_LINKED = 'N' or ph.PARENT_ID is null) and ph.HIERARCHY_LEVEL=4)"; } else if ( Boolean.TRUE.equals ( aggressiveRight ) ) { sql += " and (0.5 + sp.OFFSET <= 0.3 and (sp.IS_LINKED = 'N' or ph.PARENT_ID is null) and ph.HIERARCHY_LEVEL=4)"; } /* * DNS or Sales Justify */ if ( Boolean.TRUE.equals ( dnsCount ) ) { sql += " and (ssp.MIN_GREATER_ZERO > 5000 and sp.STOCK_IT='N' and ph.HIERARCHY_LEVEL=4)"; } else if ( Boolean.TRUE.equals ( sjCount ) ) { sql += " and (ssp.MIN_GREATER_ZERO > 5000 and (0.5+sp.OFFSET) = 1 and sp.STOCK_IT='Y' and ph.HIERARCHY_LEVEL=4)"; } if ( Boolean.TRUE.equals ( isDeclineOrder ) && Boolean.TRUE.equals ( isDeclineReturn ) ) { sql += " and (ssp.DECLINE_ORDER=1 or ssp.DECLINE_RETURN=1) "; } else if ( Boolean.TRUE.equals ( isDeclineOrder ) ) { sql += " and ssp.DECLINE_ORDER=1 "; } else if ( Boolean.TRUE.equals ( isDeclineReturn ) ) { sql += " and ssp.DECLINE_RETURN=1 "; } if ( Boolean.TRUE.equals ( highSkuEdit ) ) { sql += " and (ssp.HIERARCHY_ID in (select level4_parent_id from " + " (select level4_parent_id, product_id, " + " max(case when recommended_return_qty > 0 or recommended_order_qty > 0 then 1 else 0 end) as isRecommended, " + " max(case when recommended_return_qty > 0 and return_qty < recommended_return_qty then 1 else 0 end) as hasReturnEdits, " + " max(case when recommended_order_qty > 0 then 1 else 0 end) as hasRecommendedOrders, " + " max(case when order_qty > 0 then 1 else 0 end) as hasOrders, " + " max(case when min_stocking_qty > 0 and (keep_qty is null or keep_qty > 0) then 1 else 0 end) isStocked " + " from session_site_product where session_id = :sessionId " + " and site_id in (select site_id from session_site where session_id = :sessionId) " + " group by level4_parent_id, product_id) " + " group by level4_parent_id " + " having " + " (sum (case when hasReturnEdits = 1 then 1 " + " when hasRecommendedOrders = 1 and hasOrders = 0 and isStocked = 0 then 1 " + " when hasRecommendedOrders = 0 and hasOrders = 1 then 1 " + " else 0 end) > 5 " + " or " + " sum (case when hasReturnEdits = 1 then 1 " + " when hasRecommendedOrders = 1 and hasOrders = 0 and isStocked = 0 then 1 " + " when hasRecommendedOrders = 0 and hasOrders = 1 then 1 " + " else 0 end) * 100 / nullif(sum (isRecommended),0) > 5) " + " ))"; } if ( highSkuEditHierarchyId != null ) { sql += " and ssp.HIERARCHY_ID = :highSkuEditHierarchyId "; } /* * Filter for "In Session" - isInSession flag has three states, which * matches the 3 states on the web ui (In Session, Not In Session, and * Both). Where isInSession is true if the "In Session" radio button is * selected, isInSession is false if the "Not in Session" radio button * is selected, and isInSession is null if the "Both" radio button is * selected. */ if ( Boolean.TRUE.equals ( isInSession ) ) { sql += " and ssp.HIERARCHY_ID in (select hierarchy_id from session_hierarchy where session_id = " + sessionId + ") "; } else if ( Boolean.FALSE.equals ( isInSession ) ) { sql += " and ssp.HIERARCHY_ID not in (select hierarchy_id from session_hierarchy where session_id = " + sessionId + ") "; } // isReviewed filter if ( Boolean.TRUE.equals ( isUnReviewed ) ) { sql += " and NVL(sp.IS_REVIEWED, 0) = 0 "; } if ( hierarchyLevel != null ) { sql += " and ssp.hierarchy_level = " + hierarchyLevel; } // continuation of main query if ( exportType.length ( ) == 0 ) { int startRowNumber = ( ( pageNumber - 1 ) * numberOfRecordsPerPage ) + 1; int endRowNumber = ( ( pageNumber * numberOfRecordsPerPage ) - 1 ) + 1; sql += ") WHERE RN BETWEEN " + startRowNumber + " AND " + endRowNumber; } // export expect (excel|pdf) if ( "".equals ( exportType ) ) { response.setContentType ( "text/html" ); response.setCharacterEncoding ( "UTF-8" ); PrintWriter pw = response.getWriter ( ); NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate ( jdbcTemplate ); MapSqlParameterSource parameters = new MapSqlParameterSource ( ); parameters.addValue ( "sessionId" , sessionId ); if ( siteId != null ) { parameters.addValue ( "siteId" , siteId ); } if ( hierarchyId != null ) { parameters.addValue ( "hierarchyId" , hierarchyId ); } if ( highSkuEditHierarchyId != null ) { parameters.addValue ( "highSkuEditHierarchyId" , highSkuEditHierarchyId ); } if ( hierarchyLevel != null ) { parameters.addValue ( "hierarchyLevel" , hierarchyLevel ); } List> sessionDashboardData; sessionDashboardData = namedParameterJdbcTemplate.queryForList ( sql , parameters ); Gson mygson = new GsonBuilder ( ).disableHtmlEscaping ( ).create ( ); pw.write ( mygson.toJson ( sessionDashboardData ) ); pw.close ( ); } else { processExport ( response , exportType , siteId , hierarchyId , sessionId , sql , user ); } LOGGER.info ( "getSessionDashboardData| Duration for sessionId " + sessionId + " and export type " + exportType + ": " + ( System.currentTimeMillis ( ) - start ) ); } @Override public void getMinMaxSessionDashboardData ( Integer sessionId , String minMaxSiteIds , String minMaxLineAbbr , String minMaxGroupCode , Boolean minMaxisReviewed , String isMinMaxSkus , String sortBy , String sortingOrder , Integer pageNumber , Integer numberOfRecordsPerPage , String exportType , HttpServletResponse response , User user,Integer salesHistoryMin, Integer salesHistoryMax ) throws Exception { boolean isFinalized = isSessionFinalized ( sessionId ); long start = System.currentTimeMillis ( ); LOGGER.info ( "getMinMaxSessionDashboardData - sessionId: " + sessionId ); /* Null checks and setting default values */ if ( exportType == null ) { exportType = PulseConstants.BLANK; } if ( sortingOrder == null ) { sortingOrder = "asc"; } String sortingStmt1 = "order by a.site_id " + sortingOrder + " "; String minMaxSkusFilterStmt = " \nAND (( a.min_stocking_qty <> a.curr_min OR a.max_stocking_qty <> a.curr_max ) \nOR ( a.min_stocking_qty = a.curr_min AND a.max_stocking_qty = a.curr_max ) ) "; if ( isMinMaxSkus.equals ( "true" ) ) minMaxSkusFilterStmt = " \nAND ( a.min_stocking_qty <> a.curr_min \n OR a.max_stocking_qty <> a.curr_max ) "; else if ( isMinMaxSkus.equals ( "false" ) ) minMaxSkusFilterStmt = " \nAND ( a.min_stocking_qty = a.curr_min\n AND a.max_stocking_qty = a.curr_max ) "; if ( StringUtils.isNotBlank ( minMaxSiteIds ) ) { if ( ! ( "[]" ).equals ( minMaxSiteIds ) ) { minMaxSiteIds = minMaxSiteIds.substring ( 1 , minMaxSiteIds.length ( ) - 1 ); minMaxSiteIds = " AND a.site_id IN ( " + minMaxSiteIds + " ) "; } else minMaxSiteIds = " "; } else minMaxSiteIds = " "; if ( StringUtils.isNotBlank ( minMaxLineAbbr ) ) { if ( ! ( "[]" ).equals ( minMaxLineAbbr ) ) { minMaxLineAbbr = minMaxLineAbbr.substring ( 1 , minMaxLineAbbr.length ( ) - 1 ); minMaxLineAbbr = minMaxLineAbbr.replace ( "\"" , "'" ); minMaxLineAbbr = " AND c.field_abbr IN ( " + minMaxLineAbbr + " ) "; } else minMaxLineAbbr = " "; } else minMaxLineAbbr = " "; if ( StringUtils.isNotBlank ( minMaxGroupCode ) ) { minMaxGroupCode = " AND c.group_code = " + minMaxGroupCode + " "; } else minMaxGroupCode = " "; // Reviewed String minMaxReviewedStmt = ""; if ( minMaxisReviewed != null && minMaxisReviewed ) { minMaxReviewedStmt = " and a.REVIEWED=1 "; } else if ( minMaxisReviewed != null && ! minMaxisReviewed ) { minMaxReviewedStmt = " and NVL(a.REVIEWED, 0) = 0 "; } String salesCode = jdbcTemplate.queryForObject ( "select MIN_MAX_SALES_SETTING from PULSE_SESSION where SESSION_ID=" + sessionId , String.class ); salesCode = ( "DIT" ).equals ( salesCode ) ? " NVL(a.sold_last_12_months,0) + NVL(a.supersede_12_months,0) " : ( "IT" ).equals ( salesCode ) ? " (NVL(a.sold_last_12_months,0) + NVL(a.transfer_12_months,0)) " : " (NVL(a.sold_last_12_months,0) - NVL(a.transfer_12_months,0)) "; String minMaxSaleshistory = ""; if ( salesHistoryMin != null && salesHistoryMax != null){ minMaxSaleshistory = " and " +salesCode + " BETWEEN " +salesHistoryMin+ " AND " +salesHistoryMax; } else if (salesHistoryMin == null && salesHistoryMax != null) { minMaxSaleshistory = " and " +salesCode+ " <= " +salesHistoryMax; } else if (salesHistoryMin != null && salesHistoryMax == null) { minMaxSaleshistory = " and " +salesCode + " >= " +salesHistoryMin; } String sortingStmt2 = " "; if ( sortBy != null ) { switch (sortBy) { case "line": sortingStmt1 = "order by c.field_abbr " + sortingOrder + " "; sortingStmt2 = " order by c.field_abbr " + sortingOrder + ", c.part_number asc"; break; case "group": sortingStmt1 = "order by c.group_code " + sortingOrder + " "; break; case "reviewed": sortingStmt1 = "order by a.REVIEWED " + sortingOrder + " "; break; case "perCar": sortingStmt1 = "order by c.per_car " + sortingOrder + " "; break; case "firstStockDate": sortingStmt1 = "order by a.initial_stocking_dt " + sortingOrder + " "; break; case "siteName": sortingStmt1 = "order by d.site_short_name " + sortingOrder + " "; break; case "newMin": sortingStmt1 = "order by a.min_stocking_qty " + sortingOrder + " "; break; case "newMax": sortingStmt1 = "order by a.max_stocking_qty " + sortingOrder + " "; break; case "currMin": sortingStmt1 = "order by a.curr_min " + sortingOrder + " "; break; case "currMax": sortingStmt1 = "order by a.curr_max " + sortingOrder + " "; break; case "partDescription": sortingStmt1 = "order by c.product_desc " + sortingOrder + " "; break; case "recommended": sortingStmt1 = "order by (a.max_stocking_qty * c.golden_price) " + sortingOrder + " "; break; case "change": sortingStmt1 = "order by ((a.max_stocking_qty * c.golden_price)-(a.curr_max * c.golden_price)) " + sortingOrder + " "; break; case "currentInv": sortingStmt1 = "order by (a.curr_max * c.golden_price) " + sortingOrder + " "; break; case "partNumber": sortingStmt1 = "order by c.part_number " + sortingOrder + " "; break; case "topApp": sortingStmt1 = "order by c.top_app " + sortingOrder + " "; break; case "salesHistory": if ( sortingOrder.equals ( "desc" ) ) sortingStmt1 = "order by " + salesCode + sortingOrder + " nulls last "; else sortingStmt1 = "order by " + salesCode + sortingOrder + " "; break; default: sortingStmt1 = "order by a.site_id " + sortingOrder + " "; } } String sql = "WITH min_max_data as (\n" + " SELECT a.site_id, a.REVIEWED, d.site_short_name , c.field_abbr , c.group_code , c.per_car , c.part_number , a.min_stocking_qty as newmin , a.max_stocking_qty as newmax , a.initial_stocking_dt ,\n" + " a.curr_min as currmin, a.curr_max as currmax , c.product_desc ,\n" + salesCode + " as saleshistory , c.top_app , (a.curr_max * c.golden_price) as currentinv ,\n" + " (a.max_stocking_qty * c.golden_price) as recommended, " + " ((a.max_stocking_qty * c.golden_price)-(a.curr_max * c.golden_price)) as changeval, ROW_NUMBER() over (" + sortingStmt1 + ") RN\n" + " FROM " + ( isFinalized ? "session_site_product_finalized" : "session_site_product" ) + " a, product c, site d\n" + " WHERE session_id = " + sessionId + " AND a.site_id IN (SELECT site_id FROM session_site WHERE session_Id = " + sessionId + " ) " + " AND a.product_id = c.product_id\n" + " AND a.site_id = d.site_id" + minMaxGroupCode + minMaxLineAbbr + minMaxSiteIds + minMaxSkusFilterStmt + minMaxReviewedStmt + sortingStmt2 + minMaxSaleshistory + ")\n" + " SELECT site_id \"siteId\" , site_short_name \"siteName\" , field_abbr \"line\", group_code \"group\", nvl(REVIEWED,0) \"reviewed\",per_car \"perCar\" ," + " part_number \"partNumber\",\n" + " newmin \"newMin\",newmax \"newMax\", initial_stocking_dt \"firstStockDate\",\n" + " currmin \"currMin\", currmax \"currMax\", product_desc \"partDescription\",\n" + " saleshistory \"salesHistory\", top_app \"topApp\", currentinv \"currentInv\",\n" + " recommended \"recommended\", changeval \"change\"\n" + " from min_max_data \n"; // System.out.println("Main Query" +sql); // continuation of main query if ( exportType.length ( ) == 0 ) { int startRowNumber = ( ( pageNumber - 1 ) * numberOfRecordsPerPage ) + 1; int endRowNumber = ( ( pageNumber * numberOfRecordsPerPage ) - 1 ) + 1; sql += "where RN BETWEEN " + startRowNumber + " AND " + endRowNumber; } // export expect (excel|pdf)\ if ( "".equals ( exportType ) ) { response.setContentType ( "text/html" ); response.setCharacterEncoding ( "UTF-8" ); PrintWriter pw = response.getWriter ( ); NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate ( jdbcTemplate ); MapSqlParameterSource parameters = new MapSqlParameterSource ( ); parameters.addValue ( "sessionId" , sessionId ); List> minMaxSessionDashboardData; minMaxSessionDashboardData = namedParameterJdbcTemplate.queryForList ( sql , parameters ); Gson mygson = new GsonBuilder ( ).disableHtmlEscaping ( ).create ( ); pw.write ( mygson.toJson ( minMaxSessionDashboardData ) ); pw.close ( ); } else { processExportForMinMax ( response , exportType , sql , user ); } LOGGER.info ( "getMinMaxSessionDashboardData| Duration for sessionId " + sessionId + ": " + ( System.currentTimeMillis ( ) - start ) ); } @Override public ActionResultItem>> updateSkuDashboardTableSetReviewedList ( Integer sessionId , DashboardList[] dashboardListArr ) { ActionResultItem>> actionItem = new ActionResultItem<> ( ); try { boolean isFinalized = isSessionFinalized ( sessionId ); jdbcTemplate.batchUpdate ( "update " + ( isFinalized ? " SESSION_SITE_PRODUCT_FINALIZED " : " SESSION_SITE_PRODUCT " ) + "set REVIEWED=? where SESSION_ID=? " + "and SITE_ID =? " + "and product_id in (select product_id from product where part_number = ?)" , new BatchPreparedStatementSetter ( ) { @Override public void setValues ( PreparedStatement ps , int i ) throws SQLException { DashboardList dashboardList = dashboardListArr[i]; ps.setBoolean ( 1 , dashboardList.getIsReviewed ( ) ); ps.setInt ( 2 , sessionId ); ps.setInt ( 3 , dashboardList.getSiteId ( ) ); ps.setString ( 4 , dashboardList.getPartNumber ( ) ); } @Override public int getBatchSize ( ) { return dashboardListArr.length; } } ); actionItem.setSuccess ( true ); } catch ( Exception e ) { LOGGER.error ( "updateSkuDashboardTableSetReviewedList: " , e ); actionItem.setSuccess ( false ); } return actionItem; } @Override public ActionResultItem>> updateSkuDashboardTableSetReviewedFilter ( SKUDashboardTableData skuDashboardTableData ) { ActionResultItem>> actionItem = new ActionResultItem<> ( ); int sessionId = skuDashboardTableData.getSessionId ( ); try { boolean isFinalized = isSessionFinalized ( sessionId ); Integer isReviewed = skuDashboardTableData.getIsReviewed ( ) ? 1 : 0; String append = ""; // Reviewed // if (skuDashboardTableData.getIsReviewed() != null && skuDashboardTableData.getIsReviewed()) { // append += " and ssp.REVIEWED=1 "; // } else if (skuDashboardTableData.getIsReviewed() != null && !skuDashboardTableData.getIsReviewed()) { // append += " and NVL(ssp.REVIEWED, 0) = 0 "; // } // Line Abbr filter String lineAbbr = skuDashboardTableData.getLineAbbr ( ); if ( lineAbbr != null && ! ( "[]" ).equals ( lineAbbr ) && ! lineAbbr.equalsIgnoreCase ( "null" ) ) { if ( StringUtils.isNotBlank ( lineAbbr ) ) { lineAbbr = lineAbbr.substring ( 1 , lineAbbr.length ( ) - 1 ); lineAbbr = lineAbbr.replace ( "\"" , "'" ); } else lineAbbr = " "; append += " and c.FIELD_ABBR IN ( " + lineAbbr + ")"; } // Group Code if ( skuDashboardTableData.getGroupCode ( ) >= 0 ) { append += " and c.GROUP_CODE = " + skuDashboardTableData.getGroupCode ( ) + " "; } //siteId String siteIds = skuDashboardTableData.getSiteIds ( ); if ( siteIds != null && ! ( "[]" ).equals ( siteIds ) && ! siteIds.equalsIgnoreCase ( "null" ) ) { if ( StringUtils.isNotBlank ( siteIds ) ) { siteIds = siteIds.substring ( 1 , siteIds.length ( ) - 1 ); siteIds = siteIds.replace ( "\"" , "'" ); } else siteIds = " "; append += " and a.site_id IN ( " + siteIds + ") "; } //Skus if ( skuDashboardTableData.getIsMinMaxSkus ( ).equals ( "true" ) ) { append += " and (a.min_stocking_qty <> a.curr_min OR a.max_stocking_qty <> a.curr_max )"; } else if ( skuDashboardTableData.getIsMinMaxSkus ( ).equals ( "false" ) ) { append += " and ( a.min_stocking_qty = a.curr_min AND a.max_stocking_qty = a.curr_max )"; } else { append += " and (( a.min_stocking_qty <> a.curr_min OR a.max_stocking_qty <> a.curr_max ) OR ( a.min_stocking_qty = a.curr_min AND a.max_stocking_qty = a.curr_max) ) "; } String sql = "merge into" + ( isFinalized ? " SESSION_SITE_PRODUCT_FINALIZED" : " SESSION_SITE_PRODUCT ssp using " + " (SELECT a.session_id,a.site_id ,a.product_id " + " FROM session_site_product a, product c WHERE session_id = " + sessionId + " " + " and SITE_ID IN (select site_id from session_site where SESSION_ID=" + sessionId + ") AND a.product_id = c.product_id " + append + " ) b" + " ON ( b.session_id = ssp.session_id AND b.site_id = ssp.site_id AND b.product_id = ssp.product_id)" + " WHEN MATCHED THEN UPDATE set ssp.reviewed='" + isReviewed + "'" ); // sql +=")"; jdbcTemplate.update ( sql ); actionItem.setSuccess ( true ); if ( skuDashboardTableData.getIsReviewed ( ) != null && skuDashboardTableData.getIsReviewed ( ) ) { actionItem.setMessage ( "Reviewed : 1" ); } else if ( skuDashboardTableData.getIsReviewed ( ) != null && ! skuDashboardTableData.getIsReviewed ( ) ) { actionItem.setMessage ( "Reviewed : 0" ); } } catch ( Exception e ) { LOGGER.error ( "updateSkuDashboardTableSetReviewedFilter: " , e ); actionItem.setSuccess ( false ); } return actionItem; } private void processExport ( HttpServletResponse response , String exportType , Integer siteId , Integer hierarchyId , Integer sessionId , String sql , User user ) throws Exception { try ( Connection conn = Objects.requireNonNull ( jdbcTemplate.getDataSource ( ) ).getConnection ( ); PreparedStatement ps = conn.prepareStatement ( sql ) ) { if ( hierarchyId != null && siteId != null ) { ps.setInt ( 1 , hierarchyId ); ps.setInt ( 2 , sessionId ); ps.setInt ( 3 , siteId ); } else if ( hierarchyId != null ) { ps.setInt ( 1 , hierarchyId ); ps.setInt ( 2 , sessionId ); } else if ( siteId != null ) { ps.setInt ( 1 , sessionId ); ps.setInt ( 2 , siteId ); } else { ps.setInt ( 1 , sessionId ); } ResultSet rs = ps.executeQuery ( ); if ( "excel".equals ( exportType ) ) { excelExporter.buildExcel ( user , rs , response , "Standard" ); } else { buildPDF ( user , rs , response ); } } catch ( SQLException e ) { throw new Exception ( e ); } } private void processExportForMinMax ( HttpServletResponse response , String exportType , String sql , User user ) throws Exception { try ( Connection conn = Objects.requireNonNull ( jdbcTemplate.getDataSource ( ) ).getConnection ( ); PreparedStatement ps = conn.prepareStatement ( sql ) ) { ResultSet rs = ps.executeQuery ( ); if ( "excel".equals ( exportType ) ) { excelExporter.buildExcel ( user , rs , response , "MinMax" ); } else { buildPDF ( user , rs , response ); } } catch ( SQLException e ) { throw new Exception ( e ); } } private void buildPDF ( User user , ResultSet rs , HttpServletResponse response ) throws DocumentException, SQLException, IOException { response.setHeader ( "Content-Type" , "application/pdf" ); response.setHeader ( "Content-Disposition" , "attachment;filename=\"pdfReport.pdf\"" ); Document document = new Document ( PageSize.A4 , 0 , 0 , 50 , 50 ); try { PdfWriter.getInstance ( document , response.getOutputStream ( ) ); Font font = FontFactory.getFont ( "Arial" , 5 , BaseColor.GRAY ); Font redFont = FontFactory.getFont ( "Arial" , 5 , BaseColor.RED ); document.open ( ); PdfPTable table = new PdfPTable ( 16 ); Map imgMap = new HashMap ( ) { private static final long serialVersionUID = 1L; { put ( "not_stocking" , "not_stocking.png" ); put ( "red_right_linked" , "red_right_linked.png" ); put ( "red_right_unlinked" , "red_right_unlinked.png" ); put ( "yellow_right_linked" , "yellow_right_linked.png" ); put ( "yellow_right_unlinked" , "yellow_right_unlinked.png" ); put ( "green_right_linked" , "green_right_linked.png" ); put ( "green_right_unlinked" , "green_right_unlinked.png" ); put ( "green_linked" , "green_linked.png" ); put ( "green_unlinked" , "green_unlinked.png" ); put ( "green_left_linked" , "green_left_linked.png" ); put ( "green_left_unlinked" , "green_left_unlinked.png" ); put ( "yellow_left_linked" , "yellow_left_linked.png" ); put ( "yellow_left_unlinked" , "yellow_left_unlinked.png" ); put ( "red_left_linked" , "red_left_linked.png" ); put ( "red_left_unlinked" , "red_left_unlinked.png" ); put ( "blue_linked" , "blue_linked.png" ); put ( "blue_unlinked" , "blue_unlinked.png" ); } }; float[] columnWidths = new float[]{50f , 100f , 60f , 60f , 80f , 80f , 80f , 60f , 60f , 60f , 80f , 80f , 80f , 80f , 80f , 80f}; table.setWidths ( columnWidths ); Paragraph p; PdfPCell c = null; Image img; DecimalFormat df = new DecimalFormat ( ); NumberFormat formatter = NumberFormat.getCurrencyInstance ( ); List colLst = new ArrayList ( ) { private static final long serialVersionUID = 1L; { add ( "Site" ); add ( "Hierarchy" ); add ( "Reco." ); add ( "Ignore Stock" ); add ( "Min > 0" ); add ( "COGS" ); add ( "Criticality" ); add ( "Turnover" ); add ( "Order Count" ); add ( "Return Count" ); add ( "Net Count" ); add ( "Order Total" ); add ( "Return Total" ); add ( "Stocking Return" ); add ( "Non-Stocking Return" ); add ( "Net Total" ); } }; List colLstFr = new ArrayList ( ) { private static final long serialVersionUID = 1L; { add ( "EntitÈs" ); add ( "HiÈrarchie" ); add ( "Reco." ); add ( "Ignorer la pÈriode d'approvisionnement" ); add ( "Min > 0" ); add ( "CMV" ); add ( "CriticitÈ" ); add ( "Tour d'inventaire" ); add ( "Produits ‡ commander" ); add ( "Produits ‡ retourner" ); add ( "Produits net" ); add ( "Commande Totale" ); add ( "Retour total" ); add ( "Retour tenu" ); add ( "Retour non-tenu" ); add ( "Valeur nette" ); } }; if ( "en".equals ( user.getLanguage ( ) ) ) { for (String s : colLst) { c = new PdfPCell ( ); p = new Paragraph ( s , font ); p.setAlignment ( Element.ALIGN_CENTER ); c.addElement ( p ); table.addCell ( c ); } } else { for (String s : colLstFr) { c = new PdfPCell ( ); p = new Paragraph ( s , font ); p.setAlignment ( Element.ALIGN_CENTER ); c.addElement ( p ); table.addCell ( c ); } } table.setHeaderRows ( 1 ); table.setComplete ( false ); int cnt = 0; while (rs.next ( )) { addTable ( rs , table , c , new Paragraph ( rs.getString ( "SITE_SHORT_NAME" ) , font ) ); addTable ( rs , table , c , new Paragraph ( rs.getString ( "hierarchy" ) , font ) ); ClassLoader classLoader = Thread.currentThread ( ).getContextClassLoader ( ); URL imgUrl = classLoader.getResource ( "static/" + imgMap.get ( rs.getString ( "reco" ) ) ); LOGGER.info ( "imgUrl -> " + imgUrl ); img = Image.getInstance ( imgUrl ); img.scalePercent ( 25 , 25 ); img.setAlignment ( Element.ALIGN_CENTER ); c = new PdfPCell ( ); c.addElement ( Image.getInstance ( img ) ); table.addCell ( c ); addTable ( rs , table , c , new Paragraph ( String.valueOf ( rs.getInt ( "IGNORE_STOCK" ) ) , font ) ); float minGtr0 = rs.getFloat ( "MIN_GREATER_ZERO" ); // $ value addTable ( rs , table , c , new Paragraph ( formatter.format ( minGtr0 ) , ( minGtr0 < 0 ) ? redFont : font ) ); float cogs = rs.getFloat ( "cogs" ); // $ value addTable ( rs , table , c , new Paragraph ( formatter.format ( cogs ) , ( cogs < 0 ) ? redFont : font ) ); df.setMaximumFractionDigits ( 0 ); float criticality = rs.getFloat ( "criticality" ); criticality = ( criticality * 100 ); if ( criticality > 0 && criticality < 1 ) { addTable ( rs , table , c , new Paragraph ( "<1%" , font ) ); } else { addTable ( rs , table , c , new Paragraph ( df.format ( criticality ) + "%" , font ) ); } df.setMaximumFractionDigits ( 1 ); addTable ( rs , table , c , new Paragraph ( df.format ( rs.getFloat ( "turnover" ) ) , font ) ); addTable ( rs , table , c , new Paragraph ( String.valueOf ( rs.getInt ( "ORDER_COUNT" ) ) , font ) ); addTable ( rs , table , c , new Paragraph ( String.valueOf ( rs.getInt ( "RETURN_COUNT" ) ) , font ) ); addTable ( rs , table , c , new Paragraph ( String.valueOf ( rs.getInt ( "NET_COUNT" ) ) , font ) ); float orderTotal = rs.getFloat ( "ORDER_TOTAL" ); // $ value addTable ( rs , table , c , new Paragraph ( formatter.format ( orderTotal ) , ( orderTotal < 0 ) ? redFont : font ) ); float returnTotal = rs.getFloat ( "RETURN_TOTAL" ); // $ value addTable ( rs , table , c , new Paragraph ( formatter.format ( returnTotal ) , ( returnTotal < 0 ) ? redFont : font ) ); float stockingReturn = rs.getFloat ( "STOCKING_RETURN" ); // $ // value addTable ( rs , table , c , new Paragraph ( formatter.format ( stockingReturn ) , ( stockingReturn < 0 ) ? redFont : font ) ); float nonStockingReturn = rs.getFloat ( "NON_STOCKING_RETURN" ); // $ // value addTable ( rs , table , c , new Paragraph ( formatter.format ( nonStockingReturn ) , ( nonStockingReturn < 0 ) ? redFont : font ) ); float netTotal = rs.getFloat ( "NET_TOTAL" ); // $ value addTable ( rs , table , c , new Paragraph ( formatter.format ( netTotal ) , ( netTotal < 0 ) ? redFont : font ) ); cnt++; if ( cnt == 10 ) { document.add ( table ); cnt = 0; } } table.setComplete ( true ); document.add ( table ); } catch ( DocumentException de ) { throw new DocumentException ( de ); } catch ( SQLException sqle ) { throw new SQLException ( sqle ); } catch ( IOException ioe ) { throw new IOException ( ioe ); } finally { document.close ( ); } } private void addTable ( ResultSet rs , PdfPTable table , PdfPCell c , Paragraph p ) { c = new PdfPCell ( ); p.setAlignment ( Element.ALIGN_CENTER ); c.addElement ( p ); table.addCell ( c ); } @Override public Integer getSessionDashboardCnt ( int sessionId , boolean isReturns , boolean isOrders , Boolean isInSession , Integer siteId , Integer level , Integer hierarchyId , Boolean isUnreviewed , Boolean aggressiveLeft , Boolean aggressiveRight , Boolean dnsCount , Boolean sjCount , Boolean highSkuEdit , Integer highSkuEditHierarchyId , Boolean isDeclineOrder , Boolean isDeclineReturn , Integer hierarchyLevel ) { boolean isFinalized = isSessionFinalized ( sessionId ); String sql = "with ph(HIERARCHY_ID, PARENT_ID) as " + "(select HIERARCHY_ID, PARENT_ID from PRODUCT_HIERARCHY where "; // Filter setting to return a hierarchy subset, else return everything if ( level != null && level < 4 && hierarchyId != null ) { sql += "PARENT_ID = :hierarchyId "; } else if ( level != null && level == 4 && hierarchyId != null ) { sql += "HIERARCHY_ID = :hierarchyId "; } else { sql += "PARENT_ID is null "; } sql += "union all select ph2.HIERARCHY_ID, ph2.PARENT_ID " + "from PRODUCT_HIERARCHY ph2, ph where ph2.PARENT_ID=ph.HIERARCHY_ID) "; sql += " select count(*) as \"cnt\" from " + ( isFinalized ? " session_site_para_finalized " : " SESSION_SITE_PARAMETER " ) + "ssp " + "inner join SITE s on ssp.SITE_ID=s.SITE_ID "; if ( hierarchyLevel != null && hierarchyId == null ) { sql += " inner join product_hierarchy ph on ssp.HIERARCHY_ID=ph.HIERARCHY_ID "; } else if ( hierarchyLevel != null ) { sql += " inner join product_hierarchy ph on ssp.HIERARCHY_ID=ph.HIERARCHY_ID " + " inner join (select HIERARCHY_ID " + "from product_hierarchy start with HIERARCHY_ID = :hierarchyId " + "connect by prior HIERARCHY_ID = PARENT_ID) A on A.hierarchy_id=ph.hierarchy_id "; } else { sql += "inner join ph on ssp.HIERARCHY_ID=ph.HIERARCHY_ID "; } sql += "left join SESSION_DECOUPLED_PARAMETER sdp on sdp.session_id=ssp.session_id and ssp.SITE_ID=sdp.SITE_ID and ssp.HIERARCHY_ID=sdp.HIERARCHY_ID " + "left join SITE_PARAMETER sp on ssp.SITE_ID=sp.SITE_ID and ssp.HIERARCHY_ID=sp.HIERARCHY_ID " + "left join SESSION_HIERARCHY sh on ssp.session_id=sh.session_id and ssp.hierarchy_id=sh.hierarchy_id " + "where ssp.session_id=:sessionId "; // Filter setting to return a subset of data by site if ( siteId != null ) { sql += " and ssp.SITE_ID = :siteId "; } // Orders and Returns filters if ( isReturns && isOrders ) { sql += " and (ssp.RETURN_COUNT > 0 OR ssp.ORDER_COUNT > 0) "; } else if ( isReturns ) { sql += " and ssp.RETURN_COUNT > 0 "; } else if ( isOrders ) { sql += " and ssp.ORDER_COUNT > 0 "; } /* * Agressive Red Right or Red Left Slider Filter */ if ( Boolean.TRUE.equals ( aggressiveLeft ) ) { sql += " and (0.5 + sp.OFFSET < 1 and 0.5 + sp.OFFSET >= 0.7 and (sp.IS_LINKED = 'N' or ph.PARENT_ID is null) and ssp.HIERARCHY_LEVEL=4)"; } else if ( Boolean.TRUE.equals ( aggressiveRight ) ) { sql += " and (0.5 + sp.OFFSET <= 0.3 and (sp.IS_LINKED = 'N' or ph.PARENT_ID is null) and ssp.HIERARCHY_LEVEL=4)"; } /* * DNS or Sales Justify */ if ( Boolean.TRUE.equals ( dnsCount ) ) { sql += " and (ssp.MIN_GREATER_ZERO > 5000 and sp.STOCK_IT='N' and ssp.HIERARCHY_LEVEL=4)"; } else if ( Boolean.TRUE.equals ( sjCount ) ) { sql += " and (ssp.MIN_GREATER_ZERO > 5000 and (0.5+sp.OFFSET) = 1 and sp.STOCK_IT='Y' and ssp.HIERARCHY_LEVEL=4)"; } // isReviewed filter if ( Boolean.TRUE.equals ( isUnreviewed ) ) { sql += " and NVL(sp.IS_REVIEWED, 0) = 0 "; } /* * Filter for "In Session" - isInSession flag has three states, which * matches the 3 states on the web ui (In Session, Not In Session, and * Both). Where isInSession is true if the "In Session" radio button is * selected, isInSession is false if the "Not in Session" radio button * is selected, and isInSession is null if the "Both" radio button is * selected. */ if ( Boolean.TRUE.equals ( isInSession ) ) { sql += " and ssp.HIERARCHY_ID in (select hierarchy_id from session_hierarchy where session_id = :sessionId )"; } else if ( Boolean.FALSE.equals ( isInSession ) ) { sql += " and ssp.HIERARCHY_ID not in (select hierarchy_id from session_hierarchy where session_id = :sessionId )"; } if ( Boolean.TRUE.equals ( highSkuEdit ) ) { sql += " and (ssp.HIERARCHY_ID in (select level4_parent_id from " + " (select level4_parent_id, product_id, " + " max(case when recommended_return_qty > 0 or recommended_order_qty > 0 then 1 else 0 end) as isRecommended, " + " max(case when recommended_return_qty > 0 and return_qty < recommended_return_qty then 1 else 0 end) as hasReturnEdits, " + " max(case when recommended_order_qty > 0 then 1 else 0 end) as hasRecommendedOrders, " + " max(case when order_qty > 0 then 1 else 0 end) as hasOrders, " + " max(case when min_stocking_qty > 0 and (keep_qty is null or keep_qty > 0) then 1 else 0 end) isStocked " + " from session_site_product " + " where session_id = :sessionId " + " and site_id in (select site_id from session_site where session_id = :sessionId ) " + " group by level4_parent_id, product_id) " + " group by level4_parent_id " + " having (sum (case when hasReturnEdits = 1 then 1 " + " when hasRecommendedOrders = 1 and hasOrders = 0 and isStocked = 0 then 1 " + " when hasRecommendedOrders = 0 and hasOrders = 1 then 1 else 0 end) > 5 " + " or sum (case when hasReturnEdits = 1 then 1 " + " when hasRecommendedOrders = 1 and hasOrders = 0 and isStocked = 0 then 1 " + " when hasRecommendedOrders = 0 and hasOrders = 1 then 1 " + " else 0 end) * 100 / nullif(sum (isRecommended),0) > 5) ))"; } if ( highSkuEditHierarchyId != null ) { sql += " and ssp.HIERARCHY_ID = :highSkuEditHierarchyId "; } if ( hierarchyLevel != null ) { sql += " and ssp.hierarchy_level = :hierarchyLevel "; } if ( Boolean.TRUE.equals ( isDeclineOrder ) ) { sql += " and ssp.DECLINE_ORDER=1 "; } if ( Boolean.TRUE.equals ( isDeclineReturn ) ) { sql += " and ssp.DECLINE_RETURN=1 "; } Integer sessionDashboardCnt; NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate ( jdbcTemplate ); MapSqlParameterSource parameters = new MapSqlParameterSource ( ); parameters.addValue ( "sessionId" , sessionId ); if ( siteId != null ) { parameters.addValue ( "siteId" , siteId ); } if ( hierarchyId != null ) { parameters.addValue ( "hierarchyId" , hierarchyId ); } if ( highSkuEditHierarchyId != null ) { parameters.addValue ( "highSkuEditHierarchyId" , highSkuEditHierarchyId ); } if ( hierarchyLevel != null ) { parameters.addValue ( "hierarchyLevel" , hierarchyLevel ); } sessionDashboardCnt = namedParameterJdbcTemplate.queryForObject ( sql , parameters , Integer.class ); return sessionDashboardCnt; } /* * (non-Javadoc) * * @see * com.napa.pulse.dao.interfaces.SessionDAO#getMinMaxSessionDashboardCnt(int) */ @Override public Integer getMinMaxSessionDashboardCnt ( int sessionId , String minMaxSiteIds , String minMaxLineAbbr , String minMaxGroupCode , Boolean minMaxisReviewed , String isMinMaxSkus,Integer salesHistoryMin,Integer salesHistoryMax ) { boolean isFinalized = isSessionFinalized ( sessionId ); String minMaxSkusFilterStmt = " \nAND (( a.min_stocking_qty <> a.curr_min OR a.max_stocking_qty <> a.curr_max ) \nOR ( a.min_stocking_qty = a.curr_min AND a.max_stocking_qty = a.curr_max) ) "; if ( isMinMaxSkus.equals ( "true" ) ) minMaxSkusFilterStmt = " \nAND ( a.min_stocking_qty <> a.curr_min \n OR a.max_stocking_qty <> a.curr_max ) "; else if ( isMinMaxSkus.equals ( "false" ) ) minMaxSkusFilterStmt = " \nAND ( a.min_stocking_qty = a.curr_min\n AND a.max_stocking_qty = a.curr_max ) "; if ( StringUtils.isNotBlank ( minMaxSiteIds ) ) { if ( ! ( "[]" ).equals ( minMaxSiteIds ) ) { minMaxSiteIds = minMaxSiteIds.substring ( 1 , minMaxSiteIds.length ( ) - 1 ); minMaxSiteIds = " AND a.site_id IN ( " + minMaxSiteIds + " ) "; } else minMaxSiteIds = " "; } else minMaxSiteIds = " "; if ( StringUtils.isNotBlank ( minMaxLineAbbr ) ) { if ( ! ( "[]" ).equals ( minMaxLineAbbr ) ) { minMaxLineAbbr = minMaxLineAbbr.substring ( 1 , minMaxLineAbbr.length ( ) - 1 ); minMaxLineAbbr = minMaxLineAbbr.replace ( "\"" , "'" ); minMaxLineAbbr = " AND c.field_abbr IN ( " + minMaxLineAbbr + " ) "; } else minMaxLineAbbr = " "; } else minMaxLineAbbr = " "; if ( StringUtils.isNotBlank ( minMaxGroupCode ) ) { minMaxGroupCode = " AND c.group_code = " + minMaxGroupCode + " "; } else minMaxGroupCode = " "; // Reviewed String minMaxReviewedStmt = ""; if ( minMaxisReviewed != null && minMaxisReviewed ) { minMaxReviewedStmt = " and a.REVIEWED=1 "; } else if ( minMaxisReviewed != null && ! minMaxisReviewed ) { minMaxReviewedStmt = " and NVL(a.REVIEWED, 0) = 0 "; } String salesCode = jdbcTemplate.queryForObject ( "select MIN_MAX_SALES_SETTING from PULSE_SESSION where SESSION_ID=" + sessionId , String.class ); salesCode = ( "DIT" ).equals ( salesCode ) ? " NVL(a.sold_last_12_months,0) + NVL(a.supersede_12_months,0) " : ( "IT" ).equals ( salesCode ) ? " (NVL(a.sold_last_12_months,0) + NVL(a.transfer_12_months,0)) " : " (NVL(a.sold_last_12_months,0) - NVL(a.transfer_12_months,0)) "; String minMaxSaleshistory = ""; if ( salesHistoryMin != null && salesHistoryMax != null){ minMaxSaleshistory = " and " +salesCode + " BETWEEN " +salesHistoryMin+ " AND " +salesHistoryMax; } else if (salesHistoryMin == null && salesHistoryMax != null) { minMaxSaleshistory = " and " +salesCode+ " <= " +salesHistoryMax; } else if (salesHistoryMin != null && salesHistoryMax == null) { minMaxSaleshistory = " and " +salesCode + " >= " +salesHistoryMin; } String sql = "WITH min_max_data as (\n" + "SELECT c.field_abbr , c.part_number , a.min_stocking_qty as newmin , a.max_stocking_qty as newmax , a.initial_stocking_dt ,\n" + " a.curr_min as minsq, a.curr_max as maxsq , c.product_desc ,\n" + salesCode + " as saleshistory , (a.curr_max * c.golden_price) as max_qty ,\n" + " (a.max_stocking_qty * c.golden_price) as min_qty, ROW_NUMBER() over (order by c.part_number asc) RN\n" + " FROM " + ( isFinalized ? "session_site_product_finalized" : "session_site_product" ) + " a, product c\n" + " WHERE session_id = " + sessionId + " AND a.site_id IN (SELECT site_id FROM session_site WHERE session_Id = " + sessionId + " ) " + " AND a.product_id = c.product_id" + minMaxGroupCode + minMaxLineAbbr + minMaxSiteIds + minMaxSkusFilterStmt + minMaxReviewedStmt + minMaxSaleshistory + ")\n" + " SELECT count(*) from min_max_data"; Integer sessionDashboardCnt; // System.out.println("cnt query" +sql); NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate ( jdbcTemplate ); MapSqlParameterSource parameters = new MapSqlParameterSource ( ); parameters.addValue ( "sessionId" , sessionId ); sessionDashboardCnt = namedParameterJdbcTemplate.queryForObject ( sql , parameters , Integer.class ); return sessionDashboardCnt; } /* * (non-Javadoc) * * @see * com.napa.pulse.dao.interfaces.SessionDAO#getSessionTypeCount(java.lang. * String) */ @Override public int getSessionTypeCount ( String sessionType , User user ) { String sql = " SELECT COUNT(PS.SESSION_ID) as \"countSessionType\" " + " FROM PULSE_SESSION PS, SESSION_STATUS SS " + " WHERE PS.STATUS_ID = SS.STATUS_ID AND " + " SS." + ( ( "en".equals ( user.getLanguage ( ) ) || user.getLanguage ( ) == null ) ? "HIGH_LEVEL_DESCRIPTION='Open'" : "HIGH_LEVEL_DESCRIPTION_FR='Ouvert'" ) + " AND CREATED_USER_ID = ?"; int sessionTypeCount = jdbcTemplate.queryForObject ( sql , Integer.class , user.getUserId ( ) ); return sessionTypeCount; } /* * (non-Javadoc) * * @see com.napa.pulse.dao.interfaces.SessionDAO#getPendingT.ransmissions() */ @Override public int getPendingTransmissions ( String responsibleUserAccessSites ) { if ( responsibleUserAccessSites == null || responsibleUserAccessSites.length ( ) == 0 ) { return 0; } // the pending transmission count is only for the sites the user is // responsible // for String sql = " SELECT COUNT(*) as \"countPendingTransmissions\" FROM TRANSMISSION T " + " WHERE T.ORDER_ON_DATE > SYSDATE " + " AND T.SITE_ID IN (SELECT SITE_ID FROM SITE S WHERE ACTIVE_FLAG = 'Y' AND (SITE_ID,0) IN (" + responsibleUserAccessSites + " ) )" + " AND ((T.ORDER_STATUS_ID = (SELECT TRANSMISSION_STATUS_ID FROM TRANSMISSION_STATUS WHERE TRANSMISSION_STATUS_CD = 'PP') " + " AND T.ORDER_ON_DATE >= SYSDATE) " + " OR (T.RETURN_STATUS_ID = (SELECT TRANSMISSION_STATUS_ID FROM TRANSMISSION_STATUS WHERE TRANSMISSION_STATUS_CD = 'PP') " + " AND T.RETURN_ON_DATE >= SYSDATE))"; int pendingTransmissionsCount = jdbcTemplate.queryForObject ( sql , Integer.class ); return pendingTransmissionsCount; } @Override public int getPendingApprovals ( User user ) { return jdbcTemplate .queryForObject ( "select count(SESSION_ID) as count " + "from pulse_session ps, pulse_user pu " + "where pu.USER_ID=ps.SUBMITTAL_USER_ID " + "and STATUS_ID=6 and SUBMITTAL_USER_ID=? " + "and (pu.role_id=10 or pu.role_id=8)" , new Object[]{user.getUserId ( )} , Integer.class ); } /* * (non-Javadoc) * * @see com.napa.pulse.dao.interfaces.SessionDAO#getActiveUserCount() */ @Override public int getActiveUserCount ( ) { String sql; sql = " SELECT " + " COUNT(DISTINCT(USER_ID)) as \"countActiveUsers\" " + " FROM USER_LOGIN WHERE LOGIN_TIME > (SYSDATE - 30)"; int activeUserCount = jdbcTemplate.queryForObject ( sql , Integer.class ); return activeUserCount; } /* * (non-Javadoc) * * @see com.napa.pulse.dao.interfaces.SessionDAO#getOverdueReturn() */ @Override public int getOverdueReturn ( String responsibleUserAccessSites ) { if ( responsibleUserAccessSites == null || responsibleUserAccessSites.length ( ) == 0 ) { return 0; } String sql = "SELECT COUNT(*) as \"countOverdueReturn\" FROM " + "(SELECT SITE_ID FROM SITE S WHERE ACTIVE_FLAG = 'Y' AND (SITE_ID,0) IN (" + responsibleUserAccessSites + " )) A " + "LEFT JOIN (SELECT SITE_ID, MAX(RETURN_ON_DATE) AS MAX_RETURN FROM TRANSMISSION GROUP BY SITE_ID) B ON A.SITE_ID = B.SITE_ID " + "WHERE MAX_RETURN IS NULL OR MAX_RETURN < (SYSDATE - 120)"; int overdueReturnCount = jdbcTemplate.queryForObject ( sql , Integer.class ); return overdueReturnCount; } /** * @param sessionId * @return */ @Override public List> getMinMaxSessionTotals ( int sessionId ) { boolean isFinalized = isSessionFinalized ( sessionId ); String sql; if ( isFinalized ) { sql = "select CURRENT_INVENTORY_TOTAL AS \"currentInvTotal\", RECOMMENDED_INVENTORY AS \"recommendedTotal\", NET_CHANGE AS \"netChange\", DEPTH_INC AS \"depthInc\", DEPTH_DEC AS \"depthDec\" " + "from PULSE_SESSION where session_id = " + sessionId; } else { sql = "select nvl(sum(currentInvTotal),0) as \"currentInvTotal\",\n" + " nvl(sum(recommendedTotal),0) as \"recommendedTotal\",\n" + " nvl(sum(netChange),0) as \"netChange\",\n" + " nvl(sum(depthInc),0) as \"depthInc\",\n" + " nvl(sum(depthDec),0) as \"depthDec\"\n" + "from\n" + "(SELECT \n" + "NVL(a.curr_max,0) * NVL(c.golden_price,0) AS currentInvTotal ,\n" + "NVL(a.max_stocking_qty,0) * NVL(c.golden_price,0) AS recommendedTotal ,\n" + "NVL(a.max_stocking_qty,0) * NVL(c.golden_price,0) - NVL(a.curr_max, 0) * NVL(c.golden_price, 0) as netChange,\n" + "case when ((a.max_stocking_qty * c.golden_price)-(a.curr_max * c.golden_price))>=0 then \n" + " NVL(a.max_stocking_qty,0) * NVL(c.golden_price,0) - NVL(a.curr_max, 0) * NVL(c.golden_price, 0) else 0 end depthInc,\n" + "case when ((a.max_stocking_qty * c.golden_price)-(a.curr_max * c.golden_price))<0 then \n" + " NVL(a.max_stocking_qty,0) * NVL(c.golden_price,0) - NVL(a.curr_max, 0) * NVL(c.golden_price, 0) else 0 end depthDec\n" + "FROM session_site_product a, product c\n" + "WHERE session_id = " + sessionId + " AND a.site_id IN (SELECT site_id FROM session_site WHERE session_Id = " + sessionId + " )\n" + "AND a.product_id = c.product_id)"; } // System.out.println("min max totals" + sql); return jdbcTemplate.queryForList ( sql ); } /** * @param sessionId * @return */ @Override public List> getSessionOrderReturnTotals ( int sessionId ) { boolean isFinalized = isSessionFinalized ( sessionId ); List> sessionOrderReturnTotals; if ( isFinalized ) { String sql = "select ORDER_TOTAL AS \"orderTotal\", STOCKING_RETURN_TOTAL AS \"stockingReturn\", NON_STOCKING_RETURN_TOTAL AS \"nonStockingReturn\", " + "NET_AMOUNT AS \"netTotal\" from PULSE_SESSION where session_id=?"; sessionOrderReturnTotals = jdbcTemplate.queryForList ( sql , sessionId ); } else { String sql = "select SUM(NVL(ORDER_TOTAL,0)) AS \"orderTotal\", " + " SUM(NVL(STOCKING_RETURN,0)) AS \"stockingReturn\", " + " SUM(NVL(NON_STOCKING_RETURN,0)) AS \"nonStockingReturn\" " + " from session_site_parameter SSP " + " where session_id = ? and site_id in (select site_id from session_site where session_id = ?) " + " and HIERARCHY_LEVEL=1"; sessionOrderReturnTotals = jdbcTemplate.queryForList ( sql , sessionId , sessionId ); if ( sessionOrderReturnTotals != null ) { double netTotal = 0; Map map = sessionOrderReturnTotals.get ( 0 ); if ( ( map.get ( "orderTotal" ) != null && map.get ( "stockingReturn" ) != null && map.get ( "nonStockingReturn" ) != null ) ) { netTotal = ( (BigDecimal) map.get ( "orderTotal" ) ).doubleValue ( ) - ( (BigDecimal) map.get ( "stockingReturn" ) ).doubleValue ( ) - ( (BigDecimal) map.get ( "nonStockingReturn" ) ).doubleValue ( ); } map.put ( "netTotal" , netTotal ); } } Map map = sessionOrderReturnTotals.get ( 0 ); map.put ( "showSpokeSiteExcelDropdown" , isSpokeSiteExcelDropdownVisible ( sessionId ) ); return sessionOrderReturnTotals; } public boolean isSpokeSiteExcelDropdownVisible ( Integer sessionId ) { try { if ( ! isStandard ( sessionId ) ) return false; Integer siteCount = jdbcTemplate.queryForObject ( "Select count(distinct ssp.site_id) from session_site_product ssp where ssp.session_id = " + sessionId , Integer.class ); if ( siteCount == null || siteCount != 1 ) return false; Integer rs = jdbcTemplate.queryForObject ( "select count( distinct hns.spoke_site_id ) from hs_network_spoke hns where is_active=1 and hns.spoke_site_id in (Select distinct ssp.site_id from session_site_product ssp where ssp.session_id = " + sessionId + " and ssp.hs_part='S') " , Integer.class ); return rs == 1 ? true : false; } catch ( Exception e ) { return false; } } @Override public List> getACOSessionOrderReturnTotals ( int sessionId ) { String sql = "select ORDER_TOTAL AS \"orderTotal\", STOCKING_RETURN AS \"stockingReturn\", NON_STOCKING_RETURN AS \"nonStockingReturn\", (ORDER_TOTAL-STOCKING_RETURN-NON_STOCKING_RETURN) AS \"netTotal\" from " + "(select session_id, site_id, sum(order_total) as order_total, sum(non_stocking_return) as non_stocking_return, sum(STOCKING_RETURN) as STOCKING_RETURN from " + "(select ss.session_id, ss.site_id, sh.hierarchy_id, ph.hierarchy_long_name, parent_id, " + "sum(case when on_hand > 0 and order_qty > on_hand then " + " (CASE WHEN nvl(std_pkg,0) = 0 THEN 0 WHEN product_rownum = 1 THEN nvl(ceil((order_qty - on_hand) / std_pkg) * std_pkg * price_wt_core, 0) END) " + " WHEN on_hand > 0 and nvl(order_qty, 0) <= on_hand then 0 " + " ELSE " + " (CASE WHEN nvl(std_pkg,0) = 0 THEN 0 WHEN product_rownum = 1 THEN nvl(ceil(order_qty / std_pkg) * std_pkg * price_wt_core, 0) END) end ) as order_total, " + "SUM (CASE WHEN product_rownum = 1 and NVL(sspc.min_stocking_qty,0) <= 0 THEN NVL(return_qty*price_wt_core,0) ELSE 0 END) AS non_stocking_return, " + "SUM (CASE WHEN product_rownum = 1 and sspc.min_stocking_qty > 0 THEN NVL(return_qty*price_wt_core,0) ELSE 0 END) AS stocking_return " + " from " + " session_site ss " + " join session_hierarchy sh on ss.session_id = sh.session_id " + "join product_hierarchy ph on ph.hierarchy_id = sh.hierarchy_id " + "join session_site_parameter ssp on ss.session_id = ssp.session_id and ss.site_id = ssp.site_id and sh.hierarchy_id = ssp.hierarchy_id " + "left join aco_preferences ap on ap.site_id = ss.site_id and sh.hierarchy_id = ap.level4_parent_id " + "left join (select a.PRODUCT_ID, spc.min_stocking_qty, level4_parent_id, order_qty, return_qty, on_hand, std_pkg, price_wt_core, " + "ROW_NUMBER() OVER (PARTITION BY a.product_id ORDER BY a.product_id, level4_parent_id) product_rownum \n" + "from aco_dup_report_pivot_final a " + " inner join session_site_product_coverage spc on a.session_id = spc.session_id and a.product_id = spc.product_id " + " and a.product_id = spc.product_id where a.session_id = ? " + " and a.product_id=spc.product_id and fitment_desc is not null) sspc on sspc.level4_parent_id = ph.hierarchy_id " + "where ss.session_id = ? and ph.hierarchy_level = 4 " + "group by ss.session_id, ss.site_id, sh.hierarchy_id, ph.hierarchy_long_name, parent_id) " + "group by session_id, site_id) "; return jdbcTemplate.queryForList ( sql , sessionId , sessionId ); } /** * @param sessionId * @return */ @Override public List> getMinMaxSessionOrderReturnTotals ( int sessionId , int siteId , boolean onlyForSingleSite ) { //boolean isFinalized = isSessionFinalized(sessionId); String sql = "select sum(currentInvTotal) as \"currentInvTotal\",\n" + " sum(recommendedTotal) as \"recommendedTotal\",\n" + " sum(netChange) as \"netChange\",\n" + " sum(depthInc) as \"depthInc\",\n" + " sum(depthDec)as \"depthDec\",\n" + " site_id,\n" + " site_short_name,\n" + " session_id \n" + "from\n" + "(SELECT\n" + "a.site_id,(select distinct s.site_short_name from site s where s.site_id=a.site_id and s.active_flag='Y') site_short_name, a.session_id,\n" + "NVL(a.curr_max,0) * NVL(c.golden_price,0) AS currentInvTotal ,\n" + "NVL(a.max_stocking_qty,0) * NVL(c.golden_price,0) AS recommendedTotal ,\n" + "NVL(a.max_stocking_qty,0) * NVL(c.golden_price,0) - NVL(a.curr_max, 0) * NVL(c.golden_price, 0) as netChange,\n" + "case when ((a.max_stocking_qty * c.golden_price)-(a.curr_max * c.golden_price))>=0 then \n" + " NVL(a.max_stocking_qty,0) * NVL(c.golden_price,0) - NVL(a.curr_max, 0) * NVL(c.golden_price, 0) else 0 end depthInc,\n" + "case when ((a.max_stocking_qty * c.golden_price)-(a.curr_max * c.golden_price))<0 then \n" + " NVL(a.max_stocking_qty,0) * NVL(c.golden_price,0) - NVL(a.curr_max, 0) * NVL(c.golden_price, 0) else 0 end depthDec\n" + "FROM session_site_product a, product c\n" + "WHERE session_id = ? AND a.site_id " + ( onlyForSingleSite ? ( " = " + siteId ) : ( " IN (SELECT site_id FROM session_site WHERE session_Id = " + sessionId + " )" ) ) + "\n" + "AND a.product_id = c.product_id)\n" + "group by site_id,site_short_name,session_id"; List> minMaxSessionTotals; // System.out.println("mm order test" + sql); minMaxSessionTotals = jdbcTemplate.queryForList ( sql , sessionId ); return minMaxSessionTotals; } /** * @param sessionId * @return */ @Override public List> getSessionOrderReturnTotalsDetails ( int sessionId ) { boolean isFinalized = isSessionFinalized ( sessionId ); String sql = "select a.session_id, a.site_id, a.SITE_SHORT_NAME, SUM(NVL(SSP.ORDER_TOTAL, 0)) AS \"orderTotal\", SUM(NVL(SSP.STOCKING_RETURN, 0)) AS \"stockingReturn\", " + "SUM(NVL(SSP.NON_STOCKING_RETURN, 0)) AS \"nonStockingReturn\", SUM(NVL(SSP.NET_TOTAL, 0)) AS \"netTotal\" " + "from (select ss.session_id, ss.site_id, S.SITE_SHORT_NAME from session_site ss, site s where ss.site_id = s.site_id and session_id = ?) A " + "left join " + ( isFinalized ? " session_site_para_finalized " : " SESSION_SITE_PARAMETER " ) + " ssp on ssp.session_id = a.session_id and ssp.site_id = a.site_id " + "and ssp.hierarchy_level=1 group by a.session_id, a.site_id, a.SITE_SHORT_NAME"; List> sessionOrderReturnTotals; sessionOrderReturnTotals = jdbcTemplate.queryForList ( sql , sessionId ); return sessionOrderReturnTotals; } @Override public List> getRecommendationChart ( int sessionId , boolean isOrders , boolean isReturns , Boolean isInSession , Integer siteId , Integer hierarchyId , Integer hierarchyLevel ) { boolean isFinalized = isSessionFinalized ( sessionId ); Map ptsRecommendations = commonService.getPTSRecommendations ( ); String sql = "select " + "nvl(sum(case when sp.STOCK_IT = 'N' then 1 " + "else 0 " + "end),0) as \"Grey\", " + "nvl(sum(case when (" + ptsRecommendations.get ( PulseConstants.SLIDER_MID_POINT ) + " + sp.OFFSET) <= " + ptsRecommendations.get ( PulseConstants.SLIDER_LEFT_YELLOW ) + " then 1 " + "else 0 " + "end),0) as \"RightRed\", " + "nvl(sum(case when ((" + ptsRecommendations.get ( PulseConstants.SLIDER_MID_POINT ) + " + sp.OFFSET) > " + ptsRecommendations.get ( PulseConstants.SLIDER_LEFT_YELLOW ) + " AND (" + ptsRecommendations.get ( PulseConstants.SLIDER_MID_POINT ) + " + sp.OFFSET) <= " + ptsRecommendations.get ( PulseConstants.SLIDER_LEFT_GREEN ) + ") then 1 " + "else 0 " + "end),0) as \"RightYellow\", " + "nvl(sum(case when ((" + ptsRecommendations.get ( PulseConstants.SLIDER_MID_POINT ) + " + sp.OFFSET) > " + ptsRecommendations.get ( PulseConstants.SLIDER_LEFT_GREEN ) + " AND (" + ptsRecommendations.get ( PulseConstants.SLIDER_MID_POINT ) + " + sp.OFFSET) < " + ptsRecommendations.get ( PulseConstants.SLIDER_RIGHT_GREEN ) + ") then 1 " + "else 0 " + "end),0) as \"Green\", " + "nvl(sum(case when ((" + ptsRecommendations.get ( PulseConstants.SLIDER_MID_POINT ) + " + sp.OFFSET) >= " + ptsRecommendations.get ( PulseConstants.SLIDER_RIGHT_GREEN ) + " AND (" + ptsRecommendations.get ( PulseConstants.SLIDER_MID_POINT ) + " + sp.OFFSET) < " + ptsRecommendations.get ( PulseConstants.SLIDER_RIGHT_YELLOW ) + ") then 1 " + "else 0 " + "end),0) as \"LeftYellow\", " + "nvl(sum(case when ((" + ptsRecommendations.get ( PulseConstants.SLIDER_MID_POINT ) + " + sp.OFFSET) >= " + ptsRecommendations.get ( PulseConstants.SLIDER_RIGHT_YELLOW ) + " AND (" + ptsRecommendations.get ( PulseConstants.SLIDER_MID_POINT ) + " + sp.OFFSET) < " + ptsRecommendations.get ( PulseConstants.SLIDER_LEFT_BLUE ) + ") then 1 " + "else 0 " + "end),0) as \"LeftRed\", " + "nvl(sum(case when (" + ptsRecommendations.get ( PulseConstants.SLIDER_MID_POINT ) + " + sp.OFFSET) = " + ptsRecommendations.get ( PulseConstants.SLIDER_LEFT_BLUE ) + " and sp.STOCK_IT = 'Y' then 1 " + "else 0 " + "end),0) as \"Blue\" "; if ( hierarchyId == null ) { sql += " FROM " + ( isFinalized ? " session_site_para_finalized " : " SESSION_SITE_PARAMETER " ) + "ssp, SITE_PARAMETER sp " + "where " + "session_id= ? and " + "ssp.SITE_ID=sp.SITE_ID and " + "ssp.HIERARCHY_ID=sp.HIERARCHY_ID "; } else { sql += " FROM " + ( isFinalized ? " session_site_para_finalized " : " SESSION_SITE_PARAMETER " ) + "ssp, SITE_PARAMETER sp, " + " (select hierarchy_id from product_hierarchy " + " start with parent_id = ? " + " connect by prior hierarchy_id = parent_id) ph " + "where " + "session_id= ? and " + "ssp.SITE_ID=sp.SITE_ID and " + "ssp.HIERARCHY_ID=sp.HIERARCHY_ID and " + "sp.hierarchy_id = ph.hierarchy_id "; } // Filter setting to return a subset of data by site if ( siteId != null ) { sql += " and ssp.SITE_ID = ? "; } // Orders and Returns filters if ( isReturns && isOrders ) { sql += " and (ssp.RETURN_COUNT > 0 OR ssp.ORDER_COUNT > 0) "; } else if ( isReturns ) { sql += " and ssp.RETURN_COUNT > 0 "; } else if ( isOrders ) { sql += " and ssp.ORDER_COUNT > 0 "; } /* * Filter for "In Session" - isInSession flag has three states, which * matches the 3 states on the web ui (In Session, Not In Session, and * Both). Where isInSession is true if the "In Session" radio button is * selected, isInSession is false if the "Not in Session" radio button * is selected, and isInSession is null if the "Both" radio button is * selected. */ if ( Boolean.TRUE.equals ( isInSession ) ) { sql += " and ssp.HIERARCHY_ID in (select hierarchy_id from session_hierarchy where session_id = :sessionId) "; } else if ( Boolean.FALSE.equals ( isInSession ) ) { sql += " and ssp.HIERARCHY_ID not in (select hierarchy_id from session_hierarchy where session_id = :sessionId) "; } if ( hierarchyLevel != null ) { sql += " and ssp.hierarchy_level = " + hierarchyLevel; } // different prepared statements based on filters being set, else no // filters List> sessionDashboardData; if ( hierarchyId != null && siteId != null && isInSession != null ) { sessionDashboardData = jdbcTemplate.queryForList ( sql , hierarchyId , sessionId , siteId , sessionId ); } else if ( hierarchyId != null && siteId != null ) { sessionDashboardData = jdbcTemplate.queryForList ( sql , hierarchyId , sessionId , siteId ); } else if ( isInSession != null && siteId != null ) { sessionDashboardData = jdbcTemplate.queryForList ( sql , sessionId , siteId , sessionId ); } else if ( isInSession != null && hierarchyId != null ) { sessionDashboardData = jdbcTemplate.queryForList ( sql , hierarchyId , sessionId , sessionId ); } else if ( isInSession != null ) { sessionDashboardData = jdbcTemplate.queryForList ( sql , sessionId , sessionId ); } else if ( hierarchyId != null ) { sessionDashboardData = jdbcTemplate.queryForList ( sql , hierarchyId , sessionId ); } else if ( siteId != null ) { sessionDashboardData = jdbcTemplate.queryForList ( sql , sessionId , siteId ); } else { sessionDashboardData = jdbcTemplate.queryForList ( sql , sessionId ); } return sessionDashboardData; } /** * Returns the first level only of hierarchical names in a session * * @param sessionId * @return */ @Override public List> getHierarchicalNames ( Integer sessionId , User user ) { boolean isFinalized = isSessionFinalized ( sessionId ); String sql = "select distinct(ph." + ( ( "en".equals ( user.getLanguage ( ) ) || user.getLanguage ( ) == null ) ? "HIERARCHY_LONG_NAME" : "HIERARCHY_LONG_NAME_FR" ) + ") as \"hierarchyName\", ph.hierarchy_id as \"hierarchyId\" " + "FROM " + ( isFinalized ? " session_site_para_finalized " : " SESSION_SITE_PARAMETER " ) + "ssp, product_hierarchy ph " + "where ssp.hierarchy_id=ph.hierarchy_id " + "and ssp.session_id=? " + "and ph.parent_id is null order by ph." + ( ( "en".equals ( user.getLanguage ( ) ) || user.getLanguage ( ) == null ) ? "HIERARCHY_LONG_NAME" : "HIERARCHY_LONG_NAME_FR" ) + ""; List> hierarchialNameMap = jdbcTemplate.queryForList ( sql , sessionId ); return hierarchialNameMap; } /** * Returns the N level of hierarchical (not first) names in a session * * @param sessionId * @param hierarchyId * @return */ @Override public List> getHierarchicalNames ( Integer sessionId , Integer hierarchyId , User user ) { boolean isFinalized = isSessionFinalized ( sessionId ); String sql = "select distinct(ph." + ( ( "en".equals ( user.getLanguage ( ) ) || user.getLanguage ( ) == null ) ? "HIERARCHY_LONG_NAME" : "HIERARCHY_LONG_NAME_FR" ) + ") as \"hierarchyName\", ph.hierarchy_id as \"hierarchyId\" " + "from product_hierarchy ph, " + ( isFinalized ? " session_site_para_finalized " : " SESSION_SITE_PARAMETER " ) + "ssp where ph.parent_id=? " + "and ssp.HIERARCHY_ID=ph.HIERARCHY_ID " + "and ssp.session_id=? " + "order by ph." + ( ( "en".equals ( user.getLanguage ( ) ) || user.getLanguage ( ) == null ) ? "HIERARCHY_LONG_NAME" : "HIERARCHY_LONG_NAME_FR" ) + ""; List> hierarchialNameMap = jdbcTemplate.queryForList ( sql , hierarchyId , sessionId ); return hierarchialNameMap; } @Override public List> getInventoryHistory ( int sessionId , boolean isOrders , boolean isReturns , Boolean isInSession , Integer siteId , Integer hierarchyId ) { boolean isFinalized = isSessionFinalized ( sessionId ); String sql; if ( hierarchyId != null ) { // Note: only level 1-3 may be passed in the filter sql = "select SUM(NVL(SALES,0)) as \"sales\", SUM(NVL(i.STOCKING_INVENTORY,0)) as \"stocking\", " + " SUM(NVL(NON_STOCKING_INVENTORY,0)) as \"nonStocking\", trunc(MON_YEAR) as \"date\" " + " from INVENTORY_SUMMARY_24 i, " + ( isFinalized ? " session_site_para_finalized " : " SESSION_SITE_PARAMETER " ) + " ssp, (select hierarchy_id from product_hierarchy " + " where hierarchy_level = 4 " + " start with parent_id = ? " + " connect by prior hierarchy_id = parent_id) ph " + " where ssp.site_id in (select site_id from session_site where session_id = ?) and " + " session_id= ? and " + " i.site_id = ssp.site_id and " + " i.hierarchy_id = ssp.hierarchy_id and " + " ssp.hierarchy_id = ph.hierarchy_id "; } else { sql = "select sum(nvl(SALES,0)) as \"sales\", sum(nvl(i.STOCKING_INVENTORY,0)) as \"stocking\", " + " sum(nvl(NON_STOCKING_INVENTORY,0)) as \"nonStocking\", trunc(MON_YEAR) as \"date\" " + " from INVENTORY_SUMMARY_24 i, " + ( isFinalized ? " session_site_para_finalized " : " SESSION_SITE_PARAMETER " ) + " ssp where " + " ssp.site_id in (select site_id from session_site where session_id = ?) and session_id = ? and " + " i.site_id = ssp.site_id and " + " i.hierarchy_id = ssp.hierarchy_id " + " and hierarchy_level = 4"; } // Filter setting to return a subset of data by site if ( siteId != null ) { sql += " and ssp.SITE_ID = ? "; } // Orders and Returns filters if ( isReturns && isOrders ) { sql += " and (ssp.RETURN_COUNT > 0 OR ssp.ORDER_COUNT > 0) "; } else if ( isReturns ) { sql += " and ssp.RETURN_COUNT > 0 "; } else if ( isOrders ) { sql += " and ssp.ORDER_COUNT > 0 "; } /* * Filter for "In Session" - isInSession flag has three states, which * matches the 3 states on the web ui (In Session, Not In Session, and * Both). Where isInSession is true if the "In Session" radio button is * selected, isInSession is false if the "Not in Session" radio button * is selected, and isInSession is null if the "Both" radio button is * selected. */ if ( Boolean.TRUE.equals ( isInSession ) ) { sql += " and ssp.HIERARCHY_ID in (select hierarchy_id from session_hierarchy where session_id = :sessionId) "; } else if ( Boolean.FALSE.equals ( isInSession ) ) { sql += " and ssp.HIERARCHY_ID not in (select hierarchy_id from session_hierarchy where session_id = :sessionId) "; } sql += " group by trunc(mon_year)"; // different prepared statements based on filters being set, else no // filters List> sessionDashboardData; if ( hierarchyId != null && siteId != null && isInSession != null ) { sessionDashboardData = jdbcTemplate.queryForList ( sql , hierarchyId , sessionId , sessionId , siteId , sessionId ); } else if ( hierarchyId != null && siteId != null ) { sessionDashboardData = jdbcTemplate.queryForList ( sql , hierarchyId , sessionId , sessionId , siteId ); } else if ( isInSession != null && siteId != null ) { sessionDashboardData = jdbcTemplate.queryForList ( sql , sessionId , sessionId , siteId , sessionId ); } else if ( isInSession != null && hierarchyId != null ) { sessionDashboardData = jdbcTemplate.queryForList ( sql , hierarchyId , sessionId , sessionId , sessionId ); } else if ( isInSession != null ) { sessionDashboardData = jdbcTemplate.queryForList ( sql , sessionId , sessionId , sessionId ); } else if ( hierarchyId != null ) { sessionDashboardData = jdbcTemplate.queryForList ( sql , hierarchyId , sessionId , sessionId ); } else if ( siteId != null ) { sessionDashboardData = jdbcTemplate.queryForList ( sql , sessionId , sessionId , siteId ); } else { sessionDashboardData = jdbcTemplate.queryForList ( sql , sessionId , sessionId ); } return sessionDashboardData; } @Override public List> getInventoryHistory ( Integer siteId , Integer hierarchyId ) { return jdbcTemplate.queryForList ( "select SALES as \"sales\", STOCKING_INVENTORY as \"stocking\", " + "NON_STOCKING_INVENTORY as \"nonStocking\", MON_YEAR as \"date\" from INVENTORY_SUMMARY_24 WHERE SITE_ID = ? AND HIERARCHY_ID = ?" , siteId , hierarchyId ); } @Override public List> getCubicFeet ( int sessionId ) { return jdbcTemplate.queryForList ( "select round((nvl(ORDER_CUBIC_TOTAL,0)/1728), 2) as \"orderCubic\", round((nvl(RETURN_CUBIC_TOTAL,0)/1728), 2) as \"returnCubic\", " + "round((NET_CUBIC_TOTAL/1728), 2) as \"netCubic\" " + "from pulse_session where session_id=?" , sessionId ); } /** * @param sessionId * @param siteId * @param parentId * @return */ @Override public List getSessionSiteParameters ( Integer sessionId , Integer siteId , Integer parentId ) { List listSessionSiteParameter; boolean isFinalized = isSessionFinalized ( sessionId ); String sqlSessionSiteParametersDetail = "SELECT SSP.SITE_ID , SSP.HIERARCHY_ID ,round(ssp.CRITICALITY,3) ,SSP.ORDER_TOTAL ,SSP.RETURN_TOTAL ," + "SSP.NON_STOCKING_RETURN ,SSP.STOCKING_RETURN ,SSP.RETURN_COUNT ," + "SSP.ORDER_COUNT ,SSP.NET_COUNT ,SSP.MIN_GREATER_ZERO ,SSP.COGS ,SSP.HIERARCHY_LEVEL ," + "SSP.NET_TOTAL, SP.STOCK_IT, SP.IS_LINKED, SP.HAS_LINKED_CHILDREN " + " FROM " + ( isFinalized ? " session_site_para_finalized " : " SESSION_SITE_PARAMETER " ) + " SSP, SITE_PARAMETER SP, PRODUCT_HIERARCHY PH " + "WHERE SSP.HIERARCHY_ID = PH.HIERARCHY_ID " + "AND SSP.SITE_ID = SP.SITE_ID " + "AND SSP.HIERARCHY_ID = SP.HIERARCHY_ID " + "AND SSP.SITE_ID = ? AND SSP.SESSION_ID = ? "; // if the level is 1 if ( parentId == null ) { sqlSessionSiteParametersDetail += "AND PH.PARENT_ID IS NULL "; } else { // for other levels sqlSessionSiteParametersDetail += "AND PH.PARENT_ID = ? "; } if ( parentId == null ) { listSessionSiteParameter = jdbcTemplate.query ( sqlSessionSiteParametersDetail , new Object[]{siteId , sessionId} , new SessionSiteParameterDTOMapper ( commonService.getPTSRecommendations ( ) ) ); } else { listSessionSiteParameter = jdbcTemplate.query ( sqlSessionSiteParametersDetail , new Object[]{siteId , sessionId , parentId} , new SessionSiteParameterDTOMapper ( commonService.getPTSRecommendations ( ) ) ); } return listSessionSiteParameter; } /* * (non-Javadoc) * * @see * com.napa.pulse.dao.interfaces.SessionDAO#getSessionSiteParameters(java. * lang. Integer, java.lang.Integer, java.lang.Integer[]) */ @Override public List getSessionSiteParameters ( Integer sessionId , Integer siteId , Integer[] nodeIds ) { boolean isFinalized = isSessionFinalized ( sessionId ); StringBuilder sqlSessionSiteParametersDetail = new StringBuilder ( "SELECT SSP.SITE_ID, SSP.HIERARCHY_ID, round(ssp.CRITICALITY,3), SSP.ORDER_TOTAL, SSP.RETURN_TOTAL, " + "SSP.NON_STOCKING_RETURN, SSP.STOCKING_RETURN, SSP.RETURN_COUNT, " + "SSP.ORDER_COUNT, SSP.NET_COUNT, SSP.MIN_GREATER_ZERO, SSP.COGS, SSP.HIERARCHY_LEVEL, " + "SSP.NET_TOTAL, SP.STOCK_IT, SP.IS_LINKED, SP.HAS_LINKED_CHILDREN, SP.OFFSET " + " FROM " + ( isFinalized ? " session_site_para_finalized " : " SESSION_SITE_PARAMETER " ) + "SSP, SITE_PARAMETER SP " + "WHERE SSP.SITE_ID = SP.SITE_ID AND SSP.HIERARCHY_ID = SP.HIERARCHY_ID " + "AND SSP.SITE_ID = ? AND SSP.SESSION_ID = ? " + "AND SSP.HIERARCHY_ID IN (" ); for (Integer nodeId : nodeIds) { sqlSessionSiteParametersDetail.append ( nodeId + "," ); } sqlSessionSiteParametersDetail.append ( PulseConstants.DYNAMIC_QUERY_END_INTEGER ); List listSessionSiteParameter = jdbcTemplate.query ( sqlSessionSiteParametersDetail.toString ( ) , new Object[]{siteId , sessionId} , new SessionSiteParameterDTOMapper ( commonService.getPTSRecommendations ( ) ) ); return listSessionSiteParameter; } @Override public String getSessionState ( Integer sessionId , User user ) { try { return jdbcTemplate.queryForObject ( "select " + ( ( "en".equals ( user.getLanguage ( ) ) || user.getLanguage ( ) == null ) ? "HIGH_LEVEL_DESCRIPTION" : "HIGH_LEVEL_DESCRIPTION_FR" ) + " from " + "PULSE_SESSION ps, SESSION_STATUS ss where SESSION_ID=? " + "and ps.STATUS_ID=ss.STATUS_ID" , String.class , sessionId ); } catch ( Exception e ) { LOGGER.error ( "Session " + sessionId + " was not found in PULSE_SESSION table." ); return "Session " + sessionId + " has been deleted."; } } @Override public void deleteSession ( Integer sessionId , int userId ) { new Thread ( new DeleteSessionRunner ( sessionId , userId ) ).start ( ); } private class DeleteSessionRunner implements Runnable { private final int sessionId; private final int userId; public DeleteSessionRunner ( int sessionId , int userId ) { this.sessionId = sessionId; this.userId = userId; } @Override public void run ( ) { jdbcTemplate.update ( "CALL DELETE_SESSION (?)" , sessionId ); LOGGER.info ( "Delete session| sessionId " + sessionId + ", userId " + userId ); } } @Override public boolean checkEmptySiteGroup ( Integer siteGroupObj , String userAccessSites ) { String sql = "select count(sgm.SITE_ID) from SITE_GROUP_MAPPING sgm " + "where sgm.SITE_GROUP_ID = ? "; if ( userAccessSites.length ( ) > 0 ) { sql += "and (sgm.SITE_ID,0) in (" + userAccessSites + " )"; } int countSitesPerSiteGroup = jdbcTemplate.queryForObject ( sql , new Object[]{siteGroupObj} , Integer.class ); return countSitesPerSiteGroup != 0; } /* * (non-Javadoc) * * @see * com.napa.pulse.dao.interfaces.SessionDAO#sessionReviewPreferenceSettings( * int) */ @Override public List sessionReviewPreferenceSettings ( int sessionId ) { List list = new ArrayList<> ( ); String sql = "SELECT DISTINCT SS.SITE_GROUP_NAME, PS.SESSION_ID, INCLUDE_HS_ORDER, INCLUDE_HS_RETURN, EXCLUDE_DISPLAY_ITEM,SESSION_COMMENT, INCLUDE_OVERSTOCK, INCLUDE_SPECIALS, INCLUDE_OBSOLETES, INCLUDE_ORDERS, INCLUDE_SELL_THRU,INCLUDE_RETURNS, INCLUDE_LT_STD_PKG, NVL(INCLUDE_FLEETS, 0) INCLUDE_FLEETS, " + "NVL(USE_INV_COLL, 0) USE_INV_COLL, NVL(INCLUDE_SYSTEMS, 0) INCLUDE_SYSTEMS, NVL(PROVIDE_STORE_DEPTH, 0) PROVIDE_STORE_DEPTH,NSD_WEEK_SUPPLY_OPTION, EXPIRATION_DATE, CREATED_DATE, " + "CASE WHEN UPPER(SS.SITE_GROUP_NAME) = 'CUSTOM GROUP' THEN 'Y' ELSE 'N' END AS createdByCustomGroup, SESSION_DESC, PER_CAR_ORDER, INCLUDE_SALES_DEMAND, INCLUDE_NEW_NUMBERS, INCLUDE_ORDER_ALL, INCLUDE_RETURN_ALL, DOLLAR_LIMIT, APPLICATION_PARTS_ONLY " + "FROM PULSE_SESSION PS, SESSION_SITE SS WHERE PS.SESSION_ID = SS.SESSION_ID AND PS.SESSION_ID = ?"; SqlRowSet rs = jdbcTemplate.queryForRowSet ( sql , sessionId ); while (rs.next ( )) { SessionPreferencesDTO sessionPreferencesDTO = new SessionPreferencesDTO ( ); sessionPreferencesDTO.setSelectSiteGroupName ( rs.getString ( "SITE_GROUP_NAME" ) ); sessionPreferencesDTO.setSessionId ( rs.getInt ( "SESSION_ID" ) ); sessionPreferencesDTO.setIncludeHubSpokeOrder ( rs.getString ( "INCLUDE_HS_ORDER" ) == null ? 0 : 1 ); sessionPreferencesDTO.setIncludeHubSpokeReturn ( rs.getString ( "INCLUDE_HS_RETURN" ) == null ? 0 : 1 ); sessionPreferencesDTO.setComments ( rs.getString ( "SESSION_COMMENT" ) ); sessionPreferencesDTO.setIncludeOverstocks ( rs.getInt ( "INCLUDE_OVERSTOCK" ) ); sessionPreferencesDTO.setIncludeSpecials ( rs.getInt ( "INCLUDE_SPECIALS" ) ); sessionPreferencesDTO.setIncludeObsoletes ( rs.getInt ( "INCLUDE_OBSOLETES" ) ); sessionPreferencesDTO.setIncludeOrders ( rs.getInt ( "INCLUDE_ORDERS" ) ); sessionPreferencesDTO.setIncludeReturns ( rs.getInt ( "INCLUDE_RETURNS" ) ); sessionPreferencesDTO.setIncludeLessThanStdPkg ( rs.getInt ( "INCLUDE_LT_STD_PKG" ) ); sessionPreferencesDTO.setIncludeFleets ( rs.getInt ( "INCLUDE_FLEETS" ) ); sessionPreferencesDTO.setIncludeSystems ( rs.getInt ( "INCLUDE_SYSTEMS" ) ); sessionPreferencesDTO.setUseInvColls ( rs.getInt ( "USE_INV_COLL" ) ); sessionPreferencesDTO.setProvideStoreDepth ( rs.getInt ( "PROVIDE_STORE_DEPTH" ) ); sessionPreferencesDTO.setWeekSupplyOptionName(rs.getString("NSD_WEEK_SUPPLY_OPTION")); sessionPreferencesDTO.setExpirationDate ( sdf.format ( rs.getDate ( "EXPIRATION_DATE" ) ) ); sessionPreferencesDTO.setCreatedByCustomGroup ( rs.getString ( "CREATEDBYCUSTOMGROUP" ) ); sessionPreferencesDTO.setSessionCreationDate ( sdf.format ( rs.getDate ( "CREATED_DATE" ) ) ); sessionPreferencesDTO.setSessionDescription ( rs.getString ( "SESSION_DESC" ) ); sessionPreferencesDTO.setPerCarOrder ( rs.getInt ( "PER_CAR_ORDER" ) ); sessionPreferencesDTO.setIncludeSoldNotStocked ( rs.getInt ( "INCLUDE_SALES_DEMAND" ) ); sessionPreferencesDTO.setIncludeNewNumbers ( rs.getInt ( "INCLUDE_NEW_NUMBERS" ) ); sessionPreferencesDTO.setorderAllSkus ( rs.getInt ( "INCLUDE_ORDER_ALL" ) ); sessionPreferencesDTO.setReturnALLSKUs ( rs.getInt ( "INCLUDE_RETURN_ALL" ) ); sessionPreferencesDTO.setDollarLimit ( rs.getInt ( "DOLLAR_LIMIT" ) ); sessionPreferencesDTO.setApplicationPartsOnly ( rs.getInt ( "APPLICATION_PARTS_ONLY" ) ); sessionPreferencesDTO.setIncludeSupersede ( rs.getInt ( "INCLUDE_SELL_THRU" ) ); list.add ( sessionPreferencesDTO ); } return list; } /* * (non-Javadoc) * * @see com.napa.pulse.dao.interfaces.SessionDAO#updateSessionComment(int, * java.lang.String) */ @Override public void updateSessionComment ( int sessionId , String comment , String expirationDate ) throws Exception { jdbcTemplate.update ( "UPDATE PULSE_SESSION SET SESSION_COMMENT = ?, EXPIRATION_DATE=? WHERE SESSION_ID = ?" , comment , sdf.parse ( expirationDate ) , sessionId ); } /* * (non-Javadoc) * * @see * com.napa.pulse.dao.interfaces.SessionDAO#getSelectedProductGroupsTree( * int, java.lang.Integer) */ @Override public Map getSelectedProductTree ( Integer sessionId , Integer productGroupId , User user ) { if ( productGroupId <= 1 ) { Object[] hierarchyItemList = jdbcTemplate.queryForObject ( " SELECT CAST (MULTISET(select h.hierarchy_id, " + ( ( "en".equals ( user.getLanguage ( ) ) || user.getLanguage ( ) == null ) ? "HIERARCHY_LONG_NAME" : "HIERARCHY_LONG_NAME_FR" ) + ", " + " parent_id, HIERARCHY_LEVEL, IS_SELECTED, NVL(aco_enabled,0) aco_enabled from session_hierarchy_detail m " + " inner join PRODUCT_HIERARCHY h on h.hierarchy_id = m.hierarchy_id " + " and product_group_id = ? " + " and m.SESSION_ID = ? order by hierarchy_level, " + ( ( "en".equals ( user.getLanguage ( ) ) || user.getLanguage ( ) == null ) ? "HIERARCHY_LONG_NAME" : "HIERARCHY_LONG_NAME_FR" ) + ") AS HIERARCHY_VA) FROM DUAL" , new Object[]{productGroupId , sessionId} , new StructVarrayRowMapper ( ) ); return HierarchyUtils.getHierarchyTreeFromHierarchyVarray ( hierarchyItemList ); } else { Object[] hierarchyItemList = jdbcTemplate.queryForObject ( " SELECT CAST (MULTISET(select h.hierarchy_id, " + ( ( "en".equals ( user.getLanguage ( ) ) || user.getLanguage ( ) == null ) ? "HIERARCHY_LONG_NAME" : "HIERARCHY_LONG_NAME_FR" ) + ", parent_id, HIERARCHY_LEVEL, IS_SELECTED, NVL(aco_enabled,0) aco_enabled from PRODUCT_GROUP pg, " + "PRODUCT_GROUP_MAPPING pgm, PRODUCT_HIERARCHY h where " + "pg.product_group_id=pgm.product_group_id and pgm.hierarchy_id=h.hierarchy_id " + "and pg.product_group_id=? order by hierarchy_level, " + ( ( "en".equals ( user.getLanguage ( ) ) || user.getLanguage ( ) == null ) ? "HIERARCHY_LONG_NAME" : "HIERARCHY_LONG_NAME_FR" ) + ") AS HIERARCHY_VA) FROM DUAL" , new Object[]{productGroupId} , new StructVarrayRowMapper ( ) ); return HierarchyUtils.getHierarchyTreeFromHierarchyVarray ( hierarchyItemList ); } } @Override public TreeMap> getSelectedGroupCodes ( Integer sessionId , Integer productGroupId , User user ) { if ( productGroupId <= 1 ) { SqlRowSet rs = jdbcTemplate.queryForRowSet ( "select Distinct gc.FIELD_ABBR," + "LISTAGG(gc.GROUP_CODE||'-'||gc.GROUP_DESC,',') within group ( order by gc.GROUP_CODE) group_desc ," + "LISTAGG( gc.GROUP_CODE,',') within group ( order by gc.GROUP_CODE) group_code\n" + " from GROUP_CODE gc\n" + " Inner join session_group_code sgc on (sgc.session_id=" + sessionId + " and sgc.group_code=gc.group_code " + " and sgc.field_abbr=gc.field_abbr) \n" + " where ACTIVE_FLAG='Y' group by gc.FIELD_ABBR order by gc.FIELD_ABBR asc" ); HashMap> groupDesc = new HashMap<> ( ); while (rs.next ( )) { String desc = rs.getString ( "group_desc" ); String code = rs.getString ( "group_code" ); String field_abbr = rs.getString ( "FIELD_ABBR" ); List groupDescs = Arrays.asList ( desc.split ( "," ) ); List groupCodes = Arrays.stream ( code.split ( "," ) ) .map ( Integer :: parseInt ) .collect ( Collectors.toList ( ) ); List result = IntStream.range ( 0 , Math.min ( groupCodes.size ( ) , groupDescs.size ( ) ) ) .mapToObj ( i -> new GroupCodes ( groupCodes.get ( i ) , groupDescs.get ( i ) , field_abbr ) ) .collect ( Collectors.toList ( ) ); groupDesc.put ( field_abbr , result ); } TreeMap> sortedMap = new TreeMap<> ( groupDesc ); return sortedMap; } else { TreeMap> groupCodeProducts = productGroupDAO.getGroupCodesByGroup ( user , productGroupId ); return groupCodeProducts; } } @Override public List> getGroupCodesForSession ( Integer sessionId ) { return jdbcTemplate.queryForList ( "SELECT SGCD.PRODUCT_GROUP_NAME AS \"productGroupName\"," + " SGCD.PRODUCT_GROUP_ID AS \"productGroupId\", " + " max(SGCD.INCLUDE_ORDER)AS \"includeOrder\", " + " max(SGCD.INCLUDE_RETURN) AS \"includeReturn\" " + " FROM SESSION_GROUP_CODE_DETAIL SGCD " + " WHERE SGCD.SESSION_ID = ? " + " group by SGCD.PRODUCT_GROUP_ID, SGCD.PRODUCT_GROUP_NAME " + " ORDER BY SGCD.PRODUCT_GROUP_ID " , sessionId ); } @Override public List> getProductGroupsForSession ( Integer sessionId ) { return jdbcTemplate.queryForList ( "SELECT SH.PRODUCT_GROUP_NAME AS \"productGroupName\"," + " SH.PRODUCT_GROUP_ID AS \"productGroupId\", " + " max(SH.INCLUDE_ORDER) AS \"includeOrder\", " + " max(SH.INCLUDE_RETURN) AS \"includeReturn\" " + " FROM SESSION_HIERARCHY_detail SH " + " WHERE SH.SESSION_ID = ? " + " group by SH.PRODUCT_GROUP_ID, SH.PRODUCT_GROUP_NAME " + " ORDER BY SH.PRODUCT_GROUP_ID " , sessionId ); } @Override public List getFleetGroupsForSession ( Integer sessionId ) { return jdbcTemplate .queryForList ( "SELECT SF.FLEET_ID \"fleetGroupId\" " + " FROM SESSION_FLEET SF " + " WHERE SF.SESSION_ID = ? " , new Object[]{sessionId} ) .stream ( ).map ( ( item ) -> (BigDecimal) item.get ( "fleetGroupId" ) ).collect ( Collectors.toList ( ) ); } @Override public boolean isSessionFinalized ( Integer sessionId ) { LOGGER.debug ( "SessionDAOImpl.isSessionFinalized: " + sessionId ); int status = 0; try { status = jdbcTemplate.queryForObject ( "SELECT STATUS_ID FROM PULSE_SESSION WHERE SESSION_ID = ?" , new Object[]{sessionId} , Integer.class ); } catch ( Exception e ) { LOGGER.error ( "Session " + sessionId + " was not found in PULSE_SESSION table." ); } return status == 3; } @Override public List> getExecutiveSummaryData ( int sessionId ) { boolean isFinalized = isSessionFinalized ( sessionId ); return jdbcTemplate.queryForList ( "select SUM((CASE WHEN (NVL(MIN_STOCKING_QTY,0)=0) and RECOMMENDED_ORDER_QTY > 0 THEN 1 ELSE 0 END)) as suggestedForOrder, " + "SUM((CASE WHEN (NVL(MIN_STOCKING_QTY,0) > 0 and NVL(RECOMMENDED_RETURN_QTY,0) = 0) THEN 1 ELSE 0 END)) as recommendToStay, " + "SUM((CASE WHEN (NVL(MIN_STOCKING_QTY,0) > 0 and NVL(RECOMMENDED_RETURN_QTY,0) > 0) THEN 1 ELSE 0 END)) as recommededToReturn, " + "SUM((CASE WHEN (NVL(MIN_STOCKING_QTY,0)=0 and NVL(RECOMMENDED_ORDER_QTY,0) = 0) THEN 1 ELSE 0 END)) as notSuggestedOrStocked " + "from " + ( isFinalized ? " SESSION_SITE_PRODUCT_FINALIZED " : " SESSION_SITE_PRODUCT " ) + " WHERE SESSION_ID=? " + "and SITE_ID in (select SITE_ID from SESSION_SITE where SESSION_ID=?)" , sessionId , sessionId ); } @Override public Object[] validateSKUs ( SKUValidationDTO[] skusArray , Integer id , Boolean isCustomSelection ) throws Exception { Map skusMap; long start = System.currentTimeMillis ( ); try { if ( ! isCustomSelection ) { SqlRowSet selectSite = jdbcTemplate.queryForRowSet ( "select SITE_ID \"ID\" from SITE_GROUP_MAPPING where SITE_GROUP_ID=?" , id ); if ( selectSite.next ( ) ) { id = selectSite.getInt ( "ID" ); } } skusMap = new HashMap<> ( ); if ( skusArray != null && skusArray.length > 0 ) { Integer cslId = jdbcTemplate.queryForObject ( "SELECT NVL(MAX(ID)+1,1) FROM CUSTOM_SKU_LIST" , Integer.class );// cslId for inserting id for set of records in custom_sku_list table for (int i = 0; i < skusArray.length; i++) { jdbcTemplate.update ( "INSERT INTO CUSTOM_SKU_LIST (ID, ABBR, PART_NUMBER) VALUES(?,?,?)" , cslId , skusArray[i].getAbbr ( ) , skusArray[i].getPartNumber ( ) ); skusMap.put ( skusArray[i].getAbbr ( ) + " " + skusArray[i].getPartNumber ( ) , skusArray[i] ); } String sql = "SELECT product_id, hq_abbr, field_abbr, part_number, superseded_product_id, surviving_product_id FROM\n" + "(\n" + "SELECT DISTINCT PP.product_id,\n" + "PP.HQ_ABBR, PP.FIELD_ABBR, PP.PART_NUMBER, PP.SUPERSEDED_PRODUCT_ID, PP.SURVIVING_PRODUCT_ID ,\n" + "rank() OVER (PARTITION BY PPP.dc_id ORDER BY PPP.dc_id NULLS LAST) rk\n" + "FROM (SELECT P.*,SP.* FROM CUSTOM_SKU_LIST csl JOIN PRODUCT P ON (P.PART_NUMBER = csl.PART_NUMBER AND (P.FIELD_ABBR = csl.ABBR OR P.HQ_ABBR = csl.ABBR))\n" + "LEFT JOIN SUPERSEDE_PRODUCT SP ON P.PRODUCT_ID=SP.SUPERSEDED_PRODUCT_ID OR P.PRODUCT_ID=SP.SURVIVING_PRODUCT_ID\n" + "where csl.ID = " + cslId + "\n" + " ) PP \n" + " JOIN\n" + "(\n" + "SELECT dcs.dc_id, dcs.hq_abbr FROM site s\n" + " JOIN site dc ON CASE WHEN dc.SITE_TYPE_CODE = 'DC' AND s.PRODUCT_AVAILABILITY_ID = dc.PRODUCT_AVAILABILITY_ID then 1 \n " + " WHEN dc.SITE_TYPE_CODE = 'DC' AND s.MA_SERVICING_DC = dc.site_id THEN 1 \n" + " ELSE 0 \n" + " END =1 \n" + " JOIN dc_stocking_v dcs ON (dc.site_id = dcs.dc_id) \n " + "WHERE s.site_id = " + id + ")" + " PPP ON PP.HQ_abbr=PPP.hq_abbr \n" + ")\n" + "where rk = 1"; SqlRowSet rs = jdbcTemplate.queryForRowSet ( sql ); while (rs.next ( )) { String partNumber = rs.getString ( "PART_NUMBER" ); String hqAbbr = rs.getString ( "HQ_ABBR" ); String fieldAbbr = rs.getString ( "FIELD_ABBR" ); int productId = rs.getBigDecimal ( "PRODUCT_ID" ).intValue ( ); int supersededProductId = rs.getInt ( "SUPERSEDED_PRODUCT_ID" ); int survivingProductId = rs.getInt ( "SURVIVING_PRODUCT_ID" ); if ( skusMap.containsKey ( hqAbbr + " " + partNumber ) ) { processSKUData ( partNumber , hqAbbr , productId , supersededProductId , survivingProductId , skusMap ); } else if ( skusMap.containsKey ( fieldAbbr + " " + partNumber ) ) { processSKUData ( partNumber , fieldAbbr , productId , supersededProductId , survivingProductId , skusMap ); } } jdbcTemplate.update ( "DELETE FROM CUSTOM_SKU_LIST WHERE ID = " + cslId ); } } catch ( Exception e ) { LOGGER.error ( "Error in Custom SKU Validation: validateSKUs()" ); throw new Exception ( e ); } LOGGER.info ( "Custom SKU Validated Time taken: {} ms" , ( System.currentTimeMillis ( ) - start ) ); return skusMap.values ( ).toArray ( ); } public void setSkuData ( Map skusMap , SqlRowSet rs ) { String partNumber = rs.getString ( "PART_NUMBER" ); String hqAbbr = rs.getString ( "HQ_ABBR" ); String fieldAbbr = rs.getString ( "FIELD_ABBR" ); int productId = rs.getBigDecimal ( "PRODUCT_ID" ).intValue ( ); int supersede = rs.getInt ( "SUPERSEDED_PRODUCT_ID" ); int surviving = rs.getInt ( "SURVIVING_PRODUCT_ID" ); if ( skusMap.containsKey ( hqAbbr + " " + partNumber ) ) { processSKUData ( partNumber , hqAbbr , productId , supersede , surviving , skusMap ); } else if ( skusMap.containsKey ( fieldAbbr + " " + partNumber ) ) { processSKUData ( partNumber , fieldAbbr , productId , supersede , surviving , skusMap ); } } @Override public List> getCustomSkuSessionProducts ( int sessionId ) { return jdbcTemplate .queryForList ( "SELECT P.PRODUCT_ID, P.HQ_ABBR, P.PART_NUMBER FROM SESSION_PRODUCT SP, PRODUCT P " + " WHERE SESSION_ID = ? AND SP.PRODUCT_ID = P.PRODUCT_ID" , sessionId ); } @Override public List> getMinMaxSessionProducts ( Integer sites[] ) { String siteIds = Arrays.toString ( sites ).replace ( "[" , "" ).replace ( "]" , "" ); String sql = ( sites == null || sites.length == 0 ) ? "SELECT MM.LINE_ID as \"id\", MM.FIELD_ABBR as \"code\", 7 as \"leadTime\", 14 as \"orderCycle\", 1 as \"perCar\",1 as \"isSelected\", 1 as \"isOrderCycleEditOff\",1 as \"isLeadTimeEditOff\" FROM MIN_MAX_CALC_LINE MM where MM.active_flag = 1 order by mm.field_abbr asc" : "\n" + "select c.line_id as \"id\",\n" + " c.field_abbr as \"code\",\n" + " case\n" + " when nvl(c.lead_time_cnt,0)=0 then 7\n" + " when c.lead_time_cnt = 1\n" + " and (select distinct lead_time from min_max_site_preferences where site_id in (" + siteIds + ") and product_line_id=c.line_id) = 7\n" + " then 7\n" + " when c.lead_time_cnt = 1\n" + " and (select distinct lead_time from min_max_site_preferences where site_id in (" + siteIds + ") and product_line_id=c.line_id) <> 7\n" + " and (select count(distinct site_id) from min_max_site_preferences where site_id in (" + siteIds + ") and product_line_id=c.line_id)\n" + " = " + sites.length + "\n" + " then (select distinct lead_time from min_max_site_preferences where site_id in (" + siteIds + ") and product_line_id=c.line_id)\n" + " else -1\n" + " end as \"leadTime\",\n" + " case\n" + " when nvl(c.order_cycle_cnt,0)=0 then 14\n" + " when c.order_cycle_cnt = 1\n" + " and (select distinct order_cycle from min_max_site_preferences where site_id in (" + siteIds + ") and product_line_id=c.line_id)=14\n" + " then 14\n" + " when c.order_cycle_cnt = 1\n" + " and (select distinct order_cycle from min_max_site_preferences where site_id in (" + siteIds + ") and product_line_id=c.line_id) <> 14\n" + " and (select count(distinct site_id) from min_max_site_preferences where site_id in (" + siteIds + ") and product_line_id=c.line_id)\n" + " = " + sites.length + "\n" + " then (select distinct order_cycle from min_max_site_preferences where site_id in (" + siteIds + ") and product_line_id=c.line_id)\n" + " else -1\n" + " end as \"orderCycle\",\n" + " case\n" + " when nvl(c.per_car,0)=0 then 1\n" + " when c.per_car = 1\n" + " and (select distinct per_car from min_max_site_preferences where site_id in (" + siteIds + ") and product_line_id=c.line_id)=1\n" + " then 1\n" + " when c.per_car = 1\n" + " and (select distinct per_car from min_max_site_preferences where site_id in (" + siteIds + ") and product_line_id=c.line_id) <> 1\n" + " and (select count(distinct site_id) from min_max_site_preferences where site_id in (" + siteIds + ") and product_line_id=c.line_id)\n" + " = " + sites.length + "\n" + " then (select distinct per_car from min_max_site_preferences where site_id in (" + siteIds + ") and product_line_id=c.line_id)\n" + " else -1\n" + " end as \"perCar\",\n" + " case\n" + " when nvl(c.is_selected,0)=0 then 1\n" + " when c.is_selected = 1\n" + " and (select distinct is_selected from min_max_site_preferences where site_id in (" + siteIds + ") and product_line_id=c.line_id)=1\n" + " then 1\n" + " when c.is_selected = 1\n" + " and (select distinct is_selected from min_max_site_preferences where site_id in (" + siteIds + ") and product_line_id=c.line_id) <> 1\n" + " and (select count(distinct site_id) from min_max_site_preferences where site_id in (" + siteIds + ") and product_line_id=c.line_id)\n" + " = " + sites.length + "\n" + " then (select distinct is_selected from min_max_site_preferences where site_id in (" + siteIds + ") and product_line_id=c.line_id)\n" + " else -1\n" + " end as \"isSelected\", 1 as \"isOrderCycleEditOff\",1 as \"isLeadTimeEditOff\" \n" + "from (\n" + " select line_id,field_abbr,order_cycle_cnt,lead_time_cnt,per_car,is_selected\n" + " from\n" + " (select line_id,field_abbr\n" + " from min_max_calc_line l where l.active_flag=1) a\n" + " left outer join\n" + " (select distinct p.product_line_id,\n" + " count(distinct p.lead_time) lead_time_cnt,\n" + " count(distinct order_cycle) order_cycle_cnt,\n" + " count(distinct p.per_car) per_car,\n" + " count(distinct p.is_selected) is_selected\n" + " from min_max_site_preferences p where site_id in (" + siteIds + ")\n" + " group by product_line_id) b\n" + " on a.line_id=b.product_line_id) c\n" + "order by c.field_abbr"; return jdbcTemplate.queryForList ( sql ); } @Override public List> getMinMaxSessionSelectedProducts ( int sessionId ) { return jdbcTemplate.queryForList ( "select mmcl.LINE_ID as \"id\", mmcl.FIELD_ABBR as \"code\", 'true' as \"line\", mmspl.order_cycle as \"orderCycle\",mmspl.lead_time as \"leadTime\" ,case when mmspl.per_car = 1 then 'true' else 'false' end as \"perCar\", 'true' as \"isStockEditOff\" " + "from MIN_MAX_CALC_LINE mmcl, MIN_MAX_SESSION_PROD_LINE mmspl \n" + "where mmspl.SESSION_ID = " + sessionId + " and mmspl.PRODUCT_LINE_ID = mmcl.LINE_ID and mmcl.ACTIVE_FLAG = 1 order by mmcl.FIELD_ABBR" ); } @Override public List getMinMaxSessionSelectedProductsAsList ( int sessionId ) { List list = new ArrayList<> ( ); SqlRowSet rs = jdbcTemplate.queryForRowSet ( "select mmcl.LINE_ID as \"id\", mmcl.FIELD_ABBR as \"code\", 'true' as \"line\", mmspl.order_cycle as \"orderCycle\",mmspl.lead_time as \"leadTime\" , case when mmspl.per_car = 1 then 'true' else 'false' end as \"perCar\", 'true' as \"isStockEditOff\" " + "from MIN_MAX_CALC_LINE mmcl, MIN_MAX_SESSION_PROD_LINE mmspl \n" + "where mmspl.SESSION_ID = " + sessionId + " and mmspl.PRODUCT_LINE_ID = mmcl.LINE_ID and mmcl.ACTIVE_FLAG = 1 order by mmcl.FIELD_ABBR" ); while (rs.next ( )) { ProductLinesData obj = new ProductLinesData ( ); obj.setId ( rs.getInt ( "id" ) ); obj.setCode ( rs.getString ( "code" ) ); obj.setLine ( rs.getBoolean ( "line" ) ); obj.setStock ( rs.getInt ( "stock" ) ); obj.setPerCar ( rs.getInt ( "perCar" ) ); obj.setStockEditOff ( rs.getBoolean ( "isStockEditOff" ) ); list.add ( obj ); } return list; } @Override public String getMinMaxSessionAdditionalSettings ( int sessionId ) { StringJoiner sj = new StringJoiner ( ", " ); SqlRowSet rowSet = jdbcTemplate.queryForRowSet ( "SELECT MIN_MAX_SALES_SETTING as \"sales\", NVL(MIN_MAX_MIN_CALC_SETTING,0) as \"minCalc\" from PULSE_SESSION where SESSION_TYPE_CODE = 'MM' and SESSION_ID = " + sessionId ); if ( rowSet.next ( ) ) { String salesComment = jdbcTemplate.queryForObject ( "select SETTING_DESC from MIN_MAX_SESSION_SETTING where SETTING_TYPE = 'Sales' and SETTING_CODE = '" + rowSet.getString ( "sales" ) + "'" , String.class ); sj.add ( salesComment ); if ( rowSet.getInt ( "minCalc" ) > 0 ) { String minComment = jdbcTemplate.queryForObject ( "select SETTING_DESC from MIN_MAX_SESSION_SETTING where SETTING_TYPE = 'MIN' and SETTING_CODE = 'MINGTPERCAR'" , String.class ); sj.add ( minComment ); } } return sj.toString ( ); } @Override public List> getMinMaxSessionUnselectedProducts ( int sessionId ) { return jdbcTemplate.queryForList ( "select mmcl.LINE_ID as \"id\", mmcl.FIELD_ABBR as \"code\", 'false' as \"line\", 14 as \"orderCycle\",7 as \"leadTime\" , 'false' as \"perCar\", 'true' as \"isStockEditOff\" from MIN_MAX_CALC_LINE mmcl \n" + "where mmcl.ACTIVE_FLAG = 1 and mmcl.LINE_ID in (\n" + "select LINE_ID from MIN_MAX_CALC_LINE where LINE_ID not in (select PRODUCT_LINE_ID from MIN_MAX_SESSION_PROD_LINE where SESSION_ID = " + sessionId + ")\n" + "and ACTIVE_FLAG = 1\n" + ") order by mmcl.FIELD_ABBR" ); } @Override public ActionResult requestFinalizeSession ( int sessionId , RequestDatesDTO[] requestDatesArr , String messageText , User user ) { List sessionDetails = getSessionDetails ( user , sessionId ); SessionDetailsDTO session = sessionDetails.get ( 0 ); String sql = ""; jdbcTemplate.update ( "DELETE FROM SESSION_SEND_DATE WHERE SESSION_ID=?" , sessionId ); jdbcTemplate.batchUpdate ( "INSERT INTO SESSION_SEND_DATE (SESSION_ID, SITE_ID, SEND_RETURN_DT, SEND_ORDER_DT) values (?,?,?,?)" , new BatchPreparedStatementSetter ( ) { final List requestDates = Arrays.asList ( requestDatesArr ); @Override public void setValues ( PreparedStatement ps , int i ) throws SQLException { RequestDatesDTO requestDate = requestDates.get ( i ); ps.setInt ( 1 , sessionId ); ps.setInt ( 2 , requestDate.getSiteId ( ) ); try { if ( ! "".equals ( requestDate.getReturnDate ( ) ) ) { Date returnDt = sdf.parse ( requestDate.getReturnDate ( ) ); ps.setTimestamp ( 3 , new java.sql.Timestamp ( returnDt.getTime ( ) ) ); } else { ps.setDate ( 3 , null ); } if ( ! "".equals ( requestDate.getOrderDate ( ) ) ) { Date orderDt = sdf.parse ( requestDate.getOrderDate ( ) ); ps.setTimestamp ( 4 , new java.sql.Timestamp ( orderDt.getTime ( ) ) ); } else { ps.setDate ( 4 , null ); } } catch ( ParseException e ) { LOGGER.error ( "Error parsing request dates: " , e ); } } @Override public int getBatchSize ( ) { return requestDates.size ( ); } } ); // Change session status to "Pending Approval" jdbcTemplate.update ( "update PULSE_SESSION set SUBMITTAL_USER_ID = ?, STATUS_ID = 6 where SESSION_ID = ? AND STATUS_ID <> 3" , user.getUserId ( ) , sessionId ); jdbcTemplate.update ( "update PULSE_SESSION set EXPIRATION_DATE=EXPIRATION_DATE+7 where SESSION_ID=? AND SYSDATE>EXPIRATION_DATE-10" , sessionId ); // send message to appropriate user if ( "MM".equals ( session.getSessionTypeCode ( ) ) ) { sql = "SELECT user_id FROM pulse_user a, pulse_session b WHERE a.role_id IN (" + ROLE.ADMIN.getNumVal ( ) + "," + ROLE.MIN_MAX_REVIEW.getNumVal ( ) + ") AND b.session_id = ?"; } else { SqlRowSet rs = jdbcTemplate.queryForRowSet ("select config_value from pulse_config where config_code = 'Alert for Finalization'" ); String result = ""; while (rs.next ()) { result = rs.getString ( "config_value" ); } sql = "SELECT user_id " + "FROM " + "( " + "SELECT a.session_id, a.site_id session_site_id, b.user_id, COUNT(DISTINCT a.site_id) OVER (PARTITION BY a.session_id) site_count, c.site_id assigned_site_id, " + "SUM(DECODE(c.site_id, NULL, 1, 0)) OVER (PARTITION BY a.session_id, b.user_id) assigned " + ",ROW_NUMBER () OVER (PARTITION BY a.session_id, b.user_id ORDER BY b.user_id) rn " + "FROM session_site a, pulse_user b, " + "( " + "SELECT DISTINCT x.user_id, DECODE(x.site_or_group, 'G', y.site_id, 'S', x.access_id) site_id " + "FROM user_site_access x, site_group_mapping y " + "WHERE 1 = 1 AND x.access_type = 'R' " + "AND x.access_id = y.site_group_id (+) " + ") c " + "WHERE a.session_id = ? " + "AND b.user_id = c.user_id (+) " + "AND a.site_id = c.site_id (+) " + "AND b.role_id in ("+ result +") " + ") " + "WHERE assigned = 0 " + "AND rn = 1"; } return messageHandler.processPendingApprovalNotifications ( sessionId , user , jdbcTemplate.query ( sql , new IntegerRowMapper ( ) , sessionId ) , messageText ); } @Override public ActionResult rejectFinalizeSession ( int sessionId , String reasonText , int userId ) { Integer requestingUser = null; Date expirationDate = null; DateTime today = new DateTime ( ); int daysRemaining = 0; SqlRowSet rs = jdbcTemplate.queryForRowSet ( "select SUBMITTAL_USER_ID, EXPIRATION_DATE from PULSE_SESSION where status_id=6 and SESSION_ID=?" , sessionId ); while (rs.next ( )) { requestingUser = rs.getInt ( "SUBMITTAL_USER_ID" ); expirationDate = rs.getDate ( "EXPIRATION_DATE" ); } DateTime expDt = new DateTime ( expirationDate ); Days d = Days.daysBetween ( today , expDt ); int days = d.getDays ( ); if ( days < 10 ) { DateTime updatedExpDtTm = expDt.plusDays ( 10 ); Date updatedExpDt = expDt.plusDays ( 10 ).toDate ( ); jdbcTemplate.update ( "update PULSE_SESSION set EXPIRATION_DATE=? where SESSION_ID=?" , updatedExpDt , sessionId ); } else { daysRemaining = days; } // Change session status to "Pending Approval" jdbcTemplate.update ( "UPDATE PULSE_SESSION SET STATUS_ID = 1, LAST_MODIFIED_DATE = sysdate WHERE SESSION_ID = ? AND STATUS_ID = 6" , sessionId ); return messageHandler.processRejectFinalizeSessionNotifications ( sessionId , userId , reasonText , daysRemaining , requestingUser ); } private void processSKUData ( String partNumber , String abbr , Integer productId , int supersede , int surviving , Map skusMap ) { SKUValidationDTO skuObj = skusMap.get ( abbr + " " + partNumber ); skuObj.setStatus ( 1 ); skuObj.setProductId ( productId ); skuObj.setIsSupersede ( ( productId == supersede ) ? 1 : 0 ); skuObj.setIsSurviving ( ( productId == surviving ) ? 1 : 0 ); } @Override public SessionModificationInfoDTO wasSessionModified ( int sessionId ) { SessionModificationInfoDTO modified = jdbcTemplate.queryForObject ( "select " + "max(case when last_refresh_date < parameter_update_dt and NVL(ssp.OFFSET,0) != NVL(sp.OFFSET,0) then 1 " + "when last_refresh_date < parameter_update_dt and ssp.STOCK_IT != sp.STOCK_IT then 1 " + "when last_refresh_date < parameter_update_dt and NVL(ssp.ignore_stock, 0) != NVL(sp.ignore_stock, 0) then 1 else 0 end) as \"wasSessionModified\", TO_CHAR(CAST(max(parameter_update_dt) AS TIMESTAMP WITH TIME ZONE), 'YYYY-MM-DD\"T\"HH24:MI:SS.FF3TZHTZM') as \"maxUpdateDate\" " + "from SESSION_DECOUPLED_PARAMETER ssp, site_parameter sp " + "where ssp.session_id = ? and ssp.site_id=sp.site_id " + "and ssp.hierarchy_id=sp.hierarchy_id and ssp.hierarchy_level = 4 " , new Object[]{sessionId} , new BeanPropertyRowMapper<> ( SessionModificationInfoDTO.class ) ); if ( modified.getWasSessionModified ( ) != null && modified.getWasSessionModified ( ) == 1 ) { List siteInfo = jdbcTemplate.query ( "select s.site_id as \"siteId\", s.site_name as \"siteName\", sum(case when ssp.last_refresh_date < sp.parameter_update_dt and NVL(ssp.OFFSET,0) != NVL(sp.OFFSET,0) then 1 else 0 end) as \"sliderChangeCount\", " + "sum(case when ssp.last_refresh_date < sp.parameter_update_dt and NVL(ssp.ignore_stock, 0) != NVL(sp.ignore_stock, 0) then 1 else 0 end) as \"ispChangeCount\" " + "from session_decoupled_parameter ssp, site_parameter sp, site s " + "where s.site_id = sp.site_id and ssp.session_id = ? and ssp.site_id=sp.site_id " + "and ssp.hierarchy_id=sp.hierarchy_id and ssp.hierarchy_level = 4 " + "group by s.site_id, s.site_name" , new Object[]{sessionId} , new BeanPropertyRowMapper<> ( SiteModificationInfoDTO.class ) ); modified.setSiteCounts ( siteInfo ); } return modified; } @Override public void updateSession ( int sessionId ) throws Exception { List workerList = new ArrayList<> ( ); final int DB_THREADS = 8; ExecutorService threadPool = Executors.newFixedThreadPool ( DB_THREADS ); CompletionService pool = new ExecutorCompletionService<> ( threadPool ); Callable worker; String sql = "select distinct sdp.site_id " + "from pulse_session ps, SESSION_DECOUPLED_PARAMETER sdp, site_parameter sp " + "where ps.session_id=? and ps.STATUS_ID in (1,2,4,5,6) and sdp.session_id=ps.session_id " + "and sdp.site_id=sp.site_id and sdp.hierarchy_id=sp.hierarchy_id " + "and nvl(last_refresh_date, created_date) < parameter_update_dt "; SqlRowSet rs = jdbcTemplate.queryForRowSet ( sql , sessionId ); while (rs.next ( )) { int siteId = rs.getInt ( "SITE_ID" ); worker = new UpdateSessionCallable ( sessionId , siteId , jdbcTemplate ); pool.submit ( worker ); workerList.add ( "" ); } for (int i = 0; i < workerList.size ( ); i++) { Future future = pool.take ( ); Exception result = future.get ( ); LOGGER.info ( "Update Session| Got result back for sessionId " + sessionId + ". Thread " + i ); if ( result != null ) { List remaining = threadPool.shutdownNow ( ); LOGGER.info ( "Update Session| Shutting down pool. Remaining: " + remaining.size ( ) ); threadPool.awaitTermination ( 60 , TimeUnit.SECONDS ); throw result; } } } @Override public void updateSession ( int sessionId , int siteId , int hierarchyId ) throws Exception { Callable worker = new UpdateSessionCallable ( sessionId , siteId , jdbcTemplate ); Exception exception = worker.call ( ); if ( exception != null ) { throw exception; } } private static class UpdateSessionCallable implements Callable { private final int sessionId; private final int siteId; private final JdbcTemplate jdbcTemplate; UpdateSessionCallable ( int sessionId , int siteId , JdbcTemplate jdbcTemplate ) { this.sessionId = sessionId; this.siteId = siteId; this.jdbcTemplate = jdbcTemplate; } @Override public Exception call ( ) throws Exception { try { LOGGER.debug ( "UpdateSessionCallable for sessionId: " + sessionId + " siteId: " + siteId ); jdbcTemplate.update ( "CALL UPDATE_SESS_FROM_PARAM (?,?)" , sessionId , siteId ); jdbcTemplate.update ( "CALL RECALC_SESSION_TOTALS (?,?)" , sessionId , siteId ); } catch ( Exception e ) { LOGGER.error ( "Error running UpdateSessionCallable for sessionId: " + sessionId + " siteId: " + siteId + e.getMessage ( ) ); return e; } return null; } } @Override public List getSessionTransmissionStatus ( Integer sessionId ) { List sessionTransmissionStatusList = new ArrayList<> ( ); SqlRowSet rs = jdbcTemplate.queryForRowSet ( "select SITE_NAME, " + "(select TRANSMISSION_STATUS_CD from TRANSMISSION_STATUS where TRANSMISSION_STATUS_ID=ORDER_STATUS_ID) as orderStatusCode, " + "(select TRANSMISSION_STATUS_CD from TRANSMISSION_STATUS where TRANSMISSION_STATUS_ID=RETURN_STATUS_ID) as returnStatusCode " + "from session_site ss " + "inner join site s on ss.site_id=s.site_id " + "left join transmission t on ss.session_id=t.session_id and ss.site_id=t.site_id " + "where ss.session_id=? order by SITE_NAME" , sessionId ); while (rs.next ( )) { SessionTransmissionStatus sessionTransmissionStatus = new SessionTransmissionStatus ( ); sessionTransmissionStatus.setSiteName ( rs.getString ( "SITE_NAME" ) ); sessionTransmissionStatus.setOrderStatusCode ( rs.getString ( "orderStatusCode" ) ); sessionTransmissionStatus.setReturnStatusCode ( rs.getString ( "returnStatusCode" ) ); sessionTransmissionStatusList.add ( sessionTransmissionStatus ); } return sessionTransmissionStatusList; } @Override public BigDecimal recreateSession ( User user , Integer sessionId , String userAccessSites ) throws Exception { List sessionPreferences = sessionReviewPreferenceSettings ( sessionId ); List sessionDetails = getSessionDetails ( user , sessionId ); if ( sessionPreferences.size ( ) == 1 && sessionDetails.size ( ) == 1 ) { SessionPreferencesDTO sessionPreference = sessionPreferences.get ( 0 ); List> sitesInfo = this.getSessionSites ( sessionId ); List sites = new ArrayList<> ( ); Set hierarchies = new HashSet<> ( ); String siteGroupName = "Custom Group"; for (Map siteInfo : sitesInfo) { int site = ( (BigDecimal) siteInfo.get ( "siteId" ) ).intValue ( ); if ( siteInfo.get ( "siteGroupName" ) != null ) { siteGroupName = siteInfo.get ( "siteGroupName" ).toString ( ); } Map hierarchy = getHierarchyTreeForSession ( sessionId , site , user ); sites.add ( site ); for (Integer hierarchyId : hierarchy.keySet ( )) { hierarchies.add ( hierarchyId.toString ( ) ); } } CustomProductGroup customProducts = null; if ( "Y".equals ( sessionPreference.getCreatedByCustomGroup ( ) ) ) { customProducts = new CustomProductGroup ( ); customProducts.setHierarchyIds ( hierarchies.toArray ( new String[]{} ) ); customProducts.setOrders ( true ); customProducts.setReturns ( true ); } Integer[] customSkus = null; List> customSkuInfo = getCustomSkuSessionProducts ( sessionId ); if ( customSkuInfo.size ( ) > 0 ) { List customSkuList = new ArrayList<> ( ); for (Map customSkuRow : customSkuInfo) { customSkuList.add ( ( (BigDecimal) customSkuRow.get ( "PRODUCT_ID" ) ).intValue ( ) ); } customSkus = customSkuList.toArray ( new Integer[]{} ); } ProductLinesData[] productLineIds = null; List ProductLineIdInfo = getMinMaxSessionSelectedProductsAsList ( sessionId ); if ( ProductLineIdInfo.size ( ) > 0 ) { productLineIds = ProductLineIdInfo.toArray ( new ProductLinesData[]{} ); } ProductLinesData[] allProductLines = null; List allProductLineIdInfo = getMinMaxSessionSelectedProductsAsList ( sessionId ); if ( ProductLineIdInfo.size ( ) > 0 ) { allProductLines = allProductLineIdInfo.toArray ( new ProductLinesData[]{} ); } Calendar calendar = Calendar.getInstance ( ); calendar.roll ( Calendar.DATE , 30 ); List productGroups = new ArrayList<> ( ); List> productInfo = this.getProductGroupsForSession ( sessionId ); for (Map productRow : productInfo) { ProductGroupOrderReturn pgor = new ProductGroupOrderReturn ( ( (BigDecimal) productRow.get ( "productGroupId" ) ).intValue ( ) , "1".equals ( productRow.get ( "includeOrder" ) ) , "1".equals ( productRow.get ( "includeReturn" ) ) ); pgor.setProductGroupName ( (String) productRow.get ( "productGroupName" ) ); productGroups.add ( pgor ); } return this.createNewSession ( user , sessionPreference.getPerCarOrder ( ) == 1 , sessionPreference.getIncludeFleets ( ) == 1 , sessionPreference.getIncludeSystems ( ) == 1 , sessionPreference.getIncludeSpecials ( ) == 1 , sessionPreference.getIncludeObsoletes ( ) == 1 , sessionPreference.getIncludeOverstocks ( ) == 1 ,sessionPreference.getIncludeSupersede() == 1,false,false, sessionPreference.getIncludeLessThanStdPkg ( ) == 1 ,sessionPreference.getIncludeWeekSupplyOption( ) ,sessionPreference.getWeekSupplyOptionName( ), sessionPreference.getUseInvestmentCollections ( ) == 1 , sessionPreference.getProvideStoreDepth ( ) == 1 , sessionPreference.getIncludeSoldNotStocked ( ) == 1 , sessionPreference.getIncludeNewNumbers ( ) == 1 , sessionPreference.getorderAllSkus ( ) == 1 , sessionPreference.getReturnALLSKUs ( ) == 1 , sessionPreference.getDollarLimit ( ) , sessionPreference.getComments ( ) , null /* siteGroupId */ , siteGroupName , sites.toArray ( new Integer[]{} ) , productGroups.toArray ( new ProductGroupOrderReturn[]{} ) , productGroups.toArray ( new ProductGroupOrderReturn[]{} ) , getFleetGroupsForSession ( sessionId ).stream ( ).mapToInt ( BigDecimal :: intValue ).boxed ( ) .collect ( Collectors.toList ( ) ).toArray ( new Integer[]{} ) , customProducts , null , false , sessionPreference.getSessionDescription ( ) , userAccessSites , customSkus , productLineIds , allProductLines , sessionPreference.getIncludeOrders ( ) , sessionPreference.getIncludeReturns ( ) , calendar.getTime ( ) , sessionPreference.getIncludeDaysCount ( ) , sessionPreference.getPerCarMinMaxValue ( ) , sessionPreference.getSalesHistoryValue ( ) , sessionPreference.getDisplayQty ( ) , "SS" , sessionPreference.getIncludeHubSpokeOrderOption ( ) , sessionPreference.getIncludeHubSpokeReturnOption ( ) , sessionId.longValue ( ) , sessionPreference.getApplicationPartsOnly ( ) == 1 ); } throw new Exception ( "Session not found" ); } @Override public ActionResult declineOrderReturns ( User user , Integer sessionId , Integer siteId , Integer hierarchyId , Boolean declineOrder , Boolean declineReturn ) throws PulseException { ActionResult result = null; boolean returnCode = siteQueueMap.canStart ( siteId , "declineOrderReturns" ); if ( ! returnCode ) { throw new PulseException ( PulseConstants.OPERATION_TIMEOUT ); } else { try { int declineOrderFinal, declineReturnFinal; if ( declineOrder != null ) { declineOrderFinal = ( ! declineOrder ) ? 0 : 1; String sql = "merge into DECLINE_ORDER_RETURN d using (" + " SELECT ? session_id, ? site_id, ? hierarchy_id, ? decline_order " + " FROM dual ) x" + " ON (d.session_id=x.session_id AND d.site_id=x.site_id AND d.hierarchy_id = x.hierarchy_id )" + " WHEN MATCHED THEN UPDATE " + " SET d.decline_order = x.decline_order " + " WHEN NOT MATCHED THEN " + " INSERT (d.SESSION_ID, d.SITE_ID, d.HIERARCHY_ID, d.DECLINE_ORDER ) values (?, ?, ?, ? )"; jdbcTemplate.update ( sql , sessionId , siteId , hierarchyId , declineOrderFinal , sessionId , siteId , hierarchyId , declineOrderFinal ); } if ( declineReturn != null ) { declineReturnFinal = ( declineReturn == null || ! declineReturn ) ? 0 : 1; String sql = "merge into DECLINE_ORDER_RETURN d using (" + " SELECT ? session_id, ? site_id, ? hierarchy_id, ? decline_return " + " FROM dual ) x" + " ON (d.session_id=x.session_id AND d.site_id=x.site_id AND d.hierarchy_id = x.hierarchy_id )" + " WHEN MATCHED THEN UPDATE " + " SET d.decline_return=x.decline_return" + " WHEN NOT MATCHED THEN " + " INSERT (d.SESSION_ID, d.SITE_ID, d.HIERARCHY_ID, d.DECLINE_RETURN ) values (?, ?, ?, ? )"; jdbcTemplate.update ( sql , sessionId , siteId , hierarchyId , declineReturnFinal , sessionId , siteId , hierarchyId , declineReturnFinal ); } if ( declineOrder != null ) { // call DECLINE_ORDERS_RETURNS SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall ( jdbcTemplate ) .withProcedureName ( "DECLINE_ORDERS" ); Map inParamMap = new HashMap<> ( ); inParamMap.put ( "SESSIONID_IN" , sessionId ); inParamMap.put ( "SITEID_IN" , siteId ); inParamMap.put ( "HIERARCHYID_IN" , hierarchyId ); inParamMap.put ( "DECLINE_ORDER_IN" , declineOrder ); inParamMap.put ( "UPDATE_HQ_VALUES_IN" , user.isStoreUser ( ) ? 0 : 1 ); SqlParameterSource in = new MapSqlParameterSource ( inParamMap ); simpleJdbcCall.execute ( in ); // call is made to the procedure to recalculate the totals. reCalculateSessionTotals ( sessionId , siteId ); result = new ActionResult ( true ); } if ( declineReturn != null ) { // call DECLINE_ORDERS_RETURNS SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall ( jdbcTemplate ) .withProcedureName ( "DECLINE_RETURNS" ); Map inParamMap = new HashMap<> ( ); inParamMap.put ( "SESSIONID_IN" , sessionId ); inParamMap.put ( "SITEID_IN" , siteId ); inParamMap.put ( "HIERARCHYID_IN" , hierarchyId ); inParamMap.put ( "DECLINE_RETURN_IN" , declineReturn ); inParamMap.put ( "UPDATE_HQ_VALUES_IN" , user.isStoreUser ( ) ? 0 : 1 ); SqlParameterSource in = new MapSqlParameterSource ( inParamMap ); simpleJdbcCall.execute ( in ); // call is made to the procedure to recalculate the totals. reCalculateSessionTotals ( sessionId , siteId ); result = new ActionResult ( true ); } } finally { siteQueueMap.removeHead ( siteId , "declineOrderReturns" ); } } return result; } @Override public boolean revertDeclineOrderReturns ( int sessionId ) { SqlRowSet rs = jdbcTemplate.queryForRowSet ( "select SITE_ID, HIERARCHY_ID, DECLINE_ORDER, DECLINE_RETURN from DECLINE_ORDER_RETURN where session_id=?" , sessionId ); while (rs.next ( )) { int siteId = rs.getInt ( "SITE_ID" ); int hierarchyId = rs.getInt ( "HIERARCHY_ID" ); int declineOrder = rs.getInt ( "DECLINE_ORDER" ); int declineReturn = rs.getInt ( "DECLINE_RETURN" ); try { SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall ( jdbcTemplate ) .withProcedureName ( "REVERT_ORDER_RETURN" ); Map inParamMap = new HashMap<> ( ); inParamMap.put ( "SESSIONID_IN" , sessionId ); inParamMap.put ( "SITEID_IN" , siteId ); inParamMap.put ( "HIERARCHYID_IN" , hierarchyId ); inParamMap.put ( "DECLINE_ORDER_IN" , declineOrder ); inParamMap.put ( "DECLINE_RETURN_IN" , declineReturn ); SqlParameterSource in = new MapSqlParameterSource ( inParamMap ); simpleJdbcCall.execute ( in ); // call is made to the procedure to recalculate the totals. reCalculateSessionTotals ( sessionId , siteId ); } catch ( Exception e ) { LOGGER.error ( "Error processing declineOrderReturns: " , e ); return false; } } return true; } @Override @Async public void createDeferredSessionImmediately ( int sessionId ) { jdbcTemplate.update ( "call DEFERRED_SESSIONS.CREATE_IMMEDIATELY (?)" , sessionId ); } @Override public SessionLimit getSessionlimits ( User user ) { SessionLimit sl = new SessionLimit ( ); int limitOpenSess = jdbcTemplate.queryForObject ( "select CONFIG_VALUE from PULSE_CONFIG where CONFIG_CODE='LIMIT_OPEN_SESSIONS'" , Integer.class ); int limitOpenSite = jdbcTemplate.queryForObject ( "select CONFIG_VALUE from PULSE_CONFIG where CONFIG_CODE='LIMIT_OPEN_SITE'" , Integer.class ); SqlRowSet rs = jdbcTemplate.queryForRowSet ( "select SESSION_ID " + "from pulse_session where STATUS_ID not in (3, 7, 13) " + "and CREATED_USER_ID=? " , user.getUserId ( ) ); while (rs.next ( )) { sl.setOpenSessions ( rs.getInt ( "SESSION_ID" ) ); } if ( sl.getOpenSessionCnt ( ) >= limitOpenSess ) { sl.setLimitReached ( true ); } sl.setSessionLimit ( limitOpenSess ); sl.setSiteLimitCnt ( limitOpenSite ); return sl; } @Override public String getEasyLink ( String key , String type ) { String link = jdbcTemplate.queryForObject ( "SELECT RESOURCE_URL FROM PULSE_TRAINING_RESOURCE WHERE RESOURCE_TITLE=? AND RESOURCE_GROUP=?" , String.class , key , type ); return link; } @Override public List getSiteLimits ( Integer[] sitesArray ) { List slist = new ArrayList<> ( ); StringJoiner sites = new StringJoiner ( "," ); for (Integer s : sitesArray) { sites.add ( s.toString ( ) ); } int siteLimit = jdbcTemplate.queryForObject ( "select CONFIG_VALUE from PULSE_CONFIG where CONFIG_CODE='LIMIT_OPEN_SITE'" , Integer.class ); SqlRowSet rs = jdbcTemplate.queryForRowSet ( "select ss.session_id, ss.site_id, site_name, p.CREATED_USER_ID, FIRST_NAME, LAST_NAME " + "from session_site ss, pulse_session p, pulse_user u, site s where ss.session_id=p.session_id and p.CREATED_USER_ID=u.user_id " + "and s.site_id=ss.site_id and STATUS_ID not in (3, 7, 13) and ss.site_id in (" + "select site_id from (" + "select site_id, count(site_id) as siteCnt from session_site ss, pulse_session ps " + "where site_id in (" + sites + ") and ss.session_id=ps.session_id " + "and STATUS_ID not in (3, 7, 13) " + "group by site_id) where siteCnt >= " + siteLimit + ")" ); while (rs.next ( )) { SiteLimit sl = new SiteLimit ( ); sl.setSessionId ( rs.getInt ( "session_id" ) ); sl.setSiteId ( rs.getInt ( "site_id" ) ); sl.setSiteName ( rs.getString ( "site_name" ) ); sl.setFirstName ( rs.getString ( "FIRST_NAME" ) ); sl.setLastName ( rs.getString ( "LAST_NAME" ) ); slist.add ( sl ); } return slist; } @Override public Integer[] getGroupSites ( int siteGroupObj ) { List s = new ArrayList<> ( ); SqlRowSet rs = jdbcTemplate.queryForRowSet ( "select SITE_ID from SITE_GROUP_MAPPING where SITE_GROUP_ID=?" , siteGroupObj ); while (rs.next ( )) { s.add ( rs.getInt ( "SITE_ID" ) ); } return s.toArray ( new Integer[0] ); } @Override public Boolean isACO ( int sessionId ) { String sql = "select decode(session_type_code, 'AC', 1, 0) is_aco " + "from pulse_session " + "where session_id = ?"; try { return jdbcTemplate.queryForObject ( sql , Boolean.class , sessionId ); } catch ( EmptyResultDataAccessException e ) { throw new RuntimeException ( "Session " + sessionId + " does not exist" ); } } public Boolean isStandard ( int sessionId ) { String sql = "select decode(session_type_code, 'SS', 1, 0) is_standard " + "from pulse_session " + "where session_id = ?"; try { return jdbcTemplate.queryForObject ( sql , Boolean.class , sessionId ); } catch ( EmptyResultDataAccessException e ) { throw new RuntimeException ( "Session " + sessionId + " does not exist" ); } } @Override public Boolean isMinMax ( int sessionId ) { String sql = "select decode(session_type_code, 'MM', 1, 0) is_minmax " + "from pulse_session " + "where session_id = ?"; try { return jdbcTemplate.queryForObject ( sql , Boolean.class , sessionId ); } catch ( EmptyResultDataAccessException e ) { throw new RuntimeException ( "Session " + sessionId + " does not exist" ); } } @Override public List getSitesFromSession ( int sessionId ) { String sql = "select site_id " + "from session_site " + "where session_id = ?"; return jdbcTemplate.queryForList ( sql , Integer.class , sessionId ); } @Override public Integer getSessionStatus ( int sessionId ) { String sql = "select status_id " + "from pulse_session " + "where session_id = ?"; return jdbcTemplate.queryForObject ( sql , Integer.class , sessionId ); } @Override public void markSessionStatusExpired ( int sessionId ) { String sql = "update pulse_session " + "set " + " status_id = 7, " + " expiration_date = sysdate " + "where " + " session_id = ?"; jdbcTemplate.update ( sql , sessionId ); } @Override public void markSessionStatusDeleted ( int sessionId ) { String sql = "update pulse_session " + "set " + " status_id = 13 " + "where " + " session_id = ?"; jdbcTemplate.update ( sql , sessionId ); } @Override public void insertDeletedSessionLog ( int sessionId , int siteId , int userId ) { String sql = "insert into deleted_session_log " + " (session_id, site_id, deleted_by_user, deleted_dt) " + "values " + " (?, ?, ?, sysdate)"; jdbcTemplate.update ( sql , sessionId , siteId , userId ); } @Override public ActionResult getMinMaxSafetyStock ( ) { ActionResult item = new ActionResult ( ); item.setData ( jdbcTemplate.queryForList ( "SELECT SAFETY_STOCK AS \"key\", TO_CHAR(SAFETY_STOCK) AS \"label\" FROM MIN_MAX_SAFETY_STOCK WHERE IS_ACTIVE = 1 ORDER BY \"key\"" ) ); return item; } @Override public ActionResult getMinMaxOrderCycle ( ) { ActionResult item = new ActionResult ( ); item.setData ( jdbcTemplate.queryForList ( "SELECT ORDER_CYCLE AS \"key\", TO_CHAR(ORDER_CYCLE) AS \"label\" FROM MIN_MAX_ORDER_CYCLE WHERE IS_ACTIVE = 1 ORDER BY \"key\"" ) ); return item; } @Override public ActionResult getMinMaxLeadTime ( ) { ActionResult item = new ActionResult ( ); item.setData ( jdbcTemplate.queryForList ( "SELECT LEAD_TIME AS \"key\", TO_CHAR(LEAD_TIME) AS \"label\" FROM MIN_MAX_LEAD_TIME WHERE IS_ACTIVE = 1 ORDER BY \"key\"" ) ); return item; } @Override public List> getDropdownSites ( List accessSites ) { List> result = new ArrayList<> ( ); Map defaultSelection = new HashMap<> ( ); defaultSelection.put ( "key" , 0 ); defaultSelection.put ( "label" , "Default selection" ); result.add ( defaultSelection ); try { String sites = ""; if ( accessSites.size ( ) < 1000 && accessSites.size ( ) > 0 ) { String commaSeparatedSites = accessSites.stream ( ) .map ( site -> String.valueOf ( site.getSiteId ( ) ) ) .collect ( Collectors.joining ( "," ) ); sites = " where mmsp.site_id in (" + commaSeparatedSites + ")"; } String sql = "select s.SITE_NAME as \"label\", s.SITE_ID as \"key\" from SITE s where s.SITE_ID in (select distinct site_id from MIN_MAX_SITE_PREFERENCES mmsp " + sites + ") order by s.SITE_NAME"; result.addAll ( jdbcTemplate.queryForList ( sql ) ); } catch ( Exception e ) { LOGGER.error ( "error in geDropdownSite(): {}" , e.getMessage ( ) ); } return result; } @Override public boolean pulseDownTime ( ) { DateTime today = new DateTime ( ); List str= new ArrayList<> (); try { String sql = " select config_desc , config_value FROM pulse_config where config_code = 'Pulse_DownTime' "; SqlRowSet rs = jdbcTemplate.queryForRowSet ( sql ); while (rs.next ( )) { str.add ( rs.getString ( "config_value" )) ; } DateFormatSymbols dfs = new DateFormatSymbols (); String weekdays[] = dfs.getWeekdays(); Calendar calendar = Calendar.getInstance(); int dayOfWeek = calendar.get(Calendar.DAY_OF_WEEK); //GregorianCalendar gregorianCalendar = (GregorianCalendar) GregorianCalendar.getInstance (); int currentHour = calendar.get(calendar.HOUR_OF_DAY); String currentDay = weekdays[dayOfWeek].toLowerCase (); String shutDownDayHour=str.stream().map(Object::toString).collect(Collectors.joining(",")); GsonJsonParser jsonParser = new GsonJsonParser (); Set> setValues = jsonParser.parseMap ( shutDownDayHour).entrySet (); for(Map.Entry entry : setValues) { String startTime= entry.getValue ( ).toString ().split ( "-" )[0]; String endTime= entry.getValue ( ).toString ().split ( "-" )[1]; String shutDownDay = entry.getKey (); if(currentDay.equals (shutDownDay)) { return currentHour>= Integer.parseInt (startTime) && currentHour<= Integer.parseInt (endTime) ? false : true; } } }catch (Exception e ){ throw new RuntimeException (e.getMessage ()); } return true; } @Override public void reCalculateSessionTotals ( Integer sessionId , Integer siteId ) { try { Map inParamMap = new HashMap<> ( ); inParamMap.put ( "SESSIONID_IN" , sessionId ); inParamMap.put ( "SITEID_IN" , siteId ); SqlParameterSource in = new MapSqlParameterSource ( inParamMap ); // call is made to the procedure to recalculate the totals. SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall ( jdbcTemplate ).withProcedureName ( "RECALC_SESSION_TOTALS" ); simpleJdbcCall.execute ( in ); } catch ( Exception e ) { LOGGER.error ( "Error in reCalculateSessionTotals(): {}" , e.getMessage ( ) ); } } }