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)