mirror of
https://github.com/bcomsugi/dasaproject.git
synced 2026-01-08 18:42:37 +07:00
226 lines
8.4 KiB
Python
226 lines
8.4 KiB
Python
#!usr/bin/python
|
|
import win32com.client
|
|
#from win32com.client import constants
|
|
import xml.etree.ElementTree as ET
|
|
|
|
def QBStock(client_socket=None, filename='', searchitems = None, debug:bool=False):
|
|
if searchitems is None:
|
|
searchitems = {}
|
|
|
|
# Connect to Quickbooks
|
|
sessionManager = win32com.client.Dispatch("QBXMLRP2.RequestProcessor")
|
|
sessionManager.OpenConnection('', 'DASA')
|
|
#ticket = sessionManager.BeginSession("C:\Quickbooks Bogor\qb test\distrindo bakti wutama.qbw", 0)
|
|
#ticket = sessionManager.BeginSession("", 0)
|
|
#print("constant:",constants.qbFileOpenDoNotCare)
|
|
#print("constant:",constants.qmDontCare)
|
|
ticket = sessionManager.BeginSession("", 2 )
|
|
|
|
# Send query and receive response
|
|
qbxml_query = """
|
|
<?qbxml version="13.0"?>
|
|
<QBXML>
|
|
<QBXMLMsgsRq onError="stopOnError">
|
|
<ItemInventoryQueryRq metaData="MetaDataAndResponseData">
|
|
<NameFilter>
|
|
<MatchCriterion>Contains</MatchCriterion>
|
|
<Name>TS-I502</Name>
|
|
</NameFilter>
|
|
<IncludeRetElement>QuantityOnHand</IncludeRetElement>
|
|
</ItemInventoryQueryRq>
|
|
</QBXMLMsgsRq>
|
|
</QBXML>
|
|
"""
|
|
qbxml_query = """
|
|
<?qbxml version="13.0"?>
|
|
<QBXML>
|
|
<QBXMLMsgsRq onError="stopOnError">
|
|
<ItemInventoryQueryRq>
|
|
<FullName>TSHT:TS WOODGRAIN:TS-I502</FullName>
|
|
<FullName>TSHT:TS WOODGRAIN:TS-I500</FullName>
|
|
<IncludeRetElement>QuantityOnHand</IncludeRetElement>
|
|
<IncludeRetElement>QuantityOnOrder</IncludeRetElement>
|
|
<IncludeRetElement>QuantityOnSalesOrder</IncludeRetElement>
|
|
<IncludeRetElement>FullName</IncludeRetElement>
|
|
<IncludeRetElement>Name</IncludeRetElement>
|
|
</ItemInventoryQueryRq>
|
|
</QBXMLMsgsRq>
|
|
</QBXML>
|
|
"""
|
|
qbxml_query = """
|
|
<?qbxml version="13.0"?>
|
|
<QBXML>
|
|
<QBXMLMsgsRq onError="continueOnError">
|
|
<GeneralSummaryReportQueryRq>
|
|
<GeneralSummaryReportType>InventoryStockStatusByItem</GeneralSummaryReportType>
|
|
</GeneralSummaryReportQueryRq>
|
|
</QBXMLMsgsRq>
|
|
</QBXML>
|
|
"""
|
|
|
|
qbxml_query1 = """
|
|
<?qbxml version="13.0"?>
|
|
<QBXML>
|
|
<QBXMLMsgsRq onError="stopOnError">
|
|
<ItemInventoryQueryRq metaData="MetaDataAndResponseData">
|
|
<IncludeRetElement>QuantityOnHand</IncludeRetElement>
|
|
<IncludeRetElement>QuantityOnOrder</IncludeRetElement>
|
|
<IncludeRetElement>QuantityOnSalesOrder</IncludeRetElement>
|
|
<IncludeRetElement>FullName</IncludeRetElement>
|
|
<IncludeRetElement>Name</IncludeRetElement>
|
|
</ItemInventoryQueryRq>
|
|
</QBXMLMsgsRq>
|
|
</QBXML>
|
|
"""
|
|
|
|
def createSubElement(ET, parentNode, thisNode, text="", whiteSpace = 0, attrib ={}, debug:bool=False):
|
|
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
|
|
|
|
|
|
root = ET.Element("QBXML")
|
|
root.tail = "\n"
|
|
root.text = "\n "
|
|
QBXMLMsgsRq = ET.SubElement(root, "QBXMLMsgsRq")
|
|
QBXMLMsgsRq.set("onError", "continueOnError")
|
|
#QBXMLMsgsRq.tail = "\n"
|
|
#QBXMLMsgsRq.text = "\n "
|
|
GeneralSummaryReportQueryRq = createSubElement(ET, QBXMLMsgsRq, "GeneralSummaryReportQueryRq","\n " )
|
|
GeneralSummaryReportType = createSubElement(ET, GeneralSummaryReportQueryRq, "GeneralSummaryReportType", "InventoryStockStatusByItem")
|
|
ReportItemFilter = createSubElement(ET, GeneralSummaryReportQueryRq, "ReportItemFilter","\n ")
|
|
#FullName = createSubElement(ET, ReportItemFilter, "FullName", "TSHT:TS WOODGRAIN:TS-Z188")
|
|
#FullName = createSubElement(ET, ReportItemFilter, "FullName", "TACO:G_D:TH-002G")
|
|
#FullName = createSubElement(ET, ReportItemFilter, "FullName", "TACO:AA_B_H:TH-107AA")
|
|
#ReportTxnTypeFilter = createSubElement(ET, GeneralSummaryReportQueryRq, "ReportTxnTypeFilter","\n ")
|
|
#TxnTypeFilter = createSubElement(ET, ReportTxnTypeFilter, "TxnTypeFilter","All")
|
|
|
|
# if you have searchitems then loop here
|
|
print(f"awal searchitems itemreportquery -> {searchitems}")
|
|
if searchitems:
|
|
print(f"searchitems itemreportquery -> {searchitems}")
|
|
for searchitem in searchitems:
|
|
FullName = createSubElement(ET, ReportItemFilter, "FullName", searchitem,6)
|
|
|
|
# # if you have searchitems then loop here
|
|
# if searchitems:
|
|
# NameFilter = createSubElement(ET, ItemInventoryQueryRq, "NameFilter", "\n ",6)
|
|
# MatchCriterion = createSubElement(ET, NameFilter, "MatchCriterion", "Contains", 8)
|
|
# for searchitem in searchitems:
|
|
# Name = createSubElement(ET, NameFilter, "Name", searchitem, 8)
|
|
|
|
#ItemInventoryQueryRq = ET.SubElement(QBXMLMsgsRq, "ItemInventoryQueryRq")
|
|
#ItemInventoryQueryRq.set("metaData", "MetaDataAndResponseData")
|
|
#ItemInventoryQueryRq.set("metaData", "NoMetaData")
|
|
#ItemInventoryQueryRq.tail = "\n "
|
|
#ItemInventoryQueryRq.text = "\n "
|
|
|
|
# IncludeRetElement = createSubElement(ET, ItemInventoryQueryRq, "IncludeRetElement", "QuantityOnHand",6)
|
|
# IncludeRetElement = createSubElement(ET, ItemInventoryQueryRq, "IncludeRetElement", "FullName",6)
|
|
# IncludeRetElement = createSubElement(ET, ItemInventoryQueryRq, "IncludeRetElement", "Name",6)
|
|
# IncludeRetElement = createSubElement(ET, ItemInventoryQueryRq, "IncludeRetElement", "Count",6)
|
|
|
|
#IncludeRetElement = createSubElement(ET, ItemInventoryQueryRq, "IncludeRetElement", "QuantityOnOrder",6)
|
|
|
|
#IncludeRetElement = createSubElement(ET, ItemInventoryQueryRq, "IncludeRetElement", "QuantityOnSalesOrder",4)
|
|
|
|
|
|
#mydata = ET.tostring(root, encoding = "unicode", method = "xml")
|
|
mydata = ET.tostring(root, encoding = "unicode")
|
|
#mydata = ET.tostring(root).decode()
|
|
#print (mydata,type(mydata))
|
|
#qbxml_query1 = """<?xml version="1.0" encoding="utf-8"?>\n"""
|
|
qbxml_query1 = """<?xml version="1.0" encoding="utf-8"?>\n"""
|
|
qbxml_query1 = qbxml_query1 + """<?qbxml version="13.0"?>"""
|
|
qbxml_query1 = qbxml_query1 + "\n" + mydata
|
|
#print (qbxml_query, type(qbxml_query))
|
|
print("")
|
|
|
|
print(qbxml_query1, type(qbxml_query1))
|
|
|
|
#print("")
|
|
|
|
response_string = sessionManager.ProcessRequest(ticket, qbxml_query1)
|
|
# print (f"itemreportquery.py response -> {response_string}")
|
|
# Disconnect from Quickbooks
|
|
sessionManager.EndSession(ticket) # Close the company file
|
|
sessionManager.CloseConnection() # Close the connection
|
|
if debug:print (f"itemreportquery.py response -> {response_string}")
|
|
|
|
# Parse the response into an Element Tree and peel away the layers of response
|
|
#QBXML = xml.etree.ElementTree.fromstring(response_string)
|
|
itemInvDict={}
|
|
QBXML = ET.fromstring(response_string)
|
|
print("")
|
|
#print(QBXML)
|
|
QBXMLMsgsRs = QBXML.find('QBXMLMsgsRs')
|
|
#print(QBXMLMsgsRs.tag, QBXMLMsgsRs)
|
|
#InventoryAdjustmentQueryRs = QBXMLMsgsRs.getiterator("InventoryAdjustmentRet")
|
|
#ItemInventoryQueryRs = QBXMLMsgsRs.iter("ItemInventoryRet")
|
|
GeneralSummaryReportQueryRs = QBXML.findall('GeneralSummaryReportQueryRs')
|
|
for child in QBXMLMsgsRs:
|
|
print("IRQ Modul:",child.tag, child.attrib['statusMessage'])
|
|
itemInvDict[child.attrib['statusSeverity']] = [child.attrib['statusMessage'], child.attrib['statusCode']]
|
|
#print(GeneralSummaryReportQueryRs) Cannot use this. Result : None
|
|
#print("")
|
|
DataRow = QBXML.iter("DataRow")
|
|
print(f"itemreportquery -> {DataRow}")
|
|
# print("Hello")
|
|
for ItemInvRet in DataRow:
|
|
try:
|
|
Rowdata = ItemInvRet.find("RowData")
|
|
|
|
fullName = Rowdata.attrib["value"]
|
|
ColDatas = ItemInvRet.iter("ColData")
|
|
|
|
for ColData in ColDatas:
|
|
if ColData.attrib["colID"] == "5":
|
|
QOH = ColData.attrib["value"]
|
|
elif ColData.attrib["colID"] =="6":
|
|
QOSO = ColData.attrib["value"]
|
|
elif ColData.attrib["colID"] =="8":
|
|
QA = ColData.attrib["value"]
|
|
print("IRQ Modul:",fullName, QOH, QOSO, QA, getShortName(fullName))
|
|
itemInvDict[fullName] = [getShortName(fullName), int(QOH) - int(QOSO)]
|
|
#print(ItemInvRet.attrib, ItemInvRet.tag, ItemInvRet.text)
|
|
|
|
# QOH = ItemInvRet.find('QuantityOnHand').text
|
|
# QOSO = ItemInvRet.find('QuantityOnSalesOrder').text
|
|
# #txnid = ItemInvRet.find('ListID').text
|
|
# name = ItemInvRet.find('Name').text
|
|
# fullName = ItemInvRet.find('FullName').text
|
|
# itemInvDict[fullName] = [name, int(QOH) - int(QOSO)]
|
|
# #print(itemInvDict[name], name, QOH, QOSO)
|
|
except AttributeError:
|
|
pass
|
|
print(" Attribute err")
|
|
except:
|
|
print("err")
|
|
print("")
|
|
return itemInvDict
|
|
|
|
def getShortName(FullName):
|
|
#print(FullName)
|
|
return FullName.split(":")[-1]
|
|
|
|
|
|
if __name__=="__main1__":
|
|
itemInvDict=QBStock()
|
|
if __name__=="__main__":
|
|
itemInvDict=QBStock(searchitems={"ECO:0:ECO-002","ECO:0:ECO-001",})
|
|
print("SEcond")
|
|
#itemInvDict=QBStock(searchitems={"ECO:0:ECO-002","ECO:0:ECO-001","TS-Z2223"})
|
|
name = "TS-Z222"
|
|
|
|
for x in itemInvDict:
|
|
print (x, itemInvDict[x])
|
|
if name in itemInvDict:
|
|
print(itemInvDict[name])
|
|
print(itemInvDict["TS-Z222"])
|
|
|