public static void DeleteBeforeDateInclusive(DateTime date)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), date);
return;
}
int countDeleted = 0;
List <long> listSecurityLogNums;
do
{
//Delete the hashes
MiscDataEvent.Fire(CodeBase.ODEventType.MiscData,
Lans.g("FormBackup", "Removing old data from securityloghash table. Rows deleted so far:") + " " + countDeleted);
//limiting to 500,000 to avoid out of memory exceptions
string command = $"SELECT SecurityLogNum FROM securitylog WHERE DATE(LogDateTime) <= {POut.DateT(date.Date)} LIMIT 500000";
listSecurityLogNums = Db.GetListLong(command);
if (listSecurityLogNums.Count < 1)
{
break;
}
SecurityLogHashes.DeleteForSecurityLogEntries(listSecurityLogNums);
//Then delete the securitylog entries themselves
MiscDataEvent.Fire(CodeBase.ODEventType.MiscData,
Lans.g("FormBackup", "Removing old data from securitylog table. Rows deleted so far:") + " " + countDeleted);
command = $"DELETE FROM securitylog WHERE SecurityLogNum IN ({string.Join(",",listSecurityLogNums)})";
Db.NonQ(command);
countDeleted += listSecurityLogNums.Count;
}while(listSecurityLogNums.Count > 0);
}
///<summary>Deletes FeeScheds that are hidden and not attached to any insurance plans. Returns the number of deleted fee scheds.</summary>
public static long CleanupAllowedScheds()
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetLong(MethodBase.GetCurrentMethod()));
}
long result;
//Detach allowed FeeSchedules from any hidden InsPlans.
string command = "UPDATE insplan "
+ "SET AllowedFeeSched=0 "
+ "WHERE IsHidden=1";
Db.NonQ(command);
//Delete unattached FeeSchedules.
command = "DELETE FROM feesched "
+ "WHERE FeeSchedNum NOT IN (SELECT AllowedFeeSched FROM insplan) "
+ "AND FeeSchedType=" + POut.Int((int)FeeScheduleType.OutNetwork);
result = Db.NonQ(command);
//Delete all orphaned fees.
command = "SELECT FeeNum FROM fee "
+ "WHERE FeeSched NOT IN (SELECT FeeSchedNum FROM feesched)";
List <long> listFeeNums = Db.GetListLong(command);
Fees.DeleteMany(listFeeNums);
return(result);
}
///<summary>Returns dictionary such that every given claim num is a key and the value is a bool indicating if their is a claimpayment associated to it.</summary>
public static SerializableDictionary <long, bool> HasClaimPayment(List <long> listClaimNums)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <SerializableDictionary <long, bool> >(MethodBase.GetCurrentMethod(), listClaimNums));
}
SerializableDictionary <long, bool> retVal = new SerializableDictionary <long, bool>();
listClaimNums = listClaimNums.Distinct().ToList();
if (listClaimNums == null || listClaimNums.Count == 0)
{
return(retVal);
}
string command = @"SELECT claimproc.ClaimNum
FROM claimproc
INNER JOIN claimpayment ON claimpayment.ClaimPaymentNum=claimproc.ClaimPaymentNum
WHERE claimproc.ClaimNum IN (" + string.Join(",", listClaimNums) + ") " +
"GROUP BY claimproc.ClaimNum";
List <long> listRetClaimNums = Db.GetListLong(command);
foreach (long claimNum in listClaimNums)
{
retVal.Add(claimNum, listRetClaimNums.Contains(claimNum));
}
return(retVal);
}
///<summary>Get document info for all images linked to this patient.
///Also gets dependents' images if this patient is a guarantor (needed by the patient portal).</summary>
public static List <Document> GetPatientPortalDocsForPat(long patNum, List <long> listDependents)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <Document> >(MethodBase.GetCurrentMethod(), patNum, listDependents));
}
string command = "SELECT def.DefNum FROM definition def "
+ "WHERE def.Category=" + POut.Int((int)DefCat.ImageCats) + " "
+ "AND def.IsHidden=0 "
+ "AND def.ItemValue LIKE '%L%' "; //Patient Portal category indicator
List <long> listDefNums = Db.GetListLong(command);
if (listDefNums.Count == 0) //There are no Patient Portal image categories
{
return(new List <Document>());
}
if (!listDependents.Contains(patNum))
{
listDependents.Add(patNum);
}
command = "SELECT * FROM document d "
+ "WHERE d.PatNum IN (" + string.Join(",", listDependents) + ") "
+ "AND d.DocCategory IN (" + string.Join(",", listDefNums) + ") "
+ "ORDER BY d.DateCreated DESC";
return(Crud.DocumentCrud.SelectMany(command));
}
///<summary>Hides FeeScheds that are not hidden and not in use by anything. Returns the number of fee scheds that were hidden.</summary>
public static long HideUnusedScheds()
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetLong(MethodBase.GetCurrentMethod()));
}
ODEvent.Fire(ODEventType.HideUnusedFeeSchedules, Lans.g("FormFeeScheds", "Finding unused fee schedules..."));
string command = @"SELECT feesched.FeeSchedNum
FROM feesched
LEFT JOIN provider ON provider.FeeSched=feesched.FeeSchedNum
LEFT JOIN patient ON patient.FeeSched=feesched.FeeSchedNum
LEFT JOIN insplan ON insplan.FeeSched=feesched.FeeSchedNum
OR insplan.AllowedFeeSched=feesched.FeeSchedNum
OR insplan.CopayFeeSched=feesched.FeeSchedNum
LEFT JOIN discountplan ON discountplan.FeeSchedNum=feesched.FeeSchedNum
WHERE COALESCE(provider.FeeSched,patient.FeeSched,insplan.FeeSched,discountplan.FeeSchedNum) IS NULL
AND feesched.IsHidden=0" ;
List <long> listFeeScheds = Db.GetListLong(command);
if (listFeeScheds.Count == 0)
{
return(0);
}
ODEvent.Fire(ODEventType.HideUnusedFeeSchedules, Lans.g("FormFeeScheds", "Hiding unused fee schedules..."));
command = "UPDATE feesched SET IsHidden=1 WHERE FeeSchedNum IN(" + string.Join(",", listFeeScheds.Select(x => POut.Long(x))) + ")";
long rowsChanged = Db.NonQ(command);
return(rowsChanged);
}
///<summary>Gets all AptNums for HistAppointments that have a DateTStamp after dateTimeSince.</summary>
public static List <long> GetAptNumsChangedSince(DateTime dateTimeSince)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <long> >(MethodBase.GetCurrentMethod(), dateTimeSince));
}
string command = "SELECT AptNum FROM histappointment WHERE DateTStamp > " + POut.DateT(dateTimeSince);
return(Db.GetListLong(command));
}
public static List <long> GetAllInsVerifyUserNums()
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <long> >(MethodBase.GetCurrentMethod()));
}
string command = "SELECT DISTINCT UserNum FROM insverify";
return(Db.GetListLong(command));
}
///<summary>Get TaskListNums based on description.</summary>
public static List <long> GetNumsByDescription(string descript, bool doRunOnReportServer)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <long> >(MethodBase.GetCurrentMethod(), descript, doRunOnReportServer));
}
string command = "SELECT TaskListNum FROM tasklist WHERE Descript LIKE '%" + POut.String(descript) + "%'";
return(ReportsComplex.RunFuncOnReportServer(() => Db.GetListLong(command), doRunOnReportServer));
}
///<summary>Gets all of the 'PatNumFroms's linked to the passed-in patNumTo. Does not recursively look up additional patient links.
///The list returned will NOT include the patient passed in unless there is an entry in the database linking them to... themselves.</summary>
public static List <long> GetPatNumsLinkedTo(long patNumTo, PatientLinkType patLinkType)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <long> >(MethodBase.GetCurrentMethod(), patNumTo, patLinkType));
}
string command = "SELECT PatNumFrom FROM patientlink "
+ "WHERE PatNumTo=" + POut.Long(patNumTo) + " "
+ "AND LinkType=" + POut.Int((int)patLinkType);
return(Db.GetListLong(command));
}
///<summary>Get completed job num for the date range based on the "job implemented" JobLog.</summary>
public static List <long> GetCompletedJobNumsForRange(DateTime dateStart, DateTime dateEnd)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <long> >(MethodBase.GetCurrentMethod(), dateStart, dateEnd));
}
string command = $@"SELECT DISTINCT JobNum FROM joblog
WHERE Description LIKE '%Job implemented%'
AND DateTimeEntry BETWEEN {POut.Date(dateStart)} AND {POut.Date(dateEnd)}" ;
return(Db.GetListLong(command));
}
///<summary>Gets all FKeys for the statement and StmtLinkType passed in. Returns an empty list if statementNum is invalid or none found.</summary>
public static List <long> GetForStatementAndType(long statementNum, StmtLinkTypes statementType)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <long> >(MethodBase.GetCurrentMethod(), statementNum, statementType));
}
string command = "SELECT FKey FROM stmtlink "
+ "WHERE StatementNum=" + POut.Long(statementNum) + " "
+ "AND StmtLinkType=" + POut.Int((int)statementType);
return(Db.GetListLong(command));
}
///<summary>Deletes all fees for the supplied FeeSched that aren't for the HQ clinic.</summary>
public static void DeleteNonHQFeesForSched(long feeSchedNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), feeSchedNum);
return;
}
string command = "SELECT FeeNum FROM fee WHERE FeeSched=" + POut.Long(feeSchedNum) + " AND ClinicNum!=0";
List <long> listFeeNums = Db.GetListLong(command);
DeleteMany(listFeeNums);
}
///<summary>Gets logs from the passed in datetime and before.</summary>
public static List <InsEditLog> GetLogsForPlan(long planNum, long carrierNum, long employerNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <InsEditLog> >(MethodBase.GetCurrentMethod(), planNum, carrierNum, employerNum));
}
List <long> listCarrierNums = InsEditLogs.GetAssociatedCarrierNums(planNum);
listCarrierNums.Add(carrierNum);
List <InsEditLog> retVal = new List <InsEditLog>();
string command = @"SELECT PlanNum FROM insplan WHERE PlanNum = " + POut.Long(planNum);
long insPlanNum = Db.GetLong(command);
command = @"SELECT CarrierNum FROM carrier WHERE CarrierNum IN (" + string.Join(",", listCarrierNums) + @")";
listCarrierNums = Db.GetListLong(command);
command = @"SELECT EmployerNum FROM employer WHERE EmployerNum=" + POut.Long(employerNum);
long empNum = Db.GetLong(command);
List <string> listWhereOrs = new List <string>();
if (insPlanNum > 0)
{
listWhereOrs.Add("(LogType=" + POut.Int((int)InsEditLogType.InsPlan) + " AND FKey = " + POut.Long(insPlanNum) + ")");
}
if (listCarrierNums.Count > 0)
{
listWhereOrs.Add("(LogType=" + POut.Int((int)InsEditLogType.Carrier) + " AND FKey IN (" + string.Join(",", listCarrierNums) + "))");
}
if (empNum > 0)
{
listWhereOrs.Add("(LogType=" + POut.Int((int)InsEditLogType.Employer) + " AND FKey=" + POut.Long(empNum) + ")");
}
listWhereOrs.Add("(LogType=" + POut.Int((int)InsEditLogType.Benefit) + " AND ParentKey=" + POut.Long(planNum) + ")");
command = @"SELECT * FROM inseditlog
WHERE " + string.Join(@"
OR " , listWhereOrs);
List <InsEditLog> listLogs = Crud.InsEditLogCrud.SelectMany(command); //get all of the logs
//get any logs that show that InsPlan's PlanNum changed
return(GetChangedLogs(listLogs).OrderBy(x => x.DateTStamp)
.ThenBy(x => x.LogType != InsEditLogType.InsPlan)
.ThenBy(x => x.LogType != InsEditLogType.Carrier)
.ThenBy(x => x.LogType != InsEditLogType.Employer)
.ThenBy(x => x.LogType != InsEditLogType.Benefit)
.ThenBy(x => x.FKey)
//primary keys first
.ThenBy(x => x.LogType == InsEditLogType.Benefit?(x.FieldName != "BenefitNum")
:x.LogType == InsEditLogType.Carrier?(x.FieldName != "CarrierNum")
:x.LogType == InsEditLogType.Employer?(x.FieldName != "EmployerNum")
:(x.FieldName != "PlanNum")).ToList());
}
///<summary>Returns a list of valid diseasedefnums to delete from the passed in list.</summary>
public static List <long> ValidateDeleteList(List <long> listDiseaseDefNums)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <long> >(MethodBase.GetCurrentMethod(), listDiseaseDefNums));
}
List <long> listDiseaseDefNumsNotDeletable = new List <long>();
if (listDiseaseDefNums == null || listDiseaseDefNums.Count < 1)
{
return(listDiseaseDefNumsNotDeletable);
}
//In use by preference
if (listDiseaseDefNums.Contains(PrefC.GetLong(PrefName.ProblemsIndicateNone)))
{
listDiseaseDefNumsNotDeletable.Add(PrefC.GetLong(PrefName.ProblemsIndicateNone));
}
//Validate patient attached
string command = "SELECT DISTINCT disease.DiseaseDefNum "
+ "FROM patient,disease "
+ "WHERE patient.PatNum=disease.PatNum "
+ "AND disease.DiseaseDefNum IN (" + String.Join(",", listDiseaseDefNums) + ") ";
try {
listDiseaseDefNumsNotDeletable.AddRange(Db.GetListLong(command));
}
catch {
//Do Nothing
}
//Validate edu resource attached
command = "SELECT DISTINCT eduresource.DiseaseDefNum FROM eduresource WHERE eduresource.DiseaseDefNum IN (" + String.Join(",", listDiseaseDefNums) + ") ";
try {
listDiseaseDefNumsNotDeletable.AddRange(Db.GetListLong(command));
}
catch {
//Do Nothing
}
//Validate family health history attached
command = "SELECT DISTINCT familyhealth.DiseaseDefNum FROM patient,familyhealth "
+ "WHERE patient.PatNum=familyhealth.PatNum "
+ "AND familyhealth.DiseaseDefNum IN (" + String.Join(",", listDiseaseDefNums) + ") ";
try {
listDiseaseDefNumsNotDeletable.AddRange(Db.GetListLong(command));
}
catch {
//Do Nothing
}
return(listDiseaseDefNumsNotDeletable);
}
///<summary>Returns a list of PatNums that have an allergy from the PatNums that are passed in.</summary>
public static List <long> GetPatientsWithAllergy(List <long> listPatNums)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <long> >(MethodBase.GetCurrentMethod(), listPatNums));
}
if (listPatNums.Count == 0)
{
return(new List <long>());
}
string command = "SELECT DISTINCT PatNum FROM allergy WHERE PatNum IN (" + string.Join(",", listPatNums) + ") "
+ "AND allergy.AllergyDefNum != " + POut.Long(PrefC.GetLong(PrefName.AllergiesIndicateNone));
return(Db.GetListLong(command));
}
///<summary>May not return correct values if notes are stored with newline characters.</summary>
public static List <long> GetNumsByNote(string oldNote)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <long> >(MethodBase.GetCurrentMethod(), oldNote));
}
oldNote = oldNote.Replace("\r", "");
//oldNote=oldNote.Replace("\r","").Replace("\n","\r\n");
//oldNote=oldNote.Replace("\r","").Replace("\n","*?");
string command = "SELECT TreatPlanNum FROM treatplan WHERE REPLACE(Note,'\\r','')='" + POut.String(oldNote) + "' " +
"AND TPStatus IN (" + POut.Int((int)TreatPlanStatus.Active) + "," + POut.Int((int)TreatPlanStatus.Inactive) + ")";
//string command="SELECT TreatPlanNum FROM treatplan WHERE Note='"+POut.String(oldNote)+"' "+
// "AND TPStatus IN ("+POut.Int((int)TreatPlanStatus.Active)+","+POut.Int((int)TreatPlanStatus.Inactive)+")";
return(Db.GetListLong(command));
}
///<summary>Returns the PatNum for the patient with a matching phone number. If no patient is found, returns the PatNum for 'Misc.'
///If more than one patient is found, returns 0. Called from PhoneTrackingServer.</summary>
public static long GetPatNumForPhone(string phoneNumber)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetLong(MethodBase.GetCurrentMethod(), phoneNumber));
}
if (phoneNumber.Count(x => char.IsDigit(x)) == 0)
{
return(MiscPatNum);
}
string phoneRegex = string.Join("[^0-9]*", phoneNumber.Where(x => char.IsDigit(x))); //So that any intervening non-digit characters will still match
if (phoneNumber.Count(x => char.IsDigit(x)) > 3) //If there is more than three digits
{
if (phoneRegex.StartsWith("1")) //and the first digit is 1, make it optional.
{
phoneRegex = "1?" + phoneRegex.Substring(1);
}
else
{
phoneRegex = "1?[^0-9]*" + phoneRegex; //add a leading 1 so that 1-800 numbers can show up simply by typing in 800 followed by the number.
}
}
string command = @"SELECT DISTINCT patient.PatNum
FROM patient
LEFT JOIN phonenumber ON phonenumber.PatNum=patient.PatNum
WHERE (patient.HmPhone REGEXP '" + POut.String(phoneRegex) + @"'
OR patient.WkPhone REGEXP '" + POut.String(phoneRegex) + @"'
OR patient.WirelessPhone REGEXP '" + POut.String(phoneRegex) + @"'
OR phonenumber.PhoneNumberVal REGEXP '" + POut.String(phoneRegex) + @"')
AND patient.PatStatus!=" + POut.Int((int)PatientStatus.Deleted);
List <long> listPatNums = Db.GetListLong(command);
if (listPatNums.Count == 0)
{
return(MiscPatNum);
}
if (listPatNums.Count > 1)
{
return(0);
}
return(listPatNums[0]);
}
public static List <long> GetAllInUseMedicationNums()
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <long> >(MethodBase.GetCurrentMethod()));
}
//If any more tables are added here in the future, then also update IsInUse() to include the new table.
string command = "SELECT MedicationNum FROM medicationpat WHERE MedicationNum!=0 "
+ "UNION SELECT MedicationNum FROM allergydef WHERE MedicationNum!=0 "
+ "UNION SELECT MedicationNum FROM eduresource WHERE MedicationNum!=0 "
+ "GROUP BY MedicationNum";
List <long> listMedicationNums = Db.GetListLong(command);
if (PrefC.GetLong(PrefName.MedicationsIndicateNone) != 0)
{
listMedicationNums.Add(PrefC.GetLong(PrefName.MedicationsIndicateNone));
}
return(listMedicationNums);
}
///<summary>Returns a list of PatNums for guars who have a TsiTransLog with type SS (suspend) less than 50 days ago who don't have a TsiTransLog
///with type CN (cancel), PF (paid in full), PT (paid in full, thank you), or PL (placement) with a more recent date, since this would change the
///account status from suspended to either closed/canceled or if the more recent message had type PL (placement) back to active.</summary>
public static List <long> GetSuspendedGuarNums()
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <long> >(MethodBase.GetCurrentMethod()));
}
int[] arrayStatusTransTypes = new[] { (int)TsiTransType.SS, (int)TsiTransType.CN, (int)TsiTransType.RI, (int)TsiTransType.PF, (int)TsiTransType.PT, (int)TsiTransType.PL };
string command = "SELECT DISTINCT tsitranslog.PatNum "
+ "FROM tsitranslog "
+ "INNER JOIN ("
+ "SELECT PatNum,MAX(TransDateTime) transDateTime "
+ "FROM tsitranslog "
+ "WHERE TransType IN(" + string.Join(",", arrayStatusTransTypes) + ") "
+ "AND TransDateTime>" + POut.DateT(DateTime.Now.AddDays(-50)) + " "
+ "GROUP BY PatNum"
+ ") mostRecentTrans ON tsitranslog.PatNum=mostRecentTrans.PatNum "
+ "AND tsitranslog.TransDateTime=mostRecentTrans.transDateTime "
+ "WHERE tsitranslog.TransType=" + (int)TsiTransType.SS;
return(Db.GetListLong(command));
}
///<summary>Gets all the PatNums that are recursively linked to this PatNum and adds them to the list passed in.</summary>
private static void AddPatNumsLinkedFromRecursive(long patNumFrom, PatientLinkType patLinkType, List <long> listPatNums)
{
//No need to check RemotingRole; private method.
string command = "SELECT PatNumTo FROM patientlink "
+ "WHERE PatNumFrom=" + POut.Long(patNumFrom) + " "
+ "AND LinkType=" + POut.Int((int)patLinkType);
List <long> listPatNumTos = Db.GetListLong(command);
if (listPatNumTos.Count == 0)
{
return; //Base case
}
foreach (long patNumTo in listPatNumTos)
{
if (listPatNums.Contains(patNumTo))
{
continue; //So that a patient that links to itself does not cause an infinite circle of recursion.
}
listPatNums.Add(patNumTo);
AddPatNumsLinkedFromRecursive(patNumTo, patLinkType, listPatNums); //Find all the patients that are linked to the "To" patient.
}
}
///<summary>Gets a list of aptNums for one day in the schedule for a given set of providers and clinics. Will be for all clinics and/or all provs
///if the corresponding list is null or empty.</summary>
public static List <long> GetRouting(DateTime date, List <long> listProvNums, List <long> listClinicNums)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <long> >(MethodBase.GetCurrentMethod(), date, listProvNums, listClinicNums));
}
//Excluding PtNote and PtNoteCompleted per Nathan and Arna, see job 1064
string command = "SELECT AptNum FROM appointment "
+ "WHERE " + DbHelper.DateTConditionColumn("AptDateTime", ConditionOperator.Equals, date) + " "
+ "AND AptStatus NOT IN (" + POut.Int((int)ApptStatus.UnschedList) + "," + POut.Int((int)ApptStatus.Planned) + "," + POut.Int((int)ApptStatus.PtNote) + ","
+ POut.Int((int)ApptStatus.PtNoteCompleted) + ") ";
if (listProvNums != null && listProvNums.Count > 0)
{
command += "AND (ProvNum IN (" + string.Join(",", listProvNums) + ") OR ProvHyg IN (" + string.Join(",", listProvNums) + ")) ";
}
if (listClinicNums != null && listClinicNums.Count > 0)
{
command += "AND ClinicNum IN (" + string.Join(",", listClinicNums) + ") ";
}
command += "ORDER BY AptDateTime";
return(ReportsComplex.RunFuncOnReportServer(() => Db.GetListLong(command)));
}
public static void CleanupInsVerifyRows(DateTime startDate, DateTime endDate)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), startDate, endDate);
return;
}
//Nathan OK'd the necessity for a complex update query like this to avoid looping through update statements. This will be changed to a crud update method sometime in the future.
string command = "";
List <long> listInsVerifyNums = Db.GetListLong(GetInsVerifyCleanupQuery(startDate, endDate));
if (listInsVerifyNums.Count == 0)
{
return;
}
command = "UPDATE insverify "
+ "SET insverify.DateLastAssigned='0001-01-01', "
+ "insverify.DefNum=0, "
+ "insverify.Note='', "
+ "insverify.UserNum=0 "
+ "WHERE insverify.InsVerifyNum IN (" + string.Join(",", listInsVerifyNums) + ")";
Db.NonQ(command);
}
///<summary>Used to check whether codes starting with T exist and are in a visible category. If so, it moves them to the Obsolete category. If the T code has never been used, then it deletes it.</summary>
public static void TcodesClear()
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod());
return;
}
//first delete any unused T codes
string command = @"SELECT CodeNum,ProcCode FROM procedurecode
WHERE CodeNum NOT IN(SELECT CodeNum FROM procedurelog)
AND CodeNum NOT IN(SELECT CodeNum FROM autocodeitem)
AND CodeNum NOT IN(SELECT CodeNum FROM procbuttonitem)
AND CodeNum NOT IN(SELECT CodeNum FROM recalltrigger)
AND CodeNum NOT IN(SELECT CodeNum FROM benefit)
AND ProcCode NOT IN(SELECT CodeValue FROM encounter WHERE CodeSystem='CDT')
AND ProcCode LIKE 'T%'" ;
DataTable table = Db.GetTable(command);
List <long> listCodeNums = new List <long>();
List <string> listRecallCodes = RecallTypes.GetDeepCopy()
.SelectMany(x => x.Procedures.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries))
.ToList();
for (int i = 0; i < table.Rows.Count; i++)
{
if (!listRecallCodes.Contains(PIn.String(table.Rows[i]["ProcCode"].ToString()))) //The ProcCode is not attached to a recall type.
{
listCodeNums.Add(PIn.Long(table.Rows[i]["CodeNum"].ToString()));
}
}
if (listCodeNums.Count > 0)
{
ProcedureCodes.ClearFkey(listCodeNums); //Zero securitylog FKey column for rows to be deleted.
command = "SELECT FeeNum FROM fee WHERE CodeNum IN(" + String.Join(",", listCodeNums) + ")";
List <long> listFeeNums = Db.GetListLong(command);
Fees.DeleteMany(listFeeNums);
command = "DELETE FROM proccodenote WHERE CodeNum IN(" + String.Join(",", listCodeNums) + ")";
Db.NonQ(command);
command = "DELETE FROM procedurecode WHERE CodeNum IN(" + String.Join(",", listCodeNums) + ")";
Db.NonQ(command);
}
//then, move any other T codes to obsolete category
command = @"SELECT DISTINCT ProcCat FROM procedurecode,definition
WHERE procedurecode.ProcCode LIKE 'T%'
AND definition.IsHidden=0
AND procedurecode.ProcCat=definition.DefNum" ;
table = Db.GetTable(command);
long catNum = Defs.GetByExactName(DefCat.ProcCodeCats, "Obsolete"); //check to make sure an Obsolete category exists.
Def def;
if (catNum != 0) //if a category exists with that name
{
def = Defs.GetDef(DefCat.ProcCodeCats, catNum);
if (!def.IsHidden)
{
def.IsHidden = true;
Defs.Update(def);
Defs.RefreshCache();
}
}
if (catNum == 0)
{
List <Def> listDefs = Defs.GetDefsForCategory(DefCat.ProcCodeCats);
def = new Def();
def.Category = DefCat.ProcCodeCats;
def.ItemName = "Obsolete";
def.ItemOrder = listDefs.Count;
def.IsHidden = true;
Defs.Insert(def);
Defs.RefreshCache();
catNum = def.DefNum;
}
for (int i = 0; i < table.Rows.Count; i++)
{
command = "UPDATE procedurecode SET ProcCat=" + POut.Long(catNum)
+ " WHERE ProcCat=" + table.Rows[i][0].ToString()
+ " AND procedurecode.ProcCode LIKE 'T%'";
Db.NonQ(command);
}
//finally, set Never Used category to be hidden. This isn't really part of clearing Tcodes, but is required
//because many customers won't have that category hidden
catNum = Defs.GetByExactName(DefCat.ProcCodeCats, "Never Used");
if (catNum != 0) //if a category exists with that name
{
def = Defs.GetDef(DefCat.ProcCodeCats, catNum);
if (!def.IsHidden)
{
def.IsHidden = true;
Defs.Update(def);
Defs.RefreshCache();
}
}
}
///<summary>Checks dependencies first. Throws exception if can't delete.</summary>
public static void Delete(Clinic clinic)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), clinic);
return;
}
//Check FK dependencies.
#region Patients
string command = "SELECT LName,FName FROM patient WHERE ClinicNum ="
+ POut.Long(clinic.ClinicNum);
DataTable table = Db.GetTable(command);
if (table.Rows.Count > 0)
{
string pats = "";
for (int i = 0; i < table.Rows.Count; i++)
{
pats += "\r";
if (i == 15)
{
pats += Lans.g("Clinics", "And") + " " + (table.Rows.Count - i) + " " + Lans.g("Clinics", "others");
break;
}
pats += table.Rows[i]["LName"].ToString() + ", " + table.Rows[i]["FName"].ToString();
}
throw new Exception(Lans.g("Clinics", "Cannot delete clinic because it is in use by the following patients:") + pats);
}
#endregion
#region Payments
command = "SELECT patient.LName,patient.FName FROM patient,payment "
+ "WHERE payment.ClinicNum =" + POut.Long(clinic.ClinicNum)
+ " AND patient.PatNum=payment.PatNum";
table = Db.GetTable(command);
if (table.Rows.Count > 0)
{
string pats = "";
for (int i = 0; i < table.Rows.Count; i++)
{
pats += "\r";
if (i == 15)
{
pats += Lans.g("Clinics", "And") + " " + (table.Rows.Count - i) + " " + Lans.g("Clinics", "others");
break;
}
pats += table.Rows[i]["LName"].ToString() + ", " + table.Rows[i]["FName"].ToString();
}
throw new Exception(Lans.g("Clinics", "Cannot delete clinic because the following patients have payments using it:") + pats);
}
#endregion
#region ClaimPayments
command = "SELECT patient.LName,patient.FName FROM patient,claimproc,claimpayment "
+ "WHERE claimpayment.ClinicNum =" + POut.Long(clinic.ClinicNum)
+ " AND patient.PatNum=claimproc.PatNum"
+ " AND claimproc.ClaimPaymentNum=claimpayment.ClaimPaymentNum "
+ "GROUP BY patient.LName,patient.FName,claimpayment.ClaimPaymentNum";
table = Db.GetTable(command);
if (table.Rows.Count > 0)
{
string pats = "";
for (int i = 0; i < table.Rows.Count; i++)
{
pats += "\r";
if (i == 15)
{
pats += Lans.g("Clinics", "And") + " " + (table.Rows.Count - i) + " " + Lans.g("Clinics", "others");
break;
}
pats += table.Rows[i]["LName"].ToString() + ", " + table.Rows[i]["FName"].ToString();
}
throw new Exception(Lans.g("Clinics", "Cannot delete clinic because the following patients have claim payments using it:") + pats);
}
#endregion
#region Appointments
command = "SELECT patient.LName,patient.FName FROM patient,appointment "
+ "WHERE appointment.ClinicNum =" + POut.Long(clinic.ClinicNum)
+ " AND patient.PatNum=appointment.PatNum";
table = Db.GetTable(command);
if (table.Rows.Count > 0)
{
string pats = "";
for (int i = 0; i < table.Rows.Count; i++)
{
pats += "\r";
if (i == 15)
{
pats += Lans.g("Clinics", "And") + " " + (table.Rows.Count - i) + " " + Lans.g("Clinics", "others");
break;
}
pats += table.Rows[i]["LName"].ToString() + ", " + table.Rows[i]["FName"].ToString();
}
throw new Exception(Lans.g("Clinics", "Cannot delete clinic because the following patients have appointments using it:") + pats);
}
#endregion
#region Procedures
//reassign procedure.ClinicNum=0 if the procs are status D.
command = "SELECT ProcNum FROM procedurelog WHERE ProcStatus=" + POut.Int((int)ProcStat.D) + " AND ClinicNum=" + POut.Long(clinic.ClinicNum);
List <long> listProcNums = Db.GetListLong(command);
if (listProcNums.Count > 0)
{
command = "UPDATE procedurelog SET ClinicNum=0 WHERE ProcNum IN (" + string.Join(",", listProcNums.Select(x => POut.Long(x))) + ")";
Db.NonQ(command);
}
command = "SELECT patient.LName,patient.FName FROM patient,procedurelog "
+ "WHERE procedurelog.ClinicNum =" + POut.Long(clinic.ClinicNum)
+ " AND patient.PatNum=procedurelog.PatNum";
table = Db.GetTable(command);
if (table.Rows.Count > 0)
{
string pats = "";
for (int i = 0; i < table.Rows.Count; i++)
{
pats += "\r";
if (i == 15)
{
pats += Lans.g("Clinics", "And") + " " + (table.Rows.Count - i) + " " + Lans.g("Clinics", "others");
break;
}
pats += table.Rows[i]["LName"].ToString() + ", " + table.Rows[i]["FName"].ToString();
}
throw new Exception(Lans.g("Clinics", "Cannot delete clinic because the following patients have procedures using it:") + pats);
}
#endregion
#region Operatories
command = "SELECT OpName FROM operatory "
+ "WHERE ClinicNum =" + POut.Long(clinic.ClinicNum);
table = Db.GetTable(command);
if (table.Rows.Count > 0)
{
string ops = "";
for (int i = 0; i < table.Rows.Count; i++)
{
ops += "\r";
if (i == 15)
{
ops += Lans.g("Clinics", "And") + " " + (table.Rows.Count - i) + " " + Lans.g("Clinics", "others");
break;
}
ops += table.Rows[i]["OpName"].ToString();
}
throw new Exception(Lans.g("Clinics", "Cannot delete clinic because the following operatories are using it:") + ops);
}
#endregion
#region Userod
command = "SELECT UserName FROM userod "
+ "WHERE ClinicNum =" + POut.Long(clinic.ClinicNum);
table = Db.GetTable(command);
if (table.Rows.Count > 0)
{
string userNames = "";
for (int i = 0; i < table.Rows.Count; i++)
{
userNames += "\r";
if (i == 15)
{
userNames += Lans.g("Clinics", "And") + " " + (table.Rows.Count - i) + " " + Lans.g("Clinics", "others");
break;
}
userNames += table.Rows[i]["UserName"].ToString();
}
throw new Exception(Lans.g("Clinics", "Cannot delete clinic because the following Open Dental users are using it:") + userNames);
}
#endregion
#region AlertSub
command = "SELECT DISTINCT UserNum FROM AlertSub "
+ "WHERE ClinicNum =" + POut.Long(clinic.ClinicNum);
table = Db.GetTable(command);
if (table.Rows.Count > 0)
{
List <string> listUsers = new List <string>();
for (int i = 0; i < table.Rows.Count; i++)
{
long userNum = PIn.Long(table.Rows[i]["UserNum"].ToString());
Userod user = Userods.GetUser(userNum);
if (user == null) //Should not happen.
{
continue;
}
listUsers.Add(user.UserName);
}
throw new Exception(Lans.g("Clinics", "Cannot delete clinic because the following Open Dental users are subscribed to it:") + "\r" + String.Join("\r", listUsers.OrderBy(x => x).ToArray()));
}
#endregion
#region UserClinics
command = "SELECT userod.UserName FROM userclinic INNER JOIN userod ON userclinic.UserNum=userod.UserNum "
+ "WHERE userclinic.ClinicNum=" + POut.Long(clinic.ClinicNum);
table = Db.GetTable(command);
if (table.Rows.Count > 0)
{
string users = "";
for (int i = 0; i < table.Rows.Count; i++)
{
if (i > 0)
{
users += ",";
}
users += table.Rows[i][0].ToString();
}
throw new Exception(
Lans.g("Clinics", "Cannot delete clinic because the following users are restricted to this clinic in security setup:") + " " + users);
}
#endregion
//End checking for dependencies.
//Clinic is not being used, OK to delete.
//Delete clinic specific program properties.
command = "DELETE FROM programproperty WHERE ClinicNum=" + POut.Long(clinic.ClinicNum) + " AND ClinicNum!=0"; //just in case a programming error tries to delete an invalid clinic.
Db.NonQ(command);
Crud.ClinicCrud.Delete(clinic.ClinicNum);
}
///<summary>Gets the DataTable to display for treatment finder report</summary>
///<param name="listProviders">Include '0' in the list to get for all providers.</param>
///<param name="listBilling">Include '0' in the list to get for all billing types.</param>
///<param name="listClinicNums">Pass in an empty list to get for all clinics.</param>
public static DataTable GetTreatmentFinderList(bool noIns, bool patsWithAppts, int monthStart, DateTime dateSince, double aboveAmount,
List <long> listProviders, List <long> listBilling, string code1, string code2, List <long> listClinicNums, bool isProcsGeneral)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), noIns, patsWithAppts, monthStart, dateSince, aboveAmount, listProviders, listBilling, code1, code2,
listClinicNums, isProcsGeneral));
}
#if DEBUG
Stopwatch sw = Stopwatch.StartNew();
#endif
DataTable table = new DataTable();
DataRow row;
//columns that start with lowercase are altered for display rather than being raw data.
table.Columns.Add("PatNum");
table.Columns.Add("LName");
table.Columns.Add("FName");
table.Columns.Add("contactMethod");
table.Columns.Add("address");
table.Columns.Add("City");
table.Columns.Add("State");
table.Columns.Add("Zip");
table.Columns.Add("annualMaxInd");
table.Columns.Add("annualMaxFam");
table.Columns.Add("amountUsedInd");
table.Columns.Add("amountUsedFam");
table.Columns.Add("amountPendingInd");
table.Columns.Add("amountPendingFam");
table.Columns.Add("amountRemainingInd");
table.Columns.Add("amountRemainingFam");
table.Columns.Add("treatmentPlan");
table.Columns.Add("carrierName");
table.Columns.Add("clinicAbbr");
List <DataRow> rows = new List <DataRow>();
string command = "";
string joinAnnualMax = "";
string joinCoverageInfo = "";
string joinIndInfo = "";
string joinFamInfo = "";
string subSelectPlanned = "";
string cmdFutureApt = @" AND patient.PatNum NOT IN (
SELECT PatNum FROM appointment WHERE AptStatus=" + POut.Int((int)ApptStatus.Scheduled) + @"
AND AptDateTime>=" + DbHelper.Curdate() + ")";
DateTime renewDate = BenefitLogic.ComputeRenewDate(DateTime.Now, monthStart);
List <long> listPatNums = new List <long>();
if ((!listProviders.Contains(0) || !listBilling.Contains(0) || listClinicNums.Count > 0))
{
string cmdPatients = "SELECT PatNum from patient ";
string patWhere = "";
if (!listProviders.Contains(0))
{
patWhere = " AND patient.PriProv IN (" + string.Join(",", listProviders) + ") ";
}
if (!listBilling.Contains(0))
{
patWhere = " AND patient.BillingType IN (" + string.Join(",", listBilling) + ") ";
}
if (listClinicNums.Count > 0)
{
patWhere += " AND patient.ClinicNum IN (" + string.Join(",", listClinicNums) + ") ";
}
if (!patsWithAppts)
{
patWhere += cmdFutureApt;
}
cmdPatients += "WHERE TRUE " + patWhere;
listPatNums = Db.GetListLong(cmdPatients);
if (listPatNums.Count == 0)
{
return(table);
}
}
joinCoverageInfo = @"
SELECT patplan.PatPlanNum,claimproc.InsSubNum,
SUM(CASE WHEN claimproc.Status=" + POut.Int((int)ClaimProcStatus.NotReceived) + @" AND claimproc.InsPayAmt=0
THEN claimproc.InsPayEst ELSE 0 END) AmtPending,
SUM(CASE WHEN claimproc.Status IN (" + POut.Int((int)ClaimProcStatus.Received) + ","
+ POut.Int((int)ClaimProcStatus.Adjustment) + ","
+ POut.Int((int)ClaimProcStatus.Supplemental) + @"
) THEN claimproc.InsPayAmt ELSE 0 END) AmtUsed
FROM claimproc
INNER JOIN patient ON patient.PatNum=claimproc.PatNum
LEFT JOIN patplan ON patplan.PatNum=claimproc.PatNum
AND patplan.InsSubNum=claimproc.InsSubNum
LEFT JOIN procedurelog pl ON pl.ProcNum=claimproc.ProcNum
LEFT JOIN procedurecode pc ON pc.CodeNum=pl.CodeNum " ;
if (!isProcsGeneral)
{
joinCoverageInfo += @"
LEFT JOIN (
SELECT isub.InsSubNum,
COALESCE(cp.FromCode,pc.ProcCode) AS FromCode,
COALESCE(cp.ToCode,pc.ProcCode) AS ToCode
FROM inssub isub
INNER JOIN benefit b ON b.PlanNum=isub.PlanNum
AND b.BenefitType=" + (int)InsBenefitType.Limitations + @"
AND b.QuantityQualifier=" + (int)BenefitQuantity.None + @"
AND b.TimePeriod IN (" + (int)BenefitTimePeriod.ServiceYear + "," + (int)BenefitTimePeriod.CalendarYear + @")
LEFT JOIN covcat cc ON cc.CovCatNum=b.CovCatNum
LEFT JOIN covspan cp ON cp.CovCatNum=cc.CovCatNum
LEFT JOIN procedurecode pc ON pc.CodeNum=b.CodeNum
WHERE (cc.CovCatNum IS NOT NULL OR b.CodeNum!=0)
)ProcCheck ON ProcCheck.InsSubNum=claimproc.InsSubNum
AND pc.ProcCode BETWEEN ProcCheck.FromCode AND ProcCheck.ToCode " ;
}
joinCoverageInfo += "WHERE claimproc.Status IN (" + (int)ClaimProcStatus.NotReceived + ", " + (int)ClaimProcStatus.Received
+ ", " + (int)ClaimProcStatus.Adjustment + ", " + (int)ClaimProcStatus.Supplemental + ") ";
if (!isProcsGeneral)
{
joinCoverageInfo += "AND ProcCheck.InsSubNum IS NULL ";
}
joinCoverageInfo += "AND claimproc.ProcDate BETWEEN " + POut.Date(renewDate) + @" AND " + POut.Date(renewDate.AddYears(1)) + @" ";
if (listPatNums.Count > 0)
{
joinCoverageInfo += @"AND patient.PatNum IN (" + string.Join(",", listPatNums) + ") ";
}
else if (!patsWithAppts)
{
joinCoverageInfo += cmdFutureApt;
}
joinIndInfo = joinCoverageInfo + " GROUP BY patplan.PatPlanNum ";
joinFamInfo = joinCoverageInfo + " GROUP BY claimproc.InsSubNum ";
subSelectPlanned = @"
(SELECT COALESCE(SUM(ProcFee),0) AmtPlanned
FROM procedurelog " ;
if (code1 != "")
{
subSelectPlanned += "INNER JOIN procedurecode ON procedurecode.CodeNum=procedurelog.CodeNum ";
}
subSelectPlanned += "WHERE ProcStatus=" + (int)ProcStat.TP + " ";
if (code1 != "")
{
subSelectPlanned += "AND procedurecode.ProcCode>='" + POut.String(code1) + "' "
+ " AND procedurecode.ProcCode<='" + POut.String(code2) + "' ";
}
if (dateSince.Year > 1880)
{
subSelectPlanned += "AND procedurelog.DateTP>=" + POut.DateT(dateSince) + " ";
}
subSelectPlanned += "AND PatNum=patient.PatNum ";
subSelectPlanned += "GROUP BY PatNum) ";
joinAnnualMax = @"
SELECT insplan.PlanNum, MAX(CASE WHEN CoverageLevel!=" + POut.Int((int)BenefitCoverageLevel.Family) + @"
THEN MonetaryAmt ELSE -1 END) AnnualMaxInd/*for oracle in case there's more than one*/,
MAX(CASE WHEN CoverageLevel=" + POut.Int((int)BenefitCoverageLevel.Family) + @"
THEN MonetaryAmt ELSE -1 END) AnnualMaxFam/*for oracle in case there's more than one*/
FROM benefit
INNER JOIN insplan ON insplan.PlanNum=benefit.PlanNum
INNER JOIN inssub ON inssub.PlanNum=benefit.PlanNum
INNER JOIN patplan ON patplan.InsSubNum=inssub.InsSubNum
INNER JOIN patient ON patient.PatNum=patplan.PatNum
LEFT JOIN covcat ON benefit.CovCatNum=covcat.CovCatNum
WHERE (covcat.EbenefitCat=" + (int)EbenefitCategory.General + @" OR ISNULL(covcat.EbenefitCat))
AND benefit.BenefitType=" + (int)InsBenefitType.Limitations + @"
AND benefit.MonetaryAmt > 0
AND benefit.QuantityQualifier=" + (int)BenefitQuantity.None + " ";
if (listPatNums.Count > 0)
{
joinAnnualMax += @"AND patient.PatNum IN (" + string.Join(",", listPatNums) + ") ";
}
else if (!patsWithAppts)
{
joinAnnualMax += cmdFutureApt;
}
joinAnnualMax += @"GROUP BY insplan.PlanNum";
command = @"SELECT patient.PatNum, patient.LName, patient.FName,
patient.Email, patient.HmPhone, patient.PreferRecallMethod,
patient.WirelessPhone, patient.WkPhone, patient.Address,
patient.Address2, patient.City, patient.State, patient.Zip,
patient.PriProv, patient.BillingType,
COALESCE(annualMax.AnnualMaxInd,0) ""AnnualMaxInd"",
COALESCE(annualMax.AnnualMaxFam,0) ""AnnualMaxFam"",
IndividualInfo.AmtUsed ""AmountUsedInd"",
FamilyInfo.AmtUsed ""AmountUsedFam"",
IndividualInfo.AmtPending ""AmountPendingInd"",
FamilyInfo.AmtPending ""AmountPendingFam"",
COALESCE(annualMax.AnnualMaxInd,0)-COALESCE(IndividualInfo.AmtUsed,0)-COALESCE(IndividualInfo.AmtPending,0) AS ""$AmtRemainingInd"",
COALESCE(annualMax.AnnualMaxFam,0)-COALESCE(FamilyInfo.AmtUsed,0)-COALESCE(FamilyInfo.AmtPending,0) AS ""$AmtRemainingFam""," +
subSelectPlanned + @"""$TreatmentPlan"", carrier.CarrierName,COALESCE(clinic.Abbr,'Unassigned') clinicAbbr
FROM patient
LEFT JOIN patplan ON patient.PatNum=patplan.PatNum
LEFT JOIN inssub ON patplan.InsSubNum=inssub.InsSubNum
LEFT JOIN insplan ON insplan.PlanNum=inssub.PlanNum
LEFT JOIN carrier ON insplan.CarrierNum=carrier.CarrierNum
LEFT JOIN ("
+ joinIndInfo
+ @")IndividualInfo ON IndividualInfo.PatPlanNum=patplan.PatPlanNum
LEFT JOIN ("
+ joinFamInfo
+ @")FamilyInfo ON FamilyInfo.InsSubNum=inssub.InsSubNum
LEFT JOIN ("
+ joinAnnualMax
+ @") annualMax ON annualMax.PlanNum=inssub.PlanNum
AND (annualMax.AnnualMaxInd>0 OR annualMax.AnnualMaxFam>0)/*may not be necessary*/
LEFT JOIN clinic ON clinic.ClinicNum=patient.ClinicNum
WHERE TRUE
AND patient.PatStatus=" + POut.Int((int)PatientStatus.Patient) + " ";
if (!noIns) //if we don't want patients without insurance
{
command += " AND patplan.Ordinal=1 AND insplan.MonthRenew=" + POut.Int(monthStart) + " ";
}
if (aboveAmount > 0)
{
command += " AND (annualMax.PlanNum IS NULL OR ((annualMax.AnnualMaxInd=-1 OR annualMax.AnnualMaxInd-COALESCE(IndividualInfo.AmtUsed,0) > "
+ POut.Double(aboveAmount) + @")
AND (annualMax.AnnualMaxFam=-1 OR annualMax.AnnualMaxFam-COALESCE(FamilyInfo.AmtUsed,0) > " + POut.Double(aboveAmount) + "))) ";
}
if (listPatNums.Count > 0)
{
command += " AND patient.PatNum IN (" + string.Join(",", listPatNums) + ") ";
}
else if (!patsWithAppts)
{
command += cmdFutureApt;
}
command += @"HAVING $TreatmentPlan > 0 ";
command += @"ORDER BY $TreatmentPlan DESC";
DataTable rawtable = Db.GetTable(command);
#if DEBUG
sw.Stop();
Console.WriteLine("Finishing retreiving query: {0}", sw.ElapsedMilliseconds);
sw = Stopwatch.StartNew();
#endif
ContactMethod contmeth;
for (int i = 0; i < rawtable.Rows.Count; i++)
{
row = table.NewRow();
row["PatNum"] = PIn.Long(rawtable.Rows[i]["PatNum"].ToString());
row["LName"] = rawtable.Rows[i]["LName"].ToString();
row["FName"] = rawtable.Rows[i]["FName"].ToString();
contmeth = (ContactMethod)PIn.Long(rawtable.Rows[i]["PreferRecallMethod"].ToString());
if (contmeth == ContactMethod.None)
{
if (PrefC.GetBool(PrefName.RecallUseEmailIfHasEmailAddress)) //if user only wants to use email if contact method is email
{
if (rawtable.Rows[i]["Email"].ToString() != "")
{
row["contactMethod"] = rawtable.Rows[i]["Email"].ToString();
}
else
{
row["contactMethod"] = Lans.g("FormRecallList", "Hm:") + rawtable.Rows[i]["HmPhone"].ToString();
}
}
else
{
row["contactMethod"] = Lans.g("FormRecallList", "Hm:") + rawtable.Rows[i]["HmPhone"].ToString();
}
}
else if (contmeth == ContactMethod.HmPhone)
{
row["contactMethod"] = Lans.g("FormRecallList", "Hm:") + rawtable.Rows[i]["HmPhone"].ToString();
}
else if (contmeth == ContactMethod.WkPhone)
{
row["contactMethod"] = Lans.g("FormRecallList", "Wk:") + rawtable.Rows[i]["WkPhone"].ToString();
}
else if (contmeth == ContactMethod.WirelessPh)
{
row["contactMethod"] = Lans.g("FormRecallList", "Cell:") + rawtable.Rows[i]["WirelessPhone"].ToString();
}
else if (contmeth == ContactMethod.Email)
{
row["contactMethod"] = rawtable.Rows[i]["Email"].ToString();
}
else if (contmeth == ContactMethod.Mail)
{
row["contactMethod"] = Lans.g("FormRecallList", "Mail");
}
else if (contmeth == ContactMethod.DoNotCall || contmeth == ContactMethod.SeeNotes)
{
row["contactMethod"] = Lans.g("enumContactMethod", contmeth.ToString());
}
row["address"] = rawtable.Rows[i]["Address"].ToString();
if (rawtable.Rows[i]["Address2"].ToString() != "")
{
row["address"] += "\r\n" + rawtable.Rows[i]["Address2"].ToString();
}
row["City"] = rawtable.Rows[i]["City"].ToString();
row["State"] = rawtable.Rows[i]["State"].ToString();
row["Zip"] = rawtable.Rows[i]["Zip"].ToString();
row["annualMaxInd"] = (PIn.Double(rawtable.Rows[i]["AnnualMaxInd"].ToString())).ToString("N");
row["annualMaxFam"] = (PIn.Double(rawtable.Rows[i]["AnnualMaxFam"].ToString())).ToString("N");
row["amountUsedInd"] = (PIn.Double(rawtable.Rows[i]["AmountUsedInd"].ToString())).ToString("N");
row["amountUsedFam"] = (PIn.Double(rawtable.Rows[i]["AmountUsedFam"].ToString())).ToString("N");
row["amountPendingInd"] = (PIn.Double(rawtable.Rows[i]["AmountPendingInd"].ToString())).ToString("N");
row["amountPendingFam"] = (PIn.Double(rawtable.Rows[i]["AmountPendingFam"].ToString())).ToString("N");
row["amountRemainingInd"] = (PIn.Double(rawtable.Rows[i]["$AmtRemainingInd"].ToString())).ToString("N");
row["amountRemainingFam"] = (PIn.Double(rawtable.Rows[i]["$AmtRemainingFam"].ToString())).ToString("N");
row["treatmentPlan"] = (PIn.Double(rawtable.Rows[i]["$TreatmentPlan"].ToString())).ToString("N");
row["carrierName"] = rawtable.Rows[i]["CarrierName"].ToString();
row["clinicAbbr"] = rawtable.Rows[i]["clinicAbbr"].ToString();
rows.Add(row);
}
for (int i = 0; i < rows.Count; i++)
{
table.Rows.Add(rows[i]);
}
#if DEBUG
sw.Stop();
Console.WriteLine("Finished Filling query result: {0}", sw.ElapsedMilliseconds);
#endif
return(table);
}
