public void DeleteAccidents(int AccidentID)
{
Transaction trans;
SqlCommand cmd;
trans = new Transaction();
trans.BegindTransactions();
try
{
cmd = new SqlCommand("DeleteAccident", trans.getcon(), trans.GetTransaction());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@AccidentID", AccidentID));
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
trans.RollBack();
throw e;
}
trans.Commit();
}
public List<Message> GetMessage(int UserID)
{
trans = new Transaction();
trans.BegindTransactions();
try
{
cmd = new SqlCommand("GetMessage", trans.getcon(), trans.GetTransaction());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@UserID", UserID));
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.HasRows && rdr.Read())
{
messageList = new List<Message>();
Message A = new Message(
rdr["Message"].ToString(),
DateTime.Parse(rdr["sendDate"].ToString()),
rdr["Tittle"].ToString(),
new Costumer(rdr["Name"].ToString()));
messageList.Add(A);
}
}
catch (Exception e)
{
trans.RollBack();
throw e;
}
trans.getcon().Close();
return messageList;
}
public int getUserIDFromUsername(string username)
{
int userID = 0;
trans = new Transaction();
trans.BegindTransactions();
try
{
cmd = new SqlCommand("getIDfromUsername", trans.getcon(), trans.GetTransaction());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@Username", username));
SqlDataReader dbr;
dbr = cmd.ExecuteReader();
while (dbr.Read() == true)
{
if (dbr.HasRows)
{
userID = (int)dbr["UserID"];
}
}
dbr.Close();
}
catch (Exception e)
{
trans.RollBack();
throw e;
// trans.getcon().Close();
}
trans.Commit();
trans.getcon().Close();
return userID;
}
public List<Day> getAccidentDaysFromDB(DateTime selectStart, DateTime selectEnd, int DeviceNr)
{
List<Day> dayAccidents = new List<Day>();
trans = new Transaction();
trans.BegindTransactions();
try
{
cmd = new SqlCommand("nrOfAccident", trans.getcon(), trans.GetTransaction());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@DeviceID", DeviceNr));
cmd.Parameters.Add(new SqlParameter("@StartDate", selectStart));
cmd.Parameters.Add(new SqlParameter("@EndDate", selectEnd));
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
// Device D = new Device(int.Parse(rdr["DeviceNR"].ToString()), User);
Day A = new Day(DateTime.Parse((rdr["TimeOfAccident"]).ToString()), int.Parse(rdr["NrOfAccident"].ToString()),int.Parse(rdr["Average"].ToString()));
dayAccidents.Add(A);
}
rdr.Close();
}
catch (Exception e)
{
//trans.RollBack();
throw e;
//trans.getcon().Close();
}
//trans.Commit();
trans.getcon().Close();
return dayAccidents;
}
public void RegisterAccident( int amount, DateTime time, int Drinks, DateTime TimeSleep, DateTime TimeToilet, int ToiletVisit )
{
Transaction trans;
SqlCommand cmd;
trans = new Transaction();
trans.BegindTransactions();
int i = 101;
try
{
cmd = new SqlCommand("RegisterBWAcc", trans.getcon(), trans.GetTransaction());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@Amount", amount));
cmd.Parameters.Add(new SqlParameter("@TimeOfAccident", time));
cmd.Parameters.Add(new SqlParameter("@DeviceID", DeviceNumber));
cmd.Parameters.Add(new SqlParameter("@Drinks", Drinks));
cmd.Parameters.Add(new SqlParameter("@TimeSleep", TimeSleep));
cmd.Parameters.Add(new SqlParameter("@TimeToilet", TimeToilet));
cmd.Parameters.Add(new SqlParameter("@Toilet", ToiletVisit));
//cmd.Parameters.Add(new SqlParameter("@Version", (Version + 1)));
i = cmd.ExecuteNonQuery();
trans.Commit();
}
catch (Exception e)
{
trans.RollBack();
throw e;
}
//trans.Commit();
}
public void SendMessage(string Messagtxt, DateTime senddate, string Tittle, int UserID)
{
Transaction trans;
SqlCommand cmd;
trans = new Transaction();
trans.BegindTransactions();
try
{
cmd = new SqlCommand("SendMessage", trans.getcon(), trans.GetTransaction());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@Message", Messagtxt));
cmd.Parameters.Add(new SqlParameter("@sendDate", senddate));
cmd.Parameters.Add(new SqlParameter("@Tittle", Tittle));
cmd.Parameters.Add(new SqlParameter("@UserID", UserID));
cmd.ExecuteNonQuery();
trans.Commit();
}
catch (Exception e)
{
trans.RollBack();
throw e;
}
}
public void UpdateAccident(int accID, DateTime time, int amount, int DeviceID)
{
Transaction trans;
SqlCommand cmd;
int versionCheck = getVersion(accID);
trans = new Transaction();
trans.BegindTransactions();
try
{
cmd = new SqlCommand("UpdateAccident", trans.getcon(), trans.GetTransaction());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@AccidentID", accID));
cmd.Parameters.Add(new SqlParameter("@Amount", amount));
cmd.Parameters.Add(new SqlParameter("@TimeOfAccident", time));
cmd.Parameters.Add(new SqlParameter("@DeviceID", DeviceID));
cmd.Parameters.Add(new SqlParameter("@versionCheck", versionCheck));
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
trans.RollBack();
throw e;
}
trans.Commit();
}
public void RegisterDevice(int userID)
{
Transaction trans = new Transaction();
SqlCommand cmd;
trans = new Transaction();
trans.BegindTransactions();
try
{
cmd = new SqlCommand("RegisterDevice", trans.getcon(), trans.GetTransaction());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@UserID", userID));
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
trans.RollBack();
throw e;
}
trans.Commit();
}
public List<Accident> ReadAccidents(int UserID)
{
trans = new Transaction();
trans.BegindTransactions();
try
{
cmd = new SqlCommand("ReadAccidents", trans.getcon(), trans.GetTransaction());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@UserID", UserID));
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.HasRows && rdr.Read())
{
Accident A = new Accident(int.Parse(rdr["AccidentID"].ToString()), int.Parse(rdr["Amount"].ToString()), DateTime.Parse(rdr["TimeOfAccident"].ToString()), new Device(int.Parse(rdr["DeviceID"].ToString()), User));
A.Version = int.Parse(rdr["Version"].ToString());
tempList.Add(A);
}
}
catch (Exception e)
{
trans.RollBack();
throw e;
}
trans.getcon().Close();
return tempList;
}
public int getVersion(int accID)
{
trans = new Transaction();
trans.BegindTransactions();
SqlDataReader dbr;
int version = 0;
try
{
cmd = new SqlCommand("getVersion", trans.getcon(), trans.GetTransaction());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@AccidentID", accID));
cmd.ExecuteNonQuery();
dbr = cmd.ExecuteReader();
while (dbr.Read() == true)
{
if (dbr.HasRows)
{
version = (int)dbr["Version"];
}
}
dbr.Close();
}
catch (Exception)
{
throw;
}
trans.getcon().Close();
return version;
}
public List<Accident> getListByDateFromDB(DateTime Date, int DeviceNr)
{
List<Accident> dayAccidents = new List<Accident>();
DateTime newDate = DateTime.Parse(Date.ToShortDateString());
trans = new Transaction();
trans.BegindTransactions();
try
{
cmd = new SqlCommand("getInfoFromDate" , trans.getcon(), trans.GetTransaction());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@Date", newDate));
cmd.Parameters.Add(new SqlParameter("@DeviceNr", DeviceNr));
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.HasRows && rdr.Read())
{
// Device D = new Device(int.Parse(rdr["DeviceNR"].ToString()), User);
Accident acc = new Accident(int.Parse((rdr["AccidentID"]).ToString()), int.Parse((rdr["Amount"]).ToString()), DateTime.Parse((rdr["TimeOfAccident"]).ToString()),int.Parse((rdr["DeviceID"]).ToString()), int.Parse((rdr["Drinks"]).ToString()), DateTime.Parse((rdr["TimeSleep"]).ToString()), DateTime.Parse((rdr["TimeToilet"]).ToString()), int.Parse((rdr["Toilet"]).ToString()), int.Parse((rdr["Version"]).ToString()));
dayAccidents.Add(acc);
}
}
catch (Exception e)
{
trans.RollBack();
throw e;
// trans.getcon().Close();
}
//trans.Commit();
trans.getcon().Close();
return dayAccidents;
}
public void SignUp(string type, string birthday, string UserName, string PassWord, string Name, int deviceID)
{
Transaction trans;
SqlCommand cmd;
trans = new Transaction();
trans.BegindTransactions();
try
{
cmd = new SqlCommand("NewSignUp", trans.getcon(), trans.GetTransaction());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@type", type));
cmd.Parameters.Add(new SqlParameter("@birthday", birthday));
cmd.Parameters.Add(new SqlParameter("@UserName", UserName));
cmd.Parameters.Add(new SqlParameter("@PassWord", PassWord));
cmd.Parameters.Add(new SqlParameter("@Name", Name));
cmd.Parameters.Add(new SqlParameter("@deviceID", deviceID));
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
trans.RollBack();
throw e;
}
trans.Commit();
}
public int login(string Username, string Password)
{
int Perm = 3;
Transaction trans;
SqlCommand cmd;
trans = new Transaction();
trans.BegindTransactions();
String query = "Select UserName ,PassWord ,TypeOfUser from LogIn1 where UserName = '******'and PassWord = '******'";
cmd = new SqlCommand(query, trans.getcon(), trans.GetTransaction());
SqlDataReader dbr;
dbr = cmd.ExecuteReader();
while (dbr.Read() == true)
{
if (dbr.HasRows)
{
Perm = (int)dbr["TypeOfUser"];
}
}
dbr.Close();
trans.Commit();
trans.GetConnection().Close();
return Perm;
}