<?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.cyksj.mapper.shop.YhShopMapper">

    <select id="selectSoldMaxGoods" resultType="java.lang.String">
        select title
        from goods_don
        where id = (select od.goods_id
                    from (select id from yh_shop where user_id = #{userId} limit 1) s
                             inner join order_don od
                                        on od.yhs_id = s.id and od.status not in ('close', 'noPayment', 'refund')
                    group by od.goods_id
                    order by max(od.money) desc limit 1)
    </select>

    <select id="getApplyDetailByUserId" resultType="com.cyksj.model.views.YhShopDistributeView">
        select ifnull(sum(da.money), 0) as gotMoney, u.yhs_money as availableMoney
        from (select id, yhs_money from user where id = #{userId} limit 1) u
                 left join yh_shop_distribute_money_apply da on da.user_id = u.id and verify_status = 'success'
        group by u.id, u.yhs_money
    </select>

    <select id="selectYhShopDetail" resultType="java.util.Map">
        select count(distinct ys.user_id) as numOfYhShop,
               count(distinct us.user_id) as numOfRegisterYhShopUser
        from (select user_id from `yh_shop` ys where
        verify_status = 'success'
        <if test="startDate != null">
            and created_time >= #{startDate}
        </if>
        <if test="endDate != null">
            and created_time &lt; #{endDate}
        </if>) ys left join yh_shop_user_distribute_shared us on us.shop_user_id = ys.user_id
    </select>

    <select id="selectYhShopTakeMoney" resultType="java.math.BigDecimal">
        select ifnull(sum(money),0) from `yh_shop_distribute_waiting_send_points` where send_status in
        ('waiting','success')
        <if test="startDate != null">
            and created_time >= #{startDate}
        </if>
        <if test="endDate != null">
            and created_time &lt; #{endDate}
        </if>
    </select>

    <select id="selectYhShopApplyMoney" resultType="java.util.Map">
        select ifnull(sum(if(verify_status = 'success',money,0)),0)s,
        ifnull(sum(if(verify_status = 'verifying',money,0)),0)vf
        from yh_shop_distribute_money_apply where verify_status in ('verifying','success')
        <if test="startDate != null">
            and created_time >= #{startDate}
        </if>
        <if test="endDate != null">
            and created_time &lt; #{endDate}
        </if>
    </select>

    <select id="selectRelationBusiness" resultType="com.cyksj.model.entity.YhShopDistributeMoneyApply">
        select ys.business_id, cu.nickname as businessName
        from (select * from yh_shop where user_id = #{userId} limit 1) ys
                 left join cms_user cu on cu.id = ys.business_id
    </select>

    <select id="getYhShopSkuSysStatusDownInfo" resultType="com.cyksj.model.dto.YhShopSkuDownInfo">
        select any_value(g.title) as title, group_concat(distinct sku.spec_val SEPARATOR '') as specVal
        from (select goods_id, sku_id
              from yh_shop_sku_sys_take_down_record
              where user_id = #{userId}
                and deleted is true) s
                 inner join goods_don_sku sku on sku.id = s.sku_id
                 inner join goods_don g on g.id = s.goods_id
        group by g.id
    </select>
</mapper>