<?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.tmerclub.cloud.product.mapper.CategoryShopMapper">
  <resultMap id="categoryShopMap" type="com.tmerclub.cloud.product.model.CategoryShop">
    <id column="category_shop_id" property="categoryShopId" />
    <result column="create_time" property="createTime"/>
    <result column="update_time" property="updateTime"/>
    <result column="shop_id" property="shopId"/>
    <result column="category_id" property="categoryId"/>
    <result column="rate" property="rate"/>
    <result column="qualifications" property="qualifications"/>
    <result column="sys_type" property="sysType"/>
  </resultMap>
  <resultMap id="categoryShopVOMap" type="com.tmerclub.cloud.product.vo.CategoryShopVO">
    <id column="category_id" property="categoryId" />
    <result column="name" property="name" />
    <result column="parent_id" property="parentId" />
    <result column="parent_name" property="parentName" />
    <result column="platform_rate" property="platformRate" />
    <result column="customize_rate" property="customizeRate" />
    <result column="qualifications" property="qualifications" />
  </resultMap>
  <sql id="Vo_Column_List">
    `category_shop_id`,`create_time`,`update_time`,`shop_id`,`category_id`,`rate`,`qualifications`,`sys_type`
  </sql>

  <select id="getByCategoryShopId" resultMap="categoryShopMap">
    select <include refid="Vo_Column_List"/> from category_shop where category_shop_id = #{categoryShopId}
  </select>
  <select id="listByShopId" resultMap="categoryShopVOMap">
    SELECT
      cs.category_shop_id,
      cl1.name AS name,
      c1.category_id,
      c1.parent_id,
      c1.status AS categoryStatus,
      cl2.name AS parent_name,
      c1.deduction_rate AS platform_rate,
      cs.rate AS customize_rate,
      cs.qualifications
    FROM
      `category_shop` cs
        JOIN category c1 ON cs.category_id = c1.category_id
        JOIN category_lang cl1 ON c1.category_id = cl1.category_id AND cl1.lang = #{lang}
        JOIN category c2 ON c1.parent_id = c2.category_id
        JOIN category_lang cl2 ON c2.category_id = cl2.category_id AND cl2.lang = #{lang}
    WHERE cs.shop_id = #{shopId} and cs.sys_type = #{sysType}
    ORDER BY c1.status DESC, cs.category_shop_id DESC
  </select>
  <insert id="saveBatch">
    insert into category_shop (`category_shop_id`,`shop_id`,`category_id`,`rate`,`qualifications`,`sys_type`) values
    <foreach collection="categoryShopList" item="categoryShop" separator=",">
        (#{categoryShop.categoryShopId},#{shopId},#{categoryShop.categoryId},#{categoryShop.rate},#{categoryShop.qualifications},#{sysType})
    </foreach>
  </insert>

  <delete id="deleteById">
    delete from category_shop where category_shop_id = #{categoryShopId}
  </delete>
  <delete id="deleteByShopId">
    delete from category_shop where shop_id = #{shopId} and sys_type = #{sysType}
  </delete>
  <delete id="deleteByCategoryId">
    delete from category_shop where category_id = #{categoryId}
  </delete>
  <select id="listRateByShopId" resultType="com.tmerclub.cloud.product.bo.CategoryRateBO">
    SELECT cs.category_id,IFNULL(cs.rate, c.deduction_rate) rate
    FROM category_shop cs
    JOIN category c ON cs.category_id = c.category_id
    WHERE cs.shop_id = #{shopId} and c.level = 2 and cs.sys_type = #{sysType}
  </select>
  <select id="listShopIdByCategoryId" resultType="java.lang.Long">
    select shop_id from category_shop where category_id = #{categoryId}
  </select>
  <select id="listShopIdByCategoryIds" resultType="java.lang.Long">
    select distinct(shop_id) from category_shop
    where category_id in
    <foreach collection="categoryIds" item="categoryId" open="(" close=")" separator=",">
      #{categoryId}
    </foreach>
  </select>
  <select id="getByShopIdAndCategoryId" resultType="com.tmerclub.cloud.product.vo.CategoryShopVO">
    select <include refid="Vo_Column_List"/> from category_shop where shop_id = #{shopId} and category_id = #{categoryId} and sys_type = #{sysType}
  </select>

  <select id="getSupplierIdsByShopId" resultType="long">
    SELECT shop_id FROM `category_shop`
    WHERE sys_type = 3 AND category_id IN (
      SELECT category_id FROM `category_shop`
      WHERE shop_id = #{shopId} AND sys_type = 1
    )
    GROUP BY shop_id
  </select>

  <select id="getCategoryIdsByShopId" resultType="long">
    SELECT category_id FROM `category_shop`
    WHERE sys_type = #{sysType} and shop_id = #{shopId}
  </select>

  <select id="countByShopIdAndCategoryId" resultType="java.lang.Integer">
    select count(*) from category_shop
    <where>
      <if test="shopId != null and shopId != ''">
        and shop_id = #{shopId}
      </if>
      <if test="categoryId != null and categoryId != ''">
        and category_id = #{categoryId}
      </if>
      <if test="sysType != null">
        and sys_type = #{sysType}
      </if>
    </where>
  </select>

  <select id="getSupplyIdByCategoryIds" resultType="java.lang.Long">
    SELECT DISTINCT shop_id FROM category_shop WHERE sys_type = 3 AND category_id  IN
    <foreach collection="categoryIds" item="categoryId" open="(" separator="," close=")">
      #{categoryId}
    </foreach>
  </select>
</mapper>
