sp_ggr_summary_tables.sql

create or replace function sp_get_winnings(start_date date, end_date date)
returns table(
     winnings numeric(12,2),
     winning_date date,
     bets_won bigint,
     slug varchar(255)
) AS
$body$
DECLARE
   start_dt_sql text := '''' || $1 || ' 00:00:00';
   end_dt_sql text := '''' || $2 || ' 23:59:59 ';
BEGIN
   return query execute
'
-- WINNINGS
--
-- Winnings.
-- major lottery winnings
-- winnings: total value of winnings
-- bets_won: number of bets won
select
   sum(auwl.winnings) as winnings,
   ge.draw_datetime::date as winning_date,
   count(*) as bets_won,
   gg.slug
 FROM auth_userwinningslog auwl
 join game_event ge on ge.id = auwl.event_id
 join game_game gg on gg.id = ge.game_id 
 where ge.draw_datetime >= ' || '''' || $1 || '''' || ' and ge.draw_datetime < ' || '''' || $2 || '''' || '
group by winning_date, gg.slug
UNION
-- sports winnings
-- winnings: total value of winnings
-- bets_won: number of bets won
SELECT 
 	SUM(winnings) AS winnings,
 	auth_usersportswinningslog.created_at::date AS winning_date,
 	count(*) as bets_won,
 	E''sports'' as slug
FROM auth_usersportswinningslog
  WHERE auth_usersportswinningslog.created_at >= ' || '''' || $1 || '''' || '
AND auth_usersportswinningslog.created_at < ' || '''' || $2 || '''' || '
GROUP by winning_date, slug;'

USING start_dt_sql, end_dt_sql;
END
$body$
LANGUAGE plpgsql;

   
--select sp_get_winnings(x.start, x.end) from (select now()::date as start, (now() + interval '1 day')::date as end) x;
create or replace function sp_get_cancellations(start_date date, end_date date)
returns table(
     draw_date date,
     product_id integer,
     product_name varchar(255),
     cancellations numeric
) AS
$body$
DECLARE
   start_dt_sql text := '''' || $1 || ' 00:00:00';
   end_dt_sql text := '''' || $2 || ' 23:59:59 ';
BEGIN
   return query execute
'
-- CANCELLATIONS
--
-- Sports Bets are missing
SELECT  date(ge.draw_datetime) as draw_date,
        ticket.product_id,
        ( SELECT game_product.name
            FROM game_product
           WHERE game_product.id = ticket.product_id) AS product_name,
SUM( CASE WHEN refund = True THEN entry.price ELSE 0 END ) as cancelations
FROM   play_moderatecancellation c,
play_cancellation a,
play_entry entry
join game_event ge on ge.id = entry.event_id
left join play_ticket ticket
     on entry.ticket_id = ticket.id 
WHERE a.content_type_id = 77
AND c.moderation_status = 1
AND c.cancellation_id = a.id
AND a.object_id = entry.id
and c.monitor_moderation_status >= ' || '''' || $1 || '''' || '  and c.monitor_moderation_status  < ' || '''' || $2 || '''' || '
Group by 1,2,3
UNION
SELECT  
	date(date_added) as draw_date,
	E''92''::integer as product_id,
	E''sports'' as product_name,
	SUM( CASE WHEN refund = True THEN bet_placed.stake ELSE 0 END ) as cancelations
FROM  play_moderatecancellation c,
      play_cancellation a,
      sports_betplaced bet_placed
 WHERE
        a.content_type_id = 398
   AND  c.monitor_moderation_status >= ' || '''' || $1 || '''' || '
   AND  c.monitor_moderation_status < ' || '''' || $2 || '''' || '
   AND  c.moderation_status = 1
   AND  c.cancellation_id = a.id
   AND  a.object_id = bet_placed.id
 GROUP BY 1,2,3;'
USING start_dt_sql, end_dt_sql;
END
$body$
LANGUAGE plpgsql;
create or replace function sp_get_bets_placed(start_date date, end_date date)
returns table(
     bets_placed integer,
     bets_value numeric(12,2),
     bets_won numeric,
     game_type varchar(255),
     updated_date date,
     slug varchar(255)
) AS
$body$
DECLARE
   start_dt_sql text := '''' || $1 || ' 00:00:00';
   end_dt_sql text := '''' || $2 || ' 23:59:59 ';
BEGIN
   return query execute
'WITH events as (
  select id from game_event where draw_datetime>= ' || '''' || $1 || '''' || ' and draw_datetime <' || '''' || $2 ||'''' || '
)
SELECT COUNT(play_entry.id)::integer as bets_placed,
       COALESCE(SUM(play_entry.price), 0) as bets_value,
       COALESCE(SUM(play_entry.winnings), 0) as bets_won,
       gg.game_type,
       ge.draw_datetime::date as transaction_date,
       gg.slug
FROM play_entry
JOIN game_event ge on ge.id = play_entry.event_id
join game_game gg on gg.id = ge.game_id 
and play_entry.event_id IN (SELECT id from events)
group by gg.slug, gg.game_type,  ge.draw_datetime::date
UNION
SELECT sum(x.bets_placed)::integer as bets_placed,
       sum(x.bets_value) as bets_value,
       sum(x.bets_won) as bets_won,
       x.game_type,
       x.resulted_date,
       x.slug
FROM (       
SELECT  
        count(sports_betplaced.id) as bets_placed, 
        COALESCE(SUM( sports_betplaced.stake ),0) as bets_value,
        COALESCE(SUM( ausp.winnings),0) as bets_won,
        E''Sports'' as game_type,
        sports_betresult.resulted_date::date as resulted_date,
        E''sports'' as slug
 FROM  sports_betplaced
 INNER JOIN sports_betresult ON sports_betresult.bet_placed_id = sports_betplaced.id
 LEFT JOIN auth_usersportswinningslog ausp on ausp.bet_placed_id = sports_betplaced.id
WHERE  sports_betresult.resulted_date >=' || '''' || $1 || '''' || '
  AND  sports_betresult.resulted_date < ' || '''' || $2 || '''' || '
  AND  sports_betresult.result != E''Pending''
  group by slug, game_type,  resulted_date  
) as x
group by x.slug, x.game_type, x.resulted_date;'
USING start_dt_sql, end_dt_sql;
END
$body$
LANGUAGE plpgsql;