def all_reports():
cmsws = []
number = 0
file_names = filedialog.askopenfilenames(
title='Select database file',
filetypes=(('sqlite files', '*.sqlite'), ('all files', '*.*')))
print('Processing file selection', end='')
file_names = list(file_names)
file_names.sort()
for file in file_names:
cmsws.append(str(cmsw_read.cmsw_id_read(file)))
print('.', end='')
for cmsw in cmsws:
cmsw = cmsw.replace('/', '')
cmsws[number] = cmsw
number += 1
print('')
print('Input ready, beginning report...')
try:
rods_rockin_data.excel_write(file_names, cmsws)
sales_data.write(file_names, cmsws)
DyeMinish_data.excel_flag_write(file_names, cmsws)
# DyeMinish_data.excel_destructive_write(file_names, cmsws)
except Exception:
logging.exception('Unexpected issue')
print('Done')
def dyeminish_report(self):
"""Opens file browser, processes chosen files, checks the delete flag,
then calls the appropriate write method
"""
file_names = filedialog.askopenfilenames(
title='Select database file',
filetypes=(('sqlite files', '*.sqlite'), ('all files', '*.*')))
file_names = list(file_names)
file_names.sort()
cmsws = []
print('Processing file selection', end='')
for file in file_names:
cmsws.append(str(cmsw_read.cmsw_id_read(file)))
print('.', end='')
number = 0
for cmsw in cmsws:
cmsws[number] = cmsw
number += 1
print('')
print('Input ready, beginning report...')
try:
# if not self.delete.get():
DyeMinish_data.excel_flag_write(file_names, cmsws)
# else:
# DyeMinish_data.excel_destructive_write(file_names, cmsws)
except Exception:
logging.exception('Unexpected issue')
print('Done')
def sales_report():
"""Opens file browser, processes chosen files, then calls the write method"""
cmsws = []
number = 0
file_names = filedialog.askopenfilenames(
title='Select database file',
filetypes=(('sqlite files', '*.sqlite'), ('all files', '*.*')))
print('Processing file selection', end='')
file_names = list(file_names)
for file in file_names:
cmsws.append(str(cmsw_read.cmsw_id_read(file)))
print('.', end='')
for cmsw in cmsws:
cmsw = cmsw.replace('/', '')
cmsws[number] = cmsw
number += 1
print('')
print('Input ready, beginning report...')
try:
sales_data_inkind.write(file_names, cmsws)
except Exception:
logging.exception('Unexpected issue')
print('Done')
def injection_table(file_names, cmsw):
"""Connects to an individual database and determines which injections were puffs, injections,
and leaves some uncatagorized to be classified by a person looking at the surrounding data
"""
xlsx2_name = str(cmsw).replace('s', '') + 'rods-detailed-data.xlsx'
wb = Workbook(write_only=True)
data_sheet = wb.create_sheet()
yellow = PatternFill(fill_type="solid", start_color=YLW, end_color=YLW)
green = PatternFill(fill_type="solid", start_color=GRN, end_color=GRN)
alignment = Alignment(wrap_text=True, shrink_to_fit=True)
data_sheet.title = 'Sheet1'
data_sheet.row_dimensions.group(2, 3, hidden=True)
data_sheet.column_dimensions.group('B', 'E', hidden=True)
data_sheet.column_dimensions.group('H', 'R', hidden=True)
data_sheet.column_dimensions.group('U', 'U', hidden=True)
data_sheet.column_dimensions.group('X', 'AC', hidden=True)
data_sheet.column_dimensions.group('AF', 'AI', hidden=True)
# values for width do not match actual values
# columns ending in .42 are .71 over the actual width
# columns ending in .01 are .72 over the actual width
data_sheet.column_dimensions['A'].width = 18.42
data_sheet.column_dimensions['G'].width = 10.01
data_sheet.column_dimensions['V'].width = 12.01
data_sheet.column_dimensions['W'].width = 11.01
data_sheet.column_dimensions['AD'].width = 14.42
data_sheet.column_dimensions['AE'].width = 10.42
data_sheet.column_dimensions['AJ'].width = 12.42
data_sheet.freeze_panes = 'A2'
frmcell = [
WriteOnlyCell(ws=data_sheet, value='Contrast Aspirated'),
WriteOnlyCell(ws=data_sheet, value='Contrast Diverted'),
WriteOnlyCell(ws=data_sheet, value='Percent Saved'),
WriteOnlyCell(ws=data_sheet, value='Contrast to Patient'),
WriteOnlyCell(ws=data_sheet, value='Cumulative'),
WriteOnlyCell(ws=data_sheet, value='Flow Rate from Syringe'),
WriteOnlyCell(ws=data_sheet, value='Flow Rate to Patient'),
WriteOnlyCell(ws=data_sheet, value='Volume Attempted')
]
empty_cell = WriteOnlyCell(ws=data_sheet, value='')
for cell in range(len(frmcell)):
frmcell[cell].font = Font(bold=True)
cases = [
[
empty_cell, empty_cell, empty_cell, empty_cell, empty_cell,
empty_cell, frmcell[0], empty_cell, empty_cell, empty_cell,
empty_cell, empty_cell, empty_cell, empty_cell, empty_cell,
empty_cell, empty_cell, empty_cell, frmcell[1], frmcell[2],
empty_cell, frmcell[3], frmcell[4], empty_cell, empty_cell,
empty_cell, empty_cell, empty_cell, empty_cell, frmcell[5],
frmcell[6], empty_cell, empty_cell, empty_cell, empty_cell,
frmcell[7]
],
[
empty_cell, empty_cell, empty_cell, empty_cell, empty_cell,
empty_cell, empty_cell, empty_cell, empty_cell, empty_cell,
empty_cell, empty_cell, empty_cell, empty_cell, empty_cell,
empty_cell, empty_cell, empty_cell, empty_cell, empty_cell,
empty_cell, empty_cell, empty_cell, empty_cell, empty_cell,
empty_cell, empty_cell, empty_cell, empty_cell, empty_cell,
empty_cell, empty_cell, empty_cell, empty_cell, empty_cell,
empty_cell
],
[
WriteOnlyCell(ws=data_sheet, value='Time Stamp'),
WriteOnlyCell(ws=data_sheet, value='Syringe Revision'),
WriteOnlyCell(ws=data_sheet, value='PMDV Revision'),
WriteOnlyCell(ws=data_sheet, value='Syringe Address'),
WriteOnlyCell(ws=data_sheet, value='PMDV Address'),
WriteOnlyCell(ws=data_sheet, value='Injection or Aspiration'),
WriteOnlyCell(ws=data_sheet, value='Aspirating Contrast'),
WriteOnlyCell(ws=data_sheet, value='Replacing Device'),
WriteOnlyCell(ws=data_sheet, value='Current DyeVert Diameter'),
WriteOnlyCell(ws=data_sheet, value='Current Syringe Diameter'),
WriteOnlyCell(ws=data_sheet,
value='Starting Syringe Plunger Position'),
WriteOnlyCell(ws=data_sheet,
value='Ending Syringe Plunger Position'),
WriteOnlyCell(ws=data_sheet,
value='Syringe Linear Plunger Position'),
WriteOnlyCell(ws=data_sheet, value='Volume(Injected / Aspirated)'),
WriteOnlyCell(
ws=data_sheet,
value='Starting DyeVert Plus Reservoir Plunger Position'),
WriteOnlyCell(
ws=data_sheet,
value='Ending DyeVert Plus Reservoir Plunger Position'),
WriteOnlyCell(
ws=data_sheet,
value='DyeVert Plus Reservoir Linear Plunger Position'),
WriteOnlyCell(ws=data_sheet,
value='DyeVert Plus Reservoir Volume Diverted'),
WriteOnlyCell(
ws=data_sheet,
value='DyeVert Plus Reservoir Contrast Volume Diverted'),
WriteOnlyCell(ws=data_sheet, value='PercentContrastSaved'),
WriteOnlyCell(ws=data_sheet,
value='Total Injection Volume to Patient'),
WriteOnlyCell(ws=data_sheet, value='Volume of Contrast Injected'),
WriteOnlyCell(ws=data_sheet,
value='Cumulative Contrast Volume Injected'),
WriteOnlyCell(ws=data_sheet, value='Volume of Other Injected'),
WriteOnlyCell(ws=data_sheet,
value='Starting Contrast Percentage in Syringe'),
WriteOnlyCell(ws=data_sheet,
value='Ending Contrast Percentage in Syringe'),
WriteOnlyCell(
ws=data_sheet,
value='Starting Contrast Percentage in DyeVert Plus Reservoir'
),
WriteOnlyCell(
ws=data_sheet,
value='Ending Contrast Percentage in DyeVert Plus Reservoir'),
WriteOnlyCell(ws=data_sheet, value='Duration'),
WriteOnlyCell(ws=data_sheet, value='Flow Rate from Syringe'),
WriteOnlyCell(ws=data_sheet, value='Flow Rate to Patient'),
WriteOnlyCell(ws=data_sheet, value='Contrast Line Pressure'),
WriteOnlyCell(ws=data_sheet,
value='DyeVert Plus Stopcock Position'),
WriteOnlyCell(ws=data_sheet, value='System IsSystemPaused'),
empty_cell, empty_cell
]
]
case_number = 0
for file_name in file_names:
con = sqlite.connect(file_name)
with con:
cur = con.cursor()
cur.execute('SELECT * FROM CMSWCases')
rows = cur.fetchall()
case_id_number = {}
global TIME_STAMP, SYRINGE_REVISION, PMDV_REVISION, IS_AN_INJECTION, IS_ASPIRATING_CONTRAST, \
DYEVERT_DIAMETER, SYRINGE_DIAMETER, STARTING_SYRINGE_POSITION, ENDING_SYRINGE_POSITION, \
LINEAR_SYRINGE_MOVEMENT, SYRINGE_VOLUME_INJECTED_OR_ASPIRATED, STARTING_DYEVERT_POSITION, \
ENDING_DYEVERT_POSITION, LINEAR_DYEVERT_MOVEMENT, DIVERT_VOLUME_DIVERTED, \
DYEVERT_CONTRAST_VOLUME_DIVERTED, PERCENT_CONTRAST_SAVED, INJECTION_VOLUME_TO_PATIENT, \
CONTRAST_VOLUME_TO_PATIENT, CUMULATIVE_CONTRAST_VOLUME_TO_PATIENT, OTHER_VOLUME_TO_PATIENT, \
STARTING_CONTRAST_PERCENT_IN_SYRINGE, STARTING_CONTRAST_PERCENT_IN_DYEVERT, \
ENDING_CONTRAST_PERCENT_IN_DYEVERT, DURATION, FLOW_RATE_TO_FROM_SYRINGE, FLOW_RATE_TO_PATIENT, \
PREDOMINANT_CONTRAST_LINE_PRESSURE, STARTING_DYEVERT_STOPCOCK_POSITION, IS_SYSTEM_PAUSED, \
ENDING_CONTRAST_PERCENT_IN_SYRINGE, SYRINGE_ADDRESS, PMDV_ADDRESS, IS_DEVICE_REPLACEMENT, \
SERIAL_NUMBER, DATE_OF_PROCEDURE, THRESHOLD_VOLUME, ATTEMPTED_CONTRAST_INJECTION_VOLUME, \
DIVERTED_CONTRAST_VOLUME, CUMULATIVE_VOLUME_TO_PATIENT, PERCENTAGE_CONTRAST_DIVERTED, TOTAL_DURATION
if not rows == []:
if rows[0][2] == '2.1.56' or rows[0][2] == '2.1.24' or rows[0][2] == '2.1.67' or \
rows[0][2] == '2.0.1981' or rows[0][2] == '2.0.2013':
TIME_STAMP = 2
SYRINGE_REVISION = 3
PMDV_REVISION = 4
IS_AN_INJECTION = 5
IS_ASPIRATING_CONTRAST = 6
DYEVERT_DIAMETER = 7
SYRINGE_DIAMETER = 8
STARTING_SYRINGE_POSITION = 9
ENDING_SYRINGE_POSITION = 10
LINEAR_SYRINGE_MOVEMENT = 11
SYRINGE_VOLUME_INJECTED_OR_ASPIRATED = 12
STARTING_DYEVERT_POSITION = 13
ENDING_DYEVERT_POSITION = 14
LINEAR_DYEVERT_MOVEMENT = 15
DIVERT_VOLUME_DIVERTED = 16
DYEVERT_CONTRAST_VOLUME_DIVERTED = 17
PERCENT_CONTRAST_SAVED = 18
INJECTION_VOLUME_TO_PATIENT = 19
CONTRAST_VOLUME_TO_PATIENT = 20
CUMULATIVE_CONTRAST_VOLUME_TO_PATIENT = 21
OTHER_VOLUME_TO_PATIENT = 22
STARTING_CONTRAST_PERCENT_IN_SYRINGE = 24
STARTING_CONTRAST_PERCENT_IN_DYEVERT = 25
ENDING_CONTRAST_PERCENT_IN_DYEVERT = 26
DURATION = 27
FLOW_RATE_TO_FROM_SYRINGE = 28
FLOW_RATE_TO_PATIENT = 29
PREDOMINANT_CONTRAST_LINE_PRESSURE = 30
STARTING_DYEVERT_STOPCOCK_POSITION = 31
IS_SYSTEM_PAUSED = 32
ENDING_CONTRAST_PERCENT_IN_SYRINGE = 33
SYRINGE_ADDRESS = 34
PMDV_ADDRESS = 35
IS_DEVICE_REPLACEMENT = 36
else:
SERIAL_NUMBER = 3
DATE_OF_PROCEDURE = 5
THRESHOLD_VOLUME = 8
ATTEMPTED_CONTRAST_INJECTION_VOLUME = 13
DIVERTED_CONTRAST_VOLUME = 14
CUMULATIVE_VOLUME_TO_PATIENT = 15
PERCENTAGE_CONTRAST_DIVERTED = 16
TOTAL_DURATION = 20
TIME_STAMP = 2
SYRINGE_REVISION = 3
PMDV_REVISION = 4
IS_AN_INJECTION = 8
IS_ASPIRATING_CONTRAST = 9
DYEVERT_DIAMETER = 10
SYRINGE_DIAMETER = 11
STARTING_SYRINGE_POSITION = 12
ENDING_SYRINGE_POSITION = 13
LINEAR_SYRINGE_MOVEMENT = 14
SYRINGE_VOLUME_INJECTED_OR_ASPIRATED = 15
STARTING_DYEVERT_POSITION = 16
ENDING_DYEVERT_POSITION = 17
LINEAR_DYEVERT_MOVEMENT = 18
DIVERT_VOLUME_DIVERTED = 19
DYEVERT_CONTRAST_VOLUME_DIVERTED = 20
PERCENT_CONTRAST_SAVED = 21
INJECTION_VOLUME_TO_PATIENT = 22
CONTRAST_VOLUME_TO_PATIENT = 23
CUMULATIVE_CONTRAST_VOLUME_TO_PATIENT = 24
OTHER_VOLUME_TO_PATIENT = 25
STARTING_CONTRAST_PERCENT_IN_SYRINGE = 27
STARTING_CONTRAST_PERCENT_IN_DYEVERT = 29
ENDING_CONTRAST_PERCENT_IN_DYEVERT = 30
DURATION = 31
FLOW_RATE_TO_FROM_SYRINGE = 32
FLOW_RATE_TO_PATIENT = 33
PREDOMINANT_CONTRAST_LINE_PRESSURE = 34
STARTING_DYEVERT_STOPCOCK_POSITION = 35
IS_SYSTEM_PAUSED = 36
ENDING_CONTRAST_PERCENT_IN_SYRINGE = 28
SYRINGE_ADDRESS = 5
PMDV_ADDRESS = 4
IS_DEVICE_REPLACEMENT = 7
for row in rows:
case_id_number[row[CMSW_CASE_ID]] = row[CASE_ID][-23:-4]
with con:
cur = con.cursor()
cur.execute('SELECT * FROM CMSWInjections')
rows = cur.fetchall()
for row in rows:
perc_saved = WriteOnlyCell(value=row[PERCENT_CONTRAST_SAVED],
ws=data_sheet)
if case_number != row[CASE_ID]:
case_number = row[CASE_ID]
_cmsw = cmsw_read.cmsw_id_read(file_name)
cases.append([
WriteOnlyCell(ws=data_sheet, value='CMSW'), empty_cell,
empty_cell, empty_cell, empty_cell,
WriteOnlyCell(ws=data_sheet, value=_cmsw), empty_cell,
empty_cell, empty_cell, empty_cell, empty_cell,
empty_cell, empty_cell, empty_cell, empty_cell,
empty_cell, empty_cell, empty_cell, empty_cell,
empty_cell, empty_cell
])
cases.append([
WriteOnlyCell(ws=data_sheet, value='Case'), empty_cell,
empty_cell, empty_cell, empty_cell,
WriteOnlyCell(ws=data_sheet,
value=case_id_number[case_number]),
empty_cell, empty_cell, empty_cell, empty_cell,
empty_cell, empty_cell, empty_cell, empty_cell,
empty_cell, empty_cell, empty_cell, empty_cell,
empty_cell, empty_cell, empty_cell
])
if row[IS_AN_INJECTION] == 1:
inj_asp = 'INJ'
else:
inj_asp = 'ASP'
if row[IS_ASPIRATING_CONTRAST] == 1:
contrast_asp = 'Yes'
else:
contrast_asp = ''
if row[IS_DEVICE_REPLACEMENT] == 1:
replacement = 'Yes'
else:
replacement = ''
if inj_asp == 'INJ':
perc_saved.font = Font(bold=True)
if row[CONTRAST_VOLUME_TO_PATIENT] + row[
DYEVERT_CONTRAST_VOLUME_DIVERTED] >= 3:
puff_inj = WriteOnlyCell(value='Injection',
ws=data_sheet)
puff_inj.font = Font(bold=True)
elif row[CONTRAST_VOLUME_TO_PATIENT] + row[
DYEVERT_CONTRAST_VOLUME_DIVERTED] <= 2:
puff_inj = WriteOnlyCell(value='Puff', ws=data_sheet)
puff_inj.font = Font(bold=True)
elif row[FLOW_RATE_TO_FROM_SYRINGE] >= 2.5:
puff_inj = WriteOnlyCell(value='Injection',
ws=data_sheet)
puff_inj.font = Font(bold=True)
elif row[FLOW_RATE_TO_FROM_SYRINGE] <= 2:
puff_inj = WriteOnlyCell(value='Puff', ws=data_sheet)
puff_inj.font = Font(bold=True)
else:
debug_msg = 'Event ' + str(row[0]) + ' in cmsw ' + str(cmsw_read.cmsw_id_read(file_name)) + \
', case ' + str(row[CASE_ID]) + ' matched neither type'
logging.warning(debug_msg)
print(debug_msg)
puff_inj = empty_cell
else:
puff_inj = empty_cell
perc_saved.font = Font(bold=False)
for entry in range(len(row)):
if row[entry] is None:
newrow = list(row)
newrow[entry] = '0'
row = newrow
cases.append([
WriteOnlyCell(ws=data_sheet, value=row[TIME_STAMP]),
WriteOnlyCell(ws=data_sheet, value=row[SYRINGE_REVISION]),
WriteOnlyCell(ws=data_sheet, value=row[PMDV_REVISION]),
WriteOnlyCell(ws=data_sheet, value=row[SYRINGE_ADDRESS]),
WriteOnlyCell(ws=data_sheet, value=row[PMDV_ADDRESS]),
WriteOnlyCell(ws=data_sheet, value=inj_asp),
WriteOnlyCell(ws=data_sheet, value=contrast_asp),
WriteOnlyCell(ws=data_sheet, value=replacement),
WriteOnlyCell(ws=data_sheet, value=row[DYEVERT_DIAMETER]),
WriteOnlyCell(ws=data_sheet, value=row[SYRINGE_DIAMETER]),
WriteOnlyCell(ws=data_sheet,
value=row[STARTING_SYRINGE_POSITION]),
WriteOnlyCell(ws=data_sheet,
value=row[ENDING_SYRINGE_POSITION]),
WriteOnlyCell(ws=data_sheet,
value=row[LINEAR_SYRINGE_MOVEMENT]),
WriteOnlyCell(
ws=data_sheet,
value=row[SYRINGE_VOLUME_INJECTED_OR_ASPIRATED]),
WriteOnlyCell(ws=data_sheet,
value=row[STARTING_DYEVERT_POSITION]),
WriteOnlyCell(ws=data_sheet,
value=row[ENDING_DYEVERT_POSITION]),
WriteOnlyCell(ws=data_sheet,
value=row[LINEAR_DYEVERT_MOVEMENT]),
WriteOnlyCell(ws=data_sheet,
value=row[DIVERT_VOLUME_DIVERTED]),
WriteOnlyCell(ws=data_sheet,
value=round(
row[DYEVERT_CONTRAST_VOLUME_DIVERTED],
2)), perc_saved,
WriteOnlyCell(ws=data_sheet,
value=row[INJECTION_VOLUME_TO_PATIENT]),
WriteOnlyCell(ws=data_sheet,
value=round(row[CONTRAST_VOLUME_TO_PATIENT],
2)),
WriteOnlyCell(
ws=data_sheet,
value=round(row[CUMULATIVE_CONTRAST_VOLUME_TO_PATIENT],
2)),
WriteOnlyCell(ws=data_sheet,
value=row[OTHER_VOLUME_TO_PATIENT]),
WriteOnlyCell(
ws=data_sheet,
value=row[STARTING_CONTRAST_PERCENT_IN_SYRINGE]),
WriteOnlyCell(
ws=data_sheet,
value=row[ENDING_CONTRAST_PERCENT_IN_SYRINGE]),
WriteOnlyCell(
ws=data_sheet,
value=row[STARTING_CONTRAST_PERCENT_IN_DYEVERT]),
WriteOnlyCell(
ws=data_sheet,
value=row[ENDING_CONTRAST_PERCENT_IN_DYEVERT]),
WriteOnlyCell(ws=data_sheet, value=row[DURATION]),
WriteOnlyCell(ws=data_sheet,
value=round(
float(row[FLOW_RATE_TO_FROM_SYRINGE]),
2)),
WriteOnlyCell(ws=data_sheet,
value=round(float(row[FLOW_RATE_TO_PATIENT]),
2)),
WriteOnlyCell(
ws=data_sheet,
value=row[PREDOMINANT_CONTRAST_LINE_PRESSURE]),
WriteOnlyCell(
ws=data_sheet,
value=row[STARTING_DYEVERT_STOPCOCK_POSITION]),
WriteOnlyCell(ws=data_sheet, value=row[IS_SYSTEM_PAUSED]),
WriteOnlyCell(ws=data_sheet, value=''),
WriteOnlyCell(
ws=data_sheet,
value=round(
float(row[CONTRAST_VOLUME_TO_PATIENT]) +
float(row[DYEVERT_CONTRAST_VOLUME_DIVERTED]), 2)),
puff_inj
])
cases[-1][29].font = Font(color=BLUE)
cases[-1][30].font = Font(color=BLUE)
print('Applying formatting', end='')
for case in range(len(cases)):
if case % 10000 == 0:
print('.', end='')
for cell in range(len(cases[case])):
_cell = cases[case][cell]
_cell.alignment = alignment
if cases[case][5].internal_value == 'INJ' and _cell != empty_cell:
if cases[case][19].internal_value == 0:
_cell.fill = yellow
