public void loadTours()
{
toursList.Items.Clear();
SQLite connection = new SQLite();
SQLiteDataReader reader = connection.ReadData("SELECT ID, SID, CID, Date, Count, Discount, Price FROM Tours ORDER BY Date");
while (reader.Read())
{
var readclient = connection.ReadData(string.Format("SELECT Surname, Name, Secname FROM Clients WHERE ID = '{0}'", reader.GetInt32(2)));
readclient.Read();
var readroute = connection.ReadData(string.Format("SELECT Country, Hotel, Duration FROM Scopes WHERE ID = '{0}'", reader.GetInt32(1)));
readroute.Read();
toursList.Items.Add(new Tour
(reader.GetInt32(0),
string.Format("{0} {1} {2}", readclient.GetString(0), readclient.GetString(1), readclient.GetString(2)),
readroute.GetString(0),
readroute.GetString(1),
readroute.GetInt32(2),
reader.GetString(3),
reader.GetInt32(4),
reader.GetString(5),
reader.GetFloat(6)));
}
connection.Close();
}
public tourPurchase(int routeId, routeChoice routesWindow, int clientId)
{
InitializeComponent();
//datepicker.SelectedDate = DateTime.Now;
this.routesWindow = routesWindow;
this.routeId = routeId;
this.clientId = clientId;
SQLite connection = new SQLite();
var reader = connection.ReadData(string.Format("SELECT Surname, Name, Secname FROM Clients WHERE ID ='{0}'", clientId));
while (reader.Read())
{
personName.Content = string.Format("Продажа тура клиенту: {0} {1} {2}", reader.GetString(0), reader.GetString(1), reader.GetString(2));
}
reader = connection.ReadData(string.Format("SELECT Climat, Country, Hotel, Duration, Cost FROM Scopes WHERE ID = '{0}'", routeId));
while (reader.Read())
{
Route route = new Route(
routeId,
reader.GetString(0),
reader.GetString(1),
reader.GetString(2),
reader.GetInt32(3),
reader.GetFloat(4));
routesList.Items.Add(route);
}
connection.Close();
}
/* обновление ListBox климат */
private void updateClimateList()
{
var climateList = new List<Obj>();
SQLite connection = new SQLite();
var reader = connection.ReadData("SELECT Climat FROM Scopes GROUP BY Climat ORDER BY Climat");
while (reader.Read())
climateList.Add(new Obj(false, reader.GetString(0)));
ClimateListBox.ItemsSource = climateList;
}
/* вывод маршрутов из БД в таблицу */
public void loadRoutes()
{
routesList.Items.Clear();
SQLite connection = new SQLite();
SQLiteDataReader reader = connection.ReadData("SELECT ID, Climat, Country, Hotel, Duration, Cost FROM Scopes ORDER BY Climat, Country, Hotel, Duration, Cost");
while (reader.Read())
{
routesList.Items.Add(new Route(reader.GetInt32(0), reader.GetString(1), reader.GetString(2), reader.GetString(3), reader.GetInt32(4), reader.GetFloat(5)));
}
connection.Close();
}
/* вывод клиентов из БД в таблицу */
public void UpdateClientsList(string surname)
{
clientsList.Items.Clear();
if (surname == null) { surname = ""; } else { surname = string.Format("WHERE Surname LIKE '%{0}%'", surname); }
SQLite connection = new SQLite();
SQLiteDataReader reader = connection.ReadData(string.Format("SELECT ID, Surname, Name, Secname FROM Clients {0} ORDER BY Surname, Name, Secname", surname));
while (reader.Read())
{
clientsList.Items.Add(new Client(reader.GetInt32(0), reader.GetString(1), reader.GetString(2), reader.GetString(3)));
}
connection.Close();
}
/* обновление ListBox страны */
private void updateCountryList()
{
var countryList = new List<Obj>();
SQLite connection = new SQLite();
var climateArr = getItemsArr(ClimateListBox);
var query = string.Format("SELECT Country FROM Scopes WHERE Climat IN {0} GROUP BY Country ORDER BY Country", climateArr);
var reader = connection.ReadData(query);
while (reader.Read())
countryList.Add(new Obj(false, reader.GetString(0)));
CountryListBox.ItemsSource = countryList;
}
/* обновление списка маршрутов */
private void updateRoutesList()
{
routesList.Items.Clear();
// считываем идентификаторы отмеченных элементов листбоксов
var climateArr = " AND Climat IN " + getItemsArr(ClimateListBox);
var countriesArr = " AND Country IN " + getItemsArr(CountryListBox);
var hotelsArr = " AND Hotel IN " + getItemsArr(HotelListBox);
// считываем диапазон длительности
var duration = "";
if (Check.checkNumber(minDurationBox.Text)) { duration += string.Format(" AND duration >= '{0}'", minDurationBox.Text); }
if (Check.checkNumber(maxDurationBox.Text)) { duration += string.Format(" AND duration <= '{0}'", maxDurationBox.Text); }
// считываем диапазон цен
var cost = "";
if (Check.checkNumber(minCostBox.Text)) { cost += string.Format(" AND cost >= '{0}'", minCostBox.Text); }
if (Check.checkNumber(maxCostBox.Text)) { cost += string.Format(" AND cost <= '{0}'", maxCostBox.Text); }
var query = string.Format("SELECT ID, Country, Climat, Hotel, Duration, Cost FROM Scopes WHERE 1 {0} {1} {2} {3} {4}",
climateArr, countriesArr, hotelsArr, duration, cost);
SQLite connection = new SQLite();
var reader = connection.ReadData(query);
while (reader.Read())
routesList.Items.Add(new Route(reader.GetInt32(0), reader.GetString(1), reader.GetString(2), reader.GetString(3), reader.GetInt32(4), reader.GetFloat(5)));
}
/* обновление ListBox отели */
private void updateHotelList()
{
var hotelList = new List<Obj>();
SQLite connection = new SQLite();
var climateArr = getItemsArr(ClimateListBox);
var countriesArr = getItemsArr(CountryListBox);
var query = string.Format("SELECT Hotel FROM Scopes WHERE Climat IN {0} AND Country IN {1} GROUP BY Hotel ORDER BY Hotel", climateArr, countriesArr);
var reader = connection.ReadData(query);
while (reader.Read())
hotelList.Add(new Obj(false, reader.GetString(0)));
HotelListBox.ItemsSource = hotelList;
}