using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace bd
{
    public partial class Form2 : Form
    {
        public SqlConnection sql = null;

        public Form2(SqlConnection sqlConnection)
        {
            InitializeComponent();
// НЕ ПОМНЮ ЗАЧЕМ ЗДЕСЬ ТОЖЕ ЭТА СТРОКА, такая же внизу
            string ConnectionString = @"Data Source=192.168.112.103;Initial Catalog=db22204;User ID=User032;Password=PASSWORD;Integrated Security=False";
            sqlConnection = new SqlConnection(ConnectionString);

            sqlConnection.Open();
            sql = sqlConnection;
			// У тебя такое было, просто список счетов
            SqlCommand command1 = new SqlCommand("SELECT txtAccountTypeName FROM  tblAccountType", sqlConnection);
            SqlDataReader reader1 = command1.ExecuteReader();
            var sp1 = new List<string>();
            while (reader1.Read())
            {
                sp1.Add(reader1.GetString(0));
            }
            reader1.Close();
            txtAccountTypeName.DataSource = sp1;
			//СПИСОК СУЩЕСТВУЮЩИХ ФИО
            SqlCommand command2 = new SqlCommand("SELECT concat(txtClientSurname, ' ', txtClientName, ' ', txtClientSecondName) FROM  tblClient", sqlConnection);
            SqlDataReader reader2 = command2.ExecuteReader();
            var sp2 = new List<string>();
            while (reader2.Read())
            {
                sp2.Add(reader2.GetString(0));
            }
            reader2.Close();
            Client.DataSource = sp2;
            sqlConnection.Close();
        }

        private void Form2_Load(object sender, EventArgs e)
        {

        }
        private void Add_Click(object sender, EventArgs e)
        {
            int idperson; // АЙДИ ДЛЯ ФИО
            string ConnectionString = @"Data Source=192.168.112.103;Initial Catalog=db22204;User ID=User032;Password=PASSWORD;Integrated Security=False";

            if (Client.Items.Count > 0 && txtAccountTypeName.Items.Count > 0 && datAccountBegin.TextLength > 0 && txtAccountNumber.TextLength > 0 && txtAccountNumber.TextLength > 0 && fltAccountSum.TextLength > 0)
            {
                sql.Open();
				// СВЯЗКА ФИО ПО АЙДИ
                var a = Client.Text.ToString();
                var b = a.Split(' ');
                using (SqlConnection connection = new SqlConnection(ConnectionString))
                {
                    connection.Open();
                    SqlCommand sqlCommand1 = new SqlCommand("SELECT intClientId FROM tblClient WHERE (txtClientSurname = '" + b[0] + "' and txtClientName = '" + b[1] + "' and txtClientSecondName = '" + b[2] + "')", sql);
                    idperson = (int)sqlCommand1.ExecuteScalar();
                    sqlCommand1.ExecuteNonQuery();
                    connection.Close();
                }

                SqlCommand sqlCommand = new SqlCommand(
                $"INSERT INTO[tblAccount](intClientId, intAccountTypeId, datAccountBegin, datAccountEnd, txtAccountNumber, fltAccountSum) VALUES (@intClientId, @intAccountTypeId, @datAccountBegin, @datAccountEnd, @txtAccountNumber, @fltAccountSum)", sql
                );
				// Связка списка счетов по id, у тебя было по аналогии
                SqlCommand command2 = new SqlCommand("(Select tblAccountType.intAccountTypeId FROM tblAccountType WHERE txtAccountTypeName = '" + txtAccountTypeName.SelectedItem.ToString() + "') ", sql);
                SqlDataReader reader = command2.ExecuteReader();
                reader.Read();
                var id = reader.GetInt32(0);
                reader.Close();

                DateTime date = DateTime.Parse(datAccountBegin.Text);
                DateTime dateEnd = DateTime.Parse(datAccountEnd.Text);
                sqlCommand.Parameters.AddWithValue("intClientId", idperson);
                sqlCommand.Parameters.AddWithValue("intAccountTypeId", id);
                sqlCommand.Parameters.AddWithValue("datAccountBegin", $"{date.Month}/{date.Day}/{date.Year}");
                sqlCommand.Parameters.AddWithValue("datAccountEnd", $"{dateEnd.Month}/{dateEnd.Day}/{dateEnd.Year}");
                sqlCommand.Parameters.AddWithValue("txtAccountNumber", txtAccountNumber.Text);
                sqlCommand.Parameters.AddWithValue("fltAccountSum", int.Parse(fltAccountSum.Text));

                sqlCommand.ExecuteNonQuery();
                this.Close();
            }
        }

        private void Cancel_Click_1(object sender, EventArgs e)
        {
            this.Close();
        }
    }
}