<?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.gree.mall.manager.commonmapper.workorder.OrderBaseCMapper">

    <select id="countOrderStatus" resultType="java.util.Map">
        select
          count(distinct a.id) 'total',
          order_status 'orderStatus',
          order_status_text 'orderStatusText'
        from pg_order_base a
          where 1=1
        <if test="companyWechatId != null and companyWechatId !=''">
            and a.company_wechat_id = #{companyWechatId}
        </if>
        <if test="adminWebsitIds != null and adminWebsitIds.size > 0">
            AND (
            a.websit_id IN
            <foreach item="item" index="index" collection="adminWebsitIds" open="(" separator="," close=")">
                #{item}
            </foreach>
            or
            a.create_websit_id IN
            <foreach item="item" index="index" collection="adminWebsitIds" open="(" separator="," close=")">
                #{item}
            </foreach>
            )
        </if>
        <if test="orderSmallType != null and orderSmallType != ''">
            and a.order_small_type = #{orderSmallType}
        </if>
        <if test="orderSmallTypeText != null and orderSmallTypeText != ''">
            and a.order_small_type_text = #{orderSmallTypeText}
        </if>

        <if test="startTime != null and startTime != ''">
            and a.create_time between #{startTime} and #{endTime}
        </if>
        and a.order_status not in ('DYY','DJD')
        group by order_status_text
    union
        select
            count(distinct a.id)  'total',
            'YCD' as 'orderStatus',
            '异常待处理' as 'orderStatusText'
        from pg_order_base a
        where is_exception=1 and a.order_status NOT IN ('YWG','YWGO','YJS')
        <if test="companyWechatId != null and companyWechatId !=''">
            and a.company_wechat_id = #{companyWechatId}
        </if>
        <if test="adminWebsitIds != null and adminWebsitIds.size > 0">
            AND (
            a.websit_id IN
            <foreach item="item" index="index" collection="adminWebsitIds" open="(" separator="," close=")">
                #{item}
            </foreach>
            or
            a.create_websit_id IN
            <foreach item="item" index="index" collection="adminWebsitIds" open="(" separator="," close=")">
                #{item}
            </foreach>
            )
        </if>
        <if test="orderSmallType != null and orderSmallType != ''">
            and a.order_small_type = #{orderSmallType}
        </if>
        <if test="orderSmallTypeText != null and orderSmallTypeText != ''">
            and a.order_small_type_text = #{orderSmallTypeText}
        </if>
        <if test="startTime != null and startTime != ''">
            and a.create_time between #{startTime} and #{endTime}
        </if>
        union
        select
        count(distinct a.id)  'total',
        'DJD' as 'orderStatus',
        '待接单' as 'orderStatusText'
        from pg_order_base a
        where (is_meet=0 or a.order_status  IN ('DJD')) and a.order_status not in ('YQX','FWQX','FL','FWZT','YCGB')
        <if test="companyWechatId != null and companyWechatId !=''">
            and a.company_wechat_id = #{companyWechatId}
        </if>
        <if test="adminWebsitIds != null and adminWebsitIds.size > 0">
            AND (
            a.websit_id IN
            <foreach item="item" index="index" collection="adminWebsitIds" open="(" separator="," close=")">
                #{item}
            </foreach>
            or
            a.create_websit_id IN
            <foreach item="item" index="index" collection="adminWebsitIds" open="(" separator="," close=")">
                #{item}
            </foreach>
            )
        </if>
        <if test="orderSmallType != null and orderSmallType != ''">
            and a.order_small_type = #{orderSmallType}
        </if>
        <if test="orderSmallTypeText != null and orderSmallTypeText != ''">
            and a.order_small_type_text = #{orderSmallTypeText}
        </if>
        <if test="startTime != null and startTime != ''">
            and a.create_time between #{startTime} and #{endTime}
        </if>
    union
        select
            count(distinct a.id)  'total',
            'DYY' as 'orderStatus',
            '待预约' as 'orderStatusText'
        from pg_order_base a
        where appointment_time is null and a.order_status not in ('YWG','GCSZX','WDWG','YWGO','YQX','FWZT','YCGB',
        'FWQX','FL','YJS','LRCD','DSHPG','CJ','YPD','DXSPD','DZBPG','DWDSPGP','DXSSPGP','DTJXSSPGP','DZBSPGP'
        )
        <if test="companyWechatId != null and companyWechatId !=''">
            and a.company_wechat_id = #{companyWechatId}
        </if>
        <if test="adminWebsitIds != null and adminWebsitIds.size > 0">
            AND (
            a.websit_id IN
            <foreach item="item" index="index" collection="adminWebsitIds" open="(" separator="," close=")">
                #{item}
            </foreach>
            or
            a.create_websit_id IN
            <foreach item="item" index="index" collection="adminWebsitIds" open="(" separator="," close=")">
                #{item}
            </foreach>
            )
        </if>
        <if test="orderSmallType != null and orderSmallType != ''">
            and a.order_small_type = #{orderSmallType}
        </if>
        <if test="orderSmallTypeText != null and orderSmallTypeText != ''">
            and a.order_small_type_text = #{orderSmallTypeText}
        </if>
        <if test="startTime != null and startTime != ''">
            and a.create_time between #{startTime} and #{endTime}
        </if>
    union
        select
        count(distinct a.id)  'total',
        'PJSQZ' AS 'orderStatus',
        '配件申请中' AS 'orderStatusText'
        from pg_order_base a  join websit_parts_apply b on a.id = b.order_base_id
        where b.status = 'ING'
        <if test="companyWechatId != null and companyWechatId !=''">
            and a.company_wechat_id = #{companyWechatId}
        </if>
        <if test="adminWebsitIds != null and adminWebsitIds.size > 0">
            AND (
            a.websit_id IN
            <foreach item="item" index="index" collection="adminWebsitIds" open="(" separator="," close=")">
                #{item}
            </foreach>
            or
            a.create_websit_id IN
            <foreach item="item" index="index" collection="adminWebsitIds" open="(" separator="," close=")">
                #{item}
            </foreach>
            )
        </if>
        <if test="orderSmallType != null and orderSmallType != ''">
            and a.order_small_type = #{orderSmallType}
        </if>
        <if test="orderSmallTypeText != null and orderSmallTypeText != ''">
            and a.order_small_type_text = #{orderSmallTypeText}
        </if>
        <if test="startTime != null and startTime != ''">
            and a.create_time between #{startTime} and #{endTime}
        </if>
    union
        select
        count(distinct a.id)  'total',
        'PJYDH' AS 'orderStatus',
        '配件已到货' as 'orderStatusText'
        from pg_order_base a join websit_parts_apply b on a.id = b.order_base_id
        where b.status = 'END'
        <if test="companyWechatId != null and companyWechatId !=''">
            and a.company_wechat_id = #{companyWechatId}
        </if>
        <if test="adminWebsitIds != null and adminWebsitIds.size > 0">
            AND (
            a.websit_id IN
            <foreach item="item" index="index" collection="adminWebsitIds" open="(" separator="," close=")">
                #{item}
            </foreach>
            or
            a.create_websit_id IN
            <foreach item="item" index="index" collection="adminWebsitIds" open="(" separator="," close=")">
                #{item}
            </foreach>
            )
        </if>
        <if test="orderSmallType != null and orderSmallType != ''">
            and a.order_small_type = #{orderSmallType}
        </if>
        <if test="orderSmallTypeText != null and orderSmallTypeText != ''">
            and a.order_small_type_text = #{orderSmallTypeText}
        </if>
        <if test="startTime != null and startTime != ''">
            and a.create_time between #{startTime} and #{endTime}
        </if>
    union
        select
        count(distinct a.id)  'total',
        'PJYQX' AS 'orderStatus',
        '配件已取消' as 'orderStatusText'
        from pg_order_base a join websit_parts_apply b on a.id = b.order_base_id
        where b.status = 'CANCEL'
        <if test="companyWechatId != null and companyWechatId !=''">
            and a.company_wechat_id = #{companyWechatId}
        </if>
        <if test="adminWebsitIds != null and adminWebsitIds.size > 0">
            AND (
            a.websit_id IN
            <foreach item="item" index="index" collection="adminWebsitIds" open="(" separator="," close=")">
                #{item}
            </foreach>
            or
            a.create_websit_id IN
            <foreach item="item" index="index" collection="adminWebsitIds" open="(" separator="," close=")">
                #{item}
            </foreach>
            )
        </if>
        <if test="orderSmallType != null and orderSmallType != ''">
            and a.order_small_type = #{orderSmallType}
        </if>
        <if test="orderSmallTypeText != null and orderSmallTypeText != ''">
            and a.order_small_type_text = #{orderSmallTypeText}
        </if>
        <if test="startTime != null and startTime != ''">
            and a.create_time between #{startTime} and #{endTime}
        </if>
    </select>

    <select id="countOrderBaseIndex" resultType="com.gree.mall.manager.bean.workorder.OrderBaseCountBean">
        select
            count(if(order_status in ('DSHPG','CJ','YPD','DXSPD','DZBPG','DWDSPGP','DXSSPGP','DTJXSSPGP','DZBSPGP','DFZXPD','DFZXSPGP','DWDPG','DWDPD'),1,null)) as 'dpg',
            count(if((a.is_meet =0 or a.order_status  IN ('DJD')) and a.order_status not in ('YQX','FWQX','FL','FWZT','YCGB'),1,null)) as 'djs',
            count(if(order_status in ('DQD'),1,null)) as 'dqd',
            count(if(order_status in ('FWZ','GCSZT','BFWG','DSM','YZP','WDBH','XSBH','TJXSBH','ZBBH','FZXBH'),1,null)) as 'fwz',
            count(if(appointment_time is null  and a.order_status not in ('YWG','GCSZX','WDWG','YWGO','YQX','FWZT','YCGB',
        'FWQX','FL','YJS','LRCD','DSHPG','CJ','YPD','DXSPD','DZBPG','DWDSPGP','DXSSPGP','DTJXSSPGP','DZBSPGP'
        ),1,null)) as 'dyy',
            count(if(is_exception = 1 and a.order_status not in  ('YWG','YWGO','YJS'),1,null)) as 'ycdcl',
            count(if(appraise_status in ('C'),1,null)) as 'cp'
        from pg_order_base a
        where 1=1
        <if test="companyWechatId != null and companyWechatId !=''">
            and a.company_wechat_id = #{companyWechatId}
        </if>
        <if test="adminWebsitIds != null and adminWebsitIds.size > 0">
            AND (
            a.websit_id IN
            <foreach item="item" index="index" collection="adminWebsitIds" open="(" separator="," close=")">
                #{item}
            </foreach>
            or
            a.create_websit_id IN
            <foreach item="item" index="index" collection="adminWebsitIds" open="(" separator="," close=")">
                #{item}
            </foreach>
            )
        </if>
    </select>

    <sql id="orderBaseSqlWhere">
        <if test="companyWechatId != null and companyWechatId !=''">
            and a.company_wechat_id = #{companyWechatId}
        </if>
        <if test="adminWebsitIds != null and adminWebsitIds.size > 0 and adminUserType != null and adminUserType == 0">
            AND (
            a.websit_id IN
            <foreach item="item" index="index" collection="adminWebsitIds" open="(" separator="," close=")">
                #{item}
            </foreach>
            or
            a.create_websit_id IN
            <foreach item="item" index="index" collection="adminWebsitIds" open="(" separator="," close=")">
                #{item}
            </foreach>
            )
        </if>
    </sql>

    <select id="countOrderStatus2" resultType="java.util.Map">
        SELECT
        COUNT(DISTINCT a.id) 'total',
        order_status 'orderStatus',
        order_status_text 'orderStatusText'
        FROM pg_order_base a JOIN pg_order_product b ON a.id = b.order_base_id
            JOIN pg_order_settle_norm c ON b.id = c.pg_order_product_id AND c.settle_norm_type IN ('INSTALL','REPAIR')
        WHERE 1=1
        <include refid="orderBaseSqlWhere"></include>
        <if test="orderSmallTypeText != null and orderSmallTypeText != ''">
            AND a.order_small_type_text = #{orderSmallTypeText}
        </if>
        GROUP BY order_status_text
    </select>

</mapper>