def convert():
list_voices = []
try:
database_connection = DbConnection()
with database_connection:
query_voices = database_connection.query(Voice.UNCONVERTED_FILES)
for voice in query_voices:
list_voices.append(Voice(id_num=voice[0], voice_file=voice[1], email=voice[2],
name=voice[3], tittle=voice[4]))
if list_voices.__len__() != 0:
database_connection.update(Voice.create_update_converting_sql(list_voices))
except:
print('error en la base de datos')
for voice in list_voices:
file_mp3 = voice.voice_file[:-3]
if voice.voice_file[-3:] != 'mp3':
try:
media_in = Settings.MEDIA_DIR + voice.voice_file
path_out = 'converted/' + file_mp3 + 'mp3'
media_out = Settings.MEDIA_DIR + path_out
my_thread = Thread(target=ffmpeg, args=[media_in, media_out, voice.id_num, path_out,
voice.email, voice.tittle, voice.name])
my_thread.start()
my_thread.join(60)
except OSError as e:
print('error de os')
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 add_companies_to_portfolio(portfolio, list_company, db=None):
"""
Add list of company to portfolio
:param portfolio: id or name of portfolio
:type portfolio: int|string
:param list_company: list contains symbol of company
:type list_company: list[str]
:param db: if we have already connexion in other function who cal this function
:type db: DbConnection
:return: nb added company to portfolio
:rtype: int
"""
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)
# add each company to portfolio
query = """INSERT INTO company_portfolio (id_portfolio, symbol_company, is_activate_in_portfolio)
VALUES (%(id_portfolio)s, %(symbol_company)s, 1) ON DUPLICATE KEY UPDATE is_activate_in_portfolio = 1"""
nb_company_add = 0
for company in list_company:
params = {'id_portfolio': portfolio, 'symbol_company': company}
nb_company_add += db.modified_db(query, params)
return nb_company_add
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_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 __init__(self):
self.__str__ = "Trader"
self.email = ""
self.company = ""
self.db = DbConnection('market',
'stocks') # create database connection object
self.menu_options = [
x for x in dir(self) if x.startswith("m_")
] # create options menu for user using class attributes
while True:
self._menu()
# get input from user prompt and call the appropriate function name
try:
option_num = int(
input(
"Enter option number and press ENTER, or 0 to quit: "))
if option_num == 0:
print("Good bye!")
return
try:
func = getattr(self, self.menu[option_num])
print(func())
except AttributeError:
print(
"Unable to retrieve function name %s from menu option"
% self.menu[option_num])
except:
print("Invalid option")
class ConverterFactory:
def __init__(self, fileName):
self.fileName = fileName
Dbconnection = DbConnection(self.fileName)
self.connection = Dbconnection.connectDb()
self.db = DbConnection(self.fileName)
Dbinfoset = Dbinfo(self.connection)
self.rules = Dbinfoset.ctcaeRuleTable() # store the ctcae rule information in this object
self.para = Dbinfoset.ctcaeParaTable() # store the ctcae parameter information in this object
self.units = Dbinfoset.ctcaeUnitTable() # store the ctcae units information in this object
self.alias = Dbinfoset.ctcaeAliaTable() # store the ctcae alias information in this object
self.unittrans = Dbinfoset.ctcaeUnitTransfer() # store the ctcae mu information in this object
def ConverterLine(self):
AeCompare = AeCompar(self.fileName)
AeTestlist = AeCompare.aeReal()
TestConvertlist = []
for item in AeTestlist:
print(item)
subj_3d_id = item[0]
# aespid = item[1]
aeName = item[2]
labstartdate = item[6]
labenddate = item[7]
aeConverter = AeConverter(self.connection, subj_3d_id, labstartdate, labenddate, self.rules, self.para, self.alias, self.units, self.unittrans, aeName )
# dbConnection, subj_3d_id, startdate, enddate, rules, para, alias, units, unittrans, aeName)
valuedic = aeConverter.lrInfo()
if valuedic: # if the valuedic is not None
transpre = aeConverter.transPrepare()
if transpre: # if the list that contains rules to be evaluated is not None
gradelist = aeConverter.gradeCheker()
itemlist = [item[0], item[1], item[2],item[3], item[4], item[5], gradelist]
TestConvertlist.append(itemlist)
if TestConvertlist:
sqlstring = "delete from cc_ae_grade "
self.db.execute(sqlstring)
return TestConvertlist
def home():
# connection = None
# try:
connection = DbConnection("mysql-app", "usuarios")
res = jsonify(
connection.query(
"SELECT id_user, usuario, nombre, apellido FROM login;"))
res.status_code = 200
return res
def run(self, username, password, server, port, powerTopic, energyTopic,
deviceName):
dbConnection = DbConnection()
self.dbIdTestDevice = dbConnection.findDataloggerDbIdByName(deviceName)
self.logger.info("Device dbId %s" % self.dbIdTestDevice)
dbConnection.close()
self.superRun(username, password, server, port, powerTopic)
self.subscribe(energyTopic, 0)
self.energyTopic = energyTopic
def insert_transaction_to_db(id_portfolio,
symbol_company,
quantity,
value_current,
transaction_date,
id_simulation=None,
db=None):
"""
Insert transaction link to portfolio
:param id_portfolio: id of portfolio
:type id_portfolio: int
:param symbol_company: symbol of a company
:type symbol_company: str
:param quantity:
:type quantity: int
:param value_current:
:type value_current: float
:param transaction_date:
:type transaction_date: datetime
:param id_simulation: if transaction is during simulation
:type id_simulation: int
:param db: if we have already connexion in other function who cal this function
:type db: DbConnection
:return: number row affected
:rtype: int
"""
if not id_portfolio:
raise ValueError(
"Need to id_portfolio valid to create transaction. id_portfolio = %s"
% id_portfolio)
if not symbol_company:
raise ValueError(
"Need to symbol_company valid to create transaction. symbol_company = %s"
% symbol_company)
if not db or type(db) is not DbConnection:
db = DbConnection(HOST, USER, PASSWORD, DATABASE)
query = """INSERT INTO transaction (id_portfolio, id_symbol, quantity, value_current,
transaction_date, id_simulation)
VALUES (%(id_portfolio)s, %(symbol_company)s, %(quantity)s, %(value_current)s, %(transaction_date)s,
%(id_simulation)s)
ON DUPLICATE KEY UPDATE id_symbol = %(symbol_company)s, quantity = %(quantity)s,
value_current = %(value_current)s, transaction_date = %(transaction_date)s,
id_simulation = %(id_simulation)s)"""
params = {
'id_portfolio': id_portfolio,
'symbol_company': symbol_company,
'quantity': quantity,
'value_current': value_current,
'transaction_date': transaction_date,
'id_simulation': id_simulation
}
return db.modified_db(query, params)
def ffmpeg(media_in, media_out, id_num, path_out, email, tittle, name):
output = subprocess.call(['ffmpeg', '-i', media_in,
media_out, '-y'])
if output < 0:
print('error en conversion')
else:
try:
database_connection = DbConnection()
with database_connection:
database_connection.update(Voice.create_update_converted_sql(id_num, path_out))
Email.send_email(email=email, tittle=tittle, name=name)
except:
print('Error actualizando')
class CapstoneTestCase(unittest.TestCase):
def setUp(self):
self.db = DbConnection("foo_db", "foo_coll")
def test_chr2hex_encode(self):
self.assertEqual(chr2hex.encode("snhucapstone"),
"736E687563617073746F6E65")
def test_chr2hex_decode(self):
self.assertEqual(chr2hex.decode("736E687563617073746F6E65"),
"snhucapstone")
def test_request_prompt(self):
request_prompt.input = mock.Mock()
request_prompt.re = mock.Mock()
request_prompt.return_value = "m"
self.assertEqual("m", request_prompt.return_value)
request_prompt.return_value = "t"
self.assertEqual("t", request_prompt.return_value)
def test_create_document(self):
self.db.collection.insert_one = mock.Mock()
result = self.db.create_document('{"FOO": 1000}')
self.assertEqual(self.db.create_document('{"FOO": 1000}'), result)
def test_update_volume(self):
self.db.collection.update_one = mock.Mock()
self.db.collection.find_one = mock.Mock()
self.db.collection.find_one.return_value = {"FOO: 2000"}
self.assertEqual(self.db.update_volume("FOO", 2000),
self.db.collection.find_one.return_value)
def test_delete_document_by_ticker(self):
self.db.collection.find_one_and_delete = mock.Mock()
self.db.collection.find_one_and_delete.return_value = dict(
{"FOO": 1000})
self.assertEqual(self.db.collection.find_one_and_delete.return_value,
self.db.delete_document("FOO"))
def test_get_industry_tickers(self):
self.db.collection.find = mock.Mock()
self.db.collection.find.return_value = [{
"Industry": "Semiconductors",
"Ticker": "AMD"
}, {
"Industry": "Semiconductors",
"Ticker": "INTL"
}]
self.assertEqual(self.db.get_industry_tickers("Semiconductors"),
['AMD', 'INTL'])
def __init__(self, fileName):
self.fileName = fileName
Dbconnection = DbConnection(self.fileName)
self.connection = Dbconnection.connectDb()
self.db = DbConnection(self.fileName)
Dbinfoset = Dbinfo(self.connection)
self.rules = Dbinfoset.ctcaeRuleTable() # store the ctcae rule information in this object
self.para = Dbinfoset.ctcaeParaTable() # store the ctcae parameter information in this object
self.units = Dbinfoset.ctcaeUnitTable() # store the ctcae units information in this object
self.alias = Dbinfoset.ctcaeAliaTable() # store the ctcae alias information in this object
self.unittrans = Dbinfoset.ctcaeUnitTransfer() # store the ctcae mu information in this object
def process_api_login():
try:
with DbConnection(decrypt_dict_vals(dbconfig, {'password'})) as cur:
creds = request.get_json(force=True)
if authenticate(creds['username'][0:150], creds['password'][0:150],
cur):
logger.info('authenticate succesful')
session['user'] = create_new_session(creds['username'][0:150],
cur)
return json.dumps({
'logged_in':
True,
'organizations':
session['user']['organizations']
})
else:
logger.warning('authentication failed.')
session['user'] = None
return json.dumps({'logged_in': False, 'organizations': [{}]})
#- return '{"logged_in":false}'
except Exception as err:
logger.error('process_api_login exception: {}, {}, {}'.format(
exc_info()[0],
exc_info()[1], err))
session['user'] = None
return json.dumps({'logged_in': False, 'organizations': [{}]})
def get_chart_list(system_uuid):
#TODO - verify that the user has the privliges to see charts for the
# grow system identified by <system_uuid>
#TODO - I bet this could moved to a decorator or hell put it in enforce_login!
logger.info('{}: api/get_chart_list/{}'.format(
session['user']['nick_name'], system_uuid))
try:
with DbConnection(decrypt_dict_vals(dbconfig, {'password'})) as cur:
sql = """select chart_config from grow_system inner join grow_system_devices on
grow_system.uuid = grow_system_devices.grow_system_uuid inner join
device on device.guid = grow_system_devices.device_uuid where
uuid = %s;"""
cur.execute(sql, (system_uuid, ))
assert (
cur.rowcount == 1
), 'No or more than one device found. Only one device was expected.'
return json.dumps({
'r':
True,
'chart_list': [{
'rel_url':
'/chart/{}/{}'.format(cl['vue_name'], system_uuid)
} for cl in cur.fetchone()[0]['chart_list']]
})
except:
logger.error('error {}, {}'.format(exc_info()[0], exc_info()[1]))
return json.dumps({'r': False, 'chart_list': [{}]})
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 process_login():
try:
with DbConnection(decrypt_dict_vals(dbconfig, {'password'})) as cur:
if authenticate(request.form['username'][0:150],
request.form['password'], cur):
logger.info('authenticate succesful')
session['user'] = create_new_session(
request.form['username'][0:150], cur)
return render_template(
'home.html',
devices=session['user']['devices'],
selected_device=session['user']['devices'][0],
chart_list=session['user']['chart_config']['chart_list'])
#- return render_template('home.html', chart_list=session['user']['chart_config']['chart_list'])
else:
logger.warning('authentication failed.')
session['user'] = None
flash('incorrect username or password')
return render_template('login.html')
except:
logger.error('process_login exception: {}, {}'.format(
exc_info()[0],
exc_info()[1]))
session['user'] = None
flash('system error F_PL')
return render_template('login.html')
def get_crops():
logger.info('{}: api/get_crops/'.format(session['user']['nick_name']))
try:
with DbConnection(decrypt_dict_vals(dbconfig, {'password'})) as cur:
sql = """select grow_batch_id, g.start_date, 'germination' as status, v.common_name, v.species from germination as g
inner join grow_batch as gb on g.grow_batch_id = gb.id
inner join seed_lot as sl on gb.seed_lot_id = sl.id
inner join variety as v on sl.variety_id = v.id
union
select 100, '20190620' as start_date, 'stage 2' as status, 'basil' as common_name, 'Genovese' as variety;"""
cur.execute(sql)
if cur.rowcount > 0:
crops = [{
'batch_id': c[0],
'start_date': c[1].strftime('%x'),
'status': c[2],
'name': c[3],
'variety': c[4]
} for c in cur.fetchall()]
else:
crops = [{}]
return json.dumps({'r': True, 'crops': crops})
except:
logger.error('get_crops exception: {}, {}'.format(
exc_info()[0],
exc_info()[1]))
return json.dumps({'server error': True})
def __init__(self, user_name):
self.nick_name = None
self.guid = None
self.django_username = None
self.text_number = None
with DbConnection(decrypt_dict_vals(dbconfig, {'password'})) as cur:
sql = """select person.nick_name, person.guid, person.django_username,
text_number
from person where person.django_username = %s;"""
cur.execute(sql, (user_name[0:150], ))
if cur.rowcount == 1:
record = cur.fetchone()
self.nick_name = record[0]
self.guid = record[1]
self.django_username = record[2]
self.text_number = record[3]
else:
logger.warn('no unique database record for {}'.format(
user_name[0:150]))
def setUpClass(cls):
ManagerDbTest.init_db_mysql_test(HOST, USER, PASSWORD, DATABASE)
cls.__db = DbConnection(HOST, USER, PASSWORD, DATABASE)
cls.__company = [{
'symbol': 'GOOGL',
'name': 'Alphabet Inc Class A',
'last_update_historic': None
}, {
'symbol': 'AMZN',
'name': 'Amazon.com Inc',
'last_update_historic': None
}, {
'symbol': 'AAPL',
'name': 'Apple Inc.',
'last_update_historic': None
}, {
'symbol': 'HRB',
'name': 'Block H&R',
'last_update_historic': None
}, {
'symbol': 'gps',
'name': 'Gap (The)',
'last_update_historic': None
}]
def get_data_json(system_uuid, start_date, end_date):
#TODO - verify that the user has the privliges to see the contents of the zip file.
logger.info('{}: api/get_data_json/{}/{}/{}'.format(
session['user']['nick_name'], system_uuid, start_date, end_date))
try:
# Get the fopd device UUID
with DbConnection(decrypt_dict_vals(dbconfig, {'password'})) as cur:
sql = """select device_uuid from grow_system as gs inner join
grow_system_devices as gsd on gs.uuid = gsd.grow_system_uuid where
gs.uuid = %s"""
cur.execute(sql, (system_uuid, ))
# Get the 1st device id returned from the grow system devices list
device_uuid = cur.fetchone()[0]
result = get_device_data_json(device_uuid, start_date, end_date,
session['user']['ct_offset'])
if result:
return result
else:
#TODO: Need a different error message here than the s3_error
return send_from_directory(path.join(app.root_path, 'static'),
's3_error.jpg',
mimetype='image/png')
except:
logger.error('in /api/get_data_json route: {}, {}'.format(
exc_info()[0],
exc_info()[1]))
return send_from_directory(path.join(app.root_path, 'static'),
's3_error.jpg',
mimetype='image/png')
def __init__(self):
self.bot = self.create_bot()
self.connection = DbConnection()
self.commands = {
'add': AddCommand(self.bot, self.connection),
'list': ListCommand(self.bot, self.connection),
'reset': ResetCommand(self.bot, self.connection),
'start': WelcomeCommand(self.bot, self.connection)
}
self.current_command = None
self.register_commands(self.bot)
def set_new_password_reset_code(self, rc):
#TODO: update the Person table to contain the password reset code and hte timestamp
# save the 6 digit number in the db as the reset code with a timeout of say 1 hour
with DbConnection(decrypt_dict_vals(dbconfig, {'password'})) as cur:
sql = """update person set password_reset_code = %s,
password_reset_code_create_time = now(),
password_reset_failed_tries = 0
where guid = %s;"""
cur.execute(sql, (rc, self.guid))
def get_device_data_json(device_uuid, start_date, end_date, utc_offset):
try:
q = """select ea.name as attribute, seo.units as units, seo.utc_timestamp + interval '%s' hour as sample_time, seo.measurement_value as value,
d.local_name as device_local_name, es.name as subject_name, esl.location_guid as subject_location_id
from environment_observation as eo
inner join scalar_environment_observation as seo on eo.id = seo.environment_observation_id
inner join environment_attribute as ea on eo.environment_attribute_id = ea.id
inner join environment_subject_location as esl on esl.guid = eo.environment_subject_location_guid
inner join environment_subject as es on esl.environment_subject_id = es.id
inner join device as d on d.guid = eo.participant_guid
where eo.participant_guid = %s and utc_timestamp >= timestamp %s and utc_timestamp < timestamp %s
order by seo.utc_timestamp desc;
"""
with DbConnection(decrypt_dict_vals(dbconfig, {'password'})) as cur:
# Note: We convert time filters from the user's local time to utc.
cur.execute(q, (utc_offset, device_uuid,
datetime.strptime(start_date, '%Y-%m-%d') -
timedelta(hours=utc_offset),
datetime.strptime(end_date, '%Y-%m-%d') +
timedelta(days=1) - timedelta(hours=utc_offset)))
rc = cur.rowcount
if rc == 0:
logger.warning('No device data available')
return json.dumps('No device data available')
obs_list = []
for record in cur:
#- obs = {'value_name': None}
obs = {'type': 'environment'}
obs['device_name'] = record[4]
obs['device_id'] = device_uuid
obs['subject'] = record[5]
obs['subject_location_id'] = record[6]
obs['attribute'] = record[0]
obs['value'] = record[3]
obs['units'] = record[1]
#- obs['ts'] = record[2].strftime('%c')
obs['ts'] = record[2].isoformat()
obs_list.append(obs)
return json.dumps(obs_list, indent=3)
except:
logger.error('in get_device_data_json: {}, {}'.format(
exc_info()[0],
exc_info()[1]))
return None
def check_that_unique_user_exists(user_name):
# See if the user name given matches anything in the database.
with DbConnection(decrypt_dict_vals(dbconfig, {'password'})) as cur:
sql = """select person.guid from person where person.django_username = %s;"""
cur.execute(sql, (user_name[0:150], ))
if cur.rowcount == 1:
return True
else:
return False
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 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 writeFile():
os.mkdir(dirNand + "NoCleanedFile")
for id in idUsers[0:70]:
i = 0
file = open(dirNand + "NoCleanedFile/" + str(id) + ".txt", "w+")
tweet = DbConnection.takeText(id)
while (i < len(tweet)):
text = tweet[i]['Tweet']['text']
newText = unicodedata.normalize('NFKD',
text).encode('ascii', 'ignore')
file.write(newText + "\n")
i = i + 1
file.close()
def get_device_data(out_fp, device_uuid, start_date, end_date, utc_offset):
try:
q = """select ea.name as attribute, seo.units as units, seo.utc_timestamp + interval '%s' hour as sample_time, seo.measurement_value as value
from environment_observation as eo
inner join scalar_environment_observation as seo on eo.id = seo.environment_observation_id
inner join environment_attribute as ea on eo.environment_attribute_id = ea.id
inner join environment_subject_location as esl on esl.guid = eo.environment_subject_location_guid
inner join environment_subject as es on esl.environment_subject_id = es.id
where participant_guid = %s and utc_timestamp >= timestamp %s and utc_timestamp < timestamp %s
order by seo.utc_timestamp desc;
"""
with DbConnection(decrypt_dict_vals(dbconfig, {'password'})) as cur:
# Note: We convert time filters from the user's local time to utc.
cur.execute(q, (utc_offset, device_uuid,
datetime.strptime(start_date, '%Y-%m-%d') -
timedelta(hours=utc_offset),
datetime.strptime(end_date, '%Y-%m-%d') +
timedelta(days=1) - timedelta(hours=utc_offset)))
rc = cur.rowcount
if rc == 0:
logger.warning('No device data available')
return False
csv_writer = csv.writer(out_fp,
delimiter=',',
quotechar='|',
quoting=csv.QUOTE_MINIMAL)
rc = 0
csv_writer.writerow(['attribute', 'unit', 'sample time', 'value'])
for record in cur:
csv_writer.writerow(record)
if rc == 0:
logger.info('writing first row {}'.format(
out_fp.getvalue()))
rc = rc + 1
out_fp.seek(0)
return True
except:
logger.error('in get_device_data: {}, {}'.format(
exc_info()[0],
exc_info()[1]))
return False
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 __init__(self):
"""Constructor"""
self.dbcon = DbConnection.get_con()
def __init__(self):
self.db = DbConnection()
class Database():
def __init__(self):
self.db = DbConnection()
"""Get Queries"""
def getById(self,id):
return self.db.readQuery("""select * from inventory where product_id = '{}'""".format(id))
def getNameAndSellingPrice(self,productId):
return self.db.readQuery("""select name, selling_price from inventory where product_id = '{}'""".format(productId))
def getByName(self,productName):
productNameLike = str(productName+"%")
return self.db.readQuery("""select * from inventory where name LIKE '{}'""".format(productNameLike))
def getBySupplier(self,supplierName):
supplierNameLike = str(supplierName+"%")
return self.db.readQuery("""select * from inventory where supplier LIKE '{}'""".format(supplierNameLike))
def getItemsByPrice(self, price):
return self.db.readQuery("""select * from inventory where selling_price = '{}'""".format(price))
def getItems(self):
return self.db.readQuery("""select * from inventory""")
def getAllProductIds(self):
return self.db.readQuery("""select product_id from inventory""")
def getItemsWithLowInventory(self):
return self.db.readQuery("""select * from inventory where amount_in_stock < 5""")
def getFromInventoryLogByProductId(self, productId):
return self.db.readQuery("""select * from inventory_log where product_id = '{}'""".format(productId))
def totalItemsSoldFromProductId(self,productId):
return self.db.readQuery("""select products_sold from inventory_log where product_id = '{}'""".format(productId))
def totalItemsBoughtFromProductId(self,productId):
return self.db.readQuery("""select products_recieved from inventory_log where product_id = '{}'""".format(productId))
def getTransactions(self):
return self.db.readQuery("""select * from transactions""")
def getUserLogs(self):
return self.db.readQuery("""select * from user_logging""")
"""User Queries"""
def getUser(self, username):
usernameLike = str(username+"%")
return self.db.readQuery("""select * from customer_emails where customer_name LIKE '{}'""".format(usernameLike))
def getEmailsInSystem(self):
return self.db.readQuery("""select * from customer_emails""")
def getPasswordForUser(self,username):
return self.db.readQuery("""select password from employee_credentials where username = '******'""".format(username))
def getManagerStatus(self):
username = session.get("current_user")
return self.db.readQuery("""select ismanager from employee_credentials where username = '******'""".format(username))
"""Insert Queries"""
def writeEmailToDatabase(self, customerName, customerEmail):
self.db.writeQuery("""insert into customer_emails values ('{}','{}')""".format(customerName,customerEmail))
def insertNewInventoryItem(self, name, productId, originalCost, sellPrice, supplier, productType, amountInStock):
self.db.writeQuery("""insert into inventory values ('{}','{}','{}','{}','{}','{}','{}')""".format(name,productId, originalCost, sellPrice, supplier, productType, amountInStock))
def insertIntoInventoryLog(self,productId, productsRecieved, productsSold):
self.db.writeQuery("""insert into inventory_log values ('{}','{}', '{}')""".format(productId, productsRecieved, productsSold))
def insertNewTransaction(self,transactionId,amountPaid,paymentType,itemsPurchased):
self.db.writeQuery("""insert into transactions values ('{}','{}','{}','{}')""".format(transactionId, amountPaid, paymentType, itemsPurchased))
def insertUserLog(self,userName, endpoint, time):
self.db.writeQuery("""insert into user_logging values ('{}', '{}', '{}')""".format(userName, endpoint, time))
"""Update Queries"""
def updateInventoryItem(self, name, productId, originalCost, sellPrice, supplier, productType, amountInStock):
self.db.writeQuery("""update inventory set name ='{}',
purchase_price = '{}', selling_price = '{}', supplier = '{}', product_type = '{}', amount_in_stock = '{}' where product_id='{}'""".format(name, originalCost, sellPrice, supplier, productType, amountInStock,productId))
def updateInventoryLog(self, productId,howManyPurchased):
inventoryLogGet = self.getFromInventoryLogByProductId(productId)
amountSold = inventoryLogGet[0][2] + int(howManyPurchased)
self.db.writeQuery("""update inventory_log set products_sold = '{}' where product_id = '{}'""".format(amountSold, productId))
def updateInventoryLogItemsPurchased(self, productId,howManyRecieved):
inventoryLogGet = self.getFromInventoryLogByProductId(productId)
amountBought = inventoryLogGet[0][1] + int(howManyRecieved)
self.db.writeQuery("""update inventory_log set products_recieved = '{}' where product_id = '{}'""".format(amountBought, productId))
def orderNewProducts(self,productId, howManyPurchased):
inventoryGet = self.getById(productId)
amountInStock = inventoryGet[0][6] + int(howManyPurchased)
self.db.writeQuery("""update inventory set amount_in_stock = '{}' where product_id = '{}'""".format(amountInStock, productId))
def createSale(self,productId, newPrice):
self.db.writeQuery("""update inventory set selling_price = '{}' where product_id = '{}'""".format(newPrice, productId))
def sellItem(self,productId,howManyPurchased):
item = self.getById(productId)
howMany = item[0][6] - int(howManyPurchased)
self.db.writeQuery("""update inventory set amount_in_stock = '{}' where product_id = '{}'""".format(howMany, productId))
def __init__(self):
"""
Get a DB connection.
"""
self.dbcon = DbConnection.get_con()
def __init__(self):
'''
Constructor
'''
self.dbcon = DbConnection.get_con()
media = db_connection.graph.cypher.execute(
"match (n:Envio) where n.mes= {a} "
"return avg(n.peso) as mediaPeso, avg(n.volumen) as mediaVolumen, count(n) as cuenta "
, a = mes)
return media
@staticmethod
def getResumenOcupacion(tipo):
db_connection = DbConnection()
media = db_connection.graph.cypher.execute(
"match (n:Locomotora)-[r]-(e:Estacion) where n.tipo = {a} "
"return count(r) as numero", a = tipo)
return media
if __name__ == '__main__':
DbConnection.deleteAllGraph()
DbConnection.init()
Estacion.crearEstaciones()
Estacion.crearTramos()
#Estacion.getVias("Madrid")
madrid = Estacion.getEstacionFromNombre('Madrid')
Locomotora.createLocomotoras()
Locomotora.asociarLocomotoras(madrid)
Envio.createEnvios()
Envio.asociarEnvios()
print "Camino economico Madrid - LaMancha:"
print Estacion.getRutaOptima('Madrid', 'LaMancha', 'economico')
