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

Access VBA > MySQL Update TimeSheetRates

Private Sub UpdateTimeSheetCalculations()
On Error GoTo Err_UpdateTimeSheetCalculations
‘this simple subroutine looks at the MySQL View “time_sheet_calc_dynamic” and copies the calculated values therein to the time_sheet table
‘The “time_sheet_calc_dynamic” does not include records where the “ts_calculation_completed” column is set to 1. The DB Manager can switch this flag
‘on and off via the Time Sheet Management form or when running a report for the timesheet. This means calculations made are “frozen in time”
‘with the rates as they were so subsequent changes to mileage or on-call rates are not re-calculated.

Dim strSQLUpdate As String

strSQLUpdate = “UPDATE time_sheet AS x INNER JOIN time_sheet_calc_dynamic AS y ON y.ts_id = x.ts_id SET ”

strSQLUpdate = strSQLUpdate & “x.ts_MonMileage = y.MonMileage,”
strSQLUpdate = strSQLUpdate & “x.ts_TueMileage = y.TueMileage,”
strSQLUpdate = strSQLUpdate & “x.ts_wedMileage = y.wedMileage,”
strSQLUpdate = strSQLUpdate & “x.ts_thuMileage = y.thuMileage,”
strSQLUpdate = strSQLUpdate & “x.ts_friMileage = y.friMileage,”
strSQLUpdate = strSQLUpdate & “x.ts_satMileage = y.satMileage,”
strSQLUpdate = strSQLUpdate & “x.ts_sunMileage = y.sunMileage,”
strSQLUpdate = strSQLUpdate & “x.ts_mamMileage = y.mamMileage,”
strSQLUpdate = strSQLUpdate & “x.ts_MonStandbyRate = y.MonStandbyRate,”
strSQLUpdate = strSQLUpdate & “x.ts_tueStandbyRate = y.tueStandbyRate,”
strSQLUpdate = strSQLUpdate & “x.ts_wedStandbyRate = y.wedStandbyRate,”
strSQLUpdate = strSQLUpdate & “x.ts_thuStandbyRate = y.thuStandbyRate,”
strSQLUpdate = strSQLUpdate & “x.ts_friStandbyRate = y.friStandbyRate,”
strSQLUpdate = strSQLUpdate & “x.ts_MonTotalHours = y.MonTotalHours,”
strSQLUpdate = strSQLUpdate & “x.ts_tueTotalHours = y.tueTotalHours,”
strSQLUpdate = strSQLUpdate & “x.ts_wedTotalHours = y.wedTotalHours,”
strSQLUpdate = strSQLUpdate & “x.ts_thuTotalHours = y.thuTotalHours,”
strSQLUpdate = strSQLUpdate & “x.ts_friTotalHours = y.friTotalHours,”
strSQLUpdate = strSQLUpdate & “x.ts_satTotalHours = y.satTotalHours,”
strSQLUpdate = strSQLUpdate & “x.ts_sunTotalHours = y.sunTotalHours,”
strSQLUpdate = strSQLUpdate & “x.ts_mamTotalHours = y.mamTotalHours,”
strSQLUpdate = strSQLUpdate & “x.ts_MonMileageCosts = y.MonMileageCosts,”
strSQLUpdate = strSQLUpdate & “x.ts_tueMileageCosts = y.tueMileageCosts,”
strSQLUpdate = strSQLUpdate & “x.ts_wedMileageCosts = y.wedMileageCosts,”
strSQLUpdate = strSQLUpdate & “x.ts_thuMileageCosts = y.thuMileageCosts,”
strSQLUpdate = strSQLUpdate & “x.ts_friMileageCosts = y.friMileageCosts,”
strSQLUpdate = strSQLUpdate & “x.ts_satMileageCosts = y.satMileageCosts,”
strSQLUpdate = strSQLUpdate & “x.ts_sunMileageCosts = y.sunMileageCosts,”
strSQLUpdate = strSQLUpdate & “x.ts_mamMileageCosts = y.mamMileageCosts;”

CurrentDb.Execute strSQLUpdate, dbFailOnError

Exit_UpdateTimeSheetCalculations:
Exit Sub
Err_UpdateTimeSheetCalculations:
MsgBox Err.description
Resume Exit_UpdateTimeSheetCalculations
End Sub

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

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;

MySQL Commands

Export:


mysqldump -u username -p database_name > dump.sql

Import:


mysql -u username -p database_name < dump.sql

Live export/duplication (note you may need to enter the password on the line for the mysql command):


mysql -uuser -ppassword -e 'DROP DATABASE test_db;'
mysql -uuser -ppassword -e 'CREATE DATABASE test_db;'
mysqldump -uuser -ppassword live_db | mysql -uuser -ppassword test_db;

 

MySQL ODBC 5.2.6 Driver for Windows (2008 r2)

I was unable to install the x86 or x64 MySQL ODBC driver (v5.2.6) in my Windows 2008r2 server due to a reported missing module, the exact error was:

“Product: MySQL Connector/ODBC 5.2 — Error 1918.Error installing ODBC driver MySQL ODBC 5.2 ANSI Driver, ODBC error 13: The setup routines for the MySQL ODBC 5.2 ANSI Driver ODBC driver could not be loaded due to system error code 126: The specified module could not be found. (C:Program FilesMySQLConnector ODBC 5.2myodbc5S.dll).. Verify that the file MySQL ODBC 5.2 ANSI Driver exists and that you can access it.”

A little searching turned up a gem from another blogger, copying C:WindowsSystem32msvcr100_clr0400.dll to C:WindowsSystem32msvcr100.dll and then restarting the MySQL driver installation worked a treat!

SOURCE: Thanks to Matt of http://iwantanitcareer.com for his reference in this fix!