<?php

require_once(PLUGIN_DIR . 'Config.php');

class TospurDao
{
    //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(Config::TOSPUR_VIEW_HOUSE_TABLE, $insert_view_house_array);
        return $result;
    }

    public static function update_view_house($id)
    {
        global $wpdb;
        $result = $wpdb->update(Config::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(Config::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,h.name from ' . Config::TOSPUR_VIEW_HOUSE_TABLE . ' v ' .
            'left join ' . Config::TOSPUR_HOUSE_TABLE . ' h on v.house_id = h.id ' .
            'where v.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.date,v.time,v.user_id,v.handle,h.name from ' . Config::TOSPUR_VIEW_HOUSE_TABLE . ' v ' .
            'left join ' . Config::TOSPUR_HOUSE_TABLE . ' h on v.house_id = h.id ' .
            'where v.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(Config::TOSPUR_CONSULTANT_SCORE_TABLE, $insert_consultant_score_array);
        return $result;
    }

    public static function search_score_type($user_id, $consultant_id)
    {
        global $wpdb;
        $sql = 'select type from (SELECT user_id,consultant_id,handle_date,1 as type FROM ' . Config::TOSPUR_VIEW_HOUSE_TABLE .
            ' where handle = 1 and user_id = ' . $user_id . ' and consultant_id = ' . $consultant_id . ' UNION' .
            ' SELECT user_id,consultant_id,handle_date,2 as type FROM ' . Config::TOSPUR_SALE_TABLE .
            ' where handle = 1 and user_id = ' . $user_id . ' and consultant_id = ' . $consultant_id . ') as u' .
            ' order by handle_date desc limit 1';
        return $wpdb->get_var($sql);
    }

    //search 置业顾问信息
    public static function search_consultant_info($consultant_id)
    {
        global $wpdb;
        $sql = 'select c.*,u.display_name,s.average_score from ' . Config::TOSPUR_CONSULTANT . ' c ' .
            'left join ' . Config::WP_USERS_TABLE . ' u on c.id = u.ID ' .
            'left join (select consultant_id,sum(score)/count(score) as average_score from ' . Config::TOSPUR_CONSULTANT_SCORE_TABLE . ' group by consultant_id) s ' .
            'on c.id = s.consultant_id where c.id = ' . $consultant_id;
        return $wpdb->get_row($sql);
    }

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

    public static function search_tospur_verify($phone, $code)
    {
        global $wpdb;
        $sql = 'select code from ' . Config::TOSPUR_VERIFY_TABLE . ' 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(Config::TOSPUR_VERIFY_TABLE, $insert_tospur_verify_array);
        return $result;
    }

    public static function update_tospur_verify($phone)
    {
        global $wpdb;
        $wpdb->update(Config::TOSPUR_VERIFY_TABLE, 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 ' . Config::TOSPUR_CONSULTANT . ' c ' .
            'left join ' . Config::WP_USERS_TABLE . ' u on c.id = u.ID ' .
            'left join (select consultant_id,sum(score)/count(score) as average_score from ' . Config::TOSPUR_CONSULTANT_SCORE_TABLE . ' 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);
    }

    public static function insert_tospur_sale($post)
    {
        $post['submission_date'] = current_time('Y-m-d H:i:s');
        unset($post['action']);
        global $wpdb;
        $result = $wpdb->insert(Config::TOSPUR_SALE_TABLE, $post);
        return $result;
    }
}

?>