def process_all_reports(self, additional_reports=None):
"""
Process all reports in folder plus more with the additional reports list
:param additional_reports: [{'name': '', 'path': ''}]
"""
self.xlsx = None
self.reports_names_paths += self._set_reports_names_paths(
additional_reports) if additional_reports else []
for report in self.reports_names_paths:
print('SQL: Running ' + report['name'])
query_result = self._run(report['path'])
if query_result:
try:
if not self.xlsx:
self.xlsx = XlsxTools()
self.xlsx.create_document(query_result, report['name'],
self.xlsx_output_path)
else:
self.xlsx.add_work_sheet(query_result, report['name'])
except PermissionError:
print('Close the file for f# sake.')
# self.set_created_by(query_result)
self.set_modified_by(query_result)
if self.xlsx:
self.email()
def output_to_temp_xlsx_file(contents: List[Dict], timestamp=''):
if not timestamp:
timestamp = datetime.datetime.now().strftime('%d%b%y_%H%M')
results_file = os.path.join(
tempfile.gettempdir(), 'csc_calls_analysis_{0}.xlsx'.format(timestamp))
xlsx = XlsxTools()
xlsx.create_document(contents, 'csc analysis', results_file)
return results_file
def import_file(self,
input_file_path,
output_pipe_folder,
table,
sheet_name='Sheet1',
delimiter='|',
header_row_cell_value=''):
if not os.access(input_file_path, os.R_OK):
raise FileNotFoundError('Can\'t acccess file: ' + input_file_path)
file_path_no_ext, file_extension = os.path.splitext(input_file_path)
file_out = ''
if file_extension == '.xlsb' or file_extension == '.xls' or file_extension == '.xlxm':
# Prepare xlsb files into xlsx so openpyxl can read them
xl = Excel()
xl.open(input_file_path, read_only=True)
save_to = os.path.join(output_pipe_folder,
os.path.basename(input_file_path) + '.xlsx')
xl.save_as(save_to, True)
xl.close()
file_out = os.path.join(output_pipe_folder,
os.path.basename(input_file_path) + '.csv')
xlsx = XlsxTools()
xlsx.xlsx_to_csv(save_to,
file_out,
sheet_name,
delimiter,
header_row_cell_value=header_row_cell_value)
if file_extension == '.xlsx':
file_out = os.path.join(
output_pipe_folder,
os.path.basename(file_path_no_ext) + '.csv')
xlsx = XlsxTools()
xlsx.xlsx_to_csv(input_file_path,
file_out,
sheet_name,
delimiter,
header_row_cell_value=header_row_cell_value)
if file_extension == '.csv':
file_out = os.path.join(output_pipe_folder,
os.path.basename(input_file_path))
csv_tools.csv_to_pipe(input_file_path, file_out, delimiter)
if file_out:
self._sql_import_table(file_out, table)
class Overlord:
"""
Looks in an input_folder of sql scripts, and will email Excel result to recipients in "to.txt" found in same folder
v0.1: 14/Mar/16, made into a class 15/Mar/16
v0.2: 22/Mar/16: moved to a folder input, with optional dictionary
v0.3: 30/Mar/16: added in "which reports" were run in to body of email & cleaned up additional_reports
"""
def __init__(self, input_folder):
server = 'SERVER'
database = 'DATABASE'
username = '******'
password = '******'
self.db = QueryDB(server, database, username, password)
self.xlsx_output_path = os.path.join(tempfile.gettempdir(),
'Lorenzo Auto DQ.xlsx')
self.created_by = []
self.modified_by = []
self.xlsx = None
self.names_paths = self.get_names_paths_from_folder(input_folder)
self.reports_names_paths = self._set_reports_names_paths(
self.names_paths)
self.to = '*****@*****.**'
for x in self.names_paths:
if x['name'] == 'to':
self.to = open(x['path']).read()
def process_all_reports(self, additional_reports=None):
"""
Process all reports in folder plus more with the additional reports list
:param additional_reports: [{'name': '', 'path': ''}]
"""
self.xlsx = None
self.reports_names_paths += self._set_reports_names_paths(
additional_reports) if additional_reports else []
for report in self.reports_names_paths:
print('SQL: Running ' + report['name'])
query_result = self._run(report['path'])
if query_result:
try:
if not self.xlsx:
self.xlsx = XlsxTools()
self.xlsx.create_document(query_result, report['name'],
self.xlsx_output_path)
else:
self.xlsx.add_work_sheet(query_result, report['name'])
except PermissionError:
print('Close the file for f# sake.')
# self.set_created_by(query_result)
self.set_modified_by(query_result)
if self.xlsx:
self.email()
def _run(self, sql_path):
return self.db.exec_sql(open(sql_path).read())
def set_created_by(self, data):
# Analyse who created items
for row in data:
if 'Service Point Created by' in row.keys():
self.created_by.append(row['Service Point Created by'])
self.created_by = list(set(self.created_by))
def set_modified_by(self, data):
# Analyse who modified items
for row in data:
if 'Service Point Modified by' in row.keys():
self.modified_by.append(row['Service Point Modified by'])
if 'Session modified by' in row.keys():
self.modified_by.append(row['Session modified by'])
if 'Modified by' in row.keys():
self.modified_by.append(row['Modified by'])
self.modified_by = list(set(self.modified_by))
def email(self):
email = Outlook()
body = 'Please see attached document for Lorenzo DQ.\n'
body += '\nReports processed are: ' + ', '.join(
[x['name'] for x in self.reports_names_paths]) + '\n'
if self.created_by:
body += 'Created by includes: ' + ', '.join(self.created_by) + '\n'
if self.modified_by:
body += 'Modified by includes: ' + ', '.join(
self.modified_by) + '\n'
body += '\nKind Regards,\nOverlord.'
email.send(True,
self.to,
'Lorenzo DQ Report',
body,
attachments=[self.xlsx_output_path])
@staticmethod
def _set_reports_names_paths(names_paths):
report_names_paths = []
for report in names_paths:
_, ext = os.path.splitext(report['path'])
if ext == '.sql':
report_names_paths.append(report)
return report_names_paths
@staticmethod
def get_names_paths_from_folder(folder):
"""
Scans folder for files, outputs list of dictionaries with {'name': ... and 'path'...}
:param folder: input folder
:return: [{'name': '', 'path': ''}] name is without extension
"""
names_and_paths = []
for file in os.listdir(folder):
if os.path.isfile(os.path.join(folder, file)):
names_and_paths += [{
'name': os.path.splitext(file)[0],
'path': os.path.join(folder, file)
}]
return names_and_paths
def main(process_xlsb=False):
if process_xlsb:
# Prepare xlsb files into xlsx so openpyxl can read them
e = excel_com.Excel()
# File locations
dcs_users_original_file = r'I:\Lorenzo Implementation\Documentation\System Configuration\Data Migration\Users\DCS_USR_v6_LRC2.7_RVJ_20150904.xlsb'
dcs_users_save_to = r'I:\Lorenzo Implementation\Documentation\System Configuration\Data Migration\non-dcs iffs\DCS_Users.xlsx'
dcs_app_original_file = r'I:\Lorenzo Implementation\Documentation\System Configuration\Data Migration\Access Planning\DCS_AP_v3_LRC2.7_RVJ_20150904.xlsb'
dcs_app_save_to = r'I:\Lorenzo Implementation\Documentation\System Configuration\Data Migration\non-dcs iffs\DCS_APPs.xlsx'
dcs_locations_original_file = r'I:\Lorenzo Implementation\Documentation\System Configuration\Data Migration\Locations\DCS_Locations_v1_LRC2.7_RVJ_20150902.xlsb'
dcs_locations_save_to = r'I:\Lorenzo Implementation\Documentation\System Configuration\Data Migration\non-dcs iffs\DCS_Locations.xlsx'
# Check files exist!
if not os.access(dcs_users_original_file, os.R_OK):
print('Can\'t find Users DCS: ' + dcs_users_original_file)
exit()
if not os.access(dcs_app_original_file, os.R_OK):
print('Can\'t find Access Plan Profile DCS: ' +
dcs_app_original_file)
exit()
if not os.access(dcs_locations_original_file, os.R_OK):
print('Can\'t find Locations DCS: ' + dcs_locations_original_file)
exit()
print('Converting DCS APPs to xlsx')
e.open(dcs_app_original_file, read_only=True)
e.save_as(dcs_app_save_to, True)
e.close()
print('Converting DCS Users to xlsx')
e.open(dcs_users_original_file, read_only=True)
e.save_as(dcs_users_save_to, True)
e.close()
print('Converting DCS Locations to xlsx')
e.open(dcs_locations_original_file, read_only=True)
e.save_as(dcs_locations_save_to, True)
e.close()
original_files_folder = r'I:\Lorenzo Implementation\Documentation\System Configuration\Data Migration\non-dcs iffs'
pipe_output_folder = r'C:\Cerner Audit\lorenzoDCS'
files_to_process = [
{
'fileInName':
os.path.join(original_files_folder, 'LRD_SERVEVENTSTATUS.xlsx'),
'fileOutName':
os.path.join(pipe_output_folder, 'LRD_SERVEVENTSTATUS.csv'),
'sheetName':
'Sheet1',
'table_name':
'LRD_SERVEVENTSTATUS'
},
{
'fileInName':
os.path.join(original_files_folder, 'LRD_SERVICELOCN_IPED.xlsx'),
'fileOutName':
os.path.join(pipe_output_folder, 'LRD_SERVICELOCN_IPED.csv'),
'sheetName':
'Sheet1',
'table_name':
'LRD_SERVICELOCN_IPED'
},
{
'fileInName':
os.path.join(original_files_folder, 'LRD_SERVICELOCN_OP.xlsx'),
'fileOutName':
os.path.join(pipe_output_folder, 'LRD_SERVICELOCN_OP.csv'),
'sheetName':
'Sheet1',
'table_name':
'LRD_SERVICELOCN_OP'
},
{
'fileInName':
os.path.join(original_files_folder, 'LRD_SERVPOINT_IPED.xlsx'),
'fileOutName':
os.path.join(pipe_output_folder, 'LRD_SERVPOINT_IPED.csv'),
'sheetName':
'Sheet1',
'table_name':
'LRD_SERVPOINT_IPED'
},
{
'fileInName':
os.path.join(original_files_folder, 'LRD_SERVPOINT_OP.xlsx'),
'fileOutName':
os.path.join(pipe_output_folder, 'LRD_SERVPOINT_OP.csv'),
'sheetName':
'Sheet1',
'table_name':
'LRD_SERVPOINT_OP'
},
{
'fileInName':
os.path.join(original_files_folder, 'LRD_SERVPROVDET.xlsx'),
'fileOutName':
os.path.join(pipe_output_folder, 'LRD_SERVPROVDET.csv'),
'sheetName':
'Sheet1',
'table_name':
'LRD_SERVPROVDET'
},
{
'fileInName': os.path.join(original_files_folder,
'LRD_TEAMS.xlsx'),
'fileOutName': os.path.join(pipe_output_folder, 'LRD_TEAMS.csv'),
'sheetName': 'Sheet1',
'table_name': 'LRD_TEAMS'
},
{
'fileInName': os.path.join(original_files_folder,
'DCS_Users.xlsx'),
'fileOutName': os.path.join(pipe_output_folder, 'DCS_Users.csv'),
'sheetName': 'Users',
'table_name': 'DCS_USERS',
'headerRowCellValue': 'Record Number'
},
{
'fileInName':
os.path.join(original_files_folder, 'DCS_APPs.xlsx'),
'fileOutName':
os.path.join(pipe_output_folder, 'DCS_APP_Outpatient.csv'),
'sheetName':
'Outpatient',
'table_name':
'DCS_APP_OUTPATIENT',
'headerRowCellValue':
'profilename'
},
{
'fileInName':
os.path.join(original_files_folder, 'DCS_APPs.xlsx'),
'fileOutName':
os.path.join(pipe_output_folder, 'DCS_APP_Inpatient.csv'),
'sheetName':
'ElectiveAdmission',
'table_name':
'DCS_APP_INPATIENT',
'headerRowCellValue':
'ProfileName'
},
{
'fileInName': os.path.join(original_files_folder,
'DCS_Locations.xlsx'),
'fileOutName': os.path.join(pipe_output_folder,
'DCS_Locations.csv'),
'sheetName': 'Locations',
'table_name': 'DCS_LOCATIONS',
'headerRowCellValue': 'Record Number'
},
{
'fileInName':
os.path.join(original_files_folder, 'LRD_APGROUPING.xlsx'),
'fileOutName':
os.path.join(pipe_output_folder, 'LRD_APGROUPING.csv'),
'sheetName':
'Sheet1',
'table_name':
'LRD_APGROUPING'
},
{
'fileInName': os.path.join(original_files_folder, 'mapping.xlsx'),
'fileOutName': os.path.join(pipe_output_folder, 'mapping.csv'),
'sheetName': 'Sheet1',
'table_name': 'mapping'
},
{
'fileInName':
os.path.join(original_files_folder, 'location_mapping.xlsx'),
'fileOutName':
os.path.join(pipe_output_folder, 'location_mapping.csv'),
'sheetName':
'mapping',
'table_name':
'location_mapping'
},
]
xlsx = XlsxTools()
lr = LorenzoNonDCSIFFs()
for file in files_to_process:
print("Loading: " + file['table_name'])
xlsx.xlsx_to_csv(file['fileInName'],
file['fileOutName'],
file['sheetName'],
delimeter='|',
header_row_cell_value=file['headerRowCellValue']
if 'headerRowCellValue' in file else '')
# TODO: change xlsx_to_csv to the below
# data = xlsx.dict_reader(file['fileInName'],
# file['sheetName'],
# header_row_cell_value=file['headerRowCellValue'] if 'headerRowCellValue' in file else '')
# csv_tools.dict_to_csv(file['fileOutName'], data, '|')
lr.sql_import_table(
os.path.join(pipe_output_folder, file['fileOutName']),
file['table_name'])
def service_point_data_pull_all(folder, results_file_output):
"""
Go through a folder of xlsx files, collating data from each, outputting a csv of all data
:param folder: folder with xlsx files to process
:param results_file_output: csv output path
:return: csv file
"""
exclude = [
# 'PRS - Physiotherapy 20150826b.xlsx',
]
xlsx = XlsxTools()
results = []
for filename in os.listdir(folder):
print(filename)
_, ext = os.path.splitext(filename)
if ext == '.xlsx' and '~' not in filename and filename not in exclude:
try:
file_dict = xlsx.dict_reader(os.path.join(folder, filename),
'clinics')
except KeyError:
try:
file_dict = xlsx.dict_reader(
os.path.join(folder, filename), 'Clinics')
except KeyError:
try:
file_dict = xlsx.dict_reader(
os.path.join(folder, filename), 'Sheet1')
except:
raise
for row in file_dict:
results.append(
OrderedDict([
('Filename', filename),
# Cerner details
('Cerner Template Name', row['Cerner Template Name']
if 'Cerner Template Name' in row else ''),
('Cerner Resource', row['Cerner Resource']
if 'Cerner Resource' in row else ''),
('Cerner Location', row['Cerner Location']
if 'Cerner Location' in row else ''),
('Appointment Type (Concat)',
row['Appointment Type (Concat)']
if 'Appointment Type (Concat)' in row else ''),
# Clinic
('Lorenzo Clinic Name', row['Lorenzo Clinic Name']),
('Clinic ID',
row['Main Identifier'] if 'Main Identifier' in row
else row['Main Identifier TF1']),
# Clinician
('Clinician Main Identifier',
row['Clinician MainIdentifier']
if 'Clinician MainIdentifier' in row else
row['Clinician Main Identifier']
if 'Clinician Main Identifier' in row else ''),
('Care Provider', row['Care Provider']
if 'Care Provider' in row else ''),
# Treatment Function of clinic
('Treatment Function', row['Treatment Function']
if 'Treatment Function' in row else ''),
# Session
('Session Name', row['Session Name']),
('From Date', row['From Date']),
('Frequency', row['Frequency']),
# Location
('Location ID', row['Most used Location']
if 'Most used Location' in row
and row['Most used Location'] != '' else
row['Lorenzo Location']
if 'Lorenzo Location' in row else ''),
('Location Lorenzo Main ID', row['Lorenzo Main ID']
if 'Lorenzo Main ID' in row else ''),
# Session continued
('C&B Service Identifier', ', '.join(
sorted(
row['C&B Service Identifier (again)'].replace(
' ', '').split(',')))
if 'C&B Service Identifier (again)' in row
and row['C&B Service Identifier (again)'] != '' else
', '.join(
sorted(row['C&B Service Identifier'].replace(
' ', '').split(',')))
if 'C&B Service Identifier' in row
and row['C&B Service Identifier'] != '' else ''),
('Slot Start Time', row['Slot Start Time']),
('Slot End Time', row['Slot End Time']),
('Priority', row['Priority']),
('Applicable Apt Types', row['Applicable Apt Types'])
]))
csv_tools.dict_to_csv(results_file_output, results)
]
ftd = FileToDB('SERVER', 'DATABASE', 'USERNAME', 'PASSWORD')
for file in files_to_process:
print("Loading: " + file['table_name'])
ftd.import_file(os.path.join(input_folder, file['file_in_name']),
pipes_folder,
file['table_name'],
file['sheet_name'],
header_row_cell_value=file['header_row_cell_value']
if 'header_row_cell_value' in file else '')
print('Processing results')
db = QueryDB('SERVER', 'DATABASE', 'USERNAME', 'PASSWORD')
results = db.exec_sql('EXEC dbo.Lorenzo_DCS_to_Domain')
if results:
XlsxTools().create_document(results, 'DCS vs Domain', results_file)
else:
print('No results were returned')
print('Add conditional formatting')
wb = load_workbook(results_file)
ws = wb.active
ws.conditional_formatting.add(
'A:N', FormulaRule(formula=['$A1="Domain"'], font=Font(color=RED)))
ws.conditional_formatting.add(
'A:N', FormulaRule(formula=['$A1="DCS"'], font=Font(color='008000')))
pattern_yellow = PatternFill(start_color=YELLOW,
end_color=YELLOW,
fill_type='solid')
ws.conditional_formatting.add(
'G:G',