def test_to_xls_export_respects_custom_field_delimiter(self):
survey = self._create_childrens_survey()
export_builder = ExportBuilder()
export_builder.GROUP_DELIMITER = ExportBuilder.GROUP_DELIMITER_DOT
export_builder.set_survey(survey)
xls_file = NamedTemporaryFile(suffix='.xls')
filename = xls_file.name
export_builder.to_xls_export(filename, self.data)
xls_file.seek(0)
wb = load_workbook(filename)
# check header columns
main_sheet = wb.get_sheet_by_name('childrens_survey')
expected_column_headers = [
u'name', u'age', u'geo.geolocation', u'geo._geolocation_latitude',
u'geo._geolocation_longitude', u'geo._geolocation_altitude',
u'geo._geolocation_precision', u'tel.tel.office',
u'tel.tel.mobile', u'_id', u'meta.instanceID', u'_uuid',
u'_submission_time', u'_index', u'_parent_index',
u'_parent_table_name', u'_tags', '_notes', '_version',
'_duration', '_submitted_by']
column_headers = [c[0].value for c in main_sheet.columns]
self.assertEqual(sorted(column_headers),
sorted(expected_column_headers))
xls_file.close()
def test_to_xls_export_respects_custom_field_delimiter(self):
survey = self._create_childrens_survey()
export_builder = ExportBuilder()
export_builder.GROUP_DELIMITER = ExportBuilder.GROUP_DELIMITER_DOT
export_builder.set_survey(survey)
xls_file = NamedTemporaryFile(suffix='.xls')
filename = xls_file.name
export_builder.to_xls_export(filename, self.data)
xls_file.seek(0)
wb = load_workbook(filename)
# check header columns
main_sheet = wb.get_sheet_by_name('childrens_survey')
expected_column_headers = [
u'name', u'age', u'geo.geolocation', u'geo._geolocation_latitude',
u'geo._geolocation_longitude', u'geo._geolocation_altitude',
u'geo._geolocation_precision', u'tel.tel.office',
u'tel.tel.mobile', u'_id', u'meta.instanceID', u'_uuid',
u'_submission_time', u'_index', u'_parent_index',
u'_parent_table_name', u'_tags', '_notes', '_version', '_duration',
'_submitted_by'
]
column_headers = [c[0].value for c in main_sheet.columns]
self.assertEqual(sorted(column_headers),
sorted(expected_column_headers))
xls_file.close()
def test_delimiter_replacement_works_existing_fields(self):
survey = self._create_childrens_survey()
export_builder = ExportBuilder()
export_builder.GROUP_DELIMITER = "."
export_builder.set_survey(survey)
expected_sections = [{"name": "children", "elements": [{"title": "children.name", "xpath": "children/name"}]}]
children_section = export_builder.section_by_name("children")
self.assertEqual(children_section["elements"][0]["title"], expected_sections[0]["elements"][0]["title"])
def test_delimiter_replacement_works_for_generated_gps_fields(self):
survey = self._create_childrens_survey()
export_builder = ExportBuilder()
export_builder.GROUP_DELIMITER = "."
export_builder.set_survey(survey)
expected_section = {
"name": "childrens_survey",
"elements": [{"title": "geo._geolocation_latitude", "xpath": "geo/_geolocation_latitude"}],
}
main_section = export_builder.section_by_name("childrens_survey")
match = filter(lambda x: (expected_section["elements"][0]["xpath"] == x["xpath"]), main_section["elements"])[0]
self.assertEqual(expected_section["elements"][0]["title"], match["title"])
def test_delimiter_replacement_works_generated_multi_select_fields(self):
survey = self._create_childrens_survey()
export_builder = ExportBuilder()
export_builder.GROUP_DELIMITER = "."
export_builder.set_survey(survey)
expected_section = {
"name": "children",
"elements": [{"title": "children.fav_colors.red", "xpath": "children/fav_colors/red"}],
}
childrens_section = export_builder.section_by_name("children")
match = filter(lambda x: expected_section["elements"][0]["xpath"] == x["xpath"], childrens_section["elements"])[
0
]
self.assertEqual(expected_section["elements"][0]["title"], match["title"])
def generate_form_report(sheet):
group_delimiter = '/'
form_id = sheet.form.id
from onadata.libs.utils.export_tools import ExportBuilder, query_mongo
fieldsight_xf = FieldSightXF.objects.get(pk=form_id)
xform = fieldsight_xf.xf
id_string = xform.id_string
export_builder = ExportBuilder()
export_builder.GROUP_DELIMITER = group_delimiter
export_builder.SPLIT_SELECT_MULTIPLES = True
export_builder.BINARY_SELECT_MULTIPLES = False
export_builder.set_survey(xform.data_dictionary().survey)
prefix = datetime.datetime.now().strftime("%Y_%m_%d_%H_%M_%S") + "__" + \
xform.id_string
temp_file = NamedTemporaryFile(prefix=prefix, suffix=(".xls"))
filter_query = {
'$and': [{
'fs_project_uuid': str(form_id)
}, {
'$or': [{
'_deleted_at': {
'$exists': False
}
}, {
'_deleted_at': None
}]
}],
'_deleted_at': {
'$exists': False
}
}
# filter_query = {"fs_project_uuid": str(form_id)}
records = query_mongo(xform.user.username, xform.id_string, filter_query)
export_builder.to_xls_export(temp_file.name, records, xform.user.username,
xform.id_string, filter_query)
if not os.path.exists("media/forms/"):
os.makedirs("media/forms/")
temporarylocation = "media/forms/submissions_{}.xls".format(
xform.id_string)
import shutil
shutil.copy(temp_file.name, temporarylocation)
upload_to_drive(temporarylocation,
str(fieldsight_xf.id) + '_' + id_string, None,
fieldsight_xf.project, sheet.user, sheet)
os.remove(temporarylocation)
def form_submission(form_id):
from onadata.libs.utils.export_tools import ExportBuilder, query_mongo
fieldsight_xf = FieldSightXF.objects.get(pk=form_id)
xform = fieldsight_xf.xf
export_builder = ExportBuilder()
export_builder.GROUP_DELIMITER = group_delimiter
export_builder.SPLIT_SELECT_MULTIPLES = True
export_builder.BINARY_SELECT_MULTIPLES = False
export_builder.set_survey(xform.data_dictionary().survey)
prefix = datetime.datetime.now().strftime("%Y_%m_%d_%H_%M_%S") + "__" + \
xform.id_string
temp_file = NamedTemporaryFile(prefix=prefix, suffix=(".xls"))
filter_query = {'$and': [{'fs_project_uuid': str(form_id)}, {'$or': [{
'_deleted_at':{'$exists': False}}, {'_deleted_at': None}]}],
'_deleted_at': {'$exists': False}}
# filter_query = {"fs_project_uuid": str(form_id)}
records = query_mongo(xform.user.username, xform.id_string, filter_query)
export_builder.to_xls_export(temp_file.name, records, xform.user.username,
xform.id_string, filter_query)
df = pd.read_excel(temp_file)
df = df.applymap(unicode)
# select multiples as Bool
childrens = json.loads(xform.json)['children']
select_multiples = [c['name'] for c in childrens if c['type'] == 'select all that apply']
select_multiples_slash = [s+"/" for s in select_multiples]
dict_keys = {"1.0": True, "0.0": False}
for col in df.columns:
for select_m_slash in select_multiples_slash:
if select_m_slash in col:
df = df.replace({col: dict_keys})
df = df.replace('nan', '')
try:
print("df shape", df.shape)
x, y = df.shape
print(" total cell in sheets ==", x*y)
values = df.values.tolist()
values.insert(0, list(df.columns.values))
return values
except Exception as e:
return [[]]
return [[]]
def test_delimiter_replacement_works_existing_fields(self):
survey = self._create_childrens_survey()
export_builder = ExportBuilder()
export_builder.GROUP_DELIMITER = "."
export_builder.set_survey(survey)
expected_sections =\
[
{
'name': 'children',
'elements': [
{
'title': 'children.name',
'xpath': 'children/name'
}
]
}
]
children_section = export_builder.section_by_name('children')
self.assertEqual(children_section['elements'][0]['title'],
expected_sections[0]['elements'][0]['title'])
def test_delimiter_replacement_works_generated_multi_select_fields(self):
survey = self._create_childrens_survey()
export_builder = ExportBuilder()
export_builder.GROUP_DELIMITER = "."
export_builder.set_survey(survey)
expected_section =\
{
'name': 'children',
'elements': [
{
'title': 'children.fav_colors.red',
'xpath': 'children/fav_colors/red'
}
]
}
childrens_section = export_builder.section_by_name('children')
match = filter(lambda x: expected_section['elements'][0]['xpath']
== x['xpath'], childrens_section['elements'])[0]
self.assertEqual(
expected_section['elements'][0]['title'], match['title'])
def test_delimiter_replacement_works_generated_multi_select_fields(self):
survey = self._create_childrens_survey()
export_builder = ExportBuilder()
export_builder.GROUP_DELIMITER = "."
export_builder.set_survey(survey)
expected_section =\
{
'name': 'children',
'elements': [
{
'title': 'children.fav_colors.red',
'xpath': 'children/fav_colors/red'
}
]
}
childrens_section = export_builder.section_by_name('children')
match = filter(lambda x: expected_section['elements'][0]['xpath']
== x['xpath'], childrens_section['elements'])[0]
self.assertEqual(
expected_section['elements'][0]['title'], match['title'])
def test_delimiter_replacement_works_for_generated_gps_fields(self):
survey = self._create_childrens_survey()
export_builder = ExportBuilder()
export_builder.GROUP_DELIMITER = "."
export_builder.set_survey(survey)
expected_section = \
{
'name': 'childrens_survey',
'elements': [
{
'title': 'geo._geolocation_latitude',
'xpath': 'geo/_geolocation_latitude'
}
]
}
main_section = export_builder.section_by_name('childrens_survey')
match = filter(
lambda x: (expected_section['elements'][0]['xpath'] == x['xpath']),
main_section['elements'])[0]
self.assertEqual(expected_section['elements'][0]['title'],
match['title'])
def test_delimiter_replacement_works_for_generated_gps_fields(self):
survey = self._create_childrens_survey()
export_builder = ExportBuilder()
export_builder.GROUP_DELIMITER = "."
export_builder.set_survey(survey)
expected_section = \
{
'name': 'childrens_survey',
'elements': [
{
'title': 'geo._geolocation_latitude',
'xpath': 'geo/_geolocation_latitude'
}
]
}
main_section = export_builder.section_by_name('childrens_survey')
match = filter(
lambda x: (expected_section['elements'][0]['xpath']
== x['xpath']), main_section['elements'])[0]
self.assertEqual(
expected_section['elements'][0]['title'], match['title'])
def test_delimiter_replacement_works_existing_fields(self):
survey = self._create_childrens_survey()
export_builder = ExportBuilder()
export_builder.GROUP_DELIMITER = "."
export_builder.set_survey(survey)
expected_sections =\
[
{
'name': 'children',
'elements': [
{
'title': 'children.name',
'xpath': 'children/name'
}
]
}
]
children_section = export_builder.section_by_name('children')
self.assertEqual(
children_section['elements'][0]['title'],
expected_sections[0]['elements'][0]['title'])
def test_to_xls_export_respects_custom_field_delimiter(self):
survey = self._create_childrens_survey()
export_builder = ExportBuilder()
export_builder.GROUP_DELIMITER = ExportBuilder.GROUP_DELIMITER_DOT
export_builder.set_survey(survey)
xls_file = NamedTemporaryFile(suffix=".xls")
filename = xls_file.name
export_builder.to_xls_export(filename, self.data)
xls_file.seek(0)
wb = load_workbook(filename)
# check header columns
main_sheet = wb.get_sheet_by_name("childrens_survey")
expected_column_headers = [
u"name",
u"age",
u"geo.geolocation",
u"geo._geolocation_latitude",
u"geo._geolocation_longitude",
u"geo._geolocation_altitude",
u"geo._geolocation_precision",
u"tel.tel.office",
u"tel.tel.mobile",
u"_id",
u"meta.instanceID",
u"_uuid",
u"_submission_time",
u"_index",
u"_parent_index",
u"_parent_table_name",
u"_tags",
"_notes",
]
column_headers = [c[0].value for c in main_sheet.columns]
self.assertEqual(sorted(column_headers), sorted(expected_column_headers))
xls_file.close()
