private void fill_cmbProc() { DataTable table = new DataTable(); table.Columns.Add("Procedure Code"); DataRow row; string command = @" SELECT ProcCode FROM procedurecode"; DataTable raw = ReportsComplex.GetTable(command); for (int i = 0; i < raw.Rows.Count; i++) { row = table.NewRow(); row["Procedure Code"] = raw.Rows[i]["ProcCode"].ToString(); } for (int i = 0; i < raw.Rows.Count; i++) { cmbProc.Items.Add(raw.Rows[i]["ProcCode"].ToString()); } }
// public static String GetQueryPats() public static DataTable GetPendingTreatmentPats(DateTime dateStart, DateTime dateEnd) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateStart, dateEnd)); } DataTable table = new DataTable(); table.Columns.Add("PatNum"); table.Columns.Add("Name"); table.Columns.Add("Home Phone"); table.Columns.Add("Cell Phone"); table.Columns.Add("Wireless Phone"); table.Columns.Add("Email"); DataRow row; string command = @" SELECT DISTINCT p.PatNum, p.LName, p.FName, p.MiddleI, p.HmPhone, p.WkPhone, p.WirelessPhone, p.Email FROM procedurelog pl JOIN procedurecode pc ON pl.CodeNum = pc.CodeNum JOIN appointment a ON a.AptNum = pl.PlannedAptNum JOIN patient p ON a.PatNum = p.PatNum WHERE pl.AptNum = 0 AND a.AptStatus = 6 AND pc.ProcCode != 01202 "; DataTable raw = ReportsComplex.GetTable(command); Patient pat; 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.MiddleI = raw.Rows[i]["MiddleI"].ToString(); row["Name"] = raw.Rows[i]["FName"].ToString() + " " + raw.Rows[i]["MiddleI"].ToString() + " " + raw.Rows[i]["LName"].ToString(); pat.HmPhone = raw.Rows[i]["HmPhone"].ToString(); pat.WkPhone = raw.Rows[i]["WkPhone"].ToString(); pat.WirelessPhone = raw.Rows[i]["WirelessPhone"].ToString(); pat.Email = raw.Rows[i]["Email"].ToString(); row["PatNum"] = raw.Rows[i]["PatNum"]; row["Home Phone"] = raw.Rows[i]["HmPhone"].ToString(); row["Work Phone"] = raw.Rows[i]["WkPhone"].ToString(); row["Cell Phone"] = raw.Rows[i]["WirelessPhone"].ToString(); row["Email"] = raw.Rows[i]["Email"].ToString(); table.Rows.Add(row); } return(table); }
///<summary>If not using clinics then supply an empty list of clinicNums. dateStart and dateEnd can be MinVal/MaxVal to indicate "forever".</summary> public static DataTable GetActiveRecall(DateTime dateStart, DateTime dateEnd) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateStart, dateEnd)); } DataTable table = new DataTable(); table.Columns.Add("Name"); table.Columns.Add("Gender"); table.Columns.Add("Age"); table.Columns.Add("Postal Code"); table.Columns.Add("Date of Service"); table.Columns.Add("Frequency"); table.Columns.Add("Primary Provider"); DataRow row; string command = @" SELECT p.LName, p.FName, p.MiddleI, p.Gender, p.Zip, p.PriProv, p.Preferred, r.ProcDate, p.Birthdate, q.RecallInterval FROM patient p JOIN procedurelog r ON r.PatNum = p.PatNum JOIN recall q ON p.PatNum = q.PatNum WHERE r.ProcDate = (SELECT MAX(r2.ProcDate) FROM procedurelog r2 JOIN procedurecode c ON r2.CodeNum = c.CodeNum WHERE r.PatNum = r2.PatNum AND r2.PlannedAptNum > 0 AND c.ProcCode = 01202 AND r2.ProcDate BETWEEN " + POut.DateT(dateStart) + @" AND " + POut.DateT(dateEnd) + @") AND q.RecallTypeNum = 1 AND q.IsDisabled = 0 GROUP BY p.PatNum"; DataTable raw = ReportsComplex.GetTable(command); Patient pat; 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.MiddleI = raw.Rows[i]["MiddleI"].ToString(); pat.Preferred = raw.Rows[i]["Preferred"].ToString(); row["Name"] = pat.GetNameLF(); row["Primary Provider"] = Providers.GetAbbr(PIn.Long(raw.Rows[i]["PriProv"].ToString())); Console.Write(raw.Rows[i]["Gender"].ToString()); row["Gender"] = genderFormat(raw.Rows[i]["Gender"].ToString()); row["Postal Code"] = raw.Rows[i]["Zip"].ToString(); row["Date of Service"] = raw.Rows[i]["ProcDate"].ToString().Substring(0, 10); row["Age"] = birthdate_to_age(raw.Rows[i]["Birthdate"].ToString()); Interval frequency = new Interval(Int32.Parse(raw.Rows[i]["RecallInterval"].ToString())); row["Frequency"] = frequency.ToString(); table.Rows.Add(row); } return(table); }
///<summary>If not using clinics then supply an empty list of clinicNums. dateStart and dateEnd can be MinVal/MaxVal to indicate "forever".</summary> public static DataTable GetPerioRecall(DateTime dateStart, DateTime dateEnd) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateStart, dateEnd)); } DataTable table = new DataTable(); table.Columns.Add("Name"); table.Columns.Add("Gender"); table.Columns.Add("Age"); table.Columns.Add("Postal Code"); table.Columns.Add("Date of Next Appointment"); table.Columns.Add("Frequency"); table.Columns.Add("HygienistID"); table.Columns.Add("Primary Provider"); DataRow row; //Could not find code 43400 so just assumed all procedure codes between 40000 and 50000 string command = @" SELECT p.LName, p.FName, p.MiddleI, p.Gender, p.Zip, p.PriProv, p.Preferred, p.Birthdate, r.RecallInterval, a.AptDateTime, a.ProvHyg FROM patient p JOIN procedurelog x ON x.PatNum = p.PatNum JOIN procedurecode c ON c.CodeNum = x.CodeNum JOIN appointment a ON a.AptNum = x.AptNum JOIN recall r ON r.PatNum = p.PatNum WHERE r.IsDisabled = 0 AND r.RecallTypeNum = 3 AND c.ProcCode = 43400 AND a.AptDateTime = (SELECT MAX(a2.AptDateTime) FROM appointment a2 WHERE a2.AptNum = a.AptNum AND a2.AptDateTime BETWEEN " + POut.DateT(dateStart) + @" AND " + POut.DateT(dateEnd) + @") GROUP BY p.PatNum"; DataTable raw = ReportsComplex.GetTable(command); Patient pat; 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.MiddleI = raw.Rows[i]["MiddleI"].ToString(); pat.Preferred = raw.Rows[i]["Preferred"].ToString(); row["Name"] = pat.GetNameLF(); row["Primary Provider"] = Providers.GetAbbr(PIn.Long(raw.Rows[i]["PriProv"].ToString())); row["Gender"] = genderFormat(raw.Rows[i]["Gender"].ToString()); row["Postal Code"] = raw.Rows[i]["Zip"].ToString(); row["Date of Next Appointment"] = raw.Rows[i]["AptDateTime"].ToString().Substring(0, 10); Interval frequency = new Interval(Int32.Parse(raw.Rows[i]["RecallInterval"].ToString())); row["Frequency"] = frequency.ToString(); row["HygienistID"] = raw.Rows[i]["ProvHyg"].ToString(); row["Age"] = birthdate_to_age(raw.Rows[i]["Birthdate"].ToString()); table.Rows.Add(row); } return(table); }
public static DataTable GetRecallBacklog(DateTime dateStart, DateTime dateEnd) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetTable(MethodBase.GetCurrentMethod())); } DataTable table = new DataTable(); table.Columns.Add("Name"); table.Columns.Add("Gender"); table.Columns.Add("Preferred Contact Method"); table.Columns.Add("Phone"); table.Columns.Add("Email"); table.Columns.Add("Hygienist ID (Last appointment)"); table.Columns.Add("Date of Last Recall"); table.Columns.Add("Due Date"); DataRow row; string command = @" SELECT p.LName, p.FName, p.MiddleI, p.Preferred, p.Gender, p.PreferContactMethod, p.HmPhone, p.Email, q.DatePrevious, c.ProcCode, r.AptNum, a.ProvHyg, a.AptDateTime, q.DateDue FROM patient p INNER JOIN procedurelog r ON r.PatNum = p.PatNum INNER JOIN procedureCode c ON r.CodeNum = c.CodeNum INNER JOIN recall q ON p.PatNum = q.PatNum INNER JOIN plannedappt pa on pa.PatNum = p.PatNum INNER JOIN appointment a ON r.PatNum = a.PatNum WHERE c.ProcCode=01202 AND a.AptStatus=6 AND r.PlannedAptNum = pa.AptNum AND r.AptNum = 0 or a.AptStatus=3 AND c.ProcCode=1202 OR a.AptStatus=5 AND c.ProcCode=1202 AND r.ProcDate BETWEEN " + POut.DateT(dateStart) + @" AND " + POut.DateT(dateEnd) + @" GROUP BY p.PatNUm"; DataTable raw = ReportsComplex.GetTable(command); Patient pat; 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.MiddleI = raw.Rows[i]["MiddleI"].ToString(); pat.Preferred = raw.Rows[i]["Preferred"].ToString(); row["Name"] = pat.GetNameLF(); row["Gender"] = genderFormat(raw.Rows[i]["Gender"].ToString()); row["Preferred Contact Method"] = getContactMethod(raw.Rows[i]["PreferContactMethod"].ToString()); row["Phone"] = raw.Rows[i]["HmPhone"].ToString(); row["Email"] = raw.Rows[i]["Email"].ToString(); row["Hygienist ID (Last appointment)"] = raw.Rows[i]["ProvHyg"].ToString(); row["Date of Last Recall"] = raw.Rows[i]["DatePrevious"].ToString().Substring(0, 10); row["Due Date"] = raw.Rows[i]["DateDue"].ToString().Substring(0, 10); table.Rows.Add(row); } return(table); }
///<summary>If not using clinics then supply an empty list of clinicNums. dateStart and dateEnd can be MinVal/MaxVal to indicate "forever".</summary> public static DataTable GetNoShowAppt(DateTime dateStart, DateTime dateEnd) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateStart, dateEnd)); } DataTable table = new DataTable(); table.Columns.Add("Name"); table.Columns.Add("Gender"); table.Columns.Add("Age"); table.Columns.Add("Postal Code"); table.Columns.Add("Date of Service"); table.Columns.Add("Primary Provider"); table.Columns.Add("Procedure Description"); DataRow row; string command = @" SELECT p.LName, p.FName, p.MiddleI, p.Gender, p.Zip, p.PriProv, p.Preferred, p.Birthdate, r.ProcDate, a.ProcDescript FROM patient p JOIN procedurelog r ON r.PatNum = p.PatNum JOIN procedurecode c ON r.CodeNum = c.CodeNum JOIN appointment a ON a.AptNum = r.AptNum WHERE c.ProcCode = 99999 AND r.ProcDate BETWEEN " + POut.DateT(dateStart) + @" AND " + POut.DateT(dateEnd); DataTable raw = ReportsComplex.GetTable(command); Patient pat; 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.MiddleI = raw.Rows[i]["MiddleI"].ToString(); pat.Preferred = raw.Rows[i]["Preferred"].ToString(); row["Name"] = pat.GetNameLF(); row["Primary Provider"] = Providers.GetAbbr(PIn.Long(raw.Rows[i]["PriProv"].ToString())); row["Gender"] = genderFormat(raw.Rows[i]["Gender"].ToString()); row["Postal Code"] = raw.Rows[i]["Zip"].ToString(); row["Date of Service"] = raw.Rows[i]["ProcDate"].ToString().Substring(0, 10); row["Procedure Description"] = raw.Rows[i]["ProcDescript"].ToString(); if (birthdate_to_age(raw.Rows[i]["Birthdate"].ToString()) < 150) { row["Age"] = birthdate_to_age(raw.Rows[i]["Birthdate"].ToString()); } else { row["Age"] = "N/A"; } table.Rows.Add(row); } return(table); }
/* * Completed cases * List of patients compiled from the Treatment Plan module whose treatment has been * completed within the period designated by the operator */ ///<summary>If not using clinics then supply an empty list of clinicNums. dateStart and dateEnd can be MinVal/MaxVal to indicate "forever".</summary> public static DataTable GetCompletedCases(DateTime dateStart, DateTime dateEnd) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateStart, dateEnd)); } DataTable table = new DataTable(); table.Columns.Add("Name"); table.Columns.Add("Date of Service"); table.Columns.Add("Treatment Code"); table.Columns.Add("Treatment Completed"); table.Columns.Add("Billed"); DataRow row; string command = @" SELECT p.LName, p.FName, p.MiddleI, p.Gender, p.Zip, p.PriProv, pl.ProcDate, pl.ProcNum, pc.ProcCode, pc.Descript, pl.ProcFee FROM procedurelog pl JOIN appointment a ON pl.PlannedAptNum = a.AptNum JOIN procedurecode pc ON pc.CodeNum = pl.CodeNum JOIN patient p ON p.PatNum = pl.PatNum WHERE a.AptStatus = 6 AND pl.ProcStatus = 2 AND (pl.ProcDate BETWEEN " + POut.DateT(dateStart) + @" AND " + POut.DateT(dateEnd) + @")"; DataTable raw = ReportsComplex.GetTable(command); Patient pat; 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.MiddleI = raw.Rows[i]["MiddleI"].ToString(); row["Name"] = raw.Rows[i]["FName"].ToString() + " " + raw.Rows[i]["MiddleI"].ToString() + " " + raw.Rows[i]["LName"].ToString(); row["Date of Service"] = raw.Rows[i]["ProcDate"].ToString().Substring(0, 10); row["Treatment Code"] = raw.Rows[i]["ProcCode"].ToString(); row["Treatment Completed"] = raw.Rows[i]["Descript"].ToString(); row["Billed"] = raw.Rows[i]["ProcFee"].ToString(); table.Rows.Add(row); } return(table); }
///<summary>If not using clinics then supply an empty list of clinicNums. dateStart and dateEnd can be MinVal/MaxVal to indicate "forever".</summary> public static DataTable GetNewPatients(DateTime dateStart, DateTime dateEnd) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateStart, dateEnd)); } DataTable table = new DataTable(); table.Columns.Add("Name"); table.Columns.Add("Gender"); table.Columns.Add("Age"); table.Columns.Add("Date of Service"); DataRow row; string command = @" SELECT p.LName, p.FName, p.MiddleI, p.Gender, p.Preferred, r.ProcDate, p.Birthdate FROM patient p INNER JOIN procedurelog r ON r.PatNum = p.PatNum INNER JOIN procedurecode c ON c.CodeNum = r.CodeNum WHERE (c.ProcCode = 01101 OR c.ProcCode = 01102 OR c.ProcCode = 01103) AND (r.ProcDate BETWEEN " + POut.DateT(dateStart) + @" AND " + POut.DateT(dateEnd) + @") GROUP BY p.PatNum"; DataTable raw = ReportsComplex.GetTable(command); Patient pat; 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.MiddleI = raw.Rows[i]["MiddleI"].ToString(); pat.Preferred = raw.Rows[i]["Preferred"].ToString(); row["Name"] = pat.GetNameLF(); row["Gender"] = genderFormat(raw.Rows[i]["Gender"].ToString()); if (birthdate_to_age(raw.Rows[i]["Birthdate"].ToString()) < 150) { row["Age"] = birthdate_to_age(raw.Rows[i]["Birthdate"].ToString()); } else { row["Age"] = "N/A"; } row["Date of Service"] = raw.Rows[i]["ProcDate"].ToString(); table.Rows.Add(row); } return(table); }
public static DataTable GetPatients(DateTime dateStart, DateTime dateEnd, string query) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateStart, dateEnd)); } DataTable table = new DataTable(); table.Columns.Add("PatNum"); table.Columns.Add("Name"); table.Columns.Add("Home Phone"); table.Columns.Add("Work Phone"); table.Columns.Add("Wireless Phone"); table.Columns.Add("Email"); DataRow row; DataTable raw = ReportsComplex.GetTable(query); Patient pat; 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.MiddleI = raw.Rows[i]["MiddleI"].ToString(); row["Name"] = raw.Rows[i]["FName"].ToString() + " " + raw.Rows[i]["MiddleI"].ToString() + " " + raw.Rows[i]["LName"].ToString(); pat.HmPhone = raw.Rows[i]["HmPhone"].ToString(); pat.WkPhone = raw.Rows[i]["WkPhone"].ToString(); pat.WirelessPhone = raw.Rows[i]["WirelessPhone"].ToString(); pat.Email = raw.Rows[i]["Email"].ToString(); row["PatNum"] = raw.Rows[i]["PatNum"].ToString(); row["Home Phone"] = raw.Rows[i]["HmPhone"].ToString(); row["Work Phone"] = raw.Rows[i]["WkPhone"].ToString(); row["Wireless Phone"] = raw.Rows[i]["WirelessPhone"].ToString(); row["Email"] = raw.Rows[i]["Email"].ToString(); table.Rows.Add(row); } return(table); }
///<summary>If not using clinics then supply an empty list of clinicNums. dateStart and dateEnd can be MinVal/MaxVal to indicate "forever".</summary> public static DataTable GetRecTreatmentYYY(DateTime dateStart, DateTime dateEnd, long pnum, String pc) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateStart, dateEnd)); } DataTable table = new DataTable(); table.Columns.Add("Date of Service"); table.Columns.Add("Name"); table.Columns.Add("Procedure"); table.Columns.Add("Priority"); table.Columns.Add("Status of Pre-Authorization"); DataRow row; string command = @" SELECT r.ProcDate, p.LName, p.FName, p.MiddleI, rc.ProcCode, tpa.Priority, r.ProcStatus FROM patient p JOIN procedurelog r ON r.PatNum = p.PatNum JOIN treatplan t ON t.PatNum = p.PatNum JOIN treatplanattach tpa ON tpa.ProcNum = r.ProcNum AND tpa.TreatPlanNum = t.TreatPlanNum JOIN procedurecode rc ON rc.CodeNum = r.CodeNum WHERE r.ProcDate BETWEEN " + POut.DateT(dateStart) + @" AND " + POut.DateT(dateEnd) + @" AND rc.ProcCode = " + "'" + POut.String(pc) + "'" + @" AND p.PatNum = " + POut.Long(pnum) + @" ORDER BY r.ProcDate"; DataTable raw = ReportsComplex.GetTable(command); Patient pat; 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(); row["Date of Service"] = raw.Rows[i]["ProcDate"].ToString().Substring(0, 10); row["Name"] = pat.LName + ", " + pat.FName; row["Procedure"] = raw.Rows[i]["ProcCode"].ToString(); row["Priority"] = raw.Rows[i]["Priority"].ToString(); row["Status of Pre-Authorization"] = raw.Rows[i]["ProcStatus"]; table.Rows.Add(row); } return(table); }
///<summary>If not using clinics then supply an empty list of clinicNums. dateStart and dateEnd can be MinVal/MaxVal to indicate "forever".</summary> public static float GetConversionRate(DateTime dateStart, DateTime dateEnd, String pc) { // Find all procedures for that ProcCode string command = @" SELECT COUNT(*) FROM appointment a JOIN procedurelog pl ON pl.PlannedAptNum = a.NextAptNum JOIN procedurecode x ON x.CodeNum = pl.CodeNum WHERE x.ProcCode = " + "'" + POut.String(pc) + "'" + @" AND pl.DateTP BETWEEN " + POut.DateT(dateStart) + @" AND " + POut.DateT(dateEnd) + @""; // Find procedures that have been planned. string command2 = @" SELECT COUNT(*) FROM appointment a JOIN procedurelog pl ON pl.PlannedAptNum = a.AptNum JOIN procedurecode x ON x.CodeNum = pl.CodeNum WHERE x.ProcCode = " + "'" + POut.String(pc) + "'" + @" AND pl.DateTP BETWEEN " + POut.DateT(dateStart) + @" AND " + POut.DateT(dateEnd) + @" AND pl.AptNum = 0"; DataTable raw = ReportsComplex.GetTable(command); DataTable raw2 = ReportsComplex.GetTable(command2); float returnval = 0; // No Procedures At All float TOTALPROCEDURES = float.Parse(raw.Rows[0]["COUNT(*)"].ToString()); float PROCEDURESPLANNED = float.Parse(raw2.Rows[0]["COUNT(*)"].ToString()); float TOTALCOUNTPROCEDURES = TOTALPROCEDURES + PROCEDURESPLANNED; if (TOTALCOUNTPROCEDURES == 0) { return(9999); } else { System.Diagnostics.Debug.Write(TOTALCOUNTPROCEDURES); System.Diagnostics.Debug.Write(PROCEDURESPLANNED); // (Count of all procedures - Count of Planned Procedures) / Count of All Procedures // Simply put: (Count of procedures scheduled) / Count of All procedures returnval = ((TOTALCOUNTPROCEDURES - PROCEDURESPLANNED) / TOTALCOUNTPROCEDURES) * 100; return((float)Math.Round(returnval, 2)); } }
public static DataTable GetRecTreatmentNNN() { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetTable(MethodBase.GetCurrentMethod())); } DataTable table = new DataTable(); table.Columns.Add("Date of Service"); table.Columns.Add("Name"); table.Columns.Add("Procedure"); table.Columns.Add("Priority"); table.Columns.Add("Status of Pre-Authorization"); DataRow row; string command = @" SELECT r.ProcDate, p.LName, p.FName, p.MiddleI, rc.ProcCode, tpa.Priority, r.ProcStatus FROM patient p JOIN procedurelog r ON r.PatNum = p.PatNum JOIN treatplan t ON t.PatNum = p.PatNum JOIN treatplanattach tpa ON tpa.ProcNum = r.ProcNum AND tpa.TreatPlanNum = t.TreatPlanNum JOIN procedurecode rc ON rc.CodeNum = r.CodeNum ORDER BY r.ProcDate"; System.Diagnostics.Debug.WriteLine(command); DataTable raw = ReportsComplex.GetTable(command); Patient pat; 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(); row["Date of Service"] = raw.Rows[i]["ProcDate"].ToString().Substring(0, 10); row["Name"] = pat.LName + ", " + pat.FName; row["Procedure"] = raw.Rows[i]["ProcCode"].ToString(); row["Priority"] = raw.Rows[i]["Priority"].ToString(); row["Status of Pre-Authorization"] = raw.Rows[i]["ProcStatus"]; table.Rows.Add(row); } return(table); }
public static DataTable GetRecTreatmentYNN(DateTime dateStart, DateTime dateEnd) { DataTable table = new DataTable(); table.Columns.Add("Date of Service"); table.Columns.Add("Name"); table.Columns.Add("Procedure"); table.Columns.Add("Priority"); table.Columns.Add("Status of Pre-Authorization"); DataRow row; string command = @" SELECT r.ProcDate, p.LName, p.FName, p.MiddleI, rc.ProcCode, tpa.Priority, r.ProcStatus FROM patient p JOIN procedurelog r ON r.PatNum = p.PatNum JOIN treatplan t ON t.PatNum = p.PatNum JOIN treatplanattach tpa ON tpa.ProcNum = r.ProcNum AND tpa.TreatPlanNum = t.TreatPlanNum JOIN procedurecode rc ON rc.CodeNum = r.CodeNum WHERE r.ProcDate BETWEEN " + POut.DateT(dateStart) + @" AND " + POut.DateT(dateEnd) + @" ORDER BY r.ProcDate"; System.Diagnostics.Debug.WriteLine(command); DataTable raw = ReportsComplex.GetTable(command); Patient pat; 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(); row["Date of Service"] = raw.Rows[i]["ProcDate"].ToString().Substring(0, 10); row["Name"] = pat.LName + ", " + pat.FName; row["Procedure"] = raw.Rows[i]["ProcCode"].ToString(); row["Priority"] = raw.Rows[i]["Priority"].ToString(); row["Status of Pre-Authorization"] = raw.Rows[i]["ProcStatus"]; table.Rows.Add(row); } return(table); }
public static DataTable GetCompletedCasesPerPat(DateTime dateStart, DateTime dateEnd, String patNum) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateStart, dateEnd)); } DataTable table = new DataTable(); table.Columns.Add("Date of Service"); table.Columns.Add("Treatment Code"); table.Columns.Add("Treatment Completed"); table.Columns.Add("Billed"); DataRow row; string command = @" SELECT pc.ProcCode, pl.ProcDate, pc.Descript, pl.ProcFee FROM procedurelog pl JOIN appointment a ON pl.PlannedAptNum = a.AptNum JOIN procedurecode pc ON pc.CodeNum = pl.CodeNum JOIN patient p ON p.PatNum = pl.PatNum WHERE a.AptStatus = 6 AND pl.ProcStatus = 2 AND (pl.ProcDate BETWEEN " + POut.DateT(dateStart) + @" AND " + POut.DateT(dateEnd) + @") AND p.PatNum = '" + patNum + @"' "; DataTable raw = ReportsComplex.GetTable(command); for (int i = 0; i < raw.Rows.Count; i++) { row = table.NewRow(); row["Date of Service"] = raw.Rows[i]["ProcDate"].ToString().Substring(0, 10); row["Treatment Code"] = raw.Rows[i]["ProcCode"].ToString(); row["Treatment Completed"] = raw.Rows[i]["Descript"].ToString(); double a = (double)raw.Rows[i]["ProcFee"]; row["Billed"] = a; table.Rows.Add(row); } return(table); }
///<summary> dateStart and dateEnd can be MinVal/MaxVal to indicate "forever".</summary> public static DataTable GetDowntime(DateTime dateStart, DateTime dateEnd) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateStart, dateEnd)); } DataTable table = new DataTable(); table.Columns.Add("Provider"); table.Columns.Add("Provider Number"); table.Columns.Add("Total Down-time"); DataRow row; string command = @" SELECT appointment.ProvNum , sec_to_time((sum((CHAR_LENGTH(appointment.Pattern) - CHAR_LENGTH(REPLACE(appointment.Pattern, 'X', ''))) ))*5*60) AS DownTime FROM appointment WHERE EXISTS ( SELECT * FROM procedurelog LEFT JOIN procedurecode ON (procedurecode.CodeNum = procedurelog.CodeNum) WHERE procedurelog.AptNum = appointment.AptNum AND (procedurecode.ProcCode = 99999 OR procedurecode.ProcCode = 99998) ) AND appointment.AptStatus = 5 AND appointment.AptDateTime BETWEEN " + POut.DateT(dateStart) + @" AND " + POut.DateT(dateEnd) + @" GROUP BY appointment.ProvNum"; DataTable raw = ReportsComplex.GetTable(command); for (int i = 0; i < raw.Rows.Count; i++) { row = table.NewRow(); row["Provider"] = Providers.GetFormalName(PIn.Long(raw.Rows[i]["ProvNum"].ToString())); row["Provider Number"] = raw.Rows[i]["ProvNum"].ToString(); row["Total Down-time"] = raw.Rows[i]["DownTime"].ToString(); table.Rows.Add(row); } return(table); }
public static DataTable GetPendingTreatmentProcsPerPat(DateTime dateStart, DateTime dateEnd, String patNum) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateStart, dateEnd)); } DataTable table = new DataTable(); table.Columns.Add("Procedure Code"); table.Columns.Add("Treatment Planned"); DataRow row; string command = @" SELECT pc.Descript, pc.ProcCode FROM procedurelog pl JOIN procedurecode pc ON pl.CodeNum = pc.CodeNum JOIN appointment a ON a.AptNum = pl.PlannedAptNum JOIN patient p ON a.PatNum = p.PatNum WHERE pl.AptNum = 0 AND a.AptStatus = 6 AND pc.ProcCode != 01202 AND p.PatNum = '" + patNum + @"' "; DataTable raw = ReportsComplex.GetTable(command); for (int i = 0; i < raw.Rows.Count; i++) { row = table.NewRow(); row["Procedure Code"] = raw.Rows[i]["ProcCode"].ToString(); row["Treatment Planned"] = raw.Rows[i]["Descript"].ToString(); table.Rows.Add(row); } return(table); }
///<summary>If not using clinics then supply an empty list of clinicNums. dateStart and dateEnd can be MinVal/MaxVal to indicate "forever".</summary> public static DataTable GetByReferralSource(DateTime dateStart, DateTime dateEnd) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateStart, dateEnd)); } DataTable table = new DataTable(); table.Columns.Add("Name"); table.Columns.Add("Gender"); table.Columns.Add("Age"); table.Columns.Add("Date of Service"); table.Columns.Add("Referral Source"); DataRow row; string command = @" SELECT p.LName AS PLName, p.FName AS PFName, p.MiddleI, p.Gender, p.Preferred, l.ProcDate, p.Birthdate, r.LName, r.FName, r.IsDoctor FROM patient p INNER JOIN procedurelog l ON l.PatNum = p.PatNum INNER JOIN procedurecode c ON c.CodeNum = l.CodeNum INNER JOIN refattach a ON p.PatNum = a.PatNum INNER JOIN referral r ON a.ReferralNum = r.ReferralNum WHERE (c.ProcCode = 01101 OR c.ProcCode = 01102 OR c.ProcCode = 01103) AND (l.ProcDate BETWEEN " + POut.DateT(dateStart) + @" AND " + POut.DateT(dateEnd) + @") GROUP BY p.PatNum"; DataTable raw = ReportsComplex.GetTable(command); Patient pat; String referralsource; for (int i = 0; i < raw.Rows.Count; i++) { row = table.NewRow(); pat = new Patient(); referralsource = raw.Rows[i]["Lname"].ToString() + ", " + raw.Rows[i]["FName"].ToString(); if (raw.Rows[i]["IsDoctor"].ToString() == "1") { referralsource += " (Doctor)"; } pat.LName = raw.Rows[i]["PLName"].ToString(); pat.FName = raw.Rows[i]["PFName"].ToString(); pat.MiddleI = raw.Rows[i]["MiddleI"].ToString(); pat.Preferred = raw.Rows[i]["Preferred"].ToString(); row["Name"] = pat.GetNameLF(); row["Gender"] = genderFormat(raw.Rows[i]["Gender"].ToString()); if (birthdate_to_age(raw.Rows[i]["Birthdate"].ToString()) < 150) { row["Age"] = birthdate_to_age(raw.Rows[i]["Birthdate"].ToString()); } else { row["Age"] = "N/A"; } row["Date of Service"] = raw.Rows[i]["ProcDate"].ToString(); row["Referral Source"] = referralsource; table.Rows.Add(row); } return(resort(table, "Referral Source", "ASC")); }
///<summary>If not using clinics then supply an empty list of clinicNums. dateStart and dateEnd can be MinVal/MaxVal to indicate "forever".</summary> public static DataTable GetNewToRecall(DateTime dateStart, DateTime dateEnd) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateStart, dateEnd)); } DataTable table = new DataTable(); table.Columns.Add("Name"); table.Columns.Add("Gender"); table.Columns.Add("Age"); table.Columns.Add("Type of Recall"); DataRow row; string command = @" SELECT p.LName, p.FName, p.MiddleI, p.Gender, p.Preferred, p.Birthdate, t1.Description FROM patient p INNER JOIN procedurelog l ON l.PatNum = p.PatNum INNER JOIN procedurecode c ON c.CodeNum = l.CodeNum INNER JOIN recalltrigger t2 ON c.CodeNum = t2.CodeNum INNER JOIN recalltype t1 ON t2.RecallTypeNum = t1.RecallTypeNum WHERE c.ProcCode = 01202 AND (l.ProcDate BETWEEN " + POut.DateT(dateStart) + @" AND " + POut.DateT(dateEnd) + @") AND l.PatNum IN (SELECT p2.PatNum FROM patient p2 INNER JOIN procedurelog l2 ON l2.PatNum = p2.PatNum INNER JOIN procedurecode c2 ON c2.CodeNum = l2.CodeNum WHERE (c2.ProcCode = 01101 OR c2.ProcCode = 01102 OR c2.ProcCode = 01103) AND l2.ProcDate < l.ProcDate AND l2.ProcDate > (DATE_SUB(l.ProcDate,INTERVAL 1 YEAR))) GROUP BY p.PatNum"; DataTable rawrecall = ReportsComplex.GetTable(command); Patient pat; String recalltype; for (int j = 0; j < rawrecall.Rows.Count; j++) { row = table.NewRow(); pat = new Patient(); recalltype = rawrecall.Rows[j]["Description"].ToString(); if (recalltype == null || recalltype == "") { recalltype = "Default"; } pat.LName = rawrecall.Rows[j]["LName"].ToString(); pat.FName = rawrecall.Rows[j]["FName"].ToString(); pat.MiddleI = rawrecall.Rows[j]["MiddleI"].ToString(); pat.Preferred = rawrecall.Rows[j]["Preferred"].ToString(); row["Name"] = pat.GetNameLF(); row["Gender"] = genderFormat(rawrecall.Rows[j]["Gender"].ToString()); if (birthdate_to_age(rawrecall.Rows[j]["Birthdate"].ToString()) < 150) { row["Age"] = birthdate_to_age(rawrecall.Rows[j]["Birthdate"].ToString()); } else { row["Age"] = "N/A"; } row["Type of Recall"] = recalltype; table.Rows.Add(row); } return(table); }
///<summary> dateStart and dateEnd can be MinVal/MaxVal to indicate "forever".</summary> public static List <object> GetNonProductivePracticeTime(DateTime dateStart, DateTime dateEnd) { List <object> tableAndTotal = new List <object>(); if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { tableAndTotal.Add(Meth.GetTable(MethodBase.GetCurrentMethod(), dateStart, dateEnd)); tableAndTotal.Add(""); return(tableAndTotal); } DataTable table = new DataTable(); table.Columns.Add("Name"); table.Columns.Add("Gender"); table.Columns.Add("Age"); table.Columns.Add("Postal Code"); table.Columns.Add("Date of Service"); table.Columns.Add("Primary Provider"); table.Columns.Add("Procedure Description"); table.Columns.Add("Non-Productive Practice Time"); DataRow row; string command = @" SELECT p.LName, p.FName, p.MiddleI, p.Gender, p.Zip, p.PriProv, p.Preferred, p.Birthdate, r.ProcDate, a.ProcDescript, a.Pattern FROM patient p JOIN procedurelog r ON r.PatNum = p.PatNum JOIN procedurecode c ON r.CodeNum = c.CodeNum JOIN appointment a ON a.AptNum = r.AptNum WHERE c.ProcCode = 99999 OR 99998 AND a.AptStatus = 5 AND a.AptDateTime BETWEEN " + POut.DateT(dateStart) + @" AND " + POut.DateT(dateEnd); int runningTimeTotal = 0; DataTable raw = ReportsComplex.GetTable(command); Patient pat; for (int i = 0; i < raw.Rows.Count; i++) { runningTimeTotal = runningTimeTotal + PatternToSeconds(raw.Rows[i]["Pattern"].ToString()); row = table.NewRow(); pat = new Patient(); pat.LName = raw.Rows[i]["LName"].ToString(); pat.FName = raw.Rows[i]["FName"].ToString(); pat.MiddleI = raw.Rows[i]["MiddleI"].ToString(); pat.Preferred = raw.Rows[i]["Preferred"].ToString(); row["Name"] = pat.GetNameLF(); row["Primary Provider"] = Providers.GetAbbr(PIn.Long(raw.Rows[i]["PriProv"].ToString())); row["Gender"] = genderFormat(raw.Rows[i]["Gender"].ToString()); row["Postal Code"] = raw.Rows[i]["Zip"].ToString(); row["Date of Service"] = raw.Rows[i]["ProcDate"].ToString().Substring(0, 10); row["Procedure Description"] = raw.Rows[i]["ProcDescript"].ToString(); row["Age"] = birthdate_to_age(raw.Rows[i]["Birthdate"].ToString()); row["Non-Productive Practice Time"] = sec_to_time(PatternToSeconds(raw.Rows[i]["Pattern"].ToString())); table.Rows.Add(row); } tableAndTotal.Add(table); tableAndTotal.Add(sec_to_time(runningTimeTotal)); return(tableAndTotal); }