public ImportReadIn(string _importType, List <string> _path, MainWindow _mainWindow, bool specifiedByUser) { path = _path; importType = _importType; mainWindow = _mainWindow; if (path[0] != "FolderAdress")//a path wasn't choosen, useless ( not in use ) { for (int i = 0; i < path.Count; i++) { string [] splittedFileName = path[i].Split('\\'); int lastSplitIndex = splittedFileName.Length - 1; currentFileName = splittedFileName[lastSplitIndex]; if (importType == "Bank") { ReadWorkbook = excel.Workbooks.Open(path[i]); ReadWorksheet = ReadWorkbook.Worksheets[1]; if (!specifiedByUser) { TemplateBankReadIn templateBank = new TemplateBankReadIn(this, ReadWorkbook, ReadWorksheet, mainWindow, false); //so far we got the Starting Row(of the transactions),Number of Columns, account number templateBank.readOutTransactionColumns(templateBank.getStartingRow(), templateBank.getNumberOfColumns()); } else //userSpecified==true { TemplateBankReadIn templateBank = new TemplateBankReadIn(this, ReadWorkbook, ReadWorksheet, mainWindow, true); string startingRow = SpecifiedImportBank.getInstance(null, mainWindow).transactionsRowTextBox.Text.ToString(); string dateColumn = SpecifiedImportBank.getInstance(null, mainWindow).dateColumnTextBox.Text.ToString(); string commentColumn = SpecifiedImportBank.getInstance(null, mainWindow).commentColumnTextBox.Text.ToString(); string accountNumberCB = SpecifiedImportBank.getInstance(null, mainWindow).accountNumberCB.SelectedItem.ToString(); string transactionPriceCB = SpecifiedImportBank.getInstance(null, mainWindow).priceColumnCB.SelectedItem.ToString(); string balanceCB = SpecifiedImportBank.getInstance(null, mainWindow).balanceColumnCB.SelectedItem.ToString(); string balanceComboBocString = SpecifiedImportBank.getInstance(null, mainWindow).balanceColumnTextBox.Text.ToString(); templateBank.readOutUserspecifiedTransactions(startingRow, dateColumn, commentColumn, accountNumberCB, transactionPriceCB, balanceCB, balanceComboBocString); } } else if (importType == "Stock") { if (!specifiedByUser) { TemplateStockReadIn templateStock = new TemplateStockReadIn(this, path[i]); templateStock.analyzeStockTransactionFile(); templateStock.readOutTransactions(); } else//userSpecified==true { TemplateStockReadIn templateStock = new TemplateStockReadIn(this, path[i]); string startingRowString = SpecifiedImportStock.getInstance(null, mainWindow).transactionsRowTextBox.ToString(); string nameColumnString = SpecifiedImportStock.getInstance(null, mainWindow).stockNameColumnTextBox.ToString(); string priceColumnString = SpecifiedImportStock.getInstance(null, mainWindow).priceColumnTextBox.ToString(); string quantityColumnString = SpecifiedImportStock.getInstance(null, mainWindow).quantityColumnTextBox.ToString(); string dateColumnString = SpecifiedImportStock.getInstance(null, mainWindow).dateColumnTextBox.ToString(); string transactionTypeString = SpecifiedImportStock.getInstance(null, mainWindow).transactionTypeTextBox.ToString(); templateStock.readOutUserspecifiedTransactions(startingRowString, nameColumnString, priceColumnString, quantityColumnString, dateColumnString, transactionTypeString); } } } excel.Application.Quit(); excel.Quit(); } }
public void setSpecifiedImportPageTextBoxes() { if (mostMatchingRow != null) { SpecifiedImportBank.getInstance(null, mainWindow).bankChoice = mostMatchingRow["BankName"].ToString(); SpecifiedImportBank.getInstance(null, mainWindow).transactionsRowTextBox.Text = mostMatchingRow["TransStartRow"].ToString(); SpecifiedImportBank.getInstance(null, mainWindow).accountNumberChoice = accountNumberComboBox; SpecifiedImportBank.getInstance(null, mainWindow).accountNumberTextBox.Text = mostMatchingRow["AccountNumberPos"].ToString(); SpecifiedImportBank.getInstance(null, mainWindow).dateColumnTextBox.Text = mostMatchingRow["DateColumn"].ToString(); SpecifiedImportBank.getInstance(null, mainWindow).priceColumnChoice = priceComboBox; if (priceComboBox == "One column") { SpecifiedImportBank.getInstance(null, mainWindow).priceColumnTextBox_1.Text = mostMatchingRow["PriceColumn"].ToString(); } else { string[] splittedPriceColumns = mostMatchingRow["PriceColumn"].ToString().Split(','); SpecifiedImportBank.getInstance(null, mainWindow).priceColumnTextBox_1.Text = splittedPriceColumns[0]; SpecifiedImportBank.getInstance(null, mainWindow).priceColumnTextBox_1.Text = splittedPriceColumns[1]; } SpecifiedImportBank.getInstance(null, mainWindow).balanceColumnChoice = balanceComboBox; if (balanceComboBox != "None") { SpecifiedImportBank.getInstance(null, mainWindow).balanceColumnTextBox.Text = mostMatchingRow["BalanceColumn"].ToString(); } SpecifiedImportBank.getInstance(null, mainWindow).commentColumnTextBox.Text = mostMatchingRow["CommentColumn"].ToString(); } else//no data in sql { SpecifiedImportBank.getInstance(null, mainWindow).bankChoice = "Add new Bank"; } }
public void addDistinctBanksToCB() { foreach (var item in SpecifiedImportBank.getInstance(null, mainWindow).bankChoices.ToList()) { if (item != "Add new Bank") { SpecifiedImportBank.getInstance(null, mainWindow).bankChoices.Remove(item); } } SqlConnection sqlConn = new SqlConnection("Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=ImportFileData;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"); sqlConn.Open(); string getEveryRow = "Select distinct BankName From [StoredColumns]"; SqlDataAdapter sda = new SqlDataAdapter(getEveryRow, sqlConn); System.Data.DataTable datatable = new System.Data.DataTable(); sda.Fill(datatable); if (datatable.Rows.Count > 0) { foreach (DataRow row in dtb.Rows) { SpecifiedImportBank.getInstance(null, mainWindow).bankChoices.Add(row["BankName"].ToString()); } } }
public void getDataTableFromSql(MainWindow mainWindow) { mConn.Open(); this.mainWindow = mainWindow; using (SQLiteCommand mCmd = new SQLiteCommand("CREATE TABLE IF NOT EXISTS [StoredColumnsBank] " + "(id INTEGER PRIMARY KEY AUTOINCREMENT, 'BankName' TEXT, 'TransStartRow' INTEGER, " + "'AccountNumberPos' TEXT, 'DateColumn' TEXT, 'PriceColumn' TEXT, 'BalanceColumn' TEXT, " + "'CommentColumn' TEXT);", mConn)) { mCmd.ExecuteNonQuery(); } string storedQuery = "select * from [StoredColumnsBank]"; SQLiteCommand command = new SQLiteCommand(storedQuery, mConn); System.Data.DataTable datatable = new System.Data.DataTable(); SQLiteDataAdapter adapter = new SQLiteDataAdapter(command); adapter.Fill(datatable); /* * SqlConnection sqlConn = new SqlConnection("Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=ImportFileData;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"); * sqlConn.Open(); * string getEveryRow = "Select * From [StoredColumns]"; * SqlDataAdapter sda = new SqlDataAdapter(getEveryRow, sqlConn); * System.Data.DataTable datatable = new System.Data.DataTable(); * sda.Fill(datatable); */ dtb = datatable; SpecifiedImportBank.getInstance(null, mainWindow).setDataTableFromSql(datatable); mConn.Close(); }
public void Execute(object parameter) { if (buttonContent.Equals("Import Bank Transactions")) { MessageBoxResult messageBoxResult = CustomMessageBox.ShowYesNo( "\tPlease choose an import type!", "Import type alert!", "Automatized", "User specified"); if (messageBoxResult == MessageBoxResult.Yes || messageBoxResult == MessageBoxResult.No) { Microsoft.Win32.OpenFileDialog dlg = new Microsoft.Win32.OpenFileDialog(); dlg.DefaultExt = ".xls,.csv"; dlg.Filter = "Excel files (*.xls)|*.xls|Excel Files (*.xlsx)|*.xlsx|Excel Files (*.xlsm)|*.xlsm|CSV Files (*.csv)|*.csv"; dlg.Multiselect = true; Nullable <bool> result = dlg.ShowDialog(); if (result == true) { List <string> fileAdresses = dlg.FileNames.ToList(); for (int i = 0; i < dlg.FileNames.ToList().Count; i++) { check_if_csv(i, ref fileAdresses); } if (messageBoxResult == MessageBoxResult.Yes) { importPageBank.getTransactions("Bank", fileAdresses); } else if (messageBoxResult == MessageBoxResult.No) { string[] fileName = dlg.FileNames.ToList()[0].Split('\\'); int lastPartIndex = fileName.Length - 1; // to see which file the user immporting first SpecifiedImportBank.getInstance(fileAdresses, importPageBank.mainWindow).setCurrentFileLabel(fileName[lastPartIndex]); //fájl felismerés SpecifiedImportBank.getInstance(null, importPageBank.mainWindow).setBoxValuesToZero(); StoredColumnChecker columnChecker = new StoredColumnChecker(); columnChecker.getDataTableFromSql(importPageBank.mainWindow); columnChecker.addDistinctBanksToCB(); columnChecker.setAnalyseWorksheet(dlg.FileNames.ToList()[0]); columnChecker.setMostMatchesRow(columnChecker.findMostMatchingRow()); columnChecker.setSpecifiedImportPageTextBoxes(); importPageBank.mainWindow.MainFrame.Content = SpecifiedImportBank.getInstance(dlg.FileNames.ToList(), importPageBank.mainWindow); } } } } else if (buttonContent == "importHistoryButton") { ImportStatsChartBank importStatsChart = new ImportStatsChartBank(importPageBank.banks); importStatsChart.selectedBank = "All"; importPageBank.mainWindow.MainFrame.Content = importStatsChart; } }
public void getDataTableFromSql(MainWindow mainWindow) { this.mainWindow = mainWindow; SqlConnection sqlConn = new SqlConnection("Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=ImportFileData;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"); sqlConn.Open(); string getEveryRow = "Select * From [StoredColumns]"; SqlDataAdapter sda = new SqlDataAdapter(getEveryRow, sqlConn); System.Data.DataTable datatable = new System.Data.DataTable(); sda.Fill(datatable); dtb = datatable; SpecifiedImportBank.getInstance(null, mainWindow).setDataTableFromSql(datatable); }
public void addDistinctBanksToCB() { mConn.Open(); foreach (var item in SpecifiedImportBank.getInstance(null, mainWindow).bankChoices.ToList()) { if (item != "Add new Bank") { SpecifiedImportBank.getInstance(null, mainWindow).bankChoices.Remove(item); } } using (SQLiteCommand mCmd = new SQLiteCommand("CREATE TABLE IF NOT EXISTS [StoredColumnsBank] " + "(id INTEGER PRIMARY KEY AUTOINCREMENT, 'BankName' TEXT, 'TransStartRow' INTEGER, " + "'AccountNumberPos' TEXT, 'DateColumn' TEXT, 'PriceColumn' TEXT, 'BalanceColumn' TEXT, " + "'CommentColumn' TEXT);", mConn)) { mCmd.ExecuteNonQuery(); } string storedQuery = "select distinct BankName from [StoredColumnsBank]"; SQLiteCommand command = new SQLiteCommand(storedQuery, mConn); System.Data.DataTable datatable = new System.Data.DataTable(); SQLiteDataAdapter adapter = new SQLiteDataAdapter(command); adapter.Fill(datatable); if (datatable.Rows.Count > 0) { foreach (DataRow row in dtb.Rows) { SpecifiedImportBank.getInstance(null, mainWindow).bankChoices.Add(row["BankName"].ToString()); } } mConn.Close(); /* * SqlConnection sqlConn = new SqlConnection("Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=ImportFileData;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"); * sqlConn.Open(); * string getEveryRow = "Select distinct BankName From [StoredColumns]"; * SqlDataAdapter sda = new SqlDataAdapter(getEveryRow, sqlConn); * System.Data.DataTable datatable = new System.Data.DataTable(); * sda.Fill(datatable); * if (datatable.Rows.Count > 0) * { * foreach (DataRow row in dtb.Rows) * { * SpecifiedImportBank.getInstance(null,mainWindow).bankChoices.Add(row["BankName"].ToString()); * } * } */ }
public void setSpecifiedImportPageTextBoxes() { if (mostMatchingRow != null) { SpecifiedImportStock.getInstance(null, mainWindow).bankChoice = mostMatchingRow["BankName"].ToString(); SpecifiedImportStock.getInstance(null, mainWindow).transactionsRowTextBox.Text = mostMatchingRow["TransStartRow"].ToString(); SpecifiedImportStock.getInstance(null, mainWindow).stockNameColumnTextBox.Text = mostMatchingRow["StockName"].ToString(); SpecifiedImportStock.getInstance(null, mainWindow).priceColumnTextBox.Text = mostMatchingRow["PriceColumn"].ToString(); SpecifiedImportStock.getInstance(null, mainWindow).quantityColumnTextBox.Text = mostMatchingRow["QuantityColumn"].ToString(); SpecifiedImportStock.getInstance(null, mainWindow).dateColumnTextBox.Text = mostMatchingRow["DateColumn"].ToString(); SpecifiedImportStock.getInstance(null, mainWindow).transactionTypeTextBox.Text = mostMatchingRow["TypeColumn"].ToString(); } else//no data in sql { SpecifiedImportBank.getInstance(null, mainWindow).bankChoice = "Add new Type"; } }
public void readOutUserspecifiedTransactions(string startingRowString, string nameColumnString, string priceColumnString, string quantityColumnString, string dateColumnString, string transactionTypeColumnString) { int startingRow = 0; try { startingRow = int.Parse(startingRowString); } catch (Exception e) { startingRow = ExcelColumnNameToNumber(startingRowString); } int nameColumn = 0; try { nameColumn = int.Parse(nameColumnString); } catch (Exception e) { nameColumn = ExcelColumnNameToNumber(nameColumnString); } int priceColumn = 0; try { priceColumn = int.Parse(priceColumnString); } catch (Exception e) { priceColumn = ExcelColumnNameToNumber(priceColumnString); } int quantityColumn = 0; try { quantityColumn = int.Parse(quantityColumnString); } catch (Exception e) { quantityColumn = ExcelColumnNameToNumber(quantityColumnString); } int dateColumn = 0; try { dateColumn = int.Parse(dateColumnString); } catch (Exception e) { dateColumn = ExcelColumnNameToNumber(dateColumnString); } int transactionTypeColumn = 0; try { transactionTypeColumn = int.Parse(transactionTypeColumnString); } catch (Exception e) { transactionTypeColumn = ExcelColumnNameToNumber(transactionTypeColumnString); } if ((nameColumn != 0) && (dateColumn != 0) && (priceColumn != 0)) { importedStocks = new List <Stock>(); int blank_cell_counter = 0; while (blank_cell_counter < 2) { if ((stockWorksheet.Cells[startingRow, nameColumn].Value != null) && (stockWorksheet.Cells[startingRow, dateColumn].Value != null) && (stockWorksheet.Cells[startingRow, priceColumn].Value != null)) { blank_cell_counter = 0; string companyName = stockWorksheet.Cells[startingRow, nameColumn].Value.ToString(); string transactionDate = stockWorksheet.Cells[startingRow, dateColumn].Value.ToString(); string transactionPriceString = stockWorksheet.Cells[startingRow, priceColumn].Value.ToString().Replace(',', '.'); double transactionPrice = 0; try { transactionPrice = double.Parse(transactionPriceString, CultureInfo.InvariantCulture); } catch (Exception e) { } string transactionType = "-"; string quantityString = ""; int quantity = 1; if (stockWorksheet.Cells[startingRow, transactionTypeColumn].Value != null) { transactionType = stockWorksheet.Cells[startingRow, transactionTypeColumn].Value.ToString(); } if (stockWorksheet.Cells[startingRow, quantityColumn].Value != null) { quantityString = stockWorksheet.Cells[startingRow, quantityColumn].Value.ToString(); try { quantity = int.Parse(quantityString); } catch (Exception e) { } } Stock stock = new Stock(companyName, transactionPrice, quantity, transactionDate, transactionType); importedStocks.Add(stock); } else { blank_cell_counter++; } startingRow++; } } if (importedStocks.Count > 0) { string bankName = ""; if (SpecifiedImportStock.getInstance(null, mainWindow).storedTypesCB.SelectedItem.ToString() != "Add new Type") { bankName = SpecifiedImportBank.getInstance(null, mainWindow).storedTypesCB.SelectedItem.ToString(); } else { bankName = SpecifiedImportBank.getInstance(null, mainWindow).newBankTextbox.Text.ToString(); } /* * for (int i = 0; i < importedStocks.Count; i++) * importedStocks[i].setBankname(bankName); * bankHanlder.addTransactions(transactions); * //todo another thread */ addImportFileDataToDB(int.Parse(startingRowString), nameColumnString, priceColumnString, quantityColumnString, dateColumnString, transactionTypeColumnString); } }