Exemplo n.º 1
0
        private void byDate()
        {
            ByDate ob = new ByDate();

            ob.ShowDialog();
            if (gridPatientsList.Rows.Count > 0)
            {
                gridPatientsList.Rows.Clear();
            }
            string query = ob.getQuery();

            string[] dates            = query.Split(':');
            var      connectionString = DbConnect.conString;

            using (var connection = new MySqlConnection(connectionString))
            {
                connection.Open();
                MySqlCommand command = new MySqlCommand();
                command.CommandType = CommandType.Text;
                command.Connection  = connection;
                command.CommandText = "SELECT Name,ContactNo,Date,Address,Age,AdharCard,Remark,CenterType,CenterName,CreatedBy FROM PatientsRemark WHERE (CenterType='" + AdvanceSearch.centerType + "' and CenterName='" + AdvanceSearch.centerName + "') and (Date BETWEEN @date1 and @date2)";
                command.Parameters.AddWithValue("@date1", DateTime.Parse(dates[0]).ToString("yyyy-MM-dd"));
                command.Parameters.AddWithValue("@date2", DateTime.Parse(dates[1]).ToString("yyyy-MM-dd"));

                using (var reader = command.ExecuteReader())
                {
                    int srno = 1;
                    while (reader.Read())
                    {
                        string date, fn, cn, add, age, adhar;
                        date  = reader["Date"].ToString();
                        fn    = reader["Name"].ToString();
                        cn    = reader["ContactNo"].ToString();
                        add   = reader["Address"].ToString();
                        age   = reader["Age"].ToString();
                        adhar = reader["AdharCard"].ToString();
                        string remark = reader["Remark"].ToString();
                        string type   = reader["CenterType"].ToString();
                        string cname  = reader["CenterName"].ToString();
                        string cat    = reader["CreatedBy"].ToString();
                        gridPatientsList.Rows.Add(srno, fn, cn, DateTime.Parse(date), add, age, adhar, remark, type, cname, cat);
                        srno++;
                    }
                }
            }
            gridPatientsList.ClearSelection();
        }
Exemplo n.º 2
0
        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (comboBox1.SelectedIndex == 1)
            {
                ByCenter ob = new ByCenter();
                ob.ShowDialog();
                string   query   = ob.getQuery();
                string[] centers = query.Split(':');
                if (centers[0] == "--CENTER TYPE--" || centers[1] == "--CENTER NAME--")
                {
                    MessageBox.Show("Wrong parameter to search !!!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }
                MySqlConnection cn = new MySqlConnection();
                cn.ConnectionString = DbConnect.conString;
                cn.Open();
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection  = cn;
                cmd.CommandText = "Select Name,ContactNo,AdharCard,Date,Remark,CenterType,CenterName From Patients WHERE (CenterType='" + Search.centerType + "' and CenterName='" + Search.centerName + "') and (CenterType='" + centers[0] + "' and CenterName='" + centers[1] + "')";
                MySqlDataAdapter da     = new MySqlDataAdapter(cmd);
                DataTable        table1 = new DataTable("Patients");
                da.Fill(table1);
                cmd.CommandText = "Select AdharCard,Address,Date,Remark,CenterType,CenterName From PatientsRemark WHERE (CenterType='" + Search.centerType + "' and CenterName='" + Search.centerName + "')";
                DataTable table2 = new DataTable("Remarks");
                da.Fill(table2);
                DataSet ds = new DataSet();
                ds.Tables.Add(table1);
                ds.Tables.Add(table2);
                DataRelation Datatablerelation = new DataRelation("Remark Details", ds.Tables[0].Columns[2], ds.Tables[1].Columns[0], false);
                ds.Relations.Add(Datatablerelation);
                dataGrid1.DataSource = ds.Tables[0];
                cn.Close();
            }
            if (comboBox1.SelectedIndex == 2)
            {
                ByName ob = new ByName();
                ob.ShowDialog();
                string name = ob.getQuery();
                if (name == "")
                {
                    MessageBox.Show("Wrong parameter to search !!!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }
                MySqlConnection cn = new MySqlConnection();
                cn.ConnectionString = DbConnect.conString;
                cn.Open();
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection  = cn;
                cmd.CommandText = "Select Name,ContactNo,AdharCard,Date,Remark,CenterType,CenterName From Patients WHERE (CenterType='" + Search.centerType + "' and CenterName='" + Search.centerName + "') and (Name LIKE '%" + name + "%')";
                MySqlDataAdapter da     = new MySqlDataAdapter(cmd);
                DataTable        table1 = new DataTable("Patients");
                da.Fill(table1);
                cmd.CommandText = "Select AdharCard,Address,Date,Remark,CenterType,CenterName From PatientsRemark WHERE CreatedBy='" + created_by + "'";
                DataTable table2 = new DataTable("Remarks");
                da.Fill(table2);
                DataSet ds = new DataSet();
                ds.Tables.Add(table1);
                ds.Tables.Add(table2);
                DataRelation Datatablerelation = new DataRelation("Remark Details", ds.Tables[0].Columns[2], ds.Tables[1].Columns[0], false);
                ds.Relations.Add(Datatablerelation);
                dataGrid1.DataSource = ds.Tables[0];
                cn.Close();
            }
            if (comboBox1.SelectedIndex == 3)
            {
                ByGender ob = new ByGender();
                ob.ShowDialog();
                string gender = ob.getQuery();
                if (gender == "")
                {
                    MessageBox.Show("Please select gender to search !!!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }
                MySqlConnection cn = new MySqlConnection();
                cn.ConnectionString = DbConnect.conString;
                cn.Open();
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection  = cn;
                cmd.CommandText = "Select Name,ContactNo,AdharCard,Date,Remark,CenterType,CenterName From Patients WHERE (CenterType='" + Search.centerType + "' and CenterName='" + Search.centerName + "') and (Gender='" + gender + "')";
                MySqlDataAdapter da     = new MySqlDataAdapter(cmd);
                DataTable        table1 = new DataTable("Patients");
                da.Fill(table1);
                cmd.CommandText = "Select AdharCard,Address,Date,Remark,CenterType,CenterName From PatientsRemark WHERE CreatedBy='" + created_by + "'";
                DataTable table2 = new DataTable("Remarks");
                da.Fill(table2);
                DataSet ds = new DataSet();
                ds.Tables.Add(table1);
                ds.Tables.Add(table2);
                DataRelation Datatablerelation = new DataRelation("Remark Details", ds.Tables[0].Columns[2], ds.Tables[1].Columns[0], false);
                ds.Relations.Add(Datatablerelation);
                dataGrid1.DataSource = ds.Tables[0];
                cn.Close();
            }
            if (comboBox1.SelectedIndex == 4)
            {
                ByDate ob = new ByDate();
                ob.ShowDialog();
                string          query = ob.getQuery();
                string[]        dates = query.Split(':');
                MySqlConnection cn    = new MySqlConnection();
                cn.ConnectionString = DbConnect.conString;
                cn.Open();
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection  = cn;
                cmd.CommandText = "Select Name,ContactNo,AdharCard,Date,Remark,CenterType,CenterName From Patients WHERE (CenterType='" + Search.centerType + "' and CenterName='" + Search.centerName + "') and (Date BETWEEN @date1 and @date2)";
                cmd.Parameters.AddWithValue("@date1", DateTime.Parse(dates[0]).ToString("yyyy-MM-dd"));
                cmd.Parameters.AddWithValue("@date2", DateTime.Parse(dates[1]).ToString("yyyy-MM-dd"));
                MySqlDataAdapter da     = new MySqlDataAdapter(cmd);
                DataTable        table1 = new DataTable("Patients");
                da.Fill(table1);
                cmd.CommandText = "Select AdharCard,Address,Date,Remark,CenterType,CenterName From PatientsRemark WHERE CreatedBy='" + created_by + "'";

                DataTable table2 = new DataTable("Remarks");
                da.Fill(table2);
                DataSet ds = new DataSet();
                ds.Tables.Add(table1);
                ds.Tables.Add(table2);
                DataRelation Datatablerelation = new DataRelation("Remark Details", ds.Tables[0].Columns[2], ds.Tables[1].Columns[0], false);
                ds.Relations.Add(Datatablerelation);
                dataGrid1.DataSource = ds.Tables[0];
                cn.Close();
            }
            if (comboBox1.SelectedIndex == 5)
            {
                //age
                ByAge ob = new ByAge();
                ob.ShowDialog();
                string query = ob.getQuery();
                if (query == "" || query == ":")
                {
                    MessageBox.Show("Wrong parameters to search !!!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }
                string[] ages = query.Split(':');
                if (!ages[0].All(char.IsDigit) || !ages[1].All(char.IsDigit))
                {
                    MessageBox.Show("Wrong parameters to search !!!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }
                MySqlConnection cn = new MySqlConnection();
                cn.ConnectionString = DbConnect.conString;
                cn.Open();
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection  = cn;
                cmd.CommandText = "Select Name,ContactNo,AdharCard,Age,Date,Remark,CenterType,CenterName From Patients WHERE (CenterType='" + Search.centerType + "' and CenterName='" + Search.centerName + "') and (Age >=" + ages[0] + " and Age <=" + ages[1] + ")";
                MySqlDataAdapter da     = new MySqlDataAdapter(cmd);
                DataTable        table1 = new DataTable("Patients");
                da.Fill(table1);
                cmd.CommandText = "Select AdharCard,Address,Age,Date,Remark,CenterType,CenterName From PatientsRemark WHERE CreatedBy='" + created_by + "'";
                DataTable table2 = new DataTable("Remarks");
                da.Fill(table2);
                DataSet ds = new DataSet();
                ds.Tables.Add(table1);
                ds.Tables.Add(table2);
                DataRelation Datatablerelation = new DataRelation("Remark Details", ds.Tables[0].Columns[2], ds.Tables[1].Columns[0], false);
                ds.Relations.Add(Datatablerelation);
                dataGrid1.DataSource = ds.Tables[0];
                cn.Close();
            }
        }