예제 #1
0
        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();
            }
        }
예제 #2
0
        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();
            }
        }
예제 #13
0
        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;
        }
예제 #14
0
        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;
        }
예제 #15
0
        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();
            }
        }