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