<?php
  include '_session_login.php';
  include '_connect.php';
  $DMY_1 = $_REQUEST['DMY_1'];
  $DMY_2 = $_REQUEST['DMY_2'];
  $CROP_NAMEXC = $_REQUEST['CROP_NAME'];
  $SUMXX = $_REQUEST['icar'];
  if ($_REQUEST['SUM_PAPER'] != "") {
    $SUM_PAPER = $_REQUEST['SUM_PAPER'];
  }
  else {
    $SUM_PAPER = '0';
  }
  if ($_REQUEST['SUM_PANCIL'] != "") {
    $SUM_PANCIL = $_REQUEST['SUM_PANCIL'];
  }
  else {
    $SUM_PANCIL = '0';
  }
  $ACT_DATE = date('Y-m-d');

  //บริษัท
  $query = "SELECT * FROM [EHONGDB].[dbo].[CROPORATION] WHERE CROP_NAME = '$CROP_NAMEXC'";
  $select=$conn->prepare($query);
  $select->execute();
  $row = $select->fetch();
  $CROP_NOX = $row['CROP_NO'];

  // เลขบิล
  $query = "SELECT MAX(ACT_NO2) ACT_NO2 FROM [CONFIG].[dbo].[ACT_RECEIPT]";
  $select=$conn->prepare($query);
  $select->execute();
  $row_3 = $select->fetch();
  $ACTx = $row_3['ACT_NO2']; //ค่าเดิมที่อ่านมา
  $old = str_split($ACTx,4);
  $date = $old[0];
  $number = $old[1];
  $newdate = date("ym");
  if($date!=$newdate)
  {
  		$number = 1;
  		$number = sprintf("%03d",$number);
  }else{
  		$number = sprintf("%03d",++$number);;
  }
  $ACT_NO = $CROP_NOX.$newdate.$number;
  $ACT_NO2 = $newdate.$number;
?>

<?php
// ค่า พรบ ปกติ
$sql = "SELECT a.bill_no as xbill_no,a.bill_date as xbill_date,*,a.rectime as xrectime from [EHONGDB].[dbo].[reg_billdata] a
left outer join(SELECT * FROM [EHONGDB].[dbo].[reg_bill]) b on(a.bill_no=b.bill_no)
left outer join(SELECT * FROM [EHONGDB].[dbo].[BRANCH]) c on (a.BR_ID=c.BR_ID)
left outer join(SELECT * FROM [EHONGDB].[dbo].[REG_GWORK]) d on(b.g_id=d.G_ID)
left outer join(SELECT BILL_NO as EBILL_NO,ACT_DATE FROM [CONFIG].[dbo].[ACT_EXPOST]) e on(a.bill_no=e.EBILL_NO)
left outer JOIN(SELECT BILL_NO as sBILL_NO,ACT_STATUS,ACT_USDATE FROM CONFIG.dbo.ACT_STATUSEXPOST) dx on(a.bill_no=dx.sBILL_NO)
left outer JOIN(SELECT FEE_PRICE AS EDITFEE_PRICE, BILL_NO AS EDITBILL_NO,NOTE FROM CONFIG.dbo.ACT_EDIT) edit on(a.bill_no=edit.EDITBILL_NO)
where a.status<>'V' and fee_no='11' and a.bill_date between '$DMY_1' and '$DMY_2'
and ACT_DATE IS NULL
order by a.rectime,a.br_id,a.bill_date";
// echo $sql;
$select=$conn->prepare($sql);
$select->execute();
$item = 1;
$B10 = '10.00';
$SUMFEE_SUMPRICE = '0';
$SUMFEE_PRICE = '0';
$SUMB10 = '0';
$SUMsB10 = '0';
$SUM_x1 = '0';
$SUM_x2 = '0';
$FEE_PRICE = '0';
$SUMxFEE_PRICE = '0';
while($fetch=$select->fetch()){
  include 'act_sum_1.php';
  if ($CROP_NAME == "$CROP_NAMEXC") {
    // $FEE_PRICE = number_format($fetch['FEE_PRICE'],2);
    if ($fetch['EDITFEE_PRICE'] == "") {
      $FEE_PRICE = number_format($fetch['FEE_PRICE'],2);
      $NOTE = "";
      $CL = "blue";
      $EDIT = "แก้ไข";
    }
    else {
      $FEE_PRICE = number_format($fetch['EDITFEE_PRICE'],2);
      $NOTE = $fetch['NOTE'];
      $CL = "green";
      $EDIT = "แก้ไขแล้ว";
    }

    $FPX1 = number_format($fetch['FEE_PRICE']-$B10,2);
    if ($fetch['FEE_SUMPRICE'] != '0') {
      if ($fetch['CC_SIZE'] < '75') {
        $FEE_SUMPRICE = number_format(190,2);
      }
      if ($fetch['CC_SIZE'] >= '75') {
        $FEE_SUMPRICE = number_format(350,2);
      }
      if ($fetch['CC_SIZE'] > '125') {
        $FEE_SUMPRICE = number_format(450,2);
      }
      if ($fetch['CC_SIZE'] > '150') {
        $FEE_SUMPRICE = number_format(700,2);
      }
      $SUM_1 = number_format(($FEE_SUMPRICE-$B10-$FPX1)/1.07,2);
      $SUM_2 = number_format(($SUM_1*0.07),2);
      $SUMFEE_SUMPRICE += $FEE_SUMPRICE;
      $SUM_x1 += $SUM_1;
      $SUM_x2 += $SUM_2;
    }
    else {
      $FEE_SUMPRICE = '';
      $SUM_1 = '';
      $SUM_2 = '';
    }
    $SUMxFEE_PRICE += $FEE_PRICE;
    $SUMsB10 += $B10;
    $SUMFB10_1 = $SUMxFEE_PRICE-$SUMsB10;
    // echo $SUMxFEE_PRICE;
    // echo $B10;
    // echo $FPX1." ".$B10." ".$SUM_1." ".$SUM_2." <br>";

    //บันทึกลง ACT_EXPOST เก็บรายงาน พรบ ปกติ
    $strSQL = "INSERT INTO CONFIG.dbo.ACT_EXPOST";
    $strSQL .="(BILL_NO,BILL_DATE,BR_ID,BR_NAME,CT_NO,PS_NAME,G_ID,FEE_STATUSX,FEE_STATUSX1,CROP_NAME,CROP_NAME1,BRAND_NAME,SERIAL_NO,CC_SIZE,ACT_DATE,ACT_NO,ACT_PRICE,ACT_BROKER,ACT_INCOME,ACT_VAT) ";
    $strSQL .="VALUES ";
    $strSQL .="('".$fetch['BILL_NO']."','".$fetch['BILL_DATE']."','".$fetch['BR_ID']."','".$fetch['BR_NAME']."','".$fetch['CT_NO']."',
    '".$fetch['PS_NAME']."','".$fetch['g_id']."','$FEE_STATUSX','$FEE_STATUSX1','".$fetch['CROP_NAME']."',
    '$CROP_NAME','".$fetch['BRAND_NAME']."','".$fetch['SERIAL_NO']."','".$fetch['CC_SIZE']."','$ACT_DATE',
    '$ACT_NO','$FPX1','$B10','$SUM_1','$SUM_2')";
    $result = $conn->query($strSQL);
    // echo $strSQL;

    if ($item == "1") {
      $ACT_DATE1 = $fetch['BILL_DATE'];
    }
    $ACT_DATE2 = $fetch['BILL_DATE'];
    // echo $strSQL;
    $item++; } }
?>

<?php
// เพิ่ม พรบ
$sql_in = "SELECT DISTINCT
inx.BILL_DATE, inx.BR_ID, inx.CT_NO, inx.PS_NAME, inx.FEE_STATUS, inx.CROP_NAME, inx.BRAND_NAME, inx.SERIAL_NO, inx.CC_SIZE, inx.FEE_SUMPRICE,
inx.BILL_DATE, inx.BILL_NO, inx.FEE_PRICE, inx.ACT_DATE, inx.PLOCNO, inx.PLCDATE, inx.PLATE, inx.PR_CODE, inx.REF_NO, inx.FEE_SNO,
c.BR_NAME,
d.g_id,
e.ACT_DATE
FROM [CONFIG].[dbo].[ACT_NEWACTS] inx
LEFT OUTER JOIN (SELECT * FROM EHONGDB.dbo.reg_billdata) a on (inx.BILL_NO = a.bill_no)
LEFT OUTER JOIN (SELECT * FROM EHONGDB.dbo.reg_bill) b on (a.bill_no = b.bill_no)
left outer join(SELECT * FROM [EHONGDB].[dbo].[BRANCH]) c on (inx.BR_ID=c.BR_ID)
left outer join(SELECT * FROM [EHONGDB].[dbo].[REG_GWORK]) d on(b.g_id=d.G_ID)
left outer join(SELECT BILL_NO as EBILL_NO,ACT_DATE FROM [CONFIG].[dbo].[ACT_EXPOST]) e on(a.bill_no=e.EBILL_NO)
left outer JOIN(SELECT BILL_NO as sBILL_NO,ACT_STATUS,ACT_USDATE FROM CONFIG.dbo.ACT_STATUSEXPOST) dx on(a.bill_no=dx.sBILL_NO)
left outer JOIN(SELECT FEE_PRICE AS EDITFEE_PRICE, BILL_NO AS EDITBILL_NO,NOTE FROM CONFIG.dbo.ACT_EDIT) edit on(a.bill_no=edit.EDITBILL_NO)
WHERE inx.BILL_DATE BETWEEN '$DMY_1' AND '$DMY_2' and inx.CROP_NAME = '$CROP_NAMEXC' and e.ACT_DATE is NULL";
// echo $sql_in;
$select_in=$conn->prepare($sql_in);
$select_in->execute();
$b10_in = '10.00';
$FEE_PRICE_in = 0;
$sumb10_in = 0;
while($fetch_in=$select_in->fetch()){
  $FEE_STATUSX_in = '';
  $FEE_STATUSX1_in = $fetch_in['FEE_STATUS'];
  $FEE_PRICE_in += $fetch_in['FEE_PRICE']; // พรบ
  $sumb10_in += $b10_in; // ค่ค่านายหน้า
  $FPX1_in = number_format($fetch_in['FEE_PRICE']-$b10_in,2);
  if ($fetch_in['FEE_SUMPRICE'] != '0') {
    if ($fetch_in['CC_SIZE'] < '75') {
      $FEE_SUMPRICE_in = number_format(190,2);
    }
    if ($fetch_in['CC_SIZE'] >= '75') {
      $FEE_SUMPRICE_in = number_format(350,2);
    }
    if ($fetch_in['CC_SIZE'] > '125') {
      $FEE_SUMPRICE_in = number_format(450,2);
    }
    if ($fetch_in['CC_SIZE'] > '150') {
      $FEE_SUMPRICE_in = number_format(700,2);
    }
    $SUM_1_in = number_format(($FEE_SUMPRICE_in-$b10_in-$FPX1_in)/1.07,2);
    $SUM_2_in = number_format(($SUM_1_in*0.07),2);
  }

  $strSQL_in = "INSERT INTO CONFIG.dbo.ACT_EXPOST";
  $strSQL_in .="(BILL_NO,BILL_DATE,BR_ID,BR_NAME,CT_NO,PS_NAME,G_ID,FEE_STATUSX,FEE_STATUSX1,CROP_NAME,CROP_NAME1,BRAND_NAME,SERIAL_NO,CC_SIZE,ACT_DATE,ACT_NO,ACT_PRICE,ACT_BROKER,ACT_INCOME,ACT_VAT) ";
  $strSQL_in .="VALUES ";
  $strSQL_in .="('".$fetch_in['BILL_NO']."','".$fetch_in['BILL_DATE']."','".$fetch_in['BR_ID']."','".$fetch_in['BR_NAME']."','".$fetch_in['CT_NO']."',
  '".$fetch_in['PS_NAME']."','".$fetch_in['g_id']."','$FEE_STATUSX_in','$FEE_STATUSX1_in','".$fetch_in['CROP_NAME']."',
  '".$fetch_in['CROP_NAME']."','".$fetch_in['BRAND_NAME']."','".$fetch_in['SERIAL_NO']."','".$fetch_in['CC_SIZE']."','$ACT_DATE',
  '$ACT_NO','$FPX1_in','$b10_in','$SUM_1_in','$SUM_2_in')";
  // echo $strSQL_in."<br>";
  $result_in = $conn->query($strSQL_in);
}
  $FEE_PRICE_IND10 = $FEE_PRICE_in-$sumb10_in; // พรบ-ค่านายหน้า

// echo $FEE_PRICE_in."<br>";
// echo $sumb10_in."<br>";

// รถตีเทิร์น
$sql_tx = "SELECT
tx.SERIAL_NO,tx.FEE_STATUSX,tx.FEE_STATUSX1,
a.bill_no as xbill_no,a.bill_date as xbill_date,a.rectime as xrectime,a.FEE_PRICE,a.FEE_SUMPRICE,a.BR_ID,a.CT_NO,
b.PS_NAME,b.BRAND_NAME,b.CROP_NAME,b.CC_SIZE,
c.BR_NAME,
d.g_id
FROM CONFIG.dbo.ACT_TURNX tx
LEFT OUTER JOIN (SELECT * FROM EHONGDB.dbo.reg_billdata) a on (tx.SERIAL_NO = a.SERIAL_NO)
LEFT OUTER JOIN (SELECT * FROM EHONGDB.dbo.reg_bill) b on (a.bill_no = b.bill_no)
left outer join(SELECT * FROM [EHONGDB].[dbo].[BRANCH]) c on (a.BR_ID=c.BR_ID)
left outer join(SELECT * FROM [EHONGDB].[dbo].[REG_GWORK]) d on(b.g_id=d.G_ID)
left outer join(SELECT BILL_NO as EBILL_NO,ACT_DATE FROM [CONFIG].[dbo].[ACT_EXPOST]) e on(a.bill_no=e.EBILL_NO)
left outer JOIN(SELECT BILL_NO as sBILL_NO,ACT_STATUS,ACT_USDATE FROM CONFIG.dbo.ACT_STATUSEXPOST) dx on(a.bill_no=dx.sBILL_NO)
left outer JOIN(SELECT FEE_PRICE AS EDITFEE_PRICE, BILL_NO AS EDITBILL_NO,NOTE FROM CONFIG.dbo.ACT_EDIT) edit on(a.bill_no=edit.EDITBILL_NO)
WHERE a.status<>'V' and fee_no='11' and tx.BILL_DATE BETWEEN '$DMY_1 00:00:00.000' AND '$DMY_2 00:00:00.000' and e.ACT_DATE is NULL
and b.CROP_NAME = '$CROP_NAMEXC'
order by a.rectime,a.br_id,a.bill_date";
// echo $sql_tx;
$select_tx=$conn->prepare($sql_tx);
$select_tx->execute();
$b10_tx = '10.00';
$FEE_PRICE_TX = 0;
$sumb10_tx = 0;
while($fetch_tx=$select_tx->fetch()){
  $FEE_STATUSX_tx = $fetch_tx['FEE_STATUSX'];
  $FEE_STATUSX1_tx = $fetch_tx['FEE_STATUSX1'];
  $FEE_PRICE_TX += $fetch_tx['FEE_PRICE']; // พรบ
  $sumb10_tx += $b10_tx; // ค่ค่านายหน้า
  $FPX1_tx = number_format($fetch_tx['FEE_PRICE']-$b10_tx,2);
  if ($fetch_tx['FEE_SUMPRICE'] != '0') {
    if ($fetch_tx['CC_SIZE'] < '75') {
      $FEE_SUMPRICE_tx = number_format(190,2);
    }
    if ($fetch_tx['CC_SIZE'] >= '75') {
      $FEE_SUMPRICE_tx = number_format(350,2);
    }
    if ($fetch_tx['CC_SIZE'] > '125') {
      $FEE_SUMPRICE_tx = number_format(450,2);
    }
    if ($fetch_tx['CC_SIZE'] > '150') {
      $FEE_SUMPRICE_tx = number_format(700,2);
    }
    $SUM_1_tx = number_format(($FEE_SUMPRICE_tx-$b10_tx-$FPX1_tx)/1.07,2);
    $SUM_2_tx = number_format(($SUM_1_tx*0.07),2);
    // $SUMFEE_SUMPRICE += $FEE_SUMPRICE;
    // $SUM_x1 += $SUM_1;
    // $SUM_x2 += $SUM_2;
  }
  else {
    $FEE_SUMPRICE_tx = '';
    $SUM_1_tx = '';
    $SUM_2_tx = '';
  }
  // echo $FEE_SUMPRICE_tx." ".$SUM_1_tx." ".$SUM_2_tx."<br>";

  //บันทึกลง ACT_EXPOST เก็บรายงาน
  $strSQL_tx = "INSERT INTO CONFIG.dbo.ACT_EXPOST";
  $strSQL_tx .="(BILL_NO,BILL_DATE,BR_ID,BR_NAME,CT_NO,PS_NAME,G_ID,FEE_STATUSX,FEE_STATUSX1,CROP_NAME,CROP_NAME1,BRAND_NAME,SERIAL_NO,CC_SIZE,ACT_DATE,ACT_NO,ACT_PRICE,ACT_BROKER,ACT_INCOME,ACT_VAT) ";
  $strSQL_tx .="VALUES ";
  $strSQL_tx .="('".$fetch_tx['xbill_no']."','".$fetch_tx['xbill_date']."','".$fetch_tx['BR_ID']."','".$fetch_tx['BR_NAME']."','".$fetch_tx['CT_NO']."',
  '".$fetch_tx['PS_NAME']."','".$fetch_tx['g_id']."','$FEE_STATUSX_tx','$FEE_STATUSX1_tx','".$fetch_tx['CROP_NAME']."',
  '".$fetch_tx['CROP_NAME']."','".$fetch_tx['BRAND_NAME']."','".$fetch_tx['SERIAL_NO']."','".$fetch_tx['CC_SIZE']."','$ACT_DATE',
  '$ACT_NO','$FPX1_tx','$b10_tx','$SUM_1_tx','$SUM_2_tx')";
  // echo $strSQL_tx."<br>";
  $result_tx = $conn->query($strSQL_tx);
  if ($item == "1") {
    $ACT_DATE1 = $fetch_tx['xbill_date'];
  }
  $ACT_DATE2 = $fetch_tx['xbill_date'];
}
  $FEE_PRICE_TXD10 = $FEE_PRICE_TX-$sumb10_tx; // พรบ-ค่านายหน้า
?>

<?php
  // คำนวน
  $S1_FEE_PRINCE = $FEE_PRICE_TX+$SUMxFEE_PRICE+$FEE_PRICE_in; // พรบ
  $S2_B10 = $sumb10_tx+$SUMsB10+$sumb10_in; // ค่ค่านายหน้า
  $S3_FPB10 = $FEE_PRICE_TXD10+$SUMFB10_1+$FEE_PRICE_IND10+$SUM_PAPER+$SUM_PANCIL; // พรบ-ค่านายหน้า
  // $SUM_PAPER = '';
  // $SUM_PANCIL = '';
  // $S4 = $SUM_1_tx; // รายได้ค่าจดทะเบียน รถตีเทิร์น
  // $S5 = $SUM_2_tx; // ภาษี รถตีเทิร์น

  // echo $S1." ".$S2." ".$S3." ".$S4." ".$S5;
?>

<!-- ใบเสร็จ -->
<?php
    $COMPANY = "บริษัท กลางคุ้มครองผู้ประสบภัยจากรถยนต์";
    $PAYER = "นางสาวสวาสดิพร แก้วมาลา";
    $REG_POSITION = "เจ้าหน้าที่ พ.ร.บ.";
    $SUMX4 = $SUMxFEE_PRICE-$SUMsB10;
    $SUM_PRINCE = $SUMxFEE_PRICE-$SUMsB10;

    // บันทึกลง ACT_RECEIPT เก็บรายงาน
    $strSQL = "INSERT INTO CONFIG.dbo.ACT_RECEIPT";
    $strSQL .="(ACT_NO,CROP_NAME,COMPANY,SUMX_PRICE,REG_PRICE,
    SUM_BROKER,SUM_PAPER,SUM_PANCIL,SUM_PRINCE,PAYER,
    REG_POSITION,ACT_DATE1,ACT_DATE2,ACT_DATE,
    ACT_NO2) ";
    $strSQL .="VALUES ";
    $strSQL .="('$ACT_NO','$CROP_NAMEXC','$COMPANY','$SUMXX','$S1_FEE_PRINCE',
    '$S2_B10','$SUM_PAPER','$SUM_PANCIL','$S3_FPB10','$PAYER','$REG_POSITION','$ACT_DATE1','$ACT_DATE2','$ACT_DATE',
    '$ACT_NO2')";
    // echo $strSQL;
    $result = $conn->query($strSQL);

    $SUMPRICEX1 = number_format(($SUMX4+$SUM_PAPER)+$SUM_PANCIL,2);
    if ($ACT_DATE1 != '') {
      $ACT_DATE11 = substr($ACT_DATE1,0,4)+543; $ACT_DATE12 = substr($ACT_DATE1,5,2); $ACT_DATE13 = substr($ACT_DATE1,8,2);
      $ACT_DATE1x = $ACT_DATE13."/".$ACT_DATE12."/".$ACT_DATE11;
    } else {
      $ACT_DATE1x = '';
    }
    if ($ACT_DATE2 != '') {
      $ACT_DATE21 = substr($ACT_DATE2,0,4)+543; $ACT_DATE22 = substr($ACT_DATE2,5,2); $ACT_DATE23 = substr($ACT_DATE2,8,2);
      $ACT_DATE2x = $ACT_DATE23."/".$ACT_DATE22."/".$ACT_DATE21;
    } else {
      $ACT_DATE2x = '';
    }
?>
