/// <summary>
/// Used to get the list of requests made by user along with their status
/// </summary>
/// <param name="argLoggedInUser">Logged in User ID</param>
/// <param name="argType">Request Type</param>
/// <returns></returns>
public UserRequestsBECollection GetUserSkillRequests(int argLoggedInUser, string argType)
{
#region Declarations
UserRequestsBECollection l_requestCollection = new UserRequestsBECollection();
SkillsDBManager l_SkillsDBManager = new SkillsDBManager("SkillsDBConnection");
List <IDbDataParameter> l_Parameters = new List <IDbDataParameter>();
DataTable l_SkillDataTable = new DataTable();
UserRequestsBE l_RequestBE;
int l_SkillCount = 0;
#endregion
try
{
l_Parameters.Add(l_SkillsDBManager.CreateParameter(ProcedureParams.LOGGEDINUSERID, argLoggedInUser, DbType.Int32));
if (string.IsNullOrEmpty(argType))
{
l_Parameters.Add(l_SkillsDBManager.CreateParameter(ProcedureParams.TYPE, DBNull.Value, DbType.String));
}
else
{
l_Parameters.Add(l_SkillsDBManager.CreateParameter(ProcedureParams.TYPE, argType, DbType.String));
}
l_SkillDataTable = l_SkillsDBManager.GetDataTable(StoredProcedures.GET_ALLSKILLREQUESTS, CommandType.StoredProcedure, l_Parameters.ToArray());
if (l_SkillDataTable != null && l_SkillDataTable.Rows.Count > 0)
{
l_SkillCount = l_SkillDataTable.Rows.Count;
for (int i = 0; i < l_SkillCount; i++)
{
l_RequestBE = new UserRequestsBE();
DataRow l_Row = l_SkillDataTable.Rows[i];
l_RequestBE.RequestType = l_Row["Request type"] == DBNull.Value ? string.Empty : Convert.ToString(l_Row["Request type"]);
l_RequestBE.Status = l_Row["Status"] == DBNull.Value ? string.Empty : Convert.ToString(l_Row["Status"]);
l_RequestBE.Comments = l_Row["Comments"] == DBNull.Value ? string.Empty : Convert.ToString(l_Row["Comments"]);
l_RequestBE.RequestedDate = l_Row["Request date"] == DBNull.Value ? DateTime.Now : Convert.ToDateTime(l_Row["Request date"]);
l_RequestBE.Skill = l_Row["Skill"] == DBNull.Value ? string.Empty : Convert.ToString(l_Row["Skill"]);
l_RequestBE.SubSkill = l_Row["Sub-skill"] == DBNull.Value ? string.Empty : Convert.ToString(l_Row["Sub-skill"]);
//Badge Details
l_RequestBE.BadgeGivenFor = l_Row["BadgeGivenFor"] == DBNull.Value ? string.Empty : Convert.ToString(l_Row["BadgeGivenFor"]);
l_RequestBE.BadgeName = l_Row["BadgeName"] == DBNull.Value ? string.Empty : Convert.ToString(l_Row["BadgeName"]);
l_RequestBE.BadgeImageURL = l_Row["BadgeImageURL"] == DBNull.Value ? string.Empty : Convert.ToString(l_Row["BadgeImageURL"]);
l_RequestBE.BadgeID = l_Row["BadgeID"] == DBNull.Value ? 0 : Convert.ToInt32(l_Row["BadgeID"]);
l_requestCollection.Add(l_RequestBE);
}
}
}
catch (Exception ex)
{
throw ex;
}
return(l_requestCollection);
}
/// <summary>
/// Get badge details in system
/// </summary>
/// <param name="argLoggedInUser">Logged in user ID</param>
/// <param name="argBadgeType">Badge Type</param>
/// <returns></returns>
public BadgeBECollection GetBadges(int argLoggedInUser, string argBadgeType)
{
#region Declarations
BadgeBECollection l_BadgeBECollection = new BadgeBECollection();
SkillsDBManager l_SkillsDBManager = new SkillsDBManager("SkillsDBConnection");
List <IDbDataParameter> l_Parameters = new List <IDbDataParameter>();
DataTable l_BadgeDataTable = new DataTable();
BadgeBE l_BadgeBE;
int l_BadgeCount = 0;
#endregion
try
{
l_Parameters.Add(l_SkillsDBManager.CreateParameter(ProcedureParams.LOGGEDINUSERID, argLoggedInUser, DbType.Int32));
if (string.IsNullOrEmpty(argBadgeType))
{
l_Parameters.Add(l_SkillsDBManager.CreateParameter(ProcedureParams.TYPE, DBNull.Value, DbType.String));
}
else
{
l_Parameters.Add(l_SkillsDBManager.CreateParameter(ProcedureParams.TYPE, argBadgeType, DbType.String));
}
l_BadgeDataTable = l_SkillsDBManager.GetDataTable(StoredProcedures.GET_ALLBADGES, CommandType.StoredProcedure, l_Parameters.ToArray());
if (l_BadgeDataTable != null && l_BadgeDataTable.Rows.Count > 0)
{
l_BadgeCount = l_BadgeDataTable.Rows.Count;
for (int i = 0; i < l_BadgeCount; i++)
{
l_BadgeBE = new BadgeBE();
DataRow l_Row = l_BadgeDataTable.Rows[i];
l_BadgeBE.BadgeName = l_Row["BadgeName"] == DBNull.Value ? string.Empty : Convert.ToString(l_Row["BadgeName"]);
l_BadgeBE.BadgeID = l_Row["BadgeID"] == DBNull.Value ? 0 : Convert.ToInt32(l_Row["BadgeID"]);
l_BadgeBE.BadgeURL = l_Row["BadgeImageURL"] == DBNull.Value ? string.Empty : Convert.ToString(l_Row["BadgeImageURL"]);
l_BadgeBECollection.Add(l_BadgeBE);
}
}
}
catch (Exception ex)
{
throw ex;
}
return(l_BadgeBECollection);
}
/// <summary>
/// Used to get list of experts with required skills and subskills
/// </summary>
/// <param name="argLoggedInUser">Logged in User ID</param>
/// <param name="argSkillID">Skill ID</param>
/// <param name="argSubSkillID">SubSkill ID</param>
/// <returns></returns>
public QueryBECollection GetEmployeesBasedOnSkillAndSubskill(int argLoggedInUser, int argSkillID, int argSubSkillID)
{
#region Declarations
QueryBECollection l_QueryBECollection = new QueryBECollection();
SkillsDBManager l_SkillsDBManager = new SkillsDBManager("SkillsDBConnection");
List <IDbDataParameter> l_Parameters = new List <IDbDataParameter>();
DataTable l_QueryDataTable = new DataTable();
QueryBE l_Query;
int l_QueryResultCount = 0;
#endregion
try
{
l_Parameters.Add(l_SkillsDBManager.CreateParameter(ProcedureParams.LOGGEDINUSERID, argLoggedInUser, DbType.Int32));
l_Parameters.Add(l_SkillsDBManager.CreateParameter(ProcedureParams.SKILLID, argSkillID, DbType.Int32));
l_Parameters.Add(l_SkillsDBManager.CreateParameter(ProcedureParams.SUBSKILLID, argSubSkillID, DbType.Int32));
l_QueryDataTable = l_SkillsDBManager.GetDataTable(StoredProcedures.GET_EMPLOYEESFORASKILLSUBSKILL, CommandType.StoredProcedure, l_Parameters.ToArray());
if (l_QueryDataTable != null && l_QueryDataTable.Rows.Count > 0)
{
l_QueryResultCount = l_QueryDataTable.Rows.Count;
for (int i = 0; i < l_QueryResultCount; i++)
{
l_Query = new QueryBE();
DataRow l_Row = l_QueryDataTable.Rows[i];
l_Query.Name = l_Row["Employee Name"] == DBNull.Value ? string.Empty : Convert.ToString(l_Row["Employee Name"]);
l_Query.Email = l_Row["Email"] == DBNull.Value ? string.Empty : Convert.ToString(l_Row["Email"]);
l_Query.ContactNumber = l_Row["Contact Number"] == DBNull.Value ? string.Empty : Convert.ToString(l_Row["Contact Number"]);
l_Query.SkillPoints = l_Row["Skill Points"] == DBNull.Value ? 0 : Convert.ToInt32(l_Row["Skill Points"]);
l_QueryBECollection.Add(l_Query);
}
}
}
catch (Exception ex)
{
throw;
}
return(l_QueryBECollection);
}
/// <summary>
/// Used to fetch all subskills for a skill
/// </summary>
/// <param name="argLoggedInUser">Logged in UserID</param>
/// <param name="argSkillID">SkillID</param>
/// <returns></returns>
public SubSkillBECollection GetAllSubSkills(int argLoggedInUser, int argSkillID)
{
#region Declarations
SubSkillBECollection l_SubSkillCollection = new SubSkillBECollection();
SkillsDBManager l_SkillsDBManager = new SkillsDBManager("SkillsDBConnection");
List <IDbDataParameter> l_Parameters = new List <IDbDataParameter>();
DataTable l_SubSkillDataTable = new DataTable();
SubskillsBE l_SubSkill;
int l_SubSkillCount = 0;
#endregion
try
{
l_Parameters.Add(l_SkillsDBManager.CreateParameter(ProcedureParams.LOGGEDINUSERID, argLoggedInUser, DbType.Int32));
l_Parameters.Add(l_SkillsDBManager.CreateParameter(ProcedureParams.SKILLID, argSkillID, DbType.Int32));
l_SubSkillDataTable = l_SkillsDBManager.GetDataTable(StoredProcedures.GET_ALLSUBSKILLS_FORASKILL, CommandType.StoredProcedure, l_Parameters.ToArray());
if (l_SubSkillDataTable != null && l_SubSkillDataTable.Rows.Count > 0)
{
l_SubSkillCount = l_SubSkillDataTable.Rows.Count;
for (int i = 0; i < l_SubSkillCount; i++)
{
l_SubSkill = new SubskillsBE();
DataRow l_Row = l_SubSkillDataTable.Rows[i];
l_SubSkill.SkillID = l_Row["SkillID"] == DBNull.Value ? 0 : Convert.ToInt32(l_Row["SkillID"]);
l_SubSkill.SubSkillID = l_Row["SubskillID"] == DBNull.Value ? 0 : Convert.ToInt32(l_Row["SubskillID"]);
l_SubSkill.SubSkillName = l_Row["SubskillName"] == DBNull.Value ? string.Empty : Convert.ToString(l_Row["SubskillName"]);
l_SubSkillCollection.Add(l_SubSkill);
}
}
}
catch (Exception ex)
{
throw;
}
return(l_SubSkillCollection);
}
/// <summary>
/// Get list of users on the basis of name prefix
/// </summary>
/// <param name="argLoggedInUser">Logged in user ID</param>
/// <param name="argNamePrefix">Name prefix for search</param>
/// <returns></returns>
public UserDetailsBECollection GetUsersByName(int argLoggedInUser, string argNamePrefix)
{
#region Declarations
UserDetailsBECollection l_UserDetailsBECollection = new UserDetailsBECollection();
SkillsDBManager l_SkillsDBManager = new SkillsDBManager("SkillsDBConnection");
List <IDbDataParameter> l_Parameters = new List <IDbDataParameter>();
DataTable l_UsersDataTable = new DataTable();
UserDetailsBE l_UserDetailsBE;
int l_UserCount = 0;
#endregion
try
{
l_Parameters.Add(l_SkillsDBManager.CreateParameter(ProcedureParams.LOGGEDINUSERID, argLoggedInUser, DbType.Int32));
l_Parameters.Add(l_SkillsDBManager.CreateParameter(ProcedureParams.NAMEPREFIX, argNamePrefix, DbType.String));
l_UsersDataTable = l_SkillsDBManager.GetDataTable(StoredProcedures.GET_ALLUSERSBYNAME, CommandType.StoredProcedure, l_Parameters.ToArray());
if (l_UsersDataTable != null && l_UsersDataTable.Rows.Count > 0)
{
l_UserCount = l_UsersDataTable.Rows.Count;
for (int i = 0; i < l_UserCount; i++)
{
l_UserDetailsBE = new UserDetailsBE();
DataRow l_Row = l_UsersDataTable.Rows[i];
l_UserDetailsBE.UserName = l_Row["UserName"] == DBNull.Value ? string.Empty : Convert.ToString(l_Row["UserName"]);
l_UserDetailsBE.UserID = l_Row["UserID"] == DBNull.Value ? 0 : Convert.ToInt32(l_Row["UserID"]);
l_UserDetailsBECollection.Add(l_UserDetailsBE);
}
}
}
catch (Exception ex)
{
throw ex;
}
return(l_UserDetailsBECollection);
}
/// <summary>
/// Used to get list of pending approvals for skill expert
/// </summary>
/// <param name="argLoggedInUser">Logged in User ID</param>
/// <param name="argType">Request Type</param>
/// <returns></returns>
public PendingSkillExpertApprovalBECollection GetPendingSkillExpertApprovals(int argLoggedInUser, string argType)
{
#region Declarations
PendingSkillExpertApprovalBECollection l_requestCollection = new PendingSkillExpertApprovalBECollection();
SkillsDBManager l_SkillsDBManager = new SkillsDBManager("SkillsDBConnection");
List <IDbDataParameter> l_Parameters = new List <IDbDataParameter>();
DataTable l_SkillDataTable = new DataTable();
PendingSkillExpertApprovalBE l_RequestBE;
int l_SkillCount = 0;
#endregion
try
{
l_Parameters.Add(l_SkillsDBManager.CreateParameter(ProcedureParams.LOGGEDINUSERID, argLoggedInUser, DbType.Int32));
if (string.IsNullOrEmpty(argType))
{
l_Parameters.Add(l_SkillsDBManager.CreateParameter(ProcedureParams.TYPE, DBNull.Value, DbType.String));
}
else
{
l_Parameters.Add(l_SkillsDBManager.CreateParameter(ProcedureParams.TYPE, argType, DbType.String));
}
l_SkillDataTable = l_SkillsDBManager.GetDataTable(StoredProcedures.GET_ALLPENDINGSKILLEXPERTAPPROVALS, CommandType.StoredProcedure, l_Parameters.ToArray());
if (l_SkillDataTable != null && l_SkillDataTable.Rows.Count > 0)
{
l_SkillCount = l_SkillDataTable.Rows.Count;
for (int i = 0; i < l_SkillCount; i++)
{
l_RequestBE = new PendingSkillExpertApprovalBE();
DataRow l_Row = l_SkillDataTable.Rows[i];
l_RequestBE.RequestType = l_Row["Request type"] == DBNull.Value ? string.Empty : Convert.ToString(l_Row["Request type"]);
l_RequestBE.Status = l_Row["Status"] == DBNull.Value ? string.Empty : Convert.ToString(l_Row["Status"]);
l_RequestBE.ManagerComments = l_Row["ManagerComments"] == DBNull.Value ? string.Empty : Convert.ToString(l_Row["ManagerComments"]);
l_RequestBE.SkillExpertComments = l_Row["SkillExpertComments"] == DBNull.Value ? string.Empty : Convert.ToString(l_Row["SkillExpertComments"]);
l_RequestBE.UserComments = l_Row["UserComments"] == DBNull.Value ? string.Empty : Convert.ToString(l_Row["UserComments"]);
l_RequestBE.RequestedDate = l_Row["Request date"] == DBNull.Value ? DateTime.Now : Convert.ToDateTime(l_Row["Request date"]);
l_RequestBE.SkillName = l_Row["SkillName"] == DBNull.Value ? string.Empty : Convert.ToString(l_Row["SkillName"]);
l_RequestBE.SubSkillName = l_Row["SubskillName"] == DBNull.Value ? string.Empty : Convert.ToString(l_Row["SubskillName"]);
l_RequestBE.SkillPoints = l_Row["SkillPoints"] == DBNull.Value ? 0 : Convert.ToInt32(l_Row["SkillPoints"]);
l_RequestBE.UniqueID = l_Row["UniqueID"] == DBNull.Value ? 0 : Convert.ToInt32(l_Row["UniqueID"]);
l_RequestBE.SubSkillName = l_Row["SubskillName"] == DBNull.Value ? string.Empty : Convert.ToString(l_Row["SubskillName"]);
l_RequestBE.FileName = l_Row["FileName"] == DBNull.Value ? string.Empty : Convert.ToString(l_Row["FileName"]);
l_RequestBE.FileGUID = l_Row["FileGUID"] == DBNull.Value ? string.Empty : Convert.ToString(l_Row["FileGUID"]);
l_RequestBE.RequestedBy = l_Row["UserName"] == DBNull.Value ? string.Empty : Convert.ToString(l_Row["UserName"]);
l_RequestBE.Room = l_Row["Room"] == DBNull.Value ? string.Empty : Convert.ToString(l_Row["Room"]);
l_RequestBE.DemoSchedule = l_Row["DemoSchedule"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(l_Row["DemoSchedule"]);
l_RequestBE.RequestCode = l_Row["RequestCode"] == DBNull.Value ? string.Empty : Convert.ToString(l_Row["RequestCode"]);
l_requestCollection.Add(l_RequestBE);
}
}
}
catch (Exception ex)
{
throw ex;
}
return(l_requestCollection);
}