#UPDATE Machine_Archive SET lease = 'Owned' WHERE lease = 'Owner'
/*Apostrophes are escaped in export, but cause errors on insert
SELECT * FROM Machine_Archive WHERE room_name LIKE("%'%");
SELECT * FROM Machine_Archive WHERE `Comments` LIKE("%'%");
SELECT * from Machine_Archive WHERE length(room_name) = 83; ID = 34463
SELECT * FROM `Machine_Archive` WHERE org_id_ma is null; ID = 25427
*/


SELECT `ID` arch_device_id,
CAST(org_id_ma AS UNSIGNED) organizations_id,
CAST(building_id_ma AS UNSIGNED) buildings_id,
CAST(dept_id_ma AS UNSIGNED) departments_id,

room_name,
#(CASE WHEN room_name LIKE("%'%") THEN NULL ELSE room_name END) room_name,
CAST(school_year AS UNSIGNED)school_year,
vendor_id,
`VendorMachID` vendor_mach_id,
CAST(model_id AS UNSIGNED) model_id,
`Features` features_list,
`SerialNumber` serial_number,
(CASE `Condition` WHEN '' THEN NULL ELSE `Condition` END) mach_condition,
`Traded` traded,
`Install` install_date,
`Anticipated` projected_volume_black,
`ProjColorVol` projected_volume_color,

/*CAST(`CostCopy` AS UNSIGNED) cpc_black,
CAST(`NewCostCopy` AS UNSIGNED) new_cpc_black,*/

/*Some of these values in floorplans db are way too long*/
ROUND(`CostCopy`,2) cpc_black,
ROUND(`NewCostCopy`,2) new_cpc_black,
ROUND(`Mstrcpc`,2) cpc_color,
ROUND(`NewColorCPC`,2) new_cpc_color,
`BDate` meter_begin_date,
`EDate` meter_end_date,
`Begin` meter_begin_read_black,
`End` meter_end_read_black,
`MstrBegin` meter_begin_read_color,
`MstrEnd` meter_end_read_color,

`Comments` device_comments,
#(CASE WHEN `Comments` LIKE("%'%") THEN NULL ELSE `Comments` END) device_comments,
(CASE lease WHEN 'Owned' Then 'O' WHEN 'Leased' THEN 'L'  WHEN 'Rental/Loaner' THEN 'B' ELSE NULL END) lease_own,
ROUND(`Black_Vendor_cpc`,2) cpc_black_vendor, 
ROUND(`Color_Vendor_cpc`,2) cpc_color_vendor,
ROUND(`New_Blk_Ven_cpc`,2) new_cpc_black_vendor,
ROUND(`New_Color_Ven_cpc`,2) new_cpc_color_vendor,
`txtReplacedMachine` replacement_for,
NULL last_meter_import,
NULL pre_upgrade_data,
(CASE WHEN `Created` IS NULL THEN NOW() ELSE `Created` END) created_date,
(Case `Created By` WHEN 'Bob Dutil' THEN 141 WHEN 'Alex Webster' THEN 2
	WHEN 'Skip Tilton' THEN 73 WHEN 'Pam Weed' THEN 142 WHEN 'James Cartwright' THEN 1229 WHEN 'Jamin Tilton' THEN 648 WHEN 'Heidi Tilton' THEN 35 ELSE 1271 END)  created_by,
(CASE WHEN `Modified` IS NULL THEN NOW() ELSE `Modified` END) modified_date,
 
(Case `Modified By` WHEN 'Bob Dutil' THEN 141 WHEN 'Alex Webster' THEN 2
	WHEN 'Skip Tilton' THEN 73 WHEN 'Pam Weed' THEN 142 WHEN 'James Cartwright' THEN 1229 WHEN 'Jamin Tilton' THEN 648 WHEN 'Heidi Tilton' THEN 35 ELSE 1271 END) modified_by
FROM Machine_Archive AS machine_archive
WHERE `ID` NOT IN(25427, 34463)
AND modified > '2016-06-30 23:59:59'; /*LIMIT RECORDS TO 7 YEARS*/







/**********************/
SELECT * FROM Machine_Archive WHERE room_name LIKE("%'%")  AND modified > '2016-06-30 23:59:59'; #3501 - -  913 AND modified > '2016-06-30 23:59:59';
SELECT * FROM Machine_Archive WHERE `Comments` LIKE("%'%")  AND modified > '2016-06-30 23:59:59'; #331 - - 25
SELECT * from Machine_Archive WHERE length(room_name) = 83  AND modified > '2016-06-30 23:59:59';  #1 - - 1 #ID = 34463
SELECT * FROM `Machine_Archive` WHERE org_id_ma is null  AND modified > '2016-06-30 23:59:59'; #4- - 0