根据org.unit查contract information

本文详细介绍了CRM系统中如何通过se16:intertable命令进行数据整合,包括合同、产品、合作伙伴、机会等关键信息的查找和获取流程。

CRM系统中

se16: inter table

1.      crmd_orgman:

Through filed org.unit(sales) or field namesales_org_resp find object guid.

2.      crmd_link:将上步得到到objectguid放入guid_set里得到表中内容。

其中05代表contract 06代表item guid_HI包括headeritem级的guid

3.      crmd_order_index根据Trans.Category/object_type. BUS2000121(sales contract,spro)查出headerguid.另外sales org, division, distribution channel等也在index表中。

4.      header guid放入crmd_orderadm_h查出contract

5.      查询product number根据H表中contract guid在表crmd_orderadm_i里找到itemguid.

6.      contract status在表CRM_JEST中。

7.      查询partner,由contract number找到header guid; crmd_link 05 07找到set guid;到表crmd_partner中找到partner_no,然后再在but000里找到相应的partner。或者使用        view: ZVORDS_PARTNRS也可以查。

8.      product id可以在表crmd_order_index表里找到,

9.      product description在表COMM_PRSHTEXT里通过comm_product表里的productguid找到。

10.  product target quantity在表CRMD_SCHEDLIN里找到。

11.  opportunity是最难找的,但是据crm同事说一个contract不大可能对应多个opportunity,可以有简单的方法。首先在SRRELROLES,输入key(contractguid),role type NACHFOLGE(德语后置的意思)找到其对应的contract这个rolerole guid,然后找表CRMD_BINREL,这里的roleB为输入的后置contract role guild,而对应的role A则为opportunity role guid。这时,可以用找到的opportunity role guid放入srrelroles表中,找到对应的object guid然后到crmd_orderadm_h表中找到对应的opportunity number。另外一种方法是,在crmd_binrel中找到role guid对应的item guid然后再去crmd_order_index里找对应的header guid,然后去crmd_orderadm_h表中找到对应的transaction.

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 优化此存储过程,结构及执行效率
08-09
这个是完整源码 python实现 Django 【python毕业设计】基于Python的天气预报(天气预测分析)(Django+sklearn机器学习+selenium爬虫)可视化系统.zip 源码+论文+sql脚本 完整版 数据库是mysql 本研究旨在开发一个基于Python的天气预报可视化系统,该系统结合了Django框架、sklearn机器学习库和Selenium爬虫技术,实现对天气数据的收集、分析和可视化。首先,我们使用Selenium爬虫技术从多个天气数据网站实时抓取气象数据,包括温度、湿度、气压、风速等多项指标。这些数据经过清洗和预处理后本研究旨在开发一个基于Python的天气预报可视化系统,该系统结合了Django框架、sklearn机器学习库和Selenium爬虫技术,实现对天气数据的收集、分析和可视化。首先,我们使用Selenium爬虫技术从多个天气数据网站实时抓取气象数据,包括温度、湿度、气压、风速等多项指标。这些数据经过清洗和预处理后,将其存储在后端数据库中,以供后续分析。 其次,采用s,将其存储在后端数据库中,以供后续分析。 其次,采用sklearn机器学习库构建预测模型,通过时间序列分析和回归方法,对未来天气情况进行预测。我们利用以往的数据训练模型,以提高预测的准确性。通过交叉验证和超参数优化等技术手段,我们优化了模型性能,确保其在实际应用中的有效性和可靠性。 最后,基于Django框架开发前端展示系统,实现天气预报的可视化。用户可以通过友好的界面询实时天气信息和未来几天内的天气预测。系统还提供多种图类型,包括折线图和柱状图,帮助用户直观理解天气变化趋势。 本研究的成果为天气预报领域提供了一种新的技术解决方案,不仅增强了数据获取和处理的效率,还提升了用户体验。未来,该系统能够扩展至其他气象相关的应用场景,为大众提供更加准确和及时的气象服务。
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值