package QCM.COMMUNAL; import QCM.COMMUNAL.VO.*; import QCM.JHY01.VO.JhySampleDItemModel; import QCM.JHY01.VO.JhySampleDModel; import QCM.JHY01.VO.JhySampleMMdoel; import QCM.JHY01.VO.JhySampleOrdMdoel; import org.apache.ibatis.annotations.*; import java.util.HashMap; import java.util.LinkedHashMap; import java.util.List; public interface OrderSqMapper { @Select("select m.*,d.pline_code,d.specimen_no,d.MATERIAL_NO from(SELECT R.* FROM QCM_JHY_SAMPLE_R_ORD R WHERE R.SMP_NO IN " +" (SELECT DISTINCT (M.SMP_NO) FROM QCM_JHY_SAMPLE_CONSIGN_M M LEFT JOIN QCM_JHY_SAMPLE_CONSIGN_D D " +" ON M.SMP_NO=D.SMP_NO WHERE M.SMP_CATG='A' and d.smp_type_code = '0' AND D.STATUS='3')) m left join QCM_JHY_SAMPLE_CONSIGN_D d on m.smp_no=d.smp_no where 1=1 ${condition}") public List GetSampleChemInfo(@Param("condition") String condition); @Select(" SELECT R.* FROM QCM_JHY_SAMPLE_R_ORD R WHERE R.SMP_NO IN " +" (SELECT DISTINCT (M.SMP_NO) FROM QCM_JHY_SAMPLE_CONSIGN_M M LEFT JOIN QCM_JHY_SAMPLE_CONSIGN_D D " + " ON M.SMP_NO=D.SMP_NO WHERE M.SMP_CATG='B' and d.smp_type_code = '0' AND D.STATUS='3' and m.smp_no like '%' || #{smp_no} || '%') and ${condition}") public List GetSamplePhyInfo(@Param("condition") String condition,@Param("smp_no") String smp_no); @Select(" SELECT R.* FROM QCM_JHY_SAMPLE_R_ORD R WHERE R.SMP_NO IN " +" (SELECT DISTINCT (M.SMP_NO) FROM QCM_JHY_SAMPLE_CONSIGN_M M LEFT JOIN QCM_JHY_SAMPLE_CONSIGN_D D " + " ON M.SMP_NO=D.SMP_NO WHERE M.SMP_CATG='B' and d.smp_type_code = '1' AND D.STATUS='3' and m.smp_no like '%' || #{smp_no} || '%') and ${condition}") public List GetSamplePhyInfo2(@Param("condition") String condition,@Param("smp_no") String smp_no); @Select(" SELECT R.* FROM QCM_JHY_SAMPLE_R_ORD R WHERE R.SMP_NO IN " +" (SELECT DISTINCT (M.SMP_NO) FROM QCM_JHY_SAMPLE_CONSIGN_M M LEFT JOIN QCM_JHY_SAMPLE_CONSIGN_D D " + " ON M.SMP_NO=D.SMP_NO WHERE M.SMP_CATG='B' AND D.STATUS IN('3','5') and m.smp_no like '%' || #{smp_no} || '%') and ${condition}") public List GetSamplePhyInfo1(@Param("condition") String condition,@Param("smp_no") String smp_no); @Select(" SELECT R.* FROM QCM_JHY_SAMPLE_R_ORD R WHERE R.SMP_NO IN " +" (SELECT DISTINCT (M.SMP_NO) FROM QCM_JHY_SAMPLE_CONSIGN_M M LEFT JOIN QCM_JHY_SAMPLE_CONSIGN_D D " + " ON M.SMP_NO=D.SMP_NO WHERE M.SMP_CATG='B' and m.CERT_INST_NAME = #{cert_inst_name} and m.INSPECTION_LOT = #{INSPECTION_LOT} AND D.STATUS='3' ) and ${condition} and r.INSPECTION_LOT = #{INSPECTION_LOT}") public List GetSamplePhyInfos(@Param("condition") String condition,@Param("INSPECTION_LOT") String INSPECTION_LOT,@Param("cert_inst_name") String cert_inst_name); @Select("select * from qcm_jhy_sample_consign_d where smp_no = #{smp_no} and inspection_lot = #{inspection_lot} and heat_no = #{heat_no}") public List findQcmJhySampleConsignD(@Param("smp_no") String smp_no,@Param("inspection_lot") String inspection_lot,@Param("heat_no")String heat_no); @Select("select * from qcm_jhy_sample_consign_d where smp_no = #{smp_no} and specimen_no = #{specimen_no}") public List findQcmJhySampleConsignD1(@Param("smp_no") String smp_no,@Param("specimen_no") String specimen_no); @Select("select * from qcm_jhy_sample_consign_d where 1=1 and ${condition}") public List findQcmJhySampleConsignDC(@Param("condition") String condition); @Select("select * from qcm_jhy_sample_consign_d where smp_no = #{smp_no} order by SPECIMEN_NO DESC") public List findQcmJhySampleConsignD2(@Param("smp_no") String smp_no); @Select("select * from qcm_jhy_sample_consign_d x where x.specimen_no in (select case " +" when a.fy_quote_specimen_no is null then a.specimen_no else a.fy_quote_specimen_no end from qcm_jhy_sample_consign_d_item a where a.specimen_no = #{specimen_no} and a.seq = #{seq})") public List findQcmJhySampleConsignD3(@Param("specimen_no") String specimen_no,@Param("seq") long seq); @Select("select * from (select * from qcm_judge_chemical where cic_id like #{date} || '%' order by cic_id DESC) where rownum <= 1") public List QueryQcmJudgeChemicals(@Param("date") String date); public void insertqcmjudgechemcial(@Param("qjc") QcmJudgeChemical qjc); public void updateQcmJhySampleROrds(@Param("qsrd") QcmJhySampleROrd qsrd); /* public void updateQcmJudgePhysical(@Param("qsrd") QcmJudgePhysical qsrd); public void updateQcmJudgeChemical(@Param("qsrd") QcmJudgeChemical qsrd);*/ public void insertQcmPhyJudgeResult(@Param("q") QcmPhyJudgeResult q); public void insertQcmJudgeSurface(@Param("q") QcmJudgeSurface q); public void insertQcmJudgeSurfaceFlaw(@Param("q") QcmJudgeSurfaceFlaw q); public void insertQcmJudgeUltimateReason(@Param("q") QcmJudgeUltimateReason q); public void insertQcmJudgePhysical(@Param("qjc")QcmJudgePhysical qjc); public void insertQcmJhySampleConsignDItem(@Param("qcm")QcmJhySampleConsignDItem qcm); public void insertQcmJhySampleConsignD(@Param("qcm")QcmJhySampleConsignD qcm); public void insertQcmJudgePhysicalUtmM(@Param("qpu")QcmJudgePhysicalUtmM qpu); public void insertQcmJudgePhysicalUtmD(@Param("qpd")QcmJudgePhysicalUtmD qpd); public void insertQcmJudgeGpInfo(@Param("q") QcmJudgeGpInfo q); public void insertQcmJudgeUltimate(@Param("qju")QcmJudgeUltimate qju); public void insertQCMJudgeLocking(@Param("q")QCMJudgeLocking q); public void insertQCMJudgeLockingApply(@Param("q")QcmJudgeLockingApply q); public void insertQcmJudgeDetectionM(@Param("q")QcmJudgeDetectionM q); public void insertQcmJudgeDetectionD(@Param("q")QcmJudgeDetectionD q); public void insertQCMJudgeMeasure(@Param("qju")QCMJudgeMeasure qju); public void insertQcmJudgeMeasureD(@Param("q") QcmJudgeMeasureD q); public void insertQcmJudgeUltimateSendinfo(@Param("q") QcmJudgeUltimateSendinfo q); /*@Select("select assay_no from QCM_CHEM_RESULT where assay_no=#{assay_no}") public String getsample(@Param("assay_no") String assay_no);*/ //@Select("select a.*,b.smp_location from Qcm_Jhy_Insp_Physics a left join qcm_jhy_sample_consign_d_item b on a.specimen_no = b.specimen_no and a.seq = b.seq where a.specimen_no= #{specimen_no}") @Select("select * from Qcm_Jhy_Insp_Physics where specimen_no=#{specimen_no} ") public List findphyresult(@Param("specimen_no") String specimen_no); @Select("select * from Qcm_Jhy_Insp_Physics where specimen_no=#{specimen_no} and seq = #{seq}" ) public List findphyresult1(@Param("specimen_no") String specimen_no,@Param("seq") long seq); @Select("select * from Qcm_Jhy_Insp_Physics where specimen_no=#{specimen_no} and seq = #{seq} and specimen_no || guid in (select z.specimen_no||z.guid from qcm_judge_physical_result z where z.phy_id = #{phy_id})") public List findphyresult2(@Param("specimen_no") String specimen_no,@Param("seq") long seq,@Param("phy_id") String phy_id); @Select("select * from Qcm_Jhy_Insp_Physics where specimen_no=#{specimen_no} and seq = #{seq} and specimen_no || guid in (select z.specimen_no||z.guid from qcm_jhy_insp_physics z where z.specimen_no=#{specimen_no})") public List findphyresult3(@Param("specimen_no") String specimen_no,@Param("seq") long seq); @Select("select a.*,b.seq from QCM_JUDGE_PHYSICAL_RESULT a left join qcm_jhy_insp_physics b on a.specimen_no = b.specimen_no and a.guid =b.guid where a.specimen_no=#{specimen_no} and a.phy_id = #{phy_id}") public List findphyresults(@Param("specimen_no") String specimen_no,@Param("phy_id") String phy_id); @Select("select * from QCM_JHY_SAMPLE_CONSIGN_D_ITEM where SPECIMEN_NO = #{specimen_no}") public List findQcmJhySampleConsignDItem(@Param("specimen_no") String specimen_no); @Select("select * from QCM_JHY_SAMPLE_CONSIGN_D_ITEM x join (select specimen_no, seq from Qcm_Jhy_Insp_Physics where specimen_no||guid in (select z.specimen_no||z.guid from qcm_judge_physical_result z where z.phy_id = #{phy_id})) y on x.specimen_no = y.specimen_no and x.seq = y.seq where x.SPECIMEN_NO = #{specimen_no} ") public List findQcmJhySampleConsignDItem4(@Param("specimen_no") String specimen_no,@Param("phy_id") String phy_id); @Select("select * from QCM_JHY_SAMPLE_CONSIGN_D_ITEM x join (select specimen_no, seq from Qcm_Jhy_Insp_Physics where specimen_no||guid in (select z.specimen_no||z.guid from qcm_jhy_insp_physics z where z.SPECIMEN_NO = #{specimen_no})) y on x.specimen_no = y.specimen_no and x.seq = y.seq where x.SPECIMEN_NO = #{specimen_no} ") public List findQcmJhySampleConsignDItem5(@Param("specimen_no") String specimen_no); @Select("select * from QCM_JHY_SAMPLE_CONSIGN_D_ITEM where SPECIMEN_NO = #{specimen_no} or SPECIMEN_NO = #{specimen_no1}") public List findQcmJhySampleConsignDItem3(@Param("specimen_no") String specimen_no,@Param("specimen_no1") String specimen_no1); @Select("select * from QCM_JHY_SAMPLE_CONSIGN_D_ITEM where SPECIMEN_NO = #{specimen_no} and group_seq = #{group_seq}") public List findQcmJhySampleConsignDItem1(@Param("specimen_no") String specimen_no,@Param("group_seq") String group_seq); @Select("select * from QCM_JHY_SAMPLE_CONSIGN_D_ITEM where SPECIMEN_NO = #{specimen_no}") public List findQcmJhySampleConsignDItem2(@Param("specimen_no") String specimen_no); @Select("${sql}") public List queryQcmJudgePhysical(@Param("sql") String sql); @Select("${sql}") public List queryQcmPhyJudgeResult(@Param("sql") String sql); @Select("${sql}") public List queryQcmJudgeChemical(@Param("sql") String sql); @Select("${sql}") public List queryQcmJudgePhysicalUtmM(@Param("sql") String sql); @Select("${sql}") public List queryQcmJudgePhysicalUtmD(@Param("sql") String sql); @Select("${sql}") public List QcmJudgeUltimate(@Param("sql") String sql); @Select("${sql}") public List QcmJudgeLockingApply(@Param("sql") String sql); @Select("${sql}") public List QcmJudgeGpInfo(@Param("sql") String sql); @Select("${sql}") public List queryQcmJudgeUltimateReason(@Param("sql") String sql); @Select("${sql}") public List queryQcmJudgePhysicalResult(@Param("sql") String sql); @Select("${sql}") public List query(@Param("sql") String sql); @Select("${sql}") public List query1(@Param("sql") String sql); @Select(" select t1.*, t2.USEDSTAND std_code, C2N@xgcx(t2.USEDSTAND) std_name, " + " t2.PURPOSEID prod_code, C2N@xgcx(t2.PURPOSEID) prod_name,psel_get_standroll@XGCX(t2.STANDROLLER) DELIVERY_STATE_DESC,t2.PSRNO psc,t2.DEV_SPEC_CD psc_desc from (select '' ht_no,CASE WHEN a.PLD = '4001GX1' THEN 'GX1' WHEN A.PLD = '4001GX2' THEN 'GX2' WHEN A.PLD = '4001BC2' THEN 'BC2' END PLINE_CODE,C2N@xgcx(a.PLD) PLINE_NAME,substr(a.STOVENO,1,10) HEAT_NO,a.STOVENO BOARD_NO,ROLLNUMBER batchno, PLANNO plan_no,a.BILLETID material_no, " + " ORDERNO DESIGN_KEY, a.PLY thick, " + " a.WIDTH, a.LENGTH, a.THEORYWEIGHT weight, '' CUTSIDESTATE, (select name_ from scm_base_info@xgcx where id_ = RuLE) FIXSIZE, " + " PTIME PRODUCETIME, DETERMINANTCARDNUMBER steel_code,DETERMINANTCARDNUMBER steel_name,b.sizeid,b.SURFACEID surface_id,C.STEELCODE GRADE_CODE, C.STEELCODE GRADE_NAME,b.BILLETID_jy INSPECTION_LOT from kcx_turnofflist@xgcx a left join zj_result_all@xgcx b on a.BILLETID = b.BILLETID LEFT JOIN kcx_stufflist@xgcx C ON C.STOVENO = A.STOVENO" + " where 1=1 and a.BILLETID = #{material_no})T1 left join sel_pactdetail@xgcx t2 on t1.design_key = t2.ORDERNO") public List doQueryMaterialInfoX(@Param("material_no") String material_no); @Select("select t1.*,case when (select instroagesteel from KCh_TURNOFFLIST@XGCX where billetid = t1.material_no) is not null then (select instroagesteel from KCh_TURNOFFLIST@XGCX where billetid = t1.material_no) else t2.steelcode end steel_code,C2N@xgcx(t2.STEELCODE) steel_name,t2.USEDSTAND std_code, " + " case when (select STANDARDNO from KCh_TURNOFFLIST@XGCX where billetid = t1.material_no) is not null then (select STANDARDNO from KCh_TURNOFFLIST@XGCX where billetid = t1.material_no) else t2.USEDSTAND end std_name,t2.PURPOSEID prod_code,C2N@xgcx(t2.PURPOSEID) prod_name,psel_get_standroll@XGCX(t2.STANDROLLER) DELIVERY_STATE_DESC, " + " t2.PSRNO psc, t2.DEV_SPEC_CD psc_desc from (select (select rcl_state from kch_turnofflist@xgcx " + " where BILLETID like 'H%' and billetid = a.slabno) ht_no, 'HB1' PLINE_CODE,C2N@xgcx('4001HB1') PLINE_NAME, TO_CHAR(a.heatno) heat_no, TO_CHAR(substr(A.BILLETID, 1, 12) || '0') BOARD_NO,batchno, a.taskno plan_no, " + " a.slabno material_no, a.ORDERID design_key, case when (select ply from KCh_TURNOFFLIST@XGCX where billetid = a.slabno) is not null then (select ply from KCh_TURNOFFLIST@XGCX where billetid = a.slabno) else a.thick end thick, a.width, a.len length, a.weight, " + " (select name_ from scm_base_info where id_ = a.cutsizestate) CUTSIDESTATE, (select name_ from scm_base_info where id_ = a.SIZEMODE) FIXSIZE, " + " a.maketime PRODUCETIME, b.sizeid, b.SURFACEID surface_id, c.bigshopsign grade_code, c.bigshopsign grade_name, b.BILLETID_jy INSPECTION_LOT " + " from (select * from zyhb_plandetailofplate@xgcx) a,zj_result_all@xgcx b,kch_storagelist@xgcx c where a.SLABNO = b.BILLETID " + " and a.billetid = c.billetid and a.slabno = #{material_no}) T1 left join sel_pactdetail@xgcx t2 " + " on t1.design_key = t2.ORDERNO where T1.MATERIAL_NO NOT IN (SELECT BILLETID FROM KCH_TURNOFFLIST@XGCX where ALLOTORDERFORM is not null)" + " union all select t1.*,case when (select instroagesteel from KCh_TURNOFFLIST@XGCX where billetid = t1.material_no) is not null then (select instroagesteel from KCh_TURNOFFLIST@XGCX where billetid = t1.material_no) else t2.steelcode end steel_code,C2N@xgcx(t2.STEELCODE) steel_name,t2.USEDSTAND std_code, " + " case when (select STANDARDNO from KCh_TURNOFFLIST@XGCX where billetid = t1.material_no) is not null then (select STANDARDNO from KCh_TURNOFFLIST@XGCX where billetid = t1.material_no) else t2.USEDSTAND end std_name,t2.PURPOSEID prod_code,C2N@xgcx(t2.PURPOSEID) prod_name,psel_get_standroll@XGCX(t2.STANDROLLER) DELIVERY_STATE_DESC, " + " t2.PSRNO psc, t2.DEV_SPEC_CD psc_desc from (select (select rcl_state from kch_turnofflist@xgcx " + " where BILLETID like 'H%' and billetid = a.slabno) ht_no, 'HB1' PLINE_CODE,C2N@xgcx('4001HB1') PLINE_NAME, TO_CHAR(a.heatno) heat_no, TO_CHAR(substr(A.BILLETID, 1, 12) || '0') BOARD_NO,batchno, a.taskno plan_no, " + " a.slabno material_no, a.ORDERID design_key, case when (select ply from KCh_TURNOFFLIST@XGCX where billetid = a.slabno) is not null then (select ply from KCh_TURNOFFLIST@XGCX where billetid = a.slabno) else a.thick end thick, a.width, a.len length, a.weight, " + " (select name_ from scm_base_info where id_ = a.cutsizestate) CUTSIDESTATE, (select name_ from scm_base_info where id_ = a.SIZEMODE) FIXSIZE, " + " a.maketime PRODUCETIME, b.sizeid, b.SURFACEID surface_id, c.bigshopsign grade_code, c.bigshopsign grade_name, b.BILLETID_jy INSPECTION_LOT " + " from (SELECT * FROM ZYHB_PLANDETAILOFPLATE_ZJNEW@XGCX) a,zj_result_all@xgcx b,kch_storagelist@xgcx c where a.SLABNO = b.BILLETID " + " and a.billetid = c.billetid and a.slabno = #{material_no}) T1 left join sel_pactdetail@xgcx t2 " + " on t1.design_key = t2.ORDERNO where T1.MATERIAL_NO NOT IN (SELECT BILLETID FROM KCH_TURNOFFLIST@XGCX where ALLOTORDERFORM is not null)" + "union all select t1.*, t2.STEELCODE steel_code, C2N@xgcx(t2.STEELCODE) steel_name, t2.USEDSTAND std_code,C2N@xgcx(t2.USEDSTAND) std_name, t2.PURPOSEID prod_code, " + " C2N@xgcx(t2.PURPOSEID) prod_name, psel_get_standroll@XGCX(t2.STANDROLLER) DELIVERY_STATE_DESC, t2.PSRNO psc, t2.DEV_SPEC_CD psc_desc from (select rcl_state, 'HB1' PLINE_CODE, C2N@xgcx('4001HB1') PLINE_NAME, " + " to_char(a.STOVENO) heat_no, substr(A.BILLETID, 1, 12) || '0' BOARD_NO, A.ROLLNUMBER batchno, a.PLANNUMBER plan_no, a.BILLETID material_no, a.ALLOTORDERFORM design_key, a.PLY thick, " + " a.width,a.lenGTH length,a.THEORYWEIGHT weight,(select name_ from scm_base_info where id_ = a.CUTSIDESTATE) CUTSIDESTATE,(select name_ from scm_base_info where id_ = a.ROLE) FIXSIZE, " + " a.PRODUCETIME PRODUCETIME, b.sizeid, b.SURFACEID surface_id,c.bigshopsign grade_code, c.bigshopsign grade_name,b.BILLETID_jy from KCH_TURNOFFLIST@xgcx a, zj_result_all@xgcx b, kch_storagelist@xgcx c, " + " (SELECT * FROM zyhb_plandetailofplate@Xgcx) D where a.BILLETID = b.BILLETID and D.billetid = c.billetid AND A.BILLETID = D.SLABNO " + " and a.billetid = #{material_no}) T1 join sel_pactdetail_new@xgcx t2 on t1.design_key = t2.ORDERNO where t2.orderno like '19%' " + "union all select t1.*, t2.STEELCODE steel_code, C2N@xgcx(t2.STEELCODE) steel_name, t2.USEDSTAND std_code,C2N@xgcx(t2.USEDSTAND) std_name, t2.PURPOSEID prod_code, " + " C2N@xgcx(t2.PURPOSEID) prod_name, psel_get_standroll@XGCX(t2.STANDROLLER) DELIVERY_STATE_DESC, t2.PSRNO psc, t2.DEV_SPEC_CD psc_desc from (select rcl_state, 'HB1' PLINE_CODE, C2N@xgcx('4001HB1') PLINE_NAME, " + " to_char(a.STOVENO) heat_no, substr(A.BILLETID, 1, 12) || '0' BOARD_NO, A.ROLLNUMBER batchno, a.PLANNUMBER plan_no, a.BILLETID material_no, a.ALLOTORDERFORM design_key, a.PLY thick, " + " a.width,a.lenGTH length,a.THEORYWEIGHT weight,(select name_ from scm_base_info where id_ = a.CUTSIDESTATE) CUTSIDESTATE,(select name_ from scm_base_info where id_ = a.ROLE) FIXSIZE, " + " a.PRODUCETIME PRODUCETIME, b.sizeid, b.SURFACEID surface_id,c.bigshopsign grade_code, c.bigshopsign grade_name,b.BILLETID_jy from KCH_TURNOFFLIST@xgcx a, zj_result_all@xgcx b, kch_storagelist@xgcx c, " + " (SELECT * FROM zyhb_plandetailofplate_ZJNEW@XGCX) D where a.BILLETID = b.BILLETID and D.billetid = c.billetid AND A.BILLETID = D.SLABNO " + " and a.billetid = #{material_no}) T1 join sel_pactdetail_new@xgcx t2 on t1.design_key = t2.ORDERNO where t2.orderno like '19%' " + "union all select t1.*, t2.STEELCODE steel_code, C2N@xgcx(t2.STEELCODE) steel_name, t2.USEDSTAND std_code,C2N@xgcx(t2.USEDSTAND) std_name, t2.PURPOSEID prod_code, " + " C2N@xgcx(t2.PURPOSEID) prod_name, psel_get_standroll@XGCX(t2.STANDROLLER) DELIVERY_STATE_DESC, t2.PSRNO psc, t2.DEV_SPEC_CD psc_desc from (select rcl_state, 'HB1' PLINE_CODE, C2N@xgcx('4001HB1') PLINE_NAME, " + " to_char(a.STOVENO) heat_no, substr(A.BILLETID, 1, 12) || '0' BOARD_NO, A.ROLLNUMBER batchno, a.PLANNUMBER plan_no, a.BILLETID material_no, a.ALLOTORDERFORM design_key, a.PLY thick, " + " a.width,a.lenGTH length,a.THEORYWEIGHT weight,(select name_ from scm_base_info where id_ = a.CUTSIDESTATE) CUTSIDESTATE,(select name_ from scm_base_info where id_ = a.ROLE) FIXSIZE, " + " a.PRODUCETIME PRODUCETIME, b.sizeid, b.SURFACEID surface_id,c.bigshopsign grade_code, c.bigshopsign grade_name,b.BILLETID_jy from KCH_TURNOFFLIST@xgcx a, zj_result_all@xgcx b, kch_storagelist@xgcx c, " + " (SELECT * FROM zyhb_plandetailofplate@Xgcx) D where a.BILLETID = b.BILLETID and D.billetid = c.billetid AND A.BILLETID = D.SLABNO(+) " + " and a.billetid = #{material_no}) T1 join sel_pactdetail@xgcx t2 on t1.design_key = t2.ORDERNO" + " union all select t1.*, t2.STEELCODE steel_code, C2N@xgcx(t2.STEELCODE) steel_name, t2.USEDSTAND std_code,C2N@xgcx(t2.USEDSTAND) std_name, t2.PURPOSEID prod_code, " + " C2N@xgcx(t2.PURPOSEID) prod_name, psel_get_standroll@XGCX(t2.STANDROLLER) DELIVERY_STATE_DESC, t2.PSRNO psc, t2.DEV_SPEC_CD psc_desc from (select rcl_state, 'HB1' PLINE_CODE, C2N@xgcx('4001HB1') PLINE_NAME, " + " to_char(a.STOVENO) heat_no, substr(A.BILLETID, 1, 12) || '0' BOARD_NO, A.ROLLNUMBER batchno, a.PLANNUMBER plan_no, a.BILLETID material_no, a.ALLOTORDERFORM design_key, a.PLY thick, " + " a.width,a.lenGTH length,a.THEORYWEIGHT weight,(select name_ from scm_base_info where id_ = a.CUTSIDESTATE) CUTSIDESTATE,(select name_ from scm_base_info where id_ = a.ROLE) FIXSIZE, " + " a.PRODUCETIME PRODUCETIME, b.sizeid, b.SURFACEID surface_id,c.bigshopsign grade_code, c.bigshopsign grade_name,b.BILLETID_jy from KCH_TURNOFFLIST@xgcx a, zj_result_all@xgcx b, kch_storagelist@xgcx c, " + " (SELECT * FROM zyhb_plandetailofplate_ZJNEW@XGCX) D where a.BILLETID = b.BILLETID and D.billetid = c.billetid AND A.BILLETID = D.SLABNO(+) " + " and a.billetid = #{material_no}) T1 join sel_pactdetail@xgcx t2 on t1.design_key = t2.ORDERNO") public List doQueryMaterialInfoHB(@Param("material_no") String material_no); @Select("select t1.*,case when (select instroagesteel from KCh_TURNOFFLIST@XGCX where billetid = t1.material_no) is not null then (select instroagesteel from KCh_TURNOFFLIST@XGCX where billetid = t1.material_no) else t2.steelcode end steel_code,C2N@xgcx(t2.STEELCODE) steel_name,t2.USEDSTAND std_code, " + " case when (select STANDARDNO from KCh_TURNOFFLIST@XGCX where billetid = t1.material_no) is not null then (select STANDARDNO from KCh_TURNOFFLIST@XGCX where billetid = t1.material_no) else t2.USEDSTAND end std_name,t2.PURPOSEID prod_code,C2N@xgcx(t2.PURPOSEID) prod_name,psel_get_standroll@XGCX(t2.STANDROLLER) DELIVERY_STATE_DESC, " + " t2.PSRNO psc, t2.DEV_SPEC_CD psc_desc from (select (select rcl_state from kch_turnofflist@xgcx " + " where BILLETID like 'H%' and billetid = a.slabno) ht_no, 'HB1' PLINE_CODE,C2N@xgcx('4001HB1') PLINE_NAME, TO_CHAR(a.heatno) heat_no, TO_CHAR(substr(A.BILLETID, 1, 12) || '0') BOARD_NO,batchno, a.taskno plan_no, " + " a.slabno material_no, a.ORDERID design_key, case when (select ply from KCh_TURNOFFLIST@XGCX where billetid = a.slabno) is not null then (select ply from KCh_TURNOFFLIST@XGCX where billetid = a.slabno) else a.thick end thick, a.width, a.len length, a.weight, " + " (select name_ from scm_base_info where id_ = a.cutsizestate) CUTSIDESTATE, (select name_ from scm_base_info where id_ = a.SIZEMODE) FIXSIZE, " + " a.maketime PRODUCETIME, '' sizeid, '' surface_id, c.bigshopsign grade_code, c.bigshopsign grade_name, '' INSPECTION_LOT " + " from (select * from zyhb_plandetailofplate@xgcx union all SELECT * FROM ZYHB_PLANDETAILOFPLATE_ZJNEW@XGCX) a,kch_storagelist@xgcx c where " + " a.billetid = c.billetid and a.slabno = #{material_no}) T1 left join sel_pactdetail@xgcx t2 " + " on t1.design_key = t2.ORDERNO where T1.MATERIAL_NO NOT IN (SELECT BILLETID FROM KCH_TURNOFFLIST@XGCX where ALLOTORDERFORM is not null)" + " UNION ALL select t1.*, case when (select insteel from KCz_TURNOFFLIST@XGCX where billetid = t1.material_no) is not null then (select insteel from KCz_TURNOFFLIST@XGCX where billetid = t1.material_no) else t2.steelcode end steel_code, " + " case when (select insteel from KCz_TURNOFFLIST@XGCX where billetid = t1.material_no) is not null then (select insteel from KCz_TURNOFFLIST@XGCX where billetid = t1.material_no) else t2.steelcode end steel_name, t2.USEDSTAND std_code, C2N@xgcx(t2.USEDSTAND) std_name, t2.PURPOSEID prod_code, " + " C2N@xgcx(t2.PURPOSEID) prod_name,psel_get_standroll@XGCX(t2.STANDROLLER) DELIVERY_STATE_DESC,t2.PSRNO psc,t2.DEV_SPEC_CD psc_desc " + " from (select (select rcl_state " + " from kcZ_turnofflist@xgcx where BILLETID like 'Z%' and billetid = a.PRODUCTNO) ht_no, 'ZB1' PLINE_CODE,C2N@xgcx('4001ZB1') PLINE_NAME,a.heatno heat_no, substr(A.BILLETID, 1, 12) || '0' BOARD_NO,A.batchno, a.taskno plan_no, a.PRODUCTNO material_no, a.ORDERNO design_key, " + " case when (select ply from KCz_TURNOFFLIST@XGCX where billetid = a.PRODUCTNO) is not null then (select ply from KCz_TURNOFFLIST@XGCX where billetid = a.PRODUCTNO) else a.height end THICK, a.width,a.lenGTH, a.weight,(select name_ from scm_base_info where id_ = a.CUTSTYLE) CUTSIDESTATE, " + " (select name_ from scm_base_info where id_ = a.SIZESTYLE) FIXSIZE,a.maketime PRODUCETIME, '' sizeid,'' surface_id, " + " c.bigshopsign grade_code, c.bigshopsign grade_name, '' INSPECTION_LOT from (select * from mb_plandetailofplate@xgcx union all select * from mb_plandetailofplate_zjnew@xgcx) a, " + " kcZ_storagelist@xgcx c where a.Billetid = c.billetid and a.PRODUCTNO = #{material_no}) T1 left join sel_pactdetail@xgcx t2 " + " on t1.design_key = t2.ORDERNO where T1.MATERIAL_NO NOT IN (SELECT BILLETID FROM KCz_TURNOFFLIST@XGCX where orderno is not null) ") public List doQueryMaterialInfoZHB1(@Param("material_no") String material_no); @Select(" select t1.*, case when (select insteel from KCz_TURNOFFLIST@XGCX where billetid = t1.material_no) is not null then (select insteel from KCz_TURNOFFLIST@XGCX where billetid = t1.material_no) else t2.steelcode end steel_code, " + " case when (select insteel from KCz_TURNOFFLIST@XGCX where billetid = t1.material_no) is not null then (select insteel from KCz_TURNOFFLIST@XGCX where billetid = t1.material_no) else t2.steelcode end steel_name, t2.USEDSTAND std_code, C2N@xgcx(t2.USEDSTAND) std_name, t2.PURPOSEID prod_code, " + " C2N@xgcx(t2.PURPOSEID) prod_name,psel_get_standroll@XGCX(t2.STANDROLLER) DELIVERY_STATE_DESC,t2.PSRNO psc,t2.DEV_SPEC_CD psc_desc " + " from (select (select rcl_state " + " from kcZ_turnofflist@xgcx where BILLETID like 'Z%' and billetid = a.PRODUCTNO) ht_no, 'ZB1' PLINE_CODE,C2N@xgcx('4001ZB1') PLINE_NAME,a.heatno heat_no, substr(A.BILLETID, 1, 12) || '0' BOARD_NO,A.batchno, a.taskno plan_no, a.PRODUCTNO material_no, a.ORDERNO design_key, " + " case when (select ply from KCz_TURNOFFLIST@XGCX where billetid = a.PRODUCTNO) is not null then (select ply from KCz_TURNOFFLIST@XGCX where billetid = a.PRODUCTNO) else a.height end THICK, a.width,a.lenGTH, a.weight,(select name_ from scm_base_info where id_ = a.CUTSTYLE) CUTSIDESTATE, " + " (select name_ from scm_base_info where id_ = a.SIZESTYLE) FIXSIZE,a.maketime PRODUCETIME, b.sizeid,b.SURFACEID surface_id, " + " c.bigshopsign grade_code, c.bigshopsign grade_name, b.BILLETID_jy INSPECTION_LOT from (select * from mb_plandetailofplate@xgcx) a, zj_result_all@xgcx b, " + " kcZ_storagelist@xgcx c where a.PRODUCTNO = b.BILLETID and a.Billetid = c.billetid and a.PRODUCTNO = #{material_no}) T1 left join sel_pactdetail@xgcx t2 " + " on t1.design_key = t2.ORDERNO where T1.MATERIAL_NO NOT IN (SELECT BILLETID FROM KCz_TURNOFFLIST@XGCX where orderno is not null) " + " union all select t1.*, case when (select insteel from KCz_TURNOFFLIST@XGCX where billetid = t1.material_no) is not null then (select insteel from KCz_TURNOFFLIST@XGCX where billetid = t1.material_no) else t2.steelcode end steel_code, " + " case when (select insteel from KCz_TURNOFFLIST@XGCX where billetid = t1.material_no) is not null then (select insteel from KCz_TURNOFFLIST@XGCX where billetid = t1.material_no) else t2.steelcode end steel_name, t2.USEDSTAND std_code, C2N@xgcx(t2.USEDSTAND) std_name, t2.PURPOSEID prod_code, " + " C2N@xgcx(t2.PURPOSEID) prod_name,psel_get_standroll@XGCX(t2.STANDROLLER) DELIVERY_STATE_DESC,t2.PSRNO psc,t2.DEV_SPEC_CD psc_desc " + " from (select (select rcl_state " + " from kcZ_turnofflist@xgcx where BILLETID like 'Z%' and billetid = a.PRODUCTNO) ht_no, 'ZB1' PLINE_CODE,C2N@xgcx('4001ZB1') PLINE_NAME,a.heatno heat_no, substr(A.BILLETID, 1, 12) || '0' BOARD_NO,A.batchno, a.taskno plan_no, a.PRODUCTNO material_no, a.ORDERNO design_key, " + " case when (select ply from KCz_TURNOFFLIST@XGCX where billetid = a.PRODUCTNO) is not null then (select ply from KCz_TURNOFFLIST@XGCX where billetid = a.PRODUCTNO) else a.height end THICK, a.width,a.lenGTH, a.weight,(select name_ from scm_base_info where id_ = a.CUTSTYLE) CUTSIDESTATE, " + " (select name_ from scm_base_info where id_ = a.SIZESTYLE) FIXSIZE,a.maketime PRODUCETIME, b.sizeid,b.SURFACEID surface_id, " + " c.bigshopsign grade_code, c.bigshopsign grade_name, b.BILLETID_jy INSPECTION_LOT from (select * from mb_plandetailofplate_zjnew@xgcx) a, zj_result_all@xgcx b, " + " kcZ_storagelist@xgcx c where a.PRODUCTNO = b.BILLETID and a.Billetid = c.billetid and a.PRODUCTNO = #{material_no}) T1 left join sel_pactdetail@xgcx t2 " + " on t1.design_key = t2.ORDERNO where T1.MATERIAL_NO NOT IN (SELECT BILLETID FROM KCz_TURNOFFLIST@XGCX where orderno is not null) " + "union all select t1.*, t2.STEELCODE steel_code, C2N@xgcx(t2.STEELCODE) steel_name, t2.USEDSTAND std_code,C2N@xgcx(t2.USEDSTAND) std_name, t2.PURPOSEID prod_code, " + " C2N@xgcx(t2.PURPOSEID) prod_name, psel_get_standroll@XGCX(t2.STANDROLLER) DELIVERY_STATE_DESC, t2.PSRNO psc, t2.DEV_SPEC_CD psc_desc from (select rcl_state, 'ZB1' PLINE_CODE, C2N@xgcx('4001ZB1') PLINE_NAME, " + " to_char(a.STOVENO) heat_no, substr(A.BILLETID, 1, 12) || '0' BOARD_NO, A.ROLLNUMBER batchno, a.planplanno plan_no, a.BILLETID material_no, a.ORDERNO design_key, a.PLY thick, " + " a.width,a.lenGTH length,a.THEORYWEIGHT weight,(select name_ from scm_base_info where id_ = a.CUTSIDESTATE) CUTSIDESTATE,(select name_ from scm_base_info where id_ = a.rule) FIXSIZE, " + " a.PTIME PRODUCETIME, b.sizeid, b.SURFACEID surface_id,c.bigshopsign grade_code, c.bigshopsign grade_name,b.BILLETID_jy from KCZ_TURNOFFLIST@xgcx a, zj_result_all@xgcx b, kcz_storagelist@xgcx c, " + " (select * from mb_plandetailofplate@xgcx) D where a.BILLETID = b.BILLETID and D.billetid = c.billetid AND A.BILLETID = D.PRODUCTNO " + " and a.billetid = #{material_no}) T1 join sel_pactdetail_new@xgcx t2 on t1.design_key = t2.ORDERNO where t2.orderno like '19%' " + "union all select t1.*, t2.STEELCODE steel_code, C2N@xgcx(t2.STEELCODE) steel_name, t2.USEDSTAND std_code,C2N@xgcx(t2.USEDSTAND) std_name, t2.PURPOSEID prod_code, " + " C2N@xgcx(t2.PURPOSEID) prod_name, psel_get_standroll@XGCX(t2.STANDROLLER) DELIVERY_STATE_DESC, t2.PSRNO psc, t2.DEV_SPEC_CD psc_desc from (select rcl_state, 'ZB1' PLINE_CODE, C2N@xgcx('4001ZB1') PLINE_NAME, " + " to_char(a.STOVENO) heat_no, substr(A.BILLETID, 1, 12) || '0' BOARD_NO, A.ROLLNUMBER batchno, a.planplanno plan_no, a.BILLETID material_no, a.ORDERNO design_key, a.PLY thick, " + " a.width,a.lenGTH length,a.THEORYWEIGHT weight,(select name_ from scm_base_info where id_ = a.CUTSIDESTATE) CUTSIDESTATE,(select name_ from scm_base_info where id_ = a.rule) FIXSIZE, " + " a.PTIME PRODUCETIME, b.sizeid, b.SURFACEID surface_id,c.bigshopsign grade_code, c.bigshopsign grade_name,b.BILLETID_jy from KCZ_TURNOFFLIST@xgcx a, zj_result_all@xgcx b, kcz_storagelist@xgcx c, " + " (select * from mb_plandetailofplate_zjnew@xgcx) D where a.BILLETID = b.BILLETID and D.billetid = c.billetid AND A.BILLETID = D.PRODUCTNO " + " and a.billetid = #{material_no}) T1 join sel_pactdetail_new@xgcx t2 on t1.design_key = t2.ORDERNO where t2.orderno like '19%' " + " union all select t1.*, t2.STEELCODE steel_code, C2N@xgcx(t2.STEELCODE) steel_name, t2.USEDSTAND std_code,C2N@xgcx(t2.USEDSTAND) std_name, t2.PURPOSEID prod_code, " + " C2N@xgcx(t2.PURPOSEID) prod_name, psel_get_standroll@XGCX(t2.STANDROLLER) DELIVERY_STATE_DESC, t2.PSRNO psc, t2.DEV_SPEC_CD psc_desc from (select rcl_state, 'ZB1' PLINE_CODE, C2N@xgcx('4001ZB1') PLINE_NAME, " + " to_char(a.STOVENO) heat_no, substr(A.BILLETID, 1, 12) || '0' BOARD_NO, A.ROLLNUMBER batchno, a.planplanno plan_no, a.BILLETID material_no, a.ORDERNO design_key, a.PLY thick, " + " a.width,a.lenGTH length,a.THEORYWEIGHT weight,(select name_ from scm_base_info where id_ = a.CUTSIDESTATE) CUTSIDESTATE,(select name_ from scm_base_info where id_ = a.rule) FIXSIZE, " + " a.PTIME PRODUCETIME, b.sizeid, b.SURFACEID surface_id,c.bigshopsign grade_code, c.bigshopsign grade_name,b.BILLETID_jy from KCZ_TURNOFFLIST@xgcx a, zj_result_all@xgcx b, kcz_storagelist@xgcx c, " + " (select * from mb_plandetailofplate@xgcx) D where a.BILLETID = b.BILLETID and D.billetid = c.billetid AND A.BILLETID = D.PRODUCTNO " + " and a.billetid = #{material_no}) T1 join sel_pactdetail@xgcx t2 on t1.design_key = t2.ORDERNO" + " union all select t1.*, t2.STEELCODE steel_code, C2N@xgcx(t2.STEELCODE) steel_name, t2.USEDSTAND std_code,C2N@xgcx(t2.USEDSTAND) std_name, t2.PURPOSEID prod_code, " + " C2N@xgcx(t2.PURPOSEID) prod_name, psel_get_standroll@XGCX(t2.STANDROLLER) DELIVERY_STATE_DESC, t2.PSRNO psc, t2.DEV_SPEC_CD psc_desc from (select rcl_state, 'ZB1' PLINE_CODE, C2N@xgcx('4001ZB1') PLINE_NAME, " + " to_char(a.STOVENO) heat_no, substr(A.BILLETID, 1, 12) || '0' BOARD_NO, A.ROLLNUMBER batchno, a.planplanno plan_no, a.BILLETID material_no, a.ORDERNO design_key, a.PLY thick, " + " a.width,a.lenGTH length,a.THEORYWEIGHT weight,(select name_ from scm_base_info where id_ = a.CUTSIDESTATE) CUTSIDESTATE,(select name_ from scm_base_info where id_ = a.rule) FIXSIZE, " + " a.PTIME PRODUCETIME, b.sizeid, b.SURFACEID surface_id,c.bigshopsign grade_code, c.bigshopsign grade_name,b.BILLETID_jy from KCZ_TURNOFFLIST@xgcx a, zj_result_all@xgcx b, kcz_storagelist@xgcx c, " + " (select * from mb_plandetailofplate_zjnew@xgcx) D where a.BILLETID = b.BILLETID and D.billetid = c.billetid AND A.BILLETID = D.PRODUCTNO " + " and a.billetid = #{material_no}) T1 join sel_pactdetail@xgcx t2 on t1.design_key = t2.ORDERNO") public List doQueryMaterialInfoZB(@Param("material_no") String material_no); @Select("select * from (select ''ht_no,'RZ1' PLINE_CODE,'热轧线' PLINE_NAME,SUBSTR(T.SLAB_NO, 1, 10) HEAT_NO,T.SLAB_NO BOARD_NO,SUBSTR(t.OLD_SAMPL_NO, 1, 10) batchno,t1.ROLL_MANA_NO plan_no, T.OLD_SAMPL_NO material_no, T.ORD_NO || T.ORD_SEQ DESIGN_KEY, (select PSC from tbb01_ord_prod F WHERE F.ORD_NO= T.ORD_NO AND ROWNUM=1) psc,'' psc_desc, " + " t.PRDNM_CD DELIVERY_STATE_DESC, T.instr_COIL_THK THICK,T.instr_COIL_WTH WIDTH, T.instr_COIL_LEN LENGTH,T.ACT_WGT/1000 WEIGHT,''CUTSIDESTATE,''FIXSIZE, to_date(substr(t.MILL_DTIME,'0','8'), 'yyyy-mm-dd') PRODUCETIME,b.sizeid,nvl(t2.LG_STL_GRD, t2.STL_GRD) GRADE_CODE,nvl(t2.LG_STL_GRD, t2.STL_GRD) GRADE_NAME,b.INSPECTION_LOT,T.SPEC_STL_GRD steel_code," + " T.SPEC_STL_GRD steel_name,T.SPEC_ABBSYM std_code,T.SPEC_ABBSYM std_name, T.ORD_USE_CD prod_code,PKG_QUALITY_COMM.PK00_COMM('A01007',T.ORD_USE_CD) prod_name " + " from tbh02_coil_comm t,tbf02_spec_mill t1,QCM_JUDGE_COIL_RESULT b,tbg02_slab_comm t2 where t.COIL_NO=t1.COIL_NO(+) AND t.SLAB_NO = t2.SLAB_NO and t.OLD_SAMPL_NO=b.coil_no and t.OLD_SAMPL_NO = #{material_no} " + "union all select '' ht_no,'SZ1','酸轧线',SUBSTR(T.SLAB_NO, 1, 10),T.SLAB_NO,SUBSTR(t.OLD_SAMPL_NO, 1, 9), t1.ROLL_MANA_NO, T.OLD_SAMPL_NO, T.ORD_NO || T.ORD_SEQ, (select PSC from tbb01_ord_prod F WHERE F.ORD_NO= T.ORD_NO AND ROWNUM=1) psc,'' psc_desc, " + " t.PRDNM_CD, T.instr_COIL_THK, T.instr_COIL_WTH,T.instr_COIL_LEN,T.ACT_WGT/1000,'','',to_date(substr(t.MILL_DTIME,'0','8'), 'yyyy-mm-dd') PRODUCETIME,b.sizeid,nvl(t2.LG_STL_GRD, t2.STL_GRD) GRADE_CODE,nvl(t2.LG_STL_GRD, t2.STL_GRD) GRADE_NAME,b.INSPECTION_LOT,T.SPEC_STL_GRD," + "T.SPEC_STL_GRD,T.SPEC_ABBSYM,T.SPEC_ABBSYM, T.ORD_USE_CD,PKG_QUALITY_COMM.PK00_COMM('A01007',T.ORD_USE_CD) " + " from c_tbl02_coil_comm t,c_tbf03_spec_mill t1,QCM_JUDGE_COIL_RESULT b,tbg02_slab_comm t2 where t.COIL_NO=t1.C_COIL_NO(+) AND t.SLAB_NO = t2.SLAB_NO and t.OLD_SAMPL_NO=b.coil_no and t.OLD_SAMPL_NO = #{material_no}" + " union all select '' ht_no,'LT1','连退线',SUBSTR(T.SLAB_NO, 1, 10),T.SLAB_NO,SUBSTR(t.OLD_SAMPL_NO, 1, 9), t1.CAL_NO, T.OLD_SAMPL_NO,T.ORD_NO || T.ORD_SEQ,(select PSC from tbb01_ord_prod F WHERE F.ORD_NO= T.ORD_NO AND ROWNUM=1) psc,'' psc_desc, " + "t.PRDNM_CD,T.instr_COIL_THK,T.instr_COIL_WTH,T.instr_COIL_LEN,T.ACT_WGT/1000,'','',to_date(substr(t.MILL_DTIME,'0','8'), 'yyyy-mm-dd') PRODUCETIME,b.sizeid,nvl(t2.LG_STL_GRD, t2.STL_GRD) GRADE_CODE,nvl(t2.LG_STL_GRD, t2.STL_GRD) GRADE_NAME,b.INSPECTION_LOT,T.SPEC_STL_GRD," + "T.SPEC_STL_GRD,T.SPEC_ABBSYM,T.SPEC_ABBSYM, T.ORD_USE_CD,PKG_QUALITY_COMM.PK00_COMM('A01007',T.ORD_USE_CD) " + " from c_tbc02_coil_comm t,l_tbf03_spec_mill t1,QCM_JUDGE_COIL_RESULT b,tbg02_slab_comm t2 where t.ORI_OLD_SAMPL_NO=t1.C_COIL_NO(+) AND t.SLAB_NO = t2.SLAB_NO and t.OLD_SAMPL_NO=b.coil_no and t.OLD_SAMPL_NO = #{material_no}) ") public List doQueryMaterialInfoJ(@Param("material_no") String material_no); @Select("select '' ht_no,'YT1' PLINE_CODE,'优特' PLINE_NAME, t.HEATNO HEAT_NO,T.HEATNO BOARD_NO,t.BATCHNO,'' plan_no,t.COILNO MATERIAL_NO,substr(ORDERNO, 0, length(ORDERNO) - 6) || substr(ORDERNO, -3, 3) as DESIGN_KEY,'' PSC,'' PSC_DESC,T.INSPECTION_LOT INSPECTION_LOT1, soi.LEVEL_NAME C_EXTSHAPE_REQ," + " t.DELIVERY_STATE_DESC, t.THICK,t.WIDTH, t.LENGTH,t.THEORYWEIGHT,t.FACTWEIGHT/1000 WEIGHT,'' CUTSIDESTATE,'' FIXSIZE,t.PRODUCEDATE PRODUCETIME, b.sizeid,b.final_processes, " + " '' GRADE_CODE,'' GRADE_NAME,b.INSPECTION_LOT, cbs.STEEL_code,cbs.STEEL_NAME,T.std_code,t.STD_NAME,T.prod_code,t.PROD_NAME,pp.THICK plan_thick,PP.width plan_width,y.PASS_EXIT_THICK,y.num from YDM_PRODUCT_DETAIL@LINK_YTG t " + " left join COM_BASE_STEEL@LINK_YTG cbs on t.STEELCODE = cbs.STEEL_CODE left join SLM_ORDER_INFO@LINK_YTG soi on t.ORDERNO = soi.CONTRACT_NO || soi.CONTRACT_LINE_SEQ || soi.ORDER_SEQ " + " and nvl(soi.VALID_FLAG, '0') = '1' and nvl(soi.CHANGE_FLAG, '0') != '1' left join PLN_PRODORDER@LINK_YTG pp on soi.CONTRACT_NO || soi.CONTRACT_LINE_SEQ || soi.ORDER_SEQ = pp.ORDER_NO left join PLN_MATERIEL_DETAIL@LINK_YTG pmd on t.COILNO = pmd.OBJECT_NO JOIN QCM_JUDGE_YT_COIL_RESULT B ON T.COILNO = B.COIL_NO left join ( " + " select row_number() over (partition by COIL_NO order by PASS_END_TIME desc ) rn,COIL_NO, PASS_EXIT_THICK from MES_RCM_D_PASS@link_ytg )y on t.COILNO = y.COIL_NO and rn = 1 left join ( select count(1) as num,MATERAILCOILNO from ( " + " select MATERAILCOILNO from YDM_PRODUCT_DETAIL@LINK_YTG ypd join MES_RCM_D@LINK_YTG d on ypd.COILNO = d.COIL_NO where substr(ypd.COILNO, -1, 1) not in ('H', 'B') and ypd.MACHINE_CODE = 'M0008' group by ypd.MATERAILCOILNO,d.PROCESS_NO ) group by MATERAILCOILNO )y on y.MATERAILCOILNO = t.MATERAILCOILNO where t.STATE in ('0','-1', '7') and coilno = #{material_no} ") public List doQueryMaterialInfoY(@Param("material_no") String material_no); @Select("select '' ht_no,'YT1' PLINE_CODE,'优特' PLINE_NAME, t.HEATNO HEAT_NO,T.HEATNO BOARD_NO,t.BATCHNO,'' plan_no,t.COILNO MATERIAL_NO,substr(ORDERNO, 0, length(ORDERNO) - 6) || substr(ORDERNO, -3, 3) as DESIGN_KEY,'' PSC,'' PSC_DESC,T.INSPECTION_LOT INSPECTION_LOT1, soi.LEVEL_NAME C_EXTSHAPE_REQ," + " t.DELIVERY_STATE_DESC, t.THICK,t.WIDTH, t.LENGTH,t.THEORYWEIGHT,t.FACTWEIGHT/1000 WEIGHT,'' CUTSIDESTATE,'' FIXSIZE,t.PRODUCEDATE PRODUCETIME, b.sizeid,b.final_processes, " + " '' GRADE_CODE,'' GRADE_NAME,b.INSPECTION_LOT, cbs.STEEL_code,cbs.STEEL_NAME,T.std_code,t.STD_NAME,T.prod_code,t.PROD_NAME,pp.THICK plan_thick,PP.width plan_width,y.PASS_EXIT_THICK,y.num from YDM_PRODUCT_DETAIL@LINK_YTG t " + " left join COM_BASE_STEEL@LINK_YTG cbs on t.STEELCODE = cbs.STEEL_CODE left join SLM_ORDER_INFO@LINK_YTG soi on t.ORDERNO = soi.CONTRACT_NO || soi.CONTRACT_LINE_SEQ || soi.ORDER_SEQ " + " and nvl(soi.VALID_FLAG, '0') = '1' and nvl(soi.CHANGE_FLAG, '0') != '1' left join PLN_PRODORDER@LINK_YTG pp on soi.CONTRACT_NO || soi.CONTRACT_LINE_SEQ || soi.ORDER_SEQ = pp.ORDER_NO left join PLN_MATERIEL_DETAIL@LINK_YTG pmd on t.COILNO = pmd.OBJECT_NO JOIN QCM_JUDGE_YT_COIL_RESULT B ON T.COILNO = B.COIL_NO left join ( " + " select row_number() over (partition by COIL_NO order by PASS_END_TIME desc ) rn,COIL_NO, PASS_EXIT_THICK from MES_RCM_D_PASS@link_ytg )y on t.COILNO = y.COIL_NO and rn = 1 left join ( select count(1) as num,MATERAILCOILNO from ( " + " select MATERAILCOILNO from YDM_PRODUCT_DETAIL@LINK_YTG ypd join MES_RCM_D@LINK_YTG d on ypd.COILNO = d.COIL_NO where substr(ypd.COILNO, -1, 1) not in ('H', 'B') and ypd.MACHINE_CODE = 'M0008' group by ypd.MATERAILCOILNO,d.PROCESS_NO ) group by MATERAILCOILNO )y on y.MATERAILCOILNO = t.MATERAILCOILNO where coilno = #{material_no} ") public List doQueryMaterialInfoY1(@Param("material_no") String material_no); @Select("select * from (select ''ht_no,'RZ1' PLINE_CODE,'热轧线' PLINE_NAME,SUBSTR(T.SLAB_NO, 1, 10) HEAT_NO,T.SLAB_NO BOARD_NO,SUBSTR(t.OLD_SAMPL_NO, 1, 10) batchno,t1.ROLL_MANA_NO plan_no, T.OLD_SAMPL_NO material_no, T.ORD_NO || T.ORD_SEQ DESIGN_KEY, (select PSC from tbb01_ord_prod F WHERE F.ORD_NO= T.ORD_NO AND ROWNUM=1) psc,'' psc_desc, " + " t.PRDNM_CD DELIVERY_STATE_DESC, T.instr_COIL_THK THICK,T.instr_COIL_WTH WIDTH, T.instr_COIL_LEN LENGTH,T.ACT_WGT/1000 WEIGHT,''CUTSIDESTATE,''FIXSIZE, to_date(substr(t.MILL_DTIME,'0','8'), 'yyyy-mm-dd') PRODUCETIME,b.sizeid,nvl(t2.LG_STL_GRD, t2.STL_GRD) GRADE_CODE,nvl(t2.LG_STL_GRD, t2.STL_GRD) GRADE_NAME,b.INSPECTION_LOT,T.SPEC_STL_GRD steel_code," + " T.SPEC_STL_GRD steel_name,T.SPEC_ABBSYM std_code,T.SPEC_ABBSYM std_name, T.ORD_USE_CD prod_code,PKG_QUALITY_COMM.PK00_COMM('A01007',T.ORD_USE_CD) prod_name " + " from tbh02_coil_comm t,tbf02_spec_mill t1,QCM_JUDGE_COIL_RESULT b,tbg02_slab_comm t2 where t.COIL_NO=t1.COIL_NO(+) AND t.SLAB_NO = t2.SLAB_NO and t.OLD_SAMPL_NO=b.coil_no and t.OLD_SAMPL_NO = #{material_no} " + "union all select '' ht_no,'SZ1','酸轧线',SUBSTR(T.SLAB_NO, 1, 10),T.SLAB_NO,SUBSTR(t.OLD_SAMPL_NO, 1, 12), t1.ROLL_MANA_NO, T.OLD_SAMPL_NO, T.ORD_NO || T.ORD_SEQ, (select PSC from tbb01_ord_prod F WHERE F.ORD_NO= T.ORD_NO AND ROWNUM=1) psc,'' psc_desc, " + " t.PRDNM_CD, T.instr_COIL_THK, T.instr_COIL_WTH,T.instr_COIL_LEN,T.ACT_WGT/1000,'','',to_date(substr(t.MILL_DTIME,'0','8'), 'yyyy-mm-dd') PRODUCETIME,b.sizeid,nvl(t2.LG_STL_GRD, t2.STL_GRD) GRADE_CODE,nvl(t2.LG_STL_GRD, t2.STL_GRD) GRADE_NAME,b.INSPECTION_LOT,T.SPEC_STL_GRD," + "T.SPEC_STL_GRD,T.SPEC_ABBSYM,T.SPEC_ABBSYM, T.ORD_USE_CD,PKG_QUALITY_COMM.PK00_COMM('A01007',T.ORD_USE_CD) " + " from c_tbl02_coil_comm t,c_tbf03_spec_mill t1,QCM_JUDGE_COIL_RESULT b,tbg02_slab_comm t2 where t.COIL_NO=t1.C_COIL_NO(+) AND t.SLAB_NO = t2.SLAB_NO and t.OLD_SAMPL_NO=b.coil_no and t.OLD_SAMPL_NO = #{material_no}" + " union all select '' ht_no,'LT1','连退线',SUBSTR(T.SLAB_NO, 1, 10),T.SLAB_NO,SUBSTR(t.OLD_SAMPL_NO, 1, 12), t1.CAL_NO, T.OLD_SAMPL_NO,T.ORD_NO || T.ORD_SEQ,(select PSC from tbb01_ord_prod F WHERE F.ORD_NO= T.ORD_NO AND ROWNUM=1) psc,'' psc_desc, " + "t.PRDNM_CD,T.instr_COIL_THK,T.instr_COIL_WTH,T.instr_COIL_LEN,T.ACT_WGT/1000,'','',to_date(substr(t.MILL_DTIME,'0','8'), 'yyyy-mm-dd') PRODUCETIME,b.sizeid,nvl(t2.LG_STL_GRD, t2.STL_GRD) GRADE_CODE,nvl(t2.LG_STL_GRD, t2.STL_GRD) GRADE_NAME,b.INSPECTION_LOT,T.SPEC_STL_GRD," + "T.SPEC_STL_GRD,T.SPEC_ABBSYM,T.SPEC_ABBSYM, T.ORD_USE_CD,PKG_QUALITY_COMM.PK00_COMM('A01007',T.ORD_USE_CD) " + " from c_tbc02_coil_comm t,l_tbf03_spec_mill t1,QCM_JUDGE_COIL_RESULT b,tbg02_slab_comm t2 where t.ORI_OLD_SAMPL_NO=t1.C_COIL_NO(+) AND t.SLAB_NO = t2.SLAB_NO and t.OLD_SAMPL_NO=b.coil_no and t.OLD_SAMPL_NO = #{material_no}) ") public List doQueryMaterialInfoJ1(@Param("material_no") String material_no); @Select("${sql}") public List queryQCMBaseTolerance(@Param("sql") String sql); @Select("${sql}") public List queryQcmJhyInspElements(@Param("sql") String sql); @Select("${sql}") public List queryQcmJhyPhyresult(@Param("sql") String sql); @Select("${sql}") public List queryQCMOrdDesignStdCic(@Param("sql") String sql); @Select("${sql}") public List queryQCMOrdDesignStdPic(@Param("sql") String sql); @Select("${sql}") public List queryQCMOrdDesignStdSic(@Param("sql") String sql); @Select("${sql}") public List queryQCMOrdDesignStd(@Param("sql") String sql); @Select("${sql}") public List queryQcmJudgeSurfaces(@Param("sql") String sql); @Select("${sql}") public List queryQcmJudgeSurfaceFlaw(@Param("sql") String sql); @Select("${sql}") public List queryQcmJudgeOperateLog(@Param("sql") String sql); @Select("${sql}") public List queryQCMJudgeMeasures(@Param("sql") String sql); @Select("${sql}") public List queryQcmJudgeUltimates(@Param("sql") String sql); @Select("select * from qcm_ord_design_std where design_key = #{design_key} and psc = ${psc}") public QCMOrdDesignStd findQCMOrdDesignStd(@Param("design_key") String design_key,@Param("psc") String psc); @Select(" select * from qcm_ord_design_std_pic where design_key = #{design_key} and steel_name = #{steel_name}") public List findQcmOrdDesignStdPic(@Param("design_key") String design_key,@Param("steel_name") String steel_name); @Select(" select * from qcm_ord_design_std_pic where design_key = #{design_key}") public List findQcmOrdDesignStdPic4(@Param("design_key") String design_key); @Select(" select * from qcm_ord_design_std_pic where design_key = #{design_key} and steel_name = #{steel_name} AND PHY_desc_L = '夹杂' ") public List findQcmOrdDesignStdPic3(@Param("design_key") String design_key,@Param("steel_name") String steel_name); @Select(" select * from qcm_ord_design_std_pic where design_key = #{design_key} and steel_name = #{steel_name} and process_code = #{process_code}") public List findQcmOrdDesignStdPic2(@Param("design_key") String design_key,@Param("steel_name") String steel_name,@Param("process_code") String process_code); @Select("select * from qcm_ord_design_std_pic where design_key = #{design_key} and SPECL_FL = #{specl_fl}") public List findQcmOrdDesignStdPic1(@Param("design_key") String design_key,@Param("specl_fl") String specl_fl); @Update(" update qcm_jhy_sample_r_ord set judge_status = #{ord.judge_status}, phy_id = #{ord.phy_id}, JUDGE_RESULT_CODE = #{ord.judge_result_code},JUDGE_RESULT_DESC = #{ord.judge_result_desc}, " + "judge_name = #{ord.judge_name},judge_time = #{ord.judge_time},judge_memo = #{ord.judge_memo} where DESIGN_KEY = #{ord.design_key} and SMP_NO = #{ord.smp_no} and psc = #{ord.psc}") public void update(@Param("ord") QcmJhySampleROrd ord); @Select("select * from QCM_JHY_SAMPLE_R_ORD t left join qcm_jhy_sample_consign_m m on m.smp_no=t.smp_no where t.design_key=#{design_key} " +" and m.smp_catg=#{smp_catg} and T.INSPECTION_LOT=#{INSPECTION_LOT}") public List findInfo(@Param("design_key") String design_key,@Param("smp_catg") String smp_catg,@Param("INSPECTION_LOT") String INSPECTION_LOT); @Select("select * from QCM_JHY_SAMPLE_R_ORD t left join qcm_jhy_sample_consign_m m on m.smp_no=t.smp_no where " +" m.smp_catg=#{smp_catg} and T.INSPECTION_LOT=#{INSPECTION_LOT}") public List findInfo1(@Param("smp_catg") String smp_catg,@Param("INSPECTION_LOT") String INSPECTION_LOT); public YdmProductDetail queryYdmProductDetail(@Param("material_no") String material_no); @Select("select * from QCM_JUDGE_ULTIMATE where material_no = #{material_no}") public List queryQcmJudgeUltimate(@Param("material_no") String material_no); @Select("SELECT * FROM (select * from QCM_JUDGE_LOCKING where 1=1 ${condition}) WHERE ROWNUM <= 1") public List queryQCMJudgeLocking(@Param("condition") String condition); @Select("${sql}") public List queryQCMJudgeLockings(@Param("sql") String sql); @Select("select * from QCM_JHY_SAMPLE_R_ORD where design_key = #{design_key} and rownum = 1") public QcmJhySampleROrd queryQcmJhySampleROrd(@Param("design_key") String design_key); @Select("${sql}") public List queryQcmJhySampleROrd1(@Param("sql") String sql); @Select("select * from qcm_judge_measure where sic_id = #{sic_id}") public QCMJudgeMeasure queryQCMJudgeMeasure(@Param("sic_id") String sic_id); @Select("select * from qcm_judge_measure_d where sic_id = #{sic_id}") public List queryQcmJudgeMeasureD(@Param("sic_id") String sic_id); public void UpdateSurfaceFlaw(@Param("sufId") String sufId,@Param("FlawSeq") String FlawSeq,@Param("flag") String flag); public void UpdateSurfaceFlaws(@Param("sufId") String sufId,@Param("FlawSeq") String FlawSeq,@Param("flag") String flag,@Param("fixed_type_desc") String fixed_type_desc,@Param("fixed_thick") String fixed_thick); public void UpdateQcmJudgeLocking(@Param("qjl") QCMJudgeLocking qjl); public void UpdateQcmJudgeLocking1(@Param("qjl") QCMJudgeLocking qjl); public void UpdateQcmJudgeLocking2(@Param("qjl") QCMJudgeLocking qjl); public void UpdateQcmJudgeLocking3(@Param("qjl") QCMJudgeLocking qjl); public void UpdateQcmJudgeLocking4(@Param("qjl") QCMJudgeLocking qjl); public void UpdateQcmJudgeLocking5(@Param("qjl") QCMJudgeLocking qjl); public void UpdateQcmJudgeLocking9(@Param("qjl") QCMJudgeLocking qjl); public void UpdateQcmJudgeLocking6(@Param("qjl") QCMJudgeLocking qjl); public void UpdateQcmJudgeLocking7(@Param("qjl") QCMJudgeLocking qjl); public void UpdateQcmJudgeLocking8(@Param("qjl") QCMJudgeLocking qjl); public void UpdateQcmJhySampleROrd(@Param("q") QcmJhySampleROrd q); public void UpdateQcmJhySampleROrd1(@Param("q") QcmJhySampleROrd q); @Update("update qcm_judge_physical t set t.cancel_memo = #{remark},t.cancel_name = #{username} where phy_id = #{phy_id}") public void CancelPhysical(@Param("phy_id") String phy_id,@Param("remark") String remark,@Param("username")String username); @Update("update QCM_JUDGE_ULTIMATE t set t.cancel_memo = #{remark},t.cancel_name = #{username},t.cancel_time = sysdate where utm_id = #{utm_id}") public void CancelUltimate(@Param("utm_id") String utm_id,@Param("remark") String remark,@Param("username")String username); @Update(" update qcm_judge_locking set status = '3',check_time = sysdate,check_name = #{username} where apply_id = #{apply_id} ") public void ApplyBack(@Param("apply_id") String apply_id,@Param("username")String username); @Update(" update qcm_judge_locking_apply set status = #{status},check_name = #{username},check_time = sysdate where apply_id = #{apply_id} ") public void doApply(@Param("apply_id") String apply_id,@Param("username") String username,@Param("status") String status); public void UpdateQcmJudgeSurface(@Param("q") QcmJudgeSurface q); public void UpdateQcmJudgeSurface1(@Param("q") QcmJudgeSurface q); public void UpdateQcmJudgeUltimateByUtmid(@Param("utm_id") String utm_id); public void UpdateQcmJudgeUltimate(@Param("q") QcmJudgeUltimate q); //取消第三方申报 public void cancelLoad(@Param("material_no")String material_no,@Param("username")String username,@Param("remark") String remark); //清空成分判定结果-产销 public void clearCHEMRESULT(@Param("inspection_lot")String inspection_lot); //清空成分判定结果-卷板 public void clearCHEMRESULT1(@Param("inspection_lot")String inspection_lot); //清空材质判定结果 产销 public void updatePHYRESULT(@Param("inspection_lot")String inspection_lot); //清空材质判定结果 卷板 public void updatePHYRESULT1(@Param("inspection_lot")String inspection_lot); //清空综合判定结果 产销 public void clearJUDGERESULT(@Param("material_no")String material_no,@Param("inspection_lot")String inspection_lot); //清空综合判定结果 卷板 public void clearJUDGERESULT1(@Param("material_no")String material_no,@Param("inspection_lot")String inspection_lot); //清空综合判定结果 优特钢带 public void clearJUDGERESULT2(@Param("material_no")String material_no,@Param("inspection_lot")String inspection_lot); //新增成分判定结果 产销 //public void updateCHEMRESULT2(@Param("inspection_lot")String inspection_lot,@Param("CHEMRESULT_DESC") String CHEMRESULT_DESC,@Param("CHEMRESULT") String CHEMRESULT,@Param("CHEMID") String CHEMID,@Param("CIC_IS_LOCK") String CIC_IS_LOCK); //新增成分判定结果 卷板 //public void updateCHEMRESULT3(@Param("inspection_lot")String inspection_lot,@Param("CHEMRESULT_DESC") String CHEMRESULT_DESC,@Param("CHEMRESULT") String CHEMRESULT,@Param("CHEMID") String CHEMID,@Param("CIC_IS_LOCK") String CIC_IS_LOCK); //新增综合判定结果 产销 //public void updateJudgeRESULT(@Param("material_no")String material_no,@Param("utm_id")String utm_id,@Param("judgeresult")String judgeresult,@Param("judgeresultid")String judgeresultid); //新增综合判定结果 卷板 //public void updateJudgeRESULT1(@Param("material_no")String material_no,@Param("utm_id")String utm_id,@Param("judgeresult")String judgeresult,@Param("judgeresultid")String judgeresultid); //修复修改表面判定结果 产销 //public void uptdateSurfaceResult2(@Param("material_no")String material_no); //修复修改表面判定结果 卷板 //public void uptdateSurfaceResult3(@Param("material_no")String material_no); //表面判定结果 //public void UpdateSurfaceResults(@Param("material_no") String material_no,@Param("is_sfu_lock") String is_sfu_lock,@Param("is_sfd_lock") String is_sfd_lock,@Param("is_sfe_lock") String is_sfe_lock,@Param("is_pass") String is_pass,@Param("sid") String sid); //表面判定结果 //public void UpdateSurfaceResultss(@Param("material_no") String material_no,@Param("is_sfu_lock") String is_sfu_lock,@Param("is_sfd_lock") String is_sfd_lock,@Param("is_sfe_lock") String is_sfe_lock,@Param("is_pass") String is_pass,@Param("sid") String sid); //修复修改表面判定结果 @Update("update qcm_judge_surface set SFE_IS_LOCK = '0',SFD_IS_LOCK = '0',SFE_IS_LOCK = '0',SF_RESULT_CODE = '1', SF_RESULT_DESC = '合格'" + " WRK_ORD = #{userorder}, WRK_GRP = #{usergroup}, JUDGE_NAME = #{username},judge_time = sysdate where surface_id = #{surface_id}") public void updatesurface(@Param("surface_id")String surface_id,@Param("username") String username,@Param("userorder") String userorder,@Param("usergroup")String usergroup); public void UpdateJugeResultJ(@Param("q") QcmJudgeCoilResult q); public void UpdateJugeResultY(@Param("q") QcmJudgeYtCoilResult q); public void UpdateJugeResultB(@Param("q") ZjResultAll q); public void UpdateJugeResultB1(@Param("q") ZjResultAll q); @Select("select to_char(sysdate-1,'yyyy-mm-dd') from dual") public String GetStart(); @Select("select to_char(sysdate,'yyyy-mm-dd') from dual") public String GetEnd(); @Select("select memo from qcm_ord_design_memo a where a.design_key = #{ORDERNO}") public String GetLGMemo(@Param("ORDERNO")String ORDERNO); @Select("select operate_name from (select a.operate_name from qcm_judge_operate_log a where a.params = #{BILLETNO} and a.prod_line = '4001LGX'" + " and a.Operate_Type = '发送备注' order by a.operate_time desc) where rownum = 1") public String GetLGMemoName(@Param("BILLETNO")String BILLETNO); @Select("${sql}") public HashMap queryOne(@Param("sql") String sql); //更新委托编号 @Update("update QCM_JHY_SAMPLE_CONSIGN_D set STATUS = '2',SEND_TIME=sysdate," + "CONSIGN_NO=#{consignNo}, CONSIGN_NO_SEQ = ${consignNoSeq}, SEND_ID = #{sendId},SEND_NAME = #{sendName} where SPECIMEN_NO = #{specimenNo}") public void updateConsignNo(@Param("consignNo")String consignNo,@Param("consignNoSeq") Integer consignNoSeq, @Param("sendId") String sendId,@Param("sendName")String sendName,@Param("specimenNo")String specimenNo); public void insertSampleM(@Param("q")JhySampleMMdoel q); public void insertSampleD(@Param("q")JhySampleDModel q); public void insertSampleItem(@Param("q")JhySampleDItemModel q); public void insertSampleOrd(@Param("q")JhySampleOrdMdoel q); public void insertQcmJudgeLog(@Param("q") QcmJudgeLog q); public void insertQcmJudgeOperateLog(@Param("q") QcmJudgeOperateLog q); @Select("${sql}") public List queryString(@Param("sql") String sql); //更新委托编号 @Update("${sql}") public void updateJudgeStatus(@Param("sql")String sql); //更新热轧备注 // @Update("update tbh02_coil_comm t set t.CJ_REMARK =#{CJ_REMARK},t.CPCJ_REMARK =#{CPCJ_REMARK},t.SPM_RMK = #{SPM_RMK}, " // + "t.DEAL_REMARK =#{DEAL_REMARK} where t.OLD_SAMPL_NO = #{OLD_SAMPL_NO}") // public void upRZRemarks(@Param("CJ_REMARK")String CJ_REMARK,@Param("CPCJ_REMARK")String CPCJ_REMARK,@Param("SPM_RMK")String SPM_RMK, // @Param("DEAL_REMARK")String DEAL_REMARK,@Param("OLD_SAMPL_NO")String OLD_SAMPL_NO); @Update("${sql}") public int upRZRemarks(@Param("sql")String sql); @Insert("${sql}") public int Inxiumojilv(@Param("sql")String sql); @Select("select SMP_NO,SPECIMEN_NO,OLD_CONSIGN_NO from qcm_jhy_sample_consign_d where quote_consign_no = #{quote_consign_no} ") public List findQuoteConsignD(@Param("quote_consign_no") String quote_consign_no); //QcmJhyPhyresult public void insertPhyResults(@Param("q")QCM.JHY01.VO.QcmJhyPhyresult q); public void insertExcelWGJMain(@Param("list") ExcelWGJMain list); public void insertExcelWGJCF(@Param("list") ExcelWGJCF list); public void insertExcelWGJCXN(@Param("list") ExcelWGJCXN list); public void insertExcelWGJMain(@Param("list") List list); public void insertExcelWGJCF(@Param("list") List list); public void insertExcelWGJCF1(@Param("list") List list);//20230628外购卷成分需插入tbb02_work_ingr表 public void insertExcelWGJCXN(@Param("list") List list); @Select("select count(*) from C_TBK02_COIL_COMM where coil_no=#{coil_no} ") public int haswgjcoil_no(@Param("coil_no") String coil_no); /*@Select("select count(*) from QCM_JHY_INSP_ELEMENTS where CHARGE_NO=#{charge_no} ") public int haswgjcf(@Param("charge_no") String charge_no);*/ @Select("SELECT NVL(MAX(TO_NUMBER(SUBSTR(T.COIL_NO, 4, 6))), 0) V_MAX_COIL_NO\n" + " FROM C_TBK02_COIL_COMM T\n" + //正式测试的时候切换这个表C_TBK02_COIL_COMM " WHERE T.COIL_NO LIKE\n" + " 'R' || (select substr(to_char(sysdate, 'yy'), 0, 2) from dual) || '%'\n" + " AND SUBSTR(T.OLD_SAMPL_NO, 10, 1) = 'W'") public String queryMaxXuHao(); //QcmJhyPhyresult @Select("${sql}") public List queryQcmJhyPhyresult1(@Param("sql") String sql); // @Delete("${sql}") public void delete(@Param("sql")String sql); @Insert("${sql}") public void insert(@Param("sql")String sql); @Update("${sql}") public void UpdateQcmWt(@Param("sql")String sql); public void insertQcmJhyElementsEditLog(@Param("q") QcmJhyElementsEditLog q); public void updateQcmJhyInspElements(@Param("q") QcmJhyElementsEditLog q); //锁定材质 public void LockP(@Param("material_no")String material_no); @Select("select * from QCM_BASE_DEFECT where validflag = '1' and defect_code = #{defect_code}") public List finddefecttype(@Param("defect_code")String defect_code); //更新ZJ表 public void zjResultAllZHB(@Param("material_no")String material_no,@Param("inspection_lot")String inspection_lot); @Update(" update QCM_JUDGE_COIL_RESULT t set t.abatchlog = #{type} where t.COIL_NO= #{material_no}") public void updateCoilResult(@Param("material_no")String material_no,@Param("type") String type); }