<?php
require_once(__DIR__ . '/../../../../../vendor/autoload.php');

use Orchard\Mysqli\QueryData;
use Orchard\Mysqli\Wrapper as MysqliWrapper;

$pdoWrapperObj = getPDOQueryWrapper();

	$eu_countries = array();
	//for diplay section
	$dynamic_column_name = array();

	//getCountryName
	$country_name_arr = array();
	$getCountryName = "select id, name from country";
	$getCountryName_rs = $pdoWrapperObj->executeQuery($getCountryName, []);
	if ($getCountryName_rs === false) {
	    show_pdo_error($getCountryName, $pdoWrapperObj->getLastStatement());
	}

	foreach ($getCountryName_rs as $getCountryName_row) {
		$country_name_arr[$getCountryName_row['id']] = $getCountryName_row['name'];
	}
	$country_name_arr[0] = '[No Country]';

	//get ca rep name
	$ca_rep_arr = array();
	$getCaRep = "select id, concat(f_name, ' ', l_name) as name from orchadmin_users";
	$getCaRep_rs = $pdoWrapperObj->executeQuery($getCaRep, []);
	if ($getCaRep_rs === false) {
	    show_pdo_error($getCaRep, $pdoWrapperObj->getLastStatement());
	}
	foreach ($getCaRep_rs as $getCaRep_row) {
		$ca_rep_arr[$getCaRep_row['id']] = $getCaRep_row['name'];
	}

	//get owner name
	$owner_name_arr = array();
	$sql = 'select owner_id, owner_name from owner';
	$result = $pdoWrapperObj->executeQuery($sql, []);
	if ($result === false) {
	    show_pdo_error($sql, $pdoWrapperObj->getLastStatement());
	}
	foreach ($result as $row) {
		$owner_name_arr[$row['owner_id']] = $row['owner_name'];
	}

	$sql = 'SELECT id FROM country WHERE continent = "Europe"';
	$result = $pdoWrapperObj->executeQuery($sql, []);
	if ($result === false) {
	    show_pdo_error($sql, $pdoWrapperObj->getLastStatement());
	}
	foreach ($result as $row) {
		$eu_countries[] = $row['id'];
	}

	$whereClause = '';
	$whereClauseArr = array();
	if (is_array($owner_ids) and count($owner_ids)) {
	    $whereClause .= ' AND o.owner_id IN (' . implode(', ', array_fill(0, count($owner_ids), '?')) . ')';
	    foreach ($owner_ids as $id) {
	        $whereClauseArr[]['i'] = $id;
	    }
	}
	elseif ($owner_id) {
	    $whereClause .= ' AND o.owner_id = ?';
	    $whereClauseArr[]['i'] = $owner_id;
	}
	if (is_array($ca_reps) and count($ca_reps)) {
			$data_params = [];
			$sql = "SELECT v.vendor_id
					FROM vendor v
					INNER JOIN vendor_contract vcc ON v.vendor_id=vcc.vendor_id
					LEFT JOIN vendor_contract vcc2 ON vcc.vendor_id = vcc2.vendor_id AND vcc2.cont_start <= CURDATE()
									AND ( vcc2.contract_type = 'per_release_term' AND
									    (vcc2.cont_start = '0000-00-00' OR vcc2.release_term <= 0 OR ( vcc2.cont_start + interval vcc2.release_term month) > CURDATE())
									OR ((vcc2.contract_type <> 'per_release_term' OR vcc2.contract_type IS NULL) AND (vcc2.cont_end >= CURDATE() OR vcc2.cont_end IS NULL OR vcc2.cont_end = '0000-00-00'))
									)
								 AND vcc.cont_start < vcc2.cont_start
					WHERE vcc2.id IS NULL AND vcc.cont_start <= CURDATE()
					AND ( vcc.contract_type = 'per_release_term' AND
					    (vcc.cont_start = '0000-00-00' OR vcc.release_term <= 0 OR ( vcc.cont_start + interval vcc.release_term month) > CURDATE())
					OR ((vcc.contract_type <> 'per_release_term' OR vcc.contract_type IS NULL) AND (vcc.cont_end >= CURDATE() OR vcc.cont_end IS NULL OR vcc.cont_end = '0000-00-00'))
					)
					WHERE vcc.orchrep_name IN (" . implode(', ', array_fill(0, count($ca_reps), '?')) . ")";
			foreach ($ca_reps as $reps) {
			    $data_params[] = $reps;
			}
			$rs = $pdoWrapperObj->executeQuery($sql, $data_params);
			if ($rs === false) {
			    show_pdo_error($sql, $pdoWrapperObj->getLastStatement());
			}
			$vendors_belong_to_orchrep_name = array();
			foreach ($rs as $row) {
			    $vendors_belong_to_orchrep_name[] = $row['vendor_id'];
			}
		if(count($vendors_belong_to_orchrep_name)){
		    $whereClause .= ' AND v.vendor_id IN (' . implode(', ', array_fill(0, count($vendors_belong_to_orchrep_name), '?')) . ')';
		    foreach ($vendors_belong_to_orchrep_name as $vendor) {
		        $whereClauseArr[]['i'] = $vendor;
		    }
		}
	}
	if (is_array($label_countries) and count($label_countries)) {
	    $whereClause .= ' AND vcc.orchard_country IN (' . implode(', ', array_fill(0, count($label_countries), '?')) . ')';
	    foreach ($label_countries as $country) {
	        $whereClauseArr[]['i'] = $country;
	    }
	}
	$periodClause = '';
	$periodClauseArr = array();
	$period_arr = array();
	switch ($pd1_select) {
		case 'month-year':
			$from_date = $myear . '-' . $month . '-01';
			$to_date = $myear_to . '-' . $month_to . '-31';
			$group_by_period = 'month';
			break;
		case 'quarter-year':
			$from_date = ConvertQuarterToDate('start',$quarter,$qyear,'Y-m-d');
			$to_date = ConvertQuarterToDate('end',$quarter_to,$qyear_to,'Y-m-d');
			$group_by_period = 'quarter';
			break;
	}
	$periodClause .= ' AND dsd.date BETWEEN ? AND ?';
	$periodClauseArr[]['s'] = $from_date;
	$periodClauseArr[]['s'] = $to_date;
	$NoWhereClause = (!$whereClause and !$periodClause);

	$curr_date = $from_date;
	while ($curr_date <= $to_date) {
		$curr_date = date('Y-m-d', DateAdd('m',1,strtotime($curr_date)));
		$period_arr[] = date('Y-m-d', DateAdd('d',-1,strtotime($curr_date)));
	}
	//getting countries that the partners exclusively represent
	
	if($disp_sales_within_terr){
		$exclusive_countries = array();
		$data_params = [];
		$get_exclusive_countries = "select distinct at.territory_id
									from agreement a
									inner join agreement_territory at on a.agreement_id = at.agreement_id
									where service_type = 'owner' ";
		if(is_array($owner_ids) and count($owner_ids)){
		    $get_exclusive_countries .= ' and service_id in (' . implode(', ', array_fill(0, count($owner_ids), '?')) . ') and at.type = "exclusive"';
		    foreach ($owner_ids as $id) {
		        $data_params[] = $id;
		    }
		}elseif ($owner_id){
		    $get_exclusive_countries .= ' and service_id = ? and at.type = "exclusive"';
		    $data_params[] = $owner_id;
		}

		$get_exclusive_countries_rs = $pdoWrapperObj->executeQuery($get_exclusive_countries, $data_params);
		if ($get_exclusive_countries_rs === false) {
		    show_pdo_error($get_exclusive_countries, $pdoWrapperObj->getLastStatement());
		}
		foreach ($get_exclusive_countries_rs as $get_exclusive_countries_row) {
		    $exclusive_countries[] = $get_exclusive_countries_row['territory_id'];
		}
	}
	
	
	/*
	$tmp_period_arr = array();
	foreach ($period_arr as $period_end_date) {
			if ($pd1_select == 'month-year') {
				$tmp_period_arr[] = date('n-Y', strtotime($period_end_date));
			}
			else {
				$tmp_period_arr[] = ConvertDateToQuarterString($period_end_date);
			}
	}
	$period_arr = $tmp_period_arr;
	*/
	$mainarray = array();
	$displayArr = array();
	$region_country_arr = array();
	if ($report_type == 'total_gross_sales' or $report_type == 'avg_sales_per_track' or $report_type == 'pct_of_sales_compared' or $report_type == 'pct_of_active_tracks') {
		$region_sales = array();
			$sql = "SELECT if (cm.territory = 1, 'US', if (cm.territory IN (" . implode(', ', array_fill(0, count($eu_countries), '?')) . "), 'EUR', 'ROW')) as sales_region,
						if(vcc.orchard_country is null or vcc.orchard_country ='' or vcc.orchard_country=0,0,vcc.orchard_country) as label_country,
						SUM(dsd.total) AS sales, o.owner_id as k, " . ($group_by_period == 'quarter' ? " concat(YEAR(dsd.date),'Q',QUARTER(dsd.date)) " : " concat(month(dsd.date),'-',year(dsd.date)) ") . " as period
					FROM vendor v
						INNER JOIN owner o ON v.owner = o.owner_abbrivation
						INNER JOIN artist_info a ON v.vendor_id = a.vendor_id
						INNER JOIN releases r ON a.artist_id = r.artist_id
						INNER JOIN accountingflat.dig_sales_detail dsd ON r.upc = dsd.upc
						INNER JOIN dig_sales ds ON ds.statement_id = dsd.statement_id
						INNER JOIN customer_master cm ON ds.dms_customer_id = cm.customer_id
						INNER JOIN vend_contact vc ON v.vendor_id = vc.vendor_id and vc.master = 'Y'
						INNER JOIN contact vcc ON vcc.contact_id = vc.contact_id
					WHERE " . ($NoWhereClause ? 0 : 1 .' ' . $whereClause . ' ' . $periodClause) . " AND o.owner_id NOT IN(1,2,3) 
					GROUP BY k, label_country, period, sales_region";
			$data_params = [];
			foreach ($eu_countries as $country) {
			    $data_params[] = $country;
			}
			if (count($whereClauseArr)) {
			    foreach ($whereClauseArr as $key => $value) {
			        $data_params[] = current($value);
			    }
			}
			if (count($periodClauseArr)) {
			    foreach ($periodClauseArr as $key => $value) {
			        $data_params[] = current($value);
			    }
			}
			$rs = $pdoWrapperObj->executeQuery($sql, $data_params);
			if ($rs === false) {
			    show_pdo_error($sql, $pdoWrapperObj->getLastStatement());
			}
			foreach ($rs as $row) {
			    $mainarray[$row['k']][$row['label_country']][$row['period']][$row['sales_region']]['sales'] += $row['sales'];
			    $region_sales[$row['period']][$row['sales_region']] += $row['sales'];
			}
		if($disp_sales_within_terr){
			$sql = "SELECT cm.territory, if(vcc.orchard_country is null or vcc.orchard_country ='' or vcc.orchard_country=0,0,vcc.orchard_country) as label_country,
					SUM(dsd.total) AS sales, o.owner_id as k, " . ($group_by_period == 'quarter' ? " concat(YEAR(dsd.date),'Q',QUARTER(dsd.date)) " : " concat(month(dsd.date),'-',year(dsd.date)) ") . " as period
					FROM vendor v
						INNER JOIN owner o ON v.owner = o.owner_abbrivation
						INNER JOIN artist_info a ON v.vendor_id = a.vendor_id
						INNER JOIN releases r ON a.artist_id = r.artist_id
						INNER JOIN accountingflat.dig_sales_detail dsd ON r.upc = dsd.upc
						INNER JOIN dig_sales ds ON ds.statement_id = dsd.statement_id
						INNER JOIN customer_master cm ON ds.dms_customer_id = cm.customer_id
						INNER JOIN vend_contact vc ON v.vendor_id = vc.vendor_id and vc.master = 'Y'
						INNER JOIN contact vcc ON vcc.contact_id = vc.contact_id
					WHERE " . ($NoWhereClause ? 0 : 1 . ' ' . $whereClause . ' ' . $periodClause) . " AND o.owner_id NOT IN(1,2,3)
					group by k, cm.territory, label_country, period";
			$data_params = [];
			if (count($whereClauseArr)) {
			    foreach ($whereClauseArr as $key => $value) {
			        $data_params[] = current($value);
			    }
			}
			if (count($periodClauseArr)) {
			    foreach ($periodClauseArr as $key => $value) {
			        $data_params[] = current($value);
			    }
			}
			$rs = $pdoWrapperObj->executeQuery($sql, $data_params);
			if ($rs === false) {
			    show_pdo_error($sql, $pdoWrapperObj->getLastStatement());
			}
			foreach ($rs as $row) {
			    $mainarray[$row['k']][$row['label_country']][$row['period']][$row['territory']]['sales'] += $row['sales'];
			    $region_sales[$row['period']][$row['territory']] += $row['sales'];

			    if (in_array($row['territory'], $exclusive_countries)) {
			        $region_country_arr[$row['period']][$row['territory']] = $country_name_arr[$row['territory']];
			    }
			}
		}
		
		foreach($region_country_arr as $period=>$countries){
			asort($region_country_arr[$period]);
		}

		foreach ($mainarray as $k => $country_ids) {
			foreach ($country_ids as $country_id => $periods) {
				foreach ($periods as $period => $regions) {
					foreach ($regions as $region => $sales) {
						if (!$disp_sales_within_terr and is_numeric($region)) {
							continue;
						}
						$displayArr[$k.'_'.$country_id]['name'] = $owner_name_arr[$k];
						$displayArr[$k.'_'.$country_id]['partner_territory'] = $country_name_arr[$country_id];
						$displayArr[$k.'_'.$country_id][$period.'_'.$region] = $sales['sales'];
						$displayArr[$k.'_'.$country_id][$period.'_total_sales'] += (is_numeric($region) ? 0 : $sales['sales']);
						$displayArr[$k.'_'.$country_id][$period.'_'.$region.'_sales_pct'] = ($region_sales[$period][$region] > 0 ? $sales['sales']/$region_sales[$period][$region] : 0);
					}
				}
			}
		}
	}
	if ($report_type == 'pct_of_sales_compared'){
		//concat all the countries from $region_country_arr arrat
		$temp_countries = array();
		foreach($region_country_arr as $period=>$countries){
			asort($region_country_arr[$period]);
		}
		$data_params = [];
		$sql = "SELECT if (cm.territory = 1, 'US', if (cm.territory IN (" . implode(', ', array_fill(0, count($eu_countries), '?')) . "), 'EUR', 'ROW')) as sales_region,
					SUM(dsd.total) AS sales,  " . ($group_by_period == 'quarter' ? " concat(YEAR(dsd.date),'Q',QUARTER(dsd.date)) " : " concat(month(dsd.date),'-',year(dsd.date)) ") . " as period
				FROM vendor v
					INNER JOIN artist_info a ON v.vendor_id = a.vendor_id
					INNER JOIN releases r ON a.artist_id = r.artist_id
					INNER JOIN accountingflat.dig_sales_detail dsd ON r.upc = dsd.upc
					INNER JOIN dig_sales ds ON ds.statement_id = dsd.statement_id
					INNER JOIN customer_master cm ON ds.dms_customer_id = cm.customer_id
				WHERE 1 " . $periodClause . " 
				GROUP BY sales_region, period";
		foreach ($eu_countries as $country) {
		    $data_params[] = $country;
		}
		if (count($periodClauseArr)) {
		    foreach ($periodClauseArr as $key => $value) {
		        $data_params[] = current($value);
		    }
		}
		$rs = $pdoWrapperObj->executeQuery($sql, $data_params);
		if ($rs === false) {
		    show_pdo_error($sql, $pdoWrapperObj->getLastStatement());
		}

		$total_orchard_sales = array();
		foreach ($rs as $row) {
		    $total_orchard_sales[$row['period']][$row['sales_region']] += $row['sales'];
		}

		if($disp_sales_within_terr){
			$sql = "SELECT cm.territory as sales_region,
						SUM(dsd.total) AS sales,  " . ($group_by_period == 'quarter' ? " concat(YEAR(dsd.date),'Q',QUARTER(dsd.date)) " : " concat(month(dsd.date),'-',year(dsd.date)) ") . " as period
					FROM vendor v
						INNER JOIN artist_info a ON v.vendor_id = a.vendor_id
						INNER JOIN releases r ON a.artist_id = r.artist_id
						INNER JOIN accountingflat.dig_sales_detail dsd ON r.upc = dsd.upc
						INNER JOIN dig_sales ds ON ds.statement_id = dsd.statement_id
						INNER JOIN customer_master cm ON ds.dms_customer_id = cm.customer_id
					WHERE 1 " . $periodClause . " 
					GROUP BY sales_region, period";
			$data_params = [];
			if (count($periodClauseArr)) {
			    foreach ($periodClauseArr as $key => $value) {
			        $data_params[] = current($value);
			    }
			}
			$rs = $pdoWrapperObj->executeQuery($sql, $data_params);
			if ($rs === false) {
			    show_pdo_error($sql, $pdoWrapperObj->getLastStatement());
			}

			foreach ($rs as $row) {
			    $total_orchard_sales[$row['period']][$row['sales_region']] += $row['sales'];
			}
		}
		
		//$temp_sales = array();
		foreach ($mainarray as $k => $country_ids) {
			foreach ($country_ids as $country_id => $periods) {
				foreach ($periods as $period => $regions) {
					foreach ($regions as $region => $sales) {
						//if (!$disp_sales_within_terr and is_numeric($region)) {
						//	continue;
						//}
						//$temp_sales[$period][$region] += $sales['sales'];
						//$displayArr[$k.'_'.$country_id][$period.'_'.$region.'_sales_pct'] = ($total_orchard_sales[$period][$region]) ? ($sales['sales'] / $total_orchard_sales[$period][$region]) : 1;
						$displayArr[$k.'_'.$country_id][$period.'_'.$region.'_sales_pct'] = ($region_sales[$period][$region]) ? ($sales['sales'] / $total_orchard_sales[$period][$region]) : 1;
					}
				}
			}
		}
	}
	if ($report_type == 'avg_sales_per_track' or $report_type == 'pct_of_active_tracks') {
		$sql = "SELECT if (cm.territory = 1, 'US', if (cm.territory IN (" . implode(', ', array_fill(0, count($eu_countries), '?')) . "), 'EUR', 'ROW')) as sales_region,
					if(vcc.orchard_country is null or vcc.orchard_country ='' or vcc.orchard_country=0,0,vcc.orchard_country) as label_country,
					count(distinct t.id) as track_count, o.owner_id as k, " . ($group_by_period == 'quarter' ? " concat(YEAR(dsd.date),'Q',QUARTER(dsd.date)) " : " concat(month(dsd.date),'-',year(dsd.date)) ") . " as period
				FROM vendor v
					INNER JOIN owner o ON v.owner = o.owner_abbrivation
					INNER JOIN artist_info a ON v.vendor_id = a.vendor_id
					INNER JOIN releases r ON a.artist_id = r.artist_id
					INNER JOIN accountingflat.dig_sales_detail dsd ON r.upc = dsd.upc
					INNER JOIN dig_sales ds ON ds.statement_id = dsd.statement_id
					INNER JOIN track t ON (dsd.trans_type = 'DA' and t.upc = dsd.upc) or (t.upc = dsd.upc and t.cd = dsd.cd and t.track_id = dsd.track_id)
					INNER JOIN customer_master cm ON ds.dms_customer_id = cm.customer_id
					INNER JOIN vend_contact vc ON v.vendor_id = vc.vendor_id and vc.master = 'Y'
					INNER JOIN contact vcc ON vcc.contact_id = vc.contact_id
				WHERE " . ($NoWhereClause ? 0 : 1 . ' ' . $whereClause . ' ' . $periodClause) . " AND o.owner_id NOT IN(1,2,3)
				GROUP BY k, label_country, period, sales_region";
		$data_params = [];
		foreach ($eu_countries as $country) {
		    $data_params[] = $country;
		}
		if (count($whereClauseArr)) {
		    foreach ($whereClauseArr as $key => $value) {
		        $data_params[] = current($value);
		    }
		}
		if (count($periodClauseArr)) {
		    foreach ($periodClauseArr as $key => $value) {
		        $data_params[] = current($value);
		    }
		}
		$rs = $pdoWrapperObj->executeQuery($sql, $data_params);
		if ($rs === false) {
		    show_pdo_error($sql, $pdoWrapperObj->getLastStatement());
		}
		foreach ($rs as $row) {
		    $mainarray[$row['k']][$row['label_country']][$row['period']][$row['sales_region']]['track_count'] = $row['track_count'];
		}
		if($disp_sales_within_terr){
			$sql = "SELECT cm.territory,
						if(vcc.orchard_country is null or vcc.orchard_country ='' or vcc.orchard_country=0,0,vcc.orchard_country) as label_country,
						count(distinct t.id) as track_count, o.owner_id as k, " . ($group_by_period == 'quarter' ? " concat(YEAR(dsd.date),'Q',QUARTER(dsd.date)) " : " concat(month(dsd.date),'-',year(dsd.date)) ") . " as period
					FROM vendor v
						INNER JOIN owner o ON v.owner = o.owner_abbrivation
						INNER JOIN artist_info a ON v.vendor_id = a.vendor_id
						INNER JOIN releases r ON a.artist_id = r.artist_id
						INNER JOIN accountingflat.dig_sales_detail dsd ON r.upc = dsd.upc
						INNER JOIN dig_sales ds ON ds.statement_id = dsd.statement_id
						INNER JOIN track t ON (dsd.trans_type = 'DA' and t.upc = dsd.upc) or (t.upc = dsd.upc and t.cd = dsd.cd and t.track_id = dsd.track_id)
						INNER JOIN customer_master cm ON ds.dms_customer_id = cm.customer_id
						INNER JOIN vend_contact vc ON v.vendor_id = vc.vendor_id and vc.master = 'Y'
						INNER JOIN contact vcc ON vcc.contact_id = vc.contact_id
					WHERE " . ($NoWhereClause ? 0 : 1 . ' ' . $whereClause . ' ' . $periodClause) . " AND o.owner_id NOT IN(1,2,3)
					GROUP BY k, label_country, period, territory";
			$data_params = [];
			if (count($whereClauseArr)) {
			    foreach ($whereClauseArr as $key => $value) {
			        $data_params[] = current($value);
			    }
			}
			if (count($periodClauseArr)) {
			    foreach ($periodClauseArr as $key => $value) {
			        $data_params[] = current($value);
			    }
			}
			$rs = $pdoWrapperObj->executeQuery($sql, $data_params);
			if ($rs === false) {
			    show_pdo_error($sql, $pdoWrapperObj->getLastStatement());
			}
			foreach ($rs as $row) {
			    $mainarray[$row['k']][$row['label_country']][$row['period']][$row['territory']]['track_count'] = $row['track_count'];
			}
		}

		foreach($region_country_arr as $period=>$countries){
			asort($region_country_arr[$period]);
		}
		
		foreach ($mainarray as $k => $country_ids) {
			foreach ($country_ids as $country_id => $periods) {
				foreach ($periods as $period => $regions) {
					foreach ($regions as $region => $sales) {
						if (!$disp_sales_within_terr and is_numeric($region)) {
							continue;
						}
						$displayArr[$k.'_'.$country_id]['name'] = $owner_name_arr[$k];
						$displayArr[$k.'_'.$country_id]['partner_territory'] = $country_name_arr[$country_id];
						$displayArr[$k.'_'.$country_id][$period.'_'.$region.'_avg'] = ($sales['track_count'] ? ($sales['sales']/$sales['track_count']) : 0);
						$displayArr[$k.'_'.$country_id][$period.'_total_track_count'] += $sales['track_count'];
					}
				}
			}
		}
	}
	if ($report_type == 'pct_of_active_tracks' and count($displayArr)){
		// get total track available
		foreach ($period_arr as $curr_period_date_tmp) {
			if ($pd1_select == 'month-year') {
				$period = date('n-Y', strtotime($curr_period_date_tmp));
			}else {
				$period = ConvertDateToQuarterString($curr_period_date_tmp);
			}
			$sql = "SELECT count(distinct t.id) as availability, x.k, x.label_country
				FROM (
					SELECT dd.upc, o.owner_id as k,
						if(vcc.orchard_country is null or vcc.orchard_country ='' or vcc.orchard_country=0,0,vcc.orchard_country) as label_country
					FROM delivery_history dd
						INNER JOIN releases r ON r.upc = dd.upc
						INNER JOIN artist_info a ON a.artist_id = r.artist_id
						INNER JOIN vendor v ON v.vendor_id = a.vendor_id
						INNER JOIN owner o ON v.owner = o.owner_abbrivation
						INNER JOIN customer_master cm ON dd.customer_master_master_id = cm.customer_master_master_id
						INNER JOIN vend_contact vc ON v.vendor_id = vc.vendor_id and vc.master = 'Y'
						INNER JOIN contact vcc ON vcc.contact_id = vc.contact_id
					WHERE o.owner_id NOT IN (1, 2, 3) AND dd.date_delivered <= ?
						$whereClause
					GROUP BY dd.upc, k, label_country

					UNION DISTINCT

					SELECT dsd.upc, o.owner_id as k,
						if(vcc.orchard_country is null or vcc.orchard_country ='' or vcc.orchard_country=0,0,vcc.orchard_country) as label_country
					FROM accountingflat.dig_sales_detail dsd
						INNER JOIN dig_sales ds ON ds.statement_id = dsd.statement_id
						INNER JOIN releases r ON r.upc = dsd.upc
						INNER JOIN artist_info a ON a.artist_id = r.artist_id
						INNER JOIN vendor v ON v.vendor_id = a.vendor_id
						INNER JOIN owner o ON v.owner = o.owner_abbrivation
						INNER JOIN customer_master cm ON ds.dms_customer_id = cm.customer_id
						INNER JOIN vend_contact vc ON v.vendor_id = vc.vendor_id and vc.master = 'Y'
						INNER JOIN contact vcc ON vcc.contact_id = vc.contact_id
					WHERE o.owner_id NOT IN (1, 2, 3) AND dsd.date <= ?
						$whereClause
					GROUP BY dsd.upc, k, label_country
				) x INNER JOIN track t ON t.upc = x.upc
				GROUP BY x.k, x.label_country";
			$data_params = [];
			$data_params[] = $curr_period_date_tmp;
			if (count($whereClauseArr)) {
			    foreach ($whereClauseArr as $key => $value) {
			        $data_params[] = current($value);
			    }
			}
			$data_params[] = $curr_period_date_tmp;
			if (count($whereClauseArr)) {
			    foreach ($whereClauseArr as $key => $value) {
			        $data_params[] = current($value);
			    }
			}
			$result = $pdoWrapperObj->executeQuery($sql, $data_params);
			if ($result === false) {
			    show_pdo_error($sql, $pdoWrapperObj->getLastStatement());
			}
			foreach ($result as $row) {
			    $region_availability[$row['k']][$row['label_country']][$period]['total_track_available'] = $row['availability'];
			}
			$sql = "SELECT count(distinct t.id) as availability, x.sales_region, x.k, x.label_country
				FROM (
					SELECT dd.upc, if (cm.territory = 1, 'US', if (cm.territory IN (" . implode(', ', array_fill(0, count($eu_countries), '?')) . "), 'EUR', 'ROW')) as sales_region, o.owner_id as k,
						if(vcc.orchard_country is null or vcc.orchard_country ='' or vcc.orchard_country=0,0,vcc.orchard_country) as label_country
					FROM delivery_history dd
						INNER JOIN releases r ON r.upc = dd.upc
						INNER JOIN artist_info a ON a.artist_id = r.artist_id
						INNER JOIN vendor v ON v.vendor_id = a.vendor_id
						INNER JOIN owner o ON v.owner = o.owner_abbrivation
						INNER JOIN customer_master cm ON dd.customer_master_master_id = cm.customer_master_master_id
						INNER JOIN vend_contact vc ON v.vendor_id = vc.vendor_id and vc.master = 'Y'
						INNER JOIN contact vcc ON vcc.contact_id = vc.contact_id
					WHERE o.owner_id NOT IN (1, 2, 3) AND dd.date_delivered <= ?
						$whereClause
					GROUP BY dd.upc, k, sales_region, label_country

					UNION DISTINCT

					SELECT dsd.upc, if (cm.territory = 1, 'US', if (cm.territory IN (" . implode(', ', array_fill(0, count($eu_countries), '?')) . "), 'EUR', 'ROW')) as sales_region, o.owner_id as k,
						if(vcc.orchard_country is null or vcc.orchard_country ='' or vcc.orchard_country=0,0,vcc.orchard_country) as label_country
					FROM accountingflat.dig_sales_detail dsd
						INNER JOIN dig_sales ds ON ds.statement_id = dsd.statement_id
						INNER JOIN releases r ON r.upc = dsd.upc
						INNER JOIN artist_info a ON a.artist_id = r.artist_id
						INNER JOIN vendor v ON v.vendor_id = a.vendor_id
						INNER JOIN owner o ON v.owner = o.owner_abbrivation
						INNER JOIN customer_master cm ON ds.dms_customer_id = cm.customer_id
						INNER JOIN vend_contact vc ON v.vendor_id = vc.vendor_id and vc.master = 'Y'
						INNER JOIN contact vcc ON vcc.contact_id = vc.contact_id
					WHERE o.owner_id NOT IN (1, 2, 3) AND dsd.date <= ?
						$whereClause
					GROUP BY dsd.upc, k, sales_region, label_country
				) x INNER JOIN track t ON t.upc = x.upc
				GROUP BY x.k, x.label_country, x.sales_region";
			$data_params = [];
			foreach ($eu_countries as $country) {
			    $data_params[] = $country;
			}
			$data_params[] = $curr_period_date_tmp;
			if (count($whereClauseArr)) {
			    foreach ($whereClauseArr as $key => $value) {
			        $data_params[] = current($value);
			    }
			}
			foreach ($eu_countries as $country) {
			    $data_params[] = $country;
			}
			$data_params[] = $curr_period_date_tmp;
			if (count($whereClauseArr)) {
			    foreach ($whereClauseArr as $key => $value) {
			        $data_params[] = current($value);
			    }
			}
			$result = $pdoWrapperObj->executeQuery($sql, $data_params);
			if ($result === false) {
			    show_pdo_error($sql, $pdoWrapperObj->getLastStatement());
			}
			foreach ($result as $row) {
			    if (isset($mainarray[$row['k']][$row['label_country']][$period][$row['sales_region']]['track_count'])) {
			        $mainarray[$row['k']][$row['label_country']][$period][$row['sales_region']]['track_available'] = $row['availability'];
			    }
			}
			if($disp_sales_within_terr){
				$sql = "SELECT count(distinct t.id) as availability, x.sales_region, x.label_country, x.k
					FROM (
						SELECT dd.upc,cm.territory as sales_region, o.owner_id as k,
							if(vcc.orchard_country is null or vcc.orchard_country ='' or vcc.orchard_country=0,0,vcc.orchard_country) as label_country
						FROM delivery_history dd
							INNER JOIN releases r ON r.upc = dd.upc
							INNER JOIN artist_info a ON a.artist_id = r.artist_id
							INNER JOIN vendor v ON v.vendor_id = a.vendor_id
							INNER JOIN owner o ON v.owner = o.owner_abbrivation
							INNER JOIN customer_master cm ON dd.customer_master_master_id = cm.customer_master_master_id
							INNER JOIN vend_contact vc ON v.vendor_id = vc.vendor_id and vc.master = 'Y'
							INNER JOIN contact vcc ON vcc.contact_id = vc.contact_id
						WHERE o.owner_id NOT IN (1, 2, 3) AND dd.date_delivered <= ?
							$whereClause
						GROUP BY dd.upc, k, label_country, sales_region

						UNION DISTINCT

						SELECT dsd.upc, cm.territory as sales_region, o.owner_id as k,
							if(vcc.orchard_country is null or vcc.orchard_country ='' or vcc.orchard_country=0,0,vcc.orchard_country) as label_country
						FROM accountingflat.dig_sales_detail dsd
							INNER JOIN dig_sales ds ON ds.statement_id = dsd.statement_id
							INNER JOIN releases r ON r.upc = dsd.upc
							INNER JOIN artist_info a ON a.artist_id = r.artist_id
							INNER JOIN vendor v ON v.vendor_id = a.vendor_id
							INNER JOIN owner o ON v.owner = o.owner_abbrivation
							INNER JOIN customer_master cm ON ds.dms_customer_id = cm.customer_id
							INNER JOIN vend_contact vc ON v.vendor_id = vc.vendor_id and vc.master = 'Y'
							INNER JOIN contact vcc ON vcc.contact_id = vc.contact_id
						WHERE o.owner_id NOT IN (1, 2, 3) AND dsd.date <= ?
							$whereClause
						GROUP BY dsd.upc, k, label_country, sales_region
					) x INNER JOIN track t ON t.upc = x.upc
					GROUP BY x.k, x.label_country, x.sales_region";
				$data_params = [];
				$data_params[] = $curr_period_date_tmp;
				if (count($whereClauseArr)) {
				    foreach ($whereClauseArr as $key => $value) {
				        $data_params[] = current($value);
				    }
				}
				$data_params[] = $curr_period_date_tmp;
				if (count($whereClauseArr)) {
				    foreach ($whereClauseArr as $key => $value) {
				        $data_params[] = current($value);
				    }
				}
				$result = $pdoWrapperObj->executeQuery($sql, $data_params);
				if ($result === false) {
				    show_pdo_error($sql, $pdoWrapperObj->getLastStatement());
				}
				foreach ($result as $row) {
				    if (isset($mainarray[$row['k']][$row['label_country']][$period][$row['sales_region']]['track_count'])) {
				        $mainarray[$row['k']][$row['label_country']][$period][$row['sales_region']]['track_available'] = $row['availability'];
				    }
				}
			}

			foreach ($mainarray as $k => $country_ids) {
				foreach ($country_ids as $country_id => $periods) {
					foreach ($periods as $period => $regions) {
						foreach ($regions as $region => $sales) {
							if (!$disp_sales_within_terr and is_numeric($region)) {
								continue;
							}
							$displayArr[$k.'_'.$country_id][$period.'_'.$region.'_pct_active'] = $sales['track_available'] ? $sales['track_count']/$sales['track_available'] : 0;
							$displayArr[$k.'_'.$country_id][$period.'_total_track_available'] = $region_availability[$k][$country_id][$period]['total_track_available'];
						}
					}
				}
			}
		}
	}

	//echo "<pre>";
	//print_r($region_sales);
	//print_r($mainarray);
	//print_r($displayArr);
	//echo "</pre>";

	//add the total fields here
	if (count($displayArr)) {
		foreach($displayArr as $k=>$subarray){
			foreach($period_arr as $period_end_date){
				if ($pd1_select == 'month-year') {
					$period = date('n-Y', strtotime($period_end_date));
				}
				else {
					$period = ConvertDateToQuarterString($period_end_date);
				}
				if($report_type == "avg_sales_per_track"){
					$total_avg_sales = ($subarray[$period.'_total_track_count'] ? ($subarray[$period.'_total_sales']/$subarray[$period.'_total_track_count']):0);
					$displayArr[$k][$period.'_total_avg_sales'] = $total_avg_sales;
				}
				elseif($report_type == "pct_of_active_tracks"){
					$total_percent = ($subarray[$period.'_total_track_available']?$subarray[$period.'_total_track_count']/$subarray[$period.'_total_track_available']:0);
					$displayArr[$k][$period.'_total_track_available'] = $total_percent;
				}
			}
		}
	}

	switch ($sort_by) {
		case 'name':
			$displayArr = multisort($displayArr,'name',($sort_desc ? false : true),1);
			break;
		case 'partner_territory':
			$displayArr = multisort($displayArr,'partner_territory',($sort_desc ? false : true),1);
			break;
		default:
			$order_by = 'name';
			$displayArr = multisort($displayArr,'name',($sort_desc ? false : true),1);
			$displayArr = multisort($displayArr,'partner_territory',($sort_desc ? false : true),1);
	}

	$varTable = '
			<table '.($no_url ? 'border=1' : 'class=data').'>
			<tr><th rowspan="2">'.($no_url ? "Partner Territory" : sortColumnHeader('Partner Territory', 'partner_territory', $url)).'</th>
				<th rowspan="2" class="center">'.($no_url ? ('Partner Name') : sortColumnHeader(('Partner Name'), 'name', $url)).'</th>';

	if ($pd1_select == 'month-year') {
		$temporary_period_array = $period_arr;
	}else{
		$temporary_period_array = array();
		foreach ($period_arr as $period){
			$temporary_period_array[] = ConvertDateToQuarterString($period);
		}
		$temporary_period_array = array_unique($temporary_period_array);
	}
	foreach($temporary_period_array as $period_end_date){
			$period = ($pd1_select=='month-year') ? date('n-Y', strtotime($period_end_date)) : $period_end_date;
			$period_colspan = count($region_country_arr[$period] ?? []);
			$colspan = $disp_sales_within_terr ? $period_colspan+3 : 3;
			$colspan += ($report_type != "pct_of_sales_compared" && $report_type != "pct_of_active_tracks") ? 1 : 0;
			$varTable .= '
					<th colspan="'.$colspan.'" class="center">'.$period.'</th>';
	}

	//---------calculate the colspan------
	$varTable .= '
			</tr>
			<tr>';
	foreach ($temporary_period_array as $period_end_date) {
		if ($pd1_select == 'month-year') {
			$period = date('n-Y', strtotime($period_end_date));
		}
		else {
			$period = ($period_end_date);
		}
		$varTable .='
				<th class="center">US</th>
				<th class="center">EUR</th>
				<th class="center">ROW</th>';
		if ($disp_sales_within_terr){
			if(count($region_country_arr[$period])){
				$tmp_country = array();
				foreach ($region_country_arr[$period] as $country_id=>$countries) {
					$varTable .= '<th class="center">' . $countries . '</th>';
				}
			}
		}
		if($report_type != "pct_of_sales_compared" && $report_type != "pct_of_active_tracks"){
			$varTable .='
				<th class="center"><b>Total</b></th>';
		}
	}
	$varTable .= '
			</tr>';
	$rci = 0;

	if (count($displayArr)) {
		foreach($displayArr as $k=>$subarray){
			$temp_carep = explode('_',$k);
			$temp_carep = $temp_carep[0];
			$row_class = (++$rci & 1) ? '' : 'alt';
			$varTable .= '
			<tr class="'.$row_class.'">
				<td>'.$subarray['partner_territory'].'</td>
				<td>'.$subarray['name'].'</td>';
			foreach ($temporary_period_array as $period_end_date){
				if ($pd1_select == 'month-year') {
					$period = date('n-Y', strtotime($period_end_date));
				}else{
					$period = $period_end_date;
				}
				if($report_type=="total_gross_sales"){
					$varTable .= '
				<td bgcolor="#CCFFFF" align="right">'.money_format('%2n',$subarray[$period.'_US']).'</td>
				<td bgcolor="#CCFFFF" align="right">'.money_format('%2n',$subarray[$period.'_EUR']).'</td>
				<td bgcolor="#CCFFFF" align="right">'.money_format('%2n',$subarray[$period.'_ROW']).'</td>';
					if ($disp_sales_within_terr){
						if(count($region_country_arr[$period])){
							foreach ($region_country_arr[$period] as $country_id=>$countries) {
								$varTable .= '<td align="right">'.money_format('%2n',$subarray[$period.'_'.$country_id]).'</td>';
							}
						}
					}
					$varTable .= '
				<td align="right"><b>'.money_format('%2n',$subarray[$period.'_total_sales']).'</b></td>';
				}
				elseif($report_type == "avg_sales_per_track"){
					$varTable .= '
				<td bgcolor="#CCFFFF" align="right">'.money_format('%2n',$subarray[$period.'_US_avg']).'</td>
				<td bgcolor="#CCFFFF" align="right">'.money_format('%2n',$subarray[$period.'_EUR_avg']).'</td>
				<td bgcolor="#CCFFFF" align="right">'.money_format('%2n',$subarray[$period.'_ROW_avg']).'</td>';
					if ($disp_sales_within_terr){
						if(count($region_country_arr[$period])){
							foreach ($region_country_arr[$period] as $country_id=>$countries) {
								$varTable .= '
					<td align="right">'.money_format('%2n',$subarray[$period.'_'.$country_id.'_avg']).'</td>';
							}
						}
					}
					$varTable .='
				<td align="right"><b>'.money_format('%2n',$subarray[$period.'_total_avg_sales']).'</b></td>';
				}
				elseif($report_type == "pct_of_active_tracks"){
					$varTable .= '
				<td bgcolor="#CCFFFF" align="right">'.number_format($subarray[$period.'_US_pct_active']*100,2).'%</td>
				<td bgcolor="#CCFFFF" align="right">'.number_format($subarray[$period.'_EUR_pct_active']*100,2).'%</td>
				<td bgcolor="#CCFFFF" align="right">'.number_format($subarray[$period.'_ROW_pct_active']*100,2).'%</td>';
					if ($disp_sales_within_terr){
						if(count($region_country_arr[$period])){
							foreach ($region_country_arr[$period] as $country_id=>$countries) {
								$varTable .= '
					<td align="right">'.number_format($subarray[$period.'_'.$country_id.'_pct_active']*100,2).'%</td>';
							}
						}
					}
				}
				elseif($report_type == "pct_of_sales_compared"){
					$varTable .='
				<td bgcolor="#CCFFFF" align="right">'.number_format($subarray[$period.'_US_sales_pct']*100,2).'%</td>
				<td bgcolor="#CCFFFF" align="right">'.number_format($subarray[$period.'_EUR_sales_pct']*100,2).'%</td>
				<td bgcolor="#CCFFFF" align="right">'.number_format($subarray[$period.'_ROW_sales_pct']*100,2).'%</td>';
					if ($disp_sales_within_terr){
						if(count($region_country_arr[$period])){
							foreach ($region_country_arr[$period] as $country_id=>$countries) {
								$varTable .= '
					<td align="right">'.number_format($subarray[$period.'_'.$country_id.'_sales_pct']*100,2).'%</td>';
							}
						}
					}
				}
			}
			$varTable .= '
			</tr>';
		}
	}
	else {
		$varTable .='
			<tr><td colspan=6>No data found.</td>
			</tr>';
	}
	$varTable .='
			</table>';
?>