Categories
MySQL

Maintenance MySQL – JobSheets Android: Jobsheet_History

CREATE VIEW jobsheet_history AS
SELECT x.jr_id, x.jr_jobid, x.jr_userid, x.jr_user_assigned, x.jr_pdaid, x.jr_datedue, x.jr_scandt, x.jr_priloc, x.jr_secloc, x.jr_terloc, x.jr_workrqd, x.jr_workcomp, x.jr_workleft, x.jr_jobcomp, x.jr_timetaken,
x.jr_pu1barcode, p1.il_description pu1desc, x.jr_pu1qty,
x.jr_pu2barcode, p2.il_description pu2desc, x.jr_pu2qty,
x.jr_pu3barcode, p3.il_description pu3desc, x.jr_pu3qty,
x.jr_pu4barcode, p4.il_description pu4desc, x.jr_pu4qty,
x.jr_pu5barcode, p5.il_description pu5desc, x.jr_pu5qty,
x.jr_pu6barcode, p6.il_description pu6desc, x.jr_pu6qty,
x.jr_pu7barcode, p7.il_description pu7desc, x.jr_pu7qty,
x.jr_pu8barcode, p8.il_description pu8desc, x.jr_pu8qty,
x.jr_pu9barcode, p9.il_description pu9desc, x.jr_pu9qty,
x.jr_pu10barcode, p10.il_description pu10desc, x.jr_pu10qty, x.jr_ts
FROM (((((((((jobsheet_report x
LEFT JOIN item_list as p1 on x.jr_pu1barcode=p1.il_barcode)
LEFT JOIN item_list as p2 on x.jr_pu2barcode=p2.il_barcode)
LEFT JOIN item_list as p3 on x.jr_pu3barcode=p3.il_barcode)
LEFT JOIN item_list as p4 on x.jr_pu4barcode=p4.il_barcode)
LEFT JOIN item_list as p5 on x.jr_pu5barcode=p5.il_barcode)
LEFT JOIN item_list as p6 on x.jr_pu6barcode=p6.il_barcode)
LEFT JOIN item_list as p7 on x.jr_pu7barcode=p7.il_barcode)
LEFT JOIN item_list as p8 on x.jr_pu8barcode=p8.il_barcode)
LEFT JOIN item_list as p9 on x.jr_pu9barcode=p9.il_barcode)
LEFT JOIN item_list as p10 on x.jr_pu10barcode=p10.il_barcode;

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);