SELECT a.longbarcode ,e.materialname 条码品牌,b.logisticsid 物流号,b.inshiftid 班次,b.boxid 码垛次序  ,

 a.fxjid 封箱机号,a.laneno 分拣道号,a.inserttime 一号工程码打码时间 ,c.createtime 物流建立时间,b.producetime 码垛时间,c.intime 上架时间,

c.nowposition 位置,c.lane 排,c.bay 列,c.floor 层,a.orderstatus

FROM

 (SELECT longbarcode,inserttime,fxjid,laneno,orderstatus FROM T_ARTICALINPICK_INTF 

where 1=1 

${if(len(barcode)> 0,"and  longbarcode ='"+barcode+"'",

  if((len(fxjid)==0) && (len(brandcode)==0),

       "",

       if(len(fxjid)==0 ,

         "and substr(longbarcode,0,16)='"+brandcode+"'",

         if(len(brandcode)==0,

          "and fxjid='"+fxjid+"'",

          "and substr(longbarcode,0,16)='"+brandcode+"'"+"and fxjid='"+fxjid+"'" )

        )

    )+"and to_char(inserttime,"+"'"+"yyyy-mm-dd hh24:mi:ss"+"')"+" between "+"'"+starttime +"'"+" and"+ "'"+endtime+"'"

  )

}

 )  a

LEFT JOIN

( select *  from (

 SELECT barcode,logisticsid,boxid,inshiftid,producetime FROM T_UNIT

 UNION ALL

 SELECT barcode,logisticsid,boxid, inshiftid,producetime FROM T_UNITHISTORY)

 where 1=1 ${if(len(barcode)> 0,"and  barcode ='"+barcode+"'",

"and to_char(producetime,"+"'"+"yyyy-mm-dd hh24:mi:ss"+"')"+" between "+"'"+starttime +"'"+" and"+ "'"+endtime+"'")

         }

) b ON b.barcode=a.longbarcode

LEFT JOIN

( select * from (

SELECT logisticsid,materialid, createtime,intime,nowposition,lane,bay,floor  FROM T_LOGISTICS

UNION ALL

SELECT logisticsid,materialid, createtime,intime,nowposition,lane,bay,floor FROM T_LOGISTICSHISTORY)

 where 1=1 ${if(len(barcode)> 0,"",

 "and to_char(createtime,"+"'"+"yyyy-mm-dd hh24:mi:ss"+"')"+" between "+"'"+starttime +"'"+" and"+ "'"+endtime+"'")

                 }

) c ON c.logisticsid=b.logisticsid 

LEFT JOIN

( SELECT materialname,barcode FROM c_material WHERE materialname LIKE'%)' ) e ON e.barcode=SUBSTR(a.longbarcode,1,16)

order by  a.inserttime desc