<?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.production_order_number_name IS NOT NULL

        <!--
            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-->
        fawhd.production_order_number_name
    </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
        months,
        sum( number_of_people_to_arrive ) number_of_people_to_arrive,
        sum( actual_number_of_people ) actual_number_of_people
        FROM
        (
        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' )
        UNION
        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_warehouse
        WHERE
        del_flag = '0'
        AND DATE_FORMAT( date, '%Y' ) = DATE_FORMAT( now( ), '%Y' )
        GROUP BY
        DATE_FORMAT( date, '%m' )
        ) aa
        GROUP BY
        aa.months
    </select>

    <select id="getTotal" resultType="org.jeecg.modules.fbsAbnormalWorkingHours.entity.FbsAbnormalWorkingHours">
        SELECT
            sum( attendance_hours ) AS attendance_hours,
            sum( overtime_hours ) AS overtime_hours,
            sum( other ) AS other,
            sum( quality ) AS quality,
            sum( plan ) AS plan
        FROM
            (
        SELECT
            sum( attendance_hours ) AS attendance_hours,
            sum( overtime_hours ) AS overtime_hours,
            IFNULL( sum( train ), 0 ) + IFNULL( sum( meeting ), 0 ) + IFNULL( sum( research_and_development ), 0 ) + IFNULL( sum( five_s ), 0 ) + IFNULL( sum( pack ), 0 ) + IFNULL( sum( other ), 0 ) AS other,
            IFNULL( sum( quality ), 0 ) + IFNULL( sum( materiel ), 0 ) + IFNULL( sum( equipment ), 0 ) + IFNULL( sum( design ), 0 ) + IFNULL( sum( energy ), 0 ) + IFNULL( sum( plan ), 0 ) quality,
            0 AS plan
        FROM
            fbs_abnormal_working_hours fawh
        WHERE
            del_flag = '0' AND date_format( date,'%Y-%m' )=#{yearMonth}
             UNION
        SELECT
            sum( attendance_hours ) AS attendance_hours,
            sum( overtime_hours ) AS overtime_hours,
            IFNULL( sum( train ), 0 ) + IFNULL( sum( meeting ), 0 ) + IFNULL( sum( five_s ), 0 ) + IFNULL( sum( other ), 0 ) AS other,
            0 AS quality,
            IFNULL( sum( work_order_batching ), 0 ) + IFNULL( sum( storage_finished_products ), 0 ) + IFNULL( sum( storage_raw_materials ), 0 ) + IFNULL( sum( research_and_development ), 0 ) + IFNULL( sum( project_ingredients ), 0 ) + IFNULL( sum( project_delivery ), 0 ) + IFNULL( sum( pack_project ), 0 ) + IFNULL( sum( loading ), 0 )
            + IFNULL( sum( inventory ), 0 )+ IFNULL( sum( accessories ), 0 )+ IFNULL( sum( defray ), 0 ) AS plan
        FROM
            fbs_warehouse
        WHERE
            del_flag = '0' AND date_format( date,'%Y-%m' )=#{yearMonth}
            ) aa
    </select>
</mapper>