webadmin.php

/var/www/hkosl.com/b2b2c/webadmin/_ajax.php

<?php
    include('../_init.php');
    include('../JSON.php');

    foreach ($_POST as $key => $value) {
        $_POST[$key] = _h($value);
    }

    $data = $_POST;

    if ($data['ajax'] == 'course_search') {

        $sql         = "select profile_id,(select id from `student_main` where cmsloginid = tb.user_id LIMIT 1) as id
                        from `profile_user` as tb where user_id = ? and deleted = ? LIMIT 1";
        $parameters  = array($_SESSION['cmsloginid'], 0);
        $row_profile = bind_pdo($sql, $parameters, "selectone");

        unset($exsql);
        if ($data['level'] && $data['level'] != 'ANY') {
            $exsql[] = 'level = ' . $data['level'];
        }
        if ($data['mas_instrument'] && $data['mas_instrument'] != 'ANY') {
            $exsql[] = 'mas_instrument = ' . $data['category_id'];
        }
        if ($data['quick_search']) {
            $exsql[] = 'title_cn like "%' . $data['quick_search'] . '%"';
        }
        if (is_array($exsql)) {
            $exsql = ' and ' . implode(' and ', $exsql);
        } else {
            $exsql = '';
        }

        $sql              = "select *,
                (select count(*) from `order` where job_id = tb.id and (status = 'process' or status = 'paid') and deleted = 0) as count,
                (select count(*) from `order` where job_id = tb.id and (status = 'process' or status = 'paid') and studentmain_id = '" . $row_profile['id'] . "' and deleted = 0) as studentcount
                from tutor_postjob as tb where deleted = ? and approved = ? and status = ? and (NOW() between start_date and end_date)" . $exsql;
        $parameters       = array(0, 1, 1);
        $all_product_info = bind_pdo($sql, $parameters, "selectall");

        foreach ($all_product_info as $row) {
            $sql            = "select name_cn as name
                    from master_type_code where id = ? and typeid = ? and deleted = ? LIMIT 1";
            $parameters     = array($row["mas_instrument"], 'INSTRUMENT', 0);
            $row_instrument = bind_pdo($sql, $parameters, "selectone");

            $sql               = "SELECT * FROM tutor_postjob_date WHERE postjob_refid = ?";
            $parameters99      = array(intval($row['id']));
            $rows_postjob_date = bind_pdo($sql, $parameters99, "selectall");

            ?>
            <div class="col-lg-4 col-md-4">
                <div class="Tutor">
                    <div class="CategoryTutorName">
                        <?= $row["title_cn"] ?>
                    </div>
                    <div class="TutorInfo"><span style="font-size: 18px;">
                            <?= $row_instrument["name"] ?>
                        </span><br> <span style="font-size: 12px; font-weight: bold; text-decoration: underline;">
                            <?= _lang("Tutorial Fee") ?>
                            :</span><br> <span style="font-size: 18px;">
                            <?= " HK$" . (float)$row["fee"] ?>
                        </span> <br> <span style="font-size: 12px; font-weight: bold; text-decoration: underline;">
                            <?= _lang("Teaching District") . ":<br> " ?>
                        </span> <span style="font-size: 18px;">
                            <?= $row["address_cn"] ?>
                        </span><br> <span style="font-size: 12px; font-weight: bold; text-decoration: underline;">
                            <?= _lang("上課日期及時間") . ":<br> " ?>
                        </span>
                        <div class="TutorDescription">
                            <? foreach ($rows_postjob_date as $index => $row_postjob_date) { ?>
                                <div><?= $row_postjob_date['date'] ?></div>
                            <? } ?>
                        </div>
                        <? if ($row['studentcount'] == 0 && (($row["reserved_people_count"] + $row['count']) < $row["people_count"])) { ?>
                            <form action="course_search_process.php" method="post">
                                <input type="hidden" name="id" value="<?= $row['id'] ?>"/>
                                <button type="submit" onclick="return confirm('你確認參加?');" class="btn btn-block btn-success" style="width:100%; margin-top:10px;">
                                    報名參加 (<?= ($row["reserved_people_count"] + $row['count']) ?>
                                    /<?= $row["people_count"] ?>)
                                </button>
                            </form>
                        <? } else { ?>
                            <button type="button" class="btn btn-block" style="width:100%; margin-top:10px; color:#333;">
                                報名參加 (<?= ($row["reserved_people_count"] + $row['count']) ?>/<?= $row["people_count"] ?>
                                )
                            </button>
                        <? } ?>
                    </div>
                </div>
            </div>
        <? } ?>
        <?
    } elseif ($data['ajax'] == 'tutor_search') {
        //student post find tutor

        $valid = true;
        if (!$valid) {
            echo "FAIL";
            exit;
        } else {
            unset($_SESSION["tutor_search"]);

            $data["tutorreq_gender"]         = json_decode(html_entity_decode($data["tutorreq_gender"]));
            $data["tutor_mas_teachareacode"] = json_decode(html_entity_decode($data["tutor_mas_teachareacode"]));
            $data["tutor_mas_teachmode"]     = json_decode(html_entity_decode($data["tutor_mas_teachmode"]));
            $data["tutor_mas_teachvenus"]    = json_decode(html_entity_decode($data["tutor_mas_teachvenus"]));

            $quick_search = $data["quick_search"];

            $category_id = $data["category_id"];

            $require_tutor_gender = ($data["tutorreq_gender"]);

            $require_tutor_instrument_level = $data["level"]; //LEVEL OF GRADE

            $require_tutor_theory_level = $data["tutorreq_level"]; //theory grade

            $require_tutor_age_min = $data["tutorreq_age_min"];

            $require_tutor_age_max = $data["tutorreq_age_max"];

            $_30mins_min_budget = $data["_30mins_min_budget"];

            $_30mins_max_budget = $data["_30mins_max_budget"];

            $_45mins_min_budget = $data["_45mins_min_budget"];

            $_45mins_max_budget = $data["_45mins_max_budget"];

            $_60mins_min_budget = $data["_60mins_min_budget"];

            $_60mins_max_budget = $data["_60mins_max_budget"];

            $require_tutor_mas_teachareacode = ($data["tutor_mas_teachareacode"]);

            $require_tutor_mas_teachmode = ($data["tutor_mas_teachmode"]);

            $require_tutor_mas_teachvenus = ($data["tutor_mas_teachvenus"]);


            foreach ($data as $key => $value) {
                $_SESSION["tutor_search"][$key] = $value;
            }

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

            //quick search tutor nickname
            if (!empty($_POST["quick_search"])) {
                $where_clause .= " and tutor_main.nickname LIKE ?";
                $parameters[] = "%" . $_POST["quick_search"] . "%";
            }

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


            //tutor instrument grade
            if (!empty($require_tutor_instrument_level) && $require_tutor_instrument_level != "ANY") {
                $where_clause .= " and tutor_expectcharge.grade=?";
                $parameters[] = $require_tutor_instrument_level;
            }

            //tutor theory grade
            if (!empty($require_tutor_theory_level) && $require_tutor_theory_level != "ANY") {
                $where_clause .= " and tutor_main.mas_mustheorygrade=?";
                $parameters[] = $require_tutor_theory_level;
            }

            //tutor teacharea
            $any = false;
            if (!empty($require_tutor_mas_teachareacode) && count($require_tutor_mas_teachareacode) > 0) {
                foreach ($require_tutor_mas_teachareacode as $row) {
                    if ($row == "ANY") {
                        $any = true;
                    }
                }

                if (!$any) {
                    $teacharea_clause = " and (";
                    foreach ($require_tutor_mas_teachareacode as $row) {
                        $teacharea_clause .= "tutor_teachareacode.mas_teachareacode=? or ";
                        $parameters[] = $row;
                    }

                    $teacharea_clause = substr_replace($teacharea_clause, "", -3) . ")";
                    $where_clause .= $teacharea_clause;

                    $inner_sql .= " INNER JOIN tutor_teachareacode ON tutor_teachareacode.tutormain_id = tutor_main.id";
                }
            }


            //tutor mode
            $any = false;
            if (!empty($require_tutor_mas_teachmode) && count($require_tutor_mas_teachmode) > 0) {
                foreach ($require_tutor_mas_teachmode as $row) {
                    if ($row == "ANY") {
                        $any = true;
                    }
                }

                if (!$any) {
                    $teach_mode_clause = " and (";
                    foreach ($require_tutor_mas_teachmode as $row) {
                        $teach_mode_clause .= "tutor_teachmode.mas_teachmode=? or ";
                        $parameters[] = $row;
                    }

                    $teach_mode_clause = substr_replace($teach_mode_clause, "", -3) . ")";
                    $where_clause .= $teach_mode_clause;

                    $inner_sql .= " INNER JOIN tutor_teachmode ON tutor_teachmode.tutormain_id = tutor_main.id";
                }
            }

            //tutor venue
            $any = false;
            if (!empty($require_tutor_mas_teachvenus) && count($require_tutor_mas_teachvenus) > 0) {
                foreach ($require_tutor_mas_teachvenus as $row) {
                    if ($row == "ANY") {
                        $any = true;
                    }
                }

                if (!$any) {
                    $teach_venue_clause = " and (";
                    foreach ($require_tutor_mas_teachvenus as $row) {
                        $teach_venue_clause .= "tutor_teachvenus.mas_teachvenus=? or ";
                        $parameters[] = $row;
                    }

                    $teach_venue_clause = substr_replace($teach_venue_clause, "", -3) . ")";
                    $where_clause .= $teach_venue_clause;

                    $inner_sql .= " INNER JOIN tutor_teachvenus ON tutor_teachvenus.tutormain_id = tutor_main.id";
                }

            }

            $gender = array();
            if (!empty($_POST["gender_f"]) && !empty($_POST["gender_m"])) {
                $where_clause .= " and tutor_main.gender in ('m','f')";
            } elseif (!empty($_POST["gender_f"])) {
                $where_clause .= " and tutor_main.gender=?";
                $parameters[] = $_POST["gender_f"];
            } elseif (!empty($_POST["gender_m"])) {
                $where_clause .= " and tutor_main.gender=?";
                $parameters[] = $_POST["gender_m"];
            }
            if (!empty($_POST["gender_f"])) {
                $gender[] = $_POST["gender_f"];
            }
            if (!empty($_POST["gender_m"])) {
                $gender[] = $_POST["gender_m"];
            }
            $_SESSION["tutor_search"]["tutorreq_gender"] = $gender;

            //tutor age
            $current_year          = date("Y");
            $require_tutor_dob_max = ($current_year - $require_tutor_age_min) . "-" . date("m");
            $require_tutor_dob_min = ($current_year - $require_tutor_age_max) . "-" . date("m");

            $where_clause .= " and CONCAT(tutor_main.dob_year, '-', tutor_main.dob_mon) >= ? and CONCAT(tutor_main.dob_year, '-', tutor_main.dob_mon) <= ?";
            $parameters[] = $require_tutor_dob_min;
            $parameters[] = $require_tutor_dob_max;

            //tutor fee
            if ((isset($_30mins_min_budget) && isset($_30mins_max_budget)) || (isset($_45mins_min_budget) && isset($_45mins_max_budget)) || (isset($_60mins_min_budget) && isset($_60mins_max_budget))) {
                $where_clause .= " and (";

                //if (isset($_30mins_min_budget)  && $_30mins_min_budget != 0  && isset($_30mins_max_budget) && $_30mins_max_budget != 9999) {
                if (isset($_30mins_min_budget) && isset($_30mins_max_budget)) {
                    $where_clause .= " (tutor_expectcharge.charge_30min >= ? and tutor_expectcharge.charge_30min <= ?) or ";
                    $parameters[] = $_30mins_min_budget;
                    $parameters[] = $_30mins_max_budget;
                }

                //if (isset($_45mins_min_budget) && $_45mins_min_budget != 0 && isset($_45mins_max_budget) && $_45mins_max_budget != 9999) {
                if (isset($_45mins_min_budget) && isset($_45mins_max_budget)) {
                    $where_clause .= " (tutor_expectcharge.charge_45min >= ? and tutor_expectcharge.charge_45min <= ?) or ";
                    $parameters[] = $_45mins_min_budget;
                    $parameters[] = $_45mins_max_budget;
                }

                //if (isset($_60mins_min_budget) && $_60mins_min_budget != 0 && isset($_60mins_max_budget) && $_60mins_min_budget != 9999) {
                if (isset($_60mins_min_budget) && isset($_60mins_max_budget)) {
                    $where_clause .= " (tutor_expectcharge.charge_60min >= ? and tutor_expectcharge.charge_60min <= ?) and ";
                    $parameters[] = $_60mins_min_budget;
                    $parameters[] = $_60mins_max_budget;
                }

                $where_clause = substr_replace($where_clause, "", -4) . ")";
            }


            if (!isset($_POST["start"])) {
                $start                             = 0;
                $_SESSION["tutor_search"]["start"] = $start;
            } else {
                $start = (int)$_POST["start"];
            }

            $limit = 9;

            $grade_info = get_master_type_code("LEVEL_OF_GRADE");

            /*$sql = "select * 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 tutor_teachareacode ON tutor_teachareacode.tutormain_id = tutor_main.id
                    INNER JOIN tutor_teachvenus ON tutor_teachvenus.tutormain_id = tutor_main.id
                    INNER JOIN tutor_teachmode ON tutor_teachmode.tutormain_id = tutor_main.id
                    where tutor_charge.deleted = ? and tutor_main.status = ? and tutor_main.deleted = ? and tutor_main.approved = ? and tutor_expectcharge.type= ? {$where_clause} group by tutor_main.id";*/

            $sql = "select tutor_main.nickname, tutor_main.recommend, tutor_main.profilephoto_path, tutor_main.self_describemyself, tutor_main.cmsloginid, tutor_charge.tutormain_id, tutor_expectcharge.grade, stars, tutorial_mins
            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_sql}
                    where tutor_charge.deleted = ? and tutor_expectcharge.type = ? and tutor_main.status = ? and tutor_main.deleted = ? and tutor_main.approved = ? {$where_clause}
                    group by tutor_main.id
                    order by tutor_main.recommend DESC, tutor_main.tutorial_mins DESC, tutor_main.createdate ASC";


            //debug_log("tutor search ajax", dump_sql($sql, $parameters));
            $tutors = bind_pdo($sql, $parameters, "selectall");

            if (!empty($tutors)) {
                $limit   = 9;
                $count   = count($tutors);
                $numpage = $count / $limit;

                foreach ($tutors as $key => $tutor) {
                    if (!empty($tutor["tutormain_id"])) {
                        //$teaching_experience = get_master_type_code_by_id($tutor["mas_teachexp"]);
                        $tutor_expectcharge = get_tutor_expectcharge($tutor["tutormain_id"], $category_id, "fee");

                        //$tutors[$key]["teaching_experience"] = $teaching_experience["name_" . $_SESSION["wlangcode"]];
                        $tutors[$key]["min_fee"]              = $tutor_expectcharge[0]["min_fee"];
                        $tutors[$key]["min_minutes"]          = $tutor_expectcharge[0]["min_minutes"];
                        $tutors[$key]["min_instrument_name"]  = $tutor_expectcharge[0]["min_instrument_name"];
                        $tutors[$key]["min_instrument_grade"] = $tutor_expectcharge[0]["min_instrument_grade"];
                        $tutors[$key]["min_category_id"]      = $tutor_expectcharge[0]["min_category_id"];

                    }
                }

                if (!empty($_POST["sort_by"])) {
                    if ($_POST["sort_by"] == "MOST_POPULAR") {
                        //$where_clause .= " order by tutor_expectcharge.charge_30min ";
                    } else if ($_POST["sort_by"] == "LOW_HIGH") {
                        //$where_clause .= " order by tutor_expectcharge.charge_30min ASC";

                        uasort($tutors, 'low2high');
                    } else if ($_POST["sort_by"] == "HIGH_LOW") {
                        //$where_clause .= " order by tutor_expectcharge.charge_30min DESC";
                        uasort($tutors, 'high2low');
                    } else if ($_POST["sort_by"] == "ACCUMULATE_HOUR_LOW_HIGH") {
                        //$where_clause .= " order by stars ASC";
                        uasort($tutors, 'accumulate_hour_low2high');
                    } else if ($_POST["sort_by"] == "ACCUMULATE_HOUR_HIGH_LOW") {
                        //$where_clause .= " order by stars DESC";
                        uasort($tutors, 'accumulate_hour_high2low');
                    }
                }

                $count_tutor = 0; //start from 0
                foreach ($tutors as $tutor) {
                    if (!empty($tutor["tutormain_id"])) {

                        if ($count_tutor >= $start && $count_tutor < ($start + $limit)) {

                            $sql        = "select DISTINCT(master_type_code.extra1) 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['tutormain_id'], 0);
                            $rows       = bind_pdo($sql, $parameters, "selectall");

                            $area_array = array();
                            foreach ($rows as $row) {
                                $sql               = "select name_en,name_cn from master_type_code where typeid = 'TEACH_AREACODE' and code = ? and deleted = ?";
                                $parameters        = array($row['extra1'], 0);
                                $row_teachareacode = bind_pdo($sql, $parameters, "selectone");
                                $area_array[]      = $row_teachareacode['name_' . $langcode];
                            }
                            ?>
                            <div class="col-lg-4 col-md-4">
                                <div class="Tutor">
                                    <a href="../profile.php?id=<?= $tutor["tutormain_id"] ?>&category_id=<?= $tutor["min_category_id"] ?>" target="_blank">
                                        <?php
                                            if ($tutor["recommend"] == 1) {
                                                echo "<div style='position:absolute; top:0; left: 0'><img src='../img/recommend.png' style='width: 40%;'> </div>";
                                            }
                                        ?>
                                        <?php if (!empty($tutor["profilephoto_path"])) { ?>
                                            <div class="tutor_img_container pointer">
                                                <div class="tutor_img" style="background: url(../file/teacher/<?= $tutor["profilephoto_path"] ?>) center center no-repeat; background-size: cover; width: inherit; height: inherit"></div>
                                                <img class="tutor_img2" src="../file/teacher/<?= $tutor["profilephoto_path"] ?>">
                                            </div>
                                        <?php } ?>
                                    </a>
                                    <div class="CategoryTutorName" <?= ($tutor["recommend"] == 1 ? "style='    background-color: #D70000; font-weight: bold;'" : "") ?>>
                                        <?= $tutor["nickname"] ?>
                                    </div>
                                    <div class="TutorInfo"><span style="font-size: 18px;">
                                            <?= $tutor["min_instrument_name"] ?>
                                        </span><br>
                                        <span style="font-size: 12px; font-weight: bold; text-decoration: underline;">
                                            <?= _lang("Tutorial Fee") ?>
                                            :</span><br>
                                        <? if ($_SESSION["category_page"]["level_of_grade"] && $_SESSION["category_page"]["level_of_grade"] != "ANY") { ?>
                                            <?
                                            if ($tutor["charge_30min"]) {
                                                $tutor["min_fee"]     = $tutor["charge_30min"];
                                                $tutor["min_minutes"] = 30;
                                            } else if ($tutor["charge_45min"]) {
                                                $tutor["min_fee"]     = $tutor["charge_45min"];
                                                $tutor["min_minutes"] = 45;
                                            } else if ($tutor["charge_60min"]) {
                                                $tutor["min_fee"]     = $tutor["charge_60min"];
                                                $tutor["min_minutes"] = 60;
                                            }
                                            ?>
                                            <span style="font-size: 18px;">
                                                <?= _lang("Level") . ": " . $tutor["grade_name"] . " HK$" . (float)$tutor["min_fee"] ?>
                                                <?= _lang("per " . $tutor["min_minutes"] . " mins") ?>
                                            </span> <br>
                                        <? } else { ?>
                                            <span style="font-size: 18px;">
                                                <?= _lang("Level") . ": " . $tutor["min_instrument_grade"] . " HK$" . (float)$tutor["min_fee"] ?>
                                                <?= _lang("per " . $tutor["min_minutes"] . " mins") ?>
                                            </span> <br>
                                        <? } ?>
                                        <span style="font-size: 12px; font-weight: bold; text-decoration: underline;">
                                            <?= _lang("Teaching District") . ":<br> " ?>
                                        </span> <span style="font-size: 18px;">
                                            <?= implode(',', $area_array) ?>
                                        </span><br>
                                        <div class="TutorDescription">
                                            <?php
                                                echo "<p class='self_describemyself' style='height: 33px;'>" . nl2br($tutor["self_describemyself"]) . "</p>";

                                                echo "<p><a class='fancybox1' href='#accumulate_tutorial_hours' style='text-decoration: none; color: inherit;'>" . _lang("Accumulate Tutorial Hours") . " <span class='glyphicon glyphicon-question-sign' aria-hidden='true'></span></a>: <span class='stars'>" . $tutor["stars"] . "</span></p>";
                                            ?>
                                        </div>
                                        <!--<hr style="margin: 6px 0;">
                                        <div class=""> --><?php /*echo _lang("LAST LOGIN") . ": " . get_last_login_date($tutor["cmsloginid"]); */ ?>
                                        <!--<span class="glyphicon glyphicon-paperclip"></span><font color="#ff0000"> <?/*= $tutor["teaching_experience"] */ ?><?/*= _lang("years experience") */ ?></font>-->
                                    </div>
                                </div>
                            </div>
                            </div>
                            <?php
                        }
                        $count_tutor++;
                    }
                }

                echo '<div class="col-lg-12">';
                if ($numpage > 1) {
                    pagenav();
                }
                echo '</div>';
            }

        }
    } else if ($data['ajax'] == 'student_search') {
        //tutor find student

        /*var_dump($data);
        exit;*/

        $valid = true;
        if (!$valid) {
            echo "FAIL";
            /*echo '<script>alert("Access Denied!"); history.back();</script>';*/
            exit;
        } else {

            unset($_SESSION["student_search"]);

            $data["category_ids"]              = json_decode(html_entity_decode($data["category_ids"]));
            $data["studentreq_gender"]         = json_decode(html_entity_decode($data["studentreq_gender"]));
            $data["student_mas_teachareacode"] = json_decode(html_entity_decode($data["student_mas_teachareacode"]));
            $data["student_mas_teachmode"]     = json_decode(html_entity_decode($data["student_mas_teachmode"]));
            $data["student_mas_teachvenus"]    = json_decode(html_entity_decode($data["student_mas_teachvenus"]));

            $quick_search = $data["quick_search"];

            $category_ids = $data["category_ids"];

            $require_student_gender = $data["studentreq_gender"];

            $require_student_instrument_level = $data["level"]; //LEVEL OF GRADE

            $require_student_theory_level = $data["studentreq_level"]; //theory grade

            $require_student_age_min = $data["studentreq_age_min"];

            $require_student_age_max = $data["studentreq_age_max"];

            $_30mins_min_budget = $data["_30mins_min_budget"];

            $_30mins_max_budget = $data["_30mins_max_budget"];

            $_45mins_min_budget = $data["_45mins_min_budget"];

            $_45mins_max_budget = $data["_45mins_max_budget"];

            $_60mins_min_budget = $data["_60mins_min_budget"];

            $_60mins_max_budget = $data["_60mins_max_budget"];

            $require_student_mas_teachareacode = $data["student_mas_teachareacode"];

            $require_student_mas_teachmode = $data["student_mas_teachmode"];

            $require_student_mas_teachvenus = $data["student_mas_teachvenus"];


            foreach ($data as $key => $value) {
                $_SESSION["student_search"][$key] = $value;
            }

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

            //quick search student contact name
            if (!empty($quick_search)) {
                $where_clause .= " and (student_main.contactname LIKE ? || student_postjob.postjob_no LIKE ?)";
                $parameters[] = "%" . $quick_search . "%";
                $parameters[] = "%" . $quick_search . "%";
            }

            //student instrument

            $any = false;
            if (!empty($category_ids) && count($category_ids) > 0) {
                foreach ($category_ids as $row) {
                    if ($row == "ANY") {
                        $any = true;
                    }
                }

                if (!$any) {
                    $category_ids_clause = " and (";
                    foreach ($category_ids as $row) {
                        $category_ids_clause .= "student_postjob.mas_instrument=? or ";
                        $parameters[] = $row;
                    }

                    if (!empty($category_ids_clause)) {
                        $category_ids_clause = substr_replace($category_ids_clause, "", -3) . ")";
                    }

                    $where_clause .= $category_ids_clause;
                }
            }

            //student instrument grade
            if (!empty($require_student_instrument_level) && $require_student_instrument_level != "ANY") {
                $where_clause .= " and student_postjob.level=?";
                $parameters[] = $require_student_instrument_level;
            }

            //student theory grade
            if (!empty($require_student_theory_level) && $require_student_theory_level != "ANY") {
                $where_clause .= " and student_postjob.tutorreq_level=?";
                $parameters[] = $require_student_theory_level;
            }

            //student teacharea
            $any = false;
            if (!empty($require_student_mas_teachareacode) && count($require_student_mas_teachareacode) > 0) {
                foreach ($require_student_mas_teachareacode as $row) {
                    if ($row == "ANY") {
                        $any = true;
                    }
                }

                if (!$any) {
                    $teacharea_clause = " and (";
                    foreach ($require_student_mas_teachareacode as $row) {
                        $teacharea_clause .= "student_postjoblearndistrict.mas_teachareacode=? or ";
                        $parameters[] = $row;
                    }

                    if (!empty($teacharea_clause)) {
                        $teacharea_clause = substr_replace($teacharea_clause, "", -3) . ")";
                    }

                    $where_clause .= $teacharea_clause;
                }
            }


            //tutor mode
            $any = false;
            if (!empty($require_student_mas_teachmode) && count($require_student_mas_teachmode) > 0) {
                foreach ($require_student_mas_teachmode as $row) {
                    if ($row == "ANY") {
                        $any = true;
                    }
                }

                if (!$any) {
                    $teach_mode_clause = " and (";
                    foreach ($require_student_mas_teachmode as $row) {
                        $teach_mode_clause .= "student_postjoblearnmode.mas_teachmode=? or ";
                        $parameters[] = $row;
                    }

                    if (!empty($teach_mode_clause)) {
                        $teach_mode_clause = substr_replace($teach_mode_clause, "", -3) . ")";
                    }

                    $where_clause .= $teach_mode_clause;
                }
            }


            //tutor venue
            $any = false;
            if (!empty($require_student_mas_teachvenus) && count($require_student_mas_teachvenus) > 0) {
                foreach ($require_student_mas_teachvenus as $row) {
                    if ($row == "ANY") {
                        $any = true;
                    }
                }

                if (!$any) {
                    $teach_venue_clause = " and (";
                    foreach ($require_student_mas_teachvenus as $row) {
                        $teach_venue_clause .= "student_postjoblearnvenus.mas_teachvenus=? or ";
                        $parameters[] = $row;
                    }
                    if (!empty($teach_venue_clause)) {
                        $teach_venue_clause = substr_replace($teach_venue_clause, "", -3) . ")";
                    }

                    $where_clause .= $teach_venue_clause;
                }

            }


            //tutor gender
            /*if (count($require_student_gender) == 1) {
                $where_clause .= " and student_postjob.tutorreq_gender=?";
                $parameters[] = $require_student_gender[0];
            }*/

            $gender = array();
            if (!empty($_POST["gender_f"]) && !empty($_POST["gender_m"])) {
                $where_clause .= " and student_postjob.tutorreq_gender in ('m','f')";
            } elseif (!empty($_POST["gender_f"])) {
                $where_clause .= " and student_postjob.tutorreq_gender=?";
                $parameters[] = $_POST["gender_f"];
            } elseif (!empty($_POST["gender_m"])) {
                $where_clause .= " and student_postjob.tutorreq_gender=?";
                $parameters[] = $_POST["gender_m"];
            }
            if (!empty($_POST["gender_f"])) {
                $gender[] = $_POST["gender_f"];
            }
            if (!empty($_POST["gender_m"])) {
                $gender[] = $_POST["gender_m"];
            }
            $_SESSION["student_search"]["studentreq_gender"] = $gender;

            //student age
            /*$current_year          = date("Y");
            $require_tutor_dob_max = ($current_year - $require_student_age_min) . "-" . date("m");
            $require_tutor_dob_min = ($current_year - $require_student_age_max) . "-" . date("m");

            $where_clause .= " and CONCAT(tutor_main.dob_year, '-', tutor_main.dob_mon) >= ? and CONCAT(tutor_main.dob_year, '-', tutor_main.dob_mon) <= ?";
            $parameters[] = $require_tutor_dob_min;
            $parameters[] = $require_tutor_dob_max;*/

            //tutor fee
            if ((isset($_30mins_min_budget) && isset($_30mins_max_budget)) || (isset($_45mins_min_budget) && isset($_45mins_max_budget)) || (isset($_60mins_min_budget) && isset($_60mins_max_budget))) {
                $where_clause .= " and (";

                if (isset($_30mins_min_budget) && isset($_30mins_max_budget)) {
                    /*$where_clause .= " (student_postjob.30mins_min_budget >= ? and student_postjob.30mins_max_budget <= ? ) or ";
                    $parameters[] = $_30mins_min_budget;
                    $parameters[] = $_30mins_max_budget;*/

                    $where_clause .= " ((student_postjob.30mins_min_budget >= ? and student_postjob.30mins_min_budget <= ? ) or (student_postjob.30mins_max_budget >= ? and student_postjob.30mins_max_budget <= ?)) or ";
                    $parameters[] = $_30mins_min_budget;
                    $parameters[] = $_30mins_max_budget;
                    $parameters[] = $_30mins_min_budget;
                    $parameters[] = $_30mins_max_budget;
                } else {
                    /*if (isset($_30mins_min_budget) && !isset($_30mins_max_budget)) {
                        $where_clause .= " (student_postjob.30mins_min_budget >= ?) or ";
                        $parameters[] = $_30mins_min_budget;
                        $parameters[] = $_30mins_max_budget;
                    }else if (!isset($_30mins_min_budget) && isset($_30mins_max_budget)) {
                        $where_clause .= " (student_postjob.30mins_max_budget <= ?) or ";
                        $parameters[] = $_30mins_min_budget;
                        $parameters[] = $_30mins_max_budget;
                    }*/
                }

                if (isset($_45mins_min_budget) && isset($_45mins_max_budget)) {
                    /*$where_clause .= " (student_postjob.45mins_min_budget >= ? and student_postjob.45mins_max_budget <= ?) or ";
                    $parameters[] = $_45mins_min_budget;
                    $parameters[] = $_45mins_max_budget;*/

                    $where_clause .= " ((student_postjob.45mins_min_budget >= ? and student_postjob.45mins_min_budget <= ? ) or (student_postjob.45mins_max_budget >= ? and student_postjob.45mins_max_budget <= ?)) or ";
                    $parameters[] = $_45mins_min_budget;
                    $parameters[] = $_45mins_max_budget;
                    $parameters[] = $_45mins_min_budget;
                    $parameters[] = $_45mins_max_budget;
                } else {
                    /*if (isset($_45mins_min_budget) && !isset($_45mins_max_budget)) {
                        $where_clause .= " (student_postjob.45mins_min_budget >= ?) or ";
                        $parameters[] = $_45mins_min_budget;
                        $parameters[] = $_45mins_max_budget;
                    }else if (!isset($_45mins_min_budget) && isset($_45mins_max_budget)) {
                        $where_clause .= " (student_postjob.45mins_max_budget <= ?) or ";
                        $parameters[] = $_45mins_min_budget;
                        $parameters[] = $_45mins_max_budget;
                    }*/
                }


                if (isset($_60mins_min_budget) && isset($_60mins_max_budget)) {
                    /*$where_clause .= " (student_postjob.60mins_min_budget >= ? and student_postjob.60mins_max_budget <= ?) or ";
                    $parameters[] = $_60mins_min_budget;
                    $parameters[] = $_60mins_max_budget;*/

                    $where_clause .= " ((student_postjob.60mins_min_budget >= ? and student_postjob.60mins_min_budget <= ? ) or (student_postjob.60mins_max_budget >= ? and student_postjob.60mins_max_budget <= ?)) or ";
                    $parameters[] = $_60mins_min_budget;
                    $parameters[] = $_60mins_max_budget;
                    $parameters[] = $_60mins_min_budget;
                    $parameters[] = $_60mins_max_budget;
                } else {
                    /*if (isset($_60mins_min_budget) && !isset($_60mins_max_budget)) {
                        $where_clause .= " (student_postjob.60mins_min_budget >= ?) or ";
                        $parameters[] = $_60mins_min_budget;
                        $parameters[] = $_60mins_max_budget;
                    }else if (!isset($_60mins_min_budget) && isset($_60mins_max_budget)) {
                        $where_clause .= " (student_postjob.60mins_max_budget <= ?) or ";
                        $parameters[] = $_60mins_min_budget;
                        $parameters[] = $_60mins_max_budget;
                    }*/
                }

                $where_clause = substr_replace($where_clause, "", -4) . ")";
            }

            $sql = "select student_main.*,student_postjob.*,student_postjob.id as student_postjob_id, master_type_code.name_" . $langcode . " as instrument_name , mtc2.name_" . $langcode . " as level_name , mtc3.name_" . $langcode . " as student_age_range, mtc4.name_" . $langcode . " as tutor_instrument_level, mtc5.name_" . $langcode . " as tutor_age , student_postjob.createdate as post_date
            from student_postjob
            INNER JOIN student_main ON student_main.id = student_postjob.studentmain_id
            INNER JOIN student_postjoblearndistrict ON student_postjoblearndistrict.studentpostjob_id = student_postjob.id
            INNER JOIN student_postjoblearnmode ON student_postjoblearnmode.studentpostjob_id = student_postjob.id
            INNER JOIN student_postjoblearnvenus ON student_postjoblearnvenus.studentpostjob_id = student_postjob.id
            INNER JOIN master_type_code ON master_type_code.id = student_postjob.mas_instrument
            INNER JOIN master_type_code mtc2 ON mtc2.code = student_postjob.level and mtc2.typeid = 'LEVEL_OF_GRADE'
            INNER JOIN master_type_code mtc3 ON mtc3.id = student_main.mas_yearrange
            INNER JOIN master_type_code mtc4 ON mtc4.id = student_postjob.tutorreq_level
            INNER JOIN master_type_code mtc5 ON mtc5.id = student_postjob.tutorreq_age
            where student_postjob.status = ? and student_postjob.deleted = ? and student_main.approved = ? and student_main.status = ? and student_main.deleted = ? {$where_clause}
            group by student_postjob.id
            order by student_postjob.createdate DESC";

            //debug_log("student search ajax", dump_sql($sql, $parameters));
            $students = bind_pdo($sql, $parameters, "selectall");

            //echo dump_sql($sql, $parameters) . "<br><br>";
            //var_dump($students);


            if (!empty($students)) {
                $limit   = 10;
                $count   = count($students);
                $numpage = $count / $limit;

                foreach ($students as $row) {
                    if (!empty($row["studentmain_id"])) {

                        if (!empty($row["student_post_gender"])) {
                            $row["gender"] = $row["student_post_gender"];
                        }
                        //$instrument_info = get_master_type_code_by_id($student["mas_instrument"]);

                        //check if student has paid trial fee
                        $has_order  = false;
                        $sql        = "select * from `order` where job_id = ? and deleted = ? and status = ?";
                        $parameters = array($row["student_postjob_id"], 0, "paid");
                        $order      = bind_pdo($sql, $parameters, "selectone");
                        if (!empty($order)) {
                            $has_order = true;
                        }

                        //budget
                        $_30mins = "";
                        $_45mins = "";
                        $_60mins = "";

                        if (!empty($row["30mins_min_budget"]) && !empty($row["30mins_max_budget"])) {
                            $_30mins = "30" . _lang("mins") . ": $" . $row["30mins_min_budget"] . ' - ' . $row["30mins_max_budget"] . ', ';
                        } else {
                            if (!empty($row["30mins_min_budget"]) && empty($row["30mins_max_budget"])) {
                                $_30mins = "30" . _lang("mins") . ": " . _lang("Above") . " $" . $row["30mins_min_budget"] . ', ';
                            }

                            if (empty($row["30mins_min_budget"]) && !empty($row["30mins_max_budget"])) {
                                $_30mins = "30" . _lang("mins") . ": " . _lang("Below") . " $" . $row["30mins_max_budget"] . ', ';
                            }
                        }

                        if (!empty($row["45mins_min_budget"]) && !empty($row["45mins_max_budget"])) {
                            $_45mins = "45" . _lang("mins") . ": $" . $row["45mins_min_budget"] . ' - ' . $row["45mins_max_budget"] . ', ';
                        } else {
                            if (!empty($row["45mins_min_budget"]) && empty($row["45mins_max_budget"])) {
                                $_45mins = "45" . _lang("mins") . ": " . _lang("Above") . " $" . $row["45mins_min_budget"] . ', ';
                            }

                            if (empty($row["45mins_min_budget"]) && !empty($row["45mins_max_budget"])) {
                                $_45mins = "45" . _lang("mins") . ": " . _lang("Below") . " $" . $row["45mins_max_budget"] . ', ';
                            }
                        }

                        if (!empty($row["60mins_min_budget"]) && !empty($row["60mins_max_budget"])) {
                            $_60mins = "60" . _lang("mins") . ": $" . $row["60mins_min_budget"] . ' - ' . $row["60mins_max_budget"] . ', ';
                        } else {
                            if (!empty($row["60mins_min_budget"]) && empty($row["60mins_max_budget"])) {
                                $_60mins = "60" . _lang("mins") . ": " . _lang("Above") . " $" . $row["60mins_min_budget"] . ', ';
                            }

                            if (empty($row["60mins_min_budget"]) && !empty($row["60mins_max_budget"])) {
                                $_60mins = "60" . _lang("mins") . ": " . _lang("Below") . " $" . $row["60mins_max_budget"] . ', ';
                            }
                        }

                        //district
                        $sql                          = "select *, master_type_code.name_" . $langcode . " as district_name from student_postjoblearndistrict
                        INNER JOIN master_type_code ON master_type_code.id = student_postjoblearndistrict.mas_teachareacode

                        where student_postjoblearndistrict.deleted = ? and student_postjoblearndistrict.studentpostjob_id = ?";
                        $parameters                   = array(0, $row["student_postjob_id"]);
                        $result                       = bind_pdo($sql, $parameters, "selectall");
                        $student_postjoblearndistrict = "";

                        if (!empty($result)) {
                            foreach ($result as $key2 => $row2) {
                                $student_postjoblearndistrict .= $row2["district_name"] . ", ";
                            }

                            if (!empty($student_postjoblearndistrict)) {
                                $student_postjoblearndistrict = substr_replace($student_postjoblearndistrict, "", -2);
                            }
                        } else {
                            $student_postjoblearndistrict = _lang("N/A");
                        }

                        //lesson time
                        $sql                      = "select * from student_postjoblearntime where studentpostjob_id = ? and deleted = ?";
                        $parameters               = array($row["student_postjob_id"], 0);
                        $student_postjoblearntime = bind_pdo($sql, $parameters, "selectall");

                        $order_by_week = array();
                        foreach ($student_postjoblearntime as $row2) {
                            $token                      = explode("_", $row2["learntime"]);
                            $order_by_week[$token[1]][] = $token[0];
                        }

                        $student_postjoblearntime_text = "";
                        $weekmap                       = array(1 => "Mon", 2 => "Tue", 3 => "Wed", 4 => "Thu", 5 => "Fri", 6 => "Sat", 7 => "Sun");

                        //foreach ($order_by_week as $key3 => $row3) {
                        for ($i = 1; $i <= 7; $i++) {
                            if (!empty($order_by_week[$i])) {
                                $student_postjoblearntime_text .= _lang($weekmap[$i]) . " ";
                                foreach ($order_by_week[$i] as $item3) {
                                    $student_postjoblearntime_text .= "[" . ($item3 == "am" ? _lang("Morning") : ($item3 == "pm" ? _lang("Afternoon") : _lang("Night"))) . "] ";
                                }
                                $student_postjoblearntime_text .= ", ";
                            }
                        }

                        if (!empty($student_postjoblearntime_text)) {
                            $student_postjoblearntime_text = substr_replace($student_postjoblearntime_text, "", -2);
                        }

                        //teaching mode
                        $teaching_mode = "";
                        $sql           = "select * from student_postjoblearnmode
                        INNER JOIN master_type_code ON master_type_code.id = student_postjoblearnmode.mas_teachmode

                        where student_postjoblearnmode.deleted = ? and student_postjoblearnmode.studentpostjob_id = ?";
                        $parameters    = array(0, $row["student_postjob_id"]);
                        $result        = bind_pdo($sql, $parameters, "selectall");

                        foreach ($result as $row3) {
                            $teaching_mode .= $row3["name_" . $langcode] . ", ";
                        }

                        if (!empty($teaching_mode)) {
                            $teaching_mode = substr_replace($teaching_mode, "", -2);
                        }

                        //teaching venus
                        $teaching_venus = "";
                        $sql            = "select * from student_postjoblearnvenus
                        INNER JOIN master_type_code ON master_type_code.id = student_postjoblearnvenus.mas_teachvenus

                        where student_postjoblearnvenus.deleted = ? and student_postjoblearnvenus.studentpostjob_id = ?";
                        $parameters     = array(0, $row["student_postjob_id"]);
                        $result         = bind_pdo($sql, $parameters, "selectall");

                        foreach ($result as $row3) {
                            $teaching_venus .= $row3["name_" . $langcode] . ", ";
                        }

                        if (!empty($teaching_venus)) {
                            $teaching_venus = substr_replace($teaching_venus, "", -2);
                        }
                        ?>
                        <div class="col-lg-12 col-md-12">
                            <div class="JobSection row" style="padding-bottom: 0; margin: 0 auto;/*width: auto; min-width: 100%;*/" id="job_<?= $row["student_postjob_id"] ?>">
                                <!--<div class="ProfileSectionTitle"></div>-->
                                <?php
                                    echo "<div class='col-sm-10 short_info' style='margin-bottom: 15px;'><div class='bold'>" . _lang("Pairing Number") . ": " . $row["postjob_no"] . "&nbsp;&nbsp;&nbsp;" . _lang("Post Date") . ": " . date("Y-m-d", strtotime($row["post_date"])) . "</div><br><table><tr><td><p><span class='job_title_text'>" . _lang("Student Info.") . "</span></td><td>" . _lang("Age") . ": " . $row["student_age_range"] . "<span class='job_field_space'></span>" . _lang("Gender") . ": " . ($row["gender"] == "m" ? _lang("Male") : ($row["gender"] == "f" ? _lang("Female") : _lang("Male / Female"))) . "<span class='job_field_space'></span>" . _lang("Expected Tutorial District") . ": " . $student_postjoblearndistrict . "</p></td></tr>";

                                    echo "<tr><td><p><span class='job_title_text'>" . _lang("Learning Requirement") . "</span></td><td>" . _lang("Musical Instrument") . ": " . $row["instrument_name"] . "<span class='job_field_space'></span>" . _lang("Learning Level") . ": " . $row["level_name"] . "<span class='job_field_space'></span>" . _lang("Tutorial Budget") . ": " . $_30mins . $_45mins . $_60mins . "</p></td></tr>";

                                    echo "<tr><td><p><span class='job_title_text'>" . _lang("Tutor Requirement Expectation") . "</span></td><td>" . _lang("Gender") . ": " . ($row["tutorreq_gender"] == "m" ? _lang("Male") : ($row["gender"] == "f" ? _lang("Female") : _lang("Male / Female"))) . "<span class='job_field_space'></span>" . _lang("Musical Instrument Level") . ": " . $row["tutor_instrument_level"] . "<span class='job_field_space'></span>" . _lang("Age") . ": " . $row["tutor_age"] . "<span class='job_field_space'></span>" . _lang("Special Request") . ": " . (!empty($row["tutorreq_others"]) ? $row["tutorreq_others"] : "") . "</p></td></tr>";

                                    //echo "<tr><td colspan='2'>連結</td></tr>";
                                    echo '</table></div>';

                                    //full info
                                    echo "<div class='col-sm-10 full_info' style='margin-bottom: 15px;'><div class='bold'>" . _lang("Pairing Number") . ": " . $row["postjob_no"] . "&nbsp;&nbsp;&nbsp;" . _lang("Post Date") . ": " . date("Y-m-d", strtotime($row["post_date"])) . "</div><br><table><tr><td><p><span class='job_title_text'>" . _lang("Student Info.") . "</span></td></tr><tr><td>" . _lang("Age") . ": " . $row["student_age_range"] . "<span class='job_field_space'></span>" . _lang("Gender") . ": " . ($row["gender"] == "m" ? _lang("Male") : ($row["gender"] == "f" ? _lang("Female") : _lang("Male / Female"))) . "<span class='job_field_space'></span>" . _lang("Expected Tutorial District") . ": " . $student_postjoblearndistrict . "<span class='job_field_space'></span>" . _lang("Expected Tutorial Venue") . ": " . $teaching_venus . "</p></td></tr>";

                                    echo "<tr><td><p><span class='job_title_text'>" . _lang("Learning Requirement") . "</span></td></tr><tr><td>" . _lang("Musical Instrument") . ": " . $row["instrument_name"] . "<span class='job_field_space'></span>" . _lang("Level") . ": " . $row["level_name"] . "<span class='job_field_space'></span>
                        " . _lang("Teaching Mode") . ": " . $teaching_mode . "<br>
                        " . _lang("Expected Tutorial Time") . ": " . $student_postjoblearntime_text . "<span class='job_field_space'></span>" . _lang("Lessons in a month") . ": " . $row["explessonpermonth"] . "
                        <br>" . _lang("Tutorial Budget") . ": " . $_30mins . $_45mins . $_60mins . "</p>
                        </td></tr>";

                                    echo "<tr><td><p><span class='job_title_text'>" . _lang("Tutor Requirement Expectation") . "</span></td></tr><tr><td>" . _lang("Gender") . ": " . ($row["tutorreq_gender"] == "m" ? _lang("Male") : ($row["gender"] == "f" ? _lang("Female") : _lang("Male / Female"))) . "<span class='job_field_space'></span>" . _lang("Musical Instrument Level") . ": " . $row["tutor_instrument_level"] . "<span class='job_field_space'></span>" . _lang("Age") . ": " . $row["tutor_age"] . "<span class='job_field_space'></span>";

                                    if ($row['approved'] == 1 && $row["tutorreq_others"]) {
                                        echo '<br>' . _lang("Special Request") . ": " . (!empty($row["tutorreq_others"]) ? $row["tutorreq_others"] : "");
                                    }

                                    echo '</p></td></tr>';

                                    echo "<tr><td colspan='2'>" . _lang("Link") . ": <a href='http://www.imusiccircle.com/job.php?id=" . $row["student_postjob_id"] . "'>" . $site_info["url"] . "job.php?id=" . $row["student_postjob_id"] . "</td></tr>";

                                    echo '</table></div>';

                                    if ($has_order === true || $row["completed_flag"] == 1) {
                                        echo '<div class="col-sm-2 tr nowrap padding0 "><button class="ContactTutorBTN" type="button" style="cursor: default; background-color: #575656;">' . _lang("Matched") . '</button></div>';
                                    } else {
                                        echo '<div class="col-sm-2 tr nowrap padding0 "><button class="ContactTutorBTN" type="button" onclick="contact_student(' . $row["student_postjob_id"] . ', ' . (int)$_SESSION["member_login"] . ');">' . _lang("Contact Student") . '</button></div>';
                                    }


                                    echo '<div style="clear:both"></div>';

                                    echo '<div class="col-sm-12 tc nowrap padding0">
<div class="expand pointer" style="margin: 5px 0"><span class="glyphicon glyphicon-chevron-up" aria-hidden="true"></span></div>
<div class="close_expand pointer" style="margin: 5px 0"><span class="glyphicon glyphicon-chevron-down" aria-hidden="true"></span></div>
                            </div>';
                                ?>
                            </div>
                        </div>
                        <?php
                    }
                }

                echo '<div class="col-lg-12">';
                if ($numpage > 1) {
                    pagenav();
                }
                echo '</div>';
            }

        }
    } else if ($data['ajax'] == 'chat_content') {
        $job_id        = (int)$_POST["job_id"];
        $user_id       = (int)$_POST["user_id"];
        $user_type     = $_POST["user_type"];
        $first_chat_id = $_POST["first_chat_id"];

        //security checking
        if ($_SESSION["is_tutor"] == 1 && $_SESSION["cmsrole"] == "user") {
            if ($user_type != "TUTOR" || $user_id != $_SESSION["member_login"]) {
                echo _lang("Access Denied!");
                exit;
            }
        } else if ($_SESSION["is_student"] == 1 && $_SESSION["cmsrole"] == "user") {
            if ($user_type != "STUDENT" || $user_id != $_SESSION["student_login"]) {
                echo _lang("Access Denied!");
                exit;
            }
        }

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

        if ($_POST["view_type"] == "job") {
            $where_clause .= " and chat.studentpostjob_id = ? and first_chat_id = ?";
            $parameters[] = $job_id;
            $parameters[] = $first_chat_id;
        } else if ($_POST["view_type"] == "user") {
            $where_clause = " and ((chat.from_id = ? and chat.from_role = ?) or (chat.to_id = ? and chat.to_role = ?)) ";
            $parameters[] = $user_id;
            $parameters[] = $user_type;
            $parameters[] = $user_id;
            $parameters[] = $user_type;

            if ($_SESSION["is_tutor"] == 1) {
                $job_info = get_studentpostjob2($job_id);
                $where_clause .= " and ( ((chat.from_id = ? and chat.from_role = ?) and (chat.to_id = ? and chat.to_role = ?)) or ((chat.from_id = ? and chat.from_role = ?) and (chat.to_id = ? and chat.to_role = ?)) )";
                $parameters[] = $job_info["studentmain_id"];
                $parameters[] = "STUDENT";
                $parameters[] = $_SESSION["member_login"];
                $parameters[] = "TUTOR";
                $parameters[] = $_SESSION["member_login"];
                $parameters[] = "TUTOR";
                $parameters[] = $job_info["studentmain_id"];
                $parameters[] = "STUDENT";
            } else if ($_SESSION["is_student"] == 1) {
                //$where_clause .= " and ((chat.from_id = ? and chat.from_role = ?) or (chat.to_id = ? and chat.to_role = ?)) ";
                $where_clause .= " and ( ((chat.from_id = ? and chat.from_role = ?) and (chat.to_id = ? and chat.to_role = ?)) or ((chat.from_id = ? and chat.from_role = ?) and (chat.to_id = ? and chat.to_role = ?)) )";
                $parameters[] = (int)$_POST["tutor_id"];
                $parameters[] = "TUTOR";
                $parameters[] = $_SESSION["student_login"];
                $parameters[] = "STUDENT";
                $parameters[] = $_SESSION["student_login"];
                $parameters[] = "STUDENT";
                $parameters[] = (int)$_POST["tutor_id"];
                $parameters[] = "TUTOR";
            }
        }

        $sql = "select chat.*, chat.id as chat_id, tutor_main.nickname, tutor_main.id as tutormain_id, student_main.contactname, student_main.id as studentmain_id, tutor_main.profilephoto_path
        from chat
        INNER JOIN tutor_main ON ( (tutor_main.id = chat.from_id and chat.from_role = 'TUTOR') or (tutor_main.id = chat.to_id and chat.to_role = 'TUTOR') )
        INNER JOIN student_main ON ( (student_main.id = chat.from_id and chat.from_role = 'STUDENT') or (student_main.id = chat.to_id and chat.to_role = 'STUDENT') )
        where chat.id > 0 {$where_clause} order by chat.sent_date ASC";
        //debug_log(123, dump_sql($sql, $parameters));
        $chats = bind_pdo($sql, $parameters, "selectall");

        $tutor_chat_count   = 1;
        $student_chat_count = 1;
        $approved_chat      = 0;
        $result             = '';
        $this_count_chat    = 0;
        $first_chat_id      = 0;
        foreach ($chats as $chat) {
            //skip not approved and not that user login chat
            if ($chat["approved"] != 1 && $chat["from_id"] != $user_id) {
                continue;
            } else {
                $approved_chat++;
            }

            if ($chat["chat_limit"] > 100) {
                $chat["chat_limit"] = "∞";
            }

            //update read
            if ((($chat["to_role"] == "STUDENT" && $chat["to_id"] == $_SESSION["student_login"]) || ($chat["to_role"] == "TUTOR" && $chat["to_id"] == $_SESSION["member_login"])) && empty($chat["read_date"])) {
                $sql        = "update chat set read_date = ? where id = ? ";
                $parameters = array(date("Y-m-d H:i:s"), $chat["chat_id"]);
                bind_pdo($sql, $parameters);
            }

            if ((($chat["from_role"] == "STUDENT" && $chat["from_id"] == $_SESSION["student_login"]) || ($chat["from_role"] == "TUTOR" && $chat["from_id"] == $_SESSION["member_login"])) && empty($chat["read_date"]) && $chat["deleted"] == 1) {
                $sql        = "update chat set read_date = ? where id = ? ";
                $parameters = array(date("Y-m-d H:i:s"), $chat["chat_id"]);
                bind_pdo($sql, $parameters);
            }

            $result .= "<div class='row' style='padding: 0 15px; margin-bottom: 15px;'>";

            $first_chat_id = $chat["first_chat_id"];

            if ($chat["from_role"] == $user_type) {
                if ($chat["deleted"] == 0) {
                    $this_count_chat++;
                }

                $result .= '<div class="col-xs-10" style="border: 1px solid #000; padding: 0;">
                                <div style="padding-left: 5px;">
                                    ' . nl2br($chat["content"]) . '
                                </div>
                                <div class="tr">';

                if ($chat["deleted"] == 1 && $chat["warning"] == 1) {
                    $result .= "<div style='color:red;'>這個對話違反對話規則，已被管理員刪除。<br><span style='color: #000;'>" . _lang("Date") . ": " . $chat["sent_date"] . "</div>";
                } else {
                    if ($chat["from_role"] == "TUTOR") {
                        $result .= _lang("Tutor") . ": " . ($_POST["view_type"] == "job" ? $tutor_chat_count : "-") . " / " . $chat["chat_limit"] . " " . _lang("Date") . ": " . $chat["sent_date"] . "&nbsp;";

                        if ($chat["deleted"] == 0) {
                            $tutor_chat_count++;
                        }

                    } else {
                        $result .= _lang("Student") . ": " . ($_POST["view_type"] == "job" ? $student_chat_count : "-") . " / " . $chat["chat_limit"] . " " . _lang("Date") . ": " . $chat["sent_date"] . "&nbsp;";

                        if ($chat["deleted"] == 0) {
                            $student_chat_count++;
                        }

                    }
                }

                $result .= '
                                </div>
                            </div>
                            <div class="col-xs-2 tr padding0">';
                if ($user_type == "TUTOR") {
                    $result .= '<img src="../file/teacher/' . $chat["profilephoto_path"] . '" style="height: 50px;">';
                } else {
                    $result .= '<img src="dist/img/user2-160x160.jpg" style="height: 50px;">';
                }
                $result .= '</div>';
            } else {
                $result .= '<div class="col-xs-2 trl padding0">';
                if ($user_type == "STUDENT") {
                    $result .= '<img src="../file/teacher/' . $chat["profilephoto_path"] . '" style="height: 50px;">';
                } else {
                    $result .= '<img src="dist/img/user2-160x160.jpg" style="height: 50px;">';
                }
                $result .= '</div>
                            <div class="col-xs-10 " style="border: 1px solid #000; padding: 0; ">
                                <div style="padding-left: 5px;">
                                    ' . nl2br($chat["content"]) . '
                                </div>
                                <div class="tr">';

                if ($chat["deleted"] == 1) {
                    $result .= "<p style='color:red;'>這個對話違反對話規則，已被管理員刪除。</p>";
                } else {
                    if ($chat["from_role"] == "TUTOR") {
                        $result .= _lang("Tutor") . ": " . ($_POST["view_type"] == "job" ? $tutor_chat_count : "-") . " / " . $chat["chat_limit"] . " " . _lang("Date") . ": " . $chat["sent_date"] . "&nbsp;";

                        if ($chat["deleted"] == 0) {
                            $tutor_chat_count++;
                        }

                    } else {
                        $result .= _lang("Student") . ": " . ($_POST["view_type"] == "job" ? $student_chat_count : "-") . " / " . $chat["chat_limit"] . " " . _lang("Date") . ": " . $chat["sent_date"] . "&nbsp;";

                        if ($chat["deleted"] == 0) {
                            $student_chat_count++;
                        }

                    }
                }

                $result .= '
                                </div>
                            </div>';
            }
            $result .= "</div>";
        }

        $result .= "<input type='hidden' name='this_first_chat_id' id='this_first_chat_id' value='" . $first_chat_id . "'>";
        $result .= "<input type='hidden' name='this_count_chat' id='this_count_chat' value='" . $this_count_chat . "'>";
        $result .= "<input type='hidden' name='this_chat_limit' id='this_chat_limit' value='" . $chats[0]["chat_limit"] . "'>";

        if (!empty($chats) && $approved_chat > 0) {
            $result .= "<div class='tr'>" . _lang("Chat History") . "</div>";

            if ($_SESSION["cmsrole"] == "super_admin" || $_SESSION["cmsrole"] == "admin") {
                $result .= "<div><input type='text' name='chat_limit' id='chat_limit' value='" . $chats[0]["chat_limit"] . "' style='height: 30px; width: 50px;'><button type='button' class='btn btn-primary' id='update_chat_limit' onclick='update_chat_limit(" . $chats[0]["studentpostjob_id"] . ", " . $chats[0]["from_id"] . ", " . $chats[0]["to_id"] . ")' style='margin-left: 10px;'>" . _lang("Update Chat Limit") . "</button></div>";
            }

        } else {
            $result .= _lang("No chats");
        }

        //check if trial invitation sent
        $tutormain_id   = "";
        $studentmain_id = "";
        if ($chats[0]["from_role"] == "TUTOR") {
            $tutormain_id   = $chats[0]["from_id"];
            $studentmain_id = $chats[0]["to_id"];
        } else if ($chats[0]["from_role"] == "STUDENT") {
            $studentmain_id = $chats[0]["from_id"];
            $tutormain_id   = $chats[0]["to_id"];
        }

        $sql        = "select *, `order`.id as order_id from `order`
        INNER JOIN master_type_code ON master_type_code.id = `order`.mas_residencecode
        where `order`.job_id = ? and `order`.studentmain_id = ? and `order`.tutormain_id = ? and `order`.deleted = ? and `order`.status != ? order by `order`.id DESC";
        $parameters = array($job_id, $studentmain_id, $tutormain_id, 0, "cancel");
        $order_info = bind_pdo($sql, $parameters, "selectone");
        if (!empty($order_info)) {
            $order_from       = $order_info["from_type"];
            $order_id         = $order_info["order_id"];
            $order_id_encrypt = aes_crypt($order_id, 1);

            $result .= "<hr>";

            if ($order_from == "tutor") {
                if ($_SESSION["is_tutor"] == 1 && $order_info["status"] == "process") {
                    $result .= "<p>" . _lang("You have sent an tutorial invitation to student.") . " <a href='order_form.php?id=" . $order_id . "'>" . _lang("Detail") . "</a></p>";
                } else if ($_SESSION["is_tutor"] == 1 && $order_info["status"] == "cancel") {
                    $result .= "<p>" . _lang("You have canceled an tutorial invitation.") . " <a href='order_form.php?id=" . $order_id . "'>" . _lang("Detail") . "</a></p>";
                } else if ($_SESSION["is_tutor"] == 1 && $order_info["status"] == "accept") {
                    $result .= "<p>" . _lang("Tutorial invitation has been accepted.") . " <a href='order_form.php?id=" . $order_id . "'>" . _lang("Detail") . "</a></p>";
                } else if ($_SESSION["is_tutor"] == 1 && $order_info["status"] == "reject") {
                    $result .= "<p>" . _lang("Tutorial invitation has been rejected.") . " <a href='order_form.php?id=" . $order_id . "'>" . _lang("Detail") . "</a></p>";
                } else if ($_SESSION["is_tutor"] == 1 && $order_info["status"] == "paid") {
                    $result .= "<p>" . _lang("Tutorial invitation has been paid.") . " <a href='order_form.php?id=" . $order_id . "'>" . _lang("Detail") . "</a></p>";
                } else if ($_SESSION["is_student"] == 1 && $order_info["status"] == "process") {
                    $result .= "<p>" . _lang("You have received an tutorial invitation from tutor.") . " <a href='order_form.php?id=" . $order_id . "'>" . _lang("Detail") . "</a></p>";
                } else if ($_SESSION["is_student"] == 1 && $order_info["status"] == "cancel") {
                    $result .= "<p>" . _lang("Tutorial invitation has been canceled.") . "</p>";
                } else if ($_SESSION["is_student"] == 1 && $order_info["status"] == "accept") {
                    $result .= "<p>" . _lang("You have accepted an tutorial invitation from tutor.") . " <a href='order_form.php?id=" . $order_id . "'>" . _lang("Click To Paid.") . "</a></p>";
                } else if ($_SESSION["is_student"] == 1 && $order_info["status"] == "reject") {
                    $result .= "<p>" . _lang("You have rejected an tutorial invitation from tutor.") . " <a href='order_form.php?id=" . $order_id . "'>" . _lang("Detail") . "</a></p>";
                } else if ($_SESSION["is_student"] == 1 && $order_info["status"] == "paid") {
                    $result .= "<p>" . _lang("You have paid an tutorial invitation from tutor.") . " <a href='order_form.php?id=" . $order_id . "'>" . _lang("Detail") . "</a></p>";
                }
            } else if ($order_from == "student") {
                if ($_SESSION["is_tutor"] == 1 && $order_info["status"] == "process") {
                    $result .= "<p>" . _lang("You have received an tutorial invitation from student.") . " <a href='order_form.php?id=" . $order_id . "'>" . _lang("Detail") . "</a></p>";
                } else if ($_SESSION["is_tutor"] == 1 && $order_info["status"] == "cancel") {
                    $result .= "<p>" . _lang("Tutorial invitation has been canceled.") . "</p>";
                } else if ($_SESSION["is_tutor"] == 1 && $order_info["status"] == "accept") {
                    $result .= "<p>" . _lang("You have accepted an tutorial invitation from tutor.") . " <a href='order_form.php?id=" . $order_id . "'>" . _lang("Detail") . "</a></p>";
                } else if ($_SESSION["is_tutor"] == 1 && $order_info["status"] == "reject") {
                    $result .= "<p>" . _lang("You have rejected an tutorial invitation from tutor.") . " <a href='order_form.php?id=" . $order_id . "'>" . _lang("Detail") . "</a></p>";
                } else if ($_SESSION["is_tutor"] == 1 && $order_info["status"] == "paid") {
                    $result .= "<p>" . _lang("Tutorial invitation has been paid.") . " <a href='order_form.php?id=" . $order_id . "'>" . _lang("Detail") . "</a></p>";
                } else if ($_SESSION["is_student"] == 1 && $order_info["status"] == "process") {
                    $result .= "<p>" . _lang("You have sent an tutorial invitation to tutor.") . " <a href='order_form.php?id=" . $order_id . "'>" . _lang("Detail") . "</a></p>";

                } else if ($_SESSION["is_student"] == 1 && $order_info["status"] == "cancel") {
                    $result .= "<p>" . _lang("Tutorial invitation has been canceled.") . " <a href='order_form.php?id=" . $order_id . "'>" . _lang("Detail") . "</a></p>";

                } else if ($_SESSION["is_student"] == 1 && $order_info["status"] == "accept") {
                    $result .= "<p>" . _lang("Tutorial invitation has been accepted.") . " <a href='order_form.php?id=" . $order_id . "'>" . _lang("Detail") . "</a></p>";

                } else if ($_SESSION["is_student"] == 1 && $order_info["status"] == "reject") {
                    $result .= "<p>" . _lang("Tutorial invitation has been rejected.") . " <a href='order_form.php?id=" . $order_id . "'>" . _lang("Detail") . "</a></p>";

                } else if ($_SESSION["is_student"] == 1 && $order_info["status"] == "paid") {
                    $result .= "<p>" . _lang("Tutorial invitation has been paid.") . " <a href='order_form.php?id=" . $order_id . "'>" . _lang("Detail") . "</a></p>";

                }
            }
        }


        $json                    = new Services_JSON();
        $jsonReturnArr['result'] = $result;

        $job_info                     = get_studentpostjob2($job_id);
        $jsonReturnArr['job_deleted'] = $job_info["deleted"];

        if ($_SESSION["is_tutor"] == 1) {
            $jsonReturnArr['from_type'] = 'tutor';
        } else if ($_SESSION["is_student"] == 1) {
            $jsonReturnArr['from_type'] = 'student';
        }

        if ($chats[0]["from_role"] == "TUTOR") {
            //if ($_SESSION["is_tutor"] == 1) {
            $jsonReturnArr['tutor_id']   = $chats[0]["from_id"];
            $jsonReturnArr['student_id'] = $chats[0]["to_id"];
        } else if ($chats[0]["from_role"] == "STUDENT") {
            //} else if ($_SESSION["is_student"] == 1) {
            $jsonReturnArr['student_id'] = $chats[0]["from_id"];
            $jsonReturnArr['tutor_id']   = $chats[0]["to_id"];
        }

        $jsonReturnArr['first_chat_id'] = $chats[0]["first_chat_id"];

        //
        $sql        = "select count(*) as count from `order` where tutormain_id = ? and studentmain_id = ? and order_type = 'try' and job_id = ? and (status = 'paid' or status = 'process' or status = 'approved' or (from_type = ? and status = 'reject')) and deleted = 0 ";
        $parameters = array($jsonReturnArr['tutor_id'], $jsonReturnArr['student_id'], $job_id, $jsonReturnArr['from_type']);
        $row_order  = bind_pdo($sql, $parameters, "selectone");

        // 是否有已付款的試堂
        $sql            = "select count(*) as count from `order` where tutormain_id = ? and studentmain_id = ? and order_type = 'try' and status = 'paid' and deleted = 0 and job_id = ?";
        $parameters     = array($jsonReturnArr['tutor_id'], $jsonReturnArr['student_id'], $job_id);
        $row_paid_order = bind_pdo($sql, $parameters, "selectone");

        //debug_log(111, $row_paid_order['count']);
        if ($row_paid_order['count'] > 0) { //如果有試堂
            // 是否有已邀請或已拒絕的正堂
            $sql              = "select count(*) as count from `order` where tutormain_id = ? and studentmain_id = ? and order_type = 'formal' and job_id = ? and (status = 'process' or (from_type = ? and status = 'reject')) and deleted = 0";
            $parameters       = array($jsonReturnArr['tutor_id'], $jsonReturnArr['student_id'], $job_id, $jsonReturnArr['from_type']);
            $row_formal_order = bind_pdo($sql, $parameters, "selectone");

            if ($row_formal_order['count'] < 1) { // 如沒有
                $jsonReturnArr['formal_status'] = true;
            } else { //如有
                $jsonReturnArr['formal_status'] = false;
            }
            //$jsonReturnArr['formal_status'] = true;
            $jsonReturnArr['test_status'] = false;
        } else { //如未有已付款的試堂
            if ($row_order['count'] < 1) {
                $jsonReturnArr['test_status'] = true;
            } else {
                $jsonReturnArr['test_status'] = false;
            }
            //$jsonReturnArr['test_status'] = true;
            $jsonReturnArr['formal_status'] = false;
        }

        //debug_log(123, $jsonReturnArr['formal_status'] );
        //debug_log(123, $jsonReturnArr['test_status'] );

        $sql        = "select * from master_type_code as tt where typeid = ? and deleted = ? and (select count(*) from master_type_code as tb where typeid = 'TEACH_POSITIONCODE' and extra1 = tt.code and deleted = 0 and (select count(*) from tutor_teachareacode where tutormain_id = '" . $jsonReturnArr['tutor_id'] . "' and mas_teachareacode = tb.id and deleted = 0) > 0) > 0";
        $parameters = array('TEACH_AREACODE', 0);
        $rows_area  = bind_pdo($sql, $parameters, "selectall");

        $row_job   = get_studentpostjob($job_id);
        $row_tutor = get_teacher($jsonReturnArr['tutor_id']);

        $sql             = "select * from master_type_code where typeid = ? and deleted = ?";
        $parameters      = array('TEACH_VENUS', 0);
        $rows_teachvenus = bind_pdo($sql, $parameters, "selectall");

        $sql        = "select id from tutor_charge where tutormain_id = ? and mas_instrument = ? and deleted = ?";
        $parameters = array($row_tutor['id'], $row_job['mas_instrument'], 0);
        $row_charge = bind_pdo($sql, $parameters, "selectone");

        $sql = "select grade from tutor_expectcharge where charge_id = ? and type = ? and deleted = ?";
        if ($jsonReturnArr['formal_status'] == 'true') {
            $parameters = array($row_charge['id'], 'fee', 0);
        } else {
            $parameters = array($row_charge['id'], 'tfee', 0);
        }
        $rows_expectcharge = bind_pdo($sql, $parameters, "selectall");

        $sql            = "select name_en,name_cn from master_type_code where id = ? and typeid = ? and deleted = ? LIMIT 1";
        $parameters     = array($row_job['mas_instrument'], 'INSTRUMENT', 0);
        $row_instrument = bind_pdo($sql, $parameters, "selectone");

        $jsonReturnArr['instrumentBox'] = $row_instrument['name_' . get_langcode()];

        $jsonReturnArr['learnAreaBox'] = '<select class="form-control" name="mas_residencecode" data-value="">
                                            <option value="">-</option>';
        if (is_array($rows_area)) {
            foreach ($rows_area as $row_area) {
                $sql           = "select * from master_type_code as tb where typeid = ? and extra1 = ? and deleted = ? and (select count(*) from tutor_teachareacode where tutormain_id = '" . $jsonReturnArr['tutor_id'] . "' and mas_teachareacode = tb.id and deleted = 0) > 0";
                $parameters    = array('TEACH_POSITIONCODE', $row_area['code'], 0);
                $rows_position = bind_pdo($sql, $parameters, "selectall");
                $jsonReturnArr['learnAreaBox'] .= '<optgroup label="' . $row_area['name_cn'] . '">';
                if (is_array($rows_position)) {
                    foreach ($rows_position as $row_position) {
                        $jsonReturnArr['learnAreaBox'] .= '<option value="' . $row_position['id'] . '">' . $row_position['name_cn'] . '</option>';
                    }
                }
                $jsonReturnArr['learnAreaBox'] .= '</optgroup>';
                /**/
            }
        }
        $jsonReturnArr['learnAreaBox'] .= '</select>';

        $jsonReturnArr['teachvenusBox'] = '<select class="form-control" name="course_venus">
                                              <option value="">-</option>';
        foreach ($rows_teachvenus as $row_teachvenus) {
            $sql              = "select * from tutor_teachvenus where tutormain_id = ? and mas_teachvenus = ? and deleted = ?";
            $parameters       = array($row_tutor['id'], $row_teachvenus['id'], 0);
            $count_teachvenus = bind_pdo($sql, $parameters, "selectone");
            if ($count_teachvenus['id']) {
                $jsonReturnArr['teachvenusBox'] .= '<option value="' . $row_teachvenus['id'] . '">' . $row_teachvenus['name_' . get_langcode()] . '</option>';
            }
        }
        $jsonReturnArr['teachvenusBox'] .= '</select>';

        $jsonReturnArr['levelBox'] = '<select class="form-control" name="level">
                                              <option value="">-</option>';
        foreach ($rows_expectcharge as $row_expectcharge) {
            if ($jsonReturnArr['formal_status'] == true) {
                $jsonReturnArr['levelBox'] .= '<option value="' . $row_expectcharge['grade'] . '">' . $row_expectcharge['grade'] . '</option>';
            } else {
                if ($row_expectcharge['grade'] == 1) {
                    $jsonReturnArr['levelBox'] .= '<option value="1">1 - 2</option>';
                }
                if ($row_expectcharge['grade'] == 2) {
                    $jsonReturnArr['levelBox'] .= '<option value="2">3 - 5</option>';
                }
                if ($row_expectcharge['grade'] == 3) {
                    $jsonReturnArr['levelBox'] .= '<option value="3">' . _lang("6 or above 6") . '</option>';
                }
            }
        }
        $jsonReturnArr['levelBox'] .= '</select>';

        $jsonReturnArr['learnTimeBox'] = '<select class="form-control" name="course_length">
                                              <option value="">-</option>
                                          </select>';
        $jsonReturnArr['job_id']       = $job_id;
        echo $json->encode($jsonReturnArr);
    } else if ($data['ajax'] == 'test_course_length') {
        $json          = new Services_JSON();
        $job_id        = $_POST['job_id'];
        $tutor_id      = $_POST['tutor_id'];
        $grade         = $_POST['grade'];
        $formal_status = $_POST['formal_status'];

        $row_job = get_studentpostjob($job_id);

        $sql        = "select id from tutor_charge as tt where tutormain_id = ? and mas_instrument = ? and deleted = ?";
        $parameters = array($tutor_id, $row_job['mas_instrument'], 0);
        $row_charge = bind_pdo($sql, $parameters, "selectone");

        $sql = "select charge_30min,charge_45min,charge_60min from tutor_expectcharge where charge_id = ? and type = ? and grade = ? and deleted = ?";
        if ($formal_status == 'true') {
            $parameters = array($row_charge['id'], 'fee', $grade, 0);
        } else {
            $parameters = array($row_charge['id'], 'tfee', $grade, 0);
        }
        $row_expectcharge = bind_pdo($sql, $parameters, "selectone");

        $jsonReturnArr['learnTimeBox'] = '<select class="form-control" name="course_length">
                                              <option value="">-</option>';
        if ($row_expectcharge['charge_30min']) {
            $jsonReturnArr['learnTimeBox'] .= '<option value="30">30分鐘</option>';
        }
        if ($row_expectcharge['charge_45min']) {
            $jsonReturnArr['learnTimeBox'] .= '<option value="45">45分鐘</option>';
        }
        if ($row_expectcharge['charge_60min']) {
            $jsonReturnArr['learnTimeBox'] .= '<option value="60">60分鐘</option>';
        }
        $jsonReturnArr['learnTimeBox'] .= '</select>';
        $jsonReturnArr['job_id']   = $job_id;
        $jsonReturnArr['tutor_id'] = $tutor_id;
        echo $json->encode($jsonReturnArr);
    } else if ($data['ajax'] == 'test_fee') {
        $type              = $_POST['type'];
        $job_id            = (int)$_POST["job_id"];
        $tutor_id          = (int)$_POST["tutor_id"];
        $mas_residencecode = (int)$_POST["mas_residencecode"];
        $course_length     = (int)$_POST["course_length"];
        $course_venus      = (int)$_POST["course_venus"];
        $level             = $_POST["level"];
        $json              = new Services_JSON();
        if ($mas_residencecode && $course_length && $course_venus) {
            $sql        = "select mas_instrument from student_postjob as tt where id = ? and deleted = ?";
            $parameters = array($job_id, 0);
            $row_job    = bind_pdo($sql, $parameters, "selectone");
            if ($course_venus == 6) {
                $sql           = "select studenhome_addcharge as fee from tutor_teachareacode as tt where mas_teachareacode = ? and tutormain_id = ? and deleted = ?";
                $parameters    = array($mas_residencecode, $tutor_id, 0);
                $row_addcharge = bind_pdo($sql, $parameters, "selectone");
            }
            $sql        = "select id from tutor_charge as tt where tutormain_id = ? and mas_instrument = ? and deleted = ?";
            $parameters = array($tutor_id, $row_job['mas_instrument'], 0);
            $row_charge = bind_pdo($sql, $parameters, "selectone");

            $sql = "select charge_" . $course_length . "min as fee from tutor_expectcharge where type = ? and charge_id = ? and grade = ? and deleted = ?";
            if ($type == 'try') {
                $parameters = array('tfee', $row_charge['id'], $level, 0);
            } else if ($type == 'formal') {
                $parameters = array('fee', $row_charge['id'], $level, 0);
            }
            $row_expectcharge = bind_pdo($sql, $parameters, "selectone");

            if ($type == 'try') {
                $jsonReturnArr['extra_fee'] = intval($row_addcharge['fee']);
                $jsonReturnArr['fee']       = intval($row_expectcharge['fee']);
                $jsonReturnArr['total_fee'] = (intval($jsonReturnArr['fee']) + intval($jsonReturnArr['extra_fee'])) * 2;
            } else if ($type == 'formal') {
                $jsonReturnArr['fee_min'] = (intval($row_expectcharge['fee']) + intval($row_addcharge['fee'])) * 0.8;
                $jsonReturnArr['fee_max'] = (intval($row_expectcharge['fee']) + intval($row_addcharge['fee'])) * 1.2;
            }
        } else {
            $jsonReturnArr['extra_fee'] = '';
            $jsonReturnArr['fee']       = '';
            $jsonReturnArr['total_fee'] = '';
        }
        $jsonReturnArr['type'] = $type;
        echo $json->encode($jsonReturnArr);
    } else if ($data['ajax'] == 'testcourse_submit') {

        $json              = new Services_JSON();
        $job_id            = (int)$_POST["job_id"];
        $tutor_id          = (int)$_POST["tutor_id"];
        $student_id        = (int)$_POST["student_id"];
        $order_type        = $_POST["order_type"];
        $course_venus      = $_POST["course_venus"];
        $level             = $_POST["level"];
        $mas_residencecode = (int)$_POST["mas_residencecode"];
        $course_length     = (int)$_POST["course_length"];
        $course_count      = 0;
        $from_type         = $_POST["from_type"];
        $first_chat_id     = (int)$_POST["first_chat_id"];
        if ($order_type == 'try') {
            $datetime1 = $_POST["datetime1"];
            $datetime2 = $_POST["datetime2"];
            if ($datetime1) {
                $datetime[] = $datetime1;
                $course_count++;
            }
            if ($datetime2) {
                $datetime[] = $datetime2;
                $course_count++;
            }
        } else if ($order_type == 'formal') {
            $course_count = $_POST["course_count"];
        }

        if ($mas_residencecode && $mas_residencecode && $course_length && $course_venus && $level && ($order_type == 'formal' || ($order_type == 'try' && $datetime1 && $datetime2))) {
            $sql        = "select count(*) as count from `order` where tutormain_id = ? and studentmain_id = ? and job_id = ? and order_type = ? and (status = 'paid' or status = 'reject' or status = 'process') and deleted = 0";
            $parameters = array($tutor_id, $student_id, $job_id, 'try');
            $row_order  = bind_pdo($sql, $parameters, "selectone");
            if (($order_type == 'try' && $row_order['count'] < 1) || ($order_type == 'formal' && $row_order['count'] >= 1)) {

                $sql         = "select count(*)+1 as maxcount from `order`";
                $parameters  = array();
                $row_orderno = bind_pdo($sql, $parameters, "selectone");

                $sql        = "select mas_instrument from student_postjob as tt where id = ? and deleted = ?";
                $parameters = array($job_id, 0);
                $row_job    = bind_pdo($sql, $parameters, "selectone");

                if ($order_type == 'try') {
                    if ($course_venus == 6) {
                        $sql           = "select studenhome_addcharge as fee from tutor_teachareacode as tt where mas_teachareacode = ? and tutormain_id = ? and deleted = ?";
                        $parameters    = array($mas_residencecode, $tutor_id, 0);
                        $row_addcharge = bind_pdo($sql, $parameters, "selectone");
                    }

                    $sql              = "select id from tutor_charge as tt where tutormain_id = ? and mas_instrument = ? and deleted = ?";
                    $parameters       = array($tutor_id, $row_job['mas_instrument'], 0);
                    $row_charge       = bind_pdo($sql, $parameters, "selectone");
                    $sql              = "select charge_" . $course_length . "min as fee from tutor_expectcharge where type = ? and charge_id = ? and grade = ? and deleted = ?";
                    $parameters       = array('tfee', $row_charge['id'], $level, 0);
                    $row_expectcharge = bind_pdo($sql, $parameters, "selectone");
                }
                $order_data['id']                = $row_orderno['maxcount'];
                $order_data['order_type']        = $order_type;
                $order_data['studentmain_id']    = $student_id;
                $order_data['tutormain_id']      = $tutor_id;
                $order_data['job_id']            = $job_id;
                $order_data['course_venus']      = $course_venus;
                $order_data['level']             = $level;
                $order_data['mas_residencecode'] = $mas_residencecode;
                $order_data['course_count']      = $course_count;
                $order_data['course_length']     = $course_length;
                if ($order_type == 'try') {
                    $order_data['order_no']  = 'OT' . date('ymd') . sprintf("%06d", $row_orderno['maxcount']);
                    $order_data['extra_fee'] = $row_addcharge['fee'] ? $row_addcharge['fee'] : 0;
                    $order_data['fee']       = $row_expectcharge['fee'];
                } else if ($order_type == 'formal') {
                    $order_data['order_no']  = 'OF' . date('ymd') . sprintf("%06d", $row_orderno['maxcount']);
                    $order_data['extra_fee'] = 0;
                    $order_data['fee']       = $_POST['fee'] ? $_POST['fee'] : 0;
                }
                $order_data['total_fee']     = ($order_data['fee'] + $order_data['extra_fee']) * $course_count;
                $order_data['from_type']     = $from_type;
                $order_data['first_chat_id'] = $first_chat_id;
                $order_data['createdate']    = date("Y-m-d H:i:s");
                $order_data['status']        = 'process';
                $sql                         = mysql_install($order_data, 'order', 'add');
                $arraykey                    = array_keys($order_data);
                unset($parameters);
                for ($i = 0; $i < count($arraykey); $i++) {
                    $parameters[$i] = $order_data[$arraykey[$i]];
                }
                bind_pdo($sql, $parameters);
                send_order_notification('process', $order_data['id']);

                $notification_title = "MusicCircle";
                $notification_body = _lang("你有1個試堂邀請，請查看及確定。");
                $app_notification_id = create_app_notification($notification_title, $notification_body, "SENT", "trial_invite");
                if($_SESSION["is_tutor"] == 1){
                    $user = get_student($student_id);
                }else if($_SESSION["is_student"] == 1){
                    $user = get_tutor($tutor_id);
                }
                $cmsloginid = $user["cmsloginid"];

                send_app_notification($app_notification_id, $cmsloginid, null, $notification_title, $notification_body);


                if ($order_type == 'try') {
                    for ($a = 0; $a < $course_count; $a++) {
                        unset($tmp_order_data);
                        $tmp_order_data['order_id']       = $order_data['id'];
                        $tmp_order_data['datetime']       = $datetime[$a];
                        $tmp_order_data['tutor_status']   = 0;
                        $tmp_order_data['student_status'] = 0;
                        $tmp_order_data['sort']           = $a;
                        $sql                              = mysql_install($tmp_order_data, 'order_detail', 'add');
                        $arraykey                         = array_keys($tmp_order_data);
                        unset($parameters);
                        for ($i = 0; $i < count($arraykey); $i++) {
                            $parameters[$i] = $tmp_order_data[$arraykey[$i]];
                        }
                        bind_pdo($sql, $parameters);
                    }
                }

                $jsonReturnArr['status'] = true;
            } else {
                $jsonReturnArr['status'] = false;
            }
            echo $json->encode($jsonReturnArr);
        }
    } else if ($data['ajax'] == 'order') {
        $json = new Services_JSON();
        $id   = intval(aes_crypt($_POST['order_id'], 2));
        if ($id) {
            $sql        = "select * from `order` where id = ? and status = 'process' and deleted = ? LIMIT 1";
            $parameters = array($id, 0);
            $row_order  = bind_pdo($sql, $parameters, "selectone");

            if ($data['type'] == 'accept') {
                if ($row_order['id']) {
                    $sql        = "select mas_instrument from student_postjob as tt where id = ? and deleted = ?";
                    $parameters = array($row_order['job_id'], 0);
                    $row_job    = 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");

                    $shipping_price = 0;
                    $amount         = $row_order['total_fee'];
                    $invoiceNo      = $row_order['order_no'];
                    //$amount = 1;
                    $extra_url    = EXTRA_URL;
                    $paypal_url   = PAYPAL_URL;
                    $paypal_array = array('cmd'           => '_xclick',
                                          'upload'        => '1',
                                          'custom'        => $id,
                                          'invoice'       => $invoiceNo,
                                          'business'      => PAYPAL_BUSINESS,
                                          'item_name'     => $row_instrument['name_en'] . ' - Course Fee',
                        //'item_name'        =>'a',
                                          'item_number'   => $invoiceNo,
                                          'currency_code' => 'HKD',
                                          'shipping'      => $shipping_price,
                                          'no_shipping'   => 1,
                                          'amount'        => $amount,
                        //'image_url'        =>$GLOBALS['site_info']['full_path'].'images/logo.png',
                                          'return'        => 'http://' . $_SERVER['HTTP_HOST'] . '/' . $extra_url . 'webadmin/chat_index.php?type=1',
                                          'notify_url'    => 'http://' . $_SERVER['HTTP_HOST'] . '/' . $extra_url . 'paypal_notify.php',
                                          'cancel_return' => 'http://' . $_SERVER['HTTP_HOST'] . '/' . $extra_url . 'webadmin/chat_index.php?type=1'
                    );

                    $array = array();
                    foreach ($paypal_array as $name => $data) {
                        $array[] = $name . '=' . urlencode($data);
                    }
                    $path                    = $paypal_url . '?' . implode('&', $array);
                    $jsonReturnArr['url']    = $path;
                    $jsonReturnArr['status'] = true;
                }
            } else if ($data['type'] == 'reject' || $data['type'] == 'cancel') {
                unset($tmp_order_data);
                $tmp_order_data['status'] = $data['type'];
                $sql                      = mysql_install($tmp_order_data, 'order', 'edit', 'id');
                $tmp_order_data['id']     = $id;
                $arraykey                 = array_keys($tmp_order_data);
                unset($parameters);
                for ($i = 0; $i < count($arraykey); $i++) {
                    $parameters[$i] = $tmp_order_data[$arraykey[$i]];
                }
                bind_pdo($sql, $parameters);
                //$jsonReturnArr['sql'] = $sql;
                $jsonReturnArr['job_id'] = $row_order['job_id'];
                $jsonReturnArr['status'] = true;
            }
        } else {
            $jsonReturnArr['status'] = false;
        }
        echo $json->encode($jsonReturnArr);
    } else if ($data['ajax'] == 'update_chat_limit') {
        $job_id     = (int)$_POST["job_id"];
        $from_id    = (int)$_POST["from_id"];
        $to_id      = (int)$_POST["to_id"];
        $chat_limit = (int)$_POST["chat_limit"];

        if (($from_id == $_SESSION["member_login"] || $from_id != $_SESSION["student_id"]) && ($to_id == $_SESSION["member_login"] || $to_id != $_SESSION["student_id"])) {
            $sql        = "update chat set chat_limit = ? where studentpostjob_id = ? and ( (from_id = ? and to_id = ?) or (from_id = ? and to_id = ?) )";
            $parameters = array($chat_limit, $job_id, $from_id, $to_id, $to_id, $from_id);
            bind_pdo($sql, $parameters);

            echo _lang("Update Successfully.");
        } else {
            //error
            echo _lang("Cannot Update.");
        }

    } else if ($data['ajax'] == 'coupon') {
        $json = new Services_JSON();

        $limit_type  = $_POST['limit_type'];
        $coupon_code = strtoupper($_POST['coupon']);
        $row         = get_order((int)$_POST["id"]);

        //hardcode for fixed price to $X
        if ((strtoupper($coupon_code) == "INKY20" || strtoupper($coupon_code) == "IMC$20") && $limit_type == "try") {
            //check if promotion code used
            $sql        = "select count(*) as coupon_used_num from `order` where coupon_code = ? and deleted = ? and (status = ? or status = ?)";
            $parameters = array($coupon_code, 0, "paid", "approved");
            $result     = bind_pdo($sql, $parameters, "selectone");

            if ($result["coupon_used_num"] < 100) {
                $jsonReturnArr['status']   = true;
                $jsonReturnArr['total']    = 20;
                $jsonReturnArr['discount'] = $row['total_fee'] - $jsonReturnArr['total'];

                $tmp_order_data['coupon_fee']  = $jsonReturnArr['discount'];
                $tmp_order_data['coupon_code'] = $coupon_code;
                $sql                           = mysql_install($tmp_order_data, 'order', 'edit', 'id');
                $tmp_order_data['id']          = $row['id'];
                $arraykey                      = array_keys($tmp_order_data);
                unset($parameters);
                for ($i = 0; $i < count($arraykey); $i++) {
                    $parameters[$i] = $tmp_order_data[$arraykey[$i]];
                }
                //$jsonReturnArr['sql'] = $sql;
                bind_pdo($sql, $parameters);
            } else {
                $jsonReturnArr['status'] = false;
            }
            echo $json->encode($jsonReturnArr);
            exit;
        }

        $sql        = "select id,discount_type,number,one_time from coupon where coupon_code = ? and status = ? and limit_type = ?";
        $parameters = array($coupon_code, 1, $limit_type);
        $row_coupon = bind_pdo($sql, $parameters, "selectone");

        if ($row_coupon['id']) {
            if ($row_coupon['one_time'] == 1) {
                //check if one time promotion code used
                $sql        = "select id from `order` where coupon_code = ? and deleted = ? and (status = ? or status = ?)";
                $parameters = array($coupon_code, 0, "paid", "approved");
                $result     = bind_pdo($sql, $parameters, "selectone");

                if (!empty($result)) {
                    $jsonReturnArr['status'] = false;
                } else {
                    $jsonReturnArr['status'] = true;
                }
            } else {
                $jsonReturnArr['status'] = true;
            }

            if ($jsonReturnArr['status']) {
                if ($row_coupon['discount_type'] == 'rate') {
                    $jsonReturnArr['total'] = $row['total_fee'] * ($row_coupon['number'] / 100);
                } else if ($row_coupon['discount_type'] == 'cash') {
                    $jsonReturnArr['total'] = $row['total_fee'] - $row_coupon['number'];
                }

                $jsonReturnArr['discount'] = $row['total_fee'] - $jsonReturnArr['total'];

                $tmp_order_data['coupon_fee']  = $jsonReturnArr['discount'];
                $tmp_order_data['coupon_code'] = $coupon_code;
                $sql                           = mysql_install($tmp_order_data, 'order', 'edit', 'id');
                $tmp_order_data['id']          = $row['id'];
                $arraykey                      = array_keys($tmp_order_data);
                unset($parameters);
                for ($i = 0; $i < count($arraykey); $i++) {
                    $parameters[$i] = $tmp_order_data[$arraykey[$i]];
                }
                //$jsonReturnArr['sql'] = $sql;
                bind_pdo($sql, $parameters);
            }

        } else {
            $jsonReturnArr['status'] = false;
        }
        echo $json->encode($jsonReturnArr);
    } else if ($data['ajax'] == 'read_notification') {
        if (!empty($_POST["notification_ids"])) {
            read_notification($_POST["notification_ids"]);
        }

    } else if ($data['ajax'] == 'add_event') {
        if (!empty($_POST["title"]) && !empty($_POST["start"]) && !empty($_POST["end"])) {
            if ($_POST["start"] > $_POST["end"]) {
                echo "開始日期時間必須大於完結日期時間。";
            } else {
                $sql        = "insert into calendar (title, start, end, lastupdate, cmsloginid) values (?,?,?,?,?)";
                $parameters = array($_POST["title"], $_POST["start"], $_POST["end"], date("Y-m-d H:i:s"), $_SESSION["cmsloginid"]);
                bind_pdo($sql, $parameters);

                echo "success";
            }
        }
    }