///<summary>Clears all schedule entries for the given date range and the given providers, employees, and practice.</summary>
public static void Clear(DateTime dateStart, DateTime dateEnd, List <long> provNums, List <long> empNums, bool includePractice)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), dateStart, dateEnd, provNums, empNums, includePractice);
return;
}
if (provNums.Count == 0 && empNums.Count == 0 && !includePractice)
{
return;
}
string command;
string orClause = "";
//make deleted entries for synch purposes:
if (provNums.Count > 0)
{
for (int i = 0; i < provNums.Count; i++)
{
if (orClause != "")
{
orClause += "OR ";
}
orClause += "schedule.ProvNum=" + POut.Long(provNums[i]) + " ";
}
command = "SELECT ScheduleNum FROM schedule "
+ "WHERE SchedDate >= " + POut.Date(dateStart) + " "
+ "AND SchedDate <= " + POut.Date(dateEnd) + " "
+ "AND SchedType=" + POut.Long((int)ScheduleType.Provider)
+ " AND (" + orClause + ")";
DataTable table = Db.GetTable(command);
for (int i = 0; i < table.Rows.Count; i++)
{
DeletedObjects.SetDeleted(DeletedObjectType.ScheduleProv, PIn.Long(table.Rows[i][0].ToString()));
}
}
//Then, the usual deletion for everything
command = "DELETE FROM schedule "
+ "WHERE SchedDate >= " + POut.Date(dateStart) + " "
+ "AND SchedDate <= " + POut.Date(dateEnd) + " "
+ "AND (";
orClause = ""; //this is guaranteed to be non empty by the time the command is assembled.
if (includePractice)
{
orClause = "SchedType=0 ";
}
for (int i = 0; i < provNums.Count; i++)
{
if (orClause != "")
{
orClause += "OR ";
}
orClause += "schedule.ProvNum=" + POut.Long(provNums[i]) + " ";
}
for (int i = 0; i < empNums.Count; i++)
{
if (orClause != "")
{
orClause += "OR ";
}
orClause += "schedule.EmployeeNum=" + POut.Long(empNums[i]) + " ";
}
command += orClause + ")";
Db.NonQ(command);
}
///<summary>Gets the DataTable to display for treatment finder report</summary>
///<param name="listProviders">Include '0' in the list to get for all providers.</param>
///<param name="listBilling">Include '0' in the list to get for all billing types.</param>
///<param name="listClinicNums">Pass in an empty list to get for all clinics.</param>
public static DataTable GetTreatmentFinderList(bool noIns, bool patsWithAppts, int monthStart, DateTime dateSince, double aboveAmount,
List <long> listProviders, List <long> listBilling, string code1, string code2, List <long> listClinicNums, bool isProcsGeneral)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), noIns, patsWithAppts, monthStart, dateSince, aboveAmount, listProviders, listBilling, code1, code2,
listClinicNums, isProcsGeneral));
}
#if DEBUG
Stopwatch sw = Stopwatch.StartNew();
#endif
DataTable table = new DataTable();
DataRow row;
//columns that start with lowercase are altered for display rather than being raw data.
table.Columns.Add("PatNum");
table.Columns.Add("LName");
table.Columns.Add("FName");
table.Columns.Add("contactMethod");
table.Columns.Add("address");
table.Columns.Add("City");
table.Columns.Add("State");
table.Columns.Add("Zip");
table.Columns.Add("annualMaxInd");
table.Columns.Add("annualMaxFam");
table.Columns.Add("amountUsedInd");
table.Columns.Add("amountUsedFam");
table.Columns.Add("amountPendingInd");
table.Columns.Add("amountPendingFam");
table.Columns.Add("amountRemainingInd");
table.Columns.Add("amountRemainingFam");
table.Columns.Add("treatmentPlan");
table.Columns.Add("carrierName");
table.Columns.Add("clinicAbbr");
List <DataRow> rows = new List <DataRow>();
string command = "";
string joinAnnualMax = "";
string joinCoverageInfo = "";
string joinIndInfo = "";
string joinFamInfo = "";
string subSelectPlanned = "";
string cmdFutureApt = @" AND patient.PatNum NOT IN (
SELECT PatNum FROM appointment WHERE AptStatus=" + POut.Int((int)ApptStatus.Scheduled) + @"
AND AptDateTime>=" + DbHelper.Curdate() + ")";
DateTime renewDate = BenefitLogic.ComputeRenewDate(DateTime.Now, monthStart);
List <long> listPatNums = new List <long>();
if ((!listProviders.Contains(0) || !listBilling.Contains(0) || listClinicNums.Count > 0))
{
string cmdPatients = "SELECT PatNum from patient ";
string patWhere = "";
if (!listProviders.Contains(0))
{
patWhere += " AND patient.PriProv IN (" + string.Join(",", listProviders) + ") ";
}
if (!listBilling.Contains(0))
{
patWhere += " AND patient.BillingType IN (" + string.Join(",", listBilling) + ") ";
}
if (listClinicNums.Count > 0)
{
patWhere += " AND patient.ClinicNum IN (" + string.Join(",", listClinicNums) + ") ";
}
if (!patsWithAppts)
{
patWhere += cmdFutureApt;
}
cmdPatients += "WHERE TRUE " + patWhere;
listPatNums = Db.GetListLong(cmdPatients);
if (listPatNums.Count == 0)
{
return(table);
}
}
joinCoverageInfo = @"
SELECT patplan.PatPlanNum,claimproc.InsSubNum,
SUM(CASE WHEN claimproc.Status=" + POut.Int((int)ClaimProcStatus.NotReceived) + @" AND claimproc.InsPayAmt=0
THEN claimproc.InsPayEst ELSE 0 END) AmtPending,
SUM(CASE WHEN claimproc.Status IN (" + POut.Int((int)ClaimProcStatus.Received) + ","
+ POut.Int((int)ClaimProcStatus.Adjustment) + ","
+ POut.Int((int)ClaimProcStatus.Supplemental) + @"
) THEN claimproc.InsPayAmt ELSE 0 END) AmtUsed
FROM claimproc
INNER JOIN patient ON patient.PatNum=claimproc.PatNum
LEFT JOIN patplan ON patplan.PatNum=claimproc.PatNum
AND patplan.InsSubNum=claimproc.InsSubNum
LEFT JOIN procedurelog pl ON pl.ProcNum=claimproc.ProcNum
LEFT JOIN procedurecode pc ON pc.CodeNum=pl.CodeNum " ;
if (!isProcsGeneral)
{
joinCoverageInfo += @"
LEFT JOIN (
SELECT isub.InsSubNum,
COALESCE(cp.FromCode,pc.ProcCode) AS FromCode,
COALESCE(cp.ToCode,pc.ProcCode) AS ToCode
FROM inssub isub
INNER JOIN benefit b ON b.PlanNum=isub.PlanNum
AND b.BenefitType=" + (int)InsBenefitType.Limitations + @"
AND b.QuantityQualifier=" + (int)BenefitQuantity.None + @"
AND b.TimePeriod IN (" + (int)BenefitTimePeriod.ServiceYear + "," + (int)BenefitTimePeriod.CalendarYear + @")
LEFT JOIN covcat cc ON cc.CovCatNum=b.CovCatNum
LEFT JOIN covspan cp ON cp.CovCatNum=cc.CovCatNum
LEFT JOIN procedurecode pc ON pc.CodeNum=b.CodeNum
WHERE (cc.CovCatNum IS NOT NULL OR b.CodeNum!=0)
)ProcCheck ON ProcCheck.InsSubNum=claimproc.InsSubNum
AND pc.ProcCode BETWEEN ProcCheck.FromCode AND ProcCheck.ToCode " ;
}
joinCoverageInfo += "WHERE claimproc.Status IN (" + (int)ClaimProcStatus.NotReceived + ", " + (int)ClaimProcStatus.Received
+ ", " + (int)ClaimProcStatus.Adjustment + ", " + (int)ClaimProcStatus.Supplemental + ") ";
if (!isProcsGeneral)
{
joinCoverageInfo += "AND ProcCheck.InsSubNum IS NULL ";
}
joinCoverageInfo += "AND claimproc.ProcDate BETWEEN " + POut.Date(renewDate) + @" AND " + POut.Date(renewDate.AddYears(1)) + @" ";
if (listPatNums.Count > 0)
{
joinCoverageInfo += @"AND patient.PatNum IN (" + string.Join(",", listPatNums) + ") ";
}
else if (!patsWithAppts)
{
joinCoverageInfo += cmdFutureApt;
}
joinIndInfo = joinCoverageInfo + " GROUP BY patplan.PatPlanNum ";
joinFamInfo = joinCoverageInfo + " GROUP BY claimproc.InsSubNum ";
subSelectPlanned = @"
(SELECT COALESCE(SUM(ProcFee),0) AmtPlanned
FROM procedurelog " ;
if (code1 != "")
{
subSelectPlanned += "INNER JOIN procedurecode ON procedurecode.CodeNum=procedurelog.CodeNum ";
}
subSelectPlanned += "WHERE ProcStatus=" + (int)ProcStat.TP + " ";
if (code1 != "")
{
subSelectPlanned += "AND procedurecode.ProcCode>='" + POut.String(code1) + "' "
+ " AND procedurecode.ProcCode<='" + POut.String(code2) + "' ";
}
if (dateSince.Year > 1880)
{
subSelectPlanned += "AND procedurelog.DateTP>=" + POut.DateT(dateSince) + " ";
}
subSelectPlanned += "AND PatNum=patient.PatNum ";
subSelectPlanned += "GROUP BY PatNum) ";
joinAnnualMax = @"
SELECT insplan.PlanNum, MAX(CASE WHEN CoverageLevel!=" + POut.Int((int)BenefitCoverageLevel.Family) + @"
THEN MonetaryAmt ELSE -1 END) AnnualMaxInd/*for oracle in case there's more than one*/,
MAX(CASE WHEN CoverageLevel=" + POut.Int((int)BenefitCoverageLevel.Family) + @"
THEN MonetaryAmt ELSE -1 END) AnnualMaxFam/*for oracle in case there's more than one*/
FROM benefit
INNER JOIN insplan ON insplan.PlanNum=benefit.PlanNum
INNER JOIN inssub ON inssub.PlanNum=benefit.PlanNum
INNER JOIN patplan ON patplan.InsSubNum=inssub.InsSubNum
INNER JOIN patient ON patient.PatNum=patplan.PatNum
LEFT JOIN covcat ON benefit.CovCatNum=covcat.CovCatNum
WHERE (covcat.EbenefitCat=" + (int)EbenefitCategory.General + @" OR ISNULL(covcat.EbenefitCat))
AND benefit.BenefitType=" + (int)InsBenefitType.Limitations + @"
AND benefit.MonetaryAmt > 0
AND benefit.QuantityQualifier=" + (int)BenefitQuantity.None + " ";
if (listPatNums.Count > 0)
{
joinAnnualMax += @"AND patient.PatNum IN (" + string.Join(",", listPatNums) + ") ";
}
else if (!patsWithAppts)
{
joinAnnualMax += cmdFutureApt;
}
joinAnnualMax += @"GROUP BY insplan.PlanNum";
command = @"SELECT patient.PatNum, patient.LName, patient.FName,
patient.Email, patient.HmPhone, patient.PreferRecallMethod,
patient.WirelessPhone, patient.WkPhone, patient.Address,
patient.Address2, patient.City, patient.State, patient.Zip,
patient.PriProv, patient.BillingType,
COALESCE(annualMax.AnnualMaxInd,0) ""AnnualMaxInd"",
COALESCE(annualMax.AnnualMaxFam,0) ""AnnualMaxFam"",
IndividualInfo.AmtUsed ""AmountUsedInd"",
FamilyInfo.AmtUsed ""AmountUsedFam"",
IndividualInfo.AmtPending ""AmountPendingInd"",
FamilyInfo.AmtPending ""AmountPendingFam"",
COALESCE(annualMax.AnnualMaxInd,0)-COALESCE(IndividualInfo.AmtUsed,0)-COALESCE(IndividualInfo.AmtPending,0) AS ""$AmtRemainingInd"",
COALESCE(annualMax.AnnualMaxFam,0)-COALESCE(FamilyInfo.AmtUsed,0)-COALESCE(FamilyInfo.AmtPending,0) AS ""$AmtRemainingFam""," +
subSelectPlanned + @"""$TreatmentPlan"", carrier.CarrierName,COALESCE(clinic.Abbr,'Unassigned') clinicAbbr
FROM patient
LEFT JOIN patplan ON patient.PatNum=patplan.PatNum
LEFT JOIN inssub ON patplan.InsSubNum=inssub.InsSubNum
LEFT JOIN insplan ON insplan.PlanNum=inssub.PlanNum
LEFT JOIN carrier ON insplan.CarrierNum=carrier.CarrierNum
LEFT JOIN ("
+ joinIndInfo
+ @")IndividualInfo ON IndividualInfo.PatPlanNum=patplan.PatPlanNum
LEFT JOIN ("
+ joinFamInfo
+ @")FamilyInfo ON FamilyInfo.InsSubNum=inssub.InsSubNum
LEFT JOIN ("
+ joinAnnualMax
+ @") annualMax ON annualMax.PlanNum=inssub.PlanNum
AND (annualMax.AnnualMaxInd>0 OR annualMax.AnnualMaxFam>0)/*may not be necessary*/
LEFT JOIN clinic ON clinic.ClinicNum=patient.ClinicNum
WHERE TRUE
AND patient.PatStatus=" + POut.Int((int)PatientStatus.Patient) + " ";
if (!noIns) //if we don't want patients without insurance
{
command += " AND patplan.Ordinal=1 AND insplan.MonthRenew=" + POut.Int(monthStart) + " ";
}
if (aboveAmount > 0)
{
command += " AND (annualMax.PlanNum IS NULL OR ((annualMax.AnnualMaxInd=-1 OR annualMax.AnnualMaxInd-COALESCE(IndividualInfo.AmtUsed,0) > "
+ POut.Double(aboveAmount) + @")
AND (annualMax.AnnualMaxFam=-1 OR annualMax.AnnualMaxFam-COALESCE(FamilyInfo.AmtUsed,0) > " + POut.Double(aboveAmount) + "))) ";
}
if (listPatNums.Count > 0)
{
command += " AND patient.PatNum IN (" + string.Join(",", listPatNums) + ") ";
}
else if (!patsWithAppts)
{
command += cmdFutureApt;
}
command += @"HAVING $TreatmentPlan > 0 ";
command += @"ORDER BY $TreatmentPlan DESC";
DataTable rawtable = Db.GetTable(command);
#if DEBUG
sw.Stop();
Console.WriteLine("Finishing retreiving query: {0}", sw.ElapsedMilliseconds);
sw = Stopwatch.StartNew();
#endif
ContactMethod contmeth;
for (int i = 0; i < rawtable.Rows.Count; i++)
{
row = table.NewRow();
row["PatNum"] = PIn.Long(rawtable.Rows[i]["PatNum"].ToString());
row["LName"] = rawtable.Rows[i]["LName"].ToString();
row["FName"] = rawtable.Rows[i]["FName"].ToString();
contmeth = (ContactMethod)PIn.Long(rawtable.Rows[i]["PreferRecallMethod"].ToString());
if (contmeth == ContactMethod.None)
{
if (PrefC.GetBool(PrefName.RecallUseEmailIfHasEmailAddress)) //if user only wants to use email if contact method is email
{
if (rawtable.Rows[i]["Email"].ToString() != "")
{
row["contactMethod"] = rawtable.Rows[i]["Email"].ToString();
}
else
{
row["contactMethod"] = Lans.g("FormRecallList", "Hm:") + rawtable.Rows[i]["HmPhone"].ToString();
}
}
else
{
row["contactMethod"] = Lans.g("FormRecallList", "Hm:") + rawtable.Rows[i]["HmPhone"].ToString();
}
}
else if (contmeth == ContactMethod.HmPhone)
{
row["contactMethod"] = Lans.g("FormRecallList", "Hm:") + rawtable.Rows[i]["HmPhone"].ToString();
}
else if (contmeth == ContactMethod.WkPhone)
{
row["contactMethod"] = Lans.g("FormRecallList", "Wk:") + rawtable.Rows[i]["WkPhone"].ToString();
}
else if (contmeth == ContactMethod.WirelessPh)
{
row["contactMethod"] = Lans.g("FormRecallList", "Cell:") + rawtable.Rows[i]["WirelessPhone"].ToString();
}
else if (contmeth == ContactMethod.Email)
{
row["contactMethod"] = rawtable.Rows[i]["Email"].ToString();
}
else if (contmeth == ContactMethod.Mail)
{
row["contactMethod"] = Lans.g("FormRecallList", "Mail");
}
else if (contmeth == ContactMethod.DoNotCall || contmeth == ContactMethod.SeeNotes)
{
row["contactMethod"] = Lans.g("enumContactMethod", contmeth.ToString());
}
row["address"] = rawtable.Rows[i]["Address"].ToString();
if (rawtable.Rows[i]["Address2"].ToString() != "")
{
row["address"] += "\r\n" + rawtable.Rows[i]["Address2"].ToString();
}
row["City"] = rawtable.Rows[i]["City"].ToString();
row["State"] = rawtable.Rows[i]["State"].ToString();
row["Zip"] = rawtable.Rows[i]["Zip"].ToString();
row["annualMaxInd"] = (PIn.Double(rawtable.Rows[i]["AnnualMaxInd"].ToString())).ToString("N");
row["annualMaxFam"] = (PIn.Double(rawtable.Rows[i]["AnnualMaxFam"].ToString())).ToString("N");
row["amountUsedInd"] = (PIn.Double(rawtable.Rows[i]["AmountUsedInd"].ToString())).ToString("N");
row["amountUsedFam"] = (PIn.Double(rawtable.Rows[i]["AmountUsedFam"].ToString())).ToString("N");
row["amountPendingInd"] = (PIn.Double(rawtable.Rows[i]["AmountPendingInd"].ToString())).ToString("N");
row["amountPendingFam"] = (PIn.Double(rawtable.Rows[i]["AmountPendingFam"].ToString())).ToString("N");
row["amountRemainingInd"] = (PIn.Double(rawtable.Rows[i]["$AmtRemainingInd"].ToString())).ToString("N");
row["amountRemainingFam"] = (PIn.Double(rawtable.Rows[i]["$AmtRemainingFam"].ToString())).ToString("N");
row["treatmentPlan"] = (PIn.Double(rawtable.Rows[i]["$TreatmentPlan"].ToString())).ToString("N");
row["carrierName"] = rawtable.Rows[i]["CarrierName"].ToString();
row["clinicAbbr"] = rawtable.Rows[i]["clinicAbbr"].ToString();
rows.Add(row);
}
for (int i = 0; i < rows.Count; i++)
{
table.Rows.Add(rows[i]);
}
#if DEBUG
sw.Stop();
Console.WriteLine("Finished Filling query result: {0}", sw.ElapsedMilliseconds);
#endif
return(table);
}
///<summary>Used only from FormReferenceSelect to get the list of references.</summary>
public static DataTable GetReferenceTable(bool limit, long[] billingTypes, bool showBadRefs, bool showUsed, bool showGuarOnly, string city, string state, string zip,
string areaCode, string specialty, int superFam, string lname, string fname, string patnum, int age, string country)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), limit, billingTypes, showBadRefs, showUsed, showGuarOnly, city, state, zip, areaCode, specialty, superFam, lname, fname, patnum, age, country));
}
string billingSnippet = "";
if (billingTypes.Length != 0)
{
for (int i = 0; i < billingTypes.Length; i++)
{
if (i == 0)
{
billingSnippet += "AND (";
}
else
{
billingSnippet += "OR ";
}
billingSnippet += "BillingType=" + POut.Long(billingTypes[i]) + " ";
if (i == billingTypes.Length - 1)
{
billingSnippet += ") ";
}
}
}
string phonedigits = "";
for (int i = 0; i < areaCode.Length; i++)
{
if (Regex.IsMatch(areaCode[i].ToString(), "[0-9]"))
{
phonedigits = phonedigits + areaCode[i];
}
}
string regexp = "";
for (int i = 0; i < phonedigits.Length; i++)
{
if (i < 1)
{
regexp = "^[^0-9]?"; //Allows phone to start with "("
}
regexp += phonedigits[i] + "[^0-9]*";
}
DataTable table = new DataTable();
DataRow row;
//columns that start with lowercase are altered for display rather than being raw data.
table.Columns.Add("CustReferenceNum");
table.Columns.Add("PatNum");
table.Columns.Add("FName");
table.Columns.Add("LName");
table.Columns.Add("HmPhone");
table.Columns.Add("State");
table.Columns.Add("City");
table.Columns.Add("Zip");
table.Columns.Add("Country");
table.Columns.Add("Specialty");
table.Columns.Add("age");
table.Columns.Add("SuperFamily");
table.Columns.Add("DateMostRecent");
table.Columns.Add("TimesUsed");
table.Columns.Add("IsBadRef");
List <DataRow> rows = new List <DataRow>();
string command = @"SELECT cr.*,p.LName,p.FName,p.HmPhone,p.State,p.City,p.Zip,p.Birthdate,pf.FieldValue,
(SELECT COUNT(*) FROM patient tempp WHERE tempp.SuperFamily=p.SuperFamily AND tempp.SuperFamily<>0) AS SuperFamily,
(SELECT COUNT(*) FROM custrefentry tempcre WHERE tempcre.PatNumRef=cr.PatNum) AS TimesUsed,p.Country
FROM custreference cr
INNER JOIN patient p ON cr.PatNum=p.PatNum
LEFT JOIN patfield pf ON cr.PatNum=pf.PatNum AND pf.FieldName='Specialty'
WHERE TRUE " ; //This just makes the following AND statements brainless.
command += "AND (p.PatStatus=" + POut.Int((int)PatientStatus.Patient) + " OR p.PatStatus=" + POut.Int((int)PatientStatus.NonPatient) + ") " //excludes deleted, etc.
+ billingSnippet;
if (age > 0)
{
command += "AND p.Birthdate <" + POut.Date(DateTime.Now.AddYears(-age)) + " ";
}
if (regexp != "")
{
command += "AND (p.HmPhone REGEXP '" + POut.String(regexp) + "' )";
}
command += (lname.Length > 0?"AND (p.LName LIKE '" + POut.String(lname) + "%' OR p.Preferred LIKE '" + POut.String(lname) + "%') ":"")
+ (fname.Length > 0?"AND (p.FName LIKE '" + POut.String(fname) + "%' OR p.Preferred LIKE '" + POut.String(fname) + "%') ":"")
+ (city.Length > 0?"AND p.City LIKE '" + POut.String(city) + "%' ":"")
+ (state.Length > 0?"AND p.State LIKE '" + POut.String(state) + "%' ":"")
+ (zip.Length > 0?"AND p.Zip LIKE '" + POut.String(zip) + "%' ":"")
+ (country.Length > 0?"AND p.Country LIKE '" + POut.String(country) + "%' ":"")
+ (patnum.Length > 0?"AND p.PatNum LIKE '" + POut.String(patnum) + "%' ":"")
+ (specialty.Length > 0?"AND pf.FieldValue LIKE '" + POut.String(specialty) + "%' ":"")
+ (showBadRefs?"":"AND cr.IsBadRef=0 ")
+ (showGuarOnly?"AND p.Guarantor=p.PatNum ":"")
+ "HAVING TRUE "; //Once again just making AND statements brainless.
if (superFam > 0)
{
command += "AND SuperFamily>" + POut.Int(superFam) + " ";
}
if (showUsed)
{
command += "AND TimesUsed>0 ";
}
if (limit)
{
command = DbHelper.LimitOrderBy(command, 40);
}
DataTable rawtable = Db.GetTable(command);
for (int i = 0; i < rawtable.Rows.Count; i++)
{
row = table.NewRow();
row["CustReferenceNum"] = rawtable.Rows[i]["CustReferenceNum"].ToString();
row["PatNum"] = rawtable.Rows[i]["PatNum"].ToString();
row["FName"] = rawtable.Rows[i]["FName"].ToString();
row["LName"] = rawtable.Rows[i]["LName"].ToString();
row["HmPhone"] = rawtable.Rows[i]["HmPhone"].ToString();
row["State"] = rawtable.Rows[i]["State"].ToString();
row["City"] = rawtable.Rows[i]["City"].ToString();
row["Zip"] = rawtable.Rows[i]["Zip"].ToString();
row["Country"] = rawtable.Rows[i]["Country"].ToString();
row["Specialty"] = rawtable.Rows[i]["FieldValue"].ToString();
row["age"] = Patients.DateToAge(PIn.Date(rawtable.Rows[i]["Birthdate"].ToString())).ToString();
row["SuperFamily"] = rawtable.Rows[i]["SuperFamily"].ToString();
DateTime recentDate = PIn.DateT(rawtable.Rows[i]["DateMostRecent"].ToString());
row["DateMostRecent"] = "";
if (recentDate.Year > 1880)
{
row["DateMostRecent"] = recentDate.ToShortDateString();
}
row["TimesUsed"] = rawtable.Rows[i]["TimesUsed"].ToString();
row["IsBadRef"] = rawtable.Rows[i]["IsBadRef"].ToString();
rows.Add(row);
}
for (int i = 0; i < rows.Count; i++)
{
table.Rows.Add(rows[i]);
}
return(table);
}
///<summary>Returns a query string used to get adjustments for all patients who have an appointment in the date range and in one of the operatories
///passed in.</summary>
public static string GetQueryAdjustmentsForAppointments(DateTime dateStart, DateTime dateEnd, List <long> listOpNums, bool doGetSum)
{
//No need to check RemotingRole; no call to db.
if (listOpNums.IsNullOrEmpty())
{
return("SELECT " + (doGetSum ? "SUM(adjustment.AdjAmt)" : "*")
+ " FROM adjustment WHERE AdjDate BETWEEN " + POut.Date(dateStart) + " AND " + POut.Date(dateEnd) + " ");
}
string command = "SELECT "
+ (doGetSum ? "SUM(adjustment.AdjAmt)" : "*")
+ " FROM adjustment WHERE AdjDate BETWEEN " + POut.Date(dateStart) + " AND " + POut.Date(dateEnd)
+ " AND PatNum IN("
+ "SELECT PatNum FROM appointment "
+ "WHERE AptDateTime BETWEEN " + POut.Date(dateStart) + " AND " + POut.Date(dateEnd.AddDays(1))
+ "AND AptStatus IN (" + POut.Int((int)ApptStatus.Scheduled)
+ ", " + POut.Int((int)ApptStatus.Complete)
+ ", " + POut.Int((int)ApptStatus.Broken)
+ ", " + POut.Int((int)ApptStatus.PtNote)
+ ", " + POut.Int((int)ApptStatus.PtNoteCompleted) + ")"
+ " AND Op IN(" + string.Join(",", listOpNums) + ")) ";
return(command);
}
///<summary>Gets data for the history grid in the SendClaims window. The listEtransType must contain as least one item.</summary>
public static DataTable RefreshHistory(DateTime dateFrom,DateTime dateTo,List<EtransType> listEtransType) {
if(RemotingClient.RemotingRole==RemotingRole.ClientWeb) {
return Meth.GetTable(MethodBase.GetCurrentMethod(),dateFrom,dateTo,listEtransType);
}
string command="SELECT (CASE WHEN etrans.PatNum=0 THEN etrans.PatientNameRaw "
+"ELSE CONCAT(CONCAT(patient.LName,', '),patient.FName) END) AS PatName,"
+"(CASE WHEN etrans.carrierNum=0 THEN etrans.CarrierNameRaw ELSE carrier.CarrierName END) AS CarrierName,"
+"clearinghouse.Description AS Clearinghouse,DateTimeTrans,etrans.OfficeSequenceNumber,"
+"etrans.CarrierTransCounter,Etype,etrans.ClaimNum,etrans.EtransNum,etrans.AckCode,etrans.Note,etrans.EtransMessageTextNum,etrans.TranSetId835,"
+"etrans.UserNum,etrans.PatNum "
+"FROM etrans "
+"LEFT JOIN carrier ON etrans.CarrierNum=carrier.CarrierNum "
+"LEFT JOIN patient ON patient.PatNum=etrans.PatNum "
+"LEFT JOIN clearinghouse ON clearinghouse.ClearinghouseNum=etrans.ClearinghouseNum WHERE "
+DbHelper.DtimeToDate("DateTimeTrans")+" >= "+POut.Date(dateFrom)+" AND "
+DbHelper.DtimeToDate("DateTimeTrans")+" <= "+POut.Date(dateTo)+" "
+"AND Etype IN ("+POut.Long((int)listEtransType[0]);
for(int i=1;i<listEtransType.Count;i++){//String.Join doesn't work because there's no way to cast the enums to ints in the function, db uses longs.
command+=", "+POut.Long((int)listEtransType[i]);
}
command+=") "
//For Canada, when the undo button is used from Manage | Send Claims, the ClaimNum is set to 0 instead of deleting the etrans entry.
//For transaction types related to claims where the claimnum=0, we do not want them to show in the history section of Manage | Send Claims because they have been undone.
+"AND (ClaimNum<>0 OR Etype NOT IN ("+POut.Long((int)EtransType.Claim_CA)+","+POut.Long((int)EtransType.ClaimCOB_CA)+","+POut.Long((int)EtransType.Predeterm_CA)+","+POut.Long((int)EtransType.ClaimReversal_CA)+")) "
+"ORDER BY DateTimeTrans";
DataTable table=Db.GetTable(command);
DataTable tHist=new DataTable("Table");
tHist.Columns.Add("patName");
tHist.Columns.Add("CarrierName");
tHist.Columns.Add("Clearinghouse");
tHist.Columns.Add("dateTimeTrans");
tHist.Columns.Add("OfficeSequenceNumber");
tHist.Columns.Add("CarrierTransCounter");
tHist.Columns.Add("etype");
tHist.Columns.Add("Etype");
tHist.Columns.Add("ClaimNum");
tHist.Columns.Add("EtransNum");
tHist.Columns.Add("ack");
tHist.Columns.Add("Note");
tHist.Columns.Add("EtransMessageTextNum");
tHist.Columns.Add("TranSetId835");
tHist.Columns.Add("UserNum");
tHist.Columns.Add("PatNum");
DataRow row;
string etype;
for(int i=0;i<table.Rows.Count;i++) {
row=tHist.NewRow();
row["patName"]=table.Rows[i]["PatName"].ToString();
row["CarrierName"]=table.Rows[i]["CarrierName"].ToString();
row["Clearinghouse"]=table.Rows[i]["Clearinghouse"].ToString();
row["dateTimeTrans"]=PIn.DateT(table.Rows[i]["DateTimeTrans"].ToString()).ToShortDateString();
row["OfficeSequenceNumber"]=table.Rows[i]["OfficeSequenceNumber"].ToString();
row["CarrierTransCounter"]=table.Rows[i]["CarrierTransCounter"].ToString();
row["Etype"]=table.Rows[i]["Etype"].ToString();
etype=Lans.g("enumEtransType",((EtransType)PIn.Long(table.Rows[i]["Etype"].ToString())).ToString());
if(etype.EndsWith("_CA")){
etype=etype.Substring(0,etype.Length-3);
}
row["etype"]=etype;
row["ClaimNum"]=table.Rows[i]["ClaimNum"].ToString();
row["EtransNum"]=table.Rows[i]["EtransNum"].ToString();
if(table.Rows[i]["AckCode"].ToString()=="A"){
row["ack"]=Lans.g("Etrans","Accepted");
}
else if(table.Rows[i]["AckCode"].ToString()=="R") {
row["ack"]=Lans.g("Etrans","Rejected");
}
else if(table.Rows[i]["AckCode"].ToString()=="Recd") {
row["ack"]=Lans.g("Etrans","Received");
}
else {
row["ack"]="";
}
row["Note"]=table.Rows[i]["Note"].ToString();
row["EtransMessageTextNum"]=table.Rows[i]["EtransMessageTextNum"].ToString();
row["TranSetId835"]=table.Rows[i]["TranSetId835"].ToString();
row["UserNum"]=table.Rows[i]["UserNum"].ToString();
row["PatNum"]=table.Rows[i]["PatNum"].ToString();
tHist.Rows.Add(row);
}
return tHist;
}
///<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));
}
///<summary></summary>
public static DataTable GetOrderTable(long patNum, bool includeDiscontinued)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), patNum, includeDiscontinued));
}
DataTable table = new DataTable("orders");
DataRow row;
table.Columns.Add("date");
table.Columns.Add("DateTime", typeof(DateTime));
table.Columns.Add("description");
table.Columns.Add("MedicalOrderNum");
table.Columns.Add("MedicationPatNum");
table.Columns.Add("prov");
table.Columns.Add("status");
table.Columns.Add("type");
List <DataRow> rows = new List <DataRow>();
string command = "SELECT DateTimeOrder,Description,IsDiscontinued,MedicalOrderNum,MedOrderType,ProvNum "
+ "FROM medicalorder WHERE PatNum = " + POut.Long(patNum);
if (!includeDiscontinued) //only include current orders
{
command += " AND IsDiscontinued=0"; //false
}
DataTable rawOrder = Db.GetTable(command);
DateTime dateT;
MedicalOrderType medOrderType;
long medicalOrderNum;
bool isDiscontinued;
for (int i = 0; i < rawOrder.Rows.Count; i++)
{
row = table.NewRow();
dateT = PIn.DateT(rawOrder.Rows[i]["DateTimeOrder"].ToString());
medOrderType = (MedicalOrderType)PIn.Int(rawOrder.Rows[i]["MedOrderType"].ToString());
medicalOrderNum = PIn.Long(rawOrder.Rows[i]["MedicalOrderNum"].ToString());
row["DateTime"] = dateT;
row["date"] = dateT.ToShortDateString();
row["description"] = PIn.String(rawOrder.Rows[i]["Description"].ToString());
if (medOrderType == MedicalOrderType.Laboratory)
{
List <LabPanel> listPanelsForOrder = LabPanels.GetPanelsForOrder(medicalOrderNum);
for (int p = 0; p < listPanelsForOrder.Count; p++)
{
row["description"] += "\r\n "; //new row for each panel
List <LabResult> listResults = LabResults.GetForPanel(listPanelsForOrder[p].LabPanelNum);
if (listResults.Count > 0)
{
row["description"] += listResults[0].DateTimeTest.ToShortDateString() + " - ";
}
row["description"] += listPanelsForOrder[p].ServiceName;
}
}
row["MedicalOrderNum"] = medicalOrderNum.ToString();
row["MedicationPatNum"] = "0";
row["prov"] = Providers.GetAbbr(PIn.Long(rawOrder.Rows[i]["ProvNum"].ToString()));
isDiscontinued = PIn.Bool(rawOrder.Rows[i]["IsDiscontinued"].ToString());
if (isDiscontinued)
{
row["status"] = "Discontinued";
}
else
{
row["status"] = "Active";
}
row["type"] = medOrderType.ToString();
rows.Add(row);
}
//MedicationPats
command = "SELECT DateStart,DateStop,MedicationPatNum,MedName,PatNote,ProvNum "
+ "FROM medicationpat "
+ "LEFT JOIN medication ON medication.MedicationNum=medicationpat.MedicationNum "
+ "WHERE PatNum = " + POut.Long(patNum);
if (!includeDiscontinued) //exclude invalid orders
{
command += " AND DateStart > " + POut.Date(new DateTime(1880, 1, 1)) + " AND PatNote !='' "
+ "AND (DateStop < " + POut.Date(new DateTime(1880, 1, 1)) + " " //no date stop
+ "OR DateStop > " + POut.Date(DateTime.Today) + ")"; //date stop hasn't happened yet
}
DataTable rawMed = Db.GetTable(command);
DateTime dateStop;
for (int i = 0; i < rawMed.Rows.Count; i++)
{
row = table.NewRow();
dateT = PIn.DateT(rawMed.Rows[i]["DateStart"].ToString());
row["DateTime"] = dateT;
if (dateT.Year < 1880)
{
row["date"] = "";
}
else
{
row["date"] = dateT.ToShortDateString();
}
row["description"] = PIn.String(rawMed.Rows[i]["MedName"].ToString()) + ", "
+ PIn.String(rawMed.Rows[i]["PatNote"].ToString());
row["MedicalOrderNum"] = "0";
row["MedicationPatNum"] = rawMed.Rows[i]["MedicationPatNum"].ToString();
row["prov"] = Providers.GetAbbr(PIn.Long(rawMed.Rows[i]["ProvNum"].ToString()));
dateStop = PIn.Date(rawMed.Rows[i]["DateStop"].ToString());
if (dateStop.Year < 1880 || dateStop > DateTime.Today) //not stopped or in the future
{
row["status"] = "Active";
}
else
{
row["status"] = "Discontinued";
}
row["type"] = "Medication";
rows.Add(row);
}
//Sorting-----------------------------------------------------------------------------------------
rows.Sort(new MedicalOrderLineComparer());
for (int i = 0; i < rows.Count; i++)
{
table.Rows.Add(rows[i]);
}
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)));
}
public static DataTable GetSmsUsageLocal(List <long> listClinicNums, DateTime dateMonth, List <SmsPhone> listPhones)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), listClinicNums, dateMonth, listPhones));
}
#region Initialize retVal DataTable
string strNoActivePhones = "No Active Phones";
List <SmsPhone> listSmsPhones = listPhones.Where(x => x.ClinicNum.In(listClinicNums)).ToList();
DateTime dateStart = dateMonth.Date.AddDays(1 - dateMonth.Day); //remove time portion and day of month portion. Remainder should be midnight of the first of the month
DateTime dateEnd = dateStart.AddMonths(1); //This should be midnight of the first of the following month.
//This query builds the data table that will be filled from several other queries, instead of writing one large complex query.
//It is written this way so that the queries are simple to write and understand, and makes Oracle compatibility easier to maintain.
string command = @"SELECT
0 ClinicNum,
' ' PhoneNumber,
' ' CountryCode,
0 SentMonth,
0.0 SentCharge,
0.0 SentDiscount,
0.0 SentPreDiscount,
0 ReceivedMonth,
0.0 ReceivedCharge
FROM
DUAL" ; //this is a cute way to get a data table with the correct layout without having to query any real data.
DataTable retVal = Db.GetTable(command).Clone(); //use .Clone() to get schema only, with no rows.
retVal.TableName = "SmsUsageLocal";
for (int i = 0; i < listClinicNums.Count; i++)
{
DataRow row = retVal.NewRow();
row["ClinicNum"] = listClinicNums[i];
row["PhoneNumber"] = strNoActivePhones;
SmsPhone firstActivePhone = listSmsPhones
.Where(x => x.ClinicNum == listClinicNums[i]) //phones for this clinic
.Where(x => x.DateTimeInactive.Year < 1880) //that are active
.OrderByDescending(x => x.IsPrimary)
.ThenBy(x => x.DateTimeActive)
.FirstOrDefault();
if (firstActivePhone != null)
{
row["PhoneNumber"] = firstActivePhone.PhoneNumber;
row["CountryCode"] = firstActivePhone.CountryCode;
}
row["SentMonth"] = 0;
row["SentCharge"] = 0.0;
row["SentDiscount"] = 0.0;
row["SentPreDiscount"] = 0.0;
row["ReceivedMonth"] = 0;
row["ReceivedCharge"] = 0.0;
retVal.Rows.Add(row);
}
#endregion
#region Fill retVal DataTable
//Sent Last Month
command = "SELECT ClinicNum, COUNT(*), ROUND(SUM(MsgChargeUSD),2),ROUND(SUM(MsgDiscountUSD),2)"
+ ",SUM(CASE SmsPhoneNumber WHEN '" + POut.String(SmsPhones.SHORTCODE) + "' THEN 1 ELSE 0 END) FROM smstomobile "
+ "WHERE DateTimeSent >=" + POut.Date(dateStart) + " "
+ "AND DateTimeSent<" + POut.Date(dateEnd) + " "
+ "AND MsgChargeUSD>0 GROUP BY ClinicNum";
DataTable table = Db.GetTable(command);
for (int i = 0; i < table.Rows.Count; i++)
{
for (int j = 0; j < retVal.Rows.Count; j++)
{
if (retVal.Rows[j]["ClinicNum"].ToString() != table.Rows[i]["ClinicNum"].ToString())
{
continue;
}
retVal.Rows[j]["SentMonth"] = table.Rows[i][1]; //.ToString();
retVal.Rows[j]["SentCharge"] = table.Rows[i][2]; //.ToString();
retVal.Rows[j]["SentDiscount"] = table.Rows[i][3];
retVal.Rows[j]["SentPreDiscount"] = PIn.Double(retVal.Rows[j]["SentCharge"].ToString()) + PIn.Double(retVal.Rows[j]["SentDiscount"].ToString());
//No active phone but at least one of these messages sent from Short Code
if (retVal.Rows[j]["PhoneNumber"].ToString() == strNoActivePhones && PIn.Long(table.Rows[i][4].ToString()) > 0)
{
retVal.Rows[j]["PhoneNumber"] = POut.String(SmsPhones.SHORTCODE); //display "SHORTCODE" as primary number.
}
break;
}
}
//Received Month
command = "SELECT ClinicNum, COUNT(*),SUM(CASE SmsPhoneNumber WHEN '" + POut.String(SmsPhones.SHORTCODE) + "' THEN 1 ELSE 0 END) FROM smsfrommobile "
+ "WHERE DateTimeReceived >=" + POut.Date(dateStart) + " "
+ "AND DateTimeReceived<" + POut.Date(dateEnd) + " "
+ "GROUP BY ClinicNum";
table = Db.GetTable(command);
for (int i = 0; i < table.Rows.Count; i++)
{
for (int j = 0; j < retVal.Rows.Count; j++)
{
if (retVal.Rows[j]["ClinicNum"].ToString() != table.Rows[i]["ClinicNum"].ToString())
{
continue;
}
retVal.Rows[j]["ReceivedMonth"] = table.Rows[i][1].ToString();
retVal.Rows[j]["ReceivedCharge"] = "0";
//No active phone but at least one of these messages sent from Short Code
if (retVal.Rows[j]["PhoneNumber"].ToString() == strNoActivePhones && PIn.Long(table.Rows[i][2].ToString()) > 0)
{
retVal.Rows[j]["PhoneNumber"] = POut.String(SmsPhones.SHORTCODE); //display "SHORTCODE" as primary number.
}
break;
}
}
#endregion
return(retVal);
}
///<summary></summary>
public static DataTable GetWriteoffTable(DateTime dateStart, DateTime dateEnd, bool isIndividual, string carrierText, PPOWriteoffDateCalc writeoffType)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateStart, dateEnd, isIndividual, carrierText, writeoffType));
}
string queryText = "";
//individual
if (isIndividual)
{
queryText = "SET @DateFrom=" + POut.Date(dateStart) + ", @DateTo=" + POut.Date(dateEnd)
+ ", @CarrierName='%" + POut.String(carrierText) + "%';";
if (writeoffType == PPOWriteoffDateCalc.InsPayDate)
{
queryText += @"SELECT claimproc.DateCP,
CONCAT(CONCAT(CONCAT(CONCAT(patient.LName,', '),patient.FName),' '),patient.MiddleI),
carrier.CarrierName,
provider.Abbr,
SUM(claimproc.FeeBilled),
SUM(claimproc.FeeBilled-claimproc.WriteOff),
SUM(claimproc.WriteOff),
claimproc.ClaimNum
FROM claimproc
INNER JOIN insplan ON claimproc.PlanNum = insplan.PlanNum
AND insplan.PlanType='p'
INNER JOIN patient ON claimproc.PatNum = patient.PatNum
INNER JOIN carrier ON carrier.CarrierNum = insplan.CarrierNum
AND carrier.CarrierName LIKE @CarrierName
INNER JOIN provider ON provider.ProvNum = claimproc.ProvNum
WHERE claimproc.Status IN (" + (int)ClaimProcStatus.Received + "," + (int)ClaimProcStatus.Supplemental + @")
AND claimproc.DateCP >= @DateFrom
AND claimproc.DateCP <= @DateTo
GROUP BY claimproc.ClaimNum
ORDER BY claimproc.DateCP" ;
}
else if (writeoffType == PPOWriteoffDateCalc.ProcDate) //use procedure date
{
queryText += @"SELECT claimproc.ProcDate,
CONCAT(CONCAT(CONCAT(CONCAT(patient.LName,', '),patient.FName),' '),patient.MiddleI),
carrier.CarrierName,
provider.Abbr,
SUM(claimproc.FeeBilled),
SUM(claimproc.FeeBilled-claimproc.WriteOff),
SUM(claimproc.WriteOff),
claimproc.ClaimNum
FROM claimproc
INNER JOIN insplan ON claimproc.PlanNum = insplan.PlanNum
AND insplan.PlanType='p'
INNER JOIN patient ON claimproc.PatNum = patient.PatNum
INNER JOIN carrier ON carrier.CarrierNum = insplan.CarrierNum
AND carrier.CarrierName LIKE @CarrierName
INNER JOIN provider ON provider.ProvNum = claimproc.ProvNum
WHERE claimproc.Status IN (" + (int)ClaimProcStatus.Received + "," + (int)ClaimProcStatus.Supplemental + "," + (int)ClaimProcStatus.NotReceived + @")
AND claimproc.ProcDate >= @DateFrom
AND claimproc.ProcDate <= @DateTo
GROUP BY claimproc.ClaimNum
ORDER BY claimproc.ProcDate" ;
}
else //writeoffType==PPOWriteoffDateCalc.ClaimPayDate
{
queryText += @"SELECT claimsnapshot.DateTEntry,
CONCAT(CONCAT(CONCAT(CONCAT(patient.LName,', '),patient.FName),' '),patient.MiddleI),
carrier.CarrierName,
provider.Abbr,
SUM(claimproc.FeeBilled),
SUM(claimproc.FeeBilled-claimsnapshot.WriteOff),
SUM(claimsnapshot.WriteOff),
claimproc.ClaimNum
FROM claimproc
INNER JOIN insplan ON insplan.PlanNum=claimProc.PlanNum
AND insplan.PlanType='p'
INNER JOIN patient ON patient.PatNum=claimProc.PatNum
INNER JOIN carrier ON carrier.CarrierNum=insplan.CarrierNum
AND carrier.CarrierName LIKE @CarrierName
INNER JOIN provider ON provider.ProvNum=claimProc.ProvNum
INNER JOIN claimsnapshot ON claimsnapshot.ClaimProcNum=claimproc.ClaimProcNum
AND claimsnapshot.DateTEntry BETWEEN @DateFrom AND @DateTo
WHERE claimproc.Status IN (" + (int)ClaimProcStatus.Received + "," + (int)ClaimProcStatus.Supplemental + "," + (int)ClaimProcStatus.NotReceived + @")
GROUP BY claimproc.ClaimNum
ORDER BY claimsnapshot.DateTEntry" ;
}
}
else
{
//group
if (writeoffType == PPOWriteoffDateCalc.InsPayDate)
{
queryText = "SET @DateFrom=" + POut.Date(dateStart) + ", @DateTo=" + POut.Date(dateEnd)
+ ", @CarrierName='%" + POut.String(carrierText) + "%';"
+ @"SELECT carrier.CarrierName,
SUM(claimproc.FeeBilled),
SUM(claimproc.FeeBilled-claimproc.WriteOff),
SUM(claimproc.WriteOff),
claimproc.ClaimNum
FROM claimproc
INNER JOIN insplan ON claimproc.PlanNum = insplan.PlanNum
AND insplan.PlanType='p'
INNER JOIN carrier ON carrier.CarrierNum = insplan.CarrierNum
AND carrier.CarrierName LIKE @CarrierName
WHERE claimproc.Status IN (" + (int)ClaimProcStatus.Received + "," + (int)ClaimProcStatus.Supplemental + @")
AND claimproc.DateCP >= @DateFrom
AND claimproc.DateCP <= @DateTo
GROUP BY carrier.CarrierNum
ORDER BY carrier.CarrierName" ;
}
else if (writeoffType == PPOWriteoffDateCalc.ProcDate)
{
queryText = "SET @DateFrom=" + POut.Date(dateStart) + ", @DateTo=" + POut.Date(dateEnd)
+ ", @CarrierName='%" + POut.String(carrierText) + "%';"
+ @"SELECT carrier.CarrierName,
SUM(claimproc.FeeBilled),
SUM(claimproc.FeeBilled-claimproc.WriteOff),
SUM(claimproc.WriteOff),
claimproc.ClaimNum
FROM claimproc
INNER JOIN insplan ON claimproc.PlanNum = insplan.PlanNum
AND insplan.PlanType='p'
INNER JOIN carrier ON carrier.CarrierNum = insplan.CarrierNum
AND carrier.CarrierName LIKE @CarrierName
WHERE claimproc.Status IN (" + (int)ClaimProcStatus.Received + "," + (int)ClaimProcStatus.Supplemental + "," + (int)ClaimProcStatus.NotReceived + @")
AND claimproc.ProcDate >= @DateFrom
AND claimproc.ProcDate <= @DateTo
GROUP BY carrier.CarrierNum
ORDER BY carrier.CarrierName" ;
}
else // writeoffType==PPOWriteoffDateCalc.ClaimPayDate
{
queryText = "SET @DateFrom=" + POut.Date(dateStart) + ", @DateTo=" + POut.Date(dateEnd)
+ ", @CarrierName='%" + POut.String(carrierText) + "%';"
+ @"SELECT carrier.CarrierName,
SUM(claimproc.FeeBilled),
SUM(claimproc.FeeBilled-claimproc.WriteOff),
SUM(claimproc.WriteOff),
claimproc.ClaimNum
FROM claimproc
INNER JOIN insplan on claimproc.PlanNum = insplan.PlanNum
AND insplan.PlanType='p'
INNER JOIN carrier on carrier.CarrierNum = insplan.CarrierNum
AND carrier.CarrierName LIKE @CarrierName
INNER JOIN claimsnapshot on claimsnapshot.ClaimProcNum=claimproc.ClaimProcNum
AND claimsnapshot.DateTEntry >= @DateFrom
AND claimsnapshot.DateTEntry <= @DateTo
WHERE claimproc.Status IN (" + (int)ClaimProcStatus.Received + "," + (int)ClaimProcStatus.Supplemental + "," + (int)ClaimProcStatus.NotReceived + @")
GROUP BY carrier.CarrierNum
ORDER BY carrier.CarrierName" ;
}
}
return(ReportsComplex.RunFuncOnReportServer(() => ReportsComplex.GetTable(queryText)));
}
///<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);
}
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);
}
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);
}
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);
}
public static List <List <int> > GetProdInc(DateTime dateFrom, DateTime dateTo)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <List <int> > >(MethodBase.GetCurrentMethod(), dateFrom, dateTo));
}
#if DEBUG
_elapsedTimeProdInc = "";
System.Diagnostics.Stopwatch stopWatch = new System.Diagnostics.Stopwatch();
System.Diagnostics.Stopwatch stopWatchTotal = new System.Diagnostics.Stopwatch();
_elapsedTimeProdInc = "Elapsed time for GetProdInc:\r\n";
stopWatch.Restart();
stopWatchTotal.Restart();
#endif
string command;
command = @"SELECT procedurelog.ProcDate,
SUM(procedurelog.ProcFee*(procedurelog.UnitQty+procedurelog.BaseUnits))-IFNULL(SUM(claimproc.WriteOff),0)
FROM procedurelog
LEFT JOIN claimproc ON procedurelog.ProcNum=claimproc.ProcNum
AND claimproc.Status='7' /*only CapComplete writeoffs are subtracted here*/
WHERE procedurelog.ProcStatus = '2'
AND procedurelog.ProcDate >= " + POut.Date(dateFrom) + @"
AND procedurelog.ProcDate <= " + POut.Date(dateTo) + @"
GROUP BY MONTH(procedurelog.ProcDate)" ;
DataTable tableProduction = Db.GetTable(command);
#if DEBUG
stopWatch.Stop();
_elapsedTimeProdInc += "tableProduction: " + stopWatch.Elapsed.ToString() + "\r\n";
stopWatch.Restart();
#endif
command = @"SELECT AdjDate,
SUM(AdjAmt)
FROM adjustment
WHERE AdjDate >= " + POut.Date(dateFrom) + @"
AND AdjDate <= " + POut.Date(dateTo) + @"
GROUP BY MONTH(AdjDate)" ;
DataTable tableAdj = Db.GetTable(command);
#if DEBUG
stopWatch.Stop();
_elapsedTimeProdInc += "tableAdj: " + stopWatch.Elapsed.ToString() + "\r\n";
stopWatch.Restart();
#endif
switch ((PPOWriteoffDateCalc)PrefC.GetInt(PrefName.ReportsPPOwriteoffDefaultToProcDate))
{
case PPOWriteoffDateCalc.InsPayDate:
command = "SELECT "
+ "claimproc.DateCP,"
+ "SUM(claimproc.WriteOff) "
+ "FROM claimproc "
+ "WHERE claimproc.DateCP >= " + POut.Date(dateFrom) + " "
+ "AND claimproc.DateCP <= " + POut.Date(dateTo) + " "
+ "AND claimproc.Status IN (" + (int)ClaimProcStatus.Received + "," + (int)ClaimProcStatus.Supplemental + ") " //Received or supplemental
+ "GROUP BY MONTH(claimproc.DateCP)";
break;
case PPOWriteoffDateCalc.ProcDate:
command = "SELECT "
+ "claimproc.ProcDate,"
+ "SUM(claimproc.WriteOff) "
+ "FROM claimproc "
+ "WHERE claimproc.ProcDate >= " + POut.Date(dateFrom) + " "
+ "AND claimproc.ProcDate <= " + POut.Date(dateTo) + " "
+ "AND claimproc.Status IN (" + (int)ClaimProcStatus.Received + "," + (int)ClaimProcStatus.Supplemental + "," + (int)ClaimProcStatus.NotReceived + ") "
+ "GROUP BY MONTH(claimproc.ProcDate)";
break;
case PPOWriteoffDateCalc.ClaimPayDate: //Means preference is PPOWriteoffDateCalc.InsDate, or PPOWriteoffDateCalc.ClaimPayDate.
command = "SELECT "
+ "claimsnaptshot.DateTEntry,"
+ "SUM(claimsnapshot.WriteOff) "
+ "FROM claimproc "
+ "INNER JOIN claimsnapshot ON claimsnapshot.ClaimProcNum=claimproc.ClaimProcNum "
+ "AND claimsnapshot.DateTEntry >= " + POut.Date(dateFrom) + " "
+ "AND claimsnapshot.DateTEntry <= " + POut.Date(dateTo) + " "
+ "WHERE claimproc.Status IN (" + (int)ClaimProcStatus.Received + "," + (int)ClaimProcStatus.Supplemental + ") "
+ "GROUP BY MONTH(claimsnaptshot.DateTEntry)";
break;
}
DataTable tableWriteoff = Db.GetTable(command);
#if DEBUG
stopWatch.Stop();
_elapsedTimeProdInc += "tableWriteoff: " + stopWatch.Elapsed.ToString() + "\r\n";
stopWatch.Restart();
#endif
command = "SELECT "
+ "paysplit.DatePay,"
+ "SUM(paysplit.SplitAmt) "
+ "FROM paysplit "
+ "WHERE paysplit.IsDiscount=0 "
+ "AND paysplit.DatePay >= " + POut.Date(dateFrom) + " "
+ "AND paysplit.DatePay <= " + POut.Date(dateTo) + " "
+ "GROUP BY MONTH(paysplit.DatePay)";
DataTable tablePay = Db.GetTable(command);
#if DEBUG
stopWatch.Stop();
_elapsedTimeProdInc += "tablePay: " + stopWatch.Elapsed.ToString() + "\r\n";
stopWatch.Restart();
#endif
command = "SELECT claimpayment.CheckDate,SUM(claimproc.InsPayamt) "
+ "FROM claimpayment,claimproc WHERE "
+ "claimproc.ClaimPaymentNum = claimpayment.ClaimPaymentNum "
+ "AND claimpayment.CheckDate >= " + POut.Date(dateFrom) + " "
+ "AND claimpayment.CheckDate <= " + POut.Date(dateTo) + " "
+ " GROUP BY claimpayment.CheckDate ORDER BY checkdate";
DataTable tableIns = Db.GetTable(command);
#if DEBUG
stopWatch.Stop();
stopWatchTotal.Stop();
_elapsedTimeProdInc += "tableIns: " + stopWatch.Elapsed.ToString() + "\r\n";
_elapsedTimeProdInc += "Total: " + stopWatchTotal.Elapsed.ToString();
if (_showElapsedTimesForDebug)
{
System.Windows.Forms.MessageBox.Show(_elapsedTimeProdInc);
}
#endif
//production--------------------------------------------------------------------
List <int> listInt;
listInt = new List <int>();
for (int i = 0; i < 12; i++)
{
decimal prod = 0;
decimal adjust = 0;
decimal inswriteoff = 0;
DateTime datePeriod = dateFrom.AddMonths(i); //only the month and year are important
for (int j = 0; j < tableProduction.Rows.Count; j++)
{
if (datePeriod.Year == PIn.Date(tableProduction.Rows[j][0].ToString()).Year &&
datePeriod.Month == PIn.Date(tableProduction.Rows[j][0].ToString()).Month)
{
prod += PIn.Decimal(tableProduction.Rows[j][1].ToString());
}
}
for (int j = 0; j < tableAdj.Rows.Count; j++)
{
if (datePeriod.Year == PIn.Date(tableAdj.Rows[j][0].ToString()).Year &&
datePeriod.Month == PIn.Date(tableAdj.Rows[j][0].ToString()).Month)
{
adjust += PIn.Decimal(tableAdj.Rows[j][1].ToString());
}
}
for (int j = 0; j < tableWriteoff.Rows.Count; j++)
{
if (datePeriod.Year == PIn.Date(tableWriteoff.Rows[j][0].ToString()).Year &&
datePeriod.Month == PIn.Date(tableWriteoff.Rows[j][0].ToString()).Month)
{
inswriteoff += PIn.Decimal(tableWriteoff.Rows[j][1].ToString());
}
}
listInt.Add((int)(prod + adjust - inswriteoff));
}
List <List <int> > retVal = new List <List <int> >();
retVal.Add(listInt);
//income----------------------------------------------------------------------
listInt = new List <int>();
for (int i = 0; i < 12; i++)
{
decimal ptincome = 0;
decimal insincome = 0;
DateTime datePeriod = dateFrom.AddMonths(i); //only the month and year are important
for (int j = 0; j < tablePay.Rows.Count; j++)
{
if (datePeriod.Year == PIn.Date(tablePay.Rows[j][0].ToString()).Year &&
datePeriod.Month == PIn.Date(tablePay.Rows[j][0].ToString()).Month)
{
ptincome += PIn.Decimal(tablePay.Rows[j][1].ToString());
}
}
for (int j = 0; j < tableIns.Rows.Count; j++) //
{
if (datePeriod.Year == PIn.Date(tableIns.Rows[j][0].ToString()).Year &&
datePeriod.Month == PIn.Date(tableIns.Rows[j][0].ToString()).Month)
{
insincome += PIn.Decimal(tableIns.Rows[j][1].ToString());
}
}
listInt.Add((int)(ptincome + insincome));
}
retVal.Add(listInt);
return(retVal);
}
///<summary>Returns current clinic limit minus message usage for current calendar month.</summary>
public static double GetClinicBalance(long clinicNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetDouble(MethodBase.GetCurrentMethod(), clinicNum));
}
double limit = 0;
if (!PrefC.HasClinicsEnabled)
{
if (PrefC.GetDate(PrefName.SmsContractDate).Year > 1880)
{
limit = PrefC.GetDouble(PrefName.SmsMonthlyLimit);
}
}
else
{
if (clinicNum == 0 && Clinics.GetCount(true) > 0) //Sending text for "Unassigned" patient. Use the first non-hidden clinic. (for now)
{
clinicNum = Clinics.GetFirst(true).ClinicNum;
}
Clinic clinicCur = Clinics.GetClinic(clinicNum);
if (clinicCur != null && clinicCur.SmsContractDate.Year > 1880)
{
limit = clinicCur.SmsMonthlyLimit;
}
}
DateTime dtStart = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1);
DateTime dtEnd = dtStart.AddMonths(1);
string command = "SELECT SUM(MsgChargeUSD) FROM smstomobile WHERE ClinicNum=" + POut.Long(clinicNum) + " "
+ "AND DateTimeSent>=" + POut.Date(dtStart) + " AND DateTimeSent<" + POut.Date(dtEnd);
limit -= PIn.Double(Db.GetScalar(command));
return(limit);
}
public static List <List <int> > GetNewPatients(DateTime dateFrom, DateTime dateTo)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <List <int> > >(MethodBase.GetCurrentMethod(), dateFrom, dateTo));
}
Random rnd = new Random();
string rndStr = rnd.Next(1000000).ToString();
#if DEBUG
_elapsedTimeNewPatients = "";
System.Diagnostics.Stopwatch stopWatch = new System.Diagnostics.Stopwatch();
System.Diagnostics.Stopwatch stopWatchTotal = new System.Diagnostics.Stopwatch();
_elapsedTimeNewPatients = "Elapsed time for GetNewPatients:\r\n";
stopWatch.Restart();
stopWatchTotal.Restart();
#endif
string command;
command = "DROP TABLE IF EXISTS tempdash" + rndStr + @";";
Db.NonQ(command);
#if DEBUG
stopWatch.Stop();
_elapsedTimeNewPatients += "DROP TABLE: " + stopWatch.Elapsed.ToString() + "\r\n";
stopWatch.Restart();
#endif
command = @"CREATE TABLE tempdash" + rndStr + @" (
PatNum bigint NOT NULL PRIMARY KEY,
dateFirstProc datetime NOT NULL
) DEFAULT CHARSET=utf8" ;
Db.NonQ(command);
#if DEBUG
stopWatch.Stop();
_elapsedTimeNewPatients += "CREATE TABLE: " + stopWatch.Elapsed.ToString() + "\r\n";
stopWatch.Restart();
#endif
//table full of individual patients and their dateFirstProcs.
command = @"INSERT INTO tempdash" + rndStr + @"
SELECT PatNum, MIN(ProcDate) dateFirstProc
FROM procedurelog USE INDEX(indexPatNum)
WHERE ProcStatus=2 GROUP BY PatNum
HAVING dateFirstProc >= " + POut.Date(dateFrom) + " "
+ "AND dateFirstProc <= " + POut.Date(dateTo);
Db.NonQ(command);
#if DEBUG
stopWatch.Stop();
_elapsedTimeNewPatients += "INSERT INTO: " + stopWatch.Elapsed.ToString() + "\r\n";
stopWatch.Restart();
#endif
command = "SELECT dateFirstProc,COUNT(*) "
+ "FROM tempdash" + rndStr + @" "
+ "GROUP BY MONTH(dateFirstProc)";
DataTable tableCounts = Db.GetTable(command);
#if DEBUG
stopWatch.Stop();
stopWatchTotal.Stop();
_elapsedTimeNewPatients += "SELECT dateFirstProc,COUNT(*): " + stopWatch.Elapsed.ToString() + "\r\n";
_elapsedTimeNewPatients += "Total: " + stopWatchTotal.Elapsed.ToString();
if (_showElapsedTimesForDebug)
{
System.Windows.Forms.MessageBox.Show(_elapsedTimeNewPatients);
}
#endif
List <int> listInt = new List <int>();
for (int i = 0; i < 12; i++)
{
int ptcount = 0;
DateTime datePeriod = dateFrom.AddMonths(i); //only the month and year are important
for (int j = 0; j < tableCounts.Rows.Count; j++)
{
if (datePeriod.Year == PIn.Date(tableCounts.Rows[j][0].ToString()).Year &&
datePeriod.Month == PIn.Date(tableCounts.Rows[j][0].ToString()).Month)
{
ptcount += PIn.Int(tableCounts.Rows[j][1].ToString());
}
}
listInt.Add(ptcount);
}
List <List <int> > retVal = new List <List <int> >();
retVal.Add(listInt);
return(retVal);
}
public static int GetRecallUndoCount(DateTime date)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetInt(MethodBase.GetCurrentMethod(), date));
}
string command = "SELECT COUNT(*) FROM commlog "
+ "WHERE " + DbHelper.DateColumn("CommDateTime") + " = " + POut.Date(date) + " "
+ "AND (SELECT ItemValue FROM definition WHERE definition.DefNum=commlog.CommType) ='" + CommItemTypeAuto.RECALL.ToString() + "'";
return(PIn.Int(Db.GetScalar(command)));
}
///<summary></summary>
public static List <Equipment> GetList(DateTime fromDate, DateTime toDate, EnumEquipmentDisplayMode display, string snDesc)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <Equipment> >(MethodBase.GetCurrentMethod(), fromDate, toDate, display, snDesc));
}
string command = "";
if (display == EnumEquipmentDisplayMode.Purchased)
{
command = "SELECT * FROM equipment "
+ "WHERE DatePurchased >= " + POut.Date(fromDate)
+ " AND DatePurchased <= " + POut.Date(toDate)
+ " AND (SerialNumber LIKE '%" + POut.String(snDesc) + "%' OR Description LIKE '%" + POut.String(snDesc) + "%' OR Location LIKE '%" + POut.String(snDesc) + "%')"
+ " ORDER BY DatePurchased";
}
if (display == EnumEquipmentDisplayMode.Sold)
{
command = "SELECT * FROM equipment "
+ "WHERE DateSold >= " + POut.Date(fromDate)
+ " AND DateSold <= " + POut.Date(toDate)
+ " AND (SerialNumber LIKE '%" + POut.String(snDesc) + "%' OR Description LIKE '%" + POut.String(snDesc) + "%' OR Location LIKE '%" + POut.String(snDesc) + "%')"
+ " ORDER BY DatePurchased";
}
if (display == EnumEquipmentDisplayMode.All)
{
command = "SELECT * FROM equipment "
+ "WHERE ((DatePurchased >= " + POut.Date(fromDate) + " AND DatePurchased <= " + POut.Date(toDate) + ")"
+ " OR (DateSold >= " + POut.Date(fromDate) + " AND DateSold <= " + POut.Date(toDate) + "))"
+ " AND (SerialNumber LIKE '%" + POut.String(snDesc) + "%' OR Description LIKE '%" + POut.String(snDesc) + "%' OR Location LIKE '%" + POut.String(snDesc) + "%')"
+ " ORDER BY DatePurchased";
}
return(Crud.EquipmentCrud.SelectMany(command));
}
public static void RecallUndo(DateTime date)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), date);
return;
}
string command = "DELETE FROM commlog "
+ "WHERE " + DbHelper.DateColumn("CommDateTime") + " = " + POut.Date(date) + " "
+ "AND (SELECT ItemValue FROM definition WHERE definition.DefNum=commlog.CommType) ='" + CommItemTypeAuto.RECALL.ToString() + "'";
Db.NonQ(command);
}
///<summary>If not using clinics then supply an empty list of clinicNums.</summary>
public static DataSet GetPayPlanTable(DateTime dateStart, DateTime dateEnd, List <long> listProvNums, List <long> listClinicNums,
bool hasAllProvs, DisplayPayPlanType displayPayPlanType, bool hideCompletedPlans, bool showFamilyBalance, bool hasDateRange, bool isPayPlanV2)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetDS(MethodBase.GetCurrentMethod(), dateStart, dateEnd, listProvNums, listClinicNums, hasAllProvs, displayPayPlanType,
hideCompletedPlans, showFamilyBalance, hasDateRange, isPayPlanV2));
}
string whereProv = "";
if (!hasAllProvs)
{
whereProv += " AND payplancharge.ProvNum IN(";
for (int i = 0; i < listProvNums.Count; i++)
{
if (i > 0)
{
whereProv += ",";
}
whereProv += POut.Long(listProvNums[i]);
}
whereProv += ") ";
}
string whereClin = "";
bool hasClinicsEnabled = ReportsComplex.RunFuncOnReportServer(() => (!Prefs.GetBoolNoCache(PrefName.EasyNoClinics)));
if (hasClinicsEnabled) //Using clinics
{
whereClin += " AND payplancharge.ClinicNum IN(";
for (int i = 0; i < listClinicNums.Count; i++)
{
if (i > 0)
{
whereClin += ",";
}
whereClin += POut.Long(listClinicNums[i]);
}
whereClin += ") ";
}
DataSet ds = new DataSet();
DataTable table = new DataTable("Clinic");
table.Columns.Add("provider");
table.Columns.Add("guarantor");
table.Columns.Add("ins");
table.Columns.Add("princ");
table.Columns.Add("accumInt");
table.Columns.Add("paid");
table.Columns.Add("balance");
table.Columns.Add("due");
if (isPayPlanV2)
{
table.Columns.Add("notDue");
}
table.Columns.Add("famBal");
table.Columns.Add("clinicName");
DataTable tableTotals = new DataTable("Total");
tableTotals.Columns.Add("clinicName");
tableTotals.Columns.Add("princ");
tableTotals.Columns.Add("accumInt");
tableTotals.Columns.Add("paid");
tableTotals.Columns.Add("balance");
tableTotals.Columns.Add("due");
if (isPayPlanV2)
{
tableTotals.Columns.Add("notDue");
}
tableTotals.Columns.Add("famBal");
DataRow row;
string datesql = "CURDATE()"; //This is used to find out how much people owe currently and has nothing to do with the selected range
if (DataConnection.DBtype == DatabaseType.Oracle)
{
datesql = "(SELECT CURRENT_DATE FROM dual)";
}
//Oracle TODO: Either put entire query without GROUP BY in SUBSELECT and then GROUP BY outside, or rewrite query to use joins instead of subselects.
string command = "SELECT FName,LName,MiddleI,PlanNum,Preferred,PlanNum, "
+ "COALESCE((SELECT SUM(Principal+Interest) FROM payplancharge WHERE payplancharge.PayPlanNum=payplan.PayPlanNum "
+ "AND payplancharge.ChargeType=" + POut.Int((int)PayPlanChargeType.Debit) + " "//for v1, debits are the only ChargeType.
+ "AND ChargeDate <= " + datesql + @"),0) '_accumDue', ";
command += "COALESCE((SELECT SUM(Interest) FROM payplancharge WHERE payplancharge.PayPlanNum=payplan.PayPlanNum "
+ "AND payplancharge.ChargeType=" + POut.Int((int)PayPlanChargeType.Debit) + " " //for v1, debits are the only ChargeType.
+ "AND ChargeDate <= " + datesql + @"),0) '_accumInt', ";
command += "COALESCE((SELECT SUM(SplitAmt) FROM paysplit WHERE paysplit.PayPlanNum=payplan.PayPlanNum AND paysplit.PayPlanNum!=0),0) '_paid', ";
command += "COALESCE((SELECT SUM(InsPayAmt) FROM claimproc WHERE claimproc.PayPlanNum=payplan.PayPlanNum "
+ "AND claimproc.Status IN("
+ POut.Int((int)ClaimProcStatus.Received) + ","
+ POut.Int((int)ClaimProcStatus.Supplemental) + ","
+ POut.Int((int)ClaimProcStatus.CapClaim)
+ ") AND claimproc.PayPlanNum!=0),0) '_insPaid', ";
command += "COALESCE((SELECT SUM(Principal) FROM payplancharge WHERE payplancharge.PayPlanNum=payplan.PayPlanNum "
+ "AND payplancharge.ChargeType=" + POut.Int((int)PayPlanChargeType.Debit) + "),0) '_principal', " //for v1, debits are the only ChargeType.
+ "COALESCE((SELECT SUM(Principal) FROM payplancharge WHERE payplancharge.PayPlanNum=payplan.PayPlanNum "
+ "AND payplancharge.ChargeType=" + POut.Int((int)PayPlanChargeType.Credit) + "),0) '_credits', " //for v1, will always be 0.
+ "COALESCE((SELECT SUM(Principal) FROM payplancharge WHERE payplancharge.PayPlanNum=payplan.PayPlanNum "
+ "AND payplancharge.ChargeType=" + POut.Int((int)PayPlanChargeType.Credit) + " AND ChargeDate > " + datesql + "),0) '_notDue', "
+ "patient.PatNum PatNum, "
+ "payplancharge.ProvNum ProvNum ";
if (hasClinicsEnabled)
{
command += ", payplancharge.ClinicNum ClinicNum ";
}
//In order to determine if the patient has completely paid off their payment plan we need to get the total amount of interest as of today.
//Then, after the query has run, we'll add the interest up until today with the total principal for the entire payment plan.
//For this reason, we cannot use _accumDue which only gets the principle up until today and not the entire payment plan principle.
command += ",COALESCE((SELECT SUM(Interest) FROM payplancharge WHERE payplancharge.PayPlanNum=payplan.PayPlanNum "
+ "AND payplancharge.ChargeType=" + POut.Int((int)PayPlanChargeType.Debit) + " " //for v1, debits are the only ChargeType.
+ "AND ChargeDate <= " + datesql + @"),0) '_interest' "
+ "FROM payplan "
+ "LEFT JOIN patient ON patient.PatNum=payplan.Guarantor "
+ "LEFT JOIN payplancharge ON payplan.PayPlanNum=payplancharge.PayPlanNum "
+ "WHERE TRUE "; //Always include true, so that the WHERE clause may always be present.
if (hasDateRange)
{
command += "AND payplan.PayPlanDate >= " + POut.Date(dateStart) + " "
+ "AND payplan.PayPlanDate <= " + POut.Date(dateEnd) + " ";
}
command += whereProv
+ whereClin;
if (displayPayPlanType == DisplayPayPlanType.Insurance)
{
command += "AND payplan.PlanNum!=0 ";
}
else if (displayPayPlanType == DisplayPayPlanType.Patient)
{
command += "AND payplan.PlanNum=0 ";
}
else if (displayPayPlanType == DisplayPayPlanType.Both)
{
//Do not filter the query at all which will show both insurance and patient payment plan types.
}
if (hideCompletedPlans)
{
command += "AND payplan.IsClosed=0 ";
}
command += "GROUP BY FName,LName,MiddleI,Preferred,payplan.PayPlanNum ";
if (hasClinicsEnabled)
{
command += "ORDER BY ClinicNum,LName,FName";
}
else
{
command += "ORDER BY LName,FName";
}
DataTable raw = ReportsComplex.RunFuncOnReportServer(() => ReportsComplex.GetTable(command));
List <Provider> listProvs = ReportsComplex.RunFuncOnReportServer(() => Providers.GetAll());
//DateTime payplanDate;
Patient pat;
double princ;
double paid;
double interest;
double accumDue;
double notDue;
decimal famBal = 0;
double princTot = 0;
double paidTot = 0;
double interestTot = 0;
double balanceTot = 0;
double accumDueTot = 0;
double notDueTot = 0;
decimal famBalTot = 0;
string clinicDescOld = "";
for (int i = 0; i < raw.Rows.Count; i++)
{
princ = PIn.Double(raw.Rows[i]["_principal"].ToString());
interest = PIn.Double(raw.Rows[i]["_accumInt"].ToString());
if (raw.Rows[i]["PlanNum"].ToString() == "0") //pat payplan
{
paid = PIn.Double(raw.Rows[i]["_paid"].ToString());
}
else //ins payplan
{
paid = PIn.Double(raw.Rows[i]["_insPaid"].ToString());
}
accumDue = PIn.Double(raw.Rows[i]["_accumDue"].ToString());
notDue = PIn.Double(raw.Rows[i]["_notDue"].ToString());
row = table.NewRow();
//payplanDate=PIn.PDate(raw.Rows[i]["PayPlanDate"].ToString());
//row["date"]=raw.Rows[i]["PayPlanDate"].ToString();//payplanDate.ToShortDateString();
pat = new Patient();
pat.LName = raw.Rows[i]["LName"].ToString();
pat.FName = raw.Rows[i]["FName"].ToString();
pat.MiddleI = raw.Rows[i]["MiddleI"].ToString();
pat.Preferred = raw.Rows[i]["Preferred"].ToString();
row["provider"] = Providers.GetLName(PIn.Long(raw.Rows[i]["ProvNum"].ToString()), listProvs);
row["guarantor"] = pat.GetNameLF();
if (raw.Rows[i]["PlanNum"].ToString() == "0")
{
row["ins"] = "";
}
else
{
row["ins"] = "X";
}
row["princ"] = princ.ToString("f");
row["accumInt"] = interest.ToString("f");
row["paid"] = paid.ToString("f");
row["balance"] = (princ + interest - paid).ToString("f");
row["due"] = (accumDue - paid).ToString("f");
if (isPayPlanV2)
{
row["notDue"] = ((princ + interest - paid) - (accumDue - paid)).ToString("f");
}
if (showFamilyBalance)
{
Family famCur = ReportsComplex.RunFuncOnReportServer(() => Patients.GetFamily(PIn.Long(raw.Rows[i]["PatNum"].ToString())));
famBal = (decimal)famCur.ListPats[0].BalTotal;
row["famBal"] = (famBal - (decimal)famCur.ListPats[0].InsEst).ToString("F");
}
if (hasClinicsEnabled) //Using clinics
{
List <Clinic> listClinics = ReportsComplex.RunFuncOnReportServer(() => Clinics.GetClinicsNoCache());
string clinicDesc = Clinics.GetDesc(PIn.Long(raw.Rows[i]["ClinicNum"].ToString()), listClinics);
clinicDesc = (clinicDesc == "")?Lans.g("FormRpPayPlans", "Unassigned"):clinicDesc;
if (!String.IsNullOrEmpty(clinicDescOld) && clinicDesc != clinicDescOld) //Reset all the total values
{
DataRow rowTot = tableTotals.NewRow();
rowTot["clinicName"] = clinicDescOld;
rowTot["princ"] = princTot.ToString();
rowTot["accumInt"] = interestTot.ToString();
rowTot["paid"] = paidTot.ToString();
rowTot["balance"] = balanceTot.ToString();
rowTot["due"] = accumDueTot.ToString();
if (isPayPlanV2)
{
rowTot["notDue"] = notDueTot.ToString();
}
rowTot["famBal"] = famBalTot.ToString();
tableTotals.Rows.Add(rowTot);
princTot = 0;
paidTot = 0;
interestTot = 0;
accumDueTot = 0;
balanceTot = 0;
notDueTot = 0;
famBalTot = 0;
}
row["clinicName"] = clinicDesc;
clinicDescOld = clinicDesc;
princTot += princ;
paidTot += paid;
interestTot += interest;
accumDueTot += (accumDue - paid);
balanceTot += (princ + interest - paid);
notDueTot += ((princ + interest - paid) - (accumDue - paid));
famBalTot += famBal;
if (i == raw.Rows.Count - 1)
{
DataRow rowTot = tableTotals.NewRow();
rowTot["clinicName"] = clinicDescOld;
rowTot["princ"] = princTot.ToString();
rowTot["accumInt"] = interestTot.ToString();
rowTot["paid"] = paidTot.ToString();
rowTot["balance"] = balanceTot.ToString();
rowTot["due"] = accumDueTot.ToString();
if (isPayPlanV2)
{
rowTot["notDue"] = notDueTot.ToString();
}
rowTot["famBal"] = famBalTot.ToString();
tableTotals.Rows.Add(rowTot);
}
}
table.Rows.Add(row);
}
ds.Tables.Add(table);
ds.Tables.Add(tableTotals);
return(ds);
}
///<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>Gets all X12 835 etrans entries relating to a specific claim.</summary>
public static List<Etrans> GetErasOneClaim(string claimIdentifier,DateTime dateClaimService) {
//The main goal of this check is to prevent null claimIdentifiers from causing an exception.
//However, an empty claim identifier should also return an empty list because that is a terrible identifier IMO.
if(string.IsNullOrEmpty(claimIdentifier)) {
return new List<Etrans>();
}
if(RemotingClient.RemotingRole==RemotingRole.ClientWeb) {
return Meth.GetObject<List<Etrans>>(MethodBase.GetCurrentMethod(),claimIdentifier,dateClaimService);
}
string claimId=claimIdentifier;
if(claimId.Length>16) {
//Our claim identifiers in the database can be longer than 20 characters (mostly when using replication).
//When the claim identifier is sent out on the claim, it is truncated to 20 characters.
//Therefore, if the claim identifier is longer than 20 characters,
//then it was truncated when sent out, so we have to look for claims beginning with the claim identifier given if there is not an exact match.
//We also send shorter identifiers for some clearinghouses. For example, the maximum claim identifier length for Denti-Cal is 17 characters.
claimId=claimId.Substring(0,16);
}
string command="SELECT * FROM etrans"
+" INNER JOIN etransmessagetext ON etransmessagetext.EtransMessageTextNum=etrans.EtransMessageTextNum"
+" AND etransmessagetext.MessageText REGEXP 'CLP."+POut.String(claimId)+"'"
//CLP = match CLP, . = match any character, then up to 16 other chars after.
+" WHERE Etype="+POut.Int((int)EtransType.ERA_835)+" AND etrans.DateTimeTrans >= "+POut.Date(dateClaimService);
if(claimIdentifier.Length<16) {
DataTable tableEtrans=Db.GetTable(command);
List<Etrans> listEtrans=Crud.EtransCrud.TableToList(tableEtrans);
List<Etrans> retVal=new List<Etrans>();
for(int i=0;i<tableEtrans.Rows.Count;i++) {
string messageText=PIn.String(tableEtrans.Rows[i]["MessageText"].ToString());
string separator=messageText.Substring(3,1);//The character that is used as a separator is always at the third index of the message text.
if(messageText.Contains("CLP"+separator+claimId+separator)) {
retVal.Add(listEtrans[i]);//This Etrans has an exact match for the claimIdentifier, it's an accurate search result.
}
}
return retVal;
}
else {
//If the claimIdentifier is > 16 we trust it's unique enough we don't need to do more searching.
//Plus, we cannot trust any characters after the 16th character, since the identifier might have been truncated at some point.
return Crud.EtransCrud.SelectMany(command);
}
}
///<summary>Computes aging for the family specified. Specify guarantor=0 in order to calculate aging for all families.
///Gets all info from database.
///The aging calculation will use the following rules within each family:
///1) The aging "buckets" (0 to 30, 31 to 60, 61 to 90 and Over 90) ONLY include account activity on or
///before AsOfDate.
///2) BalTotal will always include all account activity, even future entries, except when in historical
///mode, where BalTotal will exclude account activity after AsOfDate.
///3) InsEst will always include all insurance estimates, even future estimates, except when in
///historical mode where InsEst excludes insurance estimates after AsOfDate.
///4) PayPlanDue will always include all payment plan charges minus credits, except when in
///historical mode where PayPlanDue excludes payment plan charges and payments after AsOfDate.</summary>
public static void ComputeAging(long guarantor, DateTime AsOfDate, bool historic)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), guarantor, AsOfDate, historic);
return;
}
//Zero out either entire database or entire family.
//Need to zero everything out first to catch former guarantors.
string command = "UPDATE patient SET "
+ "Bal_0_30 = 0"
+ ",Bal_31_60 = 0"
+ ",Bal_61_90 = 0"
+ ",BalOver90 = 0"
+ ",InsEst = 0"
+ ",BalTotal = 0"
+ ",PayPlanDue= 0";
if (guarantor != 0)
{
command += " WHERE Guarantor=" + POut.Long(guarantor);
}
Db.NonQ(command);
if (AsOfDate.Year < 1880)
{
AsOfDate = DateTime.Today;
}
string asOfDate = POut.Date(AsOfDate);
string billInAdvanceDate = POut.Date(AsOfDate.AddDays(PrefC.GetLong(PrefName.PayPlansBillInAdvanceDays)));
if (historic)
{
billInAdvanceDate = POut.Date(DateTime.Today.AddDays(PrefC.GetLong(PrefName.PayPlansBillInAdvanceDays)));
}
string thirtyDaysAgo = POut.Date(AsOfDate.AddDays(-30));
string sixtyDaysAgo = POut.Date(AsOfDate.AddDays(-60));
string ninetyDaysAgo = POut.Date(AsOfDate.AddDays(-90));
string familyPatNums = "";
Collection <string> familyPatNumList = new Collection <string> ();
if (guarantor != 0)
{
familyPatNums = "(";
command = "SELECT p.PatNum FROM patient p WHERE p.Guarantor=" + guarantor;
DataTable tFamilyPatNums = Db.GetTable(command);
for (int i = 0; i < tFamilyPatNums.Rows.Count; i++)
{
if (i > 0)
{
familyPatNums += ",";
}
string patNum = tFamilyPatNums.Rows[i][0].ToString();
familyPatNums += patNum;
familyPatNumList.Add(patNum);
}
familyPatNums += ")";
}
//We use temporary tables using the "CREATE TEMPORARY TABLE" syntax here so that any temporary
//tables created are specific to the current database connection and no actual files are created
//in the database. This will prevent rogue files from collecting in the live database, and will
//prevent aging calculations on one computer from affecting the aging calculations on another computer.
//Unfortunately, this has one side effect, which is that our connector reopens the
//connection every time a command is run, so the temporary tables only last for a single
//command. To get around this issue, we run the aging script as a single command/script.
//Unfortunately, the "CREATE TEMPORARY TABLE" syntax gets replicated if MySQL replication is enabled,
//which becomes a problem becauase the command is then no longer connection specific. Therefore,
//to accomodate to the few offices using database replication with MySQL, when creating the temporary aging tables,
//we append a random string to the temporary table names so the possibility to temporary table
//name collision is practically zero.
//Create a temporary table to calculate aging into temporarily, so that the patient table is
//not being changed by multiple threads if more than one user is calculating aging.
//Since a temporary table is dropped automatically only when the connection is closed,
//and since we use connection pooling, drop them before using.
string tempTableSuffix = CodeBase.MiscUtils.CreateRandomAlphaNumericString(14);//max size for a table name in oracle is 30 chars.
string tempAgingTableName = "tempaging" + tempTableSuffix;
string tempOdAgingTransTableName = "tempodagingtrans" + tempTableSuffix;
if (DataConnection.DBtype == DatabaseType.Oracle)
{
try {
//We would use DROP TEMPORARY TABLE IF EXISTS syntax here but no such syntax exists in Oracle.
command = "DROP TEMPORARY TABLE " + tempAgingTableName + ", " + tempOdAgingTransTableName;
Db.NonQ(command);
}
catch {
//The tables do not exist. Nothing to do.
}
try {
//We would use DROP TABLE IF EXISTS syntax here but no such syntax exists in Oracle.
command = "DROP TABLE " + tempAgingTableName + ", " + tempOdAgingTransTableName;
Db.NonQ(command);
}
catch {
//The tables do not exist. Nothing to do.
}
}
else
{
command = "DROP TEMPORARY TABLE IF EXISTS " + tempAgingTableName + ", " + tempOdAgingTransTableName;
Db.NonQ(command);
command = "DROP TABLE IF EXISTS " + tempAgingTableName + ", " + tempOdAgingTransTableName;
Db.NonQ(command);
}
if (DataConnection.DBtype == DatabaseType.Oracle)
{
command = "CREATE GLOBAL TEMPORARY TABLE " + tempAgingTableName + " (" +
"PatNum NUMBER," +
"Guarantor NUMBER," +
"Charges_0_30 NUMBER(38,8) DEFAULT 0," +
"Charges_31_60 NUMBER(38,8) DEFAULT 0," +
"Charges_61_90 NUMBER(38,8) DEFAULT 0," +
"ChargesOver90 NUMBER(38,8) DEFAULT 0," +
"TotalCredits NUMBER(38,8) DEFAULT 0," +
"InsEst NUMBER(38,8) DEFAULT 0," +
"PayPlanDue NUMBER(38,8) DEFAULT 0," +
"BalTotal NUMBER(38,8) DEFAULT 0" +
");";
}
else
{
command = "CREATE TEMPORARY TABLE " + tempAgingTableName + " (" +
"PatNum bigint," +
"Guarantor bigint," +
"Charges_0_30 DOUBLE DEFAULT 0," +
"Charges_31_60 DOUBLE DEFAULT 0," +
"Charges_61_90 DOUBLE DEFAULT 0," +
"ChargesOver90 DOUBLE DEFAULT 0," +
"TotalCredits DOUBLE DEFAULT 0," +
"InsEst DOUBLE DEFAULT 0," +
"PayPlanDue DOUBLE DEFAULT 0," +
"BalTotal DOUBLE DEFAULT 0" +
");";
}
if (guarantor == 0)
{
//We insert all of the patient numbers and guarantor numbers only when we are running aging for everyone,
//since we do not want to examine every patient record when running aging for a single family.
command += "INSERT INTO " + tempAgingTableName + " (PatNum,Guarantor) " +
"SELECT p.PatNum,p.Guarantor " +
"FROM patient p;";
//When there is only one patient that aging is being calculated for, then the indexes actually
//slow the calculation down slightly, but they significantly improve the speed when aging is being
//calculated for all familes.
if (DataConnection.DBtype == DatabaseType.Oracle)
{
command += "CREATE INDEX " + tempAgingTableName.ToUpper() + "_PATNUM ON " + tempAgingTableName + " (PatNum);";
command += "CREATE INDEX " + tempAgingTableName.ToUpper() + "_GUAR ON " + tempAgingTableName + " (Guarantor);";
}
else
{
command += "ALTER TABLE " + tempAgingTableName + " ADD INDEX IDX_" + tempAgingTableName.ToUpper() + "_PATNUM (PatNum);";
command += "ALTER TABLE " + tempAgingTableName + " ADD INDEX IDX_" + tempAgingTableName.ToUpper() + "_GUARANTOR (Guarantor);";
}
}
else
{
//Manually create insert statements to avoid having the database system visit every patient record again.
//In my testing, this saves about 0.25 seconds on an individual family aging calculation on my machine in MySQL.
command += "INSERT INTO " + tempAgingTableName + " (PatNum,Guarantor) VALUES ";
for (int i = 0; i < familyPatNumList.Count; i++)
{
if (i > 0)
{
command += ",";
}
command += "(" + familyPatNumList[i] + "," + guarantor + ")";
}
command += ";";
}
//Create another temporary table which holds a very concise summary of the entire office transaction history,
//so that all transactions can be treated as either a general credit or a general charge in the aging calculation.
//Since we are recreating a temporary table with the same name as last time aging was run,
//the old temporary table gets wiped out.
if (DataConnection.DBtype == DatabaseType.Oracle)
{
command += "CREATE GLOBAL TEMPORARY TABLE " + tempOdAgingTransTableName + " (" +
"PatNum NUMBER," +
"TranDate DATE DEFAULT TO_DATE('0001-01-01', 'yyyy-mm-dd')," +
"TranAmount NUMBER(38,8) DEFAULT 0" +
");";
}
else
{
command += "CREATE TEMPORARY TABLE " + tempOdAgingTransTableName + " (" +
"PatNum bigint," +
"TranDate DATE DEFAULT '0001-01-01'," +
"TranAmount DOUBLE DEFAULT 0" +
");";
}
//Get the completed procedure dates and charges for the entire office history.
command += "INSERT INTO " + tempOdAgingTransTableName + " (PatNum,TranDate,TranAmount) " +
"SELECT pl.PatNum PatNum," +
"pl.ProcDate TranDate," +
"pl.ProcFee*(pl.UnitQty+pl.BaseUnits) TranAmount " +
"FROM procedurelog pl " +
"WHERE pl.ProcStatus=2 " +
(guarantor == 0?"":(" AND pl.PatNum IN " + familyPatNums)) + ";";
//Paysplits for the entire office history.
command += "INSERT INTO " + tempOdAgingTransTableName + " (PatNum,TranDate,TranAmount) " +
"SELECT ps.PatNum PatNum," +
"ps.DatePay TranDate," +
"-ps.SplitAmt TranAmount " +
"FROM paysplit ps " +
"WHERE ps.PayPlanNum=0 " + //Only splits not attached to payment plans.
(guarantor == 0?"":(" AND ps.PatNum IN " + familyPatNums)) + ";";
//Get the adjustment dates and amounts for the entire office history.
command += "INSERT INTO " + tempOdAgingTransTableName + " (PatNum,TranDate,TranAmount) " +
"SELECT a.PatNum PatNum," +
"a.AdjDate TranDate," +
"a.AdjAmt TranAmount " +
"FROM adjustment a " +
"WHERE a.AdjAmt<>0 " +
(guarantor == 0?"":(" AND a.PatNum IN " + familyPatNums)) + ";";
//Claim payments and capitation writeoffs for the entire office history.
command += "INSERT INTO " + tempOdAgingTransTableName + " (PatNum,TranDate,TranAmount) " +
"SELECT cp.PatNum PatNum," +
"cp.DateCp TranDate," + //Always use DateCP rather than ProcDate to calculate the date of a claim payment.
"-cp.InsPayAmt-cp.Writeoff TranAmount " +
"FROM claimproc cp " +
"WHERE cp.status IN (1,4,5,7) " + //received, supplemental, CapClaim or CapComplete.
(guarantor == 0?"":(" AND cp.PatNum IN " + familyPatNums)) + ";";
//Payment plan principal for the entire office history.
command += "INSERT INTO " + tempOdAgingTransTableName + " (PatNum,TranDate,TranAmount) " +
"SELECT pp.PatNum PatNum," +
"pp.PayPlanDate TranDate," +
"-pp.CompletedAmt TranAmount " +
"FROM payplan pp " +
"WHERE pp.CompletedAmt<>0 " +
(guarantor == 0?"":(" AND pp.PatNum IN " + familyPatNums)) + ";";
if (DataConnection.DBtype == DatabaseType.Oracle)
{
//The aging calculation buckets, insurance estimates, and payment plan due amounts are
//not yet calculated for Oracle as they have not been needed yet. Just calculates
//account balance totals.
string tempTotalsTableName = "temptotals" + tempTableSuffix;
command += "CREATE GLOBAL TEMPORARY TABLE " + tempTotalsTableName + " (" +
"PatNum NUMBER DEFAULT 0," +
"BalTotal NUMBER(38,8) DEFAULT 0" +
");";
command += "CREATE INDEX " + tempTotalsTableName.ToUpper() + "_PATNU ON " + tempTotalsTableName + " (PatNum);";
command += "INSERT INTO " + tempTotalsTableName + " " +
"SELECT PatNum,ROUND(SUM(TranAmount),2) FROM " + tempOdAgingTransTableName +
"GROUP BY PatNum;";
command += "UPDATE patient p " +
"SET p.BalTotal=(SELECT t.BalTotal FROM " + tempTotalsTableName + " t WHERE t.PatNum=p.PatNum " + DbHelper.LimitAnd(1) + ");";
Db.NonQ(command);
}
else
{
//Now that we have all of the pertinent transaction history, we will calculate all of the charges for
//the associated patients.
//Calculate over 90 day charges for all specified families.
command += "UPDATE " + tempAgingTableName + " a," +
//Calculate the total charges for each patient during this time period and
//place the results into memory table 'chargesOver90'.
"(SELECT t.PatNum,SUM(t.TranAmount) TotalCharges FROM " + tempOdAgingTransTableName + " t " +
"WHERE t.TranAmount>0 AND t.TranDate<" + DbHelper.DateColumn(ninetyDaysAgo) + " GROUP BY t.PatNum) chargesOver90 " +
//Update the tempaging table with the caculated charges for the time period.
"SET a.ChargesOver90=chargesOver90.TotalCharges " +
"WHERE a.PatNum=chargesOver90.PatNum;";
//Calculate 61 to 90 day charges for all specified families.
command += "UPDATE " + tempAgingTableName + " a," +
//Calculate the total charges for each patient during this time period and
//place the results into memory table 'charges_61_90'.
"(SELECT t.PatNum,SUM(t.TranAmount) TotalCharges FROM " + tempOdAgingTransTableName + " t " +
"WHERE t.TranAmount>0 AND t.TranDate<" + DbHelper.DateColumn(sixtyDaysAgo) + " AND " +
"t.TranDate>=" + DbHelper.DateColumn(ninetyDaysAgo) + " GROUP BY t.PatNum) charges_61_90 " +
//Update the tempaging table with the caculated charges for the time period.
"SET a.Charges_61_90=charges_61_90.TotalCharges " +
"WHERE a.PatNum=charges_61_90.PatNum;";
//Calculate 31 to 60 day charges for all specified families.
command += "UPDATE " + tempAgingTableName + " a," +
//Calculate the total charges for each patient during this time period and
//place the results into memory table 'charges_31_60'.
"(SELECT t.PatNum,SUM(t.TranAmount) TotalCharges FROM " + tempOdAgingTransTableName + " t " +
"WHERE t.TranAmount>0 AND t.TranDate<" + DbHelper.DateColumn(thirtyDaysAgo) + " AND " +
"t.TranDate>=" + DbHelper.DateColumn(sixtyDaysAgo) + " GROUP BY t.PatNum) charges_31_60 " +
//Update the tempaging table with the caculated charges for the time period.
"SET a.Charges_31_60=charges_31_60.TotalCharges " +
"WHERE a.PatNum=charges_31_60.PatNum;";
//Calculate 0 to 30 day charges for all specified families.
command += "UPDATE " + tempAgingTableName + " a," +
//Calculate the total charges for each patient during this time period and
//place the results into memory table 'charges_0_30'.
"(SELECT t.PatNum,SUM(t.TranAmount) TotalCharges FROM " + tempOdAgingTransTableName + " t " +
"WHERE t.TranAmount>0 AND t.TranDate<=" + DbHelper.DateColumn(asOfDate) + " AND " +
"t.TranDate>=" + DbHelper.DateColumn(thirtyDaysAgo) + " GROUP BY t.PatNum) charges_0_30 " +
//Update the tempaging table with the caculated charges for the time period.
"SET a.Charges_0_30=charges_0_30.TotalCharges " +
"WHERE a.PatNum=charges_0_30.PatNum;";
//Calculate the total credits each patient has ever received so we can apply the credits to the aged charges below.
command += "UPDATE " + tempAgingTableName + " a," +
//Calculate the total credits for each patient and store the results in memory table 'credits'.
"(SELECT t.PatNum,-SUM(t.TranAmount) TotalCredits FROM " + tempOdAgingTransTableName + " t " +
"WHERE t.TranAmount<0 AND t.TranDate<=" + DbHelper.DateColumn(asOfDate) + " GROUP BY t.PatNum) credits " +
//Update the total credit for each patient into the tempaging table.
"SET a.TotalCredits=credits.TotalCredits " +
"WHERE a.PatNum=credits.PatNum;";
//Calculate claim estimates for each patient individually on or before the specified date.
command += "UPDATE " + tempAgingTableName + " a," +
//Calculate the insurance estimates for each patient and store the results into
//memory table 't'.
"(SELECT cp.PatNum,SUM(cp.InsPayEst+cp.Writeoff) InsEst " +
"FROM claimproc cp " +
"WHERE cp.PatNum<>0 " +
(historic?(" AND ((cp.Status=0 AND cp.ProcDate<=" + DbHelper.DateColumn(asOfDate) + ") OR " +
"(cp.Status=1 AND cp.DateCP>" + DbHelper.DateColumn(asOfDate) + ")) AND cp.ProcDate<=" + DbHelper.DateColumn(asOfDate) + " "):" AND cp.Status=0 ") +
(guarantor == 0?"":(" AND cp.PatNum IN " + familyPatNums + " ")) +
"GROUP BY cp.PatNum) t " + //not received claims.
//Update the tempaging table with the insurance estimates for each patient.
"SET a.InsEst=t.InsEst " +
"WHERE a.PatNum=t.PatNum;";
//Calculate the payment plan charges for each payment plan guarantor
//on or before the specified date (also considering the PayPlansBillInAdvanceDays setting).
//We cannot exclude payments made outside the specified family, since payment plan
//guarantors can be in another family.
command += "UPDATE " + tempAgingTableName + " a," +
"(SELECT ppc.Guarantor,IFNULL(SUM(ppc.Principal+ppc.Interest),0) PayPlanCharges " +
"FROM payplancharge ppc " +
"WHERE ppc.ChargeDate<=" + DbHelper.DateColumn(billInAdvanceDate) + " " + //bill in adv. date accounts for historic vs current because of how it is set above.
"GROUP BY ppc.Guarantor) c " +
"SET a.PayPlanDue=c.PayPlanCharges " +
"WHERE c.Guarantor=a.PatNum;";
//Calculate the total payments made to each payment plan
//on or before the specified date and store the results in memory table 'p'.
//We cannot exclude payments made outside the specified family, since payment plan
//guarantors can be in another family.
command += "UPDATE " + tempAgingTableName + " a," +
"(SELECT ps.PatNum,SUM(ps.SplitAmt) PayPlanPayments " +
"FROM paysplit ps " +
"WHERE ps.PayPlanNum<>0 " + //only payments attached to payment plans.
(historic?(" AND ps.DatePay<=" + DbHelper.DateColumn(asOfDate) + " "):"") +
"GROUP BY ps.PatNum) p " +
"SET a.PayPlanDue=a.PayPlanDue-p.PayPlanPayments " +
"WHERE p.PatNum=a.PatNum;";
//Calculate the total balance for each patient.
//In historical mode, only transactions on or before AsOfDate will be included.
command += "UPDATE " + tempAgingTableName + " a," +
//Calculate the total balance for each patient and
//place the results into memory table 'totals'.
"(SELECT t.PatNum,SUM(t.TranAmount) BalTotal FROM " + tempOdAgingTransTableName + " t " +
"WHERE t.TranAmount<>0 " + (historic?(" AND t.TranDate<=" + DbHelper.DateColumn(asOfDate)):"") + " GROUP BY t.PatNum) totals " +
//Update the tempaging table with the caculated charges for the time period.
"SET a.BalTotal=totals.BalTotal " +
"WHERE a.PatNum=totals.PatNum;";
//Update the family aged balances onto the guarantor rows of the patient table
//by placing credits on oldest charges first, then on younger charges.
command += "UPDATE patient p," +
//Sum each colum within each family group inside of the tempaging table so that we are now
//using family amounts instead of individual patient amounts, and store the result into
//memory table 'f'.
"(SELECT a.Guarantor,SUM(a.Charges_0_30) Charges_0_30,SUM(a.Charges_31_60) Charges_31_60," +
"SUM(a.Charges_61_90) Charges_61_90,SUM(a.ChargesOver90) ChargesOver90," +
"SUM(TotalCredits) TotalCredits,SUM(InsEst) InsEst,SUM(PayPlanDue) PayPlanDue," +
"SUM(BalTotal) BalTotal " +
"FROM " + tempAgingTableName + " a " +
"GROUP BY a.Guarantor) f " +
//Perform the update of the patient table based on the family amounts summed into table 'f', and
//distribute the payments into the oldest balances first.
"SET " +
"p.BalOver90=ROUND((CASE " +
//over 90 balance paid in full.
"WHEN f.TotalCredits>=f.ChargesOver90 THEN 0 " +
//over 90 balance partially paid or unpaid.
"ELSE f.ChargesOver90-f.TotalCredits END),2)," +
"p.Bal_61_90=ROUND((CASE " +
//61 to 90 day balance unpaid.
"WHEN f.TotalCredits<=f.ChargesOver90 THEN f.Charges_61_90 " +
//61 to 90 day balance paid in full.
"WHEN f.ChargesOver90+f.Charges_61_90<=f.TotalCredits THEN 0 " +
//61 to 90 day balance partially paid.
"ELSE f.ChargesOver90+f.Charges_61_90-f.TotalCredits END),2)," +
"p.Bal_31_60=ROUND((CASE " +
//31 to 60 day balance unpaid.
"WHEN f.TotalCredits<f.ChargesOver90+f.Charges_61_90 THEN f.Charges_31_60 " +
//31 to 60 day balance paid in full.
"WHEN f.ChargesOver90+f.Charges_61_90+f.Charges_31_60<=f.TotalCredits THEN 0 " +
//31 to 60 day balance partially paid.
"ELSE f.ChargesOver90+f.Charges_61_90+f.Charges_31_60-f.TotalCredits END),2)," +
"p.Bal_0_30=ROUND((CASE " +
//0 to 30 day balance unpaid.
"WHEN f.TotalCredits<f.ChargesOver90+f.Charges_61_90+f.Charges_31_60 THEN f.Charges_0_30 " +
//0 to 30 day balance paid in full.
"WHEN f.ChargesOver90+f.Charges_61_90+f.Charges_31_60+f.Charges_0_30<=f.TotalCredits THEN 0 " +
//0 to 30 day balance partially paid.
"ELSE f.ChargesOver90+f.Charges_61_90+f.Charges_31_60+f.Charges_0_30-f.TotalCredits END),2)," +
"p.BalTotal=ROUND(f.BalTotal,2)," +
"p.InsEst=ROUND(f.InsEst,2)," +
"p.PayPlanDue=ROUND(f.PayPlanDue,2) " +
"WHERE p.PatNum=f.Guarantor;"; //Aging calculations only apply to guarantors.
Db.NonQ(command);
}
if (DataConnection.DBtype == DatabaseType.Oracle)
{
try {
//We would use DROP TEMPORARY TABLE IF EXISTS syntax here but no such syntax exists in Oracle.
command = "DROP TEMPORARY TABLE " + tempAgingTableName + ", " + tempOdAgingTransTableName;
Db.NonQ(command);
}
catch {
//The tables do not exist. Nothing to do.
}
try {
//We would use DROP TABLE IF EXISTS syntax here but no such syntax exists in Oracle.
command = "DROP TABLE " + tempAgingTableName + ", " + tempOdAgingTransTableName;
Db.NonQ(command);
}
catch {
//The tables do not exist. Nothing to do.
}
}
else
{
command = "DROP TEMPORARY TABLE IF EXISTS " + tempAgingTableName + ", " + tempOdAgingTransTableName;
Db.NonQ(command);
command = "DROP TABLE IF EXISTS " + tempAgingTableName + ", " + tempOdAgingTransTableName;
Db.NonQ(command);
}
}
public static DataTable GetClaimsNotSent(DateTime fromDate, DateTime toDate, List <long> listClinicNums
, bool hasClaimTypeExpanded, ClaimNotSentStatuses claimStatusFilter)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), fromDate, toDate, listClinicNums
, hasClaimTypeExpanded, claimStatusFilter));
}
bool hasClinicsEnabled = ReportsComplex.RunFuncOnReportServer(() => !Prefs.GetBoolNoCache(PrefName.EasyNoClinics));
string command = "";
string whereClin = "";
string claimFilter = "";
if (hasClinicsEnabled && listClinicNums.Count > 0) //construct the IN statement for all of the selected clinics
{
whereClin += " AND claim.ClinicNum IN(" + string.Join(",", listClinicNums) + ")";
}
if (hasClinicsEnabled)
{
command = "SELECT clinic.Abbr AS 'Clinic',";
}
else
{
command = "SELECT ";
}
if (hasClaimTypeExpanded)
{
command += "claim.DateService,(CASE WHEN claim.ClaimType='P' THEN 'Primary' WHEN claim.ClaimType='S' THEN 'Secondary' "
+ "WHEN claim.ClaimType='Cap' THEN 'Capitation' ELSE claim.ClaimType END) AS ClaimType,";
}
else
{
command += "claim.DateService,claim.ClaimType,";
}
//Claim statuses of Unsent, Hold until pri, and Waiting are considered for "All" in this report.
string claimStatusAll = "AND claim.ClaimStatus IN ('U','H','W')";
switch (claimStatusFilter)
{
case ClaimNotSentStatuses.Primary:
claimFilter = "AND claim.ClaimType='P' " + claimStatusAll;
break;
case ClaimNotSentStatuses.Secondary:
claimFilter = "AND claim.ClaimType='S' " + claimStatusAll;
break;
case ClaimNotSentStatuses.Holding:
claimFilter = "AND claim.ClaimStatus='H'";
break;
default: //All
claimFilter += claimStatusAll;
break;
}
string clinJoin = "";
if (hasClinicsEnabled)
{
clinJoin = " LEFT JOIN clinic ON clinic.ClinicNum=claim.ClinicNum";
}
command += "(CASE WHEN claim.ClaimStatus='U' THEN 'Unsent' WHEN "
+ "claim.ClaimStatus='H' THEN 'Hold' WHEN claim.ClaimStatus='W' THEN 'WaitQ' ELSE claim.ClaimStatus END) AS ClaimStatus, "
+ "CONCAT(CONCAT(CONCAT(CONCAT(patient.LName,', '),patient.FName),' '),patient.MiddleI) as 'Patient Name',carrier.CarrierName"
+ ",claim.ClaimFee,GROUP_CONCAT(procedurecode.ProcCode SEPARATOR ', ') ProcCodes,claim.ClaimNum,claim.PatNum "
+ "FROM patient"
+ " INNER JOIN claim ON claim.PatNum=patient.PatNum"
+ " INNER JOIN claimproc ON claimproc.ClaimNum=claim.ClaimNum"
+ " INNER JOIN procedurelog ON procedurelog.ProcNum=claimproc.ProcNum"
+ " INNER JOIN procedurecode ON procedurecode.CodeNum=procedurelog.CodeNum"
+ " INNER JOIN insplan ON insplan.PlanNum=claim.PlanNum"
+ " INNER JOIN carrier ON carrier.CarrierNum=insplan.CarrierNum"
+ clinJoin
+ " WHERE claim.DateService >= " + POut.Date(fromDate)
+ " AND claim.DateService <= " + POut.Date(toDate)
+ whereClin
+ claimFilter
+ " GROUP BY claim.ClaimNum";
if (DataConnection.DBtype == DatabaseType.Oracle)
{
command += ",claim.Status,patient.LName,patient.FName,patient.MiddleI,carrier.CarrierName,claim.ClaimFee,claim.ClaimNum,claim.PatNum";
}
command += " ORDER BY claim.DateService";
return(ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(command)));
}
public static DataTable GetProvList(DateTime dt)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), dt));
}
#if DEBUG
_elapsedTimeProvList = "";
System.Diagnostics.Stopwatch stopWatch = new System.Diagnostics.Stopwatch();
System.Diagnostics.Stopwatch stopWatchTotal = new System.Diagnostics.Stopwatch();
_elapsedTimeProvList = "Elapsed time for GetProvList:\r\n";
stopWatch.Restart();
stopWatchTotal.Restart();
#endif
Random rnd = new Random();
string rndStr = rnd.Next(1000000).ToString();
string command;
command = "DROP TABLE IF EXISTS tempdash" + rndStr + @";";
Db.NonQ(command);
#if DEBUG
stopWatch.Stop();
_elapsedTimeProvList += "DROP TABLE: " + stopWatch.Elapsed.ToString() + "\r\n";
stopWatch.Restart();
#endif
command = @"CREATE TABLE tempdash" + rndStr + @" (
ProvNum bigint NOT NULL PRIMARY KEY,
production decimal NOT NULL,
income decimal NOT NULL
) DEFAULT CHARSET=utf8" ;
Db.NonQ(command);
#if DEBUG
stopWatch.Stop();
_elapsedTimeProvList += "CREATE TABLE: " + stopWatch.Elapsed.ToString() + "\r\n";
stopWatch.Restart();
#endif
//providers
command = @"INSERT INTO tempdash" + rndStr + @" (ProvNum)
SELECT ProvNum
FROM provider WHERE IsHidden=0
ORDER BY ItemOrder" ;
Db.NonQ(command);
#if DEBUG
stopWatch.Stop();
_elapsedTimeProvList += "providers: " + stopWatch.Elapsed.ToString() + "\r\n";
stopWatch.Restart();
#endif
//production--------------------------------------------------------------------
//procs
command = @"UPDATE tempdash" + rndStr + @"
SET production=(SELECT SUM(ProcFee*(UnitQty+BaseUnits)) FROM procedurelog
WHERE procedurelog.ProvNum=tempdash" + rndStr + @".ProvNum
AND procedurelog.ProcStatus=" + POut.Int((int)ProcStat.C) + @"
AND ProcDate=" + POut.Date(dt) + ")";
Db.NonQ(command);
#if DEBUG
stopWatch.Stop();
_elapsedTimeProvList += "production - procs: " + stopWatch.Elapsed.ToString() + "\r\n";
stopWatch.Restart();
#endif
//capcomplete writeoffs were skipped
//adjustments
command = @"UPDATE tempdash" + rndStr + @"
SET production=production+(SELECT IFNULL(SUM(AdjAmt),0) FROM adjustment
WHERE adjustment.ProvNum=tempdash" + rndStr + @".ProvNum
AND AdjDate=" + POut.Date(dt) + ")";
Db.NonQ(command);
#if DEBUG
stopWatch.Stop();
_elapsedTimeProvList += "production - adjustments: " + stopWatch.Elapsed.ToString() + "\r\n";
stopWatch.Restart();
#endif
//insurance writeoffs
switch ((PPOWriteoffDateCalc)PrefC.GetInt(PrefName.ReportsPPOwriteoffDefaultToProcDate)) //use procdate
{
case PPOWriteoffDateCalc.ProcDate:
command = @"UPDATE tempdash" + rndStr + @"
SET production=production-(SELECT IFNULL(SUM(WriteOff),0) FROM claimproc
WHERE claimproc.ProvNum=tempdash" + rndStr + @".ProvNum
AND ProcDate=" + POut.Date(dt) + @"
AND (claimproc.Status=1 OR claimproc.Status=4 OR claimproc.Status=0) )" ;//received or supplemental or notreceived
break;
case PPOWriteoffDateCalc.InsPayDate:
command = @"UPDATE tempdash" + rndStr + @"
SET production=production-(SELECT IFNULL(SUM(WriteOff),0) FROM claimproc
WHERE claimproc.ProvNum=tempdash" + rndStr + @".ProvNum
AND DateCP=" + POut.Date(dt) + @"
AND (claimproc.Status=1 OR claimproc.Status=4) )" ;//received or supplemental
break;
case PPOWriteoffDateCalc.ClaimPayDate:
command = @"UPDATE tempdash" + rndStr + @"
SET production=production-(SELECT IFNULL(SUM(claimsnapshot.WriteOff),0) FROM claimproc cp
INNER JOIN claimsnapshot ON cp.ClaimProcNum=claimsnapshot.ClaimProcNum
WHERE claimproc.ProvNum=tempdash" + rndStr + @".ProvNum
AND claimsnapshot.DateTEntry=" + POut.Date(dt) + @"
AND (claimproc.Status=1 OR claimproc.Status=4) )" ;//received or supplemental
break;
}
Db.NonQ(command);
#if DEBUG
stopWatch.Stop();
_elapsedTimeProvList += "production - writeoffs: " + stopWatch.Elapsed.ToString() + "\r\n";
stopWatch.Restart();
#endif
//income------------------------------------------------------------------------
//patient income
command = @"UPDATE tempdash" + rndStr + @"
SET income=(SELECT SUM(SplitAmt) FROM paysplit
WHERE paysplit.ProvNum=tempdash" + rndStr + @".ProvNum
AND DatePay=" + POut.Date(dt) + ")";
Db.NonQ(command);
#if DEBUG
stopWatch.Stop();
_elapsedTimeProvList += "income - patient: " + stopWatch.Elapsed.ToString() + "\r\n";
stopWatch.Restart();
#endif
//ins income
command = @"UPDATE tempdash" + rndStr + @"
SET income=income+(SELECT IFNULL(SUM(InsPayAmt),0) FROM claimproc
WHERE claimproc.ProvNum=tempdash" + rndStr + @".ProvNum
AND DateCP=" + POut.Date(dt) + ")";
Db.NonQ(command);
#if DEBUG
stopWatch.Stop();
_elapsedTimeProvList += "income - insurance: " + stopWatch.Elapsed.ToString() + "\r\n";
stopWatch.Restart();
#endif
//final queries
command = "SELECT * FROM tempdash" + rndStr + @"";
DataTable table = Db.GetTable(command);
#if DEBUG
stopWatch.Stop();
_elapsedTimeProvList += "SELECT * : " + stopWatch.Elapsed.ToString() + "\r\n";
stopWatch.Restart();
#endif
command = "DROP TABLE IF EXISTS tempdash" + rndStr + @";";
Db.NonQ(command);
#if DEBUG
stopWatch.Stop();
stopWatchTotal.Stop();
_elapsedTimeProvList += "DROP TABLE: " + stopWatch.Elapsed.ToString() + "\r\n";
_elapsedTimeProvList += "Total: " + stopWatchTotal.Elapsed.ToString();
if (_showElapsedTimesForDebug)
{
System.Windows.Forms.MessageBox.Show(_elapsedTimeProvList);
}
#endif
return(table);
}
public static DataSet GetMonthlyGoalDataSet(DateTime dateFrom, DateTime dateTo, List <Provider> listProvs, List <Clinic> listClinics, bool hasAllProvs
, bool hasAllClinics, PPOWriteoffDateCalc writeoffPayType, bool isCEMT = false)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetDS(MethodBase.GetCurrentMethod(), dateFrom, dateTo, listProvs, listClinics, hasAllProvs, hasAllClinics, writeoffPayType, isCEMT));
}
List <long> listClinicNums = listClinics.Select(x => x.ClinicNum).ToList();
List <long> listProvNums = listProvs.Select(x => x.ProvNum).ToList();
#region Procedures
string whereProv = "";
if (!hasAllProvs && listProvNums.Count > 0)
{
whereProv = "AND procedurelog.ProvNum IN (" + string.Join(",", listProvNums) + ") ";
}
string whereClin = "";
if (!hasAllClinics && listClinicNums.Count > 0)
{
whereClin = "AND procedurelog.ClinicNum IN (" + string.Join(",", listClinicNums) + ") ";
}
string command = "SELECT "
+ "procedurelog.ProcDate,procedurelog.ClinicNum,"
+ "SUM(procedurelog.ProcFee*(procedurelog.UnitQty+procedurelog.BaseUnits))-IFNULL(SUM(cp.WriteOff),0) Production "
+ "FROM procedurelog "
+ "LEFT JOIN (SELECT SUM(claimproc.WriteOff) AS WriteOff, claimproc.ProcNum FROM claimproc "
+ "WHERE claimproc.Status=7 " //only CapComplete writeoffs are subtracted here.
+ "GROUP BY claimproc.ProcNum) cp ON procedurelog.ProcNum=cp.ProcNum "
+ "WHERE procedurelog.ProcStatus = 2 "
+ whereProv
+ whereClin
+ "AND procedurelog.ProcDate >= " + POut.Date(dateFrom) + " "
+ "AND procedurelog.ProcDate <= " + POut.Date(dateTo) + " "
+ "GROUP BY ClinicNum,YEAR(procedurelog.ProcDate),MONTH(procedurelog.ProcDate),DAY(procedurelog.ProcDate)"; //Does not work for Oracle. Consider enhancing with DbHelper.Year(),DbHelper.Month()
command += " ORDER BY ClinicNum,ProcDate";
DataTable tableProduction = new DataTable();
if (isCEMT)
{
tableProduction = Db.GetTable(command);
}
else
{
tableProduction = ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(command));
}
tableProduction.TableName = "tableProduction";
#endregion
#region Adjustments
if (!hasAllProvs && listProvNums.Count > 0)
{
whereProv = "AND adjustment.ProvNum IN (" + string.Join(",", listProvNums) + ") ";
}
if (!hasAllClinics && listClinicNums.Count > 0)
{
whereClin = "AND adjustment.ClinicNum IN (" + string.Join(",", listClinicNums) + ") ";
}
command = "SELECT "
+ "adjustment.AdjDate,"
+ "adjustment.ClinicNum,"
+ "SUM(adjustment.AdjAmt) Adjustment "
+ "FROM adjustment "
+ "WHERE adjustment.AdjDate >= " + POut.Date(dateFrom) + " "
+ "AND adjustment.AdjDate <= " + POut.Date(dateTo) + " "
+ whereProv
+ whereClin
+ "GROUP BY ClinicNum,YEAR(adjustment.AdjDate),MONTH(adjustment.AdjDate),DAY(adjustment.AdjDate)";
command += " ORDER BY ClinicNum,AdjDate";
DataTable tableAdj = new DataTable();
if (isCEMT)
{
tableAdj = Db.GetTable(command);
}
else
{
tableAdj = ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(command));
}
tableAdj.TableName = "tableAdj";
#endregion
#region TableInsWriteoff
if (!hasAllProvs && listProvNums.Count > 0)
{
whereProv = "AND claimproc.ProvNum IN (" + string.Join(",", listProvNums) + ") ";
}
if (!hasAllClinics && listClinicNums.Count > 0)
{
whereClin = "AND claimproc.ClinicNum IN (" + string.Join(",", listClinicNums) + ") ";
}
if (writeoffPayType == PPOWriteoffDateCalc.InsPayDate)
{
command = "SELECT "
+ "claimproc.DateCP Date,"
+ "claimproc.ClinicNum,"
+ "SUM(claimproc.WriteOff) WriteOff "
+ "FROM claimproc "
+ "WHERE claimproc.DateCP >= " + POut.Date(dateFrom) + " "
+ "AND claimproc.DateCP <= " + POut.Date(dateTo) + " "
+ whereProv
+ whereClin
+ "AND claimproc.Status IN (" + (int)ClaimProcStatus.Received + "," + (int)ClaimProcStatus.Supplemental + ") "//received or supplemental
+ "GROUP BY ClinicNum,DATE(claimproc.DateCP) "
+ "ORDER BY ClinicNum,DateCP";
}
else if (writeoffPayType == PPOWriteoffDateCalc.ProcDate)
{
command = "SELECT "
+ "claimproc.ProcDate Date,"
+ "claimproc.ClinicNum,"
+ "SUM(claimproc.WriteOff) WriteOff "
+ "FROM claimproc "
+ "WHERE claimproc.ProcDate >= " + POut.Date(dateFrom) + " "
+ "AND claimproc.ProcDate <= " + POut.Date(dateTo) + " "
+ whereProv
+ whereClin
+ "AND claimproc.Status IN (" + (int)ClaimProcStatus.Received + "," + (int)ClaimProcStatus.Supplemental + "," + (int)ClaimProcStatus.NotReceived + ") "//received or supplemental or notreceived
+ "GROUP BY ClinicNum,DATE(claimproc.ProcDate) "
+ "ORDER BY ClinicNum,ProcDate";
}
else // writeoffPayType==PPOWriteoffDateCalc.ClaimPayDate
{
command = "SELECT "
+ "claimsnapshot.DateTEntry Date,"
+ "claimproc.ClinicNum,"
+ "SUM(CASE WHEN claimsnapshot.WriteOff=-1 THEN 0 ELSE claimsnapshot.WriteOff END) WriteOff "
+ "FROM claimproc "
+ "INNER JOIN claimsnapshot ON claimsnapshot.ClaimProcNum=claimProc.ClaimProcNum "
+ "WHERE " + DbHelper.BetweenDates("claimsnapshot.DateTEntry", dateFrom, dateTo) + " "
+ whereProv
+ whereClin
+ "AND claimproc.Status IN (" + (int)ClaimProcStatus.Received + "," + (int)ClaimProcStatus.Supplemental + "," + (int)ClaimProcStatus.NotReceived + ") "//received or supplemental or notreceived
+ "GROUP BY ClinicNum,DATE(claimsnapshot.DateTEntry) "
+ "ORDER BY ClinicNum,claimsnapshot.DateTEntry";
}
DataTable tableInsWriteoff = new DataTable();
if (isCEMT)
{
tableInsWriteoff = Db.GetTable(command);
}
else
{
tableInsWriteoff = ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(command));
}
tableInsWriteoff.TableName = "tableInsWriteoff";
#endregion
#region TableSched
DataTable tableSched = new DataTable();
//Reads from the procedurelog table instead of claimproc because we are looking for scheduled procedures.
if (!hasAllProvs && listProvNums.Count > 0)
{
whereProv = "AND procedurelog.ProvNum IN (" + string.Join(",", listProvNums) + ") ";
}
if (!hasAllClinics && listClinicNums.Count > 0)
{
whereClin = "AND procedurelog.ClinicNum IN (" + string.Join(",", listClinicNums) + ") ";
}
command = "SELECT " + DbHelper.DtimeToDate("t.AptDateTime") + " SchedDate,SUM(t.Fee-t.WriteoffEstimate) Amount,ClinicNum "
+ "FROM (SELECT appointment.AptDateTime,IFNULL(procedurelog.ProcFee*(procedurelog.UnitQty+procedurelog.BaseUnits),0) Fee,appointment.ClinicNum,";
if (ReportsComplex.RunFuncOnReportServer(() => Prefs.GetBoolNoCache(PrefName.ReportPandIschedProdSubtractsWO)))
{
//Subtract both PPO and capitation writeoffs
command += "SUM(IFNULL(CASE WHEN WriteOffEstOverride != -1 THEN WriteOffEstOverride ELSE WriteOffEst END,0)) WriteoffEstimate ";
}
else
{
//Always subtract CapEstimate writeoffs from scheduled production. This is so that the scheduled production will match actual production
//when the procedures are set complete. Nathan decided this 01/05/2017.
command += "SUM(IFNULL((CASE WHEN claimproc.Status=" + POut.Int((int)ClaimProcStatus.Estimate) + " THEN 0 "
+ "WHEN WriteOffEstOverride != -1 THEN WriteOffEstOverride ELSE WriteOffEst END),0)) WriteoffEstimate ";
}
command += "FROM appointment "
+ "LEFT JOIN procedurelog ON appointment.AptNum = procedurelog.AptNum AND procedurelog.ProcStatus=" + POut.Int((int)ProcStat.TP) + " "
+ "LEFT JOIN claimproc ON procedurelog.ProcNum = claimproc.ProcNum "
+ "AND claimproc.Status IN(" + POut.Int((int)ClaimProcStatus.Estimate) + "," + POut.Int((int)ClaimProcStatus.CapEstimate) + ") "
+ " AND (WriteOffEst != -1 OR WriteOffEstOverride != -1) "
+ "WHERE appointment.AptStatus = " + POut.Int((int)ApptStatus.Scheduled) + " "
+ "AND " + DbHelper.DtimeToDate("appointment.AptDateTime") + " >= " + POut.Date(dateFrom) + " "
+ "AND " + DbHelper.DtimeToDate("appointment.AptDateTime") + " <= " + POut.Date(dateTo) + " "
+ whereProv
+ whereClin
+ " GROUP BY procedurelog.ProcNum) t " //without this, there can be duplicate proc rows due to the claimproc join with dual insurance.
+ "GROUP BY SchedDate,ClinicNum "
+ "ORDER BY SchedDate";
if (isCEMT)
{
tableSched = Db.GetTable(command);
}
else
{
tableSched = ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(command));
}
tableSched.TableName = "tableSched";
#endregion
#region TableProdGoal
//1. Find all schedules for the month
//2. ClinicNum will come from the schedule's operatory
//3. Fetch HourlyProdGoalAmt from provider on the schedule
//4. Sum scheduled hours, grouped by prov and clinic
//5. Multiply the scheduled hours by the provider's HourlyProdGoalAmt
DataTable tableProdGoal = new DataTable();
if (!hasAllProvs && listProvNums.Count > 0)
{
whereProv = "AND schedule.ProvNum IN (" + string.Join(",", listProvNums) + ") ";
}
if (!hasAllClinics && listClinicNums.Count > 0)
{
whereClin = "AND COALESCE(operatory.ClinicNum,0) IN (" + string.Join(",", listClinicNums) + ") ";
}
//Fetch all schedules for the month and associated information (clinic from operatory, HourlyProdGoalAmt from provider)
command = "SELECT " + DbHelper.DtimeToDate("schedule.SchedDate") + @" AS SchedDate, schedule.StartTime AS StartTime, schedule.StopTime AS StopTime,
COALESCE(operatory.ClinicNum,0) AS ClinicNum, provider.HourlyProdGoalAmt AS ProvProdGoal, provider.ProvNum AS ProvNum
FROM schedule
INNER JOIN provider ON provider.ProvNum=schedule.ProvNum
LEFT JOIN scheduleop ON scheduleop.ScheduleNum=schedule.ScheduleNum
LEFT JOIN operatory ON scheduleop.OperatoryNum=operatory.OperatoryNum
WHERE schedule.SchedType=" + POut.Int((int)ScheduleType.Provider) + " "
+ "AND schedule.Status=" + POut.Int((int)SchedStatus.Open) + " "
+ "AND schedule." + DbHelper.BetweenDates("SchedDate", dateFrom, dateTo) + " "
+ whereProv
+ whereClin;
if (isCEMT)
{
tableProdGoal = Db.GetTable(command);
}
else
{
tableProdGoal = ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(command));
}
tableProdGoal.TableName = "tableProdGoal";
#endregion
#region WriteOffAdjustments
DataTable tableWriteOffAdjustments = new DataTable();
if (!hasAllProvs && listProvNums.Count > 0)
{
whereProv = "AND claimproc.ProvNum IN (" + string.Join(",", listProvNums) + ") ";
}
if (!hasAllClinics && listClinicNums.Count > 0)
{
whereClin = "AND claimproc.ClinicNum IN (" + string.Join(",", listClinicNums) + ") ";
}
if (writeoffPayType == PPOWriteoffDateCalc.ClaimPayDate)
{
//Insurance WriteOff Adjustments----------------------------------------------------------------------------
command = $@"SELECT claimproc.DateCP Date,claimproc.ClinicNum,
-SUM(CASE WHEN COALESCE(claimsnapshot.WriteOff,-1)=-1 THEN 0 ELSE claimsnapshot.WriteOff END) WriteOffEst,
SUM(claimproc.WriteOff) WriteOff,
claimproc.ClaimNum
FROM claimproc
LEFT JOIN claimsnapshot ON claimsnapshot.ClaimProcNum=claimproc.ClaimProcNum
WHERE claimproc.DateCP BETWEEN {POut.Date(dateFrom)} AND {POut.Date(dateTo)}
AND claimproc.Status IN ({(int)ClaimProcStatus.Received},{(int)ClaimProcStatus.Supplemental})
{whereProv}
{whereClin}
GROUP BY ClinicNum,DATE(claimproc.DateCP)" ;
if (isCEMT)
{
tableWriteOffAdjustments = Db.GetTable(command);
}
else
{
tableWriteOffAdjustments = ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(command));
}
}
tableWriteOffAdjustments.TableName = "tableWriteOffAdjustments";
#endregion WriteOffAdjustments
DataSet dataSet = new DataSet();
dataSet.Tables.Add(tableProduction);
dataSet.Tables.Add(tableAdj);
dataSet.Tables.Add(tableInsWriteoff);
dataSet.Tables.Add(tableWriteOffAdjustments);
dataSet.Tables.Add(tableSched);
dataSet.Tables.Add(tableProdGoal);
return(dataSet);
}
public static List <List <int> > GetProdProvs(DateTime dateFrom, DateTime dateTo)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <List <int> > >(MethodBase.GetCurrentMethod(), dateFrom, dateTo));
}
Random rnd = new Random();
string rndStr = rnd.Next(1000000).ToString();
string command;
#if DEBUG
_elapsedTimeProdProvs = "";
System.Diagnostics.Stopwatch stopWatch = new System.Diagnostics.Stopwatch();
System.Diagnostics.Stopwatch stopWatchTotal = new System.Diagnostics.Stopwatch();
_elapsedTimeProdProvs = "Elapsed time for GetProdProvs:\r\n";
stopWatch.Restart();
stopWatchTotal.Restart();
#endif
command = "DROP TABLE IF EXISTS tempdash" + rndStr + @";";
Db.NonQ(command);
#if DEBUG
stopWatch.Stop();
_elapsedTimeProdProvs += "DROP TABLE: " + stopWatch.Elapsed.ToString() + "\r\n";
stopWatch.Restart();
#endif
//this table will contain approx 12x3xProv rows if there was production for each prov in each month.
command = @"CREATE TABLE tempdash" + rndStr + @" (
DatePeriod date NOT NULL,
ProvNum bigint NOT NULL,
production decimal NOT NULL
) DEFAULT CHARSET=utf8" ;
Db.NonQ(command);
#if DEBUG
stopWatch.Stop();
_elapsedTimeProdProvs += "CREATE TABLE: " + stopWatch.Elapsed.ToString() + "\r\n";
stopWatch.Restart();
#endif
//procs. Inserts approx 12xProv rows
command = @"INSERT INTO tempdash" + rndStr + @"
SELECT procedurelog.ProcDate,procedurelog.ProvNum,
SUM(procedurelog.ProcFee*(procedurelog.UnitQty+procedurelog.BaseUnits))-IFNULL(SUM(claimproc.WriteOff),0)
FROM procedurelog USE INDEX(indexPNPD)
LEFT JOIN claimproc ON procedurelog.ProcNum=claimproc.ProcNum
AND claimproc.Status='7' /*only CapComplete writeoffs are subtracted here*/
WHERE procedurelog.ProcStatus = '2'
AND procedurelog.ProcDate >= " + POut.Date(dateFrom) + @"
AND procedurelog.ProcDate <= " + POut.Date(dateTo) + @"
GROUP BY procedurelog.ProvNum,MONTH(procedurelog.ProcDate)" ;
Db.NonQ(command);
#if DEBUG
stopWatch.Stop();
_elapsedTimeProdProvs += "INSERT INTO: " + stopWatch.Elapsed.ToString() + "\r\n";
stopWatch.Restart();
#endif
//todo 2 more tables
//get all the data as 12xProv rows
command = @"SELECT DatePeriod,ProvNum,SUM(production) prod
FROM tempdash" + rndStr + @"
GROUP BY ProvNum,MONTH(DatePeriod)" ;//this fails with date issue
DataTable tableProd = Db.GetTable(command);
#if DEBUG
stopWatch.Stop();
_elapsedTimeProdProvs += "tableProd: " + stopWatch.Elapsed.ToString() + "\r\n";
stopWatch.Restart();
#endif
command = "DROP TABLE IF EXISTS tempdash" + rndStr + @";";
#if DEBUG
stopWatch.Stop();
_elapsedTimeProdProvs += "DROP TABLE: " + stopWatch.Elapsed.ToString() + "\r\n";
stopWatch.Restart();
#endif
Db.NonQ(command);
command = @"SELECT ProvNum
FROM provider WHERE IsHidden=0
ORDER BY ItemOrder" ;
DataTable tableProv = Db.GetTable(command);
#if DEBUG
stopWatch.Stop();
stopWatchTotal.Stop();
_elapsedTimeProdProvs += "SELECT ProvNum FROM provider: " + stopWatch.Elapsed.ToString() + "\r\n";
_elapsedTimeProdProvs += "Total: " + stopWatchTotal.Elapsed.ToString();
if (_showElapsedTimesForDebug)
{
System.Windows.Forms.MessageBox.Show(_elapsedTimeProdProvs);
}
#endif
List <List <int> > retVal = new List <List <int> >();
for (int p = 0; p < tableProv.Rows.Count; p++) //loop through each provider
{
long provNum = PIn.Long(tableProv.Rows[p]["ProvNum"].ToString());
List <int> listInt = new List <int>(); //12 items
for (int i = 0; i < 12; i++)
{
decimal prod = 0;
DateTime datePeriod = dateFrom.AddMonths(i); //only the month and year are important
for (int j = 0; j < tableProd.Rows.Count; j++)
{
if (provNum == PIn.Long(tableProd.Rows[j]["ProvNum"].ToString()) &&
datePeriod.Month == PIn.Date(tableProd.Rows[j]["DatePeriod"].ToString()).Month &&
datePeriod.Year == PIn.Date(tableProd.Rows[j]["DatePeriod"].ToString()).Year)
{
prod = PIn.Decimal(tableProd.Rows[j]["prod"].ToString());
break;
}
}
listInt.Add((int)(prod));
}
retVal.Add(listInt);
}
return(retVal);
}
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);
}
///<summary>Returns a 7 column data table in a calendar layout so all you have to do is draw it on the screen. If includePractice is true, then practice notes and holidays will be included.</summary>
public static DataTable GetPeriod(DateTime dateStart, DateTime dateEnd, List <long> provNums, List <long> empNums, bool includePractice)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateStart, dateEnd, provNums, empNums, includePractice));
}
DataTable table = new DataTable();
DataRow row;
table.Columns.Add("sun");
table.Columns.Add("mon");
table.Columns.Add("tues");
table.Columns.Add("wed");
table.Columns.Add("thurs");
table.Columns.Add("fri");
table.Columns.Add("sat");
if (provNums.Count == 0 && empNums.Count == 0 && !includePractice)
{
return(table);
}
string command = "SELECT Abbr,employee.FName,Note,SchedDate,SchedType,Status,StartTime,StopTime "
+ "FROM schedule "
+ "LEFT JOIN provider ON schedule.ProvNum=provider.ProvNum "
+ "LEFT JOIN employee ON schedule.EmployeeNum=employee.EmployeeNum "
+ "WHERE SchedDate >= " + POut.Date(dateStart) + " "
+ "AND SchedDate <= " + POut.Date(dateEnd) + " "
+ "AND (";
string orClause = ""; //this is guaranteed to be non empty by the time the command is assembled.
if (includePractice)
{
orClause = "SchedType=0 ";
}
for (int i = 0; i < provNums.Count; i++)
{
if (orClause != "")
{
orClause += "OR ";
}
orClause += "schedule.ProvNum=" + POut.Long(provNums[i]) + " ";
}
for (int i = 0; i < empNums.Count; i++)
{
if (orClause != "")
{
orClause += "OR ";
}
orClause += "schedule.EmployeeNum=" + POut.Long(empNums[i]) + " ";
}
command += orClause + ") ";
//if(FormChooseDatabase.DBtype==DatabaseType.Oracle){
// command+="";
//}
//else{
command += "ORDER BY SchedDate,employee.FName,provider.ItemOrder,StartTime";
//}
DataTable raw = Db.GetTable(command);
DateTime dateSched;
DateTime startTime;
DateTime stopTime;
int rowsInGrid = GetRowCal(dateStart, dateEnd) + 1; //because 0-based
for (int i = 0; i < rowsInGrid; i++)
{
row = table.NewRow();
table.Rows.Add(row);
}
dateSched = dateStart;
while (dateSched <= dateEnd)
{
table.Rows[GetRowCal(dateStart, dateSched)][(int)dateSched.DayOfWeek] =
dateSched.ToString("MMM d, yyyy");
dateSched = dateSched.AddDays(1);
}
int rowI;
for (int i = 0; i < raw.Rows.Count; i++)
{
dateSched = PIn.Date(raw.Rows[i]["SchedDate"].ToString());
startTime = PIn.DateT(raw.Rows[i]["StartTime"].ToString());
stopTime = PIn.DateT(raw.Rows[i]["StopTime"].ToString());
rowI = GetRowCal(dateStart, dateSched);
if (i != 0 && //not first row
raw.Rows[i - 1]["Abbr"].ToString() == raw.Rows[i]["Abbr"].ToString() && //same provider as previous row
raw.Rows[i - 1]["FName"].ToString() == raw.Rows[i]["FName"].ToString() && //same employee as previous row
raw.Rows[i - 1]["SchedDate"].ToString() == raw.Rows[i]["SchedDate"].ToString()) //and same date as previous row
{
table.Rows[rowI][(int)dateSched.DayOfWeek] += ", ";
if (startTime.TimeOfDay == PIn.DateT("12 AM").TimeOfDay &&
stopTime.TimeOfDay == PIn.DateT("12 AM").TimeOfDay)
{
if (raw.Rows[i]["Status"].ToString() == "2") //if holiday
{
table.Rows[rowI][(int)dateSched.DayOfWeek] += Lans.g("Schedules", "Holiday:");
}
}
else
{
table.Rows[rowI][(int)dateSched.DayOfWeek] += startTime.ToString("h:mm") + "-" + stopTime.ToString("h:mm");
}
}
else
{
table.Rows[rowI][(int)dateSched.DayOfWeek] += "\r\n";
if (startTime.TimeOfDay == PIn.DateT("12 AM").TimeOfDay &&
stopTime.TimeOfDay == PIn.DateT("12 AM").TimeOfDay)
{
if (raw.Rows[i]["Status"].ToString() == "2") //if holiday
{
table.Rows[rowI][(int)dateSched.DayOfWeek] += Lans.g("Schedules", "Holiday:"); //+raw.Rows[i]["Note"].ToString();
}
else //note
{
if (raw.Rows[i]["Abbr"].ToString() != "")
{
table.Rows[rowI][(int)dateSched.DayOfWeek] += raw.Rows[i]["Abbr"].ToString() + " ";
}
if (raw.Rows[i]["FName"].ToString() != "")
{
table.Rows[rowI][(int)dateSched.DayOfWeek] += raw.Rows[i]["FName"].ToString() + " ";
}
//table.Rows[rowI][(int)dateSched.DayOfWeek]+=raw.Rows[i]["Note"].ToString();
}
}
else
{
if (raw.Rows[i]["Abbr"].ToString() != "")
{
table.Rows[rowI][(int)dateSched.DayOfWeek] += raw.Rows[i]["Abbr"].ToString() + " ";
}
if (raw.Rows[i]["FName"].ToString() != "")
{
table.Rows[rowI][(int)dateSched.DayOfWeek] += raw.Rows[i]["FName"].ToString() + " ";
}
table.Rows[rowI][(int)dateSched.DayOfWeek] +=
startTime.ToString("h:mm") + "-" + stopTime.ToString("h:mm");
}
}
if (raw.Rows[i]["Note"].ToString() != "")
{
table.Rows[rowI][(int)dateSched.DayOfWeek] += " " + raw.Rows[i]["Note"].ToString();
}
}
return(table);
}
