Subversion Repositories SmartDukaan

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

import javax.persistence.*;
import java.time.LocalDateTime;

@Entity
@Table(name = "fofo.activated_imei", schema = "fofo")

@NamedQueries({

                @NamedQuery(name = "ActivatedImei.selectActivatedModelGroupByBrand", query = "select new com.spice.profitmandi.dao.model.BrandWiseActivatedModel(li.brand, "
                                + "sum(case when concat(year(ai.activationTimestamp), month(ai.activationTimestamp))= :lms then 1 else 0 end),"
                                + "sum(case when concat(year(ai.activationTimestamp), month(ai.activationTimestamp))= :lms  then CAST(li.unitPrice  AS int) else 0 end),"
                                + "sum(case when concat(year(ai.activationTimestamp), month(ai.activationTimestamp))= :mtd then 1 else 0 end),"
                                + "sum(case when concat(year(ai.activationTimestamp), month(ai.activationTimestamp))= :mtd   then CAST(li.unitPrice AS int) else 0 end), "
                                + "sum(case when ai.activationTimestamp between  :lmtdStartDate and :lmtdEndDate then 1 else 0 end), "
                                + "sum(case when ai.activationTimestamp between  :lmtdStartDate and :lmtdEndDate then  CAST(li.unitPrice  AS int) else 0 end))"
                                + " from ActivatedImei ai join LineItemImeiView lim on ai.serialNumber = lim.serialNumber join LineItem li on li.id = lim.lineItemId join Order o on o.id = li.orderId "
                                + "     join FofoStore fs on fs.id = o.retailerId where (fs.fofoType = 'FRANCHISE' or fs.fofoType = 'THIRD_PARTY') and fs.id in :fofoIds group by li.brand"),

                @NamedQuery(name = "ActivatedImei.selectActivatedModelGroupByWarehouse", query = "select new com.spice.profitmandi.dao.model.WarehouseWiseActivatedModel(o.warehouseId, "
                                + "sum(case when concat(year(ai.activationTimestamp), month(ai.activationTimestamp))= :lms then 1 else 0 end),"
                                + "sum(case when concat(year(ai.activationTimestamp), month(ai.activationTimestamp))= :lms  then CAST(li.unitPrice  AS int) else 0 end),"
                                + "sum(case when concat(year(ai.activationTimestamp), month(ai.activationTimestamp))= :mtd then 1 else 0 end),"
                                + "sum(case when concat(year(ai.activationTimestamp), month(ai.activationTimestamp))= :mtd   then CAST(li.unitPrice AS int) else 0 end), "
                                + "sum(case when ai.activationTimestamp between  :lmtdStartDate and :lmtdEndDate then 1 else 0 end), "
                                + "sum(case when ai.activationTimestamp between  :lmtdStartDate and :lmtdEndDate then  CAST(li.unitPrice  AS int) else 0 end))"
                                + " from ActivatedImei ai join LineItemImeiView lim on ai.serialNumber = lim.serialNumber join LineItem li on li.id = lim.lineItemId join Order o on o.id = li.orderId "
                                + " join FofoStore fs on fs.id = o.retailerId where (fs.fofoType = 'FRANCHISE' or fs.fofoType = 'THIRD_PARTY') and li.brand = :brand and fs.id in :fofoIds"
                                + " group by o.warehouseId"),

                @NamedQuery(name = "ActivatedImei.selectWarehouseBrandActivatedItem", query = "select new com.spice.profitmandi.dao.model.WarehouseBrandWiseItemActivatedModel(fs.warehouseId, li.itemId, li.brand,li.modelName,"
                                + " li.modelNumber, li.color,"
                                + "sum(case when concat(year(ai.activationTimestamp), month(ai.activationTimestamp))= :lms then 1 else 0 end),"
                                + "sum(case when concat(year(ai.activationTimestamp), month(ai.activationTimestamp))= :lms  then CAST(li.unitPrice  AS int) else 0 end),"
                                + "sum(case when concat(year(ai.activationTimestamp), month(ai.activationTimestamp))= :mtd then 1 else 0 end),"
                                + "sum(case when concat(year(ai.activationTimestamp), month(ai.activationTimestamp))= :mtd   then CAST(li.unitPrice AS int) else 0 end), "
                                + "sum(case when ai.activationTimestamp between  :lmtdStartDate and :lmtdEndDate then 1 else 0 end), "
                                + "sum(case when ai.activationTimestamp between  :lmtdStartDate and :lmtdEndDate then  CAST(li.unitPrice  AS int) else 0 end))"
                                + " from ActivatedImei ai join LineItemImeiView lim on ai.serialNumber = lim.serialNumber join LineItem li on li.id = lim.lineItemId join Order o on o.id = li.orderId "
                                + " join FofoStore fs on fs.id = o.retailerId where (fs.fofoType = 'FRANCHISE' or fs.fofoType = 'THIRD_PARTY') and fs.warehouseId in :warehouseId and li.brand = :brand and fs.id in :fofoIds "
                                + " group by li.itemId"),

                @NamedQuery(name = "ActivatedImei.selectActivatedUpdationDate", query = "select new com.spice.profitmandi.dao.model.ActivationImeiUpdationModel(fs.warehouseId,li.brand, "
                                + " Max(ai.createTimestamp))"
                                + " from ActivatedImei ai join LineItemImeiView lim on ai.serialNumber = lim.serialNumber join LineItem li on li.id = lim.lineItemId join Order o on o.id = li.orderId "
                                + "     join FofoStore fs on fs.id = o.retailerId group by li.brand,fs.warehouseId"),

                @NamedQuery(name = "ActivatedImei.selectImeiActivationByBrand", query = "select new com.spice.profitmandi.dao.model.ImeiActivationTimestampModel(limei.serialNumber, ai.activationTimestamp) "
                                + " from Order o join LineItem  li on o.id=li.orderId join LineItemImei  limei on li.id=limei.lineItemId join FofoStore fs on fs.id=o.retailerId left join ActivatedImei ai on limei.serialNumber = ai.serialNumber "
                                + "     where (ai.createTimestamp is null or ai.createTimestamp < :daysBeforeToday) and li.brand = :brand and ai.activationTimestamp is null and fs.internal = false and date(o.billingTimestamp)>'2021-01-01'"),

                @NamedQuery(name = "ActivatedImei.selectActivatedImeisByOrders", query = "select new com.spice.profitmandi.dao.model.ImeiActivationTimestampModel(o.id, o.lineItem.unitPrice, limei.serialNumber, ai.activationTimestamp) "
                                + " from Order o join LineItem  li on o.id=li.orderId join LineItemImei  limei on li.id=limei.lineItemId join FofoStore fs on fs.id=o.retailerId left join ActivatedImei ai on limei.serialNumber = ai.serialNumber "
                                + "     where o.id in :orderIds and ai.activationTimestamp is not null"),

                @NamedQuery(name = "ActivatedImei.selectActivatedGrnPendingAmount", query = "select cast(sum(o.lineItem.unitPrice) as float )"
                                + " from Order o join LineItemImei  limei on o.lineItem.id=limei.lineItemId join FofoStore fs on fs.id=o.retailerId left join ActivatedImei ai on limei.serialNumber = ai.serialNumber "
                                + "     where o.status in (12, 9) and ai.activationTimestamp is not null and o.partnerGrnTimestamp is null and o.retailerId=:fofoId group by o.retailerId"),

})
public class ActivatedImei {
        @Id
        @Column(name = "serial_number", unique = true)
        private String serialNumber;

        @Column(name = "activation_timestamp")
        private LocalDateTime activationTimestamp;

        @Column(name = "create_timestamp")
        private LocalDateTime createTimestamp;

        public ActivatedImei() {
                super();
        }

        public ActivatedImei(String serialNumber, LocalDateTime activationTimestamp) {
                this.activationTimestamp = activationTimestamp;
                this.serialNumber = serialNumber;
        }

        @Override
        public String toString() {
                return "ActivatedImei [serialNumber=" + serialNumber + ", activationTimestamp=" + activationTimestamp
                                + ", createTimestamp=" + createTimestamp + "]";
        }

        public String getSerialNumber() {
                return serialNumber;
        }

        public void setSerialNumber(String serialNumber) {
                this.serialNumber = serialNumber;
        }

        public LocalDateTime getActivationTimestamp() {
                return activationTimestamp;
        }

        public void setActivationTimestamp(LocalDateTime activationTimestamp) {
                this.activationTimestamp = activationTimestamp;
        }

        public LocalDateTime getCreateTimestamp() {
                return createTimestamp;
        }

        public void setCreateTimestamp(LocalDateTime createTimestamp) {
                this.createTimestamp = createTimestamp;
        }

        @Override
        public int hashCode() {
                final int prime = 31;
                int result = 1;
                result = prime * result + ((activationTimestamp == null) ? 0 : activationTimestamp.hashCode());
                result = prime * result + ((createTimestamp == null) ? 0 : createTimestamp.hashCode());
                result = prime * result + ((serialNumber == null) ? 0 : serialNumber.hashCode());
                return result;
        }

        @Override
        public boolean equals(Object obj) {
                if (this == obj)
                        return true;
                if (obj == null)
                        return false;
                if (getClass() != obj.getClass())
                        return false;
                ActivatedImei other = (ActivatedImei) obj;
                if (activationTimestamp == null) {
                        if (other.activationTimestamp != null)
                                return false;
                } else if (!activationTimestamp.equals(other.activationTimestamp))
                        return false;
                if (createTimestamp == null) {
                        if (other.createTimestamp != null)
                                return false;
                } else if (!createTimestamp.equals(other.createTimestamp))
                        return false;
                if (serialNumber == null) {
                        if (other.serialNumber != null)
                                return false;
                } else if (!serialNumber.equals(other.serialNumber))
                        return false;
                return true;
        }

}