def get_transaction_simulation(id_simulation, db=None): """ Get transaction of simulation as id of simulation :param id_simulation: id of simulation :type id_simulation: int :param db: if we have already connexion in other function who cal this function :type db: DbConnection :return: transaction of simulation :rtype: list[dict] """ if not id_simulation: raise ValueError('ID simulation is None.') if not db or type(db) is not DbConnection: db = DbConnection(HOST, USER, PASSWORD, DATABASE) query = """SELECT id_transaction, id_portfolio, id_symbol, quantity, value_current, transaction_date FROM "transaction" WHERE id_simulation = %(id_simulation)s""" res = db.select_in_db(query, {'id_simulation': id_simulation}) return_value = [] for id_transaction, id_portfolio, id_symbol, quantity, value_current, transaction_date in res: return_value.append({ 'id_transaction': id_transaction, 'id_portfolio': id_portfolio, 'id_symbol': id_symbol, 'quantity': quantity, 'value_current': value_current, 'transaction_date': transaction_date }) return return_value
def get_info_simulation(id_simulation, db=None): """ Get information of simulation as id of simulation :param id_simulation: id of simulation :type id_simulation: int :param db: if we have already connexion in other function who cal this function :type db: DbConnection :return: information on simulation :rtype: list[dict] """ if not id_simulation: raise ValueError('ID simulation is None.') if not db or type(db) is not DbConnection: db = DbConnection(HOST, USER, PASSWORD, DATABASE) query = """SELECT id_portfolio, parameters, results FROM simulation WHERE id_simulation = %(id_simulation)s""" res = db.select_in_db(query, {'id_simulation': id_simulation}) return_value = [] for id_portfolio, parameters, results in res: return_value.append({ 'id_portfolio': id_portfolio, 'parameters': parameters, 'results': results }) return return_value
def get_companies_to_portfolio(portfolio, db=None): """ Get all company associate to a portfolio :param portfolio: id or name of portfolio :type portfolio: int|string :param db: if we have already connexion in other function who cal this function :type db: DbConnection :return: list company name and symbol :rtype: list[dict] """ if not db or not isinstance(db, DbConnection): db = DbConnection(HOST, USER, PASSWORD, DATABASE) # check if id or name of portfolio, if name => get id if isinstance(portfolio, str): if not portfolio.isdigit(): id_portfolio = get_id_portfolio(portfolio) portfolio = id_portfolio[0].get('id_portfolio')[0] # check if portfolio number is valid if not portfolio: raise ValueError( "Need to portfolio valid to add company to portfolio. portfolio = %s" % portfolio) query = """SELECT c.name, cp.symbol_company FROM company_portfolio cp JOIN company c ON c.symbol = cp.symbol_company WHERE cp.id_portfolio = %(portfolio)s AND cp.is_activate_in_portfolio = 1""" res = db.select_in_db(query, {'portfolio': portfolio}) return_value = [] for name, symbol in res: return_value.append({'name': name, 'symbol': symbol}) return return_value
def get_all_portfolio_info(db=None): """ Get all portfolio with name and id :param db: if we have already connexion in other function who cal this function :type db: DbConnection :return: list[dict] """ if not db or type(db) is not DbConnection: db = DbConnection(HOST, USER, PASSWORD, DATABASE) query = """SELECT id_portfolio, name FROM portfolio""" res = db.select_in_db(query) return_value = [] for id_portfolio, name in res: return_value.append({'id_portfolio': id_portfolio, 'name': name}) return return_value
def get_name_portfolio_name(id_portfolio, db=None): """ Get name of portfolio as id of portfolio :param id_portfolio: id of portfolio :type id_portfolio: int :param db: if we have already connexion in other function who cal this function :type db: DbConnection :return: result in list of dict :rtype: list[dict] """ if not id_portfolio: raise ValueError('ID portfolio is None.') if not db or type(db) is not DbConnection: db = DbConnection(HOST, USER, PASSWORD, DATABASE) query = """SELECT name FROM portfolio WHERE id_portfolio = %(id)s""" res = db.select_in_db(query, {'id': id_portfolio}) return_value = [] for name in res: return_value.append({'name': name}) return return_value
def get_id_portfolio(name, db=None): """ Get name of portfolio as name of portfolio :param name: name of portfolio :type name: str :param db: if we have already connexion in other function who cal this function :type db: DbConnection :return: result in list of dict :rtype: list[dict] """ if not name: raise ValueError('Name portfolio is None.') if not db or type(db) is not DbConnection: db = DbConnection(HOST, USER, PASSWORD, DATABASE) query = """SELECT id_portfolio FROM portfolio WHERE name = %(name)s""" res = db.select_in_db(query, {'name': name}) return_value = [] for id_portfolio in res: return_value.append({'id_portfolio': id_portfolio}) return return_value
def create_portfolio(name=None, db=None): """ Create new portfolio :param name: name of portfolio :type name: str :param db: if we have already connexion in other function who cal this function :type db: DbConnection :return: id of portfolio :rtype int """ if not db or type(db) is not DbConnection: db = DbConnection(HOST, USER, PASSWORD, DATABASE) if not name: query = """SELECT (CASE COUNT(id_portfolio) WHEN 0 THEN 1 ELSE COUNT(id_portfolio)+1 END) AS new_id FROM portfolio""" res = db.select_in_db(query) name = 'new_portfolio' + str(res[0][0]) query = """INSERT INTO portfolio (name) VALUES (%(name)s) ON DUPLICATE KEY UPDATE name = name""" db.modified_db(query, {'name': name}) # get id portfolio return get_id_portfolio(name, db)
def get_transaction_portfolio(id_portfolio, symbol_company=None, db=None): """ Get all transaction of portfolio and if we want of a company :param id_portfolio: id of portfolio :type id_portfolio: int :param symbol_company: symbol of company in stock market :type symbol_company: str :param db: if we have already connexion in other function who cal this function :type db: DbConnection :return: result in list of dict :rtype: list[dict] """ if not id_portfolio: raise ValueError('ID portfolio is None.') if not db or type(db) is not DbConnection: db = DbConnection(HOST, USER, PASSWORD, DATABASE) query = """SELECT id_transaction, id_symbol, quantity, value_current, transaction_date, id_simulation FROM transaction WHERE id_portfolio = %(id_portfolio)s""" params = {'id_portfolio': id_portfolio} if symbol_company: query += "AND id_symbol = %(id_symbol)s" params['symbol_company'] = symbol_company res = db.select_in_db(query, params) return_value = [] for id_transaction, id_symbol, quantity, value_current, transaction_date, id_simulation in res: return_value.append({ 'id_transaction': id_transaction, 'id_symbol': id_symbol, 'quantity': quantity, 'value_current': value_current, 'transaction_date': transaction_date, 'id_simulation': id_simulation }) return return_value