<?php
require_once(__DIR__ . '/../../../../vendor/autoload.php');
use Orchard\Mysqli\QueryData;
use Orchard\Mysqli\Wrapper as MysqliWrapper;

$wrapperObj = new MysqliWrapper($mysqli);

$sql = 'SELECT upc, DATEDIFF(encoding_date, date_received) AS days_taken
    FROM cd_receive
    WHERE encoding_date BETWEEN ? AND ?';
$dataParams = new QueryData();
$dataParams->addDataParam('s', $from_date);
$dataParams->addDataParam('s', $to_date);
$result = $wrapperObj->executeQuery($sql, $dataParams);
if ($result === false) {
    showierror($sql);
}
$array_1 = array();
foreach ($result as $row) {
    $array_1[$row['upc']] = $row['days_taken'];
}

$sql = 'SELECT r.upc, DATEDIFF(r.ingestion_completed, cd.date_received) AS days_taken
    FROM releases r
    INNER JOIN cd_receive cd ON r.upc = cd.upc
    WHERE r.upc > 0
        AND r.ingestion_completed BETWEEN ? AND ? AND DATEDIFF(r.ingestion_completed, cd.date_received) <= 25';
$dataParams = new QueryData();
$dataParams->addDataParam('s', $from_date);
$dataParams->addDataParam('s', $to_date);
$result = $wrapperObj->executeQuery($sql, $dataParams);
if ($result === false) {
    showierror($sql);
}
$array_2 = array();
foreach ($result as $row) {
    $array_2[$row['upc']] = $row['days_taken'];
}

$sql = 'SELECT r.upc, DATEDIFF(IF(r.ingestion_completed IS NULL OR r.ingestion_completed = "0000-00-00",
    r.release_date, r.ingestion_completed), ears.date) AS days_taken
    FROM releases r
    INNER JOIN release_status ears ON r.release_id = ears.release_id
    WHERE r.ingestion_completed BETWEEN ? AND ? AND ears.status = "orchard_processing"';
$dataParams = new QueryData();
$dataParams->addDataParam('s', $from_date);
$dataParams->addDataParam('s', $to_date);
$result = $wrapperObj->executeQuery($sql, $dataParams);
if ($result === false) {
    showierror($sql);
}
$array_3 = array();
foreach ($result as $row) {
    $array_3[$row['upc']] = $row['days_taken'];
}

$sql = 'SELECT r.upc,
    DATEDIFF(MIN(eo.encoder_delivery_date),
    IF(r.ingestion_completed IS NULL OR r.ingestion_completed = "0000-00-00", r.release_date, r.ingestion_completed)) AS days_taken
    FROM encoding_order eo
    INNER JOIN encoding_order_detail eod ON eo.encoding_order_id = eod.encoding_order_id
    INNER JOIN releases r ON r.upc = eod.upc
    WHERE eo.encoder_delivery_date BETWEEN ? AND ?
        AND eo.type = "track"
        AND eo.order_status = "close"
        AND r.upc NOT IN (
            SELECT eodx.upc
                FROM encoding_order eox
                INNER JOIN encoding_order_detail eodx ON eox.encoding_order_id = eodx.encoding_order_id
                WHERE eox.encoder_delivery_date < ?
                    AND eox.type = "track"
                    AND eox.order_status = "close"
                    AND eodx.upc = eod.upc
        )
    GROUP BY r.upc HAVING days_taken <= 25';
$dataParams = new QueryData();
$dataParams->addDataParam('s', $from_date);
$dataParams->addDataParam('s', $to_date);
$dataParams->addDataParam('s', $from_date);
$result = $wrapperObj->executeQuery($sql, $dataParams);
if ($result === false) {
    showierror($sql);
}
$array_4 = array();
foreach ($result as $row) {
    $array_4[$row['upc']] = $row['days_taken'];
}

$sql = 'SELECT dd.upc, DATEDIFF(MIN(dd.date_delivered), MAX(eo.encoder_delivery_date)) AS days_taken
    FROM encoding_order eo
    INNER JOIN encoding_order_detail eod ON eo.encoding_order_id = eod.encoding_order_id
    INNER JOIN delivery_history dd ON dd.upc = eod.upc
    WHERE eo.type = "track"
        AND eo.order_status = "close"
        AND eo.encoder_id = dd.encoder_id
        AND eo.encoder_id IN (1,9)
        AND eo.encoder_delivery_date < dd.date_delivered
        AND dd.date_delivered BETWEEN ? AND ?
    GROUP BY dd.upc';
$dataParams = new QueryData();
$dataParams->addDataParam('s', $from_date);
$dataParams->addDataParam('s', $to_date);
$result = $wrapperObj->executeQuery($sql, $dataParams);
if ($result === false) {
    showierror($sql);
}
$array_5 = array();
foreach ($result as $row) {
	$array_5[$row['upc']] = $row['days_taken'];
}
$count1 = count($array_1) ? round(array_sum($array_1) / count(array_keys($array_1))) : '';
$count2 = count($array_2) ? round(array_sum($array_2) / count(array_keys($array_2))) : '';
$count3 = count($array_3) ? round(array_sum($array_3) / count(array_keys($array_3))) : '';
$count4 = count($array_4) ? round(array_sum($array_4) / count(array_keys($array_4))) : '';
$count5 = count($array_5) ? round(array_sum($array_5) / count(array_keys($array_5))) : '';

$varTable .= '
			<table' . ($no_url ? ' border=1' : ' class="data"') . '>
			<tr><th></th>
				<th class="center">Average # of Days per Release</th>
			</tr>
			<tr>
				<td>CD Received to Date Ripped</td>
				<td align="center">' . ($no_url ? $count1 : '<a href="/report/release_transition_report_detail.php?tp=1&from_date=' . $from_date . '&to_date=' . $to_date . '">' . $count1 . '</a>') . '</td>
			</tr>
			<tr class="alt">
				<td>CD Received to Uploaded to OA</td>
				<td align="center">' . ($no_url ? $count2 : '<a href="/report/release_transition_report_detail.php?tp=2&from_date=' . $from_date . '&to_date=' . $to_date . '">' . $count2 . '</a>') . '</td>
			</tr>
			<tr>
				<td>Uploaded to OA to Transferred to Content</td>
				<td align="center">' . ($no_url ? $count3 : '<a href="/report/release_transition_report_detail.php?tp=3&from_date=' . $from_date . '&to_date=' . $to_date . '">' . $count3 . '</a>') . '</td>
			</tr>
			<tr class="alt">
				<td>Transferred to Content to Delivered to Encoder</td>
				<td align="center">' . ($no_url ? $count4 : '<a href="/report/release_transition_report_detail.php?tp=4&from_date=' . $from_date . '&to_date=' . $to_date . '">' . $count4 . '</a>') . '</td>
			</tr>
			<tr>
				<td>Delivered to Encoder to 1st Delivery</td>
				<td align="center">' . ($no_url ? $count5 : '<a href="/report/release_transition_report_detail.php?tp=5&from_date=' . $from_date . '&to_date=' . $to_date . '">' . $count5 . '</a>') . '</td>
			</tr>
			</table>';
?>