/// <summary>
/// Updates a field to the new value, but makes a log of the change in the change log if necessary
/// </summary>
/// <param name="toSave">the claim object that will be updated</param>
/// <param name="field_name">the name of the field to be updated</param>
/// <param name="newValue">the new value of the field</param>
private void SaveWithCheckForChange(ref claim toSave, string field_name, object newValue)
{
object oldValue;
try
{
oldValue = toSave[field_name];
}
catch
{
// Make sure not to pass in an invalid field name here, or it will be confusing
// and crash later one
oldValue = null;
}
bool valuesEqual = CheckValuesEqual(toSave[field_name], newValue);
if (!valuesEqual)
{
claim_change_log ccl = new claim_change_log();
AddChange(toSave.id, field_name, toSave[field_name], newValue);
toSave[field_name] = newValue;
}
}
private string GenerateText(claim_change_log ccl)
{
string toReturn = ccl.field_info;
toReturn += " changed from " + ccl.old_data;
toReturn += " to " + ccl.new_data;
return(toReturn);
}
private void AddChange(int claim_id, string field_name, object oldValue, object newValue)
{
claim_change_log ccl = new claim_change_log();
ccl.claim_id = claim_id;
ccl.old_data = DataToString(oldValue);
ccl.new_data = DataToString(newValue);
ccl.field_info = field_name;
ccl.change_date = DateTime.Now;
ccl.Save();
}
/// <summary>
/// Like the SaveWithCheckForChange, but uses a special format to deal with the fact that
/// we don't show the default date of 3/2/1753
/// </summary>
/// <param name="toSave"></param>
/// <param name="p"></param>
/// <param name="nullable"></param>
private void SaveWithCheckForChangeSpecialDateTime(ref claim toSave, string field_name, DateTime?newValue)
{
DateTime?oldValue;
if (toSave[field_name] == DBNull.Value)
{
oldValue = new DateTime?();
}
else
{
oldValue = (DateTime?)toSave[field_name];
}
bool valuesEqual;
if (newValue == null)
{
if (!oldValue.HasValue)
{
valuesEqual = true;
}
else if (oldValue.Value.Year == 1753)
{
valuesEqual = true;
}
else
{
valuesEqual = false;
}
}
else
{
if (oldValue.HasValue)
{
valuesEqual = CheckValuesEqual(oldValue, newValue);
}
else
{
valuesEqual = false;
}
}
if (!valuesEqual)
{
claim_change_log ccl = new claim_change_log();
AddChange(toSave.id, field_name, toSave[field_name], newValue);
toSave[field_name] = newValue;
}
}
/// <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);
}