invisio v2.0

<?php
include('config/main_function.php');
require("PHPMailer/class.phpmailer.php");

$secure = "cAh3DrJACzw4RbU";
$connection = connectDB($secure);
$date_now = date('Y-m-d');

function sendEmail($to_email, $mail_title, $mail_body)
{

$mail_txt = "<html><head><title>$mail_title</title></head>
				<body>";

$mail_txt .= $mail_body;

$mail_txt .= "</body></html>";

$mail = new PHPMailer();

$body = $mail_txt;

$mail->CharSet = "utf-8";
    $mail->IsHTML(true);
    $mail->IsSMTP();
    $mail->SMTPDebug = 1;
    $mail->Debugoutput = 'html';
    $mail->SMTPSecure = "ssl";
    $mail->Host = "smtp.gmail.com"; // SMTP server
    $mail->Port = 465; // พอร์ท
    // $mail->Username = "hiroshimatsumoto98@gmail.com"; // account SMTP
    // $mail->Password = "rinqaxygecblmeok"; // รหัสผ่าน SMTP
    $mail->Username = "efa.thailand@gmail.com"; // account SMTP
    $mail->Password = "zmwowuwyvbestzns"; // รหัสผ่าน SMTP

$mail->SMTPAuth = true;

// $mail->SetFrom("hiroshimatsumoto98@gmail.com", "Tester");
    $mail->SetFrom("efa.thailand@gmail.com", "สมาคมผู้ประกอบการนำคนต่างด้าวมาทำงานในประเทศ (ประเทศไทย)");

$mail->Subject = "$mail_title";
    $mail->MsgHTML($body);
    $mail->AddAddress($to_email, "email");

if (!$mail->Send()) {
        return 0;
    } else {
        return 1;
    }
}

// $member_id = '2358478306';
$today = date('Y-m-d', strtotime('+1 month'));
// $today = date('Y-m-d');

// $today = '2024-10-20';
$ch_time = "SELECT * FROM tbl_member m
LEFT JOIN tbl_member_detail d ON d.member_id = m.member_id
WHERE m.expire_cer_date = '$today'";
$res_ch = mysqli_query($connection, $ch_time);

if ($res_ch->num_rows > 0) {
    while ($row_ch = mysqli_fetch_assoc($res_ch)) {

$member_id = $row_ch['member_id'];
        $username = $row_ch['email'];
        $register_level = $row_ch['register_level'];

$sql_package = "SELECT * FROM tbl_package_price WHERE member_type = '$register_level'";
        $rs_package = mysqli_query($connection, $sql_package);
        $row_package = mysqli_fetch_array($rs_package);

$payment_id = getRandomID(10, 'tbl_member_payment', 'payment_id');
        $gb_ref_no = getRandomID2(15, 'tbl_member_payment', 'gb_ref_no');
        $invoice_number = getRunNO($size = 4, "INV" . $text = date('y') + 43. . date('m'), 'tbl_member_payment', 'invoice_number');

$cost_per_unit = $row_package['unit_price'];
        $total_amount = $row_package['unit_price'];

$payment_insert = "INSERT INTO tbl_member_payment SET
                            payment_id = '$payment_id'
                            ,member_id = '$member_id'
                            ,gb_ref_no = '$gb_ref_no'
                            ,member_level = '$register_level'
                            ,invoice_number ='$invoice_number'
                            ,payment_amount = '{$row_package['unit_price']}'
                        ;";

// $member_id = $row_ch['member_id'];
        $rs_payment_insert = mysqli_query($connection, $payment_insert);

$renew_id  = getRandomID2(10, 'tbl_member_renew', 'renew_id');
        $expire_cer_date = date('Y-m-d', strtotime($row_ch['expire_cer_date']));
        $sql_renew = "INSERT INTO tbl_member_renew SET 
                renew_id ='$renew_id'
                , member_id ='$member_id'
                , expire_cer_date ='$expire_cer_date'
                , invoice_id = '$invoice_id'
                , payment_id = '$payment_id'";
        $res_renew = mysqli_query($connection, $sql_renew);

if ($rs_payment_insert && $res_renew) {

$invoice_id  = getRandomID(10, 'tbl_invoice_head', 'invoice_id');
            $invoice_no = $invoice_number;

$create_user_id = '0000000000';
            $receipt_date = date('Y-m-d');

$customer_name = $row_ch['member_name'];
            $customer_tax_no = $row_ch['company_tax_no'];
            $customer_phone = $row_ch['phone'];
            $customer_address = $row_ch['card_address'];
            $customer_email = $row_ch['email'];
            $job_name = 'ต่ออายุการเป็นสมาชิก';

$sql_inv_head = "INSERT INTO tbl_invoice_head SET
            invoice_id  = '$invoice_id',
            create_user_id = '$create_user_id',
            invoice_no = '$invoice_no',
            invoice_date = '$date_now',
            job_name = '$job_name',
            member_id = '$member_id',
            payment_id = '$payment_id',
            customer_name = '$customer_name',
            customer_tax_no = '$customer_tax_no',
            customer_phone = '$customer_phone',
            customer_address = '$customer_address',
            customer_email = '$customer_email'
            ";

$res_inv_head = mysqli_query($connection, $sql_inv_head)  or die($connection->error);

if ($res_inv_head) {

$product_name = $job_name;
                $quantity = '1';
                $unit = 'รายการ';
                $invoice_detail_id  = getRandomID(10, 'tbl_invoice_detail', 'invoice_detail_id');
                $discount_per_unit = 0.00;
                $list_order = list_order("tbl_invoice_detail", "list_order", "WHERE invoice_id = ' . $invoice_id. '");

$sql_insert = "INSERT INTO tbl_invoice_detail SET
                            invoice_detail_id = '$invoice_detail_id'
                            ,invoice_id = '$invoice_id'
                            ,product_name = '$product_name'
                            ,quantity = '$quantity'
                            ,unit = '$unit'
                            ,cost_per_unit = '$cost_per_unit'
                            ,discount_type = ''
                            ,discount_per_unit = '$discount_per_unit'
                            ,total_amount = '$total_amount'
                            ,list_order = '$list_order'";

$rs_insert = mysqli_query($connection, $sql_insert) or die($connection->error);

if ($rs_insert) {

// อัพเดทส่วนหัว

$sqlFindHead = "SELECT a.*
                                              ,(SELECT SUM(qd.total_amount) as total_detail FROM tbl_invoice_detail qd WHERE qd.invoice_id = a.invoice_id) as total_detail 
                                        FROM tbl_invoice_head a WHERE a.invoice_id = '$invoice_id';";
                    $rsFindHead = mysqli_query($connection, $sqlFindHead) or die($connection->error);
                    $rowFindHead = mysqli_fetch_array($rsFindHead);

// จำนวนทั้งหมดล่าสุด
                    $total_amount_head  = $rowFindHead['total_detail'] + $total_amount;
                    // มี Vat ไหม
                    $vat_status = $rowFindHead['vat_status'];
                    // มี หัก ณ ที่ขายไหม
                    $wht_percent = $rowFindHead['wht_percent'];
                    // มีส่วนลดไหม
                    $discount_type_head = $rowFindHead['discount_type'];
                    $discount_amount = $rowFindHead['discount_amount'];

if ($discount_type_head == 1) {

$temp1 = $total_amount_head -  $discount_amount_head;
                    } else if ($discount_type_head == 2) {
                        $temp1 = $total_amount_head - ($total_amount_head *  $discount_amount_head / 100);
                    } else {
                        $temp1 = $total_amount_head;
                    }

// บวก VAT เข้าไป ถ้ามี VAT
                    if ($rowFindHead['vat_status'] == 1) {
                        $vat_amount = $temp1 * 7;
                        $temp2 = $temp1 + ($temp1 * 7 / 100);
                    } else {

$temp2 = $temp1;
                    }
                    // ถ้ามี หัก ณ ที่จ่าย
                    if ($rowFindHead['wht_percent'] != 0) {
                        $wht_amount =  ($temp2 * $rowFindHead['wht_percent'] / 100);
                        $temp3 = $temp2 - $wht_amount;
                        $wht = " , wht_amount = '$wht_amount'";
                    } else {
                        $temp3 = $temp2;
                    }

$sqlUpdateHead = "UPDATE tbl_invoice_head SET
                                            total_amount = '$temp3'
                                            $wht
                                         WHERE invoice_id = '$invoice_id';";

$rsUpdateHead = mysqli_query($connection, $sqlUpdateHead) or die($connection->error);

if ($rsUpdateHead) {

$mail_title = "ต่ออายุสมาชิกสมาคมผู้ประกอบการนำคนต่างด้าวมาทำงานในประเทศ (ประเทศไทย)";
 $mail_body = "
 เรียน ผู้สมัคร 
 แจ้งเตือนหมดอายุการเป็นสามาชิกท่านสามารถต่ออายุโดยชำระเงินตามรายละเอียดในลิ้งด้านล่าง
 <a href='https://efa-member.com/print/inv_renew?inv=$invoice_id'>รายละเอียดใบแจ้งหนี้</a>
 ท่านสามารถต่ออายุโดยชำระเงินที่ลิ้งด้านล่างนี้
 <a href='https://efa.or.th/payment_renew?payment=$invoice_id'>ชำระเงินต่ออายุสมาชิก</a>
 ขอบคุณที่เข้าร่วมกับเรา
 สมาคมผู้ประกอบการนำคนต่างด้าวมาทำงานในประเทศ (ประเทศไทย)
 034-112-568
 efa.thailand@gmail.com
 Website : efa.co.th
 LINE : @efa.thailand";

$mail_result = sendEmail($username, $mail_title, $mail_body);

$arr['result'] = 1;
                    } else {
                        $arr['result'] = 0;
                        $arr['msg'] = 'uphead err.';
                    }
                } else {
                    $arr['result'] = 0;
                    $arr['msg'] = 'inv detail err.';
                }
            } else {
                $arr['result'] = 0;
                $arr['msg'] = 'inv err.';
            }
        } else {
            $arr['result'] = 0;
            $arr['msg'] = 'payment err.';
        }
    }
} else {
    $arr['result'] = 0;
    $arr['msg'] = 'no member.';
}

echo json_encode($arr);