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
