def write_tables(self):
tabla_rekordok = []
for cs in range(len(self.csoportok)):
for sor in range(len(self.csoportok[cs])):
if self.csoportok[cs][sor]._get_player_id() != 0:
tabla_rekord = []
tabla_rekord.append(self.torna_id)
tabla_rekord.append(
self.csoportok[cs][sor]._get_player_id())
tabla_rekord.append(
self.csoportok[cs][sor]._get_csoport_number())
tabla_rekord.append(
self.csoportok[cs][sor]._get_csoport_sor())
tabla_rekordok.append(tabla_rekord)
insertDataQuery = QSqlQuery()
insertDataQuery.prepare("""
insert into torna_tablak (
torna_id,
player_id,
csoport_number,
csoport_sor
)
values (?, ?, ?, ?)
""")
for x in range(len(tabla_rekordok)):
for i in range(len(tabla_rekordok[x])):
insertDataQuery.addBindValue(tabla_rekordok[x][i])
insertDataQuery.exec_()
query = QSqlQuery(
f"update torna_settings set aktiv=1 where torna_id={self.torna_id}"
)
query.exec_()
def insert_torrent(self, torrent):
db = self.get_db()
try:
if not db.isOpen():
db.open()
query = QSqlQuery(db=db)
query.prepare(
'update torrents set forum=:f, title=:t, seed=:seed, leech=:leech, published=:pub, last_modified=:lm, '
'hash=:hash, downloads=:d, last_checked=:lc, user_id=:user where id=:id'
)
query.bindValue(':id', torrent['id'])
query.bindValue(':f', torrent['forum'])
query.bindValue(':t', torrent['title'])
query.bindValue(':seed', torrent['seed'])
query.bindValue(':leech', torrent['leech'])
query.bindValue(':pub', str(torrent['published']))
query.bindValue(':lm', str(torrent['last_modified']))
query.bindValue(':hash', torrent['hash'])
query.bindValue(':d', torrent['downloads'])
query.bindValue(':lc', str(torrent['last_checked']))
query.bindValue(':user', torrent['user_id'])
return query.exec_()
except Exception as ex:
return False
finally:
db.close()
def setValue(self, key, value):
set_query = QSqlQuery(self.db)
set_query.prepare("INSERT OR REPLACE INTO settings(id, name, value) "
"VALUES((SELECT id FROM settings WHERE name=:key), :key, :value)")
set_query.bindValue(":key", key)
set_query.bindValue(":value", value)
if not set_query.exec_():
logging.fatal(f"Failed to set settings key='{key}' to value='{value}'")
self.db.commit()
def getValue(self, key):
get_query = QSqlQuery(self.db)
get_query.setForwardOnly(True)
get_query.prepare("SELECT value FROM settings WHERE name=:key")
value = None
get_query.bindValue(":key", key)
if not get_query.exec_():
logging.fatal(f"Failed to get settings for key='{key}'")
return value
if get_query.next():
value = get_query.value(0)
return value
def deregisterStudent(self, id):
self.db = QSqlDatabase.addDatabase("QSQLITE")
self.db.setDatabaseName("face.db")
if self.db.open() is False:
print("Failed opening db")
query = QSqlQuery(self.db)
query.prepare("DELETE FROM Student WHERE id = :id")
query.bindValue(":id", int(id))
query.exec_()
path = 'dataset'
imagePaths = [os.path.join(path, f) for f in os.listdir(path)]
[os.remove(x) for x in imagePaths if ('/' + str(id) + ".") in x]
self.getThreadSignal(9, "Requested Record removed completely. Kindly retrain.")
def save_torrent(self, torrent):
db = self.get_db()
try:
if not db.isOpen():
db.open()
query = QSqlQuery(db=db)
query.prepare(
'insert into torrents(id, forum, title) values(:id, :f, :t)')
query.bindValue(':id', torrent[0])
query.bindValue(':f', torrent[1])
query.bindValue(':t', torrent[2])
return query.exec_()
except Exception as ex:
return False
finally:
db.close()
def readSQL(sql_text, params=None, named=False, check_unique=False):
if params is None:
params = []
query = QSqlQuery(db_connection())
query.setForwardOnly(True)
if not query.prepare(sql_text):
logging.error(
f"SQL prep: '{query.lastError().text()}' for query '{sql_text}' | '{params}'"
)
return None
for param in params:
query.bindValue(param[0], param[1])
if not query.exec_():
logging.error(
f"SQL exec: '{query.lastError().text()}' for query '{sql_text}' | '{params}'"
)
return None
if query.next():
res = readSQLrecord(query, named=named)
if check_unique and query.next():
logging.warning(
g_tr('DB', "More than 1 record matched SQL query ") +
f"'{sql_text}' | '{params}'")
return None # More then one record in result when only one expected
return res
else:
return None
def generate_match_records(self):
match_rekords = []
csoport_tabla = [6, 5, 4, 3, 2,
1] # todo táblához rendeléshez kell majd
for cs in range(self.csoportok_szama):
for sor in range(self.sorok_szama):
for oszlop in range(sor + 1, self.sorok_szama):
if self.eredmenyek[cs][sor][oszlop]._get_p1_id(
) != 0 and self.eredmenyek[cs][sor][oszlop]._get_p2_id(
) != 0:
match_id = (10000 * self.torna_id) + (
100 *
int(self.eredmenyek[cs][sor][oszlop]._get_p1_id())
) + int(self.eredmenyek[cs][sor][oszlop]._get_p2_id())
match_rekord = []
match_rekord.append(self.torna_id)
match_rekord.append(match_id)
match_rekord.append(
self.eredmenyek[cs][sor][oszlop]._get_p1_id())
match_rekord.append(
self.eredmenyek[cs][sor][oszlop]._get_p2_id())
match_rekord.append(self.variant)
match_rekord.append(self.sets)
match_rekord.append(self.legsperset)
match_rekord.append(csoport_tabla[cs])
match_rekords.append(match_rekord)
insertDataQuery = QSqlQuery()
insertDataQuery.prepare("""
insert into torna_match (
torna_id,
match_id,
player1_id,
player2_id,
variant,
sets,
legsperset,
tabla
)
values (?, ?, ?, ?, ?, ?, ?, ?)
""")
for x in range(len(match_rekords)):
for i in range(len(match_rekords[x])):
insertDataQuery.addBindValue(match_rekords[x][i])
insertDataQuery.exec_()
self.write_tables()
def get_forum(self, id):
db = self.get_db()
try:
if not db.isOpen():
db.open()
query = QSqlQuery(db=db)
query.prepare("select * from forums where id=:id;")
query.bindValue(':id', id)
query.exec_()
forum: Dict[str, Optional[Any]] = {}
while query.next():
forum['id'] = query.value('id')
forum['category'] = query.value('category')
forum['title'] = query.value('title')
return forum
finally:
db.close()
def contextMenuEvent(self, event):
print("context-menü")
contextMenu = QMenu(self)
firstAction = contextMenu.addAction("Egy")
secondAction = contextMenu.addAction("Kettő")
action = contextMenu.exec_(self.mapToGlobal(event.pos()))
if action == firstAction:
if self._csoport_sor < self._csoport_oszlop:
match_id = (10000 * self.parent.torna_id) + (
100 * self._player1_id) + self._player2_id
elif self._csoport_sor > self._csoport_oszlop:
match_id = (10000 * self.parent.torna_id) + (
100 * self._player2_id) + self._player1_id
# print("match_id: ", match_id, "player1: ", self._player1_id, self.parent.torna_id, "hány leg", self.parent.nyereshez_kell)
winner_id = self._player1_id
set_id = 1
now = datetime.now().strftime("%Y-%m-%d %H.%M.%S")
query3 = QSqlQuery(
f"delete from matches where match_id={match_id} and winner_id={winner_id}",
db=db)
query3.exec_()
insertDataQuery = QSqlQuery()
insertDataQuery.prepare("""
insert into matches (
match_id,
leg_id,
set_id,
winner_id,
timestamp
)
values (?, ?, ?, ?, ?)
""")
for x in range(1, self.parent.nyereshez_kell + 1):
insertDataQuery.addBindValue(match_id)
insertDataQuery.addBindValue(x)
insertDataQuery.addBindValue(1)
insertDataQuery.addBindValue(winner_id)
insertDataQuery.addBindValue(now)
insertDataQuery.exec_()
query2 = QSqlQuery(
f"update torna_match set match_status=2 where match_id={match_id}",
db=db)
query2.exec_()
def update_user(self, user):
db = self.get_db()
try:
if not db.isOpen():
db.open()
query = QSqlQuery(db=db)
query.prepare(
'update users set name=:name, registered=:reg, nation=:nation where id=:id'
)
query.bindValue(':id', user['id'])
query.bindValue(':name', user['name'])
query.bindValue(':reg', user['registered'])
query.bindValue(':nation', user['nation'])
return query.exec_()
except Exception as ex:
return False
finally:
db.close()
def insert_user(self, user):
db = self.get_db()
try:
if not db.isOpen():
db.open()
query = QSqlQuery(db=db)
query.prepare(
'insert into users(id, name, registered, nation) values(:id, :name, :reg, :nation)'
)
query.bindValue(':id', user['id'])
query.bindValue(':name', user['name'])
query.bindValue(':reg', user['registered'])
query.bindValue(':nation', user['nation'])
return query.exec_()
except Exception as ex:
return False
finally:
db.close()
def __init__(self):
super().__init__()
self.table = QTableView()
self.model = QSqlQueryModel()
self.table.setModel(self.model)
query = QSqlQuery(db=db)
query.prepare("SELECT Name, Composer, Album.Title FROM Track "
"INNER JOIN Album ON Track.AlbumId = Album.AlbumId "
"WHERE Album.Title LIKE '%' || :album_title || '%' ")
query.bindValue(":album_title", "Sinatra")
query.exec_()
self.model.setQuery(query)
self.setMinimumSize(QSize(1024, 600))
self.setCentralWidget(self.table)
def get_categories(self):
db = self.get_db()
try:
if not db.isOpen():
db.open()
query = QSqlQuery(db=db)
query.prepare("select * from categories;")
query.exec_()
categories: List[Dict[str, None]] = []
while query.next():
category = {
'id': query.value('id'),
'title': query.value('title')
}
categories.append(category)
return categories
finally:
db.close()
def update_rss(self, f, d, ls):
db = self.get_db()
try:
if not db.isOpen():
db.open()
query = QSqlQuery(db=db)
query.prepare(
'update forums_rss set last_scanned=:ls, delta=:d where id=:id'
)
query.bindValue(':ls', str(ls))
query.bindValue(':d', d)
query.bindValue(':id', f)
query.exec_()
db.close()
return True
except Exception as ex:
return False
finally:
db.close()
def get_category(self, id):
category: Dict[str, Optional[Any]] = {}
if id is None:
category['id'] = id
category['title'] = '<UNKNOWN>'
db = self.get_db()
try:
if not db.isOpen():
db.open()
query = QSqlQuery(db=db)
query.prepare("select * from categories where id=:id;")
query.bindValue(':id', id)
query.exec_()
while query.next():
category['id'] = query.value('id')
category['title'] = query.value('title')
return category
finally:
db.close()
def main():
db = QSqlDatabase.addDatabase("QSQLITE")
db.setDatabaseName("foo.sqlite")
if not db.open():
sys.exit(-1)
lib = CDLL(os.path.join(CURRENT_DIR, "libqsqlite.so"))
lib.enable_extension(c_void_p(shiboken2.getCppPointer(db.driver())[0]))
load_spatialite()
query = QSqlQuery()
query.exec_("CREATE TABLE my_line(id INTEGER PRIMARY KEY)")
query.exec_(
"""SELECT AddGeometryColumn("my_line","geom" , 4326, "LINESTRING", 2)"""
)
polygon_wkt = "POLYGON ((11 50,11 51,12 51,12 50,11 50))"
XA = 11
YA = 52
XB = 12
YB = 49
line_wkt = "LINESTRING({0} {1}, {2} {3})".format(XA, YA, XB, YB)
query.prepare("""INSERT INTO my_line VALUES (?,GeomFromText(?, 4326))""")
query.addBindValue(1)
query.addBindValue(line_wkt)
query.exec_()
query.prepare(
"""SELECT astext(st_intersection(geom, GeomFromText(?, 4326))) from my_line WHERE st_intersects(geom, GeomFromText(?, 4326))"""
)
query.addBindValue(polygon_wkt)
query.addBindValue(polygon_wkt)
query.exec_()
while query.next():
for i in range(query.record().count()):
print(query.value(i))
def registerStudent(self, id, rollno, name, stclass, section):
self.db = QSqlDatabase.addDatabase("QSQLITE")
self.db.setDatabaseName("face.db")
if self.db.open() is False:
print("Failed opening db")
query = QSqlQuery(self.db)
query.prepare("Insert Into Student (id, rollno, name, class, section)"
"Values (:id, :rollno, :name, :class, :section)")
query.bindValue(":id", id)
query.bindValue(":rollno", rollno)
query.bindValue(":name", name)
query.bindValue(":class", stclass)
query.bindValue(":section", section)
query.exec_()
# take photos for training
self.startCapture(id)
self.db.close()
def get_forums(self):
db = self.get_db()
try:
if not db.isOpen():
db.open()
query = QSqlQuery(db=db)
query.prepare(
"SELECT *, c.title as ctitle FROM forums as f INNER JOIN categories as c ON f.category=c.id;"
)
query.exec_()
forums = []
while query.next():
forum: Dict[str, Optional[Any]] = {}
forum['id'] = query.value('id')
forum['category'] = query.value('ctitle')
forum['title'] = query.value('title')
forums.append(forum)
return forums
finally:
db.close()
def execute(self, *args):
"""
@type query: str
@type params: list
"""
query = QSqlQuery(db=self.db)
query.prepare(args[0])
if len(args) == 1:
text = args[0]
elif len(args) == 2:
text = args[0] % args[1]
for value in args[1]:
query.addBindValue(value)
query.exec_(args[0])
statusWindow = self.applicationWindow.mainWindow.txtStatus
# 状态窗口打印执行的SQL
statusWindow.append("%s;" % text)
return query
def executeSQL(db, sql_text, params = [], forward_only = True):
query = QSqlQuery(db)
query.setForwardOnly(forward_only)
if not query.prepare(sql_text):
logging.error(f"SQL prep: '{query.lastError().text()}' for query '{sql_text}' with params '{params}'")
return None
for param in params:
query.bindValue(param[0], param[1])
if not query.exec_():
logging.error(f"SQL exec: '{query.lastError().text()}' for query '{sql_text}' with params '{params}'")
return None
return query
def create_password() -> str:
"""Prompt for a password to be created and return it."""
dialogue = CreatePassword()
dialogue.exec_()
if dialogue.result() != QDialog.Accepted:
sys.exit()
password = dialogue.new_password
# upsert
query = QSqlQuery(QSqlDatabase.database())
query.prepare("""
insert into credentials (id, password)
values (0, :password)
on conflict (id)
do update set password=:password
""")
query.bindValue(":password", password)
query.exec_()
query.finish()
return password
def readSQL(db, sql_text, params = []):
query = QSqlQuery(db)
query.setForwardOnly(True)
if not query.prepare(sql_text):
logging.error(f"SQL prep: '{query.lastError().text()}' for query '{sql_text}' with params '{params}'")
return None
for param in params:
query.bindValue(param[0], param[1])
if not query.exec_():
logging.error(f"SQL exec: '{query.lastError().text()}' for query '{sql_text}' with params '{params}'")
return None
if query.next():
return readSQLrecord(query)
else:
return None
index = connn.primaryIndex(table)
print(
"name",
index.name(),
"Count of fields in index",
index.count(),
"isDescending",
index.isDescending(0),
)
query = QSqlQuery()
if 'good' not in tables:
if query.exec_(
"create table good(id integer primary key autoincrement, goodname text, goodcount integer) "
):
query.finish()
query.prepare("insert into good values (null, ?, ?)")
query.addBindValue('FlashDrive')
query.addBindValue(10)
if not query.exec_():
print("Error with insert record flashdrive",
e.lastError().text())
query.finish()
query.prepare("insert into good values (null, ?, ?)")
query.bindValue(0, 'Paper for printer')
query.bindValue(1, 3)
if not query.exec_():
print("Error with insert record paper", e.lastError().text())
query.finish()
query.prepare("insert into good values (null, :name, :count)")
query.bindValue(':name', 'Cartridge for printer')
query.bindValue(':count', 8)
def run(self):
self.inform.emit(7, "Testing...")
# Initialize and start realtime video capture
cam = cv2.VideoCapture(0)
# set video width
cam.set(3, 640)
# set video height
cam.set(4, 480)
# Define min window size to be recognized as a face
# minW = 0.1*cam.get(3)
# minH = 0.1*cam.get(4)
i = 0
# font = cv2.FONT_HERSHEY_SIMPLEX
self.db = QSqlDatabase.addDatabase("QSQLITE")
n = 0
while True:
ret, img = cam.read()
cv2.imwrite("temp" + str(n) + ".jpg", img)
gray = cv2.cvtColor(img, cv2.COLOR_BGR2GRAY)
faces = self.detector.detectMultiScale(gray, 1.4, 8)
for (x, y, w, h) in faces:
cv2.rectangle(img, (x, y), (x + w, y + h), (0, 255, 0), 2)
id, confidence = self.recognizer.predict(gray[y:y + h,
x:x + w])
# print(id, confidence)
# Check if confidence is less then 100 ==> "0" is perfect match
if (confidence > 40):
self.db.setDatabaseName("face.db")
if not self.db.open():
self.inform.emit(
99, "Critical Error: Database failed to open.")
else:
cDate = QDate().currentDate().toString('yyyy-MM-dd')
# query = QSqlQuery(self.db)
qstr = "SELECT * FROM Attendance WHERE id = " + str(
id
) + " AND date = '" + cDate + "' AND lecture_no = " + str(
self.lectureNo)
query = QSqlQuery(qstr)
query.next()
if not query.value(0):
query.prepare(
"INSERT INTO Attendance (id, date, lecture_no)"
"Values (:id, :date, :lectureNo)")
query.bindValue(":id", id)
query.bindValue(":date", str(cDate))
query.bindValue(":lectureNo", str(self.lectureNo))
query.exec_()
self.inform.emit(
8, "Attendance marked for ID = " + str(id) +
" for Lecture = " + str(self.lectureNo))
else:
self.inform.emit(
8, "Attendance for ID = " + str(id) +
" for Lecture = " + str(self.lectureNo) +
" is already marked.")
i += 1
break
# self.db.close()
# count.append(id)
if n == 0:
n = 1
else:
n = 0
if i > 0:
break
cam.release()
cv2.destroyAllWindows()
class ConnectPostgreSQL:
def __init__(self):
self.db = QSqlDatabase.addDatabase('QPSQL')
self.db.setHostName('localhost')
self.db.setPort(5432)
self.db.setDatabaseName('database_name')
self.db.setUserName('dbuser')
self.db.setPassword('123456')
self.db.open()
self.query = QSqlQuery()
# Removendo a tabela.
self.drop_table(table='table_name')
# Criando a tabela.
self.create_table()
def create_table(self):
sql = '''CREATE TABLE IF NOT EXISTS table_name (
id SERIAL NOT NULL,
name VARCHAR(100) NOT NULL,
age INTEGER NOT NULL,
gender VARCHAR(10) NOT NULL,
PRIMARY KEY(id)
);'''
self.query.exec_(sql)
def drop_table(self, table):
sql = f'DROP TABLE IF EXISTS {table};'
self.query.exec_(sql)
def insert_row(self, data):
sql = 'INSERT INTO table_name (name, age, gender) VALUES (?, ?, ?)'
self.query.prepare(sql)
for index, value in enumerate(data):
self.query.addBindValue(data[index])
if self.query.exec_():
self.query.clear()
return True
print(self.query.lastError())
return False
def insert_rows(self, data):
for row in data:
self.insert_row(data=row)
def find_by_id(self, rowid):
sql = 'SELECT * FROM table_name WHERE id = ?'
self.query.prepare(sql)
self.query.addBindValue(rowid)
self.query.exec_()
if self.query.first():
data = (self.query.value(0), self.query.value(1),
self.query.value(2), self.query.value(3))
self.query.clear()
return data
print(self.query.lastError())
return False
def find(self, limit=10):
sql = 'SELECT * FROM table_name LIMIT ?'
self.query.prepare(sql)
self.query.addBindValue(limit)
self.query.exec_()
result = []
while self.query.next():
data = (self.query.value(0), self.query.value(1),
self.query.value(2), self.query.value(3))
result.append(data)
self.query.clear()
return result
def update_row(self, rowid, name, age, gender):
sql = 'UPDATE table_name SET name=?, age=?, gender=? WHERE id=?'
self.query.prepare(sql)
self.query.addBindValue(name)
self.query.addBindValue(age)
self.query.addBindValue(gender)
self.query.addBindValue(rowid)
if self.query.exec_():
return True
print(self.query.lastError())
return False
def remove_row(self, rowid):
sql = 'DELETE FROM table_name WHERE id=?'
self.query.prepare(sql)
self.query.addBindValue(rowid)
if self.query.exec_():
return True
self.db.rollback()
print(self.query.lastError())
return False
class ConnectSQLite:
def __init__(self):
self.db = QSqlDatabase.addDatabase('QSQLITE')
self.db.setDatabaseName('DataBaseName.sqlite3')
# self.db.setDatabaseName(':memory:')
self.db.open()
self.query = QSqlQuery()
# Removendo a tabela.
self.drop_table(table='table_name')
# Criando a tabela.
self.create_table()
def create_table(self):
sql = '''CREATE TABLE IF NOT EXISTS table_name (
id INTEGER PRIMARY KEY,
name TEXT,
age INTEGER,
gender TEXT
);'''
self.query.exec_(sql)
def drop_table(self, table):
sql = f'DROP TABLE IF EXISTS {table};'
self.query.exec_(sql)
def insert_row(self, data):
sql = 'INSERT INTO table_name (name, age, gender) VALUES (?, ?, ?)'
self.query.prepare(sql)
for index, value in enumerate(data):
self.query.addBindValue(data[index])
self.query.exec_()
def insert_rows(self, data):
for row in data:
self.insert_row(data=row)
def find_by_id(self, rowid):
sql = 'SELECT * FROM table_name WHERE id = ?'
self.query.prepare(sql)
self.query.addBindValue(rowid)
self.query.exec_()
if self.query.first():
return (self.query.value(0), self.query.value(1),
self.query.value(2), self.query.value(3))
return False
def find(self, limit=10):
sql = 'SELECT * FROM table_name LIMIT ?'
self.query.prepare(sql)
self.query.addBindValue(limit)
self.query.exec_()
result = []
while self.query.next():
data = (self.query.value(0), self.query.value(1),
self.query.value(2), self.query.value(3))
result.append(data)
return result
def update_row(self, rowid, name, age, gender):
sql = 'UPDATE table_name SET name=?, age=?, gender=? WHERE id=?'
self.query.prepare(sql)
self.query.addBindValue(name)
self.query.addBindValue(age)
self.query.addBindValue(gender)
self.query.addBindValue(rowid)
if self.query.exec_():
return True
self.db.rollback()
print(self.query.lastError())
return False
def remove_row(self, rowid):
sql = 'DELETE FROM table_name WHERE id=?'
self.query.prepare(sql)
self.query.addBindValue(rowid)
if self.query.exec_():
return True
self.db.rollback()
print(self.query.lastError())
return False
class VentanaPrincipal(QTabWidget):
def __init__(self):
super(VentanaPrincipal, self).__init__()
self.ui = Ui_TabWidget()
self.ui.setupUi(self)
# DB
self.db = QSqlDatabase.addDatabase("QSQLITE")
self.db.setDatabaseName("cuentas.db")
self.conector = QSqlDatabase.database()
self.general_query = QSqlQuery()
self.organizar_db()
# Querys para psw_tool
self.master_query = QSqlQuery()
self.save_query = QSqlQuery()
self.passwords_query_retrieve = QSqlQuery()
self.passwords_query_write = QSqlQuery()
self.popular = QSqlQuery()
self.comboBoxes_query_categoria = QSqlQuery()
self.comboBoxes_query_mail = QSqlQuery()
self.comboBoxes_query_usuario = QSqlQuery()
self.verificar_columna_contrasenas = QSqlQuery()
self.filtro = QSqlQuery()
# Querys para pswItemDelegate
self.all_data = QSqlQuery()
# BASE DE DATOS en UI
self.model = QSqlTableModel() # pswItemDelegate.CustomSqlModel()
self.organizar_tabla_ui()
self.model.setEditStrategy(QSqlTableModel.OnManualSubmit) # Va aca abajo por self.model.setTable('passwords')
# Para cuando se cambian datos
self.model.dataChanged.connect(self.celdas_cambiadas)
self.ui.tabla_db.setContextMenuPolicy(Qt.CustomContextMenu)
self.ui.tabla_db.customContextMenuRequested.connect(self.context_menu)
# Filtro DB
# .connect(lambda: self.
self.tabBarClicked.connect(self.actualizar_tabs)
# Iconos DB en UI
self.icon_seguridad = QIcon()
self.icon_seguridad.addPixmap(QPixmap(":/media/iconografia/locked.png"), QIcon.Normal, QIcon.Off)
self.icon_desbloqueado = QIcon()
self.icon_desbloqueado.addPixmap(QPixmap(":/media/iconografia/unlocked.png"), QIcon.Normal, QIcon.Off)
self.icon_editar = QIcon()
self.icon_editar.addPixmap(QPixmap(":/media/iconografia/pencil.png"), QIcon.Normal, QIcon.Off)
self.icon_guardar = QIcon()
self.icon_guardar.addPixmap(QPixmap(":/media/iconografia/broken-pencil.png"), QIcon.Normal, QIcon.Off)
# Procesos iniciales
self.cargar_config()
self.master_key = None
self.seguridad_alterada = False
self.candado = "cerrado"
self.modo_boton_editar_guardar = "editar"
self.contrasenas_nuevas = {}
self.edits = {}
self.revisar_columna_contrasenas()
self.cargar_opciones_combo_boxes()
# Alertas
# Iconos para las alertas sin UI
self.icon_ventana = QIcon()
self.icon_ventana.addPixmap(QPixmap(":/media/imagenes/main_frame.png"), QIcon.Normal, QIcon.Off)
self.alerta_config = QMessageBox(QMessageBox.Warning,
"Problema con la configuración actual",
"Existen dos posibilidades para este error:\n\n1. El archivo de configuración está dañando\n2. Usted tiene todas las opciones desmarcadas (el amor no cuenta)\n\nPara solucionarlo, borre el archivo de configuración ('opciones.ini'),\no marque alguna opción en la pestaña de configuración y guarde su selección\n"
)
self.alerta_master_psw_mala = QMessageBox(QMessageBox.Warning,
"Problema con la contraseña ingresada",
"Por favor tome precauciones con la elección de la contraseña maestra.\nPara poder proseguir debe ingresar una contraseña con más de 5 y menos de 17 caracteres, o sino presione cancelar."
)
self.alerta_master_psw_incorrecta = QMessageBox(QMessageBox.Warning,
"Problema con la contraseña ingresada",
"La contraseña que ingresaste es incorrecta"
)
self.alerta_guardado_exitoso = QMessageBox(QMessageBox.Information, "Información guardada",
"Toda la información que ingresaste se guardó con éxito."
)
self.alerta_config.setWindowIcon(self.icon_ventana)
self.alerta_master_psw_mala.setWindowIcon(self.icon_ventana)
self.alerta_master_psw_incorrecta.setWindowIcon(self.icon_ventana)
self.alerta_guardado_exitoso.setWindowIcon(self.icon_ventana)
# Alertas con su propia UI
# Dialog info
self.dialogo_info = QDialog()
self.info_app = Ui_dialogo_info()
self.info_app.setupUi(self.dialogo_info)
# Alerta master psw
self.dialogo_master_psw = QDialog()
self.alerta_master_psw = Ui_dialogo_master_psw()
self.alerta_master_psw.setupUi(self.dialogo_master_psw)
# Botones
self.ui.boton_guardar_config.clicked.connect(self.guardar_config)
self.ui.boton_info.clicked.connect(self.cargar_info)
self.ui.boton_generar.clicked.connect(self.llamar_generar_contrasena) # Boton generar contrasñea
self.ui.boton_guardar.clicked.connect(self.guardar_contrasena) # boton guardar data
# Si presionan el boton revelar contraseña
self.ui.reveal_psw.clicked.connect(self.mostrar_contrasena)
self.alerta_master_psw.reveal_master_psw.clicked.connect(self.mostrar_contrasena_maestra)
# Icono del boton revelar contraseña
self.icon_not_view = QIcon()
self.icon_not_view.addPixmap(QPixmap(":/media/iconografia/not_view.png"), QIcon.Normal, QIcon.Off)
self.icon_view = QIcon()
self.icon_view.addPixmap(QPixmap(":/media/iconografia/view.png"), QIcon.Normal, QIcon.Off)
# Click en botones copiar # Otra manera de hacerlo: partial(self.llamar_copiar(n)) using functools.partial
self.ui.cp1.clicked.connect(lambda: self.llamar_copiar(1))
self.ui.cp2.clicked.connect(lambda: self.llamar_copiar(2))
self.ui.cp3.clicked.connect(lambda: self.llamar_copiar(3))
# Botones DB
self.ui.boton_filtro.clicked.connect(lambda: self.filtrar())
self.ui.boton_editar.clicked.connect(lambda: self.gestor_boton_editar_guardar())
self.ui.boton_seguridad.clicked.connect(lambda: self.mostrar_contrasenas())
# Botones Info
self.info_app.boton_steam.clicked.connect(lambda: backend.abrir_link("https://steamcommunity.com/id/JosephKm"))
self.info_app.boton_discord.clicked.connect(lambda: backend.abrir_link("https://discord.gg/wYuXPQS"))
self.info_app.boton_github.clicked.connect(
lambda: backend.abrir_link("https://github.com/kuttz-dev/Password-manager"))
# Si se presiona la pestaña de configuracion
# self.ui.tab_3.connect(self.cargar_config)
# SETEAR COMBOBOXES
self.ui.comboBox_usuario.setInsertPolicy(QComboBox.InsertAlphabetically)
self.ui.comboBox_mail.setInsertPolicy(QComboBox.InsertAlphabetically)
self.ui.comboBox_categoria.setInsertPolicy(QComboBox.InsertAlphabetically)
self.ui.comboBox_usuario.setDuplicatesEnabled(False)
self.ui.comboBox_mail.setDuplicatesEnabled(False)
self.ui.comboBox_categoria.setDuplicatesEnabled(False)
self.ui.comboBox_usuario.clearEditText()
self.ui.comboBox_mail.clearEditText()
self.ui.comboBox_categoria.clearEditText()
def organizar_db(self):
self.general_query.exec_(
'CREATE TABLE IF NOT EXISTS passwords (id INTEGER PRIMARY KEY ASC, categoria TEXT, icono BLOB, servicio TEXT, mail TEXT, usuario TEXT, contraseña_encriptada BLOB);'
)
self.general_query.exec_(
'CREATE TABLE IF NOT EXISTS maestra (id INTEGER PRIMARY KEY, muestra BLOB);'
)
self.general_query.exec_(
'DELETE FROM passwords WHERE usuario = "" AND mail = "" AND contraseña_encriptada = ""'
)
self.db.commit()
def borrar_columna_contrasenas(self):
self.general_query.exec_(
'DROP TABLE IF EXISTS temporal'
)
self.general_query.exec_(
'CREATE TABLE temporal (id INTEGER PRIMARY KEY ASC, categoria TEXT, icono BLOB, servicio TEXT, mail TEXT, usuario TEXT, contraseña_encriptada BLOB);'
)
self.popular.exec_(
'INSERT INTO temporal(id, categoria, icono, servicio, mail, usuario, contraseña_encriptada) SELECT id, categoria, icono, servicio, mail, usuario, contraseña_encriptada FROM passwords'
)
self.db.commit()
self.general_query.exec_(
'DROP TABLE IF EXISTS passwords'
)
self.popular.exec_(
'ALTER TABLE temporal RENAME TO passwords'
)
self.general_query.exec_(
'DROP TABLE IF EXISTS temporal'
)
self.db.commit()
def cargar_config(self):
largo, mayus, minus, numeros, special, icono = backend.obtener_cfg()
self.ui.spinBox_largo.setProperty("value", int(largo))
self.ui.check_mayus.setChecked(backend.string2bool(mayus))
self.ui.check_min.setChecked(backend.string2bool(minus))
self.ui.check_numeros.setChecked(backend.string2bool(numeros))
self.ui.check_caracteres.setChecked(backend.string2bool(special))
self.ui.check_amor.setChecked(backend.string2bool(icono))
def guardar_config(self):
hay_opcion_verdadera = False
largo = self.ui.spinBox_largo.value()
mayus = self.ui.check_mayus.checkState()
minus = self.ui.check_min.checkState()
numeros = self.ui.check_numeros.checkState()
special = self.ui.check_caracteres.checkState()
icono = self.ui.check_amor.checkState()
estado_checkeado = [mayus, minus, numeros, special, icono]
for i in range(len(estado_checkeado)):
if str(estado_checkeado[i]) == "PySide2.QtCore.Qt.CheckState.Checked":
if i != 4: # len de estado_checkeado - 1
hay_opcion_verdadera = True
estado_checkeado[i] = True
else:
estado_checkeado[i] = False
if hay_opcion_verdadera is False:
return self.alerta_config.exec()
backend.generar_cfg(largo,
estado_checkeado[0],
estado_checkeado[1],
estado_checkeado[2],
estado_checkeado[3],
estado_checkeado[4]
)
self.cargar_config()
def cargar_info(self):
return self.dialogo_info.exec()
def llamar_generar_contrasena(self):
# Primero obtenemos que tipo de contraseña quiere el usuario
largo, mayus, minus, numeros, special, icono = backend.obtener_cfg()
mayus = backend.string2bool(mayus)
minus = backend.string2bool(minus)
numeros = backend.string2bool(numeros)
special = backend.string2bool(special)
if mayus is False and minus is False and numeros is False and special is False:
self.alerta_config.exec()
texto_contrasena = backend.generar_contraseña(int(largo), mayus, minus, numeros, special)
self.ui.input_psw.setText(str(texto_contrasena)) # Ponemos la contraseña en la aplicacion
def mostrar_contrasena(self):
# Si esta en password a normal y viceversa
if self.ui.input_psw.echoMode() == QLineEdit.EchoMode.Password:
self.ui.reveal_psw.setIcon(self.icon_not_view)
self.ui.input_psw.setEchoMode(QLineEdit.Normal)
else:
self.ui.reveal_psw.setIcon(self.icon_view)
self.ui.input_psw.setEchoMode(QLineEdit.Password)
def mostrar_contrasena_maestra(self, modo_echo=False):
# Si no se cambio el estado va a estar en False y se va a usar el echoMode del input
if modo_echo is False:
modo_echo = self.alerta_master_psw.input_master_psw.echoMode()
# Si esta en password a normal y viceversa
if modo_echo == QLineEdit.EchoMode.Password:
self.alerta_master_psw.reveal_master_psw.setIcon(self.icon_not_view)
self.alerta_master_psw.input_master_psw.setEchoMode(QLineEdit.Normal)
else:
self.alerta_master_psw.reveal_master_psw.setIcon(self.icon_view)
self.alerta_master_psw.input_master_psw.setEchoMode(QLineEdit.Password)
def llamar_copiar(self, numero_boton):
if numero_boton == 1:
backend.copiar(str(self.ui.input_psw.text()))
if numero_boton == 2:
backend.copiar(str(self.ui.comboBox_usuario.currentText()))
if numero_boton == 3:
backend.copiar(str(self.ui.comboBox_mail.currentText()))
def preparar_favicon(self, url):
try:
archivo_ico = backend.descargar_favico(url)
except Exception: # Si lo que se ingreso era un link pero no se consiguio favicon
with open("media/favicons/domain.ico") as ico:
return QByteArray(ico.read())
# Si no se consiguio la imagen
if archivo_ico is None:
return None
with open(archivo_ico, "rb") as ico:
return QByteArray(ico.read())
def guardar_contrasena(self):
# self.ui.comboBox_usuario.currentText() / self.ui.comboBox_mail.currentText()
# self.ui.comboBox_categoria.currentText() / self.ui.input_url.text()
# self.ui.input_psw.text()
if self.master_key is None:
try:
self.master_key = self.pedir_contrasena_maestra()
except Exception:
return
if self.ui.input_psw.text() != "":
contrasena_ingresada_encriptada = QByteArray(pswCrypto.encriptar(self.ui.input_psw.text(), self.master_key))
else:
contrasena_ingresada_encriptada = ""
try:
fav_icon = self.preparar_favicon(self.ui.input_url.text())
self.save_query.prepare(
'INSERT INTO passwords (categoria, icono, servicio, mail, usuario, contraseña_encriptada) VALUES(?,?,?,?,?,?)'
)
self.save_query.addBindValue(self.ui.comboBox_categoria.currentText())
self.save_query.addBindValue(fav_icon)
self.save_query.addBindValue(self.ui.input_url.text())
self.save_query.addBindValue(self.ui.comboBox_mail.currentText())
self.save_query.addBindValue(self.ui.comboBox_usuario.currentText())
self.save_query.addBindValue(contrasena_ingresada_encriptada)
self.save_query.exec_()
self.db.commit()
self.model.select()
self.ui.tabla_db.resizeColumnsToContents()
self.cargar_opciones_combo_boxes()
return self.alerta_guardado_exitoso.exec()
except Exception as ex:
template = "An exception of type {0} occurred. Arguments:\n{1!r}"
message = template.format(type(ex).__name__, ex.args)
print(message)
def pedir_contrasena_maestra(self):
self.dialogo_master_psw.exec()
contrasena_maestra = self.alerta_master_psw.input_master_psw.text()
# Borrarmos el texto porque no se borra solo, y lo volvemos secreto de nuevo
self.alerta_master_psw.input_master_psw.setText("")
self.mostrar_contrasena_maestra(QLineEdit.EchoMode.Normal)
# Si le dio a cancelar
if bool(self.dialogo_master_psw.result()) is False:
raise Exception("Accion canelada")
# Comprobamos que cumpla requisitos
if contrasena_maestra == "" or len(contrasena_maestra) < 6 or len(contrasena_maestra) > 16:
self.alerta_master_psw_mala.exec()
return self.pedir_contraseña_maestra()
# Encriptacion
key_contrasena_maestra = pswCrypto.generar_key(contrasena_maestra) # La convertimos en una key
# Verificacion
# Obtenemos la muestra guardada en la db
muestra_db = backend.obtener_muestra_db()
# Si no habia guardamos una nueva con esta contraseña maestra
if muestra_db is None:
array = QByteArray(backend.generar_muestra(key_contrasena_maestra))
self.master_query.prepare("INSERT INTO maestra (id, muestra) VALUES(1, ?)")
self.master_query.addBindValue(array)
self.master_query.exec_()
return key_contrasena_maestra
else:
# Ahora si verificamos
psw_correcta = backend.verificar_key(muestra_db, key_contrasena_maestra)
if psw_correcta is True:
return key_contrasena_maestra
else:
self.alerta_master_psw_incorrecta.exec_()
raise Exception("Contraseña maestra incorrecta")
def filtrar(self):
if self.ui.input_filtro.text() == "":
self.model.setFilter("")
return self.model.select()
else:
self.model.setFilter(
"{} LIKE '{}%'".format(self.ui.combobox_filtro.currentText().lower(), self.ui.input_filtro.text())
)
return self.model.select()
def mostrar_contrasenas(self):
if self.master_key is None:
try:
self.master_key = self.pedir_contrasena_maestra()
except Exception:
return
if self.candado == "abierto": # Si esta abierto
self.borrar_columna_contrasenas()
self.ui.combobox_filtro.removeItem(4)
self.organizar_tabla_ui()
self.ui.boton_seguridad.setIcon(self.icon_seguridad)
self.candado = "cerrado" # lo cerramos
return
else: # Si estaba cerrado
self.candado = "abierto" # lo abrimos
# y se abre asi:
self.ui.boton_seguridad.setIcon(self.icon_desbloqueado)
# Conseguimos las contraseñas encriptadas
self.passwords_query_retrieve.exec_('SELECT id, contraseña_encriptada FROM passwords')
# Creamos una columna para las contraseñas descifradas
self.general_query.exec_('ALTER TABLE passwords ADD contraseña TEXT')
self.db.commit()
# Para cada contraseña
while self.passwords_query_retrieve.next():
# Si se guardo un texto vacio
contrasena_descifrada = self.passwords_query_retrieve.value(1)
if type(contrasena_descifrada) != str:
contrasena_descifrada = pswCrypto.descifrar(contrasena_descifrada.data(), self.master_key)
self.passwords_query_write.prepare('UPDATE passwords SET contraseña = ? WHERE id= ?')
self.passwords_query_write.addBindValue(contrasena_descifrada)
self.passwords_query_write.addBindValue(self.passwords_query_retrieve.value(0))
self.passwords_query_write.exec_()
self.ui.combobox_filtro.addItem("Contraseña")
self.organizar_tabla_ui() # Las hacemos visibles el la tabla
def actualizar_tabs(self, index):
if index == 0:
self.resize(437, 200)
self.cargar_opciones_combo_boxes()
if index == 1 and self.ui.boton_seguridad.isEnabled() is True:
self.organizar_tabla_ui()
elif index == 2:
self.resize(437, 200)
self.cargar_config()
def organizar_tabla_ui(self):
self.model.setTable('passwords')
self.model.setSort(1, Qt.AscendingOrder)
self.model.select()
self.ui.tabla_db.setModel(self.model)
self.ui.tabla_db.setItemDelegateForColumn(2, pswItemDelegate.ImageDelegate(self.ui.tabla_db))
self.ui.tabla_db.hideColumn(0) # Escondemos id
self.ui.tabla_db.hideColumn(6) # Escondemos las contraseñas encriptadas
self.ui.tabla_db.setWindowTitle("Lista de cuentas")
# Tamaño columnas
#self.ui.tabla_db.resizeColumnsToContents()
self.ui.tabla_db.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)
self.ui.tabla_db.horizontalHeader().setSectionResizeMode(2, QHeaderView.Fixed) # iconos
self.ui.tabla_db.setColumnWidth(2, 32) # tamaño iconos
self.ui.tabla_db.verticalHeader().setVisible(False)
self.ui.tabla_db.setSortingEnabled(True)
def cargar_opciones_combo_boxes(self):
a = self.ui.comboBox_categoria.currentText()
b = self.ui.comboBox_mail.currentText()
c = self.ui.comboBox_usuario.currentText()
self.comboBoxes_query_categoria.exec_(
'SELECT DISTINCT categoria FROM passwords'
)
self.comboBoxes_query_mail.exec_(
'SELECT DISTINCT mail FROM passwords'
)
self.comboBoxes_query_usuario.exec_(
'SELECT DISTINCT usuario FROM passwords'
)
# Borramos las anteriores
self.ui.comboBox_usuario.clear()
self.ui.comboBox_mail.clear()
self.ui.comboBox_categoria.clear()
# Cargamos las nuevas
while self.comboBoxes_query_categoria.next():
if self.comboBoxes_query_categoria.value(0) == "" or self.comboBoxes_query_categoria.value(0) is None:
continue
self.ui.comboBox_categoria.addItem(self.comboBoxes_query_categoria.value(0))
while self.comboBoxes_query_mail.next():
if self.comboBoxes_query_mail.value(0) == "" or self.comboBoxes_query_mail.value(0) is None:
continue
self.ui.comboBox_mail.addItem(self.comboBoxes_query_mail.value(0))
while self.comboBoxes_query_usuario.next():
if self.comboBoxes_query_usuario.value(0) == "" or self.comboBoxes_query_usuario.value(0) is None:
continue
self.ui.comboBox_usuario.addItem(self.comboBoxes_query_usuario.value(0))
self.ui.comboBox_categoria.setCurrentText(a)
self.ui.comboBox_mail.setCurrentText(b)
self.ui.comboBox_usuario.setCurrentText(c)
def revisar_columna_contrasenas(self, borrar=True):
existe = self.verificar_columna_contrasenas.exec_('SELECT contraseña FROM passwords LIMIT 1')
if existe is True and borrar is True:
self.verificar_columna_contrasenas.finish()
self.borrar_columna_contrasenas()
return self.organizar_tabla_ui()
elif existe is True:
return "Existe"
def gestor_boton_editar_guardar(self):
if self.modo_boton_editar_guardar == "editar":
try:
self.editar_tabla_ui()
except Exception:
return
self.modo_boton_editar_guardar = "guardar" # Cambiamos al proximo modo
return self.ui.boton_editar.setIcon(self.icon_guardar)
elif self.modo_boton_editar_guardar == "guardar":
self.borrar_columna_contrasenas()
self.organizar_tabla_ui()
self.candado = "cerrado"
self.ui.boton_seguridad.setDisabled(False)
self.ui.boton_seguridad.setIcon(self.icon_seguridad)
self.ui.boton_editar.setIcon(self.icon_editar)
self.modo_boton_editar_guardar = "editar" # Cambiamos al proximo modo
return
def editar_tabla_ui(self):
if self.master_key is None:
try:
self.master_key = self.pedir_contrasena_maestra()
except Exception:
raise Exception("No se pudo conseguir master key")
self.ui.boton_seguridad.setDisabled(True)
if self.candado == "cerrado":
self.mostrar_contrasenas()
def celdas_cambiadas(self, top_left, bottom_right):
if self.modo_boton_editar_guardar == "editar":
return
else:
if top_left.column() == 7:
contrasena_editada_encriptada = ""
if self.model.record(top_left.row()).value('contraseña') != "":
contrasena_editada_encriptada = QByteArray(
pswCrypto.encriptar(self.model.record(top_left.row()).value('contraseña'), self.master_key))
casilla_editada = QSqlField("contraseña_encriptada")
casilla_editada.setValue(contrasena_editada_encriptada)
valores_fila = self.model.record(top_left.row())
valores_fila.replace(6, casilla_editada)
return self.model.updateRowInTable(top_left.row(), valores_fila)
else:
self.model.updateRowInTable(top_left.row(), self.model.record(top_left.row()))
return self.db.commit()
def borrar_linea(self, row):
self.model.deleteRowFromTable(row)
return self.model.select()
def context_menu(self):
# Menu con click derecho
if self.ui.tabla_db.selectedIndexes() and self.modo_boton_editar_guardar == "guardar":
menu = QMenu()
borrar_data = menu.addAction("Borrar linea de la base de datos")
borrar_data.triggered.connect(lambda: self.borrar_linea(self.ui.tabla_db.currentIndex().row()))
cursor = QCursor()
menu.exec_(cursor.pos())
class OperationsModel(QAbstractTableModel):
PAGE_SIZE = 100
COL_TYPE = 0
COL_SUBTYPE = 1
COL_ID = 2
COL_TIMESTAMP = 3
COL_ACCOUNT_ID = 4
COL_ACCOUNT = 5
COL_NUMBER_PEER = 6
COL_ASSET_ID = 7
COL_ASSET = 8
COL_ASSET_NAME = 9
COL_NOTE = 10
COL_NOTE2 = 11
COL_AMOUNT = 12
COL_QTY = 13
COL_PRICE = 14
COL_FEE_TAX = 15
COL_TOTAL_AMOUNT = 16
COL_TOTAL_QTY = 17
COL_CURRENCY = 18
COL_RECONCILED = 19
_columns = [
" ",
g_tr('OperationsModel', "Timestamp"),
g_tr('OperationsModel', "Account"),
g_tr('OperationsModel', "Notes"),
g_tr('OperationsModel', "Amount"),
g_tr('OperationsModel', "Balance"),
g_tr('OperationsModel', "Currency")
]
_tables = {
TransactionType.Action: "actions",
TransactionType.Dividend: "dividends",
TransactionType.Trade: "trades",
TransactionType.Transfer: "transfers",
TransactionType.CorporateAction: "corp_actions"
}
OperationSign = {
(TransactionType.Action, -1): ('—', CustomColor.DarkRed),
(TransactionType.Action, +1): ('+', CustomColor.DarkGreen),
(TransactionType.Dividend, DividendSubtype.Dividend):
('Δ', CustomColor.DarkGreen),
(TransactionType.Dividend, DividendSubtype.BondInterest):
('%', CustomColor.DarkGreen),
(TransactionType.Trade, -1): ('S', CustomColor.DarkRed),
(TransactionType.Trade, +1): ('B', CustomColor.DarkGreen),
(TransactionType.Transfer, TransferSubtype.Outgoing):
('<', CustomColor.DarkBlue),
(TransactionType.Transfer, TransferSubtype.Incoming):
('>', CustomColor.DarkBlue),
(TransactionType.Transfer, TransferSubtype.Fee): ('=',
CustomColor.DarkRed),
(TransactionType.CorporateAction, CorporateAction.Merger):
('âƒ', CustomColor.Black),
(TransactionType.CorporateAction, CorporateAction.SpinOff):
('⎇', CustomColor.DarkGreen),
(TransactionType.CorporateAction, CorporateAction.Split):
('á—•', CustomColor.Black),
(TransactionType.CorporateAction, CorporateAction.SymbolChange):
('🡘', CustomColor.Black),
(TransactionType.CorporateAction, CorporateAction.StockDividend):
('Δ\ns', CustomColor.DarkGreen)
}
CorpActionNames = {
CorporateAction.SymbolChange:
g_tr('OperationsModel', "Symbol change {old} -> {new}"),
CorporateAction.Split:
g_tr('OperationsModel', "Split {old} {before} into {after}"),
CorporateAction.SpinOff:
g_tr('OperationsModel', "Spin-off {after} {new} from {before} {old}"),
CorporateAction.Merger:
g_tr('OperationsModel', "Merger {before} {old} into {after} {new}"),
CorporateAction.StockDividend:
g_tr('OperationsModel', "Stock dividend: {after} {new}")
}
def __init__(self, parent_view):
super().__init__(parent_view)
self._view = parent_view
self._amount_delegate = None
self._data = []
self._row_count = 0
self._query = QSqlQuery(db_connection())
self._begin = 0
self._end = 0
self._account = 0
self._text_filter = ''
self.prepareData()
def rowCount(self, parent=None):
return len(self._data)
def columnCount(self, parent=None):
return len(self._columns)
def canFetchMore(self, index):
return len(self._data) < self._row_count
def fetchMore(self, index):
new_size = len(self._data) + self.PAGE_SIZE
new_size = new_size if new_size < self._row_count else self._row_count
self.beginInsertRows(index, len(self._data), new_size - 1)
i = 0
indexes = range(self._query.record().count())
while (i < self.PAGE_SIZE) and self._query.next():
values = list(map(self._query.value, indexes))
self._data.append(values)
i += 1
self.endInsertRows()
def headerData(self, section, orientation, role=Qt.DisplayRole):
if orientation == Qt.Horizontal and role == Qt.DisplayRole:
return self._columns[section]
return None
def get_operation(self, row):
if (row >= 0) and (row < len(self._data)):
return self._data[row][self.COL_TYPE], self._data[row][self.COL_ID]
else:
return [0, 0]
def data(self, index, role=Qt.DisplayRole):
if not index.isValid():
return None
if role == Qt.DisplayRole:
return self.data_text(index.row(), index.column())
if role == Qt.FontRole and index.column() == 0:
font = QFont()
font.setBold(True)
return font
if role == Qt.ForegroundRole:
return self.data_foreground(index.row(), index.column())
if role == Qt.TextAlignmentRole:
if index.column() == 0:
return Qt.AlignCenter
if index.column() == 4 or index.column() == 5:
return Qt.AlignRight
return Qt.AlignLeft
def data_text(self, row, column):
if column == 0:
try:
return self.OperationSign[self._data[row][self.COL_TYPE],
self._data[row][self.COL_SUBTYPE]][0]
except KeyError:
return '?'
elif column == 1:
if (self._data[row][self.COL_TYPE] == TransactionType.Trade) or (self._data[row][self.COL_TYPE] == TransactionType.Dividend) \
or (self._data[row][self.COL_TYPE] == TransactionType.CorporateAction):
return f"{datetime.utcfromtimestamp(self._data[row][self.COL_TIMESTAMP]).strftime('%d/%m/%Y %H:%M:%S')}\n# {self._data[row][self.COL_NUMBER_PEER]}"
else:
return datetime.utcfromtimestamp(self._data[row][
self.COL_TIMESTAMP]).strftime('%d/%m/%Y %H:%M:%S')
elif column == 2:
if self._data[row][self.COL_TYPE] == TransactionType.Action:
return self._data[row][self.COL_ACCOUNT]
elif (self._data[row][self.COL_TYPE] == TransactionType.Trade) \
or (self._data[row][self.COL_TYPE] == TransactionType.Dividend) \
or (self._data[row][self.COL_TYPE] == TransactionType.CorporateAction):
return self._data[row][self.COL_ACCOUNT] + "\n" + self._data[
row][self.COL_ASSET_NAME]
elif self._data[row][self.COL_TYPE] == TransactionType.Transfer:
if self._data[row][self.COL_SUBTYPE] == TransferSubtype.Fee:
return self._data[row][self.COL_ACCOUNT]
elif self._data[row][
self.COL_SUBTYPE] == TransferSubtype.Outgoing:
return self._data[row][
self.COL_ACCOUNT] + " -> " + self._data[row][
self.COL_NOTE2]
elif self._data[row][
self.COL_SUBTYPE] == TransferSubtype.Incoming:
return self._data[row][
self.COL_ACCOUNT] + " <- " + self._data[row][
self.COL_NOTE2]
elif column == 3:
if self._data[row][self.COL_TYPE] == TransactionType.Action:
note = self._data[row][self.COL_NUMBER_PEER]
if self._data[row][self.COL_ASSET] != '' and self._data[row][
self.COL_FEE_TAX] != 0:
note += "\n" + g_tr('OperationsModel', "Rate: ")
if self._data[row][self.COL_FEE_TAX] >= 1:
note += f"{self._data[row][self.COL_FEE_TAX]:.4f} " \
f"{self._data[row][self.COL_ASSET]}/{self._data[row][self.COL_CURRENCY]}"
else:
note += f"{1/self._data[row][self.COL_FEE_TAX]:.4f} " \
f"{self._data[row][self.COL_CURRENCY]}/{self._data[row][self.COL_ASSET]}"
return note
elif self._data[row][self.COL_TYPE] == TransactionType.Transfer:
rate = 0 if self._data[row][
self.COL_PRICE] == '' else self._data[row][self.COL_PRICE]
if self._data[row][self.COL_CURRENCY] != self._data[row][
self.COL_NUMBER_PEER]:
if rate != 0:
if rate > 1:
return self._data[row][
self.
COL_NOTE] + f" [1 {self._data[row][self.COL_CURRENCY]} = {rate:.4f} {self._data[row][self.COL_NUMBER_PEER]}]"
elif rate < 1:
rate = 1 / rate
return self._data[row][
self.
COL_NOTE] + f" [{rate:.4f} {self._data[row][self.COL_CURRENCY]} = 1 {self._data[row][self.COL_NUMBER_PEER]}]"
else:
return self._data[row][self.COL_NOTE]
else:
return g_tr('OperationsModel', "Error. Zero rate")
else:
return self._data[row][self.COL_NOTE]
elif self._data[row][self.COL_TYPE] == TransactionType.Dividend:
return self._data[row][self.COL_NOTE] + "\n" + g_tr(
'OperationsModel',
"Tax: ") + self._data[row][self.COL_NOTE2]
elif self._data[row][self.COL_TYPE] == TransactionType.Trade:
if self._data[row][self.COL_FEE_TAX] != 0:
text = f"{self._data[row][self.COL_QTY]:+.2f} @ {self._data[row][self.COL_PRICE]:.4f}\n({self._data[row][self.COL_FEE_TAX]:.2f}) "
else:
text = f"{self._data[row][self.COL_QTY]:+.2f} @ {self._data[row][self.COL_PRICE]:.4f}\n"
text = text + self._data[row][
self.COL_NOTE] if self._data[row][self.COL_NOTE] else text
return text
elif self._data[row][
self.COL_TYPE] == TransactionType.CorporateAction:
basis = 100.0 * self._data[row][self.COL_PRICE]
if self._data[row][
self.COL_SUBTYPE] == CorporateAction.StockDividend:
qty_after = self._data[row][
self.COL_QTY] - self._data[row][self.COL_AMOUNT]
else:
qty_after = self._data[row][self.COL_QTY]
text = self.CorpActionNames[self._data[row][
self.COL_SUBTYPE]].format(
old=self._data[row][self.COL_ASSET],
new=self._data[row][self.COL_NOTE],
before=self._data[row][self.COL_AMOUNT],
after=qty_after)
if self._data[row][
self.COL_SUBTYPE] == CorporateAction.SpinOff:
text += f"; {basis:.2f}% " + g_tr(
'OperationsModel',
" cost basis") + "\n" + self._data[row][self.COL_NOTE2]
return text
else:
assert False
elif column == 4:
if self._data[row][self.COL_TYPE] == TransactionType.Trade:
return [
self._data[row][self.COL_AMOUNT],
self._data[row][self.COL_QTY]
]
elif self._data[row][self.COL_TYPE] == TransactionType.Dividend:
return [
self._data[row][self.COL_AMOUNT],
-self._data[row][self.COL_FEE_TAX]
]
elif self._data[row][self.COL_TYPE] == TransactionType.Action:
if self._data[row][self.COL_ASSET] != '':
return [
self._data[row][self.COL_AMOUNT],
self._data[row][self.COL_PRICE]
]
else:
return [self._data[row][self.COL_AMOUNT]]
elif self._data[row][self.COL_TYPE] == TransactionType.Transfer:
return [self._data[row][self.COL_AMOUNT]]
elif self._data[row][
self.COL_TYPE] == TransactionType.CorporateAction:
if self._data[row][
self.
COL_SUBTYPE] == CorporateAction.SpinOff or self._data[
row][self.
COL_SUBTYPE] == CorporateAction.StockDividend:
return [
None, self._data[row][self.COL_QTY] -
self._data[row][self.COL_AMOUNT]
]
else:
return [
-self._data[row][self.COL_AMOUNT],
self._data[row][self.COL_QTY]
]
else:
assert False
elif column == 5:
upper_part = f"{self._data[row][self.COL_TOTAL_AMOUNT]:,.2f}" if self._data[
row][self.COL_TOTAL_AMOUNT] != '' else "-.--"
lower_part = f"{self._data[row][self.COL_TOTAL_QTY]:,.2f}" if self._data[
row][self.COL_TOTAL_QTY] != '' else ''
if self._data[row][
self.COL_TYPE] == TransactionType.CorporateAction:
qty_before = self._data[row][
self.COL_AMOUNT] if self._data[row][
self.COL_SUBTYPE] == CorporateAction.SpinOff else 0
qty_after = self._data[row][self.COL_TOTAL_QTY] if self._data[row][
self.
COL_SUBTYPE] == CorporateAction.StockDividend else self._data[
row][self.COL_QTY]
if self._data[row][
self.COL_SUBTYPE] == CorporateAction.StockDividend:
text = f"\n{qty_after:,.2f}" if qty_after != '' else "\n-.--"
else:
text = f"{qty_before:,.2f}\n{qty_after:,.2f}"
return text
elif self._data[row][
self.COL_TYPE] == TransactionType.Action or self._data[
row][self.COL_TYPE] == TransactionType.Transfer:
return upper_part
else:
return upper_part + "\n" + lower_part
elif column == 6:
if self._data[row][
self.COL_TYPE] == TransactionType.CorporateAction:
asset_before = self._data[row][
self.COL_ASSET] if self._data[row][
self.
COL_SUBTYPE] != CorporateAction.StockDividend else ""
return f" {asset_before}\n {self._data[row][self.COL_NOTE]}"
else:
if self._data[row][self.COL_ASSET] != '':
return f" {self._data[row][self.COL_CURRENCY]}\n {self._data[row][self.COL_ASSET]}"
else:
return f" {self._data[row][self.COL_CURRENCY]}"
else:
assert False
def data_foreground(self, row, column):
if column == 0:
try:
return QBrush(
self.OperationSign[self._data[row][self.COL_TYPE],
self._data[row][self.COL_SUBTYPE]][1])
except KeyError:
return QBrush(CustomColor.LightRed)
if column == 5:
if self._data[row][self.COL_RECONCILED] == 1:
return QBrush(CustomColor.Blue)
def configureView(self):
self._view.setColumnWidth(0, 10)
self._view.setColumnWidth(
1,
self._view.fontMetrics().width("00/00/0000 00:00:00") * 1.1)
self._view.setColumnWidth(2, 300)
self._view.horizontalHeader().setSectionResizeMode(
3, QHeaderView.Stretch)
font = self._view.horizontalHeader().font()
font.setBold(True)
self._view.horizontalHeader().setFont(font)
self._amount_delegate = ColoredAmountsDelegate(self._view)
self._view.setItemDelegateForColumn(4, self._amount_delegate)
self._view.verticalHeader().setSectionResizeMode(
QHeaderView.ResizeToContents)
@Slot()
def setAccount(self, account_id):
if self._account != account_id:
self._account = account_id
self.prepareData()
def getAccount(self):
return self._account
@Slot()
def setDateRange(self, start, end=0):
self._begin = start
if end:
self._end = end
else:
self._end = QDate.currentDate().endOfDay(Qt.UTC).toSecsSinceEpoch()
self.prepareData()
@Slot()
def filterText(self, filter):
if filter:
self._text_filter = f" AND (num_peer LIKE '%{filter}%' COLLATE NOCASE "\
f"OR note LIKE '%{filter}%' COLLATE NOCASE "\
f"OR note2 LIKE '%{filter}%' COLLATE NOCASE "\
f"OR asset LIKE '%{filter}%' COLLATE NOCASE "\
f"OR asset_name LIKE '%{filter}%' COLLATE NOCASE)"
else:
self._text_filter = ''
self.prepareData()
def update(self):
self.prepareData()
def get_operation_type(self, row):
if (row >= 0) and (row < len(self._data)):
return self._data[row][self.COL_TYPE]
else:
return 0
def reconcile_operation(self, row):
if (row >= 0) and (row < len(self._data)):
timestamp = self._data[row][self.COL_TIMESTAMP]
account_id = self._data[row][self.COL_ACCOUNT_ID]
_ = executeSQL(
"UPDATE accounts SET reconciled_on=:timestamp WHERE id = :account_id",
[(":timestamp", timestamp), (":account_id", account_id)])
self.prepareData()
@Slot()
def refresh(self):
idx = self._view.selectionModel().selection().indexes()
self.prepareData()
if idx:
self._view.setCurrentIndex(idx[0])
def prepareData(self):
self._data = []
if self._begin == 0 and self._end == 0:
self._row_count = 0
else:
count_pfx = "SELECT COUNT(*) "
query_pfx = "SELECT * "
query_suffix = f"FROM all_operations AS o WHERE o.timestamp>={self._begin} AND o.timestamp<={self._end}" + \
self._text_filter
if self._account:
query_suffix = query_suffix + f" AND o.account_id = {self._account}"
self._row_count = readSQL(count_pfx + query_suffix)
self._query.prepare(query_pfx + query_suffix)
self._query.setForwardOnly(True)
self._query.exec_()
self.fetchMore(self.createIndex(0, 0))
self.modelReset.emit()
def deleteRows(self, rows):
for row in rows:
if (row >= 0) and (row < len(self._data)):
table_name = self._tables[self._data[row][self.COL_TYPE]]
query = f"DELETE FROM {table_name} WHERE id={self._data[row][self.COL_ID]}"
_ = executeSQL(query)
self.prepareData()
class MainWindow(QMainWindow):
def __init__(self):
super().__init__()
container = QWidget()
layout_search = QHBoxLayout()
self.track = QLineEdit()
self.track.setPlaceholderText("Track name...")
self.track.textChanged.connect(self.update_query)
self.composer = QLineEdit()
self.composer.setPlaceholderText("Artist name...")
self.composer.textChanged.connect(self.update_query)
self.album = QLineEdit()
self.album.setPlaceholderText("Album name...")
self.album.textChanged.connect(self.update_query)
layout_search.addWidget(self.track)
layout_search.addWidget(self.composer)
layout_search.addWidget(self.album)
layout_view = QVBoxLayout()
layout_view.addLayout(layout_search)
self.table = QTableView()
layout_view.addWidget(self.table)
container.setLayout(layout_view)
self.model = QSqlQueryModel()
self.table.setModel(self.model)
self.query = QSqlQuery(db=db)
self.query.prepare(
"SELECT Name, Composer, Album.Title FROM Track "
"INNER JOIN Album ON Track.AlbumId=Album.AlbumId WHERE "
"Track.Name LIKE '%' || :track_name || '%' AND "
"Track.Composer LIKE '%' || :track_composer || '%' AND "
"Album.Title LIKE '%' || :album_title || '%'"
)
self.update_query()
self.setMinimumSize(QSize(1024, 600))
self.setCentralWidget(container)
def update_query(self, s=None):
# Get the text values from the widgets.
track_name = self.track.text()
track_composer = self.composer.text()
album_title = self.album.text()
self.query.bindValue(":track_name", track_name)
self.query.bindValue(":track_composer", track_composer)
self.query.bindValue(":album_title", album_title)
self.query.exec_()
self.model.setQuery(self.query)
