Subversion Repositories SmartDukaan

Rev

Rev 29927 | Rev 30243 | Go to most recent revision | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 29927 Rev 30050
Line 1... Line 1...
1 
package com.spice.profitmandi.dao.entity.fofo;
 1 
package com.spice.profitmandi.dao.entity.fofo;
2 
 
 2 
 
3 
import com.spice.profitmandi.dao.enumuration.catalog.SchemeType;
 - 
 
4 
import com.spice.profitmandi.dao.enumuration.transaction.SchemePayoutStatus;
 - 
 
5 
 
 - 
 
6 
import javax.persistence.*;
 - 
 
7 
import java.io.Serializable;
 3 
import java.io.Serializable;
8 
import java.time.LocalDateTime;
 4 
import java.time.LocalDateTime;
9 
 
 5 
 
- 
 
 6 
import javax.persistence.Column;
- 
 
 7 
import javax.persistence.Entity;
- 
 
 8 
import javax.persistence.EnumType;
- 
 
 9 
import javax.persistence.Enumerated;
- 
 
 10 
import javax.persistence.GeneratedValue;
- 
 
 11 
import javax.persistence.GenerationType;
- 
 
 12 
import javax.persistence.Id;
- 
 
 13 
import javax.persistence.NamedQueries;
- 
 
 14 
import javax.persistence.NamedQuery;
- 
 
 15 
import javax.persistence.Table;
- 
 
 16 
import javax.persistence.Transient;
- 
 
 17 
 
- 
 
 18 
import com.spice.profitmandi.dao.enumuration.catalog.SchemeType;
- 
 
 19 
import com.spice.profitmandi.dao.enumuration.transaction.SchemePayoutStatus;
- 
 
 20 
 
10 
@Entity
 21 
@Entity
11 
@Table(name = "fofo.scheme_in_out", schema = "fofo")
 22 
@Table(name = "fofo.scheme_in_out", schema = "fofo")
12 
 
 23 
 
13 
@NamedQueries({
 24 
@NamedQueries({
14 
 
 - 
 
15 
		@NamedQuery(name = "SchemeInOut.selectPendingActivationGroupByBrandYearMonth", query = "select new com.spice.profitmandi.dao.model.ActivationYearMonthModel(i.brand, "
 25 
	@NamedQuery(name = "SchemeInOut.selectPendingActivationGroupByBrandYearMonth", query = "select new com.spice.profitmandi.dao.model.ActivationYearMonthModel(i.brand, "
16 
				+ "   DATE_FORMAT(sio.createTimestamp, '%m-%Y'),sum(cast(sio.amount As integer )) ) from Scheme sc join  SchemeInOut sio  on"
 26 
			+ "   DATE_FORMAT(sio.createTimestamp, '%m-%Y'),sum(cast(sio.amount As integer )) ) from Scheme sc join  SchemeInOut sio  on"
17 
				+ " sc.id = sio.schemeId join InventoryItem ii  on ii.id = sio.inventoryItemId"
 27 
			+ " sc.id = sio.schemeId join InventoryItem ii  on ii.id = sio.inventoryItemId"
18 
				+ " join Item i on i.id = ii.itemId where sc.type = 'ACTIVATION' and sio.status ='PENDING' and sio.createTimestamp >= :startDate and ii.fofoId = :fofoId  "
 28 
			+ " join Item i on i.id = ii.itemId where sc.type = 'ACTIVATION' and sio.status ='PENDING' and sio.createTimestamp >= :startDate and ii.fofoId = :fofoId  "
19 
				+ "  group by i.brand, DATE_FORMAT(sio.createTimestamp, '%m-%Y')"),
 29 
			+ "  group by i.brand, DATE_FORMAT(sio.createTimestamp, '%m-%Y')"),
20 
 
 30 
 
21 
		@NamedQuery(name = "SchemeInOut.selectByYearMonthActivationGroupByBrand", query = "select new com.spice.profitmandi.dao.model.ActivationBrandModel(i.brand, "
 31 
	@NamedQuery(name = "SchemeInOut.selectByYearMonthActivationGroupByBrand", query = "select new com.spice.profitmandi.dao.model.ActivationBrandModel(i.brand, "
22 
				+ "  sum(cast(sio.amount As integer )) ) from Scheme sc join  SchemeInOut sio  on"
 32 
			+ "  sum(cast(sio.amount As integer )) ) from Scheme sc join  SchemeInOut sio  on"
23 
				+ " sc.id = sio.schemeId join InventoryItem ii  on ii.id = sio.inventoryItemId"
 33 
			+ " sc.id = sio.schemeId join InventoryItem ii  on ii.id = sio.inventoryItemId"
24 
				+ " join Item i on i.id = ii.itemId where sc.type = 'ACTIVATION' and sio.status ='PENDING' and sio.createTimestamp <= :endDate and ii.fofoId = :fofoId  "
 34 
			+ " join Item i on i.id = ii.itemId where sc.type = 'ACTIVATION' and sio.status ='PENDING' and sio.createTimestamp <= :endDate and ii.fofoId = :fofoId  "
25 
				+ "  group by i.brand"),
 35 
			+ "  group by i.brand"),
26 
 
 36 
 
27 
		@NamedQuery(name = "SchemeInOut.selectBrandPendingActivationItemDetails", query = "select new com.spice.profitmandi.dao.model.ActivationItemDetailModel(i.brand, "
 37 
	@NamedQuery(name = "SchemeInOut.selectBrandPendingActivationItemDetails", query = "select new com.spice.profitmandi.dao.model.ActivationItemDetailModel(i.brand, "
28 
				+ "  i.modelName,i.modelNumber,cast(sio.amount As integer ),ii.serialNumber, ii.id) from Scheme sc join SchemeInOut sio  on"
 38 
			+ "  i.modelName,i.modelNumber,cast(sio.amount As integer ),ii.serialNumber, ii.id) from Scheme sc join SchemeInOut sio  on"
29 
				+ "	 sc.id = sio.schemeId join InventoryItem ii  on ii.id = sio.inventoryItemId"
 39 
			+ "	 sc.id = sio.schemeId join InventoryItem ii  on ii.id = sio.inventoryItemId"
30 
				+ "	 join Item i on i.id = ii.itemId where sc.type = 'ACTIVATION' and sio.status ='PENDING' and "
 40 
			+ "	 join Item i on i.id = ii.itemId where sc.type = 'ACTIVATION' and sio.status ='PENDING' and "
31 
				+ "  ii.fofoId = :fofoId and i.brand = :brand and  DATE_FORMAT(sio.createTimestamp, '%m-%Y') = :yearMonthValue"),
 41 
			+ "  ii.fofoId = :fofoId and i.brand = :brand and  DATE_FORMAT(sio.createTimestamp, '%m-%Y') = :yearMonthValue"),
32 
 
 42 
 
33 
		@NamedQuery(name = "SchemeInOut.selectBrandPendingActivationItemDetailByYearMonth", query = "select new com.spice.profitmandi.dao.model.ActivationItemDetailModel(i.brand, "
 43 
	@NamedQuery(name = "SchemeInOut.selectBrandPendingActivationItemDetailByYearMonth", query = "select new com.spice.profitmandi.dao.model.ActivationItemDetailModel(i.brand, "
34 
				+ "  i.modelName,i.modelNumber,cast(sio.amount As integer ),ii.serialNumber, ii.id) from Scheme sc join SchemeInOut sio  on"
 44 
			+ "  i.modelName,i.modelNumber,cast(sio.amount As integer ),ii.serialNumber, ii.id) from Scheme sc join SchemeInOut sio  on"
35 
				+ "	 sc.id = sio.schemeId join InventoryItem ii  on ii.id = sio.inventoryItemId"
 45 
			+ "	 sc.id = sio.schemeId join InventoryItem ii  on ii.id = sio.inventoryItemId"
36 
				+ "	 join Item i on i.id = ii.itemId where sc.type = 'ACTIVATION' and sio.status ='PENDING' and "
 46 
			+ "	 join Item i on i.id = ii.itemId where sc.type = 'ACTIVATION' and sio.status ='PENDING' and "
37 
				+ "  ii.fofoId = :fofoId and i.brand = :brand and sio.createTimestamp <= :endDate"),
 47 
			+ "  ii.fofoId = :fofoId and i.brand = :brand and sio.createTimestamp <= :endDate"),
- 
 
 48 
 
- 
 
 49
- 
 
 50 
		@NamedQuery(name = "SchemeInOut.selectAllPurchaseInventoryByFofoId", query = "select new com.spice.profitmandi.dao.model.AllPurchaseInventoryModel(sum(cast(sio.amount As int ))) from InventoryItem ii join SchemeInOut sio on"
- 
 
 51 
				+ "  sio.inventoryItemId=ii.id join Scheme sc on sc.id=sio.schemeId  where sc.type= 'IN' and ii.fofoId = :fofoId and ii.createTimestamp >= :startDate and "
- 
 
 52 
				+ "  ii.createTimestamp <= :endDate and ii.fofoId = :fofoId group by ii.fofoId"),
- 
 
 53
- 
 
 54 
		@NamedQuery(name = "SchemeInOut.selectAllCreditedSaleInventoryByFofoId", query = "select new com.spice.profitmandi.dao.model.AllPurchaseInventoryModel(sum(cast(sio.amount As int ))) from "
- 
 
 55 
				+ "  ScanRecord sr join FofoOrder fo on fo.id=sr.orderId join SchemeInOut sio on  sio.inventoryItemId=sr.inventoryItemId join Scheme sc on sc.id=sio.schemeId   where   sc.type != 'IN' and fo.cancelledTimestamp is null and "
- 
 
 56 
				+ "  sio.status='CREDITED' and sr.type='SALE' and sr.createTimestamp >= :startDate and sr.createTimestamp <= :endDate and sr.fofoId = :fofoId group by sr.fofoId"),
- 
 
 57
- 
 
 58 
		@NamedQuery(name = "SchemeInOut.selectFrontIncomeByFofoId", query = "select new com.spice.profitmandi.dao.model.AllPurchaseInventoryModel(sum(cast(foi.sellingPrice As int )-cast(foi.dp As int ))) "
- 
 
 59 
				+ " from InventoryItem ii join ScanRecord sr on sr.inventoryItemId=ii.id join FofoOrder fo on fo.id=sr.orderId join FofoOrderItem foi on foi.orderId=fo.id where "
- 
 
 60 
				+ " fo.cancelledTimestamp is null and sr.type='SALE' and sr.createTimestamp >=:startDate and sr.createTimestamp <= :endDate and sr.fofoId = :fofoId group by sr.fofoId"),
- 
 
 61
- 
 
 62 
		@NamedQuery(name = "SchemeInOut.selectAllPendingSaleInventoryByFofoId", query = "select new com.spice.profitmandi.dao.model.AllPurchaseInventoryModel(sum(cast(sio.amount As int ))) from "
- 
 
 63 
				+ "  ScanRecord sr join FofoOrder fo on fo.id=sr.orderId join SchemeInOut sio on  sio.inventoryItemId=sr.inventoryItemId  where  fo.cancelledTimestamp is null and "
- 
 
 64 
				+ "  sio.status='PENDING' and sr.type='SALE' and sr.createTimestamp >= :startDate and sr.createTimestamp <= :endDate and sr.fofoId = :fofoId group by sr.fofoId"),
- 
 
 65
- 
 
 66 
        @NamedQuery(name = "SchemeInOut.selectLastMonthCreditedIncomeByFofoId", query = "select new com.spice.profitmandi.dao.model.LastMonthCreditedIncomeModel(foi.brand,sum(cast(foi.quantity As int)), sum(cast(sio.amount As int))) from ScanRecord sr "
- 
 
 67 
		        + " join FofoOrder fo on fo.id=sr.orderId join SchemeInOut sio on sio.inventoryItemId=sr.inventoryItemId join FofoOrderItem foi on foi.orderId=fo.id join Scheme sc on sc.id=sio.schemeId   where   sc.type != 'IN' and fo.cancelledTimestamp is null and "
- 
 
 68 
		       + "sio.status='CREDITED' and sr.type='SALE' and sr.createTimestamp >= :startDate and sr.createTimestamp <= :endDate and sr.fofoId = :fofoId group by foi.brand"),
- 
 
 69
- 
 
 70 
        @NamedQuery(name = "SchemeInOut.selectFrontIncomeByBrand", query = "select new com.spice.profitmandi.dao.model.LastMonthCreditedIncomeModel(foi.brand,sum(cast(foi.quantity As int)), sum(cast(foi.sellingPrice As int )-cast(foi.dp As int ))) "
- 
 
 71 
				+ " from InventoryItem ii join ScanRecord sr on sr.inventoryItemId=ii.id join FofoOrder fo on fo.id=sr.orderId join FofoOrderItem foi on foi.orderId=fo.id where "
- 
 
 72 
				+ " fo.cancelledTimestamp is null and sr.type='SALE' and sr.createTimestamp >=:startDate and sr.createTimestamp <= :endDate and sr.fofoId = :fofoId group by foi.brand"),
- 
 
 73
- 
 
 74 
        @NamedQuery(name = "SchemeInOut.selectLastMonthPendingIncomeByFofoId", query = "select new com.spice.profitmandi.dao.model.LastMonthCreditedIncomeModel(foi.brand,sum(cast(foi.quantity As int)),sum(cast(sio.amount As int ))) from "
- 
 
 75 
				+ "  ScanRecord sr join FofoOrder fo on fo.id=sr.orderId join SchemeInOut sio on  sio.inventoryItemId=sr.inventoryItemId  join FofoOrderItem foi on  foi.orderId=fo.id where  fo.cancelledTimestamp is null and "
- 
 
 76 
				+ "  sio.status='PENDING' and sr.type='SALE' and sr.createTimestamp >= :startDate and sr.createTimestamp <= :endDate and sr.fofoId = :fofoId group by foi.brand"),
- 
 
 77
- 
 
 78 
		@NamedQuery(name = "SchemeInOut.selectLastMonthPurchaseInMarginByFofoId", query = "select new com.spice.profitmandi.dao.model.LastMonthCreditedIncomeModel(i.brand, count(distinct ii.id ),sum(cast(sio.amount As int ))) from InventoryItem ii join SchemeInOut sio on"
- 
 
 79 
				+ "  sio.inventoryItemId=ii.id join Scheme sc on sc.id=sio.schemeId join Item i on i.id=ii.itemId where sio.status='CREDITED' and sc.type= 'IN' and ii.fofoId = :fofoId and ii.createTimestamp >= :startDate and "
- 
 
 80 
				+ "  ii.createTimestamp <= :endDate group by i.brand"),
- 
 
 81
- 
 
 82 
		@NamedQuery(name = "SchemeInOut.selectLastMonthBrandWiseIncome", query = "select new com.spice.profitmandi.dao.model.LastMonthBrandWiseIncomeModel(i.brand,sum(cast(foi.quantity As int)),sum(cast(sio.amount As float)),i.catalogItemId,i.modelName,i.modelNumber) from ScanRecord sr "
- 
 
 83 
		        + " join FofoOrder fo on fo.id=sr.orderId join SchemeInOut sio on sio.inventoryItemId=sr.inventoryItemId join FofoOrderItem foi on foi.orderId=fo.id  join FofoLineItem  fli on fli.fofoOrderItemId=foi.id  join  Item i on i.id=foi.itemId join Scheme sc on sc.id=sio.schemeId   where   sc.type != 'IN' and fo.cancelledTimestamp is null and "
- 
 
 84 
		       + "sio.status='CREDITED' and sr.type='SALE' and sr.createTimestamp >= :startDate and sr.createTimestamp <= :endDate and sr.fofoId = :fofoId  and foi.brand = :brand group by i.catalogItemId,i.modelName,i.modelNumber,i.brand"),
- 
 
 85 
 
- 
 
 86 
		@NamedQuery(name = "SchemeInOut.selectFrontIncomeBrandWise", query = "select new com.spice.profitmandi.dao.model.LastMonthBrandWiseIncomeModel(i.brand,sum(cast(foi.quantity As int)),sum(cast(foi.sellingPrice -foi.dp As float)),i.catalogItemId,i.modelName,i.modelNumber) "
- 
 
 87 
					+ " from InventoryItem ii join ScanRecord sr on sr.inventoryItemId=ii.id join FofoOrder fo on fo.id=sr.orderId join FofoOrderItem foi on foi.orderId=fo.id join Item i on foi.itemId=i.id where "
- 
 
 88 
					+ " fo.cancelledTimestamp is null and sr.type='SALE' and sr.createTimestamp >=:startDate and sr.createTimestamp <= :endDate and sr.fofoId = :fofoId and foi.brand = :brand group by i.catalogItemId,i.modelName,i.modelNumber,i.brand"),
- 
 
 89 
 
- 
 
 90 
		@NamedQuery(name = "SchemeInOut.selectLastMonthPurchaseBrandWiseIncome", query = "select new com.spice.profitmandi.dao.model.LastMonthBrandWiseIncomeModel( i.brand ,count(distinct ii.id),sum(cast(sio.amount As float)) ,i.catalogItemId,i.modelName,i.modelNumber) from"
- 
 
 91 
				+ "  InventoryItem ii join SchemeInOut sio on sio.inventoryItemId=ii.id join Scheme sc on sc.id=sio.schemeId join Item i on i.id=ii.itemId where sio.status='CREDITED' and "
- 
 
 92 
				+ "  sc.type= 'IN' and ii.fofoId = :fofoId and i.brand= :brand and ii.createTimestamp >= :startDate and ii.createTimestamp <= :endDate group by i.catalogItemId,i.modelName,i.modelNumber,i.brand "),
- 
 
 93 
 
- 
 
 94
- 
 
 95 
		@NamedQuery(name = "SchemeInOut.selectLastMonthCreditedImei", query = "select new com.spice.profitmandi.dao.model.LastMonthImeiModel(fli.serialNumber,sio.amount,sc.description,sr.createTimestamp)from ScanRecord sr "
- 
 
 96 
				+ "      join FofoOrder fo on fo.id=sr.orderId join SchemeInOut sio on sio.inventoryItemId=sr.inventoryItemId join FofoOrderItem foi on foi.orderId=fo.id  join FofoLineItem  fli on fli.fofoOrderItemId=foi.id "
- 
 
 97 
				+ "      join  Item i on i.id=foi.itemId join Scheme sc on sc.id=sio.schemeId   where   sc.type != 'IN' and fo.cancelledTimestamp is null and sio.status='CREDITED' and sr.type='SALE' and sr.createTimestamp >= :startDate and sr.createTimestamp <= :endDate and "
- 
 
 98 
				+ "      i.catalogItemId= :catalogItemId and sr.fofoId = :fofoId"),
- 
 
 99 
 
- 
 
 100 
		@NamedQuery(name = "SchemeInOut.selectLastMonthPurchaseInImei", query = "select new com.spice.profitmandi.dao.model.LastMonthImeiModel(ii.serialNumber,sio.amount,sc.description,ii.createTimestamp) from InventoryItem ii "
- 
 
 101 
				+ "     join SchemeInOut sio on sio.inventoryItemId=ii.id join Scheme sc on sc.id=sio.schemeId join Item i on i.id=ii.itemId where sio.status='CREDITED' and sc.type= 'IN' and ii.fofoId = :fofoId and "
- 
 
 102 
				+ "     i.catalogItemId= :catalogItemId and ii.createTimestamp >= :startDate and ii.createTimestamp <= :endDate"),
- 
 
 103
- 
 
 104
- 
 
 105 
		@NamedQuery(name = "SchemeInOut.selectLastMonthFrontEndImei", query = "select new com.spice.profitmandi.dao.model.LastMonthFrontEndImeiModel( ii.serialNumber,foi.sellingPrice -foi.dp) from InventoryItem ii "
- 
 
 106 
				+ "      join ScanRecord sr on sr.inventoryItemId=ii.id join FofoOrder fo on fo.id=sr.orderId join FofoOrderItem foi on foi.orderId=fo.id join Item i on foi.itemId=i.id where "
- 
 
 107 
				+ "     fo.cancelledTimestamp is null and sr.type='SALE' and i.catalogItemId= :catalogItemId and sr.createTimestamp >=:startDate and sr.createTimestamp <= :endDate and sr.fofoId = :fofoId "
- 
 
 108 
				+ "     group by ii.id"),
- 
 
 109
- 
 
 110 
		@NamedQuery(name = "SchemeInOut.selectLastMonthCreditedByImei", query = "select new com.spice.profitmandi.dao.model.LastMonthImeiModel(fli.serialNumber,sio.amount,sc.description,sr.createTimestamp)from ScanRecord sr "
- 
 
 111 
				+ "      join FofoOrder fo on fo.id=sr.orderId join SchemeInOut sio on sio.inventoryItemId=sr.inventoryItemId join FofoOrderItem foi on foi.orderId=fo.id  join FofoLineItem  fli on fli.fofoOrderItemId=foi.id "
- 
 
 112 
				+ "      join  Item i on i.id=foi.itemId join Scheme sc on sc.id=sio.schemeId   where   sc.type != 'IN' and fo.cancelledTimestamp is null and sio.status='CREDITED' and sr.type='SALE' and "
- 
 
 113 
				+ "      fli.serialNumber in :imeis and sr.fofoId = :fofoId"),
- 
 
 114 
 
- 
 
 115 
		@NamedQuery(name = "SchemeInOut.selectLastMonthPurchaseInByImei", query = "select new com.spice.profitmandi.dao.model.LastMonthImeiModel(ii.serialNumber,sio.amount,sc.description,ii.createTimestamp) from InventoryItem ii "
- 
 
 116 
				+ "     join SchemeInOut sio on sio.inventoryItemId=ii.id join Scheme sc on sc.id=sio.schemeId join Item i on i.id=ii.itemId where sio.status='CREDITED' and sc.type= 'IN' and ii.fofoId = :fofoId and "
- 
 
 117 
				+ "     ii.serialNumber in :imeis"),
- 
 
 118
- 
 
 119
- 
 
 120 
		@NamedQuery(name = "SchemeInOut.selectLastMonthFrontEndByImei", query = "select new com.spice.profitmandi.dao.model.LastMonthFrontEndImeiModel( ii.serialNumber,foi.sellingPrice -foi.dp) from InventoryItem ii "
- 
 
 121 
				+ "      join ScanRecord sr on sr.inventoryItemId=ii.id join FofoOrder fo on fo.id=sr.orderId join FofoOrderItem foi on foi.orderId=fo.id join Item i on foi.itemId=i.id where "
- 
 
 122 
				+ "     fo.cancelledTimestamp is null and sr.type='SALE' and ii.serialNumber in (:imeis) and sr.fofoId = :fofoId  group by ii.id"),
- 
 
 123
- 
 
 124 
		@NamedQuery(name = "SchemeInOut.selectLastMonthCategoryUpgradeMarginByBrand", query = "select new com.spice.profitmandi.dao.model.LastMonthCategoryUpgradeMargin(foi.brand, sum(case when sio.status='REJECTED' then -sio.amount else sio.amount end) ) from ScanRecord sr "
- 
 
 125 
		        + " join FofoOrder fo on fo.id=sr.orderId join SchemeInOut sio on sio.inventoryItemId=sr.inventoryItemId join FofoOrderItem foi on foi.orderId=fo.id join Scheme sc on sc.id=sio.schemeId   where   sc.type = 'CATEGORY' and fo.cancelledTimestamp is null "
- 
 
 126 
		       +  "  and sr.type='SALE' and sr.createTimestamp >= :startDate and sr.createTimestamp <= :endDate and sr.fofoId = :fofoId group by foi.brand"),
- 
 
 127
- 
 
 128 
		@NamedQuery(name = "SchemeInOut.selectLastMonthCategoryUpgradeMarginByBrandModel", query = "select new com.spice.profitmandi.dao.model.LastMonthCategoryUpgradeMargin(i.modelNumber,sum(case when sio.status='REJECTED' then -sio.amount else sio.amount end)) from ScanRecord sr "
- 
 
 129 
		        + " join FofoOrder fo on fo.id=sr.orderId join SchemeInOut sio on sio.inventoryItemId=sr.inventoryItemId join FofoOrderItem foi on foi.orderId=fo.id  join FofoLineItem  fli on fli.fofoOrderItemId=foi.id  join  Item i on i.id=foi.itemId join Scheme sc on sc.id=sio.schemeId   where   sc.type = 'CATEGORY' and fo.cancelledTimestamp is null and "
- 
 
 130 
		       +  " sr.type='SALE' and sr.createTimestamp >= :startDate and sr.createTimestamp <= :endDate and sr.fofoId = :fofoId  and foi.brand = :brand group by i.catalogItemId,i.modelName,i.modelNumber,i.brand"),
- 
 
 131 
 
- 
 
 132 
		@NamedQuery(name = "SchemeInOut.selectLastMonthCategoryUpgradeMarginByImei", query = "select new com.spice.profitmandi.dao.model.LastMonthCategoryUpgradeMargin(fli.serialNumber,sum(case when sio.status='REJECTED' then -sio.amount else sio.amount end) ) from ScanRecord sr "
- 
 
 133 
				+ "      join FofoOrder fo on fo.id=sr.orderId join SchemeInOut sio on sio.inventoryItemId=sr.inventoryItemId join FofoOrderItem foi on foi.orderId=fo.id  join FofoLineItem  fli on fli.fofoOrderItemId=foi.id "
- 
 
 134 
				+ "      join  Item i on i.id=foi.itemId join Scheme sc on sc.id=sio.schemeId   where sc.type = 'CATEGORY' and fo.cancelledTimestamp is null and sr.type='SALE' and sr.createTimestamp >= :startDate and sr.createTimestamp <= :endDate and "
- 
 
 135 
				+ "      i.catalogItemId= :catalogItemId and sr.fofoId = :fofoId group by fli.serialNumber"),
38 
 
 136 
 
39 
})
 137 
})
40 
public class SchemeInOut implements Serializable {
 138 
public class SchemeInOut implements Serializable {
41 
 
 139 
 
42 
	public SchemeInOut(int schemeId, int inventoryItemId) {
 140 
	public SchemeInOut(int schemeId, int inventoryItemId) {