public static string GetData(string ID) { string SrtConnString = WebConfigurationManager.ConnectionStrings["SageConnnetionString"].ConnectionString; SqlConnection db = new SqlConnection(SrtConnString); calendar_detail e = new calendar_detail(); try { db.Open(); StringBuilder sb = new StringBuilder(); sb.AppendLine(" SELECT cr.CID, "); sb.AppendLine(" cr.UniqueID, "); sb.AppendLine(" cr.StartDate, "); sb.AppendLine(" cr.EndDate, "); sb.AppendLine(" cr.Subject, "); sb.AppendLine(" cr.Creator, "); sb.AppendLine(" cr.Name, "); sb.AppendLine(" cr.HR_Calendar "); sb.AppendLine(" FROM Calendar_Room as cr "); sb.AppendLine(" WHERE 1=1 "); sb.AppendLine(" and cr.CID = '" + ID + "' "); sb.AppendLine(" and cr.Deleted is null "); SqlCommand sql = new SqlCommand(sb.ToString(), db); DataTable table = new DataTable(); table.Load(sql.ExecuteReader()); if (table.Rows.Count > 0) { string StartDate = table.Rows[0]["StartDate"].ToString(); string EndDate = table.Rows[0]["EndDate"].ToString(); string CID = table.Rows[0]["CID"].ToString(); string UniqueID = table.Rows[0]["UniqueID"].ToString(); string HR_Calendar = table.Rows[0]["HR_Calendar"].ToString(); DateTime Select_StartDate = Convert.ToDateTime(StartDate); e = new calendar_detail(); e.id = UniqueID; e.cid = CID; e.start = Convert.ToDateTime(StartDate).ToString("yyyy-MM-dd HH:mm", new System.Globalization.CultureInfo("en-US")); e.end = Convert.ToDateTime(EndDate).ToString("yyyy-MM-dd HH:mm", new System.Globalization.CultureInfo("en-US")); e.timestart = Convert.ToDateTime(StartDate).ToString("HH:mm", new System.Globalization.CultureInfo("en-US")); e.timeend = Convert.ToDateTime(EndDate).ToString("HH:mm tt", new System.Globalization.CultureInfo("en-US")); e.title = table.Rows[0]["Subject"].ToString(); e.creator = table.Rows[0]["Creator"].ToString(); e.Room = table.Rows[0]["Name"].ToString(); e.HRCalendar = table.Rows[0]["HR_Calendar"].ToString(); sb = new StringBuilder(); sb.AppendLine(" SELECT ca.CAID, "); sb.AppendLine(" ca.Email, "); sb.AppendLine(" ca.Name, "); sb.AppendLine(" ca.BookingDate "); sb.AppendLine(" FROM Calendar_Attendee as ca "); sb.AppendLine(" WHERE 1=1 "); sb.AppendLine(" and ca.CID = '" + CID + "' "); sb.AppendLine(" and ca.Deleted is null "); sql = new SqlCommand(sb.ToString(), db); table = new DataTable(); table.Load(sql.ExecuteReader()); string List = ""; if (table.Rows.Count > 0) { List += "<table class='table table-bordered table-striped'>"; List += "<thead class='bg-secondary text-white'>"; List += "<tr>"; List += "<th class='text-center; align-top'>Email</th>"; List += "<th class='text-center; align-top'>Name</th>"; List += "<th class='text-center; align-top'>Booking Date</th>"; if (HR_Calendar.Equals("True")) { if (Select_StartDate > DateTime.Now) { List += "<th class='text-center; align-top'>Remove</th>"; } } List += "</tr>"; List += "</thead>"; List += "<tbody id='body" + CID + "'>"; for (int i = 0; i < table.Rows.Count; i++) { string CAID = table.Rows[i]["CAID"].ToString(); string EmailAttendee = table.Rows[i]["Email"].ToString(); string NameAttendee = table.Rows[i]["Name"].ToString(); string BookingDate = Convert.ToDateTime(table.Rows[i]["BookingDate"]).ToString("yyyy-MM-dd HH:mm", new System.Globalization.CultureInfo("en-US")); List += "<tr id='attendee" + CAID + "'>"; List += "<td>"; List += EmailAttendee; List += "</td>"; List += "<td>"; List += NameAttendee; List += "</td>"; List += "<td>"; List += BookingDate; List += "</td>"; if (HR_Calendar.Equals("True")) { if (Select_StartDate > DateTime.Now) { List += "<td class='text-center'>"; List += "<a class='delete' onclick='d(" + table.Rows[i]["CAID"].ToString() + ")' caid='" + table.Rows[i]["CAID"].ToString() + "'><i class='fas fa-trash-alt' class='text-secondary'></i></a>"; List += "</td>"; } } List += "</tr>"; } List += "</tbody>"; List += "</table>"; } e.attendee = List; } } catch (Exception ex) { e = new calendar_detail(); e.title = ex.Message + "_" + ex.StackTrace; } finally { db.Close(); } return(JsonConvert.SerializeObject(e)); }
public static string GetData(string ID, string EmailStaff) { string SrtConnString = WebConfigurationManager.ConnectionStrings["SageConnnetionString"].ConnectionString; SqlConnection db = new SqlConnection(SrtConnString); calendar_detail e = new calendar_detail(); try { db.Open(); StringBuilder sb = new StringBuilder(); sb.AppendLine(" SELECT cr.CID, "); sb.AppendLine(" cr.UniqueID, "); sb.AppendLine(" cr.StartDate, "); sb.AppendLine(" cr.EndDate, "); sb.AppendLine(" cr.Subject, "); sb.AppendLine(" cr.Creator, "); sb.AppendLine(" cr.Name, "); sb.AppendLine(" cr.HR_Calendar "); sb.AppendLine(" FROM Calendar_Room as cr "); sb.AppendLine(" WHERE 1=1 "); sb.AppendLine(" and cr.CID = '" + ID + "' "); sb.AppendLine(" and cr.Deleted is null "); SqlCommand sql = new SqlCommand(sb.ToString(), db); DataTable table = new DataTable(); table.Load(sql.ExecuteReader()); if (table.Rows.Count > 0) { string StartDate = table.Rows[0]["StartDate"].ToString(); string EndDate = table.Rows[0]["EndDate"].ToString(); string CID = table.Rows[0]["CID"].ToString(); string UniqueID = table.Rows[0]["UniqueID"].ToString(); string HR_Calendar = table.Rows[0]["HR_Calendar"].ToString(); DateTime Select_StartDate = Convert.ToDateTime(StartDate); e = new calendar_detail(); e.id = UniqueID; e.cid = CID; e.start = Convert.ToDateTime(StartDate).ToString("yyyy-MM-dd HH:mm", new System.Globalization.CultureInfo("en-US")); e.end = Convert.ToDateTime(EndDate).ToString("yyyy-MM-dd HH:mm", new System.Globalization.CultureInfo("en-US")); e.timestart = Convert.ToDateTime(StartDate).ToString("HH:mm", new System.Globalization.CultureInfo("en-US")); e.timeend = Convert.ToDateTime(EndDate).ToString("HH:mm tt", new System.Globalization.CultureInfo("en-US")); e.title = table.Rows[0]["Subject"].ToString(); e.creator = table.Rows[0]["Creator"].ToString(); e.Room = table.Rows[0]["Name"].ToString(); e.HRCalendar = table.Rows[0]["HR_Calendar"].ToString(); sb = new StringBuilder(); sb.AppendLine(" SELECT ca.CAID, "); sb.AppendLine(" ca.Email, "); sb.AppendLine(" ca.Name, "); sb.AppendLine(" ca.BookingDate "); sb.AppendLine(" FROM Calendar_Attendee as ca "); sb.AppendLine(" WHERE 1=1 "); sb.AppendLine(" and ca.CID = '" + CID + "' "); sb.AppendLine(" and ca.Deleted is null "); sql = new SqlCommand(sb.ToString(), db); table = new DataTable(); table.Load(sql.ExecuteReader()); string List = ""; if (table.Rows.Count > 0) { for (int i = 0; i < table.Rows.Count; i++) { string CAID = table.Rows[i]["CAID"].ToString(); string EmailAttendee = table.Rows[i]["Email"].ToString(); string NameAttendee = table.Rows[i]["Name"].ToString(); string BookingDate = Convert.ToDateTime(table.Rows[i]["BookingDate"]).ToString("yyyy-MM-dd HH:mm", new System.Globalization.CultureInfo("en-US")); if (EmailAttendee.Equals(EmailStaff)) { e.attendee = "1"; break; } else { e.attendee = "0"; } } } else { e.attendee = "0"; } } } catch (Exception ex) { e = new calendar_detail(); e.title = ex.Message + "_" + ex.StackTrace; } finally { db.Close(); } return(JsonConvert.SerializeObject(e)); }