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