///<summary>This is used by FormImageViewer to get a list of paths based on supplied list of DocNums. The reason is that later we will allow sharing of documents, so the paths may not be in the current patient folder.</summary>
public static ArrayList GetPaths(ArrayList docNums)
{
if (docNums.Count == 0)
{
return(new ArrayList());
}
string command = "SELECT document.DocNum,document.FileName,patient.ImageFolder "
+ "FROM document "
+ "LEFT JOIN patient ON patient.PatNum=document.PatNum "
+ "WHERE document.DocNum = '" + docNums[0].ToString() + "'";
for (int i = 1; i < docNums.Count; i++)
{
command += " OR document.DocNum = '" + docNums[i].ToString() + "'";
}
//remember, they will not be in the correct order.
DataTable table = General2.GetTable(command);
Hashtable hList = new Hashtable(); //key=docNum, value=path
//one row for each document, but in the wrong order
for (int i = 0; i < table.Rows.Count; i++)
{
//We do not need to check if A to Z folders are being used here, because
//thumbnails are not visible from the chart module when A to Z are disabled,
//making it impossible to launch the form image viewer (the only place this
//function is called from.
hList.Add(PIn.PInt(table.Rows[i][0].ToString()),
ODFileUtils.CombinePaths(new string[] { FileStoreSettings.GetPreferredImagePath,
PIn.PString(table.Rows[i][2].ToString()).Substring(0, 1).ToUpper(),
PIn.PString(table.Rows[i][2].ToString()),
PIn.PString(table.Rows[i][1].ToString()), }));
}
ArrayList retVal = new ArrayList();
for (int i = 0; i < docNums.Count; i++)
{
retVal.Add((string)hList[(int)docNums[i]]);
}
return(retVal);
}
///<summary></summary>
public static Userod GetUser(int userNum)
{
Userod user = null;
for (int i = 0; i < RawData.Rows.Count; i++)
{
if (RawData.Rows[i]["UserNum"].ToString() != userNum.ToString())
{
continue;
}
user = new Userod();
user.UserNum = PIn.PInt(RawData.Rows[i][0].ToString());
user.UserName = PIn.PString(RawData.Rows[i][1].ToString());
user.Password = PIn.PString(RawData.Rows[i][2].ToString());
user.UserGroupNum = PIn.PInt(RawData.Rows[i][3].ToString());
user.EmployeeNum = PIn.PInt(RawData.Rows[i][4].ToString());
user.ClinicNum = PIn.PInt(RawData.Rows[i][5].ToString());
user.ProvNum = PIn.PInt(RawData.Rows[i][6].ToString());
user.IsHidden = PIn.PBool(RawData.Rows[i][7].ToString());
}
return(user);
}
public static DataTable GetProgNotes(int patNum, bool isAuditMode)
{
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("AptNum");
table.Columns.Add("CodeNum");
table.Columns.Add("colorBackG");
table.Columns.Add("colorText");
table.Columns.Add("CommlogNum");
table.Columns.Add("description");
table.Columns.Add("dx");
table.Columns.Add("Dx");
table.Columns.Add("LabCaseNum");
table.Columns.Add("note");
table.Columns.Add("PatNum"); //only used for Commlog
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("prov");
table.Columns.Add("RxNum");
table.Columns.Add("signature");
table.Columns.Add("Surf");
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>();
//Procedures-----------------------------------------------------------------------------------------------------
string command = "SELECT LaymanTerm,ProcDate,ProcStatus,ToothNum,Surf,Dx,UnitQty,procedurelog.BaseUnits,"
+ "procedurecode.ProcCode,ProcNum,procedurecode.Descript,"
+ "provider.Abbr,ProcFee,ProcNumLab,appointment.AptDateTime,Priority,ToothRange,procedurelog.CodeNum "
+ "FROM procedurelog "
+ "LEFT JOIN procedurecode ON procedurecode.CodeNum=procedurelog.CodeNum "
+ "LEFT JOIN provider ON provider.ProvNum=procedurelog.ProvNum "
+ "LEFT JOIN appointment ON appointment.AptNum=procedurelog.AptNum "
+ "AND (appointment.AptStatus=" + POut.PInt((int)ApptStatus.Scheduled)
+ " OR appointment.AptStatus=" + POut.PInt((int)ApptStatus.ASAP)
+ " OR appointment.AptStatus=" + POut.PInt((int)ApptStatus.Broken)
+ " OR appointment.AptStatus=" + POut.PInt((int)ApptStatus.Complete)
+ ") WHERE procedurelog.PatNum=" + POut.PInt(patNum);
if (!isAuditMode)
{
command += " AND ProcStatus !=6"; //don't include deleted
}
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.PInt(patNum)
+ " ORDER BY EntryDateTime"; // but this helps when looping for notes
DataTable rawNotes = dcon.GetTable(command);
DateTime dateT;
List <DataRow> labRows = new List <DataRow>(); //Canadian lab procs, which must be added in a loop at the very end.
for (int i = 0; i < rawProcs.Rows.Count; i++)
{
row = table.NewRow();
row["aptDateTime"] = PIn.PDateT(rawProcs.Rows[i]["AptDateTime"].ToString());
row["AptNum"] = 0;
row["CodeNum"] = rawProcs.Rows[i]["CodeNum"].ToString();
row["colorBackG"] = Color.White.ToArgb();
if (((DateTime)row["aptDateTime"]).Date == DateTime.Today)
{
row["colorBackG"] = DefB.Long[(int)DefCat.MiscColors][6].ItemColor.ToArgb().ToString();
}
switch ((ProcStat)PIn.PInt(rawProcs.Rows[i]["ProcStatus"].ToString()))
{
case ProcStat.TP:
row["colorText"] = DefB.Long[(int)DefCat.ProgNoteColors][0].ItemColor.ToArgb().ToString();
break;
case ProcStat.C:
row["colorText"] = DefB.Long[(int)DefCat.ProgNoteColors][1].ItemColor.ToArgb().ToString();
break;
case ProcStat.EC:
row["colorText"] = DefB.Long[(int)DefCat.ProgNoteColors][2].ItemColor.ToArgb().ToString();
break;
case ProcStat.EO:
row["colorText"] = DefB.Long[(int)DefCat.ProgNoteColors][3].ItemColor.ToArgb().ToString();
break;
case ProcStat.R:
row["colorText"] = DefB.Long[(int)DefCat.ProgNoteColors][4].ItemColor.ToArgb().ToString();
break;
case ProcStat.D:
row["colorText"] = Color.Black.ToArgb().ToString();
break;
}
row["CommlogNum"] = 0;
if (rawProcs.Rows[i]["LaymanTerm"].ToString() == "")
{
row["description"] = rawProcs.Rows[i]["Descript"].ToString();
}
else
{
row["description"] = rawProcs.Rows[i]["LaymanTerm"].ToString();
}
row["dx"] = DefB.GetValue(DefCat.Diagnosis, PIn.PInt(rawProcs.Rows[i]["Dx"].ToString()));
row["Dx"] = rawProcs.Rows[i]["Dx"].ToString();
row["LabCaseNum"] = 0;
//note-----------------------------------------------------------------------------------------------------------
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.PDateT(rawNotes.Rows[n]["EntryDateTime"].ToString()).ToString();
row["note"] += " " + UserodB.GetName(PIn.PInt(rawNotes.Rows[n]["UserNum"].ToString()));
if (rawNotes.Rows[n]["SigPresent"].ToString() == "1")
{
row["note"] += " " + Lan.g("ChartModule", "(signed)");
}
row["note"] += "\r\n" + rawNotes.Rows[n]["Note"].ToString();
}
}
else //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["user"] = UserodB.GetName(PIn.PInt(rawNotes.Rows[n]["UserNum"].ToString()));
row["note"] = rawNotes.Rows[n]["Note"].ToString();
if (rawNotes.Rows[n]["SigPresent"].ToString() == "1")
{
row["signature"] = Lan.g("ChartModule", "Signed");
}
else
{
row["signature"] = "";
}
break; //out of note loop.
}
}
row["Priority"] = rawProcs.Rows[i]["Priority"].ToString();
row["ProcCode"] = rawProcs.Rows[i]["ProcCode"].ToString();
dateT = PIn.PDateT(rawProcs.Rows[i]["ProcDate"].ToString());
if (dateT.Year < 1880)
{
row["procDate"] = "";
}
else
{
row["procDate"] = dateT.ToShortDateString();
}
row["ProcDate"] = dateT;
double amt = PIn.PDouble(rawProcs.Rows[i]["ProcFee"].ToString());
int qty = PIn.PInt(rawProcs.Rows[i]["UnitQty"].ToString()) + PIn.PInt(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"] = Lan.g("enumProcStat", ((ProcStat)PIn.PInt(rawProcs.Rows[i]["ProcStatus"].ToString())).ToString());
row["ProcStatus"] = rawProcs.Rows[i]["ProcStatus"].ToString();
if (dateT.TimeOfDay != TimeSpan.Zero)
{
row["procTime"] = dateT.ToString("h:mm") + dateT.ToString("%t").ToLower();
}
row["prov"] = rawProcs.Rows[i]["Abbr"].ToString();
row["RxNum"] = 0;
row["Surf"] = rawProcs.Rows[i]["Surf"].ToString();
row["toothNum"] = Tooth.ToInternat(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
}
}
//Commlog-----------------------------------------------------------------------------------------------------------
command = "SELECT CommlogNum,CommDateTime,CommType,Note,commlog.PatNum,p1.FName "
+ "FROM patient p1,patient p2,commlog "
+ "WHERE commlog.PatNum=p1.PatNum "
+ "AND p1.Guarantor=p2.Guarantor "
+ "AND p2.PatNum=" + POut.PInt(patNum)
+ " AND IsStatementSent=0 ORDER BY CommDateTime";
DataTable rawComm = dcon.GetTable(command);
string txt;
for (int i = 0; i < rawComm.Rows.Count; i++)
{
row = table.NewRow();
row["AptNum"] = 0;
row["colorBackG"] = Color.White.ToArgb();
row["colorText"] = DefB.Long[(int)DefCat.ProgNoteColors][6].ItemColor.ToArgb().ToString();
row["CommlogNum"] = rawComm.Rows[i]["CommlogNum"].ToString();
if (rawComm.Rows[i]["PatNum"].ToString() == patNum.ToString())
{
txt = "";
}
else
{
txt = "(" + rawComm.Rows[i]["FName"].ToString() + ") ";
}
row["description"] = txt + Lan.g("ChartModule", "Comm - ")
+ DefB.GetName(DefCat.CommLogTypes, PIn.PInt(rawComm.Rows[i]["CommType"].ToString()));
row["LabCaseNum"] = 0;
row["note"] = rawComm.Rows[i]["Note"].ToString();
row["PatNum"] = rawComm.Rows[i]["PatNum"].ToString();
dateT = PIn.PDateT(rawComm.Rows[i]["CommDateTime"].ToString());
if (dateT.Year < 1880)
{
row["procDate"] = "";
}
else
{
row["procDate"] = dateT.ToShortDateString();
}
row["ProcDate"] = dateT;
if (dateT.TimeOfDay != TimeSpan.Zero)
{
row["procTime"] = dateT.ToString("h:mm") + dateT.ToString("%t").ToLower();
}
row["ProcNum"] = 0;
row["RxNum"] = 0;
rows.Add(row);
}
//Rx------------------------------------------------------------------------------------------------------------------
command = "SELECT RxNum,RxDate,Drug,Disp,ProvNum,Notes FROM rxpat WHERE PatNum=" + POut.PInt(patNum)
+ " ORDER BY RxDate";
DataTable rawRx = dcon.GetTable(command);
for (int i = 0; i < rawRx.Rows.Count; i++)
{
row = table.NewRow();
row["AptNum"] = 0;
row["colorBackG"] = Color.White.ToArgb();
row["colorText"] = DefB.Long[(int)DefCat.ProgNoteColors][5].ItemColor.ToArgb().ToString();
row["CommlogNum"] = 0;
row["description"] = Lan.g("ChartModule", "Rx - ") + rawRx.Rows[i]["Drug"].ToString() + " - #" + rawRx.Rows[i]["Disp"].ToString();
row["LabCaseNum"] = 0;
row["note"] = rawRx.Rows[i]["Notes"].ToString();
dateT = PIn.PDate(rawRx.Rows[i]["RxDate"].ToString());
if (dateT.Year < 1880)
{
row["procDate"] = "";
}
else
{
row["procDate"] = dateT.ToShortDateString();
}
row["ProcDate"] = dateT;
row["ProcNum"] = 0;
//row["prov"]=ProviderB. PIn.PInt(rawRx.Rows[i]["ProvNum"].ToString());
row["RxNum"] = rawRx.Rows[i]["RxNum"].ToString();
rows.Add(row);
}
//LabCase------------------------------------------------------------------------------------------------------------------
command = "SELECT labcase.*,Description,Phone FROM labcase,laboratory "
+ "WHERE labcase.LaboratoryNum=laboratory.LaboratoryNum "
+ "AND PatNum=" + POut.PInt(patNum)
+ " ORDER BY DateTimeCreated";
DataTable rawLab = dcon.GetTable(command);
DateTime duedate;
for (int i = 0; i < rawLab.Rows.Count; i++)
{
row = table.NewRow();
row["AptNum"] = 0;
row["colorBackG"] = Color.White.ToArgb();
row["colorText"] = DefB.Long[(int)DefCat.ProgNoteColors][7].ItemColor.ToArgb().ToString();
row["CommlogNum"] = 0;
row["description"] = Lan.g("ChartModule", "LabCase - ") + rawLab.Rows[i]["Description"].ToString() + " "
+ rawLab.Rows[i]["Phone"].ToString();
if (PIn.PDate(rawLab.Rows[i]["DateTimeDue"].ToString()).Year > 1880)
{
duedate = PIn.PDateT(rawLab.Rows[i]["DateTimeDue"].ToString());
row["description"] += "\r\n" + Lan.g("ChartModule", "Due") + " " + duedate.ToString("ddd") + " "
+ duedate.ToShortDateString() + " " + duedate.ToShortTimeString();
}
if (PIn.PDate(rawLab.Rows[i]["DateTimeChecked"].ToString()).Year > 1880)
{
row["description"] += "\r\n" + Lan.g("ChartModule", "Quality Checked");
}
else if (PIn.PDate(rawLab.Rows[i]["DateTimeRecd"].ToString()).Year > 1880)
{
row["description"] += "\r\n" + Lan.g("ChartModule", "Received");
}
else if (PIn.PDate(rawLab.Rows[i]["DateTimeSent"].ToString()).Year > 1880)
{
row["description"] += "\r\n" + Lan.g("ChartModule", "Sent");
}
row["LabCaseNum"] = rawLab.Rows[i]["LabCaseNum"].ToString();
row["note"] = rawLab.Rows[i]["Instructions"].ToString();
dateT = PIn.PDateT(rawLab.Rows[i]["DateTimeCreated"].ToString());
if (dateT.Year < 1880)
{
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;
row["ProcNum"] = 0;
row["RxNum"] = 0;
rows.Add(row);
}
//Appointments---------------------------------------------------------------------------------------------------------
command = "SELECT * FROM appointment WHERE PatNum=" + POut.PInt(patNum)
+ " ORDER BY AptDateTime";
//+" AND AptStatus != 6"//do not include planned appts.
DataTable rawApt = dcon.GetTable(command);
int apptStatus;
for (int i = 0; i < rawApt.Rows.Count; i++)
{
row = table.NewRow();
row["AptNum"] = rawApt.Rows[i]["AptNum"].ToString();
row["colorBackG"] = Color.White.ToArgb();
dateT = PIn.PDateT(rawApt.Rows[i]["AptDateTime"].ToString());
apptStatus = PIn.PInt(rawApt.Rows[i]["AptStatus"].ToString());
row["colorText"] = DefB.Long[(int)DefCat.ProgNoteColors][8].ItemColor.ToArgb().ToString();
row["CommlogNum"] = 0;
row["description"] = Lan.g("ChartModule", "Appointment - ") + dateT.ToShortTimeString() + "\r\n"
+ rawApt.Rows[i]["ProcDescript"].ToString();
if (dateT.Date.Date == DateTime.Today.Date)
{
row["colorBackG"] = DefB.Long[(int)DefCat.ProgNoteColors][9].ItemColor.ToArgb().ToString(); //deliniates nicely between old appts
row["colorText"] = DefB.Long[(int)DefCat.ProgNoteColors][8].ItemColor.ToArgb().ToString();
}
else if (dateT.Date < DateTime.Today)
{
row["colorBackG"] = DefB.Long[(int)DefCat.ProgNoteColors][11].ItemColor.ToArgb().ToString();
row["colorText"] = DefB.Long[(int)DefCat.ProgNoteColors][10].ItemColor.ToArgb().ToString();
}
else if (dateT.Date > DateTime.Today)
{
row["colorBackG"] = DefB.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"] = DefB.Long[(int)DefCat.ProgNoteColors][12].ItemColor.ToArgb().ToString();
}
if (apptStatus == (int)ApptStatus.Broken)
{
row["colorText"] = DefB.Long[(int)DefCat.ProgNoteColors][14].ItemColor.ToArgb().ToString();
row["colorBackG"] = DefB.Long[(int)DefCat.ProgNoteColors][15].ItemColor.ToArgb().ToString();
row["description"] = Lan.g("ChartModule", "BROKEN Appointment - ") + dateT.ToShortTimeString() + "\r\n"
+ rawApt.Rows[i]["ProcDescript"].ToString();
}
else if (apptStatus == (int)ApptStatus.UnschedList)
{
row["colorText"] = DefB.Long[(int)DefCat.ProgNoteColors][14].ItemColor.ToArgb().ToString();
row["colorBackG"] = DefB.Long[(int)DefCat.ProgNoteColors][15].ItemColor.ToArgb().ToString();
row["description"] = Lan.g("ChartModule", "UNSCHEDULED Appointment - ") + dateT.ToShortTimeString() + "\r\n"
+ rawApt.Rows[i]["ProcDescript"].ToString();
}
else if (apptStatus == (int)ApptStatus.Planned)
{
row["colorText"] = DefB.Long[(int)DefCat.ProgNoteColors][16].ItemColor.ToArgb().ToString();
row["colorBackG"] = DefB.Long[(int)DefCat.ProgNoteColors][17].ItemColor.ToArgb().ToString();
row["description"] = Lan.g("ChartModule", "PLANNED Appointment") + "\r\n"
+ rawApt.Rows[i]["ProcDescript"].ToString();
}
else if (apptStatus == (int)ApptStatus.PtNote)
{
row["colorText"] = DefB.Long[(int)DefCat.ProgNoteColors][18].ItemColor.ToArgb().ToString();
row["colorBackG"] = DefB.Long[(int)DefCat.ProgNoteColors][19].ItemColor.ToArgb().ToString();
row["description"] = Lan.g("ChartModule", "*** Patient NOTE *** - ") + dateT.ToShortTimeString();
}
else if (apptStatus == (int)ApptStatus.PtNoteCompleted)
{
row["colorText"] = DefB.Long[(int)DefCat.ProgNoteColors][20].ItemColor.ToArgb().ToString();
row["colorBackG"] = DefB.Long[(int)DefCat.ProgNoteColors][21].ItemColor.ToArgb().ToString();
row["description"] = Lan.g("ChartModule", "** Complete Patient NOTE ** - ") + dateT.ToShortTimeString();
}
row["LabCaseNum"] = 0;
row["note"] = rawApt.Rows[i]["Note"].ToString();
if (dateT.Year < 1880)
{
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;
row["ProcNum"] = 0;
row["RxNum"] = 0;
rows.Add(row);
} //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;
}
}
}
for (int i = 0; i < rows.Count; i++)
{
table.Rows.Add(rows[i]);
}
return(table);
}
private static DataTable GetProcTable(string patNum, string aptNum, string apptStatus)
{
DataConnection dcon = new DataConnection();
DataTable table = new DataTable("Procedure");
DataRow row;
//columns that start with lowercase are altered for display rather than being raw data.
table.Columns.Add("attached"); //0 or 1
table.Columns.Add("CodeNum");
table.Columns.Add("descript");
table.Columns.Add("fee");
table.Columns.Add("priority");
table.Columns.Add("ProcCode");
table.Columns.Add("ProcNum");
table.Columns.Add("ProcStatus");
table.Columns.Add("ProvNum");
table.Columns.Add("status");
table.Columns.Add("toothNum");
table.Columns.Add("Surf");
string command = "SELECT procedurecode.ProcCode,AptNum,PlannedAptNum,Priority,ProcFee,ProcNum,ProcStatus,Surf,ToothNum, "
+ "procedurecode.Descript,procedurelog.CodeNum,procedurelog.ProvNum "
+ "FROM procedurelog LEFT JOIN procedurecode ON procedurelog.CodeNum=procedurecode.CodeNum "
+ "WHERE PatNum=" + patNum //sort later
+ " AND (ProcStatus=1 OR "; //tp
if (apptStatus == "6") //planned
{
command += "PlannedAptNum=" + aptNum + ")";
}
else
{
command += "AptNum=" + aptNum + ") ";
//+"AND (AptNum=0 OR AptNum="+aptNum+")";//exclude procs attached to other appts.
}
DataTable rawProc = dcon.GetTable(command);
for (int i = 0; i < rawProc.Rows.Count; i++)
{
row = table.NewRow();
if (apptStatus == "6") //planned
{
row["attached"] = (rawProc.Rows[i]["PlannedAptNum"].ToString() == aptNum) ? "1" : "0";
}
else
{
row["attached"] = (rawProc.Rows[i]["AptNum"].ToString() == aptNum) ? "1" : "0";
}
row["CodeNum"] = rawProc.Rows[i]["CodeNum"].ToString();
row["descript"] = "";
if (rawProc.Rows[i]["AptNum"].ToString() != "0" && rawProc.Rows[i]["AptNum"].ToString() != aptNum)
{
row["descript"] = Lan.g("FormApptEdit", "(other appt)");
}
row["descript"] += rawProc.Rows[i]["Descript"].ToString();
row["fee"] = PIn.PDouble(rawProc.Rows[i]["ProcFee"].ToString()).ToString("F");
row["priority"] = DefB.GetName(DefCat.TxPriorities, PIn.PInt(rawProc.Rows[i]["Priority"].ToString()));
row["ProcCode"] = rawProc.Rows[i]["ProcCode"].ToString();
row["ProcNum"] = rawProc.Rows[i]["ProcNum"].ToString();
row["ProcStatus"] = rawProc.Rows[i]["ProcStatus"].ToString();
row["ProvNum"] = rawProc.Rows[i]["ProvNum"].ToString();
row["status"] = ((ProcStat)PIn.PInt(rawProc.Rows[i]["ProcStatus"].ToString())).ToString();
row["toothNum"] = Tooth.ToInternat(rawProc.Rows[i]["ToothNum"].ToString());
row["Surf"] = rawProc.Rows[i]["Surf"].ToString();
table.Rows.Add(row);
}
return(table);
}
///<summary>If aptnum is specified, then the dates are ignored. If getting data for one planned appt, then pass isPlanned=true. This changes which procedures are retrieved.</summary>
private static DataTable GetPeriodApptsTable(string strDateStart, string strDateEnd, string strAptNum, string strIsPlanned)
{
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("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("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("famFinUrgNote");
table.Columns.Add("hmPhone");
table.Columns.Add("ImageFolder");
table.Columns.Add("insurance");
table.Columns.Add("IsHygiene");
table.Columns.Add("lab");
table.Columns.Add("MedUrgNote");
table.Columns.Add("Note");
table.Columns.Add("Op");
table.Columns.Add("patientName");
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("production");
table.Columns.Add("productionVal");
table.Columns.Add("provider");
table.Columns.Add("ProvHyg");
table.Columns.Add("ProvNum");
table.Columns.Add("wkPhone");
table.Columns.Add("wirelessPhone");
string command = "SELECT p1.Abbr ProvAbbr,p2.Abbr HygAbbr,patient.AddrNote,"
+ "patient.ApptModNote,AptDateTime,appointment.AptNum,AptStatus,Assistant,"
+ "patient.BillingType,patient.BirthDate,patient.Guarantor,"
+ "patient.ChartNumber,Confirmed,patient.CreditType,DateTimeChecked,DateTimeRecd,DateTimeSent,"
+ "guar.FamFinUrgNote,patient.FName,patient.HmPhone,patient.ImageFolder,IsHygiene,IsNewPatient,"
+ "LabCaseNum,patient.LName,patient.MedUrgNote,patient.MiddleI,Note,Op,appointment.PatNum,"
+ "Pattern,patplan.PlanNum,patient.PreferConfirmMethod,patient.PreferContactMethod,patient.Preferred,"
+ "patient.PreferRecallMethod,patient.Premed,"
+ "(SELECT SUM(ProcFee) FROM procedurelog ";
if (isPlanned)
{
command += "WHERE procedurelog.PlannedAptNum=appointment.AptNum) Production, ";
}
else
{
command += "WHERE procedurelog.AptNum=appointment.AptNum) Production, ";
}
command += "ProvHyg,appointment.ProvNum,patient.WirelessPhone,patient.WkPhone "
+ "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 ";
if (aptNum == 0)
{
command += "WHERE AptDateTime >= " + POut.PDate(dateStart) + " "
+ "AND AptDateTime < " + POut.PDate(dateEnd.AddDays(1)) + " "
+ "AND (AptStatus=1 OR AptStatus=2 OR AptStatus=4 OR AptStatus=5 OR AptStatus=7 OR AptStatus=8) ";
}
else
{
command += "WHERE appointment.AptNum=" + POut.PInt(aptNum);
}
command += " GROUP BY appointment.AptNum";
DataTable raw = dcon.GetTable(command);
command = "SELECT AbbrDesc,procedurelog.AptNum,procedurelog.CodeNum,PlannedAptNum,Surf,ToothNum,TreatArea "
+ "FROM procedurelog,appointment,procedurecode ";
if (isPlanned)
{
command += "WHERE procedurelog.PlannedAptNum=appointment.AptNum ";
}
else
{
command += "WHERE procedurelog.AptNum=appointment.AptNum ";
}
command += "AND procedurelog.CodeNum=procedurecode.CodeNum ";
if (aptNum == 0)
{
command += "AND AptDateTime >= " + POut.PDate(dateStart) + " "
+ "AND AptDateTime < " + POut.PDate(dateEnd.AddDays(1)) + " ";
}
else
{
command += "AND appointment.AptNum=" + POut.PInt(aptNum);
}
DataTable rawProc = dcon.GetTable(command);
//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.PDate(DateTime.Now.AddYears(-1)) + " "
+ "AND procedurelog.ProcDate <= " + POut.PDate(DateTime.Now) + " "
+ "GROUP BY patient.Guarantor";
DataTable rawInsProc = dcon.GetTable(command);
DateTime aptDate;
TimeSpan span;
int hours;
int minutes;
DateTime labDate;
DateTime birthdate;
for (int i = 0; i < raw.Rows.Count; i++)
{
row = table.NewRow();
if (raw.Rows[i]["AddrNote"].ToString() != "")
{
row["addrNote"] = Lan.g("Appointments", "AddrNote: ") + raw.Rows[i]["AddrNote"].ToString();
}
aptDate = PIn.PDateT(raw.Rows[i]["AptDateTime"].ToString());
row["AptDateTime"] = aptDate;
birthdate = PIn.PDate(raw.Rows[i]["Birthdate"].ToString());
row["age"] = Lan.g("Appointments", "Age: ");
if (birthdate.Year > 1880)
{
row["age"] += PatientB.DateToAgeString(birthdate);
}
else
{
row["age"] += "?";
}
if (raw.Rows[i]["ApptModNote"].ToString() != "")
{
row["apptModNote"] = Lan.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() + Lan.g("Appointments", " Min");
}
else if (hours == 1)
{
row["aptLength"] = hours.ToString() + Lan.g("Appointments", " Hr, ")
+ minutes.ToString() + Lan.g("Appointments", " Min");
}
else
{
row["aptLength"] = hours.ToString() + Lan.g("Appointments", " Hrs, ")
+ minutes.ToString() + Lan.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["billingType"] = DefB.GetName(DefCat.BillingTypes, PIn.PInt(raw.Rows[i]["BillingType"].ToString()));
if (raw.Rows[i]["ChartNumber"].ToString() != "")
{
row["chartNumber"] = raw.Rows[i]["ChartNumber"].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() + " "
+ PatientB.GetNameLF(raw.Rows[i]["LName"].ToString(), raw.Rows[i]["FName"].ToString(),
raw.Rows[i]["Preferred"].ToString(), raw.Rows[i]["MiddleI"].ToString());
row["confirmed"] = DefB.GetName(DefCat.ApptConfirmed, PIn.PInt(raw.Rows[i]["Confirmed"].ToString()));
row["Confirmed"] = raw.Rows[i]["Confirmed"].ToString();
row["contactMethods"] = "";
if (raw.Rows[i]["PreferConfirmMethod"].ToString() != "0")
{
row["contactMethods"] += Lan.g("Appointments", "Confirm Method: ")
+ ((ContactMethod)PIn.PInt(raw.Rows[i]["PreferConfirmMethod"].ToString())).ToString();
}
if (raw.Rows[i]["PreferContactMethod"].ToString() != "0")
{
if (row["contactMethods"].ToString() != "")
{
row["contactMethods"] += "\r\n";
}
row["contactMethods"] += Lan.g("Appointments", "Contact Method: ")
+ ((ContactMethod)PIn.PInt(raw.Rows[i]["PreferContactMethod"].ToString())).ToString();
}
if (raw.Rows[i]["PreferRecallMethod"].ToString() != "0")
{
if (row["contactMethods"].ToString() != "")
{
row["contactMethods"] += "\r\n";
}
row["contactMethods"] += Lan.g("Appointments", "Recall Method: ")
+ ((ContactMethod)PIn.PInt(raw.Rows[i]["PreferRecallMethod"].ToString())).ToString();
}
row["creditIns"] = raw.Rows[i]["CreditType"].ToString();
//figure out if pt's family has ins claims that need to be created
bool InsToSend = false;
for (int j = 0; j < rawInsProc.Rows.Count; j++)
{
if (raw.Rows[i]["PlanNum"].ToString() != "" && raw.Rows[i]["PlanNum"].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;
}
}
}
if (InsToSend)
{
row["creditIns"] += "!";
}
else if (raw.Rows[i]["PlanNum"].ToString() != "" && raw.Rows[i]["PlanNum"].ToString() != "0")
{
row["creditIns"] += "I";
}
if (raw.Rows[i]["FamFinUrgNote"].ToString() != "")
{
row["famFinUrgNote"] = Lan.g("Appointments", "FamFinUrgNote: ") + raw.Rows[i]["FamFinUrgNote"].ToString();
}
row["hmPhone"] = Lan.g("Appointments", "Hm: ") + raw.Rows[i]["HmPhone"].ToString();
row["ImageFolder"] = raw.Rows[i]["ImageFolder"].ToString();
if (raw.Rows[i]["PlanNum"].ToString() != "" && raw.Rows[i]["PlanNum"].ToString() != "0")
{
row["insurance"] = Lan.g("Appointments", "Insured");
}
row["IsHygiene"] = raw.Rows[i]["IsHygiene"].ToString();
row["lab"] = "";
if (raw.Rows[i]["LabCaseNum"].ToString() != "")
{
labDate = PIn.PDateT(raw.Rows[i]["DateTimeChecked"].ToString());
if (labDate.Year > 1880)
{
row["lab"] = Lan.g("Appointments", "Lab Quality Checked");
}
else
{
labDate = PIn.PDateT(raw.Rows[i]["DateTimeRecd"].ToString());
if (labDate.Year > 1880)
{
row["lab"] = Lan.g("Appointments", "Lab Received");
}
else
{
labDate = PIn.PDateT(raw.Rows[i]["DateTimeSent"].ToString());
if (labDate.Year > 1880)
{
row["lab"] = Lan.g("Appointments", "Lab Sent"); //sent but not received
}
else
{
row["lab"] = Lan.g("Appointments", "Lab Not Sent");
}
}
}
}
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"] += PatientB.GetNameLF(raw.Rows[i]["LName"].ToString(), raw.Rows[i]["FName"].ToString(),
raw.Rows[i]["Preferred"].ToString(), raw.Rows[i]["MiddleI"].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() + " "
+ PatientB.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();
if (raw.Rows[i]["Premed"].ToString() == "1")
{
row["preMedFlag"] = Lan.g("Appointments", "Premedicate");
}
row["procs"] = "";
for (int p = 0; p < rawProc.Rows.Count; p++)
{
if (!isPlanned && rawProc.Rows[p]["AptNum"].ToString() != raw.Rows[i]["AptNum"].ToString())
{
continue;
}
if (isPlanned && rawProc.Rows[p]["PlannedAptNum"].ToString() != raw.Rows[i]["AptNum"].ToString())
{
continue;
}
if (row["procs"].ToString() != "")
{
row["procs"] += ", ";
}
switch (rawProc.Rows[p]["TreatArea"].ToString())
{
case "1": //TreatmentArea.Surf:
row["procs"] += "#" + Tooth.ToInternat(rawProc.Rows[p]["ToothNum"].ToString()) + "-"
+ rawProc.Rows[p]["Surf"].ToString() + "-"; //""#12-MOD-"
break;
case "2": //TreatmentArea.Tooth:
row["procs"] += "#" + Tooth.ToInternat(rawProc.Rows[p]["ToothNum"].ToString()) + "-"; //"#12-"
break;
default: //area 3 or 0 (mouth)
break;
case "4": //TreatmentArea.Quad:
row["procs"] += rawProc.Rows[p]["Surf"].ToString() + "-"; //"UL-"
break;
case "5": //TreatmentArea.Sextant:
row["procs"] += "S" + rawProc.Rows[p]["Surf"].ToString() + "-"; //"S2-"
break;
case "6": //TreatmentArea.Arch:
row["procs"] += rawProc.Rows[p]["Surf"].ToString() + "-"; //"U-"
break;
case "7": //TreatmentArea.ToothRange:
//strLine+=table.Rows[j][13].ToString()+" ";//don't show range
break;
}
row["procs"] += rawProc.Rows[p]["AbbrDesc"].ToString();
}
row["production"] = PIn.PDouble(raw.Rows[i]["Production"].ToString()).ToString("c");
row["productionVal"] = 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["wirelessPhone"] = Lan.g("Appointments", "Cell: ") + raw.Rows[i]["WirelessPhone"].ToString();
row["wkPhone"] = Lan.g("Appointments", "Wk: ") + raw.Rows[i]["WkPhone"].ToString();
table.Rows.Add(row);
}
return(table);
}
private static DataTable GetPatTable(string patNum)
{
DataConnection dcon = new DataConnection();
DataTable table = new DataTable("Patient");
//columns that start with lowercase are altered for display rather than being raw data.
table.Columns.Add("field");
table.Columns.Add("value");
string command = "SELECT * FROM patient WHERE PatNum=" + patNum;
DataTable rawPat = dcon.GetTable(command);
DataRow row;
//Patient Name--------------------------------------------------------------------------
row = table.NewRow();
row["field"] = Lan.g("FormApptEdit", "Name");
row["value"] = PatientB.GetNameLF(rawPat.Rows[0]["LName"].ToString(), rawPat.Rows[0]["FName"].ToString(),
rawPat.Rows[0]["Preferred"].ToString(), rawPat.Rows[0]["MiddleI"].ToString());
table.Rows.Add(row);
//Patient First Name--------------------------------------------------------------------
row = table.NewRow();
row["field"] = Lan.g("FormApptEdit", "First Name");
row["value"] = rawPat.Rows[0]["FName"];
table.Rows.Add(row);
//Patient Last name---------------------------------------------------------------------
row = table.NewRow();
row["field"] = Lan.g("FormApptEdit", "Last Name");
row["value"] = rawPat.Rows[0]["LName"];
table.Rows.Add(row);
//Patient middle initial----------------------------------------------------------------
row = table.NewRow();
row["field"] = Lan.g("FormApptEdit", "Middle Initial");
row["value"] = rawPat.Rows[0]["MiddleI"];
table.Rows.Add(row);
//Patient home phone--------------------------------------------------------------------
row = table.NewRow();
row["field"] = Lan.g("FormApptEdit", "Home Phone");
row["value"] = rawPat.Rows[0]["HmPhone"];
table.Rows.Add(row);
//Patient work phone--------------------------------------------------------------------
row = table.NewRow();
row["field"] = Lan.g("FormApptEdit", "Work Phone");
row["value"] = rawPat.Rows[0]["WkPhone"];
table.Rows.Add(row);
//Patient wireless phone----------------------------------------------------------------
row = table.NewRow();
row["field"] = Lan.g("FormApptEdit", "Wireless Phone");
row["value"] = rawPat.Rows[0]["WirelessPhone"];
table.Rows.Add(row);
//Patient credit type-------------------------------------------------------------------
row = table.NewRow();
row["field"] = Lan.g("FormApptEdit", "Credit Type");
row["value"] = rawPat.Rows[0]["CreditType"];
table.Rows.Add(row);
//Patient billing type------------------------------------------------------------------
row = table.NewRow();
row["field"] = Lan.g("FormApptEdit", "Billing Type");
row["value"] = DefB.GetName(DefCat.BillingTypes, PIn.PInt(rawPat.Rows[0]["BillingType"].ToString()));
table.Rows.Add(row);
//Patient total balance-----------------------------------------------------------------
row = table.NewRow();
row["field"] = Lan.g("FormApptEdit", "Total Balance");
double totalBalance = PIn.PDouble(rawPat.Rows[0]["EstBalance"].ToString());
row["value"] = totalBalance.ToString("F");
table.Rows.Add(row);
//Patient address and phone notes-------------------------------------------------------
row = table.NewRow();
row["field"] = Lan.g("FormApptEdit", "Address and Phone Notes");
row["value"] = rawPat.Rows[0]["AddrNote"];
table.Rows.Add(row);
//Patient family balance----------------------------------------------------------------
command = "SELECT BalTotal,InsEst FROM patient WHERE Guarantor='"
+ rawPat.Rows[0]["Guarantor"].ToString() + "'";
DataTable familyBalance = dcon.GetTable(command);
row = table.NewRow();
row["field"] = Lan.g("FormApptEdit", "Family Balance");
double balance = PIn.PDouble(familyBalance.Rows[0]["BalTotal"].ToString())
- PIn.PDouble(familyBalance.Rows[0]["InsEst"].ToString());
row["value"] = balance.ToString("F");
table.Rows.Add(row);
return(table);
}
private static DataTable GetCommLog(int patNum)
{
DataConnection dcon = new DataConnection();
DataTable table = new DataTable("Commlog");
DataRow row;
//columns that start with lowercase are altered for display rather than being raw data.
table.Columns.Add("CommDateTime", typeof(DateTime));
table.Columns.Add("commDate");
table.Columns.Add("commTime");
table.Columns.Add("CommlogNum");
table.Columns.Add("commType");
table.Columns.Add("EmailMessageNum");
table.Columns.Add("FormPatNum");
table.Columns.Add("mode");
table.Columns.Add("Note");
table.Columns.Add("patName");
//table.Columns.Add("sentOrReceived");
//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>();
//Commlog------------------------------------------------------------------------------------------
string command = "SELECT CommDateTime,CommType,Mode_,SentOrReceived,Note,CommlogNum,IsStatementSent,p1.FName,commlog.PatNum "
+ "FROM commlog,patient p1,patient p2 "
+ "WHERE commlog.PatNum=p1.PatNum "
+ "AND p1.Guarantor=p2.Guarantor "
+ "AND p2.PatNum =" + POut.PInt(patNum) + " ORDER BY CommDateTime";
DataTable rawComm = dcon.GetTable(command);
DateTime dateT;
for (int i = 0; i < rawComm.Rows.Count; i++)
{
if (rawComm.Rows[i]["IsStatementSent"].ToString() == "1")
{
continue;
}
row = table.NewRow();
dateT = PIn.PDateT(rawComm.Rows[i]["CommDateTime"].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"] = rawComm.Rows[i]["CommlogNum"].ToString();
row["commType"] = DefB.GetName(DefCat.CommLogTypes, PIn.PInt(rawComm.Rows[i]["CommType"].ToString()));
row["EmailMessageNum"] = "0";
row["FormPatNum"] = "0";
if (rawComm.Rows[i]["Mode_"].ToString() != "0") //anything except none
{
row["mode"] = Lan.g("enumCommItemMode", ((CommItemMode)PIn.PInt(rawComm.Rows[i]["Mode_"].ToString())).ToString());
}
row["Note"] = rawComm.Rows[i]["Note"].ToString();
if (rawComm.Rows[i]["PatNum"].ToString() != patNum.ToString())
{
row["patName"] = rawComm.Rows[i]["FName"].ToString();
}
//row["sentOrReceived"]=Lan.g("enumCommSentOrReceived",
// ((CommSentOrReceived)PIn.PInt(rawComm.Rows[i]["SentOrReceived"].ToString())).ToString());
rows.Add(row);
}
//emailmessage---------------------------------------------------------------------------------------
command = "SELECT MsgDateTime,SentOrReceived,Subject,EmailMessageNum "
+ "FROM emailmessage WHERE PatNum ='" + POut.PInt(patNum) + "' ORDER BY MsgDateTime";
DataTable rawEmail = dcon.GetTable(command);
string txt;
for (int i = 0; i < rawEmail.Rows.Count; i++)
{
row = table.NewRow();
dateT = PIn.PDateT(rawEmail.Rows[i]["MsgDateTime"].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";
//type
row["EmailMessageNum"] = rawEmail.Rows[i]["EmailMessageNum"].ToString();
row["FormPatNum"] = "0";
row["mode"] = Lan.g("enumCommItemMode", CommItemMode.Email.ToString());
txt = "";
if (rawEmail.Rows[i]["SentOrReceived"].ToString() == "0")
{
txt = "(" + Lan.g("AccountModule", "Unsent") + ") ";
}
row["Note"] = txt + rawEmail.Rows[i]["Subject"].ToString();
//if(rawEmail.Rows[i]["SentOrReceived"].ToString()=="0") {
// row["sentOrReceived"]=Lan.g("AccountModule","Unsent");
//}
//else {
// row["sentOrReceived"]=Lan.g("enumCommSentOrReceived",
// ((CommSentOrReceived)PIn.PInt(rawEmail.Rows[i]["SentOrReceived"].ToString())).ToString());
//}
rows.Add(row);
}
//formpat---------------------------------------------------------------------------------------
command = "SELECT FormDateTime,FormPatNum "
+ "FROM formpat WHERE PatNum ='" + POut.PInt(patNum) + "' ORDER BY FormDateTime";
DataTable rawForm = dcon.GetTable(command);
for (int i = 0; i < rawForm.Rows.Count; i++)
{
row = table.NewRow();
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"] = Lan.g("AccountModule", "Questionnaire");
row["EmailMessageNum"] = "0";
row["FormPatNum"] = rawForm.Rows[i]["FormPatNum"].ToString();
row["mode"] = "";
row["Note"] = "";
//row["sentOrReceived"]="";
rows.Add(row);
}
//Sorting
//rows.Sort(CompareCommRows);
for (int i = 0; i < rows.Count; i++)
{
table.Rows.Add(rows[i]);
}
DataView view = table.DefaultView;
view.Sort = "CommDateTime";
table = view.ToTable();
return(table);
}
public static DataSet GetAccount(int patNum)
{
string command = "SELECT CommDateTime,CommType,Mode,SentOrReceived,Note,CommlogNum "
+ "FROM commlog WHERE PatNum ='" + POut.PInt(patNum) + "' ORDER BY CommDateTime";
DataConnection dcon = new DataConnection();
DataTable tRaw = dcon.GetTable(command);
DataSet retVal = new DataSet();
DataTable tComm = new DataTable("Commlog");
tComm.Columns.Add("CommDateTime", typeof(DateTime));
tComm.Columns.Add("CommType"); //1
tComm.Columns.Add("Mode"); //2
tComm.Columns.Add("SentOrReceived"); //3
tComm.Columns.Add("Note"); //4
tComm.Columns.Add("CommlogNum"); //5
tComm.Columns.Add("EmailMessageNum"); //6
tComm.Columns.Add("FormPatNum"); //7
DataRow row;
//Commlog------------------------------------------------------------------------------------------
for (int i = 0; i < tRaw.Rows.Count; i++)
{
if ((CommItemType)PIn.PInt(tRaw.Rows[i][1].ToString()) == CommItemType.StatementSent)
{
continue;
}
row = tComm.NewRow();
row[0] = PIn.PDateT(tRaw.Rows[i][0].ToString());
row[1] = Lan.g("enumCommItemType", ((CommItemType)PIn.PInt(tRaw.Rows[i][1].ToString())).ToString());
row[2] = Lan.g("enumCommItemMode", ((CommItemMode)PIn.PInt(tRaw.Rows[i][2].ToString())).ToString());
row[3] = Lan.g("enumCommSentOrReceived", ((CommSentOrReceived)PIn.PInt(tRaw.Rows[i][3].ToString())).ToString());
row[4] = tRaw.Rows[i][3].ToString();
row[5] = tRaw.Rows[i][4].ToString();
row[6] = "0";
row[7] = "0";
tComm.Rows.Add(row);
}
//emailmessage---------------------------------------------------------------------------------------
command = "SELECT MsgDateTime,SentOrReceived,Subject,EmailMessageNum "
+ "FROM emailmessage WHERE PatNum ='" + POut.PInt(patNum) + "' ORDER BY MsgDateTime";
tRaw = dcon.GetTable(command);
for (int i = 0; i < tRaw.Rows.Count; i++)
{
row = tComm.NewRow();
row[0] = PIn.PDateT(tRaw.Rows[i][0].ToString());
//row[1]="";//type
row[2] = Lan.g("enumCommItemMode", CommItemMode.Email.ToString());
if (tRaw.Rows[i][1].ToString() == "0")
{
row[3] = Lan.g("ModuleAccount", "Unsent");
}
else
{
row[3] = Lan.g("enumCommSentOrReceived", ((CommSentOrReceived)PIn.PInt(tRaw.Rows[i][1].ToString())).ToString());
}
row[4] = tRaw.Rows[i][2].ToString(); //note
row[5] = "0";
row[6] = tRaw.Rows[i][3].ToString();
row[7] = "0";
tComm.Rows.Add(row);
}
//formpat---------------------------------------------------------------------------------------
command = "SELECT FormDateTime,FormPatNum "
+ "FROM formpat WHERE PatNum ='" + POut.PInt(patNum) + "' ORDER BY FormDateTime";
tRaw = dcon.GetTable(command);
for (int i = 0; i < tRaw.Rows.Count; i++)
{
row = tComm.NewRow();
row[0] = PIn.PDateT(tRaw.Rows[i][0].ToString());
row[1] = Lan.g("ModuleAccount", "Form"); //type
row[2] = ""; //mode
row[3] = Lan.g("enumCommSentOrReceived", "Received");
row[4] = ""; //note
row[5] = "0";
row[6] = "0";
row[7] = tRaw.Rows[i][1].ToString();
tComm.Rows.Add(row);
}
DataView view = tComm.DefaultView;
view.Sort = "CommDateTime";
tComm = view.ToTable();
retVal.Tables.Add(tComm);
return(retVal);
}
private static DataTable GetCommLog(int patNum)
{
DataConnection dcon = new DataConnection();
DataTable table = new DataTable("Commlog");
DataRow row;
//columns that start with lowercase are altered for display rather than being raw data.
table.Columns.Add("CommDateTime", typeof(DateTime));
table.Columns.Add("commDate");
table.Columns.Add("CommlogNum");
table.Columns.Add("commType");
table.Columns.Add("EmailMessageNum");
table.Columns.Add("FormPatNum");
table.Columns.Add("mode");
table.Columns.Add("Note");
table.Columns.Add("sentOrReceived");
//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>();
//Commlog------------------------------------------------------------------------------------------
string command = "SELECT CommDateTime,CommType,Mode_,SentOrReceived,Note,CommlogNum "
+ "FROM commlog WHERE PatNum ='" + POut.PInt(patNum) + "' ORDER BY CommDateTime";
DataTable rawComm = dcon.GetTable(command);
DateTime dateT;
for (int i = 0; i < rawComm.Rows.Count; i++)
{
if ((CommItemType)PIn.PInt(rawComm.Rows[i]["CommType"].ToString()) == CommItemType.StatementSent)
{
continue;
}
row = table.NewRow();
dateT = PIn.PDateT(rawComm.Rows[i]["CommDateTime"].ToString());
row["CommDateTime"] = dateT;
row["commDate"] = dateT.ToShortDateString();
row["CommlogNum"] = rawComm.Rows[i]["CommlogNum"].ToString();
row["commType"] = Lan.g("enumCommItemType", ((CommItemType)PIn.PInt(rawComm.Rows[i]["CommType"].ToString())).ToString());
row["EmailMessageNum"] = "0";
row["FormPatNum"] = "0";
row["mode"] = Lan.g("enumCommItemMode", ((CommItemMode)PIn.PInt(rawComm.Rows[i]["Mode_"].ToString())).ToString());
row["Note"] = rawComm.Rows[i]["Note"].ToString();
row["sentOrReceived"] = Lan.g("enumCommSentOrReceived",
((CommSentOrReceived)PIn.PInt(rawComm.Rows[i]["SentOrReceived"].ToString())).ToString());
rows.Add(row);
}
//emailmessage---------------------------------------------------------------------------------------
command = "SELECT MsgDateTime,SentOrReceived,Subject,EmailMessageNum "
+ "FROM emailmessage WHERE PatNum ='" + POut.PInt(patNum) + "' ORDER BY MsgDateTime";
DataTable rawEmail = dcon.GetTable(command);
for (int i = 0; i < rawEmail.Rows.Count; i++)
{
row = table.NewRow();
dateT = PIn.PDateT(rawEmail.Rows[i]["MsgDateTime"].ToString());
row["CommDateTime"] = dateT;
row["commDate"] = dateT.ToShortDateString();
row["CommlogNum"] = "0";
//type
row["EmailMessageNum"] = rawEmail.Rows[i]["EmailMessageNum"].ToString();
row["FormPatNum"] = "0";
row["mode"] = Lan.g("enumCommItemMode", CommItemMode.Email.ToString());
row["Note"] = rawEmail.Rows[i]["Subject"].ToString();
if (rawEmail.Rows[i]["SentOrReceived"].ToString() == "0")
{
row["sentOrReceived"] = Lan.g("AccountModule", "Unsent");
}
else
{
row["sentOrReceived"] = Lan.g("enumCommSentOrReceived",
((CommSentOrReceived)PIn.PInt(rawEmail.Rows[i]["SentOrReceived"].ToString())).ToString());
}
rows.Add(row);
}
//formpat---------------------------------------------------------------------------------------
command = "SELECT FormDateTime,FormPatNum "
+ "FROM formpat WHERE PatNum ='" + POut.PInt(patNum) + "' ORDER BY FormDateTime";
DataTable rawForm = dcon.GetTable(command);
for (int i = 0; i < rawForm.Rows.Count; i++)
{
row = table.NewRow();
dateT = PIn.PDateT(rawForm.Rows[i]["FormDateTime"].ToString());
row["CommDateTime"] = dateT;
row["commDate"] = dateT.ToShortDateString();
row["CommlogNum"] = "0";
row["commType"] = Lan.g("AccountModule", "Questionnaire");
row["EmailMessageNum"] = "0";
row["FormPatNum"] = rawForm.Rows[i]["FormPatNum"].ToString();
row["mode"] = "";
row["Note"] = "";
row["sentOrReceived"] = "";
rows.Add(row);
}
//Sorting
//rows.Sort(CompareCommRows);
for (int i = 0; i < rows.Count; i++)
{
table.Rows.Add(rows[i]);
}
DataView view = table.DefaultView;
view.Sort = "CommDateTime";
table = view.ToTable();
return(table);
}
private static DataTable GetProgNotes(int patNum, bool isAuditMode)
{
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("ADACode");
table.Columns.Add("aptDateTime", typeof(DateTime));
table.Columns.Add("colorBackG");
table.Columns.Add("colorText");
table.Columns.Add("CommlogNum");
table.Columns.Add("description");
table.Columns.Add("dx");
table.Columns.Add("Dx");
table.Columns.Add("note");
table.Columns.Add("Priority");
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("prov");
table.Columns.Add("RxNum");
table.Columns.Add("signature");
table.Columns.Add("Surf");
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>();
//Procedures-----------------------------------------------------------------------------------------------------
string command = "SELECT ProcDate,ProcStatus,ToothNum,Surf,Dx,procedurelog.ADACode,ProcNum,procedurecode.Descript,"
+ "provider.Abbr,ProcFee,ProcNumLab,appointment.AptDateTime,Priority,ToothRange "
+ "FROM procedurelog "
+ "LEFT JOIN procedurecode ON procedurecode.ADACode=procedurelog.ADACode "
+ "LEFT JOIN provider ON provider.ProvNum=procedurelog.ProvNum "
+ "LEFT JOIN appointment ON appointment.AptNum=procedurelog.AptNum "
+ "AND (appointment.AptStatus=" + POut.PInt((int)ApptStatus.Scheduled)
+ " OR appointment.AptStatus=" + POut.PInt((int)ApptStatus.ASAP)
+ " OR appointment.AptStatus=" + POut.PInt((int)ApptStatus.Broken)
+ " OR appointment.AptStatus=" + POut.PInt((int)ApptStatus.Complete)
+ ") WHERE procedurelog.PatNum=" + POut.PInt(patNum);
if (!isAuditMode)
{
command += " AND ProcStatus !=6"; //don't include deleted
}
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.PInt(patNum)
+ " ORDER BY EntryDateTime"; // but this helps when looping for notes
DataTable rawNotes = dcon.GetTable(command);
DateTime dateT;
List <DataRow> labRows = new List <DataRow>(); //Canadian lab procs, which must be added in a loop at the very end.
for (int i = 0; i < rawProcs.Rows.Count; i++)
{
row = table.NewRow();
row["ADACode"] = rawProcs.Rows[i]["ADACode"].ToString();
row["aptDateTime"] = PIn.PDateT(rawProcs.Rows[i]["AptDateTime"].ToString());
row["colorBackG"] = Color.White.ToArgb();
if (((DateTime)row["aptDateTime"]).Date == DateTime.Today)
{
row["colorBackG"] = DefB.Long[(int)DefCat.MiscColors][6].ItemColor.ToArgb().ToString();
}
switch ((ProcStat)PIn.PInt(rawProcs.Rows[i]["ProcStatus"].ToString()))
{
case ProcStat.TP:
row["colorText"] = DefB.Long[(int)DefCat.ProgNoteColors][0].ItemColor.ToArgb().ToString();
break;
case ProcStat.C:
row["colorText"] = DefB.Long[(int)DefCat.ProgNoteColors][1].ItemColor.ToArgb().ToString();
break;
case ProcStat.EC:
row["colorText"] = DefB.Long[(int)DefCat.ProgNoteColors][2].ItemColor.ToArgb().ToString();
break;
case ProcStat.EO:
row["colorText"] = DefB.Long[(int)DefCat.ProgNoteColors][3].ItemColor.ToArgb().ToString();
break;
case ProcStat.R:
row["colorText"] = DefB.Long[(int)DefCat.ProgNoteColors][4].ItemColor.ToArgb().ToString();
break;
case ProcStat.D:
row["colorText"] = Color.Black.ToArgb().ToString();
break;
}
row["CommlogNum"] = 0;
row["description"] = rawProcs.Rows[i]["Descript"].ToString();
row["dx"] = DefB.GetValue(DefCat.Diagnosis, PIn.PInt(rawProcs.Rows[i]["Dx"].ToString()));
row["Dx"] = rawProcs.Rows[i]["Dx"].ToString();
//note-----------------------------------------------------------------------------------------------------------
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.PDateT(rawNotes.Rows[n]["EntryDateTime"].ToString()).ToString();
row["note"] += " " + UserodB.GetName(PIn.PInt(rawNotes.Rows[n]["UserNum"].ToString()));
if (rawNotes.Rows[n]["SigPresent"].ToString() == "1")
{
row["note"] += " " + Lan.g("ChartModule", "(signed)");
}
row["note"] += "\r\n" + rawNotes.Rows[n]["Note"].ToString();
}
}
else //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["user"] = UserodB.GetName(PIn.PInt(rawNotes.Rows[n]["UserNum"].ToString()));
row["note"] = rawNotes.Rows[n]["Note"].ToString();
if (rawNotes.Rows[n]["SigPresent"].ToString() == "1")
{
row["signature"] = Lan.g("ChartModule", "Signed");
}
else
{
row["signature"] = "";
}
break; //out of note loop.
}
}
row["Priority"] = rawProcs.Rows[i]["Priority"].ToString();
dateT = PIn.PDateT(rawProcs.Rows[i]["ProcDate"].ToString());
if (dateT.Year < 1880)
{
row["procDate"] = "";
}
else
{
row["procDate"] = dateT.ToShortDateString();
}
row["ProcDate"] = dateT;
row["procFee"] = PIn.PDouble(rawProcs.Rows[i]["ProcFee"].ToString()).ToString("F");
row["ProcNum"] = rawProcs.Rows[i]["ProcNum"].ToString();
row["ProcNumLab"] = rawProcs.Rows[i]["ProcNumLab"].ToString();
row["procStatus"] = Lan.g("enumProcStat", ((ProcStat)PIn.PInt(rawProcs.Rows[i]["ProcStatus"].ToString())).ToString());
row["ProcStatus"] = rawProcs.Rows[i]["ProcStatus"].ToString();
row["prov"] = rawProcs.Rows[i]["Abbr"].ToString();
row["RxNum"] = 0;
row["Surf"] = rawProcs.Rows[i]["Surf"].ToString();
row["toothNum"] = Tooth.ToInternat(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
}
}
//Commlog-----------------------------------------------------------------------------------------------------------
command = "SELECT CommlogNum,CommDateTime,CommType,Note FROM commlog WHERE PatNum=" + POut.PInt(patNum)
+ " ORDER BY CommDateTime";
DataTable rawComm = dcon.GetTable(command);
for (int i = 0; i < rawComm.Rows.Count; i++)
{
row = table.NewRow();
row["colorBackG"] = Color.White.ToArgb();
row["colorText"] = DefB.Long[(int)DefCat.ProgNoteColors][6].ItemColor.ToArgb().ToString();
row["CommlogNum"] = rawComm.Rows[i]["CommlogNum"].ToString();
row["description"] = Lan.g("ChartModule", "Comm - ")
+ Lan.g("enumCommItemType", ((CommItemType)PIn.PInt(rawComm.Rows[i]["CommType"].ToString())).ToString());
row["note"] = rawComm.Rows[i]["Note"].ToString();
dateT = PIn.PDateT(rawComm.Rows[i]["CommDateTime"].ToString());
if (dateT.Year < 1880)
{
row["procDate"] = "";
}
else
{
row["procDate"] = dateT.ToShortDateString();
}
row["ProcDate"] = dateT;
row["ProcNum"] = 0;
row["RxNum"] = 0;
rows.Add(row);
}
//Rx------------------------------------------------------------------------------------------------------------------
command = "SELECT RxNum,RxDate,Drug,Disp,ProvNum,Notes FROM rxpat WHERE PatNum=" + POut.PInt(patNum)
+ " ORDER BY RxDate";
DataTable rawRx = dcon.GetTable(command);
for (int i = 0; i < rawRx.Rows.Count; i++)
{
row = table.NewRow();
row["colorBackG"] = Color.White.ToArgb();
row["colorText"] = DefB.Long[(int)DefCat.ProgNoteColors][5].ItemColor.ToArgb().ToString();
row["CommlogNum"] = 0;
row["description"] = Lan.g("ChartModule", "Rx - ") + rawRx.Rows[i]["Drug"].ToString() + " - #" + rawRx.Rows[i]["Disp"].ToString();
row["note"] = rawRx.Rows[i]["Notes"].ToString();
dateT = PIn.PDate(rawRx.Rows[i]["RxDate"].ToString());
if (dateT.Year < 1880)
{
row["procDate"] = "";
}
else
{
row["procDate"] = dateT.ToShortDateString();
}
row["ProcDate"] = dateT;
row["ProcNum"] = 0;
row["RxNum"] = rawRx.Rows[i]["RxNum"].ToString();
rows.Add(row);
}
//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;
}
}
}
for (int i = 0; i < rows.Count; i++)
{
table.Rows.Add(rows[i]);
}
return(table);
}
