<?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="org.dromara.work.mapper.TpOrderMapper">
    <select id="selectListBySpace" resultType="org.dromara.work.domain.vo.TpOrderVo">
        SELECT * from tp_order where length(space) > 3
    </select>

    <select id="deptRankingList" resultType="org.dromara.work.domain.vo.DeptRankingVo">
        WITH dept_sales AS (
            SELECT
                d.dept_id,
                d.dept_name,
                d.parent_id,
                d.dept_category,
                (SELECT SUM(o.price)
                 FROM tp_order AS o
                 WHERE o.is_del = 1
                    <if test="bo.startTime != null and bo.endTime != null">
                        AND o.add_time BETWEEN #{bo.startTime} AND #{bo.endTime}
                    </if>
                    <if test="bo.isNull == 1">
                        AND o.ancestors LIKE CONCAT('%', d.dept_id, '%')
                    </if>
                    <if test="bo.isNull == 0">
                        AND o.dept_id = d.dept_id
                    </if>
                ) AS total_sales,
                (SELECT SUM(o.pay_price)
                 FROM tp_order AS o
                 WHERE o.is_del = 1
                    <if test="bo.startTime != null and bo.endTime != null">
                        AND o.add_time BETWEEN #{bo.startTime} AND #{bo.endTime}
                    </if>
                    <if test="bo.isNull == 1">
                        AND o.ancestors LIKE CONCAT('%', d.dept_id, '%')
                    </if>
                    <if test="bo.isNull == 0">
                       AND o.dept_id = d.dept_id
                    </if>
                ) AS pay_total,
                (SELECT count(1)
                FROM tp_order AS o
                WHERE o.is_del = 1
                <if test="bo.startTime != null and bo.endTime != null">
                    AND o.add_time BETWEEN #{bo.startTime} AND #{bo.endTime}
                </if>
                <if test="bo.isNull == 1">
                    AND o.ancestors LIKE CONCAT('%', d.dept_id, '%')
                </if>
                <if test="bo.isNull == 0">
                    AND o.dept_id = d.dept_id
                </if>
                ) AS order_count,
                (SELECT SUM(CASE WHEN m.gongsi = 0 THEN m.geren ELSE m.gongsi END) AS result_field FROM tp_month m WHERE m.`month` = #{bo.month}
                    <if test="bo.isNull == 1">
                        AND m.ancestors LIKE CONCAT('%', d.dept_id, '%')
                    </if>
                    <if test="bo.isNull == 0">
                        AND m.dept_id = d.dept_id
                    </if>
                ) AS mu_biao
            FROM sys_dept AS d
                <if test="bo.isNull == 1">
                    WHERE d.parent_id = #{bo.deptId}
                </if>
                <if test="bo.isNull == 0">
                    WHERE d.dept_id = #{bo.deptId}
                </if>
        )
        SELECT
            ds.dept_id,
            ds.dept_name,
            ds.parent_id,
            ds.dept_category,
            ds.total_sales as totalPerformance,
            ds.pay_total as completionAmount,
            ds.order_count as orderCount,
            concat(round((ds.pay_total / ds.total_sales) * 100), '%') AS completionRate,
            concat(round(((ds.total_sales-ds.pay_total) / ds.total_sales) * 100), '%') AS unpaidRate,
            concat(round((ds.pay_total / ds.mu_biao) * 100), '%') AS targetCompletionRate
        FROM dept_sales AS ds
    </select>

    <select id="deptRankingJSList" resultType="org.dromara.work.domain.vo.DeptRankingVo">
        WITH dept_sales AS (
        SELECT
        d.dept_id,
        d.dept_name,
        d.parent_id,
        d.dept_category,
        (SELECT SUM(o.js_price)
        FROM tp_order AS o
        WHERE o.is_del = 1
        <if test="bo.startTime != null and bo.endTime != null">
            AND o.add_time BETWEEN #{bo.startTime} AND #{bo.endTime}
        </if>
        <if test="bo.isNull == 1">
            AND o.ancestors_js LIKE CONCAT('%', d.dept_id, '%')
        </if>
        <if test="bo.isNull == 0">
            AND o.dept_id_js = d.dept_id
        </if>
        ) AS total_sales,
        (SELECT SUM(o.js_pay_price)
        FROM tp_order AS o
        WHERE o.is_del = 1
        <if test="bo.startTime != null and bo.endTime != null">
            AND o.add_time BETWEEN #{bo.startTime} AND #{bo.endTime}
        </if>
        <if test="bo.isNull == 1">
            AND o.ancestors_js LIKE CONCAT('%', d.dept_id, '%')
        </if>
        <if test="bo.isNull == 0">
            AND o.dept_id_js = d.dept_id
        </if>
        ) AS pay_total,
        (SELECT count(1)
        FROM tp_order AS o
        WHERE o.is_del = 1
        <if test="bo.startTime != null and bo.endTime != null">
            AND o.add_time BETWEEN #{bo.startTime} AND #{bo.endTime}
        </if>
        <if test="bo.isNull == 1">
            AND o.ancestors_js LIKE CONCAT('%', d.dept_id, '%')
        </if>
        <if test="bo.isNull == 0">
            AND o.dept_id_js = d.dept_id
        </if>
        ) AS order_count
        FROM sys_dept AS d
            <if test="bo.isNull == 1">
                WHERE d.parent_id = #{bo.deptId}
            </if>
            <if test="bo.isNull == 0">
                WHERE d.dept_id = #{bo.deptId}
            </if>
        )
        SELECT
        ds.dept_id,
        ds.dept_name,
        ds.parent_id,
        ds.dept_category,
        ds.total_sales as totalPerformance,
        ds.pay_total as completionAmount,
        ds.order_count as orderCount,
        concat(round((ds.pay_total / ds.total_sales) * 100), '%') AS completionRate,
        concat(round(((ds.total_sales-ds.pay_total) / ds.total_sales) * 100), '%') AS unpaidRate
        FROM dept_sales AS ds
    </select>

    <select id="kfDayList" resultType="org.dromara.work.domain.vo.OrderDayListVo">
        SELECT
            o.sid,
            DATE_FORMAT(o.add_time, '%Y-%m-%d') AS date,
            SUM(o.price) AS amount
        FROM
            tp_order as o
        WHERE
            o.is_del = 1 AND DATE_FORMAT(o.add_time, '%Y-%m') = #{bo.month}
        <if test="userIds != null and userIds.size() > 0"> and o.sid in
            <foreach collection="userIds" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY
            DATE_FORMAT(o.add_time, '%Y-%m-%d'),o.sid
        ORDER BY o.sid,date
    </select>


    <select id="ftDayList" resultType="org.dromara.work.domain.vo.OrderDayListVo">
        SELECT
        o.fid,
        DATE_FORMAT(o.add_time, '%Y-%m') AS date,
        count(1) AS amount
        FROM
        tp_order as o
        WHERE
        o.is_del = 1 AND DATE_FORMAT(o.add_time, '%Y') = #{bo.year}
        <if test="userIds != null and userIds.size() > 0"> and o.fid in
            <foreach collection="userIds" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY
        DATE_FORMAT(o.add_time, '%Y-%m'),o.fid
        ORDER BY o.fid,date
    </select>

    <select id="jsDayList" resultType="org.dromara.work.domain.vo.OrderDayListVo">
        SELECT
        o.bid,
        DATE_FORMAT(o.add_time, '%Y-%m') AS date,
        SUM(o.js_price) AS amount
        FROM
        tp_order as o
        WHERE
        o.is_del = 1 AND DATE_FORMAT(o.add_time, '%Y') = #{bo.year}
        <if test="userIds != null and userIds.size() > 0"> and o.bid in
            <foreach collection="userIds" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY
        DATE_FORMAT(o.add_time, '%Y-%m'),o.bid
        ORDER BY o.bid,date
    </select>

    <select id="monthArrivedPer" resultType="org.dromara.work.domain.vo.PerSumVo">
        SELECT
            DATE_FORMAT(o.add_time, '%Y-%m-%d') AS date,
            SUM(o.price) AS total_amount,
		    SUM(o.pay_price) pay_amount,
            SUM(o.kfpay) AS kfPayAmount
        FROM
            tp_order as o
        WHERE
            o.is_del = 1
        <if test="month != null">
            AND DATE_FORMAT(o.add_time, '%Y-%m') = #{month}
        </if>
        <if test="deptIds != null and deptIds.size() > 0"> AND o.dept_id in
            <foreach collection="deptIds" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY DATE_FORMAT(o.add_time, '%Y-%m-%d')
        ORDER BY date
    </select>


    <select id="yearArrivedPer" resultType="org.dromara.work.domain.vo.PerSumVo">
        SELECT
        DATE_FORMAT(o.add_time, '%Y-%m') AS date,
        SUM(o.price) AS total_amount,
        SUM(o.pay_price) pay_amount,
        SUM(o.kfpay) AS kfPayAmount
        FROM
        tp_order as o
        WHERE
        o.is_del = 1
        <if test="year != null">
            AND DATE_FORMAT(o.add_time, '%Y') = #{year}
        </if>
        <if test="deptIds != null and deptIds.size() > 0"> AND o.dept_id in
            <foreach collection="deptIds" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY DATE_FORMAT(o.add_time, '%Y-%m')
        ORDER BY date
    </select>

    <select id="monthOrderType" resultType="org.dromara.work.domain.vo.PerCountVo">
        SELECT
            o.type AS type,
            sum(o.price) AS amount
        FROM
            tp_order as o
        WHERE
            o.is_del = 1 AND o.pay_state != 1
        <if test="month != null">
            AND DATE_FORMAT(o.add_time, '%Y-%m') = #{month}
        </if>
        <if test="deptIds != null and deptIds.size() > 0"> AND o.dept_id in
            <foreach collection="deptIds" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY o.type
    </select>

    <select id="monthOrderStyle" resultType="org.dromara.work.domain.vo.PerCountVo">
        SELECT
        o.style AS type,
        sum(o.price) AS amount
        FROM
        tp_order as o
        WHERE
        o.is_del = 1 AND o.pay_state != 1
        <if test="month != null">
            AND DATE_FORMAT(o.add_time, '%Y-%m') = #{month}
        </if>
        <if test="deptIds != null and deptIds.size() > 0"> AND o.dept_id in
            <foreach collection="deptIds" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY o.style
    </select>

    <select id="monthOrderSpace" resultType="org.dromara.work.domain.vo.PerCountVo">
        SELECT
        o.space AS type,
        sum(o.price) AS amount
        FROM
        tp_order as o
        WHERE
        o.is_del = 1 AND o.pay_state != 1
        <if test="month != null">
            AND DATE_FORMAT(o.add_time, '%Y-%m') = #{month}
        </if>
        <if test="deptIds != null and deptIds.size() > 0"> AND o.dept_id in
            <foreach collection="deptIds" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY o.space
    </select>
</mapper>
