public ActionResult employee_delete(Int32 emp_id)
{
using (jugglecontext dbcon = new jugglecontext())
{
if (Session["User_Id"] != null)
{
// when employee delete at that time his/her assigned appointment will be upassign
SqlConnection connection = new SqlConnection(dbcon.connectionString());
var command = new SqlCommand("[updatedata]", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@emp_id", emp_id);
command.Parameters.AddWithValue("@startdate", 0);
command.Parameters.AddWithValue("@enddate", 0);
command.Parameters.AddWithValue("@StartTime", 0);
command.Parameters.AddWithValue("@endTime", 0);
command.Parameters.AddWithValue("@user_id", Convert.ToInt32(Session["User_Id"]));
command.Parameters.AddWithValue("@attribute_id", 0);
command.Parameters.AddWithValue("@client_id", 0);
command.Parameters.AddWithValue("@appointment_id", 0);
command.Parameters.AddWithValue("@StatementType", "emp_data");
connection.Open();
SqlDataReader rdr = command.ExecuteReader();
tbl_employee_info objEmpl = dbcon.tbl_employee_info.Find(emp_id);
dbcon.tbl_employee_info.Remove(objEmpl);
dbcon.SaveChanges();
}
return(RedirectToAction("Employee", "Employee"));
}
}
public ActionResult Employeeavailibility_Edit(tbl_employee_availability employee)
{
if (Session["User_Id"] != null)
{
using (jugglecontext dbcon = new jugglecontext())
{
try
{
tbl_employee_availability empl = new tbl_employee_availability();
empl.emp_id = employee.emp_id;
empl.start_date = Convert.ToDateTime(employee.start_date);
empl.end_date = Convert.ToDateTime(employee.end_date);
empl.userid = Convert.ToInt32(Session["User_Id"]);
empl.time_range_start = employee.time_range_start;
empl.time_range_end = employee.time_range_end;
empl.created_date = employee.created_date;
empl.updated_date = DateTime.Now;
empl.empavailability_id = Convert.ToInt32(employee.empavailability_id);
dbcon.Entry(empl).State = System.Data.Entity.EntityState.Modified;
dbcon.SaveChanges();
// when employee delete at that time his/her assigned appointment will be upassign
SqlConnection connection = new SqlConnection(dbcon.connectionString());
var command = new SqlCommand("[updateempavailibility]", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@startdate", Convert.ToDateTime(employee.start_date));
command.Parameters.AddWithValue("@enddate", Convert.ToDateTime(employee.end_date));
command.Parameters.AddWithValue("@StartTime", employee.time_range_start);
command.Parameters.AddWithValue("@endTime", employee.time_range_end);
command.Parameters.AddWithValue("@emp_id", Convert.ToInt32(employee.emp_id));
command.Parameters.AddWithValue("@type", "edit");
connection.Open();
SqlDataReader rdr = command.ExecuteReader();
return(RedirectToAction("EmployeeAvailability", "EmployeeAvailability"));
}
catch (DbEntityValidationException ex)
{
foreach (var entityValidationErrors in ex.EntityValidationErrors)
{
foreach (var validationError in entityValidationErrors.ValidationErrors)
{
Response.Write("Property: " + validationError.PropertyName + " Error: " + validationError.ErrorMessage);
}
}
}
}
}
else
{
return(RedirectToAction("Login", "Account"));
}
return(View());
}
public ActionResult Worktype_delete(Int32 work_id)
{
if (Session["User_Id"] != null)
{
try
{
using (jugglecontext dbcon = new jugglecontext())
{
if (Session["User_Id"] != null)
{
string workid = work_id.ToString();
var employee_detail = (from a in dbcon.tbl_employee_info
where a.emp_qualifiedservicetypes.Contains(workid)
select a).ToList();
for (int i = 0; i < employee_detail.Count; i++)
{
string emp_worktype = employee_detail[i].emp_qualifiedservicetypes.ToString();
emp_worktype = emp_worktype.Replace(workid, "");
SqlConnection connection = new SqlConnection(dbcon.connectionString());
var command = new SqlCommand("[updatedata]", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@client_id", 0);
command.Parameters.AddWithValue("@attribute_id", emp_worktype);
command.Parameters.AddWithValue("@appointment_id", 0);
command.Parameters.AddWithValue("@StatementType", "worktype_data");
command.Parameters.AddWithValue("@emp_id", Convert.ToInt32(employee_detail[i].emp_id));
command.Parameters.AddWithValue("@startdate", 0);
command.Parameters.AddWithValue("@enddate", 0);
command.Parameters.AddWithValue("@StartTime", 0);
command.Parameters.AddWithValue("@endTime", 0);
command.Parameters.AddWithValue("@user_id", Convert.ToInt32(Session["User_Id"]));
connection.Open();
SqlDataReader rdr = command.ExecuteReader();
}
tbl_worktype objEmp = dbcon.tbl_worktype.Find(work_id);
dbcon.tbl_worktype.Remove(objEmp);
dbcon.SaveChanges();
}
return(RedirectToAction("Work_type", "Work_type"));
}
}
catch
{
return(View());
}
}
else
{
return(View("Login", "Account"));
}
}
public JsonResult doesemployeeavailability(int emp_id, int empavailability_id = 0, string time_range_start = null, string time_range_end = null, string start_date = null, string end_date = null, string day = null, Boolean recurring = false)
{
try
{
jugglecontext dbcon = new jugglecontext();
DateTime start_Date_check = Convert.ToDateTime(start_date);
if (string.IsNullOrEmpty(end_date))
{
end_date = start_date;
}
int userid = Convert.ToInt32(Session["User_Id"]);
DateTime end_Date_check = Convert.ToDateTime(end_date);
TimeSpan time_range_start_timespan = TimeSpan.Parse(time_range_start);
TimeSpan time_range_end_timespan = TimeSpan.Parse(time_range_end);
DateTime time_range_start_timespandate = Convert.ToDateTime(start_date + " " + time_range_start);
DateTime time_range_end_timespandate = Convert.ToDateTime(end_date + " " + time_range_end);
var id = (from a in dbcon.tbl_employee_availability
where a.emp_id == emp_id && a.empavailability_id != empavailability_id
select a).ToList();
for (int i = 0; i < id.Count; i++)
{
SqlConnection connection = new SqlConnection(dbcon.connectionString());
var command = new SqlCommand("compare_client_Availibility", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@startdate", Convert.ToDateTime(start_date));
command.Parameters.AddWithValue("@enddate", Convert.ToDateTime(end_date));
command.Parameters.AddWithValue("@StartTime", time_range_start.ToString());
command.Parameters.AddWithValue("@endTime", time_range_end.ToString());
command.Parameters.AddWithValue("@emp_id", Convert.ToInt32(emp_id));
command.Parameters.AddWithValue("@empavailability_id", Convert.ToInt32(empavailability_id));
command.Parameters.AddWithValue("@userid", userid);
connection.Open();
SqlDataReader rdr = command.ExecuteReader();
while (rdr.Read())
{
connection.Close();
return(Json(string.Format("This Employee has already availability."), JsonRequestBehavior.AllowGet));
}
}
return(Json(true, JsonRequestBehavior.AllowGet));
}
catch (Exception ex)
{
Logfile.WriteCDNLog(ex.ToString(), "doesemployeeavailability");
return(Json("", JsonRequestBehavior.AllowGet));
}
}
public ActionResult attribute_delete(Int32 atrribute_id)
{
try
{
using (jugglecontext dbcon = new jugglecontext())
{
if (Session["User_Id"] != null)
{
string atrributeid = atrribute_id.ToString();
// when delete attribute then remove that attribute from the appointment, client,employee
var client_detail = (from a in dbcon.tbl_client
where a.attribute_id.Contains(atrributeid)
select a).ToList();
var employee_detail = (from a in dbcon.tbl_employee_info
where a.attribute_id.Contains(atrributeid)
select a).ToList();
var appointment_detail = (from a in dbcon.tbl_appointment
where a.attribute_id.Contains(atrributeid)
select a).ToList();
for (int i = 0; i < client_detail.Count; i++)
{
string client_attri = client_detail[i].attribute_id.ToString();
client_attri = client_attri.Replace(atrributeid, "");
SqlConnection connection = new SqlConnection(dbcon.connectionString());
var command = new SqlCommand("[updatedata]", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@client_id", Convert.ToInt32(client_detail[i].client_id));
command.Parameters.AddWithValue("@attribute_id", client_attri);
command.Parameters.AddWithValue("@appointment_id", 0);
command.Parameters.AddWithValue("@StatementType", "attribute_data");
command.Parameters.AddWithValue("@emp_id", 0);
command.Parameters.AddWithValue("@startdate", 0);
command.Parameters.AddWithValue("@enddate", 0);
command.Parameters.AddWithValue("@StartTime", 0);
command.Parameters.AddWithValue("@endTime", 0);
command.Parameters.AddWithValue("@user_id", Convert.ToInt32(Session["User_Id"]));
connection.Open();
SqlDataReader rdr = command.ExecuteReader();
}
for (int i = 0; i < employee_detail.Count; i++)
{
string emp_attri = employee_detail[i].attribute_id.ToString();
emp_attri = emp_attri.Replace(atrributeid, "");
SqlConnection connection = new SqlConnection(dbcon.connectionString());
var command = new SqlCommand("[updatedata]", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@client_id", 0);
command.Parameters.AddWithValue("@attribute_id", emp_attri);
command.Parameters.AddWithValue("@StatementType", "attribute_data");
command.Parameters.AddWithValue("@appointment_id", 0);
command.Parameters.AddWithValue("@emp_id", employee_detail[i].emp_id);
command.Parameters.AddWithValue("@startdate", 0);
command.Parameters.AddWithValue("@enddate", 0);
command.Parameters.AddWithValue("@StartTime", 0);
command.Parameters.AddWithValue("@endTime", 0);
command.Parameters.AddWithValue("@user_id", Convert.ToInt32(Session["User_Id"]));
connection.Open();
SqlDataReader rdr = command.ExecuteReader();
}
for (int i = 0; i < appointment_detail.Count; i++)
{
string appointment_attri = appointment_detail[i].attribute_id.ToString();
appointment_attri = appointment_attri.Replace(atrributeid, "");
SqlConnection connection = new SqlConnection(dbcon.connectionString());
var command = new SqlCommand("[updatedata]", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@client_id", 0);
command.Parameters.AddWithValue("@attribute_id", appointment_attri);
command.Parameters.AddWithValue("@StatementType", "attribute_data");
command.Parameters.AddWithValue("@appointment_id", appointment_detail[i].appointment_id);
command.Parameters.AddWithValue("@emp_id", 0);
command.Parameters.AddWithValue("@startdate", 0);
command.Parameters.AddWithValue("@enddate", 0);
command.Parameters.AddWithValue("@StartTime", 0);
command.Parameters.AddWithValue("@endTime", 0);
command.Parameters.AddWithValue("@user_id", Convert.ToInt32(Session["User_Id"]));
connection.Open();
SqlDataReader rdr = command.ExecuteReader();
}
tbl_attribute_data objEmp = dbcon.tbl_attribute_data.Find(atrribute_id);
dbcon.tbl_attribute_data.Remove(objEmp);
dbcon.SaveChanges();
}
return(RedirectToAction("attribute", "Attribute"));
}
}
catch
{
return(View());
}
}
public ActionResult appointment_edit(juggle.Models.tbl_appointment appointment)
{
if (Session["User_Id"] == null)
{
return(RedirectToAction("Appointment", "Appointment"));
}
if (Session["User_Id"] != null)
{
try
{
using (jugglecontext dbcon = new jugglecontext())
{
tbl_appointment editappointment = new tbl_appointment();
string multipleattribute = Request.Form["attribute_iddata"].ToString();
editappointment.appointment_id = appointment.appointment_id;
editappointment.description = appointment.description;
editappointment.recurring = appointment.recurring;
editappointment.start_date = appointment.start_date;
editappointment.end_date = appointment.end_date;
editappointment.time = appointment.time;
string multiplevalue = Request.Form["multiple"];
if (appointment.recurring)
{
//appointment.end_date = null;
}
if (!string.IsNullOrEmpty(multiplevalue))
{
editappointment.day = multiplevalue;
}
else
{
multiplevalue = "0"; editappointment.day = "0";
}
editappointment.updated_date = DateTime.Now;
if (!string.IsNullOrEmpty(appointment.notes))
{
editappointment.notes = appointment.notes;
}
else
{
editappointment.notes = "N/A";
}
// editappointment.notes = appointment.notes;
editappointment.time_range_start = appointment.time_range_start;
editappointment.time_range_end = appointment.time_range_end;
// add two extra field that store the calendar start time and end time
// it is use for the event of google calendar only
// start
string startdate1 = appointment.start_date.ToString();
DateTime strstartdate1 = Convert.ToDateTime(startdate1);
string enddate1 = appointment.end_date.ToString();
DateTime strenddate1 = Convert.ToDateTime(enddate1);
DateTime cal_startdate = Convert.ToDateTime(strstartdate1.ToString("yyyy/MM/dd") + " " + appointment.time_range_start);
DateTime cal_enddate = Convert.ToDateTime(strenddate1.ToString("yyyy/MM/dd") + " " + appointment.time_range_end);
editappointment.Calendar_Start_datetime = DateTimeOffset.Parse(cal_startdate.ToString()).UtcDateTime;
editappointment.Calendar_End_datetime = DateTimeOffset.Parse(cal_enddate.ToString()).UtcDateTime;
string timezone = "West Asia Standard Time";
TimeZoneInfo targetZone = TimeZoneInfo.FindSystemTimeZoneById(timezone);
cal_startdate = TimeZoneInfo.ConvertTimeFromUtc(cal_startdate, targetZone);
cal_enddate = TimeZoneInfo.ConvertTimeFromUtc(cal_enddate, targetZone);
editappointment.Calendar_Start_datetime = cal_startdate;
editappointment.Calendar_End_datetime = cal_enddate;
// end
// get time into total minutes
string startingtime = appointment.time_range_start.ToString();
string endindtime = appointment.time_range_end.ToString();
TimeSpan starttime = TimeSpan.Parse(startingtime);
TimeSpan endatime = TimeSpan.Parse(endindtime);
TimeSpan Difflength = endatime.Subtract(starttime); // difference between start time and end time
int total = (Difflength.Hours * 60) + Difflength.Minutes; // convert difference in minutes
editappointment.length = total;
editappointment.client_id = Convert.ToInt32(appointment.client_id);
editappointment.attribute_id = multipleattribute.ToString();
editappointment.emp_id = Convert.ToInt32(appointment.emp_id);
editappointment.work_id = Convert.ToInt32(appointment.work_id);
editappointment.time_interval_id = Convert.ToInt32(appointment.time_interval_id);
editappointment.user_id = Convert.ToInt32(Session["User_Id"]);
dbcon.Entry(editappointment).State = System.Data.Entity.EntityState.Modified;
dbcon.SaveChanges();
// delete the appointment already inserted days and again insert into another table
//SqlConnection appointmentconnection = new SqlConnection(dbcon.connectionString());
//var appointmentcommand = new SqlCommand("appooint_day", appointmentconnection);
//appointmentcommand.CommandType = CommandType.StoredProcedure;
//appointmentcommand.Parameters.AddWithValue("@appointment_id", Convert.ToInt32(appointment.appointment_id));
//appointmentcommand.Parameters.AddWithValue("@type", "deleteappointmentday");
//appointmentconnection.Open();
//SqlDataReader appointmentreader = appointmentcommand.ExecuteReader();
//appointmentconnection.Close();
SqlConnection connectiondeleteday = new SqlConnection(dbcon.connectionString());
var commanddeleteday = new SqlCommand("Appointmentday", connectiondeleteday);
commanddeleteday.CommandType = CommandType.StoredProcedure;
commanddeleteday.Parameters.AddWithValue("@maxid", Convert.ToInt32(0));
commanddeleteday.Parameters.AddWithValue("@day", Convert.ToInt32(0));
commanddeleteday.Parameters.AddWithValue("@appointmentid", Convert.ToInt32(appointment.appointment_id));
commanddeleteday.Parameters.AddWithValue("@type", "deleteday");
connectiondeleteday.Open();
SqlDataReader rdrdelete = commanddeleteday.ExecuteReader();
connectiondeleteday.Close();
if (!string.IsNullOrEmpty(multiplevalue))
{
string[] values = multiplevalue.Split(',');
for (int i = 0; i < values.Length; i++)
{
values[i] = values[i].Trim();
SqlConnection connectionupdateday = new SqlConnection(dbcon.connectionString());
var commandupdateday = new SqlCommand("Appointmentday", connectionupdateday);
commandupdateday.CommandType = CommandType.StoredProcedure;
commandupdateday.Parameters.AddWithValue("@maxid", Convert.ToInt32(appointment.appointment_id));
commandupdateday.Parameters.AddWithValue("@day", Convert.ToInt32(values[i]));
commandupdateday.Parameters.AddWithValue("@appointmentid", Convert.ToInt32(0));
commandupdateday.Parameters.AddWithValue("@type", "updateday");
connectionupdateday.Open();
SqlDataReader rdrupdate = commandupdateday.ExecuteReader();
connectiondeleteday.Close();
}
}
return(RedirectToAction("Appointment", "Appointment"));
}
}
catch (DbEntityValidationException ex)
{
foreach (var entityValidationErrors in ex.EntityValidationErrors)
{
foreach (var validationError in entityValidationErrors.ValidationErrors)
{
Response.Write("Property: " + validationError.PropertyName + " Error: " + validationError.ErrorMessage);
}
}
return(View());
}
}
else
{
return(RedirectToAction("Login", "Account"));
}
}
public ActionResult Create_Appointment(juggle.Models.tbl_appointment appointment)
{
if (Session["User_Id"] == null)
{
return(RedirectToAction("Login", "Account"));
}
using (jugglecontext dbcon = new jugglecontext())
{
try
{
tbl_appointment addappointment = new tbl_appointment();
string multipleattribute = Request.Form["attribute_idmul"].ToString();
string multiplelist = null;
if (appointment.recurring)
{
multiplelist = Request.Form["multiple"];
}
else
{
multiplelist = null;
}
addappointment.created_date = DateTime.Now;
addappointment.description = appointment.description;
addappointment.recurring = appointment.recurring;
if (!string.IsNullOrEmpty(multiplelist))
{
addappointment.day = multiplelist;
}
else
{
multiplelist = "0"; addappointment.day = "0";
}
//addappointment.day = appointment.day;
addappointment.start_date = appointment.start_date;
addappointment.end_date = appointment.end_date;
addappointment.time_range_start = appointment.time_range_start;
addappointment.time_range_end = appointment.time_range_end;
// add two extra field that store the calendar start time and end time
// it is use for the event of google calendar only
// start
string startdate1 = appointment.start_date.ToString();
DateTime strstartdate1 = Convert.ToDateTime(startdate1);
string enddate1 = appointment.end_date.ToString();
DateTime strenddate1 = Convert.ToDateTime(enddate1);
DateTime cal_startdate = Convert.ToDateTime(strstartdate1.ToString("yyyy/MM/dd") + " " + appointment.time_range_start);
DateTime cal_enddate = Convert.ToDateTime(strenddate1.ToString("yyyy/MM/dd") + " " + appointment.time_range_end);
addappointment.Calendar_Start_datetime = DateTimeOffset.Parse(cal_startdate.ToString()).UtcDateTime;
addappointment.Calendar_End_datetime = DateTimeOffset.Parse(cal_enddate.ToString()).UtcDateTime;
string timezone = "West Asia Standard Time";
TimeZoneInfo targetZone = TimeZoneInfo.FindSystemTimeZoneById(timezone);
cal_startdate = TimeZoneInfo.ConvertTimeFromUtc(cal_startdate, targetZone);
cal_enddate = TimeZoneInfo.ConvertTimeFromUtc(cal_enddate, targetZone);
addappointment.Calendar_Start_datetime = cal_startdate;
addappointment.Calendar_End_datetime = cal_enddate;
// end
string startingtime = appointment.time_range_start.ToString();
string endindtime = appointment.time_range_end.ToString();
TimeSpan starttime = TimeSpan.Parse(startingtime);
TimeSpan endatime = TimeSpan.Parse(endindtime);
TimeSpan Difflength = endatime.Subtract(starttime);
int total = (Difflength.Hours * 60) + Difflength.Minutes;
addappointment.length = total;
if (!string.IsNullOrEmpty(addappointment.notes))
{
addappointment.notes = appointment.notes;
}
else
{
addappointment.notes = "N/A";
}
addappointment.client_id = Convert.ToInt32(appointment.client_id);
addappointment.work_id = Convert.ToInt32(appointment.work_id);
addappointment.time_interval_id = Convert.ToInt32(appointment.time_interval_id);
addappointment.user_id = Convert.ToInt32(Session["User_Id"]);
addappointment.attribute_id = multipleattribute.ToString();
addappointment.time = appointment.time;
dbcon.tbl_appointment.Add(addappointment);
dbcon.SaveChanges();
var maxid = 0;
try
{
var context_latest_Id = new jugglecontext();
var maxappointmentid = (from a in context_latest_Id.tbl_appointment
select new
{
a.appointment_id
}).ToList();
maxid = Convert.ToInt32(maxappointmentid[0].appointment_id);
for (int i = 0; i < maxappointmentid.Count; i++)
{
if (maxid < Convert.ToInt32(maxappointmentid[i].appointment_id))
{
maxid = Convert.ToInt32(maxappointmentid[i].appointment_id);
}
}
}
catch { }
if (!string.IsNullOrEmpty(multiplelist))
{
string[] values = multiplelist.Split(',');
if (appointment.recurring)
{
appointment.end_date = null;
}
for (int i = 0; i < values.Length; i++)
{
values[i] = values[i].Trim();
// add day into another table
SqlConnection connectioninsertday = new SqlConnection(dbcon.connectionString());
var commandinsertday = new SqlCommand("Appointmentday", connectioninsertday);
commandinsertday.CommandType = CommandType.StoredProcedure;
commandinsertday.Parameters.AddWithValue("@maxid", Convert.ToInt32(maxid));
commandinsertday.Parameters.AddWithValue("@day", Convert.ToInt32(values[i]));
commandinsertday.Parameters.AddWithValue("@appointmentid", Convert.ToInt32(0));
//commandinsertday.Parameters.AddWithValue("@date", dating);
commandinsertday.Parameters.AddWithValue("@type", "insertday");
connectioninsertday.Open();
SqlDataReader rdr = commandinsertday.ExecuteReader();
connectioninsertday.Close();
}
}
return(RedirectToAction("AppointMent", "AppointMent"));
}
catch (DbEntityValidationException ex)
{
foreach (var entityValidationErrors in ex.EntityValidationErrors)
{
foreach (var validationError in entityValidationErrors.ValidationErrors)
{
Response.Write("Property: " + validationError.PropertyName + " Error: " + validationError.ErrorMessage);
}
}
return(View(appointment));
}
}
}
