Exemplo n.º 1
0
    public List <LeavesCount> GetLeavesCountAssignedByRole(int roleId)
    {
        DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
        DataSet ds;
        int     i = 0;

        using (DBDataHelper objDDBDataHelper = new DBDataHelper())
        {
            string query = @"SELECT LeaveTypeId,NoOfLeaves 
                             FROM tblLeaveAssignedByRole
                             WHERE RoleId = @roleId  AND
                                   IsDeleted = 0";
            List <SqlParameter> lstParams = new List <SqlParameter>()
            {
                new SqlParameter("@roleId", roleId)
            };
            ds = objDDBDataHelper.GetDataSet(query, SQLTextType.Query, lstParams);

            List <LeavesCount> lstLeavesCount = new List <LeavesCount>();

            foreach (DataRow rows in ds.Tables[0].Rows)
            {
                LeavesCount objLeavesCount = new LeavesCount();

                objLeavesCount.LeaveId    = Convert.ToInt32(ds.Tables[0].Rows[i][0]);
                objLeavesCount.LeaveCount = Convert.ToInt32(ds.Tables[0].Rows[i][1]);
                lstLeavesCount.Add(objLeavesCount);
                i++;
            }
            return(lstLeavesCount);
        }
    }
    public List<LeavesCount> GetLeavesCountAssignedByRole(int roleId)
    {
        DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
        DataSet ds;
        int i = 0;
        using (DBDataHelper objDDBDataHelper = new DBDataHelper())
        {
            string query = @"SELECT LeaveTypeId,NoOfLeaves 
                             FROM tblLeaveAssignedByRole
                             WHERE RoleId = @roleId  AND
                                   IsDeleted = 0";
            List<SqlParameter> lstParams = new List<SqlParameter>(){
                 new SqlParameter("@roleId",roleId)
            };
            ds = objDDBDataHelper.GetDataSet(query, SQLTextType.Query, lstParams);

            List<LeavesCount> lstLeavesCount = new List<LeavesCount>();

            foreach (DataRow rows in ds.Tables[0].Rows)
            {
                LeavesCount objLeavesCount = new LeavesCount();

                objLeavesCount.LeaveId = Convert.ToInt32(ds.Tables[0].Rows[i][0]);
                objLeavesCount.LeaveCount = Convert.ToInt32(ds.Tables[0].Rows[i][1]);
                lstLeavesCount.Add(objLeavesCount);
                i++;
            }
            return lstLeavesCount;
        }
    }
 public List<LeavesCount> GetLeavesBalanceByEmployee(List<LeavesCount> lstLeavesDueOfEmployee, List<LeavesCount> lstLeavesAvailedByEmployee)
 {
     List<LeavesCount> lstLeavesBalance = new List<LeavesCount>();
     foreach (LeavesCount item in lstLeavesDueOfEmployee)
     {
         LeavesCount objLeavesCount = new LeavesCount();
         objLeavesCount.LeaveId = item.LeaveId;
         objLeavesCount.LeaveName = item.LeaveName;
         objLeavesCount.LeaveCount = item.LeaveCount - (lstLeavesAvailedByEmployee.Select(x => x).Where(y => y.LeaveId == item.LeaveId).FirstOrDefault()).LeaveCount;
         lstLeavesBalance.Add(objLeavesCount);
     }
     return lstLeavesBalance;
 }
    public List<LeavesCount> GetLeavesAvailedByEmployee(int employeeId, DateTime monthStart, DateTime monthEnd)
    {
        DataTable dtLeavesAvailed;
        DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
        List<LeavesCount> lstLeavesAvailed = new List<LeavesCount>();

        #region Leaves Availed  That Month
        using (DBDataHelper helper = new DBDataHelper())
        {

            List<SqlParameter> list_params = new List<SqlParameter>()
            {
                new SqlParameter("@employeeId", employeeId),
                new SqlParameter("@monthStartDate", monthStart),
                new SqlParameter("@monthEndDate", monthEnd)
            };

            dtLeavesAvailed = helper.GetDataTable("spGetLeavesAvailedByMonthEmployeeWise", SQLTextType.Stored_Proc, list_params);

            foreach (DataRow row in dtLeavesAvailed.Rows)
            {
                LeavesCount objLeavesCount = new LeavesCount();
                objLeavesCount.LeaveId = row[0] == DBNull.Value ? 0 : Int32.Parse(row[0].ToString());
                objLeavesCount.LeaveName = row[1] == DBNull.Value ? "" : row[1].ToString();
                objLeavesCount.LeaveCount = row[2] == DBNull.Value ? 0 : Int32.Parse(row[2].ToString());
                lstLeavesAvailed.Add(objLeavesCount);
            }
        }

        #endregion
        return lstLeavesAvailed;
    }
    public List<LeavesCount> GetLeavesDueOfEmployee(int employeeId, DateTime Date)
    {
        DataTable dtAssignedLeaves, dtLeavesAvailed;
        DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
        List<LeavesCount> lstLeavesAssigned = new List<LeavesCount>();
        List<LeavesCount> lstLeavesAvailed = new List<LeavesCount>();
        List<LeavesCount> lstLeavesDue = new List<LeavesCount>();

        #region Leaves Assigned Count
        DateTime sessionStartDate = (Date.Month >= 8) ? new DateTime(Date.Year, 8, 01) : new DateTime(Date.Year - 1, 8, 01);
        DateTime sessionEndDate = (Date.Month <= 7) ? new DateTime(Date.Year, 7, 31) : new DateTime(Date.Year + 1, 7, 31);

        using (DBDataHelper helper = new DBDataHelper())
        {
            List<SqlParameter> list_params_Assigned = new List<SqlParameter>()
            {   new SqlParameter("@employeeId", employeeId),
                new SqlParameter("@sessionStartDate", sessionStartDate),
                new SqlParameter("@sessionEndDate", sessionEndDate)
            };
            dtAssignedLeaves = helper.GetDataTable("spGetLeavesAssignedToEmployeeSessionWise", SQLTextType.Stored_Proc, list_params_Assigned);
            foreach (DataRow row in dtAssignedLeaves.Rows)
            {
                LeavesCount objLeavesCount = new LeavesCount();
                objLeavesCount.LeaveId = row[0] == DBNull.Value ? 0 : Int32.Parse(row[0].ToString());
                objLeavesCount.LeaveName = row[1] == DBNull.Value ? "" : row[1].ToString();
                objLeavesCount.LeaveCount = row[2] == DBNull.Value ? 0 : Int32.Parse(row[2].ToString());
                lstLeavesAssigned.Add(objLeavesCount);
            }
        }
        #endregion

        #region Leaves Availed Upto That Month
        using (DBDataHelper helper = new DBDataHelper())
        {
            DateTime SessionStartDate = new DateTime();
            DateTime SessionEndDate = new DateTime();
            if (Date.Month > 7)
            {
                SessionStartDate = new DateTime(Date.Year, 08, 01);
                SessionEndDate = new DateTime(Date.Year + 1, 07, 31);
            }
            else
            {
                SessionStartDate = new DateTime(Date.Year - 1, 08, 01);
                SessionEndDate = new DateTime(Date.Year, 07, 31);
            }

            List<SqlParameter> list_params = new List<SqlParameter>()
            {
                new SqlParameter("@employeeId", employeeId),
                new SqlParameter("@sessionStart", SessionStartDate),
                new SqlParameter("@sessionEnd", SessionEndDate),
                new SqlParameter("@monthStartDate", Date)
            };

            dtLeavesAvailed = helper.GetDataTable("spGetLeavesAvailedUptoPreviousMonthEmployeeWise", SQLTextType.Stored_Proc, list_params);

            foreach (DataRow row in dtLeavesAvailed.Rows)
            {
                LeavesCount objLeavesCount = new LeavesCount();
                objLeavesCount.LeaveId = row[0] == DBNull.Value ? 0 : Int32.Parse(row[0].ToString());
                objLeavesCount.LeaveName = row[1] == DBNull.Value ? "" : row[1].ToString();
                objLeavesCount.LeaveCount = row[2] == DBNull.Value ? 0 : Int32.Parse(row[2].ToString());
                lstLeavesAvailed.Add(objLeavesCount);
            }
        }

        #endregion

        #region Leaves Due
        foreach (LeavesCount item in lstLeavesAssigned)
        {
            LeavesCount objLeavesCount = new LeavesCount();
            objLeavesCount.LeaveId = item.LeaveId;
            objLeavesCount.LeaveName = item.LeaveName;
            objLeavesCount.LeaveCount = item.LeaveCount - (lstLeavesAvailed.Select(x => x).Where(y => y.LeaveId == item.LeaveId).FirstOrDefault()).LeaveCount;
            lstLeavesDue.Add(objLeavesCount);
        }
        #endregion

        return lstLeavesDue;
    }
    public List<LeavesCount> GetLeavesAssignedPerSessionEmployeeWise(int employeeId, DateTime Date)
    {
        DataTable dtAssignedLeaves;
        DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
        List<LeavesCount> lstLeavesAssigned = new List<LeavesCount>();

        DateTime sessionStartDate = (Date.Month >= 8) ? new DateTime(Date.Year, 8, 01) : new DateTime(Date.Year - 1, 8, 01);
        DateTime sessionEndDate = (Date.Month <= 7) ? new DateTime(Date.Year, 7, 31) : new DateTime(Date.Year + 1, 7, 31);

        using (DBDataHelper helper = new DBDataHelper())
        {
            List<SqlParameter> list_params_Assigned = new List<SqlParameter>()
            {   new SqlParameter("@employeeId", employeeId),
                new SqlParameter("@sessionStartDate", sessionStartDate),
                new SqlParameter("@sessionEndDate", sessionEndDate)
            };
            dtAssignedLeaves = helper.GetDataTable("spGetLeavesAssignedToEmployeeSessionWise", SQLTextType.Stored_Proc, list_params_Assigned);
            foreach (DataRow row in dtAssignedLeaves.Rows)
            {
                LeavesCount objLeavesCount = new LeavesCount();
                objLeavesCount.LeaveId = row[0] == DBNull.Value ? 0 : Int32.Parse(row[0].ToString());
                objLeavesCount.LeaveName = row[1] == DBNull.Value ? "" : row[1].ToString();
                objLeavesCount.LeaveCount = row[2] == DBNull.Value ? 0 : Int32.Parse(row[2].ToString());
                lstLeavesAssigned.Add(objLeavesCount);
            }
        }
        return lstLeavesAssigned;
    }