def handle_record(self, jsdata, entity, periods): logger.info(periods) missing = '0.0' data = {} # loop on rows indic_data = {(indic_id, pid): val for indic_id, pid, val in jsdata['rows']} for period in periods: pid = period.dhis_strid for indicator in Indicator.get_all_dhis(): numerator = indic_data.get((indicator.dhis_numerator_id, pid)) denominator = indic_data.get( (indicator.dhis_denominator_id, pid)) if numerator is None or numerator == missing: logger.error("Missing numerator `{}` for `{}`".format( indicator.dhis_numerator_id, indicator)) continue if not indicator.is_number and (denominator is None or denominator == missing): logger.error("Missing denominator `{}` for `{}`".format( indicator.dhis_denominator_id, indicator)) continue logger.debug(data_ident_for(indicator, period, entity)) data.update({ data_ident_for(indicator, period, entity): { 'slug': indicator.slug, 'period': period, 'entity': entity, 'numerator': numerator, 'denominator': denominator } }) d = DataRecord.batch_create(data, dhisbot, source=DataRecord.DHIS, arrival_status=DataRecord.ARRIVED, auto_validate=True) if self.debug: pp(d) return d
def handle_record(self, jsdata, entity, periods): logger.info(periods) missing = '0.0' data = {} # loop on rows indic_data = {(indic_id, pid): val for indic_id, pid, val in jsdata['rows']} for period in periods: pid = period.dhis_strid for indicator in Indicator.get_all_dhis(): numerator = indic_data.get((indicator.dhis_numerator_id, pid)) denominator = indic_data.get( (indicator.dhis_denominator_id, pid)) if numerator is None or numerator == missing: logger.error("Missing numerator `{}` for `{}`" .format(indicator.dhis_numerator_id, indicator)) continue if not indicator.is_number and (denominator is None or denominator == missing): logger.error("Missing denominator `{}` for `{}`" .format(indicator.dhis_denominator_id, indicator)) continue logger.debug(data_ident_for(indicator, period, entity)) data.update({data_ident_for(indicator, period, entity): { 'slug': indicator.slug, 'period': period, 'entity': entity, 'numerator': numerator, 'denominator': denominator}}) d = DataRecord.batch_create(data, dhisbot, source=DataRecord.DHIS, arrival_status=DataRecord.ARRIVED, auto_validate=True) if self.debug: pp(d) return d
def read_xls(filepath, partner): if not partner.can_upload: raise UploadPermissionDenied(_("{user} is not allowed to submit data") .format(user=partner)) try: wb = load_workbook(filepath, data_only=True) except InvalidFileException: raise IncorrectExcelFile(_("Not a proper XLSX Template.")) ws = wb.active nb_rows = len(ws.rows) cd = lambda row, column: ws.cell(row=row, column=column).value # data holder data = { 'errors': [], } # record now's time to compare with delays submited_on = timezone.now() def add_error(row, column=None, indicator=None, error=None, text=None): data['errors'].append({ 'row': row, 'column': column, 'indicator': Indicator.get_or_none(indicator.slug) if indicator else None, 'slug': error, 'text': text, }) # retrieve and store default year/month default_year_addr = "=$C$4" default_month_addr = "=$D$4" try: default_year = int(float(cd(4, 3))) except: default_year = None try: default_month = int(float(cd(4, 4))) except: default_month = None for row in range(5, nb_rows + 1): row_has_data = sum([1 for x in ws.rows[row - 1][4:] if x.value]) if not row_has_data: continue rdc = Entity.get_root() dps = Entity.find_by_stdname(cd(row, 1), parent=rdc) if dps is None: logger.warning("No DPS for row #{}".format(row)) continue # no DPS, no data zs = Entity.find_by_stdname(cd(row, 2), parent=dps) if zs is None: if cd(row, 2).lower().strip() != "-": logger.warning("No ZS for row #{}".format(row)) continue # no ZS, no data else: entity = dps else: entity = zs # check upload location authorization if partner.upload_location not in entity.get_ancestors(): add_error(row, error='permission_denied', text=_("You can't submit data for " "that location ({entity})") .format(entity=entity)) continue # retrieve period year_str = cd(row, 3) if year_str == default_year_addr: year = default_year else: try: year = int(float(year_str)) except: year = None month_str = cd(row, 4) if month_str == default_month_addr: month = default_month else: try: month = int(float(month_str)) except: month = None if year is None or month is None: logger.warning("No year or month for row #{}".format(row)) add_error(row, error='incorrect_period', text=_("Missing year or month at row {row}") .format(row)) continue try: period = MonthPeriod.get_or_create(year, month) except ValueError as e: logger.warning("Unable to retrieve period: {}".format(e)) add_error(row, error='incorrect_period', text=_("Unable to retrieve period for {y}/{m}") .format(y=year, m=month)) continue for idx, cell in enumerate(ws.rows[2][4:]): if idx % 3 != 0: continue # skip empty merged cols column = letter_to_column(cell.column) try: number = cell.value.split('-')[0].strip() except: # the header doesn't respect the format # better fail everything raise IncorrectExcelFile(_("Not a proper XLSX Template.")) num = cd(row, column) denom = cd(row, column + 1) # skip if missing numerator if num is None: # logger.debug("No numerator for indic #{}".format(number)) continue indicator = Indicator.get_by_number(number) # not an expected number if indicator is None: logger.warning("No indicator found at col #{}".format(column)) add_error(row, column=cell.column, error='incorrect_indicator', text=_("Unable to match an Indicator at col {col}") .format(col=cell.column)) continue # ensure level of data submitted depending on type if indicator.collection_type == indicator.SURVEY: # data must be DPS if not entity.is_dps: logger.warning("Data for Survey Indic on non-DPS #{}" .format(cell.coordinate)) add_error(row, column=cell.column, error='incorect_level', text=_("Survey indicator require DPS data")) continue elif indicator.collection_type == indicator.ROUTINE: # data must be ZS if not entity.is_zs: logger.warning("Data for Routine Indic on non-ZS #{}" .format(cell.coordinate)) add_error(row, column=cell.column, error='incorect_level', text=_("Routine indicator require ZS data")) continue # check submission period for that Indicator if not indicator.can_submit_on(on=submited_on, period=period): logger.warning("{on} is not a valid submission time " "for {ind} {period}" .format(on=submited_on, ind=indicator, period=period)) add_error(row, column=cell.column, indicator=indicator, error='outside_submussion_delay', text=_("{on} is outside submission period " "for Indicator #{ind} at {period}") .format(on=submited_on.strftime('%d-%m-%Y'), ind=indicator.number, period=period)) continue if not indicator.is_number and denom is None: logger.warning("No denominator for indic #{}".format(number)) add_error(row, column=cell.column, error='missing_denominator', text=_("Missing denominator on " "non-number Indicator")) continue elif indicator.is_number: denom = 1 try: num = float(num) denom = float(denom) except: add_error(row, column=cell.column, error='incorrect_value', indicator=indicator, text=_("Incorrect value for numerator " "or denominator `{num} / {denom}`") .format(num=num, denom=denom)) continue data.update({data_ident_for(indicator, period, entity): { 'slug': indicator.slug, 'period': period, 'entity': entity, 'numerator': num, 'denominator': denom}}) return data