| 7410 |
amar.kumar |
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 |
|
|
|
6 |
|
|
|
7 |
<mapper namespace="in.shop2020.warehouse.persistence.TransferLotMapper">
|
|
|
8 |
<resultMap type="transferLot" id="transferLotResult">
|
|
|
9 |
<id property="id" column="id"/>
|
|
|
10 |
</resultMap>
|
|
|
11 |
|
|
|
12 |
<insert id="createTransferLot" parameterType="transferLot" useGeneratedKeys="true" keyProperty="id">
|
|
|
13 |
INSERT INTO transferLot
|
|
|
14 |
(id, originWarehouseId, destinationWarehouseId, status, transferDate, transitCompletionReferenceNumber, referenceUpdationDate, remarks)
|
|
|
15 |
VALUES
|
|
|
16 |
(#{id}, #{originWarehouseId}, #{destinationWarehouseId}, #{status}, #{transferDate}, #{transitCompletionReferenceNumber}, #{referenceUpdationDate}, #{remarks})
|
|
|
17 |
</insert>
|
|
|
18 |
|
|
|
19 |
<select id="getTransferLot" parameterType="java.lang.Long" resultType="transferLot">
|
|
|
20 |
SELECT * from transferLot
|
|
|
21 |
WHERE id = #{transferLotId}
|
|
|
22 |
</select>
|
|
|
23 |
|
|
|
24 |
|
|
|
25 |
<select id="getTransferLotsByDate" parameterType="map" resultType="transferLot">
|
|
|
26 |
SELECT * from transferLot
|
|
|
27 |
WHERE transferDate between #{fromDate} and #{toDate}
|
|
|
28 |
</select>
|
|
|
29 |
|
|
|
30 |
<select id="getItemsInTransferLot" parameterType="java.lang.Long" resultType="map">
|
|
|
31 |
SELECT i.itemId as itemId, sum(s.quantity) as count
|
|
|
32 |
FROM inventoryItem i
|
|
|
33 |
JOIN scanNew s
|
|
|
34 |
ON i.id = s.inventoryItemId
|
|
|
35 |
WHERE s.transferLotId = #{transferLotId}
|
|
|
36 |
AND s.type = 'WAREHOUSE_TRANSFER_OUT'
|
|
|
37 |
group by i.itemId
|
|
|
38 |
</select>
|
|
|
39 |
|
|
|
40 |
<update id="markTransferLotAsReceived" parameterType = "map">
|
|
|
41 |
UPDATE transferLot
|
|
|
42 |
SET
|
|
|
43 |
status = 'TRANSFER_COMPLETE',
|
|
|
44 |
transitCompletionReferenceNumber = #{remoteTransferRefNumber},
|
|
|
45 |
referenceUpdationDate = NOW()
|
|
|
46 |
WHERE id = #{id}
|
|
|
47 |
</update>
|
|
|
48 |
|
|
|
49 |
<select id="getAllowedDestinationWarehousesForTransfer" parameterType="java.lang.Long" resultType="java.lang.Long">
|
|
|
50 |
SELECT toWarehouseId
|
|
|
51 |
FROM possibleWarehouseMovement
|
|
|
52 |
WHERE fromWarehouseId = #{originWarehouseId}
|
|
|
53 |
</select>
|
|
|
54 |
|
|
|
55 |
<update id = "updateTransferLotStatus" parameterType = "map">
|
|
|
56 |
UPDATE transferLot
|
|
|
57 |
SET
|
|
|
58 |
status = #{status}
|
|
|
59 |
WHERE id = #{id}
|
|
|
60 |
</update>
|
|
|
61 |
|
| 10120 |
manish.sha |
62 |
<update id="markTransferLotAsReceivedPartial" parameterType = "map">
|
|
|
63 |
UPDATE transferLot
|
|
|
64 |
SET
|
|
|
65 |
status = 'PARTIAL_TRANSFER',
|
|
|
66 |
transitCompletionReferenceNumber = #{remoteTransferRefNumber},
|
|
|
67 |
referenceUpdationDate = NOW()
|
|
|
68 |
WHERE id = #{id}
|
|
|
69 |
</update>
|
|
|
70 |
|
| 15045 |
manish.sha |
71 |
<insert id="createRemovalStockShipmentReference" parameterType="removalStockShipmentReference" useGeneratedKeys="true" keyProperty="id">
|
|
|
72 |
INSERT INTO removalStockShipmentReference
|
|
|
73 |
(id, shipmentReference, createdOn, source, inventoryType, quantity, unfulfilQuantity, completed)
|
|
|
74 |
VALUES
|
|
|
75 |
(#{id}, #{shipmentReference}, #{createdOn}, #{source}, #{inventoryType}, #{quantity}, #{unfulfilQuantity}, #{completed})
|
|
|
76 |
</insert>
|
|
|
77 |
|
|
|
78 |
<update id="updateStockShipment" parameterType="map" >
|
|
|
79 |
UPDATE removalStockShipmentReference
|
|
|
80 |
SET
|
|
|
81 |
unfulfilQuantity = unfulfilQuantity - #{lineItemQuantity}
|
|
|
82 |
WHERE id= #{id}
|
|
|
83 |
</update>
|
|
|
84 |
|
|
|
85 |
<update id="markStockShipmentComplete" parameterType= "java.lang.Long" >
|
|
|
86 |
UPDATE removalStockShipmentReference
|
|
|
87 |
SET
|
|
|
88 |
completed =1
|
|
|
89 |
WHERE id= #{id}
|
|
|
90 |
</update>
|
|
|
91 |
|
|
|
92 |
<select id="getAllUnCompletedStockShipments" parameterType="java.lang.String" resultType= "removalStockShipmentReference">
|
|
|
93 |
SELECT * FROM
|
|
|
94 |
removalStockShipmentReference
|
|
|
95 |
WHERE source= #{source}
|
|
|
96 |
AND completed = 0
|
|
|
97 |
</select>
|
|
|
98 |
|
|
|
99 |
<update id="updateShipmentReferenceTransferLot" parameterType="map">
|
|
|
100 |
UPDATE transferLot
|
|
|
101 |
SET
|
|
|
102 |
shipmentReference = #{shipmentReference}
|
|
|
103 |
WHERE id= #{id}
|
|
|
104 |
</update>
|
|
|
105 |
|
|
|
106 |
<select id="getRemovalStockShipmentReferenceById" parameterType="java.lang.Long" resultType= "removalStockShipmentReference">
|
|
|
107 |
SELECT * FROM
|
|
|
108 |
removalStockShipmentReference
|
|
|
109 |
WHERE id= #{id}
|
|
|
110 |
</select>
|
| 7410 |
amar.kumar |
111 |
</mapper>
|
|
|
112 |
|
|
|
113 |
|