<?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.jeecg.modules.fbsAbnormalWorkingHours.mapper.FbsAbnormalWorkingHoursMapper">
    <select id="getQualityType" resultType="org.jeecg.modules.fbsAbnormalWorkingHours.entity.FbsAbnormalWorkingHours">
        SELECT
            sum( quality ) AS quality,
            type_name as typeName
        FROM
            fbs_abnormal_working_hours
        WHERE
            del_flag = '0' and type_name is not null
        GROUP BY
            type_name
    </select>

    <select id="getRejectsNumber" resultType="org.jeecg.modules.fbsAbnormalWorkingHours.entity.FbsAbnormalWorkingHours">
       <!-- SELECT
            fawh.product,
            sum( fawh.rejects_number ) as rejectsNumberTotal
        FROM
            fbs_abnormal_working_hours fawh
            LEFT JOIN fbs_workshop_dispatch_list fwdl ON fawh.workshop_dispatch_list_id = fwdl.id
        WHERE
            fawh.del_flag = '0'
            AND fawh.product IS NOT NULL
            AND date_format( fawh.date, #{dateString} ) = DATE_FORMAT( now( ), #{dateString} )
        GROUP BY
            fawh.product -->
        SELECT
          fawhd.product,
          sum( fawhd.rejects_number ) rejectsNumberTotal,
          fawhd.planned_quantity plannedQuantity
        FROM
          fbs_abnormal_working_hours_describe fawhd
          LEFT JOIN fbs_abnormal_working_hours fawh ON fawhd.abnormal_working_hours_id = fawh.id
        WHERE
            fawhd.del_flag = '0'
            AND fawhd.del_flag = '0'
            AND fawhd.product IS NOT NULL
            AND fawhd.type='产品'
            <if test="dateString=='%Y-%m-%d'">
                AND date_format( fawh.date, #{dateString} ) = DATE_FORMAT(date_sub(curdate(),interval 1 day), #{dateString} )
            </if>
            <if test="dateString=='%Y-%m'">
                AND date_format( fawh.date, #{dateString} ) = DATE_FORMAT( now( ), #{dateString} )
            </if>

        GROUP BY
            fawhd.product,fawhd.planned_quantity
    </select>

    <select id="getRejectsNumberSameDay" resultType="org.jeecg.modules.fbsAbnormalWorkingHours.entity.FbsAbnormalWorkingHours">
        SELECT
        fawh.product,
        sum( fawh.rejects_number ) as rejectsNumberTotal
        FROM
        fbs_abnormal_working_hours fawh
        LEFT JOIN fbs_workshop_dispatch_list fwdl ON fawh.workshop_dispatch_list_id = fwdl.id
        WHERE
        fawh.del_flag = '0'
        AND fawh.product IS NOT NULL
        AND date_format( fawh.date, '%Y-%m-%d' ) = DATE_FORMAT( now( ), '%Y-%m-%d' )
        GROUP BY
        fawh.product
    </select>

    <select id="getMonthAttendance" resultType="org.jeecg.modules.fbsAbnormalWorkingHours.entity.FbsAbnormalWorkingHours">
        SELECT
            DATE_FORMAT( date, '%m' ) months,
            IfNULL( sum( number_of_people_to_arrive ), 0 ) number_of_people_to_arrive,
            IfNULL( sum( actual_number_of_people ), 0 ) actual_number_of_people
        FROM
            fbs_abnormal_working_hours
        WHERE
            del_flag = '0'
            AND DATE_FORMAT( date, '%Y' ) = DATE_FORMAT( now( ), '%Y' )
        GROUP BY
            DATE_FORMAT( date, '%m' )
    </select>
</mapper>