<?php
	require_once (__DIR__ . '/../../../../vendor/autoload.php');
	use Orchard\Mysqli\QueryData;
	use Orchard\Mysqli\Wrapper as MysqliWrapper;
	$wrapperObj = new MysqliWrapper($mysqli);
	$no_url				= isset($no_url)		? $no_url					: false;
	$page				= ($page && is_numeric($page)) ? $page : 1;
	$sort_by			= isset($sort_by)		? stripslashes($sort_by)	: 'date_added';
	$sort_desc			= ($sort_desc)			? $sort_desc				: 0;
	
	$pageRecord			= ($pageRecord && is_numeric($pageRecord)) ? $pageRecord : 100;
	$varQueryString 	= formatURLQueryString('page,pageRecord', array($_REQUEST));
	$url				= formatURLQueryString('sort_by,sort_desc,page,pageRecord', array($_REQUEST));
	$pageNum			= 0;
	$total				= 0;
	
	//QUARTERLY REPORT
	$curr_qy = ConvertDateToQuarterString(date("Y-m-d"));
	$curr_year = 	 substr($curr_qy, 0,4);
	$curr_quarter  = substr($curr_qy,5,1);
	$qy = QY_Add_Subtract($curr_year."Q".$curr_quarter, "subtract",1);
	
	$start_quarter	= isset($start_quarter)&&trim($start_quarter) 	? trim($start_quarter) 	: (isset($start_year)&&trim($start_year)	? 1 : substr($qy, 5,1));
	$end_quarter 	= isset($end_quarter)&&trim($end_quarter) 		? trim($end_quarter) 	: (isset($end_year)&&trim($end_year)		? 4 : substr($qy, 5,1));
	
	$start_year 	= isset($start_year)&&trim($start_year) 		? trim($start_year) 	: substr($qy, 0,4);
	$end_year 		= isset($end_year)&&trim($end_year) 			? trim($end_year) 		: substr($qy, 0,4);
	
	$data_params = new QueryData();
	$data_params->addDataParam('i', $publisher_id);
	if ($start_year && $start_quarter) {
		$start_period = new quaterly_period($start_year, $start_quarter);
		$date_clause .= ' AND ps.period_id  >= ?';
		$data_params->addDataParam('i', $start_period->getMinPeriodID());
	}
	if ($end_year && $end_quarter) {
		$end_period = new quaterly_period($end_year, $end_quarter);
		$date_clause .= ' AND ps.period_id  <= ?';
		$data_params->addDataParam('i', $end_period->getMaxPeriodID());
	}
	
	function hfa_code_map($orchard_code)
	{
		switch($orchard_code)
		{
			case "DPD":
				return "SP";
				break;
			case "DR":
				return "RR";
				break;
			case "SFF":
				return "FX";
				break;
			case "RB":
				return "RB";
				break;
			default:
				return false;
		}
	}
	
	$sql = "SELECT SQL_CALC_FOUND_ROWS cm.customer_id, cm.name, ps.date_added, CONVERT(CONCAT(pr.year, 'Q', pr.quarter) USING utf8) AS royalty_period,
	    psd.ownership*100 as ownership, psd.royalty, psd.trans_type, psd.qty, psd.royalty_rate, pl.publisher_license_no, t.upc,r.display_upc, t.isrc, t.track_name
	    FROM publishers p
	    INNER JOIN publisher_statement ps ON p.publisher_id = ps.publisher_id
	    INNER JOIN publisher_statement_detail psd ON ps.publisher_statement_id = psd.publisher_statement_id
	    INNER JOIN period pr ON pr.period_id = psd.period_id
	    LEFT JOIN publishers_license pl ON psd.license_no = pl.license_no
	    INNER JOIN track t ON t.id = psd.track_id
	    INNER JOIN releases r ON r.upc = t.upc
	    INNER JOIN customer_master cm ON cm.customer_id = psd.dms_customer_id
	    WHERE p.publisher_id = ?
        " . $date_clause . "
	    ORDER BY ";
	switch ($sort_by) {
		case 'date_added':
			$sql .= 'ps.date_added';
			break;
		case 'royalty_period':
			$sql .= 'royalty_period';
			break;
		case 'publisher_license_no':
			$sql .= 'pl.publisher_license_no';
			break;
		case 'isrc':
			$sql .= 't.isrc';
			break;
		case 'upc':
			$sql .= 't.upc';
			break;
		case 'trans_type':
			$sql .= 'psd.trans_type';
			break;
		case 'track_name':
			$sql .= 't.track_name';
			break;
		case 'ownership':
			$sql .= 'psd.ownership';
			break;
		case 'qty':
			$sql .= 'psd.trans_type';
			break;
		case 'royalty_rate':
			$sql .= 'psd.royalty_rate';
			break;
		case 'royalty':
			$sql .= 'psd.royalty';
			break;
		case 'name':
			$sql .= 'cm.name';
			break;
		default:
			$sql .= 'ps.date_added';
			$sort_by = 'date_added';
	}
	$sql .= ($sort_desc ? ' DESC' : '');
	if (!$no_url) {
	    $sql .= ' LIMIT ?, ?';
	    $data_params->addDataParam('i', (($page - 1) * $pageRecord));
	    $data_params->addDataParam('i', $pageRecord);
	} 
	$result = $wrapperObj->executeQuery($sql, $data_params);
	if ($result === false) {
	    showierror($sql);
	}

	$sql = 'SELECT FOUND_ROWS() AS total';
	$data_params = new QueryData();
	$total_count = $wrapperObj->executeQuery($sql, $data_params);
	if ($total_count === false) {
	    showierror($sql);
	}
	$total_count_row = $total_count[0];
	$total = $total_count_row['total'];
	$pageNum = ceil($total/$pageRecord); 
	
	$varData = '<table' . ($no_url ? ' border=1' : ' class="data"') . '>
			<tr><th>Manufacturer Number</th>
				<th class="center">' . ($no_url ? 'Transaction Date' : sortColumnHeader('Transaction Date', 'date_added', $url)) . '</th>
				<th class="center">' . ($no_url ? 'Royalty Reporting Period' : sortColumnHeader('Royalty Reporting Period', 'royalty_period', $url)) . '</th>
				<th class="center">' . ($no_url ? 'License Number' : sortColumnHeader('License Number', 'publisher_license_no', $url)) . '</th>
				<th class="center">' . ($no_url ? 'ISRC' : sortColumnHeader('ISRC', 'isrc', $url)) . '</th>
				<th class="center">' . ($no_url ? 'UPC' : sortColumnHeader('UPC', 'upc', $url)) . '</th>
				<th class="center">Configuration Code</th>
				<th class="center">' . ($no_url ? 'Song Title' : sortColumnHeader('Song Title', 'track_name', $url)) . '</th>
				<th class="center">Manufcturer Request Number</th>
				<th class="center">Sales Channel</th>
				<th class="center">' . ($no_url ? 'Split' : sortColumnHeader('Split', 'ownership', $url)) . '</th>
				<th class="center">' . ($no_url ? 'Units' : sortColumnHeader('Units', 'qty', $url)) . '</th>
				<th class="center">' . ($no_url ? 'Net Royalty Rate' : sortColumnHeader('Royalty Rate', 'royalty_rate', $url)) . '</th>
				<th class="center">' . ($no_url ? 'Royalty Amount' : sortColumnHeader('Royalty Amount', 'royalty', $url)) . '</th>
				<th class="center">' . ($no_url ? 'Distribution Service' : sortColumnHeader('Distribution Service', 'name', $url)) . '</th>
			</tr>';
	$rci = 0;
	foreach ($result as $row) {
		$row_class = (++$rci & 1) ? '' : 'alt';
		$royalty_period = get_royalty_period($row['royalty_period']); 
		$varData .= '
			<tr class="' . $row_class . '">
				<td>M16106</td>
				<td>' . format_date($row['date_added']) . '</td>
				<td>' . $royalty_period . '</td>
				<td>' . $row['publisher_license_no'] . '</td>
				<td>' . $row['isrc'] . '</td>
				<td>' .($no_url ? '"'.$row['display_upc'] . '"' : $row['display_upc'])  . '</td>
				<td>'.hfa_code_map($row["trans_type"]).'</td>
				<td>' . $row['track_name'] . '</td>
				<td></td>
				<td>'.hfa_code_map($row["trans_type"]).'</td>
				<td>' . number_format($row['ownership'],2) . '</td>
				<td>' . $row['qty'] . '</td>
				<td align="right">' . money_format('%.4n',$row['royalty_rate']) . '</td>
				<td align="right">' . money_format('%.4n',$row['royalty']) . '</td>
				<td>' . $row["name"] . '</td>
			</tr>';
	} 
	$varData .= '
			</table>';
?>