///<summary>Gets a list of aptNums for one day in the schedule for a given set of providers and clinics.</summary>
public static List <long> GetRouting(DateTime date, List <long> listProvNums, List <long> listClinicNums, bool hasAllprovs)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <long> >(MethodBase.GetCurrentMethod(), date, listProvNums, listClinicNums, hasAllprovs));
}
//Excluding PtNote and PtNoteCompleted per Nathan and Arna, see job 1064
string command =
"SELECT AptNum FROM appointment "
+ "WHERE AptDateTime LIKE '" + POut.Date(date, false) + "%' "
+ "AND AptStatus NOT IN ('" + POut.Int((int)ApptStatus.UnschedList) + "', '" + POut.Int((int)ApptStatus.Planned) + "', '"
+ POut.Int((int)ApptStatus.PtNote) + "', '" + POut.Int((int)ApptStatus.PtNoteCompleted) + "') ";
if (!hasAllprovs)
{
command += "AND (ProvNum IN (" + String.Join(",", listProvNums) + ") ";
command += "OR ProvHyg IN (" + String.Join(",", listProvNums) + ")) ";
}
if (listClinicNums.Count > 0)
{
command += "AND ClinicNum IN (" + String.Join(",", listClinicNums) + ") ";
}
command += "ORDER BY AptDateTime";
DataTable table = ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(command));
List <long> retVal = new List <long>();
for (int i = 0; i < table.Rows.Count; i++)
{
retVal.Add(PIn.Long(table.Rows[i][0].ToString()));
}
return(retVal);
}
public static DataTable GetGroupedTable(DateTime dateFrom, DateTime dateTo, List <long> listProvNums, List <long> listClinicNums, string procCode, bool hasAllProvs)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateFrom, dateTo, listProvNums, listClinicNums, procCode, hasAllProvs));
}
string query = "SELECT procs.ItemName,procs.ProcCode,procs.Descript,COUNT(*),FORMAT(ROUND(AVG(procs.fee),2),2) $AvgFee,SUM(procs.fee) AS $TotFee "
+ "FROM ( "
+ "SELECT procedurelog.ProcFee*(procedurelog.UnitQty+procedurelog.BaseUnits) -COALESCE(SUM(claimproc.WriteOff),0) fee, "
+ "procedurecode.ProcCode, procedurecode.Descript, definition.ItemName, definition.ItemOrder "
+ "FROM procedurelog "
+ "INNER JOIN procedurecode ON procedurelog.CodeNum=procedurecode.CodeNum "
+ "INNER JOIN definition ON definition.DefNum=procedurecode.ProcCat "
+ "LEFT JOIN claimproc ON claimproc.ProcNum=procedurelog.ProcNum AND claimproc.Status=" + POut.Int((int)ClaimProcStatus.CapComplete) + " "
+ "WHERE procedurelog.ProcStatus=" + POut.Int((int)ProcStat.C) + " ";
if (!hasAllProvs)
{
query += "AND procedurelog.ProvNum IN (" + String.Join(",", listProvNums) + ") ";
}
if (ReportsComplex.RunFuncOnReportServer(() => Prefs.HasClinicsEnabledNoCache))
{
query += "AND procedurelog.ClinicNum IN (" + String.Join(",", listClinicNums) + ") ";
}
if (!string.IsNullOrEmpty(procCode)) //don't include ProcCode condition if blank, it changes the execution plan and is much slower
{
query += "AND procedurecode.ProcCode LIKE '%" + POut.String(procCode) + "%' ";
}
query += "AND procedurelog.ProcDate >= " + POut.Date(dateFrom) + " "
+ "AND procedurelog.ProcDate <= " + POut.Date(dateTo) + " "
+ "GROUP BY procedurelog.ProcNum ) procs "
+ "GROUP BY procs.ProcCode "
+ "ORDER BY procs.ItemOrder,procs.ProcCode";
return(ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(query)));
}
///<summary>If not using clinics then supply an empty list of clinicNums.</summary>
public static DataTable GetBrokenApptTable(DateTime dateStart, DateTime dateEnd, List <long> listProvNums, List <long> listClinicNums,
List <long> listAdj, BrokenApptProcedure brokenApptOption, bool hasAllClinics, bool isByProc, bool isByAptStatus, bool isByAdj, bool hasClinicsEnabled)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateStart, dateEnd, listProvNums, listClinicNums, listAdj, brokenApptOption, hasAllClinics, isByProc
, isByAptStatus, isByAdj, hasClinicsEnabled));
}
string whereProv = "";
if (listProvNums.Count > 0)
{
if (isByProc) //Report looking at ADA procedure code D9986
{
whereProv = " AND procedurelog.ProvNum IN (" + string.Join(",", listProvNums) + ") ";
}
else if (isByAdj) //Report looking at broken appointment adjustments
{
whereProv = " AND adjustment.ProvNum IN (" + string.Join(",", listProvNums) + ") ";
}
else //Report looking at appointments with a status of 'Broken'
{
whereProv = " AND (appointment.ProvNum IN (" + string.Join(",", listProvNums) + ") "
+ "OR appointment.ProvHyg IN (" + string.Join(",", listProvNums) + ")) ";
}
}
string whereClin = "";
if (hasClinicsEnabled && listClinicNums.Count > 0)
{
if (isByProc) //Report looking at ADA procedure code D9986
{
whereClin += " AND procedurelog.ClinicNum IN(";
}
else if (isByAdj) //Report looking at broken appointment adjustments
{
whereClin += " AND adjustment.ClinicNum IN(";
}
else //Report looking at appointments with a status of 'Broken'
{
whereClin += " AND appointment.ClinicNum IN(";
}
whereClin += string.Join(",", listClinicNums) + ") ";
}
string queryBrokenApts = "";
if (isByProc)
{
queryBrokenApts = ByProceduresQuery(hasClinicsEnabled, dateStart, dateEnd, whereProv, whereClin, brokenApptOption);
}
if (isByAdj)
{
queryBrokenApts = ByAdjustmentsQuery(hasClinicsEnabled, dateStart, dateEnd, whereProv, whereClin, listAdj);
}
if (isByAptStatus)
{
queryBrokenApts = ByApptStatusQuery(hasClinicsEnabled, dateStart, dateEnd, whereProv, whereClin);
}
return(ReportsComplex.RunFuncOnReportServer(() => ReportsComplex.GetTable(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);
}
///<summary></summary>
public static DataTable GetCapitationTable(DateTime dateStart, DateTime dateEnd, string textCarrier, bool isMedicalOrClinic)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateStart, dateEnd, textCarrier, isMedicalOrClinic));
}
string queryString = @"SELECT carrier.CarrierName,CONCAT(CONCAT(patSub.LName,', '),patSub.FName)
,patSub.SSN,CONCAT(CONCAT(patPat.LName,', '),patPat.FName)
,patPat.Birthdate,procedurecode.ProcCode,procedurecode.Descript" ;
if (!isMedicalOrClinic)
{
queryString += ",procedurelog.ToothNum,procedurelog.Surf";
}
queryString += @",procedurelog.ProcDate,procedurelog.ProcFee
,procedurelog.ProcFee-claimproc.WriteOff
FROM procedurelog,patient AS patSub,patient AS patPat
,insplan,inssub,carrier,procedurecode,claimproc
WHERE procedurelog.PatNum = patPat.PatNum
AND claimproc.InsSubNum = inssub.InsSubNum
AND procedurelog.ProcNum = claimproc.ProcNum
AND claimproc.PlanNum = insplan.PlanNum
AND claimproc.Status = " + POut.Int((int)ClaimProcStatus.CapComplete) + @"
AND claimproc.NoBillIns = 0
AND inssub.Subscriber = patSub.PatNum
AND insplan.CarrierNum = carrier.CarrierNum
AND procedurelog.CodeNum = procedurecode.CodeNum "
+ "AND carrier.CarrierName LIKE '%" + POut.String(textCarrier) + "%' "
+ "AND procedurelog.ProcDate >= " + POut.Date(dateStart) + " "
+ "AND procedurelog.ProcDate <= " + POut.Date(dateEnd) + " "
+ "AND insplan.PlanType = 'c' "
+ "AND procedurelog.ProcStatus = " + POut.Int((int)ProcStat.C);
return(ReportsComplex.RunFuncOnReportServer(() => ReportsComplex.GetTable(queryString)));
}
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)));
}
public static DataTable GetTable(string command)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), command));
}
return(ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(command)));
}
///<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 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>Gets a list of unfinalized insurance payments.</summary>
public static List <UnfinalizedInsPay> GetUnfinalizedInsPay(string carrierName)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <UnfinalizedInsPay> >(MethodBase.GetCurrentMethod(), carrierName));
}
string command = @"
SELECT partialpay.PayType,partialpay.PatNum,partialpay.ClaimPaymentNum,partialpay.ClinicNum,partialpay.CarrierName,partialpay.Date,
partialpay.DOS,partialpay.Amount,partialpay.ClaimNum,partialpay.CountPats
FROM (
SELECT 'PartialPayment' PayType,COALESCE(MAX(claimproc.PatNum),0) PatNum,MAX(claimpayment.ClaimPaymentNum) ClaimPaymentNum,
COUNT(DISTINCT claimproc.PatNum) CountPats,MAX(claimpayment.ClinicNum) ClinicNum,MAX(claimpayment.CarrierName) CarrierName,
MAX(claimpayment.CheckDate) Date,COALESCE(MAX(claimproc.ProcDate),'0001-01-01') DOS,MAX(claimpayment.CheckAmt) Amount,0 ClaimNum
FROM claimpayment
LEFT JOIN claimproc ON claimproc.ClaimPaymentNum=claimpayment.ClaimPaymentNum
WHERE claimpayment.IsPartial = 1
AND claimpayment.CarrierName LIKE '%" + POut.String(carrierName.Trim()) + "%' " + @"
GROUP BY claimpayment.ClaimPaymentNum
UNION ALL
SELECT 'UnfinalizedPayment' PayType,MAX(claimproc.PatNum) PatNum,0 ClaimPaymentNum,1 CountPats,MAX(claimproc.ClinicNum) ClinicNum,
MAX(carrier.CarrierName) CarrierName,MAX(claimproc.DateCP) Date,MAX(claimproc.ProcDate) DOS,SUM(claimproc.InsPayAmt) Amount,
claimproc.ClaimNum
FROM claimproc
INNER JOIN insplan ON insplan.PlanNum=claimproc.PlanNum
INNER JOIN carrier ON carrier.CarrierNum=insplan.CarrierNum
AND carrier.CarrierName LIKE '%" + POut.String(carrierName.Trim()) + "%' "
//Filter logic here mimics batch payments in ClaimProcs.AttachAllOutstandingToPayment().
+ @"WHERE claimproc.ClaimPaymentNum = 0 AND claimproc.InsPayAmt != 0
AND claimproc.Status IN(" + POut.Int((int)ClaimProcStatus.Received) + ","
+ POut.Int((int)ClaimProcStatus.Supplemental) + "," + POut.Int((int)ClaimProcStatus.CapClaim) + @")
AND claimproc.IsTransfer=0
GROUP BY claimproc.ClaimNum
) partialpay" ;
DataTable table = ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(command));
List <Patient> listPats = Patients.GetMultPats(table.Select().Select(x => PIn.Long(x["PatNum"].ToString())).ToList()).ToList();
List <Claim> listClaims = Claims.GetClaimsFromClaimNums(table.Select().Select(x => PIn.Long(x["ClaimNum"].ToString())).ToList());
List <ClaimPayment> listPayments = ClaimPayments.GetByClaimPaymentNums(table.Select().Select(x => PIn.Long(x["ClaimPaymentNum"].ToString()))
.ToList());
List <UnfinalizedInsPay> listUnfinalizedInsPay = new List <UnfinalizedInsPay>();
for (int i = 0; i < table.Rows.Count; i++)
{
listUnfinalizedInsPay.Add(new UnfinalizedInsPay(table.Rows[i]["PayType"].ToString(),
listPats.FirstOrDefault(x => x.PatNum == PIn.Long(table.Rows[i]["PatNum"].ToString())),
PIn.Long(table.Rows[i]["ClinicNum"].ToString()),
table.Rows[i]["CarrierName"].ToString(),
PIn.Date(table.Rows[i]["Date"].ToString()),
PIn.Date(table.Rows[i]["DOS"].ToString()),
PIn.Double(table.Rows[i]["Amount"].ToString()),
listPayments.FirstOrDefault(x => x.ClaimPaymentNum == PIn.Long(table.Rows[i]["ClaimPaymentNum"].ToString())),
listClaims.FirstOrDefault(x => x.ClaimNum == PIn.Long(table.Rows[i]["ClaimNum"].ToString())),
PIn.Int(table.Rows[i]["CountPats"].ToString())
));
}
return(listUnfinalizedInsPay);
}
///<summary></summary>
public static DataTable GetPatUncollected(DateTime dateFrom, DateTime dateTo, List <long> listClinicNums)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateFrom, dateTo, listClinicNums));
}
bool hasClinicsEnabled = ReportsComplex.RunFuncOnReportServer(() => (!Prefs.GetBoolNoCache(PrefName.EasyNoClinics)));
string query = "SELECT procedurelog.ProcDate,CONCAT(patient.LName,', ',patient.FName)AS Patient, procedurecode.AbbrDesc, "
+ "procedurelog.ProcFee*(procedurelog.BaseUnits+procedurelog.UnitQty)-IFNULL(cp.CapWriteOff,0) Fee,"
+ "IFNULL((procedurelog.ProcFee*(procedurelog.BaseUnits+procedurelog.UnitQty))-IFNULL(cp.CapWriteOff,0)-IFNULL(cp.InsAmt,0)"
+ "-IFNULL(cp.WriteOff,0),0) AS PatPortion,"
+ "IFNULL(adj.AdjAmt,0) AS Adjustment,"
+ "IFNULL(pay.SplitAmt,0) AS Payment,"
+ "IFNULL((procedurelog.ProcFee*(procedurelog.BaseUnits+procedurelog.UnitQty))-IFNULL(cp.CapWriteOff,0)-IFNULL(cp.InsAmt,0)-"
+ "IFNULL(cp.WriteOff,0),0)+IFNULL(adj.AdjAmt,0)-IFNULL(pay.SplitAmt,0) Uncollected "
+ "FROM procedurelog "
+ "INNER JOIN patient ON patient.PatNum=procedurelog.PatNum "
+ "INNER JOIN procedurecode ON procedurecode.CodeNum=procedurelog.CodeNum "
+ "LEFT JOIN ( "
+ "SELECT SUM(adjustment.AdjAmt) AdjAmt, adjustment.ProcNum "
+ "FROM adjustment "
+ "WHERE adjustment.ProcNum != 0 "
+ "GROUP BY ProcNum "
+ ")adj ON adj.ProcNum=procedurelog.ProcNum "
+ "LEFT JOIN ( "
+ "SELECT claimproc.Status, SUM(CASE WHEN claimproc.Status = " + POut.Int((int)ClaimProcStatus.NotReceived) + " THEN claimproc.InsPayEst "
+ "WHEN claimproc.Status IN(" + POut.Int((int)ClaimProcStatus.Received) + "," + POut.Int((int)ClaimProcStatus.Supplemental) + ") "
+ "THEN claimproc.InsPayAmt "
+ "ELSE 0 END) AS InsAmt, "
+ "SUM(IF(claimproc.Status!=" + POut.Int((int)ClaimProcStatus.CapComplete) + ",claimproc.WriteOff,0)) AS WriteOff, "
+ "SUM(IF(claimproc.Status=" + POut.Int((int)ClaimProcStatus.CapComplete) + ",claimproc.WriteOff,0)) AS CapWriteOff, "
+ "claimproc.ProcNum "
+ "FROM claimproc "
+ "WHERE claimproc.Status IN(" + POut.Int((int)ClaimProcStatus.NotReceived) + ","
+ POut.Int((int)ClaimProcStatus.Received) + ","
+ POut.Int((int)ClaimProcStatus.Supplemental) + ","
+ POut.Int((int)ClaimProcStatus.CapComplete) + ") "
+ "GROUP BY claimproc.ProcNum "
+ ")cp ON cp.ProcNum=procedurelog.ProcNum "
+ "LEFT JOIN ( "
+ "SELECT SUM(paysplit.SplitAmt) SplitAmt, paysplit.ProcNum "
+ "FROM paysplit "
+ "WHERE paysplit.ProcNum != 0 "
+ "GROUP BY paysplit.ProcNum "
+ ")pay ON pay.ProcNum=procedurelog.ProcNum "
+ "WHERE procedurelog.ProcStatus=2 "
+ "AND procedurelog.ProcDate BETWEEN " + POut.Date(dateFrom) + " AND " + POut.Date(dateTo) + " ";
if (hasClinicsEnabled && listClinicNums.Count > 0)
{
query += "AND procedurelog.ClinicNum IN(" + string.Join(",", listClinicNums.Select(x => POut.Long(x))) + ") ";
}
query += "HAVING Uncollected > 0.005 "
+ "ORDER BY procedurelog.ProcDate,patient.LName,patient.FName,procedurecode.ProcCode";
return(ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(query)));
}
public static DataTable GetAgingTable(RpAgingParamObject rpo)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), rpo));
}
string queryAg = GetQueryString(rpo);
return(ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(queryAg)));
}
///<summary>Dashboardar is safe to truncate because it gets refilled as needed and there are no FKeys to any other table.</summary>
public static void Truncate()
{
string command = "TRUNCATE dashboardar";
Db.NonQ(command);
if (!string.IsNullOrEmpty(PrefC.ReportingServer.Server)) //only attempt to insert into the reporting server if the reporting server is set up.
{
ReportsComplex.RunFuncOnReportServer(() => Db.NonQ(command));
}
}
///<summary>Gets all rows gt= dateFrom.</summary>
public static List <DashboardAR> Refresh(DateTime dateFrom)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <DashboardAR> >(MethodBase.GetCurrentMethod(), dateFrom));
}
string command = "SELECT * FROM dashboardar WHERE DateCalc >= " + POut.Date(dateFrom);
return(ReportsComplex.RunFuncOnReportServer(() => Crud.DashboardARCrud.SelectMany(command)));
}
///<summary>Get TaskListNums based on description.</summary>
public static List <long> GetNumsByDescription(string descript, bool doRunOnReportServer)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <long> >(MethodBase.GetCurrentMethod(), descript, doRunOnReportServer));
}
string command = "SELECT TaskListNum FROM tasklist WHERE Descript LIKE '%" + POut.String(descript) + "%'";
return(ReportsComplex.RunFuncOnReportServer(() => Db.GetListLong(command), doRunOnReportServer));
}
/// <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>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);
}
///<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)));
}
///<summary>Dashboardar is safe to truncate because it gets refilled as needed and there are no FKeys to any other table.</summary>
public static void Truncate()
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod());
return;
}
string command = "TRUNCATE dashboardar";
Db.NonQ(command);
if (!string.IsNullOrEmpty(PrefC.ReportingServer.Server)) //only attempt to insert into the reporting server if the reporting server is set up.
{
ReportsComplex.RunFuncOnReportServer(() => Db.NonQ(command));
}
}
public static DataTable GetDataSet(long feeSchedNum, long clinicNum, long provNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), feeSchedNum, clinicNum, provNum));
}
string command = "SELECT procedurecode.ProcCode,fee.Amount,procedurecode.Descript,"
+ "procedurecode.AbbrDesc FROM procedurecode,fee "
+ "WHERE procedurecode.CodeNum=fee.CodeNum "
+ "AND fee.FeeSched='" + POut.Long(feeSchedNum) + "' "
+ "AND fee.ClinicNum='" + POut.Long(clinicNum) + "' "
+ "AND fee.ProvNum='" + POut.Long(provNum) + "' "
+ "ORDER BY procedurecode.ProcCode";
return(ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(command)));
}
public static DataTable GetAgingTable(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.GetTable(MethodBase.GetCurrentMethod(), asOfDate, isWoAged, hasDateLastPay, isGroupByFam, isOnlyNeg, accountAge, isIncludeNeg,
isExcludeInactive, isExcludeBadAddress, listProv, listClinicNums, listBillType, isExcludeArchive, isIncludeInsNoBal, isOnlyInsNoBal,
isForceAgeNegAdj, isForInsAging, doAgePatPayPlanPayments));
}
string queryAg = GetQueryString(asOfDate, isWoAged, hasDateLastPay, isGroupByFam, isOnlyNeg, accountAge, isIncludeNeg, isExcludeInactive,
isExcludeBadAddress, listProv, listClinicNums, listBillType, isExcludeArchive, isIncludeInsNoBal, isOnlyInsNoBal, isForceAgeNegAdj, isForInsAging
, doAgePatPayPlanPayments);
return(ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(queryAg)));
}
///<summary>Gets a table of data using low permissions.</summary>
public static DataTable GetTable(string description)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), description));
}
string query = "SELECT discountplan.Description, feesched.Description FeeSched, definition.ItemName AdjType," +
" CONCAT(CONCAT(CONCAT(CONCAT(patient.LName,', '),patient.FName),' '),patient.MiddleI) PatientName" +
" FROM discountplan" +
" INNER JOIN feesched ON feesched.FeeSchedNum=discountplan.FeeSchedNum" +
" INNER JOIN definition ON definition.DefNum=discountplan.DefNum" +
" INNER JOIN patient ON patient.DiscountPlanNum=discountplan.DiscountPlanNum" +
" WHERE discountplan.Description LIKE '%" + POut.String(description) + "%'" +
" ORDER BY discountplan.Description,patient.LName,patient.FName,patient.MiddleI";
return(ReportsComplex.RunFuncOnReportServer(() => ReportsComplex.GetTable(query)));
}
///<summary></summary>
public static DataTable GetReferralTable(DateTime dateStart, DateTime dateEnd, List <long> listProvNums
, bool hasAddress, bool hasOnlyNewPats)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateStart, dateEnd, listProvNums, hasAddress, hasOnlyNewPats));
}
string whereProv = "";
if (listProvNums.Count > 0)
{
whereProv += " AND procedurelog.ProvNum IN(" + string.Join(",", listProvNums) + ") ";
}
string query = @"SELECT referral.LName,referral.FName,
COUNT(DISTINCT attach.PatNum) AS HowMany,
SUM(procedurelog.ProcFee*(procedurelog.UnitQty+procedurelog.BaseUnits)) AS '$HowMuch'" ;
if (hasAddress)
{
query += ",referral.Title,referral.Address,referral.Address2,referral.City,referral.ST,referral.Zip,definition.ItemName AS Specialty";
}
query += " FROM referral"
+ " INNER JOIN("
+ " SELECT refattach.ReferralNum, refattach.PatNum FROM refattach"
+ " WHERE refattach.RefType=" + POut.Int((int)ReferralType.RefFrom)
+ " GROUP BY refattach.PatNum, refattach.ReferralNum"
+ ") attach ON attach.ReferralNum = referral.ReferralNum"
+ " INNER JOIN procedurelog ON procedurelog.PatNum=attach.PatNum"
+ " AND procedurelog.ProcDate BETWEEN " + POut.Date(dateStart) + " AND " + POut.Date(dateEnd)
+ " AND procedurelog.ProcStatus=" + POut.Int((int)ProcStat.C)
+ " INNER JOIN procedurecode ON procedurecode.CodeNum=procedurelog.CodeNum"
+ " AND ProcCode NOT IN ('D9986','D9987')" //Do not count missed or canceled appointments
+ " " + whereProv
+ " INNER JOIN patient ON patient.PatNum=procedurelog.PatNum";
if (hasOnlyNewPats)
{
query += " AND patient.DateFirstVisit BETWEEN " + POut.Date(dateStart) + " AND " + POut.Date(dateEnd);
}
if (hasAddress)
{
query += " LEFT JOIN definition ON referral.Specialty=definition.DefNum";
}
query += " GROUP BY referral.ReferralNum"
+ " ORDER BY HowMany Desc";
return(ReportsComplex.RunFuncOnReportServer(() => ReportsComplex.GetTable(query)));
}
public static DataTable GetGroupedTable(DateTime dateFrom, DateTime dateTo, List <long> listProvNums, List <long> listClinicNums, string procCode, bool hasAllProvs)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateFrom, dateTo, listProvNums, listClinicNums, procCode, hasAllProvs));
}
string query = "SELECT procs.ItemName,procs.ProcCode,procs.Descript,";
if (DataConnection.DBtype == DatabaseType.MySql)
{
query += "Count(*),FORMAT(ROUND(AVG(procs.fee),2),2) $AvgFee,SUM(procs.fee) AS $TotFee ";
}
else //Oracle needs quotes.
{
query += "Count(*),AVG(procs.fee) \"$AvgFee\",SUM(procs.fee) AS \"$TotFee\" ";
}
query += "FROM ( "
+ "SELECT procedurelog.ProcFee*(procedurelog.UnitQty+procedurelog.BaseUnits) -COALESCE(SUM(claimproc.WriteOff),0) fee, "
+ "procedurecode.ProcCode, procedurecode.Descript, definition.ItemName, definition.ItemOrder "
+ "FROM procedurelog "
+ "INNER JOIN procedurecode ON procedurelog.CodeNum=procedurecode.CodeNum "
+ "INNER JOIN definition ON definition.DefNum=procedurecode.ProcCat "
+ "LEFT JOIN claimproc ON claimproc.ProcNum=procedurelog.ProcNum AND claimproc.Status=" + POut.Int((int)ClaimProcStatus.CapComplete) + " "
+ "WHERE procedurelog.ProcStatus=" + POut.Int((int)ProcStat.C) + " ";
if (!hasAllProvs)
{
query += "AND procedurelog.ProvNum IN (" + String.Join(",", listProvNums) + ") ";
}
if (ReportsComplex.RunFuncOnReportServer(() => (!Prefs.GetBoolNoCache(PrefName.EasyNoClinics))))
{
query += "AND procedurelog.ClinicNum IN (" + String.Join(",", listClinicNums) + ") ";
}
query += "AND procedurecode.ProcCode LIKE '%" + POut.String(procCode) + "%' "
+ "AND procedurelog.ProcDate >= " + POut.Date(dateFrom) + " "
+ "AND procedurelog.ProcDate <= " + POut.Date(dateTo) + " "
+ "GROUP BY procedurelog.ProcNum ) procs "
+ "GROUP BY procs.ProcCode "
+ "ORDER BY procs.ItemOrder,procs.ProcCode";
return(ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(query)));
}
///<summary></summary>
public static DataTable GetInsCoTable(string carrier)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), carrier));
}
string query = "SELECT carrier.CarrierName"
+ ",CONCAT(CONCAT(CONCAT(CONCAT(patient.LName,', '),patient.FName),' '),patient.MiddleI) AS SubscriberName,carrier.Phone,"
+ "insplan.Groupname "
+ "FROM insplan,inssub,patient,carrier " //,patplan "//we only include patplan to make sure insurance is active for a patient. We don't want any info from patplan.
+ "WHERE inssub.Subscriber=patient.PatNum "
+ "AND inssub.PlanNum=insplan.PlanNum "
+ "AND EXISTS (SELECT * FROM patplan WHERE patplan.InsSubNum=inssub.InsSubNum) "
//+"AND insplan.PlanNum=patplan.PlanNum "
//+"AND patplan.PatNum=patient.PatNum "
//+"AND patplan.Ordinal=1 "
+ "AND carrier.CarrierNum=insplan.CarrierNum "
+ "AND carrier.CarrierName LIKE '" + POut.String(carrier) + "%' "
+ "ORDER BY carrier.CarrierName,patient.LName";
return(ReportsComplex.RunFuncOnReportServer(() => ReportsComplex.GetTable(query)));
}
///<summary>Gets a list of aptNums for one day in the schedule for a given set of providers and clinics. Will be for all clinics and/or all provs
///if the corresponding list is null or empty.</summary>
public static List <long> GetRouting(DateTime date, List <long> listProvNums, List <long> listClinicNums)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <long> >(MethodBase.GetCurrentMethod(), date, listProvNums, listClinicNums));
}
//Excluding PtNote and PtNoteCompleted per Nathan and Arna, see job 1064
string command = "SELECT AptNum FROM appointment "
+ "WHERE " + DbHelper.DateTConditionColumn("AptDateTime", ConditionOperator.Equals, date) + " "
+ "AND AptStatus NOT IN (" + POut.Int((int)ApptStatus.UnschedList) + "," + POut.Int((int)ApptStatus.Planned) + "," + POut.Int((int)ApptStatus.PtNote) + ","
+ POut.Int((int)ApptStatus.PtNoteCompleted) + ") ";
if (listProvNums != null && listProvNums.Count > 0)
{
command += "AND (ProvNum IN (" + string.Join(",", listProvNums) + ") OR ProvHyg IN (" + string.Join(",", listProvNums) + ")) ";
}
if (listClinicNums != null && listClinicNums.Count > 0)
{
command += "AND ClinicNum IN (" + string.Join(",", listClinicNums) + ") ";
}
command += "ORDER BY AptDateTime";
return(ReportsComplex.RunFuncOnReportServer(() => Db.GetListLong(command)));
}
///<summary></summary>
public static DataTable GetPrescriptionTable(bool isRadioPatient, string inputText)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), isRadioPatient, inputText));
}
string query = "SELECT CONCAT(CONCAT(CONCAT(CONCAT(patient.LName,', '),patient.FName)," +
"' '),patient.MiddleI),rxpat.rxdate,"
+ "rxpat.drug,rxpat.sig,rxpat.disp,provider.abbr FROM patient,rxpat,provider "
+ "WHERE patient.patnum=rxpat.patnum AND provider.provnum=rxpat.provnum ";
if (isRadioPatient)
{
query += "AND patient.lname like '" + POut.String(inputText) + "%'"
+ " ORDER BY patient.lname,patient.fname,rxpat.rxdate";
}
else
{
query += "AND rxpat.drug like '" + POut.String(inputText) + "%'"
+ " ORDER BY patient.lname,rxpat.drug,rxpat.rxdate";
}
return(ReportsComplex.RunFuncOnReportServer(() => ReportsComplex.GetTable(query)));
}
public static DataTable GetBirthdayTable(DateTime dateFrom, DateTime dateTo)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateFrom, dateTo));
}
string dateWhereClause;
string orderByClause;
if (dateFrom.Year == dateTo.Year)
{
dateWhereClause = "SUBSTRING(Birthdate,6,5) >= '" + dateFrom.ToString("MM-dd") + "' "
+ "AND SUBSTRING(Birthdate,6,5) <= '" + dateTo.ToString("MM-dd") + "' ";
orderByClause = "MONTH(Birthdate),DAY(Birthdate)";
}
else //The date range spans more than 1 calendar year
{
dateWhereClause = "(SUBSTRING(Birthdate,6,5) >= '" + dateFrom.ToString("MM-dd") + "' "
+ "OR SUBSTRING(Birthdate,6,5) <= '" + dateTo.ToString("MM-dd") + "') ";
orderByClause = "SUBSTRING(Birthdate,6,5) < '" + dateFrom.ToString("MM-dd") + "',MONTH(Birthdate),DAY(Birthdate)";
}
string command = "SELECT LName,FName,Preferred,Address,Address2,City,State,Zip,Birthdate "
+ "FROM patient "
+ "WHERE " + dateWhereClause + " "
+ "AND Birthdate > '1880-01-01' "
+ "AND PatStatus=0 "
+ "ORDER BY " + orderByClause;
DataTable table = ReportsComplex.RunFuncOnReportServer(() => ReportsComplex.GetTable(command));
table.Columns.Add("Age");
for (int i = 0; i < table.Rows.Count; i++)
{
table.Rows[i]["Age"] = Patients.DateToAge(PIn.Date(table.Rows[i]["Birthdate"].ToString()), dateTo).ToString();
}
return(table);
}
///<summary>If the user has opted to age credits and debits for payment plans, all payments are included.
///Otherwise, only payments not attached to payment plans are included.
///This is determined by the user's choice for this particular report, NOT their practice-wide preference.</summary>
private static string GetPatPayAgingQuery(AgingOptions ageOptions)
{
string command = @"
SELECT 'PatPay' TranType,ps.PatNum,ps.DatePay TranDate,-ps.SplitAmt TranAmount
FROM paysplit ps
WHERE ps.SplitAmt != 0
AND ps.DatePay <= " + POut.Date(ageOptions.DateAsOf) + " ";
if (!ageOptions.AgingInc.HasFlag(AgingOptions.AgingInclude.PayPlanCharges) ||
!ageOptions.AgingInc.HasFlag(AgingOptions.AgingInclude.PayPlanCredits))
{
command += @"
AND ps.PayPlanNum = 0 " ;
}
List <long> listHiddenUnearnedDefNums = ReportsComplex.RunFuncOnReportServer(() =>
Defs.GetDefsNoCache(DefCat.PaySplitUnearnedType).Where(x => !string.IsNullOrEmpty(x.ItemValue)).Select(x => x.DefNum).ToList()
);
if (listHiddenUnearnedDefNums.Count > 0)
{
command += "AND ps.UnearnedType NOT IN (" + string.Join(",", listHiddenUnearnedDefNums) + ") ";
}
return(command);
}
