Subversion Repositories SmartDukaan

Rev

Rev 6880 | Rev 7089 | 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 
 
34 
  <select id="getPurchaseScans" parameterType="map" resultType="detailedPurchaseScan">
5395 mandeep.dh 35 
    SELECT p.purchaseOrder_id AS purchaseOrderId, 1000 * UNIX_TIMESTAMP(po.createdAt) AS poCreatedAt, s.name AS supplierName,
6494 amar.kumar 36 
        GROUP_CONCAT(DISTINCT(p.invoiceNumber)) AS invoiceNumbers, GROUP_CONCAT(DISTINCT(inv.receivedFrom)) AS receivedBy, i.itemId,
6361 rajveer 37 
        brand, modelName, modelNumber, color, unitPrice, SUM(initialQuantity) AS quantity, 1000 * UNIX_TIMESTAMP(receivedOn) as purchasedAt, p.id as purchaseId
5372 mandeep.dh 38 
    FROM purchase p
39 
    JOIN purchaseorder po
40 
        ON (po.id = p.purchaseOrder_id)
41 
    JOIN inventoryItem i
42 
        ON (p.id = i.purchaseId)
43 
    JOIN lineitem l
44 
        ON (l.itemId = i.itemId AND l.purchaseOrder_id = po.id)
45 
    JOIN supplier s
46 
        ON (s.id = po.supplierId)
6494 amar.kumar 47 
    JOIN invoice inv
48 
    	ON (inv.invoiceNumber = p.invoiceNumber)
5372 mandeep.dh 49 
    WHERE po.createdAt BETWEEN #{startDate} AND #{endDate}
6364 rajveer 50 
    AND lastScanType != 'DOA_REPLACED'
6362 rajveer 51 
    GROUP BY p.purchaseOrder_id, p.id, i.itemId
52 
    ORDER BY p.purchaseOrder_id, p.id, i.itemId
5372 mandeep.dh 53 
  </select>
5496 mandeep.dh 54 
 
55 
  <select id="fetchMismatchScansPerInvoiceNumber" parameterType="java.util.Date" resultType="invoiceScan" >
56 
    SELECT i.invoiceNumber, i.numItems, sum(initialQuantity) as scannedQuantity, i.date, s.name as supplierName
57 
    FROM invoice i
58 
    JOIN supplier s
59 
        ON (s.id = i.supplierId)
60 
    LEFT JOIN purchase p
61 
        ON (p.invoiceNumber = i.invoiceNumber)
62 
    LEFT JOIN inventoryItem ii ON (ii.purchaseId = p.id)
63 
    WHERE i.date = #{date}
64 
    GROUP BY i.invoiceNumber, i.numItems
65 
    HAVING SUM(initialQuantity) != i.numItems
66 
  </select>
5372 mandeep.dh 67 
 
5711 mandeep.dh 68 
  <select id="getInventoryAge" resultType="inventoryAge">
69 
    SELECT t.itemId, brand, modelName, modelNumber, color,
70 
        SUM((age &lt; 1) * currentQuantity) AS freshCount,
5768 mandeep.dh 71 
        SUM((age &gt;= 1 and age &lt; 2) * currentQuantity) AS oneToTwoCount,
72 
        SUM((age &gt;= 2 and age &lt; 3) * currentQuantity) AS twoToThreeCount,
73 
        SUM((age &gt;= 3 and age &lt; 4) * currentQuantity) AS threeToFourCount,
74 
        SUM((age &gt;= 4) * currentQuantity) as fourPlusCount,
75 
        SUM((age &gt;= 1) * currentQuantity) as onePlusCount,
76 
        SUM((age &gt;= 1) * unitPrice) as onePlusCost,
77 
        SUM(currentQuantity) as zeroPlusCount,
78 
        SUM(l.unitPrice) as zeroPlusCost,
79 
        l.productGroup as category
5711 mandeep.dh 80 
    FROM  (SELECT ii.id, ii.itemId, ii.currentQuantity, ii.purchaseId,
81 
                DATEDIFF(now(), MAX(s.scannedAt)) / 7 as age
82 
           FROM inventoryItem ii
83 
           JOIN scanNew s
84 
                ON (s.inventoryItemId = ii.id)
85 
           WHERE ii.lastScanType IN ('PURCHASE', 'SALE_RET')
86 
           GROUP BY ii.id, ii.itemId, ii.currentQuantity) t
87 
    JOIN purchase p
88 
        ON (p.id = t.purchaseId)
89 
    JOIN lineitem l
90 
        ON (l.purchaseOrder_id = p.purchaseOrder_id AND l.itemId = t.itemId)
91 
    GROUP BY t.itemId
92 
    HAVING SUM(currentQuantity) != 0
93 
  </select>
6322 amar.kumar 94 
 
95 
  <select id = "getScansForItem" parameterType = "map" resultType = "scan">
96 
	  SELECT s.id, s.inventoryItemId, s.warehouseId, s.type, s.scannedAt,
97 
	  s.quantity, s.orderId from scanNew s JOIN inventoryItem i
98 
	  ON
99 
	  s.inventoryItemId = i.id
100 
	  AND
6414 amar.kumar 101 
	  i.itemId = #{itemId}
6322 amar.kumar 102 
	  AND
103 
	  s.scannedAt BETWEEN #{fromDate} AND #{toDate};
104 
  </select>
105 
 
6558 amar.kumar 106 
  <insert id="genericScan" parameterType="scan" useGeneratedKeys="true" keyProperty="id">
6467 amar.kumar 107 
    INSERT INTO scanNew
108 
      (inventoryItemId, warehouseId, type, scannedAt, quantity)
109 
    VALUES
110 
      (#{inventoryItemId}, #{warehouseId}, #{type}, NOW(), #{quantity})
111 
  </insert>
112 
 
6558 amar.kumar 113 
  <select id = "getCurrentSerializedInventoryByScans" resultType = "inventoryAvailability">
114 
	SELECT x.itemId, ci.brand, ci.model_name as modelName,
6630 amar.kumar 115 
	ci.model_number as modelNumber, ci.color as color, (x.inx-if(y.outx is null,0,y.outx)) as quantity
6558 amar.kumar 116 
	from
117 
	(select i.itemId, count(*) as inx from scanNew s
118 
		JOIN inventoryItem i ON (i.id = s.inventoryItemId)
119 
		JOIN catalog.item c ON i.itemId = c.id
120 
		where s.type in ('PURCHASE','SALE_RET') and c.type = 'SERIALIZED'
121 
		group by i.itemId ) as x
6630 amar.kumar 122 
	LEFT JOIN
6558 amar.kumar 123 
	(select i.itemId, count(*) as outx from scanNew s
124 
		JOIN inventoryItem i on i.id = s.inventoryItemId
125 
		JOIN catalog.item c ON i.itemId = c.id
126 
		where s.type in ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN') and c.type = 'SERIALIZED'
127 
		group by i.itemId) as y
128 
	ON x.itemId = y.itemId
129 
	JOIN catalog.item ci ON ci.id = x.itemId
6630 amar.kumar 130 
	where ci.type = 'SERIALIZED' having quantity>0
6558 amar.kumar 131 
  </select>
6762 amar.kumar 132 
 
133 
<select id = "getHistoricSerializedInventoryByScans" resultType = "inventoryAvailability" parameterType = "date">
134 
	SELECT x.itemId, ci.brand, ci.model_name as modelName,
135 
	ci.model_number as modelNumber, ci.color as color, (x.inx-if(y.outx is null,0,y.outx)) as quantity
136 
	from
137 
	(select i.itemId, count(*) as inx from scanNew s
138 
		JOIN inventoryItem i ON (i.id = s.inventoryItemId)
139 
		JOIN catalog.item c ON i.itemId = c.id
140 
		where s.type in ('PURCHASE','SALE_RET') and c.type = 'SERIALIZED'
141 
		and s.scannedAt &lt; #{date}
142 
		group by i.itemId ) as x
143 
	LEFT JOIN
144 
	(select i.itemId, count(*) as outx from scanNew s
145 
		JOIN inventoryItem i on i.id = s.inventoryItemId
146 
		JOIN catalog.item c ON i.itemId = c.id
147 
		where s.type in ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN') and c.type = 'SERIALIZED'
148 
		and s.scannedAt &lt; #{date}
149 
		group by i.itemId) as y
150 
	ON x.itemId = y.itemId
151 
	JOIN catalog.item ci ON ci.id = x.itemId
152 
	where ci.type = 'SERIALIZED' having quantity>0
153 
  </select>
154 
 
6558 amar.kumar 155 
 
6630 amar.kumar 156 
  <select id = "getCurrentNonSerializedInventoryByScans" resultType = "inventoryAvailability">
157 
	SELECT x.itemId, ci.brand, ci.model_name as modelName,
158 
	ci.model_number as modelNumber, ci.color as color, (x.inx-if(y.outx is null,0,y.outx)) as quantity
159 
	from
160 
	(select i.itemId, sum(quantity) as inx from scanNew s
161 
		JOIN inventoryItem i ON (i.id = s.inventoryItemId)
162 
		JOIN catalog.item c ON i.itemId = c.id
163 
		where s.type in ('PURCHASE','SALE_RET') and c.type = 'NON_SERIALIZED'
164 
		group by i.itemId ) as x
165 
	LEFT JOIN
166 
	(select i.itemId, sum(quantity) as outx from scanNew s
167 
		JOIN inventoryItem i on i.id = s.inventoryItemId
168 
		JOIN catalog.item c ON i.itemId = c.id
169 
		where s.type in ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN') and c.type = 'NON_SERIALIZED'
170 
		group by i.itemId) as y
171 
	ON x.itemId = y.itemId
172 
	JOIN catalog.item ci ON ci.id = x.itemId
173 
	where ci.type = 'NON_SERIALIZED' having quantity>0
174 
  </select>
6762 amar.kumar 175 
 
176 
<select id = "getHistoricNonSerializedInventoryByScans" resultType = "inventoryAvailability" parameterType = "date">
177 
	SELECT x.itemId, ci.brand, ci.model_name as modelName,
178 
	ci.model_number as modelNumber, ci.color as color, (x.inx-if(y.outx is null,0,y.outx)) as quantity
179 
	from
180 
	(select i.itemId, sum(quantity) 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 = 'NON_SERIALIZED'
184 
		and s.scannedAt &lt; #{date}
185 
		group by i.itemId ) as x
186 
	LEFT JOIN
187 
	(select i.itemId, sum(quantity) as outx from scanNew s
188 
		JOIN inventoryItem i on i.id = s.inventoryItemId
189 
		JOIN catalog.item c ON i.itemId = c.id
190 
		where s.type in ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN') and c.type = 'NON_SERIALIZED'
191 
		and s.scannedAt &lt; #{date}
192 
		group by i.itemId) as y
193 
	ON x.itemId = y.itemId
194 
	JOIN catalog.item ci ON ci.id = x.itemId
195 
	where ci.type = 'NON_SERIALIZED' having quantity>0
196 
  </select>
197 
 
6880 amar.kumar 198 
 <select id = "getMovementSerializedInventoryByScans" resultType = "inventoryMovement" parameterType = "map">
6762 amar.kumar 199 
 	select itemId, brand, model_name as modelName, model_number as modelNumber, color, s.type, count(*) as quantity
200 
 	FROM inventoryItem i JOIN scanNew s ON i.id = s.inventoryItemId
201 
 	LEFT JOIN catalog.item c ON i.itemId = c.id
6963 amar.kumar 202 
 	where s.scannedAt between #{startDate} and #{endDate} and c.type = 'SERIALIZED'
203 
 	and i.currentWarehouseId not in (1,2,6,9)
6762 amar.kumar 204 
 	group by itemId, s.type
205 
 </select>
206 
 
207 
 <select id = "getMovementNonSerializedInventoryByScans" resultType = "inventoryMovement" parameterType = "map">
6880 amar.kumar 208 
 	select itemId, brand, model_name as modelName, model_number as modelNumber, color, s.type, sum(quantity) as quantity
6762 amar.kumar 209 
 	FROM inventoryItem i JOIN scanNew s ON i.id = s.inventoryItemId
210 
 	LEFT JOIN catalog.item c ON i.itemId = c.id
6963 amar.kumar 211 
 	where s.scannedAt between #{startDate} and #{endDate} and c.type = 'NON_SERIALIZED'
212 
 	and i.currentWarehouseId not in (1,2,6,9)
6762 amar.kumar 213 
 	group by itemId, s.type
6880 amar.kumar 214 
 </select>
6630 amar.kumar 215 
 
4500 mandeep.dh 216 
</mapper>