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