public List<TypeOfLeave> LeaveFromGivenMasterLeaveType(int masterLeaveType)
{
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
DBDataHelper helper = new DBDataHelper();
List<SqlParameter> lstLeaveDetail = new List<SqlParameter>();
string query = "select Id,Name from tblTypeOfLeave where MasterLeaveType=@masterleavetype";
lstLeaveDetail.Add(new SqlParameter("@masterleavetype", masterLeaveType));
DataTable dt = new DataTable();
DataSet ds;
int i = 0;
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
ds = objDDBDataHelper.GetDataSet(query, SQLTextType.Query, lstLeaveDetail);
List<TypeOfLeave> lstLeaveType = new List<TypeOfLeave>();
foreach (DataRow rows in ds.Tables[0].Rows)
{
TypeOfLeave objLeaveType = new TypeOfLeave();
objLeaveType.Id = Convert.ToInt32(ds.Tables[0].Rows[i][0]);
objLeaveType.Name = (ds.Tables[0].Rows[i][1]).ToString();
lstLeaveType.Add(objLeaveType);
i++;
}
return lstLeaveType;
}
}
public List<Employees> GetEmployeesPresentDateWise(DateTime dateTime)
{
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
List<Employees> lstEmployees = new List<Employees>();
List<SqlParameter> lstAttendanceDetail = new List<SqlParameter>();
lstAttendanceDetail.Add(new SqlParameter("@date", dateTime));
int i = 0;
DataSet ds = new DataSet();
try
{
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
ds = objDDBDataHelper.GetDataSet("spGetEmployeesPresentDateWise", SQLTextType.Stored_Proc, lstAttendanceDetail);
}
}
catch (Exception ex)
{
return lstEmployees;
}
foreach (DataRow rows in ds.Tables[0].Rows)
{
Employees objEmployees = new Employees();
objEmployees.Id = Convert.ToInt32(ds.Tables[0].Rows[i][0]);
objEmployees.FirstName = ds.Tables[0].Rows[i][1].ToString();
objEmployees.MiddleName = ds.Tables[0].Rows[i][2].ToString();
objEmployees.LastName = ds.Tables[0].Rows[i][3].ToString();
lstEmployees.Add(objEmployees);
i++;
}
return lstEmployees;
}
public void CreateEmployee(Employees objEmployee)
{
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
List<SqlParameter> lstMasterEmployeeDetail = new List<SqlParameter>();
lstMasterEmployeeDetail.Add(new SqlParameter("@facultyId", objEmployee.Id));
lstMasterEmployeeDetail.Add(new SqlParameter("@name", objEmployee.Name));
lstMasterEmployeeDetail.Add(new SqlParameter("@joiningDate", objEmployee.JoiningDate));
lstMasterEmployeeDetail.Add(new SqlParameter("@gender", objEmployee.Gender));
lstMasterEmployeeDetail.Add(new SqlParameter("@createdOn", objEmployee.CreatedOn));
lstMasterEmployeeDetail.Add(new SqlParameter("@isDeleted", false));
DataTable dt = new DataTable();
DataSet ds;
int EmployeeId;
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
ds = objDDBDataHelper.GetDataSet("spCreateEmployee", SQLTextType.Stored_Proc, lstMasterEmployeeDetail);
EmployeeId = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
}
List<SqlParameter> lstEmployeeDetail = new List<SqlParameter>();
lstEmployeeDetail.Add(new SqlParameter("@employeeId", EmployeeId));
lstEmployeeDetail.Add(new SqlParameter("@roleId", objEmployee.RoleId));
lstEmployeeDetail.Add(new SqlParameter("@departmentId", objEmployee.DepartmentId));
lstEmployeeDetail.Add(new SqlParameter("@contactNumber", objEmployee.ContactNumber));
lstEmployeeDetail.Add(new SqlParameter("@weeklyOffDay", objEmployee.WeeklyOffDay));
lstEmployeeDetail.Add(new SqlParameter("@shiftId", objEmployee.ShiftId));
lstEmployeeDetail.Add(new SqlParameter("@createdAt", objEmployee.CreatedOn));
lstEmployeeDetail.Add(new SqlParameter("@updatedAt", objEmployee.UpdatedOn));
lstEmployeeDetail.Add(new SqlParameter("@isDeleted", false));
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
objDDBDataHelper.ExecSQL("spCreateEmployeeTransc", SQLTextType.Stored_Proc, lstEmployeeDetail);
}
}
public List<AssignLeaveViewModel> BindGridData()
{
MasterEntries objMasterEntries = new MasterEntries();
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
DataTable dt = new DataTable();
List<SqlParameter> lstData = new List<SqlParameter>();
lstData.Add(new SqlParameter("@employeeId", Convert.ToInt32(Session["employeeId"])));
lstData.Add(new SqlParameter("@dateMonth", Calendar1.SelectedDate.Month));
lstData.Add(new SqlParameter("@dateYear", Calendar1.SelectedDate.Year));
lstData.Add(new SqlParameter("@IsDeleted", Convert.ToInt32(0)));
DataSet ds;
int i = 0;
string query = "SELECT Id , LeaveTypeId , [Date] FROM tblLeave WHERE MONTH([Date]) = @dateMonth AND YEAR([Date]) = @dateYear And EmployeeId = @employeeId And IsDeleted = @IsDeleted";
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
ds = objDDBDataHelper.GetDataSet(query, SQLTextType.Query, lstData);
List<AssignLeaveViewModel> lstLeaves = new List<AssignLeaveViewModel>();
foreach (DataRow row in ds.Tables[0].Rows)
{
AssignLeaveViewModel objLeaves = new AssignLeaveViewModel();
int Id = Convert.ToInt32(ds.Tables[0].Rows[i][0]);
int leaveId = Convert.ToInt32(ds.Tables[0].Rows[i][1]);
Leaves objLeaves1 = new Leaves();
objMasterEntries.GetLeavesById(leaveId, out objLeaves1);
objLeaves.leaveId = Id;
objLeaves.Id = objLeaves1.Id;
objLeaves.LeaveName = objLeaves1.LeaveName;
objLeaves.EmployeeId = Convert.ToInt32(Session["employeeId"]);
objLeaves.Date = (Convert.ToDateTime(ds.Tables[0].Rows[i][2])).Date;
lstLeaves.Add(objLeaves);
i++;
}
return lstLeaves;
}
}
public List<AssignLeaveViewModel> GetDataForGridview()
{
MasterEntries objMasterEntries = new MasterEntries();
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
DateTime sessionStartDate = new DateTime();
DateTime sessionEndDate = new DateTime();
if (DateTime.Now.Month <= 7)
{
sessionStartDate = new DateTime(DateTime.Now.Year - 1, 8, 1);
sessionEndDate = new DateTime(DateTime.Now.Year, 7, 31);
}
else
{
sessionStartDate = new DateTime(DateTime.Now.Year, 8, 1);
sessionEndDate = new DateTime(DateTime.Now.Year + 1, 7, 31);
}
List<SqlParameter> lstData = new List<SqlParameter>();
lstData.Add(new SqlParameter("@employeeId", Convert.ToInt32(Session["employeeId"])));
lstData.Add(new SqlParameter("@sessionStartDate", sessionStartDate));
lstData.Add(new SqlParameter("@sessionEndDate", sessionEndDate));
lstData.Add(new SqlParameter("@IsDeleted", Convert.ToInt32(0)));
DataSet ds;
int i = 0;
string query = @"SELECT Id , LeaveTypeId , [Date]
FROM tblLeave
WHERE [Date] >= @sessionStartDate
AND [Date] <= @sessionEndDate
AND EmployeeId = @employeeId
AND IsDeleted = @IsDeleted";
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
ds = objDDBDataHelper.GetDataSet(query, SQLTextType.Query, lstData);
List<AssignLeaveViewModel> lstLeaves = new List<AssignLeaveViewModel>();
foreach (DataRow row in ds.Tables[0].Rows)
{
AssignLeaveViewModel objLeaves = new AssignLeaveViewModel();
int Id = Convert.ToInt32(ds.Tables[0].Rows[i][0]);
int leaveId = Convert.ToInt32(ds.Tables[0].Rows[i][1]);
Leaves objLeaves1 = new Leaves();
objMasterEntries.GetLeavesById(leaveId, out objLeaves1);
objLeaves.leaveId = Id;
objLeaves.Id = objLeaves1.Id;
objLeaves.LeaveName = objLeaves1.LeaveName;
objLeaves.EmployeeId = Convert.ToInt32(Session["employeeId"]);
objLeaves.Date = (Convert.ToDateTime(ds.Tables[0].Rows[i][2])).Date;
lstLeaves.Add(objLeaves);
i++;
}
return lstLeaves;
}
}
public bool IsLeaveExist(int employeeId, DateTime date)
{
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
DBDataHelper helper = new DBDataHelper();
List<SqlParameter> lstLeaveDetail = new List<SqlParameter>();
lstLeaveDetail.Add(new SqlParameter("@employeeId", employeeId));
lstLeaveDetail.Add(new SqlParameter("@date", date));
List<SqlParameter> lstLeaveDetail1 = new List<SqlParameter>();
lstLeaveDetail1.Add(new SqlParameter("@employeeId", employeeId));
lstLeaveDetail1.Add(new SqlParameter("@date", date));
List<SqlParameter> lstLeaveDetail2 = new List<SqlParameter>();
lstLeaveDetail2.Add(new SqlParameter("@employeeId", employeeId));
lstLeaveDetail2.Add(new SqlParameter("@date", date));
List<SqlParameter> lstLeaveDetail3 = new List<SqlParameter>();
lstLeaveDetail3.Add(new SqlParameter("@employeeId", employeeId));
lstLeaveDetail3.Add(new SqlParameter("@date", date));
DataTable dt = new DataTable();
DataSet ds, ds1, ds2, ds3;
// string query = "insert into tblLeave values(@employeeId,@leaveTypeId,@createdOn,@updatedOn,@isDeleted)";
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
// ds = objDDBDataHelper.GetDataSet("IsEmployeeOnMultidayLeaveByDate", SQLTextType.Stored_Proc, lstLeaveDetail);
ds1 = objDDBDataHelper.GetDataSet("spIsEmployeeOnHalfDayLeaveByDate", SQLTextType.Stored_Proc, lstLeaveDetail1);
ds2 = objDDBDataHelper.GetDataSet("spIsEmployeeOnFullDayLeaveByDate", SQLTextType.Stored_Proc, lstLeaveDetail2);
ds3 = objDDBDataHelper.GetDataSet("spIsEmployeeOnDurationalLeaveByDate", SQLTextType.Stored_Proc, lstLeaveDetail3);
}
int count = Convert.ToInt32(ds1.Tables[0].Rows[0][0]);
int count1 = Convert.ToInt32(ds1.Tables[0].Rows[0][0]);
int count2 = Convert.ToInt32(ds2.Tables[0].Rows[0][0]);
int count3 = Convert.ToInt32(ds3.Tables[0].Rows[0][0]);
if (count == 1 || count1 == 1 || count2 >= 1 || count3 == 1)
return false;
else
return true;
}
/// <summary>
///
/// </summary>
/// <param name="departmentId"></param>
/// <returns></returns>
public string GetDepartmentById(int departmentId)
{
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
List<SqlParameter> lstDepartment = new List<SqlParameter>();
lstDepartment.Add(new SqlParameter("@departmentId", departmentId));
DataTable dt = new DataTable();
DataSet ds;
string department;
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
ds = objDDBDataHelper.GetDataSet("spGetDepartmentById", SQLTextType.Stored_Proc, lstDepartment);
department = ds.Tables[0].Rows[0][0].ToString();
}
return department;
}
protected void btnUpdate_Click(object sender, EventArgs e)
{
List<LeaveAssignedRecord> lstLeaveAssignedRecord = new List<LeaveAssignedRecord>();
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
DataSet ds;
int leaveId;
LeaveAssignedPerSession objLeaveAssignedPerSession = new LeaveAssignedPerSession();
ManageReports objManageReports = new ManageReports();
foreach (RepeaterItem i in EditgvLeaves.Items)
{
TextBox txtLeaveCount = (TextBox)i.FindControl("txtLeaveCount");
Label txtLeaveName = (Label)i.FindControl("txtLeave");
string leaveName = txtLeaveName.Text;
List<SqlParameter> lstParams = new List<SqlParameter>();
lstParams.Add(new SqlParameter("@name", leaveName));
string query = "Select Id from tblTypeOfLeave where Name = @name";
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
ds = objDDBDataHelper.GetDataSet(query, SQLTextType.Query, lstParams);
leaveId = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
}
DateTime SessionStartDate, SessionEndDate;
if (DateTime.Now.Month >= 8)
{
SessionStartDate = new DateTime(DateTime.Now.Year, 08, 01);
SessionEndDate = new DateTime(DateTime.Now.Year+1, 07, 31);
}
else
{
SessionStartDate = new DateTime(DateTime.Now.Year-1, 08, 01);
SessionEndDate = new DateTime(DateTime.Now.Year, 07, 31);
}
objLeaveAssignedPerSession.EmployeeId = Convert.ToInt32(Session["empId"]);
objLeaveAssignedPerSession.leaveCount = Convert.ToInt32(txtLeaveCount.Text);
objLeaveAssignedPerSession.leaveType = leaveId;
objManageReports.UpdateLeavesAssignedPerSessionEmployeeWise(objLeaveAssignedPerSession, SessionStartDate,SessionEndDate);
}
popupEditLeaveAssigned.Hide();
int departmentId = Convert.ToInt32(ddlShowDepartment.SelectedValue);
ManageReports objManageReports1 = new ManageReports();
lstLeaveAssignedRecord = objManageReports1.GetLeavesAssignedPerSession(departmentId, DateTime.Now);
//grid1.DataSource = lstLeaveAssignedRecord;
//grid1.DataBind();
}
/// <summary>
///
/// </summary>
/// <param name="departmentId"></param>
/// <returns></returns>
public string GetRoleById(int roleId)
{
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
List <SqlParameter> lstRole = new List <SqlParameter>();
lstRole.Add(new SqlParameter("@roleId", roleId));
DataTable dt = new DataTable();
DataSet ds;
string role;
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
ds = objDDBDataHelper.GetDataSet("spGetRoleById", SQLTextType.Stored_Proc, lstRole);
role = ds.Tables[0].Rows[0][0].ToString();
}
return(role);
}
protected void btnAddSession_Click(object sender, EventArgs e)
{
int shiftId = Convert.ToInt32(ddlShift.SelectedValue);
DateTime date = DateTime.Parse(txtDate.Text);
int employeeId = Convert.ToInt32(txtEmployeeId.Text);
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
List<SqlParameter> lstparameter = new List<SqlParameter>();
lstparameter.Add(new SqlParameter("@shiftId", shiftId));
lstparameter.Add(new SqlParameter("@date", date));
lstparameter.Add(new SqlParameter("@employeeId", employeeId));
string query = "Insert into tblDateWiseShift values(@shiftId,@employeeId,@date)";
DataTable dt = new DataTable();
DataSet ds;
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
ds = objDDBDataHelper.GetDataSet(query, SQLTextType.Query, lstparameter);
}
}
public int Count(int roleId, int leaveId)
{
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
DataTable dt = new DataTable();
List <SqlParameter> lstLeaveAssignedByRole = new List <SqlParameter>();
lstLeaveAssignedByRole.Add(new SqlParameter("@leaveId", leaveId));
lstLeaveAssignedByRole.Add(new SqlParameter("@roleId", roleId));
DataSet ds;
int i;
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
ds = objDDBDataHelper.GetDataSet("IsSingle", SQLTextType.Stored_Proc, lstLeaveAssignedByRole);
i = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
}
return(i);
}
public bool UpdateLeavesAssignedByRole(int id, int noOfLeaves, bool isPromoted)
{
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
List <SqlParameter> lstparameter = new List <SqlParameter>();
lstparameter.Add(new SqlParameter("@id", id));
lstparameter.Add(new SqlParameter("@noOfLeaves", noOfLeaves));
lstparameter.Add(new SqlParameter("@isPromoted", isPromoted));
lstparameter.Add(new SqlParameter("@updatedAt", DateTime.Now));
string query = "Update tblLeaveAssignedByRole set NoOfLeaves = @noOfLeaves , IsPromoted = @isPromoted, UpdatedAt = @updatedAt where Id=@id";
DataTable dt = new DataTable();
DataSet ds;
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
ds = objDDBDataHelper.GetDataSet(query, SQLTextType.Query, lstparameter);
}
return(true);
}
protected void lnkDelete_Click(object sender, EventArgs e)
{
LinkButton b = (LinkButton)sender;
int id = Convert.ToInt32(b.CommandArgument);
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
DataTable dt = new DataTable();
List <SqlParameter> lstData = new List <SqlParameter>();
lstData.Add(new SqlParameter("@leaveId", id));
DataSet ds;
int i = 0;
string query = "Update tblLeave set IsDeleted = 1 where Id = @leaveId";
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
ds = objDDBDataHelper.GetDataSet(query, SQLTextType.Query, lstData);
}
}
protected void btnAddSession_Click(object sender, EventArgs e)
{
int shiftId = Convert.ToInt32(ddlShift.SelectedValue);
DateTime date = DateTime.Parse(txtDate.Text);
int employeeId = Convert.ToInt32(txtEmployeeId.Text);
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
List <SqlParameter> lstparameter = new List <SqlParameter>();
lstparameter.Add(new SqlParameter("@shiftId", shiftId));
lstparameter.Add(new SqlParameter("@date", date));
lstparameter.Add(new SqlParameter("@employeeId", employeeId));
string query = "Insert into tblDateWiseShift values(@shiftId,@employeeId,@date)";
DataTable dt = new DataTable();
DataSet ds;
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
ds = objDDBDataHelper.GetDataSet(query, SQLTextType.Query, lstparameter);
}
}
/// <summary>
/// Returns all type of leaves
/// </summary>
/// <returns>List of types of leaves(objects)</returns>
public List <Leaves> GetAllTypeOfLeaves()
{
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
DataTable dt = new DataTable();
DataSet ds;
int i = 0;
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
ds = objDDBDataHelper.GetDataSet("spGetAllTypeOfLeaves ", SQLTextType.Stored_Proc);
List <Leaves> lstLeaveType = new List <Leaves>();
foreach (DataRow rows in ds.Tables[0].Rows)
{
Leaves objLeaves = new Leaves();
objLeaves.Id = Convert.ToInt32(ds.Tables[0].Rows[i][0]);
objLeaves.LeaveName = (ds.Tables[0].Rows[i][1]).ToString();
lstLeaveType.Add(objLeaves);
i++;
}
return(lstLeaveType);
}
}
//public int GetEntryStatus(Attendance objAttendance)
//{
// DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
// DBDataHelper helper = new DBDataHelper();
// List<SqlParameter> lstAttendanceDetail = new List<SqlParameter>();
// lstAttendanceDetail.Add(new SqlParameter("@employeeId", objAttendance.EmployeeId));
// lstAttendanceDetail.Add(new SqlParameter("@dateTime", objAttendance.Date));
// DataTable dt = new DataTable();
// DataSet ds;
// using (DBDataHelper objDDBDataHelper = new DBDataHelper())
// {
// ds = objDDBDataHelper.GetDataSet("spGetAttendanceStatus", SQLTextType.Stored_Proc, lstAttendanceDetail);
// Employees objEmployee = new Employees();
// List<Employees> lstEmployee = new List<Employees>();
// return Convert.ToInt32(ds.Tables[0].Rows[0][0]);
// }
//}
//public int GetExitStatus(Attendance objAttendance)
//{
// DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
// DBDataHelper helper = new DBDataHelper();
// List<SqlParameter> lstAttendanceDetail = new List<SqlParameter>();
// lstAttendanceDetail.Add(new SqlParameter("@employeeId", objAttendance.EmployeeId));
// lstAttendanceDetail.Add(new SqlParameter("@dateTime", objAttendance.Date));
// DataSet ds;
// using (DBDataHelper objDDBDataHelper = new DBDataHelper())
// {
// ds = objDDBDataHelper.GetDataSet("spGetExitStatus", SQLTextType.Stored_Proc, lstAttendanceDetail);
// }
// var x = ds.Tables[0].Rows[0][0].ToString();
// return (String.IsNullOrEmpty(x)) ? 0 : 1;
//}
public bool EntryOrExitEmployee(Attendance objAttendance, out int status)
{
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
List<SqlParameter> lstAttendanceDetail = new List<SqlParameter>();
lstAttendanceDetail.Add(new SqlParameter("@employeeId", objAttendance.EmployeeId));
lstAttendanceDetail.Add(new SqlParameter("@dateTime", objAttendance.Date));
DataSet ds = new DataSet();
try
{
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
ds = objDDBDataHelper.GetDataSet("spAssignAttendance", SQLTextType.Stored_Proc, lstAttendanceDetail);
}
status = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
return true;
}
catch(Exception ex)
{
status = 0;
return false;
}
}
/// <summary>
/// Gives All Departments
/// </summary>
/// <returns> List of Department Objects</returns>
public List<Departments> GetAllDepartments()
{
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
DataTable dt = new DataTable();
DataSet ds;
int i = 0;
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
ds = objDDBDataHelper.GetDataSet("spGetAllDepartments", SQLTextType.Stored_Proc);
List<Departments> lstDepartment = new List<Departments>();
foreach (DataRow rows in ds.Tables[0].Rows)
{
Departments objDepartment = new Departments();
objDepartment.Id = Convert.ToInt32(ds.Tables[0].Rows[i][0]);
objDepartment.Name = (ds.Tables[0].Rows[i][1]).ToString();
lstDepartment.Add(objDepartment);
i++;
}
return lstDepartment;
}
}
public List<Employees> GetEmployeesByDepartment(int departmentId)
{
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
DataSet ds;
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
List<SqlParameter> lstEmployeeDetail = new List<SqlParameter>();
lstEmployeeDetail.Add(new SqlParameter("@departmentId", departmentId));
ds = objDDBDataHelper.GetDataSet("spGetAllEmployeesByDepartment", SQLTextType.Stored_Proc, lstEmployeeDetail);
List<Employees> lstEmployee = new List<Employees>();
int i = 0;
foreach (DataRow rows in ds.Tables[0].Rows)
{
Employees objEmployee = new Employees();
objEmployee.Id = Convert.ToInt32(ds.Tables[0].Rows[i][0]);
objEmployee.Name = ds.Tables[0].Rows[i][1].ToString();
objEmployee.RoleId = Convert.ToInt32(ds.Tables[0].Rows[i][2]);
objEmployee.RoleName = ds.Tables[0].Rows[i][3].ToString();
lstEmployee.Add(objEmployee);
i++;
}
return lstEmployee;
}
}
public TimeSpan GetDurationById(int Id)
{
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
DataTable dt = new DataTable();
DataSet ds;
List <SqlParameter> lstDurations = new List <SqlParameter>();
lstDurations.Add(new SqlParameter("@id", Id));
int i = 0;
TimeSpan duration = TimeSpan.Parse("00:00");
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
ds = objDDBDataHelper.GetDataSet("spGetDurationById", SQLTextType.Stored_Proc, lstDurations);
List <Duration> lstDuration = new List <Duration>();
Leaves objLeaves = new Leaves();
foreach (DataRow rows in ds.Tables[0].Rows)
{
duration = (TimeSpan)(ds.Tables[0].Rows[i][2]);
}
}
return(duration);
}
public bool UpdateDuration(int durationId, TimeSpan duration) // To be Edited
{
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
List <SqlParameter> lstDuration = new List <SqlParameter>();
lstDuration.Add(new SqlParameter("@durationId", durationId));
lstDuration.Add(new SqlParameter("@duration", duration));
lstDuration.Add(new SqlParameter("@updatedAt", DateTime.Now));
DataTable dt = new DataTable();
DataSet ds;
try
{
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
ds = objDDBDataHelper.GetDataSet("spUpdateDuration", SQLTextType.Stored_Proc, lstDuration);
}
return(true);
}
catch (Exception)
{
return(false);
}
}
public void CreateEmployee(Employees objEmployee)
{
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
List <SqlParameter> lstMasterEmployeeDetail = new List <SqlParameter>();
lstMasterEmployeeDetail.Add(new SqlParameter("@facultyId", objEmployee.Id));
lstMasterEmployeeDetail.Add(new SqlParameter("@name", objEmployee.Name));
lstMasterEmployeeDetail.Add(new SqlParameter("@joiningDate", objEmployee.JoiningDate));
lstMasterEmployeeDetail.Add(new SqlParameter("@gender", objEmployee.Gender));
lstMasterEmployeeDetail.Add(new SqlParameter("@createdOn", objEmployee.CreatedOn));
lstMasterEmployeeDetail.Add(new SqlParameter("@isDeleted", false));
DataTable dt = new DataTable();
DataSet ds;
int EmployeeId;
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
ds = objDDBDataHelper.GetDataSet("spCreateEmployee", SQLTextType.Stored_Proc, lstMasterEmployeeDetail);
EmployeeId = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
}
List <SqlParameter> lstEmployeeDetail = new List <SqlParameter>();
lstEmployeeDetail.Add(new SqlParameter("@employeeId", EmployeeId));
lstEmployeeDetail.Add(new SqlParameter("@roleId", objEmployee.RoleId));
lstEmployeeDetail.Add(new SqlParameter("@departmentId", objEmployee.DepartmentId));
lstEmployeeDetail.Add(new SqlParameter("@contactNumber", objEmployee.ContactNumber));
lstEmployeeDetail.Add(new SqlParameter("@weeklyOffDay", objEmployee.WeeklyOffDay));
lstEmployeeDetail.Add(new SqlParameter("@shiftId", objEmployee.ShiftId));
lstEmployeeDetail.Add(new SqlParameter("@createdAt", objEmployee.CreatedOn));
lstEmployeeDetail.Add(new SqlParameter("@updatedAt", objEmployee.UpdatedOn));
lstEmployeeDetail.Add(new SqlParameter("@isDeleted", false));
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
objDDBDataHelper.ExecSQL("spCreateEmployeeTransc", SQLTextType.Stored_Proc, lstEmployeeDetail);
}
}
public bool HalfDayLeave(int employeeId, DateTime date, int leaveId)
{
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
DBDataHelper helper = new DBDataHelper();
string query = "select Id from tblShifts where IsActive=1";
DataSet ds;
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
ds = objDDBDataHelper.GetDataSet(query, SQLTextType.Query);
}
int shiftId = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
List <SqlParameter> lstHalfDayLeave = new List <SqlParameter>();
lstHalfDayLeave.Add(new SqlParameter("@employeeId", employeeId));
lstHalfDayLeave.Add(new SqlParameter("@leaveTypeId", leaveId));
lstHalfDayLeave.Add(new SqlParameter("@date", date));
lstHalfDayLeave.Add(new SqlParameter("@shiftId", shiftId));
lstHalfDayLeave.Add(new SqlParameter("@shift", 1));
lstHalfDayLeave.Add(new SqlParameter("@createdAt", DateTime.Now));
lstHalfDayLeave.Add(new SqlParameter("@updatedAt", DateTime.Now));
DataTable dt = new DataTable();
try
{
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
objDDBDataHelper.ExecSQL("spAssignHalfDayLeave", SQLTextType.Stored_Proc, lstHalfDayLeave);
}
return(true);
}
catch (Exception)
{
return(false);
}
}
public bool IsEmployeeOnLeave(int employeeId, DateTime date, out string LeaveType)
{
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
DBDataHelper helper = new DBDataHelper();
List<SqlParameter> lstLeave1 = new List<SqlParameter>();
lstLeave1.Add(new SqlParameter("@employeeId", employeeId));
lstLeave1.Add(new SqlParameter("@date", date));
List<SqlParameter> lstLeave2 = new List<SqlParameter>();
lstLeave2.Add(new SqlParameter("@employeeId", employeeId));
lstLeave2.Add(new SqlParameter("@date", date));
List<SqlParameter> lstLeave3 = new List<SqlParameter>();
lstLeave3.Add(new SqlParameter("@employeeId", employeeId));
lstLeave3.Add(new SqlParameter("@date", date));
List<SqlParameter> lstLeave4 = new List<SqlParameter>();
lstLeave4.Add(new SqlParameter("@employeeId", employeeId));
lstLeave4.Add(new SqlParameter("@date", date));
List<SqlParameter> lstLeave5 = new List<SqlParameter>();
lstLeave5.Add(new SqlParameter("@employeeId", employeeId));
lstLeave5.Add(new SqlParameter("@date", date));
DataSet ds1, ds2, ds3, ds4, ds5 = new DataSet();
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
ds1 = objDDBDataHelper.GetDataSet("spIsEmployeeOnMultidayLeaveByDate", SQLTextType.Stored_Proc, lstLeave1);
ds2 = objDDBDataHelper.GetDataSet("spIsEmployeeOnHalfDayLeaveByDate", SQLTextType.Stored_Proc, lstLeave2);
ds3 = objDDBDataHelper.GetDataSet("spIsEmployeeOnFullDayLeaveByDate", SQLTextType.Stored_Proc, lstLeave3);
ds4 = objDDBDataHelper.GetDataSet("spIsEmployeeOnDurationalLeaveByDate", SQLTextType.Stored_Proc, lstLeave4);
}
int count1 = ds1.Tables.Count > 0 ? (ds1.Tables[0].Rows.Count > 0 ? Convert.ToInt32(ds1.Tables[0].Rows[0][0]) : 0) : 0;
int count2 = ds2.Tables.Count > 0 ? (ds2.Tables[0].Rows.Count > 0 ? Convert.ToInt32(ds2.Tables[0].Rows[0][0]) : 0) : 0;
int count3 = ds3.Tables.Count > 0 ? (ds3.Tables[0].Rows.Count > 0 ? Convert.ToInt32(ds3.Tables[0].Rows[0][0]) : 0) : 0;
int count4 = ds4.Tables.Count > 0 ? (ds4.Tables[0].Rows.Count > 0 ? Convert.ToInt32(ds4.Tables[0].Rows[0][0]) : 0) : 0;
if (count1 > 0 || count2 > 0 || count3 > 0 || count4 > 0)
{
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
ds5 = objDDBDataHelper.GetDataSet("spGetTypeOfLeaveOfEmployeeByDate", SQLTextType.Stored_Proc, lstLeave5);
}
LeaveType = ds5.Tables[0].Rows[0][0].ToString();
return true;
}
else
{
LeaveType = "No Leave";
return false;
}
}
public List<Employees> GetAllEmployees()
{
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
DataSet ds;
int i = 0;
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
ds = objDDBDataHelper.GetDataSet("spGetAllEmployees", SQLTextType.Stored_Proc);
List<Employees> lstEmployee = new List<Employees>();
foreach (DataRow rows in ds.Tables[0].Rows)
{
Employees objEmployee = new Employees();
objEmployee.Id = Convert.ToInt32(ds.Tables[0].Rows[i][0]);
objEmployee.Name = ds.Tables[0].Rows[i][1].ToString();
objEmployee.Gender = ds.Tables[0].Rows[i][2] == DBNull.Value ? "NULL" : ds.Tables[0].Rows[i][2].ToString();
objEmployee.JoiningDate = ds.Tables[0].Rows[i][3] == DBNull.Value ? new DateTime() : Convert.ToDateTime(ds.Tables[0].Rows[i][3]);
objEmployee.ImagePath = ds.Tables[0].Rows[i][4] == DBNull.Value ? "NULL" : ds.Tables[0].Rows[i][4].ToString();
objEmployee.ContactNumber = ds.Tables[0].Rows[i][5] == DBNull.Value ? new Int64() : Convert.ToInt64(ds.Tables[0].Rows[i][5]);
objEmployee.Password = ds.Tables[0].Rows[i][6] == DBNull.Value ? "NULL" : ds.Tables[0].Rows[i][6].ToString();
objEmployee.DepartmentId = ds.Tables[0].Rows[i][9] == DBNull.Value ? new Int32() : Convert.ToInt32(ds.Tables[0].Rows[i][9]);
objEmployee.DepartmentName = ds.Tables[0].Rows[i][10] == DBNull.Value ? "NULL" : ds.Tables[0].Rows[i][10].ToString();
objEmployee.RoleId = ds.Tables[0].Rows[i][7] == DBNull.Value ? new Int32() : Convert.ToInt32(ds.Tables[0].Rows[i][7]); ;
objEmployee.RoleName = ds.Tables[0].Rows[i][8] == DBNull.Value ? "NULL" : ds.Tables[0].Rows[i][8].ToString();
objEmployee.WeeklyOffDay = ds.Tables[0].Rows[i][11] == DBNull.Value ? 1 : Convert.ToInt32(ds.Tables[0].Rows[i][11]);
objEmployee.ShiftId = ds.Tables[0].Rows[i][12] == DBNull.Value ? 1 : Convert.ToInt32(ds.Tables[0].Rows[i][12]);
lstEmployee.Add(objEmployee);
i++;
}
return lstEmployee;
}
}
public bool HalfDayLeave(int employeeId, DateTime date, int leaveId)
{
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
DBDataHelper helper = new DBDataHelper();
string query = "select Id from tblShifts where IsActive=1";
DataSet ds;
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
ds = objDDBDataHelper.GetDataSet(query, SQLTextType.Query);
}
int shiftId = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
List<SqlParameter> lstHalfDayLeave = new List<SqlParameter>();
lstHalfDayLeave.Add(new SqlParameter("@employeeId", employeeId));
lstHalfDayLeave.Add(new SqlParameter("@leaveTypeId", leaveId));
lstHalfDayLeave.Add(new SqlParameter("@date", date));
lstHalfDayLeave.Add(new SqlParameter("@shiftId", shiftId));
lstHalfDayLeave.Add(new SqlParameter("@shift", 1));
lstHalfDayLeave.Add(new SqlParameter("@createdAt", DateTime.Now));
lstHalfDayLeave.Add(new SqlParameter("@updatedAt", DateTime.Now));
DataTable dt = new DataTable();
try
{
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
objDDBDataHelper.ExecSQL("spAssignHalfDayLeave", SQLTextType.Stored_Proc, lstHalfDayLeave);
}
return true;
}
catch (Exception)
{
return false;
}
}
public bool UpdateDuration(int durationId, TimeSpan duration) // To be Edited
{
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
List<SqlParameter> lstDuration = new List<SqlParameter>();
lstDuration.Add(new SqlParameter("@durationId", durationId));
lstDuration.Add(new SqlParameter("@duration", duration));
lstDuration.Add(new SqlParameter("@updatedAt", DateTime.Now));
DataTable dt = new DataTable();
DataSet ds;
try
{
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
ds = objDDBDataHelper.GetDataSet("spUpdateDuration", SQLTextType.Stored_Proc, lstDuration);
}
return true;
}
catch(Exception)
{
return false;
}
}
public List<LeaveAssignedPerSession> ShowLeaveDetailsOfEmployeeByDepartment(int departmentId)
{
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
List<SqlParameter> lstDetails = new List<SqlParameter>();
lstDetails.Add(new SqlParameter("@departmentId", departmentId));
DataTable dt = new DataTable();
DataSet ds;
int i = 0;
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
ds = objDDBDataHelper.GetDataSet("spAssignLeaveByFaculty", SQLTextType.Stored_Proc, lstDetails);
List<LeaveAssignedPerSession> lstLeaveAssignedPerSession = new List<LeaveAssignedPerSession>();
foreach (DataRow rows in ds.Tables[0].Rows)
{
LeaveAssignedPerSession objLeaveAssignedPerSession = new LeaveAssignedPerSession();
objLeaveAssignedPerSession.EmployeeName = (ds.Tables[0].Rows[i][0]).ToString();
objLeaveAssignedPerSession.leaveType = Convert.ToInt32(ds.Tables[0].Rows[i][1]);
objLeaveAssignedPerSession.leaveCount = Convert.ToInt32(ds.Tables[0].Rows[i][2]);
lstLeaveAssignedPerSession.Add(objLeaveAssignedPerSession);
i++;
}
return lstLeaveAssignedPerSession;
}
}
public bool GetLeavesAssignedByRoleById(int id, out LeaveAssignedByRole objLeaves)
{
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
List<SqlParameter> lstparameter = new List<SqlParameter>();
lstparameter.Add(new SqlParameter("@id", id));
DataTable dt = new DataTable();
DataSet ds;
int i = 0;
string query = "Select * from tblLeaveAssignedByRole where Id = @id";
LeaveAssignedByRole objLeaves1 = new LeaveAssignedByRole();
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
ds = objDDBDataHelper.GetDataSet(query, SQLTextType.Query, lstparameter);
List<LeaveAssignedByRole> lstLeaveType = new List<LeaveAssignedByRole>();
Leaves objLeave = new Leaves();
foreach (DataRow rows in ds.Tables[0].Rows)
{
objLeaves1.Id = Convert.ToInt32(ds.Tables[0].Rows[i][0]);
objLeaves1.RoleId = Convert.ToInt32(ds.Tables[0].Rows[i][1]);
objLeaves1.LeaveTypeId = Convert.ToInt32(ds.Tables[0].Rows[i][2]);
objLeaves1.NoOfLeaves = Convert.ToInt32(ds.Tables[0].Rows[i][3]);
objLeaves1.IsPromoted = Convert.ToBoolean(ds.Tables[0].Rows[i][4]);
GetLeavesById(objLeaves1.LeaveTypeId, out objLeave);
objLeaves1.LeaveName = objLeave.LeaveName;
objLeaves1.RoleName = GetRoleById(objLeaves1.RoleId);
lstLeaveType.Add(objLeaves1);
i++;
}
objLeaves = objLeaves1;
return true;
}
}
public TimeSpan GetDurationById(int Id)
{
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
DataTable dt = new DataTable();
DataSet ds;
List<SqlParameter> lstDurations = new List<SqlParameter>();
lstDurations.Add(new SqlParameter("@id", Id));
int i = 0;
TimeSpan duration = TimeSpan.Parse("00:00");
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
ds = objDDBDataHelper.GetDataSet("spGetDurationById", SQLTextType.Stored_Proc, lstDurations);
List<Duration> lstDuration = new List<Duration>();
Leaves objLeaves = new Leaves();
foreach (DataRow rows in ds.Tables[0].Rows)
{
duration = (TimeSpan)(ds.Tables[0].Rows[i][2]);
}
}
return duration;
}
public void GetShiftsById(int Id, out MasterShifts objShift) //to be edited
{
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
DataTable dt = new DataTable();
List<SqlParameter> lst = new List<SqlParameter>();
lst.Add(new SqlParameter("@id", Id));
DataSet ds;
int i = 0;
MasterShifts objShifts1 = new MasterShifts();
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
ds = objDDBDataHelper.GetDataSet("spGetShiftsById", SQLTextType.Stored_Proc, lst);
List<MasterShifts> lstShifts = new List<MasterShifts>();
foreach (DataRow rows in ds.Tables[0].Rows)
{
objShifts1.Id = Convert.ToInt32(ds.Tables[0].Rows[i][0]);
objShifts1.Name = ds.Tables[0].Rows[i][1].ToString();
objShifts1.FirstHalfStart = TimeSpan.Parse(ds.Tables[0].Rows[i][2].ToString());
objShifts1.FirstHalfEnd = TimeSpan.Parse(ds.Tables[0].Rows[i][3].ToString());
objShifts1.SecondHalfStart = TimeSpan.Parse(ds.Tables[0].Rows[i][4].ToString());
objShifts1.SecondHalfEnd = TimeSpan.Parse(ds.Tables[0].Rows[i][5].ToString());
objShifts1.SHLDuration = TimeSpan.Parse(ds.Tables[0].Rows[i][6].ToString());
i++;
}
objShift = objShifts1;
}
}
/// <summary>
/// Get all Durations
/// </summary>
/// <returns>List of Duration objects</returns>
public List<Duration> GetAllDurations()
{
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
DataTable dt = new DataTable();
DataSet ds;
int i = 0;
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
ds = objDDBDataHelper.GetDataSet("spGetAllDurations", SQLTextType.Stored_Proc);
List<Duration> lstDuration = new List<Duration>();
Leaves objLeaves = new Leaves();
foreach (DataRow rows in ds.Tables[0].Rows)
{
Duration objDuration = new Duration();
objDuration.Id = Convert.ToInt32(ds.Tables[0].Rows[i][0]);
objDuration.leaveId = Convert.ToInt32(ds.Tables[0].Rows[i][1]);
GetLeavesById(objDuration.leaveId, out objLeaves);
objDuration.leaveName = objLeaves.LeaveName;
objDuration.duration = (TimeSpan)(ds.Tables[0].Rows[i][2]);
lstDuration.Add(objDuration);
i++;
}
return lstDuration;
}
}
public bool GetLeavesById(int leaveId, out Leaves objLeaves)
{
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
List<SqlParameter> lstLeave = new List<SqlParameter>();
lstLeave.Add(new SqlParameter("@leaveId", leaveId));
DataTable dt = new DataTable();
DataSet ds;
int i = 0;
Leaves objLeaves1 = new Leaves();
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
ds = objDDBDataHelper.GetDataSet("spGetTypeOfLeaveById ", SQLTextType.Stored_Proc, lstLeave);
List<Leaves> lstLeaveType = new List<Leaves>();
foreach (DataRow rows in ds.Tables[0].Rows)
{
objLeaves1.Id = leaveId;
objLeaves1.LeaveName = (ds.Tables[0].Rows[i][0]).ToString();
lstLeaveType.Add(objLeaves1);
i++;
}
objLeaves = objLeaves1;
return true;
}
}
/// <summary>
/// Get all shifts
/// </summary>
/// <returns>List of Shifts object</returns>
public List<MasterShifts> GetAllShifts()
{
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
DataTable dt = new DataTable();
DataSet ds;
int i = 0;
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
ds = objDDBDataHelper.GetDataSet("spGetAllShifts", SQLTextType.Stored_Proc);
List<MasterShifts> lstShifts = new List<MasterShifts>();
foreach (DataRow rows in ds.Tables[0].Rows)
{
MasterShifts objShifts = new MasterShifts();
objShifts.Id = Convert.ToInt32(ds.Tables[0].Rows[i][0]);
objShifts.Name = ds.Tables[0].Rows[i][1].ToString();
objShifts.FirstHalfStart = TimeSpan.Parse(ds.Tables[0].Rows[i][2].ToString());
objShifts.FirstHalfEnd = TimeSpan.Parse(ds.Tables[0].Rows[i][3].ToString());
objShifts.SecondHalfStart = TimeSpan.Parse(ds.Tables[0].Rows[i][4].ToString());
objShifts.SecondHalfEnd = TimeSpan.Parse(ds.Tables[0].Rows[i][5].ToString());
objShifts.SHLDuration = TimeSpan.Parse(ds.Tables[0].Rows[i][6].ToString());
objShifts.IsActive = Convert.ToBoolean(ds.Tables[0].Rows[i][7]);
lstShifts.Add(objShifts);
i++;
}
return lstShifts;
}
}
/// <summary>
///
/// </summary>
/// <param name="departmentId"></param>
/// <returns></returns>
public string GetRoleById(int roleId)
{
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
List<SqlParameter> lstRole = new List<SqlParameter>();
lstRole.Add(new SqlParameter("@roleId", roleId));
DataTable dt = new DataTable();
DataSet ds;
string role;
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
ds = objDDBDataHelper.GetDataSet("spGetRoleById", SQLTextType.Stored_Proc, lstRole);
role = ds.Tables[0].Rows[0][0].ToString();
}
return role;
}
public List<LeaveAssignedByRole> GetAllTypeOfLeavesAssignedByRole()
{
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
DataTable dt = new DataTable();
DataSet ds;
int i = 0;
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
ds = objDDBDataHelper.GetDataSet("spGetAllTypeOfLeavesAssignedByRole", SQLTextType.Stored_Proc);
List<LeaveAssignedByRole> lstLeaveType = new List<LeaveAssignedByRole>();
foreach (DataRow rows in ds.Tables[0].Rows)
{
LeaveAssignedByRole objLeaves = new LeaveAssignedByRole();
objLeaves.Id = Convert.ToInt32(ds.Tables[0].Rows[i][0]);
objLeaves.RoleId = Convert.ToInt32(ds.Tables[0].Rows[i][1]);
objLeaves.LeaveTypeId = Convert.ToInt32(ds.Tables[0].Rows[i][2]);
objLeaves.NoOfLeaves = Convert.ToInt32(ds.Tables[0].Rows[i][3]);
objLeaves.IsPromoted = Convert.ToBoolean(ds.Tables[0].Rows[i][4]);
objLeaves.RoleName = (ds.Tables[0].Rows[i][5]).ToString();
objLeaves.LeaveName = (ds.Tables[0].Rows[i][6]).ToString();
lstLeaveType.Add(objLeaves);
i++;
}
return lstLeaveType;
}
}
public bool UpdateLeavesAssignedByRole(int id, int noOfLeaves, bool isPromoted)
{
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
List<SqlParameter> lstparameter = new List<SqlParameter>();
lstparameter.Add(new SqlParameter("@id", id));
lstparameter.Add(new SqlParameter("@noOfLeaves", noOfLeaves));
lstparameter.Add(new SqlParameter("@isPromoted", isPromoted));
lstparameter.Add(new SqlParameter("@updatedAt", DateTime.Now));
string query = "Update tblLeaveAssignedByRole set NoOfLeaves = @noOfLeaves , IsPromoted = @isPromoted, UpdatedAt = @updatedAt where Id=@id";
DataTable dt = new DataTable();
DataSet ds;
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
ds = objDDBDataHelper.GetDataSet(query, SQLTextType.Query, lstparameter);
}
return true;
}
protected int LeaveExists(DateTime date)
{
MasterEntries objMasterEntries = new MasterEntries();
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
List<SqlParameter> lstData = new List<SqlParameter>();
lstData.Add(new SqlParameter("@employeeId", Convert.ToInt32(Session["employeeId"])));
lstData.Add(new SqlParameter("@date", date));
lstData.Add(new SqlParameter("@IsDeleted", Convert.ToInt32(0)));
DataSet ds;
string query = @"SELECT Count(Id)
FROM tblLeave
WHERE [Date] = @date
AND EmployeeId = @employeeId
AND IsDeleted = @IsDeleted";
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
ds = objDDBDataHelper.GetDataSet(query, SQLTextType.Query, lstData);
List<AssignLeaveViewModel> lstLeaves = new List<AssignLeaveViewModel>();
int number = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
return number;
}
}
public Employees GetEmployeeById(long Id)
{
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
DBDataHelper helper = new DBDataHelper();
List<SqlParameter> lstEmployeeDetail = new List<SqlParameter>();
lstEmployeeDetail.Add(new SqlParameter("@employeeId", Id));
DataSet ds;
int i = 0;
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
ds = objDDBDataHelper.GetDataSet("spGetEmployeeById", SQLTextType.Stored_Proc, lstEmployeeDetail);
Employees objEmployee = new Employees();
objEmployee.Id = Convert.ToInt32(ds.Tables[0].Rows[i][0]);
objEmployee.Name = ds.Tables[0].Rows[i][1].ToString();
objEmployee.Gender = ds.Tables[0].Rows[i][2] == DBNull.Value ? "NULL" : ds.Tables[0].Rows[i][2].ToString();
objEmployee.JoiningDate = ds.Tables[0].Rows[i][3] == DBNull.Value ? new DateTime() : Convert.ToDateTime(ds.Tables[0].Rows[i][3]);
objEmployee.ImagePath = ds.Tables[0].Rows[i][4] == DBNull.Value ? "NULL" : ds.Tables[0].Rows[i][4].ToString();
objEmployee.ContactNumber = ds.Tables[0].Rows[i][5] == DBNull.Value ? new Int64() : Convert.ToInt64(ds.Tables[0].Rows[i][5]);
objEmployee.Password = ds.Tables[0].Rows[i][6] == DBNull.Value ? "NULL" : ds.Tables[0].Rows[i][6].ToString();
objEmployee.DepartmentId = ds.Tables[0].Rows[i][9] == DBNull.Value ? new Int32() : Convert.ToInt32(ds.Tables[0].Rows[i][9]);
objEmployee.DepartmentName = ds.Tables[0].Rows[i][10] == DBNull.Value ? "NULL" : ds.Tables[0].Rows[i][10].ToString();
objEmployee.RoleId = ds.Tables[0].Rows[i][7] == DBNull.Value ? new Int32() : Convert.ToInt32(ds.Tables[0].Rows[i][7]); ;
objEmployee.RoleName = ds.Tables[0].Rows[i][8] == DBNull.Value ? "NULL" : ds.Tables[0].Rows[i][8].ToString();
objEmployee.WeeklyOffDay = ds.Tables[0].Rows[i][11] == DBNull.Value ? 1 : Convert.ToInt32(ds.Tables[0].Rows[i][11]);
objEmployee.ShiftId = ds.Tables[0].Rows[i][12] == DBNull.Value ? 1 : Convert.ToInt32(ds.Tables[0].Rows[i][12]);
return objEmployee;
}
}
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 int Count(int roleId, int leaveId)
{
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
DataTable dt = new DataTable();
List<SqlParameter> lstLeaveAssignedByRole = new List<SqlParameter>();
lstLeaveAssignedByRole.Add(new SqlParameter("@leaveId", leaveId));
lstLeaveAssignedByRole.Add(new SqlParameter("@roleId", roleId));
DataSet ds;
int i;
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
ds = objDDBDataHelper.GetDataSet("IsSingle", SQLTextType.Stored_Proc, lstLeaveAssignedByRole);
i = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
}
return i;
}