--
-- ============== 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