public List <EmployeeSalaryIncrementReportModel> GetReportData(DateTime?DateFrom, DateTime?DateTo, int?DepartmentID, int?DesignationID, int?LocationID, int?EmployementTypeID)
{
using (dbVisionEntities db = new dbVisionEntities())
{
var ReportData = (from sl in db.tblEmployeeSalaryIncrements
join r in db.tblEmployees on sl.EmployeeID equals r.EmployeeID
join pe in db.tblEmployeeServiceDetails on r.EmployeeLastServiceDetailID equals pe.EmployeeServiceDetailID into PE
from ep in PE.DefaultIfEmpty()
join np in db.tblEmployeeNoPrefixes on r.EmployeeNoPrefixID equals np.EmployeeNoPrefixID into NP
from pn in NP.DefaultIfEmpty()
join qe in db.tblEmployeeDesignations on ep.EmployeeDesignationID equals qe.EmployeeDesignationID into QE
from eq in QE.DefaultIfEmpty()
join lc in db.tblLocations on ep.LocationID equals lc.LocationID into LC
from L in LC.DefaultIfEmpty()
join jdep in db.tblEmployeeDepartments on ep.EmployeeDepartmentID equals jdep.EmployeeDepartmentID into gdep
from dep in gdep.DefaultIfEmpty()
where ((DateFrom == null || sl.EmployeeSalaryIncrementDate <= DateTo) &&
(DateTo == null || sl.EmployeeSalaryIncrementDate == null || sl.EmployeeSalaryIncrementDate >= DateFrom)) &&
r.CompanyID == Model.CommonProperties.LoginInfo.LoggedInCompany.CompanyID &&
(DesignationID == null || ep.EmployeeDesignationID == DesignationID.Value) &&
(LocationID == null || ep.LocationID == LocationID.Value) &&
(DepartmentID == null || ep.EmployeeDepartmentID == DepartmentID.Value) &&
(EmployementTypeID == null || (ep != null && (ep.EmploymentType == EmployementTypeID || (EmployementTypeID == 3 && (ep.EmploymentType == 1 || ep.EmploymentType == 2))))) &&
r.rstate != 2
select new EmployeeSalaryIncrementReportModel()
{
EmployeeSalaryIncrementID = (sl != null ? sl.EmployeeSalaryIncrementID : 0),
EmployeeSalaryIncrementNo = (sl != null ? sl.EmployeeSalaryIncrementNo : 0),
EmployeeNoPrefix = (pn != null ? pn.EmployeeNoPrefixName : null),
PriviousBasicSalary = sl.LastIncAmount,
EmployeeNo = r.EmployeeNo,
EmployeeName = (r != null ? r.EmployeeFirstName + " " + r.EmployeeLastName : ""),
LastIncDate = sl.LastIncDate,
CurrentBasicSalary = (sl != null ? sl.CurrentBasicSalary : 0),
CurrentIncDate = sl.EmployeeSalaryIncrementDate,
NewBasicSalary = sl.NewBasicSalary,
IncrementAmount = (sl != null ? sl.IncrementAmount : 0),
IncrementPercentage = (sl != null ? sl.IncrementPercentage : 0),
Remarks = sl.Remarks,
Designation = eq.EmployeeDesignationName,
Department = dep.EmployeeDepartmentName,
Location = L.LocationName,
EmployementTypeID = (eEmploymentType)ep.EmploymentType,
}).ToList();
return(ReportData);
}
}
public List <EmployeeMinimumWagesReport> GetReportData(DateTime?DateFrom, DateTime?DateTo, int?DepartmentID, int?DesignationID, int?LocationID, int?EmployementTypeID)
{
using (dbVisionEntities db = new dbVisionEntities())
{
var Res = (from r in db.tblEmployees
join pe in db.tblEmployeeServiceDetails on r.EmployeeLastServiceDetailID equals pe.EmployeeServiceDetailID into PE
from ep in PE.DefaultIfEmpty()
join np in db.tblEmployeeNoPrefixes on r.EmployeeNoPrefixID equals np.EmployeeNoPrefixID into NP
from pn in NP.DefaultIfEmpty()
join qe in db.tblEmployeeDesignations on ep.EmployeeDesignationID equals qe.EmployeeDesignationID into QE
from eq in QE.DefaultIfEmpty()
join lc in db.tblLocations on ep.LocationID equals lc.LocationID into LC
from L in LC.DefaultIfEmpty()
join mw in db.tblMinimumWageCategories on ep.MinimumWageCategoryID equals mw.MinimumWageCategoryID into MW
from M in MW.DefaultIfEmpty()
where ((DateFrom == null || ep.EmploymentEffectiveDate <= DateTo) &&
(DateTo == null || ep.TerminationDate == null || ep.TerminationDate >= DateFrom)) &&
r.CompanyID == Model.CommonProperties.LoginInfo.LoggedInCompany.CompanyID &&
(DesignationID == null || ep.EmployeeDesignationID == DesignationID.Value) &&
(LocationID == null || ep.LocationID == LocationID.Value) &&
(DepartmentID == null || ep.EmployeeDepartmentID == DepartmentID.Value) &&
(EmployementTypeID == 3 ? (EmployementTypeID == null || ep.EmploymentType != 0) : (EmployementTypeID == null || ep.EmploymentType == EmployementTypeID.Value)) &&
r.rstate != (int)eRecordState.Deleted
select new EmployeeMinimumWagesReport()
{
EmployeeID = r.EmployeeID,
EmployeeNoPrefix = (pn != null ? pn.EmployeeNoPrefixName : null),
EmployeeNo = r.EmployeeNo,
EmployementTypeID = (eEmploymentType)ep.EmploymentType,
EmployeeName = r.EmployeeFirstName + " " + r.EmployeeLastName,
Designation = eq.EmployeeDesignationName,
Category = M.MinimumWageCategoryName,
Location = L.LocationName,
LocationTypeID = (eLocationType)L.LocationTypeID,
BasicSalary = ep.BasicSalary,
MinWagesSal = ((from d in db.tblMinimumWageRates
where d.MinimumWageCategoryID == ep.MinimumWageCategoryID &&
((DateFrom == null || d.WEDateFrom >= DateFrom) && (d.WEDateTo == null))
orderby d.MinimumWageRateID
select new
{
minsale = (eLocationType)L.LocationTypeID == eLocationType.Rural ? d.MinimumWageCategoryRuralRate : d.MinimumWageCategoryUrbanRate
}).FirstOrDefault() == null ?
0
:
(from d in db.tblMinimumWageRates
where d.MinimumWageCategoryID == ep.MinimumWageCategoryID &&
((DateFrom == null || d.WEDateFrom >= DateFrom) && (d.WEDateTo == null))
orderby d.MinimumWageRateID
select new
{
minsale = (eLocationType)L.LocationTypeID == eLocationType.Rural ? d.MinimumWageCategoryRuralRate : d.MinimumWageCategoryUrbanRate
}).FirstOrDefault().minsale),
}).ToList();
return(Res);
}
}