public IList<PayrollPeriod> LoadAllPeriod()
{
using (var db = new pisidataContext())
{
var query = from p in db.tblMonths
where p.monClose.Equals(true)
select new PayrollPeriod() { Code = p.MonthID, Name = p.monName };
return query.ToList();
}
}
public VerificationEmployee LoadByKey(string key)
{
using (var db = new pisidataContext())
{
var query = from p in db.tblEmployees
join v in db.tblUserVerifications on p.EmployeeID equals v.EmployeeId
where v.VeryficationKey.Equals(key)
select new VerificationEmployee() {
EmployeeId =p.EmployeeID,
Name =p.empName,
Address =p.empAddress,
BirthPlace =p.empBirthPlace,
BirthDate =p.empBirthDate
};
return query.FirstOrDefault();
}
}
public IList<PayrollAllowance> LoadAllowanceForEmployee(string employeeid, string periodid)
{
using (var db = new pisidataContext())
{
var query = from pa in db.tblPayAllows
join a in db.tblAllowances on pa.AllowanceID equals a.AllowanceID into joinedpayalloAndAllow
where pa.EmployeeID.Equals(employeeid) && pa.PayrollID.Equals(periodid)
from pallow in joinedpayalloAndAllow.DefaultIfEmpty()
select new
{
AllowanceID = pa.AllowanceID,
PayrollID = pa.PayrollID,
AllowanceName = pallow.aloName,
CurrencyID = pa.CurrencyID,
EmployeeID = pa.EmployeeID,
ExcRate =pa.palExcRate,
Normal = pa.palNormal,
Rate = pa.palRate,
Taxable = pa.palTaxAble
};
var list = query.OrderBy(x => x.AllowanceID).ToList();
IList<PayrollAllowance> resultList = new List<PayrollAllowance>();
foreach (var a in list)
{
resultList.Add(new PayrollAllowance()
{
AllowanceID = a.AllowanceID,
PayrollID = a.PayrollID,
AllowanceName = ToString(a.AllowanceName),
CurrencyID = ToString(a.CurrencyID),
EmployeeID = a.EmployeeID,
ExcRate = ToDecimal(a.ExcRate),
Normal = ToDecimal(a.Normal),
Rate = ToDecimal(a.Rate),
Taxable = ToBool(a.Taxable)
});
}
return resultList;
}
}
public IList<PayrollDeduction> LoadDeductionForEmployee(string employeeid, string periodid)
{
using (var db = new pisidataContext())
{
var query = from pd in db.tblPayDedus
join d in db.tblDeductions on pd.DeductionID equals d.DeductionID into joinedPayDeduAndDedu
where pd.EmployeeID.Equals(employeeid) && pd.PayrollID.Equals(periodid)
from dedu in joinedPayDeduAndDedu.DefaultIfEmpty()
select new
{
pd.DeductionID,
pd.PayrollID,
DeductionName = dedu.dedName,
pd.CurrencyID,
pd.EmployeeID,
ExcRate = pd.padExcRate,
Normal = pd.padNormal,
Rate = pd.padRate,
Taxable = pd.padTaxable
};
var list = query.OrderBy(x => x.DeductionID).ToList();
IList<PayrollDeduction> resultList = new List<PayrollDeduction>();
foreach (var a in list)
{
resultList.Add(new PayrollDeduction()
{
DeductionID = a.DeductionID,
PayrollID = a.PayrollID,
DeductionName = ToString(a.DeductionName),
CurrencyID = ToString(a.CurrencyID),
EmployeeID = a.EmployeeID,
ExcRate = ToDecimal(a.ExcRate),
Normal = ToDecimal(a.Normal),
Rate = ToDecimal(a.Rate),
Taxable = ToBool(a.Taxable)
});
};
return resultList;
}
}
public IList<PayslipPeriod> LoadPayslipPeriodForEmployee(string employeeid)
{
using (var db = new pisidataContext())
{
var query =from p in db.tblPayrolls
join m in db.tblMonths on p.PayrollID equals m.MonthID
where p.EmployeeID.Equals(employeeid)
select new PayslipPeriod() { Code = m.MonthID , Name = m.monName };
return query.OrderByDescending(x=>x.Code).ToList();
}
}
public Payroll LoadPayrollForEmployee(string employeeid, string periodid)
{
using (var db = new pisidataContext())
{
var query = from p in db.tblPayrolls
where p.EmployeeID.Equals(employeeid) && p.PayrollID.Equals(periodid)
select p;
return assemblePayroll(query.FirstOrDefault());
}
}
