#coding=gbk
import ftplib
import os
import os.path
import utildate
import cx_Oracle
import win32com.client
import shutil
(year,mon,day)=utildate.getyearandmonth(-1)
#HOST='10.19.90.210'
#DIRN='/ftproot/extunit/bakfiles/misfiles'
HOST='10.19.84.10'
DIRN='/oracle/jiarm'
f=ftplib.FTP()
f.connect(HOST,21)
#f.login('bosscron','bon_g112')
f.login('oracle','1sq0YuYU')
f.cwd(DIRN)
ldir='D:\\sdmobile\\treaure report&billing\\'+str(year)+str(mon)+'\\temp\\'
if os.path.exists(ldir) ==False:
print ldir+'不存在,开始创建目录'
os.mkdir(ldir)
print '完成创建目录'
os.chdir(ldir)
files=[]
for region in ['530','531','532','533','534','535','536','537','538','539','543','546','631','632','633','634','635']:
files.append(region+year[2:]+str(mon)+'01-01.txt')
for file in files:
print '开始从服务器下载文件,名称:'+file
f.retrbinary('RETR '+file, open(file, 'wb').write)
print '完成从服务器下载文件,名称:'+file
f.quit()
targetdir=ldir
sourcedir='D:\\sdmobile\\treaure report&billing\\'
sourceFilename=sourcedir+'接口数据验证.xls'
targetFilename=targetdir+'接口数据验证'+str(year)+str(mon)+'.xls'
shutil.copyfile(sourceFilename,targetFilename)
xlsApp=win32com.client.Dispatch("Excel.Application")
xlsApp.visible ="true"
xlsBook=xlsApp.Workbooks.Open(targetFilename)
xlsSheet=xlsBook.Sheets("sheet1")
connection = cx_Oracle.connect("acct", "xiaoliu", "ls1")
cursor = connection.cursor()
cycle=str(year)+str(mon)
sql='select region,sum(fee)/100 from newrpt.statq_erp_income@lnk_scx1a \
where cycle='+cycle+' group by region order by region'
cursor.execute(sql)
col=1;row=1
print '@#$%&&导出数据库中的数据开始'
for region,totalfee in cursor.fetchall():
row+=1
print '@#$%&&导出数据库中的数据开始'+str(region)
xlsSheet.cells(row,col).value=region
xlsSheet.cells(row,col+1).value=totalfee
print '@#$%&&导出数据库中的数据完成'+str(region)
cursor.close()
connection.close()
print '@#$%&&导出数据库中的数据完成'
print '***!!!@#$%&&提取接口文件中的数据开始'
col=2;row=1
for region in ['530','531','532','533','534','535','536','537','538','539','543','546','631','632','633','634','635']:
#for region in []:
total=0.00
row+=1
File='D:\\sdmobile\\treaure report&billing\\'+str(year)+str(mon)+'\\temp\\'+region+year[2:]+str(mon)+'01-01.txt'
lines=[line.rstrip() for line in open( File,'U')]
for a in lines:
l=a.split(',')
total=total+float(l[4])
xlsSheet.cells(row,col+1).value=total
print '***!!!@#$%&&提取接口文件中的数据完成'
#xlsBook.SaveAs('d:\\book1.xls')
#xlsApp.quit()