private void SaveBN_Click(object sender, RoutedEventArgs e) // сохранение изменений { try { if ((SexFemaleRB.IsChecked == false && SexMaleRB.IsChecked == false) || FullNameTB.Text == "" || NumberTB.Text == "" || IssuedByTB.Text == "" || AddresTB.Text == "" || CodeTB.Text == "" || TypeTB.Text == "") { System.Windows.Forms.MessageBox.Show("Заполните поля"); return; } string sql = "UPDATE [Document] SET [Type] = '" + TypeTB.Text + "', " + "[Code] = '" + CodeTB.Text + "', " + "[Number] = '" + NumberTB.Text + "', " + "[DI] = '" + Convert.ToString(Convert.ToDateTime(DayIDT.Text)) + "', " + "[IssuedBy] = '" + IssuedByTB.Text + "' " + "WHERE [DocumentID] = " + Clients.index; WorkWithBD.Update(sql); string sex = (SexMaleRB.IsChecked == true) ? "М" : "Ж"; sql = "UPDATE [Client] SET [FullNameClient] = '" + FullNameTB.Text + "', " + "[Sex] = '" + sex + "', " + "[DOB] = '" + Convert.ToString(Convert.ToDateTime(BirthdayDT.Text)) + "', " + "[Address] = '" + AddresTB.Text + "', " + "[Tel] = '" + TelTB.Text + "', " + "[Email] = '" + EmailTB.Text + "' " + "WHERE [ClientID] = " + Clients.index; WorkWithBD.Update(sql); Close(); } catch { System.Windows.Forms.MessageBox.Show("Введите корректные данные"); } }
private void start() { string sql = "SELECT Service.ServiceID, Service.Service, " + "Service.Cost " + "FROM Service"; DataGridTotal.ItemsSource = (WorkWithBD.outPutdb(sql)).Tables[0].DefaultView; }
public static void removeClientdb(int index) // удаление из бд клиента { string sql = "SELECT RegistrationID FROM Registration WHERE Registration.ClientID=" + index + ""; List <int> list = new List <int>(); for (int i = 0; i < Convert.ToInt32(WorkWithBD.outPutdb(sql).Tables[0].Rows.Count); i++) { list.Add(Convert.ToInt32(WorkWithBD.outPutdb(sql).Tables[0].DefaultView[i].Row[0].ToString())); } for (int i = 0; i < list.Count; i++) { removeServiceListdb(list[i]); removeRegistrationdb(list[i]); } using (SqlConnection sqlConn = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand("DELETE FROM [Client] WHERE [ClientID] = " + index, sqlConn)) { sqlConn.Open(); cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); } } using (SqlConnection sqlConn = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand("DELETE FROM [Document] WHERE [DocumentID] = " + index, sqlConn)) { sqlConn.Open(); cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); } } }
public static void inPutRegistrationdb(string sql, DateTime AD, DateTime DD, string Client, int Room, string Employee, string Booked) // добавление в бд { Int64 id; string sqlClient = "SELECT ClientID FROM Client WHERE FullNameClient='" + Client + "'"; int ClientID = Convert.ToInt32((WorkWithBD.outPutdb(sqlClient)).Tables[0].DefaultView[0].Row[0]); string sqlEmployee = "SELECT EmployeeID FROM Employee WHERE FullName='" + Employee + "'"; int EmployeeID = Convert.ToInt32((WorkWithBD.outPutdb(sqlEmployee)).Tables[0].DefaultView[0].Row[0]); using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); using (var cmd = new SqlCommand(sql, connection)) { cmd.Parameters.AddWithValue("@ClientID", ClientID); cmd.Parameters.AddWithValue("@RoomID", Room); cmd.Parameters.AddWithValue("@EmployeeID", EmployeeID); cmd.Parameters.AddWithValue("@Booked", Booked); cmd.Parameters.AddWithValue("@AD", AD); cmd.Parameters.AddWithValue("@DD", DD); SqlParameter idParam = new SqlParameter { ParameterName = "@RegistrationID", SqlDbType = SqlDbType.Int, Direction = ParameterDirection.Output, }; cmd.Parameters.Add(idParam); cmd.ExecuteNonQuery(); cmd.CommandText = "SELECT @@IDENTITY"; id = Convert.ToInt64(cmd.ExecuteScalar()); } } }
private void checkRoom() // проверка, свободна ли комната в данный промежуток дней или нет { string sql = "SELECT Registration.AD, Registration.DD " + "FROM Registration JOIN Room ON(Registration.RoomID = Room.RoomID) JOIN Type ON (Room.TypeID = Type.TypeID) " + "WHERE " + //(((Registration.AD < '" + dDayAD + "' AND Registration.DD < '" + dDayAD + "' AND Registration.AD < '" + dDayDD + "' AND Registration.DD < '" + dDayDD + "') OR (Registration.AD > '" + dDayAD + "' AND Registration.DD < '" + dDayAD + "' AND Registration.AD < '" + dDayDD + "' AND Registration.DD < '" + dDayDD + "')) AND " + "Registration.RoomID = " + sRoomIDCB + ""; //if (WorkWithBD.outPutdb(sql).Tables[0].DefaultView.Count == 0) MessageBox.Show("Можно бронировать"); //else { MessageBox.Show("Выберите другие дни"); } //// WorkWithBD.outPutdb(sql).Tables[0].DefaultView[i].Row[0]; int buf = 0; for (int i = 0; i < WorkWithBD.outPutdb(sql).Tables[0].DefaultView.Count; i++) { if (((Convert.ToDateTime(WorkWithBD.outPutdb(sql).Tables[0].DefaultView[i].Row[0]) < dDayAD && Convert.ToDateTime(WorkWithBD.outPutdb(sql).Tables[0].DefaultView[i].Row[1]) < dDayAD) && ((Convert.ToDateTime(WorkWithBD.outPutdb(sql).Tables[0].DefaultView[i].Row[0]) < dDayDD && Convert.ToDateTime(WorkWithBD.outPutdb(sql).Tables[0].DefaultView[i].Row[1]) < dDayDD))) || ((Convert.ToDateTime(WorkWithBD.outPutdb(sql).Tables[0].DefaultView[i].Row[0]) > dDayAD && Convert.ToDateTime(WorkWithBD.outPutdb(sql).Tables[0].DefaultView[i].Row[1]) > dDayAD) && ((Convert.ToDateTime(WorkWithBD.outPutdb(sql).Tables[0].DefaultView[i].Row[0]) > dDayDD && Convert.ToDateTime(WorkWithBD.outPutdb(sql).Tables[0].DefaultView[i].Row[1]) > dDayDD)))) { buf++; } else { MessageBox.Show("Выберите другие дни"); return; } } if (buf == WorkWithBD.outPutdb(sql).Tables[0].DefaultView.Count) { MessageBox.Show("Можно бронировать"); } }
private void AddBN_Click(object sender, RoutedEventArgs e) { try { if (Convert.ToDateTime(DayDD.Text) < Convert.ToDateTime((DateTime.Now).ToString("d")) || Convert.ToDateTime(DayAD.Text) < Convert.ToDateTime((DateTime.Now).ToString("d"))) { MessageBox.Show("Введите корректные даты1"); return; } if (Convert.ToDateTime(DayDD.Text) < Convert.ToDateTime(DayAD.Text)) { MessageBox.Show("Введите корректные даты2"); return; } if (BookedIDCB.Text == "") { MessageBox.Show("Заполните поле: статус регистрации"); return; } string sql = "INSERT INTO Registration (ClientID,RoomID, EmployeeID,Booked,AD,DD) " + "VALUES (@ClientID,@RoomID, @EmployeeID,@Booked,@AD,@DD)"; WorkWithBD.inPutRegistrationdb(sql, Convert.ToDateTime(DayAD.Text), Convert.ToDateTime(DayDD.Text), ClientIDCB.Text, Convert.ToInt32(RoomIDCB.Text), EmloyeeIDCB.Text, BookedIDCB.Text); // string update = "UPDATE [Room] SET [Status] ='" + BookedIDCB.Text + "' WHERE RoomID=" + RoomIDCB.Text; // WorkWithBD.Update(update); Close(); } catch { System.Windows.Forms.MessageBox.Show("Введите корректные данные"); } }
private void DelBN_Click(object sender, RoutedEventArgs e) // нажатие кнопки удалить при различных менюшек слева { switch (ListViewMenu.SelectedIndex) { case 0: // главная WorkWithBD.removeRegistrationdb(MainMenu.index); ContentGrid.Children.Clear(); ContentGrid.Children.Add(new MainMenu()); break; case 1: //свободные номера break; case 2: // услуги WorkWithBD.removeServicedb(Service.index); ContentGrid.Children.Clear(); ContentGrid.Children.Add(new Service()); break; case 3: // услуги клиентов try { string sql = "SELECT Status FROM ServiceList WHERE ServiceListID=" + ServiceClients.index; if (WorkWithBD.outPutdb(sql).Tables[0].DefaultView[0].Row[0].ToString() == "Выполнено") { MessageBox.Show("Эта услуга оказана, нельзя удалить"); } else { WorkWithBD.removeServiceListGooddb(ServiceClients.index); ContentGrid.Children.Clear(); ContentGrid.Children.Add(new ServiceClients()); } } catch { } break; case 4: // информация о клиентах WorkWithBD.removeClientdb(Clients.index); ContentGrid.Children.Clear(); ContentGrid.Children.Add(new Clients()); break; case 5: //отчет break; case 6: // справка break; default: MessageBox.Show("Не выбран пункт слева"); break; } }
private void start() { string sql = "SELECT Registration.RegistrationID, Registration.RoomID, Type.Class, Type.DayCost, Registration.AD, Registration.DD " + "FROM Registration JOIN Room ON(Registration.RoomID = Room.RoomID) JOIN Type ON(Room.TypeID = Type.TypeID) " + "WHERE(Registration.RoomID = " + WindowAddRegistration.sRoomIDCB + ")"; DataGridTotal.ItemsSource = (WorkWithBD.outPutdb(sql)).Tables[0].DefaultView; }
private void addClient() { string sql = "SELECT Client.FullNameClient " + "FROM Client"; for (int i = 0; i < ((WorkWithBD.outPutdb(sql)).Tables[0].DefaultView.Count); i++) { ClientCB.Items.Add((WorkWithBD.outPutdb(sql)).Tables[0].DefaultView[i].Row[0]); } }
private void addRegistration() { string sql = "SELECT Registration.RegistrationID, Client.FullNameClient " + "FROM Registration JOIN Client ON(Registration.ClientID=Client.ClientID) WHERE (Registration.Booked = 'Заселился' OR Registration.Booked = 'Забронировал')"; for (int i = 0; i < ((WorkWithBD.outPutdb(sql)).Tables[0].DefaultView.Count); i++) { RegistrationIDCB.Items.Add((WorkWithBD.outPutdb(sql)).Tables[0].DefaultView[i].Row[0] + " " + (WorkWithBD.outPutdb(sql)).Tables[0].DefaultView[i].Row[1].ToString()); } }
private void addEmployees() // добавление тех сотрудников, которые есть в комбо бокс { string sql = "SELECT Employee.FullName " + "FROM Employee WHERE (Employee.Post = 'Управляющий' OR Employee.Post = 'Администратор' OR Employee.Post = 'Менеджер по бронированию')"; for (int i = 0; i < ((WorkWithBD.outPutdb(sql)).Tables[0].DefaultView.Count); i++) { EmloyeeIDCB.Items.Add((WorkWithBD.outPutdb(sql)).Tables[0].DefaultView[i].Row[0]); } }
private void addService() { string sql = "SELECT Service.Service " + "FROM Service"; for (int i = 0; i < ((WorkWithBD.outPutdb(sql)).Tables[0].DefaultView.Count); i++) { ServiceIDCB.Items.Add((WorkWithBD.outPutdb(sql)).Tables[0].DefaultView[i].Row[0]); } }
private void addRegistration(string FullNameClient) { string sql = "SELECT Registration.RegistrationID " + "FROM Registration JOIN Client ON (Registration.ClientID = Client.ClientID) WHERE (Client.FullNameClient = '" + FullNameClient + "')"; for (int i = 0; i < ((WorkWithBD.outPutdb(sql)).Tables[0].DefaultView.Count); i++) { RegistrationClientCB.Items.Add((WorkWithBD.outPutdb(sql)).Tables[0].DefaultView[i].Row[0]); } }
private void addRooms() // добавление свободных комнат { string sql = "SELECT Room.RoomID " + "FROM Room"; for (int i = 0; i < ((WorkWithBD.outPutdb(sql)).Tables[0].DefaultView.Count); i++) { RoomIDCB.Items.Add((WorkWithBD.outPutdb(sql)).Tables[0].DefaultView[i].Row[0]); } }
private void start() { string sql = "SELECT ServiceList.ServiceListID, Registration.RegistrationID,Service.ServiceID, Client.FullNameClient, Service.Service, " + "Service.Cost, ServiceList.Status " + "FROM ServiceList " + "JOIN Registration ON(ServiceList.RegistrationID=Registration.RegistrationID) " + "JOIN Client ON(Registration.ClientID=Client.ClientID) " + "JOIN Service ON(ServiceList.ServiceID=Service.ServiceID) WHERE (Registration.Booked = 'Заселился' OR Registration.Booked = 'Забронировал')"; DataGridTotal.ItemsSource = (WorkWithBD.outPutdb(sql)).Tables[0].DefaultView; }
private void start() { string sql = "SELECT Room.RoomID, " + "Type.Class, " + "Type.Berths, " + "Type.DayCost, " + "Type.Info, " + "Room.QQ " + "FROM Room JOIN Type ON(Room.TypeID=Type.TypeID)"; DataGridTotal.ItemsSource = (WorkWithBD.outPutdb(sql)).Tables[0].DefaultView; }
private void addClient() // добавление тех сотрудников, которые есть в комбо бокс { ClientIDCB.Items.Clear(); string sql = "SELECT Client.FullNameClient " + "FROM Client"; for (int i = 0; i < ((WorkWithBD.outPutdb(sql)).Tables[0].DefaultView.Count); i++) { ClientIDCB.Items.Add((WorkWithBD.outPutdb(sql)).Tables[0].DefaultView[i].Row[0]); } }
private void AddBN_Click(object sender, RoutedEventArgs e) // сохранение изменений { try { string sql = "UPDATE [Room] SET [QQ] = '" + QQTB.Text + "' WHERE [RoomID] = " + AvailableRooms.index; WorkWithBD.Update(sql); Close(); } catch { System.Windows.Forms.MessageBox.Show("Введите корректные данные"); } }
private void fillingPayment() { string sql = "SELECT Registration.RegistrationID " + "FROM Registration"; for (int i = 0; i < Convert.ToInt32(WorkWithBD.outPutdb(sql).Tables[0].DefaultView.Count); i++) { WorkWithBD.sumForRoom(Convert.ToInt32(WorkWithBD.outPutdb(sql).Tables[0].DefaultView[i].Row[0])); WorkWithBD.sumForService(Convert.ToInt32(WorkWithBD.outPutdb(sql).Tables[0].DefaultView[i].Row[0]), 0); WorkWithBD.TotalSum(Convert.ToInt32(WorkWithBD.outPutdb(sql).Tables[0].DefaultView[i].Row[0])); } }
private void start() { string sql = "SELECT Registration.RegistrationID, " + "Registration.PaymentL, " + "Registration.PaymentS, " + "Registration.TotalSumD, " + "Registration.EmployeeID, " + "Employee.FullName " + "FROM Registration " + "JOIN Employee ON(Registration.EmployeeID=Employee.EmployeeID) WHERE (Registration.Booked = 'Забронировал')"; DataGridTotal.ItemsSource = (WorkWithBD.outPutdb(sql)).Tables[0].DefaultView; }
private void start() { string sql = "SELECT Client.FullNameClient, Service.Service " + "FROM Registration JOIN ServiceList ON (ServiceList.RegistrationID=Registration.RegistrationID)" + " JOIN Client ON (Client.ClientID=Registration.ClientID)" + " JOIN Service ON (Service.ServiceID=ServiceList.ServiceID) Where Service Like '%" + WindowSearch.InfoSearch + "%'"; // "UNION " + "SELECT Room.RoomID, Type.Class, " + "Type.DayCost " + for (int i = 0; i < (WorkWithBD.outPutdb(sql)).Tables[0].DefaultView.Count; i++) { DataGridService.Items.Add((WorkWithBD.outPutdb(sql).Tables[0].DefaultView[i])); } }
private void AddBN_Click(object sender, RoutedEventArgs e) { try { string sql = "UPDATE [Registration] SET [Booked] = '" + BookedCB.Text + "' " + "WHERE [RegistrationID] = " + MainMenu.index; WorkWithBD.Update(sql); sql = "SELECT RoomID FROM Registration WHERE RegistrationID=" + MainMenu.index; sql = "UPDATE [Room] SET [Status] ='" + BookedCB.Text + "' WHERE RoomID=" + (WorkWithBD.outPutdb(sql)).Tables[0].DefaultView[0].Row[0]; WorkWithBD.Update(sql); Close(); } catch { System.Windows.Forms.MessageBox.Show("Введите корректные данные"); } }
private void AddBN_Click(object sender, RoutedEventArgs e) { try { if (ServiceTB.Text == "") { MessageBox.Show("Заполните название услуги"); return; } string sql = "UPDATE [Service] SET [Service] = '" + ServiceTB.Text + "', " + "[Cost] = '" + CostTB.Text + "' " + "WHERE [ServiceID] = " + Service.index; WorkWithBD.Update(sql); Close(); } catch { System.Windows.Forms.MessageBox.Show("Введите корректные данные"); } }
private void AddBN_Click(object sender, RoutedEventArgs e) { string sql = "INSERT INTO ServiceList (RegistrationID,ServiceID, Status) " + "VALUES (@RegistrationID,@ServiceID,@Status)"; try { WorkWithBD.inPutServiceClientdb(sql, Convert.ToInt32(RegistrationIDCB.Text.ToString().Split(' ')[0]), Convert.ToInt32(ServiceIDCB.Text.ToString().Split(' ')[0]), "Не выполнено"); Close(); } catch { MessageBox.Show("Ошибка при заполнении, проверьте введённые данные"); } }
public static void startStatment(string NameClient, string RegistrationClient) // запус { CreateWord("Statement"); string sql = "SELECT Registration.AD, Registration.DD, Registration.PaymentS,Registration.PaymentL, Registration.TotalSumD FROM Registration Where Registration.RegistrationID = " + RegistrationClient; findString("FullName", NameClient); findString("DayAD", Convert.ToDateTime((WorkWithBD.outPutdb(sql)).Tables[0].DefaultView[0].Row[0]).Date.ToString("d")); findString("DayDD", Convert.ToDateTime((WorkWithBD.outPutdb(sql)).Tables[0].DefaultView[0].Row[1]).Date.ToString("d")); findString("PaymentS", (WorkWithBD.outPutdb(sql)).Tables[0].DefaultView[0].Row[2].ToString()); findString("PaymentL", (WorkWithBD.outPutdb(sql)).Tables[0].DefaultView[0].Row[3].ToString()); findString("TotalSumD", (WorkWithBD.outPutdb(sql)).Tables[0].DefaultView[0].Row[4].ToString()); sql = "SELECT[Service].[Service], [Service].Cost FROM Registration JOIN ServiceList ON(Registration.RegistrationID= ServiceList.RegistrationID) JOIN[Service] ON(ServiceList.[ServiceID]=[Service].ServiceID) WHERE(Registration.RegistrationID= " + RegistrationClient + " AND ServiceList.Status='Выполнено')"; TableStatment(sql); }
private void start() { string sql = "SELECT Client.ClientID, Client.FullNameClient, Client.Sex, " + "Client.DOB, " + "Client.Address, " + "Client.Tel, " + "Client.Email, " + "Document.Type, " + "Document.Code, " + "Document.Number, " + "Document.DI, " + "Document.IssuedBy " + "FROM Client JOIN Document ON(Client.DocumentID=Document.DocumentID) "; DataGridTotal.ItemsSource = (WorkWithBD.outPutdb(sql)).Tables[0].DefaultView; }
private void AddBN_Click(object sender, RoutedEventArgs e) { string sql = "INSERT INTO Service (Service,Cost) " + "VALUES (@Service, @Cost)"; try { if (ServiceTB.Text == "") { MessageBox.Show("Заполните название услуги"); return; } WorkWithBD.inPutServicedb(sql, ServiceTB.Text, Convert.ToDouble(CostTB.Text)); Close(); } catch { MessageBox.Show("Ошибка при заполнении, проверьте введённые данные"); } }
private void start() { string sql = "SELECT Registration.RegistrationID, " + "Registration.PaymentL, " + "Registration.PaymentS, " + "Registration.TotalSumD, " + "Registration.Booked, " + "Registration.AD, " + "Registration.DD, " + "Employee.FullName, " + "Client.FullNameClient, " + "Room.RoomID " + "FROM Registration JOIN Employee ON(Registration.EmployeeID=Employee.EmployeeID) " + "JOIN Client ON(Registration.ClientID=Client.ClientID)" + "JOIN Room ON(Registration.RoomID=Room.RoomID) WHERE (Registration.Booked='Заселился' OR Registration.Booked='Забронировал')"; DataGridTotal.ItemsSource = (WorkWithBD.outPutdb(sql)).Tables[0].DefaultView; }
private void SaveBN_Click(object sender, RoutedEventArgs e) { try { if (ServiceIDCB.Text == "") { MessageBox.Show("Заполните название услуги"); return; } string sql = "UPDATE [ServiceList] SET [RegistrationID] = " + RegistrationIDCB.Text.ToString().Split(' ')[0] + ", " + "[ServiceID] = (SELECT [Service].ServiceID From [Service] Where Service.Service='" + ServiceIDCB.Text + "') , " + "[Status] = '" + StatusCB.Text + "' " + "WHERE [ServiceListID] = " + ServiceClients.index; WorkWithBD.Update(sql); Close(); } catch { System.Windows.Forms.MessageBox.Show("Введите корректные данные"); } }
public static void sumForService(int index, double PaymentS) // считается сумма за услуги PaymentS { string sql = "SELECT ServiceList.ServiceListID, " + "ServiceList.ServiceID, " + "Registration.RegistrationID, " + "Service.Cost " + "FROM ServiceList JOIN Registration ON(ServiceList.RegistrationID=Registration.RegistrationID) " + "JOIN Service ON(ServiceList.ServiceID=Service.ServiceID) " + "WHERE (ServiceList.RegistrationID=" + index + " AND ServiceList.Status='Выполнено')"; for (int j = 0; j < Convert.ToInt32((WorkWithBD.outPutdb(sql)).Tables[0].DefaultView.Count.ToString()); j++) // кол-во услуг у регистрации { PaymentS += Convert.ToDouble((WorkWithBD.outPutdb(sql)).Tables[0].DefaultView[j].Row[3].ToString()); } sql = "UPDATE [Registration] SET [PaymentS] = '" + PaymentS + "' " + "WHERE [RegistrationID] = " + index; Update(sql); }