Subversion Repositories SmartDukaan

package com.smartdukaan.cron.scheduled;

import com.spice.profitmandi.common.model.ProfitMandiConstants;

import com.spice.profitmandi.dao.entity.auth.AuthUser;

import com.spice.profitmandi.dao.repository.cs.CsService;

import org.apache.logging.log4j.LogManager;

import org.apache.logging.log4j.Logger;

import org.hibernate.SessionFactory;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.beans.factory.annotation.Qualifier;

import org.springframework.core.io.ByteArrayResource;

import org.springframework.mail.javamail.JavaMailSender;

import org.springframework.mail.javamail.MimeMessageHelper;

import org.springframework.stereotype.Component;

import org.springframework.transaction.annotation.Transactional;

import javax.mail.internet.InternetAddress;

import javax.mail.internet.MimeMessage;

import java.nio.charset.StandardCharsets;

import java.time.LocalDate;

import java.util.Arrays;

import java.util.LinkedHashSet;

import java.util.List;

import java.util.Objects;

import java.util.Set;

import java.util.stream.Collectors;

/**

 * Daily list of smartphone items with no catalog.item.brand_identifier, for brands whose mobile

 * brand_category row (category_id 3) has brand_identifier_required set. Upload keeps the column optional;

 * this report is how the gaps get filled. Excludes feature phones (HSN 85171400), non-channel suppliers

 * ONEJMD 500 / Longlife 515 and "Any Colour" placeholders. Mail body = items listed or in stock with partners/us;

 * CSV = all. Goes to every level of the category team.

 */

@Component

@Transactional(rollbackFor = Throwable.class)

public class BrandIdentifierGapMonitor {

    private static final Logger LOGGER = LogManager.getLogger(BrandIdentifierGapMonitor.class);

    private static final String[] HEADERS = {"Item Id", "Catalog Id", "Brand", "Model", "Color", "Created On", "Live Since", "Listed",

            "Partner Stock", "Our Stock"};

    private static final String GAP_QUERY =

            "SELECT i.id, i.catalog_item_id, b.name, " +

            "TRIM(CONCAT_WS(' ', NULLIF(i.model_name, ''), i.model_number)) model, " +

            "TRIM(REPLACE(REPLACE(i.color, 'f_', ''), 'F_', '')) color, DATE(i.addedOn) created_on, " +

            "(SELECT DATE(MIN(COALESCE(tl.start_date, tl.create_timestamp))) FROM catalog.tag_listing tl WHERE tl.item_id = i.id AND tl.tag_id = 4) live_since, " +

            "IF(EXISTS(SELECT 1 FROM catalog.tag_listing tl WHERE tl.item_id = i.id AND tl.tag_id = 4 AND tl.active = 1), 'Y', 'N') listed, " +

            "COALESCE((SELECT SUM(cis.availability) FROM fofo.current_inventory_snapshot cis WHERE cis.item_id = i.id AND cis.availability > 0), 0) partner_stock, " +

            "COALESCE((SELECT SUM(ii.currentQuantity) FROM warehouse.inventoryItem ii WHERE ii.itemId = i.id AND ii.currentQuantity > 0), 0) our_stock " +

            "FROM catalog.item i " +

            "JOIN catalog.catalog c ON c.id = i.catalog_item_id " +

            "JOIN catalog.brand b ON b.id = c.brand_id " +

            "JOIN catalog.brand_category bc ON bc.brand_id = b.id AND bc.category_id = 3 AND bc.brand_identifier_required = 1 " +

            "WHERE i.category = 10006 AND i.hsnCode IN ('85171300', '85171290') " +

            "AND (i.brand_identifier IS NULL OR TRIM(i.brand_identifier) = '') " +

            "AND i.preferredVendor NOT IN (500, 515) " +

            "AND LOWER(TRIM(REPLACE(REPLACE(i.color, 'f_', ''), 'F_', ''))) <> 'any colour' " +

            "ORDER BY listed DESC, partner_stock + our_stock DESC, i.addedOn DESC, i.id DESC";

    private static final String[] EXTRA_RECIPIENTS = {"alok.divedi@smartdukaan.com", "gaurav.sharma@smartdukaan.com"};

    @Autowired

    private SessionFactory sessionFactory;

    @Autowired

    private CsService csService;

    @Autowired

    @Qualifier(value = "googleMailSender")

    private JavaMailSender googleMailSender;

    @SuppressWarnings("unchecked")

    public void monitor() throws Exception {

        List<Object[]> rows = sessionFactory.getCurrentSession().createNativeQuery(GAP_QUERY).list();

        List<Object[]> liveRows = rows.stream().filter(x -> "Y".equals(String.valueOf(x[7]))

                || ((Number) x[8]).intValue() > 0 || ((Number) x[9]).intValue() > 0).collect(Collectors.toList());

        LOGGER.info("Brand identifier gaps: {} items, {} listed or in stock", rows.size(), liveRows.size());

        if (!rows.isEmpty()) {

            this.sendReport(rows, liveRows);

        }

    }

    private void sendReport(List<Object[]> rows, List<Object[]> liveRows) {

        try {

            StringBuilder sb = new StringBuilder();

            sb.append("<html><body>");

            sb.append("<h3>Missing Brand Identifier</h3>");

            sb.append("<p>Smartphones of flagged brands without a brand identifier: <b>").append(rows.size())

              .append("</b>, of which listed or in stock with partners/us: <b>").append(liveRows.size()).append("</b>.</p>");

            sb.append("<p>All items are in the attached CSV. Listed or in-stock items:</p>");

            sb.append("<table border='1' cellpadding='5' cellspacing='0'><tr>");

            for (String header : HEADERS) {

                sb.append("<th>").append(header).append("</th>");

            }

            sb.append("</tr>");

            for (Object[] row : liveRows) {

                sb.append("<tr>");

                for (Object cell : row) {

                    sb.append("<td>").append(cell != null ? cell.toString() : "").append("</td>");

                }

                sb.append("</tr>");

            }

            sb.append("</table></body></html>");

            MimeMessage message = googleMailSender.createMimeMessage();

            MimeMessageHelper helper = new MimeMessageHelper(message, true);

            helper.setSubject("Missing Brand Identifier - " + LocalDate.now());

            helper.setText(sb.toString(), true);

            helper.addAttachment("missing-brand-identifier-" + LocalDate.now() + ".csv",

                    new ByteArrayResource(this.toCsv(rows).getBytes(StandardCharsets.UTF_8)));

            Set<String> to = csService.getAuthUserByCategoryId(ProfitMandiConstants.TICKET_CATEGORY_CATEGORY).stream()

                    .map(AuthUser::getEmailId).filter(Objects::nonNull).collect(Collectors.toCollection(LinkedHashSet::new));

            to.addAll(Arrays.asList(EXTRA_RECIPIENTS));

            helper.setTo(to.toArray(new String[0]));

            helper.setCc(new String[]{"sdtech@smartdukaan.com", "amit.gupta@smartdukaan.com"});

            helper.setFrom(new InternetAddress("sdtech@smartdukaan.com", "Smart Dukaan"));

            googleMailSender.send(message);

            LOGGER.info("Brand identifier gap report emailed");

        } catch (Exception e) {

            LOGGER.error("Failed to send brand identifier gap report", e);

        }

    }

    private String toCsv(List<Object[]> rows) {

        StringBuilder sb = new StringBuilder(String.join(",", HEADERS)).append("\n");

        for (Object[] row : rows) {

            for (int i = 0; i < row.length; i++) {

                String cell = row[i] != null ? row[i].toString() : "";

                sb.append(i > 0 ? "," : "").append('"').append(cell.replace("\"", "\"\"")).append('"');

            }

            sb.append("\n");

        }

        return sb.toString();

    }

}