· 10 years ago · Jan 09, 2016, 05:45 AM
1USE [Adayroi_TMS]
2GO
3/****** Object: StoredProcedure [dbo].[APIClient_DE_UpdateDeliverySlip_V01] Script Date: 1/9/2016 12:41:33 PM ******/
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8-- =============================================
9-- Author: Hoà ng Äình Khiêm
10-- Create date: 13/05/2015
11-- Description: Cáºp nháºt trạng thái giao hà ng
12-- =============================================
13ALTER PROCEDURE [dbo].[APIClient_DE_UpdateDeliverySlip_V01]
14 @CodId INT,
15 @StationId INT,
16 @CurrentUser INT,
17 @SesstionKey VARCHAR(250),
18 @DESDetail NVARCHAR(MAX),
19 @IsState INT
20AS
21BEGIN
22 DECLARE @PROC_NAME VARCHAR(250) = OBJECT_NAME(@@PROCID)
23 --Tạo Table temp
24 CREATE TABLE #TableOpenXml
25 (
26 DeliverySlipsId INT,
27 DeliverySlipsDetailId BIGINT,
28 QuantityCompleted INT,
29 QuantityCanceled INT,
30 QuantityAgain INT,
31 QuantityFailing INT,
32 ReasonAgain INT,
33 ReasonCanceled INT,
34 IsCompleted BIT,
35 IsAgain BIT,
36 IsCanceled BIT ,
37 DateAgain DATETIME,
38 DateAgainTo DATETIME,
39 [Status] INT,
40 ReasonAgainOrther NVARCHAR(250),
41 ReasonCanceledOrther NVARCHAR(250),
42 SoDetailId BIGINT,
43 DeliveryFlowId INT,
44 CodId INT,
45 ClientSoId BIGINT,
46 SoId BIGINT,
47 Note NVARCHAR(500)
48 )
49 BEGIN TRY
50 EXEC [dbo].[CHECK_SESSION_AND_PERMISSION] @CurrentUser, @SesstionKey, @PROC_NAME
51 --DECLARE @RetryCounter INT
52 --SET @RetryCounter = 1
53 --RETRY: -- Label RETRY
54 BEGIN TRAN T
55 BEGIN TRY-------------------------------------------------------------------------------------BEGIN TRY-------------------------------------------------->
56 --Deadlock------------------------------------------
57 UPDATE LockTableSoDetail SET COUNT=1 WHERE ID=1 --
58 ----------------------------------------------------
59 BEGIN
60 DECLARE @DeliverySlipsId INT
61 DECLARE @CashId INT
62 DECLARE @CodDe INT
63 DECLARE @idoc int
64 DECLARE @Counter INT
65 EXEC sp_xml_preparedocument @idoc OUTPUT, @DESDetail
66
67 --INSERT TO TABLE TEMP---------------------------------------------------------------BEGIN------------------------------------------------------->
68 INSERT INTO #TableOpenXml (DeliverySlipsId, DeliverySlipsDetailId,QuantityCompleted,QuantityCanceled,QuantityAgain,QuantityFailing,ReasonAgain,ReasonCanceled,
69 IsCompleted,IsAgain,IsCanceled,DateAgain,DateAgainTo,[Status],ReasonAgainOrther,ReasonCanceledOrther,SoDetailId,DeliveryFlowId,CodId,ClientSoId,SoId, Note)
70 SELECT DeliverySlipsId, DeliverySlipsDetailId,QuantityCompleted,QuantityCanceled,QuantityAgain,QuantityFailing,ReasonAgain,ReasonCanceled,
71 IsCompleted,IsAgain,IsCanceled,DateAgain,DateAgainTo,[Status],ReasonAgainOrther,ReasonCanceledOrther,SoDetailId,DeliveryFlowId,CodId,ClientSoId,SoId, Note
72 FROM OPENXML (@idoc, '/ArrayOfDeSlipsBo/DeSlipsBo', 8)
73 WITH
74 (
75 DeliverySlipsId INT 'DeliverySlipsId',
76 DeliverySlipsDetailId BIGINT 'DeliverySlipsDetailId',
77 QuantityCompleted INT 'QuantityCompleted',
78 QuantityCanceled INT 'QuantityCanceled',
79 QuantityAgain INT 'QuantityAgain',
80 QuantityFailing INT 'QuantityFailing',
81 ReasonAgain INT 'ReasonAgain',
82 ReasonCanceled INT 'ReasonCanceled',
83 IsCompleted BIT 'IsCompleted',
84 IsAgain BIT 'IsAgain',
85 IsCanceled BIT 'IsCanceled',
86 DateAgain DATETIME 'DateAgain',
87 DateAgainTo DATETIME 'DateAgainTo',
88 [Status] INT 'Status',
89 ReasonAgainOrther NVARCHAR(250) 'ReasonAgainOrther',
90 ReasonCanceledOrther NVARCHAR(250) 'ReasonCanceledOrther',
91 SoDetailId BIGINT 'SoDetailId',
92 DeliveryFlowId INT 'DeliveryFlowId',
93 CodId INT 'CodId',
94 ClientSoId BIGINT 'ClientSoId',
95 SoId BIGINT 'SoId',
96 Note NVARCHAR(500) 'Note'
97 )
98 -------------------------------------------------------------------------------------END--------------------------------------------------------->
99 IF (@IsState = 2) -->LƯU HOÀN TẤT
100 BEGIN--------------------------------------------------------------------------------BEGIN------------------------------------------------------->
101 --CHECK DSGH ÄÃ ÄƯỢC LƯU Ở SECTION KHÃC HAY CHƯA
102 SELECT @Counter = COUNT(sd.ClientSoId)
103 FROM #TableOpenXml tox INNER JOIN dbo.SO_Detail sd ON tox.SoDetailId = sd.SoDetailId
104 WHERE sd.SODetailStatusID >= 13
105
106 IF (@Counter = 0)
107 BEGIN
108 --1. CẬP NHẬT TRẠNG THÃI CHI TIẾT PHIẾU: DE_DeliverySlipsDetails
109 UPDATE DE_DeliverySlipsDetails
110 SET
111 IsCompleted = D.IsCompleted,
112 IsAgain = D.IsAgain,
113 IsCanceled = D.IsCanceled,
114 DateCompleted = CASE WHEN D.IsCompleted = 1 THEN GETDATE() ELSE NULL END,
115 DateAgain = CASE WHEN D.IsAgain = 1 THEN D.DateAgain ELSE NULL END,
116 DateAgainTo = CASE WHEN D.IsAgain = 1 THEN D.DateAgainTo ELSE NULL END,
117 DateCanceled = CASE WHEN D.IsCanceled = 1 THEN GETDATE() ELSE NULL END,
118 QuantityCompleted = CASE WHEN D.IsCompleted = 1 THEN D.QuantityCompleted ELSE 0 END,
119 QuantityAgain = CASE WHEN D.IsAgain = 1 THEN D.QuantityAgain ELSE 0 END,
120 QuantityCanceled = CASE WHEN D.IsCanceled = 1 THEN D.QuantityCanceled ELSE 0 END,
121 QuantityFailing = D.QuantityFailing,
122 ReasonAgain = CASE WHEN D.IsAgain = 1 THEN D.ReasonAgain ELSE 0 END,
123 ReasonCanceled = CASE WHEN D.IsCanceled = 1 THEN D.ReasonCanceled ELSE 0 END,
124 ModifiedBy = @CurrentUser,
125 DateModified = GETDATE(),
126 [Status] = 29,
127 ReasonAgainOrther = CASE WHEN D.IsAgain = 1 THEN D.ReasonAgainOrther ELSE NULL END,
128 ReasonCanceledOrther= CASE WHEN D.IsCanceled = 1 THEN D.ReasonCanceledOrther ELSE NULL END,
129 DateSaveTemp = CASE WHEN DED.DateSaveTemp IS NULL THEN GETDATE() ELSE DED.DateSaveTemp END,
130 Note = D.Note
131 FROM
132 DE_DeliverySlipsDetails DED INNER JOIN #TableOpenXml D ON (DED.DeliverySlipsDetailId = D.DeliverySlipsDetailId)
133 ---------------------------------------------------------------------------------END----------------------------------------------------------
134 --2. TRƯỜNG HỢP HOÀN THÀNH HẾT
135 ---------------------------------------------------------------------------------BEGIN------------------------------------------------------->
136 IF(EXISTS(SELECT 1 FROM #TableOpenXml WHERE IsCompleted = 1 AND IsAgain = 0 AND IsCanceled = 0))
137 BEGIN
138 --UPDATE SO_Detail -----------------------------------------------------BEGIN UPDATE SO_Detail-------------------------------------->
139 UPDATE SO_Detail
140 SET SODetailStatusID = 13, --> Äã giao cho khách hà ng
141 Visible = 1,
142 SoDetailIdRoot = CASE WHEN sd.SoDetailIdRoot IS NULL THEN sd.SODetailID ELSE sd.SoDetailIdRoot END,
143 SoDetailStatusLastUpdateTime=GETDATE()
144 FROM SO_Detail sd WITH (NOLOCK) INNER JOIN #TableOpenXml D ON sd.SODetailID=D.SoDetailId
145 WHERE
146 D.IsCompleted = 1 AND
147 D.IsAgain = 0 AND
148 D.IsCanceled = 0
149 --GHI LOG
150 INSERT INTO Log_Information
151 (
152 ObjectTypeId,ObjectId,ActionId,LogDetails,CreatedBy,CreatedDate,UID_AssignTo,OldStatusId,NewStatusId,StationId
153 )
154 SELECT 3,D.SoDetailId,
155 CASE
156 WHEN D.DeliveryFlowId = 1 THEN 15
157 WHEN D.DeliveryFlowId = 2 THEN 23
158 WHEN D.DeliveryFlowId = 3 THEN 14
159 WHEN D.DeliveryFlowId = 4 THEN 25
160 END,
161 N'Cáºp nháºt : ' + CAST(D.SoDetailId AS nvarchar(50)),@CurrentUser,GETDATE(),D.CodId,7,13,@StationId
162 FROM #TableOpenXml D
163 WHERE
164 D.IsCompleted = 1 AND
165 D.IsAgain = 0 AND
166 D.IsCanceled = 0
167 --HÀNG BÃŒNH THƯỜNG DeliveryFlow = 3 (Giao phải nháºp trạm):
168 --UPDATE So_Detail_Serial: StatusId = 62 trong trÆ°á»ng hợp sản phẩm có quản lý số serial và sản phẩm đó hoà n tất hêt
169 ;WITH SoDetailWithSerial AS
170 (
171 SELECT sd.SoDetailId, sd.ParentSoDetailId
172 FROM SO_Detail sd WITH (NOLOCK)
173 INNER JOIN #TableOpenXml AS s ON sd.SODetailID = s.SoDetailId
174 WHERE
175 s.IsCompleted = 1 AND -->TrÆ°á»ng hợp hoà n thà nh hết
176 s.IsAgain = 0 AND
177 s.IsCanceled = 0
178 UNION ALL
179 SELECT sd.SoDetailId, sd.ParentSoDetailId
180 FROM dbo.SO_Detail sd WITH (NOLOCK)
181 INNER JOIN SoDetailWithSerial sdr ON sdr.ParentSoDetailId = sd.SoDetailId
182 ), SoDetail AS
183 (
184 SELECT sds.SoDetailId,sds.SDSId
185 FROM SoDetailWithSerial s INNER JOIN dbo.SO_Detail_Serial sds ON s.SoDetailId=sds.SODetailID
186 )
187 UPDATE SO_Detail_Serial
188 SET StatusId = 62, -- ÄÃ GIAO CHO KHÃCH HÀNG
189 RollBackSerial = 0
190 FROM SO_Detail_Serial sds
191 INNER JOIN SoDetail sd WITH (NOLOCK) ON sds.SDSId = sd.SDSId
192 WHERE IsNull(sds.StatusId,60) IN (59,60)
193
194 --UPDATE SO_Detail_Serial
195 --SET StatusId = 62 -- ÄÃ GIAO CHO KHÃCH HÀNG
196 --FROM #TableOpenXml tox INNER JOIN SO_Detail sd ON sd.SODetailID=tox.SoDetailId
197 -- INNER JOIN SO_Detail_Serial sds ON sds.SODetailID=sd.SoDetailIdRoot
198 --WHERE IsNull(sds.StatusId,60) IN (59,60)
199 -------------------------------------------------------------------------END UPDATE SO_Detail-----------------------------------------
200 END
201 ---------------------------------------------------------------------------------END----------------------------------------------------------
202 --3. TRƯỜNG HỢP HẸN LẠI HẾT
203 ---------------------------------------------------------------------------------BEGIN------------------------------------------------------->
204 IF (EXISTS(SELECT 1 FROM #TableOpenXml WHERE IsCompleted = 0 AND IsAgain = 1 AND IsCanceled = 0))
205 BEGIN
206 --Update note và o SoDetails
207 UPDATE SO_Detail
208 SET IsDeliveryAgain = 1,
209 IsDeliveryDateAgain = 1,
210 UserModify = @CurrentUser,
211 IsPushCS = CASE WHEN ddsd.IsCallCustomerFail = 1 THEN 1 ELSE 0 END,
212 Note = CASE WHEN sd.Note IS NOT NULL THEN N'Hẹn lại. ' + sd.Note ELSE N'Hẹn lại. ' END,
213 DeliveryDateFrom = D.DateAgain,
214 DeliveryDateTo = D.DateAgainTo,
215 Visible = 1,
216 SoDetailIdRoot = CASE WHEN sd.SoDetailIdRoot IS NULL THEN sd.SODetailID ELSE sd.SoDetailIdRoot END,
217 SoDetailStatusLastUpdateTime=GETDATE(),
218 DeliveryPeriodTime = CASE WHEN D.IsAgain=1 THEN (SELECT TOP 1 dpt.Name FROM DeliveryPeriodTime dpt WHERE CONVERT(CHAR(8), D.DateAgain, 108) > dpt.FromTime AND CONVERT(CHAR(8), D.DateAgain, 108) <= dpt.ToTime) END,
219 DeliveryFromTimeUpdateTime = GETDATE()
220
221 FROM #TableOpenXml D INNER JOIN SO_Detail sd WITH (NOLOCK) ON sd.SODetailID=D.SoDetailId
222 LEFT JOIN DE_DeliverySlipsDetails ddsd WITH (NOLOCK) ON (ddsd.SoDetailId = sd.SODetailID AND ddsd.DeliverySlipsId = D.DeliverySlipsId)
223 WHERE
224 D.IsCompleted = 0 AND
225 D.IsAgain = 1 AND
226 D.IsCanceled = 0
227 -- =>GHI LOG
228 INSERT INTO dbo.Log_Information
229 (
230 ObjectTypeId,ObjectId,ActionId,LogDetails,CreatedBy,CreatedDate,UID_AssignTo,OldStatusId,NewStatusId,StationId
231 )
232 SELECT 3,D.SoDetailId,
233 CASE
234 WHEN D.DeliveryFlowId = 1 THEN 15
235 WHEN D.DeliveryFlowId = 2 THEN 23
236 WHEN D.DeliveryFlowId = 3 THEN 14
237 WHEN D.DeliveryFlowId = 4 THEN 25
238 END,
239 N'Hẹn lại : ' + CAST(D.SoDetailId AS nvarchar(50)),@CurrentUser,GETDATE(),D.CodId,7,7,@StationId
240 FROM #TableOpenXml D
241 WHERE
242 D.IsCompleted = 0 AND
243 D.IsAgain = 1 AND
244 D.IsCanceled = 0
245 END
246 ---------------------------------------------------------------------------------END----------------------------------------------------------
247 --4. TRƯỜNG HỢP HỦY HẾT
248 ---------------------------------------------------------------------------------BEGIN------------------------------------------------------->
249 IF (EXISTS(SELECT 1 FROM #TableOpenXml WHERE IsCompleted = 0 AND IsAgain = 0 AND IsCanceled = 1))
250 BEGIN
251 --UPDATE SO_Detail -----------------------------------------------------BEGIN UPDATE SO_Detail-------------------------------------->
252 UPDATE SO_Detail
253 SET SODetailStatusID = 16, --==> Hủy
254 ReasonID = D.ReasonCanceled,
255 ReasonCanceledOrther= D.ReasonCanceledOrther,
256 Visible = 1,
257 SoDetailIdRoot = CASE WHEN sd.SoDetailIdRoot IS NULL THEN sd.SODetailID ELSE sd.SoDetailIdRoot END,
258 SoDetailStatusLastUpdateTime=GETDATE()
259 FROM #TableOpenXml D INNER JOIN SO_Detail sd WITH (NOLOCK) ON sd.SODetailID = D.SoDetailId
260 WHERE
261 D.IsCompleted = 0 AND
262 D.IsAgain = 0 AND
263 D.IsCanceled = 1
264 --GHI LOG
265 INSERT INTO dbo.Log_Information
266 (
267 ObjectTypeId,ObjectId,ActionId,LogDetails,CreatedBy,CreatedDate,UID_AssignTo,OldStatusId,NewStatusId,StationId
268 )
269 SELECT 3,D.SoDetailId,
270 CASE
271 WHEN D.DeliveryFlowId = 1 THEN 15
272 WHEN D.DeliveryFlowId = 2 THEN 23
273 WHEN D.DeliveryFlowId = 3 THEN 14
274 WHEN D.DeliveryFlowId = 4 THEN 25
275 END,
276 N'Cáºp nháºt : ' + CAST(D.SoDetailId AS nvarchar(50)),@CurrentUser,GETDATE(),D.CodId,7,16,@StationId
277 FROM #TableOpenXml D
278 WHERE D.IsCompleted = 0 AND
279 D.IsAgain = 0 AND
280 D.IsCanceled = 1
281 -------------------------------------------------------------------------END UPDATE SO_Detail-----------------------------------------
282 END
283 ---------------------------------------------------------------------------------END----------------------------------------------------------
284 --5. TRƯỜNG HỢP HOÀN THÀNH + HẸN LẠI + HỦY
285 ---------------------------------------------------------------------------------BEGIN------------------------------------------------------->
286 IF (EXISTS(SELECT 1 FROM #TableOpenXml WHERE IsCompleted = 1 AND IsAgain = 1 AND IsCanceled = 1))
287 BEGIN
288 --UPDATE SO_Detail
289 UPDATE SO_Detail
290 SET Visible = 0,
291 SoDetailStatusLastUpdateTime=GETDATE()
292 WHERE SoDetailId IN (SELECT DS.SoDetailId
293 FROM #TableOpenXml DS INNER JOIN DE_DeliverySlipsDetails DED ON (DED.DeliverySlipsDetailId = DS.DeliverySlipsDetailId)
294 WHERE DS.IsCompleted = 1 AND
295 DS.IsAgain = 1 AND
296 DS.IsCanceled = 1)
297 --INSERT SO_Detail TRƯỜNG HỢP HOÀN THÀNH
298 INSERT INTO SO_Detail
299 (
300 LogisticSKULabel, Amount,SellPrice,UnitPriceExclVat,AdrVat,DiscountPercent,UnitPriceExclVatDiscount,SOId,Note,SODetailStatusID,
301 ReasonID,ManufactureBarcode,ClientMerchantId,ClientMerchantWarehouseId,ClientProductCateId,ClientProductId,DeliveryDateFrom,DeliveryDateTo,Visible,
302 [Priority],RequireSerial,TotalPaid,RemainingAmount,ParentSoDetailId,ClientPoId,ClientSoId,PoId,MTID,ProductTypeID,UnitBuyPrice,SID_CurrentStation,SoDetailIdRoot,SoDetailStatusLastUpdateTime,
303 CustomerRequestTime,CommitTime,FreshFoodTypeId
304 )
305 SELECT
306 sd.LogisticSKULabel, ISNULL(D.QuantityCompleted,0),sd.SellPrice,sd.UnitPriceExclVat,sd.AdrVat,sd.DiscountPercent,sd.UnitPriceExclVatDiscount,sd.SOId,N'Hoà n thà nh.',13,
307 0,sd.ManufactureBarcode,sd.ClientMerchantId,sd.ClientMerchantWarehouseId,sd.ClientProductCateId,sd.ClientProductId,sd.DeliveryDateFrom,sd.DeliveryDateTo,1,
308 1,sd.RequireSerial,
309 0,--CASE WHEN sd.TotalPaid < D.QuantityCompleted*sd.SellPrice THEN sd.TotalPaid ELSE D.QuantityCompleted*sd.SellPrice END,
310 0,--CASE WHEN sd.TotalPaid < D.QuantityCompleted*sd.SellPrice THEN D.QuantityCompleted*sd.SellPrice - sd.TotalPaid ELSE 0 END,
311 D.SoDetailId,sd.ClientPoId,sd.ClientSoId,sd.PoId,sd.MTID,sd.ProductTypeID,sd.UnitBuyPrice,sd.SID_CurrentStation,CASE WHEN sd.SoDetailIdRoot IS NULL THEN sd.SODetailID ELSE sd.SoDetailIdRoot END,GETDATE(),
312 sd.CustomerRequestTime,sd.CommitTime,sd.FreshFoodTypeId
313 FROM #TableOpenXml D INNER JOIN SO_Detail sd WITH (NOLOCK) ON sd.SODetailID = D.SoDetailId
314 WHERE D.IsCompleted = 1 AND
315 D.IsAgain = 1 AND
316 D.IsCanceled = 1
317 --Ghi log HOÀN THÀNH
318 INSERT INTO dbo.Log_Information
319 (
320 ObjectTypeId,ObjectId,ActionId,LogDetails,CreatedBy,CreatedDate,UID_AssignTo,OldStatusId,NewStatusId,StationId
321 )
322 SELECT 3,ISNULL(sd.SODetailID,0),
323 CASE
324 WHEN D.DeliveryFlowId = 1 THEN 15
325 WHEN D.DeliveryFlowId = 2 THEN 23
326 WHEN D.DeliveryFlowId = 3 THEN 14
327 WHEN D.DeliveryFlowId = 4 THEN 25
328 END,
329 N'Sinh ra từ SoDetailId : ' + CAST(D.SoDetailId AS nvarchar(50)),@CurrentUser,GETDATE(),D.CodId,7,13,@StationId
330 FROM #TableOpenXml D INNER JOIN SO_Detail sd WITH (NOLOCK) ON sd.ParentSoDetailId=D.SoDetailId
331 WHERE
332 D.IsCompleted = 1 AND
333 D.IsAgain = 1 AND
334 D.IsCanceled = 1 AND
335 sd.SODetailStatusID = 13
336 --INSERT SO_Detail TRƯỜNG HỢP HẸN LẠI
337 INSERT INTO SO_Detail
338 (
339 LogisticSKULabel, Amount,SellPrice,UnitPriceExclVat,AdrVat,DiscountPercent,UnitPriceExclVatDiscount,SOId,Note,SODetailStatusID,
340 ReasonID,ManufactureBarcode,ClientMerchantId,ClientMerchantWarehouseId,ClientProductCateId,ClientProductId,DeliveryDateFrom,DeliveryDateTo,Visible,
341 [Priority],RequireSerial,TotalPaid,RemainingAmount,ParentSoDetailId,ClientPoId,ClientSoId,PoId,MTID,ProductTypeID,UnitBuyPrice,SID_CurrentStation,IsDeliveryAgain,SoDetailIdRoot, SoDetailStatusLastUpdateTime,
342 CustomerRequestTime, CommitTime, FreshFoodTypeId,DeliveryPeriodTime,DeliveryFromTimeUpdateTime,IsPushCS,UserModify,IsDeliveryDateAgain
343 )
344 SELECT
345 sd.LogisticSKULabel, ISNULL(D.QuantityAgain,0),sd.SellPrice,sd.UnitPriceExclVat,sd.AdrVat,sd.DiscountPercent,sd.UnitPriceExclVatDiscount,sd.SOId,N'Hẹn lại.',7,
346 D.ReasonAgain,sd.ManufactureBarcode,sd.ClientMerchantId,sd.ClientMerchantWarehouseId,sd.ClientProductCateId,sd.ClientProductId,D.DateAgain,D.DateAgainTo,1,
347 2,sd.RequireSerial,
348 0,
349 0,--D.QuantityAgain * sd.SellPrice,
350 D.SoDetailId,sd.ClientPoId,sd.ClientSoId,sd.PoId,sd.MTID,sd.ProductTypeID,sd.UnitBuyPrice,sd.SID_CurrentStation,1,CASE WHEN sd.SoDetailIdRoot IS NULL THEN sd.SODetailID ELSE sd.SoDetailIdRoot END,GETDATE(),
351 sd.CustomerRequestTime, sd.CommitTime, sd.FreshFoodTypeId,
352 CASE WHEN D.IsAgain=1 THEN (SELECT TOP 1 dpt.Name FROM DeliveryPeriodTime dpt WHERE CONVERT(CHAR(8), D.DateAgain, 108) > dpt.FromTime AND CONVERT(CHAR(8), D.DateAgain, 108) <= dpt.ToTime) END,
353 GETDATE(),
354 CASE WHEN ddsd.IsCallCustomerFail = 1 THEN 1 ELSE 0 END,
355 @CurrentUser, 1
356 FROM #TableOpenXml D INNER JOIN SO_Detail sd WITH (NOLOCK) ON sd.SODetailID = D.SoDetailId
357 LEFT JOIN DE_DeliverySlipsDetails ddsd WITH (NOLOCK) ON (ddsd.SoDetailId = sd.SODetailID AND ddsd.DeliverySlipsId = D.DeliverySlipsId)
358 WHERE D.IsCompleted = 1 AND
359 D.IsAgain = 1 AND
360 D.IsCanceled = 1
361 --Ghi log HẸN LẠI
362 INSERT INTO Log_Information
363 (
364 ObjectTypeId,ObjectId,ActionId,LogDetails,CreatedBy,CreatedDate,UID_AssignTo,OldStatusId,NewStatusId,StationId
365 )
366 SELECT 3,ISNULL(sd.SODetailID,0),
367 CASE
368 WHEN D.DeliveryFlowId = 1 THEN 15
369 WHEN D.DeliveryFlowId = 2 THEN 23
370 WHEN D.DeliveryFlowId = 3 THEN 14
371 WHEN D.DeliveryFlowId = 4 THEN 25
372 END,
373 N'Sinh ra từ SoDetailId : ' + CAST(D.SoDetailId AS nvarchar(50)),@CurrentUser,GETDATE(),D.CodId,7,7,@StationId
374 FROM #TableOpenXml D INNER JOIN SO_Detail sd WITH (NOLOCK) ON sd.ParentSoDetailId=D.SoDetailId
375 WHERE
376 D.IsCompleted = 1 AND
377 D.IsAgain = 1 AND
378 D.IsCanceled = 1 AND
379 sd.SODetailStatusID = 7
380 --INSERT SO_Detail TRƯỜNG HỢP HỦY
381 INSERT INTO SO_Detail
382 (
383 LogisticSKULabel, Amount,SellPrice,UnitPriceExclVat,AdrVat,DiscountPercent,UnitPriceExclVatDiscount,SOId,Note,SODetailStatusID,
384 ReasonID,ManufactureBarcode,ClientMerchantId,ClientMerchantWarehouseId,ClientProductCateId,ClientProductId,DeliveryDateFrom,DeliveryDateTo,Visible,
385 [Priority],RequireSerial,TotalPaid,RemainingAmount,ParentSoDetailId,ClientPoId,ClientSoId,PoId,MTID,ProductTypeID,UnitBuyPrice,SID_CurrentStation,ReasonCanceledOrther,SoDetailIdRoot, SoDetailStatusLastUpdateTime,
386 CustomerRequestTime, CommitTime, FreshFoodTypeId
387 )
388 SELECT
389 sd.LogisticSKULabel, ISNULL(D.QuantityCanceled,0),sd.SellPrice,sd.UnitPriceExclVat,sd.AdrVat,sd.DiscountPercent,sd.UnitPriceExclVatDiscount,sd.SOId,N'Hủy.',16,
390 D.ReasonCanceled,sd.ManufactureBarcode,sd.ClientMerchantId,sd.ClientMerchantWarehouseId,sd.ClientProductCateId,sd.ClientProductId,sd.DeliveryDateFrom,sd.DeliveryDateTo,1,
391 3,sd.RequireSerial,0,0,D.SoDetailId,sd.ClientPoId,sd.ClientSoId,sd.PoId,sd.MTID,sd.ProductTypeID,sd.UnitBuyPrice,sd.SID_CurrentStation,D.ReasonCanceledOrther,CASE WHEN sd.SoDetailIdRoot IS NULL THEN sd.SODetailID ELSE sd.SoDetailIdRoot END,GETDATE(),
392 sd.CustomerRequestTime, sd.CommitTime, sd.FreshFoodTypeId
393 FROM #TableOpenXml D INNER JOIN SO_Detail sd WITH (NOLOCK) ON sd.SODetailID=D.SoDetailId
394 WHERE D.IsCompleted = 1 AND
395 D.IsAgain = 1 AND
396 D.IsCanceled = 1
397 -- Ghi log HỦY
398 INSERT INTO Log_Information
399 (
400 ObjectTypeId,ObjectId,ActionId,LogDetails,CreatedBy,CreatedDate,UID_AssignTo,OldStatusId,NewStatusId,StationId
401 )
402 SELECT 3,ISNULL(sd.SODetailID,0),
403 CASE
404 WHEN D.DeliveryFlowId = 1 THEN 15
405 WHEN D.DeliveryFlowId = 2 THEN 23
406 WHEN D.DeliveryFlowId = 3 THEN 14
407 WHEN D.DeliveryFlowId = 4 THEN 25
408 END,
409 N'Sinh ra từ SoDetailId : ' + CAST(D.SoDetailId AS nvarchar(50)),@CurrentUser,GETDATE(),D.CodId,7,16,@StationId
410 FROM #TableOpenXml D INNER JOIN SO_Detail sd WITH (NOLOCK) ON sd.ParentSoDetailId=D.SoDetailId
411 WHERE D.IsCompleted = 1 AND
412 D.IsAgain = 1 AND
413 D.IsCanceled = 1 AND
414 sd.SODetailStatusID = 16
415 END
416 ---------------------------------------------------------------------------------END----------------------------------------------------------
417 --6. TRƯỜNG HỢP HOÀN THÀNH + HẸN LẠI
418 ---------------------------------------------------------------------------------BEGIN------------------------------------------------------->
419 IF (EXISTS(SELECT 1 FROM #TableOpenXml WHERE IsCompleted = 1 AND IsAgain = 1 AND IsCanceled = 0))
420 BEGIN
421 --UPDATE SO_Detail
422 UPDATE SO_Detail
423 SET Visible = 0,
424 SoDetailStatusLastUpdateTime=GETDATE()
425 WHERE SoDetailId IN ( SELECT D.SoDetailId
426 FROM #TableOpenXml D INNER JOIN DE_DeliverySlipsDetails DED ON (DED.DeliverySlipsDetailId = D.DeliverySlipsDetailId)
427 WHERE D.IsCompleted = 1 AND
428 D.IsAgain = 1 AND
429 D.IsCanceled = 0 )
430 --INSERT SO_Detail TRƯỜNG HỢP HOÀN THÀNH
431 INSERT INTO SO_Detail
432 (
433 LogisticSKULabel, Amount,SellPrice,UnitPriceExclVat,AdrVat,DiscountPercent,UnitPriceExclVatDiscount,SOId,Note,SODetailStatusID,
434 ReasonID,ManufactureBarcode,ClientMerchantId,ClientMerchantWarehouseId,ClientProductCateId,ClientProductId,DeliveryDateFrom,DeliveryDateTo,Visible,
435 [Priority],RequireSerial,TotalPaid,RemainingAmount,ParentSoDetailId,ClientPoId,ClientSoId,PoId,MTID,ProductTypeID,UnitBuyPrice,SID_CurrentStation,SoDetailIdRoot, SoDetailStatusLastUpdateTime,
436 CustomerRequestTime, CommitTime, FreshFoodTypeId
437 )
438 SELECT
439 sd.LogisticSKULabel, ISNULL(D.QuantityCompleted,0),sd.SellPrice,sd.UnitPriceExclVat,sd.AdrVat,sd.DiscountPercent,sd.UnitPriceExclVatDiscount,sd.SOId,N'Hoà n thà nh.',13,
440 0,sd.ManufactureBarcode,sd.ClientMerchantId,sd.ClientMerchantWarehouseId,sd.ClientProductCateId,sd.ClientProductId,sd.DeliveryDateFrom,sd.DeliveryDateTo,1,
441 1,sd.RequireSerial,
442 0,--CASE WHEN sd.TotalPaid < D.QuantityCompleted*sd.SellPrice THEN sd.TotalPaid ELSE D.QuantityCompleted*sd.SellPrice END,
443 0,--CASE WHEN sd.TotalPaid < D.QuantityCompleted*sd.SellPrice THEN D.QuantityCompleted*sd.SellPrice - sd.TotalPaid ELSE 0 END,
444 D.SoDetailId,sd.ClientPoId,sd.ClientSoId,sd.PoId,sd.MTID,sd.ProductTypeID,sd.UnitBuyPrice,sd.SID_CurrentStation,CASE WHEN sd.SoDetailIdRoot IS NULL THEN sd.SODetailID ELSE sd.SoDetailIdRoot END,GETDATE(),
445 sd.CustomerRequestTime, sd.CommitTime, sd.FreshFoodTypeId
446 FROM #TableOpenXml D INNER JOIN SO_Detail sd WITH (NOLOCK) ON sd.SODetailID = D.SoDetailId
447 WHERE D.IsCompleted = 1 AND
448 D.IsAgain = 1 AND
449 D.IsCanceled = 0
450 --Ghi log HOÀN THÀNH
451 INSERT INTO Log_Information
452 (
453 ObjectTypeId,ObjectId,ActionId,LogDetails,CreatedBy,CreatedDate,UID_AssignTo,OldStatusId,NewStatusId,StationId
454 )
455 SELECT 3,ISNULL(sd.SODetailID,0),
456 CASE
457 WHEN D.DeliveryFlowId = 1 THEN 15
458 WHEN D.DeliveryFlowId = 2 THEN 23
459 WHEN D.DeliveryFlowId = 3 THEN 14
460 WHEN D.DeliveryFlowId = 4 THEN 25
461 END,
462 N'Sinh ra từ SoDetailId : ' + CAST(D.SoDetailId AS nvarchar(50)),@CurrentUser,GETDATE(),D.CodId,7,13,@StationId
463 FROM #TableOpenXml D INNER JOIN SO_Detail sd WITH (NOLOCK) ON sd.ParentSoDetailId = D.SoDetailId
464 WHERE D.IsCompleted = 1 AND
465 D.IsAgain = 1 AND
466 D.IsCanceled = 0 AND
467 sd.SODetailStatusID = 13
468 --HẸN LẠI
469 INSERT INTO SO_Detail
470 (
471 LogisticSKULabel, Amount,SellPrice,UnitPriceExclVat,AdrVat,DiscountPercent,UnitPriceExclVatDiscount,SOId,Note,SODetailStatusID,
472 ReasonID,ManufactureBarcode,ClientMerchantId,ClientMerchantWarehouseId,ClientProductCateId,ClientProductId,DeliveryDateFrom,DeliveryDateTo,Visible,
473 [Priority],RequireSerial,TotalPaid,RemainingAmount,ParentSoDetailId,ClientPoId,ClientSoId,PoId,MTID,ProductTypeID,UnitBuyPrice,SID_CurrentStation,IsDeliveryAgain,SoDetailIdRoot, SoDetailStatusLastUpdateTime,
474 CustomerRequestTime, CommitTime, FreshFoodTypeId,DeliveryPeriodTime,DeliveryFromTimeUpdateTime,IsPushCS,UserModify, IsDeliveryDateAgain
475 )
476 SELECT
477 sd.LogisticSKULabel, ISNULL(D.QuantityAgain,0),sd.SellPrice,sd.UnitPriceExclVat,sd.AdrVat,sd.DiscountPercent,sd.UnitPriceExclVatDiscount,sd.SOId,N'Hẹn lại.',7,
478 D.ReasonAgain,sd.ManufactureBarcode,sd.ClientMerchantId,sd.ClientMerchantWarehouseId,sd.ClientProductCateId,sd.ClientProductId,D.DateAgain,D.DateAgainTo,1,
479 2,sd.RequireSerial,
480 0,
481 0,--D.QuantityAgain * sd.SellPrice,
482 D.SoDetailId,sd.ClientPoId,sd.ClientSoId,sd.PoId,sd.MTID,sd.ProductTypeID,sd.UnitBuyPrice,sd.SID_CurrentStation,1,CASE WHEN sd.SoDetailIdRoot IS NULL THEN sd.SODetailID ELSE sd.SoDetailIdRoot END,GETDATE(),
483 sd.CustomerRequestTime, sd.CommitTime, sd.FreshFoodTypeId,
484 CASE WHEN D.IsAgain=1 THEN (SELECT TOP 1 dpt.Name FROM DeliveryPeriodTime dpt WHERE CONVERT(CHAR(8), D.DateAgain, 108) > dpt.FromTime AND CONVERT(CHAR(8), D.DateAgain, 108) <= dpt.ToTime) END,
485 GETDATE(),
486 CASE WHEN ddsd.IsCallCustomerFail = 1 THEN 1 ELSE 0 END,
487 @CurrentUser, 1
488 FROM #TableOpenXml D INNER JOIN SO_Detail sd WITH (NOLOCK) ON sd.SODetailID = D.SoDetailId
489 LEFT JOIN DE_DeliverySlipsDetails ddsd WITH (NOLOCK) ON (ddsd.SoDetailId = sd.SODetailID AND ddsd.DeliverySlipsId = D.DeliverySlipsId)
490 WHERE D.IsCompleted = 1 AND
491 D.IsAgain = 1 AND
492 D.IsCanceled = 0
493 --Ghi log HẸN LẠI
494 INSERT INTO Log_Information
495 (
496 ObjectTypeId,ObjectId,ActionId,LogDetails,CreatedBy,CreatedDate,UID_AssignTo,OldStatusId,NewStatusId,StationId
497 )
498 SELECT 3,ISNULL(sd.SODetailID,0),
499 CASE
500 WHEN D.DeliveryFlowId = 1 THEN 15
501 WHEN D.DeliveryFlowId = 2 THEN 23
502 WHEN D.DeliveryFlowId = 3 THEN 14
503 WHEN D.DeliveryFlowId = 4 THEN 25
504 END,
505 N'Sinh ra từ SoDetailId : ' + CAST(D.SoDetailId AS nvarchar(50)),@CurrentUser,GETDATE(),D.CodId,7,7,@StationId
506 FROM #TableOpenXml D INNER JOIN SO_Detail sd WITH (NOLOCK) ON sd.ParentSoDetailId=D.SoDetailId
507 WHERE
508 D.IsCompleted = 1 AND
509 D.IsAgain = 1 AND
510 D.IsCanceled = 0 AND
511 sd.SODetailStatusID = 7
512 END
513 ---------------------------------------------------------------------------------END----------------------------------------------------------
514 --7. TRƯỜNG HỢP HOÀN THÀNH + HỦY
515 ---------------------------------------------------------------------------------BEGIN------------------------------------------------------->
516 IF (EXISTS(SELECT 1 FROM #TableOpenXml WHERE IsCompleted = 1 AND IsAgain = 0 AND IsCanceled = 1))
517 BEGIN
518 --UPDATE SO_Detail
519 UPDATE SO_Detail
520 SET Visible = 0,
521 SoDetailStatusLastUpdateTime=GETDATE()
522 WHERE SoDetailId IN ( SELECT D.SoDetailId
523 FROM #TableOpenXml D INNER JOIN DE_DeliverySlipsDetails DED ON (DED.DeliverySlipsDetailId = D.DeliverySlipsDetailId)
524 WHERE D.IsCompleted = 1 AND
525 D.IsAgain = 0 AND
526 D.IsCanceled = 1 )
527 --INSERT SO_Detail TRƯỜNG HỢP HOÀN THÀNH
528 INSERT INTO SO_Detail
529 (
530 LogisticSKULabel, Amount,SellPrice,UnitPriceExclVat,AdrVat,DiscountPercent,UnitPriceExclVatDiscount,SOId,Note,SODetailStatusID,
531 ReasonID,ManufactureBarcode,ClientMerchantId,ClientMerchantWarehouseId,ClientProductCateId,ClientProductId,DeliveryDateFrom,DeliveryDateTo,Visible,
532 [Priority],RequireSerial,TotalPaid,RemainingAmount,ParentSoDetailId,ClientPoId,ClientSoId,PoId,MTID,ProductTypeID,UnitBuyPrice,SID_CurrentStation,SoDetailIdRoot, SoDetailStatusLastUpdateTime,
533 CustomerRequestTime, CommitTime, FreshFoodTypeId
534 )
535 SELECT sd.LogisticSKULabel, ISNULL(D.QuantityCompleted,0),sd.SellPrice,sd.UnitPriceExclVat,sd.AdrVat,sd.DiscountPercent,sd.UnitPriceExclVatDiscount,sd.SOId,N'Hoà n thà nh.',13,
536 0,sd.ManufactureBarcode,sd.ClientMerchantId,sd.ClientMerchantWarehouseId,sd.ClientProductCateId,sd.ClientProductId,sd.DeliveryDateFrom,sd.DeliveryDateTo,1,
537 1,sd.RequireSerial,
538 0,--CASE WHEN sd.TotalPaid < D.QuantityCompleted*sd.SellPrice THEN sd.TotalPaid ELSE D.QuantityCompleted*sd.SellPrice END,
539 0,--CASE WHEN sd.TotalPaid < D.QuantityCompleted*sd.SellPrice THEN D.QuantityCompleted*sd.SellPrice - sd.TotalPaid ELSE 0 END,
540 D.SoDetailId,sd.ClientPoId,sd.ClientSoId,sd.PoId,sd.MTID,sd.ProductTypeID,sd.UnitBuyPrice,sd.SID_CurrentStation,CASE WHEN sd.SoDetailIdRoot IS NULL THEN sd.SODetailID ELSE sd.SoDetailIdRoot END,GETDATE(),
541 sd.CustomerRequestTime, sd.CommitTime, sd.FreshFoodTypeId
542 FROM #TableOpenXml D INNER JOIN SO_Detail sd WITH (NOLOCK) ON sd.SODetailID = D.SoDetailId
543 WHERE D.IsCompleted = 1 AND
544 D.IsAgain = 0 AND
545 D.IsCanceled = 1
546 --Ghi log HOÀN THÀNH
547 INSERT INTO Log_Information
548 (
549 ObjectTypeId,ObjectId,ActionId,LogDetails,CreatedBy,CreatedDate,UID_AssignTo,OldStatusId,NewStatusId,StationId
550 )
551 SELECT 3,ISNULL(sd.SODetailID,0),
552 CASE
553 WHEN D.DeliveryFlowId = 1 THEN 15
554 WHEN D.DeliveryFlowId = 2 THEN 23
555 WHEN D.DeliveryFlowId = 3 THEN 14
556 WHEN D.DeliveryFlowId = 4 THEN 25
557 END,
558 N'Sinh ra từ SoDetailId : ' + CAST(D.SoDetailId AS nvarchar(50)),@CurrentUser,GETDATE(),D.CodId,7,13,@StationId
559 FROM #TableOpenXml D INNER JOIN SO_Detail sd WITH (NOLOCK) ON sd.ParentSoDetailId = D.SoDetailId
560 WHERE D.IsCompleted = 1 AND
561 D.IsAgain = 0 AND
562 D.IsCanceled = 1 AND
563 sd.SODetailStatusID = 13
564 --INSERT SO_Detail TRƯỜNG HỢP HỦY
565 INSERT INTO SO_Detail
566 (
567 LogisticSKULabel, Amount,SellPrice,UnitPriceExclVat,AdrVat,DiscountPercent,UnitPriceExclVatDiscount,SOId,Note,SODetailStatusID,
568 ReasonID,ManufactureBarcode,ClientMerchantId,ClientMerchantWarehouseId,ClientProductCateId,ClientProductId,DeliveryDateFrom,DeliveryDateTo,Visible,
569 [Priority],RequireSerial,TotalPaid,RemainingAmount,ParentSoDetailId,ClientPoId,ClientSoId,PoId,MTID,ProductTypeID,UnitBuyPrice,SID_CurrentStation,ReasonCanceledOrther,SoDetailIdRoot, SoDetailStatusLastUpdateTime,
570 CustomerRequestTime, CommitTime, FreshFoodTypeId
571 )
572 SELECT sd.LogisticSKULabel, ISNULL(D.QuantityCanceled,0),sd.SellPrice,sd.UnitPriceExclVat,sd.AdrVat,sd.DiscountPercent,sd.UnitPriceExclVatDiscount,sd.SOId,N'Hủy.',16,
573 D.ReasonCanceled,sd.ManufactureBarcode,sd.ClientMerchantId,sd.ClientMerchantWarehouseId,sd.ClientProductCateId,sd.ClientProductId,sd.DeliveryDateFrom,sd.DeliveryDateTo,1,
574 2,sd.RequireSerial,0,0,D.SoDetailId,sd.ClientPoId,sd.ClientSoId,sd.PoId,sd.MTID,sd.ProductTypeID,sd.UnitBuyPrice,sd.SID_CurrentStation,D.ReasonCanceledOrther,CASE WHEN sd.SoDetailIdRoot IS NULL THEN sd.SODetailID ELSE sd.SoDetailIdRoot END,GETDATE(),
575 sd.CustomerRequestTime, sd.CommitTime, sd.FreshFoodTypeId
576 FROM #TableOpenXml D INNER JOIN SO_Detail sd WITH (NOLOCK) ON sd.SODetailID = D.SoDetailId
577 WHERE
578 D.IsCompleted = 1 AND
579 D.IsAgain = 0 AND
580 D.IsCanceled = 1
581 --Ghi log HỦY
582 INSERT INTO Log_Information
583 (
584 ObjectTypeId,ObjectId,ActionId,LogDetails,CreatedBy,CreatedDate,UID_AssignTo,OldStatusId,NewStatusId,StationId
585 )
586 SELECT 3,ISNULL(sd.SODetailID,0),
587 CASE
588 WHEN D.DeliveryFlowId = 1 THEN 15
589 WHEN D.DeliveryFlowId = 2 THEN 23
590 WHEN D.DeliveryFlowId = 3 THEN 14
591 WHEN D.DeliveryFlowId = 4 THEN 25
592 END,
593 N'Sinh ra từ SoDetailId : ' + CAST(D.SoDetailId AS nvarchar(50)),@CurrentUser,GETDATE(),D.CodId,7,16,@StationId
594 FROM #TableOpenXml D INNER JOIN dbo.SO_Detail sd WITH (NOLOCK) ON sd.ParentSoDetailId = D.SoDetailId
595 WHERE
596 D.IsCompleted = 1 AND
597 D.IsAgain = 0 AND
598 D.IsCanceled = 1 AND
599 sd.SODetailStatusID = 16
600 END
601 ---------------------------------------------------------------------------------END----------------------------------------------------------
602 --8. TRƯỜNG HỢP HẸN LẠI + HỦY
603 ---------------------------------------------------------------------------------BEGIN------------------------------------------------------->
604 IF (EXISTS(SELECT 1 FROM #TableOpenXml WHERE IsCompleted = 0 AND IsAgain = 1 AND IsCanceled = 1))
605 BEGIN
606 --UPDATE SO_Detail ---------------------------------------------------------BEGIN UPDATE SO_Detail-------------------------------------->
607 UPDATE SO_Detail
608 SET Visible = 0,
609 SoDetailStatusLastUpdateTime=GETDATE()
610 WHERE SoDetailId IN ( SELECT D.SoDetailId
611 FROM #TableOpenXml D INNER JOIN DE_DeliverySlipsDetails DED ON (DED.DeliverySlipsDetailId = D.DeliverySlipsDetailId)
612 WHERE D.IsCompleted = 0 AND
613 D.IsAgain = 1 AND
614 D.IsCanceled = 1 )
615 --INSERT SO_Detail TRƯỜNG HỢP HẸN LẠI
616 INSERT INTO SO_Detail
617 (
618 LogisticSKULabel, Amount,SellPrice,UnitPriceExclVat,AdrVat,DiscountPercent,UnitPriceExclVatDiscount,SOId,Note,SODetailStatusID,
619 ReasonID,ManufactureBarcode,ClientMerchantId,ClientMerchantWarehouseId,ClientProductCateId,ClientProductId,DeliveryDateFrom,DeliveryDateTo,Visible,
620 [Priority],RequireSerial,TotalPaid,RemainingAmount,ParentSoDetailId,ClientPoId,ClientSoId,PoId,MTID,ProductTypeID,UnitBuyPrice,SID_CurrentStation,IsDeliveryAgain,SoDetailIdRoot, SoDetailStatusLastUpdateTime,
621 CustomerRequestTime, CommitTime, FreshFoodTypeId,DeliveryPeriodTime,DeliveryFromTimeUpdateTime,IsPushCS,UserModify, IsDeliveryDateAgain
622 )
623 SELECT sd.LogisticSKULabel, ISNULL(D.QuantityAgain,0),sd.SellPrice,sd.UnitPriceExclVat,sd.AdrVat,sd.DiscountPercent,sd.UnitPriceExclVatDiscount,sd.SOId,N'Hẹn lại.',7,
624 D.ReasonAgain,sd.ManufactureBarcode,sd.ClientMerchantId,sd.ClientMerchantWarehouseId,sd.ClientProductCateId,sd.ClientProductId,D.DateAgain,D.DateAgainTo,1,
625 1,sd.RequireSerial,
626 0,
627 0,--D.QuantityAgain * sd.SellPrice,
628 D.SoDetailId,sd.ClientPoId,sd.ClientSoId,sd.PoId,sd.MTID,sd.ProductTypeID,sd.UnitBuyPrice,sd.SID_CurrentStation,1,CASE WHEN sd.SoDetailIdRoot IS NULL THEN sd.SODetailID ELSE sd.SoDetailIdRoot END,GETDATE(),
629 sd.CustomerRequestTime, sd.CommitTime, sd.FreshFoodTypeId,
630 CASE WHEN D.IsAgain=1 THEN (SELECT TOP 1 dpt.Name FROM DeliveryPeriodTime dpt WHERE CONVERT(CHAR(8), D.DateAgain, 108) > dpt.FromTime AND CONVERT(CHAR(8), D.DateAgain, 108) <= dpt.ToTime) END,
631 GETDATE(),
632 CASE WHEN ddsd.IsCallCustomerFail = 1 THEN 1 ELSE 0 END,
633 @CurrentUser, 1
634 FROM #TableOpenXml D INNER JOIN SO_Detail sd WITH (NOLOCK) ON sd.SODetailID = D.SoDetailId
635 LEFT JOIN DE_DeliverySlipsDetails ddsd WITH (NOLOCK) ON (ddsd.SoDetailId = sd.SODetailID AND ddsd.DeliverySlipsId = D.DeliverySlipsId)
636 WHERE D.IsCompleted = 0 AND
637 D.IsAgain = 1 AND
638 D.IsCanceled = 1
639 --Ghi log HẸN LẠI
640 INSERT INTO Log_Information
641 (
642 ObjectTypeId,ObjectId,ActionId,LogDetails,CreatedBy,CreatedDate,UID_AssignTo,OldStatusId,NewStatusId,StationId
643 )
644 SELECT 3,ISNULL(sd.SODetailID,0),
645 CASE
646 WHEN D.DeliveryFlowId = 1 THEN 15
647 WHEN D.DeliveryFlowId = 2 THEN 23
648 WHEN D.DeliveryFlowId = 3 THEN 14
649 WHEN D.DeliveryFlowId = 4 THEN 25
650 END,
651 N'Sinh ra từ SoDetailId : ' + CAST(D.SoDetailId AS nvarchar(50)),@CurrentUser,GETDATE(),D.CodId,7,7,@StationId
652 FROM #TableOpenXml D INNER JOIN SO_Detail sd WITH (NOLOCK) ON sd.ParentSoDetailId=D.SoDetailId
653 WHERE D.IsCompleted = 0 AND
654 D.IsAgain = 1 AND
655 D.IsCanceled = 1 AND
656 sd.SODetailStatusID = 7
657 --INSERT SO_Detail TRƯỜNG HỢP HỦY
658 INSERT INTO SO_Detail
659 (
660 LogisticSKULabel, Amount,SellPrice,UnitPriceExclVat,AdrVat,DiscountPercent,UnitPriceExclVatDiscount,SOId,Note,SODetailStatusID,
661 ReasonID,ManufactureBarcode,ClientMerchantId,ClientMerchantWarehouseId,ClientProductCateId,ClientProductId,DeliveryDateFrom,DeliveryDateTo,Visible,
662 [Priority],RequireSerial,TotalPaid,RemainingAmount,ParentSoDetailId,ClientPoId,ClientSoId,PoId,MTID,ProductTypeID,UnitBuyPrice,SID_CurrentStation,ReasonCanceledOrther,SoDetailIdRoot, SoDetailStatusLastUpdateTime,
663 CustomerRequestTime, CommitTime, FreshFoodTypeId
664 )
665 SELECT sd.LogisticSKULabel, ISNULL(D.QuantityCanceled,0),sd.SellPrice,sd.UnitPriceExclVat,sd.AdrVat,sd.DiscountPercent,sd.UnitPriceExclVatDiscount,sd.SOId,N'Hủy.',16,
666 D.ReasonCanceled,sd.ManufactureBarcode,sd.ClientMerchantId,sd.ClientMerchantWarehouseId,sd.ClientProductCateId,sd.ClientProductId,sd.DeliveryDateFrom,sd.DeliveryDateTo,1,
667 1,sd.RequireSerial,0,0,D.SoDetailId,sd.ClientPoId,sd.ClientSoId,sd.PoId,sd.MTID,sd.ProductTypeID,sd.UnitBuyPrice,sd.SID_CurrentStation,D.ReasonCanceledOrther,CASE WHEN sd.SoDetailIdRoot IS NULL THEN sd.SODetailID ELSE sd.SoDetailIdRoot END,GETDATE(),
668 sd.CustomerRequestTime, sd.CommitTime, sd.FreshFoodTypeId
669 FROM #TableOpenXml D INNER JOIN SO_Detail sd WITH (NOLOCK) ON sd.SODetailID=D.SoDetailId
670 WHERE D.IsCompleted = 0 AND
671 D.IsAgain = 1 AND
672 D.IsCanceled = 1
673 -- Ghi log HỦY
674 INSERT INTO Log_Information
675 (
676 ObjectTypeId,ObjectId,ActionId,LogDetails,CreatedBy,CreatedDate,UID_AssignTo,OldStatusId,NewStatusId,StationId
677 )
678 SELECT 3,ISNULL(sd.SODetailID,0),
679 CASE
680 WHEN D.DeliveryFlowId = 1 THEN 15
681 WHEN D.DeliveryFlowId = 2 THEN 23
682 WHEN D.DeliveryFlowId = 3 THEN 14
683 WHEN D.DeliveryFlowId = 4 THEN 25
684 END,
685 N'Sinh ra từ SoDetailId : ' + CAST(D.SoDetailId AS nvarchar(50)),@CurrentUser,GETDATE(),D.CodId,7,16,@StationId
686 FROM #TableOpenXml D INNER JOIN SO_Detail sd WITH (NOLOCK) ON sd.ParentSoDetailId= D.SoDetailId
687 WHERE
688 D.IsCompleted = 0 AND
689 D.IsAgain = 1 AND
690 D.IsCanceled = 1 AND
691 sd.SODetailStatusID = 16
692 -----------------------------------------------------------------------------END UPDATE SO_Detail-----------------------------------------
693 END
694 ---------------------------------------------------------------------------------END----------------------------------------------------------
695 --9. XUẤT KHO ẢO Äá»I VỚI HÀNH CK, CẬP NHẬT TRẠNG THÃI SO, CẬP NHẬT TRẠNG THÃI PHIẾU + TÃNH TIỀN + CẬP NHẬT LẠI TIỀN Äà TRẢ
696 --9.1 TRƯỜNG HỢP LÀ HÀNG CỒNG KỀNH (DeliveryFlowId = 2)
697 IF(EXISTS(SELECT 1 FROM dbo.#TableOpenXml WHERE DeliveryFlowId = 2 ))
698 BEGIN
699 --=>XUẤT KHO CHO CÃC SẢN PHẨM HOÀN THÀNH
700 DECLARE @warehouseSlipId BIGINT = 0
701 DECLARE @stationWHId INT = 0
702 SELECT @stationWHId = COALESCE([StationWHId],0) FROM [Station_WHType] WHERE [SID]= @StationId AND [SWHTID] = 1
703 -- Tạo phiếu xuất kho
704 DECLARE @CodViewByDe INT=0
705 SELECT @CodViewByDe = SOD.CodId FROM dbo.#TableOpenXml SOD
706 WHERE SOD.DeliveryFlowId=2
707 GROUP BY SOD.DeliverySlipsId,SOD.CodId
708 INSERT INTO WH_WarehouseSlips
709 (
710 UserIdRecipient,
711 UserIdDelivery,
712 StationWHId,
713 ActionTypeId,
714 DestinationStationId,
715 IsCompleted,
716 DateCompleted,
717 CompletedBy
718 )
719 SELECT @CurrentUser, @CodViewByDe, @stationWHId, 5, null, 1, GETDATE() , @CurrentUser
720 SET @warehouseSlipId = @@IDENTITY
721 -- Tạo chi tiết của phiếu xuất kho
722 INSERT INTO [WH_WarehouseSlip_Details]
723 (
724 WarehouseSlipId,
725 SoDetailId,
726 Quantity,
727 QuantityReceived
728 )
729 SELECT @warehouseSlipId, SOD.SoDetailId, SOD.Quantity, SOD.Quantity
730 FROM OPENXML (@idoc, '/ArrayOfDeSlipsBo/DeSlipsBo', 8)
731 WITH
732 (
733 SoDetailId BIGINT 'SoDetailId',
734 DeliverySlipsId INT 'DeliverySlipsId',
735 QuantityCompleted INT 'QuantityCompleted',
736 Quantity INT 'Quantity',
737 DeliveryFlowId INT 'DeliveryFlowId'
738 ) SOD
739 WHERE
740 SOD.DeliveryFlowId = 2
741 --Ghi log
742 INSERT INTO WH_DeliverySlip_Logs
743 (
744 DeliverySlipsId,
745 WarehouseSlipId,
746 CreatedBy
747 )
748 SELECT DED.DeliverySlipsId,@warehouseSlipId, @CurrentUser
749 FROM dbo.#TableOpenXml DED
750 WHERE DED.DeliveryFlowId = 2
751 GROUP BY DED.DeliverySlipsId
752 --INSERT WH_ImportSource
753 INSERT INTO WH_ImportSource
754 (
755 ImportSourceId,
756 WhSlipId,
757 DateCreated,
758 WhImportSourceTypeId
759 )
760 SELECT SOD.DeliverySlipsId,@warehouseSlipId,GETDATE(),4
761 FROM dbo.#TableOpenXml SOD
762 WHERE SOD.DeliveryFlowId = 2
763 GROUP BY SOD.DeliverySlipsId
764 END
765 --9.2 CẬP NHẬT TRẠNG THÃI SO
766 --Update SO
767 UPDATE dbo.SO
768 SET dbo.SO.SOStatusId = (SELECT st.Id FROM dbo.SOStatus st INNER JOIN (SELECT MIN(st1.Priority) AS MinPriority FROM dbo.SO_Detail sd WITH (NOLOCK) INNER JOIN dbo.SOStatus st1 ON sd.SODetailStatusID = st1.Id WHERE sd.ClientSoId = s.ClientSoId AND sd.Visible = 1) ts ON st.Priority = ts.MinPriority)
769 FROM dbo.SO s INNER JOIN #TableOpenXml AS Sml ON Sml.ClientSoId = s.ClientSoId
770 --Ghi LOG
771 --INSERT INTO [dbo].[Log_Information]
772 -- ([ObjectTypeId]
773 -- ,[ObjectId]
774 -- ,[ActionId]
775 -- ,[LogDetails]
776 -- ,[CreatedBy]
777 -- ,[CreatedDate]
778 -- ,[UID_AssignTo]
779 -- ,[OldStatusId]
780 -- ,[NewStatusId])
781 --SELECT 1,
782 -- s.ClientSoId,
783 -- CASE
784 -- WHEN Sml.DeliveryFlowId = 1 THEN 15
785 -- WHEN Sml.DeliveryFlowId = 2 THEN 23
786 -- WHEN Sml.DeliveryFlowId = 3 THEN 14
787 -- WHEN Sml.DeliveryFlowId = 4 THEN 25
788 -- END,
789 -- NULL,
790 -- @CurrentUser,
791 -- GETDATE(),
792 -- NULL,
793 -- s.SOStatusId,
794 -- (SELECT st.Id FROM dbo.SOStatus st WITH (NOLOCK) INNER JOIN (SELECT MIN(st1.Priority) AS MinPriority FROM dbo.SO_Detail sd WITH (NOLOCK) INNER JOIN dbo.SOStatus st1 WITH (NOLOCK) ON sd.SODetailStatusID = st1.Id WHERE sd.ClientSoId = s.ClientSoId AND sd.Visible = 1) ts ON st.Priority = ts.MinPriority)
795 -- FROM dbo.SO s WITH (NOLOCK) INNER JOIN #TableOpenXml AS Sml ON Sml.ClientSoId = s.ClientSoId
796 -- WHERE s.SOStatusId < (SELECT st.Id FROM dbo.SOStatus st WITH (NOLOCK) INNER JOIN (SELECT MIN(st1.Priority) AS MinPriority FROM dbo.SO_Detail sd WITH (NOLOCK) INNER JOIN dbo.SOStatus st1 WITH (NOLOCK) ON sd.SODetailStatusID = st1.Id WHERE sd.ClientSoId = s.ClientSoId AND sd.Visible = 1) ts ON st.Priority = ts.MinPriority)
797
798 --9.3 KIỂM TRA CẬP NHẬT PHIẾU GIAO HÀNG
799 --Update DE_DeliverySlips--------------------------------------------------------BEGIN------------------------------------------------------->
800 UPDATE
801 DE_DeliverySlips
802 SET
803 [StatusId] = CASE WHEN EXISTS(SELECT 1 FROM DE_DeliverySlipsDetails WHERE (IsCanceled = 1 OR IsAgain = 1) AND DeliverySlipsId = D.DeliverySlipsId) THEN 28 ELSE 29 END,
804 [DateCompleted] = GETDATE(),
805 [DateModified] = GETDATE(),
806 [ModifiedBy] = @CurrentUser
807 FROM
808 DE_DeliverySlips de INNER JOIN #TableOpenXml D ON (de.DeliverySlipsId = D.DeliverySlipsId)
809 WHERE
810 NOT EXISTS (
811 SELECT 1
812 FROM
813 DE_DeliverySlipsDetails ddsd
814 WHERE
815 ddsd.Visible = 1
816 AND ddsd.DeliverySlipsId = de.DeliverySlipsId
817 AND (isnull(ddsd.Status,0) <> 29))
818 ---------------------------------------------------------------------------------END----------------------------------------------------------
819 --9.4 CẬP NHẬT LẠI TIỀN ÄÃ TRẢ
820 ---------------------------------------------------------------------------------BEGIN------------------------------------------------------->
821 --@DESDetail
822 IF EXISTS (SELECT 1 FROM #TableOpenXml D WHERE D.IsAgain = 1 OR D.IsCanceled = 1) AND
823 NOT EXISTS (SELECT 1 FROM #TableOpenXml D INNER JOIN dbo.UM_Users uu ON D.CodId = uu.UserId WHERE isnull(uu.UnitTransportId,0) > 2 AND D.IsAgain = 1)
824 BEGIN
825 EXEC APIClient_DE_RollBackTotalPaidOfPacking
826 @DeliverySlipsId = 0,
827 @ListSoDetails = @DESDetail,
828 @CurrentUser = @CurrentUser,
829 @SesstionKey = @SesstionKey
830 END
831
832 --DECLARE @DeliverySlipsIdCurrent INT
833 --DECLARE @ListSoDetails NVARCHAR(MAX)
834 --DECLARE DeCursor CURSOR FOR
835 --SELECT D.DeliverySlipsId
836 --FROM #TableOpenXml D
837 --GROUP BY D.DeliverySlipsId
838 --OPEN DeCursor
839 --FETCH NEXT FROM DeCursor
840 -- INTO @DeliverySlipsIdCurrent
841 --WHILE @@FETCH_STATUS = 0
842 --BEGIN
843 -- SELECT @ListSoDetails = LEFT(SoDetailId, LEN(SoDetailId) - 1)
844 -- FROM (
845 -- SELECT CAST(SoDetailId AS nvarchar) + ','
846 -- FROM #TableOpenXml
847 -- WHERE DeliverySlipsId = @DeliverySlipsIdCurrent
848 -- FOR XML PATH ('')
849 -- ) c (SoDetailId)
850
851 -- EXEC APIClient_DE_RollBackTotalPaidOfPacking
852 -- @DeliverySlipsId = @DeliverySlipsIdCurrent,
853 -- @ListSoDetails = @ListSoDetails,
854 -- @CurrentUser = @CurrentUser,
855 -- @SesstionKey = @SesstionKey
856 -- FETCH NEXT FROM DeCursor
857 -- INTO @DeliverySlipsIdCurrent
858 --END
859 --CLOSE DeCursor;
860 --DEALLOCATE DeCursor;
861 ---------------------------------------------------------------------------------END----------------------------------------------------------
862 --9.5 TẠO PHIẾU THU TIỀN
863 ---------------------------------------------------------------------------------BEGIN------------------------------------------------------->
864 --KIỂM TRA NẾU CodId KHÃC 3PL THÃŒ MỚI TẠO PHIẾU THU TIỀN
865 IF EXISTS (SELECT 1 FROM #TableOpenXml D INNER JOIN dbo.UM_Users uu ON D.CodId = uu.UserId WHERE isnull(uu.UnitTransportId,0) < 2)
866 BEGIN
867 --CHECK XEM 1 HAY NHIỀU DANH SÃCH GIAO HÀNG
868 DECLARE @CountDeSlip int
869 SELECT @CountDeSlip = COUNT(DISTINCT tox.DeliverySlipsId) FROM dbo.#TableOpenXml tox
870
871 IF (@CountDeSlip=1)
872 BEGIN
873 DECLARE @DeSlipId int
874 DECLARE @CodByDeSlip int
875 SELECT DISTINCT @DeSlipId = tox.DeliverySlipsId FROM dbo.#TableOpenXml tox INNER JOIN dbo.DE_DeliverySlips dds ON dds.DeliverySlipsId=tox.DeliverySlipsId WHERE dds.StatusId IN (28,29)
876 SELECT @CodByDeSlip = tox.CodId FROM dbo.#TableOpenXml tox WHERE tox.DeliverySlipsId=@DeSlipId
877 IF EXISTS ( SELECT 1
878 FROM DE_DeliverySlipsDetails D INNER JOIN SO_Detail sd WITH (NOLOCK) ON (sd.ParentSoDetailId = D.SoDetailId or sd.SoDetailId = D.SoDetailId)
879 WHERE sd.SODetailStatusID = 13 AND
880 sd.Visible = 1 AND
881 D.IsCompleted = 1 AND
882 D.DeliverySlipsId = @DeSlipId)
883 BEGIN
884
885 --INSERT CA_CashDelivery
886 INSERT INTO CA_CashDelivery
887 (
888 [UserIdDelivery],
889 [Visible],
890 [IsCompleted],
891 [DateCompleted],
892 [CreatedBy],
893 [TotalMoney],
894 [DeliverySlipsId]
895 )
896 SELECT @CodByDeSlip,1,0,GETDATE(),@CurrentUser,0, @DeSlipId
897 SET @CashId = @@IDENTITY
898 --INSERT CA_CashDeliveryDetails
899 INSERT INTO CA_CashDeliveryDetails
900 (
901 [CashId],
902 [SoDetailId],
903 [Quantity]
904 )
905 SELECT @CashId, sd.SoDetailId , DESD.QuantityCompleted
906 FROM DE_DeliverySlipsDetails DESD
907 INNER JOIN dbo.SO_Detail sd WITH (NOLOCK) ON (sd.ParentSoDetailId = DESD.SoDetailId or sd.SoDetailId = DESD.SoDetailId)
908 WHERE sd.SODetailStatusID = 13 AND
909 sd.Visible = 1 AND
910 DESD.IsCompleted = 1 AND
911 DESD.DeliverySlipsId = @DeSlipId
912 --INSERT CA_CashDeliverySlip_Logs
913 INSERT INTO CA_CashDeliverySlip_Logs
914 (
915 [CashId],
916 [DeliverySlipsId],
917 [CreatedBy]
918 )
919 SELECT @CashId, @DeSlipId, @CurrentUser
920 END
921 END
922 ELSE
923 BEGIN
924 DECLARE @iDeCodId int = 0;
925 DECLARE curSoId CURSOR FOR
926 SELECT DISTINCT de.DeliverySlipsId, de.CodId
927 FROM
928 DE_DeliverySlips de INNER JOIN #TableOpenXml as [des] ON [des].DeliverySlipsId = de.DeliverySlipsId
929 WHERE
930 de.[StatusId] IN (29,28)
931 OPEN curSoId
932 FETCH NEXT FROM curSoId INTO @DeliverySlipsId , @iDeCodId
933 WHILE @@FETCH_STATUS = 0
934 BEGIN
935 IF EXISTS (SELECT 1 FROM DE_DeliverySlipsDetails D
936 INNER JOIN SO_Detail sd WITH (NOLOCK) ON (sd.ParentSoDetailId = D.SoDetailId or sd.SoDetailId = D.SoDetailId)
937 WHERE sd.SODetailStatusID = 13 AND
938 sd.Visible = 1 AND
939 D.IsCompleted = 1 AND
940 D.DeliverySlipsId = @DeliverySlipsId)
941 BEGIN
942 --INSERT CA_CashDelivery
943 INSERT INTO CA_CashDelivery
944 (
945 [UserIdDelivery],
946 [Visible],
947 [IsCompleted],
948 [DateCompleted],
949 [CreatedBy],
950 [TotalMoney],
951 [DeliverySlipsId]
952 )
953 SELECT @iDeCodId,1,0,GETDATE(),@CurrentUser,0, @DeliverySlipsId
954 SET @CashId = @@IDENTITY
955 --INSERT CA_CashDeliveryDetails
956 INSERT INTO CA_CashDeliveryDetails
957 (
958 [CashId],
959 [SoDetailId],
960 [Quantity]
961 )
962 SELECT @CashId, sd.SoDetailId , DESD.QuantityCompleted
963 FROM DE_DeliverySlipsDetails DESD
964 INNER JOIN dbo.SO_Detail sd WITH (NOLOCK) ON (sd.ParentSoDetailId = DESD.SoDetailId or sd.SoDetailId = DESD.SoDetailId)
965 WHERE sd.SODetailStatusID = 13 AND
966 sd.Visible = 1 AND
967 DESD.IsCompleted = 1 AND
968 DESD.DeliverySlipsId = @DeliverySlipsId
969 --INSERT CA_CashDeliverySlip_Logs
970 INSERT INTO CA_CashDeliverySlip_Logs
971 (
972 [CashId],
973 [DeliverySlipsId],
974 [CreatedBy]
975 )
976 SELECT @CashId,@DeliverySlipsId,@CurrentUser
977 END
978 -----------------------------------------------------------------------------------------------------
979 FETCH NEXT FROM curSoId INTO @DeliverySlipsId , @iDeCodId
980 END
981 CLOSE curSoId
982 DEALLOCATE curSoId
983 END
984 END
985 ---------------------------------------------------------------------------------END----------------------------------------------------------
986 --9.6 LƯU GHI CHÚ ÄẶC BIỆC
987 DECLARE @FullName nvarchar(50)
988 DECLARE @DepartmentName nvarchar(50)
989 SELECT @FullName = uu.FullName FROM dbo.UM_Users uu WHERE uu.UserId=@CurrentUser
990 SELECT @DepartmentName = ud.DepartmentName FROM dbo.UM_Users uu INNER JOIN dbo.UM_Departments ud ON ud.DepartmentId = uu.DepartmentId WHERE uu.UserId=@CurrentUser
991
992 INSERT INTO dbo.SpecialNotes
993 (
994 CreatedBy,
995 FullName,
996 DepartmentName,
997 CreatedDate,
998 Note,
999 ClientSoId,
1000 SoId
1001 )
1002 SELECT DISTINCT
1003 @CurrentUser,
1004 @FullName,
1005 @DepartmentName,
1006 GETDATE(),
1007 CASE WHEN r.ReasonName LIKE N'%Lý do khác%' THEN cast(tox.ReasonAgainOrther AS nvarchar(250)) + N'(Thá»i gian hẹn lại: ' + convert(varchar(5), tox.DateAgain, 108) + ' -> ' + convert(varchar(5), tox.DateAgainTo,108) + ' ' + convert(varchar(10), tox.DateAgain, 103) + ')'
1008 ELSE cast(r.ReasonName AS nvarchar(250)) + N'(Thá»i gian hẹn lại: ' + convert(varchar(5), tox.DateAgain, 108) + ' -> ' + convert(varchar(5), tox.DateAgainTo,108) + ' ' + convert(varchar(10), tox.DateAgain, 103) + ')'
1009 END,
1010 tox.ClientSoId,
1011 tox.SoId
1012 FROM dbo.#TableOpenXml tox LEFT JOIN dbo.Reason r ON r.ReasonId=tox.ReasonAgain
1013 WHERE tox.IsAgain = 1 --AND NOT tox.ReasonAgainOrther IS NULL
1014
1015 --9.7 KIỂM TRA NẾU LÀ ÄÆ N HÀNG MÀ 3PL GIAO THÃŒ CẬP NHẬT SANG UnitTransport_Log_CreateShippingOrder
1016 UPDATE UnitTransport_Log_CreateShippingOrder
1017 SET StatusId = CASE
1018 WHEN tox.IsCompleted = 1 THEN 7 --> Äã giao hà ng
1019 WHEN tox.IsAgain = 1 THEN 9 --> Hẹn lại
1020 WHEN tox.IsCanceled = 1 THEN 8 --> Hủy
1021 END,
1022 LastModifiedDate = GETDATE(),
1023 IsCheckSave = 1
1024 FROM UnitTransport_Log_CreateShippingOrder ulc INNER JOIN dbo.Packing_Detail pd ON pd.PackingID = ulc.ReferenceId
1025 INNER JOIN dbo.DE_DeliverySlipsDetails ddsd ON ddsd.PackingDetailId = pd.PackingDetailId
1026 INNER JOIN #TableOpenXml tox ON tox.DeliverySlipsDetailId = ddsd.DeliverySlipsDetailId
1027 WHERE isnull(ulc.UnitTransportId,0) > 1
1028 AND ulc.MethodId = 1
1029 AND ulc.StatusId NOT IN (7,8,10,11,12)
1030
1031 END
1032
1033 END
1034 ELSE IF (@IsState = 1) -->LƯU TẠM
1035 BEGIN
1036 --1. CẬP NHẬT TRẠNG THÃI CHI TIẾT PHIẾU: DE_DeliverySlipsDetails
1037 UPDATE DE_DeliverySlipsDetails
1038 SET
1039 IsCompleted = D.IsCompleted,
1040 IsAgain = D.IsAgain,
1041 IsCanceled = D.IsCanceled,
1042 DateCompleted = CASE WHEN D.IsCompleted = 1 THEN GETDATE() ELSE NULL END,
1043 DateAgain = CASE WHEN D.IsAgain = 1 THEN D.DateAgain ELSE NULL END,
1044 DateAgainTo = CASE WHEN D.IsAgain = 1 THEN D.DateAgainTo ELSE NULL END,
1045 DateCanceled = CASE WHEN D.IsCanceled = 1 THEN GETDATE() ELSE NULL END,
1046 QuantityCompleted = CASE WHEN D.IsCompleted = 1 THEN D.QuantityCompleted ELSE 0 END,
1047 QuantityAgain = CASE WHEN D.IsAgain = 1 THEN D.QuantityAgain ELSE 0 END,
1048 QuantityCanceled = CASE WHEN D.IsCanceled = 1 THEN D.QuantityCanceled ELSE 0 END,
1049 QuantityFailing = D.QuantityFailing,
1050 ReasonAgain = CASE WHEN D.IsAgain = 1 THEN D.ReasonAgain ELSE 0 END,
1051 ReasonCanceled = CASE WHEN D.IsCanceled = 1 THEN D.ReasonCanceled ELSE 0 END,
1052 ModifiedBy = @CurrentUser,
1053 DateModified = CASE WHEN (D.IsCompleted <> DED.IsCompleted OR D.IsAgain<>DED.IsAgain OR D.IsCanceled<>DED.IsCanceled) THEN GETDATE() ELSE DED.DateModified END,
1054 [Status] = CASE WHEN (D.IsCompleted = 1 OR D.IsAgain = 1 OR D.IsCanceled = 1) THEN 28 ELSE NULL END,
1055 ReasonAgainOrther = CASE WHEN D.IsAgain = 1 THEN D.ReasonAgainOrther ELSE NULL END,
1056 ReasonCanceledOrther= CASE WHEN D.IsCanceled = 1 THEN D.ReasonCanceledOrther ELSE NULL END,
1057 DateSaveTemp = CASE WHEN ((D.IsCompleted <> DED.IsCompleted OR D.IsAgain<>DED.IsAgain OR D.IsCanceled<>DED.IsCanceled) AND DED.IsCallCustomerFail = 0) THEN GETDATE() ELSE DED.DateSaveTemp END,
1058 Note = D.Note
1059 FROM
1060 DE_DeliverySlipsDetails DED INNER JOIN #TableOpenXml D ON (DED.DeliverySlipsDetailId = D.DeliverySlipsDetailId)
1061 INNER JOIN dbo.SO_Detail sd ON sd.SODetailID=DED.SoDetailId
1062 WHERE sd.SODetailStatusID=7
1063 END
1064 ELSE IF (@IsState = 3) -->LƯU TRẠNG THÃI KHÔNG LH ÄƯỢC VỚI KH
1065 BEGIN
1066 --Insert
1067 INSERT INTO dbo.SMS_Customer_Tracking
1068 (
1069 ClientSoId,
1070 CreatedBy,
1071 CreatedAt,
1072 FullName,
1073 PhoneDelivery,
1074 PhoneReceive,
1075 ReasonName
1076 )
1077 SELECT DISTINCT D.ClientSoId,
1078 @CurrentUser,
1079 GETDATE(),
1080 uu.FullName,
1081 uu.PhoneNumber,
1082 s.CustomerPhone,
1083 N'Không liên hệ được với khách hà ng.'
1084 FROM
1085 DE_DeliverySlipsDetails DED INNER JOIN #TableOpenXml D ON (DED.DeliverySlipsDetailId = D.DeliverySlipsDetailId)
1086 INNER JOIN dbo.SO s ON s.ClientSoId = D.ClientSoId
1087 LEFT JOIN dbo.UM_Users uu ON uu.UserId = D.CodId
1088 WHERE isnull(DED.IsCallCustomerFail,0) = 0
1089 AND (DED.IsCompleted = 0 OR DED.IsAgain = 0 OR DED.IsCanceled = 0)
1090
1091
1092 --Update
1093 UPDATE DE_DeliverySlipsDetails
1094 SET
1095 IsCallCustomerFail = 1,
1096 DateSaveTemp = CASE WHEN DED.DateSaveTemp IS NULL THEN GETDATE() ELSE DED.DateSaveTemp END
1097
1098 FROM
1099 DE_DeliverySlipsDetails DED INNER JOIN #TableOpenXml D ON (DED.DeliverySlipsDetailId = D.DeliverySlipsDetailId)
1100 WHERE isnull(DED.IsCallCustomerFail,0) = 0
1101 AND (DED.IsCompleted = 0 OR DED.IsAgain = 0 OR DED.IsCanceled = 0)
1102
1103 --Update SoDetails
1104 UPDATE SO_Detail
1105 SET SO_Detail.IsPushCS = 1
1106 FROM dbo.SO_Detail sd INNER JOIN #TableOpenXml tox ON tox.SoDetailId = sd.SODetailID
1107
1108 END
1109 -----------------------------------------------------------------------------------------END----------------------------------------------------------
1110 END
1111 --XÓA TABLE TEMP
1112 EXEC sp_xml_removedocument @idoc
1113
1114 COMMIT TRAN T
1115 IF (@Counter = 0)
1116 BEGIN
1117 SELECT 1 AS IsSuccess, 'Successful' AS ResponseMessage
1118 END
1119 ELSE IF (@Counter > 0)
1120 BEGIN
1121 SELECT 0 AS IsSuccess, N'Fail' AS ResponseMessage;
1122 END
1123 ELSE IF (@IsState = 1)
1124 BEGIN
1125 SELECT 1 AS IsSuccess, 'Successful' AS ResponseMessage
1126 END
1127 ELSE IF (@IsState = 3)
1128 BEGIN
1129 SELECT 1 AS IsSuccess, 'Successful' AS ResponseMessage
1130 END
1131
1132 END TRY---------------------------------------------------------------------------------------END TRY---------------------------------------------------------
1133 BEGIN CATCH
1134 SELECT 0 AS IsSuccess, N'Lỗi rồi.' AS ResponseMessage;
1135 THROW;
1136 END CATCH
1137 END TRY
1138 BEGIN CATCH
1139 SELECT 0 AS IsSuccess, N'Lỗi rồi.' AS ResponseMessage;
1140 THROW;
1141 END CATCH
1142 DROP TABLE #TableOpenXml
1143END