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);
            }
        }
예제 #2
0
        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);
            }
        }