<?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.fbsWorkshopDispatchList.mapper.FbsWorkshopDispatchListMapper">
    <update id="updateState">
        update fbs_workshop_dispatch_list set state =#{state} where id = #{id}
    </update>

    <select id="selNowDispatchList" resultType="org.jeecg.modules.fbsWorkshopDispatchList.entity.FbsWorkshopDispatchList">
        SELECT
            a.*,b.first_time as firstTime
        FROM
            fbs_workshop_dispatch_list a
            left join fbs_working_statistics b on a.id = b.pk_workshop_dispatch_list and b.del_flag='0'
        WHERE
            a.del_flag = '0' and a.state ='1'

    </select>

    <select id="getDispatchListByDate" resultType="org.jeecg.modules.fbsWorkshopDispatchList.entity.FbsWorkshopDispatchList">
        SELECT
            *
        FROM
            fbs_workshop_dispatch_list
        WHERE
            del_flag = '0'
            AND date_format( date, '%Y-%m-%d' ) = DATE_FORMAT( #{date}, '%Y-%m-%d' )
        GROUP BY
            product
    </select>
    
    <select id="getSumDispatch" resultType="org.jeecg.modules.fbsWorkshopDispatchList.entity.FbsWorkshopDispatchList">
        SELECT
            *,
            sum( number_of_tasks ) AS numberOfTasksTotal,
            sum( standard_working_hours ) AS standardWorkingHoursTotal,
            sum(man_hours_completed) as manHoursCompletedTotal
        FROM
            fbs_workshop_dispatch_list
            where del_flag='0'
        GROUP BY
            production_order_number_id,product_id
        ORDER BY
            production_line_name
    </select>

    <select id="getDispatchClose" resultType="org.jeecg.modules.fbsWorkshopDispatchList.entity.FbsWorkshopDispatchList">
        SELECT
            fwdl.* ,fws.qualified_number,fws.end_date,convert(fwdl.man_hours_completed/fwdl.standard_working_hours,decimal(15,2))*100 as productionEfficiency
            ,convert(fws.qualified_number/fwdl.number_of_tasks,decimal(15,2))*100 as achievementRate
        FROM
            fbs_workshop_dispatch_list fwdl
            left join fbs_working_statistics fws on fwdl.id=fws.pk_workshop_dispatch_list
        WHERE
            fwdl.del_flag = '0' and fws.del_flag='0' and fws.final_state='3'
            <if test="startDate!=null">
                <![CDATA[   and DATE_FORMAT(fws.end_date, '%Y-%m-%d')>=  #{startDate}]]>
            </if>
            <if test="endDate!=null">
                <![CDATA[  and DATE_FORMAT(fws.end_date, '%Y-%m-%d') <= #{endDate} ]]>
            </if>
    </select>

    <select id="getProductHoursMonthReport" resultType="org.jeecg.modules.fbsWorkshopDispatchList.entity.FbsWorkshopDispatchList">
       SELECT
            sum( standard_working_hours ) as productTotal,product
        FROM
            fbs_workshop_dispatch_list
        WHERE
            del_flag = '0'
            AND date_format( date, #{dateString} ) = DATE_FORMAT( now( ), #{dateString} )
        GROUP BY
            product
    </select>

    <select id="getAbnormalHoursMonthReport" resultType="java.util.HashMap">
        SELECT
            sum( quality ) AS '质量',
            sum( design ) AS '设计',
            sum( rework ) AS '返工',
            sum( materiel ) AS '物料',
            sum( energy ) AS '能源',
            sum( other ) AS '其他',
            sum( equipment ) AS '设备',
            sum( plan ) AS '计划'
        FROM
            fbs_abnormal_working_hours
        WHERE
            del_flag = '0'
            AND date_format( date, #{dateString} ) = DATE_FORMAT( now( ),  #{dateString} )
    </select>

    <select id="getAbnormalHoursMonthReportSameDay" resultType="java.util.HashMap">
        SELECT
        sum( quality ) AS '质量',
        sum( design ) AS '设计',
        sum( rework ) AS '返工',
        sum( materiel ) AS '物料',
        sum( energy ) AS '能源',
        sum( other ) AS '其他',
        sum( equipment ) AS '设备',
        sum( plan ) AS '计划'
        FROM
        fbs_abnormal_working_hours
        WHERE
        del_flag = '0'
        AND date_format( date, '%Y-%m-%d' ) = DATE_FORMAT( now( ), '%Y-%m-%d' )
    </select>

    <select id="getAttendance" resultType="java.util.HashMap">
        SELECT
            (
        SELECT
            count( personnel_code )
        FROM
            fbs_working_statistics
        WHERE
            del_flag = '0'
            AND date_format( start_date, '%Y' ) = date_format( now( ), '%Y' )
            AND date_format( fwdl.planned_start_time, '%Y-%m' ) = date_format( start_date, '%Y-%m' )
        GROUP BY
            date_format( start_date, '%Y-%m' )
            ) as sum1,
            count( personnel_id_u8 ) as sum2,
            date_format( fwdl.planned_start_time, '%Y-%m' ) as label
        FROM
            fbs_workshop_dispatch_list fwdl
        WHERE
            del_flag = '0'
            AND date_format( planned_start_time, '%Y' ) = date_format( now( ), '%Y' )
        GROUP BY
            date_format( planned_start_time, '%Y-%m' )
    </select>

    <select id="getWorkingHoursContrast" resultType="org.jeecg.modules.fbsWorkshopDispatchList.entity.FbsWorkshopDispatchList">
        SELECT
            sum( fwdl.man_hours_completed ) AS manHoursCompleted,
            sum( fwdl.standard_working_hours ) AS standardWorkingHours,
            fwdl.product
        FROM
            fbs_workshop_dispatch_list fwdl
            LEFT JOIN fbs_working_statistics fws ON fwdl.id = fws.pk_workshop_dispatch_list
        WHERE
            fwdl.del_flag = '0'
            AND fws.del_flag = '0'
            AND fws.final_state = '3'
            AND date_format( fws.end_date, '%Y-%m' ) = date_format( now( ), '%Y-%m' )
        GROUP BY
            fwdl.product
    </select>

    <!--   需要加入当日时间判断   -->
    <select id="getProductionEfficiency" resultType="java.lang.String">
        SELECT CONVERT
            ( count( fws.pk_workshop_dispatch_list ) / count( fwdl.id ), DECIMAL ( 15, 2 ) ) * 100
        FROM
            fbs_workshop_dispatch_list fwdl
            LEFT JOIN ( SELECT pk_workshop_dispatch_list FROM fbs_working_statistics WHERE del_flag = '0' GROUP BY pk_workshop_dispatch_list ) fws ON fwdl.id = fws.pk_workshop_dispatch_list
        WHERE
            fwdl.del_flag = '0'

    </select>

    <select id="getAchievementRate" resultType="java.util.HashMap">
        SELECT
            sum( fws.qualified_number ) AS '已完成',
            sum( fwd.number_of_tasks ) - sum( fws.qualified_number ) AS '未完成'
        FROM
            fbs_workshop_dispatch_list fwd
            LEFT JOIN fbs_working_statistics fws ON fwd.id = fws.pk_workshop_dispatch_list
            AND fws.del_flag = '0'
            AND fws.final_state = '3'
        WHERE
            fwd.del_flag = '0'
            and DATE_FORMAT(now( ),'%Y-%m-%d') <![CDATA[ >= ]]> DATE_FORMAT(fwd.planned_start_time,'%Y-%m-%d')
            and DATE_FORMAT(now( ),'%Y-%m-%d') <![CDATA[ <= ]]> DATE_FORMAT(fwd.planned_end_time,'%Y-%m-%d')
        <!--   需要加入单日时间判断 派工单根据计划时间 现场单根据完工时间  -->
    </select>

    <select id="getAttendanceOfTheDay" resultType="java.util.HashMap">
        SELECT
            count( pdl.pk_workshop_dispatch_list ) AS '考勤人数',
            count( fwdl.id ) - count( pdl.pk_workshop_dispatch_list ) AS '未考勤人数'
        FROM
            fbs_workshop_dispatch_list fwdl
            LEFT JOIN ( SELECT * FROM fbs_working_statistics WHERE del_flag = '0' GROUP BY pk_workshop_dispatch_list ) pdl ON fwdl.id = pdl.pk_workshop_dispatch_list
        WHERE
            fwdl.del_flag = '0'
            AND date_format( fwdl.date, '%d' ) = DATE_FORMAT( now( ), '%d' )
    </select>

    <select id="getProductList" resultType="org.jeecg.modules.fbsWorkshopDispatchList.entity.FbsWorkshopDispatchList">
        SELECT
            fwdl.production_order_number_name,
            fws.end_date,
            fwdl.product,
            fwdl.product_id,
            fwdl.planned_start_time,
            fwdl.planned_end_time,
            fwdl.number_of_tasks,
            fws.qualified_number,
            fwdl.product_id
        FROM
          fbs_workshop_dispatch_list fwdl
        LEFT JOIN fbs_working_statistics fws ON fwdl.id = fws.pk_workshop_dispatch_list
          AND fws.del_flag = '0'
        WHERE
            fwdl.del_flag = '0'
            and fwdl.state <![CDATA[ <> ]]> '0'
            <!--and date_format( fwdl.date, '%Y-%m-%d' ) = date_format( now( ), '%Y-%m-%d' )-->
        ORDER BY
            fwdl.date DESC
            LIMIT 6
    </select>
    
</mapper>