protected void BindData()
    {
        ManageLeaves objManageLeaves = new ManageLeaves();

        grd_Employees.DataSource = objManageLeaves.getDataForOldLeaves(new DateTime(), new DateTime());
        grd_Employees.DataBind();
    }
 protected void BindData()
 {
     ManageLeaves objManageLeaves = new ManageLeaves();
     grd_Employees.DataSource = objManageLeaves.getDataForOldLeaves(new DateTime(), new DateTime());
     grd_Employees.DataBind();
 }
    public List<DailyAttendanceReportViewModel> GetDataForMonthlyReport(int departmentId, DateTime date, TimeSpan relaxation)
    {
        List<DailyAttendanceReportViewModel> lstDailyAttendanceReportViewModel = new List<DailyAttendanceReportViewModel>();
        DataTable dtEmployees, dtShifts;
        DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
        List<SqlParameter> list_params = new List<SqlParameter>() { new SqlParameter("@date", date), new SqlParameter("@departmentId", departmentId) };

        ManageLeaves objManageLeave = new ManageLeaves();
        TimeSpan tsShortLeave = objManageLeave.GetShortLeaveDuration();
        TimeSpan tsHalfLeave = objManageLeave.GetHalfDayLeaveDuration();

        try
        {
            using (DBDataHelper helper = new DBDataHelper())
            {
                dtEmployees = helper.GetDataTable("spGetEmployeesForDailyAttendanceReportByDeaprtment", SQLTextType.Stored_Proc, list_params);
                dtShifts = helper.GetDataTable("spGetActiveShift", SQLTextType.Stored_Proc);

                foreach (DataRow row in dtEmployees.Rows)
                {
                    DailyAttendanceReportViewModel objDailyAttendanceReportViewModel = new DailyAttendanceReportViewModel();

                    objDailyAttendanceReportViewModel.EmployeeId = Int32.Parse(row[1].ToString());
                    objDailyAttendanceReportViewModel.Name = row[0].ToString();
                    objDailyAttendanceReportViewModel.FirstHalfStartTime = dtShifts.Rows[0]["FirstHalfStart"].ToString();
                    objDailyAttendanceReportViewModel.SecondHalfEndTime = dtShifts.Rows[0]["SecondHalfEnd"].ToString();
                    objDailyAttendanceReportViewModel.Relaxation = relaxation.ToString();
                    objDailyAttendanceReportViewModel.Date = date;

                    if (row[2] != DBNull.Value) //Entry Time is Not  Null
                    {
                        objDailyAttendanceReportViewModel.InTime = row[2].ToString();
                        objDailyAttendanceReportViewModel.OutTime = row[3] == DBNull.Value ? dtShifts.Rows[0]["SecondHalfEnd"].ToString() : row[3].ToString();

                        if (row[3] != DBNull.Value)
                            objDailyAttendanceReportViewModel.Status = Status.Present;  //Exit Time is Not Null
                        else
                            objDailyAttendanceReportViewModel.Status = Status.PresentWithNoOutPunch; //Exit Time is Null

                        if (objDailyAttendanceReportViewModel._inTime >= objDailyAttendanceReportViewModel._firstHalfStartTime + relaxation)
                        {
                            ManageLeaves objManageLeaves = new ManageLeaves();
                            if (objDailyAttendanceReportViewModel._inTime - objDailyAttendanceReportViewModel._firstHalfStartTime >= tsHalfLeave)
                            {
                                objDailyAttendanceReportViewModel.Status = Status.OnHalfDayLeave;
                            }
                            else if (objDailyAttendanceReportViewModel._inTime - objDailyAttendanceReportViewModel._firstHalfStartTime >= tsShortLeave)
                            {
                                objDailyAttendanceReportViewModel.Status = Status.OnShortLeave;
                            }
                            else
                                objDailyAttendanceReportViewModel.Status = Status.Late;

                            objDailyAttendanceReportViewModel.LateByDuration = (objDailyAttendanceReportViewModel._firstHalfStartTime < objDailyAttendanceReportViewModel._inTime) ? (objDailyAttendanceReportViewModel._inTime - objDailyAttendanceReportViewModel._firstHalfStartTime).Duration() : TimeSpan.Parse("00:00:00");
                        }
                        objDailyAttendanceReportViewModel.EarlyGoingByDuration = (objDailyAttendanceReportViewModel._inTime.TimeOfDay > TimeSpan.Parse("00:00:00")) ? ((objDailyAttendanceReportViewModel._secondHalfEndTime > objDailyAttendanceReportViewModel._outTime) ? (objDailyAttendanceReportViewModel._secondHalfEndTime - objDailyAttendanceReportViewModel._outTime).Duration() : TimeSpan.Parse("00:00:00")) : TimeSpan.Parse("00:00:00");


                        objDailyAttendanceReportViewModel.Duration = GetTotalTime(objDailyAttendanceReportViewModel.EmployeeId, date);// Exit time null waali
                        if (TimeSpan.Parse(objDailyAttendanceReportViewModel.TotalDuration) > new TimeSpan(0, 0, 0)) // Iterartes only if emp is Present
                            objDailyAttendanceReportViewModel.PunchRecords = GetPunchRecords(objDailyAttendanceReportViewModel.EmployeeId, date);
                        else
                            objDailyAttendanceReportViewModel.PunchRecords = "No Punch Records"; //Employee is Absent
                        TimeSpan totalShiftDuration = objDailyAttendanceReportViewModel._secondHalfEndTime - objDailyAttendanceReportViewModel._firstHalfStartTime;

                        if (TimeSpan.Parse(objDailyAttendanceReportViewModel.TotalDuration) == new TimeSpan(0, 0, 0))
                        {
                            objDailyAttendanceReportViewModel.Status = Status.Absent;
                        }
                        else if (totalShiftDuration - objDailyAttendanceReportViewModel.Duration >= tsHalfLeave)
                        {
                            //ManageLeaves objManageLeaves = new ManageLeaves();
                            //objManageLeaves.AssignLeave(objDailyAttendanceReportViewModel.EmployeeId, objDailyAttendanceReportViewModel.Date, (int)LeaveTypes.HDL);
                            objDailyAttendanceReportViewModel.Status = Status.OnHalfDayLeave;
                        }
                    }

                    else
                    {
                        objDailyAttendanceReportViewModel.InTime = "00:00:00.0000000";
                        objDailyAttendanceReportViewModel.OutTime = "00:00:00.0000000";

                        ManageLeaves objManageLeaves = new ManageLeaves();
                        int TypeOfLeave;// For the type of leave
                        if (objManageLeaves.IsEmployeeOnLeave(objDailyAttendanceReportViewModel.EmployeeId, date, out TypeOfLeave))
                        {
                            objDailyAttendanceReportViewModel.Status = (Status)TypeOfLeave;
                        }
                        else
                            objDailyAttendanceReportViewModel.Status = Status.Absent; // If no leave is there then Absent

                        objDailyAttendanceReportViewModel.Duration = new TimeSpan(0, 0, 0);
                        objDailyAttendanceReportViewModel.PunchRecords = "No Punch Records"; //Employee is Absent
                    }
                    lstDailyAttendanceReportViewModel.Add(objDailyAttendanceReportViewModel);
                }
            }
        }
        catch (Exception)
        {
            //
        }

        return lstDailyAttendanceReportViewModel;
    }
    //By   : Anup Kumar Gupta
    //Date : 21/12/2015
    //Refer: BasicReportViewModel
    public List<DetailedReportViewModel> GetDataForDetailedReport(DateTime date)
    {
        List<DetailedReportViewModel> lstDetailedReportViewModel = new List<DetailedReportViewModel>();
        DataTable dt, dtShifts, dtPunchRecords;
        DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
        List<SqlParameter> list_params = new List<SqlParameter>() { new SqlParameter("@date", date) };

        try
        {
            using (DBDataHelper helper = new DBDataHelper())
            {
                dt = helper.GetDataTable("spGetEmployeesForDetailedReport", SQLTextType.Stored_Proc, list_params);
                dtShifts = helper.GetDataTable("spGetActiveShift", SQLTextType.Stored_Proc);

                foreach (DataRow row in dt.Rows)
                {
                    DetailedReportViewModel model = new DetailedReportViewModel();
                    model.EmployeeCode = Int32.Parse(row[1].ToString());
                    model.Name = row[0].ToString();

                    if (row[2] != DBNull.Value)
                    {
                        model.InTime = row[2].ToString();
                        model.OutTime = row[3] == DBNull.Value ? dtShifts.Rows[0]["SecondHalfEnd"].ToString() : row[3].ToString();
                        if (row[3] != DBNull.Value)
                            model.Status = "Present";
                        else
                            model.Status = "PresentWithNoOutPunch";
                    }
                    else
                    {
                        model.InTime = "00:00:00.0000000";
                        model.OutTime = "00:00:00.0000000";

                        ManageLeaves objManageLeaves = new ManageLeaves();
                        string TypeOfLeave;
                        if (objManageLeaves.IsEmployeeOnLeave(model.EmployeeCode, date, out TypeOfLeave))
                            model.Status = TypeOfLeave;
                        else
                            model.Status = "Absent";
                    }

                    model.TotalDuration = model.Duration.ToString();
                    model.FirstHalfStartTime = dtShifts.Rows[0]["FirstHalfStart"].ToString();
                    model.FirstHalfEndTime = dtShifts.Rows[0]["FirstHalfEnd"].ToString();
                    model.SecondHalfStartTime = dtShifts.Rows[0]["SecondHalfStart"].ToString();
                    model.SecondHalfEndTime = dtShifts.Rows[0]["SecondHalfEnd"].ToString();
                    model.Date = date.Date;
                    List<SqlParameter> list_paramsForPunchRecords = new List<SqlParameter>();
                    list_paramsForPunchRecords.Add(new SqlParameter("@date", date));
                    list_paramsForPunchRecords.Add(new SqlParameter("@employeeId", model.EmployeeCode));
                    dtPunchRecords = helper.GetDataTable("spGetPunchRecordsOfEmployeeDateWise", SQLTextType.Stored_Proc, list_paramsForPunchRecords);
                    foreach (DataRow rowPunchRecords in dtPunchRecords.Rows)
                    {
                        model.PunchRecords += rowPunchRecords[0].ToString() + " in(AKGEC), " + rowPunchRecords[1].ToString() + " out(AKGEC). ";
                    }

                    lstDetailedReportViewModel.Add(model);
                }
            }
        }
        catch (Exception)
        {
            //
        }
        return lstDetailedReportViewModel;
    }
    //By   : Mudit Juneja 
    //Date : 19/12/2015
    //Refer: BasicReportViewModel 

    public List<BasicReportViewModel> GetDataForBasicReport(DateTime date)
    {
        List<BasicReportViewModel> lst_basic = new List<BasicReportViewModel>();
        DataTable dt, dtShifts;
        DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
        List<SqlParameter> list_params = new List<SqlParameter>() { new SqlParameter("@date", date) };
        try
        {
            using (DBDataHelper helper = new DBDataHelper())
            {
                dt = helper.GetDataTable("spGetEmployeesForBasicReport", SQLTextType.Stored_Proc, list_params);
                dtShifts = helper.GetDataTable("spGetActiveShift", SQLTextType.Stored_Proc);

                foreach (DataRow row in dt.Rows)
                {
                    BasicReportViewModel model = new BasicReportViewModel();
                    model.EmployeeCode = Int32.Parse(row[1].ToString());
                    model.Name = row[0].ToString();
                    if (row[2] != DBNull.Value)
                    {
                        model.InTime = row[2].ToString();
                        model.OutTime = row[3] == DBNull.Value ? dtShifts.Rows[0]["SecondHalfEnd"].ToString() : row[3].ToString();
                        if (row[3] != DBNull.Value)
                            model.Status = "Present";
                        else
                            model.Status = "PresentWithNoOutPunch";

                    }
                    else
                    {
                        model.InTime = "00:00:00.0000000";
                        model.OutTime = "00:00:00.0000000";

                        ManageLeaves objManageLeaves = new ManageLeaves();
                        string TypeOfLeave;// For Later Use
                        if (objManageLeaves.IsEmployeeOnLeave(model.EmployeeCode, date, out TypeOfLeave))
                            model.Status = TypeOfLeave;
                        else
                            model.Status = "Absent";
                    }
                    model.InTime = row[2].ToString();
                    model.OutTime = row[3] == DBNull.Value ? "00:00:00.0000000" : row[3].ToString();
                    model.TotalDuration = model.Duration.ToString();
                    model.SecondHalfStartTime = dtShifts.Rows[0]["SecondHalfStart"].ToString();
                    lst_basic.Add(model);
                }
            }
        }
        catch (Exception)
        {
            //
        }
        return lst_basic;
    }
    public bool AssignSessionWiseLeave(DateTime sessionStartDate)
    {
        DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
        MasterEntries objMasterEntries = new MasterEntries();

        List<Role> lstRole = new List<Role>();
        lstRole = objMasterEntries.GetAllRoles();

        DateTime sessionEndDate = new DateTime(sessionStartDate.Year + 1, 7, 31);

        #region Roles

        foreach (Role role in lstRole)
        {
            ManageEmployees objManageEmployees = new ManageEmployees();

            List<Employees> lstEmployees = objManageEmployees.GetEmployeesByRole(role.Id);
            List<LeavesCount> lstLeaveDetails = new List<LeavesCount>();
            ManageLeaves objManageLeaves = new ManageLeaves();
            lstLeaveDetails = objManageLeaves.GetLeavesCountAssignedByRole(role.Id);

            #region List of Employees

            foreach (Employees objEmployees in lstEmployees)
            {
                #region List of Leaves

                foreach (LeavesCount LeaveDetails in lstLeaveDetails)
                {

                    using (DBDataHelper objDDBDataHelper = new DBDataHelper())
                    {
                        string query = @"INSERT INTO [dbo].[tblLeaveAssignedPerSession]
                                 VALUES
                                (@employeeId,
                                 @leaveTypeId, 
                                 @noOfLeaves,
                                 @sessionStartDate,
                                 @sessionEndDate)";

                        List<SqlParameter> list_params = new List<SqlParameter>()
                        {
                            new SqlParameter("@employeeId", objEmployees.Id),
                            new SqlParameter("@leaveTypeId", LeaveDetails.LeaveId),
                            new SqlParameter("@noOfLeaves", LeaveDetails.LeaveCount),
                            new SqlParameter("@sessionStartDate", sessionStartDate),
                            new SqlParameter("@sessionEndDate", sessionEndDate),
                        };

                        objDDBDataHelper.ExecSQL(query, SQLTextType.Query, list_params);
                    }
                }

                #endregion
            }

            #endregion
        }
        #endregion

        return true;

    }
    public DailyAttendanceReportViewModel GetDataForReportEmployeeWise(int employeeId, DateTime date, TimeSpan relaxation)
    {
        DataTable dtEmployees;
        DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
        TimeSpan duration = GetDurationOfEmployeeDateWise(employeeId, date);

        List<SqlParameter> list_params = new List<SqlParameter>()
                                        { new SqlParameter("@date", date),
                                          new SqlParameter("@employeeId", employeeId) };

        MasterShifts objShift = GetShiftForEmployeeDateWise(employeeId, date); //Getting Shift Active as per Employee
        ManageLeaves objManageLeaves = new ManageLeaves();

        TimeSpan ShortLeaveDuration = objShift.SHLDuration;
        TimeSpan HalfLeaveDuration = new TimeSpan(4, 0, 0); //Change According
        TimeSpan totalDuration = new DateTime(1, 1, 1, objShift.SecondHalfEnd.Hours, objShift.SecondHalfEnd.Minutes, objShift.SecondHalfEnd.Seconds) - new DateTime(1, 1, 1, objShift.FirstHalfStart.Hours, objShift.FirstHalfStart.Minutes, objShift.FirstHalfStart.Seconds);
        DayStatus dayStatus = GetStatusOfDayEmployeeWise(date, employeeId);
        DailyAttendanceReportViewModel objDailyAttendanceReportViewModel = new DailyAttendanceReportViewModel();
        int typeOfLeave;// For the type of leave

        #region Generate Data
        try
        {
            using (DBDataHelper helper = new DBDataHelper())
            {
                dtEmployees = helper.GetDataTable("spGetEmployeesForDailyAttendanceReportByEmployeeId", SQLTextType.Stored_Proc, list_params);

                foreach (DataRow row in dtEmployees.Rows)
                {
                    objDailyAttendanceReportViewModel.EmployeeId = Int32.Parse(row[1].ToString());
                    objDailyAttendanceReportViewModel.Name = row[0].ToString();
                    objDailyAttendanceReportViewModel.FirstHalfStartTime = objShift.FirstHalfStart.ToString();
                    objDailyAttendanceReportViewModel.FirstHalfEndTime = objShift.FirstHalfEnd.ToString();
                    objDailyAttendanceReportViewModel.SecondHalfStartTime = objShift.SecondHalfStart.ToString();
                    objDailyAttendanceReportViewModel.SecondHalfEndTime = objShift.SecondHalfEnd.ToString();
                    objDailyAttendanceReportViewModel.Relaxation = relaxation.ToString();
                    objDailyAttendanceReportViewModel.Date = date;
                    objDailyAttendanceReportViewModel.Duration = duration;

                    #region If Present
                    if (row[2] != DBNull.Value) //Entry Time is Not  Null ---- Employee is Present
                    {
                        TimeSpan empEntryTime = TimeSpan.Parse("00:" + row[2].ToString());
                        TimeSpan empExitTime = row[3] == DBNull.Value ? new TimeSpan() : TimeSpan.Parse("00:" + row[3].ToString());

                        var bnm = (DateTime.Parse(objDailyAttendanceReportViewModel.FirstHalfStartTime)).TimeOfDay;

                        if (empEntryTime > (DateTime.Parse(objDailyAttendanceReportViewModel.FirstHalfStartTime)).TimeOfDay + relaxation)
                        {
                            objDailyAttendanceReportViewModel.LateByDuration = empEntryTime - (DateTime.Parse(objDailyAttendanceReportViewModel.FirstHalfStartTime)).TimeOfDay - relaxation;
                        }
                        //objDailyAttendanceReportViewModel.InTime = DateTime
                        objDailyAttendanceReportViewModel.InTime = Convert.ToDateTime(empEntryTime.ToString()).ToString();

                        objDailyAttendanceReportViewModel.OutTime = row[3] == DBNull.Value ? Convert.ToDateTime(objShift.SecondHalfEnd.ToString()).ToString() : Convert.ToDateTime(empExitTime.ToString()).ToString();//IfExitPunch is Null 

                        if (row[3] == DBNull.Value)
                        {
                            if (dayStatus != DayStatus.WeeklyOff)
                                objDailyAttendanceReportViewModel.Status = BAS.Enums.Status.PresentWithNoOutPunch;//Exit Time is Null
                            else
                                objDailyAttendanceReportViewModel.Status = BAS.Enums.Status.WeeklyOffPresentWithNoOutPunch;
                        }
                        else
                        {

                            if (objDailyAttendanceReportViewModel._inTime.TimeOfDay >= relaxation + objShift.FirstHalfStart)// IF Late
                            {
                                if (duration + HalfLeaveDuration <= totalDuration)
                                {
                                    objManageLeaves.AssignLeave(employeeId, date, (int)BAS.Enums.LeaveTypes.HDL);
                                    objDailyAttendanceReportViewModel.Status = BAS.Enums.Status.OnHalfDayLeaveFirstHalf;
                                }
                                else
                                {
                                    objManageLeaves.AssignLeave(employeeId, date, (int)BAS.Enums.LeaveTypes.SHL);
                                    objDailyAttendanceReportViewModel.Status = BAS.Enums.Status.OnShortLeave;
                                }
                            }
                            else if (duration + HalfLeaveDuration < duration)// Second Half Leave
                            {
                                objManageLeaves.AssignLeave(employeeId, date, (int)BAS.Enums.LeaveTypes.HDL);
                                objDailyAttendanceReportViewModel.Status = BAS.Enums.Status.OnHalfDayLeaveSecondHalf;
                            }
                            else
                            {
                                if (dayStatus != DayStatus.WeeklyOff)
                                    objDailyAttendanceReportViewModel.Status = BAS.Enums.Status.Present;
                                else
                                    objDailyAttendanceReportViewModel.Status = BAS.Enums.Status.WeeklyOffPresent;
                            }
                        } //

                    }
                    #endregion
                    #region If Absent
                    else  //Entry Time is Null
                    {
                        objDailyAttendanceReportViewModel.InTime = "00:00:00.0000000";
                        objDailyAttendanceReportViewModel.OutTime = "00:00:00.0000000";
                        if (dayStatus == DayStatus.Holiday)
                        {
                            objDailyAttendanceReportViewModel.Status = Status.Holiday;
                        }
                        else if (dayStatus == DayStatus.WeeklyOff)
                        {
                            objDailyAttendanceReportViewModel.Status = Status.WeeklyOff;
                        }
                        else if (objManageLeaves.IsEmployeeOnLeave(objDailyAttendanceReportViewModel.EmployeeId, date, out typeOfLeave))
                        {
                            objDailyAttendanceReportViewModel.Status = (Status)typeOfLeave;
                        }
                        else
                        {
                            objDailyAttendanceReportViewModel.Status = Status.LeaveWithoutPay;
                        }
                    }

                    #endregion

                }

            }
        }
        catch (Exception)
        {
        }
        #endregion

        return objDailyAttendanceReportViewModel;
    }