private void PopulateGrid(List <Employee> employees) { int bonusId = Convert.ToInt32(comboBox1.SelectedValue); Bonus bonus = db.Bonus.Find(bonusId); DateTime To = bonus.Date; DateTime From = To.AddYears(-1); List <ViewBonus> viewBonus = new List <ViewBonus>(); foreach (Employee employee in employees) { ViewBonus view = new ViewBonus(); try { DateTime JoiningDate = Convert.ToDateTime(employee.HireDate); view.JoiningDate = JoiningDate; TimeSpan timeSpan = To.Subtract(JoiningDate); view.Year = timeSpan.TotalDays / 365; } catch { } view.CompanyPercentage = bonus.Amount; view.Emp_Id = employee.Emp_Id; view.Name = employee.EmpFullName; if (employee.IsWorker == true) { List <WorkerDesignationHistory> workerDesignationHistories = db.WorkerDesignationHistories.Where(a => a.EmployeeId == employee.Id && a.From <= To).ToList(); if (workerDesignationHistories.Count > 0) { DateTime maxDate = workerDesignationHistories.Max(a => a.From); WorkerDesignationHistory workerDesignationHistory = workerDesignationHistories.FirstOrDefault(a => a.From == maxDate); // Find Salary Grade At That Time DL.WorkerDesignation workerDesignation = db.WorkerDesignations.Find(workerDesignationHistory.WorkerDesignationId); List <ShadowSalaryGrade> shadowSalaryGrades = db.ShadowSalaryGrades.Where(a => a.UpdatedAt <= To && a.RoWId == workerDesignation.SalaryGrade.Id).ToList(); DateTime D2 = DateTime.Now; if (shadowSalaryGrades.Count > 0) { D2 = shadowSalaryGrades.Max(a => a.UpdatedAt); var shadowgrade = shadowSalaryGrades.FirstOrDefault(a => a.RoWId == workerDesignation.SalaryGrade.Id && a.UpdatedAt == D2); view.Grade = workerDesignation.Name; view.Gross = shadowgrade.Total; view.Basic = shadowgrade.Salary; } } } else { List <Salary> salaries = db.Salaries.Where(a => a.EmployeeId == employee.Id && a.Date <= To).ToList(); if (salaries.Count > 0) { DateTime maxDate = salaries.Max(a => a.Date); Salary salary = salaries.FirstOrDefault(a => a.Date == maxDate); //view.Grade = salary.; view.Gross = salary.Amount; List <SalarySetting> salarySettings = db.SalarySettings.Where(a => a.Date <= To).ToList(); if (salarySettings.Count > 0) { DateTime maxDate2 = salarySettings.Max(a => a.Date); SalarySetting settings = salarySettings.FirstOrDefault(a => a.Date == maxDate2); view.Basic = salary.Amount * (100 / settings.Basic); } } } //Count Employee attendance List <Emp_CheckInOut> emp_CheckInOuts = db.Emp_CheckInOuts.Where(a => a.UserId == employee.Emp_Id).ToList(); var DayCount = emp_CheckInOuts.Where(a => a.CHECKTIME >= From && a.CHECKTIME <= To).GroupBy(a => a.CHECKTIME.Date).ToList(); view.Days = DayCount.Count - 1; viewBonus.Add(view); } viewBonusDataGridView.DataSource = viewBonus.ToList(); // viewBonusDataGridView1.DataSource = viewBonus.ToList(); }
private void button1_Click(object sender, EventArgs e) { DateTime From = dateTimePicker1.Value; int id = Convert.ToInt32(comboBox4.SelectedValue); List <ViewSalarySheet> viewSalarySheets = new List <ViewSalarySheet>(); List <Employee> employeesALL = db.Employees.Where(a => a.SectionId == id).ToList(); List <Employee> employees = employeesALL.Where(a => a.IsActive(From)).ToList(); if (employees.Count > 0) { foreach (Employee emp in employees) { ViewSalarySheet viewSalarySheet = new ViewSalarySheet(); viewSalarySheet.Emp_Id = emp.Emp_Id; viewSalarySheet.Name = emp.EmpFullName; viewSalarySheet.Designation = emp.Designation.Name; viewSalarySheet.JoiningDate = Convert.ToDateTime(emp.HireDate); if (emp.IsWorker == false) { List <Salary> salaries = db.Salaries.Where(a => a.Date <= From && a.EmployeeId == emp.Id).ToList(); DateTime D = DateTime.Now; if (salaries.Count > 0) { D = salaries.Max(a => a.Date); } var salary = db.Salaries.FirstOrDefault(a => a.EmployeeId == emp.Id && a.Date == D); var salarySetting = db.SalarySettings.FirstOrDefault(); if (salary != null) { //Other viewSalarySheet.Other = salary.TechnicalAllowance + salary.Amount * (salarySetting.Others / 100); //Gross viewSalarySheet.Gross = salary.Amount + salary.TechnicalAllowance; //Basic viewSalarySheet.Basic = salary.Amount * (salarySetting.Basic / 100); //HouseRent viewSalarySheet.HouseRent = salary.Amount * (salarySetting.HouseRent / 100); //Convenience viewSalarySheet.Conv = salary.Amount * (salarySetting.convenience / 100); //Medical viewSalarySheet.Medical = salary.Amount * (salarySetting.Medical / 100); ////Bonus //var Bonus = db.Bonus.FirstOrDefault(a => a.Date.Month == From.Month && a.Date.Year == From.Year); //if (Bonus != null && emp.IsTrainee != true) //{ // viewSalarySheet.Bonus = salary.Amount * (Bonus.Amount / 100); //} //DFood viewSalarySheet.DFood = 0; //DTax viewSalarySheet.DTax = salary.Amount * (salarySetting.Tax / 100); //DSTamp viewSalarySheet.DStamp = salarySetting.Stamp; //Dloan var loanPayment = db.Paytimes.FirstOrDefault(a => a.Date.Month == From.Month && a.Date.Year == From.Year && a.Loan.EmployeeId == emp.Id); if (loanPayment != null) { //List<Paytime> ptimes = db.Paytimes.Where(a => a.LoanId == loanPayment.LoanId).ToList(); viewSalarySheet.DLoan = loanPayment.Loan.Amount / loanPayment.Loan.Installment; } } //Advance List <Advance> advances = db.Advances.Where(a => a.EmployeeId == emp.Id && a.Date.Month == From.Month && a.Date.Year == From.Year).ToList(); if (advances.Count > 0) { viewSalarySheet.DAdvance = advances.Sum(a => a.Amount); } //Salary Days viewSalarySheet.SalaryDays = DateTime.DaysInMonth(From.Year, From.Month);; //Salary Days, Absent, Leave DateTime NewDate = new DateTime(From.Year, From.Month, 1);; do { //Leave var leave = db.Leaves.FirstOrDefault(a => a.From <= NewDate && a.To >= NewDate); var Holiday = db.Holidays.FirstOrDefault(a => a.DepartmentId == emp.Section.DepartmentId && a.From <= NewDate && a.To >= NewDate); if (leave != null) { viewSalarySheet.Leave = viewSalarySheet.Leave + 1; if (leave.LeaveType.Tags == "CL") { viewSalarySheet.CL = viewSalarySheet.CL + 1; } if (leave.LeaveType.Tags == "ML") { viewSalarySheet.ML = viewSalarySheet.ML + 1; } if (leave.LeaveType.Tags == "EL") { viewSalarySheet.EL = viewSalarySheet.EL + 1; } if (leave.LeaveType.Tags == "SL") { viewSalarySheet.SL = viewSalarySheet.SL + 1; } } //Absent int Late = 0; //Employee Count List <Emp_CheckInOut> emp_CheckInOuts = db.Emp_CheckInOuts.Where(a => a.CHECKTIME.Day == NewDate.Day && a.CHECKTIME.Year == NewDate.Year && a.CHECKTIME.Month == NewDate.Month && a.IsManual != true && a.IsAbsent != true).ToList(); //Manual Check List <Emp_CheckInOut> Emp_CheckInOutManual = db.Emp_CheckInOuts.Where(a => a.CHECKTIME.Day == NewDate.Day && a.CHECKTIME.Year == NewDate.Year && a.CHECKTIME.Month == NewDate.Month && a.IsManual == true && a.IsAbsent == false && a.UserId == emp.Emp_Id).ToList(); List <Emp_CheckInOut> Emp_CheckInOutAbsent = db.Emp_CheckInOuts.Where(a => a.CHECKTIME.Day == NewDate.Day && a.CHECKTIME.Year == NewDate.Year && a.CHECKTIME.Month == NewDate.Month && a.IsAbsent == true && a.UserId == emp.Emp_Id).ToList(); if (Emp_CheckInOutAbsent.Count > 0) { viewSalarySheet.Absent = viewSalarySheet.Absent + 1; } else if (Emp_CheckInOutManual.Count <= 0 && NewDate.DayOfWeek.ToString() != "Friday" && leave == null && Holiday == null) { //EMployee CheckInOuts if (emp_CheckInOuts != null) { var duty_Roster = db.Duty_Rosters.FirstOrDefault(a => a.Date == From && a.EmployeeId == emp.Id); // Default Assign if (duty_Roster == null) { List <DL.Duty_Roster> duty_Rosters = db.Duty_Rosters.Where(a => a.EmployeeId == emp.Id && a.Date < From).ToList(); if (duty_Rosters.Count > 0) { DateTime date = duty_Rosters.Max(a => a.Date); duty_Roster = duty_Rosters.FirstOrDefault(a => a.Date == date); } } var salarySetting2 = db.SalarySettings.FirstOrDefault(); // Is Duty Roster Has if (duty_Roster != null) { List <Emp_CheckInOut> empindividual = emp_CheckInOuts.Where(a => a.UserId == emp.Emp_Id).ToList(); //Employee Wise Check in out if (empindividual.Count > 0) { //For C Shift if (duty_Roster.Shift.IsDayToNight == true) { DateTime In = NewDate.Date + duty_Roster.Shift.From; DateTime Out = NewDate.Date + duty_Roster.Shift.To; Out = Out.AddDays(1); DateTime OutCheck = Out.AddHours(2); DateTime InCheck = In.AddHours(-2); List <Emp_CheckInOut> emp_CheckInOuts2 = db.Emp_CheckInOuts.Where(a => a.CHECKTIME.Day >= NewDate.Day && a.CHECKTIME.Year == NewDate.Year && a.CHECKTIME.Month == NewDate.Month && a.CHECKTIME.Day <= NewDate.Day + 1 && a.IsAbsent != true && a.IsManual != true).ToList(); List <Emp_CheckInOut> empindividual2 = emp_CheckInOuts2.Where(a => a.UserId == emp.Emp_Id).ToList(); List <Emp_CheckInOut> empIndividualforshift = empindividual2.Where(a => a.CHECKTIME >= InCheck && a.CHECKTIME <= OutCheck).ToList(); //Shiftwise CheckinOut if (empIndividualforshift.Count > 0) { DateTime Min = (from d in empIndividualforshift select d.CHECKTIME).Min(); DateTime Max = (from d in empIndividualforshift select d.CHECKTIME).Max(); //Status Calculation DateTime InForStatus = In.AddMinutes(salarySetting.Time); DateTime OutForStatus = Out.AddMinutes(-1 * salarySetting.Time); if (Min == Max) { viewSalarySheet.Absent = viewSalarySheet.Absent + 1; } else if (Min > InForStatus && Max >= OutForStatus) { Late = Late + 1; if (Late == 5) { viewSalarySheet.Absent = viewSalarySheet.Absent + 1; Late = 0; } } else if (Min <= InForStatus && Max < OutForStatus) { Late = Late + 1; if (Late == 5) { viewSalarySheet.Absent = viewSalarySheet.Absent + 1; Late = 0; } } else if (Min > InForStatus && Max < OutForStatus) { Late = Late + 1; if (Late == 5) { viewSalarySheet.Absent = viewSalarySheet.Absent + 1; Late = 0; } } } else { viewSalarySheet.Absent = viewSalarySheet.Absent + 1; } } else { DateTime In = NewDate.Date + duty_Roster.Shift.From; DateTime Out = NewDate.Date + duty_Roster.Shift.To; DateTime OutCheck = Out.AddHours(2); DateTime InCheck = In.AddHours(-2); List <Emp_CheckInOut> empIndividualforshift = empindividual.Where(a => a.CHECKTIME >= InCheck && a.CHECKTIME <= OutCheck).ToList(); //Shift Wise Check In Out if (empIndividualforshift.Count > 0) { DateTime Min = (from d in empIndividualforshift select d.CHECKTIME).Min(); DateTime Max = (from d in empIndividualforshift select d.CHECKTIME).Max(); //Status Calculation DateTime InForStatus = In.AddMinutes(salarySetting.Time); DateTime OutForStatus = Out.AddMinutes(-1 * salarySetting.Time); if (Min == Max) { viewSalarySheet.Absent = viewSalarySheet.Absent + 1; } else if (Min > InForStatus && Max >= OutForStatus) { Late = Late + 1; if (Late == 5) { viewSalarySheet.Absent = viewSalarySheet.Absent + 1; Late = 0; } } else if (Min <= InForStatus && Max < OutForStatus) { Late = Late + 1; if (Late == 5) { viewSalarySheet.Absent = viewSalarySheet.Absent + 1; Late = 0; } } else if (Min > InForStatus && Max < OutForStatus) { Late = Late + 1; if (Late == 5) { viewSalarySheet.Absent = viewSalarySheet.Absent + 1; Late = 0; } } } else { viewSalarySheet.Absent = viewSalarySheet.Absent + 1; } } } else { viewSalarySheet.Absent = viewSalarySheet.Absent + 1; } } } else { viewSalarySheet.Absent = viewSalarySheet.Absent + 1; } } NewDate = NewDate.AddDays(1); } while (From.Month == NewDate.Month); } else { ////List<SalaryGrade> salaries = db.SalaryGrades.Where(a => a.EffectiveFrom <= From && a. == emp).ToList(); //DateTime D = DateTime.Now; //if (salaries.Count > 0) //{ // D = salaries.Max(a => a.Date); //} //var salary = db.Salaries.FirstOrDefault(a => a.EmployeeId == emp.Id && a.Date == D); var salarySetting = db.SalarySettings.FirstOrDefault(); List <WorkerDesignationHistory> workerDesignationHistories = db.WorkerDesignationHistories.Where(a => a.EmployeeId == emp.Id && a.From <= From).ToList(); if (workerDesignationHistories.Count > 0) { DateTime maxDate = workerDesignationHistories.Max(a => a.From); WorkerDesignationHistory workerDesignationHistory = workerDesignationHistories.FirstOrDefault(a => a.From == maxDate); // Find Salary Grade At That Time DL.WorkerDesignation workerDesignation = db.WorkerDesignations.Find(workerDesignationHistory.WorkerDesignationId); // Designation Add viewSalarySheet.Designation = workerDesignation.Name; List <ShadowSalaryGrade> shadowSalaryGrades = db.ShadowSalaryGrades.Where(a => a.UpdatedAt <= From && a.RoWId == workerDesignation.SalaryGrade.Id).ToList(); DateTime D2 = DateTime.Now; if (shadowSalaryGrades.Count > 0) { D2 = shadowSalaryGrades.Max(a => a.UpdatedAt); var shadowgrade = shadowSalaryGrades.FirstOrDefault(a => a.RoWId == workerDesignation.SalaryGrade.Id && a.UpdatedAt == D2); //Grade viewSalarySheet.Grade = shadowgrade.GradeName; //Other viewSalarySheet.Other = workerDesignationHistory.TechnicalAllowance; //Gross viewSalarySheet.Gross = shadowgrade.Total; //Basic viewSalarySheet.Basic = shadowgrade.Salary; //HouseRent viewSalarySheet.HouseRent = shadowgrade.HouseRent; //Medical viewSalarySheet.Medical = shadowgrade.MedicalAllowance; //Bonus var Bonus = db.Bonus.FirstOrDefault(a => a.Date.Month == From.Month && a.Date.Year == From.Year); if (Bonus != null && emp.IsTrainee != true) { viewSalarySheet.Bonus = shadowgrade.Total * (Bonus.Amount / 100); } //DFood viewSalarySheet.DFood = 0; //DTax //viewSalarySheet.DTax = salary.Amount * (salarySetting.Tax / 100); //DSTamp viewSalarySheet.DStamp = salarySetting.Stamp; //Dloan var loanPayment = db.Paytimes.FirstOrDefault(a => a.Date.Month == From.Month && a.Date.Year == From.Year && a.Loan.EmployeeId == emp.Id); if (loanPayment != null) { //List<Paytime> ptimes = db.Paytimes.Where(a => a.LoanId == loanPayment.LoanId).ToList(); viewSalarySheet.DLoan = loanPayment.Loan.Amount / loanPayment.Loan.Installment; } } } //Advance List <Advance> advances = db.Advances.Where(a => a.EmployeeId == emp.Id && a.Date.Month == From.Month && a.Date.Year == From.Year).ToList(); if (advances.Count > 0) { viewSalarySheet.DAdvance = advances.Sum(a => a.Amount); } //Salary Days viewSalarySheet.SalaryDays = DateTime.DaysInMonth(From.Year, From.Month);; //Salary Days, Absent, Leave DateTime NewDate = new DateTime(From.Year, From.Month, 1);; do { //Leave var leave = db.Leaves.FirstOrDefault(a => a.From <= NewDate && a.To >= NewDate); var Holiday = db.Holidays.FirstOrDefault(a => a.DepartmentId == emp.Section.DepartmentId && a.From <= NewDate && a.To >= NewDate); if (leave != null) { viewSalarySheet.Leave = viewSalarySheet.Leave + 1; if (leave.LeaveType.Tags == "CL") { viewSalarySheet.CL = viewSalarySheet.CL + 1; } if (leave.LeaveType.Tags == "ML") { viewSalarySheet.ML = viewSalarySheet.ML + 1; } if (leave.LeaveType.Tags == "EL") { viewSalarySheet.EL = viewSalarySheet.EL + 1; } if (leave.LeaveType.Tags == "SL") { viewSalarySheet.SL = viewSalarySheet.SL + 1; } } //Absent int Late = 0; //Employee Count List <Emp_CheckInOut> emp_CheckInOuts = db.Emp_CheckInOuts.Where(a => a.CHECKTIME.Day == NewDate.Day && a.CHECKTIME.Year == NewDate.Year && a.CHECKTIME.Month == NewDate.Month && a.IsManual != true && a.IsAbsent != true).ToList(); //Manual Check List <Emp_CheckInOut> Emp_CheckInOutManual = db.Emp_CheckInOuts.Where(a => a.CHECKTIME.Day == NewDate.Day && a.CHECKTIME.Year == NewDate.Year && a.CHECKTIME.Month == NewDate.Month && a.IsManual == true && a.UserId == emp.Emp_Id).ToList(); List <Emp_CheckInOut> Emp_CheckInOutAbsent = db.Emp_CheckInOuts.Where(a => a.CHECKTIME.Day == NewDate.Day && a.CHECKTIME.Year == NewDate.Year && a.CHECKTIME.Month == NewDate.Month && a.IsAbsent == true && a.UserId == emp.Emp_Id).ToList(); if (Emp_CheckInOutAbsent.Count > 0) { viewSalarySheet.Absent = viewSalarySheet.Absent + 1; } else if (Emp_CheckInOutManual.Count <= 0 && NewDate.DayOfWeek.ToString() != "Friday" && leave == null && Holiday == null) { //EMployee CheckInOuts if (emp_CheckInOuts != null) { var duty_Roster = db.Duty_Rosters.FirstOrDefault(a => a.Date == From && a.EmployeeId == emp.Id); // Default Assign if (duty_Roster == null && emp.IsWorker == false) { List <DL.Duty_Roster> duty_Rosters = db.Duty_Rosters.Where(a => a.EmployeeId == emp.Id).ToList(); if (duty_Rosters.Count > 0) { DateTime date = duty_Rosters.Max(a => a.Date); duty_Roster = duty_Rosters.FirstOrDefault(a => a.Date == date); } } var salarySetting2 = db.SalarySettings.FirstOrDefault(); // Is Duty Roster Has if (duty_Roster != null) { List <Emp_CheckInOut> empindividual = emp_CheckInOuts.Where(a => a.UserId == emp.Emp_Id).ToList(); //Employee Wise Check in out if (empindividual.Count > 0) { //For C Shift if (duty_Roster.Shift.IsDayToNight == true) { DateTime In = NewDate.Date + duty_Roster.Shift.From; DateTime Out = NewDate.Date + duty_Roster.Shift.To; Out = Out.AddDays(1); DateTime OutCheck = Out.AddHours(2); DateTime InCheck = In.AddHours(-2); List <Emp_CheckInOut> emp_CheckInOuts2 = db.Emp_CheckInOuts.Where(a => a.CHECKTIME.Day >= NewDate.Day && a.CHECKTIME.Year == NewDate.Year && a.CHECKTIME.Month == NewDate.Month && a.CHECKTIME.Day <= NewDate.Day + 1 && a.IsAbsent != true && a.IsManual != true).ToList(); List <Emp_CheckInOut> empindividual2 = emp_CheckInOuts2.Where(a => a.UserId == emp.Emp_Id).ToList(); List <Emp_CheckInOut> empIndividualforshift = empindividual2.Where(a => a.CHECKTIME >= InCheck && a.CHECKTIME <= OutCheck).ToList(); //Shiftwise CheckinOut if (empIndividualforshift.Count > 0) { DateTime Min = (from d in empIndividualforshift select d.CHECKTIME).Min(); DateTime Max = (from d in empIndividualforshift select d.CHECKTIME).Max(); //Status Calculation DateTime InForStatus = In.AddMinutes(salarySetting.Time); DateTime OutForStatus = Out.AddMinutes(-1 * salarySetting.Time); if (Min == Max) { viewSalarySheet.Absent = viewSalarySheet.Absent + 1; } else if (Min > InForStatus && Max >= OutForStatus) { Late = Late + 1; if (Late == 5) { viewSalarySheet.Absent = viewSalarySheet.Absent + 1; Late = 0; } } else if (Min <= InForStatus && Max < OutForStatus) { Late = Late + 1; if (Late == 5) { viewSalarySheet.Absent = viewSalarySheet.Absent + 1; Late = 0; } } else if (Min > InForStatus && Max < OutForStatus) { Late = Late + 1; if (Late == 5) { viewSalarySheet.Absent = viewSalarySheet.Absent + 1; Late = 0; } } } else { viewSalarySheet.Absent = viewSalarySheet.Absent + 1; } } else { DateTime In = NewDate.Date + duty_Roster.Shift.From; DateTime Out = NewDate.Date + duty_Roster.Shift.To; DateTime OutCheck = Out.AddHours(2); DateTime InCheck = In.AddHours(-2); List <Emp_CheckInOut> empIndividualforshift = empindividual.Where(a => a.CHECKTIME >= InCheck && a.CHECKTIME <= OutCheck).ToList(); //Shift Wise Check In Out if (empIndividualforshift.Count > 0) { DateTime Min = (from d in empIndividualforshift select d.CHECKTIME).Min(); DateTime Max = (from d in empIndividualforshift select d.CHECKTIME).Max(); //Status Calculation DateTime InForStatus = In.AddMinutes(salarySetting.Time); DateTime OutForStatus = Out.AddMinutes(-1 * salarySetting.Time); if (Min == Max) { viewSalarySheet.Absent = viewSalarySheet.Absent + 1; } else if (Min > InForStatus && Max >= OutForStatus) { Late = Late + 1; if (Late == 5) { viewSalarySheet.Absent = viewSalarySheet.Absent + 1; Late = 0; } } else if (Min <= InForStatus && Max < OutForStatus) { Late = Late + 1; if (Late == 5) { viewSalarySheet.Absent = viewSalarySheet.Absent + 1; Late = 0; } } else if (Min > InForStatus && Max < OutForStatus) { Late = Late + 1; if (Late == 5) { viewSalarySheet.Absent = viewSalarySheet.Absent + 1; Late = 0; } } } else { viewSalarySheet.Absent = viewSalarySheet.Absent + 1; } } } else { viewSalarySheet.Absent = viewSalarySheet.Absent + 1; } } } else { viewSalarySheet.Absent = viewSalarySheet.Absent + 1; } } NewDate = NewDate.AddDays(1); } while (From.Month == NewDate.Month); } viewSalarySheet.DAbsent = (viewSalarySheet.Basic / viewSalarySheet.SalaryDays) * viewSalarySheet.Absent; viewSalarySheet.SalaryDays = viewSalarySheet.SalaryDays - viewSalarySheet.Absent; viewSalarySheets.Add(viewSalarySheet); } } viewSalarySheetDataGridView.DataSource = viewSalarySheets; }