mirror of
https://github.com/bcomsugi/dasaproject.git
synced 2026-01-09 15:22:37 +07:00
587 lines
30 KiB
Python
587 lines
30 KiB
Python
import xml.etree.ElementTree as ET
|
|
import win32com.client
|
|
import xmltodict
|
|
import pprint
|
|
import datetime
|
|
import pandas as pd
|
|
from datetime import date
|
|
import timeit
|
|
import os
|
|
|
|
class InvoiceQuery:
|
|
def __init__(self, **kwargs) -> None:
|
|
# print(f'kwargs:{kwargs}')
|
|
# print(args)
|
|
# self.InvoiceList=[]
|
|
self.PriceLevelName = None
|
|
self.SPPriceLevelName = None
|
|
self.item_inventory_path = "ItemInventory"
|
|
self.price_level_filename = "PriceLevel.xlsx"
|
|
self._df_price_level = pd.read_excel(os.path.join(os.getcwd(), self.item_inventory_path, self.price_level_filename), usecols=['FullName', 'PriceLevelName', 'CustomPrice'],)
|
|
print(self._df_price_level)
|
|
print(type(self._df_price_level.loc[(self._df_price_level['FullName']=="ECO:0:ECO-002") & (self._df_price_level['PriceLevelName']=="T 202202")].values.tolist()[0][2]))
|
|
self.FullName = kwargs['FullName'] if 'FullName' in kwargs else None
|
|
|
|
self.CustomerPriceLevelName_filename = "CustomerList.xlsx"
|
|
self._df_customer = pd.read_excel(os.path.join(os.getcwd(), self.item_inventory_path, self.CustomerPriceLevelName_filename), usecols=['FullName', 'PriceLevelName', 'SPName'],)
|
|
self._df_customer = self._df_customer.fillna('')
|
|
print(self._df_customer)
|
|
|
|
self.Customer = self._df_customer.loc[(self._df_customer["FullName"]==self.FullName)].values.tolist()[0]
|
|
print(f'Customer:{self.Customer}')
|
|
self.DN = kwargs['DN'] if 'DN' in kwargs else {}
|
|
self.Reuse = kwargs['Reuse'] if 'Reuse' in kwargs else None
|
|
|
|
self.InvoiceList= kwargs['InvoiceList'] if 'InvoiceList' in kwargs else []
|
|
self.InvoiceList = None
|
|
self.InvoiceType = kwargs['InvoiceType'] if 'InvoiceType' in kwargs else 'SalesByCustomerSummary'
|
|
self.IncludeLineItems = kwargs['IncludeLineItems'] if 'IncludeLineItems' in kwargs else 'true'
|
|
self.IncludeRetElement = kwargs['IncludeRetElement'] if 'IncludeRetElement' in kwargs else []
|
|
self.TxnDateRangeFilter = kwargs['TxnDateRangeFilter'] if 'TxnDateRangeFilter' in kwargs else None
|
|
self.DateMacro = None
|
|
self.MaxReturned = kwargs['MaxReturned'] if 'MaxReturned' in kwargs else '1'
|
|
|
|
if 'DateMacro' in kwargs:
|
|
if kwargs['DateMacro'] in ['All', 'Today', 'ThisWeek', 'ThisWeekToDate', 'ThisMonth', 'ThisMonthToDate', 'ThisQuarter', 'ThisQuarterToDate', 'ThisYear', 'ThisYearToDate', 'Yesterday', 'LastWeek', 'LastWeekToDate', 'LastMonth', 'LastMonthToDate', 'LastQuarter', 'LastQuarterToDate', 'LastYear', 'LastYearToDate', 'NextWeek', 'NextFourWeeks', 'NextMonth', 'NextQuarter', 'NextYear']:
|
|
self.DateMacro = kwargs['DateMacro']
|
|
today = datetime.date.today()
|
|
deltatoday = today - datetime.timedelta(days = 74)
|
|
print(today, deltatoday)
|
|
self.FromTxnDate = self.validate_date(kwargs['FromTxnDate']) if 'FromTxnDate' in kwargs else deltatoday
|
|
self.ToTxnDate = self.validate_date(kwargs['ToTxnDate']) if 'ToTxnDate' in kwargs else today
|
|
self.ReportEntityFilter = kwargs['ReportEntityFilter'] if 'ReportEntityFilter' in kwargs else None
|
|
|
|
# print(self.DateMacro, self.TxnDateRangeFilter, self.FromTxnDate, self.ToTxnDate)
|
|
# print(self.Reuse)
|
|
# if not self.Reuse:
|
|
# self.dfDN, self.ext_doc_no_list = self.get_ext_doc_no_list(self.DN)
|
|
# self.RefNumber = kwargs['RefNumber'] if 'RefNumber' in kwargs else self.ext_doc_no_list
|
|
# self.get_sales_order_header()
|
|
|
|
def create_sub_element(self, ET, parentNode, thisNode, text="\n", whiteSpace = 0, attrib =None):
|
|
if type(attrib) is not dict:
|
|
attrib = {}
|
|
ele = ET.SubElement(parentNode, thisNode)
|
|
for x in attrib:
|
|
ele.set(x, attrib[x])
|
|
ele.text = text
|
|
tail = "\n"
|
|
for x in range(whiteSpace):
|
|
tail = tail + " "
|
|
ele.tail = tail
|
|
return ele
|
|
|
|
def create_QBXML(self):
|
|
root = ET.Element("QBXML")
|
|
root.tail = "\n"
|
|
root.text = "\n "
|
|
QBXMLMsgsRq = ET.SubElement(root, "QBXMLMsgsRq")
|
|
# QBXMLMsgsRq.set("onError", "continueOnError")
|
|
QBXMLMsgsRq.set("onError", "stopOnError")
|
|
QBXMLMsgsRq.tail = "\n"
|
|
QBXMLMsgsRq.text = "\n "
|
|
InvoiceQueryRq = self.create_sub_element(ET, QBXMLMsgsRq, "InvoiceQueryRq","\n " )
|
|
# InvoiceType = self.create_sub_element(ET, InvoiceQueryRq, 'InvoiceType', self.InvoiceType)
|
|
# if self.FullName:
|
|
# EntityFilter = self.create_sub_element(ET, InvoiceQueryRq, 'EntityFilter', "\n ")
|
|
# FullName = self.create_sub_element(ET, EntityFilter, "FullName", self.FullName, 6)
|
|
# if self.MaxReturned:
|
|
# MaxReturned = self.create_sub_element(ET, InvoiceQueryRq, 'MaxReturned', self.MaxReturned, 6)
|
|
if self.DateMacro:
|
|
TxnDateRangeFilter = self.create_sub_element(ET, InvoiceQueryRq, "TxnDateRangeFilter", "\n ",)
|
|
InvoiceType = self.create_sub_element(ET, TxnDateRangeFilter, "DateMacro", self.DateMacro)
|
|
# InvoiceType = self.create_sub_element(ET, InvoiceQueryRq, "DateMacro", self.DateMacro)
|
|
|
|
elif type(self.FromTxnDate) is datetime.date or type(self.ToTxnDate) is datetime.date:
|
|
TxnDateRangeFilter = self.create_sub_element(ET, InvoiceQueryRq, "TxnDateRangeFilter", "\n ",)
|
|
if type(self.FromTxnDate) is datetime.date:
|
|
FromTxnDate = self.create_sub_element(ET, TxnDateRangeFilter, "FromTxnDate", self.FromTxnDate.strftime('%Y-%m-%d'),4)
|
|
if type(self.ToTxnDate) is datetime.date:
|
|
ToTxnDate = self.create_sub_element(ET, TxnDateRangeFilter, "ToTxnDate", self.ToTxnDate.strftime('%Y-%m-%d'))
|
|
if self.IncludeLineItems:
|
|
IncludeLineItems = self.create_sub_element(ET, InvoiceQueryRq, "IncludeLineItems", self.IncludeLineItems, 4)
|
|
if len(self.IncludeRetElement)>0:
|
|
for x in self.IncludeRetElement:
|
|
IncludeRetElement = self.create_sub_element(ET, InvoiceQueryRq, "IncludeRetElement", x, 4)
|
|
mydata = ET.tostring(root, encoding = "unicode")
|
|
|
|
qbxml_query = """<?xml version="1.0" encoding="utf-8"?>\n"""
|
|
qbxml_query = qbxml_query + """<?qbxml version="13.0"?>"""
|
|
qbxml_query = qbxml_query + "\n" + mydata
|
|
# print(f'create_QBXML->qbxml_query: {qbxml_query}')
|
|
return qbxml_query
|
|
|
|
def get_customer_pricelevel(self, response_string):
|
|
QBXML = ET.fromstring(response_string)
|
|
PriceLevelName = QBXML.find('.//PriceLevelRef')
|
|
if PriceLevelName:
|
|
|
|
PriceLevelName = PriceLevelName.find("FullName").text
|
|
print(f'PriceLevelName:{PriceLevelName}')
|
|
DataExtRets = QBXML.findall('.//DataExtRet')
|
|
SP_PriceLevelName = None
|
|
if len(DataExtRets)>0:
|
|
for DataExtRet in DataExtRets:
|
|
DataExtName = DataExtRet.find('DataExtName').text
|
|
DataExtValue = DataExtRet.find('DataExtValue').text
|
|
if DataExtName.lower() == 'special cust'.lower():
|
|
SP_PriceLevelName = DataExtValue.upper()
|
|
break
|
|
self.PriceLevelName = PriceLevelName
|
|
self.SPPriceLevelName = SP_PriceLevelName
|
|
return PriceLevelName, SP_PriceLevelName
|
|
|
|
def create_customerquery_QBXML(self ):
|
|
root = ET.Element("QBXML")
|
|
root.tail = "\n"
|
|
root.text = "\n "
|
|
QBXMLMsgsRq = ET.SubElement(root, "QBXMLMsgsRq")
|
|
# QBXMLMsgsRq.set("onError", "continueOnError")
|
|
QBXMLMsgsRq.set("onError", "stopOnError")
|
|
QBXMLMsgsRq.tail = "\n"
|
|
QBXMLMsgsRq.text = "\n "
|
|
CustomerQueryRq = self.create_sub_element(ET, QBXMLMsgsRq, "CustomerQueryRq","\n " )
|
|
# InvoiceType = self.create_sub_element(ET, CustomerQueryRq, 'InvoiceType', self.InvoiceType)
|
|
FullName = self.create_sub_element(ET, CustomerQueryRq, "FullName", self.FullName, 6)
|
|
IncludeRetElement = ['FullName', 'PriceLevelRef', 'DataExtRet']
|
|
for x in IncludeRetElement:
|
|
IncludeRetElement = self.create_sub_element(ET, CustomerQueryRq, "IncludeRetElement", x, 4)
|
|
OwnerID = self.create_sub_element(ET, CustomerQueryRq, "OwnerID", "0", 6)
|
|
mydata = ET.tostring(root, encoding = "unicode")
|
|
|
|
qbxml_query = """<?xml version="1.0" encoding="utf-8"?>\n"""
|
|
qbxml_query = qbxml_query + """<?qbxml version="13.0"?>"""
|
|
qbxml_query = qbxml_query + "\n" + mydata
|
|
print(f'create_customer_QBXML->qbxml_query: {qbxml_query}')
|
|
response_string=self.connect_to_quickbooks(qbxml_query)
|
|
|
|
return self.get_customer_pricelevel(response_string)
|
|
return response_string
|
|
|
|
def create_invoiceadd_QBXML(self):
|
|
print('create_ionvoiceadd_QBXML')
|
|
root = ET.Element("QBXML")
|
|
root.tail = "\n"
|
|
root.text = "\n "
|
|
QBXMLMsgsRq = ET.SubElement(root, "QBXMLMsgsRq")
|
|
QBXMLMsgsRq.set("onError", "stopOnError")
|
|
QBXMLMsgsRq.tail = "\n"
|
|
QBXMLMsgsRq.text = "\n "
|
|
InvoiceAddRq = self.create_sub_element(ET, QBXMLMsgsRq, "InvoiceAddRq", "\n ",2 )
|
|
InvoiceAdd = self.create_sub_element(ET, InvoiceAddRq, "InvoiceAdd", "\n ",4 )
|
|
CustomerRef = self.create_sub_element(ET, InvoiceAdd, "CustomerRef", "\n ",8 )
|
|
FullName = self.create_sub_element(ET, CustomerRef, "FullName", self.InvoiceList[0]['CustomerFullName'], 8 )
|
|
TemplateRef = self.create_sub_element(ET, InvoiceAdd, "TemplateRef", "\n ", 8 )
|
|
TemplateFullName = self.create_sub_element(ET, TemplateRef, "FullName", "DBW Invoice (11%)", 8 )
|
|
today = str(date.today())
|
|
TxnDate = self.create_sub_element(ET, InvoiceAdd, "TxnDate", today,8) # self.DN['TxnDate'], 8 )
|
|
|
|
RefNumber = self.create_sub_element(ET, InvoiceAdd, "RefNumber", today,8 ) # self.DN['DNRefNum'], 8 )
|
|
ShipDate = self.create_sub_element(ET, InvoiceAdd, "ShipDate", today,8) # self.DN['TxnDate'], 8 )
|
|
# Memo = self.create_sub_element(ET, InvoiceAdd, "Memo", self.DN['Memo'], 10 )
|
|
disc_amount = 0
|
|
for soidx, salesorder in enumerate(self.InvoiceList):
|
|
SOTxnId = salesorder['TxnID']
|
|
disc_amount+=int(salesorder['Disc_Amount'])
|
|
print(f'create_invoiceadd_QBXML->SOTxnId: {SOTxnId}')
|
|
for itemline in salesorder['InvoiceLineRet']:
|
|
# if 'DNQuantity' in itemline:
|
|
InvoiceLineAdd = self.create_sub_element(ET, InvoiceAdd, "InvoiceLineAdd", "\n ", 10 )
|
|
# Quantity = self.create_sub_element(ET, InvoiceLineAdd, "Quantity", str(itemline['DNQuantity'] ), 12 )
|
|
# Quantity = self.create_sub_element(ET, InvoiceLineAdd, "Quantity", str(itemline['BackOrdered'] ), 12 )
|
|
# UnitOfMeasure = self.create_sub_element(ET, InvoiceLineAdd, "UnitOfMeasure", str(itemline['UOM']), 12 )
|
|
LinkToTxn = self.create_sub_element(ET, InvoiceLineAdd, "LinkToTxn", "\n ", 10 )
|
|
TxnID = self.create_sub_element(ET, LinkToTxn, "TxnID", SOTxnId,14 )
|
|
TxnLineID = self.create_sub_element(ET, LinkToTxn, "TxnLineID", itemline['TxnLineID'], 12 )
|
|
if soidx == len(self.InvoiceList)-1: #last list then set the'400_Sales_discount'
|
|
print(f'disc_amount:{format(disc_amount, ".2f")}')
|
|
if disc_amount != 0:
|
|
|
|
# disc_amount=format(disc_amount, ".2f")
|
|
InvoiceLineAdd = self.create_sub_element(ET, InvoiceAdd, "InvoiceLineAdd", "\n ", 10 )
|
|
ItemRef = self.create_sub_element(ET, InvoiceLineAdd, "ItemRef", "\n ", 12)
|
|
ItemFullName = self.create_sub_element(ET, ItemRef, "FullName", "400_Sales Discount", 12)
|
|
ItemRate = self.create_sub_element(ET, InvoiceLineAdd, "Rate", str(disc_amount), 10)
|
|
|
|
mydata = ET.tostring(root, encoding = "unicode")
|
|
|
|
qbxml_query = """<?xml version="1.0" encoding="utf-8"?>\n"""
|
|
qbxml_query = qbxml_query + """<?qbxml version="13.0"?>"""
|
|
qbxml_query = qbxml_query + "\n" + mydata
|
|
# print(f'create_invoiceadd_QBXML->Create_Invoiceadd_QBXML: {qbxml_query}')
|
|
# print(f"replyfrom qbxml:{self.connect_to_quickbooks(qbxml_query)}")
|
|
|
|
return qbxml_query
|
|
|
|
|
|
def connect_to_quickbooks(self, qbxml_query):
|
|
# Connect to Quickbooks
|
|
# sessionManager = win32com.client.Dispatch("QBXMLRP2.RequestProcessor")
|
|
# sessionManager.OpenConnection('', 'DASA')
|
|
# enumfodnc= win32com.client.Dispatch('QBXMLRP2.RequestProcessor')
|
|
# print(enumfodnc)
|
|
# print(enumfodnc.qbFileOpenDoNotCare)
|
|
sessionManager = win32com.client.Dispatch("QBXMLRP2.RequestProcessor")
|
|
sessionManager.OpenConnection('', 'DASA2')
|
|
# ticket = sessionManager.BeginSession("z:\\DBW Bogor.qbw", 2)
|
|
|
|
ticket = sessionManager.BeginSession("", 2)
|
|
|
|
# Send query and receive response
|
|
response_string = sessionManager.ProcessRequest(ticket, qbxml_query)
|
|
|
|
# Disconnect from Quickbooks
|
|
sessionManager.EndSession(ticket) # Close the company file
|
|
sessionManager.CloseConnection() # Close the connection
|
|
# print (f'response_string:{response_string}')
|
|
return response_string
|
|
|
|
def __str__(self, *args) -> str:
|
|
# return str(self._get_datarow(self.connect_to_quickbooks(self.create_QBXML())))
|
|
# print("__str__")
|
|
return str(self.get_sales_order_header())
|
|
# return "hello"
|
|
|
|
def get_sales_order_header(self, *args):
|
|
return self._get_sales_order_header(self.connect_to_quickbooks(self.create_QBXML()))
|
|
|
|
def status_ok(self, QBXML): #for InvoiceAddRS
|
|
|
|
tree = ET.fromstring(QBXML)
|
|
|
|
GSRQRs = tree.find(".//InvoiceQueryRs")
|
|
# print(f"GSRQRs:{GSRQRs}")
|
|
status_code = GSRQRs.attrib #.get('statusCode')
|
|
# print(GSRQRs.attrib)
|
|
# print(GSRQRs.attrib['statusCode'])
|
|
status=GSRQRs.attrib.get('statusMessage')
|
|
|
|
print(f'status={status}')
|
|
if 'OK' in status:
|
|
return True, status_code
|
|
else:
|
|
return False, status_code
|
|
|
|
|
|
def _get_sales_order_header(self, response_string):
|
|
print('_get_sales_order_header')
|
|
# print(f'responsestring:{response_string}')
|
|
QBXML = ET.fromstring(response_string)
|
|
datadict = {}
|
|
Invoicedict = {}
|
|
_Invoicelist = []
|
|
InvoiceRets = QBXML.findall('.//InvoiceRet')
|
|
# print(InvoiceRets)
|
|
for InvoiceRet in InvoiceRets:
|
|
RefNumber = InvoiceRet.find('RefNumber').text
|
|
# Memo = InvoiceRet.find('Memo').text
|
|
CustomerFullName = InvoiceRet.find('CustomerRef/FullName').text
|
|
TxnID = InvoiceRet.find('TxnID').text
|
|
TotalAmount = InvoiceRet.find('TotalAmount').text
|
|
IsFullyInvoiced = InvoiceRet.find('IsFullyInvoiced').text
|
|
IsManuallyClosed = InvoiceRet.find('IsManuallyClosed').text
|
|
# print(CustomerFullName, TxnID, TotalAmount)
|
|
Invoicedict = {'RefNumber':RefNumber, 'CustomerFullName':CustomerFullName, 'TxnID':TxnID,
|
|
'TotalAmount':TotalAmount, 'IsFullyInvoiced':IsFullyInvoiced, 'IsManuallyClosed':IsManuallyClosed, 'InvoiceLineRet':[]}
|
|
InvoiceLineRet = InvoiceRet.findall('InvoiceLineRet')
|
|
# print(len(InvoiceLineRet))
|
|
if len(InvoiceLineRet) > 0:
|
|
disc_amount=0
|
|
for InvoiceLineRet in InvoiceLineRet:
|
|
TxnLineID = InvoiceLineRet.find('TxnLineID').text
|
|
ItemFullName = InvoiceLineRet.find('ItemRef/FullName').text
|
|
Quantity = InvoiceLineRet.find('Quantity').text
|
|
UnitOfMeasure = InvoiceLineRet.find('UnitOfMeasure').text
|
|
Rate = float(InvoiceLineRet.find('Rate').text)
|
|
|
|
Amount = float(InvoiceLineRet.find('Amount').text)
|
|
# if self.SPPriceLevelName:
|
|
if self.Customer[2]:
|
|
# print(Rate, (Rate - self._df_price_level.loc[(self._df_price_level['FullName']==ItemFullName) & (self._df_price_level['PriceLevelName']==self.SPPriceLevelName)].values.tolist()[0][2]))
|
|
print(Quantity, Rate, (Rate - self._df_price_level.loc[(self._df_price_level['FullName']==ItemFullName) & (self._df_price_level['PriceLevelName']==self.Customer[2])].values.tolist()[0][2]))
|
|
disc_amount += float(Quantity) * (Rate-self._df_price_level.loc[(self._df_price_level['FullName']==ItemFullName) & (self._df_price_level['PriceLevelName']==self.Customer[2])].values.tolist()[0][2])
|
|
# disc_amount+=float(Quantity)*2000 #testing only
|
|
Invoiced = InvoiceLineRet.find('Invoiced').text
|
|
LineIsManuallyClosed = InvoiceLineRet.find('IsManuallyClosed').text
|
|
# print(TxnLineID, ItemFullName)
|
|
BackOrdered = float(Quantity) - float(Invoiced)
|
|
if BackOrdered:
|
|
InvoiceLinedict = {'TxnLineID':TxnLineID,
|
|
'ItemFullName':ItemFullName,
|
|
'Quantity':Quantity,
|
|
'UOM':UnitOfMeasure,
|
|
'Rate':Rate,
|
|
'Amount':Amount,
|
|
'BackOrdered':BackOrdered,
|
|
'Invoiced':Invoiced,
|
|
'LineIsManuallyClosed':LineIsManuallyClosed,
|
|
}
|
|
Invoicedict['InvoiceLineRet'].append(InvoiceLinedict)
|
|
Invoicedict['Disc_Amount']=disc_amount
|
|
_Invoicelist.append(Invoicedict)
|
|
# print(_Invoicelist)
|
|
self.InvoiceList=_Invoicelist
|
|
# print(f'_get_sales_order_header->Salesorderlist: {self.InvoiceList}')
|
|
return self.InvoiceList
|
|
|
|
def addDiscountToInvoiceList(self, _dict:dict):
|
|
print("addDiscountToInvoiceList")
|
|
|
|
|
|
def addDNQtyToInvoiceList(self, _dict:dict):
|
|
_bolfoundrefnum=False
|
|
_bol_dictisadded=False
|
|
Error_msg = None
|
|
for poidx, _po in enumerate(self.InvoiceList):
|
|
if _po['RefNumber']==_dict['RefNum']:
|
|
_bolfoundrefnum=True
|
|
if len(_po['InvoiceLineRet'])>0:
|
|
for polineidx, _poline in enumerate(_po['InvoiceLineRet']):
|
|
pass
|
|
if _poline['ItemFullName']==_dict['FullName']:
|
|
if _poline['UOM'].upper()==_dict['UOM'].upper():
|
|
# first do UOM in _dict convert treatment
|
|
QuantityIn_dict = _dict['Quantity']
|
|
if _dict['UOM'].upper().startswith('ROLL_'):
|
|
print(f"addDNQtyToInvoiceList->DNqty:{_dict['Quantity']}, Roll_:{_dict['UOM'].split('_')[1]}")
|
|
QuantityIn_dict = _dict['Quantity'] * int(_dict['UOM'].split("_")[1])
|
|
pass
|
|
elif _dict['UOM'].upper() == 'BOX' and _dict['Item No'].upper() == "CUTTER":
|
|
print("addDNQtyToInvoiceList->cutter")
|
|
elif _dict['UOM'].upper() == 'BOX' and (_dict['Item No'].upper().startswith("EB-") or _dict['Item No'].upper().startswith("TA-")):
|
|
print("addDNQtyToInvoiceList->LEM")
|
|
if _dict['Item No'].split("-")[1].endswith("1006"):
|
|
QuantityIn_dict = QuantityIn_dict * 12
|
|
elif _dict['Item No'].split("-")[1].endswith("1025"):
|
|
QuantityIn_dict = QuantityIn_dict * 6
|
|
elif _dict['Item No'].split("-")[1].endswith("1100"):
|
|
pass
|
|
print("1100 lem")
|
|
elif _dict['UOM'].upper() == 'BOX' and (_dict['Item No'].upper().startswith("TL-") or _dict['Item No'].upper().startswith("TFL-")):
|
|
print("addDNQtyToInvoiceList->Lock")
|
|
QuantityIn_dict = QuantityIn_dict * 20
|
|
elif _dict['UOM'].upper() == 'BOX' and _dict['Item No'].upper() == "EDG-TRIMMER":
|
|
QuantityIn_dict = QuantityIn_dict * 12 #coz box of 12
|
|
|
|
|
|
if _poline['BackOrdered']>=QuantityIn_dict:
|
|
self.InvoiceList[poidx]['InvoiceLineRet'][polineidx]['DNQuantity']=float(QuantityIn_dict)
|
|
_bol_dictisadded = True
|
|
else:
|
|
print(f"{_poline['ItemFullName']} BackOrdered < Qty in DN {_poline['BackOrdered']}<{QuantityIn_dict}")
|
|
Error_msg = f"BackOrdered < Qty in DN {_poline['BackOrdered']}<{QuantityIn_dict}"
|
|
else:
|
|
# print(f"UOM different {_poline['UOM']} <> {_dict['UOM']}")
|
|
Error_msg = f"UOM different {_poline['UOM']} <> {_dict['UOM']}"
|
|
else:
|
|
# print("errorpoline <>DN")
|
|
Error_msg = f"poline[ItemFullName] <> DN[FullName]; {_poline['ItemFullName']} <> {_dict['FullName']}, maybe there are 2 same namefromtaco in QB"
|
|
else:
|
|
pass
|
|
# print(f"this refnum {_dict['RefNum']} have no QB PO Return Line")
|
|
Error_msg = f"this refnum {_dict['RefNum']} have no QB PO Return Line"
|
|
|
|
# print (_bol_dictisadded, Error_msg)
|
|
return _bol_dictisadded, Error_msg
|
|
|
|
|
|
def prepareInvoice(self, df:pd.DataFrame = None):
|
|
# print(df)
|
|
_bolAllDNareOk = True
|
|
_notindflist=[]
|
|
_yescounter = 0
|
|
_nocounter = 0
|
|
if df is not None:
|
|
_dflist = df.to_dict('records')
|
|
# print(_dflist)
|
|
|
|
# else:
|
|
# _dflist = self.dfDN.to_dict('records')
|
|
# print(self.dfDN)
|
|
# print(f'_dflist:{_dflist}')
|
|
for idx, xdf in enumerate(_dflist):
|
|
_boladdDN, _Errormsg = self.addDNQtyToInvoiceList(xdf)
|
|
# print(f'prepareInvoice->_Errormsg:{_Errormsg}')
|
|
if _boladdDN:
|
|
_dflist[idx]['ADDED']=True
|
|
elif _Errormsg:
|
|
_dflist[idx]['ERROR']=_Errormsg
|
|
for xdf in (_dflist):
|
|
if 'ADDED' not in xdf:
|
|
# print (f"prepareInvoice->not added: {xdf['Item No']}")
|
|
print (f"prepareInvoice->not added: {xdf}")
|
|
|
|
_notindflist.append(xdf)
|
|
_nocounter+=1
|
|
_bolAllDNareOk = False
|
|
else:
|
|
print (f"ADDED: {xdf['Item No']}")
|
|
|
|
print(f'{len(_dflist) - _nocounter} of {len(_dflist)} are added')
|
|
return _bolAllDNareOk, _notindflist
|
|
|
|
|
|
def validate_date(self, date_text):
|
|
if date_text is None:
|
|
return None
|
|
try:
|
|
return datetime.datetime.strptime(date_text, '%Y-%m-%d').date()
|
|
except ValueError:
|
|
return None
|
|
# raise ValueError("Incorrect data format, should be YYYY-MM-DD")
|
|
|
|
def get_ext_doc_no_list(self, dndict=None):
|
|
if dndict:
|
|
dnlist = dndict['lines']
|
|
else:
|
|
dnlist = self.DN
|
|
df = pd.DataFrame(dnlist)
|
|
# print(df)
|
|
df['RefNum']= df['No.SO/Ext.Doc.No.'].apply(lambda x: "L"+ x.split("L-")[1])
|
|
# print(df)
|
|
ext_doc_no=df['RefNum'].unique().tolist()
|
|
if len(ext_doc_no)>0:
|
|
return df, ext_doc_no
|
|
else:
|
|
return df, None
|
|
|
|
|
|
def get_last_refnumber(self, response_string=None):
|
|
if not response_string:
|
|
response_string = self.connect_to_quickbooks(self.create_QBXML())
|
|
status, status_msg = self.status_ok(response_string)
|
|
if not status:
|
|
return None
|
|
QBXML = ET.fromstring(response_string)
|
|
_OpenInvoicelist = []
|
|
InvoiceRets = QBXML.findall('.//RefNumber')
|
|
print(f'InvoiceRets count:{len(InvoiceRets)}')
|
|
refnumbers = []
|
|
for InvoiceRet in InvoiceRets:
|
|
print(InvoiceRet, InvoiceRet.text)
|
|
refnumbers.append(InvoiceRet.text)
|
|
|
|
# IsFullyInvoiced = InvoiceRet.find('IsFullyInvoiced').text
|
|
# IsManuallyClosed = InvoiceRet.find('IsManuallyClosed').text
|
|
# if IsFullyInvoiced=='false' and IsManuallyClosed=='false':
|
|
# txndate = InvoiceRet.find('TxnDate').text
|
|
# totalamount = InvoiceRet.find('TotalAmount').text
|
|
# refnumber = InvoiceRet.find('RefNumber').text
|
|
|
|
# _OpenInvoicelist.append([InvoiceRet.find('TxnID').text, txndate, totalamount, refnumber, ])
|
|
# # _OpenInvoicelist.append(InvoiceRet.find('TxnID').text)
|
|
# # RefNumber = InvoiceRet.find('RefNumber').text
|
|
# # Memo = InvoiceRet.find('Memo').text
|
|
# # CustomerFullName = InvoiceRet.find('CustomerRef/FullName').text
|
|
# # TxnID = InvoiceRet.find('TxnID').text
|
|
# # TotalAmount = InvoiceRet.find('TotalAmount').text
|
|
# # IsFullyReceived = InvoiceRet.find('IsFullyReceived').text
|
|
# # IsManuallyClosed = InvoiceRet.find('IsManuallyClosed').text
|
|
print(refnumbers)
|
|
refnumbers.sort()
|
|
return refnumbers[-1]
|
|
|
|
def get_open_so(self, response_string=None):
|
|
if not response_string:
|
|
response_string = self.connect_to_quickbooks(self.create_QBXML())
|
|
|
|
QBXML = ET.fromstring(response_string)
|
|
_OpenInvoicelist = []
|
|
InvoiceRets = QBXML.findall('.//InvoiceRet')
|
|
# print(f'InvoiceRets count:{len(InvoiceRets)}')
|
|
for InvoiceRet in InvoiceRets:
|
|
IsFullyInvoiced = InvoiceRet.find('IsFullyInvoiced').text
|
|
IsManuallyClosed = InvoiceRet.find('IsManuallyClosed').text
|
|
if IsFullyInvoiced=='false' and IsManuallyClosed=='false':
|
|
txndate = InvoiceRet.find('TxnDate').text
|
|
totalamount = InvoiceRet.find('TotalAmount').text
|
|
refnumber = InvoiceRet.find('RefNumber').text
|
|
|
|
_OpenInvoicelist.append([InvoiceRet.find('TxnID').text, txndate, totalamount, refnumber, ])
|
|
# _OpenInvoicelist.append(InvoiceRet.find('TxnID').text)
|
|
# RefNumber = InvoiceRet.find('RefNumber').text
|
|
# Memo = InvoiceRet.find('Memo').text
|
|
# CustomerFullName = InvoiceRet.find('CustomerRef/FullName').text
|
|
# TxnID = InvoiceRet.find('TxnID').text
|
|
# TotalAmount = InvoiceRet.find('TotalAmount').text
|
|
# IsFullyReceived = InvoiceRet.find('IsFullyReceived').text
|
|
# IsManuallyClosed = InvoiceRet.find('IsManuallyClosed').text
|
|
# print(_OpenInvoicelist)
|
|
return _OpenInvoicelist
|
|
|
|
def create_open_sales_order_qbxml(self, txnid:list, IncludeLineItems=True):
|
|
root = ET.Element("QBXML")
|
|
root.tail = "\n"
|
|
root.text = "\n "
|
|
QBXMLMsgsRq = ET.SubElement(root, "QBXMLMsgsRq")
|
|
# QBXMLMsgsRq.set("onError", "continueOnError")
|
|
QBXMLMsgsRq.set("onError", "stopOnError")
|
|
QBXMLMsgsRq.tail = "\n"
|
|
QBXMLMsgsRq.text = "\n "
|
|
InvoiceQueryRq = self.create_sub_element(ET, QBXMLMsgsRq, "InvoiceQueryRq","\n " )
|
|
if len(txnid)>0:
|
|
for x in txnid:
|
|
TxnID = self.create_sub_element(ET, InvoiceQueryRq, "TxnID", x, 6 )
|
|
else:
|
|
return None
|
|
if IncludeLineItems:
|
|
IncludeLineItems = self.create_sub_element(ET, InvoiceQueryRq, "IncludeLineItems", 'true', 4)
|
|
# if len(self.IncludeRetElement)>0:
|
|
# for x in self.IncludeRetElement:
|
|
# IncludeRetElement = self.create_sub_element(ET, InvoiceQueryRq, "IncludeRetElement", x, 4)
|
|
mydata = ET.tostring(root, encoding = "unicode")
|
|
|
|
qbxml_query = """<?xml version="1.0" encoding="utf-8"?>\n"""
|
|
qbxml_query = qbxml_query + """<?qbxml version="13.0"?>"""
|
|
qbxml_query = qbxml_query + "\n" + mydata
|
|
print(f'create_open_sale_order_qbxml->qbxml_query: {qbxml_query}')
|
|
return qbxml_query
|
|
|
|
|
|
def get_open_sales_order(self, txnlist:list):
|
|
print(f'txnid: {txnlist}', type(txnlist))
|
|
txnid = []
|
|
for x in txnlist:
|
|
if isinstance(x, list):
|
|
txnid.append(x[0])
|
|
else:
|
|
txnid.append(x)
|
|
break
|
|
# txnid = [x[0] if isinstance(x, list) else x for x in txnid ]
|
|
print(txnid)
|
|
if txnid:
|
|
# print(self.connect_to_quickbooks(self.create_open_sales_order_qbxml(txnid)))
|
|
return self._get_sales_order_header(self.connect_to_quickbooks(self.create_open_sales_order_qbxml(txnid)))
|
|
else:
|
|
print("There is No Open Invoice Order")
|
|
return None
|
|
return None
|
|
|
|
print('### InvoiceQuery ###')
|
|
if __name__ == '__main__':
|
|
starttime = timeit.default_timer()
|
|
# ini=InvoiceQuery(FullName= '999 HPL', IncludeRetElement = ['TxnID', 'TimeCreated', 'TimeModified','TxnNumber', 'CustomerRef', 'IsManuallyClosed', 'IsFullyInvoiced'])
|
|
|
|
# ini=InvoiceQuery(FullName= 'YSM Interior', IncludeRetElement = ['TxnID', 'TimeCreated', 'TimeModified','TxnNumber', 'CustomerRef', 'TxnDate', 'RefNumber', 'IsManuallyClosed', 'IsFullyInvoiced','TotalAmount'])
|
|
ini=InvoiceQuery(FullName= 'Abadi Serpong', IncludeRetElement = 'RefNumber', ])
|
|
# iya = ini.create_customerquery_QBXML() #pakai excel saja lebih cepat
|
|
# print(iya)
|
|
print(f'createQBXML:{ini.create_QBXML()}')
|
|
response_string = ini.connect_to_quickbooks(ini.create_QBXML())
|
|
print(f'response_string:{response_string}')
|
|
# response_string = None
|
|
last_refnumber = ini.get_last_refnumber(response_string)
|
|
print(f'open sales orders:{last_refnumber};type:{type(last_refnumber)}')
|
|
# print('23a'+1)
|
|
# if open_sales_orders:
|
|
|
|
# itu = ini.get_open_sales_order(open_sales_orders)
|
|
# # print(itu)
|
|
# print(f'get_open_sales_order:{itu}')
|
|
# if itu:
|
|
|
|
# print(ini.create_invoiceadd_QBXML())
|
|
|
|
# # ini.connect_to_quickbooks(ini.create_invoiceadd_QBXML())
|
|
|
|
print("The time difference is :", timeit.default_timer() - starttime) |