Rev 30160 | Rev 30178 | Go to most recent revision | View as "text/plain" | Blame | Compare with Previous | Last modification | View Log | RSS feed
package com.spice.profitmandi.dao.entity.fofo;import java.io.Serializable;import java.time.LocalDate;import java.time.LocalDateTime;import java.time.format.DateTimeFormatter;import javax.persistence.Column;import javax.persistence.Convert;import javax.persistence.Entity;import javax.persistence.GeneratedValue;import javax.persistence.GenerationType;import javax.persistence.Id;import javax.persistence.NamedQueries;import javax.persistence.NamedQuery;import javax.persistence.Table;import javax.persistence.Transient;import com.spice.profitmandi.dao.convertor.LocalDateTimeAttributeConverter;@Entity@Table(name = "fofo.fofo_order", schema = "fofo")@NamedQueries({@NamedQuery(name = "FofoOrder.SelectItemWiseTertiary", query = "select new com.spice.profitmandi.dao.model.ItemWiseTertiaryModel(foi.brand,"+ "foi.modelName, foi.modelNumber, foi.color, foi.quantity, foi.mop)"+ " from FofoOrder fo join FofoOrderItem foi on fo.id = foi.orderId where fo.cancelledTimestamp is null and fo.fofoId = :fofoId"+ " and fo.createTimestamp between :startDate and :endDate"),@NamedQuery(name = "FofoOrder.selectGroupByBrandPartnerTertiary", query = "select new com.spice.profitmandi.dao.model.BrandWiseTertiaryModel(foi.brand, "+ "sum(case when foi.createTimestamp >= :today then CAST(foi.quantity*foi.mop AS int) else 0 end),"+ "sum(case when foi.createTimestamp >= :threedays and foi.createTimestamp < :endDate then CAST(foi.quantity*foi.mop AS int) else 0 end),"+ "sum(case when concat(year(foi.createTimestamp), month(foi.createTimestamp))= :mtd then CAST(foi.quantity*foi.mop AS int) else 0 end),"+ "sum(case when foi.createTimestamp between :lmtdStartDate and :lmtdEndDate then CAST(foi.quantity*foi.mop AS int) else 0 end),"+ "sum(case when foi.createTimestamp between :lmtdStartDate and :lmsEndDate then CAST(foi.quantity*foi.mop AS int) else 0 end),"+ "sum(case when foi.createTimestamp >= :today then foi.quantity else 0 end),"+ "sum(case when foi.createTimestamp >= :threedays and foi.createTimestamp < :endDate then foi.quantity else 0 end),"+ "sum(case when concat(year(foi.createTimestamp), month(foi.createTimestamp))= :mtd then foi.quantity else 0 end), "+ "sum(case when foi.createTimestamp between :lmtdStartDate and :lmtdEndDate then foi.quantity else 0 end),"+ "sum(case when foi.createTimestamp between :lmtdStartDate and :lmsEndDate then foi.quantity else 0 end)"+ " )"+ " from FofoStore fs join FofoOrder fo on fs.id = fo.fofoId join FofoOrderItem foi on foi.orderId = fo.id join Item i on i.id = foi.itemId "+ " where fo.cancelledTimestamp is null and fs.active = true "+ " and foi.createTimestamp >= :lmtdStartDate and fs.warehouseId in :warehouseId"+ " group by foi.brand "),@NamedQuery(name = "FofoOrder.selectItemPartnerTertiaryByBrand", query = "select new com.spice.profitmandi.dao.model.BrandItemWiseTertiaryModel(foi.brand, i.modelName,"+ " i.modelNumber, i.color, foi.quantity*foi.mop, foi.quantity)"+ " from FofoStore fs join FofoOrder fo on fs.id = fo.fofoId join FofoOrderItem foi on foi.orderId = fo.id join "+ " Item i on i.id = foi.itemId where fo.cancelledTimestamp is null and fs.active = true "+ " and foi.createTimestamp >= :startDate and foi.createTimestamp < :endDate and fs.warehouseId in :warehouseId and foi.brand in :brand order by foi.itemId desc"),@NamedQuery(name = "FofoOrder.selectTodayItemPartnerTertiaryByBrand", query = "select new com.spice.profitmandi.dao.model.BrandItemWiseTertiaryModel(foi.brand, i.modelName,"+ " i.modelNumber, i.color, foi.quantity*foi.mop, foi.quantity)"+ " from FofoStore fs join FofoOrder fo on fs.id = fo.fofoId join FofoOrderItem foi on foi.orderId = fo.id join "+ " Item i on i.id = foi.itemId where fo.cancelledTimestamp is null and fs.active = true "+ " and foi.createTimestamp >= :startDate and fs.warehouseId in :warehouseId and foi.brand in :brand order by foi.itemId desc"),@NamedQuery(name = "FofoOrder.selectValueOfActivatedImeis", query = "select new com.spice.profitmandi.common.model.ActivatedImeisWithSellingPrice("+ " fo.fofoId, foi.brand, sum(CAST(foi.mop AS int) * foi.quantity)) "+ " from FofoOrder fo join FofoOrderItem foi on fo.id = foi.orderId join FofoLineItem fli on foi.id = fli.fofoOrderItemId join ActivatedImei ai on "+ " fli.serialNumber = ai.serialNumber where fo.createTimestamp between :startDate and :endDate and fo.fofoId = :fofoId and fo.cancelledTimestamp is null group by foi.brand"),@NamedQuery(name = "FofoOrder.selectPartnersSaleByCatalogId", query = "select new com.spice.profitmandi.dao.model.FofoIdQtyModel("+ " fs.warehouseId, fs.id,sum(case when foi.quantity is null then 0 else foi.quantity end))"+ " from FofoStore fs join FofoOrder fo on fs.id = fo.fofoId "+ " join FofoOrderItem foi on foi.orderId = fo.id join TagListing tl on tl.itemId = foi.itemId "+ " join Item i on i.id = tl.itemId"+ " where fs.active = 1 and fs.internal = 0 and fo.cancelledTimestamp is null and fo.createTimestamp > :startDate and i.catalogItemId in :catalogItemId and i.categoryId=10006 group by fs.id"),@NamedQuery(name = "FofoOrder.selectPartnersSaleByRange", query = "select new com.spice.profitmandi.dao.model.FofoIdItemDetailModel("+ " fs.warehouseId, fs.id,sum(case when foi.quantity is null then 0 else foi.quantity end),i.brand,i.modelName,i.modelNumber,i.catalogItemId)"+ " from FofoStore fs join FofoOrder fo on fs.id = fo.fofoId "+ " join FofoOrderItem foi on foi.orderId = fo.id join TagListing tl on tl.itemId = foi.itemId "+ " join Item i on i.id = tl.itemId"+ " where fs.active = 1 and fs.internal = 0 and fo.cancelledTimestamp is null "+ " and fo.createTimestamp > :startDate and tl.mop between :startPrice and :endPrice and i.categoryId=10006 group by fs.id,i.catalogItemId,i.brand,i.modelName,i.modelNumber"),@NamedQuery(name = "FofoOrder.selectPartnersSaleItemByCatalogIdFofoId", query = "select new com.spice.profitmandi.dao.model.FofoIdItemDetailModel("+ " fs.warehouseId, fs.id,sum(foi.quantity),i.brand,i.modelName,i.modelNumber,i.catalogItemId)"+ " from FofoStore fs join FofoOrder fo on fs.id = fo.fofoId "+ " join FofoOrderItem foi on foi.orderId = fo.id join TagListing tl on tl.itemId = foi.itemId "+ " join Item i on i.id = tl.itemId"+ " where fs.active = 1 and fs.internal = 0 and fo.cancelledTimestamp is null and fo.createTimestamp > :startDate and i.catalogItemId in :catalogItemId and i.categoryId=10006 and fs.id = :fofoId group by i.catalogItemId, i.brand,i.modelName,i.modelNumber"),@NamedQuery(name = "FofoOrder.selectSaleByCatalogId", query = "select new com.spice.profitmandi.dao.model.FofoIdCatalogQtyModel("+ " fs.warehouseId, fs.id,i.catalogItemId, sum(case when foi.quantity is null then 0 else foi.quantity end))"+ " from FofoStore fs join FofoOrder fo on fs.id = fo.fofoId "+ " join FofoOrderItem foi on foi.orderId = fo.id join TagListing tl on tl.itemId = foi.itemId "+ " join Item i on i.id = tl.itemId"+ " where fs.active = 1 and fs.internal = 0 and fo.cancelledTimestamp is null and fo.createTimestamp > :startDate and i.catalogItemId in :catalogItemId and fs.warehouseId in :warehouseId and i.categoryId=10006 group by i.catalogItemId, fs.id"),@NamedQuery(name = "FofoOrder.selectFocoSaleReport", query = "select new com.spice.profitmandi.dao.model.FocoSaleReportModel(fs.code, ua.name,ua.city, ua.state, w.displayName, foi.itemId,"+ " foi.brand, foi.modelName, foi.modelNumber, foi.color, foi.quantity, cast(foi.dp As int), cast(foi.sellingPrice As int), cast(foi.mop As int), fli.serialNumber, foi.createTimestamp, ca.name, ca.phoneNumber,"+ " ca.city, ca.pinCode, fo.invoiceNumber, p.purchaseReference, fo.customerGstNumber, fo.cancelledTimestamp, p.completeTimestamp, hd.hygieneRating,hd.rating, hd.status, hd.remark,"+ " hd.createdTimestamp, hd.disposedTimestamp, hd.nextTimestamp, ai.activationTimestamp, ai.createTimestamp, c.label)"+ " from FofoStore fs join Warehouse w on w.id=fs.warehouseId join com.spice.profitmandi.dao.entity.user.User u on u.id=fs.id join Address ua on u.addressId = ua.id join FofoOrder fo on fo.fofoId=fs.id left join FofoOrderItem foi"+ " on foi.orderId=fo.id join Item i on i.id=foi.itemId join Category c on c.id=i.categoryId left join CustomerAddress ca on ca.id=fo.customerAddressId left join FofoLineItem fli on fli.fofoOrderItemId=foi.id"+ " left join InventoryItem ii on ii.id=fli.inventoryItemId left join Purchase p on ii.purchaseId=p.id left join HygieneData hd on hd.orderId=fo.id left join ActivatedImei ai on ai.serialNumber=fli.serialNumber"+ " where 1=1 and (foi.createTimestamp between :startDate and :endDate or foi.createTimestamp is null) and fs.code= :code and fs.id=:fofoId group by fo.id "),@NamedQuery(name = "FofoOrder.selectWalletSummaryReport", query = "select new com.spice.profitmandi.dao.model.WalletSummaryReportModel(uwh.id, fs.code, ua.name, "+ " u.emailId, ua.phoneNumber, uwh.amount, uwh.refundableAmount, uwh.reference, uwh.referenceType, uwh.timestamp, uwh.businessTimestamp, uwh.description)"+ " from FofoStore fs join com.spice.profitmandi.dao.entity.user.User u on u.id=fs.id join"+ " Address ua on u.addressId=ua.id join UserWallet uw on uw.userId=fs.id join UserWalletHistory uwh on uwh.walletId=uw.id "+ " where 1=1 and uwh.timestamp between :startDate and :endDate and fs.id=:fofoId group by uwh.timestamp"),@NamedQuery(name = "FofoOrder.selectPendingIndentReport", query = "select new com.spice.profitmandi.dao.model.PendingIndentReportModel(o.transactionId , o.id, o.createTimestamp,"+ " l.itemId, l.brand, l.modelName, l.modelNumber, l.color, l.quantity, l.unitPrice, o.walletAmount,case o.status when 3 then 'In Process' when 4 then 'Accepted' "+ " when 7 then 'Billed' when 9 then 'Shipped' when 12 then 'Delivered' when 13 then 'Cancellation in process' when 15 then 'Cancelled,amount refunded to wallet' "+ " when 31 then 'Returned to Origin, refunded' when 34 then 'Low inventory cancellation, refunded' "+ " else o.statusDescription end as Description, o.invoiceNumber, o.billingTimestamp ) from Order o join LineItem l on l.orderId=o.id "+ " where o.partnerGrnTimestamp is null and o.retailerId= :fofoId and o.status != 1 and o.createTimestamp between :startDate and :endDate group by o.status,o.createTimestamp "),@NamedQuery(name = "FofoOrder.selectSchemePayoutReport", query = "select new com.spice.profitmandi.dao.model.SchemePayoutReportModel(i.id, i.brand, i.modelName, i.modelNumber,"+ " i.color,ii.unitPrice-ii.priceDropAmount,foi.dp, s.id, s.name, s.type, s.amountType, s.amount,p.purchaseReference, fo.invoiceNumber,(case when sio.rolledBackTimestamp is null "+ " then sio.amount when sio.rolledBackTimestamp is not null then -sio.amount end),sio.status, sio.statusDescription, sio.createTimestamp, sio.rolledBackTimestamp, ii.serialNumber ) from "+ " SchemeInOut sio join InventoryItem ii on sio.inventoryItemId=ii.id join com.spice.profitmandi.dao.entity.catalog.Item i on i.id=ii.itemId join FofoStore fs on fs.id=ii.fofoId join com.spice.profitmandi.dao.entity.user.User u on u.id = fs.id join"+ " Address ua on u.addressId = ua.id join Scheme s on sio.schemeId=s.id join Purchase p on p.id=ii.purchaseId left join FofoLineItem fli on fli.inventoryItemId=ii.id left join FofoOrderItem foi "+ " on foi.id=fli.fofoOrderItemId left join FofoOrder fo on fo.id=foi.orderId where foi.createTimestamp between :startDate and :endDate and ii.fofoId= :fofoId "),@NamedQuery(name = "FofoOrder.selectInvoiceSchemeOutSummaryReport", query = "select new com.spice.profitmandi.dao.model.FocoSchemeOutReportModel(fo.invoiceNumber, foi.quantity, foi.brand,"+ " foi.modelName, foi.modelNumber, foi.color, sum(cast(sio.amount As float)) ) FROM FofoOrderItem foi join FofoOrder fo on fo.id=foi.orderId join FofoLineItem fli on "+ " fli.fofoOrderItemId=foi.id join SchemeInOut sio on sio.inventoryItemId=fli.inventoryItemId join Scheme s on s.id=sio.schemeId where s.type ='IN' and fo.fofoId= :fofoId and"+ " fo.createTimestamp between :startDate and :endDate and sio.rolledBackTimestamp is null group by fo.id, foi.id "),// @NamedQuery(name = "FofoOrder.selectPartnerBillingSummaryReport", query = "select new com.spice.profitmandi.dao.model.PartnerBillingSummaryModel(o.id, o.createTimestamp, o.billingTimestamp,"// + " o.deliveryTimestamp, o.partnerGrnTimestamp, o.transactionId, case when o.logisticsTransactionId is null then 'NA' else o.logisticsTransactionId end,o.airwayBillNumber, "// + " os.statusSubGroup, os.statusName, o.retailerId, o.retailerName, i.id, i.brand, i.modelName, i.modelNumber, i.color, l.unitPrice, l.quantity, l.totalPrice, o.invoiceNumber,"// + " l.igstRate, l.cgstRate, l.sgstRate ) From Order o join LineItem l on l.orderId = o.id join Item i on l.itemId = i.id join Category c on i.categoryId = c.id join OrderStatus os on os.status=o.status"// + " join FofoStore fs on fs.id=o.retailerId where 1=1 and o.billingTimestamp between :startDate and :endDate and o.retailerId = :fofoId "),//})public class FofoOrder implements Serializable {private static final long serialVersionUID = 1L;@Id@Column(name = "id")@GeneratedValue(strategy = GenerationType.IDENTITY)private int id;@Column(name = "fofo_id")private int fofoId;@Column(name = "customer_id")private int customerId;@Column(name = "customer_address_id")private int customerAddressId;@Column(name = "customer_gst_number")private String customerGstNumber;@Column(name = "total_amount")private float totalAmount;@Column(name = "invoice_number", length = 50)private String invoiceNumber;@Column(name = "cashback")private float cashback;@Convert(converter = LocalDateTimeAttributeConverter.class)@Column(name = "create_timestamp")private LocalDateTime createTimestamp = LocalDateTime.now();@Convert(converter = LocalDateTimeAttributeConverter.class)@Column(name = "cancelled_timestamp")private LocalDateTime cancelledTimestamp;// @Convert(converter = LocalDateTimeAttributeConverter.class)// @Column(name = "billing_timestamp")@Transientprivate LocalDateTime billingTimestamp;@Transientprivate FofoOrderItem orderItem;@Transientprivate LocalDate dateOfBirth;public LocalDate getDateOfBirth() {return dateOfBirth;}public void setDateOfBirth(LocalDate dateOfBirth) {this.dateOfBirth = dateOfBirth;}public FofoOrderItem getOrderItem() {return orderItem;}public void setOrderItem(FofoOrderItem orderItem) {this.orderItem = orderItem;}public LocalDateTime getCancelledTimestamp() {return cancelledTimestamp;}public void setCancelledTimestamp(LocalDateTime cancelledTimestamp) {this.cancelledTimestamp = cancelledTimestamp;}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 getCustomerId() {return customerId;}public void setCustomerId(int customerId) {this.customerId = customerId;}public int getCustomerAddressId() {return customerAddressId;}public void setCustomerAddressId(int customerAddressId) {this.customerAddressId = customerAddressId;}public String getCustomerGstNumber() {return customerGstNumber;}public void setCustomerGstNumber(String customerGstNumber) {this.customerGstNumber = customerGstNumber;}public float getTotalAmount() {return totalAmount;}public void setTotalAmount(float totalAmount) {this.totalAmount = totalAmount;}public String getInvoiceNumber() {return invoiceNumber;}public void setInvoiceNumber(String invoiceNumber) {this.invoiceNumber = invoiceNumber;}public float getCashback() {return cashback;}public void setCashback(float cashback) {this.cashback = cashback;}public LocalDateTime getCreateTimestamp() {return createTimestamp;}public void setCreateTimestamp(LocalDateTime createTimestamp) {this.createTimestamp = createTimestamp;}public String getFormattedDate() {DateTimeFormatter formatter = DateTimeFormatter.ofPattern("dd-MM-yyyy HH:mm");return this.createTimestamp.format(formatter);}@Overridepublic int hashCode() {final int prime = 31;int result = 1;result = prime * result + ((billingTimestamp == null) ? 0 : billingTimestamp.hashCode());result = prime * result + ((cancelledTimestamp == null) ? 0 : cancelledTimestamp.hashCode());result = prime * result + Float.floatToIntBits(cashback);result = prime * result + ((createTimestamp == null) ? 0 : createTimestamp.hashCode());result = prime * result + customerAddressId;result = prime * result + ((customerGstNumber == null) ? 0 : customerGstNumber.hashCode());result = prime * result + customerId;result = prime * result + ((dateOfBirth == null) ? 0 : dateOfBirth.hashCode());result = prime * result + fofoId;result = prime * result + id;result = prime * result + ((invoiceNumber == null) ? 0 : invoiceNumber.hashCode());result = prime * result + ((orderItem == null) ? 0 : orderItem.hashCode());result = prime * result + Float.floatToIntBits(totalAmount);return result;}@Overridepublic boolean equals(Object obj) {if (this == obj)return true;if (obj == null)return false;if (getClass() != obj.getClass())return false;FofoOrder other = (FofoOrder) obj;if (billingTimestamp == null) {if (other.billingTimestamp != null)return false;} else if (!billingTimestamp.equals(other.billingTimestamp))return false;if (cancelledTimestamp == null) {if (other.cancelledTimestamp != null)return false;} else if (!cancelledTimestamp.equals(other.cancelledTimestamp))return false;if (Float.floatToIntBits(cashback) != Float.floatToIntBits(other.cashback))return false;if (createTimestamp == null) {if (other.createTimestamp != null)return false;} else if (!createTimestamp.equals(other.createTimestamp))return false;if (customerAddressId != other.customerAddressId)return false;if (customerGstNumber == null) {if (other.customerGstNumber != null)return false;} else if (!customerGstNumber.equals(other.customerGstNumber))return false;if (customerId != other.customerId)return false;if (dateOfBirth == null) {if (other.dateOfBirth != null)return false;} else if (!dateOfBirth.equals(other.dateOfBirth))return false;if (fofoId != other.fofoId)return false;if (id != other.id)return false;if (invoiceNumber == null) {if (other.invoiceNumber != null)return false;} else if (!invoiceNumber.equals(other.invoiceNumber))return false;if (orderItem == null) {if (other.orderItem != null)return false;} else if (!orderItem.equals(other.orderItem))return false;if (Float.floatToIntBits(totalAmount) != Float.floatToIntBits(other.totalAmount))return false;return true;}@Overridepublic String toString() {return "FofoOrder [id=" + id + ", fofoId=" + fofoId + ", customerId=" + customerId + ", customerAddressId="+ customerAddressId + ", customerGstNumber=" + customerGstNumber + ", totalAmount=" + totalAmount+ ", invoiceNumber=" + invoiceNumber + ", cashback=" + cashback + ", createTimestamp=" + createTimestamp+ ", cancelledTimestamp=" + cancelledTimestamp + ", billingTimestamp=" + billingTimestamp+ ", orderItem=" + orderItem + ", dateOfBirth=" + dateOfBirth + "]";}}