<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8">
  <meta http-equiv="X-UA-Compatible" content="IE=edge">
  <title>EHONGMD</title>
  <?php
  include '_plugin.php';
  include '_connect.php';
  ?>
  <script src="bower_components/jquery/dist/jquery.min.js"></script>

</head>
<body class="hold-transition skin-blue sidebar-mini">

<style media="screen">
div.ex1 {
  height: 550px;
  overflow-y: scroll;
}
table{
  /* font: 12px; */
}
</style>

<div class="wrapper">

<?php
include '_header.php';
include '_menu.php';
?>
  <div class="content-wrapper">
    <section class="content">
      <div class="row">
      <!-- ค้นหา -->
      <?php
      $CROP_NO_1 = $_POST['CROP_NO_1'];
      $DMY_11 = $_POST['DMY_1']." 00:00:00.000";
      $DMY_21 = $_POST['DMY_2']." 00:00:00.000";
      ?>
      <?php
      include '_connect.php';
      $_month_name = array("01"=>"มกราคม",  "02"=>"กุมภาพันธ์",  "03"=>"มีนาคม",
         "04"=>"เมษายน",  "05"=>"พฤษภาคม",  "06"=>"มิถุนายน",
         "07"=>"กรกฎาคม",  "08"=>"สิงหาคม",  "09"=>"กันยายน",
         "10"=>"ตุลาคม", "11"=>"พฤศจิกายน",  "12"=>"ธันวาคม");
      $m = date('m');
      include 'reg_search_1.3.php'
      ?>
      <?php
      $CHK_BERG1 = substr($DMY_11,0,4)+543; $CHK_BERG2 = substr($DMY_11,5,2); $CHK_BERG3 = substr($DMY_11,8,2);
      $CHK_BERG12a = $CHK_BERG3." ". $_month_name[$CHK_BERG2]." ".$CHK_BERG1;

      $CHK_BERG11 = substr($DMY_21,0,4)+543; $CHK_BERG21 = substr($DMY_21,5,2); $CHK_BERG31 = substr($DMY_21,8,2);
      $CHK_BERG12b = " ถึงวันที่ ".$CHK_BERG31." ". $_month_name[$CHK_BERG21]." ".$CHK_BERG11;

      if ($CROP_NO_1 != '') {
        $CROP_NO_11 = "and c.REG_CROPNO = '$CROP_NO_1'";
      }
      if ($CROP_NO_1 == '') {
        $CROP_NO_11 = "";
      }

      $sql_CNO = "SELECT * FROM EHONGDB.dbo.CROPORATION WHERE CROP_NO = '$CROP_NO_1' ";
      $select_CNO=$conn->prepare($sql_CNO);
      $select_CNO->execute();
      $fetch_CNO=$select_CNO->fetch();
      // $CROP_NO_S = $fetch_CNO['CROP_NO'];
      if ($fetch_CNO['CROP_NO'] == '') { $CROP_NO_12 = 'ทั้งหมด'; }
      else { $CROP_NO_12 = $fetch_CNO['CROP_NAME']; }
      $code = "a.CHK_BERG BETWEEN '$DMY_11' and '$DMY_21' $CROP_NO_11 ";
      $code2 = "and a.FEE_NO = '00' ";

      ?>
      <!-- ตาราง -->
      <div class="col-md-12">
        <div class="box box-success">
          <div class="box-header">
            <h3 class="box-title"><p class="text-success">รายงานการเบิกเงินค่า พ.ร.บ.</p></h3>
            <p><?php echo "บริษัท : ".$CROP_NO_12;?><br> <?php echo "สำหรับงวด เริ่ม ".$CHK_BERG12a.$CHK_BERG12b; ?></p>
          </div>
          <div class="box-body">
            <div class="form-group ex1" style="overflow-x:auto; overflow-y:10%;">
              <table id="example31" class="table table-striped table-bordered" style="width:100%" >
                <thead>
                <tr style="white-space: nowrap;">
                  <td width="" rowspan="" style="text-align:center;"><b>No.</b></td>
                  <td width="" rowspan="" style="text-align:center;"><b>ชื่อลูกค้า</b></td>
                  <td width="" rowspan="" style="text-align:center;"><b>เลขที่เครื่อง</b></td>
                  <td width="" rowspan="" style="text-align:center;"><b>ยี่ห้อรถ</b></td>
                  <td width="" rowspan="" style="text-align:center;"><b>สาขา</b></td>
                  <td width="" rowspan="" style="text-align:center;"><b>จำนวน CC</b></td>
                  <td width="" rowspan="" style="text-align:center;"><b>บริษัทที่เป็นเจ้าของรถ</b></td>
                  <td width="" rowspan="" style="text-align:center;"><b>ค่านายหน้า ค่าจ้าง</b></td>
                  <td width="" rowspan="" style="text-align:center;"><b>รายการเบิก (บาท)</b></td>
                  <td width="" rowspan="" style="text-align:center;"><b>รวม</b></td>

                  <td width="" rowspan="" style="background-color:DodgerBlue; color:#fff;"><b>ต้นทุนบริการฟรี</b></td>
                  <td width="" rowspan="" style="background-color:DodgerBlue; color:#fff;"><b>ต้นทุนบริการร่วมกับการจดทะเบียน</b></td>
                  <td width="" rowspan="" style="background-color:DodgerBlue; color:#fff;"><b>ต้นทุนรับบริการเฉพาะ</b></td>
                  <td width="" rowspan="" style="background-color:DodgerBlue; color:#fff;"><b>รวม</b></td>
                  <td width="" rowspan="" style="background-color:DodgerBlue; color:#fff;"><b>วันที่จ่ายเงิน</b></td>
                  <td width="" rowspan="" style="background-color:DodgerBlue; color:#fff;"><b>เลขที่ใบสำคัญ</b></td>

                  <td width="" rowspan="" style="text-align:center;"><b>หมายเหตุ</b></td>
                </tr>
                </thead>
                <tbody style="white-space: nowrap;">
                  <?php
                  $sql = "SELECT DISTINCT a.BILL_NO, a.CHK_BERG as cb_date, a.BG_PROV as prov_no1, a.FEE_COST as FEE_COST1, a.BR_ID,
                  b.PR_NAME, b.PR_CODE,
                  c.REG_NO, c.REG_CROPNO, c.FEE_NO, c.REG_DATE1, c.REG_BREG, c.REG_BILLBREG, c.REG_BILLBREGTRANSPORT, c.REG_BILLUSER1, c.REG_BILLUSER2, c.REG_PROVINCE, c.REG_USERBREG, c.REG_CROP,
                  d.BILL_NO, d.PS_NAME, d.CROP_NAME, d.BRAND_NAME, d.SERIAL_NO, d.PSERIAL_NO,
                  e.tsp_no, e.tsp_open, e.tsp_registrar1, e.tsp_registrar,
                  f.FEE_NO, f.FEE_NAME,
                  g.xcorp_no,
                  h.BR_ID, h.BR_NAME,
                  i.FEE_NO AS IFEE ,i.FEE_SUMPRICE AS GFEE_SUM, i.FEE_PRICE,
                  j.fee_no, j.c1, j.c2, j.amnt, j.hint
                  FROM EHONGDB.dbo.REG_BILLDATADOCUMENT a
                  LEFT OUTER JOIN (SELECT * FROM [EHONGDB].[dbo].[PROV]) b ON (a.BG_PROV = b.PR_CODE)
                  LEFT OUTER JOIN (SELECT * FROM [CONFIG].[dbo].[REG_BILLDATA_V1]) c ON (a.BILL_NO=c.BILL_NO) AND (a.FEE_NO=c.FEE_NO)
                  LEFT OUTER JOIN (SELECT * FROM [EHONGDB].[dbo].[REG_BILL]) d ON (a.BILL_NO=d.BILL_NO)
                  LEFT OUTER JOIN (SELECT * FROM [CONFIG].[dbo].[tsp_transport]) e ON (a.BG_PROV=e.tsp_no)
                  LEFT OUTER JOIN (SELECT * FROM [EHONGDB].[dbo].[REG_FEEDATA]) f ON (a.FEE_NO=f.FEE_NO)
                  LEFT OUTER JOIN (SELECT * FROM [EHONGDB].[dbo].[REG_BILLDATA]) g ON (a.BILL_NO=g.BILL_NO)
                  LEFT OUTER JOIN (SELECT * FROM [EHONGDB].[dbo].[BRANCH]) h ON (a.BR_ID=h.BR_ID)
                  LEFT OUTER JOIN (SELECT * FROM [EHONGDB].[dbo].[REG_BILLDATA]) i ON (a.BILL_NO=i.BILL_NO)
                  LEFT OUTER JOIN (SELECT * FROM [EHONGDB].[dbo].[reg_feedataccsize]) j ON (i.FEE_PRICE=j.amnt)
                  WHERE $code and (a.status<>'V') and (a.chkdate2 is not null and a.chkstatus2='C') and (a.acpdate is not null) and (a.chk_berg is not null) and c.REG_STATUS = 'Y' and i.FEE_NO = '11'
                  ORDER BY b.PR_CODE,a.CHK_BERG,i.FEE_NO";
                  $select=$conn->prepare($sql);
                  $select->execute();
                  // echo $sql;
                  $item = 1;
                  $FEE_NO_02 = "0"; $FEE_NO_XX = "0";
                  $SUMB1 = 0; $SUMB2 = 0; $SUMB3 = 0;
                  $SUMB4 = 0; $SUMB5 = 0; $SUMB6 = 0;
                  $SUMB7 = 0;
                  $REG_BILLBREGTRANSPORT = 0;
                  $REG_10 = '10.00';
                  while($fetch=$select->fetch())
                  {
                    $REG_PRB = number_format($fetch['GFEE_SUM'],2);
                    $SUMPRB10 = $REG_PRB-$REG_10;
                    $SUMPRB10x = $SUMPRB10+$REG_10;
                    $REG_1 = "0.00";
                    $REG_2 = "0.00";
                    $SUMREG123 = $REG_1+$REG_PRB+$REG_2;
                    if ($fetch['cb_date'] != '') {
                      $CHK_BERG1 = substr($fetch['cb_date'],0,4)+543; $CHK_BERG2 = substr($fetch['cb_date'],5,2); $CHK_BERG3 = substr($fetch['cb_date'],8,2);
                      $CHK_BERG = $CHK_BERG3." ". $_month_name[$CHK_BERG2]." ".$CHK_BERG1;
                    } else {
                      $CHK_BERG = '';
                    }
                    $FEE_NO_XX += str_replace( ',', '', $fetch['REG_BILLBREG']);
                    $SUMB3 += str_replace( ',', '', $SUMPRB10x);
                    $SUMB7 += str_replace( ',', '', $SUMREG123);
                  ?>
                  <tr>
                    <td style="text-align:center;"><?php echo $item; ?>.</td>
                    <td><?php echo $fetch['PS_NAME']; ?></td>
                    <td><?php echo $fetch['PSERIAL_NO']; ?></td>
                    <td><?php echo $fetch['BRAND_NAME']; ?></td>
                    <td><?php echo $fetch['BR_ID'].":".$fetch['BR_NAME']; ?></td>
                    <td class="text-red"><?php echo $fetch['c1']."-".$fetch['c2'];; ?></td>
                    <td><?php echo $fetch['REG_CROP']; ?></td>

                    <?php if ($REG_PRB == "0.00") { ?>
                    <td class="text-black"><b><?php echo "0.00"; ?></b></td>
                    <?php } else { ?>
                    <td class="text-blue"><b><?php echo $REG_10; ?></b></td>
                    <?php $SUMB1 += str_replace( ',', '', $REG_10); } ?>

                    <?php if ($SUMPRB10 == "-10.00") { ?>
                    <td class="text-black"><b><?php echo "0.00"; ?></b></td>
                    <?php } else { ?>
                    <td class="text-blue"><b><?php echo number_format($SUMPRB10,2); ?></b></td>
                    <?php $SUMB2 += str_replace( ',', '', $SUMPRB10); } ?>

                    <td class="text-green"><b><?php echo number_format($SUMPRB10x,2); ?></b></td>

                    <?php if ($REG_1 == "0.00") { ?>
                    <td class="text-black"><b><?php echo "0.00"; ?></b></td>
                    <?php } else { ?>
                    <td  class="text-blue"><b><?php echo number_format($REG_1,2); ?></b></td>
                    <?php $SUMB4 += str_replace( ',', '', $REG_1); } ?>

                    <?php if ($REG_PRB == "0.00") { ?>
                    <td class="text-black"><b><?php echo "0.00"; ?></b></td>
                    <?php } else { ?>
                    <td  class="text-blue"><b><?php echo $REG_PRB; ?></b></td>
                    <?php $SUMB5 += str_replace( ',', '', $REG_PRB); } ?>

                    <?php if ($REG_2 == "0.00") { ?>
                    <td class="text-black"><b><?php echo "0.00"; ?></b></td>
                    <?php } else { ?>
                    <td  class="text-blue"><b><?php echo number_format($REG_2,2); ?></b></td>
                    <?php $SUMB6 += str_replace( ',', '', $REG_2); } ?>

                    <td class="text-green"><b><?php echo number_format($SUMREG123,2); ?></b></td>
                    <td><?php echo $CHK_BERG; ?></td>
                    <td style="background-color:; color:;" class="text-blue"><?php echo $fetch['BILL_NO']; ?></td>
                    <td></td>
                  </tr>
                  <?php
                    $item++; }
                  ?>
                  </tbody>
                  <tbody>
                  <tr style="background-color:DodgerBlue; color:#fff;">
                    <td colspan="7"><center><b>รวม</center></td>
                    <td style="text-align:center;"><b><?php echo number_format($SUMB1,2); ?></b></td>
                    <td style="text-align:center;"><b><?php echo number_format($SUMB2,2); ?></b></td>
                    <td style="text-align:center;"><b><?php echo number_format($SUMB3,2); ?></b></td>
                    <td style="text-align:center;"><b><?php echo number_format($SUMB4,2); ?></b></td>
                    <td style="text-align:center;"><b><?php echo number_format($SUMB5,2); ?></b></td>
                    <td style="text-align:center;"><b><?php echo number_format($SUMB6,2); ?></b></td>
                    <td style="text-align:center;"><b><?php echo number_format($SUMB7,2); ?></b></td>
                    <td></td>
                    <td></td>
                    <td></td>
                  </tr>
                </tbody>
              </table>
            </div>
          </div>
        </div>
      </div>

      </div>
    </section>
  </div>
</div>

<?php
// include '_footer.php';
// include '_form.php';
?>
<div class="control-sidebar-bg"></div>
<?php include '_script_2.php'; ?>
<!-- <script>
    var myApp = new function () {
        this.printTable = function () {
            var tab = document.getElementById('tab');
            var win = window.open('', '', 'height=700,width=1200');
            win.document.write(tab.outerHTML);
            win.document.close();
            win.print();
            // tab.style.font = "bold 2px arial,serif";
            // tab.style.backgroundColor = "#FF0000";
            // win.close();
        }
    }
</script> -->
</body>
</html>
