예제 #1
0
	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)
예제 #2
0
    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)
예제 #3
0
    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)
예제 #4
0
	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)