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