-
Notifications
You must be signed in to change notification settings - Fork 0
/
partnerflow2excel.py
141 lines (115 loc) · 3.7 KB
/
partnerflow2excel.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
#coding:utf-8
import os
import sys
import string
import smtplib
import MySQLdb
from pymongo import MongoClient
import datetime
import schedule
import time
import json
from pytz import timezone
from kits import getdbinfo
from xlsxwriter.workbook import Workbook
import xlrd
post_month = 0
reload(sys)
sys.setdefaultencoding("utf-8")
def createDailyFloder(partner):
tmpFloderName ="/ukl/apache-tomcat-7.0.67/webapps/weekdata/"+partner
if not os.path.exists(tmpFloderName):
os.makedirs(tmpFloderName)
floderName = tmpFloderName
return floderName
def getDBconnection(dbinfor,dbname):
dbconnection = MySQLdb.connect(user = dbinfor['usr'],
passwd = dbinfor['pwd'],
host = dbinfor['host'],
port = dbinfor['port'],
db = dbname,
charset = 'utf8')
return dbconnection
def getCon(dbcode,dbname):
DBinfo = getdbinfo(dbcode)
DBname = dbname
try:
DBconnection = getDBconnection(DBinfo,DBname)
except Exception as e:
print e
time.sleep(20)
return getCon(dbcode,dbname)
Cur = DBconnection.cursor()
return [DBconnection,Cur]
def publishQuery(period):
yesterQuery = """
SELECT * FROM
t_flowsummary_partner_adv
WHERE DATE(epochTime) = DATE_SUB(DATE(NOW()),INTERVAL 1 DAY)
"""
weeklyQuery = """
SELECT WEEK(NOW()) AS weeknum,iso2,partner,SUM(reserveup),SUM(reservedown),SUM(freeup),SUM(freedown),SUM(reservecnt),SUM(freecnt) FROM
t_flowsummary_partner_adv
WHERE DATE(epochTime) BETWEEN DATE_SUB(DATE(NOW()),INTERVAL 7 DAY) AND DATE_SUB(DATE(NOW()),INTERVAL 1 DAY)
GROUP BY iso2,partner
"""
rList = list()
con,cur = getCon("REMOTE","login_history")
if period == 'weekly':
cur.execute(weeklyQuery)
else:
cur.execute(yesterQuery)
tm = cur.fetchall()
for t in tm:
rList.append(t)
con.close()
foldername = createDailyFloder(period)
y = datetime.datetime.now().date()
dateStr = str(y)
filename = period + "_"+dateStr+".xlsx"
workbook = Workbook(foldername+"/"+filename)
sheet = workbook.add_worksheet()
date_format = workbook.add_format({'num_format': 'mmmm d yyyy'})
if period == "daily":
sheet.write(0,0,u"日期".encode('utf-8'),date_format)
else:
sheet.write(0,0,u"周".encode('utf-8'))
sheet.write(0,1,u"国家".encode('utf-8'))
sheet.write(0,2,"partner")
sheet.write(0,3,u"包卡上行".encode('utf-8'))
sheet.write(0,4,u"包卡下行".encode('utf-8'))
sheet.write(0,5,u"非包卡上行".encode('utf-8'))
sheet.write(0,6,u"非包卡下行".encode('utf-8'))
sheet.write(0,7,u"包卡用户数".encode('utf-8'))
sheet.write(0,8,u"非包卡用户数".encode('utf-8'))
r =1
for row in rList:
sheet.write(r,0,row[0])
sheet.write(r,1,row[1])
sheet.write(r,2,row[2])
sheet.write(r,3,row[3])
sheet.write(r,4,row[4])
sheet.write(r,5,row[6])
sheet.write(r,6,row[5])
sheet.write(r,7,row[7])
sheet.write(r,8,row[8])
r += 1
workbook.close()
def initiate():
today = datetime.date.today()
weekday = today.weekday()
now_month = today.strftime("%d")
publishQuery("daily")
if weekday == 4:
publishQuery("weekly")
if __name__ == "__main__":
today = datetime.date.today()
weekday = today.weekday()
post_month = today.strftime("%d")
createDailyFloder("daily")
createDailyFloder("weekly")
initiate()
schedule.every().day.at("1:20").do(initiate)
while True:
schedule.run_pending()
time.sleep(1)