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)
