/// <summary>
///
/// </summary>
/// <param name="__tablename"></param>
public SQLParameterFactory(string __tablename, SQLAdapter __adapter)
{
this.sqltype_book_ = new Dictionary<int, string>();
this.tablename_ = __tablename;
this.adapter_ = __adapter;
fetchTypeInfos();
}
public static List <UserCourseRoleMappingModel> SaveUserCourseRoleMapping(UserCourseRoleMappingModel ucrm)
{
try
{
var ucrms = SQLAdapter.SaveUserCourseRoleMapping(ModelMapper.MapFromUserModelToUserCourseMapping(ucrm), ModelMapper.GetIdsFromTutorialGroupModelList(ucrm.TutorialGroups));
var mappedUcrms = ModelMapper.MapToUsersModel(ucrms);
return(mappedUcrms);
}
catch (Exception e)
{
return(null);
}
}
public static int CreateOrUpdateQuestion(QuestionModel questionModel)
{
try
{
var question = ModelMapper.MapFromQuestionModel(questionModel);
return(SQLAdapter.AddOrUpdateQuestion(question));
}
catch (Exception e)
{
return(-1);
}
}
public static List <UserCourseRoleMappingModel> GetUserCourseRoleMappingsByCourse(int courseId)
{
try
{
var ucrms = SQLAdapter.GetUserCourseRoleMappingsByCourse(courseId);
var mappedUcrms = ModelMapper.MapToUsersModel(ucrms);
return(mappedUcrms);
}
catch (Exception e)
{
return(null);
}
}
public static UserCourseRoleMappingModel GetBotUser(int courseID)
{
try
{
var user = SQLAdapter.GetBotUser(courseID);
return(ModelMapper.MapToUserModel(user));
}
catch (Exception e)
{
return(null);
}
}
public static bool UpdateUser(UserCourseRoleMappingModel userModel)
{
try
{
var user = ModelMapper.MapFromUserModel(userModel);
return(SQLAdapter.UpdateUser(user));
}
catch (Exception e)
{
return(false);
}
}
public static UserCourseRoleMappingModel GetUser(string userPrincipleName)
{
try
{
var user = SQLAdapter.GetUserMappingsByUPN(userPrincipleName);
var ucrm = ModelMapper.MapToUserModel(user);
return(ucrm);
}
catch (Exception e)
{
return(null);
}
}
public static List <UserCourseRoleMappingModel> GetUsersByRole(string role, int courseID)
{
try
{
var users = SQLAdapter.GetUsersByRole(role, courseID);
return(ModelMapper.MapToUsersModel(users));
}
catch (Exception e)
{
return(null);
}
}
public static List <TutorialGroupModel> DeleteTutorialGroup(TutorialGroup tutorial)
{
try
{
var tutorialGroups = SQLAdapter.DeleteTutorialGroup(tutorial);
var tutorialGroupModels = ModelMapper.MapToTutorialGroupsModel(tutorialGroups);
return(tutorialGroupModels);
}
catch (Exception e)
{
return(null);
}
}
/// <summary>
/// Returns a list of O365 Group IDs where a question has been asked, which also matches the list of Group Ids passed in
/// </summary>
/// <param name="tenantId">Tenant ID</param>
/// <param name="memberOf">List of all Group Ids to filter by</param>
/// <returns>List of all O365 Group IDs</returns>
public static List <string> GetTeamGroupIdsWithQuestions(string tenantId, List <string> memberOf)
{
try
{
var groupIds = SQLAdapter.GetTeamGroupIdsWithQuestions(tenantId, memberOf);
return(groupIds);
}
catch (Exception e)
{
return(null);
}
}
public static bool DoesConversationIdExist(string conversationId)
{
try
{
var question = SQLAdapter.GetQuestionByConversationId(conversationId);
return(question != null);
}
catch (Exception e)
{
return(false);
}
}
public static void AddGlobalAdminUser(string upn, string firstName, string lastName, string email)
{
var user = new User
{
UserPrincipalName = upn,
FirstName = firstName,
LastName = lastName,
Email = email,
IsGlobalAdmin = true,
};
SQLAdapter.UpdateUser(user);
}
public static Stock GetStock(string argStockCode, string argUserId)
{
string lcQuery = "EXEC spGetStock @argCode = '" + argStockCode + "'" +
", @argUserId = '" + argUserId + "'";
DataTable dtUserStocks = SQLAdapter.Create(lcQuery);
Stock stock = null;
if (dtUserStocks != null && dtUserStocks.Rows.Count > 0)
{
stock = new Stock(dtUserStocks.Rows[0]);
}
return(stock);
}
public static List <UserCourseRoleMappingModel> DeleteUserCourseRoleMapping(UserCourseRoleMapping ucrm)
{
try
{
var ucrms = SQLAdapter.DeleteUserCourseRoleMapping(ucrm);
var mappedUcrms = ModelMapper.MapToUsersModel(ucrms);
return(mappedUcrms);
}
catch (Exception e)
{
return(null);
}
}
public static List <TutorialGroupModel> GetTutorialsByCourse(int courseId)
{
try
{
var tutorialGroups = SQLAdapter.GetTutorialsByCourse(courseId);
var tutorialGroupModels = ModelMapper.MapToTutorialGroupsModel(tutorialGroups);
return(tutorialGroupModels);
}
catch (Exception e)
{
return(null);
}
}
public static UserCourseRoleMappingModel MapToUserModel(UserCourseRoleMapping ucrm)
{
if (ucrm == null)
{
return(null);
}
UserCourseRoleMappingModel user = MapToUserModelOnly(ucrm.User);
RoleModel role = MapToRoleModel(ucrm.Role);
var userCourseRoleModel = new UserCourseRoleMappingModel()
{
ID = ucrm.Id,
UserId = ucrm.UserId.ToString(),
CourseId = ucrm.CourseId,
FirstName = user.FirstName,
LastName = user.LastName,
UserName = user.UserName,
Email = user.Email,
PersonalConversationContactData = ucrm.User.PersonalConversationContactData != null?JsonConvert.DeserializeObject <PersonalConversationContactData>(ucrm.User.PersonalConversationContactData) : null,
Role = role,
TutorialGroups = MapToTutorialGroupsModel(ucrm.User.TutorialGroupMemberships.ToList()),
};
var relatedMappingModels = SQLAdapter.GetUsersByUPN(ucrm.User.UserPrincipalName);
bool isAdmin = false;
foreach (UserCourseRoleMapping userCourseRoleMapping in relatedMappingModels)
{
if (userCourseRoleMapping.Role.Name != "Student")
{
isAdmin = true;
}
}
userCourseRoleModel.IsAdmin = isAdmin;
if (userCourseRoleModel.Role != null)
{
userCourseRoleModel.RoleName = userCourseRoleModel.Role.Name;
}
if (userCourseRoleModel.TutorialGroups != null && userCourseRoleModel.TutorialGroups.Count > 0)
{
var sb = new StringBuilder("");
userCourseRoleModel.TutorialGroups.ForEach(tg =>
{
sb.Append(tg.Code);
sb.Append(", ");
});
sb.Length -= 2;
userCourseRoleModel.TutorialGroupsString = sb.ToString();
}
return(userCourseRoleModel);
}
public static List <QuestionModel> GetAllQuestionsByTutorial(string tenantId, string code)
{
try
{
var questions = SQLAdapter.GetQuestionsByTutorialGroup(tenantId, code);
var questionModels = ModelMapper.MapToQuestionModels(questions);
return(questionModels);
}
catch (Exception e)
{
return(null);
}
}
public static List <QuestionModel> GetAllQuestions(string tenantId = null)
{
try
{
var questions = SQLAdapter.GetAllQuestions(tenantId);
var questionModels = ModelMapper.MapToQuestionModels(questions);
return(questionModels);
}
catch (Exception e)
{
return(null);
}
}
public static List <UserCourseRoleMappingModel> GetDemonstrators(int courseId)
{
try
{
var demonstrators = SQLAdapter.GetDemonstrators(courseId);
var result = ModelMapper.MapToUsersModel(demonstrators);
return(result);
}
catch (Exception e)
{
return(null);
}
}
public static List <QuestionModel> GetQuestionsByGroup(string groupId)
{
try
{
var questions = SQLAdapter.GetQuestionsByGroup(groupId);
var questionModels = ModelMapper.MapToQuestionModels(questions);
return(questionModels);
}
catch (Exception e)
{
return(null);
}
}
// конструктор. idClient - не обязательный параметр
// по умолчанию ИД клиента поставим = -1
// (т.к. для нового клиента ID у нас нет)
public ClientEdit(bool isNewClient, int idClient = -1)
{
InitializeComponent();
// создаем экземпляр класса, у которого есть методы для работой с БД
sqlAdapter = new SQLAdapter();
// полю этого класса isNewClient присваиваем то, что пришло в конструктор
this.isNewClient = isNewClient;
// полю этого класса idClient присваиваем то, что пришло в конструктор
this.idClient = idClient;
// вызываем функции для ндобавление названия формы
PaintName();
}
public static List <Stock> GetStocksByUserId(string argUserId)
{
string lcQuery = "EXEC spGetStocksByUserId @argUserId = '" + argUserId + "'";
DataTable dtUserStocks = SQLAdapter.Create(lcQuery);
List <Stock> stocks = null;
if (dtUserStocks != null && dtUserStocks.Rows.Count > 0)
{
stocks = new List <Stock>();
foreach (DataRow row in dtUserStocks.Rows)
{
stocks.Add(new Stock(row));
}
}
return(stocks);
}
/// <summary>
/// This event function runs when the add user button is pressed on the UI screen. It collects the data the user has inputted into the fields
/// and then creates an SQL statement from that data. It then checks if the user already exitst and then runs the SQL function to
/// add it to the database.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void addMemberSubmitButton_Click(object sender, EventArgs e)
{
//Get all employee details
string firstName = firstNameTextBox.Text;
string lastName = lastNameTextBox.Text;
string DOB = dateOfBirthTextBox.Text;
string salary = salaryTextBox.Text;
string gender = genderDDL.Text;
//Create the appropriate SQL statements
string sqlInsert = createInsertString(firstName, lastName, DOB, salary, gender);
string selectString = createSelectString(firstName, lastName);
bool doesEmployeeExists = SQLAdapter.checkIfRecordExists(selectString);
//If employee already exists in our database let user know
if (doesEmployeeExists)
{
((Label)Master.FindControl("validationLablel")).Text = "This user already exists in our Database";
}
else
{
try
{
//Try insert data into the database and provide a message to let the user know
SQLAdapter.ChangeDBData(sqlInsert);
((Label)Master.FindControl("validationLablel")).Text = "Member added successfully";
UpdateMembershipClass.updateMembershipClass();
firstNameTextBox.Text = "";
lastNameTextBox.Text = "";
dateOfBirthTextBox.Text = "";
salaryTextBox.Text = "";
genderDDL.Text = "";
}
catch (SqlException ex)
{
((Label)Master.FindControl("validationLablel")).Text = ex.Errors.ToString();
}
catch (Exception)
{
((Label)Master.FindControl("validationLablel")).Text = "An Unexpected error has occured adding a member";
}
finally
{
}
}
}
public SleepPlace()
{
InitializeComponent();
Skin skin_ = TabSkins.GetSkin(DevExpress.LookAndFeel.UserLookAndFeel.Default.ActiveLookAndFeel);
SkinElement element = skin_[TabSkins.SkinTabHeader];
element.Properties["AllowTouch"] = false;
element.ContentMargins.Top = 10;
element.ContentMargins.Right = 20;
element.ContentMargins.Left = 20;
element.ContentMargins.Bottom = 10;
Свободные.LayoutChanged();
adapter = new SQLAdapter();
LoadFreeRoom();
LoadBusyRoom();
}
public static List <UserCourseRoleMappingModel> GetAllAdmins(int courseID)
{
try
{
var admins = new List <UserCourseRoleMappingModel>();
var demonstrators = SQLAdapter.GetUsersByRole("Demonstrator", courseID);
var lecturers = SQLAdapter.GetUsersByRole("Lecturer", courseID);
admins.AddRange(ModelMapper.MapToUsersModel(demonstrators));
admins.AddRange(ModelMapper.MapToUsersModel(lecturers));
return(admins);
}
catch (Exception e)
{
return(null);
}
}
public static UserAccessModel GetUserAccess(string upn)
{
var result = new UserAccessModel();
var user = SQLAdapter.GetUserByUPN(upn);
if (user == null)
{
throw new Exception("User " + upn + " does not exist");
}
result.IsGlobalAdmin = user.IsGlobalAdmin == true;
var userRoleMappings = SQLAdapter.GetUsersByUPN(upn);
result.IsLecturer = userRoleMappings.Any(m => m.Role.Name == Constants.LECTURER_ROLE);
result.IsDemonstrator = userRoleMappings.Any(m => m.Role.Name == Constants.DEMONSTRATOR_ROLE);
return(result);
}
private void LoadReserverPlaces()
{
SQLAdapter sqlAdapter = new SQLAdapter();
// Создали пустую таблицу с названием allClientsDT
DataTable currentReservedPlases = new DataTable();
// В таблицу allClientsDT помещаем результат метода SelectData
// В метот SelectData передаем строковую переменную с класса Query - SelectAllClients
// Метод SelectData возвращает запрошеннуб таблицу
currentReservedPlases = sqlAdapter.SelectData(Query.AllReservedPlaces);
// очищаем журнал клиентов
gridСouchette.DataSource = null;
// передаем таблице на форме таблицу allClientsDT
gridСouchette.DataSource = currentReservedPlases;
// отключаем видимость столбца id
gridViewСouchette.Columns["idReservation"].Visible = false;
}
public void LoadJournalClient()
{
SQLAdapter sqlAdapter = new SQLAdapter();
// Создали пустую таблицу с названием allClientsDT
DataTable allClientsDT = new DataTable();
// В таблицу allClientsDT помещаем результат метода SelectData
// В метот SelectData передаем строковую переменную с класса Query - SelectAllClients
// Метод SelectData возвращает запрошеннуб таблицу
allClientsDT = sqlAdapter.SelectData(Query.SelectAllClients);
// очищаем журнал клиентов
gridControlClients.DataSource = null;
// передаем таблице на форме таблицу allClientsDT
gridControlClients.DataSource = allClientsDT;
// отключаем видимость столбца id
gridViewClients.Columns["id"].Visible = false;
}
/// <summary>
/// Will migrate 500 employees to our database.
/// </summary>
public static void readEmployee()
{
//Read all the lines from the salaries file
string[] allLinesSalaries = File.ReadAllLines(HttpContext.Current.Server.MapPath("~/App_Data/salaries.csv"));
//Split the data and save it to a variable
var salaries = from line in allLinesSalaries
let data = line.Split('|')
select data;
//Read all the values from the employees file
string[] allLinesEmployee = File.ReadAllLines(HttpContext.Current.Server.MapPath("~/App_Data/employees.csv"));
//Split that data and save it in a variable
var employees = from line in allLinesEmployee
let data = line.Split('|')
select data;
//For the first 500 members in the employees file get the trimmed data and insert it into the database
for (int i = 1; i <= 500; i++)
{
char[] charToTrim = { '\\', '\"' };
string[] emp = employees.ElementAt(i);
string id = emp[ID];
string dob = emp[DOB].Trim(charToTrim);
string firstName = emp[Fname].Trim(charToTrim);
string lastName = emp[Lname].Trim(charToTrim);
string gender = emp[Gender].Trim(charToTrim);
if (gender == "M")
{
gender = "Male";
}
if (gender == "F")
{
gender = "Female";
}
string dateJoined = emp[DateJoined].Trim(charToTrim);
string salary = getSalaryForID(id, allLinesSalaries);
string sqlInsert = createInsertString(firstName, lastName, dob, salary, gender, dateJoined);
SQLAdapter.ChangeDBData(sqlInsert);
}
}
/// <summary>
/// Run the migration of 500 emplopyees
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
//protected void migrate(object sender, EventArgs e)
//{
// //First Add the first 500 employee
// csv.readEmployee();
//}
/// <summary>
/// This will update the membership details for every employee. Only used when initially migrating the 500 employees
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
//protected void test(object sender, EventArgs e)
//{
// UpdateMembershipClass.updateMembershipClass();
//}
/// <summary>
/// Used to migrate members to our databse from engineering db using ID
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void migrate_employee(object sender, EventArgs e)
{
string id = memberShipIDTextBox.Text;
try
{
string[] employeeDeatils = csv.getEmployeebyID(id);
string selectString = createSelectString(employeeDeatils[2], employeeDeatils[3]);
bool doesEmployeeExists = SQLAdapter.checkIfRecordExists(selectString);
//If employee already exists in our database let user know
if (doesEmployeeExists)
{
((Label)Master.FindControl("validationLablel")).Text = "This user already exists in our Database";
}
else
{
//Get salary of employee by ID
string salary = csv.FindSalaryByID(employeeDeatils[0]);
//Create string to insert
string sqlInsert = createInsertString(employeeDeatils[2], employeeDeatils[3], employeeDeatils[1], salary, employeeDeatils[4]);
try
{
SQLAdapter.ChangeDBData(sqlInsert);
((Label)Master.FindControl("validationLablel")).Text = "User successfully added";
}
catch (Exception exception)
{
((Label)Master.FindControl("validationLablel")).Text = exception.Message.ToString();
}
}
}
catch (ArgumentOutOfRangeException exception)
{
((Label)Master.FindControl("validationLablel")).Text = exception.Message.ToString();
}
}
/// <summary>
///
/// </summary>
/// <param name="__factory"></param>
/// <param name="__dataset"></param>
/// <param name="__adapter"></param>
public SQLStorage(SQLAdapter __factory, DataSet __dataset, SqlDataAdapter __adapter)
{
this.factory_ = __factory;
this.dataset_ = __dataset;
this.adapter_ = __adapter;
}
public static bool NoUsers()
{
return(SQLAdapter.NoUsers());
}
