Subversion Repositories SmartDukaan

package com.spice.profitmandi.dao.model;

import javax.persistence.*;
import java.util.Objects;


@Entity
@NamedNativeQueries({
        @NamedNativeQuery(name = "Aging.15DaysOurStock",
                query = "SELECT " +
                        "    SUM(CASE WHEN cc.status = 'SLOWMOVING' THEN tl.selling_price ELSE 0 END) AS SLOWMOVING, " +
                        "    SUM(CASE WHEN cc.status = 'RUNNING' THEN tl.selling_price ELSE 0 END) AS RUNNING, " +
                        "    SUM(CASE WHEN cc.status = 'FASTMOVING' THEN tl.selling_price ELSE 0 END) AS FASTMOVING, " +
                        "    SUM(CASE WHEN cc.status = 'HID' THEN tl.selling_price ELSE 0 END) AS HID, " +
                        "    SUM(CASE WHEN cc.status IS NULL THEN tl.selling_price ELSE 0 END) AS Other, " +
                        "    SUM(tl.selling_price) AS Total " +
                        "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 " +
                        "LEFT JOIN catalog.catagoriesd_catalog cc ON cc.catalog_id = i.catalog_item_id AND cc.end_date IS NULL " +
                        "WHERE inv.invoiceDate < DATE_SUB(CURDATE(), INTERVAL 15 DAY)",
                resultSetMapping = "Our15DaysAging"),

})

@SqlResultSetMappings({

        @SqlResultSetMapping(name = "Our15DaysAging",
                classes = {@ConstructorResult(targetClass = Our15DaysOldAgingStock.class,
                        columns = {
                                @ColumnResult(name = "SLOWMOVING", type = Long.class),
                                @ColumnResult(name = "RUNNING", type = Long.class),
                                @ColumnResult(name = "FASTMOVING", type = Long.class),
                                @ColumnResult(name = "HID", type = Long.class),
                                @ColumnResult(name = "Other", type = Long.class),
                                @ColumnResult(name = "Total", type = Long.class),
                        }
                )}
        )

})

public class Our15DaysOldAgingStock {
    long slowmovingAgingStock;
    long runningAgingStock;
    long fastmovingAgingStock;
    long hidAgingStock;
    long otherAgingStock;
    long total;

    // Synthetic primary key to satisfy JPA's requirement
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id; // This will not be used in the query but satisfies JPA.

    public Our15DaysOldAgingStock(long slowmovingAgingStock, long runningAgingStock, long fastmovingAgingStock, long hidAgingStock, long otherAgingStock, long total) {
        this.slowmovingAgingStock = slowmovingAgingStock;
        this.runningAgingStock = runningAgingStock;
        this.fastmovingAgingStock = fastmovingAgingStock;
        this.hidAgingStock = hidAgingStock;
        this.otherAgingStock = otherAgingStock;
        this.total = total;
    }

    public long getSlomovingAgingStock() {
        return slowmovingAgingStock;
    }

    public void setSlowmovingAgingStock(long slowmovingAgingStock) {
        this.slowmovingAgingStock = slowmovingAgingStock;
    }

    public long getRunningAgingStock() {
        return runningAgingStock;
    }

    public void setRunningAgingStock(long runningAgingStock) {
        this.runningAgingStock = runningAgingStock;
    }

    public long getFastmovingAgingStock() {
        return fastmovingAgingStock;
    }

    public void setFastmovingAgingStock(long fastmovingAgingStock) {
        this.fastmovingAgingStock = fastmovingAgingStock;
    }

    public long getHidAgingStock() {
        return hidAgingStock;
    }

    public void setHidAgingStock(long hidAgingStock) {
        this.hidAgingStock = hidAgingStock;
    }

    public long getOtherAgingStock() {
        return otherAgingStock;
    }

    public void setOtherAgingStock(long otherAgingStock) {
        this.otherAgingStock = otherAgingStock;
    }

    public long getTotal() {
        return total;
    }

    public void setTotal(long total) {
        this.total = total;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        Our15DaysOldAgingStock that = (Our15DaysOldAgingStock) o;
        return slowmovingAgingStock == that.slowmovingAgingStock && runningAgingStock == that.runningAgingStock && fastmovingAgingStock == that.fastmovingAgingStock && hidAgingStock == that.hidAgingStock && otherAgingStock == that.otherAgingStock && total == that.total;
    }

    @Override
    public int hashCode() {
        return Objects.hash(slowmovingAgingStock, runningAgingStock, fastmovingAgingStock, hidAgingStock, otherAgingStock, total);
    }
}