def default(self,**kw):
#pull data on this view from DB
conn = MySQLdb.connect(configuration.DB_HOSTNAME,configuration.DB_USERNAME,configuration.DB_PASSWORD,configuration.DB_NAME,use_unicode=True)
curs = conn.cursor()
curs.execute("SELECT title,xdata,ydata,xlabel,ylabel,filters,y_xform,tbl FROM _wsviews WHERE url = %s", (self.url,))
view_data = curs.fetchall()[0]
log.debug("pie chart request, here is SQL data for this view:")
log.debug(view_data)
tbl = view_data[7]
#fetch the relevant filters for this template and their possible values
available_filters = view_data[5].split(',')
fdata = dict()
for filter in available_filters:
curs.execute("SELECT DISTINCT "+filter+" FROM "+tbl)
#curs.fetchall() returns a list of lists, we convert this to a plain list for ease of handling
raw_fdata = curs.fetchall()
fdata[filter] = []
for i in raw_fdata:
fdata[filter].append(i[0])
#print fdata
filters = ""
used_filters = helperlib.intersect(kw.keys(),available_filters)
ufilters_vals = dict()
startdate = ""
enddate = ""
for filter in used_filters:
kw[filter] = helperlib.listfix(kw[filter])
filter_vals = helperlib.intersect(kw[filter],fdata[filter])
filters = helperlib.fconstruct(filters,filter,filter_vals)
ufilters_vals[filter] = filter_vals
if 'startdate' in kw and 'enddate' in kw and helperlib.is_valid_date(kw['startdate']) and helperlib.is_valid_date(kw['enddate']):
filters = helperlib.rangeconstruct(filters,"timestamp",kw['startdate'],kw['enddate'])
used_filters.append("dates")
ufilters_vals["dates"] = [kw['startdate'] + " to " + kw['enddate']]
startdate = kw['startdate']
enddate = kw['enddate']
#get columns and column transformations for this view
y_xforms = view_data[6].split(',')
y_data = view_data[2].split(',')
y_data_str = ""
#they must be equal!
assert len(y_data) == len(y_xforms)
for i in range(len(y_data)):
y_data_str += y_xforms[i] + "(" + y_data[i] + "),"
y_data_str = y_data_str[0:len(y_data_str)-1]
query = "SELECT %s,%s FROM %s %s GROUP BY %s" % (view_data[1],y_data_str,tbl,filters,view_data[1])
log.debug("SQL query:")
log.debug(query)
results = helperlib.scaled_query(curs,tbl,query,100,evaluators.count_eval)
log.debug("query result:")
log.debug(results)
#generate JS datafields here because genshi templating can't emit JS...
data = ""
for i in range(0,len(results)):
data += "data.setValue("+str(i)+",0,'"+results[i][0]+"');\n"
data += "data.setValue("+str(i)+",1,"+str(results[i][1])+");\n"
return dict(title=view_data[0],xlabel=view_data[3],ylabel=view_data[4],piedata=results,
data=data,filters=available_filters,used_filters=used_filters,
ufilters_vals=ufilters_vals,fdata=fdata,startdate=startdate,enddate=enddate)
def default(self, **kw):
#pull data on this view from DB
conn = MySQLdb.connect(configuration.DB_HOSTNAME,
configuration.DB_USERNAME,
configuration.DB_PASSWORD,
configuration.DB_NAME,
use_unicode=True)
curs = conn.cursor()
curs.execute(
"SELECT title,xdata,ydata,xlabel,ylabel,filters,y_xform FROM _wsviews WHERE url = %s",
(self.url, ))
view_data = curs.fetchall()[0]
log.debug("pie chart request, here is SQL data for this view:")
log.debug(view_data)
#fetch the relevant filters for this template and their possible values
available_filters = view_data[5].split(',')
fdata = dict()
for filter in available_filters:
curs.execute("SELECT DISTINCT " + filter + " FROM GAMES_SP")
#curs.fetchall() returns a list of lists, we convert this to a plain list for ease of handling
raw_fdata = curs.fetchall()
fdata[filter] = []
for i in raw_fdata:
fdata[filter].append(i[0])
#print fdata
filters = ""
used_filters = helperlib.intersect(kw.keys(), available_filters)
ufilters_vals = dict()
for filter in used_filters:
kw[filter] = helperlib.listfix(kw[filter])
filter_vals = helperlib.intersect(kw[filter], fdata[filter])
filters = helperlib.fconstruct(filters, filter, filter_vals)
ufilters_vals[filter] = filter_vals
if 'startdate' in kw and 'enddate' in kw and helperlib.isvalid(
kw['startdate']) and helperlib.isvalid(kw['enddate']):
filters = helperlib.dateconstruct(filters, kw['startdate'],
kw['enddate'])
used_filters.append("dates")
ufilters_vals["dates"] = [kw['startdate'] + "-" + kw['enddate']]
#get columns and column transformations for this view
y_xforms = view_data[6].split(',')
y_data = view_data[2].split(',')
y_data_str = ""
#they must be equal!
assert len(y_data) == len(y_xforms)
for i in range(len(y_data)):
y_data_str += y_xforms[i] + "(" + y_data[i] + "),"
y_data_str = y_data_str[0:len(y_data_str) - 1]
query = "SELECT " + view_data[
1] + "," + y_data_str + " FROM GAMES_SP " + filters + " GROUP BY " + view_data[
1]
log.debug("SQL query:")
log.debug(query)
results = helperlib.scaled_query(curs, query, 100,
evaluators.count_eval)
log.debug("query result:")
log.debug(results)
#generate JS datafields here because genshi templating can't emit JS...
data = ""
for i in range(0, len(results)):
data += "data.setValue(" + str(
i) + ",0,'" + results[i][0] + "');\n"
data += "data.setValue(" + str(i) + ",1," + str(
results[i][1]) + ");\n"
return dict(title=view_data[0],
xlabel=view_data[3],
ylabel=view_data[4],
piedata=results,
data=data,
filters=available_filters,
used_filters=used_filters,
ufilters_vals=ufilters_vals,
fdata=fdata)
def default(self, **kw):
#pull data on this view from DB
conn = MySQLdb.connect(configuration.DB_HOSTNAME,
configuration.DB_USERNAME,
configuration.DB_PASSWORD,
configuration.DB_NAME,
use_unicode=True)
curs = conn.cursor()
curs.execute(
"SELECT title,xdata,ydata,xlabel,ylabel,filters,y_xform,tbl FROM _wsviews WHERE url = %s",
(self.url, ))
view_data = curs.fetchall()[0]
log.debug("line chart request, here is SQL data for this view:")
log.debug(view_data)
tbl = view_data[7]
#fetch the relevant filters for this template and their possible values
available_filters = view_data[5].split(',')
fdata = dict()
for filter in available_filters:
curs.execute("SELECT DISTINCT " + filter + " FROM " + tbl)
#curs.fetchall() returns a list of lists, we convert this to a plain list for ease of handling
raw_fdata = curs.fetchall()
fdata[filter] = []
for i in raw_fdata:
fdata[filter].append(i[0])
#print fdata
filters = ""
used_filters = helperlib.intersect(kw.keys(), available_filters)
ufilters_vals = dict()
startdate = ""
enddate = ""
for filter in used_filters:
kw[filter] = helperlib.listfix(kw[filter])
filter_vals = helperlib.intersect(kw[filter], fdata[filter])
filters = helperlib.fconstruct(filters, filter, filter_vals)
ufilters_vals[filter] = filter_vals
if 'startdate' in kw and 'enddate' in kw and helperlib.is_valid_date(
kw['startdate']) and helperlib.is_valid_date(kw['enddate']):
filters = helperlib.rangeconstruct(filters, "timestamp",
kw['startdate'], kw['enddate'])
used_filters.append("dates")
ufilters_vals["dates"] = [kw['startdate'] + " to " + kw['enddate']]
startdate = kw['startdate']
enddate = kw['enddate']
#calculate the number of days in the range
daterange = TWOYEARS + 1
date_sampling_filter = ""
if used_filters.__contains__("dates"):
daterange = helperlib.get_date_range(kw['startdate'],
kw['enddate'])
if daterange > TWOYEARS:
date_sampling_filter = " DAYOFMONTH(timestamp) = '1' AND MONTH(timestamp) % 2 "
elif daterange > SIXMONTHS:
date_sampling_filter = " DAYOFMONTH(timestamp) = '1' "
elif daterange > THREEMONTHS:
date_sampling_filter = " DAYOFMONTH(timestamp) % '14' = 0 "
elif daterange > ONEMONTH:
date_sampling_filter = " DAYOFMONTH(timestamp) % '7' = 0 "
elif daterange > TWOWEEKS:
date_sampling_filter = " DAYOFMONTH(timestamp) % '3' = 0 "
elif daterange > ONEWEEK:
date_sampling_filter = " "
#must do special routines for hourly sampling
elif daterange > ONEDAY:
pass
else:
pass
filters = helperlib.fconstruct_helper(filters, date_sampling_filter)
#get columns and column transformations for this view
y_xforms = view_data[6].split(',')
y_data = view_data[2].split(',')
y_data_str = ""
y_group_str = ""
#number of y columns and y transformations must be equal
assert len(y_data) == len(y_xforms)
#we currently only support time as the x axis
assert view_data[1] == "timestamp"
for i in range(len(y_data)):
y_data_str += y_xforms[i] + "(" + y_data[i] + ")," + y_data[i] + ","
y_group_str += y_data[i] + ","
y_data_str = y_data_str[0:len(y_data_str) - 1]
y_group_str = y_group_str[0:len(y_group_str) - 1]
query = "SELECT CAST(timestamp as DATE),%s FROM %s %s GROUP BY CAST(timestamp as DATE),%s" % (
y_data_str, tbl, filters, y_group_str)
log.debug("SQL query:")
log.debug(query)
results = helperlib.scaled_query(curs, tbl, query, 100,
evaluators.simple_eval)
#log.debug("query result:")
#log.debug(results)
data = LineGraphController.reformat_data(self, results)
#generate JS datafields here because genshi templating can't emit JS...
js_celldata = ""
js_columnnames = ""
dates = data[0].keys()
dates.sort()
for y in data[1]:
js_columnnames += "data.addColumn('number', '" + y + "');\n"
for i in range(len(dates)):
js_celldata += "data.setCell(" + str(
i) + ", 0, '" + dates[i].__str__() + "');\n"
for j in range(len(data[0][dates[i]])):
js_celldata += "data.setCell(" + str(i) + ", " + str(
j + 1) + ", " + str(data[0][dates[i]][j][1]) + ");\n"
return dict(title=view_data[0],
xlabel=view_data[3],
js_celldata=js_celldata,
filters=available_filters,
used_filters=used_filters,
js_columnnames=js_columnnames,
ufilters_vals=ufilters_vals,
fdata=fdata,
numdates=len(dates),
startdate=startdate,
enddate=enddate)
def default(self,**kw):
#pull data on this view from DB
conn = MySQLdb.connect(configuration.DB_HOSTNAME,configuration.DB_USERNAME,configuration.DB_PASSWORD,configuration.DB_NAME,use_unicode=True)
curs = conn.cursor()
curs.execute("SELECT title,xdata,ydata,xlabel,ylabel,filters,y_xform,tbl FROM _wsviews WHERE url = %s", (self.url,))
view_data = curs.fetchall()[0]
log.debug("line chart request, here is SQL data for this view:")
log.debug(view_data)
tbl = view_data[7]
#fetch the relevant filters for this template and their possible values
available_filters = view_data[5].split(',')
fdata = dict()
for filter in available_filters:
curs.execute("SELECT DISTINCT " + filter + " FROM " + tbl)
#curs.fetchall() returns a list of lists, we convert this to a plain list for ease of handling
raw_fdata = curs.fetchall()
fdata[filter] = []
for i in raw_fdata:
fdata[filter].append(i[0])
#print fdata
filters = ""
used_filters = helperlib.intersect(kw.keys(),available_filters)
ufilters_vals = dict()
startdate = ""
enddate = ""
for filter in used_filters:
kw[filter] = helperlib.listfix(kw[filter])
filter_vals = helperlib.intersect(kw[filter],fdata[filter])
filters = helperlib.fconstruct(filters,filter,filter_vals)
ufilters_vals[filter] = filter_vals
if 'startdate' in kw and 'enddate' in kw and helperlib.is_valid_date(kw['startdate']) and helperlib.is_valid_date(kw['enddate']):
filters = helperlib.rangeconstruct(filters,"timestamp",kw['startdate'],kw['enddate'])
used_filters.append("dates")
ufilters_vals["dates"] = [kw['startdate'] + " to " + kw['enddate']]
startdate = kw['startdate']
enddate = kw['enddate']
#calculate the number of days in the range
daterange = TWOYEARS+1
date_sampling_filter = ""
if used_filters.__contains__("dates"):
daterange = helperlib.get_date_range(kw['startdate'],kw['enddate'])
if daterange > TWOYEARS:
date_sampling_filter = " DAYOFMONTH(timestamp) = '1' AND MONTH(timestamp) % 2 "
elif daterange > SIXMONTHS:
date_sampling_filter = " DAYOFMONTH(timestamp) = '1' "
elif daterange > THREEMONTHS:
date_sampling_filter = " DAYOFMONTH(timestamp) % '14' = 0 "
elif daterange > ONEMONTH:
date_sampling_filter = " DAYOFMONTH(timestamp) % '7' = 0 "
elif daterange > TWOWEEKS:
date_sampling_filter = " DAYOFMONTH(timestamp) % '3' = 0 "
elif daterange > ONEWEEK:
date_sampling_filter = " "
#must do special routines for hourly sampling
elif daterange > ONEDAY:
pass
else:
pass
filters = helperlib.fconstruct_helper(filters,date_sampling_filter)
#get columns and column transformations for this view
y_xforms = view_data[6].split(',')
y_data = view_data[2].split(',')
y_data_str = ""
y_group_str = ""
#number of y columns and y transformations must be equal
assert len(y_data) == len(y_xforms)
#we currently only support time as the x axis
assert view_data[1] == "timestamp"
for i in range(len(y_data)):
y_data_str += y_xforms[i] + "(" + y_data[i] + ")," + y_data[i] + ","
y_group_str += y_data[i] + ","
y_data_str = y_data_str[0:len(y_data_str)-1]
y_group_str = y_group_str[0:len(y_group_str)-1]
query = "SELECT CAST(timestamp as DATE),%s FROM %s %s GROUP BY CAST(timestamp as DATE),%s" % (y_data_str,tbl,filters,y_group_str)
log.debug("SQL query:")
log.debug(query)
results = helperlib.scaled_query(curs,tbl,query,100,evaluators.simple_eval)
#log.debug("query result:")
#log.debug(results)
data = LineGraphController.reformat_data(self,results)
#generate JS datafields here because genshi templating can't emit JS...
js_celldata = ""
js_columnnames = ""
dates = data[0].keys()
dates.sort()
for y in data[1]:
js_columnnames += "data.addColumn('number', '" + y + "');\n"
for i in range(len(dates)):
js_celldata += "data.setCell(" + str(i) + ", 0, '" + dates[i].__str__() + "');\n"
for j in range(len(data[0][dates[i]])):
js_celldata += "data.setCell(" + str(i) + ", " + str(j+1) + ", " + str(data[0][dates[i]][j][1]) + ");\n"
return dict(title=view_data[0],xlabel=view_data[3],js_celldata=js_celldata,
filters=available_filters,used_filters=used_filters,js_columnnames=js_columnnames,
ufilters_vals=ufilters_vals,fdata=fdata,numdates=len(dates),startdate=startdate,
enddate=enddate)