<?
	/**
	 * ***********************************************************************
	 * 08/07/2006 - Paulo Kuong
	 * 	-	File created
	 */
?>
<?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;

	switch ($sort_by){
		case 'company':
			$order_by = 'company';
			break;
		case 'release_name':
			$order_by = 'release_name';
			break;
		case 'artist_name':
			$order_by = "artist_name";
			break;
		case 'isrc':
			$order_by = "isrc";
			break;
		case 'track_name':
			$order_by = "track_name";
			break;
		default:
			$order_by = "company";
			$sort_by ='company';
			$sort_desc = 1;
	}
	
	$data_params = new QueryData();
	if($export_type == "all_content"){
		if($vendor_type == "oms_client"){
		    $sql = 'select oc.oms_client_id as vendor_id, oc.company as company, "oms_client" as vendor_type
		            , ot.artist_name as artist_name, ot.isrc as isrc, ot.track_name as track_name
		            , ot.release_name as release_name, ot.oms_track_id as track_id, "oms_track" as track_type
		        from oms_track ot
		        inner join oms_client oc on ot.vendor_id = oc.oms_client_id and ot.vendor_type = "oms_client"
		        where royalty_collection = "Y" and oc.oms_client_id = ?';
		    $data_params->addDataParam('i', $label_id);
		}elseif($vendor_type == "vendor"){
		    $sql = 'select v.vendor_id as vendor_id, v.company as company, "vendor" as vendor_type
		            , ot.artist_name as artist_name, ot.isrc as isrc, ot.track_name as track_name
		            , ot.release_name as release_name, ot.oms_track_id as track_id, "oms_track" as track_type
		        from oms_track ot
		        inner join vendor v on ot.vendor_id = v.vendor_id and ot.vendor_type = "vendor"
		        where royalty_collection = "Y" and v.vendor_id = ?
		        UNION
		        select v.vendor_id as vendor_id, v.company as company, "vendor" as vendor_type, a.name as artist_name
		            , t.isrc as isrc, t.track_name as track_name, r.release_name as release_name, t.id as track_id
		            , "track" as track_type
		        from track t
		        inner join releases r on r.upc = t.upc
		        inner join artist_info a on a.artist_id = r.artist_id
		        inner join vendor v on v.vendor_id = a.vendor_id
		        where t.royalty_collection = "Y" and v.vendor_id = ?';
		    $data_params->addDataParam('i', $label_id);
		    $data_params->addDataParam('i', $label_id);
		}
	}elseif($export_type == "new_content"){
		if($vendor_type == "oms_client"){
		    $sql = 'select oc.oms_client_id as vendor_id, oc.company as company, "oms_client" as vendor_type
		            , ot.artist_name as artist_name, ot.isrc as isrc, ot.track_name as track_name, ot.release_name as release_name
		            , ot.oms_track_id as track_id, "oms_track" as track_type
		        from oms_track ot
		        inner join oms_client oc on ot.vendor_id = oc.oms_client_id and ot.vendor_type = "oms_client"
		        LEFT JOIN royalty_collection_exports rce ON rce.track_id = ot.oms_track_id AND rce.track_type = "oms_track" AND rce.export_type = ?
		        where ot.royalty_collection = "Y" and oc.oms_client_id = ? and rce.track_id IS NULL';
		    $data_params->addDataParam('s', $format);
		    $data_params->addDataParam('i', $label_id);
		}elseif($vendor_type == "vendor"){
		    $sql = 'select v.vendor_id as vendor_id, v.company as company, "vendor" as vendor_type, ot.artist_name as artist_name
		            , ot.isrc as isrc, ot.track_name as track_name, ot.release_name as release_name, ot.oms_track_id as track_id, "oms_track" as track_type
		        from oms_track ot
		        inner join vendor v on ot.vendor_id = v.vendor_id and ot.vendor_type = "vendor"
		        LEFT JOIN royalty_collection_exports rce ON rce.track_id = ot.oms_track_id AND rce.track_type = "oms_track" AND rce.export_type = ?
		        where ot.royalty_collection = "Y" and v.vendor_id = ? and rce.track_ID IS NULL
		        UNION
		        select v.vendor_id as vendor_id, v.company as company, "vendor" as vendor_type, a.name as artist_name, t.isrc as isrc
		            , t.track_name as track_name, r.release_name as release_name, t.id as track_id, "track" as track_type
		        from track t
		        inner join releases r on r.upc = t.upc
		        inner join artist_info a on a.artist_id = r.artist_id
		        inner join vendor v on v.vendor_id = a.vendor_id
		        LEFT JOIN royalty_collection_exports rce ON rce.track_id = t.id AND rce.track_type = "track" AND rce.export_type = ?
		        where t.royalty_collection = "Y" and v.vendor_id = ? and rce.track_id IS NULL';
		    $data_params->addDataParam('s', $format);
		    $data_params->addDataParam('i', $label_id);
		    $data_params->addDataParam('s', $format);
		    $data_params->addDataParam('i', $label_id);
		}
	}
	
	$sql .= ' ORDER BY ' . $order_by . ($sort_desc ? ' DESC' : '');
	$sql .= !$no_url ? ' LIMIT ?, ?' : '';
	if (!$no_url) {
	    $data_params->addDataParam('i', (($page - 1) * $pageRecord));
	    $data_params->addDataParam('i', $pageRecord);
	}
	$rs = $wrapperObj->executeQuery($sql, $data_params);
	if ($rs === 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);
	
	
	
	$varString = '<table' . ($no_url ? ' border=1' : ' class="data"') . '>
					<tr>';
	$varString .= (($vendor_type == "vendor") ? ('
						<th class="center">'.($no_url ? "Label" : sortColumnHeader('Label', 'company', $url)).'</th>') :
						(($vendor_type == "oms_client") ? ('<th class="center">'.($no_url ? "OMS Client" : sortColumnHeader('OMS Client', 'company', $url)).'</th>') : "")).'
						<th class="center">'.($no_url ? "Artist" : sortColumnHeader('Artist', 'artist_name', $url)).'</th>
						<th class="center">'.($no_url ? "Release" : sortColumnHeader('Release', 'release_name', $url)).'</th>
						<th class="center">'.($no_url ? "Track" : sortColumnHeader('Track', 'track_name', $url)).'</th>
						<th class="center">'.($no_url ? "ISRC" : sortColumnHeader('ISRC', 'isrc', $url)).'</th>
					</tr>
	';
	$rci = 0;
	$track_id_track_type_pair = array();
	if (count($rs)) {
	    foreach ($rs as $row) {
			$track_id_track_type_pair[$row['track_type']][] = $row['track_id'];
			$row_class = (++$rci & 1) ? '' : 'alt';
			$varString .= '<tr class="'.$row_class.'">';
			$varString .= '<td>'.$row['company'].'</td>
						   <td>'.$row['artist_name'].'</td>
						   <td>'.$row['release_name'].'</td>
						   <td>'.$row['track_name'].'</td>
						   <td>'.$row['isrc'].'</td>';
			$varString .= '</tr>';
		}
	}else{
		$varString .= '
				<tr><td colspan="8">No Data Found.</td>
				</tr>';
	}
	$varString .= '</table>';
	
	
	if($save_or_not == "save"){
		if($track_id_track_type_pair['oms_track']){
			foreach($track_id_track_type_pair['oms_track'] as $values){
			    $data_params = new QueryData();
			    $insert_history = 'insert into royalty_collection_exports(last_export_date, track_id, creator
			        , export_type, track_type) values(now(), ?, ?, ?, "oms_track")';
			    $data_params->addDataParam('i', $values);
			    $data_params->addDataParam('i', $_SESSION['S_USERID']);
			    $data_params->addDataParam('s', $format);
			    $insert_history_rs = $wrapperObj->executeQuery($insert_history, $data_params, MysqliWrapper::RETURN_INSERT_ID);
			    if ($insert_history_rs === false) {
			        showierror($insert_history);
			    }
			}
		}
		if($track_id_track_type_pair['track']){
			foreach($track_id_track_type_pair['track'] as $values){
			    $data_params = new QueryData();
			    $insert_history = 'insert into royalty_collection_exports(last_export_date, track_id, creator
			        , export_type, track_type) values(now(), ?, ?, ?, "track")';
			    $data_params->addDataParam('i', $values);
			    $data_params->addDataParam('i', $_SESSION['S_USERID']);
			    $data_params->addDataParam('s', $format);
			    $insert_history_rs = $wrapperObj->executeQuery($insert_history, $data_params, MysqliWrapper::RETURN_INSERT_ID);
			    if ($insert_history_rs === false) {
			        showierror($insert_history);
			    }
			}
		}
	}
?>