Subversion Repositories SmartDukaan

package com.spice.profitmandi.dao.model;

import javax.persistence.*;

@Entity
@NamedNativeQueries({
        @NamedNativeQuery(name = "Aging.15DaysWarehouseWiseStock",
                query = "SELECT " +
                        "    po.warehouseId AS warehouse_id, " +
                        "    SUM(tl.selling_price) AS total_aging " +
                        "FROM warehouse.invoice inv " +
                        "JOIN warehouse.supplier su ON su.id = inv.supplierId AND su.internal = 0 " +
                        "JOIN warehouse.purchase p ON p.invoice_id = inv.id " +
                        "JOIN warehouse.purchaseorder po ON po.id = p.purchaseOrder_id " +
                        "    AND po.supplierId = inv.supplierId " +
                        "JOIN warehouse.inventoryItem ii2 ON ii2.purchaseId = p.id " +
                        "    AND ii2.lastScanType != 'PURCHASE_RETURN' AND ii2.physicalWarehouseId = po.warehouseId " +
                        "JOIN warehouse.inventoryItem ii ON ii.serialNumber = ii2.serialNumber " +
                        "    AND ii.currentQuantity = 1 " +
                        "    AND ii.lastScanType NOT IN ('DOA_IN', 'DOA_OUT', 'SALE_RET_UNUSABLE') " +
                        "    AND ii.created > '2018-01-01' " +
                        "JOIN warehouse.scanNew s ON s.inventoryItemId = ii.id AND s.type = 'PURCHASE' " +
                        "JOIN catalog.item i ON i.id = ii.itemId AND i.category = 10006 " +
                        "JOIN catalog.tag_listing tl ON tl.item_id = i.id " +
                        "WHERE inv.invoiceDate < DATE_SUB(CURDATE(), INTERVAL 15 DAY) " +
                        "GROUP BY po.warehouseId",
                resultSetMapping = "WarehouseAgingStock")
})

@SqlResultSetMappings({
        @SqlResultSetMapping(name = "WarehouseAgingStock",
                classes = {@ConstructorResult(targetClass = WarehouseAgingStockModel.class,
                        columns = {
                                @ColumnResult(name = "warehouse_id", type = Integer.class),
                                @ColumnResult(name = "total_aging", type = Long.class),
                        }
                )}
        )
})

public class WarehouseAgingStockModel {

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

    private int warehouseId;
    private long totalAging;

    public WarehouseAgingStockModel(int warehouseId, long totalAging) {
        this.warehouseId = warehouseId;
        this.totalAging = totalAging;
    }

    public int getWarehouseId() {
        return warehouseId;
    }

    public void setWarehouseId(int warehouseId) {
        this.warehouseId = warehouseId;
    }

    public long getTotalAging() {
        return totalAging;
    }

    public void setTotalAging(long totalAging) {
        this.totalAging = totalAging;
    }
}