"select IF(trim(subptou1.invono0)<>'',concat('#',right(trim(subptou1.invono0),6)),trim(subptou1.invono0)),
CASE
WHEN (
color LIKE '%返%' OR
color LIKE '%退%' OR
color LIKE '%废%' OR
color LIKE '%原退%' OR
color LIKE '%退回%' OR
color LIKE '%原坯%' OR
subptou2.mem LIKE '%返%' OR
subptou2.mem LIKE '%退%' OR
subptou2.mem LIKE '%废%' OR
subptou2.mem LIKE '%原退%' OR
subptou2.mem LIKE '%退回%' OR
subptou2.mem LIKE '%原坯%'
) THEN CONCAT('分开$',model)
ELSE
CASE
WHEN EXISTS (
SELECT 1 FROM subptin2 t2
inner join subptin2 t2 on t2.invono=t1.invono
inner join subptou2 t3 on t3.mid=t2.id
left join subvendor1 on subvendor1.vendorid=t1.vendorid
where t1.vendorid=subptou1.vendorid and t3.model=subptou2.model and t3.color=subptou2.color and t2.id=subptou2.mid
AND t2.mem REGEXP '返|退|废|原退|退回|退回|原坯'
)
THEN CONCAT('分开$',model)
ELSE model
END
END AS model1,
IF(trim(subptou2.sort)<>'',subptou2.sort,'挂镀'),color,IF(trim(platsort)<>'',platsort,'压铸'),subptou1.date,subptou2.invono,concat(trim(subptou1.vendorid),replace(replace(replace(replace(replace(replace(replace(replace(vendor,char(9),''),char(10),''),char(13),''),char(40),''),char(41),''),'(',''),')',''),char(32),'')) as hebing1,'收货' csort,replace(trim(subptou2.mem),'返','') as subptou3,'→ ',subptou2.qty,subptou2.weight,'0'+0 as qty3,'0'+0 as qweight3,'0'+0 as unitpri3,'0'+0 as amt3,subptou1.vendorid,vendor,createid,confirmaid,size,subptou1.prncount,subptou1.createdt,subptou2.invono as invono1,'空的日期',subptou2.qty as qty1,subptou2.weight as weight1
from subptou1
inner join subptou2 on subptou2.invono=subptou1.invono
left join subvendor1 on subvendor1.vendorid=subptou1.vendorid
where 1=1
having (hebing1 like '%"+alltrim(acond[1,1])+"%'"+if(len(alltrim(acond[1,2]))==0,""," or hebing1 like '%"+alltrim(acond[1,2])+"%'")+")
and
(case
when '"+alltrim(acond[3,1])+"' = '2'
then date_format(subptou1.date, '%Y %m') = date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y %m')
when '"+alltrim(acond[3,1])+"' = '3'
then date_format(subptou1.date, '%Y %m') = date_format(DATE_SUB(curdate(), INTERVAL 2 MONTH),'%Y %m')
when (LENGTH('"+alltrim(acond[3,1])+"')=10)
then subptou1.date>='"+acond[3,1]+"' and subptou1.date<='"+acond[3,2]+"'
else
subptou1.date>=DATE_ADD(curdate(),interval -day(curdate())+1 day)
and date_format(subptou1.date,'%Y-%m-%d')<=date_format(current_date()-('"+alltrim(acond[3,2])+"'),'%Y-%m-%d')
end)
union all
select IF(trim(subptou1.invono0)<>'',concat('#',right(trim(subptou1.invono0),6)),trim(subptou1.invono0)) as invono2,
CONCAT(
CASE
WHEN subptou2.color LIKE '%返%' OR
subptou2.color LIKE '%退%' OR
subptou2.color LIKE '%废%' OR
subptou2.color LIKE '%原退%' OR
subptou2.color LIKE '%退回%' OR
subptou2.color LIKE '%原坯%' OR
subptin2.mem LIKE '%返%' OR
subptin2.mem LIKE '%退%' OR
subptin2.mem LIKE '%废%' OR
subptin2.mem LIKE '%原退%' OR
subptin2.mem LIKE '%退回%' OR
subptin2.mem LIKE '%原坯%'
THEN CONCAT('分开$',subptou2.model)
ELSE subptou2.model
END
) AS model2,/***送货分离***/
IF(trim(subptou2.sort)<>'',subptou2.sort,'挂镀'),subptou2.color,IF(trim(subptou2.platsort)<>'',subptou2.platsort,'压铸'),subptin1.date,subptin1.invono,concat(trim(subptin1.vendorid),replace(replace(replace(replace(replace(replace(replace(replace(vendor,char(9),''),char(10),''),char(13),''),char(40),''),char(41),''),'(',''),')',''),char(32),'')) as hebing2,'送货' csort2,replace(trim(subptin2.mem),'返','') as subptin3,subptin2.add001,'0'+0 as qty4,'0'+0 as qweight4,subptin2.qty,subptin2.weight,subptin2.unitpri,round(subptin2.qty*subptin2.unitpri,2) amt,subptin1.vendorid,vendor,subptin1.createid,subptin1.confirmaid,subptou2.size,subptin1.prncount,subptin1.prndatetime,subptou2.invono as invono3,subptou1.date as indate,subptin2.qty as qty2,subptin2.weight as weight2
from subptin1
inner join subptin2 on subptin2.invono=subptin1.invono
inner join subptou2 on subptou2.mid=subptin2.id
inner join subptou1 on subptou1.invono=subptou2.invono
left join subvendor1 on subvendor1.vendorid=subptin1.vendorid
where 1=1
having (hebing2 like '%"+alltrim(acond[2,1])+"%'"+if(len(alltrim(acond[2,2]))==0,""," or hebing2 like '%"+alltrim(acond[2,2])+"%'")+")
and
(case
when '"+alltrim(acond[3,1])+"' = '2'
then subptin1.date>=DATE_ADD(LAST_DAY(DATE_SUB(NOW(),INTERVAL 2 MONTH)),INTERVAL 1 DAY) and subptin1.date<='2099-12-31'
and date_format(indate, '%Y %m') = date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y %m')
when '"+alltrim(acond[3,1])+"' = '3'
then subptin1.date>=DATE_ADD(LAST_DAY(DATE_SUB(NOW(),INTERVAL 3 MONTH)),INTERVAL 1 DAY)
and subptin1.date<='2999-12-31'
and indate between DATE_ADD(LAST_DAY(DATE_SUB(NOW(),INTERVAL 3 MONTH)),INTERVAL 1 DAY) and LAST_DAY(DATE_SUB(NOW(),INTERVAL 2 MONTH))
when (LENGTH('"+alltrim(acond[3,1])+"')=10)
then subptin1.date>='"+acond[3,1]+"' and subptin1.date<='2999-12-31' and indate between '"+alltrim(acond[3,1])+"' and '"+alltrim(acond[3,2])+"'
else
subptin1.date>=DATE_ADD(curdate(),interval -day(curdate())+1 day)
and subptin1.date<='2099-12-31'
and indate>=DATE_ADD(curdate(),interval -day(curdate())+1 day)
and date_format(indate,'%Y-%m-%d')<=date_format(current_date()-('"+alltrim(acond[3,2])+"'),'%Y-%m-%d')
end)
ORDER BY hebing1 ASC,model1 ASC,color ASC,invono1 ASC,csort ASC,date ASC,invono ASC"
错误在第2个CASE
最新发布