def main():
cfg = load_conf()
if cfg["useSSL"]:
create_ssl_cert([cfg["backendIP"]])
if cfg["dbMode"] and cfg["sqlDatabase"] and cfg["sqlPassword"] and cfg[
"sqlServerIP"] and cfg["sqlUsername"]:
print("Using " + cfg["dbMode"] + " DB")
try:
conn = load_db_conn()[0]
if cfg["dbMode"] == "mssql":
init_mssql_db(conn)
elif cfg["dbMode"] == "mysql":
init_mysql_db(conn)
else:
print(
"Error! No valid db mode found. Please use mssql or mysql")
except Exception as e:
print(e)
else:
print("No db mode set.")
check_existing_token()
create_web_config()
server()
def get_history():
conn, cursor = load_db_conn()
history_json = []
cursor.execute(
"select SUM(total) as totalSum, location, id, timestamp from purchaseData \
where id is not null \
GROUP BY timestamp, id, location \
ORDER BY timestamp desc")
rows = cursor.fetchall()
for row in rows:
if cfg["dbMode"] == "mysql":
add_json = {
"location": row[1],
"totalSum": str(row[0]),
"timestamp": str(row[3]),
"id": row[2],
}
else:
add_json = {
"location": row.location,
"totalSum": str(row.totalSum),
"timestamp": str(row.timestamp),
"id": row.id,
}
history_json.append(add_json)
conn.close()
return json.dumps(history_json)
def get_history_details():
purchase_id = request.args["purchaseID"]
conn, cursor = load_db_conn()
sql_query = ("SELECT storeName, total, date, purchaseId " +
"FROM receipts re " +
"JOIN stores st ON re.storeId = st.id " + "where re.id = ?")
if cfg["dbMode"] == "mysql":
sql_query = convert_to_mysql_query(sql_query)
cursor.execute(sql_query, [purchase_id])
row = cursor.fetchone()
if row:
if cfg["dbMode"] == "mysql":
store_name = row[0]
receipt_total = row[1]
receipt_date = row[2]
db_purchase_id = row[3]
else:
store_name = row.storeName
receipt_total = row.total
receipt_date = row.date
db_purchase_id = row.purchaseId
receipt_date = receipt_date.strftime("%d.%m.%Y")
purchase_details = {
"storeName": store_name,
"receiptTotal": str(receipt_total),
"receiptDate": receipt_date,
"purchaseID": db_purchase_id,
"receiptItems": [],
}
sql_query = "select article_name, total, category from purchaseData where id = ?"
if cfg["dbMode"] == "mysql":
sql_query = convert_to_mysql_query(sql_query)
cursor.execute(sql_query, [purchase_id])
rows = cursor.fetchall()
for row in rows:
if cfg["dbMode"] == "mysql":
add_json = [row[0], str(row[1]), row[2]]
else:
add_json = [row.article_name, str(row.total), row.category]
purchase_details["receiptItems"].append(add_json)
conn.close()
return json.dumps(purchase_details)
else:
return "Purchase not found!", 500
def write_receipt_to_db():
post_string = json.dumps(request.get_json())
post_json = json.loads(post_string)
conn, cursor = load_db_conn()
store_id = get_store_id(post_json["storeName"])
receipt_date = post_json["receiptDate"]
receipt_total = post_json["receiptTotal"]
receipt_id = int(str(uuid.uuid1().int)[:6])
receipt_date = datetime.strptime(receipt_date, "%d.%m.%Y")
receipt_date = receipt_date.strftime("%m-%d-%Y")
# Write article positions
for article in post_json["receiptItems"]:
article_id = int(str(uuid.uuid1().int)[:8])
article_name = article[1]
article_sum = article[2]
article_category_id = get_category_id(article[3])
if not article_category_id:
return "Category id for category: " + article[3] + " not found", 500
sql_query = "INSERT INTO items values (?,?,?,?)"
if cfg["dbMode"] == "mysql":
sql_query = convert_to_mysql_query(sql_query)
cursor.execute(
sql_query,
[article_id, article_name, article_sum, article_category_id])
sql_query = "INSERT INTO purchasesArticles values (?,?)"
if cfg["dbMode"] == "mysql":
sql_query = convert_to_mysql_query(sql_query)
cursor.execute(sql_query, [receipt_id, article_id])
# Write receipt summary
if cfg["dbMode"] == "mysql":
sql_query = (
"INSERT INTO receipts values (%s,%s,STR_TO_DATE(%s,'%m-%d-%Y'),%s,%s,%s)"
)
else:
sql_query = "INSERT INTO receipts values (?,?,?,?,?,?)"
cursor.execute(
sql_query,
[receipt_id, store_id, receipt_date, receipt_total, None, receipt_id])
conn.commit()
conn.close()
return "Done!"
def upload():
file = request.files["file"]
file_name = file.filename
gaussian_blur = True
if file.content_type == "application/pdf":
file = convert_pdf_to_png(file.read())
file_name = file_name.split(".")[0] + ".png"
gaussian_blur = False
if not file:
return "No valid file found", 500
url = ("http://" + str(cfg["parserIP"]) + ":" + str(cfg["parserPort"]) +
"/api/upload?access_token=" + str(cfg["parserToken"]) +
"&legacy_parser=True" + "&grayscale_image=True" +
"&rotate_image=True" + "&gaussian_blur=" + str(gaussian_blur) +
"&median_blur=True")
receipt_upload = requests.post(url, files={"file": (file_name, file)})
if receipt_upload.status_code == 200:
upload_response = json.dumps(receipt_upload.content.decode("utf8"))
response_json = json.loads(upload_response)
response_json = json.loads(response_json)
# Replace " in Date
if '"' in response_json["receiptDate"]:
response_json["receiptDate"] = response_json[
"receiptDate"].replace('"', "")
# Create 4 digit year
if response_json["receiptDate"] != "null":
year_string = response_json["receiptDate"].split(".")
if len(year_string[2]) < 4:
year_string[2] = "20" + year_string[2]
response_json["receiptDate"] = (year_string[0] + "." +
year_string[1] + "." +
year_string[2])
conn, cursor = load_db_conn()
for idx, article in enumerate(response_json["receiptItems"]):
article = article[0]
splitted_articles = article.split(" ")
for article in splitted_articles:
if len(article) > 3:
if cfg["dbMode"] == "mysql":
sql_query = "SELECT category FROM purchaseData where article_name like %s order by timestamp desc limit 1"
else:
sql_query = "SELECT TOP 1 category FROM purchaseData where article_name like ? order by timestamp desc"
cursor.execute(sql_query, [f"%{article}%"])
row = cursor.fetchone()
if row:
if cfg["dbMode"] == "mysql":
found_cat = row[0]
else:
found_cat = row.category
copy_array = response_json["receiptItems"][idx]
copy_array.insert(2, found_cat)
response_json["receiptItems"][idx] = copy_array
break
conn.close()
return json.dumps(response_json)
else:
return "Error on upload", receipt_upload.status_code