SELECT aa.dn_vbeln,

aa.customername,

aa.PERSON_NAME,

aa.CARNUM,

aa.DRIVER,

aa.SHDAT,

aa.MATNR,

aa.MAKTX,

aa.T_COUNT,

aa.I_COUNT,

aa.SY_COUNT,

aa.I_UNIT_NAME,

aa.DN_AMT,

aa.ST_AMT,

aa.SY_AMT,

aa.remark

FROM (SELECT A.VBELN as dn_vbeln,

A.NAME as customername,

A.PERSON_NAME,

D.car_name carnum,

D.DRIVER,

D.SHDAT,

B.MATNR,

B.MAKTX,

B.T_COUNT,

B.I_COUNT,

B.T_COUNT-I_COUNT as SY_COUNT,

B.I_UNIT_NAME,

B.T_COUNT*B.ZH_PRICE as DN_AMT,

B.I_COUNT*B.ZH_PRICE as ST_AMT,

(B.T_COUNT- I_COUNT) *B.ZH_PRICE as SY_AMT,'部分签收'remark

FROM dn_order A

JOIN dn_order_item B

ON A.VBELN=B.VBELN

AND A.DID=B.DID

AND B.T_COUNT!=B.I_COUNT

LEFT JOIN pc_orders C

ON A.VBELN= C.DN_VBELN AND A.DID =C.DID

LEFT JOIN pc_order D ON C.VBELN= D.VBELN AND C.DID =D.DID

WHERE1 = 1AND B.T_COUNT> 0

AND D.SHDAT>=#{dateStart}

AND D.SHDAT<=#{dateEnd}AND A.did=#{did}

and (UPPER (A.VBELN) like #{key} or

UPPER (A.KUNNR) like #{key} or

UPPER (A.NAME) like #{key} or

UPPER (A.PERSON_NAME) like #{key} or

UPPER (D.CARNUM) like #{key} or

UPPER (B.MATNR) like #{key} or

UPPER (B.MAKTX) like #{key}

)UNION ALL

SELECT A.VBELN dn_vbeln,

A.NAME as customername,

A.PERSON_NAME,

D.car_name carnum,

D.DRIVER,

D.SHDAT,

B.MATNR,

B.MAKTX,

B.T_COUNT,0I_COUNT,

I_COUNT SY_COUNT,

B.I_UNIT_NAME,

B.T_COUNT*B.ZH_PRICE DN_AMT,0ST_AMT,

I_COUNT*B.ZH_PRICE SY_AMT,'全部未收'REMARK

FROM dn_order A

JOIN dn_order_item B

ON A.VBELN=B.VBELN

AND A.DID=B.DID

AND B.T_COUNT=B.I_COUNT

AND A.STATUS= '20'LEFT JOIN pc_orders C

ON A.VBELN= C.DN_VBELN AND A.DID =C.DID

LEFT JOIN pc_order D ON C.VBELN= D.VBELN AND C.DID =D.DID

WHERE1 = 1AND B.T_COUNT> 0

AND D.SHDAT>=#{dateStart}

AND D.SHDAT<=#{dateEnd}AND A.did=#{did}

and (UPPER (A.VBELN) like #{key} or

UPPER (A.KUNNR) like #{key} or

UPPER (A.NAME) like #{key} or

UPPER (A.PERSON_NAME) like #{key} or

UPPER (D.CARNUM) like #{key} or

UPPER (B.MATNR) like #{key} or

UPPER (B.MAKTX) like #{key}

)) aa

ORDER BY aa.REMARK, aa.SHDAT DESC