Subversion Repositories SmartDukaan

Rev

Rev 7216 | Rev 7410 | 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
12 
      (inventoryItemId, warehouseId, type, scannedAt, quantity, orderId)
13 
    VALUES
14 
      (#{inventoryItemId}, #{warehouseId}, #{type}, NOW(), #{quantity}, #{orderId})
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,
6361 rajveer 49 
        brand, modelName, modelNumber, color, unitPrice, SUM(initialQuantity) AS quantity, 1000 * UNIX_TIMESTAMP(receivedOn) as purchasedAt, p.id as purchaseId
5372 mandeep.dh 50 
    FROM purchase p
51 
    JOIN purchaseorder po
52 
        ON (po.id = p.purchaseOrder_id)
53 
    JOIN inventoryItem i
54 
        ON (p.id = i.purchaseId)
55 
    JOIN lineitem l
56 
        ON (l.itemId = i.itemId AND l.purchaseOrder_id = po.id)
57 
    JOIN supplier s
58 
        ON (s.id = po.supplierId)
6494 amar.kumar 59 
    JOIN invoice inv
60 
    	ON (inv.invoiceNumber = p.invoiceNumber)
5372 mandeep.dh 61 
    WHERE po.createdAt BETWEEN #{startDate} AND #{endDate}
6364 rajveer 62 
    AND lastScanType != 'DOA_REPLACED'
6362 rajveer 63 
    GROUP BY p.purchaseOrder_id, p.id, i.itemId
64 
    ORDER BY p.purchaseOrder_id, p.id, i.itemId
5372 mandeep.dh 65 
  </select>
5496 mandeep.dh 66 
 
7210 amar.kumar 67 
  <select id="getPurchaseScansByGrnDate" parameterType="map" resultType="detailedPurchaseScan">
68 
    SELECT p.purchaseOrder_id AS purchaseOrderId, 1000 * UNIX_TIMESTAMP(po.createdAt) AS poCreatedAt, s.name AS supplierName,
69 
        p.invoiceNumber AS invoiceNumbers, inv.receivedFrom AS receivedBy, i.itemId,brand, modelName, modelNumber, color,
70 
        unitPrice, SUM(initialQuantity) AS quantity, 1000 * UNIX_TIMESTAMP(receivedOn) as purchasedAt, p.id as purchaseId
71 
    FROM purchase p
72 
    JOIN purchaseorder po
73 
        ON (po.id = p.purchaseOrder_id)
74 
    JOIN inventoryItem i
75 
        ON (p.id = i.purchaseId)
76 
    JOIN lineitem l
77 
        ON (l.itemId = i.itemId AND l.purchaseOrder_id = po.id)
78 
    JOIN supplier s
79 
        ON (s.id = po.supplierId)
80 
    JOIN invoice inv
7224 amar.kumar 81 
    	ON (inv.invoiceNumber = p.invoiceNumber AND inv.supplierId = po.supplierId)
7216 amar.kumar 82 
    WHERE p.receivedOn BETWEEN #{startDate} AND #{endDate}
83 
    AND inv.date  &lt; #{endDate}
7224 amar.kumar 84 
    AND inv.date  &gt; ADDDATE(#{startDate},-1)
7210 amar.kumar 85 
    AND lastScanType != 'DOA_REPLACED'
7224 amar.kumar 86 
    GROUP BY p.purchaseOrder_id, p.id, i.itemId
87 
    ORDER BY p.purchaseOrder_id, p.id, i.itemId
7210 amar.kumar 88 
  </select>
89 
 
5496 mandeep.dh 90 
  <select id="fetchMismatchScansPerInvoiceNumber" parameterType="java.util.Date" resultType="invoiceScan" >
91 
    SELECT i.invoiceNumber, i.numItems, sum(initialQuantity) as scannedQuantity, i.date, s.name as supplierName
92 
    FROM invoice i
93 
    JOIN supplier s
7090 amar.kumar 94 
    	ON (i.supplierId = s.id)
5496 mandeep.dh 95 
    LEFT JOIN purchase p
96 
        ON (p.invoiceNumber = i.invoiceNumber)
7089 amar.kumar 97 
    LEFT JOIN inventoryItem ii
98 
    	ON (ii.purchaseId = p.id)
99 
    JOIN scanNew sn
100 
    	ON (sn.inventoryItemId = ii.id)
5496 mandeep.dh 101 
    WHERE i.date = #{date}
7089 amar.kumar 102 
    AND	sn.scannedAt between #{date} and ADDDATE(#{date},1)
103 
    AND sn.type = 'PURCHASE'
104 
    AND s.id !=1
5496 mandeep.dh 105 
    GROUP BY i.invoiceNumber, i.numItems
106 
    HAVING SUM(initialQuantity) != i.numItems
107 
  </select>
5372 mandeep.dh 108 
 
5711 mandeep.dh 109 
  <select id="getInventoryAge" resultType="inventoryAge">
110 
    SELECT t.itemId, brand, modelName, modelNumber, color,
111 
        SUM((age &lt; 1) * currentQuantity) AS freshCount,
5768 mandeep.dh 112 
        SUM((age &gt;= 1 and age &lt; 2) * currentQuantity) AS oneToTwoCount,
113 
        SUM((age &gt;= 2 and age &lt; 3) * currentQuantity) AS twoToThreeCount,
114 
        SUM((age &gt;= 3 and age &lt; 4) * currentQuantity) AS threeToFourCount,
115 
        SUM((age &gt;= 4) * currentQuantity) as fourPlusCount,
116 
        SUM((age &gt;= 1) * currentQuantity) as onePlusCount,
117 
        SUM((age &gt;= 1) * unitPrice) as onePlusCost,
118 
        SUM(currentQuantity) as zeroPlusCount,
119 
        SUM(l.unitPrice) as zeroPlusCost,
120 
        l.productGroup as category
5711 mandeep.dh 121 
    FROM  (SELECT ii.id, ii.itemId, ii.currentQuantity, ii.purchaseId,
122 
                DATEDIFF(now(), MAX(s.scannedAt)) / 7 as age
123 
           FROM inventoryItem ii
124 
           JOIN scanNew s
125 
                ON (s.inventoryItemId = ii.id)
126 
           WHERE ii.lastScanType IN ('PURCHASE', 'SALE_RET')
127 
           GROUP BY ii.id, ii.itemId, ii.currentQuantity) t
128 
    JOIN purchase p
129 
        ON (p.id = t.purchaseId)
130 
    JOIN lineitem l
131 
        ON (l.purchaseOrder_id = p.purchaseOrder_id AND l.itemId = t.itemId)
132 
    GROUP BY t.itemId
133 
    HAVING SUM(currentQuantity) != 0
134 
  </select>
6322 amar.kumar 135 
 
7089 amar.kumar 136 
  <!--select id="inventoryAgeOnePlus" resultType="detailedInventoryAge">
137 
    SELECT t.itemId, brand, modelName, modelNumber, color, scannedAt, i.warehouseId
138 
        SUM((age &gt;= 1) * currentQuantity) AS onePlusCount,
139 
        SUM((age &gt;= 1) * unitPrice) as onePlusCost,
140 
        l.productGroup as category
141 
    FROM  (SELECT ii.id, ii.itemId, ii.currentQuantity, ii.purchaseId, MIN(s.scannedAt)
142 
                DATEDIFF(now(), MAX(s.scannedAt)) / 7 as age
143 
           FROM inventoryItem ii
144 
           JOIN scanNew s
145 
                ON (s.inventoryItemId = ii.id)
146 
           WHERE ii.lastScanType IN ('PURCHASE', 'SALE_RET')
147 
           GROUP BY ii.id, ii.itemId, ii.currentQuantity) t
148 
    JOIN purchase p
149 
        ON (p.id = t.purchaseId)
150 
    JOIN lineitem l
151 
        ON (l.purchaseOrder_id = p.purchaseOrder_id AND l.itemId = t.itemId)
152 
    GROUP BY t.itemId, i.warehouseId
153 
    HAVING SUM(currentQuantity) != 0
154 
  </select-->
155 
 
156 
 
157 
 
6322 amar.kumar 158 
  <select id = "getScansForItem" parameterType = "map" resultType = "scan">
159 
	  SELECT s.id, s.inventoryItemId, s.warehouseId, s.type, s.scannedAt,
160 
	  s.quantity, s.orderId from scanNew s JOIN inventoryItem i
161 
	  ON
162 
	  s.inventoryItemId = i.id
163 
	  AND
6414 amar.kumar 164 
	  i.itemId = #{itemId}
6322 amar.kumar 165 
	  AND
166 
	  s.scannedAt BETWEEN #{fromDate} AND #{toDate};
167 
  </select>
168 
 
6558 amar.kumar 169 
  <insert id="genericScan" parameterType="scan" useGeneratedKeys="true" keyProperty="id">
6467 amar.kumar 170 
    INSERT INTO scanNew
171 
      (inventoryItemId, warehouseId, type, scannedAt, quantity)
172 
    VALUES
173 
      (#{inventoryItemId}, #{warehouseId}, #{type}, NOW(), #{quantity})
174 
  </insert>
175 
 
6558 amar.kumar 176 
  <select id = "getCurrentSerializedInventoryByScans" resultType = "inventoryAvailability">
177 
	SELECT x.itemId, ci.brand, ci.model_name as modelName,
6630 amar.kumar 178 
	ci.model_number as modelNumber, ci.color as color, (x.inx-if(y.outx is null,0,y.outx)) as quantity
6558 amar.kumar 179 
	from
180 
	(select i.itemId, count(*) as inx from scanNew s
181 
		JOIN inventoryItem i ON (i.id = s.inventoryItemId)
182 
		JOIN catalog.item c ON i.itemId = c.id
183 
		where s.type in ('PURCHASE','SALE_RET') and c.type = 'SERIALIZED'
184 
		group by i.itemId ) as x
6630 amar.kumar 185 
	LEFT JOIN
6558 amar.kumar 186 
	(select i.itemId, count(*) as outx from scanNew s
187 
		JOIN inventoryItem i on i.id = s.inventoryItemId
188 
		JOIN catalog.item c ON i.itemId = c.id
189 
		where s.type in ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN') and c.type = 'SERIALIZED'
190 
		group by i.itemId) as y
191 
	ON x.itemId = y.itemId
192 
	JOIN catalog.item ci ON ci.id = x.itemId
6630 amar.kumar 193 
	where ci.type = 'SERIALIZED' having quantity>0
6558 amar.kumar 194 
  </select>
6762 amar.kumar 195 
 
196 
<select id = "getHistoricSerializedInventoryByScans" resultType = "inventoryAvailability" parameterType = "date">
197 
	SELECT x.itemId, ci.brand, ci.model_name as modelName,
198 
	ci.model_number as modelNumber, ci.color as color, (x.inx-if(y.outx is null,0,y.outx)) as quantity
199 
	from
200 
	(select i.itemId, count(*) as inx from scanNew s
201 
		JOIN inventoryItem i ON (i.id = s.inventoryItemId)
202 
		JOIN catalog.item c ON i.itemId = c.id
203 
		where s.type in ('PURCHASE','SALE_RET') and c.type = 'SERIALIZED'
204 
		and s.scannedAt &lt; #{date}
205 
		group by i.itemId ) as x
206 
	LEFT JOIN
207 
	(select i.itemId, count(*) as outx from scanNew s
208 
		JOIN inventoryItem i on i.id = s.inventoryItemId
209 
		JOIN catalog.item c ON i.itemId = c.id
210 
		where s.type in ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN') and c.type = 'SERIALIZED'
211 
		and s.scannedAt &lt; #{date}
212 
		group by i.itemId) as y
213 
	ON x.itemId = y.itemId
214 
	JOIN catalog.item ci ON ci.id = x.itemId
215 
	where ci.type = 'SERIALIZED' having quantity>0
216 
  </select>
217 
 
6558 amar.kumar 218 
 
6630 amar.kumar 219 
  <select id = "getCurrentNonSerializedInventoryByScans" resultType = "inventoryAvailability">
220 
	SELECT x.itemId, ci.brand, ci.model_name as modelName,
221 
	ci.model_number as modelNumber, ci.color as color, (x.inx-if(y.outx is null,0,y.outx)) as quantity
222 
	from
223 
	(select i.itemId, sum(quantity) as inx from scanNew s
224 
		JOIN inventoryItem i ON (i.id = s.inventoryItemId)
225 
		JOIN catalog.item c ON i.itemId = c.id
226 
		where s.type in ('PURCHASE','SALE_RET') and c.type = 'NON_SERIALIZED'
227 
		group by i.itemId ) as x
228 
	LEFT JOIN
229 
	(select i.itemId, sum(quantity) as outx from scanNew s
230 
		JOIN inventoryItem i on i.id = s.inventoryItemId
231 
		JOIN catalog.item c ON i.itemId = c.id
232 
		where s.type in ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN') and c.type = 'NON_SERIALIZED'
233 
		group by i.itemId) as y
234 
	ON x.itemId = y.itemId
235 
	JOIN catalog.item ci ON ci.id = x.itemId
236 
	where ci.type = 'NON_SERIALIZED' having quantity>0
237 
  </select>
6762 amar.kumar 238 
 
239 
<select id = "getHistoricNonSerializedInventoryByScans" resultType = "inventoryAvailability" parameterType = "date">
240 
	SELECT x.itemId, ci.brand, ci.model_name as modelName,
241 
	ci.model_number as modelNumber, ci.color as color, (x.inx-if(y.outx is null,0,y.outx)) as quantity
242 
	from
243 
	(select i.itemId, sum(quantity) as inx from scanNew s
244 
		JOIN inventoryItem i ON (i.id = s.inventoryItemId)
245 
		JOIN catalog.item c ON i.itemId = c.id
246 
		where s.type in ('PURCHASE','SALE_RET') and c.type = 'NON_SERIALIZED'
247 
		and s.scannedAt &lt; #{date}
248 
		group by i.itemId ) as x
249 
	LEFT JOIN
250 
	(select i.itemId, sum(quantity) as outx from scanNew s
251 
		JOIN inventoryItem i on i.id = s.inventoryItemId
252 
		JOIN catalog.item c ON i.itemId = c.id
253 
		where s.type in ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN') and c.type = 'NON_SERIALIZED'
254 
		and s.scannedAt &lt; #{date}
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 = 'NON_SERIALIZED' having quantity>0
259 
  </select>
260 
 
6880 amar.kumar 261 
 <select id = "getMovementSerializedInventoryByScans" resultType = "inventoryMovement" parameterType = "map">
6762 amar.kumar 262 
 	select itemId, brand, model_name as modelName, model_number as modelNumber, color, s.type, count(*) as quantity
263 
 	FROM inventoryItem i JOIN scanNew s ON i.id = s.inventoryItemId
264 
 	LEFT JOIN catalog.item c ON i.itemId = c.id
6963 amar.kumar 265 
 	where s.scannedAt between #{startDate} and #{endDate} and c.type = 'SERIALIZED'
266 
 	and i.currentWarehouseId not in (1,2,6,9)
6762 amar.kumar 267 
 	group by itemId, s.type
268 
 </select>
269 
 
270 
 <select id = "getMovementNonSerializedInventoryByScans" resultType = "inventoryMovement" parameterType = "map">
6880 amar.kumar 271 
 	select itemId, brand, model_name as modelName, model_number as modelNumber, color, s.type, sum(quantity) as quantity
6762 amar.kumar 272 
 	FROM inventoryItem i JOIN scanNew s ON i.id = s.inventoryItemId
273 
 	LEFT JOIN catalog.item c ON i.itemId = c.id
6963 amar.kumar 274 
 	where s.scannedAt between #{startDate} and #{endDate} and c.type = 'NON_SERIALIZED'
275 
 	and i.currentWarehouseId not in (1,2,6,9)
6762 amar.kumar 276 
 	group by itemId, s.type
6880 amar.kumar 277 
 </select>
7216 amar.kumar 278 
 
279 
 <select id = "getCompleteMovementSerializedInventoryByScans" resultType = "inventoryMovement" parameterType = "map">
280 
 	select itemId, brand, model_name as modelName, model_number as modelNumber, color, s.type, count(*) as quantity
281 
 	FROM inventoryItem i JOIN scanNew s ON i.id = s.inventoryItemId
282 
 	LEFT JOIN catalog.item c ON i.itemId = c.id
283 
 	where s.scannedAt between #{startDate} and #{endDate} and c.type = 'SERIALIZED'
284 
 	group by itemId, s.type
285 
 </select>
286 
 
287 
 <select id = "getCompleteMovementNonSerializedInventoryByScans" resultType = "inventoryMovement" parameterType = "map">
288 
 	select itemId, brand, model_name as modelName, model_number as modelNumber, color, s.type, sum(quantity) as quantity
289 
 	FROM inventoryItem i JOIN scanNew s ON i.id = s.inventoryItemId
290 
 	LEFT JOIN catalog.item c ON i.itemId = c.id
291 
 	where s.scannedAt between #{startDate} and #{endDate} and c.type = 'NON_SERIALIZED'
292 
 	group by itemId, s.type
293 
 </select>
6630 amar.kumar 294 
 
4500 mandeep.dh 295 
</mapper>