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 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 autogenAll():
if request.json is not None and 'ids' in request.json.keys() and len(
request.json['ids']) != 0:
ids = request.json['ids']
else:
ids = db.readSQL("SELECT id from individualTransactions")
ids = [tranId[0] for tranId in ids]
for tranId in ids:
microsoft.generateConceptForTransaction(tranId)
return 'completed'
def transactionFreqs():
siteId = request.json['siteId']
if "day" in request.json.keys() and "hour" in request.json.keys():
day = request.json['day'] #0 - 6
hour = request.json['hour'] #0 - 23
data = db.readSQL(
"SELECT sum(price), individualTransactions.categoryId, categories.description, longitude, latitude FROM individualTransactions \
join individualItems on individualItems.id = individualTransactions.indItemId \
join categories on categories.id = individualTransactions.categoryId \
join sites on individualTransactions.siteId = sites.id \
where individualTransactions.siteId = N\'{}\' and dayOfWeek = {} and hourOfDay = {} group by individualTransactions.categoryId, categories.description, longitude, latitude"
.format(siteId, day, hour))
toRet = Dict()
i = 0
for row in data:
toRet[i].siteId = siteId
toRet[i].totalSales = row[0]
toRet[i].categoryId = row[1]
toRet[i].description = row[2]
toRet[i].longitude = row[3]
toRet[i].latitude = row[4]
i += 1
return json.dumps(toRet)
else:
data = db.readSQL(
"SELECT sum(price), individualTransactions.categoryId, categories.description, longitude, latitude FROM individualTransactions \
join individualItems on individualItems.id = individualTransactions.indItemId \
join categories on categories.id = individualTransactions.categoryId \
join sites on individualTransactions.siteId = sites.id \
where individualTransactions.siteId = N\'{}\' group by individualTransactions.categoryId, categories.description, longitude, latitude"
.format(siteId))
toRet = Dict()
i = 0
for row in data:
toRet[i].siteId = siteId
toRet[i].totalSales = row[0]
toRet[i].categoryId = row[1]
toRet[i].description = row[2]
toRet[i].longitude = row[3]
toRet[i].latitude = row[4]
i += 1
return json.dumps(toRet)
def transactionStatsData():
day = request.json['day'] #0 - 6
hour = request.json['hour'] #0 - 23
cat = request.json['categoryId']
if 'siteId' in request.json.keys():
siteId = request.json['siteId']
res = db.readSQL(
"SELECT individualTransactions.id, price FROM individualTransactions JOIN individualItems on indItemId = individualItems.id where dayOfWeek = {} and hourOfDay = {} and categoryId = {} and siteId = N\'{}\'"
.format(day, hour, cat, siteId))
else:
siteId = None
res = db.readSQL(
"SELECT individualTransactions.id, price FROM individualTransactions JOIN individualItems on indItemId = individualItems.id where dayOfWeek = {} and hourOfDay = {} and categoryId = {}"
.format(day, hour, cat))
prices = []
for row in res:
prices.append(row[1])
if len(prices) <= 1:
varPrice = 0
else:
varPrice = variance(prices)
if 'siteId' in request.json.keys():
return json.dumps({
'siteId': siteId,
'category': cat,
'day': day,
'hour': hour,
'avgPrice': mean(prices),
'var': varPrice
})
else:
return json.dumps({
'category': cat,
'day': day,
'hour': hour,
'avgPrice': mean(prices),
'var': varPrice
})
def allTransactionData():
if "day" in request.json.keys() and "hour" in request.json.keys():
day = request.json['day'] #0 - 6
hour = request.json['hour'] #0 - 23
data = db.readSQL(
"SELECT sum(price), individualTransactions.categoryId, categories.description, longitude, latitude, individualTransactions.siteId FROM individualTransactions \
join individualItems on individualItems.id = individualTransactions.indItemId \
join categories on categories.id = individualTransactions.categoryId \
join sites on individualTransactions.siteId = sites.id \
where dayOfWeek = {} and hourOfDay = {} \
group by individualTransactions.siteId, individualTransactions.categoryId, categories.description, longitude, latitude"
.format(day, hour))
else:
data = db.readSQL(
"SELECT sum(price), individualTransactions.categoryId, categories.description, longitude, latitude, individualTransactions.siteId FROM individualTransactions \
join individualItems on individualItems.id = individualTransactions.indItemId \
join categories on categories.id = individualTransactions.categoryId \
join sites on individualTransactions.siteId = sites.id \
group by individualTransactions.siteId, individualTransactions.categoryId, categories.description, longitude, latitude"
)
toRet = Dict()
i = 0
sumPricesBySiteId = Dict()
for row in data:
toRet[i].siteId = row[5]
toRet[i].totalSales = row[0]
toRet[i].categoryId = row[1]
toRet[i].description = row[2]
toRet[i].longitude = row[3]
toRet[i].latitude = row[4]
i += 1
if row[5] in sumPricesBySiteId.keys():
sumPricesBySiteId[row[5]] += row[0]
else:
sumPricesBySiteId[row[5]] = row[0]
for j in range(i):
toRet[j].proportionOfSiteSales = toRet[
j].totalSales / sumPricesBySiteId[toRet[j].siteId]
return json.dumps(toRet)
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))
def getTransactionsByIds():
args = request.args.to_dict()
print(args)
min = args['startId']
max = args['endId']
res = db.readSQL(
"SELECT individualTransactions.id as id, individualTransactions.dayOfWeek as day, individualTransactions.hourOfDay as hour, \
individualItems.price as price, description as description, individualTransactions.siteId as siteId\
FROM individualTransactions join individualItems on individualItems.id = indItemId where individualTransactions.id >= {} and individualTransactions.id <= {}"
.format(min, max))
toReturn = []
for row in res:
toReturn.append({
'id': row[0],
'day': row[1],
'hour': row[2],
'price': row[3],
'description': row[4],
'siteId': row[5]
})
return json.dumps(toReturn)
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']
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)
print(query)
db.writeSQL(query)
#
ids = db.readSQL("SELECT individualTransactions.id from individualTransactions left join categories on categories.id = categoryId where categories.description is null");
ids = [tranId[0] for tranId in ids]
for tranId in ids:
microsoft.generateConceptForTransaction(tranId)
def ajax_allTransactionData_splitByTime():
if request.json is not None and "day" in request.json.keys(
) and "hour" in request.json.keys():
day = request.json['day'] #0 - 6
hour = request.json['hour'] #0 - 23
data = db.readSQL(
"SELECT sum(price), individualTransactions.categoryId, categories.description, longitude, latitude, individualTransactions.siteId FROM individualTransactions \
join individualItems on individualItems.id = individualTransactions.indItemId \
join categories on categories.id = individualTransactions.categoryId \
join sites on individualTransactions.siteId = sites.id \
where dayOfWeek = {} and hourOfDay = {} \
group by individualTransactions.siteId, individualTransactions.categoryId, categories.description, longitude, latitude"
.format(day, hour))
else:
data = db.readSQL(
"SELECT sum(price), individualTransactions.categoryId, categories.description, longitude, latitude, individualTransactions.siteId, dayOfWeek, hourOfDay FROM individualTransactions \
join individualItems on individualItems.id = individualTransactions.indItemId \
join categories on categories.id = individualTransactions.categoryId \
join sites on individualTransactions.siteId = sites.id \
group by individualTransactions.siteId, individualTransactions.categoryId, categories.description, longitude, latitude, dayOfWeek, hourOfDay"
)
toRet = Dict()
i = 0
bySiteId = Dict()
for row in data:
toRet[i].siteId = row[5]
toRet[i].totalSales = row[0]
toRet[i].categoryId = row[1]
toRet[i].description = row[2]
toRet[i].longitude = row[3]
toRet[i].latitude = row[4]
toRet[i].day = row[6]
toRet[i].hour = row[7]
if row[5] in bySiteId.keys():
bySiteId[row[5]].size += row[0]
bySiteId[row[5]].cats.append(toRet[i])
else:
bySiteId[row[5]].size = row[0]
bySiteId[row[5]].cats = [toRet[i]]
i += 1
for j in range(i):
toRet[j].proportionOfSiteSales = toRet[j].totalSales / bySiteId[
toRet[j].siteId].size
toRetReally = []
for siteId in bySiteId.keys():
toappend = {
'siteId': siteId,
'longitude': bySiteId[siteId].cats[0].longitude,
'latitude': bySiteId[siteId].cats[0].latitude,
'size': bySiteId[siteId].size,
'categories': []
}
for cat in bySiteId[siteId].cats:
toappend['categories'].append({
'title': cat.description,
'value': cat.totalSales,
'proportion': cat.proportionOfSiteSales,
'day': cat.day,
'week': cat.week
})
toRetReally.append(toappend)
resp = Response(json.dumps(toRetReally))
resp.headers['Access-Control-Allow-Origin'] = '*'
resp.mimetype = 'application/json'
return resp
def categoryEntityLink(entity):
db.readSQL("SELECT categoryId, description from entityCategoryMap join categories on categories.id = categoryId where entity = N\'{}\'".format(entity))