SELECT * FROM Y2019_floorplan_machines WHERE floorplan_id = 179;
    
    /*SELECT 	fpm.id as machine_id, fpm.fmaudit_id, fpm.model_id, fpm.floorplan_id, fpm.room_number, fpm.5_year_id, fpm.cpc_black, fpm.cpc_color, 
					fpm.commencement_date, fpm.commencement_black_meter, fpm.commencement_color_meter, fpm.x_position as x_pos, fpm.y_position as y_pos,
					fpm.budgeted_blk as budgeted_black, fpm.budgeted_color, fpm.serial_number, fpm.ip_address, fpm.vendor_device_id, fpm.under_contract,
					fpm.is_proposed, fpm.present_floorplan_id, fpm.present_room_number, fpm.local_connection, fpm.present_local_connection,
					fpm.present_serial_number, fpm.savedname, fpm.save_name_id, fpm.new_vendor_device_id,
					fpm.present_x_position as present_x_pos, fpm.present_y_position as present_y_pos,
					fpm.closeout_date,
					ma.make, ma.model, ma.is_color, coalesce(ma.machine_image,'MissingDeviceImage.png') as machine_image, ma.features,
					ma_pr.make as present_make, ma_pr.model as present_model, ma_pr.is_color as present_is_color, 
					coalesce(ma_pr.machine_image,'MissingDeviceImage.png') as present_machine_image, ma_pr.features as present_features,
					fp.id as floorplan_id, fp.building_id, fp.floor_number,
					bd.id as build_id, bd.building_name,
					org.id as org_id, org.org_name, org.org_type_id, org.commencement_date as org_commencement_date, DisplayMeterData, MeterDataFeed, org_message,
					org.cs_pres_black_rate, org.cs_pres_color_rate, cs_prop_black_w_service, cs_prop_black_wo_service, 	cs_prop_color_w_service, cs_prop_color_wo_service,
					coalesce(mt.icon_type,'Unknown') as icon_type, coalesce(mt.type_name,'Unknown') as type_name, mt.type as machine_type, mt.CoveredType,
					coalesce(mt_pr.icon_type,'Unknown') as present_icon_type, coalesce(mt_pr.type_name,'Unknown') as present_type_name,
					me.black_meter, me.color_meter, me.date_timestamp as meter_timestamp,
					me_first.black_meter as first_black_meter, me_first.color_meter as first_color_meter, me_first.date_timestamp as first_meter_timestamp,
					ms.toner, ms.service_needed,
					dp.dept_name,
					orgcs.org_id as orgcs_id,
					orgcs.org_present_black_rate, orgcs.org_present_color_rate, orgcs.org_proposed_black_without_service, orgcs.org_proposed_color_with_service,
					orgcs.org_proposed_black_with_service, orgcs.org_proposed_color_without_service,
					fpmc.fmc_costSavingsWithoutService, fpmc.fmc_costSavingsWithService
			FROM Y2019_floorplan_machines fpm
			LEFT OUTER JOIN Y2019_machines ma       on fpm.model_id = ma.id
			LEFT OUTER JOIN Y2019_machines ma_pr    on fpm.present_model_id = ma_pr.id
			LEFT OUTER JOIN Y2019_floorplans fp     on fpm.floorplan_id = fp.id
			LEFT OUTER JOIN Y2019_buildings bd      on fp.building_id = bd.id
			LEFT OUTER JOIN Y2019_organization org  on bd.org_id = org.id
			LEFT OUTER JOIN Y2019_machine_types mt  on ma.model_type = mt.id
			LEFT OUTER JOIN Y2019_machine_types mt_pr on ma_pr.model_type = mt_pr.id
			LEFT OUTER JOIN Y2019_meter me       	on fpm.id = me.machine_id
                AND me.date_timestamp = (SELECT max(i.date_timestamp) FROM Y2019_meter i
                                         	WHERE i.machine_id = fpm.id and black_meter > 0 and date_timestamp < date_format(NOW(),'Y/m/d H:i:s') and date_timestamp > 	date_format('2018/02/16','Y/m/d H:i:s'))
			LEFT OUTER JOIN Y2019_meter me_first    on fpm.id = me_first.machine_id
            	AND me_first.date_timestamp = (SELECT min(k.date_timestamp) FROM Y2019_meter k WHERE k.machine_id = fpm.id and k.black_meter > 0 and k.date_timestamp < date_format(NOW(),'Y/m/d H:i:s') and k.date_timestamp >= date_format('2018/02/16','Y/m/d H:i:s'))
			LEFT OUTER JOIN Y2019_machine_status ms on fpm.id = ms.machine_id
            	AND ms.date_timestamp = (select max(j.date_timestamp) from Y2019_machine_status j where j.machine_id = fpm.id
                                         and date_timestamp < date_format(NOW(),'Y/m/d H:i:s') and date_timestamp > date_format('2018/02/16','Y/m/d H:i:s'))
			LEFT OUTER JOIN Y2019_department dp     on fpm.dept_id = dp.id
			LEFT OUTER JOIN Y2019_org_service_rates orgcs on org.id = orgcs.org_id
			LEFT OUTER JOIN floorplan_machines_calculations fpmc on fpm.id = fpmc.fmc_id
			where fpm.id = 3394;*/
			


/************************************************************ BEGIN WIP**********************************************************/			
Reducing query to only those columns needed for the gauges page			
/*SELECT fpm.id as machine_id,
		fpm.model_id,
		fpm.floorplan_id, 
		fpm.commencement_date,
		fpm.commencement_black_meter,
		fpm.commencement_color_meter,
		fpm.budgeted_blk as budgeted_black,
		fpm.budgeted_color,
		fpm.serial_number,
		fpm.under_contract,
		fpm.is_proposed,
		fpm.present_floorplan_id,
		fpm.present_serial_number,
		fpm.closeout_date,
		ma.make,
		ma.model,
		ma.is_color,
		ma.features,		
		fp.building_id,
		bd.building_name,
		org.id as org_id,
		org.org_name, org.commencement_date as org_commencement_date,
		cs_prop_black_w_service,
		cs_prop_black_wo_service,
		cs_prop_color_w_service,
		cs_prop_color_wo_service,		
		me.black_meter, me.color_meter, me.date_timestamp as meter_timestamp,
		me_first.black_meter as first_black_meter,
		me_first.color_meter as first_color_meter,
		me_first.date_timestamp as first_meter_timestamp,
		ms.toner,
		ms.service_needed,		
		bd.org_id		

FROM Y2019_floorplan_machines fpm
INNER JOIN Y2019_machines ma ON fpm.model_id = ma.id
INNER JOIN Y2019_floorplans fp ON fpm.floorplan_id = fp.id
INNER JOIN Y2019_buildings bd ON fp.building_id = bd.id
INNER JOIN Y2019_organization org ON bd.org_id = org.id

LEFT JOIN Y2019_meter me ON fpm.id = me.machine_id /*********************These may not be = ************************/
	AND me.date_timestamp = (SELECT MAX(i.date_timestamp)
								FROM Y2019_meter i
								WHERE i.machine_id = fpm.id
								and black_meter > 0
								and date_timestamp < NOW()
								and date_timestamp > '2018/07/01')
								
	/*AND me.date_timestamp = (SELECT max(i.date_timestamp) FROM Y2019_meter i WHERE i.machine_id = fpm.id
	and black_meter > 0 and date_timestamp < date_format($session_datetime,'Y/m/d H:i:s')
	and date_timestamp > date_format($startingDate,'Y/m/d H:i:s'))*/

LEFT JOIN Y2019_meter me_first ON fpm.id = me_first.machine_id
	AND me_first.date_timestamp = (SELECT MIN(k.date_timestamp)
									FROM Y2019_meter k
									WHERE k.machine_id = fpm.id
									and k.black_meter > 0
									and k.date_timestamp < NOW()
									and k.date_timestamp >= '2018/07/01')
									
LEFT JOIN Y2019_machine_status ms ON fpm.id = ms.machine_id
	AND ms.date_timestamp = (SELECT MAX(j.date_timestamp)
								from Y2019_machine_status j
								where j.machine_id = fpm.id
								and date_timestamp < NOW()
								and date_timestamp > '2018/07/01')

where fpm.id = 3394;
*/
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
/*For the above query, the 3394 represents a device_id, which is retrieved with this query
SELECT fpm.id as machine_id, fpm.is_proposed,
		bd.id as build_id, 
		fp.id as floorplan_id
			FROM ".$table_year."organization org  
			LEFT OUTER JOIN ".$table_year."buildings bd ON org.id = bd.org_id
			LEFT OUTER JOIN ".$table_year."floorplans fp ON bd.id = fp.building_id
			LEFT OUTER JOIN ".$table_year."floorplan_machines fpm ON fp.id = fpm.floorplan_id
			where org.id = :org_id
			order by bd.building_name, fpm.5_year_id
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&			
			
#####################################################################			
/*Same query, just modified and 2 additional queries for testing*/
SELECT bd.building_name, fpm.5_year_id, fpm.id as machine_id, fpm.is_proposed, #182 rows
		bd.id as build_id, 
		fp.id as floorplan_id
			FROM Y2019_organization org  
			INNER JOIN Y2019_buildings bd ON org.id = bd.org_id
			INNER JOIN Y2019_floorplans fp ON bd.id = fp.building_id
			InnER JOIN Y2019_floorplan_machines fpm ON fp.id = fpm.floorplan_id
			where org.id = 4
			order by bd.building_name, fpm.5_year_id;
            
SELECT * FROM Y2019_floorplans WHERE building_id = 180; #4 floorplans
SELECT * FROM Y2019_floorplan_machines WHERE floorplan_id IN(54, 56, 67, 64); #107 machines
#####################################################################

/*For the above query, from date_calc.inc.php, $startingDate = 
// Get the fiscal starting date (7/1/yyyy)
	if ($today['mon'] <= 6) {
		$tempDate = '07/01/'.($currentYear-1);
		$startingDate = new DateTime($tempDate);
	} else {
		$tempDate = '07/01/'.($currentYear);
		$startingDate = new DateTime($tempDate);
	}

	
SELECT MAX(date_timestamp) dt, serial_number, toner, service_needed FROM Y2019_machine_status
WHERE serial_number IN(SELECT SerialNumber FROM Machine_Archive WHERE org_id_ma = 4 AND school_year = 19)
GROUP BY serial_number;
/************************************************************ END WIP**********************************************************/

SELECT * FROM Y2019_machine_status
WHERE serial_number in (SELECT SerialNumber FROM Machine_Archive WHERE org_id_ma = 4 AND school_year = 19);



/*Total Contract Devices - this works on all except org_id 4 year 2020, because Machine_Archive has no records for this org and year*/
SELECT * FROM Y2019_floorplan_machines WHERE serial_number in
(SELECT SerialNumber FROM Machine_Archive WHERE org_id_ma = 4 AND school_year = 19)
AND model_id != 790 /*790 = closeout*/
AND save_name_id IS NOT NULL;

/*Substituting current_devices for Machine_Archive and changing year to 2020 as in query below, does return what stardoc has*/
SELECT * FROM Y2020_floorplan_machines WHERE serial_number in
(SELECT SerialNumber FROM current_devices WHERE org_id_ma = 4)
AND model_id != 790 /*790 = closeout*/
AND save_name_id IS NOT NULL;

/*Or I can use this query pulling the SerialNums from the tblWhatif to return the same records as Stardoc*/
SELECT * FROM Y2019_floorplan_machines WHERE serial_number in
(SELECT NewSerialNum FROM tblWhatif WHERE save_name_id = (SELECT id FROM FYER_Group WHERE FyerOrg_id = 4) AND proposed_model_id != 790)
AND model_id != 790 /*790 = closeout*/
AND save_name_id IS NOT NULL;

/*Or I can use this query, same as above using new column names, pulling the SerialNums from the tblWhatif to return the same records as Stardoc*/
SELECT * FROM y2019_floorplan_machines
	WHERE serial_number in
	(SELECT new_serial_number FROM tbl_what_ifs
		WHERE fyer_group_id = (SELECT id FROM fyer_group WHERE org_id = 4)
		AND proposed_model_id != 790)
AND machines_id != 790 /*790 = closeout*/
AND fyer_group_id IS NOT NULL;



/*Total Non-Contract Devices - This works well on all years, even 2020*/
SELECT fpm.*
FROM Y2013_floorplan_machines fpm
INNER JOIN Y2013_floorplans fp ON fpm.floorplan_id = fp.id #(changed from full outer join)
INNER JOIN Y2013_buildings bd ON fp.building_id = bd.id #(changed from full outer join)
WHERE save_name_id IS NULL #savedname IN('**Created from FM Audit Feed**', '**CREATED FROM FMAUDIT FEED**')
/*AND model_id = 790 (This returns 0 records for this floorplan. The model_id values are all different.)*/
AND bd.org_id = 4;

/*Discuss with Bob and show the comparisons with errant save_name_id values*/
		/*select * from `Y2019_floorplan_machines` as `fpm`
		#left join `Y2019_machine_status` as `ms` on `ms`.`serial_number` = `fpm`.`serial_number`
		left join (select max(date_timestamp) as dt, serial_number
					from `Y2019_machine_status`
					group by `serial_number`) as `serNumDate`
			on `fpm`.`serial_number` = `serNumDate`.`serial_number`
			#and `ms`.`date_timestamp` = `serNumDate`.`dt`
		left join `Y2019_machine_status` as `ms` on `ms`.`serial_number` = `fpm`.`serial_number`
			AND ms.date_timestamp = serNumDate.dt
		inner join `floorplans` as `fp` on `fp`.`id` = `fpm`.`floorplan_id`
		inner join `machines` as `mach` on `mach`.`id` = `fpm`.`model_id`
		#inner join `buildings` as `bldg` on `bldg`.`id` = `fp`.`building_id`
		#inner join `organization` as `orgs` on `orgs`.`id` = `bldg`.`org_id`
		 where `fpm`.`serial_number` in (select `SerialNumber`
										from `Machine_Archive`
										where `org_id_ma` = 96 and `school_year` = 19)
					 and `fpm`.`save_name_id` is null;*/
					 
					 


		/*SELECT fpm.*
		FROM Y2019_floorplan_machines fpm
		INNER JOIN Y2019_floorplans fp ON fpm.floorplan_id = fp.id #(changed from full outer join)
		INNER JOIN Y2019_buildings bd ON fp.building_id = bd.id #(changed from full outer join)
		WHERE save_name_id IS NULL #savedname IN('**Created from FM Audit Feed**', '**CREATED FROM FMAUDIT FEED**')
		#AND model_id = 790 (This returns 0 records for this floorplan. The model_id values are all different.)
		AND bd.org_id = 96;*/


		/*SELECT * FROM Y2019_floorplan_machines fpm
		inner join `Y2019_machine_status` as `ms` on `ms`.`serial_number` = `fpm`.`serial_number`
		WHERE fpm.serial_number IN ('NUF2800120', 'LVK4411542', 'LVK4410440');*/

		SELECT * FrOM `Y2019_machine_status` WHERE serial_number IN ('NUF2800120', 'LVK4411542'); #, 'LVK4410440'
		SELECT * FROM `Y2019_floorplan_machines` WHERE serial_number IN ('NUF2800120', 'LVK4411542');
		SELECT * FROM `Y2019_floorplan_machines` WHERE save_name_id = 1653;
		SELECT * FROM `tblWhatIf` WHERE save_name_id = 1653;
		SELECT * FROM `tblWhatIf` WHERE org_id = 96 ORDER BY save_name_id;









/*******************************************************/
/*toner alert total - This works*/
SELECT * FROM Y2019_machine_status ms
WHERE (date_timestamp, serial_number) IN(SELECT MAX(date_timestamp) dt, serial_number FROM Y2019_machine_status GROUP BY serial_number)
AND ms.serial_number IN(SELECT SerialNumber FROM Machine_Archive WHERE org_id_ma = 4
                       AND school_year = 19)
AND toner = 1;


/*same, but with new column names and reversed criteria to match index*/
SELECT * FROM Y2019_machine_status ms
WHERE (serial_number, created_date) IN(SELECT serial_number, MAX(created_date) dt FROM Y2019_machine_status GROUP BY serial_number)
AND ms.serial_number IN(SELECT serial_number FROM machine_archive WHERE org_id = 4
                       AND school_year = 19)
AND toner = 1;
/*******************************************************/


/*******************************************************/
/*service needed total - This works*/
SELECT * FROM Y2019_machine_status ms
WHERE (serial_number, created_date) IN(SELECT serial_number, MAX(created_date) dt FROM Y2019_machine_status GROUP BY serial_number)
AND ms.serial_number IN(SELECT serial_number FROM machine_archive WHERE org_id = 4
                       AND school_year = 19)
AND service_needed = 1;
/*******************************************************/



/*******************************************************/
/*Last Synch Date - using serial_number instead of machine_id. This works good*/
SELECT mtr.serial_number, MAX(mtr.date_timestamp) FROM Y2019_meter mtr
WHERE serial_number IN(SELECT serial_number
                       FROM Y2019_floorplan_machines fm
                       WHERE fm.floorplan_id IN (SELECT fp.id
                                                 from floorplans fp
                                                 INNER JOIN buildings bldg
                                                 ON bldg.id = fp.building_id
                                                 WHERE bldg.org_id = 4))
AND mtr.black_meter > 0
AND mtr.serial_number IS NOT NULL
AND mtr.serial_number != '';
//AND mtr.date_timestamp LIKE('%2017%') - This makes it much faster because it skips 2013-2016 records (millions)
//GROUP BY mtr.serial_number;


/*This works better with the criteria for serial_number moved up into the subjoin
SELECT MAX(mtr.date_timestamp) FROM Y2017_meter mtr
WHERE serial_number IN(SELECT serial_number
                       FROM Y2017_floorplan_machines fm
                       WHERE fm.floorplan_id IN (SELECT fp.id
                                                 from Y2017_floorplans fp
                                                 INNER JOIN Y2017_buildings bldg
                                                 ON bldg.id = fp.building_id
                                                 WHERE bldg.org_id = 4)
                       AND serial_number is not null
                       AND serial_number != '')
AND mtr.black_meter > 0


2/27/2021

#star2020
select max(created_date) as dt from y2019_meter as mtr
where mtr.serial_number in (select fm.serial_number
                            from y2019_floorplan_machines as fm
                            inner join floorplans as fp on fp.id = fm.floorplans_id
                            inner join buildings as bldg on bldg.id = fp.buildings_id
                            where bldg.organizations_id = 15
                            and fm.serial_number is not null
                            and fm.serial_number != '');

#floorplans                            
select max(date_timestamp) as dt from Y2019_meter as mtr
where mtr.serial_number in (select fm.serial_number
                            from Y2019_floorplan_machines as fm
                            inner join floorplans as fp on fp.id = fm.floorplan_id
                            inner join buildings as bldg on bldg.id = fp.building_id
                            where bldg.org_id = 15
                            and fm.serial_number is not null
                            and fm.serial_number != '');

/*******************************************************/


//$lastSynchDate = Replaced by query above, but /*This is faster because of pk index. Serial_number is not a pk index*/            

SELECT 	DATE_FORMAT(max( mtr.created_date),'%m/%d/%Y') as created_date
			FROM Y2019_floorplan_machines fpm
			LEFT OUTER JOIN Y2019_floorplans fp ON fpm.floorplans_id = fp.id
			LEFT OUTER JOIN Y2019_buildings bd ON fp.buildings_id = bd.id
			LEFT OUTER JOIN Y2019_organizations org ON bd.organizations_id = org.id
			LEFT OUTER JOIN Y2019_meters mtr ON fpm.id = mtr.machines_id
            	AND mtr.created_date = (SELECT max( i.created_date) FROM Y2019_meters i WHERE i.machines_id = fpm.id and black_meter > 0)
			where org.id =17;


/*Works, but takes 2 mins */
/*SELECT * FROM machine_status ms
INNER JOIN(SELECT MAX(date_timestamp) dt, serial_number FROM machine_status GROUP BY serial_number) serNums
ON serNums.serial_number = ms.serial_number
WHERE ms.serial_number IN(SELECT SerialNumber FROM current_devices WHERE org_id_ma = 4)
AND ms.date_timestamp = serNums.dt;*/

/*Works, and takes 32 secs. After deleting prior years data the time to run was 12 secs. Need to sum toner ans service_needed*/
SELECT * FROM Y2019_machine_status ms
WHERE (date_timestamp, serial_number) IN(SELECT MAX(date_timestamp) dt, serial_number FROM Y2019_machine_status GROUP BY serial_number)
AND ms.serial_number IN(SELECT SerialNumber FROM current_devices WHERE org_id_ma = 4);

			
			
#$not_under_contact_count = (Non-Contracted Devices)- - Modified for new col and table names - - 'Y' means yes, 'U' undecided?
SELECT count(*) as device_count
			FROM Y2019_floorplan_machines fpm
			LEFT OUTER JOIN Y2019_floorplans fp     on fpm.floorplans_id = fp.id
			LEFT OUTER JOIN Y2019_buildings bd      on fp.buildings_id = bd.id
			LEFT OUTER JOIN Y2019_organizations org on bd.organizations_id = org.id
			where ((is_proposed = 'Y' and under_contract = 'N') or under_contract = 'U') and org.id = 17;