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);
}
Console.WriteLine(startingRow + " - " + nameColumn + " - " + dateColumn + " - " + priceColumn);
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.ToString(), 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 = SpecifiedImportStock.getInstance(null, mainWindow).storedTypesCB.SelectedItem.ToString();
}
else
{
bankName = SpecifiedImportStock.getInstance(null, mainWindow).newBankTextbox.Text.ToString();
}
/*
* for (int i = 0; i < importedStocks.Count; i++)
* importedStocks[i].setBankname(bankName);
*/
stockHandler.addTransactions(importedStocks);
//todo another thread
addImportFileDataToDB(int.Parse(startingRowString), nameColumnString,
priceColumnString, quantityColumnString, dateColumnString, transactionTypeColumnString);
}
}
public void readOutStockSavedTransactions()
{
using (SQLiteCommand mCmd = new SQLiteCommand("CREATE TABLE IF NOT EXISTS [importedStockTransactions] " +
"(id INTEGER PRIMARY KEY AUTOINCREMENT, 'ExportDate' TEXT, 'TransactionDate' TEXT, " +
"'StockName' TEXT, 'StockPrice' TEXT, 'SoldQuantity' INTEGER, 'BoughtQuantity' INTEGER, " +
"'CurrentQuantity' INTEGER, 'Spending' TEXT, 'Profit' TEXT," +
" 'EarningMethod' TEXT, 'ImporterName' TEXT);", mConn))
{
mCmd.ExecuteNonQuery();
}
string getAllTransactionsQuery = "select * from [importedStockTransactions]";
SQLiteCommand command = new SQLiteCommand(getAllTransactionsQuery, mConn);
DataTable dtb = new DataTable();
SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
adapter.Fill(dtb);
if (dtb.Rows.Count != 0)
{
string writeoutDate = "";
string stockName = "";
string transactionDate = "";
string stockPrice = "";
int originalQuantity = 0;
string transactionType = "";
string importer = "";
int currentQuantity = 0;
string originalAndCurrentQuantity = "";
string profit = "";
string earningMethod = "";
foreach (DataRow row in dtb.Rows)
{
writeoutDate = row["ExportDate"].ToString();
transactionDate = row["TransactionDate"].ToString();
stockName = row["StockName"].ToString();
stockPrice = row["StockPrice"].ToString();
if (int.Parse(row["SoldQuantity"].ToString()) != 0)
{
transactionType = "Sell";
originalQuantity = int.Parse(row["SoldQuantity"].ToString());
originalAndCurrentQuantity = originalQuantity.ToString();
profit = row["Profit"].ToString();
earningMethod = row["EarningMethod"].ToString();
}
else if (int.Parse(row["BoughtQuantity"].ToString()) != 0)
{
transactionType = "Buy";
originalQuantity = int.Parse(row["BoughtQuantity"].ToString());
currentQuantity = int.Parse(row["CurrentQuantity"].ToString());
originalAndCurrentQuantity = originalQuantity.ToString() + " (" + currentQuantity + ")";
}
importer = row["ImporterName"].ToString();
Stock stock = new Stock(writeoutDate, transactionDate, stockName, stockPrice, originalQuantity, transactionType, importer);
if (stock.getTransactionType() == "Sell")
{
stock.setProfit(profit);
stock.setEarningMethod(earningMethod);
}
else
{
stock.setCurrentQuantity(currentQuantity);
}
stock.setOriginalAndSellQuantity(originalAndCurrentQuantity);
savedTransactionsStock.Add(stock);
}
}
mConn.Close();
/*
* sqlConn.Open();
* string query = "Select * From [importedStockTransactions]";
* SqlDataAdapter sda = new SqlDataAdapter(query, sqlConn);
* System.Data.DataTable dtb = new System.Data.DataTable();
* sda.Fill(dtb);
* if (dtb.Rows.Count != 0)
* {
* string writeoutDate = "";
* string stockName = "";
* string transactionDate = "";
* double stockPrice = 0;
* int originalQuantity = 0;
* string transactionType = "";
* string importer = "";
* string currentQuantity = "";
* string originalAndCurrentQuantity = "";
* double profit = 0;
* string earningMethod="";
* foreach (System.Data.DataRow row in dtb.Rows)
* {
* writeoutDate = row["ExportDate"].ToString();
* transactionDate = row["TransactionDate"].ToString();
* stockName = row["StockName"].ToString();
* stockPrice = double.Parse(row["StockPrice"].ToString());
* if (row["SoldQuantity"] != DBNull.Value)
* {
* transactionType = "Sell";
* originalQuantity = int.Parse(row["SoldQuantity"].ToString());
* originalAndCurrentQuantity = originalQuantity.ToString();
* profit = double.Parse(row["Profit"].ToString());
* earningMethod = row["EarningMethod"].ToString();
* }
* else if (row["BoughtQuantity"] != DBNull.Value)
* {
* transactionType = "Buy";
* originalQuantity = int.Parse(row["BoughtQuantity"].ToString());
* currentQuantity = row["CurrentQuantity"].ToString();
* originalAndCurrentQuantity = originalQuantity.ToString() + " (" + currentQuantity + ")";
* }
* importer = row["ImporterName"].ToString();
* Stock stock = new Stock(writeoutDate, transactionDate, stockName, stockPrice, originalQuantity, transactionType, importer);
* if(stock.getTransactionType()=="Sell")
* {
* stock.setProfit(profit);
* stock.setEarningMethod(earningMethod);
* }
* else
* {
* stock.setCurrentQuantity(int.Parse(currentQuantity));
* }
* stock.setOriginalAndSellQuantity(originalAndCurrentQuantity);
* savedTransactionsStock.Add(stock);
* }
* }
*
* /*
* ReadWorksheet = ReadWorkbook.Worksheets[2];
* int i = 2;
* while (ReadWorksheet.Cells[i, 1].Value != null)
* {
* string writeoutDate = "";
* string stockName = "";
* string transactionDate = "";
* string stockPriceString = "";
* double stockPrice = 0;
* int originalQuantity = 0;
* string transactionType = "";
* string importer = "";
* string currentQuantity = "";
* string currentAndOriginal = "";
* writeoutDate = ReadWorksheet.Cells[i, 1].Value.ToString();
* transactionDate = ReadWorksheet.Cells[i, 2].Value.ToString();
* stockName = ReadWorksheet.Cells[i, 3].Value.ToString();
* stockPriceString = ReadWorksheet.Cells[i, 4].Value.ToString().Replace(',','.');
* stockPrice = double.Parse(stockPriceString, CultureInfo.InvariantCulture);
* string quantityString = "";
* if (ReadWorksheet.Cells[i,5].Value!=null)//eladott
* {
* quantityString = ReadWorksheet.Cells[i, 5].Value.ToString();
* originalQuantity=int.Parse(quantityString);
* transactionType = "Sell";
* }
* else if(ReadWorksheet.Cells[i,6].Value!=null)//vásárolt
* {
* quantityString = ReadWorksheet.Cells[i, 6].Value.ToString();
* originalQuantity = int.Parse(quantityString);
* transactionType = "Buy";
* }
* if (ReadWorksheet.Cells[i, 7].Value != null) //jelenlegi darab
* {
* currentQuantity = ReadWorksheet.Cells[i, 7].Value.ToString();
* }
* currentAndOriginal = originalQuantity.ToString() + " (" + currentQuantity + ")";
* if (ReadWorksheet.Cells[i,11].Value!=null)
* {
* importer=ReadWorksheet.Cells[i, 11].Value.ToString();
* }
* Stock stock = new Stock(writeoutDate, transactionDate, stockName, stockPrice, originalQuantity, transactionType,importer);
* if(stock.getTransactionType()=="Sell")
* {
* string profitString = "";
* string earningMethod = "-";
* double profit = 0;
* if (ReadWorksheet.Cells[i,9].Value!=null)
* {
* profitString = ReadWorksheet.Cells[i, 9].Value.ToString().Replace(',', '.');
* profit = double.Parse(profitString, CultureInfo.InvariantCulture);
* stock.setProfit(profit);
* }
* if(ReadWorksheet.Cells[i,9].Value!=null)
* {
* earningMethod = ReadWorksheet.Cells[i, 10].Value.ToString();
* }
* stock.setEarningMethod(earningMethod);
* }
* stock.setOriginalAndSellQuantity(currentAndOriginal);
* savedTransactionsStock.Add(stock);
* i++;
* }
* excel.Workbooks.Close();
* excel.Quit();
*/
//sqlConn.Close();
}