Untitled

declare @date_start date = '2023-07-01';
declare @date_end date = '2023-07-27'; 


with cte_payment_way as (
select
	distinct eoth.JOURNALID
	, first_value(eoth.NEWVALUE) over (partition by eoth.journalid order by eoth.CREATEDDATETIME asc) as paymenttype_first

from
	[StagingArea].[S088].[ESHOPORDERTABLEHISTORY] eoth

	join
	[StagingArea].[S088].[TSM_ESHOPORDERTABLE] eot
	on eoth.JOURNALID = eot.JOURNALID

where
	eoth.FIELDLABEL = 'Способ оплаты'
and
	eot.PAYMENTTYPEID = 0 -- смешанная оплата
)


, cte_orders as (
select
	eot.ORDERID

	, year(cast(dateadd(hh, +3, eot.ORDERDATETIME) as date)) as year
 
	, month(cast(dateadd(hh, +3, eot.ORDERDATETIME) as date)) as month

	, cast(dateadd(hh, +3, eot.ORDERDATETIME) as date) as date

	, eot.DISCCARDHOLDERID as holderid

	, case
		when DISCCARDTYPEID like 'Black%' then 'Black'
		when DISCCARDTYPEID like 'Orange%' then 'Orange'
		when DISCCARDTYPEID like 'White%' then 'White'
		else 'No loyal'
	end as client_type

    , eof.ISVIPONLINEORDER as is_vip

	, cast(eot.AMOUNTGROSS as numeric) as gross
	, cast(eot.AMOUNTAPPROVED as numeric) as approved
	, cast(eot.AMOUNTNET as numeric) as net

	, case
		when eot.AMOUNTGROSS > 0
		then 1
		else 0
	end as gross_order
	, case
		when eot.AMOUNTAPPROVED > 0
		then 1
		else 0
	end as approved_order
	, case
		when eot.AMOUNTNET > 0
		then 1
		else 0
	end as net_order

	, case 
		when eot.PAYMENTTYPEID in (1, 7)  then 'Post-payment'
		when eot.PAYMENTTYPEID in (8, 9, 11) then 'Pre-payment'
		when eot.PAYMENTTYPEID = 0 and pw.paymenttype_first in (1, 7) then 'Post-payment'
		when eot.PAYMENTTYPEID = 0 and pw.paymenttype_first in (8, 9, 11) then 'Pre-payment'
		else 'None'
	end as payment_way

	, case 
		when eot.ORDERSOURCE in ('5') then 'iOS App'
		when eot.ORDERSOURCE in ('10') then 'Android App'
		when eot.ORDERSOURCE in ('1') then 'Website'
		when eot.ORDERSOURCE in ('3') then 'CC Chat'
		when eot.ORDERSOURCE in ('2', '4', '6', '7', '11', '12','13') then 'CC Phone'
		when eot.ORDERSOURCE in ('9') then 'Mobile Assistant'
	end as platform

	, case
		when TXT in (
		'Барвиха - Самовывоз',
		'ДЛТ - заказ из торгового зала',
		'ДЛТ - Самовывоз',
		'Екатеринбург - Самовывоз (DPD)',
		'Екатеринбург - Самовывоз (Pony)',
		'Казань - Самовывоз (DPD)',
		'Казань - Самовывоз (Pony)',
		'Краснодар - Самовывоз (DPD)',
		'Краснодар - Самовывоз (Pony)',
		'Кутузовский - Самовывоз',
		'Ленинградский - Самовывоз (Алкон)',
		'Ростов-на-Дону - Самовывоз (DPD)',
		'Ростов-на-Дону - Самовывоз (Pony)',
		'Самара - Самовывоз (DPD)',
		'Самара - Самовывоз (Pony)',
		'Сочи - Самовывоз (DPD)',
		'Сочи - Самовывоз (Pony)',
		'ЦУМ - заказ из торгового зала',
		'ЦУМ - Самовывоз',
		'ЦУМ - VIP 4 ПВЗ',
		'Шуваловский - Самовывоз'
		) then 'ПВЗ'

		when TXT in (
		'Екатеринбург - ССД (Pony)',
		'СПБ ЦУМ - собствен.курьеры',
		'Казань - ССД (DPD)',
		'Екатеринбург - ССД (DPD)',
		'Краснодар - ССД (DPD)',
		'ЦУМ - ССД (Претензия)',
		'Ростов-на-Дону - ССД (DPD)',
		'Новосибирск - ССД (DPD)',
		'ДЛТ - Экспресс',
		'VIP доставка СПб',
		'VIP доставка МСК',
		'Сочи - ССД (Pony)',
		'Самара - ССД (Pony)',
		'Самара - ССД (DPD)',
		'Санкт-Петербург ССД Экспресс',
		'Краснодар - ССД (Pony)',
		'Сочи – ССД (DPD)',
		'Казань - ССД (Pony)',
		'Ростов-на-Дону - ССД (Pony)',
		'Новосибирск - ССД (PONY)',
		'ЦУМ - ССД',
		'ЦУМ - Экспресс',
		'Москва ССД',
		'VIP Москва ССД',
		'VIP Санкт-Петербург ССД',
		'Москва ССД Экспресс',
		'Санкт-Петербург ССД',
		'Москва ССД (Претензия)'
		) then 'ССД'

		when TXT in (
		'Далли Сервис - дальняя МО (до 100 км)',
		'Далли Сервис - Москва и ближняя МО (до 30 км)',
		'КСЭ VIP',
		'TopDelivery - Москва и ближняя МО (до 30км)',
		'Top Delivery РФ',
		'Pony Express',
		'Pony Армения RUS',
		'DPD Казахстан KAZ',
		'DPD Казахстан RUS',
		'EMS - до 300 000 руб.',
		'Pony Армения ARM',
		'DPD',
		'DPD авиа',
		'DPD Беларусь'
		) then 'Агенты'

		when TXT = 'Без доставки' then 'Без доставки'
		
		when TXT = 'Свободный адрес' then 'Свободный адрес'

		else TXT

	end as delivery_method

from
	[StagingArea].[S088].[TSM_ESHOPORDERTABLE] eot

	left join
	cte_payment_way pw
	on eot.JOURNALID = pw.JOURNALID

	left join
	[StagingArea].[S088].[DLVMODE] dm
	on eot.DLVMODEID = dm.CODE

	left join
    [StagingArea].[S088].[ESHOPORDERFEATURE] eof
    on eot.ORDERID = eof.ORDERID
	
where
	eot.DATAAREAID = 'tsm'
and
	cast(dateadd(hh, +3, eot.ORDERDATETIME) as date) >= @date_start
and
	cast(dateadd(hh, +3, eot.ORDERDATETIME) as date) < @date_end
and
	eot.REJECTREASON != 'ИМ19'
and
	eot.AMOUNTGROSS > 0
)


select
	date
	, delivery_method
	, count(distinct holderid) as holderid
	, sum(gross) as gross
	, sum(approved) as approved
	, sum(net) as net
	, sum(gross_order) as gross_order
	, sum(approved_order) as approved_order
	, sum(net_order) as net_order

from
	cte_orders

group by
	date
	, delivery_method