/// <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 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 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 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);
}
}
/// <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);
}
}
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>
/// 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 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 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 IEnumerable <LanguageCode> GetLanguageInfo(string iso6393Code = null, string language = null, int?pageNumber = null, int?pageSize = null)
{
string sql = sqlRepositoryHelper.GetLanguageInfo;
List <SqlParameter> parameterCollection = new List <SqlParameter>();
parameterCollection.Add(new SqlParameter("ISO6393Code", iso6393Code));
parameterCollection.Add(new SqlParameter("Language", language));
parameterCollection.Add(new SqlParameter("PageNumber", pageNumber));
parameterCollection.Add(new SqlParameter("PageSize", pageSize));
List <LanguageCode> result = new List <LanguageCode>();
using (DBDataHelper helper = new DBDataHelper())
{
using (DataTable dt = helper.GetDataTable(sql, SQLTextType.Stored_Proc, parameterCollection))
{
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
result.Add(new LanguageCode()
{
ISO6391 = dr["ISO6391"].ToString(),
ISO6392 = dr["ISO6392"].ToString(),
ISO6393 = dr["ISO6393"].ToString(),
Language = dr["Language"].ToString(),
RowId = System.Text.Encoding.UTF32.GetBytes(dr["RowId"].ToString())
});
}
}
}
}
return(result);
}
public LeavesOldStockViewModel getDataForOldLeavesByEmployeeId(DateTime sessionStartDate, DateTime sessionEndDate, int employeeId)
{
List<LeavesOldStockViewModel> lstLeavesOldStockViewModel = new List<LeavesOldStockViewModel>();
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
DBDataHelper helper = new DBDataHelper();
List<SqlParameter> lst_params = new List<SqlParameter>();
lst_params.Add(new SqlParameter("@employeeId", employeeId));
DataTable dt = new DataTable();
string query = "SELECT tblEmployeesMaster.Id, Name ,[SLCount],[ELCount],[SessionStartDate],[SesssionEndDate] FROM[tblLeavesOldStock] right outer join tblEmployeesMaster On tblEmployeesMaster.Id = tblLeavesOldStock.EmployeeId Where tblLeavesOldStock.EmployeeId = @employeeId";
LeavesOldStockViewModel objLeavesOldStockViewModel = new LeavesOldStockViewModel();
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
dt = objDDBDataHelper.GetDataTable(query, SQLTextType.Query, lst_params);
foreach (DataRow row in dt.Rows)
{
objLeavesOldStockViewModel.employeeId = row[0] == DBNull.Value ? 0 : Int32.Parse(row[0].ToString());
objLeavesOldStockViewModel.employeeName = row[1] == DBNull.Value ? "" : row[1].ToString();
objLeavesOldStockViewModel.slCount = row[2] == DBNull.Value ? 0 : Int32.Parse(row[2].ToString());
objLeavesOldStockViewModel.elCount = row[3] == DBNull.Value ? 0 : Int32.Parse(row[3].ToString());
objLeavesOldStockViewModel.sessionStartDate = row[4] == DBNull.Value ? DateTime.Now : DateTime.Parse(row[4].ToString());
objLeavesOldStockViewModel.sessionEndDate = row[5] == DBNull.Value ? DateTime.Now : DateTime.Parse(row[5].ToString());
}
}
return objLeavesOldStockViewModel;
}
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);
}
}
protected void Calendar1_SelectionChanged(object sender, EventArgs e)
{
BindData();
lblDate.Text = Calendar1.SelectedDate.Date.ToString("d");
txtHoliday.Text = "";
btnAddHoliday.Text = "Add Holiday";
btnAddHoliday.Enabled = true;
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
DBDataHelper helper = new DBDataHelper();
string query = @"SELECT [NameOfHoliday]
FROM [tblHolidays]
WHERE Date = @date";
List<SqlParameter> lst_params = new List<SqlParameter>();
lst_params.Add(new SqlParameter("@date", Calendar1.SelectedDate));
DataTable dt = new DataTable();
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
dt = objDDBDataHelper.GetDataTable(query, SQLTextType.Query, lst_params);
if (dt != null && dt.Rows.Count > 0)
{
btnAddHoliday.Enabled = false;
btnAddHoliday.Text = "Holiday Exists";
txtHoliday.Text = dt.Rows[0][0] == DBNull.Value ? "" : dt.Rows[0][0].ToString();
}
}
}
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 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 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);
}
}
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 bool IsEmployeeOnLeave(int employeeId, DateTime date, out int LeaveType)
{
List <SqlParameter> list_params = new List <SqlParameter>()
{
new SqlParameter("@date", date), new SqlParameter("@employeeId", employeeId)
};
DataTable dt;
int count = 0;
using (DBDataHelper helper = new DBDataHelper())
{
dt = helper.GetDataTable("spGetTypeOfLeaveOfEmployeeByDate", SQLTextType.Stored_Proc, list_params);
foreach (DataRow row in dt.Rows)
{
count = int.Parse(row[0].ToString());
break;
}
}
if (count == 0)
{
LeaveType = 0;
return(false);
}
else
{
LeaveType = count;
return(false);
}
}
public List <LeavesOldStockViewModel> getDataForOldLeaves(DateTime sessionStartDate, DateTime sessionEndDate)
{
List <LeavesOldStockViewModel> lstLeavesOldStockViewModel = new List <LeavesOldStockViewModel>();
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
DBDataHelper helper = new DBDataHelper();
List <SqlParameter> lst_params = new List <SqlParameter>();
DataTable dt = new DataTable();
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
dt = objDDBDataHelper.GetDataTable("spGetDataForOldLeaves", SQLTextType.Query, lst_params);
foreach (DataRow row in dt.Rows)
{
LeavesOldStockViewModel objLeavesOldStockViewModel = new LeavesOldStockViewModel();
objLeavesOldStockViewModel.employeeId = row[0] == DBNull.Value ? 0 : Int32.Parse(row[0].ToString());
objLeavesOldStockViewModel.employeeName = row[1] == DBNull.Value ? "" : row[1].ToString();
objLeavesOldStockViewModel.slCount = row[2] == DBNull.Value ? 0 : Int32.Parse(row[2].ToString());
objLeavesOldStockViewModel.elCount = row[3] == DBNull.Value ? 0 : Int32.Parse(row[3].ToString());
objLeavesOldStockViewModel.sessionStartDate = row[4] == DBNull.Value ? DateTime.Now : DateTime.Parse(row[4].ToString());
objLeavesOldStockViewModel.sessionEndDate = row[5] == DBNull.Value ? DateTime.Now : DateTime.Parse(row[5].ToString());
lstLeavesOldStockViewModel.Add(objLeavesOldStockViewModel);
}
}
return(lstLeavesOldStockViewModel);
}
public List <LeavesCount> GetLeavesCountAssignedByRole(int roleId)
{
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
DataSet ds;
int i = 0;
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
string query = @"SELECT LeaveTypeId,NoOfLeaves
FROM tblLeaveAssignedByRole
WHERE RoleId = @roleId AND
IsDeleted = 0";
List <SqlParameter> lstParams = new List <SqlParameter>()
{
new SqlParameter("@roleId", roleId)
};
ds = objDDBDataHelper.GetDataSet(query, SQLTextType.Query, lstParams);
List <LeavesCount> lstLeavesCount = new List <LeavesCount>();
foreach (DataRow rows in ds.Tables[0].Rows)
{
LeavesCount objLeavesCount = new LeavesCount();
objLeavesCount.LeaveId = Convert.ToInt32(ds.Tables[0].Rows[i][0]);
objLeavesCount.LeaveCount = Convert.ToInt32(ds.Tables[0].Rows[i][1]);
lstLeavesCount.Add(objLeavesCount);
i++;
}
return(lstLeavesCount);
}
}
public List <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 LeavesOldStockViewModel getDataForOldLeavesByEmployeeId(DateTime sessionStartDate, DateTime sessionEndDate, int employeeId)
{
List <LeavesOldStockViewModel> lstLeavesOldStockViewModel = new List <LeavesOldStockViewModel>();
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
DBDataHelper helper = new DBDataHelper();
List <SqlParameter> lst_params = new List <SqlParameter>();
lst_params.Add(new SqlParameter("@employeeId", employeeId));
DataTable dt = new DataTable();
string query = "SELECT tblEmployeesMaster.Id, Name ,[SLCount],[ELCount],[SessionStartDate],[SesssionEndDate] FROM[tblLeavesOldStock] right outer join tblEmployeesMaster On tblEmployeesMaster.Id = tblLeavesOldStock.EmployeeId Where tblLeavesOldStock.EmployeeId = @employeeId";
LeavesOldStockViewModel objLeavesOldStockViewModel = new LeavesOldStockViewModel();
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
dt = objDDBDataHelper.GetDataTable(query, SQLTextType.Query, lst_params);
foreach (DataRow row in dt.Rows)
{
objLeavesOldStockViewModel.employeeId = row[0] == DBNull.Value ? 0 : Int32.Parse(row[0].ToString());
objLeavesOldStockViewModel.employeeName = row[1] == DBNull.Value ? "" : row[1].ToString();
objLeavesOldStockViewModel.slCount = row[2] == DBNull.Value ? 0 : Int32.Parse(row[2].ToString());
objLeavesOldStockViewModel.elCount = row[3] == DBNull.Value ? 0 : Int32.Parse(row[3].ToString());
objLeavesOldStockViewModel.sessionStartDate = row[4] == DBNull.Value ? DateTime.Now : DateTime.Parse(row[4].ToString());
objLeavesOldStockViewModel.sessionEndDate = row[5] == DBNull.Value ? DateTime.Now : DateTime.Parse(row[5].ToString());
}
}
return(objLeavesOldStockViewModel);
}
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 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 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 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 IEnumerable <string> GetDistinctTimeZones()
{
try
{
string sql = sqlRepositoryHelper.GetDistinctTimeZones;
List <string> result = new List <string>();
using (DBDataHelper helper = new DBDataHelper())
{
using (DataTable dt = helper.GetDataTable(sql, SQLTextType.Stored_Proc, parameterCollection: null))
{
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
result.Add(dr["TimeZoneId"].ToString());
}
}
}
}
return(result);
}
catch (Exception)
{
throw;
}
}
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 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 IEnumerable <FeatureCode> GetFeatureCodes(string featureCodeId, int?pageNumber, int?pageSize)
{
string sql = sqlRepositoryHelper.GetFeatureCodeInfo;
List <SqlParameter> parameterCollection = new List <SqlParameter>();
parameterCollection.Add(new SqlParameter("FeatureCodeId", featureCodeId));
parameterCollection.Add(new SqlParameter("PageNumber", pageNumber));
parameterCollection.Add(new SqlParameter("PageSize", pageSize));
List <FeatureCode> result = new List <FeatureCode>();
using (DBDataHelper helper = new DBDataHelper())
{
using (DataTable dt = helper.GetDataTable(sql, SQLTextType.Stored_Proc, parameterCollection))
{
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
result.Add(new FeatureCode()
{
FeatureCodeId = dr["FeatureCodeId"] != null ? dr["FeatureCodeId"].ToString() : string.Empty,
FeatureCodeName = dr["FeatureCodeId"] != null ? dr["FeatureCodeId"].ToString() : string.Empty,
Description = dr["Description"] != null ? dr["Description"].ToString() : string.Empty,
RowId = System.Text.Encoding.UTF32.GetBytes(dr["RowId"].ToString())
});
}
}
}
}
return(result);
}
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 IEnumerable <FeatureCategory> GetFeatureCategories(string featureCategoryId)
{
string sql = sqlRepositoryHelper.GetFeatureCategoryInfo;
List <SqlParameter> parameterCollection = new List <SqlParameter>();
parameterCollection.Add(new SqlParameter("FeatureCategoryId", featureCategoryId));
List <FeatureCategory> result = new List <FeatureCategory>();
using (DBDataHelper helper = new DBDataHelper())
{
using (DataTable dt = helper.GetDataTable(sql, SQLTextType.Stored_Proc, parameterCollection))
{
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
result.Add(new FeatureCategory()
{
FeatureCategoryId = dr["FeatureCategoryId"] != null ? dr["Featurecategoryid"].ToString() : string.Empty,
FeatureCategoryName = dr["FeatureCategoryName"] != null ? dr["FeatureCategoryName"].ToString() : string.Empty,
RowId = System.Text.Encoding.UTF32.GetBytes(dr["RowId"].ToString())
});
}
}
}
}
return(result);
}
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 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 Calendar1_SelectionChanged(object sender, EventArgs e)
{
BindData();
lblDate.Text = Calendar1.SelectedDate.Date.ToString("d");
txtHoliday.Text = "";
btnAddHoliday.Text = "Add Holiday";
btnAddHoliday.Enabled = true;
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
DBDataHelper helper = new DBDataHelper();
string query = @"SELECT [NameOfHoliday]
FROM [tblHolidays]
WHERE Date = @date";
List <SqlParameter> lst_params = new List <SqlParameter>();
lst_params.Add(new SqlParameter("@date", Calendar1.SelectedDate));
DataTable dt = new DataTable();
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
dt = objDDBDataHelper.GetDataTable(query, SQLTextType.Query, lst_params);
if (dt != null && dt.Rows.Count > 0)
{
btnAddHoliday.Enabled = false;
btnAddHoliday.Text = "Holiday Exists";
txtHoliday.Text = dt.Rows[0][0] == DBNull.Value ? "" : dt.Rows[0][0].ToString();
}
}
}
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 IEnumerable <GeonamesAPI.Domain.TimeZone> UpdateTimeZones(IEnumerable <Upd_VM.TimeZone> timeZones)
{
string sql = sqlRepositoryHelper.UpdateTimeZones;
List <SqlParameter> parameterCollection = new List <SqlParameter>();
DataTable timeZonesInputTable = new DataTable("TimeZone_TVP");
timeZonesInputTable.Columns.Add("ISOCountryCode");
timeZonesInputTable.Columns.Add("TimeZoneId");
timeZonesInputTable.Columns.Add("GMT");
timeZonesInputTable.Columns.Add("DST");
timeZonesInputTable.Columns.Add("RawOffset");
timeZonesInputTable.Columns.Add("RowId", typeof(byte[]));
foreach (Upd_VM.TimeZone timeZone in timeZones)
{
timeZonesInputTable.Rows.Add(new object[]
{
timeZone.ISOCountryCode,
timeZone.TimeZoneId,
timeZone.GMT,
timeZone.DST,
timeZone.RawOffset,
timeZone.RowId
});
}
SqlParameter inputData = new SqlParameter("Input", timeZonesInputTable);
inputData.SqlDbType = SqlDbType.Structured;
parameterCollection.Add(inputData);
List <Domain.TimeZone> result = new List <Domain.TimeZone>();
using (DBDataHelper helper = new DBDataHelper())
{
using (DataTable timeZonesOutputTable = helper.GetDataTable(sql, SQLTextType.Stored_Proc, parameterCollection))
{
if (timeZonesOutputTable.Rows.Count > 0)
{
foreach (DataRow dr in timeZonesOutputTable.Rows)
{
result.Add(new Domain.TimeZone()
{
TimeZoneId = dr["TimeZoneId"] == DBNull.Value ? string.Empty : dr["TimeZoneId"].ToString(),
ISOCountryCode = dr["ISOCountryCode"] == DBNull.Value ? string.Empty : dr["ISOCountryCode"].ToString(),
GMT = dr["GMT"] == DBNull.Value ? 0 : decimal.Parse(dr["GMT"].ToString()),
DST = dr["DST"] == DBNull.Value ? 0 : decimal.Parse(dr["DST"].ToString()),
RawOffset = dr["RawOffset"] == DBNull.Value ? 0 : decimal.Parse(dr["RawOffset"].ToString()),
RowId = System.Text.Encoding.UTF32.GetBytes(dr["RowId"].ToString())
});
}
}
}
}
return(result);
}
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;
}
}
protected void BindData()
{
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
DBDataHelper helper = new DBDataHelper();
string query = @"SELECT [Id],[SessionStartDate],[SessionEndDate]
FROM [tblSession] ORDER BY [SessionStartDate] DESC";
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
grdSession.DataSource = objDDBDataHelper.GetDataTable(query, SQLTextType.Query);
grdSession.DataBind();
}
}
/// <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;
}
/// <summary>
/// This method gets all the tasks that are assigned to the faculty based on the id of the faculty.
/// </summary>
/// <param name="facultyID">An integer parameter containing the ID of the faculty.</param>
/// <returns>It returns a dictionary of tasks assigned to the faculty along with the status of each task.</returns>
public Dictionary<Task, TaskStatusType> GetAllTasksAssignedToAFaculty(int facultyID)
{
SqlParameter FacultyID = new SqlParameter("facultyID", facultyID);
List<SqlParameter> parameterCollection = new List<SqlParameter>(){FacultyID};
DataTable taskTable = new DataTable();
using(DBDataHelper helper = new DBDataHelper())
{
taskTable = helper.GetDataTable("dbo.GetAllTasksAssignedToAFaculty", SQLTextType.Stored_Proc, parameterCollection);
}
//List<Task> tasks = new List<Task>();
Dictionary<Task, TaskStatusType> tasks = new Dictionary<Task, TaskStatusType>();
foreach (DataRow row in taskTable.Rows)
{
Task task = new Task()
{
ID = int.Parse(row["TaskID"].ToString()),
Name = row["Name"].ToString(),
Priority = (PriorityType)(Enum.Parse(typeof(PriorityType), row["PriorityID"].ToString())),
StartDate = DateTime.Parse(row["StartDate"].ToString()),
EndDate = DateTime.Parse(row["EndDate"].ToString()),
Description = row["Description"].ToString(),
Type = (TaskType)(Enum.Parse(typeof(TaskType), row["TaskTypeID"].ToString()))
};
TaskStatusType taskStatus = (TaskStatusType)(Enum.Parse(typeof(TaskStatusType),row["TaskStatusID"].ToString()));
DataTable table = new DataTable();
SqlParameter HODID = new SqlParameter("ID",row["HODID"]);
List<SqlParameter> collection = new List<SqlParameter>(){HODID};
using(DBDataHelper helper = new DBDataHelper())
{
table = helper.GetDataTable("dbo.GetFacultyByID", SQLTextType.Stored_Proc, collection);
}
HOD hod = new HOD()
{
ID = int.Parse(table.Rows[0]["FacultyID"].ToString()),
Name = table.Rows[0]["Name"].ToString(),
EmailID = table.Rows[0]["EmailID"].ToString(),
Designation = DesignationType.HOD,
Department = (DepartmentType)(Enum.Parse(typeof(DepartmentType),table.Rows[0]["DepartmentID"].ToString())),
ImageURL = table.Rows[0]["ImageURL"].ToString(),
ContactNo = table.Rows[0]["ContactNo"].ToString()
};
task.AssignedBy = hod;
task.AssignedTo = GetAllFacultiesHavingTheTask(task.ID);
tasks.Add(task,taskStatus);
}
return tasks;
}
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();
}
protected void BindData()
{
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
DBDataHelper helper = new DBDataHelper();
string query = @"SELECT [Id],[NameOfHoliday],[Status],[Date]
FROM [tblHolidays]
WHERE year([Date]) = year(@date)";
List<SqlParameter> lst_params = new List<SqlParameter>();
lst_params.Add(new SqlParameter("@date", Calendar1.SelectedDate));
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
grdHoliday.DataSource = objDDBDataHelper.GetDataTable(query, SQLTextType.Query, lst_params);
grdHoliday.DataBind();
}
}
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
}
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 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 ex)
{
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();
}
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 bool GetEmployeesOnLeaveByDate(DateTime date, out List<Reports> objEmployees)
{
DataTable dt, dt1, dt2, dt3, dt4;
List<Reports> objEmployees1 = new List<Reports>();
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
List<SqlParameter> list_params = new List<SqlParameter>() { new SqlParameter("@date", date) };
try
{
using (DBDataHelper helper = new DBDataHelper())
{
dt = helper.GetDataTable("spGetEmployeesOnLeaveByDate", SQLTextType.Stored_Proc, list_params);
for (int i = 0; i < dt.Rows.Count; i++)
{
int EmployeeId = Convert.ToInt32(dt.Rows[i]["EmployeeId"]);
List<SqlParameter> list_params2 = new List<SqlParameter>() { new SqlParameter("@employeeId", EmployeeId) };
dt1 = helper.GetDataTable("select FirstName,MiddleName,LastName from tblEmployeesMaster where Id=@employeeId", SQLTextType.Query, list_params2);
Reports objReports = new Reports();
objReports.FirstName = Convert.ToString(dt1.Rows[0]["FirstName"]);
objReports.MiddleName = Convert.ToString(dt1.Rows[0]["MiddleName"]);
objReports.LastName = Convert.ToString(dt1.Rows[0]["LastName"]);
List<SqlParameter> list_params3 = new List<SqlParameter>() { new SqlParameter("@employeeId", EmployeeId) };
dt2 = helper.GetDataTable("Select RoleId,DepartmentId from tblEmployees where EmployeeId=@employeeId", SQLTextType.Query, list_params3);
objReports.RoleId = Convert.ToInt32(dt2.Rows[0]["RoleId"]);
objReports.DepartmentId = Convert.ToInt32(dt2.Rows[0]["DepartmentId"]);
List<SqlParameter> list_params4 = new List<SqlParameter>() { new SqlParameter("@roleId", objReports.RoleId) };
dt3 = helper.GetDataTable("spGetRoleById", SQLTextType.Stored_Proc, list_params4);
objReports.RoleName = dt3.Rows[0][0].ToString();
List<SqlParameter> list_params5 = new List<SqlParameter>() { new SqlParameter("@departmentId", objReports.DepartmentId) };
dt4 = helper.GetDataTable("spGetDepartmentById", SQLTextType.Stored_Proc, list_params5);
objReports.DepartmentName = dt4.Rows[0][0].ToString();
objEmployees1.Add(objReports);
}
}
objEmployees = objEmployees1;
return true;
}
catch (Exception)
{
objEmployees = null;
return false;
}
}
/// <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;
}
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
List<SqlParameter> list_params = new List<SqlParameter>()
{
new SqlParameter("@employeeId", Convert.ToInt32(txtEmployeeId.Text)),
new SqlParameter("@password",txtPassword.Text)
};
try
{
using (DBDataHelper helper = new DBDataHelper())
{
string query = "select * from tblEmployees where EmployeeId=@employeeId and Password=@password";
dt = helper.GetDataTable(query, SQLTextType.Query, list_params);
if (dt.Rows.Count == 1)
{
int RoleId = Convert.ToInt32(dt.Rows[0]["RoleId"]);
Session["employeeId"] = txtEmployeeId.Text;
Session["roleId"] = RoleId;
if (RoleId == 1)
Response.Redirect("~/Admin/ManageMasterEntries.aspx", false);
else
Response.Redirect("masterEntry.aspx", false);
}
else
{
lblMessage.Text = "Invalid User Id or Password";
}
}
}
catch (Exception ex)
{
Console.WriteLine("{0}", ex);
}
}
/// <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 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;
}
}
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 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>
/// This method assigns the task to different persons.
/// </summary>
/// <param name="task">An object of type Task containing the details of the task.</param>
/// <returns>It returns true if the task is successfully assigned to all the persons.</returns>
public bool AssignTaskToFaculties(Task task)
{
DataTable table = new DataTable("data");
table.Columns.Add("TaskID");
table.Columns.Add("PersonID");
table.Columns.Add("TaskStatusID");
foreach (Faculty faculty in task.AssignedTo)
{
table.Rows.Add(new object[] { task.ID, faculty.ID, 1 });
}
int result = 0;
SqlParameter TaskFacultiesTVP = new SqlParameter("TaskFacultiesTVP", table);
TaskFacultiesTVP.SqlDbType = SqlDbType.Structured;
List<SqlParameter> parameterCollection = new List<SqlParameter>() { TaskFacultiesTVP };
using (DBDataHelper helper = new DBDataHelper())
{
result = helper.GetRowsAffected("dbo.AssignTaskToFaculties", SQLTextType.Stored_Proc, parameterCollection);
}
if (result > 0)
return true;
else
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 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 GetEmployeesByDate(DateTime date, out List<Employees> objEmployees)
{
DataTable dt, dt1, dt2;
List<Employees> objEmployees1 = new List<Employees>();
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
List<SqlParameter> list_params = new List<SqlParameter>() { new SqlParameter("@date", date) };
try
{
using (DBDataHelper helper = new DBDataHelper())
{
dt = helper.GetDataTable("Select EmployeeId from tblAttendance where Date=@date", SQLTextType.Query, list_params);
for (int i = 0; i < dt.Rows.Count; i++)
{
int EmployeeId = Convert.ToInt32(dt.Rows[i]["EmployeeId"]);
List<SqlParameter> list_params2 = new List<SqlParameter>() { new SqlParameter("@employeeId", EmployeeId) };
dt1 = helper.GetDataTable("select FirstName,MiddleName,LastName from tblEmployeesMaster where Id=@employeeId", SQLTextType.Query, list_params2);
Employees objEmployee = new Employees();
objEmployee.FirstName = Convert.ToString(dt1.Rows[0]["FirstName"]);
objEmployee.MiddleName = Convert.ToString(dt1.Rows[0]["MiddleName"]);
objEmployee.LastName = Convert.ToString(dt1.Rows[0]["LastName"]);
List<SqlParameter> list_params3 = new List<SqlParameter>() { new SqlParameter("@employeeId", EmployeeId) };
dt2 = helper.GetDataTable("Select RoleId,DepartmentId from tblEmployees where EmployeeId=@employeeId", SQLTextType.Query, list_params3);
objEmployee.RoleId = Convert.ToInt32(dt2.Rows[0]["RoleId"]);
objEmployee.DepartmentId = Convert.ToInt32(dt2.Rows[0]["DepartmentId"]);
objEmployees1.Add(objEmployee);
}
}
objEmployees = objEmployees1;
return true;
}
catch (Exception)
{
objEmployees = null;
return false;
}
}
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;
}
}
