
<?php
require_once(__DIR__ . '/../../../vendor/autoload.php');
use Orchard\Mysqli\QueryData;
use Orchard\Mysqli\Wrapper as MysqliWrapper;
$wrapperObj = new MysqliWrapper($mysqli_r);

$summary_user_id		= ($summary_user_id)			? $summary_user_id		:$_SESSION['S_USERID'];
$super_pm				= $super_pm						? $super_pm				: 0;

$vendors				= '';

$varCDReceived			= 0;
$varTotalCDReceived		= 0;

$varLabelConf			= 0;
$varTotalLabelConf		= 0;

$varLabelNotApproved	= 0;
$varTotalNotApproved	= 0;

$varLabelNotXferred		= 0;
$varTotalNotXferred		= 0;

$varNotDelivered		= 0;
$varNotDeliveredTotal	= 0;

$varWaitingDelivery		= 0;
$varWaitingDeliveryTotal	= 0;

$varNewLblNoRelTotal	= 0;
$varTotalNewLblNoRel	= 0;

$varOldLblNoRelTotal	= 0;
$varFailedTotal			= 0;

// count new labels that haven't received any releases for more than $appNewLabelDays days
$total_new_labels = array();
$no_cd_receive = array();
$no_content = array();
$no_label_copy1 = array();
$no_label_copy2 = array();
$union_labels = array();

$dataParams = new QueryData();

if ($aggregator_login) {
    $dataParams = addSessionOwnerDataParam($dataParams);
} else {
    if ($summary_user_id) {
        $dataParams->addDataParam('i', $summary_user_id);
    }
}
$non_orch_owner = ownerFilterSubQuery(' AND v.owner');
$sql = 'SELECT v.vendor_id, v.date_created
						FROM vendor v
							LEFT JOIN cd_receive cdr ON v.vendor_id = cdr.vendor_id
						WHERE v.status = "signed"
							' . ($aggregator_login ? $non_orch_owner : ($summary_user_id ? 'AND v.assigned_to = ?' : 'AND v.assigned_to != 0 AND v.assigned_to IS NOT NULL')) . '
							AND cdr.vendor_id IS NULL
						GROUP BY v.vendor_id';
$total_new_label = $wrapperObj->executeQuery($sql, $dataParams);
if ($total_new_label === false) {
    showierror($sql); 
}
foreach ($total_new_label as $RowData) {
	$no_cd_receive[] = $RowData['vendor_id'];
	$union_labels[$RowData['vendor_id']] = $RowData['date_created'];
}

/*$sql = 'SELECT v.vendor_id, v.date_created
						FROM vendor v
							LEFT JOIN artist_info a ON v.vendor_id = a.vendor_id
							LEFT JOIN releases r ON a.artist_id = r.artist_id
						WHERE v.status = "signed"
							' . ($aggregator_login ? 'AND v.owner = ' . QueryFormatString($_SESSION['S_OA_OWNER']) : ($summary_user_id ? 'AND v.assigned_to = ' . $summary_user_id : 'AND v.assigned_to != 0 AND v.assigned_to IS NOT NULL')) . '
							AND r.upc IS NULL
						GROUP BY v.vendor_id';*/
$sql = 'SELECT v.vendor_id, v.date_created
						FROM vendor v
							LEFT JOIN artist_info a ON v.vendor_id = a.vendor_id
							LEFT JOIN releases r ON a.artist_id = r.artist_id
						WHERE v.status = "signed"
							' . ($aggregator_login ? $non_orch_owner : ($summary_user_id ? 'AND v.assigned_to = ?' : 'AND v.assigned_to != 0 AND v.assigned_to IS NOT NULL')) . '
							AND r.release_status NOT IN ("in_content","transfer_to_content")
						GROUP BY v.vendor_id';
$total_new_label = $wrapperObj->executeQuery($sql, $dataParams);
if ($total_new_label === false) {
    showierror($sql);
}
foreach ($total_new_label as $RowData) {
	$no_content[] = $RowData['vendor_id'];
	$union_labels[$RowData['vendor_id']] = $RowData['date_created'];
}

$sql = 'SELECT v.vendor_id, v.date_created
						FROM vendor v
							LEFT JOIN artist_info a ON a.vendor_id = v.vendor_id
							LEFT JOIN releases r ON r.artist_id = a.artist_id
						WHERE v.status = "signed"
							' . ($aggregator_login ? $non_orch_owner : ($summary_user_id ? 'AND v.assigned_to = ?': 'AND v.assigned_to != 0 AND v.assigned_to IS NOT NULL')) . '
							AND r.release_id IS NULL
						GROUP BY v.vendor_id';
$total_new_label = $wrapperObj->executeQuery($sql, $dataParams);
if ($total_new_label === false) {
    showierror($sql);
}
foreach ($total_new_label as $RowData) {
	$no_label_copy2[] = $RowData['vendor_id'];
	$union_labels[$RowData['vendor_id']] = $RowData['date_created'];
}
$total_new_labels = array_intersect($no_cd_receive, $no_content, $no_label_copy1, $no_label_copy2);

if (is_array($total_new_labels) and count($total_new_labels)) {
	$varLastDate = date('Y-m-d', strtotime("-$appNewLabelDays day"));
	foreach ($total_new_labels as $vendor_id) {
		$date_created = $union_labels[$vendor_id];
		if ($date_created < $varLastDate)
		$varNewLblNoRelTotal++;
		$varTotalNewLblNoRel++;
	}
}

// count total label copy releases by status
$sql = 'SELECT a.release_id, c.vendor_id, a.ingestion_completed, MAX(d.date) AS status_date, a.release_name, c.name,
							(SELECT GROUP_CONCAT(status ORDER BY release_status_id DESC) FROM release_status WHERE release_id = a.release_id AND status <> "") AS status
						FROM releases a
						INNER JOIN artist_info c ON a.artist_id = c.artist_id
						INNER JOIN vendor v ON c.vendor_id = v.vendor_id' . ($aggregator_login ? $non_orch_owner : ($summary_user_id ? ' AND v.assigned_to = ?' : ' AND v.assigned_to != 0 AND v.assigned_to IS NOT NULL')) . '
						LEFT OUTER JOIN release_status d ON a.release_id = d.release_id
						WHERE a.release_status != "in_content" AND a.release_name <> ""
						GROUP BY a.release_id
						ORDER BY a.release_id';

$wc_data = $wrapperObj->executeQuery($sql, $dataParams);
if ($wc_data === false) {
    showierror($sql);
}
$varLastOPDate = date('Y-m-d', strtotime("-$appLabelOPDays day"));
$varLastConfDate = date('Y-m-d', strtotime("-$appLabelConfDays day"));
$varLastXferDate = date('Y-m-d', strtotime("-$appNotTransferredDays day"));

foreach ($wc_data as $RowData) {
	if (substr($RowData['status'], 0, 18) == 'orchard_processing') {
		if ($RowData['status_date'] < $varLastOPDate)
		$varOPReleases++;
		$varTotalOPReleases++;
	}
	/*if (substr($RowData['status'], 0, 18) == 'label_confirmation') {
	 if ($RowData['status_date'] < $varLastConfDate)
	 $varLabelNotApproved++;
	 $varTotalNotApproved++;
	 }*/
	if (substr($RowData['status'], 0, 19) == 'transfer_to_content') {
		if ($RowData['status_date'] < $varLastXferDate)
		$varLabelNotXferred++;
		$varTotalNotXferred++;
	}
}

// count existing labels that haven't received any releases for more than $appOldLabelDays days
$data_params = new QueryData();
$sql = 'SELECT x.vendor_id, DATE_FORMAT(MAX(x.maxDate), "%Y-%m-%d") AS maxDate
						FROM (
							SELECT cdr.vendor_id, MAX(cdr.date_received) AS maxDate
							FROM cd_receive cdr, vendor v
							WHERE cdr.vendor_id = v.vendor_id
                                           AND v.status = "signed"';
if ($aggregator_login) {
    $sql .= $non_orch_owner;
    $data_params = addSessionOwnerDataParam($data_params);
} elseif ($summary_user_id) {
    $sql .= ' AND v.assigned_to = ?';
    $data_params->addDataParam('i', $summary_user_id);
} else {
    $sql .= ' AND v.assigned_to != 0 AND v.assigned_to IS NOT NULL';
}
$sql .= ' GROUP BY cdr.vendor_id
            UNION
            SELECT b.vendor_id, MAX(a.ingestion_completed) AS maxDate
            FROM releases a
                INNER JOIN artist_info b ON a.artist_id = b.artist_id
                INNER JOIN vendor v ON b.vendor_id = v.vendor_id
            WHERE v.status = "signed"
            AND a.release_status = "in_content"';
if ($aggregator_login) {
    $sql .= $non_orch_owner;
    $data_params = addSessionOwnerDataParam($data_params);
} elseif ($summary_user_id) {
    $sql .= ' AND v.assigned_to = ?';
    $data_params->addDataParam('i', $summary_user_id);
} else {
    $sql .= ' AND v.assigned_to != 0 AND v.assigned_to IS NOT NULL';
}
$sql .= ' GROUP BY b.vendor_id
         HAVING b.vendor_id > 0
        ) x
        GROUP BY x.vendor_id';
$vend_data = $wrapperObj->executeQuery($sql, $data_params);
if ($vend_data === false) {
    showierror($sql);
}

$varLastDate =  date('Y-m-d', strtotime("-$appOldLabelDays day"));
foreach ($vend_data as $RowData) {
	if ($RowData['maxDate'] < $varLastDate) {
		$varOldLblNoRelTotal++;
	}
}

// count release waiting delivery
$sql = 'SELECT r.upc, r.ingestion_completed
					FROM vendor v
						INNER JOIN artist_info ai ON ai.vendor_id = v.vendor_id
						INNER JOIN releases r ON r.artist_id = ai.artist_id
						LEFT JOIN delivery_history dd ON r.upc = dd.upc
					WHERE v.status = "signed"
						AND r.release_status = "in_content"
						' . ($aggregator_login ? $non_orch_owner : ($summary_user_id ? 'AND v.assigned_to = ?' : 'AND v.assigned_to != 0 AND v.assigned_to IS NOT NULL')) . '
						AND dd.upc IS NULL
						AND r.ingestion_completed >= "2005-08-01"';
$encode_data = $wrapperObj->executeQuery($sql, $dataParams);

if ($encode_data === false) {
    showierror($sql);
}
$varLastDate = date('Y-m-d', strtotime("-$appWaitingDeliveryDays day"));
foreach ($encode_data as $encode_row) {
	if ($encode_row['ingestion_completed'] < $varLastDate)
		$varWaitingDelivery++;
	$varWaitingDeliveryTotal++;
}

// count cd received but not entered into label copy for more than $appCDreceivedDays days
/*$sql = 'SELECT cdr.upc, cdr.date_received
					FROM cd_receive cdr
						INNER JOIN vendor v ON cdr.vendor_id = v.vendor_id
							AND v.status = "signed"
							' . ($aggregator_login ? 'AND v.owner = ' . QueryFormatString($_SESSION['S_OA_OWNER']) : ($summary_user_id ? 'AND v.assigned_to = ' . $summary_user_id : 'AND v.assigned_to != 0 AND v.assigned_to IS NOT NULL')) . '
						LEFT OUTER JOIN releases r ON cdr.upc = r.upc
					WHERE r.upc IS NULL';*/
$sql = 'SELECT cdr.upc, cdr.date_received
					FROM cd_receive cdr
						INNER JOIN vendor v ON cdr.vendor_id = v.vendor_id
							AND v.status = "signed"
							' . ($aggregator_login ? $non_orch_owner : ($summary_user_id ? 'AND v.assigned_to = ?' : 'AND v.assigned_to != 0 AND v.assigned_to IS NOT NULL')) . '
						LEFT OUTER JOIN releases r ON cdr.upc = r.upc
					WHERE r.release_status NOT IN ("in_content","transfer_to_content")';
$cd_receive = $wrapperObj->executeQuery($sql, $dataParams);
if ($cd_receive === false) {
    showierror($sql);
}
$varLastDate = date('Y-m-d', strtotime("-$appCDreceivedDays day"));
foreach ($cd_receive as $cd_row) {
	if ($cd_row['date_received'] < $varLastDate)
	$varCDReceived++;
	$varTotalCDReceived++;
}
?>
<div class="panelHeader">
<h3>Status Summary</h3>
</div>

			<div class="panelContent">
				<table class="data">
					<tr>
						<th>My Alerts</th>
						<th class="center">Total</th>
						<th class="center">Alert</th>
						<th></th>
					</tr>
					<tr><td>Releases Received, but No Label Copy.</td>
						<td class="right"><? if ($varTotalCDReceived) { ($super_pm ? '&super_pm=' . $super_pm . '&summary_user_id=' . $summary_user_id : '')?><?=($super_pm ? ' TARGET="_blank"' : '')?><?=$varTotalCDReceived?><? } else { ?>0<? } ?></td>
						<td class="right"><? if ($varCDReceived) { ($super_pm ? '&super_pm=' . $super_pm . '&summary_user_id=' . $summary_user_id : '')?><?=($super_pm ? ' TARGET="_blank"' : '')?><?=$varCDReceived?><? } else { ?>0<? } ?></td>
						<td>(<?=$appCDreceivedDays?> days)</td>
					</tr>
					<tr class="alt"><td>Releases in Label Copy.</td>
						<td class="right"><? if ($varTotalOPReleases) { ($super_pm ? '&super_pm=' . $super_pm . '&summary_user_id=' . $summary_user_id : '')?><?=($super_pm ? ' TARGET="_blank"' : '')?><?=$varTotalOPReleases?><? } else { ?>0<? } ?></td>
						<td class="right"><? if ($varOPReleases) { ($super_pm ? '&super_pm=' . $super_pm . '&summary_user_id=' . $summary_user_id : '')?><?=($super_pm ? ' TARGET="_blank"' : '')?><?=$varOPReleases?><? } else { ?>0<? } ?></td>
						<td>(<?=$appLabelOPDays?> days)</td>
					</tr>
	<tr class="alt">
		<td>Releases Awaiting Transfer to Content.</td>
		<td class="right"><? if ($varTotalNotXferred) { ($super_pm ? '&super_pm=' . $super_pm . '&summary_user_id=' . $summary_user_id : '')?>
			<?=($super_pm ? ' TARGET="_blank"' : '')?><?=$varTotalNotXferred?><? } else { ?>0<? } ?></td>
		<td class="right"><? if ($varLabelNotXferred) { ($super_pm ? '&super_pm=' . $super_pm . '&summary_user_id=' . $summary_user_id : '')?>
			<?=($super_pm ? ' TARGET="_blank"' : '')?><?=$varLabelNotXferred?><? } else { ?>0<? } ?></td>
		<td>(<?=$appNotTransferredDays?> days)</td>
	</tr>
	<tr class="alt">
		<td>Releases Awaiting Delivery. *(Since 2005-08-01)</td>
		<td class="right"><? if ($varWaitingDeliveryTotal) { ($super_pm ? '&super_pm=' . $super_pm . '&summary_user_id=' . $summary_user_id : '')?>
			<?=($super_pm ? ' TARGET="_blank"' : '')?><?=$varWaitingDeliveryTotal?><? } else { ?>0<? } ?></td>
		<td class="right"><? if ($varWaitingDelivery) { ($super_pm ? '&super_pm=' . $super_pm . '&summary_user_id=' . $summary_user_id : '')?>
			<?=($super_pm ? ' TARGET="_blank"' : '')?><?=$varWaitingDelivery?><? } else { ?>0<? } ?></td>
		<td>(<?=$appWaitingDeliveryDays?> days)</td>
	</tr>
	<tr>
		<td colspan="4" class="formSubHeader">&nbsp;</td>
	</tr>
	<tr class="alt">
		<td>Labels - No Content Received.</td>
		<td class="right"><? if ($varTotalNewLblNoRel) { ($super_pm ? '&super_pm=' . $super_pm . '&summary_user_id=' . $summary_user_id : '')?>
			<?=($super_pm ? ' TARGET="_blank"' : '')?><?=$varTotalNewLblNoRel?><? } else { ?>0<? } ?></td>
		<td class="right"><? if ($varNewLblNoRelTotal) { ($super_pm ? '&super_pm=' . $super_pm . '&summary_user_id=' . $summary_user_id : '')?>
			<?=($super_pm ? ' TARGET="_blank"' : '')?><?=$varNewLblNoRelTotal?><? } else { ?>0<? } ?></td>
		<td>(<?=$appNewLabelDays?> days)</td>
	</tr>
	<tr>
		<td>Inactive Labels.</td>
		<td class="right">-</td>
		<td class="right"><? if ($varOldLblNoRelTotal) { ($super_pm ? '&super_pm=' . $super_pm . '&summary_user_id=' . $summary_user_id : '')?>
			<?=($super_pm ? ' TARGET="_blank"' : '')?><?=$varOldLblNoRelTotal?><? } else { ?>0<? } ?></td>
		<td>(<?=$appOldLabelDays?> days)</td>
	</tr>

</table>
</div>