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;
}