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