Categories
MySQL

MySQL Commands Maintenance v2

Create an Archive table for unused assets

This SQL statement creates the archive table based on any primary locations that equal ’23 Banbury Road’ or ‘Acland’, it then pulls in the data from the other reference tables.

CREATE TABLE `_archive_asset_log_verbose` AS
SELECT
`x`.`autonum`,
`x`.`barcode`,
`asset_list`.`description`,
`asset_list`.`assetgroup`,
`locations`.`priloc` as `locpriloc`,
`locations`.`secloc` as `locsecloc`,
`locations`.`terloc` as `locterloc`,
`x`.`datedue`,
`x`.`user`,
`users`.`employeename`,
`x`.`pdaid`,
`pda`.`human` as `pda#`,
`pda`.`pdauid`,
`x`.`timecreated`,
`x`.`status`,
`x`.`actiontaken`,
`x`.`actionremaining`,
`x`.`checkrqd`,
`x`.`changedesc`,
`x`.`changegroup`,
`x`.`changeloc`,
`changeloc`.`priloc` as `changepriloc`,
`changeloc`.`secloc` as `changesecloc`,
`changeloc`.`terloc` as `changeterloc`,
`x`.`ts`

FROM (((((maintenancev2.asset_log as x
LEFT JOIN `users` on `x`.`user`=`users`.`autonum`)
LEFT JOIN `pda` on `x`.`pdaid`=`pda`.`autonum`)
LEFT JOIN `asset_list` on `x`.`barcode`=`asset_list`.`barcode`)
LEFT JOIN `locations` on `asset_list`.`loc`=`locations`.`autonum`)
LEFT JOIN `locations` as `changeloc` on `x`.`changeloc`=`changeloc`.`autonum`)

where `x`.`barcode` in
(select `barcode` from `asset_list` where `loc` in
(select `autonum` from `maintenancev2`.`locations`
where ((`priloc`=’23 Banbury Road’) or `priloc`=’Acland’)
)
);

Categories
MySQL

Dynamic MySQL View for Calculated fields

the field on it’s own:
SELECT IF(z.ts_mon_journeys * (SELECT tsm_mileage from time_sheet_mileage where tsm_user=’MinHourPerJourney’) > IfNull(z.ts_mon_hours,0), z.ts_mon_journeys * (SELECT tsm_mileage from time_sheet_mileage where tsm_user=’MinHourPerJourney’), z.ts_mon_hours) TotalHours

mileagecosts:
(journeys * mileageforuser) * pricepermile
(IfNull(z.ts_mon_journeys,0) * (SELECT y.tsm_mileage from time_sheet_mileage y where y.tsm_user=z.ts_userid)) * (SELECT tsr_payment_rate from time_sheet_rates WHERE tsr_call_out_code=’PricePerMile’) MileageCosts

FINAL STATEMENT:

CREATE VIEW time_sheet_calc_dynamic As SELECT z.ts_id, 
IfNull(z.ts_mon_journeys,0) * IfNull((SELECT y.tsm_mileage from time_sheet_mileage y where y.tsm_user=z.ts_userid),0) MonMileage, 
IfNull((SELECT tsr_payment_rate from time_sheet_rates where time_sheet_rates.tsr_call_out_code=z.ts_mon_standby),0) MonStandbyRate, 
IfNull((SELECT IF(z.ts_mon_journeys * (SELECT tsm_mileage from time_sheet_mileage where tsm_user="MinHourPerJourney") > IfNull(z.ts_mon_hours,0), z.ts_mon_journeys * (SELECT tsm_mileage from time_sheet_mileage where tsm_user="MinHourPerJourney"), z.ts_mon_hours)),0) MonTotalHours, 
(IfNull(z.ts_mon_journeys,0) * (SELECT y.tsm_mileage from time_sheet_mileage y where y.tsm_user=z.ts_userid)) * (SELECT tsr_payment_rate from time_sheet_rates WHERE tsr_call_out_code="PricePerMile") MonMileageCosts,
IfNull(z.ts_tue_journeys,0) * IfNull((SELECT y.tsm_mileage from time_sheet_mileage y where y.tsm_user=z.ts_userid),0) TueMileage,
IfNull((SELECT tsr_payment_rate from time_sheet_rates where time_sheet_rates.tsr_call_out_code=z.ts_Tue_standby),0) TueStandbyRate,
IfNull((SELECT IF(z.ts_Tue_journeys * (SELECT tsm_mileage from time_sheet_mileage where tsm_user="MinHourPerJourney") > IfNull(z.ts_Tue_hours,0), z.ts_Tue_journeys * (SELECT tsm_mileage from time_sheet_mileage where tsm_user="MinHourPerJourney"), z.ts_Tue_hours)),0) TueTotalHours,
(IfNull(z.ts_Tue_journeys,0) * (SELECT y.tsm_mileage from time_sheet_mileage y where y.tsm_user=z.ts_userid)) * (SELECT tsr_payment_rate from time_sheet_rates WHERE tsr_call_out_code="PricePerMile") TueMileageCosts,

IfNull(z.ts_wed_journeys,0) * IfNull((SELECT y.tsm_mileage from time_sheet_mileage y where y.tsm_user=z.ts_userid),0) wedMileage,
IfNull((SELECT tsr_payment_rate from time_sheet_rates where time_sheet_rates.tsr_call_out_code=z.ts_wed_standby),0) wedStandbyRate,
IfNull((SELECT IF(z.ts_wed_journeys * (SELECT tsm_mileage from time_sheet_mileage where tsm_user="MinHourPerJourney") > IfNull(z.ts_wed_hours,0), z.ts_wed_journeys * (SELECT tsm_mileage from time_sheet_mileage where tsm_user="MinHourPerJourney"), z.ts_wed_hours)),0) wedTotalHours,
(IfNull(z.ts_wed_journeys,0) * (SELECT y.tsm_mileage from time_sheet_mileage y where y.tsm_user=z.ts_userid)) * (SELECT tsr_payment_rate from time_sheet_rates WHERE tsr_call_out_code="PricePerMile") wedMileageCosts,

IfNull(z.ts_thu_journeys,0) * IfNull((SELECT y.tsm_mileage from time_sheet_mileage y where y.tsm_user=z.ts_userid),0) thuMileage,
IfNull((SELECT tsr_payment_rate from time_sheet_rates where time_sheet_rates.tsr_call_out_code=z.ts_thu_standby),0) thuStandbyRate,
IfNull((SELECT IF(z.ts_thu_journeys * (SELECT tsm_mileage from time_sheet_mileage where tsm_user="MinHourPerJourney") > IfNull(z.ts_thu_hours,0), z.ts_thu_journeys * (SELECT tsm_mileage from time_sheet_mileage where tsm_user="MinHourPerJourney"), z.ts_thu_hours)),0) thuTotalHours,
(IfNull(z.ts_thu_journeys,0) * (SELECT y.tsm_mileage from time_sheet_mileage y where y.tsm_user=z.ts_userid)) * (SELECT tsr_payment_rate from time_sheet_rates WHERE tsr_call_out_code="PricePerMile") thuMileageCosts,

IfNull(z.ts_fri_journeys,0) * IfNull((SELECT y.tsm_mileage from time_sheet_mileage y where y.tsm_user=z.ts_userid),0) friMileage,
IfNull((SELECT tsr_payment_rate from time_sheet_rates where time_sheet_rates.tsr_call_out_code=z.ts_fri_standby),0) friStandbyRate,
IfNull((SELECT IF(z.ts_fri_journeys * (SELECT tsm_mileage from time_sheet_mileage where tsm_user="MinHourPerJourney") > IfNull(z.ts_fri_hours,0), z.ts_fri_journeys * (SELECT tsm_mileage from time_sheet_mileage where tsm_user="MinHourPerJourney"), z.ts_fri_hours)),0) friTotalHours,
(IfNull(z.ts_fri_journeys,0) * (SELECT y.tsm_mileage from time_sheet_mileage y where y.tsm_user=z.ts_userid)) * (SELECT tsr_payment_rate from time_sheet_rates WHERE tsr_call_out_code="PricePerMile") friMileageCosts,

IfNull(z.ts_sat_journeys,0) * IfNull((SELECT y.tsm_mileage from time_sheet_mileage y where y.tsm_user=z.ts_userid),0) satMileage,
IfNull((SELECT IF(z.ts_sat_journeys * (SELECT tsm_mileage from time_sheet_mileage where tsm_user="MinHourPerJourney") > IfNull(z.ts_sat_hours,0), z.ts_sat_journeys * (SELECT tsm_mileage from time_sheet_mileage where tsm_user="MinHourPerJourney"), z.ts_sat_hours)),0) satTotalHours,
(IfNull(z.ts_sat_journeys,0) * (SELECT y.tsm_mileage from time_sheet_mileage y where y.tsm_user=z.ts_userid)) * (SELECT tsr_payment_rate from time_sheet_rates WHERE tsr_call_out_code="PricePerMile") satMileageCosts,

IfNull(z.ts_sun_journeys,0) * IfNull((SELECT y.tsm_mileage from time_sheet_mileage y where y.tsm_user=z.ts_userid),0) sunMileage,
IfNull((SELECT IF(z.ts_sun_journeys * (SELECT tsm_mileage from time_sheet_mileage where tsm_user="MinHourPerJourney") > IfNull(z.ts_sun_hours,0), z.ts_sun_journeys * (SELECT tsm_mileage from time_sheet_mileage where tsm_user="MinHourPerJourney"), z.ts_sun_hours)),0) sunTotalHours,
(IfNull(z.ts_sun_journeys,0) * (SELECT y.tsm_mileage from time_sheet_mileage y where y.tsm_user=z.ts_userid)) * (SELECT tsr_payment_rate from time_sheet_rates WHERE tsr_call_out_code="PricePerMile") sunMileageCosts,

IfNull(z.ts_mam_journeys,0) * IfNull((SELECT y.tsm_mileage from time_sheet_mileage y where y.tsm_user=z.ts_userid),0) mamMileage,
IfNull((SELECT IF(z.ts_mam_journeys * (SELECT tsm_mileage from time_sheet_mileage where tsm_user="MinHourPerJourney") > IfNull(z.ts_mam_hours,0), z.ts_mam_journeys * (SELECT tsm_mileage from time_sheet_mileage where tsm_user="MinHourPerJourney"), z.ts_mam_hours)),0) mamTotalHours,
(IfNull(z.ts_mam_journeys,0) * (SELECT y.tsm_mileage from time_sheet_mileage y where y.tsm_user=z.ts_userid)) * (SELECT tsr_payment_rate from time_sheet_rates WHERE tsr_call_out_code="PricePerMile") mamMileageCosts,

z.ts_ts


FROM time_sheet z WHERE z.ts_calculation_completed<>1 or IsNull(z.ts_calculation_completed) ; 

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;