public List <Skill> GetSkillsByUserId(int userId)
{
var skills = new List <Skill>();
var prms = new List <SqlParameter>
{
SqlUtility.CreateParameter(SPGetSkillsByUserID.PARAM_USER_ID, SqlDbType.Int, userId)
};
try
{
var dt = SqlUtility.ExecuteAndGetTable(SPGetSkillsByUserID.NAME,
CommandType.StoredProcedure, SPGetSkillsByUserID.TABLE_NAME, prms);
skills.AddRange(from DataRow row in dt.Rows
select new Skill
{
Name = row["Name"].ToString(),
SkillId = Convert.ToInt32(row["SkillId"]),
Rating = (row["Rating"] == DBNull.Value) ? 0 : Convert.ToInt32(row["Rating"])
});
}
catch (Exception ex)
{
LogUtility.ErrorRoutine(ex);
}
return(skills);
}
/// <summary>
/// Gets all Users.
/// </summary>
/// <returns>List of all users.</returns>
public List <User> GetAllUsers()
{
var users = new List <User>();
var prms = new List <SqlParameter>();
try
{
var dt = SqlUtility.ExecuteAndGetTable(SPGetAllUsers.NAME,
CommandType.StoredProcedure, SPGetAllUsers.TABLE_NAME, prms);
users.AddRange(from DataRow row in dt.Rows
select new User
{
UserId = Convert.ToInt32(row["UserId"]),
FirstName = row["FirstName"].ToString(),
LastName = row["LastName"].ToString(),
UserName = row["UserName"].ToString(),
Email = row["Email"].ToString(),
Designation = row["Designation"].ToString(),
ProfilePictureName = row["ProfilePictureName"].ToString(),
IsFemale = Convert.ToBoolean(row["IsFemale"]),
IsAdministrator = Convert.ToBoolean(row["IsAdministrator"]),
IsTrainer = Convert.ToBoolean(row["IsTrainer"]),
IsTrainee = Convert.ToBoolean(row["IsTrainee"]),
IsManager = Convert.ToBoolean(row["IsManager"])
});
}
catch (Exception ex)
{
LogUtility.ErrorRoutine(ex);
}
return(users);
}
/// <summary>
/// Calls stored procedure which validates user.
/// </summary>
/// <param name="userData">User data object.</param>
/// <returns>True if valid customer.</returns>
public bool ValidateUser(User userData)
{
var prms = new List <SqlParameter>
{
SqlUtility.CreateParameter(SPValidateUser.PARAM_USER_NAME,
SqlDbType.VarChar, userData.UserName),
SqlUtility.CreateParameter(SPValidateUser.PARAM_PASSWORD,
SqlDbType.VarChar, userData.Password)
};
try
{
var dt = SqlUtility.ExecuteAndGetTable(SPValidateUser.NAME,
CommandType.StoredProcedure, SPValidateUser.TABLE_NAME, prms);
if (dt.Rows[0][0].ToString().Equals("1"))
{
return(true);
}
}
catch (Exception ex)
{
LogUtility.ErrorRoutine(ex);
}
return(false);
}
public List <Session> GetSessionsByUserId(int userId)
{
var sessions = new List <Session>();
var prms = new List <SqlParameter>
{
SqlUtility.CreateParameter(SPGetSessionsByUserID.PARAM_USER_ID, SqlDbType.Int, userId)
};
try
{
var dt = SqlUtility.ExecuteAndGetTable(SPGetSessionsByUserID.NAME,
CommandType.StoredProcedure, SPGetSessionsByUserID.TABLE_NAME, prms);
sessions.AddRange(from DataRow row in dt.Rows
select new Session
{
Title = row["Title"].ToString(),
// SessionId = Convert.ToInt32(row["SessionId"])
});
}
catch (Exception ex)
{
LogUtility.ErrorRoutine(ex);
}
return(sessions);
}
/// <summary>
/// Gets all Users.
/// </summary>
/// <returns>List of all users.</returns>
public List <UserData> GetDashboardData()
{
var users = new List <UserData>();
//var objUserData = new UserData();
var prms = new List <SqlParameter>();
try
{
var dt = SqlUtility.ExecuteAndGetTable(SPGetDashboardData.NAME,
CommandType.StoredProcedure, SPGetDashboardData.TABLE_NAME, prms);
foreach (DataRow rows in dt.Rows)
{
if (!users.Exists(x => x.User.UserId == Convert.ToInt32(rows["UserId"])))
{
users.Add(new UserData
{
User = new User
{
UserId = Convert.ToInt32(rows["UserId"]),
FullName = rows["FullName"].ToString(),
},
RemainingFeedbacks = new List <Feedback>(),
WeeklyFeedback = new List <Feedback>(),
SkillsFeedback = new List <Feedback>()
});
}
var objUserData = users.First(x => x.User.UserId == Convert.ToInt32(rows["UserId"]));
AddFeedbacksToTheList(ref users, rows);
}
//users.AddRange(from DataRow row in dt.Rows
// select new UserData
// {
// User = new User
// {
// UserId = Convert.ToInt32(row["UserId"]),
// FullName = row["FullName"].ToString(),
// UserName = row["UserName"].ToString(),
// Email = row["Email"].ToString(),
// ProfilePictureName = row["ProfilePictureName"].ToString()
// },
// PoorRating = Convert.ToInt32(row["PoorRating"]),
// AverageRating = Convert.ToInt32(row["AverageRating"]),
// FastRating = Convert.ToInt32(row["FastRating"]),
// ExceptionalRating = Convert.ToInt32(row["ExceptionalRating"]),
// Skills = (row["Skills"] == DBNull.Value) ? string.Empty : row["Skills"].ToString(),
// LastWeeklyFeedback = (row["LastWeeklyFeedback"] == DBNull.Value) ? new DateTime(1900, 1, 1) : Convert.ToDateTime(row["LastWeeklyFeedback"]),
// });
}
catch (Exception ex)
{
LogUtility.ErrorRoutine(ex);
}
return(users);
}
/// <summary>
/// Gets all feedback for a users.
/// </summary>
/// <returns>List of all feedback for a users.</returns>
public List <Feedback> GetUserFeedback(int userId, int count, int?feedbackId = null)
{
var feedbacks = new List <Feedback>();
var prms = new List <SqlParameter>
{
SqlUtility.CreateParameter(SPGetUserFeedbacks.PARAM_USER_ID, SqlDbType.Int, userId),
SqlUtility.CreateParameter(SPGetUserFeedbacks.FEEDBACK_ID, SqlDbType.Int, feedbackId),
SqlUtility.CreateParameter(SPGetUserFeedbacks.PARAM_PAGE_SIZE, SqlDbType.Int, count),
SqlUtility.CreateParameter(SPGetUserFeedbacks.PARAM_OFFSET, SqlDbType.Int, 0)
};
try
{
var dt = SqlUtility.ExecuteAndGetTable(SPGetUserFeedbacks.NAME,
CommandType.StoredProcedure, SPGetUserFeedbacks.TABLE_NAME, prms);
feedbacks.AddRange(from DataRow row in dt.Rows
select new Feedback
{
FeedbackId = Convert.ToInt32(row["FeedbackId"]),
FeedbackText = row["FeedbackText"].ToString(),
Title = row["Title"].ToString(),
FeedbackType = new FeedbackType
{
FeedbackTypeId = Convert.ToInt32(row["FeedbackType"]),
Description = row["FeedbackTypeName"].ToString(),
},
Rating = Convert.ToInt32(row["Rating"]),
AddedOn = Convert.ToDateTime(row["AddedOn"]),
AddedBy = new User
{
UserId = Convert.ToInt32(row["AddedBy"]),
FullName = row["AddedByUser"].ToString(),
ProfilePictureName = row["AddedByUserImage"].ToString(),
},
StartDate = Convert.ToDateTime(row["StartDate"]),
EndDate = Convert.ToDateTime(row["EndDate"])
});
}
catch (Exception ex)
{
LogUtility.ErrorRoutine(ex);
}
return(feedbacks);
}
public User GetUserById(int userId)
{
var user = new User();
var prms = new List <SqlParameter>
{
SqlUtility.CreateParameter(SPGetUser.PARAM_USER_ID, SqlDbType.Int, userId),
SqlUtility.CreateParameter(SPGetUser.PARAM_SCENARIO, SqlDbType.Int, 1)
};
try
{
var dt = SqlUtility.ExecuteAndGetTable(SPGetUser.NAME,
CommandType.StoredProcedure, SPGetUser.TABLE_NAME, prms);
var row = dt.Rows[0];
user = new User
{
UserId = userId,
FirstName = row["FirstName"].ToString(),
LastName = row["LastName"].ToString(),
UserName = row["UserName"].ToString(),
Email = row["Email"].ToString(),
Designation = row["Designation"].ToString(),
ProfilePictureName = row["ProfilePictureName"].ToString(),
IsFemale = Convert.ToBoolean(row["IsFemale"]),
IsAdministrator = Convert.ToBoolean(row["IsAdministrator"]),
IsTrainer = Convert.ToBoolean(row["IsTrainer"]),
IsTrainee = Convert.ToBoolean(row["IsTrainee"]),
IsManager = Convert.ToBoolean(row["IsManager"]),
UserRating = (row["UserRating"] == DBNull.Value) ? 0 : Convert.ToInt32(row["UserRating"])
};
}
catch (Exception ex)
{
LogUtility.ErrorRoutine(ex);
}
return(user);
}
public List <Skill> GetAllSkillsForApp()
{
var skills = new List <Skill>();
try
{
var dt = SqlUtility.ExecuteAndGetTable(SPGetApplicationSkills.NAME,
CommandType.StoredProcedure, SPGetApplicationSkills.TABLE_NAME, null);
skills.AddRange(from DataRow row in dt.Rows
select new Skill
{
Name = row["Name"].ToString(),
SkillId = Convert.ToInt32(row["SkillId"]),
});
}
catch (Exception ex)
{
LogUtility.ErrorRoutine(ex);
}
return(skills);
}
/// <summary>
///
/// </summary>
/// <param name="pageSize"></param>
/// <param name="sessionType"></param>
/// <param name="searchKeyword"></param>
/// <returns></returns>
public List <Session> GetSessionOnFilter(int pageSize, int sessionType, string searchKeyword)
{
List <Session> sessions = new List <Session>();
var prms = new List <SqlParameter>
{
SqlUtility.CreateParameter(SPGetSessionsOnFilter.PARAM_KEYWORD, SqlDbType.NVarChar, searchKeyword),
SqlUtility.CreateParameter(SPGetSessionsOnFilter.PARAM_PAGESIZE, SqlDbType.Int, pageSize),
SqlUtility.CreateParameter(SPGetSessionsOnFilter.PARAM_SESSIONTYPE, SqlDbType.Int, sessionType),
};
try
{
var dt = SqlUtility.ExecuteAndGetTable(SPGetSessionsOnFilter.NAME,
CommandType.StoredProcedure, SPGetSessionsOnFilter.TABLE_NAME, prms);
foreach (DataRow row in dt.Rows)
{
// var sessionID = Convert.ToInt32(row["SessionId"]);
if (sessions.All(x => x.Id != Convert.ToInt32(row["SessionId"])))
{
sessions.Add(new Session
{
Title = row["Title"].ToString(),
Id = Convert.ToInt32(row["SessionId"]),
Date = Convert.ToDateTime(row["SessionDate"]),
Description = row["Description"].ToString(),
PresenterFullName = row["PresenterFullName"].ToString(),
Presenter = Convert.ToInt32(row["Presenter"]),
SessionAttendees = new List <User>()
});
}
int userId;
var objSession = sessions.FirstOrDefault(x => x.Id == Convert.ToInt32(row["SessionId"]));
if (objSession == null || !Int32.TryParse(row["UserId"].ToString(), out userId))
{
continue;
}
User objUser = new User
{
UserId = Convert.ToInt32(row["UserId"]),
FirstName = row["FirstName"].ToString(),
LastName = row["LastName"].ToString()
};
objSession.SessionAttendees.Add(objUser);
}
//sessions.AddRange(from DataRow row in
// select new Session
// {
// Title = row["Title"].ToString() ,
// Id = Convert.ToInt32(row["SessionId"]),
// Date = Convert.ToDateTime(row["SessionDate"]),
// Description = row["Description"].ToString(),
// PresenterFullName =row["PresenterFullName"].ToString(),
// Presenter = Convert.ToInt32(row["Presenter"])
// });
}
catch (Exception ex)
{
LogUtility.ErrorRoutine(ex);
}
return(sessions);
}
