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 static SpecifiedImportBank getInstance(List <string> newfoldetPath, MainWindow mainWindow)
{
if (newfoldetPath != null)
{
folderPath = newfoldetPath;
}
if (instance == null)
{
instance = new SpecifiedImportBank(mainWindow);
}
return(instance);
}
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 ButtonCommands(SpecifiedImportBank specifiedImport, string fileName)
{
this.specifiedImport = specifiedImport;
currentFileName = fileName;
specifiedImport.PropertyChanged += new PropertyChangedEventHandler(test_PropertyChanged);
}
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);
}
}
