public static int CountPendingComissionsWorkshop(string id)
{
SqlConnection SqlCon = GetSqlCon.GetCon();
int value;
SqlCommand cmd = new SqlCommand("CountPendingComissionsWorkshop", SqlCon);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@param1", id);
try
{
SqlCon.Open();
value = (int)cmd.ExecuteScalar();
SqlCon.Close();
}
catch (Exception)
{
Console.WriteLine("An error occurred when trying to count a workshop's pending commissions.");
return(-1);
}
return(value);
}
public static double GetAvgRating(string id)
{
double rating;
SqlConnection SqlCon = GetSqlCon.GetCon();
SqlCommand cmd = new SqlCommand("CalcAvgRating", SqlCon);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@param1", id);
try
{
SqlCon.Open();
rating = (double)cmd.ExecuteScalar();
SqlCon.Close();
}
catch (Exception)
{
Console.WriteLine("An error occurred when trying to get a workshop's rating.");
return(-1);
}
return(rating);
}
public static bool CheckAccount(string email)
{
SqlConnection SqlCon = GetSqlCon.GetCon();
bool value;
SqlCommand cmd = new SqlCommand("CheckVerifiedAccount", SqlCon);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@email", email);
try
{
SqlCon.Open();
value = (bool)cmd.ExecuteScalar();
SqlCon.Close();
}
catch
{
Console.WriteLine("An error occurred when trying verify the status of a user account.");
return(false);
}
if (value == true)
{
return(true);
}
return(false);
}
public static bool ActivateComission(int id)
{
SqlConnection SqlCon = GetSqlCon.GetCon();
SqlCommand cmd = new SqlCommand("ActivateComission", SqlCon);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@param1", id);
try
{
SqlCon.Open();
cmd.ExecuteNonQuery();
SqlCon.Close();
}
catch (Exception)
{
Console.WriteLine("An error occurred when trying to activate a commission.");
return(false);
}
return(true);
}
public static bool RegisterUserTypeClient(string username, string email, string telephone)
{
SqlConnection SqlCon = GetSqlCon.GetCon();
SqlCommand cmd = new SqlCommand("RegisterUserTypeClient", SqlCon);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@param1", username);
cmd.Parameters.AddWithValue("@param2", telephone);
cmd.Parameters.AddWithValue("@email", email);
try
{
SqlCon.Open();
cmd.ExecuteNonQuery();
SqlCon.Close();
}
catch (Exception)
{
Console.WriteLine("An error occurred when trying to add a user to the database.");
return(false);
}
return(true);
}
public static string GetWorkshopId(string nome)
{
string id;
SqlConnection SqlCon = GetSqlCon.GetCon();
SqlCommand cmd = new SqlCommand("GetWorkshopID", SqlCon);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@param1", nome);
try
{
SqlCon.Open();
id = (string)cmd.ExecuteScalar();
SqlCon.Close();
}
catch (Exception)
{
Console.WriteLine("An error occurred when trying to get a workshop's id.");
return("");
}
return(id);
}
public static bool RegisterUserTypeWorkshop(string email, string workshopname, string workshopphone, string workshopowner, string workshopownernif, string workshopaddress, string regiao)
{
SqlConnection SqlCon = GetSqlCon.GetCon();
SqlCommand cmd = new SqlCommand("RegisterUserTypeWorkshop", SqlCon);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@param1", workshopname);
cmd.Parameters.AddWithValue("@param2", workshopphone);
cmd.Parameters.AddWithValue("@param3", workshopowner);
cmd.Parameters.AddWithValue("@param4", workshopownernif);
cmd.Parameters.AddWithValue("@param5", 0);
cmd.Parameters.AddWithValue("@email", email);
cmd.Parameters.AddWithValue("@param6", workshopaddress);
cmd.Parameters.AddWithValue("@param7", regiao);
try
{
SqlCon.Open();
cmd.ExecuteNonQuery();
SqlCon.Close();
}
catch (Exception)
{
Console.WriteLine("An error occurred when trying to add a user to the database.");
return(false);
}
return(true);
}
public static int IsRejected(string id)
{
bool rejected;
int value;
SqlConnection SqlCon = GetSqlCon.GetCon();
SqlCommand cmd = new SqlCommand("CheckIfComissionRejected", SqlCon);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@param1", id);
try
{
SqlCon.Open();
rejected = (bool)cmd.ExecuteScalar();
SqlCon.Close();
}
catch (Exception)
{
Console.WriteLine("An error occurred when trying to check if the commission was rejected.");
return(-1);
}
value = Convert.ToInt32(rejected);
return(value);
}
public static bool CreateComissionGroup(string model, string type, string workshop, int year, string details, string userid, int groupno)
{
string workshopid = Users.GetWorkshopId(workshop);
SqlConnection SqlCon = GetSqlCon.GetCon();
DateTime now = DateTime.Now;
SqlCommand cmd = new SqlCommand("NewComissionGroup", SqlCon);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@param1", model);
cmd.Parameters.AddWithValue("@param2", type);
cmd.Parameters.AddWithValue("@param3", workshop);
cmd.Parameters.AddWithValue("@param4", year);
cmd.Parameters.AddWithValue("@param5", details);
cmd.Parameters.AddWithValue("@param6", workshopid);
cmd.Parameters.AddWithValue("@param7", userid);
cmd.Parameters.AddWithValue("@param8", now);
cmd.Parameters.AddWithValue("@param9", groupno);
try
{
SqlCon.Open();
cmd.ExecuteNonQuery();
SqlCon.Close();
}
catch (Exception)
{
Console.WriteLine("An error occurred when trying to create a commission.");
return(false);
}
return(true);
}
public static bool UpdateUserInfo(string id, string email, string emailconfirmed, string passwordhash, string securitystamp, string phonenumber,
string phonenumberconfirmed, string twofactorenabled, DateTime lockoutenddateutc, string lockoutenabled, string accessfailedcount, string username, string workshopname, string workshopphone,
string workshopowner, string workshopownernif, string workshopaddress, string workshopregion, string isenabled, string displayname)
{
SqlConnection SqlCon = GetSqlCon.GetCon();
SqlCommand cmd = new SqlCommand("UpdateUser", SqlCon);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@param1", id);
cmd.Parameters.AddWithValue("@param2", email);
cmd.Parameters.AddWithValue("@param3", emailconfirmed);
cmd.Parameters.AddWithValue("@param4", passwordhash);
cmd.Parameters.AddWithValue("@param5", securitystamp);
cmd.Parameters.AddWithValue("@param6", phonenumber);
cmd.Parameters.AddWithValue("@param7", phonenumberconfirmed);
cmd.Parameters.AddWithValue("@param8", twofactorenabled);
cmd.Parameters.AddWithValue("@param9", lockoutenddateutc);
cmd.Parameters.AddWithValue("@param10", lockoutenabled);
cmd.Parameters.AddWithValue("@param11", accessfailedcount);
cmd.Parameters.AddWithValue("@param12", username);
cmd.Parameters.AddWithValue("@param13", workshopname);
cmd.Parameters.AddWithValue("@param14", workshopphone);
cmd.Parameters.AddWithValue("@param15", workshopowner);
cmd.Parameters.AddWithValue("@param16", workshopownernif);
cmd.Parameters.AddWithValue("@param17", isenabled);
cmd.Parameters.AddWithValue("@param18", displayname);
cmd.Parameters.AddWithValue("@param19", workshopaddress);
cmd.Parameters.AddWithValue("@param20", workshopregion);
try
{
SqlCon.Open();
cmd.ExecuteNonQuery();
SqlCon.Close();
}
catch (Exception)
{
Console.WriteLine("An error occurred when trying to update a user's info.");
return(false);
}
return(true);
}
public static bool UpdateComission(string id, string ClientId, string WorkshopId, string Concluded, DateTime CreationDate, string BicycleModel, string BicycleType,
string YearOfAquisition, string Details, string Accepted, string ComissionNo, string Rating)
{
int id_num, year_num, comission_num, rating_num;
int.TryParse(id, out id_num); int.TryParse(YearOfAquisition, out year_num); int.TryParse(ComissionNo, out comission_num); int.TryParse(Rating, out rating_num);
SqlConnection SqlCon = GetSqlCon.GetCon();
SqlCommand cmd = new SqlCommand("UpdateComission", SqlCon);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@param1", id_num);
cmd.Parameters.AddWithValue("@param2", ClientId);
cmd.Parameters.AddWithValue("@param3", WorkshopId);
cmd.Parameters.AddWithValue("@param4", Concluded);
cmd.Parameters.AddWithValue("@param5", CreationDate);
cmd.Parameters.AddWithValue("@param6", BicycleModel);
cmd.Parameters.AddWithValue("@param7", BicycleType);
cmd.Parameters.AddWithValue("@param8", year_num);
cmd.Parameters.AddWithValue("@param9", Details);
cmd.Parameters.AddWithValue("@param10", Accepted);
cmd.Parameters.AddWithValue("@param11", comission_num);
cmd.Parameters.AddWithValue("@param12", rating_num);
try
{
SqlCon.Open();
cmd.ExecuteNonQuery();
SqlCon.Close();
}
catch (Exception)
{
Console.WriteLine("An error occurred when trying to update a commission.");
return(false);
}
return(true);
}
public static int AllAccsCount()
{
SqlConnection SqlCon = GetSqlCon.GetCon();
int value;
SqlCommand cmd = new SqlCommand("CountTotalTableEntries", SqlCon);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
try
{
SqlCon.Open();
value = (int)cmd.ExecuteScalar();
SqlCon.Close();
}
catch (Exception)
{
Console.WriteLine("An error occurred when trying to count all the accounts.");
return(-1);
}
return(value);
}
public static int CountComissions()
{
SqlConnection SqlCon = GetSqlCon.GetCon();
int value;
SqlCommand cmd = new SqlCommand("Count Comissions", SqlCon);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
try
{
SqlCon.Open();
value = (int)cmd.ExecuteScalar();
SqlCon.Close();
}
catch (Exception)
{
Console.WriteLine("An error occurred when trying to count how many commissions existed.");
return(-1);
}
return(value);
}
public static int GetToVerifyCount()
{
SqlConnection SqlCon = GetSqlCon.GetCon();
int value;
SqlCommand cmd = new SqlCommand("CountEntriesToVerify", SqlCon);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@var", 0);
try
{
SqlCon.Open();
value = (int)cmd.ExecuteScalar();
SqlCon.Close();
}
catch (Exception)
{
Console.WriteLine("An error occurred when trying to count the users not yet verified.");
return(-1);
}
return(value);
}
public static int CountWorkshopsInRegion(string region)
{
SqlConnection SqlCon = GetSqlCon.GetCon();
int value;
SqlCommand cmd = new SqlCommand("CountWorkshopsInRegion", SqlCon);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@param1", region);
try
{
SqlCon.Open();
value = (int)cmd.ExecuteScalar();
SqlCon.Close();
}
catch (Exception)
{
Console.WriteLine("An error occurred when trying to count how many workshops there were in the region");
return(-1);
}
return(value);
}
public static int CheckDuplicateNIF(string nif)
{
SqlConnection SqlCon = GetSqlCon.GetCon();
int value;
SqlCommand cmd = new SqlCommand("CheckDuplicateNIF", SqlCon);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@param1", nif);
try
{
SqlCon.Open();
value = (int)cmd.ExecuteScalar();
SqlCon.Close();
}
catch (Exception)
{
Console.WriteLine("An error occurred when trying to check if nif already existed in the database!");
return(-1);
}
return(value);
}
public static int CheckIfWorkshopExist(string workshopname)
{
SqlConnection SqlCon = GetSqlCon.GetCon();
int value;
SqlCommand cmd = new SqlCommand("CheckIfWorkshopExists", SqlCon);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@param1", workshopname);
try
{
SqlCon.Open();
value = (int)cmd.ExecuteScalar();
SqlCon.Close();
}
catch (Exception)
{
Console.WriteLine("An error occurred when trying to check if the workshop existed!");
return(-1);
}
return(value);
}
public static bool RemoverUserFromDB(string id)
{
SqlConnection SqlCon = GetSqlCon.GetCon();
SqlCommand cmd = new SqlCommand("DeleteUser", SqlCon);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@id", id);
try
{
SqlCon.Open();
cmd.ExecuteNonQuery();
SqlCon.Close();
}
catch (Exception)
{
Console.WriteLine("An error occurred when trying to remove a user from the database.");
return(false);
}
return(true);
}
public static bool LockAccount(string id)
{
SqlConnection SqlCon = GetSqlCon.GetCon();
SqlCommand cmd = new SqlCommand("LockAccount", SqlCon);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@param1", id);
try
{
SqlCon.Open();
cmd.ExecuteNonQuery();
SqlCon.Close();
}
catch (Exception)
{
Console.WriteLine("An error occurred when trying to lock a user account.");
return(false);
}
return(true);
}
public static int CheckIfGroupExists(int groupno)
{
SqlConnection SqlCon = GetSqlCon.GetCon();
int value;
SqlCommand cmd = new SqlCommand("CheckGroupNoDuplicate", SqlCon);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@param1", groupno);
try
{
SqlCon.Open();
value = (int)cmd.ExecuteScalar();
SqlCon.Close();
}
catch (Exception)
{
Console.WriteLine("An error occurred when trying to get the comission's price.");
return(-1);
}
return(value);
}
