<?
/************************************************************
 08/08/2006 - Paulo Kuong
 -	File Created

 08/15/2006 - Paulo Kuong
 -	Changed CI registered column display to display problems

 08/09/2006 - Jian Wu
 -	Took out FORMAT_DATE function from query and used format_date PHP
 function.

 12/11/2006 - Paulo Kuong
 -	Added customize-dms-display panel
 ************************************************************************/
?>
<?
require_once(__DIR__ . '/../../../../vendor/autoload.php');

$sort_by		= isset($sort_by)			? stripslashes($sort_by)		: '';
$sort_desc		= $sort_desc				? $sort_desc					: 0;
$dms_masters	= isset($dms_masters)       ? $dms_masters					: array(1,2,3,4,6,7);

$UPCs = '';
$UPC_array = array();
$dms_names = array();
if(count($dms_masters)){
	$dataParams = [];
	$get_dms_names = 'select customer_master_master_id, customer_name 
	    from customer_master_master 
	    where customer_master_master_id in(' . implode(',', array_fill(0, count($dms_masters), '?')) . ')';
	foreach ($dms_masters as $val) { 
	    $dataParams[] = $val;
	}
	$get_dms_names_rs = $wrapperObj->executeQuery($get_dms_names, $dataParams);
	if ($get_dms_names_rs === false) {
        show_pdo_error($get_dms_names, $wrapperObj->getLastStatement());
	}
	foreach ($get_dms_names_rs as $get_dms_names_row) { 
		$dms_names[$get_dms_names_row['customer_master_master_id']] = $get_dms_names_row['customer_name'];
	}
}
$dataParams = [];
$sql = 'SELECT x.upc, x.release_name, x.artist_name, x.viewLink, x.artist_id, cd.date_received AS cd_received
		FROM (
				SELECT r.release_name, a.name AS artist_name, r.upc, 1 AS viewLink, a.artist_id
				FROM releases r, artist_info a
				WHERE r.artist_id = a.artist_id
					AND a.vendor_id = ?
					AND r.not_for_distribution != "SMEAnalyticsDummy"
					 AND r.release_status = "in_content"

				UNION

				SELECT cd.release_name, cd.artist_name, cd.upc, 0 AS viewLink, 0 AS artist_id
				FROM cd_receive cd
				WHERE cd.vendor_id = ?
					AND cd.upc > 0


			) x LEFT OUTER JOIN cd_receive cd ON x.upc = cd.upc
			GROUP BY x.upc';

switch ($sort_by) {
	case 'upc':
		$orderBy = 'x.upc';
		break;
	case 'rn':
		$orderBy = 'x.release_name';
		break;
	case 'an':
		$orderBy = 'x.artist_name';
		break;
	case 'cdrdate':
		$orderBy = 'cd_received';
		break;
	default:
		$sort_by = 'rn';
		$orderBy = 'x.release_name';
}
$sql .= ' ORDER BY ' . $orderBy . ($sort_desc ? ' DESC' : '');
$dataParams[] = $vendor_id;
$dataParams[] = $vendor_id;
$upc_data = $wrapperObj->executeQuery($sql, $dataParams);
if ($upc_data === false) {
    show_pdo_error($sql, $wrapperObj->getLastStatement());
}
foreach ($upc_data as $upc_row) {
	$UPCs .= ',' . $upc_row['upc'];
	$UPC_array[$upc_row['upc']] = array('cd_received'		=> format_date($upc_row['cd_received']),
											'OP_date'			=> '',
											'lbl_conf_date'		=> '',
											'xfer_content_date'	=> '',
											'sent_to_encoder'	=> '',
											'ci_registered'	    => '',
											'result'		    => $upc_row['result'],
											'viewLink'			=> $upc_row['viewLink'],
											'release_name'		=> $upc_row['release_name'],
											'artist_name'		=> $upc_row['artist_name'],
											'artist_id'			=> $upc_row['artist_id']
	);
	foreach($dms_masters as $v){
		$UPC_array[$upc_row['upc']]['delivered_dms_master_'.$v] = '';
	}
}

// get label content status dates
$dataParams = [];
$Upcs_array = explode(',', $UPCs);
if ($UPCs) {
    $upcs_val = implode(',', array_fill(0, count($Upcs_array), '?'));
    foreach ($Upcs_array as $val) {
        $dataParams[] = $val;
    }
} else {
    $upcs_val = '0';
}
$sql = 'SELECT a.upc, b.date AS status_date, b.status 
    FROM releases a, release_status b 
    WHERE a.release_id = b.release_id AND a.upc IN (' . $upcs_val . ') AND b.status <> "" AND a.upc > 0 
    ORDER BY b.date';
$data = $wrapperObj->executeQuery($sql, $dataParams);
if ($data === false) {
    show_pdo_error($sql, $wrapperObj->getLastStatement());
}
foreach ($data as $rows) {
	if ($rows['status'] == 'orchard_processing')
	$UPC_array[$rows['upc']]['OP_date'] = format_date($rows['status_date']);
	/*elseif ($rows['status'] == 'label_confirmation')
	 $UPC_array[$rows['upc']]['lbl_conf_date'] = format_date($rows['status_date']);*/
	elseif ($rows['status'] == 'transfer_to_content')
	$UPC_array[$rows['upc']]['xfer_content_date'] = format_date($rows['status_date']);
}

// get send to encoder date
// 01/17/2006 - Jian: changed query to use encoding order date, not date from sony_status
$sql = 'SELECT eod.upc, eo.encoder_delivery_date AS date 
    FROM encoding_order_detail eod, encoding_order eo 
    WHERE eod.upc IN (' . $upcs_val . ') AND eod.upc > 0 AND eod.encoding_order_id = eo.encoding_order_id 
    GROUP BY eod.upc';
$data = $wrapperObj->executeQuery($sql, $dataParams);
if ($data === false) {
    show_pdo_error($sql, $wrapperObj->getLastStatement());
}
foreach ($data as $rows) {
	$UPC_array[$rows['upc']]['sent_to_encoder'] = format_date($rows['date']);
}

//get CI registered
$sql = 'select upc, date, result
    from sony_status 
    where provider = "ci" and upc in (' . $upcs_val . ')
    order by date desc';
$data = $wrapperObj->executeQuery($sql, $dataParams);
if ($data === false) {
    show_pdo_error($sql, $wrapperObj->getLastStatement());
}

foreach ($data as $rows) {
	$UPC_array[$rows['upc']]['ci_registered'] = format_date($rows['date']);
	$UPC_array[$rows['upc']]['result'] = ($rows['result'] <> "complete") ? $rows['result'] : "";
}
//Stores
foreach ($dms_masters as $values) {
    $dataParams = [];
	$sql = 'SELECT dd.upc, MIN(dd.date_delivered) AS date_delivered
	    FROM delivery_history dd
	    WHERE dd.upc IN';
	if ($UPCs) {
	    $sql .= ' (' . implode(',', array_fill(0, count($Upcs_array), '?')) . ')';
	    foreach ($Upcs_array as $val) {
	        $dataParams[] = $val;
	    }
	} else {
	    $sql .= ' (0)';
	}
	$sql .= ' AND dd.upc > 0
	        AND dd.customer_master_master_id = ?
	    GROUP BY dd.upc';
	    $dataParams[] = $values;
	    $data = $wrapperObj->executeQuery($sql, $dataParams);
	if ($data === false) {
	    show_pdo_error($sql, $wrapperObj->getLastStatement());
	}
	foreach ($data as $rows) {
		$UPC_array[$rows['upc']]['delivered_dms_master_'.$values] = format_date($rows['date_delivered']);
	}
}

?>
<?
$varTable ='
	<table'. ($no_url ? ' border=1' : ' class="data full"') .'>
	<tr>
		<th>'.($no_url ? "UPC" : sortColumnHeader('UPC', 'upc', 'vendor_id=' . $vendor_id)).'</th>
		<th>'.($no_url ? "Release" : sortColumnHeader('Release', 'rn', 'vendor_id=' . $vendor_id)).'</th>
		<th>'.($no_url ? "Artist" : sortColumnHeader('Artist', 'an', 'vendor_id=' . $vendor_id)).'</th>
		<th>'.($no_url ? "CD Received" : sortColumnHeader('CD Received', 'cdrdate', 'vendor_id=' . $vendor_id)).'</th>
		<th bgcolor="#FFFFEB">Orchard Processed</th>
		<th bgcolor="#FFFFEB">Label Confirmed</th>
		<th bgcolor="#FFFFEB">Transferred to Content</th>
		<th>Sent to Encoder</th>
		<th bgcolor="#FFF3FE">CI Registered</th>';
foreach ($dms_masters as $v) {
	$varTable .= '<th bgcolor="#EDFFFF">Delivered to '.$dms_names[$v].'</th>';
}
$varTable .='</tr>';
$rci = 0;
foreach ($UPC_array as $key => $value) {
	$row_class = (++$rci & 1) ? '' : 'alt';
	$varTable .='
		<tr class="'.$row_class.'">
			<td nowrap="nowrap">';
	if ($value['viewLink']) {
		$varTable .='
			'.($no_url ? $key : '<a href="/warehouse/individual_upc_overall_andy.php?upc='.$key.'" title="Release Status">'.$key.'</a>');
	}
	$varTable .='
				</td>
				<td>';
	if ($value['viewLink']) {
		$varTable .='
					'.($no_url ? $value['release_name'] : '<a href="/cont_mgmt/view_release.php?upc='.$key.'" title="View Artist">'.$value['release_name'].'</a>');
	} else {
		$varTable .= $value['release_name'];
	}
	$varTable .='
				</td>
				<td>';
	if ($value['artist_id']) {
		$varTable .='
					'.($no_url ? $value['artist_name'] : '<a href="/cont_mgmt/view_artist.php?artist_id='.$value['artist_id'].'" title="View Release">'.$value['artist_name'].'</a>');
	} else {
		$varTable .= $value['artist_name'];
	}
	$varTable .='
				</td>
				<td>'.$value['cd_received'].'</td>
				<td bgcolor="#FFFFEB">'.$value['OP_date'].'</td>
				<td bgcolor="#FFFFEB">'.$value['lbl_conf_date'].'</td>
				<td bgcolor="#FFFFEB">'.$value['xfer_content_date'].'</td>
				<td>'.$value['sent_to_encoder'].'</td>
				<td bgcolor="#FFF3FE">'.$value['ci_registered'].'<br><a href="/warehouse/individual_upc_overall_andy.php?upc='.$key.'">'.ucfirst($value['result']).'</a></td>
				';
	$dms_masters = array_unique($dms_masters);
	foreach ($dms_masters as $v) {
		$varTable .= '<td bgcolor="#EDFFFF">'.$value['delivered_dms_master_'.$v].' </td>';
	}
	$varTable .='</tr>';
}
$varTable .='
			</table>';