package com.rongwei.bscommon.sys.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.rongwei.bsentity.domain.*;
import com.rongwei.bsentity.vo.BlankIdAndWorkShopVo;
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;
import java.util.Map;

/**
 * @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 (((e.WORKSTATUS='待开工' or e.WORKSTATUS='加工中') and TIMESTAMPDIFF(DAY,DATE_FORMAT(NOW(),'%Y-%m-%d'),DATE_FORMAT(e.PLANENDDATE,'%Y-%m-%d'))<0) \n" +
            "\t\tor (e.WORKSTATUS='待开工' and TIMESTAMPDIFF(DAY,DATE_FORMAT(NOW(),'%Y-%m-%d'),DATE_FORMAT(e.PLANSTARTDATE,'%Y-%m-%d'))<0)))")
    List<ApsProductionOrderDo> getProcessDelay();

    @Select("SELECT SUBSTRING_INDEX(so.TREECODE, '.', 1) as ORDERNO,GROUP_CONCAT(su.ID) AS TENANTID,GROUP_CONCAT(su.EMAIL) AS ROPTION\n" +
            "FROM sys_role sr LEFT JOIN sys_user_role sur ON sr.ID = sur.ROLEID and sur.DELETED = 0 \n" +
            "LEFT JOIN sys_user su ON sur.USERID = su.ID and su.DELETED = 0 \n" +
            "LEFT JOIN sys_user_org suo ON suo.USERID = su.ID AND suo.DELETED = 0 \n" +
            "LEFT JOIN sys_organization so ON suo.ORGID = so.ID AND so.DELETED = 0 \n" +
            "WHERE sr.CODE='role071' AND ((so.FULLPID like ',0001651000000015EFDD%' or so.ID='0001651000000015EFDD') or (so.FULLPID like ',0001651000000017DRZ1%' or so.ID='0001651000000017DRZ1')\n" +
            "\tor (so.FULLPID like ',0001S31000000000J0TR%' or so.ID='0001S31000000000J0TR')) group by ORDERNO")
    List<ApsProductionOrderDo> getScjhzr();

    @Select("select po.ID,po.ORDERNO,po.CUSTOMNAME,po.ORDERDATE,b.ID as CUSTOMID,o.OUTPUTNUMBER as CUSTOMWAREHOUSE,p.PROCESS as CUSTOMORDERNO,o.INSEPCTIONLEVEL as CUSTOMORDERTYPE,\n" +
            "\to.MAJOR as REMARK,o.MINOR as ROPTION,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);


    @Select("select * from aps_process_operation where DELETED = 0 AND PLANINPUTID = #{joboutputmaterialid} AND LEAVEWAITWORKROLL > 0 ORDER BY PLANSTARTDATE DESC LIMIT 1")
    ApsProcessOperationDo selectNeedBorrowOperation(@Param("joboutputmaterialid") String joboutputmaterialid);

    List<BlankIdAndWorkShopVo> getWorkShopByBlankId(@Param("blankIdList") List<String> blankIdList);

    List<ApsProcessOperationDo> selectListAndPlanDate(@Param("blankIdList") List<String> blankIdList);

    @Select("select DISTINCT apop.CHANGEALLOY from aps_blank_order abo LEFT JOIN aps_process_output_product apop ON abo.ID = apop.BLANKID AND apop.DELETED = 0 WHERE abo.DELETED = 0 AND abo.PRODUCTIONORDERID = #{id} AND apop.CHANGEALLOY != '' AND apop.CHANGEALLOY is not null ")
    List<String> selectChangeAlloyByOrderId(@Param("id") String id);

    List<ApsReportYearAndMonth> selectNowYearPlanGetOrderNum();

    List<ApsReportYearAndMonth> selectNowYearActualGetOrderNum();

    List<ApsReportYearAndMonth> selectNowYearActualProduceNum();

    List<ApsReportYearAndMonth> selectNowMonthPlanGetOrderNum();

    List<ApsReportYearAndMonth> selectNowMonthActualGetOrderNum();

    List<ApsReportYearAndMonth> selectNowMonthActualProduceNum();

    List<ApsReportOrderFinishTable> selectOrderTable(@Param("tenantId") String tenantId);

    ApsReportOrderFinishPhoto selectOrderFinishPhotoAvg(@Param("tenantId") String tenantId);

    List<ApsReportOrderFinishPhoto> selectOrderFinishPhotoByMonth(@Param("tenantId") String tenantId);

    List<ApsReportCastRollTable> selectCastRollData(@Param("tenantId") String tenantId);

    ApsReportCastRollPhoto selectCastRollPhotoAvg(@Param("tenantId") String tenantId);

    List<ApsReportCastRollPhoto> selectCastRollPhoto(@Param("tenantId") String tenantId);

    List<ApsReportJzProcessTable> selectProcessTable(@Param("tenantId") String tenantId);

    ApsReportJzProcessPhoto selectProcessAvg(@Param("tenantId") String tenantId);

    List<ApsReportJzProcessPhoto> selectProcessTrend(@Param("tenantId") String tenantId);

    List<ApsReportZjProcessTable> selectZjProcessTable(@Param("tenantId") String tenantId);

    ApsReportZjProcessPhoto selectAvgZjProcessPhoto(@Param("tenantId") String tenantId);

    List<ApsReportZjProcessPhoto> selectZjProcessPhoto(@Param("tenantId") String tenantId);

    List<ApsReportThProcessTable> selectThProcessTable(@Param("tenantId") String tenantId);

    ApsReportThProcessPhoto selectAvgThProcessPhoto(@Param("tenantId") String tenantId);

    List<ApsReportThProcessPhoto> selectThProcessPhoto(@Param("tenantId") String tenantId);

    @Select("TRUNCATE TABLE ${tableName}")
    void truncateByTableName(@Param("tableName") String tableName);

    List<ApsReportYearAndMonth> selectNowWeekActualGetOrderNum();

    List<ApsReportYearAndMonth> selectNowWeekActualProduceNum();

    List<Map<String, Object>> selectPlanMap();

    @Select("select * from aps_production_order where ID = #{id}")
    Map<String, Object> getMapById(@Param("id") String id);
}




