<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8">
  <meta http-equiv="X-UA-Compatible" content="IE=edge">
  <title>EHONGMD | รายงาน LINE ID</title>
  <?php
  include '_plugin.php';
  include '_connect.php';
  ?>
  <script src="bower_components/jquery/dist/jquery.min.js"></script>
  <script language="JavaScript">
	function ClickCheckAll(vol)
	{

		var i=1;
		for(i=1;i<=document.frmMain.hdnCount.value;i++)
		{
			if(vol.checked == true)
			{
				eval("document.frmMain.chkDel"+i+".checked=true");
			}
			else
			{
				eval("document.frmMain.chkDel"+i+".checked=false");
			}
		}
	}

	function onDelete()
	{
		if(confirm('ต้องการออกใบส่งเงินหรือไม่?')==true)
		{
			return true;
		}
		else
		{
			return false;
		}
	}
  </script>
</head>
<body class="hold-transition skin-red sidebar-mini">

  <style media="screen">
  div.ex1 {
    /* height: 550px;
    overflow-y: scroll; */
  }
  th, td, tbody { white-space: nowrap; }
  </style>

<div class="wrapper">
<?php
include '_header.php';
include '_menu.php';
?>
  <div class="content-wrapper">
    <section class="content">
      <div class="row">
      <!-- ค้นหา -->
      <?php
      include '_connect.php';
      include 'line_search_1.php'

      ?>
      <?php
      // $DMY1 = $_POST["DMY1"]; // วันที่
      // $DMY2 = $_POST["DMY2"]; // ถึงวันที่
      //$BR_IDx = $_POST["BR_ID"]; // สาขา
      // $zone_codex = $_POST["zone_code"]; // เขต
      // $pr_codex = $_POST["pr_code"]; // จังหวัด
      // $infofromx = $_POST["infofrom"]; // ช่องทางการรับรู้
      $DMY1 = '2020-07-01';
      $DMY2 = '2020-07-31';
      $BR_IDx = "";
      // if ($_POST["BR_ID"] != '') { $BR_ID = "a.BR_ID = $BR_IDx AND"; }
      // else { $BR_ID = ''; }

      // echo $BR_ID;

      $_month_name = array("01"=>"มกราคม",  "02"=>"กุมภาพันธ์",  "03"=>"มีนาคม",
         "04"=>"เมษายน",  "05"=>"พฤษภาคม",  "06"=>"มิถุนายน",
         "07"=>"กรกฎาคม",  "08"=>"สิงหาคม",  "09"=>"กันยายน",
         "10"=>"ตุลาคม", "11"=>"พฤศจิกายน",  "12"=>"ธันวาคม");
      $m = date('m');

      $calcdate1 = substr($DMY1,0,4); $calcdate2 = substr($DMY1,5,2); $calcdate3 = substr($DMY1,8,2);
      $calcdate = $calcdate3."/".$calcdate2."/".$calcdate1;

      $calcdate12 = substr($DMY2,0,4); $calcdate22 = substr($DMY2,5,2); $calcdate32 = substr($DMY2,8,2);
      $calcdate2 = $calcdate32."/".$calcdate22."/".$calcdate12;

      // if ($_POST["BR_ID"] != '') { $BR_ID = "AND a.BR_ID = $BR_IDx"; }
      // else { $BR_ID = ''; }
      //
      // if ($_POST["zone_code"] != '') { $zone_code = "AND c.szone_code = $zone_codex"; }
      // else { $zone_code = ''; }
      //
      // if ($_POST["pr_code"] != '') { $pr_code = "AND b.PR_CODE = $pr_codex"; }
      // else { $pr_code = ''; }
      //
      // if ($_POST["infofrom"] != '') { $infofrom = "AND a.infofrom = '$infofromx'"; }
      // else { $infofrom = ''; }
      // echo "$infofrom";
      ?>
      <!-- ตาราง -->
      <div class="col-md-12">
        <div class="box box-success">
          <div class="box-header">
            <h3 class="box-title"><p class="text-success">รายงาน LINE ID กลุ่มลูกค้า</p></h3>
            <!-- <p class="text-warning">วันที่ <?php echo $calcdate; ?> ถึงวันที่ <?php echo $calcdate2; ?></p> -->
          </div>
          <div class="box-body">
            <div class="form-group">
              <table id="tabla2" class="table table-striped table-bordered" style="width:100%">
                <thead>
                  <tr>
                    <th rowspan="" style="background-color:black; color:#fff; text-align:center;" width="" rowspan="" ><b>No.</b></th>
                    <th rowspan="" style="background-color:black; color:#fff; text-align:center;" width="" rowspan="" ><b>สาขา</b></th>
                    <th style="background-color:CornflowerBlue; color:#fff; text-align:center;" width="" rowspan="" ><b>ไม่มี LINE ID</b></th>
                    <th style="background-color:green; color:#fff; text-align:center;" width="" rowspan="" ><b>มี LINE ID</b></th>
                    <th style="background-color:yellow; color:#000; text-align:center;" width="" rowspan="" ><b>รวม</b></th>
                  </tr>
                </thead>
                <tbody style="white-space: nowrap">
                  <?php
                  $sql = "SELECT
                    a.br_id as BR_IDa,
                    count(a.ps_id) as PS_IDa,
                    count(b.ps_id) as PS_IDb,
                    sum(b.line_id) as line_id,
                    sum(b.line_id2) as line_id2
                  FROM [HPSDB].[dbo].[hps_ctmain_2020_08] a
                  left outer join(SELECT
                      BR_ID
                      ,PS_ID
                    ,sum(case when (lineid is not null) then 1 else 0 end) as line_id
                    ,sum(case when (lineid is null) then 1 else 0 end) as line_id2
                  FROM [EHONGDB].[dbo].[PERSON] GROUP BY BR_ID,PS_ID) b on (a.ps_id=b.PS_ID)
                  WHERE BILL_DATE BETWEEN '2020-08-01' AND '2020-08-31' and (status<>'X') and (product_id like 'มซ%') and (ct_no like '0%' or ct_no like '2%')
                  GROUP BY a.br_id
                  ORDER BY a.br_id";
                  $rs=$conn->prepare($sql);
                  $rs->execute();
                  $xrow = array();
                  $xrow1 = array();
                  $xrow2 = array();
                  $xrow3 = array();
                    foreach($rs as $row){
                  $xrow[$row['BR_IDa']]=$row['line_id'];
                  $xrow1[$row['BR_IDa']]=$row['line_id2'];
                  $xrow2[$row['BR_IDa']]=$row['PS_IDb'];
                  $xrow3[$row['BR_IDa']]=$row['PS_IDa'];
                  };
                  $sql = "SELECT * FROM EHONGDB.dbo.BRANCH WHERE BR_NAME NOT IN ('รวมทุกสาขา') ORDER BY BR_ID";
                  // echo "$sql";
                  $select=$conn->prepare($sql);
                  $select->execute();
                  $item = 1;
                  while($fetch=$select->fetch()){
                    $dcnt = isset($xrow[$fetch['BR_ID']]) ? $xrow[$fetch['BR_ID']] : "0";
                    $xrows = array_sum($xrow);
                    $dcnt1 = isset($xrow1[$fetch['BR_ID']]) ? $xrow1[$fetch['BR_ID']] : "0";
                    $xrows1 = array_sum($xrow1);
                    $dcnt2 = isset($xrow2[$fetch['BR_ID']]) ? $xrow2[$fetch['BR_ID']] : "0";
                    $xrows2 = array_sum($xrow2);
                    $dcnt3 = isset($xrow3[$fetch['BR_ID']]) ? $xrow3[$fetch['BR_ID']] : "0";
                    $xrows3 = array_sum($xrow3);
                  ?>
                  <tr>
                    <td style="background-color:; color:; text-align:center;"><?= number_format($item)."."?></td>
                    <td style="background-color:; color:;"><?php echo $fetch['BR_ID']." : ".$fetch['BR_NAME']; ?></td>
                    <!-- <td style="background-color:; color:;"></td>
                    <td style="background-color:; color:;"></td> -->
                    <!-- <td style="background-color:; color:; text-align:center;"><b><?php echo number_format($dcnt3)?></b></td> -->
                    <td style="background-color:; color:#; text-align:right;"><b> <a href="linezoom_1.php?BR_ID=<?php echo $fetch['BR_ID'];?>&DMY1=<?php echo $DMY1;?>&DMY2=<?php echo $DMY2;?>&LINEX=2" style="color:CornflowerBlue;" target="_blank"><?php echo number_format($dcnt1);?></a></b></td>
                    <td style="background-color:; color:#fff; text-align:right;"><b> <a href="linezoom_1.php?BR_ID=<?php echo $fetch['BR_ID'];?>&DMY1=<?php echo $DMY1;?>&DMY2=<?php echo $DMY2;?>&LINEX=1" style="color:green;" target="_blank"> <?php echo number_format($dcnt); ?></a></b></td>
                    <td style="background-color:; color:; text-align:center;"><b> <a href="linezoom_1.php?BR_ID=<?php echo $fetch['BR_ID'];?>&DMY1=<?php echo $DMY1;?>&DMY2=<?php echo $DMY2;?>&LINEX=3" style="color:#000;" target="_blank"><?php echo number_format($dcnt2); ?></a></b></td>
                  </tr>
                   <?php $item++; } ?>
                </tbody>
                <tfoot>
                  <td colspan="2" style="background-color:black; color:#fff; text-align:center;"><b>รวม</b></td>
                  <!-- <td style="background-color:CornflowerBlue; color:;"></td>
                  <td style="background-color:green; color:;"></td> -->
                  <!-- <td colspan="" style="background-color:yellow; color:#; text-align:center;"><a href="linezoom_1.php?BR_ID=<?php echo $fetch['BR_ID'];?>&DMY1=<?php echo $DMY1;?>&DMY2=<?php echo $DMY2;?>&LINEX=6" style="color:#000;" target="_blank"><b><?php echo number_format($xrows3); ?></b></a></td> -->
                  <td style="background-color:CornflowerBlue; color:; text-align:center;"><a href="linezoom_1.php?BR_ID=<?php echo $fetch['BR_ID'];?>&DMY1=<?php echo $DMY1;?>&DMY2=<?php echo $DMY2;?>&LINEX=5" style="color:#fff;" target="_blank"><B><?php echo number_format($xrows1); ?></B></a></td>
                  <td style="background-color:green; color:; text-align:center;"><a href="linezoom_1.php?BR_ID=<?php echo $fetch['BR_ID'];?>&DMY1=<?php echo $DMY1;?>&DMY2=<?php echo $DMY2;?>&LINEX=4" style="color:#fff;" target="_blank"><B><?php echo number_format($xrows); ?></B></a></td>
                  <td colspan="" style="background-color:yellow; color:#; text-align:center;"><a href="linezoom_1.php?BR_ID=<?php echo $fetch['BR_ID'];?>&DMY1=<?php echo $DMY1;?>&DMY2=<?php echo $DMY2;?>&LINEX=6" style="color:#000;" target="_blank"><b><?php echo number_format($xrows2); ?></b></a></td>
                </tfoot>
            </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>
<?php include '3datatable.php' ?>
