public IActionResult AddColumn()
{
try
{
var connectonString = _configuration
.GetConnectionString("LaboratoryBookConnectionString");
var laboratoryBookName = HttpContext
.User
.Claims
.First(claim => claim.Type == "LaboratoryBook")
.Value;
var commandString = $"SHOW columns FROM laboratory_book_{laboratoryBookName}; ";
var columnsTable = LaboratoryBookHelper.GetDbDataTable(
connectonString,
commandString);
var columnList = new List <string>();
foreach (DataRow row in columnsTable.Rows)
{
columnList.Add((string)row[0]);
}
return(PartialView("_AddColumnPartial", columnList));
}
catch (Exception exception)
{
return(Json($"Exception:{exception.Message}"));
}
}
public IActionResult SelectColumns()
{
try
{
var connectonString = _configuration
.GetConnectionString("LaboratoryBookConnectionString");
var laboratoryBook = HttpContext.User.Claims.First(claim => claim.Type == "LaboratoryBook").Value;
var commandString = $"SHOW COLUMNS FROM laboratory_book_{laboratoryBook}; ";
var columnsDataTableResult = LaboratoryBookHelper.GetDbDataTable(
connectonString,
commandString);
var columnList = new List <string>();
foreach (DataRow row in columnsDataTableResult.Rows)
{
columnList.Add((string)row[0]);
}
return(PartialView("_SelectColumnsPartial", columnList));
}
catch (Exception exception)
{
return(StatusCode(
StatusCodes.Status500InternalServerError,
new { message = exception.Message }));
}
}
public Tuple <bool, object> GetColumns()
{
try
{
var connectonString = _configuration
.GetConnectionString("LaboratoryBookConnectionString");
var laboratoryBookName = HttpContext
.User
.Claims
.First(claim => claim.Type == "LaboratoryBook")
.Value;
//default columns which are forbidden to delete or modify
var ForbidenToModifyColumnNames = new string[]
{
"sampleID",
"date",
"material",
"substrate",
"thickness",
"depositionTime",
"depositionPressure",
"regime",
"description",
"operator",
"permissionID",
};
var commandString = $"SHOW columns FROM laboratory_book_{laboratoryBookName}; ";
var columnsInfoTable = LaboratoryBookHelper.GetDbDataTable(connectonString, commandString);
var columnInfoList = new List <object>();
foreach (DataRow row in columnsInfoTable.Rows)
{
if (ForbidenToModifyColumnNames.Contains(row[0].ToString()))
{
continue;
}
columnInfoList.Add(new { columnName = row[0], columnType = row[1] });
}
return(new Tuple <bool, object>(true, columnInfoList));
}
catch (Exception exception)
{
return(new Tuple <bool, object>(false, exception.Message));
}
}
public Tuple <bool, object> GetPermissions()
{
try
{
var connectonString = _configuration
.GetConnectionString("LaboratoryBookConnectionString");
var commandString = "SELECT permission_id FROM permission; ";
var permissionIdDataTable = LaboratoryBookHelper.GetDbDataTable(
connectonString,
commandString);
var userPermission = HttpContext
.User
.Claims
.First(claim => claim.Type == "Permission")
.Value;
var userPermissionInt = int.Parse(userPermission);
var result = new List <object>();
foreach (DataRow row in permissionIdDataTable.Rows)
{
var permission = (int)((sbyte)row[0]);
if (permission < userPermissionInt)
{
result.Add(row[0]);
}
}
return(new Tuple <bool, object>(true, result));
}
catch (Exception exception)
{
return(new Tuple <bool, object>(false, exception));
}
}
public async Task <IActionResult> AddRow()
{
try
{
var connectionString = _configuration.GetConnectionString("LaboratoryBookConnectionString");
var laboratoryBook = HttpContext.User.Claims.First(claim => claim.Type == "LaboratoryBook").Value;
var getRegimesTask = Task.Run <string>(() => LaboratoryBookHelper
.GetColumnList(
connectionString,
laboratoryBook,
ColumnToGet.Regime));
var getOperatorsTask = Task.Run <string>(() => LaboratoryBookHelper
.GetColumnList(
connectionString,
laboratoryBook,
ColumnToGet.Operator));
var getMaterialsTask = Task.Run <string>(() => LaboratoryBookHelper
.GetColumnList(
connectionString,
laboratoryBook,
ColumnToGet.Material));
var getSubstratesTask = Task.Run <string>(() => LaboratoryBookHelper
.GetColumnList(
connectionString,
laboratoryBook,
ColumnToGet.Substrate));
var getPermissionIDsTask = Task.Run <string>(() => LaboratoryBookHelper
.GetColumnList(connectionString,
laboratoryBook,
ColumnToGet.PermissionID));
var getColumnValuesTasks = new List <Task <string> >();
getColumnValuesTasks.AddRange(new[] {
getRegimesTask,
getOperatorsTask,
getMaterialsTask,
getSubstratesTask,
getPermissionIDsTask
});
var addRowModel = new AddRowModel();
while (getColumnValuesTasks.Any())
{
var compleatedTask = await Task.WhenAny(getColumnValuesTasks.ToArray());
if (compleatedTask == getRegimesTask)
{
addRowModel.Regimes = compleatedTask.Result.Split(";");
}
else if (compleatedTask == getOperatorsTask)
{
addRowModel.Operators = compleatedTask.Result.Split(";");
}
else if (compleatedTask == getMaterialsTask)
{
addRowModel.Materials = compleatedTask.Result.Split(";");
}
else if (compleatedTask == getSubstratesTask)
{
addRowModel.Substrates = compleatedTask.Result.Split(";");
}
else
{
var userPermission = HttpContext
.User
.Claims
.First(claim => claim.Type == "Permission")
.Value;
var userPermissionInt = int.Parse(userPermission);
addRowModel.PermissionIDs = compleatedTask
.Result
.Split(";")
.Where(permissionId => {
var permissionIdInt = int.Parse(permissionId);
return(permissionIdInt <= userPermissionInt);
});
}
getColumnValuesTasks.Remove(compleatedTask);
}
var maxRowId = LaboratoryBookHelper.GetDbDataScalar(
connectionString,
$"SELECT MAX(sampleID) FROM laboratory_book_{laboratoryBook}; "
);
if (maxRowId is DBNull)
{
addRowModel.RowId = 1;
}
else
{
addRowModel.RowId = ((int)maxRowId) + 1;
}
var bookInfoDataTable = LaboratoryBookHelper.GetDbDataTable(
connectionString,
$"SHOW COLUMNS FROM laboratory_book_{laboratoryBook}; "
);
var columnNames = new List <string>();
foreach (DataRow dataRow in bookInfoDataTable.Rows)
{
columnNames.Add((string)dataRow["Field"]);
}
addRowModel.Columns = columnNames.ToArray();
return(PartialView("_AddRowPartial", addRowModel));
}
catch (Exception exception)
{
return(new ObjectResult(exception.Message));
}
}
public Tuple <bool, object> GetUsersInfo()
{
try
{
var connectonString = _configuration
.GetConnectionString("LaboratoryBookConnectionString");
var laboratoryBookName = HttpContext
.User
.Claims
.First(claim => claim.Type == "LaboratoryBook")
.Value;
var commandStringBuilder = new StringBuilder();
//get laboratory book id
commandStringBuilder.Append("SELECT db_id ");
commandStringBuilder.Append("FROM db_list ");
commandStringBuilder.Append($"WHERE db_name = '{laboratoryBookName}'");
var commandString = commandStringBuilder.ToString();
var laboratoryBookId = LaboratoryBookHelper.GetDbDataScalar(
connectonString,
commandString);
//get laboratory book users
commandStringBuilder.Clear();
commandStringBuilder.Append("SELECT users.user_id, user_name, permission_id ");
commandStringBuilder.Append("FROM users ");
commandStringBuilder.Append("JOIN db_users ");
commandStringBuilder.Append("ON users.user_id = db_users.user_id ");
commandStringBuilder.Append($"WHERE db_users.db_id = '{laboratoryBookId}';");
commandString = commandStringBuilder.ToString();
var usersInfoDataTable = LaboratoryBookHelper.GetDbDataTable(
connectonString,
commandString);
var userPermission = HttpContext
.User
.Claims
.First(claim => claim.Type == "Permission")
.Value;
var userPermissionInt = int.Parse(userPermission);
var usersInfoList = new List <object>();
foreach (DataRow row in usersInfoDataTable.Rows)
{
var permission = (int)((sbyte)row[2]);
if (userPermissionInt > permission)
{
usersInfoList.Add(new { userId = row[0], name = row[1], permissionId = row[2] });
}
}
return(new Tuple <bool, object>(true, usersInfoList));
}
catch (Exception exception)
{
return(new Tuple <bool, object>(false, exception.Message));
}
}
public IActionResult AddUser()
{
try
{
var connectonString = _configuration
.GetConnectionString("LaboratoryBookConnectionString");
var laboratoryBookName = HttpContext
.User
.Claims
.First(claim => claim.Type == "LaboratoryBook")
.Value;
//get permisssions
var commandString = "SELECT permission_id FROM permission; ";
var permissionIdDataTable = LaboratoryBookHelper.GetDbDataTable(
connectonString,
commandString);
var permissionIdsList = new List <object>();
foreach (DataRow row in permissionIdDataTable.Rows)
{
permissionIdsList.Add(row[0]);
}
//get laboratory book id
var laboratoryBookId = LaboratoryBookHelper.GetLaboratoryBookId(
connectonString,
laboratoryBookName
);
//get users which have a permission to access the database
var commandStringBuilder = new StringBuilder();
commandStringBuilder.Append("SELECT user_name ");
commandStringBuilder.Append("FROM users ");
commandStringBuilder.Append("JOIN db_users ");
commandStringBuilder.Append("ON users.user_id = db_users.user_id ");
commandStringBuilder.Append($"WHERE db_users.db_id = '{laboratoryBookId}';");
commandString = commandStringBuilder.ToString();
var usersWithPermissionDataTabel = LaboratoryBookHelper.GetDbDataTable(
connectonString,
commandString
);
var usersWithPermissionList = new List <string>();
foreach (DataRow row in usersWithPermissionDataTabel.Rows)
{
usersWithPermissionList.Add((string)row[0]);
}
//get available users
commandString = "SELECT user_id, user_name FROM users;";
var userInfoDataTable = LaboratoryBookHelper.GetDbDataTable(
connectonString,
commandString);
var userInfoList = new List <ManageDatabaseUserInfoModel>();
foreach (DataRow row in userInfoDataTable.Rows)
{
if (usersWithPermissionList.Contains((string)row[1]))
{
continue;
}
userInfoList.Add(new ManageDatabaseUserInfoModel()
{
UserID = (int)row[0],
UserName = (string)row[1]
});
}
var addUserModel = new ManageDatabaseAddUserModel()
{
PermissionIds = permissionIdsList,
Users = userInfoList
};
return(PartialView("_AddUserPartial", addUserModel));
}
catch (Exception exception)
{
return(Json(exception.Message));
}
}