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']