private void TrainClick(object sender, EventArgs e)
{
Label lbl = (Label)sender;
string CityFrom = SQLClass.Select("SELECT Id FROM Cities" +
" WHERE Name = '" + comboBox1.Text + "'")[0];
string CityTo = SQLClass.Select("SELECT Id FROM Cities" +
" WHERE Name = '" + comboBox2.Text + "'")[0];
OrderForm of = new OrderForm(lbl.Tag.ToString(), CityFrom, CityTo);
of.Show();
}
void MakeOrder(object sender, EventArgs e)
{
if (Program.Login == "")
{
MessageBox.Show("Вы не вошли в систему!");
return;
}
Button btn = (Button)sender;
SQLClass.Insert("INSERT INTO Orders(Login, RunId, Place, CityFrom, CityTo)" +
" VALUES('" + Program.Login + "', " + RunId + ", " + btn.Text + ", " + CityFrom + ", " + CityTo + ")");
MessageBox.Show("Сделано");
btn.Enabled = false;
}
public TicketsList()
{
InitializeComponent();
if (MainForm.pages.Count > MainForm.pagePos + 1)
{
MainForm.pages.RemoveRange(MainForm.pagePos + 1, MainForm.pages.Count - MainForm.pagePos - 1);
}
MainForm.pages.Add(this);
MainForm.pagePos++;
List <string> cities = SQLClass.Select(
"SELECT Name FROM cities ORDER BY Name");
comboBox1.Items.AddRange(cities.ToArray());
comboBox2.Items.AddRange(cities.ToArray());
}
private void button1_Click(object sender, EventArgs e)
{
string registered = SQLClass.Select(
"SELECT COUNT(*) FROM Users WHERE Login = '******'")[0];
if (registered != "0")
{
MessageBox.Show("Вы уже зарегистрированы!");
return;
}
SQLClass.Insert("INSERT INTO Users(Login, Name, Password) VALUES(" +
"'" + loginTB.Text + "', '" + fioTB.Text + "', '" + passTB.Text + "')");
MessageBox.Show("Теперь можно входить в систему");
//Close();
}
public AdminUsersForm()
{
InitializeComponent();
if (MainForm.pages.Count > MainForm.pagePos + 1)
{
MainForm.pages.RemoveRange(MainForm.pagePos + 1, MainForm.pages.Count - MainForm.pagePos - 1);
}
MainForm.pages.Add(this);
MainForm.pagePos++;
List <string> users = SQLClass.Select("SELECT Users.Login, Name, COUNT(Orders.CityFrom) FROM Users LEFT JOIN Orders ON Users.Login = Orders.Login GROUP BY Users.Login, Name");
for (int i = 0; i < users.Count; i += 3)
{
string[] row = new string[3];
row[0] = users[i];
row[1] = users[i + 1];
row[2] = users[i + 2];
dataGridView1.Rows.Add(row);
}
}
public OrderForm(string runId, string cityFrom, string cityTo)
{
InitializeComponent();
if (MainForm.pages.Count > MainForm.pagePos + 1)
{
MainForm.pages.RemoveRange(MainForm.pagePos + 1, MainForm.pages.Count - MainForm.pagePos - 1);
}
MainForm.pages.Add(this);
MainForm.pagePos++;
RunId = runId;
CityFrom = cityFrom;
CityTo = cityTo;
int x = 50;
int y = 80;
List <string> trainData = SQLClass.Select("SELECT Places FROM Trains" +
" WHERE Id = (SELECT TrainId FROM Runs WHERE Id = " + RunId + ")");
for (int i = 1; i <= Convert.ToInt32(trainData[0]); i++)
{
Button btn = new Button();
btn.Location = new Point(x, y);
btn.Size = new Size(50, 30);
btn.Text = i.ToString();
string disabled = SQLClass.Select("SELECT COUNT(*) FROM Orders" +
" WHERE RunId = " + RunId + " AND Place = " + i.ToString())[0];
btn.Enabled = (disabled == "0");
btn.Click += new EventHandler(MakeOrder);
Controls.Add(btn);
x += 100;
if (x + 100 >= Width)
{
x = 50;
y += 50;
}
}
}
private void button1_Click(object sender, EventArgs e)
{
DateTime dt1 = dateTimePicker1.Value;
DateTime dt2 = dateTimePicker2.Value;
List <string> cities = SQLClass.Select("SELECT Name FROM Cities ORDER BY Name");
Column5.Items.Clear();
Column4.Items.Clear();
Column4.Items.AddRange(cities.ToArray());
Column5.Items.AddRange(cities.ToArray());
List <string> orders =
SQLClass.Select(
"SELECT Id, Login, RunId, RunId, CityFrom, CityTo, Place, Status" +
" FROM Orders WHERE RunId IN " +
"(SELECT Id FROM Runs WHERE DT BETWEEN STR_TO_DATE('" + dt1.ToShortDateString() + "', '%d.%m.%Y')" +
" AND STR_TO_DATE('" + dt2.ToShortDateString() + "', '%d.%m.%Y')) ORDER BY Orders.Id");
for (int i = 0; i < orders.Count; i += 8)
{
string[] row = new string[8];
for (int j = i; j < i + 8; j++)
{
row[j - i] = orders[j];
}
//Города
List <string> runData = SQLClass.Select(
"SELECT Trains.Name, DT FROM Runs JOIN Trains ON Trains.Id = Runs.TrainId WHERE Runs.Id=" + row[2]);
row[2] = runData[0];
row[3] = runData[1];
row[4] = SQLClass.Select("SELECT Name FROM Cities WHERE Id = " + row[4])[0];
row[5] = SQLClass.Select("SELECT Name FROM Cities WHERE Id = " + row[5])[0];
ordersDGV.Rows.Add(row);
}
}
private void button2_Click(object sender, EventArgs e)
{
if (MessageBox.Show("Удалить предыдущие рейсы за эти даты и заменить новыми?", "Предупреждение", MessageBoxButtons.YesNo) == DialogResult.No)
{
return;
}
DateTime dt1 = dateTimePicker1.Value;
DateTime dt2 = dateTimePicker2.Value;
List <string> trains = SQLClass.Select("SELECT Id, Days FROM Trains");
SQLClass.Insert("DELETE FROM Orders WHERE RunId IN (SELECT Id FROM Runs WHERE DT BETWEEN STR_TO_DATE('" + dt1.ToShortDateString() + "', '%d.%m.%Y')" +
" AND STR_TO_DATE('" + dt2.ToShortDateString() + "', '%d.%m.%Y'))");
SQLClass.Insert("DELETE FROM Runs WHERE DT BETWEEN STR_TO_DATE('" + dt1.ToShortDateString() + "', '%d.%m.%Y')" +
" AND STR_TO_DATE('" + dt2.ToShortDateString() + "', '%d.%m.%Y')");
while (dt1 < dt2)
{
int day = (int)dt1.DayOfWeek;
if (day == 0)
{
day = 7; //Воскресенье
}
for (int i = 0; i < trains.Count; i += 2)
{
//В этот день есть поезд
if (trains[i + 1].Contains(day.ToString()))
{
SQLClass.Insert("INSERT INTO Runs(TrainId, DT)" +
"VALUES (" + trains[i] + " , STR_TO_DATE('" + dt1.ToShortDateString() + "', '%d.%m.%Y'))");
}
}
dt1 = dt1.AddDays(1);
}
MessageBox.Show("Случилось");
}
private void button1_Click(object sender, EventArgs e)
{
DateTime dt1 = dateTimePicker1.Value;
DateTime dt2 = dateTimePicker2.Value;
runsDGV.Rows.Clear();
List <string> trains = SQLClass.Select("SELECT Concat(Name, ' ('," +
"(SELECT Name FROM Cities WHERE Id = Trains.CityFrom), ' - ', " +
"(SELECT Name FROM Cities WHERE Id = Trains.CityTo), ')')" +
"FROM Trains ORDER BY Name");
Column2.Items.Clear();
Column2.Items.AddRange(trains.ToArray());
List <string> runs = SQLClass.Select(
"SELECT Runs.Id, Trains.Places, Concat(Name, ' ('," +
"(SELECT Name FROM Cities WHERE Id = Trains.CityFrom), ' - ', " +
"(SELECT Name FROM Cities WHERE Id = Trains.CityTo), ')'), DT FROM Runs JOIN Trains ON Trains.Id = Runs.TrainId" +
" WHERE DT BETWEEN STR_TO_DATE('" + dt1.ToShortDateString() + "', '%d.%m.%Y') AND STR_TO_DATE('" + dt2.ToShortDateString() + "', '%d.%m.%Y')" +
" ORDER BY DT");
for (int i = 0; i < runs.Count; i += 4)
{
string[] row = new string[4];
row[0] = runs[i];
row[1] = runs[i + 2];
row[2] = runs[i + 3];
String total = runs[i + 1];
String booked = SQLClass.Select("SELECT COUNT(*) FROM Orders WHERE RunId = " + runs[i])[0];
row[3] = booked + " / " + total;
runsDGV.Rows.Add(row);
}
}
private void button4_Click(object sender, EventArgs e)
{
if (comboBox1.Text == "")
{
MessageBox.Show("Выберите пункт отправления");
return;
}
else if (comboBox2.Text == "")
{
MessageBox.Show("Выберите пункт назначения");
return;
}
string dateFrom = dateTimePicker1.Value.ToString("yyyy-MM-dd");
string dateTo = dateTimePicker2.Value.AddDays(1).ToString("yyyy-MM-dd");
string CityFrom =
SQLClass.Select("SELECT Id FROM cities WHERE Name = '" + comboBox1.Text + "'")[0];
string CityTo =
SQLClass.Select("SELECT Id FROM cities WHERE Name = '" + comboBox2.Text + "'")[0];
List <string> trains = SQLClass.Select(
"SELECT runs.TrainId, trains.Name, " +
"'" + comboBox1.Text + "', " +
"'" + comboBox2.Text + "', " +
" (SELECT ADDTIME(DT, TimeStart) FROM routes WHERE City = " + CityFrom + " AND trainId = runs.trainId) TimeCity1," +
" (SELECT ADDTIME(DT, TimeStart) FROM routes WHERE City = " + CityTo + " AND trainId = runs.trainId) TimeCity2, " +
" runs.Id" +
" FROM runs" +
" JOIN trains ON trains.Id = runs.trainId" +
" HAVING TimeCity1<TimeCity2 AND" +
" TimeCity1 BETWEEN '" + dateFrom + "' AND '" + dateTo + "'");
Image img = Image.FromFile("../../Pictures/TrainBtn.png");
int x = 10;
int y = 10;
TrainsPanel.Controls.Clear();
for (int i = 0; i < trains.Count; i += 7)
{
Label lbl = new Label();
lbl.Text =
Environment.NewLine +
Environment.NewLine +
Environment.NewLine +
" " + "Поезд № " + trains[i + 1] + Environment.NewLine +
" " + trains[i + 2] + " - " + trains[i + 3] + Environment.NewLine +
" " + "Отправление: " + Environment.NewLine +
" " + trains[i + 4] + Environment.NewLine +
" " + "Прибытие: " + Environment.NewLine +
" " + trains[i + 5];
lbl.Location = new Point(x, y);
lbl.Size = new Size(200, 160);
lbl.Font = new Font("Arial", 11);
lbl.Tag = trains[i + 6];
lbl.Image = img;
lbl.Click += new EventHandler(TrainClick);
TrainsPanel.Controls.Add(lbl);
x += 250;
if (x + 200 > Width)
{
x = 10;
y += 180;
}
}
}
public AddTrain(string TrainId = "")
{
trainId = TrainId;
InitializeComponent();
if (MainForm.pages.Count > MainForm.pagePos + 1)
{
MainForm.pages.RemoveRange(MainForm.pagePos + 1, MainForm.pages.Count - MainForm.pagePos - 1);
}
MainForm.pages.Add(this);
MainForm.pagePos++;
comboY = comboBox1.Location.Y + 50;
cities = SQLClass.Select("SELECT Name FROM Cities ORDER BY Name");
comboBox1.Items.AddRange(cities.ToArray());
toComboBox.Items.AddRange(cities.ToArray());
fromComboBox.Items.AddRange(cities.ToArray());
if (trainId != "")
{
Text = "Изменить маршрут поезда";
button3_Click(button3, null);
List <string> trainData =
SQLClass.Select("SELECT Id, Name, CityFrom, CityTo, Days, Places FROM Trains WHERE Id = " + trainId);
if (trainData.Count > 0)
{
nameTextBox.Text = trainData[1];
placeTextBox.Text = trainData[5];
string[] days = trainData[4].Split(new string[] { ", " }, StringSplitOptions.None);
for (int i = 0; i < days.Length; i++)
{
int j = Convert.ToInt32(days[i]);
checkedListBox1.SetItemChecked(j - 1, true);
}
fromComboBox.Text = SQLClass.Select("SELECT Name FROM Cities WHERE Id = " + trainData[2])[0];
toComboBox.Text = SQLClass.Select("SELECT Name FROM Cities WHERE Id = " + trainData[3])[0];
}
List <string> citiesData =
SQLClass.Select("SELECT Cities.Name, TimeStart FROM Routes JOIN Cities ON Routes.City = Cities.Id WHERE TrainId = " + trainId);
for (int i = 0; i < citiesData.Count; i += 2)
{
ComboBox cb1 = new ComboBox();
cb1.Items.AddRange(cities.ToArray());
cb1.Text = citiesData[i];
cb1.Location = new Point(comboBox1.Location.X, comboY);
cb1.Size = new Size(255, 37);
Button btn = new Button();
btn.Location = new Point(button2.Location.X, comboY);
btn.Size = new Size(75, 34);
btn.Text = "+";
btn.Click += new EventHandler(button2_Click);
Button btn3 = new Button();
btn3.Location = new Point(button3.Location.X, comboY);
btn3.Size = new Size(75, 34);
btn3.Text = "-";
btn3.Click += new EventHandler(button3_Click);
DateTimePicker dp1 = new DateTimePicker();
dp1.Format = DateTimePickerFormat.Time;
dp1.Location = new Point(dateTimePicker1.Location.X, comboY);
dp1.Size = new Size(131, 34);
Controls.Add(cb1);
Controls.Add(btn);
Controls.Add(dp1);
Controls.Add(btn3);
comboY += 50;
}
}
}
private void button1_Click(object sender, EventArgs e)
{
List <string> days = new List <string>();
for (int i = 0; i < checkedListBox1.Items.Count; i++)
{
if (checkedListBox1.GetItemChecked(i))
{
days.Add((i + 1).ToString());
}
}
string d = String.Join(", ", days);
string CityFrom = SQLClass.Select("SELECT Id FROM Cities" +
" WHERE Name = '" + fromComboBox.Text + "'")[0];
string CityTo = SQLClass.Select("SELECT Id FROM Cities" +
" WHERE Name = '" + toComboBox.Text + "'")[0];
if (trainId == "")
{
SQLClass.Insert("INSERT INTO Trains(Name, Places, Days, CityFrom, CityTo)" +
" VALUES('" + nameTextBox.Text + "', '" +
placeTextBox.Text + "', '" +
d + "'," + CityFrom + ", " + CityTo + ")");
trainId = SQLClass.Select("SELECT MAX(Id) FROM Trains")[0];
}
else
{
SQLClass.Insert("UPDATE Trains SET" +
" Name = '" + nameTextBox.Text + "'," +
" Places = '" + placeTextBox.Text + "'," +
" Days = '" + d + "'," +
" CityFrom = " + CityFrom + ", " +
" CityTo = " + CityTo +
" WHERE Id = " + trainId);
SQLClass.Insert("DELETE FROM Routes WHERE TrainId = " + trainId);
}
//Город
foreach (Control ctrl in Controls)
{
if (ctrl is ComboBox)
{
string city = ctrl.Text;
//Время, когда поезд там
foreach (Control ctrl2 in Controls)
{
if (ctrl2 is DateTimePicker && ctrl2.Location.Y == ctrl.Location.Y)
{
string cityTime = ((DateTimePicker)ctrl2).Value.ToLongTimeString();
SQLClass.Insert("INSERT INTO Routes(TrainId, City, TimeStart, TimeFinish)" +
" SELECT " + trainId + ", Id, '" + cityTime + "', '" + cityTime + "' FROM Cities WHERE Name = '" + city + "'");
break;
}
}
}
}
MessageBox.Show("Успешно сохранено");
}