def add_sierra_ids_to_orders(source_fh, system_id):
mlogger.debug(f'Linking IDs initiated system_id-{system_id}.')
sids = get_sierra_ids(source_fh, system_id)
try:
unique_carts = set()
with session_scope() as session:
for sid in sids:
wlo, oid, bid = sid
ord_rec = retrieve_record(session, Order, wlo=wlo)
if ord_rec:
# record affected cart_id
unique_carts.add(ord_rec.cart_id)
# update ord rec
update_record(session,
Order,
ord_rec.did,
oid=oid,
bid=bid)
mlogger.debug(f'Record updated: order_id={ord_rec.did}, '
f'wlo={wlo}, oid={oid}, bid={bid}')
session.flush()
# check which carts are linked
determine_carts_linked(session, unique_carts)
mlogger.debug('Linking completed.')
except Exception as exc:
_, _, exc_traceback = sys.exc_info()
tb = format_traceback(exc, exc_traceback)
mlogger.error('Unhandled error on linking IDs.' f'Traceback: {tb}')
raise BabelError(exc)
def assign_blanketPO_to_cart(cart_id):
try:
with session_scope() as session:
cart_rec = retrieve_record(session, Cart, did=cart_id)
if cart_rec.blanketPO is None:
res = retrieve_unique_vendor_codes_from_cart(
session, cart_id, cart_rec.system_id)
vendor_codes = [code[0] for code in res]
blanketPO = create_blanketPO(vendor_codes)
unique = True
n = 0
while unique:
try:
update_record(session,
Cart,
cart_id,
blanketPO=blanketPO)
session.flush()
unique = False
except IntegrityError:
session.rollback()
n += 1
blanketPO = create_blanketPO(vendor_codes, n)
except Exception as exc:
_, _, exc_traceback = sys.exc_info()
tb = format_traceback(exc, exc_traceback)
mlogger.error('Unhandled error on assigning blanketPo to cart.'
f'Traceback: {tb}')
raise BabelError(exc)
def get_cart_data_for_order_sheet(cart_id):
try:
data_set = []
with session_scope() as session:
cart_rec = retrieve_record(session, Cart, did=cart_id)
order_recs = retrieve_records(session, Order, cart_id=cart_id)
for rec in order_recs:
data = []
data.append(rec.resource.other_no)
data.append(rec.resource.isbn)
data.append(rec.resource.title)
data.append(rec.resource.author)
total_cost = 0
total_qty = 0
for loc in rec.locations:
total_cost += loc.qty * rec.resource.price_disc
total_qty += loc.qty
data.append(f'{rec.resource.price_disc:.2f}')
data.append(total_qty)
data.append(total_cost)
data.append(rec.oid)
data.append(cart_rec.blanketPO)
data_set.append(data)
session.expunge_all()
return data_set
except Exception as exc:
_, _, exc_traceback = sys.exc_info()
tb = format_traceback(exc, exc_traceback)
mlogger.error('Unhandled error cart data retrieval.'
f'Traceback: {tb}')
raise BabelError(exc)
def update_resource(resource_id, **kwargs):
try:
with session_scope() as session:
update_record(session, Resource, resource_id, **kwargs)
except Exception as exc:
_, _, exc_traceback = sys.exc_info()
tb = format_traceback(exc, exc_traceback)
mlogger.error('Unhandled error on updating resource.'
f'Traceback: {tb}')
raise BabelError(exc)
def apply_fund_to_cart(system_id, cart_id, fund_codes):
try:
with session_scope() as session:
cart_rec = retrieve_record(session, Cart, did=cart_id)
ord_recs = retrieve_records(session, Order, cart_id=cart_id)
for code in fund_codes:
fund_rec = retrieve_record(session,
Fund,
code=code,
system_id=system_id)
fund_audn, fund_mat, fund_branch, fund_lib = valid_fund_ids(
fund_rec)
for orec in ord_recs:
audn_match = False
mat_match = False
library_match = False
if orec.audn_id in fund_audn:
audn_match = True
if orec.matType_id in fund_mat:
mat_match = True
if cart_rec.library_id in fund_lib:
library_match = True
for oloc in orec.locations:
if oloc.branch_id in fund_branch:
mlogger.debug('OrdRec-Fund branch {} match'.format(
oloc.branch_id))
if audn_match and library_match and mat_match:
# update
mlogger.debug(
'Complete match. Updating OrderLocation.')
update_record(session,
OrderLocation,
oloc.did,
fund_id=fund_rec.did)
else:
mlogger.debug(
'Incomplete match: lib={}, audn={}, '
'mat={}.'.format(library_match, audn_match,
mat_match))
except Exception as exc:
_, _, exc_traceback = sys.exc_info()
tb = format_traceback(exc, exc_traceback)
mlogger.error('Unhandled error on applying funds to cart.'
f'Traceback: {tb}')
raise BabelError(exc)
def add_resource(cart_id, **kwargs):
try:
with session_scope() as session:
orec = insert(session, Order, cart_id=cart_id)
kwargs['order_id'] = orec.did
insert(session, Resource, **kwargs)
except Exception as exc:
_, _, exc_traceback = sys.exc_info()
tb = format_traceback(exc, exc_traceback)
mlogger.error('Unhandled error on updating resource.'
f'Traceback: {tb}')
raise BabelError(exc)
def delete_data_by_did(model, did):
try:
with session_scope() as session:
delete_record(session, model, did=did)
mlogger.debug('Deleted {} record did={}'.format(model.__name__, did))
except Exception as exc:
_, _, exc_traceback = sys.exc_info()
tb = format_traceback(exc, exc_traceback)
mlogger.error('Unhandled error cart deletion.' f'Traceback: {tb}')
raise BabelError(exc)
def get_ids_for_order_boxes_values(values_dict):
try:
kwargs = {}
with session_scope() as session:
if values_dict['langCbx'].get() not in ('', 'keep current'):
rec = retrieve_record(session,
Lang,
name=values_dict['langCbx'].get())
kwargs['lang_id'] = rec.did
if values_dict['vendorCbx'].get() not in ('', 'keep current'):
rec = retrieve_record(session,
Vendor,
name=values_dict['vendorCbx'].get())
kwargs['vendor_id'] = rec.did
if values_dict['mattypeCbx'].get() not in ('', 'keep current'):
rec = retrieve_record(session,
MatType,
name=values_dict['mattypeCbx'].get())
kwargs['matType_id'] = rec.did
if values_dict['audnCbx'].get() not in ('', 'keep current'):
rec = retrieve_record(session,
Audn,
name=values_dict['audnCbx'].get())
kwargs['audn_id'] = rec.did
if 'poEnt' in values_dict:
if values_dict['poEnt'].get().strip() != '':
kwargs['poPerLine'] = values_dict['poEnt'].get().strip()
if 'noteEnt' in values_dict:
if values_dict['noteEnt'].get().strip() != '':
kwargs['note'] = values_dict['noteEnt'].get().strip()
if 'commentEnt' in values_dict:
if 'commentEnt' in values_dict:
if values_dict['commentEnt'].get().strip() != '':
kwargs['comment'] = values_dict['commentEnt'].get(
).strip()
return kwargs
except Exception as exc:
_, _, exc_traceback = sys.exc_info()
tb = format_traceback(exc, exc_traceback)
mlogger.error('Unhandled error on getting ids in cart.'
f'Traceback: {tb}')
raise BabelError(exc)
def copy_distribution_data(distr_record, user_id):
try:
with session_scope() as session:
# create new name
# check if used and adjust
exists = True
n = 0
while exists:
new_name = f'{distr_record.name}-copy({n})'
rec = retrieve_record(
session, DistSet,
name=new_name,
user_id=user_id,
system_id=distr_record.system_id)
if not rec:
exists = False
n += 1
# prep copy of distrgrids & gridlocations
grids = []
for grid in distr_record.distgrids:
locs = []
for loc in grid.gridlocations:
locs.append(
GridLocation(
branch_id=loc.branch_id,
shelfcode_id=loc.shelfcode_id,
qty=loc.qty))
grids.append(
DistGrid(
name=grid.name,
gridlocations=locs))
rec = insert(
session,
DistSet,
name=new_name,
system_id=distr_record.system_id,
user_id=user_id,
distgrids=grids)
mlogger.debug(f'Added new record: {rec}')
except Exception as exc:
_, _, exc_traceback = sys.exc_info()
tb = format_traceback(exc, exc_traceback)
mlogger.error(
'Unhandled error in copy distribution.'
f'Traceback: {tb}')
raise BabelError(exc)
def delete_data(record):
try:
# check if can be deteled without consequences first
with session_scope() as session:
model = type(record)
delete_record(session, model, did=record.did)
mlogger.debug('Deleted {} record did={}'.format(
model.__name__, record.did))
except Exception as exc:
_, _, exc_traceback = sys.exc_info()
tb = format_traceback(exc, exc_traceback)
mlogger.error('Unhandled error cart deletion.' f'Traceback: {tb}')
raise BabelError(exc)
def update_fund(**kwargs):
"""
Adds a new fund with all related data
args:
system_id: int, datastore did of NYPL or BPL
fund_code: str, Sierra fund code
describ: str, fund brief description
libraries: list, 'branches', 'research' (NYPL only)
audns: list, list of 'a', 'j', 'y' values applicable for the fund
matTypes: list, material types applicable for the fund
"""
try:
for k, v in kwargs.items():
if not v:
if k == 'libraries':
kwargs[k] = []
elif k == 'audns':
kwargs[k] = []
elif k == 'branches':
kwargs[k] = []
elif k == 'matTypes':
kwargs[k] = []
else:
kwargs[k] = None
mlogger.info('Existing fund new parameters: {}'.format(kwargs))
with session_scope() as session:
branches, libraries, audns, matTypes = create_fund_joiner_objs(
session, **kwargs)
update_record(session,
Fund,
kwargs['did'],
code=kwargs['code'],
describ=kwargs['describ'],
audns=audns,
branches=branches,
matTypes=matTypes,
libraries=libraries)
fund_rec = retrieve_record(session, Fund, did=kwargs['did'])
mlogger.info(f'Updated record {fund_rec}')
except Exception as exc:
_, _, exc_traceback = sys.exc_info()
tb = format_traceback(exc, exc_traceback)
mlogger.error('Unhandled error in Fund update.' f'Traceback: {tb}')
raise BabelError(exc)
def save_displayed_order_data(tracker_values):
try:
with session_scope() as session:
for v in tracker_values:
order = v['order']
locs = v['grid']['locs']
okwargs = {}
locations = []
for l in locs:
mlogger.debug('Saving orderLoc data: order_id:{}, '
'loc_id:{}, frm_id:{}'.format(
order['order_id'], l['loc_id'],
l['unitFrm'].winfo_id()))
lkwargs = {}
if l['loc_id'] is not None:
lkwargs['did'] = l['loc_id']
if l['branchCbx'].get() != '':
rec_id = get_branch_rec_id(session,
l['branchCbx'].get())
lkwargs['branch_id'] = rec_id
if l['shelfCbx'].get() != '':
rec_id = get_shelf_rec_id(session, l['shelfCbx'].get())
lkwargs['shelfcode_id'] = rec_id
if l['qtyEnt'].get() != '':
lkwargs['qty'] = int(l['qtyEnt'].get())
if l['fundCbx'].get() != '':
rec_id = get_fund_rec_id(session, l['fundCbx'].get())
lkwargs['fund_id'] = rec_id
# validate here
if lkwargs:
locations.append(OrderLocation(**lkwargs))
mlogger.debug(
'Saving orderLoc data, params: {}'.format(lkwargs))
okwargs = get_ids_for_order_boxes_values(order)
okwargs['locations'] = locations
mlogger.debug('Saving order data (id:{}), params: {}'.format(
order['order_id'], okwargs))
update_record(session, Order, order['order_id'], **okwargs)
except Exception as exc:
_, _, exc_traceback = sys.exc_info()
tb = format_traceback(exc, exc_traceback)
mlogger.error('Unhandled error on saving cart data.'
f'Traceback: {tb}')
raise BabelError(exc)
def create_resource_reader(template_record, sheet_fh):
record = template_record
# convert any empty strings back to None
state = inspect(record)
for attr in state.attrs:
if attr.loaded_value == '':
setattr(record, attr.key, None)
mlogger.debug('Applying following sheet template: {}'.format(record))
try:
reader = ResourceDataReader(
sheet_fh,
header_row=record.header_row,
title_col=record.title_col,
add_title_col=record.add_title_col,
author_col=record.author_col,
series_col=record.series_col,
publisher_col=record.publisher_col,
pub_date_col=record.pub_date_col,
pub_place_col=record.pub_place_col,
summary_col=record.summary_col,
isbn_col=record.isbn_col,
upc_col=record.upc_col,
other_no_col=record.other_no_col,
price_list_col=record.price_list_col,
price_disc_col=record.price_disc_col,
desc_url_col=record.desc_url_col,
comment_col=record.comment_col,
misc_col=record.misc_col)
return reader
except AttributeError as e:
mlogger.error(
f'AttributeError on ResourceDataReader: {e}')
raise BabelError(e)
except Exception as exc:
_, _, exc_traceback = sys.exc_info()
tb = format_traceback(exc, exc_traceback)
mlogger.error(
f'Unhandled error on ResourceDataReader. Traceback: {tb}')
raise BabelError(exc)
def copy_grid_data(grid_record):
mlogger.debug(
f'Copying grid record did={grid_record.did}, name={grid_record.name}')
try:
with session_scope() as session:
# create new name
# check if used and adjust
exists = True
n = 0
while exists:
new_name = f'{grid_record.name}-copy({n})'
rec = retrieve_record(
session, DistGrid,
distset_id=grid_record.distset_id,
name=new_name)
if not rec:
exists = False
n += 1
# compile location data
locs = []
for loc in grid_record.gridlocations:
locs.append(
GridLocation(
branch_id=loc.branch_id,
shelfcode_id=loc.shelfcode_id,
qty=loc.qty))
# # insert to datastore
rec = insert(
session, DistGrid,
distset_id=grid_record.distset_id,
name=new_name,
gridlocations=locs)
mlogger.debug(
f'Added new record {rec}')
except Exception as exc:
_, _, exc_traceback = sys.exc_info()
tb = format_traceback(exc, exc_traceback)
mlogger.error(
'Unhandled error in copy grid.'
f'Traceback: {tb}')
raise BabelError(exc)
def get_carts_data(system_id, user='******', status=''):
data = []
try:
with session_scope() as session:
recs = get_cart_data_view_records(session, system_id, user, status)
for r in recs:
data.append([
r.cart_id, r.cart_name, f'{r.cart_date:%y-%m-%d %H:%M}',
r.cart_status, r.cart_owner, r.linked
])
return data
except Exception as exc:
_, _, exc_traceback = sys.exc_info()
tb = format_traceback(exc, exc_traceback)
mlogger.error('Unhandled error on cart data retrieval.'
f'Traceback: {tb}')
raise BabelError(exc)
def launch_update(self):
user_data = shelve.open(USER_DATA)
if 'update_dir' in user_data:
update_dir = user_data['update_dir']
else:
update_dir = None
user_data.close()
if update_dir:
try:
args = '{} "{}" "{}"'.format('updater.exe', update_dir,
APP_DIR)
# subprocess.run(args, creationflags=subprocess.CREATE_NO_WINDOW)
subprocess.run(args)
except Exception as exc:
_, _, exc_traceback = sys.exc_info()
tb = format_traceback(exc, exc_traceback)
mlogger.error('Unhandled error on Babel update.'
f'Traceback: {tb}')
def assign_wlo_to_cart(cart_id):
try:
with session_scope() as session:
# determne how many wlo are needed and reserve them
last_wlo_rec = retrieve_last_record(session, Wlos)
order_count = count_records(session, Order, cart_id=cart_id)
wlo_numbers = wlo_pool(last_wlo_rec.did, order_count)
orders = retrieve_records(session, Order, cart_id=cart_id)
for o in orders:
if o.wlo is None:
wlo = wlo_numbers.__next__()
update_record(session, Order, o.did, wlo=wlo)
insert(session, Wlos, did=wlo)
except Exception as exc:
_, _, exc_traceback = sys.exc_info()
tb = format_traceback(exc, exc_traceback)
mlogger.error('Unhandled error on assigning wlo to cart.'
f'Traceback: {tb}')
raise BabelError(exc)
def apply_globals_to_cart(cart_id, widgets):
try:
with session_scope() as session:
# order data
okwargs = get_ids_for_order_boxes_values(widgets)
# locations
dist_id, grid_name = widgets['globgrid']
grid_rec = retrieve_record(session,
DistGrid,
distset_id=dist_id,
name=grid_name)
mlogger.debug(f'Applying globally grid {grid_rec}')
# resource data
rkwargs = {}
discount = None
if 'discEnt' in widgets:
if widgets['discEnt'].get() != '':
discount = Decimal(widgets['discEnt'].get())
if 'priceEnt' in widgets:
if widgets['priceEnt'].get() != '':
list_price = Decimal(widgets['priceEnt'].get())
rkwargs['price_list'] = list_price
if discount:
rkwargs['price_disc'] = list_price - (
(list_price * discount) / Decimal(100))
else:
rkwargs['price_disc'] = list_price
mlogger.debug('Global update to prices: {}, discount: {}'.format(
rkwargs, discount))
ord_recs = retrieve_records(session, Order, cart_id=cart_id)
for rec in ord_recs:
if grid_rec:
olocs = []
for l in grid_rec.gridlocations:
olocs.append(
OrderLocation(order_id=rec.did,
branch_id=l.branch_id,
shelfcode_id=l.shelfcode_id,
qty=l.qty))
okwargs['locations'] = olocs
update_record(session, Order, rec.did, **okwargs)
if rkwargs:
update_record(session, Resource, rec.resource.did,
**rkwargs)
session.flush()
if discount is not None:
rkwargs['price_disc'] = rec.resource.price_list - (
(rec.resource.price_list * discount) / Decimal(100))
update_record(session, Resource, rec.resource.did,
**rkwargs)
rkwargs = {}
except Exception as exc:
_, _, exc_traceback = sys.exc_info()
tb = format_traceback(exc, exc_traceback)
mlogger.error('Unhandled error on applying globals to cart.'
f'Traceback: {tb}')
raise BabelError(exc)
def insert_fund(**kwargs):
"""
Adds a new fund with all related data
args:
system_id: int, datastore did of NYPL or BPL
fund_code: str, Sierra fund code
describ: str, fund brief description
libraries: list, 'branches', 'research' (NYPL only)
audns: list, list of 'a', 'j', 'y' values applicable for the fund
matTypes: list, material types applicable for the fund
"""
try:
for k, v in kwargs.items():
if not v:
if k == 'libraries':
kwargs[k] = ['']
elif k == 'audns':
kwargs[k] = []
elif k == 'branches':
kwargs[k] = []
elif k == 'matTypes':
kwargs[k] = []
else:
kwargs[k] = None
mlogger.info('New fund record parameters: {}'.format(kwargs))
with session_scope() as session:
# check if exists first
rec = retrieve_record(session,
Fund,
code=kwargs['code'],
system_id=kwargs['system_id'])
if rec:
msg = 'Fund record with code: {} already exists.'.format(
kwargs['code'])
mlogger.error(msg)
raise BabelError('Database Error', msg)
else:
branches, libraries, audns, matTypes = create_fund_joiner_objs(
session, **kwargs)
fund_rec = insert(session,
Fund,
code=kwargs['code'],
describ=kwargs['describ'],
system_id=kwargs['system_id'],
audns=audns,
branches=branches,
matTypes=matTypes,
libraries=libraries)
mlogger.info(f'Added new record {fund_rec}')
session.expunge_all()
return fund_rec
except Exception as exc:
_, _, exc_traceback = sys.exc_info()
tb = format_traceback(exc, exc_traceback)
mlogger.error('Unhandled error in add Fund.' f'Traceback: {tb}')
raise BabelError(exc)
def create_cart_copy(cart_id, system, user, profile_idx, cart_name, status):
"""
Creates a copy of a cart
args:
cart_id: int, datastore cart did
system: str, NYPL or BPL
user: str, profile/user name
profile_idx: dict, dictionary of user_id (key) and names
cart_name: str, new cart name
status: tkinter StringVar
"""
valid = True
if not cart_id:
valid = False
status.set('Invalid cart id')
elif not system:
valid = False
status.set('Failed. Missing system parameter.')
elif not user:
valid = False
status.set('Failed. Missing profile prameter.')
elif not cart_name:
valid = False
status.set('Failed. Missing new cart name.')
try:
with session_scope() as session:
if cart_id and system and user and cart_name:
# verify name/user not used:
if system == 'BPL':
system_id = 1
elif system == 'NYPL':
system_id = 2
rec = retrieve_record(session,
Cart,
system_id=system_id,
user_id=get_id_from_index(
user, profile_idx),
name=cart_name)
if rec:
valid = False
status.set('Failed. A cart with the same name'
'already exists.\nPlease change the name.')
if valid:
# create copy of the original cart
old_orders = retrieve_records(session, Order, cart_id=cart_id)
new_orders = []
for order in old_orders:
resource = Resource(title=order.resource.title,
add_title=order.resource.add_title,
author=order.resource.author,
series=order.resource.series,
publisher=order.resource.publisher,
pub_place=order.resource.pub_place,
summary=order.resource.summary,
isbn=order.resource.isbn,
upc=order.resource.upc,
other_no=order.resource.other_no,
price_list=order.resource.price_list,
price_disc=order.resource.price_disc,
desc_url=order.resource.desc_url,
misc=order.resource.misc)
new_orders.append(
Order(lang_id=order.lang_id,
audn_id=order.audn_id,
vendor_id=order.vendor_id,
matType_id=order.matType_id,
poPerLine=order.poPerLine,
note=order.note,
comment=order.comment,
resource=resource))
insert(session,
Cart,
name=cart_name,
user_id=get_id_from_index(user, profile_idx),
system_id=system_id,
orders=new_orders)
status.set('Cart copied successfully.')
except Exception as exc:
_, _, exc_traceback = sys.exc_info()
tb = format_traceback(exc, exc_traceback)
mlogger.error('Unhandled error on cart copy.' f'Traceback: {tb}')
raise BabelError(exc)
def save2spreadsheet(fh, saving_status, system, data):
try:
if os.path.isfile(fh):
os.remove(fh)
red_font = Font(color='CC0000')
order_wb = Workbook()
order_ws = order_wb.active
address_line1 = f'BookOps {system}'
address_line2 = '31-11 Thomson Avenue'
address_line3 = 'Long Island City, NY 11101'
# set columns width
order_ws.column_dimensions['A'].width = 4
order_ws.column_dimensions['C'].width = 16
order_ws.column_dimensions['D'].width = 25
order_ws.column_dimensions['E'].width = 20
order_ws.column_dimensions['F'].width = 9
order_ws.column_dimensions['G'].width = 8
order_ws.column_dimensions['H'].width = 10
order_ws.column_dimensions['I'].width = 12
order_ws.column_dimensions['J'].width = 18
order_ws.cell(row=2, column=1).value = address_line1
order_ws.cell(row=2, column=1).font = FONT_BOLD
order_ws.cell(row=3, column=1).value = address_line2
order_ws.cell(row=3, column=1).font = FONT_BOLD
order_ws.cell(row=4, column=1).value = address_line3
order_ws.cell(row=4, column=1).font = FONT_BOLD
# headers
order_ws.cell(row=6, column=1).value = '#'
order_ws.cell(row=6, column=1).fill = FILL_GRAY
order_ws.cell(row=6, column=2).value = 'SKU'
order_ws.cell(row=6, column=2).fill = FILL_GRAY
order_ws.cell(row=6, column=3).value = 'ISBN'
order_ws.cell(row=6, column=3).fill = FILL_GRAY
order_ws.cell(row=6, column=4).value = 'Title'
order_ws.cell(row=6, column=4).fill = FILL_GRAY
order_ws.cell(row=6, column=5).value = 'Author'
order_ws.cell(row=6, column=5).fill = FILL_GRAY
order_ws.cell(row=6, column=6).value = 'Unit Price'
order_ws.cell(row=6, column=6).fill = FILL_GRAY
order_ws.cell(row=6, column=7).value = 'Copies'
order_ws.cell(row=6, column=7).fill = FILL_GRAY
order_ws.cell(row=6, column=8).value = 'Total Price'
order_ws.cell(row=6, column=8).fill = FILL_GRAY
order_ws.cell(row=6, column=9).value = 'o Number'
order_ws.cell(row=6, column=9).fill = FILL_GRAY
order_ws.cell(row=6, column=10).value = 'blanket PO'
order_ws.cell(row=6, column=10).fill = FILL_GRAY
r = 1
for title in data:
row = []
row.append(r)
row.extend(title)
order_ws.append(row)
order_ws.cell(row=6 + r, column=9).font = red_font
order_ws.cell(row=6 + r, column=10).font = red_font
r += 1
last_r = 6 + r
order_ws.cell(row=last_r + 2, column=3).value = 'total copies='
order_ws.cell(row=last_r + 2, column=3).fill = FILL_GRAY
order_ws.cell(row=last_r + 2, column=4).value = \
'=SUM(G7:G' + str(last_r) + ')'
order_ws.cell(row=last_r + 2, column=4).fill = FILL_GRAY
order_ws.cell(row=last_r + 3, column=3).value = 'total cost='
order_ws.cell(row=last_r + 3, column=3).fill = FILL_GRAY
order_ws.cell(row=last_r + 3, column=4).value = \
'=SUM(H7:H' + str(last_r) + ')'
order_ws.cell(row=last_r + 3, column=4).fill = FILL_GRAY
order_wb.save(filename=fh)
saving_status.set('Data saved to spreadsheet successfuly.')
except WindowsError as e:
raise BabelError(e)
except Exception as exc:
_, _, exc_traceback = sys.exc_info()
tb = format_traceback(exc, exc_traceback)
mlogger.error(
'Unhandled error on saving to sheet.'
f'Traceback: {tb}')
raise BabelError(exc)
def create_cart(
cart_name, system_id, profile_id,
resource_data, progbar):
try:
with session_scope() as session:
# create Cart record
name_exists = True
n = 0
while name_exists and n < 10:
name_exists = retrieve_record(
session, Cart, name=cart_name)
if name_exists:
n += 1
if '(' in cart_name:
end = cart_name.index('(')
cart_name = cart_name[:end]
cart_name = f'{cart_name}({n})'
if system_id == 1:
library_id = 1
else:
library_id = None
cart_rec = insert(
session, Cart,
name=cart_name,
created=datetime.now(),
updated=datetime.now(),
library_id=library_id,
system_id=system_id,
user_id=profile_id)
progbar['value'] += 1
progbar.update()
# create Resource records
for d in resource_data:
ord_rec = insert(
session, Order,
cart_id=cart_rec.did,
comment=d.comment)
insert(
session, Resource,
order_id=ord_rec.did,
title=d.title,
add_title=d.add_title,
author=d.author,
series=d.series,
publisher=d.publisher,
pub_date=d.pub_date,
pub_place=d.pub_place,
summary=d.summary,
isbn=d.isbn,
upc=d.upc,
other_no=d.other_no,
price_list=d.price_list,
price_disc=d.price_disc,
desc_url=d.desc_url,
misc=d.misc)
progbar['value'] += 1
progbar.update()
session.flush()
created_cart_id = cart_rec.did
return created_cart_id
except Exception as exc:
_, _, exc_traceback = sys.exc_info()
tb = format_traceback(exc, exc_traceback)
mlogger.error(
f'Unhandled on sheet ingest. Traceback: {tb}')
raise BabelError(exc)
def export_orders_to_marc_file(fh, saving_status, cart_rec, progbar):
# this has to be rewritten to make it more transparent
# and easier to maintain
try:
progbar['value'] = 0
# overwrite existing files
if os.path.isfile(fh):
try:
os.remove(fh)
except WindowsError as e:
raise BabelError(f'File in use. Error: {e}')
with session_scope() as session:
rec_count = count_records(session, Order, cart_id=cart_rec.did)
progbar['maximum'] = rec_count
selector = retrieve_record(session, User, did=cart_rec.user_id)
blanketPO = cart_rec.blanketPO
# determine some global values
if cart_rec.system_id == 1:
oclc_code = 'BKL'
selector_code = selector.bpl_code
elif cart_rec.system_id == 2:
oclc_code = 'NYP'
selector_code = selector.nyp_code
lib_rec = retrieve_record(session,
Library,
did=cart_rec.library_id)
library_code = lib_rec.code
ord_recs = retrieve_records(session, Order, cart_id=cart_rec.did)
for order in ord_recs:
mat_rec = retrieve_record(session,
MatType,
did=order.matType_id)
ven_rec = retrieve_record(session, Vendor, did=order.vendor_id)
if cart_rec.system_id == 1:
order.mat_bib = mat_rec.bpl_bib_code
order.mat_ord = mat_rec.bpl_ord_code
order.vendor = ven_rec.bpl_code
elif cart_rec.system_id == 2:
order.mat_bib = mat_rec.nyp_bib_code
order.mat_ord = mat_rec.nyp_ord_code
order.vendor = ven_rec.nyp_code
# retrieve joined values
rec = retrieve_record(session, Audn, did=order.audn_id)
order.audn = rec.code
rec = retrieve_record(session, Lang, did=order.lang_id)
order.lang = rec.code
copies = 0
locs = []
funds = []
for loc in order.locations:
rec = retrieve_record(session, Branch, did=loc.branch_id)
branch = rec.code
try:
rec = retrieve_record(session,
ShelfCode,
did=loc.shelfcode_id)
shelfcode = rec.code
shelf_with_audn = rec.includes_audn
except AttributeError:
shelfcode = ''
shelf_with_audn = False
try:
rec = retrieve_record(session, Fund, did=loc.fund_id)
fund = rec.code
except AttributeError:
fund = ''
copies += loc.qty
if shelf_with_audn:
loc_str = f'{branch}{order.audn}{shelfcode}/{loc.qty}'
else:
if shelfcode is None:
loc_str = f'{branch}/{loc.qty}'
else:
loc_str = f'{branch}{shelfcode}/{loc.qty}'
locs.append(loc_str)
fund_str = f'{fund}/{loc.qty}'
funds.append(fund_str)
order.copies = str(copies)
order.locs = ','.join(locs)
order.funds = ','.join(funds)
order.order_date = datetime.strftime(date.today(), '%m-%d-%Y')
make_bib(fh, oclc_code, library_code, blanketPO, selector_code,
order)
progbar['value'] += 1
progbar.update()
saving_status.set('Data saved to MARC file successfully.')
except Exception as exc:
_, _, exc_traceback = sys.exc_info()
tb = format_traceback(exc, exc_traceback)
mlogger.error('Unhandled error on saving to MARC.' f'Traceback: {tb}')
raise BabelError(exc)
def get_data_by_identifier(keyword, keyword_type):
if keyword_type == 'bib #':
param = Order.bid
elif keyword_type == 'order #':
param = Order.oid
elif keyword_type == 'wlo #':
param = Order.wlo
elif keyword_type == 'ISBN':
param = Resource.isbn
elif keyword_type == 'UPC':
param = Resource.upc
elif keyword_type == 'other #':
param = Resource.other_no
elif keyword_type == 'blanketPO':
param = Cart.blanketPO
else:
raise AttributeError('Invalid keyword_type passed')
mlogger.debug(f'Basic search params: {keyword}, type {param}')
try:
with session_scope() as session:
recs = (session.query(Cart, Order, Resource).join(
Order, Cart.did == Order.cart_id).join(
Resource, Order.did == Resource.order_id).filter(
param == keyword).all())
results = []
for cart_rec, ord_rec, res_rec in recs:
# cart
cart = cart_rec.name
owner = get_owner(session, cart_rec.user_id)
system = get_system_name(session, cart_rec.system_id)
library = get_library_name(session, cart_rec.library_id)
status = get_status_name(session, cart_rec.status_id)
created = cart_rec.created
blanketPO = cart_rec.blanketPO
# order
oid = ord_rec.oid
bid = ord_rec.bid
wlo = ord_rec.wlo
po = ord_rec.poPerLine
lang = get_lang_name(session, ord_rec.lang_id)
audn_name, audn_code = get_audn_name_and_code(
session, ord_rec.audn_id)
vendor = get_vendor_code(session, ord_rec.vendor_id, system)
mattype = get_mattype_name(session, ord_rec.matType_id)
locs = []
for loc in ord_rec.locations:
branch = get_branch_code(session, loc.branch_id)
shelfcode = get_shelfcode(session, loc.shelfcode_id,
audn_code)
qty = loc.qty
fund = get_fund_code(session, loc.fund_id)
locs.append(f'{branch}{shelfcode}({qty})/{fund}')
# resouce
title = res_rec.title
author = res_rec.author
isbn = res_rec.isbn
upc = res_rec.upc
other_no = res_rec.other_no
unit = dict(title=title,
author=author,
isbn=isbn,
upc=upc,
other_no=other_no,
bid=bid,
oid=oid,
wlo=wlo,
owner=owner,
cart=cart,
created=created,
system=system,
library=library,
status=status,
vendor=vendor,
lang=lang,
audn_name=audn_name,
mattype=mattype,
po=po,
locs=', '.join(locs))
results.append(unit)
session.expunge_all()
return results
except Exception as exc:
_, _, exc_traceback = sys.exc_info()
tb = format_traceback(exc, exc_traceback)
mlogger.error('Unhandled error during Basic search.'
f'Traceback: {tb}')
raise BabelError('Unable to retrieve records.')
