· 8 years ago · Jan 26, 2018, 01:08 PM
1USE [PSOL]
2GO
3/****** Object: StoredProcedure [dbo].[usp_SMI_GetReportConsumptionPrepareNew] Script Date: 01/26/2018 14:06:32 ******/
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8ALTER PROC [dbo].[usp_SMI_GetReportConsumptionPrepareNew]
9 @SmiDestId INT,
10 @ProdNo VARCHAR(8),
11 @StartDate DATETIME,
12 @EndDate DATETIME
13AS
14SET NOCOUNT ON
15
16-- TG CR 1070 2018-01-03 added so we check if any of the dates has been invoiced on.
17-- Send the date back to the code and all rows with a date smaller or equal to
18-- this date will be disabled in the gui.
19
20SET DATEFIRST 1
21
22CREATE TABLE #Result
23 (
24 UsageNo VARCHAR(30),
25 UsageNoCustomer VARCHAR(30),
26 UsageNoSodra VARCHAR(30),
27 UsageNoTransfer VARCHAR(30),
28 ConsumptionDate DATETIME,
29 ConsumptionDateFormatted VARCHAR(30),
30 ConsumedCustomerQuantity DECIMAL(14,3),
31 ConsumedSodraQuantity DECIMAL(14,3),
32 TransferQuantity DECIMAL(14,3),
33 ConsumedTotalQuantity DECIMAL(14,3),
34 ConsumedQuantity DECIMAL(14,3),
35 PreviousCustomerConsumption DECIMAL(14,3),
36 PreviousSodraConsumption DECIMAL(14,3),
37 PreviousTransfer DECIMAL(14,3),
38 TransCodeUsage BIT,
39 TransCodeInvoice BIT, --RK 060206, added so history can be correct for history rows (only invoiced should be shown)
40 IsHistory BIT,
41 StoreType VARCHAR(50),
42 ChangeDate DATETIME
43 )
44
45DECLARE @consumptiondate DATETIME
46DECLARE @PreviousCustomerConsumption DECIMAL(14,3)
47DECLARE @PreviousSodraConsumption DECIMAL(14,3)
48DECLARE @PreviousTransfer DECIMAL(14,3)
49DECLARE @UsageNo VARCHAR(30)
50DECLARE @StoreType VARCHAR(50)
51
52DECLARE @StartDateUnchanged DATETIME
53
54DECLARE @DelAdrNo Char(10)
55DECLARE @CustNo CHAR(20)
56
57SET @StartDateUnchanged = @startdate
58SET @consumptiondate = @startdate
59
60 -- inserts previously reported consumptions into temptable
61INSERT INTO #result(UsageNo, ConsumptionDate, ConsumptionDateFormatted, ConsumedQuantity, PreviousCustomerConsumption, PreviousSodraConsumption, PreviousTransfer, TransCodeUsage, TransCodeInvoice, isHistory, StoreType, ChangeDate)
62
63SELECT CH.UsageNo,
64 CAST( CONVERT(CHAR(8), CH.ConsumptionDate, 112) AS DateTime),
65 dbo.fSodraDateAndWeekFormatSMI(CH.ConsumptionDate),
66 SUM(CH.ConsumedQuantity),
67 null,
68 null,
69 null,
70 CH.TransCodeUsage,
71 CH.TransCodeInvoice,
72 1,
73 CH.StoreType,
74 CH.ChangeDate
75FROM SMI_DestDelAdr SD, ConsumptionHead CH
76INNER JOIN Products P ON P.ProdNo = CH.ProdNo
77WHERE SD.SmiDestId = @SmiDestId AND
78 SD.DelAdrNo = CH.DelAdrNo AND
79 SD.CustNo = CH.CustNo AND
80 P.ProdName = (SELECT P.ProdName FROM Products P WHERE P.ProdNo = @ProdNo) AND
81 CH.ConsumptionDate >= @startdate AND
82 CONVERT(varchar(10),CH.ConsumptionDate,20) <= CONVERT(varchar(10),@enddate,20)
83 -- Added by ph 041202
84 -- Removed by rk since we need transfers also
85 -- CH.TransCodeUsage = 1
86GROUP BY CH.ConsumptionDate, CH.UsageNo, CH.ConsumedQuantity, CH.TransCodeUsage, CH.TransCodeInvoice, CH.StoreType, CH.ChangeDate
87ORDER BY CH.ConsumptionDate ASC
88
89-- creates the new rows to insert for all the dates
90WHILE @enddate >= @startdate BEGIN
91
92 -- if the history contains gaps (no consumptions for a date) then create an empty history row
93 IF NOT EXISTS(SELECT ConsumptionDate FROM #result WHERE ConsumptionDate = @consumptiondate AND isHistory = 1)
94 INSERT INTO #result(UsageNo, ConsumptionDate, ConsumptionDateFormatted, ConsumedQuantity, PreviousCustomerConsumption, PreviousSodraConsumption, PreviousTransfer, TransCodeUsage, TranscodeInvoice, isHistory, StoreType)
95 VALUES('', @consumptiondate, dbo.fSodraDateAndWeekFormatSMI(@ConsumptionDate), null, null, null, null, 0, 1, 1, '')
96
97 -- sets the previously reported consumptions for the new rows (if such a one exists) - Customer inventory
98 --IF EXISTS (SELECT ConsumedQuantity FROM #result WHERE ConsumptionDate = @consumptiondate AND isHistory = 1)
99 -- SELECT @StoreType = StoreType, @UsageNo = UsageNo, @PreviousCustomerConsumption = ConsumedQuantity FROM #result WHERE ConsumptionDate = @consumptiondate
100 --ELSE
101 -- SET @PreviousCustomerConsumption = null
102
103 INSERT INTO #result(UsageNo, ConsumptionDate, ConsumptionDateFormatted, ConsumedQuantity, PreviousCustomerConsumption, PreviousSodraConsumption, PreviousTransfer, TransCodeUsage, TranscodeInvoice, isHistory, StoreType)
104 VALUES ('', @consumptiondate, dbo.fSodraDateAndWeekFormatSMI(@ConsumptionDate), null, null, null, null, 0, 0, 0, '')
105
106 SET @consumptiondate = @consumptiondate + 1
107 SET @startdate = @startdate + 1
108END
109
110
111----------------------------------
112-- Return main resultset
113----------------------------------
114SELECT * FROM #result ORDER BY isHistory DESC, ConsumptionDate
115
116----------------------------------
117-- Return storetypes used by site
118----------------------------------
119SELECT UsesInventoryType FROM SMI_Destinations where SmiDestId = @SmiDestId
120
121-- Check if the current prod has been invoiced in this time interval
122SELECT @DelAdrNo = DelAdrNo,
123 @CustNo = CustNo FROM SMI_DestDelAdr WHERE SmiDestId = @SmiDestId
124
125-- DEBUG
126-- SELECT @DelAdrNo
127-- SELECT @CustNo
128-- SELECT @ProdNo
129-- SELECT @StartDateUnchanged
130-- SELECT @EndDate
131
132-- TG CR 1070 2018-01-03
133-- Check if there is any invoices for the selected product within the period.
134-- If so, it will pick the most recent date and return in. All rows with a date smaller or equal to
135-- this date will be disabled in the gui.
136
137SELECT TOP 1
138 I.InvDate
139 FROM Invoices I
140INNER JOIN InvoiceLineItems ILI ON ILI.InvNo = I.InvNo
141INNER JOIN Products P ON P.ProdNo = ILI.ProductNo
142WHERE I.CustNo = @CustNo
143 AND
144 I.DelAdrNo = @DelAdrNo
145 AND
146 P.ProdName = (SELECT ProdName FROM Products WHERE Prodno = @ProdNo)
147 AND
148 CONVERT(varchar(10),I.InvDate,20) >= CONVERT(varchar(10),@StartDateUnchanged,20)
149
150ORDER BY I.InvDate DESC
151-------------------------------------------------------------------
152-- Return quantity grouped on day on rows with TransCodeInvoice = 1
153-------------------------------------------------------------------
154SELECT
155 CONVERT(VARCHAR(10),CH.ConsumptionDate,20) AS 'ConsumptionDate',
156 SUM(ISNULL(CH.ConsumedQuantity,0)) AS 'ConsumedInvoicedQuantity'
157FROM SMI_DestDelAdr SD, ConsumptionHead CH
158INNER JOIN Products P ON P.ProdNo = CH.ProdNo
159WHERE SD.SmiDestId = @SmiDestId AND
160 SD.DelAdrNo = CH.DelAdrNo AND
161 SD.CustNo = CH.CustNo AND
162 P.ProdName = (SELECT P.ProdName FROM Products P WHERE P.ProdNo = @ProdNo) AND
163 CH.ConsumptionDate >= @StartDateUnchanged AND
164 CONVERT(varchar(10),CH.ConsumptionDate,20) <= CONVERT(varchar(10),@enddate,20) AND
165 CH.TransCodeInvoice = 1
166GROUP BY CONVERT(VARCHAR(10),ConsumptionDate,20)
167ORDER BY ConsumptionDate ASC
168
169
170-------------------------------------------------------------------
171-- Return the latest date that deliveries has occured. Needed for new error message in GUI.
172-- RK 050919
173-------------------------------------------------------------------
174SELECT TOP 1
175 IDM.DelDate
176
177FROM DeliveryMessages IDM
178INNER JOIN DeliveryMessageLineItems IDML ON IDM.DelMesNo = IDML.DelMesNo
179INNER JOIN Products P ON P.ProdNo = IDML.ProductNo
180WHERE IDM.CustNo = @CustNo
181 AND
182 IDM.DelAdrNo = @DelAdrNo
183 AND
184 P.ProdName = (SELECT ProdName FROM Products WHERE Prodno = @ProdNo)
185 AND
186 IDM.IsCancelled = 0
187 AND
188 IDM.OrderType = 'C'
189ORDER BY IDM.DelDate DESC
190
191DROP TABLE #Result