///<summary>Gets the XWeb transactions for approved transactions. To get for all clinics, pass in a list of empty clinicNums.</summary>
public static DataTable GetApprovedTransactions(List <long> listClinicNums, DateTime dateFrom, DateTime dateTo)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), listClinicNums, dateFrom, dateTo));
}
string command = "SELECT " + DbHelper.Concat("patient.LName", "', '", "patient.FName") + " Patient,xwebresponse.DateTUpdate,xwebresponse.TransactionID,"
+ "xwebresponse.MaskedAcctNum,xwebresponse.ExpDate,xwebresponse.Amount,xwebresponse.PaymentNum,xwebresponse.TransactionStatus,"
+ "(CASE WHEN payment.PayNum IS NULL THEN 0 ELSE 1 END) doesPaymentExist,COALESCE(clinic.Abbr,'Unassigned') Clinic,xwebresponse.PatNum, "
+ "xwebresponse.XWebResponseNum,xwebresponse.Alias "
+ "FROM xwebresponse "
+ "INNER JOIN patient ON patient.PatNum=xwebresponse.PatNum "
+ "LEFT JOIN payment ON payment.PayNum=xwebresponse.PaymentNum "
+ "LEFT JOIN clinic ON clinic.ClinicNum=xwebresponse.ClinicNum "
+ "WHERE xwebresponse.TransactionStatus IN("
+ POut.Int((int)XWebTransactionStatus.DtgPaymentApproved) + ","
+ POut.Int((int)XWebTransactionStatus.HpfCompletePaymentApproved) + ","
+ POut.Int((int)XWebTransactionStatus.HpfCompletePaymentApprovedPartial) + ","
+ POut.Int((int)XWebTransactionStatus.DtgPaymentReturned) + ","
+ POut.Int((int)XWebTransactionStatus.DtgPaymentVoided) + ") "
+ "AND xwebresponse.ResponseCode IN("
+ POut.Int((int)XWebResponseCodes.Approval) + ","
+ POut.Int((int)XWebResponseCodes.PartialApproval) + ") "
+ "AND xwebresponse.DateTUpdate BETWEEN " + POut.DateT(dateFrom) + " AND " + POut.DateT(dateTo.AddDays(1)) + " ";
if (listClinicNums.Count > 0)
{
command += "AND xwebresponse.ClinicNum IN (" + string.Join(",", listClinicNums.Select(x => POut.Long(x))) + ") ";
}
command += "ORDER BY xwebresponse.DateTUpdate,patient.LName,patient.FName ";
return(Db.GetTable(command));
}
private static string ByApptStatusQuery(bool hasClinicsEnabled, DateTime dateStart, DateTime dateEnd, string whereProv, string whereClin)
{
string queryBrokenApts = "SELECT " + DbHelper.DateTFormatColumn("appointment.AptDateTime", "%m/%d/%Y %H:%i:%s") + " AptDateTime, "
+ "" + DbHelper.Concat("patient.LName", "', '", "patient.FName") + " Patient,doctor.Abbr Doctor,hygienist.Abbr Hygienist, "
+ "appointment.IsHygiene IsHygieneApt ";
if (hasClinicsEnabled)
{
queryBrokenApts += ",COALESCE(clinic.Description,'" + POut.String(Lans.g("FormRpBrokenAppointments", "Unassigned")) + "') ClinicDesc "; //Coalesce is Oracle compatible
}
queryBrokenApts +=
"FROM appointment "
+ "INNER JOIN patient ON appointment.PatNum=patient.PatNum "
+ "LEFT JOIN provider doctor ON doctor.ProvNum=appointment.ProvNum "
+ "LEFT JOIN provider hygienist ON hygienist.ProvNum=appointment.ProvHyg ";
if (hasClinicsEnabled)
{
queryBrokenApts += "LEFT JOIN clinic ON clinic.ClinicNum=appointment.ClinicNum ";
}
queryBrokenApts +=
"WHERE " + DbHelper.DtimeToDate("appointment.AptDateTime") + " BETWEEN " + POut.Date(dateStart)
+ " AND " + POut.Date(dateEnd) + " "
+ "AND appointment.AptStatus=" + POut.Int((int)ApptStatus.Broken) + " "
+ whereProv;
if (hasClinicsEnabled)
{
queryBrokenApts += whereClin + " "
+ "ORDER BY clinic.Description,appointment.AptDateTime,patient.LName,patient.FName";
}
else
{
queryBrokenApts += "ORDER BY appointment.AptDateTime,patient.LName,patient.FName ";
}
return(queryBrokenApts);
}
public static DataTable GetFinanceChargeTable(DateTime dateStart, DateTime dateEnd, long finChargeAdjType, List <long> listProvNums, List <long> listBillingDefNums)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateStart, dateEnd, finChargeAdjType, listProvNums, listBillingDefNums));
}
string query = "SELECT patient.PatNum," + DbHelper.Concat("patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI") + " PatName,patient.Preferred"
+ ",adjustment.AdjAmt "
+ "FROM patient "
+ "INNER JOIN adjustment ON patient.PatNum=adjustment.PatNum "
+ "AND adjustment.AdjDate BETWEEN " + POut.Date(dateStart) + " AND " + POut.Date(dateEnd) + " "
+ "AND adjustment.AdjType = " + POut.Long(finChargeAdjType) + " ";
if (listProvNums.Count > 0)
{
query += "AND patient.PriProv IN (" + string.Join(",", listProvNums.Select(x => POut.Long(x))) + ") ";
}
if (listBillingDefNums.Count > 0)
{
query += "AND patient.BillingType IN (" + string.Join(",", listBillingDefNums.Select(x => POut.Long(x))) + ") ";
}
query += "ORDER BY patient.LName,patient.FName,AdjAmt DESC";
DataTable table = ReportsComplex.RunFuncOnReportServer(() => ReportsComplex.GetTable(query));
return(table);
}
public static DataTable GetNewPatients(DateTime dateFrom, DateTime dateTo, List <long> listProvNums, bool includeAddress, bool excludeNoProd, bool hasAllProvs)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateFrom, dateTo, listProvNums, includeAddress, excludeNoProd, hasAllProvs));
}
string query = @"SET @pos=0;
SELECT @pos:=@pos+1 patCount, result.* FROM (SELECT dateFirstProc,patient.LName,patient.FName,"
+ DbHelper.Concat("referral.LName", "IF(referral.FName='','',', ')", "referral.FName") + " refname,"
+ "SUM(procedurelog.ProcFee*(procedurelog.UnitQty+procedurelog.BaseUnits)) ";
if (DataConnection.DBtype == DatabaseType.MySql)
{
query += "$HowMuch";
}
else //Oracle needs quotes.
{
query += "\"$HowMuch\"";
}
if (includeAddress)
{
query += ",patient.Preferred,patient.Address,patient.Address2,patient.City,patient.State,patient.Zip";
}
query += " FROM "
+ "(SELECT PatNum, MIN(ProcDate) dateFirstProc FROM procedurelog "
+ "INNER JOIN procedurecode ON procedurecode.CodeNum=procedurelog.CodeNum "
+ "AND ProcCode NOT IN ('D9986','D9987') " /*Do not count missed or canceled appointments*/
+ "WHERE ProcStatus=" + POut.Int((int)ProcStat.C) + " GROUP BY PatNum "
+ "HAVING dateFirstProc BETWEEN " + POut.Date(dateFrom) + " AND " + POut.Date(dateTo) + ") table1 "
+ "INNER JOIN patient ON table1.PatNum=patient.PatNum "
+ "LEFT JOIN procedurelog ON patient.PatNum=procedurelog.PatNum AND procedurelog.ProcStatus=" + POut.Int((int)ProcStat.C) + " "
+ "LEFT JOIN refattach ON patient.PatNum=refattach.PatNum AND refattach.RefType=" + POut.Int((int)ReferralType.RefFrom) + " "
+ "AND refattach.ItemOrder=("
+ "SELECT MIN(ra.ItemOrder) FROM refattach ra WHERE ra.PatNum=refattach.PatNum AND ra.RefType=" + POut.Int((int)ReferralType.RefFrom)
+ ") "
+ "LEFT JOIN referral ON referral.ReferralNum=refattach.ReferralNum ";
if (!hasAllProvs)
{
query += "WHERE patient.PriProv IN (" + String.Join(",", listProvNums) + ") ";
}
query += "GROUP BY patient.LName,patient.FName,patient.PatNum," + DbHelper.Concat("referral.LName", "IF(referral.FName='','',',')", "referral.FName");
if (includeAddress)
{
query += ",patient.Preferred,patient.Address,patient.Address2,patient.City,patient.State,patient.Zip";
}
if (excludeNoProd)
{
if (DataConnection.DBtype == DatabaseType.MySql)
{
query += " HAVING $HowMuch > 0";
}
else //Oracle needs quotes.
{
query += " HAVING \"$HowMuch\" > 0";
}
}
query += " ORDER BY dateFirstProc,patient.LName,patient.FName) result";
return(ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(query)));
}
/// <summary>Retrieves the Line Item Unearned dataset from the database.</summary>
/// <param name="listClinics">The list of clinics to filter by. Pass in an empty list if this should not be filtered by clinic.</param>
/// <returns></returns>
public static DataTable GetLineItemUnearnedData(List <long> listClinics, DateTime date1Start, DateTime date2Start, bool showProvider)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), listClinics, date1Start, date2Start, showProvider));
}
bool hasClinicsEnabled = ReportsComplex.RunFuncOnReportServer(() => Prefs.HasClinicsEnabledNoCache);
List <long> listHiddenUnearnedDefNums = ReportsComplex.RunFuncOnReportServer(() =>
Defs.GetDefsNoCache(DefCat.PaySplitUnearnedType).FindAll(x => !string.IsNullOrEmpty(x.ItemValue)).Select(x => x.DefNum).ToList()
);
string command = "";
string whereClin = "";
//This query is kind-of a mess, but we're trying to account for bugs in previous versions.
//Logic is meant to match the account module "Unearned" value logic as closely as possible.
if (listClinics.Count > 0)
{
whereClin = "AND paysplit.ClinicNum IN (" + string.Join(",", listClinics) + ") ";
}
//Outer Select
command = "SELECT results.DatePay," + DbHelper.Concat("patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI") + " Patient,"
+ "definition.ItemName,";
if (hasClinicsEnabled)
{
command += "clinic.Abbr Clinic,";
}
if (showProvider)
{
command += "provider.Abbr,";
}
command += "results.SplitAmt FROM (";
command += "SELECT SplitNum,DatePay,PatNum,UnearnedType,ClinicNum,SplitAmt,ProvNum FROM paysplit "
+ "WHERE paysplit.DatePay >= " + POut.Date(date1Start) + " "
+ "AND paysplit.DatePay <= " + POut.Date(date2Start) + " ";
if (listHiddenUnearnedDefNums.Count > 0)
{
command += $"AND paysplit.UnearnedType NOT IN ({string.Join(",",listHiddenUnearnedDefNums)}) ";
}
command += whereClin
+ "AND UnearnedType!=0 ";
//is to also show unearned that is now allocated.
command += ") results "
+ "INNER JOIN patient ON patient.PatNum=results.PatNum "
+ "LEFT JOIN definition ON definition.DefNum=results.UnearnedType ";
if (showProvider)
{
command += "LEFT JOIN provider on provider.ProvNum=results.ProvNum ";
}
if (hasClinicsEnabled)
{
command += "LEFT JOIN clinic ON clinic.ClinicNum=results.ClinicNum ";
}
command += "ORDER BY results.DatePay,Patient,results.SplitNum";
DataTable raw = ReportsComplex.RunFuncOnReportServer(() => ReportsComplex.GetTable(command));
return(raw);
}
///<summary>If not using clinics then supply an empty list of clinicNums. listClinicNums must have at least one item if using clinics. Not formatted for display</summary>
public static DataTable GetIndividualTable(DateTime dateFrom, DateTime dateTo, List <long> listProvNums, List <long> listClinicNums, string procCode,
bool isAnyClinicMedical, bool hasAllProvs, bool hasClinicsEnabled)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateFrom, dateTo, listProvNums, listClinicNums, procCode, isAnyClinicMedical, hasAllProvs,
hasClinicsEnabled));
}
string query = "SELECT procedurelog.ProcDate,"
+ DbHelper.Concat("patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI") + " "
+ "AS plfname, procedurecode.ProcCode,";
if (!isAnyClinicMedical)
{
query += "procedurelog.ToothNum,";
}
query += "procedurecode.Descript,provider.Abbr,";
if (hasClinicsEnabled)
{
query += "COALESCE(clinic.Description,\"Unassigned\") Clinic,";
}
query += "procedurelog.ProcFee*(procedurelog.UnitQty+procedurelog.BaseUnits)"
+ "-COALESCE(SUM(claimproc.WriteOff),0) "; //\"$fee\" " //if no writeoff, then subtract 0
if (DataConnection.DBtype == DatabaseType.MySql)
{
query += "$fee ";
}
else //Oracle needs quotes.
{
query += "\"$fee\" ";
}
query += "FROM patient "
+ "INNER JOIN procedurelog ON procedurelog.PatNum=patient.PatNum "
+ "INNER JOIN procedurecode ON procedurecode.CodeNum=procedurelog.CodeNum "
+ "INNER JOIN provider ON provider.ProvNum=procedurelog.ProvNum ";
if (hasClinicsEnabled)
{
query += "LEFT JOIN clinic ON clinic.ClinicNum=procedurelog.ClinicNum ";
}
query += "LEFT JOIN claimproc ON procedurelog.ProcNum=claimproc.ProcNum "
+ "AND claimproc.Status=" + POut.Int((int)ClaimProcStatus.CapComplete) + " " //only CapComplete writeoffs are subtracted here.
+ "WHERE procedurelog.ProcStatus=" + POut.Int((int)ProcStat.C) + " ";
if (!hasAllProvs)
{
query += "AND procedurelog.ProvNum IN (" + String.Join(",", listProvNums) + ") ";
}
if (hasClinicsEnabled)
{
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 "
+ "ORDER BY procedurelog.ProcDate,plfname,procedurecode.ProcCode,ToothNum";
return(ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(query)));
}
/// <summary>Retrieves the Unearned Accounts dataset from the database.</summary>
/// <param name="listClinics">The list of clinics to filter by. Pass in an empty list if this should not be filtered by clinic.</param>
/// <returns></returns>
public static DataTable GetUnearnedAccountData(List <long> listClinics)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), listClinics));
}
bool hasClinicsEnabled = ReportsComplex.RunFuncOnReportServer(() => Prefs.HasClinicsEnabledNoCache);
List <long> listHiddenUnearnedDefNums = ReportsComplex.RunFuncOnReportServer(() =>
Defs.GetDefsNoCache(DefCat.PaySplitUnearnedType).FindAll(x => !string.IsNullOrEmpty(x.ItemValue)).Select(x => x.DefNum).ToList()
);
string command = "";
string whereClin = "";
//We used to get original paysplits based on FSplitNum being 0 and having an unearned type and then get the offsetting splits from the
//FSplitNum matching the original paysplit num.
//FSplitNums no longer are important when calculating unearned so they are no included in this logic intentionally.
//The patient table joins are quite slow for large customers, which is why they were moved outside the FROM.
//If a customer complains we might do some logic to get the patnums of any family member in that clinic first, so we can filter down the unions.
if (listClinics.Count > 0)
{
whereClin = "WHERE guar.ClinicNum IN (" + string.Join(",", listClinics) + ") ";
}
command = "SELECT " + DbHelper.Concat("guar.LName", "', '", "guar.FName", "' '", "guar.MiddleI") + ",";
command += DbHelper.GroupConcat("definition.ItemName", true, true, ",");
if (hasClinicsEnabled)
{
command += ",clinic.Abbr";
}
command += ",SUM(splits.Amt) Amount FROM (";
//Prepay. Unearned is simply defined as having an unearned type set. Nothing more.
command += "SELECT paysplit.PatNum, paysplit.SplitAmt Amt,paysplit.UnearnedType "
+ "FROM paysplit "
+ "WHERE paysplit.UnearnedType!=0 ";
if (listHiddenUnearnedDefNums.Count > 0)
{
command += $"AND paysplit.UnearnedType NOT IN ({string.Join(",",listHiddenUnearnedDefNums)}) ";
}
command += "GROUP BY paysplit.SplitNum";
command += ") splits "
+ "INNER JOIN patient ON patient.PatNum=splits.PatNum "
+ "INNER JOIN patient guar ON guar.PatNum=patient.Guarantor "
+ "LEFT JOIN definition ON definition.DefNum=splits.UnearnedType ";
if (hasClinicsEnabled)
{
command += "LEFT JOIN clinic ON clinic.ClinicNum=guar.ClinicNum ";
}
command += whereClin;
command += "GROUP BY guar.PatNum HAVING ABS(Amount) > 0.005 "; //still won't work for oracle
command += "ORDER BY guar.LName, guar.FName, guar.MiddleI, Amount";
DataTable raw = ReportsComplex.RunFuncOnReportServer(() => ReportsComplex.GetTable(command));
return(raw);
}
/// <summary>Retrieves the Unearned Accounts dataset from the database.</summary>
/// <param name="listClinics">The list of clinics to filter by. Pass in an empty list if this should not be filtered by clinic.</param>
/// <returns></returns>
public static DataTable GetUnearnedAccountData(List <long> listClinics)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), listClinics));
}
bool hasClinicsEnabled = ReportsComplex.RunFuncOnReportServer(() => (!Prefs.GetBoolNoCache(PrefName.EasyNoClinics)));
string command = "";
string whereClin = "";
//This query is kind-of a mess, but we're trying to account for bugs in previous versions.
//Logic is meant to match the account module "Unearned" value logic as closely as possible.
//Cameron and I tried to optimize this code for speed as much as we could in mysql, but filtering by clinic won't make this query much faster.
//The patient table joins are quite slow for large customers, which is why they were moved outside the FROM.
//If a customer complains we might do some logic to get the patnums of any family member in that clinic first, so we can filter down the unions.
if (listClinics.Count > 0)
{
whereClin = "WHERE guar.ClinicNum IN (" + string.Join(",", listClinics) + ") ";
}
command = "SELECT " + DbHelper.Concat("guar.LName", "', '", "guar.FName", "' '", "guar.MiddleI") + ",";
command += DbHelper.GroupConcat("definition.ItemName", true, true, ",");
if (hasClinicsEnabled)
{
command += ",clinic.Description";
}
command += ",SUM(splits.Amt) Amount FROM (";
//Prepay
command += "SELECT paysplit.PatNum, paysplit.SplitAmt Amt,paysplit.UnearnedType "
+ "FROM paysplit "
+ "WHERE paysplit.UnearnedType!=0 AND paysplit.FSplitNum=0 ";
//UNION ALL
command += "UNION ALL ";
//Negative Split
command += "SELECT paysplit.PatNum, SUM(paysplit.SplitAmt) Amt,NULL UnearnedType " //UnearnedType should match prepayment, might be 0
+ "FROM paysplit "
+ "WHERE paysplit.FSplitNum!=0 "
+ "AND paysplit.FSplitNum IN (SELECT paysplit.SplitNum FROM paysplit WHERE paysplit.UnearnedType!=0 AND paysplit.FSplitNum=0) "
+ "GROUP BY paysplit.FSplitNum";
command += ") splits "
+ "INNER JOIN patient ON patient.PatNum=splits.PatNum "
+ "INNER JOIN patient guar ON guar.PatNum=patient.Guarantor "
+ "LEFT JOIN definition ON definition.DefNum=splits.UnearnedType ";
if (hasClinicsEnabled)
{
command += "LEFT JOIN clinic ON clinic.ClinicNum=guar.ClinicNum ";
}
command += whereClin;
command += "GROUP BY guar.PatNum HAVING ABS(Amount) > 0.005 "; //still won't work for oracle
command += "ORDER BY guar.LName, guar.FName, guar.MiddleI, Amount";
DataTable raw = ReportsComplex.RunFuncOnReportServer(() => ReportsComplex.GetTable(command));
return(raw);
}
///<summary>Gets the XWeb transactions for approved transactions. To get for all clinics, pass in a list of empty clinicNums.</summary>
public static DataTable GetApprovedTransactions(List <long> listClinicNums, DateTime dateFrom, DateTime dateTo)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), listClinicNums, dateFrom, dateTo));
}
string command = "SELECT " + DbHelper.Concat("patient.LName", "', '", "patient.FName") + " Patient,xwebresponse.DateTUpdate,xwebresponse.TransactionID,"
+ "xwebresponse.MaskedAcctNum,DATE_FORMAT(xwebresponse.AccountExpirationDate,'%m/%y') ExpDate,xwebresponse.Amount,xwebresponse.PaymentNum,xwebresponse.TransactionStatus,"
+ "(CASE WHEN payment.PayNum IS NULL THEN 0 ELSE 1 END) doesPaymentExist,COALESCE(clinic.Abbr,'Unassigned') Clinic,xwebresponse.PatNum, "
+ "xwebresponse.XWebResponseNum AS 'ResponseNum',xwebresponse.Alias,1 AS 'isXWeb' "
+ "FROM xwebresponse "
+ "INNER JOIN patient ON patient.PatNum=xwebresponse.PatNum "
+ "LEFT JOIN payment ON payment.PayNum=xwebresponse.PaymentNum "
+ "LEFT JOIN clinic ON clinic.ClinicNum=xwebresponse.ClinicNum "
+ "WHERE xwebresponse.TransactionStatus IN("
+ POut.Int((int)XWebTransactionStatus.DtgPaymentApproved) + ","
+ POut.Int((int)XWebTransactionStatus.HpfCompletePaymentApproved) + ","
+ POut.Int((int)XWebTransactionStatus.HpfCompletePaymentApprovedPartial) + ","
+ POut.Int((int)XWebTransactionStatus.DtgPaymentReturned) + ","
+ POut.Int((int)XWebTransactionStatus.DtgPaymentVoided) + ","
+ POut.Int((int)XWebTransactionStatus.EdgeExpressCompletePaymentApproved) + ","
+ POut.Int((int)XWebTransactionStatus.EdgeExpressCompletePaymentApprovedPartial) + ") "
+ "AND xwebresponse.ResponseCode IN("
+ POut.Int((int)XWebResponseCodes.Approval) + ","
+ POut.Int((int)XWebResponseCodes.PartialApproval) + ") "
+ "AND xwebresponse.DateTUpdate BETWEEN " + POut.DateT(dateFrom) + " AND " + POut.DateT(dateTo.AddDays(1)) + " ";
if (listClinicNums.Count > 0)
{
command += "AND xwebresponse.ClinicNum IN (" + string.Join(",", listClinicNums.Select(x => POut.Long(x))) + ") ";
}
command += "UNION ALL ";
command += "SELECT " + DbHelper.Concat("patient.LName", "', '", "patient.FName") + " Patient,payconnectresponseweb.DateTimeCompleted,payconnectresponseweb.RefNumber,"
+ "(CASE WHEN creditcard.CCNumberMasked IS NULL THEN 'CC Not Saved' ELSE creditcard.CCNumberMasked END),"
+ "(CASE WHEN creditcard.CCExpiration IS NULL THEN '' ELSE DATE_FORMAT(creditcard.CCExpiration,'%m/%y') END),"
+ "payconnectresponseweb.Amount,payconnectresponseweb.PayNum,payconnectresponseweb.TransType,(CASE WHEN payment.PayNum IS NULL THEN 0 ELSE 1 END) doesPaymentExist,"
+ "COALESCE(clinic.Abbr,'Unassigned') Clinic,payconnectresponseweb.PatNum,payconnectresponseweb.PayConnectResponseWebNum,payconnectresponseweb.PayToken,0 AS 'isXWeb' "
+ "FROM payconnectresponseweb "
+ "INNER JOIN patient ON patient.PatNum=payconnectresponseweb.PatNum "
+ "LEFT JOIN creditcard ON creditcard.PayConnectToken=payconnectresponseweb.PaymentToken "
+ "LEFT JOIN payment ON payment.PayNum=payconnectresponseweb.PayNum "
+ "LEFT JOIN clinic ON clinic.ClinicNum=payment.ClinicNum "
+ "WHERE payconnectresponseweb.DateTimeCompleted BETWEEN " + POut.DateT(dateFrom) + " AND " + POut.DateT(dateTo.AddDays(1)) + " "
+ "AND payconnectresponseweb.ProcessingStatus='" + PayConnectWebStatus.Completed.ToString() + "' "
+ "AND payconnectresponseweb.TransType!='' ";
if (listClinicNums.Count > 0)
{
command += "AND payment.ClinicNum IN (" + string.Join(",", listClinicNums.Select(x => POut.Long(x))) + ") ";
}
command += "ORDER BY DateTUpdate,Patient;";
return(Db.GetTable(command));
}
/// <summary>Retrieves the Line Item Unearned dataset from the database.</summary>
/// <param name="listClinics">The list of clinics to filter by. Pass in an empty list if this should not be filtered by clinic.</param>
/// <returns></returns>
public static DataTable GetLineItemUnearnedData(List <long> listClinics, DateTime date1Start, DateTime date2Start)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), listClinics, date1Start, date2Start));
}
bool hasClinicsEnabled = ReportsComplex.RunFuncOnReportServer(() => (!Prefs.GetBoolNoCache(PrefName.EasyNoClinics)));
string command = "";
string whereClin = "";
//This query is kind-of a mess, but we're trying to account for bugs in previous versions.
//Logic is meant to match the account module "Unearned" value logic as closely as possible.
if (listClinics.Count > 0)
{
whereClin = "AND paysplit.ClinicNum IN (" + string.Join(",", listClinics) + ") ";
}
//Outer Select
command = "SELECT results.DatePay," + DbHelper.Concat("patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI") + " Patient,"
+ "definition.ItemName,";
if (hasClinicsEnabled)
{
command += "clinic.Description,";
}
command += "results.SplitAmt FROM (";
//Inner Select - Prepayments
command += "SELECT SplitNum,DatePay,PatNum,UnearnedType,ClinicNum,SplitAmt FROM paysplit "
+ "WHERE paysplit.DatePay >= " + POut.Date(date1Start) + " "
+ "AND paysplit.DatePay <= " + POut.Date(date2Start) + " "
+ whereClin
+ "AND UnearnedType!=0 AND FsplitNum=0 ";
//Inner Select - Union All
command += "UNION ALL ";
//Inner Select - Negative Split
command += "SELECT SplitNum,DatePay,PatNum,UnearnedType,ClinicNum,SplitAmt FROM paysplit "
+ "WHERE paysplit.DatePay >= " + POut.Date(date1Start) + " "
+ "AND paysplit.DatePay <= " + POut.Date(date2Start) + " "
+ whereClin
+ "AND paysplit.FSplitNum!=0 "
+ "AND paysplit.FSplitNum IN (SELECT SplitNum FROM paysplit WHERE UnearnedType!=0 AND FSplitNum=0)"; //Attached to a prepayment
//Back to Outer Select
command += ") results "
+ "INNER JOIN patient ON patient.PatNum=results.PatNum "
+ "LEFT JOIN definition ON definition.DefNum=results.UnearnedType ";
if (hasClinicsEnabled)
{
command += "LEFT JOIN clinic ON clinic.ClinicNum=results.ClinicNum ";
}
command += "ORDER BY results.DatePay,Patient,results.SplitNum";
DataTable raw = ReportsComplex.RunFuncOnReportServer(() => ReportsComplex.GetTable(command));
return(raw);
}
private static string ByProceduresQuery(bool hasClinicsEnabled, DateTime dateStart, DateTime dateEnd, string whereProv, string whereClin, BrokenApptProcedure brokenApptOption)
{
string queryBrokenApts = "SELECT procedurelog.ProcDate ProcDate,provider.Abbr Provider,";
if (brokenApptOption == BrokenApptProcedure.Both) //Show code when running for both.
{
queryBrokenApts += "procedurecode.ProcCode,";
}
queryBrokenApts += DbHelper.Concat("patient.LName", "', '", "patient.FName") + " Patient, "
+ "procedurelog.ProcFee ProcFee ";
if (hasClinicsEnabled)
{
queryBrokenApts += ",COALESCE(clinic.Description,'" + POut.String(Lans.g("FormRpBrokenAppointments", "Unassigned")) + "') ClinicDesc ";
}
queryBrokenApts +=
"FROM procedurelog ";
switch (brokenApptOption)
{
case BrokenApptProcedure.None: //Just in case.
case BrokenApptProcedure.Missed:
queryBrokenApts += "INNER JOIN procedurecode ON procedurecode.CodeNum=procedurelog.CodeNum AND procedurecode.ProcCode='D9986' ";
break;
case BrokenApptProcedure.Cancelled:
queryBrokenApts += "INNER JOIN procedurecode ON procedurecode.CodeNum=procedurelog.CodeNum AND procedurecode.ProcCode='D9987' ";
break;
case BrokenApptProcedure.Both:
queryBrokenApts += "INNER JOIN procedurecode ON procedurecode.CodeNum=procedurelog.CodeNum AND procedurecode.ProcCode IN ('D9986','D9987') ";
break;
}
queryBrokenApts += "INNER JOIN patient ON patient.PatNum=procedurelog.PatNum "
+ "INNER JOIN provider ON provider.ProvNum=procedurelog.ProvNum "
+ whereProv;
if (hasClinicsEnabled)
{
queryBrokenApts += "LEFT JOIN clinic ON clinic.ClinicNum=procedurelog.ClinicNum ";
}
queryBrokenApts += "WHERE procedurelog.ProcDate BETWEEN " + POut.Date(dateStart) + " AND " + POut.Date(dateEnd) + " "
+ "AND procedurelog.ProcStatus=" + POut.Int((int)ProcStat.C) + " ";
if (hasClinicsEnabled)
{
queryBrokenApts += whereClin + " "
+ "ORDER BY clinic.Description,procedurelog.ProcDate,patient.LName,patient.FName";
}
else
{
queryBrokenApts += "ORDER BY procedurelog.ProcDate,patient.LName,patient.FName";
}
return(queryBrokenApts);
}
///<summary>Gets a list of AsapComms (along with a few more fields) for use in the Web Sched History window. To view for all patients or clinics,
///pass in null for those parameters.</summary>
public static List <AsapCommHist> GetHist(DateTime dateFrom, DateTime dateTo, List <long> listPatNums = null, List <long> listClinicNums = null)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <AsapCommHist> >(MethodBase.GetCurrentMethod(), dateFrom, dateTo, listPatNums, listClinicNums));
}
string command = @"
SELECT asapcomm.*," + DbHelper.Concat("patient.LName", "', '", "patient.FName") + @" PatientName,COALESCE(schedule.StartTime,'00:00:00') StartTime,
COALESCE(schedule.StopTime,'00:00:00') StopTime,COALESCE(schedule.SchedDate,'0001-01-01') SchedDate,
COALESCE(emailmessage.BodyText,'') EmailMessageText,COALESCE(smstomobile.MsgText,'') SMSMessageText
FROM asapcomm
INNER JOIN patient ON patient.PatNum=asapcomm.PatNum
LEFT JOIN schedule ON schedule.ScheduleNum=asapcomm.ScheduleNum
LEFT JOIN emailmessage ON emailmessage.EmailMessageNum=asapcomm.EmailMessageNum
LEFT JOIN smstomobile ON smstomobile.GuidMessage=asapcomm.GuidMessageToMobile
WHERE " + DbHelper.BetweenDates("asapcomm.DateTimeEntry", dateFrom, dateTo) + " ";
if (listPatNums != null)
{
if (listPatNums.Count == 0)
{
return(new List <AsapCommHist>());
}
command += "AND asapcomm.PatNum IN(" + string.Join(",", listPatNums.Select(x => POut.Long(x))) + ") ";
}
if (listClinicNums != null)
{
if (listClinicNums.Count == 0)
{
return(new List <AsapCommHist>());
}
command += "AND asapcomm.ClinicNum IN(" + string.Join(",", listClinicNums.Select(x => POut.Long(x))) + ") ";
}
DataTable table = Db.GetTable(command);
List <AsapCommHist> listHists = Crud.AsapCommCrud.TableToList(table).Select(x => new AsapCommHist()
{
AsapComm = x
}).ToList();
for (int i = 0; i < listHists.Count; i++)
{
listHists[i].PatientName = PIn.String(table.Rows[i]["PatientName"].ToString());
listHists[i].DateTimeSlotStart = PIn.Date(table.Rows[i]["SchedDate"].ToString()).Add(PIn.Time(table.Rows[i]["StartTime"].ToString()));
listHists[i].DateTimeSlotEnd = PIn.Date(table.Rows[i]["SchedDate"].ToString()).Add(PIn.Time(table.Rows[i]["StopTime"].ToString()));
listHists[i].EmailMessageText = PIn.String(table.Rows[i]["EmailMessageText"].ToString());
listHists[i].SMSMessageText = PIn.String(table.Rows[i]["SMSMessageText"].ToString());
}
return(listHists);
}
///<summary>If not using clinics then supply an empty list of clinicNums.</summary>
public static DataTable GetAdjTable(DateTime dateStart, DateTime dateEnd, List <long> listProvNums, List <long> listClinicNums,
List <string> listAdjType, bool hasAllClinics, bool hasClinicsEnabled)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateStart, dateEnd, listProvNums, listClinicNums, listAdjType, hasAllClinics, hasClinicsEnabled));
}
string whereProv = "";
if (listProvNums.Count > 0)
{
whereProv += " AND adjustment.ProvNum IN(" + string.Join(",", listProvNums) + ") ";
}
string whereClin = "";
if (hasClinicsEnabled && listClinicNums.Count > 0) //Using clinics
{
whereClin += " AND adjustment.ClinicNum IN(" + string.Join(",", listClinicNums) + ") ";
}
string whereType = "";
if (listAdjType.Count > 0)
{
whereType = " AND adjustment.AdjType IN(" + string.Join(",", listAdjType) + ") ";
}
string query = "SELECT adjustment.AdjDate,"
+ DbHelper.Concat("patient.LName", "', '", "patient.FName", "', '", "patient.MiddleI") + ","
+ "provider.Abbr,";
if (hasClinicsEnabled)
{
query += "clinic.Abbr,";
}
query += "definition.ItemName,adjustment.AdjNote,adjustment.AdjAmt "
+ "FROM adjustment "
+ "INNER JOIN definition ON definition.DefNum=adjustment.AdjType "
+ "INNER JOIN patient ON patient.PatNum=adjustment.PatNum "
+ "LEFT JOIN provider ON provider.ProvNum=adjustment.ProvNum "
+ "LEFT JOIN clinic ON clinic.ClinicNum=adjustment.ClinicNum "
+ "WHERE adjustment.AdjDate >= " + POut.Date(dateStart) + " AND adjustment.AdjDate <= " + POut.Date(dateEnd) + " ";
query += whereProv;
if (hasClinicsEnabled)
{
query += whereClin;
}
query += whereType;
query += "ORDER BY adjustment.AdjDate";
return(ReportsComplex.RunFuncOnReportServer(() => ReportsComplex.GetTable(query)));
}
public static DataTable GetOverPaidProcs(long patNum, List <long> listProvNums, List <long> listClinics, DateTime dateStart, DateTime dateEnd)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), patNum, listProvNums, listClinics, dateStart, dateEnd));
}
List <long> listHiddenUnearnedDefNums = ReportsComplex.RunFuncOnReportServer(() =>
Defs.GetDefsNoCache(DefCat.PaySplitUnearnedType).FindAll(x => !string.IsNullOrEmpty(x.ItemValue)).Select(x => x.DefNum).ToList()
);
#region Completed Procs
string command = "SELECT ";
if (PrefC.GetBool(PrefName.ReportsShowPatNum))
{
command += DbHelper.Concat("CAST(patient.PatNum AS CHAR)", "'-'", "patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI");
}
else
{
command += DbHelper.Concat("patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI");
}
command += @" AS 'patientName',
procedurelog.ProcDate,
procedurecode.ProcCode,
procedurelog.ToothNum,
provider.Abbr,
(procedurelog.ProcFee*(procedurelog.UnitQty+procedurelog.BaseUnits)) AS fee,
patient.PatNum,
procedurelog.ProcNum
FROM procedurelog
INNER JOIN patient ON patient.PatNum=procedurelog.PatNum
INNER JOIN procedurecode ON procedurecode.CodeNum=procedurelog.CodeNum
INNER JOIN provider ON provider.ProvNum=procedurelog.ProvNum
WHERE procedurelog.ProcStatus=" + POut.Int((int)ProcStat.C) + " AND "
+ DbHelper.BetweenDates("procedurelog.ProcDate", dateStart, dateEnd) + " "
+ "AND procedurelog.ProcFee>0 ";
if (listProvNums != null && listProvNums.Count > 0)
{
command += "AND procedurelog.ProvNum IN (" + string.Join(",", listProvNums.Select(x => POut.Long(x))) + ") ";
}
if (listClinics != null && listClinics.Count > 0)
{
command += "AND procedurelog.ClinicNum IN (" + string.Join(",", listClinics.Select(x => POut.Long(x))) + ") ";
}
if (patNum > 0)
{
command += "AND procedurelog.PatNum=" + POut.Long(patNum) + " ";
}
command += "ORDER BY procedurelog.ProcDate,patientName,procedurecode.ProcCode,provider.Abbr";
DataTable rawCompletedProcTable = Db.GetTable(command);
Dictionary <long, DataRow> dictCompletedProcRows = rawCompletedProcTable.Select().ToDictionary(x => PIn.Long(x["ProcNum"].ToString()));
#endregion
DataTable table = new DataTable();
if (dictCompletedProcRows.Count == 0)
{
return(table);
}
#region ClaimProcs
List <long> listPatNums = rawCompletedProcTable.Select().Select(x => PIn.Long(x["PatNum"].ToString())).Distinct().ToList();
command = @"SELECT MIN(claimproc.ProcNum) ProcNum,MIN(claimproc.PatNum) PatNum,MIN(claimproc.ProcDate) ProcDate,SUM(claimproc.InsPayAmt) insPayAmt,
SUM(claimproc.Writeoff) writeoff
FROM claimproc
WHERE claimproc.Status NOT IN(" + string.Join(",", new List <int> {
(int)ClaimProcStatus.Preauth,
(int)ClaimProcStatus.CapEstimate, (int)ClaimProcStatus.CapComplete, (int)ClaimProcStatus.Estimate, (int)ClaimProcStatus.InsHist
}
.Select(x => POut.Int(x))) + ") "
+ "AND " + DbHelper.BetweenDates("claimproc.ProcDate", dateStart, dateEnd) + " "
+ "AND claimproc.PatNum IN(" + string.Join(",", listPatNums.Select(x => POut.Long(x))) + ") "
+ @"GROUP BY claimproc.ProcNum
HAVING SUM(claimproc.InsPayAmt+claimproc.Writeoff)>0
ORDER BY NULL" ;
Dictionary <long, DataRow> dictClaimProcRows = Db.GetTable(command).Select().ToDictionary(x => PIn.Long(x["ProcNum"].ToString()));
#endregion
#region Patient Payments
command = @"SELECT paysplit.ProcNum,SUM(paysplit.SplitAmt) ptAmt
FROM paysplit
WHERE paysplit.ProcNum>0
AND paysplit.PatNum IN(" + string.Join(",", listPatNums.Select(x => POut.Long(x))) + $@") ";
if (listHiddenUnearnedDefNums.Count > 0)
{
command += $"AND paysplit.UnearnedType NOT IN ({string.Join(",",listHiddenUnearnedDefNums)}) ";
}
command += @"
GROUP BY paysplit.ProcNum
ORDER BY NULL" ;
Dictionary <long, DataRow> dictPatPayRows = Db.GetTable(command).Select().ToDictionary(x => PIn.Long(x["ProcNum"].ToString()));
#endregion
#region Adjustments
command = @"SELECT adjustment.ProcNum,SUM(adjustment.AdjAmt) AdjAmt
FROM adjustment
WHERE adjustment.ProcNum>0
AND adjustment.PatNum IN(" + string.Join(",", listPatNums.Select(x => POut.Long(x))) + @")
GROUP BY adjustment.ProcNum
ORDER BY NULL" ;
Dictionary <long, DataRow> dictAdjRows = Db.GetTable(command).Select().ToDictionary(x => PIn.Long(x["ProcNum"].ToString()));
#endregion
//columns that start with lowercase are altered for display rather than being raw data.
table.Columns.Add("patientName");
table.Columns.Add("ProcDate", typeof(DateTime));
table.Columns.Add("ProcCode");
table.Columns.Add("ToothNum");
table.Columns.Add("Abbr");
table.Columns.Add("fee");
table.Columns.Add("insPaid");
table.Columns.Add("wo");
table.Columns.Add("ptPaid");
table.Columns.Add("adjAmt");
table.Columns.Add("overPay");
table.Columns.Add("PatNum");
DataRow row;
foreach (KeyValuePair <long, DataRow> kvp in dictCompletedProcRows)
{
long procNum = kvp.Key;
decimal procFeeAmt = PIn.Decimal(kvp.Value["fee"].ToString());
decimal insPaidAmt = 0;
decimal woAmt = 0;
decimal ptPaidAmt = 0;
decimal adjAmt = 0;
if (dictClaimProcRows.ContainsKey(procNum))
{
insPaidAmt = PIn.Decimal(dictClaimProcRows[procNum]["insPayAmt"].ToString());
woAmt = PIn.Decimal(dictClaimProcRows[procNum]["writeoff"].ToString());
}
if (dictPatPayRows.ContainsKey(procNum))
{
ptPaidAmt = PIn.Decimal(dictPatPayRows[procNum]["ptAmt"].ToString());
}
if (dictAdjRows.ContainsKey(procNum))
{
adjAmt = PIn.Decimal(dictAdjRows[procNum]["AdjAmt"].ToString());
}
decimal overPay = procFeeAmt - insPaidAmt - woAmt - ptPaidAmt + adjAmt;
if (!overPay.IsLessThanZero())
{
continue; //No overpayment. Not need to continue;
}
row = table.NewRow();
row["patientName"] = PIn.String(kvp.Value["patientName"].ToString());
row["ProcDate"] = PIn.Date(kvp.Value["ProcDate"].ToString());
row["ProcCode"] = PIn.String(kvp.Value["ProcCode"].ToString());
row["ToothNum"] = PIn.String(kvp.Value["ToothNum"].ToString());
row["Abbr"] = PIn.String(kvp.Value["Abbr"].ToString());;
row["fee"] = procFeeAmt.ToString();
row["insPaid"] = insPaidAmt.ToString();
row["wo"] = woAmt.ToString();
row["ptPaid"] = ptPaidAmt.ToString();
row["adjAmt"] = adjAmt.ToString();
row["overPay"] = overPay.ToString();
row["PatNum"] = PIn.Long(kvp.Value["PatNum"].ToString());
table.Rows.Add(row);
}
return(table);
}
///<summary>If not using clinics then supply an empty list of clinicNums.</summary>
public static DataTable GetWriteoffTable(DateTime dateStart, DateTime dateEnd, List <long> listProvNums, List <long> listClinicNums
, bool hasAllClinics, bool hasClinicsEnabled, bool hasWriteoffPay)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateStart, dateEnd, listProvNums, listClinicNums, hasAllClinics
, hasClinicsEnabled, hasWriteoffPay));
}
string whereProv = "";
if (listProvNums.Count > 0)
{
whereProv += " AND claimproc.ProvNum IN(" + string.Join(",", listProvNums) + ") ";
}
string whereClin = "";
if (hasClinicsEnabled && listClinicNums.Count > 0) //Using clinics
{
whereClin += " AND claimproc.ClinicNum IN(" + string.Join(",", listClinicNums) + ") ";
}
string query = "SET @FromDate=" + POut.Date(dateStart) + ", @ToDate=" + POut.Date(dateEnd) + ";";
if (hasWriteoffPay)
{
query += "SELECT " + DbHelper.DtimeToDate("claimproc.DateCP") + " date,"
+ DbHelper.Concat("patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI") + ","
+ "carrier.CarrierName,"
+ "provider.Abbr,";
if (hasClinicsEnabled)
{
query += "clinic.Description,";
}
if (DataConnection.DBtype == DatabaseType.MySql)
{
query += "SUM(claimproc.WriteOff) $amount,";
}
else //Oracle needs quotes.
{
query += "SUM(claimproc.WriteOff) \"$amount\",";
}
query += "claimproc.ClaimNum "
+ "FROM claimproc " //,insplan,patient,carrier,provider "
+ "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 clinic ON clinic.ClinicNum=claimproc.ClinicNum "
+ "WHERE (claimproc.Status=1 OR claimproc.Status=4) " /*received or supplemental*/
+ whereProv
+ whereClin
+ "AND claimproc.DateCP >= @FromDate "
+ "AND claimproc.DateCP <= @ToDate "
+ "AND claimproc.WriteOff > 0 "
+ "GROUP BY claimproc.ProvNum,claimproc.DateCP,claimproc.ClinicNum,claimproc.PatNum "
+ "ORDER BY claimproc.DateCP,claimproc.PatNum";
}
else //using procedure date
{
query += "SELECT " + DbHelper.DtimeToDate("claimproc.ProcDate") + " date, "
+ DbHelper.Concat("patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI") + ", "
+ "carrier.CarrierName, "
+ "provider.Abbr,";
if (hasClinicsEnabled)
{
query += "clinic.Description,";
}
if (DataConnection.DBtype == DatabaseType.MySql)
{
query += "SUM(claimproc.WriteOff) $amount ";
}
else //Oracle needs quotes.
{
query += "SUM(claimproc.WriteOff) \"$amount\" ";
}
query += "FROM claimproc " //,insplan,patient,carrier,provider "
+ "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 clinic ON clinic.ClinicNum=claimproc.ClinicNum "
+ "WHERE (claimproc.Status=1 OR claimproc.Status=4 OR claimproc.Status=0) " /*received or supplemental or notreceived*/
+ whereProv
+ whereClin
+ "AND claimproc.ProcDate >= @FromDate "
+ "AND claimproc.ProcDate <= @ToDate "
+ "AND claimproc.WriteOff > 0 "
+ "GROUP BY claimproc.ProvNum,claimproc.ProcDate,claimproc.ClinicNum,claimproc.PatNum "
+ "ORDER BY claimproc.ProcDate,claimproc.PatNum";
}
return(ReportsComplex.RunFuncOnReportServer(() => ReportsComplex.GetTable(query)));
}
///<summary>Called from FormRpOutstandingIns. Gets outstanding insurance claims. Requires all fields. provNumList may be empty (but will return null if isAllProv is false). listClinicNums may be empty. dateMin and dateMax will not be used if they are set to DateTime.MinValue() (01/01/0001). If isPreauth is true only claims of type preauth will be returned.</summary>
public static List <OutstandingInsClaim> GetOutInsClaims(List <long> listProvNums, DateTime dateFrom,
DateTime dateTo, bool isPreauth, List <long> listClinicNums, string carrierName, List <long> listUserNums, DateFilterBy dateFilterBy)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <OutstandingInsClaim> >(MethodBase.GetCurrentMethod(), listProvNums, dateFrom,
dateTo, isPreauth, listClinicNums, carrierName, listUserNums, dateFilterBy));
}
string command = @"
SELECT carrier.CarrierName,
carrier.Phone carrierPhone,
claim.ClaimType,
claim.DateService,
claim.DateSent,
claim.DateSentOrig DateOrigSent,
claim.ClaimFee,
claim.ClaimNum,
claim.ClinicNum,
insplan.GroupNum,
insplan.GroupName,
inssub.SubscriberID SubID,"
+ DbHelper.Concat("sub.LName", "', '", "sub.FName") + @" SubName,
sub.Birthdate SubDOB,
patient.FName PatFName,
patient.LName PatLName,
patient.MiddleI PatMiddleI,
patient.PatNum,
patient.Birthdate PatDOB,
definition.ItemValue DaysSuppressed,"
+ DbHelper.DtimeToDate("statusHistory.DateTimeEntry") + @" DateLog,
definition.DefNum CustomTrackingDefNum,
statusHistory.TrackingErrorDefNum ErrorCodeDefNum,
COALESCE(claimtracking.UserNum,0) UserNum
FROM carrier
INNER JOIN insplan ON insplan.CarrierNum=carrier.CarrierNum
INNER JOIN claim ON claim.PlanNum=insplan.PlanNum
AND claim.ClaimStatus='S' " ;
if (dateFrom != DateTime.MinValue)
{
if (dateFilterBy == DateFilterBy.DateSentOrig)
{
command += "AND claim.DateSentOrig >= " + POut.Date(dateFrom) + " ";
}
else if (dateFilterBy == DateFilterBy.DateSent)
{
command += "AND claim.DateSent >= " + POut.Date(dateFrom) + " ";
}
else
{
command += "AND claim.DateService >= " + POut.Date(dateFrom) + " ";
}
}
if (dateTo != DateTime.MinValue)
{
if (dateFilterBy == DateFilterBy.DateSentOrig)
{
command += "AND claim.DateSentOrig <= " + POut.Date(dateTo) + " ";
}
else if (dateFilterBy == DateFilterBy.DateSent)
{
command += "AND claim.DateSent <= " + POut.Date(dateTo) + " ";
}
else
{
command += "AND claim.DateService <= " + POut.Date(dateTo) + " ";
}
}
if (listProvNums.Count > 0)
{
command += "AND claim.ProvTreat IN (" + String.Join(",", listProvNums) + ") ";
}
if (listClinicNums.Count > 0)
{
command += "AND claim.ClinicNum IN (" + String.Join(",", listClinicNums) + ") ";
}
if (!isPreauth)
{
command += "AND claim.ClaimType!='Preauth' ";
}
command += "LEFT JOIN claimtracking ON claimtracking.ClaimNum=claim.ClaimNum AND TrackingType='" + POut.String(ClaimTrackingType.ClaimUser.ToString()) + "' ";
command += "LEFT JOIN definition ON definition.DefNum=claim.CustomTracking "
+ "LEFT JOIN claimtracking statusHistory ON statusHistory.ClaimNum=claim.ClaimNum "
+ "AND statusHistory.TrackingDefNum=definition.DefNum "
+ "AND statusHistory.DateTimeEntry=(SELECT MAX(ct.DateTimeEntry) FROM claimtracking ct WHERE ct.ClaimNum=claim.ClaimNum AND ct.TrackingDefNum!=0) "
+ "AND statusHistory.TrackingType='" + POut.String(ClaimTrackingType.StatusHistory.ToString()) + "' "
+ "INNER JOIN patient ON patient.PatNum=claim.PatNum "
+ "LEFT JOIN inssub ON claim.InsSubNum = inssub.InsSubNum "
+ "LEFT JOIN patient sub ON inssub.Subscriber = sub.PatNum "
+ "WHERE carrier.CarrierName LIKE '%" + POut.String(carrierName.Trim()) + "%' ";
if (listUserNums.Count > 0)
{
command += "AND (claimtracking.UserNum IN (" + String.Join(",", listUserNums) + ") ";
if (listUserNums.Contains(0))
{
//Selected users includes 'Unassigned' so we want to allow claims without associated claimTracking rows to show.
command += " OR claimtracking.UserNum IS NULL";
}
command += ") ";
}
command += "ORDER BY carrier.CarrierName,claim.DateService,patient.LName,patient.FName,claim.ClaimType";
object[] parameters = { command };
Plugins.HookAddCode(null, "Claims.GetOutInsClaims_beforequeryrun", parameters); //Moved entire method from Claims.cs
command = (string)parameters[0];
DataTable table = Db.GetTable(command);
List <OutstandingInsClaim> listOutstandingInsClaims = table.Rows.OfType <DataRow>().Select(x => new OutstandingInsClaim(x)).ToList();
return(listOutstandingInsClaims);
}
///<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 GetProcsNotBilled(List <long> listClinicNums, bool includeMedProcs, DateTime dateStart, DateTime dateEnd, bool showProcsBeforeIns)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), listClinicNums, includeMedProcs, dateStart, dateEnd, showProcsBeforeIns));
}
string query = "SELECT ";
if (PrefC.GetBool(PrefName.ReportsShowPatNum))
{
query += DbHelper.Concat("CAST(patient.PatNum AS CHAR)", "'-'", "patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI");
}
else
{
query += DbHelper.Concat("patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI");
}
query += " AS 'PatientName',procedurelog.ProcDate,procedurecode.Descript,procedurelog.ProcFee*(procedurelog.UnitQty+procedurelog.BaseUnits),"
+ "procedurelog.ProcNum,procedurelog.ClinicNum "
+ "FROM patient "
+ "INNER JOIN procedurelog ON procedurelog.PatNum = patient.PatNum "
+ "AND procedurelog.ProcFee>0 "
+ "AND procedurelog.procstatus=" + (int)ProcStat.C + " "
+ "AND procedurelog.ProcDate BETWEEN "+ POut.Date(dateStart) + " AND " + POut.Date(dateEnd) + " "
+ "INNER JOIN ( "
+ "SELECT PatNum FROM patplan GROUP BY PatNum "
+ " )HasIns ON HasIns.PatNum = patient.PatNum ";
if (listClinicNums.Count > 0)
{
query += "AND procedurelog.ClinicNum IN (" + String.Join(",", listClinicNums) + ") ";
}
query += "INNER JOIN procedurecode ON procedurecode.CodeNum = procedurelog.CodeNum ";
if (CultureInfo.CurrentCulture.Name.EndsWith("CA"))
{
query += "AND procedurecode.IsCanadianLab=0 "; //ignore Canadian labs
}
query += "LEFT JOIN claimproc ON claimproc.ProcNum = procedurelog.ProcNum "
+ "LEFT JOIN insplan ON insplan.PlanNum = claimproc.PlanNum ";
if (showProcsBeforeIns)
{
query += "WHERE ((claimproc.NoBillIns=0 "
+ "AND claimproc.Status=" + (int)ClaimProcStatus.Estimate + ") "
+ "OR claimproc.ClaimProcNum IS NULL) ";
}
else
{
query += "WHERE claimproc.NoBillIns=0 "
+ "AND claimproc.Status=" + (int)ClaimProcStatus.Estimate + " ";
}
if (!includeMedProcs)
{
query += "AND (insplan.IsMedical=0 ";
if (showProcsBeforeIns)
{
query += "OR insplan.PlanNum IS NULL ";
}
query += ") ";
}
query += "GROUP BY procedurelog.ProcNum "
+ "ORDER BY patient.LName,patient.FName,patient.PatNum,procedurelog.ProcDate";
return(Db.GetTable(query));
}
///<summary>If not using clinics then supply an empty list of clinicNums. listClinicNums must have at least one item if using clinics.
///The table returned has the following columns in this order:
///PatientName, ProcDate, Descript, ProcFee, ProcNum, ClinicNum, PatNum, IsInProcess</summary>
public static DataTable GetProcsNotBilled(List <long> listClinicNums, bool includeMedProcs, DateTime dateStart, DateTime dateEnd,
bool showProcsBeforeIns, bool hasMultiVisitProcs)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), listClinicNums, includeMedProcs, dateStart, dateEnd, showProcsBeforeIns, hasMultiVisitProcs));
}
string query = "SELECT ";
if (PrefC.GetBool(PrefName.ReportsShowPatNum))
{
query += DbHelper.Concat("CAST(PatNum AS CHAR)", "'-'", "LName", "', '", "FName", "' '", "MiddleI");
}
else
{
query += DbHelper.Concat("LName", "', '", "FName", "' '", "MiddleI");
}
query += " AS 'PatientName',Stat,ProcDate,Descript,procFee,ProcNum,ClinicNum,PatNum "
+ "FROM (SELECT patient.LName,patient.FName,patient.MiddleI,"
+ "CASE WHEN procmultivisit.ProcMultiVisitNum IS NULL "
+ "THEN '" + Lans.g("enumProcStat", ProcStat.C.ToString()) + "' ELSE '" + Lans.g("enumProcStat", ProcStatExt.InProcess) + "' END Stat,"
+ "procedurelog.ProcDate,procedurecode.Descript,procedurelog.ProcFee*(procedurelog.UnitQty+procedurelog.BaseUnits) procFee,"
+ "procedurelog.ProcNum,procedurelog.ClinicNum,patient.PatNum,insplan.PlanNum,MAX(insplan.IsMedical) isMedical,"
+ "CASE WHEN MIN(insplan.IsMedical)=0 THEN 1 ELSE 0 END hasDental "
+ "FROM patient "
+ "INNER JOIN procedurelog ON procedurelog.PatNum = patient.PatNum "
+ "AND procedurelog.ProcFee>0 "
+ "AND procedurelog.procstatus=" + (int)ProcStat.C + " "
+ "AND procedurelog.ProcDate BETWEEN "+ POut.Date(dateStart) + " AND " + POut.Date(dateEnd) + " "
+ "INNER JOIN procedurecode ON procedurecode.CodeNum = procedurelog.CodeNum ";
if (CultureInfo.CurrentCulture.Name.EndsWith("CA"))
{
query += "AND procedurecode.IsCanadianLab=0 "; //ignore Canadian labs
}
query += "LEFT JOIN claimproc ON claimproc.ProcNum = procedurelog.ProcNum "
+ "LEFT JOIN insplan ON insplan.PlanNum = claimproc.PlanNum "
+ "LEFT JOIN procmultivisit ON procmultivisit.ProcNum=procedurelog.ProcNum AND procmultivisit.IsInProcess=1 "
+ "WHERE EXISTS(SELECT 1 FROM patplan WHERE patplan.PatNum=patient.PatNum) "
+ "AND ((claimproc.NoBillIns=0 AND claimproc.Status=" + (int)ClaimProcStatus.Estimate + ") ";
if (showProcsBeforeIns)
{
query += "OR claimproc.ClaimProcNum IS NULL ";
}
query += ") ";
if (!hasMultiVisitProcs)
{
query += "AND (procmultivisit.ProcMultiVisitNum IS NULL) ";
}
if (listClinicNums.Count > 0)
{
query += "AND procedurelog.ClinicNum IN (" + String.Join(",", listClinicNums) + ") ";
}
query += "GROUP BY procedurelog.ProcNum "
+ ") procnotbilled "; //End of the main query which is treated like a sub query in order to process includeMedProcs and showProcsBeforeIns.
//Having the "AND insplan.IsMedical=0" check within the WHERE clause of the main query causes slowness for large databases.
//MySQL will freak out when looking for what index to use which causes full row scans to take place instead of simply filtering the results.
//This problem can be resolved by putting the insplan.IsMedical=0 check into the LEFT JOIN clause and performing a corresponding NULL check.
//However, the "OR insplan.PlanNum IS NULL" complicates the query enough to where it is easier to just put the old WHERE clause outside.
//This sub query trick improved the following report for a large office from ~55 seconds to ~5 seconds.
query += "WHERE (procnotbilled.hasDental=1 "; //Always include procedures when the patient has dental insurance.
if (includeMedProcs)
{
query += "OR procnotbilled.isMedical=1 ";
}
if (showProcsBeforeIns)
{
query += "OR procnotbilled.PlanNum IS NULL ";
}
query += ") ORDER BY LName,FName,PatNum,ProcDate";
return(Db.GetTable(query));
}
private static string ByAdjustmentsQuery(bool hasClinicsEnabled, DateTime dateStart, DateTime dateEnd, string whereProv, string whereClin, List <long> listAdj)
{
string queryBrokenApts = "SELECT adjustment.AdjDate AdjDate,provider.Abbr Provider," + DbHelper.Concat("patient.LName", "', '", "patient.FName") + " Patient,"
+ "adjustment.AdjAmt AdjAmt,adjustment.AdjNote AdjNote ";
if (hasClinicsEnabled)
{
queryBrokenApts += ",COALESCE(clinic.Description,'" + POut.String(Lans.g("FormRpBrokenAppointments", "Unassigned")) + "') ClinicDesc ";
}
queryBrokenApts +=
"FROM adjustment "
+ "INNER JOIN patient ON patient.PatNum=adjustment.PatNum "
+ "INNER JOIN provider ON provider.ProvNum=adjustment.ProvNum "
+ whereProv;
if (hasClinicsEnabled)
{
queryBrokenApts += "LEFT JOIN clinic ON clinic.ClinicNum=adjustment.ClinicNum ";
}
queryBrokenApts += "WHERE adjustment.AdjDate BETWEEN " + POut.Date(dateStart) + " AND " + POut.Date(dateEnd) + " ";
if (listAdj.Count > 0)
{
queryBrokenApts += "AND adjustment.AdjType IN(" + string.Join(",", listAdj) + ") ";
}
if (hasClinicsEnabled)
{
queryBrokenApts += whereClin + " "
+ "ORDER BY clinic.Description,adjustment.AdjDate,patient.LName,patient.FName";
}
else
{
queryBrokenApts += "ORDER BY adjustment.AdjDate,patient.LName,patient.FName";
}
return(queryBrokenApts);
}
///<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.HasClinicsEnabledNoCache);
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.Abbr Clinic, ";
}
queryIns += @"claimpayment.CheckNum,SUM(claimproc.InsPayAmt) amt,claimproc.ClaimNum,claimpayment.PayType,COUNT(DISTINCT claimproc.PatNum) countPats
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.Abbr,";
}
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);
}
DataTable table = ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(queryIns));
foreach (DataRow row in table.Rows)
{
//If there is more than one patient attached to a check, we will append an asterisk to the end.
int countPats = PIn.Int(row["countPats"].ToString());
if (countPats > 1)
{
row["lfname"] = row["lfname"].ToString().TrimEnd() + "*";
}
}
table.Columns.Remove("countPats"); //Remove this column because we don't want it to show on the report
return(table);
}
///<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 GetInsuranceOverpaid(DateTime dateStart, DateTime dateEnd, List <long> listClinicNums, bool groupByProc)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateStart, dateEnd, listClinicNums, groupByProc));
}
string query = @"SELECT " + DbHelper.Concat("patient.LName", "', '", "patient.FName") + @" patname,
procs.ProcDate,procs.fee ""$sumfee"",cp.PayAmt ""$PaidAndWriteoff""
FROM (
SELECT MIN(procedurelog.ProcNum) ProcNum,MIN(procedurelog.PatNum) PatNum,MIN(procedurelog.ProcDate) ProcDate,
SUM(procedurelog.ProcFee*(procedurelog.UnitQty+procedurelog.BaseUnits)) fee
FROM procedurelog
WHERE procedurelog.ProcDate BETWEEN " + POut.Date(dateStart) + " AND " + POut.Date(dateEnd) + @"
AND procedurelog.ProcStatus=" + POut.Int((int)ProcStat.C) + @"
AND procedurelog.ProcFee>0 " ;/*Negative proc fees should not show up on this report.*/
/*We have one office that uses negative proc fees as internal adjustments*/
if (listClinicNums.Count > 0)
{
query += "AND procedurelog.ClinicNum IN (" + string.Join(",", listClinicNums) + ") ";
}
if (groupByProc)
{
query += "GROUP BY procedurelog.ProcNum ";
}
else //Group by patient, proc date
{
query += "GROUP BY procedurelog.PatNum,procedurelog.ProcDate ";
}
query += @") procs
INNER JOIN (
SELECT MIN(claimproc.ProcNum) ProcNum,MIN(claimproc.PatNum) PatNum,MIN(claimproc.ProcDate) ProcDate,
SUM(claimproc.InsPayAmt+claimproc.Writeoff) PayAmt
FROM claimproc
WHERE claimproc.Status IN ("
+ POut.Int((int)ClaimProcStatus.Received) + ","
+ POut.Int((int)ClaimProcStatus.Supplemental) + ","
+ POut.Int((int)ClaimProcStatus.CapClaim) + ","
+ POut.Int((int)ClaimProcStatus.CapComplete) + @")
AND claimproc.ProcDate BETWEEN " + POut.Date(dateStart) + " AND " + POut.Date(dateEnd) + " ";
if (groupByProc)
{
query += "GROUP BY claimproc.ProcNum ";
}
else //Group by patient, proc date
{
query += "GROUP BY claimproc.PatNum,claimproc.ProcDate ";
}
query += @"HAVING SUM(claimproc.InsPayAmt+claimproc.Writeoff)>0" /*ProcFee must be >0 and PayAmt must be >ProcFee, ergo PayAmt must be >0*/ + @"
ORDER BY NULL
) cp ON " ;
if (groupByProc)
{
query += "cp.ProcNum=procs.ProcNum ";
}
else //Group by patient, proc date
{
query += @"cp.PatNum=procs.PatNum
AND cp.ProcDate=procs.ProcDate " ;
}
query += @"INNER JOIN patient ON patient.PatNum=procs.PatNum
WHERE ROUND(procs.fee,3) < ROUND(cp.PayAmt,3)
ORDER BY patient.LName,patient.FName,procs.ProcDate " ;
return(ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(query)));
}
///<summary>If not using clinics then supply an empty list of clinicNums.</summary>
public static DataTable GetWriteoffTable(DateTime dateStart, DateTime dateEnd, List <long> listProvNums, List <long> listClinicNums
, bool hasAllClinics, bool hasClinicsEnabled, PPOWriteoffDateCalc writeoffPayType)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateStart, dateEnd, listProvNums, listClinicNums, hasAllClinics
, hasClinicsEnabled, writeoffPayType));
}
string whereProv = "";
if (listProvNums.Count > 0)
{
whereProv += " AND claimproc.ProvNum IN(" + string.Join(",", listProvNums) + ") ";
}
string whereClin = "";
if (hasClinicsEnabled && listClinicNums.Count > 0) //Using clinics
{
whereClin += " AND claimproc.ClinicNum IN(" + string.Join(",", listClinicNums) + ") ";
}
string query = "SET @FromDate=" + POut.Date(dateStart) + ", @ToDate=" + POut.Date(dateEnd) + ";";
if (writeoffPayType == PPOWriteoffDateCalc.InsPayDate)
{
query += "SELECT " + DbHelper.DtimeToDate("claimproc.DateCP") + " date,"
+ DbHelper.Concat("patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI") + ","
+ "carrier.CarrierName,"
+ "provider.Abbr,";
if (hasClinicsEnabled)
{
query += "clinic.Abbr Clinic,";
}
query += "SUM(claimproc.WriteOff) $amount, "
+ "claimproc.ClaimNum "
+ "FROM claimproc " //,insplan,patient,carrier,provider "
+ "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 clinic ON clinic.ClinicNum=claimproc.ClinicNum "
+ "WHERE claimproc.Status IN (" + (int)ClaimProcStatus.Received + "," + (int)ClaimProcStatus.Supplemental + ") "
+ whereProv
+ whereClin
+ "AND claimproc.DateCP >= @FromDate "
+ "AND claimproc.DateCP <= @ToDate "
+ "AND (claimproc.WriteOff > .0001 OR claimproc.WriteOff < -.0001) "
+ "GROUP BY claimproc.ProvNum,claimproc.DateCP,claimproc.ClinicNum,claimproc.PatNum "
+ "ORDER BY claimproc.DateCP,claimproc.PatNum";
}
else if (writeoffPayType == PPOWriteoffDateCalc.ProcDate) //Means PPOWiteoffDateCalc==ProcDate, so we use the procedure date.
{
query += "SELECT " + DbHelper.DtimeToDate("claimproc.ProcDate") + " date, "
+ DbHelper.Concat("patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI") + ", "
+ "carrier.CarrierName, "
+ "provider.Abbr,";
if (hasClinicsEnabled)
{
query += "clinic.Abbr Clinic,";
}
query += "SUM(claimproc.WriteOff) $amount "
+ "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 clinic ON clinic.ClinicNum=claimproc.ClinicNum "
+ "WHERE claimproc.Status IN (" + (int)ClaimProcStatus.Received + "," + (int)ClaimProcStatus.Supplemental + "," + (int)ClaimProcStatus.NotReceived + ") "
+ whereProv
+ whereClin
+ "AND claimproc.ProcDate >= @FromDate "
+ "AND claimproc.ProcDate <= @ToDate "
+ "AND (claimproc.WriteOff > .0001 OR claimproc.WriteOff < -.0001) "
+ "GROUP BY claimproc.ProvNum,claimproc.ProcDate,claimproc.ClinicNum,claimproc.PatNum "
+ "ORDER BY claimproc.ProcDate,claimproc.PatNum";
}
else //writeoffPayType==PPOWriteoffDateCalc.ClaimPayDate
{
query += "SELECT " + DbHelper.DtimeToDate("claimsnapshot.DateTEntry") + " date, "
+ DbHelper.Concat("patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI") + ", "
+ "carrier.CarrierName, "
+ "provider.Abbr,";
if (hasClinicsEnabled)
{
query += "clinic.Abbr Clinic,";
}
query += "SUM(claimsnapshot.WriteOff) $amount "
+ "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 clinic ON clinic.ClinicNum=claimproc.ClinicNum "
+ "INNER JOIN claimsnapshot on claimsnapshot.ClaimProcNum=claimproc.ClaimProcNum " //use claimsnapshot DateTEntry instead of claimproc
+ "WHERE claimproc.Status IN (" + (int)ClaimProcStatus.Received + "," + (int)ClaimProcStatus.Supplemental + "," + (int)ClaimProcStatus.NotReceived + ") "
+ whereProv
+ whereClin
+ "AND claimsnapshot.DateTEntry >= @FromDate "
+ "AND claimsnapshot.DateTEntry <= @ToDate "
+ "AND claimsnapshot.WriteOff > 0 "
+ "GROUP BY claimproc.ProvNum,claimsnapshot.DateTEntry,claimproc.ClinicNum,claimproc.PatNum "
+ "ORDER BY claimsnapshot.DateTEntry,claimproc.PatNum";
}
return(ReportsComplex.RunFuncOnReportServer(() => ReportsComplex.GetTable(query)));
}
///<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 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(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 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 DataTable GetLetterMergeInfo(Patient PatCur, LetterMerge letter)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), PatCur, letter));
}
//jsparks- This is messy and prone to bugs. It needs to be reworked to work just like
//in SheetFiller.FillFieldsInStaticText. Just grab a bunch of separate objects
//instead of one result row.
string command;
//We need a very small table that tells us which tp is the most recent.
//command="DROP TABLE IF EXISTS temptp;";
//Db.NonQ(command);
//command=@"CREATE TABLE temptp(
// DateTP date NOT NULL default '0001-01-01')";
//Db.NonQ(command);
//command+=@"CREATE TABLE temptp
// SELECT MAX(treatplan.DateTP) DateTP
// FROM treatplan
// WHERE PatNum="+POut.PInt(PatCur.PatNum)+";";
//Db.NonQ(command);
command = "SET @maxTpDate=(SELECT MAX(treatplan.DateTP) FROM treatplan WHERE PatNum=" + POut.Long(PatCur.PatNum) + ");";
command += "SELECT ";
for (int i = 0; i < letter.Fields.Count; i++)
{
if (i > 0)
{
command += ",";
}
if (letter.Fields[i] == "NextAptNum")
{
command += "MAX(plannedappt.AptNum) NextAptNum";
}
//other:
else if (letter.Fields[i] == "TPResponsPartyNameFL")
{
command += DbHelper.Concat("MAX(patResp.FName)", "' '", "MAX(patResp.LName)") + " TPResponsPartyNameFL";
}
else if (letter.Fields[i] == "TPResponsPartyAddress")
{
command += "MAX(patResp.Address) TPResponsPartyAddress";
}
else if (letter.Fields[i] == "TPResponsPartyCityStZip")
{
command += DbHelper.Concat("MAX(patResp.City)", "', '", "MAX(patResp.State)", "' '", "MAX(patResp.Zip)") + " TPResponsPartyCityStZip";
}
else if (letter.Fields[i] == "SiteDescription")
{
command += "MAX(site.Description) SiteDescription";
}
else if (letter.Fields[i] == "DateOfLastSavedTP")
{
command += DbHelper.DateColumn("MAX(treatplan.DateTP)") + " DateOfLastSavedTP";
}
else if (letter.Fields[i] == "DateRecallDue")
{
command += "MAX(recall.DateDue) DateRecallDue";
}
else if (letter.Fields[i] == "CarrierName")
{
command += "MAX(CarrierName) CarrierName";
}
else if (letter.Fields[i] == "CarrierAddress")
{
command += "MAX(carrier.Address) CarrierAddress";
}
else if (letter.Fields[i] == "CarrierCityStZip")
{
command += DbHelper.Concat("MAX(carrier.City)", "', '", "MAX(carrier.State)", "' '", "MAX(carrier.Zip)") + " CarrierCityStZip";
}
else if (letter.Fields[i] == "SubscriberNameFL")
{
command += DbHelper.Concat("MAX(patSubsc.FName)", "' '", "MAX(patSubsc.LName)") + " SubscriberNameFL";
}
else if (letter.Fields[i] == "SubscriberID")
{
command += "MAX(inssub.SubscriberID) SubscriberID";
}
else if (letter.Fields[i] == "NextSchedAppt")
{
command += "MIN(appointment.AptDateTime) NextSchedAppt";
}
else if (letter.Fields[i] == "Age")
{
command += "MAX(patient.Birthdate) BirthdateForAge";
}
else if (letter.Fields[i] == "Guarantor")
{
command += DbHelper.Concat("MAX(patGuar.FName)", "' '", "MAX(patGuar.LName)") + " Guarantor";
}
else if (letter.Fields[i] == "GradeSchool")
{
command += "MAX(site.Description) GradeSchool";
}
else if (letter.Fields[i].StartsWith("referral."))
{
command += "MAX(referral." + letter.Fields[i].Substring(9) + ") " + letter.Fields[i].Substring(9);
}
else
{
command += "MAX(patient." + letter.Fields[i] + ") " + letter.Fields[i];
}
}
command += " FROM patient "
+ "LEFT JOIN refattach ON patient.PatNum=refattach.PatNum AND refattach.IsFrom=1 "
+ "LEFT JOIN referral ON refattach.ReferralNum=referral.ReferralNum "
+ "LEFT JOIN plannedappt ON plannedappt.PatNum=patient.PatNum AND plannedappt.ItemOrder=1 "
+ "LEFT JOIN site ON patient.SiteNum=site.SiteNum "
+ "LEFT JOIN treatplan ON patient.PatNum=treatplan.PatNum AND DateTP=@maxTpDate "
+ "LEFT JOIN patient patResp ON treatplan.ResponsParty=patResp.PatNum "
+ "LEFT JOIN recall ON recall.PatNum=patient.PatNum "
+ "AND (recall.RecallTypeNum=" + POut.Long(PrefC.GetLong(PrefName.RecallTypeSpecialProphy))
+ " OR recall.RecallTypeNum=" + POut.Long(PrefC.GetLong(PrefName.RecallTypeSpecialPerio)) + ") "
+ "LEFT JOIN patplan ON patplan.PatNum=patient.PatNum AND Ordinal=1 "
+ "LEFT JOIN inssub ON patplan.InsSubNum=inssub.InsSubNum "
+ "LEFT JOIN insplan ON inssub.PlanNum=insplan.PlanNum "
+ "LEFT JOIN carrier ON carrier.CarrierNum=insplan.CarrierNum "
+ "LEFT JOIN patient patSubsc ON patSubsc.PatNum=inssub.Subscriber "
+ "LEFT JOIN appointment ON appointment.PatNum=patient.PatNum "
+ "AND AptStatus=" + POut.Long((int)ApptStatus.Scheduled) + " "
+ "AND AptDateTime > " + DbHelper.Now() + " "
+ "LEFT JOIN patient patGuar ON patGuar.PatNum=patient.Guarantor "
+ "WHERE patient.PatNum=" + POut.Long(PatCur.PatNum)
+ " GROUP BY patient.PatNum "
+ "ORDER BY refattach.ItemOrder";
return(Db.GetTable(command));
}
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,
bool doShowHiddenTPUnearned)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateFrom, dateTo, listProvNums, listClinicNums, listPatientTypes, hasAllProvs, hasAllClinics,
hasPatientTypes, isGroupedByPatient, isUnearnedIncluded, doShowProvSeparate, doShowHiddenTPUnearned));
}
//reports should no longer use the cache
bool hasClinicsEnabled = ReportsComplex.RunFuncOnReportServer(() => Prefs.HasClinicsEnabledNoCache);
List <long> listHiddenUnearnedDefNums = new List <long>();
if (!doShowHiddenTPUnearned)
{
listHiddenUnearnedDefNums = ReportsComplex.RunFuncOnReportServer(() =>
Defs.GetDefsNoCache(DefCat.PaySplitUnearnedType).FindAll(x => !string.IsNullOrEmpty(x.ItemValue)).Select(x => x.DefNum).ToList()
);
}
//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.Abbr clinicAbbr,";
}
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 ";
}
else if (listHiddenUnearnedDefNums.Count > 0 && !doShowHiddenTPUnearned) //Include unearned but not of the TP type.
{
queryPat += $"AND paysplit.UnearnedType NOT IN ({string.Join(",",listHiddenUnearnedDefNums)}) ";
}
queryPat += "GROUP BY payment.PayNum,payment.PayDate,payment.CheckNum,definition.ItemName,payment.PayType ";
if (doShowProvSeparate)
{
queryPat += ",provider.ProvNum ";
}
if (hasClinicsEnabled)
{
queryPat += ",clinic.Abbr ";
}
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>Returns list of credit cards that are ready for a recurring charge.</summary>
public static DataTable GetRecurringChargeList()
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod()));
}
DataTable table = new DataTable();
//This query will return patient information and the latest recurring payment whom:
// -have recurring charges setup and today's date falls within the start and stop range.
// -have a total balance >= recurring charge amount
//NOTE: Query will return patients with or without payments regardless of when that payment occurred, filtering is done below.
string command = "SELECT PatNum,PatName,FamBalTotal,LatestPayment,DateStart,Address,AddressPat,Zip,ZipPat,XChargeToken,CCNumberMasked,CCExpiration,ChargeAmt,PayPlanNum,ProvNum,ClinicNum "
+ "FROM (";
#region Payments
command += "(SELECT 1,cc.PatNum," + DbHelper.Concat("pat.LName", "', '", "pat.FName") + " PatName," //The 'SELECT 1' garuntees the UNION will not combine results with payment plans.
+ "guar.BalTotal-guar.InsEst FamBalTotal,CASE WHEN MAX(pay.PayDate) IS NULL THEN " + POut.Date(new DateTime(1, 1, 1)) + " ELSE MAX(pay.PayDate) END LatestPayment,"
+ "cc.DateStart,cc.Address,pat.Address AddressPat,cc.Zip,pat.Zip ZipPat,cc.XChargeToken,cc.CCNumberMasked,cc.CCExpiration,cc.ChargeAmt,cc.PayPlanNum,cc.DateStop,0 ProvNum,pat.ClinicNum "
+ "FROM (creditcard cc,patient pat,patient guar) "
+ "LEFT JOIN payment pay ON cc.PatNum=pay.PatNum AND pay.IsRecurringCC=1 "
+ "WHERE cc.PatNum=pat.PatNum "
+ "AND pat.Guarantor=guar.PatNum "
+ "AND cc.PayPlanNum=0 "; //Keeps card from showing up in case they have a balance AND is setup for payment plan.
if (DataConnection.DBtype == DatabaseType.MySql)
{
command += "GROUP BY cc.CreditCardNum) ";
}
else //Oracle
{
command += "GROUP BY cc.CreditCardNum,cc.PatNum," + DbHelper.Concat("pat.LName", "', '", "pat.FName") + ",PatName,guar.BalTotal-guar.InsEst,"
+ "cc.Address,cc.Zip,cc.XChargeToken,cc.CCNumberMasked,cc.CCExpiration,cc.ChargeAmt,cc.PayPlanNum,cc.DateStop) ";
}
#endregion
command += "UNION ";
#region Payment Plans
command += "(SELECT 2,cc.PatNum," + DbHelper.Concat("pat.LName", "', '", "pat.FName") + " PatName,"; //The 'SELECT 2' garuntees the UNION will not combine results with payments.
//Special select statement to figure out how much is owed on a particular payment plan. This total amount will be Labeled as FamBalTotal for UNION purposes.
command += "ROUND((SELECT CASE WHEN SUM(ppc.Principal+ppc.Interest) IS NULL THEN 0 ELSE SUM(ppc.Principal+ppc.Interest) END "
+ "FROM PayPlanCharge ppc "
+ "WHERE ppc.ChargeDate <= " + DbHelper.Curdate() + " AND ppc.PayPlanNum=cc.PayPlanNum) "
+ "- CASE WHEN SUM(ps.SplitAmt) IS NULL THEN 0 ELSE SUM(ps.SplitAmt) END,2) FamBalTotal,";
command += "CASE WHEN MAX(ps.DatePay) IS NULL THEN " + POut.Date(new DateTime(1, 1, 1)) + " ELSE MAX(pay.PayDate) END LatestPayment,"
+ "cc.DateStart,cc.Address,pat.Address AddressPat,cc.Zip,pat.Zip ZipPat,cc.XChargeToken,cc.CCNumberMasked,cc.CCExpiration,cc.ChargeAmt,cc.PayPlanNum,cc.DateStop,"
+ "(SELECT ppc1.ProvNum FROM payplancharge ppc1 WHERE ppc1.PayPlanNum=cc.PayPlanNum LIMIT 1) ProvNum,"
+ "(SELECT ppc2.ClinicNum FROM payplancharge ppc2 WHERE ppc2.PayPlanNum=cc.PayPlanNum LIMIT 1) ClinicNum "
+ "FROM creditcard cc "
+ "INNER JOIN patient pat ON pat.PatNum=cc.PatNum "
+ "LEFT JOIN paysplit ps ON ps.PayPlanNum=cc.PayPlanNum AND ps.PayPlanNum<>0 "
+ "LEFT JOIN payment pay ON pay.PayNum=ps.PayNum AND pay.IsRecurringCC=1 "
+ "WHERE cc.PayPlanNum<>0 ";
if (DataConnection.DBtype == DatabaseType.MySql)
{
command += "GROUP BY cc.CreditCardNum) ";
}
else //Oracle
{
command += "GROUP BY cc.CreditCardNum,cc.PatNum," + DbHelper.Concat("pat.LName", "', '", "pat.FName") + ",PatName,guar.BalTotal-guar.InsEst,"
+ "cc.Address,pat.Address,cc.Zip,pat.Zip,cc.XChargeToken,cc.CCNumberMasked,cc.CCExpiration,cc.ChargeAmt,cc.PayPlanNum,cc.DateStop) ";
}
#endregion
//Now we have all the results for payments and payment plans, so do an obvious filter. A more thorough filter happens later.
command += ") due "
+ "WHERE FamBalTotal>=ChargeAmt "
+ "AND ChargeAmt>0 "
+ "AND DateStart<=" + DbHelper.Curdate() + " "
+ "AND (DateStop>=" + DbHelper.Curdate() + " OR YEAR(DateStop)<1880) ";
table = Db.GetTable(command);
FilterRecurringChargeList(table);
return(table);
}
