public IEnumerable <UserType> AvailableUserTypesPerEmployee(int id)
{
List <UserType> items = new List <UserType>();
string sql = string.Format(@"SELECT *
FROM [dbo].[UserTypes] [ut]
WHERE [ut].[Id] NOT IN (
SELECT [uat].[UserTypeId]
FROM [dbo].[UserAccessTypes] [uat]
WHERE [uat].[AccountId] = {0}
AND [uat].[IsDeleted] = 0
)", id.ToString());
DataTable dt = SCObjects.LoadDataTable(sql, UserConnectionString);
if (dt != null)
{
foreach (DataRow dr in dt.Rows)
{
var item = new UserType
{
Id = int.Parse(dr["Id"].ToString()),
Description = dr["Description"].ToString(),
};
items.Add(item);
}
}
return(items);
}
public IEnumerable <PeerEvaluationListingItem> Evaluations(int userid)
{
List <PeerEvaluationListingItem> items = new List <PeerEvaluationListingItem>();
DataTable dt = SCObjects.LoadDataTable(string.Format("EXEC [dbo].[spPeerEvaluationSummaryPerEmployee] @Id = {0}", userid.ToString()), UserConnectionString);
if (dt != null)
{
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
var item = new PeerEvaluationListingItem
{
EvaluationId = int.Parse(dr["EvaluationId"].ToString()),
Id = int.Parse(dr["Id"].ToString()),
Name = dr["Name"].ToString(),
Position = dr["Position"].ToString(),
Company = dr["Company"].ToString(),
Branch = dr["Branch"].ToString(),
Department = dr["Department"].ToString(),
EvaluationDate = dr["DateEvaluated"].ToString(),
TotalScore = int.Parse(dr["TotalScore"].ToString()),
Status = dr["Status"].ToString()
};
items.Add(item);
}
}
}
return(items);
}
public IEnumerable <AccountListingViewModel> SearchAccount(string Keyword)
{
List <AccountListingViewModel> accounts = new List <AccountListingViewModel>();
var sql = string.Format("EXEC [dbo].[spEmployeeIndex] @Keyword = {0},@QueryType = 1", Keyword);
DataTable dt = SCObjects.LoadDataTable(sql, UserConnectionString);
if (dt != null)
{
foreach (DataRow dr in dt.Rows)
{
var item = new AccountListingViewModel
{
Id = int.Parse(dr["Id"].ToString()),
Name = dr["Name"].ToString(),
Company = dr["Company"].ToString(),
Branch = dr["Branch"].ToString(),
Position = dr["Position"].ToString(),
Department = dr["Department"].ToString(),
EmployeeNo = dr["EmployeeNo"].ToString(),
};
accounts.Add(item);
}
}
return(accounts);
}
public List <EvaluationLineItem> GetKRAEvaluationLineItems(int headerid)
{
List <EvaluationLineItem> items = new List <EvaluationLineItem>();
string sql = string.Format("SELECT * FROM [dbo].[fnKRASummaryPerRating]({0}) [x]", headerid.ToString());
DataTable dt = SCObjects.LoadDataTable(sql, UserConnectionString);
if (dt != null)
{
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
var item = new EvaluationLineItem
{
Id = int.Parse(dr["Id"].ToString()),
HeaderId = int.Parse(dr["RatingHeaderId"].ToString()),
Title = dr["Title"].ToString(),
Description = dr["Description"].ToString(),
Weight = decimal.Parse(dr["Weight"].ToString()),
Score = decimal.Parse(dr["Score"].ToString()),
Comment = dr["Comment"].ToString()
};
items.Add(item);
}
}
}
return(items);
}
public IEnumerable <EmployeeViewModel> EmployeeItems(int page)
{
List <EmployeeViewModel> items = new List <EmployeeViewModel>();
string sql = string.Format("EXEC [dbo].[spEmployeeAssignmetIndex] @Page = {0}", page.ToString());
DataTable dt = SCObjects.LoadDataTable(sql, UserConnectionString);
if (dt != null)
{
foreach (DataRow dr in dt.Rows)
{
var item = new EmployeeViewModel
{
Id = int.Parse(dr["Id"].ToString()),
EmployeeNo = dr["EmployeeNo"].ToString(),
Name = dr["Name"].ToString(),
Company = dr["Company"].ToString(),
Department = dr["Department"].ToString(),
Branch = dr["Branch"].ToString(),
Position = dr["Position"].ToString(),
Category = dr["Category"].ToString(),
};
items.Add(item);
}
}
return(items);
}
public IEnumerable <FirstApprovalItem> GetAllFirstApprovals(int userid)
{
List <FirstApprovalItem> items = new List <FirstApprovalItem>();
string sql = string.Format("EXEC [dbo].[spFirstApprovalPerApprover] @Id = {0}", userid.ToString());
DataTable dt = SCObjects.LoadDataTable(sql, UserConnectionString);
if (dt != null)
{
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
var item = new FirstApprovalItem
{
Id = int.Parse(dr["Id"].ToString()),
Type = dr["Type"].ToString(),
Name = dr["Name"].ToString(),
Title = dr["Title"].ToString(),
Description = dr["Description"].ToString(),
Weight = int.Parse(dr["Weight"].ToString()),
Status = dr["Status"].ToString(),
RatedBy = dr["RatedBy"].ToString(),
RatedDate = dr["RatedDate"].ToString(),
PostedDate = dr["PostedDate"].ToString(),
FirstApproverRemarks = dr["FirstApproverRemarks"].ToString(),
SecondApproverRemarks = dr["SecondApproverRemarks"].ToString(),
};
items.Add(item);
}
}
}
return(items);
}
public IEnumerable <EvaluationResultItem> GetResultPerEmployee(int id)
{
List <EvaluationResultItem> items = new List <EvaluationResultItem>();
var UserConnectionString = _dbContext.Database.GetDbConnection().ConnectionString;
var sql = string.Format("EXEC [dbo].[spGetEvaluationResultPerEmployee] @Id = {0}", id.ToString());
DataTable dt = SCObjects.LoadDataTable(sql, UserConnectionString);
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
var item = new EvaluationResultItem
{
Id = int.Parse(dr["Id"].ToString()),
Type = dr["Type"].ToString(),
Name = dr["EmployeeName"].ToString(),
TotalScore = decimal.Parse(dr["TotalScore"].ToString()),
TotalWeight = decimal.Parse(dr["TotalWeight"].ToString()),
ConvertedScore = decimal.Parse(dr["ConvertedScore"].ToString()),
WeightedScore = decimal.Parse(dr["WeightedScore"].ToString()),
Percentage = decimal.Parse(dr["Percentage"].ToString()),
};
items.Add(item);
}
}
return(items);
}
public IEnumerable <EvaluationHeaderItem> GetKRAEvaluations(int id)
{
List <EvaluationHeaderItem> items = new List <EvaluationHeaderItem>();
string sql = string.Format("SELECT * FROM [dbo].[fnGetKRAEvaluation]({0}) [x]", id.ToString());
DataTable dt = SCObjects.LoadDataTable(sql, UserConnectionString);
if (dt != null)
{
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
var item = new EvaluationHeaderItem
{
Id = int.Parse(dr["Id"].ToString()),
Type = dr["Type"].ToString(),
Name = dr["EmployeeName"].ToString(),
Rater = dr["Rater"].ToString(),
DateRated = dr["DateRated"].ToString(),
Status = dr["Status"].ToString(),
Approver = dr["Approver"].ToString(),
ApprovalDate = dr["ApprovedDate"].ToString(),
ApproverRemarks = dr["ApproverRemarks"].ToString(),
Title = dr["Title"].ToString(),
Description = dr["Description"].ToString(),
Weight = decimal.Parse(dr["Weight"].ToString()),
};
items.Add(item);
}
}
}
return(items);
}
public IEnumerable <EmployeeEvaluation> GetAllRatingPerUser(int id)
{
List <EmployeeEvaluation> employeeEvaluations = new List <EmployeeEvaluation>();
var UserConnectionString = _dbContext.Database.GetDbConnection().ConnectionString;
DataTable dt = SCObjects.LoadDataTable(string.Format("EXEC [dbo].[spGetEvaluationScorePerRater] @Rater = {0}", id.ToString()), UserConnectionString);
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
var item = new EmployeeEvaluation
{
Id = int.Parse(dr["Id"].ToString()),
Name = dr["EmployeeName"].ToString(),
TotalScore = decimal.Parse(dr["TotalScore"].ToString()),
TotalWeight = decimal.Parse(dr["TotalWeight"].ToString()),
ConvertedScore = decimal.Parse(dr["ConvertedScore"].ToString()),
WeightedScore = decimal.Parse(dr["WeightedScore"].ToString()),
Percentage = decimal.Parse(dr["Percentage"].ToString()),
};
employeeEvaluations.Add(item);
}
}
return(employeeEvaluations);
}
public IEnumerable <ApprovalLevelAccountItem> ApprovalLevelItems(int page)
{
string sql = string.Format(@"EXEC [dbo].[spApprovalLevelIndex] @Page = {0}", page.ToString());
DataTable dt = SCObjects.LoadDataTable(sql, UserConnectionString);
List <ApprovalLevelAccountItem> items = new List <ApprovalLevelAccountItem>();
if (dt != null)
{
foreach (DataRow dr in dt.Rows)
{
var item = new ApprovalLevelAccountItem
{
Id = int.Parse(dr["Id"].ToString()),
Name = dr["Name"].ToString(),
Company = dr["Company"].ToString(),
Branch = dr["Branch"].ToString(),
Department = dr["Department"].ToString(),
Position = dr["Position"].ToString(),
IsSet = dr["IsSet"].ToString().Equals("1") ? true : false,
};
items.Add(item);
}
}
return(items);
}
public DataTable GetAllEmployees()
{
var sql = @"SELECT [UserID],
[Username]
FROM [dbo].[fnGetAllUsers]()";
var result = SCObjects.LoadDataTable(sql, Properties.Settings.Default.UserConnectionString);
return(result);
}
public DataTable Terminals()
{
var sql = @"SELECT [fgag].[Id],
[fgag].[Name]
FROM [dbo].[fnGetAllGates]() [fgag]";
var items = SCObjects.LoadDataTable(sql, Properties.Settings.Default.UserConnectionString);
return(items);
}
public DataTable CashiersPerTerminal(DateTime from, DateTime to, string terminal)
{
var sql = string.Format(@"EXEC [dbo].[spGetCashierPerTerminal] @DateFrom = '{0}',
@DateTo = '{1}',
@Terminal ={2}", from, to, terminal);
var items = SCObjects.LoadDataTable(sql, Properties.Settings.Default.UserConnectionString);
return(items);
}
public EvaluationInterpretation InterpretationPerEmployee(int id)
{
var UserConnectionString = _dbContext.Database.GetDbConnection().ConnectionString;
var item = new EvaluationInterpretation();
string sql = string.Format("EXEC [dbo].[spGetInterpretationPerEmployee] @Id = {0}", id.ToString());
DataTable dt = SCObjects.LoadDataTable(sql, UserConnectionString);
if (dt.Rows.Count > 0)
{
item.Id = int.Parse(dt.Rows[0]["Id"].ToString());
item.Name = dt.Rows[0]["Name"].ToString();
item.Score = decimal.Parse(dt.Rows[0]["Score"].ToString());
item.Title = dt.Rows[0]["Title"].ToString();
item.Description = dt.Rows[0]["Description"].ToString();
item.ScoreFrom = decimal.Parse(dt.Rows[0]["ScoreFrom"].ToString());
item.ScoreTo = decimal.Parse(dt.Rows[0]["ScoreTo"].ToString());
}
return(item);
}
public IEnumerable <EmployeeItem> GetAccounts(int userid)
{
string sql = string.Format(@"SELECT a.[Id],
[a].[FirstName] + ' ' + a.[MiddleName] + ' ' + a.[LastName] AS [Name]
FROM [dbo].[Accounts] [a]
WHERE [a].[Id] NOT IN (
SELECT [peh].[EmployeeId]
FROM [dbo].[PeerEvaluationHeader] [peh]
WHERE [peh].[CreatedById] = {0}
)
AND [a].[Id] <> {0}
AND [a].[Id] IN (
SELECT [pa].[RateeId]
FROM [dbo].[PeerAssignment] [pa]
WHERE [pa].[RaterId] = {0}
AND [pa].[IsExpired] = 0
AND [pa].[IsDeleted] = 0
)", userid.ToString());
DataTable dt = SCObjects.LoadDataTable(sql, UserConnectionString);
List <EmployeeItem> items = new List <EmployeeItem>();
if (dt != null)
{
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
var item = new EmployeeItem
{
Id = int.Parse(dr["Id"].ToString()),
Name = dr["Name"].ToString(),
};
items.Add(item);
}
}
}
return(items);
}
public IEnumerable <Account> Accounts(int userid)
{
string sql = string.Format("SELECT * FROM [dbo].[fnRecommendationEmployeeLookUp]({0}) [x]", userid.ToString());
DataTable dt = SCObjects.LoadDataTable(sql, UserConnectionString);
List <Account> items = new List <Account>();
if (dt != null)
{
foreach (DataRow dr in dt.Rows)
{
var item = new Account
{
Id = int.Parse(dr["Id"].ToString()),
FirstName = dr["Firstname"].ToString(),
MiddleName = dr["Middlename"].ToString(),
LastName = dr["Lastname"].ToString(),
};
items.Add(item);
}
}
return(items);
}
public IEnumerable <BehavioralFactor> GetAvailableBehavioral(int employeeid)
{
string sql = string.Format("SELECT * FROM [dbo].[fnAvailableBehavioralLookup]({0}) [fabl]", employeeid.ToString());
DataTable dt = SCObjects.LoadDataTable(sql, UserConnectionString);
List <BehavioralFactor> items = new List <BehavioralFactor>();
if (dt != null)
{
foreach (DataRow dr in dt.Rows)
{
var item = new BehavioralFactor
{
Id = int.Parse(dr["Id"].ToString()),
Title = dr["Title"].ToString(),
Description = dr["Description"].ToString(),
Weight = int.Parse(dr["Weight"].ToString()),
};
items.Add(item);
}
}
return(items);
}
public IEnumerable <UserPerApprover> ApproverAssignedUser(int userid)
{
DataTable dt = SCObjects.LoadDataTable(string.Format(@"SELECT * FROM [dbo].[fnGetAllAssignedPerApprover]({0}) [x]", userid.ToString()), UserConnectionString);
List <UserPerApprover> items = new List <UserPerApprover>();
if (dt != null)
{
foreach (DataRow dr in dt.Rows)
{
var item = new UserPerApprover
{
Id = int.Parse(dr["Id"].ToString()),
Name = dr["Name"].ToString(),
};
items.Add(item);
}
}
return(items);
}
public IEnumerable <Account> GetAllAccountsByRaterId(int id)
{
List <Account> items = new List <Account>();
var sql = string.Format("SELECT * FROM [dbo].[fnPeerAssignmentLookUp]({0}) [x]", id.ToString());
DataTable dt = SCObjects.LoadDataTable(sql, UserConnectionString);
if (dt != null)
{
foreach (DataRow dr in dt.Rows)
{
var item = new Account
{
Id = int.Parse(dr["Id"].ToString()),
FirstName = dr["Firstname"].ToString(),
MiddleName = dr["Middlename"].ToString(),
LastName = dr["Lastname"].ToString()
};
items.Add(item);
}
}
return(items);
}
public IEnumerable <Account> Employees(int RaterId)
{
List <Account> items = new List <Account>();
string sql = string.Format(@"EXEC [dbo].[spEmployeesForRaterAssignment] @Id = {0}", RaterId.ToString());
DataTable dt = SCObjects.LoadDataTable(sql, UserConnectionString);
if (dt != null)
{
foreach (DataRow dr in dt.Rows)
{
var item = new Account
{
Id = int.Parse(dr["Id"].ToString()),
FirstName = dr["Firstname"].ToString(),
MiddleName = dr["Middlename"].ToString(),
LastName = dr["Lastname"].ToString(),
};
items.Add(item);
}
}
return(items);
}
public IEnumerable <NotificationItem> Notifications(int id)
{
List <NotificationItem> items = new List <NotificationItem>();
string sql = string.Format(@"EXEC [dbo].[spNotifications] @To = {0}, @QueryType = 2", id.ToString());
DataTable dt = SCObjects.LoadDataTable(sql, UserConnectionString);
if (dt != null)
{
foreach (DataRow dr in dt.Rows)
{
var item = new NotificationItem
{
Id = int.Parse(dr["Id"].ToString()),
From = dr["From"].ToString(),
To = dr["To"].ToString(),
Message = dr["Message"].ToString(),
Date = DateTime.Parse(dr["Date"].ToString()),
};
items.Add(item);
}
}
return(items);
}
public IEnumerable <RaterViewModel> SearchRater(string keyword)
{
List <RaterViewModel> items = new List <RaterViewModel>();
DataTable dt = SCObjects.LoadDataTable(string.Format("EXEC [dbo].[spRaterAssignmentIndex] @Keyword = '{0}', @QueryType = 1", keyword), UserConnectionString);
if (dt != null)
{
foreach (DataRow dr in dt.Rows)
{
var item = new RaterViewModel
{
Id = int.Parse(dr["Id"].ToString()),
Company = dr["Company"].ToString(),
Branch = dr["Branch"].ToString(),
Name = dr["Name"].ToString(),
Department = dr["Department"].ToString(),
Position = dr["Position"].ToString(),
};
items.Add(item);
}
}
return(items);
}
public IEnumerable <PeerAssignmentIndexItem> Employees(int page)
{
string sql = string.Format(@"EXEC [dbo].[spPeerAssignmentIndex] @Page = {0}, @QueryType = 0", page.ToString());
List <PeerAssignmentIndexItem> items = new List <PeerAssignmentIndexItem>();
DataTable dt = SCObjects.LoadDataTable(sql, UserConnectionString);
if (dt != null)
{
foreach (DataRow dr in dt.Rows)
{
var item = new PeerAssignmentIndexItem
{
Id = int.Parse(dr["Id"].ToString()),
Branch = dr["Branch"].ToString(),
Company = dr["Company"].ToString(),
Department = dr["Department"].ToString(),
Name = dr["Name"].ToString(),
Position = dr["Position"].ToString(),
};
items.Add(item);
}
}
return(items);
}
public DataTable Gates()
{
var gates = SCObjects.LoadDataTable("SELECT [g].[GateID] AS [Id],[g].[GateName] AS [Name] FROM [dbo].[Gates] [g] WHERE ISNULL([g].[IsDeleted],0) = 0 ", Properties.Settings.Default.UserConnectionString);
return(gates);
}
public DataTable Gates()
{
var gates = SCObjects.LoadDataTable("SELECT * FROM [dbo].[fnGetAllGates]() [fgag]", Properties.Settings.Default.UserConnectionString);
return(gates);
}