public void readOutStockSavedTransactions()
{
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();
}
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();
}