Subversion Repositories SmartDukaan

package com.spice.profitmandi.service.lms.meta;

import com.spice.profitmandi.common.util.ExcelUtils;

import org.apache.commons.csv.CSVFormat;

import org.apache.commons.csv.CSVParser;

import org.apache.commons.csv.CSVRecord;

import org.apache.logging.log4j.LogManager;

import org.apache.logging.log4j.Logger;

import org.apache.poi.ss.usermodel.Cell;

import org.apache.poi.ss.usermodel.Row;

import org.apache.poi.ss.usermodel.Sheet;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import org.springframework.stereotype.Component;

import org.w3c.dom.Document;

import org.w3c.dom.Element;

import org.w3c.dom.Node;

import org.w3c.dom.NodeList;

import javax.xml.XMLConstants;

import javax.xml.parsers.DocumentBuilderFactory;

import java.io.ByteArrayInputStream;

import java.io.InputStreamReader;

import java.nio.charset.StandardCharsets;

import java.util.ArrayList;

import java.util.LinkedHashMap;

import java.util.List;

import java.util.Map;

/**

 * Reads a Meta lead-ads export into header-keyed rows.

 *

 * <p><b>Why this exists rather than a call to POI.</b> Meta's export is named {@code .xls} but is

 * actually <b>SpreadsheetML 2003</b> — plain XML under

 * {@code urn:schemas-microsoft-com:office:spreadsheet}. Neither HSSF nor XSSF can open it, and every

 * other spreadsheet reader in this codebase hard-codes {@code new XSSFWorkbook(...)}, which throws on

 * it. So the format is detected from the <em>content</em>, never the file extension — this file is

 * the proof that the extension lies.

 *

 * <p><b>Rows are keyed by header name, not column index.</b> A Meta form's custom questions become

 * columns, so two forms produce different layouts; positional access would silently read the wrong

 * field the first time someone exports a different form.

 */

@Component

public class MetaLeadFileParser {

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

    private static final String SPREADSHEETML_NS = "urn:schemas-microsoft-com:office:spreadsheet";

    /** Enough bytes to recognise a format without buffering an entire upload twice. */

    private static final int SNIFF_BYTES = 512;

    /** What the parser produced, plus anything the caller should be told about the file itself. */

    public static class ParseResult {

        public final List<String> headers;

        /** One map per data row, keyed by header. Missing cells are present as empty strings. */

        public final List<Map<String, String>> rows;

        public final String detectedFormat;

        ParseResult(List<String> headers, List<Map<String, String>> rows, String detectedFormat) {

            this.headers = headers;

            this.rows = rows;

            this.detectedFormat = detectedFormat;

        }

    }

    /**

     * @param content   the whole uploaded file

     * @param fileName  used only for error messages — never for format detection

     * @throws IllegalArgumentException with a message fit to show a user

     */

    public ParseResult parse(byte[] content, String fileName) {

        if (content == null || content.length == 0) {

            throw new IllegalArgumentException("The uploaded file is empty.");

        }

        String head = new String(content, 0, Math.min(SNIFF_BYTES, content.length), StandardCharsets.UTF_8);

        try {

            if (head.contains(SPREADSHEETML_NS)) {

                return parseSpreadsheetMl(content);

            }

            if (content.length > 1 && content[0] == 'P' && content[1] == 'K') {

                return parseXlsx(content);

            }

            if (content.length > 7 && (content[0] & 0xFF) == 0xD0 && (content[1] & 0xFF) == 0xCF) {

                // Genuine legacy BIFF .xls. Meta does not produce these; say so plainly rather than

                // letting POI throw something unreadable.

                throw new IllegalArgumentException("This is a legacy binary .xls file. "

                        + "Re-export from Meta, or save it as .xlsx or .csv, and upload again.");

            }

            return parseCsv(content);

        } catch (IllegalArgumentException e) {

            throw e;

        } catch (Exception e) {

            LOGGER.error("Could not parse the uploaded Meta lead file {}", fileName, e);

            throw new IllegalArgumentException("Could not read the file: " + e.getMessage());

        }

    }

    // ---- SpreadsheetML 2003 (what Meta actually exports) --------------------------------------

    private ParseResult parseSpreadsheetMl(byte[] content) throws Exception {

        DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();

        factory.setNamespaceAware(true);

        // The file is third-party input, so external entities stay off (XXE).

        factory.setFeature(XMLConstants.FEATURE_SECURE_PROCESSING, true);

        factory.setFeature("http://apache.org/xml/features/disallow-doctype-decl", true);

        factory.setExpandEntityReferences(false);

        Document doc = factory.newDocumentBuilder().parse(new ByteArrayInputStream(content));

        NodeList rowNodes = doc.getElementsByTagNameNS(SPREADSHEETML_NS, "Row");

        if (rowNodes.getLength() == 0) {

            throw new IllegalArgumentException("The spreadsheet has no rows.");

        }

        List<List<String>> table = new ArrayList<>();

        for (int i = 0; i < rowNodes.getLength(); i++) {

            table.add(spreadsheetMlCells((Element) rowNodes.item(i)));

        }

        return toResult(table, "SpreadsheetML 2003");

    }

    /**

     * Excel omits empty cells and signals the jump with {@code ss:Index}, so cells must be placed at

     * their declared position. Reading them in document order would shift every later column left —

     * which is exactly how a phone number ends up in the city field.

     */

    private List<String> spreadsheetMlCells(Element row) {

        List<String> values = new ArrayList<>();

        NodeList children = row.getChildNodes();

        for (int i = 0; i < children.getLength(); i++) {

            Node node = children.item(i);

            if (node.getNodeType() != Node.ELEMENT_NODE

                    || !"Cell".equals(node.getLocalName())

                    || !SPREADSHEETML_NS.equals(node.getNamespaceURI())) {

                continue;

            }

            Element cell = (Element) node;

            String index = cell.getAttributeNS(SPREADSHEETML_NS, "Index");

            if (index != null && !index.isEmpty()) {

                int target = Integer.parseInt(index) - 1;

                while (values.size() < target) {

                    values.add("");

                }

            }

            values.add(firstDataText(cell));

        }

        return values;

    }

    private String firstDataText(Element cell) {

        NodeList children = cell.getChildNodes();

        for (int i = 0; i < children.getLength(); i++) {

            Node node = children.item(i);

            if (node.getNodeType() == Node.ELEMENT_NODE

                    && "Data".equals(node.getLocalName())

                    && SPREADSHEETML_NS.equals(node.getNamespaceURI())) {

                String text = node.getTextContent();

                return text == null ? "" : text.trim();

            }

        }

        return "";

    }

    // ---- xlsx ---------------------------------------------------------------------------------

    private ParseResult parseXlsx(byte[] content) throws Exception {

        List<List<String>> table = new ArrayList<>();

        try (XSSFWorkbook workbook = new XSSFWorkbook(new ByteArrayInputStream(content))) {

            Sheet sheet = workbook.getSheetAt(0);

            if (sheet == null) {

                throw new IllegalArgumentException("The workbook has no sheets.");

            }

            for (Row row : sheet) {

                List<String> values = new ArrayList<>();

                // getLastCellNum, not the cell iterator: the iterator skips undefined cells, which

                // would shift columns exactly as the ss:Index case above.

                short last = row.getLastCellNum();

                for (int c = 0; c < last; c++) {

                    Cell cell = row.getCell(c);

                    values.add(cell == null ? "" : ExcelUtils.getCellValue(cell).trim());

                }

                table.add(values);

            }

        }

        return toResult(table, "xlsx");

    }

    // ---- csv ----------------------------------------------------------------------------------

    private ParseResult parseCsv(byte[] content) throws Exception {

        List<List<String>> table = new ArrayList<>();

        // Explicit UTF-8 rather than the platform default, and the BOM is stripped — Excel writes

        // one when saving CSV, and it would otherwise corrupt the first header name.

        int offset = (content.length > 2 && (content[0] & 0xFF) == 0xEF

                && (content[1] & 0xFF) == 0xBB && (content[2] & 0xFF) == 0xBF) ? 3 : 0;

        try (CSVParser parser = new CSVParser(

                new InputStreamReader(

                        new ByteArrayInputStream(content, offset, content.length - offset),

                        StandardCharsets.UTF_8),

                CSVFormat.DEFAULT)) {

            for (CSVRecord record : parser) {

                List<String> values = new ArrayList<>();

                for (int i = 0; i < record.size(); i++) {

                    String v = record.get(i);

                    values.add(v == null ? "" : v.trim());

                }

                table.add(values);

            }

        }

        return toResult(table, "csv");

    }

    // ---- shared -------------------------------------------------------------------------------

    private ParseResult toResult(List<List<String>> table, String format) {

        if (table.isEmpty()) {

            throw new IllegalArgumentException("The file has no rows.");

        }

        List<String> headers = new ArrayList<>();

        for (String raw : table.get(0)) {

            headers.add(raw == null ? "" : raw.trim());

        }

        if (headers.isEmpty()) {

            throw new IllegalArgumentException("The first row has no column headings.");

        }

        if (table.size() < 2) {

            throw new IllegalArgumentException("The file has column headings but no data rows.");

        }

        List<Map<String, String>> rows = new ArrayList<>();

        for (int i = 1; i < table.size(); i++) {

            List<String> cells = table.get(i);

            if (isBlank(cells)) {

                // Trailing blank rows are normal in exports; they are not data and not errors.

                continue;

            }

            Map<String, String> row = new LinkedHashMap<>();

            for (int c = 0; c < headers.size(); c++) {

                String header = headers.get(c);

                if (header.isEmpty()) {

                    continue;

                }

                row.put(header, c < cells.size() && cells.get(c) != null ? cells.get(c) : "");

            }

            rows.add(row);

        }

        LOGGER.info("Parsed a {} Meta lead file: {} columns, {} data rows", format, headers.size(), rows.size());

        return new ParseResult(headers, rows, format);

    }

    private boolean isBlank(List<String> cells) {

        for (String c : cells) {

            if (c != null && !c.trim().isEmpty()) {

                return false;

            }

        }

        return true;

    }

}