<?php

require_once(PLUGIN_DIR . 'Config.php');
require_once(PLUGIN_DIR . 'Tools/Image.php');

class SearchDao
{
    public static function ajax_searchCity()
    {
        wp_send_json(SearchDao::searchCity($_GET["cityId"], $_GET['districtId']));
    }

    public static function searchCity($cityId = NULL, $districtId = NULL)
    {
        global $wpdb;
        $selectName = "cityId as id,cityName as value";
        $where = " where 1=1 ";
        $groupBy = " group by cityId";
        $params = array();
        if ($cityId != NULL && $districtId == NULL) {
            $selectName = "districtId as id,districtName as value";
            $where .= " and cityId = %d";
            $params[] = $cityId;
            $groupBy = " group by districtId";
        } else if ($cityId != NULL && $districtId != NULL) {
            $selectName = "plateId as id,plateName as value";
            $where .= " and cityId = %d and districtId = %d";
            $params[] = $cityId;
            $params[] = $districtId;
            $groupBy = "";
        }
        $result = $wpdb->get_results($wpdb->prepare('select ' . $selectName . ' from ' . Config::DIC_CITY_TABLE . $where . $groupBy, $params));
        return $result;
    }

    public static function ajax_searchArea()
    {
        wp_send_json(SearchDao::searchArea($_GET['cityId']));
    }

    public static function searchArea($cityId)
    {
        global $wpdb;
        $where = " where 1=1";
        if (isset($_GET['cityId'])) {
            $where .= " and cityId = %d";
        }
        $result = $wpdb->get_results($wpdb->prepare('select id,priceValue as value from ' . Config::DIC_AREA_TABLE . $where, $cityId));
        return $result;
    }

    public static function ajax_searchBuildProperty()
    {
        wp_send_json(SearchDao::searchBuildProperty());
    }

    public static function searchBuildProperty()
    {
        global $wpdb;
        $result = $wpdb->get_results('select value as id,literal as value from ' . Config::DIC_BUILDPROPERTY_TABLE);
        return $result;
    }

    public static function ajax_searchRoom()
    {
        wp_send_json(SearchDao::searchRoom());
    }

    public static function searchRoom()
    {
        global $wpdb;
        $result = $wpdb->get_results('select value as id,literal as value from ' . Config::DIC_ROOM_TABLE);
        return $result;
    }

    public static function ajax_searchUnitPriceRange()
    {
        wp_send_json(SearchDao::searchUnitPriceRange($_GET['cityId']));
    }

    public static function searchUnitPriceRange($cityId)
    {
        global $wpdb;
        $where = " where 1=1";
        if (isset($_GET['cityId'])) {
            $where .= " and cityId = %d";
        }
        $result = $wpdb->get_results($wpdb->prepare('select id,priceValue as value from ' . Config::DIC_UNITPRICERANGE_TABLE . $where, $cityId));
        return $result;
    }

    public static function ajax_searchTotalPrice()
    {
        wp_send_json(SearchDao::searchTotalPrice($_GET['cityId']));
    }

    public static function searchTotalPrice($cityId)
    {
        global $wpdb;
        $where = " where 1=1";
        if ($cityId) {
            $where .= " and cityId = " . $cityId;
        }
        $result = $wpdb->get_results($wpdb->prepare('select id,priceValue as value from ' . Config::DIC_TOTALPRICE_TABLE . $where, $cityId));
        return $result;
    }

    public static function ajax_searchRentalPrice()
    {
        wp_send_json(SearchDao::searchRentalPrice($_GET['cityId']));
    }

    public static function searchRentalPrice($cityId)
    {
        global $wpdb;
        $where = " where 1=1";
        if ($cityId) {
            $where .= " and cityId = " . $cityId;
        }
        $result = $wpdb->get_results($wpdb->prepare('select id,priceValue as value from ' . Config::DIC_RENTALPRICE_TABLE . $where, $cityId));
        return $result;
    }

    public static function ajax_searchOrganization()
    {
        wp_send_json(SearchDao::searchOrganization($_GET['parentId']));
    }

    public static function searchOrganization($parentId = NULL)
    {
        global $wpdb;
        $where = " where 1=1 ";
        if ($parentId != NULL) {
            $where .= " and ParentId = %d";
        }
        $result = $wpdb->get_results($wpdb->prepare('select * from ' . Config::TOSPUR_ORGANIZATION_TABLE . $where, $parentId));
        return $result;
    }

    public static function getCityNameWithId($cityId)
    {
        global $wpdb;
        $result = $wpdb->get_var($wpdb->prepare("select cityName from " . Config::DIC_CITY_TABLE . " where cityId = %d", $cityId));
        if ($result) {
            return $result;
        } else {
            return "未知";
        }
    }

	public static function ajax_searchHouse()
	{
		$house_result = SearchDao::searchHouse(
			array(
				'cityId' => $_POST['cityId'],
				'districtId' => $_POST['districtId'],
				'plateId' => $_POST['plateId'],
				'buildPropertyId' => $_POST['buildPropertyId'],
				'roomId' => $_POST['roomId'],
				'acreage' => $_POST['acreage'],
				'totalPrice' => $_POST['totalPrice'],
				'rentalPrice' => $_POST['rentalPrice'],
				'searchText' => $_POST['searchText'],
				'houseType' => $_POST['houseType'],
				'feature' => $_POST['feature'],
				'userType' => $_POST['userType'],
				'userId' => $_POST['userId'],
				'index' => $_POST['index']
			)
		);
		$array = SearchDao::get_house_list($house_result);
		wp_send_json($array);
	}

	public static function searchHouse($array)
	{
		global $wpdb;
		$params = array();
		$addSql = null;
		$addWhereSql = null;
		$orderbySql = null;
		if ($array['houseType'] == 0) {
			$addSql = " left join (select buildproperty_id,house_area,image_id,house_id as bph_id from ".Config::A_DISTRICT_AREA_TABLE.") ada on th.id = ada.bph_id".
			          " left join (select value as bp_value,literal as bp_literal from ".Config::DIC_BUILDPROPERTY_TABLE.") dbp on ada.buildproperty_id = dbp.bp_value";
			$orderbySql = " group by th.id order by th.creattime DESC";
		}else if ($array['houseType'] == 1) {
			$addSql = " left join (select value as bp_value,literal as bp_literal from ".Config::DIC_BUILDPROPERTY_TABLE.") dbp on th.buildproperty_id = dbp.bp_value";
			if ($array['feature'] > -1) {
				$addSql .= " left join (select house_id,tag_id from ".Config::A_HOUSE_TAG_TABLE.") aht on th.id = aht.house_id";
				$addWhereSql = " and tag_id = ".$array['feature'];
			}
			$orderbySql = " order by th.creattime DESC";
		}else if($array['houseType'] == 2){
			$addSql = " left join (select value as bp_value,literal as bp_literal from ".Config::DIC_BUILDPROPERTY_TABLE.") dbp on th.buildproperty_id = dbp.bp_value";
			$orderbySql = " order by th.creattime DESC";
		}
		$sql = "select th.id,th.house_type,th.name,th.latest_news,th.address,th.average_price,th.community_name,th.covered_area,th.total_price,th.decoration,th.rent,".
		       "ti.path,dr.literal,th.house_number,dbp.bp_literal,th.owner_name,th.owner_phone,(SELECT GROUP_CONCAT(left(tt.name,3)) from ".Config::A_HOUSE_TAG_TABLE." aht".
		       " LEFT JOIN ".Config::TOSPUR_TAG_TABLE." tt on tt.id = aht.tag_id".
		       " where aht.house_id = th.id) as tags from ".Config::TOSPUR_HOUSE_TABLE." th".
		       $addSql.
		       " left join ".Config::DIC_CITY_TABLE." dc on th.plate_id = dc.plateId".
		       " left join (select id as rid,value as r_value,literal from ".Config::DIC_ROOM_TABLE.") dr on th.room_id = dr.r_value".
		       " left join (select id as image_id,path from ".Config::TOSPUR_IMAGE_TABLE.") ti on ti.image_id = th.frontCover_id".
		       " where 1=1 and th.status = 1 ";
		if ($array['cityId'] > -1) {
			$params[] = $array['cityId'];
			$sql = $sql . " and cityId=%d";
		}
		if ($array['districtId'] > -1) {
			$params[] = $array['districtId'];
			$sql = $sql . " and districtId=%d";
		}
		if ($array['plateId'] > -1) {
			$params[] = $array['plateId'];
			$sql = $sql . " and plateId=%d";
		}
		if ($array['buildPropertyId'] > -1) {
			$params[] = $array['buildPropertyId'];
			if ($array['houseType'] == 0) {
				$sql = $sql . " and ada.buildproperty_id=%d";
			}else{
				$sql = $sql . " and th.buildproperty_id=%d";
			}
		}
		if ($array['roomId'] > -1) {
			$params[] = $array['roomId'];
			$sql = $sql . " and room_id=%d";
		}
		if ($array['acreage'] > -1) {
			$areaArray = explode("-", $array['acreage']);
			$params[] = $areaArray[0];
			$params[] = $areaArray[1];
			if ($array['houseType'] == 0) {
				$sql = $sql . " and ada.house_area between %d and %d";
			}else{
				$sql = $sql . " and covered_area between %d and %d";
			}
		}
		if ($array['totalPrice'] > -1) {
			$priceArray = explode("-", $array['totalPrice']);
			$params[] = $priceArray[0];
			$params[] = $priceArray[1];
			$sql = $sql . " and total_price/10000 between %d and %d";
		}
		if ($array['rentalPrice'] > -1) {
			$priceArray = explode("-", $array['rentalPrice']);
			$params[] = $priceArray[0];
			$params[] = $priceArray[1];
			$sql = $sql . " and rent between %d and %d";
		}
		if ($array['houseType'] > -1) {
			$params[] = $array['houseType'];
			$sql = $sql . " and house_type = %d";
		}
		if ($array['userType'] > -1 && $array['userId'] > -1) {
			$params[] = $array['userType'];
			$params[] = $array['userId'];
			$sql = $sql . " and id in (select house_id from ".Config::A_HOUSE_USER_TABLE." where user_type = %d and user_id = %d)";
		}
		if ($array['searchText'] != null) {
			$params[] = '%'.$array['searchText'].'%';
			$params[] = '%'.$array['searchText'].'%';
			$sql = $sql . " and (name like '%s' or address like '%s')";
		}

		if($addWhereSql){
			$sql = $sql . $addWhereSql;
		}
		$sql = $sql . $orderbySql;

		if ($array['index'] > -1) {
			$params[] = $array['index'];
			$sql = $sql . " limit %d,10";
		}
		$result = $wpdb->get_results($wpdb->prepare($sql, $params));
		return $result;
	}

    public static function ajax_searchPhotoType()
    {
        wp_send_json(SearchDao::searchPhotoType());
    }

    public static function searchPhotoType()
    {
        global $wpdb;
        $result = $wpdb->get_results('select id,photo_name as value from ' . Config::DIC_PHOTOTYPE_TABLE);
        return $result;
    }
    
        public static function ajax_searchConsultant(){
        wp_send_json(SearchDao::searchConsultant($_GET["consulCityId"],$_GET["consultantName"]));
    }

    public static function searchConsultant($consulCityId = NULL,$consultantName = NULL){
        global $wpdb;
        $sql = "select tc.id,tc.name,imageUrl,tor.Name as branchName from ".Config::TOSPUR_CONSULTANT." tc
         LEFT JOIN ".Config::TOSPUR_ORGANIZATION_TABLE." tor on tc.subsidiaryId = tor.Id
         where 1=1";
        $params = array();
        if($consulCityId != NULL){
            $params[] = $consulCityId;
            $sql = $sql." and cityId=%d";
        }
        if($consultantName != NULL){
            $params[] = '%'.$consultantName.'%';
            $sql = $sql." and tc.name  like %s";
        }
        $result = $wpdb->get_results($wpdb->prepare($sql,$params));
        return $result;
    }

	public static function get_house_list($house_result)
	{
		$array = array();
		if ($house_result) {
			$array['code'] = 2000;
			$house_ids = array();
			foreach ($house_result as $key => $value) {
				$house_id = $value->id;
				$house_ids[] = $house_id;
				if($value->path){
					$value->path = home_url() . Image::getImage($value->path,'small');
				}
			}
			$array['result'] = $house_result;
		} else {
			$array['code'] = 2001;
		}
		return $array;
	}

	public static function searchFeatureByHouse($hid){
		global $wpdb;
		$featureSql ="select tag_id,name from ".Config::A_HOUSE_TAG_TABLE." aht
        LEFT JOIN (select id as tid,name ,type from ".Config::TOSPUR_TAG_TABLE.") tt on aht.tag_id = tt.tid where aht.house_id = %d and type = 1";
		$feature = $wpdb->get_results($wpdb->prepare($featureSql,$hid));
		return $feature;
	}

	public static function searchFeature()
	{
		global $wpdb;
		$sql = 'select id,name as value from '.Config::TOSPUR_TAG_TABLE.' where type = 1;';
		return $wpdb->get_results($sql);
	}

    public static function ajax_searchTagOrFeature()
    {
        wp_send_json(SearchDao::searchTagOrFeature($_POST['type']));
    }

    public static function searchTagOrFeature($type)
    {
        global $wpdb;
        $sql = 'select id,name as value from ' . Config::TOSPUR_TAG_TABLE . ' where type = ' . $type;
        return $wpdb->get_results($sql);
    }

    public static function getDetailInfo($hid,$type = NULL){
        global $wpdb;
        $sql = "select *,dr.literal as room,db.literal as buildproperty from ".Config::TOSPUR_HOUSE_TABLE." th
			left JOIN ".Config::DIC_ROOM_TABLE." dr on th.room_id = dr.id
			left JOIN ".Config::DIC_BUILDPROPERTY_TABLE." db on th.buildproperty_id = db.value
			where th.id = %d";
        $result = $wpdb->get_row($wpdb->prepare($sql,$hid));
        $context = array();
        $context['result'] = $result;
        $houseType = $result->house_type;
        $context['cityName'] = SearchDao::getCityNameWithId($result->city_id);
        $consultantLimit = 1;
        if($houseType == 0){
            $consultantLimit = 5;
            $mainImagesSql = "SELECT ti.id,ada.buildproperty_id,db.literal as type,ada.house_area as area,ti.path FROM ".Config::A_DISTRICT_AREA_TABLE." ada
            LEFT JOIN ".Config::DIC_BUILDPROPERTY_TABLE." db on db.value = ada.buildproperty_id
            LEFT JOIN ".Config::TOSPUR_IMAGE_TABLE." ti on ti.id = ada.image_id
            where ada.house_id = %d";
            $mainImage = $wpdb->get_results($wpdb->prepare($mainImagesSql,$hid));

            foreach($mainImage as $key => $value){
                $value->path = Image::getImage($value->path,'big');
            }
            $context['mainImage'] = $mainImage;

	        $recommendSql = "select th.id,th.name,th.average_price,th.latest_news,th.address,ti.path,dr.literal,(SELECT GROUP_CONCAT(left(tt.name,3)) from ".Config::A_HOUSE_TAG_TABLE." aht
			LEFT JOIN ".Config::TOSPUR_TAG_TABLE." tt on tt.id = aht.tag_id
			where aht.house_id = th.id) as tags from ".Config::TOSPUR_HOUSE_TABLE." th
			LEFT JOIN ".Config::TOSPUR_IMAGE_TABLE." ti on th.frontCover_id = ti.id
			LEFT JOIN ".Config::DIC_ROOM_TABLE." dr on dr.value = th.room_id
			where th.id in(select recommend_id from ".Config::A_HOUSE_RECOMMEND_TABLE." where house_id = %d);";
        }else{
	        $recommendSql = "select th.id,th.name,th.community_name,ti.path,dr.value,th.rent,th.decoration,th.covered_area,th.total_price,(SELECT GROUP_CONCAT(left(tt.name,3)) from ".Config::A_HOUSE_TAG_TABLE." aht
			LEFT JOIN ".Config::TOSPUR_TAG_TABLE." tt on tt.id = aht.tag_id
			where aht.house_id = th.id) as tags from ".Config::TOSPUR_HOUSE_TABLE." th
			LEFT JOIN ".Config::DIC_ROOM_TABLE." dr on dr.id = th.room_id
			LEFT JOIN ".Config::TOSPUR_IMAGE_TABLE." ti on th.frontCover_id = ti.id
			where th.id in(select recommend_id from ".Config::A_HOUSE_RECOMMEND_TABLE." where house_id = %d);";
        }

        $recommends = $wpdb->get_results($wpdb->prepare($recommendSql,$hid));
        foreach($recommends as $key => $value){
            $value->path = Image::getImage($value->path,'small');
        }
        $context['recommends'] = $recommends;

        $consultantSql = "select id,name,mobile,imageUrl,s.average_score from ".Config::TOSPUR_CONSULTANT." c
            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 id in(select user_id from ".Config::A_HOUSE_USER_TABLE." where user_type = 1 and house_id = %d) limit {$consultantLimit};";
        $consultant = $wpdb->get_results($wpdb->prepare($consultantSql,$hid));
        $context['consultant'] = $consultant;

        if($type == 'edit'){
            $imagesSql = 'select * from a_house_image ahi
            LEFT JOIN tospur_image ti on ti.id = ahi.image_id
            where ahi.house_id = %d and ahi.image_id not in (select image_id from '.Config::A_DISTRICT_AREA_TABLE.' where house_id = %d);';
        }else{
            $imagesSql = 'select * from '.Config::TOSPUR_IMAGE_TABLE.' ti
            LEFT JOIN '.Config::A_HOUSE_IMAGE_TABLE.' ahi on ti.id = ahi.image_id
            WHERE ahi.house_id = %d order by image_type;';

        }
        $images = $wpdb->get_results($wpdb->prepare($imagesSql,$hid,$hid));
        foreach($images as $key => $value){
            $value->path = Image::getImage($value->path,'big');
        }
        $context['images'] = $images;


        return $context;
    }

    public static function searchHouseTag($houseId){
        global $wpdb;
        $sql = "select tt.name from ".Config::A_HOUSE_TAG_TABLE." aht
        LEFT JOIN ".Config::TOSPUR_TAG_TABLE." tt on aht.tag_id = tt.id
        where aht.house_id = %d and type = 0;";
        $results = $wpdb->get_results($wpdb->prepare($sql,$houseId));
        $tags = array();
        foreach($results as $item){
            $tags[] =$item->name;
        }
        return implode(",",$tags);
    }

    public static function searchStatusType($statusType){
        global $wpdb;
	    $sql="select status_id as id,status_name as value from ".Config::TOSPUR_STATUS_TABLE." where status_type = %d";
        $results = $wpdb->get_results($wpdb->prepare($sql,$statusType));
        return $results;

    }


    public static function searchFeatureOld($hid, $type){
        global $wpdb;
        $params[] = $hid;
        $params[] = $type;
        $featureSql ="select tag_id,name from a_house_tag aht
        LEFT JOIN (select id as tid,name ,type from ".Config::TOSPUR_TAG_TABLE.") tt on aht.tag_id = tt.tid where aht.house_id = %d and tt.type = %d";
        $feature = $wpdb->get_results($wpdb->prepare($featureSql,$params));
        return $feature;
    }
}