/// <summary>
/// Метод восстановления содержимого модели из сервера
/// </summary>
/// <param name="root">Ссылка на корневой объект модели</param>
/// <param name="connection">Строка подключения</param>
/// <returns></returns>
public static bool RestoreTables(Root root, string connection)
{
var server = new Database.SqlServer {
Connection = connection
};
// предметы
var dataSet = server.GetRows("Matters");
if (dataSet.Tables.Count > 0)
{
root.Matters.Clear();
foreach (var row in dataSet.Tables[0].Rows.Cast <DataRow>())
{
if (row.ItemArray.Length != 2)
{
continue;
}
root.Matters.Add(new Matter
{
IdMatter = Guid.Parse(row.ItemArray[0].ToString()),
Name = row.ItemArray[1].ToString()
});
}
}
OperationResult = server.LastError;
if (!string.IsNullOrWhiteSpace(OperationResult))
{
return(false);
}
// специальности
dataSet = server.GetRows("Specialities");
if (dataSet.Tables.Count > 0)
{
root.Specialities.Clear();
foreach (var row in dataSet.Tables[0].Rows.Cast <DataRow>())
{
if (row.ItemArray.Length != 2)
{
continue;
}
root.Specialities.Add(new Speciality
{
IdSpeciality = Guid.Parse(row.ItemArray[0].ToString()),
Name = row.ItemArray[1].ToString()
});
}
}
OperationResult = server.LastError;
if (!string.IsNullOrWhiteSpace(OperationResult))
{
return(false);
}
// сдаваемые предметы
dataSet = server.GetRows("PassMatters");
if (dataSet.Tables.Count > 0)
{
root.PassMatters.Clear();
foreach (var row in dataSet.Tables[0].Rows.Cast <DataRow>())
{
if (row.ItemArray.Length != 4)
{
continue;
}
root.PassMatters.Add(new PassMatter
{
IdPassMatter = Guid.Parse(row.ItemArray[0].ToString()),
IdSpeciality = Guid.Parse(row.ItemArray[1].ToString()),
IdMatter = Guid.Parse(row.ItemArray[2].ToString()),
PassForm = (PassKind)Enum.Parse(typeof(PassKind), row.ItemArray[3].ToString())
});
}
}
OperationResult = server.LastError;
if (!string.IsNullOrWhiteSpace(OperationResult))
{
return(false);
}
// абитуриенты
dataSet = server.GetRows("Enrollees");
if (dataSet.Tables.Count > 0)
{
root.Enrollees.Clear();
foreach (var row in dataSet.Tables[0].Rows.Cast <DataRow>())
{
if (row.ItemArray.Length != 16)
{
continue;
}
root.Enrollees.Add(new Enrollee
{
IdEnrollee = Guid.Parse(row.ItemArray[0].ToString()),
RegistrationNumber = row.ItemArray[1].ToString(),
Surname = row.ItemArray[2].ToString(),
FirstName = row.ItemArray[3].ToString(),
LastName = row.ItemArray[4].ToString(),
BirthDay = DateTime.Parse(row.ItemArray[5].ToString()),
SecodarySchoolName = row.ItemArray[6].ToString(),
SecodarySchoolNumber = row.ItemArray[7].ToString(),
SecodarySchoolTown = row.ItemArray[8].ToString(),
GraduationDate = DateTime.Parse(row.ItemArray[9].ToString()),
GoldMedal = bool.Parse(row.ItemArray[10].ToString()),
Town = row.ItemArray[11].ToString(),
Street = row.ItemArray[12].ToString(),
HouseNumber = row.ItemArray[13].ToString(),
PhoneNumber = row.ItemArray[14].ToString(),
IdSpeciality = Guid.Parse(row.ItemArray[15].ToString())
});
}
}
OperationResult = server.LastError;
if (!string.IsNullOrWhiteSpace(OperationResult))
{
return(false);
}
return(true);
}
/// <summary>
/// Метод загрузки сохранённой ранее конфигурации из базы данных
/// </summary>
/// <param name="connection"></param>
/// <returns></returns>
public static Hotel LoadFromBase(string connection)
{
var hotel = new Hotel();
var server = new Database.SqlServer {
Connection = connection
};
// категории
var dataSet = server.GetRows("Categories");
if (dataSet.Tables.Count > 0)
{
foreach (var row in dataSet.Tables[0].Rows.Cast <DataRow>())
{
if (row.ItemArray.Length != 2)
{
continue;
}
hotel.Categories.Add(Guid.Parse(row.ItemArray[0].ToString()),
row.ItemArray[1].ToString());
}
}
OperationResult = server.LastError;
// услуги
dataSet = server.GetRows("Services");
if (dataSet.Tables.Count > 0)
{
foreach (var row in dataSet.Tables[0].Rows.Cast <DataRow>())
{
if (row.ItemArray.Length != 3)
{
continue;
}
hotel.Services.Add(Guid.Parse(row.ItemArray[0].ToString()),
row.ItemArray[1].ToString(),
decimal.Parse(row.ItemArray[2].ToString()));
}
}
OperationResult = server.LastError;
// комнаты
dataSet = server.GetRows("Rooms");
if (dataSet.Tables.Count > 0)
{
foreach (var row in dataSet.Tables[0].Rows.Cast <DataRow>())
{
if (row.ItemArray.Length != 6)
{
continue;
}
var room = hotel.Rooms.Add(Guid.Parse(row.ItemArray[0].ToString()),
Guid.Parse(row.ItemArray[1].ToString()),
int.Parse(row.ItemArray[2].ToString()),
int.Parse(row.ItemArray[3].ToString()),
decimal.Parse(row.ItemArray[4].ToString()),
row.ItemArray[5].ToString());
var ds = server.GetRows("RoomServices");
if (ds.Tables.Count > 0)
{
foreach (var item in ds.Tables[0].Rows.Cast <DataRow>())
{
if (item.ItemArray.Length != 2)
{
continue;
}
var roomId = Guid.Parse(item.ItemArray[0].ToString());
var serviceId = Guid.Parse(item.ItemArray[1].ToString());
if (room.IdRoom == roomId)
{
var service = hotel.Services.FirstOrDefault(s => s.IdService == serviceId);
if (service != null)
{
room.Services.Add(service);
}
}
}
}
}
}
OperationResult = server.LastError;
// должности
dataSet = server.GetRows("EmployeeRoles");
if (dataSet.Tables.Count > 0)
{
foreach (var row in dataSet.Tables[0].Rows.Cast <DataRow>())
{
if (row.ItemArray.Length != 4)
{
continue;
}
hotel.EmployeeRoles.Add(Guid.Parse(row.ItemArray[0].ToString()),
row.ItemArray[1].ToString(),
decimal.Parse(row.ItemArray[2].ToString()),
(AllowedOperations)uint.Parse(row.ItemArray[3].ToString()));
}
}
OperationResult = server.LastError;
// штат сотрудников
dataSet = server.GetRows("RegistryStaff");
if (dataSet.Tables.Count > 0)
{
foreach (var row in dataSet.Tables[0].Rows.Cast <DataRow>())
{
if (row.ItemArray.Length != 7)
{
continue;
}
hotel.RegistryStaff.Add(Guid.Parse(row.ItemArray[0].ToString()),
row.ItemArray[1].ToString(),
row.ItemArray[2].ToString(),
row.ItemArray[3].ToString(),
row.ItemArray[4].ToString(),
Guid.Parse(row.ItemArray[5].ToString()),
row.ItemArray[6].ToString());
}
}
OperationResult = server.LastError;
// клиенты
dataSet = server.GetRows("Clients");
if (dataSet.Tables.Count > 0)
{
foreach (var row in dataSet.Tables[0].Rows.Cast <DataRow>())
{
if (row.ItemArray.Length != 9)
{
continue;
}
hotel.Clients.Add(Guid.Parse(row.ItemArray[0].ToString()),
row.ItemArray[1].ToString(),
row.ItemArray[2].ToString(),
row.ItemArray[3].ToString(),
DateTime.Parse(row.ItemArray[4].ToString()),
row.ItemArray[5].ToString(),
row.ItemArray[6].ToString(),
row.ItemArray[7].ToString(),
int.Parse(row.ItemArray[8].ToString()));
}
}
OperationResult = server.LastError;
// бронирование
dataSet = server.GetRows("Reservations");
if (dataSet.Tables.Count > 0)
{
foreach (var row in dataSet.Tables[0].Rows.Cast <DataRow>())
{
if (row.ItemArray.Length != 6)
{
continue;
}
hotel.Reservations.Add(Guid.Parse(row.ItemArray[0].ToString()),
Guid.Parse(row.ItemArray[1].ToString()),
Guid.Parse(row.ItemArray[2].ToString()),
DateTime.Parse(row.ItemArray[3].ToString()),
DateTime.Parse(row.ItemArray[4].ToString()),
Guid.Parse(row.ItemArray[5].ToString()));
}
}
OperationResult = server.LastError;
// доставка до гостиницы
dataSet = server.GetRows("Transfers");
if (dataSet.Tables.Count > 0)
{
foreach (var row in dataSet.Tables[0].Rows.Cast <DataRow>())
{
if (row.ItemArray.Length != 5)
{
continue;
}
hotel.Transfers.Add(Guid.Parse(row.ItemArray[0].ToString()),
Guid.Parse(row.ItemArray[1].ToString()),
row.ItemArray[2].ToString(),
DateTime.Parse(row.ItemArray[3].ToString()),
int.Parse(row.ItemArray[4].ToString()));
}
}
OperationResult = server.LastError;
// перечень каналов
dataSet = server.GetRows("PayChannels");
if (dataSet.Tables.Count > 0)
{
foreach (var row in dataSet.Tables[0].Rows.Cast <DataRow>())
{
if (row.ItemArray.Length != 3)
{
continue;
}
hotel.PayChannels.Add(Guid.Parse(row.ItemArray[0].ToString()),
row.ItemArray[1].ToString(),
decimal.Parse(row.ItemArray[2].ToString()));
}
}
OperationResult = server.LastError;
// перечень подписок
dataSet = server.GetRows("AccordancePayChannels");
if (dataSet.Tables.Count > 0)
{
foreach (var row in dataSet.Tables[0].Rows.Cast <DataRow>())
{
if (row.ItemArray.Length != 2)
{
continue;
}
var accordance = hotel.AccordancePayChannels.Add(Guid.Parse(row.ItemArray[0].ToString()),
Guid.Parse(row.ItemArray[1].ToString()));
var ds = server.GetRows("RoomPayChannels");
if (ds.Tables.Count > 0)
{
foreach (var item in ds.Tables[0].Rows.Cast <DataRow>())
{
if (item.ItemArray.Length != 2)
{
continue;
}
var accordanceId = Guid.Parse(item.ItemArray[0].ToString());
var channelId = Guid.Parse(item.ItemArray[1].ToString());
if (accordance.IdAccordancePayChannel == accordanceId)
{
var channel = hotel.PayChannels.FirstOrDefault(c => c.IdPayChannel == channelId);
if (channel != null)
{
accordance.PayChannels.Add(channel);
}
}
}
}
}
}
OperationResult = server.LastError;
return(hotel);
}
/// <summary>
/// Метод для сохранения содержимого модели на сервере
/// </summary>
/// <param name="root">Ссылка на корневой объект модели</param>
/// <param name="connection">Строка подключения</param>
/// <returns></returns>
public static bool StoreTables(Root root, string connection)
{
var server = new Database.SqlServer {
Connection = connection
};
// предметы
server.DeleteInto("Matters", "IdMatter", root.Matters.Select(item => item.IdMatter));
OperationResult = server.LastError;
if (!string.IsNullOrWhiteSpace(OperationResult))
{
return(false);
}
foreach (var item in root.Matters)
{
var columns = new Dictionary <string, string>
{
{ "IdMatter", item.IdMatter.ToString() },
{ "Name", item.Name }
};
if (!server.InsertInto("Matters", columns))
{
server.UpdateInto("Matters", columns);
}
OperationResult = server.LastError;
}
if (!string.IsNullOrWhiteSpace(OperationResult))
{
return(false);
}
// специальности
server.DeleteInto("Specialities", "IdSpeciality", root.Specialities.Select(item => item.IdSpeciality));
OperationResult = server.LastError;
if (!string.IsNullOrWhiteSpace(OperationResult))
{
return(false);
}
foreach (var item in root.Specialities)
{
var columns = new Dictionary <string, string>
{
{ "IdSpeciality", item.IdSpeciality.ToString() },
{ "Name", item.Name }
};
if (!server.InsertInto("Specialities", columns))
{
server.UpdateInto("Specialities", columns);
}
OperationResult = server.LastError;
}
if (!string.IsNullOrWhiteSpace(OperationResult))
{
return(false);
}
// сдаваемые предметы
server.DeleteInto("PassMatters", "IdPassMatter", root.PassMatters.Select(item => item.IdPassMatter));
OperationResult = server.LastError;
if (!string.IsNullOrWhiteSpace(OperationResult))
{
return(false);
}
foreach (var item in root.PassMatters)
{
var columns = new Dictionary <string, string>
{
{ "IdPassMatter", item.IdPassMatter.ToString() },
{ "IdSpeciality", item.IdSpeciality.ToString() },
{ "IdMatter", item.IdMatter.ToString() },
{ "PassForm", item.PassForm.ToString() }
};
if (!server.InsertInto("PassMatters", columns))
{
server.UpdateInto("PassMatters", columns);
}
OperationResult = server.LastError;
}
if (!string.IsNullOrWhiteSpace(OperationResult))
{
return(false);
}
// абитуриенты
server.DeleteInto("Enrollees", "IdEnrollee", root.Enrollees.Select(item => item.IdEnrollee));
OperationResult = server.LastError;
if (!string.IsNullOrWhiteSpace(OperationResult))
{
return(false);
}
foreach (var item in root.Enrollees)
{
var columns = new Dictionary <string, string>
{
{ "IdEnrollee", item.IdEnrollee.ToString() },
{ "RegistrationNumber", item.RegistrationNumber },
{ "Surname", item.Surname },
{ "FirstName", item.FirstName },
{ "LastName", item.LastName },
{ "BirthDay", item.BirthDay.ToString("O") },
{ "SecodarySchoolName", item.SecodarySchoolName },
{ "SecodarySchoolNumber", item.SecodarySchoolNumber },
{ "SecodarySchoolTown", item.SecodarySchoolTown },
{ "GraduationDate", item.GraduationDate.ToString("O") },
{ "GoldMedal", item.GoldMedal.ToString() },
{ "Town", item.Town },
{ "Street", item.Street },
{ "HouseNumber", item.HouseNumber },
{ "PhoneNumber", item.PhoneNumber },
{ "IdSpeciality", item.IdSpeciality.ToString() }
};
if (!server.InsertInto("Enrollees", columns))
{
server.UpdateInto("Enrollees", columns);
}
OperationResult = server.LastError;
}
if (!string.IsNullOrWhiteSpace(OperationResult))
{
return(false);
}
return(true);
}
/// <summary>
/// Метод сохранения конфигурации в базу данных
/// </summary>
/// <param name="connection"></param>
/// <param name="hotel"></param>
public static void SaveToBase(string connection, Hotel hotel)
{
var server = new Database.SqlServer {
Connection = connection
};
// категории
server.DeleteInto("Categories", "IdCategory", hotel.Categories.Select(item => item.Value.IdCategory));
OperationResult = server.LastError;
foreach (var item in hotel.Categories.Values)
{
var columns = new Dictionary <string, string>
{
{ "IdCategory", item.IdCategory.ToString() },
{ "NameCategory", item.NameCategory }
};
if (!server.InsertInto("Categories", columns))
{
server.UpdateInto("Categories", columns);
}
OperationResult = server.LastError;
}
// услуги
server.DeleteInto("Services", "IdService", hotel.Services.Select(item => item.IdService));
OperationResult = server.LastError;
foreach (var item in hotel.Services)
{
var columns = new Dictionary <string, string>
{
{ "IdService", item.IdService.ToString() },
{ "NameService", item.NameService },
{ "PriceDay", item.PriceDay.ToString() }
};
if (!server.InsertInto("Services", columns))
{
server.UpdateInto("Services", columns);
}
OperationResult = server.LastError;
}
// комнаты
server.DeleteInto("Rooms", "IdRoom", hotel.Rooms.Select(item => item.IdRoom));
foreach (var room in hotel.Rooms)
{
var columns = new Dictionary <string, string>
{
{ "IdRoom", room.IdRoom.ToString() },
{ "IdCategory", room.IdCategory.ToString() },
{ "NumberSeat", room.NumberSeat.ToString() },
{ "Floor", room.Floor.ToString() },
{ "PriceDay", room.PriceDay.ToString() },
{ "RoomNumber", room.RoomNumber }
};
server.DeleteInto("RoomServices", columns);
OperationResult = server.LastError;
if (!server.InsertInto("Rooms", columns))
{
server.UpdateInto("Rooms", columns);
}
foreach (var service in room.Services)
{
var cols = new Dictionary <string, string>
{
{ "IdRoom", room.IdRoom.ToString() },
{ "IdService", service.IdService.ToString() }
};
if (!server.InsertInto("RoomServices", cols))
{
server.UpdateInto("RoomServices", cols);
}
OperationResult = server.LastError;
}
}
// должности
server.DeleteInto("EmployeeRoles", "IdEmployeeRole", hotel.EmployeeRoles.Select(item => item.IdEmployeeRole));
foreach (var item in hotel.EmployeeRoles)
{
var columns = new Dictionary <string, string>
{
{ "IdEmployeeRole", item.IdEmployeeRole.ToString() },
{ "NameRole", item.NameRole },
{ "Salary", item.Salary.ToString() },
{ "AllowedOperations", ((uint)item.AllowedOperations).ToString() }
};
if (!server.InsertInto("EmployeeRoles", columns))
{
server.UpdateInto("EmployeeRoles", columns);
}
}
// штат сотрудников
server.DeleteInto("RegistryStaff", "IdEmployee", hotel.RegistryStaff.Select(item => item.IdEmployee));
OperationResult = server.LastError;
foreach (var item in hotel.RegistryStaff)
{
var columns = new Dictionary <string, string>
{
{ "IdEmployee", item.IdEmployee.ToString() },
{ "Surname", item.Surname },
{ "Name", item.Name },
{ "LastName", item.LastName },
{ "PhoneNumber", item.PhoneNumber },
{ "IdEmployeeRole", item.IdEmployeeRole.ToString() },
{ "Password", item.Password }
};
if (!server.InsertInto("RegistryStaff", columns))
{
server.UpdateInto("RegistryStaff", columns);
}
OperationResult = server.LastError;
}
// клиенты
server.DeleteInto("Clients", "IdClient", hotel.Clients.Select(item => item.IdClient));
OperationResult = server.LastError;
foreach (var item in hotel.Clients)
{
var columns = new Dictionary <string, string>
{
{ "IdClient", item.IdClient.ToString() },
{ "Surname", item.Surname },
{ "Name", item.Name },
{ "LastName", item.LastName },
{ "Birthday", item.Birthday.ToString("O") },
{ "City", item.City },
{ "PhoneNumber", item.PhoneNumber },
{ "Passport", item.Passport },
{ "NumberChild", item.NumberChild.ToString() }
};
if (!server.InsertInto("Clients", columns))
{
server.UpdateInto("Clients", columns);
}
OperationResult = server.LastError;
}
// бронирование
server.DeleteInto("Reservations", "IdReservation", hotel.Reservations.Select(item => item.IdReservation));
OperationResult = server.LastError;
foreach (var item in hotel.Reservations)
{
var columns = new Dictionary <string, string>
{
{ "IdReservation", item.IdReservation.ToString() },
{ "IdClient", item.IdClient.ToString() },
{ "IdRoom", item.IdRoom.ToString() },
{ "ArrivalDate", item.ArrivalDate.ToString("O") },
{ "DepartureDate", item.DepartureDate.ToString("O") },
{ "IdEmployee", item.IdEmployee.ToString() }
};
if (!server.InsertInto("Reservations", columns))
{
server.UpdateInto("Reservations", columns);
}
OperationResult = server.LastError;
}
// доставка до гостиницы
server.DeleteInto("Transfers", "IdTransfer", hotel.Transfers.Select(item => item.IdTransfer));
OperationResult = server.LastError;
foreach (var item in hotel.Transfers)
{
var columns = new Dictionary <string, string>
{
{ "IdTransfer", item.IdTransfer.ToString() },
{ "IdReservation", item.IdReservation.ToString() },
{ "FeedAddress", item.FeedAddress },
{ "FeedDateTime", item.FeedDateTime.ToString("O") },
{ "NumberSeat", item.NumberSeat.ToString() }
};
if (!server.InsertInto("Transfers", columns))
{
server.UpdateInto("Transfers", columns);
}
OperationResult = server.LastError;
}
// перечень каналов
server.DeleteInto("PayChannels", "IdPayChannel", hotel.PayChannels.Select(item => item.IdPayChannel));
OperationResult = server.LastError;
foreach (var item in hotel.PayChannels)
{
var columns = new Dictionary <string, string>
{
{ "IdPayChannel", item.IdPayChannel.ToString() },
{ "NameChannel", item.NameChannel },
{ "PriceHour", item.PriceHour.ToString() }
};
if (!server.InsertInto("PayChannels", columns))
{
server.UpdateInto("PayChannels", columns);
}
OperationResult = server.LastError;
}
// перечень подписок
server.DeleteInto("AccordancePayChannels", "IdAccordancePayChannel",
hotel.AccordancePayChannels.Select(item => item.IdAccordancePayChannel));
OperationResult = server.LastError;
foreach (var accordance in hotel.AccordancePayChannels)
{
var columns = new Dictionary <string, string>
{
{ "IdAccordancePayChannel", accordance.IdAccordancePayChannel.ToString() },
{ "IdReservation", accordance.IdReservation.ToString() }
};
server.DeleteInto("RoomPayChannels", columns);
OperationResult = server.LastError;
if (!server.InsertInto("AccordancePayChannels", columns))
{
server.UpdateInto("AccordancePayChannels", columns);
}
OperationResult = server.LastError;
foreach (var channel in accordance.PayChannels)
{
var cols = new Dictionary <string, string>
{
{ "IdAccordancePayChannel", accordance.IdAccordancePayChannel.ToString() },
{ "IdPayChannel", channel.IdPayChannel.ToString() }
};
if (!server.InsertInto("RoomPayChannels", cols))
{
server.UpdateInto("RoomPayChannels", cols);
}
OperationResult = server.LastError;
}
}
}
