<?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.project.sys_client_visit.SysClientVisitMapper">

	<!-- 增 -->
	<insert id="add">
		insert into 
		sys_client_visit (id, client_id, user_id, visit, create_time) 
		values (0, #{clientId}, #{userId}, #{visit}, now()) 
	</insert>

	<!-- 删 -->
	<delete id="delete">
		delete from sys_client_visit 
		where id = #{id}
	</delete>

	<!-- 删 - 根据应用id和用户id  -->
	<delete id="deleteByClientAndUserId">
		delete from sys_client_visit 
		where client_id = #{clientId}
		and user_id = #{userId}
	</delete>
	
	<!-- 改 -->
	<update id="update">
		update sys_client_visit set
		client_id = #{clientId}, 
		user_id = #{userId}, 
		visit = #{visit}
		where id = #{id}
	</update>


	<!-- ================================== 查询相关 ================================== -->
	<!-- select id, client_id, user_id, visit, create_time from sys_client_visit  -->
	
	<!-- 通用映射：手动模式 -->
	<resultMap id="model" type="com.pj.project.sys_client_visit.SysClientVisit">
		<result property="id" column="id" />
		<result property="clientId" column="client_id" />
		<result property="userId" column="user_id" />
		<result property="visit" column="visit" />
		<result property="createTime" column="create_time" />
		<result property="sysClientName" column="sys_client_name" />
		<result property="sysClientLogo" column="sys_client_logo" />
		<result property="sysUserUsername" column="sys_user_username" />
		<result property="sysUserAvatar" column="sys_user_avatar" />
	</resultMap>
	
	<!-- 公共查询sql片段 -->
	<sql id="select_sql">
		select cv.*, 
			sys_client.name as sys_client_name, 
			sys_client.logo as sys_client_logo, 
			sys_user.username as sys_user_username, 
			sys_user.avatar as sys_user_avatar 
		from sys_client_visit cv
			left join sys_client on client_id = sys_client.id 
			left join sys_user on user_id = sys_user.id 
	</sql>
	<sql id="select_sql2">
		select * from sys_client_visit 
	</sql>
	
	
	<!-- 查 - 根据id -->
	<select id="getById" resultMap="model">
		<include refid="select_sql"></include>
		where cv.id = #{id}
	</select>
	
	<!-- 查 - 根据id(不带有应用信息和用户信息) -->
	<select id="getById2" resultMap="model">
		<include refid="select_sql2"></include>
		where id = #{id}
	</select>
	
	<!-- 查 - 根据应用id和用户id  -->
	<select id="getByClientAndUserId" resultMap="model">
		<include refid="select_sql2"></include>
		where client_id = #{clientId}
		and user_id = #{userId}
		limit 1
	</select>
	
	<!-- 查集合 - 根据条件（参数为空时代表忽略指定条件） -->
	<select id="getList" resultMap="model">
		<include refid="select_sql"></include>
		<where>
			<if test=' this.has("id") '> and cv.id = #{id} </if>
			<if test=' this.has("clientId") '> and cv.client_id = #{clientId} </if>
			<if test=' this.has("userId") '> and cv.user_id = #{userId} </if>
			<if test=' this.has("visit") '> and cv.visit = #{visit} </if>
		</where>
		order by id desc
	</select>
	
</mapper>
