Subversion Repositories SmartDukaan

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

import com.spice.profitmandi.dao.enumuration.transaction.SchemePayoutStatus;

import javax.persistence.*;

import java.time.LocalDateTime;

import java.util.Objects;

@Entity

@Table(name = "fofo.offer_payout")

@NamedQueries({

        @NamedQuery(name = "OfferPayout.selectPaidOffers", query = "select new com.spice.profitmandi.service.transaction.InventoryMarginModel("

                + " o.retailerId, li.sgstRate, li.cgstRate, li.igstRate," +

                " cast(case when op.createTimestamp >= :startDate and op.createTimestamp <= :endDate then op.amount " +

                "			when op.rejectTimestamp >= :startDate and op.rejectTimestamp <= :endDate then -op.amount" +

                "			else 0 end as float),  op.inventoryItemId,  lii.serialNumber, op.description, o.invoiceNumber, " +

                " case when op.rejectTimestamp >= :startDate and op.rejectTimestamp <= :endDate then op.rejectTimestamp else op.createTimestamp end, ii.hsnCode)  from "

                + " OfferPayout op join InventoryItem ii on ii.id=op.inventoryItemId join LineItemImei lii on (op.serialNumber=lii.serialNumber) join LineItem li on li.id=lii.lineItemId "

                + " join Order o on (o.retailerId=op.fofoId and o.billingTimestamp is not null and o.id=li.orderId) "

                + " where op.createTimestamp >= :cnDate and ((op.createTimestamp >= :startDate and op.createTimestamp <= :endDate) or (op.rejectTimestamp >= :startDate and op.rejectTimestamp <= :endDate))"

                + " and not (op.rejectTimestamp is not null and op.createTimestamp >= :startDate and op.createTimestamp <= :endDate and op.rejectTimestamp >= :startDate and op.rejectTimestamp <= :endDate)"

                + " and lii.id=(select max(lii2.id) from LineItemImei lii2 join LineItem li2 on li2.id=lii2.lineItemId join Order o2 on o2.id=li2.orderId where lii2.serialNumber=lii.serialNumber and o2.retailerId=op.fofoId) "

                + " AND (:fofoId IS NULL OR o.retailerId = :fofoId)"),

        @NamedQuery(name = "OfferPayout.getTotalPayoutsByPartnerPeriod", query = "select new com.spice.profitmandi.common.model.OfferPayoutImeiIncomeModel(" +

                " ii.id, ii.serialNumber, " +

                " case when (sr.type='PURCHASE' and o.schemeType='SELLIN') then max(sr.createTimestamp) end," +

                " case when (sr.type='SALE' and o.schemeType != 'SELLIN') then max(sr.createTimestamp)  end, " +

                " case when (sr.type='PURCHASE' and o.schemeType='SELLIN') then sum(op.amount)  else 0 end, " +

                " case when (sr.type='SALE' and o.schemeType!='SELLIN') then sum(op.amount) else 0 end, " +

                " i.catalogItemId, i.brand, i.modelName, i.modelNumber" +

                ") from OfferPayout op join Offer o on o.id=op.offerId join InventoryItem ii on ii.serialNumber=op.serialNumber and op.fofoId=ii.fofoId" +

                " join Item i on i.id=ii.itemId join ScanRecord  sr on sr.inventoryItemId=ii.id " +

                " and ((sr.type='PURCHASE' and o.schemeType='SELLIN') or (sr.type='SALE' and o.schemeType!='SELLIN'))" +

                " where op.rejectTimestamp is null and sr.createTimestamp between :startDate and :endDate" +

                " and (:brand is null or i.brand=:brand)" +

                " and (:catalogId is null or i.catalogItemId=:catalogId)" +

                " and op.fofoId=:fofoId" +

                " group by ii.id, sr.type "),

        @NamedQuery(name = "OfferPayout.getTotalPayoutsByPartnerPeriodByMonth", query = "select new com.spice.profitmandi.dao.model.MonthlyOfferPayoutModel(" +

                " (year(sr.createTimestamp)*100 + month(sr.createTimestamp))," +

                " i.brand," +

                " sum(case when (sr.type='PURCHASE' and o.schemeType='SELLIN') then op.amount else 0 end)," +

                " sum(case when (sr.type='SALE' and o.schemeType!='SELLIN') then op.amount else 0 end)" +

                ") from OfferPayout op join Offer o on o.id=op.offerId join InventoryItem ii on ii.serialNumber=op.serialNumber and op.fofoId=ii.fofoId" +

                " join Item i on i.id=ii.itemId join ScanRecord  sr on sr.inventoryItemId=ii.id " +

                " and ((sr.type='PURCHASE' and o.schemeType='SELLIN') or (sr.type='SALE' and o.schemeType!='SELLIN'))" +

                " where op.rejectTimestamp is null and sr.createTimestamp between :startDate and :endDate" +

                " and op.fofoId=:fofoId" +

                " group by (year(sr.createTimestamp)*100 + month(sr.createTimestamp)), i.brand "),

        @NamedQuery(name = "OfferPayout.getTotalPayoutsByImei", query = "select new com.spice.profitmandi.common.model.OfferPayoutImeiIncomeModel(" +

                " ii.id, ii.serialNumber, " +

                " max(case when (sr.type='PURCHASE' and o.schemeType='SELLIN') then sr.createTimestamp end)," +

                " max(case when (sr.type='SALE' and o.schemeType != 'SELLIN') then sr.createTimestamp  end), " +

                " sum(case when (sr.type='PURCHASE' and o.schemeType='SELLIN') then op.amount  else 0 end), " +

                " sum(case when (sr.type='SALE' and o.schemeType!='SELLIN') then op.amount else 0 end), " +

                " i.catalogItemId, i.brand, i.modelName, i.modelNumber" +

                ") from OfferPayout op join Offer o on o.id=op.offerId join InventoryItem ii on ii.serialNumber=op.serialNumber and op.fofoId=ii.fofoId" +

                " join Item i on i.id=ii.itemId join ScanRecord  sr on sr.inventoryItemId=ii.id " +

                " and ((sr.type='PURCHASE' and o.schemeType='SELLIN') or (sr.type='SALE' and o.schemeType!='SELLIN'))" +

                " where op.rejectTimestamp is null and ii.serialNumber in :serialNumbers" +

                " group by ii.id "),

        /*,

        @NamedQuery(name = "OfferPayout.selectMarginsByYearMonth", query = "select com.spice.profitmandi.common.model.MarginModel(" +

                " " +

                ") from Order ord join    OfferPayout op join Offer on o.id=op.offerId join InventoryItem ii on ii.serialNumber=op.serialNumber and op.fofoId=ii.fofoId" +

                " join Item i on i.id=ii.itemId join ScanRecord  sr on sr.inventoryItemId=ii.id " +

                " and ((sr.type='PURCHASE' and o.schemeType='SELLIN') or (sr.type='SALE' and o.schemeType!='SELLIN'))" +

                " join  " +

                " where op.rejectTimestamp is null and ii.serialNumber in :serialNumbers" +

                " group by ii.id ")*/

        @NamedQuery(name = "OfferPayout.selectOfferEarningsByFofoId",

                query = "select new com.spice.profitmandi.dao.model.OfferEarningModel("

                        + " op.offerId, o.name, o.offerNotes,"

                        + " count(distinct op.serialNumber), sum(op.amount))"

                        + " from OfferPayout op join Offer o on o.id=op.offerId"

                        + " where op.fofoId=:fofoId"

                        + " and op.createTimestamp >= :startDate and op.createTimestamp <= :endDate"

                        + " and op.rejectTimestamp is null"

                        + " and op.status = 'CREDITED'"

                        + " group by op.offerId, o.name, o.offerNotes")

})

public class OfferPayout {

        @Id

        @GeneratedValue(strategy = GenerationType.IDENTITY)

        private long id;

        @Column(name = "fofo_id", nullable = false)

        private long fofoId;

        @Column(name = "offer_id", nullable = false)

        private long offerId;

        @Column(name = "criteria_id", nullable = false)

        private long criteriaId;

        @Column(name = "slab_amount", nullable = false)

        private double slabAmount;

        @Column(name = "serial_number", nullable = false)

        private String serialNumber;

        @Column(name = "amount", nullable = false)

        private double amount;

        @Column(name = "status", nullable = false)

        @Enumerated(EnumType.STRING)

        private SchemePayoutStatus status;

        @Column(name = "description", nullable = false)

        private String description;

        @Column(name = "inventory_item_id", nullable = false)

        private int inventoryItemId;

        @Column(name = "create_timestamp", nullable = false)

        private LocalDateTime createTimestamp;

        @Column(name = "reject_timestamp")

        private LocalDateTime rejectTimestamp;

        public OfferPayout() {

        }

        public OfferPayout(long fofoId, long offerId, long criteriaId, double slabAmount, String serialNumber, double amount, SchemePayoutStatus status, String description, LocalDateTime createTimestamp) {

                this.fofoId = fofoId;

                this.offerId = offerId;

                this.criteriaId = criteriaId;

                this.slabAmount = slabAmount;

                this.serialNumber = serialNumber;

                this.amount = amount;

                this.status = status;

                this.description = description;

                this.createTimestamp = createTimestamp;

        }

        @Override

        public String toString() {

                return "OfferPayout{" +

                        "id=" + id +

                        ", fofoId=" + fofoId +

                        ", offerId=" + offerId +

                        ", criteriaId=" + criteriaId +

                        ", slabAmount=" + slabAmount +

                        ", serialNumber='" + serialNumber + '\'' +

                        ", amount=" + amount +

                        ", status=" + status +

                        ", description='" + description + '\'' +

                        ", inventoryItemId=" + inventoryItemId +

                        ", createTimestamp=" + createTimestamp +

                        ", rejectTimestamp=" + rejectTimestamp +

                        '}';

        }

        public int getInventoryItemId() {

                return inventoryItemId;

        }

        public void setInventoryItemId(int inventoryItemId) {

                this.inventoryItemId = inventoryItemId;

        }

        @Override

        public boolean equals(Object o) {

                if (this == o) return true;

                if (o == null || getClass() != o.getClass()) return false;

                OfferPayout that = (OfferPayout) o;

                return id == that.id && fofoId == that.fofoId && offerId == that.offerId && criteriaId == that.criteriaId && Double.compare(that.slabAmount, slabAmount) == 0 && Double.compare(that.amount, amount) == 0 && inventoryItemId == that.inventoryItemId && Objects.equals(serialNumber, that.serialNumber) && status == that.status && Objects.equals(description, that.description) && Objects.equals(createTimestamp, that.createTimestamp) && Objects.equals(rejectTimestamp, that.rejectTimestamp);

        }

        @Override

        public int hashCode() {

                return Objects.hash(id, fofoId, offerId, criteriaId, slabAmount, serialNumber, amount, status, description, inventoryItemId, createTimestamp, rejectTimestamp);

        }

        public LocalDateTime getCreateTimestamp() {

                return createTimestamp;

        }

        public void setCreateTimestamp(LocalDateTime createTimestamp) {

                this.createTimestamp = createTimestamp;

        }

        public LocalDateTime getRejectTimestamp() {

                return rejectTimestamp;

        }

        public void setRejectTimestamp(LocalDateTime rejectTimestamp) {

                this.rejectTimestamp = rejectTimestamp;

        }

        public long getId() {

                return id;

        }

        public void setId(long id) {

                this.id = id;

        }

        public long getFofoId() {

                return fofoId;

        }

        public void setFofoId(long fofoId) {

                this.fofoId = fofoId;

        }

        public long getOfferId() {

                return offerId;

        }

        public void setOfferId(long offerId) {

                this.offerId = offerId;

        }

        public long getCriteriaId() {

                return criteriaId;

        }

        public void setCriteriaId(long criteriaId) {

                this.criteriaId = criteriaId;

        }

        public double getSlabAmount() {

                return slabAmount;

        }

        public void setSlabAmount(double slabAmount) {

                this.slabAmount = slabAmount;

        }

        public String getSerialNumber() {

                return serialNumber;

        }

        public void setSerialNumber(String serialNumber) {

                this.serialNumber = serialNumber;

        }

        public double getAmount() {

                return amount;

        }

        public void setAmount(double amount) {

                this.amount = amount;

        }

        public SchemePayoutStatus getStatus() {

                return status;

        }

        public void setStatus(SchemePayoutStatus status) {

                this.status = status;

        }

        public String getDescription() {

                return description;

        }

        public void setDescription(String description) {

                this.description = description;

        }

}