<?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.liquidnet.service.goblin.mapper.GoblinStoreOrderMapper">

    <resultMap id="exportMallOrderDaoResult" type="com.liquidnet.service.goblin.dto.MallOrdertDao">
        <result column="order_id" property="orderId"/>
        <result column="order_code" property="orderCode"/>
        <result column="user_mobile" property="userMobile"/>
        <result column="price_express" property="priceExpress"/>
        <result column="price_coupon" property="priceCoupon"/>
        <result column="store_price_coupon" property="storePriceCoupon"/>
        <result column="express_contacts" property="expressContacts"/>
        <result column="express_phone" property="expressPhone"/>
        <result column="express_detail_address" property="expressDetailAddress"/>
        <result column="spu_id" property="spuId"/>
        <result column="name" property="name"/>
        <result column="cate1_name" property="cate1Name"/>
        <result column="cate2_name" property="cate2Name"/>
        <result column="sku_name" property="skuName"/>
        <result column="num" property="num"/>
        <result column="sku_price" property="skuPrice"/>
        <result column="sku_price_actual" property="skuPriceActual"/>
        <result column="order_sku_id" property="orderSkuId"/>
        <result column="pay_type" property="payType"/>
        <result column="pay_time" property="payTime"/>
        <result column="created_at" property="createdAt"/>
        <result column="logistics_company" property="logisticsCompany"/>
        <result column="mail_no" property="mailNo"/>
    </resultMap>

    <select id="exportMallOrder" resultMap="exportMallOrderDaoResult">
        select
        gso.order_id             as order_id,
        gso.order_code             as order_code,
        gso.user_mobile            as user_mobile,
        gso.price_express          as price_express,
        gso.price_coupon           as price_coupon,
        gso.store_price_coupon     as store_price_coupon,
        goa.express_contacts       as express_contacts,
        goa.express_phone          as express_phone,
        CONCAT(goa.express_address,' ',goa.express_address_detail)       as express_detail_address,
        gso.pay_type               as pay_type,
        gso.pay_time               as pay_time,
        gso.created_at             as created_at,
        gm.logistics_company       as logistics_company,
        gm.mail_no                 as mail_no,
        gos.spu_id                 as spu_id,
        gg.name                    as name,
        gg.cate1_name              as cate1_name,
        gg.cate2_name              as cate2_name,
        gos.sku_name               as sku_name,
        gos.num                    as num,
        gos.sku_price              as sku_price,
        gos.sku_price_actual       as sku_price_actual,
        gos.order_sku_id           as order_sku_id
        from goblin_store_order as gso
        inner join goblin_order_sku as gos on gos.order_id = gso.order_id
        inner join (select spu_id, gg.name, gsgc1.name as cate1_name, gsgc2.name as cate2_name
        from goblin_goods as gg
        left join goblin_self_goods_category as gsgc1 on gsgc1.cate_id = gg.cate_fid
        left join goblin_self_goods_category as gsgc2 on gsgc2.cate_id = gg.cate_sid) as gg
        on gos.spu_id = gg.spu_id
        inner join goblin_order_attr as goa on goa.order_id = gso.order_id
        left join goblin_mail as gm on gm.order_id = gso.order_id
        <where>
                and gso.store_id = #{storeId}
            <if test="beginTime != null and endTime != null">
                and gso.created_at between #{beginTime} and #{endTime}
            </if>
            <if test="states != null">
                and gso.status in
                <foreach collection="states" item="item" index="item" open="(" separator="," close=")">
                    ${item}
                </foreach>
            </if>
            <if test="mailType != null and mailType == 1">
                and gm.mail_no is null
            </if>
            <if test="mailType != null and mailType == 2">
                and gm.mail_no is not null
            </if>
        </where>
        group by gos.order_sku_id
    </select>

</mapper>
