<?php

$ct_no = $_REQUEST['ct_no'];

// $ct_no = "257550043";
$output = "";
$outBK = "";

include '_connect.php';

// เก่า
// $sql = "SELECT
// [ct_no] ,[ps_id] ,[ps_name] ,[TI_NAME] ,[PS_FNAME] ,[PS_LNAME] ,[PS_PHONE] ,[PS_MOBILE] ,[CD_DATA] ,[ct_date]
// ,[br_id] ,[sbr_id] ,[price] ,[payamnt] ,[due_amnt] ,[due_count] ,[product_id] ,[product_name] ,[serial_no] ,[status] ,[cnsvrefno]
// ,[cnsvrefcode],[cd_no],[cd_date],[cd_paydate],[ct_nscore],[slowly_amnt]
// FROM [View_To_Table].[dbo].[RCT_MAIN]  where ct_no = '$ct_no'";

// ใหม่
$sql="SELECT TOP 1
  a.ct_no
  ,a.ps_id
  ,a.ps_name
  ,c.TI_NAME
  ,c.PS_FNAME
  ,c.PS_LNAME
  ,c.PS_PHONE
  ,c.PS_MOBILE
  ,c.CD_DATA
  ,a.ct_date
  ,a.br_id
  ,a.sbr_id
  ,a.price
  ,a.payamnt
  ,a.due_amnt
  ,a.due_count
  ,a.product_id
  ,a.product_name
  ,a.serial_no
  ,a.status
  ,a.cnsvrefno
  ,a.cnsvrefcode
  ,b.cd_no
  ,b.cd_date
  ,b.cd_paydate
  ,b.ct_nscore
  ,b.slowly_amnt,b.status as xstatus
FROM [EHONGDB].[dbo].[hps_ctmain] a
    LEFT OUTER JOIN(SELECT ct_no,cd_no,cd_date,cd_paydate,ct_nscore,slowly_amnt,status FROM [EHONGDB].[dbo].[hps_ctdue] where (status is null or status ='D')) b on (a.ct_no=b.ct_no)
    LEFT OUTER JOIN(SELECT PS_ID,TI_NAME,PS_FNAME,PS_LNAME,PS_PHONE,PS_MOBILE,CD_DATA FROM [EHONGDB].[dbo].[PERSON]) c on (a.ps_id=c.PS_ID)
WHERE a.ct_no = '$ct_no'";

$stmt = $dbh->prepare($sql);
$stmt->execute();
// echo $sql;
// $output .= '<form action="qrcode_pay_md2.php" method="POST">
// <p>กรุณากรอกจำนวนเงินที่ต้องการชำระ<p>';
while($row = $stmt->fetch()) {
  ////////////////////////////////////
  // $dueamnt=$row['due_amnt'];
  // $slowlyamnt=$row['slowly_amnt'];
  // $Camount=$dueamnt+$slowlyamnt;
  /////////////////////////////
  $billerid='|010556020330003';
  $psid=$row['ps_id'];
  $dueamnt=$row['due_amnt'];
  $slowlyamnt=$row['slowly_amnt'];
  // -- REF1 -- //
  $ctnoN1 = substr($ct_no ,0,1);
  $ctnoN2 = substr($ct_no ,1,1);
  $ctnoN3 = substr($ct_no ,2,1);
  $ctnoN4 = substr($ct_no ,3,1);
  $ctnoN5 = substr($ct_no ,4,1);
  $ctnoN6 = substr($ct_no ,5,1);
  $ctnoN7 = substr($ct_no ,6,1);
  $ctnoN8 = substr($ct_no ,7,1);
  $ctnoN9 = substr($ct_no ,8,1);
  $ctnoN10 = substr($ct_no ,9,1);
  $ctnoTotal=$ctnoN1+$ctnoN2+$ctnoN3+$ctnoN4+$ctnoN5+$ctnoN6+$ctnoN7+$ctnoN8+$ctnoN9+$ctnoN10+1;
  $digit1 = substr($ctnoTotal ,1,1);
  $log10 = log10($ct_no);
  $digit2 = substr($log10 ,2,1);
  $chkdigit3=strlen($ct_no);
  $sumdigit3 = $chkdigit3+$digit1+$digit2;
  $digit3 = substr($sumdigit3 ,1,1);
  $sumref1 = $digit1.$digit2.$digit3;
  $ref1 = $ct_no.$sumref1;
  // -- END REF1 -- //
  // -- REF2 -- //
  $date=Date('dmY');
  $sumdate1 = substr($date ,0,4);
  $sumdate2 = substr($date ,6,2);
  $sumdate3 = $sumdate1.$sumdate2;
  //$runNum=rand(1111,9999);
  $runNum=sprintf('%04d', rand(1111,9999));
  $rand=strlen($runNum);
  $zero="0";
  if($rand==1)
  {
      $randplus= $zero.$zero.$zero.$runNum;
  }
  else if($rand==2)
  {
      $randplus= $zero.$zero.$runNum;
  }
  else if($rand==3)
  {
      $randplus= $zero.$runNum;
  }
  else
  {
      $randplus= $runNum;
  }
  //$randplus."<br>";
  $totaldatenum=$sumdate3.$randplus;
  $dateN1 = substr($totaldatenum ,0,1);
  $dateN2 = substr($totaldatenum ,1,1);
  $dateN3 = substr($totaldatenum ,2,1);
  $dateN4 = substr($totaldatenum ,3,1);
  $dateN5 = substr($totaldatenum ,4,1);
  $dateN6 = substr($totaldatenum ,5,1);
  $dateN7 = substr($totaldatenum ,6,1);
  $dateN8 = substr($totaldatenum ,7,1);
  $dateN9 = substr($totaldatenum ,8,1);
  $dateN10 = substr($totaldatenum ,9,1);
  $dateTotal=$dateN1+$dateN2+$dateN3+$dateN4+$dateN5+$dateN6+$dateN7+$dateN8+$dateN9+$dateN10;
  $datedigitr1 = substr($dateTotal ,0,1);
  $datedigitr2 = substr($dateTotal ,1,1);
  $sumrefdigit = $datedigitr1+$datedigitr2;
  $Pre2_Digit1_1 = substr($sumrefdigit ,0,1);
  $Pre2_Digit1_2 = substr($sumrefdigit ,1,1);
  $chkPre2_Digit1_2=strlen($Pre2_Digit1_2);
  if($chkPre2_Digit1_2==0){$Pre2_Digit1_2="0";}
  $digit1 = $Pre2_Digit1_1+$Pre2_Digit1_2;
  $totaldatenum2="0.".$totaldatenum;
  $expDate = exp($totaldatenum2);
  $digit2 = substr($expDate ,3,1);
  $PreSquareRoot = $digit1.$digit2;
  $SquareRoot=sqrt($PreSquareRoot);
  $chk_SquareRoot=strlen($SquareRoot);
  if($chk_SquareRoot>1) {
    $digit3 = substr($SquareRoot ,2,1);
  } else {
    $digit3 ="0";
  }
  //$digit3 = substr($SquareRoot ,2,1);
  $preref=$digit1.$digit2.$digit3;
  $ref2=$totaldatenum.$preref;
  $amount=$dueamnt+$slowlyamnt."00";
  $Camount=$dueamnt;
  // echo $Camount;

  $sql_chk = "SELECT TOP 1 ct_no,status FROM [EHONGDB].[dbo].[hps_ctdue]
  WHERE ct_no ='$ct_no' and slowly_amnt is not null and slowly_amnt != '0' order by cd_no DESC";
  $stmt_chk = $dbh->prepare($sql_chk);
  $stmt_chk->execute();
  $row_chk = $stmt_chk->fetch();
  // echo $sql_chk;
  // echo $row_chk['status'];
  if($row_chk['status']=='D') {
    // -- END ตั้งเงื่อนไข -- //
    // -- คำนวณยอดที่ต้องชำระ -- //
    $sql_SD = "SELECT
    a.ct_no ,a.cd_no ,a.cd_date ,a.cd_paydate ,a.ct_nscore ,a.slowly_amnt ,b.due_amnt ,a.cd_payamnt ,b.due_amnt-a.cd_payamnt as due_amntTotal
    ,b.fw_status ,a.status ,b.ct_no ,b.ps_id ,b.ps_name ,b.ct_date ,b.br_id ,b.sbr_id ,b.price ,b.payamnt ,b.due_count ,b.product_id ,b.product_name
    ,b.serial_no ,b.status ,b.cnsvrefno ,b.cnsvrefcode ,c.TI_NAME ,c.PS_FNAME ,c.PS_LNAME ,c.PS_PHONE ,c.PS_MOBILE ,c.CD_DATA
    FROM [EHONGDB].[dbo].[hps_ctdue] a
    left outer join(SELECT
    ct_no ,ps_id ,ps_name ,ct_date ,br_id ,sbr_id ,price ,payamnt ,due_amnt ,due_count ,product_id ,product_name ,serial_no ,status
    ,cnsvrefno ,cnsvrefcode ,fw_status
    FROM [EHONGDB].[dbo].[hps_ctmain]) b on (a.ct_no=b.ct_no)
    left outer join( SELECT PS_ID ,TI_NAME ,PS_FNAME ,PS_LNAME ,PS_PHONE ,PS_MOBILE ,CD_DATA ,img_path FROM [EHONGDB].[dbo].[PERSON]) c on (a.ps_id=c.PS_ID)
    WHERE a.ct_no = '$ct_no' and slowly_amnt is not null and slowly_amnt != '0' and a.status = 'D' order by cd_date asc";
    // echo $sql_SD;
    $stmt_SD = $dbh->prepare($sql_SD);
    $stmt_SD->execute();
    $row_SD = $stmt_SD->fetch();
    $due_amntTotal=$row_SD['due_amntTotal'];
    // $total_Camount=$Camount+$due_amntTotal;
    $total_Camount=$Camount;
    // -- END คำนวณยอดที่ต้องชำระ -- //
    //////////////////////////////
    if($row_SD['status'] == 'D') {
      if($due_amntTotal!=null){
        if($row_SD['fw_status'] != '10') {
            $due_amntTotal+=$row_SD['slowly_amnt'];
            if($row_SD['fw_status'] == '11') {
              // เบี้ยปรับ
              $total_Camount+=$row_SD['slowly_amnt']-$row_SD['cd_payamnt'];
              $outBK .= "เบี้ยปรับ";
              $output .= number_format($row_SD['slowly_amnt'],2);
            } else {
              // ค้างชำระ
              $total_Camount+=$due_amntTotal;
              $outBK .= "ค้างชำระ";
              $output .= number_format($due_amntTotal,2);
            }
        } else {
          $outBK .= "ยอดค้างชำระ";
          $output .= "0.00";
        }
      } else {
        $outBK .= "ยอดค้างชำระ";
        $output .= "0.00";
      }
    } else {
      $outBK .= "ยอดค้างชำระ";
      $output .= "0.00";
    }
  } else {

    $sql_SD = "SELECT a.ct_no ,a.cd_no ,a.cd_date ,a.cd_paydate ,a.ct_nscore ,a.slowly_amnt ,b.due_amnt ,a.cd_payamnt ,b.due_amnt-a.cd_payamnt as due_amntTotal
    ,b.fw_status ,a.status ,a.ct_ngrad ,b.ct_no ,b.ps_id ,b.ps_name ,b.ct_date ,b.br_id ,b.sbr_id ,b.price ,b.payamnt ,b.due_count ,b.product_id ,b.product_name
    ,b.serial_no ,b.cnsvrefno ,b.cnsvrefcode ,c.TI_NAME ,c.PS_FNAME ,c.PS_LNAME ,c.PS_PHONE ,c.PS_MOBILE ,c.CD_DATA
    FROM [EHONGDB].[dbo].[hps_ctdue] a
    left outer join(SELECT
      ct_no ,ps_id ,ps_name ,ct_date ,br_id ,sbr_id ,price ,payamnt ,due_amnt ,due_count ,product_id
      ,product_name ,serial_no ,status ,cnsvrefno ,cnsvrefcode ,fw_status
    FROM [EHONGDB].[dbo].[hps_ctmain]) b on (a.ct_no=b.ct_no)
    left outer join(SELECT
      PS_ID ,TI_NAME ,PS_FNAME ,PS_LNAME ,PS_PHONE ,PS_MOBILE ,CD_DATA ,img_path
    FROM [EHONGDB].[dbo].[PERSON]) c on (a.ps_id=c.PS_ID)
    WHERE a.ct_no = '$ct_no' and slowly_amnt is not null and slowly_amnt != '0' and a.status is null order by cd_date asc";
    $stmt_SD = $dbh->prepare($sql_SD);
    $stmt_SD->execute();
    $row_SD = $stmt_SD->fetch();
    // echo $sql_SD;
    $due_amntTotal = $row_SD['slowly_amnt']+$row_SD['due_amntTotal'];
    // echo $row_SD['slowly_amnt'];
    // echo $row_SD['due_amntTotal'];
    // echo $due_amntTotal;
    while ($row_SD2 = $stmt_SD->fetch()) {
      $due_amntTotal+=$row_SD2['slowly_amnt']+$row_SD2['due_amntTotal'];
    }
    // เบี้ยปรับค่าติดตามหรือเบี้ยปรับอื่นๆ //
    $sql_cflw = "SELECT COUNT(*) FROM [EHONGDB].[dbo].[hps_ctcost] WHERE ct_no = '$ct_no' AND cc_docno IS NULL AND cc_payamnt = '0'";
    $stmt_cflw = $dbh->prepare($sql_cflw);
    $stmt_cflw->execute();
    $row_cflw = $stmt_cflw->fetch();
    $sql_flw = "SELECT ct_no,cc_no,cc_docno,cc_doc,cc_date,cc_amnt,cc_discount,cc_payamnt,cc_type
    FROM [EHONGDB].[dbo].[hps_ctcost] WHERE ct_no = '$ct_no' AND cc_docno IS NULL AND cc_payamnt = '0'";
    // echo $sql_flw;
    $stmt_flw = $dbh->prepare($sql_flw);
    $stmt_flw->execute();
    $row_flw = $stmt_flw->fetch();
    $due_amntTotal=$due_amntTotal+$row_flw['cc_amnt'];
    while ($row_flw2 = $stmt_flw->fetch()) {
      $due_amntTotal+=$row_flw2['cc_amnt'];
    }
    // เบี้ยปรับค่าติดตามหรือเบี้ยปรับอื่นๆ //
    // $total_Camount=$Camount+$due_amntTotal;
    $total_Camount=$Camount;
    // -- END คำนวณยอดที่ต้องชำระ -- //
    //////////////////////////////
    if($due_amntTotal!=null) {
      if($row_SD['fw_status'] != '10') {
          if($row_SD['fw_status'] == '11') {
            // เบี้ยปรับ
            $total_Camount+=$row_SD['slowly_amnt']-$row_SD['cd_payamnt'];
            $outBK .= "เบี้ยปรับ";
            $output .= number_format($row_SD['slowly_amnt'],2);
          } else {
            // ค้างชำระ
            $total_Camount+=$due_amntTotal;
            $outBK .= "ค้างชำระ";
            $output .= number_format($due_amntTotal,2);
          }
      } else {
        $outBK .= "ยอดค้างชำระ";
        $output .="0.00";
      }
    } else {
      $outBK .= "ยอดค้างชำระ";
      $output .="0.00";
    }
  }
}

//   $sql_array = array();
//       $sql_array[] = array(
//       "outBK" => $outBK,
//       "output" => $output
//   );

$sqlx = "SELECT * FROM EHONGDB.dbo.hps_ctmain
WHERE ct_no = '$ct_no'";
$selectx=$dbh->prepare($sqlx);
$selectx->execute();
$sql_array = array();
$fetchx=$selectx->fetch();
    $ps_name = $fetchx['ps_name'];
    $ct_mobileno = $fetchx['ct_mobileno'];
    $product_name = $fetchx['product_name'];
    $due_amnt = $fetchx['due_amnt'];

    if ($output == ""){
        $price = $due_amnt+0.00;
    } else {
        $price = $due_amnt+$output;
    }

    $echoprice = $billerid."&ref1=".$ref1."&ref2=".$ref2."&price=".$price;

    $sql_array[] = array(
    "ct_no" => $ct_no,
    "ps_name" => $ps_name,
    "ct_mobileno" => $ct_mobileno,
    "product_name" => $product_name,
    "due_amnt" => number_format($due_amnt,2),
    "outBK" => $outBK,
    "output" => number_format($output,2),
    "price" => number_format($price,2),
    "echoprice" => $echoprice,
    "cnsvbarcode" => $fetchx['cnsvbarcode'],
    "cnsvrefcode" => $fetchx['cnsvrefcode'],
    "cnsvrefno" => $fetchx['cnsvrefno']
    );

echo json_encode($sql_array);

?>