webadmin.php

/var/www/hkosl.com/imusiccircle/webadmin/_functions.php

<?php
    require_once("configure.php");

    /*if (!function_exists('escape_string')) {
        function escape_string($data){

            //return ($data);

            $replacements = array(
                    "'" => '&#039;',
                    '"' => '&quot;',
                    //"\\" => '\\\\',
            );
            return strtr($data, $replacements);
        }
    }*/

    if (!function_exists('bind_pdo')) {
        function bind_pdo($sql, $parameters = NULL, $action = NULL, $escape = true)
        {
            global $dbh;

            if ($action == "insert" || $action == "update" || $action == "delete" || empty($action)) {
                if (!($sth = $dbh->prepare($sql))) {
                    throw new Exception('[' . $sth->errorCode() . ']: ' . print_r($sth->errorInfo()));
                }

                if (!$sth->execute($parameters)) {
                    throw new Exception('[' . $sth->errorCode() . ']: ' . print_r($sth->errorInfo()));
                }
            }

            if ($action == "selectone") {
                if (!($sth = $dbh->prepare($sql))) {
                    throw new Exception('[' . $sth->errorCode() . ']: ' . print_r($sth->errorInfo()));
                }

                if (!$sth->execute($parameters)) {
                    throw new Exception('[' . $sth->errorCode() . ']: ' . print_r($sth->errorInfo()));
                }

                $result = $sth->fetch(PDO::FETCH_ASSOC);
                /*if (!empty($result)) {
                    foreach ($result as $key => $row) {
                        $result[$key] = escape_string($result[$key]);
                    }
                }*/
                return $result;

            }

            if ($action == "selectall") {
                if (!($sth = $dbh->prepare($sql))) {
                    throw new Exception('[' . $sth->errorCode() . ']: ' . print_r($sth->errorInfo()));
                }

                if (!$sth->execute($parameters)) {
                    throw new Exception('[' . $sth->errorCode() . ']: ' . print_r($sth->errorInfo()));
                }

                $result = $sth->fetchAll(PDO::FETCH_ASSOC);
                /*if (!empty($result)) {
                    foreach ($result as $key => $row) {
                        foreach ($row as $key2 => $row2) {
                            $result[$key][$key2] = escape_string($result[$key][$key2]);
                        }
                    }
                }*/
                return $result;
            }

            if ($action == "dump") {
                return dump_sql($sql, $parameters);
            }

        }
    }


    if (!function_exists('dump_sql')) {
        function dump_sql($sql, $parameters)
        {
            $keys = array();

            # build a regular expression for each parameter
            foreach ($parameters as $key => $value) {
                if (is_string($key)) {
                    $keys[] = '/:' . $key . '/';
                } else {
                    $keys[] = '/[?]/';
                }
            }

            foreach ($parameters as $key2 => $value) {
                $parameters[$key2] = "'" . $value . "'";
            }

            $sql = preg_replace($keys, $parameters, $sql, 1, $count);

            #trigger_error('replaced '.$count.' keys');

            return $sql;
        }
    }

    if (!function_exists('paypal_log')) {
        function paypal_log($code, $data)
        {
            /*$date = new DateTime();
            $date->modify("-2 month");

            $sql = "delete from api_log where createdate <= ?";
            $parameters = array($date->format("Y-m-d"));
            bind_pdo($sql, $parameters);*/

            unset($data[1]["nvpReqArray"]["PWD"]);
            unset($data[1]["nvpReqArray"]["USER"]);
            unset($data[1]["nvpReqArray"]["SIGNATURE"]);

            $sql        = "insert into paypal_log (code, log_data, createdate) value (?,?,?)";
            $parameters = array($code, json_encode($data), date("Y-m-d H:i:s"));
            bind_pdo($sql, $parameters);
        }
    }

    if (!function_exists('debug_log')) {
        function debug_log($code, $data)
        {
            $date = new DateTime();
            $date->modify("-2 month");

            $sql        = "delete from debug_log where createdate <= ?";
            $parameters = array($date->format("Y-m-d"));
            bind_pdo($sql, $parameters);

            $sql        = "insert into debug_log (code, log_data, createdate) value (?,?,?)";
            $parameters = array($code, json_encode($data), date("Y-m-d H:i:s"));
            bind_pdo($sql, $parameters);
        }
    }

    /***************************************************
     * _site info
     */

    $sql        = "SELECT * FROM site_info WHERE siteinfoid = ? ";
    $parameters = array(1);
    $row0       = bind_pdo($sql, $parameters, "selectone");
    $site_info  = $row0;


    /***************************************************
     * _language setting
     */

    $sql           = "SELECT * FROM sys_lang WHERE langstatus = 1 ORDER BY langsort ASC ";
    $sys_lang_info = bind_pdo($sql, NULL, "selectall");
    foreach ($sys_lang_info as $sys_lang) {
        $arraylangcode[$sys_lang['langcode']] = $sys_lang['langname'];
    }

    if (!function_exists('get_client_ip')) {
        function get_client_ip()
        {
            $ipaddress = '';
            if (isset($_SERVER['HTTP_CLIENT_IP']))
                $ipaddress = $_SERVER['HTTP_CLIENT_IP'];
            else if (isset($_SERVER['HTTP_X_FORWARDED_FOR']))
                $ipaddress = $_SERVER['HTTP_X_FORWARDED_FOR'];
            else if (isset($_SERVER['HTTP_X_FORWARDED']))
                $ipaddress = $_SERVER['HTTP_X_FORWARDED'];
            else if (isset($_SERVER['HTTP_FORWARDED_FOR']))
                $ipaddress = $_SERVER['HTTP_FORWARDED_FOR'];
            else if (isset($_SERVER['HTTP_FORWARDED']))
                $ipaddress = $_SERVER['HTTP_FORWARDED'];
            else if (isset($_SERVER['REMOTE_ADDR']))
                $ipaddress = $_SERVER['REMOTE_ADDR'];
            else
                $ipaddress = 'UNKNOWN';
            return $ipaddress;
        }
    }


    if (!function_exists('get_langcode')) {
        function get_langcode()
        {
            if (strpos($_SERVER["REQUEST_URI"], "webadmin") !== false) {
                if (!isset($_SESSION["wlangcode"])) {
                    $_SESSION["wlangcode"] = "en";
                }

                $this_langcode = $_SESSION["wlangcode"];
            } else {
                if (!isset($_SESSION["langcode"])) {
                    $_SESSION["langcode"] = "cn";
                }

                $this_langcode = $_SESSION["langcode"];
            }

            return $this_langcode;
        }
    }

    //$langcode = get_langcode();

    /*if (!function_exists('lang')) {
        function lang($text, $lang=null)
        {
            if(empty($lang)){
                $this_langcode = get_langcode();
            }else{
                $this_langcode = $lang;
            }

            $sql        = "select * from `language` where BINARY name_en = ? and deleted = ? limit 1";
            $parameters = array($text, 0);
            $result     = bind_pdo($sql, $parameters, "selectone");

            if (!empty($result)) {
                return $result["name_" . $this_langcode];
            } else {
                //temp

                if(true){
                    $sql = "select * from `language_pending` where BINARY name_en = ? and deleted = ? limit 1";
                    $parameters = array($text, 0);
                    $is_pending = bind_pdo($sql, $parameters, "selectone");
                    if(empty($is_pending)){
                        $sql = "INSERT INTO `language_pending` (name_en, deleted) VALUES (?, ?)";
                        $parameters = array($text, 0);
                        bind_pdo($sql, $parameters, "insert");
                    }
                }
                return $text;
                //return $text . "(missing)";
                //return null;
            }
        }
    }*/

    if (!function_exists('_h')) {
        function _h($str)
        {
            return htmlspecialchars($str, ENT_QUOTES);
        }
    }

    // lang() with html escapse
    if (!function_exists('_lang')) {
        function _lang($text, $lang = null)
        {
            // TODO: escapse the translated string after frontend is changed
            return _h(call_user_func_array('lang', func_get_args()));
            //return (lang($text));
        }
    }

    if (!function_exists('get_master_type_code')) {
        function get_master_type_code($typeid, $code = NULL)
        {
            if (!empty($code)) {
                $sql        = "select * from master_type_code where typeid = ? and code = ? and deleted = ? order by sort ASC, name_en ASC";
                $parameters = array($typeid, $code, 0);
                $result     = bind_pdo($sql, $parameters, "selectone");
            } else {
                $sql        = "select * from master_type_code where typeid = ? and deleted = ? order by sort ASC, name_en ASC";
                $parameters = array($typeid, 0);
                $result     = bind_pdo($sql, $parameters, "selectall");
            }

            return $result;
        }
    }

    if (!function_exists('get_master_type_code_by_id')) {
        function get_master_type_code_by_id($id = NULL)
        {
            if (!empty($id)) {
                $sql        = "select * from master_type_code where id = ? ";
                $parameters = array($id);
                $result     = bind_pdo($sql, $parameters, "selectone");
            } else {
                $sql        = "select * from master_type_code where deleted = ? order by name_en ASC";
                $parameters = array(0);
                $result     = bind_pdo($sql, $parameters, "selectall");
            }

            return $result;
        }
    }

    if (isset($_GET["msg"]) && (int)$_GET["msg"] == $_GET["msg"]) {
        $msg_master_info = get_master_type_code("WEBADMIN", $_GET["msg"]);
        $msg             = $msg_master_info["name_" . $_SESSION["wlangcode"]];
        $_GET["msg"]     = $msg_master_info["name_" . $_SESSION["wlangcode"]];
    }

    if (!function_exists('random_string')) {
        function random_string($length = 8)
        {
            $chars         = "abcdefghijkmnpqrstuvwxyzABCDEFGHIJKLMNPQRSTUVWXYZ23456789";
            $random_string = substr(str_shuffle($chars), 0, $length);
            return $random_string;
        }
    }

    if (!function_exists('last_url')) {
        function last_url()
        {
            $protocol = strpos(strtolower($_SERVER['SERVER_PROTOCOL']), 'https')
            === false ? 'http' : 'https';
            $host     = $_SERVER['HTTP_HOST'];
            /*$script   = $_SERVER['SCRIPT_NAME'];
            $params   = $_SERVER['QUERY_STRING'];*/

            //$lastUrl = $protocol . '://' . $host . $script . '?' . $params;

            $path    = $_SERVER['REQUEST_URI'];
            $lastUrl = $protocol . '://' . $host . $path;

            return $lastUrl;
        }

    }

    //useless
    if (!function_exists('aes_crypt')) {
        function aes_crypt($data, $encrypt_decrypt)
        {

            /*set_include_path(get_include_path() . PATH_SEPARATOR . 'phpseclib0.3.8');
            include('Net/SSH2.php');
            include('Crypt/RSA.php');*/

            set_include_path(get_include_path() . PATH_SEPARATOR . 'phpseclib1.0.1');

            if (!class_exists('Crypt_AES')) {
                include_once('phpseclib1.0.1/Crypt/AES.php');
            }

            include_once('phpseclib1.0.1/Crypt/Random.php');

            $aes_crypt = new Crypt_AES();

            $aes_crypt->setKey('*9Wq*LGBE8GJch?wZUC+8n?WzZb*Y*Lq');
            //$aes_crypt->setIV(crypt_random_string($aes_crypt->getBlockLength() >> 3));
            $aes_crypt->setIV('d$w5aQCY-STP*MG_hfDxP4eC?uDP@szr');

            if ($encrypt_decrypt == 1) {
                if (isset($data)) {
                    return base64_encode($aes_crypt->encrypt($data));
                } else {
                    return;
                }
            }

            if ($encrypt_decrypt == 2) {
                if (isset($data)) {
                    return $aes_crypt->decrypt(base64_decode($data));
                } else {
                    return;
                }
            }
        }
    }

    if (!function_exists('aes_crypt2')) {
        function aes_crypt2($data, $encrypt_decrypt)
        {

            /*set_include_path(get_include_path() . PATH_SEPARATOR . 'phpseclib0.3.8');
            include('Net/SSH2.php');
            include('Crypt/RSA.php');*/

            set_include_path(get_include_path() . PATH_SEPARATOR . 'phpseclib1.0.1');

            if (!class_exists('Crypt_AES')) {
                include_once('phpseclib1.0.1/Crypt/AES.php');
            }

            include_once('phpseclib1.0.1/Crypt/Random.php');

            $aes_crypt = new Crypt_AES();

            $aes_crypt->setKey('*9Wq*LGBE8GJch?wZUC+8n?WzZb*Y*Lq');
            //$aes_crypt->setIV(crypt_random_string($aes_crypt->getBlockLength() >> 3));
            $aes_crypt->setIV('d$w5aQCY-STP*MG_hfDxP4eC?uDP@szr');

            if ($encrypt_decrypt == 1) {
                if (isset($data)) {
                    return base64_encode($aes_crypt->encrypt($data));
                } else {
                    return;
                }
            }

            if ($encrypt_decrypt == 2) {
                if (isset($data)) {
                    return $aes_crypt->decrypt(base64_decode($data));
                } else {
                    return;
                }
            }
        }
    }


    if (!function_exists('matched_option')) {
        function matched_option($data1, $data2, $type)
        {
            if ($data1 == $data2) {
                if ($type == "checkbox" || $type == "radiobutton") {
                    return "checked";
                } else if ($type == "select") {
                    return "selected";
                } else {
                }

            }
        }
    }

    if (!function_exists('get_config_setting')) {
        function get_config_setting($config_id)
        {
            global $dbh;

            $sql        = "select * from config where old_record = ? and config_id = ? ";
            $parameters = array("0", $config_id);
            if (!($sth = $dbh->prepare($sql))) {
                throw new Exception('[' . $sth->errorCode() . ']: ' . print_r($sth->errorInfo()));
            }

            if (!$sth->execute($parameters)) {
                throw new Exception('[' . $sth->errorCode() . ']: ' . print_r($sth->errorInfo()));
            }

            $config_info = $sth->fetch(PDO::FETCH_ASSOC);
            return $config_info;
        }
    }


    if (!function_exists('select_options')) {
        function select_options($rows, $selected_value, $settings = array())
        {
            $settings['value_field'] = $settings['value_field'] ?: 'code';
            $settings['label_fn']    = $settings['label_fn'] ?: function ($row) {
                return "{$row['code']} - {$row['name_en']}";
            };

            $str = '';
            foreach ($rows as $row) {
                $str .= "<option value='" . $row[$settings['value_field']] . "' " . ($row[$settings['value_field']] == $selected_value ? "selected='selected'" : "") . ">" . $settings['label_fn']($row) . "</option>";
            }
            return $str;
        }
    }


    if (!function_exists('startsWith')) {
        function startsWith($haystack, $needle)
        {
            $length = strlen($needle);
            return (substr($haystack, 0, $length) === $needle);
        }
    }

    if (!function_exists('check_upload_path')) {
        function check_upload_path($img_file)
        {
            $session_path_str  = "/uploader/" . $_SESSION['KCFINDER']['uploadURL'];
            $session_path_long = strlen($session_path_str);
            $path_error        = 0;
            foreach ($img_file as $key2 => $pathname) {
                if ($key2 && $pathname) {
                    $submit_path_str  = substr($pathname, 0, $session_path_long);
                    $submit_path_long = strlen($submit_path_str);
                    $file             = str_replace('..', '', $pathname);
                    if ($session_path_long <> $submit_path_long || $session_path_str <> $submit_path_str || !startsWith($file, $session_path_str)) {
                        $path_error = 1;
                    }
                }
            }
        }
    }

    if (!function_exists('get_order_status')) {
        function get_order_status($status = null)
        {
            $array = array('approved' => '處理中', 'refunded' => '處理中', 'process' => '處理中', 'paid' => '已付款', 'cancel' => '已取消', 'fail' => '交易失易', 'reject' => '已拒絕');
            return $array[$status];
        }
    }

    if (!function_exists('get_course_detail')) {
        function get_course_detail($course_id = null, $status = 2, $deleted = 0)
        {
            global $dbh;
            if ($course_id) {
                $sql        = "select o.*,od.id as id2,od.complaint,od.datetime,od.tutor_status,od.student_status,od.solution_status, od.deduct_tutorial_mins, od.deduct_tutorial_mins_date from order_detail as od,`order` as o where o.id = od.order_id and od.id = ? and o.deleted = ? and o.status='paid'";
                $parameters = array($course_id, $deleted);
                return bind_pdo($sql, $parameters, "selectone");
            }
        }
    }

    if (!function_exists('get_order')) {
        function get_order($order_id = null, $status, $deleted = 0)
        {
            global $dbh;
            if ($order_id) {
                $sql        = "select * from `order` where id = ? and deleted = ? LIMIT 1";
                $parameters = array($order_id, $deleted);
                return bind_pdo($sql, $parameters, "selectone");
            } else {
                if ($status) {
                    if ($status == 'process') {
                        $exsql = 'and status in ("process","approved")';
                    } else {
                        $exsql = 'and status = "' . $status . '"';
                    }
                }
                $sql        = "select * from `order` where deleted = ? " . $exsql . " order by createdate desc";
                $parameters = array($deleted);
                return bind_pdo($sql, $parameters, "selectall");
            }
        }
    }

    if (!function_exists('send_order_notification')) {
        function send_order_notification($status, $id)
        {
            //
            if ($status == 'approved' || $status == 'process' || $status == 'paid' || $status == 'reject' || $status == 'cancel') {
                $sql                        = "SELECT * FROM site_info WHERE siteinfoid = ? ";
                $parameters                 = array(1);
                $site_info                  = bind_pdo($sql, $parameters, "selectone");
                $lang                       = 'cn';
                $sender_name                = $site_info['companyname_' . $lang];
                $sender_email               = $site_info['enquiryemail'];
                $content_arr['member_name'] = $record_member['name'];
                if ($lang == 'cn') {
                    $subject = $site_info['companyname_' . $lang] . ' - 訂單處理狀況';
                }
                $sql          = "select * from `order` as tb 
                        where `id` = ? and deleted = ? LIMIT 1";
                $parameters   = array($id, 0);
                $record_order = bind_pdo($sql, $parameters, "selectone");
                $sender_name  = $site_info['companyname_' . $lang];
                if ($status == 'paid') {
                    $sql         = "select datetime from order_detail where order_id = ?";
                    $parameters  = array($record_order['id']);
                    $rows_detail = bind_pdo($sql, $parameters, "selectall");

                    $sql        = "select postjob_no,mas_instrument from student_postjob where id = ? and deleted = ?";
                    $parameters = array($record_order['job_id'], 0);
                    $row_job    = bind_pdo($sql, $parameters, "selectone");

                    $sql        = "select tutor_no,nickname as name from tutor_main where id = ? and deleted = ?";
                    $parameters = array($record_order['tutormain_id'], 0);
                    $row_tutor  = bind_pdo($sql, $parameters, "selectone");

                    $sql         = "select email,student_no,contactname as name from student_main where id = ? and deleted = ?";
                    $parameters  = array($record_order['studentmain_id'], 0);
                    $row_student = bind_pdo($sql, $parameters, "selectone");

                    $sql              = "select name_cn from master_type_code where id = ? and typeid = ? and deleted = ?";
                    $parameters       = array($record_order['mas_residencecode'], 'TEACH_POSITIONCODE', 0);
                    $row_positioncode = bind_pdo($sql, $parameters, "selectone");

                    $sql                      = "select name_cn from master_type_code where id = ? and typeid = ? and deleted = ?";
                    $parameters               = array($record_order['course_venus'], 'TEACH_VENUS', 0);
                    $row_venus                = bind_pdo($sql, $parameters, "selectone");
                    $sql                      = "select name_en from master_type_code where id = ? and typeid = 'INSTRUMENT' LIMIT 1";
                    $parameters               = array($row_job['mas_instrument']);
                    $row_instrument           = bind_pdo($sql, $parameters, "selectone");
                    $content_arr['titlename'] = $site_info['companyname_' . $lang] . '- ' . $record_order['order_no'] . '確認付款';
                    $content_arr['msg']       = '你好,<br><br>
                                           我們已經確認了你的付款
                                           <br><br>
                                           <table width="100%" border="0" cellpadding="0" cellspacing="1" style="font-size:12px;">
                                              <tr>
                                                <th width="120" align="left" valign="top" class="">Paypal 編號</th>
                                                <td width="5" valign="top" class="">:&nbsp;</td>
                                                <td class=""><div class="col-sm-8 form-group">' . $record_order['paypal_id'] . '</div></td>
                                              </tr>
                                              <tr>
                                                <th width="120" align="left" valign="top" class="">Paypal 處理日期</th>
                                                <td width="5" valign="top" class="">:&nbsp;</td>
                                                <td class=""><div class="col-sm-8 form-group">
                                                    ' . $record_order['paypal_date'] . '
                                                  </div></td>
                                              </tr>
                                              <tr>
                                                <th width="120" align="left" valign="top" height="40">' . lang("Order No.") . '</th>
                                                <td valign="top" class="">&nbsp;</td>
                                                <td valign="top" class=""><div class="col-sm-8 form-group">' . $record_order['order_no'] . '</div></td>
                                              </tr>
                                              <tr>
                                                <th width="120" align="left" valign="top">' . lang("工作號碼") . '</th>
                                                <td valign="top" class="">&nbsp;</td>
                                                <td valign="top" class=""><div class="col-sm-8 form-group">
                                                    ' . $row_job['postjob_no'] . '
                                                  </div></td>
                                              </tr>
                                              <tr>
                                                <th width="120" align="left" valign="top">學生</th>
                                                <td width="5" valign="top" class="">:&nbsp;</td>
                                                <td class=""><div class="col-sm-8 form-group">' . $row_student['student_no'] . ' ' . $row_student['name'] . '</div></td>
                                              </tr>
                                              <tr>
                                                <th width="120" align="left" valign="top">導師</th>
                                                <td width="5" valign="top" class="">:&nbsp;</td>
                                                <td class=""><div class="col-sm-8 form-group">' . $row_tutor['tutor_no'] . ' ' . $row_tutor['name'] . '</div></td>
                                              </tr>
                                              <tr>
                                                <td width="120" align="left" valign="top" class="">上課地區</td>
                                                <td width="5" valign="top" class="">:&nbsp;</td>
                                                <td class=""><div class="col-sm-8 form-group">
                                                    ' . $row_positioncode["name_cn"] . '
                                                  </div></td>
                                              </tr>
                                              <tr>
                                                <td width="120" align="left" valign="top" class="">上課地區</td>
                                                <td width="5" valign="top" class="">:&nbsp;</td>
                                                <td class=""><div class="col-sm-8 form-group">' . $row_venus['name_cn'] . '</div></td>
                                              </tr>
                                              <tr>
                                                <td width="120" align="left" valign="top" class="">學習級數</td>
                                                <td width="5" valign="top" class="">:&nbsp;</td>
                                                <td class=""><div class="col-sm-8 form-group">';
                    if ($record_order['order_type'] == 'try') {
                        if ($record_order['level'] == 1) {
                            $content_arr['msg'] .= '1 - 2';
                        } elseif ($record_order['level'] == 2) {
                            $content_arr['msg'] .= '3 - 5';
                        } elseif ($record_order['level'] == 3) {
                            $content_arr['msg'] .= _lang("6 or above 6");
                        }
                    } else if ($record_order['order_type'] == 'formal') {
                        if ($record_order['level'] < 8) {
                            $content_arr['msg'] .= $record_order['level'];
                        } else if ($record_order['level'] == 9) {
                            $content_arr['msg'] .= '8級以上';
                        }
                    }
                    $content_arr['msg'] .= '
                                                  </div></td>
                                              </tr>
                                              <tr>
                                                <td width="120" align="left" valign="top" class="">學習時間長度</td>
                                                <td width="5" valign="top" class="">:&nbsp;</td>
                                                <td class=""><div class="col-sm-8 form-group">
                                                    ' . $record_order['course_length'] . ' ' . _lang("mins") . '
                                                  </div></td>
                                              </tr>
                                              <tr>
                                                <td width="120" align="left" valign="top" class="">學習堂數</td>
                                                <td width="5" valign="top" class="">:&nbsp;</td>
                                                <td class=""><div class="col-sm-8 form-group">' . $record_order['course_count'] . '
                                                  </div></td>
                                              </tr>
                                              <tr>
                                                <td width="120" align="left" valign="top" class="">上課日期 / 時間</td>
                                                <td width="5" valign="top" class="">:&nbsp;</td>
                                                <td class=""><div class="col-sm-8 form-group">';
                    foreach ($rows_detail as $i => $row_detail) {
                        $content_arr['msg'] .= '
                                                    <div>' . ($i + 1) . '. ' . $row_detail['datetime'] . '</div>
                                                    <div>';
                        if ($row['status'] == 'paid') {
                            $content_arr['msg'] .= '
                                                    <a href="course_form.php?id=' . $row_detail['id'] . '" class="btn btn-default"><i class="fa fa-link" aria-hidden="true"></i> 課堂資料</a>';
                        }
                        $content_arr['msg'] .= '
                                                    </div>';
                    }
                    $content_arr['msg'] .= '
                                                  </div></td>
                                              </tr>
                                              <tr>
                                                <td width="120" align="left" valign="top" class="">每堂上課費用</td>
                                                <td width="5" valign="top" class="">:&nbsp;</td>
                                                <td class=""><div class="col-sm-8 form-group"> $ ' . intval($record_order['fee']) . '</div></td>
                                              </tr>
                                              <tr>
                                                <td width="120" align="left" valign="top" class="">每堂上門附加費</td>
                                                <td width="5" valign="top" class="">:&nbsp;</td>
                                                <td class=""><div class="col-sm-8 form-group"> $ ' . intval($record_order['extra_fee']) . '</div></td>
                                              </tr>
                                              <tr>
                                                <td width="120" align="left" valign="top" class="">總費用</td>
                                                <td width="5" valign="top" class="">:&nbsp;</td>
                                                <td class=""><div class="col-sm-8 form-group"> $ <span>' . (intval($record_order['total_fee']) - intval($record_order['coupon_fee'])) . '</span>
                                                  </div></td>
                                              </tr>';
                    if ($row['coupon_code']) {
                        $content_arr['msg'] .= '
                                                  <tr>
                                                    <td width="120" align="left" valign="top" class="">優惠劵折扣</td>
                                                    <td width="5" valign="top" class="">:&nbsp;</td>
                                                    <td class=""><div class="col-sm-8 form-group">- $ <span>' . (intval($row['coupon_fee'])) . '</span>
                                                      </div></td>
                                                  </tr>
                                                  ';
                    }
                    $content_arr['msg'] .= '
                                            </table>
                                       <br>
                                       請登入<a href="'.$site_info["url"].'" target="_blank">'.$site_info["url"].'</a>繼續享用我們為你提供的服務。
                                       <br><br>
                                       如有任何問題，請電郵至cs@musiccircle.hk跟我們的客戶服務部聯絡。
                                       <br><br>
                                       MusicCircle 客戶服務
                                       ';
                    $content_arr['date'] = date('Y-m-d');
                    $content_arr['time'] = date('H:i:s');
                    $map_file            = 'email/page.html';
                    $to                  = aes_crypt($row_student['email'], 2);
                    sendEmail($to, $site_info['companyname_' . $lang] . '- ' . '確認付款', $content_arr, $sender_name, $sender_email, $map_file);
                    $to = 'cs@musiccircle.hk';
                    $to = 'benny2507@yhaoo.com.hk';
                    sendEmail($to, $site_info['companyname_' . $lang] . '- ' . '確認付款', $content_arr, $sender_name, $sender_email, $map_file);
                    $row_member = get_teacher($record_order['tutormain_id']);
                    $name       = '學生';
                } else if ($record_order['from_type'] == 'student' && $status) {
                    if ($status == 'process' || $status == 'cancel') {
                        $row_member = get_teacher($record_order['tutormain_id']);
                    } else {
                        $row_member = get_student($record_order['studentmain_id']);
                    }
                    $name = '導師';
                } else if ($record_order['from_type'] == 'tutor' && $status) {
                    if ($status == 'process' || $status == 'cancel') {
                        $row_member = get_student($record_order['studentmain_id']);
                    } else {
                        $row_member = get_teacher($record_order['tutormain_id']);
                    }
                    $name = '學生';
                }
                $to         = aes_crypt($row_member['email'], 2);
                $cmsloginid = $row_member['cmsloginid'];
                if ($status == 'approved') {
                    $order_status = 'ORDER_ACCEPT';
                    $title        = '已確認了你的試堂';
                } else if ($status == 'paid') {
                    $order_status = 'ORDER_PAID';
                    $title        = '已付款你的試堂';
                } else if ($status == 'cancel') {
                    $order_status = 'ORDER_CANCEL';
                    $title        = '已取消你的試堂';
                } else if ($status == 'reject') {
                    $order_status = 'ORDER_REJECT';
                    $title        = '已拒絕你的試堂';
                } else if ($status == 'process') {
                    $order_status = 'ORDER_PROCESS';
                    $title        = '已邀請你的試堂';
                } elseif ($status == 'refunded') {
                    $order_status = 'ORDER_REFUND';
                }
                $content_arr['titlename'] = $site_info['companyname_' . $lang] . '- ' . $record_order['order_no'] . '訂單處理狀況';
                $content_arr['msg']       = '你好,<br><br>
                                       ' . $name . $title . '。
                                       <br><br>
                                       請登入<a href="'.$site_info["url"].'" target="_blank">'.$site_info["url"].'</a>繼續享用我們為你提供的服務。
                                       <br><br>
                                       如有任何問題，請電郵至cs@musiccircle.hk跟我們的客戶服務部聯絡。
                                       <br><br>
                                       MusicCircle 客戶服務
                                       ';
            }
            $content_arr['date'] = date('Y-m-d');
            $content_arr['time'] = date('H:i:s');
            if ($status == 'paid') {
                $map_file = 'email/page.html';
            } else {
                $map_file = '../email/page.html';
            }
            sendEmail($to, $subject, $content_arr, $sender_name, $sender_email, $map_file);
            //
            insert_notification($order_status, 'order', $id, $cmsloginid);
            if ($status == 'paid') {
                insert_notification($order_status, 'order', $id, 0);
            }
        }
    }

    if (!function_exists('get_teacherpostjob')) {
        function get_teacherpostjob($tutor_id = null, $status = 1, $deleted = 0)
        {
            global $dbh;
            if ($tutor_id) {
                $sql        = "select * from tutor_postjob where id = ? and deleted = ? order by createdate DESC";
                $parameters = array($tutor_id, $deleted);
                return bind_pdo($sql, $parameters, "selectone");
            } else {
                //$sql        = "select *,(select count(*) from `order` where job_id = tb.id and (status = 'process' or status = 'paid') and deleted = 0) as count
                $sql        = "select *,(select count(*) from `order` where job_id = tb.id and (status = 'paid') and deleted = 0) as count 
                               from tutor_postjob as tb where deleted = ? order by createdate DESC";
                $parameters = array($deleted);
                return bind_pdo($sql, $parameters, "selectall");
            }
        }
    }

    if (!function_exists('get_studentpostjob')) {
        function get_studentpostjob($student_id = null, $status = 1, $deleted = 0)
        {
            global $dbh;
            if ($student_id) {
                $sql        = "select * from student_postjob where id = ? and deleted = ? order by createdate DESC";
                $parameters = array($student_id, $deleted);
                return bind_pdo($sql, $parameters, "selectone");
            } else {
                $sql        = "select * from student_postjob where deleted = ? order by createdate DESC";
                $parameters = array($deleted);
                return bind_pdo($sql, $parameters, "selectall");
            }
        }
    }

    if (!function_exists('get_studentpostjob2')) {
        function get_studentpostjob2($studentpostjob_id = null, $status = null, $deleted = null)
        {
            global $dbh;

            $where_clause  = "";
            $parameters    = array();
            $select_option = "selectall";

            if (!empty($studentpostjob_id)) {
                $where_clause .= " and id = ?";
                $parameters[]  = $studentpostjob_id;
                $select_option = "selectone";
            }

            if (isset($status)) {
                $where_clause .= " and status = ?";
                $parameters[] = $status;
            }

            if (isset($deleted)) {
                $where_clause .= " and deleted = ?";
                $parameters[] = $deleted;
            }

            $sql = "select * from student_postjob where id > 0 {$where_clause} order by createdate DESC";

            return bind_pdo($sql, $parameters, $select_option);
        }
    }

    if (!function_exists('get_student')) {
        function get_student($student_id = null, $status = 1, $deleted = 0)
        {
            global $dbh;
            if ($student_id) {
                $sql        = "select * from student_main where id = ? and deleted = ? order by student_no DESC";
                $parameters = array($student_id, $deleted);
                return bind_pdo($sql, $parameters, "selectone");
            } else {
                if ($status == 3) {
                    $sql = "select * from student_main where deleted = ? and status <=2 and isemailverified = 0 order by student_no DESC";
                } elseif ($status == 2) {
                    $sql = "select * from student_main where deleted = ? and status > 2 order by student_no DESC";
                } else if ($status == 1) {
                    $sql = "select * from student_main where deleted = ? and status <=2 and isemailverified = 1 order by student_no DESC";
                }
                $parameters = array($deleted);
                return bind_pdo($sql, $parameters, "selectall");
            }
        }
    }

    if (!function_exists('getstudentNo')) {
        function getstudentNo($student_id = null)
        {
            return 'S' . sprintf("%06d", $student_id);
        }
    }

    if (!function_exists('get_teacher')) {
        function get_teacher($teacher_id = null, $status = 2, $deleted = 0)
        {
            global $dbh;
            if ($teacher_id) {
                $sql        = "select * from tutor_main where id = ? and deleted = ?";
                $parameters = array($teacher_id, $deleted);
                return bind_pdo($sql, $parameters, "selectone");
            } else {
                if ($status == 3) {
                    $sql = "select * from tutor_main where deleted = ? and status <=2 and isemailverified = 0 order by tutor_no DESC";
                } elseif ($status == 2) {
                    $sql = "select * from tutor_main where deleted = ? and status > 2 order by tutor_no DESC";
                } else if ($status == 1) {
                    $sql = "select * from tutor_main where deleted = ? and status <=2 and isemailverified = 1 order by tutor_no DESC";
                }
                $parameters = array($deleted);
                return bind_pdo($sql, $parameters, "selectall");
            }
        }
    }

    if (!function_exists('gettutorNo')) {
        function gettutorNo($tutor_id = null)
        {
            return 'T' . sprintf("%06d", $tutor_id);
        }
    }

    if (!function_exists('get_tutor')) {
        function get_tutor($tutor_id = null, $status = null, $deleted = 0)
        {
            //status:  1: new registration, 2: email verified, 3: completed registration
            //$category_id -> mas_instrument
            global $dbh;

            $where_clause = "";
            $parameters   = array($deleted);

            if (!empty($tutor_id)) {
                $where_clause .= " and tutor_main.id = ?";
                $parameters[] = $tutor_id;
            }

            if (isset($status)) {
                $where_clause .= " and tutor_main.status = ?";
                $parameters[] = $status;
            }


            $sql = "select * from tutor_main where tutor_main.deleted = ? {$where_clause} order by tutor_no DESC";

            if (!empty($tutor_id)) {
                return bind_pdo($sql, $parameters, "selectone");
            } else {
                return bind_pdo($sql, $parameters, "selectall");
            }
        }
    }

    if (!function_exists('get_tutor_expectcharge')) {
        function get_tutor_expectcharge($tutor_id = null, $category_id = null, $fee_type = null, $deleted = 0)
        {
            global $dbh;

            $where_clause = "";
            $parameters   = array(0, 4, 0);

            if (!empty($tutor_id)) {
                $where_clause .= " and tutor_main.id = ?";
                $parameters[] = $tutor_id;
            }

            if (!empty($category_id) && $category_id != "ANY") {
                $where_clause .= " and tutor_charge.mas_instrument = ?";
                $parameters[] = $category_id;
            }

            if (!empty($fee_type)) {
                $where_clause .= " and tutor_expectcharge.type = ?";
                $parameters[] = $fee_type;
            }

            /*$sql = "select *, master_type_code.name_" . $_SESSION["langcode"] . " as instrument_name, mtc2.name_" . $_SESSION["langcode"] . " as grade_name from tutor_main
                    INNER JOIN tutor_charge ON tutor_main.id = tutor_charge.tutormain_id
                    INNER JOIN tutor_expectcharge ON tutor_charge.id = tutor_expectcharge.charge_id
                    INNER JOIN master_type_code ON master_type_code.id = tutor_charge.mas_instrument
                    INNER JOIN master_type_code mtc2 ON (mtc2.typeid = 'LEVEL_OF_GRADE' and mtc2.code = tutor_expectcharge.grade)
                    where tutor_charge.deleted = ? and tutor_main.status = ? and tutor_main.deleted = ? {$where_clause}";*/

            $sql = "select tutor_charge.mas_instrument, tutor_expectcharge.charge_30min, tutor_expectcharge.charge_45min, tutor_expectcharge.charge_60min, master_type_code.name_" . $_SESSION["langcode"] . " as instrument_name, mtc2.name_" . $_SESSION["langcode"] . " as grade_name from tutor_main
                    INNER JOIN tutor_charge ON tutor_main.id = tutor_charge.tutormain_id
                    INNER JOIN tutor_expectcharge ON tutor_charge.id = tutor_expectcharge.charge_id
                    INNER JOIN master_type_code ON master_type_code.id = tutor_charge.mas_instrument
                    INNER JOIN master_type_code mtc2 ON (mtc2.typeid = 'LEVEL_OF_GRADE' and mtc2.code = tutor_expectcharge.grade)
                    where tutor_charge.deleted = ? and tutor_main.status = ? and tutor_main.deleted = ? {$where_clause}";

            //debug_log(1, dump_sql($sql, $parameters));
            $tutor_expectcharge = bind_pdo($sql, $parameters, "selectall");


            $min_fee     = 9999999;
            $min_minutes = 9999999;

            $max_fee     = 0;
            $max_minutes = 0;

            $instrument_name  = "";
            $instrument_grade = "";
            $min_category_id  = "";

            if (!empty($tutor_expectcharge)) {
                foreach ($tutor_expectcharge as $key => $row) {
                    if ($row["charge_30min"] < $min_fee && $row["charge_30min"] > 0) {
                        $min_fee     = $row["charge_30min"];
                        $min_minutes = 30;

                        $instrument_name  = $row["instrument_name"];
                        $instrument_grade = $row["grade_name"];
                        $min_category_id  = $row["mas_instrument"];
                    }

                    if ($row["charge_45min"] < $min_fee && $row["charge_45min"] > 0) {
                        $min_fee     = $row["charge_45min"];
                        $min_minutes = 45;

                        $instrument_name  = $row["instrument_name"];
                        $instrument_grade = $row["grade_name"];
                        $min_category_id  = $row["mas_instrument"];
                    }

                    if ($row["charge_60min"] < $min_fee && $row["charge_60min"] > 0) {
                        $min_fee     = $row["charge_60min"];
                        $min_minutes = 60;

                        $instrument_name  = $row["instrument_name"];
                        $instrument_grade = $row["grade_name"];
                        $min_category_id  = $row["mas_instrument"];
                    }

                    /*if($row["charge_30min"] > $max_fee && $row["charge_30min"] > 0){
                        $max_fee = $row["charge_30min"];
                        $max_minutes = 30;
                    }

                    if($row["charge_45min"] > $max_fee && $row["charge_45min"] > 0){
                        $max_fee = $row["charge_45min"];
                        $max_minutes = 45;
                    }

                    if($row["charge_60min"] > $max_fee && $row["charge_60min"] > 0){
                        $max_fee = $row["charge_60min"];
                        $max_minutes = 60;
                    }*/

                    /*$tutor_expectcharge[$key]["min_fee"] = $min_fee;
                    $tutor_expectcharge[$key]["min_minutes"] = $min_minutes;
                    $tutor_expectcharge[$key]["max_fee"] = $max_fee;
                    $tutor_expectcharge[$key]["max_minutes"] = $max_minutes;*/
                }

                $tutor_expectcharge[0]["min_fee"]              = $min_fee;
                $tutor_expectcharge[0]["min_minutes"]          = $min_minutes;
                $tutor_expectcharge[0]["max_fee"]              = $max_fee;
                $tutor_expectcharge[0]["max_minutes"]          = $max_minutes;
                $tutor_expectcharge[0]["min_instrument_name"]  = $instrument_name;
                $tutor_expectcharge[0]["min_instrument_grade"] = $instrument_grade;
                $tutor_expectcharge[0]["min_category_id"]      = $min_category_id;

            }

            return $tutor_expectcharge;
        }
    }

    if (!function_exists('get_tutor_expstudentage')) {
        function get_tutor_expstudentage($tutor_id, $deleted = 0, $order_by = " order by master_type_code.sort ASC")
        {
            global $dbh;

            $sql        = "select * from tutor_expstudentage INNER JOIN master_type_code ON tutor_expstudentage.mas_expstudentage = master_type_code.id where tutor_expstudentage.tutormain_id = ? and tutor_expstudentage.deleted = ?" . $order_by;
            $parameters = array($tutor_id, $deleted);
            return bind_pdo($sql, $parameters, "selectall");
        }
    }

    if (!function_exists('get_instrument_qualification')) {
        function get_instrument_qualification($tutor_id, $deleted = 0, $order_by = " order by year ASC")
        {
            global $dbh;

            $sql        = "select *, master_type_code.name_" . $_SESSION["langcode"] . " as instrument_name, mtc2.name_" . $_SESSION["langcode"] . " as grade_name from tutor_instrumentquali 
            INNER JOIN master_type_code ON tutor_instrumentquali.mas_instrument = master_type_code.id 
            INNER JOIN master_type_code mtc2 ON tutor_instrumentquali.grade = mtc2.id 
            where tutor_instrumentquali.tutormain_id = ? and tutor_instrumentquali.deleted = ?" . $order_by;
            $parameters = array($tutor_id, $deleted);
            return bind_pdo($sql, $parameters, "selectall");
        }
    }

    if (!function_exists('get_teaching_area')) {
        function get_teaching_area($tutor_id, $deleted = 0)
        {
            global $dbh;

            $sql        = "select * from tutor_teachareacode INNER JOIN master_type_code ON tutor_teachareacode.mas_teachareacode = master_type_code.id where tutor_teachareacode.tutormain_id = ? and tutor_teachareacode.deleted = ?";
            $parameters = array($tutor_id, $deleted);
            return bind_pdo($sql, $parameters, "selectall");
        }
    }

    if (!function_exists('get_teaching_venue')) {
        function get_teaching_venue($tutor_id, $deleted = 0)
        {
            global $dbh;

            $sql        = "select * from tutor_teachvenus INNER JOIN master_type_code ON tutor_teachvenus.mas_teachvenus = master_type_code.id where tutor_teachvenus.tutormain_id = ? and tutor_teachvenus.deleted = ?";
            $parameters = array($tutor_id, $deleted);
            return bind_pdo($sql, $parameters, "selectall");
        }
    }

    if (!function_exists('get_teaching_mode')) {
        function get_teaching_mode($tutor_id, $deleted = 0)
        {
            global $dbh;

            $sql        = "select * from tutor_teachmode INNER JOIN master_type_code ON tutor_teachmode.mas_teachmode = master_type_code.id where tutor_teachmode.tutormain_id = ? and tutor_teachmode.deleted = ?";
            $parameters = array($tutor_id, $deleted);
            return bind_pdo($sql, $parameters, "selectall");
        }
    }

    if (!function_exists('get_teaching_instrument')) {
        function get_teaching_instrument($tutor_id, $deleted = 0)
        {
            global $dbh;

            $sql        = "select * from tutor_charge INNER JOIN master_type_code ON tutor_charge.mas_instrument = master_type_code.id where tutor_charge.tutormain_id = ? and tutor_charge.deleted = ?";
            $parameters = array($tutor_id, $deleted);
            return bind_pdo($sql, $parameters, "selectall");
        }
    }

    if (!function_exists('low2high')) {
        function low2high($a, $b)
        {
            if ($a["min_fee"] == $b["min_fee"]) {
                return 0;
            }
            return ($a["min_fee"] < $b["min_fee"]) ? -1 : 1;
        }
    }

    if (!function_exists('high2low')) {
        function high2low($a, $b)
        {
            if ($a["min_fee"] == $b["min_fee"]) {
                return 0;
            }
            return ($a["min_fee"] > $b["min_fee"]) ? -1 : 1;
        }
    }

    if (!function_exists('accumulate_hour_low2high')) {
        function accumulate_hour_low2high($a, $b)
        {
            if ($a["stars"] == $b["stars"]) {
                return 0;
            }
            return ($a["stars"] < $b["stars"]) ? -1 : 1;
        }
    }

    if (!function_exists('accumulate_hour_high2low')) {
        function accumulate_hour_high2low($a, $b)
        {
            if ($a["stars"] == $b["stars"]) {
                return 0;
            }
            return ($a["stars"] > $b["stars"]) ? -1 : 1;
        }
    }

    function pagenav()
    {
        global $limit, $start, $numpage, $page, $category_id;
        $current_page = ($start / $limit) + 1;
        //$current_page = $start;
        $parameters = "&id=" . $category_id;

        echo "<ul class='pagination pagination-md'>";

        if ($current_page > 3) {
            echo "<li class='start' data-value='0'><a>1...</a></li>";
        }

        //previous set page
        if ($numpage > 5 && $start > (2 * $limit)) {
            $newstart = $start - 5 * $limit;
            if ($newstart <= 0) {
                $newstart = 0;
            }
        }

        //previous page
        $newstart = "";
        if ($start - 1 <= 0) {
            $newstart = 0;
        } else {
            $newstart = $start - $limit;
        }
        //echo "<li><a href='" . $page . "?start=" . $newstart . $parameters . "'>&lt;</a></li>";
        echo "<li><a class='start' data-value='$newstart'>&lt;</a></li>";

        //current page > 3
        if ($start / $limit >= 2) {
            for ($i = (($start / $limit) - 2); $i < (($start / $limit) + 3); $i++) {
                if ((($i) * $limit) == $start) {
                    echo "<li class='page-item active'><a>" . ($i + 1) . "</a></li>";
                } else {
                    if ($i >= 0 && $i < $numpage) {
                        $newstart = ($i) * $limit;
                        //echo "<li><a href='" . $page . "?start=" . $newstart . $parameters . "'>" . ($i + 1) . "</a></li>";
                        echo "<li class='start' data-value='$newstart'><a>" . ($i + 1) . "</a></li>";
                    }

                }
            }
        } else {
            for ($i = 0; $i < 5; $i++) {
                if ($i >= 0 && $i < $numpage) {
                    if ((($i) * $limit) == $start) {
                        echo "<li class='page-item active'><a>" . ($i + 1) . "</a></li>";
                    } else {

                        $newstart = ($i) * $limit;
                        //echo "<li><a href='" . $page . "?start=" . $newstart . $parameters . "'>" . ($i + 1) . "</a></li>";
                        echo "<li class='start' data-value='$newstart'><a>" . ($i + 1) . "</a></li>";

                    }
                }
            }
        }

        //next page
        if ($start < $limit * ($numpage - 1)) {
            $newstart = $start + $limit;
            //echo "<li><a href='" . $page . "?start=" . $newstart . $parameters . "' >&gt;</a></li>";
            echo "<li class='start' data-value='$newstart'><a>&gt;</a></li>";
        }

        //next set page
        if ($start / $limit >= 3) {
            if (($start / $limit) + 3 <= $numpage) {
                $newstart = $start + 5 * $limit;
            }
        }

        if (ceil($numpage) > 5 && (ceil($numpage) - $current_page >= 3)) {
            echo "<li class='start' data-value='" . ((ceil($numpage) - 1) * $limit) . "'><a>..." . ceil($numpage) . "</a></li>";
        }
        echo "</ul>";
    } // END FUNCTION

    if (!function_exists('get_last_login_date')) {
        function get_last_login_date($cmsloginid)
        {
            $sql        = "select last_login_date from sys_cms_login where cmsloginid = ?";
            $parameters = array($cmsloginid);
            $result     = bind_pdo($sql, $parameters, "selectone");

            $date = new DateTime($result["last_login_date"]);

            return $date->format("Y-m-d");
        }
    }

    if (!function_exists('get_tutor_year_range')) {
        function get_tutor_year_range($dob_year, $dob_mon)
        {
            global $langcode;

            if (!empty($dob_year) && !empty($dob_mon)) {
                $start_date  = new DateTime($dob_year . "-" . $dob_mon);
                $since_start = $start_date->diff(new DateTime());
                $tutor_age   = $since_start->y;

                $year_range          = get_master_type_code("YEAR_RANGE");
                $tutor_year_range    = "";
                $tutor_year_range_id = "";

                foreach ($year_range as $range) {
                    if (empty($tutor_year_range) && $tutor_age <= $range["extra1"]) {
                        $tutor_year_range    = $range["name_" . $langcode];
                        $tutor_year_range_id = $range["id"];
                    }
                }

                return array("tutor_year_range" => $tutor_year_range, "master_type_code_id" => $tutor_year_range_id);
            } else {
                return array("tutor_year_range" => "", "master_type_code_id" => "");
            }

        }
    }

    if (!function_exists('insert_approval_list')) {
        function insert_approval_list($type, $table_name, $table_id)
        {
            //type = TUTOR / STUDENT / JOB / CHAT
            if (!empty($type) && !empty($table_name) && !empty($table_id)) {
                $sql        = "insert into approval_list (`type`, `table_name`, `table_id`, createdate, lastupdate) values (?,?,?,?,?)";
                $parameters = array($type, $table_name, $table_id, date("Y-m-d H:i:s"), date("Y-m-d H:i:s"));
                bind_pdo($sql, $parameters);
            }
        }
    }

    if (!function_exists('update_approval_list')) {
        function update_approval_list($table_name, $table_id)
        {
            //$table = approval_list / chat / student_postjob / tutor_main
            if (!empty($table_name) && !empty($table_id)) {
                if ($table_name == "approval_list") {
                    $sql        = "select * from approval_list where id = ? and deleted = ?";
                    $parameters = array($table_id, 0);
                    $result     = bind_pdo($sql, $parameters, "selectone");

                    if (!empty($result)) {
                        //approved target

                        $sql        = "update " . $result["table_name"] . " set approved = ?, approved_date = ?, approved_by = ? where id = ?";
                        $parameters = array(1, date("Y-m-d H:i:s"), $_SESSION["cmsloginid"], $result["table_id"]);
                        bind_pdo($sql, $parameters);

                        //approved list
                        $sql        = "update approval_list set approved = ?, lastupdate = ?, lastupby = ? where id = ? and approved = ?";
                        $parameters = array(1, date("Y-m-d H:i:s"), $_SESSION["cmsloginid"], $table_id, 0);
                        bind_pdo($sql, $parameters);
                    }
                } else {
                    $sql        = "update " . $table_name . " set approved = ?, approved_date = ?, approved_by = ? where id = ?";
                    $parameters = array(1, date("Y-m-d H:i:s"), $_SESSION["cmsloginid"], $table_id);
                    bind_pdo($sql, $parameters);

                    //approved list
                    $sql        = "update approval_list set approved = ?, lastupdate = ?, lastupby = ? where table_id = ? and table_name = ? and approved = ?";
                    $parameters = array(1, date("Y-m-d H:i:s"), $_SESSION["cmsloginid"], $table_id, $table_name, 0);
                    bind_pdo($sql, $parameters);
                }
            }

        }
    }

    if (!function_exists('image_fix_orientation')) {
        function image_fix_orientation($filename)
        {
            $exif = exif_read_data($filename);
            if (!empty($exif['Orientation'])) {
                $image = imagecreatefromjpeg($filename);
                switch ($exif['Orientation']) {
                    case 3:
                        $image = imagerotate($image, 180, 0);
                        break;

                    case 6:
                        $image = imagerotate($image, -90, 0);
                        break;

                    case 8:
                        $image = imagerotate($image, 90, 0);
                        break;
                }

                imagejpeg($image, $filename, 90);
            }
        }
    }

    if (!function_exists('compare_job_and_tutor')) {
        function compare_job_and_tutor($job_id, $tutor_id)
        {
            //check if musical instrument match
            $sql = "select student_postjob.id from tutor_main
            INNER JOIN tutor_charge ON tutor_charge.tutormain_id = tutor_main.id
            INNER JOIN student_postjob ON student_postjob.mas_instrument = tutor_charge.mas_instrument
            INNER JOIN student_main ON student_main.id = student_postjob.studentmain_id
            where 
            tutor_main.status = ? and tutor_main.approved = ? and tutor_main.deleted = ? and 
            tutor_charge.deleted = ? and 
            student_postjob.status = ? and student_postjob.approved = ? and student_postjob.deleted = ? and
            student_main.status = ? and student_main.approved = ? and student_main.deleted = ? and 
            student_postjob.id = ? and tutor_main.id = ?";

            $parameters = array(4, 1, 0, 0, 1, 1, 0, 4, 1, 0, $job_id, $tutor_id);
            $result     = bind_pdo($sql, $parameters, "selectone");

            if (!empty($result)) {
                return true;
            } else {
                return false;
            }
        }
    }

    if (!function_exists('calculate_tutor_stars')) {
        function calculate_tutor_stars()
        {
            global $site_info;

            $calculate_stars_date = New DateTime($site_info["calculate_stars_date"]);
            if ($calculate_stars_date->format("Y-m-d") != date("Y-m-d")) {

                $sql        = "select * from tutor_main where status = ? and deleted = ? order by tutorial_mins ASC, recommend ASC, createdate DESC";
                $parameters = array(4, 0);
                $all_tutors = bind_pdo($sql, $parameters, "selectall");

                $tutor_num = count($all_tutors);
                $stars[1]  = floor($tutor_num * 10 / 100);
                $stars[2]  = floor($tutor_num * 10 / 100);
                $stars[3]  = floor($tutor_num * 30 / 100);
                $stars[4]  = floor($tutor_num * 30 / 100);
                $stars[5]  = $tutor_num - $stars[1] - $stars[2] - $stars[3] - $stars[4];

                $stars_array = array();
                $j           = 0;

                foreach ($stars as $key => $row) {
                    for ($i = 0; $i < $row; $i++) {
                        if ($j <= ($tutor_num - 1)) {
                            //$stars_array[$j]["id"] = $all_tutors[$j]["id"];
                            //$stars_array[$j]["stars"] = $key;

                            $sql        = "update tutor_main set stars = ? where id = ?";
                            $parameters = array($key, $all_tutors[$j]["id"]);
                            bind_pdo($sql, $parameters);

                            $j++;
                        } else {
                            break 2;
                        }
                    }
                }

                $sql        = "update site_info set calculate_stars_date = ? where siteinfoid = ?";
                $parameters = array(date("Y-m-d"), 1);
                bind_pdo($sql, $parameters);
            }
        }
    }

    //calculate_tutor_stars();

    if (!function_exists('insert_notification')) {
        function insert_notification($type, $table_name, $table_id, $cmsloginid)
        {
            $sql        = "insert into notification (type, table_name, table_id, cmsloginid, createdate) values (?,?,?,?,?)";
            $parameters = array($type, $table_name, $table_id, $cmsloginid, date("Y-m-d H:i:s"));
            bind_pdo($sql, $parameters);
        }
    }

    if (!function_exists('read_notification')) {
        function read_notification($ids = null)
        {
            if (empty($ids)) {
                //update all
                $sql        = "update notification set `read` = ?, read_date = ? where cmsloginid = ?";
                $parameters = array(1, date("Y-m-d H:i:s"), $_SESSION["cmsloginid"]);
                bind_pdo($sql, $parameters);
            } else {
                $notification_ids = explode(",", $ids);
                if (is_array($notification_ids)) {
                    foreach ($notification_ids as $id) {
                        $sql        = "update notification set `read` = ?, read_date = ? where cmsloginid = ? and id = ?";
                        $parameters = array(1, date("Y-m-d H:i:s"), $_SESSION["cmsloginid"], $id);
                        bind_pdo($sql, $parameters);
                    }
                } else {
                    $sql        = "update notification set `read` = ?, read_date = ? where cmsloginid = ? and id = ?";
                    $parameters = array(1, date("Y-m-d H:i:s"), $_SESSION["cmsloginid"], $notification_ids);
                    bind_pdo($sql, $parameters);
                }
            }

        }
    }

    if (!function_exists('show_notification')) {
        function show_notification($read = null, $num = 5)
        {
            $sql = "select * from notification where deleted = ? and cmsloginid = ?";
            if ($_SESSION["cmsrole"] == "super_admin" || $_SESSION["cmsrole"] == "admin") {
                $parameters = array(0, 0);
            } else {
                $parameters = array(0, $_SESSION["cmsloginid"]);
            }

            if (isset($read)) {
                $sql .= " and `read` = ?";
                $parameters[] = $read;
            }

            $sql .= " order by `read` ASC, createdate DESC";

            if ($num != "ALL") {
                $sql .= " limit " . $num;
            }

            $notifications = bind_pdo($sql, $parameters, "selectall");

            $show_notifications                     = array();
            $show_notifications["notification_num"] = 0;

            if (!empty($notifications)) {
                $show_notifications["notification_num"] = count($notifications);

                foreach ($notifications as $key => $notification) {
                    if ($notification["type"] == "TUTOR") {
                        //admin has approved a tutor
                        $show_notifications["detail"][$key]["title"] = _lang("Admin has approved your information.");
                        $show_notifications["detail"][$key]["link"]  = "";
                    } else if ($notification["type"] == "JOB") {
                        //
                        $show_notifications["detail"][$key]["title"] = _lang("Admin has approved your job.");
                        $show_notifications["detail"][$key]["link"]  = "student_postjob_form.php?id=" . $notification["table_id"];
                    } else if ($notification["type"] == "ORDER_PROCESS") {
                        //
                        $show_notifications["detail"][$key]["title"] = _lang("You have receive a tutorial invitation.");
                        $show_notifications["detail"][$key]["link"]  = "order_form.php?id=" . $notification["table_id"];
                    } else if ($notification["type"] == "ORDER_CANCEL") {
                        //
                        $show_notifications["detail"][$key]["title"] = _lang("Your tutorial invitation has been canceled.");
                        $show_notifications["detail"][$key]["link"]  = "order_form.php?id=" . $notification["table_id"];
                    } else if ($notification["type"] == "ORDER_ACCEPT") {
                        //
                        $show_notifications["detail"][$key]["title"] = _lang("Your tutorial invitation has been accepted.");
                        $show_notifications["detail"][$key]["link"]  = "order_form.php?id=" . $notification["table_id"];
                    } else if ($notification["type"] == "ORDER_REJECT") {
                        //
                        $show_notifications["detail"][$key]["title"] = _lang("Your tutorial invitation has been rejected.");
                        $show_notifications["detail"][$key]["link"]  = "order_form.php?id=" . $notification["table_id"];
                    } else if ($notification["type"] == "ORDER_PAID") {
                        //
                        $show_notifications["detail"][$key]["title"] = _lang("Your tutorial invitation has been paid.");
                        $show_notifications["detail"][$key]["link"]  = "order_form.php?id=" . $notification["table_id"];
                    } else if ($notification["type"] == "ORDER_REFUND") {
                        //
                        $show_notifications["detail"][$key]["title"] = _lang("Your tutorial invitation has been refunded.");
                        $show_notifications["detail"][$key]["link"]  = "order_form.php?id=" . $notification["table_id"];
                    } else if ($notification["type"] == "ORDER_COMPLAIN") {
                        //for admin
                        $show_notifications["detail"][$key]["title"] = _lang("Student has complain a lesson.");
                        $show_notifications["detail"][$key]["link"]  = "course_form.php?id=" . $notification["table_id"];
                    } else if ($notification["type"] == "CHAT_DELETE") {
                        //for student/tutor
                        $show_notifications["detail"][$key]["title"] = _lang("A message has been deleted by administrator.");
                        $show_notifications["detail"][$key]["link"]  = "chat_index.php";
                    }

                    if (!empty($show_notifications["detail"][$key]["title"])) {
                        $show_notifications["detail"][$key]["id"]         = $notification["id"];
                        $show_notifications["detail"][$key]["createdate"] = $notification["createdate"];
                        $show_notifications["detail"][$key]["read"]       = $notification["read"];
                    }
                }
            }

            $show_notifications["message_num"] = 0;
            $where_clause                      = "";
            $parameters                        = array();


            if ($_SESSION["is_tutor"] == 1) {
                $where_clause = " and ((chat.from_id = ? and chat.from_role = ?) or (chat.to_id = ? and chat.to_role = ?)) ";
                $parameters[] = $_SESSION['member_login'];
                $parameters[] = "TUTOR";
                $parameters[] = $_SESSION['member_login'];
                $parameters[] = "TUTOR";

            } else if ($_SESSION["is_student"] == 1) {
                $where_clause = " and ((chat.from_id = ? and chat.from_role = ?) or (chat.to_id = ? and chat.to_role = ?)) ";
                $parameters[] = $_SESSION['student_login'];
                $parameters[] = "STUDENT";
                $parameters[] = $_SESSION['student_login'];
                $parameters[] = "STUDENT";

            }

            $sql = "select *
                    from chat 
                    where chat.id > 0 {$where_clause} 
                    order by chat.sent_date ASC";

            $chats_detail = bind_pdo($sql, $parameters, "selectall");

            $not_read = 0;
            foreach ($chats_detail as $row2) {
                if ((($_SESSION["is_student"] == 1 && empty($row2["read_date"]) && $row2["to_id"] == $_SESSION["student_login"]) || ($_SESSION["is_tutor"] == 1 && empty($row2["read_date"]) && $row2["to_id"] == $_SESSION["member_login"])) && $row2["approved"] == 1) {
                    $not_read++;
                }
            }

            $show_notifications["message_num"] = $not_read;

            return $show_notifications;
        }
    }

    if (!function_exists('val_in_array')) {
        function val_in_array($a, $field)
        {
            if (is_array($a)) {
                return $a[$field];
            }
        }
    }

    if (!function_exists('validateDate')) {
        function validateDate($date, $format = 'Y-m-d H:i:s')
        {
            $d = DateTime::createFromFormat($format, $date);
            return $d && $d->format($format) == $date;
        }
    }

    if (!function_exists('get_public_holiday_json')) {
        function get_public_holiday_json()
        {
            $this_year = date("Y");

            $sql  = "select `date` from public_holiday where deleted = ? and `date` between ? and ? order by `date` DESC";
            $rows = bind_pdo($sql, array(0, ($this_year - 1) . "-01-01", ($this_year + 1) . "-12-31",), "selectall");

            return json_encode($rows);
        }
    }

    if (!function_exists('call_curl')) {
        function call_curl($url, $postData, $post, $http_header = array())
        {
            global $site_info;

            if (!empty($post)) {
                $ch = curl_init();
                curl_setopt($ch, CURLOPT_URL, $url);
                curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
                curl_setopt($ch, CURLOPT_POST, 1);
                curl_setopt($ch, CURLOPT_IPRESOLVE, CURL_IPRESOLVE_V4);
                curl_setopt($ch, CURLOPT_HEADER, 0);
                curl_setopt($ch, CURLOPT_HTTPHEADER, $http_header);
                curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, 0);
                curl_setopt($ch, CURLOPT_POSTFIELDS, $postData);

                $result = curl_exec($ch);

                /*var_dump($http_header);
                var_dump($result);
                var_dump(curl_getinfo($ch));*/

                curl_close($ch);
            } else {
                $ch = curl_init();
                curl_setopt($ch, CURLOPT_URL, $url);
                curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
                curl_setopt($ch, CURLOPT_IPRESOLVE, CURL_IPRESOLVE_V4);
                curl_setopt($ch, CURLOPT_HEADER, 0);
                curl_setopt($ch, CURLOPT_HTTPHEADER, $http_header);
                curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, 0);
                $result = curl_exec($ch);
                curl_close($ch);
            }

            return $result;
        }
    }

    if (!function_exists('send_app_notification')) {
        function send_app_notification($app_notification_id, $cmsloginid, $name = null, $notification_title, $notification_body, $url=null)
        {
            global $site_info;

            $sql        = "select * from sys_cms_login where deleted = ? and cmsloginid = ?";
            $parameters = array(0, $cmsloginid);
            $result     = bind_pdo($sql, $parameters, "selectone");

            if(!empty($result["app_device_id"]) && !empty($result["app_notification_token"])){
                if (empty($name)) {
                    if ($result["is_tutor"] == 1) {
                        $sql        = "select nickname, tutor_no from tutor_main where cmsloginid = ?";
                        $parameters = array($cmsloginid);
                        $user       = bind_pdo($sql, $parameters, "selectone");
                        $name       = $user["nickname"] . " (" . $user["tutor_no"] . ")";
                    } else if ($result["is_student"] == 1) {
                        $sql        = "select contactname, student_no from student_main where cmsloginid = ?";
                        $parameters = array($cmsloginid);
                        $user       = bind_pdo($sql, $parameters, "selectone");
                        $name       = $user["contactname"] . " (" . $user["student_no"] . ")";
                    }
                }

                if($result["app_device"] == "IOS"){
                    $postData = array(
                        "to"           => ($result["app_notification_token"]),
                        "notification" => array("title" => $notification_title, "body" => $notification_body, "body_loc_key" => $url),
                        "priority"           => 'high',
                    );

                    $key = aes_crypt2($site_info["app_notification_key_ios"], 2);
                }else{
                    $postData = array(
                        "to"           => ($result["app_notification_token"]),
                        "data" => array("title" => $notification_title, "message" => $notification_body, "url" => $url),
                        "priority"           => 'high',
                    );

                    $key = aes_crypt2($site_info["app_notification_key"], 2);
                }

                $url = $site_info["app_notification_url"];
                $response = call_curl($url, json_encode($postData), 1, array('Content-Type:application/json', 'Authorization: key='.$key));

                //only admin notification?
                $response_array = json_decode($response, true);
                $success = $response_array["success"];
                $sql        = "insert into app_notification_user (app_notification_id, cmsloginid, name, response, success) value (?, ?, ?, ?, ?)";
                $parameters = array($app_notification_id, $cmsloginid, $name, $response, $success);
                bind_pdo($sql, $parameters);
            }

            return true;
        }
    }

    if (!function_exists('create_app_notification')) {
        function create_app_notification($title, $content, $status, $type)
        {
            global $dbh;

            $sql        = "insert into app_notification (title, content, status, sent_date, type) value (?, ?, ?, ?, ?)";
            $parameters = array($title, $content, $status, date("Y-m-d H:i:s"), $type);
            bind_pdo($sql, $parameters);

            return $dbh->lastInsertId();
        }
    }

    if (!function_exists('hide_sensitive_word')) {
        function hide_sensitive_word($data)
        {
            //replace: 11111111, 1111-1111, email to ********
            $data = preg_replace("/(\d{8})|(\d{4})-(\d{4})|[_a-zA-Z0-9-]+(\.[_a-zA-Z0-9-]+)*@[a-zA-Z0-9-]+(\.[a-zA-Z0-9-]+)*(\.[a-zA-Z0-9]{2,})$/", "****", $data);

            return $data;
        }
    }

    if (isset($_GET["msg"]) && (int)$_GET["msg"] > 0) {
        $msg_master_info = get_master_type_code("WEBADMIN", $_GET["msg"]);
        $msg             = $msg_master_info["name_en"];
        $_GET["msg"]     = $msg_master_info["name_en"];
    }