//Adds a new administrator with the specified userID and privilege
public AdministratorQuery addAdmin(AdministratorQuery s)
{
try
{
using (conferenceadminContext context = new conferenceadminContext())
{
//Get privilege name
s.privilege = (from p in context.privileges
where p.privilegesID == s.privilegeID
select p.privilegestType).FirstOrDefault();
//Check if user is Member. Note: In the administrator tab the user has already been checked for membership.
var userInfo = (from user in context.users
where user.membership.email == s.email
select user).FirstOrDefault();
if (userInfo != null && s.privilege != null)
{
//User exists
s.userID = userInfo.userID;
s.firstName = userInfo.firstName;
s.lastName = userInfo.lastName;
s.email = userInfo.membership.email;
//Check if newAdmin has already a privilege
var checkAdmin = (from admin in context.claims
where admin.userID == s.userID && admin.privilege.privilegestType != "Evaluator" && admin.deleted != true
select admin).FirstOrDefault();
if (checkAdmin != null)
{
return null;
}
else
{
//Check if user has had the privilege before
var adminPrivilege = (from admin in context.claims
where admin.userID == s.userID && admin.privilege.privilegestType == s.privilege
select admin).FirstOrDefault();
if (adminPrivilege != null)
{
adminPrivilege.deleted = false;
context.SaveChanges();
}
else
{
//Add admin
claim newAdmin = new claim();
newAdmin.privilegesID = s.privilegeID;
newAdmin.deleted = false;
newAdmin.userID = s.userID;
context.claims.Add(newAdmin);
context.SaveChanges();
}
try { sendEmailConfirmation(s.email, s.privilege); }
catch (Exception ex)
{
Console.Write("AdminManager.sendnewAdminEmail error " + ex);
return null;
}
if (s.privilege != "Finance")
{
EvaluatorManager evaluator = new EvaluatorManager();
evaluator.addEvaluator(s.email);
}
}
s.privilege = s.privilege == "Admin" ? "Administrator" : s.privilege == "CommitteEvaluator" ? "Committee Evaluator" : s.privilege;
return s;
}
return null;
}
}
catch (Exception ex)
{
Console.Write("AdminManager.addAdmin error " + ex);
return null;
}
}
private void FillText(TextBox txtForProvider, claim claim)
{
txtForProvider.Text = claim.doctor_provider_id + " - " + claim.DoctorName;
}
public UnsubmittedSearchResult(DateTime?createdOn, string patientName, string providerInfo, string claimType, string companyName,
decimal claimAmount, string handling, string claimBatch, string claimID, string claimDB, claim claimObject)
{
_CreatedOn = createdOn;
_PatientName = patientName;
_ClaimType = claimType;
_CompanyName = companyName;
_ClaimAmount = claimAmount;
_Handling = handling;
_claimBatch = claimBatch;
_ClaimID = claimID;
_ClaimDB = claimDB;
_ClaimObject = claimObject;
_providerInfo = providerInfo;
}
void WorkerThread_DoWork(object sender, DoWorkEventArgs e)
{
claim workingClaim = new claim();
DataTable importData = new DataTable();
DataTable importDataSecondaries = new DataTable();
DataTable importDataPredeterms = new DataTable();
OleDbConnection oConnect;
appointment_audit aa;
UpdateProgressBar(0, "Starting Import");
#region Initiate Connection, Get Data
try
{
oConnect = new OleDbConnection(data_mapping_schema.GetDefaultSchema.GetConnectionString(true));
}
catch (Exception err)
{
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)
{
LoggingHelper.Log("Could not connect to the database in frmImportAppointmentAuditdata.Import", LogSeverity.Error, err, false);
e.Cancel = true;
return;
}
DateTime lastImport = system_options.GetLastAppointmentAuditImportDate();
DateTime importStart = DateTime.Now;
system_options.SetLastAppointmentAuditImportDate(importStart);
try
{
string sql;
// ************* Start import
if (chkMergeData.Checked)
{
sql = string.Format("SELECT * FROM AUDIT_DDB_APPT_BASE" +
" WHERE DATE_CHANGED > '{0}'", CommonFunctions.ToSQLServerDateTime(lastImport));
}
else
{
UpdateStatusBox("Clearing existing appointment audit information for full import...");
UpdateProgressBar(0, "Clearing existing appointments audit data...");
aa = new appointment_audit();
aa.Zap();
sql = string.Format("SELECT * FROM AUDIT_DDB_APPT_BASE" +
" WHERE DATE_CHANGED > '{0}'",
DateTime.Now.AddDays(Convert.ToInt32(nmbFullImportDuration.Value * -1)).ToShortDateString());
}
UpdateStatusBox("Starting the following query: \r\n" + sql);
UpdateProgressBar(50, "Querying remote database...");
oAdapter = new OleDbDataAdapter(sql, oConnect);
oAdapter.SelectCommand.CommandTimeout = System.Convert.ToInt32(nmbTimeout.Value);
oAdapter.Fill(importData);
}
catch (Exception err)
{
LoggingHelper.Log("Error with SQL statement or connection in frmImportData.Import", LogSeverity.Error, err);
e.Cancel = true;
CancelImport();
return;
}
UpdateProgressBar(200, "Importing data...");
// **** Data retrieved, put it into our local table
totalRows = importData.Rows.Count;
decimal exactIncrementAmount;
decimal incrementCounter = 0;
int increment;
if (totalRows > 0)
{
exactIncrementAmount = 500m / totalRows;
}
else
{
exactIncrementAmount = 500m;
}
if (exactIncrementAmount < 1)
{
increment = 1;
}
else
{
increment = Convert.ToInt32(Math.Truncate(exactIncrementAmount));
}
int i = 0;
UpdateStatusBox("Starting import process for " + importData.Rows.Count + " rows.");
// Have data at this point, need to tie them to the internal mapping schema data
foreach (DataRow anImportRow in importData.Rows)
{
i++;
// need new data object for this table type
aa = new appointment_audit();
foreach (DataColumn aColumn in importData.Columns)
{
aa[aColumn.ColumnName] = anImportRow[aColumn.ColumnName];
}
aa.change_type = aa.DiscoverChangeType();
aa.Save();
incrementCounter += exactIncrementAmount;
if (incrementCounter >= increment)
{
UpdateProgressBar(increment, string.Format("Importing changes {0}/{1}", i, totalRows));
incrementCounter -= increment;
}
}
#endregion
}
//GetClaimFormCapturedData for this specific claim:
public string getClaimCapturedData(int ClaimID)
{
claim cl = (from c in db.claims where c.Claim_ID == ClaimID select c).SingleOrDefault();
return(cl.capturedClaimFormDataJson);
}
public static 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);
}
}
public ActionResult Create(claim cl)
{
cs.Add(cl);
cs.Commit();
return(RedirectToAction("../claim/create"));
}
private string GetClaimCountText(string where, claim workingObject)
{
return("(" + workingObject.Search("SELECT COUNT(*) FROM claims WHERE " + where).Rows[0][0].ToString() + ")");
}
public void DisplayClaim(claim toDisplay, bool showZeroAnswer)
{
rtbMain.Text = "";
List <ClaimDisplayData> displayData = new List <ClaimDisplayData>();
// Get Call History
foreach (call aCall in toDisplay.GetPastCalls(!showZeroAnswer))
{
ClaimDisplayDataCall cd = new ClaimDisplayDataCall();
cd.ActionTime = aCall.created_on.Value;
cd.CallDuration = aCall.DurationSeconds;
cd.User = aCall.operatordata;
if (aCall.call_status > 0)
{
cd.LinkedStatus = aCall.LinkedStatus.text;
}
else
{
cd.LinkedStatus = "";
}
string callText = "";
string categoryText = "";
foreach (choice aChoice in aCall.GetCallChoices())
{
string toAdd = "";
List <string> parentCatNames = new List <string>();
question workingQuestion;
if (aChoice.LinkedQuestion.parent > 0)
{
workingQuestion = aChoice.LinkedQuestion;
parentCatNames.Insert(0, workingQuestion.text);
while (workingQuestion.parent > 0)
{
workingQuestion = workingQuestion.ParentQuestion;
if (!workingQuestion.is_classification)
{
parentCatNames.Insert(0, workingQuestion.text);
}
}
}
else
{
parentCatNames.Insert(0, aChoice.LinkedQuestion.text);
}
foreach (string aCatName in parentCatNames)
{
if (toAdd == string.Empty)
{
toAdd = aCatName;
}
else
{
toAdd += " - " + aCatName;
}
}
if (callText != "")
{
callText += "\\line ";
}
callText += string.Format("{0}: {1}", toAdd, aChoice.answer);
}
foreach (notes aNote in aCall.GetNotes())
{
callText += "\\line Note: " + aNote.Note;
}
cd.CallDetails += categoryText + callText;
if (cd.CallDetails != "")
{
cd.CallDetails += @"\line ";
}
if (cd.LinkedStatus != "" && cd.CallDetails != "")
{
displayData.Add(cd);
}
}
// Get Status History
foreach (claim_status_history csh in toDisplay.GetClaimHistory(false))
{
ClaimDisplayDataStatusChange cd = new ClaimDisplayDataStatusChange();
cd.ActionTime = csh.date_of_change.GetValueOrDefault();
cd.User = csh.LinkedUser.username;
if (csh.old_status_id != csh.new_status_id)
{
if (csh.new_status_id == -1)
{
cd.LinkedStatus = "{Empty}";
}
else
{
cd.LinkedStatus = csh.LinkedNewStatus.name;
}
}
cd.RevisitDate = csh.new_revisit_date;
displayData.Add(cd);
}
var sortedDD = displayData.OrderByDescending(o => o.ActionTime);
string fullText = @"{\rtf1\ansi {\colortbl;\red0\green0\blue0;\red25\green25\blue112;}";
foreach (ClaimDisplayData anItem in sortedDD)
{
// Example (missing formatting)
/*
* 2/13/15 (aaron) | In Process
* Date Received: 1/20/2015
* Information needed by insurance company-: answered question
*/
string baseLineFormat = @"\cf2 {0}\cf1 \b {2}{3}\b0 \i{1}\i0 \line ";
string revisitDate;
if (anItem.RevisitDate.HasValue)
{
if (anItem.RevisitDate > new DateTime(2000, 1, 1))
{
revisitDate = string.Format(" +{0}", Math.Ceiling((anItem.RevisitDate.Value - anItem.ActionTime).TotalDays));
}
else
{
revisitDate = "";
}
}
else
{
revisitDate = "";
}
fullText += string.Format(baseLineFormat, anItem.ActionTime.ToString("M/d/yy"), anItem.User,
anItem.LinkedStatus, revisitDate);
if (anItem is ClaimDisplayDataCall)
{
ClaimDisplayDataCall callItem = (ClaimDisplayDataCall)anItem;
fullText += callItem.CallDetails;
}
}
fullText += "}";
rtbMain.Rtf = fullText;
if (rtbMain.Text == "")
{
rtbMain.Text = "No history found.";
}
}
public void InitializeDisplay(bool isClaimList, string whereClause = "", string orderByClause = "")
{
_whereClause = whereClause;
_orderByClause = orderByClause;
if (isClaimList)
{
claim c = new claim();
FilterByInsurance = true;
SetFormSearchMode(FormSearchModes.ClaimList);
company comp = new company();
InsuranceCompanyGroups icg = new InsuranceCompanyGroups();
carrierSource = new List <InsuranceCompanyGroups>();
carrierSource = comp.GetInsuranceCompaniesAsGroup();
// Update 'Count' for each item, remove items with no matches
OleDbConnection connObject = new OleDbConnection(new ConnectionAlias().GetConnectionString());
OleDbCommand commObject = new OleDbCommand();
OleDbDataReader dr;
List <InsuranceCompanyGroups> toRemove = new List <InsuranceCompanyGroups>();
connObject.Open();
commObject.Connection = connObject;
// For the groups only, get a count of claims
foreach (InsuranceCompanyGroups aGroup in carrierSource)
{
commObject.CommandText = "SELECT COUNT(*) FROM claims c " + BuildSQL(true, false) + " AND c.company_id IN(" + aGroup.CompanyIDAsString() + ")";
dr = commObject.ExecuteReader();
dr.Read();
aGroup.Count = System.Convert.ToInt32(dr[0]);
dr.Close();
if (aGroup.Count == 0)
{
toRemove.Add(aGroup);
}
}
// Remove any groups with a count of 0
toRemove.ForEach(t => carrierSource.Remove(t));
// Add "All" Item
icg.Name = "All";
icg.Count = -1;
carrierSource.Insert(0, icg);
cmbCarrierFilter.ItemsSource = carrierSource;
if (cmbCarrierFilter.Items.Count > 1)
{
cmbCarrierFilter.SelectedIndex = 1;
}
else
{
cmbCarrierFilter.SelectedIndex = 0;
}
}
else
{
FilterByInsurance = false;
SetFormSearchMode(FormSearchModes.Patient);
}
}
public void CreateClaim(claim c)
{
utOfWork.ClaimRepository.Add(c);
utOfWork.Commit();
}
private void OpenSelectedClaim()
{
if (datResults.SelectedItems.Count == 1)
{
int claimID = Convert.ToInt32(((DataRowView)datResults.SelectedItem).Row["ID"]);
frmClaimManager toShow;
bool found = false;
claim selectedClaim = new claim(claimID);
/*
* foreach (Form aForm in Application.OpenForms)
* {
* if (aForm is frmClaimManager)
* {
* if (((frmClaimManager)aForm).FormClaim.id == selectedClaim.id)
* {
* toShow = (frmClaimManager)aForm;
* if (toShow.WindowState == FormWindowState.Minimized)
* toShow.WindowState = FormWindowState.Normal;
* else
* toShow.BringToFront();
* found = true;
* }
* }
* }
*/
if (!found)
{
bool readOnly = false;
// Double-check to verify the form isn't currently being edited by another user.
// If it is, give them the option to open it read-only, or to force full-access
List <user> usersViewingclaim = selectedClaim.UsersViewingClaim(true);
if (usersViewingclaim.Count > 0)
{
string userList = string.Empty;
foreach (user aUser in usersViewingclaim)
{
if (userList != string.Empty)
{
userList += "; ";
}
userList += aUser.username;
}
frmClaimInUseDialog frmInUse = new frmClaimInUseDialog(userList);
frmInUse.ShowDialog();
if (frmInUse.UserChoice == frmClaimInUseDialog.ClaimInUseChoice.DoNotOpen)
{
return;
}
else if (frmInUse.UserChoice == frmClaimInUseDialog.ClaimInUseChoice.OpenReadOnly)
{
readOnly = true;
}
}
List <claim> searchList = new List <claim>();
foreach (DataRowView anItem in datResults.Items)
{
searchList.Add(new claim(Convert.ToInt32(anItem.Row["ID"])));
}
toShow = new frmClaimManager(readOnly, searchList, selectedClaim);
if (readOnly)
{
ActiveUser.LogAction(ActiveUser.ActionTypes.ViewClaim, selectedClaim.id, "(Read Only) " + selectedClaim.PatientName);
}
else
{
ActiveUser.LogAction(ActiveUser.ActionTypes.ViewClaim, selectedClaim.id, selectedClaim.PatientName);
}
try
{
toShow.Left = 0;
toShow.Top = 0;
toShow.Show();
}
catch (Exception err)
{
LoggingHelper.Log(err, false);
}
}
}
}
public ProviderEligibilityDisplay(string providerID, string displayData, claim linkClaim)
{
ProviderID = providerID;
DisplayData = displayData;
LinkedClaim = linkClaim;
}
public static List <string> ConvertBatchToXML(NHDG.NHDGCommon.Claims.ClaimBatch toConvert, string targetFolder)
{
List <string> fileData = new List <string>();
XMLClaimData xh;
foreach (NHDG.NHDGCommon.Claims.Claim aClaim in toConvert.Claims)
{
xh = new XMLClaimData();
claim localClaim = new claim();
localClaim.LoadWithDentrixIDs(aClaim.Identity.ClaimID.ToString(), aClaim.Identity.ClaimDB.ToString());
string claimtype;
if (localClaim.claim_type == claim.ClaimTypes.Predeterm || localClaim.claim_type == claim.ClaimTypes.SecondaryPredeterm)
{
claimtype = "P";
}
else
{
claimtype = "C";
}
xh.CLAIM_TYPE = claimtype;
// BILLING_PROVIDER
xh.BILLING_LASTNAME = "NEW HAVEN DENTAL GROUP";
xh.BILLING_FIRSTNAME = "";
xh.BILLING_ADDRESS1 = "123 York St";
xh.BILLING_ADDRESS2 = "4L";
xh.BILLING_CITY = "New Haven";
xh.BILLING_STATE = "CT";
xh.BILLING_ZIP = "06511";
xh.BILLING_NPIN = "1609916089";
xh.BILLING_STATE_LICENCE = "03419";
xh.BILLING_TIN_SSN = "061025204";
xh.BILLING_PHONE = "203-781-8051";
xh.BILLING_ADDITIONAL_ID = "";
// PAYTO_PROVIDER
xh.PAYTO_LASTNAME = CleanData(localClaim.doctor_last_name);
xh.PAYTO_FIRSTNAME = CleanData(localClaim.doctor_first_name);
xh.PAYTO_ADDRESS1 = CleanData(localClaim.doctor_address);
xh.PAYTO_ADDRESS2 = CleanData(localClaim.doctor_address2);
xh.PAYTO_CITY = CleanData(localClaim.doctor_city);
xh.PAYTO_STATE = CleanData(localClaim.doctor_state);
xh.PAYTO_ZIP = CleanData(localClaim.doctor_zip);
xh.PAYTO_NPIN = CleanData(aClaim.BillingDentist.NPINumber);
xh.PAYTO_STATE_LICENCE = ""; // ***
xh.PAYTO_TIN = CleanData(aClaim.BillingDentist.TIN);
xh.PAYTO_SSN = ""; // *** Do we need this?
xh.PAYTO_PHONE = CleanData(localClaim.doctor_phone_number_object.FormattedPhone);
xh.PAYTO_ADDITIONAL_ID = ""; // *** additional ID
// RENDERING_PROVIDER
xh.RENDERING_PROVIDER_LASTNAME = CleanData(localClaim.doctor_last_name);
xh.RENDERING_PROVIDER_FIRSTNAME = CleanData(localClaim.doctor_first_name);
xh.RENDERING_PROVIDER_SIGNATURE_DATE = ""; // *** Signature date
xh.RENDERING_PROVIDER_ADDRESS1 = CleanData(localClaim.doctor_address);
xh.RENDERING_PROVIDER_ADDRESS2 = CleanData(localClaim.doctor_address2);
xh.RENDERING_PROVIDER_CITY = CleanData(localClaim.doctor_city);
xh.RENDERING_PROVIDER_STATE = CleanData(localClaim.doctor_state);
xh.RENDERING_PROVIDER_ZIP = CleanData(localClaim.doctor_zip);
xh.RENDERING_PROVIDER_NPIN = CleanData(aClaim.BillingDentist.NPINumber);
xh.RENDERING_PROVIDER_LICENSE_NUMBER = CleanData(aClaim.BillingDentist.License);
if (localClaim.doctor_first_name.Contains("Eva")) // *** Hard-coded for the periodontist right now
{
xh.RENDERING_PROVIDER_SPECIALTY = "1223P0300X"; // Periodontics
}
else
{
xh.RENDERING_PROVIDER_SPECIALTY = "122300000X";
}
xh.RENDERING_PROVIDER_PHONE = CleanData(localClaim.doctor_phone_number_object.FormattedPhone);
xh.RENDERING_PROVIDER_ADDITIONAL_ID = ""; // *** additional ID
// SUBSCRIBER
xh.SUBSCRIBER_LASTNAME = CleanData(localClaim.subscriber_last_name);
xh.SUBSCRIBER_FIRSTNAME = CleanData(localClaim.subscriber_first_name);
xh.SUBSCRIBER_MIDDLENAME = CleanData(localClaim.subscriber_middle_initial);
xh.SUBSCRIBER_SUFFIX = "";
xh.SUBSCRIBER_ADDRESS1 = CleanData(localClaim.subscriber_address);
xh.SUBSCRIBER_ADDRESS2 = CleanData(localClaim.subscriber_address2);
xh.SUBSCRIBER_CITY = CleanData(localClaim.subscriber_city);
xh.SUBSCRIBER_STATE = CleanData(localClaim.subscriber_state);
xh.SUBSCRIBER_ZIP = CleanData(localClaim.subscriber_zip);
xh.SUBSCRIBER_DOB = ConvertDateTime(localClaim.subscriber_dob);
xh.SUBSCRIBER_GENDER = ConvertGender(aClaim.Subscriber.Sex);
xh.SUBSCRIBER_ID = CleanData(aClaim.Subscriber.ID);
xh.SUBSCRIBER_GROUP_PLAN_NAME = CleanData(localClaim.subscriber_group_name);
xh.SUBSCRIBER_GROUP_PLAN_NUMBER = CleanData(localClaim.subscriber_group_number);
xh.SUBSCRIBER_EMPLOYER_NAME = CleanData(aClaim.Subscriber.Employer.Name);
xh.SUBSCRIBER_INSURANCE_SEQUENCE = "P"; // *** Not sure how this should be used
xh.SUBSCRIBER_SIGNATURE = "Yes";
xh.SUBSCRIBER_SIGNATURE_DATE = ""; // *** Another signature date field, not sure
// PATIENT
xh.PATIENT_RELATION = ConvertRelationship(aClaim.Patient.SubscriberRelationship); // *** Excel document lists this as subscriber_relation, do all relationships work the same way?
xh.PATIENT_STUDENTSTATUS = CleanData(aClaim.Patient.StudentStatus); // We're ignoring this currently
xh.PATIENT_LASTNAME = CleanData(localClaim.patient_last_name);
xh.PATIENT_FIRSTNAME = CleanData(localClaim.patient_first_name);
xh.PATIENT_MIDDLENAME = CleanData(localClaim.patient_middle_initial);
xh.PATIENT_SUFFIX = "";
xh.PATIENT_ADDRESS1 = CleanData(localClaim.patient_address);
xh.PATIENT_ADDRESS2 = CleanData(localClaim.patient_address2);
xh.PATIENT_CITY = CleanData(localClaim.patient_city);
xh.PATIENT_STATE = CleanData(localClaim.patient_state);
xh.PATIENT_ZIP = CleanData(localClaim.patient_zip);
xh.PATIENT_DOB = ConvertDateTime(localClaim.patient_dob);
xh.PATIENT_GENDER = ConvertGender(aClaim.Patient.Sex);
xh.PATIENT_ID = CleanData(aClaim.Patient.ID);
xh.PATIENT_SIGNATURE = "Yes";
xh.PATIENT_SIGNATURE_DATE = ""; // *** Signature date
// PRIMARY_PAYER // *** Primary payer section, not sure what this is
xh.PRIMARY_PAYER_NAME = CleanData(localClaim.payer_name);
xh.PRIMARY_PAYER_ID = CleanData(localClaim.payer_id); // This should be the electronic ID used in eclaims
xh.PRIMARY_PAYER_ADDRESS1 = CleanData(aClaim.GeneralInformation.Carrier.Address.Street1);
xh.PRIMARY_PAYER_ADDRESS2 = CleanData(aClaim.GeneralInformation.Carrier.Address.Street2);
xh.PRIMARY_PAYER_CITY = CleanData(aClaim.GeneralInformation.Carrier.Address.City);
xh.PRIMARY_PAYER_STATE = CleanData(aClaim.GeneralInformation.Carrier.Address.State);
xh.PRIMARY_PAYER_ZIP = CleanData(aClaim.GeneralInformation.Carrier.Address.Zip);
// OTHER_COVERAGE
if (aClaim.OtherPolicy == null)
{
xh.OTHERPAYER_FLAG = "N";
}
else
{
xh.OTHERPAYER_FLAG = "Y";
CommonFunctions.FormattedName subscriberName = CommonFunctions.GetFormattedName(aClaim.OtherPolicy.SubscriberName);
xh.OTHER_SUBSCRIBER_LASTNAME = subscriberName.LastName;
xh.OTHER_SUBSCRIBER_FIRSTNAME = subscriberName.FirstName;
xh.OTHER_SUBSCRIBER_MIDDLENAME = subscriberName.MiddleInitial;
xh.OTHER_SUBSCRIBER_SUFFIX = "";
xh.OTHER_SUBSCRIBER_DOB = ConvertToMercuryDateTime(aClaim.OtherPolicy.SubscriberBirthDate);
xh.OTHER_SUBSCRIBER_GENDER = ConvertGender(aClaim.OtherPolicy.SubscriberSex);
xh.OTHER_SUBSCRIBER_ID = CleanData(aClaim.OtherPolicy.SubscriberID);
xh.OTHER_SUBSCRIBER_GROUP_PLAN_NAME = CleanData(aClaim.OtherPolicy.PlanName);
xh.OTHER_SUBSCRIBER_GROUP_PLAN_NUMBER = CleanData(aClaim.OtherPolicy.PolicyNumber);
xh.OTHER_SUBSCRIBER_RELATION = ConvertRelationshipString(aClaim.OtherPolicy.PatientRelationship);
xh.OTHER_SUBSCRIBER_ADDRESS1 = CleanData(aClaim.OtherPolicy.SubscriberAddress.Street1);
xh.OTHER_SUBSCRIBER_ADDRESS2 = CleanData(aClaim.OtherPolicy.SubscriberAddress.Street2);
xh.OTHER_SUBSCRIBER_CITY = CleanData(aClaim.OtherPolicy.SubscriberAddress.City);
xh.OTHER_SUBSCRIBER_STATE = CleanData(aClaim.OtherPolicy.SubscriberAddress.State);
xh.OTHER_SUBSCRIBER_ZIP = CleanData(aClaim.OtherPolicy.SubscriberAddress.Zip);
xh.OTHER_SUBSCRIBER_INSURANCE_SEQUENCE = "S"; // May need to look into this more
xh.OTHER_SUBSCRIBER_COB_PAYER_PAID_AMT = ""; // Same as above - leaving this blank for now
xh.OTHER_SUBSCRIBER_COB_PATIENT_RESP_AMT = ""; // Same as above - leaving this blank for now
// *********************** Not sure what to fill in for this entire Other Payer section
Dictionary <string, string> payerInfo = GetOtherPayerID(aClaim.OtherPolicy.PlanName);
if (payerInfo != null)
{
xh.OTHERPAYER_NAME = payerInfo["name"];
xh.OTHERPAYER_ID = payerInfo["id"];
}
else
{
xh.OTHERPAYER_NAME = CleanData(aClaim.OtherPolicy.PlanName);
xh.OTHERPAYER_ID = CleanData(aClaim.OtherPolicy.SubscriberID);
}
xh.OTHERPAYER_ADDRESS1 = CleanData(aClaim.OtherPolicy.InsuranceAddress.Street1);
xh.OTHERPAYER_ADDRESS2 = CleanData(aClaim.OtherPolicy.InsuranceAddress.Street2);
xh.OTHERPAYER_CITY = CleanData(aClaim.OtherPolicy.InsuranceAddress.City);
xh.OTHERPAYER_STATE = CleanData(aClaim.OtherPolicy.InsuranceAddress.State);
xh.OTHERPAYER_ZIP = CleanData(aClaim.OtherPolicy.InsuranceAddress.Zip);
xh.OTHERPAYER_CONTACT_NAME = "";
xh.OTHERPAYER_CONTACT_EMAIL = "";
xh.OTHERPAYER_CONTACT_TELEPHONE = "";
xh.OTHERPAYER_CONTACT_FAX = "";
xh.OTHERPAYER_CLAIM_PAID_DATE = "";
// ********************************************************************************************
}
// CLAIM
xh.CLAIM_NUMBER = localClaim.id.ToString();
xh.SERVICE_DATE = ConvertDateTime(localClaim.date_of_service);
xh.PREDIDENTIFICATION = ""; // *** PRED Identification?
xh.OTHERFEE = ""; // Ignore
xh.TOTAL_AMOUNT = localClaim.amount_of_claim.ToString();
// ***** COME BACK TO THIS - Missing teeth
// MISSING_TOOTHS
// xh.MISSING_TOOTH = "";
// *** Diagnosis qualifier and diagnosis codes don't appear to be available
xh.DIAG_QUALIFIER = ""; // For now, ignore
// DIAGNOSIS_CODES
xh.DIAGNOSIS_CODES_A = "";
xh.DIAGNOSIS_CODES_D = "";
xh.DIAGNOSIS_CODES_D = "";
xh.DIAGNOSIS_CODES_D = "";
xh.CLAIM_NOTES = "";
xh.PLACE_SERVICE = "OFF";
// Enclosures
xh.ENCLOSURES_RADIOGRAPH = CleanData(aClaim.AncillaryData.NumRadiographsEnclosed.ToString());
xh.ENCLOSURES_ORAL_IMAGES = ""; // Ignore
xh.ENCLOSURES_MODELS = ""; // Ignore
xh.ORTHOCLAIM = CommonFunctions.ToYesNo(aClaim.AncillaryData.Orthodontics.TreatmentForOrthodontics)[0].ToString();
xh.APPLIANCE_PLACEMENT_DATE = ConvertToMercuryDateTime(aClaim.AncillaryData.Orthodontics.DateAppliancePlaced);
if (ConvertEmptyToN(aClaim.AncillaryData.Prosthesis.InitialReplacement) == "N")
{
xh.PLACEMENT_TYPE = "Initial";
}
else
{
xh.PLACEMENT_TYPE = "Replacement";
}
xh.MONTHS_TREATMENT_REMAININT = CleanData(aClaim.AncillaryData.Orthodontics.RemainingMonths.ToString());
xh.REPLACEMENT_PROSTHESIS = ConvertEmptyToN(aClaim.AncillaryData.Prosthesis.InitialReplacement);
xh.PROIR_PLACEMENT_DATE = ConvertToMercuryDateTime(aClaim.AncillaryData.Prosthesis.PriorPlacement);
xh.OCCUPATIONAL_ILLNESS = "N"; // Ignore this, we track auto or other
if (aClaim.BillingDentist.Accident.Type == NHDG.NHDGCommon.Claims.AccidentType.Automobile)
{
xh.AUTO_ACCIDENT = "Y";
xh.OTHER_ACCIDENT = "N";
}
else if (aClaim.BillingDentist.Accident.Type == NHDG.NHDGCommon.Claims.AccidentType.Other)
{
xh.OTHER_ACCIDENT = "Y";
xh.AUTO_ACCIDENT = "N";
}
else
{
xh.AUTO_ACCIDENT = "N";
xh.OTHER_ACCIDENT = "N";
}
xh.ACCIDENT_DATE = aClaim.AncillaryData.DateOfAccident;
xh.ACCIDENT_STATE = aClaim.AncillaryData.AccidentState;
xh.ADMISSION_DATE = ""; // Ignore
xh.TOT_TREATMENT_PERIOD = ""; // Ignore
// We just won't have any attachments for now, can ask if this is important though
// ATTACHMENTS
xh.ATTACHMENT_ID = "";
// LINE_SERVICE
foreach (NHDG.NHDGCommon.Claims.Treatment t in aClaim.TreatmentInformation.Treatments)
{
LineHelper lh = new LineHelper();
lh.SERVICE_DATE = ConvertToMercuryDateTime(t.ProcedureDate);
lh.CAVITY_DESIGNATION = ""; // Ignore
lh.TOOTH_SYSTEM = ""; // Ignore
lh.PROCEDURE_CODE = t.ProcedureCode;
lh.DIAGNOSIS_POINTER = ""; // *** Ignore for now, we're not using on ADA form
lh.QUANTITY = t.Quantity;
lh.PROCEDURE_DESCRIPTION = CleanData(t.Description);
lh.FEE = t.Fee;
lh.LINE_NOTES = "";
try
{
// teeth
foreach (string aTooth in ConvertToothsToList(t.Tooth, t.ToothEnd))
{
lh.InsertTooth(aTooth);
}
}
catch (Exception ex)
{
C_DentalClaimTracker.LoggingHelper.Log(ex, false);
// Send it anyway, but log an error here
}
// surfaces
if (t.Surface != "")
{
foreach (char aChar in t.Surface.ToCharArray())
{
lh.InsertSurface(aChar.ToString());
}
}
xh.InsertLineService(lh);
}
fileData.Add(xh.MainDocAsString);
}
return(fileData);
}
} // Is SQL, ie = " AND clinic_id IN (30)
private void SetReportSource()
{
List <AgingReportRow> reportData = new List <AgingReportRow>();
claim c = new claim();
company_contact_info cci = new company_contact_info();
c.company_address_id = 0;
string SQL = @"SELECT co.name as company_name, group_name as group_plan, group_num , cci.phone as company_phone,
c.id as claim_id, sent_date, date_of_service, subscriber_first_name + ' ' + subscriber_last_name AS subscriber,
CASE c.claim_type WHEN 0 THEN 'P' WHEN 1 THEN 'S' ELSE 'PRE' END AS 'Type', c.subscriber_alternate_number as idnum, patient_first_name + ' ' + patient_last_name AS patient,
patient_dob as Birthday, c.amount_of_claim as Total, revisit_date, cs.name as current_status
FROM claims c INNER JOIN companies co ON c.company_id = co.id INNER JOIN company_contact_info cci ON
cci.order_id = c.company_address_id AND co.id = cci.company_id LEFT JOIN claimstatus cs ON cs.id = c.status_id ";
string claimsToInclude = "-1";
string reportName = string.Empty;
if (chkShowPrimary.Checked)
{
reportName = "Primary";
claimsToInclude += ",0";
}
if (chkShowSecondary.Checked)
{
if (reportName != string.Empty)
{
reportName += " and ";
}
reportName += "Secondary";
claimsToInclude += ",1";
}
if (chkShowPredeterms.Checked)
{
if (reportName != string.Empty)
{
reportName += " and ";
}
reportName += "Predeterm";
claimsToInclude += ",2,3";
}
string whereClause = string.Format(" WHERE claim_type IN({0}) AND DATEDIFF(d, sent_date, GETDATE()) > {1} {2} AND [open] = 1", claimsToInclude, DateCriteria, ClinicCriteria);
DataTable allMatches = c.Search(SQL + whereClause + " ORDER BY company_name, group_plan");
foreach (DataRow aRow in allMatches.Rows)
{
AgingReportRow ar = new AgingReportRow();
ar.CompanyName = aRow["company_name"].ToString();
ar.GroupNumber = aRow["group_num"].ToString();
ar.GroupPlan = aRow["group_plan"].ToString() == "" ? "[No Plan Name]" : aRow["group_plan"].ToString(); // give it a default val if blank
try { ar.CompanyPhone = new PhoneObject(aRow["company_phone"].ToString()).FormattedPhone; }
catch { ar.CompanyPhone = aRow["company_phone"].ToString(); }
ar.SentDate = Convert.ToDateTime(aRow["sent_date"]);
ar.DateOfService = Convert.ToDateTime(aRow["date_of_service"]);
ar.Subscriber = aRow["subscriber"].ToString();
ar.Type = aRow["type"].ToString();
ar.IDNum = aRow["idnum"].ToString();
ar.Patient = aRow["patient"].ToString();
if (CommonFunctions.DBNullToString(aRow["birthday"]) == "")
{
ar.Birthday = new DateTime(1901, 1, 1);
}
else
{
ar.Birthday = Convert.ToDateTime(aRow["birthday"]);
}
ar.Total = Convert.ToDecimal(aRow["total"]) / 100;
if (CommonFunctions.DBNullToString(aRow["revisit_date"]) == "")
{
ar.RevisitDate = "";
}
else
{
ar.RevisitDate = Convert.ToDateTime(aRow["revisit_date"]).ToString("M/d/yy");
}
ar.CurrentStatus = aRow["current_status"].ToString();
reportData.Add(ar);
}
AgingReportRowBindingSource.DataSource = reportData;
reportName += string.Format(" (> {0} days)", DateCriteria.ToString("0"));
rptvMain.ShowParameterPrompts = false;
rptvMain.LocalReport.ReportPath = "Reporting\\PriorityReport.rdlc";
rptvMain.LocalReport.SetParameters(new ReportParameter("ReportName", reportName));
rptvMain.RefreshReport();
}
private void btnImportDentrixProviders_Click(object sender, EventArgs e)
{
claim workingClaim = new claim();
DataTable importData = new DataTable();
DataTable importDataSecondaries = new DataTable();
DataTable importDataPredeterms = new DataTable();
OleDbConnection oConnect;
dentrix_providers dp;
try
{
oConnect = new OleDbConnection(data_mapping_schema.GetDefaultSchema.GetConnectionString(true));
}
catch (Exception err)
{
LoggingHelper.Log("An error occurred getting the connection string for a new connection in frmImportData.Import", LogSeverity.Error, err, false);
return;
}
OleDbDataAdapter oAdapter;
// Use Connection object for the DataAdapter to retrieve all tables from selected Database
try
{
oConnect.Open();
}
catch (Exception err)
{
LoggingHelper.Log("Could not connect to the database in frmImportAppointmentAuditdata.Import", LogSeverity.Error, err, false);
return;
}
try
{
string sql;
sql = "SELECT * FROM DDB_RSC_BASE WHERE RSCTYPE = 1 ORDER BY URSCID";
oAdapter = new OleDbDataAdapter(sql, oConnect);
oAdapter.SelectCommand.CommandTimeout = 300;
oAdapter.Fill(importData);
}
catch (Exception err)
{
LoggingHelper.Log("Error with SQL statement or connection in frmImportData.Import", LogSeverity.Error, err);
return;
}
// **** Data retrieved, put it into our local table
dp = new dentrix_providers();
dp.Zap();
string colList = "";
string valueList = "";
string sqlText = "INSERT INTO dentrix_providers ({0}) VALUES {1}";
foreach (DataColumn aColumn in importData.Columns)
{
colList += aColumn.ColumnName + ",";
}
colList = colList.Substring(0, colList.Length - 1);
// Have data at this point, need to tie them to the internal mapping schema data
foreach (DataRow anImportRow in importData.Rows)
{
valueList += "(";
foreach (DataColumn aColumn in importData.Columns)
{
string newValue = anImportRow[aColumn.ColumnName].ToString().Trim().Replace("'", "''");
if (!aColumn.DataType.ToString().Contains("int"))
{
newValue = "'" + newValue + "'";
}
valueList += newValue + ",";
}
valueList = valueList.Substring(0, valueList.Length - 1);
valueList += "),";
}
valueList = valueList.Substring(0, valueList.Length - 1);
sqlText = string.Format(sqlText, colList, valueList);
dp.ExecuteNonQuery(sqlText);
MessageBox.Show("Import Successful! " + importData.Rows.Count + " providers in the system.");
}
public static void GoImport()
{
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);
DeleteDirectoryFromTemp();
//LoggingHelper.Log("An error occurred getting the connection string for a new connection in frmImportData.Import", LogSeverity.Error, err, false);
//e.Cancel = true;
CancelImport();
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);
DeleteDirectoryFromTemp();
//LoggingHelper.Log("Could not connect to the database in frmImportdata.Import", LogSeverity.Error, err, false);
//e.Cancel = true;
CancelImport();
return;
}
try
{
//UpdateProgressBar(50, "Querying remote database (Standard)...");
// ************* Standard Claims
oAdapter = new OleDbDataAdapter(PrepareSQL(dms.sqlstatement, changesOnly), oConnect);
oAdapter.SelectCommand.CommandTimeout = System.Convert.ToInt32(dataTimeout);
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(dataTimeout);
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(dataTimeout);
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(dataTimeout);
oAdapter.Fill(importDataSecondaryPredeterms);
}
}
catch (Exception err)
{
CreateLogFile(err.ToString());
Updateimporterror(true);
DeleteDirectoryFromTemp();
//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);
DeleteDirectoryFromTemp();
//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();
system_options.SetLastImportDate(DateTime.Now);
ShowLastImportDate();
AddStatus("The import completed successfully! " + totalRows + " rows were imported.");
AddReportMessage(string.Format("The import completed successfully!\nPrimary: {0}\nSecondary: {1}\n" +
"Predeterm: {2}\nSecondary Predeterm: {3}\nTotal Open Claims: {4}", primaryClaimCount, secondaryClaimCount, predetermClaimCount, secondaryPredetermClaimCount,
primaryClaimCount + secondaryClaimCount + predetermClaimCount + secondaryPredetermClaimCount));
try
{
string importFileName = Application.StartupPath + "\\Imports\\" + DateTime.Now.ToString("yyyy-MM-dd hh-mm-ss") + " import.rtf";
//Directory.CreateDirectory(Path.GetDirectoryName(importFileName));
//rtxtReport.SaveFile(importFileName);
saveFileFromTemp();
DeleteDirectoryFromTemp();
CreateLogFile("Import Successfully.");
Updateimporterror(false);
}
catch (Exception ex) {
CreateLogFile(ex.ToString());
Updateimporterror(true);
DeleteDirectoryFromTemp();
//LoggingHelper.Log("Error creating import report file.", LogSeverity.Error, ex, false);
}
}
void WorkerThread_DoWork(object sender, DoWorkEventArgs e)
{
claim workingClaim = new claim();
DataTable importData = new DataTable();
DataTable importDataSecondaries = new DataTable();
DataTable importDataPredeterms = new DataTable();
OleDbConnection oConnect;
appointment_audit aa;
UpdateProgressBar(0, "Starting Import");
#region Initiate Connection, Get Data
try
{
oConnect = new OleDbConnection(data_mapping_schema.GetDefaultSchema.GetConnectionString(true));
}
catch (Exception err)
{
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)
{
LoggingHelper.Log("Could not connect to the database in frmImportAppointmentAuditdata.Import", LogSeverity.Error, err, false);
e.Cancel = true;
return;
}
DateTime lastImport = system_options.GetLastAppointmentAuditImportDate();
importStart = DateTime.Now;
try
{
string sql;
// ************* Start import
if (chkMergeData.Checked)
{
sql = string.Format("SELECT * FROM AUDIT_DDB_APPT_BASE" +
" WHERE DATE_CHANGED > '{0}'", CommonFunctions.ToSQLServerDateTime(lastImport));
}
else
{
UpdateStatusBox("Clearing existing appointment audit information for full import...");
UpdateProgressBar(0, "Clearing existing appointments audit data...");
aa = new appointment_audit();
aa.Zap();
sql = string.Format("SELECT * FROM AUDIT_DDB_APPT_BASE" +
" WHERE DATE_CHANGED > '{0}'",
DateTime.Now.AddDays(Convert.ToInt32(nmbFullImportDuration.Value * -1)).ToShortDateString());
}
UpdateStatusBox("Starting the following query: \r\n" + sql);
UpdateProgressBar(50, "Querying remote database...");
oAdapter = new OleDbDataAdapter(sql, oConnect);
oAdapter.SelectCommand.CommandTimeout = System.Convert.ToInt32(nmbTimeout.Value);
oAdapter.Fill(importData);
}
catch (Exception err)
{
LoggingHelper.Log("Error with SQL statement or connection in frmImportData.Import", LogSeverity.Error, err);
e.Cancel = true;
CancelImport();
return;
}
UpdateProgressBar(200, "Importing data...");
// **** Data retrieved, put it into our local table
totalRows = importData.Rows.Count;
decimal exactIncrementAmount;
decimal incrementCounter = 0;
int increment;
if (totalRows > 0)
{
exactIncrementAmount = 500m / totalRows;
}
else
{
exactIncrementAmount = 500m;
}
if (exactIncrementAmount < 1)
{
increment = 1;
}
else
{
increment = Convert.ToInt32(Math.Truncate(exactIncrementAmount));
}
UpdateStatusBox("Starting import process for " + importData.Rows.Count + " rows.");
int i = 0;
int insertedRows = 0;
aa = new appointment_audit();
string colList = "";
string valueList = "";
string sqlText = "INSERT INTO APPOINTMENT_AUDIT ({0}) VALUES {1}";
foreach (DataColumn aColumn in importData.Columns)
{
colList += aColumn.ColumnName + ",";
}
colList = colList.Substring(0, colList.Length - 1);
// Have data at this point, need to tie them to the internal mapping schema data
foreach (DataRow anImportRow in importData.Rows)
{
i++;
insertedRows++;
// need new data object for this table type
/*aa = new appointment_audit();
*
* foreach (DataColumn aColumn in importData.Columns)
* {
* aa[aColumn.ColumnName] = anImportRow[aColumn.ColumnName];
* }
*
* aa.change_type = aa.DiscoverChangeType();
* aa.Save();
*
* incrementCounter += exactIncrementAmount;
*
* */
if (insertedRows > 500) // Can't go over 1000, no reason to cut it close
{
valueList = valueList.Substring(0, valueList.Length - 1);
sqlText = string.Format(sqlText, colList, valueList);
/* if (!THREADINGON)
* Clipboard.SetText(sqlText); */
aa.ExecuteNonQuery(sqlText);
valueList = "";
sqlText = "INSERT INTO APPOINTMENT_AUDIT ({0}) VALUES {1}";
}
valueList += "(";
foreach (DataColumn aColumn in importData.Columns)
{
string newValue = anImportRow[aColumn.ColumnName].ToString().Trim().Replace("'", "''");
if (!aColumn.DataType.ToString().Contains("int"))
{
newValue = "'" + newValue + "'";
}
valueList += newValue + ",";
}
valueList = valueList.Substring(0, valueList.Length - 1);
valueList += "),";
if (incrementCounter >= increment)
{
UpdateProgressBar(increment, string.Format("Importing changes {0}/{1}", i, totalRows));
incrementCounter -= increment;
}
}
valueList = valueList.Substring(0, valueList.Length - 1);
sqlText = string.Format(sqlText, colList, valueList);
// Clipboard.SetText(sqlText);
aa.ExecuteNonQuery(sqlText);
#endregion
if (!THREADINGON)
{
WorkerThread_RunWorkerCompleted(null, new RunWorkerCompletedEventArgs(null, null, false));
}
}
//Give an evaluator the "Accepted" status
public EvaluatorQuery addEvaluator(String email)
{
try
{
using (conferenceadminContext context = new conferenceadminContext())
{
var e = (from u in context.users
where u.membership.email == email
select u).FirstOrDefault();
if (e != null)
{
//Check if user has been an evaluator before
var check = (from s in context.evaluators
where s.userID == e.userID
select s).FirstOrDefault();
if (check != null)
{
//Update status to Accepted
check.deleted = false;
var claims = (from s in context.claims
where s.userID == e.userID && s.privilege.privilegestType == "Evaluator"
select s).FirstOrDefault();
if (claims != null)
{
claims.deleted = false;
}
else
{
//Add claim
claim newClaim = new claim();
newClaim.privilegesID = 4;
newClaim.deleted = false;
newClaim.userID = e.userID;
context.claims.Add(newClaim);
}
e.evaluatorStatus = "Accepted";
}
else
{
//Change status in table user
EvaluatorQuery newEvaluator = new EvaluatorQuery();
newEvaluator.userID = e.userID;
newEvaluator.firstName = e.firstName;
newEvaluator.lastName = e.lastName;
newEvaluator.email = email;
newEvaluator.acceptanceStatus = e.evaluatorStatus;
//Add claim
claim newClaim = new claim();
newClaim.privilegesID = 4;
newClaim.deleted = false;
newClaim.userID = e.userID;
context.claims.Add(newClaim);
//Add evaluator
evaluator newEva = new evaluator();
newEva.userID = e.userID;
newEva.deleted = false;
context.evaluators.Add(newEva);
e.evaluatorStatus = "Accepted";
context.SaveChanges();
return newEvaluator;
}
context.SaveChanges();
}
return null;
}
}
catch (Exception ex)
{
Console.Write("EvaluatorManager.addEvaluator error " + ex);
return null;
}
}
private void ShowMatchingClaims()
{
pnlMatchingClaims.Visible = true;
try
{
int amount = Convert.ToInt32(dgvMain.SelectedRows[0].Cells[colAmountActual.Index].Value);
string sql = "SELECT TOP " + nmbMaxMatches.Value;
sql += @"
claim.claimid, claim.claimdb, deductable
FROM
DDB_PROC_LOG plog
INNER JOIN
DDB_claim claim ON claim.CLAIMID = plog.CLAIMID AND claim.CLAIMDB = plog.CLAIMDB
INNER JOIN
dbo.DDB_PROC_CODE pCode ON plog.PROC_CODEID = pCode.PROC_CODEID AND plog.PROC_CODEDB = pCode.PROC_CODEDB
INNER JOIN
dbo.DDB_INSURANCE ins ON claim.INSID = ins.INSID AND claim.INSDB = ins.INSDB
INNER JOIN dbo.DDB_PAT ON claim.PATID = dbo.DDB_PAT.PATID AND claim.PATDB = dbo.DDB_PAT.PATDB
INNER JOIN dbo.DDB_RSC ON claim.PROVID = dbo.DDB_RSC.URSCID
WHERE
amtpinspaid = {3}
AND adacode = '{0}'
AND {1}
AND {2}
ORDER BY AMTPINSPAID";
sql = string.Format(sql, dgvMain.SelectedRows[0].Cells[colProcCode.Index].Value, GetCompanyOrPatientCriteria(), GetDateCriteria(), amount);
if (InitializeConnection())
{
dgvMatches.Rows.Clear();
SqlDataAdapter sda = new SqlDataAdapter(sql, dbConnection);
DataTable matches = new DataTable();
claim c = new claim();
sda.Fill(matches);
foreach (DataRow aRow in matches.Rows)
{
string dentrixID = aRow[0].ToString();
string dentrixDB = aRow[1].ToString();
string deductable = aRow[2].ToString();
if (!c.LoadWithDentrixIDs(dentrixID, dentrixDB))
{
dgvMatches.Rows.Add(new object[] { dentrixID, dentrixDB, "Local not found." });
}
else
{
c.Load(matches.Rows[0]);
dgvMatches.Rows.Add(new object[] { c.PatientName, c.patient_dob.Value.ToShortDateString(), c.LinkedCompany.name, c.amount_of_claim, deductable,
c.date_of_service.HasValue ? c.date_of_service.Value.ToShortDateString() : "",
c.claim_type_display(true), c.doctor_provider_id, c.subscriber_group_name, c.subscriber_group_number, dentrixID, dentrixDB, c });
}
}
dbConnection.Close();
}
else
{
MessageBox.Show(this, "A connection with the Dentrix database could not be established");
}
}
catch (Exception err)
{
LoggingHelper.Log("Error showing matches in frmCodePaymentHistory.ShowMatchingClaims", LogSeverity.Error, err, false);
MessageBox.Show(this, "An error occurred showing the matches for this row. The error message is:\n\n" + err.Message);
}
}
