72 lines
2.1 KiB
Python
72 lines
2.1 KiB
Python
|
#!/usr/bin/env python3
|
||
|
|
"""
|
||
|
|
Simple checker for Shandex shipments in X3.
|
||
|
|
Looks for order numbers in X3's Delivery tracking number field.
|
||
|
|
Shipments sometimes cannot be brought in due to inventory, or they might
|
||
|
|
be in with a different tracking number.
|
||
|
|
"""
|
||
|
|
from tkinter import filedialog
|
||
|
|
import pprint
|
||
|
|
import openpyxl
|
||
|
|
import yamamotoyama
|
||
|
|
|
||
|
|
def main():
|
||
|
|
filecleanup()
|
||
|
|
file = select_file()
|
||
|
|
data = get_shipment_information(file)
|
||
|
|
statuses = shipment_check(data[2:])
|
||
|
|
write_to_file(statuses)
|
||
|
|
|
||
|
|
|
||
|
|
def write_to_file(statuses):
|
||
|
|
saveto = filedialog.asksaveasfilename(initialfile='report.txt')
|
||
|
|
with open(saveto, 'w', encoding='utf8') as outfile:
|
||
|
|
for status in statuses:
|
||
|
|
outfile.write(status)
|
||
|
|
outfile.write('\n')
|
||
|
|
|
||
|
|
|
||
|
|
def shipment_check(order_list):
|
||
|
|
reportables = []
|
||
|
|
with yamamotoyama.get_connection() as db_connection:
|
||
|
|
for shandex_order in order_list:
|
||
|
|
result = db_connection.query(
|
||
|
|
"""
|
||
|
|
select
|
||
|
|
SDHNUM_0,
|
||
|
|
YLICPLATE_0
|
||
|
|
from PROD.SDELIVERY
|
||
|
|
where YLICPLATE_0 = :order
|
||
|
|
""",
|
||
|
|
order=shandex_order,
|
||
|
|
).all()
|
||
|
|
if len(result) == 0:
|
||
|
|
reportables.append(shandex_order + ' was not found in X3.')
|
||
|
|
elif len(result) > 1:
|
||
|
|
reportables.append(shandex_order + ' was found multiple times.')
|
||
|
|
else:
|
||
|
|
reportables.append(shandex_order +' was found! ' + result[0]['SDHNUM_0']+ '.')
|
||
|
|
reportables.append('Orders might not be in X3 due to inventory, or missing information in EDI transmissions. Contact IT.')
|
||
|
|
return reportables
|
||
|
|
|
||
|
|
def get_shipment_information(file):
|
||
|
|
seen_orders = []
|
||
|
|
wb = openpyxl.load_workbook(file)
|
||
|
|
ws = wb['Shipped Orders']
|
||
|
|
for row in ws.iter_rows():
|
||
|
|
if row[1].value not in seen_orders:
|
||
|
|
seen_orders.append(row[1].value)
|
||
|
|
return seen_orders
|
||
|
|
|
||
|
|
|
||
|
|
def filecleanup():
|
||
|
|
return True
|
||
|
|
|
||
|
|
|
||
|
|
def select_file():
|
||
|
|
filename = filedialog.askopenfilename(title='Choose Excel file from Shandex')
|
||
|
|
return filename
|
||
|
|
|
||
|
|
|
||
|
|
if __name__ == "__main__":
|
||
|
|
main()
|