Subversion Repositories SmartDukaan

Rev

Rev 25270 | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
4500 mandeep.dh 1 
<?xml version="1.0" encoding="UTF-8"?>
2 
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
3 
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
4 
 
5 
<mapper namespace="in.shop2020.warehouse.persistence.ScanMapper">
6 
  <resultMap type="scan" id="scanResult">
7 
    <id property="id" column="id"/>
8 
  </resultMap>
9 
 
10 
  <insert id="insert" parameterType="scan" useGeneratedKeys="true" keyProperty="id">
11 
    INSERT INTO scanNew
7410 amar.kumar 12 
      (inventoryItemId, warehouseId, type, scannedAt, quantity, orderId, transferLotId, remarks)
4500 mandeep.dh 13 
    VALUES
7410 amar.kumar 14 
      (#{inventoryItemId}, #{warehouseId}, #{type}, NOW(), #{quantity}, #{orderId}, #{transferLotId}, #{remarks})
4500 mandeep.dh 15 
  </insert>
16 
 
17 
  <select id="get" parameterType="map" resultType="scan">
18 
    SELECT *
19 
    FROM scanNew
20 
    WHERE inventoryItemId = #{inventoryItemId}
21 
    <if test="scanType != null">
22 
        AND type = #{scanType}
23 
    </if>
24 
    ORDER BY scannedAt
25 
  </select>
5361 mandeep.dh 26 
 
27 
  <select id="getScansFromOrderId" parameterType="java.lang.Long" resultType="scan">
28 
    SELECT *
29 
    FROM scanNew
30 
    WHERE orderId = #{orderId}
31 
    ORDER BY scannedAt
32 
  </select>
5372 mandeep.dh 33 
 
7199 amar.kumar 34 
  <select id="getScansForOrderAndItem" parameterType="map" resultType="scan">
35 
    SELECT  s.id as id, s.inventoryItemId as inventoryItemId, s.warehouseId as warehouseId, s.type as type,
36 
    	s.scannedAt as scannedAt, s.quantity as quantity, s.orderId as orderId
37 
    FROM scanNew s
38 
    JOIN inventoryItem i
39 
    	ON (s.inventoryItemId = i.id)
40 
    WHERE i.itemId = #{itemId}
41 
    AND s.orderId = #{orderId}
42 
    ORDER BY scannedAt
43 
  </select>
44 
 
45 
 
5372 mandeep.dh 46 
  <select id="getPurchaseScans" parameterType="map" resultType="detailedPurchaseScan">
5395 mandeep.dh 47 
    SELECT p.purchaseOrder_id AS purchaseOrderId, 1000 * UNIX_TIMESTAMP(po.createdAt) AS poCreatedAt, s.name AS supplierName,
6494 amar.kumar 48 
        GROUP_CONCAT(DISTINCT(p.invoiceNumber)) AS invoiceNumbers, GROUP_CONCAT(DISTINCT(inv.receivedFrom)) AS receivedBy, i.itemId,
23402 amit.gupta 49 
        brand, modelName, modelNumber, color, unitPrice, sgstRate, cgstRate, igstRate, SUM(initialQuantity) AS quantity, 1000 * UNIX_TIMESTAMP(receivedOn) as purchasedAt,
8580 amar.kumar 50 
        p.id as purchaseId, nlc, po.warehouseId
5372 mandeep.dh 51 
    FROM purchase p
52 
    JOIN purchaseorder po
53 
        ON (po.id = p.purchaseOrder_id)
54 
    JOIN inventoryItem i
55 
        ON (p.id = i.purchaseId)
56 
    JOIN lineitem l
57 
        ON (l.itemId = i.itemId AND l.purchaseOrder_id = po.id)
58 
    JOIN supplier s
59 
        ON (s.id = po.supplierId)
6494 amar.kumar 60 
    JOIN invoice inv
7453 amar.kumar 61 
    	ON (inv.invoiceNumber = p.invoiceNumber AND inv.supplierId = po.supplierId)
5372 mandeep.dh 62 
    WHERE po.createdAt BETWEEN #{startDate} AND #{endDate}
6364 rajveer 63 
    AND lastScanType != 'DOA_REPLACED'
6362 rajveer 64 
    GROUP BY p.purchaseOrder_id, p.id, i.itemId
65 
    ORDER BY p.purchaseOrder_id, p.id, i.itemId
5372 mandeep.dh 66 
  </select>
5496 mandeep.dh 67 
 
7210 amar.kumar 68 
  <select id="getPurchaseScansByGrnDate" parameterType="map" resultType="detailedPurchaseScan">
69 
    SELECT p.purchaseOrder_id AS purchaseOrderId, 1000 * UNIX_TIMESTAMP(po.createdAt) AS poCreatedAt, s.name AS supplierName,
25272 amit.gupta 70 
        p.invoiceNumber AS invoiceNumbers, inv.receivedFrom AS receivedBy, i.itemId,brand, modelName, modelNumber, color, l.invoicePrice as invoicePrice,
23402 amit.gupta 71 
        unitPrice, sgstRate, cgstRate, igstRate, SUM(initialQuantity) AS quantity, 1000 * UNIX_TIMESTAMP(receivedOn) as purchasedAt, p.id as purchaseId,
12800 manish.sha 72 
        nlc, po.warehouseId, po.taxType, 1000 * UNIX_TIMESTAMP(inv.invoiceDate) as invoiceDate, p.purchaseComments
23770 amit.gupta 73 
    FROM purchaseorder po
74 
    JOIN purchase p
7210 amar.kumar 75 
        ON (po.id = p.purchaseOrder_id)
23771 amit.gupta 76 
    JOIN (select * from invoice group by invoiceNumber, supplierId) inv
23770 amit.gupta 77 
    	ON (inv.invoiceNumber = p.invoiceNumber AND inv.supplierId = po.supplierId)
7210 amar.kumar 78 
    JOIN inventoryItem i
79 
        ON (p.id = i.purchaseId)
80 
    JOIN lineitem l
81 
        ON (l.itemId = i.itemId AND l.purchaseOrder_id = po.id)
82 
    JOIN supplier s
83 
        ON (s.id = po.supplierId)
7216 amar.kumar 84 
    WHERE p.receivedOn BETWEEN #{startDate} AND #{endDate}
85 
    AND inv.date  &lt; #{endDate}
12357 manish.sha 86 
    AND inv.date  &gt;= ADDDATE(#{startDate},-1)
7210 amar.kumar 87 
    AND lastScanType != 'DOA_REPLACED'
7224 amar.kumar 88 
    GROUP BY p.purchaseOrder_id, p.id, i.itemId
89 
    ORDER BY p.purchaseOrder_id, p.id, i.itemId
7210 amar.kumar 90 
  </select>
91 
 
5496 mandeep.dh 92 
  <select id="fetchMismatchScansPerInvoiceNumber" parameterType="java.util.Date" resultType="invoiceScan" >
93 
    SELECT i.invoiceNumber, i.numItems, sum(initialQuantity) as scannedQuantity, i.date, s.name as supplierName
94 
    FROM invoice i
95 
    JOIN supplier s
7090 amar.kumar 96 
    	ON (i.supplierId = s.id)
5496 mandeep.dh 97 
    LEFT JOIN purchase p
98 
        ON (p.invoiceNumber = i.invoiceNumber)
7089 amar.kumar 99 
    LEFT JOIN inventoryItem ii
100 
    	ON (ii.purchaseId = p.id)
101 
    JOIN scanNew sn
102 
    	ON (sn.inventoryItemId = ii.id)
5496 mandeep.dh 103 
    WHERE i.date = #{date}
7089 amar.kumar 104 
    AND	sn.scannedAt between #{date} and ADDDATE(#{date},1)
105 
    AND sn.type = 'PURCHASE'
106 
    AND s.id !=1
5496 mandeep.dh 107 
    GROUP BY i.invoiceNumber, i.numItems
108 
    HAVING SUM(initialQuantity) != i.numItems
109 
  </select>
5372 mandeep.dh 110 
 
5711 mandeep.dh 111 
  <select id="getInventoryAge" resultType="inventoryAge">
112 
    SELECT t.itemId, brand, modelName, modelNumber, color,
113 
        SUM((age &lt; 1) * currentQuantity) AS freshCount,
5768 mandeep.dh 114 
        SUM((age &gt;= 1 and age &lt; 2) * currentQuantity) AS oneToTwoCount,
115 
        SUM((age &gt;= 2 and age &lt; 3) * currentQuantity) AS twoToThreeCount,
116 
        SUM((age &gt;= 3 and age &lt; 4) * currentQuantity) AS threeToFourCount,
117 
        SUM((age &gt;= 4) * currentQuantity) as fourPlusCount,
118 
        SUM((age &gt;= 1) * currentQuantity) as onePlusCount,
8626 amar.kumar 119 
        SUM((age &gt;= 13) * currentQuantity) as threeMonthPlusCount,
120 
        SUM((age &gt;= 26) * currentQuantity) as sixMonthPlusCount,
12800 manish.sha 121 
        SUM((age &gt;= 0 and age &lt;= 13) * currentQuantity) AS zeroToThreeMonthCount,
11512 manish.sha 122 
        SUM((age &gt; 13 and age &lt;= 26) * currentQuantity) AS threeToSixMonthCount,
123 
        SUM((age &gt; 26 and age &lt;= 52) * currentQuantity) AS sixToTwelveMonthCount,
11220 manish.sha 124 
        SUM((age &gt; 52) * currentQuantity) AS twelveMonthsPlusCount,
9830 amar.kumar 125 
        SUM((age &gt;= 1) * unitPrice*currentQuantity) as onePlusCost,
5768 mandeep.dh 126 
        SUM(currentQuantity) as zeroPlusCount,
9830 amar.kumar 127 
        SUM(l.unitPrice*currentQuantity) as zeroPlusCost,
5768 mandeep.dh 128 
        l.productGroup as category
5711 mandeep.dh 129 
    FROM  (SELECT ii.id, ii.itemId, ii.currentQuantity, ii.purchaseId,
7838 amar.kumar 130 
                DATEDIFF(now(), MIN(s.scannedAt)) / 7 as age
5711 mandeep.dh 131 
           FROM inventoryItem ii
132 
           JOIN scanNew s
133 
                ON (s.inventoryItemId = ii.id)
8625 amar.kumar 134 
           WHERE (ii.lastScanType IN ('PURCHASE', 'SALE_RET', 'MARKED_GOOD')
135 
           OR ii.currentQuantity>1)
12591 manish.sha 136 
           AND ii.lastScanType != 'MARKED_BAD'
8630 amar.kumar 137 
           AND (transferStatus is NULL or transferStatus != 'IN_TRANSIT')
12357 manish.sha 138 
           AND ii.physicalWarehouseId NOT IN (select id from inventory.warehouse where vendor_id= 40
12543 manish.sha 139 
	and inventoryType = 'GOOD' and warehouseType ='OURS_THIRDPARTY' and source in (3,7,8))
5711 mandeep.dh 140 
           GROUP BY ii.id, ii.itemId, ii.currentQuantity) t
141 
    JOIN purchase p
142 
        ON (p.id = t.purchaseId)
143 
    JOIN lineitem l
144 
        ON (l.purchaseOrder_id = p.purchaseOrder_id AND l.itemId = t.itemId)
145 
    GROUP BY t.itemId
146 
    HAVING SUM(currentQuantity) != 0
147 
  </select>
6322 amar.kumar 148 
 
7089 amar.kumar 149 
  <!--select id="inventoryAgeOnePlus" resultType="detailedInventoryAge">
150 
    SELECT t.itemId, brand, modelName, modelNumber, color, scannedAt, i.warehouseId
151 
        SUM((age &gt;= 1) * currentQuantity) AS onePlusCount,
152 
        SUM((age &gt;= 1) * unitPrice) as onePlusCost,
153 
        l.productGroup as category
154 
    FROM  (SELECT ii.id, ii.itemId, ii.currentQuantity, ii.purchaseId, MIN(s.scannedAt)
155 
                DATEDIFF(now(), MAX(s.scannedAt)) / 7 as age
156 
           FROM inventoryItem ii
157 
           JOIN scanNew s
158 
                ON (s.inventoryItemId = ii.id)
159 
           WHERE ii.lastScanType IN ('PURCHASE', 'SALE_RET')
160 
           GROUP BY ii.id, ii.itemId, ii.currentQuantity) t
161 
    JOIN purchase p
162 
        ON (p.id = t.purchaseId)
163 
    JOIN lineitem l
164 
        ON (l.purchaseOrder_id = p.purchaseOrder_id AND l.itemId = t.itemId)
165 
    GROUP BY t.itemId, i.warehouseId
166 
    HAVING SUM(currentQuantity) != 0
167 
  </select-->
168 
 
169 
 
170 
 
6322 amar.kumar 171 
  <select id = "getScansForItem" parameterType = "map" resultType = "scan">
172 
	  SELECT s.id, s.inventoryItemId, s.warehouseId, s.type, s.scannedAt,
173 
	  s.quantity, s.orderId from scanNew s JOIN inventoryItem i
174 
	  ON
175 
	  s.inventoryItemId = i.id
176 
	  AND
6414 amar.kumar 177 
	  i.itemId = #{itemId}
6322 amar.kumar 178 
	  AND
179 
	  s.scannedAt BETWEEN #{fromDate} AND #{toDate};
180 
  </select>
181 
 
6558 amar.kumar 182 
  <insert id="genericScan" parameterType="scan" useGeneratedKeys="true" keyProperty="id">
6467 amar.kumar 183 
    INSERT INTO scanNew
7410 amar.kumar 184 
     (inventoryItemId, warehouseId, type, scannedAt, quantity, orderId, transferLotId, remarks)
6467 amar.kumar 185 
    VALUES
7410 amar.kumar 186 
      (#{inventoryItemId}, #{warehouseId}, #{type}, NOW(), #{quantity}, #{orderId}, #{transferLotId}, #{remarks})
6467 amar.kumar 187 
  </insert>
188 
 
6558 amar.kumar 189 
  <select id = "getCurrentSerializedInventoryByScans" resultType = "inventoryAvailability">
190 
	SELECT x.itemId, ci.brand, ci.model_name as modelName,
6630 amar.kumar 191 
	ci.model_number as modelNumber, ci.color as color, (x.inx-if(y.outx is null,0,y.outx)) as quantity
6558 amar.kumar 192 
	from
11750 manish.sha 193 
	(select i.itemId, sum(quantity) as inx from scanNew s
6558 amar.kumar 194 
		JOIN inventoryItem i ON (i.id = s.inventoryItemId)
195 
		JOIN catalog.item c ON i.itemId = c.id
10407 amar.kumar 196 
		JOIN inventory.warehouse w ON w.id = s.warehouseId
197 
		where s.type in ('PURCHASE','SALE_RET', 'MARKED_GOOD','WAREHOUSE_TRANSFER_IN') and c.type = 'SERIALIZED'
10402 manish.sha 198 
		and w.billingWarehouseId = #{physicalWarehouseId}
6558 amar.kumar 199 
		group by i.itemId ) as x
6630 amar.kumar 200 
	LEFT JOIN
11750 manish.sha 201 
	(select i.itemId, sum(quantity) as outx from scanNew s
6558 amar.kumar 202 
		JOIN inventoryItem i on i.id = s.inventoryItemId
203 
		JOIN catalog.item c ON i.itemId = c.id
10407 amar.kumar 204 
		JOIN inventory.warehouse w ON w.id = s.warehouseId
7678 amar.kumar 205 
		where s.type in ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN','MARKED_USED','MARKED_BAD','WAREHOUSE_TRANSFER_OUT') and c.type = 'SERIALIZED'
10407 amar.kumar 206 
		and w.billingWarehouseId = #{physicalWarehouseId}
6558 amar.kumar 207 
		group by i.itemId) as y
208 
	ON x.itemId = y.itemId
209 
	JOIN catalog.item ci ON ci.id = x.itemId
6630 amar.kumar 210 
	where ci.type = 'SERIALIZED' having quantity>0
6558 amar.kumar 211 
  </select>
6762 amar.kumar 212 
 
213 
<select id = "getHistoricSerializedInventoryByScans" resultType = "inventoryAvailability" parameterType = "date">
214 
	SELECT x.itemId, ci.brand, ci.model_name as modelName,
215 
	ci.model_number as modelNumber, ci.color as color, (x.inx-if(y.outx is null,0,y.outx)) as quantity
216 
	from
13090 manish.sha 217 
	(select i.itemId, sum(s.quantity) as inx from scanNew s
6762 amar.kumar 218 
		JOIN inventoryItem i ON (i.id = s.inventoryItemId)
219 
		JOIN catalog.item c ON i.itemId = c.id
7678 amar.kumar 220 
		where s.type in ('PURCHASE','SALE_RET', 'MARKED_GOOD','WAREHOUSE_TRANSFER_IN') and c.type = 'SERIALIZED'
6762 amar.kumar 221 
		and s.scannedAt &lt; #{date}
222 
		group by i.itemId ) as x
223 
	LEFT JOIN
13090 manish.sha 224 
	(select i.itemId, sum(s.quantity) as outx from scanNew s
6762 amar.kumar 225 
		JOIN inventoryItem i on i.id = s.inventoryItemId
226 
		JOIN catalog.item c ON i.itemId = c.id
7678 amar.kumar 227 
		where s.type in ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN','MARKED_USED','MARKED_BAD','WAREHOUSE_TRANSFER_OUT') and c.type = 'SERIALIZED'
6762 amar.kumar 228 
		and s.scannedAt &lt; #{date}
229 
		group by i.itemId) as y
230 
	ON x.itemId = y.itemId
231 
	JOIN catalog.item ci ON ci.id = x.itemId
232 
	where ci.type = 'SERIALIZED' having quantity>0
233 
  </select>
234 
 
10308 amar.kumar 235 
<select id = "getOurHistoricSerializedInventoryByScans" resultType = "inventoryAvailability" parameterType = "date">
15045 manish.sha 236 
	SELECT z.Item_Id as itemId, z.Brand as brand, z.ModelName as modelName, z.ModelNumber as modelNumber, z.Color as color,
237 
	sum(z.Quantity) quantity FROM (SELECT x.itemId Item_Id, ci.brand Brand, ci.model_name as ModelName, ci.model_number as ModelNumber,
238 
	ci.color Color, (x.inx-if(y.outx is null,0,y.outx)) Quantity, x.billingWarehouseId, wh.displayName Warehouse
239 
	FROM (SELECT i.itemId, sum(quantity) as inx, w.billingWarehouseId FROM scanNew s JOIN inventoryItem i ON (i.id = s.inventoryItemId)
240 
	JOIN catalog.item c ON i.itemId = c.id JOIN inventory.warehouse w ON w.id = s.warehouseId
241 
	WHERE s.type in ('PURCHASE','SALE_RET', 'MARKED_GOOD','WAREHOUSE_TRANSFER_IN')  AND c.type= 'SERIALIZED'
242 
	AND w.billingWarehouseId not in (select id from inventory.warehouse where vendor_id= 40 and inventoryType = 'GOOD'
243 
	and warehouseType ='OURS_THIRDPARTY' and source in (3,7,8)) AND s.scannedAt &lt; #{date} GROUP BY i.itemId, w.billingWarehouseId ) as x
244 
	LEFT JOIN (SELECT i.itemId, sum(quantity) as outx, w.billingWarehouseId FROM scanNew s JOIN inventoryItem i on i.id = s.inventoryItemId
245 
	JOIN catalog.item c ON i.itemId = c.id JOIN inventory.warehouse w ON w.id = s.warehouseId
246 
	WHERE s.type in ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN','MARKED_USED','MARKED_BAD','WAREHOUSE_TRANSFER_OUT') AND c.type= 'SERIALIZED'
247 
	AND w.billingWarehouseId not in (select id from inventory.warehouse where vendor_id= 40 and inventoryType = 'GOOD'
248 
	and warehouseType ='OURS_THIRDPARTY' and source in (3,7,8)) AND s.scannedAt &lt; #{date}
249 
	GROUP BY i.itemId, w.billingWarehouseId ) as y ON (x.itemId = y.itemId AND x.billingWarehouseId = y.billingWarehouseId)
250 
	JOIN catalog.item ci ON ci.id = x.itemId JOIN inventory.warehouse wh on (x.billingWarehouseId = wh.id) having quantity>0) as z group by z.Item_Id
10308 amar.kumar 251 
</select>
252 
 
6558 amar.kumar 253 
 
6630 amar.kumar 254 
  <select id = "getCurrentNonSerializedInventoryByScans" resultType = "inventoryAvailability">
255 
	SELECT x.itemId, ci.brand, ci.model_name as modelName,
256 
	ci.model_number as modelNumber, ci.color as color, (x.inx-if(y.outx is null,0,y.outx)) as quantity
257 
	from
258 
	(select i.itemId, sum(quantity) as inx from scanNew s
259 
		JOIN inventoryItem i ON (i.id = s.inventoryItemId)
260 
		JOIN catalog.item c ON i.itemId = c.id
8603 amar.kumar 261 
		JOIN inventory.warehouse w ON w.id = s.warehouseId
7678 amar.kumar 262 
		where s.type in ('PURCHASE','SALE_RET', 'MARKED_GOOD','WAREHOUSE_TRANSFER_IN') and c.type = 'NON_SERIALIZED'
8603 amar.kumar 263 
		and w.billingWarehouseId = #{physicalWarehouseId}
6630 amar.kumar 264 
		group by i.itemId ) as x
265 
	LEFT JOIN
266 
	(select i.itemId, sum(quantity) as outx from scanNew s
267 
		JOIN inventoryItem i on i.id = s.inventoryItemId
268 
		JOIN catalog.item c ON i.itemId = c.id
8603 amar.kumar 269 
		JOIN inventory.warehouse w ON w.id = s.warehouseId
7678 amar.kumar 270 
		where s.type in ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN','MARKED_USED','MARKED_BAD','WAREHOUSE_TRANSFER_OUT') and c.type = 'NON_SERIALIZED'
8603 amar.kumar 271 
		and w.billingWarehouseId = #{physicalWarehouseId}
6630 amar.kumar 272 
		group by i.itemId) as y
273 
	ON x.itemId = y.itemId
274 
	JOIN catalog.item ci ON ci.id = x.itemId
275 
	where ci.type = 'NON_SERIALIZED' having quantity>0
276 
  </select>
6762 amar.kumar 277 
 
278 
<select id = "getHistoricNonSerializedInventoryByScans" resultType = "inventoryAvailability" parameterType = "date">
279 
	SELECT x.itemId, ci.brand, ci.model_name as modelName,
280 
	ci.model_number as modelNumber, ci.color as color, (x.inx-if(y.outx is null,0,y.outx)) as quantity
281 
	from
7410 amar.kumar 282 
	(SELECT i.itemId, sum(quantity) as inx from scanNew s
6762 amar.kumar 283 
		JOIN inventoryItem i ON (i.id = s.inventoryItemId)
284 
		JOIN catalog.item c ON i.itemId = c.id
7678 amar.kumar 285 
		where s.type in ('PURCHASE','SALE_RET', 'MARKED_GOOD','WAREHOUSE_TRANSFER_IN') and c.type = 'NON_SERIALIZED'
6762 amar.kumar 286 
		and s.scannedAt &lt; #{date}
287 
		group by i.itemId ) as x
288 
	LEFT JOIN
7410 amar.kumar 289 
	(SELECT i.itemId, sum(quantity) as outx from scanNew s
6762 amar.kumar 290 
		JOIN inventoryItem i on i.id = s.inventoryItemId
291 
		JOIN catalog.item c ON i.itemId = c.id
7678 amar.kumar 292 
		where s.type in ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN','MARKED_USED','MARKED_BAD','WAREHOUSE_TRANSFER_OUT') and c.type = 'NON_SERIALIZED'
6762 amar.kumar 293 
		and s.scannedAt &lt; #{date}
294 
		group by i.itemId) as y
295 
	ON x.itemId = y.itemId
296 
	JOIN catalog.item ci ON ci.id = x.itemId
297 
	where ci.type = 'NON_SERIALIZED' having quantity>0
298 
  </select>
299 
 
10308 amar.kumar 300 
<select id = "getOurHistoricNonSerializedInventoryByScans" resultType = "inventoryAvailability" parameterType = "date">
15045 manish.sha 301 
	SELECT z.Item_Id as itemId, z.Brand as brand, z.ModelName as modelName, z.ModelNumber as modelNumber, z.Color as color,
302 
	sum(z.Quantity) as quantity FROM (SELECT x.itemId Item_Id, ci.brand Brand, ci.model_name as ModelName, ci.model_number as ModelNumber,
303 
	ci.color Color, (x.inx-if(y.outx is null,0,y.outx)) Quantity, x.billingWarehouseId, wh.displayName Warehouse
304 
	FROM (SELECT i.itemId, sum(quantity) as inx, w.billingWarehouseId FROM scanNew s
305 
	JOIN inventoryItem i ON (i.id = s.inventoryItemId) JOIN catalog.item c ON i.itemId = c.id
306 
	JOIN inventory.warehouse w ON w.id = s.warehouseId  WHERE s.type in ('PURCHASE','SALE_RET', 'MARKED_GOOD','WAREHOUSE_TRANSFER_IN')
307 
	AND c.type= 'NON_SERIALIZED' AND w.billingWarehouseId not in (select id from inventory.warehouse where vendor_id= 40 and inventoryType = 'GOOD'
308 
	and warehouseType ='OURS_THIRDPARTY' and source in (3,7,8)) AND s.scannedAt &lt; #{date} GROUP BY i.itemId, w.billingWarehouseId ) as x
309 
	LEFT JOIN (SELECT i.itemId, sum(quantity) as outx, w.billingWarehouseId FROM scanNew s JOIN inventoryItem i on i.id = s.inventoryItemId
310 
	JOIN catalog.item c ON i.itemId = c.id JOIN inventory.warehouse w ON w.id = s.warehouseId
311 
	WHERE s.type in ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN','MARKED_USED','MARKED_BAD','WAREHOUSE_TRANSFER_OUT')
312 
	AND c.type= 'NON_SERIALIZED' AND w.billingWarehouseId not in (select id from inventory.warehouse where vendor_id= 40 and inventoryType = 'GOOD'
313 
	and warehouseType ='OURS_THIRDPARTY' and source in (3,7,8)) AND s.scannedAt &lt; #{date} GROUP BY i.itemId, w.billingWarehouseId ) as y
314 
	ON (x.itemId = y.itemId AND x.billingWarehouseId = y.billingWarehouseId) JOIN catalog.item ci ON ci.id = x.itemId
315 
	JOIN inventory.warehouse wh on (x.billingWarehouseId = wh.id) having quantity>0) as z group by z.Item_Id
10308 amar.kumar 316 
</select>
317 
 
6880 amar.kumar 318 
 <select id = "getMovementSerializedInventoryByScans" resultType = "inventoryMovement" parameterType = "map">
7410 amar.kumar 319 
 	SELECT itemId, brand, model_name as modelName, model_number as modelNumber, color, s.type, count(*) as quantity
6762 amar.kumar 320 
 	FROM inventoryItem i JOIN scanNew s ON i.id = s.inventoryItemId
321 
 	LEFT JOIN catalog.item c ON i.itemId = c.id
6963 amar.kumar 322 
 	where s.scannedAt between #{startDate} and #{endDate} and c.type = 'SERIALIZED'
323 
 	and i.currentWarehouseId not in (1,2,6,9)
12357 manish.sha 324 
 	and s.warehouseId not in (select id from inventory.warehouse where vendor_id= 40
12543 manish.sha 325 
	and inventoryType = 'GOOD' and warehouseType ='OURS_THIRDPARTY' and source in (3,7,8))
6762 amar.kumar 326 
 	group by itemId, s.type
327 
 </select>
328 
 
329 
 <select id = "getMovementNonSerializedInventoryByScans" resultType = "inventoryMovement" parameterType = "map">
7410 amar.kumar 330 
 	SELECT itemId, brand, model_name as modelName, model_number as modelNumber, color, s.type, sum(quantity) as quantity
6762 amar.kumar 331 
 	FROM inventoryItem i JOIN scanNew s ON i.id = s.inventoryItemId
332 
 	LEFT JOIN catalog.item c ON i.itemId = c.id
6963 amar.kumar 333 
 	where s.scannedAt between #{startDate} and #{endDate} and c.type = 'NON_SERIALIZED'
10308 amar.kumar 334 
 	and i.currentWarehouseId not in (1,2,6,9)
12357 manish.sha 335 
 	and s.warehouseId not in (select id from inventory.warehouse where vendor_id= 40
12543 manish.sha 336 
	and inventoryType = 'GOOD' and warehouseType ='OURS_THIRDPARTY' and source in (3,7,8))
6762 amar.kumar 337 
 	group by itemId, s.type
6880 amar.kumar 338 
 </select>
7216 amar.kumar 339 
 
340 
 <select id = "getCompleteMovementSerializedInventoryByScans" resultType = "inventoryMovement" parameterType = "map">
13529 manish.sha 341 
 	SELECT itemId, brand, model_name as modelName, model_number as modelNumber, color, s.type, sum(quantity) as quantity
7216 amar.kumar 342 
 	FROM inventoryItem i JOIN scanNew s ON i.id = s.inventoryItemId
343 
 	LEFT JOIN catalog.item c ON i.itemId = c.id
13080 manish.sha 344 
 	where s.scannedAt between #{startDate} and #{endDate} and c.type = 'SERIALIZED'
13529 manish.sha 345 
 	AND s.warehouseId NOT IN (select id from inventory.warehouse where vendor_id= 40 and inventoryType = 'GOOD' and warehouseType ='OURS_THIRDPARTY' and source in (3,7,8))
7216 amar.kumar 346 
 	group by itemId, s.type
347 
 </select>
348 
 
349 
 <select id = "getCompleteMovementNonSerializedInventoryByScans" resultType = "inventoryMovement" parameterType = "map">
7410 amar.kumar 350 
 	SELECT itemId, brand, model_name as modelName, model_number as modelNumber, color, s.type, sum(quantity) as quantity
7216 amar.kumar 351 
 	FROM inventoryItem i JOIN scanNew s ON i.id = s.inventoryItemId
352 
 	LEFT JOIN catalog.item c ON i.itemId = c.id
13080 manish.sha 353 
 	where s.scannedAt between #{startDate} and #{endDate} and c.type = 'NON_SERIALIZED'
13529 manish.sha 354 
 	AND s.warehouseId NOT IN (select id from inventory.warehouse where vendor_id= 40 and inventoryType = 'GOOD' and warehouseType ='OURS_THIRDPARTY' and source in (3,7,8))
7216 amar.kumar 355 
 	group by itemId, s.type
356 
 </select>
6630 amar.kumar 357 
 
7410 amar.kumar 358 
<select id = "getTransferrableWarehousesFromWarehouse" resultType = "java.lang.Long" parameterType = "java.lang.Long">
359 
 	SELECT toWarehouseId
360 
 	FROM possibleWarehouseMovement
361 
 	WHERE fromWarehouseId = #{warehouseId}
362 
 </select>
363 
 
7957 amar.kumar 364 
<select id = "getHistoricBadInventoryByScans" resultType = "inventoryAvailability" parameterType = "date">
15045 manish.sha 365 
	SELECT z.Item_Id as itemId, z.Brand as brand, z.ModelName as modelName, z.ModelNumber as modelNumber, z.Color as color, sum(z.Quantity) as quantity
366 
	FROM (SELECT x.itemId Item_Id, ci.brand Brand, ci.model_name as ModelName, ci.model_number as ModelNumber, ci.color Color,
367 
	(x.inx-if(y.outx is null,0,y.outx)) Quantity, x.billingWarehouseId, wh.displayName Warehouse
368 
	FROM (SELECT i.itemId, sum(quantity) as inx, w.billingWarehouseId FROM scanNew s
369 
	JOIN inventoryItem i ON (i.id = s.inventoryItemId) JOIN catalog.item c ON i.itemId = c.id
370 
	JOIN inventory.warehouse w ON w.id = s.warehouseId  WHERE s.type in ('MARKED_BAD','DOA_IN','SALE_RET_UNUSABLE','DOA_REJECTED')
371 
	AND w.billingWarehouseId not in (9,12) AND s.scannedAt &gt; '2013-01-01'
372 
	AND s.scannedAt &lt; #{date} GROUP BY i.itemId, w.billingWarehouseId ) as x
373 
	LEFT JOIN (SELECT i.itemId, sum(quantity) as outx, w.billingWarehouseId FROM scanNew s JOIN inventoryItem i on i.id = s.inventoryItemId
374 
	JOIN catalog.item c ON i.itemId = c.id JOIN inventory.warehouse w ON w.id = s.warehouseId
18127 manish.sha 375 
	WHERE s.type in ('DOA_OUT','BAD_SALE','MARKED_GOOD','BAD_PURCHASE_RETURN','LOST_IN_WAREHOUSE_BAD','EMPTY_PRODUCT_RECEIVED','WRONG_PRODUCT_RECEIVED')
15045 manish.sha 376 
	AND w.billingWarehouseId not in (9,12) AND s.scannedAt &gt; '2013-01-01'
377 
	AND s.scannedAt &lt; #{date} GROUP BY i.itemId, w.billingWarehouseId ) as y ON (x.itemId = y.itemId
378 
	AND x.billingWarehouseId = y.billingWarehouseId) JOIN catalog.item ci ON ci.id = x.itemId
379 
	JOIN inventory.warehouse wh on (x.billingWarehouseId = wh.id) having quantity>0) as z group by z.Item_Id
7957 amar.kumar 380 
</select>
7410 amar.kumar 381 
 
382 
<select id = "getScansForTransferLot" resultType = "scan" parameterType = "java.lang.Long">
383 
	SELECT *
384 
	FROM scanNew
385 
	WHERE transferLotId = #{id}
386 
	AND type = 'WAREHOUSE_TRANSFER_OUT'
387 
</select>
10120 manish.sha 388 
 
389 
<select id= "getScansForTransferLotAndInventoryItem" resultType = "scan" parameterType="map" >
390 
	SELECT *
391 
	FROM scanNew
392 
	WHERE transferLotId = #{transferLotId}
393 
	AND inventoryItemId = #{inventoryItemId}
394 
	AND type = 'WAREHOUSE_TRANSFER_OUT'
395 
	LIMIT 1
396 
</select>
10689 manish.sha 397 
 
398 
<select id="getAmazonTransferredSkuDetails" resultType= "amazonTransferredSkuDetail" parameterType="map">
12357 manish.sha 399 
	SELECT Z.itemId, Z.purchaseId, Z.purchaseDate, sum(Z.quantity) as quantity, Z.unitPrice, Z.nlc,
400 
	Z.brand, Z.modelName, Z.modelNumber, Z.category, Z.color, Z.taxType
401 
	FROM  (SELECT X.itemId, X.purchaseId, X.purchaseDate, X.quantity, X.unitPrice, X.nlc, X.brand,
402 
	X.modelName, X.modelNumber, X.category, X.color , X.inventoryItemId, X.scanTime, X.taxType
403 
	FROM (SELECT i.itemId as itemId, i.purchaseId as purchaseId, p.receivedOn as purchaseDate,
404 
	s.quantity as quantity, l.unitPrice as unitPrice, l.nlc as nlc, l.brand as brand,
405 
	l.modelName as modelName, l.modelNumber as modelNumber, l.productGroup as category,
406 
	l.color as color , i.id as inventoryItemId, s.scannedAt as scanTime, CASE po.taxType
407 
	WHEN 0 THEN 'VAT' WHEN 1 THEN 'CST' WHEN 2 THEN 'CFORM' END as taxType FROM scanNew s
408 
	JOIN inventoryItem i on s.inventoryItemId= i.id JOIN transferLot t on (s.transferLotId= t.id)
409 
	JOIN purchase p on p.id= i.purchaseId JOIN purchaseorder po on p.purchaseOrder_id = po.id
10689 manish.sha 410 
	JOIN lineitem l on (l.purchaseOrder_id= p.purchaseOrder_id and l.itemId= i.itemId)
411 
	WHERE i.itemId in
412 
	<foreach item="itemId" index="index" collection="itemIds"
413 
        open="(" separator="," close=")">
414 
	 #{itemId}
415 
	</foreach>
12357 manish.sha 416 
	and t.destinationWarehouseId in (select id from inventory.warehouse where vendor_id= 40
417 
	and inventoryType = 'GOOD' and warehouseType ='OURS_THIRDPARTY' and source = 3)
10689 manish.sha 418 
	AND t.status in ('TRANSFER_COMPLETE','IN_TRANSIT','PARTIAL_TRANSFER')
12658 manish.sha 419 
	AND i.transferStatus is not null AND s.type='WAREHOUSE_TRANSFER_OUT'
12357 manish.sha 420 
	GROUP BY i.itemId, s.transferLotId, i.purchaseId, i.id order by s.scannedAt desc)
421 
	as X GROUP BY X.inventoryItemId
422 
	UNION SELECT Y.itemId, Y.purchaseId, Y.purchaseDate, Y.quantity, Y.unitPrice, Y.nlc, Y.brand,
423 
	Y.modelName, Y.modelNumber, Y.category, Y.color , Y.inventoryItemId, Y.scanTime, Y.taxType
424 
	FROM (SELECT i.itemId as itemId, i.purchaseId as purchaseId, p.receivedOn as purchaseDate,
425 
	s.quantity as quantity, l.unitPrice as unitPrice, l.nlc as nlc, l.brand as brand, l.modelName,
426 
	l.modelNumber as modelNumber, l.productGroup as category, l.color as color,
427 
	i.id as inventoryItemId, s.scannedAt as scanTime, CASE po.taxType
428 
	WHEN 0 THEN 'VAT' WHEN 1 THEN 'CST' WHEN 2 THEN 'CFORM' END as taxType FROM scanNew s
429 
	JOIN inventoryItem i on s.inventoryItemId = i.id JOIN purchase p on p.id= i.purchaseId
430 
	JOIN purchaseorder po on p.purchaseOrder_id = po.id
431 
	JOIN lineitem l on (l.purchaseOrder_id= p.purchaseOrder_id and l.itemId= i.itemId)
432 
	WHERE i.itemId in
433 
	<foreach item="itemId" index="index" collection="itemIds"
434 
        open="(" separator="," close=")">
435 
	 #{itemId}
436 
	</foreach>
437 
	and po.warehouseId in (select id from inventory.warehouse where vendor_id= 40
438 
	and inventoryType = 'GOOD' and warehouseType ='OURS_THIRDPARTY' and source = 3)
439 
	GROUP BY i.itemId, i.purchaseId, i.id ORDER BY s.scannedAt desc) as Y
440 
	GROUP BY Y.inventoryItemId) as Z GROUP BY Z.itemId, Z.purchaseId order by Z.purchaseId desc;
10689 manish.sha 441 
</select>
442 
 
443 
<select id="getScansforPurchase" resultType="scan" parameterType="map" >
444 
	SELECT s.id, s.inventoryItemId, s.warehouseId, s.type, s.scannedAt, s.quantity, s.orderId, s.remarks, s.transferLotId
445 
	FROM scanNew s
446 
	JOIN inventoryItem i on s.inventoryItemId = i.id
447 
	WHERE i.purchaseId= #{purchaseId} and s.type=#{scanType}
448 
</select>
12800 manish.sha 449 
 
450 
<select id="getInventoryAgeConsideredItems" resultType="invAgeConsiderItems" parameterType = "java.lang.Long">
451 
	SELECT ii.id, ii.itemId, ii.currentQuantity, ii.purchaseId, DATEDIFF(now(), MIN(s.scannedAt)) / 7 as age,
12844 manish.sha 452 
	l.productGroup as category, l.unitPrice as cost
12800 manish.sha 453 
	FROM inventoryItem ii JOIN scanNew s ON (s.inventoryItemId = ii.id) JOIN purchase p ON (p.id = ii.purchaseId)
454 
	JOIN lineitem l ON (l.purchaseOrder_id = p.purchaseOrder_id AND l.itemId = ii.itemId)
455 
	WHERE ((ii.lastScanType IN ('PURCHASE', 'SALE_RET', 'MARKED_GOOD')) OR (ii.currentQuantity>0) OR (ii.lastScanType IN ('SALE') and ii.currentQuantity = 1))
456 
	AND ii.lastScanType != 'MARKED_BAD' AND (transferStatus is NULL or transferStatus != 'IN_TRANSIT') AND ii.itemId = #{itemId}
457 
	AND ii.physicalWarehouseId NOT IN (select id from inventory.warehouse where vendor_id= 40 and inventoryType = 'GOOD' and warehouseType ='OURS_THIRDPARTY' and source in (3,7,8))
458 
	GROUP BY ii.id, ii.itemId, ii.currentQuantity order by ii.id desc;
459 
</select>
460 
 
13529 manish.sha 461 
<select id="getCompleteMovementForThirdPartyWarehouse" resultType = "inventoryMovement" parameterType = "map">
462 
	SELECT itemId, brand, model_name as modelName, model_number as modelNumber, color, s.type, sum(quantity) as quantity
463 
 	FROM inventoryItem i JOIN scanNew s ON i.id = s.inventoryItemId
464 
 	LEFT JOIN catalog.item c ON i.itemId = c.id
465 
 	where s.scannedAt between #{startDate} and #{endDate}
466 
 	AND s.warehouseId IN (select id from inventory.warehouse where vendor_id= 40 and inventoryType = 'GOOD' and warehouseType ='OURS_THIRDPARTY' and source in (3,7,8))
467 
 	group by itemId, s.type
468 
</select>
469 
 
16264 manish.sha 470 
<select id="fetchMismatchScansPerTransferInvoiceNumber" parameterType="java.util.Date" resultType="transferInvoiceScan" >
471 
	SELECT r.shipmentReference, t.id transferLotId, r.source, r.quantity,
472 
	CASE r.inventoryType WHEN 1 THEN 'GOOD' WHEN 2 THEN 'BAD' END inventoryType,
473 
	1000 * UNIX_TIMESTAMP(DATE(r.createdOn)) date, sum(s.quantity) scannedQuantity
474 
	FROM removalStockShipmentReference r JOIN transferLot t on r.id = t.shipmentReference
475 
	JOIN scanNew s on t.id = s.transferLotId WHERE DATE(r.createdOn) = #{date}
476 
	AND s.scannedAt BETWEEN #{date} and ADDDATE(#{date},1) AND s.type= 'WAREHOUSE_TRANSFER_IN'
477 
	GROUP BY r.id, r.source, r.inventoryType, r.quantity
478 
	HAVING sum(s.quantity)!=r.quantity
479 
</select>
480 
 
4500 mandeep.dh 481 
</mapper>