222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308 | async def append_mandatory_fields(
custom_form: BytesIO,
form_category: str,
additional_entities: list[str] = None,
existing_id: str = None,
) -> tuple[str, BytesIO]:
"""Append mandatory fields to the XLSForm for use in FMTM.
Args:
custom_form(BytesIO): the XLSForm data uploaded, wrapped in BytesIO.
form_category(str): the form category name (in form_title and description).
additional_entities(list[str]): add extra select_one_from_file fields to
reference an additional Entity list (set of geometries).
The values should be plural, so that 's' will be stripped in the
field name.
existing_id(str): an existing UUID to use for the form_id, else random uuid4.
Returns:
tuple(str, BytesIO): the xFormId + the update XLSForm wrapped in BytesIO.
"""
log.info("Appending field mapping questions to XLSForm")
custom_sheets = pd.read_excel(custom_form, sheet_name=None, engine="calamine")
mandatory_sheets = pd.read_excel(f"{xlsforms_path}/common/mandatory_fields.xls", sheet_name=None, engine="calamine")
digitisation_sheets = pd.read_excel(f"{xlsforms_path}/common/digitisation_fields.xls", sheet_name=None, engine="calamine")
custom_sheets = standardize_xlsform_sheets(custom_sheets)
# Merge 'survey' and 'choices' sheets
if "survey" not in custom_sheets:
msg = "Survey sheet is required in XLSForm!"
log.error(msg)
raise ValueError(msg)
log.debug("Merging survey sheet XLSForm data")
custom_sheets["survey"] = merge_dataframes(
mandatory_sheets.get("survey"), custom_sheets.get("survey"), digitisation_sheets.get("survey")
)
# Hardcode the form_category value for the start instructions
if form_category.endswith("s"):
# Plural to singular
form_category = form_category[:-1]
form_category_row = custom_sheets["survey"].loc[custom_sheets["survey"]["name"] == "form_category"]
if not form_category_row.empty:
custom_sheets["survey"].loc[custom_sheets["survey"]["name"] == "form_category", "calculation"] = f"once('{form_category}')"
# Ensure the 'choices' sheet exists in custom_sheets
if "choices" not in custom_sheets or custom_sheets["choices"] is None:
custom_sheets["choices"] = pd.DataFrame(columns=["list_name", "name", "label::english(en)"])
log.debug("Merging choices sheet XLSForm data")
custom_sheets["choices"] = merge_dataframes(
mandatory_sheets.get("choices"), custom_sheets.get("choices"), digitisation_sheets.get("choices")
)
# Append or overwrite 'entities' and 'settings' sheets
log.debug("Overwriting entities and settings XLSForm sheets")
custom_sheets.update({key: mandatory_sheets[key] for key in ["entities", "settings"] if key in mandatory_sheets})
if "entities" not in custom_sheets:
msg = "Entities sheet is required in XLSForm!"
log.error(msg)
raise ValueError(msg)
if "settings" not in custom_sheets:
msg = "Settings sheet is required in XLSForm!"
log.error(msg)
raise ValueError(msg)
# Set the 'version' column to the current timestamp (if 'version' column exists in 'settings')
xform_id = existing_id if existing_id else uuid4()
current_datetime = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
log.debug(f"Setting xFormId = {xform_id} | form title = {form_category} | version = {current_datetime}")
custom_sheets["settings"]["version"] = current_datetime
custom_sheets["settings"]["form_id"] = xform_id
custom_sheets["settings"]["form_title"] = form_category
# Append select_one_from_file for additional entities
if additional_entities:
log.debug("Adding additional entity list reference to XLSForm")
for entity_name in additional_entities:
custom_sheets["survey"] = append_select_one_from_file_row(custom_sheets["survey"], entity_name)
# Return spreadsheet wrapped as BytesIO memory object
output = BytesIO()
with pd.ExcelWriter(output, engine="openpyxl") as writer:
for sheet_name, df in custom_sheets.items():
df.to_excel(writer, sheet_name=sheet_name, index=False)
output.seek(0)
return (xform_id, output)
|