<?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.BookDaoImpl">

	<!-- 书籍实体DTO -->
	<resultMap id="bookMap" type="bookDto">
		<id column="BOOK_ID" property="bookId" jdbcType="BIGINT" />
		<result column="TYPE_CODE" property="typeCode" jdbcType="VARCHAR" />
		<result column="TYPE_NAME" property="typeName" jdbcType="VARCHAR" />
		<result column="ISBN" property="isbn" jdbcType="VARCHAR" />
		<result column="BOOK_NAME" property="bookName" jdbcType="VARCHAR" />
		<result column="BOOK_PRICE" property="bookPrice" jdbcType="VARCHAR" />
		<result column="REMARK" property="remark" jdbcType="VARCHAR" />
		<result column="AUTHOR" property="author" jdbcType="VARCHAR" />
		<result column="PUBLISH" property="publish" jdbcType="VARCHAR" />
		<result column="PUBLISH_DATE" property="publishDate" jdbcType="TIMESTAMP" />
		<result column="COVER_IMG" property="coverImg" jdbcType="VARCHAR" />
		<result column="DETAIL" property="detail" jdbcType="LONGVARCHAR" />
		<result column="SPONSOR" property="sponsor" jdbcType="VARCHAR"/>
		<result column="ORGANIZER" property="organizer" jdbcType="VARCHAR"/>
		<result column="CN" property="cn" jdbcType="VARCHAR"/>
		<result column="RELEASE_CYCLE" property="releaseCycle" jdbcType="VARCHAR"/>
		<result column="BOOK_NUM" property="bookNum" jdbcType="VARCHAR"/>
		<result column="PROGRAMA" property="programa" jdbcType="VARCHAR"/>
		<result column="MAIL_CODE" property="mailCode" jdbcType="VARCHAR"/>
		<result column="ADDRESS" property="address" jdbcType="VARCHAR"/>
		<result column="TELEPHONE" property="telephone" jdbcType="VARCHAR"/>
		<result column="MAIL" property="mail" jdbcType="VARCHAR"/>
		<result column="PURPOSE" property="purpose" jdbcType="VARCHAR"/>
		<result column="ADVISER_ID" property="adviserId" jdbcType="BIGINT" />
		<result column="CHANNEL_ID" property="channelId" 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="QRCODE_COUNT" property="qrcodeCount" jdbcType="BIGINT" />
		<result column="APP_COUNT" property="appCount" jdbcType="BIGINT" />
		<result column="PRODUCT_COUNT" property="productCount" jdbcType="BIGINT" />
		<result column="ORIGIN_NAME" property="originName" jdbcType="VARCHAR" />
		<result column="ISSN" property="issn" jdbcType="VARCHAR" />
		<result column="APP_ID" property="appId" jdbcType="BIGINT" />
		<result column="SERIAL_NUMBER" property="serialNumber" jdbcType="VARCHAR" />
		<result column="AD_IMG" property="adImg" jdbcType="VARCHAR" />
		<result column="IS_MAIN_EDITOR" property="isMainEditor" jdbcType="BIT" />
		<result column="IS_FUND_SUPPORT" property="isFundSupport" jdbcType="BIT" />
		<result column="IS_HAVE_FUND" property="isHaveFund" jdbcType="BIT" />
		<result column="START_TIME" property="startTime" jdbcType="TIMESTAMP" />
		<result column="END_TIME" property="endTime" jdbcType="TIMESTAMP" />
		<result column="TEMPLET_ID" property="templetId" jdbcType="BIGINT" />
		<result column="IS_DELETE" property="isDelete" jdbcType="BIT" />
		<result column="BOOK_STATUS" property="bookStatus" jdbcType="BIT" />
		<result column="LAST_MODIFIED_DATE" property="lastModifiedDate" jdbcType="TIMESTAMP" />
		<result column="BOOK_ADVISER_ID" property="bookAdviserId" jdbcType="BIGINT" />
		<result column="SECOND_TEMPLET_ID" property="secondTempletId" jdbcType="BIGINT" />
		<result column="SECOND_TYPE_CODE" property="secondTypeCode" jdbcType="BIGINT" />
		<result column="BOOK_GROUP_ID" property="bookGroupId" jdbcType="BIGINT" />
		<result column="join_group_type" property="joinGroupType" jdbcType="INTEGER"/>
		<result column="book_group_cipher" property="bookGroupCipher" jdbcType="VARCHAR"/>
		<result column="has_serve" property="hasServe" jdbcType="VARCHAR"/>
		<result column="short_book_name" property="shortBookName" jdbcType="VARCHAR"/>
		<result column="GRA_LABEL_ID" property="graLabelId" jdbcType="BIGINT" />
		<result column="SUB_LABEL_ID" property="subLabelId" jdbcType="BIGINT" />
		<result column="VER_LABEL_ID" property="verLabelId" jdbcType="BIGINT" />
		<result column="AREA_LABEL_ID" property="areaLabelId" jdbcType="BIGINT" />
	</resultMap>
	
	<resultMap id="bookListPageMap" type="bookDto" extends ="bookMap">
	    <collection property="bookFunds" column="BOOK_ID" ofType="bookFundDto" 
	    select="com.pcloud.book.book.dao.impl.BookFundDaoImpl.getByBookId" />
	</resultMap>
	
	<!-- 新增书籍信息-->
	<insert id="insert" parameterType="book" useGeneratedKeys="true" keyProperty="bookId" flushCache="true">
		INSERT INTO `BOOK` (
			ISBN, TYPE_CODE, BOOK_NAME,BOOK_PRICE, REMARK, AUTHOR, PUBLISH, PUBLISH_DATE, COVER_IMG, DETAIL,
			SPONSOR, ORGANIZER, CN, RELEASE_CYCLE, BOOK_NUM, PROGRAMA, MAIL_CODE, ADDRESS, TELEPHONE, MAIL, PURPOSE, ORIGIN_NAME,
			CREATED_USER, CREATED_DATE, LAST_MODIFIED_USER, LAST_MODIFIED_DATE, ISSN, SERIAL_NUMBER, AD_IMG
		)
		VALUES (
			#{isbn, jdbcType=VARCHAR}, #{typeCode, jdbcType=VARCHAR}, #{bookName, jdbcType=VARCHAR}, #{bookPrice, jdbcType=VARCHAR},
			#{remark, jdbcType=VARCHAR}, #{author, jdbcType=VARCHAR}, #{publish, jdbcType=VARCHAR}, #{publishDate, jdbcType=VARCHAR}, 
			#{coverImg, jdbcType=VARCHAR}, #{detail, jdbcType=VARCHAR}, #{sponsor, jdbcType=VARCHAR}, #{organizer, jdbcType=VARCHAR},
			#{cn, jdbcType=VARCHAR}, #{releaseCycle, jdbcType=VARCHAR}, #{bookNum, jdbcType=VARCHAR}, #{programa, jdbcType=VARCHAR},
			#{mailCode, jdbcType=VARCHAR}, #{address, jdbcType=VARCHAR}, #{telephone, jdbcType=VARCHAR}, #{mail, jdbcType=VARCHAR},
			#{purpose, jdbcType=VARCHAR}, #{originName, jdbcType=VARCHAR}, #{createdUser, jdbcType=BIGINT}, NOW(),
			#{createdUser, jdbcType=BIGINT}, NOW(),#{issn, jdbcType=VARCHAR},#{serialNumber, jdbcType=VARCHAR},#{adImg, jdbcType=VARCHAR}
		) ON
			DUPLICATE KEY UPDATE IS_DELETE = 0
	</insert>

	<!-- 修改书籍信息-->
	<update id="update" parameterType="book" flushCache="true">
		UPDATE BOOK
		SET
		    <if test="bookName != null">
			BOOK_NAME = #{bookName, jdbcType=VARCHAR}, 
			</if>
			<if test="bookPrice != null">
			BOOK_PRICE = #{bookPrice, jdbcType=VARCHAR},
			</if>
			<if test="remark != null">
			REMARK = #{remark, jdbcType=VARCHAR}, 
			</if>
			<if test="issn != null">
			ISSN = #{issn, jdbcType=VARCHAR},
			</if>
			<if test="author != null">
			AUTHOR = #{author, jdbcType=VARCHAR}, 
			</if>
			<if test="publish != null">
			PUBLISH = #{publish, jdbcType=VARCHAR}, 
			</if>
			<if test="publishDate != null">
			PUBLISH_DATE = #{publishDate, jdbcType=VARCHAR},
			</if>
			<if test="coverImg != null">
			COVER_IMG = #{coverImg, jdbcType=VARCHAR}, 
			</if>
			<if test="detail != null">
			DETAIL = #{detail, jdbcType=VARCHAR},
			</if>
			<if test="sponsor != null">
			SPONSOR = #{sponsor, jdbcType=VARCHAR},
			</if>
			<if test="organizer != null">
			ORGANIZER = #{organizer, jdbcType=VARCHAR},
			</if>
			<if test="cn != null">
			CN = #{cn, jdbcType=VARCHAR},
			</if>
			<if test="releaseCycle != null">
			RELEASE_CYCLE = #{releaseCycle, jdbcType=VARCHAR},
			</if>
			<if test="bookNum != null">
			BOOK_NUM = #{bookNum, jdbcType=VARCHAR},
			</if>
			<if test="programa != null">
			PROGRAMA = #{programa, jdbcType=VARCHAR},
			</if>
			<if test="mailCode != null">
			MAIL_CODE = #{mailCode, jdbcType=VARCHAR},
			</if>
			<if test="address != null">
			ADDRESS = #{address, jdbcType=VARCHAR},
			</if>
			<if test="telephone != null">
			TELEPHONE = #{telephone, jdbcType=VARCHAR},
			</if>
			<if test="mail != null">
			MAIL = #{mail, jdbcType=VARCHAR},
			</if>
			<if test="purpose != null">
			PURPOSE = #{purpose, jdbcType=VARCHAR},
			</if>
			<if test="originName != null">
			ORIGIN_NAME =  #{originName, jdbcType=VARCHAR},
			</if>
			<if test="lastModifiedUser != null">
			LAST_MODIFIED_USER = #{lastModifiedUser, jdbcType=BIGINT}, 
			</if>
			<!-- <if test="serialNumber != null">
		    SERIAL_NUMBER = #{serialNumber, jdbcType=VARCHAR},
            </if> -->
            <if test="adImg != null">
			AD_IMG = #{adImg, jdbcType=VARCHAR}, 
			</if>
			<if test="isDelete != null">
			IS_DELETE = #{isDelete, jdbcType=BIT}, 
			</if>
            LAST_MODIFIED_DATE =  NOW(),
			VERSION = VERSION + 1
		WHERE
			BOOK_ID = #{bookId, jdbcType=BIGINT}
		AND
			VERSION = #{version, jdbcType=BIGINT}
	</update>
	
		<!-- 修改书籍信息-->
	<update id="updateCoverImg" parameterType="book" flushCache="true">
		UPDATE BOOK
		SET
			COVER_IMG = #{coverImg, jdbcType=VARCHAR}, 
			LAST_MODIFIED_USER = #{lastModifiedUser, jdbcType=BIGINT}, 
            LAST_MODIFIED_DATE =  NOW()
		WHERE
			BOOK_ID = #{bookId, jdbcType=BIGINT}
	</update>
	
	<!-- 删除书籍信息 -->
	<delete id="deleteBook" parameterType="map" flushCache="true">
		UPDATE
			`BOOK`
		SET  IS_DELETE = 1,
		     LAST_MODIFIED_USER = #{lastModifiedUser, jdbcType=BIGINT},
		     LAST_MODIFIED_DATE =  NOW()
		WHERE 
			BOOK_ID = #{bookId, jdbcType=BIGINT}
	</delete>
	
	<!-- 批量删除书籍信息 -->
	<delete id="deleteByIds" parameterType="map" flushCache="true">
		UPDATE  
			`BOOK`
		SET  IS_DELETE = 1,
		     LAST_MODIFIED_USER = #{lastModifiedUser, jdbcType=BIGINT},
		     LAST_MODIFIED_DATE =  NOW()
		WHERE 
			BOOK_ID IN 
			<foreach collection="bookIds" index="i" item="item" open="(" separator="," close=")">
				${item}
			</foreach>
	</delete>
	
	<!-- 获取单个书籍基础信息 -->
	<select id="getBaseById" resultMap="bookMap" parameterType="long">
		SELECT 
			BOOK_ID, ISBN,TYPE_CODE, BOOK_NAME, REMARK, AUTHOR, PUBLISH, PUBLISH_DATE, COVER_IMG, DETAIL,BOOK_PRICE,ISSN,
			CREATED_USER, CREATED_DATE, VERSION,SERIAL_NUMBER,AD_IMG,ORIGIN_NAME,
			SPONSOR, ORGANIZER, CN, RELEASE_CYCLE, BOOK_NUM, PROGRAMA, MAIL_CODE, ADDRESS, TELEPHONE, MAIL, PURPOSE
		FROM 
			BOOK
		WHERE
			BOOK_ID = #{bookId, jdbcType=BIGINT}
	</select>
	
	<!-- 根据ISBN获取图书信息 -->
	<select id="getByIsbnAndSnumContainsDelete" resultMap="bookMap" parameterType="map">
		SELECT 
			BOOK_ID, ISBN, BOOK_NAME,TYPE_CODE,AUTHOR,PUBLISH,PUBLISH_DATE,
			COVER_IMG, REMARK,ORIGIN_NAME,BOOK_PRICE,ISSN,VERSION,AD_IMG
		FROM 
			BOOK
		WHERE
			ISBN = #{isbn}
			<choose>
			    <when test="serialNumber == null">
			        AND 
			        	(SERIAL_NUMBER IS NULL OR SERIAL_NUMBER = '')
			    </when>
			    <otherwise>
			        AND SERIAL_NUMBER = #{serialNumber}
			    </otherwise>
			</choose>
		ORDER BY CREATED_DATE DESC
		LIMIT 1
	</select>
	
	<!-- 根据ISBN获取书刊信息（包含删除图书数据） -->
	<select id="getByIsbn" resultMap="bookMap" parameterType="string">
		SELECT 
			ISBN, TYPE_CODE, BOOK_NAME, AUTHOR,
			PUBLISH, PUBLISH_DATE, COVER_IMG,AD_IMG,REMARK,ORIGIN_NAME,BOOK_PRICE,ISSN,SERIAL_NUMBER,VERSION,
			SPONSOR, ORGANIZER, CN, RELEASE_CYCLE, BOOK_NUM, PROGRAMA, MAIL_CODE, ADDRESS, TELEPHONE, MAIL, PURPOSE
		FROM 
			BOOK
		WHERE
			ISBN = #{_parameter}
		ORDER BY CREATED_DATE DESC
		LIMIT 1
	</select>
	
	<!-- 根据ISBN和序列号获取书刊信息（不包含删除的） -->
	<select id="getByIsbnAndSnum" resultMap="bookMap" parameterType="map">
		SELECT 
			BOOK_ID, ISBN, TYPE_CODE, BOOK_NAME, AUTHOR,
			PUBLISH, PUBLISH_DATE, COVER_IMG, AD_IMG, REMARK,
			ORIGIN_NAME,BOOK_PRICE,ISSN,SERIAL_NUMBER,
			SPONSOR, ORGANIZER, CN, RELEASE_CYCLE, BOOK_NUM, 
			PROGRAMA, MAIL_CODE, ADDRESS, TELEPHONE, MAIL, PURPOSE,
			VERSION, IS_DELETE
		FROM 
			BOOK
		WHERE
			ISBN = #{isbn}
			<choose>
			    <when test="serialNumber == null">
			        AND 
			        	(SERIAL_NUMBER IS NULL OR SERIAL_NUMBER ='')
			    </when>
			    <otherwise>
			        AND SERIAL_NUMBER = #{serialNumber}
			    </otherwise>
			</choose>
			AND IS_DELETE = 0
		LIMIT 1
	</select>
	
	<!-- 获取单个书籍基础信息(包含统计信息) -->
	<select id="getById" resultMap="bookListPageMap" parameterType="bookApp">
		SELECT 
			B.BOOK_ID, B.TYPE_CODE,B.ISBN, B.BOOK_NAME,T.TYPE_NAME, B.REMARK, B.AUTHOR, B.PUBLISH, 
			B.PUBLISH_DATE, B.COVER_IMG, B.AD_IMG, B.DETAIL, B.`VERSION`,B.BOOK_PRICE,B.ISSN,B.SERIAL_NUMBER,BA.IS_MAIN_EDITOR,			
	   		IF(ISNULL(BF.BOOK_FUND_ID),0,1) IS_FUND_SUPPORT,START_TIME,END_TIME,BA.TEMPLET_ID,BA.SECOND_TEMPLET_ID,
			BA.GRA_LABEL_ID,BA.SUB_LABEL_ID,BA.VER_LABEL_ID,BA.AREA_LABEL_ID, BA.IS_PRINT isPrint
		FROM 
			BOOK_ADVISER BA	
		INNER JOIN 
		     BOOK B	
		ON B.BOOK_ID = BA.BOOK_ID AND B.BOOK_ID = #{bookId}  AND BA.CHANNEL_ID = #{channelId} AND BA.ADVISER_ID = #{adviserId} AND BA.IS_DELETE = 0
		INNER JOIN
		     BOOK_TYPE T ON  T.TYPE_CODE = B.TYPE_CODE
		LEFT JOIN 
			 BOOK_FUND BF ON BF.BOOK_ID = B.BOOK_ID AND BF.END_TIME <![CDATA[ > ]]> NOW() AND BF.START_TIME <![CDATA[ < ]]> NOW()
         LIMIT 1
	</select>
	
	<!-- 批量获取书籍信息 -->
	<select id="getMapByIds" resultMap="bookMap" parameterType="list">
		SELECT 
			BOOK_ID, ISBN, ISSN, BOOK_NAME, REMARK, AUTHOR, PUBLISH, PUBLISH_DATE, COVER_IMG,AD_IMG,ORIGIN_NAME,BOOK_PRICE,TYPE_CODE,SERIAL_NUMBER,CREATED_DATE
		FROM 
			BOOK
		WHERE
			BOOK_ID IN 
			<foreach collection="list" index="i" item="item" open="(" separator="," close=")">
				${item}
			</foreach>
	</select>
	
	<!-- 批量获取书籍信息（书名 与书刊序号 封面图） -->
	<select id="listBaseMapByIds" resultMap="bookMap" parameterType="list">
		SELECT 
			BOOK_ID, ISBN,BOOK_NAME, COVER_IMG,SERIAL_NUMBER
		FROM 
			BOOK
		WHERE
			BOOK_ID IN 
			<foreach collection="list" index="i" item="item" open="(" separator="," close=")">
				${item}
			</foreach>
	</select>

	<select id="listDetailByIds" resultMap="bookMap" parameterType="map">

		SELECT
			b.BOOK_ID,b.BOOK_NAME,ba.TEMPLET_ID,ba.CHANNEL_ID FROM book b LEFT JOIN book_adviser ba ON b.BOOK_ID = ba.BOOK_ID
		WHERE
			b.BOOK_ID IN
		<foreach collection="bookIds" index="i" item="item" open="(" separator="," close=")">
			${item}
		</foreach>
		<if test="channelIds != null">
			AND
			ba.CHANNEL_ID in
			<foreach collection="channelIds" index="i" item="item" open="(" separator="," close=")">
				${item}
			</foreach>
		</if>
		<if test="adviserId != null">
			AND
			ba.ADVISER_ID = #{adviserId}
		</if>

	</select>
	
	<!-- 获取书籍列表 (平台端)-->
	<select id="getListPage4Platform" resultMap="bookListPageMap" parameterType="map">
		SELECT 
			B.BOOK_ID, B.TYPE_CODE, B.ISBN, B.BOOK_NAME, B.AUTHOR, B.PUBLISH,
			B.COVER_IMG, B.BOOK_PRICE, B.SERIAL_NUMBER, B.AD_IMG
		FROM 
			BOOK B
			<if test="isCurrentMonth!=null or  fundName!=null">
				JOIN
				(
					SELECT DISTINCT BOOK_ID FROM BOOK_FUND
					WHERE 1=1 
					<if test="isCurrentMonth!=null">
					AND DATE_FORMAT(START_TIME, '%Y-%m')  <![CDATA[ <= ]]> DATE_FORMAT(CURRENT_DATE(), '%Y-%m')
					AND DATE_FORMAT(END_TIME, '%Y-%m') <![CDATA[ >= ]]> DATE_FORMAT(CURRENT_DATE(), '%Y-%m')
					</if>
					<if test="fundName!=null">
					  AND
						FUND_NAME LIKE CONCAT('%', #{fundName},'%')
					</if>
				)
				 BF ON  B.BOOK_ID = BF.BOOK_ID
			</if>
		WHERE
			IS_DELETE = 0
		<if test="serialNumber!=null">
		AND
			B.SERIAL_NUMBER LIKE CONCAT('%', #{serialNumber},'%')
		</if>
		<if test="bookName!=null">
		AND (
			B.BOOK_NAME LIKE CONCAT('%', #{bookName},'%')
			<if test="bookNameList != null and bookNameList.size > 0">
				<foreach collection="bookNameList" item="item" open=" " separator=" " close=" ">
					OR B.BOOK_NAME LIKE CONCAT('%', '${item}','%')
				</foreach>
			</if>
		)
		</if>
		<if test="isbn!=null">
		AND
			B.ISBN LIKE CONCAT(#{isbn},'%')
		</if>
		<if test="publish!=null">
		AND
			B.PUBLISH LIKE CONCAT('%', #{publish},'%')
		</if>
		LIMIT #{currentPage},#{numPerPage}
	</select>

	<!-- 获取书籍总数 -->
	<select id="getAllBookCount" resultType="Integer">
		SELECT 
			COUNT(1)
		FROM 
			BOOK
	</select>

	<!-- 获取平台端书籍总数（带条件） -->
	<select id="getBookCount4Platform" resultType="Integer" parameterType="map">
		SELECT 	
			COUNT(1)
		FROM
			(
				SELECT 
					B.BOOK_ID
				FROM 
					BOOK B
				<if test="isCurrentMonth!=null or  fundName!=null">
					JOIN (
					SELECT DISTINCT BOOK_ID FROM BOOK_FUND
							WHERE 1=1
						<if test="isCurrentMonth!=null">
							AND DATE_FORMAT(START_TIME, '%Y-%m')  <![CDATA[ <= ]]> DATE_FORMAT(CURRENT_DATE(), '%Y-%m')
							AND DATE_FORMAT(END_TIME, '%Y-%m') <![CDATA[ >= ]]> DATE_FORMAT(CURRENT_DATE(), '%Y-%m')
						</if>
						<if test="fundName!=null">
							AND
							FUND_NAME LIKE CONCAT('%', #{fundName},'%')
						</if>
					  ) BF ON  B.BOOK_ID = BF.BOOK_ID
				</if>
				WHERE
					IS_DELETE = 0
				<if test="serialNumber!=null">
					AND
					B.SERIAL_NUMBER LIKE CONCAT('%', #{serialNumber},'%')
				</if>
				<if test="bookName!=null">
					AND (
						B.BOOK_NAME LIKE CONCAT('%', #{bookName},'%')
						<if test="bookNameList != null and bookNameList.size > 0">
							<foreach collection="bookNameList" item="item" open=" " separator=" " close=" ">
								OR B.BOOK_NAME LIKE CONCAT('%', '${item}','%')
							</foreach>
						</if>
					)
				</if>
				<if test="isbn!=null">
				AND
					(B.ISBN LIKE CONCAT(#{isbn},'%'))
				</if>
				<if test="publish!=null">
				AND
					B.PUBLISH LIKE CONCAT('%', #{publish},'%')
				</if>
			) a
	</select>
	
	<!-- 获取书籍列表(编辑) -->
	<select id="getListPage4Adviser" resultMap="bookMap" parameterType="map">
		SELECT 
			A.BOOK_ID, A.CHANNEL_ID, A.ADVISER_ID, A.IS_MAIN_EDITOR, T.TYPE_CODE, T.TYPE_NAME, B.ISBN, B.BOOK_NAME, B.REMARK, 
			B.AUTHOR, B.PUBLISH, B.PUBLISH_DATE, B.COVER_IMG, B.ORIGIN_NAME, B.BOOK_PRICE, B.ISSN, B.BOOK_NUM, B.SERIAL_NUMBER,
			IF(ISNULL(BF.BOOK_FUND_ID),0,1) IS_FUND_SUPPORT,A.TEMPLET_ID, if(G.ID IS NULL, A.LAST_MODIFIED_DATE, G.UPDATE_TIME) LAST_MODIFIED_DATE,
            A.BOOK_ADVISER_ID,I.BOOK_STATUS, CONCAT('BK',A.BOOK_ID) uniqueNumber, if(G.ID IS NULL, 0, 1) isBookGroup, A.IS_PRINT isPrint,
			G.id BOOK_GROUP_ID, G.group_qrcode_url groupQrcodeUrl,G.group_qrcode_name groupQrcodeName
		FROM 
			BOOK_ADVISER A
	    INNER JOIN BOOK B ON A.BOOK_ID = B.BOOK_ID AND A.ADVISER_ID = #{adviserId} AND A.IS_DELETE = 0 AND B.IS_DELETE = 0
		LEFT JOIN BOOK_TYPE T ON B.TYPE_CODE = T.TYPE_CODE
		LEFT JOIN BOOK_FUND BF ON  BF.BOOK_ID = A.BOOK_ID AND BF.END_TIME <![CDATA[ > ]]> NOW()  AND BF.START_TIME <![CDATA[ < ]]> NOW()
		LEFT JOIN book_auth_info I ON I.BOOK_ID = A.BOOK_ID AND I.ADVISER_ID = #{adviserId}
        LEFT JOIN book_group G on G.BOOK_ID = A.BOOK_ID AND G.CHANNEL_ID = A.CHANNEL_ID AND G.CREATE_USER = A.ADVISER_ID AND G.IS_DELETE = 0
		LEFT JOIN book_freeze f ON B.BOOK_ID = f.BOOK_ID
		WHERE
			1=1
		<if test="bookId!=null">
		AND
			A.BOOK_ID = #{bookId}
		</if>	
		<if test="typeCode!=null">
		AND
			B.TYPE_CODE = #{typeCode}
		</if>
		<if test="channelId!=null">
		AND
			A.CHANNEL_ID = #{channelId}
		</if>
		<if test="bookName!=null">
		AND
			B.BOOK_NAME LIKE CONCAT('%', #{bookName}, '%')
		</if>
		<if test="isbn!=null">
		AND
			B.ISBN LIKE CONCAT(#{isbn},'%')
		</if>
		<if test="name != null">
		  AND
			(B.BOOK_NAME LIKE CONCAT('%', #{name}, '%') OR  B.ISBN LIKE CONCAT(#{name},'%') OR CONCAT('BK',A.BOOK_ID) = #{name})
		</if>
		<if test="isMainEditor != null">
		  AND
			A.IS_MAIN_EDITOR = 1
		</if>
		<if test="isFundSupport != null">
			AND (
				BF.BOOK_FUND_ID IS NOT NULL
				OR f.IS_FREEZE = 1
			)
		</if>
		<if test="templetId != null">
			AND
			A.TEMPLET_ID = #{templetId}
		</if>
		<if test="secondTempletIds!=null and secondTempletIds.size()>0">
		AND
			A.SECOND_TEMPLET_ID in
			<foreach collection="secondTempletIds" item="item" open="(" separator="," close=")">
				${item}
			</foreach>
		</if>
		<if test="isbns != null">
		  AND
			B.ISBN <![CDATA[ <> ]]> ${isbns}
		</if>
		<if test="bookStatus != null">
			AND
			I.book_status = #{bookStatus}
		</if>
		<if test="isPrint != null">
			AND
			A.IS_PRINT = #{isPrint}
		</if>
		<if test="bookIds != null">
			<foreach collection="bookIds" index="index" item="item" open="and (" separator="or" close=")">
				<if test="item.bookId != null and item.channelId != null">
					A.BOOK_ID = ${item.bookId} AND A.CHANNEL_ID = ${item.channelId}
				</if>
			</foreach>
		</if>
		GROUP BY A.BOOK_ID, A.CHANNEL_ID
		ORDER BY
			LAST_MODIFIED_DATE DESC
		<if test="num != null">
			LIMIT ${num}
		</if>
	</select>

	<!-- 获取未创建社群码的书刊列表 -->
	<select id="listBook4CreateBookGroup" resultMap="bookMap" parameterType="map">
		SELECT
			A.BOOK_ID, A.CHANNEL_ID, A.ADVISER_ID, A.IS_MAIN_EDITOR, B.ISBN, B.BOOK_NAME, B.TYPE_CODE,
			B.AUTHOR, B.PUBLISH, B.PUBLISH_DATE, B.COVER_IMG, B.ORIGIN_NAME, B.BOOK_PRICE, B.ISSN, B.BOOK_NUM, B.SERIAL_NUMBER,
			A.TEMPLET_ID,A.SECOND_TEMPLET_ID, A.LAST_MODIFIED_DATE, A.BOOK_ADVISER_ID, CONCAT('BK',A.BOOK_ID) uniqueNumber,
			A.GRA_LABEL_ID,A.SUB_LABEL_ID,A.VER_LABEL_ID,A.AREA_LABEL_ID
		FROM
			BOOK_ADVISER A
	    INNER JOIN BOOK B ON A.BOOK_ID = B.BOOK_ID AND B.IS_DELETE = 0
        LEFT JOIN book_group G on G.BOOK_ID = A.BOOK_ID AND G.CHANNEL_ID = A.CHANNEL_ID AND G.CREATE_USER = A.ADVISER_ID AND G.IS_DELETE = 0
		WHERE
            A.ADVISER_ID = #{adviserId} AND A.IS_DELETE = 0 AND G.ID IS NULL
		<if test="name != null">
		  AND
			(B.BOOK_NAME LIKE CONCAT('%', #{name}, '%') OR  B.ISBN LIKE CONCAT(#{name},'%') OR CONCAT('BK',A.BOOK_ID) = #{name})
		</if>
		GROUP BY A.BOOK_ID, A.CHANNEL_ID
		ORDER BY
			A.LAST_MODIFIED_DATE DESC
	</select>

	<!-- 获取社群书列表(编辑) -->
	<select id="listBookGroup4Adviser" resultMap="bookMap" parameterType="map">
		SELECT
			A.BOOK_ID, A.CHANNEL_ID, A.ADVISER_ID, A.IS_MAIN_EDITOR, T.TYPE_CODE, T.TYPE_NAME, B.ISBN, B.BOOK_NAME, B.REMARK,
			B.AUTHOR, B.PUBLISH, B.PUBLISH_DATE, B.COVER_IMG, B.ORIGIN_NAME, B.BOOK_PRICE, B.ISSN, B.BOOK_NUM, B.SERIAL_NUMBER,
			IF(ISNULL(BF.BOOK_FUND_ID),0,1) IS_FUND_SUPPORT, A.TEMPLET_ID, A.BOOK_ADVISER_ID, CONCAT('BK',A.BOOK_ID) uniqueNumber,
            G.update_time LAST_MODIFIED_DATE, G.create_time CREATED_DATE, G.id BOOK_GROUP_ID, G.group_qrcode_url groupQrcodeUrl,
            G.group_qrcode_name groupQrcodeName, G.dep_label_id depLabelId, G.join_group_type, G.book_group_cipher, G.short_book_name
			, if(IFNULL(case G.join_group_type
			WHEN 1 then bk.id
			WHEN 2 THEN bgs.id
			WHEN 3 THEN srk.id
			else null END,0)=0,0,1)as has_serve, A.IS_PRINT isPrint
		FROM
            book_group G
        LEFT JOIN BOOK_ADVISER A ON G.BOOK_ID = A.BOOK_ID AND G.CHANNEL_ID = A.CHANNEL_ID AND G.CREATE_USER = A.ADVISER_ID
        LEFT JOIN BOOK B ON A.BOOK_ID = B.BOOK_ID
		LEFT JOIN BOOK_TYPE T ON B.TYPE_CODE = T.TYPE_CODE
		LEFT JOIN BOOK_FUND BF ON BF.BOOK_ID = A.BOOK_ID AND BF.END_TIME <![CDATA[ > ]]> NOW() AND BF.START_TIME <![CDATA[ < ]]> NOW()
		LEFT JOIN BOOK_GROUP_SERVE bgs on G.id=bgs.book_group_id
		LEFT JOIN BOOK_KEYWORD bk on G.id=bk.book_group_id and  bk.is_delete=0
		LEFT JOIN self_robot_keyword srk ON G.id = srk.book_group_id
		WHERE
            G.create_user = #{adviserId} AND G.is_delete = 0
        AND (
            A.BOOK_ADVISER_ID IS NULL
            OR A.IS_DELETE = 0
        )
        AND (
            B.BOOK_ID IS NULL
            OR B.IS_DELETE = 0
        )
        <if test="bookId != null">
		AND
			A.BOOK_ID = #{bookId}
		</if>
		<if test="typeCode != null">
		AND
			B.TYPE_CODE = #{typeCode}
		</if>
		<if test="channelId != null">
		AND
			A.CHANNEL_ID = #{channelId}
		</if>
		<if test="bookName != null">
		AND
			B.BOOK_NAME LIKE CONCAT('%', #{bookName}, '%')
		</if>
		<if test="isbn != null">
		AND
			B.ISBN LIKE CONCAT(#{isbn},'%')
		</if>
		<if test="name != null">
		  AND
			(B.BOOK_NAME LIKE CONCAT('%', #{name}, '%') OR G.group_qrcode_name LIKE CONCAT('%', #{name}, '%') OR  B.ISBN LIKE CONCAT(#{name},'%') OR CONCAT('BK',A.BOOK_ID) = #{name}
			OR G.short_book_name LIKE CONCAT('%', #{name}, '%'))
		</if>
		<if test="isMainEditor != null">
		  AND
			A.IS_MAIN_EDITOR = 1
		</if>
		<if test="isFundSupport != null">
		  AND
		  	BF.BOOK_FUND_ID IS NOT NULL
		</if>
        <if test="startDate != null and endDate != null">
            AND G.create_time BETWEEN #{startDate} AND #{endDate}
        </if>
		<if test="joinGroupTypes !=null and joinGroupTypes.size()>0">
			AND G.join_group_type in
			<foreach collection="joinGroupTypes" item="item" index="index" open="(" separator="," close=")">
				${item}
			</foreach>
		</if>
		GROUP BY G.id
		<if test="hasServe!=null and hasServe==true">
			having has_serve=1
		</if>
		<if test="hasServe!=null and hasServe==false">
			having has_serve=0
		</if>
		<if test="isPrint != null">
            		AND A.IS_PRINT = #{isPrint}
        	</if>
		ORDER BY
            G.UPDATE_TIME DESC
	</select>

	<select id="listBookGroup4Channel" resultType="com.pcloud.book.group.vo.ListBook4ChannelVO" parameterType="map">
		SELECT b.BOOK_ID bookId,b.COVER_IMG coverImg, bg.id bookGroupId ,b.ISBN isbn,b.BOOK_NAME bookName,CONCAT('BK',b.BOOK_ID) bookNumber FROM `book_group` bg
		LEFT JOIN book b ON bg.book_id = b.BOOK_ID
		LEFT JOIN book_fund bf ON bg.book_id = bf.BOOK_ID AND bf.END_TIME <![CDATA[ > ]]> NOW() AND bf.START_TIME <![CDATA[ < ]]> NOW()
		LEFT JOIN book_group_classify bgc ON bg.id = bgc.book_group_id
		LEFT JOIN book_adviser ba ON b.BOOK_ID =ba.book_id
		WHERE bg.channel_id = #{channelId} and ba.IS_PRINT=1
		<if test="keywords != null">
			AND (b.ISBN like CONCAT('%',#{keywords},'%') OR b.BOOK_NAME like CONCAT('%',#{keywords},'%') OR bg.group_qrcode_name like CONCAT('%',#{keywords},'%'))
		</if>
		<if test="isFundBook != null and isFundBook == 1">
			AND bf.BOOK_FUND_ID IS NOT NULL
		</if>
		<if test="startTime != null ">
			AND bg.create_time &gt;= #{startTime}
		</if>
		<if test="endTime != null ">
			AND bg.create_time &lt;=  #{endTime}
		</if>
		AND bgc.id is not null
		AND b.BOOK_ID is not null
        AND b.IS_DELETE = 0
        AND bg.IS_DELETE = 0
		AND bgc.is_delete = 0
		AND b.BOOK_NAME NOT LIKE CONCAT('%','红榜','%')
		AND CHAR_LENGTH(b.BOOK_NAME)>=8
		GROUP BY b.BOOK_ID
		ORDER BY bg.create_time DESC
	</select>

	<select id="getBookGroupInfo4Channel" resultType="com.pcloud.book.group.vo.ListBook4ChannelVO" parameterType="map">
		SELECT b.BOOK_ID bookId,b.COVER_IMG coverImg, bg.id bookGroupId ,b.ISBN isbn,b.BOOK_NAME bookName,CONCAT('BK',b.BOOK_ID) bookNumber FROM `book_group` bg
		LEFT JOIN book b ON bg.book_id = b.BOOK_ID
		LEFT JOIN book_fund bf ON bg.book_id = bf.BOOK_ID
		LEFT JOIN book_group_classify bgc ON bg.id = bgc.book_group_id
		WHERE bg.id in
		<foreach collection="bookGroupIds" index="i" item="item" open="(" separator="," close=")">
			${item}
		</foreach>
		AND b.BOOK_ID is not null
        AND b.IS_DELETE = 0
        AND bg.IS_DELETE = 0
		GROUP BY b.BOOK_ID
	</select>

	<!-- 获取社群书列表(编辑) -->
	<select id="listSimpleBookGroup4Adviser" resultMap="bookMap" parameterType="map">
        SELECT
            A.BOOK_ID,
            A.CHANNEL_ID,
            A.ADVISER_ID,
            B.ISBN,
            B.BOOK_NAME,
            B.COVER_IMG,
			B.SERIAL_NUMBER,
            CONCAT('BK', A.BOOK_ID) uniqueNumber,
			G.ID book_group_id
        FROM
            BOOK_ADVISER A
        INNER JOIN book_group G ON G.BOOK_ID = A.BOOK_ID
        AND G.CHANNEL_ID = A.CHANNEL_ID
        AND G.CREATE_USER = A.ADVISER_ID
        AND G.IS_DELETE = 0
        INNER JOIN BOOK B ON A.BOOK_ID = B.BOOK_ID
        AND B.IS_DELETE = 0
        WHERE
            A.ADVISER_ID = #{adviserId}
        AND A.IS_DELETE = 0
        <if test="name != null">
            AND
            (B.BOOK_NAME LIKE CONCAT('%', #{name}, '%') OR G.group_qrcode_name LIKE CONCAT('%', #{name}, '%') OR  B.ISBN LIKE CONCAT(#{name},'%') OR CONCAT('BK',A.BOOK_ID) = #{name})
        </if>
        GROUP BY
            A.BOOK_ID,
            A.CHANNEL_ID
        ORDER BY
            G.UPDATE_TIME DESC
	</select>

	<!-- 获取书籍列表(编辑) -->
	<select id="listBook4Adviser" resultMap="bookMap" parameterType="map">
        SELECT
            a.BOOK_ID,
            a.CHANNEL_ID,
            a.ADVISER_ID,
            a.IS_MAIN_EDITOR,
            b.ISBN,
            b.BOOK_NAME,
            b.REMARK,
            b.AUTHOR,
            b.PUBLISH,
            b.PUBLISH_DATE,
            b.COVER_IMG,
            b.ORIGIN_NAME,
            b.BOOK_PRICE,
            b.ISSN,
            b.BOOK_NUM,
            b.SERIAL_NUMBER,
            a.TEMPLET_ID,
            a.CREATED_DATE,
            a.LAST_MODIFIED_DATE,
            a.BOOK_ADVISER_ID,
        IF (
            adp.is_open = 0
            OR (
                adp.is_open IS NULL
                AND ap.is_open = 0
            ),
            0,
            1
        ) isOpen
        FROM
            BOOK_ADVISER a
        INNER JOIN BOOK b ON a.BOOK_ID = b.BOOK_ID
        AND a.ADVISER_ID = #{adviserId}
        AND a.IS_DELETE = 0
        AND b.IS_DELETE = 0
        LEFT JOIN advertising_agent_permission ap ON ap.agent_id = #{agentId}
        LEFT JOIN advertising_adviser_permission adp ON adp.adviser_id = a.ADVISER_ID
        AND adp.channel_id = a.CHANNEL_ID
        AND adp.book_id = a.BOOK_ID
        WHERE
            a.IS_MAIN_EDITOR = 1
        <if test="name != null">
            AND (b.BOOK_NAME LIKE CONCAT('%', #{name}, '%') OR  b.ISBN LIKE CONCAT(#{name},'%'))
        </if>
        GROUP BY
            a.BOOK_ID,
            a.CHANNEL_ID
        ORDER BY
            a.LAST_MODIFIED_DATE DESC
	</select>

	<!-- 获取书籍列表(编辑) -->
	<select id="listBook4AdvertisingSpace" resultMap="bookMap" parameterType="map">
        SELECT
            a.BOOK_ID,
            a.CHANNEL_ID,
            a.ADVISER_ID,
            db.agent_id agentId,
            b.ISBN,
            b.BOOK_NAME,
            b.COVER_IMG,
            db.distribution_time distributionTime,
        IF (
            adp.is_open = 0
            OR (
                adp.is_open IS NULL
                AND ap.is_open = 0
            ),
            0,
            1
        ) isOpen
        FROM
            BOOK_ADVISER a
        INNER JOIN BOOK b ON a.BOOK_ID = b.BOOK_ID
        AND a.IS_DELETE = 0
        AND b.IS_DELETE = 0
        INNER JOIN advertising_distribution_book db ON db.book_id = a.BOOK_ID
        AND db.channel_id = a.CHANNEL_ID
        AND db.adviser_id = a.ADVISER_ID
        AND db.ad_id = #{adId}
        LEFT JOIN advertising_agent_permission ap ON ap.agent_id = db.agent_id
        LEFT JOIN advertising_adviser_permission adp ON adp.adviser_id = a.ADVISER_ID
        AND adp.channel_id = a.CHANNEL_ID
        AND adp.book_id = a.BOOK_ID
        WHERE
            1 = 1
        <if test="name != null">
            AND (b.BOOK_NAME LIKE CONCAT('%', #{name}, '%') OR  b.ISBN LIKE CONCAT(#{name},'%'))
        </if>
        GROUP BY
            a.BOOK_ID,
            a.CHANNEL_ID
        ORDER BY
            db.distribution_time DESC
	</select>

	<!-- 获取书籍列表(编辑) -->
	<select id="advertisingBookList" resultMap="bookMap" parameterType="map">
        SELECT
            a.BOOK_ID,
            a.CHANNEL_ID,
            a.ADVISER_ID,
            db.agent_id agentId,
            b.ISBN,
            b.BOOK_NAME,
            b.COVER_IMG,
            count(DISTINCT db.id) advertisingSpaceNum,
            IF (
                adp.is_open = 0
                OR (
                    adp.is_open IS NULL
                    AND ap.is_open = 0
                ),
                0,
                1
            ) isOpen
        FROM
            BOOK_ADVISER a
        INNER JOIN BOOK b ON a.BOOK_ID = b.BOOK_ID
        AND a.IS_DELETE = 0
        AND b.IS_DELETE = 0
        INNER JOIN advertising_distribution_book db ON db.book_id = a.BOOK_ID
        AND db.channel_id = a.CHANNEL_ID
        AND db.adviser_id = a.ADVISER_ID
        AND db.qrcode_id IS NULL
        LEFT JOIN advertising_agent_permission ap ON ap.agent_id = db.agent_id
        LEFT JOIN advertising_adviser_permission adp ON adp.adviser_id = a.ADVISER_ID
        AND adp.channel_id = a.CHANNEL_ID
        AND adp.book_id = a.BOOK_ID
        WHERE
            1 = 1
        <if test="name != null">
            AND (b.BOOK_NAME LIKE CONCAT('%', #{name}, '%') OR  b.ISBN LIKE CONCAT(#{name},'%'))
        </if>
        GROUP BY
            a.BOOK_ID,
            a.CHANNEL_ID
        ORDER BY
            db.id ASC
	</select>

	<!-- 获取书籍列表(平台) -->
	<select id="listBook4Platform" resultMap="bookMap" parameterType="map">
		SELECT
		a.BOOK_ID,
		a.CHANNEL_ID,
		a.ADVISER_ID,
		a.IS_MAIN_EDITOR,
		b.ISBN,
		b.BOOK_NAME,
		b.REMARK,
		b.AUTHOR,
		b.PUBLISH,
		b.PUBLISH_DATE,
		b.COVER_IMG,
		b.ORIGIN_NAME,
		b.BOOK_PRICE,
		b.ISSN,
		b.BOOK_NUM,
		b.SERIAL_NUMBER,
		a.TEMPLET_ID,
		a.CREATED_DATE,
		a.LAST_MODIFIED_DATE,
		a.BOOK_ADVISER_ID
		FROM
		BOOK_ADVISER a
		INNER JOIN BOOK b ON a.BOOK_ID = b.BOOK_ID
		AND a.IS_DELETE = 0
		AND b.IS_DELETE = 0
		<if test="bookGroupOnly != null and bookGroupOnly == 1">
			INNER JOIN book_group g ON g.BOOK_ID = a.BOOK_ID
			AND g.CHANNEL_ID = a.CHANNEL_ID
			AND g.CREATE_USER = a.ADVISER_ID
			AND g.IS_DELETE = 0
		</if>
		LEFT JOIN advertising_adviser_permission adp ON a.BOOK_ID = adp.book_id
		AND a.ADVISER_ID = adp.adviser_id
		AND a.CHANNEL_ID = adp.channel_id
		WHERE
		a.IS_MAIN_EDITOR = 1
		AND (
		adp.is_open IS NULL
		OR adp.is_open = 1
		)
        <if test="adviserId != null">
            AND a.ADVISER_ID = #{adviserId}
        </if>
        <if test="name != null">
            AND (b.BOOK_NAME LIKE CONCAT('%', #{name}, '%') OR  b.ISBN LIKE CONCAT(#{name},'%'))
        </if>
        GROUP BY
            a.BOOK_ID,
            a.CHANNEL_ID
        <if test="bookGroupOnly != null and bookGroupOnly == 1">
            ORDER BY
              g.create_time DESC
        </if>
        <if test="bookGroupOnly == null or bookGroupOnly != 1">
            ORDER BY
              a.CREATED_DATE DESC
        </if>
	</select>

	<!-- 获取书籍列表(编辑) -->
	<select id="listBookIdAndChannelId4Adviser" resultMap="bookMap" parameterType="map">
        SELECT
            a.BOOK_ID,
            a.CHANNEL_ID
        FROM
            BOOK_ADVISER a
        INNER JOIN BOOK b ON a.BOOK_ID = b.BOOK_ID
        AND a.ADVISER_ID = #{adviserId}
        AND a.IS_DELETE = 0
        AND b.IS_DELETE = 0
        WHERE
            a.IS_MAIN_EDITOR = 1
        GROUP BY
            a.BOOK_ID,
            a.CHANNEL_ID
	</select>

		<!-- 获取书籍列表(编辑) -->
	<select id="listBook" resultMap="bookMap" parameterType="map">
		SELECT 
			A.BOOK_ID, A.CHANNEL_ID, A.ADVISER_ID, A.IS_MAIN_EDITOR, T.TYPE_CODE, T.TYPE_NAME, B.ISBN, B.BOOK_NAME, B.REMARK, 
			B.AUTHOR, B.PUBLISH, B.PUBLISH_DATE, B.COVER_IMG, B.ORIGIN_NAME, B.BOOK_PRICE, B.ISSN, B.BOOK_NUM, B.SERIAL_NUMBER,A.TEMPLET_ID
		FROM 
			BOOK_ADVISER A
		INNER JOIN
			BOOK B
		ON
			A.BOOK_ID = B.BOOK_ID AND A.ADVISER_ID = #{adviserId} AND A.IS_DELETE = 0 AND B.IS_DELETE = 0
		LEFT JOIN
			BOOK_TYPE T
		ON
			B.TYPE_CODE = T.TYPE_CODE 	
		ORDER BY
			A.CREATED_DATE DESC
		LIMIT #{number}
	</select>
	
	<!-- 根据名称获取ID -->
	<select id="getIdsByName" resultType="long" parameterType="string">
		SELECT 
			BOOK_ID
		FROM 
			BOOK
		WHERE
			BOOK_NAME LIKE '%${_parameter}%'
	</select>
	
	<!-- 根据名称获取ID -->
	<select id="getIdsByNameMap" resultType="long" parameterType="map">
		SELECT 
			A.BOOK_ID
		FROM 
			BOOK_ADVISER A
		INNER JOIN
			BOOK B
		ON
			A.BOOK_ID = B.BOOK_ID AND A.ADVISER_ID = #{adviserId} AND A.IS_DELETE = 0 AND B.IS_DELETE = 0
		WHERE 
			B.BOOK_NAME LIKE CONCAT('%',#{bookName},'%')
	</select>
		
	<!-- 图书收益，获取书籍列表(编辑) -->
	<select id="getList4AdviserProfit" resultMap="bookMap" parameterType="map">
		SELECT 
			A.BOOK_ID, A.CHANNEL_ID, A.ADVISER_ID, B.BOOK_NAME, B.REMARK, B.COVER_IMG,B.ORIGIN_NAME,B.BOOK_PRICE,A.TEMPLET_ID
		FROM 
			BOOK_ADVISER A
		INNER JOIN
			BOOK B
		ON
			A.BOOK_ID = B.BOOK_ID AND A.ADVISER_ID = #{adviserId} AND A.IS_DELETE = 0 AND B.IS_DELETE = 0
		WHERE
			1=1
		<if test="bookName!=null">
		AND
			B.BOOK_NAME LIKE CONCAT('%',#{bookName},'%')
		</if>
		ORDER BY
			A.CREATED_DATE DESC
	</select>
	
	<!--  根据刊物序号获取刊物信息-->
	<select id="getBySerialNum" resultMap="bookMap" parameterType="string">
		SELECT 
			BOOK_ID, ISBN, TYPE_CODE, BOOK_NAME, AUTHOR,
			PUBLISH, PUBLISH_DATE, COVER_IMG, AD_IMG, REMARK,ORIGIN_NAME,BOOK_PRICE,ISSN,SERIAL_NUMBER
		FROM 
			BOOK
		WHERE
			SERIAL_NUMBER = #{_parameter}
	</select>
	
	<!--微信管理端获取期刊列表  -->
	<select id="getJournalPage4Adviser" resultMap="bookMap" parameterType="map">
		SELECT 
			A.BOOK_ID, A.CHANNEL_ID, A.ADVISER_ID,A.IS_MAIN_EDITOR,B.ISBN, B.BOOK_NAME, B.REMARK, 
			B.AUTHOR, B.PUBLISH, B.PUBLISH_DATE, B.COVER_IMG,B.ORIGIN_NAME,B.BOOK_PRICE, B.ISSN,B.SERIAL_NUMBER,A.TEMPLET_ID
		FROM 
			BOOK_ADVISER A
		INNER JOIN
			BOOK B
		ON
			A.BOOK_ID = B.BOOK_ID AND A.IS_DELETE = 0 AND B.IS_DELETE = 0
		WHERE
			A.IS_MAIN_EDITOR = 1
	    <if test="typeCode != null">
		AND B.TYPE_CODE = #{typeCode}
		</if>
		<if test="channelId !=null ">
		AND A.CHANNEL_ID = #{channelId}
		</if>
		<if test="isbn != null">
		AND B.ISBN = #{isbn}
		</if>
		ORDER BY B.SERIAL_NUMBER DESC
	</select>
	
	<!--微信客户端获取书刊列表  -->
	<select id="listPage4Wechat" resultMap="bookMap" parameterType="map">
		SELECT 
			A.BOOK_ID, A.CHANNEL_ID, A.ADVISER_ID,B.ISBN, B.BOOK_NAME, B.REMARK, 
			B.AUTHOR, B.PUBLISH, B.PUBLISH_DATE, B.COVER_IMG,B.ORIGIN_NAME,
			B.BOOK_PRICE, B.ISSN,B.SERIAL_NUMBER,B.TYPE_CODE,A.TEMPLET_ID
		FROM 
			BOOK_ADVISER A
		INNER JOIN
			BOOK B
		ON
			A.BOOK_ID = B.BOOK_ID AND A.IS_DELETE = 0 AND B.IS_DELETE = 0
		WHERE		
			A.CHANNEL_ID = #{channelId}
		AND 
		    A.IS_MAIN_EDITOR = 1
		ORDER BY A.CREATED_DATE DESC
	</select>
	
		<!-- 根据ISBN获取图书信息 -->
	<select id="getDetailByIsbn" resultMap="bookMap" parameterType="map">
		  SELECT B.BOOK_ID, B.ISBN, B.BOOK_NAME, B.AUTHOR, B.PUBLISH, 
			B.PUBLISH_DATE, B.COVER_IMG,
			(
				SELECT COUNT(1) FROM BOOK_APP WHERE BOOK_ID = B.BOOK_ID  AND IS_DELETE = 0		
			) APP_COUNT, 
			(
				SELECT COUNT(1) FROM BOOK_PRODUCT WHERE BOOK_ID = B.BOOK_ID 
			 ) PRODUCT_COUNT
		FROM 
			BOOK B
		WHERE
			B.ISBN = #{isbn}
			<choose>
			    <when test="serialNumber == null">
			        AND 
			        	(B.SERIAL_NUMBER IS NULL OR B.SERIAL_NUMBER = '')
			    </when>
			    <otherwise>
			        AND B.SERIAL_NUMBER = #{serialNumber}
			    </otherwise>
			</choose>
		LIMIT 1
	</select>
	
	<!-- 书刊列表-出版端 -->
	<select id="listPage4Agent" parameterType="map" resultMap="bookMap">
		SELECT 
			A.BOOK_ID, A.CHANNEL_ID, A.ADVISER_ID, A.IS_MAIN_EDITOR, T.TYPE_CODE, T.TYPE_NAME, B.ISBN, B.BOOK_NAME, B.REMARK, 
			B.AUTHOR, B.PUBLISH, B.PUBLISH_DATE, B.COVER_IMG, B.ORIGIN_NAME, B.BOOK_PRICE, B.ISSN, B.BOOK_NUM, B.SERIAL_NUMBER,
			IF(ISNULL(BF.BOOK_FUND_ID),0,1) IS_FUND_SUPPORT, A.TEMPLET_ID, A.IS_PRINT isPrint
		FROM 
			BOOK_ADVISER A
		INNER JOIN
			BOOK B
		ON
			A.BOOK_ID = B.BOOK_ID  AND A.IS_DELETE = 0 AND B.IS_DELETE = 0
		AND
		A.ADVISER_ID IN
		<foreach collection="agent4AdviserIds" index="i" item="item" open="(" separator="," close=")">
			${item}
		</foreach>
		AND A.IS_MAIN_EDITOR = 1
		LEFT JOIN
			BOOK_TYPE T
		ON
			B.TYPE_CODE = T.TYPE_CODE 
		LEFT JOIN 
			BOOK_FUND BF
		ON 
			BF.BOOK_ID = A.BOOK_ID AND BF.END_TIME <![CDATA[ > ]]> NOW()  AND BF.START_TIME <![CDATA[ < ]]> NOW()
		WHERE
		   1=1
		<if test="typeCode!=null">
		AND
			B.TYPE_CODE = #{typeCode}
		</if>
		<if test="channelId!=null">
		AND
			A.CHANNEL_ID = #{channelId}
		</if>
		<if test="bookName!=null">
		AND (B.BOOK_NAME LIKE CONCAT('%',#{bookName},'%')
			OR B.ISBN LIKE CONCAT(#{bookName},'%')
			<if test="adviserIds!=null and adviserIds.size()>0">
				OR
				A.ADVISER_ID in
				<foreach collection="adviserIds" item="item" open="(" separator="," close=")">
					${item}
				</foreach>
			</if>
			)
		</if>
		<if test="isbn!=null">
		AND
			B.ISBN LIKE CONCAT(#{isbn},'%')
		</if>
		<if test="templetId!=null">
			AND
			A.TEMPLET_ID = #{templetId}
		</if>
		<if test="secondTempletIds!=null and secondTempletIds.size()>0">
			AND
			A.SECOND_TEMPLET_ID in
			<foreach collection="secondTempletIds" item="item" open="(" separator="," close=")">
				${item}
			</foreach>
		</if>
		<if test="isFundSupport != null">
		  AND 
		  	BF.BOOK_FUND_ID IS NOT NULL
		</if>
		<if test="isPrint != null">
		  AND
		  	A.IS_PRINT = #{isPrint}
		</if>
		GROUP BY A.BOOK_ID, A.CHANNEL_ID
		ORDER BY
			A.CREATED_DATE DESC
	</select>
	
	<!-- 获取书籍列表(APP) -->
	<select id="listBook4App" resultMap="bookMap" parameterType="map">
		SELECT 
			A.BOOK_ID, A.CHANNEL_ID, A.ADVISER_ID, A.IS_MAIN_EDITOR,A.TEMPLET_ID, T.TYPE_CODE, T.TYPE_NAME, B.ISBN, B.BOOK_NAME, B.REMARK, 
			B.COVER_IMG, B.ISSN, B.SERIAL_NUMBER
		FROM 
			BOOK_ADVISER A 
		INNER JOIN
			BOOK B
		ON
			A.BOOK_ID = B.BOOK_ID AND A.ADVISER_ID = #{adviserId} AND A.IS_DELETE = 0 AND B.IS_DELETE = 0
		LEFT JOIN
			BOOK_TYPE T
		ON
			B.TYPE_CODE = T.TYPE_CODE 	
		<if test="bookName!=null">
			WHERE BOOK_NAME LIKE CONCAT('%',#{bookName},'%')
		</if>
		ORDER BY
			A.CREATED_DATE DESC 
	</select>
	
	<!--获取应用关联图书  -->
	<select id="listBookByAppId" resultMap="bookMap" parameterType="map">
		SELECT 
			A.BOOK_ID, A.CHANNEL_ID, A.ADVISER_ID,B.ISBN, B.BOOK_NAME,
			B.COVER_IMG,B.ISSN,B.SERIAL_NUMBER,B.TYPE_CODE
		FROM 
			(SELECT BOOK_ID, CHANNEL_ID, CREATED_USER AS ADVISER_ID  FROM BOOK_APP WHERE APP_ID = #{appId} AND CHANNEL_ID = #{channelId} AND IS_DELETE = 0) A
		INNER JOIN
			BOOK B
		ON
			A.BOOK_ID = B.BOOK_ID  AND B.IS_DELETE = 0
	</select>
	
	<!-- 书刊回收站列表（30天内删除的书刊列表） -->
	<select id="listPageDelete4Adviser" resultMap="bookMap" parameterType="map">
		SELECT 
			A.BOOK_ID, A.CHANNEL_ID, A.ADVISER_ID, A.IS_MAIN_EDITOR, T.TYPE_CODE, T.TYPE_NAME, B.ISBN, B.BOOK_NAME, B.REMARK, 
			B.AUTHOR, B.PUBLISH, B.PUBLISH_DATE, B.COVER_IMG, B.ORIGIN_NAME, B.BOOK_PRICE, B.ISSN, B.BOOK_NUM, B.SERIAL_NUMBER,
			IF(ISNULL(BF.BOOK_FUND_ID),0,1) IS_FUND_SUPPORT,A.TEMPLET_ID,A.LAST_MODIFIED_DATE
		FROM 
			BOOK_ADVISER A
		INNER JOIN
			BOOK B
		ON
			A.BOOK_ID = B.BOOK_ID AND A.ADVISER_ID = #{adviserId}  AND A.IS_DELETE = 1 AND B.IS_DELETE = 0
		LEFT JOIN
			BOOK_TYPE T
		ON
			B.TYPE_CODE = T.TYPE_CODE 
		LEFT JOIN 
			BOOK_FUND BF
		ON 
			BF.BOOK_ID = A.BOOK_ID AND BF.END_TIME <![CDATA[ > ]]> NOW()  AND BF.START_TIME <![CDATA[ < ]]> NOW()
		GROUP BY A.BOOK_ID, A.CHANNEL_ID
		ORDER BY
			A.LAST_MODIFIED_DATE DESC
	</select>
	
	
	<!--  获取出版下除默认生成图书的所有图书总数-->
	<select id="getAllBookCount4Agent" parameterType="map" resultType="Integer">
		SELECT 
			COUNT(1)
		FROM 
			(SELECT BOOK_ID, ADVISER_ID, CHANNEL_ID FROM BOOK_ADVISER WHERE ADVISER_ID IN
			<foreach collection="adviserIds" index="i" item="item" open="(" separator="," close=")">
				${item}
			</foreach>
			 AND IS_DELETE = 0 
			<if test="bookId!=null">
			 AND BOOK_ID != #{bookId}
	     	</if>
			) A
		INNER JOIN
			BOOK B
		ON
			A.BOOK_ID = B.BOOK_ID  AND B.IS_DELETE = 0
	</select>
	
	<!--  获取出版下除默认生成图书的所有图书-->
	<select id="getAllBook4Agent" parameterType="map" resultMap="bookMap">
		SELECT 
			A.BOOK_ID,A.ADVISER_ID,A.CHANNEL_ID,B.BOOK_NAME,B.ISBN,B.SERIAL_NUMBER
		FROM 
			(SELECT BOOK_ID, ADVISER_ID, CHANNEL_ID, CREATED_DATE FROM BOOK_ADVISER WHERE ADVISER_ID IN
			<foreach collection="adviserIds" index="i" item="item" open="(" separator="," close=")">
				${item}
			</foreach>
			 AND IS_DELETE = 0 
			<if test="bookId!=null">
			 AND BOOK_ID != #{bookId}
	     	</if>
			) A
		INNER JOIN
			BOOK B
		ON
			A.BOOK_ID = B.BOOK_ID AND B.IS_DELETE = 0
		ORDER BY A.CREATED_DATE
		<if test="currentPage!=null">
		   limit #{currentPage},#{numPerPage}
		</if>
	</select>
	
	<select id="listByIsbn" resultMap="bookMap" parameterType="map">
		SELECT 
			BOOK_ID, ISBN, BOOK_NAME,TYPE_CODE,AUTHOR,PUBLISH,PUBLISH_DATE,SERIAL_NUMBER,
			COVER_IMG
		FROM 
			BOOK
		WHERE
			ISBN = #{isbn}
	</select>
	
	<!-- 图书列表（平台端） -->
	<select id="listBookClassify" resultMap="bookMap" parameterType="map">
		SELECT
			b.BOOK_ID, b.BOOK_NAME, b.COVER_IMG, b.ISBN,b.SERIAL_NUMBER, b.PUBLISH,
			ba.ADVISER_ID, ba.TEMPLET_ID, ba.CHANNEL_ID, ba.CREATED_DATE,
			bt.TYPE_NAME, ba.IS_PRINT isPrint
		FROM
			BOOK_ADVISER ba
		JOIN
			BOOK b
		ON
			b.BOOK_ID = ba.BOOK_ID
		LEFT JOIN
			BOOK_TYPE bt
		ON
			b.TYPE_CODE = bt.TYPE_CODE
		WHERE
			ba.IS_DELETE = 0
		AND
			b.IS_DELETE = 0
		<if test="typeCode!=null">
			AND b.TYPE_CODE = #{typeCode, jdbcType=VARCHAR}
	    </if>
		<if test="templetId!=null">
			AND ba.TEMPLET_ID = #{templetId}
		</if>
		<if test="secondTempletIds!=null and secondTempletIds.size()>0">
			AND
			ba.SECOND_TEMPLET_ID in
			<foreach collection="secondTempletIds" item="item" open="(" separator="," close=")">
				${item}
			</foreach>
		</if>
		<if test="startDate!=null">
			AND ba.CREATED_DATE  <![CDATA[ > ]]> #{startDate} AND ba.CREATED_DATE <![CDATA[ < ]]> #{endDate}
	    </if>
		<if test="bookName!=null">
			AND (
				(b.BOOK_NAME LIKE CONCAT('%', #{bookName},'%') or b.ISBN LIKE CONCAT('%', #{bookName},'%')
                    or b.SERIAL_NUMBER LIKE CONCAT('%', #{bookName},'%')
				<if test="bookNameList != null and bookNameList.size > 0">
					<foreach collection="bookNameList" item="item" open=" " separator=" " close=" ">
						OR b.BOOK_NAME LIKE CONCAT('%', '${item}','%')
					</foreach>
				</if>
				)
			)
	    </if>
		<if test="publish!=null">
			AND b.PUBLISH LIKE CONCAT('%', #{publish},'%')
		</if>
		<if test="isPrint != null">
			AND ba.IS_PRINT = #{isPrint}
		</if>
	</select>

	<select id="listBookClassifyCount" resultType="Long" parameterType="map">
		SELECT
		   count(1)
		FROM
		   BOOK_ADVISER ba
		 JOIN
			BOOK b
		 ON
			b.BOOK_ID = ba.BOOK_ID
		 LEFT JOIN
			BOOK_TYPE bt
			ON
			b.TYPE_CODE = bt.TYPE_CODE
		WHERE
		ba.IS_DELETE = 0
		<if test="typeCode!=null">
			AND b.TYPE_CODE = #{typeCode, jdbcType=VARCHAR}
		</if>
		<if test="templetId!=null">
			AND ba.TEMPLET_ID = #{templetId}
		</if>
		<if test="secondTempletIds!=null and secondTempletIds.size()>0">
			AND
			ba.SECOND_TEMPLET_ID in
			<foreach collection="secondTempletIds" item="item" open="(" separator="," close=")">
				${item}
			</foreach>
		</if>
		<if test="startDate!=null">
			AND ba.CREATED_DATE  <![CDATA[ > ]]> #{startDate} AND ba.CREATED_DATE <![CDATA[ < ]]> #{endDate}
		</if>
		<if test="bookName!=null">
			AND (
				(b.BOOK_NAME LIKE CONCAT('%', #{bookName},'%') or b.ISBN LIKE CONCAT('%', #{bookName},'%')
                    or b.SERIAL_NUMBER LIKE CONCAT('%', #{bookName},'%')
				<if test="bookNameList != null and bookNameList.size > 0">
					<foreach collection="bookNameList" item="item" open=" " separator=" " close=" ">
						OR b.BOOK_NAME LIKE CONCAT('%', '${item}','%')
					</foreach>
				</if>
				)
			)
	    </if>
		<if test="publish!=null">
			AND b.PUBLISH LIKE CONCAT('%', #{publish},'%')
		</if>
        <if test="isPrint != null">
            AND ba.IS_PRINT = #{isPrint}
        </if>
	</select>

	
	<!-- 获取平台下除默认生成图书的所有图书 -->
	<select id="getAllBook4Platform" resultMap="bookMap" parameterType="map">
		SELECT
			b.BOOK_ID, b.BOOK_NAME, b.COVER_IMG, b.ISBN, b.PUBLISH, 
			ba.ADVISER_ID, ba.TEMPLET_ID,ba.SECOND_TEMPLET_ID, ba.CHANNEL_ID, ba.CREATED_DATE,
			bt.TYPE_NAME
		FROM
			BOOK_ADVISER ba
		LEFT JOIN
			BOOK b
		ON
			ba.BOOK_ID = b.BOOK_ID
		LEFT JOIN
			BOOK_TYPE bt
		ON
			b.TYPE_CODE = bt.TYPE_CODE
		WHERE
			ba.IS_DELETE = 0
		AND
			b.IS_DELETE = 0
		<if test="bookId!=null">
			 AND ba.BOOK_ID != #{bookId}
		</if>
		<if test="currentPage!=null">
		   limit #{currentPage},#{numPerPage}
		</if>
	</select>
	
	<!-- 获取图书总数除RAYS现代纸书(平台端) -->
	<select id="getAllBookCount4Platform" resultType="Integer" parameterType="map">
		SELECT 
			COUNT(1)
		FROM 
			(SELECT BOOK_ID, ADVISER_ID, CHANNEL_ID FROM BOOK_ADVISER 
			 WHERE IS_DELETE = 0 
			<if test="bookId!=null">
			 AND BOOK_ID != #{bookId}
	     	</if>
			) A
		INNER JOIN
			BOOK B
		ON
			A.BOOK_ID = B.BOOK_ID 
		WHERE
			B.IS_DELETE = 0
	</select>
	
	
	<!-- 获取编辑模板 -->
	<select id="getTempletId" resultType="long" parameterType="map">
		SELECT 
			TEMPLET_ID
		FROM 
			BOOK_ADVISER
		WHERE
			BOOK_ID = #{bookId, jdbcType=BIGINT} AND CHANNEL_ID = #{channelId} AND CREATED_USER = #{adviserId}
	</select>
	
	
	<!-- 修改书籍信息-->
	<update id="updateByAdviser" parameterType="book" flushCache="true">
		UPDATE BOOK
		SET
		    <if test="bookName != null">
			BOOK_NAME = #{bookName, jdbcType=VARCHAR}, 
			</if>
			<if test="bookPrice != null">
			BOOK_PRICE = #{bookPrice, jdbcType=VARCHAR},
			</if>
			<if test="author != null">
			AUTHOR = #{author, jdbcType=VARCHAR}, 
			</if>
			<if test="publish != null">
			PUBLISH = #{publish, jdbcType=VARCHAR}, 
			</if>
			<if test="coverImg != null">
			COVER_IMG = #{coverImg, jdbcType=VARCHAR}, 
			</if>
			<if test="lastModifiedUser != null">
			LAST_MODIFIED_USER = #{lastModifiedUser, jdbcType=BIGINT}, 
			</if>
			<if test="serialNumber != null">
		    SERIAL_NUMBER = #{serialNumber, jdbcType=VARCHAR},
            </if>
            LAST_MODIFIED_DATE =  NOW(),
			VERSION = VERSION + 1
		WHERE
			BOOK_ID = #{bookId, jdbcType=BIGINT}
	</update>


	<update id="updateTimeById" parameterType="map">
		UPDATE BOOK SET LAST_MODIFIED_DATE =  NOW() WHERE BOOK_ID = #{bookId, jdbcType=BIGINT}
	</update>

	<update id="updateTimeByIds" parameterType="map">
		UPDATE BOOK_ADVISER SET LAST_MODIFIED_DATE =  NOW() WHERE
		ADVISER_ID = #{adviserId}
		<if test="channelId != null">
			and  CHANNEL_ID = #{channelId}
		</if>
		<if test="bookId != null">
			AND  BOOK_ID = #{bookId}
		</if>
		<if test="bookIds != null">
			AND BOOK_ID in
			<foreach collection="bookIds" item="item" open="(" separator="," close=")">
				#{item}
			</foreach>
		</if>
	</update>

	<!--PC端-获取书刊分析相关信息-->
	<select id="getBookAnalysis4PC" parameterType="map" resultType="bookAnlysicsDto">
		SELECT
			ba.BOOK_ID bookId,
			b.BOOK_NAME bookName,
			b.ISBN isbn,
			ba.ADVISER_ID adviserId,
			ba.IS_MAIN_EDITOR isMainEditor,
			ba.CHANNEL_ID channelId,
			ba.CREATED_DATE createdDate,
			ba.TEMPLET_ID templetId,
			IF (
			ISNULL(BF.BOOK_FUND_ID),
			0,
			1
			) isFundSupport
		FROM
			`book_adviser` ba
		LEFT JOIN book b ON ba.BOOK_ID = b.BOOK_ID
		LEFT JOIN book_fund bf ON ba.BOOK_ID = bf.BOOK_ID
			AND
			<choose>
				<when test="statisTime != null">
					bf.END_TIME <![CDATA[ > ]]> DATE_FORMAT(#{statisTime},'yyyy-MM')
					AND bf.START_TIME <![CDATA[ < ]]> DATE_FORMAT(#{statisTime},'yyyy-MM')
				</when>
				<otherwise>
					BF.END_TIME <![CDATA[ > ]]> NOW() AND BF.START_TIME <![CDATA[ < ]]> NOW()
				</otherwise>
			</choose>
		WHERE
			ba.IS_DELETE = 0
		AND
			b.IS_DELETE = 0
		<if test="bookName != null">
			AND (b.BOOK_NAME LIKE CONCAT('%',#{bookName},'%') OR b.ISBN LIKE CONCAT('%',#{bookName},'%'))
		</if>
		<if test="templetId != null">
			AND ba.TEMPLET_ID = #{templetId}
		</if>
		<!--<if test="templetIds != null">
			AND ba.TEMPLET_ID in
			<foreach collection="templetIds" item="templetId" open="(" separator="," close=")">
				${templetId}
			</foreach>
		</if>-->
		<if test="channelIds != null">
			AND
				ba.CHANNEL_ID in
			<foreach collection="channelIds" item="channelId" open="(" separator="," close=")">
				${channelId}
			</foreach>
		</if>
		<if test="adviserIds != null">
			AND
			ba.ADVISER_ID in
			<foreach collection="adviserIds" item="adviserId" open="(" separator="," close=")">
				${adviserId}
			</foreach>
		</if>
		<if test="isFundSupport != null">
			AND
			 bf.BOOK_FUND_ID IS NOT NULL
		</if>
		ORDER BY
			ba.CREATED_DATE DESC
	</select>


	<select id="getBookInfoBy" parameterType="map" resultType="bookInfoAnalysicsDTO">
		SELECT
			ba.BOOK_ID bookId,
			b.BOOK_NAME bookName,
			b.ISBN isbn,
			ba.ADVISER_ID adviserId,
			ba.CHANNEL_ID channelId,
			ba.CREATED_DATE createdDate,
			ba.TEMPLET_ID templetId,
			IF (
			ISNULL(BF.BOOK_FUND_ID),
			0,
			1
			) isFundSupport
		FROM
			`book_adviser` ba
		LEFT JOIN book b ON ba.BOOK_ID = b.BOOK_ID
		LEFT JOIN book_fund bf ON ba.BOOK_ID = bf.BOOK_ID
		AND
		<choose>
			<when test="statisTime != null">
				DATE_FORMAT(bf.START_TIME ,'%Y-%m') <![CDATA[ <= ]]> #{statisTime}
			</when>
			<otherwise>
				bf.START_TIME <![CDATA[ < ]]> NOW()
			</otherwise>
		</choose>
		WHERE
		ba.IS_DELETE = 0
		AND
		b.IS_DELETE = 0
		<if test="channelIds != null">
			AND
			ba.CHANNEL_ID in
			<foreach collection="channelIds" item="item" open="(" separator="," close=")">
				#{item}
			</foreach>
		</if>
		<if test="adviserIds != null">
			AND
			ba.ADVISER_ID in
			<foreach collection="adviserIds" item="item" open="(" separator="," close=")">
				#{item}
			</foreach>
		</if>
		<if test="bookIds != null">
			AND
			ba.BOOK_ID in
			<foreach collection="bookIds" item="item" open="(" separator="," close=")">
				#{item}
			</foreach>
		</if>
	</select>


	<select id="getBookAnalysis" parameterType="map" resultType="BookInfo4AnlysicsDTO">
		SELECT
			b.BOOK_ID bookId,
			b.BOOK_NAME bookName,
			b.ISBN isbn,
			b.CREATED_DATE createdDate,
			IF (
			ISNULL(bf.BOOK_FUND_ID),
			0,
			1
			) isFundSupport
		FROM
			book b
		LEFT JOIN book_fund bf ON b.BOOK_ID = bf.BOOK_ID
		AND
		<choose>
			<when test="monthDate != null">
				DATE_FORMAT(bf.START_TIME ,'%Y-%m') <![CDATA[ <= ]]> #{monthDate}
			</when>
			<otherwise>
				bf.START_TIME <![CDATA[ < ]]> NOW()
			</otherwise>
		</choose>
		WHERE
			b.IS_DELETE = 0
		AND
			b.BOOK_ID in
		<foreach collection="bookIds" item="item" open="(" separator="," close=")">
			#{item}
		</foreach>
		group by b.BOOK_ID
	</select>

	<select id="getBookBaseInfo" parameterType="map" resultMap="bookMap">
		SELECT
			B.BOOK_ID,
			B.TYPE_CODE,
			B.ISBN,
			B.BOOK_NAME,
			B.AUTHOR,
			B.PUBLISH,
			B.COVER_IMG,
			B.BOOK_PRICE,
			B.SERIAL_NUMBER,
			B.AD_IMG
		FROM
			book b
		WHERE
			b.BOOK_ID IN (
		SELECT
			ba.BOOK_ID
		FROM
			book_adviser ba
		GROUP BY
			ba.BOOK_ID
		)
		AND B.IS_DELETE = 0
		<if test="name!=null">
			AND
			(B.BOOK_NAME LIKE CONCAT('%', #{name},'%') OR B.ISBN LIKE CONCAT(#{name},'%'))
		</if>
	</select>

	<select id="listNoAuthBook" resultType="noAuthBookVO" parameterType="map">
		SELECT
		A.BOOK_ID as bookId,
		A.CHANNEL_ID as channelId,
		A.ADVISER_ID as adviserId,
		B.TYPE_CODE as typeCode,
		T.TYPE_NAME as typeName,
		B.ISBN,
		B.BOOK_NAME as bookName,
	    B.COVER_IMG coverImg,
		B.SERIAL_NUMBER serialNumber,
		A.TEMPLET_ID templetId
		FROM
		BOOK_ADVISER A
		INNER JOIN BOOK B ON A.BOOK_ID = B.BOOK_ID AND A.ADVISER_ID = #{adviserId} AND A.IS_DELETE = 0 AND B.IS_DELETE = 0
		LEFT JOIN
		BOOK_TYPE T ON B.TYPE_CODE = T.TYPE_CODE
		<if test = "isFundSupport!=null">
		LEFT JOIN
			BOOK_FUND BF ON BF.BOOK_ID = A.BOOK_ID AND BF.END_TIME <![CDATA[ > ]]> NOW()  AND BF.START_TIME <![CDATA[ < ]]> NOW()
		</if>
		LEFT JOIN book_auth_info I ON I.BOOK_ID = B.BOOK_ID AND I.ADVISER_ID = #{adviserId} AND I.is_paper_book = 1
		WHERE
		I.ID IS NULL
		<if test="typeCode!=null">
			AND
			B.TYPE_CODE = #{typeCode}
		</if>
		<if test="channelId!=null">
			AND
			A.CHANNEL_ID = #{channelId}
		</if>
		<if test="name != null">
			AND
			(B.BOOK_NAME LIKE CONCAT('%', #{name}, '%') OR  B.ISBN LIKE CONCAT(#{name},'%'))
		</if>
		<if test="isMainEditor != null">
			AND
			A.IS_MAIN_EDITOR = 1
		</if>
		<if test="isFundSupport != null">
			AND
			BF.BOOK_FUND_ID IS NOT NULL
		</if>
		<if test="templetId != null">
			AND
			A.TEMPLET_ID = #{templetId}
		</if>
		<if test="secondTempletIds!=null and secondTempletIds.size()>0">
			AND
			A.SECOND_TEMPLET_ID in
			<foreach collection="secondTempletIds" item="item" open="(" separator="," close=")">
				${item}
			</foreach>
		</if>
		GROUP BY A.BOOK_ID, A.CHANNEL_ID
		ORDER BY
		A.LAST_MODIFIED_DATE DESC , A.BOOK_ADVISER_ID DESC
	</select>

    <!-- 根据名称或ISBN获取ID -->
    <select id="getIdsByNameOrISBN" resultType="long" parameterType="map">
		SELECT
			A.BOOK_ID
		FROM
			BOOK_ADVISER A
		INNER JOIN
			BOOK B
		ON
			A.BOOK_ID = B.BOOK_ID AND A.ADVISER_ID = #{adviserId} AND A.IS_DELETE = 0 AND B.IS_DELETE = 0
		WHERE
			B.BOOK_NAME LIKE CONCAT('%',#{keyword},'%') or B.ISBN LIKE CONCAT('%',#{keyword},'%')
	</select>

	<select id="listNoAuthGroupBook" resultType="noAuthBookVO" parameterType="map">
		SELECT
			A.book_id AS bookId,
			A.channel_id AS channelId,
			A.adviser_id AS adviserId,
			B.type_code AS typeCode,
			T.type_name AS typeName,
			B.isbn,
			B.book_name AS bookName,
			B.cover_img AS coverImg,
			B.serial_number AS serialNumber,
			A.templet_id AS templetId
		FROM
			book_group G
		INNER JOIN book B ON G.book_id = B.book_id
			AND G.create_user = #{adviserId}
			AND G.is_delete = 0
			AND B.is_delete = 0
		INNER JOIN BOOK_ADVISER A ON A.BOOK_ID = B.BOOK_ID
			AND A.ADVISER_ID = #{adviserId}
			AND A.IS_DELETE = 0
			AND B.IS_DELETE = 0
		LEFT JOIN BOOK_TYPE T ON B.TYPE_CODE = T.TYPE_CODE
		LEFT JOIN book_auth_info I ON I.BOOK_ID = B.BOOK_ID
			AND I.ADVISER_ID = #{adviserId} AND I.is_group_book = 1
		WHERE
		I.ID IS NULL
		<if test="typeCode!=null">
			AND
			B.TYPE_CODE = #{typeCode}
		</if>
		<if test="channelId!=null">
			AND
			G.CHANNEL_ID = #{channelId}
		</if>
		<if test="name != null">
			AND
			(B.BOOK_NAME LIKE CONCAT('%', #{name}, '%') OR  B.ISBN LIKE CONCAT(#{name},'%'))
		</if>
		<if test="templetId != null">
			AND
			A.TEMPLET_ID = #{templetId}
		</if>
		<if test="secondTempletIds!=null and secondTempletIds.size()>0">
			AND
			A.SECOND_TEMPLET_ID in
			<foreach collection="secondTempletIds" item="item" open="(" separator="," close=")">
				${item}
			</foreach>
		</if>
		GROUP BY G.book_id, G.channel_id
		ORDER BY
			G.update_time DESC , G.id DESC
	</select>


	<select id="getListPage4BookGroup" parameterType="map" resultType="com.pcloud.book.book.dto.AdviserBookInfoDTO">
		SELECT
		b.BOOK_ID bookId,
		b.ISBN isbn,
		b.BOOK_NAME bookName,
		a.ADVISER_ID adviserId,
		g.agent_id agentId,
		g.id bookGroupId
		FROM
		book_group g
		INNER JOIN book_adviser a ON g.book_id = a.BOOK_ID
		AND a.IS_DELETE = 0
		AND g.channel_id = a.CHANNEL_ID
		AND g.create_user = a.ADVISER_ID
		INNER JOIN book b ON b.BOOK_ID = a.BOOK_ID AND b.IS_DELETE = 0
		WHERE
		g.is_delete = 0
		AND g.join_group_type in
		<foreach collection="joinGroupTypes" item="item" open="(" separator="," close=")">
			${item}
		</foreach>
		<if test="name!=null">
			AND
			(b.BOOK_NAME LIKE CONCAT('%', #{name},'%') OR b.ISBN LIKE CONCAT(#{name},'%'))
		</if>
		<if test="adviserId!=null">
			AND a.ADVISER_ID = #{adviserId}
		</if>
		<if test="graLabelId!=null">
			and a.GRA_LABEL_ID =#{graLabelId}
		</if>
		<if test="subLabelId!=null">
			and a.SUB_LABEL_ID =#{subLabelId}
		</if>
		<if test="verLabelId!=null">
			and a.VER_LABEL_ID =#{verLabelId}
		</if>
		<if test="areaLabelId!=null">
			and a.AREA_LABEL_ID =#{areaLabelId}
		</if>
		ORDER BY
		g.create_time DESC
	</select>

	<!-- 书刊列表-出版端 -->
	<select id="listIncomePage4Agent" parameterType="map" resultType="com.pcloud.book.book.vo.BookSaleVO">
		SELECT
		A.BOOK_ID bookId,
		B.BOOK_NAME bookName,
		A.CHANNEL_ID channelId,
		B.ISBN isbn,
		A.ADVISER_ID adviserId,
		IF(ISNULL(BF.BOOK_FUND_ID),0,1) isFundSupport
		FROM
		BOOK_ADVISER A
		INNER JOIN
		BOOK B
		ON
		A.BOOK_ID = B.BOOK_ID  AND A.IS_DELETE = 0 AND B.IS_DELETE = 0
		AND
		A.ADVISER_ID IN
		<foreach collection="agent4AdviserIds" index="i" item="item" open="(" separator="," close=")">
			${item}
		</foreach>
		AND A.IS_MAIN_EDITOR = 1
		LEFT JOIN
		BOOK_TYPE T
		ON
		B.TYPE_CODE = T.TYPE_CODE
		LEFT JOIN
		BOOK_FUND BF
		ON
		BF.BOOK_ID = A.BOOK_ID AND BF.END_TIME <![CDATA[ > ]]> NOW()  AND BF.START_TIME <![CDATA[ < ]]> NOW()
		WHERE
		1=1
		<if test="typeCode!=null">
			AND
			B.TYPE_CODE = #{typeCode}
		</if>
		<if test="channelId!=null">
			AND
			A.CHANNEL_ID = #{channelId}
		</if>
		<if test="keyWord!=null">
			AND (B.BOOK_NAME LIKE CONCAT('%',#{keyWord},'%')
			OR B.ISBN LIKE CONCAT(#{keyWord},'%')
			<if test="adviserIds!=null and adviserIds.size()>0">
				OR
				A.ADVISER_ID in
				<foreach collection="adviserIds" item="item" open="(" separator="," close=")">
					${item}
				</foreach>
			</if>
			)
		</if>
		<if test="templetId!=null">
			AND
			A.TEMPLET_ID = #{templetId}
		</if>
		<if test="secondTempletIds!=null and secondTempletIds.size()>0">
			AND
			A.SECOND_TEMPLET_ID in
			<foreach collection="secondTempletIds" item="item" open="(" separator="," close=")">
				${item}
			</foreach>
		</if>
		<if test="isFundSupport != null">
			AND
			BF.BOOK_FUND_ID IS NOT NULL
		</if>
		GROUP BY A.BOOK_ID, A.CHANNEL_ID
		ORDER BY
		A.CREATED_DATE DESC
	</select>

	<!-- 书刊列表数量-出版端 -->
	<select id="countIncomePage4Agent" parameterType="map" resultType="long">
		SELECT
		count(1)
		FROM
		BOOK_ADVISER A
		INNER JOIN
		BOOK B
		ON
		A.BOOK_ID = B.BOOK_ID  AND A.IS_DELETE = 0 AND B.IS_DELETE = 0
		AND
		A.ADVISER_ID IN
		<foreach collection="agent4AdviserIds" index="i" item="item" open="(" separator="," close=")">
			${item}
		</foreach>
		AND A.IS_MAIN_EDITOR = 1
		LEFT JOIN
		BOOK_TYPE T
		ON
		B.TYPE_CODE = T.TYPE_CODE
		LEFT JOIN
		BOOK_FUND BF
		ON
		BF.BOOK_ID = A.BOOK_ID AND BF.END_TIME <![CDATA[ > ]]> NOW()  AND BF.START_TIME <![CDATA[ < ]]> NOW()
		WHERE
		1=1
		<if test="typeCode!=null">
			AND
			B.TYPE_CODE = #{typeCode}
		</if>
		<if test="channelId!=null">
			AND
			A.CHANNEL_ID = #{channelId}
		</if>
		<if test="keyWord!=null">
			AND (B.BOOK_NAME LIKE CONCAT('%',#{keyWord},'%')
			OR B.ISBN LIKE CONCAT(#{keyWord},'%')
			<if test="adviserIds!=null and adviserIds.size()>0">
				OR
				A.ADVISER_ID in
				<foreach collection="adviserIds" item="item" open="(" separator="," close=")">
					${item}
				</foreach>
			</if>
			)
		</if>
		<if test="templetId!=null">
			AND
			A.TEMPLET_ID = #{templetId}
		</if>
		<if test="secondTempletIds!=null and secondTempletIds.size()>0">
			AND
			A.SECOND_TEMPLET_ID in
			<foreach collection="secondTempletIds" item="item" open="(" separator="," close=")">
				${item}
			</foreach>
		</if>
		<if test="isFundSupport != null">
			AND
			BF.BOOK_FUND_ID IS NOT NULL
		</if>
	</select>

	<select id="getBookIdsByBookName" parameterType="map" resultType="long">
		SELECT DISTINCT a.BOOK_ID
		FROM
			book_adviser a
			INNER JOIN book b ON a.BOOK_ID = b.BOOK_ID  AND a.IS_DELETE = 0 AND b.IS_DELETE = 0
		WHERE
			b.BOOK_NAME LIKE CONCAT('%',#{bookName},'%')
		LIMIT 0,50
	</select>

	<select id="getAdviserBooks4Pcloud" resultMap="bookMap" parameterType="map">
		SELECT
		A.BOOK_ID, A.CHANNEL_ID, A.ADVISER_ID, A.IS_MAIN_EDITOR, T.TYPE_CODE, T.TYPE_NAME, B.ISBN, B.BOOK_NAME, B.REMARK,
		B.AUTHOR, B.PUBLISH, B.PUBLISH_DATE, B.COVER_IMG, B.ORIGIN_NAME, B.BOOK_PRICE, B.ISSN, B.BOOK_NUM, B.SERIAL_NUMBER,
		A.TEMPLET_ID,A.SECOND_TEMPLET_ID,A.LAST_MODIFIED_DATE LAST_MODIFIED_DATE,A.BOOK_ADVISER_ID,CONCAT('BK',A.BOOK_ID) uniqueNumber
		FROM
		BOOK_ADVISER A
		INNER JOIN BOOK B ON A.BOOK_ID = B.BOOK_ID AND A.IS_DELETE = 0 AND B.IS_DELETE = 0
		LEFT JOIN BOOK_TYPE T ON B.TYPE_CODE = T.TYPE_CODE
		WHERE
		1=1
		AND A.IS_MAIN_EDITOR = 1
		<if test="typeCode!=null">
			AND
			B.TYPE_CODE = #{typeCode}
		</if>
		<if test="keyword != null">
			AND (B.BOOK_NAME LIKE CONCAT('%', #{keyword}, '%')
			OR  B.ISBN LIKE CONCAT(#{keyword},'%')
			OR  CONCAT('BK',A.BOOK_ID) = #{keyword})
		</if>
		<if test="templetId != null">
			AND
			A.TEMPLET_ID = #{templetId}
		</if>
		<if test="secondTempletId!=null ">
			AND
			A.SECOND_TEMPLET_ID=#{secondTempletId}
		</if>
		<if test="adviserIds!=null and adviserIds.size()>0">
			AND
			A.ADVISER_ID in
			<foreach collection="adviserIds" item="item" open="(" separator="," close=")">
				${item}
			</foreach>
		</if>
		GROUP BY A.BOOK_ID, A.CHANNEL_ID
		ORDER BY
		A.LAST_MODIFIED_DATE DESC
	</select>

	<select id="getAdviserBooks4H5" resultMap="bookMap" parameterType="map">
		SELECT
		A.BOOK_ID, A.CHANNEL_ID, A.ADVISER_ID, A.IS_MAIN_EDITOR, T.TYPE_CODE, T.TYPE_NAME, B.ISBN, B.BOOK_NAME, B.REMARK,
		B.AUTHOR, B.PUBLISH, B.PUBLISH_DATE, B.COVER_IMG, B.ORIGIN_NAME, B.BOOK_PRICE, B.ISSN, B.BOOK_NUM, B.SERIAL_NUMBER,
		A.TEMPLET_ID,A.SECOND_TEMPLET_ID,A.LAST_MODIFIED_DATE LAST_MODIFIED_DATE,A.BOOK_ADVISER_ID,CONCAT('BK',A.BOOK_ID) uniqueNumber
		FROM
		BOOK_ADVISER A
		INNER JOIN BOOK B ON A.BOOK_ID = B.BOOK_ID AND A.IS_DELETE = 0 AND B.IS_DELETE = 0
		LEFT JOIN BOOK_TYPE T ON B.TYPE_CODE = T.TYPE_CODE
		WHERE
		1=1
		AND A.IS_MAIN_EDITOR = 1
		<if test="keyword != null">
			AND (B.BOOK_NAME LIKE CONCAT('%', #{keyword}, '%'))
		</if>
		<if test="templetId != null">
			AND
			A.TEMPLET_ID = #{templetId}
		</if>
		<if test="secondTempletId!=null ">
			AND
			A.SECOND_TEMPLET_ID=#{secondTempletId}
		</if>
		GROUP BY A.BOOK_ID, A.CHANNEL_ID
		ORDER BY
		A.LAST_MODIFIED_DATE DESC
	</select>

	<select id="getAdviserBookByName" parameterType="map" resultMap="bookMap">
		SELECT
		A.BOOK_ID, A.CHANNEL_ID, A.ADVISER_ID, A.IS_MAIN_EDITOR, T.TYPE_CODE, T.TYPE_NAME, B.ISBN, B.BOOK_NAME, B.REMARK,
		B.AUTHOR, B.PUBLISH, B.PUBLISH_DATE, B.COVER_IMG, B.ORIGIN_NAME, B.BOOK_PRICE, B.ISSN, B.BOOK_NUM, B.SERIAL_NUMBER,
		A.TEMPLET_ID,A.SECOND_TEMPLET_ID,A.LAST_MODIFIED_DATE LAST_MODIFIED_DATE,A.BOOK_ADVISER_ID,CONCAT('BK',A.BOOK_ID) uniqueNumber
		FROM
		BOOK_ADVISER A
		INNER JOIN BOOK B ON A.BOOK_ID = B.BOOK_ID AND A.IS_DELETE = 0 AND B.IS_DELETE = 0
		LEFT JOIN BOOK_TYPE T ON B.TYPE_CODE = T.TYPE_CODE
		WHERE B.BOOK_NAME=#{bookName}
		AND A.IS_MAIN_EDITOR = 1
		limit 1
	</select>
</mapper>