information_schema queries
SELECT * FROM `ROUTINES` WHERE routine_definition LIKE '%machines_first_stage_reformat%';
SELECT * FROM `REFERENTIAL_CONSTRAINTS` WHERE referenced_table_name = 'machines_first_stage_reformat';
SELECT column_name FROM `COLUMNS` WHERE table_name = 'organization';

Do not need billing_data table or the data in it because data comes from current_devices. billing_report_todo and billing_report_completed were combined with the additional column as a flag to mark whether billing_report_completed or not. billing_completed CHAR(1),


CREATE TABLE rooms
(room_id INT(11) AUTO_INCREMENT PRIMARY KEY,
org_id INT(11),
bldg_id INT(11),
floor_num INT(11) DEFAULT NULL,
room_name VARCHAR(250));

/*When Machine_Archive is in test db, insert records from it also. */
INSERT INTO rooms
(org_id, bldg_id, room_name)
SELECT org_id_ma, building_id_ma, room_name FROM spcsta5_floorplans.current_devices
GROUP BY org_id_ma, building_id_ma, room_name





CREATE TABLE billing_data
(bd_id INT(15) AUTO_INCREMENT PRIMARY KEY,
billing_grp_id INT(11),
org_id INT(11),
building_id INT(11),
room_id INT(11),
dept_id INT(11),
vendor_id INT(11),
serial_number VARCHAR(25),
model_id INT(11),
projected_vol_black INT(15),
projected_vol_color INT(15),
billed_projected_vol_black INT(15),
billed_projected_vol_color INT(15),
cpc_black DECIMAL(8, 6),
cpc_color DECIMAL(8, 6),
vendor_cpc_black DECIMAL(8, 6),
vendor_cpc_color DECIMAL(8, 6),
begin_date datetime,
end_date datetime,
comments VARCHAR(2000),
school_year INT(2),
replacement_for VARCHAR(25),
close_out_date DATE,
actual_end_meter_black INT(15),
actual_end_meter_color INT(15),
client_black_charge DECIMAL(8, 6),
client_color_charge DECIMAL(8, 6),
vendor_black_charge DECIMAL(8, 6),
vendor_color_charge DECIMAL(8, 6),
upgrade_mach CHAR(1),
billing_cycle INT(2),
billing_type CHAR(1),
created_date datetime,
modified_date datetime,
modified_by INT(11));


INSERT INTO billing_data(
bd_id,
billing_grp_id,
org_id,
building_id,
room_id,
dept_id,
vendor_id,
serial_number,
model_id,
projected_vol_black,
projected_vol_color,
billed_projected_vol_black,
billed_projected_vol_color,
cpc_black,
cpc_color,
vendor_cpc_black,
vendor_cpc_color,
begin_date,
end_date,
comments,
school_year,
replacement_for,
close_out_date, /*There are only 54 values for this in prod, so I allowed the truncate to DATE vs datetime.*/
client_black_charge,
client_color_charge,
vendor_black_charge,
vendor_color_charge,
upgrade_mach,
billing_cycle,
billing_type,
created_date,
modified_date,
modified_by)

SELECT bda_id,
billing_gp_id,
org_id_ma,
building_id_ma,

(SELECT rms.room_id FROM rooms rms
INNER JOIN spcsta5_floorplans.billing_data_archive bda
WHERE rms.org_id = bda.org_id_ma AND rms.bldg_id = bda.building_id_ma AND rms.room_name = bda.room_name)  room_id,

dept_id_ma,
vendor_id,
serial_number,
model_id,
projected_volume_black,
projected_volume_color,
billed_projected_volume_black,
billed_projected_volume_color,
cpc_black,
cpc_color,
vendor_cpc_black,
vendor_cpc_color,
begin_date,
end_date,
comments,
school_year,
replacement,
close_out_date,
client_black_charge,
client_color_charge,
vendor_black_charge,
vendor_color_charge,

(CASE WHEN upgrade_mach = "Yes" THEN "Y" ELSE "N" END) upgrade_mach,
billing_cycle,
(CASE billing_type WHEN "Client" THEN "C" WHEN "Vendor" THEN "V" ELSE "" END) billing_type,
date_created,
NOW() modified_date,
1271


FROM spcsta5_floorplans.billing_data_archive;


#SELECT * FROM billing_data_archive WHERE billing_type = "Client"; #22093
#SELECT * FROM billing_data_archive WHERE billing_type = "Vendor"; #26080
#SELECT * FROM billing_data_archive WHERE billing_type IS NULL OR billing_type = ""; #109
#SELECT DISTINCT billing_type FROM billing_data_archive;
#SELECT * FROM billing_data_archive;#48282