SELECT

deals.id as deal_id,
payments.id as payment_id,
deals.type,
products.name as product,
CONCAT_WS(',', countries.`name`) as 'GEO',
teams.name as 'department',
DATE(payments.created_at),
payments.usd_summary,
CONCAT_WS(',', affiliate_sites.`name`) as 'ad_url',
NULL as 'ad_places',
NULL as 'results',
wallet_types.name as payment_method,
'test',
'5 - Реклама' as 'cost_item',
payments.comment as 'about',
CONCAT_WS(',', affiliate_sites.`foreign_id`) as 'site_ids',
'CRM' as 'source',
payment_targets.name as payment_target

FROM payments

LEFT JOIN deals ON deals.id = payments.deal_id
LEFT JOIN deal_places ON deals.id = deal_places.deal_id
LEFT JOIN departments ON deals.department_id = departments.id
LEFT JOIN partners ON partners.id = deals.partner_id
LEFT JOIN products ON products.id = partners.product_id
LEFT JOIN team_users ON team_users.user_id = deals.manager_id
LEFT JOIN teams ON teams.id = team_users.team_id
LEFT JOIN countries ON deal_places.country_id = countries.id
LEFT JOIN affiliate_sites ON affiliate_sites.id = deal_places.site_id
LEFT JOIN wallets ON wallets.id = payments.wallet_id
LEFT JOIN wallet_types ON wallet_types.id = wallets.wallet_type_id
LEFT JOIN payment_targets ON payment_targets.id = payments.payment_target_id

WHERE payments.created_at BETWEEN '2020-03-01 00:00:00.000000' AND '2020-03-31 23:59:59.000000' AND payments.status = 'done' AND deals.type != 'google_docs' AND deals.type != 'zid_cid' GROUP BY payments.id

UNION

SELECT

deals.id as deal_id,
payments.id as payment_id,
deals.type,
products.name as product,
CONCAT_WS(',', countries.`name`) as 'GEO',
teams.name as 'department',
DATE(payments.created_at),
payments.usd_summary,
CONCAT_WS(',', deal_buying_stats.`link`) as 'ad_url',
NULL as 'ad_places',
NULL as 'results',
wallet_types.name as payment_method,
'test',
'5 - Реклама' as 'cost_item',
payments.comment as 'about',
CONCAT_WS(',', affiliate_sites.`foreign_id`) as 'site_ids',
'CRM' as 'source',
payment_targets.name as payment_target

FROM payments

LEFT JOIN deals ON deals.id = payments.deal_id
LEFT JOIN deal_places ON deals.id = deal_places.deal_id
LEFT JOIN departments ON deals.department_id = departments.id
LEFT JOIN partners ON partners.id = deals.partner_id
LEFT JOIN products ON products.id = partners.product_id
LEFT JOIN team_users ON team_users.user_id = deals.manager_id
LEFT JOIN teams ON teams.id = team_users.team_id
LEFT JOIN countries ON deal_places.country_id = countries.id
LEFT JOIN zid ON zid.deal_id = deals.id
LEFT JOIN affiliate_sites ON affiliate_sites.id = zid.site_id
LEFT JOIN wallets ON wallets.id = payments.wallet_id
LEFT JOIN wallet_types ON wallet_types.id = wallets.wallet_type_id
LEFT JOIN payment_targets ON payment_targets.id = payments.payment_target_id
LEFT JOIN deal_buying_stats ON deal_buying_stats.deal_id = payments.deal_id

WHERE payments.created_at BETWEEN '2020-03-01 00:00:00.000000' AND '2020-03-31 23:59:59.000000' AND payments.status = 'done' AND deals.type = 'zid_cid' GROUP BY payments.id

UNION

SELECT

deals.id as deal_id,
payments.id as payment_id,
deals.type,
products.name as product,
deals_tmp.geo,
teams.name as 'department',
DATE(payments.created_at),
payments.usd_summary,
deals_tmp.ad_url,
deals_tmp.ad_places,
deals_tmp.results,
deals_tmp.wallet_type as payment_method,
deals_tmp.extention,
deals_tmp.cost_item,
deals_tmp.about,
CONCAT_WS(',', deal_places.`site_id`) as 'site_ids',
'GOOGLE DOCS' as 'source',
payment_targets.name as payment_target

FROM payments

LEFT JOIN deals ON deals.id = payments.deal_id
LEFT JOIN deals_tmp ON deals.id = deals_tmp.deal_id
LEFT JOIN deal_places ON deals.id = deal_places.deal_id
LEFT JOIN team_users ON team_users.user_id = deals.manager_id
LEFT JOIN teams ON teams.id = team_users.team_id
LEFT JOIN partners ON partners.id = deals.partner_id
LEFT JOIN products ON products.id = partners.product_id
LEFT JOIN payment_targets ON payment_targets.id = payments.payment_target_id

WHERE payments.created_at BETWEEN '2020-03-01 00:00:00.000000' AND '2020-03-31 23:59:59.000000' AND payments.status = 'done' AND deals.type = 'google_docs' GROUP BY payments.id