def refresh_session(self):
session_id = self.get_ru_tax_session()
if not session_id:
logging.info(self.tr("No valid session present"))
return SlipsTaxAPI.Failure
logging.info(self.tr("Refreshing session..."))
client_secret = JalSettings().getValue('RuTaxClientSecret')
refresh_token = JalSettings().getValue('RuTaxRefreshToken')
self.web_session.headers['sessionId'] = session_id
payload = '{' + f'"client_secret":"{client_secret}","refresh_token":"{refresh_token}"' + '}'
response = self.web_session.post(
'https://irkkt-mobile.nalog.ru:8888/v2/mobile/users/refresh',
data=payload)
if response.status_code == 200:
logging.info(self.tr("Session refreshed: ") + f"{response.text}")
json_content = json.loads(response.text)
new_session_id = json_content['sessionId']
new_refresh_token = json_content['refresh_token']
settings = JalSettings()
settings.setValue('RuTaxSessionId', new_session_id)
settings.setValue('RuTaxRefreshToken', new_refresh_token)
return SlipsTaxAPI.Pending # not Success as it is sent transparently to upper callers
else:
logging.error(
self.tr("Can't refresh session, response: ") +
f"{response}/{response.text}")
JalSettings().setValue('RuTaxSessionId', '')
if self.get_ru_tax_session():
return SlipsTaxAPI.Failure
else:
return SlipsTaxAPI.Pending # not Success as it is sent transparently to upper callers
def get_ru_tax_session(self):
stored_id = JalSettings().getValue('RuTaxSessionId')
if stored_id != '':
return stored_id
login_dialog = LoginFNS()
if login_dialog.exec() == QDialog.Accepted:
stored_id = JalSettings().getValue('RuTaxSessionId')
if stored_id is not None:
return stored_id
logging.warning(self.tr("No Russian Tax SessionId available"))
return ''
def refresh_session(self):
logging.info(g_tr('SlipsTaxAPI', "Refreshing session..."))
session_id = self.get_ru_tax_session()
client_secret = JalSettings().getValue('RuTaxClientSecret')
refresh_token = JalSettings().getValue('RuTaxRefreshToken')
s = requests.Session()
s.headers['ClientVersion'] = '2.9.0'
s.headers['Device-Id'] = str(uuid.uuid1())
s.headers['Device-OS'] = 'Android'
s.headers['sessionId'] = session_id
s.headers['Content-Type'] = 'application/json; charset=UTF-8'
s.headers['Accept-Encoding'] = 'gzip'
s.headers['User-Agent'] = 'okhttp/4.2.2'
payload = '{' + f'"client_secret":"{client_secret}","refresh_token":"{refresh_token}"' + '}'
response = s.post(
'https://irkkt-mobile.nalog.ru:8888/v2/mobile/users/refresh',
data=payload)
if response.status_code == 200:
logging.info(
g_tr('SlipsTaxAPI', "Session refreshed: ") +
f"{response.text}")
json_content = json.loads(response.text)
new_session_id = json_content['sessionId']
new_refresh_token = json_content['refresh_token']
settings = JalSettings()
settings.setValue('RuTaxSessionId', new_session_id)
settings.setValue('RuTaxRefreshToken', new_refresh_token)
return SlipsTaxAPI.Pending # not Success as it is sent transparently to upper callers
else:
logging.error(
g_tr('SlipsTaxAPI', "Can't refresh session, response: ") +
f"{response}/{response.text}")
return SlipsTaxAPI.Failure
def __init__(self, language):
QMainWindow.__init__(self, None)
self.running = False
self.setupUi(self)
self.restoreGeometry(base64.decodebytes(JalSettings().getValue('WindowGeometry', '').encode('utf-8')))
self.restoreState(base64.decodebytes(JalSettings().getValue('WindowState', '').encode('utf-8')))
self.ledger = Ledger()
# Customize Status bar and logs
self.ProgressBar = QProgressBar(self)
self.StatusBar.addPermanentWidget(self.ProgressBar)
self.ProgressBar.setVisible(False)
self.ledger.setProgressBar(self, self.ProgressBar)
self.Logs.setStatusBar(self.StatusBar)
self.logger = logging.getLogger()
self.logger.addHandler(self.Logs)
log_level = os.environ.get('LOGLEVEL', 'INFO').upper()
self.logger.setLevel(log_level)
self.currentLanguage = language
self.downloader = QuoteDownloader()
self.statements = Statements(self)
self.reports = Reports(self, self.mdiArea)
self.backup = JalBackup(self, get_dbfilename(get_app_path()))
self.estimator = None
self.price_chart = None
self.actionImportSlipRU.setEnabled(dependency_present(['pyzbar', 'PIL']))
self.actionAbout = QAction(text=self.tr("About"), parent=self)
self.MainMenu.addAction(self.actionAbout)
self.langGroup = QActionGroup(self.menuLanguage)
self.createLanguageMenu()
self.statementGroup = QActionGroup(self.menuStatement)
self.createStatementsImportMenu()
self.reportsGroup = QActionGroup(self.menuReports)
self.createReportsMenu()
self.setWindowIcon(load_icon("jal.png"))
self.connect_signals_and_slots()
self.actionOperations.trigger()
def main():
sys.excepthook = exception_logger
os.environ['QT_MAC_WANTS_LAYER'] = '1' # Workaround for https://bugreports.qt.io/browse/QTBUG-87014
error = init_and_check_db(get_app_path())
if error.code == LedgerInitError.EmptyDbInitialized: # If DB was just created from SQL - initialize it again
error = init_and_check_db(get_app_path())
app = QApplication([])
language = JalDB().get_language_code(JalSettings().getValue('Language', default=1))
translator = QTranslator(app)
language_file = get_app_path() + Setup.LANG_PATH + os.sep + language + '.qm'
translator.load(language_file)
app.installTranslator(translator)
if error.code == LedgerInitError.OutdatedDbSchema:
error = update_db_schema(get_app_path())
if error.code == LedgerInitError.DbInitSuccess:
error = init_and_check_db(get_app_path())
if error.code != LedgerInitError.DbInitSuccess:
window = QMessageBox()
window.setAttribute(Qt.WA_DeleteOnClose)
window.setWindowTitle("JAL: Start-up aborted")
window.setIcon(QMessageBox.Critical)
window.setText(error.message)
window.setInformativeText(error.details)
else:
window = MainWindow(language)
window.show()
app.exec()
app.removeTranslator(translator)
def output_accrued_interest(self, actions, trade_number, share, level):
interest = readSQL("SELECT b.symbol AS symbol, b.isin AS isin, i.timestamp AS o_date, i.number AS number, "
"i.amount AS interest, r.quote AS rate, cc.iso_code AS country_iso "
"FROM dividends AS i "
"LEFT JOIN accounts AS a ON a.id = i.account_id "
"LEFT JOIN assets_ext AS b ON b.id = i.asset_id AND b.currency_id=a.currency_id "
"LEFT JOIN countries AS cc ON cc.id = a.country_id "
"LEFT JOIN t_last_dates AS ld ON i.timestamp=ld.ref_id "
"LEFT JOIN quotes AS r ON ld.timestamp=r.timestamp AND a.currency_id=r.asset_id AND r.currency_id=:base_currency "
"WHERE i.account_id=:account_id AND i.type=:interest AND i.number=:trade_number",
[(":account_id", self.account_id), (":interest", Dividend.BondInterest),
(":trade_number", trade_number),
(":base_currency", JalSettings().getValue('BaseCurrency'))], named=True)
if interest is None:
return
interest['empty'] = ''
interest['interest'] = interest['interest'] if share == 1 else share * interest['interest']
interest['interest_rub'] = abs(round(interest['interest'] * interest['rate'], 2)) if interest['rate'] else 0
if interest['interest'] < 0: # Accrued interest paid for purchase
interest['interest'] = -interest['interest']
interest['operation'] = ' ' * level * 3 + "НКД уплачен"
interest['spending_rub'] = interest['interest_rub']
interest['income_rub'] = 0.0
else: # Accrued interest received for sale
interest['operation'] = ' ' * level * 3 + "НКД получен"
interest['income_rub'] = interest['interest_rub']
interest['spending_rub'] = 0.0
interest['report_template'] = "bond_interest"
actions.append(interest)
def init_db(self, db_path) -> JalDBError:
db = QSqlDatabase.addDatabase("QSQLITE", Setup.DB_CONNECTION)
if not db.isValid():
return JalDBError(JalDBError.DbDriverFailure)
db.setDatabaseName(get_dbfilename(db_path))
db.setConnectOptions("QSQLITE_ENABLE_REGEXP=1")
db.open()
sqlite_version = readSQL("SELECT sqlite_version()")
if parse_version(sqlite_version) < parse_version(
Setup.SQLITE_MIN_VERSION):
db.close()
return JalDBError(JalDBError.OutdatedSqlite)
tables = db.tables(QSql.Tables)
if not tables:
logging.info("Loading DB initialization script")
error = self.run_sql_script(db_path + Setup.INIT_SCRIPT_PATH)
if error.code != JalDBError.NoError:
return error
schema_version = JalSettings().getValue('SchemaVersion')
if schema_version < Setup.TARGET_SCHEMA:
db.close()
return JalDBError(JalDBError.OutdatedDbSchema)
elif schema_version > Setup.TARGET_SCHEMA:
db.close()
return JalDBError(JalDBError.NewerDbSchema)
_ = executeSQL("PRAGMA foreign_keys = ON")
db_triggers_enable()
return JalDBError(JalDBError.NoError)
def init_and_check_db(db_path):
db = QSqlDatabase.addDatabase("QSQLITE", Setup.DB_CONNECTION)
if not db.isValid():
return LedgerInitError(LedgerInitError.DbDriverFailure)
db.setDatabaseName(get_dbfilename(db_path))
db.setConnectOptions("QSQLITE_ENABLE_REGEXP=1")
db.open()
tables = db.tables(QSql.Tables)
if not tables:
db.close()
connection_name = db.connectionName()
init_db_from_sql(get_dbfilename(db_path), db_path + Setup.INIT_SCRIPT_PATH)
QSqlDatabase.removeDatabase(connection_name)
return LedgerInitError(LedgerInitError.EmptyDbInitialized)
schema_version = JalSettings().getValue('SchemaVersion')
if schema_version < Setup.TARGET_SCHEMA:
db.close()
return LedgerInitError(LedgerInitError.OutdatedDbSchema)
elif schema_version > Setup.TARGET_SCHEMA:
db.close()
return LedgerInitError(LedgerInitError.NewerDbSchema)
_ = executeSQL("PRAGMA foreign_keys = ON")
db_triggers_enable()
return LedgerInitError(LedgerInitError.DbInitSuccess)
def onClick(self):
if self.null_flag.isChecked():
if self.currency.selected_id == 0:
self.currency.selected_id = JalSettings().getValue('BaseCurrency')
self.currency_id = self.currency.selected_id
else:
self.currency_id = 0
self.changed.emit()
def login_fns(self):
client_secret = JalSettings().getValue('RuTaxClientSecret')
inn = self.InnEdit.text()
password = self.PasswordEdit.text()
s = requests.Session()
s.headers['ClientVersion'] = '2.9.0'
s.headers['Device-Id'] = str(uuid.uuid1())
s.headers['Device-OS'] = 'Android'
s.headers['Content-Type'] = 'application/json; charset=UTF-8'
s.headers['Accept-Encoding'] = 'gzip'
s.headers['User-Agent'] = 'okhttp/4.2.2'
payload = '{' + f'"client_secret":"{client_secret}","inn":"{inn}","password":"******"' + '}'
response = s.post(
'https://irkkt-mobile.nalog.ru:8888/v2/mobile/users/lkfl/auth',
data=payload)
if response.status_code != 200:
logging.error(
g_tr('SlipsTaxAPI', "FNS login failed: ") +
f"{response}/{response.text}")
return
logging.info(
g_tr('SlipsTaxAPI', "FNS login successful: ") + f"{response.text}")
json_content = json.loads(response.text)
new_session_id = json_content['sessionId']
new_refresh_token = json_content['refresh_token']
settings = JalSettings()
settings.setValue('RuTaxSessionId', new_session_id)
settings.setValue('RuTaxRefreshToken', new_refresh_token)
self.accept()
def __init__(self, parent=None):
MdiWidget.__init__(self, parent)
self.setupUi(self)
self.current_index = None # this is used in onOperationContextMenu() to track item for menu
# Set icons
self.NewOperationBtn.setIcon(load_icon("new.png"))
self.CopyOperationBtn.setIcon(load_icon("copy.png"))
self.DeleteOperationBtn.setIcon(load_icon("delete.png"))
# Operations view context menu
self.contextMenu = QMenu(self.OperationsTableView)
self.actionReconcile = QAction(load_icon("reconcile.png"),
self.tr("Reconcile"), self)
self.actionCopy = QAction(load_icon("copy.png"), self.tr("Copy"), self)
self.actionDelete = QAction(load_icon("delete.png"), self.tr("Delete"),
self)
self.contextMenu.addAction(self.actionReconcile)
self.contextMenu.addSeparator()
self.contextMenu.addAction(self.actionCopy)
self.contextMenu.addAction(self.actionDelete)
# Customize UI configuration
self.balances_model = BalancesModel(self.BalancesTableView)
self.BalancesTableView.setModel(self.balances_model)
self.balances_model.configureView()
self.operations_model = OperationsModel(self.OperationsTableView)
self.OperationsTableView.setModel(self.operations_model)
self.operations_model.configureView()
self.OperationsTableView.setContextMenuPolicy(Qt.CustomContextMenu)
self.connect_signals_and_slots()
self.NewOperationMenu = QMenu()
for i in range(self.OperationsTabs.count()):
if hasattr(self.OperationsTabs.widget(i), "isCustom"):
self.OperationsTabs.widget(i).dbUpdated.connect(self.dbUpdated)
self.OperationsTabs.widget(i).dbUpdated.connect(
self.operations_model.refresh)
self.NewOperationMenu.addAction(
self.OperationsTabs.widget(i).name,
partial(self.createOperation, i))
self.NewOperationBtn.setMenu(self.NewOperationMenu)
# Setup balance and holdings parameters
current_time = QDateTime.currentDateTime()
current_time.setTimeSpec(
Qt.UTC) # We use UTC everywhere so need to force TZ info
self.BalanceDate.setDateTime(current_time)
self.BalancesCurrencyCombo.setIndex(
JalSettings().getValue('BaseCurrency'))
self.OperationsTabs.setCurrentIndex(LedgerTransaction.NA)
self.OperationsTableView.selectRow(0)
self.DateRange.setCurrentIndex(0)
def prepare_derivatives(self):
derivatives = []
# Take all actions without conversion
query = executeSQL("SELECT s.symbol, d.qty AS qty, cc.iso_code AS country_iso, "
"o.timestamp AS o_date, qo.quote AS o_rate, o.settlement AS os_date, o.number AS o_number, "
"qos.quote AS os_rate, o.price AS o_price, o.qty AS o_qty, o.fee AS o_fee, "
"c.timestamp AS c_date, qc.quote AS c_rate, c.settlement AS cs_date, c.number AS c_number, "
"qcs.quote AS cs_rate, c.price AS c_price, c.qty AS c_qty, c.fee AS c_fee "
"FROM deals AS d "
"JOIN trades AS o ON o.id=d.open_op_id AND o.op_type=d.open_op_type "
"JOIN trades AS c ON c.id=d.close_op_id AND c.op_type=d.close_op_type "
"LEFT JOIN accounts AS a ON a.id = :account_id "
"LEFT JOIN assets_ext AS s ON s.id = o.asset_id AND s.currency_id=a.currency_id "
"LEFT JOIN countries AS cc ON cc.id = a.country_id "
"LEFT JOIN t_last_dates AS ldo ON o.timestamp=ldo.ref_id "
"LEFT JOIN quotes AS qo ON ldo.timestamp=qo.timestamp AND a.currency_id=qo.asset_id AND qo.currency_id=:base_currency "
"LEFT JOIN t_last_dates AS ldos ON o.settlement=ldos.ref_id "
"LEFT JOIN quotes AS qos ON ldos.timestamp=qos.timestamp AND a.currency_id=qos.asset_id AND qos.currency_id=:base_currency "
"LEFT JOIN t_last_dates AS ldc ON c.timestamp=ldc.ref_id "
"LEFT JOIN quotes AS qc ON ldc.timestamp=qc.timestamp AND a.currency_id=qc.asset_id AND qc.currency_id=:base_currency "
"LEFT JOIN t_last_dates AS ldcs ON c.settlement=ldcs.ref_id "
"LEFT JOIN quotes AS qcs ON ldcs.timestamp=qcs.timestamp AND a.currency_id=qcs.asset_id AND qcs.currency_id=:base_currency "
"WHERE c.settlement>=:begin AND c.settlement<:end AND d.account_id=:account_id "
"AND s.type_id = :derivative "
"ORDER BY s.symbol, o.timestamp, c.timestamp",
[(":begin", self.year_begin), (":end", self.year_end), (":account_id", self.account_id),
(":base_currency", JalSettings().getValue('BaseCurrency')),
(":derivative", PredefinedAsset.Derivative)])
while query.next():
deal = readSQLrecord(query, named=True)
if not self.use_settlement:
deal['os_rate'] = deal['o_rate']
deal['cs_rate'] = deal['c_rate']
deal['o_type'] = "Покупка" if deal['qty'] >= 0 else "Продажа"
deal['c_type'] = "Продажа" if deal['qty'] >= 0 else "Покупка"
deal['o_amount'] = round(deal['o_price'] * abs(deal['qty']), 2)
deal['o_amount_rub'] = round(deal['o_amount'] * deal['os_rate'], 2) if deal['os_rate'] else 0
deal['c_amount'] = round(deal['c_price'] * abs(deal['qty']), 2)
deal['c_amount_rub'] = round(deal['c_amount'] * deal['cs_rate'], 2) if deal['cs_rate'] else 0
deal['o_fee'] = deal['o_fee'] * abs(deal['qty'] / deal['o_qty'])
deal['c_fee'] = deal['c_fee'] * abs(deal['qty'] / deal['c_qty'])
deal['o_fee_rub'] = round(deal['o_fee'] * deal['o_rate'], 2) if deal['o_rate'] else 0
deal['c_fee_rub'] = round(deal['c_fee'] * deal['c_rate'], 2) if deal['c_rate'] else 0
deal['income_rub'] = deal['c_amount_rub'] if deal['qty'] >= 0 else deal['o_amount_rub']
deal['income'] = deal['c_amount'] if deal['qty'] >= 0 else deal['o_amount']
deal['spending_rub'] = deal['o_amount_rub'] if deal['qty'] >= 0 else deal['c_amount_rub']
deal['spending_rub'] = deal['spending_rub'] + deal['o_fee_rub'] + deal['c_fee_rub']
deal['spending'] = deal['o_amount'] if deal['qty'] >= 0 else deal['c_amount']
deal['spending'] = deal['spending'] + deal['o_fee'] + deal['c_fee']
deal['profit_rub'] = deal['income_rub'] - deal['spending_rub']
deal['profit'] = deal['income'] - deal['spending']
deal['report_template'] = "trade"
derivatives.append(deal)
self.insert_totals(derivatives, ["income_rub", "spending_rub", "profit_rub", "profit"])
return derivatives
def onLanguageChanged(self, action):
language_code = action.data()
if language_code != self.currentLanguage:
JalSettings().setValue('Language', JalDB().get_language_id(language_code))
QMessageBox().information(self, self.tr("Restart required"),
self.tr("Language was changed to ") +
QLocale.languageToString(QLocale(language_code).language()) + "\n" +
self.tr("You should restart application to apply changes\n"
"Application will be terminated now"),
QMessageBox.Ok)
self.close()
def send_sms(self):
client_secret = JalSettings().getValue('RuTaxClientSecret')
self.phone_number = self.PhoneNumberEdit.text().replace('-', '')
payload = '{' + f'"client_secret":"{client_secret}","phone":"{self.phone_number}"' + '}'
response = self.web_session.post(
'https://irkkt-mobile.nalog.ru:8888/v2/auth/phone/request',
data=payload)
if response.status_code != 204:
logging.error(
self.tr("FNS login failed: ") + f"{response}/{response.text}")
else:
logging.info(self.tr("SMS was requested successfully"))
def currency_id(self, currency_symbol) -> int:
match = [x for x in self._data[FOF.SYMBOLS] if
x['symbol'] == currency_symbol and self._asset(x['asset'])['type'] == FOF.ASSET_MONEY]
if match:
if len(match) == 1:
return match[0]["asset"]
else:
raise Statement_ImportError(self.tr("Multiple currency match for ") + f"{currency_symbol}")
else:
asset_id = max([0] + [x['id'] for x in self._data[FOF.ASSETS]]) + 1
self._data[FOF.ASSETS].append({"id": asset_id, "type": "money", "name": ""})
symbol_id = max([0] + [x['id'] for x in self._data[FOF.SYMBOLS]]) + 1
currency = {"id": symbol_id, "asset": asset_id, "symbol": currency_symbol,
"currency": -JalSettings().getValue('BaseCurrency')}
self._data[FOF.SYMBOLS].append(currency)
return asset_id
def output_purchase(self, actions, operation_id, proceed_qty, basis, level, group):
if proceed_qty <= 0:
return proceed_qty
purchase = readSQL("SELECT t.id AS trade_id, s.symbol, s.isin AS isin, s.type_id AS type_id, "
"coalesce(d.qty-SUM(lq.total_value), d.qty) AS qty, "
"t.timestamp AS t_date, qt.quote AS t_rate, t.number AS trade_number, "
"t.settlement AS s_date, qts.quote AS s_rate, t.price AS price, t.fee AS fee "
"FROM trades AS t "
"JOIN deals AS d ON t.id=d.open_op_id AND t.op_type=d.open_op_type "
"LEFT JOIN accounts AS a ON a.id = t.account_id "
"LEFT JOIN assets_ext AS s ON s.id = t.asset_id AND s.currency_id=a.currency_id "
"LEFT JOIN t_last_dates AS ldt ON t.timestamp=ldt.ref_id "
"LEFT JOIN quotes AS qt ON ldt.timestamp=qt.timestamp AND a.currency_id=qt.asset_id AND qt.currency_id=:base_currency "
"LEFT JOIN t_last_dates AS ldts ON t.settlement=ldts.ref_id "
"LEFT JOIN quotes AS qts ON ldts.timestamp=qts.timestamp AND a.currency_id=qts.asset_id AND qts.currency_id=:base_currency "
"LEFT JOIN t_last_assets AS lq ON lq.id = t.id "
"WHERE t.id = :operation_id", [(":operation_id", operation_id),
(":base_currency", JalSettings().getValue('BaseCurrency'))],
named=True)
if purchase['qty'] <= (2 * Setup.CALC_TOLERANCE):
return proceed_qty # This trade was fully mached before
purchase['operation'] = ' ' * level * 3 + "Покупка"
purchase['basis_ratio'] = 100.0 * basis
deal_qty = purchase['qty']
purchase['qty'] = proceed_qty if proceed_qty < deal_qty else deal_qty
purchase['amount'] = round(purchase['price'] * purchase['qty'], 2)
purchase['amount_rub'] = round(purchase['amount'] * purchase['s_rate'], 2) if purchase['s_rate'] else 0
purchase['fee'] = purchase['fee'] * purchase['qty'] / deal_qty
purchase['fee_rub'] = round(purchase['fee'] * purchase['t_rate'], 2) if purchase['t_rate'] else 0
purchase['income_rub'] = 0
purchase['spending_rub'] = round(basis*(purchase['amount_rub'] + purchase['fee_rub']), 2)
_ = executeSQL("INSERT INTO t_last_assets (id, total_value) VALUES (:trade_id, :qty)",
[(":trade_id", purchase['trade_id']), (":qty", purchase['qty'])])
if level >= 0: # Don't output if level==-1, i.e. corp action is out of report scope
purchase['report_template'] = "trade"
purchase['report_group'] = group
actions.append(purchase)
if purchase['type_id'] == PredefinedAsset.Bond:
share = purchase['qty'] / deal_qty if purchase['qty'] < deal_qty else 1
self.output_accrued_interest(actions, purchase['trade_number'], share, level)
return proceed_qty - purchase['qty']
def __init__(self, parent=None):
MdiWidget.__init__(self, parent)
self.setupUi(self)
self.parent_mdi = parent
self.holdings_model = HoldingsModel(self.HoldingsTableView)
self.HoldingsTableView.setModel(self.holdings_model)
self.holdings_model.configureView()
self.HoldingsTableView.setContextMenuPolicy(Qt.CustomContextMenu)
self.connect_signals_and_slots()
# Setup holdings parameters
current_time = QDateTime.currentDateTime()
current_time.setTimeSpec(
Qt.UTC) # We use UTC everywhere so need to force TZ info
self.HoldingsDate.setDateTime(current_time)
self.HoldingsCurrencyCombo.setIndex(
JalSettings().getValue('BaseCurrency'))
def calculateIncomeSpendings(self):
query = executeSQL("WITH "
"_months AS (SELECT strftime('%s', datetime(timestamp, 'unixepoch', 'start of month') ) "
"AS month, asset_id, MAX(timestamp) AS last_timestamp "
"FROM quotes AS q "
"LEFT JOIN assets AS a ON q.asset_id=a.id "
"WHERE a.type_id=:asset_money "
"GROUP BY month, asset_id), "
"_category_amounts AS ( "
"SELECT strftime('%s', datetime(t.timestamp, 'unixepoch', 'start of month')) AS month_start, "
"t.category_id AS id, sum(-t.amount * coalesce(q.quote, 1)) AS amount "
"FROM ledger AS t "
"LEFT JOIN _months AS d ON month_start = d.month AND t.asset_id = d.asset_id "
"LEFT JOIN quotes AS q ON d.last_timestamp = q.timestamp AND t.asset_id = q.asset_id "
"AND q.currency_id=:base_currency "
"WHERE (t.book_account=:book_costs OR t.book_account=:book_incomes) "
"AND t.timestamp>=:begin AND t.timestamp<=:end "
"GROUP BY month_start, category_id) "
"SELECT ct.level, ct.id, c.pid, c.name, ct.path, ca.month_start, "
"coalesce(ca.amount, 0) AS amount "
"FROM categories_tree AS ct "
"LEFT JOIN _category_amounts AS ca ON ct.id=ca.id "
"LEFT JOIN categories AS c ON ct.id=c.id "
"ORDER BY path, month_start",
[(":asset_money", PredefinedAsset.Money), (":book_costs", BookAccount.Costs),
(":book_incomes", BookAccount.Incomes), (":begin", self._begin), (":end", self._end),
(":base_currency", JalSettings().getValue('BaseCurrency'))], forward_only=True)
self._root = ReportTreeItem(self._begin, self._end, -1, "ROOT") # invisible root
self._root.appendChild(ReportTreeItem(self._begin, self._end, 0, self.tr("TOTAL"))) # visible root
indexes = range(query.record().count())
while query.next():
values = list(map(query.value, indexes))
leaf = self._root.getLeafById(values[self.COL_ID])
if leaf is None:
parent = self._root.getLeafById(values[self.COL_PID])
leaf = ReportTreeItem(self._begin, self._end, values[self.COL_ID], values[self.COL_NAME], parent)
parent.appendChild(leaf)
if values[self.COL_TIMESTAMP]:
year = int(datetime.utcfromtimestamp(int(values[self.COL_TIMESTAMP])).strftime('%Y'))
month = int(datetime.utcfromtimestamp(int(values[self.COL_TIMESTAMP])).strftime('%m').lstrip('0'))
leaf.addAmount(year, month, values[self.COL_AMOUNT])
self.modelReset.emit()
self._view.expandAll()
def prepare_dividends(self):
dividends = []
query = executeSQL("SELECT d.type, d.timestamp AS payment_date, s.symbol, s.full_name AS full_name, "
"s.isin AS isin, d.amount AS amount, d.tax AS tax, q.quote AS rate, p.quote AS price, "
"c.name AS country, c.iso_code AS country_iso, c.tax_treaty AS tax_treaty "
"FROM dividends AS d "
"LEFT JOIN accounts AS a ON d.account_id = a.id "
"LEFT JOIN assets_ext AS s ON s.id = d.asset_id AND s.currency_id=a.currency_id "
"LEFT JOIN countries AS c ON s.country_id = c.id "
"LEFT JOIN t_last_dates AS ld ON d.timestamp=ld.ref_id "
"LEFT JOIN quotes AS q ON ld.timestamp=q.timestamp AND a.currency_id=q.asset_id AND q.currency_id=:base_currency "
"LEFT JOIN quotes AS p ON d.timestamp=p.timestamp AND d.asset_id=p.asset_id AND p.currency_id=a.currency_id "
"WHERE d.timestamp>=:begin AND d.timestamp<:end AND d.account_id=:account_id "
" AND d.amount>0 AND (d.type=:type_dividend OR d.type=:type_stock_dividend) "
"ORDER BY d.timestamp",
[(":begin", self.year_begin), (":end", self.year_end), (":account_id", self.account_id),
(":base_currency", JalSettings().getValue('BaseCurrency')),
(":type_dividend", Dividend.Dividend), (":type_stock_dividend", Dividend.StockDividend)])
while query.next():
dividend = readSQLrecord(query, named=True)
dividend["note"] = ''
if dividend["type"] == Dividend.StockDividend:
if not dividend["price"]:
logging.error(self.tr("No price data for stock dividend: ") + f"{dividend}")
continue
dividend["amount"] = dividend["amount"] * dividend["price"]
dividend["note"] = "Дивиденд выплачен в натуральной форме (ценными бумагами)"
dividend["amount_rub"] = round(dividend["amount"] * dividend["rate"], 2) if dividend["rate"] else 0
dividend["tax_rub"] = round(dividend["tax"] * dividend["rate"], 2) if dividend["rate"] else 0
dividend["tax2pay"] = round(0.13 * dividend["amount_rub"], 2)
if dividend["tax_treaty"]:
if dividend["tax2pay"] > dividend["tax_rub"]:
dividend["tax2pay"] = dividend["tax2pay"] - dividend["tax_rub"]
else:
dividend["tax2pay"] = 0
dividend['tax_treaty'] = "Да" if dividend['tax_treaty'] else "Нет"
dividend['report_template'] = "dividend"
del dividend['type']
del dividend['price']
dividends.append(dividend)
self.insert_totals(dividends, ["amount", "amount_rub", "tax", "tax_rub", "tax2pay"])
return dividends
def response_esia(self, auth_code, state):
client_secret = JalSettings().getValue('RuTaxClientSecret')
payload = '{' + f'"authorization_code": "{auth_code}", "client_secret": "{client_secret}", "state": "{state}"' \
+ '}'
response = self.web_session.post(
'https://irkkt-mobile.nalog.ru:8888/v2/mobile/users/esia/auth',
data=payload)
if response.status_code != 200:
logging.error(
self.tr("ESIA login failed: ") + f"{response}/{response.text}")
return
logging.info(self.tr("ESIA login successful: ") + f"{response.text}")
json_content = json.loads(response.text)
new_session_id = json_content['sessionId']
new_refresh_token = json_content['refresh_token']
settings = JalSettings()
settings.setValue('RuTaxSessionId', new_session_id)
settings.setValue('RuTaxRefreshToken', new_refresh_token)
self.accept()
def prepare_broker_fees(self):
fees = []
query = executeSQL("SELECT a.timestamp AS payment_date, d.amount AS amount, d.note AS note, q.quote AS rate "
"FROM actions AS a "
"LEFT JOIN action_details AS d ON d.pid=a.id "
"LEFT JOIN accounts AS c ON c.id = :account_id "
"LEFT JOIN t_last_dates AS ld ON a.timestamp=ld.ref_id "
"LEFT JOIN quotes AS q ON ld.timestamp=q.timestamp AND c.currency_id=q.asset_id AND q.currency_id=:base_currency "
"WHERE a.timestamp>=:begin AND a.timestamp<:end "
"AND a.account_id=:account_id AND d.category_id=:fee",
[(":begin", self.year_begin), (":end", self.year_end),
(":account_id", self.account_id), (":fee", PredefinedCategory.Fees),
(":base_currency", JalSettings().getValue('BaseCurrency'))])
while query.next():
fee = readSQLrecord(query, named=True)
fee['amount'] = -fee['amount']
fee['amount_rub'] = round(fee['amount'] * fee['rate'], 2) if fee['rate'] else 0
fee['report_template'] = "fee"
fees.append(fee)
self.insert_totals(fees, ["amount", "amount_rub"])
return fees
def prepare_exchange_rate_dates(self):
_ = executeSQL("DELETE FROM t_last_dates")
_ = executeSQL("INSERT INTO t_last_dates(ref_id, timestamp) "
"SELECT ref_id, coalesce(MAX(q.timestamp), 0) AS timestamp "
"FROM ("
"SELECT d.timestamp AS ref_id FROM dividends AS d WHERE d.account_id = :account_id "
"UNION "
"SELECT a.timestamp AS ref_id FROM actions AS a WHERE a.account_id = :account_id "
"UNION "
"SELECT d.open_timestamp AS ref_id FROM deals AS d WHERE d.account_id=:account_id "
"UNION "
"SELECT d.close_timestamp AS ref_id FROM deals AS d WHERE d.account_id=:account_id "
"UNION "
"SELECT c.settlement AS ref_id FROM deals AS d LEFT JOIN trades AS c ON "
"(c.id=d.open_op_id AND c.op_type=d.open_op_type) OR (c.id=d.close_op_id AND c.op_type=d.close_op_type) "
"WHERE d.account_id = :account_id) "
"LEFT JOIN accounts AS a ON a.id = :account_id "
"LEFT JOIN quotes AS q ON ref_id >= q.timestamp "
"AND a.currency_id=q.asset_id AND q.currency_id=:base_currency "
"WHERE ref_id IS NOT NULL "
"GROUP BY ref_id", [(":account_id", self.account_id),
(":base_currency", JalSettings().getValue('BaseCurrency'))], commit=True)
def login_fns(self):
client_secret = JalSettings().getValue('RuTaxClientSecret')
inn = self.InnEdit.text()
password = self.PasswordEdit.text()
payload = '{' + f'"client_secret":"{client_secret}","inn":"{inn}","password":"******"' + '}'
response = self.web_session.post(
'https://irkkt-mobile.nalog.ru:8888/v2/mobile/users/lkfl/auth',
data=payload)
if response.status_code != 200:
logging.error(
self.tr("FNS login failed: ") + f"{response}/{response.text}")
return
logging.info(self.tr("FNS login successful: ") + f"{response.text}")
json_content = json.loads(response.text)
new_session_id = json_content['sessionId']
new_refresh_token = json_content['refresh_token']
settings = JalSettings()
settings.setValue('RuTaxSessionId', new_session_id)
settings.setValue('RuTaxRefreshToken', new_refresh_token)
self.accept()
def login_sms(self):
if not self.phone_number:
return
client_secret = JalSettings().getValue('RuTaxClientSecret')
code = self.CodeEdit.text()
payload = '{' + f'"client_secret":"{client_secret}","code":"{code}","phone":"{self.phone_number}"' + '}'
response = self.web_session.post(
'https://irkkt-mobile.nalog.ru:8888/v2/auth/phone/verify',
data=payload)
if response.status_code != 200:
logging.error(
self.tr("FNS login failed: ") + f"{response}/{response.text}")
return
logging.info(self.tr("FNS login successful: ") + f"{response.text}")
json_content = json.loads(response.text)
new_session_id = json_content['sessionId']
new_refresh_token = json_content['refresh_token']
settings = JalSettings()
settings.setValue('RuTaxSessionId', new_session_id)
settings.setValue('RuTaxRefreshToken', new_refresh_token)
self.accept()
def __init__(self, language):
QMainWindow.__init__(self, None)
self.setupUi(self)
self.currentLanguage = language
self.current_index = None # this is used in onOperationContextMenu() to track item for menu
self.ledger = Ledger()
self.downloader = QuoteDownloader()
self.taxes = TaxesRus()
self.statements = StatementLoader()
self.backup = JalBackup(self, get_dbfilename(get_app_path()))
self.estimator = None
self.price_chart = None
self.actionImportSlipRU.setEnabled(
dependency_present(['pyzbar', 'PIL']))
self.actionAbout = QAction(text=self.tr("About"), parent=self)
self.MainMenu.addAction(self.actionAbout)
self.langGroup = QActionGroup(self.menuLanguage)
self.createLanguageMenu()
self.statementGroup = QActionGroup(self.menuStatement)
self.createStatementsImportMenu()
# Set icons
self.setWindowIcon(load_icon("jal.png"))
self.NewOperationBtn.setIcon(load_icon("new.png"))
self.CopyOperationBtn.setIcon(load_icon("copy.png"))
self.DeleteOperationBtn.setIcon(load_icon("delete.png"))
# Operations view context menu
self.contextMenu = QMenu(self.OperationsTableView)
self.actionReconcile = QAction(load_icon("reconcile.png"),
self.tr("Reconcile"), self)
self.actionCopy = QAction(load_icon("copy.png"), self.tr("Copy"), self)
self.actionDelete = QAction(load_icon("delete.png"), self.tr("Delete"),
self)
self.contextMenu.addAction(self.actionReconcile)
self.contextMenu.addSeparator()
self.contextMenu.addAction(self.actionCopy)
self.contextMenu.addAction(self.actionDelete)
# Customize Status bar and logs
self.ProgressBar = QProgressBar(self)
self.StatusBar.addWidget(self.ProgressBar)
self.ProgressBar.setVisible(False)
self.ledger.setProgressBar(self, self.ProgressBar)
self.NewLogEventLbl = QLabel(self)
self.StatusBar.addWidget(self.NewLogEventLbl)
self.Logs.setNotificationLabel(self.NewLogEventLbl)
self.Logs.setFormatter(
logging.Formatter('%(asctime)s - %(levelname)s - %(message)s'))
self.logger = logging.getLogger()
self.logger.addHandler(self.Logs)
log_level = os.environ.get('LOGLEVEL', 'INFO').upper()
self.logger.setLevel(log_level)
# Setup reports tab
self.reports = Reports(self.ReportTableView, self.ReportTreeView)
# Customize UI configuration
self.balances_model = BalancesModel(self.BalancesTableView)
self.BalancesTableView.setModel(self.balances_model)
self.balances_model.configureView()
self.holdings_model = HoldingsModel(self.HoldingsTableView)
self.HoldingsTableView.setModel(self.holdings_model)
self.holdings_model.configureView()
self.HoldingsTableView.setContextMenuPolicy(Qt.CustomContextMenu)
self.operations_model = OperationsModel(self.OperationsTableView)
self.OperationsTableView.setModel(self.operations_model)
self.operations_model.configureView()
self.OperationsTableView.setContextMenuPolicy(Qt.CustomContextMenu)
self.connect_signals_and_slots()
self.NewOperationMenu = QMenu()
for i in range(self.OperationsTabs.count()):
if hasattr(self.OperationsTabs.widget(i), "isCustom"):
self.OperationsTabs.widget(i).dbUpdated.connect(
self.ledger.rebuild)
self.OperationsTabs.widget(i).dbUpdated.connect(
self.operations_model.refresh)
self.NewOperationMenu.addAction(
self.OperationsTabs.widget(i).name,
partial(self.createOperation, i))
self.NewOperationBtn.setMenu(self.NewOperationMenu)
# Setup balance and holdings parameters
current_time = QDateTime.currentDateTime()
current_time.setTimeSpec(
Qt.UTC) # We use UTC everywhere so need to force TZ info
self.BalanceDate.setDateTime(current_time)
self.BalancesCurrencyCombo.setIndex(
JalSettings().getValue('BaseCurrency'))
self.HoldingsDate.setDateTime(current_time)
self.HoldingsCurrencyCombo.setIndex(
JalSettings().getValue('BaseCurrency'))
self.OperationsTabs.setCurrentIndex(TransactionType.NA)
self.OperationsTableView.selectRow(0)
self.OnOperationsRangeChange(0)
def prepare_corporate_actions(self):
corp_actions = []
# get list of all deals that were opened with corp.action and closed by normal trade
query = executeSQL("SELECT d.open_op_id AS operation_id, s.symbol, d.qty AS qty, "
"t.number AS trade_number, t.timestamp AS t_date, qt.quote AS t_rate, "
"t.settlement AS s_date, qts.quote AS s_rate, t.price AS price, t.fee AS fee, "
"s.full_name AS full_name, s.isin AS isin, s.type_id AS type_id "
"FROM deals AS d "
"JOIN trades AS t ON t.id=d.close_op_id AND t.op_type=d.close_op_type "
"LEFT JOIN accounts AS a ON a.id = :account_id "
"LEFT JOIN assets_ext AS s ON s.id = t.asset_id AND s.currency_id=a.currency_id "
"LEFT JOIN t_last_dates AS ldt ON t.timestamp=ldt.ref_id "
"LEFT JOIN quotes AS qt ON ldt.timestamp=qt.timestamp AND a.currency_id=qt.asset_id AND qt.currency_id=:base_currency "
"LEFT JOIN t_last_dates AS ldts ON t.settlement=ldts.ref_id "
"LEFT JOIN quotes AS qts ON ldts.timestamp=qts.timestamp AND a.currency_id=qts.asset_id AND qts.currency_id=:base_currency "
"WHERE t.settlement<:end AND d.account_id=:account_id AND d.open_op_type=:corp_action "
"ORDER BY s.symbol, t.timestamp",
[(":end", self.year_end), (":account_id", self.account_id),
(":corp_action", LedgerTransaction.CorporateAction),
(":base_currency", JalSettings().getValue('BaseCurrency'))])
group = 1
basis = 1
previous_symbol = ""
while query.next():
actions = []
sale = readSQLrecord(query, named=True)
if previous_symbol != sale['symbol']:
# Clean processed qty records if symbol have changed
_ = executeSQL("DELETE FROM t_last_assets")
if sale["s_date"] >= self.year_begin: # Don't put sub-header of operation is out of scope
corp_actions.append({'report_template': "symbol_header",
'report_group': 0,
'description': f"Сделки по бумаге: {sale['symbol']} - {sale['full_name']}"})
previous_symbol = sale['symbol']
sale['operation'] = "Продажа"
sale['basis_ratio'] = 100.0 * basis
sale['amount'] = round(sale['price'] * sale['qty'], 2)
if sale['s_rate']:
sale['amount_rub'] = round(sale['amount'] * sale['s_rate'], 2)
else:
sale['amount_rub'] = 0
if sale['t_rate']:
sale['fee_rub'] = round(sale['fee'] * sale['t_rate'], 2)
else:
sale['fee_rub'] = 0
sale['income_rub'] = sale['amount_rub']
sale['spending_rub'] = sale['fee_rub']
if sale["t_date"] < self.year_begin: # Don't show deal that is before report year (level = -1)
self.proceed_corporate_action(actions, sale['operation_id'], sale['symbol'], sale['qty'], basis, -1, group)
else:
sale['report_template'] = "trade"
sale['report_group'] = group
actions.append(sale)
if sale['type_id'] == PredefinedAsset.Bond:
self.output_accrued_interest(actions, sale['trade_number'], 1, 0)
self.proceed_corporate_action(actions, sale['operation_id'], sale['symbol'], sale['qty'], basis, 1, group)
self.insert_totals(actions, ["income_rub", "spending_rub"])
corp_actions += actions
group += 1
return corp_actions
def rebuild(self, from_timestamp=-1, fast_and_dirty=False):
exception_happened = False
last_timestamp = 0
self.amounts.clear()
self.values.clear()
if from_timestamp >= 0:
frontier = from_timestamp
operations_count = readSQL(
"SELECT COUNT(id) FROM operation_sequence WHERE timestamp >= :frontier",
[(":frontier", frontier)])
else:
frontier = self.getCurrentFrontier()
operations_count = readSQL(
"SELECT COUNT(id) FROM operation_sequence WHERE timestamp >= :frontier",
[(":frontier", frontier)])
if operations_count > self.SILENT_REBUILD_THRESHOLD:
if QMessageBox().warning(
None, self.tr("Confirmation"),
f"{operations_count}" + self.
tr(" operations require rebuild. Do you want to do it right now?"
), QMessageBox.Yes,
QMessageBox.No) == QMessageBox.No:
JalSettings().setValue('RebuildDB', 1)
return
if operations_count == 0:
logging.info(self.tr("Leger is empty"))
return
if self.progress_bar is not None:
self.progress_bar.setRange(0, operations_count)
self.main_window.showProgressBar(True)
logging.info(
self.tr("Re-building ledger since: ") +
f"{datetime.utcfromtimestamp(frontier).strftime('%d/%m/%Y %H:%M:%S')}"
)
start_time = datetime.now()
_ = executeSQL("DELETE FROM deals WHERE close_timestamp >= :frontier",
[(":frontier", frontier)])
_ = executeSQL("DELETE FROM ledger WHERE timestamp >= :frontier",
[(":frontier", frontier)])
_ = executeSQL(
"DELETE FROM ledger_totals WHERE timestamp >= :frontier",
[(":frontier", frontier)])
_ = executeSQL("DELETE FROM open_trades WHERE timestamp >= :frontier",
[(":frontier", frontier)])
db_triggers_disable()
if fast_and_dirty: # For 30k operations difference of execution time is - with 0:02:41 / without 0:11:44
_ = executeSQL("PRAGMA synchronous = OFF")
try:
query = executeSQL(
"SELECT op_type, id, timestamp, account_id, subtype FROM operation_sequence "
"WHERE timestamp >= :frontier", [(":frontier", frontier)])
while query.next():
data = readSQLrecord(query, named=True)
last_timestamp = data['timestamp']
operation = LedgerTransaction().get_operation(
data['op_type'], data['id'], data['subtype'])
operation.processLedger(self)
if self.progress_bar is not None:
self.progress_bar.setValue(query.at())
except Exception as e:
exception_happened = True
logging.error(f"{traceback.format_exc()}")
finally:
if fast_and_dirty:
_ = executeSQL("PRAGMA synchronous = ON")
db_triggers_enable()
if self.progress_bar is not None:
self.main_window.showProgressBar(False)
# Fill ledger totals values
_ = executeSQL(
"INSERT INTO ledger_totals"
"(op_type, operation_id, timestamp, book_account, asset_id, account_id, amount_acc, value_acc) "
"SELECT op_type, operation_id, timestamp, book_account, "
"asset_id, account_id, amount_acc, value_acc FROM ledger "
"WHERE id IN ("
"SELECT MAX(id) FROM ledger WHERE timestamp >= :frontier "
"GROUP BY op_type, operation_id, book_account, account_id, asset_id)",
[(":frontier", frontier)])
JalSettings().setValue('RebuildDB', 0)
if exception_happened:
logging.error(
self.
tr("Exception happened. Ledger is incomplete. Please correct errors listed in log"
))
else:
logging.info(
self.tr("Ledger is complete. Elapsed time: ") +
f"{datetime.now() - start_time}" +
self.tr(", new frontier: ") +
f"{datetime.utcfromtimestamp(last_timestamp).strftime('%d/%m/%Y %H:%M:%S')}"
)
self.updated.emit()
def prepare_tax(self):
_ = executeSQL("DELETE FROM t_last_dates")
_ = executeSQL("DELETE FROM t_last_quotes")
_ = executeSQL(
"INSERT INTO t_last_dates(ref_id, timestamp) "
"SELECT ref_id, coalesce(MAX(q.timestamp), 0) AS timestamp "
"FROM ("
"SELECT t.timestamp AS ref_id FROM trades AS t "
"WHERE t.account_id=:account_id AND t.asset_id=:asset_id "
"UNION "
"SELECT t.settlement AS ref_id FROM trades AS t "
"WHERE t.account_id=:account_id AND t.asset_id=:asset_id "
") LEFT JOIN accounts AS a ON a.id = :account_id "
"LEFT JOIN quotes AS q ON ref_id >= q.timestamp AND a.currency_id=q.asset_id AND q.currency_id=:base_currency "
"WHERE ref_id IS NOT NULL "
"GROUP BY ref_id ORDER BY ref_id",
[(":account_id", self.account_id), (":asset_id", self.asset_id),
(":base_currency", JalSettings().getValue('BaseCurrency'))])
_ = executeSQL(
"INSERT INTO t_last_quotes(timestamp, asset_id, quote) "
"SELECT MAX(timestamp) AS timestamp, asset_id, quote "
"FROM quotes AS q LEFT JOIN accounts AS a ON a.id = :account_id "
"WHERE q.asset_id = :asset_id OR q.asset_id = a.currency_id AND q.currency_id=:base_currency "
"GROUP BY asset_id",
[(":account_id", self.account_id), (":asset_id", self.asset_id),
(":base_currency", JalSettings().getValue('BaseCurrency'))])
self.quote = readSQL(
"SELECT quote FROM t_last_quotes WHERE asset_id=:asset_id",
[(":asset_id", self.asset_id)])
if self.quote is None:
logging.error(
self.tr("Can't get current quote for ") + self.asset_name)
return
self.currency_name = JalDB().get_asset_name(
JalDB().get_account_currency(self.account_id))
self.rate = readSQL(
"SELECT quote FROM accounts AS a "
"LEFT JOIN t_last_quotes AS q ON q.asset_id=a.currency_id WHERE id=:account_id",
[(":account_id", self.account_id)])
if self.rate is None:
logging.error(
self.tr("Can't get current rate for ") + self.currency_name)
return
query = executeSQL(
"SELECT strftime('%d/%m/%Y', datetime(t.timestamp, 'unixepoch')) AS timestamp, "
"t.qty AS qty, t.price AS o_price, oq.quote AS o_rate FROM trades AS t "
"LEFT JOIN accounts AS ac ON ac.id = :account_id "
"LEFT JOIN t_last_dates AS od ON od.ref_id = IIF(t.settlement=0, t.timestamp, t.settlement) "
"LEFT JOIN quotes AS oq ON ac.currency_id=oq.asset_id AND oq.currency_id=:base_currency "
"AND oq.timestamp=od.timestamp "
"WHERE t.account_id=:account_id AND t.asset_id=:asset_id AND t.qty*(:total_qty)>0 "
"ORDER BY t.timestamp DESC, t.id DESC",
[(":account_id", self.account_id), (":asset_id", self.asset_id),
(":total_qty", self.asset_qty),
(":base_currency", JalSettings().getValue('BaseCurrency'))])
table = []
remainder = self.asset_qty
profit = 0
value = 0
profit_rub = 0
value_rub = 0
while query.next():
record = readSQLrecord(query, named=True)
record['qty'] = record[
'qty'] if record['qty'] <= remainder else remainder
record['profit'] = record['qty'] * (self.quote - record['o_price'])
record[
'o_rate'] = 1 if record['o_rate'] == '' else record['o_rate']
record['profit_rub'] = record['qty'] * (
self.quote * self.rate - record['o_price'] * record['o_rate'])
record['tax'] = 0.13 * record['profit_rub'] if record[
'profit_rub'] > 0 else 0
table.append(record)
remainder -= record['qty']
profit += record['profit']
value += record['qty'] * record['o_price']
profit_rub += record['profit_rub']
value_rub += record['qty'] * record['o_price'] * record['o_rate']
if remainder <= 0:
break
tax = 0.13 * profit_rub if profit_rub > 0 else 0
table.append({
'timestamp': self.tr("TOTAL"),
'qty': self.asset_qty,
'o_price': value / self.asset_qty,
'o_rate': value_rub / value,
'profit': profit,
'profit_rub': profit_rub,
'tax': tax
})
data = pd.DataFrame(table)
self.dataframe = data
def calculateBalances(self):
query = executeSQL(
"WITH "
"_last_quotes AS (SELECT MAX(timestamp) AS timestamp, asset_id, currency_id, quote "
"FROM quotes WHERE timestamp <= :balances_timestamp GROUP BY asset_id, currency_id), "
"_last_dates AS (SELECT account_id AS ref_id, MAX(timestamp) AS timestamp "
"FROM ledger WHERE timestamp <= :balances_timestamp GROUP BY ref_id) "
"SELECT a.type_id AS account_type, t.name AS type_name, l.account_id AS account, "
"a.name AS account_name, a.currency_id AS currency, c.symbol AS currency_name, "
"SUM(CASE WHEN l.book_account=:assets_book THEN l.amount*q.quote ELSE l.amount END) AS balance, "
"SUM(CASE WHEN l.book_account=:assets_book THEN l.amount*coalesce(q.quote*r.quote/ra.quote, 0) "
"ELSE l.amount*coalesce(r.quote/ra.quote, 0) END) AS balance_a, "
"(d.timestamp - coalesce(a.reconciled_on, 0))/86400 AS unreconciled, "
"a.active AS active "
"FROM ledger AS l "
"LEFT JOIN accounts AS a ON l.account_id = a.id "
"LEFT JOIN assets_ext AS c ON c.id = a.currency_id AND c.currency_id = :base_currency "
"LEFT JOIN account_types AS t ON a.type_id = t.id "
"LEFT JOIN _last_quotes AS q ON l.asset_id = q.asset_id AND q.currency_id = a.currency_id "
"LEFT JOIN _last_quotes AS r ON a.currency_id = r.asset_id AND r.currency_id = :base_currency "
"LEFT JOIN _last_quotes AS ra ON ra.asset_id = :currency AND ra.currency_id = :base_currency "
"LEFT JOIN _last_dates AS d ON l.account_id = d.ref_id "
"WHERE (book_account=:money_book OR book_account=:assets_book OR book_account=:liabilities_book) "
"AND l.timestamp <= :balances_timestamp "
"GROUP BY l.account_id "
"HAVING ABS(balance)>:tolerance "
"ORDER BY account_type",
[(":currency", self._currency), (":money_book", BookAccount.Money),
(":assets_book", BookAccount.Assets),
(":liabilities_book", BookAccount.Liabilities),
(":balances_timestamp", self._date),
(":tolerance", Setup.DISP_TOLERANCE),
(":base_currency", JalSettings().getValue('BaseCurrency'))],
forward_only=True)
self._data = []
current_type = 0
current_type_name = ''
field_names = list(
map(query.record().fieldName, range(
query.record().count()))) + ['level']
while query.next():
values = readSQLrecord(query, named=True)
values['level'] = 0
if self._active_only and (values['active'] == 0):
continue
if values['account_type'] != current_type:
if current_type != 0:
sub_total = sum([
row['balance_a'] for row in self._data
if row['account_type'] == current_type
])
self._data.append(
dict(
zip(field_names, [
current_type, current_type_name, 0, '', 0, '',
0, sub_total, 0, 1, 1
])))
current_type = values['account_type']
current_type_name = values['type_name']
self._data.append(values)
if current_type != 0:
sub_total = sum([
row['balance_a'] for row in self._data
if row['account_type'] == current_type
])
self._data.append(
dict(
zip(field_names, [
current_type, current_type_name, 0, '', 0, '', 0,
sub_total, 0, 1, 1
])))
total_sum = sum(
[row['balance_a'] for row in self._data if row['level'] == 0])
self._data.append(
dict(
zip(field_names,
[0,
self.tr("Total"), 0, '', 0, '', 0, total_sum, 0, 1, 2])))
self.modelReset.emit()
def calculateHoldings(self):
query = executeSQL(
"WITH "
"_last_quotes AS (SELECT MAX(timestamp) AS timestamp, asset_id, currency_id, quote "
"FROM quotes WHERE timestamp <= :holdings_timestamp GROUP BY asset_id, currency_id), "
"_last_assets AS ("
"SELECT id, SUM(t_value) AS total_value "
"FROM "
"("
"SELECT a.id, SUM(l.amount) AS t_value "
"FROM ledger AS l "
"LEFT JOIN accounts AS a ON l.account_id = a.id "
"WHERE (l.book_account=:money_book OR l.book_account=:liabilities_book) "
"AND a.type_id = :investments AND l.timestamp <= :holdings_timestamp GROUP BY a.id "
"UNION ALL "
"SELECT a.id, SUM(l.amount*q.quote) AS t_value "
"FROM ledger AS l "
"LEFT JOIN accounts AS a ON l.account_id = a.id "
"LEFT JOIN _last_quotes AS q ON l.asset_id = q.asset_id AND q.currency_id = a.currency_id "
"WHERE l.book_account=:assets_book AND a.type_id = :investments AND l.timestamp <= :holdings_timestamp "
"GROUP BY a.id"
") "
"GROUP BY id HAVING ABS(total_value) > :tolerance) "
"SELECT h.currency_id, c.symbol AS currency, h.account_id, h.account, h.asset_id, "
"h.currency_id=h.asset_id AS asset_is_currency, coalesce(a.symbol, c.symbol) AS asset, "
"coalesce(a.full_name, c.full_name) AS asset_name, ad.value AS expiry, h.qty, h.value AS value_i, "
"h.quote, h.quote_a, h.total "
"FROM ("
"SELECT a.currency_id, l.account_id, a.name AS account, l.asset_id, sum(l.amount) AS qty, "
"sum(l.value) AS value, q.quote, q.quote*r.quote/ra.quote AS quote_a, t.total_value AS total "
"FROM ledger AS l "
"LEFT JOIN accounts AS a ON l.account_id = a.id "
"LEFT JOIN _last_quotes AS q ON l.asset_id = q.asset_id AND q.currency_id = a.currency_id "
"LEFT JOIN _last_quotes AS r ON a.currency_id = r.asset_id AND r.currency_id = :base_currency "
"LEFT JOIN _last_quotes AS ra ON ra.asset_id = :currency AND ra.currency_id = :base_currency "
"LEFT JOIN _last_assets AS t ON l.account_id = t.id "
"WHERE a.type_id = :investments AND l.book_account = :assets_book AND l.timestamp <= :holdings_timestamp "
"GROUP BY l.account_id, l.asset_id "
"HAVING ABS(qty) > :tolerance "
"UNION ALL "
"SELECT a.currency_id, l.account_id, a.name AS account, l.asset_id, sum(l.amount) AS qty, "
"0 AS value, 1, r.quote/ra.quote AS quote_a, t.total_value AS total "
"FROM ledger AS l "
"LEFT JOIN accounts AS a ON l.account_id = a.id "
"LEFT JOIN _last_quotes AS r ON a.currency_id = r.asset_id AND r.currency_id = :base_currency "
"LEFT JOIN _last_quotes AS ra ON ra.asset_id = :currency AND ra.currency_id = :base_currency "
"LEFT JOIN _last_assets AS t ON l.account_id = t.id "
"WHERE (l.book_account=:money_book OR l.book_account=:liabilities_book) "
"AND a.type_id = :investments AND l.timestamp <= :holdings_timestamp "
"GROUP BY l.account_id, l.asset_id "
"HAVING ABS(qty) > :tolerance "
") AS h "
"LEFT JOIN assets_ext AS c ON c.id=h.currency_id AND c.currency_id=:base_currency "
"LEFT JOIN assets_ext AS a ON a.id=h.asset_id AND a.currency_id=h.currency_id "
"LEFT JOIN asset_data AS ad ON ad.asset_id=a.id AND ad.datatype=:expiry "
"ORDER BY currency, account, asset_is_currency, asset",
[(":currency", self._currency), (":money_book", BookAccount.Money),
(":assets_book", BookAccount.Assets),
(":liabilities_book", BookAccount.Liabilities),
(":holdings_timestamp", self._date),
(":investments", PredefindedAccountType.Investment),
(":tolerance", Setup.DISP_TOLERANCE),
(":expiry", AssetData.ExpiryDate),
(":base_currency", JalSettings().getValue('BaseCurrency'))],
forward_only=True)
# Load data from SQL to tree
self._root = TreeItem({})
currency = 0
c_node = None
account = 0
a_node = None
while query.next():
values = readSQLrecord(query, named=True)
values['level'] = 2
if values['currency_id'] != currency:
currency = values['currency_id']
c_node = TreeItem(values, self._root)
c_node.data['level'] = 0
c_node.data['asset_name'] = ''
c_node.data['expiry'] = 0
c_node.data['qty'] = 0
self._root.appendChild(c_node)
if values['account_id'] != account:
account = values['account_id']
a_node = TreeItem(values, c_node)
a_node.data['level'] = 1
a_node.data['asset_name'] = ''
a_node.data['expiry'] = 0
a_node.data['qty'] = 0
c_node.appendChild(a_node)
if values['quote']:
if values['asset_is_currency']:
profit = 0
else:
profit = values['quote'] * values['qty'] - values['value_i']
if values['value_i'] != 0:
profit_relative = values['quote'] * values['qty'] / values[
'value_i'] - 1
else:
profit_relative = 0
value = values['quote'] * values['qty']
share = 100.0 * value / values['total']
value_adjusted = values['quote_a'] * values['qty'] if values[
'quote_a'] else 0
values.update(
dict(
zip(self.calculated_names, [
share, profit, profit_relative, value,
value_adjusted
])))
else:
values.update(dict(zip(self.calculated_names,
[0, 0, 0, 0, 0])))
node = TreeItem(values, a_node)
a_node.appendChild(node)
# Update totals
for i in range(self._root.count()): # Iterate through each currency
currency_child = self._root.getChild(i)
for j in range(currency_child.count(
)): # Iterate through each account for given currency
self.add_node_totals(currency_child.getChild(j))
self.add_node_totals(currency_child)
for j in range(currency_child.count(
)): # Calculate share of each account within currency
if currency_child.data['value']:
currency_child.getChild(j).data['share'] = \
100.0 * currency_child.getChild(j).data['value'] / currency_child.data['value']
# Get full total of totals for all currencies adjusted to common currency
total = sum([
self._root.getChild(i).data['value_a']
for i in range(self._root.count())
])
for i in range(self._root.count(
)): # Calculate share of each currency (adjusted to common currency)
if total != 0:
self._root.getChild(
i).data['share'] = 100.0 * self._root.getChild(
i).data['value_a'] / total
else:
self._root.getChild(i).data['share'] = None
self.modelReset.emit()
self._view.expandAll()