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)); } } }