public List <string> GetClientsFromTimeSheet()
{
using (SGSDBEntities db = new SGSDBEntities())
{
return(db.TimeSheets.Select(x => x.Client).Distinct().ToList());
}
}
public int GetDefaultRole()
{
using (SGSDBEntities db = new SGSDBEntities())
{
return(db.Roles.Where(x => x.RoleName == "User").First().RoleId);
}
}
public void AddRequestApprovals(Request request)
{
RequestType requestType = RequestManager.Instance.GetRequestType(request.RequestTypeId);
string[] approvers = requestType.Approvers.Split('|');
using (SGSDBEntities db = new SGSDBEntities())
{
foreach (string approver in approvers)
{
Approval item = new Approval();
item.ApprovalType = "Request";
item.RequestId = request.RequestId;
item.ApprovalStatus = RequestStatusEnum.ForApproval.ToString();
item.ApproverId = Convert.ToInt32(approver);
item.RequestedDate = request.RequestedDate;
item.RequestedBy = request.RequestedBy.Value;
item.CreatedDate = DateHelper.DateTimeNow;
item.CreatedBy = "system";
db.Approvals.Add(item);
}
db.SaveChanges();
}
}
public User Get(string empId, string password)
{
using (SGSDBEntities db = new SGSDBEntities())
{
//string pwdEncript = EncryptionManager.encrypt(password);
User result = null;
User item = (from x in db.Users
where x.EmployeeId == empId && x.Password == password &&
x.Status == "Active" && x.RecState == "A"
select x).FirstOrDefault();
if (item != null)
{
result = new User();
result.UserId = item.UserId;
result.EmployeeId = item.EmployeeId;
result.LastName = item.LastName;
result.FirstName = item.FirstName;
result.MiddleName = item.MiddleName;
result.NickName = item.NickName;
result.EmploymentType = item.EmploymentType;
result.RoleId = item.RoleId;
result.RecState = item.RecState;
result.Picture = item.Picture;
}
return(result);
}
}
private static string GetValueStr(string code)
{
using (SGSDBEntities db = new SGSDBEntities())
{
var val = db.Settings.Where(x => x.Code == code).First().Value;
return(val.ToString());
}
}
private static double GetValueNum(string code)
{
using (SGSDBEntities db = new SGSDBEntities())
{
var val = db.Settings.Where(x => x.Code == code).First().Value;
return(double.Parse(val.ToString()));
}
}
public IEnumerable <Role> GetAllRoles()
{
using (SGSDBEntities db = new SGSDBEntities())
{
return((from x in db.Roles
select x).ToList());
}
}
public static double WorkingDays(int year)
{
using (SGSDBEntities db = new SGSDBEntities())
{
string code = "WorkingDays_" + year;
var val = db.Settings.Where(x => x.Code == code).First().Value;
return(double.Parse(val.ToString()));
}
}
public RequestType GetRequestType(string requestType)
{
using (SGSDBEntities db = new SGSDBEntities())
{
return((from x in db.RequestTypes
where x.Code == requestType
select x).FirstOrDefault());
}
}
public RequestType GetRequestType(int typeId)
{
using (SGSDBEntities db = new SGSDBEntities())
{
return((from x in db.RequestTypes
where x.RequestTypeId == typeId
select x).FirstOrDefault());
}
}
public IEnumerable <Request> GetParkedItems(int userId)
{
using (SGSDBEntities db = new SGSDBEntities())
{
return((from x in db.Requests
where x.RecState == "A" && x.RequestedBy == userId && x.Status == RequestStatusEnum.Parked.ToString()
select x).ToList());
}
}
public IEnumerable <User> GetAll()
{
using (SGSDBEntities db = new SGSDBEntities())
{
return((from x in db.Users
where x.RecState == "A"
select x).ToList());
}
}
public IEnumerable <User> GetAllByClient(string client)
{
using (SGSDBEntities db = new SGSDBEntities())
{
return((from x in db.Users
where x.RecState == "A" && x.Client == client
select x).ToList());
}
}
public User Get(int id)
{
using (SGSDBEntities db = new SGSDBEntities())
{
return((from x in db.Users
where x.UserId == id
select x).FirstOrDefault());
}
}
public IEnumerable <Request> GetRequest(int requestId, int userId)
{
using (SGSDBEntities db = new SGSDBEntities())
{
return((from x in db.Requests
where x.RecState == "A" && x.RequestedBy == userId && x.RequestId == requestId
select x).ToList());
}
}
public List <TimeSheet> Search(string client, string cutoff)
{
DateTime dt1 = DateTime.Parse(cutoff.Split('-')[0]);
DateTime dt2 = DateTime.Parse(cutoff.Split('-')[1]);
using (SGSDBEntities db = new SGSDBEntities())
{
return((from x in db.TimeSheets
where x.WorkDate >= dt1 && x.WorkDate <= dt2 && x.Client == client
select x).ToList());
}
}
public int SubmitRequest(Request request)
{
int requestId = request.RequestId;
using (SGSDBEntities db = new SGSDBEntities())
{
Request item = db.Requests.SingleOrDefault(x => x.RequestId == requestId);
if (item == null)
{
item = new Request();
}
item.RequestTypeId = request.RequestTypeId;
item.Status = request.Status;
item.Reimb_Desc = request.Reimb_Desc;
item.Reimb_Amt = request.Reimb_Amt;
item.RequestedBy = request.RequestedBy;
if (request.Status == RequestStatusEnum.ForApproval.ToString())
{
item.RequestedDate = DateHelper.DateTimeNow;
}
if (item.RequestId == 0)
{
item.RequestNo = "TMP";
item.CreatedDate = DateHelper.DateTimeNow;
item.CreatedBy = request.CreatedBy;
item.RecState = "A";
db.Requests.Add(item);
}
else
{
item.ModifiedBy = request.ModifiedBy;
item.ModifiedDate = DateHelper.DateTimeNow;
}
db.SaveChanges();
requestId = item.RequestId;
item.RequestNo = String.Format("R-{0:D7}", requestId);
db.SaveChanges();
request = item;
}
if (request.Status == RequestStatusEnum.ForApproval.ToString())
{
ApprovalManager.Instance.AddRequestApprovals(request);
}
return(requestId);
}
public List <GetApprovals_Result> Select(int approvalId, int approverId, int transactionId)
{
List <GetApprovals_Result> result = new List <GetApprovals_Result>();
using (SGSDBEntities db = new SGSDBEntities())
{
var approvals = db.GetApprovals(approvalId, approverId, transactionId);
if (approvals != null)
{
result.AddRange(approvals);
}
}
return(result);
}
public List <GetRequests_Result> GetAll(int requestId, string status, int typeId, int requestedBy)
{
List <GetRequests_Result> result = new List <GetRequests_Result>();
using (SGSDBEntities db = new SGSDBEntities())
{
var requests = db.GetRequests(requestId, status, typeId, requestedBy);
if (requests != null)
{
result.AddRange(requests);
}
}
return(result);
}
public bool IsUserInRole(string userName, string roleName)
{
using (SGSDBEntities db = new SGSDBEntities())
{
User user = db.Users.FirstOrDefault(u => u.EmployeeId.Equals(userName, StringComparison.CurrentCultureIgnoreCase) && u.RecState == "A" && u.Role.RoleName == roleName);
if (user != null)
{
return(true);
}
else
{
return(false);
}
}
}
public string[] GetRoles(string userName)
{
using (SGSDBEntities db = new SGSDBEntities())
{
int memberId = int.Parse(userName);
User user = db.Users.FirstOrDefault(u => u.UserId == memberId && u.RecState == "A");
if (user != null)
{
return new string[] { user.Role.RoleName }
}
;
else
{
return new string[] { }
};
}
}
public ClientObject GetClient(string code)
{
using (SGSDBEntities db = new SGSDBEntities())
{
return((from x in db.Clients
where x.RecState == "A" && x.ClientCode == code
select new ClientObject
{
Id = x.ClientId,
Code = x.ClientCode,
Name = x.ClientName,
Address = x.Address,
AgencyFee = x.AgencyFee,
GovtRemitType = x.GovtRemitType,
CutOffs = x.CutOffs,
GovtRemitDeductCutOff = x.GovtRemitDeductCutOff
}).FirstOrDefault());
}
}
public List <ClientObject> GetClients()
{
using (SGSDBEntities db = new SGSDBEntities())
{
return((from x in db.Clients
where x.RecState == "A"
select new ClientObject
{
Id = x.ClientId,
Code = x.ClientCode,
Name = x.ClientName,
Address = x.Address,
AgencyFee = x.AgencyFee,
GovtRemitType = x.GovtRemitType,
SeparationPay = x.SeparationPay == null ? 0 : x.SeparationPay.Value,
CutOffs = x.CutOffs,
GovtRemitDeductCutOff = x.GovtRemitDeductCutOff
}).ToList());
}
}
public List <BillingObject> GetBilling(string cutoff, string client)
{
List <BillingObject> result = new List <BillingObject>();
DateTime dt1 = DateTime.Parse(cutoff.Split('-')[0]);
DateTime dt2 = DateTime.Parse(cutoff.Split('-')[1]);
_colaRate = SettingManager.ColaRate;
_reliever = SettingManager.RelieverCheck;
_pagibig = SettingManager.Pagibig;
_philHealth = SettingManager.PhilHealth;
_workingDays = SettingManager.WorkingDays(DateTime.Now.Year);
using (SGSDBEntities db = new SGSDBEntities())
{
Client currClient = db.Clients.Where(x => x.ClientCode == client).FirstOrDefault();
_separationPayRate = currClient.SeparationPay == null? 0 : currClient.SeparationPay.Value;
double agencyFee = currClient.AgencyFee / 100;
List <TimeSheet> ts = (from x in db.TimeSheets
where x.Client == client && (x.WorkDate >= dt1 && x.WorkDate <= dt2)
select x).ToList();
List <TimeSheet> ts_prev = null;
bool hasBenefitsDeductions = dt1.Day == int.Parse(currClient.GovtRemitDeductCutOff);
if (hasBenefitsDeductions)
{
string[] clientCutoffs = currClient.CutOffs.Split(',');
string prev_day1 = currClient.GovtRemitDeductCutOff != clientCutoffs[0] ? clientCutoffs[0] : clientCutoffs[1];
DateTime prev_dt1 = dt1.Day < 16 ? new DateTime(dt1.AddMonths(-1).Year, dt1.AddMonths(-1).Month, int.Parse(prev_day1))
: new DateTime(dt1.Year, dt1.Month, int.Parse(prev_day1));
DateTime prev_dt2 = dt1.AddDays(-1);
ts_prev = (from x in db.TimeSheets
where x.Client == client && (x.WorkDate >= prev_dt1 && x.WorkDate <= prev_dt2)
select x).ToList();
}
List <string> empIds = ts.Where(x => x.Client == client).Select(x => x.EmpId).Distinct().ToList();
foreach (string empId in empIds)
{
List <TimeSheet> empTS = ts.Where(x => x.EmpId == empId).ToList();
if (empTS.Count() > 0)
{
bool isReliever = empTS.First().Position.ToLower() == _reliever;
List <TimeSheet> empTS_prev = null;
double emp_prev_total = 0;
if (hasBenefitsDeductions && !isReliever)
{
empTS_prev = ts_prev.Where(x => x.EmpId == empId).ToList();
if (empTS_prev.Count > 0)
{
BillingObject t = ComputeBilling(empTS_prev);
if (currClient.GovtRemitType == "BasicPayOnly")
{
emp_prev_total = t.BasicPay;
}
else
{
emp_prev_total = t.Total - (t.IncentiveLeavePay + t.ThirteenthMonthPay + t.SeparationPay);
}
}
else
{
emp_prev_total = 0;
}
}
if (empTS != null)
{
BillingObject t = ComputeBilling(empTS);
t.HasSeparationPay = currClient.SeparationPay.Value > 0;
t.HasBenefitsDeductions = hasBenefitsDeductions;
if (hasBenefitsDeductions && !isReliever)
{
double total = 0;
if (currClient.GovtRemitType == "BasicPayOnly")
{
total = t.BasicPay + emp_prev_total;
}
else
{
total = (t.Total + emp_prev_total) - (t.IncentiveLeavePay + t.ThirteenthMonthPay + t.SeparationPay);
}
SSSContribution sss = SSSCompute(total);
t.SSS = sss.ER;
t.Pagibig = _pagibig;
double phContri = PhilHealthContribution(total);
t.PhilHealth = phContri;
t.TotalGovRemitance = t.SSS + t.Pagibig + t.PhilHealth;
}
t.TotalReimbursableCost = t.Total + t.TotalGovRemitance;
t.AgencyFee = t.TotalReimbursableCost * agencyFee;
t.TotalWithFee = t.TotalReimbursableCost + t.AgencyFee;
t.VAT = t.TotalWithFee * 0.12;
t.TotalAmount = t.TotalWithFee + t.VAT + t.Allowance + t.Adjustment;
result.Add(t);
}
}
}
}
return(result.Count() > 0 ? result : null);
}
public int SaveUser(User user)
{
int memberId = user.UserId;
using (SGSDBEntities db = new SGSDBEntities())
{
int nextId = db.Users.Where(x => x.Client == user.Client).Count() + 1;
User item = db.Users.SingleOrDefault(x => x.UserId == user.UserId);
if (item == null)
{
item = new User();
}
item.FirstName = user.FirstName;
item.MiddleName = user.MiddleName;
item.LastName = user.LastName;
item.NickName = user.NickName;
item.Gender = user.Gender;
item.MaritalStatus = user.MaritalStatus;
item.DateOfBirth = user.DateOfBirth;
item.PlaceOfBirth = user.PlaceOfBirth;
item.PresentAddress = user.PresentAddress;
item.ProvincialAddress = user.ProvincialAddress;
item.LandlineNo = user.LandlineNo;
item.MobileNo = user.MobileNo;
item.Email = user.Email;
item.Nationality = user.Nationality;
item.Ref_Name = user.Ref_Name;
item.Ref_Relationship = user.Ref_Relationship;
item.Ref_Address = user.Ref_Address;
item.Ref_ContactNo = user.Ref_ContactNo;
item.TaxIdNo = user.TaxIdNo;
item.SSSNo = user.SSSNo;
item.HDMFNo = user.HDMFNo;
item.PhilHealthNo = user.PhilHealthNo;
item.Status = user.Status;
item.EmploymentType = user.EmploymentType;
item.Position = user.Position;
item.Client = user.Client;
item.DateHired = user.DateHired;
if (user.Picture != null)
{
item.Picture = user.Picture;
}
if (item.UserId == 0)
{
item.RoleId = GetDefaultRole();
if (item.Client == "HO")
{
item.EmployeeId = String.Format("E-{0:D4}", nextId);
}
else
{
item.EmployeeId = String.Format("C-" + item.Client + "-{0:D3}", nextId);
}
item.Password = user.Password;
item.CreatedDate = DateHelper.DateTimeNow;
item.CreatedBy = user.CreatedBy;
item.RecState = "A";
db.Users.Add(item);
}
else
{
item.ModifiedBy = user.ModifiedBy;
item.ModifiedDate = DateHelper.DateTimeNow;
}
db.SaveChanges();
memberId = item.UserId;
}
return(memberId);
}
public void Save(string fileName, string UploadedBy)
{
string con =
@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";" +
@"Extended Properties='Excel 8.0;HDR=Yes;'";
using (OleDbConnection connection = new OleDbConnection(con))
{
connection.Open();
OleDbCommand command = new OleDbCommand("select * from [DTS$]", connection);
using (OleDbDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
using (SGSDBEntities db = new SGSDBEntities())
{
string empId = GetValueString(dr["ID"]);
DateTime?workDate = GetValueDate(dr["Date"]);
if (workDate != null && empId != "")
{
TimeSheet item = db.TimeSheets.SingleOrDefault(x => x.EmpId == empId && x.WorkDate == workDate);
if (item == null)
{
item = new TimeSheet();
}
item.EmpId = empId;
item.Name = GetValueString(dr["Name"]);
item.Position = GetValueString(dr["Position"]);
item.Client = GetValueString(dr["Client"]);
item.BasicRate = GetValueNum(dr["BasicRate"]);
item.WorkDate = workDate;
item.TimeIn = GetValueTime(dr["TimeIn"]);
item.TimeOut = GetValueTime(dr["TimeOut"]);
item.RegularHours = GetValueNum(dr["RegularHours"]);
item.Late = GetValueNum(dr["Late"]);
item.LegalHolidayNotWorked = GetValueNum(dr["LegalHolidayNotWorked"]);
item.LegalHolidayOT = GetValueNum(dr["LegalHolidayOT"]);
item.ExcessOfLegalHolidayOT = GetValueNum(dr["ExcessOfLegalHolidayOT"]);
item.SpecialHolidayNotWorked = GetValueNum(dr["SpecialHolidayNotWorked"]);
item.SpecialHolidayOT = GetValueNum(dr["SpecialHolidayOT"]);
item.ExcessOfSpecialHolidayOT = GetValueNum(dr["ExcessOfSpecialHolidayOT"]);
item.AuthorizedOT = GetValueNum(dr["AuthorizedOT"]);
item.RestDayOT = GetValueNum(dr["RestDayOT"]);
item.ExcessOfRestDayOT = GetValueNum(dr["ExcessOfRestDayOT"]);
item.NightDiff = GetValueNum(dr["NightDiff"]);
item.SSSLoan = GetValueNum(dr["SSSLoan"]);
item.PagibigLoan = GetValueNum(dr["PagibigLoan"]);
item.Allowance = GetValueNum(dr["Allowance"]);
item.Adjustment = GetValueNum(dr["Adjustment"]);
if (item.LegalHolidayOT > 0 || item.SpecialHolidayOT > 0 || item.RestDayOT > 0)
{
item.RegularHours = 0;
}
if (item.Id == 0)
{
item.CreatedBy = UploadedBy;
item.CreatedDate = DateTime.UtcNow;
db.TimeSheets.Add(item);
}
else
{
item.ModifiedBy = UploadedBy;
item.ModifiedDate = DateTime.UtcNow;
}
db.SaveChanges();
}
}
}
}
}
}
public List <PayrollObject> GetPayroll(string cutoff, string client)
{
List <PayrollObject> result = new List <PayrollObject>();
DateTime dt1 = DateTime.Parse(cutoff.Split('-')[0]);
DateTime dt2 = DateTime.Parse(cutoff.Split('-')[1]);
_colaRate = SettingManager.ColaRate;
_reliever = SettingManager.RelieverCheck;
_pagibig = SettingManager.Pagibig;
_philHealth = SettingManager.PhilHealth;
_workingDays = SettingManager.WorkingDays(DateTime.Now.Year);
using (SGSDBEntities db = new SGSDBEntities())
{
Client currClient = db.Clients.Where(x => x.ClientCode == client).FirstOrDefault();
List <TimeSheet> ts = (from x in db.TimeSheets
where x.Client == client && (x.WorkDate >= dt1 && x.WorkDate <= dt2)
select x).ToList();
List <TimeSheet> ts_prev = null;
bool hasBenefitsDeductions = dt1.Day == int.Parse(currClient.GovtRemitDeductCutOff);
if (hasBenefitsDeductions)
{
string[] clientCutoffs = currClient.CutOffs.Split(',');
string prev_day1 = currClient.GovtRemitDeductCutOff != clientCutoffs[0] ? clientCutoffs[0] : clientCutoffs[1];
DateTime prev_dt1 = dt1.Day < 16 ? new DateTime(dt1.AddMonths(-1).Year, dt1.AddMonths(-1).Month, int.Parse(prev_day1))
: new DateTime(dt1.Year, dt1.Month, int.Parse(prev_day1));
DateTime prev_dt2 = dt1.AddDays(-1);
ts_prev = (from x in db.TimeSheets
where x.Client == client && (x.WorkDate >= prev_dt1 && x.WorkDate <= prev_dt2)
select x).ToList();
}
List <string> empIds = ts.Select(x => x.EmpId).Distinct().ToList();
foreach (string empId in empIds)
{
List <TimeSheet> empTS = ts.Where(x => x.EmpId == empId).ToList();
if (empTS.Count() > 0)
{
bool isReliever = empTS.First().Position.ToLower() == _reliever;
List <TimeSheet> empTS_prev = null;
double emp_prev_total = 0;
if (hasBenefitsDeductions && !isReliever)
{
empTS_prev = ts_prev.Where(x => x.EmpId == empId).ToList();
if (empTS_prev.Count > 0)
{
PayrollObject t = ComputePayroll(empTS_prev);
if (currClient.GovtRemitType == "BasicPayOnly")
{
emp_prev_total = t.BasicPay;
}
else
{
emp_prev_total = t.Total;
}
}
else
{
emp_prev_total = 0;
}
}
if (empTS != null)
{
PayrollObject t = ComputePayroll(empTS);
t.HasBenefitsDeductions = hasBenefitsDeductions;
if (hasBenefitsDeductions && !isReliever)
{
double total = 0;
if (currClient.GovtRemitType == "BasicPayOnly")
{
total = t.BasicPay + emp_prev_total;
}
else
{
total = t.Total + emp_prev_total;
}
SSSContribution sss = SSSCompute(total);
t.SSSER = sss.ER;
t.SSSEE = sss.EE;
t.SSSTotal = sss.ER + sss.EE;
t.PagibigER = _pagibig;
t.PagibigEE = _pagibig;
t.PagibigTotal = t.PagibigER + t.PagibigEE;
double phContri = PhilHealthContribution(total);
t.PhilHealthER = phContri;
t.PhilHealthEE = phContri;
t.PhilHealthTotal = phContri * 2;
t.TotalDeduction = t.SSSEE + t.PagibigEE + t.PhilHealthEE;
}
else
{
t.SSSLoan = empTS.First().SSSLoan.Value;
t.PagibigLoan = empTS.First().PagibigLoan.Value;
t.TotalDeduction = t.SSSLoan + t.PagibigLoan;
}
if (t.Adjustment < 0)
{
t.TotalDeduction += t.Adjustment;
}
else
{
t.Total += t.Adjustment;
}
t.Total += t.Allowance;
t.NetPay = t.Total - t.TotalDeduction;
result.Add(t);
}
}
}
}
return(result.Count() > 0? result : null);
}
