dn5N9kHr

1import csv
2import datetime
3import sqlite3
4import os
5
6def date_key(row):
7	return datetime.datetime.strptime(row[0].strip(), "%Y/%m/%d %H:%M:%S")
8
9def read_data_csv(filename):
10	excel_data = []
11	for row in csv.reader(open(filename)):
12		excel_data.append(row)
13	table_headers = excel_data.pop(0) ### removing headers
14	excel_data.sort(key=date_key)
15	return excel_data
16
17
18def create_db_with_dict(data_key,data_values):
19	#data_key = data_keys.replace('-','_') ### Remove special charecters
20	#import pdb; pdb.set_trace()
21	print "="*20
22	print data_key+"    :"+str(len(data_values))+" Records"
23	conn = sqlite3.connect(data_key)
24	c = conn.cursor()
25	c.executescript("DROP TABLE IF EXISTS "+data_key+"; CREATE TABLE "+data_key+" (id INTEGER PRIMARY KEY AUTOINCREMENT,date_time TEXT, applianceName TEXT, tenantName TEXT, localAccCktId INTEGER, localAccCktName TEXT, remoteAccCktId INTEGER, remoteAccCktName TEXT, localSiteId INTEGER, localSiteName TEXT, remoteSiteId INTEGER, remoteSiteName TEXT, fwdClass TEXT, applianceId INTEGER, tenantId INTEGER, delay INTEGER, fwdDelayVar INTEGER, revDelayVar INTEGER, fwdLoss INTEGER, revLoss INTEGER, fwdLossRatio INTEGER, revLossRatio INTEGER, pduLossRatio INTEGER)")
26	conn.commit()
27	for row1 in data_values:
28		row = [w.replace('/', '-') for w in row1]
29		c.execute("INSERT INTO "+data_key+" (date_time, applianceName, tenantName, localAccCktId, localAccCktName, remoteAccCktId, remoteAccCktName, localSiteId, localSiteName, remoteSiteId, remoteSiteName, fwdClass, applianceId, tenantId, delay, fwdDelayVar, revDelayVar, fwdLoss, revLoss, fwdLossRatio, revLossRatio, pduLossRatio) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);", row)
30		conn.commit()
31	conn.close()
32
33
34
35
36def get_new_aggr(db_name,time_in_min):
37	conn = sqlite3.connect(db_name)
38	c = conn.cursor()
39	last_id = 0	
40	c.execute("select max(id),min(date_time) from "+db_name+";")
41	total_datas = c.fetchall()
42	total_max_id = total_datas[0][0]
43	total_max_id = total_datas[0][0]
44	while last_id != total_max_id:
45		c.execute("select min(id), max(id) from "+db_name+" where date_time between (select min(date_time) from "+db_name+" where id > "+str(last_id)+") and (select datetime((select min(date_time) from "+db_name+" where id > "+str(last_id)+"), +'"+str(time_in_min)+" minutes'));")
46		id_datas = c.fetchall()
47		print id_datas
48		last_id = id_datas[0][1]
49		#total_max_id = id_datas[0][1]
50		print "Ratio     :: "+str(id_datas[0][0])+":"+str(id_datas[0][1])
51		#import pdb; pdb.set_trace()
52		c.execute("select date_time from "+db_name+" where id >= "+str(id_datas[0][0])+" and id <= "+str(id_datas[0][1])+";")
53		timestamp_list = []
54		for row in c:
55			timestamp_list.append(row)
56		avg_time = get_avg_times(timestamp_list)
57		print "Average_Time   :"+str(avg_time)
58		c.execute("select localSiteName,avg(delay),avg(fwdDelayVar),avg(revDelayVar),avg(fwdLoss),avg(revLoss),avg(fwdLossRatio),avg(revLossRatio),avg(pduLossRatio) from "+db_name+" where id >= "+str(id_datas[0][0])+" and id <= "+str(id_datas[0][1])+";")
59		avg_datas = c.fetchall()
60		write_to_csv_data = []
61		all_dates = []
62		timestamp_list1 = [i[0] for i in timestamp_list]
63		coll_dates = '__'.join(str(x) for x in timestamp_list1)
64		tm = list(avg_datas[0])
65		tm.insert(0,coll_dates)
66		tm.insert(1,avg_time)
67		record_name = db_name.replace('_','-')
68		tm.insert(2,record_name)
69		#tm.insert(3,data_remote_site_name)
70		write_datas_to_csv('avg_analytics.csv',tm)
71		if total_max_id == last_id:
72			break
73	conn.close()
74	cleanup(db_name)
75
76
77
78
79def get_unique_vcpe(data_list):
80	app = []
81	for a in data_list:
82		mm = a[10]
83		if mm not in app:
84			app.append(mm)
85	app = [v for v in app if "controller" not in v ]
86	return app
87
88
89
90def get_data_per_cpe(cpe_name,data_list):
91	data_by_cpe = []
92	for items in data_list:
93		if cpe_name in items:
94			data_by_cpe.append(items)
95	if len(data_by_cpe)%3 != 0:
96		print "exception on records"
97	return data_by_cpe
98
99
100def write_headers_to_csv(filename):
101	headers = ["Date","Avg-Time","local_site_name","remote_site_name","AVG-delay","AVG-fwdDelayVar","AVG-RevDelayVar","AVG-fwdLoss","AVG-revLoss","AVG-fwdLossRatio","AVG-revLossRatio","AVG-pduLossRatio"]
102	mp = open(filename,'a')
103	wr1 = csv.writer(mp, dialect='excel')
104	wr1.writerow(headers)
105	mp.close()
106
107
108
109def write_datas_to_csv(filename,datas):
110	print datas
111	#print dates
112	mp = open(filename,'a')
113	wr = csv.writer(mp, dialect='excel')
114	wr.writerow(datas)
115	items = []
116	mp.close()
117
118
119def cleanup(file):
120	os.system('rm -rf '+file)
121
122
123def get_avg_times(times_list):
124	only_times = []
125	#print times_list
126	for date_times in times_list:
127		coll_time = str(date_times).split(' ')
128		coll_times = coll_time[1].replace("',)", ' ')
129		#import pdb; pdb.set_trace()
130		only_times.append(coll_times)
131	#print only_times
132	return str(datetime.timedelta(seconds=sum(map(lambda f: int(f[0])*3600 + int(f[1])*60 + int(f[2]), map(lambda f: f.split(':'), only_times)))/len(only_times)))
133
134
135tm = read_data_csv('AnalyticsData_15122016T18.csv')
136tc = get_unique_vcpe(tm)
137cpe_data_dict = {}
138
139for items in tc:
140 	li = get_data_per_cpe(items,tm)
141 	cpe_data_dict[items] = li
142
143
144cleanup('avg_analytics.csv')
145
146write_headers_to_csv('avg_analytics.csv')
147for k,v in cpe_data_dict.iteritems():
148	k = k.replace('-','_')
149	create_db_with_dict(k,v)
150	get_new_aggr(k,'15')