<?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.pcloud.book.book.dao.impl.BookAdviserDaoImpl">

	<!-- 书籍编辑实体DTO -->
	<resultMap id="bookAdviserMap" type="bookAdviserDto">
		<id column="BOOK_ADVISER_ID" property="bookAdviserId" jdbcType="BIGINT" />
		<result column="BOOK_ID" property="bookId" jdbcType="BIGINT" />
		<result column="ADVISER_ID" property="adviserId" jdbcType="BIGINT" />
		<result column="CHANNEL_ID" property="channelId" jdbcType="BIGINT" />
		<result column="TEMPLET_ID" property="templetId" jdbcType="BIGINT" />
		<result column="CREATED_USER" property="createdUser" jdbcType="BIGINT" />
		<result column="CREATED_DATE" property="createdDate" jdbcType="TIMESTAMP" />
		<result column="VERSION" property="version" jdbcType="BIGINT" />
		<result column="COUNT" property="count" jdbcType="BIGINT" />
		<result column="IS_MAIN_EDITOR" property="isMainEditor" jdbcType="BIT" />
		<result column="IS_DELETE" property="isDelete" jdbcType="BIT" />
		<result column="SECOND_TEMPLET_ID" property="secondTempletId" jdbcType="BIGINT" />
	</resultMap>
	
	<resultMap id="manageAdviserMap" type="adviserManageDto" >
		<result column="BOOK_ID" property="bookId" jdbcType="BIGINT" />
		<result column="CHANNEL_ID" property="channelId" jdbcType="BIGINT" />
	<collection property="adviserList" column="{channelId=CHANNEL_ID,bookId=bookId}" ofType="bookAdviserDto" select="getAdviserInfoByBookId" />
	
	</resultMap>


	<!-- 查询编辑的书刊信息 -->
	<select id="listBookIdsByAdviser" resultType="long">
		select BOOK_ID from BOOK_ADVISER where IS_DELETE = 0 and ADVISER_ID = #{adviserId} and CREATED_DATE >= DATE_SUB(CURDATE(), INTERVAL 3 MONTH)
	</select>

		<!-- 新增书籍和编辑关联信息-->
	<insert id="insert" parameterType="bookAdviser" useGeneratedKeys="true" keyProperty="bookAdviserId" flushCache="true">
		INSERT INTO BOOK_ADVISER (
			BOOK_ID, ADVISER_ID, CHANNEL_ID, CREATED_USER, CREATED_DATE,IS_MAIN_EDITOR, TEMPLET_ID, SECOND_TEMPLET_ID
		)
		VALUES (
			#{bookId, jdbcType=VARCHAR}, #{adviserId, jdbcType=VARCHAR}, #{channelId, jdbcType=VARCHAR}, 
			#{adviserId, jdbcType=BIGINT}, NOW(),#{isMainEditor, jdbcType=BIT},#{templetId, jdbcType=BIGINT},
			#{secondTempletId, jdbcType=BIGINT}
		)
		ON
			DUPLICATE KEY UPDATE IS_DELETE = 0, IS_MAIN_EDITOR = #{isMainEditor, jdbcType=BIT}
		  <if test="templetId!=null">
			,TEMPLET_ID=#{templetId, jdbcType=BIGINT}
		  </if>
			<if test="secondTempletId!=null">
				,SECOND_TEMPLET_ID=#{secondTempletId, jdbcType=BIGINT}
			</if>
	</insert>

	<!-- 批量新增书籍和编辑关联信息-->
	<insert id="batchInsert" parameterType="bookAdviser" useGeneratedKeys="true" keyProperty="bookAdviserId" flushCache="true">
		<foreach collection="list" item="item" index="index" separator=";">
		INSERT INTO BOOK_ADVISER (
			BOOK_ID, ADVISER_ID, CHANNEL_ID, CREATED_USER, CREATED_DATE,IS_MAIN_EDITOR, TEMPLET_ID,SECOND_TEMPLET_ID
		)
		VALUES 
		(
			#{item.bookId, jdbcType=VARCHAR}, #{item.adviserId, jdbcType=VARCHAR}, #{item.channelId, jdbcType=VARCHAR}, 
			#{item.adviserId, jdbcType=BIGINT}, NOW(),#{item.isMainEditor, jdbcType=BIT},#{item.templetId, jdbcType=BIGINT},#{item.secondTempletId, jdbcType=BIGINT}
		) ON DUPLICATE KEY UPDATE IS_DELETE = 0,IS_MAIN_EDITOR=#{item.isMainEditor, jdbcType=BIT}
		<if test="item.templetId!=null">
		   ,TEMPLET_ID=#{item.templetId, jdbcType=BIGINT}
		</if>
		<if test="item.secondTempletId!=null">
		  ,SECOND_TEMPLET_ID=#{item.secondTempletId, jdbcType=BIGINT}
		</if>
		</foreach>
	</insert>
	
	<!-- 删除编辑和书籍的推广信息 -->
	<update id="deleteByBook" parameterType="java.lang.Long" flushCache="true">
		UPDATE  
			BOOK_ADVISER
		SET
		    IS_DELETE = 1,
		    LAST_MODIFIED_DATE =  NOW()
		WHERE 
			BOOK_ID = #{bookId, jdbcType=BIGINT}
	</update>
	
	<!-- 批量删除编辑和书籍的推广信息 -->
	<update id="deleteByBooks" parameterType="list" flushCache="true">
		UPDATE  
			BOOK_ADVISER
		SET
		    IS_DELETE = 1,
			LAST_MODIFIED_DATE =  NOW()
		WHERE 
			BOOK_ID IN 
			<foreach collection="list" index="i" item="item" open="(" separator="," close=")">
				${item}
			</foreach>
	</update>
	
	<!-- 批量获取书籍信息 -->
	<select id="getBase" resultMap="bookAdviserMap" parameterType="map">
	   SELECT 
			BOOK_ADVISER_ID, BOOK_ID, CHANNEL_ID, ADVISER_ID, IS_DELETE
		FROM 
			BOOK_ADVISER
		WHERE
			BOOK_ID = #{bookId}
		AND
			CHANNEL_ID = #{channelId}
		AND
			ADVISER_ID = #{adviserId}
	</select>

	<select id="getByAdviser" resultMap="bookAdviserMap" parameterType="bookAdviser">
		SELECT
		ba.BOOK_ADVISER_ID, ba.BOOK_ID, ba.CHANNEL_ID, ba.ADVISER_ID, ba.IS_DELETE
		FROM
		BOOK_ADVISER ba
		LEFT JOIN BOOK b on b.BOOK_ID = ba.BOOK_ID
		WHERE
		ba.BOOK_ID = #{bookId}
		AND
		ba.CHANNEL_ID = #{channelId}
		AND
		ba.ADVISER_ID = #{adviserId}
		AND
		ba.IS_DELETE = 0
		<if test="serialNumber != null  and serialNumber != ''">
			AND b.SERIAL_NUMBER = #{serialNumber}
		</if>
		ORDER BY ba.CREATED_DATE
		LIMIT 1
	</select>

	
	<!-- 获取书籍信息 -->
	<select id="getByAdviserContainsDelete" resultMap="bookAdviserMap" parameterType="bookAdviser">
	   SELECT 
			ba.BOOK_ADVISER_ID, ba.BOOK_ID, ba.CHANNEL_ID, ba.ADVISER_ID, ba.IS_DELETE
		FROM 
			BOOK_ADVISER ba
		WHERE
			ba.BOOK_ID = #{bookId}
		AND
			ba.CHANNEL_ID = #{channelId}
		AND
			ba.ADVISER_ID = #{adviserId}
		ORDER BY ba.CREATED_DATE
        LIMIT 1
	</select>
	
	<!-- 统计编辑推广的图书总数 -->
	<select id="getCountByAdvisers" resultMap="bookAdviserMap" parameterType="list">
		SELECT 
			ADVISER_ID, COUNT(1) COUNT
		FROM 
			BOOK_ADVISER
		WHERE
			ADVISER_ID IN
			<foreach collection="list" index="i" item="item" open="(" separator="," close=")">
				${item}
			</foreach>
		AND 
			IS_DELETE = 0 
		GROUP BY 
			ADVISER_ID
	</select>
	
		<!-- 获取配置过书籍的编辑top1 -->
	<select id="getAdviserTop1" resultType="Long" parameterType="map">
		SELECT 
			ADVISER_ID
		FROM 
			BOOK_ADVISER 
		WHERE
			BOOK_ID = #{bookId}
		AND
			CHANNEL_ID = #{channelId}
		AND 
			IS_DELETE = 0
		LIMIT 1
	</select>
	
	<!-- 获取书籍信息 -->
	<!--<select id="getByChannel" resultMap="bookAdviserMap" parameterType="bookAdviser">-->
		<!--SELECT -->
			<!--BOOK_ADVISER_ID, BOOK_ID, CHANNEL_ID, ADVISER_ID-->
		<!--FROM -->
			<!--BOOK_ADVISER -->
		<!--WHERE-->
			<!--BOOK_ID = #{bookId}-->
		<!--AND-->
			<!--CHANNEL_ID = #{channelId}-->
		<!--AND-->
			<!--IS_DELETE = 0-->
		<!--AND -->
			<!--ADVISER_ID != #{adviserId}	      -->
		<!--LIMIT 1-->
	<!--</select>-->


	<select id="getCount4BookAdviser" resultType="Integer" parameterType="Long">
		SELECT
			COUNT(1)
		FROM
			BOOK_ADVISER
		WHERE
			BOOK_ID = #{bookId}
		AND
			IS_DELETE = 0
		AND
			IS_MAIN_EDITOR = 1
	</select>
	
	<!-- 根据渠道与书籍标识删除编辑和书籍的推广信息 -->
	<update id="deleteByBookAndChannel" parameterType="map" flushCache="true">
	    UPDATE 
	    	BOOK_ADVISER 
	    SET 
	        IS_DELETE = 1
	    WHERE 
	    	BOOK_ID = #{bookId, jdbcType=BIGINT}
		AND 
		    CHANNEL_ID = #{channelId, jdbcType=BIGINT}
		AND 
		    IS_DELETE = 0
	</update>
	
	<!-- 根据图书信息获取主编辑ID -->
	<select id="getMainAdviserId" resultType="Long" parameterType="map">
		SELECT 
			ADVISER_ID
		FROM 
			BOOK_ADVISER
		WHERE
			BOOK_ID = #{bookId}
		AND
			CHANNEL_ID = #{channelId}
		AND
			IS_MAIN_EDITOR = 1
		AND 
			IS_DELETE = 0
		LIMIT 1
	</select>
	
    <!--当前编辑是否是主编辑 -->
	 <select id="isMainAdviser4Book" resultType="Boolean" parameterType="map">
		SELECT 
			count(1)
		FROM 
			BOOK_ADVISER
		WHERE
			BOOK_ID = #{bookId}
		AND
			CHANNEL_ID = #{channelId}
		AND
			IS_MAIN_EDITOR = 1
		AND 
			ADVISER_ID = #{adviserId}
	</select>
	
	<!-- 根据bookId获取编辑信息 -->
	<select id="getAdviserList" resultMap="manageAdviserMap" parameterType="Long">
		SELECT 
			BOOK_ADVISER_ID, CHANNEL_ID, #{bookId} bookId
		FROM 
			BOOK_ADVISER 
		WHERE
			BOOK_ID = #{bookId}
		AND 
			IS_DELETE = 0
		GROUP BY  CHANNEL_ID
	</select>
	<!-- 根据bookId获取编辑信息 -->
	<select id="getAdviserInfoByBookId" resultMap="bookAdviserMap" >
		SELECT 
			BOOK_ADVISER_ID, BOOK_ID, CHANNEL_ID, ADVISER_ID,IS_MAIN_EDITOR
		FROM 
			BOOK_ADVISER 
		WHERE
			BOOK_ID = #{bookId}
			AND CHANNEL_ID = #{channelId}
			AND IS_DELETE = 0
	</select>
	
	<!--根据bookId获取编辑ID  -->
	<select id="getAdviserIdsByBookId" resultType="Long" parameterType="Long">
		SELECT 
			ADVISER_ID
		FROM 
			BOOK_ADVISER 
		WHERE
			BOOK_ID = #{bookId}
		AND 
			IS_DELETE = 0
	</select>
	
	<!-- 编辑管理  删除副编辑 -->
	<delete id="delViceAdviser" parameterType="bookAdviser"  flushCache="true">
		DELETE FROM 
			BOOK_ADVISER
		WHERE 
			IS_MAIN_EDITOR != 1
			AND	
			<foreach collection="list" item="item" index="index" open="(" separator="OR" close=")">
				BOOK_ID = #{item.bookId, jdbcType=BIGINT}
			AND 
			    CHANNEL_ID = #{item.channelId, jdbcType=BIGINT}
			AND 
				ADVISER_ID = #{item.adviserId, jdbcType=BIGINT}
		</foreach>
		AND 
			IS_DELETE = 0
	</delete>
	
	<!--获取在某个运营平台关联过某本书的所有编辑的ID -->
	<select id="getAdviserIdsByBookAndChannel" resultType="Long" parameterType="map">
		SELECT 
			ADVISER_ID
		FROM 
			BOOK_ADVISER
		WHERE
			BOOK_ID = #{bookId}
		AND
			CHANNEL_ID = #{channelId}
		AND 
			IS_DELETE = 0
	</select>
	<!-- 根据bookIds查询书刊编辑关联信息 -->
	<select id="getByBookIds" resultMap="bookAdviserMap" parameterType="list" >
		SELECT  BOOK_ADVISER_ID,BOOK_ID, ADVISER_ID, CHANNEL_ID,IS_MAIN_EDITOR
		FROM BOOK_ADVISER 
		WHERE
		  	BOOK_ID IN
			<foreach collection="list" index="index" item="item" open="(" separator="," close=")">
				${item}
			</foreach>
		AND 
			IS_DELETE = 0
		GROUP BY  BOOK_ID
	</select>
	
	<!-- 编辑下的所有书刊的数目 -->
	<select id="getBookCount" parameterType="list" resultType="int">
		SELECT
			COUNT(DISTINCT BOOK_ID)
		FROM
			BOOK_ADVISER
		WHERE
			ADVISER_ID IN 
			<foreach collection="list" index="i" item="item" open="(" separator="," close=")">
				${item}
			</foreach>
		AND 
			IS_DELETE = 0
	</select>
	
	<!-- 修改书籍模板信息-->
	<update id="updateBookTemplet" parameterType="bookAdviser" flushCache="true">
		UPDATE 
			BOOK_ADVISER
		SET
			TEMPLET_ID = #{templetId, jdbcType=BIGINT}
			<if test="secondTempletId!=null">
				,SECOND_TEMPLET_ID = #{secondTempletId, jdbcType=BIGINT}
			</if>
		WHERE
			BOOK_ID = #{bookId, jdbcType=BIGINT}
		AND 
			ADVISER_ID = #{adviserId, jdbcType=BIGINT}
		AND 
			CHANNEL_ID = #{channelId, jdbcType=BIGINT}
	</update>	
	
	<!-- 代理每日新增书刊-->
	<select id="listBookCountByDate4Agent" resultType="bookCountDto" parameterType="map">
	    SELECT
			DATE(CREATED_DATE) date,
			COUNT(DISTINCT BOOK_ID) as bookCount
		FROM
			`BOOK_ADVISER` ba
		WHERE
			ba.IS_DELETE = 0
		AND	ba.ADVISER_ID IN 
		 <foreach collection = "adviserIds" index="index" item = "item" open = "("  separator= "," close = ")"> 
		  		${item} 
		 </foreach>
		AND DATE(CREATED_DATE) BETWEEN #{startDate} AND #{endDate}
		AND BOOK_ID NOT IN (
			SELECT DISTINCT
				BOOK_ID
			FROM
				BOOK_ADVISER
			WHERE
		<![CDATA[	DATE(CREATED_DATE) < DATE(ba.CREATED_DATE)  ]]> 
			AND ADVISER_ID IN 
		 <foreach collection = "adviserIds" index="index" item = "item" open = "("  separator= "," close = ")"> 
		  		${item} 
		 </foreach>
		 AND 
			IS_DELETE = 0
		)
		GROUP BY
			DATE(CREATED_DATE)
	</select>
	
	<!-- 恢复图书 -->
	<update id="updateBook4Adviser" parameterType="map" flushCache="true">
		UPDATE
			BOOK_ADVISER
		SET
			IS_DELETE = #{status, jdbcType=BIGINT}
		<if test="isMainAdviser != null">	
			,IS_MAIN_EDITOR = #{isMainAdviser}
		</if>
		<if test="templetId != null">	
			,TEMPLET_ID = #{templetId}
		</if>
		<if test="secondTempletId != null">
			,SECOND_TEMPLET_ID = #{secondTempletId}
		</if>
			,LAST_MODIFIED_DATE =  NOW()
		WHERE
			BOOK_ID = #{bookId, jdbcType=BIGINT}
		<if test="adviserId != null">
		AND 
			ADVISER_ID = #{adviserId, jdbcType=BIGINT}
		</if>
		AND 
			CHANNEL_ID = #{channelId, jdbcType=BIGINT}
	</update>
	
	<!-- 获取编辑的图书总数 -->
	<select id="getAdviserBookCount" resultType="int" parameterType="long">
		SELECT
			COUNT(b.BOOK_ID)
		FROM
			book b,
			book_adviser ba
		WHERE
			ba.ADVISER_ID = #{adviserId, jdbcType=BIGINT}
		AND
			ba.IS_DELETE = 0 AND b.IS_DELETE = 0
		AND 
			ba.BOOK_ID = b.BOOK_ID	
	</select>
	
	<!-- 获取被诉方信息(即与该书关联的所有编辑) -->
	<select id="getAdvisers" resultMap="bookAdviserMap" parameterType="Long">
		SELECT
			ADVISER_ID,  BOOK_ID, CHANNEL_ID, IS_MAIN_EDITOR, CREATED_DATE
		FROM
			book_adviser
		WHERE
			BOOK_ID = #{bookId, jdbcType=BIGINT}
		AND
			IS_DELETE = 0
	</select>


	<select id="listAdviserBookInfo" resultType="AdviserBookInfoDTO" parameterType="aviserBookInfoParam">
		SELECT
			ba.BOOK_ID bookId,
			b.BOOK_NAME bookName,
			b.cover_img coverImg,
			b.type_code typeCode,
			b.isbn,
			b.serial_number serialNumber,
			ba.adviser_id adviserId,
			ba.channel_id channelId,
			ba.created_date createdDate
		FROM
			book_adviser ba join book b on ba.book_id = b.book_id and  ba.is_delete = 0 and b.is_delete = 0
		WHERE
		  ba.BOOK_ID in
		<foreach collection = "bookIds" index="index" item = "item" open = "("  separator= "," close = ")">
			${item}
		</foreach>
		<if test="adviserIds!=null">
		AND
			ADVISER_ID in
		<foreach collection = "adviserIds" index="index" item = "item" open = "("  separator= "," close = ")">
			${item}
		</foreach>
		</if>
		<if test="channelIds!=null">
		AND
			CHANNEL_ID in
		<foreach collection = "channelIds" index="index" item = "item" open = "("  separator= "," close = ")">
			${item}
		</foreach>
		</if>
	</select>

	<select id="getBookCountByAdviserId" parameterType="bookCountByAdvisersDto" resultType="bookCountAndAdviserIdDTO">
		SELECT adviser_id adviserId, count(distinct (book_id)) bookCount
		FROM book_adviser
		WHERE adviser_id IN
		<foreach collection="adviserIds" item="adviserId" open="(" close=")" separator=",">
			#{adviserId}
		</foreach>
		<if test="startTime != null">
			AND CREATED_DATE &gt;= DATE_FORMAT(#{startTime}, '%Y-%m-%d 00:00:00')
		</if>
		<if test="endTime != null">
			AND CREATED_DATE &lt;= DATE_FORMAT(#{endTime}, '%Y-%m-%d 23:59:59')
		</if>
		GROUP BY adviser_id
	</select>

	<!--刷数据使用，请勿使用-->
	<update id="updateBookBySecondTempletId" parameterType="map">
		UPDATE
			book_adviser
	  	SET
	  		<if test="secondTempletId!=null">
		  		SECOND_TEMPLET_ID = #{secondTempletId},
			</if>
		  	TEMPLET_ID = #{parentId}
	  	WHERE
		  	TEMPLET_ID = #{originTempletId}
	</update>

</mapper>