CMS views made readable

cms-views.sql

CREATE OR REPLACE
    ALGORITHM=MERGE SQL SECURITY DEFINER VIEW `cms_view_bets_preplay` AS
SELECT
    coalesce(`b_each_way`.`id`, `b`.`id`) AS `id`,
    `b`.`status` AS `status`,
    `b`.`history_id` AS `transaction_id`,
    `b_each_way`.`win_bet_id` AS `ew_win_bet_id`,
    coalesce(`b_each_way`.`outcome`, `b`.`outcome`) AS `outcome`,
    `b`.`is_each_way` AS `is_each_way`,
    `b`.`bet_structure_id` AS `bet_structure_id`,
    `bd`.`id` AS `bet_data_id`,
    `bd`.`price` AS `price`,
    `bdei`.`value` AS `place_odds_divisor`,
    `bdei2`.`value` AS `places_paid`,
    if(`bd`.`void`, 'VOID', `bd`.`outcome`) AS `bet_outcome`,
    `b`.`stake` AS `stake`,
    `b`.`total_stake` AS `total_stake`,
    (coalesce(`b`.`returns`, 0) + coalesce(`b_each_way`.`returns`, 0)) AS `returns`,
    (coalesce(`b`.`potential_return`, 0) + coalesce(`b_each_way`.`potential_return`, 0)) AS `potential_return`,
    `b`.`free_bet_id` AS `free_bet_id`,
    `bd`.`starting_price` AS `is_sp`,
    `bd`.`deduction_factor` AS `deduction_factor`,
    `bs`.`name` AS `multibet_name`,
    format((`b`.`total_stake` / `b`.`stake`), 0) AS `calculated_num_bets`,
    coalesce(`m`.`display_name`, `m`.`name`) AS `market`,
    `m`.`translate` AS `translate`,
    `m`.`id` AS `market_id`,
    NULL AS `exchange_market_id`,
    NULL AS `exchange_market_subtype`,
    NULL AS `exchange_market_type`,
    `e`.`name` AS `event_name`,
    `e`.`scheduled_start` AS `scheduled_start`,
    `e`.`home_team_id` AS `home_team_id`,
    `e`.`away_team_id` AS `away_team_id`,
    NULL AS `team1_name`,
    NULL AS `team2_name`,
    `s`.`name` AS `selection_name`,
    if(`bd`.`void`, 'VOID', `s`.`outcome`) AS `selection_outcome`,
    if(`bd`.`void`, 'VOID', `bd`.`outcome`) AS `display_outcome`,
    NULL AS `selection_type`,
    `s`.`specialoddsvalue` AS `specialoddsvalue`,
    `h`.`id` AS `history_id`,
    `h`.`customer_id` AS `customer_id`,
    `h`.`date` AS `history_date`,
    `h`.`amount` AS `amount`,
    `h`.`balance` AS `balance`,
    `h`.`is_inplay` AS `is_inplay`,
    `h`.`transaction_type_id` AS `transaction_type_id`,
    `tt`.`code` AS `transaction_code`,
    `tt`.`display_name` AS `transaction`,
    if(isnull(`o`.`event_id`), 0, 1) AS `is_outright`,
    `s`.`id` AS `selection_id`,
    `sei`.`value` AS `trap_number`,
    concat(`cp`.`first_name`, ' ', `cp`.`last_name`) AS `customer_name`,
    `t_type`.`code` AS `trading_type_code`,
    coalesce(`sports`.`display_name`, `sports`.`name`) AS `sport_name`,
    `e`.`sport_id` AS `sport_id`,
    `ss`.`feed_sport_id` AS `inplay_sport_id`,
    `categories`.`name` AS `category_name`,
    `tournaments`.`name` AS `tournament_name`,
    `cp`.`mobile_number` AS `mobile_number`,
    `ee`.`value` AS `feed_event_id`,
    if(isnull(`bdei3`.`bet_data_id`),0,1) AS `is_freeleg`
FROM
    (((((((((((((((((((((`customer_history` `h`
    JOIN `customer_profile` `cp` ON ((`h`.`customer_id` = `cp`.`acm_user_id`)))
    LEFT JOIN `trading_type` `t_type` ON ((`cp`.`trading_type_id` = `t_type`.`id`)))
    JOIN `transaction_types` `tt` ON ((`tt`.`id` = `h`.`transaction_type_id`)))
    JOIN `bets` `b` ON (((`h`.`customer_id` = `b`.`customer_id`) AND (`h`.`transaction_reference` = `b`.`id`))))
    LEFT JOIN `bets` `b_each_way` ON ((`b_each_way`.`win_bet_id` = `b`.`id`)))
    JOIN `bet_data` `bd` ON ((`b`.`id` = `bd`.`bet_id`)))
    LEFT JOIN `bet_data_extra_info` `bdei` ON (((`bd`.`id` = `bdei`.`bet_data_id`) AND (`bdei`.`attribute` = 'place_odds_divisor'))))
    LEFT JOIN `bet_data_extra_info` `bdei2` ON (((`bd`.`id` = `bdei2`.`bet_data_id`) AND (`bdei2`.`attribute` = 'places_paid'))))
    LEFT JOIN `bet_data_extra_info` `bdei3` ON (`b`.`bet_structure_id` = 26 AND `bd`.`id` = `bdei3`.`bet_data_id` AND `bdei3`.`attribute` = 'is_freeleg' AND `bdei3`.`value` = '1'))
    JOIN `bet_structure` `bs` ON ((`b`.`bet_structure_id` = `bs`.`id`)))
    JOIN `books` `boo` ON ((`b`.`book_id` = `boo`.`id`)))
    JOIN `markets` `m` ON ((`bd`.`market_id` = `m`.`id`)))
    JOIN `events` `e` ON (((`bd`.`event_id` = `e`.`id`) AND (`e`.`event_type_id` <> 4))))
    JOIN `sports` ON ((`e`.`sport_id` = `sports`.`id`)))
    LEFT JOIN `supplier_sports` `ss` ON (((`sports`.`id` = `ss`.`sport_id`) AND (`ss`.`supplier_id` = 2))))
    JOIN `categories` ON ((`e`.`category_id` = `categories`.`id`)))
    JOIN `tournaments` ON ((`e`.`tournament_id` = `tournaments`.`id`)))
    JOIN `selections` `s` ON ((`bd`.`selection_id` = `s`.`id`)))
    LEFT JOIN `selections_extra_info` `sei` ON (((`sei`.`selection_id` = `s`.`id`) AND (`sei`.`attribute` = 'trap_number'))))
    LEFT JOIN `events_extra_info` `ee` ON (((`e`.`id` = `ee`.`event_id`) AND (`ee`.`attribute` = 'virtual_feed_event_id'))))
    LEFT JOIN `outrights` `o` ON ((`e`.`id` = `o`.`event_id`)))
WHERE 
    isnull(`h`.`transaction_failure_id`)
    AND (`h`.`is_inplay` = 0)
    AND (`h`.`transaction_type_id` IN (3, 4))
    AND (`bd`.`inplay` = 0);


CREATE OR REPLACE
    ALGORITHM=MERGE SQL SECURITY DEFINER VIEW `cms_view_bets_inplay2` AS
SELECT
    coalesce(`b_each_way`.`id`, `b`.`id`) AS `id`,
    `b`.`status` AS `status`,
    `b`.`history_id` AS `transaction_id`,
    `b_each_way`.`win_bet_id` AS `ew_win_bet_id`,
    coalesce(`b_each_way`.`outcome`, `b`.`outcome`) AS `outcome`,
    `b`.`is_each_way` AS `is_each_way`,
    `b`.`bet_structure_id` AS `bet_structure_id`,
    `bd`.`id` AS `bet_data_id`,
    `bd`.`price` AS `price`,
    `bdei`.`value` AS `place_odds_divisor`,
    `bdei2`.`value` AS `places_paid`,
    if(`bd`.`void`, 'VOID', `bd`.`outcome`) AS `bet_outcome`,
    `b`.`stake` AS `stake`,
    `b`.`total_stake` AS `total_stake`,
    (coalesce(`b`.`returns`, 0) + coalesce(`b_each_way`.`returns`, 0)) AS `returns`,
    (coalesce(`b`.`potential_return`, 0) + coalesce(`b_each_way`.`potential_return`, 0)) AS `potential_return`,
    `b`.`free_bet_id` AS `free_bet_id`,
    `bd`.`starting_price` AS `is_sp`,
    `bd`.`deduction_factor` AS `deduction_factor`,
    `bs`.`name` AS `multibet_name`,
    format((`b`.`total_stake` / `b`.`stake`), 0) AS `calculated_num_bets`,
    coalesce(`m`.`display_name`, `m`.`name`) AS `market`,
    `m`.`translate` AS `translate`,
    `m`.`id` AS `market_id`,
    NULL AS `exchange_market_id`,
    NULL AS `exchange_market_subtype`,
    NULL AS `exchange_market_type`,
    `e`.`name` AS `event_name`,
    `e`.`scheduled_start` AS `scheduled_start`,
    `e`.`home_team_id` AS `home_team_id`,
    `e`.`away_team_id` AS `away_team_id`,
    NULL AS `team1_name`,
    NULL AS `team2_name`,
    `s`.`name` AS `selection_name`,
    if(`bd`.`void`, 'VOID', `s`.`outcome`) AS `selection_outcome`,
    if(`bd`.`void`, 'VOID', `bd`.`outcome`) AS `display_outcome`,
    NULL AS `selection_type`,
    `s`.`specialoddsvalue` AS `specialoddsvalue`,
    `h`.`id` AS `history_id`,
    `h`.`customer_id` AS `customer_id`,
    `h`.`date` AS `history_date`,
    `h`.`amount` AS `amount`,
    `h`.`balance` AS `balance`,
    `h`.`is_inplay` AS `is_inplay`,
    `h`.`transaction_type_id` AS `transaction_type_id`,
    `tt`.`code` AS `transaction_code`,
    `tt`.`display_name` AS `transaction`,
    NULL AS `is_outright`,
    `s`.`id` AS `selection_id`,
    `sei`.`value` AS `trap_number`,
    concat(`cp`.`first_name`, ' ', `cp`.`last_name`) AS `customer_name`,
    `t_type`.`code` AS `trading_type_code`,
    coalesce(`sports`.`display_name`, `sports`.`name`) AS `sport_name`,
    `e`.`sport_id` AS `sport_id`,
    `ss`.`feed_sport_id` AS `inplay_sport_id`,
    `categories`.`name` AS `category_name`,
    `tournaments`.`name` AS `tournament_name`,
    `cp`.`mobile_number` AS `mobile_number`,
    `ee`.`value` AS `feed_event_id`,
    if(isnull(`bdei3`.`bet_data_id`),0,1) AS `is_freeleg`
FROM
    (((((((((((((((((((((`customer_history` `h`
    JOIN `customer_profile` `cp` ON ((`h`.`customer_id` = `cp`.`acm_user_id`)))
    LEFT JOIN `trading_type` `t_type` ON ((`cp`.`trading_type_id` = `t_type`.`id`)))
    JOIN `transaction_types` `tt` ON ((`tt`.`id` = `h`.`transaction_type_id`)))
    JOIN `bets` `b` ON (((`h`.`customer_id` = `b`.`customer_id`) AND (`h`.`transaction_reference` = `b`.`id`))))
    LEFT JOIN `bets` `b_each_way` ON ((`b_each_way`.`win_bet_id` = `b`.`id`)))
    JOIN `bet_data` `bd` ON ((`b`.`id` = `bd`.`bet_id`)))
    LEFT JOIN `bet_data_extra_info` `bdei` ON (((`bd`.`id` = `bdei`.`bet_data_id`) AND (`bdei`.`attribute` = 'place_odds_divisor'))))
    LEFT JOIN `bet_data_extra_info` `bdei2` ON (((`bd`.`id` = `bdei2`.`bet_data_id`) AND (`bdei2`.`attribute` = 'places_paid'))))
    LEFT JOIN `bet_data_extra_info` `bdei3` ON (`b`.`bet_structure_id` = 26 AND `bd`.`id` = `bdei3`.`bet_data_id` AND `bdei3`.`attribute` = 'is_freeleg' AND `bdei3`.`value` = '1'))
    JOIN `bet_structure` `bs` ON ((`b`.`bet_structure_id` = `bs`.`id`)))
    JOIN `books` `boo` ON ((`b`.`book_id` = `boo`.`id`)))
    JOIN `markets` `m` ON ((`bd`.`market_id` = `m`.`id`)))
    JOIN `events` `e` ON (((`bd`.`event_id` = `e`.`id`) AND (`e`.`event_type_id` = 4))))
    JOIN `event_type` `et` ON ((`et`.`id` = `e`.`event_type_id`)))
    JOIN `sports` ON ((`e`.`sport_id` = `sports`.`id`)))
    LEFT JOIN `supplier_sports` `ss` ON (((`sports`.`id` = `ss`.`sport_id`) AND (`ss`.`supplier_id` = 2))))
    JOIN `categories` ON ((`e`.`category_id` = `categories`.`id`)))
    JOIN `tournaments` ON ((`e`.`tournament_id` = `tournaments`.`id`)))
    JOIN `selections` `s` ON ((`bd`.`selection_id` = `s`.`id`)))
    LEFT JOIN `selections_extra_info` `sei` ON (((`sei`.`selection_id` = `s`.`id`) AND (`sei`.`attribute` = 'trap_number'))))
    LEFT JOIN `events_extra_info` `ee` ON (((`e`.`id` = `ee`.`event_id`) AND (`ee`.`attribute` = 'virtual_feed_event_id'))))
WHERE
    isnull(`h`.`transaction_failure_id`)
    AND (`h`.`is_inplay` = 1)
    AND (`bd`.`inplay` = 1)
    AND (`et`.`code` = 'INPLAY')
    AND (`s`.`inplay` = 1)
    AND (`h`.`transaction_type_id` IN (3, 4));


CREATE OR REPLACE
    ALGORITHM=MERGE SQL SECURITY DEFINER VIEW `cms_view_bets_inplay` AS
SELECT
    concat(`cib`.`bet_id`, 'i') AS `id`,
    `cib`.`status` AS `status`,
    `b`.`history_id` AS `transaction_id`,
    NULL AS `ew_win_bet_id`,
    `cib`.`outcome` AS `outcome`,
    0 AS `is_each_way`,
    `cib`.`bet_structure_id` AS `bet_structure_id`,
    `bdei`.`value` AS `place_odds_divisor`,
    `bdei2`.`value` AS `places_paid`,
    `cibd`.`bet_data_id` AS `bet_data_id`,
    `cibd`.`price` AS `price`,
    `cibd`.`outcome` AS `bet_outcome`,
    `cib`.`stake` AS `stake`,
    `cib`.`total_stake` AS `total_stake`,
    `cib`.`returns` AS `returns`,
    `cib`.`potential_return` AS `potential_return`,
    `cib`.`free_bet_id` AS `free_bet_id`,
    0 AS `is_sp`,
    0 AS `deduction_factor`,
    `bs`.`name` AS `multibet_name`,
    format((`cib`.`total_stake` / `cib`.`stake`), 0) AS `calculated_num_bets`,
    `cim`.`market_name` AS `market`,
    0 AS `translate`,
    NULL AS `market_id`,
    `cim`.`exchange_market_id` AS `exchange_market_id`,
    `cim`.`exchange_market_subtype` AS `exchange_market_subtype`,
    `cim`.`exchange_market_type` AS `exchange_market_type`,
    `ce`.`event_name` AS `event_name`,
    `ce`.`kickoff_time` AS `scheduled_start`,
    NULL AS `home_team_id`,
    NULL AS `away_team_id`,
    `ce`.`team1` AS `team1_name`,
    `ce`.`team2` AS `team2_name`,
    `cims`.`selection_name` AS `selection_name`,
    '' AS `selection_outcome`,
    `cibd`.`outcome` AS `display_outcome`,
    `cims`.`selection_type` AS `selection_type`,
    `cs`.`specialoddsvalue` AS `specialoddsvalue`,
    `h`.`id` AS `history_id`,
    `h`.`customer_id` AS `customer_id`,
    `h`.`date` AS `history_date`,
    `h`.`amount` AS `amount`,
    `h`.`balance` AS `balance`,
    `h`.`is_inplay` AS `is_inplay`,
    `h`.`transaction_type_id` AS `transaction_type_id`,
    `tt`.`code` AS `transaction_code`,
    `tt`.`display_name` AS `transaction`,
    0 AS `is_outright`,
    `cs`.`selection_id` AS `selection_id`,
    NULL AS `trap_number`,
    concat(`cp`.`first_name`, ' ', `cp`.`last_name`) AS `customer_name`,
    `t_type`.`code` AS `trading_type_code`,
    replace(`cis`.`sport_name`, 'Soccer', 'Football') AS `sport_name`,
    `ss`.`sport_id` AS `sport_id`,
    `ce`.`exchange_sport_id` AS `inplay_sport_id`,
    `cic`.`category_name` AS `category_name`,
    `cit`.`tournament_name` AS `tournament_name`,
    `cp`.`mobile_number` AS `mobile_number`,
    `ee`.`value` AS `feed_event_id`,
    0 AS `is_freeleg`
FROM
    (((((((((((((((((`customer_history` `h`
    JOIN `customer_profile` `cp` ON ((`h`.`customer_id` = `cp`.`acm_user_id`)))
    LEFT JOIN `trading_type` `t_type` ON ((`cp`.`trading_type_id` = `t_type`.`id`)))
    JOIN `transaction_types` `tt` ON ((`tt`.`id` = `h`.`transaction_type_id`)))
    JOIN `core_inplay_bet` `cib` ON (((`h`.`customer_id` = `cib`.`customer_id`) AND (`h`.`transaction_reference` = `cib`.`bet_id`))))
    JOIN `core_inplay_bet_data` `cibd` ON ((`cib`.`bet_id` = `cibd`.`bet_id`)))
    JOIN `bet_structure` `bs` ON ((`cib`.`bet_structure_id` = `bs`.`id`)))
    JOIN `core_events` `ce` ON ((`cibd`.`event_id` = `ce`.`event_id`)))
    JOIN `core_inplay_tournament` `cit` ON ((`ce`.`exchange_tournament_id` = `cit`.`exchange_tournament_id`)))
    JOIN `core_inplay_category` `cic` ON ((`ce`.`exchange_category_id` = `cic`.`exchange_category_id`)))
    JOIN `core_inplay_sport` `cis` ON ((`ce`.`exchange_sport_id` = `cis`.`exchange_sport_id`)))
    LEFT JOIN `supplier_sports` `ss` ON (((`cis`.`exchange_sport_id` = `ss`.`feed_sport_id`) AND (`ss`.`supplier_id` = 2))))
    LEFT JOIN `bet_data_extra_info` `bdei` ON (((`cib`.`bet_id` = `bdei`.`bet_data_id`) AND (`bdei`.`attribute` = 'place_odds_divisor'))))
    LEFT JOIN `bet_data_extra_info` `bdei2` ON (((`cib`.`bet_id` = `bdei2`.`bet_data_id`) AND (`bdei2`.`attribute` = 'places_paid'))))
    JOIN `core_inplay_market_selection` `cims` ON ((`cims`.`inplay_market_selection_id` = `cibd`.`inplay_market_selection_id`)))
    JOIN `core_inplay_market` `cim` ON (((`cim`.`exchange_market_id` = `cims`.`exchange_market_id`) AND (`cim`.`exchange_market_subtype` = `cims`.`exchange_market_subtype`))))
    JOIN `core_selection` `cs` ON ((`cs`.`selection_id` = `cibd`.`selection_id`)))
    JOIN `events_extra_info` `ee` ON (((`ce`.`event_id` = `ee`.`event_id`) AND (`ee`.`attribute` = 'virtual_feed_event_id')))
    LEFT JOIN `bets` `b` ON ((`b`.`id` = `cib`.`bet_id`)))
WHERE
    isnull(`h`.`transaction_failure_id`)
    AND (`h`.`is_inplay` = 1)
    AND (`h`.`transaction_type_id` IN (3, 4));

CREATE OR REPLACE
    ALGORITHM = MERGE SQL SECURITY DEFINER VIEW `cms_view_bets_mixed_preplay_inplay` AS
SELECT
    coalesce(`b_each_way`.`id`, `b`.`id`) AS `id`,
    `b`.`status` AS `status`,
    `b`.`history_id` AS `transaction_id`,
    `b_each_way`.`win_bet_id` AS `ew_win_bet_id`,
    coalesce(`b_each_way`.`outcome`, `b`.`outcome`) AS `outcome`,
    `b`.`is_each_way` AS `is_each_way`,
    `b`.`bet_structure_id` AS `bet_structure_id`,
    `bd`.`id` AS `bet_data_id`,
    `bd`.`price` AS `price`,
    `bdei`.`value` AS `place_odds_divisor`,
    `bdei2`.`value` AS `places_paid`,
    if(`bd`.`void`, 'VOID', `bd`.`outcome`) AS `bet_outcome`,
    `b`.`stake` AS `stake`,
    `b`.`total_stake` AS `total_stake`,
    (coalesce(`b`.`returns`, 0) + coalesce(`b_each_way`.`returns`, 0)) AS `returns`,
    (coalesce(`b`.`potential_return`, 0) + coalesce(`b_each_way`.`potential_return`, 0)) AS `potential_return`,
    `b`.`free_bet_id` AS `free_bet_id`,
    `bd`.`starting_price` AS `is_sp`,
    `bd`.`deduction_factor` AS `deduction_factor`,
    `bs`.`name` AS `multibet_name`,
    format((`b`.`total_stake` / `b`.`stake`), 0) AS `calculated_num_bets`,
    coalesce(`m`.`display_name`, `m`.`name`) AS `market`,
    `m`.`translate` AS `translate`,
    `m`.`id` AS `market_id`,
    NULL AS `exchange_market_id`,
    NULL AS `exchange_market_subtype`,
    NULL AS `exchange_market_type`,
    `e`.`name` AS `event_name`,
    `e`.`scheduled_start` AS `scheduled_start`,
    `e`.`home_team_id` AS `home_team_id`,
    `e`.`away_team_id` AS `away_team_id`,
    NULL AS `team1_name`,
    NULL AS `team2_name`,
    `s`.`name` AS `selection_name`,
    if(`bd`.`void`, 'VOID', `s`.`outcome`) AS `selection_outcome`,
    if(`bd`.`void`, 'VOID', `bd`.`outcome`) AS `display_outcome`,
    NULL AS `selection_type`,
    `s`.`specialoddsvalue` AS `specialoddsvalue`,
    `h`.`id` AS `history_id`,
    `h`.`customer_id` AS `customer_id`,
    `h`.`date` AS `history_date`,
    `h`.`amount` AS `amount`,
    `h`.`balance` AS `balance`,
    `s`.`inplay` AS `is_inplay`,
    `h`.`transaction_type_id` AS `transaction_type_id`,
    `tt`.`code` AS `transaction_code`,
    `tt`.`display_name` AS `transaction`,
    if(isnull(`o`.`event_id`), 0, 1) AS `is_outright`,
    `s`.`id` AS `selection_id`,
    `sei`.`value` AS `trap_number`,
    concat(`cp`.`first_name`, ' ', `cp`.`last_name`) AS `customer_name`,
    `t_type`.`code` AS `trading_type_code`,
    coalesce(`sports`.`display_name`, `sports`.`name`) AS `sport_name`,
    `e`.`sport_id` AS `sport_id`,
    `ss`.`feed_sport_id` AS `inplay_sport_id`,
    `categories`.`name` AS `category_name`,
    `tournaments`.`name` AS `tournament_name`,
    `cp`.`mobile_number` AS `mobile_number`,
    `ee`.`value` AS `feed_event_id`,
    if(isnull(`bdei3`.`bet_data_id`),0,1) AS `is_freeleg`
FROM
    (((((((((((((((((((((`customer_history` `h`
    JOIN `customer_profile` `cp` ON ((`h`.`customer_id` = `cp`.`acm_user_id`)))
    LEFT JOIN `trading_type` `t_type` ON ((`cp`.`trading_type_id` = `t_type`.`id`)))
    JOIN `transaction_types` `tt` ON ((`tt`.`id` = `h`.`transaction_type_id`)))
    JOIN `bets` `b` ON (((`h`.`customer_id` = `b`.`customer_id`) AND (`h`.`transaction_reference` = `b`.`id`))))
    LEFT JOIN `bets` `b_each_way` ON ((`b_each_way`.`win_bet_id` = `b`.`id`)))
    JOIN `bet_data` `bd` ON ((`b`.`id` = `bd`.`bet_id`)))
    LEFT JOIN `bet_data_extra_info` `bdei` ON (((`bd`.`id` = `bdei`.`bet_data_id`) AND (`bdei`.`attribute` = 'place_odds_divisor'))))
    LEFT JOIN `bet_data_extra_info` `bdei2` ON (((`bd`.`id` = `bdei2`.`bet_data_id`) AND (`bdei2`.`attribute` = 'places_paid'))))
    LEFT JOIN `bet_data_extra_info` `bdei3` ON (`b`.`bet_structure_id` = 26 AND `bd`.`id` = `bdei3`.`bet_data_id` AND `bdei3`.`attribute` = 'is_freeleg' AND `bdei3`.`value` = '1'))
    JOIN `bet_structure` `bs` ON ((`b`.`bet_structure_id` = `bs`.`id`)))
    JOIN `books` `boo` ON ((`b`.`book_id` = `boo`.`id`)))
    JOIN `markets` `m` ON ((`bd`.`market_id` = `m`.`id`)))
    JOIN `events` `e` ON (((`bd`.`event_id` = `e`.`id`))))
    JOIN `sports` ON ((`e`.`sport_id` = `sports`.`id`)))
    LEFT JOIN `supplier_sports` `ss` ON (((`sports`.`id` = `ss`.`sport_id`) AND (`ss`.`supplier_id` = 2))))
    JOIN `categories` ON ((`e`.`category_id` = `categories`.`id`)))
    JOIN `tournaments` ON ((`e`.`tournament_id` = `tournaments`.`id`)))
    JOIN `selections` `s` ON ((`bd`.`selection_id` = `s`.`id`)))
    LEFT JOIN `selections_extra_info` `sei` ON (((`sei`.`selection_id` = `s`.`id`) AND (`sei`.`attribute` = 'trap_number'))))
    LEFT JOIN `events_extra_info` `ee` ON (((`e`.`id` = `ee`.`event_id`) AND (`ee`.`attribute` = 'virtual_feed_event_id'))))
    LEFT JOIN `outrights` `o` ON ((`e`.`id` = `o`.`event_id`)))
WHERE 
    isnull(`h`.`transaction_failure_id`)
    AND (`h`.`transaction_type_id` IN (3, 4));

rollback.sql

CREATE OR REPLACE ALGORITHM=MERGE SQL SECURITY DEFINER VIEW 
`cms_view_bets_preplay` AS select coalesce(`b_each_way`.`id`,`b`.`id`) AS 
`id`,`b`.`status` AS `status`,`b`.`history_id` AS 
`transaction_id`,`b_each_way`.`win_bet_id` AS 
`ew_win_bet_id`,coalesce(`b_each_way`.`outcome`,`b`.`outcome`) AS 
`outcome`,`b`.`is_each_way` AS `is_each_way`,`b`.`bet_structure_id` AS 
`bet_structure_id`,`bd`.`id` AS `bet_data_id`,`bd`.`price` AS 
`price`,`bdei`.`value` AS `place_odds_divisor`,`bdei2`.`value` AS 
`places_paid`,if(`bd`.`void`,'VOID',`bd`.`outcome`) AS `bet_outcome`,`b`.`stake`
AS `stake`,`b`.`total_stake` AS `total_stake`,(coalesce(`b`.`returns`,0) + 
coalesce(`b_each_way`.`returns`,0)) AS `returns`,
(coalesce(`b`.`potential_return`,0) + 
coalesce(`b_each_way`.`potential_return`,0)) AS 
`potential_return`,`b`.`free_bet_id` AS `free_bet_id`,`bd`.`starting_price` AS 
`is_sp`,`bd`.`deduction_factor` AS `deduction_factor`,`bs`.`name` AS 
`multibet_name`,format((`b`.`total_stake` / `b`.`stake`),0) AS 
`calculated_num_bets`,coalesce(`m`.`display_name`,`m`.`name`) AS 
`market`,`m`.`translate` AS `translate`,`m`.`id` AS `market_id`,NULL AS 
`exchange_market_id`,NULL AS `exchange_market_subtype`,NULL AS 
`exchange_market_type`,`e`.`name` AS `event_name`,`e`.`scheduled_start` AS 
`scheduled_start`,`e`.`home_team_id` AS `home_team_id`,`e`.`away_team_id` AS 
`away_team_id`,NULL AS `team1_name`,NULL AS `team2_name`,`s`.`name` AS 
`selection_name`,if(`bd`.`void`,'VOID',`s`.`outcome`) AS 
`selection_outcome`,if(`bd`.`void`,'VOID',`bd`.`outcome`) AS 
`display_outcome`,NULL AS `selection_type`,`s`.`specialoddsvalue` AS 
`specialoddsvalue`,`h`.`id` AS `history_id`,`h`.`customer_id` AS 
`customer_id`,`h`.`date` AS `history_date`,`h`.`amount` AS 
`amount`,`h`.`balance` AS `balance`,`h`.`is_inplay` AS 
`is_inplay`,`h`.`transaction_type_id` AS `transaction_type_id`,`tt`.`code` AS 
`transaction_code`,`tt`.`display_name` AS 
`transaction`,if(isnull(`o`.`event_id`),0,1) AS `is_outright`,`s`.`id` AS 
`selection_id`,`sei`.`value` AS `trap_number`,concat(`cp`.`first_name`,' 
',`cp`.`last_name`) AS `customer_name`,`t_type`.`code` AS 
`trading_type_code`,coalesce(`sports`.`display_name`,`sports`.`name`) AS 
`sport_name`,`e`.`sport_id` AS `sport_id`,`ss`.`feed_sport_id` AS 
`inplay_sport_id`,`categories`.`name` AS `category_name`,`tournaments`.`name` AS
`tournament_name`,`cp`.`mobile_number` AS `mobile_number`,`ee`.`value` AS 
`feed_event_id` from ((((((((((((((((((((`customer_history` `h` join 
`customer_profile` `cp` on((`h`.`customer_id` = `cp`.`acm_user_id`))) left join 
`trading_type` `t_type` on((`cp`.`trading_type_id` = `t_type`.`id`))) join 
`transaction_types` `tt` on((`tt`.`id` = `h`.`transaction_type_id`))) join 
`bets` `b` on(((`h`.`customer_id` = `b`.`customer_id`) and 
(`h`.`transaction_reference` = `b`.`id`)))) left join `bets` `b_each_way` 
on((`b_each_way`.`win_bet_id` = `b`.`id`))) join `bet_data` `bd` on((`b`.`id` = 
`bd`.`bet_id`))) left join `bet_data_extra_info` `bdei` on(((`bd`.`id` = 
`bdei`.`bet_data_id`) and (`bdei`.`attribute` = 'place_odds_divisor')))) left 
join `bet_data_extra_info` `bdei2` on(((`bd`.`id` = `bdei2`.`bet_data_id`) and 
(`bdei2`.`attribute` = 'places_paid')))) join `bet_structure` `bs` 
on((`b`.`bet_structure_id` = `bs`.`id`))) join `books` `boo` on((`b`.`book_id` =
`boo`.`id`))) join `markets` `m` on((`bd`.`market_id` = `m`.`id`))) join 
`events` `e` on(((`bd`.`event_id` = `e`.`id`) and (`e`.`event_type_id` <> 4)))) 
join `sports` on((`e`.`sport_id` = `sports`.`id`))) left join `supplier_sports` 
`ss` on(((`sports`.`id` = `ss`.`sport_id`) and (`ss`.`supplier_id` = 2)))) join 
`categories` on((`e`.`category_id` = `categories`.`id`))) join `tournaments` 
on((`e`.`tournament_id` = `tournaments`.`id`))) join `selections` `s` 
on((`bd`.`selection_id` = `s`.`id`))) left join `selections_extra_info` `sei` 
on(((`sei`.`selection_id` = `s`.`id`) and (`sei`.`attribute` = 'trap_number'))))
left join `events_extra_info` `ee` on(((`e`.`id` = `ee`.`event_id`) and 
(`ee`.`attribute` = 'virtual_feed_event_id')))) left join `outrights` `o` 
on((`e`.`id` = `o`.`event_id`))) where (isnull(`h`.`transaction_failure_id`) and
(`h`.`is_inplay` = 0) and (`h`.`transaction_type_id` in (3,4)) and 
(`bd`.`inplay` = 0));

CREATE OR REPLACE ALGORITHM=MERGE SQL SECURITY DEFINER VIEW 
`cms_view_bets_inplay2` AS select coalesce(`b_each_way`.`id`,`b`.`id`) AS 
`id`,`b`.`status` AS `status`,`b`.`history_id` AS 
`transaction_id`,`b_each_way`.`win_bet_id` AS 
`ew_win_bet_id`,coalesce(`b_each_way`.`outcome`,`b`.`outcome`) AS 
`outcome`,`b`.`is_each_way` AS `is_each_way`,`b`.`bet_structure_id` AS 
`bet_structure_id`,`bd`.`id` AS `bet_data_id`,`bd`.`price` AS 
`price`,`bdei`.`value` AS `place_odds_divisor`,`bdei2`.`value` AS 
`places_paid`,if(`bd`.`void`,'VOID',`bd`.`outcome`) AS `bet_outcome`,`b`.`stake`
AS `stake`,`b`.`total_stake` AS `total_stake`,(coalesce(`b`.`returns`,0) + 
coalesce(`b_each_way`.`returns`,0)) AS `returns`,
(coalesce(`b`.`potential_return`,0) + 
coalesce(`b_each_way`.`potential_return`,0)) AS 
`potential_return`,`b`.`free_bet_id` AS `free_bet_id`,`bd`.`starting_price` AS 
`is_sp`,`bd`.`deduction_factor` AS `deduction_factor`,`bs`.`name` AS 
`multibet_name`,format((`b`.`total_stake` / `b`.`stake`),0) AS 
`calculated_num_bets`,coalesce(`m`.`display_name`,`m`.`name`) AS 
`market`,`m`.`translate` AS `translate`,`m`.`id` AS `market_id`,NULL AS 
`exchange_market_id`,NULL AS `exchange_market_subtype`,NULL AS 
`exchange_market_type`,`e`.`name` AS `event_name`,`e`.`scheduled_start` AS 
`scheduled_start`,`e`.`home_team_id` AS `home_team_id`,`e`.`away_team_id` AS 
`away_team_id`,NULL AS `team1_name`,NULL AS `team2_name`,`s`.`name` AS 
`selection_name`,if(`bd`.`void`,'VOID',`s`.`outcome`) AS 
`selection_outcome`,if(`bd`.`void`,'VOID',`bd`.`outcome`) AS 
`display_outcome`,NULL AS `selection_type`,`s`.`specialoddsvalue` AS 
`specialoddsvalue`,`h`.`id` AS `history_id`,`h`.`customer_id` AS 
`customer_id`,`h`.`date` AS `history_date`,`h`.`amount` AS 
`amount`,`h`.`balance` AS `balance`,`h`.`is_inplay` AS 
`is_inplay`,`h`.`transaction_type_id` AS `transaction_type_id`,`tt`.`code` AS 
`transaction_code`,`tt`.`display_name` AS `transaction`,NULL AS 
`is_outright`,`s`.`id` AS `selection_id`,`sei`.`value` AS 
`trap_number`,concat(`cp`.`first_name`,' ',`cp`.`last_name`) AS 
`customer_name`,`t_type`.`code` AS 
`trading_type_code`,coalesce(`sports`.`display_name`,`sports`.`name`) AS 
`sport_name`,`e`.`sport_id` AS `sport_id`,`ss`.`feed_sport_id` AS 
`inplay_sport_id`,`categories`.`name` AS `category_name`,`tournaments`.`name` AS
`tournament_name`,`cp`.`mobile_number` AS `mobile_number`,`ee`.`value` AS 
`feed_event_id` from ((((((((((((((((((((`customer_history` `h` join 
`customer_profile` `cp` on((`h`.`customer_id` = `cp`.`acm_user_id`))) left join 
`trading_type` `t_type` on((`cp`.`trading_type_id` = `t_type`.`id`))) join 
`transaction_types` `tt` on((`tt`.`id` = `h`.`transaction_type_id`))) join 
`bets` `b` on(((`h`.`customer_id` = `b`.`customer_id`) and 
(`h`.`transaction_reference` = `b`.`id`)))) left join `bets` `b_each_way` 
on((`b_each_way`.`win_bet_id` = `b`.`id`))) join `bet_data` `bd` on((`b`.`id` = 
`bd`.`bet_id`))) left join `bet_data_extra_info` `bdei` on(((`bd`.`id` = 
`bdei`.`bet_data_id`) and (`bdei`.`attribute` = 'place_odds_divisor')))) left 
join `bet_data_extra_info` `bdei2` on(((`bd`.`id` = `bdei2`.`bet_data_id`) and 
(`bdei2`.`attribute` = 'places_paid')))) join `bet_structure` `bs` 
on((`b`.`bet_structure_id` = `bs`.`id`))) join `books` `boo` on((`b`.`book_id` =
`boo`.`id`))) join `markets` `m` on((`bd`.`market_id` = `m`.`id`))) join 
`events` `e` on(((`bd`.`event_id` = `e`.`id`) and (`e`.`event_type_id` = 4)))) 
join `event_type` `et` on((`et`.`id` = `e`.`event_type_id`))) join `sports` 
on((`e`.`sport_id` = `sports`.`id`))) left join `supplier_sports` `ss` 
on(((`sports`.`id` = `ss`.`sport_id`) and (`ss`.`supplier_id` = 2)))) join 
`categories` on((`e`.`category_id` = `categories`.`id`))) join `tournaments` 
on((`e`.`tournament_id` = `tournaments`.`id`))) join `selections` `s` 
on((`bd`.`selection_id` = `s`.`id`))) left join `selections_extra_info` `sei` 
on(((`sei`.`selection_id` = `s`.`id`) and (`sei`.`attribute` = 'trap_number'))))
left join `events_extra_info` `ee` on(((`e`.`id` = `ee`.`event_id`) and 
(`ee`.`attribute` = 'virtual_feed_event_id')))) where 
(isnull(`h`.`transaction_failure_id`) and (`h`.`is_inplay` = 1) and 
(`bd`.`inplay` = 1) and (`et`.`code` = 'INPLAY') and (`s`.`inplay` = 1) and 
(`h`.`transaction_type_id` in (3,4)));

CREATE OR REPLACE ALGORITHM=MERGE SQL SECURITY DEFINER VIEW 
`cms_view_bets_inplay` AS select concat(`cib`.`bet_id`,'i') AS 
`id`,`cib`.`status` AS `status`,`b`.`history_id` AS `transaction_id`,NULL AS 
`ew_win_bet_id`,`cib`.`outcome` AS `outcome`,0 AS 
`is_each_way`,`cib`.`bet_structure_id` AS `bet_structure_id`,`bdei`.`value` AS 
`place_odds_divisor`,`bdei2`.`value` AS `places_paid`,`cibd`.`bet_data_id` AS 
`bet_data_id`,`cibd`.`price` AS `price`,`cibd`.`outcome` AS 
`bet_outcome`,`cib`.`stake` AS `stake`,`cib`.`total_stake` AS 
`total_stake`,`cib`.`returns` AS `returns`,`cib`.`potential_return` AS 
`potential_return`,`cib`.`free_bet_id` AS `free_bet_id`,0 AS `is_sp`,0 AS 
`deduction_factor`,`bs`.`name` AS `multibet_name`,format((`cib`.`total_stake` / 
`cib`.`stake`),0) AS `calculated_num_bets`,`cim`.`market_name` AS `market`,0 AS 
`translate`,NULL AS `market_id`,`cim`.`exchange_market_id` AS 
`exchange_market_id`,`cim`.`exchange_market_subtype` AS 
`exchange_market_subtype`,`cim`.`exchange_market_type` AS 
`exchange_market_type`,`ce`.`event_name` AS `event_name`,`ce`.`kickoff_time` AS 
`scheduled_start`,NULL AS `home_team_id`,NULL AS `away_team_id`,`ce`.`team1` AS 
`team1_name`,`ce`.`team2` AS `team2_name`,`cims`.`selection_name` AS 
`selection_name`,'' AS `selection_outcome`,`cibd`.`outcome` AS 
`display_outcome`,`cims`.`selection_type` AS 
`selection_type`,`cs`.`specialoddsvalue` AS `specialoddsvalue`,`h`.`id` AS 
`history_id`,`h`.`customer_id` AS `customer_id`,`h`.`date` AS 
`history_date`,`h`.`amount` AS `amount`,`h`.`balance` AS 
`balance`,`h`.`is_inplay` AS `is_inplay`,`h`.`transaction_type_id` AS 
`transaction_type_id`,`tt`.`code` AS `transaction_code`,`tt`.`display_name` AS 
`transaction`,0 AS `is_outright`,`cs`.`selection_id` AS `selection_id`,NULL AS 
`trap_number`,concat(`cp`.`first_name`,' ',`cp`.`last_name`) AS 
`customer_name`,`t_type`.`code` AS 
`trading_type_code`,replace(`cis`.`sport_name`,'Soccer','Football') AS 
`sport_name`,`ss`.`sport_id` AS `sport_id`,`ce`.`exchange_sport_id` AS 
`inplay_sport_id`,`cic`.`category_name` AS 
`category_name`,`cit`.`tournament_name` AS 
`tournament_name`,`cp`.`mobile_number` AS `mobile_number`,`ee`.`value` AS 
`feed_event_id` from (((((((((((((((((`customer_history` `h` join 
`customer_profile` `cp` on((`h`.`customer_id` = `cp`.`acm_user_id`))) left join 
`trading_type` `t_type` on((`cp`.`trading_type_id` = `t_type`.`id`))) join 
`transaction_types` `tt` on((`tt`.`id` = `h`.`transaction_type_id`))) join 
`core_inplay_bet` `cib` on(((`h`.`customer_id` = `cib`.`customer_id`) and 
(`h`.`transaction_reference` = `cib`.`bet_id`)))) join `core_inplay_bet_data` 
`cibd` on((`cib`.`bet_id` = `cibd`.`bet_id`))) join `bet_structure` `bs` 
on((`cib`.`bet_structure_id` = `bs`.`id`))) join `core_events` `ce` 
on((`cibd`.`event_id` = `ce`.`event_id`))) join `core_inplay_tournament` `cit` 
on((`ce`.`exchange_tournament_id` = `cit`.`exchange_tournament_id`))) join 
`core_inplay_category` `cic` on((`ce`.`exchange_category_id` = 
`cic`.`exchange_category_id`))) join `core_inplay_sport` `cis` 
on((`ce`.`exchange_sport_id` = `cis`.`exchange_sport_id`))) left join 
`supplier_sports` `ss` on(((`cis`.`exchange_sport_id` = `ss`.`feed_sport_id`) 
and (`ss`.`supplier_id` = 2)))) left join `bet_data_extra_info` `bdei` 
on(((`cib`.`bet_id` = `bdei`.`bet_data_id`) and (`bdei`.`attribute` = 
'place_odds_divisor')))) left join `bet_data_extra_info` `bdei2` 
on(((`cib`.`bet_id` = `bdei2`.`bet_data_id`) and (`bdei2`.`attribute` = 
'places_paid')))) join `core_inplay_market_selection` `cims` 
on((`cims`.`inplay_market_selection_id` = `cibd`.`inplay_market_selection_id`)))
join `core_inplay_market` `cim` on(((`cim`.`exchange_market_id` = 
`cims`.`exchange_market_id`) and (`cim`.`exchange_market_subtype` = 
`cims`.`exchange_market_subtype`)))) join `core_selection` `cs` 
on((`cs`.`selection_id` = `cibd`.`selection_id`))) join `events_extra_info` `ee`
on(((`ce`.`event_id` = `ee`.`event_id`) and (`ee`.`attribute` = 
'virtual_feed_event_id'))) left join `bets` `b` on((`b`.`id` = `cib`.`bet_id`)))
where (isnull(`h`.`transaction_failure_id`) and (`h`.`is_inplay` = 1) and 
(`h`.`transaction_type_id` in (3,4)));

CREATE OR REPLACE ALGORITHM = MERGE SQL SECURITY DEFINER VIEW 
`cms_view_bets_mixed_preplay_inplay` AS select coalesce(`b_each_way`.`id`, 
`b`.`id`) AS `id`, `b`.`status` AS `status`, `b`.`history_id` AS 
`transaction_id`, `b_each_way`.`win_bet_id` AS `ew_win_bet_id`, 
coalesce(`b_each_way`.`outcome`, `b`.`outcome`) AS `outcome`, `b`.`is_each_way` 
AS `is_each_way`, `b`.`bet_structure_id` AS `bet_structure_id`, `bd`.`id` AS 
`bet_data_id`, `bd`.`price` AS `price`, `bdei`.`value` AS `place_odds_divisor`, 
`bdei2`.`value` AS `places_paid`, if(`bd`.`void`, 'VOID', `bd`.`outcome`) AS 
`bet_outcome`, `b`.`stake` AS `stake`, `b`.`total_stake` AS `total_stake`,
(coalesce(`b`.`returns`, 0) + coalesce(`b_each_way`.`returns`, 0)) AS `returns`,
(coalesce(`b`.`potential_return`, 0) + coalesce(`b_each_way`.`potential_return`,
0)) AS `potential_return`, `b`.`free_bet_id` AS `free_bet_id`, 
`bd`.`starting_price` AS `is_sp`, `bd`.`deduction_factor` AS `deduction_factor`,
`bs`.`name` AS `multibet_name`, format((`b`.`total_stake` / `b`.`stake`), 0) AS 
`calculated_num_bets`, coalesce(`m`.`display_name`, `m`.`name`) AS `market`, 
`m`.`translate` AS `translate`, `m`.`id` AS `market_id`, NULL AS 
`exchange_market_id`, NULL AS `exchange_market_subtype`, NULL AS 
`exchange_market_type`, `e`.`name` AS `event_name`, `e`.`scheduled_start` AS 
`scheduled_start`, `e`.`home_team_id` AS `home_team_id`, `e`.`away_team_id` AS 
`away_team_id`, NULL AS `team1_name`, NULL AS `team2_name`, `s`.`name` AS 
`selection_name`, if(`bd`.`void`, 'VOID', `s`.`outcome`) AS `selection_outcome`,
if(`bd`.`void`, 'VOID', `bd`.`outcome`) AS `display_outcome`, NULL AS 
`selection_type`, `s`.`specialoddsvalue` AS `specialoddsvalue`, `h`.`id` AS 
`history_id`, `h`.`customer_id` AS `customer_id`, `h`.`date` AS `history_date`, 
`h`.`amount` AS `amount`, `h`.`balance` AS `balance`, `s`.`inplay` AS 
`is_inplay`, `h`.`transaction_type_id` AS `transaction_type_id`, `tt`.`code` AS 
`transaction_code`, `tt`.`display_name` AS `transaction`, 
if(isnull(`o`.`event_id`), 0, 1) AS `is_outright`, `s`.`id` AS `selection_id`, 
`sei`.`value` AS `trap_number`, concat(`cp`.`first_name`, ' ', `cp`.`last_name`)
AS `customer_name`, `t_type`.`code` AS `trading_type_code`, 
coalesce(`sports`.`display_name`, `sports`.`name`) AS `sport_name`, 
`e`.`sport_id` AS `sport_id`, `ss`.`feed_sport_id` AS `inplay_sport_id`, 
`categories`.`name` AS `category_name`, `tournaments`.`name` AS 
`tournament_name`, `cp`.`mobile_number` AS `mobile_number`, `ee`.`value` AS 
`feed_event_id` from ((((((((((((((((((((`customer_history` `h` join 
`customer_profile` `cp` ON ((`h`.`customer_id` = `cp`.`acm_user_id`))) left join
`trading_type` `t_type` ON ((`cp`.`trading_type_id` = `t_type`.`id`))) join 
`transaction_types` `tt` ON ((`tt`.`id` = `h`.`transaction_type_id`))) join 
`bets` `b` ON (((`h`.`customer_id` = `b`.`customer_id`) and 
(`h`.`transaction_reference` = `b`.`id`)))) left join `bets` `b_each_way` ON 
((`b_each_way`.`win_bet_id` = `b`.`id`))) join `bet_data` `bd` ON ((`b`.`id` = 
`bd`.`bet_id`))) left join `bet_data_extra_info` `bdei` ON (((`bd`.`id` = 
`bdei`.`bet_data_id`) and (`bdei`.`attribute` = 'place_odds_divisor')))) left 
join `bet_data_extra_info` `bdei2` ON (((`bd`.`id` = `bdei2`.`bet_data_id`) and 
(`bdei2`.`attribute` = 'places_paid')))) join `bet_structure` `bs` ON 
((`b`.`bet_structure_id` = `bs`.`id`))) join `books` `boo` ON ((`b`.`book_id` = 
`boo`.`id`))) join `markets` `m` ON ((`bd`.`market_id` = `m`.`id`))) join 
`events` `e` ON (((`bd`.`event_id` = `e`.`id`)))) join `sports` ON 
((`e`.`sport_id` = `sports`.`id`))) left join `supplier_sports` `ss` ON 
(((`sports`.`id` = `ss`.`sport_id`) and (`ss`.`supplier_id` = 2)))) join 
`categories` ON ((`e`.`category_id` = `categories`.`id`))) join `tournaments` ON
((`e`.`tournament_id` = `tournaments`.`id`))) join `selections` `s` ON 
((`bd`.`selection_id` = `s`.`id`))) left join `selections_extra_info` `sei` ON 
(((`sei`.`selection_id` = `s`.`id`) and (`sei`.`attribute` = 'trap_number')))) 
left join `events_extra_info` `ee` ON (((`e`.`id` = `ee`.`event_id`) and 
(`ee`.`attribute` = 'virtual_feed_event_id')))) left join `outrights` `o` ON 
((`e`.`id` = `o`.`event_id`))) where (isnull(`h`.`transaction_failure_id`) and 
(`h`.`transaction_type_id` in (3 , 4)));