VF实现按日期自动运行的处理机制

CLOSE TABLES
SET SAFETY OFF
SET TALK OFF
SET DEFAULT TO D:/

CLOSE TABLES
USE xinzengbada
ZAP
cc=date()-1
DT=dtos(cc)
@ 5,10 say DT
FIC='xinzengbada'+ALLTRIM(DT)+'.txt'
*@ 20,10 say FIB
FICNEW='xinzengbada'+ALLTRIM(DT)
FICADD='add_'+ALLTRIM(DT)
FICNEWTXT='add'+ALLTRIM(DT)+'.txt'
ADDBEIFEN='add_30_'+ALLTRIM(DT)+'.txt'
*FIA=ALLTRIM(DT)
APPEND FROM &FIC DELIMITED WITH CHARACTER |
COPY TO &FICNEW
SELECT dist mobileno from &FICNEW WHERE mobileno NOT in(select mobileno FROM add_30) AND servproid>0 into table &FICADD
*COPY TO &FICADD
CLOSE TABLES
SELECT *From add_30 into curs add_30bk
COPY TO &ADDBEIFEN DELIMITED WITH |
CLOSE TABLES
*INSERT INTO add_30 SELECT mobileno FROM &FICADD
use add_30
appe from &FICADD
close tables
SELECT *From &FICADD into curs FICNEWTXTbk
COPY TO &FICNEWTXT DELIMITED WITH |

CLOSE TABLES
SET SAFETY OFF
SET TALK OFF
cc=date()-1
DT=dtos(cc)
@ 5,10 say DT
FIB='haitaobeifen'+ALLTRIM(DT)+'.txt'
FYC='haitao_yucun'+ALLTRIM(DT)
FBL='haitao_banli'+ALLTRIM(DT)
FIBBL='haitao'+ALLTRIM(DT)+'.txt'
FBLI=FBL+'_I'
FBLD=FBL+'_D'
YCA='ycbabai_add'+ALLTRIM(DT)+'.txt'
YCB='ycwubai_add'+ALLTRIM(DT)+'.txt'
YCC='ycsanbai_add'+ALLTRIM(DT)+'.txt'
YCCNEW='ycbaili'+ALLTRIM(DT)+'.xls'
YCQXA='qxbabai_del'+ALLTRIM(DT)+'.txt'
YCQXB='qxwubai_del'+ALLTRIM(DT)+'.txt'
YCQXC='qxsanbai_del'+ALLTRIM(DT)+'.txt'
@ 40,10 say FIBBL
ZFL='ADDZong'+ALLTRIM(DT)+'.txt'
ZFLQX='Zong_QX'+ALLTRIM(DT)+'.txt'
CWBL='CWBL'+ALLTRIM(DT)+'.txt'
CLOSE TABLES
USE haitao_yucun
ZAP
COPY STRUCTURE TO &FYC
CLOSE TABLES
USE &FYC
ZAP
APPEND FROM &FIB DELIMITED WITH CHARACTER |

cc=date()-1
DT=dtos(cc)
FIBBL='haitao'+ALLTRIM(DT)+'.txt'
FBL='haitao_banli'+ALLTRIM(DT)
FIB='haitaobeifen'+ALLTRIM(DT)+'.txt'
FYC='haitao_yucun'+ALLTRIM(DT)
FBL='haitao_banli'+ALLTRIM(DT)
FIBBL='haitao'+ALLTRIM(DT)+'.txt'
FBLI=FBL+'_I'
FBLD=FBL+'_D'
YCA='ycbabai_add'+ALLTRIM(DT)+'.txt'
YCB='ycwubai_add'+ALLTRIM(DT)+'.txt'
YCC='ycsanbai_add'+ALLTRIM(DT)+'.txt'
YCQXA='qxbabai_del'+ALLTRIM(DT)+'.txt'
YCQXB='qxwubai_del'+ALLTRIM(DT)+'.txt'
YCQXC='qxsanbai_del'+ALLTRIM(DT)+'.txt'
@ 40,10 say FIBBL
ZFL='ADDZong'+ALLTRIM(DT)+'.txt'
ZFLQX='Zong_QX'+ALLTRIM(DT)+'.txt'
CWBL='CWBL'+ALLTRIM(DT)+'.txt'
use haitao_banli
ZAP
COPY STRUCTURE TO &FBL
CLOSE TABLES
USE &FBL
ZAP

APPEND FROM &FIBBL DELIMITED WITH CHARACTER |

*SELECT * from haitao_banli WHERE idu!='I'
SELECT * from &FBL WHERE optype!='I' into curs dl
SET DELETED ON
DELETE FROM &FBL WHERE optype in('O','U')
UPDATE &FBL SET opr=SUBSTR(opr,1,16)
SELECT mobileno,MAX(opr)as opr from &FBL GROUP BY mobileno INTO dbf kkk
SELECT a.* from &FBL a JOIN kkk b ON a.opr=b.opr AND a.mobileno=b.mobileno ORDER BY 1 INTO dbf tttt

SELECT mobileno from tttt GROUP BY mobileno HAVING count(*)>1 INTO DBF duo
SELECT * from tttt WHERE mobileno in (select mobileno from duo) into table temp
*COPY TO temp
SELECT mobileno from temp WHERE optype='D' INTO dbf aaa
SELECT mobileno from temp WHERE optype='I' INTO dbf aaa1
SELECT * from aaa WHERE NOT exist(select * from aaa1) into curs dl1
SET DEleted ON
DELETE FROM temp WHERE optype ='D'

DELETE FROM tttt WHERE mobileno in (select mobileno from duo)
*INSERT INTO tttt SELECT * FROM TEMP
close tables
use tttt
appe from TEMP
close tables
SELECT * FROM tttt WHERE optype='D' INTO dbf &FBLD
SELECT * FROM tttt WHERE optype='I' INTO dbf &FBLI

*SELECT count(*) from &FBLI WHERE privid in('75900000062583','75900000062584','75900000062585')
*COPY TO &YCA DELIMITED WITH |

*SELECT count(*) from &FBLI WHERE privid in('75900000062586','75900000062587','75900000062588')
*COPY TO &YCB DELIMITED WITH |

*SELECT count(*) from &FBLI WHERE privid in('75900000062589','75900000062590','75900000062591')
*COPY TO &YCC DELIMITED WITH |

SELECT "800" as leixing,count(*)as num from &FBLI WHERE privid in('00000062583','00000062584','00000062585');
union;
SELECT "500" as leixing,count(*) as num from &FBLI WHERE privid in('00000062586','00000062587','00000062588');
union;
SELECT "300" as leixing,count(*) as num from &FBLI WHERE privid in('00000062589','00000062590','00000062591') into curs banli
copy to &YCCNEW type xls
close tables

SELECT count(*) from &FBLD WHERE privid in('75900000062583','75900000062584','75900000062585') into curs zzz
COPY TO &YCQXA DELIMITED WITH |

SELECT count(*) from &FBLD WHERE privid in('75900000062586','75900000062587','75900000062588') into curs yyy
COPY TO &YCQXB DELIMITED WITH |

SELECT count(*) from &FBLD WHERE privid in('75900000062589','75900000062590','75900000062591') into curs xxx
COPY TO &YCQXC DELIMITED WITH |
CLOSE TABLES
*CLOSE ALL

*SELECT DISTINCT mobileno From haitao_banli20080201_I.DBF WHERE privid in('75900000062583','75900000062584','75900000062585')
SELECT DISTINCT mobileno From &FBLI into curs zflbk
COPY TO &ZFL DELIMITED WITH |
CLOSE TABLES
SELECT DISTINCT mobileno From &FBLD into curs zflqxbk
COPY TO &ZFLQX DELIMITED WITH |
CLOSE TABLES
CLOSE ALL

SELECT *From &FBLI WHERE mobileno in(select mobileno FROM &FBLI GROUP BY mobileno HAVING count(*)>1) into curs cwblbk
COPY TO &CWBL DELIMITED WITH |
CLOSE TABLES

FBLIBK=FBL+'IBK'
FBLIBK2=FBL+'IBK'+'.txt'
USE &FBLI
COPY STRUCTURE TO &FBLIBK
CLOSE TABLES
USE &FBLIBK
APPEND FROM &FIBBL DELIMITED WITH |
CLOSE ALL
SELECT mobileno From &FBLIBK GROUP BY mobileno HAVING count(*)>1 into curs FBLIBK2bk
COPY TO &FBLIBK2 DELIMITED WITH |

*select *From haitao_banli20080203_I where mobileno not in (select dhhm from haitaobeifen)