<?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.api.app.dao.shoppingCenter.ShoppingDao">

    <select id="list" parameterType="java.lang.Integer" resultType="com.api.vo.app.AppCategoryVo">
        select id,name from shop_category where parent_id = #{parentId}
    </select>
    <select id="findTopGoods" resultType="com.api.vo.app.AppGoodsVo">
        select * from shop_goods where status = 1 and is_delete = 1 order By subscribe_num desc
    </select>
    <select id="findGoodsByCategoryId" parameterType="java.lang.Integer" resultType="com.api.vo.app.AppGoodsVo">
        select * from shop_goods where category_id = #{categoryId} and  status = 1 and is_delete = 1 order By subscribe_num
    </select>
    <select id="findDetailByGoodsId" parameterType="java.lang.Integer" resultType="com.api.vo.app.AppGoodsDetailVo">
        select sg.id,sg.recommend,sg.title,sg.selling_price,sg.marking_price,sc.name as category_name,
        sg.subscribe_num,sg.detail,sg.arrival_time,ss.id as supplier_id,ss.name as supplier_name,ss.tel as supplier_tel,
        ss.address as supplier_address
        from shop_goods as sg,shop_supplier as ss,shop_category as sc
        where sg.category_id = sc.id and sg.supplier_id = ss.id and sg.id = #{goodsId} and  sg.status = 1 and sg.is_delete = 1
    </select>
    <select id="countAppointmentByGIdAndUId" parameterType="com.api.model.app.AppGoodsIdAndUserId" resultType="java.lang.Integer">
        select count(*) from shop_goods_appointment where goods_id = #{goodsId} and create_id = #{UserId}
    </select>
    <select id="findTopGoodsBySupplierId" parameterType="java.lang.Integer" resultType="com.api.vo.app.AppGoodsVo">
        select * from shop_goods where supplier_id = #{supplierId} and  status = 1 and is_delete = 1 order By subscribe_num limit 4
    </select>

    <insert id="goodsAppointment" parameterType="com.api.model.app.AppGoodsAppointment">
        insert into shop_goods_appointment(
            code,
            goods_id,
            status,
            user_name,
            user_tel,
            num,
            create_id,
            create_date
        )values (
            #{code},
            #{goodsId},
            #{status},
            #{userName},
            #{userTel},
            #{num},
            #{createId},
            #{createDate}
        )
    </insert>
    <insert id="insertGoodsOrder" parameterType="com.api.model.app.AppGoodsAppointment">
        insert into shop_goods_appointment(
            code,
            goods_id,
            status,
            user_name,
            user_tel,
            num,
            create_id,
            create_date,
            unit_price,
            pay_type,
            pay_price,
            back_library
        )values (
            #{code},
            #{goodsId},
            #{status},
            #{userName},
            #{userTel},
            #{num},
            #{createId},
            #{createDate},
            #{unitPrice},
            #{payType},
            #{payPrice},
            #{backLibrary}
        )
    </insert>

    <select id="goodsSearch" parameterType="java.lang.String" resultType="com.api.vo.app.AppGoodsVo">
        select * from shop_goods where title like concat('%',#{searchName},'%')
    </select>

    <update id="incGoodsAppointmentNum" parameterType="com.api.model.app.AppGoodsIdAndAppointmentNum">
        update shop_goods
        set subscribe_num = subscribe_num + #{appointmentNum},
        stock = stock - #{appointmentNum}
        where id = #{goodsId} and #{appointmentNum} <![CDATA[ <= ]]> stock
    </update>

    <select id="myOrder" parameterType="com.api.model.shoppingCenter.Order" resultType="com.api.vo.app.AppMyOrderVo">
        select sga.*,sg.title as goods_name,sg.selling_price,sg.marking_price,sg.arrival_time,
        ss.id as supplier_id,ss.name as supplier_name,sc2.name as level_two_category,sc1.name as level_one_category
        from shop_goods_appointment as sga,shop_goods as sg,user_resident as ur,shop_supplier as ss,shop_category as sc2,shop_category as sc1
        <where>
            sga.goods_id = sg.id
            and sga.create_id = ur.id
            and sg.supplier_id = ss.id
            and sg.category_id = sc2.id
            and sc2.parent_id = sc1.id
            <if test="status == 1">
                and sga.status = 1
            </if>
            <if test="status == 2">
                and sga.status = 2
            </if>
            <if test="status == 3">
                and sga.status = 3
            </if>
            <if test="status == 4">
                and sga.status = 4
            </if>
            <if test="status == 6">
                and sga.status <![CDATA[ >= ]]> 3
                and score is not null
            </if>
            <if test="status == 8">
                and sga.status <![CDATA[ >= ]]> 8
            </if>
            and sga.create_id = #{createId}
        </where>
        order by sga.create_date desc
    </select>
    <select id="findOrderDetailByOrderId" parameterType="com.api.model.app.UserIdAndGoodsAppointmentId" resultType="com.api.vo.app.AppMyOrderVo">
        select sga.*,sg.title as goods_name,sg.selling_price,sg.marking_price,sg.arrival_time,
        ss.id as supplier_id,ss.name as supplier_name,sc2.name as level_two_category,sc1.name as level_one_category
        from shop_goods_appointment as sga,shop_goods as sg,user_resident as ur,shop_supplier as ss,shop_category as sc2,shop_category as sc1
        where sga.goods_id = sg.id
            and sga.create_id = ur.id
            and sg.supplier_id = ss.id
            and sg.category_id = sc2.id
            and sc2.parent_id = sc1.id
            and sga.create_id = #{id}
            and sga.id = #{goodsAppointmentId}
    </select>
    <select id="findSellingPriceByGoodsId" parameterType="java.lang.Integer" resultType="java.math.BigDecimal">
        select selling_price from shop_goods where id = #{goodsId}
    </select>
    <select id="findGoodsOrderByCode" parameterType="java.lang.String" resultType="com.api.model.app.AppGoodsAppointment">
        select * from shop_goods_appointment where code = #{outTradeNo}
    </select>

    <select id="findGoodsOrderById" parameterType="java.lang.Integer" resultType="com.api.model.app.AppGoodsAppointment">
        select * from shop_goods_appointment where id = #{goodsAppointmentId}
    </select>

    <select id="findBackGoodsOrder" resultType="com.api.model.app.AppGoodsAppointment">
        select * from shop_goods_appointment where status = -1 and back_library = 0
    </select>
    <select id="findUnPaymentOrder" resultType="com.api.model.app.AppGoodsAppointment">
        select * from shop_goods_appointment where status = -2 and NOW() <![CDATA[ >= ]]> DATE_ADD(create_date,INTERVAL 30 MINUTE)
    </select>

    <delete id="cancel" parameterType="com.api.model.shoppingCenter.Order">
        delete from shop_goods_appointment where id = #{id} and create_id = #{createId} and status = #{status}
    </delete>

    <update id="applicationRefund" parameterType="com.api.model.shoppingCenter.Order">
        update shop_goods_appointment
        <set>
            back_type = #{backType},
            back_date = #{backDate},
            <if test="backReason != null and backReason != ''">
                back_reason = #{backReason},
            </if>
            status = #{status}
        </set>
        where id = #{id} and create_id = #{createId}
    </update>
    <update id="confirmReceipt" parameterType="com.api.model.shoppingCenter.Order">
        update shop_goods_appointment
        <set>
            receiving_date = #{receivingDate},
            status = #{status}
        </set>
        where id = #{id} and create_id = #{createId}
    </update>
    <update id="evaluation" parameterType="com.api.model.shoppingCenter.Evaluation">
        update shop_goods_appointment
        <set>
            score = #{score},
            evaluation_date = #{evaluationDate},
            <if test="evaluationReason != null and evaluationReason != ''">
                evaluation_reason = #{evaluationReason}
            </if>
        </set>
        where id = #{goodsAppointmentId} and create_id = #{id}
    </update>
    <update id="updateSGAStatusByCode" parameterType="com.api.model.app.AppGoodsAppointment">
        update shop_goods_appointment
        set status = #{status}
        where code = #{code}
    </update>
    <update id="decGoodsAppointmentNum" parameterType="com.api.model.app.AppGoodsIdAndAppointmentNum">
        update shop_goods
        set subscribe_num = subscribe_num - #{appointmentNum},
        stock = stock + #{appointmentNum}
        where id = #{goodsId}
    </update>
    <update id="updateBackLibraryByOrderId" parameterType="java.lang.Integer">
        update shop_goods_appointment
        set back_library = 1
        where id = #{id}
    </update>


</mapper>