private static void DeleteReplenishmentSchedule(int patientId)
{
SqlConnection conn = null;
try
{
DBHelper myHelper = new DBHelper();
conn = new SqlConnection(myHelper.OmdbConn);
conn.Open();
var dataAdapter = new SqlDataAdapter { DeleteCommand = new SqlCommand { Connection = conn } };
// Setup Insert Command
var iRxId = Int32.Parse(SetCorrectRxId(patientId));
dataAdapter.DeleteCommand.CommandText = string.Format("DELETE FROM REPLENISHMENTSCHEDULE WHERE RX_ID = {0}", iRxId);
dataAdapter.DeleteCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
//var logger = new ExceptionLogger();
//logger.AddLogger(new EventLogLogger());
//logger.LogException(ex, "Could not Delete Replenishment Schedule");
//throw new Exception(ex.Message, ex);
}
finally
{
if (conn != null) conn.Close();
}
}
private static string SetCorrectRxId(int patientId)
{
SqlConnection conn = null;
var sbSql = new StringBuilder();
var result = "";
try
{
DBHelper myHelper = new DBHelper();
conn = new SqlConnection(myHelper.OmdbConn);
conn.Open();
sbSql.Append("SELECT max(rx_id) ");
sbSql.Append("FROM Soft_Contact_Rx ");
sbSql.Append("WHERE patient_id =" + patientId);
var dataAdapter = new SqlDataAdapter(sbSql.ToString(), conn);
// Populate a new data table and bind it to the BindingSource.
var table = new DataTable
{
Locale = System.Globalization.CultureInfo.InvariantCulture
};
dataAdapter.Fill(table);
if (table.Rows[0][0] != null)
{
result = table.Rows[0][0].ToString();
}
if (result.Length == 0)
{
return "0";
}
}
catch (Exception ex)
{
//var logger = new ExceptionLogger();
//logger.AddLogger(new EventLogLogger());
//logger.LogException(ex, "Could not set correct rx id!");
//throw new Exception(ex.Message, ex);
}
finally
{
if (conn != null) conn.Close();
}
return result;
}
private static string GetPatientName(int patientId)
{
var patientName = "";
SqlConnection conn = null;
try
{
DBHelper myHelper = new DBHelper();
conn = new SqlConnection(myHelper.OmdbConn);
conn.Open();
var dataAdapter = new SqlDataAdapter(GetPatientSql(patientId), conn);
var commandBuilder = new SqlCommandBuilder(dataAdapter);
// Populate a new data table and bind it to the BindingSource.
var table = new DataTable
{
Locale = System.Globalization.CultureInfo.InvariantCulture
};
dataAdapter.Fill(table);
if (table.Rows.Count > 0)
{
patientName = table.Rows[0][0].ToString();
}
}
catch (Exception ex)
{
//var logger = new ExceptionLogger();
//logger.AddLogger(new EventLogLogger());
//logger.LogException(ex, "Could not get Patient Name!");
//throw new Exception(ex.Message, ex);
}
finally
{
if (conn != null) conn.Close();
}
return patientName;
}
private void DeleteReplenishmentSchedule()
{
SqlConnection conn = null;
try
{
DBHelper myHelper = new DBHelper();
conn = new SqlConnection(myHelper.OmdbConn);
conn.Open();
var dataAdapter = new SqlDataAdapter
{
DeleteCommand = new SqlCommand
{
Connection = conn,
CommandText =
string.Format(
"DELETE FROM REPLENISHMENTSCHEDULE WHERE RX_ID = {0}",
RxId)
}
};
// Setup Delete Command
dataAdapter.DeleteCommand.ExecuteNonQuery();
// Delete Replenishment Info
dataAdapter = new SqlDataAdapter
{
DeleteCommand = new SqlCommand
{
Connection = conn,
CommandText =
string.Format(
"DELETE FROM Replenishment WHERE RX_ID = {0}",
RxId)
}
};
// Setup Delete Command
dataAdapter.DeleteCommand.ExecuteNonQuery();
// Clear Values
ClearValues();
RefreshReplenishmentSchedule();
}
catch (Exception ex)
{
//var logger = new ExceptionLogger();
//logger.AddLogger(new EventLogLogger());
//logger.LogException(ex, "Could not delete Replenishment Schedule!");
//throw new Exception(ex.Message, ex);
}
finally
{
if (conn != null) conn.Close();
}
}
private void AddReplenishmentScheduleDate(DateTime dtReplenishmentDate)
{
SqlConnection conn = null;
try
{
DBHelper myHelper = new DBHelper();
conn = new SqlConnection(myHelper.OmdbConn);
conn.Open();
var dataAdapter = new SqlDataAdapter
{
InsertCommand = new SqlCommand
{
Connection = conn,
CommandText =
string.Format(
"INSERT INTO REPLENISHMENTSCHEDULE(REPLENISHMENTDATE, RX_ID) VALUES ('{0}', {1})",
dtReplenishmentDate.ToShortDateString(),
RxId)
}
};
// Setup Delete Command
dataAdapter.InsertCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
// var logger = new ExceptionLogger();
// logger.AddLogger(new EventLogLogger());
// logger.LogException(ex, "Could not delete Replenishment Schedule!");
//throw new Exception(ex.Message, ex);
}
finally
{
if (conn != null) conn.Close();
}
}
private void UpdateReplenishmentDate(DateTime? dtReplenishmentDate, int ID)
{
SqlConnection conn = null;
try
{
DBHelper myHelper = new DBHelper();
conn = new SqlConnection(myHelper.OmdbConn);
conn.Open();
var dataAdapter = new SqlDataAdapter();
SqlCommand UpdateCommand = new SqlCommand();
dataAdapter.UpdateCommand = UpdateCommand;
UpdateCommand.Connection = conn;
if (dtReplenishmentDate == null)
{
UpdateCommand.CommandText = "DELETE FROM REPLENISHMENTSCHEDULE WHERE ID = " + ID;
}
else
{
UpdateCommand.CommandText =
string.Format(
"UPDATE REPLENISHMENTSCHEDULE SET REPLENISHMENTDATE = '{0}' WHERE ID = {1}",
dtReplenishmentDate,
ID);
}
// Setup Delete Command
dataAdapter.UpdateCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
//var logger = new ExceptionLogger();
//logger.AddLogger(new EventLogLogger());
//logger.LogException(ex, "Could not update Replenishment Schedule!");
//throw new Exception(ex.Message, ex);
}
finally
{
if (conn != null) conn.Close();
}
}
private void UpdateComplianceDate(DateTime dtComplianceDate, int iDelqt, int ID)
{
SqlConnection conn = null;
try
{
DBHelper myHelper = new DBHelper();
conn = new SqlConnection(myHelper.OmdbConn);
conn.Open();
var dataAdapter = new SqlDataAdapter
{
UpdateCommand = new SqlCommand
{
Connection = conn,
CommandText =
string.Format(
"UPDATE REPLENISHMENTSCHEDULE SET COMPLIANCEDATE = '{0}', DELQT = {1} WHERE ID = {2}",
dtComplianceDate.ToShortDateString(),
iDelqt,
ID)
}
};
// Setup Delete Command
dataAdapter.UpdateCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
//var logger = new ExceptionLogger();
//logger.AddLogger(new EventLogLogger());
//logger.LogException(ex, "Could not update Replenishment Schedule!");
//throw new Exception(ex.Message, ex);
}
finally
{
if (conn != null) conn.Close();
}
}
private void SaveValues()
{
SqlConnection conn = null;
try
{
Cursor = Cursors.WaitCursor;
DBHelper myHelper = new DBHelper();
conn = new SqlConnection(myHelper.OmdbConn);
conn.Open();
var dataAdapter = new SqlDataAdapter
{
SelectCommand = new SqlCommand {Connection = conn},
InsertCommand = new SqlCommand {Connection = conn},
UpdateCommand = new SqlCommand {Connection = conn}
};
// Setup Select Command
dataAdapter.SelectCommand.CommandText = "SELECT * FROM REPLENISHMENT WHERE RX_ID = " + RxId;
// Setup Update Command
var intDoNotSend = 0;
if (cbDoNotSend.Checked)
{
intDoNotSend = 1;
}
// Setup Insert Command
dataAdapter.InsertCommand.CommandText = "INSERT INTO REPLENISHMENT(RX_ID,NOTES,CATEGORY,AUTOMAIL,DONOTSEND,INITIALDISPENSINGDATE) VALUES(@RX_ID,@NOTES,@CATEGORY,@AUTOMAIL,@DONOTSEND,@INITIALDISPENSINGDATE)";
dataAdapter.InsertCommand.Parameters.Add("@RX_ID", SqlDbType.Int).Value = RxId;
dataAdapter.InsertCommand.Parameters.Add("@NOTES", SqlDbType.VarChar).Value = txtRxNotes.Text;
dataAdapter.InsertCommand.Parameters.Add("@CATEGORY", SqlDbType.VarChar).Value = cbReplenishmentNoticeCategory.Text;
dataAdapter.InsertCommand.Parameters.Add("@AUTOMAIL", SqlDbType.VarChar).Value = cmbAutomail.Text;
dataAdapter.InsertCommand.Parameters.Add("@DONOTSEND", SqlDbType.Int).Value = intDoNotSend;
DateTime dtInitialDispensingDate;
if (DateTime.TryParse(txtInitialDispensingDate.Text, out dtInitialDispensingDate))
{
dataAdapter.InsertCommand.Parameters.Add("@INITIALDISPENSINGDATE", SqlDbType.DateTime).Value =
DateTime.Parse(txtInitialDispensingDate.Text);
}
else
{
dataAdapter.InsertCommand.Parameters.Add("@INITIALDISPENSINGDATE", SqlDbType.DateTime).Value =
DBNull.Value;
}
if (DateTime.TryParse(txtInitialDispensingDate.Text, out dtInitialDispensingDate))
{
dataAdapter.UpdateCommand.CommandText = "UPDATE REPLENISHMENT SET NOTES = '" + txtRxNotes.Text +
"', CATEGORY = '" + cbReplenishmentNoticeCategory.Text +
"', AUTOMAIL = '" + cmbAutomail.Text + "', DONOTSEND = " +
intDoNotSend + ", INITIALDISPENSINGDATE = '" +
DateTime.Parse(txtInitialDispensingDate.Text) +
"' WHERE RX_ID = @RX_ID";
}
else
{
dataAdapter.UpdateCommand.CommandText = "UPDATE REPLENISHMENT SET NOTES = '" + txtRxNotes.Text +
"', CATEGORY = '" + cbReplenishmentNoticeCategory.Text +
"', AUTOMAIL = '" + cmbAutomail.Text + "', DONOTSEND = " +
intDoNotSend + " WHERE RX_ID = @RX_ID";
}
dataAdapter.UpdateCommand.Parameters.Add("@RX_ID", SqlDbType.Int).Value = RxId;
// Populate a new data table and bind it to the BindingSource.
var table = new DataTable
{
Locale = System.Globalization.CultureInfo.InvariantCulture
};
dataAdapter.Fill(table);
if (table.Rows.Count > 0)
{
dataAdapter.UpdateCommand.ExecuteNonQuery();
}
else
{
dataAdapter.InsertCommand.ExecuteNonQuery();
}
Cursor = Cursors.Arrow;
}
catch (Exception ex)
{
//var logger = new ExceptionLogger();
//logger.AddLogger(new EventLogLogger());
//logger.LogException(ex, "Could not save Replenishment Values!");
//throw new Exception(ex.Message, ex);
}
finally
{
if (conn != null) conn.Close();
}
}
private void SaveComplianceDates()
{
SqlConnection conn = null;
try
{
Cursor = Cursors.WaitCursor;
DBHelper myHelper = new DBHelper();
conn = new SqlConnection(myHelper.OmdbConn);
conn.Open();
Cursor = Cursors.Arrow;
}
catch (Exception ex)
{
//var logger = new ExceptionLogger();
//logger.AddLogger(new EventLogLogger());
//logger.LogException(ex, "Could not save Replenishment Values!");
//throw new Exception(ex.Message, ex);
}
finally
{
if (conn != null) conn.Close();
}
}
private void RefreshReplenishmentSchedule()
{
SqlConnection conn = null;
try
{
DBHelper myHelper = new DBHelper();
conn = new SqlConnection(myHelper.OmdbConn);
conn.Open();
var _dataReplenishmentScheduleAdapter = new SqlDataAdapter
{
SelectCommand = new SqlCommand
{
Connection = conn,
CommandText =
"SELECT ReplenishmentDate as [Replenishment Date], ComplianceDate as [Compliance Date], Delqt, rx_id, id FROM REPLENISHMENTSCHEDULE WHERE RX_ID = " +
RxId
}
};
// Populate a new data table and bind it to the BindingSource.
var ds = new DataSet();
_dataReplenishmentScheduleAdapter.Fill(ds);
if (ds != null)
{
//set the DataGridView DataSource
dgDates.DataSource = ds;
dgDates.DataMember = "Table";
dgDates.Columns[0].ValueType = typeof(String);
dgDates.Columns[1].ValueType = typeof(String);
dgDates.Columns[2].ValueType = typeof(String);
}
}
catch (Exception ex)
{
//var logger = new ExceptionLogger();
//logger.AddLogger(new EventLogLogger());
//logger.LogException(ex, "Could not go load Replenishment Schedule");
//throw new Exception(ex.Message, ex);
}
finally
{
if (conn != null) conn.Close();
}
}
private void LoadValues()
{
SqlConnection conn = null;
try
{
DBHelper myHelper = new DBHelper();
conn = new SqlConnection(myHelper.OmdbConn);
conn.Open();
var dataAdapter = new SqlDataAdapter
{
SelectCommand = new SqlCommand
{
Connection = conn,
CommandText =
"SELECT * FROM REPLENISHMENT WHERE RX_ID = " +
RxId
}
};
// Setup Select Command
// Populate a new data table and bind it to the BindingSource.
var table = new DataTable
{
Locale = System.Globalization.CultureInfo.InvariantCulture
};
dataAdapter.Fill(table);
if (table.Rows.Count > 0)
{
txtRxNotes.Text = table.Rows[0]["NOTES"].ToString();
cbReplenishmentNoticeCategory.Text = table.Rows[0]["CATEGORY"].ToString();
cmbAutomail.Text = table.Rows[0]["AUTOMAIL"].ToString();
if (table.Rows[0]["DONOTSEND"].ToString() == "1")
{
cbDoNotSend.Checked = true;
}
DateTime dtInitialDispensingDate;
if (DateTime.TryParse(table.Rows[0]["INITIALDISPENSINGDATE"].ToString(), out dtInitialDispensingDate))
{
txtInitialDispensingDate.Text =
DateTime.Parse(table.Rows[0]["INITIALDISPENSINGDATE"].ToString()).ToShortDateString();
}
}
}
catch (Exception ex)
{
//var logger = new ExceptionLogger();
//logger.AddLogger(new EventLogLogger());
//logger.LogException(ex, "Could not load values!");
//throw new Exception(ex.Message, ex);
}
finally
{
if (conn != null) conn.Close();
}
}
private void GetLensData()
{
SqlConnection conn = null;
var sbSql = new StringBuilder(606);
try
{
DBHelper myHelper = new DBHelper();
conn = new SqlConnection(myHelper.OmdbConn);
conn.Open();
sbSql.AppendFormat(@"SELECT vendor.vendor_name1, Soft_Contact_Lens_RX.lens_name, Soft_Contact_Lens_RX.base_curve_mm, ");
sbSql.AppendFormat(@"Soft_Contact_Lens_RX.diameter_mm, Soft_Contact_Lens_RX.sphere_diopter, ");
sbSql.AppendFormat(@"Soft_Contact_Lens_RX.cylinder_diopter, Soft_Contact_Lens_RX.axis_deg, ");
sbSql.AppendFormat(@"Soft_Contact_Lens_RX.add_diopter, code.description, Soft_Contact_RX.Rx_Notes, Soft_Contact_Lens_RX.right_or_left_cd {0}", Environment.NewLine);
sbSql.AppendFormat(@"FROM ((Soft_Contact_Lens_RX INNER JOIN vendor ON Soft_Contact_Lens_RX.lens_brand_cd = ");
sbSql.AppendFormat(@"vendor.vendor_no) LEFT JOIN code ON Soft_Contact_Lens_RX.tint_cd = code.code) INNER JOIN ");
sbSql.AppendFormat(@"Soft_Contact_RX ON Soft_Contact_Lens_RX.rx_id = Soft_Contact_RX.rx_id{0}", Environment.NewLine);
sbSql.Append(" WHERE Soft_Contact_Lens_RX.rx_id =" + RxId);
var dataAdapter = new SqlDataAdapter(sbSql.ToString(), conn);
var commandBuilder = new SqlCommandBuilder(dataAdapter);
// Populate a new data table and bind it to the BindingSource.
var table = new DataTable
{
Locale = System.Globalization.CultureInfo.InvariantCulture
};
dataAdapter.Fill(table);
if (table.Rows.Count == 0)
{
StringBuilder sbSQLNoVendor = new StringBuilder();
sbSQLNoVendor.AppendFormat(@"SELECT '' as vendor_name1, Soft_Contact_Lens_RX.lens_name, Soft_Contact_Lens_RX.base_curve_mm, ");
sbSQLNoVendor.AppendFormat(@"Soft_Contact_Lens_RX.diameter_mm, {0}", Environment.NewLine);
sbSQLNoVendor.AppendFormat(@" Soft_Contact_Lens_RX.sphere_diopter, Soft_Contact_Lens_RX.cylinder_diopter, ");
sbSQLNoVendor.AppendFormat(@"Soft_Contact_Lens_RX.axis_deg, Soft_Contact_Lens_RX.add_diopter, {0}", Environment.NewLine);
sbSQLNoVendor.AppendFormat(@" code.description, Soft_Contact_RX.Rx_Notes{0}, Soft_Contact_Lens_RX.right_or_left_cd ", Environment.NewLine);
sbSQLNoVendor.AppendFormat(@"FROM Soft_Contact_Lens_RX LEFT OUTER JOIN{0}", Environment.NewLine);
sbSQLNoVendor.AppendFormat(@" code ON Soft_Contact_Lens_RX.tint_cd = code.code INNER JOIN{0}", Environment.NewLine);
sbSQLNoVendor.AppendFormat(@" Soft_Contact_RX ON Soft_Contact_Lens_RX.rx_id = Soft_Contact_RX.rx_id{0}", Environment.NewLine);
sbSQLNoVendor.Append(" WHERE Soft_Contact_Lens_RX.rx_id =" + RxId);
var dataAdapterNoVendor = new SqlDataAdapter(sbSQLNoVendor.ToString(), conn);
var commandBuilderNoVendor = new SqlCommandBuilder(dataAdapterNoVendor);
// Populate a new data table and bind it to the BindingSource.
var tableNoVendor = new DataTable
{
Locale = System.Globalization.CultureInfo.InvariantCulture
};
dataAdapterNoVendor.Fill(tableNoVendor);
if (tableNoVendor.Rows.Count > 0)
{
txtRLensType.Text = tableNoVendor.Rows[0]["vendor_name1"].ToString();
txtRSeries.Text = tableNoVendor.Rows[0]["lens_name"].ToString();
txtRBC.Text = tableNoVendor.Rows[0]["base_curve_mm"].ToString();
txtRDia.Text = tableNoVendor.Rows[0]["diameter_mm"].ToString();
txtRSphere.Text = tableNoVendor.Rows[0]["sphere_diopter"].ToString();
txtRCyl.Text = tableNoVendor.Rows[0]["cylinder_diopter"].ToString();
txtRAxis.Text = tableNoVendor.Rows[0]["axis_deg"].ToString();
txtRAdd.Text = tableNoVendor.Rows[0]["add_diopter"].ToString();
txtRTint.Text = tableNoVendor.Rows[0]["description"].ToString();
}
if (tableNoVendor.Rows.Count > 1)
{
txtLLensType.Text = tableNoVendor.Rows[1]["vendor_name1"].ToString();
txtLSeries.Text = tableNoVendor.Rows[1]["lens_name"].ToString();
txtLBC.Text = tableNoVendor.Rows[1]["base_curve_mm"].ToString();
txtLDia.Text = tableNoVendor.Rows[1]["diameter_mm"].ToString();
txtLSphere.Text = tableNoVendor.Rows[1]["sphere_diopter"].ToString();
txtLCyl.Text = tableNoVendor.Rows[1]["cylinder_diopter"].ToString();
txtLAxis.Text = tableNoVendor.Rows[1]["axis_deg"].ToString();
txtLAdd.Text = tableNoVendor.Rows[1]["add_diopter"].ToString();
txtLTint.Text = tableNoVendor.Rows[1]["description"].ToString();
}
}
// Set Eye Data
foreach (DataRow row in table.Rows)
{
SetEye(row);
}
}
catch (Exception ex)
{
//var logger = new ExceptionLogger();
//logger.AddLogger(new EventLogLogger());
//logger.LogException(ex, "Could not get Lens Data!");
throw new Exception(ex.Message, ex);
}
finally
{
if (conn != null) conn.Close();
}
}
public static DataView GetDataView(string strStartDate, string strEndDate)
{
var dt = new DataTable("Report");
dt.Columns.Add("PatientName", typeof(string));
//dt.Columns.Add("PatientAddress", typeof(string));
dt.Columns.Add("RpS", typeof(string));
dt.Columns.Add("NextSchdReplacement", typeof(string));
dt.Columns.Add("Eye", typeof(string));
dt.Columns.Add("Manf", typeof(string));
dt.Columns.Add("Series", typeof(string));
dt.Columns.Add("BC", typeof(string));
dt.Columns.Add("DIA", typeof(string));
dt.Columns.Add("Sphere", typeof(string));
dt.Columns.Add("CYL", typeof(string));
dt.Columns.Add("Axis", typeof(string));
dt.Columns.Add("Add", typeof(string));
dt.Columns.Add("Tint", typeof(string));
dt.Columns.Add("Automail", typeof(string));
dt.Columns.Add("DoNotSend", typeof(string));
try
{
DBHelper myHelper = new DBHelper();
var conn = new SqlConnection(myHelper.OmdbConn);
conn.Open();
DateTime dtNewDate;
DateTime.TryParse(strEndDate, out dtNewDate);
StringBuilder myStringBuilder = new StringBuilder(3478);
myStringBuilder.AppendFormat(@"SELECT Soft_Contact_RX_2.patient_id, Soft_Contact_Lens_RX_1.right_or_left_cd, tbl.first_name, ");
myStringBuilder.AppendFormat(@"tbl.last_name, address.address1, Replenishment.Category, {0}", Environment.NewLine);
myStringBuilder.AppendFormat(@" MIN(tbl.ReplenishmentDate) AS ReplenishmentDate, vendor.vendor_name1, ");
myStringBuilder.AppendFormat(@"Soft_Contact_Lens_RX_1.lens_name, {0}", Environment.NewLine);
myStringBuilder.AppendFormat(@" Soft_Contact_Lens_RX_1.base_curve_mm, Soft_Contact_Lens_RX_1.diameter_mm, ");
myStringBuilder.AppendFormat(@"Soft_Contact_Lens_RX_1.sphere_diopter, {0}", Environment.NewLine);
myStringBuilder.AppendFormat(@" Soft_Contact_Lens_RX_1.cylinder_diopter, Soft_Contact_Lens_RX_1.axis_deg, ");
myStringBuilder.AppendFormat(@"Soft_Contact_Lens_RX_1.add_diopter, code.description, Replenishment.Automail, {0}", Environment.NewLine);
myStringBuilder.AppendFormat(@" Replenishment.DoNotSend, Replenishment.Notes, NULL AS ComplianceDate{0}", Environment.NewLine);
myStringBuilder.AppendFormat(@"FROM patient INNER JOIN{0}", Environment.NewLine);
myStringBuilder.AppendFormat(@" address ON patient.address_no = address.address_no INNER JOIN{0}", Environment.NewLine);
myStringBuilder.AppendFormat(@" (SELECT ReplenishmentSchedule.Rx_ID, ");
myStringBuilder.AppendFormat(@"MAX(ReplenishmentSchedule.ReplenishmentDate) AS ReplenishmentDate, patient_1.last_name, {0}", Environment.NewLine);
myStringBuilder.AppendFormat(@" patient_1.first_name{0}", Environment.NewLine);
myStringBuilder.AppendFormat(@" FROM ReplenishmentSchedule INNER JOIN{0}", Environment.NewLine);
myStringBuilder.AppendFormat(@" Soft_Contact_RX ON ReplenishmentSchedule.Rx_ID = ");
myStringBuilder.AppendFormat(@"Soft_Contact_RX.rx_id INNER JOIN{0}", Environment.NewLine);
myStringBuilder.AppendFormat(@" patient AS patient_1 ON ");
myStringBuilder.AppendFormat(@"Soft_Contact_RX.patient_id = patient_1.patient_no CROSS JOIN{0}", Environment.NewLine);
myStringBuilder.AppendFormat(@" Soft_Contact_Lens_RX{0}", Environment.NewLine);
myStringBuilder.AppendFormat(@" GROUP BY ReplenishmentSchedule.ComplianceDate, ");
myStringBuilder.AppendFormat(@"ReplenishmentSchedule.ReplenishmentDate, ReplenishmentSchedule.Rx_ID, {0}", Environment.NewLine);
myStringBuilder.AppendFormat(@" patient_1.last_name, patient_1.first_name{0}", Environment.NewLine);
myStringBuilder.AppendFormat(@" HAVING (ReplenishmentSchedule.ComplianceDate IS NULL) AND ");
myStringBuilder.AppendFormat(@"(ReplenishmentSchedule.ReplenishmentDate <= '" + DBHelper.GetLastDayOfMonth(dtNewDate.AddMonths(-1)).ToShortDateString() + "' " + ") AND ");
myStringBuilder.AppendFormat(@"(ReplenishmentSchedule.Rx_ID IN{0}", Environment.NewLine);
myStringBuilder.AppendFormat(@" (SELECT rx_id{0}", Environment.NewLine);
myStringBuilder.AppendFormat(@" FROM Soft_Contact_RX AS ");
myStringBuilder.AppendFormat(@"Soft_Contact_RX_1))) AS tbl INNER JOIN{0}", Environment.NewLine);
myStringBuilder.AppendFormat(@" Soft_Contact_RX AS Soft_Contact_RX_2 ON tbl.Rx_ID = Soft_Contact_RX_2.rx_id ");
myStringBuilder.AppendFormat(@"INNER JOIN{0}", Environment.NewLine);
myStringBuilder.AppendFormat(@" Soft_Contact_Lens_RX AS Soft_Contact_Lens_RX_1 ON Soft_Contact_RX_2.rx_id = ");
myStringBuilder.AppendFormat(@"Soft_Contact_Lens_RX_1.rx_id ON {0}", Environment.NewLine);
myStringBuilder.AppendFormat(@" patient.patient_no = Soft_Contact_RX_2.patient_id INNER JOIN{0}", Environment.NewLine);
myStringBuilder.AppendFormat(@" Replenishment ON Soft_Contact_RX_2.rx_id = Replenishment.Rx_ID INNER JOIN{0}", Environment.NewLine);
myStringBuilder.AppendFormat(@" vendor ON Soft_Contact_Lens_RX_1.lens_brand_cd = vendor.vendor_no LEFT OUTER JOIN{0}", Environment.NewLine);
myStringBuilder.AppendFormat(@" code ON Soft_Contact_Lens_RX_1.tint_cd = code.code{0}", Environment.NewLine);
myStringBuilder.AppendFormat(@"GROUP BY tbl.Rx_ID, tbl.last_name, tbl.first_name, Soft_Contact_RX_2.patient_id, ");
myStringBuilder.AppendFormat(@"Soft_Contact_Lens_RX_1.right_or_left_cd, address.address1, {0}", Environment.NewLine);
myStringBuilder.AppendFormat(@" Replenishment.Category, Replenishment.Automail, Replenishment.DoNotSend, ");
myStringBuilder.AppendFormat(@"vendor.vendor_name1, Soft_Contact_Lens_RX_1.lens_name, {0}", Environment.NewLine);
myStringBuilder.AppendFormat(@" Soft_Contact_Lens_RX_1.base_curve_mm, Soft_Contact_Lens_RX_1.diameter_mm, ");
myStringBuilder.AppendFormat(@"Soft_Contact_Lens_RX_1.sphere_diopter, {0}", Environment.NewLine);
myStringBuilder.AppendFormat(@" Soft_Contact_Lens_RX_1.cylinder_diopter, Soft_Contact_Lens_RX_1.axis_deg, ");
myStringBuilder.AppendFormat(@"Soft_Contact_Lens_RX_1.add_diopter, Replenishment.Notes, {0}", Environment.NewLine);
myStringBuilder.AppendFormat(@" code.description{0}", Environment.NewLine);
myStringBuilder.AppendFormat(@"ORDER BY last_name, first_name, ReplenishmentDate ASC, right_or_left_cd ASC");
var dataAdapter = new SqlDataAdapter(myStringBuilder.ToString(), conn);
var table = new DataTable
{
Locale = System.Globalization.CultureInfo.InvariantCulture
};
dataAdapter.Fill(table);
for (int i = 0; i <= table.Rows.Count-1; i++)
{
DataRow currentRow = table.Rows[i];
DataRow nextRow = table.Rows[i+1];
if (currentRow[0].ToString().Equals(nextRow[0].ToString()))
{
// Add Contact Lens 1
AddContactLens(ref dt, ref currentRow);
// Add Contact Lens 2
AddContactLens(ref dt, ref nextRow);
// Add Replenishment Notes
AddReplenishmentNotes(ref dt, ref currentRow);
// Add Blank Line
AddBlankLine(ref dt);
i++;
}
else
{
// Add Contact Lens 1
AddContactLens(ref dt, ref currentRow);
// Add Replenishment Notes
AddReplenishmentNotes(ref dt, ref currentRow);
// Add Blank Line
AddBlankLine(ref dt);
}
}
}
catch {}
var dv = dt.DefaultView;
return dv;
}
public static DataView GetDataView(string strStartDate, string strEndDate)
{
var dt = new DataTable("Report");
dt.Columns.Add("PatientName", typeof(string));
//dt.Columns.Add("PatientAddress", typeof(string));
dt.Columns.Add("RpS", typeof(string));
dt.Columns.Add("NextSchdReplacement", typeof(string));
dt.Columns.Add("Eye", typeof(string));
dt.Columns.Add("Manf", typeof(string));
dt.Columns.Add("Series", typeof(string));
dt.Columns.Add("BC", typeof(string));
dt.Columns.Add("DIA", typeof(string));
dt.Columns.Add("Sphere", typeof(string));
dt.Columns.Add("CYL", typeof(string));
dt.Columns.Add("Axis", typeof(string));
dt.Columns.Add("Add", typeof(string));
dt.Columns.Add("Tint", typeof(string));
dt.Columns.Add("Automail", typeof(string));
dt.Columns.Add("DoNotSend", typeof(string));
try
{
DBHelper myHelper = new DBHelper();
var conn = new SqlConnection(myHelper.OmdbConn);
conn.Open();
var myStringBuilder = new StringBuilder();
myStringBuilder.AppendFormat(@"SELECT Soft_Contact_RX.patient_id,{0}", Environment.NewLine);
myStringBuilder.AppendFormat(@"Soft_Contact_Lens_RX.right_or_left_cd, {0}", Environment.NewLine);
myStringBuilder.AppendFormat(@"patient.first_name, {0}", Environment.NewLine);
myStringBuilder.AppendFormat(@"patient.last_name, {0}", Environment.NewLine);
myStringBuilder.AppendFormat(@"address.address1, {0}", Environment.NewLine);
myStringBuilder.AppendFormat(@"Replenishment.Category, {0}", Environment.NewLine);
myStringBuilder.AppendFormat(@"ReplenishmentSchedule.ReplenishmentDate, {0}", Environment.NewLine);
myStringBuilder.AppendFormat(@"vendor.vendor_name1, {0}", Environment.NewLine);
myStringBuilder.AppendFormat(@"Soft_Contact_Lens_RX.lens_name, {0}", Environment.NewLine);
myStringBuilder.AppendFormat(@"Soft_Contact_Lens_RX.base_curve_mm, {0}", Environment.NewLine);
myStringBuilder.AppendFormat(@"Soft_Contact_Lens_RX.diameter_mm, {0}", Environment.NewLine);
myStringBuilder.AppendFormat(@"Soft_Contact_Lens_RX.sphere_diopter, {0}", Environment.NewLine);
myStringBuilder.AppendFormat(@"Soft_Contact_Lens_RX.cylinder_diopter, {0}", Environment.NewLine);
myStringBuilder.AppendFormat(@"Soft_Contact_Lens_RX.axis_deg, {0}", Environment.NewLine);
myStringBuilder.AppendFormat(@"Soft_Contact_Lens_RX.add_diopter, {0}", Environment.NewLine);
myStringBuilder.AppendFormat(@"code.description, {0}", Environment.NewLine);
myStringBuilder.AppendFormat(@"Replenishment.Automail, {0}", Environment.NewLine);
myStringBuilder.AppendFormat(@"Replenishment.DoNotSend, {0}", Environment.NewLine);
myStringBuilder.AppendFormat(@"Replenishment.Notes {0}, ", Environment.NewLine);
myStringBuilder.AppendFormat(@"ReplenishmentSchedule.ComplianceDate {0}", Environment.NewLine);
myStringBuilder.AppendFormat(@"FROM ((((([dbo].[patient] {0}", Environment.NewLine);
myStringBuilder.AppendFormat(@"INNER JOIN ([dbo].[Soft_Contact_RX]{0}", Environment.NewLine);
myStringBuilder.AppendFormat(@"INNER JOIN [dbo].[Soft_Contact_Lens_RX]{0}", Environment.NewLine);
myStringBuilder.AppendFormat(@"ON Soft_Contact_RX.rx_id = Soft_Contact_Lens_RX.rx_id) {0}", Environment.NewLine);
myStringBuilder.AppendFormat(@"ON patient.patient_no = Soft_Contact_RX.patient_id) {0}", Environment.NewLine);
myStringBuilder.AppendFormat(@"INNER JOIN [dbo].[address]{0}", Environment.NewLine);
myStringBuilder.AppendFormat(@"ON patient.address_no = address.address_no) {0}", Environment.NewLine);
myStringBuilder.AppendFormat(@"INNER JOIN [dbo].[vendor] {0}", Environment.NewLine);
myStringBuilder.AppendFormat(@"ON Soft_Contact_Lens_RX.lens_brand_cd = vendor.vendor_no) {0}", Environment.NewLine);
myStringBuilder.AppendFormat(@"LEFT JOIN [dbo].[code] ON Soft_Contact_Lens_RX.tint_cd = ");
myStringBuilder.AppendFormat(@"code.code) {0}", Environment.NewLine);
myStringBuilder.AppendFormat(@"INNER JOIN [dbo].[Replenishment] {0}", Environment.NewLine);
myStringBuilder.AppendFormat(@"ON Soft_Contact_RX.rx_id = Replenishment.rx_id) {0}", Environment.NewLine);
myStringBuilder.AppendFormat(@"INNER JOIN [dbo].[ReplenishmentSchedule] {0}", Environment.NewLine);
myStringBuilder.AppendFormat(@"ON Soft_Contact_RX.rx_id = ReplenishmentSchedule.rx_id ");
//myStringBuilder.AppendFormat(@"WHERE ReplenishmentSchedule.ReplenishmentDate Between '" + DBHelper.GetFirstDayOfMonth(DateTime.Now.AddMonths(1)).ToShortDateString() + "' And '" + DBHelper.GetLastDayOfMonth(DateTime.Now.AddMonths(1)).ToShortDateString() + "' ");
myStringBuilder.AppendFormat(@"WHERE ReplenishmentSchedule.ReplenishmentDate Between '" + strStartDate + "' And '" + strEndDate + "' ");
myStringBuilder.AppendFormat(@"AND LEN(Replenishment.Automail) > 0 ");
myStringBuilder.AppendFormat(@"AND ReplenishmentSchedule.ComplianceDate IS NULL ");
myStringBuilder.AppendFormat(@"ORDER BY patient.last_name, patient.first_name, ReplenishmentSchedule.ReplenishmentDate ASC, Soft_Contact_Lens_RX.right_or_left_cd ASC {0}", Environment.NewLine);
var dataAdapter = new SqlDataAdapter(myStringBuilder.ToString(), conn);
var table = new DataTable
{
Locale = System.Globalization.CultureInfo.InvariantCulture
};
dataAdapter.Fill(table);
DataTable distinctTable = table.DefaultView.ToTable( /*distinct*/ true);
table = distinctTable;
for (int i = 0; i <= table.Rows.Count - 1; i++)
{
DataRow currentRow = table.Rows[i];
DataRow nextRow = table.Rows[i + 1];
if (currentRow[0].ToString().Equals(nextRow[0].ToString()))
{
// Add Contact Lens 1
AddContactLens(ref dt, ref currentRow);
// Add Contact Lens 2
AddContactLens(ref dt, ref nextRow);
// Add Replenishment Notes
AddReplenishmentNotes(ref dt, ref currentRow);
// Add Blank Line
AddBlankLine(ref dt);
i++;
}
else
{
// Add Contact Lens 1
AddContactLens(ref dt, ref currentRow);
// Add Replenishment Notes
AddReplenishmentNotes(ref dt, ref currentRow);
// Add Blank Line
AddBlankLine(ref dt);
}
}
}
catch (Exception ex)
{
//throw new Exception(ex.Message, ex);
}
var dv = dt.DefaultView;
return dv;
}
private void FindPatient()
{
SqlConnection conn = null;
try
{
Cursor = Cursors.WaitCursor;
dgResults.DataSource = null;
AddColumns();
DBHelper myHelper = new DBHelper();
conn = new SqlConnection(myHelper.OmdbConn);
conn.Open();
var dataAdapter = new SqlDataAdapter(GetSql(), conn);
// Populate a new data table and bind it to the BindingSource.
var table = new DataTable();
dataAdapter.Fill(table);
dbBindSource.DataSource = table;
// you can make it grid readonly.
dgResults.ReadOnly = true;
// finally bind the data to the grid
dgResults.DataSource = dbBindSource;
txtPatientsFound.Text = table.Rows.Count.ToString();
Cursor = Cursors.Arrow;
}
catch (Exception ex)
{
//var logger = new ExceptionLogger();
//logger.AddLogger(new EventLogLogger());
//logger.LogException(ex, "Find Patient Error!");
//throw new Exception(ex.Message, ex);
}
finally
{
if (conn != null) conn.Close();
}
}