Delen

SQL | Bedrijfsmonitor (Pettrie)

-- 
-- ============== Real querys ==================
-- Voertuigen verkocht afgelopen 3 maanden
with
range_values AS (
  SELECT date_part('week', $endDate - interval '3 months') as minval,
         date_part('week', $endDate) as maxval),
week_range AS (
  SELECT generate_series(minval::int, maxval::int) as week
  FROM range_values
),
weekly_counts AS (
  SELECT date_part('week', call_date) as weekly,
         count(*)
  FROM autoflex_cloud.vehicle_check_regulations vcr
	where
		vcr.organization_id in $organizations
	and 
		vcr.is_signed_off = 1 
	and
		vcr.is_archived is null or vcr.is_archived = 0
	and
		vcr.call_date is not null
	and
		vcr.call_date::date between $endDate - INTERVAL '3 months' and $endDate
  GROUP BY 1
)
SELECT week_range.week as "label",
       weekly_counts.count as "value"
FROM week_range
LEFT OUTER JOIN weekly_counts on week_range.week = weekly_counts.weekly
order by week_range.week


-- ============== Real querys ==================
-- Morgen in werkplaats
-- todayInShop - getOrdersInShop(false)
with 
meta as (
	select avg(range.count)::decimal as avg, max(range.count) as max
	--select range.count, range.date
	from (
	select count(*) as "count", o.planning_start_date::date as "date"
	from autoflex_cloud.orders o 
	where 
		o.organization_id in $organizations
	and
		(o.is_invoiced is null or o.is_invoiced = 0) 
	and
		(o.is_archived is null or o.is_archived = 0)
	and 
		o.order_type = 1
	and
		o.planning_start_date >= CURRENT_DATE - 60
	group by o.planning_start_date::date
	order by o.planning_start_date::date desc
	) as "range"
)
select 
	count(planning_start_date ) as "value"
	, ARRAY[0,(select round(max*1.1 + 0.5,0) from meta)] as "range"
	, ARRAY[(select round(avg,1) from meta), (select round(avg*1.4 ,1) from meta)] as "delimeters"
	--, (select round(avg*1.1 ,2) from meta) as avg
	--, (select round(max*1.1 ,1) from meta) as max
from autoflex_cloud.orders o 
	where 
		o.organization_id in $organizations
	and
		(o.is_invoiced is null or o.is_invoiced = 0) 
	and
		(o.is_archived is null or o.is_archived = 0)
	and 
		o.order_type = 1
	and
		(o.planning_start_date <= (current_date + time '23:59:59') and o.planning_ready_date >= (current_date + time '00:00:00'))



		
		-- ============== Real querys ==================
-- Morgen in werkplaats
-- todayInShop - getOrdersInShop(false)
with 
meta as (
	select avg(range.count)::decimal as avg, max(range.count) as max
	--select range.count, range.date
	from (
	select count(*) as "count", o.planning_start_date::date as "date"
	from autoflex_cloud.orders o 
	where 
		o.organization_id in $organizations
	and
		(o.is_invoiced is null or o.is_invoiced = 0) 
	and
		(o.is_archived is null or o.is_archived = 0)
	and 
		o.order_type = 1
	and
		o.planning_start_date >= CURRENT_DATE - 60
	group by o.planning_start_date::date
	order by o.planning_start_date::date desc
	) as "range"
)
select 
	'Werkorder' as "label"
	, count(planning_start_date ) as "value"
	, ARRAY[0,(select round(max*1.1 + 0.5,0) from meta)] as "range"
	, ARRAY[(select round(avg,1) from meta), (select round(avg*1.4 ,1) from meta)] as "delimeters"
	, ARRAY['Datum','Werkorders'] as "series"
	--, (select round(avg*1.1 ,2) from meta) as avg
	--, (select round(max*1.1 ,1) from meta) as max
from autoflex_cloud.orders o 
	where 
		o.organization_id in $organizations
	and
		(o.is_invoiced is null or o.is_invoiced = 0) 
	and
		(o.is_archived is null or o.is_archived = 0)
	and 
		o.order_type = 1
	and
		o.planning_start_date::date = current_date+1
		
		
	with
-- ophalen id voor werkplaats offerte
"status" as (
select
	os.order_status_id
from
	autoflex_cloud.order_statuses os
where
	os.status_description = 'Offerte'
	and os.order_type = 1)
,
"meta" as (
select
	avg(range.count)::decimal as avg,
	max(range.count) as max
	--select range.count, range.date
from
	(
	select
		count(*) as "count",
		o.planning_start_date::date as "date"
	from
		autoflex_cloud.orders o
	where 
		o.organization_id in $organizations
		and
		(o.is_invoiced is null
			or o.is_invoiced = 0)
		and
		(o.is_archived is null
			or o.is_archived = 0)
		and 
		o.order_type = 1
		and
		o.planning_start_date >= CURRENT_DATE - 60
	group by
		o.planning_start_date::date
	order by
		o.planning_start_date::date desc
	) as "range"
)	
select
	count(*) value
	,
	array[0,
	(
	select
		round(max * 1.1 + 0.5,
		0)
	from
		meta)] as "range"
	,
	array[(
	select
		round(avg,
		1)
	from
		meta),
	(
	select
		round(avg * 1.4 ,
		1)
	from
		meta)] as "delimeters"
from
	autoflex_cloud.orders o
where 
		o.organization_id in $organizations
	and
		(o.is_archived is null
		or o.is_archived = 0)
	and
		(o.is_invoiced is null
		or o.is_invoiced = 0)
	and 
		o.order_type = 1
	and o.order_status_id <> (
	select
		*
	from
		status)
		
		
		
with
-- ophalen id voor werkplaats offerte
"status" as (
select
	os.order_status_id
from
	autoflex_cloud.order_statuses os
where
	os.status_description = 'Offerte'
	and os.order_type = 1)
,
"meta" as (
select
	avg(range.count)::decimal as avg,
	max(range.count) as max
	--select range.count, range.date
from
	(
	select
		count(*) as "count",
		o.planning_start_date::date as "date"
	from
		autoflex_cloud.orders o
	where 
		o.organization_id in ('242F127A-7D14-4653-98E5-D5F8E919DA11')
		and
		(o.is_invoiced is null
			or o.is_invoiced = 0)
		and
		(o.is_archived is null
			or o.is_archived = 0)
		and 
		o.order_type = 1
		and
		o.planning_start_date >= CURRENT_DATE - 60
	group by
		o.planning_start_date::date
	order by
		o.planning_start_date::date desc
	) as "range"
)	
select
	count(*) value
	,
	array[0,
	(
	select
		round(max * 1.1 + 0.5,
		0)
	from
		meta)] as "range"
	,
	array[(
	select
		round(avg,
		1)
	from
		meta),
	(
	select
		round(avg * 1.4 ,
		1)
	from
		meta)] as "delimeters"
from
	autoflex_cloud.orders o
where 
		o.organization_id in ('242F127A-7D14-4653-98E5-D5F8E919DA11')
	and
		(o.is_archived is null
		or o.is_archived = 0)
	and
		(o.is_invoiced is null
		or o.is_invoiced = 0)
	and 
		o.order_type = 1
	and o.order_status_id <> (
	select
		*
	from
		status)
		
		
		
select
	o.order_number 
	,planning_start_date
	, v.license_plate 
	, v.v_display_name 
	, o.description 
	, c.v_display_name
	--, o.order_status_id 
	, os.status_description 
from
	orders o
left join vehicles v on v.vehicle_id = o.vehicle_id 
left join contacts c on c.contact_id = o.contact_id 
left join order_statuses os on os.order_status_id =o.order_status_id 
where
	(o.is_invoiced is null
		or o.is_invoiced = 0)
	and (o.is_archived is null
		or o.is_archived = 0)
	and o.order_type = 1
	and (o.is_ci_printed = 0
		or o.is_ci_printed is null)
	and (o.is_collect = 0
		or o.is_collect is null)
	and (o.planning_start_date <= '2024-02-09'
		and o.planning_ready_date >= '2024-02-05')
	and o.organization_id = '242F127A-7D14-4653-98E5-D5F8E919DA11'
order by
	o.planning_start_date desc
		
	
	
/*
 * APK
 */	
WITH range_values AS (
  SELECT date_part('week', $endDate - interval '3 months') as minval,
         date_part('week', $endDate) as maxval),
week_range AS (
  SELECT generate_series(minval::int, maxval::int) as week
  FROM range_values
),
weekly_counts AS (
  SELECT date_part('week', call_date) as weekly,
         count(*)
  FROM autoflex_cloud.vehicle_check_regulations vcr
	where
		vcr.organization_id in $organizations
	and 
		vcr.is_signed_off = 1 
	and
		vcr.is_archived is null or vcr.is_archived = 0
	and
		vcr.call_date is not null
	and
		vcr.call_date::date between $endDate - INTERVAL '3 months' and $endDate
  GROUP BY 1
)
SELECT week_range.week as label,
       weekly_counts.count
FROM week_range
LEFT OUTER JOIN weekly_counts on week_range.week = weekly_counts.weekly
order by week_range.week


-- ============== Real querys ==================
-- APK's nog te doen
WITH 
"meta" as (
select
	avg(range.count)::decimal as avg,
	max(range.count) as max
	--select range.count, range.date
from
	(
	select
		count(*) as "count",
		vcr.call_date::date as "date"
	from
		autoflex_cloud.vehicle_check_regulations vcr
	where 
		vcr.organization_id in $organizations
	and 
		vcr.is_signed_off = 1
	and
		vcr.is_archived is null or vcr.is_archived = 0
	and
		vcr.call_date is not null
	and
		vcr.call_date::date >= CURRENT_DATE - 60
	group by
		vcr.call_date::date
	order by
		vcr.call_date::date desc
	) as "range"
)
select 
	count(call_date) as "value"
	,
	array[0,
	(
	select
		round(max * 1.1 + 0.5,
		0)
	from
		meta)] as "range"
	,
	array[(
	select
		round((round(avg,
		1) * 100) / round(max * 1.1 + 0.5,
		0),0)
	from
		meta),
	(
	select
		round((round(avg * 1.4,
		1) * 100) / round(max * 1.1 + 0.5,
		0),0)
	from
		meta),
	(
	select
		round((round(max,
		1) * 100) / round(max * 1.1 + 0.5,
		0),0)
	from
		meta)		
		] as "delimiters"
	--, (select round(avg*1.1 ,2) from meta) as avg
	--, (select round(max*1.1 + 0.5 ,1) from meta) as max
from
	autoflex_cloud.vehicle_check_regulations vcr
where 
		vcr.organization_id in $organizations
	and 
		vcr.is_signed_off is null
	and
		vcr.is_archived is null or vcr.is_archived = 0
	and
		vcr.call_date is not null
	and
		vcr.call_date::date between date_trunc('week',
	CURRENT_DATE) and (date_trunc('week',
	CURRENT_DATE) + '6 days'::interval)
	
	
	
-- ============== Real querys ==================
-- Status open orders
	
	
-- ============== Real querys ==================
-- Openstaande orders (gauge)
-- Orders werkplaats niet offerte, niet gearchiveerd, niet gefactureerd	
with
-- ophalen id voor werkplaats offerte
"status" as (
select
	os.order_status_id
from
	autoflex_cloud.order_statuses os
where
	os.status_description = 'Offerte'
	and os.order_type = 1)
,
"meta" as (
select
	avg(range.count)::decimal as avg,
	max(range.count) as max
	--select range.count, range.date
from
	(
	select
		count(*) as "count",
		o.planning_start_date::date as "date"
	from
		autoflex_cloud.orders o
	where 
		o.organization_id in $organizations
		and
		(o.is_invoiced is null
			or o.is_invoiced = 0)
		and
		(o.is_archived is null
			or o.is_archived = 0)
		and 
		o.order_type = 1
		and
		o.planning_start_date >= CURRENT_DATE - 60
	group by
		o.planning_start_date::date
	order by
		o.planning_start_date::date desc
	) as "range"
)	
select
	count(*) value
	,
	array[0,
	(
	select
		round(max * 1.1 + 0.5,
		0)
	from
		meta)] as "range"
	,
	array[(
	select
		round(avg,
		1)
	from
		meta),
	(
	select
		round(avg * 1.4 ,
		1)
	from
		meta)] as "delimiters"
from
	autoflex_cloud.orders o
where 
		o.organization_id in $organizations
	and
		(o.is_archived is null
		or o.is_archived = 0)
	and
		(o.is_invoiced is null
		or o.is_invoiced = 0)
	and 
		o.order_type = 1
	and o.order_status_id <> (
	select
		*
	from
		status)

		
-- ============== Real querys ==================
-- Openstaande orders by status on a donut
with
-- ophalen id voor werkplaats offerte
"status" as (
select
	os.order_status_id, os.status_description, os.column_number, *
from
	autoflex_cloud.order_statuses os
order by 
	os.column_number
)	
select status_description as "label"
	--,status.column_number
	, count(*) as "value"
from
	autoflex_cloud.orders o
LEFT OUTER JOIN status on status.order_status_id = o.order_status_id
where 
		o.organization_id in $organizations
	and
		(o.is_archived is null
		or o.is_archived = 0)
	and
		(o.is_invoiced is null
		or o.is_invoiced = 0)
	and 
		o.order_type = 1
group by status_description, status.column_number
order by status.column_number
		
	
-- ============== Real querys ==================
-- Openstaande orders by status on a donut
select to_char(date(delivery_date), 'Day') as label, count(*) as value from autoflex_cloud.orders o 
	where 
		o.organization_id in $organizations
	and
		o.order_status_id = (select order_status_id from autoflex_cloud.order_statuses os where os.status_description = 'Afgeleverd' and os.order_type = 2)
	group by to_char(date(delivery_date), 'Day'), extract(isodow from date(o.delivery_date))
	order by extract(isodow from date(o.delivery_date))
	
	
-- ============== Real querys ==================
-- Onderdelen in bestelling (Top 50)
select
	o.order_id, o.order_number as "bi.orderNumber"
	,(select v.license_plate
	from autoflex_cloud.vehicles v
	where v.vehicle_id = o.vehicle_id) as "bi.licensePlate"
	, ol.article_number as "Onderdeelnr."
	, ol.description as "bi.description"
	, ol.delivery_date as "Verwacht"
from order_lines ol
join autoflex_cloud.orders o on o.order_id = ol.order_id 
where
	ol.organization_id in $organizations
and 
	ol.is_ordered = 1
and
	ol.description is not null
-- only active orders
and
	(o.is_archived is null
	or o.is_archived = 0)
and
	(o.is_invoiced is null
	or o.is_invoiced = 0)
and 
	o.order_type = 1
order by o.order_date desc, ol.delivery_date asc
limit 50


-- ============== Real querys ==================
-- Insepctie orders (max 50)
select
	o.order_id, o.order_number as "bi.orderNumber"
	, o.order_date as "Order datum"
	-- select contacts, fullname
	,(select c.v_display_name 
	from autoflex_cloud.contacts c 
	where c.contact_id = o.contact_id) as "bi.contact"
	-- select vehicle, licenseplage
	,(select v.license_plate
	from autoflex_cloud.vehicles v
	where v.vehicle_id = o.vehicle_id) as "bi.licensePlate"
	, o.description as "bi.description"
from
	autoflex_cloud.orders o 
where
	(o.is_invoiced is null or o.is_invoiced = 0)
	and (o.is_archived is null or o.is_archived = 0)
	-- filter on 'inspectie'
	and o.order_status_id = 'F62E3B8E-FC41-4893-97F0-4A07ABF1B61A'
	and o.organization_id in $organizations
order by
	order_date desc,
	order_id asc
limit 50


-- ============== Real querys ==================
-- Nog lopende biedingen
select 
	vb.bid_on_date as "bi.date"
	, vb.bid as "bi.price"
	, vb.brand || ' ' || vb.vehicle_version || ' ' ||  vb.model as "bi.description"
from autoflex_cloud.vehicle_bids vb 
where
	vb.organization_id in $organizations
	and (vb.is_pledged is null or vb.is_pledged=0)
order by vb.bid_on_date desc
limit 50

-- ============== Real querys ==================
-- Wat komt er nog binnen
select 
	vb.bid_on_date as "bi.date"
	, vb.bid as "bi.price"
	, vb.brand || ' ' || vb.vehicle_version || ' ' ||  vb.model as "bi.description"
from autoflex_cloud.vehicle_bids vb 
where
	vb.organization_id in $organizations
	and (vb.is_pledged = 1)
order by vb.bid_on_date desc
limit 50

-- ============== Real querys ==================
-- Langstaanders, bij aantal
with 
"bidding" as (
	select s.dash_long_standing as long , s.dash_just_in as short
	from autoflex_cloud.settings s 
	where
	 s.organization_id in $organizations
),
"stock" as (
select 
	vehicles.vehicle_id 
	--, vehicles.license_plate as "bi.licensePlate"
	--, vehicles.purchase_date as "bi.date"
	, case 
		when vehicles.purchase_date::date >= (CURRENT_DATE - (select bidding.short from bidding)) then 'bi.just_in'
		when vehicles.purchase_date::date >= (CURRENT_DATE - (select bidding.long from bidding)) then 'bi.average'
		else 'bi.long'
	end as "langstaander"
from autoflex_cloud.vehicles 
left join purchase_sale_ids purchase_sale_ids on vehicles.vehicle_id=purchase_sale_ids.vehicle_id 
left join vehicle_purchases vehicle_purchases on purchase_sale_ids.vehicle_purchase_id=vehicle_purchases.vehicle_purchase_id 
left join purchases purchases on vehicle_purchases.purchase_id=purchases.purchase_id 
where 
	vehicles.organization_id in $organizations
	and (vehicles.is_archived is null or vehicles.is_archived = 0) 
	and vehicles.is_company_stock = 1 
	and purchases.purchase_date is not null
order by purchases.purchase_date asc
)
select stock.langstaander as label
, count(*) as value
from stock
group by stock.langstaander

-- ============== Real querys ==================
-- Langstaanders (GRID)
with 
"bidding" as (
	select s.dash_long_standing as long , s.dash_just_in as short
	from autoflex_cloud.settings s 
	where
	 s.organization_id in $organizations
)
select 
	vehicles.vehicle_id 
	, vehicles.license_plate as "bi.licensePlate"
	, purchases.purchase_date as "bi.date"
	, vehicle_purchases.created_date as "date2"
	, vehicles.brand || ' ' || vehicles.vehicle_version || ' ' ||  vehicles.model as "bi.description"
	, case 
		when vehicles.purchase_date::date >= (CURRENT_DATE - (select bidding.short from bidding)) then 'bi.just_in'
		when vehicles.purchase_date::date >= (CURRENT_DATE - (select bidding.long from bidding)) then 'bi.average'
		else 'bi.long'
	end
from autoflex_cloud.vehicles 
left join purchase_sale_ids purchase_sale_ids on vehicles.vehicle_id=purchase_sale_ids.vehicle_id 
left join vehicle_purchases vehicle_purchases on purchase_sale_ids.vehicle_purchase_id=vehicle_purchases.vehicle_purchase_id 
left join purchases purchases on vehicle_purchases.purchase_id=purchases.purchase_id 
where 
	vehicles.organization_id in $organizations
	and (vehicles.is_archived is null or vehicles.is_archived = 0) 
	and vehicles.is_company_stock = 1 
	and purchases.purchase_date is not null
order by purchases.purchase_date asc

-- ============== Real querys ==================
-- Lopende offertes orders (GRID)
select o.order_date  
	-- select contacts, fullname
	,(select c.v_display_name 
	from autoflex_cloud.contacts c 
	where c.contact_id = o.contact_id) as "bi.contact"
	-- select vehicle, licenseplage
	, v.license_plate 
	, o.description 
from autoflex_cloud.orders o 
left join order_lines_vehicle_sale olvs on olvs.order_id = o.order_id 
left join vehicles v on v.vehicle_id =olvs.vehicle_id 
where 
	o.organization_id in $organizations
and (o.is_archived is null or o.is_archived = 0)
and (o.is_invoiced is null or o.is_invoiced = 0)
and o.order_type = 2
and o.order_status_id = '1D5DC923-EB54-458A-8470-6A50442BC9C3'



-- ============== Real querys ==================
-- Afleveringen deze week

select
	--to_char(date(o.delivery_date),'Day') as "label"
	o.delivery_date::date
	, count(*) as value
from autoflex_cloud.orders o
where 
		o.organization_id in $organizations
	and 
		o.delivery_date::date between date_trunc('week',
		CURRENT_DATE) and (date_trunc('week',
		CURRENT_DATE) + '6 days'::interval)
	and
		o.order_status_id = (
	select
		order_status_id
	from
		autoflex_cloud.order_statuses os
	where
		os.status_description = 'Afgeleverd'
		and os.order_type = 2)
group by
	o.delivery_date::date
order by
	extract(isodow
from
	date(o.delivery_date))

-- ============== Real querys ==================
-- Afleveringen deze week
with 
"date_range" as (
select 
	generate_series( 
		date(date_trunc('week',CURRENT_DATE)::date)
		,date(date_trunc('week',CURRENT_DATE)::date +6)
		, '1 day'::interval)::date as day
),
"orders" as (
select
	--to_char(date(o.delivery_date),'Day') as "label"
	o.delivery_date::date
	, count(*) as value
from autoflex_cloud.orders o
where 
		o.organization_id in $organizations
	and 
		o.delivery_date::date between date_trunc('week',
		CURRENT_DATE) and (date_trunc('week',
		CURRENT_DATE) + '6 days'::interval)
	and
		o.order_status_id = (
	select
		order_status_id
	from
		autoflex_cloud.order_statuses os
	where
		os.status_description = 'Afgeleverd'
		and os.order_type = 2)
group by
	o.delivery_date::date
order by
	extract(isodow
from
	date(o.delivery_date))
)
select 
	to_char(date(date_range.day),'Day') as "label"
	--,extract(isodow from date(date_range.day))
	--,o.description
	,coalesce( o.value, 0) as "value"
from date_range
left join orders o on o.delivery_date=date_range.day




-- ============== Real querys ==================
-- APK's in range	
-- TODO - show all weekdays
WITH range_values AS (
  SELECT date_part('week', $startDate) as minval,
         date_part('week', $endDate) as maxval),
week_range AS (
  SELECT generate_series(minval::int, maxval::int) as week
  FROM range_values
),
weekly_counts AS (
  SELECT date_part('week', call_date) as weekly,
         count(*)
  FROM autoflex_cloud.vehicle_check_regulations vcr
	where
		vcr.organization_id in $organizations
	and 
		vcr.is_signed_off = 1 
	and
		vcr.is_archived is null or vcr.is_archived = 0
	and
		vcr.call_date is not null
	and
		vcr.call_date::date between $startDate and $endDate
  GROUP BY 1
)
SELECT 
 week_range.week as label,
       weekly_counts.count as value
FROM week_range
LEFT OUTER JOIN weekly_counts on week_range.week = weekly_counts.weekly
order by week_range.week


-- ============== Real querys ==================
-- Totaal aantal orders per periode
WITH 
"range_values" AS (
  SELECT to_char($startDate, 'ww')::int as minval,
         to_char($endDate, 'ww')::int as maxval),
"week_range" AS (
	SELECT generate_series(minval::int, maxval::int) as week
	FROM range_values
	),
"weekly_counts" AS (
select 
       date_part('week', invoice_date::date) AS weekly
       ,count(*) from autoflex_cloud.orders o
       --,sum(o.total_exclusive_tax)
	where
		o.organization_id in $organizations
	and o.is_invoiced = 1 
	and o.order_type = 1 -- Workshop
	--and o.order_type = 2 -- Sales		
	and 
	o.invoice_date between $startDate and $endDate
  GROUP BY 1
)
SELECT week_range.week as "label",
       coalesce(weekly_counts.count,0) as "value"
FROM week_range
LEFT OUTER JOIN weekly_counts on week_range.week = weekly_counts.weekly
order by week_range.week


-- ============== Real querys ==================
-- Werk deze dag voor mij
with 
"meta" as (
select
	avg(range.count)::decimal as avg,
	max(range.count) as max
	--select range.count, range.date
from
	(
	select
		count(*) as "count",
		o.planning_start_date::date as "date"
	from
		autoflex_cloud.orders o
	where 
		o.organization_id in $organizations
		and
		(o.is_invoiced is null
			or o.is_invoiced = 0)
		and
		(o.is_archived is null
			or o.is_archived = 0)
		and 
		o.order_type = 1
		and
		o.planning_start_date >= CURRENT_DATE - 60
	group by
		o.planning_start_date::date
	order by
		o.planning_start_date::date desc
	) as "range"
)
select     
	count(distinct o.order_id) as "value"
	,
	array[0,
	(
	select
		round(max * 1.1 + 0.5,
		0)
	from
		meta)] as "range"
	,
	array[(
	select
		round((round(avg,
		1) * 100) / round(max * 1.1 + 0.5,
		0),
		0)
	from
		meta),
	(
	select
		round((round(avg * 1.4,
		1) * 100) / round(max * 1.1 + 0.5,
		0),
		0)
	from
		meta),
	(
	select
		round((round(max,
		1) * 100) / round(max * 1.1 + 0.5,
		0),
		0)
	from
		meta)		
		] as "delimiters"
from
	autoflex_cloud.orders o
inner join autoflex_cloud.orders_to_employees ote on
	ote.employee_id = o.employee_id
where 
	o.organization_id in $organizations
	and ((o.employee_id_assigned_to in $employeeIds)
		or (ote.employee_id in $employeeIds))
	and coalesce(o.is_invoiced,
	0) = 0
	and coalesce(o.is_archived,
	0) = 0
	and 
		o.order_type = 1
	and	
		CURRENT_DATE between o.planning_start_date::date and o.planning_ready_date::date
		
		
		
-- weeklyTurnoverTotal - per week
WITH range_values AS (
  SELECT to_char($startDate, 'ww')::int as minval,
         to_char($endDate, 'ww')::int as maxval),
week_range AS (
  SELECT generate_series(minval::int, maxval::int) as week
  FROM range_values
),
weekly_counts AS (
select 
       date_part('week', invoice_date::date) AS weekly,
       count(*) from autoflex_cloud.orders o
	where
		o.organization_id in $organizations
	and 
		o.is_invoiced = 1 
	and
		-- workshop
		o.order_type = 1
	and
		o.invoice_date between $startDate and $endDate
--	and
--		date_part('year', invoice_date::date) = date_part('year', CURRENT_DATE)
  GROUP BY 1
)
SELECT week_range.week as label,
       weekly_counts.count as value
FROM week_range
LEFT OUTER JOIN weekly_counts on week_range.week = weekly_counts.weekly
order by week_range.week	
	
--================================
/*
 * APK
 */	
WITH range_values AS (
  SELECT date_part('week', $endDate - interval '3 months') as minval,
         date_part('week', $endDate) as maxval),
week_range AS (
  SELECT generate_series(minval::int, maxval::int) as week
  FROM range_values
),
weekly_counts AS (
  SELECT date_part('week', call_date) as weekly,
         count(*)
  FROM autoflex_cloud.vehicle_check_regulations vcr
	where
		vcr.organization_id in $organizations
	and 
		vcr.is_signed_off = 1 
	and
		vcr.is_archived is null or vcr.is_archived = 0
	and
		vcr.call_date is not null
	and
		vcr.call_date::date between $endDate - INTERVAL '3 months' and $endDate
  GROUP BY 1
)
SELECT week_range.week as label,
       weekly_counts.count
FROM week_range
LEFT OUTER JOIN weekly_counts on week_range.week = weekly_counts.weekly
order by week_range.week

-- ============== Real querys ==================
-- APK's in range	
WITH range_values AS (
  SELECT date_part('week', $startDate) as minval,
         date_part('week', $endDate) as maxval),
week_range AS (
  SELECT generate_series(minval::int, maxval::int) as week
  FROM range_values
),
weekly_counts AS (
  SELECT date_part('week', call_date) as weekly,
         count(*)
  FROM autoflex_cloud.vehicle_check_regulations vcr
	where
		vcr.organization_id in $organizations
	and 
		vcr.is_signed_off = 1 
	and
		vcr.is_archived is null or vcr.is_archived = 0
	and
		vcr.call_date is not null
	and
		vcr.call_date::date between $startDate and $endDate
  GROUP BY 1
)
SELECT week_range.week as label,
       weekly_counts.count as value
FROM week_range
LEFT OUTER JOIN weekly_counts on week_range.week = weekly_counts.weekly
order by week_range.week


-- ============== Real querys ==================
-- Groei, Voertuigen toegevoegd in Autoflex
WITH 
range_values AS (
  SELECT to_char($startDate, 'ww')::int as minval,
         to_char($endDate, 'ww')::int as maxval
),
range_week AS (
  SELECT generate_series(minval::int, maxval::int) as "week"
  FROM range_values
),
range_data as (
select 
	DATE_PART('week', v.created_date) as "week"
	, DATE_PART('week', v.created_date) || ' ' || DATE_PART('year', v.created_date)  as "label"
	, count(*) as "value"
from autoflex_cloud.vehicles v 
where 
	v.organization_id in $organizations
	and v.created_date is not null
	and v.created_date between $startDate and $endDate
group by DATE_PART('year', v.created_date), DATE_PART('week', v.created_date)
order by DATE_PART('year', v.created_date) asc, DATE_PART('week', v.created_date) asc
)
select 
	range_week.week as "label"
	, coalesce(range_data.value, 0) as "value"
	, 123 as "main"
	, 'Voertuigen' as "title"
from range_week
left join range_data on range_data.week = range_week.week

-- ============== Real querys ==================
-- Groei, Contacten/Relaties toegevoegd in Autoflex
WITH 
range_values AS (
  SELECT to_char($startDate, 'ww')::int as minval,
         to_char($endDate, 'ww')::int as maxval
),
range_week AS (
  SELECT generate_series(minval::int, maxval::int) as "week"
  FROM range_values
),
range_data as (
select 
	DATE_PART('week', a.created_date) as "week"
	, DATE_PART('week', a.created_date) || ' ' || DATE_PART('year', a.created_date)  as "label"
	, count(*) as "value"
from autoflex_cloud.contacts a 
where 
	a.organization_id in $organizations
	and a.created_date is not null
	and a.created_date between $startDate and $endDate
group by DATE_PART('year', a.created_date), DATE_PART('week', a.created_date)
order by DATE_PART('year', a.created_date) asc, DATE_PART('week', a.created_date) asc
)
select 
	range_week.week as "label"
	, coalesce(range_data.value, 0) as "value"
	, 123 as "main"
	, 'Contacten' as "title"
from range_week
left join range_data on range_data.week = range_week.week


-- ============== Real querys ==================
-- Groei, Werkorders toegevoegd in Autoflex
WITH 
range_values AS (
  SELECT to_char($startDate, 'ww')::int as minval,
         to_char($endDate, 'ww')::int as maxval
),
range_week AS (
  SELECT generate_series(minval::int, maxval::int) as "week"
  FROM range_values
),
range_data as (
select 
	DATE_PART('week', a.created_date) as "week"
	, DATE_PART('week', a.created_date) || ' ' || DATE_PART('year', a.created_date)  as "label"
	, count(*) as "value"
from autoflex_cloud.orders a 
where 
	a.organization_id in $organizations
	and a.order_type = 1 --Werkorders
	and a.created_date is not null
	and a.created_date between $startDate and $endDate
group by DATE_PART('year', a.created_date), DATE_PART('week', a.created_date)
order by DATE_PART('year', a.created_date) asc, DATE_PART('week', a.created_date) asc
)
select 
	range_week.week as "label"
	, coalesce(range_data.value, 0) as "value"
	, 123 as "main"
	, 'Werkorders' as "title"
from range_week
left join range_data on range_data.week = range_week.week



-- ============== Real querys ==================
-- Actieve Relaties / per week in Autoflex
WITH 
range_values AS (
  SELECT to_char($startDate, 'ww')::int as minval,
         to_char($endDate, 'ww')::int as maxval
),
range_week AS (
  SELECT generate_series(minval::int, maxval::int) as "week"
  FROM range_values
),
range_data as (
select 
	DATE_PART('week', a.last_visit_date) as "week"
	, DATE_PART('week', a.last_visit_date) || ' ' || DATE_PART('year', a.last_visit_date)  as "label"
	, count(*) as "value"
from autoflex_cloud.contacts a 
where 
	a.organization_id in $organizations
	and a.last_visit_date is not null
	and a.last_visit_date between $startDate and $endDate
group by DATE_PART('year', a.last_visit_date), DATE_PART('week', a.last_visit_date)
order by DATE_PART('year', a.last_visit_date) asc, DATE_PART('week', a.last_visit_date) asc
),
range_total as (
	select 
	count(*) as "total"
from autoflex_cloud.contacts a 
where 
	a.organization_id in $organizations
	and a.last_visit_date is not null
)
select 
	range_week.week as "label"
	, coalesce(range_data.value, 0) as "value"
	, (select * from range_total) as "main"
	, 'Totaal | per week' as "title"
from range_week
left join range_data on range_data.week = range_week.week

	
-- ============== Real querys ==================
-- Relaties per type
--   "Handel": 3,
--    "Leverancier": 4,
--    "Particulier": 1,
--    "Bedrijf": 2
select 
	a.contact_type 
	, case 
		when a.contact_type = 1 then 'Particulier'
		when a.contact_type = 2 then 'Bedrijf'
		when a.contact_type = 3 then 'Handel'
		when a.contact_type = 4 then 'Leverancier'
		else 'Onbekend'
	end as "label"
	, count(*) as "value"
from autoflex_cloud.contacts a 
where 
	a.organization_id in $organizations
group by a.contact_type 
order by a.contact_type asc

-- ============== Real querys ==================
-- Relaties per gender
--   "Man": 3,
--    "Vrouw": 4,
--    "Onbekend": 1,
select 
	a.gender  
	, case 
		when a.gender = 1 then 'Man'
		when a.gender = 2 then 'Vrouw'
		when a.gender = 3 then 'Onbekend'
		else 'Niet ingevuld'
	end as "label"
	, count(*) as "value"
from autoflex_cloud.contacts a 
where 
	a.organization_id in $organizations
group by a.gender 
order by a.gender  asc

-- ============== Real querys ==================
-- Realties: Verjaardagen per dag v/d week, aantal
with 
range_dayofweek as (
	select generate_series(1, 7) as "dayofweek"
),
range_data as (
select
	extract(isodow from a.date_birth::date) as "dayofweek"
	, count(*) as "value"
from autoflex_cloud.contacts a 
where 
	a.organization_id in $organizations
	and a.date_birth is not null
group by extract(isodow from a.date_birth::date)
order by extract(isodow from a.date_birth::date) asc
)
--
select 
	('{bi.monday,bi.tuesday,bi.wednesday,bi.thursday,bi.friday,bi.saturday,bi.sunday}'::text[])[range_dayofweek.dayofweek] as "label"
	, coalesce(range_data.value,0) as "value"
from range_dayofweek
left join range_data on range_data.dayofweek = range_dayofweek.dayofweek
order by range_dayofweek.dayofweek

-- ============== Real querys ==================
-- Relaties niet volledig
--  GENDER:   "Onbekend": 1, or null
(select 
	case 
		when a.gender = 3 then 'Geslacht: Onbekend'
		else 'Geslacht: Niet ingevuld'
	end as "label"
	, count(*) as "value"
from autoflex_cloud.contacts a 
where 
	a.organization_id in $organizations
	and (a.gender=3 or a.gender is null)
group by a.gender 
order by a.gender  asc
)
UNION
-- TOTAL
(select 
	'Totaal relaties' as "label"
	,count(*) as "value"
from autoflex_cloud.contacts a 
where 
	a.organization_id in $organizations
)
union
--Archived
(select 
	'Gearchiveerd' as "label"
	,count(*) as "value"
from autoflex_cloud.contacts a 
where 
	a.organization_id in $organizations
	and a.is_archived = 1
)
union
--Blocked
(select 
	'Geblokkeerd' as "label"
	,count(*) as "value"
from autoflex_cloud.contacts a 
where 
	a.organization_id in $organizations
	and a.is_blocked  = 1
)
order by "label"

-- ============== Real querys ==================
-- CONTACT: Wrong address (top 50)
select 	
	a.contact_id 
	,a.v_display_name as "bi.contact"
	, a.contact_number as "Relatienummer"
	, a.shortname  as "Zoeknaam"
	, case 
		when a.contact_type = 1 then 'Particulier'
		when a.contact_type = 2 then 'Bedrijf'
		when a.contact_type = 3 then 'Handel'
		when a.contact_type = 4 then 'Leverancier'
		else 'Onbekend'
	end as "Type"	
	, case 
		when a.postalcode is null then 'Adres: postcode is leeg'
		when a.city is null then 'Adres: Plaatsnaam is leeg'
		when a.address is null then 'Adres: Straat is leeg'
	end as "Probleem"
from autoflex_cloud.contacts a 
where 
	a.organization_id in $organizations
	and a.v_display_name != ''
	and ( 
		a.postalcode is null
		or a.city is null
		or a.address is null
	)
order by a.v_display_name asc
limit 50

-- ============== Real querys ==================
-- CONTACT: Tag's use + count
with
range_tag as (
select 
	a.description as "label"
	, a.contact_crm_tag_id as "id"
from autoflex_cloud.contact_crm_tags a
where 
	a.organization_id in $organizations
	),
range_data as (
select 
	jsonb_array_elements_text(crm_tags->'tags') as "id"
	, count(*) as "value"
	--,a.crm_tags->>'tags'
from autoflex_cloud.contacts a 
left join autoflex_cloud.contact_crm_tags cct on cct.contact_crm_tag_id  = a.contact_id 
where 
	a.organization_id in $organizations
	-- NO empty CRM_tags
	and a.crm_tags is not null
	and a.crm_tags::jsonb <> '{}'::jsonb
	and a.crm_tags::jsonb <> '{"tags": []}'::jsonb
group by jsonb_array_elements_text(crm_tags->'tags')
)
select 
	range_tag.label as "label"
	, range_data.value as "value"
from range_data
join range_tag on range_tag.id = range_data.id
order by range_data.value desc

-- ============== Real querys ==================
-- CONTACT GRID: contact communication info
with
"range_type" as (
select 
	a.description as "label"
	, a.contact_communication_type_id as "id"
from autoflex_cloud.contact_communication_types a
order by a.description asc
)
select 
	a.contact_id 
	, a.v_display_name as "bi.contact"
	, (select range_type.label from range_type where range_type.id = cc.contact_communication_type_id) as "type"
	, cc.communication_value as "value"
	, cc.description as "info"
	--, cc.sort_order  as "order"
from autoflex_cloud.contacts a 
join autoflex_cloud.contact_communications cc on cc.contact_id = a.contact_id 
where 
	a.organization_id in $organizations
order by a.v_display_name asc, cc.sort_order 

-- ============== Real querys ==================
-- CONTACT: contact communication info
with
"range_type" as (
select 
	a.description as "label"
	, a.contact_communication_type_id as "id"
from autoflex_cloud.contact_communication_types a
order by a.description asc
)
select
	t.label as "label"
	, count(*) as "value"
from autoflex_cloud.contacts a 
join autoflex_cloud.contact_communications cc on cc.contact_id = a.contact_id 
join range_type t on t.id = cc.contact_communication_type_id
where 
	a.organization_id in $organizations
group by t.label

-- ============== Real querys ==================
-- CONTACT: Group by city
select 
	 initcap(a.city) as "label"
	, count(*) as "value"
from autoflex_cloud.contacts a 
where 
	a.organization_id in $organizations
	and a.city is not null
	and a.city <> ''
group by initcap(a.city)
order by count(*) desc
limit 50
	
-- ============== Real querys ==================
-- CONTACT: Soort role gekoppeld aan voertuig
select 
	 vr.description as "label"
	 , count(*) as "value"
from autoflex_cloud.contacts a 
left join autoflex_cloud.vehicle_role_lines as vrl on vrl.contact_id = a.contact_id 
left join autoflex_cloud.vehicles v on v.vehicle_id = vrl.vehicle_id 
left join autoflex_cloud.vehicle_roles vr on vr.vehicle_role_id = vrl.vehicle_role_id 
where 
	a.organization_id in $organizations
	and vrl.vehicle_id is not null
group by vr.description 
order by count(*) desc

-- ============== Real querys ==================
-- CONTACT GRID: Contact with role connected to vehicle
select 
	a.contact_id 
	, a.v_display_name as "bi.contact"
	 , vr.description 	 
	 , v.vehicle_id
	 , v.v_display_name as "bi.vehicle"
	 , v.vehicle_id	 
	 , v.license_plate as "bi.licensePlate"
from autoflex_cloud.contacts a 
left join autoflex_cloud.vehicle_role_lines as vrl on vrl.contact_id = a.contact_id 
left join autoflex_cloud.vehicles v on v.vehicle_id = vrl.vehicle_id 
left join autoflex_cloud.vehicle_roles vr on vr.vehicle_role_id = vrl.vehicle_role_id 
where 
	a.organization_id in $organizations
	and vrl.vehicle_id is not null
order by a.v_display_name asc, vrl.sort_order asc, v.v_display_name asc


-- ============== Real querys ==================
-- MANAGEMENT: !@#TODO
select 
	a.v_display_name 
	, o.description 
	, o.invoice_status 
	, o.total_inclusive_tax 
from autoflex_cloud.contacts a 
join autoflex_cloud.orders o on 
	o.contact_id = a.contact_id 
	and o.invoice_status = 1
	and o.total_inclusive_tax > 0
where 
	a.organization_id in $organizations
order by a.v_display_name asc

-- ============== Real querys ==================
-- Customer GRID: show invoice with open value, days open
select 
	a.contact_id 
	, a.v_display_name as "bi.contact"
	, o.description as "Omschrijving"
	--, o.invoice_status 
	, o.total_inclusive_tax as "bi.price Totaal"
	--, o.is_invoiced 
	, o.open_amount as "bi.price Open"
	, o.invoice_date::date as "bi.date Factuur datum"
	, (CURRENT_DATE - o.invoice_date::date) AS "Dagen open"
from autoflex_cloud.contacts a 
join autoflex_cloud.orders o on 
	o.contact_id = a.contact_id 
	and o.invoice_status = 1
	and o.total_inclusive_tax > 0
	and o.open_amount > 0
where 
	a.organization_id in $organizations
order by (CURRENT_DATE - o.invoice_date::date) desc
limit 25

-- ============== Real querys ==================
-- Customer GRID: show invoice with open value, days open
select 
	a.contact_id 
	, a.v_display_name as "bi.contact"
	, o.description as "Omschrijving"
	--, o.invoice_status 
	, o.total_inclusive_tax as "bi.price Totaal"
	--, o.is_invoiced 
	-- , o.open_amount as "bi.price Open"
	, o.invoice_date::date as "bi.date Factuur datum"
	, (CURRENT_DATE - o.invoice_date::date) AS "Dagen open"
from autoflex_cloud.contacts a 
join autoflex_cloud.orders o on 
	o.contact_id = a.contact_id 
	and o.invoice_status = 1
	and o.total_inclusive_tax > 0
	and (CURRENT_DATE - o.invoice_date::date) < 180
where 
	a.organization_id in $organizations
order by (CURRENT_DATE - o.invoice_date::date) desc
limit 100

-- ============== Real querys ==================
-- Customer GRID: show invoice with open value, days open
select 
	a.contact_number as "Relatie#"
	, a.contact_id 
	, a.v_display_name as "bi.contact"
	, o.description as "Omschrijving"
	, o.total_inclusive_tax as "bi.price Totaal"
	--, o.is_financial_exported 
	--, o.open_amount as "bi.price Open"
	, case 
		when (CURRENT_DATE - o.invoice_date::date) between 15 and 30 then o.open_amount
		else 0
	end as "1-30"
	, case 
		when (CURRENT_DATE - o.invoice_date::date) between 31 and 60 then o.open_amount
		else 0
	end as "31-60"
	, case 
		when (CURRENT_DATE - o.invoice_date::date) between 61 and 90 then o.open_amount
		else 0
	end as "61-90"
	, case 
		when (CURRENT_DATE - o.invoice_date::date) > 90 then o.open_amount
		else 0
	end as "> 90"	
	, (CURRENT_DATE - o.invoice_date::date) AS "Dagen open"	
	, o.invoice_date::date as "bi.date Factuur datum"	
from autoflex_cloud.contacts a 
join autoflex_cloud.orders o on 
	o.contact_id = a.contact_id 
	and o.invoice_status = 1
	and o.total_inclusive_tax > 0
	and o.is_financial_exported  = 1
	and o.open_amount > 0
	and (CURRENT_DATE - o.invoice_date::date) > 15
	--and (CURRENT_DATE - o.invoice_date::date) < 180
where 
	a.organization_id in $organizations
	--a.organization_id = 'AF5298D4-10EB-4C72-9F5E-C25733FEEDFF'
order by 
	--(CURRENT_DATE - o.invoice_date::date) asc
	o.invoice_date::date desc
limit 100