<?php

define('tospur_view_house_table', 'tospur_view_house');

class dao
{
    //insert 预约看房
    public static function insert_view_house($house_id, $user_id, $date, $index, $time, $consultant_id)
    {
        $insert_view_house_array = array(
            'house_id' => $house_id,
            'user_id' => $user_id,
            'date' => $date,
            'time_index' => $index,
            'time' => $time,
            'consultant_id' => $consultant_id,
            'handle' => 0
        );
        global $wpdb;
        $result = $wpdb->insert(tospur_view_house_table, $insert_view_house_array);
        return $result;
    }

    public static function update_view_house($id)
    {
        global $wpdb;
        $result = $wpdb->update(tospur_view_house_table, array(
            'handle' => 1,
            'handle_date' => current_time('Y-m-d H:i:s')
        ), array(
            'id' => $id
        ));
        return $result;
    }

    public static function update_view_house_consultant($house_id, $consultant_id)
    {
        global $wpdb;
        $result = $wpdb->update(tospur_view_house_table, array(
            'consultant_id' => $consultant_id
        ), array(
            'id' => $house_id
        ));
        return $result;
    }

    //search 预约看房 user_id
    public static function search_view_house_by_user_id($user_id)
    {
        global $wpdb;
        $sql = "select v.house_id,v.date,v.time,v.consultant_id,v.handle from " . tospur_view_house_table . " v where user_id = " . $user_id . " order by v.date desc";
        return $wpdb->get_results($sql);
    }

    //search 预约看房 consultant_id
    public static function search_view_house_by_consultant_id($consultant_id)
    {
        global $wpdb;
        $sql = "select v.id,v.house_id,v.user_id,v.date,v.time,v.handle from " . tospur_view_house_table . " v where consultant_id = " . $consultant_id . " order by v.date desc";
        return $wpdb->get_results($sql);
    }

    //insert 置业顾问评分
    public static function insert_consultant_score($consultant_id, $user_id, $score, $valid = 1)
    {
        $insert_consultant_score_array = array(
            'consultant_id' => $consultant_id,
            'user_id' => $user_id,
            'score' => $score,
            'valid' => $valid
        );
        global $wpdb;
        $result = $wpdb->insert('tospur_consultant_score', $insert_consultant_score_array);
        return $result;
    }

    //search 置业顾问评分
    public static function search_consultant_score($consultant_id)
    {
        global $wpdb;
        $sql = "select sum(score)/count(score) as average_score from tospur_consultant_score where consultant_id = " . $consultant_id . " and valid = 1;";
        return $wpdb->get_var($sql);
    }

    public static function update_consultant_score($score_id, $valid)
    {
        global $wpdb;
        $result = $wpdb->update('tospur_consultant_score', array(
            'valid' => $valid
        ), array(
            'id' => $score_id
        ));
        return $result;
    }

    public static function search_tospur_verify($phone, $code)
    {
        global $wpdb;
        $sql = "select code from tospur_verify where phone = " . $phone . " and status = 0 " .
            "and create_time > NOW() - INTERVAL 30 MINUTE " .
            "order by create_time desc limit 1";
        return $wpdb->get_var($sql);
    }

    public static function insert_tospur_verify($phone, $code)
    {
        $insert_tospur_verify_array = array(
            'phone' => $phone,
            'code' => $code,
            'create_time' => current_time('Y-m-d H:i:s'),
            'status' => 0
        );
        global $wpdb;
        $result = $wpdb->insert('tospur_verify', $insert_tospur_verify_array);
        return $result;
    }

    public static function update_tospur_verify($phone)
    {
        global $wpdb;
        $wpdb->update('tospur_verify', array(
            'status' => 1
        ), array(
            'phone' => $phone
        ));
    }

    public static function search_consultant_by_city($city_id, $index)
    {
        global $wpdb;
        $consultant_sql = 'select c.*,u.display_name,s.average_score from tospur_consultant c ' .
            'left join wp_users u on c.id = u.ID ' .
            'left join (select consultant_id,sum(score)/count(score) as average_score from tospur_consultant_score where valid=1 group by consultant_id) s on c.id = s.consultant_id ' .
            'where cityid = ' . $city_id . ' LIMIT ' . $index . ',20;';
        return $wpdb->get_results($consultant_sql);
    }
}

?>