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