listBox1.ScrollAlwaysVisible = true;
            listBox1.HorizontalScrollbar = true;
            string connect = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=E:\An 3 - Sem 1\BD - proiect sincretic\proiectTema2\proiectTema2\Database1.mdf;Integrated Security=True";
            SqlConnection con = new SqlConnection(connect);

            con.Open();
            string stmt = "select IdConsultatie, IdPacient, CostTotal from Consultatii";
            SqlDataAdapter da = new SqlDataAdapter(stmt, con);
            DataSet ds = new DataSet();
            da.Fill(ds, "Consultatii");
            DataRow[] found = ds.Tables["Consultatii"].Select();
            con.Close();
            da.Dispose();
            ds.Dispose();

            con.Open();
            string stmt2 = "select IdConsultatie, SumaTotala from Chitante";
            SqlDataAdapter da2 = new SqlDataAdapter(stmt2, con);
            DataSet ds2 = new DataSet();
            da2.Fill(ds2, "Chitante");
            DataRow[] chitante = ds2.Tables["Chitante"].Select();
            con.Close();
            da2.Dispose();
            ds2.Dispose();

            string ultimaSuma = null;

            string consulAchitata = null;

            foreach (DataRow row in found)
            {
                foreach (DataRow row2 in chitante)
                {
                    foreach (DataRow test in chitante)
                    {
                        if (row["IdConsultatie"].ToString() == test["IdConsultatie"].ToString())
                        {
                            ultimaSuma = test["SumaTotala"].ToString();
                        }
                    }
                    if (row["IdConsultatie"].ToString() == row2["IdConsultatie"].ToString() && row2["SumaTotala"].ToString() == ultimaSuma && row["CostTotal"].ToString()==ultimaSuma)
                    {
                        consulAchitata = row["IdConsultatie"].ToString();
                        //listBox1.Items.Add(row["IdConsultatie"].ToString() + "\t" + row["IdPacient"].ToString() + " \t" + row["CostTotal"].ToString() + "\t" + row2["SumaTotala"].ToString()); //CHITANTE ACHITATE
                    }
                }
                if (row["IdConsultatie"].ToString()!=consulAchitata)
                {
                    con.Open();
                    string stmt3 = "select Nume, Prenume, CNP, Telefon from Pacienti where IdPacient='"+row["IdPacient"].ToString()+"'";
                    SqlDataAdapter da3 = new SqlDataAdapter(stmt3, con);
                    DataSet ds3 = new DataSet();
                    da3.Fill(ds3, "Pacienti");
                    DataRow[] numeP = ds3.Tables["Pacienti"].Select();
                    string nume = numeP[0]["Nume"].ToString();
                    string prenume = numeP[0]["Prenume"].ToString();
                    string cnp = numeP[0]["Cnp"].ToString();
                    string telefon = numeP[0]["Telefon"].ToString();
                    con.Close();
                    da3.Dispose();
                    ds3.Dispose();
                    listBox1.Items.Add(nume + " " + prenume + "\t" + "CNP: " + cnp + "\t" + "Nr. consultatie: " + row["IdConsultatie"].ToString() + " \t" + "Cost total: " + row["CostTotal"].ToString() + "\t" + "Nr. telefon: " + telefon + "\t"); //CHITANTE NEACHITATE
                }
            }
        }

        private void cautaBTN_Click(object sender, EventArgs e)
        {
            string cnp = cautaCnpTB.Text;
            int count = 0;
            foreach (var items in listBox1.Items)
            {
                string item = items.ToString();
                string[] split = item.Split('\t');
                string cnpL = split[1].Substring(5);
                if (cnpL==cnp)
                {
                    listBox1.SelectedIndex = count;
                    break;
                }
                count++;
            }
            //listBox1.Focus();
        }