Subversion Repositories SmartDukaan

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

import com.spice.profitmandi.dao.model.BrandCountByStatus;
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," +
                        "   (CASE" +
                        "         WHEN ((COALESCE(sale1.last2daysSale, 0) * :allocationConst) = 0)" +
                        "             OR (COALESCE(SUM(os.current_qty), 0) = 0) THEN 2" +
                        "         ELSE ROUND((COALESCE(sale1.last2daysSale, 0) * :allocationConst))" +
                        "        END) AS allocation" +
                        " 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 o.fofo_id = :fofoId" +
                        "      AND o.create_timestamp >= CURDATE() - INTERVAL 2 DAY" +
                        "    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" +
                        "         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"),
        @NamedNativeQuery(
                name = "Stock.PartnerHidFastMovingRunningstock",
                query = "SELECT " +
                        "    fos.catalog_id, " +
                        "    c.brand, " +
                        "    COUNT(*) AS item_count " +
                        "FROM fofo.fofo_opening_stock fos " +
                        "JOIN catalog.catagoriesd_catalog cc ON fos.catalog_id = cc.catalog_id AND cc.end_date IS NULL " +
                        "JOIN catalog.catalog c ON c.id = fos.catalog_id " +
                        "WHERE cc.status IN ('RUNNING', 'FASTMOVING', 'HID') " +
                        "  AND fos.current_qty > 0 " +
                        "  AND fos.fofo_id = :fofoId " +
                        "GROUP BY fos.catalog_id, c.brand",
                resultSetMapping = "HidFastMovingRunningStock"
        )
})
@SqlResultSetMapping(
        name = "HidFastMovingRunningStock",
        classes = @ConstructorResult(
                targetClass = BrandCountByStatus.class,
                columns = {
                        @ColumnResult(name = "catalog_id", type = int.class),
                        @ColumnResult(name = "brand", type = String.class),
                        @ColumnResult(name = "item_count", type = int.class)
                }
        )
)

@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 = "allocation ", 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);
    }
}