212 lines
8.6 KiB
Python
212 lines
8.6 KiB
Python
import MySQLdb as sql
|
|
|
|
import config
|
|
|
|
def sql_connect():
|
|
db = sql.connect(config.db_host, config.db_user, config.db_password, config.db_database)
|
|
return db
|
|
|
|
def __sql_execute(db, query, *args, **kwargs):
|
|
cursor = db.cursor(sql.cursors.DictCursor)
|
|
r = cursor.execute(query, *args, **kwargs)
|
|
if not query.startswith("SELECT"):
|
|
cursor.close()
|
|
return r
|
|
|
|
data = cursor.fetchall()
|
|
cursor.close()
|
|
return data
|
|
|
|
def initialize(db):
|
|
__sql_execute(db, "DROP TABLE IF EXISTS %sproducts;" % config.db_prefix)
|
|
__sql_execute(db, "DROP TABLE IF EXISTS %srestocks;" % config.db_prefix)
|
|
__sql_execute(db, "DROP TABLE IF EXISTS %stopups;" % config.db_prefix)
|
|
__sql_execute(db, "DROP TABLE IF EXISTS %sfinancing;" % config.db_prefix)
|
|
__sql_execute(db, "DROP TABLE IF EXISTS %spurchases;" % config.db_prefix)
|
|
|
|
__sql_execute(db, """CREATE TABLE %sproducts (
|
|
id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
|
|
name VARCHAR(40) NOT NULL,
|
|
code VARCHAR(100) NOT NULL,
|
|
cost INT(11) NOT NULL,
|
|
value INT(11) NOT NULL);""" % config.db_prefix)
|
|
|
|
__sql_execute(db, """CREATE TABLE %srestocks (
|
|
id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
|
|
product_id INT(11) NOT NULL,
|
|
title VARCHAR(100) NOT NULL,
|
|
datetime DATETIME NOT NULL,
|
|
count INT(11) NOT NULL);""" % config.db_prefix)
|
|
|
|
__sql_execute(db, """CREATE TABLE %sfinancing (
|
|
id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
|
|
title VARCHAR(100) NOT NULL,
|
|
datetime DATETIME NOT NULL,
|
|
value INT(11) NOT NULL);""" % config.db_prefix)
|
|
|
|
__sql_execute(db, """CREATE TABLE %stopups (
|
|
id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
|
|
value INT(11) NOT NULL,
|
|
datetime DATETIME NOT NULL,
|
|
user_id VARCHAR(40) NOT NULL);""" % config.db_prefix)
|
|
|
|
__sql_execute(db, """CREATE TABLE %spurchases (
|
|
id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
|
|
datetime DATETIME,
|
|
product_id INT(11) NOT NULL,
|
|
user_id VARCHAR(40) NOT NULL);""" % config.db_prefix)
|
|
|
|
def add_product(db, name, code, value, cost):
|
|
__sql_execute(db, "INSERT INTO %sproducts (name, code, value, cost) VALUES (%%s, %%s, %%s, %%s);" % config.db_prefix, (name, code, value, cost))
|
|
|
|
|
|
def add_restock(db, product_id, title, count):
|
|
__sql_execute(db, "INSERT INTO %srestocks (product_id, title, count, datetime) VALUES (%%s, %%s, %%s, NOW());" % config.db_prefix, (product_id, title, count))
|
|
|
|
def add_financing(db, title, value):
|
|
__sql_execute(db, "INSERT INTO %sfinancing (title, value, datetime) VALUES (%%s, %%s, NOW());" % config.db_prefix, (title, value))
|
|
|
|
def add_topup(db, user_id, value):
|
|
__sql_execute(db, "INSERT INTO %stopups (value, user_id, datetime) VALUES (%%s, %%s, NOW());" % config.db_prefix, (value, user_id))
|
|
|
|
def add_purchase(db, user_id, product_id, code=None):
|
|
if code:
|
|
product_id = __sql_execute(db, "SELECT id FROM %sproducts WHERE code=%%s" %
|
|
config.db_prefix, (code,))[0]['id']
|
|
__sql_execute(db, "INSERT INTO %spurchases (product_id, user_id, datetime) VALUES (%%s, %%s, NOW());" % config.db_prefix, (product_id, user_id))
|
|
|
|
def print_info(db):
|
|
f = get_financing_data(db)
|
|
u = get_users_data(db)
|
|
s = get_summary(db)
|
|
r = get_restocks_data(db)
|
|
p = get_products_data(db)
|
|
|
|
print " FINANCING"
|
|
print "-" * 76
|
|
print "% 40s | % 20s | % 10s" % ("title", "date", "value")
|
|
print "-" * 76
|
|
for financing in f["financings"]:
|
|
print "% 40s | % 20s | % 10.2f" % (financing["title"], financing["datetime"].strftime("%H:%M %m/%d/%y"), financing["value"]/100.0)
|
|
print "-" * 76
|
|
print " " * (63 - len("total")) + "total | " + "% 10.2f" % (f["financings_amount"]/100.0)
|
|
print "-" * 76
|
|
|
|
|
|
print "\n\n USERS"
|
|
print "-" * 76
|
|
print "% 63s | % 10s" % ("user id", "balance")
|
|
print "-" * 76
|
|
for user_id, balance in u["balance"].iteritems():
|
|
print "% 63s | % 10.2f" % (user_id, balance/100.0)
|
|
print "-" * 76
|
|
|
|
print "\n\n PRODUCTS"
|
|
print "-" * 76
|
|
print "% 24s | % 10s | % 10s | % 10s | % 10s" % ("name", "sold", "in stock", "revenue", "profit")
|
|
print "-" * 76
|
|
for product_id, product in p["products"].iteritems():
|
|
print "% 24s | % 10i | % 10i | % 10.2f | % 10.2f" % (product["name"], product["sold"], product["available"], product["revenue"]/100.0, product["profit"]/100.0)
|
|
print "-" * 76
|
|
|
|
print "\n\n RESTOCKS"
|
|
print "-" * 76
|
|
print "% 21s | % 15s | % 15s | % 6s | % 6s" % ("title", "date", "product", "count", "cost")
|
|
print "-" * 76
|
|
for restock in r["restocks"]:
|
|
print "% 21s | % 15s | % 15s | % 6i | % 6.2f" % (restock["title"], restock["datetime"].strftime("%H:%M %m/%d/%y"), restock["name"], restock["count"], restock["count"] * restock["cost"]/100.0)
|
|
print "-" * 76
|
|
|
|
print "\n\n"
|
|
print "Revenue (without financing): %.2f" % (s["revenue"]/100.0)
|
|
print "Revenue (with financing): %.2f" % (s["revenue_financing"]/100.0)
|
|
print "Expenses : %.2f" % (s["expenses"]/100.0)
|
|
print "Profit : %.2f" % (s["profit"]/100.0)
|
|
print "Vault : %.2f" % (s["real_money"]/100.0)
|
|
|
|
def get_financing_data(db):
|
|
financings = __sql_execute(db, "SELECT title, datetime, value from %sfinancing;" % config.db_prefix)
|
|
financings_amount = 0
|
|
for financing in financings:
|
|
financings_amount += financing["value"]
|
|
|
|
return {"financings": financings, "financings_amount": financings_amount}
|
|
|
|
def get_restocks_data(db):
|
|
restocks = __sql_execute(db, "SELECT %srestocks.product_id, %srestocks.title, %srestocks.datetime, %srestocks.count, %sproducts.cost, %sproducts.name FROM %srestocks LEFT JOIN %sproducts on %srestocks.product_id = %sproducts.id;" % (config.db_prefix, config.db_prefix, config.db_prefix, config.db_prefix, config.db_prefix, config.db_prefix, config.db_prefix, config.db_prefix, config.db_prefix, config.db_prefix))
|
|
return {"restocks": restocks}
|
|
|
|
def get_products_data(db):
|
|
products = __sql_execute(db, "SELECT id, name, cost, value, code FROM %sproducts;" % config.db_prefix)
|
|
purchases = __sql_execute(db, "SELECT COUNT(id), product_id FROM %spurchases GROUP BY product_id;" % config.db_prefix)
|
|
purchases_dict = dict([(purchase["product_id"], purchase["COUNT(id)"]) for purchase in purchases])
|
|
restocks = get_restocks_data(db)["restocks"]
|
|
restocks_dict = {}
|
|
for restock in restocks:
|
|
if restock["product_id"] not in restocks_dict:
|
|
restocks_dict[restock["product_id"]] = 0
|
|
restocks_dict[restock["product_id"]] += restock["count"]
|
|
r = {}
|
|
|
|
for product in products:
|
|
p = {}
|
|
|
|
sold = 0
|
|
if product["id"] in purchases_dict:
|
|
sold += purchases_dict[product["id"]]
|
|
|
|
stocked = 0
|
|
if product["id"] in restocks_dict:
|
|
stocked = restocks_dict[product["id"]]
|
|
|
|
p["name"] = product["name"]
|
|
p["sold"] = sold
|
|
p["stocked"] = stocked
|
|
p["available"] = stocked - sold
|
|
p["value"] = product["value"]
|
|
p["cost"] = product["cost"]
|
|
p["revenue"] = product["value"] * sold
|
|
p["profit"] = p["revenue"] - product["cost"] * sold
|
|
p["code"] = product["code"]
|
|
r[product["id"]] = p
|
|
|
|
return {"products": r}
|
|
|
|
def get_summary(db):
|
|
u = get_users_data(db)
|
|
f = get_financing_data(db)
|
|
|
|
real_money = 0
|
|
for topup in u["topups"]:
|
|
real_money += topup["value"]
|
|
|
|
revenue = real_money
|
|
|
|
real_money += f["financings_amount"]
|
|
revenue_financing = real_money
|
|
|
|
restocks = get_restocks_data(db)["restocks"]
|
|
expenses = 0
|
|
for restock in restocks:
|
|
expenses += int(restock["count"]) * int (restock["cost"])
|
|
|
|
profit = revenue - expenses
|
|
real_money -= expenses
|
|
|
|
return {"revenue": revenue, "revenue_financing": revenue_financing, "expenses": expenses, "profit": profit, "real_money": real_money}
|
|
|
|
def get_users_data(db):
|
|
topups = [{"user_id": topup["user_id"], "value": int(topup["SUM(value)"])} for topup in __sql_execute(db, "SELECT user_id, SUM(value) FROM %stopups GROUP BY user_id" % config.db_prefix)]
|
|
users = dict([(topup["user_id"], topup["value"]) for topup in topups])
|
|
|
|
purchases = __sql_execute(db, "SELECT %spurchases.user_id, SUM(%sproducts.value) FROM %spurchases LEFT JOIN %sproducts on %spurchases.product_id = %sproducts.id GROUP BY %spurchases.user_id;" % (config.db_prefix, config.db_prefix, config.db_prefix, config.db_prefix, config.db_prefix, config.db_prefix, config.db_prefix))
|
|
|
|
for purchase in purchases:
|
|
if purchase["user_id"] not in users:
|
|
users[purchase["user_id"]] = 0
|
|
|
|
users[purchase["user_id"]] -= int(purchase['SUM(hf_products.value)'])
|
|
|
|
return {"balance":users, "topups": topups}
|
|
|