<?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.fjhx.mes.mapper.production.ProductionTaskProgressMapper">

    <select id="getList" resultType="com.fjhx.mes.entity.production.vo.ProductionTaskProgressVo">
        SELECT ptp.id,
               ptp.task_id,
               ptp.processes_id,
               pp.`name` AS progressName,
               ptp.finish_quantity,
               ptp.balance_quantity
        FROM production_task_progress ptp
                 LEFT JOIN production_processes pp ON ptp.processes_id = pp.id
            ${ew.customSqlSegment}
    </select>
    <select id="workshopReport" resultType="com.fjhx.mes.entity.production.bo.WorkshopReportBo">
        SELECT ptp.id,
               pod.id                                                       AS task_id,
               pod.produce_order_id,
               pp.id                                                        AS progressId,
               pp.`name`                                                    AS progressName,
               po.`code`                                                    AS orderCode,
               pod.product_id,
               pod.quantity,
               ptp.finish_quantity,
               (pod.quantity - ptp.finish_quantity)                         AS residueQuantity,
               IFNULL((SELECT sum(quantity)
                       FROM production_scheduling ps
                       WHERE ps.task_id = pod.id
                         AND ps.processes_id = ptp.processes_id
                         AND DATEDIFF(ps.scheduling_date, now()) = - 1), 0) as yesterdaySchedulingQuantity,
               IFNULL((SELECT sum(finish_quantity)
                       FROM production_scheduling ps
                       WHERE ps.task_id = pod.id
                         AND ps.processes_id = ptp.processes_id
                         AND DATEDIFF(ps.scheduling_date, now()) = - 1), 0) as yesterdayFinishQuantity,
               IFNULL((SELECT sum(quantity)
                       FROM production_scheduling ps
                       WHERE ps.task_id = pod.id
                         AND ps.processes_id = ptp.processes_id
                         AND DATEDIFF(ps.scheduling_date, now()) = 0), 0)   as todaySchedulingQuantity,
               IFNULL((SELECT sum(finish_quantity)
                       FROM production_scheduling ps
                       WHERE ps.task_id = pod.id
                         AND ps.processes_id = ptp.processes_id
                         AND DATEDIFF(ps.scheduling_date, now()) = 0), 0)   as todayFinishQuantity,
               IFNULL((SELECT sum(quantity)
                       FROM production_scheduling ps
                       WHERE ps.task_id = pod.id
                         AND ps.processes_id = ptp.processes_id
                         AND DATEDIFF(ps.scheduling_date, now()) = 1), 0)   as tomorrowSchedulingQuantity
        FROM production_task_progress ptp
                 LEFT JOIN production_processes pp ON ptp.processes_id = pp.id
                 LEFT JOIN production_order_detail pod ON ptp.task_id = pod.id
                 LEFT JOIN production_order po ON pod.produce_order_id = po.id
                 LEFT JOIN production_scheduling ps on ps.task_id = pod.id AND ps.processes_id = ptp.processes_id
            ${ew.customSqlSegment}
    </select>
    <select id="workshopReportStatistics"
            resultType="com.fjhx.mes.entity.production.vo.ProductionTaskProgressVo">
        SELECT ptp.processes_id,
               pp.`name`                          AS progressName,
               IFNULL(sum(ps.quantity), 0)        AS quantity,
               IFNULL(sum(ps.finish_quantity), 0) AS finishQuantity
        FROM production_task_progress ptp
                 LEFT JOIN production_processes pp ON ptp.processes_id = pp.id
                 LEFT JOIN production_order_detail pod ON ptp.task_id = pod.id
                 LEFT JOIN production_scheduling ps ON ps.task_id = pod.id
            AND ps.processes_id = ptp.processes_id
            ${ew.customSqlSegment}
    </select>
</mapper>
