public HttpResponseMessage Update([FromBody] User user)
{
using (SQLiteConnection sqliteConnection = DBConnecter.DBConnect())
{
using (SQLiteCommand sqliteCommand = new SQLiteCommand("UPDATE Users SET Firstname = @firstname, Lastname = @lastname, Password = @password, Email = @email, Recycle = @recycle, Upcycle = @upcycle, Donate = @donate WHERE Username = @user", sqliteConnection))
{
user.Password = StrUtils.Hash(string.Format("{0}:{1}:{2}", user.Email, user.Password, configReader.GetString("Realm")));
sqliteCommand.Parameters.Add(new SQLiteParameter("@firstname", StrUtils.Sanitize(user.Firstname)));
sqliteCommand.Parameters.Add(new SQLiteParameter("@lastname", StrUtils.Sanitize(user.Lastname)));
sqliteCommand.Parameters.Add(new SQLiteParameter("@password", user.Password));
sqliteCommand.Parameters.Add(new SQLiteParameter("@email", user.Email));
sqliteCommand.Parameters.Add(new SQLiteParameter("@user", StrUtils.Sanitize(user.Username)));
sqliteCommand.Parameters.Add(new SQLiteParameter("@recycle", user.Recycle));
sqliteCommand.Parameters.Add(new SQLiteParameter("@upcycle", user.Upcycle));
sqliteCommand.Parameters.Add(new SQLiteParameter("@donate", user.Donate));
try
{
sqliteCommand.ExecuteNonQuery();
sqliteConnection.Close();
return(Request.CreateResponse(HttpStatusCode.OK, "User updated"));
}
catch (Exception ex)
{
sqliteConnection.Close();
return(Request.CreateResponse(HttpStatusCode.InternalServerError, "Internal Server Error: DB Insert fail - " + ex.ToString()));
}
}
}
}
public HttpResponseMessage Purchase([FromBody] PurchasedItem item)
{
using (SQLiteConnection sqliteConnection = DBConnecter.DBConnect())
{
using (SQLiteCommand sqliteInsertCommand = new SQLiteCommand("INSERT INTO ShopPurchases (UserEmail, ShopProductID, Quantity, Cost, DatePurchased) VALUES (@email, @productID, @quantity, @cost, @date)", sqliteConnection))
{
sqliteInsertCommand.Parameters.Add(new SQLiteParameter("@email", item.Email));
sqliteInsertCommand.Parameters.Add(new SQLiteParameter("@productID", item.ProductID));
sqliteInsertCommand.Parameters.Add(new SQLiteParameter("@quantity", item.Quantity));
sqliteInsertCommand.Parameters.Add(new SQLiteParameter("@cost", item.Price));
sqliteInsertCommand.Parameters.Add(new SQLiteParameter("@date", DateTime.Now));
try
{
sqliteInsertCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
sqliteConnection.Close();
return(Request.CreateResponse(HttpStatusCode.InternalServerError, "Internal Server Error: DB Insert fail - " + ex.ToString()));
}
}
using (SQLiteCommand sqliteUpdateCommand = new SQLiteCommand("UPDATE ShopItems SET Stock = (SELECT Stock FROM ShopItems WHERE ProductID = @productID) - @quantity WHERE ProductID = @productID", sqliteConnection))
{
sqliteUpdateCommand.Parameters.Add(new SQLiteParameter("@productID", item.ProductID));
sqliteUpdateCommand.Parameters.Add(new SQLiteParameter("@productID", item.ProductID));
sqliteUpdateCommand.Parameters.Add(new SQLiteParameter("@quantity", item.Quantity));
try
{
sqliteUpdateCommand.ExecuteNonQuery();
sqliteConnection.Close();
return(Request.CreateResponse(HttpStatusCode.OK, "Items purchased"));
}
catch (Exception ex)
{
sqliteConnection.Close();
return(Request.CreateResponse(HttpStatusCode.InternalServerError, "Internal Server Error: DB Update fail - " + ex.ToString()));
}
}
}
}
public static bool IsAuthorizedUser(string email, string password)
{
using (SQLiteConnection sqliteConnection = DBConnecter.DBConnect())
{
using (SQLiteCommand sqliteCommand = new SQLiteCommand("SELECT * FROM Users WHERE Email=@email", sqliteConnection))
{
sqliteCommand.Parameters.Add(new SQLiteParameter("@email", email));
try
{
using (SQLiteDataReader sqliteDataReader = sqliteCommand.ExecuteReader())
{
while (sqliteDataReader.Read())
{
string userPassword = (string)sqliteDataReader["Password"];
password = StrUtils.Hash(string.Format("{0}:{1}:{2}", email, password, configReader.GetString("Realm")));
if (password == userPassword)
{
sqliteDataReader.Close();
sqliteConnection.Close();
return(true);
}
else
{
sqliteDataReader.Close();
sqliteConnection.Close();
return(false);
}
}
sqliteConnection.Close();
}
}
catch (Exception ex)
{
sqliteConnection.Close();
return(false);
}
}
}
return(false);
}
public HttpResponseMessage GetCategoryItems([FromUri] string category)
{
using (SQLiteConnection sqliteConnection = DBConnecter.DBConnect())
{
using (SQLiteCommand sqliteCommand = new SQLiteCommand("SELECT * FROM ShopItems INNER JOIN ShopCategory on ShopItems.CategoryID = ShopCategory.ID WHERE ShopCategory.Name = @type", sqliteConnection))
{
sqliteCommand.Parameters.Add(new SQLiteParameter("@type", StrUtils.Sanitize(category)));
try
{
using (SQLiteDataReader sqliteDataReader = sqliteCommand.ExecuteReader())
{
List <ShopItem> shopItems = new List <ShopItem>();
while (sqliteDataReader.Read())
{
ShopItem item = new ShopItem()
{
ProductID = (string)sqliteDataReader["ProductID"],
Name = (string)sqliteDataReader["Name"],
Description = (string)sqliteDataReader["Description"],
Stock = Convert.ToInt16(sqliteDataReader["Stock"]),
CategoryID = Convert.ToInt16(sqliteDataReader["CategoryID"]),
Price = (double)sqliteDataReader["Price"],
ShopImg = (string)sqliteDataReader["ShopImg"]
};
shopItems.Add(item);
}
sqliteDataReader.Close();
sqliteConnection.Close();
return(Request.CreateResponse(HttpStatusCode.OK, shopItems));
}
}
catch (Exception ex)
{
sqliteConnection.Close();
return(Request.CreateResponse(HttpStatusCode.InternalServerError, "Internal Server Error: DB Selecet fail - " + ex.ToString()));
}
}
}
}
public HttpResponseMessage ShopList()
{
using (SQLiteConnection sqliteConnection = DBConnecter.DBConnect())
{
using (SQLiteCommand sqliteCommand = new SQLiteCommand("SELECT * FROM ShopItems", sqliteConnection))
{
try
{
using (SQLiteDataReader sqliteDataReader = sqliteCommand.ExecuteReader())
{
List <ShopItem> shopItems = new List <ShopItem>();
while (sqliteDataReader.Read())
{
ShopItem item = new ShopItem()
{
ProductID = (string)sqliteDataReader["ProductID"],
Name = (string)sqliteDataReader["Name"],
Description = (string)sqliteDataReader["Description"],
Stock = Convert.ToInt16(sqliteDataReader["Stock"]),
CategoryID = Convert.ToInt16(sqliteDataReader["CategoryID"]),
Price = (double)sqliteDataReader["Price"],
ShopImg = (string)sqliteDataReader["ShopImg"],
Size = (string)sqliteDataReader["Size"]
};
shopItems.Add(item);
}
sqliteDataReader.Close();
sqliteConnection.Close();
return(Request.CreateResponse(HttpStatusCode.OK, shopItems));
}
}
catch (Exception ex)
{
sqliteConnection.Close();
return(Request.CreateResponse(HttpStatusCode.InternalServerError, "Internal Server Error: DB Selecet fail - " + ex.ToString()));
}
}
}
}
public HttpResponseMessage Login([FromBody] User user)
{
using (SQLiteConnection sqliteConnection = DBConnecter.DBConnect())
{
using (SQLiteCommand sqliteCommand = new SQLiteCommand("SELECT * FROM Users WHERE Email=@email", sqliteConnection))
{
sqliteCommand.Parameters.Add(new SQLiteParameter("@email", user.Email));
try
{
using (SQLiteDataReader sqliteDataReader = sqliteCommand.ExecuteReader())
{
while (sqliteDataReader.Read())
{
user.Password = "";
user.Firstname = (string)sqliteDataReader["Firstname"];
user.Lastname = (string)sqliteDataReader["Lastname"];
user.Username = (string)sqliteDataReader["Username"];
user.Recycle = Convert.ToInt16(sqliteDataReader["Recycle"]);
user.Upcycle = Convert.ToInt16(sqliteDataReader["Upcycle"]);
user.Donate = Convert.ToInt16(sqliteDataReader["Donate"]);
sqliteDataReader.Close();
sqliteConnection.Close();
return(Request.CreateResponse(HttpStatusCode.OK, user));
}
sqliteDataReader.Close();
sqliteConnection.Close();
}
}
catch (Exception ex)
{
sqliteConnection.Close();
return(Request.CreateResponse(HttpStatusCode.InternalServerError, "Internal Server Error: DB Select fail - " + ex.ToString()));
}
}
}
return(Request.CreateResponse(HttpStatusCode.InternalServerError, "Internal Server Error: Execution fail"));
}
public HttpResponseMessage Kiosks()
{
using (SQLiteConnection sqliteConnection = DBConnecter.DBConnect())
{
using (SQLiteCommand sqliteCommand = new SQLiteCommand("SELECT * FROM Kiosk", sqliteConnection))
{
try
{
using (SQLiteDataReader sqliteDataReader = sqliteCommand.ExecuteReader())
{
List <Kiosk> kiosks = new List <Kiosk>();
while (sqliteDataReader.Read())
{
Kiosk kiosk = new Kiosk()
{
Name = (string)sqliteDataReader["Name"],
Longitude = (double)sqliteDataReader["Longitude"],
Latitude = (double)sqliteDataReader["Latitude"],
Address = (string)sqliteDataReader["Address"],
KioskType = (string)sqliteDataReader["Type"]
};
kiosks.Add(kiosk);
}
sqliteDataReader.Close();
sqliteConnection.Close();
return(Request.CreateResponse(HttpStatusCode.OK, kiosks));
}
}
catch (Exception ex)
{
sqliteConnection.Close();
return(Request.CreateResponse(HttpStatusCode.InternalServerError, "Internal Server Error: DB Select fail - " + ex.ToString()));
}
}
}
}
public HttpResponseMessage Register([FromBody] User user)
{
Console.WriteLine("Recieved Details: {0} {1} {2} {3} {4} {5}", user.Username, user.Firstname, user.Lastname, user.Password, user.Email, user.Recycle);
using (SQLiteConnection sqliteConnection = DBConnecter.DBConnect())
{
user.Username = StrUtils.Sanitize(user.Username);
user.Firstname = StrUtils.Sanitize(user.Firstname);
user.Lastname = StrUtils.Sanitize(user.Lastname);
using (SQLiteCommand sqliteSelectCommand = new SQLiteCommand("SELECT * FROM Users WHERE Username=@user OR Email=@email", sqliteConnection))
{
sqliteSelectCommand.Parameters.Add(new SQLiteParameter("@user", user.Username));
sqliteSelectCommand.Parameters.Add(new SQLiteParameter("@email", user.Email));
try
{
using (SQLiteDataReader sqliteDataReader = sqliteSelectCommand.ExecuteReader())
{
while (sqliteDataReader.Read())
{
string errorMsg = "";
if (user.Username == (string)sqliteDataReader["Username"])
{
errorMsg = "Username not avaliable";
}
else if (user.Email == (string)sqliteDataReader["Email"])
{
errorMsg = "Email not avaliable";
}
sqliteDataReader.Close();
if (errorMsg != "")
{
sqliteConnection.Close();
return(Request.CreateResponse(HttpStatusCode.BadRequest, errorMsg));
}
}
sqliteDataReader.Close();
}
}
catch
{
}
using (SQLiteCommand sqliteInsertCommand = new SQLiteCommand("INSERT INTO Users(Username, Firstname, Lastname, Password, Email, Recycle, Upcycle, Donate) VALUES (@user, @firstname, @lastname, @password, @email, @recycle, @upcycle, @donate);", sqliteConnection))
{
user.Password = StrUtils.Hash(string.Format("{0}:{1}:{2}", user.Email, user.Password, configReader.GetString("Realm")));
sqliteInsertCommand.Parameters.Add(new SQLiteParameter("@user", user.Username));
sqliteInsertCommand.Parameters.Add(new SQLiteParameter("@firstname", user.Firstname));
sqliteInsertCommand.Parameters.Add(new SQLiteParameter("@lastname", user.Lastname));
sqliteInsertCommand.Parameters.Add(new SQLiteParameter("@password", user.Password));
sqliteInsertCommand.Parameters.Add(new SQLiteParameter("@email", user.Email));
sqliteInsertCommand.Parameters.Add(new SQLiteParameter("@recycle", user.Recycle));
sqliteInsertCommand.Parameters.Add(new SQLiteParameter("@upcycle", user.Upcycle));
sqliteInsertCommand.Parameters.Add(new SQLiteParameter("@donate", user.Donate));
try
{
sqliteInsertCommand.ExecuteNonQuery();
sqliteConnection.Close();
return(Request.CreateResponse(HttpStatusCode.OK, "User Registered"));
}
catch (Exception ex)
{
sqliteConnection.Close();
return(Request.CreateResponse(HttpStatusCode.InternalServerError, "Internal Server Error: DB Insert fail - " + ex.ToString()));
}
}
}
}
}
public HttpResponseMessage Search([FromBody] Kiosk location)
{
using (SQLiteConnection sqliteConnection = DBConnecter.DBConnect())
{
using (SQLiteCommand sqliteCommand = new SQLiteCommand("SELECT * FROM Kiosk", sqliteConnection))
{
try
{
using (SQLiteDataReader sqliteDataReader = sqliteCommand.ExecuteReader())
{
List <Kiosk> kiosks = new List <Kiosk>();
while (sqliteDataReader.Read())
{
Kiosk kiosk = new Kiosk()
{
Name = (string)sqliteDataReader["Name"],
Longitude = (double)sqliteDataReader["Longitude"],
Latitude = (double)sqliteDataReader["Latitude"],
Address = (string)sqliteDataReader["Address"],
KioskType = (string)sqliteDataReader["Type"]
};
kiosks.Add(kiosk);
}
sqliteDataReader.Close();
sqliteConnection.Close();
List <Kiosk> closeKiosks = kiosks.FindAll(k =>
{
Console.WriteLine(k.Longitude);
Console.WriteLine(k.Latitude);
var sCoord = new GeoCoordinate(location.Latitude, k.Longitude);
var eCoord = new GeoCoordinate(k.Latitude, location.Longitude);
k.Distance = sCoord.GetDistanceTo(eCoord) / 1000.0;
Console.WriteLine(k.Distance);
if (k.Distance <= location.Distance)
{
return(true);
}
else
{
return(false);
}
});
if (closeKiosks.Count == 0)
{
return(Request.CreateResponse(HttpStatusCode.OK, "No Kiosks within set distance"));
}
else
{
return(Request.CreateResponse(HttpStatusCode.OK, closeKiosks));
}
}
}
catch (Exception ex)
{
sqliteConnection.Close();
return(Request.CreateResponse(HttpStatusCode.InternalServerError, "Internal Server Error: DB Insert fail - " + ex.ToString()));
}
}
}
}