///<summary>Removes the assigned user from the InsVerify of the InsPlan that is associated to the PatPlan passed in.
///Will only unassign if the user assigned to the patplan matches the user assigned to the insplan.</summary>
private static void RemoveAssignedUser(PatPlan patPlanCur)
{
//No Remoting check; no call to db.
//Get the insurance verified assigned to the PatPlan.
InsVerify insVerifyForPatPlan = InsVerifies.GetOneByFKey(patPlanCur.PatPlanNum, VerifyTypes.PatientEnrollment);
if (insVerifyForPatPlan != null && insVerifyForPatPlan.UserNum > 0)
{
//Get the insplan associated to the PatPlan.
InsSub inssub = null;
if (patPlanCur != null)
{
inssub = InsSubs.GetOne(patPlanCur.InsSubNum);
}
InsPlan insPlan = null;
if (inssub != null)
{
insPlan = InsPlans.RefreshOne(inssub.PlanNum);
}
if (insPlan != null)
{
//Get the insVerify for the insplan associated to the patplan we are about to delete.
InsVerify insVerifyForInsPlan = InsVerifies.GetOneByFKey(insPlan.PlanNum, VerifyTypes.InsuranceBenefit);
//Only unassign the user for the insplan if it matches the user for the patplan being dropped
if (insVerifyForInsPlan != null && insVerifyForInsPlan.UserNum == insVerifyForPatPlan.UserNum)
{
//Remove user and set DateLastVerified to MinValue.
insVerifyForInsPlan.UserNum = 0;
insVerifyForInsPlan.DateLastVerified = DateTime.MinValue;
InsVerifies.Update(insVerifyForInsPlan);
}
}
}
}
///<summary></summary>
public static void Update(InsVerify insVerify)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), insVerify);
return;
}
Crud.InsVerifyCrud.Update(insVerify);
}
///<summary></summary>
public static long Insert(InsVerify insVerify)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
insVerify.InsVerifyNum = Meth.GetLong(MethodBase.GetCurrentMethod(), insVerify);
return(insVerify.InsVerifyNum);
}
return(Crud.InsVerifyCrud.Insert(insVerify));
}
public InsVerifyGridObject(InsVerify pat = null, InsVerify plan = null)
{
if (pat != null)
{
PatInsVerify = pat;
}
if (plan != null)
{
PlanInsVerify = plan;
}
}
///<summary>Inserts a default InsVerify into the database based on the passed in insplan. Used when inserting a new insplan.
///Returns the primary key of the new InsVerify.</summary>
public static long InsertForPlanNum(long planNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetLong(MethodBase.GetCurrentMethod(), planNum));
}
InsVerify insVerify = new InsVerify();
insVerify.VerifyType = VerifyTypes.InsuranceBenefit;
insVerify.FKey = planNum;
return(Crud.InsVerifyCrud.Insert(insVerify));
}
public InsVerifyHist(InsVerify insVerify)
{
this.InsVerifyNum = insVerify.InsVerifyNum;
this.DateLastVerified = insVerify.DateLastVerified;
this.UserNum = insVerify.UserNum;
this.VerifyType = insVerify.VerifyType;
this.FKey = insVerify.FKey;
this.DefNum = insVerify.DefNum;
this.Note = insVerify.Note;
this.DateLastAssigned = insVerify.DateLastAssigned;
this.DateTimeEntry = insVerify.DateTimeEntry;
this.HoursAvailableForVerification = insVerify.HoursAvailableForVerification;
this.VerifyUserNum = Security.CurUser.UserNum;
}
///<summary>Gets one InsVerifyNum from the db that has the given fkey and verify type.</summary>
public static long GetInsVerifyNumByFKey(long fkey, VerifyTypes verifyType)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetLong(MethodBase.GetCurrentMethod(), fkey, verifyType));
}
string command = "SELECT * FROM insverify WHERE FKey=" + POut.Long(fkey) + " AND VerifyType=" + POut.Int((int)verifyType) + "";
InsVerify insVerify = Crud.InsVerifyCrud.SelectOne(command);
if (insVerify == null)
{
return(0);
}
return(insVerify.InsVerifyNum);
}
///<summary>If the passed in InsVerify is null, do nothing.
///Otherwise, insert the passed in InsVerify into InsVerifyHist and blank out InsVerify's UserNum, Status, and Note.</summary>
public static void InsertFromInsVerify(InsVerify insVerify)
{
if (insVerify == null)
{
return;
}
Insert(new InsVerifyHist(insVerify));
insVerify.UserNum = 0;
insVerify.DefNum = 0;
insVerify.Note = "";
insVerify.DateLastAssigned = DateTime.MinValue;
if (PrefC.GetBool(PrefName.InsVerifyFutureDateBenefitYear) && insVerify.AppointmentDateTime > DateTime.MinValue)
{
insVerify.DateLastVerified = insVerify.AppointmentDateTime;
}
InsVerifies.Update(insVerify);
}
///<summary>Calculates the time available to verify based on a variety of patient and appointment factors.
///Looks at Appointment date, appointment creation date, last verification date, next scheduled verification, insurance renewal
///</summary>
public static InsVerify SetTimeAvailableForVerify(InsVerify insVer, PlanToVerify planType, int appointmentScheduledDays, int insBenefitEligibilityDays,
int patientEnrollmentDays)
{
//DateAppointmentScheduled-DateAppointmentCreated
DateTime dateTimeLatestApptScheduling;
//DateAppointmentScheduled-appointmentScheduledDays (default is 7)
DateTime dateTimeUntilAppt;
//DateTime the appointment takes place.
DateTime dateTimeAppointment;
//DateTime the patient was scheduled to be re-verified
DateTime dateTimeScheduledReVerify;
//DateTime verification last took place. Will be 01/01/0001 if verification has never happened.
DateTime dateTimeLastVerified;
//DateTime the insurance renewal month rolls over.
//The month the renewal takes place is stored in the database. If the month is 0, then it is actually january.
//It is the first day of the given month at midnight, or (Month#)/01/(year) @ 00:00AM.
//Set to max val by default in case the PrefName.InsVerifyFutureDateBenefitYear=false.
//Since max val is later in time than the appointment time, it will be ignored.
DateTime dateBenifitRenewalNeeded = DateTime.MaxValue;
#region Appointment Dates
dateTimeAppointment = insVer.AppointmentDateTime;
dateTimeUntilAppt = insVer.AppointmentDateTime.AddDays(-appointmentScheduledDays);
//Calculate when the appointment was put into it's current time slot.
//This will be the earliest datetime where the scheduled appointment time is what it is now
List <HistAppointment> listHistAppt = HistAppointments.GetForApt(insVer.AptNum);
listHistAppt.RemoveAll(x => x.AptDateTime.Date != insVer.AppointmentDateTime.Date);
listHistAppt = listHistAppt.Where(x => x.AptStatus == ApptStatus.Scheduled).OrderBy(x => x.AptDateTime).ToList();
if (listHistAppt.Count > 0)
{
//If the appointment was moved to the current date after the (Apt.DateTime-appointmentScheduledDays),
//we only had (Apt.DateTime-listHistAppt.First().HistDateTstamp) days instead of (appointmentScheduledDays)
dateTimeLatestApptScheduling = listHistAppt.First().HistDateTStamp;
}
else
{
//Just in case there's no history for an appointment for some reason.
//Shouldn't happen because a log entry is created when the appointment is created.
//Use the date the appointment was created. This is better than nothing and should never happen anyways.
dateTimeLatestApptScheduling = Appointments.GetOneApt(insVer.AptNum).SecDateEntry;
}
#endregion Appointment Dates
#region Insurance Verification
dateTimeLastVerified = insVer.DateLastVerified;
//Add defined number of days to date last verified to calculate when the next verification date should have started.
if (planType == PlanToVerify.InsuranceBenefits)
{
if (insVer.DateLastVerified == DateTime.MinValue) //If it's the min value, the insurance has never been verified.
{
dateTimeScheduledReVerify = insVer.DateTimeEntry;
}
else
{
dateTimeScheduledReVerify = insVer.DateLastVerified.AddDays(insBenefitEligibilityDays);
}
}
else //PlanToVerify.PatientEligibility
{
if (insVer.DateLastVerified == DateTime.MinValue)
{
dateTimeScheduledReVerify = insVer.DateTimeEntry;
}
else
{
dateTimeScheduledReVerify = insVer.DateLastVerified.AddDays(patientEnrollmentDays);
}
}
#endregion insurance verification
#region Benifit Renewal
if (PrefC.GetBool(PrefName.InsVerifyFutureDateBenefitYear))
{
InsPlan insPlan = InsPlans.GetPlan(insVer.PlanNum, null);
//Setup the month renew dates. Need all 3 years in case the appointment verify window crosses over a year
//e.g. Appt verify date: 12/30/2016 and Appt Date: 1/6/2017
DateTime dateTimeOldestRenewal = new DateTime(DateTime.Now.Year - 1, Math.Max((byte)1, insPlan.MonthRenew), 1);
DateTime dateTimeMiddleRenewal = new DateTime(DateTime.Now.Year, Math.Max((byte)1, insPlan.MonthRenew), 1);
DateTime dateTimeNewestRenewal = new DateTime(DateTime.Now.Year + 1, Math.Max((byte)1, insPlan.MonthRenew), 1);
//We want to find the date closest to the appointment date without going past it.
if (dateTimeMiddleRenewal > dateTimeAppointment)
{
dateBenifitRenewalNeeded = dateTimeOldestRenewal;
}
else
{
if (dateTimeNewestRenewal > dateTimeAppointment)
{
dateBenifitRenewalNeeded = dateTimeMiddleRenewal;
}
else
{
dateBenifitRenewalNeeded = dateTimeNewestRenewal;
}
}
}
#endregion Benifit Renewal
DateTime dateTimeAbleToVerify = VerifyDateCalulation(dateTimeUntilAppt, dateTimeLatestApptScheduling, dateTimeScheduledReVerify, dateBenifitRenewalNeeded);
insVer.HoursAvailableForVerification = insVer.AppointmentDateTime.Subtract(dateTimeAbleToVerify).TotalHours;
return(insVer);
}
///<summary>UserNum=-1 is "All", UserNum=0 is "Unassigned".
///statusDefNum=-1 or statusDefNum=0 is "All".
///listClinicNums containing -1 is "All". listClinicNums containing 0 is "Unassigned".
///listRegionDefNums containing 0 or -1 is "All".</summary>
public static List <InsVerifyGridObject> GetVerifyGridList(DateTime startDate, DateTime endDate, DateTime datePatEligibilityLastVerified
, DateTime datePlanBenefitsLastVerified, List <long> listClinicNums, List <long> listRegionDefNums, long statusDefNum
, long userNum, string carrierName, bool excludePatVerifyWhenNoIns, bool excludePatClones)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <InsVerifyGridObject> >(MethodBase.GetCurrentMethod(), startDate, endDate, datePatEligibilityLastVerified
, datePlanBenefitsLastVerified, listClinicNums, listRegionDefNums, statusDefNum, userNum, carrierName, excludePatVerifyWhenNoIns, excludePatClones));
}
//clinicJoin should only be used if the passed in clinicNum is a value other than 0 (Unassigned).
string whereClinic = "";
if (listClinicNums.Contains(-1)) //All clinics
{
whereClinic = "AND (clinic.IsInsVerifyExcluded=0 OR clinic.ClinicNum IS NULL) ";
if (!listRegionDefNums.Contains(0) && !listRegionDefNums.Contains(-1) && listRegionDefNums.Count > 0) //Specific region
{
whereClinic += " AND clinic.Region IN(" + string.Join(",", listRegionDefNums.Select(x => POut.Long(x))) + ") ";
}
}
else if (listClinicNums.Contains(0)) //Unassigned clinics
{
whereClinic = "AND clinic.ClinicNum IS NULL ";
if (listClinicNums.Count(x => x != 0) > 0) //Also has specific clinics selected
{
whereClinic = "AND (clinic.ClinicNum IS NULL OR ";
whereClinic += "(clinic.IsInsVerifyExcluded=0 AND clinic.ClinicNum IN(" + string.Join(",", listClinicNums.Select(x => POut.Long(x))) + ") ";
if (!listRegionDefNums.Contains(0) && !listRegionDefNums.Contains(-1) && listRegionDefNums.Count > 0) //Specific region
{
whereClinic += " AND clinic.Region IN(" + string.Join(",", listRegionDefNums.Select(x => POut.Long(x))) + ") ";
}
whereClinic += ")) ";
}
}
else //Specific Clinic
{
whereClinic = "AND clinic.IsInsVerifyExcluded=0 AND clinic.ClinicNum IN(" + string.Join(",", listClinicNums.Select(x => POut.Long(x))) + ") ";
if (!listRegionDefNums.Contains(0) && !listRegionDefNums.Contains(-1) && listRegionDefNums.Count > 0) //Specific region
{
whereClinic += " AND clinic.Region IN(" + string.Join(",", listRegionDefNums.Select(x => POut.Long(x))) + ") ";
}
}
bool checkBenefitYear = PrefC.GetBool(PrefName.InsVerifyFutureDateBenefitYear);
string mainQuery = @"
SELECT insverify.*,
patient.LName,patient.FName,patient.Preferred,appointment.PatNum,appointment.AptNum,appointment.AptDateTime,patplan.PatPlanNum,insplan.PlanNum,carrier.CarrierName,
COALESCE(clinic.Abbr,'None') AS ClinicName
FROM appointment
LEFT JOIN clinic ON clinic.ClinicNum=appointment.ClinicNum
INNER JOIN patient ON patient.PatNum=appointment.PatNum
INNER JOIN patplan ON patplan.PatNum=appointment.PatNum
INNER JOIN inssub ON inssub.InsSubNum=patplan.InsSubNum
INNER JOIN insplan ON insplan.PlanNum=inssub.PlanNum
" + (excludePatVerifyWhenNoIns ? "AND insplan.HideFromVerifyList=0" : "") + @"
INNER JOIN carrier ON carrier.CarrierNum=insplan.CarrierNum
" + (string.IsNullOrEmpty(carrierName) ? "" : "AND carrier.CarrierName LIKE '%" + POut.String(carrierName) + "%'") + @"
" + (excludePatClones ? "LEFT JOIN patientlink ON patientlink.PatNumTo=patient.PatNum AND patientlink.LinkType="
+ POut.Int((int)PatientLinkType.Clone) + " " : "");
string insVerifyJoin1 = @"INNER JOIN insverify ON
(insverify.VerifyType=" + POut.Int((int)VerifyTypes.InsuranceBenefit) + @"
AND insverify.FKey=insplan.PlanNum
AND (insverify.DateLastVerified<" + POut.Date(datePlanBenefitsLastVerified) + @"
" + (checkBenefitYear?@"OR (insverify.DateLastVerified<DATE_FORMAT(appointment.AptDateTime,CONCAT('%Y-',LPAD(MonthRenew,2,'0'),'-01'))
AND DATE_FORMAT(appointment.AptDateTime,CONCAT('%Y-',LPAD(MonthRenew,2,'0'),'-01'))<=" + DbHelper.DtimeToDate("appointment.AptDateTime") + ")":"") + @")
" + (excludePatVerifyWhenNoIns ? "" : "AND insplan.HideFromVerifyList=0") + @") ";
string insVerifyJoin2 = @"INNER JOIN insverify ON
(insverify.VerifyType=" + POut.Int((int)VerifyTypes.PatientEnrollment) + @"
AND insverify.FKey=patplan.PatPlanNum
AND (insverify.DateLastVerified<" + POut.Date(datePatEligibilityLastVerified) + @"
" + (checkBenefitYear?@"OR (insverify.DateLastVerified<DATE_FORMAT(appointment.AptDateTime,CONCAT('%Y-',LPAD(MonthRenew,2,'0'),'-01'))
AND DATE_FORMAT(appointment.AptDateTime,CONCAT('%Y-',LPAD(MonthRenew,2,'0'),'-01'))<=" + DbHelper.DtimeToDate("appointment.AptDateTime") + ")":"") + @")) ";
string whereClause = @"
WHERE appointment.AptDateTime BETWEEN " + DbHelper.DtimeToDate(POut.Date(startDate)) + " AND " + DbHelper.DtimeToDate(POut.Date(endDate.AddDays(1))) + @"
AND appointment.AptStatus IN (" + POut.Int((int)ApptStatus.Scheduled) + "," + POut.Int((int)ApptStatus.Complete) + @")
" + (userNum == -1 ? "" : "AND insverify.UserNum=" + POut.Long(userNum)) + @"
" + (statusDefNum < 1 ? "" : "AND insverify.DefNum=" + POut.Long(statusDefNum)) + @"
" + (excludePatClones ? "AND patientlink.PatNumTo IS NULL" : "") + @"
" + whereClinic;
//Previously we joined the insverify table using a large OR clause. This caused MySQL to not be able to use any index on the insverify table.
//Now we run two unioned queries, each with a different join clause for the insverify table, so that MySQL can use insverify.FKKey as an index.
string command =
mainQuery +
insVerifyJoin1 +
whereClause + @"
UNION ALL
" +
mainQuery +
insVerifyJoin2 +
whereClause + @"
ORDER BY AptDateTime" ;
DataTable table = Db.GetTable(command);
List <InsVerify> listInsVerifies = Crud.InsVerifyCrud.TableToList(table);
List <InsVerifyGridObject> retVal = new List <InsVerifyGridObject>();
for (int i = 0; i < table.Rows.Count; i++)
{
DataRow row = table.Rows[i];
InsVerify insVerifyCur = listInsVerifies[i].Clone();
insVerifyCur.PatNum = PIn.Long(row["PatNum"].ToString());
insVerifyCur.PlanNum = PIn.Long(row["PlanNum"].ToString());
insVerifyCur.PatPlanNum = PIn.Long(row["PatPlanNum"].ToString());
insVerifyCur.ClinicName = PIn.String(row["ClinicName"].ToString());
string patName = PIn.String(row["LName"].ToString())
+ ", ";
if (PIn.String(row["Preferred"].ToString()) != "")
{
patName += "'" + PIn.String(row["Preferred"].ToString()) + "' ";
}
patName += PIn.String(row["FName"].ToString());
insVerifyCur.PatientName = patName;
insVerifyCur.CarrierName = PIn.String(row["CarrierName"].ToString());
insVerifyCur.AppointmentDateTime = PIn.DateT(row["AptDateTime"].ToString());
insVerifyCur.AptNum = PIn.Long(row["AptNum"].ToString());
if (insVerifyCur.VerifyType == VerifyTypes.InsuranceBenefit)
{
InsVerifyGridObject gridObjPlanExists = retVal.FirstOrDefault(x => x.PlanInsVerify != null && x.PlanInsVerify.PlanNum == insVerifyCur.PlanNum);
if (gridObjPlanExists == null)
{
InsVerifyGridObject gridObjExists = retVal.FirstOrDefault(x => x.PatInsVerify != null &&
x.PatInsVerify.PatPlanNum == insVerifyCur.PatPlanNum &&
x.PatInsVerify.PlanNum == insVerifyCur.PlanNum &&
x.PatInsVerify.Note == insVerifyCur.Note &&
x.PatInsVerify.DefNum == insVerifyCur.DefNum &&
x.PlanInsVerify == null);
if (gridObjExists != null)
{
gridObjExists.PlanInsVerify = insVerifyCur;
}
else
{
retVal.Add(new InsVerifyGridObject(plan: insVerifyCur));
}
}
}
else if (insVerifyCur.VerifyType == VerifyTypes.PatientEnrollment)
{
InsVerifyGridObject gridObjPatExists = retVal.FirstOrDefault(x => x.PatInsVerify != null && x.PatInsVerify.PatPlanNum == insVerifyCur.PatPlanNum);
if (gridObjPatExists == null)
{
InsVerifyGridObject gridObjExists = retVal.FirstOrDefault(x => x.PlanInsVerify != null &&
x.PlanInsVerify.PlanNum == insVerifyCur.PlanNum &&
x.PlanInsVerify.Note == insVerifyCur.Note &&
x.PlanInsVerify.DefNum == insVerifyCur.DefNum &&
x.PatInsVerify == null);
if (gridObjExists != null)
{
gridObjExists.PatInsVerify = insVerifyCur;
}
else
{
retVal.Add(new InsVerifyGridObject(pat: insVerifyCur));
}
}
}
}
return(retVal);
}
