Subversion Repositories SmartDukaan

Rev

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