mirror of
https://github.com/bcomsugi/dasaproject.git
synced 2026-01-08 18:42:37 +07:00
196 lines
7.3 KiB
Python
196 lines
7.3 KiB
Python
#!usr/bin/python
|
|
import win32com.client
|
|
#from win32com.client import constants
|
|
import xml.etree.ElementTree as ET
|
|
import itemreportquery as irq
|
|
|
|
def QBStock(client_socket=None, filename='', searchitems = None):
|
|
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_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 ={}):
|
|
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", "stopOnError")
|
|
QBXMLMsgsRq.tail = "\n"
|
|
QBXMLMsgsRq.text = "\n "
|
|
ItemInventoryQueryRq = createSubElement(ET, QBXMLMsgsRq, "ItemInventoryQueryRq","\n " ,2,{"metaData":"MetaDataAndResponseData"})
|
|
|
|
# # if you have searchitems then loop here
|
|
if searchitems:
|
|
#print(searchitems)
|
|
for searchitem in searchitems:
|
|
searchitem = searchitem.strip('"')
|
|
FullName = createSubElement(ET, ItemInventoryQueryRq, "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", "TimeCreated",6)
|
|
IncludeRetElement = createSubElement(ET, ItemInventoryQueryRq, "IncludeRetElement", "TimeModified",6)
|
|
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)
|
|
|
|
# Disconnect from Quickbooks
|
|
sessionManager.EndSession(ticket) # Close the company file
|
|
sessionManager.CloseConnection() # Close the connection
|
|
print (response_string)
|
|
|
|
# Parse the response into an Element Tree and peel away the layers of response
|
|
#QBXML = xml.etree.ElementTree.fromstring(response_string)
|
|
QBXML = ET.fromstring(response_string)
|
|
print("")
|
|
#print(QBXML)
|
|
#QBXMLMsgsRs = QBXML.find('QBXMLMsgsRs')
|
|
#InventoryAdjustmentQueryRs = QBXMLMsgsRs.getiterator("InventoryAdjustmentRet")
|
|
#ItemInventoryQueryRs = QBXMLMsgsRs.iter("ItemInventoryRet")
|
|
ItemInventoryQueryRs = QBXML.iter("ItemInventoryRet")
|
|
itemInvDict={}
|
|
for ItemInvRet in ItemInventoryQueryRs:
|
|
try:
|
|
# QOH = ItemInvRet.find('QuantityOnHand').text
|
|
QOH = ItemInvRet.find('QuantityOnHand').text
|
|
QOSO = ItemInvRet.find('QuantityOnSalesOrder')
|
|
if QOSO != None:
|
|
QOSO = QOSO.text
|
|
else:
|
|
QOSO = 0
|
|
#txnid = ItemInvRet.find('ListID').text
|
|
name = ItemInvRet.find('Name').text
|
|
fullName = ItemInvRet.find('FullName').text
|
|
# print(name, fullName)
|
|
itemInvDict[fullName] = [name, int(QOH) - int(QOSO)]
|
|
print(itemInvDict[fullName], name, QOH, QOSO)
|
|
except AttributeError as e:
|
|
pass
|
|
print(" Attribute err:", e)
|
|
except Exception as e:
|
|
print("err:", e)
|
|
checkItemReportQuery(itemInvDict)
|
|
return itemInvDict
|
|
|
|
def checkItemReportQuery(itemInvDict):
|
|
DictRs=irq.QBStock(searchitems=itemInvDict)
|
|
print ("from IRQ:", DictRs)
|
|
for item in DictRs:
|
|
|
|
#print(item)
|
|
if item in itemInvDict:
|
|
print(itemInvDict[item], DictRs[item])
|
|
if itemInvDict[item] != DictRs[item]:
|
|
print("item:{} berbeda QOH {} di ItemList dan {} di ItemReport".format(item, itemInvDict[item], DictRs[item]))
|
|
itemInvDict[item]= [itemInvDict[item][0], "?{}??{}".format(itemInvDict[item][1], DictRs[item][1])]
|
|
else:
|
|
itemInvDict[item]= [itemInvDict[item][0], "{}=={}".format(itemInvDict[item][1], DictRs[item][1])]
|
|
print("")
|
|
|
|
if __name__=="__main__":
|
|
print('iteminventorydasa main start')
|
|
# itemInvDict=QBStock(searchitems={"ECO:0:ECO-002","ECO:0:ECO-001",})
|
|
itemInvDict=QBStock(searchitems={"taco:g_d:TH-048D","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"])
|
|
print(type(itemInvDict), itemInvDict)
|
|
print('iteminventorydasa main END') |