Maintenance MySQL – Timesheets Android: time_sheet_last

1. time_sheet_last_sub
CREATE VIEW time_sheet_last_sub AS
SELECT DISTINCT MAX(ts_id) ts_id, ts_userid, ts_WE, MAX(ts_scandt) ts_last_synch
FROM time_sheet GROUP BY ts_userid, ts_WE;

2. time_sheet_last
CREATE VIEW time_sheet_last AS
SELECT time_sheet.* from time_sheet, time_sheet_last_sub
WHERE time_sheet.ts_id=time_sheet_last_sub.ts_id
ORDER BY time_sheet.ts_id DESC;

Maintenance MySQL – Job Sheets Android: jobsheet_report_last_entry

1. jobsheet_report_last_entry_sub
CREATE VIEW jobsheet_report_last_entry_sub AS
SELECT MAX(jr_id) id, jr_jobid, MAX(jr_scandt) scandt
FROM jobsheet_report
GROUP BY jr_jobid;

2. jobsheet_report_last_entry
CREATE VIEW jobsheet_report_last_entry AS
SELECT x.*
FROM jobsheet_report x, jobsheet_report_last_entry_sub y
WHERE x.jr_id=y.id;

Maintenance MySQL – Job Sheets Android: jobsheet_report_total

1. jobsheet_report_total_sub
CREATE VIEW jobsheet_report_total_sub(jobid, barcode, qty) AS
(SELECT jr_jobid jobid, jr_pu1barcode Barcode, jr_pu1qty qty FROM jobsheet_report_last_entry WHERE jobsheet_report_last_entry.jr_pu1barcode <> “”)
union all
(SELECT jr_jobid jobid, jr_pu2barcode Barcode, jr_pu2qty qty FROM jobsheet_report_last_entry WHERE jobsheet_report_last_entry.jr_pu2barcode <> “”)
union all
(SELECT jr_jobid jobid, jr_pu3barcode Barcode, jr_pu3qty qty FROM jobsheet_report_last_entry WHERE jobsheet_report_last_entry.jr_pu3barcode <> “”)
union all
(SELECT jr_jobid jobid, jr_pu4barcode Barcode, jr_pu4qty qty FROM jobsheet_report_last_entry WHERE jobsheet_report_last_entry.jr_pu4barcode <> “”)
union all
(SELECT jr_jobid jobid, jr_pu5barcode Barcode, jr_pu5qty qty FROM jobsheet_report_last_entry WHERE jobsheet_report_last_entry.jr_pu5barcode <> “”)
union all
(SELECT jr_jobid jobid, jr_pu6barcode Barcode, jr_pu6qty qty FROM jobsheet_report_last_entry WHERE jobsheet_report_last_entry.jr_pu6barcode <> “”)
union all
(SELECT jr_jobid jobid, jr_pu7barcode Barcode, jr_pu7qty qty FROM jobsheet_report_last_entry WHERE jobsheet_report_last_entry.jr_pu7barcode <> “”)
union all
(SELECT jr_jobid jobid, jr_pu8barcode Barcode, jr_pu8qty qty FROM jobsheet_report_last_entry WHERE jobsheet_report_last_entry.jr_pu8barcode <> “”)
union all
(SELECT jr_jobid jobid, jr_pu9barcode Barcode, jr_pu9qty qty FROM jobsheet_report_last_entry WHERE jobsheet_report_last_entry.jr_pu9barcode <> “”)
union all
(SELECT jr_jobid jobid, jr_pu10barcode Barcode, jr_pu10qty qty FROM jobsheet_report_last_entry WHERE jobsheet_report_last_entry.jr_pu10barcode <> “”);

2. jobsheet_report_total
CREATE VIEW jobsheet_report_total AS
SELECT barcode, SUM(qty) qtyused
FROM jobsheet_report_total_sub
GROUP BY Barcode;