public static string getNursesData(Endorsement_Nursing_schedule data) { string config = Convert.ToString(ConfigurationManager.ConnectionStrings["dbcon"]); List <Endorsement_Nursing_schedule> user = new List <Endorsement_Nursing_schedule>(); List <Nurses> Nurses = new List <Nurses>(); SqlConnection con = new SqlConnection(config); con.Open(); using (SqlCommand cmd = new SqlCommand("select * from adminusers where Role_id = @Role_id and Emp_ID in (select Nurse_id from Endorsement_Nursing_schedule where Unit_id = @Unit_id and Shift_date = @Date and (Shift = @Shift))", con)) { cmd.Parameters.Add("@Unit_id", SqlDbType.Int).Value = data.Unit_id; cmd.Parameters.Add("@Role_id", SqlDbType.Int).Value = data.Role_id; cmd.Parameters.Add("@Shift", SqlDbType.VarChar).Value = data.Shift; cmd.Parameters.Add("@Date", SqlDbType.VarChar).Value = data.Date; SqlDataReader idr = cmd.ExecuteReader(); if (idr.HasRows) { Nurses = populateNursesLisst(idr, con); } } con.Close(); return(JsonConvert.SerializeObject(Nurses)); }
public static string getScheduleData(Endorsement_Nursing_schedule data) { string config = Convert.ToString(ConfigurationManager.ConnectionStrings["dbcon"]); List <Endorsement_Nursing_schedule> Endorsement_Nursing_schedule = new List <Endorsement_Nursing_schedule>(); SqlConnection con = new SqlConnection(config); con.Open(); using ( SqlCommand cmd = new SqlCommand("SELECT s.Id,s.Shift_date,s.Shift,s.Unit_id,s.Unit_name,s.Nurse_role,s.Nurse_name,s.Nurse_id, Endorsement_PatientData.Unit, COUNT(Endorsement_PatientData.id) AS patientsNum from Endorsement_PatientData INNER JOIN Endorsement_Nursing_schedule as s On s.Nurse_id = @Nurse_id and s.Shift_date >= DATEADD(day,-2, GETDATE()) and s.Shift_date < DATEADD(day,0, GETDATE()) and Endorsement_PatientData.Unit = s.Unit_name and Endorsement_PatientData.Patient_Status = 1 GROUP BY Endorsement_PatientData.Unit, s.Id,s.Shift_date,s.Shift,s.Unit_id,s.Unit_name,s.Nurse_role,s.Nurse_name,s.Nurse_id", con) ) { cmd.Parameters.Add("@Nurse_id", SqlDbType.Int).Value = data.Nurse_id; SqlDataReader idr = cmd.ExecuteReader(); if (idr.HasRows) { Endorsement_Nursing_schedule = populateEndorsement_Nursing_scheduleLisst(idr, con); } else { con.Close(); con.Open(); using ( SqlCommand cmd1 = new SqlCommand("SELECT * from Endorsement_Nursing_schedule where Nurse_id = @Nurse_id and Shift_date >= DATEADD(day,-2, GETDATE()) and Shift_date < DATEADD(day,0, GETDATE())", con) ) { cmd1.Parameters.Add("@Nurse_id", SqlDbType.Int).Value = data.Nurse_id; SqlDataReader idr1 = cmd1.ExecuteReader(); if (idr1.HasRows) { Endorsement_Nursing_schedule = populateEndorsement_Nursing_scheduleLisst1(idr1, con); } } } } con.Close(); return(JsonConvert.SerializeObject(Endorsement_Nursing_schedule)); }