select a.whid,a.WHCode,a.WHName,a.Logogram
#if (dbtype == "Microsoft SQL Server"){
,case when
case when a.iscontrol = 'Y'
then isnull(c.placenum,0) else isnull(d.basenum,0)
end - isnull(b.num,0) >= 0
then case when a.iscontrol = 'Y' and isnull(c.placenum,0) > 0 then '是'
when a.iscontrol = 'N' and isnull(d.basenum,0) > 0 then '是'
else '否' end
else '否' end as 是否配齐
#}else if (dbtype == "Oracle" ){
,case when
case when a.iscontrol = 'Y'
then nvl(c.placenum,0) else nvl(d.basenum,0)
end - nvl(b.num,0) >= 0
then case when a.iscontrol = 'Y' and nvl(c.placenum,0) > 0 then '是'
when a.iscontrol = 'N' and nvl(d.basenum,0) > 0 then '是'
else '否' end
else '否' end as 是否配齐
#}
from storehouse a
left join (
select sum(a.num) as num,a.whid,a.entid from ecorderdt a
where a.entid = :_entid
#__sql += " and a.billno in (" + billnoList + ")"
group by a.whid,a.entid
) b on a.whid = b.whid and a.entid=b.entid
left join(
select sum(a.placenum) as placenum,a.whid,a.entid from storbalance a
where a.entid = :_entid
and exists(select 1 from ecorderdt x
join ecordermt x3 on x.billno = x3.billno and x.entid = x3.entid
join ECGoodsDoc x1 on x.ecgoodsid = x1.ecgoodsid and x3.ecshopid = x1.ecshopid and x.entid = x1.entid
join ECGoodsRf x2 on x3.ecshopid = x2.ecshopid and x.skuid = x2.skuid
and x.ecgoodsid = x2.ecgoodsid and x.entid = x2.entid
where a.goodsid = x2.goodsid
#__sql += " and x.billno in (" + billnoList + ")"
)
group by a.whid,a.entid
) c on a.whid = c.whid and a.entid = c.entid
left join(
select a.whid,sum(a.basenum) as basenum from KK_ECWHBal a
where 1=1
and exists(select 1 from ecorderdt x
join ecordermt x3 on x.billno = x3.billno and x.entid = x3.entid
join ECGoodsDoc x1 on x.ecgoodsid = x1.ecgoodsid and x3.ecshopid = x1.ecshopid and x.entid = x1.entid
join ECGoodsRf x2 on x3.ecshopid = x2.ecshopid and x.skuid = x2.skuid
and x.ecgoodsid = x2.ecgoodsid and x.entid = x2.entid
join goodsdoc x4 on x2.goodsid = x4.goodsid and x2.entid = x4.entid
where a.goodscode = x4.goodscode
#__sql += " and x.billno in (" + billnoList + ")"
)
group by a.whid
) d on a.whid = d.whid
where a.entid= :_entid and a.beactive='Y'
order by a.WHCode
#if (dbtype == "Microsoft SQL Server"){
,case when
case when a.iscontrol = 'Y'
then isnull(c.placenum,0) else isnull(d.basenum,0)
end - isnull(b.num,0) >= 0
then case when a.iscontrol = 'Y' and isnull(c.placenum,0) > 0 then '是'
when a.iscontrol = 'N' and isnull(d.basenum,0) > 0 then '是'
else '否' end
else '否' end as 是否配齐
#}else if (dbtype == "Oracle" ){
,case when
case when a.iscontrol = 'Y'
then nvl(c.placenum,0) else nvl(d.basenum,0)
end - nvl(b.num,0) >= 0
then case when a.iscontrol = 'Y' and nvl(c.placenum,0) > 0 then '是'
when a.iscontrol = 'N' and nvl(d.basenum,0) > 0 then '是'
else '否' end
else '否' end as 是否配齐
#}
from storehouse a
left join (
select sum(a.num) as num,a.whid,a.entid from ecorderdt a
where a.entid = :_entid
#__sql += " and a.billno in (" + billnoList + ")"
group by a.whid,a.entid
) b on a.whid = b.whid and a.entid=b.entid
left join(
select sum(a.placenum) as placenum,a.whid,a.entid from storbalance a
where a.entid = :_entid
and exists(select 1 from ecorderdt x
join ecordermt x3 on x.billno = x3.billno and x.entid = x3.entid
join ECGoodsDoc x1 on x.ecgoodsid = x1.ecgoodsid and x3.ecshopid = x1.ecshopid and x.entid = x1.entid
join ECGoodsRf x2 on x3.ecshopid = x2.ecshopid and x.skuid = x2.skuid
and x.ecgoodsid = x2.ecgoodsid and x.entid = x2.entid
where a.goodsid = x2.goodsid
#__sql += " and x.billno in (" + billnoList + ")"
)
group by a.whid,a.entid
) c on a.whid = c.whid and a.entid = c.entid
left join(
select a.whid,sum(a.basenum) as basenum from KK_ECWHBal a
where 1=1
and exists(select 1 from ecorderdt x
join ecordermt x3 on x.billno = x3.billno and x.entid = x3.entid
join ECGoodsDoc x1 on x.ecgoodsid = x1.ecgoodsid and x3.ecshopid = x1.ecshopid and x.entid = x1.entid
join ECGoodsRf x2 on x3.ecshopid = x2.ecshopid and x.skuid = x2.skuid
and x.ecgoodsid = x2.ecgoodsid and x.entid = x2.entid
join goodsdoc x4 on x2.goodsid = x4.goodsid and x2.entid = x4.entid
where a.goodscode = x4.goodscode
#__sql += " and x.billno in (" + billnoList + ")"
)
group by a.whid
) d on a.whid = d.whid
where a.entid= :_entid and a.beactive='Y'
order by a.WHCode