3w | Groupe Milliet

USE [GESTCOM_BBCM]
GO

begin transaction

delete from CAMBOS_BBCM..DASHBOARD_LIVRAISON_TMP

;

with o as (
select
cast(ORDDELIVERYDATE as date) _DATE_O,
count(*) COMMANDES,
sum(case ORDINVOICED when 0 then 1 else 0 end) A_FACTURER
-- nb clients diffÈrents
-- nb colis total
-- nb colis par univers
-- nb produits diffÈrents
-- nb tournÈes
from ORDERS
where 1 = 1
and ORDNOSOCAUX = 1
and ORDTYPE = N'O'
--and coalesce(ORDCRITERIA1, N'') is null
and ORDNOCUSTOMER not in (select CUSNOCUSTOMER from CUSTOMER where CUSCATEGORYCODE = N'Z')
--and ORDBALANCE > 0
and ORDSTATUS != 2
and ORDNOORDER not in (select ORDNOORDER from ORDERS inner join DELIVERYORDER on ORDNOORDER = DEONOORDER where DEOORDRETOURNEE = N'37')
group by cast(ORDDELIVERYDATE as date)
), c as (
select
_DATE_C,
count(*) CLIENTS
from (
select
cast(ORDDELIVERYDATE as date) _DATE_C,
ORDNOCUSTOMER,
null filler
from ORDERS
where 1 = 1
and ORDNOSOCAUX = 1
and ORDTYPE = N'O'
--and nullif(ORDCRITERIA1, N'') is null
and ORDNOCUSTOMER not in (select CUSNOCUSTOMER from CUSTOMER where CUSCATEGORYCODE = N'Z')
--and ORDBALANCE > 0
and ORDSTATUS != 2
and ORDINVOICED = 0
and ORDNOORDER not in (select ORDNOORDER from ORDERS inner join DELIVERYORDER on ORDNOORDER = DEONOORDER where DEOORDRETOURNEE = N'37')
group by cast(ORDDELIVERYDATE as date), ORDNOCUSTOMER
) c
group by _DATE_C
), t as (
select
_DATE_T,
count(*) TOURNEES
from (
select
cast(ORDDELIVERYDATE as date) _DATE_T,
DEOORDRETOURNEE,
null filler
from ORDERS
inner join DELIVERYORDER
on ORDNOORDER = DEONOORDER
where 1 = 1
and ORDNOSOCAUX = 1
and ORDTYPE = N'O'
--and nullif(ORDCRITERIA1, N'') is null
and ORDNOCUSTOMER not in (select CUSNOCUSTOMER from CUSTOMER where CUSCATEGORYCODE = N'Z')
--and ORDBALANCE > 0
and ORDSTATUS != 2
and ORDINVOICED = 0
and ORDNOORDER not in (select ORDNOORDER from ORDERS inner join DELIVERYORDER on ORDNOORDER = DEONOORDER where DEOORDRETOURNEE = N'37')
group by cast(ORDDELIVERYDATE as date), DEOORDRETOURNEE
) t
group by _DATE_T
), olc as (
select
cast(ORDDELIVERYDATE as date) _DATE_OLC,
cast(sum(ODLQTEPACKAGE2) as int)
- (5 * (sum(case when PARUPPERFAMILYCODE = N'SPI' then cast(ODLQTEPACKAGE2 as int) else 0 end) + 5) / 6)
COLIS,
sum(case when PARUPPERFAMILYCODE = N'BIE' then cast(ODLQTEPACKAGE2 as int) else 0 end) [BIE],
sum(case when PARUPPERFAMILYCODE = N'BIF' then cast(ODLQTEPACKAGE2 as int) else 0 end) [BIF],
sum(case when PARUPPERFAMILYCODE = N'EAU' then cast(ODLQTEPACKAGE2 as int) else 0 end) [EAU],
sum(case when PARUPPERFAMILYCODE = N'JUS' then cast(ODLQTEPACKAGE2 as int) else 0 end) [JUS],
sum(case when PARUPPERFAMILYCODE = N'SOD' then cast(ODLQTEPACKAGE2 as int) else 0 end) [SOD],
(sum(case when PARUPPERFAMILYCODE = N'SPI' then cast(ODLQTEPACKAGE2 as int) else 0 end) + 5) / 6 [SPI],
sum(case when PARUPPERFAMILYCODE = N'VIN' then cast(ODLQTEPACKAGE2 as int) else 0 end) [VIN],
sum(case when PARUPPERFAMILYCODE not in (N'BIE', N'BIF', N'EAU', N'JUS', N'SOD', N'SPI', N'VIN') then cast(ODLQTEPACKAGE2 as int) else 0 end) [XXX],
null filler
from ORDERS
inner join ORDERLINE
on ORDNOORDER = ODLNOORDER
inner join ARTICLE
on ODLNOARTICLE = ARTNOARTICLE
inner join ARTFAMILY
on ARTFAMILYCODE = PARCODEPARAM
where 1 = 1
and ORDNOSOCAUX = 1
and ORDTYPE = N'O'
--and nullif(ORDCRITERIA1, N'') is null
and ORDNOCUSTOMER not in (select CUSNOCUSTOMER from CUSTOMER where CUSCATEGORYCODE = N'Z')
--and ORDBALANCE > 0
and ORDSTATUS != 2
and ORDINVOICED = 0
and ORDNOORDER not in (select ORDNOORDER from ORDERS inner join DELIVERYORDER on ORDNOORDER = DEONOORDER where DEOORDRETOURNEE = N'37')
and ODLQUANTITYORDER <> 0
and ODLNOARTICLE not in (9995, 9996, 9997, 9998, 9999)
group by cast(ORDDELIVERYDATE as date)
), olp as (
select
_DATE_OLP,
count(*) PRODUITS
from (
select
cast(ORDDELIVERYDATE as date) _DATE_OLP,
ODLNOARTICLE,
null filler
from ORDERS
inner join ORDERLINE
on ORDNOORDER = ODLNOORDER
where 1 = 1
and ORDNOSOCAUX = 1
and ORDTYPE = N'O'
--and nullif(ORDCRITERIA1, N'') is null
and ORDNOCUSTOMER not in (select CUSNOCUSTOMER from CUSTOMER where CUSCATEGORYCODE = N'Z')
--and ORDBALANCE > 0
and ORDSTATUS != 2
and ORDINVOICED = 0
and ORDNOORDER not in (select ORDNOORDER from ORDERS inner join DELIVERYORDER on ORDNOORDER = DEONOORDER where DEOORDRETOURNEE = N'37')
and ODLQUANTITYORDER <> 0
and ODLNOARTICLE not in (9995, 9996, 9997, 9998, 9999)
group by cast(ORDDELIVERYDATE as date), ODLNOARTICLE
) olp
group by _DATE_OLP
)

insert into CAMBOS_BBCM..DASHBOARD_LIVRAISON_TMP
(
DATE_LIVRAISON,
COMMANDES,
CLIENTS,
COLIS,
TOURNEES,
PRODUITS,
[BIE],
[BIF],
[EAU],
[JUS],
[SOD],
[SPI],
[VIN],
[XXX]
)

select
_DATE_O DATE_LIVRAISON,
A_FACTURER COMMANDES,
CLIENTS,
COLIS,
TOURNEES,
PRODUITS,
[BIE],
[BIF],
[EAU],
[JUS],
[SOD],
[SPI],
[VIN],
[XXX]
--, null filler
from o
left outer join c
on o._DATE_O = c._DATE_C
left outer join t
on o._DATE_O = t._DATE_T
left outer join olc
on o._DATE_O = olc._DATE_OLC
left outer join olp
on o._DATE_O = olp._DATE_OLP

where 1 = 1
and cast(o._DATE_O as date) between convert(date, getdate(), 126) and convert(date, getdate() + 28, 126)
--and cast(o._DATE_O as date) between convert(date, N'2020-02-01', 126) and convert(date, N'2020-02-28', 126)

--order by _DATE_O

;

--rollback
commit




begin transaction

delete from CAMBOS_BBCM..DASHBOARD_LIVRAISON

;

insert into CAMBOS_BBCM..DASHBOARD_LIVRAISON
select * from CAMBOS_BBCM..DASHBOARD_LIVRAISON_TMP
where DATE_LIVRAISON > cast(getdate() as date)



--rollback
commit