public bool FullDayLeave(int employeeId, DateTime date, int leaveTypeId)
{
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
DBDataHelper helper = new DBDataHelper();
List <SqlParameter> lstFullDayLeave = new List <SqlParameter>();
lstFullDayLeave.Add(new SqlParameter("@employeeId", employeeId));
lstFullDayLeave.Add(new SqlParameter("@leaveTypeId", leaveTypeId));
lstFullDayLeave.Add(new SqlParameter("@createdAt", DateTime.Now));
lstFullDayLeave.Add(new SqlParameter("@updatedAt", DateTime.Now));
lstFullDayLeave.Add(new SqlParameter("@date", date));
DataTable dt = new DataTable();
try
{
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
objDDBDataHelper.ExecSQL("spAssignFullDayLeave", SQLTextType.Stored_Proc, lstFullDayLeave);
}
return(true);
}
catch (Exception)
{
return(false);
}
}
public bool UpdateEmployee(Employees objEmployee)
{
List<SqlParameter> lstEmployeeDetail = new List<SqlParameter>();
lstEmployeeDetail.Add(new SqlParameter("@employeeId", objEmployee.Id));
lstEmployeeDetail.Add(new SqlParameter("@gender", objEmployee.Gender));
lstEmployeeDetail.Add(new SqlParameter("@joiningDate", objEmployee.JoiningDate));
lstEmployeeDetail.Add(new SqlParameter("@isDeleted", false));
lstEmployeeDetail.Add(new SqlParameter("@updatedOn", DateTime.Now));
lstEmployeeDetail.Add(new SqlParameter("@password", objEmployee.Password));
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));
DataTable dt = new DataTable();
try
{
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
objDDBDataHelper.ExecSQL("spUpdateEmployeeByEmployeeId", SQLTextType.Stored_Proc, lstEmployeeDetail);
}
return true;
}
catch(Exception ex)
{
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 UpdateEmployee(Employees objEmployee)
{
List <SqlParameter> lstEmployeeDetail = new List <SqlParameter>();
lstEmployeeDetail.Add(new SqlParameter("@employeeId", objEmployee.Id));
lstEmployeeDetail.Add(new SqlParameter("@gender", objEmployee.Gender));
lstEmployeeDetail.Add(new SqlParameter("@joiningDate", objEmployee.JoiningDate));
lstEmployeeDetail.Add(new SqlParameter("@isDeleted", false));
lstEmployeeDetail.Add(new SqlParameter("@updatedOn", DateTime.Now));
lstEmployeeDetail.Add(new SqlParameter("@password", objEmployee.Password));
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));
DataTable dt = new DataTable();
try
{
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
objDDBDataHelper.ExecSQL("spUpdateEmployeeByEmployeeId", SQLTextType.Stored_Proc, lstEmployeeDetail);
}
return(true);
}
catch (Exception ex)
{
return(false);
}
}
/// <summary>
/// Add a new duration entry
/// </summary>
/// <param name="duration">TimeSpan value for duration</param>
/// <returns>True if entry is successfull, else False</returns>
///
public bool AddDuration(TimeSpan duration, int leaveId)
{
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
DBDataHelper helper = new DBDataHelper();
List <SqlParameter> lstDuration = new List <SqlParameter>();
lstDuration.Add(new SqlParameter("@duration", duration));
lstDuration.Add(new SqlParameter("@leaveId", leaveId));
lstDuration.Add(new SqlParameter("@createdOn", DateTime.Now));
lstDuration.Add(new SqlParameter("@updatedOn", DateTime.Now));
lstDuration.Add(new SqlParameter("@isActive", false));
lstDuration.Add(new SqlParameter("@isDeleted", false));
DataTable dt = new DataTable();
try
{
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
objDDBDataHelper.ExecSQL("spAddDuration", SQLTextType.Stored_Proc, lstDuration);
}
return(true);
}
catch (Exception)
{
return(false);
}
}
protected void btnUpdate_Click(object sender, EventArgs e)
{
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
List <SqlParameter> lstData = new List <SqlParameter>();
lstData.Add(new SqlParameter("@employeeId", Convert.ToInt32(Session["id"])));
lstData.Add(new SqlParameter("@sickleave", Convert.ToInt32(txtEditSL.Text)));
lstData.Add(new SqlParameter("@emergencyLeave", Convert.ToInt32(txtEditEL.Text)));
if (ddlDate.SelectedValue == "0")
{
lstData.Add(new SqlParameter("@sessionstart", new DateTime(2015, 08, 01)));
lstData.Add(new SqlParameter("@sessionend", new DateTime(2016, 07, 31)));
}
else
{
lstData.Add(new SqlParameter("@sessionstart", new DateTime(2016, 08, 01)));
lstData.Add(new SqlParameter("@sessionend", new DateTime(2017, 07, 31)));
}
string query = "Insert into tblLeavesOldStock values(@employeeId,@sickleave,@emergencyLeave,@sessionstart,@sessionend)";
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
objDDBDataHelper.ExecSQL(query, SQLTextType.Query, lstData);
}
BindData();
}
public bool UpdateShifts(int shiftId, MasterShifts objShift)
{
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
List <SqlParameter> lstShift = new List <SqlParameter>();
lstShift.Add(new SqlParameter("@shiftId", shiftId));
lstShift.Add(new SqlParameter("@name", objShift.Name));
lstShift.Add(new SqlParameter("@firstHalfStart", objShift.FirstHalfStart));
lstShift.Add(new SqlParameter("@firstHalfEnd", objShift.FirstHalfEnd));
lstShift.Add(new SqlParameter("@secondHalfStart", objShift.SecondHalfStart));
lstShift.Add(new SqlParameter("@secondHalfEnd", objShift.SecondHalfEnd));
lstShift.Add(new SqlParameter("@shlDuration", objShift.SHLDuration));
lstShift.Add(new SqlParameter("@updatedOn", DateTime.Now));
DataTable dt = new DataTable();
DataSet ds;
try
{
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
objDDBDataHelper.ExecSQL("spUpdateShift", SQLTextType.Stored_Proc, lstShift);
}
return(true);
}
catch (Exception)
{
return(false);
}
}
/// <summary>
/// Add a new type of Role
/// </summary>
/// <param name="Role">String Value for name of Role</param>
/// <returns>True if entry is successfull else False</returns>
public bool AddRole(string Role)
{
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
List <SqlParameter> lstRole = new List <SqlParameter>();
lstRole.Add(new SqlParameter("@name", Role));
lstRole.Add(new SqlParameter("@createdOn", DateTime.Now));
lstRole.Add(new SqlParameter("@updatedOn", DateTime.Now));
lstRole.Add(new SqlParameter("@isDeleted", false));
DataTable dt = new DataTable();
try
{
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
objDDBDataHelper.ExecSQL("spAddRole", SQLTextType.Stored_Proc, lstRole);
}
return(true);
}
catch (Exception)
{
return(false);
}
}
public bool LeaveAssignedByRole(int leaveId, int roleId, int noOfLeaves, int isPromoted)
{
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
DBDataHelper helper = new DBDataHelper();
List <SqlParameter> lstLeaveAssignedByRole = new List <SqlParameter>();
lstLeaveAssignedByRole.Add(new SqlParameter("@leaveId", leaveId));
lstLeaveAssignedByRole.Add(new SqlParameter("@roleId", roleId));
lstLeaveAssignedByRole.Add(new SqlParameter("@noOfLeaves", noOfLeaves));
lstLeaveAssignedByRole.Add(new SqlParameter("@isPromoted", Convert.ToBoolean(isPromoted)));
lstLeaveAssignedByRole.Add(new SqlParameter("@createdAt", DateTime.Now));
DataTable dt = new DataTable();
try
{
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
objDDBDataHelper.ExecSQL("spAssignLeaveByRole", SQLTextType.Stored_Proc, lstLeaveAssignedByRole);
}
return(true);
}
catch (Exception)
{
return(false);
}
}
public bool DurationalLeave(int employeeId, int leaveId, DateTime date)
{
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
DBDataHelper helper = new DBDataHelper();
string query = "select Id from tblDuration where IsActive=1";
DataSet ds;
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
ds = objDDBDataHelper.GetDataSet(query, SQLTextType.Query);
}
int durationId = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
List <SqlParameter> lstDurationalLeave = new List <SqlParameter>();
lstDurationalLeave.Add(new SqlParameter("@employeeId", employeeId));
lstDurationalLeave.Add(new SqlParameter("@leaveTypeId", leaveId));
lstDurationalLeave.Add(new SqlParameter("@date", date));
lstDurationalLeave.Add(new SqlParameter("@DurationalId", durationId));
lstDurationalLeave.Add(new SqlParameter("@createdAt", DateTime.Now));
lstDurationalLeave.Add(new SqlParameter("@updatedAt", DateTime.Now));
DataTable dt = new DataTable();
try
{
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
objDDBDataHelper.ExecSQL("spAssignDurationalLeave", SQLTextType.Stored_Proc, lstDurationalLeave);
}
return(true);
}
catch (Exception)
{
return(false);
}
}
protected void lnkAddSession_Click(object sender, EventArgs e)
{
string confirmValue = Request.Form["confirm_value"];
if (confirmValue == "Yes")
{
#region Add_Session
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
DateTime sessionStartDate = new DateTime(Int32.Parse(txtSessionStart.Text), 8, 1);
DateTime sessionEndDate = new DateTime(sessionStartDate.Year + 1, 7, 31);
string query = "Select Count(*) from tblSession Where SessionStartDate=@sessionStartDate";
List <SqlParameter> lstParams = new List <SqlParameter>();
lstParams.Add(new SqlParameter("@sessionStartDate", sessionStartDate));
DataTable ds = new DataTable();
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
ds = objDDBDataHelper.GetDataTable(query, SQLTextType.Query, lstParams);
}
if (ds.Rows.Count != 0)
{
if (Int32.Parse(ds.Rows[0][0].ToString()) == 0)
{
string addSession = @"INSERT INTO [dbo].[tblSession]
([SessionStartDate]
,[SessionEndDate])
VALUES (@sessionStartDate,@sessionEndDate)";
List <SqlParameter> lstParams2 = new List <SqlParameter>();
lstParams2.Add(new SqlParameter("@sessionStartDate", sessionStartDate));
lstParams2.Add(new SqlParameter("@sessionEndDate", sessionEndDate));
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
objDDBDataHelper.ExecSQL(addSession, SQLTextType.Query, lstParams2);
}
ManageReports objManageReprts = new ManageReports();
objManageReprts.AssignSessionWiseLeave(sessionStartDate);
objManageReprts.UpdateLeaveBalanceTable(sessionStartDate);
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alertMessage", "alert('Session Added')", true);
}
else
{
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alertMessage", "alert('Session Exists')", true);
}
}
#endregion
}
else
{
this.Page.ClientScript.RegisterStartupScript(this.GetType(), "alert", "alert('You Cancelled')", true);
}
}
public void AssignLeave(DateTime date)
{
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("@date", date));
lstData.Add(new SqlParameter("@leaveTypeId", Convert.ToInt32(ddlLeaves.SelectedValue)));
lstData.Add(new SqlParameter("@createdAt", DateTime.Now));
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
objDDBDataHelper.ExecSQL("spAssignLeave", SQLTextType.Stored_Proc, lstData);
}
}
protected void btnAddSession_Click(object sender, EventArgs e)
{
#region Add_Session
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
DateTime sessionStartDate = new DateTime(Int32.Parse(DateTime.Now.Year.ToString()), 8, 1);
DateTime sessionEndDate = new DateTime(sessionStartDate.Year + 1, 7, 31);
string query = "Select Count(*) from tblSession Where SessionStartDate=@sessionStartDate";
List<SqlParameter> lstParams = new List<SqlParameter>();
lstParams.Add(new SqlParameter("@sessionStartDate", sessionStartDate));
DataTable ds = new DataTable();
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
ds = objDDBDataHelper.GetDataTable(query, SQLTextType.Query, lstParams);
}
if (ds.Rows.Count != 0)
if (Int32.Parse(ds.Rows[0][0].ToString()) == 0)
{
string addSession = @"INSERT INTO [dbo].[tblSession]
([SessionStartDate]
,[SessionEndDate])
VALUES (@sessionStartDate,@sessionEndDate)";
List<SqlParameter> lstParams2 = new List<SqlParameter>();
lstParams2.Add(new SqlParameter("@sessionStartDate", sessionStartDate));
lstParams2.Add(new SqlParameter("@sessionEndDate", sessionEndDate));
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
objDDBDataHelper.ExecSQL(addSession, SQLTextType.Query, lstParams2);
}
ManageReports objManageReprts = new ManageReports();
objManageReprts.AssignSessionWiseLeave(sessionStartDate);
objManageReprts.UpdateLeaveBalanceTable(sessionStartDate);
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alertMessage", "alert('Session Added')", true);
}
else
{
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alertMessage", "alert('Session Exists')", true);
}
#endregion
}
public void Add(Day day)
{
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
List <SqlParameter> list_params = new List <SqlParameter>()
{
new SqlParameter("@date", day.Date), new SqlParameter("@status", day.Status)
};
try
{
using (DBDataHelper helper = new DBDataHelper())
{
helper.ExecSQL("Insert into [tblSpecialDays] values (@date,@status)", SQLTextType.Query, list_params);
}
}
catch (Exception)
{
}
}
public void Add(Day day)
{
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
List<SqlParameter> list_params = new List<SqlParameter>() { new SqlParameter("@date", day.Date), new SqlParameter("@status", day.Status) };
try
{
using (DBDataHelper helper = new DBDataHelper())
{
helper.ExecSQL("Insert into [tblSpecialDays] values (@date,@status)", SQLTextType.Query, list_params);
}
}
catch(Exception)
{
}
}
protected void btnAddHoliday_Click(object sender, EventArgs e)
{
string query = "INSERT INTO [tblHolidays] VALUES (@date,@status,@nameOfHoliday)";
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
DataTable dt = new DataTable();
List<SqlParameter> lstData = new List<SqlParameter>();
lstData.Add(new SqlParameter("@date", Calendar1.SelectedDate.Date.ToString("d")));
lstData.Add(new SqlParameter("@nameOfHoliday", txtHoliday.Text == "" ? ((Convert.ToInt32(ddlStatus.SelectedValue)) == (int)DayStatus.Holiday ? DayStatus.Holiday.ToString() : "Weekly Off") : (txtHoliday.Text)));
lstData.Add(new SqlParameter("@status", ddlStatus.SelectedValue));
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
objDDBDataHelper.ExecSQL(query, SQLTextType.Query, lstData);
}
ScriptManager.RegisterStartupScript(this, this.GetType(), "script", "alert('Holiday Added Successfully...');", true);
btnAddHoliday.Enabled = false;
btnAddHoliday.Text = "Holiday Exists";
txtHoliday.Text = dt.Rows[0][0] == DBNull.Value ? "" : dt.Rows[0][0].ToString();
BindData();
}
public bool AssignLeave(int employeeId, DateTime date, int leaveTypeId)
{
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
DBDataHelper helper = new DBDataHelper();
List <SqlParameter> lst_params = new List <SqlParameter>();
lst_params.Add(new SqlParameter("@date", date));
lst_params.Add(new SqlParameter("@employeeId", employeeId));
string query = "SELECT Count([EmployeeId]) FROM [tblLeave] Where [Date] = @date AND [EmployeeId] =@employeeId AND [isDeleted]=0";
DataTable dt = new DataTable();
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
dt = objDDBDataHelper.GetDataTable(query, SQLTextType.Query, lst_params);
}
if (Int32.Parse(dt.Rows[0][0].ToString()) == 0)
{
return(false);
}
else
{
List <SqlParameter> lst_params1 = new List <SqlParameter>();
lst_params1.Add(new SqlParameter("@employeeId", employeeId));
lst_params1.Add(new SqlParameter("@date", date));
lst_params1.Add(new SqlParameter("@leaveTypeId", leaveTypeId));
lst_params1.Add(new SqlParameter("@createdAt", DateTime.Now));
try
{
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
objDDBDataHelper.ExecSQL("spAssignLeave", SQLTextType.Stored_Proc, lst_params1);
}
return(true);
}
catch (Exception)
{
return(false);
}
}
}
public bool DeleteEmployee(long employeeId)
{
List <SqlParameter> lstEmployeeDetail = new List <SqlParameter>();
lstEmployeeDetail.Add(new SqlParameter("@employeeId", employeeId));
DataTable dt = new DataTable();
try
{
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
objDDBDataHelper.ExecSQL("spDeleteEmployee", SQLTextType.Stored_Proc, lstEmployeeDetail);
}
return(true);
}
catch (Exception)
{
return(false);
}
}
protected void btnDelete_Click(object sender, EventArgs e)
{
Button btn = (Button)sender;
int id = Convert.ToInt32(btn.CommandArgument);
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
DataTable dt = new DataTable();
List <SqlParameter> lstData = new List <SqlParameter>();
lstData.Add(new SqlParameter("@id", id));
DataSet ds;
string query = "DELETE FROM tblHolidays WHERE Id=@id";
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
objDDBDataHelper.ExecSQL(query, SQLTextType.Query, lstData);
}
ScriptManager.RegisterStartupScript(this, this.GetType(), "script", "alert('Holiday Deleted Successfully...');", true);
BindData();
}
public bool DeleteDuration(int durationId)
{
List <SqlParameter> lstDuration = new List <SqlParameter>();
lstDuration.Add(new SqlParameter("@durationId", durationId));
lstDuration.Add(new SqlParameter("@updatedAt", DateTime.Now));
DataTable dt = new DataTable();
try
{
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
objDDBDataHelper.ExecSQL("spDeleteDuration", SQLTextType.Stored_Proc, lstDuration);
}
return(true);
}
catch (Exception)
{
return(false);
}
}
protected void btnAddHoliday_Click(object sender, EventArgs e)
{
string query = "INSERT INTO [tblHolidays] VALUES (@date,@status,@nameOfHoliday)";
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
DataTable dt = new DataTable();
List <SqlParameter> lstData = new List <SqlParameter>();
lstData.Add(new SqlParameter("@date", Calendar1.SelectedDate.Date.ToString("d")));
lstData.Add(new SqlParameter("@nameOfHoliday", txtHoliday.Text == "" ? ((Convert.ToInt32(ddlStatus.SelectedValue)) == (int)DayStatus.Holiday ? DayStatus.Holiday.ToString() : "Weekly Off") : (txtHoliday.Text)));
lstData.Add(new SqlParameter("@status", ddlStatus.SelectedValue));
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
objDDBDataHelper.ExecSQL(query, SQLTextType.Query, lstData);
}
ScriptManager.RegisterStartupScript(this, this.GetType(), "script", "alert('Holiday Added Successfully...');", true);
btnAddHoliday.Enabled = false;
btnAddHoliday.Text = "Holiday Exists";
txtHoliday.Text = dt.Rows[0][0] == DBNull.Value ? "" : dt.Rows[0][0].ToString();
BindData();
}
/// <summary>
/// Adds a new Department
/// </summary>
/// <param name="Department">String value for Department Name</param>
/// <returns>True if Department is added successfully</returns>
public bool AddDepartment(string Department)
{
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
List<SqlParameter> lstDepartment = new List<SqlParameter>();
lstDepartment.Add(new SqlParameter("@name", Department));
lstDepartment.Add(new SqlParameter("@createdOn", DateTime.Now));
lstDepartment.Add(new SqlParameter("@updatedOn", DateTime.Now));
DataTable dt = new DataTable();
try
{
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
objDDBDataHelper.ExecSQL("spCreateDepartment", SQLTextType.Stored_Proc, lstDepartment);
}
return true;
}
catch (Exception ex)
{
return false;
}
}
public bool UpdateLeave(int leaveId, string leaveName)
{
List <SqlParameter> lstLeave = new List <SqlParameter>();
lstLeave.Add(new SqlParameter("@leaveId", leaveId));
lstLeave.Add(new SqlParameter("@name", leaveName));
lstLeave.Add(new SqlParameter("@updatedAt", DateTime.Now));
DataTable dt = new DataTable();
try
{
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
objDDBDataHelper.ExecSQL("spUpdateLeave", SQLTextType.Stored_Proc, lstLeave);
}
return(true);
}
catch (Exception)
{
return(false);
}
}
/// <summary>
///
/// </summary>
/// <param name="departmentId"></param>
/// <returns></returns>
public bool UpdateDepartment(int departmentId, string Department)
{
List <SqlParameter> lstDepartment = new List <SqlParameter>();
lstDepartment.Add(new SqlParameter("@name", Department));
lstDepartment.Add(new SqlParameter("@departmentId", departmentId));
lstDepartment.Add(new SqlParameter("@updatedAt", DateTime.Now));
DataTable dt = new DataTable();
try
{
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
objDDBDataHelper.ExecSQL("spUpdateDepartment", SQLTextType.Stored_Proc, lstDepartment);
}
return(true);
}
catch (Exception)
{
return(false);
}
}
protected void btnAddSession_Click(object sender, EventArgs e)
{
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
DBDataHelper helper = new DBDataHelper();
#region Get Last Session
string query = @"SELECT TOP 1 [SessionStartDate],[SessionEndDate]
FROM [BiometricsAttendanceSystem].[dbo].[tblSession]
ORDER BY [SessionStartDate] DESC";
DataTable dt = new DataTable();
DateTime startDate = new DateTime();
DateTime endDate = new DateTime();
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
dt = objDDBDataHelper.GetDataTable(query, SQLTextType.Query);
startDate = DateTime.Parse(dt.Rows[0][0].ToString());
endDate = DateTime.Parse(dt.Rows[0][1].ToString());
}
#endregion
#region Adding New Session
startDate = startDate.AddYears(1);
endDate = endDate.AddYears(1);
string query1 = @"INSERT INTO [BiometricsAttendanceSystem].[dbo].[tblSession]
VALUES (@startDate,@endDate)";
List<SqlParameter> paramsLst = new List<SqlParameter>();
paramsLst.Add(new SqlParameter("@startDate", startDate));
paramsLst.Add(new SqlParameter("@endDate", endDate));
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
objDDBDataHelper.ExecSQL(query1, SQLTextType.Query, paramsLst);
}
#endregion
#region Update Leave Balance Table
#endregion
}
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);
}
}
/// <summary>
/// Adds a new task to the database and assigns the task to different persons.
/// </summary>
/// <param name="task">An object of Task class containing the details about the task.</param>
/// <returns>It returns true if the task is added and assigned successfully.</returns>
public bool AddNewTask(Task task)
{
SqlParameter Name = new SqlParameter("name", task.Name);
SqlParameter Description = new SqlParameter("description", task.Description);
SqlParameter StartDate = new SqlParameter("startDate", task.StartDate);
SqlParameter EndDate = new SqlParameter("endDate", task.EndDate);
SqlParameter HODID = new SqlParameter("hodID", task.AssignedBy.ID);
SqlParameter PriorityID = new SqlParameter("priorityID", (int)task.Priority);
SqlParameter TaskTypeID = new SqlParameter("TaskTypeID", (int)task.Type);
SqlParameter TaskID = new SqlParameter("id", SqlDbType.Int);
TaskID.Direction = ParameterDirection.Output;
TaskID.Size = 50;
SqlParameter ReminderTime = new SqlParameter("reminderTime", task.ReminderTime);
List<SqlParameter> parameterCollection = new List<SqlParameter>()
{
Name,Description,StartDate,EndDate,HODID,PriorityID,TaskTypeID,TaskID,ReminderTime
};
using (DBDataHelper helper = new DBDataHelper())
{
helper.ExecSQL("dbo.AddNewTask", SQLTextType.Stored_Proc, parameterCollection);
}
task.ID = int.Parse(TaskID.SqlValue.ToString());
return AssignTaskToFaculties(task);
}
protected void btnUpdate_Click(object sender, EventArgs e)
{
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
List<SqlParameter> lstData = new List<SqlParameter>();
lstData.Add(new SqlParameter("@employeeId", Convert.ToInt32(Session["id"])));
lstData.Add(new SqlParameter("@sickleave", Convert.ToInt32(txtEditSL.Text)));
lstData.Add(new SqlParameter("@emergencyLeave", Convert.ToInt32(txtEditEL.Text)));
if (ddlDate.SelectedValue == "0")
{
lstData.Add(new SqlParameter("@sessionstart", new DateTime(2015, 08, 01)));
lstData.Add(new SqlParameter("@sessionend", new DateTime(2016, 07, 31)));
}
else
{
lstData.Add(new SqlParameter("@sessionstart", new DateTime(2016, 08, 01)));
lstData.Add(new SqlParameter("@sessionend", new DateTime(2017, 07, 31)));
}
string query = "Insert into tblLeavesOldStock values(@employeeId,@sickleave,@emergencyLeave,@sessionstart,@sessionend)";
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
objDDBDataHelper.ExecSQL(query, SQLTextType.Query, lstData);
}
BindData();
}
public bool AssignHalfDayLeaveRemovingShortDayLeave(int employeeId, DateTime date)
{
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
DBDataHelper helper = new DBDataHelper();
List<SqlParameter> lst_params = new List<SqlParameter>();
lst_params.Add(new SqlParameter("@employeeId", employeeId));
lst_params.Add(new SqlParameter("@date", date));
string query = "SELECT Count([EmployeeId]) FROM [tblLeave] Where [Date] = @date AND [EmployeeId] =@employeeId AND isDeleted=0";
DataTable dt = new DataTable();
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
dt = objDDBDataHelper.GetDataTable(query, SQLTextType.Query, lst_params);
}
if (Int32.Parse(dt.Rows[0][0].ToString()) == 0)
{
AssignLeave(employeeId, date, (int)LeaveTypes.HDL);
return true;
}
else
{
List<SqlParameter> lst_params1 = new List<SqlParameter>();
lst_params1.Add(new SqlParameter("@employeeId", employeeId));
lst_params1.Add(new SqlParameter("@date", date));
lst_params1.Add(new SqlParameter("@createdAt", DateTime.Now));
try
{
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
objDDBDataHelper.ExecSQL("spAssignHalfDayLeaveRemovingShortDayLeave", SQLTextType.Stored_Proc, lst_params1);
}
return true;
}
catch (Exception)
{
return false;
}
}
}
/// <summary>
///
/// </summary>
/// <param name="departmentId"></param>
/// <returns></returns>
public bool UpdateDepartment(int departmentId, string Department)
{
List<SqlParameter> lstDepartment = new List<SqlParameter>();
lstDepartment.Add(new SqlParameter("@name", Department));
lstDepartment.Add(new SqlParameter("@departmentId", departmentId));
lstDepartment.Add(new SqlParameter("@updatedAt", DateTime.Now));
DataTable dt = new DataTable();
try
{
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
objDDBDataHelper.ExecSQL("spUpdateDepartment", SQLTextType.Stored_Proc, lstDepartment);
}
return true;
}
catch(Exception)
{
return false;
}
}
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;
}
}
/// <summary>
/// Add a new duration entry
/// </summary>
/// <param name="duration">TimeSpan value for duration</param>
/// <returns>True if entry is successfull, else False</returns>
///
public bool AddDuration(TimeSpan duration, int leaveId)
{
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
DBDataHelper helper = new DBDataHelper();
List<SqlParameter> lstDuration = new List<SqlParameter>();
lstDuration.Add(new SqlParameter("@duration", duration));
lstDuration.Add(new SqlParameter("@leaveId", leaveId));
lstDuration.Add(new SqlParameter("@createdOn", DateTime.Now));
lstDuration.Add(new SqlParameter("@updatedOn", DateTime.Now));
lstDuration.Add(new SqlParameter("@isActive", false));
lstDuration.Add(new SqlParameter("@isDeleted", false));
DataTable dt = new DataTable();
try
{
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
objDDBDataHelper.ExecSQL("spAddDuration", SQLTextType.Stored_Proc, lstDuration);
}
return true;
}
catch(Exception)
{
return false;
}
}
public bool DeleteDuration(int durationId)
{
List<SqlParameter> lstDuration = new List<SqlParameter>();
lstDuration.Add(new SqlParameter("@durationId", durationId));
lstDuration.Add(new SqlParameter("@updatedAt", DateTime.Now));
DataTable dt = new DataTable();
try
{
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
objDDBDataHelper.ExecSQL("spDeleteDuration", SQLTextType.Stored_Proc, lstDuration);
}
return true;
}
catch(Exception)
{
return false;
}
}
public bool DeleteEmployee(long employeeId)
{
List<SqlParameter> lstEmployeeDetail = new List<SqlParameter>();
lstEmployeeDetail.Add(new SqlParameter("@employeeId", employeeId));
DataTable dt = new DataTable();
try
{
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
objDDBDataHelper.ExecSQL("spDeleteEmployee", SQLTextType.Stored_Proc, lstEmployeeDetail);
}
return true;
}
catch(Exception)
{
return false;
}
}
public bool UpdateShifts(int shiftId, MasterShifts objShift)
{
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
List<SqlParameter> lstShift = new List<SqlParameter>();
lstShift.Add(new SqlParameter("@shiftId", shiftId));
lstShift.Add(new SqlParameter("@name", objShift.Name));
lstShift.Add(new SqlParameter("@firstHalfStart", objShift.FirstHalfStart));
lstShift.Add(new SqlParameter("@firstHalfEnd", objShift.FirstHalfEnd));
lstShift.Add(new SqlParameter("@secondHalfStart", objShift.SecondHalfStart));
lstShift.Add(new SqlParameter("@secondHalfEnd", objShift.SecondHalfEnd));
lstShift.Add(new SqlParameter("@shlDuration", objShift.SHLDuration));
lstShift.Add(new SqlParameter("@updatedOn", DateTime.Now));
DataTable dt = new DataTable();
DataSet ds;
try
{
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
objDDBDataHelper.ExecSQL("spUpdateShift", SQLTextType.Stored_Proc, lstShift);
}
return true;
}
catch(Exception)
{
return false;
}
}
public bool UpdateLeave(int leaveId, string leaveName)
{
List<SqlParameter> lstLeave = new List<SqlParameter>();
lstLeave.Add(new SqlParameter("@leaveId", leaveId));
lstLeave.Add(new SqlParameter("@name", leaveName));
lstLeave.Add(new SqlParameter("@updatedAt", DateTime.Now));
DataTable dt = new DataTable();
try
{
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
objDDBDataHelper.ExecSQL("spUpdateLeave", SQLTextType.Stored_Proc, lstLeave);
}
return true;
}
catch(Exception)
{
return false;
}
}
public bool LeaveAssignedByRole(int leaveId, int roleId, int noOfLeaves, int isPromoted)
{
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
DBDataHelper helper = new DBDataHelper();
List<SqlParameter> lstLeaveAssignedByRole = new List<SqlParameter>();
lstLeaveAssignedByRole.Add(new SqlParameter("@leaveId", leaveId));
lstLeaveAssignedByRole.Add(new SqlParameter("@roleId", roleId));
lstLeaveAssignedByRole.Add(new SqlParameter("@noOfLeaves", noOfLeaves));
lstLeaveAssignedByRole.Add(new SqlParameter("@isPromoted", Convert.ToBoolean(isPromoted)));
lstLeaveAssignedByRole.Add(new SqlParameter("@createdAt", DateTime.Now));
DataTable dt = new DataTable();
try
{
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
objDDBDataHelper.ExecSQL("spAssignLeaveByRole", SQLTextType.Stored_Proc, lstLeaveAssignedByRole);
}
return true;
}
catch(Exception)
{
return false;
}
}
protected void btnAssignLeave_Click(object sender, EventArgs e)
{
if (Convert.ToInt32(ddlLeaves.SelectedValue) > 0)
{
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("@date", Calendar1.SelectedDate.Date));
lstData.Add(new SqlParameter("@leaveTypeId", Convert.ToInt32(ddlLeaves.SelectedValue)));
lstData.Add(new SqlParameter("@createdAt", DateTime.Now));
DataSet ds;
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
objDDBDataHelper.ExecSQL("spAssignLeave", SQLTextType.Stored_Proc, lstData);
}
ScriptManager.RegisterStartupScript(this, this.GetType(), "script", "alert('Leave Assigned');", true);
BindGrid();
}
else
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "script", "alert('Select a Leave');", true);
}
}
protected void btnDelete_Click(object sender, EventArgs e)
{
Button btn = (Button)sender;
int id = Convert.ToInt32(btn.CommandArgument);
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
DataTable dt = new DataTable();
List<SqlParameter> lstData = new List<SqlParameter>();
lstData.Add(new SqlParameter("@id", id));
DataSet ds;
string query = "DELETE FROM tblHolidays WHERE Id=@id";
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
objDDBDataHelper.ExecSQL(query, SQLTextType.Query, lstData);
}
ScriptManager.RegisterStartupScript(this, this.GetType(), "script", "alert('Holiday Deleted Successfully...');", true);
BindData();
}
/// <summary>
/// Add a new Shift
/// </summary>
/// <param name="shift">Shift type object</param>
/// <returns>True if entry is successfull else False</returns>
public bool AddShift(Shifts shift)
{
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
List<SqlParameter> lstShift = new List<SqlParameter>();
lstShift.Add(new SqlParameter("@firstHalfStart", shift.FirstHalfStart));
lstShift.Add(new SqlParameter("@firstHalfEnd", shift.FirstHalfEnd));
lstShift.Add(new SqlParameter("@secondHalfStart", shift.SecondHalfStart));
lstShift.Add(new SqlParameter("@secondHalfEnd", shift.SecondHalfEnd));
lstShift.Add(new SqlParameter("@isActive", false));
lstShift.Add(new SqlParameter("@createdOn", DateTime.Now));
lstShift.Add(new SqlParameter("@updatedOn", DateTime.Now));
lstShift.Add(new SqlParameter("@isDeleted", false));
DataTable dt = new DataTable();
try
{
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
objDDBDataHelper.ExecSQL("spAddShift", SQLTextType.Stored_Proc, lstShift);
}
return true;
}
catch (Exception ex)
{
return false;
}
}
public void AssignLeave(DateTime date)
{
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("@date", date));
lstData.Add(new SqlParameter("@leaveTypeId", Convert.ToInt32(ddlLeaves.SelectedValue)));
lstData.Add(new SqlParameter("@createdAt", DateTime.Now));
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
objDDBDataHelper.ExecSQL("spAssignLeave", SQLTextType.Stored_Proc, lstData);
}
}
public bool UpdateLeavesAssignedPerSessionEmployeeWise(LeaveAssignedPerSession objLeaveAssignedPerSession, DateTime sessionStartDate, DateTime sessionEndDate)
{
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
List<SqlParameter> lstParams = new List<SqlParameter>();
lstParams.Add(new SqlParameter("@employeeId", objLeaveAssignedPerSession.EmployeeId));
lstParams.Add(new SqlParameter("@leaveTypeId", objLeaveAssignedPerSession.leaveType));
lstParams.Add(new SqlParameter("@noOfLeaves", objLeaveAssignedPerSession.leaveCount));
lstParams.Add(new SqlParameter("@sessionStartDate", sessionStartDate.Date));
lstParams.Add(new SqlParameter("@sessionEndDate", sessionEndDate.Date));
string query = @"UPDATE [dbo].[tblLeaveAssignedPerSession]
SET
[NoOfLeaves] = @noOfLeaves
WHERE
EmployeeId = @employeeId AND
LeaveTypeId = @leaveTypeId AND
CAST(SessionStartDate AS DATE) = CAST (@sessionStartDate AS DATE) AND
CAST(SessionEndDate AS DATE) = CAST (@sessionEndDate AS DATE)";
try
{
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
objDDBDataHelper.ExecSQL(query, SQLTextType.Query, lstParams);
}
return true;
}
catch (Exception ex)
{
return false;
}
}
public bool FullDayLeave(int employeeId, DateTime date, int leaveTypeId)
{
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
DBDataHelper helper = new DBDataHelper();
List<SqlParameter> lstFullDayLeave = new List<SqlParameter>();
lstFullDayLeave.Add(new SqlParameter("@employeeId", employeeId));
lstFullDayLeave.Add(new SqlParameter("@leaveTypeId", leaveTypeId));
lstFullDayLeave.Add(new SqlParameter("@createdAt", DateTime.Now));
lstFullDayLeave.Add(new SqlParameter("@updatedAt", DateTime.Now));
lstFullDayLeave.Add(new SqlParameter("@date", date));
DataTable dt = new DataTable();
try
{
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
objDDBDataHelper.ExecSQL("spAssignFullDayLeave", SQLTextType.Stored_Proc, lstFullDayLeave);
}
return true;
}
catch (Exception)
{
return false;
}
}
/// <summary>
/// Add a new type of Role
/// </summary>
/// <param name="Role">String Value for name of Role</param>
/// <returns>True if entry is successfull else False</returns>
public bool AddRole(string Role)
{
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
List<SqlParameter> lstRole = new List<SqlParameter>();
lstRole.Add(new SqlParameter("@name", Role));
lstRole.Add(new SqlParameter("@createdOn", DateTime.Now));
lstRole.Add(new SqlParameter("@updatedOn", DateTime.Now));
lstRole.Add(new SqlParameter("@isDeleted", false));
DataTable dt = new DataTable();
try
{
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
objDDBDataHelper.ExecSQL("spAddRole", SQLTextType.Stored_Proc, lstRole);
}
return true;
}
catch(Exception)
{
return false;
}
}
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 void UpdateLeaveBalanceTable(DateTime sessionStartDate)
{
try
{
#region Vars
DateTime sessionEndDate = new DateTime(sessionStartDate.Year + 1, 7, 31);
int SLCountTaken = 0, ELCountTaken = 0, SLCountAssigned = 0, ELCountAssigned = 0, SLCountOld = 0, ELCountOld = 0, SLCountBalance = 0, ELCountBalance = 0;
DateTime prevSessionStartDate = sessionStartDate.AddYears(-1);
DateTime prevSessionEndDate = sessionEndDate.AddYears(-1);
#endregion
#region GetAllEmployees
ManageEmployees objManageEmployees = new ManageEmployees();
List<Employees> lstEmployees = objManageEmployees.GetAllEmployees();
#endregion
foreach (var employee in lstEmployees)
{
#region Get EL Taken By Emp
string queryELTaken = "Select Count(*) from tblLeave Where EmployeeId = @employeeId AND LeaveTypeId = 2 AND [Date] Between @sessionStartDate AND @sessionEndDate";
List<SqlParameter> list_params_el = new List<SqlParameter>()
{ new SqlParameter("@employeeId", employee.Id),
new SqlParameter("@sessionStartDate", prevSessionStartDate),
new SqlParameter("@sessionEndDate", prevSessionEndDate)
};
DataTable dtEL;
using (DBDataHelper helper = new DBDataHelper())
{
dtEL = helper.GetDataTable(queryELTaken, SQLTextType.Query, list_params_el);
ELCountTaken = Convert.ToInt32(dtEL.Rows[0][0].ToString());
}
#endregion
#region Get SL Taken By Emp
string querySLTaken = "Select Count(*) from tblLeave Where EmployeeId = @employeeId AND LeaveTypeId = 5 AND [Date] Between @sessionStartDate AND @sessionEndDate";
List<SqlParameter> list_params = new List<SqlParameter>()
{ new SqlParameter("@employeeId", employee.Id),
new SqlParameter("@sessionStartDate", prevSessionStartDate),
new SqlParameter("@sessionEndDate", prevSessionEndDate)
};
DataTable dtSL;
using (DBDataHelper helper = new DBDataHelper())
{
dtSL = helper.GetDataTable(querySLTaken, SQLTextType.Query, list_params);
if (dtSL.Rows.Count > 0)
SLCountTaken = dtSL.Rows[0][0] != DBNull.Value ? Convert.ToInt32(dtSL.Rows[0][0].ToString()) : 0;
else
SLCountTaken = 0;
}
#endregion
#region Get EL Assigned
string queryELAssigned = @"SELECT [NoOfLeaves]FROM [BiometricAttendanceManagementSystem].[dbo].[tblLeaveAssignedPerSession] WHERE EmployeeId = @employeeId AND LeaveTypeId = 2 AND [SessionStartDate] = @sessionStartDate";
List<SqlParameter> list_params_el2 = new List<SqlParameter>()
{ new SqlParameter("@employeeId", employee.Id),
new SqlParameter("@sessionStartDate", prevSessionStartDate)
};
DataTable dtELAssigned;
using (DBDataHelper helper = new DBDataHelper())
{
dtELAssigned = helper.GetDataTable(queryELAssigned, SQLTextType.Query, list_params_el2);
if (dtELAssigned.Rows.Count > 0)
ELCountAssigned = dtELAssigned.Rows[0][0] != DBNull.Value ? Convert.ToInt32(dtELAssigned.Rows[0][0].ToString()) : 0;
else
ELCountAssigned = 0;
}
#endregion
#region Get SL Assigned
string querySLAssigned = @"SELECT [NoOfLeaves] FROM [BiometricAttendanceManagementSystem].[dbo].[tblLeaveAssignedPerSession] WHERE EmployeeId = @employeeId AND LeaveTypeId = 5 AND [SessionStartDate] = @sessionStartDate";
List<SqlParameter> list_params_sl2 = new List<SqlParameter>()
{ new SqlParameter("@employeeId", employee.Id),
new SqlParameter("@sessionStartDate", prevSessionStartDate)
};
DataTable dtSLAssigned;
using (DBDataHelper helper = new DBDataHelper())
{
dtSLAssigned = helper.GetDataTable(querySLAssigned, SQLTextType.Query, list_params_sl2);
if (dtSLAssigned.Rows.Count > 0)
SLCountAssigned = dtSLAssigned.Rows[0][0] != DBNull.Value ? Convert.ToInt32(dtSLAssigned.Rows[0][0].ToString()) : 0;
else
SLCountAssigned = 0;
//SLCountAssigned = Convert.ToInt32(dtSLAssigned.Rows[0][0].ToString());
}
#endregion
#region Get Old Data
string queryOld = @"SELECT TOP 1000
[SLCount]
,[ELCount]
FROM [tblLeavesOldStock]
WHERE EmployeeId = @employeeId
AND SessionStartDate = @sessionStartDate";
List<SqlParameter> list_params_old = new List<SqlParameter>()
{ new SqlParameter("@employeeId", employee.Id),
new SqlParameter("@sessionStartDate", prevSessionStartDate)
};
DataTable dtOld;
using (DBDataHelper helper = new DBDataHelper())
{
dtOld = helper.GetDataTable(queryOld, SQLTextType.Query, list_params_old);
if (dtOld.Rows.Count > 0)
{
ELCountOld = Convert.ToInt32(dtOld.Rows[0][1].ToString());
SLCountOld = Convert.ToInt32(dtOld.Rows[0][0].ToString());
}
}
#endregion
#region Calculation Of Balance
ELCountBalance = ELCountOld + ELCountAssigned - ELCountTaken;
ELCountBalance = ELCountBalance > 60 ? 60 : ELCountBalance;
SLCountBalance = SLCountOld + SLCountAssigned - SLCountTaken;
SLCountBalance = SLCountBalance > 30 ? 30 : SLCountBalance;
#endregion
#region Insert New Data
string queryInset = @"INSERT INTO [tblLeavesOldStock] VALUES (@employeeId, @slCount,@elCount, @sessionStartDate,@sessionEndDate)";
List<SqlParameter> list_params_insert = new List<SqlParameter>()
{ new SqlParameter("@employeeId", employee.Id),
new SqlParameter("@slCount", SLCountBalance),
new SqlParameter("@elCount", ELCountBalance),
new SqlParameter("@sessionStartDate", sessionStartDate),
new SqlParameter("@sessionEndDate", sessionEndDate)
};
using (DBDataHelper helper = new DBDataHelper())
{
helper.ExecSQL(queryInset, SQLTextType.Query, list_params_insert);
}
#endregion
}
}
catch (Exception)
{
}
}