def aggregate_query(self):
# This query uses DISTINCT ON and ORDER BY to find the most recent form of each day
# inserted_at is used as a proxy for received_on or timeend as those are not in this table.
return """
INSERT INTO "{tablename}" (
SELECT DISTINCT ON (awc_id, submitted_on)
doc_id as doc_id,
awc_id as awc_id,
month as month,
submitted_on as pse_date,
awc_open_count as awc_open_count,
1 as count,
eligible_children as eligible_children,
attended_children as attended_children,
attended_children_percent as attended_children_percent,
form_location as form_location,
form_location_lat as form_location_lat,
form_location_long as form_location_long,
image_name as image_name,
pse_conducted as pse_conducted,
supervisor_id as supervisor_id,
state_id as state_id,
open_four_acts_count as open_4_acts_count,
open_one_acts_count as open_1_acts_count
FROM "{ucr_daily_attendance_tablename}"
WHERE month = %(start_month)s and (awc_open_count=1 OR awc_not_open = 1)
ORDER BY awc_id, submitted_on, inserted_at DESC
)
""".format(tablename=self.tablename,
ucr_daily_attendance_tablename=self.
ucr_daily_attendance_tablename), {
"start_month": date_to_string(self.month),
}
def aggregate_query(self):
return """
INSERT INTO "{tablename}" (
SELECT DISTINCT ON (awc_id, submitted_on)
doc_id as doc_id,
awc_id as awc_id,
month as month,
submitted_on as pse_date,
awc_open_count as awc_open_count,
1 as count,
eligible_children as eligible_children,
attended_children as attended_children,
attended_children_percent as attended_children_percent,
form_location as form_location,
form_location_lat as form_location_lat,
form_location_long as form_location_long,
image_name as image_name,
pse_conducted as pse_conducted,
supervisor_id as supervisor_id
FROM "{ucr_daily_attendance_tablename}"
WHERE month = %(start_month)s and (awc_open_count=1 OR awc_not_open = 1)
ORDER BY awc_id, submitted_on, inserted_at DESC
)
""".format(
tablename=self.tablename,
ucr_daily_attendance_tablename=self.ucr_daily_attendance_tablename
), {
"start_month": date_to_string(self.month),
}
def aggregate_query(self):
return """
INSERT INTO "{tablename}" (
SELECT DISTINCT ON (awc_id, submitted_on)
doc_id,
awc_id,
month,
submitted_on as pse_date,
awc_open_count,
1,
eligible_children,
attended_children,
attended_children_percent,
form_location,
form_location_lat,
form_location_long,
image_name,
pse_conducted
FROM "{ucr_daily_attendance_tablename}"
WHERE month = %(start_month)s
ORDER BY awc_id, submitted_on, inserted_at DESC
)
""".format(
tablename=self.tablename,
ucr_daily_attendance_tablename=self.ucr_daily_attendance_tablename
), {
"start_month": date_to_string(self.month),
}
def create_table_query(self):
return """
CREATE TABLE IF NOT EXISTS "{tablename}" (
CHECK (month = %(table_date)s)) INHERITS ("{parent_tablename}")
""".format(
tablename=self.tablename,
parent_tablename=self.base_tablename,
), {
"table_date": date_to_string(self.month)
}
def create_table_query(self):
return """
CREATE TABLE IF NOT EXISTS "{tablename}" (
CHECK (month = %(table_date)s)) INHERITS ("{parent_tablename}")
""".format(
tablename=self.tablename,
parent_tablename=self.base_tablename,
), {
"table_date": date_to_string(self.month)
}
def tablename(self):
return "{}_{}".format(self.base_tablename, date_to_string(self.month))
def tablename(self):
return "{}_{}".format(self.base_tablename, date_to_string(self.month))