SELECT statusname AS "מצב פקודה", count(*) AS "מספר פקודות" FROM {{schema}}.workorder WHERE deletedat IS NULL GROUP BY 1 ORDER BY 2 DESC;

SELECT workordernumber AS "פקודת עבודה", sku AS "מק״ט", workflowname AS "תהליך", statusname AS "מצב", progress AS "התקדמות", quantity AS "כמות מתוכננת", expecteddate AS "מועד יעד" FROM {{schema}}.workorder WHERE deletedat IS NULL AND replace(statustype,'WO.STATUSES.','') IN ('NEW','IN_PROGRESS','FROZEN') ORDER BY expecteddate NULLS LAST;

SELECT date_trunc('month',createdat) AS "חודש פתיחה", count(*) AS "פקודות" FROM {{schema}}.workorder WHERE deletedat IS NULL GROUP BY 1 ORDER BY 1;

SELECT date_trunc('month',"end") AS "חודש סיום", count(*) AS "פקודות" FROM {{schema}}.workorder WHERE deletedat IS NULL AND replace(statustype,'WO.STATUSES.','')='END' AND "end" IS NOT NULL GROUP BY 1 ORDER BY 1;

SELECT coalesce(workflowname,'(ללא תהליך)') AS "תהליך",count(*) AS "פקודות פתוחות" FROM {{schema}}.workorder WHERE deletedat IS NULL AND replace(statustype,'WO.STATUSES.','') IN ('NEW','IN_PROGRESS') GROUP BY 1 ORDER BY 2 DESC;

SELECT sku AS "מק״ט",count(*) AS "פקודות פתוחות" FROM {{schema}}.workorder WHERE deletedat IS NULL AND replace(statustype,'WO.STATUSES.','') IN ('NEW','IN_PROGRESS') GROUP BY 1 ORDER BY 2 DESC;

SELECT CASE WHEN date_diff('day',createdat,current_timestamp)<7 THEN '01 · פחות משבוע' WHEN date_diff('day',createdat,current_timestamp)<30 THEN '02 · שבוע עד חודש' WHEN date_diff('day',createdat,current_timestamp)<90 THEN '03 · חודש עד שלושה' ELSE '04 · מעל שלושה חודשים' END AS "גיל פקודה",count(*) AS "פקודות" FROM {{schema}}.workorder WHERE deletedat IS NULL AND replace(statustype,'WO.STATUSES.','') IN ('NEW','IN_PROGRESS') GROUP BY 1 ORDER BY 1;

SELECT workordernumber AS "פקודה",sku AS "מק״ט",workflowname AS "תהליך",expecteddate AS "מועד יעד",date_diff('day',expecteddate,current_timestamp) AS "ימי איחור",progress AS "התקדמות" FROM {{schema}}.workorder WHERE deletedat IS NULL AND replace(statustype,'WO.STATUSES.','') IN ('NEW','IN_PROGRESS') AND expecteddate<current_timestamp ORDER BY 5 DESC;

SELECT workordernumber AS "פקודה",sku AS "מק״ט",workflowname AS "תהליך",createdat AS "תאריך יצירה",statusname AS "מצב" FROM {{schema}}.workorder WHERE deletedat IS NULL AND replace(statustype,'WO.STATUSES.','') IN ('NEW','IN_PROGRESS') AND expecteddate IS NULL ORDER BY createdat;

SELECT date_trunc('month',"end") AS "חודש סיום",count(*) AS "פקודות עם מועדים",count_if("end"<=expecteddate) AS "הסתיימו במועד",100.0*count_if("end"<=expecteddate)/nullif(count(*),0) AS "אחוז במועד" FROM {{schema}}.workorder WHERE deletedat IS NULL AND replace(statustype,'WO.STATUSES.','')='END' AND "end" IS NOT NULL AND expecteddate IS NOT NULL GROUP BY 1 ORDER BY 1;

SELECT workflowname AS "תהליך",count(*) AS "פקודות",approx_percentile(date_diff('hour',coalesce(releasedat,createdat),"end")/24.0,0.5) AS "חציון ימים",count_if(releasedat IS NULL) AS "חושב ממועד יצירה" FROM {{schema}}.workorder WHERE deletedat IS NULL AND replace(statustype,'WO.STATUSES.','')='END' AND "end">=coalesce(releasedat,createdat) GROUP BY 1 ORDER BY 3 DESC;

SELECT coalesce(source,'local') AS "מקור",count(*) AS "פקודות" FROM {{schema}}.workorder WHERE deletedat IS NULL GROUP BY 1 ORDER BY 2 DESC;

SELECT statusname AS "מצב",isserial AS "מנוהל סידורית",bulkproduction AS "ייצור באצווה",count(*) AS "פקודות" FROM {{schema}}.workorder WHERE deletedat IS NULL GROUP BY 1,2,3 ORDER BY 4 DESC;

SELECT CASE WHEN progress IS NULL THEN 'לא רשומה' WHEN progress=0 THEN 'אפס' ELSE 'התקדמות רשומה' END AS "התקדמות",count(*) AS "פקודות" FROM {{schema}}.workorder WHERE deletedat IS NULL AND replace(statustype,'WO.STATUSES.','') IN ('NEW','IN_PROGRESS') GROUP BY 1;

SELECT workordernumber AS "פקודה",sku AS "מק״ט",workflowname AS "תהליך",expecteddate AS "מועד יעד","end" AS "מועד סיום",CASE WHEN expecteddate IS NULL OR "end" IS NULL THEN 'חסר מידע' WHEN "end"<=expecteddate THEN 'במועד' ELSE 'באיחור' END AS "עמידה במועד" FROM {{schema}}.workorder WHERE deletedat IS NULL AND replace(statustype,'WO.STATUSES.','')='END' ORDER BY "end" DESC;

SELECT date_trunc('month',expecteddate) AS "חודש יעד",statusname AS "מצב",count(*) AS "פקודות" FROM {{schema}}.workorder WHERE deletedat IS NULL AND expecteddate IS NOT NULL GROUP BY 1,2 ORDER BY 1,2;

SELECT workordernumber AS "פקודה", sku AS "מק״ט", workflowname AS "תהליך", statusname AS "מצב", expecteddate AS "מועד יעד", start AS "תחילת עבודה", quantity AS "כמות מתוכננת"
FROM {{schema}}.workorder
WHERE deletedat IS NULL AND replace(statustype,'WO.STATUSES.','') IN ('NEW','IN_PROGRESS')
AND expecteddate>=current_timestamp AND expecteddate<current_timestamp+interval '7' day
ORDER BY expecteddate;

SELECT workordernumber AS "פקודה", sku AS "מק״ט", workflowname AS "תהליך", statusname AS "מצב", createdat AS "נוצרה", expecteddate AS "מועד יעד", quantity AS "כמות מתוכננת"
FROM {{schema}}.workorder
WHERE deletedat IS NULL AND replace(statustype,'WO.STATUSES.','')='FROZEN'
ORDER BY expecteddate NULLS LAST;

SELECT DISTINCT workflowname AS "תהליך"
FROM {{schema}}.workorder
WHERE deletedat IS NULL
AND replace(statustype,'WO.STATUSES.','') IN ('NEW','IN_PROGRESS','FROZEN')
AND workflowname IS NOT NULL AND trim(workflowname)<>''
ORDER BY 1;