<?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.yiqi.core.mapper.ClothPriceMapper">

    <resultMap type="ClothPrice" id="ClothPriceResult">
        <result property="id" column="id"/>
        <result property="clothId" column="cloth_id"/>
        <result property="storeAreaId" column="store_area_id"/>
        <result property="price" column="price"/>
        <result property="washModeId" column="wash_mode_id"/>
        <result property="clothType" column="cloth_type"/>
        <result property="priceType" column="price_type"/>
        <result property="createById" column="create_by_id"/>
        <result property="createBy" column="create_by"/>
        <result property="createTime" column="create_time"/>
        <result property="updateById" column="update_by_id"/>
        <result property="updateBy" column="update_by"/>
        <result property="updateTime" column="update_time"/>
        <result property="remark" column="remark"/>
    </resultMap>

    <sql id="selectClothPriceVo">
        select id,
               cloth_id,
               store_area_id,
               price,
               wash_mode_id,
               cloth_type,
               price_type,
               create_by_id,
               create_by,
               create_time,
               update_by_id,
               update_by,
               update_time,
               remark
        from cloth_price
    </sql>

    <select id="selectClothPriceList" parameterType="ClothPrice" resultMap="ClothPriceResult">
        <include refid="selectClothPriceVo"/>
        <where>
        </where>
    </select>

    <select id="selectClothPriceById" parameterType="Long"
            resultMap="ClothPriceResult">
        <include refid="selectClothPriceVo"/>
        where id = #{id}
    </select>

    <select id="getClothPriceListByClothType" resultType="com.yiqi.core.domain.vo.ClothPriceQueryByClothTypeVO">
        SELECT
        ab.id AS clothId,
        ab.NAME AS NAME,
        ab.area_id AS storeAreaId,
        ab.area_name AS areaName,
        ab.type_id,
        cp.price AS price
        FROM
        (
        SELECT
        a.*,
        sa.id AS area_id,
        sa.`name` AS area_name
        FROM
        cloth_item a
        LEFT JOIN sys_store_area sa ON 1 = 1
        WHERE
        a.`status` = 0
        AND a.del_flag = 0
        ) AS ab
        LEFT JOIN cloth_price cp ON ab.id = cp.cloth_id
        AND ab.area_id = cp.store_area_id and cp.price_type = 1
        AND ab.type_id = cp.cloth_type where 1=1
        <if test="name != null  and name != ''">
            and ab.name like concat('%', #{name}, '%')
        </if>
        <if test="storeAreaId != null  and storeAreaId != ''">
            and ab.area_id = #{storeAreaId}
        </if>
        <if test="clothType != null  and clothType != ''">
            and ab.type_id = #{clothType}
        </if>
        order by ab.id
    </select>
    <select id="countClothPriceListByClothType" resultType="java.lang.Integer">
        select count(1)
        FROM
        (
        SELECT
        a.*,
        b.id AS area_id,
        b.`name` AS area_name
        FROM
        cloth_item a
        LEFT JOIN sys_store_area b ON 1 = 1
        WHERE
        a.`status` = 0
        AND a.del_flag = 0
        ) AS ab
        LEFT JOIN cloth_price c ON ab.id = c.cloth_id
        AND ab.area_id = c.store_area_id and c.price_type = 1
        AND c.cloth_type = #{clothType}
        <if test="name != null  and name != ''">
            and ab.name like concat('%', #{name}, '%')
        </if>
        <if test="storeAreaId != null  and storeAreaId != ''">
            and ab.area_id = #{storeAreaId}
        </if>
    </select>

    <select id="getClothPriceListByWashMode" resultType="com.yiqi.core.domain.vo.ClothPriceQueryByWashModeVO">
        SELECT
        ab.id AS clothId,
        ab.NAME AS NAME,
        ab.area_id AS storeAreaId,
        ab.area_name AS areaName,
        ab.wash_mode_id AS washModeId,
        ab.wash_name as washName,
        cp.price AS price
        FROM
        (
        SELECT
        a.*,
        sa.id AS area_id,
        sa.`name` AS area_name,
        cwm.id as wash_mode_id,
        cwm.name as wash_name
        FROM
        cloth_item a
        LEFT JOIN sys_store_area sa ON 1 = 1
        LEFT JOIN cloth_wash_mode cwm on 1 = 1
        WHERE
        a.`status` = 0 AND a.del_flag = 0
        and cwm.`status` = 0 and cwm.del_flag = 0
        ) AS ab
        LEFT JOIN cloth_price cp ON ab.id = cp.cloth_id AND ab.area_id = cp.store_area_id and cp.price_type = 0 and
        ab.wash_mode_id = cp.wash_mode_id
        where 1=1
        <if test="name != null  and name != ''">
            and ab.name like concat('%', #{name}, '%')
        </if>
        <if test="washModeId != null  and washModeId != ''">
            and ab.wash_mode_id = #{washModeId}
        </if>
        <if test="storeAreaId != null  and storeAreaId != ''">
            and ab.area_id = #{storeAreaId}
        </if>
        order by ab.id
    </select>
    <select id="countClothPriceListByWashMode" resultType="java.lang.Integer">
        SELECT
        count(1)
        FROM
        (
        SELECT
        a.*,
        sa.id AS area_id,
        sa.`name` AS area_name,
        cwm.id as wash_mode_id,
        cwm.name as wash_name
        FROM
        cloth_item a
        LEFT JOIN sys_store_area sa ON 1 = 1
        LEFT JOIN cloth_wash_mode cwm on 1 = 1
        WHERE
        a.`status` = 0 AND a.del_flag = 0
        and cwm.`status` = 0 and cwm.del_flag = 0
        ) AS ab
        LEFT JOIN cloth_price cp ON ab.id = cp.cloth_id AND ab.area_id = cp.store_area_id and ab.wash_mode_id =
        cp.wash_mode_id and cp.price_type = 0
        where 1=1
        <if test="washModeId != null  and washModeId != ''">
            and ab.wash_mode_id = #{washModeId}
        </if>
        <if test="name != null  and name != ''">
            and ab.name like concat('%', #{name}, '%')
        </if>
        <if test="storeAreaId != null  and storeAreaId != ''">
            and ab.area_id = #{storeAreaId}
        </if>
    </select>


    <insert id="insertClothPrice" parameterType="ClothPrice" useGeneratedKeys="true"
            keyProperty="id">
        insert into cloth_price
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="price != null">price,
            </if>
            <if test="clothId != null">cloth_id,
            </if>
            <if test="storeAreaId != null">store_area_id,
            </if>
            <if test="washModeId != null">wash_mode_id,
            </if>
            <if test="clothType != null">cloth_type,
            </if>
            <if test="priceType != null">price_type,
            </if>
            <if test="createById != null">create_by_id,
            </if>
            <if test="createBy != null">create_by,
            </if>
            <if test="createTime != null">create_time,
            </if>
            <if test="updateById != null">update_by_id,
            </if>
            <if test="updateBy != null">update_by,
            </if>
            <if test="updateTime != null">update_time,
            </if>
            <if test="remark != null">remark,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="price != null">#{price},
            </if>
            <if test="clothId != null">#{clothId},
            </if>
            <if test="storeAreaId != null">#{storeAreaId},
            </if>
            <if test="washModeId != null">#{washModeId},
            </if>
            <if test="clothType != null">#{clothType},
            </if>
            <if test="priceType != null">#{priceType},
            </if>
            <if test="createById != null">#{createById},
            </if>
            <if test="createBy != null">#{createBy},
            </if>
            <if test="createTime != null">#{createTime},
            </if>
            <if test="updateById != null">#{updateById},
            </if>
            <if test="updateBy != null">#{updateBy},
            </if>
            <if test="updateTime != null">#{updateTime},
            </if>
            <if test="remark != null">#{remark},
            </if>
        </trim>
    </insert>

    <update id="updateClothPrice" parameterType="ClothPrice">
        update cloth_price
        <trim prefix="SET" suffixOverrides=",">
            <if test="clothId != null">cloth_id =
                #{clothId},
            </if>
            <if test="storeAreaId != null">store_area_id =
                #{storeAreaId},
            </if>
            <if test="price != null">price =
                #{price},
            </if>
            <if test="washModeId != null">wash_mode_id =
                #{washModeId},
            </if>
            <if test="clothType != null">cloth_type =
                #{clothType},
            </if>
            <if test="priceType != null">price_type =
                #{priceType},
            </if>
            <if test="createById != null">create_by_id =
                #{createById},
            </if>
            <if test="createBy != null">create_by =
                #{createBy},
            </if>
            <if test="createTime != null">create_time =
                #{createTime},
            </if>
            <if test="updateById != null">update_by_id =
                #{updateById},
            </if>
            <if test="updateBy != null">update_by =
                #{updateBy},
            </if>
            <if test="updateTime != null">update_time =
                #{updateTime},
            </if>
            <if test="remark != null">remark =
                #{remark},
            </if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteClothPriceById" parameterType="Long">
        delete
        from cloth_price
        where id = #{id}
    </delete>

    <delete id="deleteClothPriceByIds" parameterType="String">
        delete from cloth_price where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>

    <select id="selectClothPriceSetList" resultType="com.yiqi.core.domain.vo.ClothPriceSetVO">
        select ci.id as id,  ct.name    as type_name,
               ci.name       as name
        from cloth_item ci LEFT JOIN cloth_type ct on ci.type_id = ct.id
        where ci.status = '0'
          and ci.del_flag = '0'
         <if test="clothItem.typeId!=null">
             and ci.type_id = #{clothItem.typeId}
         </if>
        <if test="clothItem.name!=null">
            and ci.name = #{clothItem.name}
        </if>
    </select>
    <select id="getClothPriceListByAreaIdExport" resultType="com.yiqi.core.domain.vo.ClothPriceExportVO">
        select a.cloth_id             as clothId,
               a.cloth_name           as clothName,
               a.type_name            as typeName,
               a.area_id              as areaId,
               a.area_name            as areaName,
               IFNULL(b.price, "未设置") as thickeningPrice,
               IFNULL(c.price, "未设置") as cashmerePrice,
               IFNULL(d.price, "未设置") as silkPrice,
               IFNULL(e.price, "未设置") as childPrice,
               IFNULL(f.price, "未设置") as dryCleaningPrice,
               IFNULL(g.price, "未设置") as washPrice,
               IFNULL(h.price, "未设置") as darningPrice,
               IFNULL(i.price, "未设置") as permPrice
        FROM (
                 select ci.id      as cloth_id,
                        ssa.id     AS area_id,
                        ssa.`name` AS area_name,
                        ci.name    as cloth_name,
                        ct.name    as type_name
                 from cloth_item ci
                          LEFT JOIN cloth_type ct on ci.type_id = ct.id
                          LEFT JOIN sys_store_area ssa ON 1 = 1
                 WHERE ci.del_flag = 0
                   and ci.`status` = 0
             ) as a
                 LEFT JOIN cloth_price b
                           ON a.cloth_id = b.cloth_id and a.area_id = b.store_area_id and b.cloth_type = 0 and
                              b.price_type = 1
                 LEFT JOIN cloth_price c
                           ON a.cloth_id = c.cloth_id and a.area_id = c.store_area_id and c.cloth_type = 1 and
                              c.price_type = 1
                 LEFT JOIN cloth_price d
                           ON a.cloth_id = d.cloth_id and a.area_id = d.store_area_id and d.cloth_type = 2 and
                              d.price_type = 1
                 LEFT JOIN cloth_price e
                           ON a.cloth_id = e.cloth_id and a.area_id = e.store_area_id and e.cloth_type = 3 and
                              e.price_type = 1
                 LEFT JOIN cloth_price f
                           ON a.cloth_id = f.cloth_id and a.area_id = f.store_area_id and f.wash_mode_id = 1 and
                              f.price_type = 0
                 LEFT JOIN cloth_price g
                           ON a.cloth_id = g.cloth_id and a.area_id = g.store_area_id and g.wash_mode_id = 2 and
                              g.price_type = 0
                 LEFT JOIN cloth_price h
                           ON a.cloth_id = h.cloth_id and a.area_id = h.store_area_id and h.wash_mode_id = 3 and
                              h.price_type = 0
                 LEFT JOIN cloth_price i
                           ON a.cloth_id = i.cloth_id and a.area_id = i.store_area_id and i.wash_mode_id = 4 and
                              i.price_type = 0

        where a.area_id = #{areaId}

    </select>

    <select id="selectClothPriceByClothIdAndWashModeId" resultType="com.yiqi.core.domain.ClothPrice">
        select *
        from cloth_price
        where cloth_id = #{clothId}
        and store_area_id = #{storeAreaId}
        and price_type = #{priceType}
        <if test="washModeId!=null">
            and wash_mode_id = #{washModeId}
        </if>
        <if test="clothType!=null">
            and cloth_type = #{clothType}
        </if>
    </select>

    <select id="selectClothPriceGroupByWashMode" resultType="com.yiqi.core.domain.ClothPrice">
        select *
        from cloth_price
        where store_area_id = #{storeAreaId} and price_type = '0'
        group by cloth_id,wash_mode_id
    </select>


    <select id="selectClothPriceGroupByClothType" resultType="com.yiqi.core.domain.ClothPrice">
        select *
        from cloth_price
        where store_area_id = #{storeAreaId} and price_type = '1'
        group by cloth_id, cloth_type
    </select>


</mapper>