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

	<!-- 批量新增销售线索-->
	<insert id="replaceSaleClueInfoBatch">
		replace into sale_clue_info(
		sales_leads_type,
		seller_id,
		seller_name,
		fans,
		industry_id,
		province_id,
		city_id,
		telephone,
		provider_flag,
		transition_flag,
		upload_date,
		distribution_sale_flag,
		import_phone_flag,
		sale_id,
		`state`,
		offline_sale_flag,
		create_user_id,
		create_time)
		values
		<foreach collection="saleClueList" item="info" separator=",">
			(
			#{info.salesLeadsType},
			#{info.sellerId},
			#{info.sellerName},
			#{info.fans},
			#{info.industryId},
			#{info.provinceId},
			#{info.cityId},
			#{info.telephone},
			#{info.providerFlag},
			#{info.transitionFlag},
			#{info.uploadDate},
			#{info.distributionSaleFlag},
			#{info.importPhoneFlag},
			#{info.saleId},
			#{info.state},
			#{info.offlineSaleFlag},
			#{info.createUserId},
			NOW()
			)
		</foreach>
	</insert>


	<!-- 销售线索列表信息 -->
	<select id="selectSaleClueInfoList" resultType="com.ruixuan.salesLeads.pojo.SaleClueInfoPojo">
		SELECT
			s.id,
			s.sales_leads_type,
			s.seller_id,
			s.seller_name,
			s.fans,
			s.industry_id,
			(SELECT name from sys_industry_info i where i.id = s.industry_id) industryName,
			s.province_id,
			(SELECT name from sys_area_info a WHERE a.`code` = s.province_id) provinceName,
			s.city_id,
			(SELECT name from sys_area_info a WHERE a.`code` = s.city_id) cityName,
			s.telephone,
			s.provider_flag,
			s.transition_flag,
			s.upload_date,
			s.distribution_sale_flag,
			s.import_phone_flag,
			s.sale_id,
			(SELECT u.nick_name FROM sys_user u WHERE u.user_id = s.sale_id ) saleName,
			s.state,
			s.offline_sale_flag,
			s.create_user_id,
			s.create_time
		FROM
			sale_clue_info s
		<where>
			<if test="sellerName !=null and sellerName != ''">
				and (s.seller_name like CONCAT('%', CONCAT(#{sellerName}, '%')) or s.seller_id like CONCAT('%', CONCAT(#{sellerName}, '%')))
			</if>
			<if test="uploadDate !=null and uploadDate != ''">
				and s.upload_date = #{uploadDate}
			</if>
			<if test="distributionSaleFlag !=null and distributionSaleFlag != ''" >
				and s.distribution_sale_flag = #{distributionSaleFlag}
			</if>
			<if test="industryId !=null and industryId != ''">
				and s.industry_id = #{industryId}
			</if>
			<if test="state !=null and state != ''">
				and s.state = #{state}
			</if>
			<if test="salesLeadsType !=null and salesLeadsType != ''">
				and s.sales_leads_type = #{salesLeadsType}
			</if>
			<if test="userId !=null and userId != ''">
				and s.update_user_id = #{userId}
			</if>
			<if test="fansType == '1'.toString()">
				and s.fans BETWEEN 0 AND 500000
			</if>
			<if test="fansType == '2'.toString()">
				and s.fans BETWEEN 500000 AND 2000000
			</if>
			<if test="fansType == '3'.toString()">
				and s.fans BETWEEN 2000000 AND 5000000
			</if>
			<if test="fansType == '4'.toString()">
				and s.fans &gt;=5000000
			</if>
		</where>

	</select>


	<!-- 销售线索 任务 列表信息 -->
	<select id="selectSaleClueTaskList" resultType="com.ruixuan.salesLeads.pojo.SaleClueInfoPojo">
		SELECT
			s.id,
			s.sales_leads_type,
			s.seller_id,
			s.seller_name,
			s.fans,
			s.industry_id,
			(SELECT name from sys_industry_info i where i.id = s.industry_id) industryName,
			s.province_id,
			(SELECT name from sys_area_info a WHERE a.`code` = s.province_id) provinceName,
			s.city_id,
			(SELECT name from sys_area_info a WHERE a.`code` = s.city_id) cityName,
			s.telephone,
			s.provider_flag,
			s.transition_flag,
			s.upload_date,
			s.distribution_sale_flag,
			s.import_phone_flag,
			s.sale_id,
			(SELECT u.nick_name FROM sys_user u WHERE u.user_id = s.sale_id ) saleName,
			s.state,
			s.offline_sale_flag,
			s.create_user_id,
			s.create_time
		FROM
			sale_clue_info s
		<where>
			<if test="sellerName !=null and sellerName != ''">
				and (s.seller_name like CONCAT('%', CONCAT(#{sellerName}, '%')) or s.seller_id like CONCAT('%', CONCAT(#{sellerName}, '%')))
			</if>
			<if test="state !=null and state != ''">
				and s.state = #{state}
			</if>
			<if test="saleId !=null and saleId != ''">
				and s.sale_id = #{saleId}
			</if>
		</where>

	</select>

	<!-- 根据卖家ids 查询销售线索 -->
	<select id="selectSaleClueInfoBySellers" resultType="com.ruixuan.salesLeads.pojo.SaleClueInfoPojo">
		SELECT
			s.id,
			s.sales_leads_type,
			s.seller_id,
			s.seller_name,
			s.fans,
			s.industry_id,
			s.province_id,
			s.city_id,
			s.telephone,
			s.provider_flag,
			s.transition_flag,
			s.upload_date,
			s.distribution_sale_flag,
			s.import_phone_flag,
			s.sale_id,
			s.state,
			s.offline_sale_flag,
			s.create_user_id,
			s.create_time
		FROM
			sale_clue_info s
		<where>
			<if test="sellerIds != null and sellerIds.size() != 0 ">
				and s.seller_id in
				<foreach collection="sellerIds" item="item" separator=","
						 open="(" close=")">
					#{item}
				</foreach>
			</if>
		</where>
	</select>



	<!--  修改销售线索 销售人员 -->
	<update id="updateSaleClueSale">
		UPDATE sale_clue_info
		SET
		    distribution_sale_flag = #{distributionSaleFlag},
			sale_id = #{saleId}
			WHERE
				seller_id = #{sellerId}
			  and sale_id != null
	</update>



	<!--  修改销售线索 状态 -->
	<update id="updateSaleClueState">
		UPDATE sale_clue_info
		SET
			state = #{state}
			WHERE
				seller_id = #{sellerId}
	</update>



	<!-- 根据销售 查询未完成数量 -->
	<select id="selectSaleClueComplete" resultType="java.lang.Integer">
		SELECT
			COUNT( s.id )
		FROM
			sale_clue_info s
		WHERE
			s.sale_id = #{saleId}
		  AND s.state != 3
	</select>




	<!-- 卖家id 查询销售信息  -->
	<select id="selectSaleClueInfoBySellerId" resultType="com.ruixuan.salesLeads.pojo.vo.SaleRecordVo">
		SELECT
		s.id,
		s.sales_leads_type,
		s.seller_id,
		s.seller_name,
		s.fans,
		s.industry_id,
		(SELECT name from sys_industry_info i where i.id = s.industry_id) industryName,
		s.province_id,
		(SELECT name from sys_area_info a WHERE a.`code` = s.province_id) provinceName,
		s.city_id,
		(SELECT name from sys_area_info a WHERE a.`code` = s.city_id) cityName,
		s.telephone,
		s.provider_flag,
		s.transition_flag,
		s.upload_date,
		s.distribution_sale_flag,
		s.import_phone_flag,
		s.sale_id,
		(SELECT u.nick_name FROM sys_user u WHERE u.user_id = s.sale_id ) saleName,
		s.state,
		s.offline_sale_flag,
		s.create_user_id,
		s.create_time,
		q.nearly_thirty_days,
		q.gmv_month,
		q.gmv,
		q.mtd_date,
		q.mtd
		FROM
		sale_clue_info s
		LEFT JOIN sale_quota_info q on s.seller_id = q.seller_id
		<where>
			<if test="sellerId !=null and sellerId != ''">
				and s.seller_id = #{sellerId}
			</if>
		</where>
		ORDER BY q.create_time DESC
		limit 1

	</select>


	<!-- 销售线索id 查询销售信息  -->
	<select id="selectSaleClueInfoById" resultType="com.ruixuan.salesLeads.pojo.vo.SaleRecordVo">
		SELECT
		s.id,
		s.sales_leads_type,
		s.seller_id,
		s.seller_name,
		s.fans,
		s.industry_id,
		(SELECT name from sys_industry_info i where i.id = s.industry_id) industryName,
		s.province_id,
		(SELECT name from sys_area_info a WHERE a.`code` = s.province_id) provinceName,
		s.city_id,
		(SELECT name from sys_area_info a WHERE a.`code` = s.city_id) cityName,
		s.telephone,
		s.provider_flag,
		s.transition_flag,
		s.upload_date,
		s.distribution_sale_flag,
		s.import_phone_flag,
		s.sale_id,
		(SELECT u.nick_name FROM sys_user u WHERE u.user_id = s.sale_id ) saleName,
		s.state,
		s.offline_sale_flag,
		s.create_user_id,
		s.create_time,
		q.nearly_thirty_days,
		q.gmv_month,
		q.gmv,
		q.mtd_date,
		q.mtd
		FROM
		sale_clue_info s
		LEFT JOIN sale_quota_info q on s.seller_id = q.seller_id
		where s.id = #{id}
		and s.import_phone_flag = 0
		ORDER BY q.create_time DESC
		limit 1
	</select>



	<!--  修改销售线索 销售类别 电销或面销 -->
	<update id="updateSaleClueOfflineFlag">
		UPDATE sale_clue_info
		SET
			offline_sale_flag = #{offlineSaleFlag},
			sale_id = #{saleId}
		WHERE
			seller_id = #{sellerId}
	</update>


	<!--  修改销售线索 导入电话记录的状态 -->
	<update id="updateSaleClueImportPhoneFlag">
		UPDATE sale_clue_info
		SET
			import_phone_flag = #{importPhoneFlag}
		WHERE
			id = #{id}
	</update>







</mapper> 