/*billing_data*/

/*NOTE: This replaces billing_data_archive. We do not need the billing_data table
	or the data in it because that data comes from current_devices.*/

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,
created_by,
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,
created_date,
1271,
NOW() modified_date,
1271

FROM spcsta5_floorplans.billing_data_archive;













/*billing_reports*/
/******* INSERT data from billing_report_todo ***/
INSERT INTO billing_reports(
billing_grp_id,
org_id,
vendor_id,
school_year,
sort_by,
billing_cycle,
projected,
archived_records,
upgrade_client,
start_date,
billing_type,
billing_report_completed,
notes,
created_date,
created_by,
modified_date,
modified_by)

SELECT id
org_id,
vendor_id,
school_year,

(CASE sort_by WHEN "Building" THEN "B" WHEN "Department" THEN "D" ELSE "" END) sort_by,
CAST(billing_cycle AS UNSIGNED) billing_cycle,
(CASE projected WHEN "Yes" THEN "Y" ELSE "N" END) projected,
(CASE archived_records WHEN "Yes" THEN "Y" ELSE "N" END) archived_records,
(CASE upgrade_client WHEN "Yes" THEN "Y" ELSE "N" END) upgrade_client,
start_date,
(CASE billing_type WHEN "Client" THEN "C" WHEN "Vendor" THEN "V" ELSE NULL END) billing_type,
"N", #from billing_report_todo
notes,
NOW() created_date,
1271 created_by,
NOW() modified_date,
1271 modified_by

FROM spcsta5_floorplans.billing_report_todo;

/******** INSERT data from billing_report_completed ***/

INSERT INTO billing_reports(
billing_grp_id,
org_id,
vendor_id,
school_year,
sort_by,
billing_cycle,
projected,
archived_records,
upgrade_client,
start_date,
billing_type,
billing_report_completed,
created_date,
created_by,
modified_date,
modified_by)

SELECT id,
org_id,
vendor_id,
school_year,
(CASE sort_by WHEN "Building" THEN "B" WHEN "Department" THEN "D" ELSE "" END) sort_by,
CAST(billing_cycle AS UNSIGNED) billing_cycle,
(CASE projected WHEN "Yes" THEN "Y" ELSE "N" END) projected,
(CASE archived_records WHEN "Yes" THEN "Y" ELSE "N" END) archived_records,
(CASE upgrade_client WHEN "Yes" THEN "Y" ELSE "N" END) upgrade_client,
start_date,
(CASE billing_type WHEN "Client" THEN "C" WHEN "Vendor" THEN "V" ELSE NULL END) billing_type,
"Y", #from billing_report_completed
NOW() created_date,
1271 created_by,
NOW() modified_date,
1271 modified_by

FROM spcsta5_floorplans.billing_report_completed;





/*rooms*/
SELECT * FROM (SELECT org_id_ma, building_id_ma, room_name
FROM spcsta5_floorplans.current_devices
GROUP BY org_id_ma, building_id_ma, room_name
UNION

SELECT org_id_ma, building_id_ma, room_name
FROM spcsta5_floorplans.Machine_Archive
GROUP BY org_id_ma, building_id_ma, room_name) temp;

UPDATE rooms
SET created_date = NOW(), created_by = 1271, modified_date = NOW90, modified_by = 1271;



/*contacts*/
INSERT INTO contacts(
contact_id,
building_id,
org_id,
last_name,
first_name,
email_address,
job_title,
decision_maker,
notes,
it_email_cont,
created_date,
modified_date,
modified_by)

SELECT `ID`,
building_id,
org_id_contact,
`Last Name`,
`First Name`,
`E-mail Address`,
`Job Title`,
(CASE decision_maker WHEN "Yes" THEN "Y" WHEN "No" THEN "N" WHEN "n" THEN "N" ELSE NULL END) decision_maker,	
`Notes`,
`it_email_cont`,
cdts,
NOW(),
1271 /*Sallie id*/

FROM spcsta5_floorplans.tblContacts;