Subversion Repositories SmartDukaan

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

import java.time.LocalDateTime;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Convert;
import javax.persistence.Entity;
import javax.persistence.EnumType;
import javax.persistence.Enumerated;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.OneToOne;
import javax.persistence.Table;

import com.spice.profitmandi.dao.convertor.LocalDateTimeAttributeConverter;
import com.spice.profitmandi.dao.enumuration.fofo.ScanType;

@Entity
@Table(name="fofo.scan_record", schema = "fofo")
@NamedQueries({
        @NamedQuery(
                        name = "ScanRecord.selectOpeningByFofoId",
                        query = "select "
                                        +"new com.spice.profitmandi.dao.model.ItemLedgerRow("
                                        + "ii.itemId, "
                                        +"sum("
                                                + "case "
                                                        + "when (sr.type in('SALE', 'PURCHASE_RET')) then "
                                                                + "-sr.quantity "
                                                        + "when (sr.type in('PURCHASE')) then "
                                                        + "sr.quantity end), "
                                        + "sum("
                                                + "case "
                                                        + "when (sr.type in ('SALE', 'PURCHASE_RET')) then "
                                                                + "(-sr.quantity * (ii.unitPrice - ii.priceDropAmount)) "
                                                        + "when (sr.type in('PURCHASE')) then "
                                                                + "(sr.quantity * (ii.unitPrice - ii.priceDropAmount)) end) "
                                                        + "/ "
                                        +"sum("
                                        + "case "
                                                + "when (sr.type in('SALE', 'PURCHASE_RET')) then "
                                                        + "-sr.quantity "
                                                + "when (sr.type in('PURCHASE')) then "
                                                + "sr.quantity end)"
                                        + ") "
                                        //+ "ii.unitPrice "
                                        + "from ScanRecord sr join InventoryItem ii on sr.inventoryItemId = ii.id "
                                        + "where sr.createTimestamp < :createTimestamp and sr.fofoId = :fofoId "
                                        + "group by ii.itemId"),
        @NamedQuery(
                        name = "ScanRecord.selectClosingByFofoId",
                        query = "select "
                                        +"new com.spice.profitmandi.dao.model.ItemLedgerRow("
                                        + "ii.itemId, "
                                        +"sum("
                                                + "case "
                                                        + "when (sr.type in('SALE','PURCHASE_RET')) then "
                                                                + "-sr.quantity "
                                                        + "when (sr.type in('PURCHASE')) then "
                                                        + "sr.quantity end), "
                                        + "sum("
                                                + "case "
                                                        + "when (sr.type in ('SALE', 'PURCHASE_RET')) then "
                                                                + "(-sr.quantity * (ii.unitPrice - ii.priceDropAmount)) "
                                                        + "when (sr.type in('PURCHASE')) then "
                                                                + "(sr.quantity * (ii.unitPrice - ii.priceDropAmount)) end) "
                                                        + "/ "
                                        +"sum("
                                        + "case "
                                                + "when (sr.type in('SALE', 'PURCHASE_RET')) then "
                                                        + "-sr.quantity "
                                                + "when (sr.type in('PURCHASE')) then "
                                                + "sr.quantity end)"
                                        + ") "
                                        //+ "ii.unitPrice "
                                        + "from ScanRecord sr join InventoryItem ii on sr.inventoryItemId = ii.id "
                                        + "where sr.createTimestamp > :createTimestamp and sr.fofoId = :fofoId "
                                        + "group by ii.itemId"),
        @NamedQuery(
                        name = "ScanRecord.selectPurchaseByFofoId",
                        query = "select new com.spice.profitmandi.dao.model.ItemLedgerRow(ii.itemId, sum(sr.quantity), avg(ii.unitPrice - ii.priceDropAmount)) from ScanRecord sr join InventoryItem ii on ii.id = sr.inventoryItemId where sr.type='PURCHASE' and sr.createTimestamp >= :startDateTime and sr.createTimestamp <= :endDateTime and sr.fofoId = :fofoId group by ii.itemId"),
        
        @NamedQuery(
                        name = "ScanRecord.selectSaleByFofoId",
                        query = "select new com.spice.profitmandi.dao.model.ItemLedgerRow(ii.itemId, sum(sr.quantity), avg(ii.unitPrice - ii.priceDropAmount)) from ScanRecord sr join InventoryItem ii on ii.id = sr.inventoryItemId where sr.type='SALE' and sr.createTimestamp >= :startDateTime and sr.createTimestamp <= :endDateTime and sr.fofoId = :fofoId group by ii.itemId")
})
public class ScanRecord {
        
        @Id
        @Column(name="id", unique=true, updatable=false)
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        private int id;
        
        @Column(name = "fofo_id")
        private int fofoId;
        
        @Column(name = "inventory_item_id")
        private int inventoryItemId;
        
        @Column(name = "quantity")
        private int quantity;
        
        @Column(name = "type")
        @Enumerated(EnumType.STRING)
        private ScanType type;
        
        @Convert(converter = LocalDateTimeAttributeConverter.class)
        @Column(name = "create_timestamp")
        private LocalDateTime createTimestamp = LocalDateTime.now();
        
        @OneToOne(cascade=CascadeType.ALL,fetch=FetchType.LAZY)
        @JoinColumn(name="inventory_item_id",insertable=false,updatable=false,nullable=false, referencedColumnName = "id")
        private InventoryItem inventoryItem;

        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 getInventoryItemId() {
                return inventoryItemId;
        }
        public void setInventoryItemId(int inventoryItemId) {
                this.inventoryItemId = inventoryItemId;
        }
        
        public int getQuantity() {
                return quantity;
        }
        public void setQuantity(int quantity) {
                this.quantity = quantity;
        }

        public ScanType getType() {
                return type;
        }

        public void setType(ScanType type) {
                this.type = type;
        }

        public LocalDateTime getCreateTimestamp() {
                return createTimestamp;
        }
        public void setCreateTimestamp(LocalDateTime createTimestamp) {
                this.createTimestamp = createTimestamp;
        }
        
        public InventoryItem getInventoryItem() {
                return inventoryItem;
        }
        
        public void setInventoryItem(InventoryItem inventoryItem) {
                this.inventoryItem = inventoryItem;
        }

        @Override
        public int hashCode() {
                final int prime = 31;
                int result = 1;
                result = prime * result + id;
                return result;
        }

        @Override
        public boolean equals(Object obj) {
                if (this == obj)
                        return true;
                if (obj == null)
                        return false;
                if (getClass() != obj.getClass())
                        return false;
                ScanRecord other = (ScanRecord) obj;
                if (id != other.id)
                        return false;
                return true;
        }

        @Override
        public String toString() {
                return "ScanRecord [id=" + id + ", fofoId=" + fofoId + ", inventoryItemId=" + inventoryItemId + ", quantity="
                                + quantity + ", type=" + type + ", createTimestamp=" + createTimestamp + ", inventoryItem="
                                + inventoryItem + "]";
        }
        
}