<?php
  if ($br_id != "") {
    $bbr_id = "and ax.BR_ID = '$br_id'";
  }
  else {
    $bbr_id = "";
  }
  if ($zone_codex != "") {
    $sszone_id = "and bx.szone_code = '$zone_codex'";
  }
  else {
    $sszone_id = "";
  }
  if ($zstaff_idx != "") {
    $zzstaft_id = "and bx.zstaff_id = '$zstaff_idx'";
  }
  else {
    $zzstaft_id = "";
  }

  // and (a.ct_no like '0%' or a.ct_no like '2%')
  // and product_name not like '%สามล้อ%' and a.type_sale<>4 and d.SZONE_CODE like '%' and a.br_id like '%'
  // and d.PR_CODE like '%' and a.brand_name like '%'

  $sql = "SELECT
  	$SLnumber1,
  	SUM(b.sale) as sale ,
  	SUM(b.reg) as reg ,
  	SUM(b.berg) as berg ,
  	SUM(b.berg_in) as berg_in ,
  	SUM(b.berg_out) as berg_out ,
  	SUM(b.FNDATE) as FNDATE ,
  	SUM(b.FNDATE_IN) as FNDATE_IN ,
  	SUM(b.SENTBRDATE) as SENTBRDATE ,
  	SUM(b.BRACPDATE) as BRACPDATE ,
  	SUM(b.CUSTACPDATE) as CUSTACPDATE
  FROM [EHONGDB].[dbo].[BRANCH] a
  LEFT OUTER JOIN( SELECT
  	d.SZONE_CODE ,
  	a.br_id ,
  	d.BR_NAME ,
  	count(*) as sale ,
  	SUM(case when b.CT_NO is not null then 1 else 0 end) as reg ,
  	SUM(case when c.CHK_BERG is not null then 1 else 0 end) as berg ,
  	SUM(case when (c.CHK_BERG is not null and MONTH(c.CHK_BERG)=".$_REQUEST['date_1'].") then 1 else 0 end) as berg_in ,
  	SUM(case when (c.CHK_BERG is not null and MONTH(c.CHK_BERG)!=".$_REQUEST['date_1'].") then 1 else 0 end) as berg_out ,
  	SUM(case when e.FNDATE>0 then 1 else 0 end) as FNDATE ,
  	SUM(case when e.FNDATE_IN>0 then 1 else 0 end) as FNDATE_IN ,
  	SUM(case when e.SENTBRDATE>0 then 1 else 0 end) as SENTBRDATE ,
  	SUM(case when e.BRACPDATE>0 then 1 else 0 end) as BRACPDATE ,
  	SUM(case when e.CUSTACPDATE>0 then 1 else 0 end) as CUSTACPDATE
  FROM [HPSDB].[dbo].[hps_ctmain_".$date_2_1."_".str_pad($_REQUEST['date_1'], 2, '0', STR_PAD_LEFT)."] a
  LEFT OUTER JOIN( SELECT CT_NO,BILL_DATE,BILL_NO,g_id FROM ehongdb.dbo.reg_bill ) b on a.ct_no=b.CT_NO
  LEFT OUTER JOIN( SELECT DISTINCT BILL_NO,CHK_BERG FROM EHONGDB.dbo.reg_billdata WHERE CHK_BERG is not null ) c on b.BILL_NO=c.BILL_NO
  LEFT OUTER JOIN( SELECT [BR_ID],[BR_NAME],[SZONE_CODE],[PR_CODE] FROM [EHONGDB].[dbo].[BRANCH] ) d on a.br_id=d.BR_ID
  LEFT OUTER JOIN( SELECT
  	BILL_NO ,
  	SUM(case when FNDATE is not null then 1 else 0 end) as FNDATE ,
  	SUM(case when YEAR(FNDATE)=".$date_2_1." and MONTH(FNDATE)=".$_REQUEST['date_1']." then 1 else 0 end) as FNDATE_IN ,
  	SUM(case when SENTBRDATE is not null then 1 else 0 end) as SENTBRDATE ,
  	SUM(case when BRACPDATE is not null then 1 else 0 end) as BRACPDATE ,
  	SUM(case when CUSTACPDATE is not null then 1 else 0 end) as CUSTACPDATE
  FROM EHONGDB.dbo.REG_BILLDATADOCUMENT GROUP BY BILL_NO ) e on b.BILL_NO=e.BILL_NO
  where (status<>'X') and (product_id like 'มซ%')
  and ".($_REQUEST['bike_condition']=="%"?"(a.ct_no like '0%' or a.ct_no like '2%')":"a.ct_no like '".$_REQUEST['bike_condition']."%'")."
  and product_name not like '%สามล้อ%' and a.type_sale<>4 and d.SZONE_CODE like '$zone_codex%' and a.br_id like '$br_id%'
  and d.PR_CODE like '".$_REQUEST['province']."%' and a.brand_name like '".$_REQUEST['brand']."%'
  group by d.SZONE_CODE,a.br_id,d.BR_NAME ) b on a.BR_ID=b.br_id
  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)
  WHERE a.SZONE_CODE like '$zone_codex%' and a.BR_ID like '$br_id%' and bx.zstaff_id like '$zstaff_idx%' and a.PR_CODE like '".$_REQUEST['province']."%'
  GROUP by $SLnumber2
  ORDER by $SLnumber2";
  // echo $sql;
  $rs=$conn->prepare($sql);
  $rs->execute();
  $reg = array();
  $berg = array();
  $berg_in = array();
  $berg_out = array();
  $FNDATE = array();
  $FNDATE_IN = array();
  $SENTBRDATE = array();
  $BRACPDATE = array();
  $CUSTACPDATE = array();
    foreach($rs as $row){
    $sale[$row['NUMBERS']]=$row['sale'];
    $reg[$row['NUMBERS']]=$row['reg'];
    $berg[$row['NUMBERS']]=$row['berg'];
    $berg_in[$row['NUMBERS']]=$row['berg_in'];
    $berg_out[$row['NUMBERS']]=$row['berg_out'];
    $FNDATE[$row['NUMBERS']]=$row['FNDATE'];
    $FNDATE_IN[$row['NUMBERS']]=$row['FNDATE_IN'];
    $SENTBRDATE[$row['NUMBERS']]=$row['SENTBRDATE'];
    $BRACPDATE[$row['NUMBERS']]=$row['BRACPDATE'];
    $CUSTACPDATE[$row['NUMBERS']]=$row['CUSTACPDATE'];
  };

  $m = $date_1;
  $y = $date_2_1;

  $sum_date_1 = $y."-".$m."-01";
  $sum_date_2 = date('Y-m-t',strtotime($y."-".$m."-".'01'));

  $sqlsale = "SELECT
    $SLnumber1,
    count(b.ps_id) as sale
  FROM [HPSDB].[dbo].[hps_ctmain_".$date_2_1."_".str_pad($_REQUEST['date_1'], 2, '0', STR_PAD_LEFT)."] 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 * 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)
  WHERE BILL_DATE BETWEEN '$sum_date_1' AND '$sum_date_2' and (status<>'X')
  and ".($_REQUEST['bike_condition']=="%"?"(a.ct_no like '0%' or a.ct_no like '2%')":"a.ct_no like '".$_REQUEST['bike_condition']."%'")."
  and ax.SZONE_CODE like '$zone_codex%' and ax.BR_ID like '$br_id%' and bx.zstaff_id like '$zstaff_idx%' and ax.PR_CODE like '".$_REQUEST['province']."%'
  and a.brand_name like '".$_REQUEST['brand']."%'
  GROUP by $SLnumber2
  ORDER by $SLnumber2";
  // echo $sqlsale;
  $rs=$conn->prepare($sqlsale);
  $rs->execute();
  $sale = array();
  foreach($rs as $row){
    $sale[$row['NUMBERS']]=$row['sale'];
  }

  if($_REQUEST['refdata']=="bill"){
    $sql = "SELECT
      $SLnumber1,
    	SUM(b.reg) as reg ,
    	SUM(b.berg) as berg ,
    	SUM(b.berg_in) as berg_in ,
    	SUM(b.berg_out) as berg_out ,
    	SUM(b.FNDATE) as FNDATE ,
    	SUM(b.FNDATE_IN) as FNDATE_IN ,
    	SUM(b.SENTBRDATE) as SENTBRDATE ,
    	SUM(b.BRACPDATE) as BRACPDATE ,
    	SUM(b.CUSTACPDATE) as CUSTACPDATE
    FROM [EHONGDB].[dbo].[BRANCH] a
      LEFT OUTER JOIN(
        SELECT
          d.SZONE_CODE
          ,a.BR_ID
          ,d.BR_NAME
          ,count(a.BILL_NO) as reg
          ,SUM(case when c.CHK_BERG is not null then 1 else 0 end) as berg
          ,SUM(case when (c.CHK_BERG is not null and MONTH(c.CHK_BERG)=".$_REQUEST['date_1'].") then 1 else 0 end) as berg_in
          ,SUM(case when (c.CHK_BERG is not null and MONTH(c.CHK_BERG)!=".$_REQUEST['date_1'].") then 1 else 0 end) as berg_out
          ,SUM(case when e.FNDATE>0 then 1 else 0 end) as FNDATE
          ,SUM(case when e.FNDATE_IN>0 then 1 else 0 end) as FNDATE_IN
          ,SUM(case when e.SENTBRDATE>0 then 1 else 0 end) as SENTBRDATE
          ,SUM(case when e.BRACPDATE>0 then 1 else 0 end) as BRACPDATE
          ,SUM(case when e.CUSTACPDATE>0 then 1 else 0 end) as CUSTACPDATE
    FROM ehongdb.dbo.reg_bill a
      LEFT OUTER JOIN(
        SELECT DISTINCT BILL_NO,CHK_BERG
        FROM EHONGDB.dbo.reg_billdata
        WHERE CHK_BERG is not null
      ) c on a.BILL_NO=c.BILL_NO
      LEFT OUTER JOIN(
        SELECT [BR_ID],[BR_NAME],[SZONE_CODE],[PR_CODE]
    FROM [EHONGDB].[dbo].[BRANCH]
      ) d on a.BR_ID=d.BR_ID
      LEFT OUTER JOIN(
        SELECT
          BILL_NO
          ,SUM(case when FNDATE is not null then 1 else 0 end) as FNDATE
          ,SUM(case when YEAR(FNDATE)=".$date_2_1." and MONTH(FNDATE)=".$_REQUEST['date_1']." then 1 else 0 end) as FNDATE_IN
          ,SUM(case when SENTBRDATE is not null then 1 else 0 end) as SENTBRDATE
          ,SUM(case when BRACPDATE is not null then 1 else 0 end) as BRACPDATE
          ,SUM(case when CUSTACPDATE is not null then 1 else 0 end) as CUSTACPDATE
        FROM EHONGDB.dbo.REG_BILLDATADOCUMENT
        GROUP BY BILL_NO
      ) e on a.BILL_NO=e.BILL_NO
    where (status<>'V') and YEAR(a.bill_date)=$date_2_1 and MONTH(a.bill_date)=".$_REQUEST['date_1']."
    and ".($_REQUEST['bike_condition']=="%"?"(a.ct_no like '0%' or a.ct_no like '2%')":"a.ct_no like '".$_REQUEST['bike_condition']."%'")."
    and d.SZONE_CODE like '$zone_codex%' and a.BR_ID like '$br_id%' and d.PR_CODE like '".$_REQUEST['province']."%'
    and a.brand_name like '".$_REQUEST['brand']."%'
    group by d.SZONE_CODE,a.BR_ID,d.BR_NAME) b on a.BR_ID=b.BR_ID
    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)
    WHERE a.SZONE_CODE like '$zone_codex%' and a.BR_ID like '$br_id%' and bx.zstaff_id like '$zstaff_idx%' and a.PR_CODE like '".$_REQUEST['province']."%'
    GROUP by $SLnumber2
    ORDER by $SLnumber2";
    // echo $sql;
    $rs=$conn->prepare($sql);
    $rs->execute();
    $reg = array();
    $berg = array();
    $berg_in = array();
    $berg_out = array();
    $FNDATE = array();
    $FNDATE_IN = array();
    $SENTBRDATE = array();
    $BRACPDATE = array();
    $CUSTACPDATE = array();
      foreach($rs as $row){
      $reg[$row['NUMBERS']]=$row['reg'];
      $berg[$row['NUMBERS']]=$row['berg'];
      $berg_in[$row['NUMBERS']]=$row['berg_in'];
      $berg_out[$row['NUMBERS']]=$row['berg_out'];
      $FNDATE[$row['NUMBERS']]=$row['FNDATE'];
      $FNDATE_IN[$row['NUMBERS']]=$row['FNDATE_IN'];
      $SENTBRDATE[$row['NUMBERS']]=$row['SENTBRDATE'];
      $BRACPDATE[$row['NUMBERS']]=$row['BRACPDATE'];
      $CUSTACPDATE[$row['NUMBERS']]=$row['CUSTACPDATE'];
    };
  }
?>
