<?
	require_once(__DIR__ . '/../../../../../vendor/autoload.php');
	$pdoWrapperObj = getPDOQueryWrapper();
	$dataParams = [];

	$report_data = '';

	if(is_array($labels)){
		$labels_arr = $labels;
	}else{
		$labels = trim(str_replace(",","",str_replace(" ","",str_replace("\r",'',$labels))));
		$labels_arr = explode("\n",$labels);
	}
	
	if(is_array($clip_codes)){
		$clip_codes_arr = $clip_codes;
	}else{
		$clip_codes = trim(str_replace(",","",str_replace(" ","",str_replace("\r",'',$clip_codes))));
		$clip_codes_arr = explode("\n",$clip_codes);
	}

	$wrong_labels_input = false;
	foreach($labels_arr as $value){
		if(!is_numeric(trim($value)))
			$wrong_labels_input = true;
	}
	
	$wrong_clip_code_input = false;
	foreach($clip_codes_arr as $value){
		if(!preg_match("/^[0-9]{12,13}_[0-9]+_[0-9]+_[0-9]+$/",trim($value)))
			$wrong_clip_code_input = true;
	}

	if (is_array($dms_ids) and count($dms_ids)) {
		foreach ($dms_ids as $dms_id) {
		    $dms_clause[] = 'find_in_set(?, ro.dms_list)';
		    $dataParams[] = $dms_id;
		}
	}
	$sql = 'SELECT t.upc, t.cd, t.track_id, tc.clip_number, tc.clip_title, tc.start_time, tc.end_time, t.id, t.track_name, t.isrc, t.version, t.explicit_lyrics, a.name AS artist_name, r.release_name, r.label, g.genre, if(v.company="" or v.company is null, v.name, v.company) AS label_name, v.vendor_id, GROUP_CONCAT(DISTINCT ro.dms_list) AS dms_list, max(rod.date_cut) as date_cut, max(rod.date_encoded) as date_encoded, max(rod.date_delivered) as date_delivered
		FROM track_clips tc
			INNER JOIN ringtone_order_detail rod ON tc.clip_id = rod.clip_id
			INNER JOIN ringtone_order ro ON ro.ringtone_order_id = rod.ringtone_order_id
			INNER JOIN track t ON t.upc = tc.upc AND t.cd = tc.cd AND t.track_id = tc.track_id
			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
			INNER JOIN genre g ON g.genre_id = r.genre_id
		WHERE v.status = "signed"
			' . ((is_array($dms_ids) and count($dms_ids)) ? 'AND (' . implode(' OR ', $dms_clause) . ')' : '') . '
			' . ($status == 'cut' ? 'AND tc.start_time IS NOT NULL AND tc.end_time IS NOT NULL' : '');
	
	if($show_only_deleted != "on" && $include_deleted != "on"){
		$sql .= ' and r.deletions <> "Y" ';
	}elseif($show_only_deleted == "on" && $include_deleted != "on"){
		$sql .= ' and r.deletions = "Y" ';
	}
	
	switch ($status) {
		case 'cut':
			$sql .= '
				AND rod.date_cut IS NOT NULL';
			break;
		case 'encoded':
			$sql .= '
				AND rod.date_encoded IS NOT NULL';
			break;
		case 'delivered':
			$sql .= '
				AND rod.date_delivered IS NOT NULL';
			break;
	}
	if ($start_date and $end_date) {
		switch ($status) {
			case 'cut':
				$sql .= '
				AND DATE(rod.date_cut) BETWEEN ? AND ?';
				break;
			case 'encoded':
				$sql .= '
				AND DATE(rod.date_encoded) BETWEEN ? AND ?';
				break;
			case 'delivered':
				$sql .= '
				AND DATE(rod.date_delivered) BETWEEN ? AND ?';
				break;
		}
		$dataParams[] = $start_date;
		$dataParams[] = $end_date;
	}
	
	if ($labels && !$wrong_labels_input) {
	    $sql .= ' AND v.vendor_id IN (' . implode(',', array_fill(0, count($labels_arr), '?')) . ')';
	    foreach ($labels_arr as $val) {
	        $dataParams[] = $val;
	    }
	}
	
	if ($clip_codes && !$wrong_clip_code_input) {
		$sql .= ' AND (';
		$i=1;
		foreach ($clip_codes_arr as $v) {
			list($tmp_upc,$tmp_cd,$tmp_track_id,$tmp_clip_number) = explode('_',$v);
			$sql .= (' (t.upc = ? and t.cd = ? and t.track_id = ? and tc.clip_number = ?) ');
			$dataParams[] = $tmp_upc;
			$dataParams[] = $tmp_cd;
			$dataParams[] = $tmp_track_id;
			$dataParams[] = $tmp_clip_number;
			if ($i != count($clip_codes_arr)) {
				$sql .= ' OR ';
			}
			$i++;
		}
		$sql .= ' ) ';
	}
	$sql .='
		GROUP BY t.upc, t.cd, t.track_id, tc.clip_number
		ORDER BY t.upc, t.cd, t.track_id, tc.clip_number';
	$clip_result = $pdoWrapperObj->executeQuery($sql, $dataParams);
	if ($clip_result === false) {
	    show_pdo_error($sql, $pdoWrapperObj->getLastStatement());
	}

	$track_arr = array();
	$clips = array();
	foreach ($clip_result as $clip_row) {
	    $sql = 'SELECT id FROM vendor_contract WHERE vendor_id = ?';
	    $chk_vendor_contract = $pdoWrapperObj->executeQuery($sql, [$clip_row['vendor_id']]);
	    if ($chk_vendor_contract === false) {
	        show_pdo_error($sql, $pdoWrapperObj->getLastStatement());
	    }
	    if (count($chk_vendor_contract)) {
	        $sql = 'SELECT id, territory_carve_out
	            FROM vendor_contract
	            WHERE vendor_id = ?
	            AND cont_start <= CURDATE() 
	            AND (cont_end >= CURDATE() OR cont_end IS NULL OR cont_end = "0000-00-00")
	            ORDER BY cont_end DESC, cont_start DESC LIMIT 0, 1';
	        $vend_contract = $pdoWrapperObj->executeQuery($sql, [$clip_row['vendor_id']]);
	        if ($vend_contract === false) {
	            show_pdo_error($sql, $pdoWrapperObj->getLastStatement());
	        }
	        if (count($vend_contract)) {
	            $vend_contract_row = $vend_contract[0];
	            if (trim($vend_contract_row['territory_carve_out'], ',')) {
	                $dataParams = [];
	                $territory_carve_out = explode(',', $vend_contract_row['territory_carve_out']);
	                $sql = 'select name from country where id in (' . implode(',', array_fill(0, count($territory_carve_out), '?')) . ') order by name';
	                foreach ($territory_carve_out as $val) {
	                    $dataParams[] = $val;
	                }
	                $cntry_result = $pdoWrapperObj->executeQuery($sql, $dataParams);
	                if ($cntry_result === false) {
	                    show_pdo_error($sql, $pdoWrapperObj->getLastStatement());
	                }
	                foreach ($cntry_result as $cntry_row) {
	                    $restrictions[] = $cntry_row['name'];
	                }
	            }
	        }
	    }
		$the_track = $clip_row['upc'] . '_' . $clip_row['cd'] . '_' . $clip_row['track_id'];

		$restrictions = array();
		$sql = 'SELECT c.name AS country
		    FROM release_territory_restriction tr
		    INNER JOIN country c ON c.id = tr.country_id
		    WHERE tr.upc = ?
		    ORDER BY c.name';
		$track_restriction_result = $pdoWrapperObj->executeQuery($sql, [$clip_row['upc']]);
		if ($track_restriction_result === false) {
		    show_pdo_error($sql, $pdoWrapperObj->getLastStatement());
		}
		foreach ($track_restriction_result as $track_restr_row) {
			if (!isset($restrictions[$track_restr_row['country']])) {
				$restrictions[] = $track_restr_row['country'];
			}
		}

		$recording_artists = array();
		$dataParams = [];
		$sql = 'SELECT ta.name
		    FROM track_artist ta
		    INNER JOIN track t ON t.id = ta.track_id
		    WHERE ta.type <> "Composer"
		    AND t.upc = ?
		    AND t.cd = ?
		    AND t.track_id = ?
		    ORDER BY ta.name';
		$dataParams[] = $clip_row['upc'];
		$dataParams[] = $clip_row['cd'];
		$dataParams[] = $clip_row['track_id'];
		$track_restriction_result = $pdoWrapperObj->executeQuery($sql, $dataParams);
		if ($track_restriction_result === false) {
		    show_pdo_error($sql, $pdoWrapperObj->getLastStatement());
		}
		foreach ($track_restriction_result as $track_restr_row) {
			if (!isset($recording_artists[$track_restr_row['name']])) {
				$recording_artists[] = $track_restr_row['name'];
			}
		}

		//Get the dms names
		$dmss = trim($clip_row['dms_list'],",");
		$dmss_arr = array();
		$dmss_array = explode(',', $dmss);

		if ($dmss) {
			$dataParams = [];
			$sql = 'SELECT customer_master_master_id, customer_name FROM customer_master_master WHERE customer_master_master_id IN (' . implode(',', array_fill(0, count($dmss_array), '?')) . ') ORDER BY customer_name';
			foreach ($dmss_array as $val) {
			    $dataParams[] = $val;
			}
			$result = $pdoWrapperObj->executeQuery($sql, $dataParams);
			if ($result === false) {
			    show_pdo_error($sql, $pdoWrapperObj->getLastStatement());
			}
			foreach ($result as $row) {
				$dmss_arr[$row['customer_master_master_id']] = $row['customer_name'];
			}
		}//end if
		if(!count($dmss_arr)){
				$clip_track_code = ($clip_row['clip_number'] ? $the_track . '_' . $clip_row['clip_number'] : $the_track);
				$key = $clip_track_code;
				$clips[$key]['audio_file'] = ($clip_row['clip_number'] ? $the_track . '_' . $clip_row['clip_number'] . '.wav' : '');
				$clips[$key]['label_company'] = $clip_row['label_name'];
				$clips[$key]['artist_name'] = $clip_row['artist_name'];
				$clips[$key]['release_name'] = $clip_row['release_name'];
				$clips[$key]['label'] = $clip_row['label'];
				$clips[$key]['unique_track_id'] = $clip_row['id'];
				$clips[$key]['track_name'] = $clip_row['track_name'];
				$clips[$key]['track_code'] = $the_track;
				$clips[$key]['clip_code'] = ($clip_row['clip_number']) ? $clip_track_code : '';
				$clips[$key]['isrc'] = $clip_row['isrc'];
				$clips[$key]['genre'] = $clip_row['genre'];
				$clips[$key]['start_time'] = $clip_row['start_time'];
				$clips[$key]['end_time'] = $clip_row['end_time'];
				$clips[$key]['clip_title'] = $clip_row['clip_title'];
				$clips[$key]['version'] = $clip_row['version'];
				$clips[$key]['explicit_lyrics'] = $clip_row['explicit_lyrics'];
				$clips[$key]['dms_name'] = '';
				$clips[$key]['restriction'] = $restrictions;
				$clips[$key]['recording_artist'] = $recording_artists;
				$clips[$key]['date_cut'] = $clip_row['date_cut'];
				$clips[$key]['date_encoded'] = $clip_row['date_encoded'];
				$clips[$key]['date_delivered'] = $clip_row['date_delivered'];
		}else{
			if($status=="cut"){
					foreach($dmss_arr as $dms_id=>$dms_name){
						$clip_track_code = ($clip_row['clip_number'] ? $the_track . '_' . $clip_row['clip_number'] : $the_track);
						$key = $clip_track_code;
						$clips[$key]['audio_file'] = ($clip_row['clip_number'] ? $the_track . '_' . $clip_row['clip_number'] . '.wav' : '');
						$clips[$key]['label_company'] = $clip_row['label_name'];
						$clips[$key]['artist_name'] = $clip_row['artist_name'];
						$clips[$key]['release_name'] = $clip_row['release_name'];
						$clips[$key]['label'] = $clip_row['label'];
						$clips[$key]['unique_track_id'] = $clip_row['id'];
						$clips[$key]['track_name'] = $clip_row['track_name'];
						$clips[$key]['track_code'] = $the_track;
						$clips[$key]['clip_code'] = ($clip_row['clip_number']) ? $clip_track_code : '';
						$clips[$key]['isrc'] = $clip_row['isrc'];
						$clips[$key]['genre'] = $clip_row['genre'];
						$clips[$key]['start_time'] = $clip_row['start_time'];
						$clips[$key]['end_time'] = $clip_row['end_time'];
						$clips[$key]['clip_title'] = $clip_row['clip_title'];
						$clips[$key]['version'] = $clip_row['version'];
						$clips[$key]['explicit_lyrics'] = $clip_row['explicit_lyrics'];
						$clips[$key]['dms_name'][] = $dms_name;
						$clips[$key]['restriction'] = $restrictions;
						$clips[$key]['recording_artist'] = $recording_artists;
						$clips[$key]['date_cut'] = $clip_row['date_cut'];
						$clips[$key]['date_encoded'] = $clip_row['date_encoded'];
						$clips[$key]['date_delivered'] = $clip_row['date_delivered'];
					}//end foreach
			}else{
					foreach($dmss_arr as $dms_id=>$dms_name){
						$clip_track_code = ($clip_row['clip_number'] ? $the_track . '_' . $clip_row['clip_number'] : $the_track);
						$key = $clip_track_code . '_' . $dms_id;
						$clips[$key]['audio_file'] = ($clip_row['clip_number'] ? $the_track . '_' . $clip_row['clip_number'] . '.wav' : '');
						$clips[$key]['label_company'] = $clip_row['label_name'];
						$clips[$key]['artist_name'] = $clip_row['artist_name'];
						$clips[$key]['release_name'] = $clip_row['release_name'];
						$clips[$key]['label'] = $clip_row['label'];
						$clips[$key]['unique_track_id'] = $clip_row['id'];
						$clips[$key]['track_name'] = $clip_row['track_name'];
						$clips[$key]['track_code'] = $the_track;
						$clips[$key]['clip_code'] = ($clip_row['clip_number']) ? $clip_track_code : '';
						$clips[$key]['isrc'] = $clip_row['isrc'];
						$clips[$key]['genre'] = $clip_row['genre'];
						$clips[$key]['start_time'] = $clip_row['start_time'];
						$clips[$key]['end_time'] = $clip_row['end_time'];
						$clips[$key]['clip_title'] = $clip_row['clip_title'];
						$clips[$key]['version'] = $clip_row['version'];
						$clips[$key]['explicit_lyrics'] = $clip_row['explicit_lyrics'];
						$clips[$key]['dms_name'] = $dms_name;
						$clips[$key]['restriction'] = $restrictions;
						$clips[$key]['recording_artist'] = $recording_artists;
						$clips[$key]['date_cut'] = $clip_row['date_cut'];
						$clips[$key]['date_encoded'] = $clip_row['date_encoded'];
						$clips[$key]['date_delivered'] = $clip_row['date_delivered'];
					}//end foreach
			}//end if else
		}//end if check for dms arr is empty
	}

	if (!$no_url and !$no_sort) {
		$clips = multisort($clips,$sort_by,($sort_desc ? false : true),1);
		$total = count($clips);
		$pageNum = ceil($total/$pageRecord);
	}

	$report_data = '
			<div class="panelContent fullReport">
				<table' . ($no_url ? ' border=1' : ' class="data"') . '>
				<thead>
				<tr><th>' . (($no_url or $no_sort) ? 'Audio File Name' : sortColumnHeader('Audio File Name', 'audio_file', $url)) . '</th>
					<th>' . (($no_url or $no_sort) ? 'Label/Company' : sortColumnHeader('Label/Company', 'label_company', $url)) . '</th>
					<th>' . (($no_url or $no_sort) ? 'Artist Name' : sortColumnHeader('Artist Name', 'artist_name', $url)) . '</th>
					<th>' . (($no_url or $no_sort) ? 'Release Name' : sortColumnHeader('Release Name', 'release_name', $url)) . '</th>
					<th>' . (($no_url or $no_sort) ? 'Label' : sortColumnHeader('Label', 'label', $url)) . '</th>
					<th>' . (($no_url or $no_sort) ? 'Unique Track ID' : sortColumnHeader('Unique Track ID', 'unique_track_id', $url)) . '</th>
					<th>' . (($no_url or $no_sort) ? 'Track Name' : sortColumnHeader('Track Name', 'track_name', $url)) . '</th>
					<th>' . (($no_url or $no_sort) ? 'Label Track Code' : sortColumnHeader('Label Track Code', 'track_code', $url)) . '</th>
					<th>' . (($no_url or $no_sort) ? 'Label Clip Code' : sortColumnHeader('Label Clip Code', 'clip_code', $url)). '</th>
					<th>' . (($no_url or $no_sort) ? 'ISRC' : sortColumnHeader('ISRC', 'isrc', $url)) . '</th>
					<th>' . (($no_url or $no_sort) ? 'Genre' : sortColumnHeader('Genre', 'genre', $url)) . '</th>
					<th>' . (($no_url or $no_sort) ? 'Version' : sortColumnHeader('Version', 'version', $url)) . '</th>
					<th>' . (($no_url or $no_sort) ? 'Clip Name' : sortColumnHeader('Clip Name', 'clip_title', $url)) . '</th>
					<th>' . (($no_url or $no_sort) ? 'Explicit' : sortColumnHeader('Explicit', 'explicit_lyrics', $url)) . '</th>
					<th>' . (($no_url or $no_sort) ? 'Time In' : sortColumnHeader('Time In', 'start_time', $url)) . '</th>
					<th>' . (($no_url or $no_sort) ? 'Time Out' : sortColumnHeader('Time Out', 'end_time', $url)) . '</th>
					<th>'.(($no_url or $no_sort) ? 'DMS' : ($status=='cut' ? 'DMS': sortColumnHeader('DMS', 'dms_name', $url))).'</th>
					<th>Territory Restrictions</th>
					<th>Track Artists</th>
					<th>Date Cut</th>
					<th>Date Encoded</th>
					<th>Date Delivered</th>
				</tr>
				</thead>
				<tbody>
				';
	if (count($clips)) {
		$rci = 0;
		$subarray = (($no_url or $no_sort) ? $clips : array_slice($clips, (($page - 1) * $pageRecord), $pageRecord));
		foreach ($subarray as $clip_code => $clip) {
			$row_class = (++$rci & 1) ? '' : 'alt';
			$restrictions = implode(',', $clip['restriction']);
			$recording_artists = implode(',', $clip['recording_artist']);

				$report_data .= '
				<tr class="' . $row_class . '">
					<td>' . $clip['audio_file'] . '</td>
					<td>' . $clip['label_company'] . '</td>
					<td>' . $clip['artist_name'] . '</td>
					<td>' . $clip['release_name'] . '</td>
					<td>' . $clip['label'] . '</td>
					<td>' . $clip['unique_track_id'] . '</td>
					<td>' . $clip['track_name'] . '</td>
					<td>' . $clip['track_code'] . '</td>
					<td>' . ($clip['audio_file'] ? $clip['clip_code'] : '') . '</td>
					<td>' . $clip['isrc'] . '</td>
					<td>' . $clip['genre'] . '</td>
					<td>' . $clip['version'] . '</td>
					<td>' . $clip['clip_title'] . '</td>
					<td>' . $clip['explicit_lyrics'] . '</td>
					<td>' . conv_msec_to_time($clip['start_time']) . '</td>
					<td>' . conv_msec_to_time($clip['end_time']) . '</td>
					<td>' . ($status=="cut" ? ((is_array($clip['dms_name'])) ? implode(', ',$clip['dms_name']) : '') : $clip['dms_name']) . '</td>
					<td>' . implode(',',$clip['restriction']) . '</td>
					<td>' . implode(',',$clip['recording_artist']) . '</td>
					<td>'.format_date($clip['date_cut']).'</td>
					<td>'.format_date($clip['date_encoded']).'</td>
					<td>'.format_date($clip['date_delivered']).'</td>
				</tr>';
		}//end foreach
	}else{
		$report_data .= '
				<tr><td colspan="19">No clips found.</td>
				</tr>';
	}
	$report_data .= '
				</tbody>
				</table>
			</div>';
?>