private void LoadTable(string carrier)
{
string sqlRequest = "select distinct dir.Direction, DepartingPoint, ArrivalPoint, cast(Departing as time(0))[DepartingTime], (Select Count(*) from Runs where Direction =dir.Direction)[RunsCountByDir] " +
"from DIRECTION dir " +
"where Carrier = '" + carrier + "'";
FilLCurrentUserDirections = new DataTable();
SqlDataAdapter adapter = ConnectionToDatabase.GetRequestResult(sqlRequest);
try
{
adapter.Fill(FilLCurrentUserDirections);
}
catch
{
Switcher.Switch(new FirstPage());
}
if (FilLCurrentUserDirections.Rows.Count == 0)
{
AllDirections.Visibility = Visibility.Collapsed;
NoDirections.Visibility = Visibility.Visible;
}
else
{
AllDirections.Visibility = Visibility.Visible;
NoDirections.Visibility = Visibility.Collapsed;
AllDirections.ItemsSource = FilLCurrentUserDirections.DefaultView;
}
}
private void GetOrdersOnCurrentRun()
{
string sqlRequest = "Select Client, CargoWeight, (select PhoneNumber from Users where Username = Client)[phoneNumber] from Orders where run = '" + FillCurrentDirectionRuns.Rows[RunsOnChosenDirection.SelectedIndex][0] + "'";
FillCurrentRunOrders = new DataTable();
try
{
SqlDataAdapter adapter = ConnectionToDatabase.GetRequestResult(sqlRequest);
adapter.Fill(FillCurrentRunOrders);
}
catch
{
Switcher.Switch(new FirstPage());
}
AllOrdersOnCurrentRun.Visibility = Visibility.Visible;
if (FillCurrentRunOrders.Rows.Count != 0)
{
AllOrdersOnCurrentRunTable.ItemsSource = FillCurrentRunOrders.DefaultView;
AllOrdersOnCurrentRunTable.Visibility = Visibility.Visible;
AllOrdersTopic.Visibility = Visibility.Visible;
NoOrders.Visibility = Visibility.Collapsed;
}
else
{
AllOrdersOnCurrentRunTable.Visibility = Visibility.Collapsed;
AllOrdersTopic.Visibility = Visibility.Collapsed;
NoOrders.Visibility = Visibility.Visible;
}
}
private void Page_Loaded(object sender, RoutedEventArgs e)
{
string sqlRequest = "select Username from users where Userstatus = 'Carrier'";
SqlDataAdapter adapter = ConnectionToDatabase.GetRequestResult(sqlRequest);
DataTable Carriers = new DataTable();
try
{
adapter.Fill(Carriers);
}
catch
{
Switcher.Switch(new FirstPage());
}
ForAdmin.Items.Clear();
for (int i = 0; i < Carriers.Rows.Count; i++)
{
ForAdmin.Items.Add(Convert.ToString(Carriers.Rows[i][0]));
}
if (CurrentUser.UserStatusV == "Admin")
{
ForAdmin.Visibility = Visibility.Visible;
ForAdminText.Visibility = Visibility.Visible;
carrier = Convert.ToString(ForAdmin.SelectedItem);
}
else
{
ForAdminText.Visibility = Visibility.Collapsed;
ForAdmin.Visibility = Visibility.Collapsed;
carrier = CurrentUser.UsernameV;
}
LoadTable(carrier);
}
private void showFullMessage(int messageID)
{
string sqlRequest = "Select [from], headerMessage, messageMain, [to] from ALLMESSAGERS where IDmessage = '" + messageID + "';";
DataTable CurrentMessage = new DataTable();
try
{
SqlDataAdapter adapter = ConnectionToDatabase.GetRequestResult(sqlRequest);
adapter.Fill(CurrentMessage);
}
catch
{
Switcher.Switch(new FirstPage());
}
if (!outMessage)
{
sqlRequest = "Update ALLMESSAGERS set IsReaded = 1 where IDmessage = '" + messageID + "'";
}
ConnectionToDatabase.insertData(sqlRequest);
Sender.Text = Convert.ToString(CurrentMessage.Rows[0][0]);
Adresee.Text = Convert.ToString(CurrentMessage.Rows[0][3]);
Header.Text = Convert.ToString(CurrentMessage.Rows[0][1]);
Text.Text = Convert.ToString(CurrentMessage.Rows[0][2]);
ReadMessage.Visibility = Visibility.Visible;
ShowAllMessages.Visibility = Visibility.Collapsed;
}
private void loadRunsTable()
{
string sqlRequest = "select Run , DepartureDate, (Select COUNT(*) from orders where Run =r.Run)[OrderCount], RunStatus from RUNS r where DIRECTION ='" + FilLCurrentUserDirections.Rows[AllDirections.SelectedIndex][0] + "'";
FillCurrentDirectionRuns = new DataTable();
try
{
SqlDataAdapter adapter = ConnectionToDatabase.GetRequestResult(sqlRequest);
adapter.Fill(FillCurrentDirectionRuns);
}
catch
{
Switcher.Switch(new FirstPage());
}
if (FillCurrentDirectionRuns.Rows.Count != 0)
{
RunsOnChosenDirection.Visibility = Visibility.Visible;
NoRuns.Visibility = Visibility.Collapsed;
RunsOnChosenDirection.ItemsSource = FillCurrentDirectionRuns.DefaultView;
}
else
{
RunsOnChosenDirection.Visibility = Visibility.Collapsed;
NoRuns.Visibility = Visibility.Visible;
}
}
private void FillOutcomingMessages()
{
string sqlRequest = "select IDmessage, [from] , headerMessage, case when IsReaded = 0 then N'Не прочитано' else N'Прочитано' end IsReaded from ALLMESSAGERS where [from] = '" + Receiver + "'";
Outcoming = new DataTable();
try
{
SqlDataAdapter adapter = ConnectionToDatabase.GetRequestResult(sqlRequest);
adapter.Fill(Outcoming);
}
catch
{
Switcher.Switch(new FirstPage());
}
if (Outcoming.Rows.Count != 0)
{
NoOutcoming.Visibility = Visibility.Collapsed;
OutMessages.Visibility = Visibility.Visible;
OutMessages.ItemsSource = Outcoming.DefaultView;
}
else
{
NoOutcoming.Visibility = Visibility.Visible;
OutMessages.Visibility = Visibility.Collapsed;
}
}
private void LoadOrders()
{
string sqlRequest = "select [Order],DepartingPoint, ArrivalPoint, Price*CargoWeight[OrderPrice], CargoWeight, OrderStatus " +
"from Orders Inner join Runs on Orders.Run = Runs.Run " +
"inner join Direction on Runs.Direction = Direction.Direction where Client = '" + CurrentUser.UsernameV + "'";
CurrentUserOrders = new DataTable();
try
{
SqlDataAdapter adapter = ConnectionToDatabase.GetRequestResult(sqlRequest);
adapter.Fill(CurrentUserOrders);
}
catch
{
Switcher.Switch(new FirstPage());
}
if (CurrentUserOrders.Rows.Count == 0)
{
ThisUserOrders.Visibility = System.Windows.Visibility.Collapsed;
tip.Visibility = Visibility.Collapsed;
NoOrders.Visibility = System.Windows.Visibility.Visible;
}
else
{
NoOrders.Visibility = System.Windows.Visibility.Collapsed;
tip.Visibility = Visibility.Visible;
ThisUserOrders.ItemsSource = CurrentUserOrders.DefaultView;
}
}
private void NewMessage_Click(object sender, RoutedEventArgs e)
{
if (CurrentUser.UserStatusV == "Admin")
{
{
string sqlRequest = "select Username from users where Userstatus != 'Admin'";
DataTable Users = new DataTable();
try
{
SqlDataAdapter adapter = ConnectionToDatabase.GetRequestResult(sqlRequest);
adapter.Fill(Users);
}
catch
{
Switcher.Switch(new FirstPage());
}
SelectUser.Items.Clear();
for (int i = 0; i < Users.Rows.Count; i++)
{
SelectUser.Items.Add(Convert.ToString(Users.Rows[i][0]));
}
SelectUser.SelectedIndex = 0;
}
MessageText.Height = 128;
ChooseUsers.Visibility = Visibility.Visible;
}
else
{
MessageText.Height = 150;
ChooseUsers.Visibility = Visibility.Collapsed;
}
ShowAllMessages.Visibility = Visibility.Collapsed;
WriteNewMessage.Visibility = Visibility.Visible;
}
private void getOrderInfo()
{
string SqlRequest = "select DepartingPoint, ArrivalPoint, cast (DepartureDate as date) [DepartureDate], cast(Departing as time(0))[DepartingTime],cast(Arrival as time(0))[ArrivalTime], (select Count (*) from ORDERS)[OrderAmount], Price, case when r.AvailableSpace is NULL or (select sum (CargoWeight) from Orders where Run = r.Run) is null then 0 else (r.AvailableSpace -(select sum (CargoWeight) from Orders where Run = r.Run)) end SpaceAvailable " +
"from DIRECTION inner join Runs r on r.Direction = DIRECTION.Direction where Run = " + NewOrder.RunV;
SqlDataAdapter adapter = ConnectionToDatabase.GetRequestResult(SqlRequest);
adapter.Fill(OrderInfo);
}
private void LoadTable()
{
string SqlRequest = "select direction.Direction[DirectionKey], Run[RunKey], DepartingPoint, ArrivalPoint, cast(Departing as time(0))[DepartingTime],cast(Arrival as time(0))[ArrivalTime], Round((cast(datediff(minute, Departing, Arrival) as float(2)) / 60), 2)[TimeToReach],Price, cast (DepartureDate as date) [DepartureDate], Carrier, Ranking " +
" from DIRECTION inner join Runs on RUNS.Direction = DIRECTION.Direction " +
"inner join USERS on DIRECTION.Carrier = USERS.Username ";
if (FromPoint.Text != "" || ToPoint.Text != "")
{
SqlRequest += "where ";
}
if (FromPoint.Text != "")
{
SqlRequest += "(DepartingPoint = N'" + FromPoint.Text + "')";
if (ToPoint.Text != "")
{
SqlRequest += " and ";
}
}
if (ToPoint.Text != "")
{
SqlRequest += "(ArrivalPoint = N'" + ToPoint.Text + "')";
}
SqlRequest += " Order by ";
if (HowToSort.SelectedIndex == 0)
{
SqlRequest += " TimeToReach";
}
else
{
SqlRequest += " Price";
}
if (Order.SelectedIndex == 1)
{
SqlRequest += " Desc";
}
DirectionsTable = new DataTable();
try
{
SqlDataAdapter Adapter = ConnectionToDatabase.GetRequestResult(SqlRequest);
Adapter.Fill(DirectionsTable);
}
catch
{
Switcher.Switch(new FirstPage());
}
if (DirectionsTable.Rows.Count == 0)
{
AllDirections.Visibility = System.Windows.Visibility.Collapsed;
Tip.Text = "Поиск не дал результатов.";
}
else
{
AllDirections.Visibility = System.Windows.Visibility.Visible;
Tip.Text = "Нажмите на результат, для бронирования места";
AllDirections.ItemsSource = DirectionsTable.DefaultView;
}
}
private void UpdateTable()
{
SqlDataAdapter adapter;
string sqlRequest = "select Username, UserPassword, Userstatus, PhoneNumber, Convert(nvarchar,RegistrationDate,1)[RegistrationDate1], Ranking from USERS ";
if (SearchVar.Text != "" && SearchVar.Text != null && SearchVar.Text != " ")
{
sqlRequest += "where ";
switch (SearchParams.SelectedIndex)
{
case 0:
sqlRequest += "Username";
break;
case 1:
sqlRequest += "PhoneNumber";
break;
}
sqlRequest += " like N'%" + SearchVar.Text + "%' ";
}
sqlRequest += "ORDER BY ";
switch (SortPref.SelectedIndex)
{
case 0:
sqlRequest += "Username";
break;
case 1:
sqlRequest += "Userstatus";
break;
case 2:
sqlRequest += "Ranking";
break;
case 3:
sqlRequest += "RegistrationDate";
break;
}
if (SortOrder.SelectedIndex == 1)
{
sqlRequest += " Desc";
}
UsersTable = new DataTable();
try
{
adapter = ConnectionToDatabase.GetRequestResult(sqlRequest);
adapter.Fill(UsersTable);
}
catch
{
Switcher.Switch(new FirstPage());
}
AllUsers.ItemsSource = UsersTable.DefaultView;
}
private void Enter(object sender, RoutedEventArgs e)
{
if (UsernameEntered.Text.Length == 0)
{
UsernameEntered.BorderBrush = Brushes.Red;
UsernameError.Text = "Заполните это поле";
}
if (PasswordEntered.Password.Length == 0)
{
PasswordEntered.BorderBrush = Brushes.Red;
passwordError.Text = "Заполните это поле";
}
if (UsernameError.Text == "" && passwordError.Text == "")
{
string sqlCommand = "select UserPassword from Users where Username = '******'";
SqlDataAdapter adapter = ConnectionToDatabase.GetRequestResult(sqlCommand);
DataTable ThisUser = new DataTable();
DataTable ThisUserFound = new DataTable();
try
{
adapter.Fill(ThisUser);
}
catch (Exception ex)
{
Switcher.Switch(new pages.FirstPage());
}
if (ThisUser.Rows.Count == 0)
{
UsernameEntered.BorderBrush = Brushes.Red;
UsernameError.Text = "Пользователь не найден, проверьте введённые данные";
}
else
{
if (Convert.ToString(ThisUser.Rows[0][0]) != PasswordEntered.Password)
{
PasswordEntered.BorderBrush = Brushes.Red;
passwordError.Text = "Неверный пароль";
}
else
{
sqlCommand = "select * from Users where Username = '******'";
adapter = ConnectionToDatabase.GetRequestResult(sqlCommand);
adapter.Fill(ThisUserFound);
DBUsers EnteredUser = new DBUsers((string)ThisUserFound.Rows[0][0], (string)ThisUserFound.Rows[0][1], (long)ThisUserFound.Rows[0][3], (string)ThisUserFound.Rows[0][2], (DateTime)ThisUserFound.Rows[0][4], (int)ThisUserFound.Rows[0][5]);
SendedObject Send = new SendedObject(EnteredUser);
Switcher.Switch(new UserMainPage(), Send);
}
}
}
}
private void OrdersAmount()
{
string SqlRequest = "select Count(*) from ORDERS where client = '" + CurrentUser.UsernameV + "'";
DataTable OrderCount = new DataTable();
try
{
SqlDataAdapter adapter = ConnectionToDatabase.GetRequestResult(SqlRequest);
adapter.Fill(OrderCount);
}
catch
{
Switcher.Switch(new FirstPage());
}
ShowOrderAmount.Text = "Колличество заказов: " + OrderCount.Rows[0][0];
}
private void AllUsers_Selected(object sender, RoutedEventArgs e)
{
switch (UsersTable.Rows[AllUsers.SelectedIndex][2])
{
case "User":
ShowStatus.SelectedIndex = 0;
break;
case "Carrier":
ShowStatus.SelectedIndex = 1;
break;
case "Admin":
ShowStatus.SelectedIndex = 2;
break;
}
ShowRanking.Value = Convert.ToDouble(UsersTable.Rows[AllUsers.SelectedIndex][5]);
AboutAccount.Visibility = Visibility.Visible;
ChangeSelectedAccount.Visibility = Visibility.Visible;
{
string sqlRequest = "select [Order],DepartingPoint, ArrivalPoint, Price*CargoWeight[OrderPrice], CargoWeight, OrderStatus " +
"from Orders Inner join Runs on Orders.Run = Runs.Run " +
"inner join Direction on Runs.Direction = Direction.Direction where Client = '" + UsersTable.Rows[AllUsers.SelectedIndex][0] + "'";
DataTable CurrentUserOrders = new DataTable();
try
{
SqlDataAdapter adapter = ConnectionToDatabase.GetRequestResult(sqlRequest);
adapter.Fill(CurrentUserOrders);
}
catch
{
Switcher.Switch(new FirstPage());
}
if (CurrentUserOrders.Rows.Count == 0)
{
ThisUserOrders.Visibility = System.Windows.Visibility.Collapsed;
}
else
{
ThisUserOrders.ItemsSource = CurrentUserOrders.DefaultView;
}
Status.Text = "Пользователь: " + Convert.ToString(UsersTable.Rows[AllUsers.SelectedIndex][0]) + " Статус пользователя: " + Convert.ToString(UsersTable.Rows[AllUsers.SelectedIndex][2]) + " Рейтинг: " + Convert.ToString(UsersTable.Rows[AllUsers.SelectedIndex][5]);
AllOrders.Text = "Всего заказов: " + Convert.ToString(CurrentUserOrders.Rows.Count);
}
}
private void Register(object sender, RoutedEventArgs e)
{
string sqlRequest = "select Username from Users where Username = '******'";
DataTable Usernames = new DataTable();
try
{
if (UsernameEntered.Text.Length == 0)
{
usernameError.Text = "Заполните это поле";
UsernameEntered.BorderBrush = Brushes.Red;
}
else
{
if (Regex.IsMatch(UsernameEntered.Text, @"\W"))
{
usernameError.Text = "Имя пользователя должно состоять из чисел и букв";
UsernameEntered.BorderBrush = Brushes.Red;
}
else
{
SqlDataAdapter adapter = ConnectionToDatabase.GetRequestResult(sqlRequest);
adapter.Fill(Usernames);
if (Usernames.Rows.Count != 0)
{
usernameError.Text = "Имя пользователя занято";
UsernameEntered.BorderBrush = Brushes.Red;
}
}
}
if (PasswordEntered1.Password.Length == 0)
{
password1Error.Text = "Заполните это поле";
PasswordEntered1.BorderBrush = Brushes.Red;
}
else
{
if (PasswordEntered1.Password.Length < 4)
{
password1Error.Text = "Пароль слишком короткий (минимум 4 символа)";
PasswordEntered1.BorderBrush = Brushes.Red;
}
}
if (PasswordEntered2.Password.Length == 0)
{
password2Error.Text = "Заполните это поле";
PasswordEntered2.BorderBrush = Brushes.Red;
}
else
{
if (PasswordEntered1.Password != PasswordEntered2.Password)
{
password2Error.Text = "Пароли не совпадают";
PasswordEntered1.BorderBrush = Brushes.Red;
PasswordEntered2.BorderBrush = Brushes.Red;
}
}
if (PhoneNumber.Text.Length == 0)
{
phoneError.Text = "Заполните это поле";
PhoneNumber.BorderBrush = Brushes.Red;
}
else
{
if (PhoneNumber.Text.Length < 12 || Regex.IsMatch(PhoneNumber.Text, @"\D"))
{
phoneError.Text = "Введите корректный номер мобильного телефона, начиная с цифры";
PhoneNumber.BorderBrush = Brushes.Red;
}
}
if (usernameError.Text == "" && password1Error.Text == "" && password2Error.Text == "" && phoneError.Text == "")
{
DBUsers CurrentUser = new DBUsers(UsernameEntered.Text, PasswordEntered1.Password, Convert.ToInt64(PhoneNumber.Text));
sqlRequest = "insert into " +
"USERS (Username, UserPassword, PhoneNumber, RegistrationDate, Ranking) " +
"values ('" + CurrentUser.UsernameV + "', '" + CurrentUser.UserPasswordV + "', '" + CurrentUser.PhoneNumberV + "', '" + CurrentUser.RegistrarionDateV.Year + "-" + CurrentUser.RegistrarionDateV.Month + "-" + CurrentUser.RegistrarionDateV.Day + "', '" + CurrentUser.RankingV + "')";
int i = ConnectionToDatabase.insertData(sqlRequest);
if (i == 0)
{
SendedObject send = new SendedObject(CurrentUser);
Switcher.Switch(new UserMainPage(), send);
}
}
}
catch
{
Switcher.Switch(new FirstPage());
}
}