prebill queries modified for new tables

/*SELECT org.org_name, looksy.school_year, bd.billing_type, lookbill.billing_cycle_name,
        tbc.first_name contactName, vendOrg.org_name vendor_org

        FROM billing_data bd
        INNER JOIN organization org ON bd.org_id = org.org_id
        
        INNER JOIN billing_reports brtd ON brtd.org_id = org.org_id
        INNER JOIN organization vendOrg ON vendOrg.org_id = bd.vendor_id
        INNER JOIN lkp_school_year looksy ON looksy.yr_id = brtd.school_year
        INNER JOIN lkp_billing_cycle lookbill ON lookbill.billing_cycle_id = bd.billing_cycle
        LEFT JOIN contacts tbc ON tbc.org_id = bd.org_id AND tbc.decision_maker = 'Y'
        WHERE bd.billing_grp_id = 263
        GROUP BY org.org_name;*/
		
		
SELECT org.org_name, looksy.school_year, bd.billing_type, lookbill.billing_cycle_name,
        tbc.first_name contactName, vendOrg.org_name vendor_org

        FROM billing_data bd
        INNER JOIN organization org ON bd.org_id = org.org_id
        
        #INNER JOIN billing_report_todo brtd ON brtd.org_id = org.org_id
        INNER JOIN current_devices cd ON cd.serial_number = bd.serial_number
        LEFT JOIN organization vendOrg ON vendOrg.org_id = bd.vendor_id
        INNER JOIN lkp_school_year looksy ON looksy.yr_id = bd.school_year
        INNER JOIN lkp_billing_cycle lookbill ON lookbill.billing_cycle_id = bd.billing_cycle
        LEFT JOIN contacts tbc ON tbc.org_id = bd.org_id AND tbc.decision_maker = 'Y'
        WHERE bd.billing_grp_id = 1016
        GROUP BY org.org_name;
		
		
/******************** GET DATA FOR BLACK AND COLOR Lines 278-326 ***********/
if($billing_type == 'Vendor'){
    $qA = ' SUM(bd.projected_vol_black * bd.vendor_cpc_black) proj_bk_chg,
        SUM(bd.billed_projected_vol_black * bd.vendor_cpc_black) pb_bk_chg,
        SUM(bd.projected_vol_color * bd.vendor_cpc_color) proj_col_chg,
        SUM(bd.billed_projected_vol_color * bd.vendor_cpc_color) pb_col_chg';
        
    /*If report is for a vendor, ONLY machines owned by that vendor should be in report */
    $qB = ' AND vendOrg.org_id = ? ';
}
else{
    $qA = ' SUM(bd.projected_vol_black * bd.cpc_black) proj_bk_chg,
        SUM(bd.billed_projected_vol_black * bd.cpc_black) pb_bk_chg,
        SUM(bd.projected_vol_color * bd.cpc_color) proj_col_chg,
        SUM(bd.billed_projected_vol_color * bd.cpc_color) pb_col_chg';
}

if($grpBy == 'Building'){
    $q = 'SELECT orgs.org_name,
            bldg.building_name,
            SUM(bd.projected_vol_black) proj_bk_vol,
            SUM(bd.billed_projected_vol_black) pb_bk_vol,
            SUM(bd.projected_vol_color) proj_col_vol,
            SUM(bd.billed_projected_vol_color) pb_col_vol,' . $qA .
           
            ' FROM billing_data bd
            INNER JOIN buildings bldg ON bd.bldg_id = bldg.bldg_id
            INNER JOIN organization orgs ON bd.org_id = orgs.org_id
            INNER JOIN organization vendOrg ON vendOrg.org_id = bd.vendor_id
            
            WHERE bd.billing_grp_id = ? ' . $qB .
            ' GROUP BY bldg.building_name';
}
else{
    $q =  'SELECT orgs.org_name,
        dpt.dept_name,
        bd.dept_id,
        SUM(bd.projected_vol_black) proj_bk_vol,
        SUM(bd.billed_projected_vol_black) pb_bk_vol,
        SUM(bd.projected_vol_color) proj_col_vol,
        SUM(bd.billed_projected_vol_color) pb_col_vol,' . $qA . 
        
        ' FROM billing_data bd
        INNER JOIN department dpt ON dpt.dept_id = bd.dept_id
        INNER JOIN organization orgs ON bd.org_id = orgs.org_id
        INNER JOIN organization vendOrg ON vendOrg.org_id = bd.vendor_id
        
        WHERE bd.billing_grp_id = ? ' . $qB .
        ' GROUP BY bd.dept_id
        ORDER BY dpt.dept_name;';
}


/******************* PREBILL BEGIN MID SECTION Lines 658-713 ********************/

if($grpBy == 'Building'){
        
        /*$qB will be concatenated to query, whether the billing_type = Vendor or Client,
            but it will be an empty string for Client and extra criterion for Vendor.*/
        
        $sql=("SELECT
               bldg.building_name,
               rms.room_name,
               concat(Make,' ', model) AS MakeModel,
               bd.serial_number,
               cd.vendor_mach_id,
               bd.projected_vol_black,
               bd.billed_projected_vol_black,
               bd.cpc_black,
               bd.projected_vol_color,
               bd.billed_projected_vol_color,
               bd.cpc_color,
               org.org_name,
               bd.vendor_cpc_black,
               bd.vendor_cpc_color
               
              FROM billing_data bd
                INNER JOIN organization org ON bd.org_id = org.org_id 
                INNER JOIN buildings bldg ON bd.bldg_id = bldg.bldg_id
				LEFT JOIN rooms rms ON rms.room_id = bd.room_id				
                INNER JOIN machines mac ON bd.model_id = mac.machines_id
				LEFT JOIN current_devices cd ON cd.serial_number = bd.serial_number
                INNER JOIN organization vendOrg ON vendOrg.org_id = bd.vendor_id
                WHERE bd.billing_grp_id = " . $recordId . $qB . 
                " ORDER BY bldg.building_name, rms.room_name");
    }
    else{
        $sql=("SELECT
           bldg.building_name,
           rms.room_name,
           dpt.dept_name,
           concat(Make,' ', model) AS MakeModel,
           bd.serial_number,
           cd.vendor_mach_id,
           bd.projected_vol_black,
           bd.billed_projected_vol_black,
           bd.cpc_black,
           bd.projected_vol_color,
           bd.billed_projected_vol_color,
           bd.cpc_color,
           org.org_name,
           bd.vendor_cpc_black,
           bd.vendor_cpc_color
           
          FROM billing_data bd
            INNER JOIN organization org ON bd.org_id = org.org_id 
            INNER JOIN buildings bldg ON bd.bldg_id = bldg.bldg_id
			LEFT JOIN rooms rms ON rms.room_id = bd.room_id				
            INNER JOIN machines mac ON bd.model_id = mac.machines_id
			LEFT JOIN current_devices cd ON cd.serial_number = bd.serial_number
            INNER JOIN department dpt ON dpt.dept_id = bd.dept_id
            INNER JOIN organization vendOrg ON vendOrg.org_id = bd.vendor_id
            WHERE bd.billing_grp_id = " . $recordId . $qB . 
            " ORDER BY dpt.dept_name, bldg.building_name, rms.room_name");
    }
	
	
	/************************MAIN CONTENT BLACK Lines 1215-1239************/
	if($billing_type == 'Vendor'){
        $qB = ' Sum(( bd.projected_vol_black * vendor_cpc_black )) proj_bk_chg,
        Sum(( bd.billed_projected_vol_black * vendor_cpc_black )) pb_bk_chg';
        
        $qC = ' AND vendOrg.org_id = ?';
    }
    else{
        $qB = ' Sum(( bd.projected_vol_black * cpc_black )) proj_bk_chg,
        Sum(( bd.billed_projected_vol_black * cpc_black )) pb_bk_chg';
    }

    $selQB = 'SELECT org.org_name,
                mt.type_name model_type,
                vendOrg.org_name vendor,
                Sum(bd.projected_vol_black) proj_bk_vol,
                Sum(bd.billed_projected_vol_black) pb_bk_vol, ' . $qB .
                ' FROM billing_data bd
                INNER JOIN organization org ON bd.org_id = org.org_id
                INNER JOIN machines ma ON bd.model_id = ma.machines_id
                INNER JOIN machine_types mt ON ma.model_type = mt.machine_type_id
                
                INNER JOIN organization vendOrg ON vendOrg.org_id = bd.vendor_id
                
                WHERE bd.billing_grp_id = ? ' . $qC .
                ' GROUP BY bd.vendor_id, mt.type_name;';




/*****************************MAIN CONTENT COLOR Lines 1420-1460****************************/
NOTE: I removed the separate query and used if to assign variables as for the black content.

if($billing_type == 'Vendor'){
        $qB = ' Sum(( bd.projected_vol_color * vendor_cpc_color )) proj_col_chg,
        Sum(( bd.billed_projected_vol_color * vendor_cpc_color )) pb_col_chg';
        
        $qC = ' AND vendOrg.org_id = ?';
    }
    else{
        $qB = ' Sum(( bd.projected_vol_color * cpc_color )) proj_col_chg,
        Sum(( bd.billed_projected_vol_color * cpc_color )) pb_col_chg';
    }
            
            $selQC = 'SELECT org.org_name,
                    mt.type_name model_type,
                    vendOrg.org_name vendor,
                    Sum(bd.projected_volume_color) proj_col_vol,
                    Sum(bd.billed_projected_volume_color) pb_col_vol ' . $qB .                    
                    
                    ' FROM billing_data bd
                    INNER JOIN organization org ON bd.org_id = org.org_id
                    INNER JOIN machines ma ON bd.model_id = ma.machines_id
                    INNER JOIN machine_types mt ON ma.model_type = mt.id
                    INNER JOIN organization vendOrg ON vendOrg.org_id = bd.vendor_id
                    
                    WHERE bd.billing_grp_id = ? ' . $qC .                    
                    ' GROUP BY mt.type_name;';
        }