Categories
Uncategorized

Maintenance MySQL – Timesheets Android: time_sheet_we_next

CREATE VIEW time_sheet_we_next AS
SELECT x.tsw_we from time_sheet_we x
WHERE x.tsw_we >=DATE_SUB(now(), INTERVAL 21 DAY)
AND x.tsw_we

Categories
Uncategorized

Maintenance MySQL – Timesheets Android: time_sheet_last_month

CREATE VIEW time_sheet_last_month AS
SELECT * from time_sheet_last t
WHERE t.ts_WE >= DATE_ADD(LAST_DAY(DATE_SUB(NOW(), INTERVAL 2 MONTH)), INTERVAL 1 DAY);

Categories
Uncategorized

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;

Categories
Uncategorized

Maintenance MySQL – Job Sheets Android: locations_unique

CREATE VIEW locations_distinctpri AS
SELECT DISTINCT l_priloc from locations;

Categories
Uncategorized

Maintenance MySQL – Job Sheets Android: jobsheet_report_closed

CREATE VIEW jobsheet_report_closed AS
SELECT * FROM jobsheet_report_last_entry

WHERE jr_jobcomp=-1;

Categories
Uncategorized

Maintenance MySQL – Job Sheets Android: jobsheet_report_open

CREATE VIEW jobsheet_report_open AS
SELECT * FROM jobsheet_report_last_entry
WHERE jr_jobcomp=0
OR jr_jobcomp IS NULL ;

Categories
Uncategorized

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;

Categories
Uncategorized

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;

Categories
Uncategorized

Maintenance MySQL – Stock Report Android: stock_record_current_day

CREATE VIEW stock_record_current_day AS
SELECT * FROM stock_record
WHERE
stock_record.sr_ts >=DATE_SUB(now(), INTERVAL 1 DAY);

Categories
Uncategorized

Maintenance MySQL – Stock Report Android: congroups

CREATE VIEW congroups AS
SELECT DISTINCT itemgroup
FROM t_conlist;