public OutstandingInsClaim(DataRow rowCur) { CarrierName = rowCur["CarrierName"].ToString(); CarrierPhone = rowCur["CarrierPhone"].ToString(); ClaimType = rowCur["ClaimType"].ToString(); PatFName = rowCur["PatFName"].ToString(); PatLName = rowCur["PatLName"].ToString(); PatMiddleI = rowCur["PatMiddleI"].ToString(); PatNum = PIn.Long(rowCur["PatNum"].ToString()); PatDOB = PIn.DateT(rowCur["PatDOB"].ToString()); DateService = PIn.DateT(rowCur["DateService"].ToString()); DateSent = PIn.DateT(rowCur["DateSent"].ToString()); DateOrigSent = PIn.DateT(rowCur["DateOrigSent"].ToString()); ClaimFee = PIn.Decimal(rowCur["ClaimFee"].ToString()); ClaimNum = PIn.Long(rowCur["ClaimNum"].ToString()); ClinicNum = PIn.Long(rowCur["ClinicNum"].ToString()); DaysSuppressed = PIn.Int(rowCur["DaysSuppressed"].ToString()); DateLog = PIn.DateT(rowCur["DateLog"].ToString()); ErrorCodeDefNum = PIn.Long(rowCur["ErrorCodeDefNum"].ToString()); GroupNum = PIn.String(rowCur["GroupNum"].ToString()); GroupName = PIn.String(rowCur["GroupName"].ToString()); SubName = PIn.String(rowCur["SubName"].ToString()); SubDOB = PIn.DateT(rowCur["SubDOB"].ToString()); SubID = PIn.String(rowCur["SubID"].ToString()); CustomTrackingDefNum = PIn.Long(rowCur["CustomTrackingDefNum"].ToString()); UserNum = PIn.Long(rowCur["UserNum"].ToString()); }
///<summary>Returns the sum of adjustments for the date range for the passed in operatories or providers.</summary> public static decimal GetAdjustAmtForAptView(DateTime dateStart, DateTime dateEnd, long clinicNum, List <long> listOpNums, List <long> listProvNums) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetObject <decimal>(MethodBase.GetCurrentMethod(), dateStart, dateEnd, clinicNum, listOpNums, listProvNums)); } string command = GetQueryAdjustmentsForAppointments(dateStart, dateEnd, listOpNums, doGetSum: true); if (!listProvNums.IsNullOrEmpty()) { command += "AND adjustment.ProvNum IN(" + string.Join(",", listProvNums.Select(x => POut.Long(x))) + ") "; } if (clinicNum > 0 && PrefC.HasClinicsEnabled) { command += "AND adjustment.ClinicNum=" + POut.Long(clinicNum); } return(PIn.Decimal(Db.GetScalar(command))); }
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 List <List <int> > GetProdProvs(DateTime dateFrom, DateTime dateTo) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetObject <List <List <int> > >(MethodBase.GetCurrentMethod(), dateFrom, dateTo)); } Random rnd = new Random(); string rndStr = rnd.Next(1000000).ToString(); string command; #if DEBUG _elapsedTimeProdProvs = ""; System.Diagnostics.Stopwatch stopWatch = new System.Diagnostics.Stopwatch(); System.Diagnostics.Stopwatch stopWatchTotal = new System.Diagnostics.Stopwatch(); _elapsedTimeProdProvs = "Elapsed time for GetProdProvs:\r\n"; stopWatch.Restart(); stopWatchTotal.Restart(); #endif command = "DROP TABLE IF EXISTS tempdash" + rndStr + @";"; Db.NonQ(command); #if DEBUG stopWatch.Stop(); _elapsedTimeProdProvs += "DROP TABLE: " + stopWatch.Elapsed.ToString() + "\r\n"; stopWatch.Restart(); #endif //this table will contain approx 12x3xProv rows if there was production for each prov in each month. command = @"CREATE TABLE tempdash" + rndStr + @" ( DatePeriod date NOT NULL, ProvNum bigint NOT NULL, production decimal NOT NULL ) DEFAULT CHARSET=utf8" ; Db.NonQ(command); #if DEBUG stopWatch.Stop(); _elapsedTimeProdProvs += "CREATE TABLE: " + stopWatch.Elapsed.ToString() + "\r\n"; stopWatch.Restart(); #endif //procs. Inserts approx 12xProv rows command = @"INSERT INTO tempdash" + rndStr + @" SELECT procedurelog.ProcDate,procedurelog.ProvNum, SUM(procedurelog.ProcFee*(procedurelog.UnitQty+procedurelog.BaseUnits))-IFNULL(SUM(claimproc.WriteOff),0) FROM procedurelog USE INDEX(indexPNPD) LEFT JOIN claimproc ON procedurelog.ProcNum=claimproc.ProcNum AND claimproc.Status='7' /*only CapComplete writeoffs are subtracted here*/ WHERE procedurelog.ProcStatus = '2' AND procedurelog.ProcDate >= " + POut.Date(dateFrom) + @" AND procedurelog.ProcDate <= " + POut.Date(dateTo) + @" GROUP BY procedurelog.ProvNum,MONTH(procedurelog.ProcDate)" ; Db.NonQ(command); #if DEBUG stopWatch.Stop(); _elapsedTimeProdProvs += "INSERT INTO: " + stopWatch.Elapsed.ToString() + "\r\n"; stopWatch.Restart(); #endif //todo 2 more tables //get all the data as 12xProv rows command = @"SELECT DatePeriod,ProvNum,SUM(production) prod FROM tempdash" + rndStr + @" GROUP BY ProvNum,MONTH(DatePeriod)" ;//this fails with date issue DataTable tableProd = Db.GetTable(command); #if DEBUG stopWatch.Stop(); _elapsedTimeProdProvs += "tableProd: " + stopWatch.Elapsed.ToString() + "\r\n"; stopWatch.Restart(); #endif command = "DROP TABLE IF EXISTS tempdash" + rndStr + @";"; #if DEBUG stopWatch.Stop(); _elapsedTimeProdProvs += "DROP TABLE: " + stopWatch.Elapsed.ToString() + "\r\n"; stopWatch.Restart(); #endif Db.NonQ(command); command = @"SELECT ProvNum FROM provider WHERE IsHidden=0 ORDER BY ItemOrder" ; DataTable tableProv = Db.GetTable(command); #if DEBUG stopWatch.Stop(); stopWatchTotal.Stop(); _elapsedTimeProdProvs += "SELECT ProvNum FROM provider: " + stopWatch.Elapsed.ToString() + "\r\n"; _elapsedTimeProdProvs += "Total: " + stopWatchTotal.Elapsed.ToString(); if (_showElapsedTimesForDebug) { System.Windows.Forms.MessageBox.Show(_elapsedTimeProdProvs); } #endif List <List <int> > retVal = new List <List <int> >(); for (int p = 0; p < tableProv.Rows.Count; p++) //loop through each provider { long provNum = PIn.Long(tableProv.Rows[p]["ProvNum"].ToString()); List <int> listInt = new List <int>(); //12 items for (int i = 0; i < 12; i++) { decimal prod = 0; DateTime datePeriod = dateFrom.AddMonths(i); //only the month and year are important for (int j = 0; j < tableProd.Rows.Count; j++) { if (provNum == PIn.Long(tableProd.Rows[j]["ProvNum"].ToString()) && datePeriod.Month == PIn.Date(tableProd.Rows[j]["DatePeriod"].ToString()).Month && datePeriod.Year == PIn.Date(tableProd.Rows[j]["DatePeriod"].ToString()).Year) { prod = PIn.Decimal(tableProd.Rows[j]["prod"].ToString()); break; } } listInt.Add((int)(prod)); } retVal.Add(listInt); } return(retVal); }
public static 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); }
///<summary>Gets the full list to display in the Chart of Accounts, including balances.</summary> public static DataTable GetFullList(DateTime asOfDate, bool showInactive) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetTable(MethodBase.GetCurrentMethod(), asOfDate, showInactive)); } DataTable table = new DataTable("Accounts"); DataRow row; //columns that start with lowercase are altered for display rather than being raw data. table.Columns.Add("type"); table.Columns.Add("Description"); table.Columns.Add("balance"); table.Columns.Add("BankNumber"); table.Columns.Add("inactive"); table.Columns.Add("color"); table.Columns.Add("AccountNum"); //but we won't actually fill this table with rows until the very end. It's more useful to use a List<> for now. List <DataRow> rows = new List <DataRow>(); //first, the entire history for the asset, liability, and equity accounts (except Retained Earnings)----------- string command = "SELECT account.AcctType, account.Description, account.AccountNum, " + "SUM(DebitAmt) AS SumDebit, SUM(CreditAmt) AS SumCredit, account.BankNumber, account.Inactive, account.AccountColor " + "FROM account " + "LEFT JOIN journalentry ON journalentry.AccountNum=account.AccountNum AND " + "DateDisplayed <= " + POut.Date(asOfDate) + " WHERE AcctType<=2 "; if (!showInactive) { command += "AND Inactive=0 "; } command += "GROUP BY account.AccountNum, account.AcctType, account.Description, account.BankNumber," + "account.Inactive, account.AccountColor ORDER BY AcctType, Description"; DataTable rawTable = Db.GetTable(command); AccountType aType; decimal debit = 0; decimal credit = 0; for (int i = 0; i < rawTable.Rows.Count; i++) { row = table.NewRow(); aType = (AccountType)PIn.Long(rawTable.Rows[i]["AcctType"].ToString()); row["type"] = Lans.g("enumAccountType", aType.ToString()); row["Description"] = rawTable.Rows[i]["Description"].ToString(); debit = PIn.Decimal(rawTable.Rows[i]["SumDebit"].ToString()); credit = PIn.Decimal(rawTable.Rows[i]["SumCredit"].ToString()); if (DebitIsPos(aType)) { row["balance"] = (debit - credit).ToString("N"); } else { row["balance"] = (credit - debit).ToString("N"); } row["BankNumber"] = rawTable.Rows[i]["BankNumber"].ToString(); if (rawTable.Rows[i]["Inactive"].ToString() == "0") { row["inactive"] = ""; } else { row["inactive"] = "X"; } row["color"] = rawTable.Rows[i]["AccountColor"].ToString(); //it will be an unsigned int at this point. row["AccountNum"] = rawTable.Rows[i]["AccountNum"].ToString(); rows.Add(row); } //now, the Retained Earnings (auto) account----------------------------------------------------------------- DateTime firstofYear = new DateTime(asOfDate.Year, 1, 1); command = "SELECT AcctType, SUM(DebitAmt) AS SumDebit, SUM(CreditAmt) AS SumCredit " + "FROM account,journalentry " + "WHERE journalentry.AccountNum=account.AccountNum " + "AND DateDisplayed < " + POut.Date(firstofYear) //all from previous years + " AND (AcctType=3 OR AcctType=4) " //income or expenses + "GROUP BY AcctType ORDER BY AcctType"; //income first, but could return zero rows. rawTable = Db.GetTable(command); decimal balance = 0; for (int i = 0; i < rawTable.Rows.Count; i++) { aType = (AccountType)PIn.Long(rawTable.Rows[i]["AcctType"].ToString()); debit = PIn.Decimal(rawTable.Rows[i]["SumDebit"].ToString()); credit = PIn.Decimal(rawTable.Rows[i]["SumCredit"].ToString()); //this works for both income and expenses, because we are subracting expenses, so signs cancel balance += credit - debit; } row = table.NewRow(); row["type"] = Lans.g("enumAccountType", AccountType.Equity.ToString()); row["Description"] = Lans.g("Accounts", "Retained Earnings (auto)"); row["balance"] = balance.ToString("N"); row["BankNumber"] = ""; row["color"] = Color.White.ToArgb(); row["AccountNum"] = "0"; rows.Add(row); //finally, income and expenses------------------------------------------------------------------------------ command = "SELECT account.AcctType, account.Description, account.AccountNum, " + "SUM(DebitAmt) AS SumDebit, SUM(CreditAmt) AS SumCredit, account.BankNumber, account.Inactive, account.AccountColor " + "FROM account " + "LEFT JOIN journalentry ON journalentry.AccountNum=account.AccountNum " + "AND DateDisplayed <= " + POut.Date(asOfDate) + " AND DateDisplayed >= " + POut.Date(firstofYear) //only for this year + " WHERE (AcctType=3 OR AcctType=4) "; if (!showInactive) { command += "AND Inactive=0 "; } command += "GROUP BY account.AccountNum, account.AcctType, account.Description, account.BankNumber," + "account.Inactive, account.AccountColor ORDER BY AcctType, Description"; rawTable = Db.GetTable(command); for (int i = 0; i < rawTable.Rows.Count; i++) { row = table.NewRow(); aType = (AccountType)PIn.Long(rawTable.Rows[i]["AcctType"].ToString()); row["type"] = Lans.g("enumAccountType", aType.ToString()); row["Description"] = rawTable.Rows[i]["Description"].ToString(); debit = PIn.Decimal(rawTable.Rows[i]["SumDebit"].ToString()); credit = PIn.Decimal(rawTable.Rows[i]["SumCredit"].ToString()); if (DebitIsPos(aType)) { row["balance"] = (debit - credit).ToString("N"); } else { row["balance"] = (credit - debit).ToString("N"); } row["BankNumber"] = rawTable.Rows[i]["BankNumber"].ToString(); if (rawTable.Rows[i]["Inactive"].ToString() == "0") { row["inactive"] = ""; } else { row["inactive"] = "X"; } row["color"] = rawTable.Rows[i]["AccountColor"].ToString(); //it will be an unsigned int at this point. row["AccountNum"] = rawTable.Rows[i]["AccountNum"].ToString(); rows.Add(row); } for (int i = 0; i < rows.Count; i++) { table.Rows.Add(rows[i]); } 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); }
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); }