///<summary>Gets all SigElements for a set of Signals, ordered by type: user,extras, message.</summary>
public static SigElement[] GetElements(List <Signalod> signalList)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <SigElement[]>(MethodBase.GetCurrentMethod(), signalList));
}
if (signalList.Count == 0)
{
return(new SigElement[0]);
}
string command = "SELECT sigelement.* FROM sigelement,sigelementdef WHERE "
+ "sigelement.SigElementDefNum=sigelementdef.SigElementDefNum AND (";
for (int i = 0; i < signalList.Count; i++)
{
if (i > 0)
{
command += " OR ";
}
command += "SignalNum=" + POut.Long(signalList[i].SignalNum);
}
command += ") ORDER BY sigelementdef.SigElementType";
return(Crud.SigElementCrud.SelectMany(command).ToArray());
}
///<summary>This returns the number of plans affected.</summary>
public static void UpdatePlan(TrojanObject troj, long planNum, bool updateBenefits)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), troj, planNum, updateBenefits);
return;
}
long employerNum = Employers.GetEmployerNum(troj.ENAME);
string command;
//for(int i=0;i<planNums.Count;i++) {
command = "UPDATE insplan SET "
+ "EmployerNum=" + POut.Long(employerNum) + ", "
+ "GroupName='" + POut.String(troj.PLANDESC) + "', "
+ "GroupNum='" + POut.String(troj.POLICYNO) + "', "
+ "CarrierNum= " + POut.Long(troj.CarrierNum) + " "
+ "WHERE PlanNum=" + POut.Long(planNum);
Db.NonQ(command);
command = "UPDATE inssub SET "
+ "BenefitNotes='" + POut.String(troj.BenefitNotes) + "' "
+ "WHERE PlanNum=" + POut.Long(planNum);
Db.NonQ(command);
if (updateBenefits)
{
//clear benefits
command = "DELETE FROM benefit WHERE PlanNum=" + POut.Long(planNum);
Db.NonQ(command);
//benefitList
for (int j = 0; j < troj.BenefitList.Count; j++)
{
troj.BenefitList[j].PlanNum = planNum;
Benefits.Insert(troj.BenefitList[j]);
}
InsPlans.ComputeEstimatesForTrojanPlan(planNum);
}
}
public static DataTable GetMissingXTransTable(DateTime dateStart, DateTime dateEnd)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateStart, dateEnd));
}
string command = "SELECT payment.PatNum,LName,FName,payment.DateEntry,payment.PayDate,payment.PayNote,payment.PayAmt "
+ "FROM patient "
+ "INNER JOIN payment ON payment.PatNum=patient.PatNum "
//only payments with the same PaymentType as the X-Charge PaymentType for the clinic
+ "INNER JOIN ("
+ "SELECT ClinicNum,PropertyValue AS PaymentType FROM programproperty "
+ "WHERE ProgramNum=" + POut.Long(Programs.GetProgramNum(ProgramName.Xcharge)) + " AND PropertyDesc='PaymentType'"
+ ") paytypes ON paytypes.ClinicNum=payment.ClinicNum AND paytypes.PaymentType=payment.PayType "
+ "LEFT JOIN xchargetransaction ON xchargetransaction.PatNum=payment.PatNum "
+ "AND " + DbHelper.DtimeToDate("TransactionDateTime") + "=payment.DateEntry "
+ "AND (CASE WHEN xchargetransaction.ResultCode=5 THEN 0 ELSE xchargetransaction.Amount END)=payment.PayAmt "
+ "AND xchargetransaction.ResultCode IN(0,5,10) "
+ "WHERE payment.DateEntry BETWEEN " + POut.Date(dateStart) + " AND " + POut.Date(dateEnd) + " "
+ "AND TransactionDateTime IS NULL "
+ "ORDER BY payment.PayDate ASC,LName,FName";
return(Db.GetTable(command));
}
///<summary>If not using clinics then supply an empty list of clinicNums. listClinicNums must have at least one item if using clinics.</summary>
public static DataTable GetInsTable(DateTime dateFrom, DateTime dateTo, List <long> listProvNums, List <long> listClinicNums,
List <long> listInsuranceTypes, List <long> listClaimPayGroups, bool hasAllProvs, bool hasAllClinics, bool hasInsuranceTypes, bool isGroupedByPatient,
bool hasAllClaimPayGroups, bool doShowProvSeparate)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateFrom, dateTo, listProvNums, listClinicNums, listInsuranceTypes, listClaimPayGroups,
hasAllProvs, hasAllClinics, hasInsuranceTypes, isGroupedByPatient, hasAllClaimPayGroups, doShowProvSeparate));
}
string whereProv = "";
if (!hasAllProvs)
{
whereProv += " AND claimproc.ProvNum IN(";
for (int i = 0; i < listProvNums.Count; i++)
{
if (i > 0)
{
whereProv += ",";
}
whereProv += POut.Long(listProvNums[i]);
}
whereProv += ") ";
}
string whereClin = "";
//reports should no longer use the cache
bool hasClinicsEnabled = ReportsComplex.RunFuncOnReportServer(() => Prefs.HasClinicsEnabledNoCache);
if (hasClinicsEnabled)
{
whereClin += " AND claimproc.ClinicNum IN(";
for (int i = 0; i < listClinicNums.Count; i++)
{
if (i > 0)
{
whereClin += ",";
}
whereClin += POut.Long(listClinicNums[i]);
}
whereClin += ") ";
}
string whereClaimPayGroup = "";
if (!hasAllClaimPayGroups)
{
whereClaimPayGroup = " AND PayGroup IN (" + String.Join(",", listClaimPayGroups) + ") ";
}
string queryIns =
@"SELECT claimproc.DateCP,carrier.CarrierName,MAX("
+ DbHelper.Concat("patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI") + @") lfname,GROUP_CONCAT(DISTINCT provider.Abbr) Provider, ";
if (hasClinicsEnabled)
{
queryIns += "clinic.Abbr Clinic, ";
}
queryIns += @"claimpayment.CheckNum,SUM(claimproc.InsPayAmt) amt,claimproc.ClaimNum,claimpayment.PayType,COUNT(DISTINCT claimproc.PatNum) countPats
FROM claimproc
LEFT JOIN insplan ON claimproc.PlanNum = insplan.PlanNum
LEFT JOIN patient ON claimproc.PatNum = patient.PatNum
LEFT JOIN carrier ON carrier.CarrierNum = insplan.CarrierNum
LEFT JOIN provider ON provider.ProvNum=claimproc.ProvNum
LEFT JOIN claimpayment ON claimproc.ClaimPaymentNum = claimpayment.ClaimPaymentNum " ;
if (hasClinicsEnabled)
{
queryIns += "LEFT JOIN clinic ON clinic.ClinicNum=claimproc.ClinicNum ";
}
queryIns += "WHERE (claimproc.Status=1 OR claimproc.Status=4) " //received or supplemental
+ whereProv
+ whereClin
+ whereClaimPayGroup
+ "AND claimpayment.CheckDate >= " + POut.Date(dateFrom) + " "
+ "AND claimpayment.CheckDate <= " + POut.Date(dateTo) + " ";
if (!hasInsuranceTypes && listInsuranceTypes.Count > 0)
{
queryIns += "AND claimpayment.PayType IN (";
for (int i = 0; i < listInsuranceTypes.Count; i++)
{
if (i > 0)
{
queryIns += ",";
}
queryIns += POut.Long(listInsuranceTypes[i]);
}
queryIns += ") ";
}
queryIns += @"GROUP BY claimproc.DateCP,claimproc.ClaimPaymentNum,";
if (doShowProvSeparate)
{
queryIns += @"provider.ProvNum,";
}
if (hasClinicsEnabled)
{
queryIns += "claimproc.ClinicNum,clinic.Abbr,";
}
queryIns += "carrier.CarrierName,claimpayment.CheckNum";
if (isGroupedByPatient)
{
queryIns += ",patient.PatNum";
}
queryIns += " ORDER BY claimpayment.PayType,claimproc.DateCP,lfname";
if (!hasInsuranceTypes && listInsuranceTypes.Count == 0)
{
queryIns = DbHelper.LimitOrderBy(queryIns, 0);
}
DataTable table = ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(queryIns));
foreach (DataRow row in table.Rows)
{
//If there is more than one patient attached to a check, we will append an asterisk to the end.
int countPats = PIn.Int(row["countPats"].ToString());
if (countPats > 1)
{
row["lfname"] = row["lfname"].ToString().TrimEnd() + "*";
}
}
table.Columns.Remove("countPats"); //Remove this column because we don't want it to show on the report
return(table);
}
///<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 List <PayPlanLink> GetForPayPlans(List <long> listPayPlans)
{
if (listPayPlans.IsNullOrEmpty())
{
return(new List <PayPlanLink>());
}
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <PayPlanLink> >(MethodBase.GetCurrentMethod(), listPayPlans));
}
string command = $"SELECT * FROM payplanlink WHERE PayPlanNum IN ({string.Join(",",listPayPlans.Select(x => POut.Long(x)))}) ";
return(Crud.PayPlanLinkCrud.SelectMany(command));
}
///<summary>This will assign all PlanNums to new value when Create New Plan If Needed is selected and there are multiple subscribers to a plan and an inssub object has been updated to point at a new PlanNum. The PlanNum values need to be reflected in the claim, claimproc, payplan, and etrans tables, since those all both store inssub.InsSubNum and insplan.PlanNum.</summary>
public static void SynchPlanNumsForNewPlan(InsSub SubCur)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), SubCur);
return;
}
//claim.PlanNum
string command = "UPDATE claim SET claim.PlanNum=" + POut.Long(SubCur.PlanNum) + " "
+ "WHERE claim.InsSubNum=" + POut.Long(SubCur.InsSubNum) + " AND claim.PlanNum!=" + POut.Long(SubCur.PlanNum);
Db.NonQ(command);
//claim.PlanNum2
command = "UPDATE claim SET claim.PlanNum2=" + POut.Long(SubCur.PlanNum) + " "
+ "WHERE claim.InsSubNum2=" + POut.Long(SubCur.InsSubNum) + " AND claim.PlanNum2!=" + POut.Long(SubCur.PlanNum);
Db.NonQ(command);
//claimproc.PlanNum
command = "UPDATE claimproc SET claimproc.PlanNum=" + POut.Long(SubCur.PlanNum) + " "
+ "WHERE claimproc.InsSubNum=" + POut.Long(SubCur.InsSubNum) + " AND claimproc.PlanNum!=" + POut.Long(SubCur.PlanNum);
Db.NonQ(command);
//payplan.PlanNum
command = "UPDATE payplan SET payplan.PlanNum=" + POut.Long(SubCur.PlanNum) + " "
+ "WHERE payplan.InsSubNum=" + POut.Long(SubCur.InsSubNum) + " AND payplan.PlanNum!=" + POut.Long(SubCur.PlanNum);
Db.NonQ(command);
//etrans.PlanNum, only used if EtransType.BenefitInquiry270 and BenefitResponse271 and Eligibility_CA.
command = "UPDATE etrans SET etrans.PlanNum=" + POut.Long(SubCur.PlanNum) + " "
+ "WHERE etrans.InsSubNum!=0 AND etrans.InsSubNum=" + POut.Long(SubCur.InsSubNum) + " AND etrans.PlanNum!=" + POut.Long(SubCur.PlanNum);
Db.NonQ(command);
}
public static SupplyOrderItem CreateObject(long supplyOrderItemNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <SupplyOrderItem>(MethodBase.GetCurrentMethod(), supplyOrderItemNum));
}
string command = "SELECT * FROM supplyorderitem WHERE SupplyOrderItemNum=" + POut.Long(supplyOrderItemNum);
return(Crud.SupplyOrderItemCrud.SelectOne(command));
}
///<summary>Sets the end date for the active links for the specific jobnum</summary>
public static void EndForJobNum(long jobNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), jobNum);
return;
}
string command = "UPDATE jobactivelink SET DateTimeEnd=" + POut.DateT(DateTime.Now) + " WHERE JobNum = " + POut.Long(jobNum);
Db.NonQ(command);
}
///<summary>Get all lab results for one patient.</summary>
public static List <LabResult> GetAllForPatient(long patNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <LabResult> >(MethodBase.GetCurrentMethod(), patNum));
}
string command = "SELECT * FROM labresult WHERE LabPanelNum IN (SELECT LabPanelNum FROM labpanel WHERE PatNum=" + POut.Long(patNum) + ")";
return(Crud.LabResultCrud.SelectMany(command));
}
///<summary>If not using clinics then supply an empty list of clinicNums.</summary>
public static DataSet GetPayPlanTable(DateTime dateStart, DateTime dateEnd, List <long> listProvNums, List <long> listClinicNums,
bool hasAllProvs, DisplayPayPlanType displayPayPlanType, bool hideCompletedPlans, bool showFamilyBalance, bool hasDateRange, bool isPayPlanV2)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetDS(MethodBase.GetCurrentMethod(), dateStart, dateEnd, listProvNums, listClinicNums, hasAllProvs, displayPayPlanType,
hideCompletedPlans, showFamilyBalance, hasDateRange, isPayPlanV2));
}
string whereProv = "";
if (!hasAllProvs)
{
whereProv += " AND payplancharge.ProvNum IN(";
for (int i = 0; i < listProvNums.Count; i++)
{
if (i > 0)
{
whereProv += ",";
}
whereProv += POut.Long(listProvNums[i]);
}
whereProv += ") ";
}
string whereClin = "";
bool hasClinicsEnabled = ReportsComplex.RunFuncOnReportServer(() => Prefs.HasClinicsEnabledNoCache);
if (hasClinicsEnabled) //Using clinics
{
whereClin += " AND payplancharge.ClinicNum IN(";
for (int i = 0; i < listClinicNums.Count; i++)
{
if (i > 0)
{
whereClin += ",";
}
whereClin += POut.Long(listClinicNums[i]);
}
whereClin += ") ";
}
DataSet ds = new DataSet();
DataTable table = new DataTable("Clinic");
table.Columns.Add("provider");
table.Columns.Add("guarantor");
table.Columns.Add("ins");
table.Columns.Add("princ");
table.Columns.Add("accumInt");
table.Columns.Add("paid");
table.Columns.Add("balance");
table.Columns.Add("due");
if (isPayPlanV2)
{
table.Columns.Add("notDue");
}
table.Columns.Add("famBal");
table.Columns.Add("clinicName");
DataTable tableTotals = new DataTable("Total");
tableTotals.Columns.Add("clinicName");
tableTotals.Columns.Add("princ");
tableTotals.Columns.Add("accumInt");
tableTotals.Columns.Add("paid");
tableTotals.Columns.Add("balance");
tableTotals.Columns.Add("due");
if (isPayPlanV2)
{
tableTotals.Columns.Add("notDue");
}
tableTotals.Columns.Add("famBal");
DataRow row;
string datesql = "CURDATE()"; //This is used to find out how much people owe currently and has nothing to do with the selected range
if (DataConnection.DBtype == DatabaseType.Oracle)
{
datesql = "(SELECT CURRENT_DATE FROM dual)";
}
List <long> listHiddenUnearnedDefNums = ReportsComplex.RunFuncOnReportServer(() =>
Defs.GetDefsNoCache(DefCat.PaySplitUnearnedType).FindAll(x => !string.IsNullOrEmpty(x.ItemValue)).Select(x => x.DefNum).ToList()
);
string command = "SELECT FName,LName,MiddleI,PlanNum,Preferred,PlanNum, "
+ "COALESCE((SELECT SUM(Principal+Interest) FROM payplancharge WHERE payplancharge.PayPlanNum=payplan.PayPlanNum "
+ "AND payplancharge.ChargeType=" + POut.Int((int)PayPlanChargeType.Debit) + " "//for v1, debits are the only ChargeType.
+ "AND ChargeDate <= " + datesql + @"),0) '_accumDue', ";
command += "COALESCE((SELECT SUM(Interest) FROM payplancharge WHERE payplancharge.PayPlanNum=payplan.PayPlanNum "
+ "AND payplancharge.ChargeType=" + POut.Int((int)PayPlanChargeType.Debit) + " " //for v1, debits are the only ChargeType.
+ "AND ChargeDate <= " + datesql + @"),0) '_accumInt', ";
command += $@"COALESCE((SELECT SUM(SplitAmt) FROM paysplit WHERE paysplit.PayPlanNum=payplan.PayPlanNum ";
if (listHiddenUnearnedDefNums.Count > 0)
{
command += $"AND paysplit.UnearnedType NOT IN ({string.Join(",",listHiddenUnearnedDefNums)}) ";
}
command += @"AND paysplit.PayPlanNum!=0),0) '_paid',
COALESCE((SELECT SUM(InsPayAmt) FROM claimproc WHERE claimproc.PayPlanNum=payplan.PayPlanNum
AND claimproc.Status IN("
+ POut.Int((int)ClaimProcStatus.Received) + ","
+ POut.Int((int)ClaimProcStatus.Supplemental) + ","
+ POut.Int((int)ClaimProcStatus.CapClaim)
+ ") AND claimproc.PayPlanNum!=0),0) '_insPaid', ";
command += "COALESCE(CASE "
//When pay plan isn't dynamic, all charges are already created, so we can sum principal from them to get plans total principal.
+ "WHEN payplan.IsDynamic=0 THEN "
+ "(SELECT SUM(Principal) FROM payplancharge "
//for v1, debits are the only ChargeType.
+ "WHERE payplancharge.PayPlanNum=payplan.PayPlanNum AND payplancharge.ChargeType=" + POut.Int((int)PayPlanChargeType.Debit) + ") "
//When pay plan is dynamic, we will get it from dppprincipal, a table constructed to calculate total principal for dynamic pay plans.
+ "WHEN payplan.IsDynamic=1 THEN dppprincipal.TotalPrincipal ELSE 0 END,0)'_principal', "
+ "COALESCE((SELECT SUM(Principal) FROM payplancharge WHERE payplancharge.PayPlanNum=payplan.PayPlanNum "
+ "AND payplancharge.ChargeType=" + POut.Int((int)PayPlanChargeType.Credit) + "),0) '_credits', " //for v1, will always be 0.
+ "COALESCE((SELECT SUM(Principal) FROM payplancharge WHERE payplancharge.PayPlanNum=payplan.PayPlanNum "
+ "AND payplancharge.ChargeType=" + POut.Int((int)PayPlanChargeType.Credit) + " AND ChargeDate > " + datesql + "),0) '_notDue', "
+ "patient.PatNum PatNum, "
+ "payplancharge.ProvNum ProvNum ";
if (hasClinicsEnabled)
{
command += ", payplancharge.ClinicNum ClinicNum ";
}
//In order to determine if the patient has completely paid off their payment plan we need to get the total amount of interest as of today.
//Then, after the query has run, we'll add the interest up until today with the total principal for the entire payment plan.
//For this reason, we cannot use _accumDue which only gets the principle up until today and not the entire payment plan principle.
command += ",COALESCE((SELECT SUM(Interest) FROM payplancharge WHERE payplancharge.PayPlanNum=payplan.PayPlanNum "
+ "AND payplancharge.ChargeType=" + POut.Int((int)PayPlanChargeType.Debit) + " " //for v1, debits are the only ChargeType.
+ "AND ChargeDate <= " + datesql + @"),0) '_interest' "
+ "FROM payplan "
+ "LEFT JOIN patient ON patient.PatNum=payplan.Guarantor "
+ "LEFT JOIN payplancharge ON payplan.PayPlanNum=payplancharge.PayPlanNum "
+ "LEFT JOIN "
//construct dppprincipal (dynamic payment plan principal) table
+ "(SELECT payplanlink.PayPlanNum,"
//Sum total production linked to dynamic pay plan for all linked adjustments and procedures
+ "ROUND(SUM(CASE "
+ "WHEN payplanlink.AmountOverride!=0 THEN payplanlink.AmountOverride " //If override isn't zero, use it in sum
+ "ELSE (CASE " //Otherwise, use adjustment amount or total proc fee for linked production
+ "WHEN payplanlink.LinkType=" + POut.Int((int)PayPlanLinkType.Adjustment) + " THEN adjustment.AdjAmt "
+ "WHEN payplanlink.LinkType=" + POut.Int((int)PayPlanLinkType.Procedure) + " "
+ "THEN procedurelog.ProcFee*GREATEST(1,procedurelog.BaseUnits+procedurelog.UnitQty) "
+ "ELSE 0 END)"
//Factor in non-payplan pay splits, adjustments to procedures, and insurance estimates, payments, writeoffs, and estimated writeoffs
+ "-COALESCE(sumsplit.SumSplit,0)+COALESCE(sumprocadj.SumProcAdj,0)-COALESCE(sumins.SumIns,0) "
+ "END),2) AS 'TotalPrincipal' "
+ "FROM payplanlink "
+ "LEFT JOIN adjustment ON adjustment.AdjNum=payplanlink.FKey AND payplanlink.LinkType=" + POut.Int((int)PayPlanLinkType.Adjustment) + " "
+ "LEFT JOIN procedurelog ON procedurelog.ProcNum=payplanlink.FKey AND payplanlink.LinkType=" + POut.Int((int)PayPlanLinkType.Procedure) + " "
+ "LEFT JOIN " //Table to sum all paysplits made to linked production outside of pay plan.
+ "(SELECT paysplit.ProcNum,paysplit.AdjNum,SUM(paysplit.SplitAmt) AS 'SumSplit' "
+ "FROM paysplit WHERE paysplit.PayPlanNum=0 AND paysplit.PayPlanChargeNum=0 GROUP BY paysplit.ProcNum,paysplit.AdjNum) "
+ "AS sumsplit ON (payplanlink.FKey=sumsplit.ProcNum AND sumsplit.ProcNum!=0 AND payplanlink.LinkType=" + POut.Int((int)PayPlanLinkType.Procedure) + ") OR "
+ "(payplanlink.FKey=sumsplit.AdjNum AND sumsplit.AdjNum!=0 AND payplanlink.LinkType=" + POut.Int((int)PayPlanLinkType.Adjustment) + ") "
+ "LEFT JOIN " //Table to sum all adjustments made to linked procedures.
+ "(SELECT adjustment.ProcNum, SUM(adjustment.AdjAmt) AS 'SumProcAdj' FROM adjustment GROUP BY adjustment.ProcNum) "
+ "AS sumprocadj ON sumprocadj.ProcNum=procedurelog.ProcNum AND payplanlink.LinkType=" + POut.Int((int)PayPlanLinkType.Procedure) + " "
+ "LEFT JOIN " //Table to sum all ins estimates, ins payments, estimated writeoffs, and writeoffs to linked procedures.
+ "(SELECT claimproc.ProcNum, SUM(CASE "
+ "WHEN claimproc.Status IN (" + string.Join(",", ClaimProcs.GetInsPaidStatuses().Select(x => POut.Int((int)x))) + ") "
+ "THEN claimproc.InsPayAmt+claimproc.WriteOff "
+ "WHEN claimproc.Status IN (" + string.Join(",", ClaimProcs.GetEstimatedStatuses().Select(x => POut.Int((int)x))) + ") "
+ "THEN claimproc.InsPayEst+(CASE "
+ "WHEN claimproc.WriteOffEstOverride!=-1 THEN claimproc.WriteOffEstOverride "
+ "WHEN claimproc.WriteOffEst!=-1 THEN claimproc.WriteOffEst "
+ "ELSE 0 END) "
+ "ELSE 0 END) AS 'SumIns' "
+ "FROM claimproc GROUP BY claimproc.ProcNum) AS sumins "
+ "ON procedurelog.ProcNum=sumins.ProcNum AND payplanlink.LinkType=" + POut.Int((int)PayPlanLinkType.Procedure) + " "
//Grouped by PayPlanNum so that we can sum total principal for each dynamic pay plan.
+ "GROUP BY PayPlanLink.PayPlanNum) AS dppprincipal ON dppprincipal.PayPlanNum=PayPlan.PayPlanNum AND PayPlan.IsDynamic=1 "
+ "WHERE TRUE "; //Always include true, so that the WHERE clause may always be present.
if (hasDateRange)
{
command += "AND payplan.PayPlanDate >= " + POut.Date(dateStart) + " "
+ "AND payplan.PayPlanDate <= " + POut.Date(dateEnd) + " ";
}
command += whereProv
+ whereClin;
if (displayPayPlanType == DisplayPayPlanType.Insurance)
{
command += "AND payplan.PlanNum!=0 ";
}
else if (displayPayPlanType == DisplayPayPlanType.Patient)
{
command += "AND payplan.PlanNum=0 ";
}
else if (displayPayPlanType == DisplayPayPlanType.Both)
{
//Do not filter the query at all which will show both insurance and patient payment plan types.
}
if (hideCompletedPlans)
{
command += "AND payplan.IsClosed=0 ";
}
command += "GROUP BY FName,LName,MiddleI,Preferred,payplan.PayPlanNum ";
if (hasClinicsEnabled)
{
command += "ORDER BY ClinicNum,LName,FName";
}
else
{
command += "ORDER BY LName,FName";
}
DataTable raw = ReportsComplex.RunFuncOnReportServer(() => ReportsComplex.GetTable(command));
List <Provider> listProvs = ReportsComplex.RunFuncOnReportServer(() => Providers.GetAll());
//DateTime payplanDate;
Patient pat;
double princ;
double paid;
double interest;
double accumDue;
double notDue;
decimal famBal = 0;
double princTot = 0;
double paidTot = 0;
double interestTot = 0;
double balanceTot = 0;
double accumDueTot = 0;
double notDueTot = 0;
decimal famBalTot = 0;
string clinicAbbrOld = "";
for (int i = 0; i < raw.Rows.Count; i++)
{
princ = PIn.Double(raw.Rows[i]["_principal"].ToString());
interest = PIn.Double(raw.Rows[i]["_accumInt"].ToString());
if (raw.Rows[i]["PlanNum"].ToString() == "0") //pat payplan
{
paid = PIn.Double(raw.Rows[i]["_paid"].ToString());
}
else //ins payplan
{
paid = PIn.Double(raw.Rows[i]["_insPaid"].ToString());
}
accumDue = PIn.Double(raw.Rows[i]["_accumDue"].ToString());
notDue = PIn.Double(raw.Rows[i]["_notDue"].ToString());
row = table.NewRow();
//payplanDate=PIn.PDate(raw.Rows[i]["PayPlanDate"].ToString());
//row["date"]=raw.Rows[i]["PayPlanDate"].ToString();//payplanDate.ToShortDateString();
pat = new Patient();
pat.LName = raw.Rows[i]["LName"].ToString();
pat.FName = raw.Rows[i]["FName"].ToString();
pat.MiddleI = raw.Rows[i]["MiddleI"].ToString();
pat.Preferred = raw.Rows[i]["Preferred"].ToString();
row["provider"] = Providers.GetLName(PIn.Long(raw.Rows[i]["ProvNum"].ToString()), listProvs);
row["guarantor"] = pat.GetNameLF();
if (raw.Rows[i]["PlanNum"].ToString() == "0")
{
row["ins"] = "";
}
else
{
row["ins"] = "X";
}
row["princ"] = princ.ToString("f");
row["accumInt"] = interest.ToString("f");
row["paid"] = paid.ToString("f");
row["balance"] = (princ + interest - paid).ToString("f");
row["due"] = (accumDue - paid).ToString("f");
if (isPayPlanV2)
{
row["notDue"] = ((princ + interest - paid) - (accumDue - paid)).ToString("f");
}
if (showFamilyBalance)
{
Family famCur = ReportsComplex.RunFuncOnReportServer(() => Patients.GetFamily(PIn.Long(raw.Rows[i]["PatNum"].ToString())));
famBal = (decimal)famCur.ListPats[0].BalTotal;
row["famBal"] = (famBal - (decimal)famCur.ListPats[0].InsEst).ToString("F");
}
if (hasClinicsEnabled) //Using clinics
{
List <Clinic> listClinics = ReportsComplex.RunFuncOnReportServer(() => Clinics.GetClinicsNoCache());
string clinicAbbr = Clinics.GetAbbr(PIn.Long(raw.Rows[i]["ClinicNum"].ToString()), listClinics);
clinicAbbr = (clinicAbbr == "")?Lans.g("FormRpPayPlans", "Unassigned"):clinicAbbr;
if (!String.IsNullOrEmpty(clinicAbbrOld) && clinicAbbr != clinicAbbrOld) //Reset all the total values
{
DataRow rowTot = tableTotals.NewRow();
rowTot["clinicName"] = clinicAbbrOld;
rowTot["princ"] = princTot.ToString();
rowTot["accumInt"] = interestTot.ToString();
rowTot["paid"] = paidTot.ToString();
rowTot["balance"] = balanceTot.ToString();
rowTot["due"] = accumDueTot.ToString();
if (isPayPlanV2)
{
rowTot["notDue"] = notDueTot.ToString();
}
rowTot["famBal"] = famBalTot.ToString();
tableTotals.Rows.Add(rowTot);
princTot = 0;
paidTot = 0;
interestTot = 0;
accumDueTot = 0;
balanceTot = 0;
notDueTot = 0;
famBalTot = 0;
}
row["clinicName"] = clinicAbbr;
clinicAbbrOld = clinicAbbr;
princTot += princ;
paidTot += paid;
interestTot += interest;
accumDueTot += (accumDue - paid);
balanceTot += (princ + interest - paid);
notDueTot += ((princ + interest - paid) - (accumDue - paid));
famBalTot += famBal;
if (i == raw.Rows.Count - 1)
{
DataRow rowTot = tableTotals.NewRow();
rowTot["clinicName"] = clinicAbbrOld;
rowTot["princ"] = princTot.ToString();
rowTot["accumInt"] = interestTot.ToString();
rowTot["paid"] = paidTot.ToString();
rowTot["balance"] = balanceTot.ToString();
rowTot["due"] = accumDueTot.ToString();
if (isPayPlanV2)
{
rowTot["notDue"] = notDueTot.ToString();
}
rowTot["famBal"] = famBalTot.ToString();
tableTotals.Rows.Add(rowTot);
}
}
table.Rows.Add(row);
}
ds.Tables.Add(table);
ds.Tables.Add(tableTotals);
return(ds);
}
/////<summary>Removes gaps and overlaps in item orders with a single call to the DB. Example: 1,2,5,5,13 becomes 0,1,2,3,4; the overlaps are sorted arbitrarily.</summary>
// public static void NormalizeItemOrders() {
// if(RemotingClient.RemotingRole==RemotingRole.ClientWeb) {
// Meth.GetVoid(MethodBase.GetCurrentMethod());
// return;
// }
// string command="SET @newOrder=0";
// Db.NonQ(command);
// command=@"SELECT @newOrder:=@newOrder+1 AS newOrder, t.supplynum, t.*
// FROM
// (SELECT supply.*
// FROM supply
// LEFT JOIN definition ON supply.category=definition.defnum
// ORDER BY definition.itemorder,supply.itemorder) t";
// Db.NonQ(command);
// }
///<summary>Surround with try-catch.</summary>
public static void DeleteObject(Supply supp)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), supp);
return;
}
//validate that not already in use.
string command = "SELECT COUNT(*) FROM supplyorderitem WHERE SupplyNum=" + POut.Long(supp.SupplyNum);
int count = PIn.Int(Db.GetCount(command));
if (count > 0)
{
throw new ApplicationException(Lans.g("Supplies", "Supply is already in use on an order. Not allowed to delete."));
}
Crud.SupplyCrud.Delete(supp.SupplyNum);
command = "UPDATE supply SET ItemOrder=(ItemOrder-1) WHERE Category=" + POut.Long(supp.Category) + " AND ItemOrder>=" + POut.Int(supp.ItemOrder);
Db.NonQ(command);
}
public static string GetPageContent(long wikiPageNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetString(MethodBase.GetCurrentMethod(), wikiPageNum));
}
if (wikiPageNum < 1)
{
return("");
}
string command = "SELECT PageContent FROM wikipagehist WHERE WikiPageNum=" + POut.Long(wikiPageNum);
return(Db.GetScalar(command));
}
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);
}
public static DataTable GetNetUnearnedData(List <long> listClinicNums, List <long> listProvNums,
List <long> listUnearnedTypeNums, bool isExcludeNetZero)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), listClinicNums, listProvNums, listUnearnedTypeNums, isExcludeNetZero));
}
List <long> listHiddenUnearnedDefNums = ReportsComplex.RunFuncOnReportServer(() =>
Defs.GetDefsNoCache(DefCat.PaySplitUnearnedType).FindAll(x => !string.IsNullOrEmpty(x.ItemValue)).Select(x => x.DefNum).ToList()
);
//show all families where unearned income was collected and no unallocated procedures exist.
/*All families with unallocated paysplits*/
DataTable retVal = new DataTable();
retVal.Columns.Add("PatientName");
retVal.Columns.Add("GuarantorName");
retVal.Columns.Add("PatUnearnedAmt");
retVal.Columns.Add("FamBal");
string command = @"
SELECT patient.Guarantor, paysplit.PatNum, patient.FName, patient.LName,
guar.FName GuarF, guar.LName GuarL,SUM(paysplit.SplitAmt) UnallocatedAmt
FROM paysplit
INNER JOIN patient ON patient.PatNum = paysplit.PatNum " ;
if (listClinicNums.Count > 0)
{
command += @"
AND patient.ClinicNum IN (" + string.Join(",", listClinicNums.Select(x => POut.Long(x))) + ") ";
}
if (listProvNums.Count > 0)
{
command += @"
AND patient.PriProv IN (" + string.Join(",", listProvNums.Select(x => POut.Long(x))) + ") ";
}
command += @"
INNER JOIN patient guar ON guar.PatNum = patient.Guarantor
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 paysplit.PatNum ";
if (isExcludeNetZero)
{
command += "HAVING ABS(UnallocatedAmt) > 0.005 ";
}
DataTable tableUnallocatedPrepayments = ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(command));
//get remaining amount for all procedures of the returned families.
List <long> listGuarantorNums = tableUnallocatedPrepayments.Rows.OfType <DataRow>().Select(x => PIn.Long(x["Guarantor"].ToString())).ToList();
if (listGuarantorNums.Count == 0)
{
return(retVal); //No families have paysplits with unallocated prepayments. Return empty table.
}
/*As long as any patient in the family has AT LEAST ONE procedure that is not fully, explicitly paid off, they will not show in this report.*/
List <UnearnedProc> listGuarantorRemainingProcs = ReportsComplex.RunFuncOnReportServer(() =>
Procedures.GetRemainingProcsForFamilies(listGuarantorNums));
Dictionary <long, double> dictFamilyBalances = ReportsComplex.RunFuncOnReportServer(() => Ledgers.GetBalancesForFamilies(listGuarantorNums));
foreach (DataRow rowCur in tableUnallocatedPrepayments.Rows)
{
long unallocatedGuarantor = PIn.Long(rowCur["Guarantor"].ToString());
if (listGuarantorRemainingProcs.Select(x => x.GuarNum).Contains(unallocatedGuarantor))
{
continue; //Has at least one procedure that is not fully paid off.
}
double famBal;
if (!dictFamilyBalances.TryGetValue(unallocatedGuarantor, out famBal))
{
famBal = 0;
}
DataRow newRow = retVal.NewRow();
newRow["PatientName"] = rowCur["LName"].ToString() + ", " + rowCur["FName"].ToString();
newRow["GuarantorName"] = rowCur["GuarL"].ToString() + ", " + rowCur["GuarF"].ToString();
newRow["PatUnearnedAmt"] = PIn.Double(rowCur["UnallocatedAmt"].ToString());
newRow["FamBal"] = famBal.ToString("f");
retVal.Rows.Add(newRow);
}
return(retVal);
}
///<summary>Gets a list of all EvaluationCriterion attached to an EvaluationDef. Ordered by ItemOrder.</summary>
public static List <EvaluationCriterionDef> GetAllForEvaluationDef(long evaluationDefNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <EvaluationCriterionDef> >(MethodBase.GetCurrentMethod(), evaluationDefNum));
}
string command = "SELECT * FROM evaluationcriteriondef WHERE EvaluationDefNum = " + POut.Long(evaluationDefNum) + " "
+ "ORDER BY ItemOrder";
return(Crud.EvaluationCriterionDefCrud.SelectMany(command));
}
///<summary></summary>
public static void Delete(long evaluationCriterionDefNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), evaluationCriterionDefNum);
return;
}
string command = "DELETE FROM evaluationcriteriondef WHERE EvaluationCriterionDefNum = " + POut.Long(evaluationCriterionDefNum);
Db.NonQ(command);
}
///<summary>Used when viewing securityLog from the security admin window. PermTypes can be length 0 to get all types.
///Throws exceptions.</summary>
public static SecurityLog[] Refresh(DateTime dateFrom, DateTime dateTo, Permissions permType, long patNum, long userNum,
DateTime datePreviousFrom, DateTime datePreviousTo, int limit = 0)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <SecurityLog[]>(MethodBase.GetCurrentMethod(), dateFrom, dateTo, permType, patNum, userNum, datePreviousFrom, datePreviousTo, limit));
}
string command = "SELECT securitylog.*,LName,FName,Preferred,MiddleI,LogHash FROM securitylog "
+ "LEFT JOIN patient ON patient.PatNum=securitylog.PatNum "
+ "LEFT JOIN securityloghash ON securityloghash.SecurityLogNum=securitylog.SecurityLogNum "
+ "WHERE LogDateTime >= " + POut.Date(dateFrom) + " "
+ "AND LogDateTime <= " + POut.Date(dateTo.AddDays(1)) + " "
+ "AND DateTPrevious >= " + POut.Date(datePreviousFrom) + " "
+ "AND DateTPrevious <= " + POut.Date(datePreviousTo.AddDays(1));
if (patNum != 0)
{
command += " AND securitylog.PatNum IN (" + string.Join(",",
PatientLinks.GetPatNumsLinkedToRecursive(patNum, PatientLinkType.Merge).Select(x => POut.Long(x))) + ")";
}
if (permType != Permissions.None)
{
command += " AND PermType=" + POut.Long((int)permType);
}
if (userNum != 0)
{
command += " AND UserNum=" + POut.Long(userNum);
}
command += " ORDER BY LogDateTime DESC"; //Using DESC so that the most recent ones appear in the list
if (limit > 0)
{
command = DbHelper.LimitOrderBy(command, limit);
}
DataTable table = Db.GetTable(command);
List <SecurityLog> listLogs = Crud.SecurityLogCrud.TableToList(table);
for (int i = 0; i < listLogs.Count; i++)
{
if (table.Rows[i]["PatNum"].ToString() == "0")
{
listLogs[i].PatientName = "";
}
else
{
listLogs[i].PatientName = table.Rows[i]["PatNum"].ToString() + "-"
+ Patients.GetNameLF(table.Rows[i]["LName"].ToString()
, table.Rows[i]["FName"].ToString()
, table.Rows[i]["Preferred"].ToString()
, table.Rows[i]["MiddleI"].ToString());
}
listLogs[i].LogHash = table.Rows[i]["LogHash"].ToString();
}
return(listLogs.OrderBy(x => x.LogDateTime).ToArray());
}
///<summary></summary>
public static JobActiveLink GetForJobAndUser(long jobNum, long userNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <JobActiveLink>(MethodBase.GetCurrentMethod(), jobNum, userNum));
}
string command = "SELECT * FROM jobactivelink WHERE JobNum = " + POut.Long(jobNum) + " AND UserNum = " + POut.Long(userNum);
return(Crud.JobActiveLinkCrud.SelectOne(command));
}
///<summary>Used when viewing various audit trails of specific types. This overload will return security logs for multiple objects (or fKeys).
///Typically you will only need a specific type audit log for one type.
///However, for things like ortho charts, each row (FK) in the database represents just one part of a larger ortho chart "object".
///Thus, to get the full experience of a specific type audit trail window, we need to get security logs for multiple objects (FKs) that
///comprise the larger object (what the user sees). Only implemented with ortho chart so far. FKeys can be null.
///Throws exceptions.</summary>
public static SecurityLog[] Refresh(long patNum, List <Permissions> permTypes, List <long> fKeys)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <SecurityLog[]>(MethodBase.GetCurrentMethod(), patNum, permTypes, fKeys));
}
string types = "";
for (int i = 0; i < permTypes.Count; i++)
{
if (i > 0)
{
types += " OR";
}
types += " PermType=" + POut.Long((int)permTypes[i]);
}
string command = "SELECT * FROM securitylog "
+ "WHERE (" + types + ") ";
if (fKeys != null && fKeys.Count > 0)
{
command += "AND FKey IN (" + String.Join(",", fKeys) + ") ";
}
if (patNum != 0) //appointments
{
command += " AND PatNum IN (" + string.Join(",",
PatientLinks.GetPatNumsLinkedToRecursive(patNum, PatientLinkType.Merge).Select(x => POut.Long(x))) + ")";
}
command += "ORDER BY LogDateTime";
List <SecurityLog> listLogs = Crud.SecurityLogCrud.SelectMany(command);
return(listLogs.OrderBy(x => x.LogDateTime).ToArray());
}
///<summary>Called from FormInsPlan when user wants to view a benefit note for other subscribers on a plan. Should never include the current subscriber that the user is editing. This function will get one note from the database, not including blank notes. If no note can be found, then it returns empty string.</summary>
public static string GetBenefitNotes(long planNum, long subNumExclude)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetString(MethodBase.GetCurrentMethod(), planNum, subNumExclude));
}
string command = "SELECT BenefitNotes FROM inssub WHERE BenefitNotes != '' AND PlanNum=" + POut.Long(planNum) + " AND InsSubNum !=" + POut.Long(subNumExclude) + " " + DbHelper.LimitAnd(1);
DataTable table = Db.GetTable(command);
if (table.Rows.Count == 0)
{
return("");
}
return(PIn.String(table.Rows[0][0].ToString()));
}
///<summary>Gets all WebSchedRecalls for which a reminder has not been sent. To get for all clinics, don't include the listClinicNums or pass
///in an empty list.</summary>
public static List <WebSchedRecall> GetAllUnsent(List <long> listClinicNums = null)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <WebSchedRecall> >(MethodBase.GetCurrentMethod(), listClinicNums));
}
//We don't want to include rows that have a status of SendFailed or SendSuccess
string command = "SELECT * FROM webschedrecall WHERE DateTimeReminderSent < '1880-01-01' "
+ "AND EmailSendStatus IN(" + POut.Int((int)AutoCommStatus.DoNotSend) + ", " + POut.Int((int)AutoCommStatus.SendNotAttempted) + ") "
+ "AND SmsSendStatus IN(" + POut.Int((int)AutoCommStatus.DoNotSend) + ", " + POut.Int((int)AutoCommStatus.SendNotAttempted) + ") "
+ "AND (EmailSendStatus=" + POut.Int((int)AutoCommStatus.SendNotAttempted) + " "
+ "OR SmsSendStatus=" + POut.Int((int)AutoCommStatus.SendNotAttempted) + ") ";
if (listClinicNums != null && listClinicNums.Count > 0)
{
command += "AND ClinicNum IN(" + string.Join(",", listClinicNums.Select(x => POut.Long(x))) + ") ";
}
return(Crud.WebSchedRecallCrud.SelectMany(command));
}
///<summary>Returns all tsitranslogs for the patients in listPatNums. Returns empty list if listPatNums is empty or null.</summary>
public static List <TsiTransLog> SelectMany(List <long> listPatNums)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <TsiTransLog> >(MethodBase.GetCurrentMethod(), listPatNums));
}
if (listPatNums == null || listPatNums.Count < 1)
{
return(new List <TsiTransLog>());
}
string command = "SELECT * FROM tsitranslog "
+ "WHERE PatNum IN (" + string.Join(",", listPatNums.Select(x => POut.Long(x))) + ")";
return(Crud.TsiTransLogCrud.SelectMany(command));
}
///<summary>Changes the value of the DateTStamp column to the current time stamp for all statements of a patient</summary>
public static void ResetTimeStamps(long patNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), patNum);
return;
}
string command = "UPDATE statement SET DateTStamp = CURRENT_TIMESTAMP WHERE PatNum =" + POut.Long(patNum);
Db.NonQ(command);
}
///<summary>Gets all of the payplanlink entries for the given fKey and linkType.</summary>
public static List <PayPlanLink> GetForFKeysAndLinkType(List <long> listFKeys, PayPlanLinkType linkType)
{
if (listFKeys.IsNullOrEmpty())
{
return(new List <PayPlanLink>());
}
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <PayPlanLink> >(MethodBase.GetCurrentMethod(), listFKeys, linkType));
}
string command = $"SELECT * FROM payplanlink WHERE payplanlink.FKey IN ({string.Join(",",listFKeys.Select(x => POut.Long(x)))}) " +
$"AND payplanlink.LinkType={POut.Int((int)linkType)} ";
return(Crud.PayPlanLinkCrud.SelectMany(command));
}
///<summary>Gets a list of InsSubs where the subscribers are in the passed-in list of patnums.</summary>
public static List <InsSub> GetListInsSubs(List <long> listPatNums)
{
if (listPatNums.Count == 0)
{
return(new List <InsSub>());
}
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <InsSub> >(MethodBase.GetCurrentMethod(), listPatNums));
}
string command = "SELECT * FROM InsSub WHERE InsSub.Subscriber IN (" + string.Join(",", listPatNums.Select(x => POut.Long(x))) + ")";
return(Crud.InsSubCrud.SelectMany(command));
}
/// <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>Gets all of the families and their corresponding InsSubs for all families passed in (saves calling RefreshForFam() one by one).
///Returns a dictionary of key: family and all of their corresponding value: InsSubs</summary>
public static SerializableDictionary <Family, List <InsSub> > GetDictInsSubsForFams(List <Family> listFamilies)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetSerializableDictionary <Family, List <InsSub> >(MethodBase.GetCurrentMethod(), listFamilies));
}
SerializableDictionary <Family, List <InsSub> > dictFamilyInsSubs = new SerializableDictionary <Family, List <InsSub> >();
if (listFamilies == null || listFamilies.Count < 1)
{
return(dictFamilyInsSubs);
}
List <long> listPatNums = listFamilies.SelectMany(x => x.ListPats).Select(x => x.PatNum).ToList();
if (listPatNums == null || listPatNums.Count < 1)
{
return(dictFamilyInsSubs);
}
//The command is written ina nested fashion in order to be compatible with both MySQL and Oracle.
string command = "SELECT D.*,C.OnBehalfOf "
+ "FROM inssub D,((SELECT A.InsSubNum,A.Subscriber AS OnBehalfOf "
+ "FROM inssub A "
+ "WHERE A.Subscriber IN(" + string.Join(",", listPatNums.Select(x => POut.Long(x))) + ")"
//in union, distinct is implied
+ ") UNION (SELECT B.InsSubNum,P.PatNum AS OnBehalfOf "
+ "FROM inssub B,patplan P "
+ "WHERE B.InsSubNum=P.InsSubNum AND P.PatNum IN(" + string.Join(",", listPatNums.Select(x => POut.Long(x))) + "))"
+ ") C "
+ "WHERE D.InsSubNum=C.InsSubNum "
+ "ORDER BY " + DbHelper.UnionOrderBy("DateEffective", 4);
DataTable table = Db.GetTable(command);
foreach (Family family in listFamilies)
{
List <long> listOnBehalfOfs = family.ListPats.Select(x => x.PatNum).ToList();
List <InsSub> listInsSubs = new List <InsSub>();
List <DataRow> listDataRows = table.Select().Where(x => listOnBehalfOfs.Exists(y => y == PIn.Long(x["OnBehalfOf"].ToString()))).ToList();
DataTable tableFamilyInsSubs = table.Clone();
foreach (DataRow row in listDataRows)
{
tableFamilyInsSubs.ImportRow(row);
}
dictFamilyInsSubs[family] = Crud.InsSubCrud.TableToList(tableFamilyInsSubs);
}
return(dictFamilyInsSubs);
}
///<summary>If not using clinics, or for all clinics with clinics enabled, supply an empty list of clinicNums. If the user is restricted, for all
///clinics supply only those clinics for which the user has permission to access, otherwise it will be run for all clinics.</summary>
public static DataTable GetPatTable(DateTime dateFrom, DateTime dateTo, List <long> listProvNums, List <long> listClinicNums, List <long> listPatientTypes,
bool hasAllProvs, bool hasAllClinics, bool hasPatientTypes, bool isGroupedByPatient, bool isUnearnedIncluded, bool doShowProvSeparate,
bool doShowHiddenTPUnearned)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateFrom, dateTo, listProvNums, listClinicNums, listPatientTypes, hasAllProvs, hasAllClinics,
hasPatientTypes, isGroupedByPatient, isUnearnedIncluded, doShowProvSeparate, doShowHiddenTPUnearned));
}
//reports should no longer use the cache
bool hasClinicsEnabled = ReportsComplex.RunFuncOnReportServer(() => Prefs.HasClinicsEnabledNoCache);
List <long> listHiddenUnearnedDefNums = new List <long>();
if (!doShowHiddenTPUnearned)
{
listHiddenUnearnedDefNums = ReportsComplex.RunFuncOnReportServer(() =>
Defs.GetDefsNoCache(DefCat.PaySplitUnearnedType).FindAll(x => !string.IsNullOrEmpty(x.ItemValue)).Select(x => x.DefNum).ToList()
);
}
//patient payments-----------------------------------------------------------------------------------------
//the selected columns have to remain in this order due to the way the report complex populates the returned sheet
string queryPat = "SELECT payment.PayDate DatePay,"
+ "MAX(" + DbHelper.Concat("patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI") + ") lfname,GROUP_CONCAT(DISTINCT provider.Abbr),";
if (hasClinicsEnabled)
{
queryPat += "clinic.Abbr clinicAbbr,";
}
queryPat += "payment.CheckNum,SUM(COALESCE(paysplit.SplitAmt,0)) amt,payment.PayNum,ItemName,payment.PayType "
+ "FROM payment "
+ "LEFT JOIN paysplit ON payment.PayNum=paysplit.PayNum "
+ "LEFT JOIN patient ON payment.PatNum=patient.PatNum "
+ "LEFT JOIN provider ON paysplit.ProvNum=provider.ProvNum "
+ "LEFT JOIN definition ON payment.PayType=definition.DefNum ";
if (hasClinicsEnabled)
{
queryPat += "LEFT JOIN clinic ON clinic.ClinicNum=paysplit.ClinicNum ";
}
queryPat += "WHERE payment.PayDate BETWEEN " + POut.Date(dateFrom) + " AND " + POut.Date(dateTo) + " ";
if (hasClinicsEnabled && listClinicNums.Count > 0)
{
queryPat += "AND paysplit.ClinicNum IN(" + string.Join(",", listClinicNums.Select(x => POut.Long(x))) + ") ";
}
if (!hasAllProvs && listProvNums.Count > 0)
{
queryPat += "AND paysplit.ProvNum IN(" + string.Join(",", listProvNums.Select(x => POut.Long(x))) + ") ";
}
if (!hasPatientTypes && listPatientTypes.Count > 0)
{
queryPat += "AND payment.PayType IN (" + string.Join(",", listPatientTypes.Select(x => POut.Long(x))) + ") ";
}
if (!isUnearnedIncluded) //UnearnedType of 0 means the paysplit is NOT unearned
{
queryPat += "AND paysplit.UnearnedType=0 ";
}
else if (listHiddenUnearnedDefNums.Count > 0 && !doShowHiddenTPUnearned) //Include unearned but not of the TP type.
{
queryPat += $"AND paysplit.UnearnedType NOT IN ({string.Join(",",listHiddenUnearnedDefNums)}) ";
}
queryPat += "GROUP BY payment.PayNum,payment.PayDate,payment.CheckNum,definition.ItemName,payment.PayType ";
if (doShowProvSeparate)
{
queryPat += ",provider.ProvNum ";
}
if (hasClinicsEnabled)
{
queryPat += ",clinic.Abbr ";
}
if (isGroupedByPatient)
{
queryPat += ",patient.PatNum ";
}
queryPat += "ORDER BY payment.PayType,payment.PayDate,lfname";
if (!hasPatientTypes && listPatientTypes.Count == 0)
{
queryPat = DbHelper.LimitOrderBy(queryPat, 0);
}
return(ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(queryPat)));
}
///<summary>Returns the number of subscribers moved.
///No need to pass in userNum, it's set before remoting role check and passed to the server if necessary.</summary>
public static long MoveSubscribers(long insPlanNumFrom, long insPlanNumTo, long userNum = 0)
{
if (RemotingClient.RemotingRole != RemotingRole.ServerWeb)
{
userNum = Security.CurUser.UserNum; //must be before normal remoting role check to get user at workstation
}
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetLong(MethodBase.GetCurrentMethod(), insPlanNumFrom, insPlanNumTo, userNum));
}
List <InsSub> listInsSubsFrom = GetListForPlanNum(insPlanNumFrom);
List <long> listBlockedPatNums = new List <long>();
//Perform the same validation as when the user manually drops insplans from FormInsPlan using the Drop button.
for (int i = 0; i < listInsSubsFrom.Count; i++)
{
InsSub insSubFrom = listInsSubsFrom[i];
List <PatPlan> listPatPlanFrom = PatPlans.Refresh(insSubFrom.Subscriber);
for (int j = 0; j < listPatPlanFrom.Count; j++)
{
PatPlan patPlanFrom = listPatPlanFrom[j];
//The following comments and logic are copied from the FormInsPlan Drop button...
//If they have a claim for this ins with today's date, don't let them drop.
//We already have code in place to delete claimprocs when we drop ins, but the claimprocs attached to claims are protected.
//The claim clearly needs to be deleted if they are dropping. We need the user to delete the claim before they drop the plan.
//We also have code in place to add new claimprocs when they add the correct insurance.
List <Claim> listClaims = Claims.Refresh(patPlanFrom.PatNum); //Get all claims for patient.
for (int k = 0; k < listClaims.Count; k++)
{
if (listClaims[k].PlanNum != insPlanNumFrom) //Make sure the claim is for the insurance plan we are about to change, not any other plans the patient might have.
{
continue;
}
if (listClaims[k].DateService != DateTime.Today) //not today
{
continue;
}
//Patient currently has a claim for the insplan they are trying to drop.
if (!listBlockedPatNums.Contains(patPlanFrom.PatNum))
{
listBlockedPatNums.Add(patPlanFrom.PatNum);
}
}
}
}
if (listBlockedPatNums.Count > 0)
{
StringBuilder sb = new StringBuilder();
for (int i = 0; i < listBlockedPatNums.Count; i++)
{
sb.Append("\r\n");
Patient pat = Patients.GetPat(listBlockedPatNums[i]);
sb.Append("#" + listBlockedPatNums[i] + " " + pat.GetNameFLFormal());
}
throw new ApplicationException(Lans.g("InsSubs", "Before changing the subscribers on the insurance plan being moved from, please delete all of today's claims related to the insurance plan being moved from for the following patients") + ":" + sb.ToString());
}
//This loop mimics some of the logic in PatPlans.Delete().
int insSubMovedCount = 0;
for (int i = 0; i < listInsSubsFrom.Count; i++)
{
InsSub inssub = listInsSubsFrom[i];
long oldInsSubNum = inssub.InsSubNum;
inssub.InsSubNum = 0; //This will allow us to insert a new record.
inssub.PlanNum = insPlanNumTo;
inssub.DateEffective = DateTime.MinValue;
inssub.BenefitNotes = "";
inssub.SubscNote = "";
inssub.SecUserNumEntry = userNum;
long insSubNumNew = InsSubs.Insert(inssub);
string command = "SELECT PatNum FROM patplan WHERE InsSubNum=" + POut.Long(oldInsSubNum);
DataTable tablePatsForInsSub = Db.GetTable(command);
if (tablePatsForInsSub.Rows.Count == 0)
{
continue;
}
insSubMovedCount++;
for (int j = 0; j < tablePatsForInsSub.Rows.Count; j++)
{
long patNum = PIn.Long(tablePatsForInsSub.Rows[j]["PatNum"].ToString());
List <PatPlan> listPatPlans = PatPlans.Refresh(patNum);
for (int k = 0; k < listPatPlans.Count; k++)
{
PatPlan patPlan = listPatPlans[k];
if (patPlan.InsSubNum == oldInsSubNum)
{
command = "DELETE FROM benefit WHERE PatPlanNum=" + POut.Long(patPlan.PatPlanNum); //Delete patient specific benefits (rare).
Db.NonQ(command);
patPlan.InsSubNum = insSubNumNew;
PatPlans.Update(patPlan);
}
}
//Now that the plan has changed for the current subscriber, recalculate estimates.
bool prefChanged = false;
//Forcefully set pref false to prevent creating new estimates for all procs (including completed, sent procs)
if (Prefs.UpdateBool(PrefName.ClaimProcsAllowedToBackdate, false))
{
prefChanged = true; //We will turn the preference back on for the user after we finish our computations.
}
Family fam = Patients.GetFamily(patNum);
Patient pat = fam.GetPatient(patNum);
List <ClaimProc> listClaimProcs = ClaimProcs.Refresh(patNum);
List <Procedure> listProcs = Procedures.Refresh(patNum);
listPatPlans = PatPlans.Refresh(patNum);
List <InsSub> listInsSubs = InsSubs.RefreshForFam(fam);
List <InsPlan> listInsPlans = InsPlans.RefreshForSubList(listInsSubs);
List <Benefit> listBenefits = Benefits.Refresh(listPatPlans, listInsSubs);
Procedures.ComputeEstimatesForAll(patNum, listClaimProcs, listProcs, listInsPlans, listPatPlans, listBenefits, pat.Age, listInsSubs);
if (prefChanged)
{
Prefs.UpdateBool(PrefName.ClaimProcsAllowedToBackdate, true); //set back to original value if changed.
}
}
}
InsPlan insPlanFrom = InsPlans.RefreshOne(insPlanNumFrom);
InsPlan planOld = insPlanFrom.Copy();
insPlanFrom.IsHidden = true;
InsPlans.Update(insPlanFrom, planOld);
return(insSubMovedCount);
}
