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