<?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.ShopInfoMapper">
  <resultMap id="BaseResultMap" type="com.cool.store.entity.ShopInfoDO">
    <id column="id" jdbcType="BIGINT" property="id" />
    <result column="region_id" jdbcType="BIGINT" property="regionId" />
    <result column="line_id" jdbcType="BIGINT" property="lineId" />
    <result column="partner_id" jdbcType="VARCHAR" property="partnerId" />
    <result column="point_id" jdbcType="BIGINT" property="pointId" />
    <result column="shop_name" jdbcType="VARCHAR" property="shopName" />
    <result column="shop_code" jdbcType="VARCHAR" property="shopCode" />
    <result column="store_num" jdbcType="VARCHAR" property="storeNum" />
    <result column="shop_manager_user_id" jdbcType="VARCHAR" property="shopManagerUserId" />
    <result column="supervisor_user_id" jdbcType="VARCHAR" property="supervisorUserId" />
    <result column="plan_open_time" jdbcType="TIMESTAMP" property="planOpenTime" />
    <result column="cur_progress" jdbcType="DECIMAL" property="curProgress" />
    <result column="shop_type" jdbcType="TINYINT" property="shopType" />
    <result column="shop_stage" jdbcType="TINYINT" property="shopStage" />
    <result column="deleted" jdbcType="BIT" property="deleted" />
    <result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
    <result column="update_time" jdbcType="TIMESTAMP" property="updateTime" />
  </resultMap>

  <sql id="allColumn">
    id, region_id, line_id, partner_id, point_id, shop_name, shop_code, store_num, shop_manager_user_id, supervisor_user_id, plan_open_time, cur_progress, shop_type, shop_stage, deleted, create_time, update_time
  </sql>

  <insert id="batchAddShop" useGeneratedKeys="true" keyProperty="id" keyColumn="id">
    insert into xfsg_shop_info(region_id, line_id, partner_id, shop_name, shop_code) values
    <foreach collection="shopInfoList" item="shop" separator=",">
      (#{shop.regionId}, #{shop.lineId}, #{shop.partnerId}, #{shop.shopName}, #{shop.shopCode})
    </foreach>
  </insert>

  <select id="getShopList" resultMap="BaseResultMap">
    select <include refid="allColumn"/> from xfsg_shop_info where line_id = #{lineId} and deleted= '0'
  </select>

  <select id="getShopInfoByPointId" resultMap="BaseResultMap">
    select <include refid="allColumn"/> from xfsg_shop_info where point_id = #{pointId} and deleted= '0'
  </select>

  <select id="getStageShopCount" resultType="com.cool.store.vo.shop.StageShopCountVO">
    select
      sum(if(shop_stage = 1, 1, 0)) as selectPointCount,
      sum(if(shop_stage = 2, 1, 0)) as buildShopCount,
      sum(if(shop_stage = 3, 1, 0)) as openShopCount
    from
      xfsg_shop_info
    where
      deleted = '0' and line_id = #{lineId}
  </select>

  <update id="unbindPoint">
    update xfsg_shop_info set point_id = null where id = #{shopId}
  </update>

  <select id="getShopListByIds" resultMap="BaseResultMap">
    select
      <include refid="allColumn"/>
    from
      xfsg_shop_info where id in
    <foreach collection="shopIds" item="shopId" separator="," open="(" close=")">
      #{shopId}
    </foreach>
  </select>


  <select id="queryStoreNumeListByid" resultType="com.cool.store.dto.openPreparation.OpenPlanShopInfoDTO">
    select id as shopId,store_num as storeNum
    from xfsg_shop_info
    where 1=1
    <if test="shopIdList != null and shopIdList.size >0">
      and id in
        <foreach collection="shopIdList" separator="," open="(" close=")" item="shopId">
            #{shopId}
        </foreach>
    </if>
  </select>

  <select id="getSelectedShopNum" resultType="com.cool.store.dto.point.LineCountDTO">
    select
      line_id as lineId,
      count(1) as selectedShopNum
    from xfsg_shop_info
    where deleted = 0 and point_id > 0 and line_id in
    <foreach collection="lineIds" item="lineId" index="index" open="(" separator="," close=")">
      #{lineId}
    </foreach>
    group by line_id
  </select>

  <select id="ListByCondition" resultType="com.cool.store.dto.Preparation.PreparationDTO">
    select
    a.id as id,
    a.line_id as lineId,
    a.shop_name  as shopName,
    a.store_num as storeNum,
    a.shop_manager_user_id  as shopManagerUserId,
    a.supervisor_user_id as supervisorUserId,
    a.region_id as regionId,
    b.username as username,
    b.mobile as mobile,
    b.investment_manager as investmentManager,
    DATE_ADD(b.create_time, INTERVAL 50 DAY) as planOpenTime
    from xfsg_shop_info a left join xfsg_line_info b on a.line_id = b.id
    where a.deleted = 0
    <if test="request.keyword != null and request.keyword != ''">
      and (b.username like concat('%', #{request.keyword}, '%') or b.mobile like concat('%', #{request.keyword}, '%'))
    </if>
    <if test="request.shopName!=null and request.shopName!=''">
      and a.shop_name like concat('%', #{request.shopName}, '%')
    </if>
    <if test="request.investmentUserId != null and request.investmentUserId != ''">
      and b.investment_manager = #{request.investmentUserId}
    </if>
    <if test="request.supervisorUserId != null and request.supervisorUserId != ''">
      and a.supervisor_user_id = #{request.supervisorUserId}
    </if>
    <if test="request.planOpenStartTime != null and request.planOpenStartTime != ''">
      and DATE_ADD(b.create_time, INTERVAL 50 DAY) >= #{request.planOpenStartTime}
    </if>
    <if test="request.planOpenEndTime != null and request.planOpenEndTime != ''">
      <![CDATA[and DATE_ADD(b.create_time, INTERVAL 50 DAY) <= #{request.planOpenEndTime}]]>
    </if>
    <if test="request.regionIds != null and request.regionIds.size() > 0">
      and a.region_id in
      <foreach collection="request.regionIds" item="regionId" index="index" open="(" separator="," close=")">
        #{regionId}
      </foreach>
    </if>
    <if test="request.authRegionIds != null and request.authRegionIds.size() > 0">
      and a.region_id in
      <foreach collection="request.authRegionIds" item="regionId" index="index" open="(" separator="," close=")">
        #{regionId}
      </foreach>
    </if>
    order by a.update_time desc
  </select>
  <select id="getRegionIdByid" resultType="java.lang.Long">
    select r.parent_id
    from xfsg_shop_info xsi
           join region_${enterpriseId} r on r.id = xsi.region_id
    where xsi.id = #{shopId}
  </select>
    <select id="selectByStoreNum" resultType="com.cool.store.entity.ShopInfoDO">
      select <include refid="allColumn"/>
      from xfsg_shop_info
      where store_num = #{storeNum}
    </select>
    <select id="selectShopListByRegionId" resultType="com.cool.store.entity.ShopInfoDO">
      select
      xsi.id,xsi.line_id as lineId,xsi.region_id as regionId,xsi.shop_name as shopName,xsi.store_num as storeNum
      from xfsg_shop_info xsi
      left join xfsg_shop_stage_info xssi on xssi.shop_id = xsi.id
      where
       xsi.deleted = 0
      <if test="regionIds != null and regionIds.size() > 0">
        and xsi.region_id in
        <foreach collection="regionIds" item="regionId" index="index" open="(" separator="," close=")">
          #{regionId}
        </foreach>
      </if>
      AND xssi.shop_sub_stage_status != -100
      and xssi.shop_sub_stage = #{shopSubStage}
      <if test="subStageStatus != null and subStageStatus.size()>0">
          and xssi.shop_sub_stage_status in
        <foreach collection="subStageStatus" item="item" index="index" open="(" separator="," close=")">
          #{item}
        </foreach>
      </if>
      <if test="keyWord != null and keyWord != '' ">
        and xsi.shop_name Like concat("%",#{keyWord},"%") or xsi.store_num Like concat("%",#{keyWord},"%")
      </if>
    </select>



</mapper>