def setupSourceFiles(self):
for fileName in [
'books.source', 'books.inc', 'books.bp', 'books.pa',
'books.scripts', 'books.tables', 'test.bp', 'test.source',
'images', 'books.pysrc', 'TESTS', 'TEST_BACKOUT', 'TEST_BATCH',
'TEST_DBG', 'TEST_INC', 'TEST_LOCAL', 'TEST_MOCKS', 'TEST_OUT',
'TEST_PROFILE', 'TEST_RESULTS', 'TEST_RUN', 'TEST_SCRIPTS',
'TEST_STATE'
]:
rec = u2py.DynArray()
rec.replace(1, 'F')
rec.replace(2, '../../bookstore/server/' + fileName)
rec.replace(3, 'D_VOC')
if fileName[-3:] == '.bp':
rec.replace(4, 'M')
rec.replace(7, 1, fileName)
rec.replace(8, 1, fileName)
rec.replace(7, 2, 'unidata')
rec.replace(8, 2, 'unidata')
self.VOC_FILE.write(fileName, rec)
u2py.run('CREATE.FILE DICT ' + fileName + ' 1,1', capture=False)
if fileName[-3:] == '.bp':
self.compilePrograms(fileName)
elif fileName[-3:] == '.pa':
self.createRemotes(fileName)
def setupDataFiles(self):
self.createFilePointer('books.tables', 'books.tables.safe')
u2py.run('MAKETABLE books.tables.safe * /NOINCLUDES')
tempFL = u2py.File('books.tables.safe')
slist = u2py.List(1, tempFL)
for id in slist:
fileName = str(id)
if not fileName == 'DEFAULT':
if not self.loadAssets(fileName):
self.createFilePointer(fileName, fileName + '.safe')
cmd = 'COPYI FROM ' + fileName + '.safe TO ' + fileName + ' ALL OVERWRITING'
u2py.run(cmd)
def run(self, args):
imageName = ''
errText = ''
try:
startDate = args['start_date']
endDate = args['end_date']
path = '..\\..\\bookstore\\web\\work\\'
cmd = "SELECT BOOK_GENRE, SUM(QTY) FROM UNNEST U2_ORDERS ON BOOK_ID "
cmd = cmd + "WHERE ORDER_DATE BETWEEN '" + startDate[0]
cmd = cmd + "' AND '" + endDate[0]
cmd = cmd + "' GROUP BY BOOK_GENRE TOXML;"
xmlData = u2py.run(cmd, capture=True)
etree = ET.fromstring(xmlData.replace('\n', ''))
dfcols = ['Genre', 'Qty']
df = pd.DataFrame(columns=dfcols)
for i in etree.iter(tag='U2_ORDERS'):
df = df.append(pd.Series(
[i.get('BOOK_GENRE'),
int(i.get('QTY'))], index=dfcols),
ignore_index=True)
# not before pandas 0.21df.infer_objects()
df[['Qty']] = df[['Qty']].apply(pd.to_numeric)
rcParams.update({'figure.autolayout': True})
ax = df['Qty'].plot()
ax.set_xticks(df.index)
ax.set_xticklabels(df.Genre, rotation=90)
imageName = str(uuid.uuid4()) + '.png'
plt.savefig(path + imageName)
except:
errText = "Error building chart"
return {'chart': imageName, 'error': errText}
def invoiceOrderDetails(orderId):
status = checkExistingRecord('PO.ORDER.MST',orderId)
if(status):
orderDetailsXML = u2py.run("LIST DATA PO.ORDER.MST "+orderId+" ORDER.ITEM.IDS ORDER.ITEM.QTY ORDER.ITEM.COST TOXML",capture=True)
xmldata = orderDetailsXML.strip()
# itemCost=itemIds=itemQuantity=data=[]
itemCost = []
itemQuantity = []
itemIds = []
orderDict={}
orderDetail = xmltodict.parse(xmldata)['ROOT']['PO.ORDER.MST']
print(orderDetail)
for i in range(len(orderDetail['ORDER.ITEM.COST_MV'])):
itemCost.append(orderDetail['ORDER.ITEM.COST_MV'][i]['@ORDER.ITEM.COST'])
for i in range(len(orderDetail['ORDER.ITEM.QTY_MV'])):
itemQuantity.append(orderDetail['ORDER.ITEM.QTY_MV'][i]['@ORDER.ITEM.QTY'])
for i in range(len(orderDetail['ORDER.ITEM.IDS_MV'])):
itemIds.append(orderDetail['ORDER.ITEM.IDS_MV'][i]['@ORDER.ITEM.IDS'])
return{
'status':200,
'cost':itemCost,
'quantity':itemQuantity,
"ids": itemIds,
"orderID":orderDetail['@_ID']
}
else:
return{
'status':404,
'message':'OrderNo not found'
}
def particularVendor(vendorId):
status = checkExistingRecord("PO.VENDOR.MST",vendorId)
if(status):
ids={}
cost=[]
itemData=[]
vendorDetail=[]
dictItems={}
itemId=[]
itemDict=vendorDict={}
cmd=u2py.run("LIST DATA PO.VENDOR.MST "+vendorId+" VEND.COMPANY VEND.NAME VEND.ADDRESS VEND.PHONE ITEM.IDS TOXML",capture=True)
my_xml=cmd.strip()
data = xmltodict.parse(my_xml)['ROOT']['PO.VENDOR.MST']
for j in range (len(data['ITEM.IDS_MV'])):
itemDict={}
itemDict['itemId']= data['ITEM.IDS_MV'][j]['@ITEM.IDS']
itemId.append(itemDict)
vendorDict['Company']=data['@VEND.COMPANY']
vendorDict['Contact']=data['@VEND.NAME']
vendorDict['Phone']=data['@VEND.PHONE']
vendorDict['Street']=data['VEND.ADDRESS_MV'][0]['@VEND.ADDRESS']
vendorDict['City']=data['VEND.ADDRESS_MV'][1]['@VEND.ADDRESS']
vendorDict['State']=data['VEND.ADDRESS_MV'][2]['@VEND.ADDRESS']
vendorDict['Zip']=data['VEND.ADDRESS_MV'][3]['@VEND.ADDRESS']
return{'status':200,
'data':vendorDict,
'itemIds':itemId
}
else:
return {
'status':404,
'msg':'Order ID not found'
}
def getvendorItemDetails(itemID):
itemDescriptionXML = u2py.run("LIST PO.ITEM.MST WITH @ID = "+itemID+" DESC TOXML",capture=True)
xmldata = itemDescriptionXML.strip()
itemDescription = xmltodict.parse(xmldata)['ROOT']['PO.ITEM.MST']['@DESC']
return {
'status':200,
'msg':'success',
'data':itemDescription
}
def readFromU2():
cmd=u2py.run("LIST DATA PO.ITEM.MST DESC TOXML",capture=True)
my_xml = cmd.strip()
ids={}
cost=[]
itemData=[]
dictItems={}
dataLength=len(xmltodict.parse(my_xml)['ROOT']['PO.ITEM.MST'])
for i in range(dataLength):
data = xmltodict.parse(my_xml)['ROOT']['PO.ITEM.MST'][i]
ids=(data['@_ID'])
itemData.append(data['@DESC'])
dictItems[ids]=itemData
itemData=[]
return{"table": dictItems },200
def allVendors():
ids={}
cost=[]
itemData=[]
vendorDetail=[]
dictItems={}
itemId=[]
cmd=u2py.run("LIST DATA PO.VENDOR.MST VEND.COMPANY VEND.NAME VEND.ADDRESS VEND.PHONE ITEM.IDS TOXML",capture=True)
my_xml=cmd.strip()
data = xmltodict.parse(my_xml)['ROOT']['PO.VENDOR.MST']
if(type(data) is list):
for i in range(len(data)):
data = xmltodict.parse(my_xml)['ROOT']['PO.VENDOR.MST'][i]
for j in data['ITEM.IDS_MV']:
itemId.append(j['@ITEM.IDS'])
ids=data['@_ID']
vendorDetail.append(data['@VEND.COMPANY'])
vendorDetail.append(data['@VEND.NAME'])
vendorDetail.append(data['@VEND.PHONE'])
itemData.append(vendorDetail)
itemData.append(itemId)
dictItems[ids]=itemData
itemData=[]
itemId=[]
vendorDetail=[]
else:
if(type(data['ITEM.IDS_MV']) is list):
for j in data['ITEM.IDS_MV']:
itemId.append(j['@ITEM.IDS'])
ids=data['@_ID']
else:
itemId.append(data['ITEM.IDS_MV']['@ITEM.IDS'])
ids=data['ITEM.IDS_MV']['@_ID']
vendorDetail.append(data['@VEND.COMPANY'])
vendorDetail.append(data['@VEND.NAME'])
vendorDetail.append(data['@VEND.PHONE'])
itemData.append(vendorDetail)
itemData.append(itemId)
dictItems[ids]=itemData
return{'status':200,
'data':dictItems
}
def particularOrderDetails(orderID):
status = checkExistingRecord('PO.ORDER.MST',orderID)
if(status):
orderDetailsXML = u2py.run("LIST DATA PO.ORDER.MST "+orderID+" ORDER.DATE ORDER.STATUS COMP.NAME COMP.CONTACT.NAME COMP.ADDRESS COMP.PHONE ORDER.ITEM.IDS ORDER.ITEM.QTY ORDER.ITEM.COST VEND.NAME TOXML",capture=True)
xmldata = orderDetailsXML.strip()
orderDetail = xmltodict.parse(xmldata)['ROOT']['PO.ORDER.MST']
orderDetailsDict = itemDict = {}
itemList = []
orderDetailsDict['orderDate'] = orderDetail['@ORDER.DATE']
orderDetailsDict['companyName'] = orderDetail['@COMP.NAME']
orderDetailsDict['phoneNumber'] = orderDetail['@COMP.PHONE']
orderDetailsDict['contactName'] = orderDetail['@COMP.CONTACT.NAME']
orderDetailsDict['vendorName'] = orderDetail['@VEND.NAME']
orderDetailsDict['street'] = orderDetail['COMP.ADDRESS_MV'][0]['@COMP.ADDRESS']
orderDetailsDict['city'] = orderDetail['COMP.ADDRESS_MV'][1]['@COMP.ADDRESS']
orderDetailsDict['state'] = orderDetail['COMP.ADDRESS_MV'][2]['@COMP.ADDRESS']
orderDetailsDict['zipCode'] = orderDetail['COMP.ADDRESS_MV'][3]['@COMP.ADDRESS']
if(type(orderDetail['ORDER.ITEM.IDS_MV']) is list):
for i in range(len(orderDetail['ORDER.ITEM.IDS_MV'])):
itemDict = {}
itemDict['itemID'] = orderDetail['ORDER.ITEM.IDS_MV'][i]['@ORDER.ITEM.IDS']
itemDict['cost'] = orderDetail['ORDER.ITEM.COST_MV'][i]['@ORDER.ITEM.COST']
itemDict['quantity'] = orderDetail['ORDER.ITEM.QTY_MV'][i]['@ORDER.ITEM.QTY']
itemList.append(itemDict)
else:
itemDict = {}
itemDict['itemID'] = orderDetail['ORDER.ITEM.IDS_MV']['@ORDER.ITEM.IDS']
itemDict['cost'] = orderDetail['ORDER.ITEM.COST_MV']['@ORDER.ITEM.COST']
itemDict['quantity'] = orderDetail['ORDER.ITEM.QTY_MV']['@ORDER.ITEM.QTY']
itemList.append(itemDict)
return {
'status': 200,
'data': orderDetailsDict,
'itemList': itemList,
'submitStatus':orderDetail['@ORDER.STATUS']
}
else:
return{
'status':404,
'msg': 'Order no not found'
}
def run(self, args):
results = []
if "search" in args:
search = args["search"][0].upper()
cmd = 'SORT U2_BOOKS ID TITLE AUTHOR_NAME ISBN SALE_PRICE MEDIA'
cmd = cmd + ' WITH U_TITLE LIKE "...' + search + '..."'
cmd = cmd + ' OR WITH U_AUTHOR_NAME LIKE "...' + search + '..."'
cmd = cmd + ' ID.SUPP TOXML'
xmlData = u2py.run(cmd, capture=True)
etree = ET.fromstring(xmlData.replace('\n', ''))
for i in etree.iter(tag='U2_BOOKS'):
book = {
'id': i.get('ID'),
'title': i.get('TITLE'),
'author': i.get('AUTHOR_NAME'),
'isbn': i.get('ISBN'),
'price': i.get('SALE_PRICE'),
'media': i.get('MEDIA')
}
results.append(book)
return results
def run(startDate, endDate, path):
cmd = "SELECT BOOK_GENRE, SUM(QTY) FROM UNNEST U2_ORDERS ON BOOK_ID "
cmd = cmd + "WHERE ORDER_DATE BETWEEN '" + startDate
cmd = cmd + "' AND '" + endDate
cmd = cmd + "' GROUP BY BOOK_GENRE TOXML;"
xmlData = u2py.run(cmd, capture=True)
etree = ET.fromstring(xmlData.replace('\n', ''))
dfcols = ['Genre', 'Qty']
df = pd.DataFrame(columns=dfcols)
for i in etree.iter(tag='U2_ORDERS'):
df = df.append(pd.Series(
[i.get('BOOK_GENRE'), int(i.get('QTY'))], index=dfcols),
ignore_index=True)
# not before pandas 0.21df.infer_objects()
df[['Qty']] = df[['Qty']].apply(pd.to_numeric)
rcParams.update({'figure.autolayout': True})
ax = df['Qty'].plot()
ax.set_xticks(df.index)
ax.set_xticklabels(df.Genre, rotation=90)
imageName = str(uuid.uuid4()) + '.png'
plt.savefig(path + imageName)
return imageName
def getAllOrders():
itemList = []
itemOrderDataXML = u2py.run("LIST DATA PO.ORDER.MST ORDER.DATE VEND.NAME TOXML",capture=True)
xmldata = itemOrderDataXML.strip()
itemOrderDict = xmltodict.parse(xmldata)['ROOT']['PO.ORDER.MST']
if type(itemOrderDict) is list:
for item in itemOrderDict:
tempDict = {}
tempDict['purchaseOrderNo'] = item['@_ID']
tempDict['orderDate'] = item['@ORDER.DATE']
tempDict['companyName'] = item['@VEND.NAME']
itemList.append(tempDict)
else:
tempDict = {}
tempDict['purchaseOrderNo'] = itemOrderDict['@_ID']
tempDict['orderDate'] = itemOrderDict['@ORDER.DATE']
tempDict['companyName'] = itemOrderDict['@VEND.NAME']
itemList.append(tempDict)
return {
'status': 200,
'msg':'success',
'list': itemList
}
def initAccount(self):
print('Initializing account')
self.VOC_FILE = u2py.File('VOC')
rec = u2py.DynArray()
rec.replace(1, 'PA')
rec.replace(2, 'PTERM CASE NOINVERT')
self.VOC_FILE.write('LOGIN', rec)
u2py.run('PTERM CASE NOINVERT', capture=False)
u2py.run('LONGNAMES ON', capture=False)
u2py.run('TERM ,9999', capture=False)
self.setupUtilityPrograms()
self.setupSourceFiles()
self.setupDataFiles()
self.setupPythonPath()
self.setupPip()
self.setupPackages()
self.setupWebserver()
def compilePrograms(self, fileName):
u2py.run('CREATE.FILE ' + fileName + '.O 1,1 1,1,19', capture=False)
u2py.run('BASIC ' + fileName + ' * ', capture=False)
u2py.run('CATALOG ' + fileName + ' * ', capture=False)
# This demonstrates how to use pandas to sore UniVerse data retrieved
# using an XML query. The data is plotted using matplotlib.
#
# Usage: RUNPY books.pysrc plotstockgenre.py
#
# See the accompanying document on Python Samples in the docs folder.
#
import pandas as pd
import xml.etree.ElementTree as ET
import u2py
import matplotlib.pyplot as plt
from matplotlib import rcParams
xmlData = u2py.run(
"SELECT GENRE,SUM(STOCK_LEVEL) FROM U2_BOOKS GROUP BY GENRE TOXML;",
capture=True)
etree = ET.fromstring(xmlData.replace('\n', ''))
dfcols = ['Genre', 'Stock']
df = pd.DataFrame(columns=dfcols)
for i in etree.iter(tag='U2_BOOKS'):
df = df.append(pd.Series(
[i.get('GENRE'), int(i.get('STOCK_LEVEL'))], index=dfcols),
ignore_index=True)
# not before pandas 0.21df.infer_objects()
df[['Stock']] = df[['Stock']].apply(pd.to_numeric)
rcParams.update({'figure.autolayout': True})
ax = df['Stock'].plot()
ax.set_xticks(df.index)
ax.set_xticklabels(df.Genre, rotation=90)
imageName = 'stock.png'
