def cadastrar_grupo(nome: str, integrantes: [str], dono: Usuario) -> bool:
integrantes.append(dono.nome)
conexao = __conectar()
dados = __select(conexao, "nm_grupo", "grupos")
if nome in dados:
__desconectar(conexao)
return False
else:
__insert(conexao, "grupos(nm_grupo)", nome)
codigo_grupo = __select(conexao, "cd_grupo", "grupos", f"nm_grupo='{nome}'")
if not codigo_grupo:
__desconectar(conexao, rollback=True)
raise MySQL.Error("insert nao executado")
for nome_integrante in integrantes:
check = __select(conexao, "*", "usuarios", f"nm_usuario='{nome_integrante}'")
if check:
codigo_integrante = __select(conexao, "cd_usuario", "usuarios", f"nm_usuario='{nome_integrante}'")
__insert(conexao, "membros_grupo(fk_usuario, fk_grupo)", (codigo_integrante[0], codigo_grupo[0]))
check = __select(conexao, "*", "membros_grupo",
f"fk_usuario={codigo_integrante[0]} and fk_grupo={codigo_grupo[0]}")
if not check:
__desconectar(conexao, rollback=True)
raise MySQL.Error("insert nao executado")
else:
__desconectar(conexao, rollback=True)
return False
__desconectar(conexao)
return True
def arquivar_mensagens_grupo(mensagem: MensagemGrupo, usuarios_nao_disponiveis: [Usuario]):
conexao = __conectar()
codigo_grupo = __select(conexao, "cd_grupo", "grupos", f"nm_grupo='{mensagem.grupo}'")
__insert(conexao, "mensagens_grupo (fk_usuario, fk_grupo, ds_mensagem)",
(mensagem.remetente.codigo, codigo_grupo[0], mensagem.mensagem))
codigo_mensagem = __select(conexao, "cd_mensagem_grupo", "mensagens_grupo",
f"fk_usuario={mensagem.remetente.codigo} and "
f"fk_grupo={codigo_grupo[0]} and "
f"ds_mensagem='{mensagem.mensagem}'")
if codigo_mensagem:
for usuario_nao_disponivel in usuarios_nao_disponiveis:
codigo_usuario_nao_disponivel = __select(conexao, "cd_usuario", "usuarios",
f"nm_usuario='{usuario_nao_disponivel.nome}'")
__insert(conexao, "mensagem_grupo_recebido (fk_mensagem_grupo, fk_usuario, bl_recebido)",
(codigo_mensagem[0], codigo_usuario_nao_disponivel[0], 0))
check = __select(conexao, "*", "mensagem_grupo_recebido",
f"fk_mensagem_grupo={codigo_mensagem[0]} and"
f" fk_usuario={codigo_usuario_nao_disponivel[0]}", desempacotar=False)
if not check:
__desconectar(conexao, rollback=True)
raise MySQL.Error("insert nao executado")
else:
__desconectar(conexao, rollback=True)
raise MySQL.Error("insert nao executado")
__desconectar(conexao)
def getForgiven(account_id, start_date, end_date):
cn = connection()
cur = cn.cursor()
fileDate = start_date
csvFileName = 'forgiven.csv'
print 'loading forgiven\n'
query = """select fh.remote_Note_Id noteID, fh.time, fh.oldState, fh.newState
from forgiven_history fh
where fh.account_id= """+account_id+"""
and time >='"""+start_date+"""'
and time <'"""+end_date+"""' + interval 1 day
order by 1;"""
print(query)
cur.execute(query)
results = cur.fetchall()
with open(csvFileName, 'w') as output:
a = csv.writer(output, delimiter = ',')
a.writerows(results)
if cur.rowcount != 0:
print('writing results to ' + csvFileName)
else:
print(str(connector.Error('Could not write results to .csv file')))
cur.close()
cn.close()
def __enter__(self):
if self.__connection is None:
self.__connection = mariadb.connect(
user=self.__connection_data.user,
password=self.__connection_data.password,
database=self.__connection_data.db)
else:
warnings.warn("Connection is already active, can't have two at a the same time")
raise mariadb.Error(msg="Previous connection not closed")
return self.__connection
def getAgg(account_id, start_date, end_date):
cn = connection()
cur = cn.cursor()
fileDate = start_date
csvFileName = 'agg.csv'
print 'loading agg\n'
query = """select agg.aggDate
, v.remote_vehicle_id vehicle_id
, v.name vehicle_name
, v.group_fullname
, dr.remote_driver_id driver_id
, dr.empid
, dr.name driver_name
, (agg.mpgOdometer1+agg.mpgOdometer2+agg.mpgOdometer3)/100 as mpgMiles
, (agg.mpgGal1+agg.mpgGal2+agg.mpgGal3)/1000 mpgGallons
, round(10*(mpgOdometer1+mpgOdometer2+mpgOdometer3)/(mpgGal1+mpgGal2+mpgGal3),2) as mpg
, driveTime as driveTime
, idleLo as idleLo
, idleHi as idleHi
, (odometer1+odometer2+odometer3+odometer4+odometer5)/100 as milesDriven
from agg
join vehicle v
on agg.vehicle_id = v.vehicle_Id
join driver dr
on agg.driver_id = dr.driver_Id
where agg.account_id= """+account_id+"""
and agg.aggDate >='"""+start_date+"""'
and agg.aggDate < '"""+end_date+"""' + interval 1 day
and agg.odometer6 > 0
order by 1;"""
print(query)
cur.execute(query)
results = cur.fetchall()
with open(csvFileName, 'w') as output:
a = csv.writer(output, delimiter = ',')
a.writerows(results)
if cur.rowcount != 0:
print('writing results to ' + csvFileName)
else:
print(str(connector.Error('Could not write results to .csv file')))
cur.close()
cn.close()
def getViolations(account_id, start_date, end_date):
cn = connection()
cur = cn.cursor()
fileDate = start_date
csvFileName = 'violations.csv'
print 'loading notes\n'
query = """select *
from (
select convert_TZ(n.time, 'GMT', dr.tzName) date
, n.noteID
, g.remote_group_id group_id
, g.name group_name
, dr.group_fullname
, dr.remote_driver_id driver_id
, dr.empid
, dr.name driver_name
, v.remote_vehicle_id vehicle_id
, v.name vehicle_name
, concat(
nn.name
, case when n.type in (2,171) then '- ' else '' end
, ifnull(case when n.type in (2,171) then getStyleType(n.type,n.deltaX,n.deltaY,n.deltaZ) else cast('' as char(45)) end, '')
)
event_name
, n.speed
, n.topSpeed
, n.avgSpeed
, n.speedLimit
, n.distance/100 distance
, n.latitude
, n.longitude
, n.forgiven
from driver dr
join note n
on dr.driver_id = n.driver_id
join vehicle v
on n.vehicle_Id = v.vehicle_id
join groups g
on dr.group_id = g.group_id
join noteNames nn
on n.type = nn.type
where dr.account_id = """+account_id+"""
and n.time >= '"""+start_date+"""' - interval 1 day
and n.time <= '"""+end_date+"""' + interval 2 day
and n.type in (2,3,4,49,58,93,171,191)
) a
where a.date >= '"""+start_date+"""'
and a.date < '"""+end_date+"""' + interval 1 day
order by 1;"""
print(query)
cur.execute(query)
results = cur.fetchall()
with open(csvFileName, 'w') as output:
a = csv.writer(output, delimiter= ',')
a.writerows(results)
if cur.rowcount != 0:
print('writing results to ' + csvFileName)
else:
print(str(connector.Error('Could not write results to .csv file')))
cur.close()
cn.close()
def test_create_mysql_connection_exception(config):
# For example, if the credentials in the config are wrong
with patch("mysql.connector.connect", side_effect=mysql.Error()):
assert create_mysql_connection(config) is None