WebSVN – SmartDukaan – Diff – /trunk/WarehouseService/src/main/resources/in/shop2020/warehouse/persistence/ScanMapper.xml

     <id property="id" column="id"/>
   </resultMap>
   <insert id="insert" parameterType="scan" useGeneratedKeys="true" keyProperty="id">
     INSERT INTO scanNew
-      (inventoryItemId, warehouseId, type, scannedAt, quantity, orderId)
+      (inventoryItemId, warehouseId, type, scannedAt, quantity, orderId, transferLotId, remarks)
     VALUES
-      (#{inventoryItemId}, #{warehouseId}, #{type}, NOW(), #{quantity}, #{orderId})
+      (#{inventoryItemId}, #{warehouseId}, #{type}, NOW(), #{quantity}, #{orderId}, #{transferLotId}, #{remarks})
   </insert>
   <select id="get" parameterType="map" resultType="scan">
     SELECT *
     FROM scanNew
 	  s.scannedAt BETWEEN #{fromDate} AND #{toDate};
   </select>
   <insert id="genericScan" parameterType="scan" useGeneratedKeys="true" keyProperty="id">
     INSERT INTO scanNew
-      (inventoryItemId, warehouseId, type, scannedAt, quantity)
+     (inventoryItemId, warehouseId, type, scannedAt, quantity, orderId, transferLotId, remarks)
     VALUES
-      (#{inventoryItemId}, #{warehouseId}, #{type}, NOW(), #{quantity})
+      (#{inventoryItemId}, #{warehouseId}, #{type}, NOW(), #{quantity}, #{orderId}, #{transferLotId}, #{remarks})
   </insert>
   <select id = "getCurrentSerializedInventoryByScans" resultType = "inventoryAvailability">
 	SELECT x.itemId, ci.brand, ci.model_name as modelName,
 	ci.model_number as modelNumber, ci.color as color, (x.inx-if(y.outx is null,0,y.outx)) as quantity
 <select id = "getHistoricNonSerializedInventoryByScans" resultType = "inventoryAvailability" parameterType = "date">
 	SELECT x.itemId, ci.brand, ci.model_name as modelName,
 	ci.model_number as modelNumber, ci.color as color, (x.inx-if(y.outx is null,0,y.outx)) as quantity
 	from
-	(select i.itemId, sum(quantity) as inx from scanNew s
+	(SELECT i.itemId, sum(quantity) as inx from scanNew s
 		JOIN inventoryItem i ON (i.id = s.inventoryItemId)
 		JOIN catalog.item c ON i.itemId = c.id
 		where s.type in ('PURCHASE','SALE_RET') and c.type = 'NON_SERIALIZED'
 		and s.scannedAt &lt; #{date}
 		group by i.itemId ) as x
 	LEFT JOIN
-	(select i.itemId, sum(quantity) as outx from scanNew s
+	(SELECT i.itemId, sum(quantity) as outx from scanNew s
 		JOIN inventoryItem i on i.id = s.inventoryItemId
 		JOIN catalog.item c ON i.itemId = c.id
 		where s.type in ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN') and c.type = 'NON_SERIALIZED'
 		and s.scannedAt &lt; #{date}
 		group by i.itemId) as y
 	JOIN catalog.item ci ON ci.id = x.itemId
 	where ci.type = 'NON_SERIALIZED' having quantity>0
   </select>
  <select id = "getMovementSerializedInventoryByScans" resultType = "inventoryMovement" parameterType = "map">
- 	select itemId, brand, model_name as modelName, model_number as modelNumber, color, s.type, count(*) as quantity
+ 	SELECT itemId, brand, model_name as modelName, model_number as modelNumber, color, s.type, count(*) as quantity
  	FROM inventoryItem i JOIN scanNew s ON i.id = s.inventoryItemId
  	LEFT JOIN catalog.item c ON i.itemId = c.id
  	where s.scannedAt between #{startDate} and #{endDate} and c.type = 'SERIALIZED'
  	and i.currentWarehouseId not in (1,2,6,9)
  	group by itemId, s.type
  </select>
  <select id = "getMovementNonSerializedInventoryByScans" resultType = "inventoryMovement" parameterType = "map">
- 	select itemId, brand, model_name as modelName, model_number as modelNumber, color, s.type, sum(quantity) as quantity
+ 	SELECT itemId, brand, model_name as modelName, model_number as modelNumber, color, s.type, sum(quantity) as quantity
  	FROM inventoryItem i JOIN scanNew s ON i.id = s.inventoryItemId
  	LEFT JOIN catalog.item c ON i.itemId = c.id
  	where s.scannedAt between #{startDate} and #{endDate} and c.type = 'NON_SERIALIZED'
  	and i.currentWarehouseId not in (1,2,6,9)
  	group by itemId, s.type
  </select>
  <select id = "getCompleteMovementSerializedInventoryByScans" resultType = "inventoryMovement" parameterType = "map">
- 	select itemId, brand, model_name as modelName, model_number as modelNumber, color, s.type, count(*) as quantity
+ 	SELECT itemId, brand, model_name as modelName, model_number as modelNumber, color, s.type, count(*) as quantity
  	FROM inventoryItem i JOIN scanNew s ON i.id = s.inventoryItemId
  	LEFT JOIN catalog.item c ON i.itemId = c.id
  	where s.scannedAt between #{startDate} and #{endDate} and c.type = 'SERIALIZED'
  	group by itemId, s.type
  </select>
  <select id = "getCompleteMovementNonSerializedInventoryByScans" resultType = "inventoryMovement" parameterType = "map">
- 	select itemId, brand, model_name as modelName, model_number as modelNumber, color, s.type, sum(quantity) as quantity
+ 	SELECT itemId, brand, model_name as modelName, model_number as modelNumber, color, s.type, sum(quantity) as quantity
  	FROM inventoryItem i JOIN scanNew s ON i.id = s.inventoryItemId
  	LEFT JOIN catalog.item c ON i.itemId = c.id
  	where s.scannedAt between #{startDate} and #{endDate} and c.type = 'NON_SERIALIZED'
  	group by itemId, s.type
  </select>
+<select id = "getTransferrableWarehousesFromWarehouse" resultType = "java.lang.Long" parameterType = "java.lang.Long">
+ 	SELECT toWarehouseId
+ 	FROM possibleWarehouseMovement
+ 	WHERE fromWarehouseId = #{warehouseId}
+ </select>
+<select id = "getScansForTransferLot" resultType = "scan" parameterType = "java.lang.Long">
+	SELECT *
+	FROM scanNew
+	WHERE transferLotId = #{id}
+	AND type = 'WAREHOUSE_TRANSFER_OUT'
+</select>
 </mapper>

Subversion Repositories SmartDukaan

(root)/trunk/WarehouseService/src/main/resources/in/shop2020/warehouse/persistence/ScanMapper.xml – Rev 7224 → 7410