<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="cn.com.countrygarden.bi.main.dao.mediaResource.ScheduleBasicInfoMapper">

	<select id="selectBasicInfo" resultType="map">
		select * from (
			select 
			comm_info_id as commInfoId,
			basics_id as basicsId,
			comm_id as commId,
			organ_code as organCode,
			organ_name as organName,
			comm_name as commName,
			develop_unit_name as developUnitName,
			comm_address as commAddress,
			province,
			city,
			county,
			DATE_FORMAT(actual_property,'%Y-%m-%d') as actualProperty,
			plan_number as planNumber,
			take_number as takeNumber,
			IFNULL(concat(0+CAST((occupancy_rate*100) AS CHAR),'%'),'') as occupancyRate,
			up_car_park as upCarPark,
			down_car_park as downCarPark,
			elevator_door_number as elevatorDoorNumber,
			elevator_room_number as elevatorRoomNumber,
			elevator_amount as elevatorAmount,
			stairs_room_number as stairsRoomNumber,
			stairs_door_number as stairsDoorNumber,
			villa_amount as villaAmount,
			DATE_FORMAT(planned_open,'%Y-%m-%d') as plannedOpen,                   		
			DATE_FORMAT(actual_open,'%Y-%m-%d') as actualOpen,				
			DATE_FORMAT(contract_property,'%Y-%m-%d') as contractProperty,			
			property_name					as			  propertyName,
			development_subject_names		as				  developmentSubjectNames,
			property_form					as			  propertyForm,
			development_state				as			  developmentState,
			takeover_state					as			  takeoverState,	
			DATE_FORMAT(actual_enter_time,'%Y-%m-%d')	as	 actualEnterTime,
			state_property					as			  stateProperty,				
			total_residential_area			as			  totalResidentialArea,
			reality_area as realityArea,
			take_room_number as takeRoomNumber,
			often_number as oftenNumber,
			passenger_elevator_number as passengerElevatorNumber,
			freight_elevator_number as freightElevatorNumber,
			apartment as apartment
			from t_media_comm_info
			where is_delete = 0 
			<if test="organCode!=null and organCode!=''">
				and organ_code = #{organCode}
			</if>
			<if test="commId!=null and commId!=''">
				and comm_id like CONCAT('%',#{commId},'%')
			</if>
			<if test="organName!=null and organName!=''">
				and organ_name like CONCAT('%',#{organName},'%')
			</if>
			<if test="commName!=null and commName!=''">
				and comm_name like CONCAT('%',#{commName},'%')
			</if>
			<if test="developUnitName!=null and developUnitName!=''">
				and develop_unit_name = #{developUnitName}
			</if>
			<if test="actualProperty!=null and actualProperty!=''">
				and actual_property = #{actualProperty}
			</if>
			<if test="province!=null and province!=''">
				and province like CONCAT('%',#{province},'%')
			</if>
			<if test="city!=null and city!=''">
				and city like CONCAT('%',#{city},'%')
			</if>
			<if test="county!=null and county!=''">
				and county like CONCAT('%',#{county},'%')
			</if>
			<if test="occupancyRate!=null and occupancyRate!=''">
				and occupancy_rate >= #{occupancyRate}
			</if>
			<if test="planNumber!=null and planNumber!=''">
				and plan_number >= #{planNumber}
			</if>
			<if test="takeNumber!=null and takeNumber!=''">
				and take_number >= #{takeNumber}
			</if>
			order by organ_name,province,city,county asc 
		) t 
		where 1=1 
		<if test="organSearch!=null and organSearch!=''">
			and organCode like CONCAT('%',#{organSearch},'%')		
		</if>
		limit #{pageOffset}, #{pageSize}
		
	</select>
	
	<select id="selectBasicInfoCount" resultType="long">
		select count(comm_info_id) as count
		from t_media_comm_info where is_delete = 0 
		<if test="organCode!=null and organCode!=''">
			and organ_code = #{organCode}
		</if>
		<if test="commId!=null and commId!=''">
			and comm_id like CONCAT('%',#{commId},'%')
		</if>
		<if test="organName!=null and organName!=''">
			and organ_name like CONCAT('%',#{organName},'%')
		</if>
		<if test="commName!=null and commName!=''">
			and comm_name like CONCAT('%',#{commName},'%')
		</if>
		<if test="developUnitName!=null and developUnitName!=''">
			and develop_unit_name = #{developUnitName}
		</if>
		<if test="actualProperty!=null and actualProperty!=''">
			and actual_property = #{actualProperty}
		</if>
		<if test="province!=null and province!=''">
			and province like CONCAT('%',#{province},'%')
		</if>
		<if test="city!=null and city!=''">
			and city like CONCAT('%',#{city},'%')
		</if>
		<if test="county!=null and county!=''">
			and county like CONCAT('%',#{county},'%')
		</if>
		<if test="occupancyRate!=null and occupancyRate!=''">
			and occupancy_rate >= #{occupancyRate}
		</if>
		<if test="planNumber!=null and planNumber!=''">
			and plan_number >= #{planNumber}
		</if>
		<if test="takeNumber!=null and takeNumber!=''">
			and take_number >= #{takeNumber}
		</if>
		<if test="organSearch!=null and organSearch!=''">
			and organ_code like CONCAT('%',#{organSearch},'%')		
		</if>
		
	</select>
	
	<select id="findBasic" resultType="map">
		select 
		organ_code as organCode,
		organ_name as organName,
		comm_name as commName,
		comm_id as commId,
		comm_address as commAddress,
		develop_unit_name as developUnitName,
		province,
		city,
		county,
		DATE_FORMAT(actual_property,'%Y-%m-%d') as actualProperty,
		plan_number as planNumber,
		take_number as takeNumber,
		concat(0+CAST((occupancy_rate*100) AS CHAR),'%') as occupancyRate,
		up_car_park as upCarPark,
		down_car_park as downCarPark,
		elevator_door_number as elevatorDoorNumber,
		elevator_room_number as elevatorRoomNumber,
		elevator_amount as elevatorAmount,
		stairs_room_number as stairsRoomNumber,
		stairs_door_number as stairsDoorNumber,
		villa_amount as villaAmount,
		DATE_FORMAT(planned_open,'%Y-%m-%d') as plannedOpen,                   		
		DATE_FORMAT(actual_open,'%Y-%m-%d') as actualOpen,				
		DATE_FORMAT(contract_property,'%Y-%m-%d') as contractProperty,			
		property_name					as			  propertyName,
		development_subject_names		as				  developmentSubjectNames,
		property_form					as			  propertyForm,
		development_state				as			  developmentState,
		takeover_state					as			  takeoverState,	
		DATE_FORMAT(actual_enter_time,'%Y-%m-%d')	as	 actualEnterTime,
		state_property					as			  stateProperty,				
		total_residential_area			as			  totalResidentialArea,
		reality_area as realityArea,
		take_room_number as takeRoomNumber,
		often_number as oftenNumber,
		passenger_elevator_number as passengerElevatorNumber,
		freight_elevator_number as freightElevatorNumber,
		apartment as apartment 
		from t_media_comm_info where is_delete = 0 
		<if test="organCode!=null and organCode!=''">
			and organ_code = #{organCode}
		</if>
		<if test="commId!=null and commId!=''">
			and comm_id like CONCAT('%',#{commId},'%')
		</if>
		<if test="organName!=null and organName!=''">
			and organ_name like CONCAT('%',#{organName},'%')
		</if>
		<if test="commName!=null and commName!=''">
			and comm_name like CONCAT('%',#{commName},'%')
		</if>
		<if test="developUnitName!=null and developUnitName!=''">
			and develop_unit_name = #{developUnitName}
		</if>
		<if test="actualProperty!=null and actualProperty!=''">
			and actual_property = #{actualProperty}
		</if>
		<if test="province!=null and province!=''">
			and province like CONCAT('%',#{province},'%')
		</if>
		<if test="city!=null and city!=''">
			and city like CONCAT('%',#{city},'%')
		</if>
		<if test="county!=null and county!=''">
			and county like CONCAT('%',#{county},'%')
		</if>
		<if test="occupancyRate!=null and occupancyRate!=''">
			and occupancy_rate >= #{occupancyRate}
		</if>
		<if test="planNumber!=null and planNumber!=''">
			and plan_number >= #{planNumber}
		</if>
		<if test="takeNumber!=null and takeNumber!=''">
			and take_number >= #{takeNumber}
		</if>
		<if test="organSearch!=null and organSearch!=''">
			and organ_code like CONCAT('%',#{organSearch},'%')		
		</if>
		order by organ_name,province,city,county asc 
	</select>
	
	<select id="verify" resultType="string">
		select 
		concat(case when organ_name = #{organName} then '' else '物业区域不存在，' end,
		case when comm_name = #{commName} then '' else '物业项目不存在，' end) 
		from t_media_comm_info 
		where 
		organ_code = #{organCode} 
		and comm_id = #{commId}
	</select>
	
	<select id="finddbBasicInfo" resultType="cn.com.countrygarden.bi.main.model.mediaResource.BasicInfo">
		select 
		comm_info_id as commInfoId,
		basics_id as basicsId,
		version 
		from t_media_comm_info 
		where 1=1 
		and ifnull(is_delete,0) = 0
	</select>
	
	<update id="updateMatchingBasic">
		UPDATE t_media_comm_info
		SET 
		 basics_id = #{basicsId},
		 organ_code = #{organCode},
		 organ_name = #{organName},
		 comm_id = #{commId},
		 comm_name = #{commName},
		 comm_address = #{commAddress},
		 develop_unit_name = #{developUnitName},
		 province = #{province},
		 city = #{city},
		 county = #{county},
		 actual_property = #{actualProperty},
		 plan_number = #{planNumber},
		 take_number = #{takeNumber},
		 occupancy_rate = #{occupancyRate},
		 version = #{version}
		WHERE comm_info_id = #{commInfoId}
	</update>
	
	<select id="verify2" resultType="string">
		select 
		concat(case when organ_name = #{organName} then '' else '物业区域不存在，' end,
		case when comm_name = #{commName} then '' else '物业项目不存在，' end) 
		from t_media_comm_info 
		where comm_id = #{commId} and ifnull(is_delete,0) = 0
		limit 1 
	</select>
	
	<update id="updateOne">
		UPDATE t_media_comm_info
		SET 
		 up_car_park = #{upCarPark},
		 down_car_park = #{downCarPark},
		 elevator_door_number = #{elevatorDoorNumber},
		 elevator_room_number = #{elevatorRoomNumber},
		 elevator_amount = #{elevatorAmount},
		 stairs_room_number = #{stairsRoomNumber},
		 stairs_door_number = #{stairsDoorNumber},
		 villa_amount = #{villaAmount},
		 passenger_elevator_number = #{passengerElevatorNumber},
		 freight_elevator_number = #{freightElevatorNumber},
		 apartment = #{apartment},
		 version = (version+1),
		 mod_time = #{modTime},
		 mod_by = #{modBy},
		 mod_by_name = #{modByName}
		WHERE
			comm_id = #{commId};
	</update>
	
	<update id="saveOne">
		UPDATE t_media_comm_info
		SET 
		 up_car_park = #{upCarPark},
		 down_car_park = #{downCarPark},
		 elevator_door_number = #{elevatorDoorNumber},
		 elevator_room_number = #{elevatorRoomNumber},
		 elevator_amount = #{elevatorAmount},
		 stairs_room_number = #{stairsRoomNumber},
		 stairs_door_number = #{stairsDoorNumber},
		 villa_amount = #{villaAmount},
		 passenger_elevator_number = #{passengerElevatorNumber},
		 freight_elevator_number = #{freightElevatorNumber},
		 apartment = #{apartment},
		 version = #{version},
		 mod_time = #{modTime},
		 mod_by = #{modBy},
		 mod_by_name = #{modByName}
		WHERE
			comm_info_id = #{commInfoId};
	</update>
	
	<select id="selectPropertyAreaData" resultType="String">
		select organ_name as organName from t_media_comm_info where is_delete = 0 
		<if test="organCode!=null and organCode!=''">
			and organ_code = #{organCode}
		</if>
		group by organ_name
	</select>
	
	<select id="selectPropertyData" resultType="String">
		select organ_name as organName from t_media_comm_info where is_delete = 0 
		<if test="organCode!=null and organCode!=''">
			and organ_code = #{organCode}
		</if>
		<!-- <if test="organList.size() > 0">
		    and organ_code in 
			<foreach collection="organList" item="item" index="index" open="(" close=")" separator=",">
            	#{item}
       		</foreach>
		</if> -->
		group by organ_name
	</select>
	
	<select id="findOne" resultType="map">
		select 
			comm_info_id as commInfoId,
			basics_id as basicsId,
			comm_id as commId,
			organ_code as organCode,
			organ_name as organName,
			comm_name as commName,
			develop_unit_name as developUnitName,
			comm_address as commAddress,
			province,
			city,
			county,
			DATE_FORMAT(actual_property,'%Y-%m-%d') as actualProperty,
			plan_number as planNumber,
			take_number as takeNumber,
			IFNULL(concat(0+CAST(occupancy_rate AS CHAR),'%'),'') as occupancyRate,
			up_car_park as upCarPark,
			down_car_park as downCarPark,
			elevator_door_number as elevatorDoorNumber,
			elevator_room_number as elevatorRoomNumber,
			elevator_amount as elevatorAmount,
			stairs_room_number as stairsRoomNumber,
			stairs_door_number as stairsDoorNumber,
			villa_amount as villaAmount,
			DATE_FORMAT(planned_open,'%Y-%m-%d') as plannedOpen,                   		
			DATE_FORMAT(actual_open,'%Y-%m-%d') as actualOpen,				
			DATE_FORMAT(contract_property,'%Y-%m-%d') as contractProperty,			
			property_name					as			  propertyName,
			development_subject_names		as				  developmentSubjectNames,
			property_form					as			  propertyForm,
			development_state				as			  developmentState,
			takeover_state					as			  takeoverState,	
			DATE_FORMAT(actual_enter_time,'%Y-%m-%d')	as	 actualEnterTime,
			state_property					as			  stateProperty,				
			total_residential_area			as			  totalResidentialArea,
			reality_area as realityArea,
			take_room_number as takeRoomNumber,
			often_number as oftenNumber,
			passenger_elevator_number as passengerElevatorNumber,
			freight_elevator_number as freightElevatorNumber,
			apartment as apartment,
			version
			from t_media_comm_info
			where is_delete = 0 and comm_info_id = #{commInfoId}
	</select>
	
</mapper>