Subversion Repositories SmartDukaan

Rev

Rev 10402 | Rev 10489 | 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
7957 amar.kumar 375 
		s.scannedAt &lt; #{date} group by i.itemId ) as x
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
10407 amar.kumar 379 
		s.type in ('DOA_OUT') and s.scannedAt &lt; #{date} group by i.itemId) as y
7957 amar.kumar 380 
		ON x.itemId = y.itemId
381 
		JOIN catalog.item ci ON ci.id = x.itemId
382 
	having quantity>0;
383 
</select>
7410 amar.kumar 384 
 
385 
<select id = "getScansForTransferLot" resultType = "scan" parameterType = "java.lang.Long">
386 
	SELECT *
387 
	FROM scanNew
388 
	WHERE transferLotId = #{id}
389 
	AND type = 'WAREHOUSE_TRANSFER_OUT'
390 
</select>
10120 manish.sha 391 
 
392 
<select id= "getScansForTransferLotAndInventoryItem" resultType = "scan" parameterType="map" >
393 
	SELECT *
394 
	FROM scanNew
395 
	WHERE transferLotId = #{transferLotId}
396 
	AND inventoryItemId = #{inventoryItemId}
397 
	AND type = 'WAREHOUSE_TRANSFER_OUT'
398 
	LIMIT 1
399 
</select>
7410 amar.kumar 400 
 
4500 mandeep.dh 401 
</mapper>