<resultMap id="approveRep" type="com.intertek.srpboot.entity.Vo.Response">
<collection property="Model" ofType="com.intertek.srpboot.entity.Vo.Model">
<result column="fid" property="FID"/>
<result column="F_DH_SourceNO" property="F_DH_SourceNO"/>
<result column="F_ABCD_State" property="F_ABCD_State"/>
<result column="FDate" property="FDate"/>
<result column="FDate" property="FCreateDate"/>
<collection property="FPOOrderFinance" ofType="com.intertek.srpboot.entity.Vo.FPOOrderFinance">
<!-- <result column="FSettleCurrId" property="FExchangeRate"/>-->
<collection property="FSettleCurrId" ofType="com.intertek.srpboot.entity.Vo.FSettleCurrId">
<result column="FSettleCurrId" property="FNumber"/>
</collection>
</collection>
<collection property="FPurchaseOrgId" ofType="com.intertek.srpboot.entity.Vo.FPurchaseOrgId">
<result column="FPurchaseOrgId" property="FNumber"/>
</collection>
<collection property="FPurchaseDeptId" ofType="com.intertek.srpboot.entity.Vo.FPurchaseDeptId">
<result column="FPurchaseDeptId" property="FNumber"/>
</collection>
<collection property="FCreatorId" ofType="com.intertek.srpboot.entity.Vo.FCreatorId">
<result column="FCreatorId" property="FUserId"/>
</collection>
<collection property="F_ABCD_CPX" ofType="com.intertek.srpboot.entity.Vo.F_ABCD_CPX">
<result column="F_ABCD_CPX" property="FNUMBER"/>
</collection>
<collection property="FSupplierId" ofType="com.intertek.srpboot.entity.Vo.FSupplierId">
<result column="FSupplierId" property="FNumber"/>
</collection>
<collection property="FPOOrderEntry" ofType="com.intertek.srpboot.entity.Vo.FPOOrderEntry">
<!-- <result column="F_ABCD_JIN" property="F_ABCD_JIN"/>-->
<result column="F_ABCD_JN" property="F_ABCD_JN"/>
<result column="F_ABCD_JIA" property="F_ABCD_JIA"/>
<result column="F_ABCD_JS" property="F_ABCD_JS"/>
<result column="F_ABCD_SN" property="F_ABCD_SN"/>
<result column="F_ABCD_FBLX" property="F_ABCD_FBLX"/>
<result column="F_ABCD_ZY" property="F_ABCD_ZY"/>
<result column="FPrice" property="FPrice"/>
<result column="F_ABCD_SF" property="F_ABCD_SF"/>
<!-- <collection column="F_ABCD_JIN" property="F_ABCD_JINlist" select="getF_ABCD_JIN" ofType="string"/>-->
<!-- 20240319 发票改为incomedate-->
<result column="F_ABCD_JIN" property="F_ABCD_JIN"/>
<collection property="FMaterialId" ofType="com.intertek.srpboot.entity.Vo.FMaterialId">
<result column="FMaterialId" property="FNumber"/>
</collection>
</collection>
</collection>
</resultMap>
<select id="selectRep" resultMap="approveRep">
SELECT
s.fid as fid,
-- 来源单号,分包单号,Synetls定义,SubcontracNo:GZ2003012701
s.no as F_DH_SourceNO,
-- 用Subcontract的office信息关联映射表取得公司code。(附表D列)
-- j.OFFICE as FPurchaseOrgId,
t1.company_code as FPurchaseOrgId,
-- Register Invoice User(Subcontract.Applicant_NO)关联User表取得HR_No
-- s.APPLICANT_NO as FCreatorId,
t4.hr_no as FCreatorId,
-- 用Subcontract的Invoice Team信息关联映射表取得部门Code。(附表F列)
-- j.INVOICE_TEAM as FPurchaseDeptId,
t2.department_code as FPurchaseDeptId,
-- 用Subcontract的Invoice Team信息关联映射表取得产品线信息。(附表E列)
-- j.INVOICE_TEAM as F_ABCD_CPX,
t2.product_line_T1Code as F_ABCD_CPX,
-- 等分包商基础信息同步过来 notice
-- s.subcontractor_no as FSUPPLIERID,
st.subcontractor_code as FSUPPLIERID,
-- Job No关联的Invoice line和Invoice表里取得已开票的税票号(Tax_invoice_no),若有多个用逗号,隔开。
-- 20240312 取消字段
-- 20240319 增加字段为incomedate
-- j.job_no as F_ABCD_JIN,
t5.F_ABCD_JIN,
j.job_no as F_ABCD_JN,
-- jobinamount
j.Service_Amount*j.Service_Exch_rate as F_ABCD_JIA,
-- jobstatus
j.status as F_ABCD_JS,
s.no as F_ABCD_SN,
s.category as F_ABCD_FBLX,
-- notice
-- s.test_item_id as FMaterialId,
t3.test_item_code as FMaterialId,
s.MARK as F_ABCD_ZY,
s.TEST_FEE_AMOUNT as FPrice,
s.TEST_FEE_AMOUNT as F_ABCD_SF,
s.TEST_FEE_CURRENCY_NAME as FSettleCurrId,
-- s.TEST_FEE_CURRENCY_NAME as FExchangeRate,
s.status as F_ABCD_State,
convert(VARCHAR,GETDATE(),120) as FDate
FROM SUBCONTRACT s
left join JOB j on s.JOB_NO=j.JOB_NO
left join (select CONVERT(varchar(10), MAX(i2.APPROVE_DATE), 111) as F_ABCD_JIN,i1.INVOICE_JOB_NO
from INVOICE_LINE i1
LEFT JOIN INVOICE i2 on i1.INVOICE_NO = i2.INVOICE_NO and i1.AMOUNT !=0 GROUP BY i1.INVOICE_JOB_NO) t5 on t5.INVOICE_JOB_NO = j.job_No
-- left join invoice_line il on j.JOB_NO=il.INVOICE_JOB_NO
-- left join invoice i on i.invoice_no=il.invoice_no and il.AMOUNT!=0
LEFT JOIN SUBCONTRACTOR st on s.SUBCONTRACTOR_NO=st.no
left join (SELECT DISTINCT testing_office_synetls,company_code from sc_map) t1 on t1.testing_office_synetls=j.office
left join sc_map t2 on t2.invoice_team_synetls= j.INVOICE_TEAM
LEFT JOIN test_item t3 on s.test_item_id=t3.id
left join user_entity t4 on s.APPLICANT_NO=t4.user_no
where s.status = 2 and s.Category = 'Certification' and s.test_fee_amount!=0 and s.no not like 'I%'
and j.service_amount!=0
and st.subcontractor_code is not null and s.no='GZ2022011819'
</select>
result和collection不要弄混,会导致Cause: java.lang.NullPointerException异常。
正常来说mybatis对查回来的null值不会做什么判断处理,不会报这个空指针错误。
上面的也可以写成子查询,但是子查询非常非常慢。
<select id="getFPurchaseOrgId" resultType="com.intertek.its.enity.FPurchaseOrgId">
select DISTINCT company_code as FNumber
from sc_map
where testing_office_synetls = #{FPurchaseOrgId}
</select>
<select id="getFPurchaseDeptId" resultType="com.intertek.its.enity.FPurchaseDeptId">
SELECT department_code as FNumber
from sc_map
where invoice_team_synetls = #{FPurchaseDeptId}
</select>
<select id="getF_ABCD_CPX" resultType="com.intertek.its.enity.F_ABCD_CPX">
SELECT product_line_T1Code as FNUMBER
from sc_map
where invoice_team_synetls = #{F_ABCD_CPX}
</select>
<select id="getF_ABCD_JIN" resultType="string">
-- 20240319 改为取incomedate job和invoice是多对多的关系
SELECT
CONVERT(varchar(10), MAX(i2.APPROVE_DATE), 111) as JIDate
from INVOICE_LINE i1
LEFT JOIN INVOICE i2 on i1.INVOICE_NO = i2.INVOICE_NO and i1.AMOUNT !=0
where i1.INVOICE_JOB_NO = #{F_ABCD_JIN}
</select>
<select id="getFSUPPLIERID" resultType="com.intertek.its.enity.FSupplierId">
SELECT subcontractor_code as FNumber
FROM SUBCONTRACTOR
where NO = #{FSupplierId}
</select>
<select id="getFMaterialId" resultType="com.intertek.its.enity.FMaterialId">
select test_item_code as FNumber
from test_item
where id = #{FMaterialId}
</select>
<select id="getFCreatorId" resultType="com.intertek.its.enity.FCreatorId">
SELECT HR_NO as FUserId
FROM USER_ENTITY
WHERE USER_NO = #{FCreatorId}
</select>
<!-- <select id="getFSettleCurrId" resultType="com.intertek.its.enity.FSettleCurrId">
select from
</select>-->
<resultMap id="approveRep" type="com.intertek.its.enity.Response">
<collection property="Model" ofType="com.intertek.its.enity.Model">
<result column="fid" property="FID"/>
<result column="F_DH_SourceNO" property="F_DH_SourceNO"/>
<result column="F_ABCD_State" property="F_ABCD_State"/>
<result column="FDate" property="FDate"/>
<result column="FDate" property="FCreateDate"/>
<collection property="FPOOrderFinance" ofType="com.intertek.its.enity.FPOOrderFinance">
<!-- <result column="FSettleCurrId" property="FExchangeRate"/>-->
<collection property="FSettleCurrId" ofType="com.intertek.its.enity.FSettleCurrId">
<result column="FSettleCurrId" property="FNumber"/>
</collection>
</collection>
<collection column="FPurchaseOrgId" property="FPurchaseOrgId" select="getFPurchaseOrgId"
ofType="com.intertek.its.enity.FPurchaseOrgId"/>
<collection column="FPurchaseDeptId" property="FPurchaseDeptId" select="getFPurchaseDeptId"
ofType="com.intertek.its.enity.FPurchaseDeptId"/>
<collection property="FCreatorId" column="FCreatorId" select="getFCreatorId"
ofType="com.intertek.its.enity.FCreatorId"/>
<collection column="F_ABCD_CPX" property="F_ABCD_CPX" select="getF_ABCD_CPX"
ofType="com.intertek.its.enity.F_ABCD_CPX"/>
<collection column="FSupplierId" property="FSupplierId" select="getFSUPPLIERID"
ofType="com.intertek.its.enity.FSupplierId"/>
<collection property="FPOOrderEntry" ofType="com.intertek.its.enity.FPOOrderEntry">
<!-- <result column="F_ABCD_JIN" property="F_ABCD_JIN"/>-->
<result column="F_ABCD_JN" property="F_ABCD_JN"/>
<result column="F_ABCD_JIA" property="F_ABCD_JIA"/>
<result column="F_ABCD_JS" property="F_ABCD_JS"/>
<result column="F_ABCD_SN" property="F_ABCD_SN"/>
<result column="F_ABCD_FBLX" property="F_ABCD_FBLX"/>
<result column="F_ABCD_ZY" property="F_ABCD_ZY"/>
<result column="FPrice" property="FPrice"/>
<result column="F_ABCD_SF" property="F_ABCD_SF"/>
<collection property="FMaterialId" column="FMaterialId" select="getFMaterialId"
ofType="com.intertek.its.enity.FMaterialId"/>
<!-- <collection column="F_ABCD_JIN" property="F_ABCD_JINlist" select="getF_ABCD_JIN" ofType="string"/>-->
<!-- 20240319 发票改为incomedate-->
<collection column="F_ABCD_JIN" property="F_ABCD_JIN" select="getF_ABCD_JIN" ofType="string"/>
</collection>
</collection>
</resultMap>
<select id="selectRep" resultMap="approveRep">
SELECT
s.fid as fid,
-- 来源单号,分包单号,Synetls定义,SubcontracNo:GZ2003012701
s.no as F_DH_SourceNO,
-- 用Subcontract的office信息关联映射表取得公司code。(附表D列)
j.OFFICE as FPurchaseOrgId,
-- Register Invoice User(Subcontract.Applicant_NO)关联User表取得HR_No
s.APPLICANT_NO as FCreatorId,
-- 用Subcontract的Invoice Team信息关联映射表取得部门Code。(附表F列)
j.INVOICE_TEAM as FPurchaseDeptId,
-- 用Subcontract的Invoice Team信息关联映射表取得产品线信息。(附表E列)
j.INVOICE_TEAM as F_ABCD_CPX,
-- 等分包商基础信息同步过来 notice
s.subcontractor_no as FSUPPLIERID,
-- Job No关联的Invoice line和Invoice表里取得已开票的税票号(Tax_invoice_no),若有多个用逗号,隔开。
-- 20240312 取消字段
-- 20240319 增加字段为incomedate
j.job_no as F_ABCD_JIN,
j.job_no as F_ABCD_JN,
-- jobinamount
j.Service_Amount*j.Service_Exch_rate as F_ABCD_JIA,
-- jobstatus
j.status as F_ABCD_JS,
s.no as F_ABCD_SN,
s.category as F_ABCD_FBLX,
-- notice
s.test_item_id as FMaterialId,
s.MARK as F_ABCD_ZY,
s.TEST_FEE_AMOUNT as FPrice,
s.TEST_FEE_AMOUNT as F_ABCD_SF,
s.TEST_FEE_CURRENCY_NAME as FSettleCurrId,
-- s.TEST_FEE_CURRENCY_NAME as FExchangeRate,
s.status as F_ABCD_State,
convert(VARCHAR,GETDATE(),120) as FDate
FROM SUBCONTRACT s left join JOB j on s.JOB_NO=j.JOB_NO
<!-- <if test="nos !=null and nos.size()>0">-->
<where>
s.status =#{status}
and s.no in
<foreach collection="nos" item="i" open="(" close=")" separator=",">
#{i}
</foreach>
</where>
<!-- </if>-->
</select>
建议还是连表查询,只需查一次。