def test_readxlslist_findheader_p03_xlsx_simple_blankReqCols_col_header(
self):
logger.debug('STARTUP')
result = kvxls.readxls2list_findheader(filenamexlsx, [],
optiondict={'col_header': True},
debug=False)
self.assertEqual(result[0], records[0])
def test_readxlslist_findheader_p10_xlsx_simple_blankReqCols_save_row(
self):
logger.debug('STARTUP')
result = kvxls.readxls2list_findheader(filenamexlsx, [],
optiondict={'save_row': True},
debug=False)
self.assertEqual(result[0]['XLSRow'], 2)
def test_readxlslist_findheader_p01_xlsx_simple_reqcols(self):
logger.debug('STARTUP')
result = kvxls.readxls2list_findheader(filenamexls,
req_cols,
debug=False)
self.assertEqual(result[0], records[0])
self.assertEqual(len(result), len(records))
def test_readxlslist_findheader_p09_xlsx_simple_blankReqCols_convert_dateflds(
self):
logger.debug('STARTUP')
result = kvxls.readxls2list_findheader(
filenamexlsx2, [],
optiondict={'dateflds': ['DateField']},
debug=False)
self.assertEqual(result[0], records2[0])
def test_readxlslist_findheader_p02_xlsx_simple_reqcols_aref_result(self):
logger.debug('STARTUP')
result = kvxls.readxls2list_findheader(
filenamexls,
req_cols,
optiondict={'aref_result': True},
debug=False)
self.assertEqual(result[0], list(records[0].values()))
def test_readxlslist_findheader_f02_xlsx_unique_columns_test(self):
logger.debug('STARTUP')
header = list(records[0].keys())
dupkey = header[0]
header.append(dupkey)
aref = []
for rec in records:
aref.append(list(rec.values()) + [rec[dupkey]])
kvxls.writelist2xls(filenamexlsx3,
aref,
optiondict={'aref_result': True},
debug=False)
with self.assertRaises(Exception) as context:
kvxls.readxls2list_findheader(filenamexlsx3,
req_cols,
optiondict={'unique_column': True},
debug=False)
kvutil.remove_filename(filenamexlsx3, kvutil.functionName())
def test_readxlslist_findheader_f01_xlsx_maxrows_exceeded_in_header_search(
self):
logger.debug('STARTUP')
# create a list of values that are used to create the xls - we have 6 blank lines at the top
aref = [[''], [''], [''], [''], ['']]
aref.append(list(records[0].keys()))
for rec in records:
aref.append(list(rec.values()))
kvxls.writelist2xls(filenamexlsx3,
aref,
optiondict={'no_header': True},
debug=False)
with self.assertRaises(Exception) as context:
kvxls.readxls2list_findheader(filenamexlsx3,
req_cols,
optiondict={'maxrows': 2},
debug=False)
kvutil.remove_filename(filenamexlsx3, kvutil.functionName())
def test_readxlslist_findheader_p07_xlsx_simple_blankReqCols_col_aref_blank_column(
self):
logger.debug('STARTUP')
col_aref = list(records[0].keys())[:-2]
result = kvxls.readxls2list_findheader(filenamexlsx, [],
optiondict={'save_row': True},
col_aref=col_aref,
debug=False)
self.assertTrue('blank001' in result[0].keys())
def test_readxlslist_findheader_p04_xlsx_simple_blankReqCols_no_header_start_row_return_aref(
self):
logger.debug('STARTUP')
result = kvxls.readxls2list_findheader(filenamexlsx, [],
optiondict={
'no_header': True,
'start_row': 2
},
debug=False)
self.assertEqual(result[0], list(records[0].values()))
def test_readxlslist_findheader_p05_xls_simple_blankReqCols_no_header_start_row_col_aref(
self):
logger.debug('STARTUP')
result = kvxls.readxls2list_findheader(filenamexls, [],
optiondict={
'no_header': True,
'start_row': 2
},
col_aref=list(
records[0].keys()),
debug=False)
self.assertEqual(result[0], records[0])
def test_readxlslist_findheader_p08_xlsx_simple_blankReqCols_col_header_col_aref_missing_cols(
self):
logger.debug('STARTUP')
col_aref = list(records[0].keys())[:-2]
result = kvxls.readxls2list_findheader(filenamexlsx, [],
optiondict={'col_header': True},
col_aref=col_aref,
debug=False)
# manipulate the data to make them match - first copy the data so we don't change the original
temprec = dict(records[0])
temprec['blank001'] = temprec['Type']
del temprec['Type']
temprec['blank002'] = temprec['LastSeen']
del temprec['LastSeen']
self.assertEqual(result[0], temprec)
def test_readxlslist_findheader_p11_xlsx_simple_blankReqCols_aref_result_starting_blank_lines(
self):
logger.debug('STARTUP')
# create a list of values that are used to create the xls - we have 6 blank lines at the top
aref = [[''], [''], [''], [''], ['']]
aref.append(list(records[0].keys()))
for rec in records:
aref.append(list(rec.values()))
kvxls.writelist2xls(filenamexlsx3,
aref,
optiondict={'aref_result': True},
debug=False)
# now read in the file
result = kvxls.readxls2list_findheader(
filenamexlsx3, [], optiondict={'aref_result': True}, debug=False)
self.assertEqual(result[0], [None, None, None, None, None, None, None])
self.assertEqual(result[6], list(records[0].values()))
kvutil.remove_filename(filenamexlsx3, kvutil.functionName())
def loadWineCollection(optiondict, debug=False):
# determine what the winecolletion filename is
if optiondict['wine_file']:
# user specfied specific wine collection file to process
wc_xls_filename = optiondict['wine_file']
else:
# get the largest file - as the wine collection to be loaded
wc_xls_filename = kvutil.filename_maxmin(optiondict['wine_glob'],
reverse=True)
# define the sheets that we are processing
worksheets = []
if optiondict['wine_sheetname']:
worksheets = [optiondict['wine_sheetname']]
elif optiondict['wine_sheets']:
worksheets = optiondict['wine_sheets']
else:
print(
'wine_sheetname and wine_sheets not defined - program termination')
sys.exit(1)
# messaging
if optiondict['verbose']:
print('loadWineCollection:wc_xls_filename:', wc_xls_filename)
print('loadWineCollection:worksheets:', worksheets)
# load up all the records from this xls
winecollection = []
for worksheet in worksheets:
if debug:
print('loadwineCollection:worksheet being loaded:', worksheet)
data = kvxls.readxls2list_findheader(wc_xls_filename, [],
optiondict={
'col_header': True,
'sheetname': worksheet
})
if debug: print('loadwineCollection:records loaded:', len(data))
for line in data:
line['worksheet'] = worksheet
winecollection.extend(data)
# return the data
return winecollection
def load_convert_save_file( xlsfile, req_cols, occupy_filename, fldFirstNight, fldNights, fldType, xlsdateflds, debug=False ):
# logging
logger.info('Read in XLS:%s', xlsfile)
# read in the XLS
xlsaref=kvxls.readxls2list_findheader(xlsfile,req_cols=req_cols,optiondict={'dateflds' : xlsdateflds},debug=False)
#print(xlsaref)
#sys.exit(1)
# capture if the current renter is still there - their start date
current_guest_start = None
# get the current date
now = datetime.datetime.now()
# logging
logger.info('Create occupancy file:%s', occupy_filename)
# create the output file and start the conversion/output process
with open( occupy_filename, 'w' ) as t:
# create the header to the file
t.write('date,occtype\n')
# set the first exit date to something that will NOT match
exitdate = datetime.datetime(2019,1,1)
# run through the records read in
for rec in xlsaref:
# process the record from the file - convert first night into a date variable
# eventdate = datetime.datetime.strptime(rec[fldFirstNight], datefmt)
eventdate = rec[fldFirstNight]
# add to the dictionary the datetime value just calculated
rec['startdate'] = eventdate
# for each night of stay - plus occ_type days to model the day the guest exits
for cnt in range(int(rec[fldNights]) + occtype_conv[rec[fldType]][1]):
# skip the date if this date matches the exit date of the prior
if eventdate == exitdate:
# skip the date if this date matches the exit date of the prior
logger.info('Start date is same as the last guests exit date:skip record creation:%s', exitdate)
else:
# convert the eventdate to a string
eventdate_str = datetime.datetime.strftime(eventdate,datefmt)
# output this value
t.write('%s,%s\n'%(eventdate_str,occtype_conv[rec[fldType]][0]))
# set the exit date to the last date written out
exitdate = eventdate
# capture the date prior to looping
rec['exitdate'] = exitdate
# add one day to this date and loop
eventdate += addoneday
# capture current guest if it exists
if rec['startdate'] < now and rec['exitdate']> now:
current_guest_start = rec['startdate']
# return the BP file with modifications
return xlsaref, current_guest_start
