验证中...
Languages: SQL
Categories: 数据库相关
Latest update 2019-05-16 18:38
订单箱型汇总
Raw Copy
delimiter $$
use `seawork`$$
drop function if exists `f_getfeebytype`$$
create definer=`seawork`@`%` function `f_getfeebytype`(v_orderid varchar(50)) returns decimal(18,2)
begin
declare bl_amount decimal(18,2) default 0;
declare container_num int default 0;
-- 查询当时汇率
declare v_ratevalue decimal(12,4) default 0;
select ratevalue into v_ratevalue from te_cw_exchangerate a, te_orders b
where a.year=year(b.etd) and a.`month` =month(b.etd) and a.flag='1' and b.orderid = v_orderid;
if (v_ratevalue is null ) then set v_ratevalue=6.4; end if;
-- cny_pay 人民币应付 v_salepaycny
insert into ts_staffteureport_extend1
select v_orderid, pre.*, pre.grouptotal/pre.amount_bl as avg_bl
from (
select a.`UnitID`, ubt.typename as tpname,
(case when a.`UnitID` = '136104' then (select sum(Amount) from te_OrderContainer x where x.OrderID = v_orderid)
else cbt.amount end) as amount_bl,
sum(case when a.`CurrencyID` = '133102' then a.`Price`*a.`Amount`
when a.`CurrencyID` = '133101' then a.`Price`*a.`Amount`*v_ratevalue end
) as grouptotal,
sum(case when a.`CurrencyID` = '133102' then a.`Price`*a.`Amount`
when a.`CurrencyID` = '133101' then a.`Price`*a.`Amount`*v_ratevalue end
)/cbt.amount as avg_group
from `te_orderfee` a
inner join seawork.`td_basetype` as ubt on ubt.`BaseTypeID` = a.`UnitID`
left outer join (select x.OrderID, c.typename, Amount
from te_OrderContainer x inner join td_BaseType c on x.ContainerTypeID=c.basetypeid
where x.OrderID = v_orderid
group by c.typename) as cbt on cbt.typename = ubt.`TypeName`
where a.`Flag` = 1
and a.`PayTypeID` in ('pay','PAY')
and feetypeid not in ('138170','138171','201138')
and a.orderid=v_orderid
and feetypeid not in
(select b.`LimitType` from ts_staffteureport_step1 a ,sys_control b where b.codename='negative_carrlist' and a.carrierid=b.codekey
and a.orderid=v_orderid and etd>='2216-06-01'
)
group by a.orderid, a.`UnitID`
) as pre;
-- update ts_staffteureport_extend1 set avg_group = bl_amount/container_num where orderid = v_orderid
-- and UnitID = '136104' and PayOrRecv = 'PAY';
-- 人民币应付 财务底 v_costpaycny
insert into ts_staffteureport_extend1
select v_orderid, 'costpay', pre.*, pre.grouptotal/pre.amount_bl as avg_bl
from (
select a.`UnitID`, ubt.typename as tpname,
sum(case when a.`CurrencyID` = '133102' then a.`Price`*a.`Amount`
when a.`CurrencyID` = '133101' then a.`Price`*a.`Amount`*v_ratevalue end
) as grouptotal,
(case when a.`UnitID` = '136104' then (select sum(Amount) from te_OrderContainer x where x.OrderID = v_orderid)
else cbt.amount end) as amount_bl,
sum(case when a.`CurrencyID` = '133102' then a.`Price`*a.`Amount`
when a.`CurrencyID` = '133101' then a.`Price`*a.`Amount`*v_ratevalue end
)/cbt.amount as avg_group
from `te_orderfee` a
inner join seawork.`td_basetype` as ubt on ubt.`BaseTypeID` = a.`UnitID`
left outer join (select x.OrderID, c.typename, Amount
from te_OrderContainer x inner join td_BaseType c on x.ContainerTypeID=c.basetypeid
where x.OrderID = v_orderid
group by c.typename) as cbt on cbt.typename = ubt.`TypeName`
where a.`Flag` = 1
and a.`PayTypeID` in ('pay','PAY')
and a.orderid=v_orderid
and feetypeid not in
(select b.`LimitType` from ts_staffteureport_step1 a ,sys_control b where b.codename='negative_carrlist' and a.carrierid=b.codekey
and a.orderid=v_orderid and etd>='2216-06-01'
)
group by a.orderid, a.`UnitID`
) as pre;
-- update ts_staffteureport_extend1 set avg_group = bl_amount/container_num where orderid = v_orderid
-- and UnitID = '136104' and PayOrRecv = 'PAY';
-- recvcny 人民币应收
insert into ts_staffteureport_extend1
select v_orderid, 'RECEIVE', pre.*, pre.grouptotal/pre.amount_bl as avg_bl
from (
select a.`UnitID`, ubt.typename as tpname,
sum(case when a.`CurrencyID` = '133102' then a.`Price`*a.`Amount`
when a.`CurrencyID` = '133101' then a.`Price`*a.`Amount`*v_ratevalue end
) as grouptotal,
(case when a.`UnitID` = '136104' then (select sum(Amount) from te_OrderContainer x where x.OrderID = v_orderid)
else cbt.amount end) as amount_bl,
sum(case when a.`CurrencyID` = '133102' then a.`Price`*a.`Amount`
when a.`CurrencyID` = '133101' then a.`Price`*a.`Amount`*v_ratevalue end
)/cbt.amount as avg_group
from `te_orderfee` a
inner join seawork.`td_basetype` as ubt on ubt.`BaseTypeID` = a.`UnitID`
left outer join (select x.OrderID, c.typename, Amount
from te_OrderContainer x inner join td_BaseType c on x.ContainerTypeID=c.basetypeid
where x.OrderID = v_orderid
group by c.typename) as cbt on cbt.typename = ubt.`TypeName`
where a.`Flag` = 1
and a.`PayTypeID` in ('receive','RECEIVE') and a.OrderFeeStatusID not in ('188001','188002')
and a.orderid=v_orderid
group by a.orderid, a.`UnitID`
) as pre;
-- update ts_staffteureport_extend1 set avg_group = bl_amount/container_num where orderid = v_orderid
-- and UnitID = '136104' and PayOrRecv = 'RECEIVE';
update ts_staffteureport_extend1 set avg_group = avg_bl where orderid = v_orderid and UnitID = '136104';
update ts_staffteureport_extend1 set avg_bl = 0 where orderid = v_orderid and UnitID = '136104';
update ts_staffteureport_extend1 as a inner join ts_staffteureport_extend1 as b
on b.orderid = v_orderid and b.UnitID = '136104' and b.PayOrRecv = a.PayOrRecv
set a.avg_bl = a.avg_bl + b.avg_group
where a.orderid = v_orderid and a.UnitID != '136104';
return v_ratevalue;
end$$
delimiter ;

Comment list( 0 )

You need to Sign in for post a comment

Help Search