private static int SortInsuranceGroupsByName(Claims_Primary.InsuranceCompanyGroups x, Claims_Primary.InsuranceCompanyGroups y)
{
if (x == null)
{
if (y == null)
{
return(0);
}
else
{
return(-1);
}
}
else
{
if (y == null)
{
return(1);
}
else
{
return(x.Name.CompareTo(y.Name));
}
}
}
/// <summary>
/// Returns a list of all Insurance Company Groups and all Insurance Companies not in a group
/// </summary>
/// <returns></returns>
internal List <Claims_Primary.InsuranceCompanyGroups> GetInsuranceCompaniesAsGroup()
{
List <Claims_Primary.InsuranceCompanyGroups> allGroups = new List <Claims_Primary.InsuranceCompanyGroups>();
List <company> companiesInAGroup = new List <company>();
insurance_company_group icg = new insurance_company_group();
foreach (DataRow aRow in icg.GetAllData("name").Rows)
{
Claims_Primary.InsuranceCompanyGroups newGroup = new Claims_Primary.InsuranceCompanyGroups();
icg = new insurance_company_group(aRow);
List <company> matchingComps = icg.GetMatchingCompanies();
newGroup.Name = icg.name;
newGroup.Companies = matchingComps;
newGroup.Group = icg;
allGroups.Add(newGroup);
companiesInAGroup.AddRange(matchingComps);
}
DataTable companiesNotInGroup = icg.Search(string.Format("SELECT * FROM companies WHERE ID NOT IN ({0}) ORDER BY name", ClaimTrackerCommon.CompaniesToInString(companiesInAGroup)));
foreach (DataRow aRow in companiesNotInGroup.Rows)
{
company c = new company(aRow);
Claims_Primary.InsuranceCompanyGroups newGroup = new Claims_Primary.InsuranceCompanyGroups(c.name, c);
allGroups.Add(newGroup);
}
allGroups.Sort(SortInsuranceGroupsByName);
return(allGroups);
}
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);
}
}
/// <summary>
/// Builds the SQL to be used on this form. If whereonly is true, will return only the where clause
/// </summary>
/// <param name="whereOnly"></param>
/// <returns></returns>
private string BuildSQL(bool whereOnly = false, bool includeCarrierFilter = true)
{
string toReturn;
if (whereOnly)
{
toReturn = "WHERE 0=0 ";
}
else
{
toReturn = @"SELECT TOP 500 c.patient_last_name + ', ' + c.patient_first_name AS 'PatientName', c.id as 'ID',
CONVERT(varchar, c.patient_dob, 101) AS 'DOB', cmp.name AS 'Carrier', c.amount_of_claim / 100 AS 'Amount',
CONVERT(varchar, c.date_of_service , 101) AS 'DOS', c.date_of_service, c.patient_dob,
CASE c.claim_type
WHEN 0 THEN 'PRIM'
WHEN 1 THEN 'SEC'
When 2 THEN 'PRE'
WHEN 3 THEN 'SECPRE'
ELSE '?'
END AS 'Type', CONVERT(varchar, c.revisit_date, 101) AS 'Revisit', c.revisit_date,
c.doctor_provider_id AS 'Provider', c.doctor_provider_id as 'AlternateProvider', cs.name AS 'Status',
CONVERT(varchar, (SELECT MAX(csent.sent_date) FROM claim_sent_history csent WHERE csent.claim_id = c.id), 101) AS 'LastSent',
(SELECT MAX(csent.sent_date) FROM claim_sent_history csent WHERE csent.claim_id = c.id) AS 'last_sent',
(SELECT CONVERT(varchar, MAX(alog.action_taken_time), 101) FROM user_action_log alog WHERE alog.claim_id = c.id) AS 'LastEdit',
(SELECT MAX(alog.action_taken_time) FROM user_action_log alog WHERE alog.claim_id = c.id) AS 'last_edit', override_address_provider,
(SELECT TOP 1 username FROM user_action_log alog inner join users u on alog.user_id = u.id WHERE alog.claim_id = c.id ORDER BY action_taken_time desc) AS 'User',
CONVERT(varchar, (SELECT MAX(pl_date) FROM procedures p WHERE p.claim_id = c.id), 101) AS 'LastDOS',
(SELECT MAX(pl_date) FROM procedures p WHERE p.claim_id = c.id) AS 'last_dos'
FROM claims c
LEFT JOIN Companies cmp ON c.company_id = cmp.id
LEFT JOIN users u ON c.owner_id = u.id
LEFT JOIN claimstatus cs ON cs.id = c.status_id
WHERE 0=0 ";
}
claim_change_log c = new claim_change_log();
if (_whereClause == "")
{
if (_formSearchMode == FormSearchModes.Patient)
{
// create custom search string for patient name
toReturn += BuildWherePatientName(txtPatientName.Text, 1);
if (chkPatientUnpaidClaimsOnly.IsChecked.GetValueOrDefault(true))
{
toReturn += BuildWhereSingle("c.[open]", "1", DataTypes.Numeric, DataObject.SearchTypes.Exact);
}
if (chkPatientExcludeWorkedClaims.IsChecked.GetValueOrDefault(true))
{
toReturn += BuildWhereSingle("c.revisit_date", CommonFunctions.ToSQLServerDateTime(DateTime.Now), DataTypes.Date, DataObject.SearchTypes.After);
}
}
else if (_formSearchMode == FormSearchModes.Insurance)
{
// name, age, worked claims
toReturn += BuildWhereSingle("cmp.name", txtInsuranceName.Text.Trim(), DataTypes.Text, DataObject.SearchTypes.Contains);
// Do the "by date" filtering
string dateFilterString = string.Empty;
// Only add SQL if at least one is unchecked
if (!chkAge24.IsChecked.GetValueOrDefault(true) || !chkAge44.IsChecked.GetValueOrDefault(true) || !chkAge45.IsChecked.GetValueOrDefault(true))
{
if (chkAge24.IsChecked.GetValueOrDefault(true)) // 0-24
{
dateFilterString += " date_of_service > DATEADD(\"day\", -24, GETDATE())";
}
if (chkAge44.IsChecked.GetValueOrDefault(true)) // 25-44
{
if (dateFilterString != string.Empty)
{
dateFilterString += " OR ";
}
dateFilterString += " (date_of_service > DATEADD(\"day\", -24, GETDATE()) AND date_of_service < DATEADD(\"d\", -45, GETDATE()))";
}
if (chkAge45.IsChecked.GetValueOrDefault(true)) // 45+
{
if (dateFilterString != string.Empty)
{
dateFilterString += " OR ";
}
dateFilterString += " date_of_service < DATEADD(\"day\", -45, GETDATE())";
}
}
if (dateFilterString != string.Empty)
{
toReturn += string.Format(" AND ({0})", dateFilterString);
}
if (chkInsuranceExcludeWorked.IsChecked.GetValueOrDefault(true))
{
toReturn += BuildWhereSingle("c.[open]", "1", DataTypes.Numeric, DataObject.SearchTypes.Exact);
}
}
}
else
{
toReturn += _whereClause;
if (FilterByInsurance && cmbCarrierFilter.SelectedIndex > 0 && includeCarrierFilter)
{
// Add a company criteria
// toReturn += " AND cmp.id = " + cmbCarrierFilter.SelectedValue;
InsuranceCompanyGroups icg = null;
if (carrierSource.Find(item => item.ToString() == ((InsuranceCompanyGroups)cmbCarrierFilter.SelectedItem).ToString()) != null)
{
icg = (InsuranceCompanyGroups)cmbCarrierFilter.SelectedItem;
if (icg.Group == null)
{
toReturn += BuildWhereSingle("cmp.id", icg.Companies[0].id.ToString(), DataTypes.Numeric, DataObject.SearchTypes.Exact);
}
else
{
toReturn += string.Format(" AND cmp.id IN({0})", ClaimTrackerCommon.CompaniesToInString(icg.Companies));
}
}
}
}
if (!whereOnly)
{
if (_orderByClause == "")
{
toReturn += " ORDER BY c.sent_date";
}
else
{
toReturn += _orderByClause;
}
}
return(toReturn);
}