Proizvod_practik/Дол_Восток/changeChild.cs

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 Дол_Восток
{
    public partial class changeChild : Form
    {
        SqlCommand query = null;

        public changeChild()
        {
            InitializeComponent();
        }

        private void changeChild_Load(object sender, EventArgs e)
        {
            try
            {
                query = new SqlCommand($"select surname + ' ' + [name] + ' ' + patronymic from zakon_predstavitel", db_helper.sqlConnection);
                SqlDataReader reader = query.ExecuteReader();
                while (reader.Read())
                {
                    cb_zakPredstavitel.Items.Add(reader.GetString(0));
                }
                reader.Close();

                query = new SqlCommand($"select kod_otryad from otryad", db_helper.sqlConnection);
                reader = query.ExecuteReader();
                while (reader.Read())
                {
                    cb_otryad.Items.Add(reader.GetInt32(0));
                }
                reader.Close();

                query = new SqlCommand($"select surname from children where kod_children like '{GLavn.row}'", db_helper.sqlConnection);
                tb_surname.Text = query.ExecuteScalar().ToString();

                query = new SqlCommand($"select [name] from children where kod_children like '{GLavn.row}'", db_helper.sqlConnection);
                tb_name.Text = query.ExecuteScalar().ToString();

                query = new SqlCommand($"select patronymic from children where kod_children like '{GLavn.row}'", db_helper.sqlConnection);
                tb_patronymic.Text = query.ExecuteScalar().ToString();

                query = new SqlCommand($"select date_birth from children where kod_children like '{GLavn.row}'", db_helper.sqlConnection);
                dtp_dateBirth.Text = query.ExecuteScalar().ToString();

                query = new SqlCommand($"select zakon_predstavitel.surname + ' ' + zakon_predstavitel.[name] + ' ' + zakon_predstavitel.patronymic from children join zakon_predstavitel on zakon_predstavitel.kod_zakon_predstavitel = children.kod_zakon_predstavitel where kod_children like '{GLavn.row}'", db_helper.sqlConnection);
                cb_zakPredstavitel.SelectedItem = query.ExecuteScalar().ToString();

                query = new SqlCommand($"select adress from children where kod_children like '{GLavn.row}'", db_helper.sqlConnection);
                tb_adress.Text = query.ExecuteScalar().ToString();

                query = new SqlCommand($"select type_document from children join document on document.kod_document = children.kod_document where kod_children like '{GLavn.row}'", db_helper.sqlConnection);
                cb_typeDoc.SelectedItem = query.ExecuteScalar().ToString();

                query = new SqlCommand($"select kem_vudan from children join document on document.kod_document = children.kod_document where kod_children like '{GLavn.row}'", db_helper.sqlConnection);
                tb_kemVudan.Text = query.ExecuteScalar().ToString();

                query = new SqlCommand($"select date_vudachi from children join document on document.kod_document = children.kod_document where kod_children like '{GLavn.row}'", db_helper.sqlConnection);
                dtp_dateVudach.Text = query.ExecuteScalar().ToString();

                query = new SqlCommand($"select seria from children join document on document.kod_document = children.kod_document where kod_children like '{GLavn.row}'", db_helper.sqlConnection);
                mtb_seria.Text = query.ExecuteScalar().ToString();

                query = new SqlCommand($"select nomer from children join document on document.kod_document = children.kod_document where kod_children like '{GLavn.row}'", db_helper.sqlConnection);
                mtb_nomer.Text = query.ExecuteScalar().ToString();

                query = new SqlCommand($"select snils from children where kod_children like '{GLavn.row}'", db_helper.sqlConnection);
                mtb_snils.Text = query.ExecuteScalar().ToString();

                //query = new SqlCommand($"select year_buy from children join putevka on putevka.kod_putevki = children.kod_putevki where kod_children like '{GLavn.row}'", db_helper.sqlConnection);
                //dtp_yearBuy.Text = query.ExecuteScalar().ToString();

                query = new SqlCommand($"select stoimost from children where kod_children like '{GLavn.row}'", db_helper.sqlConnection);
                nud_stoimost.Value = Convert.ToInt32(query.ExecuteScalar());

                query = new SqlCommand($"select lgot_stoimost from children where kod_children like '{GLavn.row}'", db_helper.sqlConnection);
                cb_lgotStoimost.Checked = Convert.ToBoolean(query.ExecuteScalar());

                query = new SqlCommand($"select kod_otryada from children where kod_children like '{GLavn.row}'", db_helper.sqlConnection);
                cb_otryad.SelectedItem = Convert.ToInt32(query.ExecuteScalar());
        }
            catch
            {
                MessageBox.Show("Произошла непредвиденная ошибка!", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
}

        private void bt_change_Click(object sender, EventArgs e)
        {
            try
            {
                query = new SqlCommand($"select count(*) from children where kod_otryada like '{cb_otryad.SelectedItem}'", db_helper.sqlConnection);
                int amount = (int)query.ExecuteScalar();
                query = new SqlCommand($"select kol_vo_children from otryad where kod_otryad like '{cb_otryad.SelectedItem}'", db_helper.sqlConnection);
                int totalAmount = (int)query.ExecuteScalar();
                if (amount < totalAmount)
                {
                    var res = MessageBox.Show("Вы точно хотите изменить данные?", "Подтверждение", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
                    if(res == DialogResult.Yes)
                    {
                        double price = (double)nud_stoimost.Value;
                        double percent = 0;
                        query = new SqlCommand($"select lgot_stoimost from children where kod_children like '{GLavn.row}'", db_helper.sqlConnection);
                        if (cb_lgotStoimost.Checked == true && cb_lgotStoimost.Checked != Convert.ToBoolean(query.ExecuteScalar()))
                        {
                            percent = price / 100.0;
                            price -= percent * 10;
                        }
                        else if (cb_lgotStoimost.Checked == false && cb_lgotStoimost.Checked != Convert.ToBoolean(query.ExecuteScalar()))
                        {
                            percent = price / 90.0;
                            price += percent * 10;
                        }
                        DateTime date1 = DateTime.Parse(dtp_dateBirth.Text);
                        query = new SqlCommand($"select kod_zakon_predstavitel from zakon_predstavitel where (surname + ' ' + [name] + ' ' + patronymic) like '{cb_zakPredstavitel.SelectedItem}'", db_helper.sqlConnection);
                        string kodZP = query.ExecuteScalar().ToString();
                        query = new SqlCommand($"update children set surname = @surname, [name] = @name, patronymic = @patronymic, date_birth = @date_birth, kod_zakon_predstavitel = @kod_zakon_predstavitel, adress = @adress, snils = @snils, kod_otryada = @kod_otryada, lgot_stoimost = @lgot_stoimost, stoimost = @stoimost where kod_children like '{GLavn.row}'", db_helper.sqlConnection);
                        query.Parameters.AddWithValue("surname", tb_surname.Text);
                        query.Parameters.AddWithValue("name", tb_name.Text);
                        query.Parameters.AddWithValue("patronymic", tb_patronymic.Text);
                        query.Parameters.AddWithValue("date_birth", $"{date1.Day}.{date1.Month}.{date1.Year}");
                        query.Parameters.AddWithValue("kod_zakon_predstavitel", kodZP);
                        query.Parameters.AddWithValue("adress", tb_adress.Text);
                        query.Parameters.AddWithValue("snils", mtb_snils.Text);
                        query.Parameters.AddWithValue("kod_otryada", cb_otryad.SelectedItem);
                        query.Parameters.AddWithValue("lgot_stoimost", cb_lgotStoimost.Checked);
                        query.Parameters.AddWithValue("stoimost", Math.Round(price));
                        if (query.ExecuteNonQuery().ToString().Equals("1"))
                        {
                            MessageBox.Show("Изменение прошло успешно!", "Уведомление", MessageBoxButtons.OK, MessageBoxIcon.Information);
                            this.Close();
                        }
                    }
                    else
                    {
                        MessageBox.Show("Действие отменено!", "Уведмоление", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    }
                }
                else
                {
                    MessageBox.Show("Данный отряд полностью укомплектован", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
            catch
            {
                MessageBox.Show("Произошла непредвиденная ошибка!", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void bt_addZP_Click(object sender, EventArgs e)
        {
            try
            {
                addZakPredstavitel add = new addZakPredstavitel();
                add.Show();

                cb_zakPredstavitel.Items.Clear();
                add.FormClosing += (obj, args) =>
                {
                    query = new SqlCommand($"select surname + ' ' + [name] + ' ' + patronymic from zakon_predstavitel", db_helper.sqlConnection);
                    SqlDataReader reader = query.ExecuteReader();
                    while (reader.Read())
                    {
                        cb_zakPredstavitel.Items.Add(reader.GetString(0));
                    }
                    reader.Close();
                };
            }
            catch
            {
                MessageBox.Show("Произошла непредвиденная ошибка!", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void bt_addOtryad_Click(object sender, EventArgs e)
        {
            try
            {
                addOtryad add = new addOtryad();
                add.Show();

                cb_otryad.Items.Clear();
                add.FormClosing += (obj, args) =>
                {
                    query = new SqlCommand($"select kod_otryad from otryad", db_helper.sqlConnection);
                    SqlDataReader reader = query.ExecuteReader();
                    while (reader.Read())
                    {
                        cb_otryad.Items.Add(reader.GetInt32(0));
                    }
                    reader.Close();
                };
            }
            catch
            {
                MessageBox.Show("Произошла непредвиденная ошибка!", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        static public string row = null;

        private void bt_changeOtryad_Click(object sender, EventArgs e)
        {
            try
            {
                row = cb_otryad.SelectedItem.ToString();
                changeOtryad change = new changeOtryad();
                change.Show();

                cb_otryad.Items.Clear();
                change.FormClosing += (obj, args) =>
                {
                    query = new SqlCommand($"select kod_otryad from otryad", db_helper.sqlConnection);
                    SqlDataReader reader = query.ExecuteReader();
                    while (reader.Read())
                    {
                        cb_otryad.Items.Add(reader.GetInt32(0));
                    }
                    reader.Close();
                    cb_otryad.SelectedIndex = 0;
                };
            }
            catch
            {
                MessageBox.Show("Произошла непредвиденная ошибка!", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void bt_changeZP_Click(object sender, EventArgs e)
        {
            try
            {
                query = new SqlCommand($"select kod_zakon_predstavitel from zakon_predstavitel where (surname + ' ' + [name] + ' ' + patronymic) like '{cb_zakPredstavitel.SelectedItem}'", db_helper.sqlConnection);
                row = query.ExecuteScalar().ToString();
                changeZakPredstavitel change = new changeZakPredstavitel();
                change.Show();

                cb_zakPredstavitel.Items.Clear();
                change.FormClosing += (obj, args) =>
                {
                    query = new SqlCommand($"select surname + ' ' + [name] + ' ' + patronymic from zakon_predstavitel", db_helper.sqlConnection);
                    SqlDataReader reader = query.ExecuteReader();
                    while (reader.Read())
                    {
                        cb_zakPredstavitel.Items.Add(reader.GetString(0));
                    }
                    reader.Close();
                    cb_zakPredstavitel.SelectedIndex = 0;
                };
            }
            catch
            {
                MessageBox.Show("Произошла непредвиденная ошибка!", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void label17_Click(object sender, EventArgs e)
        {

        }
    }
}