Subversion Repositories SmartDukaan

Rev

Rev 11311 | Rev 11512 | Go to most recent revision | 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,
8580 amar.kumar 49 
        brand, modelName, modelNumber, color, unitPrice, SUM(initialQuantity) AS quantity, 1000 * UNIX_TIMESTAMP(receivedOn) as purchasedAt,
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,
70 
        p.invoiceNumber AS invoiceNumbers, inv.receivedFrom AS receivedBy, i.itemId,brand, modelName, modelNumber, color,
8580 amar.kumar 71 
        unitPrice, SUM(initialQuantity) AS quantity, 1000 * UNIX_TIMESTAMP(receivedOn) as purchasedAt, p.id as purchaseId,
11336 manish.sha 72 
        nlc, po.warehouseId, po.taxType, 1000 * UNIX_TIMESTAMP(inv.invoiceDate) as invoiceDate
7210 amar.kumar 73 
    FROM purchase p
74 
    JOIN purchaseorder po
75 
        ON (po.id = p.purchaseOrder_id)
76 
    JOIN inventoryItem i
77 
        ON (p.id = i.purchaseId)
78 
    JOIN lineitem l
79 
        ON (l.itemId = i.itemId AND l.purchaseOrder_id = po.id)
80 
    JOIN supplier s
81 
        ON (s.id = po.supplierId)
82 
    JOIN invoice inv
7224 amar.kumar 83 
    	ON (inv.invoiceNumber = p.invoiceNumber AND inv.supplierId = po.supplierId)
7216 amar.kumar 84 
    WHERE p.receivedOn BETWEEN #{startDate} AND #{endDate}
85 
    AND inv.date  &lt; #{endDate}
7224 amar.kumar 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,
11220 manish.sha 121 
        SUM((age &gt; 0) and (age &lt;= 13) * currentQuantity) AS zeroToThreeMonthCount,
122 
        SUM((age &gt; 13) and (age &lt;= 26) * currentQuantity) AS threeToSixMonthCount,
123 
        SUM((age &gt; 26) and (age &lt;= 52) * currentQuantity) AS sixToTwelveMonthCount,
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)
8630 amar.kumar 136 
           AND (transferStatus is NULL or transferStatus != 'IN_TRANSIT')
11311 manish.sha 137 
           AND ii.physicalWarehouseId NOT IN (16, 1771)
5711 mandeep.dh 138 
           GROUP BY ii.id, ii.itemId, ii.currentQuantity) t
139 
    JOIN purchase p
140 
        ON (p.id = t.purchaseId)
141 
    JOIN lineitem l
142 
        ON (l.purchaseOrder_id = p.purchaseOrder_id AND l.itemId = t.itemId)
143 
    GROUP BY t.itemId
144 
    HAVING SUM(currentQuantity) != 0
145 
  </select>
6322 amar.kumar 146 
 
7089 amar.kumar 147 
  <!--select id="inventoryAgeOnePlus" resultType="detailedInventoryAge">
148 
    SELECT t.itemId, brand, modelName, modelNumber, color, scannedAt, i.warehouseId
149 
        SUM((age &gt;= 1) * currentQuantity) AS onePlusCount,
150 
        SUM((age &gt;= 1) * unitPrice) as onePlusCost,
151 
        l.productGroup as category
152 
    FROM  (SELECT ii.id, ii.itemId, ii.currentQuantity, ii.purchaseId, MIN(s.scannedAt)
153 
                DATEDIFF(now(), MAX(s.scannedAt)) / 7 as age
154 
           FROM inventoryItem ii
155 
           JOIN scanNew s
156 
                ON (s.inventoryItemId = ii.id)
157 
           WHERE ii.lastScanType IN ('PURCHASE', 'SALE_RET')
158 
           GROUP BY ii.id, ii.itemId, ii.currentQuantity) t
159 
    JOIN purchase p
160 
        ON (p.id = t.purchaseId)
161 
    JOIN lineitem l
162 
        ON (l.purchaseOrder_id = p.purchaseOrder_id AND l.itemId = t.itemId)
163 
    GROUP BY t.itemId, i.warehouseId
164 
    HAVING SUM(currentQuantity) != 0
165 
  </select-->
166 
 
167 
 
168 
 
6322 amar.kumar 169 
  <select id = "getScansForItem" parameterType = "map" resultType = "scan">
170 
	  SELECT s.id, s.inventoryItemId, s.warehouseId, s.type, s.scannedAt,
171 
	  s.quantity, s.orderId from scanNew s JOIN inventoryItem i
172 
	  ON
173 
	  s.inventoryItemId = i.id
174 
	  AND
6414 amar.kumar 175 
	  i.itemId = #{itemId}
6322 amar.kumar 176 
	  AND
177 
	  s.scannedAt BETWEEN #{fromDate} AND #{toDate};
178 
  </select>
179 
 
6558 amar.kumar 180 
  <insert id="genericScan" parameterType="scan" useGeneratedKeys="true" keyProperty="id">
6467 amar.kumar 181 
    INSERT INTO scanNew
7410 amar.kumar 182 
     (inventoryItemId, warehouseId, type, scannedAt, quantity, orderId, transferLotId, remarks)
6467 amar.kumar 183 
    VALUES
7410 amar.kumar 184 
      (#{inventoryItemId}, #{warehouseId}, #{type}, NOW(), #{quantity}, #{orderId}, #{transferLotId}, #{remarks})
6467 amar.kumar 185 
  </insert>
186 
 
6558 amar.kumar 187 
  <select id = "getCurrentSerializedInventoryByScans" resultType = "inventoryAvailability">
188 
	SELECT x.itemId, ci.brand, ci.model_name as modelName,
6630 amar.kumar 189 
	ci.model_number as modelNumber, ci.color as color, (x.inx-if(y.outx is null,0,y.outx)) as quantity
6558 amar.kumar 190 
	from
191 
	(select i.itemId, count(*) as inx from scanNew s
192 
		JOIN inventoryItem i ON (i.id = s.inventoryItemId)
193 
		JOIN catalog.item c ON i.itemId = c.id
10407 amar.kumar 194 
		JOIN inventory.warehouse w ON w.id = s.warehouseId
195 
		where s.type in ('PURCHASE','SALE_RET', 'MARKED_GOOD','WAREHOUSE_TRANSFER_IN') and c.type = 'SERIALIZED'
10402 manish.sha 196 
		and w.billingWarehouseId = #{physicalWarehouseId}
6558 amar.kumar 197 
		group by i.itemId ) as x
6630 amar.kumar 198 
	LEFT JOIN
6558 amar.kumar 199 
	(select i.itemId, count(*) as outx from scanNew s
200 
		JOIN inventoryItem i on i.id = s.inventoryItemId
201 
		JOIN catalog.item c ON i.itemId = c.id
10407 amar.kumar 202 
		JOIN inventory.warehouse w ON w.id = s.warehouseId
7678 amar.kumar 203 
		where s.type in ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN','MARKED_USED','MARKED_BAD','WAREHOUSE_TRANSFER_OUT') and c.type = 'SERIALIZED'
10407 amar.kumar 204 
		and w.billingWarehouseId = #{physicalWarehouseId}
6558 amar.kumar 205 
		group by i.itemId) as y
206 
	ON x.itemId = y.itemId
207 
	JOIN catalog.item ci ON ci.id = x.itemId
6630 amar.kumar 208 
	where ci.type = 'SERIALIZED' having quantity>0
6558 amar.kumar 209 
  </select>
6762 amar.kumar 210 
 
211 
<select id = "getHistoricSerializedInventoryByScans" resultType = "inventoryAvailability" parameterType = "date">
212 
	SELECT x.itemId, ci.brand, ci.model_name as modelName,
213 
	ci.model_number as modelNumber, ci.color as color, (x.inx-if(y.outx is null,0,y.outx)) as quantity
214 
	from
215 
	(select i.itemId, count(*) as inx from scanNew s
216 
		JOIN inventoryItem i ON (i.id = s.inventoryItemId)
217 
		JOIN catalog.item c ON i.itemId = c.id
7678 amar.kumar 218 
		where s.type in ('PURCHASE','SALE_RET', 'MARKED_GOOD','WAREHOUSE_TRANSFER_IN') and c.type = 'SERIALIZED'
6762 amar.kumar 219 
		and s.scannedAt &lt; #{date}
220 
		group by i.itemId ) as x
221 
	LEFT JOIN
222 
	(select i.itemId, count(*) as outx from scanNew s
223 
		JOIN inventoryItem i on i.id = s.inventoryItemId
224 
		JOIN catalog.item c ON i.itemId = c.id
7678 amar.kumar 225 
		where s.type in ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN','MARKED_USED','MARKED_BAD','WAREHOUSE_TRANSFER_OUT') and c.type = 'SERIALIZED'
6762 amar.kumar 226 
		and s.scannedAt &lt; #{date}
227 
		group by i.itemId) as y
228 
	ON x.itemId = y.itemId
229 
	JOIN catalog.item ci ON ci.id = x.itemId
230 
	where ci.type = 'SERIALIZED' having quantity>0
231 
  </select>
232 
 
10308 amar.kumar 233 
<select id = "getOurHistoricSerializedInventoryByScans" resultType = "inventoryAvailability" parameterType = "date">
234 
	SELECT x.itemId, ci.brand, ci.model_name as modelName,
235 
	ci.model_number as modelNumber, ci.color as color, (x.inx-if(y.outx is null,0,y.outx)) as quantity
236 
	from
237 
	(select i.itemId, count(*) as inx from scanNew s
238 
		JOIN inventoryItem i ON (i.id = s.inventoryItemId)
239 
		JOIN catalog.item c ON i.itemId = c.id
240 
		where s.type in ('PURCHASE','SALE_RET', 'MARKED_GOOD','WAREHOUSE_TRANSFER_IN') and c.type = 'SERIALIZED'
241 
		and s.scannedAt &lt; #{date}
242 
		and s.warehouseId != 16
243 
		group by i.itemId ) as x
244 
	LEFT JOIN
245 
	(select i.itemId, count(*) as outx from scanNew s
246 
		JOIN inventoryItem i on i.id = s.inventoryItemId
247 
		JOIN catalog.item c ON i.itemId = c.id
248 
		where s.type in ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN','MARKED_USED','MARKED_BAD','WAREHOUSE_TRANSFER_OUT') and c.type = 'SERIALIZED'
249 
		and s.scannedAt &lt; #{date}
250 
		and s.warehouseId != 16
251 
		group by i.itemId) as y
252 
	ON x.itemId = y.itemId
253 
	JOIN catalog.item ci ON ci.id = x.itemId
254 
	where ci.type = 'SERIALIZED' having quantity>0
255 
</select>
256 
 
6558 amar.kumar 257 
 
6630 amar.kumar 258 
  <select id = "getCurrentNonSerializedInventoryByScans" resultType = "inventoryAvailability">
259 
	SELECT x.itemId, ci.brand, ci.model_name as modelName,
260 
	ci.model_number as modelNumber, ci.color as color, (x.inx-if(y.outx is null,0,y.outx)) as quantity
261 
	from
262 
	(select i.itemId, sum(quantity) as inx from scanNew s
263 
		JOIN inventoryItem i ON (i.id = s.inventoryItemId)
264 
		JOIN catalog.item c ON i.itemId = c.id
8603 amar.kumar 265 
		JOIN inventory.warehouse w ON w.id = s.warehouseId
7678 amar.kumar 266 
		where s.type in ('PURCHASE','SALE_RET', 'MARKED_GOOD','WAREHOUSE_TRANSFER_IN') and c.type = 'NON_SERIALIZED'
8603 amar.kumar 267 
		and w.billingWarehouseId = #{physicalWarehouseId}
6630 amar.kumar 268 
		group by i.itemId ) as x
269 
	LEFT JOIN
270 
	(select i.itemId, sum(quantity) as outx from scanNew s
271 
		JOIN inventoryItem i on i.id = s.inventoryItemId
272 
		JOIN catalog.item c ON i.itemId = c.id
8603 amar.kumar 273 
		JOIN inventory.warehouse w ON w.id = s.warehouseId
7678 amar.kumar 274 
		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 275 
		and w.billingWarehouseId = #{physicalWarehouseId}
6630 amar.kumar 276 
		group by i.itemId) as y
277 
	ON x.itemId = y.itemId
278 
	JOIN catalog.item ci ON ci.id = x.itemId
279 
	where ci.type = 'NON_SERIALIZED' having quantity>0
280 
  </select>
6762 amar.kumar 281 
 
282 
<select id = "getHistoricNonSerializedInventoryByScans" resultType = "inventoryAvailability" parameterType = "date">
283 
	SELECT x.itemId, ci.brand, ci.model_name as modelName,
284 
	ci.model_number as modelNumber, ci.color as color, (x.inx-if(y.outx is null,0,y.outx)) as quantity
285 
	from
7410 amar.kumar 286 
	(SELECT i.itemId, sum(quantity) as inx from scanNew s
6762 amar.kumar 287 
		JOIN inventoryItem i ON (i.id = s.inventoryItemId)
288 
		JOIN catalog.item c ON i.itemId = c.id
7678 amar.kumar 289 
		where s.type in ('PURCHASE','SALE_RET', 'MARKED_GOOD','WAREHOUSE_TRANSFER_IN') and c.type = 'NON_SERIALIZED'
6762 amar.kumar 290 
		and s.scannedAt &lt; #{date}
291 
		group by i.itemId ) as x
292 
	LEFT JOIN
7410 amar.kumar 293 
	(SELECT i.itemId, sum(quantity) as outx from scanNew s
6762 amar.kumar 294 
		JOIN inventoryItem i on i.id = s.inventoryItemId
295 
		JOIN catalog.item c ON i.itemId = c.id
7678 amar.kumar 296 
		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 297 
		and s.scannedAt &lt; #{date}
298 
		group by i.itemId) as y
299 
	ON x.itemId = y.itemId
300 
	JOIN catalog.item ci ON ci.id = x.itemId
301 
	where ci.type = 'NON_SERIALIZED' having quantity>0
302 
  </select>
303 
 
10308 amar.kumar 304 
<select id = "getOurHistoricNonSerializedInventoryByScans" resultType = "inventoryAvailability" parameterType = "date">
305 
	SELECT x.itemId, ci.brand, ci.model_name as modelName,
306 
	ci.model_number as modelNumber, ci.color as color, (x.inx-if(y.outx is null,0,y.outx)) as quantity
307 
	from
308 
	(SELECT i.itemId, sum(quantity) as inx from scanNew s
309 
		JOIN inventoryItem i ON (i.id = s.inventoryItemId)
310 
		JOIN catalog.item c ON i.itemId = c.id
311 
		where s.type in ('PURCHASE','SALE_RET', 'MARKED_GOOD','WAREHOUSE_TRANSFER_IN') and c.type = 'NON_SERIALIZED'
312 
		and s.scannedAt &lt; #{date}
313 
		and s.warehouseId != 16
314 
		group by i.itemId ) as x
315 
	LEFT JOIN
316 
	(SELECT i.itemId, sum(quantity) as outx from scanNew s
317 
		JOIN inventoryItem i on i.id = s.inventoryItemId
318 
		JOIN catalog.item c ON i.itemId = c.id
319 
		where s.type in ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN','MARKED_USED','MARKED_BAD','WAREHOUSE_TRANSFER_OUT') and c.type = 'NON_SERIALIZED'
320 
		and s.scannedAt &lt; #{date}
321 
		and s.warehouseId != 16
322 
		group by i.itemId) as y
323 
	ON x.itemId = y.itemId
324 
	JOIN catalog.item ci ON ci.id = x.itemId
325 
	where ci.type = 'NON_SERIALIZED' having quantity>0
326 
</select>
327 
 
6880 amar.kumar 328 
 <select id = "getMovementSerializedInventoryByScans" resultType = "inventoryMovement" parameterType = "map">
7410 amar.kumar 329 
 	SELECT itemId, brand, model_name as modelName, model_number as modelNumber, color, s.type, count(*) as quantity
6762 amar.kumar 330 
 	FROM inventoryItem i JOIN scanNew s ON i.id = s.inventoryItemId
331 
 	LEFT JOIN catalog.item c ON i.itemId = c.id
6963 amar.kumar 332 
 	where s.scannedAt between #{startDate} and #{endDate} and c.type = 'SERIALIZED'
333 
 	and i.currentWarehouseId not in (1,2,6,9)
10308 amar.kumar 334 
 	and s.warehouseId != 16
6762 amar.kumar 335 
 	group by itemId, s.type
336 
 </select>
337 
 
338 
 <select id = "getMovementNonSerializedInventoryByScans" resultType = "inventoryMovement" parameterType = "map">
7410 amar.kumar 339 
 	SELECT itemId, brand, model_name as modelName, model_number as modelNumber, color, s.type, sum(quantity) as quantity
6762 amar.kumar 340 
 	FROM inventoryItem i JOIN scanNew s ON i.id = s.inventoryItemId
341 
 	LEFT JOIN catalog.item c ON i.itemId = c.id
6963 amar.kumar 342 
 	where s.scannedAt between #{startDate} and #{endDate} and c.type = 'NON_SERIALIZED'
10308 amar.kumar 343 
 	and i.currentWarehouseId not in (1,2,6,9)
344 
 	and s.warehouseId != 16
6762 amar.kumar 345 
 	group by itemId, s.type
6880 amar.kumar 346 
 </select>
7216 amar.kumar 347 
 
348 
 <select id = "getCompleteMovementSerializedInventoryByScans" resultType = "inventoryMovement" parameterType = "map">
7410 amar.kumar 349 
 	SELECT itemId, brand, model_name as modelName, model_number as modelNumber, color, s.type, count(*) as quantity
7216 amar.kumar 350 
 	FROM inventoryItem i JOIN scanNew s ON i.id = s.inventoryItemId
351 
 	LEFT JOIN catalog.item c ON i.itemId = c.id
352 
 	where s.scannedAt between #{startDate} and #{endDate} and c.type = 'SERIALIZED'
10308 amar.kumar 353 
 	and s.warehouseId != 16
7216 amar.kumar 354 
 	group by itemId, s.type
355 
 </select>
356 
 
357 
 <select id = "getCompleteMovementNonSerializedInventoryByScans" resultType = "inventoryMovement" parameterType = "map">
7410 amar.kumar 358 
 	SELECT itemId, brand, model_name as modelName, model_number as modelNumber, color, s.type, sum(quantity) as quantity
7216 amar.kumar 359 
 	FROM inventoryItem i JOIN scanNew s ON i.id = s.inventoryItemId
360 
 	LEFT JOIN catalog.item c ON i.itemId = c.id
361 
 	where s.scannedAt between #{startDate} and #{endDate} and c.type = 'NON_SERIALIZED'
10308 amar.kumar 362 
 	and s.warehouseId != 16
7216 amar.kumar 363 
 	group by itemId, s.type
364 
 </select>
6630 amar.kumar 365 
 
7410 amar.kumar 366 
<select id = "getTransferrableWarehousesFromWarehouse" resultType = "java.lang.Long" parameterType = "java.lang.Long">
367 
 	SELECT toWarehouseId
368 
 	FROM possibleWarehouseMovement
369 
 	WHERE fromWarehouseId = #{warehouseId}
370 
 </select>
371 
 
7957 amar.kumar 372 
<select id = "getHistoricBadInventoryByScans" resultType = "inventoryAvailability" parameterType = "date">
373 
	SELECT x.itemId, ci.brand, ci.model_name as modelName, ci.model_number as modelNumber,
374 
	ci.color as color, (x.inx-if(y.outx is null,0,y.outx)) as quantity
375 
	FROM
376 
		(SELECT i.itemId, count(*) as inx FROM scanNew s JOIN inventoryItem i ON
377 
		(i.id = s.inventoryItemId) JOIN catalog.item c ON i.itemId = c.id
10407 amar.kumar 378 
		where s.type in ('DOA_IN', 'SALE_RET_UNUSABLE') and
10489 amar.kumar 379 
		s.scannedAt &lt; #{date} and s.scannedAt &gt; '2013-01-01' group by i.itemId ) as x
7957 amar.kumar 380 
		LEFT JOIN
381 
		(SELECT i.itemId, count(*) as outx FROM scanNew s JOIN inventoryItem i ON
382 
		i.id = s.inventoryItemId JOIN catalog.item c ON i.itemId = c.id WHERE
10489 amar.kumar 383 
		s.type in ('DOA_OUT') and s.scannedAt &lt; #{date} and s.scannedAt &gt; '2013-01-01'
384 
		group by i.itemId) as y
7957 amar.kumar 385 
		ON x.itemId = y.itemId
386 
		JOIN catalog.item ci ON ci.id = x.itemId
387 
	having quantity>0;
388 
</select>
7410 amar.kumar 389 
 
390 
<select id = "getScansForTransferLot" resultType = "scan" parameterType = "java.lang.Long">
391 
	SELECT *
392 
	FROM scanNew
393 
	WHERE transferLotId = #{id}
394 
	AND type = 'WAREHOUSE_TRANSFER_OUT'
395 
</select>
10120 manish.sha 396 
 
397 
<select id= "getScansForTransferLotAndInventoryItem" resultType = "scan" parameterType="map" >
398 
	SELECT *
399 
	FROM scanNew
400 
	WHERE transferLotId = #{transferLotId}
401 
	AND inventoryItemId = #{inventoryItemId}
402 
	AND type = 'WAREHOUSE_TRANSFER_OUT'
403 
	LIMIT 1
404 
</select>
10689 manish.sha 405 
 
406 
<select id="getAmazonTransferredSkuDetails" resultType= "amazonTransferredSkuDetail" parameterType="map">
407 
	SELECT Y.itemId, Y.purchaseId, Y.purchaseDate, sum(Y.quantity) as quantity, Y.unitPrice , Y.brand, Y.modelName, Y.modelNumber, Y.category, Y.color
408 
	FROM (SELECT X.itemId, X.purchaseId, X.purchaseDate, X.quantity, X.unitPrice, X.brand, X.modelName, X.modelNumber, X.category, X.color , X.inventoryItemId, X.scanTime
409 
	FROM (SELECT i.itemId as itemId, i.purchaseId as purchaseId, p.receivedOn as purchaseDate, s.quantity as quantity, l.unitPrice as unitPrice, l.brand as brand, l.modelName as modelName, l.modelNumber as modelNumber, l.productGroup as category, l.color as color , i.id as inventoryItemId, s.scannedAt as scanTime
410 
	FROM scanNew s
411 
	JOIN inventoryItem i on s.inventoryItemId= i.id
412 
	JOIN transferLot t on (s.transferLotId= t.id)
413 
	JOIN purchase p on p.id= i.purchaseId
414 
	JOIN lineitem l on (l.purchaseOrder_id= p.purchaseOrder_id and l.itemId= i.itemId)
415 
	WHERE i.itemId in
416 
	<foreach item="itemId" index="index" collection="itemIds"
417 
        open="(" separator="," close=")">
418 
	 #{itemId}
419 
	</foreach>
420 
	AND t.destinationWarehouseId= 16
421 
	AND t.status in ('TRANSFER_COMPLETE','IN_TRANSIT','PARTIAL_TRANSFER')
422 
	AND i.transferStatus is not null AND type='WAREHOUSE_TRANSFER_OUT'
423 
	group by i.itemId, s.transferLotId, i.purchaseId, i.id
424 
	order by s.scannedAt desc) as X group by inventoryItemId) as Y group by Y.itemId, Y.purchaseId
10808 manish.sha 425 
	order by Y.purchaseId desc
10689 manish.sha 426 
</select>
427 
 
428 
<select id="getScansforPurchase" resultType="scan" parameterType="map" >
429 
	SELECT s.id, s.inventoryItemId, s.warehouseId, s.type, s.scannedAt, s.quantity, s.orderId, s.remarks, s.transferLotId
430 
	FROM scanNew s
431 
	JOIN inventoryItem i on s.inventoryItemId = i.id
432 
	WHERE i.purchaseId= #{purchaseId} and s.type=#{scanType}
433 
</select>
7410 amar.kumar 434 
 
4500 mandeep.dh 435 
</mapper>