Subversion Repositories SmartDukaan

Rev

Rev 13090 | Rev 13529 | 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
13090 manish.sha 217 
	(select i.itemId, sum(s.quantity) as inx from scanNew s
6762 amar.kumar 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
13090 manish.sha 224 
	(select i.itemId, sum(s.quantity) as outx from scanNew s
6762 amar.kumar 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
13090 manish.sha 239 
	(select i.itemId, sum(s.quantity) as inx from scanNew s
10308 amar.kumar 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
13090 manish.sha 248 
	(select i.itemId, sum(s.quantity) as outx from scanNew s
10308 amar.kumar 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
13080 manish.sha 360 
 	where s.scannedAt between #{startDate} and #{endDate} and c.type = 'SERIALIZED'
7216 amar.kumar 361 
 	group by itemId, s.type
362 
 </select>
363 
 
364 
 <select id = "getCompleteMovementNonSerializedInventoryByScans" resultType = "inventoryMovement" parameterType = "map">
7410 amar.kumar 365 
 	SELECT itemId, brand, model_name as modelName, model_number as modelNumber, color, s.type, sum(quantity) as quantity
7216 amar.kumar 366 
 	FROM inventoryItem i JOIN scanNew s ON i.id = s.inventoryItemId
367 
 	LEFT JOIN catalog.item c ON i.itemId = c.id
13080 manish.sha 368 
 	where s.scannedAt between #{startDate} and #{endDate} and c.type = 'NON_SERIALIZED'
7216 amar.kumar 369 
 	group by itemId, s.type
370 
 </select>
6630 amar.kumar 371 
 
7410 amar.kumar 372 
<select id = "getTransferrableWarehousesFromWarehouse" resultType = "java.lang.Long" parameterType = "java.lang.Long">
373 
 	SELECT toWarehouseId
374 
 	FROM possibleWarehouseMovement
375 
 	WHERE fromWarehouseId = #{warehouseId}
376 
 </select>
377 
 
7957 amar.kumar 378 
<select id = "getHistoricBadInventoryByScans" resultType = "inventoryAvailability" parameterType = "date">
13504 manish.sha 379 
	SELECT badX.id as itemId, badX.brand, badX.model_name as modelName, badX.model_number as modelNumber, badX.color, sum(badX.Bad_quantity) as quantity
380 
	FROM (SELECT x.id, x.label as category,x.product_group,x.brand, x.model_name,x.model_number, x.color, (x.quantity-if(y.quantity is null,0,y.quantity)) as Bad_quantity,
381 
	x.physicalWarehouseId,x.purchaseId
382 
	FROM (SELECT it.id, ct.label, it.product_group,it.brand, it.model_name,it.model_number, it.color,
383 
	sum(s.quantity) as quantity, i.physicalWarehouseId,i.purchaseId FROM scanNew s
384 
	JOIN inventoryItem i on s.inventoryItemId = i.id JOIN purchase p on i.purchaseId= p.id
385 
	JOIN catalog.item it on i.itemId= it.id JOIN catalog.category ct on it.category = ct.id
386 
	WHERE s.type in ('MARKED_BAD','DOA_IN','SALE_RET_UNUSABLE','DOA_REJECTED')
387 
	AND s.scannedAt &gt; '2013-01-01' AND s.scannedAt &lt; #{date}
388 
	AND i.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))
389 
	group by i.itemId,i.purchaseId) as x
390 
	LEFT OUTER JOIN (SELECT it.id, ct.label, it.product_group,it.brand, it.model_name,it.model_number,
391 
	it.color, sum(s.quantity) as quantity, i.physicalWarehouseId, i.purchaseId
392 
	FROM scanNew s JOIN inventoryItem i on s.inventoryItemId = i.id JOIN purchase p on i.purchaseId= p.id
393 
	JOIN catalog.item it on i.itemId= it.id JOIN catalog.category ct on it.category = ct.id
394 
	WHERE s.type in ('DOA_OUT','BAD_SALE','MARKED_GOOD','BAD_PURCHASE_RETURN')
395 
	AND s.scannedAt &gt; '2013-01-01' AND s.scannedAt &lt; #{date}
396 
	AND i.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))
397 
	group by i.itemId,i.purchaseId) as y
398 
	on x.id=y.id AND x.purchaseId=y.purchaseId
399 
	group by x.id,x.purchaseId having Bad_quantity>0)
400 
	as badX group by badX.id
7957 amar.kumar 401 
</select>
7410 amar.kumar 402 
 
403 
<select id = "getScansForTransferLot" resultType = "scan" parameterType = "java.lang.Long">
404 
	SELECT *
405 
	FROM scanNew
406 
	WHERE transferLotId = #{id}
407 
	AND type = 'WAREHOUSE_TRANSFER_OUT'
408 
</select>
10120 manish.sha 409 
 
410 
<select id= "getScansForTransferLotAndInventoryItem" resultType = "scan" parameterType="map" >
411 
	SELECT *
412 
	FROM scanNew
413 
	WHERE transferLotId = #{transferLotId}
414 
	AND inventoryItemId = #{inventoryItemId}
415 
	AND type = 'WAREHOUSE_TRANSFER_OUT'
416 
	LIMIT 1
417 
</select>
10689 manish.sha 418 
 
419 
<select id="getAmazonTransferredSkuDetails" resultType= "amazonTransferredSkuDetail" parameterType="map">
12357 manish.sha 420 
	SELECT Z.itemId, Z.purchaseId, Z.purchaseDate, sum(Z.quantity) as quantity, Z.unitPrice, Z.nlc,
421 
	Z.brand, Z.modelName, Z.modelNumber, Z.category, Z.color, Z.taxType
422 
	FROM  (SELECT X.itemId, X.purchaseId, X.purchaseDate, X.quantity, X.unitPrice, X.nlc, X.brand,
423 
	X.modelName, X.modelNumber, X.category, X.color , X.inventoryItemId, X.scanTime, X.taxType
424 
	FROM (SELECT i.itemId as itemId, i.purchaseId as purchaseId, p.receivedOn as purchaseDate,
425 
	s.quantity as quantity, l.unitPrice as unitPrice, l.nlc as nlc, l.brand as brand,
426 
	l.modelName as modelName, l.modelNumber as modelNumber, l.productGroup as category,
427 
	l.color as color , i.id as inventoryItemId, s.scannedAt as scanTime, CASE po.taxType
428 
	WHEN 0 THEN 'VAT' WHEN 1 THEN 'CST' WHEN 2 THEN 'CFORM' END as taxType FROM scanNew s
429 
	JOIN inventoryItem i on s.inventoryItemId= i.id JOIN transferLot t on (s.transferLotId= t.id)
430 
	JOIN purchase p on p.id= i.purchaseId JOIN purchaseorder po on p.purchaseOrder_id = po.id
10689 manish.sha 431 
	JOIN lineitem l on (l.purchaseOrder_id= p.purchaseOrder_id and l.itemId= i.itemId)
432 
	WHERE i.itemId in
433 
	<foreach item="itemId" index="index" collection="itemIds"
434 
        open="(" separator="," close=")">
435 
	 #{itemId}
436 
	</foreach>
12357 manish.sha 437 
	and t.destinationWarehouseId in (select id from inventory.warehouse where vendor_id= 40
438 
	and inventoryType = 'GOOD' and warehouseType ='OURS_THIRDPARTY' and source = 3)
10689 manish.sha 439 
	AND t.status in ('TRANSFER_COMPLETE','IN_TRANSIT','PARTIAL_TRANSFER')
12658 manish.sha 440 
	AND i.transferStatus is not null AND s.type='WAREHOUSE_TRANSFER_OUT'
12357 manish.sha 441 
	GROUP BY i.itemId, s.transferLotId, i.purchaseId, i.id order by s.scannedAt desc)
442 
	as X GROUP BY X.inventoryItemId
443 
	UNION SELECT Y.itemId, Y.purchaseId, Y.purchaseDate, Y.quantity, Y.unitPrice, Y.nlc, Y.brand,
444 
	Y.modelName, Y.modelNumber, Y.category, Y.color , Y.inventoryItemId, Y.scanTime, Y.taxType
445 
	FROM (SELECT i.itemId as itemId, i.purchaseId as purchaseId, p.receivedOn as purchaseDate,
446 
	s.quantity as quantity, l.unitPrice as unitPrice, l.nlc as nlc, l.brand as brand, l.modelName,
447 
	l.modelNumber as modelNumber, l.productGroup as category, l.color as color,
448 
	i.id as inventoryItemId, s.scannedAt as scanTime, CASE po.taxType
449 
	WHEN 0 THEN 'VAT' WHEN 1 THEN 'CST' WHEN 2 THEN 'CFORM' END as taxType FROM scanNew s
450 
	JOIN inventoryItem i on s.inventoryItemId = i.id JOIN purchase p on p.id= i.purchaseId
451 
	JOIN purchaseorder po on p.purchaseOrder_id = po.id
452 
	JOIN lineitem l on (l.purchaseOrder_id= p.purchaseOrder_id and l.itemId= i.itemId)
453 
	WHERE i.itemId in
454 
	<foreach item="itemId" index="index" collection="itemIds"
455 
        open="(" separator="," close=")">
456 
	 #{itemId}
457 
	</foreach>
458 
	and po.warehouseId in (select id from inventory.warehouse where vendor_id= 40
459 
	and inventoryType = 'GOOD' and warehouseType ='OURS_THIRDPARTY' and source = 3)
460 
	GROUP BY i.itemId, i.purchaseId, i.id ORDER BY s.scannedAt desc) as Y
461 
	GROUP BY Y.inventoryItemId) as Z GROUP BY Z.itemId, Z.purchaseId order by Z.purchaseId desc;
10689 manish.sha 462 
</select>
463 
 
464 
<select id="getScansforPurchase" resultType="scan" parameterType="map" >
465 
	SELECT s.id, s.inventoryItemId, s.warehouseId, s.type, s.scannedAt, s.quantity, s.orderId, s.remarks, s.transferLotId
466 
	FROM scanNew s
467 
	JOIN inventoryItem i on s.inventoryItemId = i.id
468 
	WHERE i.purchaseId= #{purchaseId} and s.type=#{scanType}
469 
</select>
12800 manish.sha 470 
 
471 
<select id="getInventoryAgeConsideredItems" resultType="invAgeConsiderItems" parameterType = "java.lang.Long">
472 
	SELECT ii.id, ii.itemId, ii.currentQuantity, ii.purchaseId, DATEDIFF(now(), MIN(s.scannedAt)) / 7 as age,
12844 manish.sha 473 
	l.productGroup as category, l.unitPrice as cost
12800 manish.sha 474 
	FROM inventoryItem ii JOIN scanNew s ON (s.inventoryItemId = ii.id) JOIN purchase p ON (p.id = ii.purchaseId)
475 
	JOIN lineitem l ON (l.purchaseOrder_id = p.purchaseOrder_id AND l.itemId = ii.itemId)
476 
	WHERE ((ii.lastScanType IN ('PURCHASE', 'SALE_RET', 'MARKED_GOOD')) OR (ii.currentQuantity>0) OR (ii.lastScanType IN ('SALE') and ii.currentQuantity = 1))
477 
	AND ii.lastScanType != 'MARKED_BAD' AND (transferStatus is NULL or transferStatus != 'IN_TRANSIT') AND ii.itemId = #{itemId}
478 
	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))
479 
	GROUP BY ii.id, ii.itemId, ii.currentQuantity order by ii.id desc;
480 
</select>
481 
 
4500 mandeep.dh 482 
</mapper>