def saveReport(self): filename, filter = QFileDialog.getSaveFileName(None, g_tr('Reports', "Save report to:"), ".", g_tr('Reports', "Excel files (*.xlsx)")) if filename: if filter == g_tr('Reports', "Excel files (*.xlsx)") and filename[-5:] != '.xlsx': filename = filename + '.xlsx' else: return report = XLSX(filename) sheet = report.add_report_sheet(g_tr('Reports', "Report")) model = self.table_view.model() headers = {} for col in range(model.columnCount()): headers[col] = (model.headerData(col, Qt.Horizontal), report.formats.ColumnHeader()) report.write_row(sheet, 0, headers) for row in range(model.rowCount()): data_row = {} for col in range(model.columnCount()): data_row[col] = (model.data(model.index(row, col)), report.formats.Text(row)) report.write_row(sheet, row+1, data_row) report.save()
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 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 validate_backup(self): with tarfile.open(self.backup_name, "r:gz") as tar: # Check backup file list backup_file_list = [Setup.DB_PATH, 'label'] if set(backup_file_list) != set(tar.getnames()): logging.debug("Backup content expected: " + str(backup_file_list) + "\nBackup content actual: " + str(tar.getnames())) return False # Check correctness of backup label label_content = tar.extractfile('label').read().decode("utf-8") logging.debug("Backup file label: " + label_content) if label_content[:len(self.backup_label)] == self.backup_label: self._backup_label_date = label_content[len(self.backup_label ):] else: logging.warning( g_tr('JalBackup', "Backup label not recognized")) return False try: _ = datetime.strptime(self._backup_label_date, self.date_fmt) except ValueError: logging.warning(g_tr('JalBackup', "Can't validate backup date")) return False return True
def processDividend(self): if self.current['subtype'] == DividendSubtype.Dividend: self.current['category'] = PredefinedCategory.Dividends elif self.current['subtype'] == DividendSubtype.BondInterest: self.current['category'] = PredefinedCategory.Interest else: logging.error( g_tr('Ledger', "Can't process dividend with N/A type")) return if self.current['peer'] == '': logging.error( g_tr( 'Ledger', "Can't process dividend as bank isn't set for investment account" )) return dividend_amount = self.current['amount'] tax_amount = self.current['fee_tax'] if dividend_amount > 0: credit_returned = self.returnCredit(dividend_amount - tax_amount) if credit_returned < (dividend_amount - tax_amount): self.appendTransaction(BookAccount.Money, dividend_amount - credit_returned) self.appendTransaction(BookAccount.Incomes, -dividend_amount) else: credit_taken = self.takeCredit(-dividend_amount - tax_amount) # tax always positive if credit_taken < -dividend_amount: self.appendTransaction(BookAccount.Money, dividend_amount + credit_taken) self.appendTransaction(BookAccount.Costs, -dividend_amount) if tax_amount: self.appendTransaction(BookAccount.Money, -tax_amount) self.current['category'] = PredefinedCategory.Taxes self.appendTransaction(BookAccount.Costs, tax_amount)
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).lower() country_id = get_country_by_code(country_code) update_asset_country(asset_id, country_id) dividend_id = self.findDividend4Tax(timestamp, account_id, asset_id, dividend_note) if dividend_id is None: logging.warning( g_tr('StatementLoader', "Dividend not found for withholding tax: ") + f"{note}") return old_tax = readSQL("SELECT tax FROM dividends WHERE id=:id", [(":id", dividend_id)]) _ = executeSQL("UPDATE dividends SET tax=:tax WHERE id=:dividend_id", [(":dividend_id", dividend_id), (":tax", old_tax + amount)], commit=True)
def __init__(self): super().__init__() self.sources = [{ 'name': g_tr('StatementLoader', "Quik HTML"), 'filter': "Quik HTML-report (*.htm)", 'loader': self.loadQuikHtml, 'icon': "quik.ico" }, { 'name': g_tr('StatementLoader', "Interactive Brokers XML"), 'filter': "IBKR flex-query (*.xml)", 'loader': self.loadIBFlex, 'icon': "ibkr.png" }, { 'name': g_tr('StatementLoader', "Uralsib Broker"), 'filter': "Uralsib statement (*.zip)", 'loader': self.loadUralsibCapital, 'icon': "uralsib.ico" }, { 'name': g_tr('StatementLoader', "KIT Finance"), 'filter': "KIT Finance statement (*.xlsx)", 'loader': self.loadKITFinance, 'icon': 'kit.png' }, { 'name': g_tr('StatementLoader', "IBKR Activity HTML"), 'filter': "IBKR Activity statement (*.html)", 'loader': self.loadIBActivityStatement, 'icon': 'cancel.png' }]
def flAccount(data, name, default_value, caller): if name not in data.attrib: return default_value if data.tag == 'Trade' and IBKR.flAssetType( data, 'assetCategory', None, None) == PredefinedAsset.Money: if 'symbol' not in data.attrib: logging.error( g_tr('IBKR', "Can't get currencies for accounts: ") + f"{data}") return None if 'ibCommissionCurrency' not in data.attrib: logging.error( g_tr('IBKR', "Can't get account currency for fee account: ") + f"{data}") return None currencies = data.attrib['symbol'].split('.') currencies.append(data.attrib['ibCommissionCurrency']) accountIds = [] for currency in currencies: account = caller.findAccountID(data.attrib[name], currency) if account is None: return None accountIds.append(account) return accountIds if 'currency' not in data.attrib: if default_value is None: logging.error( g_tr('IBKR', "Can't get account currency for account: ") + f"{data}") return default_value return caller.findAccountID(data.attrib[name], data.attrib['currency'])
def update_db_schema(db_path): if QMessageBox().warning(None, g_tr('DB', "Database format is outdated"), g_tr('DB', "Do you agree to upgrade your data to newer format?"), QMessageBox.Yes, QMessageBox.No) == QMessageBox.No: return LedgerInitError(LedgerInitError.OutdatedDbSchema) db = sqlite3.connect(get_dbfilename(db_path)) cursor = db.cursor() try: cursor.execute("SELECT value FROM settings WHERE name='SchemaVersion'") except: return LedgerInitError(LedgerInitError.DbInitFailure) schema_version = cursor.fetchone()[0] for step in range(schema_version, Setup.TARGET_SCHEMA): delta_file = db_path + Setup.UPDATES_PATH + os.sep + Setup.UPDATE_PREFIX + f"{step+1}.sql" logging.info(f"Applying delta schema {step}->{step+1} from {delta_file}") try: with open(delta_file) as delta_sql: try: cursor.executescript(delta_sql.read()) except sqlite3.OperationalError as e: return LedgerInitError(LedgerInitError.SQLFailure, e.args[0]) except FileNotFoundError: return LedgerInitError(LedgerInitError.NoDeltaFile, delta_file) db.close() return LedgerInitError(LedgerInitError.DbInitSuccess)
def saveChanges(self): if not self.model.submitAll(): logging.fatal( g_tr('AbstractOperationDetails', "Operation submit failed: ") + self.model.lastError().text()) return pid = self.model.data(self.model.index(0, self.model.fieldIndex("id"))) if pid is None: # we just have saved new action record and need last insterted id pid = self.model.query().lastInsertId() for row in range(self.details_model.rowCount()): self.details_model.setData( self.details_model.index(row, self.details_model.fieldIndex("pid")), pid) if not self.details_model.submitAll(): logging.fatal( g_tr('AbstractOperationDetails', "Operation details submit failed: ") + self.details_model.lastError().text()) return self.modified = False self.commit_button.setEnabled(False) self.revert_button.setEnabled(False) self.dbUpdated.emit() return
def load(self): self._settled_cash = {} with ZipFile(self._filename) as zip_file: contents = zip_file.namelist() if len(contents) != 1: logging.error( g_tr( 'Uralsib', "Archive contains multiple files, only one is expected for import" )) return False with zip_file.open(contents[0]) as r_file: self._statement = pandas.read_excel(io=r_file.read(), header=None, na_filter=False) if not self.validate(): return False self.load_cash_balance() self.load_broker_fee() self.load_stock_deals() self.load_futures_deals() self.load_cash_transactions() logging.info( g_tr('Uralsib', "Uralsib Capital statement loaded; Planned cash: ") + f"{self._settled_cash[self._account_id]}") return True
def validate(self): if self._statement[4][0] != self.Header: logging.error(g_tr('KIT', "Can't find KIT Finance report header")) return False parts = re.match(self.AccountPattern, self._statement[5][5], re.IGNORECASE) if parts is None: logging.error(g_tr('KIT', "Can't parse KIT Finance account number")) return False account_name = parts.groupdict()['ACCOUNT'] parts = re.match(self.PeriodPattern, self._statement[5][8], re.IGNORECASE) if parts is None: logging.error( g_tr('KIT', "Can't parse KIT Finance statement period")) return False statement_dates = parts.groupdict() report_start = int( datetime.strptime( statement_dates['S'], "%d.%m.%Y").replace(tzinfo=timezone.utc).timestamp()) if not self._parent.checkStatementPeriod(account_name, report_start): return False logging.info( g_tr('KIT', "Loading KIT Finance statement for account ") + f"{account_name}: {statement_dates['S']} - {statement_dates['E']}") self._account_id = JalDB().get_account_id(account_name) return True
def dividend(self, timestamp, number, amount, description): parts = re.match(self.DividendPattern, description, re.IGNORECASE) if parts is None: logging.error( g_tr('Uralsib', "Can't parse dividend description ") + f"'{description}'") return dividend_data = parts.groupdict() asset_id = JalDB().get_asset_id('', reg_code=dividend_data['REG_CODE']) if asset_id is None: logging.error( g_tr('Uralsib', "Can't find asset for dividend ") + f"'{description}'") return try: tax = float(dividend_data['TAX']) except ValueError: logging.error( g_tr('Uralsib', "Failed to convert dividend tax ") + f"'{description}'") return amount = amount + tax # Statement contains value after taxation while JAL stores value before tax shortened_description = dividend_data['DESCR1'] + ' ' + dividend_data[ 'DESCR2'] JalDB().add_dividend(DividendSubtype.Dividend, timestamp, self._account_id, asset_id, amount, shortened_description, trade_number=number, tax=tax)
def validate(self): if self._statement[2][0] != self.Header: logging.error( g_tr('Uralsib', "Can't find Uralsib Capital report header")) return False account_name = self._statement[2][7] parts = re.match(self.PeriodPattern, self._statement[2][2], re.IGNORECASE) if parts is None: logging.error( g_tr('Uralsib', "Can't parse Uralsib Capital statement period")) return False statement_dates = parts.groupdict() self._report_start = int( datetime.strptime( statement_dates['S'], "%d.%m.%Y").replace(tzinfo=timezone.utc).timestamp()) end_day = datetime.strptime(statement_dates['E'], "%d.%m.%Y") self._report_end = int( datetime.combine(end_day, time( 23, 59, 59)).replace(tzinfo=timezone.utc).timestamp()) if not self._parent.checkStatementPeriod(account_name, self._report_start): return False logging.info( g_tr('Uralsib', "Loading Uralsib Capital statement for account ") + f"{account_name}: {statement_dates['S']} - {statement_dates['E']}") self._account_id = JalDB().get_account_id(account_name) if self._account_id is None: return False return True
def __init__(self, table, parent_view): super().__init__(table, parent_view) self._columns = [("name", g_tr('ReferenceDataDialog', "Name")), ("often", g_tr('ReferenceDataDialog', "Often"))] self._stretch = "name" self._bool_delegate = None self._grid_delegate = None
def readCameraQR(self): self.initUi() if len(QCameraInfo.availableCameras()) == 0: logging.warning( g_tr('ImportSlipDialog', "There are no cameras available")) return self.cameraActive = True self.CameraGroup.setVisible(True) self.SlipDataGroup.setVisible(False) camera_info = QCameraInfo.defaultCamera() logging.info( g_tr('ImportSlipDialog', "Read QR with camera: " + camera_info.deviceName())) self.camera = QCamera(camera_info) self.camera.errorOccurred.connect(self.onCameraError) self.img_capture = QCameraImageCapture(self.camera) self.img_capture.setCaptureDestination( QCameraImageCapture.CaptureToBuffer) self.img_capture.setBufferFormat(QVideoFrame.Format_RGB32) self.img_capture.error.connect(self.onCameraCaptureError) self.img_capture.readyForCaptureChanged.connect(self.onReadyForCapture) self.img_capture.imageAvailable.connect(self.onCameraImageReady) self.camera.setViewfinder(self.Viewfinder) self.camera.setCaptureMode(QCamera.CaptureStillImage) self.camera.start()
def __init__(self, table, parent_view): super().__init__(table, parent_view) self._columns = [("name", g_tr('ReferenceDataDialog', "Name")), ("location", g_tr('ReferenceDataDialog', "Location")), ("actions_count", g_tr('ReferenceDataDialog', "Docs count"))] self._stretch = "name" self._int_delegate = None self._grid_delegate = None
def checkStatementPeriod(self, account_number, start_date) -> bool: if start_date < account_last_date(account_number): if QMessageBox().warning( None, g_tr('StatementLoader', "Confirmation"), g_tr( 'StatementLoader', "Statement period starts before last recorded operation for the account. Continue import?" ), QMessageBox.Yes, QMessageBox.No) == QMessageBox.No: return False return True
def closeEvent(self, event): if self.CommitBtn.isEnabled(): # There are uncommitted changed in a table if QMessageBox().warning(self, g_tr('ReferenceDataDialog', "Confirmation"), g_tr('ReferenceDataDialog', "You have uncommitted changes. Do you want to close?"), QMessageBox.Yes, QMessageBox.No) == QMessageBox.No: event.ignore() return else: self.model.revertAll() event.accept()
def __init__(self, table, parent_view): AbstractReferenceListModel.__init__(self, table, parent_view) self._columns = [("id", ''), ("name", g_tr('ReferenceDataDialog', "Country")), ("code", g_tr('ReferenceDataDialog', "Code")), ("tax_treaty", g_tr('ReferenceDataDialog', "Tax Treaty"))] self._sort_by = "name" self._hidden = ["id"] self._stretch = "name" self._bool_delegate = None
def transfer_out(self, timestamp, _number, amount, description): currency_name = JalDB().get_asset_name(JalDB().get_account_currency( self._account_id)) text = g_tr('Uralsib', "Withdrawal of ") + f"{-amount:.2f} {currency_name} " + \ f"@{datetime.utcfromtimestamp(timestamp).strftime('%d.%m.%Y')}\n" + \ g_tr('Uralsib', "Select account to deposit to:") pair_account = self._parent.selectAccount(text, self._account_id) if pair_account == 0: return # amount is negative in XLS file JalDB().add_transfer(timestamp, self._account_id, -amount, pair_account, -amount, 0, 0, description)
def transfer_in(self, timestamp, _number, amount, description): currency_name = JalDB().get_asset_name(JalDB().get_account_currency( self._account_id)) text = g_tr('Uralsib', "Deposit of ") + f"{amount:.2f} {currency_name} " + \ f"@{datetime.utcfromtimestamp(timestamp).strftime('%d.%m.%Y')}\n" + \ g_tr('Uralsib', "Select account to withdraw from:") pair_account = self._parent.selectAccount(text, self._account_id) if pair_account == 0: return JalDB().add_transfer(timestamp, pair_account, amount, self._account_id, amount, 0, 0, description)
def __init__(self, parent_view, db): self._columns = ["id", "pid", g_tr('DetailsModel', "Category"), g_tr('DetailsModel', "Tag"), g_tr('DetailsModel', "Amount"), g_tr('DetailsModel', "Amount"), g_tr('DetailsModel', "Note")] super().__init__(parent=parent_view, db=db) self.alt_currency = '' self._view = parent_view
def load(self): self._settled_cash = {} section_loaders = { 'CashReport': self.loadIBBalances, 'SecuritiesInfo': self. loadIBSecurities, # Order of load is important - SecuritiesInfo is first 'Trades': self.loadIBTrades, 'OptionEAE': self.loadIBOptions, 'CorporateActions': self.loadIBCorporateActions, 'CashTransactions': self.loadIBCashTransactions, 'TransactionTaxes': self.loadIBTaxes } try: xml_root = etree.parse(self._filename) for FlexStatements in xml_root.getroot(): for statement in FlexStatements: attr = statement.attrib report_start = int( datetime.strptime( attr['fromDate'], "%Y%m%d").replace(tzinfo=timezone.utc).timestamp()) if not self._parent.checkStatementPeriod( attr['accountId'], report_start): return False logging.info( g_tr('StatementLoader', "Load IB Flex-statement for account ") + f"{attr['accountId']}: {attr['fromDate']} - {attr['toDate']}" ) for section in section_loaders: section_elements = statement.xpath( section ) # Actually should be list of 0 or 1 element if section_elements: section_data = self.getIBdata(section_elements[0]) if section_data is None: return False section_loaders[section](section_data) except Exception as e: logging.error( g_tr('StatementLoader', "Failed to parse Interactive Brokers flex-report") + f": {e}") return False logging.info( g_tr('StatementLoader', "IB Flex-statement loaded successfully")) for account in self._settled_cash: logging.info( g_tr('StatementLoader', 'Planned cash: ') + f"{self._settled_cash[account]:.2f} " + f"{JalDB().get_asset_name(JalDB().get_account_currency(account))}" ) return True
def __init__(self, parent_view): self._columns = [("timestamp", g_tr("Reports", "Timestamp")), ("account", g_tr("Reports", "Account")), ("name", g_tr("Reports", "Peer Name")), ("sum", g_tr("Reports", "Amount")), ("note", g_tr("Reports", "Note"))] self._view = parent_view self._query = None self._timestamp_delegate = None self._float_delegate = None QSqlTableModel.__init__(self, parent=parent_view, db=db_connection())
def headerData(self, col, orientation, role=Qt.DisplayRole): if (orientation == Qt.Horizontal and role == Qt.DisplayRole): if col == 0: return g_tr('PandasLinesModel', "Product name") if col == 1: return g_tr('PandasLinesModel', "Category") if col == 3: return g_tr('PandasLinesModel', "Tag") if col == 4: return g_tr('PandasLinesModel', "Amount") return None
def __init__(self, table, parent_view): AbstractReferenceListModel.__init__(self, table, parent_view) self._columns = [("id", ''), ("timestamp", g_tr('ReferenceDataDialog', "Date")), ("asset_id", g_tr('ReferenceDataDialog', "Asset")), ("quote", g_tr('ReferenceDataDialog', "Quote"))] self._hidden = ["id"] self._default_name = "quote" self._lookup_delegate = None self._timestamp_delegate = None self.setRelation(self.fieldIndex("asset_id"), QSqlRelation("assets", "id", "name"))
def loadIBActivityStatement(self, filename): if QMessageBox().warning( None, g_tr('StatementLoader', "Confirmation"), g_tr( 'StatementLoader', "This is an obsolete routine for specific cases of old reports import.\n" "Use it with extra care if you understand what you are doing.\n" "Otherwise please use 'Interactive Brokers XML' import.\n" "Continue?"), QMessageBox.Yes, QMessageBox.No) == QMessageBox.No: return False return IBKR_obsolete(filename).load()
def deleteOperation(self): if QMessageBox().warning( None, g_tr('MainWindow', "Confirmation"), g_tr('MainWindow', "Are you sure to delete selected transacion(s)?"), QMessageBox.Yes, QMessageBox.No) == QMessageBox.No: return rows = [] for index in self.OperationsTableView.selectionModel().selectedRows(): rows.append(index.row()) self.operations_model.deleteRows(rows) self.ledger.rebuild()
def __init__(self, parent): QPushButton.__init__(self, parent) self.p_account_id = 0 self.Menu = QMenu(self) self.Menu.addAction(g_tr('AccountButton', "Choose account"), self.ChooseAccount) self.Menu.addAction(g_tr('AccountButton', "Any account"), self.ClearAccount) self.setMenu(self.Menu) self.dialog = AccountListDialog() self.setText(self.dialog.SelectedName)