<?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.cool.store.mapper.visit.VisitRecordMapper">
  <resultMap id="BaseResultMap" type="com.cool.store.entity.visit.VisitRecordDO">
    <!--
      WARNING - @mbg.generated
    -->
    <id column="id" jdbcType="BIGINT" property="id" />
    <result column="visit_no" jdbcType="VARCHAR" property="visitNo" />
    <result column="line_id" jdbcType="BIGINT" property="lineId" />
    <result column="status" jdbcType="BIT" property="status" />
    <result column="complete_edit" jdbcType="BIT" property="completeEdit" />
    <result column="sign_in_time" jdbcType="TIMESTAMP" property="signInTime" />
    <result column="sign_in_lon_lat" jdbcType="VARCHAR" property="signInLonLat" />
    <result column="sign_in_address" jdbcType="VARCHAR" property="signInAddress" />
    <result column="sign_out_time" jdbcType="TIMESTAMP" property="signOutTime" />
    <result column="sign_out_lon_lat" jdbcType="VARCHAR" property="signOutLonLat" />
    <result column="sign_out_address" jdbcType="VARCHAR" property="signOutAddress" />
    <result column="visit_date" jdbcType="DATE" property="visitDate" />
    <result column="desire" jdbcType="VARCHAR" property="desire" />
    <result column="desire_city" jdbcType="VARCHAR" property="desireCity" />
    <result column="desire_district" jdbcType="VARCHAR" property="desireDistrict" />
    <result column="existing_shop_point" jdbcType="BIT" property="existingShopPoint" />
    <result column="remark" jdbcType="VARCHAR" property="remark" />
    <result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
    <result column="update_time" jdbcType="TIMESTAMP" property="updateTime" />
    <result column="photos" jdbcType="LONGVARCHAR" property="photos" />
    <result column="user_id" jdbcType="VARCHAR" property="userId" />
  </resultMap>

  <sql id="Visit_Record_Query">
    <if test="request.visitUserId!=null and request.visitUserId!=''">
      and a.user_id = #{request.visitUserId}
    </if>
    <if test="request.lineId != null">
      AND a.line_id = #{request.lineId}
    </if>
    <if test="request.keyword != null and request.keyword != ''">
      AND (b.mobile LIKE CONCAT('%', #{request.keyword}, '%') OR b.username LIKE CONCAT('%', #{request.keyword}, '%'))
    </if>
    <if test="request.visitNo != null and request.visitNo != ''">
      AND a.visit_no LIKE CONCAT('%', #{request.visitNo}, '%')
    </if>
    <if test="request.visitStartDate != null">
      AND a.visit_date >= #{request.visitStartDate}
    </if>
    <if test="request.visitEndDate != null">
      AND a.visit_date &lt;= #{request.visitEndDate}
    </if>
    <if test="request.status != null">
      AND a.status = #{request.status}
    </if>
    <if test="request.userId != null">
      AND a.user_id = #{request.userId}
    </if>
    <if test="request.desire != null and request.desire != ''">
      AND a.desire = #{request.desire}
    </if>
    <if test="request.investRegionId != null">
      AND b.invest_region_id = #{request.investRegionId}
    </if>
  </sql>

  <select id="getPartnerList" resultType="com.cool.store.vo.visit.VisitPartnerListVO">
    SELECT a.line_id, a.visit_num, b.username name, b.mobile
    <if test="userId != null and userId != ''">
      , a.my_visit_num
    </if>
    FROM (
      SELECT line_id, COUNT(1) visit_num
       <if test="userId != null and userId != ''">
         , SUM(CASE WHEN user_id = #{userId} THEN 1 ELSE 0 END) my_visit_num
       </if>
      FROM zxjp_visit_record
      WHERE status = 2
      GROUP BY line_id
    ) a
    INNER JOIN xfsg_line_info b ON a.line_id = b.id AND b.deleted = 0
    <where>
      <if test="userId != null and userId != ''">
        AND a.my_visit_num > 0
      </if>
      <if test="regionIds != null and !regionIds.isEmpty()">
        AND b.region_id IN
        <foreach item="regionId" collection="regionIds" separator="," open="(" close=")">
          #{regionId}
        </foreach>
      </if>
      <if test="keyword != null and keyword != ''">
        AND (b.mobile LIKE CONCAT('%', #{keyword}, '%') OR b.username LIKE CONCAT('%', #{keyword}, '%'))
      </if>
    </where>
  </select>

  <select id="getVisitRecordList" resultType="com.cool.store.vo.visit.VisitRecordListVO">
    SELECT a.id, a.line_id, a.visit_no, a.visit_date, a.sign_in_address, a.desire, a.desire_city,
           a.desire_district, a.existing_shop_point, a.photos, a.status,
           b.username name, b.mobile,a.user_id as visitUserId, b.invest_region_id, b.investment_manager
    FROM zxjp_visit_record a
    INNER JOIN xfsg_line_info b ON a.line_id = b.id AND b.deleted = 0
    <where>
        <include refid="Visit_Record_Query"/>
    </where>
    ORDER BY a.create_time DESC
  </select>

  <select id="getVisitRecordCount" resultType="java.lang.Long">
    SELECT COUNT(1)
    FROM zxjp_visit_record a
    INNER JOIN xfsg_line_info b ON a.line_id = b.id AND b.deleted = 0
    <where>
        <include refid="Visit_Record_Query"/>
    </where>
  </select>

  <select id="getPartnerLineId" resultType="java.lang.Long">
    SELECT DISTINCT a.line_id
    FROM zxjp_visit_record a
    INNER JOIN xfsg_line_info b ON a.line_id = b.id AND b.deleted = 0
    <where>
      <if test="userId != null and userId != ''">
        AND a.user_id = #{userId}
      </if>
      <if test="regionIds != null and !regionIds.isEmpty()">
        AND b.region_id IN
        <foreach item="regionId" collection="regionIds" separator="," open="(" close=")">
          #{regionId}
        </foreach>
      </if>
      <if test="keyword != null and keyword != ''">
        AND (b.mobile LIKE CONCAT('%', #{keyword}, '%') OR b.username LIKE CONCAT('%', #{keyword}, '%'))
      </if>
    </where>
  </select>

  <select id="getVisitRecordListByLineIds" resultType="com.cool.store.vo.visit.VisitPartnerListVO">
    SELECT line_id, SUM(CASE WHEN status = 2 THEN 1 ELSE 0 END) visit_num
    <if test="userId != null and userId != ''">
      , SUM(CASE WHEN user_id = #{userId} AND status = 2 THEN 1 ELSE 0 END) my_visit_num
    </if>
    FROM zxjp_visit_record
    WHERE line_id IN
    <foreach item="lineId" collection="lineIds" separator="," open="(" close=")">
      #{lineId}
    </foreach>
    GROUP BY line_id
  </select>

  <select id="countStatusStatistics" resultType="com.cool.store.vo.visit.VisitRecordStatusStatisticsVO">
    SELECT
      SUM(CASE
            WHEN a.status = 2
             AND a.visit_date &gt;= DATE_FORMAT(CURDATE(), '%Y-%m-01')
             AND a.visit_date &lt; DATE_ADD(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL 1 MONTH)
            THEN 1 ELSE 0
          END) AS monthCompletedCount,
      SUM(CASE WHEN a.status = 2 THEN 1 ELSE 0 END) AS completedCount,
      SUM(CASE WHEN a.status IN (0, 1) THEN 1 ELSE 0 END) AS uncompletedCount,
      SUM(CASE WHEN a.status = 3 THEN 1 ELSE 0 END) AS invalidCount
    FROM zxjp_visit_record a
    INNER JOIN xfsg_line_info b ON a.line_id = b.id AND b.deleted = 0
    <where>
      <include refid="Visit_Record_Query"/>
    </where>
  </select>
</mapper>