<?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.BookFundDaoImpl">
	<resultMap id="BaseResultMap" type="bookFundDto">
		<id column="BOOK_FUND_ID" property="bookFundId" jdbcType="BIGINT" />
		<result column="BOOK_ID" property="bookId" jdbcType="BIGINT" />
		<result column="FUND_NAME" property="fundName" jdbcType="VARCHAR" />
		<result column="PURCHASE_MONEY" property="purchaseMoney" jdbcType="DECIMAL" />
		<result column="START_TIME" property="startTime" jdbcType="VARCHAR" />
		<result column="END_TIME" property="endTime" jdbcType="VARCHAR" />
		<result column="CREATED_USER" property="createdUser" jdbcType="BIGINT" />
		<result column="CREATED_DATE" property="createdDate" jdbcType="TIMESTAMP" />
		<result column="LAST_MODIFIED_USER" property="lastModifiedUser" jdbcType="BIGINT" />
		<result column="LAST_MODIFIED_DATE" property="lastModifiedDate" jdbcType="TIMESTAMP" />
		<result column="FUND_MONEY_SUM" property="fundMoneySum" jdbcType="DECIMAL" />
		<result column="VERSION" property="version" jdbcType="BIGINT" />
		<!--michael 新增于20180807-->
		<result column="CONTRACT_NO" property="contractNo" jdbcType="VARCHAR" />
		<result column="BATCH_NUMBER" property="batchNumber" jdbcType="VARCHAR" />
		<result column="PROXY_MODE" property="proxyMode" jdbcType="BIT" />
		<result column="TRANSFEROR" property="transferor" jdbcType="VARCHAR" />
		<result column="IS_TRANSFEROR" property="isTransferor" jdbcType="BIT" />
	</resultMap>
	<!--michael 修改于20180807-->
	<sql id="Base_Column_List">
		BOOK_FUND_ID, BOOK_ID, FUND_NAME, PURCHASE_MONEY, START_TIME, END_TIME,
		CREATED_USER,CONTRACT_NO,BATCH_NUMBER,PROXY_MODE,TRANSFEROR,IS_TRANSFEROR,
		CREATED_DATE, LAST_MODIFIED_USER, LAST_MODIFIED_DATE, VERSION
	</sql>

	<resultMap id="BookFundInfoResult" type="bookFundInfoDto">
		<id column="BOOK_FUND_ID" property="bookFundId" jdbcType="BIGINT" />
		<result column="BOOK_ID" property="bookId" jdbcType="BIGINT" />
		<result column="FUND_NAME" property="fundName" jdbcType="VARCHAR" />
		<result column="CONTRACT_NO" property="contractNo" jdbcType="VARCHAR" />
		<result column="BATCH_NUMBER" property="batchNumber" jdbcType="VARCHAR" />
		<result column="PROXY_MODE" property="proxyMode" jdbcType="BIT"/>
		<result column="TRANSFEROR" property="transferor" jdbcType="VARCHAR" />
		<result column="IS_TRANSFEROR" property="isTransferor" jdbcType="BIT" />
		<result column="PURCHASE_MONEY" property="purchaseMoney" jdbcType="DECIMAL" />
		<result column="START_TIME" property="startTime" jdbcType="VARCHAR" />
		<result column="END_TIME" property="endTime" jdbcType="VARCHAR" />
	</resultMap>


	<!-- 创建书刊基金状态 -->
	<!--michael 修改于20180807-->
	<insert id="insert" parameterType="bookFund" useGeneratedKeys="true" keyProperty="bookFundId" flushCache="true">
		insert into book_fund (BOOK_ID, FUND_NAME,
		PURCHASE_MONEY, START_TIME, END_TIME,
		CREATED_USER, CREATED_DATE, LAST_MODIFIED_USER,
		LAST_MODIFIED_DATE, VERSION,CONTRACT_NO,BATCH_NUMBER,PROXY_MODE,TRANSFEROR,IS_TRANSFEROR)
		values (#{bookId,jdbcType=BIGINT}, #{fundName,jdbcType=VARCHAR},
		#{purchaseMoney,jdbcType=DECIMAL}, #{startTime,jdbcType=TIMESTAMP},
		#{endTime,jdbcType=TIMESTAMP},#{createdUser,jdbcType=BIGINT},
		now(), #{createdUser,jdbcType=BIGINT},now(),1,
		#{contractNo,jdbcType=VARCHAR},#{batchNumber,jdbcType=VARCHAR},
		#{proxyMode,jdbcType=BIT},#{transferor,jdbcType=VARCHAR},#{isTransferor,jdbcType=BIT})
	</insert>

	<insert id="batchInsert" parameterType="java.util.List" useGeneratedKeys="true" keyProperty="bookFundId" flushCache="true">
		insert into book_fund (BOOK_ID, FUND_NAME,
		PURCHASE_MONEY, START_TIME, END_TIME,
		CREATED_USER, CREATED_DATE, LAST_MODIFIED_USER,
		LAST_MODIFIED_DATE, VERSION,CONTRACT_NO,BATCH_NUMBER,PROXY_MODE,TRANSFEROR,IS_TRANSFEROR)
		values
		<foreach collection="list" item="item" index="index" separator="," >
		(#{item.bookId,jdbcType=BIGINT}, #{item.fundName,jdbcType=VARCHAR},
		#{item.purchaseMoney,jdbcType=DECIMAL}, #{item.startTime,jdbcType=TIMESTAMP},
		#{item.endTime,jdbcType=TIMESTAMP},#{item.createdUser,jdbcType=BIGINT},
		now(), #{item.createdUser,jdbcType=BIGINT},now(),1,
		#{item.contractNo,jdbcType=VARCHAR},#{item.batchNumber,jdbcType=VARCHAR},
		#{item.proxyMode,jdbcType=BIT},#{item.transferor,jdbcType=VARCHAR},#{item.isTransferor,jdbcType=BIT})
		</foreach>
	</insert>

	<!-- 修改书籍基金信息-->
	<!--michael 修改于20180807-->
	<update id="update" parameterType="bookFund" flushCache="true">
		UPDATE book_fund
		SET
			<if test="endTime != null">
				END_TIME = #{endTime, jdbcType=TIMESTAMP},
			</if>
			<if test="purchaseMoney != null">
				PURCHASE_MONEY = #{purchaseMoney, jdbcType=DECIMAL},
			</if>
			<if test="contractNo != null">
				CONTRACT_NO = #{contractNo,jdbcType=VARCHAR}
			</if>
			<if test="batchNumber != null">
				BATCH_NUMBER = #{batchNumber,jdbcType=VARCHAR}
			</if>
			<if test="isTransferor!=null">
				IS_TRANSFEROR =#{isTransferor,jdbcType=BIT}
			</if>
			LAST_MODIFIED_DATE =  NOW(),
		  	LAST_MODIFIED_USER = #{lastModifiedUser,jdbcType=BIGINT},
			VERSION = VERSION + 1
		WHERE
			BOOK_FUND_ID = #{bookFundId, jdbcType=BIGINT}
		AND
			VERSION = #{version, jdbcType=BIGINT}
	</update>


	
	<!-- 根据书籍ID获取书刊基金 -->
	<select id="getByBookId" resultMap="BaseResultMap" parameterType="java.lang.Long">
		SELECT
		BOOK_FUND_ID, BOOK_ID, FUND_NAME, PURCHASE_MONEY, START_TIME,
		 END_TIME, LAST_MODIFIED_USER, LAST_MODIFIED_DATE, VERSION,
		 CONTRACT_NO,BATCH_NUMBER,PROXY_MODE,TRANSFEROR,IS_TRANSFEROR
		FROM BOOK_FUND
		WHERE BOOK_ID = #{bookId,jdbcType=BIGINT}
		ORDER BY START_TIME DESC
	</select>

	<!-- 修改基金获取不包含自己的基金列表 -->
	<select id="getFundListByMap" resultMap="BaseResultMap" parameterType="map">
		SELECT
		BOOK_FUND_ID, BOOK_ID, FUND_NAME, PURCHASE_MONEY,
		 START_TIME, END_TIME, LAST_MODIFIED_USER, LAST_MODIFIED_DATE, VERSION,
		 CONTRACT_NO,BATCH_NUMBER,PROXY_MODE,TRANSFEROR,IS_TRANSFEROR
		FROM BOOK_FUND
		WHERE BOOK_ID = #{bookId,jdbcType=BIGINT}
		AND BOOK_FUND_ID != #{bookFundId,jdbcType=BIGINT}
		ORDER BY START_TIME DESC
	</select>

	<!-- 根据基金ID获取书刊基金信息 -->
	<select id="getByBookFundId" resultMap="BaseResultMap" parameterType="java.lang.Long">
		SELECT
		BOOK_FUND_ID, BOOK_ID, FUND_NAME, PURCHASE_MONEY,
		START_TIME, END_TIME, VERSION,CONTRACT_NO,BATCH_NUMBER,PROXY_MODE,TRANSFEROR,IS_TRANSFEROR
		FROM BOOK_FUND
		WHERE BOOK_FUND_ID = #{bookFundId,jdbcType=BIGINT}
	</select>
	
	<select id="listByBookId" resultMap="BaseResultMap" parameterType="map">
		SELECT
		BOOK_FUND_ID, BOOK_ID, FUND_NAME, PURCHASE_MONEY,
		START_TIME, END_TIME,VERSION,CONTRACT_NO,BATCH_NUMBER,PROXY_MODE,TRANSFEROR,IS_TRANSFEROR
		FROM BOOK_FUND
		WHERE BOOK_ID = #{bookId,jdbcType=BIGINT}
		ORDER BY START_TIME DESC
	</select>
	
	
	<!-- 根据bookIds查询没有过期书刊基金关联信息 -->
	<select id="getByBookIds" resultMap="BaseResultMap" parameterType="list" >
		select
		<include refid="Base_Column_List" />
		from book_fund
		WHERE
		  	BOOK_ID IN
			<foreach collection="list" index="index" item="item" open="(" separator="," close=")">
				${item}
			</foreach>
		AND END_TIME  >  NOW()
	</select>
	<!-- 根据参数查询书刊基金信息 -->
	<select id="getByParamMap" resultMap="BaseResultMap" parameterType="map">
		select
		BOOK_FUND_ID, BOOK_ID, FUND_NAME, PURCHASE_MONEY,
		START_TIME, END_TIME,VERSION,CONTRACT_NO,BATCH_NUMBER,PROXY_MODE,TRANSFEROR,IS_TRANSFEROR
		from book_fund
		where 1=1
		<if test="bookId != null">
		and
			BOOK_ID = #{bookId,jdbcType=BIGINT}
		</if>
		<if test="date != null">
		and 
			#{date} BETWEEN START_TIME AND END_TIME
		</if>
	</select>
	<!-- 批量获取书刊基金总额 -->
	<select id="getBookFundMoney" resultMap="BaseResultMap" parameterType="list" >
		select  
		BOOK_ID,SUM(PURCHASE_MONEY) FUND_MONEY_SUM
		from book_fund
		WHERE
		  	BOOK_ID IN
			<foreach collection="list" index="index" item="item" open="(" separator="," close=")">
				${item}
			</foreach>
		GROUP BY  BOOK_ID
	</select>
	
	<select id="getCurrentBookFund" resultMap="BaseResultMap" parameterType="java.lang.Long">
		select
		<include refid="Base_Column_List" />
		from book_fund
		where BOOK_ID = #{bookId,jdbcType=BIGINT}
		AND NOW() BETWEEN START_TIME AND END_TIME
	</select>
	
	<!-- 	 获取图书在基金支持的基金名称 -->
	<select id="getUseFundName" resultType="String" parameterType="map">
		SELECT
		   FUND_NAME
		FROM BOOK_FUND
		WHERE BOOK_ID = #{bookId,jdbcType=BIGINT}
		AND #{useDate} BETWEEN START_TIME AND END_TIME
	</select>
	<!--获取基金相关信息-->
	<select id="getUseFundInfo" resultMap="BookFundInfoResult" parameterType="map">
		SELECT
		   FUND_NAME,CONTRACT_NO,BATCH_NUMBER,PROXY_MODE,BOOK_FUND_ID,IS_TRANSFEROR,
		   TRANSFEROR, PURCHASE_MONEY, START_TIME, END_TIME
		FROM BOOK_FUND
		WHERE BOOK_ID = #{bookId,jdbcType=BIGINT}
		AND #{useDate} BETWEEN START_TIME AND END_TIME
	</select>
	
		<!-- 	 获取正在基金支持的图书 -->
	<select id="getHaveFundBook" resultType="Long" parameterType="map">
		SELECT
		   BOOK_ID
		FROM BOOK_FUND
		WHERE 
			BOOK_ID IN
			<foreach collection="bookIds" index="index" item="item" open="(" separator="," close=")">
				${item}
			</foreach>
		AND NOW() BETWEEN START_TIME AND END_TIME
		LIMIT 1;
	</select>
	
	<!-- 删除图书基金 -->
	<delete id="deleteById" parameterType="java.lang.Long" flushCache="true">
		DELETE FROM 
			BOOK_FUND
		WHERE 
			BOOK_FUND_ID = #{id, jdbcType=BIGINT}
	</delete>

	<select id="getBookFundByParamMap" resultMap="BaseResultMap" parameterType="map">
		SELECT
			<include refid="Base_Column_List" />
		FROM book_fund
		WHERE
			BOOK_ID = #{bookId,jdbcType=BIGINT}
		AND #{endTime} BETWEEN START_TIME AND END_TIME
		LIMIT 1
	</select>

	<!-- 正在基金支持的图书列表 -->
	<select id="listHaveFundBooks" resultType="long" parameterType="list">
		SELECT
		   BOOK_ID
		FROM BOOK_FUND
		WHERE 
			BOOK_ID IN
			<foreach collection="list" index="index" item="item" open="(" separator="," close=")">
				${item}
			</foreach>
		AND NOW() BETWEEN START_TIME AND END_TIME
	</select>
	
	
	<select id="listAllHaveFundBooks" resultType="long" parameterType="list">
		SELECT
		   BOOK_ID
		FROM BOOK_FUND
		WHERE 
			 NOW() BETWEEN START_TIME AND END_TIME
	</select>

	<select id="getIsHaveFundBefore" resultType="Boolean" parameterType="Long">
		SELECT
		 count(1)
		FROM BOOK_FUND
		WHERE
		BOOK_ID = #{_parameter}
		AND <![CDATA[ START_TIME < now()  ]]>
	</select>

	<select id="getFundBookCount" resultType="integer">
		SELECT
		COUNT(DISTINCT b.BOOK_ID)
		FROM
		BOOK B
		LEFT JOIN (
		SELECT BOOK_FUND_ID, BOOK_ID FROM book_fund GROUP BY BOOK_ID
		) bf ON B.BOOK_ID = bf.BOOK_ID
		WHERE
		IS_DELETE = 0
		AND bf.BOOK_FUND_ID IS NOT NULL
	</select>

	<select id="getListByBookIds" parameterType="map" resultMap="BaseResultMap">
		select
		<include refid="Base_Column_List" />
		from book_fund
		WHERE
		BOOK_ID IN
		<foreach collection="bookIds" index="index" item="item" open="(" separator="," close=")">
			${item}
		</foreach>
	</select>

	<select id="getMaxEndTimeByBookId" parameterType="map" resultMap="BaseResultMap">
       SELECT
		BOOK_FUND_ID, BOOK_ID, FUND_NAME, PURCHASE_MONEY,
		START_TIME, END_TIME,VERSION,CONTRACT_NO,BATCH_NUMBER,PROXY_MODE,TRANSFEROR,IS_TRANSFEROR
		FROM BOOK_FUND
		WHERE BOOK_ID = #{bookId,jdbcType=BIGINT}
		ORDER BY END_TIME DESC
		limit 1
	</select>

	<select id="getCountByBatchNumber" parameterType="map" resultType="Integer">
		select ifnull(count(1),0)
		from BOOK_FUND
		where BATCH_NUMBER=#{batchNumber}
	</select>

</mapper>