<?
	require_once __DIR__ . '/../../../../vendor/autoload.php';
	use Orchard\Mysqli\QueryData;
	use Orchard\Mysqli\Wrapper as MysqliWrapper;
	$wrapperObj = new MysqliWrapper($mysqli);
	if(!empty($start_year) )
	{
		$start_timestamp = strtotime(ConvertQuarterToDate("start", $start_quarter, $start_year, "Y-m-d"));
	}
	else
		$start_timestamp = strtotime(ConvertQuarterToDate("start", 1, date("Y"), "Y-m-d"));

	if(!empty($end_year))
		$end_timestamp = strtotime(ConvertQuarterToDate("end", $end_quarter, $end_year, "Y-m-d"));
	else
		$end_timestamp = strtotime(ConvertQuarterToDate("end", ceil(date("m")/3), date("Y"), "Y-m-d"));


	//get the names for the dms masters
	$dataParams = new QueryData();
	$sql = 'SELECT cmm.customer_master_master_id, cmm.customer_name
	    FROM customer_master_master cmm
	    WHERE cmm.customer_master_master_id
	        IN  ';

	if (isset($dms_master_list[$report_type][$region])) {
	    $sql .= '(' . implode(',', array_fill(0, count($dms_master_list[$report_type][$region]), '?')) . ')';
	    foreach ($dms_master_list[$report_type][$region] as $val) {
	        $dataParams->addDataParam('i', $val);
	    }
	} else {
	    $sql .= '(0)';
	}
	$dms_name_result = $wrapperObj->executeQuery($sql, $dataParams);
	if ($dms_name_result === false) {
	    showierror($sql);
	}
	$dms_names = array();
	foreach ($dms_name_result as $dms_name_row) {
		$dms_names[$dms_name_row["customer_master_master_id"]] = $dms_name_row["customer_name"];
	}
	$dms_names["others"] = "All Others";
	$start_timestamp_year = date('Y', $start_timestamp);
	$start_timestamp_month = date('m', $start_timestamp);
	$end_timestamp_year = date('Y', $end_timestamp);
	$end_timestamp_month = date('m', $end_timestamp);
	// get the plan numbers
	$sql ='SELECT rp.dms_master_master_id, rp.year, rp.month, sum(rp.total) as total
	    FROM revenue_plan rp
	    WHERE 1 AND rp.region = ?
	        AND (rp.year > ? OR (rp.year = ? AND rp.month >= ?))
	        AND (rp.year < ? OR (rp.year = ? AND rp.month <= ?))
	        AND rp.total_type = ?
	    GROUP BY rp.dms_master_master_id, rp.year, rp.month';
	$dataParams = new QueryData();
	$dataParams->addDataParam('s', $region);
	$dataParams->addDataParam('i', $start_timestamp_year);
	$dataParams->addDataParam('i', $start_timestamp_year);
	$dataParams->addDataParam('i', $start_timestamp_month);
	$dataParams->addDataParam('i', $end_timestamp_year);
	$dataParams->addDataParam('i', $end_timestamp_year);
	$dataParams->addDataParam('i', $end_timestamp_month);
	$dataParams->addDataParam('s', $report_type);
	$plan_result = $wrapperObj->executeQuery($sql, $dataParams);
	if ($plan_result === false) {
	    showierror($sql);
	}

	$result = array();

	foreach ($plan_result as $plan_row) {
		$year = $plan_row["year"];
		$month = $plan_row["month"];
		$amount = $plan_row["total"];
		$quarter = $year ."Q".ceil($month/3);
		$period = $year."_". $month;

		if(in_array($plan_row["dms_master_master_id"], $dms_master_list[$report_type][$region]))
			$curr_master_id = $plan_row["dms_master_master_id"];
		else
			$curr_master_id = "others";

		$result[$curr_master_id][$period]["plan"] = isset($result[$curr_master_id][$period]["plan"])?$result[$curr_master_id][$period]["plan"]:0;
		$result[$curr_master_id][$period]["plan"] += $amount;
		$result[$curr_master_id][$quarter]["plan"] = isset($result[$curr_master_id][$quarter]["plan"])?$result[$curr_master_id][$quarter]["plan"]:0;
		$result[$curr_master_id][$quarter]["plan"] += $amount;

	}

	// get the actual totals
	$sql = 'SELECT cmm.customer_master_master_id, cmm.customer_name, crd.year, crd.month, cr.check_date, sum(crd.total) as total
	    FROM check_receivable cr
	        INNER JOIN check_receivable_detail crd on cr.id = crd.check_id
	        INNER JOIN customer_master cm on cm.customer_id = cr.customer_id
	        INNER JOIN customer_master_master cmm on cmm.customer_master_master_id = cm.customer_master_master_id
	    WHERE 1 ' .get_where_clause($region) . '
	        AND (crd.year > ? OR (crd.year = ? AND crd.month >= ?))
	        AND (crd.year < ? OR (crd.year = ? AND crd.month <= ?))
	        AND crd.paidfor = ?
	    GROUP BY cmm.customer_master_master_id, crd.year, crd.month';
	$dataParams = new QueryData();
	$dataParams->addDataParam('i', $start_timestamp_year);
	$dataParams->addDataParam('i', $start_timestamp_year);
	$dataParams->addDataParam('i', $start_timestamp_month);
	$dataParams->addDataParam('i', $end_timestamp_year);
	$dataParams->addDataParam('i', $end_timestamp_year);
	$dataParams->addDataParam('i', $end_timestamp_month);
	$dataParams->addDataParam('s', $report_type);
	$revenue_result = $wrapperObj->executeQuery($sql, $dataParams);
	if ($revenue_result === false) {
	    showierror($sql);
	}

	foreach ($revenue_result as $revenue_row) {
		$year = $revenue_row["year"];
		$month = $revenue_row["month"];
		$amount = $revenue_row["total"];
		$quarter = $year ."Q".ceil($month/3);

		$period = $year."_". $month;
		if(in_array($revenue_row["customer_master_master_id"], $dms_master_list[$report_type][$region]))
			$curr_master_id =$revenue_row["customer_master_master_id"];
		else
			$curr_master_id = "others";

		$result[$curr_master_id][$period]["actual"] = isset($result[$curr_master_id][$period]["actual"])?$result[$curr_master_id][$period]["actual"]:0;
		$result[$curr_master_id][$period]["actual"] += $amount;
		$result[$curr_master_id][$quarter]["actual"] = isset($result[$curr_master_id][$quarter]["actual"])?$result[$curr_master_id][$quarter]["actual"]:0;
		$result[$curr_master_id][$quarter]["actual"] += $amount;
	}

	$curr_timestamp = $start_timestamp;

	$planHeader = "";
	$columnCount = 0;
	while($curr_timestamp <= $end_timestamp)
	{
		$year = date("Y", $curr_timestamp);
		$month = date("m", $curr_timestamp);
		$planHeader .= "<th>".date("M-Y", $curr_timestamp)."</th>";
		$columnCount++;
		if($month%3 == 0)
		{
			$planHeader .= "<th>$year Q ".floor($month/3)."</th>";
			$columnCount++;
		}

		$curr_timestamp = strtotime("1 month", $curr_timestamp);
	}

	$varData = <<<HEREDOC
			<div class="panelContent fullReport">
			<div class="panelSummary">
			<table class="form full">
				<tr>
					<th>Period:</th>
					<td>
HEREDOC;
	$varData .= date("Y-m-d", $start_timestamp) . " through " . date("Y-m-d", $end_timestamp);
	$varData .= <<< HEREDOC
					</td>
					<th>Report Type:</th>
					<td>
HEREDOC;
	$varData .= ucfirst($report_type);
	$varData .= <<<HEREDOC
					</td>
					<th>Region:</th>
					<td>$region</td>
				</tr>
			</table>
			</div>
			<table class="data">
				<tr>
					<th>&nbsp;</th>
					<th colspan='$columnCount' class='center'>Plan</th>
					<th colspan='$columnCount' class='center'>Actual</th>
					<th colspan='2' class='center'>Variance</th>
				</tr>
				<tr>
					<th>&nbsp;</th>
					$planHeader
					$planHeader
					<th>&nbsp;</th>
					<th>&nbsp;</th>
				</tr>
HEREDOC;
	$totalDMS = sizeof($dms_master_list[$report_type][$region]) +1;
	for($i=0;$i<$totalDMS;$i++)
	{
		if($i!=($totalDMS-1))
			$dms_master_id = $dms_master_list[$report_type][$region][$i];
		else
			$dms_master_id = "others";

		$row_class = ($row_class == '') ? 'alt' : '';
		$varData .= "<tr class='$row_class'>";
		$varData .= "<th>{$dms_names[$dms_master_id]}</th>";

		$curr_timestamp = $start_timestamp;

		$planRow = "";
		$actualRow = "";
		$plan_row_total = 0;
		$actual_row_total = 0;

		while($curr_timestamp <= $end_timestamp)
		{
			$year = date("Y", $curr_timestamp);
			$month = date("m", $curr_timestamp);
			$curr_period = date("Y_n", $curr_timestamp);
			$plan_amount = isset($result[$dms_master_id][$curr_period]["plan"])?$result[$dms_master_id][$curr_period]["plan"]:0;
			$actual_amount = isset($result[$dms_master_id][$curr_period]["actual"])?$result[$dms_master_id][$curr_period]["actual"]:0;
			$plan_row_total += $plan_amount;
			$actual_row_total += $actual_amount;

			$planRow .= "<td class='right'>".money_format("%.2n",$plan_amount)."</td>";
			$actualRow .="<td class='right'>" .((!$get_excel)?"<a href='revenue_report_detail.php?report_type=$report_type&region=$region&year=$year&month=$month&dms_master_id=$dms_master_id'>".money_format("%.2n",$actual_amount)."</a>":money_format("%.2n",$actual_amount))."</td>";

			$col_total[$curr_period]["plan"] = isset($col_total[$curr_period]["plan"])?$col_total[$curr_period]["plan"]:0;
			$col_total[$curr_period]["plan"] += $plan_amount;
			$col_total[$curr_period]["actual"] = isset($col_total[$curr_period]["actual"])?$col_total[$curr_period]["actual"]:0;
			$col_total[$curr_period]["actual"] += $actual_amount;

			if($month%3 == 0)
			{
				$qtr = ceil($month/3);
				$quarter = $year.'Q'.$qtr;
				$planRow .= "<th class='right'>".money_format("%.2n",$result[$dms_master_id][$quarter]["plan"])."</th>";
				$actualRow .= "<th class='right'>".((!$get_excel)?"<a href='revenue_report_detail.php?report_type=$report_type&region=$region&year=$year&quarter=$qtr&dms_master_id=$dms_master_id'>".money_format("%.2n",$result[$dms_master_id][$quarter]["actual"])."</a>":money_format("%.2n",$result[$dms_master_id][$quarter]["actual"]))."</th>";

				$col_total[$quarter]["plan"] = isset($col_total[$quarter]["plan"])?$col_total[$quarter]["plan"]:0;
				$col_total[$quarter]["plan"] += $result[$dms_master_id][$quarter]["plan"];
				$col_total[$quarter]["actual"] = isset($col_total[$quarter]["actual"])?$col_total[$quarter]["actual"]:0;
				$col_total[$quarter]["actual"] += $result[$dms_master_id][$quarter]["actual"];
			}

			$curr_timestamp = strtotime("1 month", $curr_timestamp);
		}
		$variance = $actual_row_total - $plan_row_total;
		$variance_per = ($plan_row_total > 0)?number_format(($actual_row_total - $plan_row_total)/$plan_row_total*100, "2")." %":"-";
		$varData .= "$planRow $actualRow
						<th class='right'>".money_format("%(.2n", $variance)."</th>
						<th class='right'>$variance_per </th>
					</tr>";
	}
		$varData .= "<tr>
						<th>Total</th>";

	$curr_timestamp = $start_timestamp;
	$planRow = "";
	$actualRow = "";
	$plan_row_total = 0;
	$actual_row_total = 0;

	while($curr_timestamp <= $end_timestamp)
	{
		$year = date("Y", $curr_timestamp);
		$month = date("m", $curr_timestamp);
		$curr_period = date("Y_n", $curr_timestamp);
		$planRow .= "<th class='right'>".money_format("%.2n",$col_total[$curr_period]["plan"])."</th>";
		$actualRow .= "<th class='right'>".((!$get_excel)?"<a href='revenue_report_detail.php?report_type=$report_type&region=$region&year=$year&month=$month'>".money_format("%.2n",$col_total[$curr_period]["actual"])."</a>":money_format("%.2n",$col_total[$curr_period]["actual"]))."</th>";
		$plan_row_total += $col_total[$curr_period]["plan"];
		$actual_row_total += $col_total[$curr_period]["actual"];

		if($month%3 == 0)
		{
			$quarter = $year.'Q'.ceil($month/3);
			$planRow .= "<th class='right'>".money_format("%.2n",$col_total[$quarter]["plan"])."</th>";
			$actualRow .= "<th class='right'>".((!$get_excel)?"<a href='revenue_report_detail.php?report_type=$report_type&region=$region&year=$year&quarter=$qtr'>".money_format("%.2n",$col_total[$quarter]["actual"])."</a>":money_format("%.2n",$col_total[$quarter]["actual"]))."</th>";
		}


		$curr_timestamp = strtotime("1 month", $curr_timestamp);
	}
	$variance = $actual_row_total - $plan_row_total;
	$variance_per = ($plan_row_total > 0)?number_format(($actual_row_total - $plan_row_total)/$plan_row_total*100, "2")." %":"-";
	$varData .= "$planRow $actualRow
					<th class='right'>".money_format("%(.2n", $variance)."</th>
					<th class='right'>$variance_per </th>
				</tr>";


	$varData .= <<<HEREDOC
				</tr>
			</table>
			</div>
HEREDOC;
?>