#Get the org_id to base this report on.
$recordId = $_GET['id'];#1
$schoolYear = $_GET['schYear'];  /* 2 digit school yr */
$rpt_type = $_GET['billing_type']; #"Client";/* This will be Client or Vendor */
$thisVendor = $_GET['vend'];  /* ID of the vendor getting this report */

/*Group report data by Building or Department. */
$grpBy = $_GET['sortBy']; #"Department";#
$groupByName = '';
$gbName='';

/*Type of bill. Reconciliation, Prebill, etc*/
$billingCycle = '';
$footerSuffix = '';

if($rpt_type == 'Vendor'){
    $footerSuffix = ' ' . $thisVendor . ' ';
}


/**************************** CONSTRUCT PAGE TITLE **************************/
/*Query for the org_name, school year and billing type.  The last 2 go in the page title and the footer*/
$sql = "SELECT org.org_name, looksy.school_year, bda.billing_cycle 
    FROM billing_data_archive bda
    
    INNER JOIN organization org ON org.id = bda.org_id_ma
    INNER JOIN lkp_school_year looksy ON looksy.yr_id = bda.school_year 
    WHERE bda.org_id_ma = $recordId
    AND bda.school_year = $schoolYear
    GROUP BY org.org_name";

$result = mysqli_query($dbc, $sql);

$schoolYearFmt = '';
$orgName = '';
$pdf->AddPage('L');

if (mysqli_num_rows($result) > 0) {
    
    while($row = mysqli_fetch_assoc($result)) {
        $orgName = $row["org_name"];
        $schoolYearFmt = $row['school_year'];
        $billingCycle = 'Reconciliation';
    }#end while
}#end if num_rows




/********************** MAIN CONTENT BLACK BEGIN SUM BY BLDG*********************/
/* These are here so all 3 report sections can use them. */
$cpcBlkStr = '';
$cpcColorStr = '';
$addCriterion = ''; #This will only have a value if the report type is Vendor.

/*If the report is Vendor type, then the vendor cpc values will be used for calculating costs. */
if($rpt_type == 'Vendor'){
    $cpcBlkStr = " bda.vendor_cpc_black";
    $cpcColorStr = " bda.vendor_cpc_color";
    
    /* If the report is for a Vendor, limit the resultset to only machines owned by that vendor. */
    /* Machines are listed once for Client and once for Vendor in the bda table, so add this criterion to not dupe the records */
    $addCriterion = ' AND vendOrg.id = ? AND bda.billing_type = "Vendor"';
}

/*If the report is Client type, then the client cpc values will be used for calculating costs. */
else{
    $cpcBlkStr = " bda.cpc_black";
    $cpcColorStr = " bda.cpc_color";
    
    /*Machines are listed once for Client and once for Vendor in the bda table, so add this criterion to not dupe the records */
    $addCriterion = ' AND bda.billing_type = "Client"';
}
$q = ''; /*This will be the query based on whether the $grpBy is Building or Department AND if it is for a client or vendor*/

/* prepare stmt */
if($grpBy == 'Building'){
    /* Sum by Building for Client type OR Vendor type report. This uses the client cpc. */
    
    $q = 'SELECT org.org_name,
            bldg.building_name,
            SUM(mc.`End` - mc.`Begin`) fy_bk_vol,
            SUM(bda.billed_projected_volume_black) prepaid_bk_vol,
            SUM((mc.`End` - mc.`Begin`) * ' . $cpcBlkStr . ') fy_bk_costs,
            SUM((bda.billed_projected_volume_black) * ' . $cpcBlkStr . ')  prepaid_bk_costs,
            SUM(mc.`MstrEnd` - mc.`MstrBegin`) fy_col_vol,
            SUM(bda.billed_projected_volume_color) prepaid_col_vol,
            SUM((mc.`MstrEnd` - mc.`MstrBegin`) * ' . $cpcColorStr . ') fy_col_costs,
            SUM(bda.billed_projected_volume_color * ' . $cpcColorStr . ') prepaid_col_costs
            
            FROM billing_data_archive bda 
            INNER JOIN buildings bldg ON bda.building_id_ma = bldg.id
            INNER JOIN organization org ON bda.org_id_ma = org.id
            INNER JOIN organization vendOrg ON vendOrg.ClientID = bda.vendor_id_ma
            INNER JOIN Machines_current mc ON mc.`SerialNumber` = bda.serial_number
            
            WHERE org.id = ?
            AND bda.school_year = ? ' . $addCriterion .
            ' GROUP BY bldg.building_name;';
}
else{
    /* Sum by Department for Client type OR Vendor type report. This uses the client cpc. */
    $q = 'SELECT org.org_name,
            dpt.dept_name,
            SUM(mc.`End` - mc.`Begin`) fy_bk_vol,
            SUM(bda.billed_projected_volume_black) prepaid_bk_vol,
            SUM((mc.`End` - mc.`Begin`) * ' . $cpcBlkStr . ') fy_bk_costs,
            SUM(bda.billed_projected_volume_black * ' . $cpcBlkStr . ') prepaid_bk_costs,
            
            SUM(mc.`MstrEnd` - mc.`MstrBegin`) fy_col_vol,
            SUM(bda.billed_projected_volume_color) prepaid_col_vol,
            SUM((mc.`MstrEnd` - mc.`MstrBegin`) * ' . $cpcColorStr . ') fy_col_costs,
            SUM(bda.billed_projected_volume_color * ' . $cpcColorStr . ') prepaid_col_costs
            
            FROM billing_data_archive bda 
            INNER JOIN buildings bldg ON bda.building_id_ma = bldg.id
            INNER JOIN organization org ON bda.org_id_ma = org.id
            INNER JOIN organization vendOrg ON vendOrg.ClientID = bda.vendor_id_ma
            INNER JOIN Machines_current mc ON mc.`SerialNumber` = bda.serial_number
            INNER JOIN department dpt ON dpt.id = bda.dept_id_ma
            
            WHERE org.id = ?
            AND bda.school_year = ? ' . $addCriterion . /*added after ? and the beginning qt on next line.*/
            ' GROUP BY dpt.dept_name;';
}      

if($rpt_type == 'Vendor'){
    $stmt->bind_param('idi', $recordId, $schoolYear, $thisVendor);
}
else{
    $stmt->bind_param('id', $recordId, $schoolYear);
}

if($stmt->num_rows > 0){    
    
    $stmt->bind_result($orgName, $gbName, $fy_bk_vol, $prepaid_bk_vol, $fy_bk_costs, $prepaid_bk_costs,
        $fy_col_vol, $prepaid_col_vol, $fy_col_costs, $prepaid_col_costs);

    while($stmt->fetch()){        
        /*Send black and color arrays separately to the getHtmlStr function so it can compose the
            main content for each group*/
        $mainBlack = $mainBlack . getHtmlStr('GTMULTIW2', $contentArrayBk);
        $mainColor = $mainColor . getHtmlStr('GTMULTIW2', $contentArrayCol);
    }#end while
}#end if num_rows
/********************** SUM BY BLDG MAIN CONTENT BLACK END *****************/


/*********************** BLACK GROUP TOTALS FOOTER BEGIN***********************/
/********************** BLACK GROUP TOTALS FOOTER END *********************/

/********************** BEGIN PRINT COLOR GROUP TITLE ***************************/
/********************** END PRINT COLOR GROUP TITLE ************************/

/********************** BEGIN SUMMARY BY BLDG COLOR ***********************/
/********************** END SUMMARY BY BLDG COLOR*********************/

/********************** SUMMARY BY BUILDING TOTALS FOOTER BEGIN*****************/
/********************* SUMMARY BY BUILDING TOTALS FOOTER END ********************/





/********************** BEGIN FULL YEAR RECONCILIATION HERE *********************/
$qA = ''; /*This will be the query based on whether the $grpBy is Building or Department AND if it is for a client or vendor*/

$deptCol = '';
$deptTab = '';
$deptOrder = '';

if($grpBy == 'Building'){
    /*The $addCriterion var is assigned above and will only have a value if the report is for a Vendor */
    
    /*$qA = "SELECT
           bldg.building_name,           
           bda.room_name,
           concat(mac.Make,' ', mac.model) AS MakeModel,           
           bda.serial_number,
           bda.vendor_mach_id,
           mc.`End` - mc.`Begin` projected_volume_black,
           bda.billed_projected_volume_black,
           bda.cpc_black,
           mc.`MstrEnd` - mc.`MstrBegin` projected_volume_color,
           bda.billed_projected_volume_color,
           bda.cpc_color,
           org.org_name,
           bda.vendor_cpc_black,
           bda.vendor_cpc_color,
           mc.`Begin` begin_meter_black,
           mc.`End` end_meter_black,
           mc.`MstrBegin` begin_meter_color,
           mc.`MstrEnd` end_meter_color
           
          FROM billing_data_archive bda
          INNER JOIN Machines_current mc ON mc.`SerialNumber` = bda.serial_number 
          INNER JOIN organization org ON org.id = bda.org_id_ma 
          INNER JOIN buildings bldg ON bldg.id = bda.building_id_ma 
          INNER JOIN machines mac ON mac.id = bda.model_id
          INNER JOIN organization vendOrg ON vendOrg.ClientID = bda.vendor_id_ma
          
          WHERE bda.org_id_ma = ?
          AND bda.billing_type = ?
          AND bda.school_year = ? " . $addCriterion .
          " ORDER BY bldg.building_name, bda.room_name;";*/
}#end if
    
else{
    $deptCol = ' dpt.dept_name, ';
    $deptTab = ' INNER JOIN department dpt ON dpt.id = bda.dept_id_ma ';
    $deptOrder = ' dpt.dept_name, ';
    
    /*$qA = "SELECT
       bldg.building_name,           
       bda.room_name,
       dpt.dept_name,
       concat(Make,' ', model) AS MakeModel,           
       bda.serial_number,
       bda.vendor_mach_id,
       mc.`End` - mc.`Begin` projected_volume_black,
       bda.billed_projected_volume_black,
       bda.cpc_black,
       mc.`MstrEnd` - mc.`MstrBegin` projected_volume_color,
       bda.billed_projected_volume_color,
       bda.cpc_color,
       org.org_name,
       bda.vendor_cpc_black,
       bda.vendor_cpc_color,
       mc.`Begin` begin_meter_black,
       mc.`End` end_meter_black,
       mc.`MstrBegin` begin_meter_color,
       mc.`MstrEnd` end_meter_color
       
      FROM billing_data_archive bda
      INNER JOIN Machines_current mc ON mc.`SerialNumber` = bda.serial_number 
      INNER JOIN organization org ON org.id = bda.org_id_ma 
      INNER JOIN buildings bldg ON bldg.id = bda.building_id_ma 
      INNER JOIN machines mac ON mac.id = bda.model_id
      INNER JOIN organization vendOrg ON vendOrg.ClientID = bda.vendor_id_ma
      INNER JOIN department dpt ON dpt.id = bda.dept_id_ma
        
      WHERE bda.org_id_ma = ?
      AND bda.billing_type = ?
      AND bda.school_year = ? " . $addCriterion . 
      " ORDER BY dpt.dept_name, bldg.building_name, bda.room_name;";*/
}

    $qA = "SELECT
       bldg.building_name,           
       bda.room_name," . $deptCol . 
       "concat(Make,' ', model) AS MakeModel,           
       bda.serial_number,
       bda.vendor_mach_id,
       mc.`End` - mc.`Begin` projected_volume_black,
       bda.billed_projected_volume_black,
       bda.cpc_black,
       mc.`MstrEnd` - mc.`MstrBegin` projected_volume_color,
       bda.billed_projected_volume_color,
       bda.cpc_color,
       org.org_name,
       bda.vendor_cpc_black,
       bda.vendor_cpc_color,
       mc.`Begin` begin_meter_black,
       mc.`End` end_meter_black,
       mc.`MstrBegin` begin_meter_color,
       mc.`MstrEnd` end_meter_color
       
      FROM billing_data_archive bda
      INNER JOIN Machines_current mc ON mc.`SerialNumber` = bda.serial_number 
      INNER JOIN organization org ON org.id = bda.org_id_ma 
      INNER JOIN buildings bldg ON bldg.id = bda.building_id_ma 
      INNER JOIN machines mac ON mac.id = bda.model_id
      INNER JOIN organization vendOrg ON vendOrg.ClientID = bda.vendor_id_ma " . 
      $deptTab . 
        
      " WHERE bda.org_id_ma = ?
      AND bda.billing_type = ?
      AND bda.school_year = ? " . $addCriterion . 
      " ORDER BY " . $deptOrder . " bldg.building_name, bda.room_name;";
    
    
    /*Bind the criteria param based on whether this is a Vendor report or Client report. */
    if($rpt_type == 'Vendor'){
        $sql->bind_param('isdi', $recordId, $rpt_type, $schoolYear, $thisVendor);
    }
    else{
        $sql->bind_param('isd', $recordId, $rpt_type, $schoolYear);
    }   
    

if($sql->num_rows > 0){
    /*Bind results*/
    if($grpBy == 'Building'){
        $sql->bind_result($buildingName, $room, $makeModel, $serialNum,
            $vendorMa, $actualBlkVol, $prepaidBlkVol, $cpcBlk_c, $actualColVol,  $prepaidColVol, $cpcColor_c, $org_name,
            $vendor_cpc_black, $vendor_cpc_color, $begin_meter_bk, $end_meter_bk, $begin_meter_col, $end_meter_col);
    }
    else{
        $sql->bind_result($buildingName, $room, $dept_name, $makeModel, $serialNum,
            $vendorMa, $actualBlkVol, $prepaidBlkVol, $cpcBlk_c, $actualColVol,  $prepaidColVol, $cpcColor_c, $org_name,
            $vendor_cpc_black, $vendor_cpc_color, $begin_meter_bk, $end_meter_bk, $begin_meter_col, $end_meter_col);
    }

    while($sql->fetch()){        
        
        if($grpBy == 'Building'){
            $groupByName = $buildingName;
        }
        else{
            $groupByName = $dept_name;
        }
        
        if($rpt_type == 'Vendor'){
            $cpcBlk = $vendor_cpc_black;
            $cpcColor = $vendor_cpc_color;
        }
        else{
            $cpcBlk = $cpcBlk_c;
            $cpcColor = $cpcColor_c;
        }    
        
        
        if( $gbName != $groupByName && $footerFlag === 'n'){             
        }#end if $groupByName != $gbName
            
        
        else if($gbName == $groupByName){            
        }#end else if $groupByName == $gbName
            
         
        else if($gbName != $groupByName && $footerFlag == 'y'){           
        }#end else if $gbName != $groupByName && $footerFlag == 'y'
    }#end while  
    
  }#end if $result
/******************************END FULL YEAR RECONCILIATION HERE************************/





/***************************** SUMMARY BY VENDOR OR TYPE ***********************/

/******************* CONSTRUCT PAGE TITLE ********************/
if($rpt_type === 'Vendor'){
    $leadColTitle = 'Machine Type';
    $ttl = 'Summary by Type';
}
else{
    $leadColTitle = 'Vendor';
    $ttl = 'Summary by Vendor';
}
/********************** PRINT PAGE TITLE AND GROUP HEADER *********************/

/*This report will be grouped by vendor for Client type reports AND by machine type for Vendor type reports, so
    the column headings will be either "Vendor" or "Machine Type". To allow dynamic values, the $leadColTitle will
    be Vendor and the $leadColValue will be the value of $vendor.  For vendor type reports, the $leadColTitle will
    be Machine Type and the $leadColValue will be the value of $make_mod. */

/********************** PRINT BLACK TABLE HEADER *********************/

/********************** MAIN CONTENT BLACK BEGIN *********************/
$cpcBlkStr = '';
$cpcColorStr = '';
$qStr = ''; /*This will be the query based on whether the $rpt_type is Vendor or Client. */
$addCriterion = '';
$grpClause = '';
if($rpt_type == 'Vendor'){
    $cpcBlkStr = "bda.vendor_cpc_black";
    $cpcColorStr = "bda.vendor_cpc_color";
    
    /*Machines are listed once for Client and once for Vendor in the bda table, so add this criterion to not dupe the records */
    $addCriterion = ' AND bda.billing_type = "Vendor" ';
    
    /* Vendors need data grouped by machine type. Only show records of machines owned
        by this specific vendor. */
        
    /*$qStr = 'SELECT org.org_name,
        mt.type_name model_type,
        bda.vendor_id_ma vendor,
        Sum(mc.`End` - mc.`Begin`) fy_bk_vol,
        Sum((mc.`End` - mc.`Begin`) * ' . $cpcBlkStr . ') fy_bk_costs,
        Sum(bda.billed_projected_volume_black) prepaid_bk_vol,
        Sum(bda.billed_projected_volume_black * ' . $cpcBlkStr . ') prepaid_bk_costs
        
        FROM billing_data_archive bda
        INNER JOIN Machines_current mc ON mc.SerialNumber = bda.serial_number
        INNER JOIN organization org ON org.id = bda.org_id_ma 
        INNER JOIN machines mac ON mac.id = bda.model_id
        INNER JOIN machine_types mt ON mt.id = mac.model_type
        INNER JOIN organization vendOrg ON vendOrg.ClientID = bda.vendor_id_ma
        WHERE bda.org_id_ma = ?
        AND bda.school_year = ?
        AND vendOrg.id = ?' . $addCriterion .
    
        ' GROUP BY mt.type_name;';*/
}#end if $rpt_type == 'Vendor'

else{
    $cpcBlkStr = "bda.cpc_black";
    $cpcColorStr = "bda.cpc_color";
    $grpClause = " bda.vendor_id_ma, ";
    
    /*Machines are listed once for Client and once for Vendor in the bda table, so add this criterion to not dupe the records */
    $addCriterion = ' AND bda.billing_type = "Client" ';
    
    /* Clients need data grouped by vendor. Show machine data for all vendors. */
    /*$qStr = 'SELECT org.org_name,
        mt.type_name model_type,
        bda.vendor_id_ma vendor,
        Sum(mc.`End` - mc.`Begin`) fy_bk_vol,
        Sum((mc.`End` - mc.`Begin`) * ' . $cpcBlkStr . ') fy_bk_costs,
        Sum(bda.billed_projected_volume_black) prepaid_bk_vol,
        Sum(bda.billed_projected_volume_black * ' . $cpcBlkStr . ') prepaid_bk_costs
        
        FROM billing_data_archive bda
        INNER JOIN Machines_current mc ON mc.SerialNumber = bda.serial_number
        INNER JOIN organization org ON org.id = bda.org_id_ma 
        INNER JOIN machines mac ON mac.id = bda.model_id
        INNER JOIN machine_types mt ON mt.id = mac.model_type
        WHERE bda.org_id_ma = ?
        AND bda.school_year = ?' . $addCriterion .
        ' GROUP BY bda.vendor_id_ma, mt.type_name;';*/
}#end else $rpt_type == 'Vendor'

$qStr = 'SELECT org.org_name,
        mt.type_name model_type,
        bda.vendor_id_ma vendor,
        Sum(mc.`End` - mc.`Begin`) fy_bk_vol,
        Sum((mc.`End` - mc.`Begin`) * ' . $cpcBlkStr . ') fy_bk_costs,
        Sum(bda.billed_projected_volume_black) prepaid_bk_vol,
        Sum(bda.billed_projected_volume_black * ' . $cpcBlkStr . ') prepaid_bk_costs
        
        FROM billing_data_archive bda
        INNER JOIN Machines_current mc ON mc.SerialNumber = bda.serial_number
        INNER JOIN organization org ON org.id = bda.org_id_ma 
        INNER JOIN machines mac ON mac.id = bda.model_id
        INNER JOIN machine_types mt ON mt.id = mac.model_type
        WHERE bda.org_id_ma = ?
        AND bda.school_year = ?' . $addCriterion .
        ' GROUP BY ' . $grpClause . ' mt.type_name;';
        
/*Bind the criteria param*/
if($rpt_type === 'Vendor'){
    $stmt->bind_param('idi', $recordId, $schoolYear, $thisVendor);
}
else{
    $stmt->bind_param('id', $recordId, $schoolYear);
}
/*Check success*/
if($stmt->num_rows > 0){
    
    /*Bind results*/
    $stmt->bind_result($orgName, $make_mod, $vendor, $fy_bk_vol, $fy_bk_costs, $prepaid_bk_vol, $prepaid_bk_costs);
    
    while($stmt->fetch()){        
        if($rpt_type == 'Vendor'){
            $leadColValue = $make_mod;
        }
        else{
            $leadColValue = $vendor;
        }        
        
        /*Send black and color arrays separately to the getHtmlStr function so it can compose the
            main content for each group*/
        $mainBlack = $mainBlack . getHtmlStr('GTMULTIW2', $contentArrayBk);
    }#end while
    
   
        /********************** SUMMARY BY VENDOR BLACK FOOTER BEGIN*********************/
        /********************** SUMMARY BY VENDOR BLACK FOOTER END *********************/
        
       
        /********************** PRINT COLOR GROUP TITLE *********************/
        
        
        /********************** PRINT COLOR TABLE HEADER BEGIN *********************/
        /********************** PRINT COLOR TABLE HEADER END*********************/
        
        
        /********************** SUMMARY BY VENDOR MAIN CONTENT COLOR BEGIN *********************/
        $cpcBlkStr = '';
        $cpcColorStr = '';
        $qStr = ''; /*This will be the query based on whether the $rpt_type is Vendor or Client. */
        $groupClause = '';
        
        if($rpt_type == 'Vendor'){
            $cpcBlkStr = "bda.vendor_cpc_black";
            $cpcColorStr = "bda.vendor_cpc_color";
            
            /*Machines are listed once for Client and once for Vendor in the bda table, so add this criterion to not dupe the records */
            $addCriterion = ' AND bda.billing_type = "Vendor" ';
            
            /* Vendors need data grouped by machine type. ONLY show machines owned by this vendor. */
           
            /*$qStr = 'SELECT org.org_name,
                mt.type_name model_type,
                bda.vendor_id_ma vendor,
                Sum(mc.`MstrEnd` - mc.`MstrBegin`) proj_col_vol,
                Sum((mc.`MstrEnd` - mc.`MstrBegin`) * ' . $cpcColorStr . ') proj_col_chg,
                Sum(bda.billed_projected_volume_color) pb_col_vol,
                Sum(bda.billed_projected_volume_color * ' . $cpcColorStr . ') pb_col_chg
                
                FROM billing_data_archive bda
                INNER JOIN Machines_current mc ON mc.SerialNumber = bda.serial_number
    	        INNER JOIN organization org ON org.id = bda.org_id_ma 
    	        INNER JOIN machines mac ON mac.id = bda.model_id
    	        INNER JOIN machine_types mt ON mt.id = mac.model_type
    	        INNER JOIN organization vendOrg ON vendOrg.ClientID = bda.vendor_id_ma
    	        
    	        WHERE bda.org_id_ma = ?
    	        AND bda.school_year = ?
    	        AND vendOrg.id = ?' . $addCriterion .
                ' GROUP BY mt.type_name;';*/
        }#end if $rpt_type == 'Vendor'
        
        else{
            $cpcBlkStr = "bda.cpc_black";
            $cpcColorStr = "bda.cpc_color";
            $groupClause = " bda.vendor_id_ma, ";
            
            /*Machines are listed once for Client and once for Vendor in the bda table, so add this criterion to not dupe the records */
            $addCriterion = ' AND bda.billing_type = "Client" ';
            
            /* Clients need data grouped by vendor. Show all machines owned by all vendors. */
           
            /*$qStr = 'SELECT org.org_name,
                mt.type_name model_type,
                bda.vendor_id_ma vendor,
                Sum(mc.`MstrEnd` - mc.`MstrBegin`) proj_col_vol,
                Sum((mc.`MstrEnd` - mc.`MstrBegin`) * ' . $cpcColorStr . ') proj_col_chg,
                Sum(bda.billed_projected_volume_color) pb_col_vol,
                Sum(bda.billed_projected_volume_color * ' . $cpcColorStr . ') pb_col_chg
                
                FROM billing_data_archive bda
                INNER JOIN Machines_current mc ON mc.SerialNumber = bda.serial_number
    	        INNER JOIN organization org ON org.id = bda.org_id_ma 
    	        INNER JOIN machines mac ON mac.id = bda.model_id
    	        INNER JOIN machine_types mt ON mt.id = mac.model_type
    	        
    	        WHERE bda.org_id_ma = ?
    	        AND bda.school_year = ?' . $addCriterion . 
                ' GROUP BY bda.vendor_id_ma, mt.type_name;';*/
        }#end else $rpt_type == 'Vendor'
        
        $qStr = 'SELECT org.org_name,
                mt.type_name model_type,
                bda.vendor_id_ma vendor,
                Sum(mc.`MstrEnd` - mc.`MstrBegin`) proj_col_vol,
                Sum((mc.`MstrEnd` - mc.`MstrBegin`) * ' . $cpcColorStr . ') proj_col_chg,
                Sum(bda.billed_projected_volume_color) pb_col_vol,
                Sum(bda.billed_projected_volume_color * ' . $cpcColorStr . ') pb_col_chg
                
                FROM billing_data_archive bda
                INNER JOIN Machines_current mc ON mc.SerialNumber = bda.serial_number
    	        INNER JOIN organization org ON org.id = bda.org_id_ma 
    	        INNER JOIN machines mac ON mac.id = bda.model_id
    	        INNER JOIN machine_types mt ON mt.id = mac.model_type
    	        
    	        WHERE bda.org_id_ma = ?
    	        AND bda.school_year = ?
    	        AND mac.is_color = 1' . $addCriterion . 
                ' GROUP BY' . $groupClause . 'mt.type_name;';
        
                        
        /*Bind the criteria param*/
        if($rpt_type == 'Vendor'){
            $eStmt->bind_param('idi', $recordId, $schoolYear, $thisVendor);
        }
        else{
            $eStmt->bind_param('id', $recordId, $schoolYear);
        }
        
               
        if($eStmt->num_rows > 0){
            /*Bind results*/
            $eStmt->bind_result($orgName, $make_mod, $vendor, $proj_col_vol, $proj_col_chg, $pb_col_vol, $pb_col_chg);
            
            $leadColValue = '';
            
            while($eStmt->fetch()){
                
                if($rpt_type == 'Vendor'){
                    $leadColValue = $make_mod;
                }
                else{
                    $leadColValue = $vendor;
                }
                /*Send black and color arrays separately to the getHtmlStr function so it can compose the
                    main content for each group*/
                $mainColor = $mainColor . getHtmlStr('GTMULTIW2', $contentArrayCol);
            }#end while
            
        }#end if $eStmt->num_rows
        /********************** SUMMARY BY VENDOR MAIN CONTENT COLOR END *********************/
        
        
        /************************** COLOR FOOTER END****************************/        
        
        /******************** COMBINED BLACK AND COLOR TOTALS FOOTER BEGIN*****************/
        /********************** COMBINED BLACK AND COLOR TOTALS FOOTER END *********************/
}#end if num_rows