<?
error_reporting(E_ALL ^ E_DEPRECATED);
error_reporting(E_ALL ^ E_NOTICE);

require_once "global_env.php";
$logger = Logger::getInstance($_SERVER['PHP_SELF']);

// memcached integrated
require_once "lib/Memcached_pool.php";
$mcache = new Memcached();
$mcache->addServers($GLOBALS['memcached-sets'][DEFAULT_MEMCACHED_SET]);

include_once "lib/sanitize.php";
//ob_start("sanitize_html");

// this will replace by login session
$db_user = $mcache->get(session_id() . MCACHE_SUFFIX);
if (!$db_user) {
  // go to index.php
  header("Location: signin.php");
  exit();
}

// Verify page authority
const pageid = 'menu03.07.03';
if (!isset($db_user['authority'][pageid])) {
  // go to index.php
  header("Location: signout.php");
  exit();
}

require_once "lib/DBFactory.php";
require_once "lib/Department.php";
require_once "lib/ext/DateTimeUtil.php";

$logger->debug(print_r($_REQUEST, true));


// Inquiry datas
$datas = null;
$WHERE = "";

$month_ini = (new DateTime("first day of last month"))->format('Y-m-d');
$month_end = (new DateTime("last day of last month"))->format('Y-m-d');
$WHERE_DATE = " BETWEEN '" . $month_ini . " 00:00:00' AND '" . $month_end . " 23:59:59'";

if (isset($_REQUEST['real_date'])) {
  if ($_REQUEST['real_date'] != '') {
    $month_ini = (new DateTime($_REQUEST['real_date']))->format('Y-m-01');
    $month_end = (new DateTime($_REQUEST['real_date']))->format('Y-m-t');
    $WHERE_DATE = " BETWEEN '" . $month_ini . " 00:00:00' AND '" . $month_end . " 23:59:59'";
  }
}

$datas = (new DBFactory())->query(
  "SELECT id AS item_id, name AS item_name
  , ROUND(CONVERT(float, o.qty)*2/(a.qty+b.qty), 1) AS turnover, unit_price*s.qty AS amount
  FROM item i
  LEFT JOIN
  (
    SELECT item_id, item_dvsn_id, sum(qty) AS qty
    FROM Stock
    GROUP BY item_id, item_dvsn_id
  ) AS s
  ON s.item_id = i.id
  AND s.item_dvsn_id = i.division
  INNER JOIN
  (
  	SELECT item_id, item_dvsn_id, sum(increase) AS qty
  	FROM StockTracking
  	WHERE CONVERT(VARCHAR(10), create_dt, 126) = '" . $month_ini . "'
    AND Section = 'Daily Jobs Tracking'
    GROUP BY item_id, item_dvsn_id
  ) a
  ON a.item_id = i.id
  AND a.item_dvsn_id = i.division
  INNER JOIN
  (
  	SELECT item_id, item_dvsn_id, sum(increase) AS qty
  	FROM StockTracking
  	WHERE CONVERT(VARCHAR(10), create_dt, 126) = '" . $month_end . "'
    AND Section = 'Daily Jobs Tracking'
    GROUP BY item_id, item_dvsn_id
  ) b
  ON b.item_id = i.id
  AND b.item_dvsn_id = i.division
  INNER JOIN
  (
  	SELECT item_id, item_dvsn_id, sum(qty) AS qty
  	FROM Outbound
  	WHERE create_dt " . $WHERE_DATE . "
  	GROUP BY item_id, item_dvsn_id
  ) o
  ON o.item_id = i.id
  AND o.item_dvsn_id = i.division
  WHERE a.qty+b.qty>0"

);


/*
if (isset($_REQUEST['cmd'] && $_REQUEST['cmd'] == 'search')) {

}
*/

?>

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <title>Pegasus System -- Inventory Turnover Worst Report</title>
  <link rel="stylesheet" href="vendor/jquery/css/jquery.dataTables.min.css">
  <link href="vendor/jquery/css/jquery-ui.css" rel="stylesheet" />
  <link rel="stylesheet" href="vendor/fontawesome-free/css/fontawesome.min.css">
  <link rel="stylesheet" href="vendor/fontawesome-free/css/regular.css">
  <link rel="stylesheet" href="vendor/fontawesome-free/css/v4-shims.css">
  <link rel="stylesheet" href="vendor/fontawesome-free/css/solid.css">
  <link rel="stylesheet" href="vendor/jquery/css/buttons.dataTables.min.css">
  <link href="css/main.css" rel="stylesheet" />

  
  <link href="css/google-material-icons.css" rel="stylesheet">
  <link href="css/menu-popup.css" rel="stylesheet" />
  <link href="css/jquery-ui-timepicker-addon.css" rel="stylesheet" />
  <link href="css/custom-drop-down-list.css" rel="stylesheet" />
  <link href="css/ext/jsuites/v4/jsuites.css" rel="stylesheet" />

  
  <script type="text/javascript" src="vendor/jquery/js/jquery.min.js"></script>
  <script type="text/javascript" src="vendor/jquery/js/jquery-ui.js"></script>

  <script src="vendor/jquery/js/jquery.dataTables.min.js"></script>
  <script src="vendor/jquery/js/dataTables.buttons.min.js"></script>
  <script src="vendor/jquery/js/buttons.flash.min.js"></script>
  <script src="vendor/jquery/js/jszip.min.js"></script>
  <script src="vendor/jquery/js/pdfmake.min.js"></script>
  <script src="vendor/jquery/js/vfs_fonts.js"></script>
  <script src="vendor/jquery/js/buttons.html5.min.js"></script>
  <script src="vendor/jquery/js/buttons.print.min.js"></script>
  <script src="vendor/jquery/js/buttons.html5.styles.min.js"></script>
  <script src="vendor/jquery/js/buttons.html5.styles.templates.min.js"></script>
  <script src="vendor/jquery/js/dataTables.select.min.js" type="text/javascript"></script>

  <script src="js/utils.js"></script>
  <script src="js/menu-popup.js"></script>
  <script src="js/dd-icon.js"></script>
  <script src="js/datetime.js"></script>
  <script src="js/jquery-ui-timepicker-addon.js"></script>
  <script src="js/ext/jsuites/v4/jsuites.js"></script>

  <script>
    var qs_=qs();
    var qs_ = <?=json_encode($_REQUEST ?? null)?>;

    function select_all(e) {
      if (!$(e).hasClass('md-inactive')) {
        $(e).addClass('md-inactive').attr('title', 'Deselect all');
        $('#table_preview').DataTable().rows().select();
      } else {
        $(e).removeClass('md-inactive').attr('title', 'Select all');
        $('#table_preview').DataTable().rows().deselect();
      }
    }

    function update_default_value() {
      for(var q in qs_) {
        $('.info').find('.parse_query_str:not(input[type=radio])')
          .filter(function(){return $(this).attr('name')==q}).val(qs_[q]);
      }

      $('.info').find('input[type=radio].parse_query_str').each(function() {
        if (qs_[$(this).attr('name')])
          $(this).prop('checked', qs_[$(this).attr('name')] == $(this).val() ? true : false);
      });

      $('.custom-drop-down-list').find('select.parse_query_str').each(function() {
        var key = $(this).attr('name');
        $(this).parent().find('.select-items div')
          .filter(function(){return $(this).html()==qs_[key]}).click();
      });

    }

    $(document).ready(function() {

      var today = {
        year: new Date().getFullYear(),
        month: (new Date().getMonth()+1).pad(2),
        smonth: MonthArry[new Date().getMonth()],
        date: new Date().getDate().pad(2)
      };

      $.fn.dataTable.ext.search.push(
        function(settings, data, dataIndex) {
          return $(table.row(dataIndex).node()).hasClass('hidden');
        }
      );

      var dtable = $('#table_preview').DataTable( {
        dom: 'B<"clear">lfrtip',
        buttons: [
            {
              extend: 'excelHtml5',
              text: 'Excel Output',
              title: function () { return $('#pdf-title').val(); },
              filename:  today.year + '-' + today.month + '-' + today.date + ' - Inventory_turnover_worst_report',
              exportOptions: {
                  columns: [1,2,3,4]
              }
            },
            {
              extend: 'pdfHtml5',
              //orientation: 'landscape',
              pageSize: 'LEGAL',
              text: 'PDF Output',
              title: function () { return $('#pdf-title').val(); },
              filename:  today.year + '-' + today.month + '-' + today.date + ' - Inventory_turnover_worst_report',
              customize: function(doc) {
                doc['header'] = (function(page, pages) {
                  return {
                    columns: [
                      {
                        alignment: 'left',
                        text: [
                          'Date : ',
                          today.date,
      					          '-',
                          today.smonth,
      					          '-',
                          today.year,
      				          ]
                      },
                      {
                        alignment: 'right',
                        text: [
                          'page ',
                          { text: page.toString(), italics: true },
      					          ' / ',
                          { text: pages.toString(), italics: true }
      				          ]
                      },
                    ],
                    margin: [15, 15]
                  }
                });
              },
              exportOptions: {
                  columns: [1,2,3,4]
              }
            }
        ],
        select: {
            style:    'multi',
            selector: 'tr:not(.no-select) td'
        },
        'pagingType': 'full_numbers',
        'searching': false,
        "order": [[ 3, "asc" ]],
        /* no any columns unsortable*/
        'columnDefs': [ {
          'targets': [0],
          'orderable': false,
        }],
        "pageLength": 25,

      });

      $('input[id^=tbDate]').datetimepicker({
        dateFormat: 'yy-mm-dd'
      });

      $('#btn_clear').on('click', function() {
        $('#frm-primary').trigger('reset');
        $('#ddl_supplier_from, #ddl_supplier_to, #ddl_item_from, #ddl_item_to').each(function() {
          $(this).parent().find('.same-as-selected').removeClass('same-as-selected');
          $(this).parent().find('.select-selected')
            .html($(this).children("option:selected").text());
        });
      });

      $('#btn_search').on('click', function(evt) {
        evt.preventDefault();

        //
        // $('.enable_on').each(function() {
        //   console.log($(this)[0], $(this).html(), $(this).val(), $(this).is(':checked'));
        // });
        // console.log($('.enable_on:checked').val()); return;

        var params = {
          cmd : 'search',
        };

        params = removeUndefined(params);
        /*console.log(params); return false;*/
        for (var key in params) {
          $('<input>').attr({ type: 'hidden', name: key, value: params[key] }).appendTo('#frm-primary');
        }

        $('#frm-primary').submit();

      });

      var _3y_early = new Date().getFullYear()-3;
      $('input.calendar')
      .each(function() {
        jSuites.calendar($(this)[0], {
          type: 'year-month-picker',
          format: 'Mon-YYYY',
          validRange: [ _3y_early + '-01-01', today.year + '-' + today.month + '-31' ],
          onchange: function(el, newValue, oldValue) {
            console.log(el, newValue, oldValue);
              var month_year = new DateFormatter('%N %y').format(new Date(newValue));
              $('#pdf-title').val(month_year + ' Monthly Report for inventory turnover worst');
              $('#real_date').val(new DateFormatter('%y-%m-%d').format(new Date(newValue)));
          }
        });
        $(this).val('last month');
      });

      update_default_value();

      var dstr = $('#txt_month').val();
      var a_ = [1,2,3,4,5,6,7,8,9,10,11,12].mergeWithPairedKey(MonthArry);
      if (dstr == 'last month') dstr = getFirstDayOfLastMonth(1);
      else {
        var tmp = dstr.split('-');
        dstr = tmp[1] + '-' + a_[tmp[0]] + '-01';
      }
      var last_month_year = new DateFormatter('%N %y').format(new Date(dstr));
      $('#pdf-title').val(last_month_year + ' Monthly Report for inventory turnover worst');

    });
  </script>

  <style>
  @import url('https://fonts.googleapis.com/css?family=Josefin+Sans&display=swap');
  *{
    margin: 0;
    padding: 0;
    box-sizing: border-box;
    list-style: none;
    text-decoration: none;
    font-family: 'Josefin Sans', sans-serif;
  }
  input.calendar {
    outline: none;
    border: solid 1px #333;
  }
  </style>
</head>
<body>
  <div class="main_content">
    <div class="header" style="text-align: center; background: #0070c0; color: white;"><h2> Inventory Turnover Worst Report </h2><p style="text-align:right; font-size: 10px;">Version 1.0</p></div>
      <div class="info" >
        <form method="POST" id="frm-primary">
          <input type="hidden" id="pdf-title">
          <div>
            <input type="hidden" name="real_date" id="real_date" value="last month" class="parse_query_str" >
            Month: <input type="text" name="txt_month" id="txt_month" class="xxlwidth calendar parse_query_str"><br/>
          </div>
          <div class="float-right">
            <input id="btn_search" type="submit" value="Search">
            <input id="btn_clear" type="button" value="Clear">
          </div>
        </form>
     </div>

<!--
  +++++ Table preview section +++++
 -->
      <div class="display" id="display_div">
        <table id="table_preview" class="display dataTable">
          <thead>
            <tr>
              <th><i class="data-editor dd-icon fas fa-black fa-check-double" for="select_all" title="select all"></i></th>
              <th>Parts No</th>
              <th>Parts Name</th>
              <th>Inventory Turnover</th>
              <th>Amount Price</th>
            </tr>
          </thead>
          <tbody>
<?

            $i = 0;
            $display_cols = array(
              'id'=>0,
              'item_id'=>0,
              'item_name'=>0,
              'turnover'=>0,
              'amount'=>0,
            );
            foreach ($datas as $index => $row) {
              echo '<tr>';
              foreach ($display_cols as $key => $val) {
                if ($key == 'id') {
                  echo '<td>' . ++$i
                    . '</td>';
                } else if ($key == 'amount') {
                  echo '<td><span id="' . $key . '_' . $row['id'] . '" map="' . $key . '_' . $row['id'] . '" class="data-display">' . number_format($row[$key], 2) . '</span>'
                    . '</td>';
                } else {
                  echo '<td><span id="' . $key . '_' . $row['id'] . '" map="' . $key . '_' . $row['id'] . '" class="data-display">' . $row[$key] . '</span>'
                    . '</td>';
                }
              }
              echo '</tr>';
            }
?>

      </tbody>
      <tfoot>
        <tr>
            <th>#</th>
            <th>Parts No</th>
            <th>Parts Name</th>
            <th>Inventory Turnover</th>
            <th>Amount Price</th>
        </tr>
      </tfoot>
    </table>
    </div>
  </div>
</body>

<script src="js/custom-drop-down-list.js"></script>
</html>
