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 = 'EOL' THEN tl.selling_price ELSE 0 END)        AS EOL," +
                        "    SUM(CASE WHEN cc.status = 'SLOWMOVING' THEN tl.selling_price ELSE 0 END) AS Slowmoving," +
                        "    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.inventoryItem ii" +
                        "         join warehouse.scanNew s on s.inventoryItemId = ii.id" +
                        "         join catalog.item i on i.id = ii.itemId" +
                        "         join catalog.tag_listing tl on tl.item_id = i.id" +
                        "         join catalog.catagoriesd_catalog cc on cc.catalog_id = i.catalog_item_id and cc.end_date is null" +
                        "         join (warehouse.inventoryItem ii2 join warehouse.purchase p on p.id = ii2.purchaseId" +
                        "    join warehouse.purchaseorder po on po.id = p.purchaseOrder_id" +
                        "    join warehouse.invoice inv on (p.invoiceNumber = inv.invoiceNumber and po.supplierId = inv.supplierId and" +
                        "                                   po.warehouseId = ii2.physicalWarehouseId)" +
                        "    join warehouse.supplier su on su.id = inv.supplierId and su.internal = 0)" +
                        "              on (ii.serialNumber = ii2.serialNumber and ii2.lastScanType != 'PURCHASE_RETURN')" +
                        "" +
                        "where s.type = 'PURCHASE'" +
                        "  and (DATEDIFF(now(), inv.invoiceDate) > 15 )" +
                        "  and ii.lastScanType not in ('DOA_IN', 'DOA_OUT', 'SALE_RET_UNUSABLE')" +
                        "  and ii.currentQuantity = 1" +
                        "  and i.category = 10006" +
                        "  and ii.created > '2018-01-01'",
                resultSetMapping = "Our15DaysAging"),

})

@SqlResultSetMappings({

        @SqlResultSetMapping(name = "Our15DaysAging",
                classes = {@ConstructorResult(targetClass = Our15DaysOldAgingStock.class,
                        columns = {
                                @ColumnResult(name = "EOL", type = Long.class),
                                @ColumnResult(name = "Slowmoving", 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 eolAgingStock;
    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 eolAgingStock, long runningAgingStock, long fastmovingAgingStock, long hidAgingStock, long otherAgingStock, long total) {
        this.eolAgingStock = eolAgingStock;
        this.runningAgingStock = runningAgingStock;
        this.fastmovingAgingStock = fastmovingAgingStock;
        this.hidAgingStock = hidAgingStock;
        this.otherAgingStock = otherAgingStock;
        this.total = total;
    }

    public long getEolAgingStock() {
        return eolAgingStock;
    }

    public void setEolAgingStock(long eolAgingStock) {
        this.eolAgingStock = eolAgingStock;
    }

    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 eolAgingStock == that.eolAgingStock && runningAgingStock == that.runningAgingStock && fastmovingAgingStock == that.fastmovingAgingStock && hidAgingStock == that.hidAgingStock && otherAgingStock == that.otherAgingStock && total == that.total;
    }

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