<?php	
require_once(__DIR__ . '/../../../vendor/autoload.php');
use Orchard\Mysqli\QueryData;
use Orchard\Mysqli\Wrapper as MysqliWrapper;

$wrapperObj = new MysqliWrapper($mysqli);
$varTotalLabels = 0;
$pendingArr = array();
$varReleaseReceived = 0;
$varTotalESTReleases = 0;

$today = date("Y-m-d");

$sql = "SELECT v.vendor_id, vcc.id, vcc.cont_start
			FROM vendor v
			INNER JOIN vendor_contract vcc ON v.vendor_id=vcc.vendor_id
			LEFT JOIN vendor_contract vcc2 ON vcc.vendor_id = vcc2.vendor_id AND vcc2.cont_start <= ?
							AND ( vcc2.contract_type = 'per_release_term' AND
								( vcc2.cont_start = '0000-00-00' OR vcc2.release_term <=0 OR ( vcc2.cont_start + interval vcc2.release_term month) > ? )
							OR ( (vcc2.contract_type <> 'per_release_term' OR vcc2.contract_type IS NULL) AND (vcc2.cont_end >= ? OR vcc2.cont_end IS NULL OR vcc2.cont_end = '0000-00-00') )
							)
						 AND vcc.cont_start < vcc2.cont_start
			where vcc2.id IS NULL  AND vcc.cont_start <= ?
			AND ( vcc.contract_type = 'per_release_term' AND
				( vcc.cont_start = '0000-00-00' OR vcc.release_term <=0 OR ( vcc.cont_start + interval vcc.release_term month) > ? )
			OR ( (vcc.contract_type <> 'per_release_term' OR vcc.contract_type IS NULL) AND (vcc.cont_end >= ? OR vcc.cont_end IS NULL OR vcc.cont_end = '0000-00-00') )
			)
			and vcc.orchrep_name = ?
			ORDER BY v.vendor_id
			";
$data_params = new QueryData();
$data_params->addDataParam('s', $today);
$data_params->addDataParam('s', $today);
$data_params->addDataParam('s', $today);
$data_params->addDataParam('s', $today);
$data_params->addDataParam('s', $today);
$data_params->addDataParam('s', $today);
$data_params->addDataParam('s', $_SESSION['S_USERID']);
$rs = $wrapperObj->executeQuery($sql, $data_params);
if ($rs === false) {
    showierror($sql);
}
$vendors_belong_to_orchrep_name = array();
foreach ($rs as $row) {
	$vendors_belong_to_orchrep_name[] = $row['vendor_id'];
}

if(count($vendors_belong_to_orchrep_name)){
	$str_q = implode(',', array_fill(0, count($vendors_belong_to_orchrep_name), '?'));

	$sql = 'SELECT (convert(v.est_total_releases, SIGNED INT) - COUNT(DISTINCT y.upc)) AS releasesPending, v.vendor_id,
					(v.est_total_tracks - SUM(y.total_tracks)) AS tracksPending,
					COUNT(DISTINCT y.upc) AS releaseReceived, v.est_total_releases AS releaseEstimated
				FROM vendor v LEFT JOIN
					(
						SELECT x.upc, x.vendor_id, MAX(x.total_tracks) AS total_tracks
						FROM (
							SELECT r.upc, a.vendor_id, COUNT(t.id) AS total_tracks
							FROM releases r
								INNER JOIN artist_info a ON   r.artist_id = a.artist_id
								INNER JOIN vendor v ON v.vendor_id = a.vendor_id
								LEFT JOIN track t ON t.upc = r.upc
							WHERE v.assigned_to != 0
								AND r.release_status = "in_content"
								AND v.status = "signed"
								AND v.vendor_id in (' . $str_q . ')
							GROUP BY r.upc

							UNION

							SELECT cd.upc, cd.vendor_id, cd.total_tracks
							FROM cd_receive cd
								INNER JOIN vendor v ON v.vendor_id = cd.vendor_id
							WHERE  v.status = "signed" AND v.assigned_to != 0
								AND v.vendor_id in (' . $str_q . ')
							GROUP BY cd.upc

							UNION

							SELECT r.upc, a.vendor_id, COUNT(eat.id) AS total_tracks
							FROM releases r
								INNER JOIN artist_info a ON a.artist_id = r.artist_id
								INNER JOIN vendor v ON a.vendor_id = v.vendor_id
								LEFT JOIN track eat ON eat.release_id = r.release_id
							WHERE r.release_status != "in_content"
								AND v.status = "signed" AND v.assigned_to != 0
								AND v.vendor_id in ('. $str_q . ')
							GROUP BY r.upc
						) x
						GROUP BY x.upc
					) y ON v.vendor_id = y.vendor_id
				WHERE v.status = "signed"
					AND v.vendor_id > 0
					AND v.vendor_id in ('. $str_q . ')
				GROUP BY v.vendor_id';
	$data_params = new QueryData();

	foreach ($vendors_belong_to_orchrep_name as $val) {
	    $data_params->addDataParam('i', $val);
	}
	foreach ($vendors_belong_to_orchrep_name as $val) {
	    $data_params->addDataParam('i', $val);
	}
	foreach ($vendors_belong_to_orchrep_name as $val) {
	    $data_params->addDataParam('i', $val);
	}
	foreach ($vendors_belong_to_orchrep_name as $val) {
	    $data_params->addDataParam('i', $val);
	}
	$pend_data = $wrapperObj->executeQuery($sql, $data_params);
	if ($pend_data === false) {
	    showierror($sql);
	}
	foreach ($pend_data as $pend_row) {
		$varElement = array('releases_pending' => ($pend_row['releasesPending'] > 0 ? $pend_row['releasesPending'] : 0), 'tracks_pending' => ($pend_row['tracksPending'] > 0 ? $pend_row['tracksPending'] : 0));
		$pendingArr[$pend_row['vendor_id']] = $varElement;
		$varReleaseReceived += $pend_row['releaseReceived'];
		$varTotalESTReleases += $pend_row['releaseEstimated'];
		$varTotalLabels++;
	}
}
?>