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 class PurchaseOrderQuery: def __init__(self, **kwargs) -> None: # print(f'kwargs:{kwargs}') # print(args) # self.PurchaseOrderList=[] self.DN = kwargs['DN'] if 'DN' in kwargs else {} self.Reuse = kwargs['Reuse'] if 'Reuse' in kwargs else None self.PurchaseOrderList= kwargs['PurchaseOrderList'] if 'PurchaseOrderList' in kwargs else [] self.PurchaseOrderType = kwargs['PurchaseOrderType'] if 'PurchaseOrderType' in kwargs else 'SalesByCustomerSummary' self.IncludeLineItems = kwargs['IncludeLineItems'] if 'IncludeLineItems' in kwargs else 'true' self.TxnDateRangeFilter = kwargs['TxnDateRangeFilter'] if 'TxnDateRangeFilter' in kwargs else None self.DateMacro = None 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'] self.FromTxnDate = self.validate_date(kwargs['FromTxnDate']) if 'FromTxnDate' in kwargs else None self.ToTxnDate = self.validate_date(kwargs['ToTxnDate']) if 'ToTxnDate' in kwargs else None self.ReportEntityFilter = kwargs['ReportEntityFilter'] if 'ReportEntityFilter' in kwargs else None self.FullName = kwargs['FullName'] if 'FullName' 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_purchase_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 " PurchaseOrderQueryRq = self.create_sub_element(ET, QBXMLMsgsRq, "PurchaseOrderQueryRq","\n " ) # PurchaseOrderType = self.create_sub_element(ET, PurchaseOrderQueryRq, 'PurchaseOrderType', self.PurchaseOrderType) print(f'refnumber:{self.RefNumber}') if self.RefNumber: for rn in self.RefNumber: RefNumber = self.create_sub_element(ET, PurchaseOrderQueryRq, "RefNumber", f'{rn}', 4) elif self.DateMacro: TxnDateRangeFilter = self.create_sub_element(ET, PurchaseOrderQueryRq, "TxnDateRangeFilter", "\n ",) PurchaseOrderType = self.create_sub_element(ET, TxnDateRangeFilter, "DateMacro", self.DateMacro) # PurchaseOrderType = self.create_sub_element(ET, PurchaseOrderQueryRq, "DateMacro", self.DateMacro) elif type(self.FromTxnDate) is datetime.date or type(self.ToTxnDate) is datetime.date: TxnDateRangeFilter = self.create_sub_element(ET, PurchaseOrderQueryRq, "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, PurchaseOrderQueryRq, "IncludeLineItems", self.IncludeLineItems, 4) mydata = ET.tostring(root, encoding = "unicode") qbxml_query = """\n""" qbxml_query = qbxml_query + """""" qbxml_query = qbxml_query + "\n" + mydata print(f'create_QBXML->qbxml_query: {qbxml_query}') return qbxml_query def create_itemreceiptadd_QBXML(self): 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 " ItemReceiptAddRq = self.create_sub_element(ET, QBXMLMsgsRq, "ItemReceiptAddRq", "\n ",2 ) ItemReceiptAdd = self.create_sub_element(ET, ItemReceiptAddRq, "ItemReceiptAdd", "\n ",4 ) VendorRef = self.create_sub_element(ET, ItemReceiptAdd, "VendorRef", "\n ",8 ) FullName = self.create_sub_element(ET, VendorRef, "FullName", "TACO", 8 ) today = str(date.today()) TxnDate = self.create_sub_element(ET, ItemReceiptAdd, "TxnDate", self.DN['TxnDate'], 8 ) RefNumber = self.create_sub_element(ET, ItemReceiptAdd, "RefNumber", self.DN['DNRefNum'], 8 ) Memo = self.create_sub_element(ET, ItemReceiptAdd, "Memo", self.DN['Memo'], 10 ) DNTotalAmount = 0 for purchaseorder in self.PurchaseOrderList: POTxnId = purchaseorder['TxnID'] print(f'create_itemreceiptadd_QBXML->POTxnID: {POTxnId}') for itemline in purchaseorder['PurchaseOrderLineRet']: if 'DNQuantity' in itemline: DNQty = itemline['DNQuantity'] Rate = itemline['Rate'] DNAmount = DNQty*Rate DNTotalAmount += DNAmount ItemLineAdd = self.create_sub_element(ET, ItemReceiptAdd, "ItemLineAdd", "\n ", 10 ) Quantity = self.create_sub_element(ET, ItemLineAdd, "Quantity", str(DNQty), 12 ) UnitOfMeasure = self.create_sub_element(ET, ItemLineAdd, "UnitOfMeasure", str(itemline['UOM']), 12 ) LinkToTxn = self.create_sub_element(ET, ItemLineAdd, "LinkToTxn", "\n ",10 ) TxnID = self.create_sub_element(ET, LinkToTxn, "TxnID", POTxnId,14 ) TxnLineID = self.create_sub_element(ET, LinkToTxn, "TxnLineID", itemline['TxnLineID'],12 ) PPN11percent = round(0.11*DNTotalAmount ,2) # if self.DN['BRANCH']!=None and self.DN['BRANCH']!='BGR' and DNTotalAmount: if self.DN['BRANCH']!=None and DNTotalAmount: ItemLineAdd = self.create_sub_element(ET, ItemReceiptAdd, "ItemLineAdd", "\n ", 10 ) ItemRef = self.create_sub_element(ET, ItemLineAdd, "ItemRef", "\n ", 12 ) ItemRef = self.create_sub_element(ET, ItemRef, "FullName", "PPN Masukan", 14 ) # Quantity = self.create_sub_element(ET, ItemLineAdd, "Quantity", "1", 12 ) Quantity = self.create_sub_element(ET, ItemLineAdd, "Cost", str(PPN11percent), 12 ) # Quantity = self.create_sub_element(ET, ItemLineAdd, "Quantity", str(DNQty), 12 ) # UnitOfMeasure = self.create_sub_element(ET, ItemLineAdd, "UnitOfMeasure", str(itemline['UOM']), 12 ) # LinkToTxn = self.create_sub_element(ET, ItemLineAdd, "LinkToTxn", "\n ",10 ) # TxnID = self.create_sub_element(ET, LinkToTxn, "TxnID", POTxnId,14 ) # TxnLineID = self.create_sub_element(ET, LinkToTxn, "TxnLineID", itemline['TxnLineID'],12 ) mydata = ET.tostring(root, encoding = "unicode") qbxml_query = """\n""" qbxml_query = qbxml_query + """""" qbxml_query = qbxml_query + "\n" + mydata print(f'create_itemreceiptadd_QBXML->Create_ItemReceiptadd_QBXML: {qbxml_query}') # print(f"replyfrom qbxml:{self.connect_to_quickbooks(qbxml_query)}") print(f'{self.DN["BRANCH"]= } {DNTotalAmount= :_} {PPN11percent= :_} {DNTotalAmount+PPN11percent= :_}') 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 (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_purchase_order_header()) # return "hello" def get_purchase_order_header(self, *args): return self. _get_purchase_order_header(self.connect_to_quickbooks(self.create_QBXML())) def status_ok(self, QBXML): #for ItemReceiptAddRS tree = ET.fromstring(QBXML) GSRQRs = tree.find(".//ItemReceiptAddRs") # 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_purchase_order_header(self, response_string): print('_get_purchase_order_header') # print(f'responsestring:{response_string}') QBXML = ET.fromstring(response_string) datadict = {} PurchaseOrderdict = {} _PurchaseOrderlist = [] PurchaseOrderRets = QBXML.findall('.//PurchaseOrderRet') for PurchaseOrderRet in PurchaseOrderRets: RefNumber = PurchaseOrderRet.find('RefNumber').text Memo = PurchaseOrderRet.find('Memo').text VendorFullName = PurchaseOrderRet.find('VendorRef/FullName').text TxnID = PurchaseOrderRet.find('TxnID').text TotalAmount = PurchaseOrderRet.find('TotalAmount').text IsFullyReceived = PurchaseOrderRet.find('IsFullyReceived').text IsManuallyClosed = PurchaseOrderRet.find('IsManuallyClosed').text # print(VendorFullName, TxnID, TotalAmount) PurchaseOrderdict = {'RefNumber':RefNumber, 'Memo':Memo, 'VendorFullName':VendorFullName, 'TxnID':TxnID, 'TotalAmount':TotalAmount, 'IsFullyReceived':IsFullyReceived, 'IsManuallyClosed':IsManuallyClosed, 'PurchaseOrderLineRet':[]} PurchaseOrderLineRet = PurchaseOrderRet.findall('PurchaseOrderLineRet') # print(len(PurchaseOrderLineRet)) if len(PurchaseOrderLineRet) > 0: for PurchaseOrderLineRet in PurchaseOrderLineRet: pass TxnLineID = PurchaseOrderLineRet.find('TxnLineID').text ItemFullName = PurchaseOrderLineRet.find('ItemRef/FullName').text Quantity = PurchaseOrderLineRet.find('Quantity').text UnitOfMeasure = PurchaseOrderLineRet.find('UnitOfMeasure').text Rate = float(PurchaseOrderLineRet.find('Rate').text) Amount = float(PurchaseOrderLineRet.find('Amount').text) ReceivedQuantity = PurchaseOrderLineRet.find('ReceivedQuantity').text IsBilled = PurchaseOrderLineRet.find('IsBilled').text LineIsManuallyClosed = PurchaseOrderLineRet.find('IsManuallyClosed').text # print(TxnLineID, ItemFullName) BackOrdered = float(Quantity) - float(ReceivedQuantity) if BackOrdered: PurchaseOrderLinedict = {'TxnLineID':TxnLineID, 'ItemFullName':ItemFullName, 'Quantity':Quantity, 'UOM':UnitOfMeasure, 'Rate':Rate, 'Amount':Amount, 'BackOrdered':BackOrdered, 'ReceivedQuantity':ReceivedQuantity, 'IsBilled':IsBilled, 'LineIsManuallyClosed':LineIsManuallyClosed, } PurchaseOrderdict['PurchaseOrderLineRet'].append(PurchaseOrderLinedict) _PurchaseOrderlist.append(PurchaseOrderdict) # print(_PurchaseOrderlist) self.PurchaseOrderList=_PurchaseOrderlist # print(f'_get_purchase_order_header->Purchaseorderlist: {self.PurchaseOrderList}') # return PurchaseOrderlist def addDNQtyToPurchaseOrderList(self, _dict:dict): _bolfoundrefnum=False _bol_dictisadded=False Error_msg = None # print(('adddnqtytopurchaseorderlist')) for poidx, _po in enumerate(self.PurchaseOrderList): if _po['RefNumber']==_dict['RefNum']: _bolfoundrefnum=True if len(_po['PurchaseOrderLineRet'])>0: bolfind_poline_dict = False for polineidx, _poline in enumerate(_po['PurchaseOrderLineRet']): pass if _poline['ItemFullName']==_dict['FullName']: bolfind_poline_dict = True print(f"{_dict['FullName'] = } {_poline['ItemFullName'] = } {_poline['UOM'] = } {_dict['UOM'] = }") if _poline['UOM'].upper()==_dict['UOM'].upper() or _dict['UOM'].upper().startswith('ROLL_'): # first do UOM in _dict convert treatment print(f"{_dict['FullName'] = } {_poline['UOM'] = } {_dict['UOM'] = }") QuantityIn_dict = _dict['Quantity'] if _dict['UOM'].upper().startswith('ROLL_'): print(f"addDNQtyToPurchaseOrderList->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("addDNQtyToPurchaseOrderList->cutter") elif _dict['UOM'].upper() == 'BOX' and (_dict['Item No'].upper().startswith("EB-") or _dict['Item No'].upper().startswith("TA-")): print("addDNQtyToPurchaseOrderList->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("addDNQtyToPurchaseOrderList->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.PurchaseOrderList[poidx]['PurchaseOrderLineRet'][polineidx]['DNQuantity']=float(QuantityIn_dict) _bol_dictisadded = True Error_msg= None break 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: pass # print("errorpoline <>DN") # Error_msg = f"poline[ItemFullName] <> DN[FullName]; {_poline['ItemFullName']} <> {_dict['FullName']}, maybe there are 2 same namefromtaco in QB" if not bolfind_poline_dict: Error_msg = f"Cannot find DN:{_dict['FullName']} in poline: {_po['RefNumber']}" 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 prepareItemReceipt(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 = }') # print("") # print(f'self.PurchaseOrderList: {self.PurchaseOrderList}') for idx, xdf in enumerate(_dflist): _boladdDN, _Errormsg = self.addDNQtyToPurchaseOrderList(xdf) # print(f'prepareItemReceipt->_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"prepareItemReceipt->not added: {xdf['Item No']}") print (f"prepareItemReceipt->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): # print(f'{dndict = }') if dndict: dnlist = dndict['lines'] else: dnlist = self.DN df = pd.DataFrame(dnlist) print(f'df from dnlist: {df}') df['RefNum']= df['No.SO'].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 print('### PurchaseOrder ###') if __name__ == '__main__': starttime = timeit.default_timer() # dndict={'DNRefNum': 'G230700127', 'TxnDate': '2023-07-05', 'Memo': 'G230700127', 'lines': [{'Ext.Doc.No': 'PO0119623070034', 'No.SO': 'TCO-SOL-2307-00666', 'Item No': 'TH-037AA', 'UOM': 'PCS', 'FullName': 'TACO:AA:TH-037AA', 'Quantity': 15}, {'Ext.Doc.No': 'PO0119623070045', 'No.SO': 'TCO-SOL-2307-00849', 'Item No': 'TH-037AA', 'UOM': 'PCS', 'FullName': 'TACO:AA:TH-037AA', 'Quantity': 15}, {'Ext.Doc.No': 'PO0119623070046', 'No.SO': 'TCO-SOL-2307-00850', 'Item No': 'EDG-805-1/42', 'UOM': 'ROLL', 'FullName': 'TEDG:WG142:EDG-805-1/42', 'Quantity': 3}, {'Ext.Doc.No': 'PO0119623070046', 'No.SO': 'TCO-SOL-2307-00850', 'Item No': 'EDG-825-1/22', 'UOM': 'ROLL', 'FullName': 'TEDG:WG122:EDG-825-1/22', 'Quantity': 5}, {'Ext.Doc.No': 'PO0119623070064', 'No.SO': 'TCO-SOL-2307-01224', 'Item No': 'TS-W309', 'UOM': 'ROLL_50', 'FullName': 'TSHT:TS DYNAMIX:TS-W309', 'Quantity': 2}, {'Ext.Doc.No': 'PO0119623070064', 'No.SO': 'TCO-SOL-2307-01224', 'Item No': 'TS-W311', 'UOM': 'ROLL_50', 'FullName': 'TSHT:TS DYNAMIX:TS-W311', 'Quantity': 1}, {'Ext.Doc.No': 'PO0119623070073', 'No.SO': 'TCO-SOL-2307-01464', 'Item No': 'TH-003D', 'UOM': 'PCS', 'FullName': 'TACO:G_D:TH-003D', 'Quantity': 15}, {'Ext.Doc.No': 'PO0119623070073', 'No.SO': 'TCO-SOL-2307-01464', 'Item No': 'TH-009D', 'UOM': 'PCS', 'FullName': 'TACO:G_D:TH-009D', 'Quantity': 15}, {'Ext.Doc.No': 'PO0119623070073', 'No.SO': 'TCO-SOL-2307-01464', 'Item No': 'TH-011AA', 'UOM': 'PCS', 'FullName': 'TACO:AA:TH-011AA', 'Quantity': 15}, {'Ext.Doc.No': 'PO0119623070073', 'No.SO': 'TCO-SOL-2307-01464', 'Item No': 'TH-014AA', 'UOM': 'PCS', 'FullName': 'TACO:AA:TH-014AA', 'Quantity': 15}, {'Ext.Doc.No': 'PO0119623070073', 'No.SO': 'TCO-SOL-2307-01464', 'Item No': 'TH-022AA', 'UOM': 'PCS', 'FullName': 'TACO:AA:TH-022AA', 'Quantity': 15}, {'Ext.Doc.No': 'PO0119623070073', 'No.SO': 'TCO-SOL-2307-01464', 'Item No': 'TH-026D', 'UOM': 'PCS', 'FullName': 'TACO:G_D:TH-026D', 'Quantity': 60}, {'Ext.Doc.No': 'PO0119623070073', 'No.SO': 'TCO-SOL-2307-01464', 'Item No': 'TH-036AA', 'UOM': 'PCS', 'FullName': 'TACO:AA:TH-036AA', 'Quantity': 15}, {'Ext.Doc.No': 'PO0119623070073', 'No.SO': 'TCO-SOL-2307-01464', 'Item No': 'TH-1205FC', 'UOM': 'PCS', 'FullName': 'TACO:J_FC:TH-1205FC', 'Quantity': 15}, {'Ext.Doc.No': 'PO0119623070073', 'No.SO': 'TCO-SOL-2307-01464', 'Item No': 'TH-1224FC', 'UOM': 'PCS', 'FullName': 'TACO:J_FC:TH-1224FC', 'Quantity': 15}, {'Ext.Doc.No': 'PO0119623070073', 'No.SO': 'TCO-SOL-2307-01464', 'Item No': 'TH-131AA', 'UOM': 'PCS', 'FullName': 'TACO:AA_B_H:TH-131AA', 'Quantity': 50}, {'Ext.Doc.No': 'PO0119623070073', 'No.SO': 'TCO-SOL-2307-01464', 'Item No': 'TH-186AA', 'UOM': 'PCS', 'FullName': 'TACO:AA_B_H:TH-186AA', 'Quantity': 15}, {'Ext.Doc.No': 'PO0119623070073', 'No.SO': 'TCO-SOL-2307-01464', 'Item No': 'TH-200AA', 'UOM': 'PCS', 'FullName': 'TACO:AA_B_H:TH-200AA', 'Quantity': 15}, {'Ext.Doc.No': 'PO0119623070073', 'No.SO': 'TCO-SOL-2307-01464', 'Item No': 'TH-202B', 'UOM': 'PCS', 'FullName': 'TACO:AA_B_H:TH-202B', 'Quantity': 15}, {'Ext.Doc.No': 'PO0119623070073', 'No.SO': 'TCO-SOL-2307-01464', 'Item No': 'TH-371H', 'UOM': 'PCS', 'FullName': 'TACO:AA_B_H:TH-371H', 'Quantity': 15}, {'Ext.Doc.No': 'PO0119623070073', 'No.SO': 'TCO-SOL-2307-01464', 'Item No': 'TH-852J', 'UOM': 'PCS', 'FullName': 'TACO:J_FC:TH-852J', 'Quantity': 30}, {'Ext.Doc.No': 'PO0119623070073', 'No.SO': 'TCO-SOL-2307-01464', 'Item No': 'TH-906J', 'UOM': 'PCS', 'FullName': 'TACO:J_FC:TH-906J', 'Quantity': 15}, {'Ext.Doc.No': 'PO0119623070088', 'No.SO': 'TCO-SOL-2307-01764', 'Item No': 'TV-3024', 'UOM': 'BOX', 'FullName': 'TACH:FLR:TV-3024', 'Quantity': 108}, {'Ext.Doc.No': 'PO0119623070089', 'No.SO': 'TCO-SOL-2307-01792', 'Item No': 'TH-001AA', 'UOM': 'PCS', 'FullName': 'TACO:W:TH-001AA', 'Quantity': 15}, {'Ext.Doc.No': 'PO0119623070089', 'No.SO': 'TCO-SOL-2307-01792', 'Item No': 'TH-002G', 'UOM': 'PCS', 'FullName': 'TACO:G_D:TH-002G', 'Quantity': 15}, {'Ext.Doc.No': 'PO0119623070089', 'No.SO': 'TCO-SOL-2307-01792', 'Item No': 'TH-007G', 'UOM': 'PCS', 'FullName': 'TACO:G_D:TH-007G', 'Quantity': 15}, {'Ext.Doc.No': 'PO0119623070089', 'No.SO': 'TCO-SOL-2307-01792', 'Item No': 'TH-013AA', 'UOM': 'PCS', 'FullName': 'TACO:AA:TH-013AA', 'Quantity': 15}, {'Ext.Doc.No': 'PO0119623070089', 'No.SO': 'TCO-SOL-2307-01792', 'Item No': 'TH-014G', 'UOM': 'PCS', 'FullName': 'TACO:G_D:TH-014G', 'Quantity': 15}, {'Ext.Doc.No': 'PO0119623070089', 'No.SO': 'TCO-SOL-2307-01792', 'Item No': 'TH-022AA', 'UOM': 'PCS', 'FullName': 'TACO:AA:TH-022AA', 'Quantity': 15}, {'Ext.Doc.No': 'PO0119623070089', 'No.SO': 'TCO-SOL-2307-01792', 'Item No': 'TH-037AA', 'UOM': 'PCS', 'FullName': 'TACO:AA:TH-037AA', 'Quantity': 15}, {'Ext.Doc.No': 'PO0119623070089', 'No.SO': 'TCO-SOL-2307-01792', 'Item No': 'TH-101AA', 'UOM': 'PCS', 'FullName': 'TACO:AA_B_H:TH-101AA', 'Quantity': 15}, {'Ext.Doc.No': 'PO0119623070089', 'No.SO': 'TCO-SOL-2307-01792', 'Item No': 'TH-102AA', 'UOM': 'PCS', 'FullName': 'TACO:AA_B_H:TH-102AA', 'Quantity': 15}, {'Ext.Doc.No': 'PO0119623070089', 'No.SO': 'TCO-SOL-2307-01792', 'Item No': 'TH-109AA', 'UOM': 'PCS', 'FullName': 'TACO:AA_B_H:TH-109AA', 'Quantity': 15}, {'Ext.Doc.No': 'PO0119623070089', 'No.SO': 'TCO-SOL-2307-01792', 'Item No': 'TH-1218FC', 'UOM': 'PCS', 'FullName': 'TACO:J_FC:TH-1218FC', 'Quantity': 15}, {'Ext.Doc.No': 'PO0119623070089', 'No.SO': 'TCO-SOL-2307-01792', 'Item No': 'TH-200AA', 'UOM': 'PCS', 'FullName': 'TACO:AA_B_H:TH-200AA', 'Quantity': 15}, {'Ext.Doc.No': 'PO0119623070089', 'No.SO': 'TCO-SOL-2307-01792', 'Item No': 'TH-215AA', 'UOM': 'PCS', 'FullName': 'TACO:AA_B_H:TH-215AA', 'Quantity': 45}, {'Ext.Doc.No': 'PO0119623070089', 'No.SO': 'TCO-SOL-2307-01792', 'Item No': 'TH-262B', 'UOM': 'PCS', 'FullName': 'TACO:AA_B_H:TH-262B', 'Quantity': 30}, {'Ext.Doc.No': 'PO0119623070089', 'No.SO': 'TCO-SOL-2307-01792', 'Item No': 'TH-321H', 'UOM': 'PCS', 'FullName': 'TACO:AA_B_H:TH-321H', 'Quantity': 15}, {'Ext.Doc.No': 'PO0119623070089', 'No.SO': 'TCO-SOL-2307-01792', 'Item No': 'TH-352H', 'UOM': 'PCS', 'FullName': 'TACO:AA_B_H:TH-352H', 'Quantity': 15}, {'Ext.Doc.No': 'PO0119623070089', 'No.SO': 'TCO-SOL-2307-01792', 'Item No': 'TH-372H', 'UOM': 'PCS', 'FullName': 'TACO:AA_B_H:TH-372H', 'Quantity': 15}, {'Ext.Doc.No': 'PO0119623070089', 'No.SO': 'TCO-SOL-2307-01792', 'Item No': 'TH-375H', 'UOM': 'PCS', 'FullName': 'TACO:AA_B_H:TH-375H', 'Quantity': 30}, {'Ext.Doc.No': 'PO0119623070089', 'No.SO': 'TCO-SOL-2307-01792', 'Item No': 'TH-839J', 'UOM': 'PCS', 'FullName': 'TACO:J_FC:TH-839J', 'Quantity': 15}, {'Ext.Doc.No': 'PO0119623070089', 'No.SO': 'TCO-SOL-2307-01792', 'Item No': 'TH-849J', 'UOM': 'PCS', 'FullName': 'TACO:J_FC:TH-849J', 'Quantity': 15}, {'Ext.Doc.No': 'PO0119623070090', 'No.SO': 'TCO-SOL-2307-01804', 'Item No': 'EDG-003-1/22', 'UOM': 'ROLL', 'FullName': 'TEDG:S122:EDG-003-1/22', 'Quantity': 5}, {'Ext.Doc.No': 'PO0119623070090', 'No.SO': 'TCO-SOL-2307-01804', 'Item No': 'EDG-363-1/42', 'UOM': 'ROLL', 'FullName': 'TEDG:WG142:EDG-363-1/42', 'Quantity': 3}, {'Ext.Doc.No': 'PO0119623070090', 'No.SO': 'TCO-SOL-2307-01804', 'Item No': 'EDG-806-1/22', 'UOM': 'ROLL', 'FullName': 'TEDG:WG122:EDG-806-1/22', 'Quantity': 5}]} dndict = {'DNRefNum': 'G230700752', 'TxnDate': '2023-07-15', 'Memo': 'G230700752', 'lines': [{'Ext.Doc.No': 'PO0119623070242', 'No.SO': 'TCO-SOL-2307-05507', 'Item No': 'TH-040G', 'UOM': 'PCS', 'FullName': 'TACO:G_D:TH-040G', 'Quantity': 75}, {'Ext.Doc.No': 'PO0119623070243', 'No.SO': 'TCO-SOL-2307-05510', 'Item No': 'EDG-305-1/22', 'UOM': 'ROLL', 'FullName': 'TEDG:WG122:EDG-305-1/22', 'Quantity': 1}, {'Ext.Doc.No': 'PO0119623070243', 'No.SO': 'TCO-SOL-2307-05510', 'Item No': 'EDG-373-1/42', 'UOM': 'ROLL', 'FullName': 'TEDG:WG142:EDG-373-1/42', 'Quantity': 3}, {'Ext.Doc.No': 'PO0119623070259', 'No.SO': 'TCO-SOL-2307-05882', 'Item No': 'TH-007G', 'UOM': 'PCS', 'FullName': 'TACO:G_D:TH-007G', 'Quantity': 15}, {'Ext.Doc.No': 'PO0119623070261', 'No.SO': 'TCO-SOL-2307-05899', 'Item No': 'TS-I500', 'UOM': 'ROLL_100', 'FullName': 'TSHT:TS WOODGRAIN:TS-I500', 'Quantity': 1}, {'Ext.Doc.No': 'PO0119623070261', 'No.SO': 'TCO-SOL-2307-05899', 'Item No': 'TS-P002', 'UOM': 'ROLL_50', 'FullName': 'TSHT:TS SOLID:TS-P002', 'Quantity': 1}, {'Ext.Doc.No': 'PO0119623070261', 'No.SO': 'TCO-SOL-2307-05899', 'Item No': 'TS-W318', 'UOM': 'ROLL_50', 'FullName': 'TSHT:TS DYNAMIX:TS-W318', 'Quantity': 1}, {'Ext.Doc.No': 'PO0119623070268', 'No.SO': 'TCO-SOL-2307-06052', 'Item No': 'TH-001AA', 'UOM': 'PCS', 'FullName': 'TACO:W:TH-001AA', 'Quantity': 15}, {'Ext.Doc.No': 'PO0119623070268', 'No.SO': 'TCO-SOL-2307-06052', 'Item No': 'TH-016AA', 'UOM': 'PCS', 'FullName': 'TACO:W:TH-016AA', 'Quantity': 30}, {'Ext.Doc.No': 'PO0119623070268', 'No.SO': 'TCO-SOL-2307-06052', 'Item No': 'TH-119AA', 'UOM': 'PCS', 'FullName': 'TACO:AA_B_H:TH-119AA', 'Quantity': 15}, {'Ext.Doc.No': 'PO0119623070268', 'No.SO': 'TCO-SOL-2307-06052', 'Item No': 'TH-1218FC', 'UOM': 'PCS', 'FullName': 'TACO:J_FC:TH-1218FC', 'Quantity': 30}, {'Ext.Doc.No': 'PO0119623070268', 'No.SO': 'TCO-SOL-2307-06052', 'Item No': 'TH-1236FA', 'UOM': 'PCS', 'FullName': 'TACO:OT:TH-1236FA', 'Quantity': 30}, {'Ext.Doc.No': 'PO0119623070268', 'No.SO': 'TCO-SOL-2307-06052', 'Item No': 'TH-276B', 'UOM': 'PCS', 'FullName': 'TACO:AA_B_H:TH-276B', 'Quantity': 30}, {'Ext.Doc.No': 'PO0119623070268', 'No.SO': 'TCO-SOL-2307-06052', 'Item No': 'TH-321H', 'UOM': 'PCS', 'FullName': 'TACO:AA_B_H:TH-321H', 'Quantity': 15}, {'Ext.Doc.No': 'PO0119623070268', 'No.SO': 'TCO-SOL-2307-06052', 'Item No': 'TH-357H', 'UOM': 'PCS', 'FullName': 'TACO:AA_B_H:TH-357H', 'Quantity': 45}, {'Ext.Doc.No': 'PO0119623070268', 'No.SO': 'TCO-SOL-2307-06052', 'Item No': 'TH-5013NT', 'UOM': 'PCS', 'FullName': 'TACO:J_FC:TH-5013NT', 'Quantity': 30}, {'Ext.Doc.No': 'PO0119623070268', 'No.SO': 'TCO-SOL-2307-06052', 'Item No': 'TH-845J', 'UOM': 'PCS', 'FullName': 'TACO:J_FC:TH-845J', 'Quantity': 15}, {'Ext.Doc.No': 'PO0119623070269', 'No.SO': 'TCO-SOL-2307-06055', 'Item No': 'EDG-003G-1/22', 'UOM': 'ROLL', 'FullName': 'TEDG:WG122:EDG-003G-1/22', 'Quantity': 5}, {'Ext.Doc.No': 'PO0119623070276', 'No.SO': 'TCO-SOL-2307-06190', 'Item No': 'TH-001AA', 'UOM': 'PCS', 'FullName': 'TACO:W:TH-001AA', 'Quantity': 15}, {'Ext.Doc.No': 'PO0119623070276', 'No.SO': 'TCO-SOL-2307-06190', 'Item No': 'TH-003AA', 'UOM': 'PCS', 'FullName': 'TACO:AA:TH-003AA', 'Quantity': 15}, {'Ext.Doc.No': 'PO0119623070276', 'No.SO': 'TCO-SOL-2307-06190', 'Item No': 'TH-852J', 'UOM': 'PCS', 'FullName': 'TACO:J_FC:TH-852J', 'Quantity': 15}, {'Ext.Doc.No': 'PO0119623070276', 'No.SO': 'TCO-SOL-2307-06190', 'Item No': 'TH-884J', 'UOM': 'PCS', 'FullName': 'TACO:J_FC:TH-884J', 'Quantity': 30}, {'Ext.Doc.No': 'PO0119623070277', 'No.SO': 'TCO-SOL-2307-06191', 'Item No': 'EDG-1205-1/22', 'UOM': 'ROLL', 'FullName': 'TEDG:WG122:EDG-1205-1/22', 'Quantity': 5}, {'Ext.Doc.No': 'PO0119623070277', 'No.SO': 'TCO-SOL-2307-06191', 'Item No': 'EDG-275-1/22', 'UOM': 'ROLL', 'FullName': 'TEDG:WG122:EDG-275-1/22', 'Quantity': 5}, {'Ext.Doc.No': 'PO0119623070277', 'No.SO': 'TCO-SOL-2307-06191', 'Item No': 'EDG-371-1/22', 'UOM': 'ROLL', 'FullName': 'TEDG:WG122:EDG-371-1/22', 'Quantity': 5}, {'Ext.Doc.No': 'PO0119623070277', 'No.SO': 'TCO-SOL-2307-06191', 'Item No': 'EDG-909-1/22', 'UOM': 'ROLL', 'FullName': 'TEDG:WG122:EDG-909-1/22', 'Quantity': 2}, {'Ext.Doc.No': 'PO0119623070284', 'No.SO': 'TCO-SOL-2307-06233', 'Item No': 'TA-1100-KB', 'UOM': 'PCS', 'FullName': 'TACH:HWR:TA-1100-KB', 'Quantity': 30}, {'Ext.Doc.No': 'PO0119623070285', 'No.SO': 'TCO-SOL-2307-06243', 'Item No': 'TH-009AA', 'UOM': 'PCS', 'FullName': 'TACO:AA:TH-009AA', 'Quantity': 30}, {'Ext.Doc.No': 'PO0119623070285', 'No.SO': 'TCO-SOL-2307-06243', 'Item No': 'TH-016AA', 'UOM': 'PCS', 'FullName': 'TACO:W:TH-016AA', 'Quantity': 45}, {'Ext.Doc.No': 'PO0119623070285', 'No.SO': 'TCO-SOL-2307-06243', 'Item No': 'TH-022AA', 'UOM': 'PCS', 'FullName': 'TACO:AA:TH-022AA', 'Quantity': 15}, {'Ext.Doc.No': 'PO0119623070285', 'No.SO': 'TCO-SOL-2307-06243', 'Item No': 'TH-025AA', 'UOM': 'PCS', 'FullName': 'TACO:AA:TH-025AA', 'Quantity': 15}, {'Ext.Doc.No': 'PO0119623070285', 'No.SO': 'TCO-SOL-2307-06243', 'Item No': 'TH-102AA', 'UOM': 'PCS', 'FullName': 'TACO:AA_B_H:TH-102AA', 'Quantity': 15}, {'Ext.Doc.No': 'PO0119623070285', 'No.SO': 'TCO-SOL-2307-06243', 'Item No': 'TH-109AA', 'UOM': 'PCS', 'FullName': 'TACO:AA_B_H:TH-109AA', 'Quantity': 15}, {'Ext.Doc.No': 'PO0119623070285', 'No.SO': 'TCO-SOL-2307-06243', 'Item No': 'TH-1207FC', 'UOM': 'PCS', 'FullName': 'TACO:J_FC:TH-1207FC', 'Quantity': 30}, {'Ext.Doc.No': 'PO0119623070285', 'No.SO': 'TCO-SOL-2307-06243', 'Item No': 'TH-220AA', 'UOM': 'PCS', 'FullName': 'TACO:AA_B_H:TH-220AA', 'Quantity': 15}, {'Ext.Doc.No': 'PO0119623070285', 'No.SO': 'TCO-SOL-2307-06243', 'Item No': 'TH-325H', 'UOM': 'PCS', 'FullName': 'TACO:AA_B_H:TH-325H', 'Quantity': 30}, {'Ext.Doc.No': 'PO0119623070285', 'No.SO': 'TCO-SOL-2307-06243', 'Item No': 'TH-357H', 'UOM': 'PCS', 'FullName': 'TACO:AA_B_H:TH-357H', 'Quantity': 15}, {'Ext.Doc.No': 'PO0119623070285', 'No.SO': 'TCO-SOL-2307-06243', 'Item No': 'TH-362H', 'UOM': 'PCS', 'FullName': 'TACO:AA_B_H:TH-362H', 'Quantity': 15}, {'Ext.Doc.No': 'PO0119623070285', 'No.SO': 'TCO-SOL-2307-06243', 'Item No': 'TH-371H', 'UOM': 'PCS', 'FullName': 'TACO:AA_B_H:TH-371H', 'Quantity': 15}, {'Ext.Doc.No': 'PO0119623070285', 'No.SO': 'TCO-SOL-2307-06243', 'Item No': 'TH-817J', 'UOM': 'PCS', 'FullName': 'TACO:J_FC:TH-817J', 'Quantity': 15}, {'Ext.Doc.No': 'PO0119623070285', 'No.SO': 'TCO-SOL-2307-06243', 'Item No': 'TH-825J', 'UOM': 'PCS', 'FullName': 'TACO:J_FC:TH-825J', 'Quantity': 30}, {'Ext.Doc.No': 'PO0119623070285', 'No.SO': 'TCO-SOL-2307-06243', 'Item No': 'TH-861J', 'UOM': 'PCS', 'FullName': 'TACO:J_FC:TH-861J', 'Quantity': 30}, {'Ext.Doc.No': 'PO0119623070285', 'No.SO': 'TCO-SOL-2307-06243', 'Item No': 'TH-889FC', 'UOM': 'PCS', 'FullName': 'TACO:J_FC:TH-889FC', 'Quantity': 15}, {'Ext.Doc.No': 'PO0119623070286', 'No.SO': 'TCO-SOL-2307-06244', 'Item No': 'EDG-002-1/42', 'UOM': 'ROLL', 'FullName': 'TEDG:S142:EDG-002-1/42', 'Quantity': 6}, {'Ext.Doc.No': 'PO0119623070288', 'No.SO': 'TCO-SOL-2307-06256', 'Item No': 'EDG-002-1/22', 'UOM': 'ROLL', 'FullName': 'TEDG:S122:EDG-002-1/22', 'Quantity': 10}, {'Ext.Doc.No': 'PO0119623070288', 'No.SO': 'TCO-SOL-2307-06256', 'Item No': 'EDG-002G-1/22', 'UOM': 'ROLL', 'FullName': 'TEDG:WG122:EDG-002G-1/22', 'Quantity': 5}, {'Ext.Doc.No': 'PO0119623070288', 'No.SO': 'TCO-SOL-2307-06256', 'Item No': 'EDG-153-1/42', 'UOM': 'ROLL', 'FullName': 'TEDG:WG142:EDG-153-1/42', 'Quantity': 3}, {'Ext.Doc.No': 'PO0119623070291', 'No.SO': 'TCO-SOL-2307-06282', 'Item No': 'TV-3001', 'UOM': 'BOX', 'FullName': 'TACH:FLR:TV-3001', 'Quantity': 41}, {'Ext.Doc.No': 'PO0119623070295', 'No.SO': 'TCO-SOL-2307-06398', 'Item No': 'TH-122AA', 'UOM': 'PCS', 'FullName': 'TACO:AA_B_H:TH-122AA', 'Quantity': 2}, {'Ext.Doc.No': 'PO0119623070298', 'No.SO': 'TCO-SOL-2307-06520', 'Item No': 'EDG-003-1/42', 'UOM': 'ROLL', 'FullName': 'TEDG:S142:EDG-003-1/42', 'Quantity': 3}, {'Ext.Doc.No': 'PO0119623070301', 'No.SO': 'TCO-SOL-2307-06533', 'Item No': 'BBS007FE36-450', 'UOM': 'BOX', 'FullName': 'TACH:RLC:BBS007FE36-450', 'Quantity': 1}, {'Ext.Doc.No': 'PO0119623070301', 'No.SO': 'TCO-SOL-2307-06533', 'Item No': 'ET-01/A', 'UOM': 'BOX', 'FullName': 'TACH:HWR:ET-01/A', 'Quantity': 3}, {'Ext.Doc.No': 'PO0119623070301', 'No.SO': 'TCO-SOL-2307-06533', 'Item No': 'ET-01/B', 'UOM': 'BOX', 'FullName': 'TACH:HWR:ET-01/B', 'Quantity': 2}, {'Ext.Doc.No': 'PO0119623070301', 'No.SO': 'TCO-SOL-2307-06533', 'Item No': 'ET-01/C', 'UOM': 'BOX', 'FullName': 'TACH:HWR:ET-01/C', 'Quantity': 2}, {'Ext.Doc.No': 'POA0119623070006', 'No.SO': 'TCO-SOL-2307-06527', 'Item No': 'EDG-153/22', 'UOM': 'ROLL', 'FullName': 'TEDG:WG22:EDG-153/22', 'Quantity': 2}, {'Ext.Doc.No': 'SRTACO23070175', 'No.SO': 'TCO-SOL-2307-06091', 'Item No': 'TH-882RE', 'UOM': 'PCS', 'FullName': 'TACO:OT:TH-882RE', 'Quantity': 49}]} dnlist = dndict['lines'] df = pd.DataFrame(dnlist) # print(df) df['RefNum']= df['No.SO'].apply(lambda x: "L"+ x.split("L-")[1]) # print(df) ext_doc_no=df['RefNum'].unique().tolist() print(f'isi:{ext_doc_no}') # ini=PurchaseOrderQuery(DateMacro='ThisWeek', IncludeLineItems='true', RefNumber=["L2307-04429", "L2307-02992", 'L2307-03649',"L2307-02108", "L2307-03638"]) # ini=PurchaseOrderQuery(DateMacro='ThisWeek', IncludeLineItems='true', RefNumber=["L2307-03638", "L2307-05899"]) ini=PurchaseOrderQuery(DateMacro='ThisWeek', IncludeLineItems='true', RefNumber=ext_doc_no, DN=dndict) # # ini=PurchaseOrderQuery(DateMacro='LastWeek', IncludeLineItems='true') print(ini.PurchaseOrderList) bol_prepareItemReceiptOk, notinthelist = ini.prepareItemReceipt(df=df) print(f'added DNQty:{ini.PurchaseOrderList}') ret = ini.create_itemreceiptadd_QBXML() if bol_prepareItemReceiptOk: # print(f"replyfrom qbxml:{ini.connect_to_quickbooks(ret)}") print(ret) status, status_msg = ini.status_ok(ini.connect_to_quickbooks(ret)) if status: print("OK", status_msg) else: print("ERROR", status_msg) else: print("not all DN are added") print("The time difference is :", timeit.default_timer() - starttime)