<?php

/**
 * @Created by          : Heru Subekti (heroe.soebekti@gmail.com)
 * @Date                : 2020-02-29
 * @File name           : procurement_report.php
 */


// key to authenticate
define('INDEX_AUTH', '1');

// main system configuration
require '../../../../sysconfig.inc.php';
// IP based access limitation
require LIB . 'ip_based_access.inc.php';
do_checkIP('smc');
do_checkIP('smc-reporting');
// start the session
require SB . 'admin/default/session.inc.php';
require SB . 'admin/default/session_check.inc.php';
require SIMBIO . 'simbio_GUI/form_maker/simbio_form_element.inc.php';
// privileges checking
$can_read = utility::havePrivilege('reporting', 'r');
$can_write = utility::havePrivilege('reporting', 'w');

if (!$can_read) {
    die('<div class="errorBox">' . __('You don\'t have enough privileges to access this area!') . '</div>');
}

$page_title = 'Procurement Report';
$reportView = false;
if (isset($_GET['reportView'])) {
    $reportView = true;
}

if (!$reportView) {
?>
    <div class="menuBox">
        <div class="menuBoxInner">
            <div class="per_title">
                <h2><?php echo __('Procurement Report'); ?></h2>
            </div>
            <div class="infoBox"><?= __('Displays a collection procurement report') ?></div>
        </div>
    </div>
    <iframe name="reportView" id="reportView" src="<?php echo $_SERVER['PHP_SELF'] . '?reportView=true'; ?>" frameborder="0" style="width: 100%; height: 500px;"></iframe>
<?php
} else {
    ob_start();

    // class array
    for ($i = 0; $i < 10; $i++) {
        $class[$i . '00'] = $i;
    }
    $class[__('Others')] = __('Others');

    // table start
    $first_header = '';
    $second_header = '';
    $dataset = array();
    $detail_class_coll = '<th class="text-center small" title="' . __('Title') . '">' . __('t') . '</th><th class="text-center small" title="' . __('Item') . '">' . __('i') . '</th>';
    // table header
    $output = '<table class="s-table table table-sm table-bordered mb-0">';
    $output .= '<tr><th rowspan="2">' . __('Receiving Year') . ' / ' . __('Classification') . '</th>';
    foreach ($class as $class_num => $month) {
        $first_header .= '<th colspan="2" class="text-center">' . $class_num . '</th>';
        $second_header .= $detail_class_coll;
        $xAxis[$class_num] = $class_num;
    }
    $output .= $first_header;
    $output .= '<th colspan="2" class="text-center">' . __('ALL') . '</th><th rowspan="2" class="text-center">' . __('Options') . '</th></tr>';
    $output .= '<tr>' . $second_header . $detail_class_coll . '</tr>';

    // get year data from database
    // use received_date as primary, input_date as secondary fallback
    // exclude NULL/empty/'0000-00-00' invalid dates to avoid YEAR() errors in strict SQL modes
    $_q = $dbs->query("SELECT YEAR(
        CASE 
            WHEN received_date IS NOT NULL 
                AND received_date NOT IN ('', '0000-00-00','0000-00-00 00:00:00') 
                AND received_date RLIKE '^[0-9]{4}-[0-9]{2}-[0-9]{2}' 
            THEN received_date
            WHEN input_date IS NOT NULL 
                AND input_date NOT IN ('', '0000-00-00','0000-00-00 00:00:00') 
                AND input_date RLIKE '^[0-9]{4}-[0-9]{2}-[0-9]{2}' 
            THEN input_date
            ELSE NULL
        END
    ) AS YEAR 
    FROM item 
    WHERE (
        (received_date IS NOT NULL 
         AND received_date NOT IN ('', '0000-00-00','0000-00-00 00:00:00') 
         AND received_date RLIKE '^[0-9]{4}-[0-9]{2}-[0-9]{2}')
        OR 
        (input_date IS NOT NULL 
         AND input_date NOT IN ('', '0000-00-00','0000-00-00 00:00:00') 
         AND input_date RLIKE '^[0-9]{4}-[0-9]{2}-[0-9]{2}')
    )
    GROUP BY YEAR(
        CASE 
            WHEN received_date IS NOT NULL 
                AND received_date NOT IN ('', '0000-00-00','0000-00-00 00:00:00') 
                AND received_date RLIKE '^[0-9]{4}-[0-9]{2}-[0-9]{2}' 
            THEN received_date
            WHEN input_date IS NOT NULL 
                AND input_date NOT IN ('', '0000-00-00','0000-00-00 00:00:00') 
                AND input_date RLIKE '^[0-9]{4}-[0-9]{2}-[0-9]{2}' 
            THEN input_date
            ELSE NULL
        END
    )");

    if ($_q && $_q->num_rows > 0) {
        while ($_d = $_q->fetch_row()) {
            $years[$_d[0]] = $_d[0];
        }
        $years[__('ALL')] = __('ALL');

        foreach ($years as $year_num => $year) {

            $total_y_title = 0;
            $total_y_item = 0;
            $output .= '<tr><td><strong>' . $year . '</strong></td>';

            foreach ($class as $class_name => $class_value) {

                //filter by classification
                $classes = preg_match('/^([^0-9]+)$/', $class_value) ? " (trim(b.classification) REGEXP '^[^0-9]' OR trim(b.classification)='' OR trim(b.classification) IS NULL) " : " trim(b.classification) LIKE '" . $class_value . "%' ";

                $_q = $dbs->query("SELECT i.item_code,i.biblio_id 
                    FROM item i LEFT JOIN biblio b ON b.biblio_id=i.biblio_id 
                    WHERE  b.title IS NOT NULL 
                    AND (
                        (i.received_date IS NOT NULL 
                         AND i.received_date NOT IN ('', '0000-00-00','0000-00-00 00:00:00') 
                         AND i.received_date RLIKE '^[0-9]{4}-[0-9]{2}-[0-9]{2}')
                        OR 
                        (i.input_date IS NOT NULL 
                         AND i.input_date NOT IN ('', '0000-00-00','0000-00-00 00:00:00') 
                         AND i.input_date RLIKE '^[0-9]{4}-[0-9]{2}-[0-9]{2}')
                    )
                    AND " . $classes . ($year == __('ALL') ? '' : ' AND YEAR(
                        CASE 
                            WHEN i.received_date IS NOT NULL 
                                AND i.received_date NOT IN (\'\', \'0000-00-00\',\'0000-00-00 00:00:00\') 
                                AND i.received_date RLIKE \'^[0-9]{4}-[0-9]{2}-[0-9]{2}\' 
                            THEN i.received_date
                            WHEN i.input_date IS NOT NULL 
                                AND i.input_date NOT IN (\'\', \'0000-00-00\',\'0000-00-00 00:00:00\') 
                                AND i.input_date RLIKE \'^[0-9]{4}-[0-9]{2}-[0-9]{2}\' 
                            THEN i.input_date
                            ELSE NULL
                        END
                    ) = ' . $year));

                $dataset[$year]['title'][$class_name] = 0;
                $dataset[$year]['item'][$class_name] = 0;

                if ($_q && $_q->num_rows > 0) {
                    while ($_d = $_q->fetch_row()) {
                        $data['item'][$class_name][$_d[0]] = $_d[0];
                        $data['title'][$class_name][$_d[1]] = $_d[1];
                    }
                    $dataset[$year]['title'][$class_name] = count($data['title'][$class_name]);
                    $dataset[$year]['item'][$class_name] = count($data['item'][$class_name]);
                }

                // table content
                $count_title = (isset($data['title'][$class_name]) && is_array($data['title'][$class_name])) ? count($data['title'][$class_name]) : 0;
                $count_item   = (isset($data['item'][$class_name]) && is_array($data['item'][$class_name])) ? count($data['item'][$class_name]) : 0;
                $output .= '<td class="text-right text-center">' . $count_title . '</td>';
                $output .= '<td  class="text-right text-center"><strong>' . $count_item . '</strong></td>';
                $total_y_title += $count_title;
                $total_y_item += $count_item;
                unset($data);
            }
            $output .= '<td  class="text-right text-center">' . $total_y_title . '</td><td class="text-right text-center"><strong>' . $total_y_item . '</strong></td>';
            $output .= '<td><div class="btn-group">';
            $output .= '<a class="btn-sm btn btn-info notAJAX openPopUp" href="' . MWB . 'reporting/customs/pop_procurement_list.php?filter=' . $year_num . '" width="900" height="630" title="' . __('Procurement List') . '"><i class="fa fa-list" aria-hidden="true"></i></a>';
            $output .= '<a class="btn-sm btn btn-primary notAJAX openPopUp" href="' . MWB . 'reporting/pop_chart.php?filter=' . $year . '" width="700" height="530" title="' . __('Procurement Bar Chart') . '"><i class="fa fa-bar-chart" aria-hidden="true"></i></a>';
            $output .= '</div></td></tr>';
        }
    }

    $output .= '</table>';
    // set abbreviation details about "t" and "i" below year column
    $output .= '<div class="d-block mt-2"><strong>' . __('Description') . '</strong><br><b class="text-bold">' . __('t') . '</b> : <label>' . __('Title') . '</label><br><b class="text-bold">' . __('i') . '</b> : <label>' . __('Item') . '</label></div>';

    $chart['xAxis'] = $xAxis;
    $chart['data'] = $dataset;
    $chart['chart_type'] = 'Bar';
    $chart['title'] =  __('Procurement Report Year : ');
    $_SESSION['chart'] = $chart;

    echo $output;

    $content = ob_get_clean();
    // include the page template
    require SB . '/admin/' . $sysconf['admin_template']['dir'] . '/pop_iframe_tpl.php';
}
