public void Display()
{
string xmlObject = "";
Nsye lastSearch = new Nsye();
//Get current user Id
int Id = Global.thisUser.Id;
string q = "select lastSearchAll from Users where Id = " + Id;
//get user data
DataTable dt1 = service.FetchProducts(q);
foreach (DataRow row in dt1.Rows)
{
xmlObject = row["lastSearchAll"].ToString();
}
//Check if user is new user!
if (xmlObject == "none")
{
MessageBox.Show("Welcome ! Make your first history search in order to see last search on homepage");
}
else
{
//Deserialize lastSearchAll (from current user) to Nyse object (lastSearch)
XmlSerializer serializer = new XmlSerializer(typeof(Nsye));
using (TextReader reader = new StringReader(xmlObject))
{
lastSearch = (Nsye)serializer.Deserialize(reader);
}
//CALL STORED PROCEDURE WHICH RETURN LAST SEARCH FROM CURRENT USER
//using (SqlCommand cmd = new SqlCommand("SearchData", con))
//{
// cmd.CommandType = CommandType.StoredProcedure;
// cmd.Parameters.Add("@exchange", SqlDbType.VarChar).Value = lastSearch.exchange;
// cmd.Parameters.Add("@symbol", SqlDbType.VarChar).Value = lastSearch.stock_symbol;
// cmd.Parameters.Add("@dateFrom", SqlDbType.VarChar).Value = lastSearch.date_from;
// cmd.Parameters.Add("@dateTo", SqlDbType.VarChar).Value = lastSearch.date_to;
// cmd.Parameters.Add("@priceOpenFrom", SqlDbType.VarChar).Value = lastSearch.stock_price_open_from;
// cmd.Parameters.Add("@priceOpenTo", SqlDbType.VarChar).Value = lastSearch.stock_price_open_to;
// cmd.Parameters.Add("@priceHighFrom", SqlDbType.VarChar).Value = lastSearch.stock_price_high_from;
// cmd.Parameters.Add("@priceHighTo", SqlDbType.VarChar).Value = lastSearch.stock_price_high_to;
// cmd.Parameters.Add("@priceCloseFrom", SqlDbType.VarChar).Value = lastSearch.stock_price_close_from;
// cmd.Parameters.Add("@priceCloseTo", SqlDbType.VarChar).Value = lastSearch.stock_price_close_to;
// cmd.Parameters.Add("@priceLowFrom", SqlDbType.VarChar).Value = lastSearch.stock_price_low_from;
// cmd.Parameters.Add("@priceLowTo", SqlDbType.VarChar).Value = lastSearch.stock_price_low_to;
// cmd.Parameters.Add("@adjFrom", SqlDbType.VarChar).Value = lastSearch.stock_price_adj_close_from;
// cmd.Parameters.Add("@adjTo", SqlDbType.VarChar).Value = lastSearch.stock_price_adj_close_to;
// cmd.Parameters.Add("@volumeFrom", SqlDbType.VarChar).Value = lastSearch.stock_volume_from;
// cmd.Parameters.Add("@volumeTo", SqlDbType.VarChar).Value = lastSearch.stock_volume_to;
// cmd.ExecuteNonQuery();
//Get last search data
//DataTable dt2 = FetchProducts1(cmd);
//Call WCF -> to db stored procedure for search history data
DataTable dt2 = service.SearchData(lastSearch);
//Insert data into datagrid_products
dataTable.ItemsSource = dt2.DefaultView;
dataTable.CanUserAddRows = false;
}
}
//FETCH All FROM DB / PUT IT IN DataTable x
//used only for testing, before stored procedures were implemented
//public DataTable FetchProducts(string query)
//{
// SqlCommand cmd2 = con.CreateCommand();
// cmd2.CommandType = CommandType.Text;
// cmd2.CommandText = query;
// cmd2.ExecuteNonQuery();
// DataTable dt1 = new DataTable();
// SqlDataAdapter da1 = new SqlDataAdapter(cmd2);
// da1.Fill(dt1);
// return dt1;
//}
//Stored procedure fetch method
//public DataTable FetchProducts1(SqlCommand cmd5)
//{
// DataTable dt2 = new DataTable();
// SqlDataAdapter da1 = new SqlDataAdapter(cmd5);
// da1.Fill(dt2);
// return dt2;
//}
//SEARCH HANDLER
private void SearchBtn_Click(object sender, RoutedEventArgs e)
{
try
{
//Store search inputs into Search (Nsye model)
Search = new ServiceReference1.Nsye();
//GET ALL INPUTS, AND SET DEFAULT VALUES IF THERE IS NO INPUT
//Exchange
Search.exchange = exchangeInput.SelectedValue.ToString();
//Symbol
Search.stock_symbol = symbolInput.SelectedValue.ToString();
//Open
if (openFromInput.Text != "")
{
Search.stock_price_open_from = float.Parse(openFromInput.Text);
}
else
{
Search.stock_price_open_from = 1;
}
if (openToInput.Text != "")
{
Search.stock_price_open_to = float.Parse(openToInput.Text);
}
else
{
Search.stock_price_open_to = 20;
}
//Close
if (closeInputFrom.Text != "")
{
Search.stock_price_close_from = float.Parse(closeInputFrom.Text);
}
else
{
Search.stock_price_close_from = 1;
}
if (closeInputTo.Text != "")
{
Search.stock_price_close_to = float.Parse(closeInputTo.Text);
}
else
{
Search.stock_price_close_to = 20;
}
//High
if (highInputFrom.Text != "")
{
Search.stock_price_high_from = float.Parse(highInputFrom.Text);
}
else
{
Search.stock_price_high_from = 1;
}
if (highInputTo.Text != "")
{
Search.stock_price_high_to = float.Parse(highInputTo.Text);
}
else
{
Search.stock_price_high_to = 20;
}
//Low
if (lowInputFrom.Text != "")
{
Search.stock_price_low_from = float.Parse(lowInputFrom.Text);
}
else
{
Search.stock_price_low_from = 1;
}
if (lowInputTo.Text != "")
{
Search.stock_price_low_to = float.Parse(lowInputTo.Text);
}
else
{
Search.stock_price_low_to = 20;
}
//Volume
if (volumeInputFrom.Text != "")
{
Search.stock_volume_from = int.Parse(volumeInputFrom.Text);
}
else
{
Search.stock_volume_from = 1;
}
if (volumeInputTo.Text != "")
{
Search.stock_volume_to = int.Parse(volumeInputTo.Text);
}
else
{
Search.stock_volume_to = 9999999;
}
//Adj
if (adjInputFrom.Text != "")
{
Search.stock_price_adj_close_from = float.Parse(adjInputFrom.Text);
}
else
{
Search.stock_price_adj_close_from = 1;
}
if (adjInputTo.Text != "")
{
Search.stock_price_adj_close_to = float.Parse(adjInputTo.Text);
}
else
{
Search.stock_price_adj_close_to = 20;
}
//Date
if (dateFrom.SelectedDate != null)
{
Search.date_from = dateFrom.SelectedDate.Value.ToString("d/MM/yyyy");
}
else
{
Search.date_from = "10/10/1999";
}
if (dateTo.SelectedDate != null)
{
Search.date_to = dateTo.SelectedDate.Value.ToString("d/MM/yyyy");
}
else
{
Search.date_to = "10/10/2020";
}
//Return Search
//string date_query = "SELECT * from nyse_history where convert(datetime, date, 103) between CONVERT(datetime, '"+Search.date_from+"', 103) and CONVERT(datetime,'"+Search.date_to+"')";
//string open_query = "SELECT * FROM nyse_history WHERE CONVERT(float, stock_price_open)" +" BETWEEN "+Search.stock_price_open_from+ " AND " +Search.stock_price_open_to;
//string mix = "SELECT * from nyse_history where (convert(datetime, date, 103) between CONVERT(datetime, '" + Search.date_from + "', 103) and CONVERT(datetime,'" + Search.date_to + "')) " +
// "AND (CONVERT(float, stock_price_open) BETWEEN " + Search.stock_price_open_from + " AND " + Search.stock_price_open_to+
// ") AND (CONVERT(float, stock_price_high) BETWEEN " + Search.stock_price_high_from + " AND " + Search.stock_price_high_to +")";
//CALL STORED PROCEDURE WHICH RETURN SEARCH FROM DB
//using (SqlCommand cmd = new SqlCommand("SearchData", con))
//{
// cmd.CommandType = CommandType.StoredProcedure;
// cmd.Parameters.Add("@exchange", SqlDbType.VarChar).Value = Search.exchange;
// cmd.Parameters.Add("@symbol", SqlDbType.VarChar).Value = Search.stock_symbol;
// cmd.Parameters.Add("@dateFrom", SqlDbType.VarChar).Value = Search.date_from;
// cmd.Parameters.Add("@dateTo", SqlDbType.VarChar).Value = Search.date_to;
// cmd.Parameters.Add("@priceOpenFrom", SqlDbType.VarChar).Value = Search.stock_price_open_from;
// cmd.Parameters.Add("@priceOpenTo", SqlDbType.VarChar).Value = Search.stock_price_open_to;
// cmd.Parameters.Add("@priceHighFrom", SqlDbType.VarChar).Value = Search.stock_price_high_from;
// cmd.Parameters.Add("@priceHighTo", SqlDbType.VarChar).Value = Search.stock_price_high_to;
// cmd.Parameters.Add("@priceCloseFrom", SqlDbType.VarChar).Value = Search.stock_price_close_from;
// cmd.Parameters.Add("@priceCloseTo", SqlDbType.VarChar).Value = Search.stock_price_close_to;
// cmd.Parameters.Add("@priceLowFrom", SqlDbType.VarChar).Value = Search.stock_price_low_from;
// cmd.Parameters.Add("@priceLowTo", SqlDbType.VarChar).Value = Search.stock_price_low_to;
// cmd.Parameters.Add("@adjFrom", SqlDbType.VarChar).Value = Search.stock_price_adj_close_from;
// cmd.Parameters.Add("@adjTo", SqlDbType.VarChar).Value = Search.stock_price_adj_close_to;
// cmd.Parameters.Add("@volumeFrom", SqlDbType.VarChar).Value = Search.stock_volume_from;
// cmd.Parameters.Add("@volumeTo", SqlDbType.VarChar).Value = Search.stock_volume_to;
// cmd.ExecuteNonQuery();
//Call WCF -> to db stored procedure for search history data
dt2 = service.SearchData(Search);
//Call Verification method (OVERLOAD for assignment requirement) to check if dt2 have more than 1 row...
bool moreThanOneRow = Global.Verification(dt2, 1);
if (!moreThanOneRow)
{
MessageBox.Show("There are no results, Please change your filters and try again.");
}
;
//Insert data into datagrid_products
dataGrid_nyse.ItemsSource = dt2.DefaultView;
dataGrid_nyse.CanUserAddRows = false;
//Serialise Search and store in database
lastSearchHistory = Global.SerializeToXml(Search);
//SqlCommand cmd3 = con.CreateCommand();
//cmd3.CommandType = CommandType.Text;
//cmd3.CommandText = "UPDATE Users SET lastSearchAll = '" +lastSearchHistory+ "' , lastSearchAll= '"+ lastSearchHistory + "' WHERE Id = " + Global.thisUser.Id;
//cmd3.ExecuteNonQuery();
int SearchToDb = service.lastSearch(lastSearchHistory, Global.thisUser.Id);
if (SearchToDb != 1)
{
MessageBox.Show("Last Search is not saved to history search, something went wrong!");
}
//update number of results
noResults.Text = "Number of results: " + dt2.Rows.Count.ToString();
//Fill Visualise by: combo box:
foreach (DataColumn column in dt2.Columns)
{
colNames.Add(column.ColumnName.ToString());
}
//add items from colList
colNames.Clear();
visualiseInputBy.Items.Clear();
foreach (DataColumn column in dt2.Columns)
{
colNames.Add(column.ColumnName.ToString());
}
foreach (string item in colNames)
{
visualiseInputBy.Items.Add(item);
}
visualiseInputBy.Items.Remove("date");
visualiseInputBy.Items.Remove("stock_symbol");
visualiseInputBy.Items.Remove("exchange");
//}
}
catch (System.Exception ex)
{
Global.CustomLog(ex.Message.ToString());
Global.CustomLog(ex.StackTrace.ToString());
Global.CustomLog("-------------------------------------------------------------------------------------------------------");
MessageBox.Show("Something went wrong! \n Please use right search format. \n For more informations check help on homepage.");
}
}
