<?
require_once(__DIR__ . '/../../../vendor/autoload.php');
use Orchard\Mysqli\QueryData;
use Orchard\Mysqli\Wrapper as MysqliWrapper;

ini_set('memory_limit', '2048M');

$wrapperObj = new MysqliWrapper($mysqli);

$page = ($page && is_numeric($page)) ? $page : 1;
$sort_by = isset($sort_by) ? stripslashes($sort_by) : '';
$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,err_msg,confirm_msg,fatal_error', array($_REQUEST));
$pageNum = 0;
$total = 0;

$dataParams = new QueryData();
if($type == 'search' || $saved_search_id){
	if(!empty($saved_search_id) && is_numeric($saved_search_id)){
		$search_type = "meta_update_queue";
		include ("../includes/retrieveSavedSearch.inc");
	}

	$criteria_counter = 0;

	unset($_SESSION['S_SEARCH_CRITERIA']);
	$search_criteria = array();
	$_SESSION["S_SEARCH_TYPE"] = "meta_update_queue";

	$havingClause = "";
	$select = 'SELECT SQL_CALC_FOUND_ROWS mq.meta_update_queue_id, mq.upc, r.display_upc, mq.update_type, mq.description, mq.date_added, mq.status
			, r.release_name,
			(SELECT vendor_id FROM artist_info a WHERE a.artist_id = r.artist_id) AS vendor_id,
			(SELECT IF(v.company IS NULL OR v.company = "",v.name,v.company) FROM vendor v INNER JOIN artist_info a ON v.vendor_id = a.vendor_id WHERE a.artist_id = r.artist_id) AS label_name,
			(SELECT CONCAT(f_name,"-",l_name) FROM orchadmin_users ou INNER JOIN vendor v ON ou.id = v.assigned_to INNER JOIN artist_info a ON v.vendor_id = a.vendor_id WHERE a.artist_id = r.artist_id) AS assigned_to_name,
			(SELECT NAME FROM artist_info WHERE artist_id = r.artist_id) AS artist_name, r.artist_id ';
	$select .= ' FROM releases r INNER JOIN meta_update_queue mq ON r.upc = mq.upc ';

	if($dms){
	    $select .= ' INNER JOIN (SELECT DISTINCT meta_update_queue_id FROM meta_update_dms_master WHERE customer_master_master_id IN';
	    if (count($dms) > 0) {
	        $select .= '(' . implode(', ', array_fill(0, count($dms), '?')) . '))';
	        foreach ($dms as $val) {
	            $dataParams->addDataParam('i', $val);
	        }
	    } else {
	        $select .= '(0))';
	    }
	    $select .= ' mm ON mm.meta_update_queue_id = mq.meta_update_queue_id';
        
	    $search_criteria[$criteria_counter]['name'] = 'dms[]';
	    $search_criteria[$criteria_counter]['value'] = $dms;
	    $criteria_counter++;
	}

	$whereClause = ' WHERE 1 ';

    if(empty($status)){
        $whereClause .= " AND mq.status <> 'completed' ";
    } else {
        $whereClause .= ' AND mq.status IN (' . implode(', ', array_fill(0, count($status), '?')) . ')';
        foreach ($status as $val) {
            $dataParams->addDataParam('s', $val);
        }

        $search_criteria[$criteria_counter]["name"] = "status[]";
        $search_criteria[$criteria_counter]["value"] = implode(",", $status);
        $criteria_counter++;
    }

	if($meta_update_queue_id){
		$whereClause .= ' AND mq.meta_update_queue_id = ?';
		$dataParams->addDataParam('i', $meta_update_queue_id);

		$search_criteria[$criteria_counter]["name"] = "meta_update_queue_id";
		$search_criteria[$criteria_counter]["value"] = $meta_update_queue_id;
		$criteria_counter++;
	}
	if($upd_type){
		$whereClause .= ' AND mq.update_type = ?';
		$dataParams->addDataParam('s', $upd_type);

		$search_criteria[$criteria_counter]["name"] = "update_type";
		$search_criteria[$criteria_counter]["value"] = $upd_type;
		$criteria_counter++;
	}
	if($upcs = html_entity_decode($upcs)){
		$upcs = explode("\n", $upcs);
		foreach($upcs as $upc){
			$upc = trim($upc, "\t");
			$upc = trim($upc, "\r");
			$upc = str_pad($upc, 12, 0, STR_PAD_LEFT);
			if(checkUPC($upc)){
				$upc_list[] = $upc;
			}
		}

        if ($upc_list) {
	        $whereClause .= ' AND mq.upc IN (' . implode(', ', array_fill(0, count($upc_list), '?')) . ')';
	        foreach ($upc_list as $upc) {
	            $dataParams->addDataParam('i', $upc);
	        }
            $search_criteria[$criteria_counter]["name"] = "upcs[]";
            $search_criteria[$criteria_counter]["value"] = $upc_list;
            $criteria_counter++;
        }
	}
	if($from_date_added){
		$whereClause .= ' AND mq.date_added >= ?';
		$dataParams->addDataParam('s', $from_date_added);

		$search_criteria[$criteria_counter]["name"] = "from_date_added";
		$search_criteria[$criteria_counter]["value"] = $from_date_added;
		$criteria_counter++;
    } else {
        $whereClause .= " AND mq.date_added >= DATE_SUB(NOW(), INTERVAL 6 MONTH)";
    }
	if($to_date_added){
		$whereClause .= ' AND mq.date_added <= ?';
		$dataParams->addDataParam('s', $to_date_added);

		$search_criteria[$criteria_counter]["name"] = "to_date_added";
		$search_criteria[$criteria_counter]["value"] = $to_date_added;
		$criteria_counter++;
	}

	$_SESSION["S_SEARCH_CRITERIA"] = serialize($search_criteria);

	$sql = $select . $whereClause;
}else{
	$sql = 'SELECT SQL_CALC_FOUND_ROWS mq.meta_update_queue_id, mq.upc, r.display_upc, mq.update_type, mq.description, mq.date_added, mq.status, r.release_name,
			(SELECT vendor_id FROM artist_info a WHERE a.artist_id = r.artist_id) AS vendor_id,
			(SELECT IF(v.company IS NULL OR v.company = "",v.name,v.company) FROM vendor v INNER JOIN artist_info a ON v.vendor_id = a.vendor_id WHERE a.artist_id = r.artist_id) AS label_name,
			(SELECT CONCAT(f_name,"-",l_name) FROM orchadmin_users ou INNER JOIN vendor v ON ou.id = v.assigned_to INNER JOIN artist_info a ON v.vendor_id = a.vendor_id WHERE a.artist_id = r.artist_id) AS assigned_to_name,
			(SELECT NAME FROM artist_info WHERE artist_id = r.artist_id) AS artist_name, r.artist_id
			FROM releases r
			INNER JOIN meta_update_queue mq ON r.upc = mq.upc
			WHERE mq.status <> "completed" 
			  AND mq.date_added >= DATE_SUB(NOW(), INTERVAL 6 MONTH)';
}
$sql .= ' AND r.not_for_distribution != "SMEAnalyticsDummy" ';
$main_arr = array();

switch($sort_by){
	case 'upc':
		$sql .= " ORDER BY upc ";
		$sql .= $sort_desc ? "DESC" : "ASC";
		break;
	case 'release_name':
		$sql .= " ORDER BY release_name ";
		$sql .= $sort_desc ? "DESC" : "ASC";
		break;
	case 'label_name':
		$sql .= " ORDER BY label_name ";
		$sql .= $sort_desc ? "DESC" : "ASC";
		break;
	case 'artist_name':
		$sql .= " ORDER BY artist_name ";
		$sql .= $sort_desc ? "DESC" : "ASC";
		break;
	case 'assigned_to_name':
		$sql .= " ORDER BY assigned_to_name ";
		$sql .= $sort_desc ? "DESC" : "ASC";
		break;
	case 'update_type':
		$sql .= " ORDER BY update_type ";
		$sql .= $sort_desc ? "DESC" : "ASC";
		break;
	case 'status':
		$sql .= " ORDER BY status ";
		$sql .= $sort_desc ? "DESC" : "ASC";
		break;
	case 'date_added':
		$sql .= " ORDER BY date_added ";
		$sql .= $sort_desc ? "DESC" : "ASC";
		break;
	case 'meta_update_queue_id':
		$sql .= " ORDER BY meta_update_queue_id ";
		$sql .= $sort_desc ? "DESC" : "ASC";
		break;
	default:
		$sort_by = 'date_added';
		$sort_desc = 1;
		$sql .= " ORDER BY date_added DESC";
}
if (!$viewall) {
    $sql .= ' LIMIT ?, ?';
    $dataParams->addDataParam('i', (($page - 1) * $pageRecord));
    $dataParams->addDataParam('i', $pageRecord);
}

$rs = $wrapperObj->executeQuery($sql, $dataParams);
if ($rs === false) {
    showierror($sql);
}

$dataParams = new QueryData();
$sql = 'SELECT FOUND_ROWS() AS total';
$total_count = $wrapperObj->executeQuery($sql, $dataParams);
if ($total_count === false) {
    showierror($sql);
}
$total = $total_count[0]['total'];
$pageNum = ceil($total / $pageRecord);

$report_data = '
		<input type="hidden" name="a" value="delete" />
				<table' . ($no_url ? ' border=1' : ' class="data"') .'>
				<tr>' .
	 (($no_url or $viewall) ? '' : '<th><input type="checkbox" name="check_all" onclick="checkAll(\'meta_update_queue_ids[]\', this.checked);" title="Check/Uncheck All" /></th>') . '
					<th>' .
	 (($no_url or $viewall) ? 'Queue ID' : sortColumnHeader('Queue ID', 'meta_update_queue_id', $url)) . '</th>
					<th class="center">' .
	 (($no_url or $viewall) ? 'UPC' : sortColumnHeader('UPC', 'upc', $url)) . '</th>
					<th class="center">' .
	 (($no_url or $viewall) ? 'Release' : sortColumnHeader('Release', 'release_name', $url)) . '</th>
					<th class="center">' .
	 (($no_url or $viewall) ? 'Artist' : sortColumnHeader('Artist', 'artist_name', $url)) . '</th>
					<th class="center">' .
	 (($no_url or $viewall) ? 'Label' : sortColumnHeader('Label', 'label_name', $url)) . '</th>
					<th class="center">' .
	 (($no_url or $viewall) ? 'Assigned To' : sortColumnHeader('Assigned To', 'assigned_to_name', $url)) . '</th>
					<th class="center">' .
	 (($no_url or $viewall) ? 'Date Added' : sortColumnHeader('Date Added', 'date_added', $url)) . '</th>
					<th class="center">' .
	 (($no_url or $viewall) ? 'Update Type' : sortColumnHeader('Update Type', 'update_type', $url)) . '</th>
					<th class="center">Description</th>
					<th class="center">DMS</th>
					<th class="center">' .
	 (($no_url or $viewall) ? 'Status' : sortColumnHeader('Status', 'status', $url)) . '</th>
				</tr>';

foreach ($rs as $row) {

	$row_class = (++$rci & 1) ? '' : 'alt';
	$dataParams = new QueryData();
	$sql = 'select * from meta_update_dms_master md
	    inner join customer_master_master cmm on cmm.customer_master_master_id = md.customer_master_master_id
	    where md.meta_update_queue_id = ?';
	$dataParams->addDataParam('i', $row['meta_update_queue_id']);
	$dms_queue_result = $wrapperObj->executeQuery($sql, $dataParams);
	if ($dms_queue_result === false) {
	    showierror($sql);
	}
	$dms_arr = array();
    $dms_list = '';
	foreach ($dms_queue_result as $dms_queue_row) {
		$dms_queue_arr[] = $dms_queue_row['customer_master_master_id'];
		$dms_date_processed_arr[] = $dms_queue_row['date_processed'];
		if(!in_array($dms_queue_row['customer_name'], $dms_arr))
			array_push($dms_arr, $dms_queue_row['customer_name']);
	}

	if($dms_date_processed_arr){
		$no_delete = false;
		foreach($dms_date_processed_arr as $val){
			if($val){
				$no_delete = true;
			}
		}
	}
	if($dms_arr) {
        $dms_list = implode('; ', $dms_arr);
    }

	$report_data .= '
			<tr class="' . $row_class . '">';

	if((!$no_url or !$viewall)){
		$report_data .= '<td>';

		if( $row['status'] == "new" ){
			$report_data .= '<input type="checkbox" name="meta_update_queue_ids[]" value="' . $row['meta_update_queue_id'] .
				 '" onclick="if (!this.checked) document.arc_meta.check_all.checked=false;" />';
		}
		$report_data .= '</td>';
	}
	$report_data .= '<td>' .
		 (($no_url) ? $row['meta_update_queue_id'] : '<a href="/cont_mgmt/create_meta_update.php?meta_update_queue_id=' .
		 $row['meta_update_queue_id'] . '&upc=' . $row['upc'] . '&status=' . $row['status'] . '" title="Edit Metadata Update Queue">' .
		 $row['meta_update_queue_id'] . '</a>') .
		 '</td>
			<td>' .
		 (($no_url) ? '"'.$row['display_upc'].'"' : '<a href="/warehouse/individual_upc_overall_andy.php?upc=' . $row['upc'] . '" title="View Release Info">' .
		 $row['display_upc'] . '</a>') .
		 '</td>
			<td>' .
		 (($no_url) ? $row['release_name'] : '<a href="/cont_mgmt/view_release.php?upc=' . $row['upc'] . '" title="View Release Info">' .
		 $row['release_name'] . '</a>') .
		 '</td>
			<td>' .
		 (($no_url) ? $row['artist_name'] : '<a href="/cont_mgmt/view_artist.php?artist_id=' . $row['artist_id'] . '" title="View Artist Info">' .
		 $row['artist_name'] . '</a>') .
		 '</td>
			<td>' .
		 (($no_url) ? $row['label_name'] : '<a href="/cont_mgmt/view_vendor.php?vendor_id=' . $row['vendor_id'] . '" title="View Label Info">' .
		 $row['label_name'] . '</a>') . '</td>
			<td>' . $row['assigned_to_name'] . '</td>
			<td>' . format_date($row['date_added'], true) . '</td>
			<td>' . ucwords(str_replace('_', ' ', $row['update_type'])) . '</td>
			<td>' . $row['description'] . '</td>
			<td>' . $dms_list . '</td>
			<td>' . $row['status'] . '</td>
		</tr>';
}
$report_data .= '
		</table>';