#Equip Health Status Query WIP
SELECT COUNT(ma.org_id_ma), ma.model_id, mach.model_id, mach.model_type,
mt.id, mt.type_name
FROM Machine_Archive ma
INNER JOIN machines mach ON mach.model_id = ma.model_id
INNER JOIN machine_types mt ON mt.id = mach.model_type
WHERE ma.org_id_ma = 4
AND mt.id IN(1, 15, 20);




SELECT * FROM(
(SELECT COUNT(ma.org_id_ma) blk_photocopy
FROM Machine_Archive ma
INNER JOIN machines mach ON mach.model_id = ma.model_id
INNER JOIN machine_types mt ON mt.id = mach.model_type
WHERE ma.org_id_ma = 37
AND mt.id IN(1, 15, 20)) bk,




SELECT blk_photocopy, col_photocopy, blk_network, col_network, rmv_frm_svc  FROM(
(SELECT COUNT(ma.org_id_ma) blk_photocopy
FROM Machine_Archive ma
INNER JOIN machines mach ON mach.model_id = ma.model_id
INNER JOIN machine_types mt ON mt.id = mach.model_type
WHERE ma.org_id_ma = 37
AND mt.id IN(1, 15, 20)) bk,


(SELECT COUNT(ma.org_id_ma) col_photocopy
FROM Machine_Archive ma
INNER JOIN machines mach ON mach.model_id = ma.model_id
INNER JOIN machine_types mt ON mt.id = mach.model_type
WHERE ma.org_id_ma = 37
OR mt.id IN(6, 16, 21, 22)) col,

(SELECT COUNT(ma.org_id_ma) blk_network
FROM Machine_Archive ma
INNER JOIN machines mach ON mach.model_id = ma.model_id
INNER JOIN machine_types mt ON mt.id = mach.model_type
WHERE ma.org_id_ma = 37
AND mt.id IN(10)) bk_net,

(SELECT COUNT(ma.org_id_ma) col_network
FROM Machine_Archive ma
INNER JOIN machines mach ON mach.model_id = ma.model_id
INNER JOIN machine_types mt ON mt.id = mach.model_type
WHERE ma.org_id_ma = 37
AND mt.id IN(11)) col_net,

(SELECT COUNT(ma.org_id_ma) rmv_frm_svc
FROM Machine_Archive ma
INNER JOIN machines mach ON mach.model_id = ma.model_id
INNER JOIN machine_types mt ON mt.id = mach.model_type
WHERE ma.org_id_ma = 37
AND ma.Traded = "Y") rem_service)

/*off warranty - this is only the amount of years from intro to now. Do the calc
	in code.  A machine is out of warranty if the yrs_in_wrnty >= 10*/
SELECT FLOOR(DATEDIFF(NOW(), mach.intro)/365) yrs_in_wrnty, mach.id,
	SUM(CASE WHEN FLOOR(DATEDIFF(NOW(), mach.intro)/365) >= 10 THEN 1 ELSE 0 END) oow,
    SUM(CASE WHEN FLOOR(DATEDIFF(NOW(), mach.intro)/365) < 10 AND FLOOR(DATEDIFF(NOW(), mach.intro)/365) >= 7 THEN 1 ELSE 0 END) eow,	
	org.lastUpdate commence_date,
	org.EndContractDate,
	org.lease_end_date,
    NULL remain_lease_pmts,  
	(CASE WHEN org.PrintMgmtSoftwareInstalled = 0 THEN "No" ELSE "Yes" END) print_soft_inst,	
    (CASE WHEN org.LENPContractSigned = 0 THEN "No" ELSE "Yes" END) cont_signed
FROM Machine_Archive ma
INNER JOIN machines mach ON mach.model_id = ma.model_id
INNER JOIN machine_types mt ON mt.id = mach.model_type
INNER JOIN organization org ON org.id = ma.org_id_ma
WHERE ma.org_id_ma = 4
AND ma.school_year = 18
ORDER BY 1;

SELECT DISTINCT DATE_FORMAT(intro, "%Y-%m") FROM machines;

/*units approaching end of warranty*/
/*Use the query for yrs_in_wrnty to calculate
if yrs_in_wrnty < 10  and >= 7 the machine is approaching end of warranty.*/

/*number of annual pmts left on lease is calculated using the lease_end_date, from the organization
	table, and NOW(). Use the off warranty query to get this value*/

/*commencement date is the lastUpdate in the organization table. Use the off warranty query to get this value*/	
/*warranty and service contracts expire is the EndContractDate from the organization table. Use the off warranty query to get this value */
/*print management software loaded is the PrintMgmtSoftwareInstalled value in organization. The values
	are 1, -1, 0.  Use this to determine the correct answer in code. If 0 answer is No if 1, -1 it is yes*/
/*contract signed is the LENPContractSigned value from the organization table. The values
	are 1, -1, 0.  Use this to determine the correct answer in code. If 0 answer is No if 1, -1 it is yes*/