<?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 = "";
}
$dateab = date('Y-m-d');
// สาขาค้างรับ
$sql = "SELECT
$SLnumber,
count(a.CT_NO) as CT_NO
from ehongdb.dbo.reg_billdatadocument a
left outer join (select bill_no as xbill_no,ps_name,brand_name,serial_no,pserial_no,model_no,color_name,chkhint0 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)
where (status<>'V') and (fdoc_no<>'N4')
and (senttype='1') and (sentbrdate is not null)  and (fndate is not null) and (bracpdate is null)
$bbr_id
$sszone_id
$zzstaft_id
group by $SLnumber2
order by $SLnumber2";
// echo $sql;
$rs=$conn->prepare($sql);
$rs->execute();
$number_1 = array();
  foreach($rs as $row){
$number_1[$row['NUMBERS']]=$row['CT_NO'];
};

// ลูกค้าค้างรับ
$sql = "SELECT
$SLnumber,
count(a.CT_NO) as CT_NO
from ehongdb.dbo.reg_billdatadocument a
left outer join (select bill_no as xbill_no,ps_name,brand_name,serial_no,pserial_no,model_no,color_name,chkhint0 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)
where (status<>'V') and (fdoc_no<>'N4')
and (senttype='1') and (sentbrdate is not null)  and (fndate is not null) and (bracpdate is not null) and (custacpdate is null)
$bbr_id
$sszone_id
$zzstaft_id
group by $SLnumber2
order by $SLnumber2";
// echo $sql;
$rs=$conn->prepare($sql);
$rs->execute();
$number_2 = array();
  foreach($rs as $row){
$number_2[$row['NUMBERS']]=$row['CT_NO'];
};

// ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
// ลูกค้าค้างรับ < 45 วัน
$sql = "SELECT
$SLnumber,
count(a.CT_NO) as CT_NO
from ehongdb.dbo.reg_billdatadocument a
left outer join (select bill_no as xbill_no,ps_name,brand_name,serial_no,pserial_no,model_no,color_name,chkhint0 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)
where (status<>'V') and (fdoc_no<>'N4')
and (senttype='1') and (sentbrdate is not null)  and (fndate is not null) and (bracpdate is not null) and (custacpdate is null)
and (datediff(day,a.bracpdate,'$dateab') < 45 )
$bbr_id
$sszone_id
$zzstaft_id
group by $SLnumber2
order by $SLnumber2";
// echo $sql;
$rs=$conn->prepare($sql);
$rs->execute();
$number_245a = array();
  foreach($rs as $row){
$number_245a[$row['NUMBERS']]=$row['CT_NO'];
};
// ลูกค้าค้างรับ < 45 วัน
$sql = "SELECT
$SLnumber,
count(a.CT_NO) as CT_NO
from ehongdb.dbo.reg_billdatadocument a
left outer join (select bill_no as xbill_no,ps_name,brand_name,serial_no,pserial_no,model_no,color_name,chkhint0 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)
where (status<>'V') and (fdoc_no<>'N4')
and (senttype='1') and (sentbrdate is not null)  and (fndate is not null) and (bracpdate is not null) and (custacpdate is null)
and (datediff(day,a.bracpdate,'$dateab') <= 45 )
$bbr_id
$sszone_id
$zzstaft_id
group by $SLnumber2
order by $SLnumber2";
// echo $sql;
$rs=$conn->prepare($sql);
$rs->execute();
$number_245a = array();
  foreach($rs as $row){
$number_245a[$row['NUMBERS']]=$row['CT_NO'];
};
// ลูกค้าค้างรับ > 45 วัน
$sql = "SELECT
$SLnumber,
count(a.CT_NO) as CT_NO
from ehongdb.dbo.reg_billdatadocument a
left outer join (select bill_no as xbill_no,ps_name,brand_name,serial_no,pserial_no,model_no,color_name,chkhint0 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)
where (status<>'V') and (fdoc_no<>'N4')
and (senttype='1') and (sentbrdate is not null)  and (fndate is not null) and (bracpdate is not null) and (custacpdate is null)
and (datediff(day,a.bracpdate,'$dateab') > 45 )
$bbr_id
$sszone_id
$zzstaft_id
group by $SLnumber2
order by $SLnumber2";
// echo $sql;
$rs=$conn->prepare($sql);
$rs->execute();
$number_245b = array();
  foreach($rs as $row){
$number_245b[$row['NUMBERS']]=$row['CT_NO'];
};
// ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

// สาขาค้างรับ ไฟแนนซ์
$sql = "SELECT
$SLnumber3,
count(a.CT_NO) as CT_NO
from ehongdb.dbo.reg_billdatadocument a
left outer join (select bill_no as xbill_no,ps_name,brand_name,serial_no,pserial_no,model_no,color_name,chkhint0 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)
where (status<>'V') and (fdoc_no<>'N4')
and  (senttype='3') and (sentbrdate is not null)  and (fndate is not null) and (bracpdate is null)
$bbr_id
$sszone_id
$zzstaft_id
";
// echo $sql;
$rs=$conn->prepare($sql);
$rs->execute();
$number_3 = array();
  foreach($rs as $row){
$number_3[$row['NUMBERS']]=$row['CT_NO'];
};

// ลูกค้าค้างรับ ไฟแนนซ์
$sql = "SELECT
$SLnumber3,
count(a.CT_NO) as CT_NO
from ehongdb.dbo.reg_billdatadocument a
left outer join (select bill_no as xbill_no,ps_name,brand_name,serial_no,pserial_no,model_no,color_name,chkhint0 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)
where (status<>'V') and (fdoc_no<>'N4')
and  (senttype='3') and (sentbrdate is not null)  and (fndate is not null) and (bracpdate is not null) and (custacpdate is null)
$bbr_id
$sszone_id
$zzstaft_id
";
// echo $sql;
$rs=$conn->prepare($sql);
$rs->execute();
$number_4 = array();
  foreach($rs as $row){
$number_4[$row['NUMBERS']]=$row['CT_NO'];
};

// ทะเบียนตรวจสอบไม่ผ่าน --- ตรวจใบเสร็จไม่ผ่าน
$sql = "SELECT
$SLnumber,
count(a.CT_NO) as CT_NO
from ehongdb.dbo.reg_bill a
left outer join (select emp_id as emp_xid,fname from ehongdb.dbo.employee) c on(a.chkusr0=c.emp_xid)
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.status<>'V') and (chkdate0 is not null) and (chkstatus0='N')
group by $SLnumber2
order by $SLnumber2";
// echo $sql;
$rs=$conn->prepare($sql);
$rs->execute();
$number_a1 = array();
  foreach($rs as $row){
$number_a1[$row['NUMBERS']]=$row['CT_NO'];
};

// ทะเบียนไม่พร้อม --- เอกสารไม่พร้อม
$sql = "SELECT
$SLnumber,
count(a.CT_NO) as CT_NO
from ehongdb.dbo.reg_bill a
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.status<>'V') and (chkdate0 is not null)
and (chkdate1 is not null and chkdate2 is not null and chkstatus2='W')  and (xt_no is not null)
$bbr_id
$sszone_id
$zzstaft_id
group by $SLnumber2
order by $SLnumber2";
// echo $sql;
$rs=$conn->prepare($sql);
$rs->execute();
$number_a2 = array();
  foreach($rs as $row){
$number_a2[$row['NUMBERS']]=$row['CT_NO'];
};

// ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

// ปัจจุบัน
$sql = "SELECT
$SLnumber,
count(a.CT_NO) as CT_NO
from ehongdb.dbo.reg_bill a
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.status<>'V') and (chkdate0 is not null)
and (chkdate1 is not null and chkdate2 is not null and chkstatus2='W')  and (xt_no is not null)
AND YEAR(a.BILL_DATE) = $datetb_1
$bbr_id
$sszone_id
$zzstaft_id
group by $SLnumber2
order by $SLnumber2";
// echo $sql;
$rs=$conn->prepare($sql);
$rs->execute();
$number_a2_x1 = array();
  foreach($rs as $row){
$number_a2_x1[$row['NUMBERS']]=$row['CT_NO'];
};

// ปัจจุบัน-1
$sql = "SELECT
$SLnumber,
count(a.CT_NO) as CT_NO
from ehongdb.dbo.reg_bill a
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.status<>'V') and (chkdate0 is not null)
and (chkdate1 is not null and chkdate2 is not null and chkstatus2='W')  and (xt_no is not null)
AND YEAR(a.BILL_DATE) = $datetb_2
$bbr_id
$sszone_id
$zzstaft_id
group by $SLnumber2
order by $SLnumber2";
// echo $sql;
$rs=$conn->prepare($sql);
$rs->execute();
$number_a2_x2 = array();
  foreach($rs as $row){
$number_a2_x2[$row['NUMBERS']]=$row['CT_NO'];
};

// ปัจจุบัน-2 เอาทั้งหมด
$sql = "SELECT
$SLnumber,
count(a.CT_NO) as CT_NO
from ehongdb.dbo.reg_bill a
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.status<>'V') and (chkdate0 is not null)
and (chkdate1 is not null and chkdate2 is not null and chkstatus2='W')  and (xt_no is not null)
AND YEAR(a.BILL_DATE) <= $datetb_3
$bbr_id
$sszone_id
$zzstaft_id
group by $SLnumber2
order by $SLnumber2";
// echo $sql;
$rs=$conn->prepare($sql);
$rs->execute();
$number_a2_x3 = array();
  foreach($rs as $row){
$number_a2_x3[$row['NUMBERS']]=$row['CT_NO'];
};

// ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

// บรรจุซองค้างส่ง (ซอง)
$sql = "SELECT
$SLnumber4,
count(a.packbarcode) as packbarcode
from ehongdb.dbo.reg_packdata a
left outer join (select packbarcode as pkgcode,count(*) as cnt from ehongdb.dbo.reg_billdatadocument group by packbarcode) b on(a.packbarcode=b.pkgcode)
left outer join (select emp_id,fname from ehongdb.dbo.employee) c on(a.packusr=c.emp_id)
left outer join(SELECT * FROM [EHONGDB].[dbo].[BRANCH]) ax on (ax.BR_ID=a.packbranch)
left outer join(SELECT * FROM [EHONGDB].[dbo].[branch_salezone]) bx on (ax.SZONE_CODE=bx.szone_code)
where (month(packdate)='$date_1') and (year(packdate)='$date_2_1') and (packsentdate is null)
$bbr_id
$sszone_id
$zzstaft_id
group by $SLnumber5
order by $SLnumber5";
// echo $sql;
$rs=$conn->prepare($sql);
$rs->execute();
$number_b1 = array();
  foreach($rs as $row){
$number_b1[$row['NUMBERS']]=$row['packbarcode'];
};

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

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

// รายงาน คืนงาน คืนเงิน // รายงานตีคืนจากขนส่ง
$sql = "SELECT
$SLnumber,
count(a.bill_no) as bill_no
from EHONGDB.dbo.REG_BILLDATA a
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_no is not null) and a.rt_date is not null
and ((fee_no between '00' and '09') or (fee_no in ('15')))
$bbr_id
$sszone_id
$zzstaft_id
group by $SLnumber2
order by $SLnumber2";
// echo $sql;
$rs=$conn->prepare($sql);
$rs->execute();
$number_b2 = array();
  foreach($rs as $row){
$number_b2[$row['NUMBERS']]=$row['bill_no'];
};

// น้อยกว่าปีก่อนทั้งหมด
$sql = "SELECT
$SLnumber,
count(a.bill_no) as bill_no
from EHONGDB.dbo.REG_BILLDATA a
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_no is not null) and a.rt_date is not null
and ((fee_no between '00' and '09') or (fee_no in ('15')))
AND YEAR(a.rt_date) <= $datetb_3
$bbr_id
$sszone_id
$zzstaft_id
group by $SLnumber2
order by $SLnumber2";
// echo $sql;
$rs=$conn->prepare($sql);
$rs->execute();
$number_b2_x1 = array();
  foreach($rs as $row){
$number_b2_x1[$row['NUMBERS']]=$row['bill_no'];
};

// ปีก่อน
$sql = "SELECT
$SLnumber,
count(a.bill_no) as bill_no
from EHONGDB.dbo.REG_BILLDATA a
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_no is not null) and a.rt_date is not null
and ((fee_no between '00' and '09') or (fee_no in ('15')))
AND YEAR(a.rt_date) = $datetb_2
$bbr_id
$sszone_id
$zzstaft_id
group by $SLnumber2
order by $SLnumber2";
// echo $sql;
$rs=$conn->prepare($sql);
$rs->execute();
$number_b2_x2 = array();
  foreach($rs as $row){
$number_b2_x2[$row['NUMBERS']]=$row['bill_no'];
};

// ปีปัจจุบัน
$sql = "SELECT
$SLnumber,
count(a.bill_no) as bill_no
from EHONGDB.dbo.REG_BILLDATA a
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_no is not null) and a.rt_date is not null
and ((fee_no between '00' and '09') or (fee_no in ('15')))
AND YEAR(a.rt_date) = $datetb_1
$bbr_id
$sszone_id
$zzstaft_id
group by $SLnumber2
order by $SLnumber2";
// echo $sql;
$rs=$conn->prepare($sql);
$rs->execute();
$number_b2_x3 = array();
  foreach($rs as $row){
$number_b2_x3[$row['NUMBERS']]=$row['bill_no'];
};


// ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

// ค้างตรวจสาขา

$sqlk1 = "SELECT
  $SLnumber,
	count(a.bill_no) as bill_no
from EHONGDB.dbo.reg_bill a
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_no is not null)
and (status<>'V') and (chkdate0 is not null) and (chkdate1 is null)
and YEAR(bill_date) = $date_2_1 and MONTH(bill_date) = $date_1
$bbr_id
$sszone_id
$zzstaft_id
group by $SLnumber2
order by $SLnumber2";
// echo $sqlk1;
$rsk1=$conn->prepare($sqlk1);
$rsk1->execute();
$numberk1 = array();
  foreach($rsk1 as $rowk1){
$numberk1[$rowk1['NUMBERS']]=$rowk1['bill_no'];
};

?>
