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);
}
