<?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.ximai.mes.report.mapper.DailyProductionReportMapper">


    <select id="getListByProcess" resultType="java.util.Map">
        SELECT
        process.process_code AS "工序编码",
        process.process_name AS "工序名称",
        DATE_FORMAT( f.feedback_time, '%Y-%m-%d' ) AS "报工日期",
        sum( f.quantity ) AS quantity,
        sum( f.quantity_qualify ) AS '合格数量',
        sum( f.quantity_unqualify ) AS '不合格数量',
        sum( f.quantity_qualify +  f.quantity_unqualify) AS "报工数量"
        FROM
        pro_feedback f
        JOIN pro_task_workunit workunit ON workunit.task_id = f.task_id
        JOIN pro_task pt ON f.task_id = pt.task_id
        JOIN pro_process process ON process.process_id = pt.process_id
        join md_workunit mw on workunit.workunit_id = mw.workunit_id
        join md_workstation workstation on workstation.workstation_id = mw.workstation_id
        <where>
            <if test="processId != null  ">and process.process_id = #{processId}</if>
            <if test="processName != null and processName  != '' ">and  process.process_name like  '%${processName}%'</if>
            <if test="startDate != null and endDate  != null">and  feedback_time BETWEEN #{startDate} AND #{endDate}</if>

            <if test="workshopId != null">and workstation.workshop_Id = #{workshopId}</if>
            <if test="workshopName != null and workshopName  != '' ">and  workstation.workshop_name like  '%${workshopName}%'</if>
            <if test="workstationId != null">and workstation.workstation_id = #{workstationId} </if>
            <if test="workstationName != null and workstationName  != '' ">and  workstation.workstation_name like  '%${workstationName}%'</if>
            <if test=" workunitId != null">and  mw.workunit_id =  #{workunitId}</if>
            <if test=" workunitName != null and workunitName  != '' ">and  mw.workunit_name like  '%${workunitName}%'</if>
            <if test="userName != null  and userName != ''">and     f.user_name like concat('%', #{userName}, '%')</if>
            <if test="nickName != null and nickName  != '' ">and   f.nick_Name like  '%${nickName}%'</if>
        </where>
        GROUP BY
        process.process_code,
        process.process_name,
        DATE_FORMAT( f.feedback_time, '%Y-%m-%d' )
    </select>
    <select id="getListByWorkshop" resultType="java.util.Map">
        SELECT
        workstation.workshop_code as '车间编码',
        workstation.workshop_name as '车间名称',
        DATE_FORMAT( f.feedback_time, '%Y-%m-%d' ) AS "报工日期",
        sum( f.quantity ) AS quantity,
        sum( f.quantity_qualify ) AS '合格数量',
        sum( f.quantity_unqualify ) AS '不合格数量',
        sum( f.quantity_qualify +  f.quantity_unqualify) AS "报工数量"
        FROM
        pro_feedback f
        JOIN pro_task_workunit workunit ON workunit.task_id = f.task_id
        JOIN pro_task pt ON f.task_id = pt.task_id
        JOIN pro_process process ON process.process_id = pt.process_id
        join pro_workorder workorder on workorder.workorder_id = f.workorder_id
        JOIN md_item it ON it.item_id = f.item_id
        join md_workunit mw on workunit.workunit_id = mw.workunit_id
        join md_workstation workstation on workstation.workstation_id = mw.workstation_id
        <where>
            <if test=" workshopId != null">and workstation.workshop_Id = #{workshopId}</if>
            <if test=" workshopName != null and workshopName  != '' ">and  workstation.workshop_name like  '%${workshopName}%'</if>
            <if test=" startDate != null and endDate  != null">and  feedback_time BETWEEN #{startDate} AND #{endDate}</if>


            <if test="processId != null  ">and process.process_id = #{processId}</if>
            <if test="processName != null and processName  != '' ">and  process.process_name like  '%${processName}%'</if>
            <if test="workstationId != null">and workstation.workstation_id = #{workstationId} </if>
            <if test="workstationName != null and workstationName  != '' ">and  workstation.workstation_name like  '%${workstationName}%'</if>
            <if test=" workunitId != null">and  mw.workunit_id =  #{workunitId}</if>
            <if test=" workunitName != null and workunitName  != '' ">and  mw.workunit_name like  '%${workunitName}%'</if>
            <if test="userName != null  and userName != ''">and     f.user_name like concat('%', #{userName}, '%')</if>
            <if test="nickName != null and nickName  != '' ">and   f.nick_Name like  '%${nickName}%'</if>
        </where>
        group by
        workstation.workshop_code,
        workstation.workshop_name,
        DATE_FORMAT( f.feedback_time, '%Y-%m-%d' )
    </select>
    <select id="getListByWorkstation" resultType="java.util.Map">
        SELECT
        workstation.workstation_code AS "工作中心编码",
        workstation.workstation_name AS "工作中心名称",
        DATE_FORMAT( f.feedback_time, '%Y-%m-%d' ) AS "报工日期",
        sum( f.quantity ) AS quantity,
        sum( f.quantity_qualify ) AS '合格数量',
        sum( f.quantity_unqualify ) AS '不合格数量',
        sum( f.quantity_qualify +  f.quantity_unqualify) AS "报工数量"
        FROM
        pro_feedback f
        JOIN pro_task_workunit workunit ON workunit.task_id = f.task_id
        JOIN pro_task pt ON f.task_id = pt.task_id
        JOIN pro_process process ON process.process_id = pt.process_id
        join pro_workorder workorder on workorder.workorder_id = f.workorder_id
        JOIN md_item it ON it.item_id = f.item_id
        join md_workunit mw on workunit.workunit_id = mw.workunit_id
        join md_workstation workstation on workstation.workstation_id = mw.workstation_id
        <where>
            <if test=" workstationId != null">and workstation.workstation_id = #{workstationId} </if>
            <if test=" workstationName != null and workstationName  != '' ">and  workstation.workstation_name like  '%${workstationName}%'</if>
            <if test=" startDate != null and endDate  != null">and  feedback_time BETWEEN #{startDate} AND #{endDate}</if>


            <if test="processId != null  ">and process.process_id = #{processId}</if>
            <if test="processName != null and processName  != '' ">and  process.process_name like  '%${processName}%'</if>
            <if test=" workshopId != null">and workstation.workshop_Id = #{workshopId}</if>
            <if test=" workshopName != null and workshopName  != '' ">and  workstation.workshop_name like  '%${workshopName}%'</if>
            <if test=" workunitId != null">and  mw.workunit_id =  #{workunitId}</if>
            <if test=" workunitName != null and workunitName  != '' ">and  mw.workunit_name like  '%${workunitName}%'</if>
            <if test="userName != null  and userName != ''">and     f.user_name like concat('%', #{userName}, '%')</if>
            <if test="nickName != null and nickName  != '' ">and   f.nick_Name like  '%${nickName}%'</if>
        </where>
        group by
        workstation.workstation_code,
        workstation.workstation_name,
        DATE_FORMAT( f.feedback_time, '%Y-%m-%d' )
    </select>
    <select id="getListByWorkunit" resultType="java.util.Map">
        SELECT
        mw.workunit_code AS "工作单元编码",
        mw.workunit_name AS "工作单元名称",
        DATE_FORMAT( f.feedback_time, '%Y-%m-%d' ) AS "报工日期",
        sum( f.quantity ) AS quantity,
        sum( f.quantity_qualify ) AS '合格数量',
        sum( f.quantity_unqualify ) AS '不合格数量',
        sum( f.quantity_qualify +  f.quantity_unqualify) AS "报工数量"
        FROM
        pro_feedback f
        JOIN pro_task_workunit workunit ON workunit.task_id = f.task_id
        JOIN pro_task pt ON f.task_id = pt.task_id
        JOIN pro_process process ON process.process_id = pt.process_id
        join pro_workorder workorder on workorder.workorder_id = f.workorder_id
        JOIN md_item it ON it.item_id = f.item_id
        join md_workunit mw on workunit.workunit_id = mw.workunit_id
        join md_workstation workstation on workstation.workstation_id = mw.workstation_id
        <where>
            <if test=" workunitId != null">and  workunit.workunit_id =  #{workunitId}</if>
            <if test=" workunitName != null and workunitName  != '' ">and  mw.workunit_name like  '%${workunitName}%'</if>
            <if test=" startDate != null and endDate  != null">and  feedback_time BETWEEN #{startDate} AND #{endDate}</if>


            <if test="processId != null  ">and process.process_id = #{processId}</if>
            <if test="processName != null and processName  != '' ">and  process.process_name like  '%${processName}%'</if>
            <if test="workshopId != null">and workstation.workshop_Id = #{workshopId}</if>
            <if test="workshopName != null and workshopName  != '' ">and  workstation.workshop_name like  '%${workshopName}%'</if>
            <if test="workstationId != null">and workstation.workstation_id = #{workstationId} </if>
            <if test="workstationName != null and workstationName  != '' ">and  workstation.workstation_name like  '%${workstationName}%'</if>
            <if test="userName != null  and userName != ''">and     f.user_name like concat('%', #{userName}, '%')</if>
            <if test="nickName != null and nickName  != '' ">and   f.nick_Name like  '%${nickName}%'</if>
        </where>
        group by
        mw.workunit_code,
        mw.workunit_name,
        DATE_FORMAT( f.feedback_time, '%Y-%m-%d' )
    </select>
    <select id="getListByUser" resultType="java.util.Map">
        SELECT
        IFNULL(f.user_name,'无')  AS "报工人员编码",
        f.nick_name  AS "报工人员名称",
        DATE_FORMAT( f.feedback_time, '%Y-%m-%d' ) AS "报工日期",
        sum( f.quantity ) AS quantity,
        sum( f.quantity_qualify ) AS '合格数量',
        sum( f.quantity_unqualify ) AS '不合格数量',
        sum( f.quantity_qualify +  f.quantity_unqualify) AS "报工数量"
        FROM
        pro_feedback f
        JOIN pro_task_workunit workunit ON workunit.task_id = f.task_id
        JOIN pro_task pt ON f.task_id = pt.task_id
        JOIN pro_process process ON process.process_id = pt.process_id
        join pro_workorder workorder on workorder.workorder_id = f.workorder_id
        JOIN md_item it ON it.item_id = f.item_id
        join md_workunit mw on workunit.workunit_id = mw.workunit_id
        join md_workstation workstation on workstation.workstation_id = mw.workstation_id
        <where>
            <if test="userName != null  and userName != ''">and     f.user_name like concat('%', #{userName}, '%')</if>
            <if test=" nickName != null and nickName  != '' ">and   f.nick_Name like  '%${nickName}%'</if>
            <if test=" startDate != null and endDate  != null">and  feedback_time BETWEEN #{startDate} AND #{endDate}</if>

            <if test="processId != null  ">and process.process_id = #{processId}</if>
            <if test="processName != null and processName  != '' ">and  process.process_name like  '%${processName}%'</if>
            <if test="workshopId != null">and workstation.workshop_Id = #{workshopId}</if>
            <if test="workshopName != null and workshopName  != '' ">and  workstation.workshop_name like  '%${workshopName}%'</if>
            <if test="workstationId != null">and workstation.workstation_id = #{workstationId} </if>
            <if test="workstationName != null and workstationName  != '' ">and  workstation.workstation_name like  '%${workstationName}%'</if>
            <if test=" workunitId != null">and  mw.workunit_id =  #{workunitId}</if>
            <if test=" workunitName != null and workunitName  != '' ">and  mw.workunit_name like  '%${workunitName}%'</if>
        </where>
        group by
        IFNULL(f.user_name,'无'),
        f.nick_name,
        DATE_FORMAT( f.feedback_time, '%Y-%m-%d' )
    </select>
</mapper>
