<?php //error_reporting(0); ?>
<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8">
  <meta http-equiv="X-UA-Compatible" content="IE=edge">
  <title>EHONGMD | รายงานทะเบียนตรวจสอบ</title>
  <script src="bower_components/jquery/dist/jquery.min.js"></script>
  <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">
      <!-- ค้นหา -->
      <!-- ตาราง -->
      <?php
      $BR_IDS = $_REQUEST['BR_IDS'];
      $ZSONE_CODES = $_REQUEST['ZSONE_CODES'];
      $ZSTAFFS = $_REQUEST['ZSTAFFS'];
      $ST = $_REQUEST['ST'];
      $dateab = date('Y-m-d');

      if ($BR_IDS != "") {
        $idcoed_1 = "and ax.BR_ID = '$BR_IDS'";
      }
      else {
        $idcoed_1 = "";
      }
      if ($ZSONE_CODES != "") {
        $idcoed_2 = "and bx.szone_code = '$ZSONE_CODES'";
      }
      else {
        $idcoed_2 = "";
      }
      if ($ZSTAFFS != "") {
        $idcoed_3 = "and bx.zstaff_id = '$ZSTAFFS'";
      }
      else {
        $idcoed_3 = "";
      }

      if ($ST == "1" || $ST == "SUM1") {
        $str ="and (a.senttype='1') and (a.sentbrdate is not null)  and (a.fndate is not null) and (a.bracpdate is null) $idcoed_1 $idcoed_2 $idcoed_3";
        $selectname = "สาขาค้างรับ";
        $out_a = "ค้างรับน้อยกว่า 15 วัน";
        $out_b = "ค้างรับมากกว่า 15 วัน";
      }
      if ($ST == "2" || $ST == "SUM2") {
        $str = "and (a.senttype='1') and (a.sentbrdate is not null)  and (a.fndate is not null) and (a.bracpdate is not null) and (a.custacpdate is null) $idcoed_1 $idcoed_2 $idcoed_3";
        $selectname = "ลูกค้าค้างรับ";
        $out_a = "ค้างรับน้อยกว่า 45 วัน";
        $out_b = "ค้างรับมากกว่า 45 วัน";
      }

      if ($ST == "245a" || $ST == "SUM245a") {
        $str = "and (a.senttype='1') and (a.sentbrdate is not null)  and (a.fndate is not null) and (a.bracpdate is not null) and (a.custacpdate is null) $idcoed_1 $idcoed_2 $idcoed_3 and (datediff(day,a.bracpdate,'$dateab') <= 45 )";
        $selectname = "ลูกค้าค้างรับ";
        $out_a = "ค้างรับน้อยกว่า 45 วัน";
        $out_b = "ค้างรับมากกว่า 45 วัน";
      }
      if ($ST == "245b" || $ST == "SUM245b") {
        $str = "and (a.senttype='1') and (a.sentbrdate is not null)  and (a.fndate is not null) and (a.bracpdate is not null) and (a.custacpdate is null) $idcoed_1 $idcoed_2 $idcoed_3 and (datediff(day,a.bracpdate,'$dateab') > 45 )";
        $selectname = "ลูกค้าค้างรับ";
        $out_a = "ค้างรับน้อยกว่า 45 วัน";
        $out_b = "ค้างรับมากกว่า 45 วัน";
      }

      if ($ST == "3") {
        $str = "and (a.senttype='3') and (a.sentbrdate is not null)  and (a.fndate is not null) and (a.bracpdate is null)";
        $selectname = "สาขาค้างรับ (ไฟแนนซ์)";
      }
      if ($ST == "4") {
        $str = "and (a.senttype='3') and (a.sentbrdate is not null)  and (a.fndate is not null) and (a.bracpdate is not null) and (custacpdate is null)";
        $selectname = "ลูกค้าค้างรับ (ไฟแนนซ์)";
      }

      include 'reg_count.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 $selectname; ?></p></h3>
          </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 rowspan="" style="background-color:black; color:#fff; text-align:center;">NO.</th>
                      <th rowspan="" style="background-color:black; color:#fff; text-align:center;">BARCODE ซอง</th>
                      <th colspan="" style="background-color:black; color:#fff; text-align:center;">BARCODE ชิ้นงาน</th>
                      <!-- <th colspan="" style="background-color:black; color:#fff; text-align:center;">ว/ด/ป ลูกค้ารับ</th> -->
                      <th colspan="" style="background-color:black; color:#fff; text-align:center;">ว/ด/ป สาขารับงาน</th>
                      <th rowspan="" style="background-color:black; color:#fff; text-align:center;">ว/ด/ป ทะเบียนส่งงาน</th>

                      <?php
                        if ($ST == "1" || $ST == "SUM1") {
                      ?>
                        <th rowspan="" style="background-color:green; color:#fff; text-align:center;"><?php //echo $out_a; ?><i class="fa fa-arrow-circle-left" aria-hidden="true"></i> 45 วัน ( <?php echo $reg_sum_a; ?> ) </th>
                        <th rowspan="" style="background-color:red; color:#fff; text-align:center;"><?php //echo $out_b; ?><i class="fa fa-arrow-circle-right" aria-hidden="true"></i> 45 วัน ( <?php echo $reg_sum_b; ?> ) </th>
                      <?php
                      } if ($ST == "2" || $ST == "SUM2") {
                        ?>
                        <th rowspan="" style="background-color:green; color:#fff; text-align:center;"><?php //echo $out_a; ?><i class="fa fa-arrow-circle-left" aria-hidden="true"></i> 45 วัน ( <?php echo $reg_sum_a; ?> ) </th>
                        <th rowspan="" style="background-color:red; color:#fff; text-align:center;"><?php //echo $out_b; ?><i class="fa fa-arrow-circle-right" aria-hidden="true"></i> 45 วัน ( <?php echo $reg_sum_b; ?> ) </th>
                        <?php
                      } if ($ST == "3" || $ST == "4") {
                      }
                      ?>
                      <?php
                        if ($ST == "245a" || $ST == "SUM245a") {
                      ?>
                        <th rowspan="" style="background-color:green; color:#fff; text-align:center;"><?php //echo $out_a; ?><i class="fa fa-arrow-circle-left" aria-hidden="true"></i> 45 วัน ( <?php echo $reg_sum_a; ?> ) </th>
                        <th rowspan="" style="background-color:red; color:#fff; text-align:center;"><?php //echo $out_b; ?><i class="fa fa-arrow-circle-right" aria-hidden="true"></i> 45 วัน ( <?php echo $reg_sum_b; ?> ) </th>
                      <?php } ?>
                      <?php
                        if ($ST == "245b" || $ST == "SUM245b") {
                      ?>
                        <th rowspan="" style="background-color:green; color:#fff; text-align:center;"><?php //echo $out_a; ?><i class="fa fa-arrow-circle-left" aria-hidden="true"></i> 45 วัน ( <?php echo $reg_sum_a; ?> ) </th>
                        <th rowspan="" style="background-color:red; color:#fff; text-align:center;"><?php //echo $out_b; ?><i class="fa fa-arrow-circle-right" aria-hidden="true"></i> 45 วัน ( <?php echo $reg_sum_b; ?> ) </th>
                      <?php } ?>
                      <th colspan="" style="background-color:black; color:#fff; text-align:center;">กลุ่มงาน</th>
                      <th colspan="" style="background-color:black; color:#fff; text-align:center;">เอกสาร</th>
                      <th colspan="" style="background-color:black; color:#fff; text-align:center;">#สัญญา</th>
                      <th colspan="" style="background-color:black; color:#fff; text-align:center;">ชื่อ-สกุล ลูกค้า</th>
                      <th rowspan="" style="background-color:black; color:#fff; text-align:center;">สภาพบัญชี</th>
                      <th rowspan="" style="background-color:black; color:#fff; text-align:center;">สมาชิกมดแดงคลับ</th>
                      <th rowspan="" style="background-color:black; color:#fff; text-align:center;">เลขเครื่อง</th>
                      <th colspan="" style="background-color:black; color:#fff; text-align:center;">เลขตัวถัง</th>
                      <th colspan="" style="background-color:black; color:#fff; text-align:center;">เลขใบเสร็จ</th>
                      <th colspan="" style="background-color:black; color:#fff; text-align:center;">ทะเบียน</th>
                      <th rowspan="" style="background-color:black; color:#fff; text-align:center;">#ใบสั่งทำ</th>
                      <th colspan="" style="background-color:black; color:#fff; text-align:center;">สาขา</th>
                      <th colspan="" style="background-color:black; color:#fff; text-align:center;">ยี่ห้อ</th>
                      <th colspan="" style="background-color:black; color:#fff; text-align:center;">รุ่น</th>
                      <th colspan="" style="background-color:black; color:#fff; text-align:center;">สี</th>
                    </tr>
                  </thead>
                  <tbody style="white-space: nowrap">
                    <?php
                    $sql = "SELECT * from ehongdb.dbo.reg_billdatadocument a
                    left outer join (select bill_no as xbill_no,ps_id,ps_name,brand_name,serial_no,pserial_no,model_no,color_name,chkhint0,g_id from ehongdb.dbo.reg_bill) b on(a.bill_no=b.xbill_no)
                    left outer join (select packbarcode as xpackbarcode,packsenttype from ehongdb.dbo.reg_packdata) c on(a.packbarcode=c.xpackbarcode)
                    left outer join(SELECT * FROM [EHONGDB].[dbo].[BRANCH]) ax on (ax.BR_ID=a.BR_ID)
                    left outer join(SELECT * FROM [EHONGDB].[dbo].[branch_salezone]) bx on (ax.SZONE_CODE=bx.szone_code)
                    left outer join(SELECT * FROM [EHONGDB].[dbo].[PROV]) cx on (a.RG_PROV=cx.PR_CODE)
                    left outer join(SELECT G_ID AS GW_ID, G_NAME FROM EHONGDB.dbo.REG_GWORK) gw on (b.g_id=gw.GW_ID)
                      left outer join(SELECT ct_no AS CT_NOXXX, fw_status, status, notpaycost FROM EHONGDB.dbo.hps_ctmain) ctno on (ctno.CT_NOXXX=a.CT_NO)
                      left outer join (select * from EHONGDB.dbo.hps_cttracktype) cct on (cct.TRK_TYPE=ctno.fw_status)
                      left outer join (select * from EHONGDB.dbo.hps_ctstatusname) cts on (cts.ct_status=ctno.status)
                      left outer join (select ps_id as px_id, lineid from EHONGDB.dbo.PERSON) p on (b.ps_id = p.px_id)
                    where (a.status<>'V') and (a.fdoc_no<>'N4') $str";
                    // echo $sql;
                    $select=$conn->prepare($sql);
                    $select->execute();
                    $item = 1;
                    while($fetch=$select->fetch()){
                      if ($fetch['CUSTACPDATE'] != '') {
                        $CUSTACPDATE1 = substr($fetch['CUSTACPDATE'],0,4)+543; $CUSTACPDATE2 = substr($fetch['CUSTACPDATE'],5,2); $CUSTACPDATE3 = substr($fetch['CUSTACPDATE'],8,2);
                        $CUSTACPDATE = $CUSTACPDATE3."/".$CUSTACPDATE2."/".$CUSTACPDATE1;
                      } else {
                        $CUSTACPDATE = '';
                      }
                      if ($fetch['BRACPDATE'] != '') {
                        $BRACPDATE1 = substr($fetch['BRACPDATE'],0,4)+543; $BRACPDATE2 = substr($fetch['BRACPDATE'],5,2); $BRACPDATE3 = substr($fetch['BRACPDATE'],8,2);
                        $BRACPDATE = $BRACPDATE3."/".$BRACPDATE2."/".$BRACPDATE1;
                      } else {
                        $BRACPDATE = '';
                      }
                      if ($fetch['SENTBRDATE'] != '') {
                        $SENTBRDATE1 = substr($fetch['SENTBRDATE'],0,4)+543; $SENTBRDATE2 = substr($fetch['SENTBRDATE'],5,2); $SENTBRDATE3 = substr($fetch['SENTBRDATE'],8,2);
                        $SENTBRDATE = $SENTBRDATE3."/".$SENTBRDATE2."/".$SENTBRDATE1;
                      } else {
                        $SENTBRDATE = '';
                      }

                      if ($fetch['lineid'] != '') {
                        $lineid = "เป็นสมาชิก";
                      } else {
                        $lineid = "";
                      }

                    ?>
                    <tr>
                      <td style="background-color:; color:; text-align:;"><?php echo number_format($item); ?>.</td>
                      <td style="background-color:; color:; text-align:;"><?php echo $fetch['packbarcode']; ?></td>
                      <td style="background-color:; color:; text-align:;"><?php echo $fetch['barcode']; ?></td>
                      <!-- <td style="background-color:; color:; text-align:;"><?php echo $CUSTACPDATE; ?></td> -->
                      <td style="background-color:; color:; text-align:;"><?php echo $BRACPDATE; ?></td>
                      <td style="background-color:; color:; text-align:;"><?php echo $SENTBRDATE; ?></td>

                      <?php
                        if ($ST == "1" || $ST == "SUM1") {
                          $day = "15";
                          $DateStart = $SENTBRDATE3; //วันเริ่มต้น
                          $MonthStart = $SENTBRDATE2; //เดือนเริ่มต้น
                          $YearStart = $SENTBRDATE1-543; //ปีเริ่มต้น
                          $DateEnd = date('d'); //วันสิ้นสุด
                          $MonthEnd = date('m'); //เดือนสิ้นสุด
                          $YearEnd = date('Y'); //ปีสิ้นสุด
                          $Start = mktime(0,0,0,$MonthStart ,$DateStart ,$YearStart);
                          $End = mktime(0,0,0,$MonthEnd,$DateEnd,$YearEnd);
                          $DateNum=ceil(($End-$Start)/86400); // 28
                          // echo $DateNum;
                          // น้อยกว่า 15 วัน
                          if ($DateNum <= $day) {
                            $outdate_a = $DateNum;
                          } else {
                            $outdate_a = "";
                          }
                          // มากกว่า 15 วัน
                          if ($DateNum > $day) {
                            $outdate_b = $DateNum;
                          } else {
                            $outdate_b = "";
                          }
                      ?>
                      <th rowspan="" style="background-color:; color:green; text-align:center;"><?php if($outdate_a != "") { echo number_format($outdate_a); } ?></th>
                      <th rowspan="" style="background-color:; color:red; text-align:center;"><?php if($outdate_b != "") { echo number_format($outdate_b); } ?></th>

                      <?php
                      } if ($ST == "2" || $ST == "SUM2") {
                        $day = "45";
                        $DateStart = $BRACPDATE3; //วันเริ่มต้น
                        $MonthStart = $BRACPDATE2; //เดือนเริ่มต้น
                        $YearStart = $BRACPDATE1-543; //ปีเริ่มต้น
                        $DateEnd = date('d'); //วันสิ้นสุด
                        $MonthEnd = date('m'); //เดือนสิ้นสุด
                        $YearEnd = date('Y'); //ปีสิ้นสุด
                        $Start = mktime(0,0,0,$MonthStart ,$DateStart ,$YearStart);
                        $End = mktime(0,0,0,$MonthEnd,$DateEnd,$YearEnd);
                        $DateNum=ceil(($End-$Start)/86400); // 28
                        // echo $DateNum;
                        // น้อยกว่า 45 วัน
                        if ($DateNum <= $day) {
                          $outdate_a = $DateNum;
                        } else {
                          $outdate_a = "";
                        }
                        // มากกว่า 45 วัน
                        if ($DateNum > $day) {
                          $outdate_b = $DateNum;
                        } else {
                          $outdate_b = "";
                        }
                      ?>
                      <th rowspan="" style="background-color:; color:green; text-align:center;"><?php if($outdate_a != "") { echo number_format($outdate_a); } ?></th>
                      <th rowspan="" style="background-color:; color:red; text-align:center;"><?php if($outdate_b != "") { echo number_format($outdate_b); } ?></th>

                      <?php
                      } if ($ST == "3" || $ST == "4") {
                        // code...
                      }
                      ?>

                      <?php
                        if ($ST == "245a" || $ST == "SUM245a") {
                        $day = "45";
                        $DateStart = $BRACPDATE3; //วันเริ่มต้น
                        $MonthStart = $BRACPDATE2; //เดือนเริ่มต้น
                        $YearStart = $BRACPDATE1-543; //ปีเริ่มต้น
                        $DateEnd = date('d'); //วันสิ้นสุด
                        $MonthEnd = date('m'); //เดือนสิ้นสุด
                        $YearEnd = date('Y'); //ปีสิ้นสุด
                        $Start = mktime(0,0,0,$MonthStart ,$DateStart ,$YearStart);
                        $End = mktime(0,0,0,$MonthEnd,$DateEnd,$YearEnd);
                        $DateNum=ceil(($End-$Start)/86400); // 28
                        // echo $DateNum;
                        // น้อยกว่า 45 วัน
                        if ($DateNum <= $day) {
                          $outdate_a = $DateNum;
                        } else {
                          $outdate_a = "";
                        }
                        // มากกว่า 45 วัน
                        if ($DateNum > $day) {
                          $outdate_b = $DateNum;
                        } else {
                          $outdate_b = "";
                        }
                      ?>
                      <th rowspan="" style="background-color:; color:green; text-align:center;"><?php if($outdate_a != "") { echo number_format($outdate_a); } ?></th>
                      <th rowspan="" style="background-color:; color:red; text-align:center;"><?php if($outdate_b != "") { echo number_format($outdate_b); } ?></th>
                      <?php } ?>

                      <?php
                        if ($ST == "245b" || $ST == "SUM245b") {
                        $day = "45";
                        $DateStart = $BRACPDATE3; //วันเริ่มต้น
                        $MonthStart = $BRACPDATE2; //เดือนเริ่มต้น
                        $YearStart = $BRACPDATE1-543; //ปีเริ่มต้น
                        $DateEnd = date('d'); //วันสิ้นสุด
                        $MonthEnd = date('m'); //เดือนสิ้นสุด
                        $YearEnd = date('Y'); //ปีสิ้นสุด
                        $Start = mktime(0,0,0,$MonthStart ,$DateStart ,$YearStart);
                        $End = mktime(0,0,0,$MonthEnd,$DateEnd,$YearEnd);
                        $DateNum=ceil(($End-$Start)/86400); // 28
                        // echo $DateNum;
                        // น้อยกว่า 45 วัน
                        if ($DateNum <= $day) {
                          $outdate_a = $DateNum;
                        } else {
                          $outdate_a = "";
                        }
                        // มากกว่า 45 วัน
                        if ($DateNum > $day) {
                          $outdate_b = $DateNum;
                        } else {
                          $outdate_b = "";
                        }
                      ?>
                      <th rowspan="" style="background-color:; color:green; text-align:center;"><?php if($outdate_a != "") { echo number_format($outdate_a); } ?></th>
                      <th rowspan="" style="background-color:; color:red; text-align:center;"><?php if($outdate_b != "") { echo number_format($outdate_b); } ?></th>
                      <?php } ?>
                      <td style="background-color:; color:; text-align:;"><?php echo $fetch['GW_ID']." : ".$fetch['G_NAME']; ?></td>
                      <td style="background-color:; color:; text-align:;"><?php echo $fetch['FDOC_NAME']; ?></td>
                      <th style="background-color:; color:; text-align:;"> <a href="http://192.168.10.53/xehong-linux/index.php?sid=hps&fid=view&presaleno=&mo_no=&ct_no=<?php echo $fetch['CT_NO']; ?>#" target="_blank"><?php echo $fetch['CT_NO']; ?></a></th>
                      <th style="background-color:; color:; text-align:;"> <a href="http://192.168.10.53/xehong-linux/index.php?sid=person&fid=maindata&ps_id=<?php echo $fetch['ps_id']; ?>&vcmd=load&typemenu=2#" target="_blank"><?php echo $fetch['ps_name']; ?></a></th>
                      <th style="background-color:; color:red; text-align:;"><?php echo $fetch['ct_statusname']; ?></th>
                      <th style="background-color:; color:green; text-align:;"><?php echo $lineid; ?></th>
                      <!-- <td style="background-color:; color:red; text-align:;"><?php echo $fetch['fw_status']." : ".$fetch['TRK_NAME']; ?></td> -->
                      <td style="background-color:; color:; text-align:;"><?php echo $fetch['serial_no']; ?></td>
                      <td style="background-color:; color:; text-align:;"><?php echo $fetch['pserial_no']; ?></td>
                      <td style="background-color:; color:; text-align:;"><?php echo $fetch['REFNO']; ?></td>
                      <td style="background-color:; color:; text-align:;"><?php echo $fetch["PLATE1"]."-".$fetch["PLATE2"]." ".$fetch["PR_NAME"]; ?></td>
                      <th style="background-color:; color:; text-align:;"> <a href="http://192.168.10.53/xehong-linux/index.php?sid=sale&fid=registbill&bill_no=<?php echo $fetch['BILL_NO']; ?>&vcmd=load#" target="_blank"><?php echo $fetch['BILL_NO']; ?></a></th>
                      <td style="background-color:; color:; text-align:;"><?php echo $fetch['BR_ID']." : ".$fetch['BR_NAME']; ?></td>
                      <td style="background-color:; color:; text-align:;"><?php echo $fetch['brand_name']; ?></td>
                      <td style="background-color:; color:; text-align:;"><?php echo $fetch['model_no']; ?></td>
                      <td style="background-color:; color:; text-align:;"><?php echo $fetch['color_name']; ?></td>
                    </tr>
                    <?php
                      $item++; }
                    ?>
                  </tbody>
              </table>
            </div>
            </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';
  include 'scroll0.php';
?>
</body>
</html>
