private static List <VersionRelease> RefreshAndFill(string command) { DataTable table = Db.GetTable(command); List <VersionRelease> retVal = new List <VersionRelease>(); VersionRelease vers; for (int i = 0; i < table.Rows.Count; i++) { vers = new VersionRelease(); vers.VersionReleaseId = PIn.Int(table.Rows[i]["VersionReleaseId"].ToString()); vers.MajorNum = PIn.Int(table.Rows[i]["MajorNum"].ToString()); vers.MinorNum = PIn.Int(table.Rows[i]["MinorNum"].ToString()); vers.BuildNum = PIn.Int(table.Rows[i]["BuildNum"].ToString()); vers.IsForeign = PIn.Bool(table.Rows[i]["IsForeign"].ToString()); vers.DateRelease = PIn.Date(table.Rows[i]["DateRelease"].ToString()); vers.IsBeta = PIn.Bool(table.Rows[i]["IsBeta"].ToString()); vers.HasConvertScript = PIn.Bool(table.Rows[i]["HasConvertScript"].ToString()); retVal.Add(vers); } return(retVal); }
public static RegistrationKey GetByKey(string regKey) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetObject <RegistrationKey>(MethodBase.GetCurrentMethod(), regKey)); } if (!Regex.IsMatch(regKey, @"^[A-Z0-9]{16}$")) { throw new ApplicationException("Invalid registration key format."); } string command = "SELECT * FROM registrationkey WHERE RegKey='" + POut.String(regKey) + "'"; DataTable table = Db.GetTable(command); if (table.Rows.Count == 0) { throw new ApplicationException("Invalid registration key."); } RegistrationKey key = null; for (int i = 0; i < table.Rows.Count; i++) { key = new RegistrationKey(); key.RegistrationKeyNum = PIn.Int(table.Rows[i][0].ToString()); key.PatNum = PIn.Int(table.Rows[i][1].ToString()); key.RegKey = PIn.String(table.Rows[i][2].ToString()); key.Note = PIn.String(table.Rows[i][3].ToString()); key.DateStarted = PIn.Date(table.Rows[i][4].ToString()); key.DateDisabled = PIn.Date(table.Rows[i][5].ToString()); key.DateEnded = PIn.Date(table.Rows[i][6].ToString()); key.IsForeign = PIn.Bool(table.Rows[i][7].ToString()); //key.UsesServerVersion =PIn.PBool(table.Rows[i][8].ToString()); key.IsFreeVersion = PIn.Bool(table.Rows[i][9].ToString()); key.IsOnlyForTesting = PIn.Bool(table.Rows[i][10].ToString()); //key.VotesAllotted =PIn.PInt(table.Rows[i][11].ToString()); } //if(key.DateDisabled.Year>1880){ // throw new ApplicationException("This key has been disabled. Please call for assistance."); //} return(key); }
///<Summary>asOfDate is typically 12/31/... </Summary> public static double NetIncomeThisYear(object asOfDateObj) { DateTime asOfDate; if (asOfDateObj.GetType() == typeof(string)) { asOfDate = PIn.Date(asOfDateObj.ToString()); } else if (asOfDateObj.GetType() == typeof(DateTime)) { asOfDate = (DateTime)asOfDateObj; } else { return(0); } DateTime firstOfYear = new DateTime(asOfDate.Year, 1, 1); string command = "SELECT SUM(ROUND(CreditAmt,3)), SUM(ROUND(DebitAmt,3)), AcctType " + "FROM journalentry,account " + "WHERE journalentry.AccountNum=account.AccountNum " + "AND DateDisplayed >= " + POut.Date(firstOfYear) + " AND DateDisplayed <= " + POut.Date(asOfDate) + " GROUP BY AcctType"; DataTable table = Db.GetTable(command); double retVal = 0; for (int i = 0; i < table.Rows.Count; i++) { if (table.Rows[i][2].ToString() == "3" || //income table.Rows[i][2].ToString() == "4") //expense { retVal += PIn.Double(table.Rows[i][0].ToString()); //add credit retVal -= PIn.Double(table.Rows[i][1].ToString()); //subtract debit //if it's an expense, we are subtracting (income-expense), but the signs cancel. } } return(retVal); }
///<summary>Returns only 5 columns for all saved treatment plans.</summary> public static List <TreatPlan> GetAllSavedLim() { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetObject <List <TreatPlan> >(MethodBase.GetCurrentMethod())); } string command = "SELECT TreatPlanNum, PatNum, DateTP, SecUserNumEntry, UserNumPresenter " + " FROM treatplan WHERE treatplan.TPStatus=" + POut.Int((int)TreatPlanStatus.Saved); DataTable table = Db.GetTable(command); List <TreatPlan> listSavedTreatPlanLim = new List <TreatPlan>(); foreach (DataRow row in table.Rows) { TreatPlan treatPlanCur = new TreatPlan(); treatPlanCur.TreatPlanNum = PIn.Long(row["TreatPlanNum"].ToString()); treatPlanCur.PatNum = PIn.Long(row["PatNum"].ToString()); treatPlanCur.DateTP = PIn.Date(row["DateTP"].ToString()); treatPlanCur.SecUserNumEntry = PIn.Long(row["SecUserNumEntry"].ToString()); treatPlanCur.UserNumPresenter = PIn.Long(row["UserNumPresenter"].ToString()); listSavedTreatPlanLim.Add(treatPlanCur); } return(listSavedTreatPlanLim); }
///<summary></summary> private static List <ClaimPaySplit> ClaimPaySplitTableToList(DataTable table) { //No need to check RemotingRole; no call to db. List <ClaimPaySplit> splits = new List <ClaimPaySplit>(); ClaimPaySplit split; for (int i = 0; i < table.Rows.Count; i++) { split = new ClaimPaySplit(); split.DateClaim = PIn.Date(table.Rows[i]["DateService"].ToString()); split.ProvAbbr = Providers.GetAbbr(PIn.Long(table.Rows[i]["ProvTreat"].ToString())); split.PatName = PIn.String(table.Rows[i]["patName_"].ToString()); split.PatNum = PIn.Long(table.Rows[i]["PatNum"].ToString()); split.Carrier = PIn.String(table.Rows[i]["CarrierName"].ToString()); split.FeeBilled = PIn.Double(table.Rows[i]["feeBilled_"].ToString()); split.InsPayAmt = PIn.Double(table.Rows[i]["insPayAmt_"].ToString()); split.ClaimNum = PIn.Long(table.Rows[i]["ClaimNum"].ToString()); split.ClaimPaymentNum = PIn.Long(table.Rows[i]["ClaimPaymentNum"].ToString()); split.PaymentRow = PIn.Int(table.Rows[i]["PaymentRow"].ToString()); splits.Add(split); } return(splits); }
public static DataTable GetBirthdayTable(DateTime dateFrom, DateTime dateTo) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateFrom, dateTo)); } string dateWhereClause; string orderByClause; if (dateFrom.Year == dateTo.Year) { dateWhereClause = "SUBSTRING(Birthdate,6,5) >= '" + dateFrom.ToString("MM-dd") + "' " + "AND SUBSTRING(Birthdate,6,5) <= '" + dateTo.ToString("MM-dd") + "' "; orderByClause = "MONTH(Birthdate),DAY(Birthdate)"; } else //The date range spans more than 1 calendar year { dateWhereClause = "(SUBSTRING(Birthdate,6,5) >= '" + dateFrom.ToString("MM-dd") + "' " + "OR SUBSTRING(Birthdate,6,5) <= '" + dateTo.ToString("MM-dd") + "') "; orderByClause = "SUBSTRING(Birthdate,6,5) < '" + dateFrom.ToString("MM-dd") + "',MONTH(Birthdate),DAY(Birthdate)"; } string command = "SELECT LName,FName,Preferred,Address,Address2,City,State,Zip,Birthdate " + "FROM patient " + "WHERE " + dateWhereClause + " " + "AND Birthdate > '1880-01-01' " + "AND PatStatus=0 " + "ORDER BY " + orderByClause; DataTable table = ReportsComplex.RunFuncOnReportServer(() => ReportsComplex.GetTable(command)); table.Columns.Add("Age"); for (int i = 0; i < table.Rows.Count; i++) { table.Rows[i]["Age"] = Patients.DateToAge(PIn.Date(table.Rows[i]["Birthdate"].ToString()), dateTo).ToString(); } return(table); }
///<summary>For orderBy, use 0 for BillingType and 1 for PatientName.</summary> public static DataTable GetBilling(bool isSent, int orderBy, DateTime dateFrom, DateTime dateTo, long clinicNum) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetTable(MethodBase.GetCurrentMethod(), isSent, orderBy, dateFrom, dateTo, clinicNum)); } DataTable table = new DataTable(); DataRow row; //columns that start with lowercase are altered for display rather than being raw data. table.Columns.Add("amountDue"); table.Columns.Add("balTotal"); table.Columns.Add("billingType"); table.Columns.Add("insEst"); table.Columns.Add("IsSent"); table.Columns.Add("lastStatement"); table.Columns.Add("mode"); table.Columns.Add("name"); table.Columns.Add("PatNum"); table.Columns.Add("payPlanDue"); table.Columns.Add("StatementNum"); List <DataRow> rows = new List <DataRow>(); string command = "SELECT BalTotal,BillingType,FName,InsEst,statement.IsSent," + "IFNULL(MAX(s2.DateSent)," + POut.Date(DateTime.MinValue) + ") LastStatement," + "LName,MiddleI,statement.Mode_,PayPlanDue,Preferred," + "statement.PatNum,statement.StatementNum " + "FROM statement " + "LEFT JOIN patient ON statement.PatNum=patient.PatNum " + "LEFT JOIN statement s2 ON s2.PatNum=patient.PatNum " + "AND s2.IsSent=1 "; if (PrefC.GetBool(PrefName.BillingIgnoreInPerson)) { command += "AND s2.Mode_ !=1 "; } if (orderBy == 0) //BillingType { command += "LEFT JOIN definition ON patient.BillingType=definition.DefNum "; } command += "WHERE statement.IsSent=" + POut.Bool(isSent) + " "; //if(dateFrom.Year>1800){ command += "AND statement.DateSent>=" + POut.Date(dateFrom) + " "; //greater than midnight this morning //} //if(dateFrom.Year>1800){ command += "AND statement.DateSent<" + POut.Date(dateTo.AddDays(1)) + " "; //less than midnight tonight //} if (clinicNum > 0) { command += "AND patient.ClinicNum=" + clinicNum + " "; } command += "GROUP BY BalTotal,BillingType,FName,InsEst,statement.IsSent," + "LName,MiddleI,statement.Mode_,PayPlanDue,Preferred," + "statement.PatNum,statement.StatementNum "; if (orderBy == 0) //BillingType { command += "ORDER BY definition.ItemOrder,LName,FName,MiddleI,PayPlanDue"; } else { command += "ORDER BY LName,FName"; } DataTable rawTable = Db.GetTable(command); Patient pat; StatementMode mode; double balTotal; double insEst; double payPlanDue; DateTime lastStatement; for (int i = 0; i < rawTable.Rows.Count; i++) { row = table.NewRow(); balTotal = PIn.Double(rawTable.Rows[i]["BalTotal"].ToString()); insEst = PIn.Double(rawTable.Rows[i]["InsEst"].ToString()); payPlanDue = PIn.Double(rawTable.Rows[i]["PayPlanDue"].ToString()); row["amountDue"] = (balTotal - insEst).ToString("F"); row["balTotal"] = balTotal.ToString("F");; row["billingType"] = DefC.GetName(DefCat.BillingTypes, PIn.Long(rawTable.Rows[i]["BillingType"].ToString())); if (insEst == 0) { row["insEst"] = ""; } else { row["insEst"] = insEst.ToString("F"); } row["IsSent"] = rawTable.Rows[i]["IsSent"].ToString(); lastStatement = PIn.Date(rawTable.Rows[i]["LastStatement"].ToString()); if (lastStatement.Year < 1880) { row["lastStatement"] = ""; } else { row["lastStatement"] = lastStatement.ToShortDateString(); } mode = (StatementMode)PIn.Long(rawTable.Rows[i]["Mode_"].ToString()); row["mode"] = Lans.g("enumStatementMode", mode.ToString()); pat = new Patient(); pat.LName = rawTable.Rows[i]["LName"].ToString(); pat.FName = rawTable.Rows[i]["FName"].ToString(); pat.Preferred = rawTable.Rows[i]["Preferred"].ToString(); pat.MiddleI = rawTable.Rows[i]["MiddleI"].ToString(); row["name"] = pat.GetNameLF(); row["PatNum"] = rawTable.Rows[i]["PatNum"].ToString(); if (payPlanDue == 0) { row["payPlanDue"] = ""; } else { row["payPlanDue"] = payPlanDue.ToString("F"); } row["StatementNum"] = rawTable.Rows[i]["StatementNum"].ToString(); rows.Add(row); } for (int i = 0; i < rows.Count; i++) { table.Rows.Add(rows[i]); } return(table); }
public static DataTable GetOverPaidProcs(long patNum, List <long> listProvNums, List <long> listClinics, DateTime dateStart, DateTime dateEnd) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetTable(MethodBase.GetCurrentMethod(), patNum, listProvNums, listClinics, dateStart, dateEnd)); } List <long> listHiddenUnearnedDefNums = ReportsComplex.RunFuncOnReportServer(() => Defs.GetDefsNoCache(DefCat.PaySplitUnearnedType).FindAll(x => !string.IsNullOrEmpty(x.ItemValue)).Select(x => x.DefNum).ToList() ); #region Completed Procs string command = "SELECT "; if (PrefC.GetBool(PrefName.ReportsShowPatNum)) { command += DbHelper.Concat("CAST(patient.PatNum AS CHAR)", "'-'", "patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI"); } else { command += DbHelper.Concat("patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI"); } command += @" AS 'patientName', procedurelog.ProcDate, procedurecode.ProcCode, procedurelog.ToothNum, provider.Abbr, (procedurelog.ProcFee*(procedurelog.UnitQty+procedurelog.BaseUnits)) AS fee, patient.PatNum, procedurelog.ProcNum FROM procedurelog INNER JOIN patient ON patient.PatNum=procedurelog.PatNum INNER JOIN procedurecode ON procedurecode.CodeNum=procedurelog.CodeNum INNER JOIN provider ON provider.ProvNum=procedurelog.ProvNum WHERE procedurelog.ProcStatus=" + POut.Int((int)ProcStat.C) + " AND " + DbHelper.BetweenDates("procedurelog.ProcDate", dateStart, dateEnd) + " " + "AND procedurelog.ProcFee>0 "; if (listProvNums != null && listProvNums.Count > 0) { command += "AND procedurelog.ProvNum IN (" + string.Join(",", listProvNums.Select(x => POut.Long(x))) + ") "; } if (listClinics != null && listClinics.Count > 0) { command += "AND procedurelog.ClinicNum IN (" + string.Join(",", listClinics.Select(x => POut.Long(x))) + ") "; } if (patNum > 0) { command += "AND procedurelog.PatNum=" + POut.Long(patNum) + " "; } command += "ORDER BY procedurelog.ProcDate,patientName,procedurecode.ProcCode,provider.Abbr"; DataTable rawCompletedProcTable = Db.GetTable(command); Dictionary <long, DataRow> dictCompletedProcRows = rawCompletedProcTable.Select().ToDictionary(x => PIn.Long(x["ProcNum"].ToString())); #endregion DataTable table = new DataTable(); if (dictCompletedProcRows.Count == 0) { return(table); } #region ClaimProcs List <long> listPatNums = rawCompletedProcTable.Select().Select(x => PIn.Long(x["PatNum"].ToString())).Distinct().ToList(); command = @"SELECT MIN(claimproc.ProcNum) ProcNum,MIN(claimproc.PatNum) PatNum,MIN(claimproc.ProcDate) ProcDate,SUM(claimproc.InsPayAmt) insPayAmt, SUM(claimproc.Writeoff) writeoff FROM claimproc WHERE claimproc.Status NOT IN(" + string.Join(",", new List <int> { (int)ClaimProcStatus.Preauth, (int)ClaimProcStatus.CapEstimate, (int)ClaimProcStatus.CapComplete, (int)ClaimProcStatus.Estimate, (int)ClaimProcStatus.InsHist } .Select(x => POut.Int(x))) + ") " + "AND " + DbHelper.BetweenDates("claimproc.ProcDate", dateStart, dateEnd) + " " + "AND claimproc.PatNum IN(" + string.Join(",", listPatNums.Select(x => POut.Long(x))) + ") " + @"GROUP BY claimproc.ProcNum HAVING SUM(claimproc.InsPayAmt+claimproc.Writeoff)>0 ORDER BY NULL" ; Dictionary <long, DataRow> dictClaimProcRows = Db.GetTable(command).Select().ToDictionary(x => PIn.Long(x["ProcNum"].ToString())); #endregion #region Patient Payments command = @"SELECT paysplit.ProcNum,SUM(paysplit.SplitAmt) ptAmt FROM paysplit WHERE paysplit.ProcNum>0 AND paysplit.PatNum IN(" + string.Join(",", listPatNums.Select(x => POut.Long(x))) + $@") "; if (listHiddenUnearnedDefNums.Count > 0) { command += $"AND paysplit.UnearnedType NOT IN ({string.Join(",",listHiddenUnearnedDefNums)}) "; } command += @" GROUP BY paysplit.ProcNum ORDER BY NULL" ; Dictionary <long, DataRow> dictPatPayRows = Db.GetTable(command).Select().ToDictionary(x => PIn.Long(x["ProcNum"].ToString())); #endregion #region Adjustments command = @"SELECT adjustment.ProcNum,SUM(adjustment.AdjAmt) AdjAmt FROM adjustment WHERE adjustment.ProcNum>0 AND adjustment.PatNum IN(" + string.Join(",", listPatNums.Select(x => POut.Long(x))) + @") GROUP BY adjustment.ProcNum ORDER BY NULL" ; Dictionary <long, DataRow> dictAdjRows = Db.GetTable(command).Select().ToDictionary(x => PIn.Long(x["ProcNum"].ToString())); #endregion //columns that start with lowercase are altered for display rather than being raw data. table.Columns.Add("patientName"); table.Columns.Add("ProcDate", typeof(DateTime)); table.Columns.Add("ProcCode"); table.Columns.Add("ToothNum"); table.Columns.Add("Abbr"); table.Columns.Add("fee"); table.Columns.Add("insPaid"); table.Columns.Add("wo"); table.Columns.Add("ptPaid"); table.Columns.Add("adjAmt"); table.Columns.Add("overPay"); table.Columns.Add("PatNum"); DataRow row; foreach (KeyValuePair <long, DataRow> kvp in dictCompletedProcRows) { long procNum = kvp.Key; decimal procFeeAmt = PIn.Decimal(kvp.Value["fee"].ToString()); decimal insPaidAmt = 0; decimal woAmt = 0; decimal ptPaidAmt = 0; decimal adjAmt = 0; if (dictClaimProcRows.ContainsKey(procNum)) { insPaidAmt = PIn.Decimal(dictClaimProcRows[procNum]["insPayAmt"].ToString()); woAmt = PIn.Decimal(dictClaimProcRows[procNum]["writeoff"].ToString()); } if (dictPatPayRows.ContainsKey(procNum)) { ptPaidAmt = PIn.Decimal(dictPatPayRows[procNum]["ptAmt"].ToString()); } if (dictAdjRows.ContainsKey(procNum)) { adjAmt = PIn.Decimal(dictAdjRows[procNum]["AdjAmt"].ToString()); } decimal overPay = procFeeAmt - insPaidAmt - woAmt - ptPaidAmt + adjAmt; if (!overPay.IsLessThanZero()) { continue; //No overpayment. Not need to continue; } row = table.NewRow(); row["patientName"] = PIn.String(kvp.Value["patientName"].ToString()); row["ProcDate"] = PIn.Date(kvp.Value["ProcDate"].ToString()); row["ProcCode"] = PIn.String(kvp.Value["ProcCode"].ToString()); row["ToothNum"] = PIn.String(kvp.Value["ToothNum"].ToString()); row["Abbr"] = PIn.String(kvp.Value["Abbr"].ToString());; row["fee"] = procFeeAmt.ToString(); row["insPaid"] = insPaidAmt.ToString(); row["wo"] = woAmt.ToString(); row["ptPaid"] = ptPaidAmt.ToString(); row["adjAmt"] = adjAmt.ToString(); row["overPay"] = overPay.ToString(); row["PatNum"] = PIn.Long(kvp.Value["PatNum"].ToString()); table.Rows.Add(row); } return(table); }
///<Summary></Summary> public static DataTable GetAllWithoutCharges() { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetTable(MethodBase.GetCurrentMethod())); } DataTable table = new DataTable(); table.Columns.Add("dateStop"); table.Columns.Add("family"); table.Columns.Add("PatNum"); table.Columns.Add("RegKey"); string command = @" DROP TABLE IF EXISTS tempRegKeys; CREATE TABLE tempRegKeys( tempRegKeyId int auto_increment NOT NULL, PatNum bigint NOT NULL, RegKey VARCHAR(255) NOT NULL, IsMissing tinyint NOT NULL, Date_ DATE NOT NULL DEFAULT '0001-01-01', PRIMARY KEY(tempRegKeyId), KEY(PatNum)); /*Fill table with patnums for all guarantors of regkeys that are still active.*/ INSERT INTO tempRegKeys (PatNum,RegKey,Date_) SELECT patient.Guarantor,RegKey,'0001-01-01' FROM registrationkey LEFT JOIN patient ON registrationkey.PatNum=patient.PatNum WHERE DateDisabled='0001-01-01' AND DateEnded='0001-01-01' AND IsFreeVersion=0 AND IsOnlyForTesting=0; /*Set indicators on keys with missing repeatcharges*/ UPDATE tempRegKeys SET IsMissing=1 WHERE NOT EXISTS(SELECT * FROM repeatcharge WHERE repeatcharge.PatNum=tempRegKeys.PatNum); /*Now, look for expired repeating charges. This is done in two steps.*/ /*Step 1: Mark all keys that have expired repeating charges.*/ /*Step 2: Then, remove those markings for all keys that also have unexpired repeating charges.*/ UPDATE tempRegKeys SET Date_=( SELECT IFNULL(MAX(DateStop),'0001-01-01') FROM repeatcharge WHERE repeatcharge.PatNum=tempRegKeys.PatNum AND DateStop < " + DbHelper.Now() + @" AND DateStop > '0001-01-01'); /*Step 2:*/ UPDATE tempRegKeys SET Date_='0001-01-01' WHERE EXISTS( SELECT * FROM repeatcharge WHERE repeatcharge.PatNum=tempRegKeys.PatNum AND DateStop = '0001-01-01'); SELECT LName,FName,tempRegKeys.PatNum,tempRegKeys.RegKey,IsMissing,Date_ FROM tempRegKeys LEFT JOIN patient ON patient.PatNum=tempRegKeys.PatNum WHERE IsMissing=1 OR Date_ > '0001-01-01' ORDER BY tempRegKeys.PatNum; DROP TABLE IF EXISTS tempRegKeys;" ; DataTable raw = Db.GetTable(command); DataRow row; DateTime dateRepeatStop; for (int i = 0; i < raw.Rows.Count; i++) { row = table.NewRow(); if (raw.Rows[i]["IsMissing"].ToString() == "1") { row["dateStop"] = "Missing Repeat Charge"; } else { row["dateStop"] = ""; } dateRepeatStop = PIn.Date(raw.Rows[i]["Date_"].ToString()); if (dateRepeatStop.Year > 1880) { if (row["dateStop"].ToString() != "") { row["dateStop"] += "\r\n"; } row["dateStop"] += "Expired Repeat Charge:" + dateRepeatStop.ToShortDateString(); } row["family"] = raw.Rows[i]["LName"].ToString() + ", " + raw.Rows[i]["FName"].ToString(); row["PatNum"] = raw.Rows[i]["PatNum"].ToString(); row["RegKey"] = raw.Rows[i]["RegKey"].ToString(); table.Rows.Add(row); } return(table); }
///<summary>For orderBy, use 0 for BillingType and 1 for PatientName.</summary> public static DataTable GetBilling(bool isSent, int orderBy, DateTime dateFrom, DateTime dateTo, List <long> clinicNums) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetTable(MethodBase.GetCurrentMethod(), isSent, orderBy, dateFrom, dateTo, clinicNums)); } DataTable table = new DataTable(); DataRow row; //columns that start with lowercase are altered for display rather than being raw data. table.Columns.Add("amountDue"); table.Columns.Add("balTotal"); table.Columns.Add("billingType"); table.Columns.Add("insEst"); table.Columns.Add("IsSent"); table.Columns.Add("lastStatement"); table.Columns.Add("mode"); table.Columns.Add("name"); table.Columns.Add("PatNum"); table.Columns.Add("payPlanDue"); table.Columns.Add("StatementNum"); table.Columns.Add("SuperFamily"); table.Columns.Add("ClinicNum"); string command = "SELECT guar.BalTotal,patient.BillingType,patient.FName,guar.InsEst,statement.IsSent," + "IFNULL(MAX(s2.DateSent)," + POut.Date(DateTime.MinValue) + ") LastStatement," + "patient.LName,patient.MiddleI,statement.Mode_,guar.PayPlanDue,patient.Preferred," + "statement.PatNum,statement.StatementNum,statement.SuperFamily,patient.ClinicNum " + "FROM statement " + "LEFT JOIN patient ON statement.PatNum=patient.PatNum " + "LEFT JOIN patient guar ON guar.PatNum=patient.Guarantor " + "LEFT JOIN statement s2 ON s2.PatNum=patient.PatNum " + "AND s2.IsSent=1 "; if (PrefC.GetBool(PrefName.BillingIgnoreInPerson)) { command += "AND s2.Mode_ !=1 "; } if (orderBy == 0) //BillingType { command += "LEFT JOIN definition ON patient.BillingType=definition.DefNum "; } command += "WHERE statement.IsSent=" + POut.Bool(isSent) + " "; //if(dateFrom.Year>1800){ command += "AND statement.DateSent>=" + POut.Date(dateFrom) + " "; //greater than midnight this morning //} //if(dateFrom.Year>1800){ command += "AND statement.DateSent<" + POut.Date(dateTo.AddDays(1)) + " "; //less than midnight tonight //} if (clinicNums.Count > 0) { command += "AND patient.ClinicNum IN (" + string.Join(",", clinicNums) + ") "; } command += "GROUP BY guar.BalTotal,patient.BillingType,patient.FName,guar.InsEst,statement.IsSent," + "patient.LName,patient.MiddleI,statement.Mode_,guar.PayPlanDue,patient.Preferred," + "statement.PatNum,statement.StatementNum,statement.SuperFamily "; if (orderBy == 0) //BillingType { command += "ORDER BY definition.ItemOrder,patient.LName,patient.FName,patient.MiddleI,guar.PayPlanDue"; } else { command += "ORDER BY patient.LName,patient.FName"; } DataTable rawTable = Db.GetTable(command); double balTotal; double insEst; double payPlanDue; DateTime lastStatement; List <Patient> listFamilyGuarantors; foreach (DataRow rawRow in rawTable.Rows) { row = table.NewRow(); if (rawRow["SuperFamily"].ToString() == "0") //not a super statement, just get bal info from guarantor { balTotal = PIn.Double(rawRow["BalTotal"].ToString()); insEst = PIn.Double(rawRow["InsEst"].ToString()); payPlanDue = PIn.Double(rawRow["PayPlanDue"].ToString()); } else //super statement, add all guar positive balances to get bal total for super family { listFamilyGuarantors = Patients.GetSuperFamilyGuarantors(PIn.Long(rawRow["SuperFamily"].ToString())).FindAll(x => x.HasSuperBilling); //exclude fams with neg balances in the total for super family stmts (per Nathan 5/25/2016) if (PrefC.GetBool(PrefName.BalancesDontSubtractIns)) { listFamilyGuarantors = listFamilyGuarantors.FindAll(x => x.BalTotal > 0); insEst = 0; } else { listFamilyGuarantors = listFamilyGuarantors.FindAll(x => (x.BalTotal - x.InsEst) > 0); insEst = listFamilyGuarantors.Sum(x => x.InsEst); } balTotal = listFamilyGuarantors.Sum(x => x.BalTotal); payPlanDue = listFamilyGuarantors.Sum(x => x.PayPlanDue); } row["amountDue"] = (balTotal - insEst).ToString("F"); row["balTotal"] = balTotal.ToString("F");; row["billingType"] = Defs.GetName(DefCat.BillingTypes, PIn.Long(rawRow["BillingType"].ToString())); if (insEst == 0) { row["insEst"] = ""; } else { row["insEst"] = insEst.ToString("F"); } row["IsSent"] = rawRow["IsSent"].ToString(); lastStatement = PIn.Date(rawRow["LastStatement"].ToString()); if (lastStatement.Year < 1880) { row["lastStatement"] = ""; } else { row["lastStatement"] = lastStatement.ToShortDateString(); } row["mode"] = Lans.g("enumStatementMode", ((StatementMode)PIn.Int(rawRow["Mode_"].ToString())).ToString()); row["name"] = Patients.GetNameLF(rawRow["LName"].ToString(), rawRow["FName"].ToString(), rawRow["Preferred"].ToString(), rawRow["MiddleI"].ToString()); row["PatNum"] = rawRow["PatNum"].ToString(); if (payPlanDue == 0) { row["payPlanDue"] = ""; } else { row["payPlanDue"] = payPlanDue.ToString("F"); } row["StatementNum"] = rawRow["StatementNum"].ToString(); row["SuperFamily"] = rawRow["SuperFamily"].ToString(); row["ClinicNum"] = rawRow["ClinicNum"].ToString(); table.Rows.Add(row); } return(table); }
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 if (PrefC.GetBool(PrefName.ReportsPPOwriteoffDefaultToProcDate)) //use 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=1 OR claimproc.Status=4 OR claimproc.Status=0) " //received or supplemental or notreceived + "GROUP BY MONTH(claimproc.ProcDate)"; } else { command = "SELECT " + "claimproc.DateCP," + "SUM(claimproc.WriteOff) " + "FROM claimproc " + "WHERE claimproc.DateCP >= " + POut.Date(dateFrom) + " " + "AND claimproc.DateCP <= " + POut.Date(dateTo) + " " + "AND (claimproc.Status=1 OR claimproc.Status=4) " //Received or supplemental + "GROUP BY MONTH(claimproc.DateCP)"; } 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); }
public static DataTable GetTreeListTableForPatient(string patNum) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetTable(MethodBase.GetCurrentMethod(), patNum)); } DataConnection dcon = new DataConnection(); DataTable table = new DataTable("DocumentList"); DataRow row; DataTable raw; string command; //Rows are first added to the resultSet list so they can be sorted at the end as a larger group, then //they are placed in the datatable to be returned. List <Object> resultSet = new List <Object>(); //columns that start with lowercase are altered for display rather than being raw data. table.Columns.Add("DocNum"); table.Columns.Add("MountNum"); table.Columns.Add("DocCategory"); table.Columns.Add("DateCreated"); table.Columns.Add("docFolder"); //The folder order to which the Document category corresponds. table.Columns.Add("description"); table.Columns.Add("ImgType"); //Move all documents which are invisible to the first document category. command = "SELECT DocNum FROM document WHERE PatNum='" + patNum + "' AND " + "DocCategory<0"; raw = dcon.GetTable(command); if (raw.Rows.Count > 0) //Are there any invisible documents? { command = "UPDATE document SET DocCategory='" + DefC.GetList(DefCat.ImageCats)[0].DefNum + "' WHERE PatNum='" + patNum + "' AND ("; for (int i = 0; i < raw.Rows.Count; i++) { command += "DocNum='" + PIn.Long(raw.Rows[i]["DocNum"].ToString()) + "' "; if (i < raw.Rows.Count - 1) { command += "OR "; } } command += ")"; dcon.NonQ(command); } //Load all documents into the result table. command = "SELECT DocNum,DocCategory,DateCreated,Description,ImgType,MountItemNum FROM document WHERE PatNum='" + patNum + "'"; raw = dcon.GetTable(command); for (int i = 0; i < raw.Rows.Count; i++) { //Make sure hidden documents are never added (there is a small possibility that one is added after all are made visible). if (DefC.GetOrder(DefCat.ImageCats, PIn.Long(raw.Rows[i]["DocCategory"].ToString())) < 0) { continue; } //Do not add individual documents which are part of a mount object. if (PIn.Long(raw.Rows[i]["MountItemNum"].ToString()) != 0) { continue; } row = table.NewRow(); row["DocNum"] = PIn.Long(raw.Rows[i]["DocNum"].ToString()); row["MountNum"] = 0; row["DocCategory"] = PIn.Long(raw.Rows[i]["DocCategory"].ToString()); row["DateCreated"] = PIn.Date(raw.Rows[i]["DateCreated"].ToString()); row["docFolder"] = DefC.GetOrder(DefCat.ImageCats, PIn.Long(raw.Rows[i]["DocCategory"].ToString())); row["description"] = PIn.Date(raw.Rows[i]["DateCreated"].ToString()).ToString("d") + ": " + PIn.String(raw.Rows[i]["Description"].ToString()); row["ImgType"] = PIn.Long(raw.Rows[i]["ImgType"].ToString()); resultSet.Add(row); } //Move all mounts which are invisible to the first document category. command = "SELECT MountNum FROM mount WHERE PatNum='" + patNum + "' AND " + "DocCategory<0"; raw = dcon.GetTable(command); if (raw.Rows.Count > 0) //Are there any invisible mounts? { command = "UPDATE mount SET DocCategory='" + DefC.GetList(DefCat.ImageCats)[0].DefNum + "' WHERE PatNum='" + patNum + "' AND ("; for (int i = 0; i < raw.Rows.Count; i++) { command += "MountNum='" + PIn.Long(raw.Rows[i]["MountNum"].ToString()) + "' "; if (i < raw.Rows.Count - 1) { command += "OR "; } } command += ")"; dcon.NonQ(command); } //Load all mounts into the result table. command = "SELECT MountNum,DocCategory,DateCreated,Description,ImgType FROM mount WHERE PatNum='" + patNum + "'"; raw = dcon.GetTable(command); for (int i = 0; i < raw.Rows.Count; i++) { //Make sure hidden mounts are never added (there is a small possibility that one is added after all are made visible). if (DefC.GetOrder(DefCat.ImageCats, PIn.Long(raw.Rows[i]["DocCategory"].ToString())) < 0) { continue; } row = table.NewRow(); row["DocNum"] = 0; row["MountNum"] = PIn.Long(raw.Rows[i]["MountNum"].ToString()); row["DocCategory"] = PIn.Long(raw.Rows[i]["DocCategory"].ToString()); row["DateCreated"] = PIn.Date(raw.Rows[i]["DateCreated"].ToString()); row["docFolder"] = DefC.GetOrder(DefCat.ImageCats, PIn.Long(raw.Rows[i]["DocCategory"].ToString())); row["description"] = PIn.Date(raw.Rows[i]["DateCreated"].ToString()).ToString("d") + ": " + PIn.String(raw.Rows[i]["Description"].ToString()); row["ImgType"] = PIn.Long(raw.Rows[i]["ImgType"].ToString()); resultSet.Add(row); } //We must sort the results after they are returned from the database, because the database software (i.e. MySQL) //cannot return sorted results from two or more result sets like we have here. resultSet.Sort(delegate(Object o1, Object o2) { DataRow r1 = (DataRow)o1; DataRow r2 = (DataRow)o2; int docFolder1 = Convert.ToInt32(r1["docFolder"].ToString()); int docFolder2 = Convert.ToInt32(r2["docFolder"].ToString()); if (docFolder1 < docFolder2) { return(-1); } else if (docFolder1 > docFolder2) { return(1); } return(PIn.Date(r1["DateCreated"].ToString()).CompareTo(PIn.Date(r2["DateCreated"].ToString()))); }); //Finally, move the results from the list into a data table. for (int i = 0; i < resultSet.Count; i++) { table.Rows.Add((DataRow)resultSet[i]); } return(table); }
///<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); }
//IMPORTANT NOTE FOR ANYBODY WHO CODES IN HERE: This is used in the CEMT so everything MUST be coded in such a way that they don't use the //cache to look up information. The CEMT does NOT keep copies of the remote database caches when this is used so things such as //PrefC.GetBool or Clinics.GetDesc will return incorrect results. ///<summary>If not using clinics then supply an empty list of clinics.</summary> public static DataSet GetData(DateTime dateFrom, DateTime dateTo, List <Provider> listProvs, List <Clinic> listClinics, bool hasAllProvs , bool hasAllClinics, PPOWriteoffDateCalc writeoffPayType, bool isCEMT = false) { //No need to check RemotingRole; no call to db. DataSet dataSet = GetMonthlyGoalDataSet(dateFrom, dateTo, listProvs, listClinics, hasAllProvs, hasAllClinics, writeoffPayType, isCEMT); DataTable tableProduction = dataSet.Tables["tableProduction"]; DataTable tableAdj = dataSet.Tables["tableAdj"]; DataTable tableInsWriteoff = dataSet.Tables["tableInsWriteoff"]; DataTable tableSched = dataSet.Tables["tableSched"]; DataTable tableProdGoal = dataSet.Tables["tableProdGoal"]; DataTable tableWriteoffAdj = dataSet.Tables["tableWriteOffAdjustments"]; decimal scheduledForDay; decimal productionForDay; decimal adjustsForDay; decimal inswriteoffsForDay; //spk 5/19/05 decimal insWriteoffAdjsForDay; decimal totalproductionForDay; decimal prodGoalForDay; DataTable dt = new DataTable("Total"); dt.Columns.Add(new DataColumn("Date")); dt.Columns.Add(new DataColumn("Weekday")); dt.Columns.Add(new DataColumn("Production")); dt.Columns.Add(new DataColumn("Prod Goal")); dt.Columns.Add(new DataColumn("Scheduled")); dt.Columns.Add(new DataColumn("Adjusts")); if (writeoffPayType == PPOWriteoffDateCalc.ClaimPayDate) { dt.Columns.Add(new DataColumn("Writeoff Est")); dt.Columns.Add(new DataColumn("Writeoff Adj")); } else { dt.Columns.Add(new DataColumn("Writeoff")); } dt.Columns.Add(new DataColumn("Tot Prod")); DataTable dtClinic = new DataTable("Clinic"); dtClinic.Columns.Add(new DataColumn("Date")); dtClinic.Columns.Add(new DataColumn("Weekday")); dtClinic.Columns.Add(new DataColumn("Production")); dtClinic.Columns.Add(new DataColumn("Prod Goal")); dtClinic.Columns.Add(new DataColumn("Scheduled")); dtClinic.Columns.Add(new DataColumn("Adjusts")); if (writeoffPayType == PPOWriteoffDateCalc.ClaimPayDate) { dtClinic.Columns.Add(new DataColumn("Writeoff Est")); dtClinic.Columns.Add(new DataColumn("Writeoff Adj")); } else { dtClinic.Columns.Add(new DataColumn("Writeoff")); } dtClinic.Columns.Add(new DataColumn("Tot Prod")); dtClinic.Columns.Add(new DataColumn("Clinic")); //length of array is number of months between the two dates plus one. //The from date and to date will not be more than one year and must will be within the same year due to FormRpProdInc UI validation enforcement. DateTime[] dates = null; dates = new DateTime[dateTo.Subtract(dateFrom).Days + 1]; //Make a DateTime array with one position for each day in the report. //Preprocess schedules for provider so we can merge them together and not double count them. //For each schedule, find all others for this prov and clinic. (in the case when they're overlapping and multiple clinics, we don't get this far) //Figure out a way to find all hours a provider worked in a single day across multiple schedules. If they overlap (due to multiple operatories) //then we only count one. //Sum up a schedule for the day. Dictionary <Tuple <DateTime, long, long>, List <DataRow> > dictDates = new Dictionary <Tuple <DateTime, long, long>, List <DataRow> >();//We are grouping data rows by day, provnum, and clinicnum for (int j = 0; j < tableProdGoal.Rows.Count; j++) { DateTime date = PIn.Date(tableProdGoal.Rows[j]["SchedDate"].ToString()); long provNum = PIn.Long(tableProdGoal.Rows[j]["ProvNum"].ToString()); long clinicNum = (!hasAllClinics && listClinics.Count == 0) ? 0 : PIn.Long(tableProdGoal.Rows[j]["ClinicNum"].ToString()); if (!dictDates.ContainsKey(Tuple.Create(date, provNum, clinicNum))) { dictDates.Add(Tuple.Create(date, provNum, clinicNum), new List <DataRow>() { tableProdGoal.Rows[j] }); continue; //It's added, no need to do more. } //Date/prov/clinic combo exists in dictionary already, add row to the row collection. dictDates[Tuple.Create(date, provNum, clinicNum)].Add(tableProdGoal.Rows[j]); } List <ProvProdGoal> listProdGoal = new List <ProvProdGoal>(); //Add all spans to a list of spans if they don't overlap. If they do overlap, extend the start/end of an existing span. //Once all spans are added, compare spans in list to other spans and see if they overlap, expand as needed (removing the one merged). //If there is no movement, we are done. foreach (KeyValuePair <Tuple <DateTime, long, long>, List <DataRow> > kvp in dictDates) //For each day (there are no multi-clinic overlaps, can't run report if there are) { double hours = 0; List <SchedRange> listRangeForDay = new List <SchedRange>(); foreach (DataRow row in kvp.Value) //Add all schedule ranges to the list { TimeSpan stopTime = PIn.Time(row["StopTime"].ToString()); TimeSpan startTime = PIn.Time(row["StartTime"].ToString()); SchedRange range = new SchedRange() { StartTime = startTime, EndTime = stopTime }; listRangeForDay.Add(range); } bool hasMovement = true; while (listRangeForDay.Count > 1 && hasMovement) //As they're added, attempt to merge ranges until there's no more movement. { for (int i = listRangeForDay.Count - 1; i >= 0; i--) { SchedRange range1 = listRangeForDay[i]; for (int j = listRangeForDay.Count - 1; j >= 0; j--) { hasMovement = false; SchedRange range2 = listRangeForDay[j]; if (range1.PriKey == range2.PriKey) { continue; } if (range1.StartTime <= range2.StartTime && range1.EndTime >= range2.StartTime) //range2 starts between range1's start and end. Time to merge end time. { if (range2.EndTime >= range1.EndTime) { range1.EndTime = range2.EndTime; } hasMovement = true; } if (range1.StartTime <= range2.EndTime && range1.EndTime >= range2.EndTime) //range2 ends between range1's start and end. Time to merge start time. { if (range2.StartTime <= range1.StartTime) { range1.StartTime = range2.StartTime; } hasMovement = true; } if (hasMovement) { listRangeForDay.RemoveAt(j); --i; } } } } foreach (SchedRange sched in listRangeForDay) { TimeSpan timeDiff = sched.EndTime.Subtract(sched.StartTime); hours += timeDiff.TotalHours; } listProdGoal.Add(new ProvProdGoal() { ClinicNum = kvp.Key.Item3, ProvNum = kvp.Key.Item2, Date = kvp.Key.Item1, Hours = hours, ProdGoal = PIn.Double(kvp.Value[0]["ProvProdGoal"].ToString()) }); } //Get a list of clinics so that we have access to their descriptions for the report. for (int it = 0; it < listClinics.Count; it++) //For each clinic { for (int i = 0; i < dates.Length; i++) //usually 12 months in loop for annual. Loop through the DateTime array, each position represents one date in the report. { dates[i] = dateFrom.AddDays(i); //Monthly/Daily report, add a day DataRow row = dtClinic.NewRow(); row["Date"] = dates[i].ToShortDateString(); row["Weekday"] = dates[i].DayOfWeek.ToString(); scheduledForDay = 0; productionForDay = 0; adjustsForDay = 0; inswriteoffsForDay = 0; //spk 5/19/05 insWriteoffAdjsForDay = 0; prodGoalForDay = 0; for (int j = 0; j < tableProduction.Rows.Count; j++) { if (listClinics[it].ClinicNum == 0 && tableProduction.Rows[j]["ClinicNum"].ToString() != "0") { continue; //Only counting unassigned this time around. } else if (listClinics[it].ClinicNum != 0 && tableProduction.Rows[j]["ClinicNum"].ToString() != POut.Long(listClinics[it].ClinicNum)) { continue; } if (dates[i].Date == PIn.Date(tableProduction.Rows[j]["ProcDate"].ToString()).Date) { productionForDay += PIn.Decimal(tableProduction.Rows[j]["Production"].ToString()); } } for (int j = 0; j < tableAdj.Rows.Count; j++) { if (listClinics[it].ClinicNum == 0 && tableAdj.Rows[j]["ClinicNum"].ToString() != "0") { continue; } else if (listClinics[it].ClinicNum != 0 && tableAdj.Rows[j]["ClinicNum"].ToString() != POut.Long(listClinics[it].ClinicNum)) { continue; } if (dates[i].Date == PIn.Date(tableAdj.Rows[j]["AdjDate"].ToString()).Date) { adjustsForDay += PIn.Decimal(tableAdj.Rows[j]["Adjustment"].ToString()); } } for (int j = 0; j < tableInsWriteoff.Rows.Count; j++) { if (listClinics[it].ClinicNum == 0 && tableInsWriteoff.Rows[j]["ClinicNum"].ToString() != "0") { continue; } else if (listClinics[it].ClinicNum != 0 && tableInsWriteoff.Rows[j]["ClinicNum"].ToString() != POut.Long(listClinics[it].ClinicNum)) { continue; } if (dates[i].Date == PIn.Date(tableInsWriteoff.Rows[j]["Date"].ToString()).Date) { inswriteoffsForDay -= PIn.Decimal(tableInsWriteoff.Rows[j]["Writeoff"].ToString()); } } foreach (DataRow rowCur in tableWriteoffAdj.Rows) { if (rowCur["ClinicNum"].ToString() != POut.Long(listClinics[it].ClinicNum) || dates[i].Date != PIn.Date(rowCur["Date"].ToString()).Date) { continue; } insWriteoffAdjsForDay -= PIn.Decimal(rowCur["WriteOffEst"].ToString()) + PIn.Decimal(rowCur["WriteOff"].ToString()); } for (int j = 0; j < tableSched.Rows.Count; j++) { if (listClinics[it].ClinicNum == 0 && tableSched.Rows[j]["ClinicNum"].ToString() != "0") { continue; } else if (listClinics[it].ClinicNum != 0 && tableSched.Rows[j]["ClinicNum"].ToString() != POut.Long(listClinics[it].ClinicNum)) { continue; } if (dates[i].Date == PIn.Date(tableSched.Rows[j]["SchedDate"].ToString()).Date) { scheduledForDay += PIn.Decimal(tableSched.Rows[j]["Amount"].ToString()); } } for (int j = 0; j < listProdGoal.Count; j++) { if (listClinics[it].ClinicNum == 0 && listProdGoal[j].ClinicNum != 0) { continue; } else if (listClinics[it].ClinicNum != 0 && listProdGoal[j].ClinicNum != listClinics[it].ClinicNum) { continue; } if (dates[i].Date == listProdGoal[j].Date) { prodGoalForDay += (decimal)(listProdGoal[j].Hours * listProdGoal[j].ProdGoal); //Multiply the hours for this schedule by the amount of production goal for this prov. } } totalproductionForDay = productionForDay + adjustsForDay + inswriteoffsForDay + insWriteoffAdjsForDay + scheduledForDay; string clinicDesc = listClinics[it].Description; row["Production"] = productionForDay.ToString("n"); row["Prod Goal"] = prodGoalForDay.ToString("n"); row["Scheduled"] = scheduledForDay.ToString("n"); row["Adjusts"] = adjustsForDay.ToString("n"); if (writeoffPayType == PPOWriteoffDateCalc.ClaimPayDate) { row["Writeoff Est"] = inswriteoffsForDay.ToString("n"); row["Writeoff Adj"] = insWriteoffAdjsForDay.ToString("n"); } else { row["Writeoff"] = inswriteoffsForDay.ToString("n"); } row["Tot Prod"] = totalproductionForDay.ToString("n"); row["Clinic"] = clinicDesc == "" ? Lans.g("FormRpProdInc", "Unassigned"):clinicDesc; dtClinic.Rows.Add(row); } } for (int i = 0; i < dates.Length; i++) //usually 12 months in loop { dates[i] = dateFrom.AddDays(i); DataRow row = dt.NewRow(); row["Date"] = dates[i].ToShortDateString(); row["Weekday"] = dates[i].DayOfWeek.ToString(); scheduledForDay = 0; productionForDay = 0; adjustsForDay = 0; inswriteoffsForDay = 0; insWriteoffAdjsForDay = 0; prodGoalForDay = 0; for (int j = 0; j < tableProduction.Rows.Count; j++) { if (dates[i].Date == PIn.Date(tableProduction.Rows[j]["ProcDate"].ToString()).Date) { productionForDay += PIn.Decimal(tableProduction.Rows[j]["Production"].ToString()); } } for (int j = 0; j < tableAdj.Rows.Count; j++) { if (dates[i].Date == PIn.Date(tableAdj.Rows[j]["AdjDate"].ToString()).Date) { adjustsForDay += PIn.Decimal(tableAdj.Rows[j]["Adjustment"].ToString()); } } for (int j = 0; j < tableInsWriteoff.Rows.Count; j++) { if (dates[i].Date == PIn.Date(tableInsWriteoff.Rows[j]["Date"].ToString()).Date) { inswriteoffsForDay -= PIn.Decimal(tableInsWriteoff.Rows[j]["Writeoff"].ToString()); } } foreach (DataRow rowCur in tableWriteoffAdj.Rows) { if (dates[i].Date == PIn.Date(rowCur["Date"].ToString()).Date) { insWriteoffAdjsForDay -= PIn.Decimal(rowCur["WriteOffEst"].ToString()) + PIn.Decimal(rowCur["WriteOff"].ToString()); } } for (int j = 0; j < tableSched.Rows.Count; j++) { if (dates[i].Date == PIn.Date(tableSched.Rows[j]["SchedDate"].ToString()).Date) { scheduledForDay += PIn.Decimal(tableSched.Rows[j]["Amount"].ToString()); } } for (int j = 0; j < listProdGoal.Count; j++) { if (dates[i].Date == listProdGoal[j].Date) { prodGoalForDay += (decimal)(listProdGoal[j].Hours * listProdGoal[j].ProdGoal); //Multiply the hours for this schedule by the amount of production goal for this prov. } } totalproductionForDay = productionForDay + adjustsForDay + inswriteoffsForDay + insWriteoffAdjsForDay + scheduledForDay; row["Production"] = productionForDay.ToString("n"); row["Prod Goal"] = prodGoalForDay.ToString("n"); row["Scheduled"] = scheduledForDay.ToString("n"); row["Adjusts"] = adjustsForDay.ToString("n"); if (writeoffPayType == PPOWriteoffDateCalc.ClaimPayDate) { row["Writeoff Est"] = inswriteoffsForDay.ToString("n"); row["Writeoff Adj"] = insWriteoffAdjsForDay.ToString("n"); } else { row["Writeoff"] = inswriteoffsForDay.ToString("n"); } row["Tot Prod"] = totalproductionForDay.ToString("n"); dt.Rows.Add(row); } DataSet ds = null; ds = new DataSet("MonthlyData"); ds.Tables.Add(dt); if (listClinics.Count != 0) { ds.Tables.Add(dtClinic); } return(ds); }
///<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); }
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)); } string command; command = "DROP TABLE IF EXISTS tempdash;"; Db.NonQ(command); //this table will contain approx 12x3xProv rows if there was production for each prov in each month. command = @"CREATE TABLE tempdash ( DatePeriod date NOT NULL, ProvNum bigint NOT NULL, production decimal NOT NULL ) DEFAULT CHARSET=utf8" ; Db.NonQ(command); //procs. Inserts approx 12xProv rows command = @"INSERT INTO tempdash SELECT procedurelog.ProcDate,procedurelog.ProvNum, 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 procedurelog.ProvNum,MONTH(procedurelog.ProcDate)" ; Db.NonQ(command); //todo 2 more tables //get all the data as 12xProv rows command = @"SELECT DatePeriod,ProvNum,SUM(production) prod FROM tempdash GROUP BY ProvNum,MONTH(DatePeriod)" ;//this fails with date issue DataTable tableProd = Db.GetTable(command); command = "DROP TABLE IF EXISTS tempdash;"; Db.NonQ(command); command = @"SELECT ProvNum FROM provider WHERE IsHidden=0 ORDER BY ItemOrder" ; DataTable tableProv = Db.GetTable(command); 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 (datePeriod.Year == PIn.Date(tableProd.Rows[j]["DatePeriod"].ToString()).Year && datePeriod.Month == PIn.Date(tableProd.Rows[j]["DatePeriod"].ToString()).Month && provNum == PIn.Long(tableProd.Rows[j]["ProvNum"].ToString())) { prod = PIn.Decimal(tableProd.Rows[j]["prod"].ToString()); break; } } listInt.Add((int)(prod)); } retVal.Add(listInt); } return(retVal); }
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); }
///<summary>Gets a pref of type date.</summary> public static DateTime GetDate(PrefName prefName) { return(PIn.Date(Prefs.GetOne(prefName).ValueString)); }
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); }
///<summary>Returns a SerializableDictionary with key=PatNum, value=PatAgingData with the filters applied.</summary> public static SerializableDictionary <long, PatAgingData> GetAgingData(bool isSinglePatient, bool includeChanged, bool excludeInsPending, bool excludeIfUnsentProcs, bool isSuperBills, List <long> listClinicNums) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetObject <SerializableDictionary <long, PatAgingData> >(MethodBase.GetCurrentMethod(), isSinglePatient, includeChanged, excludeInsPending, excludeIfUnsentProcs, isSuperBills, listClinicNums)); } SerializableDictionary <long, PatAgingData> dictPatAgingData = new SerializableDictionary <long, PatAgingData>(); string command = ""; string guarOrPat = "guar"; if (isSinglePatient) { guarOrPat = "patient"; } string whereAndClinNum = ""; if (!listClinicNums.IsNullOrEmpty()) { whereAndClinNum = $@"AND {guarOrPat}.ClinicNum IN ({string.Join(",",listClinicNums)})"; } if (includeChanged || excludeIfUnsentProcs) { command = $@"SELECT {guarOrPat}.PatNum,{guarOrPat}.ClinicNum,MAX(procedurelog.ProcDate) MaxProcDate"; if (excludeIfUnsentProcs) { command += ",MAX(CASE WHEN insplan.IsMedical=1 THEN 0 ELSE COALESCE(claimproc.ProcNum,0) END)>0 HasUnsentProcs"; } command += $@" FROM patient INNER JOIN patient guar ON guar.PatNum=patient.Guarantor INNER JOIN procedurelog ON procedurelog.PatNum = patient.PatNum " ; if (excludeIfUnsentProcs) { command += $@"LEFT JOIN claimproc ON claimproc.ProcNum = procedurelog.ProcNum AND claimproc.NoBillIns=0 AND claimproc.Status = {POut.Int((int)ClaimProcStatus.Estimate)} AND procedurelog.ProcDate > CURDATE()-INTERVAL 6 MONTH LEFT JOIN insplan ON insplan.PlanNum=claimproc.PlanNum " ; } command += $@"WHERE procedurelog.ProcFee > 0 AND procedurelog.ProcStatus = {POut.Int((int)ProcStat.C)} {whereAndClinNum} GROUP BY {guarOrPat}.PatNum ORDER BY NULL" ; using (DataTable tableChangedAndUnsent = Db.GetTable(command)) { foreach (DataRow row in tableChangedAndUnsent.Rows) { long patNum = PIn.Long(row["PatNum"].ToString()); if (!dictPatAgingData.ContainsKey(patNum)) { dictPatAgingData[patNum] = new PatAgingData(PIn.Long(row["ClinicNum"].ToString())); } if (includeChanged) { dictPatAgingData[patNum].ListPatAgingTransactions .Add(new PatAgingTransaction(PatAgingTransaction.TransactionTypes.Procedure, PIn.Date(row["MaxProcDate"].ToString()))); } if (excludeIfUnsentProcs) { dictPatAgingData[patNum].HasUnsentProcs = PIn.Bool(row["HasUnsentProcs"].ToString()); } } } } if (includeChanged) { command = $@"SELECT {guarOrPat}.PatNum,{guarOrPat}.ClinicNum,MAX(claimproc.DateCP) maxDateCP FROM claimproc INNER JOIN patient ON patient.PatNum = claimproc.PatNum INNER JOIN patient guar ON guar.PatNum=patient.Guarantor WHERE claimproc.InsPayAmt > 0 {whereAndClinNum} GROUP BY {guarOrPat}.PatNum" ; using (DataTable tableMaxPayDate = Db.GetTable(command)) { foreach (DataRow row in tableMaxPayDate.Rows) { long patNum = PIn.Long(row["PatNum"].ToString()); if (!dictPatAgingData.ContainsKey(patNum)) { dictPatAgingData[patNum] = new PatAgingData(PIn.Long(row["ClinicNum"].ToString())); } dictPatAgingData[patNum].ListPatAgingTransactions .Add(new PatAgingTransaction(PatAgingTransaction.TransactionTypes.ClaimProc, PIn.Date(row["maxDateCP"].ToString()))); } } command = $@"SELECT {guarOrPat}.PatNum,{guarOrPat}.ClinicNum,MAX(payplancharge.ChargeDate) maxDatePPC, MAX(payplancharge.SecDateTEntry) maxDatePPCSDTE FROM payplancharge INNER JOIN patient ON patient.PatNum = payplancharge.PatNum INNER JOIN patient guar ON guar.PatNum=patient.Guarantor INNER JOIN payplan ON payplan.PayPlanNum = payplancharge.PayPlanNum AND payplan.PlanNum = 0 " //don't want insurance payment plans to make patients appear in the billing list + $@"WHERE payplancharge.Principal + payplancharge.Interest>0 AND payplancharge.ChargeType = {(int)PayPlanChargeType.Debit} " //include all charges in the past or due 'PayPlanBillInAdvance' days into the future. + $@"AND payplancharge.ChargeDate <= {POut.Date(DateTime.Today.AddDays(PrefC.GetDouble(PrefName.PayPlansBillInAdvanceDays)))} {whereAndClinNum} GROUP BY {guarOrPat}.PatNum" ; using (DataTable tableMaxPPCDate = Db.GetTable(command)) { foreach (DataRow row in tableMaxPPCDate.Rows) { long patNum = PIn.Long(row["PatNum"].ToString()); if (!dictPatAgingData.ContainsKey(patNum)) { dictPatAgingData[patNum] = new PatAgingData(PIn.Long(row["ClinicNum"].ToString())); } dictPatAgingData[patNum].ListPatAgingTransactions .Add(new PatAgingTransaction( PatAgingTransaction.TransactionTypes.PayPlanCharge, PIn.Date(row["maxDatePPC"].ToString()), secDateTEntryTrans: PIn.Date(row["maxDatePPCSDTE"].ToString())) ); } } } if (excludeInsPending) { command = $@"SELECT {guarOrPat}.PatNum,{guarOrPat}.ClinicNum FROM claim INNER JOIN patient ON patient.PatNum=claim.PatNum INNER JOIN patient guar ON guar.PatNum=patient.Guarantor WHERE claim.ClaimStatus IN ('U','H','W','S') AND claim.ClaimType IN ('P','S','Other') {whereAndClinNum} GROUP BY {guarOrPat}.PatNum" ; using (DataTable tableInsPending = Db.GetTable(command)) { foreach (DataRow row in tableInsPending.Rows) { long patNum = PIn.Long(row["PatNum"].ToString()); if (!dictPatAgingData.ContainsKey(patNum)) { dictPatAgingData[patNum] = new PatAgingData(PIn.Long(row["ClinicNum"].ToString())); } dictPatAgingData[patNum].HasPendingIns = true; } } } DateTime dateAsOf = DateTime.Today; //used to determine when the balance on this date began if (PrefC.GetBool(PrefName.AgingCalculatedMonthlyInsteadOfDaily)) //if aging calculated monthly, use the last aging date instead of today { dateAsOf = PrefC.GetDate(PrefName.DateLastAging); } List <PatComm> listPatComms = new List <PatComm>(); using (DataTable tableDateBalsBegan = Ledgers.GetDateBalanceBegan(null, dateAsOf, isSuperBills, listClinicNums)) { foreach (DataRow row in tableDateBalsBegan.Rows) { long patNum = PIn.Long(row["PatNum"].ToString()); if (!dictPatAgingData.ContainsKey(patNum)) { dictPatAgingData[patNum] = new PatAgingData(PIn.Long(row["ClinicNum"].ToString())); } dictPatAgingData[patNum].DateBalBegan = PIn.Date(row["DateAccountAge"].ToString()); dictPatAgingData[patNum].DateBalZero = PIn.Date(row["DateZeroBal"].ToString()); } listPatComms = Patients.GetPatComms(tableDateBalsBegan.Select().Select(x => PIn.Long(x["PatNum"].ToString())).ToList(), null); } foreach (PatComm pComm in listPatComms) { if (!dictPatAgingData.ContainsKey(pComm.PatNum)) { dictPatAgingData[pComm.PatNum] = new PatAgingData(pComm.ClinicNum); } dictPatAgingData[pComm.PatNum].PatComm = pComm; } return(dictPatAgingData); }
///<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) { 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)); } 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("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 * FROM (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 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 cr.CustReferenceNum<>0 " ; //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) + "%' ":"") + (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":""); if (limit) { command = DbHelper.LimitOrderBy(command, 40); } command += @") AS tempcustref WHERE PatNum<>0 "; //Once again just making AND statements brainless. if (superFam > 0) { command += "AND SuperFamily>" + POut.Int(superFam) + " "; } if (showUsed) { command += "AND TimesUsed>0 "; } 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["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); }
public static DataTable GetUnearnedAllocationData(List <long> listClinicNums, List <long> listProvNums, List <long> listUnearnedTypeNums, bool isExcludeNetZeroUnearned, bool showProvider) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetTable(MethodBase.GetCurrentMethod(), listClinicNums, listProvNums, listUnearnedTypeNums, isExcludeNetZeroUnearned, showProvider)); } List <long> listHiddenUnearnedDefNums = ReportsComplex.RunFuncOnReportServer(() => Defs.GetDefsNoCache(DefCat.PaySplitUnearnedType).FindAll(x => !string.IsNullOrEmpty(x.ItemValue)).Select(x => x.DefNum).ToList() ); //get all families that have an unallocated unearned balance. //from those, remove families that have not had procedures charted/completed after the unearned amount. //All families //DatePay = the earliest date of unallocated unearned. //Unallocated Amt = the total unallocated amt for the patient. string command = $@" SELECT patient.Guarantor, MIN(paysplit.DatePay) DatePay, SUM(paysplit.SplitAmt) UnallocAmt{(showProvider ? ", provider.Abbr" : "")} FROM paysplit INNER JOIN patient ON patient.PatNum = paysplit.PatNum " ; if (listClinicNums.Count > 0 || listProvNums.Count > 0) { command += "INNER JOIN patient guar ON guar.PatNum = patient.Guarantor "; if (listClinicNums.Count > 0) { command += "AND guar.ClinicNum IN (" + string.Join(",", listClinicNums.Select(x => POut.Long(x))) + ") "; } if (listProvNums.Count > 0) { command += "AND guar.PriProv IN (" + string.Join(",", listProvNums.Select(x => POut.Long(x))) + ") "; } } if (showProvider) { command += "LEFT JOIN provider ON provider.ProvNum = paysplit.ProvNum "; } command += "WHERE paysplit.UnearnedType != 0 "; if (listUnearnedTypeNums.Count > 0) { command += "AND paysplit.UnearnedType IN (" + string.Join(",", listUnearnedTypeNums.Select(x => POut.Long(x))) + ") "; } if (listHiddenUnearnedDefNums.Count > 0) { command += $"AND paysplit.UnearnedType NOT IN ({string.Join(",",listHiddenUnearnedDefNums)}) "; } command += $"GROUP BY patient.Guarantor{(showProvider ? ", provider.Abbr" : "")} "; if (isExcludeNetZeroUnearned) { command += "HAVING ABS(UnallocAmt) > 0.005 "; } //one row per family DataTable tableUnallocatedUnearned = ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(command)); List <long> listGuarantors = tableUnallocatedUnearned.Rows.OfType <DataRow>().Select(x => PIn.Long(x["Guarantor"].ToString())).ToList(); //all procedures for the families that have not been explicitly paid off. //Key: GuarantorNum | Val:ListRemainingProcsForFam List <UnearnedProc> listRemProcs = ReportsComplex.RunFuncOnReportServer(() => Procedures.GetRemainingProcsForFamilies(listGuarantors)); Dictionary <long, List <UnearnedProc> > dictFamRemainingProcs = listRemProcs.GroupBy(x => x.GuarNum) .ToDictionary(x => x.Key, y => y.ToList()); Dictionary <long, double> dictFamilyBalances = ReportsComplex.RunFuncOnReportServer(() => Ledgers.GetBalancesForFamilies(listGuarantors)); Dictionary <long, string> dictPatNames = ReportsComplex.RunFuncOnReportServer(() => Patients.GetPatientNames(Patients.GetAllFamilyPatNums(listGuarantors))); List <ProcedureCode> listProcCodes = ReportsComplex.RunFuncOnReportServer(() => ProcedureCodes.GetAllCodes()); DataTable retVal = new DataTable(); retVal.Columns.Add("Guar"); retVal.Columns.Add("FamBal"); retVal.Columns.Add("FamUnearned"); retVal.Columns.Add("FamRemAmt"); if (showProvider) { retVal.Columns.Add("Prov"); } retVal.Columns.Add("Patient"); retVal.Columns.Add("Code"); retVal.Columns.Add("Date"); retVal.Columns.Add("Fee"); retVal.Columns.Add("RemAmt"); int rowCount = tableUnallocatedUnearned.Rows.Count; //For brevity //This has to be a for-loop instead of foreach so we can access the guarantor number from the next iteration //prior to adding the procedures to the report (to validate whether or not we should add another guarantor row //for a provider for (int i = 0; i < rowCount; i++) { DataRow guarRowCur = tableUnallocatedUnearned.Rows[i]; int nextIndex = i + 1; long guarNum = PIn.Long(guarRowCur["Guarantor"].ToString()); DateTime dateFirstUnalloc = PIn.Date(guarRowCur["DatePay"].ToString()); double unallocAmt = PIn.Double(guarRowCur["UnallocAmt"].ToString()); List <UnearnedProc> listUnearnedProcsForGuar; if (!dictFamRemainingProcs.TryGetValue(guarNum, out listUnearnedProcsForGuar)) { continue; //This family does not have any procedures that need to have money allocated to. } listUnearnedProcsForGuar = listUnearnedProcsForGuar.Where(x => x.Proc.ProcDate >= dateFirstUnalloc).OrderBy(x => x.Proc.ProcDate).ToList(); if (listUnearnedProcsForGuar.Count == 0) { continue; //We only want to show families where the procedure was completed after the unallocated prepayment. } decimal famRemAmt = listUnearnedProcsForGuar.Sum(x => x.UnallocatedAmt); DataRow guarRow = retVal.NewRow(); string guarName; double famBal; dictPatNames.TryGetValue(guarNum, out guarName); dictFamilyBalances.TryGetValue(guarNum, out famBal); guarRow["Guar"] = guarName; guarRow["FamBal"] = famBal.ToString("f"); guarRow["FamUnearned"] = unallocAmt.ToString("f"); guarRow["FamRemAmt"] = famRemAmt.ToString("f"); if (showProvider) { guarRow["Prov"] = guarRowCur["Abbr"]; } retVal.Rows.Add(guarRow); //If the next row has the same guarantor, then we know that it is another provider for this account and we should not populate the procedures yet if (nextIndex < rowCount && guarNum == PIn.Long(tableUnallocatedUnearned.Rows[nextIndex]["Guarantor"].ToString())) { continue; } foreach (UnearnedProc unearnedProc in listUnearnedProcsForGuar) { DataRow newRow = retVal.NewRow(); dictPatNames.TryGetValue(guarNum, out guarName); string patName; if (dictPatNames.TryGetValue(unearnedProc.Proc.PatNum, out patName)) { newRow["Patient"] = patName; } newRow["Code"] = ProcedureCodes.GetStringProcCode(unearnedProc.Proc.CodeNum, listProcCodes); newRow["Date"] = unearnedProc.Proc.ProcDate.ToShortDateString(); newRow["Fee"] = unearnedProc.Proc.ProcFeeTotal.ToString("f"); newRow["RemAmt"] = unearnedProc.UnallocatedAmt.ToString("f"); retVal.Rows.Add(newRow); } } return(retVal); }
///<summary>Get all payment plans for this patient with the insurance plan identified by PlanNum and InsSubNum attached (marked used for tracking expected insurance payments) that have not been paid in full. Only returns plans with no claimprocs currently attached or claimprocs from the claim identified by the claimNum sent in attached. If claimNum is 0 all payment plans with planNum, insSubNum, and patNum not paid in full will be returned.</summary> public static List <PayPlan> GetValidInsPayPlans(long patNum, long planNum, long insSubNum, long claimNum) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetObject <List <PayPlan> >(MethodBase.GetCurrentMethod(), patNum, planNum, insSubNum, claimNum)); } string command = ""; if (DataConnection.DBtype == DatabaseType.MySql) { command += "SELECT payplan.*,MAX(claimproc.ClaimNum) ClaimNum"; } else { command += "SELECT payplan.PayPlanNum,payplan.PatNum,payplan.Guarantor,payplan.PayPlanDate," + "payplan.APR,payplan.Note,payplan.PlanNum,payplan.CompletedAmt,payplan.InsSubNum,MAX(claimproc.ClaimNum) ClaimNum"; } command += " FROM payplan" + " LEFT JOIN claimproc ON claimproc.PayPlanNum=payplan.PayPlanNum" + " WHERE payplan.PatNum=" + POut.Long(patNum) + " AND payplan.PlanNum=" + POut.Long(planNum) + " AND payplan.InsSubNum=" + POut.Long(insSubNum); if (claimNum > 0) { command += " AND (claimproc.ClaimNum IS NULL OR claimproc.ClaimNum=" + POut.Long(claimNum) + ")"; //payplans with no claimprocs attached or only claimprocs from the same claim } if (DataConnection.DBtype == DatabaseType.MySql) { command += " GROUP BY payplan.PayPlanNum"; } else { command += " GROUP BY payplan.PayPlanNum,payplan.PatNum,payplan.Guarantor,payplan.PayPlanDate," + "payplan.APR,payplan.Note,payplan.PlanNum,payplan.CompletedAmt,payplan.InsSubNum"; } command += " HAVING payplan.CompletedAmt>SUM(COALESCE(claimproc.InsPayAmt,0))"; //has not been paid in full yet if (claimNum == 0) //if current claimproc is not attached to a claim, do not return payplans with claimprocs from existing claims already attached { command += " AND (MAX(claimproc.ClaimNum) IS NULL OR MAX(claimproc.ClaimNum)=0)"; } command += " ORDER BY payplan.PayPlanDate"; DataTable payPlansWithClaimNum = Db.GetTable(command); List <PayPlan> retval = new List <PayPlan>(); for (int i = 0; i < payPlansWithClaimNum.Rows.Count; i++) { PayPlan planCur = new PayPlan(); planCur.PayPlanNum = PIn.Long(payPlansWithClaimNum.Rows[i]["PayPlanNum"].ToString()); planCur.PatNum = PIn.Long(payPlansWithClaimNum.Rows[i]["PatNum"].ToString()); planCur.Guarantor = PIn.Long(payPlansWithClaimNum.Rows[i]["Guarantor"].ToString()); planCur.PayPlanDate = PIn.Date(payPlansWithClaimNum.Rows[i]["PayPlanDate"].ToString()); planCur.APR = PIn.Double(payPlansWithClaimNum.Rows[i]["APR"].ToString()); planCur.Note = payPlansWithClaimNum.Rows[i]["Note"].ToString(); planCur.PlanNum = PIn.Long(payPlansWithClaimNum.Rows[i]["PlanNum"].ToString()); planCur.CompletedAmt = PIn.Double(payPlansWithClaimNum.Rows[i]["CompletedAmt"].ToString()); planCur.InsSubNum = PIn.Long(payPlansWithClaimNum.Rows[i]["InsSubNum"].ToString()); if (claimNum > 0 && payPlansWithClaimNum.Rows[i]["ClaimNum"].ToString() == claimNum.ToString()) { //if a payplan exists with claimprocs from the same claim as the current claimproc attached, always only return that one payplan //claimprocs from one claim are not allowed to be attached to different payplans retval.Clear(); retval.Add(planCur); break; } retval.Add(planCur); } return(retval); }
public static DataTable GetUnearnedAllocationData(List <long> listClinicNums, List <long> listProvNums, List <long> listUnearnedTypeNums, bool isExcludeNetZeroUnearned) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetTable(MethodBase.GetCurrentMethod(), listClinicNums, listProvNums, listUnearnedTypeNums, isExcludeNetZeroUnearned)); } //get all families that have an unallocated unearned balance. //from those, remove families that have not had procedures charted/completed after the unearned amount. //All families //DatePay = the earliest date of unallocated unearned. //Unallocated Amt = the total unallocated amt for the patient. string command = @" SELECT patient.Guarantor, MIN(paysplit.DatePay) DatePay, SUM(COALESCE(paysplit.SplitAmt,0)) + SUM(COALESCE(alloc.AllocAmt,0)) UnallocAmt FROM paysplit LEFT JOIN ( SELECT paysplit.FSplitNum,SUM(paysplit.SplitAmt) AllocAmt FROM paysplit WHERE paysplit.FSplitNum != 0 GROUP BY paysplit.FSplitNum )alloc ON paysplit.SplitNum = alloc.FSplitNum INNER JOIN patient ON patient.PatNum = paysplit.PatNum " ; if (listClinicNums.Count > 0 || listProvNums.Count > 0) { command += @" INNER JOIN patient guar ON guar.PatNum = patient.Guarantor " ; if (listClinicNums.Count > 0) { command += @" AND guar.ClinicNum IN (" + string.Join(",", listClinicNums.Select(x => POut.Long(x))) + ") "; } if (listProvNums.Count > 0) { command += @" AND guar.PriProv IN (" + string.Join(",", listProvNums.Select(x => POut.Long(x))) + ") "; } } command += @" WHERE paysplit.UnearnedType != 0 " ; if (listUnearnedTypeNums.Count > 0) { command += @" AND paysplit.UnearnedType IN (" + string.Join(",", listUnearnedTypeNums.Select(x => POut.Long(x))) + ") "; } command += @" AND paysplit.FSplitNum = 0 AND (ABS(paysplit.SplitAmt + alloc.AllocAmt) > 0.005 OR alloc.AllocAmt IS NULL) GROUP BY patient.Guarantor " ; if (isExcludeNetZeroUnearned) { command += @" HAVING ABS(UnallocAmt) > 0.005 " ; } //one row per family DataTable tableUnallocatedUnearned = ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(command)); List <long> listGuarantors = tableUnallocatedUnearned.Rows.OfType <DataRow>().Select(x => PIn.Long(x["Guarantor"].ToString())).ToList(); //all procedures for the families that have not been explicitly paid off. //Key: GuarantorNum | Val:ListRemainingProcsForFam List <UnearnedProc> listRemProcs = ReportsComplex.RunFuncOnReportServer(() => Procedures.GetRemainingProcsForFamilies(listGuarantors)); Dictionary <long, List <UnearnedProc> > dictFamRemainingProcs = listRemProcs.GroupBy(x => x.GuarNum) .ToDictionary(x => x.Key, y => y.ToList()); Dictionary <long, double> dictFamilyBalances = ReportsComplex.RunFuncOnReportServer(() => Ledgers.GetBalancesForFamilies(listGuarantors)); Dictionary <long, string> dictPatNames = ReportsComplex.RunFuncOnReportServer(() => Patients.GetPatientNames(Patients.GetAllFamilyPatNums(listGuarantors))); List <ProcedureCode> listProcCodes = ReportsComplex.RunFuncOnReportServer(() => ProcedureCodes.GetAllCodes()); DataTable retVal = new DataTable(); retVal.Columns.Add("Guar"); retVal.Columns.Add("FamBal"); retVal.Columns.Add("FamUnearned"); retVal.Columns.Add("FamRemAmt"); retVal.Columns.Add("Patient"); retVal.Columns.Add("Code"); retVal.Columns.Add("Date"); retVal.Columns.Add("Fee"); retVal.Columns.Add("RemAmt"); foreach (DataRow guarRowCur in tableUnallocatedUnearned.Rows) { long guarNum = PIn.Long(guarRowCur["Guarantor"].ToString()); DateTime dateFirstUnalloc = PIn.Date(guarRowCur["DatePay"].ToString()); double unallocAmt = PIn.Double(guarRowCur["UnallocAmt"].ToString()); List <UnearnedProc> listUnearnedProcsForGuar; if (!dictFamRemainingProcs.TryGetValue(guarNum, out listUnearnedProcsForGuar)) { continue; //This family does not have any procedures that need to have money allocated to. } listUnearnedProcsForGuar = listUnearnedProcsForGuar.Where(x => x.Proc.ProcDate >= dateFirstUnalloc).OrderBy(x => x.Proc.ProcDate).ToList(); if (listUnearnedProcsForGuar.Count == 0) { continue; //We only want to show families where the procedure was completed after the unallocated prepayment. } decimal famRemAmt = listUnearnedProcsForGuar.Sum(x => x.UnallocatedAmt); DataRow guarRow = retVal.NewRow(); string guarName; double famBal; dictPatNames.TryGetValue(guarNum, out guarName); dictFamilyBalances.TryGetValue(guarNum, out famBal); guarRow["Guar"] = guarName; guarRow["FamBal"] = famBal.ToString("f"); guarRow["FamUnearned"] = unallocAmt.ToString("f"); guarRow["FamRemAmt"] = famRemAmt.ToString("f"); retVal.Rows.Add(guarRow); foreach (UnearnedProc unearnedProc in listUnearnedProcsForGuar) { DataRow newRow = retVal.NewRow(); dictPatNames.TryGetValue(guarNum, out guarName); string patName; if (dictPatNames.TryGetValue(unearnedProc.Proc.PatNum, out patName)) { newRow["Patient"] = patName; } newRow["Code"] = ProcedureCodes.GetStringProcCode(unearnedProc.Proc.CodeNum, listProcCodes); newRow["Date"] = unearnedProc.Proc.ProcDate.ToShortDateString(); newRow["Fee"] = (unearnedProc.Proc.ProcFee * (unearnedProc.Proc.UnitQty + unearnedProc.Proc.BaseUnits)).ToString("f"); newRow["RemAmt"] = unearnedProc.UnallocatedAmt.ToString("f"); retVal.Rows.Add(newRow); } } return(retVal); }
///<summary>Simpler way to get a Date (without time) from a DataRow.</summary> public static DateTime GetDate(this DataRow row, string columnName) { return(PIn.Date(row[columnName].ToString())); }
///<summary>Returns list of credit cards that are ready for a recurring charge. Filters by ClinicNums in list if provided. List of ClinicNums ///should contain all clinics the current user is authorized to access. Further filtering by selected clinics is done at the UI level to save ///DB calls.</summary> public static DataTable GetRecurringChargeList(List <long> listClinicNums) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetTable(MethodBase.GetCurrentMethod(), listClinicNums)); } 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. //NOTE: Query will return patients with or without payments regardless of when that payment occurred, filtering is done below. string command = "SELECT CreditCardNum,PatNum,PatName,FamBalTotal,PayPlanDue," + POut.Date(DateTime.MinValue) + " AS LatestPayment,DateStart,Address," + "AddressPat,Zip,ZipPat,XChargeToken,CCNumberMasked,CCExpiration,ChargeAmt,PayPlanNum,ProvNum,PayPlanPatnum,ClinicNum,Procedures,BillingCycleDay,Guarantor," + "PayConnectToken,PayConnectTokenExp,PaySimpleToken " + "FROM ("; #region Payments //The PayOrder is used to differentiate rows attached to payment plans command += "(SELECT 1 AS PayOrder,cc.CreditCardNum,cc.PatNum," + DbHelper.Concat("pat.LName", "', '", "pat.FName") + " PatName," + "guar.LName GuarLName,guar.FName GuarFName,guar.BalTotal-guar.InsEst FamBalTotal,0 AS PayPlanDue," + "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,0 PayPlanPatNum,pat.ClinicNum,cc.Procedures,pat.BillingCycleDay,pat.Guarantor,cc.PayConnectToken,cc.PayConnectTokenExp,cc.PaySimpleToken " + "FROM creditcard cc " + "INNER JOIN patient pat ON pat.PatNum=cc.PatNum " + "INNER JOIN patient guar ON guar.PatNum=pat.Guarantor " + "WHERE cc.PayPlanNum=0 " //Keeps card from showing up in case they have a balance AND is setup for payment plan. + "AND CCSource NOT IN (" + (int)CreditCardSource.XWeb + "," + (int)CreditCardSource.XWebPortalLogin + ") "; //Not created from the Patient Portal if (listClinicNums != null && listClinicNums.Count > 0) { command += "AND pat.ClinicNum IN (" + string.Join(",", listClinicNums.Select(x => POut.Long(x))) + ") "; } 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,PayPlanPatNum," + "pat.ClinicNum,cc.Procedures,pat.BillingCycleDay,pat.Guarantor,cc.PayConnectToken,cc.PayConnectTokenExp,cc.PaySimpleToken) "; } #endregion command += "UNION ALL "; #region Payment Plans command += "(SELECT 2 AS PayOrder,cc.CreditCardNum,cc.PatNum," + DbHelper.Concat("pat.LName", "', '", "pat.FName") + " PatName," + "guar.LName GuarLName,guar.FName GuarFName,guar.BalTotal-guar.InsEst FamBalTotal," + "ROUND(COALESCE(ppc.pastCharges,0)-COALESCE(SUM(ps.SplitAmt),0),2) PayPlanDueCalc," //payplancharges-paysplits attached to pp is PayPlanDueCalc + "cc.DateStart,cc.Address,pat.Address AddressPat,cc.Zip,pat.Zip ZipPat,cc.XChargeToken,cc.CCNumberMasked,cc.CCExpiration,cc.ChargeAmt," + "cc.PayPlanNum,cc.DateStop,COALESCE(ppc.maxProvNum,0) ProvNum,COALESCE(ppc.maxPatNum,0) PayPlanPatNum,COALESCE(ppc.maxClinicNum,0) ClinicNum,cc.Procedures," + "pat.BillingCycleDay,pat.Guarantor,cc.PayConnectToken,cc.PayConnectTokenExp,cc.PaySimpleToken " + "FROM creditcard cc " + "INNER JOIN patient pat ON pat.PatNum=cc.PatNum " + "INNER JOIN patient guar ON guar.PatNum=pat.Guarantor " + "LEFT JOIN paysplit ps ON ps.PayPlanNum=cc.PayPlanNum AND ps.PayPlanNum<>0 " + "LEFT JOIN (" + "SELECT PayPlanNum,MAX(ProvNum) maxProvNum,MAX(PatNum) maxPatNum,MAX(ClinicNum) maxClinicNum," + "SUM(CASE WHEN ChargeType=" + POut.Int((int)PayPlanChargeType.Debit) + " " + "AND ChargeDate <= " + DbHelper.Curdate() + " THEN Principal+Interest ELSE 0 END) pastCharges " + "FROM payplancharge " + "GROUP BY PayPlanNum" + ") ppc ON ppc.PayPlanNum=cc.PayPlanNum " + "WHERE cc.PayPlanNum>0 " + "AND CCSource NOT IN (" + (int)CreditCardSource.XWeb + "," + (int)CreditCardSource.XWebPortalLogin + ") ";//Not created from the Patient Portal if (listClinicNums != null && listClinicNums.Count > 0) { command += "AND ppc.maxClinicNum IN (" + string.Join(",", listClinicNums.Select(x => POut.Long(x))) + ") "; } 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,PayPlanPatNum," + "ClinicNum,cc.Procedues,pat.BillingCycleDay,pat.Guarantor,cc.PayConnectToken,cc.PayConnectTokenExp,cc.PaySimpleToken "; } command += "HAVING PayPlanDueCalc>0)"; //don't show cc's attached to payplans when the payplan has nothing due #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 DateStart<=" + DbHelper.Curdate() + " AND " + DbHelper.Year("DateStart") + ">1880 " + "AND (DateStop>=" + DbHelper.Curdate() + " OR " + DbHelper.Year("DateStop") + "<1880) " + "ORDER BY GuarLName,GuarFName,PatName,PayOrder DESC"; table = Db.GetTable(command); //Query for latest payments seperately because this takes a very long time when run as a sub select if (table.Rows.Count < 1) { return(table); } command = "SELECT PatNum,MAX(CASE WHEN " + DbHelper.Year("RecurringChargeDate") + " > 1880 " + "THEN RecurringChargeDate ELSE PayDate END) RecurringChargeDate " + "FROM payment " + "WHERE IsRecurringCC=1 AND PayAmt > 0 " + "AND PatNum IN (" + string.Join(",", table.Select().Select(x => POut.String(x["PatNum"].ToString()))) + ") " //table has at least 1 row + "GROUP BY PatNum"; //dictionary is key=PatNum, value=LatestPayment which will be the lastest date a recurring charge payment was made Dictionary <long, DateTime> dictPatNumDate = Db.GetTable(command).Select() .ToDictionary(x => PIn.Long(x["PatNum"].ToString()), x => PIn.Date(x["RecurringChargeDate"].ToString())); table.Select().Where(x => dictPatNumDate.ContainsKey(PIn.Long(x["PatNum"].ToString()))).ToList() .ForEach(x => x["LatestPayment"] = dictPatNumDate[PIn.Long(x["PatNum"].ToString())]); FilterRecurringChargeList(table); return(table); }