Subversion Repositories SmartDukaan

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

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

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

@NamedQueries({

                @NamedQuery(name = "ActivatedImei.selectActivatedModelGroupByBrand", query = "select new com.spice.profitmandi.dao.model.BrandWiseActivatedModel(li.brand, "
                                + "sum(case when ai.activationTimestamp >= :lmsStartDate and ai.activationTimestamp < :mtdStartDate then 1 else 0 end),"
                                + "sum(case when ai.activationTimestamp >= :lmsStartDate and ai.activationTimestamp < :mtdStartDate then CAST(li.unitPrice AS int) else 0 end),"
                                + "sum(case when ai.activationTimestamp >= :mtdStartDate then 1 else 0 end),"
                                + "sum(case when ai.activationTimestamp >= :mtdStartDate then CAST(li.unitPrice AS int) else 0 end), "
                                + "sum(case when ai.activationTimestamp >= :lmtdStartDate and ai.activationTimestamp < :lmtdEndDate then 1 else 0 end), "
                        + "sum(case when ai.activationTimestamp >= :lmtdStartDate and ai.activationTimestamp < :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 ai.activationTimestamp >= :lmsStartDate and (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 ai.activationTimestamp >= :lmsStartDate and ai.activationTimestamp < :mtdStartDate then 1 else 0 end),"
                                + "sum(case when ai.activationTimestamp >= :lmsStartDate and ai.activationTimestamp < :mtdStartDate then CAST(li.unitPrice AS int) else 0 end),"
                                + "sum(case when ai.activationTimestamp >= :mtdStartDate then 1 else 0 end),"
                                + "sum(case when ai.activationTimestamp >= :mtdStartDate then CAST(li.unitPrice AS int) else 0 end), "
                                + "sum(case when ai.activationTimestamp >= :lmtdStartDate and ai.activationTimestamp < :lmtdEndDate then 1 else 0 end), "
                                + "sum(case when ai.activationTimestamp >= :lmtdStartDate and ai.activationTimestamp < :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 ai.activationTimestamp >= :lmsStartDate and (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 ai.activationTimestamp >= :lmsStartDate and ai.activationTimestamp < :mtdStartDate then 1 else 0 end),"
                                + "sum(case when ai.activationTimestamp >= :lmsStartDate and ai.activationTimestamp < :mtdStartDate then CAST(li.unitPrice AS int) else 0 end),"
                                + "sum(case when ai.activationTimestamp >= :mtdStartDate then 1 else 0 end),"
                                + "sum(case when ai.activationTimestamp >= :mtdStartDate then CAST(li.unitPrice AS int) else 0 end), "
                                + "sum(case when ai.activationTimestamp >= :lmtdStartDate and ai.activationTimestamp < :lmtdEndDate then 1 else 0 end), "
                                + "sum(case when ai.activationTimestamp >= :lmtdStartDate and ai.activationTimestamp < :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 ai.activationTimestamp >= :lmsStartDate and (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 LineItemImei 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 ai.createTimestamp >= :startDate 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 o.billingTimestamp > '2021-01-01'"
                                + " and limei.serialNumber not in (select fli.serialNumber from FofoLineItem fli)"),

                @NamedQuery(name = "ActivatedImei.selectImeiActivationByBrandTertiary", query = "select new com.spice.profitmandi.dao.model.ImeiActivationTimestampModel(fli.serialNumber, ai.activationTimestamp) "
                                + " from FofoOrder fo join FofoOrderItem foi on fo.id=foi.orderId join FofoLineItem fli on fli.fofoOrderItemId=foi.id "
                                + " join Item ci on ci.id=foi.itemId join FofoStore fs on fs.id=fo.fofoId left join ActivatedImei ai on fli.serialNumber = ai.serialNumber "
                                + " where (ai.createTimestamp is null or ai.createTimestamp < :daysBeforeToday) and ci.brand = :brand and ai.activationTimestamp is null and fo.cancelledTimestamp is null and fs.internal = false and fo.createTimestamp > '2021-01-01'"),

                @NamedQuery(name = "ActivatedImei.selectImeiSoldNotActivatedByBrand", query = "select new com.spice.profitmandi.dao.model.ImeiActivationTimestampModel(fli.serialNumber, ai.activationTimestamp) "
                                + " from FofoOrder fo join FofoOrderItem foi on fo.id=foi.orderId join FofoLineItem fli on fli.fofoOrderItemId=foi.id "
                                + " join Item ci on ci.id=foi.itemId join FofoStore fs on fs.id=fo.fofoId left join ActivatedImei ai on fli.serialNumber = ai.serialNumber "
                                + " where ai.createTimestamp is null and ci.brand = :brand and ai.activationTimestamp is null and fo.cancelledTimestamp is null and fs.internal = false and fo.createTimestamp > '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"),

                @NamedQuery(name = "ActivatedImei.selectActivatedGrnPendingAmountByFofoIds", query = "select o.retailerId, 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 in :fofoIds group by o.retailerId"),

                @NamedQuery(name = "ActivatedImei.getMonthlyUnbilledTertiaryPrice", query = "select new com.spice.profitmandi.dao.model.PartnerWiseActivatedNotBilledTotal(ii.fofoId, sum((tl.mop)), DATE_FORMAT(ai.activationTimestamp, '%Y-%m')) "
                                +" from InventoryItem ii" +
                                " join Item i on i.id=ii.itemId" +
                                " join TagListing tl on tl.itemId=i.id" +
                                " join ActivatedImei ai on ai.serialNumber=ii.serialNumber" +
                                " WHERE ii.goodQuantity=1 and ai.activationTimestamp is not null and ai.activationTimestamp >= :startDate " +
                                " and ii.fofoId= :fofoId group by DATE_FORMAT(ai.activationTimestamp, '%Y-%m')"),
})
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;

        @Column
        private boolean checked = false;

        @Column(name = "auth_id")
        private int authId;

        public ActivatedImei() {
                super();
        }

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

        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;
        }

        public int getAuthId() {
                return authId;
        }

        public void setAuthId(int authId) {
                this.authId = authId;
        }

        @Override
        public String toString() {
                return "ActivatedImei{" +
                                "serialNumber='" + serialNumber + '\'' +
                                ", activationTimestamp=" + activationTimestamp +
                                ", createTimestamp=" + createTimestamp +
                                ", checked=" + checked +
                                ", authId=" + authId +
                                '}';
        }

        @Override
        public boolean equals(Object o) {
                if (this == o) return true;
                if (o == null || getClass() != o.getClass()) return false;
                ActivatedImei that = (ActivatedImei) o;
                return checked == that.checked && Objects.equals(serialNumber, that.serialNumber) && Objects.equals(activationTimestamp, that.activationTimestamp) && Objects.equals(createTimestamp, that.createTimestamp);
        }

        @Override
        public int hashCode() {
                return Objects.hash(serialNumber, activationTimestamp, createTimestamp, checked);
        }

        public boolean isChecked() {
                return checked;
        }

        public void setChecked(boolean checked) {
                this.checked = checked;
        }

}