Subversion Repositories SmartDukaan

package com.spice.profitmandi.dao.model;

import javax.persistence.*;

/**
 * Aging-stock breakdown per item, grouped by (warehouse, vendor). Used by the
 * PO stock-cover approval flow to show "if the PO's own warehouse has no aging
 * stock for this item, WHERE across the network does aging exist" so the
 * approver can see whether to redistribute rather than buy fresh.
 *
 * <p>Same trace path as {@link ItemAgingStockByVendorModel}
 * ({@code inventoryItem.purchaseId -> purchase.poId -> purchaseorder.supplierId}),
 * but without the vendor / warehouse filters — this query returns every
 * (warehouse, vendor) combination that has aging stock of the flagged items.
 */
@Entity
@NamedNativeQueries({
        @NamedNativeQuery(
                name = "PoStockApproval.AgingBreakdown",
                query = "SELECT inv.itemId                          AS item_id," +
                        "       inv.currentWarehouseId              AS warehouse_id," +
                        "       po.supplierId                       AS vendor_id," +
                        "       COALESCE(SUM(inv.currentQuantity),0) AS aging_qty" +
                        " FROM warehouse.inventoryItem  inv" +
                        "   JOIN warehouse.purchase      pr ON pr.id = inv.purchaseId" +
                        "   JOIN warehouse.purchaseorder po ON po.id = pr.purchaseOrder_id" +
                        " WHERE inv.itemId IN (:itemIds)" +
                        "   AND inv.currentQuantity > 0" +
                        "   AND pr.receivedOn <= :agingCutoff" +
                        " GROUP BY inv.itemId, inv.currentWarehouseId, po.supplierId" +
                        " HAVING SUM(inv.currentQuantity) > 0",
                resultSetMapping = "PoStockApprovalAgingBreakdown")
})
@SqlResultSetMappings({
        @SqlResultSetMapping(
                name = "PoStockApprovalAgingBreakdown",
                classes = {
                        @ConstructorResult(
                                targetClass = ItemAgingBreakdownModel.class,
                                columns = {
                                        @ColumnResult(name = "item_id", type = Integer.class),
                                        @ColumnResult(name = "warehouse_id", type = Integer.class),
                                        @ColumnResult(name = "vendor_id", type = Integer.class),
                                        @ColumnResult(name = "aging_qty", type = Long.class)
                                }
                        )
                }
        )
})
public class ItemAgingBreakdownModel {

    private Integer itemId;
    private Integer warehouseId;
    private Integer vendorId;
    private Long agingQty;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    public ItemAgingBreakdownModel() {
    }

    public ItemAgingBreakdownModel(Integer itemId, Integer warehouseId, Integer vendorId, Long agingQty) {
        this.itemId = itemId;
        this.warehouseId = warehouseId;
        this.vendorId = vendorId;
        this.agingQty = agingQty;
    }

    public Integer getItemId() {
        return itemId;
    }

    public Integer getWarehouseId() {
        return warehouseId;
    }

    public Integer getVendorId() {
        return vendorId;
    }

    public Long getAgingQty() {
        return agingQty;
    }
}