· 7 years ago · Feb 14, 2019, 03:20 PM
1DELIMITER $$
2
3USE `bmggui`$$
4
5DROP PROCEDURE IF EXISTS `proc_user_status_report_mis`$$
6
7CREATE DEFINER=`bmg`@`%` PROCEDURE `proc_user_status_report_mis`(IN InDate DATE)
8BEGIN
9 DECLARE vClientID, vBatchId,vBatchId1, vTransType,vChannelName VARCHAR(20);
10 DECLARE vRefTransId,vRemark LONGTEXT;
11 DECLARE vTotal, vDel, vSub, vFailed, vExpired, vSubmitFail, vOther,vBlockedAtBMG, vUserId,vClBal, vAmount INT;
12 DECLARE done INT DEFAULT FALSE;
13
14 DECLARE C1 CURSOR FOR
15 SELECT ClientID,
16 SUM(IFNULL(`Total`,0)) Total, SUM(IFNULL(`Delivered`,0)) Delivered,
17 SUM(IFNULL(`Submitted`,0)) Submitted, SUM(IFNULL(`Failed`,0)) Failed,
18 SUM(IFNULL(`Expired`,0)) Expired, SUM(IFNULL(`SubmitFail`,0)) SubmitFail,
19 SUM(IFNULL(`Other`,0)) Other, SUM(IFNULL(`BlockedAtBMG`,0)) BlockedAtBMG
20 FROM tbl_mis_summary
21 WHERE DATE(SubmitDate) = SUBDATE(InDate, INTERVAL 1 DAY)
22 GROUP BY ClientID;
23
24 DECLARE C2 CURSOR FOR
25 SELECT user_id, IFNULL(cl_bal,0)
26 FROM tbl_user_mis_report
27 WHERE DATE(insert_date) = SUBDATE(InDate,INTERVAL 1 DAY);
28
29 DECLARE C3 CURSOR FOR
30 SELECT a.user_id, SUBSTR(a.batch_id,1,4) batch_id , a.trans_type, SUM(a.amount) amount
31 FROM tbl_charging_temp a
32 WHERE DATE(entry_date) = SUBDATE(InDate,INTERVAL 1 DAY)
33 GROUP BY a.user_id, SUBSTR(a.batch_id,1,4) , a.trans_type;
34 DECLARE C4 CURSOR FOR
35 SELECT a.user_id, SUBSTR(a.batch_id,1,3) batch_id , a.trans_type, SUM(a.amount) amount
36 FROM tbl_charging_temp a
37 WHERE DATE(entry_date) = InDate
38 -- WHERE (entry_date) LIKE '2018-07-31 08:27%' AND trans_type = 'C'
39 AND a.batch_id LIKE 'REF%'
40 GROUP BY a.user_id, SUBSTR(a.batch_id,1,3) , a.trans_type;
41
42 DECLARE C5 CURSOR FOR
43 SELECT user_id,IFNULL(REPLACE(GROUP_CONCAT(transtype),',','|'),'NA'),IFNULL(REPLACE(GROUP_CONCAT(remarks),',','|'),'NA')
44 FROM tbl_transfer WHERE DATE(entry_datetime)=SUBDATE(InDate,INTERVAL 1 DAY)
45 GROUP BY user_id;
46
47 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
48
49 DECLARE EXIT HANDLER FOR SQLEXCEPTION
50 BEGIN
51 GET DIAGNOSTICS CONDITION 1 @SQLSTATE = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @TEXT = MESSAGE_TEXT;
52 CALL `proc_error_log`('proc_user_status_report_mis',@errno,@TEXT);
53 END;
54
55
56 TRUNCATE TABLE tbl_charging_temp;
57 INSERT INTO tbl_charging_temp
58 SELECT * FROM tbl_charging a
59 WHERE DATE(entry_date) BETWEEN SUBDATE(InDate,INTERVAL 1 DAY) AND InDate;
60
61
62 DELETE FROM tbl_user_mis_report WHERE DATE(insert_date) = InDate;
63
64 DO SLEEP(10);
65
66 INSERT INTO tbl_user_mis_report(user_id,user_name,account_type,user_type_id,charging_mode,
67 total_credits,sms_validity,credits_used,credit_limit,total_child,smsc_identifier,
68 msg_category,tps,parent_id,parent_name,`status`,creation_date,insert_date)
69 SELECT m.`user_master_id` ,m.username, m.`account_type` , m.`user_type_id`,m.charging_mode,
70 c.`total_credits`,
71 CASE WHEN c.sms_validity IS NULL THEN ' '
72 WHEN DATE(c.sms_validity) < InDate
73 THEN CONCAT('Expired on ',DATE_FORMAT(c.sms_validity, '%Y-%m-%d'))
74 ELSE DATE_FORMAT(c.sms_validity, '%Y-%m-%d') END AS "sms_validity",
75 c.`credits_used`, c.`credit_limit`,c.`total_child`,smsc_identifier,
76 c.msg_cat_id,CASE WHEN m.parent_id = 30
77 THEN c.allowed_tps
78 ELSE 0 END AS "allowed_tps",
79 m.parent_id,
80 (SELECT a.username FROM tbl_users_master a WHERE a.user_master_id = m.parent_id) parent_name,
81 `status`,DATE_FORMAT(`created_date`, '%Y-%m-%d %H:%i:%s') created_date, InDate
82 FROM `tbl_users_master` m
83 LEFT JOIN `tbl_user_configurations` c ON c.`user_id` = m.`user_master_id`;
84
85
86 DO SLEEP(10);
87
88 UPDATE tbl_user_mis_report a
89 SET op_bal = fun_op_cl_bal(a.user_id,SUBDATE(InDate,INTERVAL 1 DAY),'OB'),
90 cl_bal= fun_op_cl_bal(a.user_id,SUBDATE(InDate,INTERVAL 1 DAY),'CB')
91 WHERE DATE(insert_date) = InDate;
92
93 DO SLEEP(10);
94
95 OPEN C1;
96 read_loop: LOOP
97 FETCH C1 INTO vClientID,vTotal, vDel, vSub, vFailed, vExpired, vSubmitFail, vOther,vBlockedAtBMG;
98 IF done THEN
99 LEAVE read_loop;
100 END IF;
101
102 UPDATE tbl_user_mis_report
103 SET msgTotal = vTotal,
104 msgProcessed = vTotal - vBlockedAtBMG,
105 msgDelivered = vDel,
106 msgSubmitted = vSub,
107 msgFailed = (vFailed - vExpired - vSubmitFail),
108 msgExpired = vExpired,
109 msgSubmitFail = vSubmitFail,
110 msgOther = vOther - vBlockedAtBMG,
111 BlockedAtBMG = vBlockedAtBMG
112 WHERE user_id = vClientID
113 AND DATE(insert_date) = InDate;
114
115 END LOOP read_loop;
116 CLOSE C1;
117
118 SET done = FALSE;
119
120 DO SLEEP(10);
121
122 OPEN C2;
123 read_loop: LOOP
124 FETCH C2 INTO vUserId,vClBal;
125 IF done THEN
126 LEAVE read_loop;
127 END IF;
128
129 UPDATE tbl_user_mis_report
130 SET op_bal = vClBal
131 WHERE user_id = vUserId
132 AND DATE(insert_date) = InDate
133 AND IFNULL(op_bal,0) = 0 ;
134 END LOOP read_loop;
135 CLOSE C2;
136
137 SET done = FALSE;
138
139 DO SLEEP(10);
140
141 OPEN C3;
142 read_loop: LOOP
143 FETCH C3 INTO vUserId,vBatchId1, vTransType, vAmount;
144 IF done THEN
145 LEAVE read_loop;
146 END IF;
147
148 SET vBatchId = SUBSTR(vBatchId1,1,3);
149
150 IF vBatchId = 'REF' AND vTransType = 'C' THEN
151
152 UPDATE tbl_user_mis_report
153 SET refund_c = refund_c + vAmount
154 WHERE user_id = vUserId
155 AND DATE(insert_date) = InDate;
156
157 ELSEIF vBatchId1 = 'CMPR' AND vTransType = 'C' THEN
158
159 UPDATE tbl_user_mis_report
160 SET campaign_refund_c = campaign_refund_c + vAmount
161 WHERE user_id = vUserId
162 AND DATE(insert_date) = InDate;
163
164 ELSEIF vBatchId = 'DEB' AND vTransType = 'D' THEN
165
166 UPDATE tbl_user_mis_report
167 SET refund_d = refund_d + vAmount
168 WHERE user_id = vUserId
169 AND DATE(insert_date) = InDate;
170
171 ELSEIF (vBatchId = 'BID' OR vBatchId = 'CMP') AND vTransType = 'D' THEN
172
173 UPDATE tbl_user_mis_report
174 SET sms_charging = sms_charging + vAmount
175 WHERE user_id = vUserId
176 AND DATE(insert_date) = InDate;
177
178 ELSEIF vBatchId = 'BID' AND vTransType = 'C' THEN
179
180 UPDATE tbl_user_mis_report
181 SET sms_charging = sms_charging - vAmount
182 WHERE user_id = vUserId
183 AND DATE(insert_date) = InDate;
184
185 ELSEIF (vBatchId = 'BAL' OR vBatchId = 'DEL') AND vTransType = 'C' THEN
186
187 IF vBatchId1 = 'BALE' THEN
188 UPDATE tbl_user_mis_report
189 SET expired_c = expired_c + vAmount
190 WHERE user_id = vUserId
191 AND DATE(insert_date) = InDate;
192 ELSE
193 UPDATE tbl_user_mis_report
194 SET bal_transfer_c = bal_transfer_c + vAmount
195 WHERE user_id = vUserId
196 AND DATE(insert_date) = InDate;
197 END IF;
198
199 ELSEIF (vBatchId = 'BAL' OR vBatchId = 'DEL') AND vTransType = 'D' THEN
200
201
202 IF vBatchId1 = 'BALE' THEN
203 UPDATE tbl_user_mis_report
204 SET expired_d = expired_d + vAmount
205 WHERE user_id = vUserId
206 AND DATE(insert_date) = InDate;
207 ELSE
208 UPDATE tbl_user_mis_report
209 SET bal_transfer_d = bal_transfer_d + vAmount
210 WHERE user_id = vUserId
211 AND DATE(insert_date) = InDate;
212 END IF;
213
214
215 ELSEIF vBatchId = 'REV' AND vTransType = 'C' THEN
216
217 UPDATE tbl_user_mis_report
218 SET revoke_c = revoke_c + vAmount
219 WHERE user_id = vUserId
220 AND DATE(insert_date) = InDate;
221
222 ELSEIF vBatchId = 'REV' AND vTransType = 'D' THEN
223
224 UPDATE tbl_user_mis_report
225 SET revoke_d = revoke_d + vAmount
226 WHERE user_id = vUserId
227 AND DATE(insert_date) = InDate;
228
229 ELSEIF vTransType = 'D' THEN
230
231 UPDATE tbl_user_mis_report
232 SET other_debit = other_debit + vAmount
233 WHERE user_id = vUserId
234 AND DATE(insert_date) = InDate;
235
236 ELSEIF vTransType = 'C' THEN
237
238 UPDATE tbl_user_mis_report
239 SET other_credit = other_credit + vAmount
240 WHERE user_id = vUserId
241 AND DATE(insert_date) = InDate;
242
243 END IF;
244
245 END LOOP read_loop;
246 CLOSE C3;
247
248 SET done = FALSE;
249
250 DO SLEEP(10);
251
252 OPEN C4;
253 read_loop: LOOP
254 FETCH C4 INTO vUserId,vBatchId, vTransType, vAmount;
255 IF done THEN
256 LEAVE read_loop;
257 END IF;
258
259 IF vBatchId = 'REF' AND vTransType = 'C' THEN
260
261 UPDATE tbl_user_mis_report
262 SET refund_amount_today = refund_amount_today + vAmount
263 WHERE user_id = vUserId
264 AND DATE(insert_date) = InDate;
265 END IF;
266
267 END LOOP read_loop;
268 CLOSE C4;
269
270 SET done = FALSE;
271 DO SLEEP(10);
272
273 OPEN C5;
274 read_loop: LOOP
275 FETCH C5 INTO vUserId,vRefTransId,vRemark;
276 IF done THEN
277 LEAVE read_loop;
278 END IF;
279
280 UPDATE tbl_user_mis_report
281 SET `transtype` = vRefTransId,
282 `remarks`=vRemark
283 WHERE user_id = vUserId;
284 END LOOP read_loop;
285 -- SELECT vUserId,vRefTransId,vRemark;
286 CLOSE C5;
287
288 DO SLEEP(10);
289
290 UPDATE tbl_user_mis_report a
291 SET ChannelName = IFNULL((SELECT DISTINCT CASE WHEN b.channel_id = 2 THEN 'HTTPS'
292 WHEN b.channel_id = 4 THEN 'SMPP'
293 ELSE 'FTP' END AS "ChannelName" FROM tbl_user_channel b WHERE a.user_id = b.user_id LIMIT 1),'FTP')
294 WHERE DATE(insert_date) = InDate;
295
296 DO SLEEP(10);
297
298 UPDATE tbl_user_mis_report
299 SET cl_bal = (op_bal + refund_c + bal_transfer_c + revoke_c + expired_c + other_credit - sms_charging - refund_d - bal_transfer_d - revoke_d - expired_d -other_debit)
300 WHERE DATE(insert_date) = InDate
301 AND user_id IN (
302 SELECT user_master_id
303 FROM tbl_users_master
304 WHERE user_type_id = 1
305 )
306 AND IFNULL(cl_bal,0) = 0 ;
307
308 DO SLEEP(10);
309
310 UPDATE tbl_user_mis_report
311 SET cl_bal = (op_bal - refund_c - bal_transfer_c - revoke_c - expired_c - other_credit + sms_charging + refund_d + bal_transfer_d + revoke_d + expired_d + other_debit)
312 WHERE DATE(insert_date) = InDate
313 AND user_id IN (
314 SELECT user_master_id
315 FROM tbl_users_master
316 WHERE user_type_id = 2
317 )
318 AND IFNULL(cl_bal,0) = 0 ;
319
320 END$$
321
322DELIMITER ;