<?php
require_once (__DIR__ . '/../../../vendor/autoload.php');

$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;

$data_params = [];

$whereClause = ' AND eq.encoding_order_type = "ringtone"';
if ($refine_encoding_order_id) {
    $whereClause .= ' AND eq.encoding_order_id = ?';
    $data_params[] = $refine_encoding_order_id;
}
if ($showHideDetails == "hide" || !$showHideDetails) {
    $art_whereClause .= ' AND ro.archived = "N"';
}
if (is_array($refine_dms) && count($refine_dms)) {
    $get_encoding_queue_ids = 'select distinct eq.encoding_queue_id
        from `direct_delivery`.encoding_queue eq
        inner join `direct_delivery`.encoding_queue_detail eqd on eqd.encoding_queue_id = eq.encoding_queue_id
        where eqd.dms_master_master_id in (' . implode(',', array_fill(0, count($refine_dms), '?')) . ')';
    $stmt = $ddDb->getMysqlConnection()->prepare($get_encoding_queue_ids);
    if (!$stmt->execute($refine_dms)) {
        show_pdo_error($get_encoding_queue_ids, $stmt);
    }
    $encoding_queue_ids_arr = array();
    foreach ($stmt->fetchAll() as $get_encoding_queue_ids_row) {
        $encoding_queue_ids_arr[] = $get_encoding_queue_ids_row['encoding_queue_id'];
    }
    $whereClause .= ' AND eqd.encoding_queue_id in (';
    if (count($encoding_queue_ids_arr)) {
        $whereClause .= implode(',', array_fill(0, count($encoding_queue_ids_arr), '?')) . ')';
        $data_params = array_merge($data_params, $encoding_queue_ids_arr);
    } else {
        $whereClause .= '0)';
    }
}
if ($refine_priority) {
    $whereClause .= ' AND eq.priority = ?';
    $data_params[] = $refine_priority;
}

$havingClause = '';
if ($refine_status) {
    $havingClause = '';
    switch ($refine_status) {
        case 'new':
            $havingClause .= 'new_count > 0 ';
            break;
        case 'error':
            $havingClause .= 'error_count > 0 ';
            break;
        case 'waiting':
            $havingClause .= 'wait_count > 0 ';
            break;
        case 'processing':
            $havingClause .= 'process_count > 0 ';
            break;
        case 'completed':
            $havingClause .= 'complete_count > 0 ';
            break;
        case 'cancelled':
            $havingClause .= 'cancelled_count > 0 ';
            break;
        default:
            $havingClause = '';
            break;
    }
}

$sql = 'SELECT eq.encoding_order_id,
        eq.encoding_queue_id, GROUP_CONCAT(DISTINCT eqd.dms_master_master_id) AS dms_ids, eq.priority,
        SUM(if(eqd.status = "new",1,0)) AS new_count,
        SUM(if(eqd.status IN ("image_missing","audio_missing","incorrect_audio_count","metadata_missing","metadata_error","audio_missing_for_delivery","image_missing_for_delivery","incorrect_audio_count_for_delivery","delivery_failure","system_error","audio_encoding_error","image_encoding_error","system_cancelled"),1,0)) AS error_count,
        SUM(if(eqd.status IN ("ready_to_encode","encoded","queued_for_delivery","ready_to_deliver"),1,0)) AS wait_count,
        SUM(if(eqd.status = "ready_to_encode",1,0)) AS e_wait_count,
        SUM(if(eqd.status IN ("encoded","queued_for_delivery","ready_to_deliver"),1,0)) AS d_wait_count,
        SUM(if(eqd.status IN ("encoding","delivering"),1,0)) AS process_count,
        SUM(if(eqd.status = "encoding",1,0)) AS e_process_count,
        SUM(if(eqd.status = "delivering",1,0)) AS d_process_count,
        SUM(if(eqd.status = "delivered",1,0)) AS complete_count,
        SUM(if(eqd.status IN ("cancelled", "ineligible"),1,0)) AS cancelled_count
    FROM `direct_delivery`.encoding_queue eq
        INNER JOIN `direct_delivery`.encoding_queue_detail eqd ON eq.encoding_queue_id = eqd.encoding_queue_id
    WHERE 1 '.$whereClause.'
    GROUP BY eq.encoding_order_id '
    . ($havingClause ? 'HAVING '. $havingClause : '');
$stmt = $ddDb->getMysqlConnection()->prepare($sql);

if (!$stmt->execute($data_params)) {
    show_pdo_error($sql, $stmt);
}

$main_arr = array();
foreach ($stmt->fetchAll() as $row) {
    $main_arr[$row['encoding_order_id']]['encoding_order_id'] = $row['encoding_order_id'];
    $main_arr[$row['encoding_order_id']]['encoding_queue_id'] = $row['encoding_queue_id'];
    $main_arr[$row['encoding_order_id']]['dms_ids'] = $row['dms_ids'];
    $main_arr[$row['encoding_order_id']]['priority'] = $row['priority'];
    $main_arr[$row['encoding_order_id']]['new_count'] = $row['new_count'];
    $main_arr[$row['encoding_order_id']]['error_count'] = $row['error_count'];
    $main_arr[$row['encoding_order_id']]['wait_count'] = $row['wait_count'];
    $main_arr[$row['encoding_order_id']]['e_wait_count'] = $row['e_wait_count'];
    $main_arr[$row['encoding_order_id']]['d_wait_count'] = $row['d_wait_count'];
    $main_arr[$row['encoding_order_id']]['process_count'] = $row['process_count'];
    $main_arr[$row['encoding_order_id']]['e_process_count'] = $row['e_process_count'];
    $main_arr[$row['encoding_order_id']]['d_process_count'] = $row['d_process_count'];
    $main_arr[$row['encoding_order_id']]['complete_count'] = $row['complete_count'];
    $main_arr[$row['encoding_order_id']]['cancelled_count'] = $row['cancelled_count'];
}
$encoding_order_ids = array();
$encoding_order_ids = array_keys($main_arr);

$main_arr2 = array();
if (count($encoding_order_ids)) {
    $sql2 = 'SELECT ro.ringtone_order_id, ro.date, concat(ou.f_name, " ", ou.l_name) as created_by, ro.encoder_id, ro.order_spec
        FROM `art_relations`.ringtone_order ro
        LEFT JOIN `art_relations`.orchadmin_users ou on ou.id = ro.orchadmin_user_id
        WHERE ro.ringtone_order_id in(' . implode(',', array_fill(0, count($encoding_order_ids), '?')) . ')'
        . $art_whereClause;
    $stmt = $artDb->getMysqlConnection()->prepare($sql2);
    if (!$stmt->execute($encoding_order_ids)) {
        show_pdo_error($sql2, $stmt);
    }

    foreach ($stmt->fetchAll() as $row2) {
        if (isset($main_arr[$row2['ringtone_order_id']])) {
            $main_arr[$row2['ringtone_order_id']]['ringtone_order_id'] = $row2['ringtone_order_id'];
            $main_arr[$row2['ringtone_order_id']]['encoder_delivery_date'] = $row2['date'];
            $main_arr[$row2['ringtone_order_id']]['created_by'] = $row2['created_by'];
            $main_arr[$row2['ringtone_order_id']]['encoder_id'] = $row2['encoder_id'];
            $main_arr[$row2['ringtone_order_id']]['order_spec'] = $row2['order_spec'];
            $main_arr2[$row2['ringtone_order_id']] = array();
        }
    }
}
// $main_arr = array_intersect_key($main_arr,$main_arr2);
$common_keys = array_intersect(array_keys($main_arr), array_keys($main_arr2));
$new_arr = array();
if (count($common_keys)) {
    foreach ($common_keys as $key) {
        $new_arr[$key] = $main_arr[$key];
    }
}
$main_arr = $new_arr;

switch ($sort_by) {
    case 'ringtone_order_id':
        $main_arr = multisort($main_arr,'encoding_order_id', !$sort_desc, 2);
        break;
    case 'priority':
        $main_arr = multisort($main_arr,'priority', !$sort_desc, 2);
        break;
    case 'new_count':
        $main_arr = multisort($main_arr,'new_count', !$sort_desc, 2);
        break;
    case 'error_count':
        $main_arr = multisort($main_arr,'error_count', !$sort_desc, 2);
        break;
    case 'wait_count':
        $main_arr = multisort($main_arr,'wait_count', !$sort_desc, 2);
        break;
    case 'process_count':
        $main_arr = multisort($main_arr,'process_count', !$sort_desc, 2);
        break;
    case 'complete_count':
        $main_arr = multisort($main_arr,'complete_count', !$sort_desc, 2);
        break;
    case 'cancelled_count':
        $main_arr = multisort($main_arr,'cancelled_count', !$sort_desc, 2);
        break;
    case 'order_spec':
        $main_arr = multisort($main_arr,'order_spec', !$sort_desc, 4);
        break;
    case 'encoder_delivery_date':
        $main_arr = multisort($main_arr,'encoder_delivery_date', !$sort_desc, 4);
        break;
    default:
        $sort_by = 'ringtone_order_id';
        $sort_desc = 1;
        $main_arr = multisort($main_arr, $sort_by, false, 2);
}

if (!$no_url && count($main_arr)) {
    $total = count($main_arr);
    if($pageRecord){
        $pageNum = ceil($total/$pageRecord);
    }
    $main_arr = array_slice($main_arr, !$viewall ? 0 : (($page-1)*$pageRecord), !$viewall ? 5 : $pageRecord);
}

$report_data = '
            <table' . (($no_url or !$viewall) ? ' border=1' : ' class="data"') . '>
            <tr>'.((!$no_url and $viewall) ? '<th><input type="checkbox" name="check_all" onclick="checkAll(\'rod_ids[]\', this.checked);" title="Check/Uncheck All" /></th>' : '') . '
                <th>' . (($no_url or !$viewall) ? 'Ringtone Order ID' : sortColumnHeader('Ringtone Order ID', 'ringtone_order_id', $url)) . '</th>
                <th class="center">' . (($no_url or !$viewall) ? 'Ringtone Order Date' : sortColumnHeader('Ringtone Order Date', 'encoder_delivery_date', $url)) . '</th>
                <th class="center">'.(($no_url or !$viewall) ? 'Order Spec' : sortColumnHeader('Order Spec', 'order_spec', $url)) .'</th>
                <th class="center">DMS</th>
                <th class="center" nowrap>' . (($no_url or !$viewall) ? 'Priority' : sortColumnHeader('Priority', 'priority', $url)) . '</th>
                <th class="center">' . (($no_url or !$viewall) ? 'New' : sortColumnHeader('New', 'new_count', $url)) . '</th>
                <th class="center">' . (($no_url or !$viewall) ? 'Cancelled' : sortColumnHeader('Cancelled', 'cancelled_count', $url)) . '</th>
                <th class="center">' . (($no_url or !$viewall) ? 'Error' : sortColumnHeader('Error', 'error_count', $url)) . '</th>
                <th class="center">' . (($no_url or !$viewall) ? 'Waiting' : sortColumnHeader('Waiting', 'wait_count', $url)) . '</th>
                <th class="center">' . (($no_url or !$viewall) ? 'Processing' : sortColumnHeader('Processing', 'process_count', $url)) . '</th>
                <th class="center">' . (($no_url or !$viewall) ? 'Completed' : sortColumnHeader('Completed', 'complete_count', $url)) . '</th>
            </tr>';

foreach ($main_arr as $k=>$details) {
    $row_class = (++$rci & 1) ? '' : 'alt';
    $dms_ids = explode(',', $details['dms_ids']);

    $sql = 'SELECT customer_name FROM `art_relations`.customer_master_master
        WHERE customer_master_master_id in (';
    if (count($dms_ids)) {
        $sql .= implode(',', array_fill(0, count($dms_ids), '?')) . ')';
    } else {
        $sql .= '0)';
    }
    $sql .= ' ORDER BY customer_name';
    $stmt = $artDb->getMysqlConnection()->prepare($sql);

    if (!$stmt->execute($dms_ids)) {
        show_pdo_error($sql, $stmt);
    }
    $dms_arr = array();
    foreach ($stmt->fetchAll() as $dms_row) {
        $dms_arr[] = $dms_row['customer_name'];
    }
    $dms_list = implode('; ',$dms_arr);

    $report_data .= '
        <tr class="' . $row_class . '">';
        if (!$no_url){
            $report_data .= '
            <td class="rowSelect"><input type="checkbox" name="rod_ids[]" value="'.$details['ringtone_order_id'].'" onclick="if (!this.checked) document.arc_meta.check_all.checked=false;" /></td>';
        }
        $report_data.='
            <td>' . (($no_url or !$viewall) ? $details['ringtone_order_id'] : '<a href="/marketing/edit_ringtone_cut_order.php?ringtone_order_id=' . $details['encoding_order_id'] . '" title="View Ringtone Order">' . $details['encoding_order_id'] . '</a>') .'</td>
            <td>' . format_date($details['encoder_delivery_date']) . '</td>
            <td>' . $details['order_spec'] . '</td>
            <td>' . $dms_list .'</td>
            <td NOWRAP>' . ($details['priority']==1?"1 - High":($details['priority']==2?"2 - Normal":"3 - Low")) . '</td>
            <td align="right">' . ($details['new_count'] == 0 ? '-' : ($no_url ?  $details['new_count'] : '<a href="/warehouse/ringtone_direct_detail.php?encoding_queue_id='.$details['encoding_queue_id'].'&type=new">' . $details['new_count'] . '</a>')) . '</td>
            <td align="right">' . ($details['cancelled_count'] == 0 ? '-' : ($no_url ? $details['cancelled_count'] : '<a href="/warehouse/ringtone_direct_detail.php?encoding_queue_id='.$details['encoding_queue_id'].'&type=cancelled">' . $details['cancelled_count'] . '</a>')) . '</td>
            <td align="right">' . ($details['error_count'] == 0 ? '-' : ($no_url ? $details['error_count'] : '<a href="/warehouse/ringtone_direct_detail.php?encoding_queue_id='.$details['encoding_queue_id'].'&type=error">' . $details['error_count'] . '</a>')) . '</td>
            <td align="right" nowrap>' . ($details['wait_count'] == 0 ? '-' : (($no_url ? $details['e_wait_count'] . ' (E)' : '<a href="/warehouse/ringtone_direct_detail.php?encoding_queue_id='.$details['encoding_queue_id'].'&type=wait&subtype=ready_to_encode">' . $details['e_wait_count'] . ' (E)</a>') . '
                <br />' . ($no_url ? $details['d_wait_count'] . ' (D)' : '<a href="/warehouse/ringtone_direct_detail.php?encoding_queue_id='.$details['encoding_queue_id'].'&type=wait&subtype=ready_to_deliver">' . $details['d_wait_count'] . ' (D)</a>'))) . '</td>
            <td align="right" nowrap>' . ($details['process_count'] == 0 ? '-' : (($no_url ? $details['e_process_count'] . ' (E)' : '<a href="/warehouse/ringtone_direct_detail.php?encoding_queue_id='.$details['encoding_queue_id'].'&type=process&subtype=encoding">' . $details['e_process_count'] . ' (E)</a>') . '
                <br />' . ($no_url ? $details['d_process_count'] . ' (D)' : '<a href="/warehouse/ringtone_direct_detail.php?encoding_queue_id='.$details['encoding_queue_id'].'&type=process&subtype=delivering">' . $details['d_process_count'] . ' (D)</a>'))) . '</td>
            <td align="right">' . ($details['complete_count'] == 0 ? '-' : ($no_url ? $details['complete_count'] : '<a href="/warehouse/ringtone_direct_detail.php?encoding_queue_id='.$details['encoding_queue_id'].'&type=complete">' . $details['complete_count'] . '</a>')) . '</td>
        </tr>';
}
$report_data .= '</table>';