private static string ByApptStatusQuery(bool hasClinicsEnabled, DateTime dateStart, DateTime dateEnd, string whereProv, string whereClin)
{
string queryBrokenApts = "SELECT " + DbHelper.DateTFormatColumn("appointment.AptDateTime", "%m/%d/%Y %H:%i:%s") + " AptDateTime, "
+ "" + DbHelper.Concat("patient.LName", "', '", "patient.FName") + " Patient,doctor.Abbr Doctor,hygienist.Abbr Hygienist, "
+ "appointment.IsHygiene IsHygieneApt ";
if (hasClinicsEnabled)
{
queryBrokenApts += ",COALESCE(clinic.Description,'" + POut.String(Lans.g("FormRpBrokenAppointments", "Unassigned")) + "') ClinicDesc "; //Coalesce is Oracle compatible
}
queryBrokenApts +=
"FROM appointment "
+ "INNER JOIN patient ON appointment.PatNum=patient.PatNum "
+ "LEFT JOIN provider doctor ON doctor.ProvNum=appointment.ProvNum "
+ "LEFT JOIN provider hygienist ON hygienist.ProvNum=appointment.ProvHyg ";
if (hasClinicsEnabled)
{
queryBrokenApts += "LEFT JOIN clinic ON clinic.ClinicNum=appointment.ClinicNum ";
}
queryBrokenApts +=
"WHERE " + DbHelper.DtimeToDate("appointment.AptDateTime") + " BETWEEN " + POut.Date(dateStart)
+ " AND " + POut.Date(dateEnd) + " "
+ "AND appointment.AptStatus=" + POut.Int((int)ApptStatus.Broken) + " "
+ whereProv;
if (hasClinicsEnabled)
{
queryBrokenApts += whereClin + " "
+ "ORDER BY clinic.Description,appointment.AptDateTime,patient.LName,patient.FName";
}
else
{
queryBrokenApts += "ORDER BY appointment.AptDateTime,patient.LName,patient.FName ";
}
return(queryBrokenApts);
}
