<?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="cn.xluobo.business.sc.order.repo.mapper.OrderReportMapper">


    <select id="selectMonthCourseIncomeList"
            resultType="cn.xluobo.business.sc.order.domain.resp.RespReportMonthCourseIncome">
        select a.month,a.course_id,a.fee,b.course_name from (
            select date_format(a.handle_date,'%Y%m') as month, b.course_id, ifnull(sum(b.actual_fee),0) as fee
            from sc_order a,sc_order_detail b
            where a.order_id=b.order_id
              and b.order_detail_status='2'
              and a.order_status='2'
              <if test="deptId != null">
                  and b.dept_id=#{deptId}
              </if>
              <if test="deptIds != null">
                  <foreach collection="deptIds" separator="," open="and b.dept_id in (" close=")" item="deptId">
                    #{deptId}
                  </foreach>
              </if>
              <if test="courseIds != null">
                  <foreach collection="courseIds" separator="," open="and b.course_id in (" close=")" item="courseId">
                    #{courseId}
                  </foreach>
              </if>
              and date_format(a.handle_date,'%Y%m') between #{beginMonth} and #{endMonth}
            group by date_format(a.handle_date,'%Y%m'), b.course_id
        ) a left join sc_course b on a.course_id=b.course_id
        order by a.month desc,a.course_id
    </select>
</mapper>
