<?
//error_reporting(E_ALL ^ E_DEPRECATED);
//error_reporting(E_ALL ^ E_NOTICE);
error_reporting(0);
DEFINE('__NO_AUTHENTICATE_REQUIRED__', '');

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 = 'menu01.02';
if (!isset($db_user['authority'][pageid])) {
  // go to index.php
  header("Location: signout.php");
  exit();
}

require_once "lib/Inbound.php";
//require_once "lib/Supplier.php";
require_once "lib/Customer.php";
require_once "lib/Item.php";
require_once "lib/PackageType.php";
require_once "lib/Location.php";
require_once "lib/DBFactory.php";
require_once "lib/ext/DateTimeUtil.php";


// Inquiry datas
$inbounds = null;
//$suppliers = null;
$customers = null;
$items = null;
$WHERE = "1=1";

if (isset($_GET['date_from']) && !empty($_GET['date_from']) && isset($_GET['date_to']) && !empty($_GET['date_to'])) {
  $df = convertTz($_GET['date_from'], $db_user['timezone']['time_zone'], 'Asia/Bangkok');
  $dt = convertTz($_GET['date_to'], $db_user['timezone']['time_zone'], 'Asia/Bangkok');
  $WHERE .= " AND ib.create_dt BETWEEN '" . $df . "' AND '" . $dt . "'";
} else if (isset($_GET['date_from']) && !empty($_GET['date_from'])) {
  $df = convertTz($_GET['date_from'], $db_user['timezone']['time_zone'], 'Asia/Bangkok');
  $WHERE .= " AND ib.create_dt > '" . $df . "'";
} else if (isset($_GET['date_to']) && !empty($_GET['date_to'])) {
  $dt = convertTz($_GET['date_to'], $db_user['timezone']['time_zone'], 'Asia/Bangkok');
  $WHERE .= " AND ib.create_dt < '" . $dt . "'";
}


if (isset($_GET['customer'])) {
  // example input 00000001-001,00000001-002,00000001-003,00000001-004
  $WHERE .= " AND ib.customer_id IN (" . Util::encapsulateWithQuote($_GET['customer']) . ")";
}

// if (isset($_GET['supplier'])) {
//   // example input 00000001-001,00000001-002,00000001-003,00000001-004
//   $WHERE .= " AND ib.customer_id IN (" . Util::encapsulateWithQuote($_GET['supplier']) . ")";
// }

if (isset($_GET['item'])) {
  // example input 00000001-001,00000001-002,00000001-003,00000001-004
  $WHERE .= " AND ib.item_id IN (" . Util::encapsulateWithQuote($_GET['item']) . ")";
}



//$inbounds = (new Inbound())->get(Inbound::$__COLUMNS, $WHERE);
$inbounds = (new DBFactory())->query(
  '  SELECT ib.*, u.name AS user_name, it.unit_price, it.unit_price*ib.qty AS amount
    FROM Inbound ib
    INNER JOIN
    (
      SELECT a.id, a.division, b.name FROM
      (
        SELECT id, max(division) AS division FROM Users WHERE 1=1' . WHERE_PERIOD . ' GROUP BY id
      ) a INNER JOIN (
        SELECT id,division,name FROM Users
      ) b
      ON a.id=b.id AND a.division=b.division
    ) u
    ON u.id = ib.create_user_id
    INNER JOIN Item it
    ON it.id = ib.item_id
    AND it.division = ib.item_dvsn_id
    WHERE ' . $WHERE. ' ORDER BY ib.update_dt DESC'
);
// create paring object from fetch rows
$items = (new DBFactory())->query(
  'SELECT a.id, a.division, b.name FROM
  (
    SELECT id, max(division) AS division FROM Item WHERE 1=1' . WHERE_PERIOD . ' GROUP BY id
  ) a INNER JOIN (
    SELECT id,division,name FROM Item
  ) b
  ON a.id=b.id AND a.division=b.division');
$items_pair = Item::DbRows2ArrayMapWithDivisions((new Item())->getAll(),'id','name','division');
// $suppliers = (new DBFactory())->query(
//   'SELECT a.id, a.division, b.name FROM
//   (
//     SELECT id, max(division) AS division FROM Supplier WHERE 1=1' . WHERE_PERIOD . ' GROUP BY id
//   ) a INNER JOIN (
//     SELECT id,division,name FROM Supplier
//   ) b
//   ON a.id=b.id AND a.division=b.division');
// $suppliers_pair = Supplier::DbRows2ArrayMapWithDivisions((new Supplier())->getAll(),'id','name','division');
$customers = (new DBFactory())->query(
  'SELECT a.id, a.division, b.name FROM
  (
    SELECT id, max(division) AS division FROM Customer WHERE 1=1' . WHERE_PERIOD . ' GROUP BY id
  ) a INNER JOIN (
    SELECT id,division,name FROM Customer
  ) b
  ON a.id=b.id AND a.division=b.division');
$locations = (new DBFactory())->query(
  'SELECT a.id, a.division, b.name FROM
  (
    SELECT id, max(division) AS division FROM Location WHERE 1=1' . WHERE_PERIOD . ' GROUP BY id
  ) a INNER JOIN (
    SELECT id,division,name FROM Location
  ) b
  ON a.id=b.id AND a.division=b.division');


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

// }


const _MAX_NEW_ENTRY = 10;
?>

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <title>Pegasus System -- Inbound Inquiry 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" />

  
  <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/jquery-ui-timepicker-addon.js"></script>

  <script>
    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() {
      var qs_=qs();
      for(var q in qs_) {
        $('.info').find('.parse_query_str')
          .filter(function(){return $(this).attr('name')==q}).val(qs_[q]);
      }

      $('.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() {

      update_default_value();

      var today = {
        year: new Date().getFullYear(),
        month: (new Date().getMonth()+1).pad(2),
        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',
              filename:  today.year + '-' + today.month + '-' + today.date + ' - Inbound_inquiry_report',
              exportOptions: {
                  columns: [1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17]
              }
            },
            {
              extend: 'pdfHtml5',
              orientation: 'landscape',
              pageSize: 'LEGAL',
              text: 'PDF Output',
              filename:  today.year + '-' + today.month + '-' + today.date + ' - Inbound_inquiry_report',
              customize: function(doc) {
                doc['header'] = (function(page, pages) {
                  return {
                    columns: [
                      {
                        alignment: 'right',
                        text: [
                          'page ',
                          { text: page.toString(), italics: true },
      					          ' / ',
                          { text: pages.toString(), italics: true }
      				          ]
                    }],
                    margin: [10, 10]
                  }
                });
              },
              exportOptions: {
                  columns: [1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17]
              }
            }
        ],
        select: {
            style:    'multi',
            selector: 'tr:not(.no-select) td'
        },
        'pagingType': 'full_numbers',
        'searching': false,
        "order": [[ 15, "desc" ]],
        /* no any columns unsortable*/
        'columnDefs': [ {
          'targets': [0],
          'orderable': false,
        }],
        "pageLength": <?=isset($_GET['length']) ? $_GET['length'] : 25?>,

      });

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

      $('#btn_clear').on('click', function() {
        $('#frm-primary').trigger('reset');
        $('#ddl_customer_from, #ddl_customer_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();

        customer_ = '';
        var keep = false;
        $('#ddl_customer_from > option').each(function() {
          if ($(this).val() == $('#ddl_customer_from').children("option:selected").val()) {
            keep = true;
          }
          if (keep) {
            customer_ += $(this).val() + ",";
          }
          if ($(this).val() == $('#ddl_customer_to').children("option:selected").val()) {
            keep = false;
            return;
          }
        });

        item_ = '';
        var keep = false;
        $('#ddl_item_from > option').each(function() {
          if ($(this).val() == $('#ddl_item_from').children("option:selected").val()) {
            keep = true;
          }
          if (keep) {
            item_ += $(this).val() + ",";
          }
          if ($(this).val() == $('#ddl_item_to').children("option:selected").val()) {
            keep = false;
            return;
          }
        });

      

        var params = {
          cmd : 'search',
          date_from: $('#tbDate1').val(),
          date_to: $('#tbDate2').val(),
          customer : customer_.slice(0, -1),
          item : item_.slice(0, -1),
          length : $('#table_preview_length').find('select').children("option:selected").val(),
        };

      
        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');
        }
        //console.log(params); return false;
        $('#frm-primary').submit();

      });
    });
  </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;
  }
  </style>
</head>
<body>
  <div class="main_content">
    <div class="header" style="text-align: center; background: #0070c0; color: white;"><h2> Inbound Inquiry Report </h2><p style="text-align:right; font-size: 10px;">Version 1.0</p></div>
      <div class="info" >
        <form method="GET" id="frm-primary">
          <!-- <div class="float-right"> -->
            <!-- Supplier : -->
            <!-- <div class="custom-drop-down-list"> -->
<?
              // print Util::convert2DropDownList('customer_from',$suppliers,'id','id',null,null,$suppliers[0]['id'],null,'parse_query_str');
?>
            <!-- </div> -->
            <!-- ~ -->
            <!-- <div class="custom-drop-down-list"> -->
<?
              // print Util::convert2DropDownList('customer_to',$suppliers,'id','id',null,null,$suppliers[count($suppliers)-1]['id'],null,'parse_query_str');
?>
            <!-- </div> -->
          <!-- </div> -->

          <div class="float-right">
            Customer :
            <div class="custom-drop-down-list">
<?
              print Util::convert2DropDownList('customer_from',$customers,'id','id',null,null,$customers[0]['id'],null,'parse_query_str');
?>
            </div>
            ~
            <div class="custom-drop-down-list">
<?
              print Util::convert2DropDownList('customer_to',$customers,'id','id',null,null,$customers[count($customers)-1]['id'],null,'parse_query_str');
?>
            </div>
          </div>
          <div>
            Inbound date :
            <input type="text" id="tbDate1" name="date_from" placeholder="select a date" class="parse_query_str xlwidth tbDate" autocomplete="off" />
            ~
            <input type="text" id="tbDate2" name="date_to" placeholder="select a date" class="parse_query_str xlwidth tbDate" autocomplete="off" />
          </div>
          <div class="float-right">
            <input id="btn_search" type="submit" value="Search">
            <input id="btn_clear" type="button" value="Clear">
          </div>
          <div>
            Item code :
            <div class="custom-drop-down-list">
<?
              print Util::convert2DropDownList('item_from',$items,'id','id',null,null,$items[0]['id'],null,'parse_query_str');

?>
            </div>
            ~
            <div class="custom-drop-down-list">
<?
              print Util::convert2DropDownList('item_to',$items,'id','id',null,null,$items[count($items)-1]['id'],null,'parse_query_str');
?>
            </div>
          </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>Inbound date</th>
              <th>Item code</th>
              <th>Item name</th>
              <th>Product date</th>
              <th>Customer code</th>
              <th>Package</th>
              <th>Package quantity</th>
              <th>Quantity</th>
              <th>Location</th>
              <th>Status</th>
              <th>Slip divide</th>
              <th>HT type</th>
              <th>User name</th>
              <th>User code</th>
              <th>Updated date</th>
              <th>Unit price</th>
              <th>Amount</th>
            </tr>
          </thead>
          <tbody>
<?
            $packageTypes = (new PackageType())->get(PackageType::$__COLUMNS, 'item_id=\'*\'');
            $packageTypes_pair = PackageType::DbRows2ArrayMap($packageTypes ,'id','package_id');
            $inboundStatus = ItemStatus::getArrayConstants('val', 'id');
            $slipDivide = SlipDivide::getArrayConstants('val', 'id');
            $acttypeib = ActTypeIb::getArrayConstants('val', 'id');

            $i = 0;
            $display_cols = array(
              'id'=>0,
              'create_dt'=>0,
              'item_id'=>0,
              'product_date'=>0,
              'customer_id'=>0,
              'package_type'=>0,
              'package_qty'=>0,
              'qty'=>0,
              'location_id'=>0,
              'status'=>0,
              'slip_divide'=>0,
              'act_type'=>0,
              'user_name'=>0,
              'create_user_id'=>0,
              'update_dt'=>0,
              'unit_price'=>0,
              'amount'=>0,
            );
            foreach ($inbounds as $index => $row) {
              echo '<tr>';
              foreach ($display_cols as $key => $val) {
                if ($row[$key] instanceof DateTime) {
                  if ($key == 'create_dt') {
                    $tmp = "obdate";
                    $format = "Y-m-d H:i:s";
                    echo '<td><span id="' . $key . '" map="tbDate_edit_' . $row['id'] . '" class="data-display">'
                      . convertTz($row[$key]->format($format), 'Asia/Bangkok', $db_user['timezone']['time_zone']) . "</span>"
                      . '</td>';
                  } else {
                    $tmp = "product";
                    $format = "Y-m-d";
                    echo '<td><span id="' . $key . '" map="tbDate_edit_' . $row['id'] . '" class="data-display">'
                      . $row[$key]->format($format) . "</span>"
                      . '</td>';
                  }
                } else if ($key == 'item_id') {
                  echo '<td><span id="' . $key . '" map="ddl_item_code" class="data-display">' . $row[$key]  . "</span>"
                    . '</td>';
                  echo '<td><span class="data-display" map="item_name">' . $items_pair[$row[$key]][$row['item_dvsn_id']] . '</span></td>';
                } else if ($key == 'customer_id') {
                  echo '<td><span id="' . $key . '" map="ddl_customer_code" class="data-display">' . $row[$key] . "</span>"
                    . '</td>';
                } else if ($key == 'location_id') {
                  echo '<td><span id="' . $key . '" map="ddl_location" class="data-display">' . $row[$key] . "</span>"
                    . '</td>';
                } else if ($key == 'package_type') {
                  echo '<td><span id="' . $key . '" map="ddl_package_type" class="data-display">' . $packageTypes_pair[$row[$key]] . "</span>"
                    . '</td>';
                } else if ($key == 'status') {
                  echo '<td><span id="' . $key . '" map="ddl_inbound_status" class="data-display">' . ItemStatus::getConstant($row[$key])->toString() . "</span>"
                    . '</td>';
                } else if ($key == 'slip_divide') {
                  echo '<td><span id="' . $key . '" map="ddl_slip_divide" class="data-display">' . SlipDivide::getConstant($row[$key])->toString() . "</span>"
                    . '</td>';
                } else if ($key == 'act_type') {
                    echo '<td><span id="' . $key . '" map="ddl_slip_divide" class="data-display">' . ActTypeIb::getConstant($row[$key])->toString() . "</span>"
                      . '</td>';
                }  else if ($key == 'id') {
                  echo '<td>' . ++$i
                    . '</td>';
                } else if ($key == 'unit_price' || $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>Inbound date</th>
            <th>Item code</th>
            <th>Item name</th>
            <th>Product date</th>
            <th>Customer code</th>
            <th>Package</th>
            <th>Package quantity</th>
            <th>Quantity</th>
            <th>Location</th>
            <th>Status</th>
            <th>Slip divide</th>
            <th>HT type</th>
            <th>User name</th>
            <th>User code</th>
            <th>Updated date</th>
            <th>Unit price</th>
            <th>Amount</th>
        </tr>
      </tfoot>
    </table>
    </div>
  </div>
</body>

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