///<summary>Backs up the database to the same directory as the original just in case the user did not have sense enough to do a backup first.</summary> public static long MakeABackup() { //This function should always make the backup on the server itself, and since no directories are //referred to (all handled with MySQL), this function will always be referred to the server from //client machines. if(RemotingClient.RemotingRole==RemotingRole.ClientWeb) { return Meth.GetLong(MethodBase.GetCurrentMethod()); } //only used in two places: upgrading version, and upgrading mysql version. //Both places check first to make sure user is using mysql. //we have to be careful to throw an exception if the backup is failing. DataConnection dcon=new DataConnection(); string command="SELECT database()"; DataTable table=dcon.GetTable(command); string oldDb=PIn.String(table.Rows[0][0].ToString()); string newDb=oldDb+"backup_"+DateTime.Today.ToString("MM_dd_yyyy"); command="SHOW DATABASES"; table=dcon.GetTable(command); string[] databases=new string[table.Rows.Count]; for(int i=0;i<table.Rows.Count;i++) { databases[i]=table.Rows[i][0].ToString(); } if(Contains(databases,newDb)) {//if the new database name already exists //find a unique one int uniqueID=1; string originalNewDb=newDb; do { newDb=originalNewDb+"_"+uniqueID.ToString(); uniqueID++; } while(Contains(databases,newDb)); } command="CREATE DATABASE "+newDb+" CHARACTER SET utf8"; dcon.NonQ(command); command="SHOW FULL TABLES WHERE Table_type='BASE TABLE'";//Tables, not views. Does not work in MySQL 4.1, however we test for MySQL version >= 5.0 in PrefL. table=dcon.GetTable(command); string[] tableName=new string[table.Rows.Count]; for(int i=0;i<table.Rows.Count;i++) { tableName[i]=table.Rows[i][0].ToString(); } //switch to using the new database DataConnection newDcon=new DataConnection(newDb); for(int i=0;i<tableName.Length;i++) { command="SHOW CREATE TABLE "+oldDb+"."+tableName[i];//also works with views. table=newDcon.GetTable(command); command=PIn.ByteArray(table.Rows[0][1]); newDcon.NonQ(command);//this has to be run using connection with new database command="INSERT INTO "+newDb+"."+tableName[i] +" SELECT * FROM "+oldDb+"."+tableName[i]; newDcon.NonQ(command); } return 0; }
///<summary>Only used if using the server component. This is used for queries written by the user. It uses the user with lower privileges to prevent injection attack.</summary> public static DataTable GetTableLow(string command) { DataConnection dcon=new DataConnection(true); DataTable table=dcon.GetTable(command); //DataSet retVal=new DataSet(); //retVal.Tables.Add(table); return table; }
///<summary></summary> public static DataTable GetTable(string command) { DataConnection dcon=new DataConnection(); DataTable table=dcon.GetTable(command); //DataSet retVal=new DataSet(); //table.TableName="table"; //retVal.Tables.Add(table); //retVal.Tables[0].TableName=""; return table;//retVal; }
///<summary>For a given date, gets a list of dateTimes of missed calls. Gets directly from the Asterisk database, hard-coded.</summary> public static List<DateTime> GetMissedCalls(DateTime date) { DataConnection dcon=new DataConnection("192.168.0.197","asteriskcdrdb","opendental","secret",DatabaseType.MySql); string command="SELECT calldate FROM cdr WHERE "+DbHelper.DateColumn("calldate")+" = "+POut.Date(date)+" " +"AND (dcontext='ext-group' OR dcontext='ext-local') AND dst='vmu998'"; List<DateTime> retVal=new List<DateTime>(); DataTable table=dcon.GetTable(command); for(int i=0;i<table.Rows.Count;i++) { retVal.Add(PIn.DateT(table.Rows[i][0].ToString())); } return retVal; }
public static void SetRingGroups(int extension,AsteriskRingGroups ringGroups) { DataConnection dcon=new DataConnection(ipAddressAsterisk,"asterisk","opendental","secret",DatabaseType.MySql); string command="SELECT grpnum,grplist FROM ringgroups WHERE grpnum = '601' OR grpnum = '609'"; DataTable table=null; try { table=dcon.GetTable(command); } catch {//if remotely connecting from home return; } string rawExtensions601=""; string rawExtensions609=""; string[] arrayExtensions601=new string[0]; string[] arrayExtensions609=new string[0]; for(int i=0;i<table.Rows.Count;i++) { if(table.Rows[i]["grpnum"].ToString()=="601") {//there should always be exactly one rawExtensions601=table.Rows[i]["grplist"].ToString(); arrayExtensions601=rawExtensions601.Split(new char[] { '-' },StringSplitOptions.RemoveEmptyEntries); } if(table.Rows[i]["grpnum"].ToString()=="609") {//there should always be exactly one rawExtensions609=table.Rows[i]["grplist"].ToString(); arrayExtensions609=rawExtensions609.Split(new char[] { '-' },StringSplitOptions.RemoveEmptyEntries); } } List<string> listExtension601=new List<string>(); bool isIn601=false; for(int i=0;i<arrayExtensions601.Length;i++){ //we won't test to make sure each item is a pure number. listExtension601.Add(arrayExtensions601[i]); if(arrayExtensions601[i]==extension.ToString()) { isIn601=true; } } List<string> listExtension609=new List<string>(); bool isIn609=false; for(int i=0;i<arrayExtensions609.Length;i++) { //we won't test to make sure each item is a pure number. listExtension609.Add(arrayExtensions609[i]); if(arrayExtensions609[i]==extension.ToString()) { isIn609=true; } } if(ringGroups==AsteriskRingGroups.All) { if(!isIn601) { AddToRingGroup("601",extension.ToString(),rawExtensions601); } if(!isIn609) { AddToRingGroup("609",extension.ToString(),rawExtensions609); } } if(ringGroups==AsteriskRingGroups.None) { if(isIn601) { RemoveFromRingGroup("601",extension.ToString(),listExtension601,rawExtensions601); } if(isIn609) { RemoveFromRingGroup("609",extension.ToString(),listExtension609,rawExtensions609); } } if(ringGroups==AsteriskRingGroups.Backup) { if(isIn601) { RemoveFromRingGroup("601",extension.ToString(),listExtension601,rawExtensions601); } if(!isIn609) { AddToRingGroup("609",extension.ToString(),rawExtensions609); } } Signalods.SetInvalid(InvalidType.PhoneAsteriskReload); }
public static DataTable GetTreeListTableForPatient(string patNum){ if(RemotingClient.RemotingRole==RemotingRole.ClientWeb) { return Meth.GetTable(MethodBase.GetCurrentMethod(),patNum); } DataConnection dcon=new DataConnection(); DataTable table=new DataTable("DocumentList"); DataRow row; DataTable raw; string command; //Rows are first added to the resultSet list so they can be sorted at the end as a larger group, then //they are placed in the datatable to be returned. List<Object> resultSet=new List<Object>(); //columns that start with lowercase are altered for display rather than being raw data. table.Columns.Add("DocNum"); table.Columns.Add("MountNum"); table.Columns.Add("DocCategory"); table.Columns.Add("DateCreated"); table.Columns.Add("docFolder");//The folder order to which the Document category corresponds. table.Columns.Add("description"); table.Columns.Add("ImgType"); //Move all documents which are invisible to the first document category. command="SELECT DocNum FROM document WHERE PatNum='"+patNum+"' AND " +"DocCategory<0"; raw=dcon.GetTable(command); if(raw.Rows.Count>0){//Are there any invisible documents? command="UPDATE document SET DocCategory='"+DefC.GetList(DefCat.ImageCats)[0].DefNum +"' WHERE PatNum='"+patNum+"' AND ("; for(int i=0;i<raw.Rows.Count;i++){ command+="DocNum='"+PIn.Long(raw.Rows[i]["DocNum"].ToString())+"' "; if(i<raw.Rows.Count-1){ command+="OR "; } } command+=")"; dcon.NonQ(command); } //Load all documents into the result table. command="SELECT DocNum,DocCategory,DateCreated,Description,ImgType,MountItemNum FROM document WHERE PatNum='"+patNum+"'"; raw=dcon.GetTable(command); for(int i=0;i<raw.Rows.Count;i++){ //Make sure hidden documents are never added (there is a small possibility that one is added after all are made visible). if(DefC.GetOrder(DefCat.ImageCats,PIn.Long(raw.Rows[i]["DocCategory"].ToString()))<0){ continue; } //Do not add individual documents which are part of a mount object. if(PIn.Long(raw.Rows[i]["MountItemNum"].ToString())!=0) { continue; } row=table.NewRow(); row["DocNum"]=PIn.Long(raw.Rows[i]["DocNum"].ToString()); row["MountNum"]=0; row["DocCategory"]=PIn.Long(raw.Rows[i]["DocCategory"].ToString()); row["DateCreated"]=PIn.Date(raw.Rows[i]["DateCreated"].ToString()); row["docFolder"]=DefC.GetOrder(DefCat.ImageCats,PIn.Long(raw.Rows[i]["DocCategory"].ToString())); row["description"]=PIn.Date(raw.Rows[i]["DateCreated"].ToString()).ToString("d")+": " +PIn.String(raw.Rows[i]["Description"].ToString()); row["ImgType"]=PIn.Long(raw.Rows[i]["ImgType"].ToString()); resultSet.Add(row); } //Move all mounts which are invisible to the first document category. command="SELECT MountNum FROM mount WHERE PatNum='"+patNum+"' AND " +"DocCategory<0"; raw=dcon.GetTable(command); if(raw.Rows.Count>0) {//Are there any invisible mounts? command="UPDATE mount SET DocCategory='"+DefC.GetList(DefCat.ImageCats)[0].DefNum +"' WHERE PatNum='"+patNum+"' AND ("; for(int i=0;i<raw.Rows.Count;i++) { command+="MountNum='"+PIn.Long(raw.Rows[i]["MountNum"].ToString())+"' "; if(i<raw.Rows.Count-1) { command+="OR "; } } command+=")"; dcon.NonQ(command); } //Load all mounts into the result table. command="SELECT MountNum,DocCategory,DateCreated,Description,ImgType FROM mount WHERE PatNum='"+patNum+"'"; raw=dcon.GetTable(command); for(int i=0;i<raw.Rows.Count;i++){ //Make sure hidden mounts are never added (there is a small possibility that one is added after all are made visible). if(DefC.GetOrder(DefCat.ImageCats,PIn.Long(raw.Rows[i]["DocCategory"].ToString()))<0) { continue; } row=table.NewRow(); row["DocNum"]=0; row["MountNum"]=PIn.Long(raw.Rows[i]["MountNum"].ToString()); row["DocCategory"]=PIn.Long(raw.Rows[i]["DocCategory"].ToString()); row["DateCreated"]=PIn.Date(raw.Rows[i]["DateCreated"].ToString()); row["docFolder"]=DefC.GetOrder(DefCat.ImageCats,PIn.Long(raw.Rows[i]["DocCategory"].ToString())); row["description"]=PIn.Date(raw.Rows[i]["DateCreated"].ToString()).ToString("d")+": " +PIn.String(raw.Rows[i]["Description"].ToString()); row["ImgType"]=PIn.Long(raw.Rows[i]["ImgType"].ToString()); resultSet.Add(row); } //We must sort the results after they are returned from the database, because the database software (i.e. MySQL) //cannot return sorted results from two or more result sets like we have here. resultSet.Sort(delegate(Object o1,Object o2) { DataRow r1=(DataRow)o1; DataRow r2=(DataRow)o2; int docFolder1=Convert.ToInt32(r1["docFolder"].ToString()); int docFolder2=Convert.ToInt32(r2["docFolder"].ToString()); if(docFolder1<docFolder2){ return -1; }else if(docFolder1>docFolder2){ return 1; } return PIn.Date(r1["DateCreated"].ToString()).CompareTo(PIn.Date(r2["DateCreated"].ToString())); }); //Finally, move the results from the list into a data table. for(int i=0;i<resultSet.Count;i++){ table.Rows.Add((DataRow)resultSet[i]); } return table; }
private void butSynch_Click(object sender,EventArgs e) { if(textUsername.Text=="") { MsgBox.Show(this,"Please enter a username first."); return; } if(ReplicationServers.Listt.Count==0) { MsgBox.Show(this,"Please add at servers to the list first"); return; } Cursor=Cursors.WaitCursor; string currentDatabaseName=MiscData.GetCurrentDatabase(); for(int i=0;i<ReplicationServers.Listt.Count;i++) { string compName=ReplicationServers.Listt[i].Descript; DataConnection dc=new DataConnection(); try { //try { dc.SetDb(compName,currentDatabaseName,textUsername.Text,textPassword.Text,"","",DataConnection.DBtype); //} //catch(MySql.Data.MySqlClient.MySqlException ex) { // if(ex.Number==1042) {//The error 1042 is issued when the connection could not be made. // throw ex;//Pass the exception along. // } // DataConnection.cmd.Connection.Close(); //} //Connection is considered to be successfull at this point. Now restart the slave process to force replication. string command="SLAVE STOP; START SLAVE; SHOW SLAVE STATUS;"; DataTable slaveStatus=dc.GetTable(command); //Wait for the slave process to become active again. for(int j=0;j<40 && slaveStatus.Rows[0]["Slave_IO_Running"].ToString().ToLower()!="yes";j++) { Thread.Sleep(1000); command="SHOW SLAVE STATUS"; slaveStatus=dc.GetTable(command); } if(slaveStatus.Rows[0]["Slave_IO_Running"].ToString().ToLower()!="yes") { throw new Exception("Slave IO is not running on computer "+compName); } if(slaveStatus.Rows[0]["Slave_SQL_Running"].ToString().ToLower()!="yes") { throw new Exception("Slave SQL is not running on computer "+compName); } //Wait for replication to complete. while(slaveStatus.Rows[0]["Slave_IO_State"].ToString().ToLower()!="waiting for master to send event" || slaveStatus.Rows[0]["Seconds_Behind_Master"].ToString()!="0") { slaveStatus=dc.GetTable(command); } } catch(Exception ex) { Cursor=Cursors.Default; MessageBox.Show(Lan.g(this,"Error forcing replication on computer")+" "+compName+": "+ex.Message); return;//Cancel operation. } } Cursor=Cursors.Default; MessageBox.Show(Lan.g(this,"Database synch completed successfully.")); }
public static DataTable GetPlannedApt(long patNum) { if(RemotingClient.RemotingRole==RemotingRole.ClientWeb) { return Meth.GetTable(MethodBase.GetCurrentMethod(),patNum); } DataConnection dcon=new DataConnection(); DataTable table=new DataTable("Planned"); DataRow row; //columns that start with lowercase are altered for display rather than being raw data. table.Columns.Add("AptNum"); table.Columns.Add("colorBackG"); table.Columns.Add("colorText"); table.Columns.Add("dateSched"); table.Columns.Add("ItemOrder"); table.Columns.Add("minutes"); table.Columns.Add("Note"); table.Columns.Add("ProcDescript"); table.Columns.Add("PlannedApptNum"); //but we won't actually fill this table with rows until the very end. It's more useful to use a List<> for now. List<DataRow> rows=new List<DataRow>(); //The query below was causing a max join error for big offices. It's fixed now, //but a better option for next time would be to put SET SQL_BIG_SELECTS=1; before the query. string command="SELECT plannedappt.AptNum,ItemOrder,PlannedApptNum,appointment.AptDateTime," +"appointment.Pattern,appointment.AptStatus,"//COUNT(procedurelog.ProcNum) someAreComplete "//The count won't be accurate, but it will tell us if not zero. +"(SELECT COUNT(*) FROM procedurelog WHERE procedurelog.PlannedAptNum=plannedappt.AptNum AND procedurelog.ProcStatus=2) someAreComplete " +"FROM plannedappt " +"LEFT JOIN appointment ON appointment.NextAptNum=plannedappt.AptNum " //+"LEFT JOIN procedurelog ON procedurelog.PlannedAptNum=plannedappt.AptNum "//grab all attached completed procs //+"AND procedurelog.ProcStatus=2 " +"WHERE plannedappt.PatNum="+POut.Long(patNum)+" " +"GROUP BY plannedappt.AptNum,ItemOrder,PlannedApptNum,appointment.AptDateTime," +"appointment.Pattern,appointment.AptStatus " +"ORDER BY ItemOrder"; //plannedappt.AptNum does refer to the planned appt, but the other fields in the result are for the linked scheduled appt. DataTable rawPlannedAppts=dcon.GetTable(command); DataRow aptRow; int itemOrder=1; DateTime dateSched; ApptStatus aptStatus; for(int i=0;i<rawPlannedAppts.Rows.Count;i++) { aptRow=null; for(int a=0;a<rawApt.Rows.Count;a++) { if(rawApt.Rows[a]["AptNum"].ToString()==rawPlannedAppts.Rows[i]["AptNum"].ToString()) { aptRow=rawApt.Rows[a]; break; } } if(aptRow==null) { continue;//this will have to be fixed in dbmaint. } //repair any item orders here rather than in dbmaint. It's really fast. if(itemOrder.ToString()!=rawPlannedAppts.Rows[i]["ItemOrder"].ToString()) { command="UPDATE plannedappt SET ItemOrder="+POut.Long(itemOrder) +" WHERE PlannedApptNum="+rawPlannedAppts.Rows[i]["PlannedApptNum"].ToString(); dcon.NonQ(command); } //end of repair row=table.NewRow(); row["AptNum"]=aptRow["AptNum"].ToString(); dateSched=PIn.Date(rawPlannedAppts.Rows[i]["AptDateTime"].ToString()); //Colors---------------------------------------------------------------------------- aptStatus=(ApptStatus)PIn.Long(rawPlannedAppts.Rows[i]["AptStatus"].ToString()); //change color if completed, broken, or unscheduled no matter the date if(aptStatus==ApptStatus.Broken || aptStatus==ApptStatus.UnschedList) { row["colorBackG"]=DefC.Long[(int)DefCat.ProgNoteColors][15].ItemColor.ToArgb().ToString(); row["colorText"]=DefC.Long[(int)DefCat.ProgNoteColors][14].ItemColor.ToArgb().ToString(); } else if(aptStatus==ApptStatus.Complete) { row["colorBackG"]=DefC.Long[(int)DefCat.ProgNoteColors][11].ItemColor.ToArgb().ToString(); row["colorText"]=DefC.Long[(int)DefCat.ProgNoteColors][10].ItemColor.ToArgb().ToString(); } else if(aptStatus==ApptStatus.Scheduled && dateSched.Date!=DateTime.Today.Date) { row["colorBackG"]=DefC.Long[(int)DefCat.ProgNoteColors][13].ItemColor.ToArgb().ToString(); row["colorText"]=DefC.Long[(int)DefCat.ProgNoteColors][12].ItemColor.ToArgb().ToString(); } else if(dateSched.Date<DateTime.Today && dateSched!=DateTime.MinValue) {//Past row["colorBackG"]=DefC.Long[(int)DefCat.ProgNoteColors][11].ItemColor.ToArgb().ToString(); row["colorText"]=DefC.Long[(int)DefCat.ProgNoteColors][10].ItemColor.ToArgb().ToString(); } else if(dateSched.Date == DateTime.Today.Date) { //Today row["colorBackG"]=DefC.Long[(int)DefCat.ProgNoteColors][9].ItemColor.ToArgb().ToString(); row["colorText"]=DefC.Long[(int)DefCat.ProgNoteColors][8].ItemColor.ToArgb().ToString(); } else if(dateSched.Date > DateTime.Today) { //Future row["colorBackG"]=DefC.Long[(int)DefCat.ProgNoteColors][13].ItemColor.ToArgb().ToString(); row["colorText"]=DefC.Long[(int)DefCat.ProgNoteColors][12].ItemColor.ToArgb().ToString(); } else { row["colorBackG"]=Color.White.ToArgb().ToString(); row["colorText"]=Color.Black.ToArgb().ToString(); } //end of colors------------------------------------------------------------------------------ if(dateSched.Year<1880) { row["dateSched"]=""; } else { row["dateSched"]=dateSched.ToShortDateString(); } row["ItemOrder"]=itemOrder.ToString(); row["minutes"]=(aptRow["Pattern"].ToString().Length*5).ToString(); row["Note"]=aptRow["Note"].ToString(); row["PlannedApptNum"]=rawPlannedAppts.Rows[i]["PlannedApptNum"].ToString(); row["ProcDescript"]=aptRow["ProcDescript"].ToString(); if(aptStatus==ApptStatus.Complete) { row["ProcDescript"]=Lans.g("ContrChart","(Completed) ")+ row["ProcDescript"]; } else if(dateSched == DateTime.Today.Date) { row["ProcDescript"]=Lans.g("ContrChart","(Today's) ")+ row["ProcDescript"]; } else if(rawPlannedAppts.Rows[i]["someAreComplete"].ToString()!="0"){ row["ProcDescript"]=Lans.g("ContrChart","(Some procs complete) ")+ row["ProcDescript"]; } rows.Add(row); itemOrder++; } for(int i=0;i<rows.Count;i++) { table.Rows.Add(rows[i]); } return table; }
public static DataTable GetProgNotes(long patNum, bool isAuditMode,ChartModuleComponentsToLoad componentsToLoad) { if(RemotingClient.RemotingRole==RemotingRole.ClientWeb) { return Meth.GetTable(MethodBase.GetCurrentMethod(),componentsToLoad); } DataConnection dcon=new DataConnection(); DataTable table=new DataTable("ProgNotes"); DataRow row; //columns that start with lowercase are altered for display rather than being raw data. table.Columns.Add("aptDateTime",typeof(DateTime)); table.Columns.Add("AbbrDesc"); table.Columns.Add("AptNum"); table.Columns.Add("clinic"); table.Columns.Add("CodeNum"); table.Columns.Add("colorBackG"); table.Columns.Add("colorText"); table.Columns.Add("CommlogNum"); table.Columns.Add("dateEntryC"); table.Columns.Add("dateTP"); table.Columns.Add("description"); table.Columns.Add("dx"); table.Columns.Add("Dx"); table.Columns.Add("EmailMessageNum"); table.Columns.Add("FormPatNum"); table.Columns.Add("HideGraphics"); table.Columns.Add("isLocked"); table.Columns.Add("length"); table.Columns.Add("LabCaseNum"); table.Columns.Add("note"); table.Columns.Add("orionDateScheduleBy"); table.Columns.Add("orionDateStopClock"); table.Columns.Add("orionDPC"); table.Columns.Add("orionDPCpost"); table.Columns.Add("orionIsEffectiveComm"); table.Columns.Add("orionIsOnCall"); table.Columns.Add("orionStatus2"); table.Columns.Add("PatNum");//only used for Commlog and Task table.Columns.Add("Priority");//for sorting table.Columns.Add("priority"); table.Columns.Add("ProcCode"); table.Columns.Add("procDate"); table.Columns.Add("ProcDate",typeof(DateTime)); table.Columns.Add("procFee"); table.Columns.Add("ProcNum"); table.Columns.Add("ProcNumLab"); table.Columns.Add("procStatus"); table.Columns.Add("ProcStatus"); table.Columns.Add("procTime"); table.Columns.Add("procTimeEnd"); table.Columns.Add("prognosis"); table.Columns.Add("prov"); table.Columns.Add("quadrant"); table.Columns.Add("RxNum"); table.Columns.Add("SheetNum"); table.Columns.Add("signature"); table.Columns.Add("Surf"); table.Columns.Add("surf"); table.Columns.Add("TaskNum"); table.Columns.Add("toothNum"); table.Columns.Add("ToothNum"); table.Columns.Add("ToothRange"); table.Columns.Add("user"); //table.Columns.Add(""); //but we won't actually fill this table with rows until the very end. It's more useful to use a List<> for now. List<DataRow> rows=new List<DataRow>(); string command; DateTime dateT; string txt; List<DataRow> labRows=new List<DataRow>();//Canadian lab procs, which must be added in a loop at the very end. if(componentsToLoad.ShowTreatPlan || componentsToLoad.ShowCompleted || componentsToLoad.ShowExisting || componentsToLoad.ShowReferred || componentsToLoad.ShowConditions){ #region Procedures command="SELECT provider.Abbr,procedurecode.AbbrDesc,appointment.AptDateTime,procedurelog.BaseUnits,procedurelog.ClinicNum," +"procedurelog.CodeNum,procedurelog.DateEntryC,orionproc.DateScheduleBy,orionproc.DateStopClock,procedurelog.DateTP," +"procedurecode.Descript,orionproc.DPC,orionproc.DPCpost,Dx,HideGraphics,orionproc.IsEffectiveComm,IsLocked,orionproc.IsOnCall," +"LaymanTerm,Priority,procedurecode.ProcCode,ProcDate,ProcFee,procedurelog.ProcNum,ProcNumLab,procedurelog.ProcTime," +"procedurelog.ProcTimeEnd,procedurelog.Prognosis,ProcStatus,orionproc.Status2,Surf,ToothNum,ToothRange,UnitQty " +"FROM procedurelog " +"LEFT JOIN procedurecode ON procedurecode.CodeNum=procedurelog.CodeNum " +"LEFT JOIN provider ON provider.ProvNum=procedurelog.ProvNum " +"LEFT JOIN orionproc ON procedurelog.ProcNum=orionproc.ProcNum " +"LEFT JOIN appointment ON appointment.AptNum=procedurelog.AptNum " +"AND (appointment.AptStatus="+POut.Long((int)ApptStatus.Scheduled) +" OR appointment.AptStatus="+POut.Long((int)ApptStatus.ASAP) +" OR appointment.AptStatus="+POut.Long((int)ApptStatus.Broken) +" OR appointment.AptStatus="+POut.Long((int)ApptStatus.Complete) +") WHERE procedurelog.PatNum="+POut.Long(patNum); if(!isAuditMode) {//regular mode command+=" AND (ProcStatus !=6"//not deleted +" OR IsLocked=1)";//Any locked proc should show. This forces invalidated (deleted locked) procs to show. } command+=" ORDER BY ProcDate";//we'll just have to reorder it anyway DataTable rawProcs=dcon.GetTable(command); command="SELECT ProcNum,EntryDateTime,UserNum,Note," +"CASE WHEN Signature!='' THEN 1 ELSE 0 END AS SigPresent " +"FROM procnote WHERE PatNum="+POut.Long(patNum) +" ORDER BY EntryDateTime";// but this helps when looping for notes DataTable rawNotes=dcon.GetTable(command); for(int i=0;i<rawProcs.Rows.Count;i++) { row=table.NewRow(); row["AbbrDesc"]=rawProcs.Rows[i]["AbbrDesc"].ToString(); row["aptDateTime"]=PIn.DateT(rawProcs.Rows[i]["AptDateTime"].ToString()); row["AptNum"]=0; row["clinic"]=Clinics.GetDesc(PIn.Long(rawProcs.Rows[i]["ClinicNum"].ToString())); row["CodeNum"]=rawProcs.Rows[i]["CodeNum"].ToString(); row["colorBackG"]=Color.White.ToArgb(); if(((DateTime)row["aptDateTime"]).Date==DateTime.Today) { row["colorBackG"]=DefC.Long[(int)DefCat.MiscColors][6].ItemColor.ToArgb().ToString(); } switch((ProcStat)PIn.Long(rawProcs.Rows[i]["ProcStatus"].ToString())) { case ProcStat.TP: row["colorText"]=DefC.Long[(int)DefCat.ProgNoteColors][0].ItemColor.ToArgb().ToString(); break; case ProcStat.C: row["colorText"]=DefC.Long[(int)DefCat.ProgNoteColors][1].ItemColor.ToArgb().ToString(); break; case ProcStat.EC: row["colorText"]=DefC.Long[(int)DefCat.ProgNoteColors][2].ItemColor.ToArgb().ToString(); break; case ProcStat.EO: row["colorText"]=DefC.Long[(int)DefCat.ProgNoteColors][3].ItemColor.ToArgb().ToString(); break; case ProcStat.R: row["colorText"]=DefC.Long[(int)DefCat.ProgNoteColors][4].ItemColor.ToArgb().ToString(); break; case ProcStat.D: row["colorText"]=Color.Black.ToArgb().ToString(); break; case ProcStat.Cn: row["colorText"]=DefC.Long[(int)DefCat.ProgNoteColors][22].ItemColor.ToArgb().ToString(); break; } row["CommlogNum"]=0; dateT=PIn.DateT(rawProcs.Rows[i]["DateEntryC"].ToString()); if(dateT.Year<1880) { row["dateEntryC"]=""; } else { row["dateEntryC"]=dateT.ToString(Lans.GetShortDateTimeFormat()); } dateT=PIn.DateT(rawProcs.Rows[i]["DateTP"].ToString()); if(dateT.Year<1880) { row["dateTP"]=""; } else { row["dateTP"]=dateT.ToString(Lans.GetShortDateTimeFormat()); } if(rawProcs.Rows[i]["LaymanTerm"].ToString()=="") { row["description"]=rawProcs.Rows[i]["Descript"].ToString(); } else { row["description"]=rawProcs.Rows[i]["LaymanTerm"].ToString(); } if(rawProcs.Rows[i]["ToothRange"].ToString()!="") { row["description"]+=" #"+Tooth.FormatRangeForDisplay(rawProcs.Rows[i]["ToothRange"].ToString()); } row["dx"]=DefC.GetValue(DefCat.Diagnosis,PIn.Long(rawProcs.Rows[i]["Dx"].ToString())); row["Dx"]=rawProcs.Rows[i]["Dx"].ToString(); row["EmailMessageNum"]=0; row["FormPatNum"]=0; row["HideGraphics"]=rawProcs.Rows[i]["HideGraphics"].ToString(); row["isLocked"]=PIn.Bool(rawProcs.Rows[i]["isLocked"].ToString())?"X":""; row["LabCaseNum"]=0; row["length"]=""; row["signature"]=""; row["user"]=""; if(componentsToLoad.ShowProcNotes) { #region note----------------------------------------------------------------------------------------------------------- row["note"]=""; dateT=PIn.DateT(rawProcs.Rows[i]["DateScheduleBy"].ToString()); if(dateT.Year<1880) { row["orionDateScheduleBy"]=""; } else { row["orionDateScheduleBy"]=dateT.ToString(Lans.GetShortDateTimeFormat()); } dateT=PIn.DateT(rawProcs.Rows[i]["DateStopClock"].ToString()); if(dateT.Year<1880) { row["orionDateStopClock"]=""; } else { row["orionDateStopClock"]=dateT.ToString(Lans.GetShortDateTimeFormat()); } if(((OrionDPC)PIn.Int(rawProcs.Rows[i]["DPC"].ToString())).ToString()=="NotSpecified") { row["orionDPC"]=""; } else { row["orionDPC"]=((OrionDPC)PIn.Int(rawProcs.Rows[i]["DPC"].ToString())).ToString(); } if(((OrionDPC)PIn.Int(rawProcs.Rows[i]["DPCpost"].ToString())).ToString()=="NotSpecified") { row["orionDPCpost"]=""; } else { row["orionDPCpost"]=((OrionDPC)PIn.Int(rawProcs.Rows[i]["DPCpost"].ToString())).ToString(); } row["orionIsEffectiveComm"]=""; if(rawProcs.Rows[i]["IsEffectiveComm"].ToString()=="1") { row["orionIsEffectiveComm"]="Y"; } else if(rawProcs.Rows[i]["IsEffectiveComm"].ToString()=="0") { row["orionIsEffectiveComm"]=""; } row["orionIsOnCall"]=""; if(rawProcs.Rows[i]["IsOnCall"].ToString()=="1") { row["orionIsOnCall"]="Y"; } else if(rawProcs.Rows[i]["IsOnCall"].ToString()=="0") { row["orionIsOnCall"]=""; } row["orionStatus2"]=((OrionStatus)PIn.Int(rawProcs.Rows[i]["Status2"].ToString())).ToString(); if(isAuditMode) {//we will include all notes for each proc. We will concat and make readable. for(int n=0;n<rawNotes.Rows.Count;n++) {//loop through each note if(rawProcs.Rows[i]["ProcNum"].ToString() != rawNotes.Rows[n]["ProcNum"].ToString()) { continue; } if(row["note"].ToString()!="") {//if there is an existing note row["note"]+="\r\n------------------------------------------------------\r\n";//start a new line } row["note"]+=PIn.DateT(rawNotes.Rows[n]["EntryDateTime"].ToString()).ToString(); row["note"]+=" "+Userods.GetName(PIn.Long(rawNotes.Rows[n]["UserNum"].ToString())); if(rawNotes.Rows[n]["SigPresent"].ToString()=="1") { row["note"]+=" "+Lans.g("ChartModule","(signed)"); } row["note"]+="\r\n"+rawNotes.Rows[n]["Note"].ToString(); } } else {//Not audit mode. We just want the most recent note for(int n=rawNotes.Rows.Count-1;n>=0;n--) {//loop through each note, backwards. if(rawProcs.Rows[i]["ProcNum"].ToString() != rawNotes.Rows[n]["ProcNum"].ToString()) { continue; } row["note"]=rawNotes.Rows[n]["Note"].ToString(); break;//out of note loop. } } #endregion Note } //This section is closely related to notes, but must be filled for all procedures regardless of whether showing the actual note. if(!isAuditMode) {//Audit mode is handled above by putting this info into the note section itself. for(int n=rawNotes.Rows.Count-1;n>=0;n--) {//Loop through each note; backwards to get most recent note. if(rawProcs.Rows[i]["ProcNum"].ToString() != rawNotes.Rows[n]["ProcNum"].ToString()) { continue; } row["user"]=Userods.GetName(PIn.Long(rawNotes.Rows[n]["UserNum"].ToString())); if(rawNotes.Rows[n]["SigPresent"].ToString()=="1") { row["signature"]=Lans.g("ChartModule","Signed"); } else { row["signature"]=""; } break; } } row["PatNum"]=""; row["Priority"]=rawProcs.Rows[i]["Priority"].ToString(); row["priority"]=DefC.GetName(DefCat.TxPriorities,PIn.Long(rawProcs.Rows[i]["Priority"].ToString())); row["ProcCode"]=rawProcs.Rows[i]["ProcCode"].ToString(); dateT=PIn.DateT(rawProcs.Rows[i]["ProcDate"].ToString()); if(dateT.Year<1880) { row["procDate"]=""; } else { row["procDate"]=dateT.ToString(Lans.GetShortDateTimeFormat()); } row["ProcDate"]=dateT; double amt = PIn.Double(rawProcs.Rows[i]["ProcFee"].ToString()); int qty = PIn.Int(rawProcs.Rows[i]["UnitQty"].ToString()) + PIn.Int(rawProcs.Rows[i]["BaseUnits"].ToString()); if(qty>0) { amt *= qty; } row["procFee"]=amt.ToString("F"); row["ProcNum"]=rawProcs.Rows[i]["ProcNum"].ToString(); row["ProcNumLab"]=rawProcs.Rows[i]["ProcNumLab"].ToString(); row["procStatus"]=Lans.g("enumProcStat",((ProcStat)PIn.Long(rawProcs.Rows[i]["ProcStatus"].ToString())).ToString()); if(row["procStatus"].ToString()=="D") { if(row["isLocked"].ToString()=="X") { row["procStatus"]="I"; row["description"]=Lans.g("ChartModule","-invalid-")+" "+row["description"].ToString(); } } row["ProcStatus"]=rawProcs.Rows[i]["ProcStatus"].ToString(); row["procTime"]=""; dateT=PIn.DateT(rawProcs.Rows[i]["ProcTime"].ToString()); if(dateT.TimeOfDay!=TimeSpan.Zero) { row["procTime"]=dateT.ToString("h:mm")+dateT.ToString("%t").ToLower(); } row["procTimeEnd"]=""; dateT=PIn.DateT(rawProcs.Rows[i]["ProcTimeEnd"].ToString()); if(dateT.TimeOfDay!=TimeSpan.Zero) { row["procTimeEnd"]=dateT.ToString("h:mm")+dateT.ToString("%t").ToLower(); } row["prognosis"]=DefC.GetName(DefCat.Prognosis,PIn.Long(rawProcs.Rows[i]["Prognosis"].ToString())); row["prov"]=rawProcs.Rows[i]["Abbr"].ToString(); row["quadrant"]=""; if(ProcedureCodes.GetProcCode(PIn.Long(row["CodeNum"].ToString())).TreatArea==TreatmentArea.Tooth) { row["quadrant"]=Tooth.GetQuadrant(rawProcs.Rows[i]["ToothNum"].ToString()); } else if(ProcedureCodes.GetProcCode(PIn.Long(row["CodeNum"].ToString())).TreatArea==TreatmentArea.Surf) { row["quadrant"]=Tooth.GetQuadrant(rawProcs.Rows[i]["ToothNum"].ToString()); } else if(ProcedureCodes.GetProcCode(PIn.Long(row["CodeNum"].ToString())).TreatArea==TreatmentArea.Quad) { row["quadrant"]=rawProcs.Rows[i]["Surf"].ToString(); } else if(ProcedureCodes.GetProcCode(PIn.Long(row["CodeNum"].ToString())).TreatArea==TreatmentArea.ToothRange) { string[] toothNum=rawProcs.Rows[i]["ToothRange"].ToString().Split(','); bool sameQuad=false;//Don't want true if length==0. for(int n=0;n<toothNum.Length;n++) {//But want true if length==1 (check index 0 against itself). if(Tooth.GetQuadrant(toothNum[n])==Tooth.GetQuadrant(toothNum[0])) { sameQuad=true; } else { sameQuad=false; break; } } if(sameQuad) { row["quadrant"]=Tooth.GetQuadrant(toothNum[0]); } } row["RxNum"]=0; row["SheetNum"]=0; row["Surf"]=rawProcs.Rows[i]["Surf"].ToString(); if(ProcedureCodes.GetProcCode(PIn.Long(row["CodeNum"].ToString())).TreatArea==TreatmentArea.Surf) { row["surf"]=Tooth.SurfTidyFromDbToDisplay(rawProcs.Rows[i]["Surf"].ToString(),rawProcs.Rows[i]["ToothNum"].ToString()); } else { row["surf"]=rawProcs.Rows[i]["Surf"].ToString(); } row["TaskNum"]=0; row["toothNum"]=Tooth.GetToothLabel(rawProcs.Rows[i]["ToothNum"].ToString()); row["ToothNum"]=rawProcs.Rows[i]["ToothNum"].ToString(); row["ToothRange"]=rawProcs.Rows[i]["ToothRange"].ToString(); if(rawProcs.Rows[i]["ProcNumLab"].ToString()=="0") {//normal proc rows.Add(row); } else { row["description"]="^ ^ "+row["description"].ToString(); labRows.Add(row);//these will be added in the loop at the end } } #endregion Procedures } if(componentsToLoad.ShowCommLog) {//TODO: refine to use show Family #region Commlog command="SELECT CommlogNum,CommDateTime,commlog.DateTimeEnd,CommType,Note,commlog.PatNum,UserNum,p1.FName," +"CASE WHEN Signature!='' THEN 1 ELSE 0 END SigPresent " +"FROM patient p1,patient p2,commlog " +"WHERE commlog.PatNum=p1.PatNum " +"AND p1.Guarantor=p2.Guarantor " +"AND p2.PatNum="+POut.Long(patNum) +" ORDER BY CommDateTime"; DataTable rawComm=dcon.GetTable(command); for(int i=0;i<rawComm.Rows.Count;i++) { row=table.NewRow(); row["AbbrDesc"]=""; row["aptDateTime"]=DateTime.MinValue; row["AptNum"]=0; row["clinic"]=""; row["CodeNum"]=""; row["colorBackG"]=Color.White.ToArgb(); row["colorText"]=DefC.Long[(int)DefCat.ProgNoteColors][6].ItemColor.ToArgb().ToString(); row["CommlogNum"]=rawComm.Rows[i]["CommlogNum"].ToString(); row["dateEntryC"]=""; row["dateTP"]=""; if(rawComm.Rows[i]["PatNum"].ToString()==patNum.ToString()) { txt=""; } else { txt="("+rawComm.Rows[i]["FName"].ToString()+") "; } row["description"]=txt+Lans.g("ChartModule","Comm - ") +DefC.GetName(DefCat.CommLogTypes,PIn.Long(rawComm.Rows[i]["CommType"].ToString())); row["dx"]=""; row["Dx"]=""; row["EmailMessageNum"]=0; row["FormPatNum"]=0; row["HideGraphics"]=""; row["isLocked"]=""; row["LabCaseNum"]=0; row["length"]=""; if(PIn.DateT(rawComm.Rows[i]["DateTimeEnd"].ToString()).Year>1880) { DateTime startTime=PIn.DateT(rawComm.Rows[i]["CommDateTime"].ToString()); DateTime endTime=PIn.DateT(rawComm.Rows[i]["DateTimeEnd"].ToString()); row["length"]=(endTime-startTime).ToStringHmm(); } row["note"]=rawComm.Rows[i]["Note"].ToString(); row["orionDateScheduleBy"]=""; row["orionDateStopClock"]=""; row["orionDPC"]=""; row["orionDPCpost"]=""; row["orionIsEffectiveComm"]=""; row["orionIsOnCall"]=""; row["orionStatus2"]=""; row["PatNum"]=rawComm.Rows[i]["PatNum"].ToString(); row["Priority"]=""; row["priority"]=""; row["ProcCode"]=""; dateT=PIn.DateT(rawComm.Rows[i]["CommDateTime"].ToString()); if(dateT.Year<1880) { row["procDate"]=""; } else { row["procDate"]=dateT.ToString(Lans.GetShortDateTimeFormat()); } row["ProcDate"]=dateT; row["procTime"]=""; if(dateT.TimeOfDay!=TimeSpan.Zero) { row["procTime"]=dateT.ToString("h:mm")+dateT.ToString("%t").ToLower(); } row["procTimeEnd"]=""; row["procFee"]=""; row["ProcNum"]=0; row["ProcNumLab"]=""; row["procStatus"]=""; row["ProcStatus"]=""; row["prov"]=""; row["quadrant"]=""; row["RxNum"]=0; row["SheetNum"]=0; row["signature"]=""; if(rawComm.Rows[i]["SigPresent"].ToString()=="1") { row["signature"]=Lans.g("ChartModule","Signed"); } row["Surf"]=""; row["TaskNum"]=0; row["toothNum"]=""; row["ToothNum"]=""; row["ToothRange"]=""; row["user"]=Userods.GetName(PIn.Long(rawComm.Rows[i]["UserNum"].ToString())); rows.Add(row); } #endregion Commlog } if(componentsToLoad.ShowFormPat) { #region formpat command = "SELECT FormDateTime,FormPatNum " + "FROM formpat WHERE PatNum ='" + POut.Long(patNum) + "' ORDER BY FormDateTime"; DataTable rawForm = dcon.GetTable(command); for(int i = 0;i < rawForm.Rows.Count;i++) { row = table.NewRow(); row["AbbrDesc"]=""; row["aptDateTime"] = DateTime.MinValue; row["AptNum"] = 0; row["clinic"]=""; row["CodeNum"] = ""; row["colorBackG"] = Color.White.ToArgb(); row["colorText"] = DefC.Long[(int)DefCat.ProgNoteColors][6].ItemColor.ToArgb().ToString(); row["CommlogNum"] =0; row["dateEntryC"]=""; row["dateTP"]=""; row["description"] = Lans.g("ChartModule","Questionnaire"); row["dx"] = ""; row["Dx"] = ""; row["EmailMessageNum"] = 0; row["FormPatNum"] = rawForm.Rows[i]["FormPatNum"].ToString(); row["HideGraphics"]=""; row["isLocked"]=""; row["LabCaseNum"] = 0; row["length"]=""; row["note"] = ""; row["orionDateScheduleBy"]=""; row["orionDateStopClock"]=""; row["orionDPC"]=""; row["orionDPCpost"]=""; row["orionIsEffectiveComm"]=""; row["orionIsOnCall"]=""; row["orionStatus2"]=""; row["PatNum"] = ""; row["Priority"] = ""; row["priority"]=""; row["ProcCode"] = ""; dateT = PIn.DateT(rawForm.Rows[i]["FormDateTime"].ToString()); row["ProcDate"] = dateT.ToShortDateString(); if(dateT.TimeOfDay != TimeSpan.Zero) { row["procTime"] = dateT.ToString("h:mm") + dateT.ToString("%t").ToLower(); } if(dateT.Year < 1880) { row["procDate"] = ""; } else { row["procDate"] = dateT.ToString(Lans.GetShortDateTimeFormat()); } if(dateT.TimeOfDay != TimeSpan.Zero) { row["procTime"] = dateT.ToString("h:mm") + dateT.ToString("%t").ToLower(); } row["procTimeEnd"]=""; row["procFee"] = ""; row["ProcNum"] = 0; row["ProcNumLab"] = ""; row["procStatus"] = ""; row["ProcStatus"] = ""; row["prov"] = ""; row["quadrant"]=""; row["RxNum"] = 0; row["SheetNum"] = 0; row["signature"] = ""; row["Surf"] = ""; row["TaskNum"] = 0; row["toothNum"] = ""; row["ToothNum"] = ""; row["ToothRange"] = ""; row["user"] = ""; /*commlog code dateT = PIn.PDateT(rawForm.Rows[i]["FormDateTime"].ToString()); row["CommDateTime"] = dateT; row["commDate"] = dateT.ToShortDateString(); if (dateT.TimeOfDay != TimeSpan.Zero) { row["commTime"] = dateT.ToString("h:mm") + dateT.ToString("%t").ToLower(); } row["CommlogNum"] = "0"; row["commType"] = Lans.g("AccountModule", "Questionnaire"); row["EmailMessageNum"] = "0"; row["FormPatNum"] = rawForm.Rows[i]["FormPatNum"].ToString(); row["mode"] = ""; row["Note"] = ""; row["patName"] = ""; row["SheetNum"] = "0"; //row["sentOrReceived"]=""; */ rows.Add(row); } #endregion formpat } if(componentsToLoad.ShowRX) { #region Rx command="SELECT RxNum,RxDate,Drug,Disp,ProvNum,Notes,PharmacyNum FROM rxpat WHERE PatNum="+POut.Long(patNum) +" ORDER BY RxDate"; DataTable rawRx=dcon.GetTable(command); for(int i=0;i<rawRx.Rows.Count;i++) { row=table.NewRow(); row["AbbrDesc"]=""; row["aptDateTime"]=DateTime.MinValue; row["AptNum"]=0; row["clinic"]=""; row["CodeNum"]=""; row["colorBackG"]=Color.White.ToArgb(); row["colorText"]=DefC.Long[(int)DefCat.ProgNoteColors][5].ItemColor.ToArgb().ToString(); row["CommlogNum"]=0; row["dateEntryC"]=""; row["dateTP"]=""; row["description"]=Lans.g("ChartModule","Rx - ")+rawRx.Rows[i]["Drug"].ToString()+" - #"+rawRx.Rows[i]["Disp"].ToString(); if(rawRx.Rows[i]["PharmacyNum"].ToString()!="0") { row["description"]+="\r\n"+Pharmacies.GetDescription(PIn.Long(rawRx.Rows[i]["PharmacyNum"].ToString())); } row["dx"]=""; row["Dx"]=""; row["EmailMessageNum"]=0; row["FormPatNum"]=0; row["HideGraphics"]=""; row["isLocked"]=""; row["LabCaseNum"]=0; row["length"]=""; row["note"]=rawRx.Rows[i]["Notes"].ToString(); row["orionDateScheduleBy"]=""; row["orionDateStopClock"]=""; row["orionDPC"]=""; row["orionDPCpost"]=""; row["orionIsEffectiveComm"]=""; row["orionIsOnCall"]=""; row["orionStatus2"]=""; row["PatNum"]=""; row["Priority"]=""; row["priority"]=""; row["ProcCode"]=""; dateT=PIn.Date(rawRx.Rows[i]["RxDate"].ToString()); if(dateT.Year<1880) { row["procDate"]=""; } else { row["procDate"]=dateT.ToString(Lans.GetShortDateTimeFormat()); } row["ProcDate"]=dateT; row["procFee"]=""; row["ProcNum"]=0; row["ProcNumLab"]=""; row["procStatus"]=""; row["ProcStatus"]=""; row["procTime"]=""; row["procTimeEnd"]=""; row["prov"]=Providers.GetAbbr(PIn.Long(rawRx.Rows[i]["ProvNum"].ToString())); row["quadrant"]=""; row["RxNum"]=rawRx.Rows[i]["RxNum"].ToString(); row["SheetNum"]=0; row["signature"]=""; row["Surf"]=""; row["TaskNum"]=0; row["toothNum"]=""; row["ToothNum"]=""; row["ToothRange"]=""; row["user"]=""; rows.Add(row); } #endregion Rx } if(componentsToLoad.ShowLabCases) { #region LabCase command="SELECT labcase.*,Description,Phone FROM labcase,laboratory " +"WHERE labcase.LaboratoryNum=laboratory.LaboratoryNum " +"AND PatNum="+POut.Long(patNum) +" ORDER BY DateTimeCreated"; DataTable rawLab=dcon.GetTable(command); DateTime duedate; for(int i=0;i<rawLab.Rows.Count;i++) { row=table.NewRow(); row["AbbrDesc"]=""; row["aptDateTime"]=DateTime.MinValue; row["AptNum"]=0; row["clinic"]=""; row["CodeNum"]=""; row["colorBackG"]=Color.White.ToArgb(); row["colorText"]=DefC.Long[(int)DefCat.ProgNoteColors][7].ItemColor.ToArgb().ToString(); row["CommlogNum"]=0; row["dateEntryC"]=""; row["dateTP"]=""; row["description"]=Lans.g("ChartModule","LabCase - ")+rawLab.Rows[i]["Description"].ToString()+" " +rawLab.Rows[i]["Phone"].ToString(); if(PIn.Date(rawLab.Rows[i]["DateTimeDue"].ToString()).Year>1880) { duedate=PIn.DateT(rawLab.Rows[i]["DateTimeDue"].ToString()); row["description"]+="\r\n"+Lans.g("ChartModule","Due")+" "+duedate.ToString("ddd")+" " +duedate.ToShortDateString()+" "+duedate.ToShortTimeString(); } if(PIn.Date(rawLab.Rows[i]["DateTimeChecked"].ToString()).Year>1880) { row["description"]+="\r\n"+Lans.g("ChartModule","Quality Checked"); } else if(PIn.Date(rawLab.Rows[i]["DateTimeRecd"].ToString()).Year>1880) { row["description"]+="\r\n"+Lans.g("ChartModule","Received"); } else if(PIn.Date(rawLab.Rows[i]["DateTimeSent"].ToString()).Year>1880) { row["description"]+="\r\n"+Lans.g("ChartModule","Sent"); } row["dx"]=""; row["Dx"]=""; row["EmailMessageNum"]=0; row["FormPatNum"]=0; row["HideGraphics"]=""; row["isLocked"]=""; row["LabCaseNum"]=rawLab.Rows[i]["LabCaseNum"].ToString(); row["length"]=""; row["note"]=rawLab.Rows[i]["Instructions"].ToString(); row["orionDateScheduleBy"]=""; row["orionDateStopClock"]=""; row["orionDPC"]=""; row["orionDPCpost"]=""; row["orionIsEffectiveComm"]=""; row["orionIsOnCall"]=""; row["orionStatus2"]=""; row["PatNum"]=""; row["Priority"]=""; row["priority"]=""; row["ProcCode"]=""; dateT=PIn.DateT(rawLab.Rows[i]["DateTimeCreated"].ToString()); if(dateT.Year<1880) { row["procDate"]=""; } else { row["procDate"]=dateT.ToString(Lans.GetShortDateTimeFormat()); } row["procTime"]=""; if(dateT.TimeOfDay!=TimeSpan.Zero) { row["procTime"]=dateT.ToString("h:mm")+dateT.ToString("%t").ToLower(); } row["ProcDate"]=dateT; row["procTimeEnd"]=""; row["procFee"]=""; row["ProcNum"]=0; row["ProcNumLab"]=""; row["procStatus"]=""; row["ProcStatus"]=""; row["prov"]=""; row["quadrant"]=""; row["RxNum"]=0; row["SheetNum"]=0; row["signature"]=""; row["Surf"]=""; row["TaskNum"]=0; row["toothNum"]=""; row["ToothNum"]=""; row["ToothRange"]=""; row["user"]=""; rows.Add(row); } #endregion LabCase } if(componentsToLoad.ShowTasks) { #region Task command="SELECT task.*,tasklist.Descript ListDisc,p1.FName " +"FROM patient p1,patient p2, task,tasklist " +"WHERE task.KeyNum=p1.PatNum " +"AND task.TaskListNum=tasklist.TaskListNum " +"AND p1.Guarantor=p2.Guarantor " +"AND p2.PatNum="+POut.Long(patNum) +" AND task.ObjectType=1 " +"ORDER BY DateTimeEntry"; DataTable rawTask=dcon.GetTable(command); List<long> taskNums=new List<long>(); for(int i=0;i<rawTask.Rows.Count;i++) { taskNums.Add(PIn.Long(rawTask.Rows[i]["TaskNum"].ToString())); } List<TaskNote> TaskNoteList=TaskNotes.RefreshForTasks(taskNums); for(int i=0;i<rawTask.Rows.Count;i++) { row=table.NewRow(); row["AbbrDesc"]=""; row["aptDateTime"]=DateTime.MinValue; row["AptNum"]=0; row["clinic"]=""; row["CodeNum"]=""; //colors the same as notes row["colorText"] = DefC.Long[(int)DefCat.ProgNoteColors][18].ItemColor.ToArgb().ToString(); row["colorBackG"] = DefC.Long[(int)DefCat.ProgNoteColors][19].ItemColor.ToArgb().ToString(); //row["colorText"] = DefC.Long[(int)DefCat.ProgNoteColors][6].ItemColor.ToArgb().ToString();//same as commlog row["CommlogNum"]=0; row["dateEntryC"]=""; row["dateTP"]=""; if(rawTask.Rows[i]["KeyNum"].ToString()==patNum.ToString()) { txt=""; } else { txt="("+rawTask.Rows[i]["FName"].ToString()+") "; } if(rawTask.Rows[i]["TaskStatus"].ToString()=="2") {//completed txt += Lans.g("ChartModule","Completed "); row["colorBackG"] = Color.White.ToArgb(); //use same as note colors for completed tasks row["colorText"] = DefC.Long[(int)DefCat.ProgNoteColors][20].ItemColor.ToArgb().ToString(); row["colorBackG"] = DefC.Long[(int)DefCat.ProgNoteColors][21].ItemColor.ToArgb().ToString(); } row["description"]=txt+Lans.g("ChartModule","Task - In List: ")+rawTask.Rows[i]["ListDisc"].ToString(); row["dx"]=""; row["Dx"]=""; row["EmailMessageNum"]=0; row["FormPatNum"]=0; row["HideGraphics"]=""; row["isLocked"]=""; row["LabCaseNum"]=0; row["length"]=""; txt=""; if(!rawTask.Rows[i]["Descript"].ToString().StartsWith("==") && rawTask.Rows[i]["UserNum"].ToString()!="") { txt+=Userods.GetName(PIn.Long(rawTask.Rows[i]["UserNum"].ToString()))+" - "; } txt+=rawTask.Rows[i]["Descript"].ToString(); long taskNum=PIn.Long(rawTask.Rows[i]["TaskNum"].ToString()); for(int n=0;n<TaskNoteList.Count;n++) { if(TaskNoteList[n].TaskNum!=taskNum) { continue; } txt+="\r\n"//even on the first loop +"=="+Userods.GetName(TaskNoteList[n].UserNum)+" - " +TaskNoteList[n].DateTimeNote.ToShortDateString()+" " +TaskNoteList[n].DateTimeNote.ToShortTimeString() +" - "+TaskNoteList[n].Note; } row["note"]=txt; row["orionDateScheduleBy"]=""; row["orionDateStopClock"]=""; row["orionDPC"]=""; row["orionDPCpost"]=""; row["orionIsEffectiveComm"]=""; row["orionIsOnCall"]=""; row["orionStatus2"]=""; row["PatNum"]=rawTask.Rows[i]["KeyNum"].ToString(); row["Priority"]=""; row["priority"]=""; row["ProcCode"]=""; dateT = PIn.DateT(rawTask.Rows[i]["DateTask"].ToString()); row["procTime"]=""; if(dateT.Year < 1880) {//check if due date set for task or note dateT = PIn.DateT(rawTask.Rows[i]["DateTimeEntry"].ToString()); if(dateT.Year < 1880) {//since dateT was just redefined, check it now row["procDate"] = ""; } else { row["procDate"] = dateT.ToShortDateString(); } if(dateT.TimeOfDay != TimeSpan.Zero) { row["procTime"] = dateT.ToString("h:mm") + dateT.ToString("%t").ToLower(); } row["ProcDate"] = dateT; } else { row["procDate"] =dateT.ToString(Lans.GetShortDateTimeFormat()); if(dateT.TimeOfDay != TimeSpan.Zero) { row["procTime"] = dateT.ToString("h:mm") + dateT.ToString("%t").ToLower(); } row["ProcDate"] = dateT; //row["Surf"] = "DUE"; } row["procTimeEnd"]=""; row["procFee"]=""; row["ProcNum"]=0; row["ProcNumLab"]=""; row["procStatus"]=""; row["ProcStatus"]=""; row["prov"]=""; row["quadrant"]=""; row["RxNum"]=0; row["SheetNum"]=0; row["signature"]=""; row["Surf"]=""; row["TaskNum"]=taskNum; row["toothNum"]=""; row["ToothNum"]=""; row["ToothRange"]=""; row["user"]=""; rows.Add(row); } #endregion Task } #region Appointments command="SELECT * FROM appointment WHERE PatNum="+POut.Long(patNum); if(componentsToLoad.ShowAppointments) {//we will need this table later for planned appts, so always need to get. //get all appts } else{ //only include planned appts. We will need those later, but not in this grid. command+=" AND AptStatus = "+POut.Int((int)ApptStatus.Planned); } command+=" ORDER BY AptDateTime"; rawApt=dcon.GetTable(command); long apptStatus; for(int i=0;i<rawApt.Rows.Count;i++) { row=table.NewRow(); row["AbbrDesc"]=""; row["aptDateTime"]=DateTime.MinValue; row["AptNum"]=rawApt.Rows[i]["AptNum"].ToString(); row["clinic"]=""; row["colorBackG"]=Color.White.ToArgb(); dateT=PIn.DateT(rawApt.Rows[i]["AptDateTime"].ToString()); apptStatus=PIn.Long(rawApt.Rows[i]["AptStatus"].ToString()); row["colorBackG"]=""; row["colorText"]=DefC.Long[(int)DefCat.ProgNoteColors][8].ItemColor.ToArgb().ToString(); row["CommlogNum"]=0; row["dateEntryC"]=""; row["dateTP"]=""; row["description"]=Lans.g("ChartModule","Appointment - ")+dateT.ToShortTimeString()+"\r\n" +rawApt.Rows[i]["ProcDescript"].ToString(); if(dateT.Date.Date==DateTime.Today.Date) { row["colorBackG"]=DefC.Long[(int)DefCat.ProgNoteColors][9].ItemColor.ToArgb().ToString(); //deliniates nicely between old appts row["colorText"]=DefC.Long[(int)DefCat.ProgNoteColors][8].ItemColor.ToArgb().ToString(); } else if(dateT.Date<DateTime.Today) { row["colorBackG"]=DefC.Long[(int)DefCat.ProgNoteColors][11].ItemColor.ToArgb().ToString(); row["colorText"]=DefC.Long[(int)DefCat.ProgNoteColors][10].ItemColor.ToArgb().ToString(); } else if(dateT.Date>DateTime.Today) { row["colorBackG"]=DefC.Long[(int)DefCat.ProgNoteColors][13].ItemColor.ToArgb().ToString(); //at a glace, you see green...the pt is good to go as they have a future appt scheduled row["colorText"]=DefC.Long[(int)DefCat.ProgNoteColors][12].ItemColor.ToArgb().ToString(); } if(apptStatus==(int)ApptStatus.Broken) { row["colorText"]=DefC.Long[(int)DefCat.ProgNoteColors][14].ItemColor.ToArgb().ToString(); row["colorBackG"]=DefC.Long[(int)DefCat.ProgNoteColors][15].ItemColor.ToArgb().ToString(); row["description"]=Lans.g("ChartModule","BROKEN Appointment - ")+dateT.ToShortTimeString()+"\r\n" +rawApt.Rows[i]["ProcDescript"].ToString(); } else if(apptStatus==(int)ApptStatus.UnschedList) { row["colorText"]=DefC.Long[(int)DefCat.ProgNoteColors][14].ItemColor.ToArgb().ToString(); row["colorBackG"]=DefC.Long[(int)DefCat.ProgNoteColors][15].ItemColor.ToArgb().ToString(); row["description"]=Lans.g("ChartModule","UNSCHEDULED Appointment - ")+dateT.ToShortTimeString()+"\r\n" +rawApt.Rows[i]["ProcDescript"].ToString(); } else if(apptStatus==(int)ApptStatus.Planned) { row["colorText"]=DefC.Long[(int)DefCat.ProgNoteColors][16].ItemColor.ToArgb().ToString(); row["colorBackG"]=DefC.Long[(int)DefCat.ProgNoteColors][17].ItemColor.ToArgb().ToString(); row["description"]=Lans.g("ChartModule","PLANNED Appointment")+"\r\n" +rawApt.Rows[i]["ProcDescript"].ToString(); } else if(apptStatus==(int)ApptStatus.PtNote) { row["colorText"]=DefC.Long[(int)DefCat.ProgNoteColors][18].ItemColor.ToArgb().ToString(); row["colorBackG"]=DefC.Long[(int)DefCat.ProgNoteColors][19].ItemColor.ToArgb().ToString(); row["description"] = Lans.g("ChartModule","*** Patient NOTE *** - ") + dateT.ToShortTimeString(); } else if(apptStatus ==(int)ApptStatus.PtNoteCompleted) { row["colorText"] = DefC.Long[(int)DefCat.ProgNoteColors][20].ItemColor.ToArgb().ToString(); row["colorBackG"] = DefC.Long[(int)DefCat.ProgNoteColors][21].ItemColor.ToArgb().ToString(); row["description"] = Lans.g("ChartModule","** Complete Patient NOTE ** - ") + dateT.ToShortTimeString(); } row["dx"]=""; row["Dx"]=""; row["EmailMessageNum"]=0; row["FormPatNum"]=0; row["HideGraphics"]=""; row["isLocked"]=""; row["LabCaseNum"]=0; row["length"]=""; if(rawApt.Rows[i]["Pattern"].ToString()!="") { row["length"]=new TimeSpan(0,rawApt.Rows[i]["Pattern"].ToString().Length*5,0).ToStringHmm(); } row["note"]=rawApt.Rows[i]["Note"].ToString(); row["orionDateScheduleBy"]=""; row["orionDateStopClock"]=""; row["orionDPC"]=""; row["orionDPCpost"]=""; row["orionIsEffectiveComm"]=""; row["orionIsOnCall"]=""; row["orionStatus2"]=""; row["PatNum"]=""; row["Priority"]=""; row["priority"]=""; row["ProcCode"]=""; if(dateT.Year<1880) { row["procDate"]=""; } else { row["procDate"]=dateT.ToString(Lans.GetShortDateTimeFormat()); } row["procTime"]=""; if(dateT.TimeOfDay!=TimeSpan.Zero) { row["procTime"]=dateT.ToString("h:mm")+dateT.ToString("%t").ToLower(); } row["ProcDate"]=dateT; row["procTimeEnd"]=""; row["procFee"]=""; row["ProcNum"]=0; row["ProcNumLab"]=""; row["procStatus"]=""; row["ProcStatus"]=""; row["prov"]=""; row["quadrant"]=""; row["RxNum"]=0; row["SheetNum"]=0; row["signature"]=""; row["Surf"]=""; row["TaskNum"]=0; row["toothNum"]=""; row["ToothNum"]=""; row["ToothRange"]=""; row["user"]=""; rows.Add(row); } #endregion Appointments if(componentsToLoad.ShowEmail) { #region email command="SELECT EmailMessageNum,MsgDateTime,Subject,BodyText,PatNum,SentOrReceived " +"FROM emailmessage " +"WHERE PatNum="+POut.Long(patNum)+" AND SentOrReceived NOT IN (12,13) "//Do not show Direct message acknowledgements in Chart progress notes +"ORDER BY MsgDateTime"; DataTable rawEmail=dcon.GetTable(command); for(int i=0;i<rawEmail.Rows.Count;i++) { row=table.NewRow(); row["AbbrDesc"]=""; row["aptDateTime"]=DateTime.MinValue; row["AptNum"]=0; row["clinic"]=""; row["CodeNum"]=""; row["colorBackG"]=Color.White.ToArgb(); row["colorText"]=DefC.Long[(int)DefCat.ProgNoteColors][6].ItemColor.ToArgb().ToString();//needs to change row["CommlogNum"]=0; row["dateEntryC"]=""; row["dateTP"]=""; txt=""; if(rawEmail.Rows[i]["SentOrReceived"].ToString()=="0") { txt=Lans.g("ChartModule","(unsent) "); } row["description"]=Lans.g("ChartModule","Email - ")+txt+rawEmail.Rows[i]["Subject"].ToString(); row["dx"]=""; row["Dx"]=""; row["EmailMessageNum"]=rawEmail.Rows[i]["EmailMessageNum"].ToString(); row["FormPatNum"]=0; row["HideGraphics"]=""; row["isLocked"]=""; row["LabCaseNum"]=0; row["length"]=""; row["note"]=rawEmail.Rows[i]["BodyText"].ToString(); row["orionDateScheduleBy"]=""; row["orionDateStopClock"]=""; row["orionDPC"]=""; row["orionDPCpost"]=""; row["orionIsEffectiveComm"]=""; row["orionIsOnCall"]=""; row["orionStatus2"]=""; row["PatNum"]=""; row["Priority"]=""; row["priority"]=""; row["ProcCode"]=""; //row["PatNum"]=rawEmail.Rows[i]["PatNum"].ToString(); dateT=PIn.DateT(rawEmail.Rows[i]["msgDateTime"].ToString()); if(dateT.Year<1880) { row["procDate"]=""; } else { row["procDate"]=dateT.ToString(Lans.GetShortDateTimeFormat()); } row["ProcDate"]=dateT; row["procTime"]=""; if(dateT.TimeOfDay!=TimeSpan.Zero) { row["procTime"]=dateT.ToString("h:mm")+dateT.ToString("%t").ToLower(); } row["procTimeEnd"]=""; row["procFee"]=""; row["ProcNum"]=0; row["ProcNumLab"]=""; row["procStatus"]=""; row["ProcStatus"]=""; row["prov"]=""; row["quadrant"]=""; row["RxNum"]=0; row["SheetNum"]=0; row["signature"]=""; row["Surf"]=""; row["TaskNum"]=0; row["toothNum"]=""; row["ToothNum"]=""; row["ToothRange"]=""; row["user"]=""; rows.Add(row); } #endregion email } if(componentsToLoad.ShowSheets) { #region sheet command="SELECT Description,SheetNum,DateTimeSheet,SheetType " +"FROM sheet " +"WHERE PatNum="+POut.Long(patNum) +" AND SheetType!="+POut.Long((int)SheetTypeEnum.Rx)//rx are only accesssible from within Rx edit window. +" AND SheetType!="+POut.Long((int)SheetTypeEnum.LabSlip)//labslips are only accesssible from within the labslip edit window. +" ORDER BY DateTimeSheet"; DataTable rawSheet=dcon.GetTable(command); //SheetTypeEnum sheetType; for(int i=0;i<rawSheet.Rows.Count;i++) { row=table.NewRow(); row["AbbrDesc"]=""; row["aptDateTime"]=DateTime.MinValue; row["AptNum"]=0; row["clinic"]=""; row["CodeNum"]=""; row["colorBackG"]=Color.White.ToArgb(); row["colorText"]=Color.Black.ToArgb();//DefC.Long[(int)DefCat.ProgNoteColors][6].ItemColor.ToArgb().ToString();//needs to change row["CommlogNum"]=0; dateT=PIn.DateT(rawSheet.Rows[i]["DateTimeSheet"].ToString()); if(dateT.Year<1880) { row["dateEntryC"]=""; row["dateTP"]=""; } else { row["dateEntryC"]=dateT.ToString(Lans.GetShortDateTimeFormat()); row["dateTP"]=dateT.ToString(Lans.GetShortDateTimeFormat()); } //sheetType=(SheetTypeEnum)PIn.PLong(rawSheet.Rows[i]["SheetType"].ToString()); row["description"]=rawSheet.Rows[i]["Description"].ToString(); row["dx"]=""; row["Dx"]=""; row["EmailMessageNum"]=0; row["FormPatNum"]=0; row["HideGraphics"]=""; row["isLocked"]=""; row["LabCaseNum"]=0; row["length"]=""; row["note"]=""; row["orionDateScheduleBy"]=""; row["orionDateStopClock"]=""; row["orionDPC"]=""; row["orionDPCpost"]=""; row["orionIsEffectiveComm"]=""; row["orionIsOnCall"]=""; row["orionStatus2"]=""; row["PatNum"]=""; row["Priority"]=""; row["priority"]=""; row["ProcCode"]=""; if(dateT.Year<1880) { row["procDate"]=""; } else { row["procDate"]=dateT.ToString(Lans.GetShortDateTimeFormat()); } row["ProcDate"]=dateT; row["procTime"]=""; if(dateT.TimeOfDay!=TimeSpan.Zero) { row["procTime"]=dateT.ToString("h:mm")+dateT.ToString("%t").ToLower(); } row["procTimeEnd"]=""; row["procFee"]=""; row["ProcNum"]=0; row["ProcNumLab"]=""; row["procStatus"]=""; row["ProcStatus"]=""; row["prov"]=""; row["quadrant"]=""; row["RxNum"]=0; row["SheetNum"]=rawSheet.Rows[i]["SheetNum"].ToString(); row["signature"]=""; row["Surf"]=""; row["TaskNum"]=0; row["toothNum"]=""; row["ToothNum"]=""; row["ToothRange"]=""; row["user"]=""; rows.Add(row); } #endregion sheet } #region Sorting rows.Sort(CompareChartRows); //Canadian lab procedures need to come immediately after their corresponding proc--------------------------------- for(int i=0;i<labRows.Count;i++) { for(int r=0;r<rows.Count;r++) { if(rows[r]["ProcNum"].ToString()==labRows[i]["ProcNumLab"].ToString()) { rows.Insert(r+1,labRows[i]); break; } } } #endregion Sorting for(int i=0;i<rows.Count;i++) { table.Rows.Add(rows[i]); } return table; }
///<summary>Pass in the appointments table so that we can search based on appointments.</summary> public static DataTable GetPatFields(DataTable tableAppts) { if(RemotingClient.RemotingRole==RemotingRole.ClientWeb) { return Meth.GetTable(MethodBase.GetCurrentMethod(),tableAppts); } string command="SELECT PatNum,FieldName,FieldValue " +"FROM patfield " +"WHERE PatNum IN ("; if(tableAppts.Rows.Count==0) { command+="0"; } else{ for(int i=0;i<tableAppts.Rows.Count;i++) { if(i>0) { command+=","; } command+=tableAppts.Rows[i]["PatNum"].ToString(); } } command+=")"; DataConnection dcon=new DataConnection(); DataTable table= dcon.GetTable(command); table.TableName="PatFields"; return table; }
///<summary>If aptnum is specified, then the dates are ignored. If getting data for one planned appt, then pass isPlanned=1. This changes which procedures are retrieved.</summary> public static DataTable GetPeriodApptsTable(DateTime dateStart,DateTime dateEnd,long aptNum,bool isPlanned) { if(RemotingClient.RemotingRole==RemotingRole.ClientWeb) { return Meth.GetTable(MethodBase.GetCurrentMethod(),dateStart,dateEnd,aptNum,isPlanned); } //DateTime dateStart=PIn.PDate(strDateStart); //DateTime dateEnd=PIn.PDate(strDateEnd); //int aptNum=PIn.PInt(strAptNum); //bool isPlanned=PIn.PBool(strIsPlanned); DataConnection dcon=new DataConnection(); DataTable table=new DataTable("Appointments"); DataRow row; //columns that start with lowercase are altered for display rather than being raw data. table.Columns.Add("age"); table.Columns.Add("address"); table.Columns.Add("addrNote"); table.Columns.Add("apptModNote"); table.Columns.Add("aptDate"); table.Columns.Add("aptDay"); table.Columns.Add("aptLength"); table.Columns.Add("aptTime"); table.Columns.Add("AptDateTime"); table.Columns.Add("AptNum"); table.Columns.Add("AptStatus"); table.Columns.Add("Assistant"); table.Columns.Add("assistantAbbr"); table.Columns.Add("billingType"); table.Columns.Add("chartNumber"); table.Columns.Add("chartNumAndName"); table.Columns.Add("confirmed"); table.Columns.Add("Confirmed"); table.Columns.Add("contactMethods"); //table.Columns.Add("creditIns"); table.Columns.Add("CreditType"); table.Columns.Add("famFinUrgNote"); table.Columns.Add("guardians"); table.Columns.Add("hasIns[I]"); table.Columns.Add("hmPhone"); table.Columns.Add("ImageFolder"); table.Columns.Add("insurance"); table.Columns.Add("insToSend[!]"); table.Columns.Add("IsHygiene"); table.Columns.Add("lab"); table.Columns.Add("medOrPremed[+]"); table.Columns.Add("MedUrgNote"); table.Columns.Add("Note"); table.Columns.Add("Op"); table.Columns.Add("patientName"); table.Columns.Add("patientNameF"); table.Columns.Add("PatNum"); table.Columns.Add("patNum"); table.Columns.Add("GuarNum"); table.Columns.Add("patNumAndName"); table.Columns.Add("Pattern"); table.Columns.Add("preMedFlag"); table.Columns.Add("procs"); table.Columns.Add("procsColored"); table.Columns.Add("production"); table.Columns.Add("productionVal"); table.Columns.Add("provider"); table.Columns.Add("ProvHyg"); table.Columns.Add("ProvNum"); table.Columns.Add("timeAskedToArrive"); table.Columns.Add("wkPhone"); table.Columns.Add("wirelessPhone"); table.Columns.Add("writeoffPPO"); string command="SELECT p1.Abbr ProvAbbr,p2.Abbr HygAbbr,patient.Address,patient.Address2,patient.AddrNote," +"patient.ApptModNote,AptDateTime,appointment.AptNum,AptStatus,Assistant," +"patient.BillingType,patient.BirthDate," +"carrier1.CarrierName carrierName1,carrier2.CarrierName carrierName2," +"patient.ChartNumber,patient.City,Confirmed,patient.CreditType,DateTimeChecked," +"DateTimeDue,DateTimeRecd,DateTimeSent,DateTimeAskedToArrive," +"guar.FamFinUrgNote,patient.FName,patient.Guarantor," +"COUNT(AllergyNum) hasAllergy," +"COUNT(DiseaseNum) hasDisease," +"patient.HmPhone,patient.ImageFolder,IsHygiene,IsNewPatient," +"LabCaseNum,patient.LName,patient.MedUrgNote,patient.MiddleI,Note,Op,appointment.PatNum," +"Pattern,COUNT(patplan.InsSubNum) hasIns,patient.PreferConfirmMethod,patient.PreferContactMethod,patient.Preferred," +"patient.PreferRecallMethod,patient.Premed," +"ProcDescript,ProcsColored,ProvHyg,appointment.ProvNum," +"patient.State,patient.WirelessPhone,patient.WkPhone,patient.Zip " +"FROM appointment " +"LEFT JOIN patient ON patient.PatNum=appointment.PatNum " +"LEFT JOIN provider p1 ON p1.ProvNum=appointment.ProvNum " +"LEFT JOIN provider p2 ON p2.ProvNum=appointment.ProvHyg "; if(isPlanned){ command+="LEFT JOIN labcase ON labcase.PlannedAptNum=appointment.AptNum "; } else{ command+="LEFT JOIN labcase ON labcase.AptNum=appointment.AptNum "; } command+="LEFT JOIN patient guar ON guar.PatNum=patient.Guarantor " +"LEFT JOIN patplan ON patplan.PatNum=patient.PatNum AND patplan.Ordinal=1 " //these four lines are very rarely made use of. They depend on the appointment.InsPlan1/2 being filled, which is unreliable. +"LEFT JOIN insplan plan1 ON InsPlan1=plan1.PlanNum " +"LEFT JOIN insplan plan2 ON InsPlan2=plan2.PlanNum " +"LEFT JOIN carrier carrier1 ON plan1.CarrierNum=carrier1.CarrierNum " +"LEFT JOIN carrier carrier2 ON plan2.CarrierNum=carrier2.CarrierNum " +"LEFT JOIN disease ON patient.PatNum=disease.PatNum " +"LEFT JOIN allergy ON patient.PatNum=allergy.PatNum "; if(aptNum==0){ command+="WHERE AptDateTime >= "+POut.Date(dateStart)+" " +"AND AptDateTime < "+POut.Date(dateEnd.AddDays(1))+" " + "AND AptStatus IN (1, 2, 4, 5, 7, 8) "; } else{ command+="WHERE appointment.AptNum="+POut.Long(aptNum); } if(DataConnection.DBtype==DatabaseType.MySql) { command+=" GROUP BY appointment.AptNum"; } else {//Oracle command+=" GROUP BY p1.Abbr,p2.Abbr,patient.Address,patient.Address2,patient.AddrNote," +"patient.ApptModNote,AptDateTime,appointment.AptNum,AptStatus,Assistant," +"patient.BillingType,patient.BirthDate," +"carrier1.CarrierName,carrier2.CarrierName," +"patient.ChartNumber,patient.City,Confirmed,patient.CreditType," +"DateTimeChecked,DateTimeDue,DateTimeRecd,DateTimeSent,DateTimeAskedToArrive," +"guar.FamFinUrgNote,patient.FName,patient.Guarantor,patient.HmPhone,patient.ImageFolder,IsHygiene,IsNewPatient," +"LabCaseNum,patient.LName,patient.MedUrgNote,patient.MiddleI,Note,Op,appointment.PatNum," +"Pattern,patient.PreferConfirmMethod,patient.PreferContactMethod,patient.Preferred," +"patient.PreferRecallMethod,patient.Premed," +"ProcDescript,ProcsColored,ProvHyg,appointment.ProvNum," +"patient.State,patient.WirelessPhone,patient.WkPhone,patient.Zip "; } DataTable raw=dcon.GetTable(command); //rawProc table was historically used for other purposes. It is currently only used for production-------------------------- //rawProcLab table is only used for Canada and goes hand in hand with the rawProc table, also only used for production. DataTable rawProc; DataTable rawProcLab=null; if(raw.Rows.Count==0){ rawProc=new DataTable(); if(CultureInfo.CurrentCulture.Name.EndsWith("CA")) {//Canadian. en-CA or fr-CA rawProcLab=new DataTable(); } } else{ command="SELECT AptNum,PlannedAptNum,"//AbbrDesc,procedurecode.CodeNum +"ProcFee, " +"SUM(CASE WHEN WriteOffEstOverride!=-1 THEN WriteOffEstOverride ELSE WriteOffEst END) writeoffPPO," +"procedurelog.ProcNum " //+"Surf,ToothNum,TreatArea " +"FROM procedurelog " //+"LEFT JOIN procedurecode ON procedurelog.CodeNum=procedurecode.CodeNum " +"LEFT JOIN claimproc ON claimproc.ProcNum=procedurelog.ProcNum " +"AND (claimproc.WriteOffEst != -1 " +"OR claimproc.WriteOffEstOverride != -1) " +"WHERE ProcNumLab=0 AND "; if(isPlanned) { command+="PlannedAptNum!=0 AND PlannedAptNum "; } else { command+="AptNum!=0 AND AptNum "; } command+="IN(";//this was far too slow:SELECT a.AptNum FROM appointment a WHERE "; if(aptNum==0) { for(int a=0;a<raw.Rows.Count;a++){ if(a>0){ command+=","; } command+=raw.Rows[a]["AptNum"].ToString(); } //command+="a.AptDateTime >= "+POut.PDate(dateStart)+" " // +"AND a.AptDateTime < "+POut.PDate(dateEnd.AddDays(1)); } else { command+=POut.Long(aptNum); } if(DataConnection.DBtype==DatabaseType.MySql) { command+=") GROUP BY procedurelog.ProcNum"; } else {//Oracle command+=") GROUP BY procedurelog.ProcNum,AptNum,PlannedAptNum,ProcFee"; } rawProc=dcon.GetTable(command); if(CultureInfo.CurrentCulture.Name.EndsWith("CA") && rawProc.Rows.Count>0) {//Canadian. en-CA or fr-CA command="SELECT procedurelog.ProcNum,ProcNumLab,ProcFee,SUM(CASE WHEN WriteOffEstOverride!=-1 THEN WriteOffEstOverride ELSE WriteOffEst END) writeoffPPO " +"FROM procedurelog " +"LEFT JOIN claimproc ON claimproc.ProcNum=procedurelog.ProcNum " +"AND (claimproc.WriteOffEst != -1 " +"OR claimproc.WriteOffEstOverride != -1) " +"WHERE ProcNumLab IN ("; for(int i=0;i<rawProc.Rows.Count;i++) { if(i>0) { command+=","; } command+=rawProc.Rows[i]["ProcNum"].ToString(); } if(DataConnection.DBtype==DatabaseType.MySql) { command+=") GROUP BY procedurelog.ProcNum"; } else {//Oracle command+=") GROUP BY procedurelog.ProcNum,ProcNumLab,ProcFee"; } rawProcLab=dcon.GetTable(command); } } //rawInsProc table is usually skipped. Too slow------------------------------------------------------------------------------ DataTable rawInsProc=null; if(PrefC.GetBool(PrefName.ApptExclamationShowForUnsentIns)){ //procs for flag, InsNotSent command ="SELECT patient.PatNum, patient.Guarantor " +"FROM patient,procedurecode,procedurelog,claimproc " +"WHERE claimproc.procnum=procedurelog.procnum " +"AND patient.PatNum=procedurelog.PatNum " +"AND procedurelog.CodeNum=procedurecode.CodeNum " +"AND claimproc.NoBillIns=0 " +"AND procedurelog.ProcFee>0 " +"AND claimproc.Status=6 "//estimate +"AND procedurelog.procstatus=2 " +"AND procedurelog.ProcDate >= "+POut.Date(DateTime.Now.AddYears(-1))+" "//I'm sure this is the slow part. Should be easy to make faster with less range +"AND procedurelog.ProcDate <= "+POut.Date(DateTime.Now)+ " " +"GROUP BY patient.PatNum, patient.Guarantor"; rawInsProc=dcon.GetTable(command); } //Guardians------------------------------------------------------------------------------------------------------------------- command="SELECT PatNumChild,PatNumGuardian,Relationship,patient.FName,patient.Preferred " +"FROM guardian " +"LEFT JOIN patient ON patient.PatNum=guardian.PatNumGuardian " +"WHERE PatNumChild IN ("; if(raw.Rows.Count==0){ command+="0"; } else for(int i=0;i<raw.Rows.Count;i++) { if(i>0) { command+=","; } command+=raw.Rows[i]["PatNum"].ToString(); } command+=") ORDER BY Relationship"; DataTable rawGuardians=dcon.GetTable(command); DateTime aptDate; TimeSpan span; int hours; int minutes; DateTime labDate; DateTime labDueDate; DateTime birthdate; DateTime timeAskedToArrive; decimal production; decimal writeoffPPO; for(int i=0;i<raw.Rows.Count;i++) { row=table.NewRow(); row["address"]=Patients.GetAddressFull(raw.Rows[i]["Address"].ToString(),raw.Rows[i]["Address2"].ToString(), raw.Rows[i]["City"].ToString(),raw.Rows[i]["State"].ToString(),raw.Rows[i]["Zip"].ToString()); row["addrNote"]=""; if(raw.Rows[i]["AddrNote"].ToString()!=""){ row["addrNote"]=Lans.g("Appointments","AddrNote: ")+raw.Rows[i]["AddrNote"].ToString(); } aptDate=PIn.DateT(raw.Rows[i]["AptDateTime"].ToString()); row["AptDateTime"]=aptDate; birthdate=PIn.Date(raw.Rows[i]["Birthdate"].ToString()); row["age"]=""; if(birthdate.AddYears(18)<DateTime.Today) { row["age"]=Lans.g("Appointments","Age: ");//only show if older than 18 } if(birthdate.Year>1880){ row["age"]+=PatientLogic.DateToAgeString(birthdate); } else{ row["age"]+="?"; } row["apptModNote"]=""; if(raw.Rows[i]["ApptModNote"].ToString()!="") { row["apptModNote"]=Lans.g("Appointments","ApptModNote: ")+raw.Rows[i]["ApptModNote"].ToString(); } row["aptDate"]=aptDate.ToShortDateString(); row["aptDay"]=aptDate.ToString("dddd"); span=TimeSpan.FromMinutes(raw.Rows[i]["Pattern"].ToString().Length*5); hours=span.Hours; minutes=span.Minutes; if(hours==0){ row["aptLength"]=minutes.ToString()+Lans.g("Appointments"," Min"); } else if(hours==1){ row["aptLength"]=hours.ToString()+Lans.g("Appointments"," Hr, ") +minutes.ToString()+Lans.g("Appointments"," Min"); } else{ row["aptLength"]=hours.ToString()+Lans.g("Appointments"," Hrs, ") +minutes.ToString()+Lans.g("Appointments"," Min"); } row["aptTime"]=aptDate.ToShortTimeString(); row["AptNum"]=raw.Rows[i]["AptNum"].ToString(); row["AptStatus"]=raw.Rows[i]["AptStatus"].ToString(); row["Assistant"]=raw.Rows[i]["Assistant"].ToString(); row["assistantAbbr"]=""; if(row["Assistant"].ToString()!="0") { row["assistantAbbr"]=Employees.GetAbbr(PIn.Long(row["Assistant"].ToString())); } row["billingType"]=DefC.GetName(DefCat.BillingTypes,PIn.Long(raw.Rows[i]["BillingType"].ToString())); row["chartNumber"]=raw.Rows[i]["ChartNumber"].ToString(); row["chartNumAndName"]=""; if(raw.Rows[i]["IsNewPatient"].ToString()=="1") { row["chartNumAndName"]="NP-"; } row["chartNumAndName"]+=raw.Rows[i]["ChartNumber"].ToString()+" " +PatientLogic.GetNameLF(raw.Rows[i]["LName"].ToString(),raw.Rows[i]["FName"].ToString(), raw.Rows[i]["Preferred"].ToString(),raw.Rows[i]["MiddleI"].ToString()); row["confirmed"]=DefC.GetName(DefCat.ApptConfirmed,PIn.Long(raw.Rows[i]["Confirmed"].ToString())); row["Confirmed"]=raw.Rows[i]["Confirmed"].ToString(); row["contactMethods"]=""; if(raw.Rows[i]["PreferConfirmMethod"].ToString()!="0"){ row["contactMethods"]+=Lans.g("Appointments","Confirm Method: ") +((ContactMethod)PIn.Long(raw.Rows[i]["PreferConfirmMethod"].ToString())).ToString(); } if(raw.Rows[i]["PreferContactMethod"].ToString()!="0"){ if(row["contactMethods"].ToString()!="") { row["contactMethods"]+="\r\n"; } row["contactMethods"]+=Lans.g("Appointments","Contact Method: ") +((ContactMethod)PIn.Long(raw.Rows[i]["PreferContactMethod"].ToString())).ToString(); } if(raw.Rows[i]["PreferRecallMethod"].ToString()!="0"){ if(row["contactMethods"].ToString()!="") { row["contactMethods"]+="\r\n"; } row["contactMethods"]+=Lans.g("Appointments","Recall Method: ") +((ContactMethod)PIn.Long(raw.Rows[i]["PreferRecallMethod"].ToString())).ToString(); } bool InsToSend=false; if(rawInsProc!=null){ //figure out if pt's family has ins claims that need to be created for(int j=0;j<rawInsProc.Rows.Count;j++){ if(raw.Rows[i]["hasIns"].ToString()!="0") { if (raw.Rows[i]["Guarantor"].ToString()==rawInsProc.Rows[j]["Guarantor"].ToString() || raw.Rows[i]["Guarantor"].ToString()==rawInsProc.Rows[j]["PatNum"].ToString()) { InsToSend=true; } } } } row["CreditType"]=raw.Rows[i]["CreditType"].ToString(); row["famFinUrgNote"]=""; if(raw.Rows[i]["FamFinUrgNote"].ToString()!="") { row["famFinUrgNote"]=Lans.g("Appointments","FamFinUrgNote: ")+raw.Rows[i]["FamFinUrgNote"].ToString(); } row["guardians"]=""; GuardianRelationship guardRelat; for(int g=0;g<rawGuardians.Rows.Count;g++) { if(raw.Rows[i]["PatNum"].ToString()==rawGuardians.Rows[g]["PatNumChild"].ToString()) { if(row["guardians"].ToString()!="") { row["guardians"]+=","; } guardRelat=(GuardianRelationship)PIn.Int(rawGuardians.Rows[g]["Relationship"].ToString()); row["guardians"]+=Patients.GetNameFirstOrPreferred(rawGuardians.Rows[g]["FName"].ToString(),rawGuardians.Rows[g]["Preferred"].ToString()) +Guardians.GetGuardianRelationshipStr(guardRelat); } } row["hasIns[I]"]=""; if(raw.Rows[i]["hasIns"].ToString()!="0") { row["hasIns[I]"]+="I"; } row["hmPhone"]=Lans.g("Appointments","Hm: ")+raw.Rows[i]["HmPhone"].ToString(); row["ImageFolder"]=raw.Rows[i]["ImageFolder"].ToString(); row["insurance"]=""; if(raw.Rows[i]["carrierName1"].ToString()!="") { row["insurance"]+=raw.Rows[i]["carrierName1"].ToString(); if(raw.Rows[i]["carrierName2"].ToString()!="") { //if(row["insurance"].ToString()!="") { row["insurance"]+="\r\n"; //} row["insurance"]+=raw.Rows[i]["carrierName2"].ToString(); } } else if(raw.Rows[i]["hasIns"].ToString()!="0") { row["insurance"]=Lans.g("Appointments","Insured"); } row["insToSend[!]"]=""; if(InsToSend) { row["insToSend[!]"]="!"; } row["IsHygiene"]=raw.Rows[i]["IsHygiene"].ToString(); row["lab"]=""; if(raw.Rows[i]["LabCaseNum"].ToString()!=""){ labDate=PIn.DateT(raw.Rows[i]["DateTimeChecked"].ToString()); if(labDate.Year>1880) { row["lab"]=Lans.g("Appointments","Lab Quality Checked"); } else { labDate=PIn.DateT(raw.Rows[i]["DateTimeRecd"].ToString()); if(labDate.Year>1880) { row["lab"]=Lans.g("Appointments","Lab Received"); } else { labDate=PIn.DateT(raw.Rows[i]["DateTimeSent"].ToString()); if(labDate.Year>1880) { row["lab"]=Lans.g("Appointments","Lab Sent");//sent but not received } else { row["lab"]=Lans.g("Appointments","Lab Not Sent"); } labDueDate=PIn.DateT(raw.Rows[i]["DateTimeDue"].ToString()); if(labDueDate.Year>1880) { row["lab"]+=", "+Lans.g("Appointments","Due: ")//+dateDue.ToString("ddd")+" " +labDueDate.ToShortDateString();//+" "+dateDue.ToShortTimeString(); } } } } row["medOrPremed[+]"]=""; if(raw.Rows[i]["MedUrgNote"].ToString()!="" || raw.Rows[i]["Premed"].ToString()=="1" || raw.Rows[i]["hasDisease"].ToString()!="0" || raw.Rows[i]["hasAllergy"].ToString()!="0") { row["medOrPremed[+]"]="+"; } row["MedUrgNote"]=raw.Rows[i]["MedUrgNote"].ToString(); row["Note"]=raw.Rows[i]["Note"].ToString(); row["Op"]=raw.Rows[i]["Op"].ToString(); if(raw.Rows[i]["IsNewPatient"].ToString()=="1"){ row["patientName"]="NP-"; } row["patientName"]+=PatientLogic.GetNameLF(raw.Rows[i]["LName"].ToString(),raw.Rows[i]["FName"].ToString(), raw.Rows[i]["Preferred"].ToString(),raw.Rows[i]["MiddleI"].ToString()); row["patientNameF"]=raw.Rows[i]["FName"].ToString(); row["PatNum"]=raw.Rows[i]["PatNum"].ToString(); row["patNum"]="PatNum: "+raw.Rows[i]["PatNum"].ToString(); row["GuarNum"]=raw.Rows[i]["Guarantor"].ToString(); row["patNumAndName"]=""; if(raw.Rows[i]["IsNewPatient"].ToString()=="1") { row["patNumAndName"]="NP-"; } row["patNumAndName"]+=raw.Rows[i]["PatNum"].ToString()+" " +PatientLogic.GetNameLF(raw.Rows[i]["LName"].ToString(),raw.Rows[i]["FName"].ToString(), raw.Rows[i]["Preferred"].ToString(),raw.Rows[i]["MiddleI"].ToString()); row["Pattern"]=raw.Rows[i]["Pattern"].ToString(); row["preMedFlag"]=""; if(raw.Rows[i]["Premed"].ToString()=="1"){ row["preMedFlag"]=Lans.g("Appointments","Premedicate"); } row["procs"]=raw.Rows[i]["ProcDescript"].ToString(); row["procsColored"]+=raw.Rows[i]["ProcsColored"].ToString(); production=0; writeoffPPO=0; if(rawProc!=null) { for(int p=0;p<rawProc.Rows.Count;p++) { if(isPlanned && raw.Rows[i]["AptNum"].ToString()!=rawProc.Rows[p]["PlannedAptNum"].ToString()) { continue; } else if(!isPlanned && raw.Rows[i]["AptNum"].ToString()!=rawProc.Rows[p]["AptNum"].ToString()) { continue; } production+=PIn.Decimal(rawProc.Rows[p]["ProcFee"].ToString()); //WriteOffEst -1 and WriteOffEstOverride -1 already excluded //production-= writeoffPPO+=PIn.Decimal(rawProc.Rows[p]["writeoffPPO"].ToString());//frequently zero if(rawProcLab!=null) { //Will be null if not Canada. for(int a=0;a<rawProcLab.Rows.Count;a++) { if(rawProcLab.Rows[a]["ProcNumLab"].ToString()==rawProc.Rows[p]["ProcNum"].ToString()) { production+=PIn.Decimal(rawProcLab.Rows[a]["ProcFee"].ToString()); writeoffPPO+=PIn.Decimal(rawProcLab.Rows[a]["writeoffPPO"].ToString());//frequently zero } } } } } row["production"]=production.ToString("c");//PIn.Double(raw.Rows[i]["Production"].ToString()).ToString("c"); row["productionVal"]=production.ToString();//raw.Rows[i]["Production"].ToString(); if(raw.Rows[i]["IsHygiene"].ToString()=="1"){ row["provider"]=raw.Rows[i]["HygAbbr"].ToString(); if(raw.Rows[i]["ProvAbbr"].ToString()!=""){ row["provider"]+=" ("+raw.Rows[i]["ProvAbbr"].ToString()+")"; } } else{ row["provider"]=raw.Rows[i]["ProvAbbr"].ToString(); if(raw.Rows[i]["HygAbbr"].ToString()!="") { row["provider"]+=" ("+raw.Rows[i]["HygAbbr"].ToString()+")"; } } row["ProvNum"]=raw.Rows[i]["ProvNum"].ToString(); row["ProvHyg"]=raw.Rows[i]["ProvHyg"].ToString(); row["timeAskedToArrive"]=""; timeAskedToArrive=PIn.DateT(raw.Rows[i]["DateTimeAskedToArrive"].ToString()); if(timeAskedToArrive.Year>1880) { row["timeAskedToArrive"]=timeAskedToArrive.ToString("H:mm"); } row["wirelessPhone"]=Lans.g("Appointments","Cell: ")+raw.Rows[i]["WirelessPhone"].ToString(); row["wkPhone"]=Lans.g("Appointments","Wk: ")+raw.Rows[i]["WkPhone"].ToString(); row["writeoffPPO"]=writeoffPPO.ToString(); table.Rows.Add(row); } return table; }
/// <summary>Only called from above method, but must be public for remoting.</summary> public static DataTable GetApptFieldsByApptNums(List<long> aptNums) { if(RemotingClient.RemotingRole==RemotingRole.ClientWeb) { return Meth.GetTable(MethodBase.GetCurrentMethod(),aptNums); } string command="SELECT AptNum,FieldName,FieldValue " +"FROM apptfield " +"WHERE AptNum IN ("; if(aptNums.Count==0) { command+="0"; } else for(int i=0;i<aptNums.Count;i++) { if(i>0) { command+=","; } command+=POut.Long(aptNums[i]); } command+=")"; DataConnection dcon=new DataConnection(); DataTable table= dcon.GetTable(command); table.TableName="ApptFields"; return table; }
///<summary>Pass in one aptNum</summary> public static DataTable GetApptFields(long aptNum) { if(RemotingClient.RemotingRole==RemotingRole.ClientWeb) { return Meth.GetTable(MethodBase.GetCurrentMethod(),aptNum); } string command="SELECT ApptFieldNum,apptfielddef.FieldName,FieldValue " +"FROM apptfielddef " +"LEFT JOIN apptfield ON apptfielddef.FieldName=apptfield.FieldName " +"AND AptNum = "+POut.Long(aptNum)+" " +"ORDER BY apptfielddef.FieldName"; DataConnection dcon=new DataConnection(); DataTable table= dcon.GetTable(command); table.TableName="ApptFields"; return table; }
public static DataTable GetPeriodWaitingRoomTable() { if(RemotingClient.RemotingRole==RemotingRole.ClientWeb) { return Meth.GetTable(MethodBase.GetCurrentMethod()); } //DateTime dateStart=PIn.PDate(strDateStart); //DateTime dateEnd=PIn.PDate(strDateEnd); DataConnection dcon=new DataConnection(); DataTable table=new DataTable("WaitingRoom"); DataRow row; //columns that start with lowercase are altered for display rather than being raw data. table.Columns.Add("patName"); table.Columns.Add("waitTime"); string command="SELECT DateTimeArrived,DateTimeSeated,LName,FName,Preferred,"+DbHelper.Now()+" dateTimeNow " +"FROM appointment,patient " +"WHERE appointment.PatNum=patient.PatNum " +"AND "+DbHelper.DateColumn("AptDateTime")+" = "+POut.Date(DateTime.Now)+" " +"AND DateTimeArrived > "+POut.Date(DateTime.Now)+" "//midnight earlier today +"AND DateTimeArrived < "+DbHelper.Now()+" " +"AND "+DbHelper.DateColumn("DateTimeArrived")+"="+DbHelper.DateColumn("AptDateTime")+" ";//prevents people from getting "stuck" in waiting room. if(DataConnection.DBtype==DatabaseType.Oracle) { command+="AND TO_NUMBER(TO_CHAR(DateTimeSeated,'SSSSS')) = 0 "; } else{ command+="AND TIME(DateTimeSeated) = 0 "; } command+="AND AptStatus IN ("+POut.Int((int)ApptStatus.Complete)+"," +POut.Int((int)ApptStatus.Scheduled)+"," +POut.Int((int)ApptStatus.ASAP)+") "//None of the other statuses +"ORDER BY AptDateTime"; DataTable raw=dcon.GetTable(command); TimeSpan timeArrived; //DateTime timeSeated; DateTime waitTime; Patient pat; DateTime dateTimeNow; //int minutes; for(int i=0;i<raw.Rows.Count;i++) { row=table.NewRow(); pat=new Patient(); pat.LName=raw.Rows[i]["LName"].ToString(); pat.FName=raw.Rows[i]["FName"].ToString(); pat.Preferred=raw.Rows[i]["Preferred"].ToString(); row["patName"]=pat.GetNameLF(); dateTimeNow=PIn.DateT(raw.Rows[i]["dateTimeNow"].ToString()); timeArrived=(PIn.DateT(raw.Rows[i]["DateTimeArrived"].ToString())).TimeOfDay; waitTime=dateTimeNow-timeArrived; row["waitTime"]=waitTime.ToString("H:mm:ss"); //minutes=waitTime.Minutes; //if(waitTime.Hours>0){ // row["waitTime"]+=waitTime.Hours.ToString()+"h "; //minutes-=60*waitTime.Hours; //} //row["waitTime"]+=waitTime.Minutes.ToString()+"m"; table.Rows.Add(row); } return table; }
public static DataTable GetPeriodEmployeeSchedTable(DateTime dateStart,DateTime dateEnd) { if(RemotingClient.RemotingRole==RemotingRole.ClientWeb) { return Meth.GetTable(MethodBase.GetCurrentMethod(),dateStart,dateEnd); } //DateTime dateStart=PIn.PDate(strDateStart); //DateTime dateEnd=PIn.PDate(strDateEnd); DataConnection dcon=new DataConnection(); DataTable table=new DataTable("EmpSched"); DataRow row; //columns that start with lowercase are altered for display rather than being raw data. table.Columns.Add("empName"); table.Columns.Add("schedule"); if(dateStart!=dateEnd) { return table; } string command="SELECT StartTime,StopTime,FName,employee.EmployeeNum " +"FROM employee,schedule " +"WHERE schedule.EmployeeNum=employee.EmployeeNum " +"AND SchedType=3 "//employee +"AND SchedDate = "+POut.Date(dateStart)+" " +"ORDER BY schedule.EmployeeNum,StartTime"; DataTable raw=dcon.GetTable(command); DateTime startTime; DateTime stopTime; for(int i=0;i<raw.Rows.Count;i++) { row=table.NewRow(); if(i==0 || raw.Rows[i]["EmployeeNum"].ToString()!=raw.Rows[i-1]["EmployeeNum"].ToString()){ row["empName"]=raw.Rows[i]["FName"].ToString(); } else{ row["empName"]=""; } if(row["schedule"].ToString()!=""){ row["schedule"]+=","; } startTime=PIn.DateT(raw.Rows[i]["StartTime"].ToString()); stopTime=PIn.DateT(raw.Rows[i]["StopTime"].ToString()); row["schedule"]+=startTime.ToString("h:mm")+"-"+stopTime.ToString("h:mm"); table.Rows.Add(row); } return table; }
///<summary>Set clinicNum to 0 to return 'unassigned' clinics. Otherwise, filters the data set on the clinic num passed in.</summary> public static DataTable GetPeriodEmployeeSchedTable(DateTime dateStart,DateTime dateEnd,long clinicNum) { if(RemotingClient.RemotingRole==RemotingRole.ClientWeb) { return Meth.GetTable(MethodBase.GetCurrentMethod(),dateStart,dateEnd,clinicNum); } DataConnection dcon=new DataConnection(); DataTable table=new DataTable("EmpSched"); DataRow row; //columns that start with lowercase are altered for display rather than being raw data. table.Columns.Add("empName"); table.Columns.Add("schedule"); table.Columns.Add("Note"); if(dateStart!=dateEnd) { return table; } string clinicJoin=""; string clinicWhere=""; if(!PrefC.GetBool(PrefName.EasyNoClinics)) {//Using clinics. clinicJoin="LEFT JOIN userod ON employee.EmployeeNum=userod.EmployeeNum "; if(clinicNum==0) { clinicWhere="AND (userod.ClinicNum IS NULL OR userod.ClinicNum=0) "; } else { clinicWhere="AND userod.ClinicNum="+POut.Long(clinicNum)+" "; } } string command="SELECT StartTime,StopTime,FName,employee.EmployeeNum,Note,schedule.ScheduleNum " +"FROM employee " +"INNER JOIN schedule ON schedule.EmployeeNum=employee.EmployeeNum " +clinicJoin +"WHERE SchedType="+POut.Int((int)ScheduleType.Employee)+" " +"AND SchedDate = "+POut.Date(dateStart)+" " +"AND employee.IsHidden = 0 " +clinicWhere; if(DataConnection.DBtype==DatabaseType.MySql) { command+="GROUP BY schedule.ScheduleNum "; } else { command+="GROUP BY employee.EmployeeNum,StartTime,StopTime,FName,Note,schedule.ScheduleNum "; } command+="ORDER BY FName,StartTime"; DataTable raw=dcon.GetTable(command); DateTime startTime; DateTime stopTime; for(int i=0;i<raw.Rows.Count;i++) { row=table.NewRow(); if(i==0 || raw.Rows[i]["EmployeeNum"].ToString()!=raw.Rows[i-1]["EmployeeNum"].ToString()){ row["empName"]=raw.Rows[i]["FName"].ToString(); } else{ row["empName"]=""; } if(row["schedule"].ToString()!=""){ row["schedule"]+=","; } startTime=PIn.DateT(raw.Rows[i]["StartTime"].ToString()); stopTime=PIn.DateT(raw.Rows[i]["StopTime"].ToString()); row["schedule"]+=startTime.ToString("h:mm")+"-"+stopTime.ToString("h:mm"); row["Note"]=raw.Rows[i]["Note"].ToString(); table.Rows.Add(row); } return table; }