package com.napa.pulse.service.impl;

import com.napa.pulse.dao.interfaces.ExportColumnNameDAO;
import com.napa.pulse.dao.interfaces.FleetDAO;
import com.napa.pulse.dto.SKUTableWidgetData;
import com.napa.pulse.entity.pulseui.ExportColumn;
import com.napa.pulse.entity.pulseui.MajorAccount;
import com.napa.pulse.entity.pulseui.TranslationItem;
import com.napa.pulse.entity.security.User;
import com.napa.pulse.repository.ExportColumnNameRepository;
import com.napa.pulse.repository.MajorAccountRepository;
import com.napa.pulse.repository.TranslationItemRepository;
import com.napa.pulse.service.interfaces.ExportService;
import oracle.jdbc.driver.OracleConnection;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.Date;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;
import java.util.stream.Stream;
import java.util.stream.StreamSupport;

@Service
public class ExportServiceImpl implements ExportService {

    private static final Logger LOGGER = LoggerFactory.getLogger(ExportServiceImpl.class);

    // one million rows will keep us under Excel's max of 1,048,576 rows
    private static final int MAX_NUM_ROWS = 1000000;

    private final SimpleDateFormat sdf = new SimpleDateFormat("MM/dd/yy");

    @Autowired
    private TranslationItemRepository translationItemRepository;

    @Autowired
    private ExportColumnNameDAO exportColumnNameDAO;

    @Autowired
    private ExportColumnNameRepository exportColumnRepository;

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private MajorAccountRepository majorAccountRepository;

    @Autowired
    private FleetDAO fleetDAO;

    private Map<String, Map<String, List<String>>> headersByReportTypeAndLanguage = new HashMap<>();

    private void buildDynamicExport(User user, HttpServletResponse response, Iterable<ExportColumn> selectedColumns,
                                    ResultSet rs) throws IOException {
        SXSSFWorkbook workbook = new SXSSFWorkbook(100);
        workbook.setCompressTempFiles(true);
        buildDynamicExport(user, response, selectedColumns, workbook, null, rs);
        workbook.write(response.getOutputStream());
        response.getOutputStream().flush();
        workbook.close();
        workbook.dispose();
    }

    private void buildDynamicExport(User user, HttpServletResponse response, Iterable<ExportColumn> selectedColumns,
                                    SXSSFWorkbook workbook, String sheetName, ResultSet rs) throws IOException {
        int[] columnWidths = StreamSupport.stream(selectedColumns.spliterator(), false)
                .map(ExportColumn::getWidth).mapToInt((item) -> item).toArray();
        Map<WorkbookStyle, CellStyle> styles = createWorkbookStyles(workbook);

        Sheet sheet = createNewSheet(user, selectedColumns, workbook, columnWidths, sheetName);
        sheet.setAutoFilter(new CellRangeAddress(0, 0, 0, columnWidths.length - 1));
        int rowNum = 1; // we've already written 0 as the headers
        try {
            while (rs.next()) {

                Row row = sheet.createRow(rowNum);
                int cellNum = 0;
                for (ExportColumn column : selectedColumns) {
                    try {
                        switch (JDBCType.valueOf(column.getColumnType())) {
                            case NUMERIC:
                                if (column.getDataFormat() == 9) {
                                    float floatVal = rs.getFloat(column.getColumnName());
                                    if (rs.wasNull()) {
                                        createEmptyCell(row, cellNum++);
                                    } else {
                                        createCell(row, floatVal, cellNum++,
                                                styles.get(WorkbookStyle.fromDataFormat(column.getDataFormat())));
                                    }
                                } else {
                                    int intVal = rs.getInt(column.getColumnName());
                                    if (rs.wasNull()) {
                                        createEmptyCell(row, cellNum++);
                                    } else {
                                        createCell(row, intVal, cellNum++,
                                                styles.get(WorkbookStyle.fromDataFormat(column.getDataFormat())));
                                    }
                                }
                                break;
                            case VARCHAR:
                                createCell(row, rs.getString(column.getColumnName()), cellNum++,
                                        styles.get(WorkbookStyle.fromDataFormat(column.getDataFormat())));
                                break;
                            case DATE:
                                Date date = rs.getDate(column.getColumnName());
                                if (date == null) {
                                    createEmptyCell(row, cellNum++);
                                } else {
                                    createCell(row, sdf.format(rs.getDate(column.getColumnName())), cellNum++,
                                            styles.get(WorkbookStyle.fromDataFormat(column.getDataFormat())));
                                }
                                break;
                            case FLOAT:
                                float floatVal = rs.getFloat(column.getColumnName());
                                if (rs.wasNull()) {
                                    createEmptyCell(row, cellNum++);
                                } else {
                                    createCell(row, floatVal, cellNum++,
                                            styles.get(WorkbookStyle.fromDataFormat(column.getDataFormat())));
                                }
                                break;
                            default:
                        }
                    } catch (SQLException e) {
                        LOGGER.error("Invalid column " + column.getColumnName());
                    }
                }
                rowNum++;

                if (rowNum > MAX_NUM_ROWS) {
                    sheet = createNewSheet(user, selectedColumns, workbook, columnWidths, sheetName);
                    rowNum = 1;
                }

            }
        } catch (SQLException e) {
            LOGGER.error("Error running SQL", e);
        }
    }

    @Override
    public void generateFleetExport(User user, HttpServletResponse response, int[] fleetGroupIds) throws IOException {
        try (Connection conn = Objects.requireNonNull(jdbcTemplate.getDataSource()).getConnection()) {
            SXSSFWorkbook workbook = new SXSSFWorkbook(100);
            workbook.setCompressTempFiles(true);
            OracleConnection oconn = conn.unwrap(OracleConnection.class);

            try (
                    PreparedStatement ps = conn.prepareStatement(
                            "SELECT F.*, FV.*, (select CASE WHEN COUNT(VIN) > 1 THEN '*See VIN Tab for all VINs' ELSE LISTAGG(VIN, ', ') WITHIN GROUP (ORDER BY VIN) END from (SELECT VIN FROM FLEET_VEHICLE_VIN FVV "
                                    + " WHERE FVV.FLEET_VEHICLE_ID = FV.FLEET_VEHICLE_ID AND ROWNUM < 4)) VIN FROM FLEET F, FLEET_VEHICLE FV WHERE F.FLEET_ID = FV.FLEET_ID and F.IS_DELETED=0 AND F.FLEET_ID in (select * from table(?)) ORDER BY FLEET_NAME");
            ) {
                Array array = oconn.createARRAY("NUMBER_ARRAY", fleetGroupIds);
                ps.setArray(1, array);
                ResultSet rs = ps.executeQuery();
                buildDynamicExport(user, response,
                        exportColumnRepository.findByExportTypeOrderByColumnOrder("fleet_vehicle"), workbook,
                        getUserText(user, "vehicles"), rs);
            }

            try (
                    PreparedStatement ps = conn.prepareStatement(
                            "SELECT F.*, FV.*, FVV.VIN FROM FLEET F, FLEET_VEHICLE FV, FLEET_VEHICLE_VIN FVV WHERE F.FLEET_ID = FV.FLEET_ID AND FVV.FLEET_VEHICLE_ID = FV.FLEET_VEHICLE_ID AND F.FLEET_ID in (select * from table(?)) ORDER BY FLEET_NAME");
            ) {
                Array array = oconn.createARRAY("NUMBER_ARRAY", fleetGroupIds);
                ps.setArray(1, array);
                ResultSet rs = ps.executeQuery();
                buildDynamicExport(user, response, exportColumnRepository.findByExportTypeOrderByColumnOrder("fleet_vin"),
                        workbook, getUserText(user, "vins"), rs);
            }

            try (
                    PreparedStatement ps = conn.prepareStatement(
                            "SELECT A.FLEET_ID, B.FLEET_NAME, C.LEVEL1_NAME, C.LEVEL2_NAME, C.LEVEL3_NAME, C.LEVEL4_NAME FROM FLEET_HIERARCHY A, FLEET B, "
                                    + " ( SELECT * FROM ( SELECT A.HIERARCHY_ID, B.HIERARCHY_NAME, B.HIERARCHY_LEVEL  FROM (SELECT HIERARCHY_ID, PARENT_ID, HIERARCHY_LEVEL, B.HIERARCHY_NAME, CONNECT_BY_ROOT HIERARCHY_ID AS ROOT_ID, CONNECT_BY_ISLEAF AS LEAF, LEVEL HLEVEL "
                                    + " FROM PRODUCT_HIERARCHY B WHERE 1 = 1 CONNECT BY PRIOR  B.HIERARCHY_ID = B.PARENT_ID ORDER BY LEVEL ) A, PRODUCT_HIERARCHY B WHERE A.ROOT_ID = B.HIERARCHY_ID ) PIVOT (     MAX(HIERARCHY_NAME) "
                                    + " FOR HIERARCHY_LEVEL IN (1 AS LEVEL1_NAME, 2 AS LEVEL2_NAME, 3 AS LEVEL3_NAME, 4 AS LEVEL4_NAME) ) )C WHERE A.FLEET_ID = B.FLEET_ID AND A.HIERARCHY_ID = C.HIERARCHY_ID AND A.FLEET_ID in (select * from table(?)) AND LEVEL4_NAME IS NOT NULL ORDER BY FLEET_NAME, LEVEL1_NAME NULLS FIRST, LEVEL2_NAME NULLS FIRST, LEVEL3_NAME NULLS FIRST, LEVEL4_NAME NULLS FIRST");
            ) {
                Array array = oconn.createARRAY("NUMBER_ARRAY", fleetGroupIds);
                ps.setArray(1, array);
                ResultSet rs = ps.executeQuery();
                buildDynamicExport(user, response,
                        exportColumnRepository.findByExportTypeOrderByColumnOrder("fleet_product"), workbook,
                        getUserText(user, "products"), rs);
            }

            workbook.write(response.getOutputStream());
            response.getOutputStream().flush();
            workbook.close();
            workbook.dispose();
        } catch (SQLException e) {
            LOGGER.error("Error running SQL", e);
        }
    }

    @Override
    public void generateMajorAccountSessionExport(User user, HttpServletResponse response, int sessionId,
                                                  Integer[] siteIds) throws IOException {
        MajorAccount majorAccount = majorAccountRepository.findOneBySiteIds(Arrays.asList(siteIds));
        List<ExportColumn> selectedColumns = majorAccount.getSelectedExportColumns().stream()
                .map((item) -> item.getId().getExportColumn()).collect(Collectors.toList());

        if (majorAccount.isIfiu()) {
            selectedColumns.addAll(getIfiuColumns());
        }

        boolean isSessionFinalized = isSessionFinalized(sessionId);

        try (
                Connection conn = Objects.requireNonNull(jdbcTemplate.getDataSource()).getConnection();
                PreparedStatement ps = conn.prepareStatement(
                        "select decode(p.class, 'O', -1, DENSE_RANK() OVER (PARTITION BY s.SITE_SHORT_NAME, ich.MA_INVESTMENT_COLLECTION_ID ORDER BY case when p.class != 'O' and (min_stocking_qty > 0 or recommended_order_qty > 0) then 1 when p.class != 'O' then 2 else 3 end,"
                                + " (case when (min_stocking_qty > 0 or recommended_order_qty > 0) then nvl(sold_last_12_months, 0) else pts end) desc, (case when (min_stocking_qty > 0 or recommended_order_qty > 0) then pts else -1 end) desc, p.part_number)) as rank, "
                                + " s.SITE_SHORT_NAME, s_dc.SITE_SHORT_NAME AS SERVICING_DC, s_site.SITE_SHORT_NAME AS SERVICING_STORE, p.FIELD_ABBR, p.PART_NUMBER, p.CLASS,NVL(ORDER_QTY, 0) - NVL(RETURN_QTY, 0) ORDER_RETURN_QTY, p.PRODUCT_DESC, ssp.SESSION_ID, VIO, PCT_MARKET, MIN_APP_YEAR, MAX_APP_YEAR, TOP_APP, MAJOR_ACCOUNT_NAME, ON_HAND, PER_CAR, p.JBR_STD_PKG, GROUP_CODE, GOLDEN_PRICE, MA_INVESTMENT_COLLECTION_NAME AS IC_NAME,"
                                + " CASE WHEN NVL(ON_HAND, 0) - NVL(RETURN_QTY, 0) > 0 OR NVL(ORDER_QTY, 0) > 0 THEN 'Y' ELSE 'N' END AS STOCK, NULLIF(SSP.PEER_SALES_12, 0) AS PEER_STORES_SELLING,  ROUND(NVL(SSP.CLUSTER_PCT_SALE,0),3) AS PEER_SALES_PCT,"
                                + "       nullif(SSP.SOLD_LAST_12_MONTHS, 0) AS SALES_12 "
                                + (majorAccount.isIfiu()
                                ? ", ifiu.field_abbr as IFIU_ABBR, ifiu.part_number AS IFIU_PART_NUMBER, ifiu.product_desc AS IFIU_PRODUCT_DESC, ifiu.jbr_std_pkg AS IFIU_STD_PKG, ifiu.price AS IFIU_PRICE "
                                : "")
                                + " from "
                                + (isSessionFinalized ? "session_site_product_finalized" : "session_site_product")
                                + " ssp join site s on s.site_id = ssp.site_id join product p on p.product_id = ssp.product_id "
                                + " join site s_dc on s_dc.site_id = s.ma_servicing_dc "
                                + " join site s_site on s_site.site_id = s.ma_servicing_store "
                                + " join major_account ma on ma.major_account_id = s.major_account_id "
                                + " left join ( "
                                + "   SELECT DISTINCT a.hierarchy_id, b.ma_investment_collection_id, c.ma_inv_coll_template_id, b.ma_investment_collection_name "
                                + "   FROM ma_inv_coll_hierarchy a, ma_investment_collection b, ma_inv_coll_template c "
                                + "   WHERE a.ma_investment_collection_id = b.ma_investment_collection_id "
                                + "   AND b.ma_inv_coll_template_id = c.ma_inv_coll_template_id) ich "
                                + " ON ich.ma_inv_coll_template_id = nvl(s.ma_inv_coll_template_id,ma.default_inv_coll_template_id) "
                                + " AND ich.hierarchy_id = ssp.level4_parent_id "
                                + (majorAccount.isIfiu()
                                ? " left join table(ma_ifiu_product(?,  ?)) ifiu on ssp.product_id = ifiu.orig_product_id "
                                : "")
                                + " where ssp.session_id = ? "
                                + " and ssp.site_id in (select * from table(?)) and (nvl(order_qty,0) > 0 or nvl(return_qty,0) > 0) ");
        ) {
            OracleConnection oconn = conn.unwrap(OracleConnection.class);
            Array array = oconn.createARRAY("SITE_IDS", siteIds);
            if (majorAccount.isIfiu()) {
                ps.setInt(1, sessionId);
                ps.setArray(2, array);
                ps.setInt(3, sessionId);
                ps.setArray(4, array);
            } else {
                ps.setInt(1, sessionId);
                ps.setArray(2, array);
            }
            ResultSet rs = ps.executeQuery();
            buildDynamicExport(user, response, selectedColumns, rs);
        } catch (SQLException e) {
            LOGGER.error("Error running SQL", e);
        }
    }

    @Override
    public void generateSessionExport(User user, HttpServletResponse response, int sessionId, Boolean includePtsCols)
            throws IOException {
        boolean isFinalized = isSessionFinalized(sessionId);
        Stream<ExportColumn> columns = StreamSupport.stream(exportColumnRepository
                .findByExportTypeOrderByColumnOrder(isFinalized ? "session_finalized" : "session_unfinalized")
                .spliterator(), false);
        if (includePtsCols != null && !includePtsCols) {
            columns = columns.filter(column -> !(column.getColumnName().equals("PTS")
                    || column.getColumnName().equals("EFFECTIVE_PTS") || column.getColumnName().equals("BUSINESS_RULES_APPLIED")
                    || column.getColumnName().equals("RECOMMENDED_ORDER_QTY") || column.getColumnName().equals("RECOMMENDED_RETURN_QTY")
                    || column.getColumnName().equals("LAST_EDITED_BY")));
        }

        String sql;
        if (isFinalized) {
            sql = "SELECT q1.fleet_count as FLEET_VEHICLE_COUNT,  t.*, td.*, s.*, p.*, sp.*, (ROUND(td.PTS*100)) as SALES_POTENTIAL , \n" +
                    "  CASE WHEN td.order_qty > 0 THEN (sp.price_wt_core * td.order_qty) \n" +
                    "       WHEN td.return_qty > 0 THEN -(sp.price_wt_core * td.return_qty) \n" +
                    "  END AS ext_price, \n" +
                    "  (SELECT first_name || ' ' || last_name FROM pulse_user ps WHERE ps.user_id = td.user_id) AS last_edited_by \n" +
                    "  FROM transmission t, transmission_detail td, site s, site_product sp, product p\n" +

                    "  LEFT join \n" +
                    "(SELECT product_id, SUM(count) fleet_count FROM ( select /*+ INDEX (sp SESS_SITE_PROD_PRD_IDX) */ pv.product_id, \n" +
                    "f.fleet_vehicle_id, f.count from product_vehicle_application pv inner join \n" +
                    "(select * from TRANSMISSION_DETAIL td inner join TRANSMISSION t on t.TRANSMISSION_ID=td.TRANSMISSION_ID where t.SESSION_ID=?) sp \n" +
                    "on sp.product_id = pv.product_id inner join \n" +
                    "( SELECT fv.* FROM fleet_vehicle fv INNER JOIN \n" +
                    "(SELECT * FROM fleet WHERE site_id IN (SELECT site_id FROM session_site WHERE session_id = ?)) f \n" +
                    "ON fv.fleet_id = f.fleet_id ) f ON (pv.make_id = f.make_id \n" +
                    "AND pv.model_id = f.model_id AND pv.engine_id = f.engine_id AND pv.vehicle_year BETWEEN f.from_year AND f.to_year) \n" +
                    "GROUP BY pv.product_id, f.fleet_vehicle_id, f.count ) \n" +
                    "GROUP BY product_id) q1 on p.PRODUCT_ID = q1.product_id \n" +

                    "  WHERE t.session_id = ?\n" +
                    "  AND t.site_id = s.site_id AND p.product_id = td.product_id AND td.transmission_id = t.transmission_id AND sp.site_id = s.site_id \n" +
                    "  AND sp.product_id = td.product_id  \n" +
                    "  AND (td.on_hand > 0 OR td.order_qty > 0 OR td.return_qty > 0 OR td.recommended_order_qty != td.order_qty OR td.recommended_return_qty != td.return_qty) \n" +
                    "  ORDER BY s.site_short_name\n";
        } else {
            sql = "SELECT q1.fleet_count as FLEET_VEHICLE_COUNT,\n" +
                    "ssp.*, s.*, p.*,\n" +
                    "(ROUND(ssp.PTS*100)) as SALES_POTENTIAL , CASE WHEN ssp.order_qty > 0 THEN (ssp.price_wt_core * ssp.order_qty)\n" +
                    "WHEN ssp.return_qty > 0 THEN -(ssp.price_wt_core * ssp.return_qty) END AS ext_price\n" +
                    "FROM session_site_product ssp, site s, product p\n" +

                    "LEFT join\n" +
                    "(SELECT product_id, SUM(count) fleet_count FROM ( select /*+ INDEX (ssp SESS_SITE_PROD_PRD_IDX) */ pv.product_id,\n" +
                    "f.fleet_vehicle_id, f.count from product_vehicle_application pv inner join\n" +
                    "(SELECT * FROM session_site_product WHERE session_id = ?) ssp\n" +
                    "on ssp.product_id = pv.product_id inner join\n" +
                    "( SELECT fv.* FROM fleet_vehicle fv INNER JOIN\n" +
                    "(SELECT * FROM fleet WHERE site_id IN (SELECT site_id FROM session_site WHERE session_id = ?)) f\n" +
                    "ON fv.fleet_id = f.fleet_id ) f ON (pv.make_id = f.make_id\n" +
                    "AND pv.model_id = f.model_id AND pv.engine_id = f.engine_id AND pv.vehicle_year BETWEEN f.from_year AND f.to_year)\n" +
                    "GROUP BY pv.product_id, f.fleet_vehicle_id, f.count )\n" +
                    "GROUP BY product_id) q1 on p.PRODUCT_ID = q1.product_id\n" +

                    "WHERE ssp.site_id = s.site_id AND p.product_id = ssp.product_id\n" +
                    "AND session_id = ?\n" +
                    "AND ssp.site_id IN (SELECT site_id FROM session_site WHERE session_id = ?)\n" +
                    "AND (on_hand > 0 OR order_qty > 0 OR return_qty > 0) ORDER BY s.site_short_name";
        }

        try (
                Connection conn = Objects.requireNonNull(jdbcTemplate.getDataSource()).getConnection();
                PreparedStatement ps = conn.prepareStatement(sql)
        ) {

                ps.setInt(1, sessionId);
                ps.setInt(2, sessionId);
                ps.setInt(3, sessionId);
                if (!isFinalized){
                    ps.setInt(4, sessionId);
                }

            ResultSet rs = ps.executeQuery();

            buildDynamicExport(user, response, columns.collect(Collectors.toList()), rs);
        } catch (SQLException e) {
            LOGGER.error("Error running SQL", e);
        }
    }

    public void buildExcel(User user, ResultSet rs, HttpServletResponse response, String sessionType) throws IOException, SQLException {
        SXSSFWorkbook workbook = new SXSSFWorkbook(100);
        CreationHelper creationHelper = workbook.getCreationHelper();
        workbook.setCompressTempFiles(true);
        try {
            OutputStream out = response.getOutputStream();
            response.setHeader("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setHeader("Content-Disposition", "attachment;filename=\"excelReport.xlsx\"");

            int[] columnWidths = {2600, 10100, 4600, 3100, 3100, 3100, 2600, 2600, 2700, 3100, 3100,3100,3100, 3100, 3100, 3600,
                    4400, 3100};
            int[] columnWidthsMinMax = {2600, 2000, 2000, 3100, 2200, 2200, 3100, 2200, 2200, 2200, 3100,3100,3100, 5000, 2800, 2800,
                    3500, 2800};
            CellStyle moneyStyle = workbook.createCellStyle();
            moneyStyle.setDataFormat((short) 8);
            CellStyle numberStyle = workbook.createCellStyle();
            numberStyle.setDataFormat((short) 2);
            CellStyle percentStyle = workbook.createCellStyle();
            percentStyle.setDataFormat((short) 9);
            CellStyle generalStyle = workbook.createCellStyle();
            generalStyle.setDataFormat((short) 3);
            CellStyle dateStyle = workbook.createCellStyle();
            dateStyle.setDataFormat(creationHelper.createDataFormat().getFormat("YYYY-mmm-dd"));
            CellStyle[] frStyles = {generalStyle, generalStyle, generalStyle, generalStyle, moneyStyle, moneyStyle,
                    percentStyle, generalStyle, generalStyle, generalStyle, generalStyle, generalStyle,generalStyle,generalStyle, generalStyle,
                    generalStyle, generalStyle, generalStyle};
            CellStyle[] enStyles = {generalStyle, generalStyle, generalStyle, generalStyle, moneyStyle, moneyStyle,
                    percentStyle, generalStyle, generalStyle, generalStyle, generalStyle,generalStyle,generalStyle, moneyStyle, moneyStyle,
                    moneyStyle, moneyStyle, moneyStyle};

            CellStyle[] enMinMaxStyles = {generalStyle, generalStyle, generalStyle, generalStyle, generalStyle, generalStyle,
                    dateStyle, generalStyle, generalStyle, generalStyle, generalStyle,generalStyle,generalStyle, generalStyle, generalStyle,
                    moneyStyle, moneyStyle, moneyStyle};

            CellStyle[] frMinMaxStyles = {generalStyle, generalStyle, generalStyle, generalStyle, generalStyle, generalStyle,
                    dateStyle, generalStyle, generalStyle, generalStyle, generalStyle, generalStyle,generalStyle,generalStyle, generalStyle,
                    moneyStyle, moneyStyle, moneyStyle};

            CellStyle[] styles = "fr".equals(user.getLanguage()) ? frStyles : enStyles;
            CellStyle[] stylesMinMax = "fr".equals(user.getLanguage()) ? frMinMaxStyles : enMinMaxStyles;


            if("MinMax".equals(sessionType)) {
                Sheet sheet = createNewSheet(user, "M", workbook, columnWidthsMinMax);
                int rowNum = 1; // we've already written 0 as the headers
                while (rs.next()) {
                    addRowForMinMax(user, sheet, rs, rowNum, stylesMinMax);
                    rowNum++;
                    if (rowNum > MAX_NUM_ROWS) {
                        sheet = createNewSheet(user, "M", workbook, columnWidthsMinMax);
                        rowNum = 1;
                    }
                }
            }
            else {
                Sheet sheet = createNewSheet(user, "S", workbook, columnWidths);
                int rowNum = 1; // we've already written 0 as the headers
                while (rs.next()) {
                    addRow(user, sheet, rs, rowNum, styles);
                    rowNum++;
                    if (rowNum > MAX_NUM_ROWS) {
                        sheet = createNewSheet(user, "S", workbook, columnWidths);
                        rowNum = 1;
                    }
                }
            }
            workbook.write(out);
            out.flush();

        } catch (IOException ioe) {
            throw new IOException(ioe);
        } catch (SQLException sqle) {
            throw new SQLException(sqle);
        } finally {
            workbook.close();
            workbook.dispose();
        }
    }

    @Override
    public void generateNonNapaOrderExport(User user, HttpServletResponse response, int sessionId, int siteId)
            throws IOException {
        SXSSFWorkbook workbook = new SXSSFWorkbook(100);
        workbook.setCompressTempFiles(true);

        int[] columnWidths = {2600, 2600, 2200, 4000, 10100, 2600, 3100, 1400, 2700, 3500, 3100, 3100, 3100, 3100,
                12000};
        CellStyle moneyStyle = workbook.createCellStyle();
        moneyStyle.setDataFormat((short) 8);
        CellStyle numberStyle = workbook.createCellStyle();
        numberStyle.setDataFormat((short) 2);
        CellStyle digitStyle = workbook.createCellStyle();
        CellStyle percentStyle = workbook.createCellStyle();
        percentStyle.setDataFormat((short) 9);
        CellStyle generalStyle = workbook.createCellStyle();
        generalStyle.setDataFormat((short) 3);

        CellStyle[] styles = {digitStyle, generalStyle, generalStyle, generalStyle, generalStyle, generalStyle,
                moneyStyle, generalStyle, percentStyle, generalStyle, percentStyle, generalStyle, digitStyle,
                digitStyle, generalStyle};

        try (Connection conn = Objects.requireNonNull(jdbcTemplate.getDataSource()).getConnection()) {
            boolean isSessionFinalized = this.isSessionFinalized(sessionId);

            try (
                    PreparedStatement ps = conn.prepareStatement(
                            "select ssp.session_id, s.site_short_name, p.FIELD_ABBR, p.EXT_PART_NUMBER, p.PRODUCT_DESC, ssp.ORDER_QTY, ssp.RETURN_QTY,\n"
                                    + "(case\n" + "    when ssp.order_qty > 0 then (SSP.PRICE_WT_CORE * SSP.ORDER_QTY)\n"
                                    + "    when SSP.RETURN_QTY > 0 then -(SSP.PRICE_WT_CORE * SSP.RETURN_QTY)\n"
                                    + "    else null END) AS ext_price,\n" + "    vio,\n" + "ssp.PCT_MARKET,\n"
                                    + "ssp.peer_sales_12,round(NVL(ssp.CLUSTER_PCT_SALE,0),3) as PEER_SALES_PCT,\n" + "(case\n"
                                    + "    when (ssp.PTS >= 0 and ssp.PTS < 0.3) then 'Red Right'\n"
                                    + "    when (ssp.PTS >= 0.3 and ssp.PTS < 0.4) then 'Yellow Right'\n"
                                    + "    when (ssp.PTS >= 0.4 and ssp.PTS < 0.5) then 'Green Right'\n"
                                    + "    when (ssp.PTS = 0.5) then 'Mid Point'\n"
                                    + "    when (ssp.PTS > 0.5 and ssp.PTS <= 0.6) then 'Green Left'\n"
                                    + "    when (ssp.PTS > 0.6 and ssp.PTS <= 0.7) then 'Yellow Left'\n"
                                    + "    when (ssp.PTS > 0.7 and ssp.PTS < 1) then 'Red Left'\n"
                                    + "    else '' END) as propensity,\n" + "    min_app_year, max_app_year, \n"
                                    + "    p.top_app\n"
                                    + (isSessionFinalized ? "from session_site_product_finalized ssp\n"
                                    : "from session_site_product ssp\n")
                                    + "inner join site s on s.site_id = ssp.site_id\n"
                                    + "inner join product p on p.product_id = ssp.product_id\n"
                                    + "where ssp.session_id = ? and ssp.site_id = ?");
            ) {
                ps.setInt(1, sessionId);
                ps.setInt(2, siteId);

                ResultSet rs = ps.executeQuery();

                Sheet sheet = createNewSheet(user, "T", workbook, columnWidths);
                int rowNum = 1; // we've already written 0 as the headers
                while (rs.next()) {
                    addNonNapaOrderRow(user, sheet, rs, rowNum, styles);
                    rowNum++;
                    if (rowNum > MAX_NUM_ROWS) {
                        sheet = createNewSheet(user, "T", workbook, columnWidths);
                        rowNum = 1;
                    }
                }

                workbook.write(response.getOutputStream());
                response.getOutputStream().flush();
            }
        } catch (SQLException e) {
            LOGGER.error("Error running SQL", e);
        }
    }

    private Sheet createNewSheet(User user, String reportType, SXSSFWorkbook workbook, int[] columnWidths) {
        Sheet sheet = workbook.createSheet();

        for (int i = 0; i < columnWidths.length; i++) {
            sheet.setColumnWidth(i, columnWidths[i]);
        }

        writeHeaders(user, reportType, sheet.createRow(0));
        return sheet;
    }

    private Sheet createNewSheet(User user, Iterable<ExportColumn> columns, SXSSFWorkbook workbook, int[] columnWidths,
                                 String sheetName) {
        Sheet sheet;
        if (sheetName == null) {
            sheet = workbook.createSheet();
        } else {
            sheet = workbook.createSheet(sheetName);
        }
        sheet.createFreezePane(0, 1);

        for (int i = 0; i < columnWidths.length; i++) {
            sheet.setColumnWidth(i, columnWidths[i]);
        }

        writeHeaders(user, columns, sheet.createRow(0));
        return sheet;
    }

    private void writeHeaders(User user, Iterable<ExportColumn> columns, Row row) {
        int cellNum = 0;
        for (ExportColumn column : columns) {
            createCell(row, "en".equals(user.getLanguage()) ? column.getTranslationItem().getTextEn()
                    : column.getTranslationItem().getTextFr(), cellNum++, (CellStyle[]) null);
        }
    }

    private void writeHeaders(User user, String reportType, Row row) {
        int cellNum = 0;
        for (String header : getHeaders(reportType, user.getLanguage())) {
            createCell(row, header, cellNum++, (CellStyle[]) null);
        }
    }

    public List<String> getHeaders(String reportType, String language) {
        Map<String, List<String>> headersByLanguage = headersByReportTypeAndLanguage.computeIfAbsent(reportType, k -> new HashMap<>());
        List<String> headers = headersByLanguage.get(language);
        if (headers == null) {
            headers = exportColumnNameDAO.getNames(language, reportType);
            headersByLanguage.put(language, headers);
        }
        return headers;
    }

    private void addRow(User user, Sheet sheet, ResultSet rs, int rowNum, CellStyle[] styles) throws SQLException {
        Row row = sheet.createRow(rowNum);
        int cellNum = 0;
        createCell(row, rs.getString("SITE_SHORT_NAME"), cellNum++, styles);
        createCell(row, rs.getString("hierarchy"), cellNum++, styles);
        createCell(row, rs.getString("reco"), cellNum++, styles);
        int ignoreStock = rs.getInt("IGNORE_STOCK");
        if (ignoreStock == 999) {
            createCell(row, "Yes", cellNum++, styles);
        } else {
            createCell(row, ignoreStock, cellNum++, styles);
        }

        createCell(row, rs.getFloat("MIN_GREATER_ZERO"), cellNum++, styles);
        createCell(row, rs.getFloat("cogs"), cellNum++, styles);
        createCell(row, rs.getFloat("criticality"), cellNum++, styles);
        createCell(row, rs.getFloat("turnover"), cellNum++, styles);

        createCell(row, rs.getInt("ORDER_COUNT"), cellNum++, styles);
        createCell(row, rs.getInt("RETURN_COUNT"), cellNum++, styles);
        createCell(row, rs.getInt("NET_COUNT"), cellNum++, styles);
        createCell(row, rs.getInt("HS_NETWORK_UIC_BEFORE"), cellNum++, styles);
        createCell(row, rs.getInt("HS_NETWORK_UIC_AFTER"), cellNum++, styles);


        if ("fr".equals(user.getLanguage())) {
            Float ordertotal = rs.getFloat("ORDER_TOTAL");
            createCell(row, String.valueOf(ordertotal).replace(",", " ").replace(".", ","), cellNum++, styles);

            Float returntotal = rs.getFloat("RETURN_TOTAL");
            createCell(row, String.valueOf(returntotal).replace(",", " ").replace(".", ","), cellNum++, styles);

            Float stockingreturn = rs.getFloat("STOCKING_RETURN");
            createCell(row, String.valueOf(stockingreturn).replace(",", " ").replace(".", ","), cellNum++, styles);

            Float nonstockingreturn = rs.getFloat("NON_STOCKING_RETURN");
            createCell(row, String.valueOf(nonstockingreturn).replace(",", " ").replace(".", ","), cellNum++, styles);

            Float nettotal = rs.getFloat("NET_TOTAL");
            createCell(row, String.valueOf(nettotal).replace(",", " ").replace(".", ","), cellNum++, styles);

        } else {
            createCell(row, rs.getFloat("ORDER_TOTAL"), cellNum++, styles);
            createCell(row, rs.getFloat("RETURN_TOTAL"), cellNum++, styles);
            createCell(row, rs.getFloat("STOCKING_RETURN"), cellNum++, styles);
            createCell(row, rs.getFloat("NON_STOCKING_RETURN"), cellNum++, styles);
            createCell(row, rs.getFloat("NET_TOTAL"), cellNum++, styles);
        }

    }

    private void addRowForMinMax(User user, Sheet sheet, ResultSet rs, int rowNum, CellStyle[] styles) throws SQLException {
        Row row = sheet.createRow(rowNum);
        int cellNum = 0;
        createCell(row, rs.getString("siteName"), cellNum++, styles);
        createCell(row, rs.getString("line"), cellNum++, styles);
        createCell(row, rs.getInt("group"), cellNum++, styles);
        createCell(row, rs.getString("partNumber"), cellNum++, styles);
        createCell(row, rs.getInt("newMin"), cellNum++, styles);
        createCell(row, rs.getInt("newMax"), cellNum++, styles);
        Cell cell = row.createCell(cellNum++);
        cell.setCellValue(rs.getDate("firstStockDate"));
        cell.setCellStyle(styles[6]);
        createCell(row, rs.getInt("currMin"), cellNum++, styles);
        createCell(row, rs.getInt("currMax"), cellNum++, styles);
        createCell(row, rs.getInt("onHandCount"), cellNum++, styles);
        createCell(row, rs.getInt("perCar"), cellNum++, styles);
        createCell(row, rs.getString("partDescription"), cellNum++, styles);
        createCell(row, rs.getString("topApp"), cellNum++, styles);
        createCell(row, rs.getInt("salesHistory"), cellNum++, styles);
        createCell(row, rs.getFloat("currentInv"), cellNum++, styles);
        createCell(row, rs.getFloat("recommended"), cellNum++, styles);
        createCell(row, rs.getFloat("change"), cellNum++, styles);
    }

    private void addNonNapaOrderRow(User user, Sheet sheet, ResultSet rs, int rowNum, CellStyle[] styles)
            throws SQLException {
        Row row = sheet.createRow(rowNum);
        int cellNum = 0;
        createCell(row, rs.getInt("SESSION_ID"), cellNum++, styles);
        createCell(row, rs.getString("SITE_SHORT_NAME"), cellNum++, styles);
        createCell(row, rs.getString("FIELD_ABBR"), cellNum++, styles);
        createCell(row, rs.getString("EXT_PART_NUMBER"), cellNum++, styles);
        createCell(row, rs.getString("PRODUCT_DESC"), cellNum++, styles);
        createCell(row, rs.getInt("ORDER_QTY") - rs.getInt("RETURN_QTY"), cellNum++, styles);
        createCell(row, rs.getFloat("EXT_PRICE"), cellNum++, styles);
        createCell(row, rs.getInt("VIO"), cellNum++, styles);
        createCell(row, rs.getFloat("PCT_MARKET"), cellNum++, styles);
        createCell(row, rs.getInt("PEER_SALES_12"), cellNum++, styles);
        createCell(row, rs.getFloat("PEER_SALES_PCT"), cellNum++, styles);
        createCell(row, rs.getString("PROPENSITY"), cellNum++, styles);
        if (rs.getInt("MIN_APP_YEAR") != 0) {
            createCell(row, rs.getInt("MIN_APP_YEAR"), cellNum++, styles);
        }
        if (rs.getInt("MAX_APP_YEAR") != 0) {
            createCell(row, rs.getInt("MAX_APP_YEAR"), cellNum++, styles);
        }
        createCell(row, rs.getString("TOP_APP"), cellNum++, styles);
    }

    private void createCell(Row row, float value, int cellNum, CellStyle[] styles) {
        Cell cell = createCell(row, cellNum, CellType.NUMERIC, styles);
        cell.setCellValue(value);
    }

    private void createCell(Row row, String value, int cellNum, CellStyle[] styles) {
        Cell cell = createCell(row, cellNum, CellType.STRING, styles);
        cell.setCellValue(value);
    }

    private void createCell(Row row, int value, int cellNum, CellStyle[] styles) {
        Cell cell = createCell(row, cellNum, CellType.NUMERIC, styles);
        cell.setCellValue(value);
    }

    private Cell createCell(Row row, int cellNum, CellType cellType, CellStyle[] styles) {
        Cell cell = row.createCell(cellNum);
        cell.setCellType(cellType);
        if (styles != null) {
            cell.setCellStyle(styles[cellNum]);
        }
        return cell;
    }

    private Cell createCell(Row row, int cellNum, CellType cellType, CellStyle style) {
        Cell cell = row.createCell(cellNum);
        cell.setCellType(cellType);
        if (style != null) {
            cell.setCellStyle(style);
        }
        return cell;
    }

    private void createCell(Row row, int value, int cellNum, CellStyle style) {
        Cell cell = createCell(row, cellNum, CellType.NUMERIC, style);
        cell.setCellValue(value);
    }

    private void createCell(Row row, String value, int cellNum, CellStyle style) {
        Cell cell = createCell(row, cellNum, CellType.STRING, style);
        cell.setCellValue(value);
    }

    private void createCell(Row row, float value, int cellNum, CellStyle style) {
        Cell cell = createCell(row, cellNum, CellType.NUMERIC, style);
        cell.setCellValue(value);
    }

    private void createEmptyCell(Row row, int cellNum) {
        row.createCell(cellNum);
    }

    private Map<WorkbookStyle, CellStyle> createWorkbookStyles(SXSSFWorkbook workbook) {
        Map<WorkbookStyle, CellStyle> map = new HashMap<>();
        for (WorkbookStyle style : WorkbookStyle.values()) {
            CellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setDataFormat((short) style.dataFormat);
            map.put(style, cellStyle);
        }
        return map;
    }

    private enum WorkbookStyle {
        MONEY(8), NUMBER(2), DIGIT(1), PERCENT(9), GENERAL(3);

        private final int dataFormat;

        WorkbookStyle(int dataFormat) {
            this.dataFormat = dataFormat;
        }

        public static Object fromDataFormat(int dataFormat) {
            for (WorkbookStyle test : values()) {
                if (test.dataFormat == dataFormat) {
                    return test;
                }
            }
            throw new IllegalArgumentException("Invalid data format");
        }

    }

    private List<ExportColumn> getIfiuColumns() {
        final TranslationItem maIFIUFieldAbbr = translationItemRepository.findById("maIFIUFieldAbbr").orElse(null);
        final TranslationItem maIFIUPart = translationItemRepository.findById("maIFIUPart").orElse(null);
        final TranslationItem maIFIUDesc = translationItemRepository.findById("maIFIUDesc").orElse(null);
        final TranslationItem maIFIUStdPkg = translationItemRepository.findById("maIFIUStdPkg").orElse(null);
        final TranslationItem maIFIUPricing = translationItemRepository.findById("maIFIUPricing").orElse(null);

        List<ExportColumn> columns = new ArrayList<>();
        columns.add(new ExportColumn(maIFIUFieldAbbr, 12, "IFIU_ABBR", 3, 3000));
        columns.add(new ExportColumn(maIFIUPart, 12, "IFIU_PART_NUMBER", 3, 3000));
        columns.add(new ExportColumn(maIFIUDesc, 12, "IFIU_PRODUCT_DESC", 3, 6000));
        columns.add(new ExportColumn(maIFIUStdPkg, 2, "IFIU_STD_PKG", 1, 3000));
        columns.add(new ExportColumn(maIFIUPricing, 6, "IFIU_PRICE", 8, 3000));
        return columns;
    }

    private boolean isSessionFinalized(Integer sessionId) {

        int status = jdbcTemplate.queryForObject("SELECT STATUS_ID FROM PULSE_SESSION WHERE SESSION_ID = ?",
                new Object[]{sessionId}, Integer.class);

        return status == 3;
    }

    public void clearCachedHeaders() {
        headersByReportTypeAndLanguage = new HashMap<>();
    }

    private String getUserText(User user, String key) {
        TranslationItem item = translationItemRepository.findById(key).orElse(null);
        if (item == null) {
            return null;
        } else {
            return "fr".equals(user.getLanguage()) ? item.getTextFr() : item.getTextEn();
        }
    }

    @Override
    public void generateHubSpokeExport(User user, HttpServletResponse response, int siteId) throws IOException {
        SXSSFWorkbook workbook = new SXSSFWorkbook(100);
        workbook.setCompressTempFiles(true);

        try (Connection conn = Objects.requireNonNull(jdbcTemplate.getDataSource()).getConnection()) {
            // Hub & Spokes Tab
            try (
                    PreparedStatement ps = conn.prepareStatement("select t.name name, hs.site_name hub_name, ss.site_name spokes "
                            + "from transfer_mapping_hub t "
                            + "join site hs on hs.site_id = t.site_id "
                            + "left join transfer_mapping_spoke tms on t.transfer_mapping_hub_id = tms.transfer_mapping_hub_id "
                            + "left join site ss on ss.site_id = tms.site_id "
                            + "where t.transfer_mapping_hub_id in ( "
                            + "    select distinct transfer_mapping_hub_id "
                            + "    from transfer_mapping_hub "
                            + "    where site_id = ? "
                            + "    union select transfer_mapping_hub_id "
                            + "    from transfer_mapping_spoke "
                            + "    where site_id = ? "
                            + ") "
                            + "order by case when t.site_id = ? then 'HUB' else 'SPOKE' end, t.transfer_mapping_hub_id")
            ) {
                ps.setInt(1, siteId);
                ps.setInt(2, siteId);
                ps.setInt(3, siteId);

                ResultSet rs = ps.executeQuery();

                buildDynamicExport(user, response,
                        exportColumnRepository.findByExportTypeOrderByColumnOrder("hub_spoke_relation"), workbook, getUserText(user, "hsmHubSpoke"), rs);
            }

            // Categories Tab
            try (
                    PreparedStatement ps = conn.prepareStatement("SELECT tmh.name, ph.level1_hierarchy, ph.level2_hierarchy, ph.level3_hierarchy, ph.level4_hierarchy "
                            + "FROM transfer_mapping_hub tmh, transfer_mapping_hierarchy tmi, "
                            + "(SELECT * FROM ( "
                            + "    SELECT hierarchy_name, hierarchy_level, CONNECT_BY_ROOT hierarchy_id hierarchy_id "
                            + "    FROM product_hierarchy "
                            + "    CONNECT BY NOCYCLE "
                            + "    PRIOR parent_id = hierarchy_id "
                            + ") "
                            + "PIVOT ( "
                            + "    MAX(hierarchy_name) "
                            + "    for hierarchy_level in (1 AS level1_hierarchy, 2 AS level2_hierarchy, 3 AS level3_hierarchy, 4 AS level4_hierarchy) "
                            + ")) ph "
                            + "WHERE tmh.transfer_mapping_hub_id = tmi.transfer_mapping_hub_id "
                            + "AND tmi.hierarchy_id = ph.hierarchy_id "
                            + "AND tmh.site_id = ? "
                            + "ORDER BY tmh.name")
            ) {
                ps.setInt(1, siteId);

                ResultSet rs = ps.executeQuery();

                buildDynamicExport(user, response,
                        exportColumnRepository.findByExportTypeOrderByColumnOrder("hub_spoke_hierarchy"), workbook, getUserText(user, "hsmCategories"), rs);
            }

            workbook.write(response.getOutputStream());
            response.getOutputStream().flush();
        } catch (SQLException e) {
            LOGGER.error("Error running SQL", e);
        } finally {
            workbook.close();
            workbook.dispose();
        }
    }

    public void generateDashboardPageSpokeExcel(Integer sessionId, User user, HttpServletResponse response)
            throws Exception {
        Stream<ExportColumn> columns = StreamSupport.stream(exportColumnRepository
                .findByExportTypeOrderByColumnOrder("dashboardSpokeExcel")
                .spliterator(), false);

        SXSSFWorkbook workbook = new SXSSFWorkbook(100);
        workbook.setCompressTempFiles(true);
        Iterable<ExportColumn> selectedColumns = columns.collect(Collectors.toList());
        int[] columnWidths = StreamSupport.stream(selectedColumns.spliterator(), false)
                .map(ExportColumn::getWidth).mapToInt((item) -> item).toArray();
        Map<WorkbookStyle, CellStyle> styles = createWorkbookStyles(workbook);

        SqlRowSet sites = jdbcTemplate.queryForRowSet("SELECT SITE_ID, SITE_NAME FROM SITE WHERE SITE_ID IN (select DISTINCT site_id from session_site_product where session_id = "+sessionId+" and hs_part='S') order by SITE_SHORT_NAME");


        Font headerFont = workbook.createFont();
        headerFont.setBold(true);
        headerFont.setFontHeightInPoints((short) 14);

        CellStyle headerCellStyle = workbook.createCellStyle();
        headerCellStyle.setFont(headerFont);
        headerCellStyle.setWrapText(true);
        headerCellStyle.setAlignment(HorizontalAlignment.CENTER);

        try (Connection conn = Objects.requireNonNull(jdbcTemplate.getDataSource()).getConnection()) {
            while (sites.next()) {
                String sql=prepareDashboardPageSpokeExcelQuery(sites.getInt("SITE_ID"),sessionId);
                PreparedStatement ps = conn.prepareStatement(sql);
                    ResultSet rs = ps.executeQuery();
                String sheetName = sites.getString("SITE_NAME");

                Sheet sheet = createNewSheet(user, selectedColumns, workbook, columnWidths, sheetName);

                sheet.setAutoFilter(new CellRangeAddress(0, 0, 0, columnWidths.length - 1));
                int rowNum = 1; // we've already written 0 as the headers
                try {
                    while (rs.next()) {

                        Row row = sheet.createRow(rowNum);
                        int cellNum = 0;
                        for (ExportColumn column : selectedColumns) {
                            try {
                                switch (JDBCType.valueOf(column.getColumnType())) {
                                    case NUMERIC:
                                        if (column.getDataFormat() == 9) {
                                            float floatVal = rs.getFloat(column.getColumnName());
                                            if (rs.wasNull()) {
                                                createEmptyCell(row, cellNum++);
                                            } else {
                                                createCell(row, floatVal, cellNum++,
                                                        styles.get(WorkbookStyle.fromDataFormat(column.getDataFormat())));
                                            }
                                        } else {
                                            int intVal = rs.getInt(column.getColumnName());
                                            if (rs.wasNull()) {
                                                createEmptyCell(row, cellNum++);
                                            } else {
                                                createCell(row, intVal, cellNum++,
                                                        styles.get(WorkbookStyle.fromDataFormat(column.getDataFormat())));
                                            }
                                        }
                                        break;
                                    case VARCHAR:
                                        createCell(row, rs.getString(column.getColumnName()), cellNum++,
                                                styles.get(WorkbookStyle.fromDataFormat(column.getDataFormat())));
                                        break;
                                    case DATE:
                                        Date date = rs.getDate(column.getColumnName());
                                        if (date == null) {
                                            createEmptyCell(row, cellNum++);
                                        } else {
                                            createCell(row, sdf.format(rs.getDate(column.getColumnName())), cellNum++,
                                                    styles.get(WorkbookStyle.fromDataFormat(column.getDataFormat())));
                                        }
                                        break;
                                    case FLOAT:
                                        float floatVal = rs.getFloat(column.getColumnName());
                                        if (rs.wasNull()) {
                                            createEmptyCell(row, cellNum++);
                                        } else {
                                            createCell(row, floatVal, cellNum++,
                                                    styles.get(WorkbookStyle.fromDataFormat(column.getDataFormat())));
                                        }
                                        break;
                                    default:
                                }
                            } catch (SQLException e) {
                                LOGGER.error("Invalid column " + column.getColumnName());
                            }
                        }
                        rowNum++;

                    }
                } catch (SQLException e) {
                    LOGGER.error("Error running SQL", e);
                }

            }
        } catch (Exception e) {
            LOGGER.error("Error in generateDashboardSpokeExcel() for sessionId:{}, message:{}", sessionId, e);
        }
        workbook.write(response.getOutputStream());
        response.getOutputStream().flush();
        workbook.close();
        workbook.dispose();
    }

    private String prepareDashboardPageSpokeExcelQuery(int site_id, Integer sessionId) {
        //This essence of this query is to calculate the hubCapacity,returns to hub and returns to dc taking the whole network(hub and all spoke sites) into consideration for each sku.
            String sql="SELECT spokesite as \"spokeSite\",hubsitename AS \"hubSite\",field_abbr AS \"fieldAbbr\",part_number as \"part\",\n" +
                    "       product_desc as \"description\",nvl(per_car,0) as \"perCar\",nvl(jbr_std_pkg,0) as \"stdPack\"\n" +
                    "        ,nvl(keep_qty,0) AS \"keepQty\",nvl(return_qty,0) AS \"returnQty\",hc AS \"hubAvailCapacity\",\n" +
                    "       CASE WHEN hc>=return_qty THEN nvl(return_qty,0)\n" +
                    "            WHEN hc<return_qty THEN nvl(hc,0)\n" +
                    "           END \"returnToHubQty\",\n" +
                    "       CASE WHEN hc>=return_qty THEN 0\n" +
                    "            WHEN hc<return_qty THEN nvl(return_qty-hc,0)\n" +
                    "           END \"returnToDcQty\"\n" +
                    "    FROM (SELECT hubsitename,field_abbr,part_number,product_desc,per_car,jbr_std_pkg,return_qty,keep_qty,\n" +
                    "             site_short_name AS spokesite,\n" +
                    "             CASE\n" +
                    "                 WHEN ((nvl(max_stocking_qty,0)+nvl(DEMAND,0))-nvl(on_hand,0))>=0 THEN ((nvl(max_stocking_qty,0)+nvl(DEMAND,0))-nvl(on_hand,0))\n" +
                    "                 WHEN ((nvl(max_stocking_qty,0)+nvl(DEMAND,0))-nvl(on_hand,0))<0 THEN 0\n" +
                    "                 END hc\n" +
                    "        FROM  (SELECT  A.field_abbr,A.part_number,A.product_desc,A.per_car,A.jbr_std_pkg,A.return_qty,A.keep_qty,\n" +
                    "                    (SELECT site_short_name FROM SITE WHERE site_id=(SELECT hub_site_id FROM hs_network_hub WHERE network_hub_id=hubid) )AS hubsitename,\n" +
                    "                    A.site_short_name,\n" +
                    "                    CASE WHEN A.returnToHub = 0 THEN 0\n" +
                    "                         WHEN A.returnTOHub = 12 THEN B.occ_12\n" +
                    "                         WHEN A.returnToHub = 24 THEN B.occ_24\n" +
                    "                        END AS DEMAND,B.max_stocking_qty,B.on_hand\n" +
                    "             FROM ( (select /*count(distinct ssp.product_id)*/\n" +
                    "                        DISTINCT (ssp.product_id) product_id,\n" +
                    "                        ssp.session_id,\n" +
                    "                        P.field_abbr,\n" +
                    "                        P.part_number,\n" +
                    "                        P.product_desc,\n" +
                    "                        P.per_car,\n" +
                    "                        P.jbr_std_pkg,\n" +
                    "                        ssp.return_qty,\n" +
                    "                        ssp.keep_qty,\n" +
                    "                        ssp.level4_parent_id,\n" +
                    "                        S.site_short_name,\n" +
                    "                        hs.network_hub_id hubid,\n" +
                    "                        hsmap.heirarchy_id,\n" +
                    "                        nvl(hsmap.return_to_hub,12) as returnToHub\n" +
                    "                            FROM (\n" +
                    "                                SELECT X.*\n" +
                    "                                    FROM session_site_product X\n" +
                    "                                        WHERE hs_part='S'\n" +
                    "                                        and x.RECOMMENDED_RETURN_QTY>0\n" +
                    "                                        AND x.session_id = "+sessionId+"\n" +
                    "                                        AND x.site_id = "+site_id+"\n" +
                    "                                    ) ssp\n" +
                    "                            inner join SITE S on ssp.site_id = S.site_id\n" +
                    "                            inner join product P on P.product_id = ssp.product_id\n" +
                    "                            inner join hs_network_spoke hs on (spoke_site_id =ssp.site_id AND is_active=1)\n" +
                    "                            inner join hs_network_hierarchy_map hsmap\n" +
                    "                                       on (hsmap.heirarchy_id= ssp.level4_parent_id\n" +
                    "                                           AND hsmap.network_hub_id=hs.network_hub_id and hsmap.is_active=1)\n" +
                    "                )A\n" +//query "A" is to fetch the product and site data.
                    "                left outer join (select product_id,\n" +
                    "                                      sum(on_hand) on_hand,\n" + //only the on_hand of Hub site w.r.t the product
                    "                                      sum(max_stocking_qty) max_stocking_qty,\n" +//only the max_stocking_qty "HubMax" of the hub is taken into account
                    "                                      sum(occ_12) occ_12,\n" +//rollup of demand12 for the whole network w.r.t the product
                    "                                      sum(occ_24) occ_24\n" +//rollup of demand24 for the whole network w.r.t the product
                    "                               from (select sp.product_id,\n" +
                    "                                            case when hs_part ='H' then sp.on_hand else 0 end as on_hand,\n" +
                    "                                            case when hs_part ='H' then sp.max_stocking_qty else 0 end as max_stocking_qty,\n" +
                    "                                            nvl(sp.LOST_SALES_12_months,0)+nvl(sp.SUPERSEDE_12_MONTHS,0)+nvl(sp.SOLD_LAST_12_MONTHS,0) occ_12,\n" + //Demand12=sold_last_12+lost_sales_12+supersede_12
                    "                                            nvl(sp.LOST_SALES_24_months,0)+nvl(sp.SUPERSEDE_24_MONTHS,0)+nvl(sp.SOLD_LAST_24_MONTHS,0) occ_24\n" +
                    "                                     from site_product sp where sp.site_id in (\n" +
                    "                                        select distinct hub_site_id from hs_network_hub where network_hub_id in\n" +
                    "                                        (select network_hub_id from hs_network_spoke where spoke_site_id="+site_id+" and is_active=1)\n" +
                    "                                        union all\n" +
                    "                                        select distinct spoke_site_id from hs_network_spoke\n" +
                    "                                        where network_hub_id in (select network_hub_id from hs_network_spoke where spoke_site_id="+site_id+"\n" +
                    "                                                                                                               and is_active=1) ))\n" +
                    "                                            group by product_id)B\n" +//adding this "B" query to fetch the the demand at network Level ,i.e to for all the spokes and hub w.r.t that product_id
                    "                                        on b.PRODUCT_ID=a.product_id)))\n" +
                    " ORDER BY field_abbr ASC NULLS LAST\n" ;
        return sql;
    }

    @Override
    public void generateReviewPageExcel(SKUTableWidgetData skuTableWidgetData, User user, HttpServletResponse response)
            throws Exception {
        long start = System.currentTimeMillis();
        Stream<ExportColumn> columns = StreamSupport.stream(exportColumnRepository
                .findByExportTypeOrderByColumnOrder("reviewExcel")
                .spliterator(), false);

        SXSSFWorkbook workbook = new SXSSFWorkbook(100);
        workbook.setCompressTempFiles(true);
        Iterable<ExportColumn> selectedColumns = columns.collect(Collectors.toList());
        int[] columnWidths = StreamSupport.stream(selectedColumns.spliterator(), false)
                .map(ExportColumn::getWidth).mapToInt((item) -> item).toArray();
        Map<WorkbookStyle, CellStyle> styles = createWorkbookStyles(workbook);

        SqlRowSet sites = jdbcTemplate.queryForRowSet("SELECT SITE_ID, SITE_NAME FROM SITE WHERE SITE_ID IN (select site_id from session_site where session_id = "+skuTableWidgetData.getSessionId()+") order by SITE_SHORT_NAME");

        SXSSFSheet filterDetails = workbook.createSheet("Filter Details");
        String activeFilters = getActiveReviewFilterDetails(skuTableWidgetData, user);

        Font headerFont = workbook.createFont();
        headerFont.setBold(true);
        headerFont.setFontHeightInPoints((short) 14);

        CellStyle headerCellStyle = workbook.createCellStyle();
        headerCellStyle.setFont(headerFont);
        headerCellStyle.setWrapText(true);
        headerCellStyle.setAlignment(HorizontalAlignment.CENTER);

        Row headerRow = filterDetails.createRow(0);
        Cell cell = headerRow.createCell(0);
        cell.setCellValue("Filter Details");
        cell.setCellStyle(headerCellStyle);

        Row rows = filterDetails.createRow(1);
        cell = rows.createCell(0);
        cell.setCellValue(activeFilters);
        String sqlUpdateConnection =   "alter session set \"_optimizer_ansi_rearchitecture\"  =FALSE";
        jdbcTemplate.update(sqlUpdateConnection);
        try (Connection conn = Objects.requireNonNull(jdbcTemplate.getDataSource()).getConnection()) {


                 String sql = prepareReviewExcelQuery(skuTableWidgetData,  user);

                PreparedStatement ps = conn.prepareStatement(sql);
                ResultSet rs = ps.executeQuery();
                String sheetName = "SITES";

                Sheet sheet = createNewSheet(user, selectedColumns, workbook, columnWidths, sheetName);

                sheet.setAutoFilter(new CellRangeAddress(0, 0, 0, columnWidths.length - 1));
                int rowNum = 1; // we've already written 0 as the headers
                try {
                    while (rs.next()) {

                        Row row = sheet.createRow(rowNum);
                        int cellNum = 0;
                        for (ExportColumn column : selectedColumns) {
                            try {
                                
                                switch (JDBCType.valueOf(column.getColumnType())) {
                                    case NUMERIC:
                                        if (column.getDataFormat() == 9) {
                                            float floatVal = rs.getFloat(column.getColumnName());
                                            if (rs.wasNull()) {
                                                createEmptyCell(row, cellNum++);
                                            } else {
                                                createCell(row, floatVal, cellNum++,
                                                        styles.get(WorkbookStyle.fromDataFormat(column.getDataFormat())));
                                            }
                                        } else {
                                            int intVal = rs.getInt(column.getColumnName());
                                            if (rs.wasNull()) {
                                                createEmptyCell(row, cellNum++);
                                            } else {
                                                createCell(row, intVal, cellNum++,
                                                        styles.get(WorkbookStyle.fromDataFormat(column.getDataFormat())));
                                            }
                                        }
                                        break;
                                    case VARCHAR:
                                        createCell(row, rs.getString(column.getColumnName()), cellNum++,
                                                styles.get(WorkbookStyle.fromDataFormat(column.getDataFormat())));
                                        break;
                                    case DATE:
                                        Date date = rs.getDate(column.getColumnName());
                                        if (date == null) {
                                            createEmptyCell(row, cellNum++);
                                        } else {
                                            createCell(row, sdf.format(rs.getDate(column.getColumnName())), cellNum++,
                                                    styles.get(WorkbookStyle.fromDataFormat(column.getDataFormat())));
                                        }
                                        break;
                                    case FLOAT:
                                        float floatVal = rs.getFloat(column.getColumnName());
                                        if (rs.wasNull()) {
                                            createEmptyCell(row, cellNum++);
                                        } else {
                                            createCell(row, floatVal, cellNum++,
                                                    styles.get(WorkbookStyle.fromDataFormat(column.getDataFormat())));
                                        }
                                        break;
                                    default:
                                }
                            } catch (SQLException e) {
                                LOGGER.error("Invalid column " + column.getColumnName());
                            }
                        }
                        rowNum++;

                    }
                } catch (SQLException e) {
                    LOGGER.error("Error running SQL", e);
                }


        } catch (Exception e) {
            LOGGER.error("Error in generateReviewExcel() for sessionId:{}, message:{}", skuTableWidgetData.getSessionId(), e.getMessage());
        }
        workbook.write(response.getOutputStream());
        response.getOutputStream().flush();
        workbook.close();
        workbook.dispose();

        LOGGER.info("Review Excel generated in Time (ms): " + (System.currentTimeMillis() - start)+" for "+skuTableWidgetData.getSessionId());
    }

    public String prepareReviewExcelQuery(SKUTableWidgetData skuTableWidgetData, User user) {
        boolean isFinalized = isSessionFinalized(skuTableWidgetData.getSessionId());

        Integer dcCount = jdbcTemplate.queryForObject("select nvl(count(distinct s.product_availability_id),0) from site s where site_id in " +
                "(select site_id from session_site where session_id=" + skuTableWidgetData.getSessionId() + ")", Integer.class);
        Integer siteCount=jdbcTemplate.queryForObject("select count(distinct site_id) from session_site where session_id="+skuTableWidgetData.getSessionId(),Integer.class);
        String query3=(dcCount!=null && dcCount!=0)?" NVL(pdc.nat_product_views_12,0) ":" 0 ";
        String query = (dcCount!=null && dcCount == 1) ? "NVL(pdc.product_views_12,0) " : " 0 ";
        String query2=((skuTableWidgetData.getSiteIdsArray() != null && skuTableWidgetData.getSiteIdsArray().length ==1)||(siteCount!=null && siteCount==1))? " NVL(L.product_views_12,0) ":" 0 ";
        String sql = "SELECT distinct(ssp.product_id) as \"pId\", " +
                "ssp.session_id as \"sessionId\"," +
                "(SELECT (FIRST_NAME || ' ' || LAST_NAME) FROM PULSE_USER WHERE USER_ID IN (SELECT CREATED_USER_ID FROM PULSE_SESSION WHERE SESSION_ID = " + skuTableWidgetData.getSessionId() + ")) as \"createdBy\"," +
                "(SELECT CREATED_DATE FROM PULSE_SESSION WHERE SESSION_ID = " + skuTableWidgetData.getSessionId() + ") as \"createdDate\"," +
                "CASE when ssp.REVIEWED = 1 then 'Y' else 'N' end as \"reviewed\","  +
                "s.SITE_SHORT_NAME as \"site\","+
                " P.FIELD_ABBR as \"sku\"," + " P.PART_NUMBER as \"part\"," +
                "PRODUCT_DESC as \"description\"," +
                "P.TOP_APP as \"topApp\"," +
                "(ROUND(ssp.PTS*100)) as \"salesPotential\" , " +
                "ssp.Effective_PTS as \"EPTS\","+
                "(ROUND(ssp.DCR_PTS,2)) AS \"dcrPts\"," +
                "P.avg_age as \"avgAge\"," +
                "P.National_vio as \"nationalVIO\"," +
                "P.intro_dt as \"introDate\"," +
                "P.per_car as \"perCar\"," +
                "P.JBR_STD_PKG as \"stdPack\"," +
                "P.PRODUCT_CUBIC as \"approxSize\"," +
                "P.group_code as \"groupCode\"," +
                "ssp.price_wt_core as \"skucost\"," +
                "REGEXP_REPLACE(ssp.BUSINESS_RULES_APPLIED, '(.)(.)(.)', '\\1,\\2,\\3') as \"bri\", " +
                "nullif(SSP.OCCURANCE_12_MONTHS, 0) AS \"salesOccur12\"," +
                "nullif(SSP.OCCURANCE_24_MONTHS, 0) AS \"salesOccur24\", " +
                "nullif(SSP.SOLD_LAST_12_MONTHS, 0) AS \"sales12\"," +
                "nullif(SSP.SOLD_LAST_24_MONTHS, 0) AS \"sales24\", " +
                "nullif(ssp.TRANSFER_12_MONTHS, 0) as \"transfer12\"," +
                "nullif(ssp.TRANSFER_24_MONTHS, 0) as \"transfer24\"," +
                "  case when ( '"+ user.getAccessItemIds().contains(159)+"' = 'true' and '"+skuTableWidgetData.isCheckHubSpoke()+"' = 'true' ) then (select hdr.demand_12 from hsn_demand_rollup hdr " +
                " where hdr.hsn_network_id in(select hsn_network_id from hub_spoke_network_mapping where is_active=1 and  ssp.site_id=hsn_site_id ) " +
                " and  hdr.product_id=ssp.product_id " +
                " ) else null end as \"smartHubDemand12\", "+
                "  case when ( '" +user.getAccessItemIds().contains(159)+"' = 'true' and '"+skuTableWidgetData.isCheckHubSpoke()+"' = 'true' ) then (select hdr.demand_24 from hsn_demand_rollup hdr " +
                " where hdr.hsn_network_id in(select hsn_network_id from hub_spoke_network_mapping where is_active=1 and  ssp.site_id=hsn_site_id ) " +
                " and  hdr.product_id=ssp.product_id " +
                " ) else null end as \"smartHubDemand24\", "+
                "NVL(CASE " + "WHEN (" + "'true' = 'true'" +
                "AND 'true' = 'true'" + ")" + "THEN (" +
                "SELECT np.cnt_min_gtr_0 " + "FROM network_product np " +
                "WHERE np.network_id IN (" + "SELECT hsn_network_id " +
                "FROM hub_spoke_network_mapping " + "WHERE is_active = 1" +
                " AND ssp.site_id = hsn_site_id" + ")" +
                " AND np.product_id = ssp.product_id" +
                ")" + "  ELSE 0  " + " END,0) AS \"smartHubMin>0sites\","+

                "nullif(ssp.DC_SALES_LAST_12_MONTHS, 0) AS \"dcSales12\"," +
                "nullif(ssp.DC_SALES_LAST_24_MONTHS, 0) AS \"dcSales24\"," +
                "nullif(ssp.dcsales12 , 0) AS \"Old_dcSales12\"," +
                "nullif(ssp.dcsales24, 0) AS \"Old_dcSales24\"," +

                "nullif(SSP.LOST_SALES_12_MONTHS, 0) AS \"lost12\"," +
                "nullif(SSP.LOST_SALES_24_MONTHS, 0) AS \"lost24\"," +
                "nullif(ssp.SUPERSEDE_12_MONTHS, 0) as \"superseded12\", " +
                "nullif(ssp.SUPERSEDE_24_MONTHS, 0) as \"superseded24\"," +
                "nullif(SSP.ON_HAND,0) AS \"onHand\"," +
                "ssp.ORDER_QTY AS \"order\"," +
                "ssp.RETURN_QTY AS \"return\"," +
                "ssp.keep_qty as \"keep\"," +
                "CASE when po.IS_ACTIVE = 1 then 'Y' else 'N' end as \"blockOrderSugg\"," +
                "nullif(SSP.INITIAL_STOCK_AMOUNT,0) AS \"suggStkgQty\"," +
                "nullif(SSP.MIN_STOCKING_QTY, 0) AS \"min\", " +
                "nullif(SSP.MAX_STOCKING_QTY, 0) AS \"max\"," +
                "CASE WHEN ssp.order_qty > 0 THEN (ssp.price_wt_core * ssp.order_qty)  WHEN ssp.return_qty > 0 THEN -(ssp.price_wt_core * ssp.return_qty) END AS \"extPrice\"," +
                "ssp.vio as \"vio\"," +
                "nullif(ssp.PEER_SALES_12, 0) as \"peerStoresSelling\"," +
                "round(round(NVL(ssp.CLUSTER_PCT_SALE,0),3)*100) as \"peerSales\"," +
                "round((SSP.PCT_MARKET*100),2) AS \"pctMrkt\"," +
                "last_sale_dt as \"posLastSaleDate\"," +
                "INITIAL_STOCKING_DT AS \"firstStockDate\"," +
                "ssp.DISPLAY_QTY as \"displayQty\"," +
                "ssp.KEEP_FLAG as \"keepFlag\" ,"+
                query3+" AS \"prolinkNational\"," + query + " as \"prolinkDc\","+query2+" as \"prolinkSite\"";

        if (skuTableWidgetData.isDcStocking() &&  ("stockedatdc").equals(skuTableWidgetData.getSelectedStockNonStockOption().trim())) {
            sql += " FROM (SELECT x.*, dc_sp.dcSales12, dc_sp.dcSales24 FROM "+ (isFinalized ? "session_site_product_finalized" : "session_site_product")+" x,(SELECT product_id,min_stocking_qty,max_stocking_qty," +
                    " nullif(DC_SALES_LAST_12_MONTHS, 0) AS dcSales12, nullif(DC_SALES_LAST_24_MONTHS, 0) AS dcSales24 FROM site_product WHERE site_id IN  " +
                    " (SELECT site_id FROM site WHERE site_type_code = 'DC' AND product_availability_id IN " +
                    " (SELECT product_availability_id FROM site WHERE site_id IN " +
                    " (SELECT site_id FROM session_site WHERE session_id = " + skuTableWidgetData.getSessionId() + " )))) dc_sp WHERE " +
                    " ( x.product_id = dc_sp.product_id(+) AND Nvl(dc_sp.max_stocking_qty, 0) > 0 ) ) ssp, site s,";
        } else if (skuTableWidgetData.isDcStocking()  && ("nonstockedatdc").equals(skuTableWidgetData.getSelectedStockNonStockOption().trim())) {
            sql += " FROM (SELECT x.*, dc_sp.dcSales12, dc_sp.dcSales24 FROM "+ (isFinalized ? "session_site_product_finalized" : "session_site_product")+" x,(SELECT product_id,min_stocking_qty,max_stocking_qty, " +
                    " nullif(DC_SALES_LAST_12_MONTHS, 0) AS dcSales12, nullif(DC_SALES_LAST_24_MONTHS, 0) AS dcSales24 FROM site_product WHERE site_id IN  " +
                    " (SELECT site_id FROM site WHERE site_type_code = 'DC' AND product_availability_id IN " +
                    " (SELECT product_availability_id FROM site WHERE site_id IN " +
                    " (SELECT site_id FROM session_site WHERE session_id = " + skuTableWidgetData.getSessionId() + " )))) dc_sp WHERE " +
                    " ( x.product_id = dc_sp.product_id(+) AND NVL(dc_sp.min_stocking_qty, 0) = 0 ) ) ssp, site s,";
        } else {
          sql += " FROM (SELECT x.*,dc_sp.DC_siteId, dc_sp.dcSales12, dc_sp.dcSales24 FROM "+ (isFinalized ? "session_site_product_finalized" : "session_site_product")+" x,(SELECT rn.product_id product_id, " +
                  "nullif(rn.SALES_LAST_12_MONTHS, 0) AS dcSales12, nullif(rn.SALES_LAST_24_MONTHS, 0) AS dcSales24,rn.dc_site_id as DC_siteId,sn.site_id as site_id FROM store_sales_rollup_new rn, ( " +
                  " SELECT s.site_id dc_id,sss.site_id site_id" +
                  " FROM site s,(SELECT s.product_availability_id,ss.site_id  " +
                  "FROM site s, session_site ss " +
                  "WHERE s.site_id=ss.site_id " +
                  " and ss.session_id= " + skuTableWidgetData.getSessionId() + " ) sss" +
                  " WHERE s.site_type_code = 'DC'" +
                  "AND s.product_availability_id=sss.product_availability_id) sn " +
                  "WHERE rn.dc_site_id= sn.dc_id) dc_sp " +
                  "WHERE  x.Session_id = " + skuTableWidgetData.getSessionId() + " AND  x.product_id = dc_sp.product_id(+) " +
            "and x.site_id=dc_sp.site_id) ssp ," +
                  "site s, " ;
        }
        // lost sales
        if (skuTableWidgetData.isSoldNotStocked()) {
            sql += " SITE_PARAMETER SP, ";
        }

        // Fleets Filter
        if (skuTableWidgetData.getFleetGroupIdsArray() != null
                && skuTableWidgetData.getFleetGroupIdsArray().length > 0) {
            sql += " SESSION_SITE_FLEET_PRODUCTS_V flt, ";
        }

        // Similar Parts Filter
        if (skuTableWidgetData.isSimilarParts()) {
            if (("duplicationRisk").equals(skuTableWidgetData.getSimPartsOption()))
                sql += " (select distinct sp_.product_id, (sp_.SIMILAR_PRODUCT_ID) similar_product_id, order_qty from "+ (isFinalized ? "session_site_product_finalized" : "session_site_product")+" ssp_, SIMILAR_PRODUCT sp_" +
                        " where ssp_.SESSION_ID = "+skuTableWidgetData.getSessionId()+" and ssp_.site_id in (select site_id from session_site where session_id = "+skuTableWidgetData.getSessionId()+" )" +
                        " and sp_.similar_PRODUCT_ID = ssp_.PRODUCT_ID) sim , "+
                        " (SELECT DISTINCT sp.product_id, sim.similar_product_id," +
                        " SUM( CASE WHEN sim_sp.min_stocking_qty > 0 THEN 1 ELSE 0 END) OVER (PARTITION BY sp.product_id) coverage" +
                        " FROM site_product sp , similar_product sim, site_product sim_sp" +
                        " WHERE sp.site_id IN (SELECT site_id FROM session_site WHERE session_id = "+skuTableWidgetData.getSessionId()+")" +
                        " AND sim_sp.site_id IN (SELECT site_id FROM session_site WHERE session_id = "+skuTableWidgetData.getSessionId()+")" +
                        " AND sp.product_id = sim.product_id AND sim.similar_product_id = sim_sp.product_id ) similarproduct , ";
            else if(("coverageRisk").equals(skuTableWidgetData.getSimPartsOption()))
                sql += " (SELECT DISTINCT sp.product_id, SUM( CASE WHEN sim_sp.min_stocking_qty > 0 THEN 1 ELSE 0 END) OVER (PARTITION BY sp.product_id) coverage" +
                        " FROM site_product sp , similar_product sim, site_product sim_sp" +
                        " WHERE sp.site_id IN (SELECT site_id FROM session_site WHERE session_id = "+skuTableWidgetData.getSessionId()+")" +
                        " AND sim_sp.site_id IN (SELECT site_id FROM session_site WHERE session_id = "+skuTableWidgetData.getSessionId()+")" +
                        " AND sp.product_id = sim.product_id AND sim.similar_product_id = sim_sp.product_id ) similarproduct , ";
            else sql += " SIMILAR_PRODUCT similarproduct, (SELECT DISTINCT product_id FROM site_product WHERE site_id IN  (SELECT site_id FROM session_site" +
                        " WHERE session_id = "+skuTableWidgetData.getSessionId()+")) sp , ";
        }

        sql += " product p " +
                "FULL OUTER JOIN (SELECT * from PROLINK_ROLLUP L where L.is_dc=0 ) L ON (L.product_id = p.PRODUCT_ID and L.site_id IN (select site_id from session_site where session_id = "+skuTableWidgetData.getSessionId()+")) ";
        if(dcCount!=null)
            sql+=" FULL outer JOIN (SELECT * from PROLINK_ROLLUP pdc  WHERE pdc.is_dc=1) pdc ON (pdc.product_id = p.PRODUCT_ID " +
                    " AND pdc.site_id in ( " +
                    " SELECT DISTINCT dc.site_id FROM site s,site dc WHERE s.product_availability_id = dc.product_availability_id " +
                    " AND dc.site_type_code = 'DC' AND s.site_id IN ( SELECT site_id " +
                    " FROM session_site WHERE session_id = "+skuTableWidgetData.getSessionId()+" )))";

        sql+=    " full outer join (SELECT * FROM PRODUCT_OVERRIDE WHERE override_id = 2 AND site_id IN (select site_id from session_site where session_id = "+skuTableWidgetData.getSessionId()+")) po on (po.product_id = p.PRODUCT_ID)" +
                " WHERE ssp.site_id = s.site_id AND p.product_id = ssp.product_id AND ssp.session_id = " + skuTableWidgetData.getSessionId() +
                " AND ssp.site_id IN (select site_id from session_site where session_id = "+skuTableWidgetData.getSessionId()+")";

        // Hide system followers filter
        if (skuTableWidgetData.isHideSystemFollowers()) {
            sql += " and nvl(ssp.system_recommended, 'N') = 'N' ";
        }

        // New Number filter
        if (skuTableWidgetData.isNewNumber()) {
            sql += " and ssp.reason_id = 8 ";
        }

        // New Parts filter
        if (skuTableWidgetData.isProspectParts()) {
            sql += " and ssp.site_id=s.site_id  ";
        }


        //FirstStockDate Filter
        if(skuTableWidgetData.getFirstStockToDt() != null && skuTableWidgetData.getFirstStockFromDt()!=null ) {
            sql += " and ssp.initial_stocking_dt between '" + skuTableWidgetData.getFirstStockFromDt() + "' and '" + skuTableWidgetData.getFirstStockToDt()+"' " ;
        }
        else if (skuTableWidgetData.getFirstStockFromDt() != null) {
            sql += " and ssp.initial_stocking_dt  >= '" + skuTableWidgetData.getFirstStockFromDt()+"'";
        }
        else if (skuTableWidgetData.getFirstStockToDt() != null ) {
            sql += " and ssp.initial_stocking_dt  <='" + skuTableWidgetData.getFirstStockToDt()+"'";
        }


        // expensiveItem
        if (skuTableWidgetData.isExpensiveItem()) {
            sql += " and ORDER_QTY*PRICE_WT_CORE > 500  ";
        }

        //dcSales Filter
        if(skuTableWidgetData.getDcSalesMonth() != null && user.getAccessItemIds().contains(152)) {
            //dcSales 12 Filter
            if (skuTableWidgetData.getDcSalesMonth() == 12) {
                sql += " and ssp.dc_sales_last_12_months  ";
                if (skuTableWidgetData.getDcSalesMin() != null && skuTableWidgetData.getDcSalesMax() != null && skuTableWidgetData.getDcSalesMax() >= skuTableWidgetData.getDcSalesMin()) {
                    sql += "  between " + skuTableWidgetData.getDcSalesMin() + " and " + skuTableWidgetData.getDcSalesMax() ;
                } else if (skuTableWidgetData.getDcSalesMax() != null && skuTableWidgetData.getDcSalesMin() == null) {
                    sql += " <= " + skuTableWidgetData.getDcSalesMax();
                } else if (skuTableWidgetData.getDcSalesMin() != null && skuTableWidgetData.getDcSalesMax() == null) {
                    sql += " >= " + skuTableWidgetData.getDcSalesMin();
                }
            }
            //DcSales 24 Filter
            if (skuTableWidgetData.getDcSalesMonth() == 24) {
                sql += " and ssp.dc_sales_last_24_months  ";
                if (skuTableWidgetData.getDcSalesMin() != null && skuTableWidgetData.getDcSalesMax() != null && skuTableWidgetData.getDcSalesMax() >= skuTableWidgetData.getDcSalesMin()) {
                    sql += "  between " + skuTableWidgetData.getDcSalesMin() + " and " + skuTableWidgetData.getDcSalesMax() ;
                } else if (skuTableWidgetData.getDcSalesMax() != null && skuTableWidgetData.getDcSalesMin() == null) {
                    sql += " <= " + skuTableWidgetData.getDcSalesMax();
                } else if (skuTableWidgetData.getDcSalesMin() != null && skuTableWidgetData.getDcSalesMax() == null) {
                    sql += " >= " + skuTableWidgetData.getDcSalesMin();
                }
            }
        }

        //*dcSales Filter
        if (skuTableWidgetData.getDcWideSalesMonth() != null) {
            //*dcSales 12 Filter
            if (skuTableWidgetData.getDcWideSalesMonth() == 12) {
                if (skuTableWidgetData.getDcWideSalesMin() != null && skuTableWidgetData.getDcWideSalesMax() != null && skuTableWidgetData.getDcWideSalesMax() >= skuTableWidgetData.getDcWideSalesMin()) {
                    sql += "and ssp.product_id in (Select sp.product_id from STORE_SALES_ROLLUP_NEW sp where sp.dc_site_id = " +
                            "    (select s.site_id from site s where s.site_type_code='DC' " +
                            "      and s.product_availability_id= (select product_availability_id from site where site_id=ssp.site_id))" +
                            "      and sp.SALES_LAST_12_MONTHS BETWEEN "+skuTableWidgetData.getDcWideSalesMin() +
                            "AND "+ skuTableWidgetData.getDcWideSalesMax() +" )";

                } else if (skuTableWidgetData.getDcWideSalesMax() != null && skuTableWidgetData.getDcWideSalesMin() == null) {
                    sql += "and ssp.product_id in (Select sp.product_id from STORE_SALES_ROLLUP_NEW sp where sp.dc_site_id = " +
                            "    (select s.site_id from site s where s.site_type_code='DC' " +
                            "      and s.product_availability_id= (select product_availability_id from site where site_id=ssp.site_id))" +
                            "      and sp.SALES_LAST_12_MONTHS  <= "+skuTableWidgetData.getDcWideSalesMax() +" )";

                } else if (skuTableWidgetData.getDcWideSalesMin() != null && skuTableWidgetData.getDcWideSalesMax() == null) {
                    sql +="and ssp.product_id in (Select sp.product_id from STORE_SALES_ROLLUP_NEW sp where sp.dc_site_id = " +
                            "    (select s.site_id from site s where s.site_type_code='DC' " +
                            "      and s.product_availability_id= (select product_availability_id from site where site_id=ssp.site_id))" +
                            "      and sp.SALES_LAST_12_MONTHS  >= " + skuTableWidgetData.getDcWideSalesMin()+" )";

                }
            }
            //*DcSales 24 Filter
            if (skuTableWidgetData.getDcWideSalesMonth() == 24) {
                if (skuTableWidgetData.getDcWideSalesMin() != null && skuTableWidgetData.getDcWideSalesMax() != null && skuTableWidgetData.getDcWideSalesMax() >= skuTableWidgetData.getDcWideSalesMin()) {
                    sql += "and ssp.product_id in (Select sp.product_id from STORE_SALES_ROLLUP_NEW sp where sp.dc_site_id = " +
                            "    (select s.site_id from site s where s.site_type_code='DC' " +
                            "      and s.product_availability_id= (select product_availability_id from site where site_id=ssp.site_id))" +
                            "      and sp.SALES_LAST_24_MONTHS BETWEEN "+skuTableWidgetData.getDcWideSalesMin() +
                            "AND "+ skuTableWidgetData.getDcWideSalesMax() +" )";
                } else if (skuTableWidgetData.getDcWideSalesMax() != null && skuTableWidgetData.getDcWideSalesMin() == null) {
                    sql += "and ssp.product_id in (Select sp.product_id from STORE_SALES_ROLLUP_NEW sp where sp.dc_site_id = " +
                            "    (select s.site_id from site s where s.site_type_code='DC' " +
                            "      and s.product_availability_id= (select product_availability_id from site where site_id=ssp.site_id))" +
                            "      and sp.SALES_LAST_24_MONTHS  <= "+skuTableWidgetData.getDcWideSalesMax() +" )";



                } else if (skuTableWidgetData.getDcWideSalesMin() != null && skuTableWidgetData.getDcWideSalesMax() == null) {

                    sql +="and ssp.product_id in (Select sp.product_id from STORE_SALES_ROLLUP_NEW sp where sp.dc_site_id = " +
                            "    (select s.site_id from site s where s.site_type_code='DC' " +
                            "      and s.product_availability_id= (select product_availability_id from site where site_id=ssp.site_id))" +
                            "      and sp.SALES_LAST_24_MONTHS  >= " + skuTableWidgetData.getDcWideSalesMin()+" )";
                }
            }

        }

        //hub demand Filter
        if (skuTableWidgetData.getHubDemandMonth() != null && user.getAccessItemIds().contains(159)) {
            //hub demand 12 Filter
            if (skuTableWidgetData.getHubDemandMonth() == 12) {
                if (skuTableWidgetData.getHubDemandMin() != null && skuTableWidgetData.getHubDemandMax() != null && skuTableWidgetData.getHubDemandMax() >= skuTableWidgetData.getHubDemandMin()) {
                    sql += " AND ssp.product_id IN ( SELECT hdr.product_id FROM hsn_demand_rollup hdr " +
                            " WHERE hdr.hsn_network_id in(select hsn_network_id from hub_spoke_network_mapping where is_active=1 and  ssp.site_id=hsn_site_id) " +
                            " AND hdr.demand_12 BETWEEN  "+skuTableWidgetData.getHubDemandMin() +
                            " AND "+ skuTableWidgetData.getHubDemandMax() +" ) ";

                } else if (skuTableWidgetData.getHubDemandMax() != null && skuTableWidgetData.getHubDemandMin() == null) {
                    sql += " AND ssp.product_id IN ( SELECT hdr.product_id FROM hsn_demand_rollup hdr " +
                            " WHERE hdr.hsn_network_id in(select hsn_network_id from hub_spoke_network_mapping where is_active=1 and  ssp.site_id=hsn_site_id) " +
                            " AND hdr.demand_12 <= "+skuTableWidgetData.getHubDemandMax() +" ) ";



                } else if (skuTableWidgetData.getHubDemandMin() != null && skuTableWidgetData.getHubDemandMax() == null) {
                    sql += " AND ssp.product_id IN ( SELECT hdr.product_id FROM hsn_demand_rollup hdr " +
                            " WHERE hdr.hsn_network_id in(select hsn_network_id from hub_spoke_network_mapping where is_active=1 and  ssp.site_id=hsn_site_id) " +
                            " AND hdr.demand_12 >= "+skuTableWidgetData.getHubDemandMin() +" ) ";


                }
            }
            //hub demand 24 Filter
            if (skuTableWidgetData.getHubDemandMonth() == 24) {
                if (skuTableWidgetData.getHubDemandMin() != null && skuTableWidgetData.getHubDemandMax() != null && skuTableWidgetData.getHubDemandMax() >= skuTableWidgetData.getHubDemandMin()) {
                    sql += " AND ssp.product_id IN ( SELECT hdr.product_id FROM hsn_demand_rollup hdr " +
                            " WHERE hdr.hsn_network_id in(select hsn_network_id from hub_spoke_network_mapping where is_active=1 and  ssp.site_id=hsn_site_id) " +
                            " AND hdr.demand_24 BETWEEN  "+skuTableWidgetData.getHubDemandMin() +
                            " AND "+ skuTableWidgetData.getHubDemandMax() +" ) ";

                } else if (skuTableWidgetData.getHubDemandMax() != null && skuTableWidgetData.getHubDemandMin() == null) {
                    sql += " AND ssp.product_id IN ( SELECT hdr.product_id FROM hsn_demand_rollup hdr " +
                            " WHERE hdr.hsn_network_id in(select hsn_network_id from hub_spoke_network_mapping where is_active=1 and  ssp.site_id=hsn_site_id) " +
                            " AND hdr.demand_24 <= "+skuTableWidgetData.getHubDemandMax() +" ) ";

                } else if (skuTableWidgetData.getHubDemandMin() != null && skuTableWidgetData.getHubDemandMax() == null) {
                    sql += " AND ssp.product_id IN ( SELECT hdr.product_id FROM hsn_demand_rollup hdr " +
                            " WHERE hdr.hsn_network_id in(select hsn_network_id from hub_spoke_network_mapping where is_active=1 and  ssp.site_id=hsn_site_id) " +
                            " AND hdr.demand_24 >= "+skuTableWidgetData.getHubDemandMin() +" ) ";

                }
            }

        }





        // largeOrderQty
        if (skuTableWidgetData.isLargeOrderQty()) {
            sql += " and ( ORDER_QTY > STD_PKG and ORDER_QTY > (PER_CAR * 2) ) ";
        }

        // Lost Sales
        if (skuTableWidgetData.isSoldNotStocked()) {
            sql += " and sp.site_id in (select site_id from session_site where session_id = "+skuTableWidgetData.getSessionId()+" ) "
                    + "and ssp.SITE_ID=sp.SITE_ID and SSP.LEVEL4_PARENT_ID=sp.HIERARCHY_ID and sp.SITE_ID=s.SITE_ID "
                    + " and (ssp.effective_pts = 230 and ssp.RECOMMENDED_ORDER_QTY > 0) ";
        }


        // obsolete filter
        if (skuTableWidgetData.isObsoleteSkus()) {
            sql += " and ssp.ON_HAND>0 and ssp.RECOMMENDED_RETURN_QTY>0 and ssp.CLASS = 'O' ";
        }

        // App / Non-App Filter
        if (skuTableWidgetData.isAppNonApp()) {
            if (("nonApplication").equals(skuTableWidgetData.getAppNonAppOption()))
                sql += " AND P.TOP_APP IS NULL ";
            else if (("application").equals(skuTableWidgetData.getAppNonAppOption()))
                sql += " AND  P.TOP_APP IS NOT NULL ";
        }

        // Reviewed
        if (skuTableWidgetData.getIsReviewed() != null && skuTableWidgetData.getIsReviewed()) {
            sql += " and ssp.REVIEWED=1 ";
        } else if (skuTableWidgetData.getIsReviewed() != null && !skuTableWidgetData.getIsReviewed()) {
            sql += " and NVL(ssp.REVIEWED, 0) = 0 ";
        }

        // fleets filter
        if (skuTableWidgetData.getFleetGroupIdsArray() != null
                && skuTableWidgetData.getFleetGroupIdsArray().length > 0) {
            sql += " AND ssp.session_id = flt.session_id AND ssp.site_id = flt.site_id AND ssp.product_id = flt.product_id and flt.fleet_id in ("
                    + java.util.Arrays.toString(skuTableWidgetData.getFleetGroupIdsArray()).replace("[", "")
                    .replace("]", "")
                    + ") and EFFECTIVE_PTS=500 "; // EFFECTIVE_PTS=500
        }

        // Display Flag filter
        if (skuTableWidgetData.isDisplayFlag()) {
            sql += " and ssp.DISPLAY_QTY > 0 ";
        }

        // Keep Flag filter
        if (skuTableWidgetData.isKeepFlag()) {
            sql += " and ssp.KEEP_FLAG ='Y' ";
        }

        //Similar Parts filter
        if(skuTableWidgetData.isSimilarParts()){
            if(("duplicationRisk").equals(skuTableWidgetData.getSimPartsOption()))
                sql += " and similarproduct.PRODUCT_ID = ssp.PRODUCT_ID and (similarproduct.coverage > 0 OR sim.order_qty > 0)" +
                        " and sim.product_id = ssp.product_id and (ssp.RECOMMENDED_ORDER_QTY > 0 or ssp.MIN_STOCKING_QTY > 0) ";
            else if(("coverageRisk").equals(skuTableWidgetData.getSimPartsOption()))
                sql+=" and similarproduct.PRODUCT_ID = ssp.PRODUCT_ID and similarproduct.coverage = 0 and NVL(ssp.MIN_STOCKING_QTY, 0) = 0 ";
            else sql += " and similarproduct.PRODUCT_ID = ssp.PRODUCT_ID AND similarproduct.similar_product_id = sp.product_id ";
        }

        //Block Order Suggestions
        if (skuTableWidgetData.isBlockOrderSuggestion()) {
            sql += " and ssp.product_id in (SELECT product_id from PRODUCT_OVERRIDE WHERE OVERRIDE_ID = 2 AND IS_ACTIVE = 1 " +
                    "AND SITE_ID IN (SELECT SITE_ID FROM SESSION_SITE WHERE SESSION_ID = " + skuTableWidgetData.getSessionId() + ")) ";
        }

        // LOW VEHICLE COUNT filter -- PUL -7328 - Removed 'and ssp.recommended_order_qty > 0'
        if (skuTableWidgetData.getLowVehicleCount() != null) {
            if (skuTableWidgetData.getLowVehicleOperator() != null && (skuTableWidgetData.getLowVehicleOperator().trim().equals("<") || skuTableWidgetData.getLowVehicleOperator().trim().equals(">"))) {
                sql += " and nvl(ssp.VIO,0) >= 0 AND nvl(ssp.VIO,0) " + skuTableWidgetData.getLowVehicleOperator().trim() + " " + skuTableWidgetData.getLowVehicleCount() + " AND p.top_app IS NOT NULL ";
            } else {
                sql += " and nvl(ssp.VIO,0) >= 0 AND nvl(ssp.VIO,0) < " + skuTableWidgetData.getLowVehicleCount() + " AND p.top_app IS NOT NULL ";
            }
            sql += " ";
        }

        // Supersedes filter
        if (skuTableWidgetData.isSupersedes()) {
            sql += " and ssp.product_id in (select SURVIVING_PRODUCT_ID from SUPERSEDE_PRODUCT where SURVIVING_PRODUCT_ID=ssp.product_id ) ";
        }

        // Specials filter
        if (skuTableWidgetData.isSpecials()) {
            sql += " and ssp.reason_id = 3  ";
        }

        // Other filter
        if (skuTableWidgetData.isOther()) {
            sql += " and ssp.ON_HAND>0 and ssp.RECOMMENDED_RETURN_QTY>0 and ssp.INITIAL_STOCKING_DT is null and ssp.class != 'O'  ";
        }

        // Overstock Filter
        if (skuTableWidgetData.isOverstock()) {
            sql += " and ssp.recommended_return_qty > 0  AND  ssp.recommended_return_qty < ssp.on_hand  ";
        }

        // 24 and Out filter
        if (skuTableWidgetData.isTwentyFourAndOut()) {
            sql += " and ssp.ON_HAND>0 and ssp.RECOMMENDED_RETURN_QTY>0 and (ssp.RETURN_QTY = ssp.ON_HAND) and ssp.MIN_STOCKING_QTY>0 and ssp.INITIAL_STOCKING_DT is not null and ssp.class != 'O' ";
        }

        // Systems filter
        if (skuTableWidgetData.isSystem()) {
            sql += " and nvl(ssp.system_recommended, 'N') = 'Y' and (ssp.ORDER_QTY > 0 OR ssp.RECOMMENDED_ORDER_QTY > 0) ";
        }

        // Orders and Returns filters
        String orderFilter = "";
        if (("recommended").equals(skuTableWidgetData.getOrderOption())) {
            orderFilter = "(ssp.RECOMMENDED_ORDER_QTY > 0)";
        } else if (("accepted").equals(skuTableWidgetData.getOrderOption())) {
            orderFilter = "ssp.ORDER_QTY > 0";
        } else if (("declined").equals(skuTableWidgetData.getOrderOption())) {
            orderFilter = "(ssp.RECOMMENDED_ORDER_QTY <> 0 and ssp.ORDER_QTY = 0)";
        }

        String returnFilter = "";
        if (("recommended").equals(skuTableWidgetData.getReturnOption())) {
            returnFilter = "(ssp.RECOMMENDED_RETURN_QTY > 0)";
        } else if (("accepted").equals(skuTableWidgetData.getReturnOption())) {
            returnFilter = "(ssp.RETURN_QTY = ssp.RECOMMENDED_RETURN_QTY and ssp.RETURN_QTY > 0)";
        } else if (("declined").equals(skuTableWidgetData.getReturnOption())) {
            returnFilter = "(ssp.RECOMMENDED_RETURN_QTY <> 0 and ssp.RETURN_QTY = 0)";
        }

        if (skuTableWidgetData.isReturn() && skuTableWidgetData.isOrder()) {
            sql += " and (" + orderFilter + " OR " + returnFilter + ") ";
        } else if (skuTableWidgetData.isReturn()) {
            sql += " and " + returnFilter;
        } else if (skuTableWidgetData.isOrder()) {
            sql += " and " + orderFilter;
        }

        /** StoreInventory PUL-7910*/
        if (skuTableWidgetData.isStoreInventory()== true && skuTableWidgetData.getSelectedStoreInventoryOption() !=null) {
            if (("allStockedOnhand").equals(skuTableWidgetData.getSelectedStoreInventoryOption().trim())) {
                sql +="  and NVL(ssp.MIN_STOCKING_QTY, 0) > 0 AND NVL(ssp.ON_HAND, 0) > 0";
            } else if (("grt0onhand").equals(skuTableWidgetData.getSelectedStoreInventoryOption().trim())) {
                sql+=" and NVL(ssp.ON_HAND, 0) > 0" ;
            } else if (("grt0onandwithnoMinMax").equals(skuTableWidgetData.getSelectedStoreInventoryOption().trim())) {
                sql+=" and NVL(ssp.ON_HAND, 0) > 0 AND (NVL(ssp.MIN_STOCKING_QTY, 0) = 0 OR NVL(ssp.max_stocking_qty, 0) = 0)";
            } else if (("grt0min").equals(skuTableWidgetData.getSelectedStoreInventoryOption().trim())) {
                sql+=" and  NVL(ssp.MIN_STOCKING_QTY, 0) > 0";
            } else if (("grt0min0withnoOnHand").equals(skuTableWidgetData.getSelectedStoreInventoryOption().trim())) {
                sql+=" and NVL(ssp.MIN_STOCKING_QTY, 0) > 0 AND NVL(ssp.ON_HAND, 0) = 0";
            } else if (("grt0excludeOnHand").equals(skuTableWidgetData.getSelectedStoreInventoryOption().trim())) {
                sql+= " and  NOT NVL(ssp.ON_HAND, 0) > 0";
            } else if (("grt0excludeMin").equals(skuTableWidgetData.getSelectedStoreInventoryOption().trim())) {
                sql+=" and NOT NVL(ssp.MIN_STOCKING_QTY, 0) > 0";
            }
        }

        // Hub & Spoke Filter
        //viewData
        if (("hubOnly").equals(skuTableWidgetData.getHubSpokeOption())) {
            sql += " and ssp.HS_PART = 'H' ";
        } else if (("spokeOnly").equals(skuTableWidgetData.getHubSpokeOption())) {
            sql += " and ssp.HS_PART = 'S' ";

        } else if (("HSOnly").equals(skuTableWidgetData.getHubSpokeOption())) {
            sql += " and ssp.HS_PART IS NOT NULL ";

        } else if (("hideHS").equals(skuTableWidgetData.getHubSpokeOption())) {
            sql += " and ssp.HS_PART IS NULL ";

        }

        // New Parts filter
        if (skuTableWidgetData.isProspectParts()) {
            sql += " and ((ssp.FORECAST_MODEL_ID IN (0,200, 20000) AND ssp.RECOMMENDED_ORDER_QTY > 0) "
                    + "or (ssp.FORECAST_MODEL_ID IN (0,200, 20000) AND ssp.RECOMMENDED_ORDER_QTY > 0 and s.SITE_TYPE_CODE='DC' and ssp.EFFECTIVE_PTS > 0.25 ))  ";
        }

        // filter by hierarchy
        if (skuTableWidgetData.getHierarchyId() > 0) {
            sql += " and ssp.LEVEL4_PARENT_ID in (select HIERARCHY_ID from product_hierarchy where HIERARCHY_LEVEL=4 "
                    + "start with HIERARCHY_ID = " + skuTableWidgetData.getHierarchyId()
                    + " connect by prior HIERARCHY_ID = PARENT_ID) ";
        }

        if (skuTableWidgetData.getHighSkuEditHierarchyId() != null) {
            sql += " and ssp.LEVEL4_PARENT_ID = " + skuTableWidgetData.getHighSkuEditHierarchyId()
                    + " and ssp.PRODUCT_ID IN (select product_id from "+ (isFinalized ? "session_site_product_finalized" : "session_site_product")
                    + "	   where session_id = " + skuTableWidgetData.getSessionId()
                    + "       and site_id in (select site_id from session_site where session_id = "
                    + skuTableWidgetData.getSessionId() + ") " + "	   group by product_id "
                    + "       having (case when max(case when recommended_return_qty > 0 and return_qty < recommended_return_qty then 1 else 0 end) = 1 then 1 "
                    + "	   when max(case when recommended_order_qty > 0 then 1 else 0 end) = 1 and "
                    + "            max(case when order_qty > 0 then 1 else 0 end) = 0 and "
                    + "            max(case when min_stocking_qty > 0 and (keep_qty is null or keep_qty > 0) then 1 else 0 end) = 0 then 1 "
                    + "       when max(case when recommended_order_qty > 0 then 1 else 0 end) = 0  and "
                    + "            max(case when order_qty > 0 then 1 else 0 end) = 1  then 1 "
                    + "       else 0 end) = 1 ) ";
        }

        if (skuTableWidgetData.getHighSkuEdit() != null) {
            sql += " and ssp.PRODUCT_ID IN (select product_id from "+ (isFinalized ? "session_site_product_finalized" : "session_site_product")
                    + "	   where session_id = " + skuTableWidgetData.getSessionId()
                    + "       and site_id in (select site_id from session_site where session_id = "
                    + skuTableWidgetData.getSessionId() + ") " + "	   group by product_id "
                    + "       having (case when max(case when recommended_return_qty > 0 and return_qty < recommended_return_qty then 1 else 0 end) = 1 then 1 "
                    + "	   when max(case when recommended_order_qty > 0 then 1 else 0 end) = 1 and "
                    + "            max(case when order_qty > 0 then 1 else 0 end) = 0 and "
                    + "            max(case when min_stocking_qty > 0 and (keep_qty is null or keep_qty > 0) then 1 else 0 end) = 0 then 1 "
                    + "       when max(case when recommended_order_qty > 0 then 1 else 0 end) = 0  and "
                    + "            max(case when order_qty > 0 then 1 else 0 end) = 1  then 1 "
                    + "       else 0 end) = 1 ) ";
        }

        // Line Abbr filter
        if (skuTableWidgetData.getLineAbbr() != null) {
            sql += " and P.FIELD_ABBR IN ( " + skuTableWidgetData.getLineAbbr() + ")";

        }

        // Group Code
        if (skuTableWidgetData.getGroupCode() >= 0) {
            sql += " and P.GROUP_CODE = " + skuTableWidgetData.getGroupCode() + " ";
        }

        // Avg Age filter
        if (skuTableWidgetData.getAvgAgeMin() != null && skuTableWidgetData.getAvgAgeMax() != null) {
            sql += " and p.AVG_AGE between " + skuTableWidgetData.getAvgAgeMin() + " and "
                    + skuTableWidgetData.getAvgAgeMax() + " ";
        }

        // Part Number filter
        if (skuTableWidgetData.getPartNumber() != null) {
            String partNumber = skuTableWidgetData.getPartNumber().toUpperCase().replaceAll("[^A-Z0-9]+", "");
            if (partNumber.length() > 0) {
                sql += " and P.PART_NUMBER = '" + partNumber + "'";
            }
        }

        //SalesPotential Filter
        if (skuTableWidgetData.getPtsMin() != null && skuTableWidgetData.getPtsMax() != null) {
            sql += " and ROUND(ssp.PTS*100) >= " + skuTableWidgetData.getPtsMin() + " and round(ssp.PTS*100) <= " + skuTableWidgetData.getPtsMax();
        }

        //SalesOccurr Filter
        if (skuTableWidgetData.getSalesOccurrMonth() != null) {
            //SalesOccurr 12 Filter
            if (skuTableWidgetData.getSalesOccurrMonth() == 12) {
                sql += " and ( CASE NVL(hs_part, 'X')" +
                        "WHEN 'H' THEN  NVL(ssp.HSN_occurance_12_months, 0) + NVL(ssp.HSN_lost_sales_12_months, 0) + NVL(SSP.HSN_SUPERSEDE_12_MONTHS,0) ELSE" +
                        " NVL(ssp.occurance_12_months, 0) + NVL(ssp.lost_sales_12_months, 0) + NVL(SSP.SUPERSEDE_12_MONTHS,0) END )";
                if (skuTableWidgetData.getSalesOccurrMin() != null && skuTableWidgetData.getSalesOccurrMax() != null && skuTableWidgetData.getSalesOccurrMax() >= skuTableWidgetData.getSalesOccurrMin()) {
                    sql += "  between " + skuTableWidgetData.getSalesOccurrMin() + " and " + skuTableWidgetData.getSalesOccurrMax() ;
                } else if (skuTableWidgetData.getSalesOccurrMax() != null && skuTableWidgetData.getSalesOccurrMin() == null) {
                    sql += " <= " + skuTableWidgetData.getSalesOccurrMax();
                } else if (skuTableWidgetData.getSalesOccurrMin() != null && skuTableWidgetData.getSalesOccurrMax() == null) {
                    sql += " >= " + skuTableWidgetData.getSalesOccurrMin();
                }
            }
            //SalesOccurr 24 Filter
            if (skuTableWidgetData.getSalesOccurrMonth() == 24) {
                sql += " and ( CASE NVL(hs_part, 'X')" +
                        "WHEN 'H' THEN  NVL(ssp.HSN_occurance_24_months, 0) + NVL(ssp.HSN_lost_sales_24_months, 0) + NVL(SSP.HSN_SUPERSEDE_24_MONTHS,0) ELSE" +
                        " NVL(ssp.occurance_24_months, 0) + NVL(ssp.lost_sales_24_months, 0) + NVL(SSP.SUPERSEDE_24_MONTHS,0) END )";
                if (skuTableWidgetData.getSalesOccurrMin() != null && skuTableWidgetData.getSalesOccurrMax() != null && skuTableWidgetData.getSalesOccurrMax() >= skuTableWidgetData.getSalesOccurrMin()) {
                    sql += "  between " + skuTableWidgetData.getSalesOccurrMin() + " and " + skuTableWidgetData.getSalesOccurrMax();
                } else if (skuTableWidgetData.getSalesOccurrMax() != null && skuTableWidgetData.getSalesOccurrMin() == null) {
                    sql += " <= " + skuTableWidgetData.getSalesOccurrMax();
                } else if (skuTableWidgetData.getSalesOccurrMin() != null && skuTableWidgetData.getSalesOccurrMax() == null) {
                    sql += " >= " + skuTableWidgetData.getSalesOccurrMin();
                }
            }
        }

        //DCR_PTS Filter
        if (skuTableWidgetData.getDcrPtsMin() != null && skuTableWidgetData.getDcrPtsMax() != null) {
            sql += " and ssp.DCR_PTS >= " + skuTableWidgetData.getDcrPtsMin() + " and ssp.DCR_PTS <= " + skuTableWidgetData.getDcrPtsMax();
        }
        if (skuTableWidgetData.getCostMin() != null && skuTableWidgetData.getCostMax() != null) {
            sql += " and ssp.price_wt_core >= " + skuTableWidgetData.getCostMin() + " and ssp.price_wt_core <= " + skuTableWidgetData.getCostMax();
        }
        else if (skuTableWidgetData.getCostMin() != null && skuTableWidgetData.getCostMax() == null) {
            sql += " and ssp.price_wt_core >= " + skuTableWidgetData.getCostMin();
        }
        else if (skuTableWidgetData.getCostMin() == null && skuTableWidgetData.getCostMax() != null) {
            sql += " and ssp.price_wt_core <= " + skuTableWidgetData.getCostMax();
        }
       //HubSiteCountData
        if ((skuTableWidgetData.getHubSiteCountMin ( ) != null || skuTableWidgetData.getHubSiteCountMax ( ) != null) && user.getAccessItemIds().contains(160) ) {

            if (( null == skuTableWidgetData.getHubSiteCountMin ( ) || 0==skuTableWidgetData.getHubSiteCountMin ()) && (null==skuTableWidgetData.getHubSiteCountMax ()|| 0==skuTableWidgetData.getHubSiteCountMax ( )) )
            {

                sql += " and  not exists (select product_id from network_product where network_id in (" +
                        "  SELECT DISTINCT hsn_network_id FROM hub_spoke_network_mapping WHERE hsn_site_id IN ( SELECT ss.site_id " +
                        "  FROM session_site ss" +
                        "  WHERE ss.session_id = " + skuTableWidgetData.getSessionId() + " )" +
                        "  AND is_active = 1" +
                        "  )" + " and ssp.product_id=product_id" + ")";
                        //"  and nvl(CNT_MIN_GTR_0,0) BETWEEN  " + skuTableWidgetData.getHubSiteCountMin() + " and " + skuTableWidgetData.getHubSiteCountMax() + ") ";

            }
            else if ((null==skuTableWidgetData.getHubSiteCountMin ( ) || 0==skuTableWidgetData.getHubSiteCountMin ( )) && (null!=skuTableWidgetData.getHubSiteCountMax () && 0!=skuTableWidgetData.getHubSiteCountMax()))
            {

                sql += "   and  (  exists (select product_id from network_product where network_id in (" +
                        "  SELECT DISTINCT hsn_network_id FROM hub_spoke_network_mapping WHERE hsn_site_id IN ( SELECT ss.site_id " +
                        "  FROM session_site ss" +
                        "  WHERE ss.session_id = " + skuTableWidgetData.getSessionId() + " )" +
                        "  AND is_active = 1" +
                        "  )" + " and ssp.product_id=product_id" +
                        "  and nvl(CNT_MIN_GTR_0,0) <=   " + skuTableWidgetData.getHubSiteCountMax() + ") OR" +
                        "     not exists (select product_id from network_product where network_id in ( " +
                        "  SELECT DISTINCT hsn_network_id FROM hub_spoke_network_mapping WHERE hsn_site_id IN ( SELECT ss.site_id  " +
                        " FROM session_site ss " +
                        "  WHERE ss.session_id = " + skuTableWidgetData.getSessionId() + " )"  +
                        "   AND is_active = 1 " +
                        "  )" + " and ssp.product_id=product_id )) ";
            }
            else if ((null!=skuTableWidgetData.getHubSiteCountMin() || 0!=skuTableWidgetData.getHubSiteCountMin( )) && (null==skuTableWidgetData.getHubSiteCountMax ( ) || 0==skuTableWidgetData.getHubSiteCountMax ()))
            {

                sql += " and   exists (select product_id from network_product where network_id in (" +
                        "  SELECT DISTINCT hsn_network_id FROM hub_spoke_network_mapping WHERE hsn_site_id IN ( SELECT ss.site_id " +
                        "  FROM session_site ss" +
                        "  WHERE ss.session_id = " + skuTableWidgetData.getSessionId() + " )" +
                        "  AND is_active = 1" +
                        "  )" + " and ssp.product_id=product_id" +
                        "  and nvl(CNT_MIN_GTR_0,0) >=  " + skuTableWidgetData.getHubSiteCountMin() +  ") ";
            }
            else if ((null!=skuTableWidgetData.getHubSiteCountMin() || 0!=skuTableWidgetData.getHubSiteCountMin( )) && (null!=skuTableWidgetData.getHubSiteCountMax ( ) || 0!=skuTableWidgetData.getHubSiteCountMax ()))
            {

                sql += " and  exists (select product_id from network_product where network_id in (" +
                        "  SELECT DISTINCT hsn_network_id FROM hub_spoke_network_mapping WHERE hsn_site_id IN ( SELECT ss.site_id " +
                        "  FROM session_site ss" +
                        "  WHERE ss.session_id = " + skuTableWidgetData.getSessionId() + " )" +
                        "  AND is_active = 1" +
                        "  )" + " and ssp.product_id=product_id" +
                        "  and CNT_MIN_GTR_0 BETWEEN  " + skuTableWidgetData.getHubSiteCountMin() + " and " + skuTableWidgetData.getHubSiteCountMax() + ") ";
            }

            else if ((null!=skuTableWidgetData.getHubSiteCountMin() && 0!=skuTableWidgetData.getHubSiteCountMin()) && (null!=skuTableWidgetData.getHubSiteCountMax ( ) && 0!=skuTableWidgetData.getHubSiteCountMax()))
            {

                sql += " and  exists (select product_id from network_product where network_id in (" +
                        "  SELECT DISTINCT hsn_network_id FROM hub_spoke_network_mapping WHERE hsn_site_id IN ( SELECT ss.site_id " +
                        "  FROM session_site ss" +
                        "  WHERE ss.session_id = " + skuTableWidgetData.getSessionId() + " )" +
                        "  AND is_active = 1" +
                        "  )" + " and ssp.product_id=product_id" +
                        "  and CNT_MIN_GTR_0 BETWEEN  " + skuTableWidgetData.getHubSiteCountMin() + " and " + skuTableWidgetData.getHubSiteCountMax() + ") ";

            }
        }



        if (skuTableWidgetData.getSortBy() != null) {
            sql += " order by \"" + skuTableWidgetData.getSortBy() + "\" "
                    + (skuTableWidgetData.getSortingOrder() != null ? skuTableWidgetData.getSortingOrder() : "asc") + " nulls last";
        }
        String sql2 = "select * from (" + sql + ")a order by \"site\" asc nulls last";

        return sql2;
    }

    public String getActiveReviewFilterDetails(SKUTableWidgetData skuTableWidgetData, User user) {
        StringJoiner activeFilters = new StringJoiner(",");

        // Advance Filters
        // Display Flag filter
        if (skuTableWidgetData.isDisplayFlag()) {
            activeFilters.add("Display Flag");
        }

        // Sold Not Stocked / lost sales
        if (skuTableWidgetData.isSoldNotStocked()) {
            activeFilters.add("Sold Not Stocked");
        }

        // Prospect / New Parts filter
        if (skuTableWidgetData.isProspectParts()) {
            activeFilters.add("Prospect Parts");
        }

        //  VIO Count          /       LOW VEHICLE COUNT filter -- PUL -7328 - Removed 'and ssp.recommended_order_qty > 0'
        if (skuTableWidgetData.getLowVehicleCount() != null) {
            if (skuTableWidgetData.getLowVehicleOperator() != null)
                activeFilters.add("VIO Count " + skuTableWidgetData.getLowVehicleOperator().trim() + " " + skuTableWidgetData.getLowVehicleCount()+" vehicles");
        }

        // Keep Flag filter
        if (skuTableWidgetData.isKeepFlag()) {
            activeFilters.add("Keep Flag");
        }

        // Surviving / Supersedes filter
        if (skuTableWidgetData.isSupersedes()) {
            activeFilters.add("Surviving Parts");
        }

        // Specials filter
        if (skuTableWidgetData.isSpecials()) {
            activeFilters.add("Specials");
        }

        // Overstock Filter
        if (skuTableWidgetData.isOverstock()) {
            activeFilters.add("Overstock");
        }

        // New Number filter
        if (skuTableWidgetData.isNewNumber()) {
            activeFilters.add("New Numbers");
        }

        //Similarparts filter
        if (skuTableWidgetData.isSimilarParts()) {
            activeFilters.add("Similar Parts = "+skuTableWidgetData.getSimPartsOption());
        }

        //Block Order Suggestions
        if (skuTableWidgetData.isBlockOrderSuggestion()) {
            activeFilters.add("Block Order Suggestion");
        }

        // Generic Filters

        // Hide system followers filter
        if (skuTableWidgetData.isHideSystemFollowers()) {
            activeFilters.add("Hide System Followers");
        }

        //SalesPotential Filter
        if (skuTableWidgetData.getPtsMin() != null && skuTableWidgetData.getPtsMax() != null) {
            activeFilters.add("Sales Potential = " + skuTableWidgetData.getPtsMin() + " to " + skuTableWidgetData.getPtsMax());
        }
        //minHubCountSites

        if (skuTableWidgetData.getHubSiteCountMin() != null && skuTableWidgetData.getHubSiteCountMax() != null ) {
            activeFilters.add("min Hub Count Sites = " + skuTableWidgetData.getHubSiteCountMin() + " to " + skuTableWidgetData.getHubSiteCountMax());
        } else if (skuTableWidgetData.getHubSiteCountMin() != null && skuTableWidgetData.getHubSiteCountMax() == null) {
            activeFilters.add("min Hub Count Sites = " + skuTableWidgetData.getHubSiteCountMin()  + "to ...  " );
        } else if (skuTableWidgetData.getHubSiteCountMin() == null && skuTableWidgetData.getHubSiteCountMax() != null) {
            activeFilters.add("min Hub Count Sites = " + "...to" + skuTableWidgetData.getHubSiteCountMax());

        }

        //DCR_PTS Filter
        if (skuTableWidgetData.getDcrPtsMin() != null && skuTableWidgetData.getDcrPtsMax() != null) {
            activeFilters.add("DCR PTS = " + skuTableWidgetData.getDcrPtsMin() + " to " + skuTableWidgetData.getDcrPtsMax());
        }
        if (skuTableWidgetData.getCostMin() != null && skuTableWidgetData.getCostMax() != null) {
            activeFilters.add("Cost = $" + skuTableWidgetData.getCostMin() + " to $" + skuTableWidgetData.getCostMax());
        }

        else if (skuTableWidgetData.getCostMin() != null && skuTableWidgetData.getCostMax() == null) {
            activeFilters.add("Cost = $" + skuTableWidgetData.getCostMin() + " to $...");

        }
        else if (skuTableWidgetData.getCostMin() == null && skuTableWidgetData.getCostMax() != null) {
            activeFilters.add("Cost = $0.00" + " to $" + skuTableWidgetData.getCostMax());
        }

        //Demand Occurrences Filter
        if (skuTableWidgetData.getSalesOccurrMonth() != null) {
            if (skuTableWidgetData.getSalesOccurrMin() != null && skuTableWidgetData.getSalesOccurrMax() != null && skuTableWidgetData.getSalesOccurrMax() >= skuTableWidgetData.getSalesOccurrMin()) {
                activeFilters.add("Demand Occurrences = " + skuTableWidgetData.getSalesOccurrMonth() + " months " + skuTableWidgetData.getSalesOccurrMin() + " to " + skuTableWidgetData.getSalesOccurrMax());
            } else if (skuTableWidgetData.getSalesOccurrMax() != null && skuTableWidgetData.getSalesOccurrMin() == null) {
                activeFilters.add("Demand Occurrences = " + skuTableWidgetData.getSalesOccurrMonth() + " months " + "... to " + skuTableWidgetData.getSalesOccurrMax());
            } else if (skuTableWidgetData.getSalesOccurrMin() != null && skuTableWidgetData.getSalesOccurrMax() == null) {
                activeFilters.add("Demand Occurrences = " + skuTableWidgetData.getSalesOccurrMonth() + " months " + skuTableWidgetData.getSalesOccurrMin() + " to ...");
            }
        }

        //DC NetSales Filter
        if (skuTableWidgetData.getDcSalesMonth() != null && user.getAccessItemIds().contains(152)) {
            if (skuTableWidgetData.getDcSalesMin() != null && skuTableWidgetData.getDcSalesMax() != null && skuTableWidgetData.getDcSalesMax() >= skuTableWidgetData.getDcSalesMin()) {
                activeFilters.add("DC NetSales = " + skuTableWidgetData.getDcSalesMonth() + " months " + skuTableWidgetData.getDcSalesMin() + " to " + skuTableWidgetData.getDcSalesMax());
            } else if (skuTableWidgetData.getDcSalesMax() != null && skuTableWidgetData.getDcSalesMin() == null) {
                activeFilters.add("DC NetSales = " + skuTableWidgetData.getDcSalesMonth() + " months " + "... to " + skuTableWidgetData.getDcSalesMax());
            } else if (skuTableWidgetData.getDcSalesMin() != null && skuTableWidgetData.getDcSalesMax() == null) {
                activeFilters.add("DC NetSales = " + skuTableWidgetData.getDcSalesMonth() + " months " + skuTableWidgetData.getDcSalesMin() + " to ...");
            }
        }

        //*DC NetSales Filter
        if (skuTableWidgetData.getDcWideSalesMonth() != null) {
            if (skuTableWidgetData.getDcWideSalesMin() != null && skuTableWidgetData.getDcWideSalesMax() != null && skuTableWidgetData.getDcWideSalesMax() >= skuTableWidgetData.getDcWideSalesMin()) {
                activeFilters.add("*DC* NetSales = " + skuTableWidgetData.getDcWideSalesMonth() + " months " + skuTableWidgetData.getDcWideSalesMin() + " to " + skuTableWidgetData.getDcWideSalesMax());
            } else if (skuTableWidgetData.getDcWideSalesMax() != null && skuTableWidgetData.getDcWideSalesMin() == null) {
                activeFilters.add("*DC* NetSales = " + skuTableWidgetData.getDcWideSalesMonth() + " months " + "... to " + skuTableWidgetData.getDcWideSalesMax());
            } else if (skuTableWidgetData.getDcWideSalesMin() != null && skuTableWidgetData.getDcWideSalesMax() == null) {
                activeFilters.add("*DC* NetSales = " + skuTableWidgetData.getDcWideSalesMonth() + " months " + skuTableWidgetData.getDcWideSalesMin() + " to ...");
            }
        }

        //hubdemand 12 and 24 Filter
        if (skuTableWidgetData.getHubDemandMonth() != null && user.getAccessItemIds().contains(159)) {
            if (skuTableWidgetData.getHubDemandMin() != null && skuTableWidgetData.getHubDemandMax() != null && skuTableWidgetData.getHubDemandMax() >= skuTableWidgetData.getHubDemandMin()) {
                activeFilters.add("SmartHub Net Dmd = " + skuTableWidgetData.getHubDemandMonth() + " months " + skuTableWidgetData.getHubDemandMin() + " to " + skuTableWidgetData.getHubDemandMax());
            } else if (skuTableWidgetData.getHubDemandMax() != null && skuTableWidgetData.getHubDemandMin() == null) {
                activeFilters.add("SmartHub Net Dmd = " + skuTableWidgetData.getHubDemandMonth() + " months " + "... to " + skuTableWidgetData.getHubDemandMax());
            } else if (skuTableWidgetData.getHubDemandMin() != null && skuTableWidgetData.getHubDemandMax() == null) {
                activeFilters.add("SmartHub Net Dmd = " + skuTableWidgetData.getHubDemandMonth() + " months " + skuTableWidgetData.getHubDemandMin() + " to ...");
            }
        }

        // App / Non-App Filter
        if (skuTableWidgetData.isAppNonApp()) {
            activeFilters.add("App / Non-App = " + skuTableWidgetData.getAppNonAppOption());
        }

        // Line Abbr filter
        if (skuTableWidgetData.getLineAbbr() != null) {
            activeFilters.add("Line Abbr = " + skuTableWidgetData.getLineAbbr());
        }

        // Order filter
        if (skuTableWidgetData.isOrder()) {
            activeFilters.add("Order = " + skuTableWidgetData.getOrderOption());
        }
        // Return filter
        if (skuTableWidgetData.isReturn()) {
            activeFilters.add("Return = " + skuTableWidgetData.getReturnOption());
        }

        /** StoreInventory PUL-7910*/
        if (skuTableWidgetData.isStoreInventory() && skuTableWidgetData.getSelectedStoreInventoryOption() !=null) {
            String sI = "Store Inventory = ";
            if (("allStockedOnhand").equals(skuTableWidgetData.getSelectedStoreInventoryOption().trim())) {
                sI +="All (Stocked & On Hand)";
            } else if (("grt0onhand").equals(skuTableWidgetData.getSelectedStoreInventoryOption().trim())) {
                sI += "On Hand > 0";
            } else if (("grt0onandwithnoMinMax").equals(skuTableWidgetData.getSelectedStoreInventoryOption().trim())) {
                sI += "On Hand > 0 with no Min Max";
            } else if (("grt0min").equals(skuTableWidgetData.getSelectedStoreInventoryOption().trim())) {
                sI += "Min > 0";
            } else if (("grt0min0withnoOnHand").equals(skuTableWidgetData.getSelectedStoreInventoryOption().trim())) {
                sI += "Min > 0 with no On Hand";
            } else if (("grt0excludeOnHand").equals(skuTableWidgetData.getSelectedStoreInventoryOption().trim())) {
                sI += "Exclude On Hand > 0";
            } else if (("grt0excludeMin").equals(skuTableWidgetData.getSelectedStoreInventoryOption().trim())) {
                sI += "Exclude Min > 0";
            } else if (("all+(Stocked, OH, Not Stocked )").equals(skuTableWidgetData.getSelectedStoreInventoryOption().trim())) {
                sI +="All+(Stocked, OH, Not Stocked ) ";
            } else {
                sI += "All+ (Stocked, OH, Not Stocked )";
            }
            activeFilters.add(sI);
        }

        if(skuTableWidgetData.getFirstStockToDt() !=null || skuTableWidgetData.getFirstStockFromDt()!=null){
            String str="First Stock Date ";
            if(skuTableWidgetData.getFirstStockFromDt()!=null && skuTableWidgetData.getFirstStockToDt()==null)
            str+="applied from "+skuTableWidgetData.getFirstStockFromDt();
            else if (skuTableWidgetData.getFirstStockFromDt()==null && skuTableWidgetData.getFirstStockToDt()!=null) {
            str+="applied till "+skuTableWidgetData.getFirstStockToDt();
            }
            else
                str+="applied from "+skuTableWidgetData.getFirstStockFromDt()+" to "+skuTableWidgetData.getFirstStockToDt();
            activeFilters.add(str);
        }

        if (skuTableWidgetData.isDcStocking()) {
            if (("stockedatdc").equals(skuTableWidgetData.getSelectedStockNonStockOption().trim())) {
                activeFilters.add("DC Stocking = Stocked At Dc" );
            } else if (skuTableWidgetData.isDcStocking()  && ("nonstockedatdc").equals(skuTableWidgetData.getSelectedStockNonStockOption().trim())) {
                activeFilters.add("DC Stocking = Non Stocked At Dc" );
            }
        }
        // Part Number filter
        if (skuTableWidgetData.getPartNumber() != null) {
            String partNumber = skuTableWidgetData.getPartNumber().toUpperCase().replaceAll("[^A-Z0-9]+", "");
            activeFilters.add("Part Number = " + partNumber);
        }

        // Group Code
        if (skuTableWidgetData.getGroupCode() >= 0) {
            activeFilters.add("Group Code = " + skuTableWidgetData.getGroupCode());
        }

        // Avg Age filter
        if (skuTableWidgetData.getAvgAgeMin() != null && skuTableWidgetData.getAvgAgeMax() != null) {
            if (skuTableWidgetData.getAvgAgeMin() == 200)
                activeFilters.add("Avg Age > " + skuTableWidgetData.getAvgAgeMin()+" years");
            else if (skuTableWidgetData.getAvgAgeMin() == -10)
                activeFilters.add("Avg Age < " + skuTableWidgetData.getAvgAgeMax()+" years");
            else
                activeFilters.add("Avg Age = " + skuTableWidgetData.getAvgAgeMin() + " to " + skuTableWidgetData.getAvgAgeMax()+" years");
        }

        // Reviewed
        if (skuTableWidgetData.getIsReviewed() != null) {
            activeFilters.add("Reviewed = " + (skuTableWidgetData.getIsReviewed() == true ? "Reviewed" : "Unreviewed"));
        }

        // Fleets Filter
        if (skuTableWidgetData.getFleetGroupIdsArray() != null
                && skuTableWidgetData.getFleetGroupIdsArray().length > 0) {
            activeFilters.add("Fleet Groups = " + fleetDAO.getFleetGroupNamesAsString(skuTableWidgetData.getFleetGroupIdsArray()));
        }

        // Hub & Spoke filter
        if (skuTableWidgetData.isHubspoke()) {
            activeFilters.add("Hub & Spoke = " + skuTableWidgetData.getHubSpokeOption());
        }

        // Sort
        if (skuTableWidgetData.getSortBy() != null) {
            activeFilters.add("Sort By = " + skuTableWidgetData.getSortBy() + " in " + (skuTableWidgetData.getSortingOrder().equals("desc")? "Descending":"Ascending" )  + " order");
        }

        if (activeFilters.length() == 0)
            activeFilters.add("No Active Filter");

        return activeFilters.toString();
    }

}