<?php //error_reporting(0); ?>
<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8">
  <meta http-equiv="X-UA-Compatible" content="IE=edge">
  <title>EHONGMD | สรุปรายงานทะเบียน</title>
  <link rel="shortcut icon" href="dist/img/logo_1.png" />
  <?php
    include '_plugin.php';
    include '_connect.php';
  ?>
</head>
<body class="hold-transition skin-red sidebar-mini">
<div class="wrapper">
<?php
include '_header.php';
include '_menu.php';
?>
  <div class="content-wrapper">
    <section class="content">
      <div class="row">
      <!-- ค้นหา -->
      <!-- ตาราง -->
      <script src="bower_components/jquery/dist/jquery.min.js"></script>
      <?php
      $thai_month_arr=array(
          "0"=>"",
          "01"=>"มกราคม",
          "02"=>"กุมภาพันธ์",
          "03"=>"มีนาคม",
          "04"=>"เมษายน",
          "05"=>"พฤษภาคม",
          "06"=>"มิถุนายน",
          "07"=>"กรกฎาคม",
          "08"=>"สิงหาคม",
          "09"=>"กันยายน",
          "10"=>"ตุลาคม",
          "11"=>"พฤศจิกายน",
          "12"=>"ธันวาคม"
      );
      if (isset($_POST['submit_1']) || isset($_POST['submit_2']) || isset($_POST['submit_3'])) {
        $date_1 = $_POST['date_1'];
        $date_2 = $_POST['date_2'];
        $date_2_1 = $date_2-543;
        $br_id = $_POST['br_id'];
        $zone_codex = $_POST['zone_code'];
        $zstaff_idx = $_POST['zstaff_id'];
        $province = $_POST['province'];
        $brand = $_POST['brand'];
      }
      else {
        date_default_timezone_set('Asia/Bangkok');
        $date_1 = date('m');
        $date_2 = date('Y')+543;
        $date_2_1 = $date_2-543;
      }
      // echo $status;
      ?>
      <div class="col-md-12">
        <div class="box box-success">
          <div class="box-header with-border">
            <h3 class="box-title">EHONGMD | สรุปรายงานทะเบียน</h3>
          </div>
          <!-- target="_blank" lettersum.php-->
          <form role="form" action="" method="POST" enctype="multipart/form-data" >
            <div class="box-body">
              <div class="row">
                <div class="col-xs-2">
                  <p>เดือน</p>
                  <select name="date_1" class="form-control">
                    <option value="<?php echo $date_1; ?>"><?php echo $thai_month_arr[$date_1]; ?></option>
                    <option value="01">มกราคม</option>
                    <option value="02">กุมภาพันธ์</option>
                    <option value="03">มีนาคม</option>
                    <option value="04">เมษายน</option>
                    <option value="05">พฤษภาคม</option>
                    <option value="06">มิถุนายน</option>
                    <option value="07">กรกฎาคม</option>
                    <option value="08">สิงหาคม</option>
                    <option value="09">กันยายน</option>
                    <option value="10">ตุลาคม</option>
                    <option value="11">พฤศจิกายน</option>
                    <option value="12">ธันวาคม</option>
                  </select>
                </div>
                <div class="col-xs-2">
                  <p>ปี</p>
                  <input type="number" class="form-control" placeholder="" name="date_2" value="<?php echo $date_2; ?>">
                </div>
                <div class="col-xs-2">
                  <p>สาขา</p>
                  <select name="br_id" id="" class="form-control">
                    <?php
                      $sql = "SELECT * FROM EHONGDB.dbo.BRANCH WHERE BR_ID NOT IN ('%') AND BR_ID = '$br_id'";
                      $select=$conn->prepare($sql);
                      $select->execute();
                      while($fetch=$select->fetch()){
                    ?>
                      <option value="<?php echo $fetch['BR_ID']; ?>"><?php echo $fetch['BR_ID']." : ".$fetch['BR_NAME']; ?></option>
                    <?php } ?>
                    <option value="">ทั้งหมด</option>
                    <?php
                      $sql = "SELECT * FROM EHONGDB.dbo.BRANCH WHERE BR_ID NOT IN ('%')";
                      $select=$conn->prepare($sql);
                      $select->execute();
                      while($fetch=$select->fetch()) {
                    ?>
                      <option value="<?php echo $fetch['BR_ID']; ?>"><?php echo $fetch['BR_ID']." : ".$fetch['BR_NAME']; ?></option>
                    <?php } ?>
                   </select>
                 </div>
                 <div class="col-xs-2">
                   <p>เขต</p>
                   <select class="form-control" name="zone_code" placeholder="">
                     <?php
                     $sql = "SELECT * FROM EHONGDB.dbo.branch_salezone WHERE szone_code = '$zone_codex' ORDER BY szone_code";
                     $select=$conn->prepare($sql);
                     $select->execute();
                     while($fetch=$select->fetch()) {
                     ?>
                     <option value="<?php echo $fetch['szone_code'];?>"><?php echo $fetch['szone_code']." : ".$fetch['staff_nick'];?></option>
                     <?php } ?>
                     <option value="">รวมทุกเขต</option>
                     <?php
                     $sql = "SELECT * FROM EHONGDB.dbo.branch_salezone ORDER BY szone_code";
                     $select=$conn->prepare($sql);
                     $select->execute();
                     while($fetch=$select->fetch()) {
                     ?>
                     <option value="<?php echo $fetch['szone_code'];?>"><?php echo $fetch['szone_code']." : ".$fetch['staff_nick'];?></option>
                     <?php } ?>
                   </select>
                 </div>
                 <div class="col-xs-2">
                   <p>ภาค</p>
                   <select class="form-control" name="zstaff_id" placeholder="">
                     <?php
                     $sql = "SELECT DISTINCT zstaff_id,zstaff_name FROM EHONGDB.dbo.branch_salezone WHERE zstaff_id = '$zstaff_idx' ORDER BY zstaff_id,zstaff_name";
                     $select=$conn->prepare($sql);
                     $select->execute();
                     while($fetch=$select->fetch()) {
                     ?>
                     <option value="<?php echo $fetch['zstaff_id'];?>"><?php echo $fetch['zstaff_id']." : ".$fetch['zstaff_name'];?></option>
                     <?php } ?>
                     <option value="">รวมทุกภาค</option>
                     <?php
                     $sql = "SELECT DISTINCT zstaff_id,zstaff_name FROM EHONGDB.dbo.branch_salezone ORDER BY zstaff_id,zstaff_name";
                     $select=$conn->prepare($sql);
                     $select->execute();
                     while($fetch=$select->fetch()) {
                     ?>
                     <option value="<?php echo $fetch['zstaff_id'];?>"><?php echo $fetch['zstaff_id']." : ".$fetch['zstaff_name'];?></option>
                     <?php } ?>
                   </select>
                 </div>
                 <div class="col-xs-2">
                   <p>จังหวัด</p>
                   <select class="form-control" name="province" placeholder="">
                     <?php
                     $sql = "SELECT a.PR_CODE ,b.PR_NAME from EHONGDB.dbo.branch a
                             LEFT OUTER JOIN (SELECT PR_CODE,PR_NAME FROM [EHONGDB].[dbo].[PROV]) b on a.PR_CODE=b.PR_CODE
                             WHERE a.PR_CODE is not null and a.PR_CODE = '$province'
                             group by a.PR_CODE,b.PR_NAME";
                     $select=$conn->prepare($sql);
                     $select->execute();
                     while($fetch=$select->fetch()) {
                     ?>
                     <option value="<?php echo $fetch['PR_CODE'];?>"><?php echo $fetch['PR_CODE']." : ".$fetch['PR_NAME'];?></option>
                     <?php } ?>
                     <option value="">ทุกจังหวัด</option>
                     <?php
                     $sql = "SELECT a.PR_CODE ,b.PR_NAME from EHONGDB.dbo.branch a
                             LEFT OUTER JOIN (SELECT PR_CODE,PR_NAME FROM [EHONGDB].[dbo].[PROV]) b on a.PR_CODE=b.PR_CODE
                             WHERE a.PR_CODE is not null
                             group by a.PR_CODE,b.PR_NAME";
                     $select=$conn->prepare($sql);
                     $select->execute();
                     while($fetch=$select->fetch()) {
                     ?>
                     <option value="<?php echo $fetch['PR_CODE'];?>"><?php echo $fetch['PR_CODE']." : ".$fetch['PR_NAME'];?></option>
                     <?php } ?>
                   </select>
                   <br>
                 </div>
                 <div class="col-xs-2">
                   <p>ยี่ห้อ</p>
                   <select class="form-control" name="brand" placeholder="">
                     <?php
                     $sql = "SELECT brand_no,brand_name from EHONGDB.dbo.product_basebrand
                             where serie_no in ('002') and brand_name = '$brand'
                             group by brand_no,brand_name
                             order by brand_name";
                     $select=$conn->prepare($sql);
                     $select->execute();
                     while($fetch=$select->fetch()) {
                     ?>
                     <option value="<?php echo $fetch['brand_name'];?>"><?php echo $fetch['brand_name'];?></option>
                     <?php } ?>
                     <option value="">ทั้งหมด</option>
                     <?php
                     $sql = "SELECT brand_no,brand_name from EHONGDB.dbo.product_basebrand
                             where serie_no in ('002')
                             group by brand_no,brand_name
                             order by brand_name";
                     $select=$conn->prepare($sql);
                     $select->execute();
                     while($fetch=$select->fetch()) {
                     ?>
                     <option value="<?php echo $fetch['brand_name'];?>"><?php echo $fetch['brand_name'];?></option>
                     <?php } ?>
                   </select>
                 </div>
                 <div class="col-xs-2">
                   <p>สภาพรถ</p>
                   <select class="form-control" name="bike_condition">
                     <option value="%">ทั้งหมด</option>
                     <option value="0" <?php echo (isset($_REQUEST['bike_condition'])?($_REQUEST['bike_condition']=='0'?'selected':''):''); ?>>รถใหม่</option>
                     <option value="2" <?php echo (isset($_REQUEST['bike_condition'])?($_REQUEST['bike_condition']=='2'?'selected':''):''); ?>>รถเก่า</option>
                   </select>
                 </div>
                 <div class="col-xs-2">
                   <p>อิงตาม</p>
                   <select class="form-control" name="refdata">
                     <option value="sale">ยอดขาย</option>
                     <option value="bill" <?php echo (isset($_REQUEST['refdata'])?($_REQUEST['refdata']=='bill'?'selected':''):''); ?>>ใบสั่งทำ</option>
                   </select>
                 </div>
              </div>
            </div>
            <div class="box-footer">
              <input type="submit" name="submit_1" id="" class="btn btn-success" value="รายงาน สาขา">
              <input type="submit" name="submit_2" id="" class="btn btn-success" value="รายงาน เขต">
              <input type="submit" name="submit_3" id="" class="btn btn-success" value="รายงาน ภาค">
            </div>
          </form>

        </div>
      </div>

      <?php
      if (isset($_POST['submit_1']) || isset($_POST['submit_2']) || isset($_POST['submit_3'])) {
        if (isset($_POST['submit_1']) != "") {
          $namesll = "สาขา";
          $sll = "a.BR_ID,a.BR_NAME";
          $SLnumber1 = "ax.BR_ID as NUMBERS";
          $SLnumber2 = "ax.BR_ID";
        }
        if (isset($_POST['submit_2']) != "") {
          $namesll = "เขต";
          $sll = "b.szone_code,b.szone_name";
          $SLnumber1 = "bx.szone_code as NUMBERS";
          $SLnumber2 = "bx.szone_code";
        }
        if (isset($_POST['submit_3']) != "") {
          $namesll = "ภาค";
          $sll = "b.zstaff_id,b.zstaff_name";
          $SLnumber1 = "bx.zstaff_id as NUMBERS";
          $SLnumber2 = "bx.zstaff_id";
        }

        if ($br_id != "") {
          $BR_ID_AND = "AND a.BR_ID = '$br_id'";
          $bid = "amd a.BR_ID = '$br_id'";
        } else {
          $BR_ID_AND = "";
          $bid = "";
        }
        if ($zone_codex != "") {
          $szone_code_AND = "AND b.szone_code = '$zone_codex'";
          $szc = "and bx.szone_code = '$zone_codex' ";
        } else {
          $szone_code_AND = "";
          $szc = "";
        }
        if ($zstaff_idx != "") {
          $zstaff_id_AND = "AND b.zstaff_id = '$zstaff_idx'";
          $zid = "and bx.zstaff_id = '$zstaff_idx' ";
        } else {
          $zstaff_id_AND = "";
          $zid = "";
        }

        include '_reg_report_2.php';
      ?>
      <div class="col-md-12">
        <div class="box box-success">
          <div class="box-header">
            <h3 class="box-title"><p class="text-success">รายงานทะเบียนตรวจสอบ <?php echo $namesll; ?></p></h3>
            <p class="text-red">เดือน <?php echo $thai_month_arr[$date_1]." ".$date_2; ?></p>
          </div>
          <div class="box-body">
            <div class="form-group">
              <!-- example31 -->
              <div style="overflow-x:auto;">
                <table id="example" class="table table-striped table-bordered" style="width:100%;">
                  <thead style="white-space: nowrap">
                    <tr>
                      <!-- <th style="background-color:black; color:#fff; text-align:center;">NO.</th> -->
                      <th rowspan="" style="background-color:black; color:#fff; text-align:center;"><?php echo $namesll; ?></th>
                      <?php if($_REQUEST['refdata']!="bill"){ ?>
                        <th style="background-color:green; color:#fff; text-align:center;">ยอดขาย</th>
                      <?php } ?>
                      <th style="background-color:; color:#000; text-align:center;">ใบสั่งทำ</th>
                      <th style="background-color:; color:#000; text-align:center;">เบิกไปทำใบสั่งในเดือน</th>
                      <th style="background-color:; color:#000; text-align:center;">เบิกไปทำในเดือนอื่น</th>
                      <th style="background-color:; color:#000; text-align:center;">รวมงานเบิก</th>
                      <th style="background-color:red; color:#fff; text-align:center;">ค้างเบิกในเดือน</th>
                      <th style="background-color:blue; color:#fff; text-align:center;">รับงานขนส่งทั้งหมด</th>
                      <th style="background-color:blue; color:#fff; text-align:center;">รับงานขนส่งในเดือน</th>
                      <th style="background-color:blue; color:#fff; text-align:center;">ส่งงานสาขา</th>
                      <th style="background-color:blue; color:#fff; text-align:center;">สาขารับงาน</th>
                      <th style="background-color:blue; color:#fff; text-align:center;">ลูกค้ารับงาน</th>
                    </tr>
                  </thead>
                  <tbody style="white-space: nowrap">
                    <?php
                    $sql = "SELECT
                    $sll
                    FROM [EHONGDB].[dbo].[BRANCH] a
                    left outer join(SELECT * FROM [EHONGDB].[dbo].[branch_salezone]) b on (a.SZONE_CODE=b.szone_code)
                    WHERE a.BR_ID != '%'
                    $BR_ID_AND $szone_code_AND $zstaff_id_AND
                    GROUP BY $sll
                    ORDER BY $sll";
                    // echo $sql;
                    $select=$conn->prepare($sql);
                    $select->execute();
                    $item = 1;
                    $sum_sale = 0;
                    $sum_reg = 0;
                    $sum_berg_in = 0;
                    $sum_berg_out = 0;
                    $sum_berg = 0;
                    $sum_reg_berg = 0;
                    $sum_FNDATE = 0;
                    $sum_FNDATE_IN = 0;
                    $sum_SENTBRDATE = 0;
                    $sum_BRACPDATE = 0;
                    $sum_CUSTACPDATE = 0;
                    while($fetch=$select->fetch()){
                    ?>
                    <tr>
                      <!-- <td style="background-color:; color:; text-align:;"><?php echo number_format($item); ?>.</td> -->
                      <?php
                      if (isset($_POST['submit_1']) != "") {
                        $nwe_sale = isset($sale[$fetch['BR_ID']]) ? $sale[$fetch['BR_ID']] : "0";
                        $new_reg = isset($reg[$fetch['BR_ID']]) ? $reg[$fetch['BR_ID']] : "0";
                        $new_berg = isset($berg[$fetch['BR_ID']]) ? $berg[$fetch['BR_ID']] : "0";
                        $new_berg_in = isset($berg_in[$fetch['BR_ID']]) ? $berg_in[$fetch['BR_ID']] : "0";
                        $new_berg_out = isset($berg_out[$fetch['BR_ID']]) ? $berg_out[$fetch['BR_ID']] : "0";
                        $new_FNDATE = isset($FNDATE[$fetch['BR_ID']]) ? $FNDATE[$fetch['BR_ID']] : "0";
                        $new_FNDATE_IN = isset($FNDATE_IN[$fetch['BR_ID']]) ? $FNDATE_IN[$fetch['BR_ID']] : "0";
                        $new_SENTBRDATE = isset($SENTBRDATE[$fetch['BR_ID']]) ? $SENTBRDATE[$fetch['BR_ID']] : "0";
                        $new_BRACPDATE = isset($BRACPDATE[$fetch['BR_ID']]) ? $BRACPDATE[$fetch['BR_ID']] : "0";
                        $new_CUSTACPDATE = isset($CUSTACPDATE[$fetch['BR_ID']]) ? $CUSTACPDATE[$fetch['BR_ID']] : "0";
                      ?>
                        <td style="background-color:; color:; text-align:;"><?php echo $fetch['BR_ID']." : ".$fetch['BR_NAME']; ?></td>
                      <?php
                      }
                      if (isset($_POST['submit_2']) != "") {
                        $nwe_sale = isset($sale[$fetch['szone_code']]) ? $sale[$fetch['szone_code']] : "0";
                        $new_reg = isset($reg[$fetch['szone_code']]) ? $reg[$fetch['szone_code']] : "0";
                        $new_berg = isset($berg[$fetch['szone_code']]) ? $berg[$fetch['szone_code']] : "0";
                        $new_berg_in = isset($berg_in[$fetch['szone_code']]) ? $berg_in[$fetch['szone_code']] : "0";
                        $new_berg_out = isset($berg_out[$fetch['szone_code']]) ? $berg_out[$fetch['szone_code']] : "0";
                        $new_FNDATE = isset($FNDATE[$fetch['szone_code']]) ? $FNDATE[$fetch['szone_code']] : "0";
                        $new_FNDATE_IN = isset($FNDATE_IN[$fetch['szone_code']]) ? $FNDATE_IN[$fetch['szone_code']] : "0";
                        $new_SENTBRDATE = isset($SENTBRDATE[$fetch['szone_code']]) ? $SENTBRDATE[$fetch['szone_code']] : "0";
                        $new_BRACPDATE = isset($BRACPDATE[$fetch['szone_code']]) ? $BRACPDATE[$fetch['szone_code']] : "0";
                        $new_CUSTACPDATE = isset($CUSTACPDATE[$fetch['szone_code']]) ? $CUSTACPDATE[$fetch['szone_code']] : "0";
                      ?>
                        <td style="background-color:; color:; text-align:;"><?php echo $fetch['szone_code']." : ".$fetch['szone_name']; ?></td>
                      <?php
                      }
                      if (isset($_POST['submit_3']) != "") {
                        $nwe_sale = isset($sale[$fetch['zstaff_id']]) ? $sale[$fetch['zstaff_id']] : "0";
                        $new_reg = isset($reg[$fetch['zstaff_id']]) ? $reg[$fetch['zstaff_id']] : "0";
                        $new_berg = isset($berg[$fetch['zstaff_id']]) ? $berg[$fetch['zstaff_id']] : "0";
                        $new_berg_in = isset($berg_in[$fetch['zstaff_id']]) ? $berg_in[$fetch['zstaff_id']] : "0";
                        $new_berg_out = isset($berg_out[$fetch['zstaff_id']]) ? $berg_out[$fetch['zstaff_id']] : "0";
                        $new_FNDATE = isset($FNDATE[$fetch['zstaff_id']]) ? $FNDATE[$fetch['zstaff_id']] : "0";
                        $new_FNDATE_IN = isset($FNDATE_IN[$fetch['zstaff_id']]) ? $FNDATE_IN[$fetch['zstaff_id']] : "0";
                        $new_SENTBRDATE = isset($SENTBRDATE[$fetch['zstaff_id']]) ? $SENTBRDATE[$fetch['zstaff_id']] : "0";
                        $new_BRACPDATE = isset($BRACPDATE[$fetch['zstaff_id']]) ? $BRACPDATE[$fetch['zstaff_id']] : "0";
                        $new_CUSTACPDATE = isset($CUSTACPDATE[$fetch['zstaff_id']]) ? $CUSTACPDATE[$fetch['zstaff_id']] : "0";
                      ?>
                        <td style="background-color:; color:; text-align:;"><?php echo $fetch['zstaff_id']." : ".$fetch['zstaff_name']; ?></td>
                      <?php }
                        if($_REQUEST['refdata']!="bill") {
                          $sum_sale += $nwe_sale;
                      ?>
                      <td style="background-color:; color:; text-align:right;"><?php echo number_format($nwe_sale); ?></td>
                      <?php } ?>
                      <td style="background-color:; color:; text-align:right;"><?php echo number_format($new_reg); ?></td>
                      <td style="background-color:; color:; text-align:right;"><?php echo number_format($new_berg_in); ?></td>
                      <td style="background-color:; color:; text-align:right;"><?php echo number_format($new_berg_out); ?></td>
                      <td style="background-color:; color:; text-align:right;"><?php echo number_format($new_berg); ?></td>
                      <td style="background-color:; color:; text-align:right;"><?php echo number_format($new_reg-$new_berg); ?></td>
                      <td style="background-color:; color:; text-align:right;"><?php echo number_format($new_FNDATE); ?></td>
                      <td style="background-color:; color:; text-align:right;"><?php echo number_format($new_FNDATE_IN); ?></td>
                      <td style="background-color:; color:; text-align:right;"><?php echo number_format($new_SENTBRDATE); ?></td>
                      <td style="background-color:; color:; text-align:right;"><?php echo number_format($new_BRACPDATE); ?></td>
                      <td style="background-color:; color:; text-align:right;"><?php echo number_format($new_CUSTACPDATE); ?></td>
                    </tr>
                    <?php
                      $item++;
                      $sum_reg += $new_reg;
                      $sum_berg_in += $new_berg_in;
                      $sum_berg_out += $new_berg_out;
                      $sum_berg += $new_berg;
                      $sum_reg_berg += $new_reg-$new_berg;
                      $sum_FNDATE += $new_FNDATE;
                      $sum_FNDATE_IN += $new_FNDATE_IN;
                      $sum_SENTBRDATE += $new_SENTBRDATE;
                      $sum_BRACPDATE += $new_BRACPDATE;
                      $sum_CUSTACPDATE += $new_CUSTACPDATE;
                      }
                    ?>
                  </tbody>
                  <tfoot>
                    <tr>
                      <th style="background-color:black; color:#fff; text-align:center;">รวม</th>
                    <?php
                      if($_REQUEST['refdata']!="bill") {
                    ?>
                      <th style="background-color:green; color:#fff; text-align:right;"> <?php echo number_format($sum_sale); ?></th>
                    <?php } ?>
                      <th style="background-color:; color:#000; text-align:right;"> <?php echo number_format($sum_reg); ?></th>
                      <th style="background-color:; color:#000; text-align:right;"> <?php echo number_format($sum_berg_in); ?></th>
                      <th style="background-color:; color:#000; text-align:right;"> <?php echo number_format($sum_berg_out); ?></th>
                      <th style="background-color:; color#000:; text-align:right;"> <?php echo number_format($sum_berg); ?></th>
                      <th style="background-color:red; color:#fff; text-align:right;"> <?php echo number_format($sum_reg_berg); ?></th>
                      <th style="background-color:blue; color:#fff; text-align:right;"> <?php echo number_format($sum_FNDATE); ?></th>
                      <th style="background-color:blue; color:#fff; text-align:right;"> <?php echo number_format($sum_FNDATE_IN); ?></th>
                      <th style="background-color:blue; color:#fff; text-align:right;"> <?php echo number_format($sum_SENTBRDATE); ?></th>
                      <th style="background-color:blue; color:#fff; text-align:right;"> <?php echo number_format($sum_BRACPDATE); ?></th>
                      <th style="background-color:blue; color:#fff; text-align:right;"> <?php echo number_format($sum_CUSTACPDATE); ?></th>
                    </tr>
                  </tfoot>
              </table>
            </div>
            </div>
          </div>
        </div>
      </div>
      <?php } ?>
      </div>
    </section>
  </div>
</div>

<?php
// include '_footer.php';
// include '_form.php';
?>
<div class="control-sidebar-bg"></div>
<?php
  include '_script_2.php';
  include 'scroll0.php';
?>
</body>
</html>
