def handle(self, domain, old_data_source_id, new_data_source_id, **options): old_config, _ = get_datasource_config(old_data_source_id, domain) new_config, _ = get_datasource_config(new_data_source_id, domain) assert old_config.referenced_doc_type == new_config.referenced_doc_type old_filter = old_config.get_case_type_or_xmlns_filter() new_filter = new_config.get_case_type_or_xmlns_filter() assert set(old_filter) == set(new_filter) old_adapter = get_indicator_adapter(old_config) new_adapter = get_indicator_adapter(new_config) old_table = old_adapter.get_table() new_table = new_adapter.get_table() assert hasattr(old_table.columns, options['date_column']) column = getattr(old_table.columns, options['date_column']) new_adapter.build_table(initiated_by=options['initiated'], source='migrate_ucr') end_date = date(2016, 1, 1) query = self.insert_query(old_table, new_table, column, end_date=end_date) self.run_query(new_adapter, query) start_date = end_date end_date = end_date + relativedelta(months=1) while start_date < date.today(): query = self.insert_query(old_table, new_table, column, start_date, end_date) self.run_query(new_adapter, query) start_date += relativedelta(months=1) end_date += relativedelta(months=1) query = self.insert_query(old_table, new_table, column, start_date) self.run_query(new_adapter, query)
def agg_from_child_health_case_ucr(cls, domain, window_start, window_end): doc_id = StaticDataSourceConfiguration.get_doc_id(domain, 'reach-child_health_cases') config, _ = get_datasource_config(doc_id, domain) ucr_tablename = get_table_name(domain, config.table_id) return """ INSERT INTO "{child_tablename}" AS child ( domain, person_case_id, child_health_case_id, mother_case_id, opened_on, closed_on, birth_weight, breastfed_within_first, is_exclusive_breastfeeding, comp_feeding, diet_diversity, diet_quantity, hand_wash ) ( SELECT %(domain)s AS domain, person_case_id, doc_id, mother_case_id, opened_on, closed_on, birth_weight, breastfed_within_first, is_exclusive_breastfeeding, comp_feeding, diet_diversity, diet_quantity, hand_wash FROM "{child_health_cases_ucr_tablename}" child_health ) ON CONFLICT (child_health_case_id) DO UPDATE SET mother_case_id = EXCLUDED.mother_case_id, closed_on = EXCLUDED.closed_on, birth_weight = EXCLUDED.birth_weight, breastfed_within_first = EXCLUDED.breastfed_within_first, is_exclusive_breastfeeding = EXCLUDED.is_exclusive_breastfeeding, comp_feeding = EXCLUDED.comp_feeding, diet_diversity = EXCLUDED.diet_diversity, diet_quantity = EXCLUDED.diet_quantity, hand_wash = EXCLUDED.hand_wash """.format( child_tablename=cls._meta.db_table, child_health_cases_ucr_tablename=ucr_tablename, ), {'domain': domain, 'window_start': window_start, 'window_end': window_end}
def handle(self, domain, case_type, data_source_ids, **options): configs = [] for data_source_id in data_source_ids: config, _ = get_datasource_config(data_source_id, domain) assert config.asynchronous assert config.referenced_doc_type == CASE_DOC_TYPE configs.append(config) fake_change_doc = {'doc_type': CASE_DOC_TYPE, 'domain': domain} doc_store = get_document_store(domain, CASE_DOC_TYPE) case_accessor = doc_store.case_accessors case_ids = case_accessor.get_case_ids_in_domain(type=case_type) num_case_ids = len(case_ids) print("inserting %d docs" % num_case_ids) for config in configs: adapter = get_indicator_adapter(config, can_handle_laboratory=True) adapter.build_table() # normally called after rebuilding finishes adapter.after_table_build() config_ids = [config._id for config in configs] for i, case_id in enumerate(case_ids): change = FakeChange(case_id, fake_change_doc) AsyncIndicator.update_indicators(change, config_ids) if i % 1000 == 0: print("inserted %d / %d docs" % (i, num_case_ids))
def agg_from_eligible_couple_forms_ucr(cls, domain, window_start, window_end): doc_id = StaticDataSourceConfiguration.get_doc_id(domain, 'reach-eligible_couple_forms') config, _ = get_datasource_config(doc_id, domain) ucr_tablename = get_table_name(domain, config.table_id) return """ INSERT INTO "{woman_history_tablename}" AS woman ( person_case_id, fp_current_method_history, fp_preferred_method_history, family_planning_form_history ) ( SELECT person_case_id, array_agg(fp_current_method) AS fp_current_method_history, array_agg(fp_preferred_method) AS fp_preferred_method_history, array_agg(timeend) AS family_planning_form_history FROM ( SELECT person_case_id, timeend, ARRAY[timeend::text, fp_current_method] AS fp_current_method, ARRAY[timeend::text, fp_preferred_method] AS fp_preferred_method FROM "{eligible_couple_ucr_tablename}" ) eligible_couple GROUP BY person_case_id ) ON CONFLICT (person_case_id) DO UPDATE SET fp_current_method_history = EXCLUDED.fp_current_method_history, fp_preferred_method_history = EXCLUDED.fp_preferred_method_history, family_planning_form_history = EXCLUDED.family_planning_form_history """.format( woman_history_tablename=cls._meta.db_table, eligible_couple_ucr_tablename=ucr_tablename, ), {'domain': domain, 'window_start': window_start, 'window_end': window_end}
def agg_from_eligible_couple_forms_ucr(cls, domain, window_start, window_end): doc_id = StaticDataSourceConfiguration.get_doc_id(domain, 'reach-eligible_couple_forms') config, _ = get_datasource_config(doc_id, domain) ucr_tablename = get_table_name(domain, config.table_id) return """ UPDATE "{woman_tablename}" AS woman SET fp_current_method_ranges = eligible_couple_fp.fp_current_method_ranges FROM ( SELECT person_case_id, array_agg(fp_current_method_range) AS fp_current_method_ranges FROM ( SELECT person_case_id, fp_current_method, daterange(timeend::date, next_timeend::date) AS fp_current_method_range FROM ( SELECT person_case_id, fp_current_method, timeend, LEAD(fp_current_method) OVER w AS next_fp_current_method, LEAD(timeend) OVER w AS next_timeend FROM "{eligible_couple_ucr_tablename}" WINDOW w AS (PARTITION BY person_case_id ORDER BY timeend DESC) ) AS _tmp_table ) eligible_couple WHERE fp_current_method != 'none' GROUP BY person_case_id ) AS eligible_couple_fp WHERE woman.person_case_id = eligible_couple_fp.person_case_id """.format( woman_tablename=cls._meta.db_table, eligible_couple_ucr_tablename=ucr_tablename, ), {'domain': domain, 'window_start': window_start, 'window_end': window_end}
def recalculate_stagnant_cases(): domain = 'icds-cas' config_ids = [ 'static-icds-cas-static-ccs_record_cases_monthly_v2', 'static-icds-cas-static-ccs_record_cases_monthly_tableau_v2', 'static-icds-cas-static-child_cases_monthly_v2', ] stagnant_cases = set() for config_id in config_ids: config, is_static = get_datasource_config(config_id, domain) adapter = get_indicator_adapter(config) case_ids = _find_stagnant_cases(adapter) celery_task_logger.info( "Found {} stagnant cases in config {}".format(len(case_ids), config_id) ) stagnant_cases = stagnant_cases.union(set(case_ids)) celery_task_logger.info( "Total number of stagant cases is now {}".format(len(stagnant_cases)) ) case_accessor = CaseAccessors(domain) num_stagnant_cases = len(stagnant_cases) current_case_num = 0 for case_ids in chunked(stagnant_cases, 1000): current_case_num += len(case_ids) cases = case_accessor.get_cases(list(case_ids)) for case in cases: publish_case_saved(case, send_post_save_signal=False) celery_task_logger.info( "Resaved {} / {} cases".format(current_case_num, num_stagnant_cases) )
def agg_from_tasks_case_ucr(cls, domain, window_start, window_end): doc_id = StaticDataSourceConfiguration.get_doc_id(domain, 'reach-tasks_cases') config, _ = get_datasource_config(doc_id, domain) ucr_tablename = get_table_name(domain, config.table_id) product_codes = ', '.join("'{}'".format(code) for code in PRODUCT_CODES) column_names = ', '.join('due_list_date_{}'.format(code) for code in PRODUCT_CODES) return """ UPDATE "{child_tablename}" AS child SET tasks_case_id = tasks.doc_id, last_immunization_type = tasks.last_immunization_type, last_immunization_date = tasks.last_immunization_date FROM ( SELECT doc_id AS doc_id, parent_case_id AS parent_case_id, LAST_VALUE(product_code) OVER w AS last_immunization_type, LAST_VALUE(product_date) OVER w AS last_immunization_date FROM ( SELECT doc_id, parent_case_id, unnest(array[{product_codes}]) AS product_code, unnest(array[{column_names}]) AS product_date FROM "{tasks_cases_ucr_tablename}" WHERE tasks_type = 'child' ) AS _tasks WHERE product_date != '1970-01-01' WINDOW w AS (PARTITION BY doc_id, parent_case_id ORDER BY product_date DESC) ) tasks WHERE child.child_health_case_id = tasks.parent_case_id """.format( child_tablename=cls._meta.db_table, tasks_cases_ucr_tablename=ucr_tablename, product_codes=product_codes, column_names=column_names, ), {'domain': domain, 'window_start': window_start, 'window_end': window_end}
def agg_from_person_case_ucr(cls, domain, window_start, window_end): doc_id = StaticDataSourceConfiguration.get_doc_id( domain, 'reach-person_cases') config, _ = get_datasource_config(doc_id, domain) ucr_tablename = get_table_name(domain, config.table_id) return """ UPDATE "{child_tablename}" AS child SET household_case_id = person.household_case_id, dob = person.dob, sex = person.sex, migration_status = person.migration_status, has_aadhar_number = person.has_aadhar_number, contact_phone_number = person.contact_phone_number FROM ( SELECT household_case_id, doc_id, dob, sex, migration_status, aadhar_number IS NOT NULL and aadhar_number != '' AS has_aadhar_number, contact_phone_number FROM "{person_cases_ucr_tablename}" ) person WHERE child.person_case_id = person.doc_id """.format( child_tablename=cls._meta.db_table, person_cases_ucr_tablename=ucr_tablename, ), { 'domain': domain, 'window_start': window_start, 'window_end': window_end }
def _get_config(self): config, _ = get_datasource_config(self.ucr_name, self.domain) config.configured_indicators = [ ind for ind in config.configured_indicators if ind['column_id'] not in LOCATION_COLUMNS ] return config
def recalculate_stagnant_cases(): domain = 'icds-cas' config_ids = [ 'static-icds-cas-static-ccs_record_cases_monthly_v2', 'static-icds-cas-static-ccs_record_cases_monthly_tableau_v2', 'static-icds-cas-static-child_cases_monthly_v2', 'static-icds-cas-static-child_cases_monthly_tableau_v2', ] stagnant_cases = set() for config_id in config_ids: config, is_static = get_datasource_config(config_id, domain) adapter = get_indicator_adapter(config) case_ids = _find_stagnant_cases(adapter) celery_task_logger.info("Found {} stagnant cases in config {}".format( len(case_ids), config_id)) stagnant_cases = stagnant_cases.union(set(case_ids)) celery_task_logger.info( "Total number of stagant cases is now {}".format( len(stagnant_cases))) case_accessor = CaseAccessors(domain) num_stagnant_cases = len(stagnant_cases) current_case_num = 0 for case_ids in chunked(stagnant_cases, 1000): current_case_num += len(case_ids) cases = case_accessor.get_cases(list(case_ids)) for case in cases: publish_case_saved(case, send_post_save_signal=False) celery_task_logger.info("Resaved {} / {} cases".format( current_case_num, num_stagnant_cases))
def agg_from_delivery_forms_ucr(cls, domain, window_start, window_end): doc_id = StaticDataSourceConfiguration.get_doc_id( domain, 'reach-delivery_forms') config, _ = get_datasource_config(doc_id, domain) ucr_tablename = get_table_name(domain, config.table_id) return """ UPDATE "{child_tablename}" AS child SET ccs_record_case_id = delivery_forms.ccs_record_case_id, child_cried = delivery_forms.child_cried FROM ( SELECT child_health_case_id, LAST_VALUE(ccs_record_case_id) OVER w AS ccs_record_case_id, LAST_VALUE(child_cried) OVER w as child_cried FROM "{delivery_form_ucr_tablename}" WINDOW w AS (PARTITION BY child_health_case_id ORDER BY timeend DESC) ) AS delivery_forms WHERE child.child_health_case_id = delivery_forms.child_health_case_id """.format( child_tablename=cls._meta.db_table, delivery_form_ucr_tablename=ucr_tablename, ), { 'domain': domain, 'window_start': window_start, 'window_end': window_end }
def handle(self, domain, data_source_id, *args, **kwargs): config, _ = get_datasource_config(data_source_id, domain) adapter = get_indicator_adapter(config) q = adapter.get_query_object() document_store = get_document_store_for_doc_type(domain, config.referenced_doc_type) bad_rows = [] for row in with_progress_bar(q, length=q.count()): doc_id = row.doc_id doc = document_store.get_document(doc_id) current_rows = config.get_all_values(doc) if len(current_rows) > 1: raise ValueError("this command doesn't work for datasources returning multiple rows per doc") try: current_row = current_rows[0] except KeyError: continue # don't compare the 'inserted_at' columns current_row = [val for val in current_row if val.column.database_column_name != 'inserted_at'] for val in current_row: try: inserted_value = getattr(row, val.column.database_column_name) if (inserted_value != val.value or row.inserted_at.replace(tzinfo=pytz.utc) < parse_datetime(doc['server_modified_on'])): bad_rows.append({ 'doc_id': row.doc_id, 'column_name': val.column.database_column_name, 'inserted_at': row.inserted_at.isoformat(), 'server_modified_on': doc['server_modified_on'], 'stored_value': getattr(row, val.column.database_column_name), 'desired_value': val.value, 'message': ('column mismatch' if inserted_value != val.value else "modified date early"), }) except AttributeError: bad_rows.append({ 'doc_id': row.doc_id, 'column_name': val.column.database_column_name, 'inserted_at': 'missing', 'server_modified_on': doc['server_modified_on'], 'stored_value': 'missing', 'desired_value': val.value, 'message': 'doc missing', }) filename = 'datasource_mismatches_{}_{}.csv'.format( data_source_id[-8:], datetime.utcnow().strftime("%Y-%m-%d-%H-%M-%S") ) with open(filename, 'w', encoding='utf-8') as f: headers = ['doc_id', 'column_name', 'inserted_at', 'server_modified_on', 'stored_value', 'desired_value', 'message'] writer = csv.DictWriter(f, headers) writer.writeheader() writer.writerows(bad_rows) print("Found {} mismatches. Check {} for more details".format(len(bad_rows), filename))
def handle(self, domain, type_, case_type_or_xmlns, data_source_ids, **options): assert type_ in ('xform', 'case') self.referenced_type = CASE_DOC_TYPE if type_ == 'case' else XFORM_DOC_TYPE configs = [] for data_source_id in data_source_ids: config, _ = get_datasource_config(data_source_id, domain) assert config.asynchronous assert config.referenced_doc_type == self.referenced_type configs.append(config) for config in configs: adapter = get_indicator_adapter(config) adapter.build_table() self.domain = domain self.case_type_or_xmlns = case_type_or_xmlns self.bulk = options['bulk'] self.database = options['database'] self.config_ids = [config._id for config in configs] ids = [] for id_ in self._get_ids_to_process(): ids.append(id_) if len(ids) > 999: self._save_ids(ids) ids = [] self._save_ids(ids) for config in configs: if not config.is_static: config.meta.build.rebuilt_asynchronously = True config.save()
def agg_from_eligible_couple_forms_ucr(cls, domain, window_start, window_end): doc_id = StaticDataSourceConfiguration.get_doc_id(domain, 'reach-eligible_couple_forms') config, _ = get_datasource_config(doc_id, domain) ucr_tablename = get_table_name(domain, config.table_id) return """ UPDATE "{woman_tablename}" AS woman SET fp_current_method_ranges = eligible_couple_fp.fp_current_method_ranges FROM ( SELECT person_case_id, array_agg(fp_current_method_range) AS fp_current_method_ranges FROM ( SELECT person_case_id, fp_current_method, daterange(timeend::date, next_timeend::date) AS fp_current_method_range FROM ( SELECT person_case_id, fp_current_method, timeend, LEAD(fp_current_method) OVER w AS next_fp_current_method, LEAD(timeend) OVER w AS next_timeend FROM "{eligible_couple_ucr_tablename}" WINDOW w AS (PARTITION BY person_case_id ORDER BY timeend ASC) ) AS _tmp_table ) eligible_couple WHERE fp_current_method != 'none' GROUP BY person_case_id ) AS eligible_couple_fp WHERE woman.person_case_id = eligible_couple_fp.person_case_id """.format( woman_tablename=cls._meta.db_table, eligible_couple_ucr_tablename=ucr_tablename, ), {'domain': domain, 'window_start': window_start, 'window_end': window_end}
def agg_from_household_case_ucr(cls, domain, window_start, window_end): doc_id = StaticDataSourceConfiguration.get_doc_id(domain, 'reach-household_cases') config, _ = get_datasource_config(doc_id, domain) ucr_tablename = get_table_name(domain, config.table_id) return """ UPDATE "{child_tablename}" AS child SET awc_id = household.awc_owner_id, village_id = household.village_owner_id, hh_address = household.hh_address, hh_religion = household.hh_religion, hh_caste = household.hh_caste, hh_bpl_apl = household.hh_bpl_apl FROM ( SELECT doc_id, awc_owner_id, village_owner_id, hh_address, hh_religion, hh_caste, hh_bpl_apl FROM "{household_cases_ucr_tablename}" ) household WHERE child.household_case_id = household.doc_id """.format( child_tablename=cls._meta.db_table, household_cases_ucr_tablename=ucr_tablename, ), {'domain': domain, 'window_start': window_start, 'window_end': window_end}
def delete_report(request, domain, report_id): if not (toggle_enabled(request, toggles.USER_CONFIGURABLE_REPORTS) or toggle_enabled(request, toggles.REPORT_BUILDER) or toggle_enabled(request, toggles.REPORT_BUILDER_BETA_GROUP) or has_report_builder_add_on_privilege(request)): raise Http404() config = get_document_or_404(ReportConfiguration, domain, report_id) # Delete the data source too if it's not being used by any other reports. try: data_source, __ = get_datasource_config(config.config_id, domain) except DataSourceConfigurationNotFoundError: # It's possible the data source has already been deleted, but that's fine with us. pass else: if data_source.get_report_count() <= 1: # No other reports reference this data source. data_source.deactivate() config.delete() did_purge_something = purge_report_from_mobile_ucr(config) messages.success(request, _(u'Report "{}" deleted!').format(config.title)) if did_purge_something: messages.warning( request, _(u"This report was used in one or more applications. " "It has been removed from there too.") ) redirect = request.GET.get("redirect", None) if not redirect: redirect = reverse('configurable_reports_home', args=[domain]) return HttpResponseRedirect(redirect)
def handle(self, domain, type_, case_type_or_xmlns, data_source_ids, **options): assert type_ in ('xform', 'case') self.referenced_type = CASE_DOC_TYPE if type_ == 'case' else XFORM_DOC_TYPE configs = [] for data_source_id in data_source_ids: config, _ = get_datasource_config(data_source_id, domain) assert config.asynchronous assert config.referenced_doc_type == self.referenced_type configs.append(config) for config in configs: adapter = get_indicator_adapter(config) adapter.build_table(initiated_by=options['initiated'], source='async_rebuild_table') self.domain = domain self.case_type_or_xmlns = case_type_or_xmlns self.bulk = options['bulk'] self.database = options['database'] self.config_ids = [config._id for config in configs] ids = [] for id_ in self._get_ids_to_process(): ids.append(id_) if len(ids) > 999: self._save_ids(ids) ids = [] self._save_ids(ids) for config in configs: if not config.is_static: config.meta.build.rebuilt_asynchronously = True config.save()
def agg_from_person_case_ucr(cls, domain, window_start, window_end): doc_id = StaticDataSourceConfiguration.get_doc_id(domain, 'reach-person_cases') config, _ = get_datasource_config(doc_id, domain) ucr_tablename = get_table_name(domain, config.table_id) return """ UPDATE "{child_tablename}" AS child SET household_case_id = person.household_case_id, dob = person.dob, sex = person.sex, name = person.name, migration_status = person.migration_status, has_aadhar_number = person.has_aadhar_number, contact_phone_number = person.contact_phone_number FROM ( SELECT household_case_id, doc_id, dob, sex, name, migration_status, aadhar_number IS NOT NULL and aadhar_number != '' AS has_aadhar_number, contact_phone_number FROM "{person_cases_ucr_tablename}" ) person WHERE child.person_case_id = person.doc_id """.format( child_tablename=cls._meta.db_table, person_cases_ucr_tablename=ucr_tablename, ), {'domain': domain, 'window_start': window_start, 'window_end': window_end}
def handle(self, domain, case_type, data_source_ids, **options): configs = [] for data_source_id in data_source_ids: config, _ = get_datasource_config(data_source_id, domain) assert config.asynchronous assert config.referenced_doc_type == CASE_DOC_TYPE configs.append(config) fake_change_doc = {'doc_type': CASE_DOC_TYPE, 'domain': domain} for config in configs: adapter = get_indicator_adapter(config, can_handle_laboratory=True) adapter.build_table() # normally called after rebuilding finishes adapter.after_table_build() self.domain = domain self.case_type = case_type config_ids = [config._id for config in configs] for case_id in self._get_case_ids_to_process(): change = FakeChange(case_id, fake_change_doc) AsyncIndicator.update_from_kafka_change(change, config_ids) for config in configs: if not config.is_static: config.meta.build.rebuilt_asynchronously = True config.save()
def agg_from_village_ucr(cls, domain, window_start, window_end): doc_id = StaticDataSourceConfiguration.get_doc_id( domain, 'reach-village_location') config, _ = get_datasource_config(doc_id, domain) ucr_tablename = get_table_name(domain, config.table_id) return """ UPDATE "{child_tablename}" AS child SET sc_id = village.sc_id, phc_id = village.phc_id, taluka_id = village.taluka_id, district_id = village.district_id, state_id = village.state_id FROM ( SELECT doc_id, sc_id, phc_id, taluka_id, district_id, state_id FROM "{village_location_ucr_tablename}" ) village WHERE child.village_id = village.doc_id """.format( child_tablename=cls._meta.db_table, village_location_ucr_tablename=ucr_tablename, ), { 'domain': domain, 'window_start': window_start, 'window_end': window_end }
def agg_from_household_case_ucr(cls, domain, window_start, window_end): doc_id = StaticDataSourceConfiguration.get_doc_id( domain, 'reach-household_cases') config, _ = get_datasource_config(doc_id, domain) ucr_tablename = get_table_name(domain, config.table_id) return """ UPDATE "{child_tablename}" AS child SET awc_id = household.awc_owner_id, village_id = household.village_owner_id, hh_address = household.hh_address, hh_religion = household.hh_religion, hh_caste = household.hh_caste, hh_bpl_apl = household.hh_bpl_apl FROM ( SELECT doc_id, awc_owner_id, village_owner_id, hh_address, hh_religion, hh_caste, hh_bpl_apl FROM "{household_cases_ucr_tablename}" ) household WHERE child.household_case_id = household.doc_id """.format( child_tablename=cls._meta.db_table, household_cases_ucr_tablename=ucr_tablename, ), { 'domain': domain, 'window_start': window_start, 'window_end': window_end }
def agg_from_ccs_record_case_ucr(cls, domain, window_start, window_end): doc_id = StaticDataSourceConfiguration.get_doc_id( domain, 'reach-ccs_record_cases') config, _ = get_datasource_config(doc_id, domain) ucr_tablename = get_table_name(domain, config.table_id) return """ UPDATE "{woman_tablename}" AS woman SET pregnant_ranges = ccs_record.pregnant_ranges FROM ( SELECT person_case_id, array_agg(pregnant_range) as pregnant_ranges FROM( SELECT person_case_id, daterange(opened_on::date, add, '[]') as pregnant_range FROM "{ccs_record_cases_ucr_tablename}" WHERE opened_on < add OR add IS NULL GROUP BY person_case_id, pregnant_range ) AS _tmp_table GROUP BY person_case_id ) ccs_record WHERE woman.person_case_id = ccs_record.person_case_id """.format( woman_tablename=cls._meta.db_table, ccs_record_cases_ucr_tablename=ucr_tablename, ), { 'domain': domain, 'window_start': window_start, 'window_end': window_end }
def delete_report(request, domain, report_id): if not (toggle_enabled(request, toggles.USER_CONFIGURABLE_REPORTS) or toggle_enabled(request, toggles.REPORT_BUILDER) or toggle_enabled(request, toggles.REPORT_BUILDER_BETA_GROUP) or has_report_builder_add_on_privilege(request)): raise Http404() config = get_document_or_404(ReportConfiguration, domain, report_id) # Delete the data source too if it's not being used by any other reports. try: data_source, __ = get_datasource_config(config.config_id, domain) except DataSourceConfigurationNotFoundError: # It's possible the data source has already been deleted, but that's fine with us. pass else: if data_source.get_report_count() <= 1: # No other reports reference this data source. data_source.deactivate() config.delete() did_purge_something = purge_report_from_mobile_ucr(config) messages.success(request, _(u'Report "{}" deleted!').format(config.title)) if did_purge_something: messages.warning( request, _(u"This report was used in one or more applications. " "It has been removed from there too.")) redirect = request.GET.get("redirect", None) if not redirect: redirect = reverse('configurable_reports_home', args=[domain]) return HttpResponseRedirect(redirect)
def agg_from_person_case_ucr(cls, domain, window_start, window_end): doc_id = StaticDataSourceConfiguration.get_doc_id( domain, 'reach-person_cases') config, _ = get_datasource_config(doc_id, domain) ucr_tablename = get_table_name(domain, config.table_id) return """ INSERT INTO "{woman_tablename}" AS child ( domain, household_case_id, person_case_id, opened_on, closed_on, name, dob, marital_status, sex, migration_status, age_marriage, has_aadhar_number, husband_name, contact_phone_number, num_male_children_died, num_female_children_died, blood_group ) ( SELECT %(domain)s, household_case_id, doc_id, opened_on, closed_on, name, dob, marital_status, sex, migration_status, age_marriage, aadhar_number IS NOT NULL and aadhar_number != '' AS has_aadhar_number, husband_name, contact_phone_number, num_male_children_died, num_female_children_died, blood_group FROM "{person_cases_ucr_tablename}" person WHERE sex = 'F' AND date_part('year', age(dob)) BETWEEN 15 AND 49 ) ON CONFLICT (person_case_id) DO UPDATE SET closed_on = EXCLUDED.closed_on, name = EXCLUDED.name, dob = EXCLUDED.dob, marital_status = EXCLUDED.marital_status, sex = EXCLUDED.sex, migration_status = EXCLUDED.migration_status, age_marriage = EXCLUDED.age_marriage, has_aadhar_number = EXCLUDED.has_aadhar_number, husband_name = EXCLUDED.husband_name, contact_phone_number = EXCLUDED.contact_phone_number, num_male_children_died = EXCLUDED.num_male_children_died, num_female_children_died = EXCLUDED.num_female_children_died, blood_group = EXCLUDED.blood_group """.format( woman_tablename=cls._meta.db_table, person_cases_ucr_tablename=ucr_tablename, ), { 'domain': domain, 'window_start': window_start, 'window_end': window_end }
def handle(self, domain, data_source_id, doc_id, **options): config, _ = get_datasource_config(data_source_id, domain) doc_type = config.referenced_doc_type doc_store = get_document_store_for_doc_type(domain, doc_type) doc = doc_store.get_document(doc_id) sort_by = options['sort'] local_variables = {'config': config, 'doc': doc} cProfile.runctx('config.get_all_values(doc)', {}, local_variables, 'ucr_stats.log') print_profile_stats('ucr_stats.log', sort_by)
def handle(self, domain, data_source_id, doc_id, **options): config, _ = get_datasource_config(data_source_id, domain) doc_type = config.referenced_doc_type doc_store = get_document_store(domain, doc_type) doc = doc_store.get_document(doc_id) sort_by = options['sort'] local_variables = {'config': config, 'doc': doc} cProfile.runctx('config.get_all_values(doc)', {}, local_variables, 'ucr_stats.log') print_profile_stats('ucr_stats.log', sort_by)
def handle(self, *args, **options): fake_change_doc = {'doc_type': CASE_DOC_TYPE, 'domain': DOMAIN} for data_source_id in DATA_SOURCES: print("processing data source %s" % data_source_id) data_source, is_static = get_datasource_config(data_source_id, DOMAIN) assert is_static adapter = get_indicator_adapter(data_source) table = adapter.get_table() for case_id in self._get_case_ids_to_process(adapter, table, data_source_id): change = FakeChange(case_id, fake_change_doc) AsyncIndicator.update_from_kafka_change(change, [data_source_id])
def handle(self, case_type, **options): # get the configurations for the case type db_config = DB_CONFIGS[case_type] old_config, _ = get_datasource_config(db_config.old_config_id, DOMAIN) new_config, _ = get_datasource_config(db_config.new_config_id, DOMAIN) old_columns = [c.id for c in old_config.get_columns()] new_columns = [c.id for c in new_config.get_columns()] # verify that the old columns are subset of the new columns # new columns are allowed to be null assert set(old_columns).issubset(set(new_columns)) self.old_table = db_config.old_table self.new_table = db_config.new_table # create a column string quoted_columns = [] for col in old_columns: if col.islower(): quoted_columns.append('%s' % col) else: quoted_columns.append('"%s"' % col) self.column_string = ', '.join(quoted_columns) self.select_column_string = ', '.join( ['A.' + col for col in quoted_columns]) sql_command = self._sql_command(db_config.has_repeat_iteration) if options['dry_run']: print(sql_command) else: print("count of old table %s: " % self.old_table) print(self.get_count(self.old_table)) print("count of new table %s: " % self.new_table) print(self.get_count(self.new_table)) with connections['icds-ucr'].cursor() as cursor: cursor.execute(sql_command) print("count of new table %s: " % self.new_table) print(self.get_count(self.new_table))
def agg_from_person_case_ucr(cls, domain, window_start, window_end): doc_id = StaticDataSourceConfiguration.get_doc_id(domain, 'reach-person_cases') config, _ = get_datasource_config(doc_id, domain) ucr_tablename = get_table_name(domain, config.table_id) return """ INSERT INTO "{woman_tablename}" AS child ( domain, household_case_id, person_case_id, opened_on, closed_on, name, dob, marital_status, sex, migration_status, age_marriage, has_aadhar_number, husband_name, contact_phone_number, num_male_children_died, num_female_children_died, blood_group ) ( SELECT %(domain)s, household_case_id, doc_id, opened_on, closed_on, name, dob, marital_status, sex, migration_status, age_marriage, aadhar_number IS NOT NULL and aadhar_number != '' AS has_aadhar_number, husband_name, contact_phone_number, num_male_children_died, num_female_children_died, blood_group FROM "{person_cases_ucr_tablename}" person WHERE sex = 'F' AND date_part('year', age(dob)) BETWEEN 15 AND 49 ) ON CONFLICT (person_case_id) DO UPDATE SET closed_on = EXCLUDED.closed_on, name = EXCLUDED.name, dob = EXCLUDED.dob, marital_status = EXCLUDED.marital_status, sex = EXCLUDED.sex, migration_status = EXCLUDED.migration_status, age_marriage = EXCLUDED.age_marriage, has_aadhar_number = EXCLUDED.has_aadhar_number, husband_name = EXCLUDED.husband_name, contact_phone_number = EXCLUDED.contact_phone_number, num_male_children_died = EXCLUDED.num_male_children_died, num_female_children_died = EXCLUDED.num_female_children_died, blood_group = EXCLUDED.blood_group """.format( woman_tablename=cls._meta.db_table, person_cases_ucr_tablename=ucr_tablename, ), {'domain': domain, 'window_start': window_start, 'window_end': window_end}
def handle(self, *args, **options): data_source, is_static = get_datasource_config(DATA_SOURCE_NAME, DOMAIN) assert is_static adapter = get_indicator_adapter(data_source) table = adapter.get_table() for state_id in STATE_IDS: null_query = adapter.session_helper.Session.query(table.columns.doc_id) null_query = null_query.filter( table.columns.state_id == state_id, table.columns.aadhar_date == None ).all() AsyncIndicator.objects.bulk_create([ create_async_indicator(doc_id[0]) for doc_id in null_query ], batch_size=500)
def handle(self, *args, **options): data_source, is_static = get_datasource_config(DATA_SOURCE_NAME, DOMAIN) assert is_static adapter = get_indicator_adapter(data_source) table = adapter.get_table() for state_id in STATE_IDS: null_query = adapter.session_helper.Session.query( table.columns.doc_id) null_query = null_query.filter( table.columns.state_id == state_id, table.columns.aadhar_date == None).all() AsyncIndicator.objects.bulk_create( [create_async_indicator(doc_id[0]) for doc_id in null_query], batch_size=500)
def agg_from_person_case_ucr(cls, domain, window_start, window_end): doc_id = StaticDataSourceConfiguration.get_doc_id(domain, 'reach-person_cases') config, _ = get_datasource_config(doc_id, domain) ucr_tablename = get_table_name(domain, config.table_id) return """ UPDATE "{child_tablename}" AS child SET household_case_id = person.household_case_id FROM ( SELECT doc_id, household_case_id FROM "{person_cases_ucr_tablename}" ) person WHERE child.person_case_id = person.doc_id """.format( child_tablename=cls._meta.db_table, person_cases_ucr_tablename=ucr_tablename, ), {'domain': domain, 'window_start': window_start, 'window_end': window_end}
def agg_from_awc_ucr(cls, domain, window_start, window_end): doc_id = StaticDataSourceConfiguration.get_doc_id(domain, 'reach-awc_location') config, _ = get_datasource_config(doc_id, domain) ucr_tablename = get_table_name(domain, config.table_id) return """ UPDATE "{child_tablename}" AS child SET supervisor_id = awc.supervisor_id, block_id = awc.block_id FROM ( SELECT doc_id, supervisor_id, block_id FROM "{awc_location_ucr_tablename}" ) awc WHERE child.awc_id = awc.doc_id """.format( child_tablename=cls._meta.db_table, awc_location_ucr_tablename=ucr_tablename, ), {'domain': domain, 'window_start': window_start, 'window_end': window_end}
def agg_from_ccs_record_case_ucr(cls, domain, window_start, window_end): doc_id = StaticDataSourceConfiguration.get_doc_id(domain, 'reach-ccs_record_cases') config, _ = get_datasource_config(doc_id, domain) ucr_tablename = get_table_name(domain, config.table_id) return """ INSERT INTO "{ccs_record_tablename}" AS ccs_record ( domain, person_case_id, ccs_record_case_id, opened_on, closed_on, hrp, child_birth_location, add, edd, lmp, preg_reg_date, woman_weight_at_preg_reg, pnc1_date, pnc2_date, pnc3_date, pnc4_date ) ( SELECT %(domain)s, person_case_id, doc_id, opened_on, closed_on, hrp, child_birth_location, add, edd, lmp, preg_reg_date, woman_weight_at_preg_reg, pnc1_date, pnc2_date, pnc3_date, pnc4_date FROM "{ccs_record_cases_ucr_tablename}" ccs_record_ucr ) ON CONFLICT (ccs_record_case_id) DO UPDATE SET closed_on = EXCLUDED.closed_on, hrp = EXCLUDED.hrp, child_birth_location = EXCLUDED.child_birth_location, add = EXCLUDED.add, edd = EXCLUDED.edd, lmp = EXCLUDED.lmp, preg_reg_date = EXCLUDED.preg_reg_date, woman_weight_at_preg_reg = EXCLUDED.woman_weight_at_preg_reg, pnc1_date = EXCLUDED.pnc1_date, pnc2_date = EXCLUDED.pnc2_date, pnc3_date = EXCLUDED.pnc3_date, pnc4_date = EXCLUDED.pnc4_date """.format( ccs_record_tablename=cls._meta.db_table, ccs_record_cases_ucr_tablename=ucr_tablename, ), {'domain': domain, 'window_start': window_start, 'window_end': window_end}
def test_pnc_form(self): config, _ = get_datasource_config(self.ucr_name, 'icds-cas') config.configured_indicators = [ ind for ind in config.configured_indicators if ind['column_id'] != 'state_id' ] form_json = convert_xform_to_json(self.get_xml('pnc_form_v10326')) form_json = { 'form': form_json, 'domain': self.domain, 'xmlns': form_json['@xmlns'], 'doc_type': 'XFormInstance', } ucr_result = config.get_all_values(form_json) for row in ucr_result: row = { i.column.database_column_name: i.value for i in row if i.column.database_column_name not in BLACKLISTED_COLUMNS } self.assertEqual( { "doc_id": None, "repeat_iteration": 0, "timeend": None, "ccs_record_case_id": "081cc405-5598-430f-ac8f-39cc4a1fdb30", "child_health_case_id": "252d8e20-c698-4c94-a5a9-53bbf8972b64", "counsel_adequate_bf": None, "counsel_breast": None, "counsel_exclusive_bf": 1, "counsel_increase_food_bf": 1, "counsel_methods": None, "counsel_only_milk": None, "skin_to_skin": None, "is_ebf": 1, "water_or_milk": None, "other_milk_to_child": 0, "tea_other": None, "eating": None, "not_breastfeeding": None }, row)
def test_ebf_form(self): config, _ = get_datasource_config(self.ucr_name, 'icds-cas') config.configured_indicators = [ ind for ind in config.configured_indicators if ind['column_id'] != 'state_id' ] form_json = convert_xform_to_json(self.get_xml('ebf_form_v10326')) form_json = { 'form': form_json, 'domain': self.domain, 'xmlns': form_json['@xmlns'], 'doc_type': 'XFormInstance', } ucr_result = config.get_all_values(form_json) for row in ucr_result: row = { i.column.database_column_name: i.value for i in row if i.column.database_column_name not in BLACKLISTED_COLUMNS } self.assertEqual( { "doc_id": None, "repeat_iteration": 0, "timeend": None, "ccs_record_case_id": "d53c940c-3bf3-44f7-97a1-f43fcbe74359", "child_health_case_id": "03f39da4-8ea3-4108-b8a8-1b58fdb4a698", "counsel_adequate_bf": None, "counsel_breast": None, "counsel_exclusive_bf": None, "counsel_increase_food_bf": None, "counsel_methods": None, "counsel_only_milk": 1, "skin_to_skin": None, "is_ebf": 1, "water_or_milk": 0, "other_milk_to_child": None, "tea_other": 0, "eating": 0, "not_breastfeeding": None }, row)
def agg_from_birth_preparedness_forms_ucr(cls, domain, window_start, window_end): doc_id = StaticDataSourceConfiguration.get_doc_id(domain, 'reach-birth_preparedness') config, _ = get_datasource_config(doc_id, domain) ucr_tablename = get_table_name(domain, config.table_id) return """ UPDATE "{ccs_record_tablename}" AS ccs_record SET num_anc_checkups = bp_forms.num_anc_checkups FROM ( SELECT ccs_record_case_id, COUNT(*) as num_anc_checkups FROM "{bp_forms_ucr_tablename}" WHERE ccs_record_case_id IS NOT NULL GROUP BY ccs_record_case_id ) bp_forms WHERE ccs_record.ccs_record_case_id = bp_forms.ccs_record_case_id """.format( ccs_record_tablename=cls._meta.db_table, bp_forms_ucr_tablename=ucr_tablename, ), {'domain': domain, 'window_start': window_start, 'window_end': window_end}
def agg_from_growth_monitoring_forms_ucr(cls, domain, window_start, window_end): doc_id = StaticDataSourceConfiguration.get_doc_id( domain, 'reach-growth_monitoring_forms') config, _ = get_datasource_config(doc_id, domain) ucr_tablename = get_table_name(domain, config.table_id) return """ INSERT INTO "{child_history_tablename}" AS child ( child_health_case_id, weight_child_history, height_child_history, zscore_grading_wfa_history, zscore_grading_hfa_history, zscore_grading_wfh_history ) ( SELECT child_health_case_id, array_agg(weight_child) AS weight_child_history, array_agg(height_child) AS height_child_history, array_agg(zscore_grading_wfh) AS zscore_grading_wfh_history, array_agg(zscore_grading_hfa) AS zscore_grading_hfa_history, array_agg(zscore_grading_wfa) AS zscore_grading_wfa_history FROM ( SELECT child_health_case_id, ARRAY[timeend::text, weight_child::text] AS weight_child, ARRAY[timeend::text, height_child::text] AS height_child, ARRAY[timeend::text, zscore_grading_wfh] AS zscore_grading_wfh, ARRAY[timeend::text, zscore_grading_hfa] AS zscore_grading_hfa, ARRAY[timeend::text, zscore_grading_wfa] AS zscore_grading_wfa FROM "{growth_monitoring_ucr_tablename}" ) growth_monitoring GROUP BY child_health_case_id ) ON CONFLICT (child_health_case_id) DO UPDATE SET weight_child_history = EXCLUDED.weight_child_history, height_child_history = EXCLUDED.height_child_history, zscore_grading_wfh_history = EXCLUDED.zscore_grading_wfh_history, zscore_grading_hfa_history = EXCLUDED.zscore_grading_hfa_history, zscore_grading_wfa_history = EXCLUDED.zscore_grading_wfa_history """.format( child_history_tablename=cls._meta.db_table, growth_monitoring_ucr_tablename=ucr_tablename, ), { 'domain': domain, 'window_start': window_start, 'window_end': window_end }
def handle(self, domain, data_source_id, doc_id, **options): config, _ = get_datasource_config(data_source_id, domain) doc_type = config.referenced_doc_type doc_store = get_document_store(domain, doc_type) doc = doc_store.get_document(doc_id) local_variables = {'config': config, 'doc': doc} cProfile.runctx('config.get_all_values(doc)', {}, local_variables, 'ucr_stats.log') p = pstats.Stats('ucr_stats.log') p.sort_stats('time') print("Top 10 functions by time\n") p.print_stats(10) print("Specs timing\n") p.print_stats('userreports.*specs.*\(__call__\)') print("Socket recvs\n") p.print_stats('recv') print("Doc retrievals\n") p.print_stats('document_store.*\(get_document\)') print("Postgres queries\n") p.print_stats('execute.*psycopg') print("ES queries\n") p.print_stats('es_query.py.*\(run\)') print(""" Note: Due to overhead in profiling, these times are much larger than the real times. Next Steps: 1) choose one of the previous calls to investigate 2) use print_callees or print_callers to follow the calls * usage https://docs.python.org/2/library/profile.html#pstats.Stats.print_stats 3) check out branch je/time-ucr to get logs for processing time of each column (you'll likely need to rebase it on latest master) """)
def agg_from_delivery_forms_ucr(cls, domain, window_start, window_end): doc_id = StaticDataSourceConfiguration.get_doc_id(domain, 'reach-delivery_forms') config, _ = get_datasource_config(doc_id, domain) ucr_tablename = get_table_name(domain, config.table_id) return """ UPDATE "{child_tablename}" AS child SET ccs_record_case_id = delivery_forms.ccs_record_case_id, child_cried = delivery_forms.child_cried FROM ( SELECT child_health_case_id, LAST_VALUE(ccs_record_case_id) OVER w AS ccs_record_case_id, LAST_VALUE(child_cried) OVER w as child_cried FROM "{delivery_form_ucr_tablename}" WINDOW w AS (PARTITION BY child_health_case_id ORDER BY timeend DESC) ) AS delivery_forms WHERE child.child_health_case_id = delivery_forms.child_health_case_id """.format( child_tablename=cls._meta.db_table, delivery_form_ucr_tablename=ucr_tablename, ), {'domain': domain, 'window_start': window_start, 'window_end': window_end}
def agg_from_tasks_case_ucr(cls, domain, window_start, window_end): doc_id = StaticDataSourceConfiguration.get_doc_id( domain, 'reach-child_tasks_cases') config, _ = get_datasource_config(doc_id, domain) ucr_tablename = get_table_name(domain, config.table_id) return """ UPDATE "{child_tablename}" AS child SET tasks_case_id = tasks.doc_id FROM ( SELECT doc_id, child_health_case_id FROM "{tasks_cases_ucr_tablename}" ) tasks WHERE child.child_health_case_id = tasks.child_health_case_id """.format( child_tablename=cls._meta.db_table, tasks_cases_ucr_tablename=ucr_tablename, ), { 'domain': domain, 'window_start': window_start, 'window_end': window_end }
def agg_from_growth_monitoring_forms_ucr(cls, domain, window_start, window_end): doc_id = StaticDataSourceConfiguration.get_doc_id(domain, 'reach-growth_monitoring_forms') config, _ = get_datasource_config(doc_id, domain) ucr_tablename = get_table_name(domain, config.table_id) return """ INSERT INTO "{child_history_tablename}" AS child ( child_health_case_id, weight_child_history, height_child_history, zscore_grading_wfa_history, zscore_grading_hfa_history, zscore_grading_wfh_history ) ( SELECT child_health_case_id, array_agg(weight_child) AS weight_child_history, array_agg(height_child) AS height_child_history, array_agg(zscore_grading_wfh) AS zscore_grading_wfh_history, array_agg(zscore_grading_hfa) AS zscore_grading_hfa_history, array_agg(zscore_grading_wfa) AS zscore_grading_wfa_history FROM ( SELECT child_health_case_id, ARRAY[timeend::text, weight_child::text] AS weight_child, ARRAY[timeend::text, height_child::text] AS height_child, ARRAY[timeend::text, zscore_grading_wfh] AS zscore_grading_wfh, ARRAY[timeend::text, zscore_grading_hfa] AS zscore_grading_hfa, ARRAY[timeend::text, zscore_grading_wfa] AS zscore_grading_wfa FROM "{growth_monitoring_ucr_tablename}" ) growth_monitoring GROUP BY child_health_case_id ) ON CONFLICT (child_health_case_id) DO UPDATE SET weight_child_history = EXCLUDED.weight_child_history, height_child_history = EXCLUDED.height_child_history, zscore_grading_wfh_history = EXCLUDED.zscore_grading_wfh_history, zscore_grading_hfa_history = EXCLUDED.zscore_grading_hfa_history, zscore_grading_wfa_history = EXCLUDED.zscore_grading_wfa_history """.format( child_history_tablename=cls._meta.db_table, growth_monitoring_ucr_tablename=ucr_tablename, ), {'domain': domain, 'window_start': window_start, 'window_end': window_end}
def agg_from_ccs_record_case_ucr(cls, domain, window_start, window_end): doc_id = StaticDataSourceConfiguration.get_doc_id(domain, 'reach-ccs_record_cases') config, _ = get_datasource_config(doc_id, domain) ucr_tablename = get_table_name(domain, config.table_id) return """ UPDATE "{woman_tablename}" AS woman SET pregnant_ranges = ccs_record.pregnant_ranges FROM ( SELECT person_case_id, array_agg(pregnant_range) as pregnant_ranges FROM ( SELECT person_case_id, daterange(opened_on::date, add, '[]') as pregnant_range FROM "{ccs_record_cases_ucr_tablename}" WHERE opened_on < add OR add IS NULL GROUP BY person_case_id, pregnant_range ) AS _tmp_table GROUP BY person_case_id ) ccs_record WHERE woman.person_case_id = ccs_record.person_case_id """.format( woman_tablename=cls._meta.db_table, ccs_record_cases_ucr_tablename=ucr_tablename, ), {'domain': domain, 'window_start': window_start, 'window_end': window_end}
def _ucr_eligible_couple_table(cls, domain): doc_id = StaticDataSourceConfiguration.get_doc_id(domain, cls.ucr_table) config, _ = get_datasource_config(doc_id, domain) return get_table_name(domain, config.table_id)
def person_case_ucr_tablename(self): doc_id = StaticDataSourceConfiguration.get_doc_id(self.domain, 'static-person_cases_v3') config, _ = get_datasource_config(doc_id, self.domain) return get_table_name(self.domain, config.table_id)
def pregnant_tasks_cases_ucr_tablename(self): doc_id = StaticDataSourceConfiguration.get_doc_id(self.domain, 'static-pregnant-tasks_cases') config, _ = get_datasource_config(doc_id, self.domain) return get_table_name(self.domain, config.table_id)
def _get_data_source(id_): return get_datasource_config(id_, self.project.name)[0]
def ucr_tablename(self): doc_id = StaticDataSourceConfiguration.get_doc_id(self.domain, self.ucr_data_source_id) config, _ = get_datasource_config(doc_id, self.domain) return get_table_name(self.domain, config.table_id)
def get_datasource_config_or_404(config_id, domain): try: return get_datasource_config(config_id, domain) except DataSourceConfigurationNotFoundError: raise Http404
def data_source(self): return get_datasource_config(self.primary_data_source_id.hex, self.domain)[0]
def config(self): from corehq.apps.userreports.models import get_datasource_config if self._config is None: self._config, _ = get_datasource_config(self._config_id, self.domain) return self._config
def config(self): if self._config is None: self._config, _ = get_datasource_config(self._config_id, self.domain, self.data_source_type) return self._config
def _old_ucr_tablename(self): doc_id = StaticDataSourceConfiguration.get_doc_id(self.domain, self.ccs_record_monthly_ucr_id) config, _ = get_datasource_config(doc_id, self.domain) return get_table_name(self.domain, config.table_id)