Jan-25-2024, 03:48 PM
I used a previous 3rd party python code and edited it to fit my new task, trying to understand each part of it and wanted to attempt to edit it on my own before going back to the contractor. Function of the code in basic terms, pull data from a SQL DB for a certain vendor of ours with a few filters and export it into a txt file. Once that file is outputted update a table in the SQL to mark po's as sent ('DONE'). My dilemma is on the output file it sometimes contains more than one ponumber, how can I only include one po number per file. We can do this manually by running the script after a po is created each time, but I would like to automate it a little more. Very new to all this and trying to learn what each part of the code is doing, left off output and beginning of code.
Python 3.11.4 IDLE
Python 3.11.4 IDLE
#Query POs cursor.execute(""" SELECT 'Ariat' as Vendor, header.PurchaseOrderNo, header.ShipVia, CI_Item.UDF_UPC, detail.QuantityOrdered, header.ShipToName, u.UDF_USERNAME, header.ShipToAddress1, header.ShipToAddress2, header.ShipToCity, header.ShipToState, header.ShipToZipCode, u.UDF_USEREMAIL as ConfirmationEmail FROM PO_PurchaseOrderHeader as header LEFT JOIN PO_PurchaseOrderDetail as detail ON header.PurchaseOrderNo = detail.PurchaseOrderNo LEFT JOIN CI_Item ON CI_Item.ItemCode = detail.ItemCode LEFT JOIN SY_UDT_USERID as u ON u.UDF_USERKEY = header.UserCreatedKey LEFT JOIN Action.dbo.po_status as status ON status.vendor = 'Ariat' AND status.po_number = header.PurchaseOrderNo WHERE header.VendorNo = '0000085' AND header.OnHold = 'N' AND (status.id IS NULL OR status.status <> 'DONE' OR header.UDF_METHODSENT like '%error%') """) processed_pos = [] dataFetched = False #Write file rows = cursor.fetchall() dataFetched = len(rows) > 0 fileData = "" for row in rows: processed_pos.append(str(row[1])) for data in row: fileData += str(data) + "," fileData = fileData[0:-1] + "\n" file_path = outputPath + "/" + "ariat-" + datetime.datetime.now().strftime("%m-%d-%Y-%H%M%S") + ".csv" if dataFetched: file = open(file_path, "w") file.write(fileData) file.close() else: exit(0) #Update status table for po in processed_pos: cursor.execute(f""" DECLARE @count AS INT = (SELECT COUNT(0) FROM Action.dbo.po_status WHERE po_number = '{po}' AND vendor = 'Ariat') IF @count <> 0 UPDATE Action.dbo.po_status SET status = 'DONE' WHERE po_number = '{po}' AND vendor = 'Ariat' ELSE INSERT INTO Action.dbo.po_status(vendor, po_number, status) VALUES ('Ariat', '{po}', 'DONE') """) cursor.execute(f"UPDATE PO_PurchaseOrderHeader SET UDF_METHODSENT = 'FTP' WHERE PurchaseOrderNo = '{po}' AND UDF_METHODSENT like '%error%'") cursor.commit()