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