Subversion Repositories SmartDukaan

Rev

Rev 10689 | Rev 11220 | 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,
9433 amar.kumar 72 
        nlc, po.warehouseId, po.taxType
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,
9830 amar.kumar 121 
        SUM((age &gt;= 1) * unitPrice*currentQuantity) as onePlusCost,
5768 mandeep.dh 122 
        SUM(currentQuantity) as zeroPlusCount,
9830 amar.kumar 123 
        SUM(l.unitPrice*currentQuantity) as zeroPlusCost,
5768 mandeep.dh 124 
        l.productGroup as category
5711 mandeep.dh 125 
    FROM  (SELECT ii.id, ii.itemId, ii.currentQuantity, ii.purchaseId,
7838 amar.kumar 126 
                DATEDIFF(now(), MIN(s.scannedAt)) / 7 as age
5711 mandeep.dh 127 
           FROM inventoryItem ii
128 
           JOIN scanNew s
129 
                ON (s.inventoryItemId = ii.id)
8625 amar.kumar 130 
           WHERE (ii.lastScanType IN ('PURCHASE', 'SALE_RET', 'MARKED_GOOD')
131 
           OR ii.currentQuantity>1)
8630 amar.kumar 132 
           AND (transferStatus is NULL or transferStatus != 'IN_TRANSIT')
7802 amar.kumar 133 
           AND ii.physicalWarehouseId !=16
5711 mandeep.dh 134 
           GROUP BY ii.id, ii.itemId, ii.currentQuantity) t
135 
    JOIN purchase p
136 
        ON (p.id = t.purchaseId)
137 
    JOIN lineitem l
138 
        ON (l.purchaseOrder_id = p.purchaseOrder_id AND l.itemId = t.itemId)
139 
    GROUP BY t.itemId
140 
    HAVING SUM(currentQuantity) != 0
141 
  </select>
6322 amar.kumar 142 
 
7089 amar.kumar 143 
  <!--select id="inventoryAgeOnePlus" resultType="detailedInventoryAge">
144 
    SELECT t.itemId, brand, modelName, modelNumber, color, scannedAt, i.warehouseId
145 
        SUM((age &gt;= 1) * currentQuantity) AS onePlusCount,
146 
        SUM((age &gt;= 1) * unitPrice) as onePlusCost,
147 
        l.productGroup as category
148 
    FROM  (SELECT ii.id, ii.itemId, ii.currentQuantity, ii.purchaseId, MIN(s.scannedAt)
149 
                DATEDIFF(now(), MAX(s.scannedAt)) / 7 as age
150 
           FROM inventoryItem ii
151 
           JOIN scanNew s
152 
                ON (s.inventoryItemId = ii.id)
153 
           WHERE ii.lastScanType IN ('PURCHASE', 'SALE_RET')
154 
           GROUP BY ii.id, ii.itemId, ii.currentQuantity) t
155 
    JOIN purchase p
156 
        ON (p.id = t.purchaseId)
157 
    JOIN lineitem l
158 
        ON (l.purchaseOrder_id = p.purchaseOrder_id AND l.itemId = t.itemId)
159 
    GROUP BY t.itemId, i.warehouseId
160 
    HAVING SUM(currentQuantity) != 0
161 
  </select-->
162 
 
163 
 
164 
 
6322 amar.kumar 165 
  <select id = "getScansForItem" parameterType = "map" resultType = "scan">
166 
	  SELECT s.id, s.inventoryItemId, s.warehouseId, s.type, s.scannedAt,
167 
	  s.quantity, s.orderId from scanNew s JOIN inventoryItem i
168 
	  ON
169 
	  s.inventoryItemId = i.id
170 
	  AND
6414 amar.kumar 171 
	  i.itemId = #{itemId}
6322 amar.kumar 172 
	  AND
173 
	  s.scannedAt BETWEEN #{fromDate} AND #{toDate};
174 
  </select>
175 
 
6558 amar.kumar 176 
  <insert id="genericScan" parameterType="scan" useGeneratedKeys="true" keyProperty="id">
6467 amar.kumar 177 
    INSERT INTO scanNew
7410 amar.kumar 178 
     (inventoryItemId, warehouseId, type, scannedAt, quantity, orderId, transferLotId, remarks)
6467 amar.kumar 179 
    VALUES
7410 amar.kumar 180 
      (#{inventoryItemId}, #{warehouseId}, #{type}, NOW(), #{quantity}, #{orderId}, #{transferLotId}, #{remarks})
6467 amar.kumar 181 
  </insert>
182 
 
6558 amar.kumar 183 
  <select id = "getCurrentSerializedInventoryByScans" resultType = "inventoryAvailability">
184 
	SELECT x.itemId, ci.brand, ci.model_name as modelName,
6630 amar.kumar 185 
	ci.model_number as modelNumber, ci.color as color, (x.inx-if(y.outx is null,0,y.outx)) as quantity
6558 amar.kumar 186 
	from
187 
	(select i.itemId, count(*) as inx from scanNew s
188 
		JOIN inventoryItem i ON (i.id = s.inventoryItemId)
189 
		JOIN catalog.item c ON i.itemId = c.id
10407 amar.kumar 190 
		JOIN inventory.warehouse w ON w.id = s.warehouseId
191 
		where s.type in ('PURCHASE','SALE_RET', 'MARKED_GOOD','WAREHOUSE_TRANSFER_IN') and c.type = 'SERIALIZED'
10402 manish.sha 192 
		and w.billingWarehouseId = #{physicalWarehouseId}
6558 amar.kumar 193 
		group by i.itemId ) as x
6630 amar.kumar 194 
	LEFT JOIN
6558 amar.kumar 195 
	(select i.itemId, count(*) as outx from scanNew s
196 
		JOIN inventoryItem i on i.id = s.inventoryItemId
197 
		JOIN catalog.item c ON i.itemId = c.id
10407 amar.kumar 198 
		JOIN inventory.warehouse w ON w.id = s.warehouseId
7678 amar.kumar 199 
		where s.type in ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN','MARKED_USED','MARKED_BAD','WAREHOUSE_TRANSFER_OUT') and c.type = 'SERIALIZED'
10407 amar.kumar 200 
		and w.billingWarehouseId = #{physicalWarehouseId}
6558 amar.kumar 201 
		group by i.itemId) as y
202 
	ON x.itemId = y.itemId
203 
	JOIN catalog.item ci ON ci.id = x.itemId
6630 amar.kumar 204 
	where ci.type = 'SERIALIZED' having quantity>0
6558 amar.kumar 205 
  </select>
6762 amar.kumar 206 
 
207 
<select id = "getHistoricSerializedInventoryByScans" resultType = "inventoryAvailability" parameterType = "date">
208 
	SELECT x.itemId, ci.brand, ci.model_name as modelName,
209 
	ci.model_number as modelNumber, ci.color as color, (x.inx-if(y.outx is null,0,y.outx)) as quantity
210 
	from
211 
	(select i.itemId, count(*) as inx from scanNew s
212 
		JOIN inventoryItem i ON (i.id = s.inventoryItemId)
213 
		JOIN catalog.item c ON i.itemId = c.id
7678 amar.kumar 214 
		where s.type in ('PURCHASE','SALE_RET', 'MARKED_GOOD','WAREHOUSE_TRANSFER_IN') and c.type = 'SERIALIZED'
6762 amar.kumar 215 
		and s.scannedAt &lt; #{date}
216 
		group by i.itemId ) as x
217 
	LEFT JOIN
218 
	(select i.itemId, count(*) as outx from scanNew s
219 
		JOIN inventoryItem i on i.id = s.inventoryItemId
220 
		JOIN catalog.item c ON i.itemId = c.id
7678 amar.kumar 221 
		where s.type in ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN','MARKED_USED','MARKED_BAD','WAREHOUSE_TRANSFER_OUT') and c.type = 'SERIALIZED'
6762 amar.kumar 222 
		and s.scannedAt &lt; #{date}
223 
		group by i.itemId) as y
224 
	ON x.itemId = y.itemId
225 
	JOIN catalog.item ci ON ci.id = x.itemId
226 
	where ci.type = 'SERIALIZED' having quantity>0
227 
  </select>
228 
 
10308 amar.kumar 229 
<select id = "getOurHistoricSerializedInventoryByScans" resultType = "inventoryAvailability" parameterType = "date">
230 
	SELECT x.itemId, ci.brand, ci.model_name as modelName,
231 
	ci.model_number as modelNumber, ci.color as color, (x.inx-if(y.outx is null,0,y.outx)) as quantity
232 
	from
233 
	(select i.itemId, count(*) as inx from scanNew s
234 
		JOIN inventoryItem i ON (i.id = s.inventoryItemId)
235 
		JOIN catalog.item c ON i.itemId = c.id
236 
		where s.type in ('PURCHASE','SALE_RET', 'MARKED_GOOD','WAREHOUSE_TRANSFER_IN') and c.type = 'SERIALIZED'
237 
		and s.scannedAt &lt; #{date}
238 
		and s.warehouseId != 16
239 
		group by i.itemId ) as x
240 
	LEFT JOIN
241 
	(select i.itemId, count(*) as outx from scanNew s
242 
		JOIN inventoryItem i on i.id = s.inventoryItemId
243 
		JOIN catalog.item c ON i.itemId = c.id
244 
		where s.type in ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN','MARKED_USED','MARKED_BAD','WAREHOUSE_TRANSFER_OUT') and c.type = 'SERIALIZED'
245 
		and s.scannedAt &lt; #{date}
246 
		and s.warehouseId != 16
247 
		group by i.itemId) as y
248 
	ON x.itemId = y.itemId
249 
	JOIN catalog.item ci ON ci.id = x.itemId
250 
	where ci.type = 'SERIALIZED' having quantity>0
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">
301 
	SELECT x.itemId, ci.brand, ci.model_name as modelName,
302 
	ci.model_number as modelNumber, ci.color as color, (x.inx-if(y.outx is null,0,y.outx)) as quantity
303 
	from
304 
	(SELECT i.itemId, sum(quantity) as inx from scanNew s
305 
		JOIN inventoryItem i ON (i.id = s.inventoryItemId)
306 
		JOIN catalog.item c ON i.itemId = c.id
307 
		where s.type in ('PURCHASE','SALE_RET', 'MARKED_GOOD','WAREHOUSE_TRANSFER_IN') and c.type = 'NON_SERIALIZED'
308 
		and s.scannedAt &lt; #{date}
309 
		and s.warehouseId != 16
310 
		group by i.itemId ) as x
311 
	LEFT JOIN
312 
	(SELECT i.itemId, sum(quantity) as outx from scanNew s
313 
		JOIN inventoryItem i on i.id = s.inventoryItemId
314 
		JOIN catalog.item c ON i.itemId = c.id
315 
		where s.type in ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN','MARKED_USED','MARKED_BAD','WAREHOUSE_TRANSFER_OUT') and c.type = 'NON_SERIALIZED'
316 
		and s.scannedAt &lt; #{date}
317 
		and s.warehouseId != 16
318 
		group by i.itemId) as y
319 
	ON x.itemId = y.itemId
320 
	JOIN catalog.item ci ON ci.id = x.itemId
321 
	where ci.type = 'NON_SERIALIZED' having quantity>0
322 
</select>
323 
 
6880 amar.kumar 324 
 <select id = "getMovementSerializedInventoryByScans" resultType = "inventoryMovement" parameterType = "map">
7410 amar.kumar 325 
 	SELECT itemId, brand, model_name as modelName, model_number as modelNumber, color, s.type, count(*) as quantity
6762 amar.kumar 326 
 	FROM inventoryItem i JOIN scanNew s ON i.id = s.inventoryItemId
327 
 	LEFT JOIN catalog.item c ON i.itemId = c.id
6963 amar.kumar 328 
 	where s.scannedAt between #{startDate} and #{endDate} and c.type = 'SERIALIZED'
329 
 	and i.currentWarehouseId not in (1,2,6,9)
10308 amar.kumar 330 
 	and s.warehouseId != 16
6762 amar.kumar 331 
 	group by itemId, s.type
332 
 </select>
333 
 
334 
 <select id = "getMovementNonSerializedInventoryByScans" resultType = "inventoryMovement" parameterType = "map">
7410 amar.kumar 335 
 	SELECT itemId, brand, model_name as modelName, model_number as modelNumber, color, s.type, sum(quantity) as quantity
6762 amar.kumar 336 
 	FROM inventoryItem i JOIN scanNew s ON i.id = s.inventoryItemId
337 
 	LEFT JOIN catalog.item c ON i.itemId = c.id
6963 amar.kumar 338 
 	where s.scannedAt between #{startDate} and #{endDate} and c.type = 'NON_SERIALIZED'
10308 amar.kumar 339 
 	and i.currentWarehouseId not in (1,2,6,9)
340 
 	and s.warehouseId != 16
6762 amar.kumar 341 
 	group by itemId, s.type
6880 amar.kumar 342 
 </select>
7216 amar.kumar 343 
 
344 
 <select id = "getCompleteMovementSerializedInventoryByScans" resultType = "inventoryMovement" parameterType = "map">
7410 amar.kumar 345 
 	SELECT itemId, brand, model_name as modelName, model_number as modelNumber, color, s.type, count(*) as quantity
7216 amar.kumar 346 
 	FROM inventoryItem i JOIN scanNew s ON i.id = s.inventoryItemId
347 
 	LEFT JOIN catalog.item c ON i.itemId = c.id
348 
 	where s.scannedAt between #{startDate} and #{endDate} and c.type = 'SERIALIZED'
10308 amar.kumar 349 
 	and s.warehouseId != 16
7216 amar.kumar 350 
 	group by itemId, s.type
351 
 </select>
352 
 
353 
 <select id = "getCompleteMovementNonSerializedInventoryByScans" resultType = "inventoryMovement" parameterType = "map">
7410 amar.kumar 354 
 	SELECT itemId, brand, model_name as modelName, model_number as modelNumber, color, s.type, sum(quantity) as quantity
7216 amar.kumar 355 
 	FROM inventoryItem i JOIN scanNew s ON i.id = s.inventoryItemId
356 
 	LEFT JOIN catalog.item c ON i.itemId = c.id
357 
 	where s.scannedAt between #{startDate} and #{endDate} and c.type = 'NON_SERIALIZED'
10308 amar.kumar 358 
 	and s.warehouseId != 16
7216 amar.kumar 359 
 	group by itemId, s.type
360 
 </select>
6630 amar.kumar 361 
 
7410 amar.kumar 362 
<select id = "getTransferrableWarehousesFromWarehouse" resultType = "java.lang.Long" parameterType = "java.lang.Long">
363 
 	SELECT toWarehouseId
364 
 	FROM possibleWarehouseMovement
365 
 	WHERE fromWarehouseId = #{warehouseId}
366 
 </select>
367 
 
7957 amar.kumar 368 
<select id = "getHistoricBadInventoryByScans" resultType = "inventoryAvailability" parameterType = "date">
369 
	SELECT x.itemId, ci.brand, ci.model_name as modelName, ci.model_number as modelNumber,
370 
	ci.color as color, (x.inx-if(y.outx is null,0,y.outx)) as quantity
371 
	FROM
372 
		(SELECT i.itemId, count(*) as inx FROM scanNew s JOIN inventoryItem i ON
373 
		(i.id = s.inventoryItemId) JOIN catalog.item c ON i.itemId = c.id
10407 amar.kumar 374 
		where s.type in ('DOA_IN', 'SALE_RET_UNUSABLE') and
10489 amar.kumar 375 
		s.scannedAt &lt; #{date} and s.scannedAt &gt; '2013-01-01' group by i.itemId ) as x
7957 amar.kumar 376 
		LEFT JOIN
377 
		(SELECT i.itemId, count(*) as outx FROM scanNew s JOIN inventoryItem i ON
378 
		i.id = s.inventoryItemId JOIN catalog.item c ON i.itemId = c.id WHERE
10489 amar.kumar 379 
		s.type in ('DOA_OUT') and s.scannedAt &lt; #{date} and s.scannedAt &gt; '2013-01-01'
380 
		group by i.itemId) as y
7957 amar.kumar 381 
		ON x.itemId = y.itemId
382 
		JOIN catalog.item ci ON ci.id = x.itemId
383 
	having quantity>0;
384 
</select>
7410 amar.kumar 385 
 
386 
<select id = "getScansForTransferLot" resultType = "scan" parameterType = "java.lang.Long">
387 
	SELECT *
388 
	FROM scanNew
389 
	WHERE transferLotId = #{id}
390 
	AND type = 'WAREHOUSE_TRANSFER_OUT'
391 
</select>
10120 manish.sha 392 
 
393 
<select id= "getScansForTransferLotAndInventoryItem" resultType = "scan" parameterType="map" >
394 
	SELECT *
395 
	FROM scanNew
396 
	WHERE transferLotId = #{transferLotId}
397 
	AND inventoryItemId = #{inventoryItemId}
398 
	AND type = 'WAREHOUSE_TRANSFER_OUT'
399 
	LIMIT 1
400 
</select>
10689 manish.sha 401 
 
402 
<select id="getAmazonTransferredSkuDetails" resultType= "amazonTransferredSkuDetail" parameterType="map">
403 
	SELECT Y.itemId, Y.purchaseId, Y.purchaseDate, sum(Y.quantity) as quantity, Y.unitPrice , Y.brand, Y.modelName, Y.modelNumber, Y.category, Y.color
404 
	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
405 
	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
406 
	FROM scanNew s
407 
	JOIN inventoryItem i on s.inventoryItemId= i.id
408 
	JOIN transferLot t on (s.transferLotId= t.id)
409 
	JOIN purchase p on p.id= i.purchaseId
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>
416 
	AND t.destinationWarehouseId= 16
417 
	AND t.status in ('TRANSFER_COMPLETE','IN_TRANSIT','PARTIAL_TRANSFER')
418 
	AND i.transferStatus is not null AND type='WAREHOUSE_TRANSFER_OUT'
419 
	group by i.itemId, s.transferLotId, i.purchaseId, i.id
420 
	order by s.scannedAt desc) as X group by inventoryItemId) as Y group by Y.itemId, Y.purchaseId
10808 manish.sha 421 
	order by Y.purchaseId desc
10689 manish.sha 422 
</select>
423 
 
424 
<select id="getScansforPurchase" resultType="scan" parameterType="map" >
425 
	SELECT s.id, s.inventoryItemId, s.warehouseId, s.type, s.scannedAt, s.quantity, s.orderId, s.remarks, s.transferLotId
426 
	FROM scanNew s
427 
	JOIN inventoryItem i on s.inventoryItemId = i.id
428 
	WHERE i.purchaseId= #{purchaseId} and s.type=#{scanType}
429 
</select>
7410 amar.kumar 430 
 
4500 mandeep.dh 431 
</mapper>