Esempio n. 1
0
 public void UpdateTrips(TripCl tripCl)//обновляем
 {
     tripsList.Clear();
     LoadData();
     dataGridView1.Rows.Clear();
     dataGridView1.Columns.Clear();
     FillDGV();
 }
Esempio n. 2
0
 public void AddTrips(TripCl tripCl)//добавляем новый
 {
     tripsList.Clear();
     LoadData();
     dataGridView1.Rows.Clear();
     dataGridView1.Columns.Clear();
     FillDGV();
 }
Esempio n. 3
0
 public TripsAddEdit(TripCl tripCl, bool AddOrUpd, Trips pForms)
 {
     // Connection.Open();
     this.AddOrUpd1 = AddOrUpd;
     this.pForm     = pForms;
     tripsSS        = tripCl;
     InitializeComponent();
     dateTimePickerTravel.Format = DateTimePickerFormat.Custom;
 }
Esempio n. 4
0
        void LoadData()//загрузили данные
        {
            using (var cmd = new NpgsqlCommand($"SELECT id_trip, trips.id_client, trips.id_route, hotels.id_city, cities.id_country, routes.id_hotel, hotels.id_type_of_food, " +
                                               $"hotels.id_hotel_category, trips.id_route, surname, name_client, middle_name, date_travel, quantity, total_sum, name_city, name_country, name_hotel, " +
                                               $"number_of_nights, price, name_route, name_type_of_food, name_hotel_category " +
                                               $"FROM clientle, trips, cities, countries, hotels, routes, typies_of_food, hotels_categories " +
                                               $"WHERE trips.id_client = clientle.id_client " +
                                               $"AND hotels.id_city = cities.id_city " +
                                               $"AND cities.id_country = countries.id_country " +
                                               $"AND routes.id_hotel = hotels.id_hotel " +
                                               $"AND hotels.id_hotel_category = hotels_categories.id_hotel_category " +
                                               $"AND hotels.id_type_of_food = typies_of_food.id_type_of_food " +
                                               $"AND trips.id_route = routes.id_route", Connection))
            {
                using (var reader = cmd.ExecuteReader())
                    while (reader.Read())
                    {
                        var tripCl = new TripCl();

                        var idT = reader["id_trip"].ToString().Trim();
                        tripCl.idTrip = Int32.Parse(idT);

                        var idC = reader["id_client"].ToString().Trim();
                        tripCl.idClient = Int32.Parse(idC);

                        var idR = reader["id_route"].ToString().Trim();
                        tripCl.idRoute = Int32.Parse(idR);

                        var idCi = reader["id_city"].ToString().Trim();
                        tripCl.idCity = Int32.Parse(idCi);

                        var idCo = reader["id_country"].ToString().Trim();
                        tripCl.idCountry = Int32.Parse(idCo);

                        var idH = reader["id_hotel"].ToString().Trim();
                        tripCl.idHotel = Int32.Parse(idH);

                        var idTF = reader["id_type_of_food"].ToString().Trim();
                        tripCl.idTypeFoodHotel = Int32.Parse(idTF);

                        var idHC = reader["id_hotel_category"].ToString().Trim();
                        tripCl.idCategoryHotel = Int32.Parse(idHC);

                        var priceR = reader["price"].ToString().Trim();
                        tripCl.price = float.Parse(priceR);

                        var nT = reader["quantity"].ToString().Trim();
                        tripCl.numTrip = Int32.Parse(nT);

                        var d = reader["total_sum"].ToString().Trim();
                        tripCl.totalSum = Int32.Parse(d);

                        var nN = reader["number_of_nights"].ToString().Trim();
                        tripCl.numNights = Int32.Parse(nN);

                        tripCl.nameRoute = reader["name_route"].ToString().Trim();

                        tripCl.nameHotel = reader["name_hotel"].ToString().Trim();

                        tripCl.nameCity = reader["name_city"].ToString().Trim();

                        tripCl.nameCountry = reader["name_country"].ToString().Trim();

                        tripCl.nameCategoryHotel = reader["name_hotel_category"].ToString().Trim();

                        tripCl.nameTypeFoodHotel = reader["name_type_of_food"].ToString().Trim();

                        tripCl.nameClient = reader["name_client"].ToString().Trim();

                        tripCl.surnameClient = reader["surname"].ToString().Trim();

                        tripCl.middlenameClient = reader["middle_name"].ToString().Trim();

                        tripCl.dateTravel = reader["date_travel"].ToString().Trim();

                        tripCl.nameTypeFoodHotel = reader["name_type_of_food"].ToString().Trim();

                        tripCl.nameCategoryHotel = reader["name_hotel_category"].ToString().Trim();

                        tripsList.Add(tripCl);
                    }
            }
        }
Esempio n. 5
0
        private void button_save_Click(object sender, EventArgs e)
        {
            Connection.Open();
            if (textBoxTotalSum.Text.Length > 0)
            {
                if (AddOrUpd1) //Upd - false
                {              //Add
                    var tripC = new TripCl();
                    var cNCl  = comboBoxNameClient.SelectedItem.ToString();
                    var comboSurnameClient    = cNCl.Split()[0];
                    var comboNameClient       = cNCl.Split()[1];
                    var comboMiddleNameCLient = cNCl.Split()[2];//$""
                    using (var cmdIdClient = new NpgsqlCommand($"SELECT id_client FROM clientle WHERE surname = '{comboSurnameClient}' " +
                                                               $"AND name_client ='{comboNameClient}' AND middle_name = '{comboMiddleNameCLient}'", Connection))
                    {
                        tripC.idClient = Convert.ToInt32(cmdIdClient.ExecuteScalar());
                    }
                    var idCl = tripC.idClient;

                    var comboNameRoute = comboBoxNameRoute.SelectedItem.ToString();
                    using (var cmdIdRoute = new NpgsqlCommand($"SELECT id_route FROM routes WHERE name_route = '{comboNameRoute}'", Connection))
                    {
                        tripC.idRoute = Convert.ToInt32(cmdIdRoute.ExecuteScalar());
                    }
                    var idR = tripC.idRoute;

                    tripC.dateTravel = dateTimePickerTravel.Text.ToString().Trim();
                    var dT = tripC.dateTravel;


                    var totalS = textBoxTotalSum.Text.ToString().Trim();
                    tripC.totalSum = Int32.Parse(totalS);

                    var qu = numericUpDownQuantity.Text.Trim();
                    tripC.numTrip = Int32.Parse(qu);

                    using (var cmdInto = new NpgsqlCommand($"INSERT INTO trips(id_client, id_route, date_travel, quantity, total_sum) values " +
                                                           $"({idCl}, '{idR}', '{dateTimePickerTravel.Value}', '{qu}', '{totalS}')", Connection))
                    {
                        cmdInto.ExecuteNonQuery();
                    }

                    var idTr = 0;

                    using (var cmdMax = new NpgsqlCommand($"Select MAX(id_trip) FROM trips", Connection))
                    {
                        idTr = Convert.ToInt32(cmdMax.ExecuteScalar());
                    }
                    using (var cmd = new NpgsqlCommand($"SELECT id_trip, trips.id_client, trips.id_route, hotels.id_city, cities.id_country, routes.id_hotel, hotels.id_type_of_food, " +
                                                       $"hotels.id_hotel_category, trips.id_route, surname, name_client, middle_name, date_travel, quantity, total_sum, name_city, name_country, name_hotel, " +
                                                       $"number_of_nights, price, name_route, name_type_of_food, name_hotel_category " +
                                                       $"FROM clientle, trips, cities, countries, hotels, routes, typies_of_food, hotels_categories " +
                                                       $"WHERE trips.id_client = clientle.id_client " +
                                                       $"AND hotels.id_city = cities.id_city " +
                                                       $"AND cities.id_country = countries.id_country " +
                                                       $"AND routes.id_hotel = hotels.id_hotel " +
                                                       $"AND hotels.id_hotel_category = hotels_categories.id_hotel_category " +
                                                       $"AND hotels.id_type_of_food = typies_of_food.id_type_of_food " +
                                                       $"AND trips.id_route = routes.id_route " +
                                                       $"AND id_trip = {idTr}", Connection))
                    {
                        using (var reader = cmd.ExecuteReader())
                            while (reader.Read())
                            {
                                var priceR = reader["price"].ToString().Trim();
                                var nN     = reader["number_of_nights"].ToString().Trim();
                                pForm.AddTrips(new TripCl()
                                {
                                    nameRoute         = reader["name_route"].ToString().Trim(),
                                    surnameClient     = tripC.surnameClient = reader["surname"].ToString().Trim(),
                                    nameClient        = reader["name_client"].ToString().Trim(),
                                    middlenameClient  = reader["middle_name"].ToString().Trim(),
                                    nameCountry       = reader["name_country"].ToString().Trim(),
                                    nameCategoryHotel = reader["name_hotel_category"].ToString().Trim(),
                                    nameCity          = reader["name_city"].ToString().Trim(),
                                    nameHotel         = reader["name_hotel"].ToString().Trim(),
                                    nameTypeFoodHotel = reader["name_type_of_food"].ToString().Trim(),
                                    price             = float.Parse(priceR),
                                    numNights         = Int32.Parse(nN),
                                    dateTravel        = reader["date_travel"].ToString().Trim().Remove(10),
                                });
                            }
                        MessageBox.Show("Данные успешно добавлены!", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    }
                    Connection.Close();
                }
                else
                {
                    var tripC = new TripCl();
                    var cNCl  = comboBoxNameClient.SelectedItem.ToString();
                    var comboSurnameClient    = cNCl.Split()[0];
                    var comboNameClient       = cNCl.Split()[1];
                    var comboMiddleNameCLient = cNCl.Split()[2];//$""

                    using (var cmdIdClient = new NpgsqlCommand($"SELECT id_client FROM clientle WHERE surname = '{comboSurnameClient}' " +
                                                               $"AND name_client ='{comboNameClient}' AND middle_name = '{comboMiddleNameCLient}'", Connection))
                    {
                        tripC.idClient = Convert.ToInt32(cmdIdClient.ExecuteScalar());
                    }
                    var idCl = tripC.idClient;

                    var comboNameRoute = comboBoxNameRoute.SelectedItem.ToString();
                    using (var cmdIdRoute = new NpgsqlCommand($"SELECT id_route FROM routes WHERE name_route = '{comboNameRoute}'", Connection))
                    {
                        tripC.idRoute = Convert.ToInt32(cmdIdRoute.ExecuteScalar());
                    }
                    var idR = tripC.idRoute;

                    tripC.dateTravel = dateTimePickerTravel.Text.ToString().Trim();
                    var dT = tripC.dateTravel;

                    var qu = numericUpDownQuantity.Text.Trim();
                    tripC.numTrip = Int32.Parse(qu);

                    var totalS = textBoxTotalSum.Text.ToString().Trim();
                    tripC.totalSum = Int32.Parse(totalS);

                    using (var cmd = new NpgsqlCommand($"UPDATE trips SET id_client = {idCl}, id_route = {idR}, date_travel = '{dT}' ," +
                                                       $"quantity = {qu}, total_sum = {totalS} WHERE id_trip = {IdTrips_pk.Text}", Connection))
                    {
                        cmd.ExecuteNonQuery();
                    }
                    using (var cmd = new NpgsqlCommand($"SELECT id_trip, trips.id_client, trips.id_route, hotels.id_city, cities.id_country, routes.id_hotel, hotels.id_type_of_food, " +
                                                       $"hotels.id_hotel_category, trips.id_route, surname, name_client, middle_name, date_travel, quantity, total_sum, name_city, name_country, name_hotel, " +
                                                       $"number_of_nights, price, name_route, name_type_of_food, name_hotel_category " +
                                                       $"FROM clientle, trips, cities, countries, hotels, routes, typies_of_food, hotels_categories " +
                                                       $"WHERE trips.id_client = clientle.id_client " +
                                                       $"AND hotels.id_city = cities.id_city " +
                                                       $"AND cities.id_country = countries.id_country " +
                                                       $"AND routes.id_hotel = hotels.id_hotel " +
                                                       $"AND hotels.id_hotel_category = hotels_categories.id_hotel_category " +
                                                       $"AND hotels.id_type_of_food = typies_of_food.id_type_of_food " +
                                                       $"AND trips.id_route = routes.id_route " +
                                                       $"AND id_trip = {IdTrips_pk.Text}", Connection))
                    {
                        using (var reader = cmd.ExecuteReader())
                            while (reader.Read())
                            {
                                var priceR = reader["price"].ToString().Trim();
                                var nN     = reader["number_of_nights"].ToString().Trim();
                                pForm.UpdateTrips(new TripCl()
                                {
                                    nameRoute         = reader["name_route"].ToString().Trim(),
                                    surnameClient     = tripC.surnameClient = reader["surname"].ToString().Trim(),
                                    nameClient        = reader["name_client"].ToString().Trim(),
                                    middlenameClient  = reader["middle_name"].ToString().Trim(),
                                    nameCountry       = reader["name_country"].ToString().Trim(),
                                    nameCategoryHotel = reader["name_hotel_category"].ToString().Trim(),
                                    nameCity          = reader["name_city"].ToString().Trim(),
                                    nameHotel         = reader["name_hotel"].ToString().Trim(),
                                    nameTypeFoodHotel = reader["name_type_of_food"].ToString().Trim(),
                                    price             = float.Parse(priceR),
                                    numNights         = Int32.Parse(nN),
                                    dateTravel        = reader["date_travel"].ToString().Trim().Remove(10)
                                });
                            }
                        MessageBox.Show("Данные успешно изменены!", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
                        Connection.Close();
                    }
                }
            }
            else
            {
                MessageBox.Show("Итоговая стоимость не заполнена!", "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            Connection.Close();
        }