invisio v2.0

<?php

include('../../../config/main_function.php');
$secure = "cAh3DrJACzw4RbU";
$connection = connectDB($secure);

require('../../../fpdf/alphapdf.php');

$date_start = date_picker(mysqli_real_escape_string($connection, $_POST['date_start']));
$date_end = date_picker(mysqli_real_escape_string($connection, $_POST['date_end']));
$level = mysqli_real_escape_string($connection, $_POST['level']);
$type = mysqli_real_escape_string($connection, $_POST['type']);

if ($level == 'g9k1s') {
    $member_level = 'ทั่วไป';
} elseif ($level == 'kki22') {
    $member_level = 'วิสามัญ';
} elseif ($level == 'x9fkq') {
    $member_level = 'สามัญ.';
}

$condition_type = "";
if ($type != 'x') {
    $condition_type .= "AND a.register_type = '$type'";
} else {
    $condition_type .= "";
}

$condition_level = "";
if ($level != 'x') {
    $condition_level .= "AND a.register_level = '$level'";
} else {
    $condition_level .= "";
}

$condition_search = "";
if ($search != "") {
    $search = bigsara_encode($search, $key, $pointer);
    $condition_search  .= "AND c.citizen_no LIKE '%$search%' 
    OR d.citizen_no LIKE '%$search%' 
    OR e.citizen_no LIKE '%$search%' 
    OR f.citizen_no LIKE '%$search%'";
}

$condition_date = "";
if (isset($date_start) || isset($date_end)) {
    $condition_date  .= "AND g.approve_datetime BETWEEN '$date_start' AND '$date_end'";
}

$sql_member = "SELECT a.*,
( CASE 
 	WHEN a.register_type = '1' THEN c.member_name 
    WHEN a.register_type = '2' THEN d.member_name 
    WHEN a.register_type = '3' THEN e.member_name 
    WHEN a.register_type = '4' THEN f.member_name 
END ) AS member_name,
( CASE 
 	WHEN a.register_type = '1' THEN c.member_name_en 
    WHEN a.register_type = '2' THEN d.member_name_en 
    WHEN a.register_type = '3' THEN e.member_name_en 
    WHEN a.register_type = '4' THEN f.member_name_en 
END ) AS member_name_en,
( CASE 
 	WHEN a.register_type = '1' THEN c.company_name 
    WHEN a.register_type = '2' THEN d.company_name 
    WHEN a.register_type = '3' THEN e.company_name 
    WHEN a.register_type = '4' THEN f.company_name 
END ) AS company_name,
( CASE 
 	WHEN a.register_type = '1' THEN c.email 
    WHEN a.register_type = '2' THEN d.email 
    WHEN a.register_type = '3' THEN e.email 
    WHEN a.register_type = '4' THEN f.email 
END ) AS email,
( CASE 
 	WHEN a.register_type = '1' THEN c.phone 
    WHEN a.register_type = '2' THEN d.phone 
    WHEN a.register_type = '3' THEN e.phone 
    WHEN a.register_type = '4' THEN f.phone 
END ) AS phone,

( CASE 
 	WHEN a.register_type = '1' THEN c.race 
    WHEN a.register_type = '2' THEN d.race 
    WHEN a.register_type = '3' THEN e.race 
    WHEN a.register_type = '4' THEN f.race 
END ) AS race,
( CASE 
 	WHEN a.register_type = '1' THEN c.birth_date 
    WHEN a.register_type = '2' THEN d.birth_date 
    WHEN a.register_type = '3' THEN e.birth_date 
    WHEN a.register_type = '4' THEN f.birth_date 
END ) AS birth_date,
( CASE 
 	WHEN a.register_type = '1' THEN c.title_name 
    WHEN a.register_type = '2' THEN d.title_name 
    WHEN a.register_type = '3' THEN e.title_name 
    WHEN a.register_type = '4' THEN f.title_name 
END ) AS title_name,
( CASE 
 	WHEN a.register_type = '1' THEN c.company_phone 
    WHEN a.register_type = '2' THEN d.company_phone 
    WHEN a.register_type = '3' THEN e.company_phone 
    WHEN a.register_type = '4' THEN f.company_phone
END ) AS company_phone,
( CASE 
 	WHEN a.register_type = '1' THEN c.company_email 
    WHEN a.register_type = '2' THEN d.company_email 
    WHEN a.register_type = '3' THEN e.company_email 
    WHEN a.register_type = '4' THEN f.company_email
END ) AS company_email,
g.*,
h.*
FROM tbl_member a 
LEFT JOIN tbl_member_detail_employer c ON c.member_id = a.member_id
LEFT JOIN tbl_member_detail_company d ON  d.member_id = a.member_id
LEFT JOIN tbl_member_detail_agency e ON e.member_id = a.member_id
LEFT JOIN tbl_member_detail_worker f ON f.member_id = a.member_id
LEFT JOIN tbl_member_payment g ON g.member_id = a.member_id
LEFT JOIN tbl_discount_coupon h ON h.coupon_id = g.coupon_id

WHERE g.approve_result ='1' $condition_date $condition_type $condition_level";
$rs_member = mysqli_query($connection, $sql_member) or die($connection->error);
//$row_member = mysqli_fetch_assoc($rs_member);
// -- WHERE a.register_status = '1' AND g.approve_result ='1' $condition_date $condition_type $condition_level

// -- WHERE a.register_status = '1' AND g.approve_result ='1' $condition_date $condition_type $condition_level

$sql_sumprice = "SELECT SUM(g.payment_amount) AS sumprice,
COUNT(member_id) AS member_count
FROM tbl_member_payment g
LEFT JOIN tbl_discount_coupon dc ON dc.coupon_id = g.coupon_id 
WHERE g.approve_result ='1' $condition_date $condition_type";
$res_sumprice = mysqli_query($connection, $sql_sumprice);
$row_sumprice = mysqli_fetch_assoc($res_sumprice);

$sql_bank = "SELECT * FROM tbl_bank WHERE active_status = '1'";
$res_bank = mysqli_query($connection, $sql_bank);
$row_bank = mysqli_fetch_assoc($res_bank);

$pdf = new FPDF();
$pdf->AliasNbPages();

$pdf->SetAutoPageBreak(false);
$pdf->AddFont('THSarabunNew', '', 'THSarabunNew.php');
$pdf->AddFont('THSarabunNew Bold', '', 'THSarabunNew Bold.php');
$pdf->AddFont('THSarabunNew BoldItalic', '', 'THSarabunNew BoldItalic.php');

$pdf->AddPage();

// $pdf->Line(125, 21, 197,  21);
// $pdf->Line(125, 56, 197,  56);
// $pdf->Line(13, 101, 197,  101);
// $pdf->Line(13, 108, 197,  108);
// $pdf->Line(139, 180, 197,  180);

$pdf->Image('Logo.png', 14.8, 11, 21, 21, 'PNG'); //LOGO
$pdf->SetFont('THSarabunNew Bold', '', 14);

$pdf->SetXY(38, 13);
$pdf->MultiCell(0, 5, iconv('UTF-8', 'cp874', 'สมาคมผู้ประกอบการนำคนต่างด้าวมาทำงานในประเทศ(ประเทศไทย)'), 0, 'L', 0);
$pdf->SetXY(38, 19);
$pdf->MultiCell(0, 5, iconv('UTF-8', 'cp874', '59/28 หมู่ที่ 8 ตำบลท่าทราย อำเภอเมืองสมุทรสาคร จังหวัดสมุทรสาคร 74000'), 0, 'L', 0);
$pdf->SetXY(38, 25);
$pdf->MultiCell(0, 5, iconv('UTF-8', 'cp874', 'หมายเลขประจำตัวผู้เสียภาษี: 0745561004453'), 0, 'L', 0);

$pdf->SetFont('THSarabunNew Bold', '', 18.5);
$pdf->SetXY(90, 38); //ใบสมัครสมาชิก
$pdf->MultiCell(200, 5, iconv('UTF-8', 'cp874', 'ใบเสร็จรับเงิน'), 0, 'L', 0);

////ข้อมูลในตาราง/////(หัวตาราง)
$pdf->SetFont('THSarabunNew Bold', '', 14);

$pdf->SetXY(13, 50);
$pdf->MultiCell(0, 5, iconv('UTF-8', 'cp874', 'ลูกค้า : '), 0, 'L', 0);
$pdf->SetXY(13, 56);
$pdf->MultiCell(0, 5, iconv('UTF-8', 'cp874', 'ที่อยู่ : '), 0, 'L', 0);
$pdf->SetXY(13, 62);
$pdf->MultiCell(0, 5, iconv('UTF-8', 'cp874', 'หมายเลขประจำตัวผู้เสียภาษี : '), 0, 'L', 0);

$pdf->SetXY(155, 50);
$pdf->MultiCell(0, 5, iconv('UTF-8', 'cp874', 'เลขที่ใบเสร็จ : '), 0, 'L', 0);
$pdf->SetXY(166, 56);
$pdf->MultiCell(0, 5, iconv('UTF-8', 'cp874', 'วันที่ : '), 0, 'L', 0);

$pdf->SetXY(170, 64);
// Create headers
$pdf->Ln(); // Move to the next line
$pdf->Cell(10, 8, iconv('UTF-8', 'cp874', 'ลำดับ'), 1, 0, 'L');
$pdf->Cell(75, 8, iconv('UTF-8', 'cp874', 'รายการ'), 1, 0, 'L');
$pdf->Cell(35, 8, iconv('UTF-8', 'cp874', 'หน่วยละ'), 1, 0, 'C');
$pdf->Cell(35, 8, iconv('UTF-8', 'cp874', 'จำนวน(คน)'), 1, 0, 'C');
$pdf->Cell(35, 8, iconv('UTF-8', 'cp874', 'จำนวนเงิน'), 1, 0, 'C');
$pdf->Ln(); // Move to the next line
$pdf->Cell(10, 50, iconv('UTF-8', 'cp874', ''), 1, 0, 'L');
$pdf->Cell(75, 50, iconv('UTF-8', 'cp874', ''), 1, 0, 'L');
$pdf->Cell(35, 50, iconv('UTF-8', 'cp874', ''), 1, 0, 'C');
$pdf->Cell(35, 50, iconv('UTF-8', 'cp874', ''), 1, 0, 'C');
$pdf->Cell(35, 50, iconv('UTF-8', 'cp874', ''), 1, 0, 'C');
$pdf->Ln(); // Move to the next line
$pdf->Cell(120, 8, iconv('UTF-8', 'cp874', ''), 1, 0, 'R');
$pdf->Cell(35, 8, iconv('UTF-8', 'cp874', 'รวมทั้งสิ้น'), 1, 0, 'R');
$pdf->Cell(35, 8, iconv('UTF-8', 'cp874', ''), 1, 0, 'L');
// $pdf->Ln(); // Move to the next line
// $pdf->Cell(150, 8, iconv('UTF-8', 'cp874', ''), 1, 0, 'C');
// $pdf->Cell(40, 8, iconv('UTF-8', 'cp874', 'รวมทั้งสิ้น'), 1, 0, 'L');
$pdf->SetXY(13, 140);
$pdf->MultiCell(200, 5, iconv('UTF-8', 'cp874', 'หมายเหตุ'), 0, 'L', 0);

$pdf->SetXY(13, 255);
$pdf->MultiCell(200, 5, iconv('UTF-8', 'cp874', 'การชำระเงิน'), 0, 'L', 0);

$pdf->SetFont('THSarabunNew', '', 14);

$pdf->SetXY(25, 50);
$pdf->MultiCell(0, 5, iconv('UTF-8', 'cp874', $row_member['invoice_name']), 0, 'L', 0);

$invoice_district_id = $row_member['invoice_district'];
$sqlAddressInvoice = "SELECT `tbl_district`.`district_name_th` , `tbl_amphoe`.`amphoe_name_th` , `tbl_province`.`province_name_th` , `tbl_district`.`district_zipcode` FROM `tbl_district` 
JOIN `tbl_amphoe` ON `tbl_amphoe`.`amphoe_id` = `tbl_district`.`ref_amphoe`
JOIN `tbl_province` ON `tbl_province`.`province_id` = `tbl_amphoe`.`ref_province`
WHERE `tbl_district`.`district_id` = '$invoice_district_id'";
$rsAddressInvoice = mysqli_query($connection, $sqlAddressInvoice);
$rowAddressInvoice = mysqli_fetch_array($rsAddressInvoice);
$invoice_address = $row_member['invoice_address']." ตำบล".$rowAddressInvoice['district_name_th']." อำเภอ".$rowAddressInvoice['amphoe_name_th']." จังหวัด".$rowAddressInvoice['province_name_th']." ".$rowAddressInvoice['district_zipcode'];
$pdf->SetXY(23, 56);
$pdf->MultiCell(0, 5, iconv('UTF-8', 'cp874', $invoice_address), 0, 'L', 0);

$pdf->SetXY(55, 62);
$pdf->MultiCell(0, 5, iconv('UTF-8', 'cp874', $row_member['invoice_taxnumber']), 0, 'L', 0);
$pdf->SetXY(175, 50);
$pdf->MultiCell(0, 5, iconv('UTF-8', 'cp874', $row_member['invoice_number']), 0, 'L', 0);
$pdf->SetXY(175, 56);
$pdf->MultiCell(0, 5, iconv('UTF-8', 'cp874', dateShow($row_member['approve_datetime'])), 0, 'L', 0);

$pdf->SetXY(13, 80);
$pdf->MultiCell(0, 5, iconv('UTF-8', 'cp874', '1'), 0, 'L', 0);
$pdf->SetXY(20, 80);
$pdf->MultiCell(0, 5, iconv('UTF-8', 'cp874', 'ค่าสมัครสมาชิกระดับ ' . $member_level . ''), 0, 'L', 0);
$pdf->SetXY(115, 80);
$pdf->MultiCell(0, 5, iconv('UTF-8', 'cp874', '' . $row_member['payment_amount'] . ''), 0, 'L', 0);
$pdf->SetXY(155, 80);
$pdf->MultiCell(0, 5, iconv('UTF-8', 'cp874', $row_sumprice['member_count']), 0, 'L', 0);
$pdf->SetXY(185, 80);
$pdf->MultiCell(0, 5, iconv('UTF-8', 'cp874', '' . $row_sumprice['sumprice'] . ''), 0, 'L', 0);

if (!empty($row_member['coupon_id'])) {

$pdf->SetXY(13, 86);
    $pdf->MultiCell(0, 5, iconv('UTF-8', 'cp874', '2'), 0, 'L', 0);
    $pdf->SetXY(20, 86);
    $pdf->MultiCell(0, 5, iconv('UTF-8', 'cp874', $row_member['coupon_name']), 0, 'L', 0);
    $pdf->SetXY(115, 86);
    $pdf->MultiCell(0, 5, iconv('UTF-8', 'cp874', $row_member['discount_amount']), 0, 'L', 0);
    $pdf->SetXY(155, 86);
    $pdf->MultiCell(0, 5, iconv('UTF-8', 'cp874', '1'), 0, 'L', 0);
    $pdf->SetXY(185, 86);
    $pdf->MultiCell(0, 5, iconv('UTF-8', 'cp874', $row_member['discount_amount']), 0, 'L', 0);

$discount = $row_member['discount_amount'];
} else {
    $discount = 0;
}

$pdf->SetXY(55, 129);
$pdf->MultiCell(0, 5, iconv('UTF-8', 'cp874', convertToMoney($row_sumprice['sumprice'] - $discount)), 0, 'L', 0);
$pdf->SetXY(185, 129);
$pdf->MultiCell(0, 5, iconv('UTF-8', 'cp874', '' . number_format($row_sumprice['sumprice'] - $discount) . ''), 0, 'L', 0);

$pdf->SetXY(30, 140);
$pdf->MultiCell(0, 5, iconv('UTF-8', 'cp874', $rowPayment['payment_remark']), 0, 'L', 0);

$rowcount = 155;
while ($row_mem = mysqli_fetch_assoc($rs_member)) {
    $pdf->SetXY(13, $rowcount);
    $pdf->MultiCell(200, 5, iconv('UTF-8', 'cp874', $row_mem['invoice_name']), 0, 'L', 0);
    $rowcount += 6;
}

$pdf->SetXY(13, 261);
$pdf->MultiCell(0, 5, iconv('UTF-8', 'cp874', $row_bank['bank_name'] . " เลขบัญชี " . $row_bank['account_number']), 0, 'L', 0);
$pdf->SetXY(13, 267);
$pdf->MultiCell(0, 5, iconv('UTF-8', 'cp874', "ชื่อบัญชี " . $row_bank['account_name']), 0, 'L', 0);

$pdf->Output();