invisio v2.0

<?php

use function PHPSTORM_META\type;

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

$sql_system = "SELECT * FROM tbl_system_setting WHERE setting_id = '1';";
$rs_system = mysqli_query($connection, $sql_system);
$row_system = mysqli_fetch_array($rs_system);

$key = $row_system['secure_text'];
$pointer = $row_system['secure_pointer'];

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

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

$condition_level = "";
if ($level != 'x') {
    $condition_level .= "AND a.register_level = '$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.discount_amount
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 a.register_status = '1' AND g.approve_result ='1' $condition_date $condition_type $condition_level
ORDER BY register_no ASC";
$rs_member = mysqli_query($connection, $sql_member) or die($connection->error);

?>
<style>

</style>
<div class="table-responsive">
    <table class="table table-striped dataTables-example table-bordered" id="myTable">
        <thead>
            <tr>
                <th class="w-auto">วันที่ออก</th>
                <th class="w-auto">เลขที่</th>
                <th class="w-auto">ลูกค้า</th>
                <th class="w-auto">บริษัท</th>
                <th class="w-auto">ยอดรวมสุทธิ</th>
                <th class="w-auto"></th>
            </tr>
        </thead>
        <tbody>
            <?php $i = 1;
            while ($row_member = mysqli_fetch_assoc($rs_member)) {
                $member_id_temp = bigsara_encode($row_member['member_id'], $row_member['secure_text'], $row_member['secure_pointer']);
                $member_id = $member_id_temp . "." . $row_member['secure_text'];

$register_type = $row_member['register_type'];

if ($register_type == 1) {
                    $register_type = 'นายจ้าง';
                } elseif ($register_type == 2) {
                    $register_type = 'บนจ.';
                } elseif ($register_type == 3) {
                    $register_type = 'Agency';
                } elseif ($register_type == 4) {
                    $register_type = 'แรงงาน';
                }

$register_level = $row_member['register_level'];

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

$temp_title = explode(" ", getTitle_name($row_member['title_name']));
                $title_th = $temp_title[0];
                $title_en = $temp_title[2];

$sql_race = "SELECT * FROM tbl_country WHERE ct_code = '{$row_member['race']}'";
                $res_race = mysqli_query($connection, $sql_race);
                $row_race = mysqli_fetch_assoc($res_race);

$sql = "SELECT * FROM tbl_register_document WHERE member_type = '{$row_member['register_type']}' ORDER BY list_order ASC";
                $rs = mysqli_query($connection, $sql);
                $num_list = mysqli_num_rows($rs);
            ?>
                <tr id="<?php echo $row_member['member_id']; ?>">
                    <td>
                        <?php echo date("d/m/Y",strtotime($row_member['approve_datetime'])); ?>
                    </td>
                    <td>
                        <a href="account_receipt_view?id=<?php echo $row_member['member_id']; ?>" target="_blank"><?php echo $row_member['invoice_number']; ?></a>
                        <a href="https://efa-member.com/backoffice/print/receipt_new?id=<?php echo $row_member['member_id']; ?>" target="_blank"><?php echo $row_member['invoice_number']; ?></a>
                    </td>
                    <td>
                        <?php if (empty($row_member['member_code'])) { ?>
                            <a href="member_detail?id=<?php echo $member_id ?>&type=<?php echo $row_member['register_type'] ?>&level=<?php echo $row_member['register_level'] ?>" target="_blank"><?php echo $row_member['register_no'] ?></a>
                        <?php } else { ?>
                            <a href="member_detail?id=<?php echo $member_id ?>&type=<?php echo $row_member['register_type'] ?>&level=<?php echo $row_member['register_level'] ?>" target="_blank"><?php echo $row_member['member_code'] ?></a>
                        <?php } ?>
                        <br>
                        <?php echo $title_th . " " . bigsara_decode($row_member['member_name'], $key) ?><br>
                        <?php echo $title_en . " " . bigsara_decode($row_member['member_name_en'], $key) ?>
                        <br>
                        <span class="badge badge-info p-2"><?php echo $row_race['ct_nameTHA']; ?></span>
                        <span class="badge badge-success p-2"><?php echo  $register_level ?></span>
                        <span class="badge badge-primary p-2"><?php echo  $register_type ?></span>
                    </td>
                    <td>
                        <?php echo $row_member['company_name'] ?> <br>
                        โทร : <?php echo bigsara_decode($row_member['company_phone'], $key); ?><br>
                        Email : <?php echo bigsara_decode($row_member['company_email'], $key); ?><br>
                    </td>
                    <td>
                        <?php echo number_format($row_member['payment_amount']-$row_member['discount_amount'],2) ?>
                    </td>
                    <td>
                    <a href="https://efa-member.com/backoffice/print/receipt_new?id=<?php echo $row_member['member_id']; ?>" class="btn btn-warning" target="_blank">View</a>
                    </td>
                </tr>
            <?php $i++;
            } ?>
        </tbody>
    </table>
</div>