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 tablename(self): return "{}_{}".format(self.base_tablename, date_to_string(self.month))