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 > :billingStartDate"

				+ " and o.billingTimestamp < :billedBefore"

				+ " and not exists (select 1 from FofoLineItem fli where fli.serialNumber = limei.serialNumber)"

				+ " and limei.serialNumber is not null and limei.serialNumber <> ''"),

		@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 > :billingStartDate"

				+ " and fo.createTimestamp < :billedBefore"

				+ " and fli.serialNumber is not null and fli.serialNumber <> ''"),

		@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 > :billingStartDate"),

		@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')"),

		@NamedQuery(name = "ActivatedImei.getMonthlyUnbilledTertiaryPriceForFofoIds", 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 in :fofoIds group by ii.fofoId, 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;

	}

}