MSTO - HP1,M2,4,6 Exclude WRTV

PalletBillingPivot.java

import java.util.*;
import java.util.stream.Collectors;

public class PalletBillingPivot {

    // ---- Raw inventory row from "WH68 Product Expiry" sheet ----
    static class InventoryRow {
        String billable;      // "Y" / "N"
        String billFreq;      // "MTH" / "WKLY - PICK" / "WKLY - RESERVE" / ...
        String zone;          // HP1, M2, M4, M6, WRTV, FP, R312, ...
        long   location;
        long   supplierCode;
        long   sku;
        int    packSize;      // = Cartons per Pallet
        // Each row in the source = 1 carton of inventory

        InventoryRow(String billable, String billFreq, String zone, long location,
                     long supplierCode, long sku, int packSize) {
            this.billable     = billable;
            this.billFreq     = billFreq;
            this.zone         = zone;
            this.location     = location;
            this.supplierCode = supplierCode;
            this.sku          = sku;
            this.packSize     = packSize;
        }
    }

    // ---- Left-pivot row (A:G on the sheet) ----
    static class LeftPivotRow {
        String zone;
        long   location;
        long   supplierCode;
        long   sku;
        int    cartonsPerPallet;
        int    sumOfCartons;
        double palletQty;          // = sumOfCartons / cartonsPerPallet

        LeftPivotRow(String zone, long location, long supplierCode, long sku,
                     int cartonsPerPallet, int sumOfCartons) {
            this.zone             = zone;
            this.location         = location;
            this.supplierCode     = supplierCode;
            this.sku              = sku;
            this.cartonsPerPallet = cartonsPerPallet;
            this.sumOfCartons     = sumOfCartons;
            this.palletQty        = (double) sumOfCartons / cartonsPerPallet;
        }
    }

    // ---- Right-pivot row (J:N on the sheet) ----
    static class RightPivotRow {
        String zone;
        long   supplierCode;
        double sumOfPalletQty;     // L: SUM of Pallet Qty
        double total31Days;        // M: L * 31
        long   palletToBillWeek;   // N: ROUNDUP(M / 7, 0)

        RightPivotRow(String zone, long supplierCode, double sumOfPalletQty) {
            this.zone             = zone;
            this.supplierCode     = supplierCode;
            this.sumOfPalletQty   = sumOfPalletQty;
            this.total31Days      = sumOfPalletQty * 31.0;
            this.palletToBillWeek = (long) Math.ceil(total31Days / 7.0);
        }
    }

    // ---- Filter constants (from cells B5 / B6 / A4 on the sheet) ----
    static final String        FILTER_BILLABLE = "Y";
    static final String        FILTER_BILLFREQ = "MTH";
    static final Set<String>   INCLUDED_ZONES  =
        new HashSet<>(Arrays.asList("HP1", "M2", "M4", "M6"));   // excludes WRTV

    public static void main(String[] args) {

        // ---- 1. Hardcoded raw inventory (sample from WH68 Product Expiry) ----
        // Mix of MTH/WKLY and HP1/M2/M4/M6/WRTV/other zones so the filters do real work.
        List<InventoryRow> source = Arrays.asList(
            // ---- rows that SHOULD pass the filter (Y + MTH + HP1/M2/M4/M6) ----
            new InventoryRow("Y", "MTH",           "HP1", 34401101L, 72733L,   396904L, 45),
            new InventoryRow("Y", "MTH",           "HP1", 34401102L, 73724L, 13259518L, 100),
            new InventoryRow("Y", "MTH",           "HP1", 34401102L, 73724L, 13259518L, 100),
            new InventoryRow("Y", "MTH",           "HP1", 34403101L, 72475L, 13151779L, 50),
            new InventoryRow("Y", "MTH",           "HP1", 34403101L, 72475L, 13151779L, 50),
            new InventoryRow("Y", "MTH",           "HP1", 34404102L, 53219L, 13065217L, 91),
            new InventoryRow("Y", "MTH",           "HP1", 34404201L, 51039L, 13066314L, 66),
            new InventoryRow("Y", "MTH",           "M2",  35002101L, 70525L,    33952L, 12),
            new InventoryRow("Y", "MTH",           "M2",  35002101L, 70525L,    33952L, 12),
            new InventoryRow("Y", "MTH",           "M4",  35400110L, 71873L,    17865L, 24),
            new InventoryRow("Y", "MTH",           "M6",  35663204L, 73312L,    16627L, 12),
            new InventoryRow("Y", "MTH",           "M6",  35663204L, 73312L,    16627L, 12),
            new InventoryRow("Y", "MTH",           "HP1", 34653113L, 57440L,    47440L, 12),

            // ---- rows that SHOULD be excluded ----
            new InventoryRow("Y", "WKLY - PICK",   "HP1", 31211102L, 73356L,    12576L, 6),    // wrong freq
            new InventoryRow("Y", "WKLY - RESERVE","HP1", 31214502L, 73356L,    12576L, 6),    // wrong freq
            new InventoryRow("Y", "MTH",           "WRTV",10104372L, 70317L,    16908L, 24),   // excluded zone
            new InventoryRow("N", "MTH",           "HP1", 34401101L, 72733L,   396904L, 45),   // not billable
            new InventoryRow("Y", "MTH",           "FP",  32213102L, 73356L,    35073L, 6),    // not in zone set
            new InventoryRow("Y", "WKLY - PICK",   "NC",  35802101L, 71928L,    32219L, 48)    // wrong freq + zone
        );

        // ---- 2. Apply filter (matches cells A4 / B5 / B6 on the sheet) ----
        List<InventoryRow> filtered = source.stream()
            .filter(r -> FILTER_BILLABLE.equalsIgnoreCase(r.billable))
            .filter(r -> FILTER_BILLFREQ.equalsIgnoreCase(r.billFreq))
            .filter(r -> INCLUDED_ZONES.contains(r.zone))
            .collect(Collectors.toList());

        System.out.println("Source rows: " + source.size()
            + "   →   After filter (Billable=Y, Freq=MTH, Zone∈{HP1,M2,M4,M6}): "
            + filtered.size());

        // ---- 3. LEFT PIVOT: group by Zone + Location + Supplier + SKU ----
        //     Sum of Cartons = count of rows (each row = 1 carton of inventory).
        //     Cartons per Pallet = Pack Size of the group (constant within a group).
        Map<String, List<InventoryRow>> leftGroups = filtered.stream()
            .collect(Collectors.groupingBy(
                r -> r.zone + "|" + r.location + "|" + r.supplierCode + "|" + r.sku,
                LinkedHashMap::new,
                Collectors.toList()));

        List<LeftPivotRow> leftPivot = leftGroups.values().stream()
            .map(grp -> {
                InventoryRow first = grp.get(0);
                return new LeftPivotRow(
                    first.zone, first.location, first.supplierCode, first.sku,
                    first.packSize, grp.size());          // size() = Sum of Cartons
            })
            .sorted(Comparator.comparing((LeftPivotRow r) -> r.zone)
                              .thenComparingLong(r -> r.location)
                              .thenComparingLong(r -> r.supplierCode))
            .collect(Collectors.toList());

        System.out.println("\n=== LEFT PIVOT (columns A:G) ===");
        System.out.printf("%-6s %-10s %-10s %-10s %-8s %-8s %-10s%n",
            "Zone", "Location", "Supplier", "SKU", "C/Pallet", "Cartons", "PalletQty");
        for (LeftPivotRow r : leftPivot) {
            System.out.printf("%-6s %-10d %-10d %-10d %-8d %-8d %-10.6f%n",
                r.zone, r.location, r.supplierCode, r.sku,
                r.cartonsPerPallet, r.sumOfCartons, r.palletQty);
        }

        // ---- 4. RIGHT PIVOT: re-group left pivot by Zone + Supplier; SUM(PalletQty) ----
        Map<String, Double> rightGroups = leftPivot.stream()
            .collect(Collectors.groupingBy(
                r -> r.zone + "|" + r.supplierCode,
                LinkedHashMap::new,
                Collectors.summingDouble(r -> r.palletQty)));

        List<RightPivotRow> rightPivot = rightGroups.entrySet().stream()
            .map(e -> {
                String[] key = e.getKey().split("\\|");
                return new RightPivotRow(key[0], Long.parseLong(key[1]), e.getValue());
            })
            .sorted(Comparator.comparing((RightPivotRow r) -> r.zone)
                              .thenComparingLong(r -> r.supplierCode))
            .collect(Collectors.toList());

        System.out.println("\n=== RIGHT PIVOT (columns J:N) ===");
        System.out.printf("%-6s %-10s %-18s %-22s %-18s%n",
            "Zone", "Supplier", "Sum of PalletQty", "Total for 31 days (×31)", "Pallet/Week (÷7 up)");
        for (RightPivotRow r : rightPivot) {
            System.out.printf("%-6s %-10d %-18.6f %-22.6f %-18d%n",
                r.zone, r.supplierCode, r.sumOfPalletQty, r.total31Days, r.palletToBillWeek);
        }
    }
}