<?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.appInterface.mapper.FbsWorkingStatisticsMapper">
    <!--  <select id="getByMaterial" resultType="org.jeecg.modules.appInterface.entity.FbsWorkingStatistics">
          SELECT
              sum( fws.working_hours ) as workingHours,
              fwdl.product
          FROM
              fbs_working_statistics fws
              LEFT JOIN fbs_workshop_dispatch_list fwdl ON fws.pk_workshop_dispatch_list = fwdl.id
          WHERE
              fws.del_flag = '0'
              AND fwdl.del_flag = '0'
              AND fws.final_state IN ( '2', '3' )
              AND date_format( fws.final_time, #{dateString} ) = DATE_FORMAT( now( ), #{dateString} )
          GROUP BY
              fwdl.product
      </select>-->

    <select id="getByMaterial" resultType="java.util.HashMap">
        <!--SELECT
            sum( fwdl.man_hours_completed ) AS '生产',
            ( SELECT sum( train ) + sum( meeting ) + sum( five_s ) FROM fbs_abnormal_working_hours WHERE del_flag = '0' AND date_format( date, #{dateString} ) = DATE_FORMAT( now( ), #{dateString} ) ) AS '其他（5S、会议、培训）'
        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.final_state = '3'
            AND date_format( fws.final_time, #{dateString} ) = DATE_FORMAT( now( ), #{dateString} )-->
        SELECT
        CONVERT
        (sum( fwdl.man_hours_completed ), DECIMAL ( 15, 2 ) ) AS '生产',
        (
        SELECT
        IFNULL(sum( train ),0) + IFNULL(sum( meeting ),0) + IFNULL(sum( five_s ),0) + IFNULL(sum( research_and_development ),0)
        FROM
        fbs_abnormal_working_hours
        WHERE
        del_flag = '0'
        <if test="dateString=='%Y-%m-%d'">
            AND date_format(
            date, #{dateString} ) = DATE_FORMAT( date_sub(curdate(),interval 1 day),  #{dateString} )
        </if>
        <if test="dateString=='%Y-%m'">
            AND date_format(
            date, #{dateString} ) = DATE_FORMAT( now(),  #{dateString} )
        </if>

        ) AS '其他',
        (
        SELECT
        IFNULL(sum( quality ),0) + IFNULL(sum( design ),0) + IFNULL(sum( rework ),0) + IFNULL(sum( materiel ),0) + IFNULL(sum( energy ),0) + IFNULL(sum( other ),0)+IFNULL(sum(equipment),0) + IFNULL(sum( plan ),0)
        FROM
        fbs_abnormal_working_hours
        WHERE
        del_flag = '0'
        <if test="dateString=='%Y-%m-%d'">
            AND date_format(
            date, #{dateString} ) = DATE_FORMAT( date_sub(curdate(),interval 1 day),  #{dateString} )
        </if>
        <if test="dateString=='%Y-%m'">
            AND date_format(
            date, #{dateString} ) = DATE_FORMAT( now(),  #{dateString} )
        </if>
        ) as '异常',
        (
        SELECT
        IFNULL(sum( mixed_ingredients ),0) + IFNULL(sum( warehousing ),0) + IFNULL(sum( pack ),0) + IFNULL(sum( warehouse_others ),0)
        FROM
        fbs_abnormal_working_hours
        WHERE
        del_flag = '0'
        <if test="dateString=='%Y-%m-%d'">
            AND date_format(
            date, #{dateString} ) = DATE_FORMAT( date_sub(curdate(),interval 1 day),  #{dateString} )
        </if>
        <if test="dateString=='%Y-%m'">
            AND date_format(
            date, #{dateString} ) = DATE_FORMAT( now(),  #{dateString} )
        </if>
        ) as '仓库'
        FROM
        fbs_workshop_dispatch_list fwdl
        WHERE
        fwdl.del_flag = '0'
        <if test="dateString=='%Y-%m-%d'">
            and DATE_FORMAT(date_sub(curdate(),interval 1 day),#{dateString}) <![CDATA[ >= ]]> DATE_FORMAT(fwdl.planned_start_time,#{dateString})
            and DATE_FORMAT(date_sub(curdate(),interval 1 day),#{dateString}) <![CDATA[ <= ]]> DATE_FORMAT(fwdl.planned_end_time,#{dateString})
        </if>
        <if test="dateString=='%Y-%m'">
            and DATE_FORMAT(now(),#{dateString}) <![CDATA[ >= ]]> DATE_FORMAT(fwdl.planned_start_time,#{dateString})
            and DATE_FORMAT(now(),#{dateString}) <![CDATA[ <= ]]> DATE_FORMAT(fwdl.planned_end_time,#{dateString})
        </if>

    </select>

    <select id="getWorkingHours" resultType="org.jeecg.modules.appInterface.entity.FbsWorkingStatistics">
        SELECT
            fwdl.production_order_number_name,
            fwdl.product_id,
            sum( fws.working_hours ) as working_hours
        FROM
            fbs_working_statistics fws
            LEFT JOIN fbs_workshop_dispatch_list fwdl ON fws.pk_workshop_dispatch_list = fwdl.id
        WHERE
            fws.del_flag = '0'
            AND fws.final_state IN ( '2', '3' )
        GROUP BY
            fwdl.production_order_number_name,
            fwdl.product_id
    </select>

    <select id="getWorkingHoursGroupProjectName" resultType="org.jeecg.modules.fbsWorkshopDispatchList.entity.FbsWorkshopDispatchList">
        SELECT
            fwdl.project_name,
            CONVERT
          (sum( fwdl.man_hours_completed ), DECIMAL ( 15, 2 ) ) as man_hours_completed
        FROM
            fbs_workshop_dispatch_list fwdl
        WHERE
            fwdl.del_flag = '0'
        GROUP BY
            fwdl.project_name
    </select>


</mapper>