<?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="com.pj.tb_people.TbPeopleMapper">




	<!-- ================================== 查询相关 ================================== -->
	<!-- select id, name, code, sex, age, id_card, id_card_img, phone, bank_no, bank_code, bank_name, group_id, group_name, status, role, lng, lat, last_location, address, address_ids, detail_address, is_lock, left_price, judge_status, judge_content, register_time, judge_time, person_id, create_time, create_by, create_name, update_time, update_by, update_name, delete_status from tb_people  -->

	<!-- 通用映射：自动模式 -->
	<resultMap id="model" autoMapping="true" type="com.pj.tb_people.TbPeople">

	</resultMap>
	<sql id="select_tbPeople">
		select p.id, p.name, p.code, p.sex, p.age, p.id_card, p.id_card_img, p.phone, p.bank_no, p.bank_code, p.bank_name,
			   p.group_id, p.group_name, p.status, p.role, p.lng, p.lat, p.last_location, p.address, p.address_ids,
			   p.detail_address, p.is_lock, p.left_price, p.judge_status, p.judge_content, p.register_time, p.judge_time,
			   p.person_id, p.create_time, p.create_by, p.create_name, p.update_time, p.update_by, p.update_name,
			   p.delete_status, p.trade_area_id, p.trade_area_name
		from  tb_people p
	</sql>


	<!-- 公共查询sql片段 -->
	<sql id="select_sql">
		select * from tb_people
	</sql>
	<insert id="addApply">
		insert into tb_people_apply
		(people_id,group_id,status)
		values
		(
		<if test="peopleId">#{peopleId},</if>
		<if test="groupId"> #{groupId},</if>
		 0
		)
	</insert>

	<update id="updateApplyStatus" >
		update  tb_people_apply
		set
		<if test="status !=  null and status != '' ">status=#{status}</if>
		<where>
			<if test="peopleId != null and peopleId != ''"> people_id=#{peopleId}</if>
			and status=0
		</where>

	</update>

	<select id="checkApplyExist" resultType="Integer">
		select  count(1) from tb_people_apply where people_id=#{peopleId} and status=0
	</select>

	<select id="getApplyList"  resultType="com.pj.tb_people.TbPeople">
		<include refid="select_tbPeople"></include>
		 right join
			 tb_people_apply ap
			 on p.id = ap.people_id
		where ap.group_id = (select id from tb_group g where g.leader_id =#{leaderId})and ap.status=0
	</select>

	<select id="getMemberListByGroupId" resultType="com.pj.tb_people.TbPeople">
		<include refid="select_sql" ></include>
				<where>
					<if test="groupId != null and groupId != ''">group_id =#{groupId}</if>
				</where>
	</select>

	<select id="getMemberListByLeaderId" resultType="com.pj.tb_people.TbPeople">
		<include refid="select_sql" ></include>
		<where>
			<if test="leaderId != null and leaderId != ''">
				group_id = (SELECT id FROM tb_group WHERE leader_id = #{leaderId})
			</if>
		</where>
	</select>


	<select id="getLeftPeople" resultType="com.pj.tb_people.TbPeople">
		select * from tb_people where group_id is null and (group_name is null or group_name='')
	</select>

	<!-- 查集合 - 根据条件（参数为空时代表忽略指定条件） [G] -->
	<select id="getList" resultMap="model">
		<include refid="select_sql"></include>
		<where>
			<if test=' this.has("id") '> and id = #{id} </if>
			<if test=' this.has("name") '> and name = #{name} </if>
			<if test=' this.has("code") '> and code = #{code} </if>
			<if test=' this.has("sex") '> and sex = #{sex} </if>
			<if test=' this.has("age") '> and age = #{age} </if>
			<if test=' this.has("idCard") '> and id_card = #{idCard} </if>
			<if test=' this.has("phone") '> and phone = #{phone} </if>
			<if test=' this.has("bankNo") '> and bank_no = #{bankNo} </if>
			<if test=' this.has("bankCode") '> and bank_code = #{bankCode} </if>
			<if test=' this.has("bankName") '> and bank_name = #{bankName} </if>
			<if test=' this.has("groupId") '> and group_id = #{groupId} </if>
			<if test=' this.has("groupName") '> and group_name = #{groupName} </if>
			<if test=' this.has("status") '> and status = #{status} </if>
			<if test=' this.has("role") '> and role = #{role} </if>
			<if test=' this.has("lng") '> and lng = #{lng} </if>
			<if test=' this.has("lat") '> and lat = #{lat} </if>
			<if test=' this.has("lastLocation") '> and last_location = #{lastLocation} </if>
			<if test=' this.has("address") '> and address = #{address} </if>
			<if test=' this.has("addressIds") '> and address_ids = #{addressIds} </if>
			<if test=' this.has("detailAddress") '> and detail_address = #{detailAddress} </if>
			<if test=' this.has("isLock") '> and is_lock = #{isLock} </if>
			<if test=' this.has("leftPrice") '> and left_price = #{leftPrice} </if>
			<if test=' this.has("judgeStatus") '> and judge_status = #{judgeStatus} </if>
			<if test=' this.has("judgeContent") '> and judge_content = #{judgeContent} </if>
			<if test=' this.has("registerTime") '> and register_time = #{registerTime} </if>
			<if test=' this.has("judgeTime") '> and judge_time = #{judgeTime} </if>
			<if test=' this.has("personId") '> and person_id = #{personId} </if>
			<if test=' this.has("createTime") '> and create_time = #{createTime} </if>
			<if test=' this.has("createBy") '> and create_by = #{createBy} </if>
			<if test=' this.has("createName") '> and create_name = #{createName} </if>
			<if test=' this.has("updateTime") '> and update_time = #{updateTime} </if>
			<if test=' this.has("updateBy") '> and update_by = #{updateBy} </if>
			<if test=' this.has("updateName") '> and update_name = #{updateName} </if>
			<if test=' this.has("deleteStatus") '> and delete_status = #{deleteStatus} </if>
			<if test=' this.has("tradeAreaId") '> and trade_area_id = #{tradeAreaId} </if>
			<if test=' this.has("tradeAreaName") '> and trade_area_name = #{tradeAreaName} </if>
		</where>
		order by
		<choose>
			<when test='sortType == 1'> id desc </when>
			<when test='sortType == 2'> name desc </when>
			<when test='sortType == 3'> code desc </when>
			<when test='sortType == 4'> sex desc </when>
			<when test='sortType == 5'> age desc </when>
			<when test='sortType == 6'> id_card desc </when>
			<when test='sortType == 7'> phone desc </when>
			<when test='sortType == 8'> bank_no desc </when>
			<when test='sortType == 9'> bank_code desc </when>
			<when test='sortType == 10'> bank_name desc </when>
			<when test='sortType == 11'> group_id desc </when>
			<when test='sortType == 12'> group_name desc </when>
			<when test='sortType == 13'> status desc </when>
			<when test='sortType == 14'> role desc </when>
			<when test='sortType == 15'> lng desc </when>
			<when test='sortType == 16'> lat desc </when>
			<when test='sortType == 17'> last_location desc </when>
			<when test='sortType == 18'> address desc </when>
			<when test='sortType == 19'> address_ids desc </when>
			<when test='sortType == 20'> detail_address desc </when>
			<when test='sortType == 21'> is_lock desc </when>
			<when test='sortType == 22'> left_price desc </when>
			<when test='sortType == 23'> judge_status desc </when>
			<when test='sortType == 24'> judge_content desc </when>
			<when test='sortType == 25'> register_time desc </when>
			<when test='sortType == 26'> judge_time desc </when>
			<when test='sortType == 27'> person_id desc </when>
			<when test='sortType == 28'> create_time desc </when>
			<when test='sortType == 29'> create_by desc </when>
			<when test='sortType == 30'> create_name desc </when>
			<when test='sortType == 31'> update_time desc </when>
			<when test='sortType == 32'> update_by desc </when>
			<when test='sortType == 33'> update_name desc </when>
			<when test='sortType == 34'> delete_status desc </when>
			<otherwise> id desc </otherwise>
		</choose>
	</select>



</mapper>
