package com.rongwei.bscommon.sys.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.rongwei.bsentity.domain.ApsProductionOrderDo;
import com.rongwei.bsentity.vo.ProductDetailIdAndPlanOutPutRollCountAllVo;
import com.rongwei.bsentity.vo.ProductTypeIdAndPidVo;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;

/**
 * @Entity com.rongwei.training.domain.ApsProductionOrder
 */
public interface ApsProductionOrderDao extends BaseMapper<ApsProductionOrderDo> {
    void publish(@Param("ids") List<String> productionOrderIds);

    void publishCancel(@Param("ids") List<String> productionOrderIds);


    List<String> getStartWorkById(@Param("ids") List<String> ids);

    List<String> getStartWorkByBlankId(@Param("ids") List<String> ids);

    List<ApsProductionOrderDo> getDataByBlankId(@Param("blankIds")List<String> blankids);

    @Select("select apd.ID AS 'productDetailId',SUM(apop.PLANOUTPUTROLLCOUNT) AS 'PLANOUTPUTROLLCOUNTALL' from aps_product_detail apd LEFT JOIN aps_process_output_product apop ON apd.ID = apop.PRODUCTID AND apop.DELETED = 0 where apd.DELETED = 0 AND apd.MAINID = #{mainid} GROUP BY apd.ID")
    List<ProductDetailIdAndPlanOutPutRollCountAllVo> getProductDetailIdAndPlanOutPutRollCountAll(@Param("mainid") String mainid);

    @Select("select b.ID AS 'id',a.PID AS 'pid' from (select * from sys_dict where DICTTYPE = 'productType' AND DELETED = 0) a RIGHT JOIN (select * from sys_dict where DELETED = 0 AND ID in ('${join}')) b ON a.ID = b.PID")
    List<ProductTypeIdAndPidVo> selectProductTypeIdAndPid(@Param("join") String join);

    @Select("select o.ID,o.ORDERNO,o.CREATEUSERID,o.CREATEUSERNAME,u.EMAIL as MODIFYUSERNAME from aps_production_order o\n" +
            "left join sys_user u on o.CREATEUSERID=u.ID and u.DELETED='0'\n" +
            "where o.DELETED='0' and IFNULL(o.ORDERNO,'')!='' and o.AUDITSTATUS!='已发布计划' and o.AUDITSTATUS!='已完工关闭'\n" +
            "\tand ((IFNULL(o.CHANGEDATE,'')='' and TIMESTAMPDIFF(HOUR,o.ORDERDATE,NOW())>48) or (IFNULL(o.CHANGEDATE,'')!='' and TIMESTAMPDIFF(HOUR,o.CHANGEDATE,NOW())>48))")
    List<ApsProductionOrderDo> getOrderOuttimeAudit();

    @Select("SELECT count(1) FROM aps_production_order apo LEFT JOIN aps_blank_order abo ON apo.ID = abo.PRODUCTIONORDERID  AND abo.DELETED = 0 LEFT JOIN aps_process_operation apo2 ON abo.ID = apo2.BLANKID  AND apo2.DELETED = 0  LEFT JOIN aps_process_operation_process_equ apope ON apo2.ID = apope.PROCESSID AND apope.DELETED = 0 WHERE apo.DELETED = 0  AND apo.ID = #{id} AND (apope.CONFLICTDES is not null AND apope.CONFLICTDES != '')")
    int selectConflictdesCount(@Param("id") String id);

    @Select("select o.ID,o.ORDERNO,o.CREATEUSERID,o.CREATEUSERNAME,u.EMAIL as MODIFYUSERNAME from aps_production_order o left join sys_user u on o.CREATEUSERID=u.ID and u.DELETED='0'\n" +
            "where o.DELETED='0' and IFNULL(o.ORDERNO,'')!='' and o.MATERIALSTATUS='已评审' \n" +
            "\tand o.ID in(select distinct MAINID from aps_auxiliary_material where DELETED='0' and SUBDCRIPTIONCODE='未申购' and TIMESTAMPDIFF(DAY,DATE_FORMAT(NOW(),'%Y-%m-%d'),DATE_FORMAT(SUBSCRIPTIONDATE,'%Y-%m-%d'))<8)")
    List<ApsProductionOrderDo> getAuxiliaryMaterial();

    @Select("select o.ID,o.ORDERNO,o.CREATEUSERID,o.CREATEUSERNAME,u.EMAIL as MODIFYUSERNAME from aps_production_order o left join sys_user u on o.CREATEUSERID=u.ID and u.DELETED='0'\n" +
            "where o.DELETED='0' and IFNULL(o.ORDERNO,'')!='' and o.ID in(\n" +
            "\tselect distinct b.PRODUCTIONORDERID from aps_process_operation_process_equ e\n" +
            "\tleft join aps_process_operation p on p.DELETED='0' and p.ID=e.PROCESSID\n" +
            "\tleft join aps_blank_order b on b.DELETED='0' and b.ID=p.BLANKID\n" +
            "\twhere e.DELETED='0' and (p.PROCESSSTATUS='30' or p.PROCESSSTATUS='40') and (e.WORKSTATUS='待开工' or e.WORKSTATUS='加工中') and TIMESTAMPDIFF(DAY,DATE_FORMAT(NOW(),'%Y-%m-%d'),DATE_FORMAT(e.PLANENDDATE,'%Y-%m-%d'))<0)")
    List<ApsProductionOrderDo> getProcessDelay();

    @Select("select po.ID,po.ORDERNO,po.CUSTOMNAME,po.ORDERDATE,b.ID as AUDITORID,o.OUTPUTNUMBER as AUDITOR,p.PROCESS as CUSTOMORDERNO,o.INSEPCTIONLEVEL as CUSTOMORDERTYPE,\n" +
            "\to.MAJORDESC as REMARK,o.MINORDESC as OTHERPRECAUTION,po.CREATEUSERID,u.ACCOUNT as CREATEUSERNAME,u.EMAIL as MODIFYUSERNAME\n" +
            "from aps_report_output o\n" +
            "left join aps_report_records r on r.DELETED='0' and r.ID=o.MAINID\n" +
            "left join aps_process_operation p on r.PROCESSOPERATIONID=p.ID and p.DELETED='0'\n" +
            "left join aps_blank_order b on b.DELETED='0' and p.BLANKID=b.ID\n" +
            "left join aps_production_order po on po.DELETED='0' and b.PRODUCTIONORDERID=po.ID\n" +
            "left join sys_user u on po.CREATEUSERID=u.ID and u.DELETED='0'\n" +
            "where o.DELETED='0' and o.QUALIFIED='否' and TIMESTAMPDIFF(DAY,DATE_FORMAT(r.CHECKDATE,'%Y-%m-%d'),DATE_FORMAT(NOW(),'%Y-%m-%d'))=1\n" +
            "order by po.ORDERNO,b.ID")
    List<ApsProductionOrderDo> getReportUnqualify();

    @Update("update aps_production_order set AUDITSTATUS = '待提交', CHANGEDATE = now() where ID = #{id}")
    void changeOrderById(@Param("id") String id);

    @Select("SELECT * FROM aps_production_order WHERE DELETED = 0 AND ID = #{id}")
    ApsProductionOrderDo selectDataInfo(@Param("id") String id);


}




