/// <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 <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>
/// Inteface method to fetch sessions by User id
/// </summary>
/// <param name="objSession">User Id</param>
/// <returns>List Of session</returns>
public bool AddEditSessions(Session objSession)
{
var prms = new List <SqlParameter>
{
SqlUtility.CreateParameter(SPAddEditSession.PARAM_PRESENTER, SqlDbType.NVarChar, objSession.Presenter),
SqlUtility.CreateParameter(SPAddEditSession.PARAM_ID, SqlDbType.Int, objSession.Id),
SqlUtility.CreateParameter(SPAddEditSession.PARAM_TITLE, SqlDbType.NVarChar, objSession.Title),
SqlUtility.CreateParameter(SPAddEditSession.PARAM_DESCRIPTION, SqlDbType.NVarChar, objSession.Description),
SqlUtility.CreateParameter(SPAddEditSession.PARAM_DATE, SqlDbType.DateTime, objSession.Date),
SqlUtility.CreateParameter(SPAddEditSession.PARAM_ATTENDEES, SqlDbType.NVarChar, string.Join(",", objSession.Attendee))
};
try
{
var rowsAffected = SqlUtility.ExecuteNonQuery(SPAddEditSession.NAME,
CommandType.StoredProcedure,
prms);
return(rowsAffected > 0);
}
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 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);
}
/// <summary>
/// Calls stored procedure which adds user.
/// </summary>
/// <param name="userData">User data object.</param>
/// <returns>True if added.</returns>
public bool AddUser(User userData)
{
var prms = new List <SqlParameter>
{
SqlUtility.CreateParameter(SPAddUser.PARAM_FIRST_NAME,
SqlDbType.VarChar, userData.FirstName),
SqlUtility.CreateParameter(SPAddUser.PARAM_LAST_NAME,
SqlDbType.VarChar, userData.LastName),
SqlUtility.CreateParameter(SPAddUser.PARAM_USER_NAME,
SqlDbType.VarChar, userData.UserName),
SqlUtility.CreateParameter(SPAddUser.PARAM_PASSWORD,
SqlDbType.VarChar, userData.Password),
SqlUtility.CreateParameter(SPAddUser.PARAM_EMAIL,
SqlDbType.VarChar, userData.Email),
SqlUtility.CreateParameter(SPAddUser.PARAM_DESIGNATION,
SqlDbType.VarChar, userData.Designation),
SqlUtility.CreateParameter(SPAddUser.PARAM_PROFILE_PICTURE_NAME,
SqlDbType.VarChar, userData.ProfilePictureName),
SqlUtility.CreateParameter(SPAddUser.PARAM_IS_FEMALE,
SqlDbType.Bit, userData.IsFemale),
SqlUtility.CreateParameter(SPAddUser.PARAM_IS_ADMINISTRATOR,
SqlDbType.VarChar, userData.IsAdministrator),
SqlUtility.CreateParameter(SPAddUser.PARAM_IS_TRAINER,
SqlDbType.VarChar, userData.IsTrainer),
SqlUtility.CreateParameter(SPAddUser.PARAM_IS_TRAINEE,
SqlDbType.VarChar, userData.IsTrainee),
SqlUtility.CreateParameter(SPAddUser.PARAM_IS_MANAGER,
SqlDbType.VarChar, userData.IsManager)
};
try
{
var rowsAffected = SqlUtility.ExecuteNonQuery(SPAddUser.NAME,
CommandType.StoredProcedure, prms);
return(rowsAffected > 0);
}
catch (Exception ex)
{
LogUtility.ErrorRoutine(ex);
}
return(false);
}
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 bool AddFeedback(Feedback feedbackData)
{
try
{
var prms = new List <SqlParameter>
{
SqlUtility.CreateParameter(SPAddFeedback.PARAM_FEEDBACK_TEXT,
SqlDbType.VarChar, feedbackData.FeedbackText),
SqlUtility.CreateParameter(SPAddFeedback.PARAM_TITLE,
SqlDbType.VarChar, feedbackData.Title),
SqlUtility.CreateParameter(SPAddFeedback.PARAM_FEEDBACK_TYPE,
SqlDbType.Int, feedbackData.FeedbackType.FeedbackTypeId),
SqlUtility.CreateParameter(SPAddFeedback.PARAM_PROJECT_ID,
SqlDbType.Int, feedbackData.Project.ProjectId),
SqlUtility.CreateParameter(SPAddFeedback.PARAM_SKILL_ID,
SqlDbType.Int, feedbackData.Skill.SkillId),
SqlUtility.CreateParameter(SPAddFeedback.PARAM_RATING,
SqlDbType.Int, feedbackData.Rating),
SqlUtility.CreateParameter(SPAddFeedback.PARAM_ADDED_BY,
SqlDbType.Int, feedbackData.AddedBy.UserId),
SqlUtility.CreateParameter(SPAddFeedback.PARAM_ADDED_FOR,
SqlDbType.Int, feedbackData.AddedFor.UserId),
SqlUtility.CreateParameter(SPAddFeedback.PARAM_START_DATE,
SqlDbType.Date, feedbackData.StartDate),
SqlUtility.CreateParameter(SPAddFeedback.PARAM_END_DATE,
SqlDbType.Date, feedbackData.EndDate)
};
var rowsAffected = SqlUtility.ExecuteNonQuery(SPAddFeedback.NAME,
CommandType.StoredProcedure, prms);
return(rowsAffected > 0);
}
catch (Exception ex)
{
LogUtility.ErrorRoutine(ex);
}
return(false);
}
/// <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);
}
