· 6 years ago · Jun 20, 2019, 04:32 PM
1USE [DBS]
2GO
3DROP PROCEDURE IF EXISTS [dbo].[InsertTariffFlowIntoQueue]
4GO
5
6SET ANSI_NULLS ON
7GO
8SET QUOTED_IDENTIFIER ON
9GO
10
11-- ***************************************************************************
12-- Created By: Anthony McEvoy
13-- Date: 03/06/2019
14-- Description: Adds TariffConfig/XCPRI_IN_01 flow requests by meter ID or tariff ID
15-- - Will limit to a single daily request by meter
16-- - Can be run manually as a bulk update tool (by tariff ID)
17-- Implemented in: - [dbo].[InsertTariffChange]
18-- - [dbo].[Tariff_cancel_change]
19-- ***************************************************************************
20CREATE PROCEDURE [dbo].[InsertTariffFlowIntoQueue]
21(
22 @meter_id INT,
23 @tariff_id INT,
24 @pending_date DATETIME,
25 @debug_mode BIT
26)
27AS
28
29SET NOCOUNT ON;
30SET XACT_ABORT ON
31
32DECLARE @separator VARCHAR(200) = '>'
33
34BEGIN TRY
35 BEGIN TRAN
36
37 If @debug_mode = 1 BEGIN
38 PRINT 'DEBUG MODE !!! DEBUG MODE !!! DEBUG MODE !!! DEBUG MODE !!! DEBUG MODE !!! DEBUG MODE !!! DEBUG MODE !!! DEBUG MODE !!! DEBUG MODE'
39 PRINT @separator
40 END
41
42 DECLARE @tbl_meters_on_tariff TABLE (meter_id int, last_date_change DATETIME, crm_customer_id VARCHAR(100), flow_type VARCHAR(30))
43
44 IF @meter_id > 0 BEGIN
45 -- This by-meter-id method will traditionally execute from within other stored procedures
46 DECLARE @hasBlockTariff BIT
47 EXEC @hasBlockTariff = dbo.HasBlockTariffConfigChanged @meter_id
48
49 PRINT 'Flow message(s) only required for single customer and their relevant supplies'
50 PRINT @separator
51
52 -- Get customer ref number and meter tariff ID
53 DECLARE @crm_customer_id VARCHAR(100)
54 SELECT
55 @tariff_id = m.tariff_id, @crm_customer_id = c.crm_customer_id
56 FROM
57 Meter AS m INNER JOIN Customer AS c ON m.cust_ref = c.cust_ref
58 WHERE
59 m.meter_id = @meter_id
60
61 PRINT 'Found Customer ID: ' + @crm_customer_id
62 PRINT 'Found Tariff ID: ' + CONVERT(VARCHAR(10), @tariff_id)
63
64 -- Get customer's active meters with the same tariff (dual fuel)
65 INSERT INTO @tbl_meters_on_tariff
66 SELECT m.meter_id, tc.lastChangeDate, c.crm_customer_id, CASE WHEN DCC_service_flag in('A','S')
67 THEN CASE WHEN @hasBlockTariff = 1 THEN 'XCTARIN01'
68 ELSE 'XCPRIIN01'
69 END
70 ELSE 'TariffConfig'
71 END
72 FROM Meter m
73 LEFT OUTER JOIN (
74 SELECT tc.meter_id, tc.new_tariff, lastChangeDate
75 FROM Tariff_change tc
76 INNER JOIN (SELECT meter_id, MAX(date_effective) AS lastChangeDate
77 FROM Tariff_change
78 GROUP BY meter_id) AS lastTC ON tc.meter_id = lastTC.meter_id AND tc.date_effective = lastTC.lastChangeDate
79 ) AS tc ON tc.meter_id = m.meter_id
80 LEFT OUTER JOIN Customer c ON c.cust_ref = m.cust_ref
81 INNER JOIN Meter_type_indicators mti ON M.meter_type_indicator=mti.pk
82 WHERE (m.tariff_id = @tariff_id OR (tc.new_tariff = @tariff_id and tc.lastChangeDate < GETDATE()))
83 AND m.meter_id = @meter_id
84 AND m.meter_status IN (2,6,9)
85 AND (m.DCC_service_flag IN('A','S') OR (mti.meter_type='S1' AND m.DCC_service_flag IN('', 'W')))
86 END
87 ELSE IF @tariff_id > 0 BEGIN
88 -- This by-tariff-id method will likely be executed manually
89
90 PRINT 'XCPRI_IN_01 flow message required for all customers using tariff ID ' + CONVERT(VARCHAR(10), @tariff_id)
91 PRINT @separator
92
93 -- Get active meters on this tariff
94 INSERT INTO @tbl_meters_on_tariff
95 SELECT m.meter_id, tc.lastChangeDate, c.crm_customer_id, CASE WHEN DCC_service_flag in('A','S')
96 THEN CASE WHEN @hasBlockTariff = 1 THEN 'XCTARIN01'
97 ELSE 'XCPRIIN01'
98 END
99 ELSE 'TariffConfig'
100 END
101 FROM Meter m
102 LEFT OUTER JOIN (
103 SELECT tc.meter_id, tc.new_tariff, lastChangeDate
104 FROM Tariff_change tc
105 INNER JOIN (SELECT meter_id, MAX(date_effective) AS lastChangeDate
106 FROM Tariff_change
107 GROUP BY meter_id) AS lastTC ON tc.meter_id = lastTC.meter_id AND tc.date_effective = lastTC.lastChangeDate
108 ) AS tc ON tc.meter_id = m.meter_id
109 LEFT OUTER JOIN Customer c ON c.cust_ref = m.cust_ref
110 INNER JOIN Meter_type_indicators mti ON M.meter_type_indicator=mti.pk
111 WHERE (m.tariff_id = @tariff_id OR (tc.new_tariff = @tariff_id and tc.lastChangeDate < GETDATE()))
112 AND m.meter_status IN (2,6,9)
113 AND (m.DCC_service_flag IN('A','S') OR (mti.meter_type='S1' AND m.DCC_service_flag IN('', 'W')))
114 END
115 ELSE BEGIN
116 RAISERROR(N'Neither @tariff_id nor @meter_id have been set so there is nothing to do.', 16, 1)
117 END
118
119 DECLARE @meter_total INT
120 SELECT @meter_total = COUNT(meter_id) FROM @tbl_meters_on_tariff
121 PRINT 'Found ' + CONVERT(VARCHAR(10), @meter_total) + ' meter(s) to process'
122
123 DECLARE @c_validation_failed BIT = 0
124 DECLARE @c_meter_id INT
125 DECLARE @c_last_date_change DATETIME
126 DECLARE @c_customer_crm_id VARCHAR(100)
127 DECLARE @c_flow_type VARCHAR(50)
128 DECLARE message_cursor CURSOR FOR
129 SELECT meter_id, last_date_change, crm_customer_id, flow_type FROM @tbl_meters_on_tariff ORDER BY meter_id
130
131 OPEN message_cursor
132 FETCH NEXT FROM message_cursor INTO @c_meter_id, @c_last_date_change, @c_customer_crm_id, @c_flow_type
133
134 WHILE @@FETCH_STATUS = 0
135 BEGIN
136 PRINT @separator
137
138 SET @c_validation_failed = 0
139
140 -- Validation step #1
141 -- If the meter has a tariff change scheduled after the next process date provided
142
143 IF (@c_last_date_change IS NOT NULL) AND (@c_last_date_change > @pending_date) BEGIN
144 PRINT 'ERROR: Meter ID ' + CAST(@c_meter_id AS VARCHAR) + ' has a tariff change later then the next process date selected for this message.'
145 SET @c_validation_failed = 1
146 END
147
148 -- Validation step #2
149 -- If there are existing flow for this meter it will adjust an existing record on the same day as the provided next process date otherwise this step will pass
150 -- as flows on future days are still relevant and need to be left as they are. This will simply limit the daily amount to one
151
152 DECLARE @tbl_existing_flows TABLE(pk INT, next_process_date DATETIME)
153 DECLARE @latest_next_process_date DATETIME = NULL
154 DECLARE @existing_flows_count INT = 0
155
156 -- Get existing flows based on flow type and meter ID
157 DELETE FROM @tbl_existing_flows
158 INSERT INTO @tbl_existing_flows
159 SELECT MessageId, NextProcessDate
160 FROM [Message]
161 WHERE FlowType = @c_flow_type AND MeterId= @c_meter_id AND Activated = 1 AND Locked = 0
162
163 -- Get latest date from same day as next process date provided (if there is one)
164 SELECT @existing_flows_count = COUNT(ef.pk) FROM @tbl_existing_flows AS ef WHERE CAST(ef.next_process_date AS DATE) = CAST(@pending_date AS DATE)
165 SELECT @latest_next_process_date = MAX(ef.next_process_date)
166 FROM @tbl_existing_flows AS ef
167 WHERE CAST(ef.next_process_date AS DATE) = CAST(@pending_date AS DATE)
168 GROUP BY CAST(ef.next_process_date AS DATE)
169
170 -- If there are existing flows on the same day...
171 IF @existing_flows_count > 0 BEGIN
172 IF @pending_date > @latest_next_process_date
173 BEGIN
174 -- and the flow we want to create is later in the day so earlier-in-the-day flows can be deleted
175
176 PRINT 'Deleting ' + CAST(@existing_flows_count AS VARCHAR) + ' existing flow records for ' + CONVERT(VARCHAR(10), @pending_date, 103) + ' as the provided date/time is for later in the day (' + CONVERT(VARCHAR(10), @pending_date, 108) + ')'
177
178 IF @debug_mode = 1
179 BEGIN
180 PRINT 'DELETE FROM [Message] WHERE FlowType = ''' + @c_flow_type + ''' AND MeterId = ' + CAST(@c_meter_id AS VARCHAR) + ' + AND Activated = 1 AND Locked = 0 AND CAST(NextProcessDate AS DATE) = CAST(N''' + CONVERT(VARCHAR(10), @pending_date, 103) + ' ' + CONVERT(VARCHAR(10), @pending_date, 108) + ''' AS DATE)'
181 END
182 ELSE
183 BEGIN
184 DELETE FROM [Message] WHERE FlowType = @C_flow_type AND MeterId = @c_meter_id AND Activated = 1 AND Locked = 0 AND CAST(NextProcessDate AS DATE) = CAST(@pending_date AS DATE)
185 END
186 END
187 ELSE BEGIN
188 -- and there is a flow for later in the same day so fail validation to prevent an earlier record from being created
189 SET @c_validation_failed = 1
190
191 PRINT 'Validation failed for meter ID ' + CAST(@c_meter_id AS VARCHAR) + ' as there is already a flow queued for later in the day'
192 END
193 END
194
195 DECLARE
196 @endpointAddress VARCHAR(100) = (SELECT config_item FROM [Configuration] WHERE config_item_id = 'Futura_RESTAPI_URL')
197 ,@endpointMethod VARCHAR(30) = 'POST'
198 ,@contentType VARCHAR(30) = 'application/json'
199 ,@meterType VARCHAR(5) = (SELECT Meter_type_indicators.meter_type FROM Meter INNER JOIN Meter_type_indicators ON Meter.meter_type_indicator=Meter_type_indicators.pk WHERE meter_id=@c_meter_id)
200 ,@dccServiceFlag VARCHAR(1) = (SELECT DCC_service_flag FROM Meter WHERE meter_id=@c_meter_id);
201 DECLARE @payload VARCHAR(100) = '{"MeterId":' + CAST(@c_meter_id AS VARCHAR) + ',"Flow":"' + @c_flow_type + '","MeterType":"' + @meterType + '","DCCServiceFlag":"' + @dccServiceFlag + '","ErrorMessage":""}'
202
203 -- Post-validation step #1
204 -- If validation has passed then each meter will have a flow record created
205 IF @c_validation_failed = 0 BEGIN
206 PRINT 'Add flow for meter ID ' + CAST(@c_meter_id AS VARCHAR) + ', customer ID ' + @c_customer_crm_id
207
208 If @debug_mode = 1 BEGIN
209 -- Print the stored procedure execution script that would be performed outside of debug mode
210 PRINT 'EXEC [Message_CreateMessage] @EndpointAddress = ''' + @endpointAddress + ''', @EndpointMethod = ''' + @endpointMethod + ''', @ContentType = ''' + @contentType + ''', Payload = ''' + @payload + ''', @MeterId = ' + CONVERT(VARCHAR(10), @c_meter_id) + ', @FlowType = ''' + @c_flow_type + ''', @activated = 1, @NextProcessDate = N''' + CONVERT(VARCHAR(10), @pending_date, 103) + ' ' + CONVERT(VARCHAR(10), @pending_date, 108) + ''''
211 END
212 ELSE BEGIN
213
214 EXEC [Message_CreateMessage]
215 @EndpointAddress = @endpointAddress
216 ,@EndpointMethod = @endpointMethod
217 ,@ContentType = @contentType
218 ,@Payload = @payload
219 ,@MeterId = @c_meter_id
220 ,@FlowType = @c_flow_type
221 ,@Activated = 1
222 ,@NextProcessDate = @pending_date
223
224 END
225 END
226
227 -- Fetch next row
228 FETCH NEXT FROM message_cursor INTO @c_meter_id, @c_last_date_change, @c_customer_crm_id, @c_flow_type
229 END
230
231 CLOSE message_cursor
232 DEALLOCATE message_cursor
233 COMMIT TRAN
234END TRY
235BEGIN CATCH
236 ROLLBACK TRAN
237 SELECT ERROR_MESSAGE()
238
239 IF (SELECT CURSOR_STATUS('global','message_cursor')) >= -1
240 BEGIN
241 IF (SELECT CURSOR_STATUS('global','message_cursor')) > -1
242 BEGIN
243 CLOSE message_cursor
244 END
245
246 DEALLOCATE message_cursor
247 END
248END CATCH