Subversion Repositories SmartDukaan

package com.spice.profitmandi.dao.entity.fofo;

import com.spice.profitmandi.dao.model.HidAllocationModel;

import javax.persistence.*;
import java.io.Serializable;
import java.time.LocalDate;
import java.util.Objects;

@Entity
@Table(name = "fofo.fofo_opening_stock",
        uniqueConstraints = {@UniqueConstraint(columnNames = {"fofo_id", "catalog_id"})})


@NamedNativeQueries({
        @NamedNativeQuery(name = "Stock.PartnerHidAllocation",
                query = "SELECT" +
                        "    COALESCE(os.fofo_id,0) as fofo_id," +
                        "    cc.catalog_id," +
                        "    c.model_number," +
                        "    c.brand," +
                        "    COALESCE(SUM(os.current_qty), 0) AS available_stock," +
                        "    COALESCE(sale1.last2daysSale, 0) AS last2DaysSoldQtySum," +
                        "    COALESCE(sale2.last3rddaysale, 0) AS lastThirdDaySoldQty," +
                        "   (CASE" +
                        "         WHEN ((COALESCE(sale1.last2daysSale, 0) * :allocationConst) + COALESCE(sale2.last3rddaysale, 0) = 0)" +
                        "             OR (COALESCE(SUM(os.current_qty), 0) = 0) THEN 2" +
                        "         ELSE ROUND((COALESCE(sale1.last2daysSale, 0) * :allocationConst) + COALESCE(sale2.last3rddaysale, 0))" +
                        "        END) AS allocation ," +
                        "    (CASE" +
                        "         WHEN ROUND((COALESCE(sale1.last2daysSale, 0) * :allocationConst) + COALESCE(sale2.last3rddaysale, 0)) > 0" +
                        "             THEN ROUND((COALESCE(sale1.last2daysSale, 0) * :allocationConst) + COALESCE(sale2.last3rddaysale, 0))" +
                        "             - COALESCE(SUM(os.current_qty), 0)" +
                        "         WHEN ((COALESCE(sale1.last2daysSale, 0) * :allocationConst) + COALESCE(sale2.last3rddaysale, 0) = 0)" +
                        "             OR (COALESCE(SUM(os.current_qty), 0) = 0) THEN 2" +
                        "         ELSE 0" +
                        "        END) AS purchase_limit" +
                        " FROM catalog.catagoriesd_catalog cc" +
                        "         LEFT JOIN fofo.fofo_opening_stock os ON os.catalog_id = cc.catalog_id AND os.fofo_id = :fofoId" +
                        "         LEFT JOIN (" +
                        "    SELECT o.fofo_id, ci.catalog_item_id, SUM(oi.quantity) AS last2daysSale" +
                        "    FROM fofo.fofo_order o" +
                        "             JOIN fofo.fofo_order_item oi ON o.id = oi.order_id" +
                        "             JOIN catalog.item ci ON oi.item_id = ci.id" +
                        "    WHERE DATE(o.create_timestamp) BETWEEN (CURDATE() - INTERVAL 2 DAY) AND CURDATE()" +
                        "    GROUP BY o.fofo_id, ci.catalog_item_id" +
                        ") sale1 ON sale1.catalog_item_id = os.catalog_id AND os.fofo_id = sale1.fofo_id" +
                        "         LEFT JOIN (" +
                        "    SELECT o.fofo_id, ci.catalog_item_id, SUM(oi.quantity) AS last3rddaysale" +
                        "    FROM fofo.fofo_order o" +
                        "             JOIN fofo.fofo_order_item oi ON o.id = oi.order_id" +
                        "             JOIN catalog.item ci ON oi.item_id = ci.id" +
                        "    WHERE DATE(o.create_timestamp) = CURDATE() - INTERVAL 3 DAY" +
                        "    GROUP BY o.fofo_id, ci.catalog_item_id" +
                        ") sale2 ON sale2.catalog_item_id = os.catalog_id AND os.fofo_id = sale2.fofo_id" +
                        "         JOIN catalog.catalog c ON c.id = cc.catalog_id" +
                        " WHERE cc.end_date IS NULL AND cc.status = 'HID'" +
                        "GROUP BY cc.catalog_id",
                resultSetMapping = "HidAllocation"),

})

@SqlResultSetMappings({

        @SqlResultSetMapping(name = "HidAllocation",
                classes = {@ConstructorResult(targetClass = HidAllocationModel.class,
                        columns = {
                                @ColumnResult(name = "fofo_id", type = Integer.class),
                                @ColumnResult(name = "catalog_id", type = Integer.class),
                                @ColumnResult(name = "model_number", type = String.class),
                                @ColumnResult(name = "brand ", type = String.class),
                                @ColumnResult(name = "available_stock ", type = Integer.class),
                                @ColumnResult(name = "last2DaysSoldQtySum ", type = Integer.class),
                                @ColumnResult(name = "lastThirdDaySoldQty ", type = Integer.class),
                                @ColumnResult(name = "allocation ", type = Integer.class),
                                @ColumnResult(name = "purchase_limit ", type = Integer.class),
                        }
                )}
        )

})

public class FofoOpeningStock implements Serializable {

    @Id
    @Column(name = "id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;

    @Column(name = "fofo_id")
    private int fofoId;

    @Column(name = "catalog_id")
    private int catalogId;

    @Column(name = "opening_date")
    private LocalDate openingDate;

    @Column(name = "opening_qty")
    private int openingQty;

    @Column(name = "current_qty")
    private int currentQty;



    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public int getFofoId() {
        return fofoId;
    }

    public void setFofoId(int fofoId) {
        this.fofoId = fofoId;
    }

    public int getCatalogId() {
        return catalogId;
    }

    public void setCatalogId(int catalogId) {
        this.catalogId = catalogId;
    }

    public LocalDate getOpeningDate() {
        return openingDate;
    }

    public void setOpeningDate(LocalDate openingDate) {
        this.openingDate = openingDate;
    }

    public int getOpeningQty() {
        return openingQty;
    }

    public void setOpeningQty(int openingQty) {
        this.openingQty = openingQty;
    }

    public int getCurrentQty() {
        return currentQty;
    }

    public void setCurrentQty(int currentQty) {
        this.currentQty = currentQty;
    }

    @Override
    public String toString() {
        return "FofoOpeningStock{" +
                "id=" + id +
                ", fofoId=" + fofoId +
                ", catalogId=" + catalogId +
                ", openingDate=" + openingDate +
                ", openingQty=" + openingQty +
                ", currentQty=" + currentQty +
                '}';
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        FofoOpeningStock that = (FofoOpeningStock) o;
        return id == that.id && fofoId == that.fofoId && catalogId == that.catalogId && openingQty == that.openingQty && currentQty == that.currentQty && Objects.equals(openingDate, that.openingDate);
    }

    @Override
    public int hashCode() {
        return Objects.hash(id, fofoId, catalogId, openingDate, openingQty, currentQty);
    }
}