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