def edi_tweak(edi_process, output_filename, pad_arec, arec_padding,
append_arec, append_arec_text, force_txt_file_ext, calc_upc,
invoice_date_offset):
work_file = open(edi_process) # open input file
work_file_lined = [n for n in work_file.readlines()] # make list of lines
if force_txt_file_ext == "True":
output_filename = output_filename + ".txt"
f = open(output_filename, 'wb') # open work file, overwriting old file
for line_num, line in enumerate(
work_file_lined): # iterate over work file contents
input_edi_dict = line_from_mtc_edi_to_dict.capture_records(line)
writeable_line = line
if writeable_line.startswith("A"):
if invoice_date_offset != 0:
invoice_date_string = line_from_mtc_edi_to_dict.capture_records(
writeable_line)['invoice_date']
if not invoice_date_string == '000000':
invoice_date = datetime.strptime(invoice_date_string,
'%m%d%y')
offset_invoice_date = invoice_date + timedelta(
days=invoice_date_offset)
writeable_line = writeable_line[0:17] + datetime.strftime(
offset_invoice_date, '%m%d%y') + writeable_line[23:]
if pad_arec == "True":
writeable_line = writeable_line[0:1] + arec_padding[
0:6] + writeable_line[7:] # write "A" line
if append_arec == "True":
writeable_line = writeable_line.rstrip(
) + append_arec_text + '\n'
if writeable_line.startswith("B"):
if calc_upc == "True":
blank_upc = False
upc_string = ""
try:
_ = int(input_edi_dict['upc_number'].rstrip())
except ValueError:
blank_upc = True
if blank_upc is False:
proposed_upc = input_edi_dict['upc_number'].strip()
if len(str(proposed_upc)) == 11:
upc_string = str(proposed_upc) + str(
upc_e_to_upc_a.calc_check_digit(proposed_upc))
else:
if len(str(proposed_upc)) == 8:
upc_string = str(
upc_e_to_upc_a.convert_UPCE_to_UPCA(
proposed_upc))
else:
upc_string = " "
writeable_line = writeable_line[
0:1] + upc_string + writeable_line[12:]
f.write(writeable_line.replace('\n', "\r\n").encode())
else:
f.write(chr(26).encode())
f.close() # close output file
return output_filename
def edi_convert(edi_process, output_filename, arec_padding, append_arec,
append_arec_text, force_txt_file_ext, invoice_date_offset):
work_file = open(edi_process) # open input file
work_file_lined = [n for n in work_file.readlines()] # make list of lines
if force_txt_file_ext == "True":
output_filename = output_filename + ".txt"
f = open(output_filename, 'wb') # open work file, overwriting old file
for line_num, line in enumerate(
work_file_lined): # iterate over work file contents
input_edi_dict = line_from_mtc_edi_to_dict.capture_records(line)
writeable_line = line.strip("\r\n")
line_builder_list = []
if writeable_line.startswith("A"):
line_builder_list.append(input_edi_dict['record_type'])
line_builder_list.append(arec_padding.ljust(6))
line_builder_list.append(input_edi_dict['invoice_number'][-7:])
line_builder_list.append(' ')
write_invoice_date = line_from_mtc_edi_to_dict.capture_records(
writeable_line)['invoice_date']
if invoice_date_offset != 0:
invoice_date_string = line_from_mtc_edi_to_dict.capture_records(
writeable_line)['invoice_date']
if not invoice_date_string == '000000':
invoice_date = datetime.strptime(invoice_date_string,
'%m%d%y')
offset_invoice_date = invoice_date + timedelta(
days=invoice_date_offset)
write_invoice_date = datetime.strftime(
offset_invoice_date, '%m%d%y')
line_builder_list.append(write_invoice_date)
line_builder_list.append(input_edi_dict['invoice_total'])
if append_arec == "True":
line_builder_list.append(append_arec_text)
if writeable_line.startswith("B"):
line_builder_list.append(input_edi_dict['record_type'])
line_builder_list.append(input_edi_dict['upc_number'].ljust(14))
line_builder_list.append(input_edi_dict['description'][:25])
line_builder_list.append(input_edi_dict['vendor_item'])
line_builder_list.append(input_edi_dict['unit_cost'])
line_builder_list.append(' ')
line_builder_list.append(input_edi_dict['unit_multiplier'])
line_builder_list.append(input_edi_dict['qty_of_units'])
line_builder_list.append(input_edi_dict['suggested_retail_price'])
line_builder_list.append('001')
line_builder_list.append(' ')
if writeable_line.startswith("C"):
line_builder_list.append(input_edi_dict['record_type'])
line_builder_list.append(input_edi_dict['description'].ljust(25))
line_builder_list.append(' ')
line_builder_list.append(input_edi_dict['amount'])
writeable_line = "".join(line_builder_list)
f.write((writeable_line + '\r\n').encode())
f.close() # close output file
return output_filename
def _insert_description_and_number(issue_line):
line_dict = line_from_mtc_edi_to_dict.capture_records(issue_line)
in_memory_log.write("Item description: " + line_dict['description'] + "\r\n")
in_memory_log.write("Item number: " + line_dict['vendor_item'] + "\r\n\r\n")
def _insert_description_and_number(issue_line):
line_dict = line_from_mtc_edi_to_dict.capture_records(issue_line)
in_memory_log.write("Item description: " + line_dict['description'] + "\r\n")
in_memory_log.write("Item number: " + line_dict['vendor_item'] + "\r\n\r\n")
def edi_convert(edi_process, output_filename, calc_upc, inc_arec, inc_crec,
inc_headers, filter_ampersand, pad_arec, arec_padding):
# save input parameters as variables
conv_calc_upc = calc_upc
conv_inc_arec = inc_arec
conv_inc_crec = inc_crec
conv_inc_headers = inc_headers
def convert_to_price(value):
return (value[:-2].lstrip("0") if not value[:-2].lstrip("0") == "" else "0") + "." + value[-2:]
with open(edi_process) as work_file: # open input file
work_file_lined = [n for n in work_file.readlines()] # make list of lines
f = open(output_filename, 'wb') # open work file, overwriting old file
if conv_inc_headers != "False": # include headers if flag is set
# write line out to file
f.write("{}" "," "{}" "," "{}" "," "{}" "," "{}" "," "{}" "," "{}\r\n"
.format("UPC", "Qty. Shipped", "Cost", "Suggested Retail",
"Description", "Case Pack", "Item Number").encode())
for line_num, line in enumerate(work_file_lined): # iterate over work file contents
input_edi_dict = line_from_mtc_edi_to_dict.capture_records(line)
# if include "A" records flag is set and line starts with "A"
if line.startswith("A") and conv_inc_arec != "False":
# write "A" line
f.write(((line[0:1] + arec_padding[0:6] + line[
7:]).rstrip() + "\r\n").encode()) if pad_arec == "True" \
else f.write((line.rstrip() + "\r\n").encode())
# the following block writes "B" lines, dependent on filter and convert settings
# ternary conditional operator: puts if-then-else statement in one line
# syntax: <expression1> if <condition> else <expression2>
# needs to be wrapped an parenthesis to separate statements
if line.startswith("B"):
blank_upc = False
upc_string = ""
try:
_ = int(input_edi_dict['upc_number'].rstrip())
except ValueError:
blank_upc = True
if blank_upc is False:
proposed_upc = input_edi_dict['upc_number'].strip()
if len(str(proposed_upc)) == 11:
upc_string = str(proposed_upc) + str(upc_e_to_upc_a.calc_check_digit(proposed_upc))
else:
if len(str(proposed_upc)) == 8:
upc_string = str(upc_e_to_upc_a.convert_UPCE_to_UPCA(proposed_upc))
upc_in_csv = "\t" + upc_string if conv_calc_upc != "False" and blank_upc is False \
else input_edi_dict['upc_number']
quantity_shipped_in_csv = input_edi_dict['qty_of_units'].lstrip("0") \
if not input_edi_dict['qty_of_units'].lstrip("0") == "" else input_edi_dict['qty_of_units']
cost_in_csv = convert_to_price(input_edi_dict['unit_cost'])
suggested_retail_in_csv = convert_to_price(input_edi_dict['suggested_retail_price'])
description_in_csv = (input_edi_dict['description'].replace("&", "AND").rstrip(" ")
if filter_ampersand != "False" else
input_edi_dict['description'].rstrip(" "))
case_pack_in_csv = input_edi_dict['unit_multiplier'].lstrip("0") \
if not input_edi_dict['unit_multiplier'].lstrip("0") == "" else input_edi_dict['unit_multiplier']
item_number_in_csv = input_edi_dict['vendor_item'].lstrip("0") \
if not input_edi_dict['vendor_item'].lstrip("0") == "" else input_edi_dict['vendor_item']
f.write(
'"'"{}"'"'","'"'"{}"'"'","'"'"{}"'"'","'"'"{}"'"'","'"'"{}"'"'","'"'"{}"'"'","'"'"{}"'"'"\r\n".
format(upc_in_csv, quantity_shipped_in_csv, cost_in_csv, suggested_retail_in_csv,
description_in_csv, case_pack_in_csv, item_number_in_csv).encode())
# if include "C" records flag is set and line starts with "C"
if line.startswith("C") and conv_inc_crec != "False":
f.write((line.rstrip() + "\r\n").encode()) # write "C" line
f.close() # close output file
def edi_convert(edi_process, output_filename, calc_upc, inc_arec, inc_crec,
inc_headers, filter_ampersand, pad_arec, arec_padding):
# save input parameters as variables
conv_calc_upc = calc_upc
conv_inc_arec = inc_arec
conv_inc_crec = inc_crec
conv_inc_headers = inc_headers
def convert_to_price(value):
return (value[:-2].lstrip("0") if not value[:-2].lstrip("0") == "" else "0") + "." + value[-2:]
with open(edi_process) as work_file: # open input file
work_file_lined = [n for n in work_file.readlines()] # make list of lines
f = open(output_filename, 'wb') # open work file, overwriting old file
if conv_inc_headers != "False": # include headers if flag is set
# write line out to file
f.write("{}" "," "{}" "," "{}" "," "{}" "," "{}" "," "{}" "," "{}\r\n"
.format("UPC", "Qty. Shipped", "Cost", "Suggested Retail",
"Description", "Case Pack", "Item Number").encode())
for line_num, line in enumerate(work_file_lined): # iterate over work file contents
input_edi_dict = line_from_mtc_edi_to_dict.capture_records(line)
# if include "A" records flag is set and line starts with "A"
if line.startswith("A") and conv_inc_arec != "False":
# write "A" line
f.write(((line[0:1] + arec_padding[0:6] + line[
7:]).rstrip() + "\r\n").encode()) if pad_arec == "True" \
else f.write((line.rstrip() + "\r\n").encode())
# the following block writes "B" lines, dependent on filter and convert settings
# ternary conditional operator: puts if-then-else statement in one line
# syntax: <expression1> if <condition> else <expression2>
# needs to be wrapped an parenthesis to separate statements
if line.startswith("B"):
blank_upc = False
upc_string = ""
try:
_ = int(input_edi_dict['upc_number'].rstrip())
except ValueError:
blank_upc = True
if blank_upc is False:
proposed_upc = input_edi_dict['upc_number']
if len(str(proposed_upc).rstrip()) == 11:
upc_string = str(proposed_upc) + str(upc_e_to_upc_a.calc_check_digit(proposed_upc))
else:
if len(str(proposed_upc).rstrip()) == 8:
upc_string = str(upc_e_to_upc_a.convert_UPCE_to_UPCA(proposed_upc.rstrip()))
upc_in_csv = "\t" + upc_string if conv_calc_upc != "False" and blank_upc is False \
else input_edi_dict['upc_number']
quantity_shipped_in_csv = input_edi_dict['qty_of_units'].lstrip("0") \
if not input_edi_dict['qty_of_units'].lstrip("0") == "" else input_edi_dict['qty_of_units']
cost_in_csv = convert_to_price(input_edi_dict['unit_cost'])
suggested_retail_in_csv = convert_to_price(input_edi_dict['suggested_retail_price'])
description_in_csv = (input_edi_dict['description'].replace("&", "AND").rstrip(" ")
if filter_ampersand != "False" else
input_edi_dict['description'].rstrip(" "))
case_pack_in_csv = input_edi_dict['unit_multiplier'].lstrip("0") \
if not input_edi_dict['unit_multiplier'].lstrip("0") == "" else input_edi_dict['unit_multiplier']
item_number_in_csv = input_edi_dict['vendor_item'].lstrip("0") \
if not input_edi_dict['vendor_item'].lstrip("0") == "" else input_edi_dict['vendor_item']
f.write(
'"'"{}"'"'","'"'"{}"'"'","'"'"{}"'"'","'"'"{}"'"'","'"'"{}"'"'","'"'"{}"'"'","'"'"{}"'"'"\r\n".
format(upc_in_csv, quantity_shipped_in_csv, cost_in_csv, suggested_retail_in_csv,
description_in_csv, case_pack_in_csv, item_number_in_csv).encode())
# if include "C" records flag is set and line starts with "C"
if line.startswith("C") and conv_inc_crec != "False":
f.write((line.rstrip() + "\r\n").encode()) # write "C" line
f.close() # close output file
def edi_convert(edi_process, output_filename, settings_dict):
def adjust_column_width(adjust_worksheet):
print("Adjusting column width for {} worksheet".format(
adjust_worksheet.title))
for col in adjust_worksheet.columns:
max_length = 0
column = col[0].column_letter # Get the column name
for cell in col:
try: # Necessary to avoid error on empty cells
if len(str(cell.value)) > max_length:
# Convert cell contents to str, cannot get len of int
max_length = len(str(cell.value))
except Exception as resize_error:
print(resize_error)
adjusted_width = (max_length + 2) * 1.2
adjust_worksheet.column_dimensions[column].width = adjusted_width
work_file = open(edi_process) # open input file
work_file_lined = [n for n in work_file.readlines()] # make list of lines
invoice_list = []
for line_num, line in enumerate(
work_file_lined): # iterate over work file contents
input_edi_dict = line_from_mtc_edi_to_dict.capture_records(line)
try:
if input_edi_dict["record_type"] == "A":
invoice_list.append(input_edi_dict["invoice_number"][-7:])
except TypeError:
pass
print(invoice_list)
query_object = query_runner(
settings_dict["as400_username"],
settings_dict["as400_password"],
settings_dict["as400_address"],
f"{settings_dict['odbc_driver']}",
)
output_spreadsheet = openpyxl.Workbook()
output_worksheet = output_spreadsheet.worksheets[0]
invoice_rows = []
invoice_row_counter = 0
for invoice in invoice_list:
result = query_object.run_arbitrary_query(f"""SELECT buj4cd AS "UPC",
bubacd AS "Item",
bufbtx AS "Description",
ancctx AS "Pack",
buhxtx AS "U/M",
bue4qt AS "Qty",
bud2pr AS "Price",
bueapr AS "Retail"
FROM dacdata.odhst odhst
INNER JOIN dacdata.dsanrep dsanrep
ON odhst.bubacd = dsanrep.anbacd
WHERE odhst.buhhnb = {invoice}
AND bue4qt <> 0""")
rows_for_export = []
for row in result:
trow = [""]
# attempt to strip whitespace from entries in row
for entry in row:
try:
trow.append(entry.strip())
except AttributeError:
trow.append(entry)
rows_for_export.append(trow)
print(trow)
output_worksheet.append(['', invoice])
invoice_row_counter += 1
invoice_rows.append(invoice_row_counter)
for items_list_entry in rows_for_export:
output_worksheet.append(items_list_entry)
invoice_row_counter += 1
adjust_column_width(output_worksheet)
output_spreadsheet_name = output_filename
output_spreadsheet.save(output_spreadsheet_name)
def generate_barcode(input_string, tempdir):
ean = barcode.get("UPCA")
# select output image size via dpi. internally, pybarcode renders as svg, then renders that as a png file.
# dpi is the conversion from svg image size in mm, to what the image writer thinks is inches.
options = {
'dpi': 130,
'module_height': 5.0,
'text_distance': 1,
'font_size': 6,
'quiet_zone': 2
}
# ean.default_writer_options['dpi'] = int(130)
# # module height is the barcode bar height in mm
# ean.default_writer_options['module_height'] = float(5)
# # text distance is the distance between the bottom of the barcode, and the top of the text in mm
# ean.default_writer_options['text_distance'] = 1
# # font size is the text size in pt
# ean.default_writer_options['font_size'] = int(6)
# # quiet zone is the distance from the ends of the barcode to the ends of the image in mm
# ean.default_writer_options['quiet_zone'] = 2
# save barcode image with generated filename
print("generating barcode image")
with tempfile.NamedTemporaryFile(dir=tempdir,
suffix='.png',
delete=False) as initial_temp_file:
ean(input_string, writer=ImageWriter()).write(initial_temp_file,
options=options)
filename = initial_temp_file.name
print(filename)
print("success, barcode image path is: " + filename)
print("opening " + str(filename) + " to add border")
barcode_image = pil_Image.open(
str(filename)) # open image as pil object
print("success")
print("adding barcode and saving")
img_save = pil_ImageOps.expand(barcode_image, border=0,
fill='white') # add border around image
width, height = img_save.size # get image size of barcode with border
# write out image to file
with tempfile.NamedTemporaryFile(dir=tempdir,
suffix='.png',
delete=False) as final_barcode_path:
img_save.save(final_barcode_path.name)
print("success, final barcode path is: " + final_barcode_path.name)
return final_barcode_path.name, width, height
def interpret_barcode_string(upc_barcode_string):
if not upc_barcode_string == '':
try:
_ = int(upc_barcode_string
) # check that "upc_barcode_string" can be cast to int
except ValueError:
raise ValueError("Input contents are not an integer")
# select barcode type, specify barcode, and select image writer to save as png
if len(upc_barcode_string) < 10:
upc_barcode_string = upc_barcode_string.rjust(11, '0')
if len(upc_barcode_string) <= 11:
upc_barcode_string = upc_barcode_string.ljust(12, '0')
else:
raise ValueError("Input contents are more than 11 characters")
else:
raise ValueError("Input is empty")
return upc_barcode_string
def do_process_workbook():
# this is called as a background thread to ensure the interface is responsive
print("creating temp directory")
with tempfile.TemporaryDirectory() as tempdir:
print("temp directory created as: " + tempdir)
count = 0
save_counter = 0
for _ in output_worksheet.iter_rows(
): # iterate over all rows in current worksheet
try:
count += 1
if count not in invoice_rows:
# get code from column selected in input_colum_spinbox, on current row,
# add a zeroes to the end if option is selected to make seven or 12 digits
print("getting cell contents on line number " +
str(count))
upc_barcode_string = str(
output_worksheet["B" + str(count)].value)
print("cell contents are: " + upc_barcode_string)
print(upc_barcode_string[-12:][:-1])
upc_barcode_string = interpret_barcode_string(
upc_barcode_string[-12:][:-1])
print(upc_barcode_string)
generated_barcode_path, width, height = generate_barcode(
upc_barcode_string, tempdir)
# resize cell to size of image
output_worksheet.column_dimensions['A'].width = int(
math.ceil(float(width) * .15))
output_worksheet.row_dimensions[count].height = int(
math.ceil(float(height) * .75))
# open image with as openpyxl image object
print("opening " + generated_barcode_path +
" to insert into output spreadsheet")
img = OpenPyXlImage(generated_barcode_path)
print("success")
# attach image to cell
print("adding image to cell")
# add image to cell
output_worksheet.add_image(img,
anchor='A' + str(count))
save_counter += 1
print("success")
except Exception as barcode_error:
print(barcode_error)
# This save in the loop frees references to the barcode images,
# so that python's garbage collector can clear them
if save_counter >= 100:
print("saving intermediate workbook to free file handles")
output_spreadsheet.save(output_spreadsheet_name)
print("success")
save_counter = 1
print("saving workbook to file")
output_spreadsheet.save(output_spreadsheet_name)
print("success")
do_process_workbook()
return output_filename
def do_split_edi(edi_process, work_directory):
# credit for the col_to_excel goes to Nodebody on stackoverflow, at this link: http://stackoverflow.com/a/19154642
def col_to_excel(col): # col is 1 based
excel_col = str()
div = col
while div:
(div, mod) = divmod(div - 1, 26) # will return (x, 0 .. 25)
excel_col = chr(mod + 65) + excel_col
return excel_col
f = None
output_file_path = None
count = 0
write_counter = 0
edi_send_list = []
if not os.path.exists(work_directory):
os.mkdir(work_directory)
with open(edi_process) as work_file: # open input file
lines_in_edi = sum(1 for _ in work_file)
work_file.seek(0)
work_file_lined = [n for n in work_file.readlines()
] # make list of lines
list_of_first_characters = []
for line in work_file_lined:
list_of_first_characters.append(line[0])
if list_of_first_characters.count("A") > 700:
return edi_send_list
for line_mum, line in enumerate(
work_file_lined): # iterate over work file contents
writeable_line = line
if writeable_line.startswith("A"):
count += 1
prepend_letters = col_to_excel(count)
line_dict = line_from_mtc_edi_to_dict.capture_records(
writeable_line)
if int(line_dict['invoice_total']) < 0:
file_name_suffix = '.cr'
else:
file_name_suffix = '.inv'
if not len(edi_send_list) == 0:
f.close()
edi_send_list.append(output_file_path)
output_file_path = os.path.join(
work_directory, prepend_letters + " " +
os.path.basename(edi_process) + file_name_suffix)
f = open(output_file_path, 'wb')
f.write(writeable_line.replace('\n', "\r\n").encode())
write_counter += 1
f.close() # close output file
edi_send_list.append(output_file_path)
edi_send_list.pop(0)
edi_send_list_lines = 0
for output_file in edi_send_list:
with open(output_file) as file_handle:
edi_send_list_lines += sum(1 for _ in file_handle)
if not lines_in_edi == write_counter:
raise Exception("not all lines in input were written out")
if not lines_in_edi == edi_send_list_lines:
raise Exception(
"total lines in output files do not match input file")
if not len(edi_send_list) == list_of_first_characters.count("A"):
raise Exception('mismatched number of "A" records')
if len(edi_send_list) < 1:
raise Exception("No Split EDIs")
return edi_send_list
