def populateDB(siteDd):
transactions = ncr.getTransactionsBySite(siteId)
for transaction in transactions['pageContent']:
tlogId = transaction['tlogId']
transLog = ncr.getTransactionsByDoc(tlogId)
price = transLog['tlog']['totals']['grandAmount']['amount']
for item in transLog['tlog']['items']:
itemId = item['id']
itemName = item['productName']
itemName = re.sub('\'', '', itemName)
actualAmount = item['actualAmount']['amount']
print(
"SELECT * from individualItems where ncrItemId = N\'{}\' and price = {}"
.format(itemId, actualAmount))
possibleMatch = db.readSQL(
"SELECT id from individualItems where ncrItemId = N\'{}\' and price = {}"
.format(itemId, actualAmount))
if (len(possibleMatch) == 0):
print(
"INSERT into \"dbo\".\"individualItems\" (ncrItemId, price, description) values (N\'{}\', {}, N\"{}\")"
.format(itemId, actualAmount, itemName))
db.writeSQL(
"INSERT into \"dbo\".\"individualItems\" (ncrItemId, price, description) values (N\'{}\', {}, N\'{}\')"
.format(itemId, actualAmount, itemName))
possibleMatch = db.readSQL(
"SELECT id from individualItems where ncrItemId = N\'{}\' and price = {}"
.format(itemId, actualAmount))
transactionTime = transaction['endTransactionDateTimeUtc']
utc_time = datetime.strptime(transactionTime, "%Y-%m-%dT%H:%M:%SZ")
query = "INSERT into \"dbo\".\"individualTransactions\" (indItemId, dayOfWeek, hourOfDay, siteId) values ({}, {}, {}, N\'{}\' )".format(
possibleMatch[0][0], utc_time.weekday(), utc_time.hour, siteId)
db.writeSQL(query)
def createCategory():
siteId = request.json['siteId']
categoryName = request.json['category']
db.writeSQL(
"insert into categories (siteId, description) values (N\'{}\', N\'{}\')"
.format(siteId, categoryName))
return json.dumps({"status": "success"})
def setCategory():
categoryId = request.json['categoryId']
transactionId = request.json['transactionId']
db.writeSQL(
"UPDATE individualTransactions SET categoryId = {} where id = {}".
format(categoryId, transactionId))
db.writeSQL(
"UPDATE individualTransactions SET categoryUserApproved = N\'yes\' where id = {}"
.format(transactionId))
entities = db.readSQL(
"SELECT id, itemId, categoryId from entityCategoryMap where categoryId = {}"
)
#apply category change to other transactions where human approval not given
for entity in entities:
otherItemId = entity[1]
toFillIn = db.readSQL(
"SELECT id FROM individualTransactions where indItemId = {} and categoryUserApproved is null"
.format(otherItemId))
for id in toFillIn:
transactionId = toFillIn[0]
db.writeSQL(
"UPDATE individualTransactions SET categoryId = {} where id = {}"
.format(categoryId, transactionId))
db.writeSQL(
"UPDATE individualTransactions SET categoryUserApproved = N\'yes\' where id = {}"
.format(transactionId))
def generateConceptForTransaction(transactionId):
res = db.readSQL("SELECT description, categoryUserApproved, indItemId FROM individualTransactions join individualItems on individualItems.id = indItemId where individualTransactions.id = {}".format(transactionId))
if len(res) == 0:
raise Exception("No transaction matching the expected id")
name = res[0][0]
itemId = res[0][2]
snippets = searchSnippets(name, count=5)
snippets.append(name)
# entities = importantEntities(snippets)
# for entity in entities.keys():
# snippets += (searchSnippets(entity, count=5))
entities = importantEntities(snippets)
preexistingConcept = None
concept = findMaxInDict(entities)
concept = re.sub("\'", '', concept)
concept = re.sub('ā', 'a', concept)
if res[0][1] is None or res[0][1] == "false":
siteId = db.readSQL("SELECT siteId from individualTransactions where individualTransactions.id = N\'{}\'".format(transactionId))[0][0]
db.writeSQL("INSERT into categories (siteId, description) VALUES (N\'{}\', N\'{}\')".format(siteId, concept))
conceptId = db.readSQL("SELECT id FROM categories where siteId = N\'{}\' and description = N\'{}\'".format(siteId, concept))[0][0]
db.writeSQL("UPDATE individualTransactions SET categoryId = {} where id = {}".format(conceptId, transactionId))
for entity in entities:
db.writeSQL("INSERT into entityCategoryMap (categoryId, entityName, itemId) values ({}, N\'{}\', {})".format(conceptId, entity, itemId))
return concept
def getCategories(siteid):
if request.method == 'GET':
res = db.readSQL(
"SELECT * FROM categories where siteid = N\'{}\'".format(siteid))
toReturn = []
for row in res:
toReturn.append({
'categoryId': row[0],
'siteID': row[1],
'description': row[2]
})
return json.dumps(toReturn)
elif request.method == 'POST':
category = request.json['category']
res = db.writeSQL(
"INSERT into categories (siteId, description) values ({}, N\'{}\')"
.format(siteid, category))
import ncr
import db
import re
from datetime import datetime
import microsoft
siteIds = ["7c54465e9f5344598276ec1f941f5a3c"]
for siteId in siteIds:
siteData = ncr.getSiteById(siteId)
siteLong = siteData['coordinates']['longitude']
siteLat = siteData['coordinates']['latitude']
db.writeSQL("insert into sites values (N\'{}\', {}, {})".format(siteId, siteLong, siteLat))
transactions = ncr.getTransactionsBySite(siteId)
for transaction in transactions['pageContent']:
tlogId = transaction['tlogId']
transLog = ncr.getTransactionsByDoc(tlogId)
price = transLog['tlog']['totals']['grandAmount']['amount']
for item in transLog['tlog']['items']:
itemId = item['id']
itemName = item['productName']
itemName = re.sub('\'', '',itemName)
actualAmount = item['actualAmount']['amount']
print("SELECT * from individualItems where ncrItemId = N\'{}\' and price = {}".format(itemId, actualAmount))
possibleMatch = db.readSQL("SELECT id from individualItems where ncrItemId = N\'{}\' and price = {}".format(itemId, actualAmount))
if (len(possibleMatch) == 0):
print("INSERT into \"dbo\".\"individualItems\" (ncrItemId, price, description) values (N\'{}\', {}, N\"{}\")".format(itemId, actualAmount, itemName))
db.writeSQL("INSERT into \"dbo\".\"individualItems\" (ncrItemId, price, description) values (N\'{}\', {}, N\'{}\')".format(itemId, actualAmount, itemName))
possibleMatch = db.readSQL("SELECT id from individualItems where ncrItemId = N\'{}\' and price = {}".format(itemId, actualAmount))
transactionTime = transaction['endTransactionDateTimeUtc']