package com.ximai.mes.pro.mapper.task;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.ximai.mes.kanban.dto.BasicRatio;
import com.ximai.mes.kanban.dto.material.MesDataDto;
import com.ximai.mes.pro.domain.proWorkOrder.ProWorkorder;
import com.ximai.mes.pro.domain.task.ProTask;
import com.ximai.mes.pro.domain.task.ProTaskWorkunit;
import com.ximai.mes.pro.dto.task.ProTaskWorkunitDto;
import com.ximai.mes.pro.dto.task.WorkunitProcessingDto;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.time.LocalDateTime;
import java.util.List;

/**
 * 任务工作单元Mapper接口
 *
 * @author zht
 * @date 2024-01-23
 */
public interface ProTaskWorkunitMapper extends BaseMapper<ProTaskWorkunit> {
    /**
     * 查询任务工作单元
     *
     * @param taskWorkunitId 任务工作单元主键
     * @return 任务工作单元
     */
    ProTaskWorkunit selectProTaskWorkunitByTaskWorkunitId(Long taskWorkunitId);

    /**
     * 查询任务工作单元
     *
     * @param taskWorkunitId 任务工作单元主键
     * @return 任务工作单元
     */
    @Select("select * from pro_workorder where arrange_code in (select arrange_code from  pro_task  where task_id in (select task_id from pro_task_workunit ptw where task_workunit_id = #{taskWorkunitId})) limit 1")
    ProWorkorder selectProWorkorderByTaskWorkunitId(@Param("taskWorkunitId") Long taskWorkunitId);


    /**
     * 查询任务工作单元列表
     *
     * @param proTaskWorkunit 任务工作单元
     * @return 任务工作单元集合
     */
    List<ProTaskWorkunit> selectProTaskWorkunitList(ProTaskWorkunit proTaskWorkunit);

    /**
     * 新增任务工作单元
     *
     * @param proTaskWorkunit 任务工作单元
     * @return 结果
     */
    int insertProTaskWorkunit(ProTaskWorkunit proTaskWorkunit);

    /**
     * 修改任务工作单元
     *
     * @param proTaskWorkunit 任务工作单元
     * @return 结果
     */
    int updateProTaskWorkunit(ProTaskWorkunit proTaskWorkunit);

    /**
     * 删除任务工作单元
     *
     * @param taskWorkunitId 任务工作单元主键
     * @return 结果
     */
    int deleteProTaskWorkunitByTaskWorkunitId(Long taskWorkunitId);

    /**
     * 批量删除任务工作单元
     *
     * @param taskWorkunitIds 需要删除的数据主键集合
     * @return 结果
     */
    int deleteProTaskWorkunitByTaskWorkunitIds(Long[] taskWorkunitIds);

    ProTaskWorkunit selectProTaskWorkunitByTaskId(Long taskId);

    @Select(value = "select * from pro_task_workunit where task_id =#{taskId}")
    List<ProTaskWorkunit> selectProTaskWorkunitByTaskIds(@Param("taskId") Long taskId);

    List<ProTaskWorkunit> selectProTaskWorkunitTaskId(Long taskId);

    /**
     * 查询任务工作单元列表
     *
     * @param query 任务工作单元
     * @return 任务工作单元集合
     */
    @Select(value = "select * from pro_task_workunit\n" +
            "${ew.customSqlSegment}")
    List<ProTaskWorkunit> selectListByQw(@Param("ew") QueryWrapper<ProTaskWorkunit> query);

    /**
     * 查询任务工作单元列表
     *
     * @param query 任务工作单元
     * @return 任务工作单元集合
     */
    @Select(value = "select distinct arrange_code, t1.process_id, task_id, task_workunit_id,COALESCE(pp.is_exist_tool, 0) as is_exist_tool\n" +
            " from (\n" +
            " \tselect o1.arrange_code,  o2.process_id\n" +
            " \t\t, o2.task_id, o2.task_workunit_id\n" +
            " \tfrom pro_workorder o1\n" +
            " \t\tinner join (\n" +
            " \t\t\tselect arrange_code, process_id, t1.task_id, t2.task_workunit_id\n" +
            " \t\t\tfrom pro_task t1\n" +
            " \t\t\t\tinner join (\n" +
            " \t\t\t\t\tselect task_id, task_workunit_id\n" +
            " \t\t\t\t\tfrom pro_task_workunit\n" +
            " \t\t\t\t\t${ew.customSqlSegment}\n" +
            " \t\t\t\t) t2\n" +
            " \t\t\t\ton t1.task_id = t2.task_id\n" +
            " \t\t) o2\n" +
            " \t\ton o1.arrange_code = o2.arrange_code\n" +
            " \t\tinner join pro_work_order_process o3\n" +
            " \t\ton o1.workorder_id = o3.workorder_id\n" +
            " ) t1\n" +
            " inner join pro_process pp on pp.process_id = t1.process_id")
    List<ProTask> selectListIsExistToolByTaskWorkunitIds(@Param("ew") QueryWrapper<ProTaskWorkunit> query);


    @Select(value = "SELECT\n" +
            "\tptw.task_workunit_id,\n" +
            "\tpf.quantity_qualify,\n" +
            "\tptw.quantity \n" +
            "FROM\n" +
            "\tpro_task_workunit ptw\n" +
            "\tLEFT JOIN  (\n" +
            "\tselect task_workunit_id,sum(quantity_qualify) quantity_qualify from pro_feedback where task_workunit_id = 1) pf ON ptw.task_workunit_id = pf.task_workunit_id\n" +
            "\twhere ptw.quantity > pf.quantity_qualify and ptw.task_workunit_id = 1")
    List<ProTaskWorkunit> select(@Param("taskWorkunitId") Long taskWorkunitId);


    @Select(value = "select * from pro_task_workunit where task_id =#{taskId}  order by task_workunit_id limit  1")
    ProTaskWorkunit selectTaskScheduledForearliest(@Param("taskId") Long taskId);

    @Select(value = "select * from pro_task_workunit where task_id =#{taskId} and and actual_end_date is not null  order by task_workunit_id limit  1")
    ProTaskWorkunit selectTaskScheduledForearliestExec(@Param("taskId") Long taskId);

    @Select(value = "select t.task_id, t.task_code, t.task_name, t.task_batch\n" +
            "\t, t.workstation_id, t.workstation_code, ws.workstation_name\n" +
            "\t, t.process_id, t.process_code, t.process_name\n" +
            "\t, t.item_id, wo.product_code as item_code, wo.product_name as item_name,wo.customer_project_no,wo.customer_drawing_no,wo.order_code, t.specification, t.unit_of_measure\n" +
            "\t, ptw.task_workunit_id, ptw.quantity, ptw.create_by, t.quantity_wait, ptw.quantity_produced, ptw.quantity_changed\n" +
            "\t, t.client_id, t.client_code, t.client_name, t.client_nick, t.is_last_process, ptw.schedule_start_date\n" +
            "\t, t.duration, ptw.schedule_end_date, t.color_code, t.request_date\n" +
            "\t, t.attr1, t.attr2, t.attr3, t.attr4\n" +
            "\t, t.create_time, t.update_by, t.update_time, ptw.STATUS, ptw.quantity_qualify, ptw.remark\n" +
            "\t,ptw.source_task_workunit_id, ptw.quantity_unqualify, ptw.outsource_unit_price,ptw.outsourced, ptw.vendor_id,ptw.vendor_name, mw.workunit_id, mw.workunit_code, mw.workunit_name, ptw.std_working_time,ptw.std_working_time_uom, wo.workorder_code\n" +
            "\t, (case ptw.STATUS when 'BEGINNING' then 1 when 'PREPARE' then 2 when 'PAUSE' then 3 when 'ERROR_STOP' then 4 end) statusIndex\n" +
            "from pro_task_workunit ptw\n" +
            "\tleft join pro_task t on ptw.task_id = t.task_id\n" +
            "\tleft join pro_task_workorder tw on t.task_id = tw.task_id\n" +
            "\tleft join pro_workorder wo on wo.workorder_id = tw.workorder_id\n" +
            "\tleft join pro_process p on t.process_id = p.process_id\n" +
            "\tleft join md_workunit mw on ptw.workunit_id = mw.workunit_id\n" +
            "\tleft join md_workstation ws on mw.workstation_id = ws.workstation_id\n" +
            "${ew.customSqlSegment} ")
    List<ProTaskWorkunit> selectTaskWorkUnitJoinTask(@Param("ew") QueryWrapper<ProTaskWorkunit> query);

    @Select(value = "select t.task_code, t.task_name, t.task_batch\n" +
            "\t, t.workstation_id, t.workstation_code\n" +
            "\t, t.process_id, t.process_code, t.process_name\n" +
            "\t, t.item_id, t.specification, t.unit_of_measure\n" +
            "\t, ptw.*\n"+
            "from pro_task_workunit ptw\n" +
            "\tleft join pro_task t on ptw.task_id = t.task_id\n" +
            "${ew.customSqlSegment} ")
    List<ProTaskWorkunitDto> selectTaskWorkUnitDtoJoinTask(@Param("ew") QueryWrapper<ProTaskWorkunit> query);

    @Select(value = "select ptw.*,pf.record_id from pro_task_workunit ptw left join pro_feedback pf on ptw.task_workunit_id = pf.task_workunit_id  ${ew.customSqlSegment} ")
    List<ProTaskWorkunit> selectTaskWorkUnitJoinFeedback(@Param("ew") QueryWrapper<ProTaskWorkunit> query);

    @Select(value = "select w.* from pro_workorder w left join pro_task_workorder tw on w.workorder_id = tw.workorder_id\n" +
            "left join pro_task t on t.task_id = tw.task_id \n" +
            "left join pro_task_workunit ptw on ptw.task_id = t.task_id \n" +
            " ${ew.customSqlSegment} ")
    List<ProWorkorder> selectWorkorderTaskByQw(@Param("ew") QueryWrapper<ProTaskWorkunit> query);

    /**
     * 统计设备进行中工单数量
     * @return
     */
    @Select(value = "select workunit_id,count(*) ct from pro_task_workunit t1 where t1.`status` = 'BEGINNING' group by workunit_id")
    List<WorkunitProcessingDto> selectWorkunitProcessing();

    /**
     * 计划看板专用
     * @param query
     * @return
     */
    @Select(value = "select t.task_id, t.task_code, t.task_batch, t.workstation_id\n" +
            "\t, t.workstation_code, t.process_id, t.process_code, t.process_name, t.item_id\n" +
            "\t, wo.product_code as item_code, wo.product_name as item_name, wo.customer_project_no, wo.order_code, t.specification\n" +
            "\t, t.unit_of_measure, ptw.task_workunit_id, ptw.quantity, ptw.create_by, t.quantity_wait\n" +
            "\t, ptw.quantity_produced\n" +
            "\t, ptw.schedule_start_date, t.duration, ptw.schedule_end_date, ptw.actual_end_date\n" +
            "\t, t.ordinal as idx, t.create_time, t.update_by\n" +
            "\t, t.update_time, ptw.STATUS, ptw.quantity_qualify, ptw.remark, ptw.quantity_unqualify\n" +
            "\t, wo.workorder_code, wu.line_name\n" +
            "\t, ptw.STATUS " +
            "from pro_task_workunit ptw\n" +
            "\tleft join pro_task t on ptw.task_id = t.task_id\n" +
            "\tleft join pro_task_workorder tw on t.task_id = tw.task_id\n" +
            "\tleft join pro_workorder wo on wo.workorder_id = tw.workorder_id\n" +
            "\tleft join pro_process p on t.process_id = p.process_id\n" +
            "\tleft join md_workunit wu on wu.workunit_id = ptw.workunit_id " +
            "${ew.customSqlSegment} ")
    List<ProTaskWorkunitDto> selectTaskWorkUnitWithKanban(@Param("ew") QueryWrapper<ProTaskWorkunit> query);

    void deleteByTaskIds(@Param("taskIds")List<Long> taskIds);
    /**
     * 物料综合看板专用生产入库计划
     * @param startDate 开始时间
     * @param endDate 结束时间
     * @return
     */
    @Select(value = "select t2.arrange_code as workorderNo,wo.product_name as itemName,max(t1.schedule_end_date) planFinishDate,sum(t1.quantity) planQuantity,sum(t1.quantity_produced) finishQuantity\n" +
            "from pro_task_workunit t1 left join pro_task t2 on t1.task_id = t2.task_id\n" +
            "left join pro_task_workorder tw on t2.task_id = tw.task_id\n" +
            "left join pro_workorder wo on wo.workorder_id = tw.workorder_id\n" +
            "where t1.schedule_end_date > #{startDate} and t1.schedule_end_date < #{endDate} \n" +
            "and t2.is_last_process = 1 and wo.status <> 'close' group by t2.arrange_code,wo.product_name")
    List<MesDataDto.OrderInfoDto> selectTaskWorkUnitWithKanban2(@Param("startDate") LocalDateTime startDate, @Param("endDate") LocalDateTime endDate);

    /**
     * 物料综合看板入库完成率
     * @param startDate 开始时间
     * @param endDate 结束时间
     * @return
     */
    @Select(value = "select ifnull(sum(t1.quantity),0) denominator,ifnull(sum(t1.quantity_produced),0) numerator from pro_task_workunit t1 left join pro_task t2 on t1.task_id = t2.task_id left join pro_task_workorder tw on t2.task_id = tw.task_id left join pro_workorder wo on wo.workorder_id = tw.workorder_id\n" +
            "where t1.schedule_end_date > #{startDate} and t1.schedule_end_date < #{endDate} \n" +
            "and t2.is_last_process = 1 and wo.status <> 'close'")
    BasicRatio selectStockInCompleteRatio(@Param("startDate") LocalDateTime startDate, @Param("endDate") LocalDateTime endDate);



}
