def get_all():
"""retrieve all clients info
"""
query = "SELECT * FROM clients;"
db_log.debug(query)
return pd.read_sql(query, conn)
def get_last_timestamp():
""" retrieve the timestamp of the last transaction (CALLED FROM THE CLIENT SIDE)
@return timestamp
"""
query = "SELECT currval(pg_get_serial_sequence('ledger', 'trx_id'));"
db_log.debug(query)
cur.execute(query)
return cur.fetchone()[0]
def get_banking_id(cid):
""" called at the client side, to retrieve the stored banking id in the contacts
@param cid: contact id
@return banking_id or the associated banking id for the given contact id
"""
query=sql.SQL("SELECT (bank_account_id) FROM contacts WHERE contact_id='{cid}' LIMIT 1;").\
format(cid=sql.Literal(cid))
db_log.debug(query)
return pd.read_sql(query, conn).ix[0]
def get_owner_goods(oid):
"""return the good assigned to the given owner id (oid)
@param oid: is the owner id
@return json dict of good's ids
"""
query = sql.SQL("SELECT (good_id) FROM owners WHERE owner_id={oid}").\
format(oid=sql.Literal(oid))
db_log.debug(query)
return pd.read_sql(query, conn).to_json()
def get_good_owner(gid):
"""return owner id (oid) for the given gid
@param gid: good
@return the owner id
"""
query = sql.SQL("SELECT (owner_id) FROM owners WHERE good_id={gid}").\
format(gid=sql.Literal(gid))
db_log.debug(query)
return pd.read_sql(query, conn).ix[0]
def get_credential(cid):
""" get the credential for the client with given cid(CALLED FROM SERVER SIDE),
or get the single row for client with cid=1 (CALLED FROM CLIENT SIDE)
@param cid: client id, or 1 (in case of call from client side for it's own credential)
"""
query=sql.SQL("SELECT * FROM credentials WHERE id={cid} LIMIT 1;)").\
format(cid=sql.Literal(cid))
db_log.debug(query)
ret = pd.read_sql(conn, query)
def new_cred(passcode, cred_id):
"""add client credentials returned from the server
@param cid: client id
"""
stat=sql.SQL("INSERT INTO credentials (passcode, cred_id) VALUES ({passcode}, {credid});").\
format(passcode=sql.Literal(passcode), \
credid=sql.Literal(cred_id))
db_log.debug(stat)
cur.execute(stat)
def get_client_id(bid):
""" retrieve the corresponding client_id of the given banking_id (bid) (called at the server side)
@param bid: banking id
@return cid: contact id
"""
query = sql.SQL("SELECT (client_id) FROM banking WHERE id={bid} LIMIT 1"
).format(bid=sql.Literal(bid))
db_log.debug(query)
return pd.read_sql(query, conn).ix[0]
def get_good(gid):
"""retrive good for the given goods id (gid)
@param gid: goods id
@return pandas data series of the corresponding row
"""
query = sql.SQL("SELECT * FROM goods WHERE id={gid};").\
format(gid=sql.Literal(gid))
db_log.debug(query)
return pd.read_sql(query, conn)
def get_balance_by_credid(cred_id):
""" get balance of client with given credential id
@param cred_id: client credential id
"""
query = sql.SQL(
"SELECT (b.balance) FROM banking as b JOIN WITH credentials AS c WHERE c.cred_id={credid} AND c.id==b.client_id;"
).format(credid=sql.Literal(cred_id))
db_log.debug(query)
cur.execute(query)
return cur.fetchone()[0]
def get_credid_with_gid(gid):
"""cross reference credential id, with good's id
@param gid: good's id
@return credential id credid
"""
query=sql.SQL("SELECT (C.cred_id) FROM credentials as c JOIN WITH goods AS g JOIN WITH owners as o WHERE g.id=={gid} AND o.owner_id==c.id LIMIT 1;").\
format(gid=sql.Literal(gid))
db_log.debug(query)
cur.execute(query)
return cur.fetchone()[0]
def get_password(cred_id):
""" get user's passcode for authentication
@param cred_id: credential id
@return list of the id, or empty list of doesn't exist
"""
query=sql.SQL("SELECT (passcode) FROM credentials WHERE cred_id={credid} LIMIT 1;").\
format(credid=sql.Literal(cred_id))
db_log.debug(query)
cur.execute(query)
return cur.fetchone()[0]
def get_id(cred_id):
""" get client id
@param cred_id: credential id
@return the id, or None if doesn't exist
"""
query=sql.SQL("SELECT id FROM credentials WHERE cred_id={credid} LIMIT 1;").\
format(credid=sql.Literal(cred_id))
db_log.debug(query)
cur.execute(query)
return cur.fetchone()[0]
def get_balance_by_cid(cid):
"""called at the server side to retrieve the account balance d of the given client_id (cid)
@param cid: client id
@return bid: banking id
"""
query = sql.SQL(
"SELECT (balance) FROM banking WHERE client_id={cid} LIMIT 1").format(
cid=sql.Literal(cid))
db_log.debug(query)
return pd.read_sql(query, conn).ix[0]
def get_commodity(gname, quality=0):
"""retrive good for the given goods constraints
@param gname: goods name
@param quality: retrieve goods with quality > given threshold
@return pandas data frame of the corresponding constrains
"""
query = sql.SQL("SELECT * FROM goods WHERE good_name={gname} AND good_quality>={gquality}").\
format(gname=sql.Literal(gname), \
quality=sql.Literal(gquality))
db_log.debug(query)
return pd.read_sql(query, conn)
def get(cid):
"""retrieve client into with given client id (cid)
@param cid: client id
@return tuple (id, name, join date)
"""
query = sql.SQL(
"SELECT (id, contact_name, client_join_dt) FROM clients WHERE id={cid};"
).format(cid=sql.Literal(cid))
db_log.debug(query)
cur.execute(query)
return cur.fetchone()
def get_transactions(st_dt, end_dt=dt.datetime.now()):
""" get the transactions within the given period exclusively
@param st_dt: the start datetime
@param end_dt: the end datetime
@return dataframe of the transactions
"""
stat = sql.SQL("SELECT * FROM ledger WHERE trx_dt>{st_dt} AND trx_dt<{end_dt};").\
format(st_dt=sql.Literal(st_dt), \
end_dt=sql.Literal(end_dt))
db_log.debug(stat)
return pd.read_sql(conn, stat)
def to_dollar(cid):
""" convert currency of the corresponding id to dollar ratio
for example if currency A = 2 dollars, then the conversion would be 0.5,
for another currency B = 0.5 dollar, then the conversion to dollar would be 2
such that for given cost of xA, would be 0.5x$.
@param cid is the id of the corresponding currency
@return transformation ratio to dollar
"""
query = sql.SQL("SELECT * FROM currency WHERE id=cid;").\
format(cid=sql.Literal(cid))
db_log.debug(query)
ratio = 1.0/pd.read_sql(query, conn)['currency_value'].ix[0]
return ratio
def insert_banking(cid, balance):
""" give the client with the given id (cid) banking account (CALLED AT SERVER SIDE)
@param cid: client id
@param balance: client account balance
"""
stat=sql.SQL("INSERT INTO banking (client_id, balance, balance_dt) VALUES ({cid}, {balance}, {dt});"). \
format(cid=sql.Literal(cid), \
balance=sql.Literal(balance), \
dt=sql.Literal(dt.datetime.now().strftime(TIMESTAMP_FORMAT)))
db_log.debug(stat)
cur.execute(stat)
stat="SELECT currval(pg_get_serial_sequence('banking', 'id'));"
db_log.debug(stat)
cur.execute(stat);
return cur.fetchone()[0]
def register(cid):
"""register new client credentials with given cid (CALLED FROM SERVER SIDE)
@param cid: client id
@return a tuple (cred_id, passcode)
"""
cred_id = rand.random() * MAX_CRED_ID
passcode=''.join(rand.choice(string.ascii_uppercase+\
string.ascii_lowercase+string.digits)\
for _ in range(9))
stat=sql.SQL("INSERT INTO credentials (id, passcode, cred_id) VALUES ({cid}, {passcode}, {credid});").\
format(cid=sql.Literal(cid), \
passcode=sql.Literal(passcode), \
credid=sql.Literal(cred_id))
db_log.debug(stat)
cur.execute(stat)
return (cred_id, passcode)
def add_good(gname, gquality, gcost, gcid=1):
""" INSERT new good into the goods table
@param gname: good name
@param gquality: good quality
@param gcost: good cost
@param gcid: good currency id
"""
stat=sql.SQL("INSERT INTO goods (good_name, good_quality, good_cost, good_currency_id) VALUES ({gname}, {gquality}, {gcost}, {gcid});").\
format(gname=sql.Literal(gname), \
gquality=sql.Literal(gquality), \
gcost=sql.Literal(gcost), \
gcid=sql.Literal(gcid))
db_log.debug(stat)
cur.execute(stat)
stat="SELECT currval(pg_get_serial_sequence('goods', 'id'));"
cur.execute(stat)
db_log.debug(stat)
return cur.fetchone()[0]
def get_all():
query = "SELECT * FROM credentials;"
db_log.debug(query)
ret = pd.read_sql(conn, query)
return ret
def get_all():
query = "SELECT * FROM contacts;"
db_log.debug(query)
return pd.read_sql(query, conn)
