CLEAR
SET SAFE OFF
ef=CREATEOBJECT('Excel.application')
ef.Workbooks.open("D:/bb_704/bb_obje/分析表_A.xls")
ef.visible=.t.
ef.Worksheets("sheet1").Activate
&&先处理三月份的数据
close table
use BB_HF_703 &&用三月份原始数据
copy to bb_hf_703_1
repl all dh2 with 'S' for dh2='D'
select dh2,count(*) as zscnt,sum(to_hf) as zsfee ,sum(durat) as zstime from bb_hf_703_1 group by dh2 into dbf bb_hf_703_bk
close all
use bb_hf_703_bk
*brow
*susp
i=0
select zscnt from bb_hf_703_bk into arra zscntarr
for each cntarr in zscntarr
i=i+1
do case
case i=1
G=cntarr
case i=2
H=cntarr
case i=3
J=cntarr
case i=4
S=cntarr
otherwise
V=cntarr
endcase
endfor
ef.cells(3,3).value=G
ef.cells(4,3).value=V
ef.cells(5,3).value=J
ef.cells(6,3).value=S
ef.cells(7,3).value=H
ef.cells(8,3).value=G+V+J+S+H
select zsfee from bb_hf_703_bk into arra zsfeearr
j=0
for each feearr in zsfeearr
j=j+1
do case
case j=1
Gfee=feearr
case j=2
Hfee=feearr
case j=3
Jfee=feearr
case j=4
Sfee=feearr
otherwise
Vfee=feearr
endcase
endfor
ef.cells(9,3).value=Gfee
ef.cells(10,3).value=Vfee
ef.cells(11,3).value=Jfee
ef.cells(12,3).value=Sfee
ef.cells(13,3).value=Hfee
ef.cells(14,3).value=Gfee+Vfee+Jfee+Sfee+Hfee
select zstime from bb_hf_703_bk into arra zstimearr
k=0
for each timearr in zstimearr
k=k+1
do case
case k=1
Gtime=timearr
case k=2
Htime=timearr
case k=3
Jtime=timearr
case k=4
Stime=timearr
otherwise
Vtime=timearr
endcase
endfor
ef.cells(15,3).value=Gtime
ef.cells(16,3).value=Vtime
ef.cells(17,3).value=Jtime
ef.cells(18,3).value=Stime
ef.cells(19,3).value=Htime
ef.cells(20,3).value=Gtime+Vtime+Jtime+Stime+Htime
close all
&&处理二月份的数据
use BB_HF_702
copy to bb_hf_702_1
repl all dh2 with 'S' for dh2='D'
select dh2,count(*) as zscntfirst from bb_hf_702_1 group by dh2 into dbf bb_hf_702_bk
mannum=1
Gfnum=0
Vfnum=0
Jfnum=0
Sfnum=0
Hfnum=0
close all
select zscntfirst from bb_hf_702_bk into arra cntfirst
*brow
*susp
for each cntf in cntfirst
mannum=mannum+1
do case
case mannum=1
Gfnum=cntf
case mannum=2
Vfnum=cntf
case mannum=3
Jfnum=cntf
case mannum=4
Sfnum=cntf
otherwise
Hfnum=cntf
endcase
endfor
Gmannum=Gfnum+G
Vmannum=Vfnum+V
Jmannum=Jfnum+J
Smannum=Sfnum+S
Hmannum=Hfnum+H
&&计算各产品线ARPU之值
ef.cells(21,3).value=Gfee*2/Gmannum
ef.cells(22,3).value=Vfee*2/Vmannum
ef.cells(23,3).value=Jfee*2/Jmannum
ef.cells(24,3).value=Sfee*2/Smannum
ef.cells(25,3).value=Hfee*2/Hmannum
ef.cells(26,3).value=(ef.cells(21,3).value+ef.cells(22,3).value+ef.cells(23,3).value+ef.cells(24,3).value+ef.cells(25,3).value)/5
&&计算各产品线MOU之值
ef.cells(27,3).value=Gtime*2/Gmannum
ef.cells(28,3).value=Vtime*2/Vmannum
ef.cells(29,3).value=Jtime*2/Jmannum
ef.cells(30,3).value=Stime*2/Smannum
ef.cells(31,3).value=Htime*2/Hmannum
ef.cells(32,3).value=(ef.cells(27,3).value+ef.cells(28,3).value+ef.cells(29,3).value+ef.cells(30,3).value+ef.cells(31,3).value)/5
&&计算各产品线ARPM之值
GARPM=Gfee/Gtime
VARPM=Vfee/Vtime
JARPM=Jfee/Jtime
SARPM=Sfee/Stime
HARPM=Hfee/Htime
ef.cells(33,3).value=GARPM
ef.cells(34,3).value=VARPM
ef.cells(35,3).value=JARPM
ef.cells(36,3).value=SARPM
ef.cells(37,3).value=HARPM
ef.cells(38,3).value=(GARPM+VARPM+JARPM+SARPM+HARPM)/5
brow
susp
SET SAFETY OFF
SET DELETED ON
CLOSE table
USE tongji
ZAP
APPEND FROM jiangpin.txt DELIMITED WITH CHARACTER |
DELETE FOR SUBSTR(danhao,1,2)!='ZJ'
USE mubiao
zap
CLOSE TABLES
SELECT 1
USE tongji
SELECT 2
use mubiao
SELECT 1
GO top
DO WHILE .NOT.eof()
l1=danhao
l2=start
l3=end
l4=leixing
FOR i=1 TO 10
ll='t'+ALLTRIM(STR(i))
KK=ALLTRIM(&LL)
IF KK!='' then
duan1=&ll
n1=SUBSTR(duan1,1,AT(',',duan1,1)-1)
n2=SUBSTR(duan1,AT(',',duan1,1)+1,AT(',',duan1,2)-AT(',',duan1,1)-1 )
n3=SUBSTR(duan1,AT(',',duan1,2)+1,AT(',',duan1,3)-AT(',',duan1,2)-1 )
n4=SUBSTR(duan1,AT(',',duan1,3)+1,LEN(ALLTRIM(duan1))-AT(',',duan1,3))
*endif
*endfor
SELECT 2
APPEND blank
REPLACE danhao WITH l1,start WITH l2, end WITH l3,leixing WITH l4,bumen WITH n1,name WITH n2,jishou WITH n3,chuli WITH n4
SELECT 1
ENDIF
endfor
SELECT 1
SKIP
ENDDO
SUSP
SELECT leixing ,count(*) as num from tongji WHERE danhao like "ZJ07%" GROUP BY leixing
COPY TO 月故障类型分布.xls TYPE XLS
SELECT *,((VAL(SUBSTR(CHULI,9,2))*24+VAL(SUBSTR(CHULI,12,2)))-(VAL(SUBSTR(jishou,9,2))*24+VAL(SUBSTR(JISHOU,12,2)) ))/24 AS SHIXIAN , ;
" " BIAOJI from mubiao INTO DBF TEMP
UPDATE TEMP SET BIAOJI="超时" WHERE shixian>=2.0
UPDATE TEMP SET BIAOJI="未超时" WHERE shixian<2.0
UPDATE temp SET bumen =SUBSTR(bumen,AT('/',bumen,2)+1)
SELECT * from temp WHERE danhao like "ZJ0608%"
SELECT danhao as 工单号,leixing as 类型,bumen 部门 ,name 受理人,jishou 受理时间,chuli 完成时间 from temp WHERE danhao like "ZJ0608%" AND biaoji='超时' AND bumen not like '计算机室%' AND bumen!=' ' order BY 1
brow
susp
COPY TO 超时工单明细1.xls TYPE xls
*后续处理的
*SUSPEND
close all
UPDATE temm SET leixing=SUBSTR(leixing,1,AT('/',leixing,1)-1)
SELECT leixing,bumen,biaoji,count(*) from temm GROUP BY leixing,bumen,biaoji
COPY TO kkk
COPY TO kkk.xls TYPE xls
UPDATE kkk SET bumen=substr(bumen,8,40) WHERE SUBSTR(bumen,1,7)='服务厅/'
SELECT * from kkk
UPDATE kkk SET bumen=strtran( bumen,'/ ','')
SELECT * from kkk
DELETE FROM kkk WHERE bumen like '计算机室%'
SELECT * from kkk
DELETE FROM kkk WHERE bumen like '计算机室%' OR bumen=''
DELETE FROM kkk WHERE bumen like '计算机室%' OR bumen=' '
SELECT * from kkk
COPY TO gbmg.xls TYPE xls
分享到:
相关推荐
VF编程与软件开发若干实例
vfp vf 操作excel,灰常好的资料,全攻略,保证立马上手!
VF数据编程
vf6.0的计算机数据编程软件 可用于多个行业 是一款不错的数据入库程序
优科 ruckus vf2825 DD编程器固件
优科 RUCKUS vf2825 UBNT 编程器固件
飞鱼星VF15编程器固件
这是一个51单片机读写sst39vf400a存储器的例子
品牌 型号 版本 主控 无线 内存 闪存 飞鱼星 VF15 1.01 RTL8196C RTL8192CE SD 8MB 4MB
优科 ruckus VF2111编程器固件!不能直接刷哦!
转:VFP全面控制EXCEL。 讲述全面,很实用,分享给大家。
飞鱼星vf16编程器固件Build-1030
这是一个vf6.0上机操作题 ,比较全面
将"dbf"文件转换为"xls
VF数据库操作的基本命令 新手的首选 VF数据库操作的基本命令 新手的首选
VF辅导:内存变量操作命令及文件操作命令 来源:学生大 2007年11月01日 11:35 一.内存变量操作命令: 1.显示内存变量: 格式:display memory [to printer][to 文本文件名]或 list memory [to printer][to...
三菱电梯SG-VF程序.zip三菱PLC编程案例源码资料编程控制器应用通讯通信例子程序实例三菱电梯SG-VF程序.zip三菱PLC编程案例源码资料编程控制器应用通讯通信例子程序实例三菱电梯SG-VF程序.zip三菱PLC编程案例源码资料...
如何将excel.xslx表格数据导出成Visual Fox(VF)表.zip
在Visual Foxpro编程中通过VF日历控件,VF日期插件,VF日期控件实现日期输入