Subversion Repositories SmartDukaan

Rev

Rev 12357 | Rev 12591 | 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
12543 manish.sha 138 
	and inventoryType = 'GOOD' and warehouseType ='OURS_THIRDPARTY' and source in (3,7,8))
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}
12543 manish.sha 243 
		and s.warehouseId not in (select id from inventory.warehouse where vendor_id= 40
244 
	    and inventoryType = 'GOOD' and warehouseType ='OURS_THIRDPARTY' and source in (3,7,8))
10308 amar.kumar 245 
		group by i.itemId ) as x
246 
	LEFT JOIN
247 
	(select i.itemId, count(*) as outx from scanNew s
248 
		JOIN inventoryItem i on i.id = s.inventoryItemId
249 
		JOIN catalog.item c ON i.itemId = c.id
250 
		where s.type in ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN','MARKED_USED','MARKED_BAD','WAREHOUSE_TRANSFER_OUT') and c.type = 'SERIALIZED'
251 
		and s.scannedAt &lt; #{date}
12543 manish.sha 252 
		and s.warehouseId not in (select id from inventory.warehouse where vendor_id= 40
253 
	    and inventoryType = 'GOOD' and warehouseType ='OURS_THIRDPARTY' and source in (3,7,8))
10308 amar.kumar 254 
		group by i.itemId) as y
255 
	ON x.itemId = y.itemId
256 
	JOIN catalog.item ci ON ci.id = x.itemId
257 
	where ci.type = 'SERIALIZED' having quantity>0
258 
</select>
259 
 
6558 amar.kumar 260 
 
6630 amar.kumar 261 
  <select id = "getCurrentNonSerializedInventoryByScans" resultType = "inventoryAvailability">
262 
	SELECT x.itemId, ci.brand, ci.model_name as modelName,
263 
	ci.model_number as modelNumber, ci.color as color, (x.inx-if(y.outx is null,0,y.outx)) as quantity
264 
	from
265 
	(select i.itemId, sum(quantity) as inx from scanNew s
266 
		JOIN inventoryItem i ON (i.id = s.inventoryItemId)
267 
		JOIN catalog.item c ON i.itemId = c.id
8603 amar.kumar 268 
		JOIN inventory.warehouse w ON w.id = s.warehouseId
7678 amar.kumar 269 
		where s.type in ('PURCHASE','SALE_RET', 'MARKED_GOOD','WAREHOUSE_TRANSFER_IN') and c.type = 'NON_SERIALIZED'
8603 amar.kumar 270 
		and w.billingWarehouseId = #{physicalWarehouseId}
6630 amar.kumar 271 
		group by i.itemId ) as x
272 
	LEFT JOIN
273 
	(select i.itemId, sum(quantity) as outx from scanNew s
274 
		JOIN inventoryItem i on i.id = s.inventoryItemId
275 
		JOIN catalog.item c ON i.itemId = c.id
8603 amar.kumar 276 
		JOIN inventory.warehouse w ON w.id = s.warehouseId
7678 amar.kumar 277 
		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 278 
		and w.billingWarehouseId = #{physicalWarehouseId}
6630 amar.kumar 279 
		group by i.itemId) as y
280 
	ON x.itemId = y.itemId
281 
	JOIN catalog.item ci ON ci.id = x.itemId
282 
	where ci.type = 'NON_SERIALIZED' having quantity>0
283 
  </select>
6762 amar.kumar 284 
 
285 
<select id = "getHistoricNonSerializedInventoryByScans" resultType = "inventoryAvailability" parameterType = "date">
286 
	SELECT x.itemId, ci.brand, ci.model_name as modelName,
287 
	ci.model_number as modelNumber, ci.color as color, (x.inx-if(y.outx is null,0,y.outx)) as quantity
288 
	from
7410 amar.kumar 289 
	(SELECT i.itemId, sum(quantity) as inx 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 ('PURCHASE','SALE_RET', 'MARKED_GOOD','WAREHOUSE_TRANSFER_IN') and c.type = 'NON_SERIALIZED'
6762 amar.kumar 293 
		and s.scannedAt &lt; #{date}
294 
		group by i.itemId ) as x
295 
	LEFT JOIN
7410 amar.kumar 296 
	(SELECT i.itemId, sum(quantity) as outx from scanNew s
6762 amar.kumar 297 
		JOIN inventoryItem i on i.id = s.inventoryItemId
298 
		JOIN catalog.item c ON i.itemId = c.id
7678 amar.kumar 299 
		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 300 
		and s.scannedAt &lt; #{date}
301 
		group by i.itemId) as y
302 
	ON x.itemId = y.itemId
303 
	JOIN catalog.item ci ON ci.id = x.itemId
304 
	where ci.type = 'NON_SERIALIZED' having quantity>0
305 
  </select>
306 
 
10308 amar.kumar 307 
<select id = "getOurHistoricNonSerializedInventoryByScans" resultType = "inventoryAvailability" parameterType = "date">
308 
	SELECT x.itemId, ci.brand, ci.model_name as modelName,
309 
	ci.model_number as modelNumber, ci.color as color, (x.inx-if(y.outx is null,0,y.outx)) as quantity
310 
	from
311 
	(SELECT i.itemId, sum(quantity) as inx from scanNew s
312 
		JOIN inventoryItem i ON (i.id = s.inventoryItemId)
313 
		JOIN catalog.item c ON i.itemId = c.id
314 
		where s.type in ('PURCHASE','SALE_RET', 'MARKED_GOOD','WAREHOUSE_TRANSFER_IN') and c.type = 'NON_SERIALIZED'
315 
		and s.scannedAt &lt; #{date}
12543 manish.sha 316 
		and s.warehouseId not in (select id from inventory.warehouse where vendor_id= 40
317 
	    and inventoryType = 'GOOD' and warehouseType ='OURS_THIRDPARTY' and source in (3,7,8))
10308 amar.kumar 318 
		group by i.itemId ) as x
319 
	LEFT JOIN
320 
	(SELECT i.itemId, sum(quantity) as outx from scanNew s
321 
		JOIN inventoryItem i on i.id = s.inventoryItemId
322 
		JOIN catalog.item c ON i.itemId = c.id
323 
		where s.type in ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN','MARKED_USED','MARKED_BAD','WAREHOUSE_TRANSFER_OUT') and c.type = 'NON_SERIALIZED'
324 
		and s.scannedAt &lt; #{date}
12543 manish.sha 325 
		and s.warehouseId not in (select id from inventory.warehouse where vendor_id= 40
326 
	    and inventoryType = 'GOOD' and warehouseType ='OURS_THIRDPARTY' and source in (3,7,8))
10308 amar.kumar 327 
		group by i.itemId) as y
328 
	ON x.itemId = y.itemId
329 
	JOIN catalog.item ci ON ci.id = x.itemId
330 
	where ci.type = 'NON_SERIALIZED' having quantity>0
331 
</select>
332 
 
6880 amar.kumar 333 
 <select id = "getMovementSerializedInventoryByScans" resultType = "inventoryMovement" parameterType = "map">
7410 amar.kumar 334 
 	SELECT itemId, brand, model_name as modelName, model_number as modelNumber, color, s.type, count(*) as quantity
6762 amar.kumar 335 
 	FROM inventoryItem i JOIN scanNew s ON i.id = s.inventoryItemId
336 
 	LEFT JOIN catalog.item c ON i.itemId = c.id
6963 amar.kumar 337 
 	where s.scannedAt between #{startDate} and #{endDate} and c.type = 'SERIALIZED'
338 
 	and i.currentWarehouseId not in (1,2,6,9)
12357 manish.sha 339 
 	and s.warehouseId not in (select id from inventory.warehouse where vendor_id= 40
12543 manish.sha 340 
	and inventoryType = 'GOOD' and warehouseType ='OURS_THIRDPARTY' and source in (3,7,8))
6762 amar.kumar 341 
 	group by itemId, s.type
342 
 </select>
343 
 
344 
 <select id = "getMovementNonSerializedInventoryByScans" resultType = "inventoryMovement" parameterType = "map">
7410 amar.kumar 345 
 	SELECT itemId, brand, model_name as modelName, model_number as modelNumber, color, s.type, sum(quantity) as quantity
6762 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
6963 amar.kumar 348 
 	where s.scannedAt between #{startDate} and #{endDate} and c.type = 'NON_SERIALIZED'
10308 amar.kumar 349 
 	and i.currentWarehouseId not in (1,2,6,9)
12357 manish.sha 350 
 	and s.warehouseId not in (select id from inventory.warehouse where vendor_id= 40
12543 manish.sha 351 
	and inventoryType = 'GOOD' and warehouseType ='OURS_THIRDPARTY' and source in (3,7,8))
6762 amar.kumar 352 
 	group by itemId, s.type
6880 amar.kumar 353 
 </select>
7216 amar.kumar 354 
 
355 
 <select id = "getCompleteMovementSerializedInventoryByScans" resultType = "inventoryMovement" parameterType = "map">
7410 amar.kumar 356 
 	SELECT itemId, brand, model_name as modelName, model_number as modelNumber, color, s.type, count(*) as quantity
7216 amar.kumar 357 
 	FROM inventoryItem i JOIN scanNew s ON i.id = s.inventoryItemId
358 
 	LEFT JOIN catalog.item c ON i.itemId = c.id
359 
 	where s.scannedAt between #{startDate} and #{endDate} and c.type = 'SERIALIZED'
12357 manish.sha 360 
 	and s.warehouseId not in (select id from inventory.warehouse where vendor_id= 40
361 
	and inventoryType = 'GOOD' and warehouseType ='OURS_THIRDPARTY' and source = 3)
7216 amar.kumar 362 
 	group by itemId, s.type
363 
 </select>
364 
 
365 
 <select id = "getCompleteMovementNonSerializedInventoryByScans" resultType = "inventoryMovement" parameterType = "map">
7410 amar.kumar 366 
 	SELECT itemId, brand, model_name as modelName, model_number as modelNumber, color, s.type, sum(quantity) as quantity
7216 amar.kumar 367 
 	FROM inventoryItem i JOIN scanNew s ON i.id = s.inventoryItemId
368 
 	LEFT JOIN catalog.item c ON i.itemId = c.id
369 
 	where s.scannedAt between #{startDate} and #{endDate} and c.type = 'NON_SERIALIZED'
12357 manish.sha 370 
 	and s.warehouseId not in (select id from inventory.warehouse where vendor_id= 40
371 
	and inventoryType = 'GOOD' and warehouseType ='OURS_THIRDPARTY' and source = 3)
7216 amar.kumar 372 
 	group by itemId, s.type
373 
 </select>
6630 amar.kumar 374 
 
7410 amar.kumar 375 
<select id = "getTransferrableWarehousesFromWarehouse" resultType = "java.lang.Long" parameterType = "java.lang.Long">
376 
 	SELECT toWarehouseId
377 
 	FROM possibleWarehouseMovement
378 
 	WHERE fromWarehouseId = #{warehouseId}
379 
 </select>
380 
 
7957 amar.kumar 381 
<select id = "getHistoricBadInventoryByScans" resultType = "inventoryAvailability" parameterType = "date">
382 
	SELECT x.itemId, ci.brand, ci.model_name as modelName, ci.model_number as modelNumber,
383 
	ci.color as color, (x.inx-if(y.outx is null,0,y.outx)) as quantity
384 
	FROM
385 
		(SELECT i.itemId, count(*) as inx FROM scanNew s JOIN inventoryItem i ON
386 
		(i.id = s.inventoryItemId) JOIN catalog.item c ON i.itemId = c.id
10407 amar.kumar 387 
		where s.type in ('DOA_IN', 'SALE_RET_UNUSABLE') and
10489 amar.kumar 388 
		s.scannedAt &lt; #{date} and s.scannedAt &gt; '2013-01-01' group by i.itemId ) as x
7957 amar.kumar 389 
		LEFT JOIN
390 
		(SELECT i.itemId, count(*) as outx FROM scanNew s JOIN inventoryItem i ON
391 
		i.id = s.inventoryItemId JOIN catalog.item c ON i.itemId = c.id WHERE
10489 amar.kumar 392 
		s.type in ('DOA_OUT') and s.scannedAt &lt; #{date} and s.scannedAt &gt; '2013-01-01'
393 
		group by i.itemId) as y
7957 amar.kumar 394 
		ON x.itemId = y.itemId
395 
		JOIN catalog.item ci ON ci.id = x.itemId
396 
	having quantity>0;
397 
</select>
7410 amar.kumar 398 
 
399 
<select id = "getScansForTransferLot" resultType = "scan" parameterType = "java.lang.Long">
400 
	SELECT *
401 
	FROM scanNew
402 
	WHERE transferLotId = #{id}
403 
	AND type = 'WAREHOUSE_TRANSFER_OUT'
404 
</select>
10120 manish.sha 405 
 
406 
<select id= "getScansForTransferLotAndInventoryItem" resultType = "scan" parameterType="map" >
407 
	SELECT *
408 
	FROM scanNew
409 
	WHERE transferLotId = #{transferLotId}
410 
	AND inventoryItemId = #{inventoryItemId}
411 
	AND type = 'WAREHOUSE_TRANSFER_OUT'
412 
	LIMIT 1
413 
</select>
10689 manish.sha 414 
 
415 
<select id="getAmazonTransferredSkuDetails" resultType= "amazonTransferredSkuDetail" parameterType="map">
12357 manish.sha 416 
	SELECT Z.itemId, Z.purchaseId, Z.purchaseDate, sum(Z.quantity) as quantity, Z.unitPrice, Z.nlc,
417 
	Z.brand, Z.modelName, Z.modelNumber, Z.category, Z.color, Z.taxType
418 
	FROM  (SELECT X.itemId, X.purchaseId, X.purchaseDate, X.quantity, X.unitPrice, X.nlc, X.brand,
419 
	X.modelName, X.modelNumber, X.category, X.color , X.inventoryItemId, X.scanTime, X.taxType
420 
	FROM (SELECT i.itemId as itemId, i.purchaseId as purchaseId, p.receivedOn as purchaseDate,
421 
	s.quantity as quantity, l.unitPrice as unitPrice, l.nlc as nlc, l.brand as brand,
422 
	l.modelName as modelName, l.modelNumber as modelNumber, l.productGroup as category,
423 
	l.color as color , i.id as inventoryItemId, s.scannedAt as scanTime, CASE po.taxType
424 
	WHEN 0 THEN 'VAT' WHEN 1 THEN 'CST' WHEN 2 THEN 'CFORM' END as taxType FROM scanNew s
425 
	JOIN inventoryItem i on s.inventoryItemId= i.id JOIN transferLot t on (s.transferLotId= t.id)
426 
	JOIN purchase p on p.id= i.purchaseId JOIN purchaseorder po on p.purchaseOrder_id = po.id
10689 manish.sha 427 
	JOIN lineitem l on (l.purchaseOrder_id= p.purchaseOrder_id and l.itemId= i.itemId)
428 
	WHERE i.itemId in
429 
	<foreach item="itemId" index="index" collection="itemIds"
430 
        open="(" separator="," close=")">
431 
	 #{itemId}
432 
	</foreach>
12357 manish.sha 433 
	and t.destinationWarehouseId in (select id from inventory.warehouse where vendor_id= 40
434 
	and inventoryType = 'GOOD' and warehouseType ='OURS_THIRDPARTY' and source = 3)
10689 manish.sha 435 
	AND t.status in ('TRANSFER_COMPLETE','IN_TRANSIT','PARTIAL_TRANSFER')
436 
	AND i.transferStatus is not null AND type='WAREHOUSE_TRANSFER_OUT'
12357 manish.sha 437 
	GROUP BY i.itemId, s.transferLotId, i.purchaseId, i.id order by s.scannedAt desc)
438 
	as X GROUP BY X.inventoryItemId
439 
	UNION SELECT Y.itemId, Y.purchaseId, Y.purchaseDate, Y.quantity, Y.unitPrice, Y.nlc, Y.brand,
440 
	Y.modelName, Y.modelNumber, Y.category, Y.color , Y.inventoryItemId, Y.scanTime, Y.taxType
441 
	FROM (SELECT i.itemId as itemId, i.purchaseId as purchaseId, p.receivedOn as purchaseDate,
442 
	s.quantity as quantity, l.unitPrice as unitPrice, l.nlc as nlc, l.brand as brand, l.modelName,
443 
	l.modelNumber as modelNumber, l.productGroup as category, l.color as color,
444 
	i.id as inventoryItemId, s.scannedAt as scanTime, CASE po.taxType
445 
	WHEN 0 THEN 'VAT' WHEN 1 THEN 'CST' WHEN 2 THEN 'CFORM' END as taxType FROM scanNew s
446 
	JOIN inventoryItem i on s.inventoryItemId = i.id JOIN purchase p on p.id= i.purchaseId
447 
	JOIN purchaseorder po on p.purchaseOrder_id = po.id
448 
	JOIN lineitem l on (l.purchaseOrder_id= p.purchaseOrder_id and l.itemId= i.itemId)
449 
	WHERE i.itemId in
450 
	<foreach item="itemId" index="index" collection="itemIds"
451 
        open="(" separator="," close=")">
452 
	 #{itemId}
453 
	</foreach>
454 
	and po.warehouseId in (select id from inventory.warehouse where vendor_id= 40
455 
	and inventoryType = 'GOOD' and warehouseType ='OURS_THIRDPARTY' and source = 3)
456 
	GROUP BY i.itemId, i.purchaseId, i.id ORDER BY s.scannedAt desc) as Y
457 
	GROUP BY Y.inventoryItemId) as Z GROUP BY Z.itemId, Z.purchaseId order by Z.purchaseId desc;
10689 manish.sha 458 
</select>
459 
 
460 
<select id="getScansforPurchase" resultType="scan" parameterType="map" >
461 
	SELECT s.id, s.inventoryItemId, s.warehouseId, s.type, s.scannedAt, s.quantity, s.orderId, s.remarks, s.transferLotId
462 
	FROM scanNew s
463 
	JOIN inventoryItem i on s.inventoryItemId = i.id
464 
	WHERE i.purchaseId= #{purchaseId} and s.type=#{scanType}
465 
</select>
7410 amar.kumar 466 
 
4500 mandeep.dh 467 
</mapper>