Subversion Repositories SmartDukaan

Rev

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