Proizvod_practik/Дол_Восток/addChild.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 addChild : Form
    {
        SqlCommand query = null;

        public addChild()
        {
            InitializeComponent();
        }

        private void bt_add_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)
                {
                    int price = (int)nud_stoimost.Value;
                    double percent = price / 100.0;
                    if (cb_lgotStoimost.Checked)
                    {
                        price -= (int)percent * 10;
                    }

                    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();

                    DateTime date1 = DateTime.Parse(dtp_dateVudach.Text);
                    query = new SqlCommand($"insert into document (type_document, kem_vudan, date_vudachi, seria, nomer) values (@type_document, @kem_vudan, @date_vudachi, @seria, @nomer)", db_helper.sqlConnection);
                    query.Parameters.AddWithValue("type_document", cb_typeDoc.SelectedItem);
                    query.Parameters.AddWithValue("kem_vudan", tb_kemVudan.Text);
                    query.Parameters.AddWithValue("date_vudachi", $"{date1.Day}.{date1.Month}.{date1.Year}");
                    query.Parameters.AddWithValue("seria", mtb_seria.Text);
                    query.Parameters.AddWithValue("nomer", mtb_nomer.Text);
                    query.ExecuteNonQuery();

                    query = new SqlCommand($"select kod_document from document where type_document like '{cb_typeDoc.SelectedItem}' and kem_vudan like '{tb_kemVudan.Text}' and date_vudachi = '{date1.Day}.{date1.Month}.{date1.Year}' and seria like '{mtb_seria.Text}' and nomer like '{mtb_nomer.Text}'", db_helper.sqlConnection);
                    string kodDocument = query.ExecuteScalar().ToString();

                    date1 = DateTime.Parse(dtp_yearBuy.Text);
                    query = new SqlCommand($"insert into putevka (year_buy, stoimost) values (@year_buy, @stoimost)", db_helper.sqlConnection);
                    query.Parameters.AddWithValue("year_buy", $"01.01.{date1.Year}");
                    query.Parameters.AddWithValue("stoimost", nud_stoimost.Value);
                    query.ExecuteNonQuery();

                    query = new SqlCommand($"select kod_putevki from putevka where year(year_buy) = '{date1.Year}' and stoimost = {nud_stoimost.Value}", db_helper.sqlConnection);
                    string kodPutevki = query.ExecuteScalar().ToString();

                    date1 = DateTime.Parse(dtp_dateBirth.Text);
                    query = new SqlCommand($"insert into children (surname, [name], patronymic, date_birth, kod_zakon_predstavitel, adress, kod_document, snils, kod_otryada, lgot_stoimost, stoimost) values (@surname, @name, @patronymic, @date_birth, @kod_zakon_predstavitel, @adress, @kod_document, @snils, @kod_otryada, @lgot_stoimost, @stoimost)", 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("kod_document", kodDocument);
                    query.Parameters.AddWithValue("snils", mtb_snils.Text);
                    query.Parameters.AddWithValue("kod_otryada", cb_otryad.SelectedItem);
                    query.Parameters.AddWithValue("lgot_stoimost", cb_lgotStoimost.CheckState);
                    query.Parameters.AddWithValue("stoimost", price);
                    if (query.ExecuteNonQuery().ToString().Equals("1"))
                    {
                        MessageBox.Show("Добавление прошло успешно!", "Уведомление", MessageBoxButtons.OK, MessageBoxIcon.Information);
                        this.Close();
                    }
                }
                else
                {
                    MessageBox.Show("Данный отряд полностью укомплектован", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
            catch
            {
                MessageBox.Show("Произошла непредвиденная ошибка!", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void addChild_Load(object sender, EventArgs e)
        {
            try
            {
                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();

                query = new SqlCommand($"select surname + ' ' + [name] + ' ' + patronymic from zakon_predstavitel", db_helper.sqlConnection);
                reader = query.ExecuteReader();
                while (reader.Read())
                {
                    cb_zakPredstavitel.Items.Add(reader.GetString(0));
                }
                reader.Close();

                cb_otryad.SelectedIndex = 0;
                cb_zakPredstavitel.SelectedIndex = 0;
            }
            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);
            }
        }

        private void bt_changeOtryad_Click(object sender, EventArgs e)
        {
            try
            {
                changeChild.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);
                changeChild.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);
            }
        }
    }
}