public List <FarmTran> Xml_Load()
{
XDocument docNew = XDocument.Load(SharedDB.GetDataPath() + "FarmTransData.xml");
//Console.WriteLine(docNew.ToString());
System.Collections.Generic.IEnumerable <XElement> nodes = docNew.Element("DocumentElement").Elements("row");
var nodeList = new List <FarmTran>();
nodeList = nodes
.Select(node => {
var item = new FarmTran();
item.transactionDate = getValue(node, "交易日期");
item.cropCode = getValue(node, "作物代號");
item.cropName = getValue(node, "作物名稱");
item.marketCode = getValue(node, "市場代號");
item.marketName = getValue(node, "市場名稱");
item.priceHigh = getValue(node, "上價");
item.priceMid = getValue(node, "中價");
item.priceLow = getValue(node, "下價");
item.priceAvg = getValue(node, "平均價");
item.transactionNum = getValue(node, "交易量");
return(item);
}).ToList();
return(nodeList);
}
public void deleteEventRegistrations(int id)
{
SharedDB.setConnectionString();
SharedDB.command = new MySqlCommand("DELETE FROM Store_Event_User WHERE store_event_id = " + id, SharedDB.connection);
SharedDB.connection.Open();
using (SharedDB.connection)
{
SharedDB.command.ExecuteNonQuery();
}
}
public void insertDefaultRatings()
{
SharedDB.setConnectionString();
using (SharedDB.connection)
{
SharedDB.connection.Open();
SharedDB.command = new MySqlCommand("INSERT INTO User_Game (user_game_id, user_id, game_id, date_purchased, rating) VALUES (8, 1, 3, 01-01-2001, 5), " +
"(9, 2, 3, 01-01-2001, 4), (10, 3, 3, 01-01-2001, 4), (11, 4, 3, 01-01-2001, 4), (12, 5, 3, 01-01-2001, 4)", SharedDB.connection);
SharedDB.command.ExecuteNonQuery();
}
}
public ActionResult Register(int store_event_id)
{
int user_id = db.Users.Where(u => u.username == this.User.Identity.Name).FirstOrDefault().user_id;
SharedDB.setConnectionString();
SharedDB.command = new MySqlCommand("INSERT INTO Store_Event_User (store_event_id, user_id) VALUES (" + store_event_id + ", " + user_id + ")", SharedDB.connection);
SharedDB.connection.Open();
using (SharedDB.connection)
{
SharedDB.command.ExecuteNonQuery();
}
return(RedirectToAction("UserEventsIndex"));
}
public ActionResult UnRegister(int store_event_id)
{
int user_id = db.Users.Where(u => u.username == this.User.Identity.Name).FirstOrDefault().user_id;
SharedDB.setConnectionString();
SharedDB.command = new MySqlCommand("SELECT store_event_id FROM ", SharedDB.connection);
SharedDB.command = new MySqlCommand("DELETE FROM Store_Event_User WHERE store_event_id = " + store_event_id + " AND user_id = " +
user_id, SharedDB.connection);
SharedDB.connection.Open();
using (SharedDB.connection)
{
SharedDB.command.ExecuteNonQuery();
}
return(RedirectToAction("Index"));
}
public string[] getGameIDS()
{
string[] results;
int num_of_games = 0;
SharedDB.setConnectionString();
SharedDB.connection.Open();
using (SharedDB.connection)
{
SharedDB.command = new MySqlCommand("SELECT COUNT(game_id) FROM Game;", SharedDB.connection);
MySqlDataReader reader = SharedDB.command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
num_of_games = reader.GetInt32(0);
}
}
}
results = new string[num_of_games];
SharedDB.connection.Open();
using (SharedDB.connection)
{
SharedDB.command = new MySqlCommand("SELECT game_id FROM Game;", SharedDB.connection);
MySqlDataReader reader = SharedDB.command.ExecuteReader();
if (reader.HasRows)
{
string[] temp = new string[num_of_games];
int counter = 0;
while (reader.Read())
{
temp[counter] = reader.GetInt32(0).ToString();
counter++;
}
results = temp;
reader.Close();
}
}
return(sanitizeArray(results));
}
public string[] getEventIDS()
{
int num_of_events = 0;
SharedDB.setConnectionString();
SharedDB.command = new MySqlCommand("SELECT store_event_id FROM Store_Event ORDER BY store_event_id DESC LIMIT 1", SharedDB.connection);
SharedDB.connection.Open();
using (SharedDB.connection)
{
MySqlDataReader reader = SharedDB.command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
num_of_events = reader.GetInt32(0);
}
}
}
string[] results = new string[num_of_events];
int counter = 0;
SharedDB.command = new MySqlCommand("SELECT store_event_id FROM Store_Event", SharedDB.connection);
SharedDB.connection.Open();
using (SharedDB.connection)
{
MySqlDataReader reader = SharedDB.command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
results[counter] = reader.GetString(0);
counter++;
}
}
}
return(results);
}
public void checkIfUserIsRegisteredToEvent()
{
string[] events = getEventIDS();
string[] user_events = new string[events.Length];
int user_id = db.Users.Where(u => u.username == this.User.Identity.Name).FirstOrDefault().user_id;
SharedDB.setConnectionString();
SharedDB.command = new MySqlCommand("SELECT store_event_id FROM Store_Event_User WHERE user_id = " + user_id, SharedDB.connection);
SharedDB.connection.Open();
using (SharedDB.connection)
{
MySqlDataReader reader = SharedDB.command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
user_events[reader.GetInt32(0) - 1] = reader.GetString(0);
}
}
}
ViewData["user_events"] = user_events;
}
public void getAverageGameRatings()
{
string[] ids = getGameIDS();
Array.Sort(ids);
string[] ratingResults = new string[ids.Count()];
double averageRating = 0f;
SharedDB.setConnectionString();
int counter = 0;
int idCounter = 0;
foreach (string id in ids)
{
if (id == null)
{
continue;
}
else
{
using (SharedDB.connection)
{
SharedDB.connection.Open();
SharedDB.command = new MySqlCommand("SELECT rating FROM User_Game WHERE rating IS NOT NULL AND game_id = " + id, SharedDB.connection);
MySqlDataReader reader = SharedDB.command.ExecuteReader();
if (reader.HasRows)
{
int sum_of_ratings = 0;
int num_of_ratings = 0;
double[] ratings = new double[ids.Count()];
counter = 0;
while (reader.Read())
{
if (string.IsNullOrWhiteSpace(reader.GetString(0)))
{
continue;
}
sum_of_ratings += reader.GetInt32(0);
counter++;
}
num_of_ratings = counter;
averageRating = Math.Round((double)sum_of_ratings / (double)num_of_ratings, 1);
ratingResults[idCounter] = averageRating.ToString();
reader.Close();
}
else
{
ratingResults[idCounter] = "N/A";
}
}
}
idCounter++;
}
ViewData["AverageRatings"] = sanitizeArray(ratingResults);
}