def export_audit_csv(settings, audit_json):
"""
Save audit CSV to disk.
:param settings: Settings from command line and configuration file
:param audit_json: Audit JSON
"""
csv_exporter = csvExporter.CsvExporter(audit_json, settings[EXPORT_INACTIVE_ITEMS_TO_CSV])
count = 0
if settings[USE_REAL_TEMPLATE_NAME] is False:
csv_export_filename = audit_json['template_id']
elif settings[USE_REAL_TEMPLATE_NAME] is True:
csv_export_filename = audit_json['template_data']['metadata']['name'] + ' - ' + audit_json['template_id']
csv_export_filename = csv_export_filename.replace('/', ' ').replace('\\', ' ')
elif settings[USE_REAL_TEMPLATE_NAME] is str and settings[USE_REAL_TEMPLATE_NAME].startswith('single_file'):
csv_export_filename = settings[CONFIG_NAME]
else:
csv_export_filename = audit_json['template_id']
for row in csv_exporter.audit_table:
count += 1
row[0] = count
csv_exporter.append_converted_audit_to_bulk_export_file(
os.path.join(settings[EXPORT_PATH], csv_export_filename + '.csv'))
def export_audit_pandas(logger, settings, audit_json, get_started):
"""
Save audit to a database.
:param logger: The logger
:param settings: Settings from command line and configuration file
:param audit_json: Audit JSON
Args:
get_started: A tuple containing database connection settings
"""
for export_format in settings[EXPORT_FORMATS]:
if export_format == 'sql':
export_audit_sql(logger, settings, audit_json, get_started)
elif export_format == 'pickle':
# export_audit_doc_creation(logger, settings, audit_json, 'docx', 'test.docx', media_list=[])
logger.info('Writing to Pickle')
csv_exporter = csvExporter.CsvExporter(
audit_json, settings[EXPORT_INACTIVE_ITEMS_TO_CSV])
df = csv_exporter.audit_table
df = pd.DataFrame.from_records(df, columns=SQL_HEADER_ROW)
df.replace(
{
'ItemScore': '',
'ItemMaxScore': '',
'ItemScorePercentage': ''
},
np.nan,
inplace=True)
df.fillna(value={'Latitude': 0, 'Longitude': 0}, inplace=True)
df.to_pickle('{}.pkl'.format(settings[SQL_TABLE]))
def test_Rugby_Union_Contest_Self_Assessment_(self):
csv_exporter = csv.CsvExporter(
json.load(
open(
os.path.join(self.path_to_test_files,
'null_date_test.json'), 'r')))
csv_exporter.save_converted_audit_to_file('test 32.csv',
allow_overwrite=True)
self.assertEqual(
open('test 32.csv', 'r').read(),
open(
os.path.join(self.path_to_test_files,
'null_date_test_expected_output.csv'),
'r').read())
os.remove('test 32.csv')
def export_audit_sql(logger, settings, audit_json, get_started):
"""
Save audit to a database.
:param logger: The logger
:param settings: Settings from command line and configuration file
:param audit_json: Audit JSON
:get_started: Tuple containing settings
"""
engine = get_started[1]
database = get_started[4]
csv_exporter = csvExporter.CsvExporter(audit_json, settings[EXPORT_INACTIVE_ITEMS_TO_CSV])
df = csv_exporter.audit_table
df = pd.DataFrame.from_records(df, columns=SQL_HEADER_ROW)
df['DatePK'] = pd.to_datetime(df['DateModified']).values.astype(np.int64) // 10 ** 6
if settings[DB_TYPE].startswith(('mysql', 'postgres')):
df.replace({'DateCompleted': ''}, '1900-01-01 00:00:00', inplace=True)
df.replace({'ConductedOn': ''}, '1900-01-01 00:00:00', inplace=True)
df['DateStarted'] = pd.to_datetime(df['DateStarted'])
df['DateCompleted'] = pd.to_datetime(df['DateCompleted'])
df['DateModified'] = pd.to_datetime(df['DateModified'])
df['ConductedOn'] = pd.to_datetime(df['ConductedOn'])
df.replace({'ItemScore': '', 'ItemMaxScore': '', 'ItemScorePercentage': ''}, np.nan, inplace=True)
df.fillna(0, inplace=True)
df['SortingIndex'] = range(1, len(df) + 1)
df_dict = df.to_dict(orient='records')
Session = sessionmaker(bind=engine)
session = Session()
try:
session.bulk_insert_mappings(database, df_dict)
except KeyboardInterrupt:
logger.warning('Interrupted by user, exiting.')
session.rollback()
sys.exit(0)
except OperationalError as ex:
session.rollback()
logger.warning('Something went wrong. Here are the details: {}'.format(ex))
except IntegrityError as ex:
# If the bulk insert fails, we do a slower merge
logger.warning('Duplicate found, attempting to update')
session.rollback()
for row in df_dict:
row_to_dict = database(**row)
session.merge(row_to_dict)
logger.debug('Row successfully updated.')
session.commit()
def test_failed_response_test_(self):
csv_exporter = csv.CsvExporter(
json.load(
open(
os.path.join(self.path_to_test_files,
'unit_test_failed_response_test_.json'),
'r')))
csv_exporter.save_converted_audit_to_file('test 33.csv',
allow_overwrite=True)
self.assertEqual(
open('test 33.csv', 'r').read(),
open(
os.path.join(
self.path_to_test_files,
'unit_test_failed_response_test__expected_output.csv'),
'r').read())
os.remove('test 33.csv')
def test_single_checkbox_checked(self):
csv_exporter = csv.CsvExporter(
json.load(
open(
os.path.join(self.path_to_test_files,
'unit_test_single_checkbox_checked.json'),
'r')))
csv_exporter.save_converted_audit_to_file('test 2.csv',
allow_overwrite=True)
self.assertEqual(
open('test 2.csv', 'r').read(),
open(
os.path.join(
self.path_to_test_files,
'unit_test_single_checkbox_checked_expected_output.csv'),
'r').read())
os.remove('test 2.csv')
def test_do_not_export_inactive_fields(self):
csv_exporter = csv.CsvExporter(
json.load(
open(
os.path.join(self.path_to_test_files,
'do_not_export_inactive_fields.json'), 'r')),
False)
csv_exporter.save_converted_audit_to_file('test 37.csv',
allow_overwrite=True)
self.assertEqual(
open('test 37.csv', 'r').read(),
open(
os.path.join(
self.path_to_test_files,
'do_not_export_inactive_fields_expected_output.csv'),
'r').read())
os.remove('test 37.csv')
def test_single_date_time_field_input_date_using_now_button(self):
csv_exporter = csv.CsvExporter(
json.load(
open(
os.path.join(
self.path_to_test_files,
'unit_test_single_date_time_field_input_date_using_now_button.json'
), 'r')))
csv_exporter.save_converted_audit_to_file('test 3.csv',
allow_overwrite=True)
self.assertEqual(
open('test 3.csv', 'r').read(),
open(
os.path.join(
self.path_to_test_files,
'unit_test_single_date_time_field_input_date_using_now_button_expected_output.csv'
), 'r').read())
os.remove('test 3.csv')
def test_single_text_single_line_mathematical_symbols_in_text(self):
csv_exporter = csv.CsvExporter(
json.load(
open(
os.path.join(
self.path_to_test_files,
'unit_test_single_text_single_line_mathematical_symbols_in_text.json'
), 'r')))
csv_exporter.save_converted_audit_to_file('test 14.csv',
allow_overwrite=True)
self.assertEqual(
open('test 14.csv', 'r').read(),
open(
os.path.join(
self.path_to_test_files,
'unit_test_single_text_single_line_mathematical_symbols_in_text_expected_output.csv'
), 'r').read())
os.remove('test 14.csv')
def test_SafetyCulture_iAuditor___The_Smartest_Checklist_App_(self):
csv_exporter = csv.CsvExporter(
json.load(
open(
os.path.join(
self.path_to_test_files,
'unit_test_SafetyCulture_iAuditor___The_Smartest_Checklist_App_.json'
), 'r')))
csv_exporter.save_converted_audit_to_file('test 12.csv',
allow_overwrite=True)
self.assertEqual(
open('test 12.csv', 'r').read(),
open(
os.path.join(
self.path_to_test_files,
'unit_test_SafetyCulture_iAuditor___The_Smartest_Checklist_App__expected_output.csv'
), 'r').read())
os.remove('test 12.csv')
def test_single_question_safe___at_risk___na_answered_at_risk(self):
csv_exporter = csv.CsvExporter(
json.load(
open(
os.path.join(
self.path_to_test_files,
'unit_test_single_question_safe___at_risk___na_answered_at_risk.json'
), 'r')))
csv_exporter.save_converted_audit_to_file('test 11.csv',
allow_overwrite=True)
self.assertEqual(
open('test 11.csv', 'r').read(),
open(
os.path.join(
self.path_to_test_files,
'unit_test_single_question_safe___at_risk___na_answered_at_risk_expected_output.csv'
), 'r').read())
os.remove('test 11.csv')
def test_single_question_custom_response_set_answered_california(self):
csv_exporter = csv.CsvExporter(
json.load(
open(
os.path.join(
self.path_to_test_files,
'unit_test_single_question_custom_response_set_answered_california.json'
), 'r')))
csv_exporter.save_converted_audit_to_file('test 8.csv',
allow_overwrite=True)
self.assertEqual(
open('test 8.csv', 'r').read(),
open(
os.path.join(
self.path_to_test_files,
'unit_test_single_question_custom_response_set_answered_california_expected_output.csv'
), 'r').read())
os.remove('test 8.csv')
def test_single_media_field_Single_image_taken_with_camera(self):
csv_exporter = csv.CsvExporter(
json.load(
open(
os.path.join(
self.path_to_test_files,
'unit_test_single_media_field_Single_image_taken_with_camera.json'
), 'r')))
csv_exporter.save_converted_audit_to_file('test 7.csv',
allow_overwrite=True)
self.assertEqual(
open('test 7.csv', 'r').read(),
open(
os.path.join(
self.path_to_test_files,
'unit_test_single_media_field_Single_image_taken_with_camera_expected_output.csv'
), 'r').read())
os.remove('test 7.csv')
def test_single_address_field_textually_input_address(self):
csv_exporter = csv.CsvExporter(
json.load(
open(
os.path.join(
self.path_to_test_files,
'unit_test_single_address_field_textually_input_address.json'
), 'r')))
csv_exporter.save_converted_audit_to_file('test 6.csv',
allow_overwrite=True)
self.assertEqual(
open('test 6.csv', 'r').read(),
open(
os.path.join(
self.path_to_test_files,
'unit_test_single_address_field_textually_input_address_expected_output.csv'
), 'r').read())
os.remove('test 6.csv')
def test_single_information_field___weblink__Nothing_to_do_here(self):
csv_exporter = csv.CsvExporter(
json.load(
open(
os.path.join(
self.path_to_test_files,
'unit_test_single_information_field___weblink__Nothing_to_do_here.json'
), 'r')))
csv_exporter.save_converted_audit_to_file('test 4.csv',
allow_overwrite=True)
self.assertEqual(
open('test 4.csv', 'r').read(),
open(
os.path.join(
self.path_to_test_files,
'unit_test_single_information_field___weblink__Nothing_to_do_here_expected_output.csv'
), 'r').read())
os.remove('test 4.csv')
def test_single_drawing_field_Drawing_done_in_iOS_device(self):
csv_exporter = csv.CsvExporter(
json.load(
open(
os.path.join(
self.path_to_test_files,
'unit_test_single_drawing_field_Drawing_done_in_iOS_device.json'
), 'r')))
csv_exporter.save_converted_audit_to_file('test 5.csv',
allow_overwrite=True)
self.assertEqual(
open('test 5.csv', 'r').read(),
open(
os.path.join(
self.path_to_test_files,
'unit_test_single_drawing_field_Drawing_done_in_iOS_device_expected_output.csv'
), 'r').read())
os.remove('test 5.csv')
def test_single_dynamic_field_3_instances(self):
csv_exporter = csv.CsvExporter(
json.load(
open(
os.path.join(
self.path_to_test_files,
'unit_test_single_dynamic_field_3_instances.json'),
'r')))
csv_exporter.save_converted_audit_to_file('test 30.csv',
allow_overwrite=True)
self.assertEqual(
open('test 30.csv', 'r').read(),
open(
os.path.join(
self.path_to_test_files,
'unit_test_single_dynamic_field_3_instances_expected_output.csv'
), 'r').read())
os.remove('test 30.csv')
def test_single_question_as_mandatory_field_answered_mandatory_field(self):
csv_exporter = csv.CsvExporter(
json.load(
open(
os.path.join(
self.path_to_test_files,
'unit_test_single_question_as_mandatory_field_answered_mandatory_field.json'
), 'r')))
csv_exporter.save_converted_audit_to_file('test 28.csv',
allow_overwrite=True)
self.assertEqual(
open('test 28.csv', 'r').read(),
open(
os.path.join(
self.path_to_test_files,
'unit_test_single_question_as_mandatory_field_answered_mandatory_field_expected_output.csv'
), 'r').read())
os.remove('test 28.csv')
def test_single_media_field_24_images_from_gallery(self):
csv_exporter = csv.CsvExporter(
json.load(
open(
os.path.join(
self.path_to_test_files,
'unit_test_single_media_field_24_images_from_gallery.json'
), 'r')))
csv_exporter.save_converted_audit_to_file('test 27.csv',
allow_overwrite=True)
self.assertEqual(
open('test 27.csv', 'r').read(),
open(
os.path.join(
self.path_to_test_files,
'unit_test_single_media_field_24_images_from_gallery_expected_output.csv'
), 'r').read())
os.remove('test 27.csv')
def test_single_multiple_choice___multiple_selection_8_answers_chosen(
self):
csv_exporter = csv.CsvExporter(
json.load(
open(
os.path.join(
self.path_to_test_files,
'unit_test_single_multiple_choice___multiple_selection_8_answers_chosen.json'
), 'r')))
csv_exporter.save_converted_audit_to_file('test 25.csv',
allow_overwrite=True)
self.assertEqual(
open('test 25.csv', 'r').read(),
open(
os.path.join(
self.path_to_test_files,
'unit_test_single_multiple_choice___multiple_selection_8_answers_chosen_expected_output.csv'
), 'r').read())
os.remove('test 25.csv')
def test_single_question_yes___no___na_comment_and_images_added_to_question(
self):
csv_exporter = csv.CsvExporter(
json.load(
open(
os.path.join(
self.path_to_test_files,
'unit_test_single_question_yes___no___na_comment_and_images_added_to_question.json'
), 'r')))
csv_exporter.save_converted_audit_to_file('test 35.csv',
allow_overwrite=True)
self.assertEqual(
open('test 35.csv', 'r').read(),
open(
os.path.join(
self.path_to_test_files,
'unit_test_single_question_yes___no___na_comment_and_images_added_to_question_expected_output.csv'
), 'r').read())
os.remove('test 35.csv')
def test_single_multiple_choice_field___single_response_Answered_frog(
self):
csv_exporter = csv.CsvExporter(
json.load(
open(
os.path.join(
self.path_to_test_files,
'unit_test_single_multiple_choice_field___single_response_Answered_frog.json'
), 'r')))
csv_exporter.save_converted_audit_to_file('test 24.csv',
allow_overwrite=True)
self.assertEqual(
open('test 24.csv', 'r').read(),
open(
os.path.join(
self.path_to_test_files,
'unit_test_single_multiple_choice_field___single_response_Answered_frog_expected_output.csv'
), 'r').read())
os.remove('test 24.csv')
def test_single_address_field_used_map___15_Gilbert_St__Dover_Heights_NSW_2030__Australia(
self):
csv_exporter = csv.CsvExporter(
json.load(
open(
os.path.join(
self.path_to_test_files,
'unit_test_single_address_field_used_map___15_Gilbert_St__Dover_Heights_NSW_2030__Australia.json'
), 'r')))
csv_exporter.save_converted_audit_to_file('test 31.csv',
allow_overwrite=True)
self.assertEqual(
open('test 31.csv', 'r').read(),
open(
os.path.join(
self.path_to_test_files,
'unit_test_single_address_field_used_map___15_Gilbert_St__Dover_Heights_NSW_2030__Australia_expected_output.csv'
), 'r').read())
os.remove('test 31.csv')
def test_single_multi_line_text_field_500_unicode_characters__on_5_lines(
self):
csv_exporter = csv.CsvExporter(
json.load(
open(
os.path.join(
self.path_to_test_files,
'unit_test_single_multi_line_text_field_500_unicode_characters__on_5_lines.json'
), 'r')))
csv_exporter.save_converted_audit_to_file('test 26.csv',
allow_overwrite=True)
self.assertEqual(
open('test 26.csv', 'r').read(),
open(
os.path.join(
self.path_to_test_files,
'unit_test_single_multi_line_text_field_500_unicode_characters__on_5_lines_expected_output.csv'
), 'r').read())
os.remove('test 26.csv')
def sync_exports(logger, sc_client, settings):
"""
Perform sync, exporting documents modified since last execution
:param logger: the logger
:param sc_client: instance of SDK object
:param settings: dict containing settings values
"""
export_formats = settings['export_formats']
export_profiles = settings['export_profiles']
filename_item_id = settings['filename_item_id']
export_path = settings['export_path']
timezone = settings['timezone']
export_inactive_items_to_csv = settings['export_inactive_items_to_csv']
media_sync_offset = settings['media_sync_offset_in_seconds']
last_successful = get_last_successful(logger)
results = sc_client.discover_audits(modified_after=last_successful)
if results is not None:
logger.info(str(results['total']) + ' audits discovered')
export_count = 1
export_total = results['total']
for audit in results['audits']:
logger.info('Processing audit (' + str(export_count) + '/' +
str(export_total) + ')')
modified_at = dateutil.parser.parse(audit['modified_at'])
now = datetime.datetime.utcnow()
elapsed_time_difference = (pytz.utc.localize(now) - modified_at)
if elapsed_time_difference > datetime.timedelta(
seconds=media_sync_offset):
export_count += 1
audit_id = audit['audit_id']
logger.info('downloading ' + audit_id)
audit_json = sc_client.get_audit(audit_id)
template_id = audit_json['template_id']
if template_id in export_profiles.keys():
export_profile_id = export_profiles[template_id]
else:
export_profile_id = None
if filename_item_id is not None:
export_filename = parse_export_filename(
audit_json['header_items'], filename_item_id)
if export_filename is None:
export_filename = audit_id
else:
export_filename = audit_id
for export_format in export_formats:
if export_format in ['pdf', 'docx']:
export_doc = sc_client.get_export(
audit_id, timezone, export_profile_id,
export_format)
save_exported_document(logger, export_path, export_doc,
export_filename, export_format)
elif export_format == 'json':
export_doc = json.dumps(audit_json, indent=4)
save_exported_document(logger, export_path, export_doc,
export_filename, export_format)
elif export_format == 'csv':
csv_exporter = csvExporter.CsvExporter(
audit_json, export_inactive_items_to_csv)
csv_export_filename = audit_json['template_id']
csv_exporter.append_converted_audit_to_bulk_export_file(
os.path.join(export_path,
csv_export_filename + '.csv'))
elif export_format == 'media':
media_export_path = os.path.join(
export_path, 'media', export_filename)
extension = 'jpg'
media_id_list = get_media_from_audit(
logger, audit_json)
for media_id in media_id_list:
logger.info(
"Saving media_{0} to disc.".format(media_id))
media_file = sc_client.get_media(
audit_id, media_id)
media_export_filename = media_id
save_exported_media_to_file(
logger, media_export_path, media_file,
media_export_filename, extension)
elif export_format == 'web-report-link':
web_report_link = sc_client.get_web_report(audit_id)
web_report_data = [
template_id,
csvExporter.get_json_property(
audit_json, 'template_data', 'metadata',
'name'), audit_id,
csvExporter.get_json_property(
audit_json, 'audit_data', 'name'),
web_report_link
]
save_web_report_link_to_file(logger, export_path,
web_report_data)
logger.debug('setting last modified to ' +
audit['modified_at'])
update_sync_marker_file(audit['modified_at'])
else:
logger.info(
'Audit\'s modified_at value is less than {0} seconds in the past, skipping for now!'
.format(media_sync_offset))
def export_audit_sql(logger, settings, audit_json, get_started):
"""
Save audit to a database.
:param logger: The logger
:param settings: Settings from command line and configuration file
:param audit_json: Audit JSON
:get_started: Tuple containing settings
"""
database = get_started[2]
session = get_started[1]
csv_exporter = csvExporter.CsvExporter(
audit_json, settings[EXPORT_INACTIVE_ITEMS_TO_CSV])
df = csv_exporter.audit_table
df = pd.DataFrame.from_records(df, columns=SQL_HEADER_ROW)
df['DatePK'] = pd.to_datetime(df['DateModified']).values.astype(
np.int64) // 10**6
if settings[DB_TYPE].startswith('postgres'):
df.replace({'DateCompleted': ''}, np.datetime64(None), inplace=True)
df.replace({'ConductedOn': ''}, np.datetime64(None), inplace=True)
empty_value = np.nan
empty_score = empty_value
elif settings[DB_TYPE].startswith('mysql'):
df.replace(
{
'ItemScore': '',
'ItemMaxScore': '',
'ItemScorePercentage': ''
},
0.0,
inplace=True)
empty_value = '1970-01-01T00:00:01'
df.replace({'DateCompleted': ''}, empty_value, inplace=True)
df.replace({'ConductedOn': ''}, empty_value, inplace=True)
df['DateStarted'] = pd.to_datetime(df['DateStarted'])
df['DateCompleted'] = pd.to_datetime(df['DateCompleted'])
df['DateModified'] = pd.to_datetime(df['DateModified'])
df['ConductedOn'] = pd.to_datetime(df['ConductedOn'],
format='%Y-%m-%d %H:%M:%S',
utc=False)
df['ConductedOn'] = df['ConductedOn'].dt.tz_localize(None)
empty_value = None
empty_score = 0.0
else:
empty_value = None
empty_score = empty_value
df.replace({
'ItemScore': '',
'ItemMaxScore': '',
'ItemScorePercentage': ''
},
empty_score,
inplace=True)
df.replace(r'^\s*$', empty_value, regex=True, inplace=True)
df['SortingIndex'] = range(1, len(df) + 1)
df_dict = df.to_dict(orient='records')
try:
session.bulk_insert_mappings(database, df_dict)
except KeyboardInterrupt:
logger.warning('Interrupted by user, exiting.')
session.rollback()
session.close()
sys.exit(0)
except OperationalError as ex:
session.rollback()
session.close()
logger.warning(
'Something went wrong. Here are the details: {}'.format(ex))
except IntegrityError as ex:
# If the bulk insert fails, we do a slower merge
logger.warning('Duplicate found, attempting to update')
session.rollback()
for row in df_dict:
row_to_dict = database(**row)
session.merge(row_to_dict)
logger.debug('Row successfully updated.')
session.commit()
