def findAccountID(self, accountNumber, accountCurrency=''):
if accountCurrency:
account_id = readSQL(self.db, "SELECT a.id FROM accounts AS a "
"LEFT JOIN assets AS c ON c.id=a.currency_id "
"WHERE a.number=:account_number AND c.name=:currency_name",
[(":account_number", accountNumber), (":currency_name", accountCurrency)])
else:
account_id = readSQL(self.db, "SELECT a.id FROM accounts AS a "
"LEFT JOIN assets AS c ON c.id=a.currency_id "
"WHERE a.number=:account_number", [(":account_number", accountNumber)])
return account_id
def response_esia(self, auth_code, state):
client_secret = readSQL(
self.db,
"SELECT value FROM settings WHERE name='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(
g_tr('SlipsTaxAPI', "ESIA login failed: ") +
f"{response}/{response.text}")
return
logging.info(
g_tr('SlipsTaxAPI', "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']
_ = executeSQL(
self.db,
"UPDATE settings SET value=:new_session WHERE name='RuTaxSessionId'",
[(":new_session", new_session_id)])
_ = executeSQL(
self.db,
"UPDATE settings SET value=:new_refresh_token WHERE name='RuTaxRefreshToken'",
[(":new_refresh_token", new_refresh_token)])
self.db.commit()
self.accept()
def addWithholdingTax(self, timestamp, account_id, asset_id, amount, note):
parts = re.match(IBKR.TaxNotePattern, note)
if not parts:
logging.warning(
g_tr('StatementLoader', "*** MANUAL ENTRY REQUIRED ***"))
logging.warning(
g_tr('StatementLoader', "Unhandled tax pattern found: ") +
f"{note}")
return
dividend_note = parts.group(1) + '%'
country_code = parts.group(2)
try:
dividend_id, old_tax = readSQL(
self.db, "SELECT id, sum_tax FROM dividends "
"WHERE timestamp=:timestamp AND account_id=:account_id "
"AND asset_id=:asset_id AND note LIKE :dividend_description",
[(":timestamp", timestamp), (":account_id", account_id),
(":asset_id", asset_id),
(":dividend_description", dividend_note)])
except:
logging.warning(
g_tr('StatementLoader',
"Dividend not found for withholding tax: ") + f"{note}")
return
_ = executeSQL(
self.db,
"UPDATE dividends SET sum_tax=:tax, note_tax=:note WHERE id=:dividend_id",
[(":dividend_id", dividend_id), (":tax", old_tax + amount),
(":note", country_code + " tax")])
self.db.commit()
logging.info(
g_tr('StatementLoader', "Withholding tax added: ") + f"{note}")
def findAssetID(self, symbol):
asset_id = readSQL(self.db, "SELECT id FROM assets WHERE name=:symbol", [(":symbol", symbol)])
if asset_id is None:
dialog = AddAssetDialog(self.parent, self.db, symbol)
dialog.exec_()
asset_id = dialog.asset_id
return asset_id
def createTransfer(self, timestamp, f_acc_id, f_amount, t_acc_id, t_amount, fee_acc_id, fee, note):
transfer_id = readSQL(self.db,
"SELECT id FROM transfers_combined "
"WHERE from_timestamp=:timestamp AND from_acc_id=:from_acc_id AND to_acc_id=:to_acc_id",
[(":timestamp", timestamp), (":from_acc_id", f_acc_id), (":to_acc_id", t_acc_id)])
if transfer_id:
logging.info(f"Currency exchange {f_amount}->{t_amount} already exists in ledger. Skipped")
return
if abs(fee) > Setup.CALC_TOLERANCE:
_ = executeSQL(self.db,
"INSERT INTO transfers_combined (from_timestamp, from_acc_id, from_amount, "
"to_timestamp, to_acc_id, to_amount, fee_timestamp, fee_acc_id, fee_amount, note) "
"VALUES (:timestamp, :f_acc_id, :f_amount, :timestamp, :t_acc_id, :t_amount, "
":timestamp, :fee_acc_id, :fee_amount, :note)",
[(":timestamp", timestamp), (":f_acc_id", f_acc_id), (":t_acc_id", t_acc_id),
(":f_amount", f_amount), (":t_amount", t_amount), (":fee_acc_id", fee_acc_id),
(":fee_amount", fee), (":note", note)])
else:
_ = executeSQL(self.db,
"INSERT INTO transfers_combined (from_timestamp, from_acc_id, from_amount, "
"to_timestamp, to_acc_id, to_amount, note) "
"VALUES (:timestamp, :f_acc_id, :f_amount, :timestamp, :t_acc_id, :t_amount, :note)",
[(":timestamp", timestamp), (":f_acc_id", f_acc_id), (":t_acc_id", t_acc_id),
(":f_amount", f_amount), (":t_amount", t_amount), (":note", note)])
self.db.commit()
logging.info(g_tr('StatementLoader', "Currency exchange ") + f"{f_amount}->{t_amount}" +
g_tr('StatementLoader', " added"))
def loadIBTransactionTax(self, IBtax):
account_id = self.findAccountID(IBtax.accountId, IBtax.currency)
if account_id is None:
logging.error(g_tr('StatementLoader', "Account ") + f"{IBtax.accountId} ({IBtax.currency})" +
g_tr('StatementLoader', " not found. Tax #") + f"{IBtax.tradeID}" +
g_tr('StatementLoader', " skipped"))
return
timestamp = int(datetime.combine(IBtax.date, datetime.min.time()).timestamp())
amount = float(IBtax.taxAmount) # value is negative already
note = f"{IBtax.symbol} ({IBtax.description}) - {IBtax.taxDescription} (#{IBtax.tradeId})"
id = readSQL(self.db, "SELECT id FROM all_operations WHERE type = :type "
"AND timestamp=:timestamp AND account_id=:account_id AND amount=:amount",
[(":timestamp", timestamp), (":type", TransactionType.Action),
(":account_id", account_id), (":amount", amount)])
if id:
logging.warning(g_tr('StatementLoader', "Tax transaction #") + f"{IBtax.tradeId}" +
g_tr('StatementLoader', " already exists"))
return
query = executeSQL(self.db,
"INSERT INTO actions (timestamp, account_id, peer_id) VALUES "
"(:timestamp, :account_id, (SELECT organization_id FROM accounts WHERE id=:account_id))",
[(":timestamp", timestamp), (":account_id", account_id)])
pid = query.lastInsertId()
_ = executeSQL(self.db, "INSERT INTO action_details (pid, category_id, sum, note) "
"VALUES (:pid, :category_id, :sum, :note)",
[(":pid", pid), (":category_id", PredefinedCategory.Taxes), (":sum", amount), (":note", note)])
self.db.commit()
logging.info(g_tr('StatementLoader', "Transaction tax added: ") + f"{note}, {amount}")
def get_ru_tax_session(self):
stored_id = readSQL(
self.db, "SELECT value FROM settings WHERE name='RuTaxSessionId'")
if stored_id != '':
return stored_id
login_dialog = LoginFNS(self.db)
if login_dialog.exec_() == QDialog.Accepted:
stored_id = readSQL(
self.db,
"SELECT value FROM settings WHERE name='RuTaxSessionId'")
if stored_id is not None:
return stored_id
logging.warning(
g_tr('SlipsTaxAPI', "No Russian Tax SessionId available"))
return ''
def getAmount(self, book, asset_id=None):
if asset_id is None:
amount = readSQL(
self.db,
"SELECT sum_amount FROM ledger_sums WHERE book_account = :book AND "
"account_id = :account_id AND timestamp <= :timestamp ORDER BY sid DESC LIMIT 1",
[(":book", book), (":account_id", self.current[ACCOUNT_ID]),
(":timestamp", self.current[TIMESTAMP])])
else:
amount = readSQL(
self.db,
"SELECT sum_amount FROM ledger_sums WHERE book_account = :book "
"AND account_id = :account_id AND asset_id = :asset_id "
"AND timestamp <= :timestamp ORDER BY sid DESC LIMIT 1",
[(":book", book), (":account_id", self.current[ACCOUNT_ID]),
(":asset_id", asset_id),
(":timestamp", self.current[TIMESTAMP])])
amount = float(amount) if amount is not None else 0.0
return amount
def addNewAsset(db, symbol, name, asset_type, isin, data_source=-1):
_ = executeSQL(db, "INSERT INTO assets(name, type_id, full_name, isin, src_id) "
"VALUES(:symbol, :type, :full_name, :isin, :data_src)",
[(":symbol", symbol), (":type", asset_type), (":full_name", name),
(":isin", isin), (":data_src", data_source)])
db.commit()
asset_id = readSQL(db, "SELECT id FROM assets WHERE name=:symbol", [(":symbol", symbol)])
if asset_id is not None:
logging.info(g_tr('', "New asset with id ") + f"{asset_id}" + g_tr('', " was added: ") + f"{symbol} - '{name}'")
else:
logging.error(g_tr('', "Failed to add new asset: "), + f"{symbol}")
return asset_id
def refresh_session(self):
session_id = self.get_ru_tax_session()
client_secret = readSQL(
self.db,
"SELECT value FROM settings WHERE name='RuTaxClientSecret'")
refresh_token = readSQL(
self.db,
"SELECT value FROM settings WHERE name='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']
_ = executeSQL(
self.db,
"UPDATE settings SET value=:new_session WHERE name='RuTaxSessionId'",
[(":new_session", new_session_id)])
_ = executeSQL(
self.db,
"UPDATE settings SET value=:new_refresh_token WHERE name='RuTaxRefreshToken'",
[(":new_refresh_token", new_refresh_token)])
else:
logging.error(
g_tr('SlipsTaxAPI', "Can't refresh session, response: ") +
f"{response}/{response.text}")
def createDividend(self, timestamp, account_id, asset_id, amount, note):
id = readSQL(self.db, "SELECT id FROM dividends WHERE timestamp=:timestamp "
"AND account_id=:account_id AND asset_id=:asset_id AND note=:note",
[(":timestamp", timestamp), (":account_id", account_id), (":asset_id", asset_id), (":note", note)])
if id:
logging.warning(g_tr('StatementLoader', "Dividend already exists: ") + f"{note}")
return
_ = executeSQL(self.db, "INSERT INTO dividends (timestamp, account_id, asset_id, sum, note) "
"VALUES (:timestamp, :account_id, :asset_id, :sum, :note)",
[(":timestamp", timestamp), (":account_id", account_id), (":asset_id", asset_id),
(":sum", amount), (":note", note)])
self.db.commit()
logging.info(g_tr('StatementLoader', "Dividend added: ") + f"{note}")
def storeIBAsset(self, IBasset):
asset_id = readSQL(self.db, "SELECT id FROM assets WHERE name=:symbol", [(":symbol", IBasset.symbol)])
if asset_id is not None:
return asset_id
try:
asset_type = IBKR.AssetType[IBasset.assetCategory]
except:
logging.error(g_tr('StatementLoader', "Asset type ") + f"{IBasset.assetCategory}" +
g_tr('StatementLoader', " is not supported"))
return None
if IBasset.subCategory == "ETF":
asset_type = PredefinedAsset.ETF
return addNewAsset(self.db, IBasset.symbol, IBasset.description, asset_type, IBasset.isin)
def OnUpClick(self):
if self.search_text: # list filtered by search string
return
current_id = self.DataView.model().record(0).value('id')
if current_id is None:
pid = self.last_parent
else:
pid = readSQL(self.db,
f"SELECT c2.pid FROM {self.table} AS c1 LEFT JOIN {self.table} AS c2 ON c1.pid=c2.id "\
f"WHERE c1.id = :current_id", [(":current_id", current_id)])
if pid == '':
pid = 0
self.parent = pid
self.setFilter()
def createTrade(self, account_id, asset_id, timestamp, settlement, number, qty, price, fee, coupon=0.0):
trade_id = readSQL(self.db,
"SELECT id FROM trades "
"WHERE timestamp=:timestamp AND asset_id = :asset "
"AND account_id = :account AND number = :number AND qty = :qty AND price = :price",
[(":timestamp", timestamp), (":asset", asset_id), (":account", account_id),
(":number", number), (":qty", qty), (":price", price)])
if trade_id:
logging.info(g_tr('StatementLoader', "Trade #") + f"{number}" +
g_tr('StatementLoader', " already exists in ledger. Skipped"))
return
_ = executeSQL(self.db,
"INSERT INTO trades (timestamp, settlement, corp_action_id, number, account_id, "
"asset_id, qty, price, fee, coupon) "
"VALUES (:timestamp, :settlement, 0, :number, :account, "
":asset, :qty, :price, :fee, :coupon)",
[(":timestamp", timestamp), (":settlement", settlement), (":number", number),
(":account", account_id), (":asset", asset_id), (":qty", float(qty)),
(":price", float(price)), (":fee", -float(fee)), (":coupon", float(coupon))])
self.db.commit()
logging.info(f"Trade #{number} added for account {account_id} asset {asset_id} @{timestamp}: {qty}x{price}")
def login_fns(self):
client_secret = readSQL(
self.db,
"SELECT value FROM settings WHERE name='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']
_ = executeSQL(
self.db,
"UPDATE settings SET value=:new_session WHERE name='RuTaxSessionId'",
[(":new_session", new_session_id)])
_ = executeSQL(
self.db,
"UPDATE settings SET value=:new_refresh_token WHERE name='RuTaxRefreshToken'",
[(":new_refresh_token", new_refresh_token)])
self.db.commit()
self.accept()
def getCurrentFrontier(self):
current_frontier = readSQL(self.db,
"SELECT ledger_frontier FROM frontier")
if current_frontier == '':
current_frontier = 0
return current_frontier
def appendTransaction(self, book, amount, value=None):
seq_id = self.current_seq
timestamp = self.current[TIMESTAMP]
if book == BookAccount.Assets:
asset_id = self.current[ASSET_ID]
else:
asset_id = self.current[CURRENCY_ID]
account_id = self.current[ACCOUNT_ID]
if book == BookAccount.Costs or book == BookAccount.Incomes:
peer_id = self.current[COUPON_PEER]
category_id = self.current[PRICE_CATEGORY]
tag_id = self.current[FEE_TAX_TAG]
else: # TODO - check None for empty values (to put NULL in DB)
peer_id = None
category_id = None
tag_id = None
try:
old_sid, old_amount, old_value = readSQL(
self.db, "SELECT sid, sum_amount, sum_value FROM ledger_sums "
"WHERE book_account = :book AND asset_id = :asset_id "
"AND account_id = :account_id AND sid <= :seq_id "
"ORDER BY sid DESC LIMIT 1", [(":book", book),
(":asset_id", asset_id),
(":account_id", account_id),
(":seq_id", seq_id)])
except:
old_sid = -1
old_amount = 0.0
old_value = 0.0
new_amount = old_amount + amount
if value is None:
new_value = old_value
else:
new_value = old_value + value
if (abs(new_amount - old_amount) +
abs(new_value - old_value)) <= (2 * Setup.CALC_TOLERANCE):
return # we have zero amount - no reason to put it into ledger
_ = executeSQL(
self.db,
"INSERT INTO ledger (timestamp, sid, book_account, asset_id, account_id, "
"amount, value, peer_id, category_id, tag_id) "
"VALUES(:timestamp, :sid, :book, :asset_id, :account_id, "
":amount, :value, :peer_id, :category_id, :tag_id)",
[(":timestamp", timestamp), (":sid", seq_id), (":book", book),
(":asset_id", asset_id), (":account_id", account_id),
(":amount", amount), (":value", value), (":peer_id", peer_id),
(":category_id", category_id), (":tag_id", tag_id)])
if seq_id == old_sid:
_ = executeSQL(
self.db,
"UPDATE ledger_sums SET sum_amount = :new_amount, sum_value = :new_value"
" WHERE sid = :sid AND book_account = :book"
" AND asset_id = :asset_id AND account_id = :account_id",
[(":new_amount", new_amount), (":new_value", new_value),
(":sid", seq_id), (":book", book), (":asset_id", asset_id),
(":account_id", account_id)])
else:
_ = executeSQL(
self.db,
"INSERT INTO ledger_sums(sid, timestamp, book_account, "
"asset_id, account_id, sum_amount, sum_value) "
"VALUES(:sid, :timestamp, :book, :asset_id, "
":account_id, :new_amount, :new_value)",
[(":sid", seq_id), (":timestamp", timestamp), (":book", book),
(":asset_id", asset_id), (":account_id", account_id),
(":new_amount", new_amount), (":new_value", new_value)])
self.db.commit()
def rebuild(self, from_timestamp=-1, fast_and_dirty=False, silent=True):
operationProcess = {
TransactionType.Action: self.processAction,
TransactionType.Dividend: self.processDividend,
TransactionType.Trade: self.processTrade,
TransactionType.Transfer: self.processTransfer,
}
if from_timestamp >= 0:
frontier = from_timestamp
silent = False
else:
frontier = self.getCurrentFrontier()
operations_count = readSQL(
self.db,
"SELECT COUNT(id) FROM all_transactions WHERE timestamp >= :frontier",
[(":frontier", frontier)])
if operations_count > self.SILENT_REBUILD_THRESHOLD:
silent = False
if QMessageBox().warning(
None, g_tr('Ledger', "Confirmation"),
f"{operations_count}" + g_tr(
'Ledger',
" operations require rebuild. Do you want to do it right now?"
), QMessageBox.Yes, QMessageBox.No) == QMessageBox.No:
return
if not silent:
logging.info(
g_tr('Ledger', "Re-build ledger from: ") +
f"{datetime.fromtimestamp(frontier).strftime('%d/%m/%Y %H:%M:%S')}"
)
start_time = datetime.now()
_ = executeSQL(
self.db, "DELETE FROM deals WHERE close_sid >= "
"(SELECT coalesce(MIN(id), 0) FROM sequence WHERE timestamp >= :frontier)",
[(":frontier", frontier)])
_ = executeSQL(self.db,
"DELETE FROM ledger WHERE timestamp >= :frontier",
[(":frontier", frontier)])
_ = executeSQL(self.db,
"DELETE FROM sequence WHERE timestamp >= :frontier",
[(":frontier", frontier)])
_ = executeSQL(self.db,
"DELETE FROM ledger_sums WHERE timestamp >= :frontier",
[(":frontier", frontier)])
self.db.commit()
if fast_and_dirty: # For 30k operations difference of execution time is - with 0:02:41 / without 0:11:44
_ = executeSQL(self.db, "PRAGMA synchronous = OFF")
query = executeSQL(
self.db,
"SELECT type, id, timestamp, subtype, account, currency, asset, amount, "
"price_category, coupon_peer, fee_tax_tag FROM all_transactions "
"WHERE timestamp >= :frontier", [(":frontier", frontier)])
while query.next():
self.current = readSQLrecord(query)
seq_query = executeSQL(
self.db, "INSERT INTO sequence(timestamp, type, operation_id) "
"VALUES(:timestamp, :type, :operation_id)",
[(":timestamp", self.current[TIMESTAMP]),
(":type", self.current[TRANSACTION_TYPE]),
(":operation_id", self.current[OPERATION_ID])])
self.current_seq = seq_query.lastInsertId()
operationProcess[self.current[TRANSACTION_TYPE]]()
if not silent and (query.at() % 1000) == 0:
logging.info(
g_tr('Ledger', "Processed ") + f"{int(query.at()/1000)}" +
g_tr('Ledger', "k records, current frontier: ") +
f"{datetime.fromtimestamp(self.current[TIMESTAMP]).strftime('%d/%m/%Y %H:%M:%S')}"
)
if fast_and_dirty:
_ = executeSQL(self.db, "PRAGMA synchronous = ON")
end_time = datetime.now()
if not silent:
logging.info(
g_tr('Ledger', "Ledger is complete. Elapsed time: ") +
f"{end_time - start_time}" +
g_tr('Ledger', ", new frontier: ") +
f"{datetime.fromtimestamp(self.current[TIMESTAMP]).strftime('%d/%m/%Y %H:%M:%S')}"
)
self.updateBalancesView()
self.updateHoldingsView()
def match_shop_name(self, shop_name):
return readSQL(
self.db, "SELECT mapped_to FROM map_peer WHERE value=:shop_name",
[(":shop_name", shop_name)])
