CREATE DEFINER=`shuanglong`@`%` PROCEDURE `C30最新泵送价`(IN fdates date)
begin
DECLARE contractid BIGINT ;#合同id
create TEMPORARY table if not exists tmp_30
( htid bigint,
hth varchar(100),
jsdw bigint,
gcxm bigint,
dj decimal(10,2),#算出30最新的泵送价格
fdb decimal(10,2));#算出30最新的浮动比
insert into tmp_30(htid,hth,jsdw,gcxm) select id,code,settlement_company_id,engineering_project_id from b_salesdeliver_sales_contract where is_deleted=0;
begin
DECLARE fbsj DECIMAL (18,2) ;#泵送价，如汽车泵 15块;
DECLARE fcpje DECIMAL (18,2) ;#产品金额;
DECLARE fbsdj DECIMAL (18,2) ;#泵送单价;
DECLARE ffbdj DECIMAL (18,2) ;#非泵单价;
DECLARE fhtjlx bigint; # 合同价类型
DECLARE fzxfdb DECIMAL (10,2); # 最新浮动比
DECLARE fqjlx bigint; # 取价类型
DECLARE ffdje bigint; # 浮动金额
DECLARE fzxhtj DECIMAL (18,2); # 最新合同价格
DECLARE done bigint default 0;
DECLARE ID_Curs CURSOR FOR SELECT htid FROM tmp_30;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
if exists (select 1 from tmp_30)
then
set done=0;
end if ;
OPEN ID_Curs;
WHILE done=0 do
FETCH ID_Curs INTO contractid;
if done<>1 then
set fzxhtj=0;
set fbsj=(select ifnull(d.price,c.pumping_price) FROM b_salesdeliver_sales_contract b
INNER JOIN b_salesdeliver_sales_contract_pump_category c ON b.id=c.pid # AND a.pump_type=c.pump_category #解决非泵取价失败问题
LEFT JOIN b_salesdeliver_sales_contract_car_pump_meters d ON b.id=d.pid
WHERE b.id =contractid
and b.is_deleted=0 and c.is_deleted=0
ORDER BY ifnull(d.price,c.pumping_price) desc
limit 0,1);
set fqjlx=(select take_price_type from b_salesdeliver_sales_contract where id=contractid);
IF EXISTS #有调价函
(SELECT a.id FROM b_salesdeliver_contract_price_adjustment_letter a
INNER JOIN b_salesdeliver_contract_price_adjustment_letter_info b ON a.id=b.pid
WHERE a.contract_id=contractid AND b.varieties_id=100207 AND b.implement_start_time<=fdates and a.is_deleted=0
and a.status=9333 and ifnull(a.is_deleted,0)=0 /*and b.contract_price_type_id=157316*/ ORDER BY# b.implement_start_time DESC,
a.create_time,b.implement_start_time DESC limit 0,1)
then
set fzxhtj=(select IFNULL(b.after_adjustment_price,0)
FROM b_salesdeliver_contract_price_adjustment_letter a INNER JOIN b_salesdeliver_contract_price_adjustment_letter_info b ON a.id=b.pid
WHERE a.contract_id=contractid AND b.varieties_id=100207 AND b.implement_start_time<=fdates and a.status=9333 and a.is_deleted=0
#ORDER BY implement_start_time DESC,a.create_time DESC limit 0,1);
ORDER BY a.create_time DESC limit 0,1);
set fhtjlx=(select IFNULL(b.contract_price_type_id,0)
FROM b_salesdeliver_contract_price_adjustment_letter a INNER JOIN b_salesdeliver_contract_price_adjustment_letter_info b ON a.id=b.pid
WHERE a.contract_id=contractid AND b.varieties_id=100207 AND b.implement_start_time<=fdates and a.status=9333 and a.is_deleted=0
#ORDER BY implement_start_time DESC,a.create_time DESC limit 0,1);
ORDER BY a.create_time DESC limit 0,1);
set fzxfdb=(select IFNULL(b.after_adjustment_floating_ratio,0)
FROM b_salesdeliver_contract_price_adjustment_letter a INNER JOIN b_salesdeliver_contract_price_adjustment_letter_info b ON a.id=b.pid
WHERE a.contract_id=contractid AND b.varieties_id=100207 AND b.implement_start_time<=fdates and a.status=9333 and a.is_deleted=0
#ORDER BY implement_start_time DESC,a.create_time DESC limit 0,1);
ORDER BY a.create_time DESC limit 0,1);
set ffdje=(select IFNULL(b.after_adjustment_floating_price,0)
FROM b_salesdeliver_contract_price_adjustment_letter a INNER JOIN b_salesdeliver_contract_price_adjustment_letter_info b ON a.id=b.pid
WHERE a.contract_id=contractid AND b.varieties_id=100207 AND b.implement_start_time<=fdates and a.status=9333 and a.is_deleted=0
#ORDER BY implement_start_time DESC,a.create_time DESC limit 0,1);
ORDER BY a.create_time DESC limit 0,1);
if fhtjlx<>157316 then
if fqjlx in (9435,9437,9438) #取上月信息价
then
#取信息价最后一次的(暂估取最近一个月的，否则取上月的)
CREATE temporary TABLE if not exists PRICE_9435
(
select * from
(select ifnull(unit_price,0) zzprice1,(ifnull(ifnull(unit_price,0)-round(ifnull(unit_price,0)*(1-fzxfdb/100),5)+ffdje,0)) zzprice_zk1,
concat(a.year_num,'年',a.month_num,'月信息价') zzfnote1,b.pumping_mode BSLX1
FROM b_information_price_maintain a
INNER JOIN b_information_price_maintain_entity b ON a.id=b.pid
WHERE a.year_num*100+a.month_num <year(fdates)*100+month(fdates) AND b.variety_id=100207
AND a.information_price_type =fhtjlx and b.pumping_mode=9085 and a.is_deleted=0
ORDER BY a.year_num*100+a.month_num DESC limit 0,1) AA
union all
select * from
(select ifnull(unit_price,0) zzprice1,(ifnull(ifnull(unit_price,0)-round(ifnull(unit_price,0)*(1-fzxfdb/100),5)+ffdje,0)) zzprice_zk1,
concat(a.year_num,'年',a.month_num,'月信息价') zzfnote1,b.pumping_mode BSLX1
FROM b_information_price_maintain a
INNER JOIN b_information_price_maintain_entity b ON a.id=b.pid
WHERE a.year_num*100+a.month_num <year(fdates)*100+month(fdates) AND b.variety_id=100207
AND a.information_price_type =fhtjlx and b.pumping_mode=9086 and a.is_deleted=0
ORDER BY a.year_num*100+a.month_num DESC limit 0,1 ) BB);
SET fbsdj=(SELECT round(zzprice1-zzprice_zk1,2) FROM PRICE_9435 WHERE BSLX1=9085);
SET ffbdj=(SELECT round(zzprice1-zzprice_zk1,2) FROM PRICE_9435 WHERE BSLX1=9086);
update tmp_30 set fdb=ifnull(fzxfdb,0) where htid=contractid;
drop temporary table PRICE_9435;
END IF;
if fqjlx=9436 #取当月信息价
then
#取信息价最后一次的(暂估取最近一个月的，否则取当月的)
CREATE temporary TABLE if not exists PRICE_9436
(
SELECT * FROM (
select IFNULL(unit_price,0) zzprice1,ifnull(unit_price-round(unit_price*(1-fzxfdb/100),5)+ffdje,0) zzprice_zk1,
concat(a.year_num,'年',a.month_num,'月信息价') zzfnote1,b.pumping_mode BSLX1
FROM b_information_price_maintain a
INNER JOIN b_information_price_maintain_entity b ON a.id=b.pid
WHERE a.year_num*100+a.month_num <=year(fdates)*100+month(fdates) AND b.variety_id=100207
AND a.information_price_type =fhtjlx and b.pumping_mode=9085 and a.is_deleted=0
ORDER BY a.year_num*100+a.month_num DESC limit 0,1) AA
union all
SELECT * FROM (
select IFNULL(unit_price,0) zzprice1,ifnull(unit_price-round(unit_price*(1-fzxfdb/100),5)+ffdje,0) zzprice_zk1,
concat(a.year_num,'年',a.month_num,'月信息价') zzfnote1,b.pumping_mode BSLX1
FROM b_information_price_maintain a
INNER JOIN b_information_price_maintain_entity b ON a.id=b.pid
WHERE a.year_num*100+a.month_num <=year(fdates)*100+month(fdates) AND b.variety_id=100207
AND a.information_price_type =fhtjlx and b.pumping_mode=9086 and a.is_deleted=0
ORDER BY a.year_num*100+a.month_num DESC limit 0,1) BB);
SET fbsdj=(SELECT round(zzprice1-zzprice_zk1,2) FROM PRICE_9436 WHERE BSLX1=9085);
SET ffbdj=(SELECT round(zzprice1-zzprice_zk1,2) FROM PRICE_9436 WHERE BSLX1=9086);
update tmp_30 set fdb=ifnull(fzxfdb,0) where htid=contractid;
drop temporary table PRICE_9436;
END IF;
#非泵以泵送信息价固定扣减
IF EXISTS ( SELECT 1 FROM b_salesdeliver_sales_contract WHERE id =contractid and is_pump_decrease=1 and ifnull(is_unpump_increase=0,0) )
then
UPDATE tmp_30
SET dj=fbsdj
WHERE htid =contractid ;
END if ;
#泵送以非泵送信息价固定增加
IF EXISTS ( SELECT 1 FROM b_salesdeliver_sales_contract WHERE id =contractid and is_unpump_increase=1 and ifnull(is_pump_decrease,0)=0 )
then
UPDATE tmp_30
SET dj=round(ffbdj+ifnull(fbsj,0),2)
WHERE htid =contractid ;
END if;
IF EXISTS(SELECT 1 FROM b_salesdeliver_sales_contract WHERE id =contractid and ifnull(is_unpump_increase,0)=0 AND ifnull(is_pump_decrease,0)=0 )
then
UPDATE tmp_30
SET dj=fbsdj
WHERE htid =contractid ;
END if;
else # 固定价调价函 取固定价
UPDATE tmp_30
SET dj=round(ifnull(fzxhtj,0)-ifnull(ffdje,0),2)
WHERE htid =contractid ;
end if;
else #没有调价函取原价
set fzxhtj=(select IFNULL(a.contract_price,0)
FROM b_salesdeliver_sales_contract_varieties a
WHERE a.pid=contractid AND a.varieties_id=100207 and a.is_deleted=0
ORDER BY a.create_time DESC limit 0,1);
set fhtjlx=(select IFNULL(a.contract_price_type_id,0)
FROM b_salesdeliver_sales_contract_varieties a
WHERE a.pid=contractid AND a.varieties_id=100207 and a.is_deleted=0
ORDER BY a.create_time DESC limit 0,1);
set fzxfdb=(select IFNULL(a.floating_ratio,0)
FROM b_salesdeliver_sales_contract_varieties a
WHERE a.pid=contractid AND a.varieties_id=100207 and a.is_deleted=0
ORDER BY a.create_time DESC limit 0,1);
set ffdje=(select IFNULL(a.floating_price,0)
FROM b_salesdeliver_sales_contract_varieties a
WHERE a.pid=contractid AND a.varieties_id=100207 and a.is_deleted=0
ORDER BY a.create_time DESC limit 0,1);
if fhtjlx<>157316 then
if fqjlx in (9435,9437,9438) #取上月信息价
then
#取信息价最后一次的(暂估取最近一个月的，否则取上月的)
CREATE TEMPORARY TABLE if not exists PRICE_9435
(
select * from
(select ifnull(unit_price,0) zzprice1,(ifnull(ifnull(unit_price,0)-round(ifnull(unit_price,0)*(1-fzxfdb/100),5)+ffdje,0)) zzprice_zk1,
concat(a.year_num,'年',a.month_num,'月信息价') zzfnote1,b.pumping_mode BSLX1
FROM b_information_price_maintain a
INNER JOIN b_information_price_maintain_entity b ON a.id=b.pid
WHERE a.year_num*100+a.month_num <year(fdates)*100+month(fdates) AND b.variety_id=100207
AND a.information_price_type =fhtjlx and b.pumping_mode=9085 and a.is_deleted=0
ORDER BY a.year_num*100+a.month_num DESC limit 0,1) AA
union all
select * from
(select ifnull(unit_price,0) zzprice1,(ifnull(ifnull(unit_price,0)-round(ifnull(unit_price,0)*(1-fzxfdb/100),5)+ffdje,0)) zzprice_zk1,
concat(a.year_num,'年',a.month_num,'月信息价') zzfnote1,b.pumping_mode BSLX1
FROM b_information_price_maintain a
INNER JOIN b_information_price_maintain_entity b ON a.id=b.pid
WHERE a.year_num*100+a.month_num <year(fdates)*100+month(fdates) AND b.variety_id=100207
AND a.information_price_type =fhtjlx and b.pumping_mode=9086 and a.is_deleted=0
ORDER BY a.year_num*100+a.month_num DESC limit 0,1 ) BB);
SET fbsdj=(SELECT round(zzprice1-zzprice_zk1,2) FROM PRICE_9435 WHERE BSLX1=9085);
SET ffbdj=(SELECT round(zzprice1-zzprice_zk1,2) FROM PRICE_9435 WHERE BSLX1=9086);
update tmp_30 set fdb=fzxfdb where htid=contractid;
drop temporary table PRICE_9435;
END IF;
if fqjlx=9436 #取当月信息价
then
#取信息价最后一次的(暂估取最近一个月的，否则取当月的)
CREATE TEMPORARY TABLE if not exists PRICE_9436
(
SELECT * FROM (
select IFNULL(unit_price,0) zzprice1,ifnull(unit_price-round(unit_price*(1-fzxfdb/100),5)+ffdje,0) zzprice_zk1,
concat(a.year_num,'年',a.month_num,'月信息价') zzfnote1,b.pumping_mode BSLX1
FROM b_information_price_maintain a
INNER JOIN b_information_price_maintain_entity b ON a.id=b.pid
WHERE a.year_num*100+a.month_num <=year(fdates)*100+month(fdates) AND b.variety_id=100207
AND a.information_price_type =fhtjlx and b.pumping_mode=9085 and a.is_deleted=0
ORDER BY a.year_num*100+a.month_num DESC limit 0,1) AA
union all
SELECT * FROM (
select IFNULL(unit_price,0) zzprice1,ifnull(unit_price-round(unit_price*(1-fzxfdb/100),5)+ffdje,0) zzprice_zk1,
concat(a.year_num,'年',a.month_num,'月信息价') zzfnote1,b.pumping_mode BSLX1
FROM b_information_price_maintain a
INNER JOIN b_information_price_maintain_entity b ON a.id=b.pid
WHERE a.year_num*100+a.month_num <=year(fdates)*100+month(fdates) AND b.variety_id=100207
AND a.information_price_type =fhtjlx and b.pumping_mode=9086 and a.is_deleted=0
ORDER BY a.year_num*100+a.month_num DESC limit 0,1) BB);
SET fbsdj=(SELECT round(zzprice1-zzprice_zk1,2) FROM PRICE_9436 WHERE BSLX1=9085);
SET ffbdj=(SELECT round(zzprice1-zzprice_zk1,2) FROM PRICE_9436 WHERE BSLX1=9086);
update tmp_30 set fdb=fzxfdb where htid=contractid;
drop temporary table PRICE_9436;
END IF;
#非泵以泵送信息价固定扣减
IF EXISTS ( SELECT 1 FROM b_salesdeliver_sales_contract WHERE id =contractid and is_pump_decrease=1 and ifnull(is_unpump_increase=0,0) )
then
UPDATE tmp_30
SET dj=fbsdj
WHERE htid =contractid ;
END if ;
#泵送以非泵送信息价固定增加
IF EXISTS ( SELECT 1 FROM b_salesdeliver_sales_contract WHERE id =contractid and is_unpump_increase=1 and ifnull(is_pump_decrease,0)=0 )
then
UPDATE tmp_30
SET dj=round(ffbdj+ifnull(fbsj,0),2),fdb=fzxfdb
WHERE htid =contractid ;
END if;
IF EXISTS(SELECT 1 FROM b_salesdeliver_sales_contract WHERE id =contractid and ifnull(is_unpump_increase,0)=0 AND ifnull(is_pump_decrease,0)=0 )
then
UPDATE tmp_30
SET dj=fbsdj
WHERE htid =contractid ;
END if;
else # 固定价调价函 取固定价
UPDATE tmp_30
SET dj=round(ifnull(fzxhtj,0)-ifnull(ffdje,0),2)
WHERE htid =contractid ;
end if;
END if;
END if;
END while ;
CLOSE ID_Curs;
end;
select a.hth 合同号,b.name 结算单位,c.name 工程项目,a.dj C30最新泵送价,fdb 最新浮动比 from tmp_30 a
inner join b_basic_customer b on a.jsdw=b.id
inner join b_basic_project c on a.gcxm=c.id;
drop temporary table tmp_30;
end
优化此存储过程，结构及执行效率
本文详细介绍了CRM系统中如何通过se16:intertable命令进行数据整合，包括合同、产品、合作伙伴、机会等关键信息的查找和获取流程。
扫一扫
1615
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
