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.selectCount", query = "select count(sr) from ScanRecord sr"),

	@NamedQuery(name = "ScanRecord.selectByFofoId", query = "select sr from ScanRecord sr where sr.fofoId = :fofoId"),

	@NamedQuery(

			name = "ScanRecord.selectOpeningByFofoId",

			query = "select "

					+"new com.spice.profitmandi.dao.model.ItemLedgerRow("

					+ "ii.itemId, "

					+"sum("

						+ "case "

							+ "when (sr.type in('SALE')) then "

								+ "-sr.quantity "

							+ "when (sr.type in('PURCHASE')) then "

							+ "sr.quantity end), "

					+ "sum("

						+ "case "

							+ "when (sr.type in ('SALE')) 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')) 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')) then "

								+ "-sr.quantity "

							+ "when (sr.type in('PURCHASE')) then "

							+ "sr.quantity end), "

					+ "sum("

						+ "case "

							+ "when (sr.type in ('SALE')) 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')) 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 + "]";

	}

}