public static DataTable GetGroupedTable(DateTime dateFrom, DateTime dateTo, List <long> listProvNums, List <long> listClinicNums, string procCode, bool hasAllProvs)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateFrom, dateTo, listProvNums, listClinicNums, procCode, hasAllProvs));
}
string query = "SELECT procs.ItemName,procs.ProcCode,procs.Descript,";
if (DataConnection.DBtype == DatabaseType.MySql)
{
query += "Count(*),FORMAT(ROUND(AVG(procs.fee),2),2) $AvgFee,SUM(procs.fee) AS $TotFee ";
}
else //Oracle needs quotes.
{
query += "Count(*),AVG(procs.fee) \"$AvgFee\",SUM(procs.fee) AS \"$TotFee\" ";
}
query += "FROM ( "
+ "SELECT procedurelog.ProcFee*(procedurelog.UnitQty+procedurelog.BaseUnits) -COALESCE(SUM(claimproc.WriteOff),0) fee, "
+ "procedurecode.ProcCode, procedurecode.Descript, definition.ItemName, definition.ItemOrder "
+ "FROM procedurelog "
+ "INNER JOIN procedurecode ON procedurelog.CodeNum=procedurecode.CodeNum "
+ "INNER JOIN definition ON definition.DefNum=procedurecode.ProcCat "
+ "LEFT JOIN claimproc ON claimproc.ProcNum=procedurelog.ProcNum AND claimproc.Status=" + POut.Int((int)ClaimProcStatus.CapComplete) + " "
+ "WHERE procedurelog.ProcStatus=" + POut.Int((int)ProcStat.C) + " ";
if (!hasAllProvs)
{
query += "AND procedurelog.ProvNum IN (" + String.Join(",", listProvNums) + ") ";
}
if (ReportsComplex.RunFuncOnReportServer(() => (!Prefs.GetBoolNoCache(PrefName.EasyNoClinics))))
{
query += "AND procedurelog.ClinicNum IN (" + String.Join(",", listClinicNums) + ") ";
}
query += "AND procedurecode.ProcCode LIKE '%" + POut.String(procCode) + "%' "
+ "AND procedurelog.ProcDate >= " + POut.Date(dateFrom) + " "
+ "AND procedurelog.ProcDate <= " + POut.Date(dateTo) + " "
+ "GROUP BY procedurelog.ProcNum ) procs "
+ "GROUP BY procs.ProcCode "
+ "ORDER BY procs.ItemOrder,procs.ProcCode";
return(ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(query)));
}
///<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 GetActivePatientTable(DateTime dateStart, DateTime dateEnd, List <long> listProvNums, List <long> listClinicNums, List <long> listBillingTypes, bool hasAllProvs, bool hasAllClinics, bool hasAllBilling)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateStart, dateEnd, listProvNums, listClinicNums, listBillingTypes, hasAllProvs, hasAllClinics, hasAllBilling));
}
bool easyNoClinics = ReportsComplex.RunFuncOnReportServer(() => Prefs.GetBoolNoCache(PrefName.EasyNoClinics));
List <Provider> listProvs = ReportsComplex.RunFuncOnReportServer(() => Providers.GetAll());
List <Def> listDefs = ReportsComplex.RunFuncOnReportServer(() => Defs.GetDefsNoCache(DefCat.BillingTypes));
List <Clinic> listClinics = ReportsComplex.RunFuncOnReportServer(() => Clinics.GetClinicsNoCache());
DataTable table = new DataTable();
table.Columns.Add("name");
table.Columns.Add("priProv");
table.Columns.Add("Address");
table.Columns.Add("Address2");
table.Columns.Add("City");
table.Columns.Add("State");
table.Columns.Add("Zip");
table.Columns.Add("carrier");
table.Columns.Add("HmPhone");
table.Columns.Add("WkPhone");
table.Columns.Add("WirelessPhone");
table.Columns.Add("billingType");
table.Columns.Add("secProv");
table.Columns.Add("clinic");
DataRow row;
string command = @"
SELECT patient.PatNum,patient.LName,patient.FName,patient.MiddleI,patient.Preferred,carrier.CarrierName,patient.BillingType,patient.PriProv,patient.SecProv,
patient.HmPhone,patient.WkPhone,patient.WirelessPhone,patient.Address,patient.Address2,patient.City,patient.State,patient.Zip,patient.ClinicNum,provider.Abbr
FROM procedurelog
INNER JOIN patient ON patient.PatNum=procedurelog.PatNum AND PatStatus=" + POut.Int((int)PatientStatus.Patient) + @"
LEFT JOIN patplan ON patplan.PatNum=patient.PatNum AND patplan.Ordinal=1
LEFT JOIN inssub ON inssub.InsSubNum=patplan.InsSubNum
LEFT JOIN insplan ON insplan.PlanNum=inssub.PlanNum
LEFT JOIN carrier ON carrier.CarrierNum=insplan.CarrierNum
LEFT JOIN provider ON provider.ProvNum=patient.PriProv
WHERE procedurelog.ProcStatus=" + POut.Int((int)ProcStat.C) + @"
AND procedurelog.ProcDate BETWEEN " + POut.DateT(dateStart) + @" AND " + POut.DateT(dateEnd);
if (!hasAllProvs)
{
command += @" AND (patient.PriProv IN(" + String.Join(",", listProvNums) + ") OR patient.SecProv IN(" + String.Join(",", listProvNums) + ")) ";
}
if (listClinicNums.Count > 0)
{
command += "AND patient.ClinicNum IN(" + String.Join(",", listClinicNums) + ") ";
}
command += "AND patient.BillingType IN(" + String.Join(",", listBillingTypes) + ") ";
if (DataConnection.DBtype == DatabaseType.MySql)
{
command += "GROUP BY patient.PatNum";
}
else //Oracle
{
command += @"GROUP BY patient.PatNum,patient.LName,patient.FName,patient.MiddleI,patient.Preferred,carrier.CarrierName
,patient.BillingType,patient.PriProv,patient.SecProv,patient.HmPhone,patient.WkPhone,patient.WirelessPhone
,patient.Address,patient.Address2,patient.City,patient.State,patient.Zip,patient.ClinicNum" ;
}
if (easyNoClinics)
{
command += " ORDER BY provider.Abbr,patient.LName,patient.FName";
}
else //Using clinics
{
command += " ORDER BY patient.ClinicNum,provider.Abbr,patient.LName,patient.FName";
}
DataTable raw = ReportsComplex.RunFuncOnReportServer(() => ReportsComplex.GetTable(command));
Patient pat;
for (int i = 0; i < raw.Rows.Count; i++)
{
Def billingType = listDefs.FirstOrDefault(x => x.DefNum == PIn.Long(raw.Rows[i]["BillingType"].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["priProv"] = Providers.GetAbbr(PIn.Long(raw.Rows[i]["PriProv"].ToString()));
row["Address"] = raw.Rows[i]["Address"].ToString();
row["Address2"] = raw.Rows[i]["Address2"].ToString();
row["City"] = raw.Rows[i]["City"].ToString();
row["State"] = raw.Rows[i]["State"].ToString();
row["Zip"] = raw.Rows[i]["Zip"].ToString();
row["Carrier"] = raw.Rows[i]["CarrierName"].ToString();
row["HmPhone"] = raw.Rows[i]["HmPhone"].ToString();
row["WkPhone"] = raw.Rows[i]["WkPhone"].ToString();
row["WirelessPhone"] = raw.Rows[i]["WirelessPhone"].ToString();
row["billingType"] = (billingType == null) ? "" : billingType.ItemValue;
row["secProv"] = Providers.GetLName(PIn.Long(raw.Rows[i]["SecProv"].ToString()), listProvs);
if (!easyNoClinics) //Using clinics
{
string clinicDesc = Clinics.GetDesc(PIn.Long(raw.Rows[i]["ClinicNum"].ToString()), listClinics);
row["clinic"] = (clinicDesc == "")?Lans.g("FormRpPayPlans", "Unassigned"):clinicDesc;
}
table.Rows.Add(row);
}
return(table);
}
public static DataTable GetInsAgingTable(RpAgingParamObject rpo)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), rpo));
}
#region Insurance Aging
string asOfDateStr = POut.Date(rpo.AsOfDate);
string thirtyDaysAgo = POut.Date(rpo.AsOfDate.AddDays(-30));
string sixtyDaysAgo = POut.Date(rpo.AsOfDate.AddDays(-60));
string ninetyDaysAgo = POut.Date(rpo.AsOfDate.AddDays(-90));
string patOrGuar = (rpo.IsGroupByFam?"guar":"patient");
string command = "SELECT guarAging.PatNum,";
if (ReportsComplex.RunFuncOnReportServer(() => Prefs.GetBoolNoCache(PrefName.ReportsShowPatNum)))
{
command += DbHelper.Concat("guarAging.PatNum", "' - '", "guarAging.LName", "', '", "guarAging.FName", "' '", "guarAging.MiddleI");
}
else
{
command += DbHelper.Concat("guarAging.LName", "', '", "guarAging.FName", "' '", "guarAging.MiddleI");
}
command += @" PatName,guarAging.InsPayEst_0_30,guarAging.InsPayEst_31_60,guarAging.InsPayEst_61_90,guarAging.InsPayEst_90,guarAging.InsPayEst_Total
FROM (
SELECT " + patOrGuar + ".PatNum," + patOrGuar + ".LName," + patOrGuar + ".FName," + patOrGuar + @".MiddleI,
SUM(CASE WHEN cp.ProcDate >= " + thirtyDaysAgo + @" THEN cp.InsPayEst ELSE 0 END) InsPayEst_0_30,
SUM(CASE WHEN cp.ProcDate < " + thirtyDaysAgo + " AND cp.ProcDate >= " + sixtyDaysAgo + @" THEN cp.InsPayEst ELSE 0 END) InsPayEst_31_60,
SUM(CASE WHEN cp.ProcDate < " + sixtyDaysAgo + " AND cp.ProcDate >= " + ninetyDaysAgo + @" THEN cp.InsPayEst ELSE 0 END) InsPayEst_61_90,
SUM(CASE WHEN cp.ProcDate < " + ninetyDaysAgo + @" THEN cp.InsPayEst ELSE 0 END) InsPayEst_90,
SUM(cp.InsPayEst) InsPayEst_Total
FROM claimproc cp
INNER JOIN patient ON patient.PatNum=cp.PatNum
" + (rpo.IsGroupByFam?"INNER JOIN patient guar ON patient.Guarantor=guar.PatNum":"") + @"
WHERE cp.Status IN (" + (int)ClaimProcStatus.NotReceived + "," + (int)ClaimProcStatus.Received + @")
AND cp.ProcDate <= " + asOfDateStr + @"
AND (cp.Status = " + (int)ClaimProcStatus.NotReceived + " OR (cp.Status = " + (int)ClaimProcStatus.Received + " AND cp.DateCP > " + asOfDateStr + ")) ";
if (rpo.ListBillTypes.Count > 0) //if all bill types is selected, list will be empty
{
command += "AND " + patOrGuar + ".BillingType IN (" + string.Join(",", rpo.ListBillTypes.Select(x => POut.Long(x))) + ") ";
}
if (rpo.ListProvNums.Count > 0) //if all provs is selected, list will be empty
{
command += "AND " + patOrGuar + ".PriProv IN (" + string.Join(",", rpo.ListProvNums.Select(x => POut.Long(x))) + ") ";
}
if (rpo.ListClinicNums.Count > 0) //listClin may contain "Unassigned" clinic with ClinicNum 0, in which case it will also be in the query string
{
command += "AND " + patOrGuar + ".ClinicNum IN (" + string.Join(",", rpo.ListClinicNums.Select(x => POut.Long(x))) + ") ";
}
command += "GROUP BY " + patOrGuar + @".PatNum) guarAging
WHERE (guarAging.InsPayEst_0_30 > 0.005
OR guarAging.InsPayEst_31_60 > 0.005
OR guarAging.InsPayEst_61_90 > 0.005
OR guarAging.InsPayEst_90 > 0.005
OR guarAging.InsPayEst_Total > 0.005)
ORDER BY guarAging.LName,guarAging.FName" ;
ReportComplexEvent.Fire(new ODEventArgs("ReportComplexEvent", Lans.g("ReportComplex", "Running Insurance Estimate Query...")));
DataTable insTable = ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(command));
#endregion Insurance Aging
#region Regular Aging
DataTable regAging = ReportsComplex.RunFuncOnReportServer(() => RpAging.GetAgingTable(rpo));
#endregion Regular Aging
#region Merge Insurance and Regular Aging
DataTable insAgingTable = new DataTable();
insAgingTable.Columns.Add("PatName");
insAgingTable.Columns.Add("InsPayEst_0_30");
insAgingTable.Columns.Add("InsPayEst_31_60");
insAgingTable.Columns.Add("InsPayEst_61_90");
insAgingTable.Columns.Add("InsPayEst_90");
insAgingTable.Columns.Add("InsPayEst_Total");
insAgingTable.Columns.Add("PatBal_0_30");
insAgingTable.Columns.Add("PatBal_31_60");
insAgingTable.Columns.Add("PatBal_61_90");
insAgingTable.Columns.Add("PatBal_90");
insAgingTable.Columns.Add("PatBal_Total");
insAgingTable.Columns.Add("InsWoChange");
insAgingTable.Columns.Add("PatBalEst");
Dictionary <long, DataRow> dictPatInsAgingRows = new Dictionary <long, DataRow>();
#region Add All Insurance Aging Rows to Dictionary
foreach (DataRow insRow in insTable.Rows)
{
DataRow newRow = insAgingTable.NewRow(); //create a new row with the structure of the new table
//copy the ins aging table's values over to the new row and fill the pat bal columns with -insPayEst for the appropriate bucket
newRow["PatName"] = insRow["PatName"];
newRow["InsPayEst_0_30"] = insRow["InsPayEst_0_30"];
newRow["InsPayEst_31_60"] = insRow["InsPayEst_31_60"];
newRow["InsPayEst_61_90"] = insRow["InsPayEst_61_90"];
newRow["InsPayEst_90"] = insRow["InsPayEst_90"];
newRow["InsPayEst_Total"] = insRow["InsPayEst_Total"];
dictPatInsAgingRows[PIn.Long(insRow["PatNum"].ToString())] = newRow; //PatNum only used to link insAgingRows to regAgingRows
}
#endregion Add All Insurance Aging Rows to Dictionary
#region Add Regular Aging Rows and Apply Insurance Estimates to Dictionary
foreach (DataRow row in regAging.Rows)
{
long patNumCur = PIn.Long(row["PatNum"].ToString());
DataRow insAgingRow;
if (dictPatInsAgingRows.TryGetValue(patNumCur, out insAgingRow))
{
//check to see if that patient exists in the insurance aging report
insAgingRow["PatBal_0_30"] = PIn.Double(row["Bal_0_30"].ToString()) - PIn.Double(insAgingRow["InsPayEst_0_30"].ToString());
insAgingRow["PatBal_31_60"] = PIn.Double(row["Bal_31_60"].ToString()) - PIn.Double(insAgingRow["InsPayEst_31_60"].ToString());
insAgingRow["PatBal_61_90"] = PIn.Double(row["Bal_61_90"].ToString()) - PIn.Double(insAgingRow["InsPayEst_61_90"].ToString());
insAgingRow["PatBal_90"] = PIn.Double(row["BalOver90"].ToString()) - PIn.Double(insAgingRow["InsPayEst_90"].ToString());
insAgingRow["PatBal_Total"] = PIn.Double(row["BalTotal"].ToString()) - PIn.Double(insAgingRow["InsPayEst_Total"].ToString());
insAgingRow["InsWoChange"] = PIn.Double(row["InsWoEst"].ToString());
insAgingRow["PatBalEst"] = PIn.Double(insAgingRow["PatBal_Total"].ToString()) - PIn.Double(insAgingRow["InsWoChange"].ToString());
}
else //if pat doesn't exist in ins aging report, create a new row with 0.00 insurance values and fill the patient aging values
{
insAgingRow = insAgingTable.NewRow();
insAgingRow["PatName"] = row["PatName"];
insAgingRow["InsPayEst_0_30"] = PIn.Double("0.00");
insAgingRow["InsPayEst_31_60"] = PIn.Double("0.00");
insAgingRow["InsPayEst_61_90"] = PIn.Double("0.00");
insAgingRow["InsPayEst_90"] = PIn.Double("0.00");
insAgingRow["InsPayEst_Total"] = PIn.Double("0.00");
insAgingRow["PatBal_0_30"] = PIn.Double(row["Bal_0_30"].ToString());
insAgingRow["PatBal_31_60"] = PIn.Double(row["Bal_31_60"].ToString());
insAgingRow["PatBal_61_90"] = PIn.Double(row["Bal_61_90"].ToString());
insAgingRow["PatBal_90"] = PIn.Double(row["BalOver90"].ToString());
insAgingRow["PatBal_Total"] = PIn.Double(row["BalTotal"].ToString());
insAgingRow["InsWoChange"] = PIn.Double(row["InsWoEst"].ToString());
insAgingRow["PatBalEst"] = PIn.Double(row["BalTotal"].ToString()) - PIn.Double(row["InsWoEst"].ToString()) - PIn.Double(row["InsPayEst"].ToString());
dictPatInsAgingRows[patNumCur] = insAgingRow;
}
}
#endregion Add Regular Aging Rows and Apply Insurance Estimates to Dictionary
dictPatInsAgingRows = dictPatInsAgingRows.OrderBy(x => x.Value["PatName"]).ToDictionary(x => x.Key, x => x.Value);
#endregion Merge Insurance and Regular Aging
#region Add Rows to Table Filtered by Age
foreach (DataRow rowCur in dictPatInsAgingRows.Values)
{
double insPayEstTotal = PIn.Double(rowCur["InsPayEst_Total"].ToString());
double patBalTotal = PIn.Double(rowCur["PatBal_Total"].ToString()) + insPayEstTotal;
if (patBalTotal <= -0.005)
{
insAgingTable.Rows.Add(rowCur);
continue;
}
double insWoChange = PIn.Double(rowCur["InsWoChange"].ToString());
double patBal0_30 = PIn.Double(rowCur["PatBal_0_30"].ToString()) + PIn.Double(rowCur["InsPayEst_0_30"].ToString());
double patBal31_60 = PIn.Double(rowCur["PatBal_31_60"].ToString()) + PIn.Double(rowCur["InsPayEst_31_60"].ToString());
double patBal61_90 = PIn.Double(rowCur["PatBal_61_90"].ToString()) + PIn.Double(rowCur["InsPayEst_61_90"].ToString());
double patBal90 = PIn.Double(rowCur["PatBal_90"].ToString()) + PIn.Double(rowCur["InsPayEst_90"].ToString());
if ((!insPayEstTotal.IsZero() || !insWoChange.IsZero()) && new[] { patBal0_30, patBal31_60, patBal61_90, patBal90 }.All(x => x < 0.005))
{
insAgingTable.Rows.Add(rowCur);
continue;
}
if (patBal90 >= 0.005 || //always include if bal over 90
(rpo.AccountAge <= AgeOfAccount.Over60 && patBal61_90 >= 0.005) || //if age 60, 30, or Any, include if bal 61 to 90
(rpo.AccountAge <= AgeOfAccount.Over30 && patBal31_60 >= 0.005) || //if age 30 or Any, include if bal 31 to 60
(rpo.AccountAge == AgeOfAccount.Any && patBal0_30 >= 0.005)) //if Any age, include if bal 0 to 30
{
insAgingTable.Rows.Add(rowCur);
}
}
#endregion Add Rows to Table Filtered by Age
return(insAgingTable);
}
///<summary>If not using clinics, or for all clinics with clinics enabled, supply an empty list of clinicNums. If the user is restricted, for all
///clinics supply only those clinics for which the user has permission to access, otherwise it will be run for all clinics.</summary>
public static DataTable GetPatTable(DateTime dateFrom, DateTime dateTo, List <long> listProvNums, List <long> listClinicNums, List <long> listPatientTypes,
bool hasAllProvs, bool hasAllClinics, bool hasPatientTypes, bool isGroupedByPatient, bool isUnearnedIncluded, bool doShowProvSeparate)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateFrom, dateTo, listProvNums, listClinicNums, listPatientTypes, hasAllProvs, hasAllClinics,
hasPatientTypes, isGroupedByPatient, isUnearnedIncluded, doShowProvSeparate));
}
//reports should no longer use the cache
bool hasClinicsEnabled = ReportsComplex.RunFuncOnReportServer(() => (!Prefs.GetBoolNoCache(PrefName.EasyNoClinics)));
//patient payments-----------------------------------------------------------------------------------------
//the selected columns have to remain in this order due to the way the report complex populates the returned sheet
string queryPat = "SELECT payment.PayDate DatePay,"
+ "MAX(" + DbHelper.Concat("patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI") + ") lfname,GROUP_CONCAT(DISTINCT provider.Abbr),";
if (hasClinicsEnabled)
{
queryPat += "clinic.Description clinicDesc,";
}
queryPat += "payment.CheckNum,SUM(COALESCE(paysplit.SplitAmt,0)) amt,payment.PayNum,ItemName,payment.PayType "
+ "FROM payment "
+ "LEFT JOIN paysplit ON payment.PayNum=paysplit.PayNum "
+ "LEFT JOIN patient ON payment.PatNum=patient.PatNum "
+ "LEFT JOIN provider ON paysplit.ProvNum=provider.ProvNum "
+ "LEFT JOIN definition ON payment.PayType=definition.DefNum ";
if (hasClinicsEnabled)
{
queryPat += "LEFT JOIN clinic ON clinic.ClinicNum=paysplit.ClinicNum ";
}
queryPat += "WHERE payment.PayDate BETWEEN " + POut.Date(dateFrom) + " AND " + POut.Date(dateTo) + " ";
if (hasClinicsEnabled && listClinicNums.Count > 0)
{
queryPat += "AND paysplit.ClinicNum IN(" + string.Join(",", listClinicNums.Select(x => POut.Long(x))) + ") ";
}
if (!hasAllProvs && listProvNums.Count > 0)
{
queryPat += "AND paysplit.ProvNum IN(" + string.Join(",", listProvNums.Select(x => POut.Long(x))) + ") ";
}
if (!hasPatientTypes && listPatientTypes.Count > 0)
{
queryPat += "AND payment.PayType IN (" + string.Join(",", listPatientTypes.Select(x => POut.Long(x))) + ") ";
}
if (!isUnearnedIncluded) //UnearnedType of 0 means the paysplit is NOT unearned
{
queryPat += "AND paysplit.UnearnedType=0 ";
}
queryPat += "GROUP BY payment.PayNum,payment.PayDate,payment.CheckNum,definition.ItemName,payment.PayType ";
if (doShowProvSeparate)
{
queryPat += ",provider.ProvNum ";
}
if (hasClinicsEnabled)
{
queryPat += ",clinic.Description ";
}
if (isGroupedByPatient)
{
queryPat += ",patient.PatNum ";
}
queryPat += "ORDER BY payment.PayType,payment.PayDate,lfname";
if (!hasPatientTypes && listPatientTypes.Count == 0)
{
queryPat = DbHelper.LimitOrderBy(queryPat, 0);
}
return(ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(queryPat)));
}
///<summary>If not using clinics then supply an empty list of clinicNums. listClinicNums must have at least one item if using clinics.</summary>
public static DataTable GetInsTable(DateTime dateFrom, DateTime dateTo, List <long> listProvNums, List <long> listClinicNums,
List <long> listInsuranceTypes, List <long> listClaimPayGroups, bool hasAllProvs, bool hasAllClinics, bool hasInsuranceTypes, bool isGroupedByPatient,
bool hasAllClaimPayGroups, bool doShowProvSeparate)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateFrom, dateTo, listProvNums, listClinicNums, listInsuranceTypes, listClaimPayGroups,
hasAllProvs, hasAllClinics, hasInsuranceTypes, isGroupedByPatient, hasAllClaimPayGroups, doShowProvSeparate));
}
string whereProv = "";
if (!hasAllProvs)
{
whereProv += " AND claimproc.ProvNum IN(";
for (int i = 0; i < listProvNums.Count; i++)
{
if (i > 0)
{
whereProv += ",";
}
whereProv += POut.Long(listProvNums[i]);
}
whereProv += ") ";
}
string whereClin = "";
//reports should no longer use the cache
bool hasClinicsEnabled = ReportsComplex.RunFuncOnReportServer(() => (!Prefs.GetBoolNoCache(PrefName.EasyNoClinics)));
if (hasClinicsEnabled)
{
whereClin += " AND claimproc.ClinicNum IN(";
for (int i = 0; i < listClinicNums.Count; i++)
{
if (i > 0)
{
whereClin += ",";
}
whereClin += POut.Long(listClinicNums[i]);
}
whereClin += ") ";
}
string whereClaimPayGroup = "";
if (!hasAllClaimPayGroups)
{
whereClaimPayGroup = " AND PayGroup IN (" + String.Join(",", listClaimPayGroups) + ") ";
}
string queryIns =
@"SELECT claimproc.DateCP,carrier.CarrierName,MAX("
+ DbHelper.Concat("patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI") + @") lfname,GROUP_CONCAT(DISTINCT provider.Abbr) Provider, ";
if (hasClinicsEnabled)
{
queryIns += "clinic.Description clinicDesc, ";
}
queryIns += @"claimpayment.CheckNum,SUM(claimproc.InsPayAmt) amt,claimproc.ClaimNum,claimpayment.PayType
FROM claimproc
LEFT JOIN insplan ON claimproc.PlanNum = insplan.PlanNum
LEFT JOIN patient ON claimproc.PatNum = patient.PatNum
LEFT JOIN carrier ON carrier.CarrierNum = insplan.CarrierNum
LEFT JOIN provider ON provider.ProvNum=claimproc.ProvNum
LEFT JOIN claimpayment ON claimproc.ClaimPaymentNum = claimpayment.ClaimPaymentNum " ;
if (hasClinicsEnabled)
{
queryIns += "LEFT JOIN clinic ON clinic.ClinicNum=claimproc.ClinicNum ";
}
queryIns += "WHERE (claimproc.Status=1 OR claimproc.Status=4) " //received or supplemental
+ whereProv
+ whereClin
+ whereClaimPayGroup
+ "AND claimpayment.CheckDate >= " + POut.Date(dateFrom) + " "
+ "AND claimpayment.CheckDate <= " + POut.Date(dateTo) + " ";
if (!hasInsuranceTypes && listInsuranceTypes.Count > 0)
{
queryIns += "AND claimpayment.PayType IN (";
for (int i = 0; i < listInsuranceTypes.Count; i++)
{
if (i > 0)
{
queryIns += ",";
}
queryIns += POut.Long(listInsuranceTypes[i]);
}
queryIns += ") ";
}
queryIns += @"GROUP BY claimproc.DateCP,claimproc.ClaimPaymentNum,";
if (doShowProvSeparate)
{
queryIns += @"provider.ProvNum,";
}
if (hasClinicsEnabled)
{
queryIns += "claimproc.ClinicNum,clinic.Description,";
}
queryIns += "carrier.CarrierName,claimpayment.CheckNum";
if (isGroupedByPatient)
{
queryIns += ",patient.PatNum";
}
queryIns += " ORDER BY claimpayment.PayType,claimproc.DateCP,lfname";
if (!hasInsuranceTypes && listInsuranceTypes.Count == 0)
{
queryIns = DbHelper.LimitOrderBy(queryIns, 0);
}
return(ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(queryIns)));
}
public static DataTable GetInsAgingTable(RpAgingParamObject rpo)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), rpo));
}
#region Insurance Aging
string asOfDateStr = POut.Date(rpo.AsOfDate);
string thirtyDaysAgo = POut.Date(rpo.AsOfDate.AddDays(-30));
string sixtyDaysAgo = POut.Date(rpo.AsOfDate.AddDays(-60));
string ninetyDaysAgo = POut.Date(rpo.AsOfDate.AddDays(-90));
string patOrGuar = (rpo.IsGroupByFam?"guar":"patient");
string command = "SELECT guarAging.PatNum,";
if (ReportsComplex.RunFuncOnReportServer(() => Prefs.GetBoolNoCache(PrefName.ReportsShowPatNum)))
{
command += DbHelper.Concat("guarAging.PatNum", "' - '", "guarAging.LName", "', '", "guarAging.FName", "' '", "guarAging.MiddleI");
}
else
{
command += DbHelper.Concat("guarAging.LName", "', '", "guarAging.FName", "' '", "guarAging.MiddleI");
}
command += @" PatName," + (rpo.IsDetailedBreakdown ? "guarAging.CarrierName,guarAging.CarrierNum,guarAging.GroupName,guarAging.PlanNum," : "")
+ @"guarAging.InsPayEst_0_30,guarAging.InsPayEst_31_60,guarAging.InsPayEst_61_90,guarAging.InsPayEst_90,guarAging.InsPayEst_Total
FROM (
SELECT " + patOrGuar + ".PatNum," + patOrGuar + ".LName," + patOrGuar + ".FName," + patOrGuar + @".MiddleI,"
+ (rpo.IsDetailedBreakdown ? "carrier.CarrierName,carrier.CarrierNum,insplan.GroupName,insplan.PlanNum," : "")
+ @"SUM(CASE WHEN cp.ProcDate >= " + thirtyDaysAgo + @" THEN cp.InsPayEst ELSE 0 END) InsPayEst_0_30,
SUM(CASE WHEN cp.ProcDate < " + thirtyDaysAgo + " AND cp.ProcDate >= " + sixtyDaysAgo + @" THEN cp.InsPayEst ELSE 0 END) InsPayEst_31_60,
SUM(CASE WHEN cp.ProcDate < " + sixtyDaysAgo + " AND cp.ProcDate >= " + ninetyDaysAgo + @" THEN cp.InsPayEst ELSE 0 END) InsPayEst_61_90,
SUM(CASE WHEN cp.ProcDate < " + ninetyDaysAgo + @" THEN cp.InsPayEst ELSE 0 END) InsPayEst_90,
SUM(cp.InsPayEst) InsPayEst_Total
FROM claimproc cp
INNER JOIN patient ON patient.PatNum=cp.PatNum"
+ (rpo.IsGroupByFam ? @"
INNER JOIN patient guar ON patient.Guarantor=guar.PatNum" : "")
+ (rpo.IsDetailedBreakdown ? @"
LEFT JOIN insplan ON insplan.PlanNum=cp.PlanNum
LEFT JOIN carrier ON carrier.CarrierNum=insplan.CarrierNum" : "");
command += @"
WHERE cp.Status IN (" + (int)ClaimProcStatus.NotReceived + "," + (int)ClaimProcStatus.Received + @")
AND cp.ProcDate <= " + asOfDateStr + @"
AND (cp.Status = " + (int)ClaimProcStatus.NotReceived + " OR (cp.Status = " + (int)ClaimProcStatus.Received + " AND cp.DateCP > " + asOfDateStr + ")) ";
if (rpo.IsDetailedBreakdown)
{
if (!string.IsNullOrWhiteSpace(rpo.CarrierNameFilter))
{
command += @"
AND (carrier.CarrierNum IS NULL OR carrier.CarrierName LIKE '%" + rpo.CarrierNameFilter + "%') ";
}
if (!string.IsNullOrWhiteSpace(rpo.GroupNameFilter))
{
command += @"
AND (insplan.GroupName IS NULL OR insplan.GroupName LIKE '%" + rpo.GroupNameFilter + "%') ";
}
}
if (rpo.ListBillTypes.Count > 0) //if all bill types is selected, list will be empty
{
command += "AND " + patOrGuar + ".BillingType IN (" + string.Join(",", rpo.ListBillTypes.Select(x => POut.Long(x))) + ") ";
}
if (rpo.ListProvNums.Count > 0) //if all provs is selected, list will be empty
{
command += "AND " + patOrGuar + ".PriProv IN (" + string.Join(",", rpo.ListProvNums.Select(x => POut.Long(x))) + ") ";
}
if (rpo.ListClinicNums.Count > 0) //listClin may contain "Unassigned" clinic with ClinicNum 0, in which case it will also be in the query string
{
command += "AND " + patOrGuar + ".ClinicNum IN (" + string.Join(",", rpo.ListClinicNums.Select(x => POut.Long(x))) + ") ";
}
command += "GROUP BY " + patOrGuar + ".PatNum"
+ (rpo.GroupByCarrier ? ",carrier.CarrierName" : "")
+ (rpo.GroupByGroupName ? ",insplan.GroupName" : "") + @") guarAging
WHERE (guarAging.InsPayEst_0_30 > 0.005
OR guarAging.InsPayEst_31_60 > 0.005
OR guarAging.InsPayEst_61_90 > 0.005
OR guarAging.InsPayEst_90 > 0.005
OR guarAging.InsPayEst_Total > 0.005)
ORDER BY guarAging.LName,guarAging.FName" ;
ReportComplexEvent.Fire(ODEventType.ReportComplex, Lans.g("ReportComplex", "Running Insurance Estimate Query..."));
DataTable insTable = ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(command));
#endregion Insurance Aging
#region Regular Aging
DataTable regAging = new DataTable();
//Don't run regular aging if detailed breakdown as it can take a long time to run for large customers.
if (!rpo.IsDetailedBreakdown)
{
regAging = ReportsComplex.RunFuncOnReportServer(() => RpAging.GetAgingTable(rpo));
}
#endregion Regular Aging
#region Merge Insurance and Regular Aging
DataTable insAgingTable = new DataTable();
insAgingTable.Columns.Add("PatName");
if (rpo.IsDetailedBreakdown)
{
insAgingTable.Columns.Add("CarrierName");
insAgingTable.Columns.Add("GroupName");
}
insAgingTable.Columns.Add("InsPayEst_0_30");
insAgingTable.Columns.Add("InsPayEst_31_60");
insAgingTable.Columns.Add("InsPayEst_61_90");
insAgingTable.Columns.Add("InsPayEst_90");
insAgingTable.Columns.Add("InsPayEst_Total");
insAgingTable.Columns.Add("PatBal_0_30");
insAgingTable.Columns.Add("PatBal_31_60");
insAgingTable.Columns.Add("PatBal_61_90");
insAgingTable.Columns.Add("PatBal_90");
insAgingTable.Columns.Add("PatBal_Total");
insAgingTable.Columns.Add("InsWoChange");
insAgingTable.Columns.Add("PatBalEst");
Dictionary <long, DataRow> dictPatInsAgingRows = new Dictionary <long, DataRow>();
Dictionary <AgingTableRowId, DataRow> dictDetailedInsAgingRows = new Dictionary <AgingTableRowId, DataRow>();
#region Add All Insurance Aging Rows to Dictionary
foreach (DataRow insRow in insTable.Rows)
{
DataRow newRow = insAgingTable.NewRow(); //create a new row with the structure of the new table
//copy the ins aging table's values over to the new row and fill the pat bal columns with -insPayEst for the appropriate bucket
newRow["PatName"] = insRow["PatName"];
if (rpo.IsDetailedBreakdown)
{
newRow["CarrierName"] = insRow["CarrierName"];
newRow["GroupName"] = insRow["GroupName"];
}
newRow["InsPayEst_0_30"] = insRow["InsPayEst_0_30"];
newRow["InsPayEst_31_60"] = insRow["InsPayEst_31_60"];
newRow["InsPayEst_61_90"] = insRow["InsPayEst_61_90"];
newRow["InsPayEst_90"] = insRow["InsPayEst_90"];
newRow["InsPayEst_Total"] = insRow["InsPayEst_Total"];
dictPatInsAgingRows[PIn.Long(insRow["PatNum"].ToString())] = newRow; //PatNum only used to link insAgingRows to regAgingRows
dictDetailedInsAgingRows[AgingTableRowId.FromDataRow(insRow)] = newRow; //Only used for Detailed report.
}
#endregion Add All Insurance Aging Rows to Dictionary
#region Add Regular Aging Rows and Apply Insurance Estimates to Dictionary
foreach (DataRow row in regAging.Rows)
{
long patNumCur = PIn.Long(row["PatNum"].ToString());
DataRow insAgingRow;
if (dictPatInsAgingRows.TryGetValue(patNumCur, out insAgingRow))
{
//check to see if that patient exists in the insurance aging report
insAgingRow["PatBal_0_30"] = PIn.Double(row["Bal_0_30"].ToString()) - PIn.Double(insAgingRow["InsPayEst_0_30"].ToString());
insAgingRow["PatBal_31_60"] = PIn.Double(row["Bal_31_60"].ToString()) - PIn.Double(insAgingRow["InsPayEst_31_60"].ToString());
insAgingRow["PatBal_61_90"] = PIn.Double(row["Bal_61_90"].ToString()) - PIn.Double(insAgingRow["InsPayEst_61_90"].ToString());
insAgingRow["PatBal_90"] = PIn.Double(row["BalOver90"].ToString()) - PIn.Double(insAgingRow["InsPayEst_90"].ToString());
insAgingRow["PatBal_Total"] = PIn.Double(row["BalTotal"].ToString()) - PIn.Double(insAgingRow["InsPayEst_Total"].ToString());
insAgingRow["InsWoChange"] = PIn.Double(row["InsWoEst"].ToString());
insAgingRow["PatBalEst"] = PIn.Double(insAgingRow["PatBal_Total"].ToString()) - PIn.Double(insAgingRow["InsWoChange"].ToString());
}
else //if pat doesn't exist in ins aging report, create a new row with 0.00 insurance values and fill the patient aging values
{
insAgingRow = insAgingTable.NewRow();
insAgingRow["PatName"] = row["PatName"];
insAgingRow["InsPayEst_0_30"] = PIn.Double("0.00");
insAgingRow["InsPayEst_31_60"] = PIn.Double("0.00");
insAgingRow["InsPayEst_61_90"] = PIn.Double("0.00");
insAgingRow["InsPayEst_90"] = PIn.Double("0.00");
insAgingRow["InsPayEst_Total"] = PIn.Double("0.00");
insAgingRow["PatBal_0_30"] = PIn.Double(row["Bal_0_30"].ToString());
insAgingRow["PatBal_31_60"] = PIn.Double(row["Bal_31_60"].ToString());
insAgingRow["PatBal_61_90"] = PIn.Double(row["Bal_61_90"].ToString());
insAgingRow["PatBal_90"] = PIn.Double(row["BalOver90"].ToString());
insAgingRow["PatBal_Total"] = PIn.Double(row["BalTotal"].ToString());
insAgingRow["InsWoChange"] = PIn.Double(row["InsWoEst"].ToString());
insAgingRow["PatBalEst"] = PIn.Double(row["BalTotal"].ToString()) - PIn.Double(row["InsWoEst"].ToString()) - PIn.Double(row["InsPayEst"].ToString());
dictPatInsAgingRows[patNumCur] = insAgingRow;
}
}
#endregion Add Regular Aging Rows and Apply Insurance Estimates to Dictionary
dictPatInsAgingRows = dictPatInsAgingRows.OrderBy(x => x.Value["PatName"]).ToDictionary(x => x.Key, x => x.Value);
#endregion Merge Insurance and Regular Aging
#region Add Rows to Table Filtered by Age
if (rpo.IsDetailedBreakdown) //Remove patient columns for detailed breakdown
{
AddRowsFromDict <AgingTableRowId>(rpo, dictDetailedInsAgingRows, insAgingTable);
insAgingTable.Columns.Remove("PatBal_0_30");
insAgingTable.Columns.Remove("PatBal_31_60");
insAgingTable.Columns.Remove("PatBal_61_90");
insAgingTable.Columns.Remove("PatBal_90");
insAgingTable.Columns.Remove("PatBal_Total");
insAgingTable.Columns.Remove("InsWoChange");
insAgingTable.Columns.Remove("PatBalEst");
}
else
{
AddRowsFromDict <long>(rpo, dictPatInsAgingRows, insAgingTable);
}
#endregion Add Rows to Table Filtered by Age
return(insAgingTable);
}
public static DataSet GetMonthlyGoalDataSet(DateTime dateFrom, DateTime dateTo, List <Provider> listProvs, List <Clinic> listClinics, bool hasAllProvs
, bool hasAllClinics, PPOWriteoffDateCalc writeoffPayType, bool isCEMT = false)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetDS(MethodBase.GetCurrentMethod(), dateFrom, dateTo, listProvs, listClinics, hasAllProvs, hasAllClinics, writeoffPayType, isCEMT));
}
List <long> listClinicNums = listClinics.Select(x => x.ClinicNum).ToList();
List <long> listProvNums = listProvs.Select(x => x.ProvNum).ToList();
#region Procedures
string whereProv = "";
if (!hasAllProvs && listProvNums.Count > 0)
{
whereProv = "AND procedurelog.ProvNum IN (" + string.Join(",", listProvNums) + ") ";
}
string whereClin = "";
if (!hasAllClinics && listClinicNums.Count > 0)
{
whereClin = "AND procedurelog.ClinicNum IN (" + string.Join(",", listClinicNums) + ") ";
}
string command = "SELECT "
+ "procedurelog.ProcDate,procedurelog.ClinicNum,"
+ "SUM(procedurelog.ProcFee*(procedurelog.UnitQty+procedurelog.BaseUnits))-IFNULL(SUM(cp.WriteOff),0) Production "
+ "FROM procedurelog "
+ "LEFT JOIN (SELECT SUM(claimproc.WriteOff) AS WriteOff, claimproc.ProcNum FROM claimproc "
+ "WHERE claimproc.Status=7 " //only CapComplete writeoffs are subtracted here.
+ "GROUP BY claimproc.ProcNum) cp ON procedurelog.ProcNum=cp.ProcNum "
+ "WHERE procedurelog.ProcStatus = 2 "
+ whereProv
+ whereClin
+ "AND procedurelog.ProcDate >= " + POut.Date(dateFrom) + " "
+ "AND procedurelog.ProcDate <= " + POut.Date(dateTo) + " "
+ "GROUP BY ClinicNum,YEAR(procedurelog.ProcDate),MONTH(procedurelog.ProcDate),DAY(procedurelog.ProcDate)"; //Does not work for Oracle. Consider enhancing with DbHelper.Year(),DbHelper.Month()
command += " ORDER BY ClinicNum,ProcDate";
DataTable tableProduction = new DataTable();
if (isCEMT)
{
tableProduction = Db.GetTable(command);
}
else
{
tableProduction = ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(command));
}
tableProduction.TableName = "tableProduction";
#endregion
#region Adjustments
if (!hasAllProvs && listProvNums.Count > 0)
{
whereProv = "AND adjustment.ProvNum IN (" + string.Join(",", listProvNums) + ") ";
}
if (!hasAllClinics && listClinicNums.Count > 0)
{
whereClin = "AND adjustment.ClinicNum IN (" + string.Join(",", listClinicNums) + ") ";
}
command = "SELECT "
+ "adjustment.AdjDate,"
+ "adjustment.ClinicNum,"
+ "SUM(adjustment.AdjAmt) Adjustment "
+ "FROM adjustment "
+ "WHERE adjustment.AdjDate >= " + POut.Date(dateFrom) + " "
+ "AND adjustment.AdjDate <= " + POut.Date(dateTo) + " "
+ whereProv
+ whereClin
+ "GROUP BY ClinicNum,YEAR(adjustment.AdjDate),MONTH(adjustment.AdjDate),DAY(adjustment.AdjDate)";
command += " ORDER BY ClinicNum,AdjDate";
DataTable tableAdj = new DataTable();
if (isCEMT)
{
tableAdj = Db.GetTable(command);
}
else
{
tableAdj = ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(command));
}
tableAdj.TableName = "tableAdj";
#endregion
#region TableInsWriteoff
if (!hasAllProvs && listProvNums.Count > 0)
{
whereProv = "AND claimproc.ProvNum IN (" + string.Join(",", listProvNums) + ") ";
}
if (!hasAllClinics && listClinicNums.Count > 0)
{
whereClin = "AND claimproc.ClinicNum IN (" + string.Join(",", listClinicNums) + ") ";
}
if (writeoffPayType == PPOWriteoffDateCalc.InsPayDate)
{
command = "SELECT "
+ "claimproc.DateCP Date,"
+ "claimproc.ClinicNum,"
+ "SUM(claimproc.WriteOff) WriteOff "
+ "FROM claimproc "
+ "WHERE claimproc.DateCP >= " + POut.Date(dateFrom) + " "
+ "AND claimproc.DateCP <= " + POut.Date(dateTo) + " "
+ whereProv
+ whereClin
+ "AND claimproc.Status IN (" + (int)ClaimProcStatus.Received + "," + (int)ClaimProcStatus.Supplemental + ") "//received or supplemental
+ "GROUP BY ClinicNum,DATE(claimproc.DateCP) "
+ "ORDER BY ClinicNum,DateCP";
}
else if (writeoffPayType == PPOWriteoffDateCalc.ProcDate)
{
command = "SELECT "
+ "claimproc.ProcDate Date,"
+ "claimproc.ClinicNum,"
+ "SUM(claimproc.WriteOff) WriteOff "
+ "FROM claimproc "
+ "WHERE claimproc.ProcDate >= " + POut.Date(dateFrom) + " "
+ "AND claimproc.ProcDate <= " + POut.Date(dateTo) + " "
+ whereProv
+ whereClin
+ "AND claimproc.Status IN (" + (int)ClaimProcStatus.Received + "," + (int)ClaimProcStatus.Supplemental + "," + (int)ClaimProcStatus.NotReceived + ") "//received or supplemental or notreceived
+ "GROUP BY ClinicNum,DATE(claimproc.ProcDate) "
+ "ORDER BY ClinicNum,ProcDate";
}
else // writeoffPayType==PPOWriteoffDateCalc.ClaimPayDate
{
command = "SELECT "
+ "claimsnapshot.DateTEntry Date,"
+ "claimproc.ClinicNum,"
+ "SUM(CASE WHEN claimsnapshot.WriteOff=-1 THEN 0 ELSE claimsnapshot.WriteOff END) WriteOff "
+ "FROM claimproc "
+ "INNER JOIN claimsnapshot ON claimsnapshot.ClaimProcNum=claimProc.ClaimProcNum "
+ "WHERE " + DbHelper.BetweenDates("claimsnapshot.DateTEntry", dateFrom, dateTo) + " "
+ whereProv
+ whereClin
+ "AND claimproc.Status IN (" + (int)ClaimProcStatus.Received + "," + (int)ClaimProcStatus.Supplemental + "," + (int)ClaimProcStatus.NotReceived + ") "//received or supplemental or notreceived
+ "GROUP BY ClinicNum,DATE(claimsnapshot.DateTEntry) "
+ "ORDER BY ClinicNum,claimsnapshot.DateTEntry";
}
DataTable tableInsWriteoff = new DataTable();
if (isCEMT)
{
tableInsWriteoff = Db.GetTable(command);
}
else
{
tableInsWriteoff = ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(command));
}
tableInsWriteoff.TableName = "tableInsWriteoff";
#endregion
#region TableSched
DataTable tableSched = new DataTable();
//Reads from the procedurelog table instead of claimproc because we are looking for scheduled procedures.
if (!hasAllProvs && listProvNums.Count > 0)
{
whereProv = "AND procedurelog.ProvNum IN (" + string.Join(",", listProvNums) + ") ";
}
if (!hasAllClinics && listClinicNums.Count > 0)
{
whereClin = "AND procedurelog.ClinicNum IN (" + string.Join(",", listClinicNums) + ") ";
}
command = "SELECT " + DbHelper.DtimeToDate("t.AptDateTime") + " SchedDate,SUM(t.Fee-t.WriteoffEstimate) Amount,ClinicNum "
+ "FROM (SELECT appointment.AptDateTime,IFNULL(procedurelog.ProcFee*(procedurelog.UnitQty+procedurelog.BaseUnits),0) Fee,appointment.ClinicNum,";
if (ReportsComplex.RunFuncOnReportServer(() => Prefs.GetBoolNoCache(PrefName.ReportPandIschedProdSubtractsWO)))
{
//Subtract both PPO and capitation writeoffs
command += "SUM(IFNULL(CASE WHEN WriteOffEstOverride != -1 THEN WriteOffEstOverride ELSE WriteOffEst END,0)) WriteoffEstimate ";
}
else
{
//Always subtract CapEstimate writeoffs from scheduled production. This is so that the scheduled production will match actual production
//when the procedures are set complete. Nathan decided this 01/05/2017.
command += "SUM(IFNULL((CASE WHEN claimproc.Status=" + POut.Int((int)ClaimProcStatus.Estimate) + " THEN 0 "
+ "WHEN WriteOffEstOverride != -1 THEN WriteOffEstOverride ELSE WriteOffEst END),0)) WriteoffEstimate ";
}
command += "FROM appointment "
+ "LEFT JOIN procedurelog ON appointment.AptNum = procedurelog.AptNum AND procedurelog.ProcStatus=" + POut.Int((int)ProcStat.TP) + " "
+ "LEFT JOIN claimproc ON procedurelog.ProcNum = claimproc.ProcNum "
+ "AND claimproc.Status IN(" + POut.Int((int)ClaimProcStatus.Estimate) + "," + POut.Int((int)ClaimProcStatus.CapEstimate) + ") "
+ " AND (WriteOffEst != -1 OR WriteOffEstOverride != -1) "
+ "WHERE appointment.AptStatus = " + POut.Int((int)ApptStatus.Scheduled) + " "
+ "AND " + DbHelper.DtimeToDate("appointment.AptDateTime") + " >= " + POut.Date(dateFrom) + " "
+ "AND " + DbHelper.DtimeToDate("appointment.AptDateTime") + " <= " + POut.Date(dateTo) + " "
+ whereProv
+ whereClin
+ " GROUP BY procedurelog.ProcNum) t " //without this, there can be duplicate proc rows due to the claimproc join with dual insurance.
+ "GROUP BY SchedDate,ClinicNum "
+ "ORDER BY SchedDate";
if (isCEMT)
{
tableSched = Db.GetTable(command);
}
else
{
tableSched = ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(command));
}
tableSched.TableName = "tableSched";
#endregion
#region TableProdGoal
//1. Find all schedules for the month
//2. ClinicNum will come from the schedule's operatory
//3. Fetch HourlyProdGoalAmt from provider on the schedule
//4. Sum scheduled hours, grouped by prov and clinic
//5. Multiply the scheduled hours by the provider's HourlyProdGoalAmt
DataTable tableProdGoal = new DataTable();
if (!hasAllProvs && listProvNums.Count > 0)
{
whereProv = "AND schedule.ProvNum IN (" + string.Join(",", listProvNums) + ") ";
}
if (!hasAllClinics && listClinicNums.Count > 0)
{
whereClin = "AND COALESCE(operatory.ClinicNum,0) IN (" + string.Join(",", listClinicNums) + ") ";
}
//Fetch all schedules for the month and associated information (clinic from operatory, HourlyProdGoalAmt from provider)
command = "SELECT " + DbHelper.DtimeToDate("schedule.SchedDate") + @" AS SchedDate, schedule.StartTime AS StartTime, schedule.StopTime AS StopTime,
COALESCE(operatory.ClinicNum,0) AS ClinicNum, provider.HourlyProdGoalAmt AS ProvProdGoal, provider.ProvNum AS ProvNum
FROM schedule
INNER JOIN provider ON provider.ProvNum=schedule.ProvNum
LEFT JOIN scheduleop ON scheduleop.ScheduleNum=schedule.ScheduleNum
LEFT JOIN operatory ON scheduleop.OperatoryNum=operatory.OperatoryNum
WHERE schedule.SchedType=" + POut.Int((int)ScheduleType.Provider) + " "
+ "AND schedule.Status=" + POut.Int((int)SchedStatus.Open) + " "
+ "AND schedule." + DbHelper.BetweenDates("SchedDate", dateFrom, dateTo) + " "
+ whereProv
+ whereClin;
if (isCEMT)
{
tableProdGoal = Db.GetTable(command);
}
else
{
tableProdGoal = ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(command));
}
tableProdGoal.TableName = "tableProdGoal";
#endregion
#region WriteOffAdjustments
DataTable tableWriteOffAdjustments = new DataTable();
if (!hasAllProvs && listProvNums.Count > 0)
{
whereProv = "AND claimproc.ProvNum IN (" + string.Join(",", listProvNums) + ") ";
}
if (!hasAllClinics && listClinicNums.Count > 0)
{
whereClin = "AND claimproc.ClinicNum IN (" + string.Join(",", listClinicNums) + ") ";
}
if (writeoffPayType == PPOWriteoffDateCalc.ClaimPayDate)
{
//Insurance WriteOff Adjustments----------------------------------------------------------------------------
command = $@"SELECT claimproc.DateCP Date,claimproc.ClinicNum,
-SUM(CASE WHEN COALESCE(claimsnapshot.WriteOff,-1)=-1 THEN 0 ELSE claimsnapshot.WriteOff END) WriteOffEst,
SUM(claimproc.WriteOff) WriteOff,
claimproc.ClaimNum
FROM claimproc
LEFT JOIN claimsnapshot ON claimsnapshot.ClaimProcNum=claimproc.ClaimProcNum
WHERE claimproc.DateCP BETWEEN {POut.Date(dateFrom)} AND {POut.Date(dateTo)}
AND claimproc.Status IN ({(int)ClaimProcStatus.Received},{(int)ClaimProcStatus.Supplemental})
{whereProv}
{whereClin}
GROUP BY ClinicNum,DATE(claimproc.DateCP)" ;
if (isCEMT)
{
tableWriteOffAdjustments = Db.GetTable(command);
}
else
{
tableWriteOffAdjustments = ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(command));
}
}
tableWriteOffAdjustments.TableName = "tableWriteOffAdjustments";
#endregion WriteOffAdjustments
DataSet dataSet = new DataSet();
dataSet.Tables.Add(tableProduction);
dataSet.Tables.Add(tableAdj);
dataSet.Tables.Add(tableInsWriteoff);
dataSet.Tables.Add(tableWriteOffAdjustments);
dataSet.Tables.Add(tableSched);
dataSet.Tables.Add(tableProdGoal);
return(dataSet);
}
public static DataTable GetClaimsNotSent(DateTime fromDate, DateTime toDate, List <long> listClinicNums
, bool hasClaimTypeExpanded, ClaimNotSentStatuses claimStatusFilter)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), fromDate, toDate, listClinicNums
, hasClaimTypeExpanded, claimStatusFilter));
}
bool hasClinicsEnabled = ReportsComplex.RunFuncOnReportServer(() => !Prefs.GetBoolNoCache(PrefName.EasyNoClinics));
string command = "";
string whereClin = "";
string claimFilter = "";
if (hasClinicsEnabled && listClinicNums.Count > 0) //construct the IN statement for all of the selected clinics
{
whereClin += " AND claim.ClinicNum IN(" + string.Join(",", listClinicNums) + ")";
}
if (hasClinicsEnabled)
{
command = "SELECT clinic.Abbr AS 'Clinic',";
}
else
{
command = "SELECT ";
}
if (hasClaimTypeExpanded)
{
command += "claim.DateService,(CASE WHEN claim.ClaimType='P' THEN 'Primary' WHEN claim.ClaimType='S' THEN 'Secondary' "
+ "WHEN claim.ClaimType='Cap' THEN 'Capitation' ELSE claim.ClaimType END) AS ClaimType,";
}
else
{
command += "claim.DateService,claim.ClaimType,";
}
//Claim statuses of Unsent, Hold until pri, and Waiting are considered for "All" in this report.
string claimStatusAll = "AND claim.ClaimStatus IN ('U','H','W')";
switch (claimStatusFilter)
{
case ClaimNotSentStatuses.Primary:
claimFilter = "AND claim.ClaimType='P' " + claimStatusAll;
break;
case ClaimNotSentStatuses.Secondary:
claimFilter = "AND claim.ClaimType='S' " + claimStatusAll;
break;
case ClaimNotSentStatuses.Holding:
claimFilter = "AND claim.ClaimStatus='H'";
break;
default: //All
claimFilter += claimStatusAll;
break;
}
string clinJoin = "";
if (hasClinicsEnabled)
{
clinJoin = " LEFT JOIN clinic ON clinic.ClinicNum=claim.ClinicNum";
}
command += "(CASE WHEN claim.ClaimStatus='U' THEN 'Unsent' WHEN "
+ "claim.ClaimStatus='H' THEN 'Hold' WHEN claim.ClaimStatus='W' THEN 'WaitQ' ELSE claim.ClaimStatus END) AS ClaimStatus, "
+ "CONCAT(CONCAT(CONCAT(CONCAT(patient.LName,', '),patient.FName),' '),patient.MiddleI) as 'Patient Name',carrier.CarrierName"
+ ",claim.ClaimFee,GROUP_CONCAT(procedurecode.ProcCode SEPARATOR ', ') ProcCodes,claim.ClaimNum,claim.PatNum "
+ "FROM patient"
+ " INNER JOIN claim ON claim.PatNum=patient.PatNum"
+ " INNER JOIN claimproc ON claimproc.ClaimNum=claim.ClaimNum"
+ " INNER JOIN procedurelog ON procedurelog.ProcNum=claimproc.ProcNum"
+ " INNER JOIN procedurecode ON procedurecode.CodeNum=procedurelog.CodeNum"
+ " INNER JOIN insplan ON insplan.PlanNum=claim.PlanNum"
+ " INNER JOIN carrier ON carrier.CarrierNum=insplan.CarrierNum"
+ clinJoin
+ " WHERE claim.DateService >= " + POut.Date(fromDate)
+ " AND claim.DateService <= " + POut.Date(toDate)
+ whereClin
+ claimFilter
+ " GROUP BY claim.ClaimNum";
if (DataConnection.DBtype == DatabaseType.Oracle)
{
command += ",claim.Status,patient.LName,patient.FName,patient.MiddleI,carrier.CarrierName,claim.ClaimFee,claim.ClaimNum,claim.PatNum";
}
command += " ORDER BY claim.DateService";
return(ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(command)));
}
///<summary>If not using clinics then supply an empty list of clinicNums.</summary>
public static DataSet GetPayPlanTable(DateTime dateStart, DateTime dateEnd, List <long> listProvNums, List <long> listClinicNums,
bool hasAllProvs, DisplayPayPlanType displayPayPlanType, bool hideCompletedPlans, bool showFamilyBalance, bool hasDateRange, bool isPayPlanV2)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetDS(MethodBase.GetCurrentMethod(), dateStart, dateEnd, listProvNums, listClinicNums, hasAllProvs, displayPayPlanType,
hideCompletedPlans, showFamilyBalance, hasDateRange, isPayPlanV2));
}
string whereProv = "";
if (!hasAllProvs)
{
whereProv += " AND payplancharge.ProvNum IN(";
for (int i = 0; i < listProvNums.Count; i++)
{
if (i > 0)
{
whereProv += ",";
}
whereProv += POut.Long(listProvNums[i]);
}
whereProv += ") ";
}
string whereClin = "";
bool hasClinicsEnabled = ReportsComplex.RunFuncOnReportServer(() => (!Prefs.GetBoolNoCache(PrefName.EasyNoClinics)));
if (hasClinicsEnabled) //Using clinics
{
whereClin += " AND payplancharge.ClinicNum IN(";
for (int i = 0; i < listClinicNums.Count; i++)
{
if (i > 0)
{
whereClin += ",";
}
whereClin += POut.Long(listClinicNums[i]);
}
whereClin += ") ";
}
DataSet ds = new DataSet();
DataTable table = new DataTable("Clinic");
table.Columns.Add("provider");
table.Columns.Add("guarantor");
table.Columns.Add("ins");
table.Columns.Add("princ");
table.Columns.Add("accumInt");
table.Columns.Add("paid");
table.Columns.Add("balance");
table.Columns.Add("due");
if (isPayPlanV2)
{
table.Columns.Add("notDue");
}
table.Columns.Add("famBal");
table.Columns.Add("clinicName");
DataTable tableTotals = new DataTable("Total");
tableTotals.Columns.Add("clinicName");
tableTotals.Columns.Add("princ");
tableTotals.Columns.Add("accumInt");
tableTotals.Columns.Add("paid");
tableTotals.Columns.Add("balance");
tableTotals.Columns.Add("due");
if (isPayPlanV2)
{
tableTotals.Columns.Add("notDue");
}
tableTotals.Columns.Add("famBal");
DataRow row;
string datesql = "CURDATE()"; //This is used to find out how much people owe currently and has nothing to do with the selected range
if (DataConnection.DBtype == DatabaseType.Oracle)
{
datesql = "(SELECT CURRENT_DATE FROM dual)";
}
//Oracle TODO: Either put entire query without GROUP BY in SUBSELECT and then GROUP BY outside, or rewrite query to use joins instead of subselects.
string command = "SELECT FName,LName,MiddleI,PlanNum,Preferred,PlanNum, "
+ "COALESCE((SELECT SUM(Principal+Interest) FROM payplancharge WHERE payplancharge.PayPlanNum=payplan.PayPlanNum "
+ "AND payplancharge.ChargeType=" + POut.Int((int)PayPlanChargeType.Debit) + " "//for v1, debits are the only ChargeType.
+ "AND ChargeDate <= " + datesql + @"),0) '_accumDue', ";
command += "COALESCE((SELECT SUM(Interest) FROM payplancharge WHERE payplancharge.PayPlanNum=payplan.PayPlanNum "
+ "AND payplancharge.ChargeType=" + POut.Int((int)PayPlanChargeType.Debit) + " " //for v1, debits are the only ChargeType.
+ "AND ChargeDate <= " + datesql + @"),0) '_accumInt', ";
command += "COALESCE((SELECT SUM(SplitAmt) FROM paysplit WHERE paysplit.PayPlanNum=payplan.PayPlanNum AND paysplit.PayPlanNum!=0),0) '_paid', ";
command += "COALESCE((SELECT SUM(InsPayAmt) FROM claimproc WHERE claimproc.PayPlanNum=payplan.PayPlanNum "
+ "AND claimproc.Status IN("
+ POut.Int((int)ClaimProcStatus.Received) + ","
+ POut.Int((int)ClaimProcStatus.Supplemental) + ","
+ POut.Int((int)ClaimProcStatus.CapClaim)
+ ") AND claimproc.PayPlanNum!=0),0) '_insPaid', ";
command += "COALESCE((SELECT SUM(Principal) FROM payplancharge WHERE payplancharge.PayPlanNum=payplan.PayPlanNum "
+ "AND payplancharge.ChargeType=" + POut.Int((int)PayPlanChargeType.Debit) + "),0) '_principal', " //for v1, debits are the only ChargeType.
+ "COALESCE((SELECT SUM(Principal) FROM payplancharge WHERE payplancharge.PayPlanNum=payplan.PayPlanNum "
+ "AND payplancharge.ChargeType=" + POut.Int((int)PayPlanChargeType.Credit) + "),0) '_credits', " //for v1, will always be 0.
+ "COALESCE((SELECT SUM(Principal) FROM payplancharge WHERE payplancharge.PayPlanNum=payplan.PayPlanNum "
+ "AND payplancharge.ChargeType=" + POut.Int((int)PayPlanChargeType.Credit) + " AND ChargeDate > " + datesql + "),0) '_notDue', "
+ "patient.PatNum PatNum, "
+ "payplancharge.ProvNum ProvNum ";
if (hasClinicsEnabled)
{
command += ", payplancharge.ClinicNum ClinicNum ";
}
//In order to determine if the patient has completely paid off their payment plan we need to get the total amount of interest as of today.
//Then, after the query has run, we'll add the interest up until today with the total principal for the entire payment plan.
//For this reason, we cannot use _accumDue which only gets the principle up until today and not the entire payment plan principle.
command += ",COALESCE((SELECT SUM(Interest) FROM payplancharge WHERE payplancharge.PayPlanNum=payplan.PayPlanNum "
+ "AND payplancharge.ChargeType=" + POut.Int((int)PayPlanChargeType.Debit) + " " //for v1, debits are the only ChargeType.
+ "AND ChargeDate <= " + datesql + @"),0) '_interest' "
+ "FROM payplan "
+ "LEFT JOIN patient ON patient.PatNum=payplan.Guarantor "
+ "LEFT JOIN payplancharge ON payplan.PayPlanNum=payplancharge.PayPlanNum "
+ "WHERE TRUE "; //Always include true, so that the WHERE clause may always be present.
if (hasDateRange)
{
command += "AND payplan.PayPlanDate >= " + POut.Date(dateStart) + " "
+ "AND payplan.PayPlanDate <= " + POut.Date(dateEnd) + " ";
}
command += whereProv
+ whereClin;
if (displayPayPlanType == DisplayPayPlanType.Insurance)
{
command += "AND payplan.PlanNum!=0 ";
}
else if (displayPayPlanType == DisplayPayPlanType.Patient)
{
command += "AND payplan.PlanNum=0 ";
}
else if (displayPayPlanType == DisplayPayPlanType.Both)
{
//Do not filter the query at all which will show both insurance and patient payment plan types.
}
if (hideCompletedPlans)
{
command += "AND payplan.IsClosed=0 ";
}
command += "GROUP BY FName,LName,MiddleI,Preferred,payplan.PayPlanNum ";
if (hasClinicsEnabled)
{
command += "ORDER BY ClinicNum,LName,FName";
}
else
{
command += "ORDER BY LName,FName";
}
DataTable raw = ReportsComplex.RunFuncOnReportServer(() => ReportsComplex.GetTable(command));
List <Provider> listProvs = ReportsComplex.RunFuncOnReportServer(() => Providers.GetAll());
//DateTime payplanDate;
Patient pat;
double princ;
double paid;
double interest;
double accumDue;
double notDue;
decimal famBal = 0;
double princTot = 0;
double paidTot = 0;
double interestTot = 0;
double balanceTot = 0;
double accumDueTot = 0;
double notDueTot = 0;
decimal famBalTot = 0;
string clinicDescOld = "";
for (int i = 0; i < raw.Rows.Count; i++)
{
princ = PIn.Double(raw.Rows[i]["_principal"].ToString());
interest = PIn.Double(raw.Rows[i]["_accumInt"].ToString());
if (raw.Rows[i]["PlanNum"].ToString() == "0") //pat payplan
{
paid = PIn.Double(raw.Rows[i]["_paid"].ToString());
}
else //ins payplan
{
paid = PIn.Double(raw.Rows[i]["_insPaid"].ToString());
}
accumDue = PIn.Double(raw.Rows[i]["_accumDue"].ToString());
notDue = PIn.Double(raw.Rows[i]["_notDue"].ToString());
row = table.NewRow();
//payplanDate=PIn.PDate(raw.Rows[i]["PayPlanDate"].ToString());
//row["date"]=raw.Rows[i]["PayPlanDate"].ToString();//payplanDate.ToShortDateString();
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["provider"] = Providers.GetLName(PIn.Long(raw.Rows[i]["ProvNum"].ToString()), listProvs);
row["guarantor"] = pat.GetNameLF();
if (raw.Rows[i]["PlanNum"].ToString() == "0")
{
row["ins"] = "";
}
else
{
row["ins"] = "X";
}
row["princ"] = princ.ToString("f");
row["accumInt"] = interest.ToString("f");
row["paid"] = paid.ToString("f");
row["balance"] = (princ + interest - paid).ToString("f");
row["due"] = (accumDue - paid).ToString("f");
if (isPayPlanV2)
{
row["notDue"] = ((princ + interest - paid) - (accumDue - paid)).ToString("f");
}
if (showFamilyBalance)
{
Family famCur = ReportsComplex.RunFuncOnReportServer(() => Patients.GetFamily(PIn.Long(raw.Rows[i]["PatNum"].ToString())));
famBal = (decimal)famCur.ListPats[0].BalTotal;
row["famBal"] = (famBal - (decimal)famCur.ListPats[0].InsEst).ToString("F");
}
if (hasClinicsEnabled) //Using clinics
{
List <Clinic> listClinics = ReportsComplex.RunFuncOnReportServer(() => Clinics.GetClinicsNoCache());
string clinicDesc = Clinics.GetDesc(PIn.Long(raw.Rows[i]["ClinicNum"].ToString()), listClinics);
clinicDesc = (clinicDesc == "")?Lans.g("FormRpPayPlans", "Unassigned"):clinicDesc;
if (!String.IsNullOrEmpty(clinicDescOld) && clinicDesc != clinicDescOld) //Reset all the total values
{
DataRow rowTot = tableTotals.NewRow();
rowTot["clinicName"] = clinicDescOld;
rowTot["princ"] = princTot.ToString();
rowTot["accumInt"] = interestTot.ToString();
rowTot["paid"] = paidTot.ToString();
rowTot["balance"] = balanceTot.ToString();
rowTot["due"] = accumDueTot.ToString();
if (isPayPlanV2)
{
rowTot["notDue"] = notDueTot.ToString();
}
rowTot["famBal"] = famBalTot.ToString();
tableTotals.Rows.Add(rowTot);
princTot = 0;
paidTot = 0;
interestTot = 0;
accumDueTot = 0;
balanceTot = 0;
notDueTot = 0;
famBalTot = 0;
}
row["clinicName"] = clinicDesc;
clinicDescOld = clinicDesc;
princTot += princ;
paidTot += paid;
interestTot += interest;
accumDueTot += (accumDue - paid);
balanceTot += (princ + interest - paid);
notDueTot += ((princ + interest - paid) - (accumDue - paid));
famBalTot += famBal;
if (i == raw.Rows.Count - 1)
{
DataRow rowTot = tableTotals.NewRow();
rowTot["clinicName"] = clinicDescOld;
rowTot["princ"] = princTot.ToString();
rowTot["accumInt"] = interestTot.ToString();
rowTot["paid"] = paidTot.ToString();
rowTot["balance"] = balanceTot.ToString();
rowTot["due"] = accumDueTot.ToString();
if (isPayPlanV2)
{
rowTot["notDue"] = notDueTot.ToString();
}
rowTot["famBal"] = famBalTot.ToString();
tableTotals.Rows.Add(rowTot);
}
}
table.Rows.Add(row);
}
ds.Tables.Add(table);
ds.Tables.Add(tableTotals);
return(ds);
}
public static string GetQueryString(RpAgingParamObject rpo)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetString(MethodBase.GetCurrentMethod(), rpo));
}
//patient aging---------------------------------------------------------------------------
//The aging report always shows historical numbers based on the date entered.
//the selected columns have to remain in this order due to the way the report complex populates the returned sheet
string queryAg = "SELECT ";
if (rpo.IsForInsAging) //get patNum for insAgingReport only
{
queryAg += "patient.PatNum, ";
}
if (ReportsComplex.RunFuncOnReportServer(() => (Prefs.GetBoolNoCache(PrefName.ReportsShowPatNum))))
{
queryAg += DbHelper.Concat("patient.PatNum", "' - '", "patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI");
}
else
{
queryAg += DbHelper.Concat("patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI");
}
queryAg += " patName,guarAging.Bal_0_30,guarAging.Bal_31_60,guarAging.Bal_61_90,guarAging.BalOver90,guarAging.BalTotal,"
+ "guarAging.InsWoEst,guarAging.InsPayEst,guarAging.BalTotal-guarAging.InsPayEst-guarAging.InsWoEst AS ";
if (DataConnection.DBtype == DatabaseType.MySql)
{
queryAg += "$pat";
}
else //Oracle needs quotes.
{
queryAg += "\"$pat\"";
}
//Must select "blankCol" for use with reportComplex to fix spacing of final column
queryAg += (rpo.HasDateLastPay ? ",'' blankCol,guarAging.DateLastPay " : " ")
+ "FROM ("
+ ReportsComplex.RunFuncOnReportServer(() => Ledgers.GetAgingQueryString(asOfDate: rpo.AsOfDate, isHistoric: rpo.IsHistoric,
isInsPayWoCombined: rpo.IsInsPayWoCombined, hasDateLastPay: rpo.HasDateLastPay, isGroupByGuar: rpo.IsGroupByFam, isWoAged: rpo.IsWoAged,
isForceAgeNegAdj: rpo.IsForceAgeNegAdj, doAgePatPayPlanPayments: rpo.DoAgePatPayPlanPayments))
+ ") guarAging "
+ "INNER JOIN patient ON patient.PatNum=guarAging.PatNum ";
List <string> listWhereAnds = new List <string>();
//InsAging will filter for age, but we need to return all in here order for the filtering to be correct
if (!rpo.IsForInsAging)
{
List <string> listAgeOrs = new List <string>();
if (rpo.IsIncludeNeg || rpo.IsOnlyNeg)
{
listAgeOrs.Add("guarAging.BalTotal <= -0.005");
}
if (rpo.IsIncludeInsNoBal || rpo.IsOnlyInsNoBal)
{
listAgeOrs.Add("((ABS(guarAging.InsPayEst) >= 0.005 OR ABS(guarAging.InsWoEst) >= 0.005) "
+ "AND guarAging.Bal_0_30 < 0.005 AND guarAging.Bal_31_60 < 0.005 AND guarAging.Bal_61_90 < 0.005 AND guarAging.BalOver90 < 0.005)");
}
if (!rpo.IsOnlyNeg && !rpo.IsOnlyInsNoBal)
{
listAgeOrs.Add("guarAging.BalOver90 >= 0.005"); //applies to all ages
if (rpo.AccountAge <= AgeOfAccount.Over60)
{
listAgeOrs.Add("guarAging.Bal_61_90 >= 0.005");
}
if (rpo.AccountAge <= AgeOfAccount.Over30)
{
listAgeOrs.Add("guarAging.Bal_31_60 >= 0.005");
}
if (rpo.AccountAge == AgeOfAccount.Any) //only applies to Any age
{
listAgeOrs.Add("guarAging.Bal_0_30 >= 0.005");
}
}
listWhereAnds.Add("(" + string.Join(" OR ", listAgeOrs) + ")");
}
if (rpo.IsExcludeInactive)
{
listWhereAnds.Add("patient.PatStatus != " + (int)PatientStatus.Inactive);
}
if (rpo.IsExcludeArchive)
{
listWhereAnds.Add("patient.PatStatus != " + (int)PatientStatus.Archived);
}
if (rpo.IsExcludeBadAddress)
{
listWhereAnds.Add("patient.Zip != ''");
}
if (rpo.ListBillTypes.Count > 0) //if all bill types is selected, list will be empty
{
listWhereAnds.Add("patient.BillingType IN (" + string.Join(",", rpo.ListBillTypes.Select(x => POut.Long(x))) + ")");
}
if (rpo.ListProvNums.Count > 0) //if all provs is selected, list will be empty
{
listWhereAnds.Add("patient.PriProv IN (" + string.Join(",", rpo.ListProvNums.Select(x => POut.Long(x))) + ")");
}
if (ReportsComplex.RunFuncOnReportServer(() => (!Prefs.GetBoolNoCache(PrefName.EasyNoClinics)))) //if clinics enabled, at least one clinic will be selected
//listClin may contain "Unassigned" clinic with ClinicNum 0, in which case it will also be in the query string
{
listWhereAnds.Add("patient.ClinicNum IN (" + string.Join(",", rpo.ListClinicNums.Select(x => POut.Long(x))) + ")");
}
if (listWhereAnds.Count > 0)
{
queryAg += "WHERE " + string.Join(" AND ", listWhereAnds) + " ";
}
queryAg += "ORDER BY patient.LName,patient.FName";
return(queryAg);
}
public static string GetQueryString(DateTime asOfDate, bool isWoAged, bool hasDateLastPay, bool isGroupByFam, bool isOnlyNeg, AgeOfAccount accountAge,
bool isIncludeNeg, bool isExcludeInactive, bool isExcludeBadAddress, List <long> listProv, List <long> listClinicNums, List <long> listBillType,
bool isExcludeArchive, bool isIncludeInsNoBal, bool isOnlyInsNoBal, bool?isForceAgeNegAdj, bool isForInsAging, bool doAgePatPayPlanPayments)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetString(MethodBase.GetCurrentMethod(), asOfDate, isWoAged, hasDateLastPay, isGroupByFam, isOnlyNeg, accountAge, isIncludeNeg,
isExcludeInactive, isExcludeBadAddress, listProv, listClinicNums, listBillType, isExcludeArchive, isIncludeInsNoBal, isOnlyInsNoBal,
isForceAgeNegAdj, isForInsAging, doAgePatPayPlanPayments));
}
//patient aging---------------------------------------------------------------------------
//The aging report always shows historical numbers based on the date entered.
//the selected columns have to remain in this order due to the way the report complex populates the returned sheet
string queryAg = "SELECT ";
if (isForInsAging) //get patNum for insAgingReport only
{
queryAg += "patient.PatNum, ";
}
if (ReportsComplex.RunFuncOnReportServer(() => (Prefs.GetBoolNoCache(PrefName.ReportsShowPatNum))))
{
queryAg += DbHelper.Concat("patient.PatNum", "' - '", "patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI");
}
else
{
queryAg += DbHelper.Concat("patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI");
}
queryAg += "patName,guarAging.Bal_0_30,guarAging.Bal_31_60,guarAging.Bal_61_90,guarAging.BalOver90,guarAging.BalTotal,"
+ "guarAging.InsWoEst,guarAging.InsPayEst,guarAging.BalTotal-guarAging.InsPayEst-guarAging.InsWoEst AS ";
if (DataConnection.DBtype == DatabaseType.MySql)
{
queryAg += "$pat";
}
else //Oracle needs quotes.
{
queryAg += "\"$pat\"";
}
bool isHistoric = (asOfDate.Date != DateTime.Today);
bool isInsPayWoCombined = false;
//Must select "blankCol" for use with reportComplex to fix spacing of final column
queryAg += (hasDateLastPay ? ",'' blankCol,guarAging.DateLastPay " : " ")
+ "FROM ("
+ ReportsComplex.RunFuncOnReportServer(() => Ledgers.GetAgingQueryString(asOfDate, null, isHistoric, isInsPayWoCombined, hasDateLastPay,
isGroupByFam, isWoAged, isForceAgeNegAdj, doAgePatPayPlanPayments))
+ ") guarAging "
+ "INNER JOIN patient ON patient.PatNum=guarAging.PatNum "
+ "WHERE ";
List <string> listAgeOrs = new List <string>();
if (isIncludeNeg || isOnlyNeg)
{
listAgeOrs.Add("guarAging.BalTotal < -0.005");
}
if (isIncludeInsNoBal || isOnlyInsNoBal)
{
listAgeOrs.Add("((guarAging.InsPayEst > 0.005 OR guarAging.InsWoEst > 0.005) AND guarAging.Bal_0_30 < 0.005 AND guarAging.Bal_31_60 < 0.005 "
+ "AND guarAging.Bal_61_90 < 0.005 AND guarAging.BalOver90 < 0.005)");
}
if (!isOnlyNeg && !isOnlyInsNoBal)
{
if (accountAge <= AgeOfAccount.Over90)
{
listAgeOrs.Add("guarAging.BalOver90 > 0.005");
}
if (accountAge <= AgeOfAccount.Over60)
{
listAgeOrs.Add("guarAging.Bal_61_90 > 0.005");
}
if (accountAge <= AgeOfAccount.Over30)
{
listAgeOrs.Add("guarAging.Bal_31_60 > 0.005");
}
if (accountAge <= AgeOfAccount.Any)
{
listAgeOrs.Add("guarAging.Bal_0_30 > 0.005");
}
}
queryAg += "(" + string.Join(" OR ", listAgeOrs) + ") ";
if (isExcludeInactive)
{
queryAg += "AND patient.PatStatus != " + (int)PatientStatus.Inactive + " ";
}
if (isExcludeArchive)
{
queryAg += "AND patient.PatStatus != " + (int)PatientStatus.Archived + " ";
}
if (isExcludeBadAddress)
{
queryAg += "AND patient.Zip != '' ";
}
if (listBillType.Count > 0) //if all bill types is selected, list will be empty
{
queryAg += "AND patient.BillingType IN (" + string.Join(",", listBillType.Select(x => POut.Long(x))) + ") ";
}
if (listProv.Count > 0) //if all provs is selected, list will be empty
{
queryAg += "AND patient.PriProv IN (" + string.Join(",", listProv.Select(x => POut.Long(x))) + ") ";
}
if (ReportsComplex.RunFuncOnReportServer(() => (!Prefs.GetBoolNoCache(PrefName.EasyNoClinics)))) //validated to have at least one clinic selected if clinics are enabled above
//listClin may contain "Unassigned" clinic with ClinicNum 0, in which case it will also be in the query string
{
queryAg += "AND patient.ClinicNum IN (" + string.Join(",", listClinicNums.Select(x => POut.Long(x))) + ") ";
}
queryAg += "ORDER BY patient.LName,patient.FName";
return(queryAg);
}
public static DataTable GetInsAgingTable(DateTime asOfDate, bool isGroupByFam, AgeOfAccount accountAge,
bool hasBillTypesAll, bool hasProvAll, List <long> listProv, List <long> listClinicNums, List <long> listBillType)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), asOfDate, isGroupByFam,
accountAge, hasBillTypesAll, hasProvAll, listProv, listClinicNums, listBillType));
}
//ins aging---------------------------------------------------------------------------
if (asOfDate.Year < 1880)
{
asOfDate = DateTime.Today;
}
string asOfDateStr = POut.Date(asOfDate);
string thirtyDaysAgo = POut.Date(asOfDate.AddDays(-30));
string sixtyDaysAgo = POut.Date(asOfDate.AddDays(-60));
string ninetyDaysAgo = POut.Date(asOfDate.AddDays(-90));
string command = "SELECT patient.PatNum, ";
if (ReportsComplex.RunFuncOnReportServer(() => (Prefs.GetBoolNoCache(PrefName.ReportsShowPatNum))))
{
command += DbHelper.Concat("patient.PatNum", "' - '", "patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI");
}
else
{
command += DbHelper.Concat("patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI");
}
command += @"patName,
guarAging.InsEst0_30 AS InsBal_0_30,
guarAging.InsEst31_60 AS InsBal_31_60,
guarAging.InsEst61_90 AS InsBal_61_90,
guarAging.InsEst90 AS InsBal_90,
guarAging.InsPayEst AS InsBalTotal
FROM (
SELECT tSums.PatNum," //if grouped by guar, this is the guar's PatNum; if grouped by patient.PatNum rows are individual pats
+ @"tSums.InsPayEst InsPayEst,
tSums.InsOver90 InsEst90,
tSums.Ins61_90 InsEst61_90,
tSums.Ins31_60 InsEst31_60,
tSums.Ins0_30 InsEst0_30
FROM(" ;
command += "SELECT " + (isGroupByFam?"p.Guarantor PatNum,":"trans.PatNum,");
command += "SUM(CASE WHEN trans.TranDate <= " + asOfDateStr + @" AND trans.InsPayEst != 0 THEN trans.InsPayEst ELSE 0 END) InsPayEst,
SUM(CASE WHEN trans.TranDate < " + ninetyDaysAgo + @" THEN IFNULL(trans.InsPayEst,0) ELSE 0 END) InsOver90,
SUM(CASE WHEN trans.TranDate < " + sixtyDaysAgo + @" AND trans.TranDate >= " + ninetyDaysAgo + @" THEN IFNULL(trans.InsPayEst,0) ELSE 0 END) Ins61_90,
SUM(CASE WHEN trans.TranDate < " + thirtyDaysAgo + @" AND trans.TranDate >= " + sixtyDaysAgo + @" THEN IFNULL(trans.InsPayEst,0) ELSE 0 END) Ins31_60,
SUM(CASE WHEN trans.TranDate <= " + asOfDateStr + @" AND trans.TranDate >= " + thirtyDaysAgo + @" THEN IFNULL(trans.InsPayEst,0) ELSE 0 END) Ins0_30
FROM(" ;
#region Regular Claimproc By DateCP
command += @"
SELECT cp.ProcDate TranDate,
(CASE WHEN cp.ProcDate <= " + asOfDateStr + @"
AND (cp.Status = " + (int)ClaimProcStatus.NotReceived + @" OR (cp.Status = " + (int)ClaimProcStatus.Received + " AND cp.DateCP > " + asOfDateStr + @"))
THEN cp.InsPayEst ELSE 0 END) InsPayEst,
cp.PatNum
FROM claimproc cp
WHERE cp.status IN (" + (int)ClaimProcStatus.NotReceived + "," + (int)ClaimProcStatus.Received + ") ";
#endregion Regular Claimproc By DateCP
command += ") trans ";
if (isGroupByFam)
{
command += "INNER JOIN patient p ON p.PatNum=trans.PatNum "
+ "GROUP BY p.Guarantor";
if (!PrefC.GetBool(PrefName.AgingIsEnterprise)) //only if for one fam or if not using famaging table
{
command += " ORDER BY NULL";
}
}
else
{
command += "GROUP BY trans.PatNum";
}
command += ") tSums"
+ ") guarAging "
+ "INNER JOIN patient ON patient.PatNum=guarAging.PatNum ";
command += "WHERE TRUE ";
if (listBillType.Count > 0) //if all bill types is selected, list will be empty
{
command += "AND patient.BillingType IN (" + string.Join(",", listBillType.Select(x => POut.Long(x))) + ") ";
}
if (listProv.Count > 0) //if all provs is selected, list will be empty
{
command += "AND patient.PriProv IN (" + string.Join(",", listProv.Select(x => POut.Long(x))) + ") ";
}
if (listClinicNums.Count > 0)
{
//listClin may contain "Unassigned" clinic with ClinicNum 0, in which case it will also be in the query string
command += "AND patient.ClinicNum IN (" + string.Join(",", listClinicNums.Select(x => POut.Long(x))) + ") ";
}
command += "AND (guarAging.InsEst0_30 > 0.005 OR guarAging.InsEst31_60 > 0.005 OR guarAging.InsEst61_90 > 0.005 OR guarAging.InsEst90 > 0.005 "
+ "OR guarAging.InsPayEst > 0.005) ";
command += "ORDER BY patient.LName,patient.FName";
DataTable insTable = ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(command));
//-- regular aging table --------------------------------------------------
bool isOnlyNeg = false;
bool isWoAged = true;
bool isIncludeNeg = true;
bool hasDateLastPay = false;
bool isExcludeInactive = false;
bool isExcludeBadAddress = false;
bool isExcludeArchived = false;
bool isOnlyInsNoBal = false;
bool isIncludeInsNoBal = false;
bool? isForceAgeNegAdj = null;
bool isForInsAging = true;
bool doAgePatPayPlanPayments = false;
DataTable regAging = ReportsComplex.RunFuncOnReportServer(() => RpAging.GetAgingTable(asOfDate, isWoAged, hasDateLastPay, isGroupByFam, isOnlyNeg,
AgeOfAccount.Any, isIncludeNeg, isExcludeInactive, isExcludeBadAddress, listProv, listClinicNums, listBillType, isExcludeArchived, isIncludeInsNoBal,
isOnlyInsNoBal, isForceAgeNegAdj, isForInsAging, doAgePatPayPlanPayments));
//------------ Combine Tables ---------------------------------------------
DataTable insAgingTable = new DataTable();
//define columns here
insAgingTable.Columns.Add("PatName");
insAgingTable.Columns.Add("InsBal_0_30");
insAgingTable.Columns.Add("InsBal_31_60");
insAgingTable.Columns.Add("InsBal_61_90");
insAgingTable.Columns.Add("InsBal_90");
insAgingTable.Columns.Add("InsBal_Total");
insAgingTable.Columns.Add("PatBal_0_30");
insAgingTable.Columns.Add("PatBal_31_60");
insAgingTable.Columns.Add("PatBal_61_90");
insAgingTable.Columns.Add("PatBal_90");
insAgingTable.Columns.Add("PatBal_Total");
insAgingTable.Columns.Add("PatNum"); //this will not show, for correlating the two (ins and pat) aging tables to each other.
List <DataRow> listInsAgingRows = new List <DataRow>();
//loop through the insurance aging table
foreach (DataRow insRow in insTable.Rows)
{
//create a new row with the structure of the new table
DataRow newRow = insAgingTable.NewRow();
//copy the insurance aging table's values over to the new row. (also fill the patient bal columns with -(insurance estimate) for the appropriate bucket.)
newRow["PatNum"] = insRow["patNum"];
newRow["PatName"] = insRow["patName"];
newRow["InsBal_0_30"] = insRow["InsBal_0_30"];
newRow["InsBal_31_60"] = insRow["InsBal_31_60"];
newRow["InsBal_61_90"] = insRow["InsBal_61_90"];
newRow["InsBal_90"] = insRow["InsBal_90"];
newRow["InsBal_Total"] = insRow["InsBalTotal"];
newRow["PatBal_0_30"] = -PIn.Double(insRow["InsBal_0_30"].ToString());
newRow["PatBal_31_60"] = -PIn.Double(insRow["InsBal_31_60"].ToString());
newRow["PatBal_61_90"] = -PIn.Double(insRow["InsBal_61_90"].ToString());
newRow["PatBal_90"] = -PIn.Double(insRow["InsBal_90"].ToString());
newRow["PatBal_Total"] = -PIn.Double(insRow["InsBalTotal"].ToString());
listInsAgingRows.Add(newRow);
}
//-------------------------------------------------------------------------------------------------------------------------------------
//loop through rows in the regular aging table
foreach (DataRow row in regAging.Rows)
{
DataRow insAgingRow = listInsAgingRows.FirstOrDefault(x => x["PatNum"].ToString() == row["PatNum"].ToString());
//check to see if that patient exists in the insurance aging report
if (insAgingRow != null)
{
insAgingRow["PatBal_0_30"] = PIn.Double(insAgingRow["PatBal_0_30"].ToString()) + PIn.Double(row["Bal_0_30"].ToString());
insAgingRow["PatBal_31_60"] = PIn.Double(insAgingRow["PatBal_31_60"].ToString()) + PIn.Double(row["Bal_31_60"].ToString());
insAgingRow["PatBal_61_90"] = PIn.Double(insAgingRow["PatBal_61_90"].ToString()) + PIn.Double(row["Bal_61_90"].ToString());
insAgingRow["PatBal_90"] = PIn.Double(insAgingRow["PatBal_90"].ToString()) + PIn.Double(row["BalOver90"].ToString());
insAgingRow["PatBal_Total"] = PIn.Double(insAgingRow["PatBal_Total"].ToString()) + PIn.Double(row["BalTotal"].ToString());
}
else //if it doesn't create a new row with 0.00 insurance values and fill the patient aging values.
{
DataRow newRow = insAgingTable.NewRow();
newRow["PatName"] = row["patName"];
newRow["InsBal_0_30"] = PIn.Double("0.00");
newRow["InsBal_31_60"] = PIn.Double("0.00");
newRow["InsBal_61_90"] = PIn.Double("0.00");
newRow["InsBal_90"] = PIn.Double("0.00");
newRow["InsBal_Total"] = PIn.Double("0.00");
newRow["PatBal_0_30"] = PIn.Double(row["Bal_0_30"].ToString());
newRow["PatBal_31_60"] = PIn.Double(row["Bal_31_60"].ToString());
newRow["PatBal_61_90"] = PIn.Double(row["Bal_61_90"].ToString());
newRow["PatBal_90"] = PIn.Double(row["BalOver90"].ToString());
newRow["PatBal_Total"] = PIn.Double(row["BalTotal"].ToString());
listInsAgingRows.Add(newRow);
}
}
listInsAgingRows = listInsAgingRows.OrderBy(x => x["PatName"]).ToList();
foreach (DataRow rowCur in listInsAgingRows)
{
if (accountAge == AgeOfAccount.Any)
{
insAgingTable.Rows.Add(rowCur);
}
else if (accountAge == AgeOfAccount.Over30)
{
if (PIn.Double(rowCur["PatBal_31_60"].ToString()) != 0 ||
PIn.Double(rowCur["InsBal_31_60"].ToString()) != 0 ||
PIn.Double(rowCur["PatBal_61_90"].ToString()) != 0 ||
PIn.Double(rowCur["InsBal_61_90"].ToString()) != 0 ||
PIn.Double(rowCur["PatBal_90"].ToString()) != 0 ||
PIn.Double(rowCur["InsBal_90"].ToString()) != 0)
{
insAgingTable.Rows.Add(rowCur);
}
}
else if (accountAge == AgeOfAccount.Over60)
{
if (PIn.Double(rowCur["PatBal_61_90"].ToString()) != 0 ||
PIn.Double(rowCur["InsBal_61_90"].ToString()) != 0 ||
PIn.Double(rowCur["PatBal_90"].ToString()) != 0 ||
PIn.Double(rowCur["InsBal_90"].ToString()) != 0)
{
insAgingTable.Rows.Add(rowCur);
}
}
else
{
if (PIn.Double(rowCur["PatBal_90"].ToString()) != 0 ||
PIn.Double(rowCur["InsBal_90"].ToString()) != 0)
{
insAgingTable.Rows.Add(rowCur);
}
}
}
return(insAgingTable);
}
///<summary></summary>
public static DataTable GetPatUncollected(DateTime dateFrom, DateTime dateTo, List <long> listClinicNums)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateFrom, dateTo, listClinicNums));
}
#if DEBUG
Stopwatch s = new Stopwatch();
s.Start();
#endif
bool hasClinicsEnabled = ReportsComplex.RunFuncOnReportServer(() => (!Prefs.GetBoolNoCache(PrefName.EasyNoClinics)));
string query = "SELECT procedurelog.ProcDate,CONCAT(patient.LName,', ',patient.FName)AS Patient, procedurecode.AbbrDesc, "
+ "procedurelog.ProcFee*(procedurelog.BaseUnits+procedurelog.UnitQty)-IFNULL(cp.CapWriteOff,0) Fee,"
+ "IFNULL((procedurelog.ProcFee*(procedurelog.BaseUnits+procedurelog.UnitQty))-IFNULL(cp.CapWriteOff,0)-IFNULL(cp.InsAmt,0)"
+ "-IFNULL(cp.WriteOff,0),0) AS PatPortion,"
+ "IFNULL(adj.AdjAmt,0) AS Adjustment,"
+ "IFNULL(pay.SplitAmt,0) AS Payment,"
+ "IFNULL((procedurelog.ProcFee*(procedurelog.BaseUnits+procedurelog.UnitQty))-IFNULL(cp.CapWriteOff,0)-IFNULL(cp.InsAmt,0)-"
+ "IFNULL(cp.WriteOff,0),0)+IFNULL(adj.AdjAmt,0)-IFNULL(pay.SplitAmt,0) Uncollected "
+ "FROM procedurelog "
+ "INNER JOIN patient ON patient.PatNum=procedurelog.PatNum "
+ "INNER JOIN procedurecode ON procedurecode.CodeNum=procedurelog.CodeNum "
+ "LEFT JOIN ( "
+ "SELECT SUM(adjustment.AdjAmt) AdjAmt, adjustment.ProcNum "
+ "FROM adjustment "
+ "WHERE adjustment.ProcNum != 0 "
+ "AND adjustment.ProcDate BETWEEN " + POut.Date(dateFrom) + " AND " + POut.Date(dateTo) + " "
+ "GROUP BY ProcNum "
+ "ORDER BY NULL "
+ ")adj ON adj.ProcNum=procedurelog.ProcNum "
+ "LEFT JOIN ( "
+ "SELECT claimproc.Status, SUM(CASE WHEN claimproc.Status = " + POut.Int((int)ClaimProcStatus.NotReceived) + " THEN claimproc.InsPayEst "
+ "WHEN claimproc.Status IN(" + POut.Int((int)ClaimProcStatus.Received) + "," + POut.Int((int)ClaimProcStatus.Supplemental) + ") "
+ "THEN claimproc.InsPayAmt "
+ "ELSE 0 END) AS InsAmt, "
+ "SUM(IF(claimproc.Status!=" + POut.Int((int)ClaimProcStatus.CapComplete) + ",claimproc.WriteOff,0)) AS WriteOff, "
+ "SUM(IF(claimproc.Status=" + POut.Int((int)ClaimProcStatus.CapComplete) + ",claimproc.WriteOff,0)) AS CapWriteOff, "
+ "claimproc.ProcNum "
+ "FROM claimproc "
+ "WHERE claimproc.Status IN(" + POut.Int((int)ClaimProcStatus.NotReceived) + ","
+ POut.Int((int)ClaimProcStatus.Received) + ","
+ POut.Int((int)ClaimProcStatus.Supplemental) + ","
+ POut.Int((int)ClaimProcStatus.CapComplete) + ") "
+ "AND claimproc.ProcDate BETWEEN " + POut.Date(dateFrom) + " AND " + POut.Date(dateTo) + " "
+ "GROUP BY claimproc.ProcNum "
+ "ORDER BY NULL "
+ ")cp ON cp.ProcNum=procedurelog.ProcNum "
+ "LEFT JOIN ( "
+ "SELECT SUM(paysplit.SplitAmt) SplitAmt, paysplit.ProcNum "
+ "FROM paysplit "
+ "INNER JOIN procedurelog "
+ "ON paysplit.ProcNum=procedurelog.ProcNum "
+ "AND procedurelog.ProcStatus=2 " //Complete
+ "AND procedurelog.ProcDate BETWEEN " + POut.Date(dateFrom) + " AND " + POut.Date(dateTo) + " "
+ "WHERE paysplit.ProcNum != 0 "
+ "GROUP BY paysplit.ProcNum "
+ "ORDER BY NULL "
+ ")pay ON pay.ProcNum=procedurelog.ProcNum "
+ "WHERE procedurelog.ProcStatus=2 "
+ "AND procedurelog.ProcDate BETWEEN " + POut.Date(dateFrom) + " AND " + POut.Date(dateTo) + " "
+ "AND IFNULL((procedurelog.ProcFee*(procedurelog.BaseUnits+procedurelog.UnitQty))-IFNULL(cp.CapWriteOff,0)-IFNULL(cp.InsAmt,0)-"
+ "IFNULL(cp.WriteOff,0),0)+IFNULL(adj.AdjAmt,0)-IFNULL(pay.SplitAmt,0) > 0.005";
if (hasClinicsEnabled && listClinicNums.Count > 0)
{
query += "AND procedurelog.ClinicNum IN(" + string.Join(",", listClinicNums.Select(x => POut.Long(x))) + ") ";
}
query += "ORDER BY procedurelog.ProcDate,patient.LName,patient.FName,procedurecode.ProcCode";
DataTable table = ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(query));
#if DEBUG
s.Stop();
Console.WriteLine("Total time to generate report with " + string.Format("{0:#,##0.##}", table.Rows.Count) + " rows: "
+ (s.Elapsed.Hours > 0?(s.Elapsed.Hours + " hours "):"") + (s.Elapsed.Minutes > 0?(s.Elapsed.Minutes + " min "):"")
+ (s.Elapsed.TotalSeconds - (s.Elapsed.Hours * 60 * 60) - (s.Elapsed.Minutes * 60)) + " sec");
#endif
return(table);
}