fmmz6h8i

1DROP PROCEDURE IF EXISTS `sp_sales_report`;
2
3DELIMITER ;;
4CREATE DEFINER=`admin_prd`@`%` PROCEDURE `sp_sales_report`(
5    `eventId` BIGINT(20),
6    `session` BIGINT(20),
7    `channel` VARCHAR(255),
8    `from` DATE,
9    `to` DATE
10)
11BEGIN
12    DROP TEMPORARY TABLE IF EXISTS `sales_report_temp`;
13
14    CREATE TEMPORARY TABLE `sales_report_temp`
15    SELECT
16        `e`.`id` AS `eventId`
17        ,`e`.`title` AS `eventTitle`
18        ,`tt`.`id` AS `ticketId`
19        ,(CASE WHEN (`e`.`type` IN ('arena')) THEN CONCAT(`tt`.`name` , ' - ', `gt`.`type`)  ELSE `tt`.`name` END) AS `ticketName`
20        ,`gt`.`id` AS `guestTypeId`
21        ,(CASE WHEN (`e`.`type` IN ('arena')) THEN 'Inteira' ELSE `gt`.`type` END) AS `guestTypeName`
22        ,(CASE WHEN `gt`.`beginsalesdate` IS NOT NULL THEN `gt`.`beginsalesdate` ELSE `tt`.`beginsalesdate` END) AS `beginsalesdate`
23        ,(CASE WHEN `gt`.`beginsalestime` IS NOT NULL THEN `gt`.`beginsalestime` ELSE `tt`.`beginsalestime` END) AS `beginsalestime`
24        ,(CASE WHEN `gt`.`endsalesdate` IS NOT NULL THEN `gt`.`endsalesdate` ELSE `tt`.`endsalesdate` END) AS `endsalesdate`
25        ,(CASE WHEN `gt`.`endsalestime` IS NOT NULL THEN `gt`.`endsalestime` ELSE `tt`.`endsalestime` END) AS `endsalestime`
26        ,`ed`.`date` AS `eventDate`
27        ,(CASE
28           WHEN (`sss`.`paymentoption` IN ('boleto' , 'BoletoBancario')) THEN 'BoletoBancario'
29           WHEN (`sss`.`paymentoption` IN ('creditcard' , 'CartaoCredito')) THEN 'CartaoCredito'
30           WHEN (`sss`.`paymentoption` IN ('gift' , 'freepass', 'free', 'none')) THEN 'Cortesia'
31           WHEN (`sss`.`paymentoption` = 'debitcard') THEN 'CartaoDebito'
32           WHEN (`sss`.`paymentoption` = 'money') THEN 'Dinheiro'
33           WHEN (`sss`.`paymentoption` = 'others') THEN 'Outros'
34           ELSE NULL
35       END) AS `paymentOption`
36       ,SUM(CASE
37            WHEN  `sss`.`paymenttype` = 'freepass' OR `sss`.`paymentoption` = 'free' OR `sss`.`paymentoption` = 'none' THEN 0
38            ELSE `sss`.`price`
39        END) AS `totalRevenue`
40       ,SUM(CASE
41            WHEN  `sss`.`paymenttype` = 'freepass' OR `sss`.`paymentoption` = 'free' OR `sss`.`paymentoption` = 'none' THEN 0
42            ELSE `sss`.`tax`
43        END) AS `totalTax`
44       ,(
45            select sum(`moipTax`)
46            from `sale` `s`
47            where
48                `s`.`status` = 'approved' and `s`.`event_id` = eventId
49                AND `s`.`paymenttype` NOT IN ('freepass','free', 'none')
50                AND ((`from` is null) or (`s`.`creationdate` > `from`))
51                AND ((`to` is null) or (`s`.`creationdate` <= DATE_FORMAT(`to`, '%y-%m-%d 23:59:59')))
52                AND (
53                    (`channel` is null)
54                    or (`channel` = 'online' AND `s`.`paymenttype` IN ('moip' , 'pagarme', 'pagarme_bgs', 'ingresse'))
55                    or (`channel` = 'offline' AND `s`.`paymenttype` NOT IN ('moip' , 'pagarme', 'pagarme_bgs', 'ingresse'))
56                )
57                AND ((`session` is null) or (`s`.`event_date_id` = `session`))
58            ) AS `gatewayTax`
59           ,`tt`.`quantity` AS `ticketQuantity`
60           ,`gt`.`price` AS `ticketPrice`
61           ,`gt`.`tax` AS `ticketTax`
62       ,COUNT(CASE
63            WHEN  `sss`.`paymenttype` = 'freepass' OR `sss`.`paymentoption` = 'free' OR `sss`.`paymentoption` = 'none' THEN NULL
64            ELSE `sss`.`siId`
65        END) AS `ticketsSold`
66       ,COUNT(CASE
67            WHEN  `sss`.`paymenttype` = 'freepass' OR `sss`.`paymentoption` = 'free' OR `sss`.`paymentoption` = 'none' THEN `sss`.`siId`
68            ELSE NULL
69        END) AS `freepass`
70
71    FROM
72        `event` `e`
73        INNER JOIN `eventDate` `ed` ON `ed`.`event_id` = `e`.`id`
74        INNER JOIN (
75            SELECT guestTypeDate.*
76            FROM guestTypeDate
77            INNER JOIN eventDate ON guestTypeDate.event_date_id = eventDate.id
78            WHERE event_id = eventId
79            GROUP BY guest_type_id
80        ) `gtd` ON `gtd`.`event_date_id` = `ed`.`id`
81        INNER JOIN `guestType` `gt` ON `gt`.`id` = `gtd`.`guest_type_id` AND `gt`.`archived` IS FALSE
82        INNER JOIN `ticketType` `tt` ON `tt`.`id` = `gt`.`ticket_type_id` AND `tt`.`archived` IS FALSE
83        LEFT JOIN (
84            SELECT
85                `si`.`price` as `price`
86                ,`si`.`id` as `siId`
87                ,`si`.`tax` as `tax`
88                ,`si`.`guest_type_id` as `guest_type_id`
89                ,`s`.`paymentoption` as `paymentoption`
90                ,`s`.`paymenttype` as `paymenttype`
91                ,`s`.`moipTax` as `moipTax`
92            from `saleItem` `si` INNER JOIN `sale` `s` ON
93                `s`.`id` = `si`.`sale_id`
94            WHERE
95                `s`.`status` = 'approved' and `s`.`event_id` = eventId
96                AND ((`from` is null) or (`s`.`creationdate` > `from`))
97                AND ((`to` is null) or (`s`.`creationdate` <= DATE_FORMAT(`to`, '%y-%m-%d 23:59:59')))
98                AND (
99                    (`channel` is null)
100                    or (`channel` = 'online' AND `s`.`paymenttype` IN ('moip' , 'pagarme', 'pagarme_bgs', 'ingresse'))
101                    or (`channel` = 'offline' AND `s`.`paymenttype` NOT IN ('moip' , 'pagarme', 'pagarme_bgs', 'ingresse'))
102                )
103        ) `sss`
104        ON `sss`.`guest_type_id` = `gt`.`id`
105    WHERE
106        `e`.`id` = eventId
107        AND ((`session` is null) or (`ed`.`id` = `session`))
108
109    GROUP BY `gt`.`id`;
110END;;
111DELIMITER ;