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)])