<?php
  if (isset($_POST['brs_1']) != "") {
    $BSZ_NAME = "สาขา";
    $BSZ = "a.BR_ID,a.BR_NAME";
    $GNUMBER = "a.br_id";
  }
  if (isset($_POST['brs_2']) != "") {
    $BSZ_NAME = "เขต";
    $BSZ = "b.szone_code,b.szone_name";
    $GNUMBER = "aa1.SZONE_CODE";
  }
  if (isset($_POST['brs_3']) != "") {
    $BSZ_NAME = "ภาค";
    $BSZ = "b.zstaff_id,b.zstaff_name";
    $GNUMBER = "aa2.zstaff_id";
  }

  if ($br_id != "") {
    $BSZ_B = "AND a.BR_ID = '$br_id'";
    $bid = "amd a.BR_ID = '$br_id'";
    $bbr_id = "and a.BR_ID = '$br_id'";
  } else {
    $BSZ_B = "";
    $bid = "";
    $bbr_id = "";
  }
  if ($zone_codex != "") {
    $BSZ_S = "AND b.szone_code = '$zone_codex'";
    $szc = "and bx.szone_code = '$zone_codex' ";
    $sszone_id = "and aa1.szone_code = '$zone_codex'";
  } else {
    $BSZ_S = "AND b.szone_code IS NOT NULL";
    $szc = "";
    $sszone_id = "";
  }
  if ($zstaff_idx != "") {
    $BSZ_Z = "AND b.zstaff_id = '$zstaff_idx'";
    $zid = "and bx.zstaff_id = '$zstaff_idx' ";
    $zzstaft_id = "and aa2.zstaff_id = '$zstaff_idx'";
  } else {
    $BSZ_Z = "AND b.zstaff_id IS NOT NULL";
    $zid = "";
    $zzstaft_id = "";
  }

  $sql_code = "SELECT
  	$GNUMBER AS NUMBER,
    count(b.ps_id) AS PSID_SALE,
  	count(d.YM) AS YM,
  	(count(b.ps_id)-count(d.YM)) AS NM,
  	count(e.YRG) AS YRG,
  	(count(d.YM)-count(e.YRG)) AS NRG
  FROM [EHONGDB].[dbo].[hps_ctmain] a
  LEFT OUTER JOIN(SELECT
  	BR_ID,
  	PS_ID
  FROM [EHONGDB].[dbo].[PERSON] GROUP BY BR_ID, PS_ID) b on (a.ps_id=b.PS_ID)
  LEFT OUTER JOIN(SELECT
  	BILL_NO,
  	count(case when (BRAND_NAME  NOT IN ('AIMA','MODDAENG','EM','SAGASONIC') AND model_name NOT IN ('RK110ZH-S')) then 1 else 0 end) as YM
  FROM [EHONGDB].[dbo].[hps_ctmain] WHERE BRAND_NAME NOT IN ('AIMA','MODDAENG','EM','SAGASONIC') AND model_name NOT IN ('RK110ZH-S') GROUP BY BILL_NO, BRAND_NAME) d on (a.bill_no=d.BILL_NO)
  LEFT OUTER JOIN(SELECT
	 BILL_NO,
	  count(case when (BRAND_NAME NOT IN ('AIMA','MODDAENG','EM','SAGASONIC') AND model_name NOT IN ('RK110ZH-S') AND regbill_no IS NOT NULL) then 1 else 0 end) as YRG
  FROM [EHONGDB].[dbo].[hps_ctmain] WHERE BRAND_NAME NOT IN ('AIMA','MODDAENG','EM','SAGASONIC') AND model_name NOT IN ('RK110ZH-S') AND regbill_no IS NOT NULL GROUP BY BILL_NO, BRAND_NAME) e on (a.bill_no=e.BILL_NO)
  LEFT OUTER JOIN(SELECT
  	BR_ID,
  	SZONE_CODE
  FROM [EHONGDB].[dbo].[BRANCH]) aa1 on (aa1.BR_ID=a.BR_ID)
  LEFT OUTER JOIN(SELECT
  	SZONE_CODE,
  	zstaff_id
  FROM [EHONGDB].[dbo].[branch_salezone]) aa2 on (aa2.SZONE_CODE=aa1.SZONE_CODE)
  WHERE MONTH(a.BILL_DATE) = $date_1 AND YEAR(a.BILL_DATE) = $date_2_1
  AND (a.status<>'X') AND (a.product_id like 'มซ%') AND (a.ct_no like '0%' or a.ct_no like '2%')
  $bbr_id $sszone_id $zzstaft_id
  GROUP BY $GNUMBER
  ORDER BY $GNUMBER";
  // echo $sql_code;
  $rs_code=$conn->prepare($sql_code);
  $rs_code->execute();
  $PSID_SALE = array();
  $YM = array();
  $NM = array();
  $YRG = array();
  $NRG = array();
  foreach($rs_code as $row_code) {
    $PSID_SALE[$row_code['NUMBER']]=$row_code['PSID_SALE'];
    $YM[$row_code['NUMBER']]=$row_code['YM'];
    $NM[$row_code['NUMBER']]=$row_code['NM'];
    $YRG[$row_code['NUMBER']]=$row_code['YRG'];
    $NRG[$row_code['NUMBER']]=$row_code['NRG'];
  };

?>
<div class="col-md-12">
<div class="box box-success">
  <div class="box-header">
    <h3 class="box-title"><p class="text-success">รายงานทะเบียนตรวจสอบ - ใบสั่งทำ <?php echo $BSZ_NAME; ?></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 rowspan="2" style="background-color:black; color:#fff; text-align:center;">NO.</th>
              <th rowspan="2" style="background-color:black; color:#fff; text-align:center;"><?php echo $BSZ_NAME; ?></th>
              <th colspan="3" style="background-color:black; color:#fff; text-align:center;">ยอดขาย</th>
              <th colspan="3" style="background-color:black; color:#fff; text-align:center;">รถจดทะเบียน - ใบสั่งทำทะเบียน</th>
            </tr>
            <tr>
              <th style="background-color:black; color:#fff; text-align:center;">ยอดขาย</th>
              <th style="background-color:green; color:#fff; text-align:center;">รถจดทะเบียน</th>
              <th style="background-color:black; color:#fff; text-align:center;">ไม่ต้องจดทะเบียน</th>

              <th style="background-color:green; color:#fff; text-align:center;">มีใบเสร็จ</th>
              <th style="background-color:black; color:#fff; text-align:center;">%</th>
              <th style="background-color:red; color:#fff; text-align:center;">ยังไม่มีใบเสร็จ</th>
            </tr>
          </thead>
          <tbody style="white-space: nowrap">
            <?php
            $sql = "SELECT $BSZ 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 != '%' $BSZ_B $BSZ_S $BSZ_Z
              GROUP BY $BSZ
              ORDER BY $BSZ";
              // echo $sql;
            $select=$conn_z->prepare($sql);
            $select->execute();
            $item = 0;
            $PSID_SALE_SUM = 0;
            $YM_SUM = 0;
            $NM_SUM = 0;
            $YRG_SUM = 0;
            $NRG_SUM = 0;
            $SPSM = 0;
            while($fetch=$select->fetch()){
              $item++;
              if ($br_id != "") {
                $BR_IDS = $br_id;
              }
              else {
                $BR_IDS = $fetch['BR_ID'];
              }
              if ($zone_codex != "") {
                $ZSONE_CODES = $zone_codex;
              }
              else {
                $ZSONE_CODES = $fetch['szone_code'];
              }
              if ($zstaff_idx != "") {
                $ZSTAFFS = $zstaff_idx;
              }
              else {
                $ZSTAFFS = $fetch['zstaff_id'];
              }
            ?>
            <tr>
              <td style="background-color:; color:; text-align:;"><?php echo number_format($item); ?>.</td>
              <?php if (isset($_POST['brs_1']) != "") {
                $PSID_SALE_ECHO = isset($PSID_SALE[$fetch['BR_ID']]) ? $PSID_SALE[$fetch['BR_ID']] : "0";
                $YM_ECHO = isset($YM[$fetch['BR_ID']]) ? $YM[$fetch['BR_ID']] : "0";
                $NM_ECHO = isset($NM[$fetch['BR_ID']]) ? $NM[$fetch['BR_ID']] : "0";
                $YRG_ECHO = isset($YRG[$fetch['BR_ID']]) ? $YRG[$fetch['BR_ID']] : "0";
                $NRG_ECHO = isset($NRG[$fetch['BR_ID']]) ? $NRG[$fetch['BR_ID']] : "0";

                if ($YM_ECHO == "0") {
                  $PSM = "0";
                } else {
                  $PSM = ($YRG_ECHO/$YM_ECHO)*100;
                }
              ?>
                <td style="background-color:; color:; text-align:;"><?php echo $fetch['BR_ID']." : ".$fetch['BR_NAME']; ?></td>
              <?php } if (isset($_POST['brs_2']) != "") {
                $PSID_SALE_ECHO = isset($PSID_SALE[$fetch['szone_code']]) ? $PSID_SALE[$fetch['szone_code']] : "0";
                $YM_ECHO = isset($YM[$fetch['szone_code']]) ? $YM[$fetch['szone_code']] : "0";
                $NM_ECHO = isset($NM[$fetch['szone_code']]) ? $NM[$fetch['szone_code']] : "0";
                $YRG_ECHO = isset($YRG[$fetch['szone_code']]) ? $YRG[$fetch['szone_code']] : "0";
                $NRG_ECHO = isset($NRG[$fetch['szone_code']]) ? $NRG[$fetch['szone_code']] : "0";

                if ($YM_ECHO == "0") {
                  $PSM = "0";
                } else {
                  $PSM = ($YRG_ECHO/$YM_ECHO)*100;
                }
              ?>
                <td style="background-color:; color:; text-align:;"><?php echo $fetch['szone_code']." : ".$fetch['szone_name']; ?></td>
              <?php } if (isset($_POST['brs_3']) != "") {
                $PSID_SALE_ECHO = isset($PSID_SALE[$fetch['zstaff_id']]) ? $PSID_SALE[$fetch['zstaff_id']] : "0";
                $YM_ECHO = isset($YM[$fetch['zstaff_id']]) ? $YM[$fetch['zstaff_id']] : "0";
                $NM_ECHO = isset($NM[$fetch['zstaff_id']]) ? $NM[$fetch['zstaff_id']] : "0";
                $YRG_ECHO = isset($YRG[$fetch['zstaff_id']]) ? $YRG[$fetch['zstaff_id']] : "0";
                $NRG_ECHO = isset($NRG[$fetch['zstaff_id']]) ? $NRG[$fetch['zstaff_id']] : "0";

                if ($YM_ECHO == "0") {
                  $PSM = "0";
                } else {
                  $PSM = ($YRG_ECHO/$YM_ECHO)*100;
                }
              ?>
                <td style="background-color:; color:; text-align:;"><?php echo $fetch['zstaff_id']." : ".$fetch['zstaff_name']; ?></td>
              <?php } ?>
              <td style="background-color:; color:#; text-align:right;"><a href="reg_sreporttable.php?BR_IDS=<?php echo $BR_IDS; ?>&ZSONE_CODES=<?php echo $ZSONE_CODES; ?>&ZSTAFFS=<?php echo $ZSTAFFS; ?>&ST=SE&MONTH=<?php echo $date_1; ?>&YEAR=<?php echo $date_2_1; ?>" target="_blank" style="color:black;"><?php echo number_format($PSID_SALE_ECHO); ?></a></td>
              <td style="background-color:#; color:green; text-align:right;"><a href="reg_sreporttable.php?BR_IDS=<?php echo $BR_IDS; ?>&ZSONE_CODES=<?php echo $ZSONE_CODES; ?>&ZSTAFFS=<?php echo $ZSTAFFS; ?>&ST=YM&MONTH=<?php echo $date_1; ?>&YEAR=<?php echo $date_2_1; ?>" target="_blank" style="color:green;"><?php echo number_format($YM_ECHO); ?></a></td>
              <td style="background-color:; color:#; text-align:right;"><a href="reg_sreporttable.php?BR_IDS=<?php echo $BR_IDS; ?>&ZSONE_CODES=<?php echo $ZSONE_CODES; ?>&ZSTAFFS=<?php echo $ZSTAFFS; ?>&ST=NM&MONTH=<?php echo $date_1; ?>&YEAR=<?php echo $date_2_1; ?>" target="_blank" style="color:black;"><?php echo number_format($NM_ECHO); ?></a></td>

              <td style="background-color:#; color:green; text-align:right;"><a href="reg_sreporttable.php?BR_IDS=<?php echo $BR_IDS; ?>&ZSONE_CODES=<?php echo $ZSONE_CODES; ?>&ZSTAFFS=<?php echo $ZSTAFFS; ?>&ST=YRG&MONTH=<?php echo $date_1; ?>&YEAR=<?php echo $date_2_1; ?>" target="_blank" style="color:green;"><?php echo number_format($YRG_ECHO); ?></a></td>
              <th style="background-color:#; color:; text-align:right;"><?php echo number_format($PSM,2); ?>%</th>
              <td style="background-color:#; color:red; text-align:right;"><a href="reg_sreporttable.php?BR_IDS=<?php echo $BR_IDS; ?>&ZSONE_CODES=<?php echo $ZSONE_CODES; ?>&ZSTAFFS=<?php echo $ZSTAFFS; ?>&ST=NRG&MONTH=<?php echo $date_1; ?>&YEAR=<?php echo $date_2_1; ?>" target="_blank" style="color:red;"><?php echo number_format($NRG_ECHO); ?></a></td>
            </tr>
            <?php
              $PSID_SALE_SUM  += $PSID_SALE_ECHO;
              $YM_SUM  += $YM_ECHO;
              $NM_SUM  += $NM_ECHO;
              $YRG_SUM  += $YRG_ECHO;
              $NRG_SUM  += $NRG_ECHO;
              $SPSM = ($YRG_SUM/$YM_SUM)*100;
            } ?>
          </tbody>
          <tfoot>
            <tr>
              <th colspan="2" style="background-color:black; color:#fff; text-align:center;">รวม</th>
              <th style="background-color:black; color:#fff; text-align:right;;"><a href="reg_sreporttable.php?BR_IDS=<?php echo $br_id; ?>&ZSONE_CODES=<?php echo $zone_codex; ?>&ZSTAFFS=<?php echo $zstaff_idx; ?>&ST=SSE&MONTH=<?php echo $date_1; ?>&YEAR=<?php echo $date_2_1; ?>" target="_blank" style="color:#fff;"><?php echo number_format($PSID_SALE_SUM); ?></a></th>
              <th style="background-color:green; color:#fff; text-align:right;;"><a href="reg_sreporttable.php?BR_IDS=<?php echo $br_id; ?>&ZSONE_CODES=<?php echo $zone_codex; ?>&ZSTAFFS=<?php echo $zstaff_idx; ?>&ST=SYM&MONTH=<?php echo $date_1; ?>&YEAR=<?php echo $date_2_1; ?>" target="_blank" style="color:#fff;"><?php echo number_format($YM_SUM); ?></a></th>
              <th style="background-color:black; color:#fff; text-align:right;;"><a href="reg_sreporttable.php?BR_IDS=<?php echo $br_id; ?>&ZSONE_CODES=<?php echo $zone_codex; ?>&ZSTAFFS=<?php echo $zstaff_idx; ?>&ST=SNM&MONTH=<?php echo $date_1; ?>&YEAR=<?php echo $date_2_1; ?>" target="_blank" style="color:#fff;"><?php echo number_format($NM_SUM); ?></a></th>

              <th style="background-color:green; color:#fff; text-align:right;;"><a href="reg_sreporttable.php?BR_IDS=<?php echo $br_id; ?>&ZSONE_CODES=<?php echo $zone_codex; ?>&ZSTAFFS=<?php echo $zstaff_idx; ?>&ST=SYRG&MONTH=<?php echo $date_1; ?>&YEAR=<?php echo $date_2_1; ?>" target="_blank" style="color:#fff;"><?php echo number_format($YRG_SUM); ?></a></th>
              <th style="background-color:black; color:#fff; text-align:right;"><?php echo number_format($SPSM,2); ?></th>
              <th style="background-color:red; color:#fff; text-align:right;;"><a href="reg_sreporttable.php?BR_IDS=<?php echo $br_id; ?>&ZSONE_CODES=<?php echo $zone_codex; ?>&ZSTAFFS=<?php echo $zstaff_idx; ?>&ST=SNRG&MONTH=<?php echo $date_1; ?>&YEAR=<?php echo $date_2_1; ?>" target="_blank" style="color:#fff;"><?php echo number_format($NRG_SUM); ?></a></th>
            </tr>
          </tfoot>
      </table>
    </div>
    </div>
  </div>
</div>
</div>
