public tblPayrollEmployeeDetail FindPayrollEmployeeDetail(int Month, int Year, int EmployeeID)
{
using (dbVisionEntities db = new dbVisionEntities())
{
var res = (from r in db.tblPayrollEmployeeDetails
join p in db.tblPayrolls on r.PayrollID equals p.PayrollID
where p.Month == Month && p.Year == Year && r.EmployeeID == EmployeeID
select r).FirstOrDefault();
if (res == null)
{
return(res);
}
res.tblPayroll = res.tblPayroll;
res.tblPayrollEmployeeEarningsDeductions = res.tblPayrollEmployeeEarningsDeductions;
res.tblPayrollEmployeeNonCashBenefits = res.tblPayrollEmployeeNonCashBenefits;
res.tblPayrollEmployePAYEReliefs = res.tblPayrollEmployePAYEReliefs;
return(res);
}
}
public List <EmployeeNoPrefixLookupListModel> GetLookupList()
{
using (dbVisionEntities db = new dbVisionEntities())
{
byte RecordState_Deleted = (byte)eRecordState.Deleted;
return((from EmployeeNoPrefix in db.tblEmployeeNoPrefixes
where EmployeeNoPrefix.rstate != RecordState_Deleted &&
EmployeeNoPrefix.CompanyID == CommonProperties.LoginInfo.LoggedInCompany.CompanyID
orderby EmployeeNoPrefix.EmployeeNoPrefixName
select new EmployeeNoPrefixLookupListModel()
{
EmployeeNoPrefixID = EmployeeNoPrefix.EmployeeNoPrefixID,
EmployeeNoPrefixName = EmployeeNoPrefix.EmployeeNoPrefixName,
}).ToList());
}
}
public string GetLayout(eGridControlIDs GridID, long CompanyID, long FinPerID)
{
using (dbVisionEntities db = new dbVisionEntities())
{
tblGridLayout SaveModel = db.tblGridLayouts.FirstOrDefault(r => r.GridID == (int)GridID && r.CompanyID == CompanyID && r.FinPerID == FinPerID);
if (SaveModel == null)
{
SaveModel = db.tblGridLayouts.Where(r => r.GridID == (int)GridID && r.CompanyID == CompanyID).OrderByDescending(r => r.FinPerID).FirstOrDefault();
}
if (SaveModel != null)
{
return(SaveModel.Layout);
}
else
{
return(null);
}
}
}
public SavingResult DeleteRecord(long DeleteID)
{
SavingResult res = new SavingResult();
using (dbVisionEntities db = new dbVisionEntities())
{
if (DeleteID != 0)
{
tblUserGroup RecordToDelete = db.tblUserGroups.FirstOrDefault(r => r.UserGroupID == DeleteID);
if (RecordToDelete == null)
{
res.ValidationError = "Selected record not found. May be it has been deleted by another User over network.";
res.ExecutionResult = eExecutionResult.ValidationError;
return(res);
}
else
{
//db.tblUserGroupPerimissions.RemoveRange(db.tblUserGroupPerimissions.Where(r => r.UserGroupID == RecordToDelete.UserGroupID));
//db.tblUserGroups.Remove(RecordToDelete);
byte RecordState_Deleted = (byte)eRecordState.Deleted;
RecordToDelete.rstate = RecordState_Deleted;
db.tblUserGroups.Attach(RecordToDelete);
db.Entry(RecordToDelete).State = System.Data.Entity.EntityState.Modified;
}
try
{
db.SaveChanges();
res.ExecutionResult = eExecutionResult.CommitedSucessfuly;
}
catch (Exception ex)
{
CommonFunctions.GetFinalError(res, ex);
}
}
}
return(res);
}
public SavingResult SaveNewRecord(tblEmployeeSalaryIncrement SaveModel)
{
SavingResult res = new SavingResult();
using (dbVisionEntities db = new dbVisionEntities())
{
if (SaveModel.EmployeeSalaryIncrementID == 0)
{
SaveModel.rcuid = Model.CommonProperties.LoginInfo.LoggedinUser.UserID;
SaveModel.rcdt = DateTime.Now;
SaveModel.CompanyID = Model.CommonProperties.LoginInfo.LoggedInCompany.CompanyID;
SaveModel.FinPeriodID = CommonProperties.LoginInfo.LoggedInFinPeriod.FinPeriodID;
db.tblEmployeeSalaryIncrements.Add(SaveModel);
}
else
{
SaveModel.reuid = Model.CommonProperties.LoginInfo.LoggedinUser.UserID;
SaveModel.redt = DateTime.Now;
db.tblEmployeeSalaryIncrements.Attach(SaveModel);
db.Entry(SaveModel).State = System.Data.Entity.EntityState.Modified;
}
var tblService = db.tblEmployeeServiceDetails.Where(p => p.EmployeeID == SaveModel.EmployeeID).FirstOrDefault();
if (tblService != null)
{
tblService.BasicSalary = SaveModel.NewBasicSalary;
}
try
{
db.SaveChanges();
res.PrimeKeyValue = SaveModel.EmployeeSalaryIncrementID;
res.ExecutionResult = eExecutionResult.CommitedSucessfuly;
}
catch (Exception ex)
{
CommonFunctions.GetFinalError(res, ex);
}
}
return(res);
}
public BeforeDeleteValidationResult ValidateBeforeDelete(long DeleteID)
{
BeforeDeleteValidationResult Result = new BeforeDeleteValidationResult();
using (dbVisionEntities db = new dbVisionEntities())
{
var DeleteRecord = db.tblUsers.Find(DeleteID);
if (DeleteRecord != null)
{
if (DeleteRecord.SuperAdmin)
{
Result.IsValidForDelete = false;
Result.ValidationMessage = "Can not delete super admin record.";
return(Result);
}
}
}
Result.IsValidForDelete = true;
return(Result);
}
public List <HolidayLookupListModel> GetLookupList()
{
using (dbVisionEntities db = new dbVisionEntities())
{
byte RecordState_Deleted = (byte)eRecordState.Deleted;
return((from r in db.tblHolidays
where r.rstate != RecordState_Deleted &&
r.CompanyID == CommonProperties.LoginInfo.LoggedInCompany.CompanyID
orderby r.HolidayName
select new HolidayLookupListModel()
{
HolidayID = r.HolidayID,
HolidayName = r.HolidayName,
Type = ((eHolidayType)r.HolidayType == eHolidayType.PublicHoliday ? "Public" : "Optional"),
DateFrom = r.FromDate,
DateTo = r.ToDate,
}).ToList());
}
}
public List <EmployeeRestDayEditDetailModel> GetRestDayDetails(DateTime DateFrom, DateTime DateTo)
{
using (dbVisionEntities db = new dbVisionEntities())
{
return((from r2 in (from r in db.tblEmployeeRestDays
where r.RestDate >= DateFrom && r.RestDate <= DateTo &&
r.FinPeriodID == CommonProperties.LoginInfo.LoggedInFinPeriod.FinPeriodID &&
r.CompanyID == CommonProperties.LoginInfo.LoggedInCompany.CompanyID
select new
{
EmployeeID = r.EmployeeID,
RestDate = r.RestDate,
}).ToList()
group r2 by new { r2.EmployeeID, r2.RestDate.DayOfWeek } into gr2
select new EmployeeRestDayEditDetailModel()
{
EmployeeID = gr2.Key.EmployeeID,
RestDay = gr2.Key.DayOfWeek
}).ToList());
}
}
public List <LeaveTypeLookupListModel> GetLookupList()
{
using (dbVisionEntities db = new dbVisionEntities())
{
byte RecordState_Deleted = (byte)eRecordState.Deleted;
return((from r in db.tblLeaveTypes
where r.rstate != RecordState_Deleted &&
r.CompanyID == CommonProperties.LoginInfo.LoggedInCompany.CompanyID
orderby r.LeaveTypeName
select new LeaveTypeLookupListModel()
{
LeaveTypeID = r.LeaveTypeID,
LeaveTypeName = r.LeaveTypeName,
ApplicableTo = r.ApplicableTo,
Encashable = r.IsEncashable,
}).ToList());
}
}
public static CompanyDetailViewModel GetFirstCompany(int UserGroupID)
{
using (dbVisionEntities db = new dbVisionEntities())
{
byte RecordState_Deleted = (byte)eRecordState.Deleted;
tblCompany company = (from r in db.tblCompanies
join p in db.tblUserGroupPermissionOnCompanies on new { r.CompanyID, UserGroupID } equals new { p.CompanyID, p.UserGroupID }
where r.rstate != RecordState_Deleted
select r).FirstOrDefault();
if (company == null)
{
return(null);
}
else
{
return(ConvertToDetailViewModel(company));
}
}
}
public List <Model.Account.BankAccountEditListModel> GetEditList()
{
using (dbVisionEntities db = new dbVisionEntities())
{
byte RecordState_Deleted = (byte)eRecordState.Deleted;
byte AccountType_BankAccount = (byte)Model.Account.eAccountType.BankAccount;
return((from ac in db.tblAccounts
join bank in db.tblBankNames on ac.BankNameID equals bank.BankNameID
join branch in db.tblBankBranches on ac.BranchID equals branch.BankBranchID
join joinrcu in db.tblUsers on ac.rcuid equals joinrcu.UserID into grcu
from rcu in grcu.DefaultIfEmpty()
join joinreu in db.tblUsers on ac.reuid equals joinreu.UserID into greu
from reu in greu.DefaultIfEmpty()
where ac.rstate != RecordState_Deleted && ac.AccountType == AccountType_BankAccount &&
ac.CompanyID == CommonProperties.LoginInfo.LoggedInCompany.CompanyID
orderby ac.AccountName
select new Model.Account.BankAccountEditListModel()
{
BankAccountID = ac.AccountID,
BankAccountName = ac.AccountName,
BankAccountNo = ac.BankAccountNo,
BankName = bank.BankName,
BranchName = branch.BankBranchName,
RecordState = (eRecordState)ac.rstate,
CreatedDateTime = ac.rcdt,
EditedDateTime = ac.redt,
CreatedUserID = ac.rcuid,
EditedUserID = ac.reuid,
CreatedUserName = (rcu != null ? rcu.UserName : ""),
EditedUserName = (reu != null ? reu.UserName : ""),
}).ToList());
}
}
public List <EmployeeAttendanceHeaderReportModel> GetReportData(int?EmployeeID, DateTime?DateFrom, DateTime?DateTo)
{
using (dbVisionEntities db = new dbVisionEntities())
{
var res = (from r in db.tblEmployeeAttendances
join je in db.tblEmployees on r.EmployeeID equals je.EmployeeID into ge
from e in ge.DefaultIfEmpty()
join jep in db.tblEmployeeNoPrefixes on e.EmployeeNoPrefixID equals jep.EmployeeNoPrefixID into gep
from ep in gep.DefaultIfEmpty()
where (EmployeeID == null || r.EmployeeID == EmployeeID) &&
((DateFrom == null || r.AttendanceDate >= DateFrom) && (DateTo == null || r.AttendanceDate <= DateTo)) &&
r.CompanyID == Model.CommonProperties.LoginInfo.LoggedInCompany.CompanyID
select new EmployeeAttendanceHeaderReportModel()
{
EmployeeID = r.EmployeeID,
EmployeeNoPrefix = (ep != null ? ep.EmployeeNoPrefixName : null),
EmployeeNo = e.EmployeeNo,
EmployeeName = (e != null ? e.EmployeeFirstName + " " + e.EmployeeLastName: null),
EmployeeTACode = (e != null ? e.TACode : 0),
AttendanceDate = r.AttendanceDate,
InTime = r.InTime,
OutTime = r.OutTime,
Detail = (from d in db.tblEmployeeAttendanceDetails
where d.EmployeeAttendanceID == r.EmployeeAttendanceID
orderby d.EmployeeAttendanceDetailID
select new EmployeeAttendanceDetailReportModel()
{
AttendanceStatus = ((Model.BMDevice.eLogTimeType)d.LogTimeType == Model.BMDevice.eLogTimeType.In ? "In" :
(Model.BMDevice.eLogTimeType)d.LogTimeType == Model.BMDevice.eLogTimeType.Out ? "Out" : "Unknown"),
LogTime = d.LogTime
}).ToList()
}).ToList();
return(res);
}
}
public UserDetailModel GetUserDetailModelByEmailID(string UserName, string EmailID)
{
using (dbVisionEntities db = new dbVisionEntities())
{
UserName = UserName.ToLower();
EmailID = EmailID.ToLower();
return((from r in db.tblUsers
join jug in db.tblUserGroups on r.UserGroupID equals jug.UserGroupID into gug
from ug in gug.DefaultIfEmpty()
where r.UserName.ToLower() == UserName && r.EMailID.ToLower() == EmailID
select
new UserDetailModel()
{
UserID = r.UserID,
UserName = r.UserName,
UserGroupID = r.UserGroupID,
UserGroupName = (ug != null ? ug.UserGroupName : ""),
SuperUser = r.SuperAdmin,
EMailID = r.EMailID
}).FirstOrDefault());
}
}
public List <Model.City.CityEditListModel> GetEditList()
{
using (dbVisionEntities db = new dbVisionEntities())
{
byte RecordState_Deleted = (byte)eRecordState.Deleted;
return((from city in db.tblCities
join jstate in db.tblStates on city.StateID equals jstate.StateID into gstate
from state in gstate.DefaultIfEmpty()
join jcountry in db.tblCountries on city.CountryID equals jcountry.CountryID into gcountry
from country in gcountry.DefaultIfEmpty()
join joinrcu in db.tblUsers on city.rcuid equals joinrcu.UserID into grcu
from rcu in grcu.DefaultIfEmpty()
join joinreu in db.tblUsers on city.reuid equals joinreu.UserID into greu
from reu in greu.DefaultIfEmpty()
where city.rstate != RecordState_Deleted
orderby city.CityName
select new Model.City.CityEditListModel()
{
CityID = city.CityID,
CityName = city.CityName,
StateID = city.StateID ?? 0,
StateName = (state != null ? state.StateName : ""),
CountryName = (country != null ? country.CountryName : ""),
RecordState = (eRecordState)city.rstate,
CreatedDateTime = city.rcdt,
EditedDateTime = city.redt,
CreatedUserID = city.rcuid,
EditedUserID = city.reuid,
CreatedUserName = (rcu != null ? rcu.UserName : ""),
EditedUserName = (reu != null ? reu.UserName : ""),
}).ToList());
}
}
public List <EarlyGoingReportModel> GetReportData(DateTime?DateFrom, DateTime?DateTo)
{
using (dbVisionEntities db = new dbVisionEntities())
{
TimeSpan ShiftEnd = new TimeSpan(17, 0, 0);
byte AttendanceType_IntegratedID = (byte)Model.Employee.eTAAttendanceType.Integrated;
return((from r in db.tblEmployeeAttendances
join je in db.tblEmployees on r.EmployeeID equals je.EmployeeID into ge
from e in ge.DefaultIfEmpty()
where r.CompanyID == Model.CommonProperties.LoginInfo.LoggedInCompany.CompanyID &&
(e == null || e.TAAttendanceType == AttendanceType_IntegratedID) &&
(DateFrom == null || r.AttendanceDate >= DateFrom) &&
(DateTo == null || r.AttendanceDate <= DateTo) &&
r.OutTime != null && r.OutTime < ShiftEnd
select new EarlyGoingReportModel()
{
EmployeeID = r.EmployeeID ?? 0,
AttendanceDate = r.AttendanceDate,
TimeOut = r.OutTime,
}).ToList());
}
}
public SavingResult DeleteRecord(int DeleteID)
{
SavingResult res = new SavingResult();
using (dbVisionEntities db = new dbVisionEntities())
{
if (DeleteID != 0)
{
tblEmployeeDesignation RecordToDelete = db.tblEmployeeDesignations.Find(DeleteID);
if (RecordToDelete == null)
{
res.ValidationError = "Selected record not found. May be it has been deleted by another user over network.";
res.ExecutionResult = eExecutionResult.ValidationError;
return(res);
}
else
{
byte RecordState_Deleted = (byte)eRecordState.Deleted;
//db.tblEmployeeDesignations.Remove(RecordToDelete);
RecordToDelete.rstate = RecordState_Deleted;
db.tblEmployeeDesignations.Attach(RecordToDelete);
db.Entry(RecordToDelete).State = System.Data.Entity.EntityState.Modified;
}
try
{
db.SaveChanges();
res.ExecutionResult = eExecutionResult.CommitedSucessfuly;
}
catch (Exception ex)
{
CommonFunctions.GetFinalError(res, ex);
}
}
}
return(res);
}
public EmployeeSalaryIncrementEditListModel GetEmployeeDetail(int EmployeeID)
{
using (dbVisionEntities db = new dbVisionEntities())
{
var dataList = (from r in db.tblEmployees
join je in db.tblEmployeeServiceDetails on r.EmployeeLastServiceDetailID equals je.EmployeeServiceDetailID into ge
from e in ge.DefaultIfEmpty()
where r.EmployeeID == EmployeeID &&
r.CompanyID == CommonProperties.LoginInfo.LoggedInCompany.CompanyID
select new EmployeeSalaryIncrementEditListModel()
{
EmployeeID = r.EmployeeID,
CurrentBasicSalary = (e != null ? e.BasicSalary : 0),
LastIncAmount = (db.tblEmployeeSalaryIncrements.Where(x => x.EmployeeID == r.EmployeeID).OrderByDescending(x => x.EmployeeSalaryIncrementID).Select(x => x.IncrementAmount).FirstOrDefault()),
LastIncDate = (db.tblEmployeeSalaryIncrements.Where(x => x.EmployeeID == r.EmployeeID).Count() > 0 ? db.tblEmployeeSalaryIncrements.Where(x => x.EmployeeID == r.EmployeeID).OrderByDescending(x => x.EmployeeSalaryIncrementID).Select(x => x.EmployeeSalaryIncrementDate).FirstOrDefault() : (DateTime?)null),
}).FirstOrDefault();
return(dataList);
}
}
public List <TAApprovalLookupListModel> GetLookupList()
{
using (dbVisionEntities db = new dbVisionEntities())
{
byte RecordState_Deleted = (byte)eRecordState.Deleted;
return((from r in db.tblTAApprovals
join jp in db.tblTAApprovalNoPrefixes on r.TAApprovalNoPrefixID equals jp.TAApprovalNoPrefixID into gp
from p in gp.DefaultIfEmpty()
join je in db.tblEmployees on r.EmployeeID equals je.EmployeeID into ge
from e in ge.DefaultIfEmpty()
join jep in db.tblEmployeeNoPrefixes on e.EmployeeNoPrefixID equals jep.EmployeeNoPrefixID into gep
from ep in gep.DefaultIfEmpty()
where r.rstate != RecordState_Deleted &&
r.CompanyID == CommonProperties.LoginInfo.LoggedInCompany.CompanyID &&
r.FinPeriodID == CommonProperties.LoginInfo.LoggedInFinPeriod.FinPeriodID
orderby(e != null ? e.EmployeeNo : 0), r.ApprovedDate, r.TAApprovalNo
select new TAApprovalLookupListModel()
{
TAApprovalID = r.TAApprovalID,
TAApprovalNoPrefixName = (p != null ? p.TAApprovalNoPrefixName : null),
TAApprovalNo = r.TAApprovalNo,
TAApprovalDate = r.TAApprovalDate,
ApprovalTypeID = (eTAApprovalType)r.ApprovalTypeID,
ApprovedDate = r.ApprovedDate,
ApprovedHours = r.ApprovedHours,
EmployeeNoPrefix = (ep != null ? ep.EmployeeNoPrefixName : null),
EmployeeNo = (e != null ? e.EmployeeNo : 0),
EmployeeName = (e != null ? e.EmployeeFirstName + " " + e.EmployeeLastName : ""),
}).ToList());
}
}
public SavingResult UnAuthorize(long ID)
{
SavingResult res = new SavingResult();
using (dbVisionEntities db = new dbVisionEntities())
{
var rec = db.tblEmployeeSalaryIncrements.FirstOrDefault(r => r.EmployeeSalaryIncrementID == ID);
rec.rState = (byte)eRecordState.Active;
db.tblEmployeeSalaryIncrements.Attach(rec);
db.Entry(rec).State = System.Data.Entity.EntityState.Modified;
try
{
db.SaveChanges();
res.ExecutionResult = eExecutionResult.CommitedSucessfuly;
}
catch (Exception ex)
{
CommonFunctions.GetFinalError(res, ex);
}
}
return(res);
}
public List <LeaveApplicationLookupListModel> GetLookupList()
{
using (dbVisionEntities db = new dbVisionEntities())
{
byte RecordState_Deleted = (byte)eRecordState.Deleted;
return((from r in db.tblLeaveApplications
join jp in db.tblLeaveApplicationNoPrefixes on r.LeaveApplicationNoPrefixID equals jp.LeaveApplicationNoPrefixID into gp
from p in gp.DefaultIfEmpty()
join jlt in db.tblLeaveTypes on r.LeaveTypeID equals jlt.LeaveTypeID into glt
from lt in glt.DefaultIfEmpty()
join je in db.tblEmployees on r.EmployeeID equals je.EmployeeID into ge
from e in ge.DefaultIfEmpty()
join jep in db.tblEmployeeNoPrefixes on e.EmployeeNoPrefixID equals jep.EmployeeNoPrefixID into gep
from ep in gep.DefaultIfEmpty()
where r.rstate != RecordState_Deleted &&
r.CompanyID == CommonProperties.LoginInfo.LoggedInCompany.CompanyID &&
r.FinPeriodID == CommonProperties.LoginInfo.LoggedInFinPeriod.FinPeriodID
orderby r.FromDate descending, r.LeaveApplicationNo descending
select new LeaveApplicationLookupListModel()
{
LeaveApplicationID = r.LeaveApplicationID,
LeaveApplicationNoPrefixName = (p != null ? p.LeaveApplicationNoPrefixName : null),
LeaveApplicationNo = r.LeaveApplicationNo,
LeaveApplicateDate = r.LeaveApplicationDate,
LeaveFrom = r.FromDate,
LeaveTo = r.ToDate,
EmployeeNoPrefix = (ep != null ? ep.EmployeeNoPrefixName : null),
EmployeeNo = (e != null ? e.EmployeeNo : 0),
EmployeeName = (e != null ? e.EmployeeFirstName + " " + e.EmployeeLastName : ""),
LeaveType = (lt != null ? lt.LeaveTypeName : null)
}).ToList());
}
}
public EmployeeSalaryIncrementEditListModel GetEditList(long EmployeeIncrementID)
{
using (dbVisionEntities db = new dbVisionEntities())
{
try
{
var EmpList = (from r in db.tblEmployeeSalaryIncrements
where r.EmployeeSalaryIncrementID == EmployeeIncrementID
&& r.CompanyID == CommonProperties.LoginInfo.LoggedInCompany.CompanyID &&
r.FinPeriodID == CommonProperties.LoginInfo.LoggedInFinPeriod.FinPeriodID
select new EmployeeSalaryIncrementEditListModel
{
EmployeeSalaryIncrementID = r.EmployeeSalaryIncrementID,
EmployeeSalaryIncrementNo = r.EmployeeSalaryIncrementNo,
EffectiveMonth = r.EffectiveMonth,
EffectiveYear = r.EffectiveYear,
EmployeeID = r.EmployeeID,
EmployeeName = (db.tblEmployees.Where(x => x.EmployeeID == r.EmployeeID).Select(x => x.EmployeeFirstName + " " + x.EmployeeLastName).FirstOrDefault()),
CurrentBasicSalary = (db.tblEmployeeServiceDetails.Where(x => x.EmployeeID == r.EmployeeID).Select(x => x.BasicSalary).FirstOrDefault()),
IncrementPercentage = r.IncrementPercentage,
IncrementAmount = r.IncrementAmount,
NewBasicSalary = r.NewBasicSalary,
LastIncDate = (db.tblEmployeeSalaryIncrements.Where(x => x.EmployeeID == r.EmployeeID).OrderByDescending(x => x.EmployeeSalaryIncrementID).Select(x => x.EmployeeSalaryIncrementDate).FirstOrDefault()),
EmployeeSalaryIncrementDate = r.EmployeeSalaryIncrementDate,
LastIncAmount = r.LastIncAmount,
Remarks = r.Remarks,
IncrementType = (eEmployeeSalaryIncrementType)r.IncrementType
}).FirstOrDefault();
return(EmpList);
}
catch (Exception ex)
{
throw ex;
}
}
}
public SavingResult DeleteRecord(long DeleteID)
{
SavingResult res = null;
using (dbVisionEntities db = new dbVisionEntities())
{
res = DeleteRecord(DeleteID, db);
if (res.ExecutionResult == eExecutionResult.ValidationError)
{
return(res);
}
try
{
db.SaveChanges();
res.ExecutionResult = eExecutionResult.CommitedSucessfuly;
}
catch (Exception ex)
{
CommonFunctions.GetFinalError(res, ex);
}
}
return(res);
}
public List <DailyAttendanceSummaryReportModel> GenerateReportData(DateTime DateFrom, DateTime DateTo, int?DepartmentID, int?DesignationID, int?LocationID, int?EmployementTypeID)
{
using (dbVisionEntities db = new dbVisionEntities())
{
var res = (new DAL.Payroll.EmployeeAttendanceDAL()).GetEmployeeAttendanceData(DateFrom, DateTo, DepartmentID, DesignationID, LocationID, EmployementTypeID);
var res1 = (from r in res
group r by r.Day into gr
select new DailyAttendanceSummaryReportModel()
{
AttendanceDate = gr.Key,
Present = gr.Sum(grr => (grr.Present == Model.Payroll.eEmployeeAttendanceDayType.FullDay ? 1M : (grr.Present == Model.Payroll.eEmployeeAttendanceDayType.HalfDay ? 0.5M : (grr.Weekend == Model.Payroll.eEmployeeWeekendDayType.WeekendWorked ? 1M : (grr.Holiday == Model.Payroll.eEmployeeHolidayDayType.HolidayWorked ? 1M : 0))))),
Absent = gr.Sum(grr => (grr.Absent == Model.Payroll.eEmployeeAttendanceDayType.FullDay ? 1M : (grr.Absent == Model.Payroll.eEmployeeAttendanceDayType.HalfDay ? 0.5M : 0))),
Leave = gr.Sum(grr => (grr.Leave == Model.Payroll.eEmployeeAttendanceDayType.FullDay ? 1M : (grr.Leave == Model.Payroll.eEmployeeAttendanceDayType.HalfDay ? 0.5M : 0))),
Safari = gr.Sum(grr => (grr.Safari != Model.Payroll.eEmployeeAttendanceDayType.None ? 1 : 0)),
LateIn = gr.Sum(grr => (grr.LateIn ? 1 : 0)),
EarlyGoing = gr.Sum(grr => (grr.EarlyGoing ? 1 : 0)),
MissedPunch = gr.Sum(grr => (grr.MissedPunch ? 1 : 0)),
});
return(res1.ToList());
}
}
public void SaveNewRecord(IEnumerable <TAApprovalImportViewModel> ImportViewModels)
{
using (dbVisionEntities db = new dbVisionEntities())
{
foreach (var viewModel in ImportViewModels)
{
tblTAApproval SaveModel = new tblTAApproval()
{
ApprovedDate = viewModel.ApprovedDate.Value,
TAApprovalNoPrefixID = viewModel.ApprovalNoPrefixID.Value,
TAApprovalNo = viewModel.ApprovalNo.Value,
EmployeeID = viewModel.EmployeeID.Value,
TAApprovalDate = viewModel.ApprovalDate.Value,
ApprovalTypeID = (byte)viewModel.ApprovalTypeID.Value,
ApprovedHours = viewModel.ApprovedHours.Value,
Remarks = viewModel.Remark,
};
SavingResult res = SaveNewRecord(SaveModel, db);
if (res.ExecutionResult != eExecutionResult.ErrorWhileExecuting && res.ExecutionResult != eExecutionResult.ValidationError)
{
try
{
db.SaveChanges();
res.PrimeKeyValue = SaveModel.TAApprovalID;
viewModel.TAApprovalID = SaveModel.TAApprovalID;
res.ExecutionResult = eExecutionResult.CommitedSucessfuly;
}
catch (Exception ex)
{
CommonFunctions.GetFinalError(res, ex);
}
}
viewModel.SavingResult = res;
}
}
}
public List <EmployeeServiceDetailGridViewModel> GetEmployeeServiceDetail(int EmployeeID)
{
using (dbVisionEntities db = new dbVisionEntities())
{
return((from esd in db.tblEmployeeServiceDetails
join jdep in db.tblEmployeeDepartments on esd.EmployeeDepartmentID equals jdep.EmployeeDepartmentID into gdep
from dep in gdep
join jdes in db.tblEmployeeDesignations on esd.EmployeeDesignationID equals jdes.EmployeeDesignationID into gdes
from des in gdes
join jloc in db.tblLocations on esd.LocationID equals jloc.LocationID into gloc
from loc in gloc
join jwcn in db.tblEmployeeWIBAClasses on esd.EmployeeWIBAClassID equals jwcn.EmployeeWIBAClassID into gwcn
from wcn in gwcn
join jmwc in db.tblMinimumWageCategories on esd.MinimumWageCategoryID equals jmwc.MinimumWageCategoryID into gmwc
from mwc in gmwc
where esd.EmployeeID == EmployeeID
orderby esd.EmploymentEffectiveDate descending
select new EmployeeServiceDetailGridViewModel()
{
EmployeeServiceDetailID = esd.EmployeeServiceDetailID,
EmploymentTypeID = (eEmploymentType)esd.EmploymentType,
EmploymentEffectiveDate = esd.EmploymentEffectiveDate,
ContractExpiryDate = esd.ContractExpiryDate,
BasicSalary = esd.BasicSalary,
DailyRate = esd.DailyRate,
WeekendAllowance = esd.WeekendAllowance,
HousingAllowance = esd.HousingAllowance,
EmployeeDepartmentName = (dep != null ? dep.EmployeeDepartmentName : ""),
EmployeeDesignationName = (des != null ? des.EmployeeDesignationName : ""),
LocationName = (loc != null ? loc.LocationName : ""),
EmployeeWIBAClassName = (wcn != null ? wcn.EmployeeWIBAClassName : ""),
MinimumWageCategoryName = (mwc != null ? mwc.MinimumWageCategoryName : ""),
}).ToList());
}
}
public List <EmployeeSalaryIncrementEditListModel> GetEmployeeList()
{
using (dbVisionEntities db = new dbVisionEntities())
{
byte RecordState_Deleted = (byte)eRecordState.Deleted;
var data = (from r in db.tblEmployeeSalaryIncrements
join je in db.tblEmployees on r.EmployeeID equals je.EmployeeID into ge
from e in ge.DefaultIfEmpty()
where r.rState != RecordState_Deleted &&
r.CompanyID == CommonProperties.LoginInfo.LoggedInCompany.CompanyID &&
r.FinPeriodID == CommonProperties.LoginInfo.LoggedInFinPeriod.FinPeriodID
orderby r.EmployeeSalaryIncrementNo
select new EmployeeSalaryIncrementEditListModel()
{
EmployeeSalaryIncrementID = r.EmployeeSalaryIncrementID,
EmployeeSalaryIncrementNo = r.EmployeeSalaryIncrementNo,
EmployeeSalaryIncrementDate = r.EmployeeSalaryIncrementDate,
EffectiveMonth = r.EffectiveMonth,
EffectiveYear = r.EffectiveYear,
EmployeeID = r.EmployeeID,
EmployeeNo = e.EmployeeNo,
EmployeeName = (e != null ? e.EmployeeFirstName + " " + e.EmployeeLastName : ""),
CurrentBasicSalary = r.CurrentBasicSalary,
IncrementType = (eEmployeeSalaryIncrementType)r.IncrementType,
IncrementPercentage = r.IncrementPercentage,
IncrementAmount = r.IncrementAmount,
NewBasicSalary = r.NewBasicSalary,
Remarks = r.Remarks,
LastIncDate = (db.tblEmployeeSalaryIncrements.Where(x => x.EmployeeID == r.EmployeeID).OrderByDescending(x => x.EmployeeSalaryIncrementID).Select(x => x.LastIncDate).FirstOrDefault()),
LastIncAmount = r.LastIncAmount
}).ToList();
return(data);
}
}
public List <Model.BMDevice.BiometricDeviceEditListModel> GetEditList()
{
using (dbVisionEntities db = new dbVisionEntities())
{
byte RecordState_Deleted = (byte)eRecordState.Deleted;
return((from r in db.tblBiometricDevices
join joinrcu in db.tblUsers on r.rcuid equals joinrcu.UserID into grcu
from rcu in grcu.DefaultIfEmpty()
join joinreu in db.tblUsers on r.reuid equals joinreu.UserID into greu
from reu in greu.DefaultIfEmpty()
where r.rstate != RecordState_Deleted && r.CompanyID == CommonProperties.LoginInfo.LoggedInCompany.CompanyID
orderby r.BMDTitle
select new Model.BMDevice.BiometricDeviceEditListModel()
{
BMDID = r.BMDID,
BMDTitle = r.BMDTitle,
IPAddress = r.IPAddress,
MachineNo = r.MachineNo,
Password = r.Password,
PortNo = r.PortNo,
RecordState = (eRecordState)r.rstate,
CreatedDateTime = r.rcdt,
EditedDateTime = r.redt,
CreatedUserID = r.rcuid,
EditedUserID = r.reuid,
CreatedUserName = (rcu != null ? rcu.UserName : ""),
EditedUserName = (reu != null ? reu.UserName : ""),
}).ToList());
}
}
public SavingResult Authorize(long ID)
{
SavingResult res = new SavingResult();
using (dbVisionEntities db = new dbVisionEntities())
{
var rec = db.tblTAApprovalNoPrefixes.FirstOrDefault(r => r.TAApprovalNoPrefixID == ID);
rec.rstate = (byte)eRecordState.Authorized;
db.tblTAApprovalNoPrefixes.Attach(rec);
db.Entry(rec).State = System.Data.Entity.EntityState.Modified;
//--
try
{
db.SaveChanges();
res.ExecutionResult = eExecutionResult.CommitedSucessfuly;
}
catch (Exception ex)
{
CommonFunctions.GetFinalError(res, ex);
}
}
return(res);
}
public SavingResult SaveNewRecord(tblTAApproval SaveModel, dbVisionEntities db)
{
SavingResult res = new SavingResult();
//tblTAApproval SaveModel;
if (SaveModel.TAApprovalNo == 0)
{
res.ValidationError = "Please enter T & A Approval No.";
res.ExecutionResult = eExecutionResult.ValidationError;
return(res);
}
else if (IsDuplicateRecord(SaveModel.TAApprovalNoPrefixID, SaveModel.TAApprovalNo, SaveModel.TAApprovalID, db))
{
res.ValidationError = "Can not accept duplicate value. The T & A Approval No. is already exists.";
res.ExecutionResult = eExecutionResult.ValidationError;
return(res);
}
if (SaveModel.TAApprovalID == 0) // New Entry
{
SaveModel.rcuid = Model.CommonProperties.LoginInfo.LoggedinUser.UserID;
SaveModel.rcdt = DateTime.Now;
SaveModel.CompanyID = Model.CommonProperties.LoginInfo.LoggedInCompany.CompanyID;
SaveModel.FinPeriodID = Model.CommonProperties.LoginInfo.LoggedInFinPeriod.FinPeriodID;
db.tblTAApprovals.Add(SaveModel);
}
else
{
SaveModel.reuid = Model.CommonProperties.LoginInfo.LoggedinUser.UserID;
SaveModel.redt = DateTime.Now;
db.tblTAApprovals.Attach(SaveModel);
db.Entry(SaveModel).State = System.Data.Entity.EntityState.Modified;
}
return(res);
}
public List <EmployeeRestDayViewModel> GetEmployeeList()
{
using (dbVisionEntities db = new dbVisionEntities())
{
byte RecordState_Deleted = (byte)eRecordState.Deleted;
byte WeekendType_RestDay = (byte)Model.Employee.eTAWeekendtype.RestDay;
var res = (from e in db.tblEmployees
join jenp in db.tblEmployeeNoPrefixes on e.EmployeeNoPrefixID equals jenp.EmployeeNoPrefixID into genp
from enp in genp.DefaultIfEmpty()
where e.rstate != RecordState_Deleted &&
e.TAWeekendType == WeekendType_RestDay &&
e.CompanyID == CommonProperties.LoginInfo.LoggedInCompany.CompanyID
orderby e.EmployeeNo
select new EmployeeRestDayViewModel()
{
EmployeeID = e.EmployeeID,
EmployeeNoPrefix = (enp != null ? enp.EmployeeNoPrefixName : null),
EmployeeNo = e.EmployeeNo,
EmployeeName = e.EmployeeFirstName + " " + e.EmployeeLastName,
}).ToList();
return(res);
}
}