public async Task <JsonResult> CreateHotel(int hotelId, string databaseName)
{
// 创建空数据库
OriginSql originSql = new OriginSql(new YummyOnlineContext().Database.Connection.ConnectionString);
var result = await originSql.CreateDatabase(databaseName);
if (!result.Succeeded)
{
return(Json(new JsonError(result.Message)));
}
// 总数据库记录连接字符串等信息
await YummyOnlineManager.CreateHotel(hotelId, databaseName);
// 新数据库写入所有表格等架构
Hotel newHotel = await YummyOnlineManager.GetHotelById(hotelId);
originSql = new OriginSql(newHotel.AdminConnectionString);
result = await originSql.InitializeHotel();
if (!result.Succeeded)
{
return(Json(new JsonError(result.Message)));
}
// 新数据库初始化
string staffId = await YummyOnlineManager.GetHotelAdminId(hotelId);
HotelManager hotelManager = new HotelManager(newHotel.AdminConnectionString);
await hotelManager.InitializeHotel(hotelId, staffId);
return(Json(new JsonSuccess()));
}
public async Task <JsonResult> ExecuteSql(List <int> hotelIds, string sql)
{
StringBuilder sb = new StringBuilder();
foreach (int id in hotelIds)
{
Hotel hotel = await YummyOnlineManager.GetHotelById(id);
OriginSql originSql = new OriginSql(hotel.AdminConnectionString);
FunctionResult result = await originSql.ExecuteSql(sql);
if (!result.Succeeded)
{
sb.Append($"{hotel.Name}({hotel.Id}) Error, {result.Message}</br>");
await YummyOnlineManager.RecordLog(Log.LogProgram.System, Log.LogLevel.Error, $"Execute SQL {hotel.Name}({hotel.Id}) Failed",
$"Error: {result.Message}, SQL: {sql}");
}
else
{
await YummyOnlineManager.RecordLog(Log.LogProgram.System, Log.LogLevel.Success, $"Execute SQL {hotel.Name}({hotel.Id}) Successfully", sql);
}
}
if (sb.Length != 0)
{
return(Json(new JsonError(sb.ToString())));
}
return(Json(new JsonSuccess()));
}
public async Task <JsonResult> GetDbPartitionDetailByHotelId(int?hotelId)
{
Hotel hotel;
OriginSql dbPartition;
if (hotelId.HasValue)
{
hotel = await YummyOnlineManager.GetHotelById(hotelId.Value);
dbPartition = new OriginSql(hotel.AdminConnectionString);
}
else
{
hotel = null;
dbPartition = new OriginSql(YummyOnlineManager.ConnectionString);
}
return(Json(new {
Hotel = hotel == null ? null : new {
hotel.Id,
hotel.Name
},
DbPartitionInfos = await dbPartition.GetDbPartitionInfos(),
FileGroupInfos = await dbPartition.GetFileGroupInfos()
}));
}
public async Task <JsonResult> Backup(bool isYummyOnline, List <int> hotelIds)
{
SystemConfig config = await YummyOnlineManager.GetSystemConfig();
string path = $"{config.SpecificationDir}\\Database";
StringBuilder sb = new StringBuilder();
if (isYummyOnline)
{
OriginSql originSql = new OriginSql(YummyOnlineManager.ConnectionString);
FunctionResult result = await originSql.Backup(path);
if (!result.Succeeded)
{
sb.Append($"YummyOnlineDB Error, {result.Message}</br>");
await YummyOnlineManager.RecordLog(Log.LogProgram.System, Log.LogLevel.Error, "Backup YummyOnlineDB Failed", result.Message);
}
else
{
await YummyOnlineManager.RecordLog(Log.LogProgram.System, Log.LogLevel.Success, "Backup YummyOnlineDB Successfully");
}
}
foreach (int id in hotelIds)
{
Hotel hotel = await YummyOnlineManager.GetHotelById(id);
OriginSql originSql = new OriginSql(hotel.AdminConnectionString);
FunctionResult result = await originSql.Backup(path);
if (!result.Succeeded)
{
sb.Append($"{hotel.Name}({hotel.Id}) Error, {result.Message}</br>");
await YummyOnlineManager.RecordLog(Log.LogProgram.System, Log.LogLevel.Error, $"Backup {hotel.Name}({hotel.Id}) Failed", result.Message);
}
else
{
await YummyOnlineManager.RecordLog(Log.LogProgram.System, Log.LogLevel.Success, $"Backup {hotel.Name}({hotel.Id}) Successfully");
}
}
if (sb.Length != 0)
{
return(Json(new JsonError(sb.ToString())));
}
return(Json(new JsonSuccess()));
}
private string ManageSql()
{
CompareInfo compare = CultureInfo.InvariantCulture.CompareInfo;
int selectIndex = compare.IndexOf(OriginSql, "select ", CompareOptions.IgnoreCase);
//判断是否有select,有：+7
if (selectIndex > -1)
{
selectIndex += 7;
}
else
{
return("");
}
//select top x orderFiled from table
string startSql = OriginSql.Insert(selectIndex, string.Format(@" row_Number() over (order by {0} {1}) rowNum, ", OrderField, OrderDirection));
startSql = string.Format("select * from ({0})result where rowNum >{1} and rowNum <={2}", startSql,
(CurrentPage - 1) * PageSize, CurrentPage * PageSize);
return(startSql);
}