public void UpdateTrips(TripCl tripCl)//обновляем
{
tripsList.Clear();
LoadData();
dataGridView1.Rows.Clear();
dataGridView1.Columns.Clear();
FillDGV();
}
public void AddTrips(TripCl tripCl)//добавляем новый
{
tripsList.Clear();
LoadData();
dataGridView1.Rows.Clear();
dataGridView1.Columns.Clear();
FillDGV();
}
public TripsAddEdit(TripCl tripCl, bool AddOrUpd, Trips pForms)
{
// Connection.Open();
this.AddOrUpd1 = AddOrUpd;
this.pForm = pForms;
tripsSS = tripCl;
InitializeComponent();
dateTimePickerTravel.Format = DateTimePickerFormat.Custom;
}
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);
}
}
}
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();
}