def readSQL(sql_text, params=None, named=False, check_unique=False):
if params is None:
params = []
query = QSqlQuery(
db_connection()
) # TODO reimplement via ExecuteSQL() call in order to get rid of duplicated code
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():
return None # More then one record in result when only one expected
return res
else:
return None
def __init__(self, parent_view):
super().__init__(parent_view)
self._columns = [
" ",
self.tr("Timestamp"),
self.tr("Account"),
self.tr("Notes"),
self.tr("Amount"),
self.tr("Balance"),
self.tr("Currency")
]
self.CorpActionNames = {
CorporateAction.SymbolChange:
self.tr("Symbol change {old} -> {new}"),
CorporateAction.Split:
self.tr("Split {old} {before} into {after}"),
CorporateAction.SpinOff:
self.tr("Spin-off {after} {new} from {before} {old}"),
CorporateAction.Merger:
self.tr("Merger {before} {old} into {after} {new}"),
CorporateAction.StockDividend:
self.tr("Stock dividend: {after} {new}")
}
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 ExeQuery(self, Query): #Tested
"""
Executes an QSqlQuery and returns the query to get results
>>> library_manager.ExeQuery(query)
Parameters
----------
Query: QSqlQuery,String
Query to execute
Returns
-------
QSqlQuery
SQLQuery
"""
if isinstance(Query, str):
QueryStr = Query
Query = QSqlQuery()
if Query.prepare(QueryStr) == False:
raise QueryBuildFailed(QueryStr)
QueryExe = Query.exec()
if QueryExe == False:
msg = f"""
EXE: {QueryExe}
ERROR: {(Query.lastError().text())}
Query: {Query.lastQuery()}
"""
raise QueryExecutionFailed(dedenter(msg, 12))
else:
return Query
def __init__(self, parent):
QComboBox.__init__(self, parent)
self.p_selected_id = 0
self.model = None
self.activated.connect(self.OnUserSelection)
self.query = QSqlQuery(db=db_connection())
self.query.prepare(f"SELECT id, name FROM assets WHERE type_id={PredefinedAsset.Money}")
self.query.exec()
self.model = QSqlTableModel(db=db_connection())
self.model.setQuery(self.query)
self.model.select()
self.setModel(self.model)
self.setModelColumn(self.model.fieldIndex("name"))
def __init__(self, parent):
QComboBox.__init__(self, parent)
self.p_selected_id = 0
self.model = None
self.activated.connect(self.OnUserSelection)
self.query = QSqlQuery(db=db_connection())
self.query.prepare(f"SELECT id, symbol FROM currencies")
self.query.exec()
self.model = QSqlTableModel(db=db_connection())
self.model.setQuery(self.query)
self.model.select()
self.setModel(self.model)
self.setModelColumn(self.model.fieldIndex("symbol"))
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 init_demo_table(self):
q = QSqlQuery(query="""
CREATE TABLE IF NOT EXISTS OpExecution (
id integer primary key autoincrement
status text not null default "pending"
completed int default null,
scheduled int default null
)
CREATE TABLE IF NOT EXISTS Demo (
id integer primary key autoincrement
title text not null default ''
path text not null
created int not null default (strftime('%s', 'now'))
updated int not null default (strftime('%s', 'now'))
)
CREATE TABLE IF NOT EXISTS OpSequence (
id integer primary key autoincrement
created int not null default (strftime('%s', 'now'))
updated int not null default (strftime('%s', 'now'))
)
CREATE TABLE IF NOT EXISTS OpStep (
id integer primary key autoincrement
name text default ""
seq_id integer not null
op_id integer not null
order_index integer not null
created int not null default (strftime('%s', 'now'))
updated int not null default (strftime('%s', 'now'))
foreign key('op_id') references Ops('id')
foreign key('seq_id') references OpSequence('id')
)
""")
def BatchInsert_Metadata(self, metadata):
"""
Batch Inserts data into library table
Parameters
----------
metadata: Dict
Distonary of all the combined metadata
"""
query = QSqlQuery()
columns = ", ".join(metadata.keys())
placeholders = ", ".join(["?" for i in range(len(metadata.keys()))])
query.prepare(
f"""INSERT OR IGNORE INTO library ({columns}) VALUES ({placeholders})"""
)
for keys in metadata.keys():
query.addBindValue(metadata.get(keys))
# sets up the transcation
self.db_driver.transaction()
self.ExeQuery("PRAGMA journal_mode = MEMORY")
if not query.execBatch(): # pragma: no cover
msg = f"""
ERROR: {(query.lastError().text())}
Query: {query.lastQuery()}
"""
raise QueryExecutionFailed(dedenter(msg, 12))
self.ExeQuery("PRAGMA journal_mode = WAL")
self.db_driver.commit()
class CurrencyComboBox(QComboBox):
changed = Signal(int)
def __init__(self, parent):
QComboBox.__init__(self, parent)
self.p_selected_id = 0
self.model = None
self.activated.connect(self.OnUserSelection)
self.query = QSqlQuery(db=db_connection())
self.query.prepare(f"SELECT id, symbol FROM currencies")
self.query.exec()
self.model = QSqlTableModel(db=db_connection())
self.model.setQuery(self.query)
self.model.select()
self.setModel(self.model)
self.setModelColumn(self.model.fieldIndex("symbol"))
def isCustom(self):
return True
def getId(self):
return self.p_selected_id
def setId(self, new_id):
if self.p_selected_id == new_id:
return
self.p_selected_id = new_id
name = readSQL("SELECT symbol FROM currencies WHERE id=:id",
[(":id", self.p_selected_id)])
if self.currentIndex() == self.findText(name):
return
self.setCurrentIndex(self.findText(name))
selected_id = Property(int, getId, setId, notify=changed, user=True)
def setIndex(self, index):
if index is not None:
self.selected_id = index
self.changed.emit(self.selected_id)
@Slot()
def OnUserSelection(self, _selected_index):
self.selected_id = self.model.record(self.currentIndex()).value("id")
self.changed.emit(self.selected_id)
def FileChecker(self, filepath, FileHashList, BatchMetadata):
QSqlQuery("BEGIN TRANSCATION").exec_()
for ID, file in filepath.items():
Filehash = self.FileHasher(file)
if (Filehash not in FileHashList):
FileHashList.append(Filehash)
Metadata = MediaFile(file).getMetadata()
Metadata["path_id"] = ID
Metadata["file_id"] = Filehash
BatchMetadata.append(list(Metadata.values()))
def test_ExeQuery(self, DBManager):
Manager = DBManager
# string query complete execution
query = Manager.ExeQuery("""
SELECT IIF(name = 'library', TRUE, FALSE)
FROM sqlite_master WHERE type = 'table'
""")
assert isinstance(query, QSqlQuery)
# SQLQuery object complete execution
query = Manager.ExeQuery(QSqlQuery("""
SELECT IIF(name = 'library', TRUE, FALSE)
FROM sqlite_master WHERE type = 'table'
"""))
assert isinstance(query, QSqlQuery)
# string build failing
try:
query = Manager.ExeQuery("""
SELECT IIF(name = 'library', TRUE, FALSE)
FROM sqlite_master type = 'table'
""")
except QueryBuildFailed: assert True
else: assert False
# SQLQuery object execution failing
try:
query = Manager.ExeQuery(QSqlQuery("""
SELECT IIF(name = 'library', TRUE, FALSE)
FROM sqlite_master type = 'table'
"""))
except QueryExecutionFailed: assert True
else: assert False
Manager.close_connection() # cleanup
def init_db():
"""
init_db()
Initializes the database.
If tables "books" and "authors" are already in the database, do nothing.
Return value: None or raises ValueError
The error value is the QtSql error instance.
"""
def check(func, *args):
if not func(*args):
raise ValueError(func.__self__.lastError())
db = QSqlDatabase.addDatabase("QSQLITE")
db.setDatabaseName(":memory:")
check(db.open)
q = QSqlQuery()
check(q.exec_, BOOKS_SQL)
check(q.exec_, AUTHORS_SQL)
check(q.exec_, GENRES_SQL)
check(q.prepare, INSERT_AUTHOR_SQL)
asimovId = add_author(q, "Isaac Asimov", date(1920, 2, 1))
greeneId = add_author(q, "Graham Greene", date(1904, 10, 2))
pratchettId = add_author(q, "Terry Pratchett", date(1948, 4, 28))
check(q.prepare, INSERT_GENRE_SQL)
sfiction = add_genre(q, "Science Fiction")
fiction = add_genre(q, "Fiction")
fantasy = add_genre(q, "Fantasy")
check(q.prepare, INSERT_BOOK_SQL)
add_book(q, "Foundation", 1951, asimovId, sfiction, 3)
add_book(q, "Foundation and Empire", 1952, asimovId, sfiction, 4)
add_book(q, "Second Foundation", 1953, asimovId, sfiction, 3)
add_book(q, "Foundation's Edge", 1982, asimovId, sfiction, 3)
add_book(q, "Foundation and Earth", 1986, asimovId, sfiction, 4)
add_book(q, "Prelude to Foundation", 1988, asimovId, sfiction, 3)
add_book(q, "Forward the Foundation", 1993, asimovId, sfiction, 3)
add_book(q, "The Power and the Glory", 1940, greeneId, fiction, 4)
add_book(q, "The Third Man", 1950, greeneId, fiction, 5)
add_book(q, "Our Man in Havana", 1958, greeneId, fiction, 4)
add_book(q, "Guards! Guards!", 1989, pratchettId, fantasy, 3)
add_book(q, "Night Watch", 2002, pratchettId, fantasy, 3)
add_book(q, "Going Postal", 2004, pratchettId, fantasy, 3)
def executeSQL(sql_text, params=[], forward_only=True, commit=False):
db = db_connection()
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
if commit:
db.commit()
return query
def ScanDirectory(self, Dir, include=[], Slot=lambda: ''):
BatchMetadata = []
FileHashList = []
file_paths = {}
for D, SD, F in os.walk(os.path.normpath(Dir)):
for file in F:
if os.path.splitext(file)[1] in include:
file = os.path.normpath(os.path.join(D, file))
file_paths[(hashlib.md5(file.encode())).hexdigest()] = file
Slot(f"Scanning {Dir}")
ID = ", ".join([f"'{v}'" for v in set(file_paths.keys())])
query = QSqlQuery(
f"SELECT path_id FROM library WHERE path_id IN ({ID})")
query.exec_()
while query.next():
del file_paths[query.value(0)]
self.FileChecker(file_paths, FileHashList, BatchMetadata)
self.BatchInsert_Metadata(self.TransposeMeatadata(BatchMetadata))
Slot(f"Completed Scanning {Dir}")
class OperationsModel(QAbstractTableModel):
PAGE_SIZE = 100
_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)
}
def __init__(self, parent_view):
super().__init__(parent_view)
self._columns = [
" ",
self.tr("Timestamp"),
self.tr("Account"),
self.tr("Notes"),
self.tr("Amount"),
self.tr("Balance"),
self.tr("Currency")
]
self.CorpActionNames = {
CorporateAction.SymbolChange:
self.tr("Symbol change {old} -> {new}"),
CorporateAction.Split:
self.tr("Split {old} {before} into {after}"),
CorporateAction.SpinOff:
self.tr("Spin-off {after} {new} from {before} {old}"),
CorporateAction.Merger:
self.tr("Merger {before} {old} into {after} {new}"),
CorporateAction.StockDividend:
self.tr("Stock dividend: {after} {new}")
}
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
while (i < self.PAGE_SIZE) and self._query.next():
values = readSQLrecord(self._query, named=True)
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]['type'], self._data[row]['id']
else:
return [0, 0]
def data(self, index, role=Qt.DisplayRole, field=''):
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 and self._view.isEnabled():
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
if role == Qt.UserRole: # return underlying data for given field extra parameter
return self._data[index.row()][field]
def data_text(self, row, column):
if column == 0:
try:
return self.OperationSign[self._data[row]['type'],
self._data[row]['subtype']][0]
except KeyError:
return '?'
elif column == 1:
if (self._data[row]['type'] == TransactionType.Trade) or (self._data[row]['type'] == TransactionType.Dividend) \
or (self._data[row]['type'] == TransactionType.CorporateAction):
return f"{datetime.utcfromtimestamp(self._data[row]['timestamp']).strftime('%d/%m/%Y %H:%M:%S')}\n# {self._data[row]['num_peer']}"
else:
return datetime.utcfromtimestamp(
self._data[row]['timestamp']).strftime('%d/%m/%Y %H:%M:%S')
elif column == 2:
if self._data[row]['type'] == TransactionType.Action:
return self._data[row]['account']
elif (self._data[row]['type'] == TransactionType.Trade) \
or (self._data[row]['type'] == TransactionType.Dividend) \
or (self._data[row]['type'] == TransactionType.CorporateAction):
return self._data[row]['account'] + "\n" + self._data[row][
'asset_name']
elif self._data[row]['type'] == TransactionType.Transfer:
if self._data[row]['subtype'] == TransferSubtype.Fee:
return self._data[row]['account']
elif self._data[row]['subtype'] == TransferSubtype.Outgoing:
return self._data[row]['account'] + " -> " + self._data[
row]['note2']
elif self._data[row]['subtype'] == TransferSubtype.Incoming:
return self._data[row]['account'] + " <- " + self._data[
row]['note2']
elif column == 3:
if self._data[row]['type'] == TransactionType.Action:
note = self._data[row]['num_peer']
if self._data[row]['asset'] != '' and self._data[row][
'fee_tax'] != 0:
note += "\n" + self.tr("Rate: ")
if self._data[row]['fee_tax'] >= 1:
note += f"{self._data[row]['fee_tax']:.4f} " \
f"{self._data[row]['asset']}/{self._data[row]['currency']}"
else:
note += f"{1/self._data[row]['fee_tax']:.4f} " \
f"{self._data[row]['currency']}/{self._data[row]['asset']}"
return note
elif self._data[row]['type'] == TransactionType.Transfer:
rate = 0 if self._data[row]['price'] == '' else self._data[
row]['price']
if self._data[row]['currency'] != self._data[row]['num_peer']:
if rate != 0:
if rate > 1:
return self._data[row][
'note'] + f" [1 {self._data[row]['currency']} = {rate:.4f} {self._data[row]['num_peer']}]"
elif rate < 1:
rate = 1 / rate
return self._data[row][
'note'] + f" [{rate:.4f} {self._data[row]['currency']} = 1 {self._data[row]['num_peer']}]"
else:
return self._data[row]['note']
else:
return self.tr("Error. Zero rate")
else:
return self._data[row]['note']
elif self._data[row]['type'] == TransactionType.Dividend:
return self._data[row]['note'] + "\n" + self.tr(
"Tax: ") + self._data[row]['note2']
elif self._data[row]['type'] == TransactionType.Trade:
if self._data[row]['fee_tax'] != 0:
text = f"{self._data[row]['qty_trid']:+.2f} @ {self._data[row]['price']:.4f}\n({self._data[row]['fee_tax']:.2f}) "
else:
text = f"{self._data[row]['qty_trid']:+.2f} @ {self._data[row]['price']:.4f}\n"
text = text + self._data[row]['note'] if self._data[row][
'note'] else text
return text
elif self._data[row]['type'] == TransactionType.CorporateAction:
basis = 100.0 * self._data[row]['price']
if self._data[row]['subtype'] == CorporateAction.StockDividend:
qty_after = self._data[row]['qty_trid'] - self._data[row][
'amount']
else:
qty_after = self._data[row]['qty_trid']
text = self.CorpActionNames[self._data[row]['subtype']].format(
old=self._data[row]['asset'],
new=self._data[row]['note'],
before=self._data[row]['amount'],
after=qty_after)
if self._data[row]['subtype'] == CorporateAction.SpinOff:
text += f"; {basis:.2f}% " + self.tr(
" cost basis") + "\n" + self._data[row]['note2']
return text
else:
assert False
elif column == 4:
if self._data[row]['type'] == TransactionType.Trade:
return [self._data[row]['amount'], self._data[row]['qty_trid']]
elif self._data[row]['type'] == TransactionType.Dividend:
return [self._data[row]['amount'], -self._data[row]['fee_tax']]
elif self._data[row]['type'] == TransactionType.Action:
if self._data[row]['asset'] != '':
return [
self._data[row]['amount'], self._data[row]['price']
]
else:
return [self._data[row]['amount']]
elif self._data[row]['type'] == TransactionType.Transfer:
return [self._data[row]['amount']]
elif self._data[row]['type'] == TransactionType.CorporateAction:
if self._data[row][
'subtype'] == CorporateAction.SpinOff or self._data[
row]['subtype'] == CorporateAction.StockDividend:
return [
None,
self._data[row]['qty_trid'] - self._data[row]['amount']
]
else:
return [
-self._data[row]['amount'], self._data[row]['qty_trid']
]
else:
assert False
elif column == 5:
upper_part = f"{self._data[row]['t_amount']:,.2f}" if self._data[
row]['t_amount'] != '' else "-.--"
lower_part = f"{self._data[row]['t_qty']:,.2f}" if self._data[row][
't_qty'] != '' else ''
if self._data[row]['type'] == TransactionType.CorporateAction:
qty_before = self._data[row]['amount'] if self._data[row][
'subtype'] == CorporateAction.SpinOff else 0
qty_after = self._data[row]['t_qty'] if self._data[row][
'subtype'] == CorporateAction.StockDividend else self._data[
row]['qty_trid']
if self._data[row]['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][
'type'] == TransactionType.Action or self._data[row][
'type'] == TransactionType.Transfer:
return upper_part
else:
return upper_part + "\n" + lower_part
elif column == 6:
if self._data[row]['type'] == TransactionType.CorporateAction:
asset_before = self._data[row]['asset'] if self._data[row][
'subtype'] != CorporateAction.StockDividend else ""
return f" {asset_before}\n {self._data[row]['note']}"
else:
if self._data[row]['asset'] != '':
return f" {self._data[row]['currency']}\n {self._data[row]['asset']}"
else:
return f" {self._data[row]['currency']}"
else:
assert False
def data_foreground(self, row, column):
if column == 0:
try:
return QBrush(
self.OperationSign[self._data[row]['type'],
self._data[row]['subtype']][1])
except KeyError:
return QBrush(CustomColor.LightRed)
if column == 5:
if self._data[row]['reconciled'] == 1:
return QBrush(CustomColor.Blue)
def configureView(self):
self._view.setColumnWidth(0, 10)
self._view.setColumnWidth(
1,
self._view.fontMetrics().horizontalAdvance("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]['type']
else:
return 0
@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]['type']]
query = f"DELETE FROM {table_name} WHERE id={self._data[row]['id']}"
_ = executeSQL(query)
self.prepareData()
def getValue(self, key, default=None):
get_query = QSqlQuery(self.db)
get_query.setForwardOnly(True)
get_query.prepare("SELECT value FROM settings WHERE name=:key")
value = default
get_query.bindValue(":key", key)
if not get_query.exec():
if not default:
logging.fatal(f"Failed to get settings for key='{key}'")
return value
if get_query.next():
value = get_query.value(0)
return value
