private static List<Appointment> QueryAppointmentTable(string ClientId, string Episode, DateTime? FormDate, string StaffId, DateTime? FormTime, string QueryType) { var AppointmentList = new List<Appointment>(); var connectionString = ConfigurationManager.ConnectionStrings["CacheODBC"].ConnectionString; #region commandText var commandText = "SELECT appt_data.appointment_date as AppointmentDate," + "appt_data.appointment_start_time as StartTime," + "appt_data.appointment_end_time as EndTime," + "appt_data.ID as AppointmentId," + "appt_data.PATID as ClientId," + "appt_data.patient_name as ClientName," + "appt_data.EPISODE_NUMBER as EpisodeNumber," + "appt_data.STAFFID as StaffId," + "appt_data.staff_name as StaffName," + "appt_data.program_code as ProgramCode," + "appt_data.program_value as ProgramValue," + "appt_data.status_code as StatusCode," + "appt_data.status_value as StatusValue," + "appt_data.SERVICE_CODE as ScheduledServiceCode," + "appt_data.service_description as ScheduledServiceValue," + "appt_data.missed_visit_service_code as MissedVisitServiceCode," + "appt_data.missed_visit_service_desc as MissedVisitServiceValue," + "appt_data.SITEID as SiteId,"+ "appt_data.site_name as SiteName,"+ "appt_data.posted_code as PostedCode," + "appt_data.posted_value as PostedValue," + "appt_data.appointment_notes as Notes " + "FROM SYSTEM.appt_data "; #endregion try { using (var connection = new OdbcConnection(connectionString)) { connection.Open(); using (var dbcommand = new OdbcCommand(commandText, connection)) { switch (QueryType) { case "Client": commandText += "WHERE appt_data.PATID=? "; dbcommand.Parameters.Add(new OdbcParameter("PATID", ClientId)); break; case "Episode": commandText += "WHERE appt_data.PATID=? "; dbcommand.Parameters.Add(new OdbcParameter("PATID", ClientId)); commandText += "AND appt_data.EPISODE_NUMBER=? "; dbcommand.Parameters.Add(new OdbcParameter("EPISODE_NUMBER", Episode)); break; case "Date": commandText += "WHERE appt_data.PATID=? "; dbcommand.Parameters.Add(new OdbcParameter("PATID", ClientId)); commandText += "AND appt_data.EPISODE_NUMBER=? "; dbcommand.Parameters.Add(new OdbcParameter("EPISODE_NUMBER", Episode)); commandText += "AND appt_data.appointment_date=? "; dbcommand.Parameters.Add(new OdbcParameter("appointment_date", FormDate.Value.ToString("yyyy-MM-dd"))); break; case "Staff": commandText += "WHERE appt_data.PATID=? "; dbcommand.Parameters.Add(new OdbcParameter("PATID", ClientId)); commandText += "AND appt_data.EPISODE_NUMBER=? "; dbcommand.Parameters.Add(new OdbcParameter("EPISODE_NUMBER", Episode)); commandText += "AND appt_data.appointment_date=? "; dbcommand.Parameters.Add(new OdbcParameter("appointment_date", FormDate.Value.ToString("yyyy-MM-dd"))); commandText += "AND appt_data.STAFFID=? "; dbcommand.Parameters.Add(new OdbcParameter("STAFFID", StaffId)); break; case "Time": commandText += "WHERE appt_data.PATID=? "; dbcommand.Parameters.Add(new OdbcParameter("PATID", ClientId)); commandText += "AND appt_data.EPISODE_NUMBER=? "; dbcommand.Parameters.Add(new OdbcParameter("EPISODE_NUMBER", Episode)); commandText += "AND appt_data.appointment_date=? "; dbcommand.Parameters.Add(new OdbcParameter("appointment_date", FormDate.Value.ToString("yyyy-MM-dd"))); commandText += "AND appt_data.STAFFID=? "; dbcommand.Parameters.Add(new OdbcParameter("STAFFID", StaffId)); commandText += "AND appt_data.appointment_start_time=? "; dbcommand.Parameters.Add(new OdbcParameter("appointment_start_time", FormTime.Value.ToString("hh:mm tt"))); break; default: break; } dbcommand.CommandText = commandText; using (var reader = dbcommand.ExecuteReader()) { while (reader.Read()) { #region ReadInValues var appointment = new Appointment(); appointment.AppointmentDate = reader.GetDate(reader.GetOrdinal("AppointmentDate")); appointment.AppointmentId = reader["AppointmentId"].ToString(); appointment.ClientId = reader["ClientId"].ToString(); appointment.ClientName = reader["ClientName"].ToString(); appointment.EpisodeNumber = reader["EpisodeNumber"].ToString(); appointment.EndTime = reader["EndTime"].ToString(); appointment.MissedVisitServiceCode = reader["MissedVisitServiceCode"].ToString(); appointment.MissedVisitServiceValue = reader["MissedVisitServiceValue"].ToString(); appointment.Notes = reader["Notes"].ToString(); appointment.PostedCode = reader["PostedCode"].ToString(); appointment.PostedValue = reader["PostedValue"].ToString(); appointment.ProgramCode = reader["ProgramCode"].ToString(); appointment.ProgramValue = reader["ProgramValue"].ToString(); appointment.ScheduledServiceCode = reader["ScheduledServiceCode"].ToString(); appointment.ScheduledServiceValue = reader["ScheduledServiceValue"].ToString(); appointment.StaffId = reader["StaffId"].ToString(); appointment.StaffName = reader["StaffName"].ToString(); appointment.StartTime = reader["StartTime"].ToString(); appointment.StatusCode = reader["StatusCode"].ToString(); appointment.StatusValue = reader["StatusValue"].ToString(); appointment.SiteId = reader["SiteId"].ToString(); appointment.SiteName = reader["SiteName"].ToString(); AppointmentList.Add(appointment); #endregion } } } connection.Close(); } } catch (Exception ex) { } return AppointmentList; }
private OptionObject AppointmentEditDetails(OptionObject optionObject) { var systemCode = ConfigurationManager.AppSettings["SystemCode"].ToString(); var username = ConfigurationManager.AppSettings["Username"].ToString(); var password = ConfigurationManager.AppSettings["Password"].ToString(); string appointmentStatusList = "&2&4&5&6&7&8&9&"; var returnOptionObject = new OptionObject(); var ClientField = new FieldObject { FieldNumber = "10010" }; var EpisodeNumberField = new FieldObject { FieldNumber = "12345" }; var AppointmentDateField = new FieldObject { FieldNumber = "22000" }; var StaffField = new FieldObject { FieldNumber = "10003" }; var AppointmentStartTimeField = new FieldObject { FieldNumber = "10107" }; var AppointmentStatusField = new FieldObject { FieldNumber = "10005" }; try { foreach (var field in optionObject.Forms.ElementAt(0).CurrentRow.Fields) { if (field.FieldNumber.Equals(ClientField.FieldNumber)) ClientField.FieldValue = field.FieldValue; if (field.FieldNumber.Equals(EpisodeNumberField.FieldNumber)) EpisodeNumberField.FieldValue = field.FieldValue; if (field.FieldNumber.Equals(AppointmentDateField.FieldNumber)) AppointmentDateField.FieldValue = field.FieldValue; if (field.FieldNumber.Equals(StaffField.FieldNumber)) StaffField.FieldValue = field.FieldValue; if (field.FieldNumber.Equals(AppointmentStartTimeField.FieldNumber)) AppointmentStartTimeField.FieldValue = field.FieldValue; if (field.FieldNumber.Equals(AppointmentStatusField.FieldNumber)) AppointmentStatusField.FieldValue = field.FieldValue; } if (appointmentStatusList.IndexOf(AppointmentStatusField.FieldValue) >= 0) { var appointments = AppointmentRepository.GetAppointment(ClientField.FieldValue, EpisodeNumberField.FieldValue, DateTime.Parse(AppointmentDateField.FieldValue), StaffField.FieldValue, DateTime.Parse(AppointmentStartTimeField.FieldValue)); var WebSvcAppointmentObj = new AppointmentScheduling.AppointmentSchedulingObject(); var WebSvcAppointment = new AppointmentScheduling.AppointmentScheduling(); var SingleAppointment = new Appointment(); SingleAppointment = appointments.ElementAt(0); WebSvcAppointmentObj.ApptDate = SingleAppointment.AppointmentDate; WebSvcAppointmentObj.ApptDateSpecified = true; WebSvcAppointmentObj.ApptStatus = SingleAppointment.StatusCode; WebSvcAppointmentObj.ClientID = SingleAppointment.ClientId; WebSvcAppointmentObj.Episode = Convert.ToInt64(SingleAppointment.EpisodeNumber); WebSvcAppointmentObj.EpisodeSpecified = true; WebSvcAppointmentObj.Site = SingleAppointment.SiteId; WebSvcAppointmentObj.ApptStartTime = SingleAppointment.StartTime; WebSvcAppointmentObj.ApptEndTime = SingleAppointment.EndTime; WebSvcAppointmentObj.ServiceCode = SingleAppointment.ScheduledServiceCode; WebSvcAppointmentObj.Program = SingleAppointment.ProgramCode; WebSvcAppointmentObj.NumberOfClients = "1"; WebSvcAppointmentObj.ApptNotes = SingleAppointment.Notes; if (appointmentStatusList.IndexOf(SingleAppointment.StatusCode) >= 0) { WebSvcAppointmentObj.MissedVisitSvcCode = getMissedVisitCode(SingleAppointment.StatusCode); WebSvcAppointmentObj.MissedVisit = "X"; } var response = WebSvcAppointment.UpdateAppointment(systemCode, username, password, WebSvcAppointmentObj, SingleAppointment.StaffId, SingleAppointment.AppointmentId); } } catch (Exception e) { } returnOptionObject.EntityID = optionObject.EntityID; returnOptionObject.OptionId = optionObject.OptionId; returnOptionObject.Facility = optionObject.Facility; returnOptionObject.SystemCode = optionObject.SystemCode; return returnOptionObject; }