private void Search(string sql) { _isSearching = true; claim c = new claim(); DataTable dt = c.Search(sql); List <string> addedCompanies = new List <string>(); List <CompanyDropDownItem> combosToAdd = new List <CompanyDropDownItem>(); foreach (DataRow aClaim in dt.Rows) { string CompanyName = aClaim["NAME"].ToString(); if (addedCompanies.Contains(CompanyName)) { combosToAdd[combosToAdd.Count - 1].ClaimIDs.Add((int)aClaim["CLAIMID"]); } else { addedCompanies.Add(CompanyName); CompanyDropDownItem newItem = new CompanyDropDownItem((int)aClaim["COMPANYID"], aClaim["NAME"].ToString(), (int)aClaim["CLAIMID"]); combosToAdd.Add(newItem); } } List <CompanyDropDownItem> selectedItems = new List <CompanyDropDownItem>(); foreach (CompanyDropDownItem anItem in chkCarrierList.CheckedItems) { selectedItems.Add(anItem); } chkCarrierList.Items.Clear(); foreach (CompanyDropDownItem toAdd in combosToAdd) { chkCarrierList.Items.Add(toAdd, true); } for (int i = 0; i < chkCarrierList.Items.Count; i++) { CompanyDropDownItem anItem = (CompanyDropDownItem)chkCarrierList.Items[i]; foreach (CompanyDropDownItem checkedItem in selectedItems) { if (anItem.CompanyID == checkedItem.CompanyID) { chkCarrierList.SetItemChecked(i, true); } } } if ((chkCarrierList.Items.Count > 0) && (chkCarrierList.CheckedItems.Count == 0)) { chkCarrierList.SetItemChecked(0, true); } UpdateDisplay(); }
/// <summary> /// Searches the given list to see whether a claim should substitute another provider's information based off of the information /// in the passed claim. /// </summary> /// <param name="toSearch">The claim to search for a restriction on</param> /// <param name="restrictionsList">The set of restrictions to search through</param> /// <returns></returns> public static claim FindEligibilityRestrictions(claim toSearch, List <provider_eligibility_restrictions> restrictionsList) { claim switchToClaim = null; foreach (provider_eligibility_restrictions restriction in restrictionsList) { bool valid = true; if (toSearch.date_of_service > restriction.start_date) { if (restriction.end_date.HasValue) { if (toSearch.date_of_service > restriction.end_date) { valid = false; } } if (restriction.provider_id != toSearch.doctor_provider_id) { valid = false; } else { // Check sql for applicability to rules // unfortunately have to create a huge sql statement // Should look roughly like this: // SELECT id FROM companies WHERE name NOT LIKE '1' AND name NOT LIKE '2' AND (name like '%a%' OR name like 'b') List <int> providerIDs = new List <int>(); string finalSQL = GenerateCompanySQL("id", ConvertEligibilityListToStringArray(restriction.GetLinkedCompanyFilters())); DataTable matches = toSearch.Search(finalSQL); foreach (DataRow aMatch in matches.Rows) { providerIDs.Add((int)aMatch["id"]); } if (!providerIDs.Contains(toSearch.company_id)) { valid = false; } } } else { valid = false; } if (valid) { switchToClaim = FindClaimByProviderID(restriction.switch_to_provider_id); } } return(switchToClaim); }
/// <summary> /// Looks to see if a call has the particular claim already in the call_claim_ids table /// (for calls that accept multiple claims) /// </summary> /// <param name="LinkedClaim"></param> /// <returns></returns> private bool CallHasClaim(claim LinkedClaim) { if (LinkedClaim.Search("SELECT * FROM call_claim_ids WHERE call_id = " + id + " AND claim_id = " + LinkedClaim.id).Rows.Count > 0) { return(true); } else { return(false); } }
internal List <string> GetCompanyCriteria() { List <string> toReturn = new List <string>(); claim workingClaim = new claim(); DataTable matches = workingClaim.Search("SELECT * FROM provider_eligibility_companies WHERE per_id = " + id); foreach (DataRow aRow in matches.Rows) { toReturn.Add(aRow["restriction_text"].ToString()); } return(toReturn); }
private int FindClaim(string claimID, string claimDB) { claim toFind = new claim(); DataTable results = toFind.Search("SELECT id FROM claims WHERE claimidnum = " + claimID + " AND claimdb = " + claimDB); if (results.Rows.Count > 0) { return(System.Convert.ToInt32(results.Rows[0]["id"].ToString())); } else { return(0); } }
private List <insurance_company_groups_filters> GetLinkedCompanyFilters() { List <insurance_company_groups_filters> toReturn = new List <insurance_company_groups_filters>(); insurance_company_groups_filters icgf; claim c = new claim(); DataTable dt = c.Search("SELECT * FROM insurance_company_groups_filters WHERE icg_id = " + insurance_group_id); foreach (DataRow aMatch in dt.Rows) { icgf = new insurance_company_groups_filters(); icgf.Load(aMatch); toReturn.Add(icgf); } return(toReturn); }
public static claim FindClaimByProviderID(string ProviderID) { claim toReturn = new claim(); DataTable matches = toReturn.Search("SELECT TOP 1 * FROM claims WHERE doctor_provider_id = '" + ProviderID + "' "); if (matches.Rows.Count > 0) { toReturn.Load(matches.Rows[0]); return(toReturn); } else { return(null); } }
internal static List <claim> OverrideProviderList() { claim searchObject = new claim(); List <claim> toReturn = new List <claim>(); DataTable providers = searchObject.Search("SELECT DISTINCT(doctor_provider_id), doctor_first_name, doctor_last_name FROM claims " + "WHERE doctor_provider_id IS NOT Null AND doctor_provider_id != '' AND doctor_provider_id NOT LIKE 'H%' " + "AND doctor_provider_id NOT LIKE 'X%' ORDER BY doctor_provider_id"); foreach (DataRow aRow in providers.Rows) { searchObject = new claim(); searchObject.Load(aRow); toReturn.Add(searchObject); } return(toReturn); }
/// <summary> /// Returns false if no local copy was found /// </summary> /// <param name="dentrixID"></param> /// <param name="dentrixDB"></param> /// <returns></returns> internal bool LoadWithDentrixIDs(string dentrixID, string dentrixDB) { bool claimFound = true; claim c = new claim(); c.claimidnum = dentrixID; c.claimdb = dentrixDB; DataTable matches = c.Search(); if (matches.Rows.Count > 0) { Load(matches.Rows[0]); } else { claimFound = false; } return(claimFound); }
private claim FindClaim(DataRow anImportRow, claim.ClaimTypes claimType) { string importID = anImportRow[dms.claim_id_column].ToString(); string importDB = anImportRow[dms.claim_db_column].ToString(); try { claim c = new claim(); c.claimidnum = importID; c.claimdb = importDB; DataTable matches = c.Search(); if (matches.Rows.Count != 0) { c.Load(matches.Rows[0]); } else { // ****** Finding recreated claims ************* // Above, we checked for the common ID/DB match // Here we try to find if a claim that is not in Tracker has been recreated // We match by all fields minus 1 - if two primary fields have been changed // Tracker can't track it // If we find a possible match, we check to see that the current claim isn't in Dentrix // to avoid false positives bool possibleMatchFound = false; bool claimInDentrix = true; claim workingClaim = new claim(); DateTime?dateOfService = null; try { dateOfService = Convert.ToDateTime(anImportRow["PLDATE"]); } catch { } string lastName = anImportRow["PatLastName"].ToString(); string firstName = anImportRow["PatFirstName"].ToString(); decimal amount = Convert.ToDecimal(anImportRow["TOTAL"]); // stored as cents string doctorFirstName = anImportRow["DoctorFirstName"].ToString(); string doctorLastName = anImportRow["DoctorLastName"].ToString(); string subscriberInsuranceID = anImportRow["ID_NUM_ON_CLAIM"].ToString(); string matchingClaimInfo = matchingClaimInfo = string.Format("Patient: {0}, Doctor: {1}, Total: {2}, DOS: {3}, Ins ID: {4}, ID: {5}, Type: {6}", firstName + " " + lastName, doctorFirstName + " " + doctorLastName, amount, dateOfService, subscriberInsuranceID, importID + " | " + importDB, claimType.ToString()); int?newClaimID = null; // Search for a patient match first. If the patient doesn't match try to match on the other two fields workingClaim.patient_first_name = firstName; workingClaim.patient_last_name = lastName; workingClaim.open = 1; DataTable patientMatches = workingClaim.Search(); if (patientMatches.Rows.Count > 0) { // Iterate through all rows where the patient data matches foreach (DataRow aRow in patientMatches.Rows) { int numMatchesForclaim = 0; workingClaim.Load(aRow); if (workingClaim.amount_of_claim * 100 == amount) { numMatchesForclaim++; } if (workingClaim.doctor_first_name == doctorFirstName && workingClaim.doctor_last_name == doctorLastName) { numMatchesForclaim++; } if (workingClaim.date_of_service == dateOfService) { numMatchesForclaim++; } if (workingClaim.subscriber_alternate_number == subscriberInsuranceID) { numMatchesForclaim++; } if (workingClaim.claim_type == claimType) { numMatchesForclaim++; } if (numMatchesForclaim >= 4) { // Found a claim that appears to match newClaimID = workingClaim.id; possibleMatchFound = true; break; } } } else { // Look for a match on date of service, amount, provider, insurance id, and type workingClaim = new claim(); workingClaim.open = 1; workingClaim.amount_of_claim = amount / 100; workingClaim.doctor_first_name = doctorFirstName; workingClaim.doctor_last_name = doctorLastName; workingClaim.subscriber_alternate_number = subscriberInsuranceID; workingClaim.claim_type = claimType; string searchSQL = workingClaim.SearchSQL + string.Format(" AND DATEDIFF(d, [date_of_service], '{0}') = 0", dateOfService.GetValueOrDefault().ToShortDateString()); DataTable otherMatches = workingClaim.Search(searchSQL); if (otherMatches.Rows.Count > 0) { newClaimID = (int)otherMatches.Rows[0]["id"]; possibleMatchFound = true; } } if (possibleMatchFound) { // Check Dentrix to verify that the current ID/DB can't be found if (newClaimID.HasValue && ClaimTrackerCommon.ClaimExists(new claim(newClaimID.Value))) // No match found { // Log this temporarily for recordkeeping claimInDentrix = true; AddReportMessage("Found a possible claim to merge, but the claim exists in Dentrix. Did not merge claim..." + matchingClaimInfo, true); LoggingHelper.Log("Tried to merge a claim, but it was found in Dentrix. Claim info:\n" + matchingClaimInfo, LogSeverity.Information); } else { claimInDentrix = false; } } if (!claimInDentrix && possibleMatchFound) { // Already loaded claim, should have "correct" data c.Load(newClaimID.GetValueOrDefault(0)); AddReportMessage(string.Format("Merged a claim in Dentrix with an existing claim tracker claim. The claim's old ID/DB is {0}/{1}. The matching claim information is {2}", c.claimidnum, c.claimdb, matchingClaimInfo), true); c.claimidnum = importID; c.claimdb = importDB; LoggingHelper.Log("Successfully merged a claim. \n" + matchingClaimInfo, LogSeverity.Information); } else { AddReportMessage("New claim imported - " + matchingClaimInfo); c.created_by = "Automatic Database Importer"; c.updated_on = null; } } return(c); } catch (Exception err) { // Somewhat dangerous, but it ensures that the import won't crash LoggingHelper.Log("Error in frmImportData.FindClaim", LogSeverity.Critical, err, false); claim c = new claim(); c.claimidnum = importID; c.claimdb = importDB; return(c); } }
private void ImportThread_DoWork(object sender, DoWorkEventArgs e) { claim workingClaim = new claim(); DataTable importData = new DataTable(); DataTable importDataSecondaries = new DataTable(); DataTable importDataPredeterms = new DataTable(); DataTable importDataSecondaryPredeterms = new DataTable(); system_options.SetImportFlag(true); primaryClaimCount = 0; secondaryClaimCount = 0; predetermClaimCount = 0; secondaryPredetermClaimCount = 0; closedClaimCount = 0; OleDbConnection oConnect; UpdateProgressBar(50, "Initiating Remote Connection..."); #region Initiate Connection, Get Data try { oConnect = new OleDbConnection(dms.GetConnectionString(true)); } catch (Exception err) { CreateLogFile(err.ToString()); Updateimporterror(true); LoggingHelper.Log("An error occurred getting the connection string for a new connection in frmImportData.Import", LogSeverity.Error, err, false); e.Cancel = true; return; } OleDbDataAdapter oAdapter; // Use Connection object for the DataAdapter to retrieve all tables from selected Database try { oConnect.Open(); } catch (Exception err) { CreateLogFile(err.ToString()); Updateimporterror(true); LoggingHelper.Log("Could not connect to the database in frmImportdata.Import", LogSeverity.Error, err, false); e.Cancel = true; return; } try { UpdateProgressBar(50, "Querying remote database (Standard)..."); // ************* Standard Claims oAdapter = new OleDbDataAdapter(PrepareSQL(dms.sqlstatement, changesOnly), oConnect); oAdapter.SelectCommand.CommandTimeout = System.Convert.ToInt32(nmbTimeout.Value); oAdapter.Fill(importData); UpdateProgressBar(10, "Querying remote database (Secondary)..."); // ************** Secondaries if (dms.sqlstatementsecondaries != "") { oAdapter = new OleDbDataAdapter(PrepareSQL(dms.sqlstatementsecondaries, changesOnly), oConnect); oAdapter.SelectCommand.CommandTimeout = System.Convert.ToInt32(nmbTimeout.Value); oAdapter.Fill(importDataSecondaries); } UpdateProgressBar(10, "Querying remote database (Predeterms)..."); // *************** Predeterms if (dms.sqlstatementpredeterms != "") { oAdapter = new OleDbDataAdapter(PrepareSQL(dms.sqlstatementpredeterms, changesOnly), oConnect); oAdapter.SelectCommand.CommandTimeout = System.Convert.ToInt32(nmbTimeout.Value); oAdapter.Fill(importDataPredeterms); } UpdateProgressBar(10, "Querying remote database (Secondary Predeterms)..."); // *************** Predeterms if (dms.sqlstatementsecondarypredeterms != "") { oAdapter = new OleDbDataAdapter(PrepareSQL(dms.sqlstatementsecondarypredeterms, changesOnly), oConnect); oAdapter.SelectCommand.CommandTimeout = System.Convert.ToInt32(nmbTimeout.Value); oAdapter.Fill(importDataSecondaryPredeterms); } } catch (Exception err) { CreateLogFile(err.ToString()); Updateimporterror(true); LoggingHelper.Log("Error with SQL statement or connection in frmImportData.Import", LogSeverity.Error, err); MessageBox.Show(this, "There was an error with your SQL statement or with your connection.\n\n" + err.Message, "Error retrieving data"); CancelImport(); return; } #endregion data_mapping_schema_data dmsd = new data_mapping_schema_data(); dmsd.schema_id = dms.id; DataTable dataForSchema = dmsd.Search(); // Generate our list of objects one time, and then use them for each iteration of rows List <data_mapping_schema_data> allMappedSchemaData = new List <data_mapping_schema_data>(); foreach (DataRow aMapping in dataForSchema.Rows) { // For every row, need to get the data for every field dmsd = new data_mapping_schema_data(); dmsd.Load(aMapping); allMappedSchemaData.Add(dmsd); } UpdateProgressBar(100, "Importing data..."); if (okToZap) { company cmp = new company(); cmp.Zap(); workingClaim.Zap(); call aCall = new call(); aCall.Zap(); company_contact_info info = new company_contact_info(); info.Zap(); procedure p = new procedure(); p.Zap(); choice c = new choice(); c.Zap(); notes n = new notes(); n.Zap(); claim_batch cb = new claim_batch(); cb.Zap(); batch_claim_list bcl = new batch_claim_list(); bcl.Zap(); } else { if (!changesOnly) { workingClaim.MarkAllImportsUpdated(false); } } // Apply incremental updates to progress bar int currentRow = 0; totalRows = importData.Rows.Count + importDataSecondaries.Rows.Count + importDataPredeterms.Rows.Count + importDataSecondaryPredeterms.Rows.Count; decimal exactIncrementAmount; if (totalRows > 0) { exactIncrementAmount = 500m / totalRows; } else { exactIncrementAmount = 500m; } decimal incrementCounter = 0; int increment; if (exactIncrementAmount < 1) { increment = 1; } else { increment = Convert.ToInt32(Math.Truncate(exactIncrementAmount)); } string lastClaimID = ""; claim aClaim = new claim(); company aCompany = new company(); company_contact_info anInfo = new company_contact_info(); procedure aProcedure = new procedure(); for (int p = 0; p < 4; p++) { claim.ClaimTypes ct; DataTable thisImport; switch (p) { case 0: thisImport = importData; ct = claim.ClaimTypes.Primary; UpdateLabels(0); break; case 1: thisImport = importDataSecondaries; ct = claim.ClaimTypes.Secondary; UpdateLabels(1); break; case 2: thisImport = importDataPredeterms; ct = claim.ClaimTypes.Predeterm; UpdateLabels(2); break; default: thisImport = importDataSecondaryPredeterms; UpdateLabels(3); ct = claim.ClaimTypes.SecondaryPredeterm; break; } // Have data at this point, need to tie them to the internal mapping schema data foreach (DataRow anImportRow in thisImport.Rows) { string newID = anImportRow[dms.claim_id_column].ToString(); string newDB = anImportRow[dms.claim_db_column].ToString(); bool isOnlyProcedureData; if (newID == lastClaimID) { // We're only dealing with the import of "some" data isOnlyProcedureData = true; } else { if (ct == claim.ClaimTypes.Primary) { primaryClaimCount++; } else if (ct == claim.ClaimTypes.Secondary) { secondaryClaimCount++; } else if (ct == claim.ClaimTypes.Predeterm) { predetermClaimCount++; } else { secondaryPredetermClaimCount++; } UpdateTypeCount(); aClaim = FindClaim(anImportRow, ct); aCompany = FindCompany(anImportRow[dms.company_namecolumn].ToString()); anInfo = FindContactInfo(anImportRow["Ins_Co_Street1"].ToString(), aCompany.id, anImportRow["Ins_Co_Phone"].ToString()); lastClaimID = newID; aClaim.ClearClaimProcedures(); isOnlyProcedureData = false; // Check for "X" in provider field try { if (aClaim.doctor_provider_id.StartsWith("X")) { AddStatus(string.Format("The claim for patient {0} on {1} uses an X provider ({2})", aClaim.PatientName, aClaim.DatesOfServiceString(), aClaim.doctor_provider_id), true, true); } } catch (Exception err) { CreateLogFile(err.ToString()); Updateimporterror(true); LoggingHelper.Log(err, false); } } aProcedure = FindProcedure(anImportRow["PROC_LOGID"].ToString()); if (CommonFunctions.DBNullToString(anImportRow["DATERECEIVED"]) == "") { aClaim.open = 1; } else if (((DateTime)anImportRow["DATERECEIVED"]).Year == 1753) { aClaim.open = 1; } else { aClaim.open = 0; UpdateStatusHistory(aClaim); } foreach (data_mapping_schema_data aMappedData in allMappedSchemaData) { // We do a check for is only procedure data to speed up processing // It makes the code a little messier. if (isOnlyProcedureData) { // If we're only importing the procedure data, none of the other information is important if ((aMappedData.LinkedField.table_name == "claims") || (aMappedData.LinkedField.table_name == "companies") || (aMappedData.LinkedField.table_name == "company_contact_info")) { // Ignore } else if (aMappedData.LinkedField.table_name == "procedures") { if (aMappedData.LinkedField.field_name == "surf_string") { aProcedure[aMappedData.LinkedField.field_name] = CommonFunctions.RemoveNonPrintableCharacters(anImportRow[aMappedData.mapped_to_text].ToString()); } else if (aMappedData.LinkedField.field_name == "claim_id") { aProcedure["claim_id"] = lastClaimID; } else { aProcedure[aMappedData.LinkedField.field_name] = anImportRow[aMappedData.mapped_to_text]; } } else { LoggingHelper.Log("Uninitialized table name in frmImportData.Import", LogSeverity.Critical, new Exception("Uninitialized table name in import procedure."), true); } } else { // This is a new claim - we need to get the data for every field if (aMappedData.LinkedField.table_name == "claims") { aClaim[aMappedData.LinkedField.field_name] = anImportRow[aMappedData.mapped_to_text]; } else if (aMappedData.LinkedField.table_name == "companies") { if (aMappedData.mapped_to_text != dms.company_namecolumn) { aCompany[aMappedData.LinkedField.field_name] = anImportRow[aMappedData.mapped_to_text]; } } else if (aMappedData.LinkedField.table_name == "company_contact_info") { anInfo[aMappedData.LinkedField.field_name] = anImportRow[aMappedData.mapped_to_text]; } else if (aMappedData.LinkedField.table_name == "procedures") { if (aMappedData.LinkedField.field_name == "surf_string") { aProcedure[aMappedData.LinkedField.field_name] = CommonFunctions.RemoveNonPrintableCharacters(anImportRow[aMappedData.mapped_to_text].ToString()); } else { aProcedure[aMappedData.LinkedField.field_name] = anImportRow[aMappedData.mapped_to_text]; } } else { LoggingHelper.Log("Uninitialized table name in frmImport.Import", LogSeverity.Critical); throw new Exception("Uninitialized table name in import procedure."); } } } aCompany.Save(); anInfo.company_id = aCompany.id; if (CommonFunctions.DBNullToZero(anInfo["order_id"]) == 0) { anInfo.order_id = anInfo.GetNextOrderID(); } anInfo.Save(); aClaim.company_id = aCompany.id; aClaim.company_address_id = anInfo.order_id; aClaim["import_update_flag"] = true; aClaim.Save(); if (p == 0 || p == 2 || p == 3) // Only update the id if this is the primary claim or a predeterm { aProcedure.claim_id = aClaim.id; } aProcedure.Save(); currentRow++; if (Math.Truncate(incrementCounter + exactIncrementAmount) != Math.Truncate(incrementCounter)) { UpdateProgressBar(increment, string.Format("{0} / {1} procedures completed...", currentRow, totalRows), false); } incrementCounter += exactIncrementAmount; } } if (changesOnly) { // Grab all the deleted claims and mark them as closed here // Add a note that they have been deleted, I guess string deletedClaimsSQL = "SELECT CLAIMID, CLAIMDB " + "FROM AUDIT_DDB_CLAIM " + "WHERE N_CLAIMID is null " + "AND CLAIMID is not null AND CLAIMDB is not null " + "AND date_changed >= '" + lastWrite.ToString("G") + "'"; DataTable deletedClaims = new DataTable(); OleDbCommand cmd = new OleDbCommand(deletedClaimsSQL, oConnect); cmd.CommandTimeout = 90; oAdapter = new OleDbDataAdapter(cmd); oAdapter.Fill(deletedClaims); UpdateProgressBar(5, "Updating Local Status for Deleted Claims..."); foreach (DataRow aDeletedClaim in deletedClaims.Rows) { // Close the claims DataTable matches = aClaim.Search("SELECT * FROM claims WHERE claimidnum = '" + aDeletedClaim["claimid"] + "' and claimdb = '" + aDeletedClaim["claimdb"] + "'"); if (matches.Rows.Count > 0) { // This should honestly not load every claim aClaim = new claim(); aClaim.Load(matches.Rows[0]); aClaim.open = 0; aClaim.Save(); UpdateStatusHistory(aClaim); closedClaimCount++; } } } else { closedClaimCount = workingClaim.CloseClaimsWithoutUpdate(); } UpdateLabels(4); workingClaim.FixRevisitDateAfterImport(); }