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 Transportation
{
    public partial class AddRequest : Form
    {
        SqlCommand query = null;
        SqlDataReader reader = null;
        public AddRequest()
        {
            InitializeComponent();
        }

        private void AddBTN_Click(object sender, EventArgs e)
        {
            query = new SqlCommand($"select code_executor from executors where (surname_executor + ' ' + name_executor + ' ' + midname_executor) like '{ExecutorsCB.SelectedItem}'", dbhelper.sqlConnection);
            string codeExecutor = query.ExecuteScalar().ToString();

            query = new SqlCommand($"select code_Driver from drivers where (surname_driver + ' ' + name_driver + ' ' + midname_driver) like '{DriversCB.SelectedItem}'", dbhelper.sqlConnection);
            string codeDriver = query.ExecuteScalar().ToString();

            query = new SqlCommand($"select code_vehicle from vehicles where (brand_vehicle + ' ' + model_vehicle + ' ' + number_vehicle) like '{VehiclesCB.SelectedItem}'", dbhelper.sqlConnection);
            string codeVehicle = query.ExecuteScalar().ToString();

            query = new SqlCommand($"select code_logistician from logisticians where (surname_logistician + ' ' + name_logistician + ' ' + midname_logistician) like '{LogisticiansCB.SelectedItem}'", dbhelper.sqlConnection);
            string codeLogistician = query.ExecuteScalar().ToString();

            query = new SqlCommand("insert into requests (code_executor, code_driver, code_vehicle, date_loading, place_loading, fullname_shipper, telephone_shipper, name_cargo, transportation_conditions, date_unloading, place_unloading, fullname_consignee, telephone_consignee, route_town_request, route_mileage_request, transportation_format, payment_request, payment_conditions_request, code_logistician) values (@code_executor, @code_driver, @code_vehicle, @date_loading, @place_loading, @fullname_shipper, @telephone_shipper, @name_cargo, @transportation_conditions, @date_unloading, @place_unloading, @fullname_consignee, @telephone_consignee, @route_town_request, @route_mileage_request, @transportation_format, @payment_request, @payment_conditions_request, @code_logistician)", dbhelper.sqlConnection);
            query.Parameters.AddWithValue("code_executor", codeExecutor);
            query.Parameters.AddWithValue("code_driver", codeDriver);
            query.Parameters.AddWithValue("code_vehicle", codeVehicle);
            query.Parameters.AddWithValue("date_loading", DateLoadingDTP.Value);
            query.Parameters.AddWithValue("place_loading", PlaceLoadingRTB.Text);
            query.Parameters.AddWithValue("fullname_shipper", NameShipperTB.Text);
            query.Parameters.AddWithValue("telephone_shipper", TelephoneShipperMTB.Text);
            query.Parameters.AddWithValue("name_cargo", NameCargoRTB.Text);
            query.Parameters.AddWithValue("transportation_conditions", TransportationConditionsRTB.Text);
            query.Parameters.AddWithValue("date_unloading", DateUnloadingDTP.Value);
     
            query.Parameters.AddWithValue("place_unloading", PlaceUnloadingRTP.Text);
            query.Parameters.AddWithValue("fullname_consignee", NameConsigneeTB.Text);
            query.Parameters.AddWithValue("telephone_consignee", TelephoneConsigneeMTB.Text);
            query.Parameters.AddWithValue("route_town_request", RoutePlaceTB.Text);
            query.Parameters.AddWithValue("route_mileage_request", RouteMileageTB.Text);
            query.Parameters.AddWithValue("transportation_format", TransportationFormatDTP.Text);
            query.Parameters.AddWithValue("payment_request", PaymentTB.Text);
            query.Parameters.AddWithValue("payment_conditions_request", PaymentConditionsTB.Text);
            query.Parameters.AddWithValue("code_logistician", codeLogistician);

            if (query.ExecuteNonQuery().ToString().Equals("1"))
            {
                MessageBox.Show("Добавление прошло успешно!", "Уведомление", MessageBoxButtons.OK, MessageBoxIcon.Information);
                this.Close();
            }
        }

        private void AddRequest_Load(object sender, EventArgs e)
        {
            query = new SqlCommand("select surname_executor + ' ' + name_executor + ' ' + midname_executor from Executors", dbhelper.sqlConnection);
            reader = query.ExecuteReader();
            while (reader.Read())
            {
                ExecutorsCB.Items.Add(reader.GetString(0));
            }
            reader.Close();

            query = new SqlCommand("select surname_driver + ' ' + name_driver + ' ' + midname_driver from Drivers", dbhelper.sqlConnection);
            reader = query.ExecuteReader();
            while (reader.Read())
            {
                DriversCB.Items.Add(reader.GetString(0));
            }
            reader.Close();

            query = new SqlCommand("select brand_vehicle + ' ' + model_vehicle + ' ' + number_vehicle from Vehicles", dbhelper.sqlConnection);
            reader = query.ExecuteReader();
            while (reader.Read())
            {
                VehiclesCB.Items.Add(reader.GetString(0));
            }
            reader.Close();

            query = new SqlCommand("select surname_logistician + ' ' + name_logistician + ' ' + midname_logistician from Logisticians", dbhelper.sqlConnection);
            reader = query.ExecuteReader();
            while (reader.Read())
            {
                LogisticiansCB.Items.Add(reader.GetString(0));
            }
            reader.Close();
        }
    }
}