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(); }