def buildSubQuery(self, groupid, range=[None, time.strftime("%Y-%m-%d")], ignore_aggregates=False): # Parameters used in Subquery select Clause subParameters = self.returnParametersAsList(groupid) subGroupBy = self.returnGroupByAsList(groupid) # Required parameters for a subquery to do joins (date ranges are done internally) # Build Date Ranges for the where clause # Check DATES are valid, if not use default if not utils.is_valid_date(utils.xstr(range[0])): range[0] = "" if not utils.is_valid_date(utils.xstr(range[1])): range[1] = time.strftime("%Y-%m-%d") subDateRange = ( self.name + ".ds>='" + utils.xstr(range[0]).split(" ")[0] + "' and " + self.name + ".ds<='" + utils.xstr(range[1]).split(" ")[0] + "'" ) # Check if DATES have time, if so append server_date. if utils.has_valid_time(utils.xstr(range[0])): subDateRange += " and " + self.name + ".server_date>='" + utils.xstr(range[0]) + "'" if utils.has_valid_time(utils.xstr(range[1])): subDateRange += " and " + self.name + ".server_date<='" + utils.xstr(range[1]) + "'" subWhere = " where " + subDateRange if len(self.wheres) > 0: logicOp = " and " """for where in self.wheres: subWhere += logicOp subWhere += where logicOp = " or " """ for key in self.wheresMap.keys(): subWhere += logicOp counter = 1 subWhere += "(" for where in self.wheresMap[key]: logicOp = " or " subWhere += where if counter != len(self.wheresMap[key]): subWhere += logicOp counter += 1 subWhere += ")" logicOp = " and " query = "select " + subParameters + " from " + utils.xstr(self.name) + subWhere # if ignore aggregates is not nessesary then include the groupby parameters if self.ignore_aggregates == False: query = query + " group by " + subGroupBy return query
def __parseQueryDateRanges(self,range): if not utils.is_valid_date(utils.xstr(range[0])): range[0] = '' if not utils.is_valid_date(utils.xstr(range[1])): range[1] = time.strftime('%Y-%m-%d') if utils.has_valid_time(utils.xstr(range[0])): start_date, start_time = range[0].split(" ") else: start_date = range[0] start_time = '' if utils.has_valid_time(utils.xstr(range[1])): end_date, end_time = range[1].split(" ") else: end_date = range[1] end_time = '23:59:59' query = self.query query = query.replace("#STARTDATE",start_date) query = query.replace("#STARTTIME",start_time) query = query.replace("#ENDDATE",end_date) query = query.replace("#ENDTIME",end_time) self.query = query