src/main/resources/mybatis/system/BusinessReportMapper.xml

微信扫码关注公众号，代码整理好后第一时间通知你。

<?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.ruoyi.project.system.report.mapper.BusinessReportMapper">
    <select id="selectCarByGroupStatus" resultType="com.ruoyi.project.system.report.domain.BusinessReport">
        select count(1)as carSum,
               SUM(CASE WHEN oa_syscarinfo.carStatus = 1 THEN 1 ELSE 0 END) notRentedCar,
               SUM(CASE WHEN oa_syscarinfo.carStatus = 2 THEN 1 ELSE 0 END) leasedOutCar
        from  oa_syscarinfo


    </select>
    <select id="selectCustomerByGroupStatus" resultType="com.ruoyi.project.system.report.domain.BusinessReport">
        SELECT count(oa_customerinfo.customerCard) AS customerSum,
        COUNT(IF(ordetableright.customerCard IS NOT NULL, 1, NULL)) AS leasedOutCustomer,
        COUNT(IF(ordetableright.customerCard IS NOT NULL ,IF(ordetableright.seconds_difference &lt; 0,1,null) , NULL)) AS overdueCustomer
        FROM  oa_customerinfo
        LEFT  JOIN
        (select ordetable.customerCard,ordetable.seconds_difference from
        (select  customerCard,TIME_TO_SEC(NOW()) - TIME_TO_SEC(ENDTIME) AS seconds_difference from  oa_sysordertable
        WHERE  oa_sysordertable.orderStatus=1
        ORDER BY ENDTIME ASC) as ordetable
        GROUP BY  ordetable.customerCard
        ) as ordetableright
        on oa_customerinfo.customerCard=ordetableright.customerCard

    </select>
    <select id="selectMonthlyIncome" resultType="com.ruoyi.project.system.report.domain.BusinessReport">
        SELECT SUM(oa_sysordertable.customerZuCheFei) AS monthlyIncome
        FROM  oa_sysordertable
        WHERE YEAR(oa_sysordertable.operationTime) = YEAR(CURRENT_DATE)
          AND MONTH(operationTime) = MONTH(CURRENT_DATE);

    </select>
    <select id="selectOverdueQuantity" resultType="com.ruoyi.project.system.report.domain.BusinessReport">
        select  count(*) AS OverdueQuantity from (
   SELECT carNumber,
   MAX(carTargetBaoXianRiqi) AS carTargetBaoXianRiqi,carBaoXianTixingDay,
 CASE WHEN CURDATE() > carTargetBaoXianRiqi THEN DATEDIFF(CURDATE(), carTargetBaoXianRiqi)
  ELSE NULL END AS daysdifference
  FROM(select id,carNumber,carTargetBaoXianRiqi,carBaoXianTixingDay from  oa_sysbaoxianinfotabel as temp
  order  by  carTargetBaoXianRiqi desc ) as righttable
 GROUP BY  carNumber HAVING daysdifference IS NOT NULL AND daysdifference > carBaoXianTixingDay ) as tableinfo



    </select>
</mapper>