#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()