///<summary>Used in FormConfirmList</summary>
public static DataTable GetConfirmList(DateTime dateFrom,DateTime dateTo,long provNum,long clinicNum)
{
if(RemotingClient.RemotingRole==RemotingRole.ClientWeb) {
return Meth.GetTable(MethodBase.GetCurrentMethod(),dateFrom,dateTo,provNum,clinicNum);
}
DataTable table=new DataTable();
DataRow row;
//columns that start with lowercase are altered for display rather than being raw data.
table.Columns.Add("AddrNote");
table.Columns.Add("AptNum");
table.Columns.Add("age");
table.Columns.Add("AptDateTime",typeof(DateTime));
table.Columns.Add("aptDateTime");
table.Columns.Add("confirmed");
table.Columns.Add("contactMethod");
table.Columns.Add("email");//could be patient or guarantor email.
table.Columns.Add("Guarantor");
table.Columns.Add("medNotes");
table.Columns.Add("nameF");//or preferred.
table.Columns.Add("nameFL");
table.Columns.Add("Note");
table.Columns.Add("patientName");
table.Columns.Add("PatNum");
table.Columns.Add("PreferConfirmMethod");
table.Columns.Add("ProcDescript");
List<DataRow> rows=new List<DataRow>();
string command="SELECT patient.PatNum,"
+"patient.LName,"
+"patient.FName,patient.Preferred,patient.LName, "
+"patient.Guarantor,AptDateTime,patient.Birthdate,patient.HmPhone,"
+"patient.WkPhone,patient.WirelessPhone,ProcDescript,Confirmed,Note,"
+"patient.AddrNote,AptNum,patient.MedUrgNote,patient.PreferConfirmMethod,"
+"guar.Email guarEmail,patient.Email,patient.Premed "
+"FROM patient,appointment,patient guar "
+"WHERE patient.PatNum=appointment.PatNum "
+"AND patient.Guarantor=guar.PatNum "
+"AND AptDateTime > "+POut.Date(dateFrom)+" "
+"AND AptDateTime < "+POut.Date(dateTo.AddDays(1))+" "
+"AND (AptStatus=1 "//scheduled
+"OR AptStatus=4) ";//ASAP
if(provNum>0){
command+="AND (appointment.ProvNum="+POut.Long(provNum)+" OR appointment.ProvHyg="+POut.Long(provNum)+") ";
}
if(clinicNum>0) {
command+="AND appointment.ClinicNum="+POut.Long(clinicNum)+" ";
}
command+="ORDER BY AptDateTime";
DataTable rawtable=Db.GetTable(command);
DateTime dateT;
Patient pat;
ContactMethod contmeth;
for(int i=0;i<rawtable.Rows.Count;i++) {
row=table.NewRow();
row["AddrNote"]=rawtable.Rows[i]["AddrNote"].ToString();
row["AptNum"]=rawtable.Rows[i]["AptNum"].ToString();
row["age"]=Patients.DateToAge(PIn.Date(rawtable.Rows[i]["Birthdate"].ToString())).ToString();//we don't care about m/y.
dateT=PIn.DateT(rawtable.Rows[i]["AptDateTime"].ToString());
row["AptDateTime"]=dateT;
row["aptDateTime"]=dateT.ToShortDateString()+"\r\n"+dateT.ToShortTimeString();
row["confirmed"]=DefC.GetName(DefCat.ApptConfirmed,PIn.Long(rawtable.Rows[i]["Confirmed"].ToString()));
contmeth=(ContactMethod)PIn.Int(rawtable.Rows[i]["PreferConfirmMethod"].ToString());
if(contmeth==ContactMethod.None || contmeth==ContactMethod.HmPhone) {
row["contactMethod"]=Lans.g("FormConfirmList","Hm:")+rawtable.Rows[i]["HmPhone"].ToString();
}
if(contmeth==ContactMethod.WkPhone) {
row["contactMethod"]=Lans.g("FormConfirmList","Wk:")+rawtable.Rows[i]["WkPhone"].ToString();
}
if(contmeth==ContactMethod.WirelessPh) {
row["contactMethod"]=Lans.g("FormConfirmList","Cell:")+rawtable.Rows[i]["WirelessPhone"].ToString();
}
if(contmeth==ContactMethod.Email) {
row["contactMethod"]=rawtable.Rows[i]["Email"].ToString();
}
if(contmeth==ContactMethod.DoNotCall || contmeth==ContactMethod.SeeNotes) {
row["contactMethod"]=Lans.g("enumContactMethod",contmeth.ToString());
}
if(rawtable.Rows[i]["Email"].ToString()=="" && rawtable.Rows[i]["guarEmail"].ToString()!="") {
row["email"]=rawtable.Rows[i]["guarEmail"].ToString();
}
else {
row["email"]=rawtable.Rows[i]["Email"].ToString();
}
row["Guarantor"]=rawtable.Rows[i]["Guarantor"].ToString();
row["medNotes"]="";
if(rawtable.Rows[i]["Premed"].ToString()=="1"){
row["medNotes"]=Lans.g("FormConfirmList","Premedicate");
}
if(rawtable.Rows[i]["MedUrgNote"].ToString()!=""){
if(row["medNotes"].ToString()!="") {
row["medNotes"]+="\r\n";
}
row["medNotes"]+=rawtable.Rows[i]["MedUrgNote"].ToString();
}
pat=new Patient();
pat.LName=rawtable.Rows[i]["LName"].ToString();
pat.FName=rawtable.Rows[i]["FName"].ToString();
pat.Preferred=rawtable.Rows[i]["Preferred"].ToString();
row["nameF"]=pat.GetNameFirstOrPreferred();
row["nameFL"]=pat.GetNameFirstOrPrefL();
row["Note"]=rawtable.Rows[i]["Note"].ToString();
row["patientName"]= pat.LName+"\r\n";
if(pat.Preferred!=""){
row["patientName"]+="'"+pat.Preferred+"'";
}
else{
row["patientName"]+=pat.FName;
}
row["PatNum"]=rawtable.Rows[i]["PatNum"].ToString();
row["PreferConfirmMethod"]=rawtable.Rows[i]["PreferConfirmMethod"].ToString();
row["ProcDescript"]=rawtable.Rows[i]["ProcDescript"].ToString();
rows.Add(row);
}
//Array.Sort(orderDate,RecallList);
//return RecallList;
for(int i=0;i<rows.Count;i++) {
table.Rows.Add(rows[i]);
}
return table;
}
private static void FillFieldsForLabelAppointment(Sheet sheet,Appointment appt,Patient pat)
{
foreach(SheetField field in sheet.SheetFields) {
switch(field.FieldName) {
case "nameFL":
field.FieldValue=pat.GetNameFirstOrPrefL();
break;
case "nameLF":
field.FieldValue=pat.GetNameLF();
break;
case "weekdayDateTime":
field.FieldValue=appt.AptDateTime.ToString("ddd")+" "
+appt.AptDateTime.ToShortDateString()+" "
+appt.AptDateTime.ToShortTimeString();// h:mm tt");
break;
case "length":
int minutesTotal=appt.Pattern.Length*5;
int hours=minutesTotal/60;//automatically rounds down
int minutes=minutesTotal-hours*60;
field.FieldValue="";
if(hours>0){
field.FieldValue=hours.ToString()+" hours, ";
}
field.FieldValue+=minutes.ToString()+" min";
break;
}
}
}
///<summary>Used in FormConfirmList. The assumption is made that showRecall and showNonRecall will not both be false.</summary>
public static DataTable GetConfirmList(DateTime dateFrom,DateTime dateTo,long provNum,long clinicNum,bool showRecall,bool showNonRecall,bool showHygPresched) {
if(RemotingClient.RemotingRole==RemotingRole.ClientWeb) {
return Meth.GetTable(MethodBase.GetCurrentMethod(),dateFrom,dateTo,provNum,clinicNum,showRecall,showNonRecall,showHygPresched);
}
DataTable table=new DataTable();
DataRow row;
//columns that start with lowercase are altered for display rather than being raw data.
table.Columns.Add("AddrNote");
table.Columns.Add("AptNum");
table.Columns.Add("age");
table.Columns.Add("AptDateTime",typeof(DateTime));//This will actually be DateTimeAskedToArrive
table.Columns.Add("aptDateTime");//This will actually be DateTimeAskedToArrive
table.Columns.Add("ClinicNum");//patient.ClinicNum
table.Columns.Add("confirmed");
table.Columns.Add("contactMethod");
table.Columns.Add("dateSched");
table.Columns.Add("email");//could be patient or guarantor email.
table.Columns.Add("Guarantor");
table.Columns.Add("medNotes");
table.Columns.Add("nameF");//or preferred.
table.Columns.Add("nameFL");
table.Columns.Add("Note");
table.Columns.Add("patientName");
table.Columns.Add("PatNum");
table.Columns.Add("PreferConfirmMethod");
table.Columns.Add("ProcDescript");
table.Columns.Add("TxtMsgOk");
table.Columns.Add("WirelessPhone");
List<DataRow> rows=new List<DataRow>();
string command="SELECT patient.PatNum,patient.LName,patient.FName,patient.Preferred,patient.LName,patient.Guarantor,"
+"AptDateTime,patient.Birthdate,patient.ClinicNum,patient.HmPhone,patient.TxtMsgOk,patient.WkPhone,"
+"patient.WirelessPhone,ProcDescript,Confirmed,Note,patient.AddrNote,AptNum,patient.MedUrgNote,"
+"patient.PreferConfirmMethod,guar.Email guarEmail,patient.Email,patient.Premed,DateTimeAskedToArrive,LogDateTime "
+"FROM patient "
+"INNER JOIN appointment ON appointment.PatNum=patient.PatNum "
+"INNER JOIN patient guar ON guar.PatNum=patient.Guarantor "
+"LEFT JOIN securitylog ON securitylog.PatNum=appointment.PatNum AND securitylog.PermType=25 AND securitylog.FKey=appointment.AptNum "
+"WHERE AptDateTime > "+POut.Date(dateFrom)+" "
//Example: AptDateTime="2014-11-26 13:00". Filter is 11-26, giving "2014-11-27 00:00" to compare against. This captures all times.
+"AND AptDateTime < "+POut.Date(dateTo.AddDays(1))+" "
+"AND AptStatus IN(1,4) ";//scheduled,ASAP
if(provNum>0){
command+="AND ((appointment.ProvNum="+POut.Long(provNum)+" AND appointment.IsHygiene=0) "//only include doc if it's not a hyg appt
//"AND (appointment.ProvNum="+POut.Long(provNum)
//+" OR appointment.ProvHyg="+POut.Long(provNum)+") ";
+" OR (appointment.ProvHyg="+POut.Long(provNum)+" AND appointment.IsHygiene=1)) ";//only include hygienists if it's a hygiene appt
}
if(clinicNum>0) {
command+="AND appointment.ClinicNum="+POut.Long(clinicNum)+" ";
}
if(showRecall && !showNonRecall && !showHygPresched) {//Show recall only (the All option was not selected)
command+="AND appointment.AptNum IN ("
+"SELECT procedurelog.AptNum FROM procedurelog "
+"INNER JOIN procedurecode ON procedurelog.CodeNum=procedurecode.CodeNum "
+"AND procedurecode.IsHygiene=1) "//recall appt if there is 1 or more procedure on the appt that is marked IsHygiene
+"AND patient.PatNum IN ("
+"SELECT DISTINCT procedurelog.PatNum "
+"FROM procedurelog "
+"WHERE procedurelog.ProcStatus=2) ";//and the patient has had a procedure completed in the office (i.e. not the patient's first appt)
}
else if(!showRecall && showNonRecall && !showHygPresched) {//Show non-recall only (the All option was not selected)
command+="AND (appointment.AptNum NOT IN ("
+"SELECT AptNum FROM procedurelog "
+"INNER JOIN procedurecode ON procedurelog.CodeNum=procedurecode.CodeNum "
+"AND procedurecode.IsHygiene=1) "//include if the appointment does not have a procedure marked IsHygiene
+"OR patient.PatNum NOT IN ("
+"SELECT DISTINCT procedurelog.PatNum "
+"FROM procedurelog "
+"WHERE procedurelog.ProcStatus=2)) ";//or if the patient has never had a completed procedure (new patient appts)
}
else if(!showRecall && !showNonRecall && showHygPresched) {//Show hygiene prescheduled only (the All option was not selected)
//Example: LogDateTime="2014-11-26 13:00". Filter is 11-26, giving "2014-11-27 00:00" to compare against. This captures all times for 11-26.
string aptDateSql="";
if(DataConnection.DBtype==DatabaseType.MySql) {
aptDateSql="DATE(appointment.AptDateTime-INTERVAL 2 MONTH)";
}
else {
aptDateSql="ADD_MONTHS(TO_CHAR(appointment.AptDateTime,'MM/DD/YYYY %HH24:%MI:%SS'),-2)";
}
command+="AND (securitylog.PatNum IS NULL OR securitylog.LogDateTime < "+aptDateSql+") ";
}
command+="ORDER BY AptDateTime";
DataTable rawtable=Db.GetTable(command);
DateTime dateT;
DateTime timeAskedToArrive;
Patient pat;
ContactMethod contmeth;
for(int i=0;i<rawtable.Rows.Count;i++) {
row=table.NewRow();
row["AddrNote"]=rawtable.Rows[i]["AddrNote"].ToString();
row["AptNum"]=rawtable.Rows[i]["AptNum"].ToString();
row["age"]=Patients.DateToAge(PIn.Date(rawtable.Rows[i]["Birthdate"].ToString())).ToString();//we don't care about m/y.
dateT=PIn.DateT(rawtable.Rows[i]["AptDateTime"].ToString());
timeAskedToArrive=PIn.DateT(rawtable.Rows[i]["DateTimeAskedToArrive"].ToString());
if(timeAskedToArrive.Year>1880) {
dateT=timeAskedToArrive;
}
row["AptDateTime"]=dateT;
row["aptDateTime"]=dateT.ToShortDateString()+"\r\n"+dateT.ToShortTimeString();
row["ClinicNum"]=rawtable.Rows[i]["ClinicNum"].ToString();
row["confirmed"]=DefC.GetName(DefCat.ApptConfirmed,PIn.Long(rawtable.Rows[i]["Confirmed"].ToString()));
contmeth=(ContactMethod)PIn.Int(rawtable.Rows[i]["PreferConfirmMethod"].ToString());
if(contmeth==ContactMethod.None || contmeth==ContactMethod.HmPhone) {
row["contactMethod"]=Lans.g("FormConfirmList","Hm:")+rawtable.Rows[i]["HmPhone"].ToString();
}
if(contmeth==ContactMethod.WkPhone) {
row["contactMethod"]=Lans.g("FormConfirmList","Wk:")+rawtable.Rows[i]["WkPhone"].ToString();
}
if(contmeth==ContactMethod.WirelessPh) {
row["contactMethod"]=Lans.g("FormConfirmList","Cell:")+rawtable.Rows[i]["WirelessPhone"].ToString();
}
if(contmeth==ContactMethod.TextMessage) {
row["contactMethod"]=Lans.g("FormConfirmList","Text:")+rawtable.Rows[i]["WirelessPhone"].ToString();
}
if(contmeth==ContactMethod.Email) {
row["contactMethod"]=rawtable.Rows[i]["Email"].ToString();
}
if(contmeth==ContactMethod.DoNotCall || contmeth==ContactMethod.SeeNotes) {
row["contactMethod"]=Lans.g("enumContactMethod",contmeth.ToString());
}
row["dateSched"]="Unknown";
if(rawtable.Rows[i]["LogDateTime"].ToString().Length>0) {
row["dateSched"]=rawtable.Rows[i]["LogDateTime"].ToString();
}
if(rawtable.Rows[i]["Email"].ToString()=="" && rawtable.Rows[i]["guarEmail"].ToString()!="") {
row["email"]=rawtable.Rows[i]["guarEmail"].ToString();
}
else {
row["email"]=rawtable.Rows[i]["Email"].ToString();
}
row["Guarantor"]=rawtable.Rows[i]["Guarantor"].ToString();
row["medNotes"]="";
if(rawtable.Rows[i]["Premed"].ToString()=="1"){
row["medNotes"]=Lans.g("FormConfirmList","Premedicate");
}
if(rawtable.Rows[i]["MedUrgNote"].ToString()!=""){
if(row["medNotes"].ToString()!="") {
row["medNotes"]+="\r\n";
}
row["medNotes"]+=rawtable.Rows[i]["MedUrgNote"].ToString();
}
pat=new Patient();
pat.LName=rawtable.Rows[i]["LName"].ToString();
pat.FName=rawtable.Rows[i]["FName"].ToString();
pat.Preferred=rawtable.Rows[i]["Preferred"].ToString();
row["nameF"]=pat.GetNameFirstOrPreferred();
row["nameFL"]=pat.GetNameFirstOrPrefL();
row["Note"]=rawtable.Rows[i]["Note"].ToString();
row["patientName"]= pat.LName+"\r\n";
if(pat.Preferred!=""){
row["patientName"]+="'"+pat.Preferred+"'";
}
else{
row["patientName"]+=pat.FName;
}
row["PatNum"]=rawtable.Rows[i]["PatNum"].ToString();
row["PreferConfirmMethod"]=rawtable.Rows[i]["PreferConfirmMethod"].ToString();
row["ProcDescript"]=rawtable.Rows[i]["ProcDescript"].ToString();
row["TxtMsgOk"]=rawtable.Rows[i]["TxtMsgOk"].ToString();
row["WirelessPhone"]=rawtable.Rows[i]["WirelessPhone"].ToString();
rows.Add(row);
}
//Array.Sort(orderDate,RecallList);
//return RecallList;
for(int i=0;i<rows.Count;i++) {
table.Rows.Add(rows[i]);
}
return table;
}
