public string insertEmployee(string fname, string lname, string email, string title, string gender, DateTime bdate, DateTime hdate, string address, string phone, MemoryStream picture)
{
SqlCommand com = new SqlCommand("AddEmployeeReturnIDwithoutput", mydb.getConnection);
com.CommandType = CommandType.StoredProcedure;
com.Parameters.Add("@fn", SqlDbType.NVarChar).Value = fname;
com.Parameters.Add("@ln", SqlDbType.NVarChar).Value = lname;
com.Parameters.Add("@eml", SqlDbType.NVarChar).Value = email;
com.Parameters.Add("@tle", SqlDbType.NVarChar).Value = title;
com.Parameters.Add("@gdr", SqlDbType.NVarChar).Value = gender;
com.Parameters.Add("@bdt", SqlDbType.DateTime).Value = bdate;
com.Parameters.Add("@hdt", SqlDbType.DateTime).Value = hdate;
com.Parameters.Add("@adrs", SqlDbType.NVarChar).Value = address;
com.Parameters.Add("@phn", SqlDbType.NVarChar).Value = phone;
com.Parameters.Add("@pic", SqlDbType.Image).Value = picture.ToArray();
com.Parameters.Add("@id", SqlDbType.Int).Direction = ParameterDirection.Output;
mydb.openConnection();
try
{
int cmd = com.ExecuteNonQuery();
string id = com.Parameters["@id"].Value.ToString();
mydb.closeConnection();
return(id);
}
catch (Exception ex)
{
throw ex;
}
finally
{
mydb.closeConnection();
}
}
public bool createAccount(string id, string username, string password)
{
SqlCommand com = new SqlCommand("INSERT INTO Accounts (username, password)" + $"VALUES (@usn, @psw) ", mydb.getConnection);
com.Parameters.Add("@usn", SqlDbType.NVarChar).Value = username;
com.Parameters.Add("@psw", SqlDbType.NVarChar).Value = password;
mydb.openConnection();
try
{
com.ExecuteNonQuery();
com.CommandText = "INSERT INTO EmployeeAccounts (emp_id, acc_id)" +
$"VALUES ((SELECT emp_id FROM Employees WHERE emp_id=@id), (SELECT acc_id FROM Accounts WHERE username=@usn))";
com.Connection = mydb.getConnection;
com.Parameters.Add("@id", SqlDbType.NVarChar).Value = id;
mydb.openConnection();
com.ExecuteNonQuery();
mydb.closeConnection();
return(true);
}
catch (Exception ex)
{
throw ex;
}
finally
{
mydb.closeConnection();
}
}
public int getNumOfPGuest()
{
SqlCommand com = new SqlCommand("Select Count(*) From PaymentGuest", mydb.getConnection);
mydb.openConnection();
int num = (int)com.ExecuteScalar();
mydb.closeConnection();
return(num);
}
public int numOfSchedule()
{
com.CommandText = "Select Count(distinct schedule_id) From dbo.Schedules";
com.Connection = mydb.getConnection;
mydb.openConnection();
int num = (int)com.ExecuteScalar();
mydb.closeConnection();
return(num);
}
public int numOfSetting()
{
com.CommandText = "Select Count(distinct set_id) From Shifts";
com.Connection = mydb.getConnection;
mydb.openConnection();
int num = (int)com.ExecuteScalar();
mydb.closeConnection();
return(num);
}
public bool updateAccount(int id, string user, string pass)
{
SqlCommand com = new SqlCommand("Update Accounts Set username=@usn, password=@psw Where acc_id = @id", mydb.getConnection);
com.Parameters.Add("@id", SqlDbType.Int).Value = id;
com.Parameters.Add("@usn", SqlDbType.NVarChar).Value = user;
com.Parameters.Add("@psw", SqlDbType.NVarChar).Value = pass;
mydb.openConnection();
if (com.ExecuteNonQuery() == 1)
{
com.Parameters.Clear();
mydb.closeConnection();
return(true);
}
else
{
com.Parameters.Clear();
mydb.closeConnection();
return(false);
}
}
public bool insertCheckOutData(int id, DateTime dname, string sname, TimeSpan tout, TimeSpan twork)
{
command.Parameters.Clear();
command.CommandText = "Update CheckInOut Set time_out = @time_out, time_worked = @twork Where emp_id=@eid and day=@dname and shift_name=@sname";
command.Parameters.Add("@eid", SqlDbType.Int).Value = id;
command.Parameters.Add("@dname", SqlDbType.Date).Value = dname;
command.Parameters.Add("@sname", SqlDbType.NVarChar).Value = sname;
command.Parameters.Add("@time_out", SqlDbType.Time).Value = tout;
command.Parameters.Add("@twork", SqlDbType.Time).Value = twork;
myDB.openConnection();
if (command.ExecuteNonQuery() == 1)
{
command.Parameters.Clear();
myDB.closeConnection();
return(true);
}
else
{
command.Parameters.Clear();
myDB.closeConnection();
return(false);
}
}
public bool createRes(int gid, int rid, DateTime cin, DateTime cout)
{
int res_id = getNumOfRes() + 1;
SqlCommand com = new SqlCommand("Insert into Reservation(res_id, guest_id, room_id, cin_date_book, cout_date_book) Values(@res_id, @gid, @rid, @cin, @cout)", mydb.getConnection);
com.Parameters.Add("@res_id", SqlDbType.Int).Value = res_id;
com.Parameters.Add("@gid", SqlDbType.Int).Value = gid;
com.Parameters.Add("@rid", SqlDbType.Int).Value = rid;
com.Parameters.Add("@cin", SqlDbType.Date).Value = cin.Date;
com.Parameters.Add("@cout", SqlDbType.Date).Value = cout.Date;
mydb.openConnection();
if (com.ExecuteNonQuery() == 1)
{
com.Parameters.Clear();
mydb.closeConnection();
return(true);
}
else
{
com.Parameters.Clear();
mydb.closeConnection();
return(false);
}
}
public bool insertRoom(int rtid, bool status)
{
com.CommandText = "Insert into Rooms(room_type_id, status_id)" + " Values (@rtid, @stt)";
com.Parameters.Add("@rtid", SqlDbType.Int).Value = rtid;
com.Parameters.Add("@stt", SqlDbType.Bit).Value = status;
com.Connection = mydb.getConnection;
mydb.openConnection();
if (com.ExecuteNonQuery() == 1)
{
com.Parameters.Clear();
mydb.closeConnection();
return(true);
}
else
{
com.Parameters.Clear();
mydb.closeConnection();
return(false);
}
}
public bool insertCheckinout(int id, DateTime date, string sta, int sht)
{
SqlCommand command = new SqlCommand("insert into check_in_out (emp_id, day_check, status_check, shift_id) values (@id, @date, @sta, @sht)", myDB.getConnection);
command.Parameters.Add("@id", SqlDbType.Int).Value = id;
command.Parameters.Add("@date", SqlDbType.DateTime).Value = date;
command.Parameters.Add("@sta", SqlDbType.NVarChar).Value = sta;
command.Parameters.Add("@sht", SqlDbType.Int).Value = sht;
myDB.openConnection();
if (command.ExecuteNonQuery() == 1)
{
myDB.closeConnection();
return(true);
}
else
{
myDB.closeConnection();
return(false);
}
}
