<?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.cftech.accounts.dao.MpAccountsMapper">
	<resultMap id="BaseResultMap" type="com.cftech.accounts.model.MpAccountsEntity">
		<id column="id" property="id" jdbcType="BIGINT" />
		<result column="description" property="description" jdbcType="VARCHAR" />
		<result column="appid" property="appid" jdbcType="VARCHAR" />
		<result column="secret" property="secret" jdbcType="CHAR" />
		<result column="token" property="token" jdbcType="VARCHAR" />
		<result column="jsapi_ticket" property="jsapi_ticket" jdbcType="VARCHAR" />
		<result column="encodingaeskey" property="encodingaeskey" jdbcType="VARCHAR" />
		<result column="wctype" property="wctype" jdbcType="VARCHAR" />
		<result column="createtime" property="createtime" jdbcType="TIMESTAMP" />
		<result column="updatetime" property="updatetime" jdbcType="TIMESTAMP" />
		<result column="upload_type" property="uploadType" jdbcType="VARCHAR"/>
		<result column="agentid" property="agentId" jdbcType="VARCHAR"/>
	</resultMap>

	<sql id="Base_Column_List">
		id, description, appid, secret,
		token,encodingaeskey,createtime,updatetime,delflag,accesstoken,jsapi_ticket,wctype,upload_type, agentid
	</sql>

	<!-- 插入用户服务号 -->
	<insert id="insert" parameterType="com.cftech.accounts.model.MpAccountsEntity">
		insert into wx_mp_accounts ( appid, secret,
		encodingaeskey,token,accesstoken,delflag,
		createtime,timestamp,updatetime,description, wctype, agentid)
		values (#{appid,jdbcType=VARCHAR}, #{secret,jdbcType=VARCHAR},
		#{encodingaeskey,jdbcType=VARCHAR},#{token,jdbcType=VARCHAR},
		#{accesstoken,jdbcType=VARCHAR},
		#{delflag,jdbcType=INTEGER},
		#{createtime,jdbcType=TIMESTAMP},
		#{timestamp,jdbcType=BIGINT},
		#{updatetime,jdbcType=TIMESTAMP},
		#{description,jdbcType=VARCHAR},
		#{wctype,jdbcType=VARCHAR},#{agentId,jdbcType=VARCHAR}),
	</insert>

	<!-- 关联表插入 -->
	<insert id="insertUserAccount" parameterType="java.util.Map">
		insert into user_accounts values (id,#{userid},#{accountsid},0);
	</insert>

	<select id="getMpAccounts" resultMap="BaseResultMap"
		parameterType="java.lang.Long">
		select
		accounts.id, accounts.description, accounts.appid, accounts.secret,
		accounts.token,accounts.encodingaeskey,accounts.createtime,accounts.delflag,accounts.accesstoken,wctype, agentid
		from wx_mp_accounts accounts
		left join user_role user on user.accounts_id=accounts.id
		where user.user_id = #{userid,jdbcType=BIGINT} and accounts.delflag=0 and accounts.wctype='0'
		group by accounts.id
		order
		by accounts.createtime desc
	</select>

	<select id="getQyAccounts" resultMap="BaseResultMap"
			parameterType="java.lang.Long">
		select
		accounts.id, accounts.description, accounts.appid, accounts.secret,
		accounts.token,accounts.encodingaeskey,accounts.createtime,accounts.delflag,accounts.accesstoken,wctype, agentid
		from wx_mp_accounts accounts
		left join user_role user on user.accounts_id=accounts.id
		where user.user_id = #{userid,jdbcType=BIGINT} and accounts.delflag=0 and accounts.wctype='1'
		group by accounts.id
		order
		by accounts.createtime desc
	</select>

	<select id="getAllAccounts" resultMap="BaseResultMap"
			parameterType="java.lang.Long">
		select
		accounts.id, accounts.description, accounts.appid, accounts.secret,
		accounts.token,accounts.encodingaeskey,accounts.createtime,accounts.delflag,accounts.accesstoken,wctype, agentid
		from wx_mp_accounts accounts
		left join user_role user on user.accounts_id=accounts.id
		where user.user_id = #{userid,jdbcType=BIGINT} and accounts.delflag=0
		group by accounts.id
		order
		by accounts.createtime desc
	</select>

	<select id="getMpAccountsAppid" resultMap="BaseResultMap"
		parameterType="java.lang.String">
		select
		<include refid="Base_Column_List" />
		from wx_mp_accounts where appid = #{appid,jdbcType=VARCHAR} and delflag=0 order by
		id asc limit 0,1
	</select>

	<select id="getAllMpAccounts" resultMap="BaseResultMap" parameterType="java.lang.Long">
		select
		<include refid="Base_Column_List" />
		from wx_mp_accounts where delflag = 0 and wctype='0'  order by delflag asc,createtime
		desc
	</select>

	<select id="getAllQyAccounts" resultMap="BaseResultMap"
			parameterType="java.lang.Long">
		select
		<include refid="Base_Column_List" />
		from wx_mp_accounts where delflag = 0 and wctype='1' order by delflag asc,createtime
		desc
	</select>



	<select id="getDetail" resultMap="BaseResultMap" parameterType="java.lang.Long">
		select
		<include refid="Base_Column_List" />
		from wx_mp_accounts where id = #{id,jdbcType=BIGINT}
	</select>
	
	<select id="getUserAccounts" parameterType="java.util.Map" resultType="java.lang.Integer">
		select count(1) from user_accounts accounts where userid=#{userid} and accountsid=#{accountsid} and delflag=0
	</select>


	<!-- 更新账户信息 -->
	<update id="updateByPrimaryKey" parameterType="com.cftech.accounts.model.MpAccountsEntity">
		update wx_mp_accounts
		<set>
			<if test="encodingaeskey != null">
				encodingaeskey = #{encodingaeskey,jdbcType=VARCHAR},
			</if>
			<if test="token != null">
				token = #{token,jdbcType=CHAR},
			</if>
			<if test="description != null">
				description = #{description,jdbcType=VARCHAR},
			</if>
			<if test="delflag != null">
				delflag = #{delflag,jdbcType=VARCHAR},
			</if>
			<if test="secret != null">
				secret = #{secret,jdbcType=VARCHAR},
			</if>
			<if test="accesstoken != null">
				accesstoken = #{accesstoken,jdbcType=VARCHAR},
			</if>
			<if test="jsapi_ticket !=null">
				jsapi_ticket = #{jsapi_ticket,jdbcType=VARCHAR},
			</if>
			<if test="wctype !=null">
				wctype = #{wctype,jdbcType=VARCHAR},
			</if>
			<if test="agentId !=null">
				agentid = #{agentId,jdbcType=VARCHAR},
			</if>
			updatetime =now()
		</set>
		where appid = #{appid,jdbcType=BIGINT}
	</update>

	<!-- 逻辑删除账户 -->
	<update id="deleteByPrimaryKey" parameterType="java.lang.Long">
		update wx_mp_accounts
		<set>
			delflag = 1,
			updatetime = now()
		</set>
		where id = #{id,jdbcType=BIGINT}
	</update>
	<!--删除关联数据 -->
	<update id="deleteByUserAccount" parameterType="java.util.Map">
		update user_accounts
		<set>
			delflag = 1
		</set>
		where userid = #{userid} and accountsid = #{accountsid}
	</update>

	<select id="getByQyUserAvater" parameterType="java.lang.Long" resultType="java.lang.String">
		SELECT q.avatar FROM t_qyuser q INNER JOIN user u ON q.id = u.userid WHERE q.del_flag = '0' AND u.delflag = '0' AND u.id = #{id}
	</select>
</mapper>