<?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.ruixuan.salesLeads.mapper.SaleFinancePolicyInfoMapper">


	<!-- 查看政策列表 -->
	<select id="selectPolicyList" resultType="com.ruixuan.salesLeads.pojo.SaleFinancePolicyInfoPojo">
		SELECT
			s.id,
			s.ks_id,
			c.customer_name 'companyName',
			i.main_body_name 'advertiserName',
			s.release_type,
			s.company_subject_id,
			s.promotion_type,
			s.advance_pay,
			s.operate_settlement_type,
			s.approved_status,
			s.approved_progress,
			s.rebate_type,
			s.rebate_rate,
			s.accounting_period,
			s.policy_start_date,
			s.policy_end_date,
			s.remarks,
			s.sale_id,
			u.nick_name 'saleName',
			s.del_flag,
			s.create_user_id,
			s.create_time,
			s.update_time,
			p.policy_product_name 'productName'
		FROM
			sale_finance_policy_info s
				LEFT JOIN sale_finance_policy_product_info p ON s.id = p.policy_id
				LEFT JOIN kuaishou_live_customer c ON s.ks_id = c.ks_id
				LEFT JOIN kuaishou_live_customer_invoice i ON i.ks_id = c.ks_id
				LEFT JOIN sys_user u on u.user_id = s.sale_id
		WHERE
			s.del_flag = 0
		  AND p.del_flag = 0
		<if test="companyName != null and companyName != ''">
			AND c.customer_name like concat(concat('%',#{companyName}),'%')
		</if>

		<if test="advertiserName != null and advertiserName != ''">
			AND i.main_body_name like concat(concat('%',#{advertiserName}),'%')
		</if>

		<if test="productName != null and productName != ''">
			AND p.policy_product_name like concat(concat('%',#{productName}),'%')
		</if>

		<if test="releaseType != null and releaseType != ''">
			AND  s.release_type = #{releaseType}
		</if>

		<if test="rebateType != null">
			AND s.rebate_type = #{rebateType}
		</if>

		<if test="saleName != null and saleName != ''">
			AND u.nick_name like concat(concat('%',#{saleName}),'%')
		</if>

		<if test="createUserId != null and createUserId != ''">
			AND (s.sale_id = #{createUserId} or s.approved_progress = #{createUserId})
		</if>

		<if test="createTimeStart != null and createTimeStart != ''">
			AND s.create_time  &gt;= #{createTimeStart}
		</if>
		<if test="createTimeEnd != null and createTimeEnd != ''">
			AND s.create_time  &lt;= #{createTimeEnd}
		</if>
		order by s.create_time DESC

	</select>


	<!-- 查看政策详情 -->
	<select id="selectPolicyInfoById" resultType="com.ruixuan.salesLeads.pojo.SaleFinancePolicyInfoPojo">
		SELECT
			s.id,
			s.ks_id,
			c.customer_name 'companyName',
			i.main_body_name 'advertiserName',
			s.release_type,
			s.company_subject_id,
			s.promotion_type,
			s.advance_pay,
			s.operate_settlement_type,
			s.approved_status,
			s.approved_progress,
			s.rebate_type,
			s.rebate_rate,
			s.accounting_period,
			s.policy_start_date,
			s.policy_end_date,
			s.remarks,
			s.sale_id,
			u.nick_name 'saleName',
			s.del_flag,
			s.create_user_id,
			s.create_time,
			s.update_time,
			p.policy_product_name 'productName'
		FROM
			sale_finance_policy_info s
				LEFT JOIN sale_finance_policy_product_info p ON s.id = p.policy_id
				LEFT JOIN kuaishou_live_customer c ON s.ks_id = c.ks_id
				LEFT JOIN kuaishou_live_customer_invoice i ON i.ks_id = c.ks_id
				LEFT JOIN sys_user u on u.user_id = s.sale_id
		WHERE
			s.del_flag = 0
		  AND p.del_flag = 0
		  AND s.id = #{policyId}
			limit 1
	</select>

<!-- 新增政策-->
	<insert id="addSaleFinancePolicyInfo" parameterType="com.ruixuan.salesLeads.pojo.SaleFinancePolicyInfoPojo"
			useGeneratedKeys="true"
			keyColumn="id"
			keyProperty="id">
		insert into sale_finance_policy_info(
			ks_id,
			release_type,
			company_subject_id,
			promotion_type,
			advance_pay,
			operate_settlement_type,
			approved_status,
			approved_progress,
			rebate_type,
			rebate_rate,
			accounting_period,
			policy_start_date,
			policy_end_date,
			remarks,
			sale_id,
			del_flag,
			create_user_id,
			create_time,
			update_time
			)
		values
		(
			#{ksId},
			#{releaseType},
			#{companySubjectId},
			#{promotionType},
			#{advancePay},
			#{operateSettlementType},
			#{approvedStatus},
			#{approvedProgress},
			#{rebateType},
			#{rebateRate},
			#{accountingPeriod},
			#{policyStartDate},
			#{policyEndDate},
			#{remarks},
			#{saleId},
			0,
			#{createUserId},
			NOW(),
			NOW()
		)
	</insert>


	<!-- 查询 政策审核人信息 -->
	<select id="selectPolicyApprovalUser" resultType="com.alibaba.fastjson.JSONObject">
		SELECT
			*
		FROM
			`sale_finance_policy_approval_user_info`
		WHERE
			del_flag = 0
		ORDER BY
			`level`

	</select>



	<!-- 通过用户查询 政策审核人信息  -->
	<select id="selectPolicyApprovalUserByUserId" resultType="com.alibaba.fastjson.JSONObject">
		SELECT
			*
		FROM
			`sale_finance_policy_approval_user_info`
		WHERE
			del_flag = 0
		and approval_user_id = #{approvalUserId}
		limit 1

	</select>

	<!-- 通过当前审核人的级别查询 政策下一级审核人信息  -->
	<select id="selectNextApprovalUser" resultType="com.alibaba.fastjson.JSONObject">
		SELECT
			*
		FROM
			`sale_finance_policy_approval_user_info`
		WHERE
			del_flag = 0
		  AND `level` > (
			SELECT
				`level`
			FROM
				`sale_finance_policy_approval_user_info`
			WHERE
				del_flag = 0
			  AND approval_user_id = #{approvalUserId}
			LIMIT 1)
			limit 1



	</select>



	<!-- 修改政策审核状态 -->
	<update id="updatePolicyStatus">
		UPDATE `ruixuan`.`sale_finance_policy_info`
		SET approved_status = #{approvedStatus},
			approved_progress = #{approvedProgress},
			update_time = NOW()
		WHERE
			id = #{policyId};
	</update>




	<!-- 查询快手账户以及公司和资质  -->
	<select id="selectPolicyCustomerList" resultType="com.alibaba.fastjson.JSONObject">
		SELECT
			c.ks_id 'ksId',
			c.customer_name 'companyName',
			i.main_body_name 'advertiserName'
		FROM
			kuaishou_live_customer c
				RIGHT JOIN kuaishou_live_customer_invoice i ON c.ks_id = i.ks_id

	</select>

	<!-- 查询公司主体列表 -->
	<select id="selectCompanySubjectInfoList" resultType="com.ruixuan.salesLeads.pojo.SaleFinanceCompanySubjectInfoPojo">
		SELECT
			*
		FROM
			`sale_finance_company_subject_info`
		WHERE
			del_flag = 0
	</select>





</mapper> 