Algorithm-Optimality/stored_procedure at master · JaharshKotha/Algorithm-Optimality · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
DELIMITER ;;
CREATE DEFINER=`blackbird`@`%` PROCEDURE `DaySummaryComputation`(IN today VARCHAR(100))
BEGIN

insert into `DAY_SUMMARY_THRESHOLD` (VenueId , OfflineScanThreshold, RejectThreshold)
 	 		select t1.venueId,t2.RejectThreshold,t1.OfflineScanThreshold from
 	 		(select fres.eid,fres.`venueId`,avg(fres.totalOfflineScan) as `OfflineScanThreshold` from (
 	 		select res.eid,e.`venueId`,res.totalOfflineScan from (
 	 		select eti.eventId as eid, ifnull(COUNT(eti.eventTokenId),0) as totalOfflineScan
			from EVENT_TOKEN_INFO eti, SCAN_RESULT_LOOKUP srl
			WHERE eti.scanResult = srl.scanResult
			AND eti.eventId in (SELECT eventId from EVENTS  where venueId is not null and `localDate` BETWEEN today - INTERVAL 10 DAY AND today)
			AND srl.`online` = 'OFFLINE'
			group by eventId ) AS res
			Inner Join EVENTS e on e.`eventId` = res.eid
			) AS fres group by fres.`venueId` ) t1 left outer join

			(select fres.eid,fres.`venueId`,avg(fres.totalBadScan) as `RejectThreshold` from (
 	 		select res.eid,e.`venueId`,res.totalBadScan from (
 	 		select eti.eventId as eid, ifnull(COUNT(eti.eventTokenId),0) as totalBadScan
			from EVENT_TOKEN_INFO eti, SCAN_RESULT_LOOKUP srl
			WHERE eti.scanResult = srl.scanResult
			AND eti.eventId in (SELECT eventId from EVENTS  where venueId is not null and `localDate` BETWEEN today - INTERVAL 10 DAY AND today)
			AND srl.disposition = 'BAD'
			AND eti.horizonId is not null
			group by eventId) AS res
			Inner Join EVENTS e on e.`eventId` = res.eid
			) AS fres group by fres.`venueId` )t2
			on t1.`venueId` = t2.`venueId`


			union


			select t2.venueId,t2.RejectThreshold,t1.OfflineScanThreshold from
 	 		(select fres.eid,fres.`venueId`,avg(fres.totalOfflineScan) as `OfflineScanThreshold` from (
 	 		select res.eid,e.`venueId`,res.totalOfflineScan from (
 	 		select eti.eventId as eid, ifnull(COUNT(eti.eventTokenId),0) as totalOfflineScan
			from EVENT_TOKEN_INFO eti, SCAN_RESULT_LOOKUP srl
			WHERE eti.scanResult = srl.scanResult
			AND eti.eventId in (SELECT eventId from EVENTS  where venueId is not null and `localDate` BETWEEN today - INTERVAL 10 DAY AND today)
			AND srl.`online` = 'OFFLINE'
			group by eventId ) AS res
			Inner Join EVENTS e on e.`eventId` = res.eid
			) AS fres group by fres.`venueId` ) t1 right outer join

			(select fres.eid,fres.`venueId`,avg(fres.totalBadScan) as `RejectThreshold` from (
 	 		select res.eid,e.`venueId`,res.totalBadScan from (
 	 		select eti.eventId as eid, ifnull(COUNT(eti.eventTokenId),0) as totalBadScan
			from EVENT_TOKEN_INFO eti, SCAN_RESULT_LOOKUP srl
			WHERE eti.scanResult = srl.scanResult
			AND eti.eventId in (SELECT eventId from EVENTS  where venueId is not null and `localDate` BETWEEN today - INTERVAL 10 DAY AND today)
			AND srl.disposition = 'BAD'
			AND eti.horizonId is not null
			group by eventId) AS res
			Inner Join EVENTS e on e.`eventId` = res.eid
			) AS fres group by fres.`venueId` )t2
			on t1.`venueId` = t2.`venueId` ;


END;;
DELIMITER ;