///<Summary>asOfDate is typically 12/31/... </Summary>
public static double NetIncomeThisYear(DateTime asOfDate)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetDouble(MethodBase.GetCurrentMethod(), asOfDate));
}
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>Gets the balance of an account directly from the database.</summary>
public static double GetBalance(long accountNum, AccountType acctType)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <double>(MethodBase.GetCurrentMethod(), accountNum, acctType));
}
string command = "SELECT SUM(DebitAmt),SUM(CreditAmt) FROM journalentry "
+ "WHERE AccountNum=" + POut.Long(accountNum)
+ " GROUP BY AccountNum";
DataTable table = Db.GetTable(command);
double debit = 0;
double credit = 0;
if (table.Rows.Count > 0)
{
debit = PIn.Double(table.Rows[0][0].ToString());
credit = PIn.Double(table.Rows[0][1].ToString());
}
if (DebitIsPos(acctType))
{
return(debit - credit);
}
else
{
return(credit - debit);
}
/*}
* catch {
* Debug.WriteLine(command);
* MessageBox.Show(command);
* }
* return 0;*/
}
///<summary>Deletes the payment as well as all splits. Surround by try catch, because it will throw an exception if trying to delete a payment attached to a deposit.</summary>
public static void Delete(Payment pay)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), pay);
return;
}
string command = "SELECT DepositNum,PayAmt FROM payment WHERE PayNum=" + POut.Long(pay.PayNum);
DataTable table = Db.GetTable(command);
if (table.Rows.Count == 0)
{
return;
}
if (table.Rows[0]["DepositNum"].ToString() != "0" && //if payment is already attached to a deposit
PIn.Double(table.Rows[0]["PayAmt"].ToString()) != 0) //and it's not new
{
#if !DEBUG
throw new ApplicationException(Lans.g("Payments", "Not allowed to delete a payment attached to a deposit."));
#endif
}
command = "DELETE from payment WHERE payNum = '" + pay.PayNum.ToString() + "'";
Db.NonQ(command);
//this needs to be improved to handle EstBal
command = "DELETE from paysplit WHERE payNum = '" + pay.PayNum.ToString() + "'";
Db.NonQ(command);
//PaySplit[] splitList=PaySplits.RefreshPaymentList(PayNum);
//for(int i=0;i<splitList.Length;i++){
// splitList[i].Delete();
//}
}
///<summary>Returns the tax estimate for this specific patient, procCode, and feeAmts.
///Calls AvaTax/CreateTransaction: https://developer.avalara.com/api-reference/avatax/rest/v2/methods/Transactions/CreateTransaction/ but does
///not save the transaction in the Avalara DB.</summary>
public static decimal GetEstimate(long codeNum, long patNum, double procFee, bool hasExceptions = false)
{
if (!IsTaxable(patNum))
{
return(0);
}
ProcedureCode procCode = ProcedureCodes.GetProcCode(codeNum);
string strTaxOverride = GetTaxOverrideIfNeeded(Patients.GetPat(patNum), procCode);
if (strTaxOverride != "" && strTaxOverride.EndsWith("%"))
{
double percent = PIn.Double(strTaxOverride.TrimEnd('%'));
return((decimal)(procFee * percent / 100d));
}
try {
TransactionBuilder builder = SetUpTransaction(DocumentType.SalesOrder, patNum); //Sales Order is AvaTax's way of getting an estimate
builder.WithLine((decimal)procFee, 1, strTaxOverride, procCode.Descript, procCode.ProcCode);
TransactionModel result = Client.CreateTransaction("Lines", builder.GetCreateTransactionModel());
return(result.totalTax.Value);
}
catch (Exception ex) {
_logger.WriteLine($"Error getting estimate from Avatax for PatNum: {patNum}", LogLevel.Error);
if (hasExceptions)
{
throw ex; //Loses call stack, but everywhere that catches this only cares about the message.
}
ex.DoNothing();
//For now we just enter $0 because we don't have any proc or adjustment to attach this to, and we already have logging for errors
return(0);
}
}
///<summary>Deletes the payment as well as all splits.
///Surround with try catch, throws an exception if trying to delete a payment attached to a deposit.</summary>
public static void Delete(long payNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), payNum);
return;
}
string command = "SELECT DepositNum,PayAmt FROM payment WHERE PayNum=" + POut.Long(payNum);
DataTable table = Db.GetTable(command);
if (table.Rows.Count == 0)
{
return;
}
if (table.Rows[0]["DepositNum"].ToString() != "0" && //if payment is already attached to a deposit
PIn.Double(table.Rows[0]["PayAmt"].ToString()) != 0) //and it's not new
{
throw new ApplicationException(Lans.g("Payments", "Not allowed to delete a payment attached to a deposit."));
}
command = "DELETE from payment WHERE PayNum = " + POut.Long(payNum);
Db.NonQ(command);
//this needs to be improved to handle EstBal
command = "DELETE from paysplit WHERE PayNum = " + POut.Long(payNum);
Db.NonQ(command);
command = "UPDATE recurringcharge SET PayNum=0 WHERE PayNum=" + POut.Long(payNum);
Db.NonQ(command);
}
///<summary>Called just before Allocate in FormPayment.butOK click. If true, then it will prompt the user before allocating.</summary>
public static bool AllocationRequired(double payAmt, long patNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetBool(MethodBase.GetCurrentMethod(), payAmt, patNum));
}
string command = "SELECT EstBalance FROM patient "
+ "WHERE PatNum = " + POut.Long(patNum);
DataTable table = Db.GetTable(command);
double estBal = 0;
if (table.Rows.Count > 0)
{
estBal = PIn.Double(table.Rows[0][0].ToString());
}
if (!PrefC.GetBool(PrefName.BalancesDontSubtractIns))
{
command = @"SELECT SUM(InsPayEst)+SUM(Writeoff)
FROM claimproc
WHERE PatNum=" + POut.Long(patNum) + " "
+ "AND Status=0"; //NotReceived
table = Db.GetTable(command);
if (table.Rows.Count > 0)
{
estBal -= PIn.Double(table.Rows[0][0].ToString());
}
}
if (payAmt > estBal)
{
return(true);
}
return(false);
}
private static void AddRowsFromDict <T>(RpAgingParamObject rpo, Dictionary <T, DataRow> dict, DataTable insAgingTable)
{
foreach (DataRow rowCur in dict.Values)
{
double insPayEstTotal = PIn.Double(rowCur["InsPayEst_Total"].ToString());
double patBalTotal = PIn.Double(rowCur["PatBal_Total"].ToString()) + insPayEstTotal;
if (patBalTotal <= -0.005)
{
insAgingTable.Rows.Add(rowCur);
continue;
}
double insWoChange = PIn.Double(rowCur["InsWoChange"].ToString());
double patBal0_30 = PIn.Double(rowCur["PatBal_0_30"].ToString()) + PIn.Double(rowCur["InsPayEst_0_30"].ToString());
double patBal31_60 = PIn.Double(rowCur["PatBal_31_60"].ToString()) + PIn.Double(rowCur["InsPayEst_31_60"].ToString());
double patBal61_90 = PIn.Double(rowCur["PatBal_61_90"].ToString()) + PIn.Double(rowCur["InsPayEst_61_90"].ToString());
double patBal90 = PIn.Double(rowCur["PatBal_90"].ToString()) + PIn.Double(rowCur["InsPayEst_90"].ToString());
if ((!insPayEstTotal.IsZero() || !insWoChange.IsZero()) && new[] { patBal0_30, patBal31_60, patBal61_90, patBal90 }.All(x => x < 0.005))
{
insAgingTable.Rows.Add(rowCur);
continue;
}
if (patBal90 >= 0.005 || //always include if bal over 90
(rpo.AccountAge <= AgeOfAccount.Over60 && patBal61_90 >= 0.005) || //if age 60, 30, or Any, include if bal 61 to 90
(rpo.AccountAge <= AgeOfAccount.Over30 && patBal31_60 >= 0.005) || //if age 30 or Any, include if bal 31 to 60
(rpo.AccountAge == AgeOfAccount.Any && patBal0_30 >= 0.005)) //if Any age, include if bal 0 to 30
{
insAgingTable.Rows.Add(rowCur);
}
}
}
/// <summary>Gets info directly from database. Used from PayPlan and Account windows to get the amount paid so far on one payment plan.</summary>
public static double GetAmtPaid(PayPlan payPlan)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <double>(MethodBase.GetCurrentMethod(), payPlan));
}
string command;
if (payPlan.PlanNum == 0) //Patient payment plan
{
command = "SELECT SUM(paysplit.SplitAmt) FROM paysplit "
+ "WHERE paysplit.PayPlanNum = " + POut.Long(payPlan.PayPlanNum) + " "
+ "GROUP BY paysplit.PayPlanNum";
}
else //Insurance payment plan
{
command = "SELECT SUM(claimproc.InsPayAmt) "
+ "FROM claimproc "
+ "WHERE claimproc.Status IN(" + POut.Int((int)ClaimProcStatus.Received) + "," + POut.Int((int)ClaimProcStatus.Supplemental) + ","
+ POut.Int((int)ClaimProcStatus.CapClaim) + ") "
+ "AND claimproc.PayPlanNum=" + POut.Long(payPlan.PayPlanNum);
}
DataTable table = Db.GetTable(command);
if (table.Rows.Count == 0)
{
return(0);
}
return(PIn.Double(table.Rows[0][0].ToString()));
}
///<summary>Returns current clinic limit minus message usage for current calendar month.</summary>
public static double GetClinicBalance(long clinicNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetDouble(MethodBase.GetCurrentMethod(), clinicNum));
}
double limit = 0;
if (!PrefC.HasClinicsEnabled)
{
if (PrefC.GetDate(PrefName.SmsContractDate).Year > 1880)
{
limit = PrefC.GetDouble(PrefName.SmsMonthlyLimit);
}
}
else
{
if (clinicNum == 0 && Clinics.GetCount(true) > 0) //Sending text for "Unassigned" patient. Use the first non-hidden clinic. (for now)
{
clinicNum = Clinics.GetFirst(true).ClinicNum;
}
Clinic clinicCur = Clinics.GetClinic(clinicNum);
if (clinicCur != null && clinicCur.SmsContractDate.Year > 1880)
{
limit = clinicCur.SmsMonthlyLimit;
}
}
DateTime dtStart = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1);
DateTime dtEnd = dtStart.AddMonths(1);
string command = "SELECT SUM(MsgChargeUSD) FROM smstomobile WHERE ClinicNum=" + POut.Long(clinicNum) + " "
+ "AND DateTimeSent>=" + POut.Date(dtStart) + " AND DateTimeSent<" + POut.Date(dtEnd);
limit -= PIn.Double(Db.GetScalar(command));
return(limit);
}
///<summary>Gets a list of unfinalized insurance payments.</summary>
public static List <UnfinalizedInsPay> GetUnfinalizedInsPay(string carrierName)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <UnfinalizedInsPay> >(MethodBase.GetCurrentMethod(), carrierName));
}
string command = @"
SELECT partialpay.PayType,partialpay.PatNum,partialpay.ClaimPaymentNum,partialpay.ClinicNum,partialpay.CarrierName,partialpay.Date,
partialpay.DOS,partialpay.Amount,partialpay.ClaimNum,partialpay.CountPats
FROM (
SELECT 'PartialPayment' PayType,COALESCE(MAX(claimproc.PatNum),0) PatNum,MAX(claimpayment.ClaimPaymentNum) ClaimPaymentNum,
COUNT(DISTINCT claimproc.PatNum) CountPats,MAX(claimpayment.ClinicNum) ClinicNum,MAX(claimpayment.CarrierName) CarrierName,
MAX(claimpayment.CheckDate) Date,COALESCE(MAX(claimproc.ProcDate),'0001-01-01') DOS,MAX(claimpayment.CheckAmt) Amount,0 ClaimNum
FROM claimpayment
LEFT JOIN claimproc ON claimproc.ClaimPaymentNum=claimpayment.ClaimPaymentNum
WHERE claimpayment.IsPartial = 1
AND claimpayment.CarrierName LIKE '%" + POut.String(carrierName.Trim()) + "%' " + @"
GROUP BY claimpayment.ClaimPaymentNum
UNION ALL
SELECT 'UnfinalizedPayment' PayType,MAX(claimproc.PatNum) PatNum,0 ClaimPaymentNum,1 CountPats,MAX(claimproc.ClinicNum) ClinicNum,
MAX(carrier.CarrierName) CarrierName,MAX(claimproc.DateCP) Date,MAX(claimproc.ProcDate) DOS,SUM(claimproc.InsPayAmt) Amount,
claimproc.ClaimNum
FROM claimproc
INNER JOIN insplan ON insplan.PlanNum=claimproc.PlanNum
INNER JOIN carrier ON carrier.CarrierNum=insplan.CarrierNum
AND carrier.CarrierName LIKE '%" + POut.String(carrierName.Trim()) + "%' "
//Filter logic here mimics batch payments in ClaimProcs.AttachAllOutstandingToPayment().
+ @"WHERE claimproc.ClaimPaymentNum = 0 AND claimproc.InsPayAmt != 0
AND claimproc.Status IN(" + POut.Int((int)ClaimProcStatus.Received) + ","
+ POut.Int((int)ClaimProcStatus.Supplemental) + "," + POut.Int((int)ClaimProcStatus.CapClaim) + @")
AND claimproc.IsTransfer=0
GROUP BY claimproc.ClaimNum
) partialpay" ;
DataTable table = ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(command));
List <Patient> listPats = Patients.GetMultPats(table.Select().Select(x => PIn.Long(x["PatNum"].ToString())).ToList()).ToList();
List <Claim> listClaims = Claims.GetClaimsFromClaimNums(table.Select().Select(x => PIn.Long(x["ClaimNum"].ToString())).ToList());
List <ClaimPayment> listPayments = ClaimPayments.GetByClaimPaymentNums(table.Select().Select(x => PIn.Long(x["ClaimPaymentNum"].ToString()))
.ToList());
List <UnfinalizedInsPay> listUnfinalizedInsPay = new List <UnfinalizedInsPay>();
for (int i = 0; i < table.Rows.Count; i++)
{
listUnfinalizedInsPay.Add(new UnfinalizedInsPay(table.Rows[i]["PayType"].ToString(),
listPats.FirstOrDefault(x => x.PatNum == PIn.Long(table.Rows[i]["PatNum"].ToString())),
PIn.Long(table.Rows[i]["ClinicNum"].ToString()),
table.Rows[i]["CarrierName"].ToString(),
PIn.Date(table.Rows[i]["Date"].ToString()),
PIn.Date(table.Rows[i]["DOS"].ToString()),
PIn.Double(table.Rows[i]["Amount"].ToString()),
listPayments.FirstOrDefault(x => x.ClaimPaymentNum == PIn.Long(table.Rows[i]["ClaimPaymentNum"].ToString())),
listClaims.FirstOrDefault(x => x.ClaimNum == PIn.Long(table.Rows[i]["ClaimNum"].ToString())),
PIn.Int(table.Rows[i]["CountPats"].ToString())
));
}
return(listUnfinalizedInsPay);
}
///<summary>Sums all adjustments for a proc then returns that sum.</summary>
public static double GetTotForProc(long procNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetDouble(MethodBase.GetCurrentMethod(), procNum));
}
string command = "SELECT SUM(AdjAmt) FROM adjustment"
+ " WHERE ProcNum=" + POut.Long(procNum);
return(PIn.Double(Db.GetScalar(command)));
}
///<summary>Optionally set hasConnectionLost true to keep the calling thread here until a connection to the Middle Tier connection can be established
///in the event of a web connection failure. Set hasConnectionLost to false if a throw is desired when a connection cannot be made.</summary>
public static double ProcessGetDouble(DtoGetDouble dto, bool hasConnectionLost = true)
{
string result = SendAndReceive(dto, hasConnectionLost);
try {
return(PIn.Double(result));
}
catch (Exception ex) {
throw ProcessExceptionDeserialize(result, ex);
}
}
///<summary>Only one dimension to the list for now.</summary>
public static List <List <int> > GetAR(DateTime dateFrom, DateTime dateTo, List <DashboardAR> listDashAR)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <List <int> > >(MethodBase.GetCurrentMethod(), dateFrom, dateTo, listDashAR));
}
//assumes that dateFrom is the first of the month and that there are 12 periods
//listDashAR may be empty, in which case, this routine will take about 18 seconds, but the user was warned.
//listDashAR may also me incomplete, especially the most recent month(s).
string command;
List <int> listInt;
listInt = new List <int>();
bool agingWasRun = false;
for (int i = 0; i < 12; i++)
{
DateTime dateLastOfMonth = dateFrom.AddMonths(i + 1).AddDays(-1);
DashboardAR dash = null;
for (int d = 0; d < listDashAR.Count; d++)
{
if (listDashAR[d].DateCalc != dateLastOfMonth)
{
continue;
}
dash = listDashAR[d];
}
if (dash != null) //we found a DashboardAR object from the database for this month, so use it.
{
listInt.Add((int)dash.BalTotal);
continue;
}
agingWasRun = true;
//run historical aging on all patients based on the date entered.
Ledgers.ComputeAging(0, dateLastOfMonth, true);
command = @"SELECT SUM(Bal_0_30+Bal_31_60+Bal_61_90+BalOver90),SUM(InsEst) FROM patient";
DataTable table = Db.GetTable(command);
dash = new DashboardAR();
dash.DateCalc = dateLastOfMonth;
dash.BalTotal = PIn.Double(table.Rows[0][0].ToString());
dash.InsEst = PIn.Double(table.Rows[0][1].ToString());
DashboardARs.Insert(dash); //save it to the db for later.
listInt.Add((int)dash.BalTotal); //and also use it now.
}
if (agingWasRun)
{
Ledgers.RunAging(); //set aging back to normal
}
List <List <int> > retVal = new List <List <int> >();
retVal.Add(listInt);
return(retVal);
}
///<summary></summary>
public static double ProcessGetDouble(DtoGetDouble dto)
{
string result = SendAndReceive(dto); //this might throw an exception if server unavailable
try {
return(PIn.Double(result));
}
catch {
DtoException exception = (DtoException)DataTransferObject.Deserialize(result);
throw ThrowExceptionForDto(exception);
}
}
///<summary>Gets a pref of type double.</summary>
public static double GetDouble(PrefName prefName)
{
if (Dict == null)
{
Prefs.RefreshCache();
}
if (!Dict.ContainsKey(prefName.ToString()))
{
throw new Exception(prefName + " is an invalid pref name.");
}
return(PIn.Double(Dict[prefName.ToString()].ValueString));
}
/// <summary>Adds up the total fees for the procedures passed in that have been completed since the last billing day.</summary>
public static double TotalRecurringCharges(long patNum, string procedures, int billingDay)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetDouble(MethodBase.GetCurrentMethod(), patNum, procedures, billingDay));
}
//Find the beginning of the current billing cycle, use that date to total charges between now and then for this cycle only.
//Include that date only when we are not on the first day of the current billing cycle.
DateTime startBillingCycle;
if (DateTime.Today.Day > billingDay) //if today is 7/13/2015 and billingDay is 26, startBillingCycle will be 6/26/2015
{
startBillingCycle = new DateTime(DateTime.Today.Year, DateTime.Today.Month, billingDay);
}
else
{
//DateTime.Today.AddMonths handles the number of days in the month and leap years
//Examples: if today was 12/31/2015, AddMonths(-1) would yield 11/30/2015; if today was 3/31/2016, AddMonths(-1) would yield 2/29/2016
startBillingCycle = DateTime.Today.AddMonths(-1);
if (billingDay <= DateTime.DaysInMonth(startBillingCycle.Year, startBillingCycle.Month))
{
//This corrects the issue of a billing cycle day after today but this month doesn't have enough days when last month does
//Example: if today was 11/30/2015 and the pat's billing cycle day was the 31st, startBillingCycle=Today.AddMonths(-1) would be 10/30/2015.
//But this pat's billing cycle day is the 31st and the December has 31 days. This adjusts the start of the billing cycle to 10/31/2015.
//Example 2: if today was 2/29/2016 (leap year) and the pat's billing cycle day was the 30th, startBillingCycle should be 1/30/2016.
//Today.AddMonths(-1) would be 1/29/2016, so this adjusts startBillingCycle to 1/30/2016.
startBillingCycle = new DateTime(startBillingCycle.Year, startBillingCycle.Month, billingDay);
}
}
string procStr = "'" + POut.String(procedures).Replace(",", "','") + "'";
string command = "SELECT SUM(pl.ProcFee) "
+ "FROM procedurelog pl "
+ "INNER JOIN procedurecode pc ON pl.CodeNum=pc.CodeNum "
+ "WHERE pl.ProcStatus=2 "
+ "AND pc.ProcCode IN (" + procStr + ") "
+ "AND pl.PatNum=" + POut.Long(patNum) + " "
+ "AND pl.ProcDate<=" + DbHelper.Curdate() + " ";
//If today is the billingDay or today is the last day of the current month and the billingDay is greater than today
//i.e. billingDay=31 and today is the 30th which is the last day of the current month, only count procs with date after the 31st of last month
if (billingDay == DateTime.Today.Day ||
(billingDay > DateTime.Today.Day &&
DateTime.Today.Day == DateTime.DaysInMonth(DateTime.Today.Year, DateTime.Today.Month)))
{
command += "AND pl.ProcDate>" + POut.Date(startBillingCycle);
}
else
{
command += "AND pl.ProcDate>=" + POut.Date(startBillingCycle);
}
return(PIn.Double(Db.GetScalar(command)));
}
///<summary>Returns true if the feeAmtNewStr is an amount that does not match fee, either because fee is null and feeAmtNewStr is not, or because
///fee not null and the feeAmtNewStr is an equal amount, including a blank entry.</summary>
public static bool IsFeeAmtEqual(Fee fee, string feeAmtNewStr)
{
//There is no fee in the database and the user didn't set a new fee value so there is no change.
if (fee == null && feeAmtNewStr == "")
{
return(true);
}
//Fee exists, but new amount is the same.
if (fee != null && (feeAmtNewStr != "" && fee.Amount == PIn.Double(feeAmtNewStr) || (fee.Amount == -1 && feeAmtNewStr == "")))
{
return(true);
}
return(false);
}
///<summary>Sums all adjustments for a proc then returns that sum. Pass false to canIncludeTax in order to exclude sales tax from the end amount.
///</summary>
public static double GetTotForProc(long procNum, bool canIncludeTax = true)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetDouble(MethodBase.GetCurrentMethod(), procNum, canIncludeTax));
}
string command = "SELECT SUM(AdjAmt) FROM adjustment"
+ " WHERE ProcNum=" + POut.Long(procNum);
if (AvaTax.IsEnabled() && !canIncludeTax)
{
command += " AND AdjType NOT IN (" + string.Join(",", POut.Long(AvaTax.SalesTaxAdjType), POut.Long(AvaTax.SalesTaxReturnAdjType)) + ")";
}
return(PIn.Double(Db.GetScalar(command)));
}
//Retotals all items attached to order and updates AmountTotal.
public static SupplyOrder UpdateOrderPrice(long orderNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <SupplyOrder>(MethodBase.GetCurrentMethod(), orderNum));
}
string command = "SELECT SUM(Qty*Price) FROM supplyorderitem WHERE SupplyOrderNum=" + orderNum;
double amountTotal = PIn.Double(Db.GetScalar(command));
command = "SELECT * FROM supplyorder WHERE SupplyOrderNum=" + orderNum;
SupplyOrder so = Crud.SupplyOrderCrud.SelectOne(command);
so.AmountTotal = amountTotal;
SupplyOrders.Update(so);
return(so);
}
///<summary></summary>
public static double GetTotTaxForProc(Procedure proc)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetDouble(MethodBase.GetCurrentMethod(), proc));
}
if (!AvaTax.DoSendProcToAvalara(proc))
{
return(0);
}
string command = "SELECT SUM(AdjAmt) FROM adjustment"
+ " WHERE ProcNum=" + POut.Long(proc.ProcNum)
+ " AND AdjType IN (" + string.Join(",", POut.Long(AvaTax.SalesTaxAdjType), POut.Long(AvaTax.SalesTaxReturnAdjType)) + ")";
return(PIn.Double(Db.GetScalar(command)));
}
public static double GetValAmountTotal(long claimNum, string valCode)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <double>(MethodBase.GetCurrentMethod(), claimNum, valCode));
}
//double total = 0;
string command = "SELECT SUM(ValAmount) FROM claimvalcodelog WHERE ClaimNum=" + POut.Long(claimNum) + " AND ValCode='" + POut.String(valCode) + "'";
return(PIn.Double(Db.GetScalar(command)));
//DataTable table=Db.GetTable(command);
//for(int i=0;i<table.Rows.Count;i++){
// total+=PIn.Double(table.Rows[i][4].ToString());
//}
//return total;
}
/// <summary>Gets info directly from database. Used from PayPlan and Account windows to get the amount paid so far on one payment plan.</summary>
public static double GetAmtPaid(long payPlanNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <double>(MethodBase.GetCurrentMethod(), payPlanNum));
}
string command = "SELECT SUM(paysplit.SplitAmt) FROM paysplit "
+ "WHERE paysplit.PayPlanNum = '" + payPlanNum.ToString() + "' "
+ "GROUP BY paysplit.PayPlanNum";
DataTable table = Db.GetTable(command);
if (table.Rows.Count == 0)
{
return(0);
}
return(PIn.Double(table.Rows[0][0].ToString()));
}
///<summary></summary>
public static DataTable GetForClaim(long claimNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), claimNum));
}
DataTable table = new DataTable();
DataRow row;
table.Columns.Add("amount");
table.Columns.Add("payType");
table.Columns.Add("BankBranch");
table.Columns.Add("ClaimPaymentNum");
table.Columns.Add("checkDate");
table.Columns.Add("CheckNum");
table.Columns.Add("Note");
List <DataRow> rows = new List <DataRow>();
string command = "SELECT BankBranch,claimpayment.ClaimPaymentNum,CheckNum,CheckDate,"
+ "SUM(claimproc.InsPayAmt) amount,Note,PayType "
+ "FROM claimpayment,claimproc "
+ "WHERE claimpayment.ClaimPaymentNum = claimproc.ClaimPaymentNum "
+ "AND claimproc.ClaimNum = '" + POut.Long(claimNum) + "' "
+ "GROUP BY claimpayment.ClaimPaymentNum, BankBranch, CheckDate, CheckNum, Note, PayType";
DataTable rawT = Db.GetTable(command);
DateTime date;
for (int i = 0; i < rawT.Rows.Count; i++)
{
row = table.NewRow();
row["amount"] = PIn.Double(rawT.Rows[i]["amount"].ToString()).ToString("F");
row["payType"] = Defs.GetName(DefCat.InsurancePaymentType, PIn.Long(rawT.Rows[i]["PayType"].ToString()));
row["BankBranch"] = rawT.Rows[i]["BankBranch"].ToString();
row["ClaimPaymentNum"] = rawT.Rows[i]["ClaimPaymentNum"].ToString();
date = PIn.Date(rawT.Rows[i]["CheckDate"].ToString());
row["checkDate"] = date.ToShortDateString();
row["CheckNum"] = rawT.Rows[i]["CheckNum"].ToString();
row["Note"] = rawT.Rows[i]["Note"].ToString();
rows.Add(row);
}
for (int i = 0; i < rows.Count; i++)
{
table.Rows.Add(rows[i]);
}
return(table);
}
/*
* ///<summary>Must make sure Refresh is done first. Returns the sum of all adjustments for this patient. Amount might be pos or neg.</summary>
* public static double ComputeBal(Adjustment[] List){
* double retVal=0;
* for(int i=0;i<List.Length;i++){
* retVal+=List[i].AdjAmt;
* }
* return retVal;
* }*/
///<summary>Returns the number of finance or billing charges deleted.</summary>
public static long UndoFinanceOrBillingCharges(DateTime dateUndo, bool isBillingCharges)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetLong(MethodBase.GetCurrentMethod(), dateUndo, isBillingCharges));
}
string adjTypeStr = "Finance";
long adjTypeDefNum = PrefC.GetLong(PrefName.FinanceChargeAdjustmentType);
if (isBillingCharges)
{
adjTypeStr = "Billing";
adjTypeDefNum = PrefC.GetLong(PrefName.BillingChargeAdjustmentType);
}
string command = "SELECT adjustment.AdjAmt,patient.PatNum,patient.Guarantor,patient.LName,patient.FName,patient.Preferred,patient.MiddleI,"
+ "adjustment.SecDateTEdit "
+ "FROM adjustment "
+ "INNER JOIN patient ON patient.PatNum=adjustment.PatNum "
+ "WHERE AdjDate=" + POut.Date(dateUndo) + " "
+ "AND AdjType=" + POut.Long(adjTypeDefNum);
DataTable table = Db.GetTable(command);
List <Action> listActions = new List <Action>();
int loopCount = 0;
foreach (DataRow row in table.Rows) //loops through the rows and creates audit trail entry for every row to be deleted
{
listActions.Add(new Action(() => {
SecurityLogs.MakeLogEntry(Permissions.AdjustmentEdit, PIn.Long(row["PatNum"].ToString()),
"Delete adjustment for patient, undo " + adjTypeStr.ToLower() + " charges: "
+ Patients.GetNameLF(row["LName"].ToString(), row["FName"].ToString(), row["Preferred"].ToString(), row["MiddleI"].ToString())
+ ", " + PIn.Double(row["AdjAmt"].ToString()).ToString("c"), 0, PIn.DateT(row["SecDateTEdit"].ToString()));
if (++loopCount % 5 == 0)
{
ODEvent.Fire(new ODEventArgs(adjTypeStr + "Charge", Lans.g("FinanceCharge", "Creating log entries for " + adjTypeStr.ToLower() + " charges")
+ ": " + loopCount + " out of " + table.Rows.Count));
}
}));
}
ODThread.RunParallel(listActions, TimeSpan.FromMinutes(2));
command = "DELETE FROM adjustment WHERE AdjDate=" + POut.Date(dateUndo) + " AND AdjType=" + POut.Long(adjTypeDefNum);
ODEvent.Fire(new ODEventArgs(adjTypeStr + "Charge", Lans.g("FinanceCharge", "Deleting") + " " + table.Rows.Count + " "
+ Lans.g("FinanceCharge", adjTypeStr.ToLower() + " charge adjustments") + "..."));
return(Db.NonQ(command));
}
///<summary>Gets the amount used for the specified adjustment (Sums paysplits that have AdjNum passed in). Pass in PayNum to exclude splits on that payment.</summary>
public static double GetAmtAllocated(long adjNum, long excludedPayNum, List <PaySplit> listSplits = null)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetDouble(MethodBase.GetCurrentMethod(), adjNum, excludedPayNum, listSplits));
}
if (listSplits != null)
{
return(listSplits.FindAll(x => x.PayNum != excludedPayNum).Sum(x => x.SplitAmt));
}
else
{
string command = "SELECT SUM(SplitAmt) FROM paysplit WHERE AdjNum=" + POut.Long(adjNum);
if (excludedPayNum != 0)
{
command += " AND PayNum!=" + POut.Long(excludedPayNum);
}
return(PIn.Double(Db.GetScalar(command)));
}
}
///<summary>If trying to change the amount and attached to a deposit, it will throw an error, so surround with try catch.</summary>
public static void Update(ClaimPayment cp, bool isDepNew = false)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), cp, isDepNew);
return;
}
if (!isDepNew && cp.DepositNum != 0 && PrefC.GetBool(PrefName.ShowAutoDeposit))
{
string cmd = "SELECT deposit.Amount,SUM(COALESCE(claimpayment.CheckAmt,0))+SUM(COALESCE(payment.PayAmt,0)) depAmtOthers "
+ "FROM deposit "
+ "LEFT JOIN payment ON payment.DepositNum=deposit.DepositNum "
+ "LEFT JOIN claimpayment ON claimpayment.DepositNum=deposit.DepositNum AND claimpayment.ClaimPaymentNum!=" + POut.Long(cp.ClaimPaymentNum) + " "
+ "WHERE deposit.DepositNum=" + POut.Long(cp.DepositNum);
DataTable tble = Db.GetTable(cmd);
if (tble.Rows.Count == 0)
{
cp.DepositNum = 0;
}
else if (PIn.Double(tble.Rows[0]["depAmtOthers"].ToString()) + cp.CheckAmt != PIn.Double(tble.Rows[0]["Amount"].ToString()))
{
throw new ApplicationException(Lans.g("ClaimPayments", "Not allowed to change the amount on checks attached to deposits."));
}
}
else
{
string command = "SELECT DepositNum,CheckAmt FROM claimpayment "
+ "WHERE ClaimPaymentNum=" + POut.Long(cp.ClaimPaymentNum);
DataTable table = Db.GetTable(command);
if (table.Rows.Count == 0)
{
return;
}
if (table.Rows[0][0].ToString() != "0" && //if claimpayment is already attached to a deposit
PIn.Double(table.Rows[0][1].ToString()) != cp.CheckAmt) //and checkAmt changes
{
throw new ApplicationException(Lans.g("ClaimPayments", "Not allowed to change the amount on checks attached to deposits."));
}
}
Crud.ClaimPaymentCrud.Update(cp);
}
/// <summary>Gets info directly from database. Used when adding a payment.</summary>
public static bool PlanIsPaidOff(long payPlanNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetBool(MethodBase.GetCurrentMethod(), payPlanNum));
}
string command = "SELECT SUM(paysplit.SplitAmt) FROM paysplit "
+ "WHERE PayPlanNum = " + POut.Long(payPlanNum);// +"' "
//+" GROUP BY paysplit.PayPlanNum";
double amtPaid = PIn.Double(Db.GetScalar(command));
command = "SELECT SUM(Principal+Interest) FROM payplancharge "
+ "WHERE ChargeType=" + POut.Int((int)PayPlanChargeType.Debit) + " AND PayPlanNum=" + POut.Long(payPlanNum);
double totalCost = PIn.Double(Db.GetScalar(command));
if (totalCost - amtPaid < .01)
{
return(true);
}
return(false);
}
///<summary>If trying to change the amount and attached to a deposit, it will throw an error, so surround with try catch.</summary>
public static void Update(ClaimPayment cp)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), cp);
return;
}
string command = "SELECT DepositNum,CheckAmt FROM claimpayment "
+ "WHERE ClaimPaymentNum=" + POut.Long(cp.ClaimPaymentNum);
DataTable table = Db.GetTable(command);
if (table.Rows.Count == 0)
{
return;
}
if (table.Rows[0][0].ToString() != "0" && //if claimpayment is already attached to a deposit
PIn.Double(table.Rows[0][1].ToString()) != cp.CheckAmt) //and checkAmt changes
{
throw new ApplicationException(Lans.g("ClaimPayments", "Not allowed to change the amount on checks attached to deposits."));
}
Crud.ClaimPaymentCrud.Update(cp);
}
///<summary>Gets the list of fees by feeschednums and clinicnums from the db. Returns an empty list if listFeeSchedNums is null or empty.
///Throws an application exception if listClinicNums is null or empty. Always provide at least one ClinicNum.
///We throw instead of returning an empty list which would make it look like there are no fees for the fee schedules passed in.
///If this method returns an empty list it is because no valied fee schedules were given or the database truly doesn't have any fees.</summary>
public static List <FeeLim> GetByFeeSchedNumsClinicNums(List <long> listFeeSchedNums, List <long> listClinicNums)
{
if (listFeeSchedNums == null || listFeeSchedNums.Count == 0)
{
return(new List <FeeLim>()); //This won't hurt the FeeCache because there will be no corresponding fee schedules to "blank out".
}
if (listClinicNums == null || listClinicNums.Count == 0)
{
//Returning an empty list here would be detrimental to the FeeCache.
throw new ApplicationException("Invalid listClinicNums passed into GetByFeeSchedNumsClinicNums()");
}
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
//Unusual Middle Tier check. This method can cause out of memory exceptions when called over Middle Tier, so we are batching into multiple
//calls of 100 fee schedules at a time.
List <FeeLim> listFeeLims = new List <FeeLim>();
for (int i = 0; i < listFeeSchedNums.Count; i += 100)
{
List <long> listFeeSchedsNumsThisBatch = listFeeSchedNums.GetRange(i, Math.Min(100, listFeeSchedNums.Count - i));
listFeeLims.AddRange(Meth.GetObject <List <FeeLim> >(MethodBase.GetCurrentMethod(), listFeeSchedsNumsThisBatch, listClinicNums));
}
return(listFeeLims);
}
string command = "SELECT FeeNum,Amount,FeeSched,CodeNum,ClinicNum,ProvNum,SecDateTEdit FROM fee "
+ "WHERE FeeSched IN (" + string.Join(",", listFeeSchedNums.Select(x => POut.Long(x))) + ") "
+ "AND ClinicNum IN (" + string.Join(",", listClinicNums.Select(x => POut.Long(x))) + ")";
return(Db.GetTable(command).AsEnumerable()
.Select(x => new FeeLim {
FeeNum = PIn.Long(x["FeeNum"].ToString()),
Amount = PIn.Double(x["Amount"].ToString()),
FeeSched = PIn.Long(x["FeeSched"].ToString()),
CodeNum = PIn.Long(x["CodeNum"].ToString()),
ClinicNum = PIn.Long(x["ClinicNum"].ToString()),
ProvNum = PIn.Long(x["ProvNum"].ToString()),
SecDateTEdit = PIn.DateT(x["SecDateTEdit"].ToString()),
}).ToList());
}
///<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);
}
