Subversion Repositories SmartDukaan

Rev

Rev 10120 | Rev 10347 | Go to most recent revision | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 10120 Rev 10308
Line 222... Line 222...
222 
	ON x.itemId = y.itemId
222 
	ON x.itemId = y.itemId
223 
	JOIN catalog.item ci ON ci.id = x.itemId
223 
	JOIN catalog.item ci ON ci.id = x.itemId
224 
	where ci.type = 'SERIALIZED' having quantity>0
224 
	where ci.type = 'SERIALIZED' having quantity>0
225 
  </select>
 225 
  </select>
226 
 
 226 
 
- 
 
 227 
<select id = "getOurHistoricSerializedInventoryByScans" resultType = "inventoryAvailability" parameterType = "date">
- 
 
 228 
	SELECT x.itemId, ci.brand, ci.model_name as modelName,
- 
 
 229 
	ci.model_number as modelNumber, ci.color as color, (x.inx-if(y.outx is null,0,y.outx)) as quantity
- 
 
 230 
	from
- 
 
 231 
	(select i.itemId, count(*) as inx from scanNew s
- 
 
 232 
		JOIN inventoryItem i ON (i.id = s.inventoryItemId)
- 
 
 233 
		JOIN catalog.item c ON i.itemId = c.id
- 
 
 234 
		where s.type in ('PURCHASE','SALE_RET', 'MARKED_GOOD','WAREHOUSE_TRANSFER_IN') and c.type = 'SERIALIZED'
- 
 
 235 
		and s.scannedAt &lt; #{date}
- 
 
 236 
		and s.warehouseId != 16
- 
 
 237 
		group by i.itemId ) as x
- 
 
 238 
	LEFT JOIN
- 
 
 239 
	(select i.itemId, count(*) as outx 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 ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN','MARKED_USED','MARKED_BAD','WAREHOUSE_TRANSFER_OUT') and c.type = 'SERIALIZED'
- 
 
 243 
		and s.scannedAt &lt; #{date}
- 
 
 244 
		and s.warehouseId != 16
- 
 
 245 
		group by i.itemId) as y
- 
 
 246 
	ON x.itemId = y.itemId
- 
 
 247 
	JOIN catalog.item ci ON ci.id = x.itemId
- 
 
 248 
	where ci.type = 'SERIALIZED' having quantity>0
- 
 
 249 
</select>
- 
 
 250 
 
227
251
228 
  <select id = "getCurrentNonSerializedInventoryByScans" resultType = "inventoryAvailability">
 252 
  <select id = "getCurrentNonSerializedInventoryByScans" resultType = "inventoryAvailability">
229 
	SELECT x.itemId, ci.brand, ci.model_name as modelName,
253 
	SELECT x.itemId, ci.brand, ci.model_name as modelName,
230 
	ci.model_number as modelNumber, ci.color as color, (x.inx-if(y.outx is null,0,y.outx)) as quantity
 254 
	ci.model_number as modelNumber, ci.color as color, (x.inx-if(y.outx is null,0,y.outx)) as quantity
231 
	from
255 
	from
Line 269... Line 293...
269 
	ON x.itemId = y.itemId
293 
	ON x.itemId = y.itemId
270 
	JOIN catalog.item ci ON ci.id = x.itemId
294 
	JOIN catalog.item ci ON ci.id = x.itemId
271 
	where ci.type = 'NON_SERIALIZED' having quantity>0
295 
	where ci.type = 'NON_SERIALIZED' having quantity>0
272 
  </select>
 296 
  </select>
273 
 
 297 
 
- 
 
 298 
<select id = "getOurHistoricNonSerializedInventoryByScans" resultType = "inventoryAvailability" parameterType = "date">
- 
 
 299 
	SELECT x.itemId, ci.brand, ci.model_name as modelName,
- 
 
 300 
	ci.model_number as modelNumber, ci.color as color, (x.inx-if(y.outx is null,0,y.outx)) as quantity
- 
 
 301 
	from
- 
 
 302 
	(SELECT i.itemId, sum(quantity) as inx from scanNew s
- 
 
 303 
		JOIN inventoryItem i ON (i.id = s.inventoryItemId)
- 
 
 304 
		JOIN catalog.item c ON i.itemId = c.id
- 
 
 305 
		where s.type in ('PURCHASE','SALE_RET', 'MARKED_GOOD','WAREHOUSE_TRANSFER_IN') and c.type = 'NON_SERIALIZED'
- 
 
 306 
		and s.scannedAt &lt; #{date}
- 
 
 307 
		and s.warehouseId != 16
- 
 
 308 
		group by i.itemId ) as x
- 
 
 309 
	LEFT JOIN
- 
 
 310 
	(SELECT i.itemId, sum(quantity) as outx from scanNew s
- 
 
 311 
		JOIN inventoryItem i on i.id = s.inventoryItemId
- 
 
 312 
		JOIN catalog.item c ON i.itemId = c.id
- 
 
 313 
		where s.type in ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN','MARKED_USED','MARKED_BAD','WAREHOUSE_TRANSFER_OUT') and c.type = 'NON_SERIALIZED'
- 
 
 314 
		and s.scannedAt &lt; #{date}
- 
 
 315 
		and s.warehouseId != 16
- 
 
 316 
		group by i.itemId) as y
- 
 
 317 
	ON x.itemId = y.itemId
- 
 
 318 
	JOIN catalog.item ci ON ci.id = x.itemId
- 
 
 319 
	where ci.type = 'NON_SERIALIZED' having quantity>0
- 
 
 320 
</select>
- 
 
 321 
 
274 
 <select id = "getMovementSerializedInventoryByScans" resultType = "inventoryMovement" parameterType = "map">
 322 
 <select id = "getMovementSerializedInventoryByScans" resultType = "inventoryMovement" parameterType = "map">
275 
 	SELECT itemId, brand, model_name as modelName, model_number as modelNumber, color, s.type, count(*) as quantity
323 
 	SELECT itemId, brand, model_name as modelName, model_number as modelNumber, color, s.type, count(*) as quantity
276 
 	FROM inventoryItem i JOIN scanNew s ON i.id = s.inventoryItemId
324 
 	FROM inventoryItem i JOIN scanNew s ON i.id = s.inventoryItemId
277 
 	LEFT JOIN catalog.item c ON i.itemId = c.id
325 
 	LEFT JOIN catalog.item c ON i.itemId = c.id
278 
 	where s.scannedAt between #{startDate} and #{endDate} and c.type = 'SERIALIZED'
 326 
 	where s.scannedAt between #{startDate} and #{endDate} and c.type = 'SERIALIZED'
279 
 	and i.currentWarehouseId not in (1,2,6,9)
327 
 	and i.currentWarehouseId not in (1,2,6,9)
- 
 
 328 
 	and s.warehouseId != 16
280 
 	group by itemId, s.type
 329 
 	group by itemId, s.type
281 
 </select>
 330 
 </select>
282
331
283 
 <select id = "getMovementNonSerializedInventoryByScans" resultType = "inventoryMovement" parameterType = "map">
 332 
 <select id = "getMovementNonSerializedInventoryByScans" resultType = "inventoryMovement" parameterType = "map">
284 
 	SELECT itemId, brand, model_name as modelName, model_number as modelNumber, color, s.type, sum(quantity) as quantity
333 
 	SELECT itemId, brand, model_name as modelName, model_number as modelNumber, color, s.type, sum(quantity) as quantity
285 
 	FROM inventoryItem i JOIN scanNew s ON i.id = s.inventoryItemId
334 
 	FROM inventoryItem i JOIN scanNew s ON i.id = s.inventoryItemId
286 
 	LEFT JOIN catalog.item c ON i.itemId = c.id
335 
 	LEFT JOIN catalog.item c ON i.itemId = c.id
287 
 	where s.scannedAt between #{startDate} and #{endDate} and c.type = 'NON_SERIALIZED'
 336 
 	where s.scannedAt between #{startDate} and #{endDate} and c.type = 'NON_SERIALIZED'
288 
 	and i.currentWarehouseId not in (1,2,6,9)
337 
 	and i.currentWarehouseId not in (1,2,6,9)
- 
 
 338 
 	and s.warehouseId != 16
289 
 	group by itemId, s.type
 339 
 	group by itemId, s.type
290 
 </select>
 340 
 </select>
291 
 
 341 
 
292 
 <select id = "getCompleteMovementSerializedInventoryByScans" resultType = "inventoryMovement" parameterType = "map">
 342 
 <select id = "getCompleteMovementSerializedInventoryByScans" resultType = "inventoryMovement" parameterType = "map">
293 
 	SELECT itemId, brand, model_name as modelName, model_number as modelNumber, color, s.type, count(*) as quantity
343 
 	SELECT itemId, brand, model_name as modelName, model_number as modelNumber, color, s.type, count(*) as quantity
294 
 	FROM inventoryItem i JOIN scanNew s ON i.id = s.inventoryItemId
344 
 	FROM inventoryItem i JOIN scanNew s ON i.id = s.inventoryItemId
295 
 	LEFT JOIN catalog.item c ON i.itemId = c.id
345 
 	LEFT JOIN catalog.item c ON i.itemId = c.id
296 
 	where s.scannedAt between #{startDate} and #{endDate} and c.type = 'SERIALIZED'
 346 
 	where s.scannedAt between #{startDate} and #{endDate} and c.type = 'SERIALIZED'
- 
 
 347 
 	and s.warehouseId != 16
297 
 	group by itemId, s.type
 348 
 	group by itemId, s.type
298 
 </select>
 349 
 </select>
299
350
300 
 <select id = "getCompleteMovementNonSerializedInventoryByScans" resultType = "inventoryMovement" parameterType = "map">
 351 
 <select id = "getCompleteMovementNonSerializedInventoryByScans" resultType = "inventoryMovement" parameterType = "map">
301 
 	SELECT itemId, brand, model_name as modelName, model_number as modelNumber, color, s.type, sum(quantity) as quantity
352 
 	SELECT itemId, brand, model_name as modelName, model_number as modelNumber, color, s.type, sum(quantity) as quantity
302 
 	FROM inventoryItem i JOIN scanNew s ON i.id = s.inventoryItemId
353 
 	FROM inventoryItem i JOIN scanNew s ON i.id = s.inventoryItemId
303 
 	LEFT JOIN catalog.item c ON i.itemId = c.id
354 
 	LEFT JOIN catalog.item c ON i.itemId = c.id
304 
 	where s.scannedAt between #{startDate} and #{endDate} and c.type = 'NON_SERIALIZED'
 355 
 	where s.scannedAt between #{startDate} and #{endDate} and c.type = 'NON_SERIALIZED'
- 
 
 356 
 	and s.warehouseId != 16
305 
 	group by itemId, s.type
 357 
 	group by itemId, s.type
306 
 </select>
 358 
 </select>
307
359
308 
<select id = "getTransferrableWarehousesFromWarehouse" resultType = "java.lang.Long" parameterType = "java.lang.Long">
 360 
<select id = "getTransferrableWarehousesFromWarehouse" resultType = "java.lang.Long" parameterType = "java.lang.Long">
309 
 	SELECT toWarehouseId
361 
 	SELECT toWarehouseId