private void Search()
{
appointment_audit util = new appointment_audit();
string sql = "SELECT * FROM appointment_audit WHERE CAST(FLOOR(CAST(date_changed AS float)) AS DATETIME) >= '" + dtpStartDate.Value.ToShortDateString() +
"' AND CAST(FLOOR(CAST(date_changed AS float)) AS DATETIME) < '" + dtpStartDate.Value.AddDays((cmbDuration.SelectedIndex + 1)).ToShortDateString() + "'";
if (cmbUserList.SelectedIndex > 0)
{
sql += " AND USER_CHANGED = '" + cmbUserList.Text + "'";
}
DataTable results = util.Search(sql);
dgvAppointments.Rows.Clear();
foreach (DataRow aMatch in results.Rows)
{
util.Load(aMatch);
string apptDate;
if (util.change_type == appointment_audit.ChangeTypes.Created || util.change_type == appointment_audit.ChangeTypes.CreatedBroken)
{
if (util["n_APPTDATE"] == DBNull.Value)
{
apptDate = "";
}
else
{
apptDate = util.n_APPTDATE.ToShortDateString();
}
dgvAppointments.Rows.Add(util.DATE_CHANGED.ToString("M/d h:mm:ss"), util.USER_CHANGED.Trim(), util.n_PATNAME,
apptDate, util.change_type.ToString(), util.n_APPTID, FormatChangeList(util.ChangeList));
}
else
{
if (util["APPTDATE"] == DBNull.Value)
{
apptDate = "";
}
else
{
apptDate = util.APPTDATE.ToShortDateString();
}
dgvAppointments.Rows.Add(util.DATE_CHANGED.ToString("M/d h:mm:ss"), util.USER_CHANGED.Trim(), util.PATNAME,
apptDate, util.change_type.ToString(), util.APPTID, FormatChangeList(util.ChangeList));
}
}
lblChangedAppointments.Text = string.Format("Changed Appointments ({0})", dgvAppointments.Rows.Count);
}
private void InitializeUserList()
{
appointment_audit utility = new appointment_audit();
cmbUserList.Items.Clear();
cmbUserList.Items.Add("All");
foreach (string u in utility.UserList)
{
cmbUserList.Items.Add(u);
}
}
private void btnChangeType_Click(object sender, EventArgs e)
{
appointment_audit aa = new appointment_audit();
DataTable matches = aa.Search("SELECT * FROM appointment_audit WHERE change_type is null");
foreach (DataRow aRow in matches.Rows)
{
aa = new appointment_audit();
aa.Load(aRow);
aa.change_type = aa.DiscoverChangeType();
aa.Save();
}
MessageBox.Show(this, "Action Complete. " + matches.Rows.Count + " rows updated.");
}
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
}
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));
}
}
private void Search()
{
appointment_audit util = new appointment_audit();
int deletedRows = 0;
int brokenRows = 0;
int createdRows = 0;
int timeChange = 0;
int statusChange = 0;
int otherChange = 0;
List <int> total = new List <int>()
{
0, 0, 0
};
List <int> newPatient = new List <int>()
{
0, 0, 0
};
List <int> totalDaysBetween = new List <int>()
{
0, 0, 0
};
List <int> totalApptsCreated = new List <int>()
{
0, 0, 0
};
const int DentalIndex = 0;
const int HygieneIndex = 1;
const int OtherIndex = 2;
int currentTypeIndex = 0;
List <List <string> > patientIDs = new List <List <string> >();
#region Individual Stats
string sql = "SELECT * FROM appointment_audit WHERE CAST(FLOOR(CAST(date_changed AS float)) AS DATETIME) >= '" + dtpStartDate.Value.ToShortDateString() +
"' AND CAST(FLOOR(CAST(date_changed AS float)) AS DATETIME) < '" + dtpStartDate.Value.AddDays((cmbDuration.SelectedIndex + 1)).ToShortDateString() + "'";
if (cmbUserList.SelectedIndex > 0)
{
sql += " AND USER_CHANGED = '" + cmbUserList.Text + "'";
}
DataTable results = util.Search(sql);
dgvAppointments.Rows.Clear();
string lastPatient = "";
DateTime lastChangeDate = DateTime.Now;
foreach (DataRow aMatch in results.Rows)
{
util.Load(aMatch);
string apptDate;
if (util.change_type == appointment_audit.ChangeTypes.Created || util.change_type == appointment_audit.ChangeTypes.CreatedBroken)
{
if (util["n_APPTDATE"] == DBNull.Value)
{
apptDate = "";
}
else
{
apptDate = util.n_APPTDATE.ToShortDateString();
}
dgvAppointments.Rows.Add(util.DATE_CHANGED.ToString("M/d h:mm:ss"), util.USER_CHANGED.Trim(), util.n_PATNAME,
apptDate, util.change_type.ToString(), util.n_APPTID, FormatChangeList(util.ChangeList));
}
else
{
bool isGrouped = false;
if (chkGroupSimilar.Checked)
{
if (util.PATNAME == lastPatient && util.DATE_CHANGED == lastChangeDate)
{
if (dgvAppointments.Rows.Count > 0)
{
dgvAppointments.Rows[dgvAppointments.Rows.Count - 1].Cells[colApptDate.DisplayIndex].Value = "Multiple";
dgvAppointments.Rows[dgvAppointments.Rows.Count - 1].Cells[colApptID.DisplayIndex].Value = "Multiple";
isGrouped = true;
}
}
else
{
lastPatient = util.PATNAME;
lastChangeDate = util.DATE_CHANGED;
}
}
if (!isGrouped)
{
if (util["APPTDATE"] == DBNull.Value)
{
apptDate = "";
}
else
{
apptDate = util.APPTDATE.ToShortDateString();
}
dgvAppointments.Rows.Add(util.DATE_CHANGED.ToString("M/d h:mm:ss"), util.USER_CHANGED.Trim(), util.PATNAME,
apptDate, util.change_type.ToString(), util.APPTID, FormatChangeList(util.ChangeList));
}
}
#region General Stats
switch (util.change_type)
{
case appointment_audit.ChangeTypes.Created:
createdRows++;
break;
case appointment_audit.ChangeTypes.CreatedBroken:
createdRows++;
brokenRows++;
break;
case appointment_audit.ChangeTypes.Broken:
brokenRows++;
break;
case appointment_audit.ChangeTypes.Deleted:
deletedRows++;
break;
case appointment_audit.ChangeTypes.Status:
statusChange++;
break;
case appointment_audit.ChangeTypes.Time_Operatory:
timeChange++;
break;
case appointment_audit.ChangeTypes.Other:
otherChange++;
break;
case appointment_audit.ChangeTypes.Unbroken:
otherChange++;
break;
}
List <string> thisPatient = new List <string>()
{
util.PATID.ToString(), util.PATDB.ToString()
};
bool found = false;
foreach (List <string> aPatient in patientIDs)
{
if (aPatient[0] == thisPatient[0] && aPatient[1] == thisPatient[1])
{
found = true;
break;
}
}
if (!found)
{
patientIDs.Add(thisPatient);
}
#endregion
#region Dental / Hygiene stats
if (util.ProviderIDString.StartsWith("D"))
{
currentTypeIndex = DentalIndex;
}
else if (util.ProviderIDString.StartsWith("H"))
{
currentTypeIndex = HygieneIndex;
}
else
{
currentTypeIndex = OtherIndex;
}
total[currentTypeIndex]++;
if (util.change_type == appointment_audit.ChangeTypes.Created)
{
// Check for changes to average creation date -> schedule date
if (util.n_APPTDATE > new DateTime(1900, 1, 1))
{
totalApptsCreated[currentTypeIndex]++;
totalDaysBetween[currentTypeIndex] += Convert.ToInt32((util.n_APPTDATE - util.n_CREATEDATE).TotalDays);
}
else if (util.APPTDATE > new DateTime(1990, 1, 1))
{
totalApptsCreated[currentTypeIndex]++;
totalDaysBetween[currentTypeIndex] += Convert.ToInt32((util.APPTDATE - util.CREATEDATE).TotalDays);
}
// Check to see if it's for a new patient
if (util.IsNewPatient)
{
newPatient[currentTypeIndex]++;
}
}
#endregion
}
lblChangedAppointments.Text = string.Format("Changed Appointments ({0})", dgvAppointments.Rows.Count);
#endregion
lblBroken.Text = brokenRows.ToString();
lblCreated.Text = createdRows.ToString();
lblDeleted.Text = deletedRows.ToString();
lblStatus.Text = statusChange.ToString();
lblTime.Text = timeChange.ToString();
lblTotalPatients.Text = patientIDs.Count.ToString();
lblOther.Text = otherChange.ToString();
lblTotalChanges.Text = results.Rows.Count.ToString();
lblDentalTotal.Text = total[DentalIndex].ToString();
if (totalDaysBetween[DentalIndex] > 0)
{
lblDentalAverageDays.Text = ((double)totalDaysBetween[DentalIndex] / (double)totalApptsCreated[DentalIndex]).ToString("0.0");
}
else
{
lblDentalAverageDays.Text = "N/A";
}
lblDentalNewPatients.Text = newPatient[DentalIndex].ToString();
lblHygieneTotal.Text = total[HygieneIndex].ToString();
if (totalDaysBetween[HygieneIndex] > 0)
{
lblHygieneAverageDays.Text = ((double)totalDaysBetween[HygieneIndex] / (double)totalApptsCreated[HygieneIndex]).ToString("0.0");
}
else
{
lblHygieneAverageDays.Text = "N/A";
}
lblHygieneNewPatients.Text = newPatient[HygieneIndex].ToString();
lblOtherTotal.Text = total[OtherIndex].ToString();
if (totalDaysBetween[OtherIndex] > 0)
{
lblOtherAverageDays.Text = ((double)totalDaysBetween[OtherIndex] / (double)totalApptsCreated[OtherIndex]).ToString("0.0");
}
else
{
lblOtherAverageDays.Text = "N/A";
}
lblOtherNewPatients.Text = newPatient[OtherIndex].ToString();
}
