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);
}
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 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 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);
}
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 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 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);
}
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);
}
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 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 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);
}
protected void BindData()
{
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["sseCS"].ConnectionString;
DBDataHelper helper = new DBDataHelper();
DataTable dt = helper.GetDataTable("spGetUnapprovedRequests", SQLTextType.Stored_Proc);
gridviewApproveService.DataSource = dt;
gridviewApproveService.DataBind();
}
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 IEnumerable <LanguageCode> UpdateLanguages(IEnumerable <Upd_VM.LanguageCode> languageCodes)
{
string sql = sqlRepositoryHelper.UpdateLanguages;
List <SqlParameter> parameterCollection = new List <SqlParameter>();
DataTable languageCodesInputTable = new DataTable("LanguageCode_TVP");
languageCodesInputTable.Columns.Add("ISO6393");
languageCodesInputTable.Columns.Add("ISO6392");
languageCodesInputTable.Columns.Add("ISO6391");
languageCodesInputTable.Columns.Add("Language");
languageCodesInputTable.Columns.Add("RowId", typeof(byte[]));
foreach (Upd_VM.LanguageCode languageCode in languageCodes)
{
languageCodesInputTable.Rows.Add(new object[]
{
languageCode.ISO6393,
languageCode.ISO6392,
languageCode.ISO6391,
languageCode.Language,
languageCode.RowId
});
}
SqlParameter inputData = new SqlParameter("Input", languageCodesInputTable);
inputData.SqlDbType = SqlDbType.Structured;
parameterCollection.Add(inputData);
List <LanguageCode> result = new List <LanguageCode>();
using (DBDataHelper helper = new DBDataHelper())
{
using (DataTable featureCodesOutputTable = helper.GetDataTable(sql, SQLTextType.Stored_Proc, parameterCollection))
{
if (featureCodesOutputTable.Rows.Count > 0)
{
foreach (DataRow dr in featureCodesOutputTable.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 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;
}
}
protected void btnApprove_Click1(object sender, EventArgs e)
{
int service_id = Convert.ToInt32(((Button)sender).CommandArgument);
DBDataHelper helper = new DBDataHelper();
List <SqlParameter> list1 = new List <SqlParameter>();
list1.Add(new SqlParameter("@service_id", service_id));
DataTable dt = helper.GetDataTable("spApproveService", SQLTextType.Stored_Proc, list1);
BindData();
}
public IEnumerable <FeatureCategory> UpdateFeatureCategories(IEnumerable <Upd_VM.FeatureCategory> featureCategories)
{
try
{
string sql = sqlRepositoryHelper.UpdateFeatureCategories;
List <SqlParameter> parameterCollection = new List <SqlParameter>();
DataTable featureCategoriesInputTable = new DataTable("FeatureCategory_TVP");
featureCategoriesInputTable.Columns.Add("FeatureCategoryId");
featureCategoriesInputTable.Columns.Add("FeatureCategoryName");
featureCategoriesInputTable.Columns.Add("RowId", typeof(byte[]));
foreach (Upd_VM.FeatureCategory featureCategory in featureCategories)
{
featureCategoriesInputTable.Rows.Add(new object[]
{
featureCategory.FeatureCategoryId,
featureCategory.FeatureCategoryName,
featureCategory.RowId
});
}
SqlParameter inputData = new SqlParameter("Input", featureCategoriesInputTable);
inputData.SqlDbType = SqlDbType.Structured;
parameterCollection.Add(inputData);
List <FeatureCategory> result = new List <FeatureCategory>();
using (DBDataHelper helper = new DBDataHelper())
{
using (DataTable featureCategoriesOutputTable = helper.GetDataTable(sql, SQLTextType.Stored_Proc, parameterCollection))
{
if (featureCategoriesOutputTable.Rows.Count > 0)
{
foreach (DataRow dr in featureCategoriesOutputTable.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);
}
catch (Exception ex)
{
throw;
}
}
public IEnumerable <Country> GetCountriesInAContinent(string continentName = null, string continentCodeId = null, int?geonameId = null,
int?pageNumber = null, int?pageSize = null)
{
string sql = sqlRepositoryHelper.GetCountriesInAContinent;
List <SqlParameter> parameterCollection = new List <SqlParameter>();
parameterCollection.Add(new SqlParameter("ContinentName", continentName));
parameterCollection.Add(new SqlParameter("ContinentCodeId", continentCodeId));
parameterCollection.Add(new SqlParameter("GeonameId", geonameId));
parameterCollection.Add(new SqlParameter("PageNumber", pageNumber));
parameterCollection.Add(new SqlParameter("PageSize", pageSize));
List <Country> result = new List <Country>();
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 Country()
{
ISOCountryCode = dr["ISOCountryCode"].ToString(),
ISO3Code = dr["ISO3Code"].ToString(),
ISONumeric = dr["ISONumeric"] == DBNull.Value ? 0 : int.Parse(dr["ISONumeric"].ToString()),
FIPSCode = dr["FIPSCode"].ToString(),
CountryName = dr["CountryName"].ToString(),
Capital = dr["Capital"].ToString(),
SqKmArea = dr["SqKmArea"] == DBNull.Value ? 0f : double.Parse(dr["SqKmArea"].ToString()),
TotalPopulation = dr["TotalPopulation"] == DBNull.Value ? 0 : long.Parse(dr["TotalPopulation"].ToString()),
ContinentCodeId = dr["ContinentCodeId"].ToString(),
TopLevelDomain = dr["TopLevelDomain"].ToString(),
CurrencyCode = dr["CurrencyCode"].ToString(),
CurrencyName = dr["CurrencyName"].ToString(),
Phone = dr["Phone"].ToString(),
PostalFormat = dr["PostalFormat"].ToString(),
PostalRegex = dr["PostalRegex"].ToString(),
Languages = dr["Languages"].ToString(),
GeonameId = dr["GeonameId"] == DBNull.Value ? 0 : int.Parse(dr["GeonameId"].ToString()),
Neighbors = dr["Neighbors"].ToString(),
EquivalentFipsCode = dr["EquivalentFipsCode"].ToString(),
RowId = dr["RowId"] as byte[]
});
}
}
}
}
return(result);
}
protected void grdSessionsBind()
{
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
string query = "Select * from tblSession";
DataTable ds = new DataTable();
using (DBDataHelper objDDBDataHelper = new DBDataHelper())
{
ds = objDDBDataHelper.GetDataTable(query, SQLTextType.Query);
}
gvSession.DataSource = ds;
gvSession.DataBind();
}
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();
}
}
public IEnumerable <RawData> GetCitiesInState(string countryName = null, string isoCountryCode = null, string stateName = null, int?stateGeonameId = null, int?cityGeonameId = null, string cityName = null, int?pageSize = null, int?pageNumber = null)
{
string sql = sqlRepositoryHelper.GetCitiesInAState;
List <SqlParameter> parameterCollection = new List <SqlParameter>();
parameterCollection.Add(new SqlParameter("CountryName", countryName));
parameterCollection.Add(new SqlParameter("ISOCountryCode", isoCountryCode));
parameterCollection.Add(new SqlParameter("StateGeonameId", stateGeonameId));
parameterCollection.Add(new SqlParameter("StateName", stateName));
parameterCollection.Add(new SqlParameter("CityName", cityName));
parameterCollection.Add(new SqlParameter("CityGeonameId", cityGeonameId));
parameterCollection.Add(new SqlParameter("PageSize", pageSize));
parameterCollection.Add(new SqlParameter("PageNumber", pageNumber));
List <RawData> result = new List <RawData>();
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 RawData()
{
GeonameId = dr["GeonameId"] == DBNull.Value ? 0 : int.Parse(dr["GeonameId"].ToString()),
Name = dr["Name"] == DBNull.Value ? string.Empty : dr["Name"].ToString(),
ASCIIName = dr["ASCIIName"] == DBNull.Value ? string.Empty : dr["ASCIIName"].ToString(),
AlternateNames = dr["AlternateNames"] == DBNull.Value ? string.Empty : dr["AlternateNames"].ToString(),
Latitude = dr["Latitude"] == DBNull.Value ? 0f : double.Parse(dr["Latitude"].ToString()),
Longitude = dr["Longitude"] == DBNull.Value ? 0f : double.Parse(dr["Longitude"].ToString()),
FeatureCodeId = dr["FeatureCodeId"] == DBNull.Value ? string.Empty : dr["FeatureCodeId"].ToString(),
CC2 = dr["CC2"] != DBNull.Value ? string.Empty : dr["CC2"].ToString(),
Admin1Code = dr["Admin1Code"] == DBNull.Value ? string.Empty : dr["Admin1Code"].ToString(),
Admin2Code = dr["Admin2Code"] == DBNull.Value ? string.Empty : dr["Admin2Code"].ToString(),
Admin3Code = dr["Admin3Code"] == DBNull.Value ? string.Empty : dr["Admin3Code"].ToString(),
Admin4Code = dr["Admin4Code"] == DBNull.Value ? string.Empty : dr["Admin4Code"].ToString(),
Population = dr["Population"] == DBNull.Value ? 0 : long.Parse(dr["Population"].ToString()),
Elevation = dr["Elevation"] == DBNull.Value ? 0 : int.Parse(dr["Elevation"].ToString()),
DEM = dr["DEM"] == DBNull.Value ? 0 : int.Parse(dr["DEM"].ToString()),
ModificationDate = dr["ModificationDate"] == DBNull.Value ? DateTime.MinValue : DateTime.Parse(dr["ModificationDate"].ToString()),
RowId = System.Text.Encoding.UTF32.GetBytes(dr["RowId"].ToString())
});
}
}
}
}
return(result);
}
public IEnumerable <FeatureCode> InsertFeatureCodes(IEnumerable <Ins_VM.FeatureCode> featureCodes)
{
string sql = sqlRepositoryHelper.InsertFeatureCodes;
List <SqlParameter> parameterCollection = new List <SqlParameter>();
DataTable featureCodesInputTable = new DataTable("FeatureCode_TVP");
featureCodesInputTable.Columns.Add("FeatureCodeId");
featureCodesInputTable.Columns.Add("FeatureCodeName");
featureCodesInputTable.Columns.Add("Description");
foreach (Ins_VM.FeatureCode featureCode in featureCodes)
{
featureCodesInputTable.Rows.Add(new object[]
{
featureCode.FeatureCodeId,
featureCode.FeatureCodeName,
featureCode.Description
});
}
SqlParameter inputData = new SqlParameter("Input", featureCodesInputTable);
inputData.SqlDbType = SqlDbType.Structured;
parameterCollection.Add(inputData);
List <FeatureCode> result = new List <FeatureCode>();
using (DBDataHelper helper = new DBDataHelper())
{
using (DataTable featureCodesOutputTable = helper.GetDataTable(sql, SQLTextType.Stored_Proc, parameterCollection))
{
if (featureCodesOutputTable.Rows.Count > 0)
{
foreach (DataRow dr in featureCodesOutputTable.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);
}
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 Country GetCountryInfo(string isoCountryCode = null, string countryName = null)
{
string sql = sqlRepositoryHelper.GetCountryInfo;
List <SqlParameter> parameterCollection = new List <SqlParameter>();
parameterCollection.Add(new SqlParameter("ISOCountryCode", isoCountryCode));
parameterCollection.Add(new SqlParameter("CountryName", countryName));
Country result = new Country();
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 = new Country()
{
ISOCountryCode = dr["ISOCountryCode"] == DBNull.Value ? string.Empty : dr["ISOCountryCode"].ToString(),
ISO3Code = dr["ISO3Code"] == DBNull.Value ? string.Empty : dr["ISO3Code"].ToString(),
ISONumeric = dr["ISONumeric"] == DBNull.Value ? 0 : int.Parse(dr["ISONumeric"].ToString()),
FIPSCode = dr["FIPSCode"] == DBNull.Value ? string.Empty : dr["FIPSCode"].ToString(),
CountryName = dr["CountryName"] == DBNull.Value ? string.Empty : dr["CountryName"].ToString(),
Capital = dr["Capital"] == DBNull.Value ? string.Empty : dr["Capital"].ToString(),
SqKmArea = dr["SqKmArea"] == DBNull.Value ? 0f : double.Parse(dr["SqKmArea"].ToString()),
TotalPopulation = dr["TotalPopulation"] == DBNull.Value ? 0 : long.Parse(dr["TotalPopulation"].ToString()),
ContinentCodeId = dr["ContinentCodeId"] == DBNull.Value ? string.Empty : dr["ContinentCodeId"].ToString(),
TopLevelDomain = dr["TopLevelDomain"] == DBNull.Value ? string.Empty : dr["TopLevelDomain"].ToString(),
CurrencyCode = dr["CurrencyCode"] == DBNull.Value ? string.Empty : dr["CurrencyCode"].ToString(),
CurrencyName = dr["CurrencyName"] == DBNull.Value ? string.Empty : dr["CurrencyName"].ToString(),
Phone = dr["Phone"] == DBNull.Value ? string.Empty : dr["Phone"].ToString(),
PostalFormat = dr["PostalFormat"] == DBNull.Value ? string.Empty : dr["PostalFormat"].ToString(),
PostalRegex = dr["PostalRegex"] == DBNull.Value ? string.Empty : dr["PostalRegex"].ToString(),
Languages = dr["Languages"] == DBNull.Value ? string.Empty : dr["Languages"].ToString(),
GeonameId = dr["GeonameId"] == DBNull.Value ? 0 : int.Parse(dr["GeonameId"].ToString()),
Neighbors = dr["Neighbors"] == DBNull.Value ? string.Empty : dr["Neighbors"].ToString(),
EquivalentFipsCode = dr["EquivalentFipsCode"] == DBNull.Value ? string.Empty : dr["EquivalentFipsCode"].ToString(),
RowId = System.Text.Encoding.UTF32.GetBytes(dr["RowId"].ToString())
};
}
}
}
}
return(result);
}
/// <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 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();
}
}
public IEnumerable <Domain.TimeZone> GetTimeZoneDetails(string timeZoneId = null, string isoCountryCode = null, string iso3Code = null, int?isoNumeric = null, string countryName = null, double?latitude = null, double?longitude = null, int?pageNumber = null, int?pageSize = null)
{
try
{
string sql = sqlRepositoryHelper.GetTimeZoneDetails;
List <SqlParameter> parameterCollection = new List <SqlParameter>();
parameterCollection.Add(new SqlParameter("TimeZoneId", timeZoneId));
parameterCollection.Add(new SqlParameter("ISOCountryCode", isoCountryCode));
parameterCollection.Add(new SqlParameter("ISO3Code", iso3Code));
parameterCollection.Add(new SqlParameter("ISONumeric", isoNumeric));
parameterCollection.Add(new SqlParameter("CountryName", countryName));
parameterCollection.Add(new SqlParameter("Latitude", latitude));
parameterCollection.Add(new SqlParameter("Longitude", longitude));
parameterCollection.Add(new SqlParameter("PageNumber", pageNumber));
parameterCollection.Add(new SqlParameter("PageSize", pageSize));
List <Domain.TimeZone> result = new List <Domain.TimeZone>();
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 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);
}
catch (Exception)
{
throw;
}
}
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 IEnumerable <Domain.RawPostal> GetPostalInfo(string isoCountryCode = null, string countryName = null, string postalCode = null, int?pageNumber = null, int?pageSize = null)
{
string sql = sqlRepositoryHelper.GetPostalCodeInfo;
List <SqlParameter> parameterCollection = new List <SqlParameter>();
parameterCollection.Add(new SqlParameter("ISOCountryCode", isoCountryCode));
parameterCollection.Add(new SqlParameter("CountryName", countryName));
parameterCollection.Add(new SqlParameter("PageNumber", pageNumber));
parameterCollection.Add(new SqlParameter("PageSize", pageSize));
parameterCollection.Add(new SqlParameter("PostalCode", postalCode));
List <RawPostal> result = new List <RawPostal>();
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 RawPostal()
{
ISOCountryCode = dr["ISOCountryCode"] == DBNull.Value ? string.Empty : dr["ISOCountryCode"].ToString(),
PostalCode = dr["PostalCode"] == DBNull.Value ? string.Empty : dr["PostalCode"].ToString(),
PlaceName = dr["PlaceName"] == DBNull.Value ? string.Empty : dr["PlaceName"].ToString(),
Admin1Name = dr["Admin1Name"] == DBNull.Value ? string.Empty : dr["Admin1Name"].ToString(),
Admin1Code = dr["Admin1Code"] == DBNull.Value ? string.Empty : dr["Admin1Code"].ToString(),
Admin2Name = dr["Admin2Name"] == DBNull.Value ? string.Empty : dr["Admin2Name"].ToString(),
Admin2Code = dr["Admin2Code"] == DBNull.Value ? string.Empty : dr["Admin2Code"].ToString(),
Admin3Name = dr["Admin3Name"] == DBNull.Value ? string.Empty : dr["Admin3Name"].ToString(),
Admin3Code = dr["Admin3Code"] == DBNull.Value ? string.Empty : dr["Admin3Code"].ToString(),
Latitude = dr["Latitude"] == DBNull.Value ? 0.0 : double.Parse(dr["Latitude"].ToString()),
Longitude = dr["Longitude"] == DBNull.Value ? 0.0 : double.Parse(dr["Longitude"].ToString()),
Accuracy = dr["Accuracy"] == DBNull.Value ? 0 : int.Parse(dr["Accuracy"].ToString()),
RowId = System.Text.Encoding.UTF32.GetBytes(dr["RowId"].ToString())
});
}
}
}
}
return(result);
}
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();
}
}
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 TimeSpan GetHalfDayLeaveDuration()
{
DataTable dt;
TimeSpan dr = new TimeSpan();
string query = "Select Duration From tblDuration Where IsActive= 1 AND IsDeleted = 0 AND TypeOfLeave = 2";
try
{
using (DBDataHelper helper = new DBDataHelper())
{
dt = helper.GetDataTable(query, SQLTextType.Query);
dr = dt.Rows[0][0] == DBNull.Value ? new TimeSpan(0, 0, 0) : TimeSpan.Parse(dt.Rows[0][0].ToString());
}
}
catch (Exception)
{
new TimeSpan(0, 0, 0);
}
return(dr);
}
public IEnumerable <Continent> GetContinentInfo(string continentCodeId = null, int?geonameId = null, string continentName = null)
{
string sql = sqlRepositoryHelper.GetContinentInfo;
List <SqlParameter> parameterCollection = new List <SqlParameter>();
parameterCollection.Add(new SqlParameter("ContinentCodeId", continentCodeId));
parameterCollection.Add(new SqlParameter("GeonameId", geonameId));
parameterCollection.Add(new SqlParameter("Continent", continentName));
List <Continent> result = new List <Continent>();
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 Continent()
{
ContinentCodeId = dr["ContinentCodeId"].ToString(),
ContinentName = dr["Continent"].ToString(),
GeonameId = int.Parse(dr["GeonameId"].ToString()),
ASCIIName = dr["ASCIIName"].ToString(),
AlternateNames = dr["AlternateNames"].ToString(),
Latitude = dr["Latitude"] == DBNull.Value ? 0f : double.Parse(dr["Latitude"].ToString()),
Longitude = dr["Longitude"] == DBNull.Value ? 0f : double.Parse(dr["Longitude"].ToString()),
FeatureCategoryId = dr["FeatureCategoryId"].ToString(),
FeatureCodeId = dr["FeatureCodeId"].ToString(),
TimeZoneId = dr["TimeZoneId"].ToString(),
RowId = dr["RowId"] as byte[]
});
}
}
}
}
return(result);
}
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);
}
}
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 IEnumerable <Domain.TimeZone> GetTimeZoneDetailsByPlaceName(string placeName)
{
try
{
string sql = sqlRepositoryHelper.GetTimeZoneDetailsByPlaceName;
List <SqlParameter> parameterCollection = new List <SqlParameter>();
parameterCollection.Add(new SqlParameter("PlaceName", placeName));
List <Domain.TimeZone> result = new List <Domain.TimeZone>();
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 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);
}
catch (Exception ex)
{
throw;
}
}
public TimeSpan GetShortLeaveDurationByShiftId(int shiftId)
{
DataTable dt;
TimeSpan dr = new TimeSpan();
string query = "SELECT SHLDuration FROM tblMasterShifts WHERE Id=@shiftId AND isDeleted = 0";
List <SqlParameter> lst_params = new List <SqlParameter> {
new SqlParameter("@shiftId", shiftId)
};
try
{
using (DBDataHelper helper = new DBDataHelper())
{
dt = helper.GetDataTable(query, SQLTextType.Query, lst_params);
dr = dt.Rows[0][0] == DBNull.Value ? new TimeSpan(0, 0, 0) : TimeSpan.Parse(dt.Rows[0][0].ToString());
}
}
catch (Exception)
{
dr = new TimeSpan(0, 0, 0);
}
return(dr);
}
public TimeSpan GetHalfDayLeaveDuration()
{
DataTable dt;
TimeSpan dr = new TimeSpan();
string query = "Select Duration From tblDuration Where IsActive= 1 AND IsDeleted = 0 AND TypeOfLeave = 2";
try
{
using (DBDataHelper helper = new DBDataHelper())
{
dt = helper.GetDataTable(query, SQLTextType.Query);
dr = dt.Rows[0][0] == DBNull.Value ? new TimeSpan(0, 0, 0) : TimeSpan.Parse(dt.Rows[0][0].ToString());
}
}
catch (Exception)
{
new TimeSpan(0, 0, 0);
}
return dr;
}
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;
}
}
private DayStatus GetStatusOfDay(DateTime date, int empId)
{
DataTable dt;
DayStatus status = new DayStatus();
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
int day = GetWeeklyOffForEmployee(empId);
if (date.DayOfWeek == (DayOfWeek)day)
status = DayStatus.WeeklyOff;
else {
List<SqlParameter> list_params = new List<SqlParameter>() { new SqlParameter("@date", date) };
string query = "SELECT Count(Date) As Count FROM [tblHolidays] WHERE [Status] =1 AND Date =@date";
try
{
using (DBDataHelper helper = new DBDataHelper())
{
dt = helper.GetDataTable(query, SQLTextType.Query, list_params);
}
if (int.Parse(dt.Rows[0][0].ToString()) > 0)
status = DayStatus.Holiday;
else
status = DayStatus.Active;
}
catch (Exception)
{
}
}
return status;
}
/// <summary>
/// This method fetches all the faculties having a particular task.
/// </summary>
/// <param name="task">An object of Task class.</param>
/// <returns>It returns a list of faculties.</returns>
protected List<Faculty> GetAllFacultiesHavingTheTask(int taskID)
{
SqlParameter TaskID = new SqlParameter("taskID", taskID);
List<SqlParameter> col = new List<SqlParameter>() { TaskID };
DataTable facultyTable = new DataTable();
List<Faculty> faculties = new List<Faculty>();
using (DBDataHelper helper = new DBDataHelper())
{
facultyTable = helper.GetDataTable("dbo.GetAllFacultiesHavingTheTask", SQLTextType.Stored_Proc, col);
}
foreach (DataRow facultyRow in facultyTable.Rows)
{
Faculty faculty = new Faculty()
{
ID = int.Parse(facultyRow["FacultyID"].ToString()),
Name = facultyRow["Name"].ToString(),
ImageURL = facultyRow["ImageURL"].ToString(),
EmailID = facultyRow["EmailID"].ToString(),
ContactNo = facultyRow["ContactNo"].ToString(),
Designation = (DesignationType)(Enum.Parse(typeof(DesignationType), facultyRow["DesignationID"].ToString())),
Department = (DepartmentType)(Enum.Parse(typeof(DepartmentType), facultyRow["DepartmentID"].ToString()))
};
faculties.Add(faculty);
}
return faculties;
}
public TimeSpan GetShortLeaveDurationByShiftId(int shiftId)
{
DataTable dt;
TimeSpan dr = new TimeSpan();
string query = "SELECT SHLDuration FROM tblMasterShifts WHERE Id=@shiftId AND isDeleted = 0";
List<SqlParameter> lst_params = new List<SqlParameter> {
new SqlParameter("@shiftId",shiftId)
};
try
{
using (DBDataHelper helper = new DBDataHelper())
{
dt = helper.GetDataTable(query, SQLTextType.Query, lst_params);
dr = dt.Rows[0][0] == DBNull.Value ? new TimeSpan(0, 0, 0) : TimeSpan.Parse(dt.Rows[0][0].ToString());
}
}
catch (Exception)
{
dr = new TimeSpan(0, 0, 0);
}
return dr;
}
/// <summary>
/// This method fetches the details of the faculty by their ID.
/// </summary>
/// <param name="facultyID">An integer parameter containing the ID of the faculty.</param>
/// <returns>It returns an object of type faculty.</returns>
public Faculty GetFacultyByID(int facultyID)
{
SqlParameter FacultyID = new SqlParameter("@id", facultyID);
List<SqlParameter> parameterCollection = new List<SqlParameter> { FacultyID };
DataTable facultyDataTable = new DataTable();
using (DBDataHelper helper = new DBDataHelper())
{
facultyDataTable = helper.GetDataTable("dbo.GetFacultyByID", SQLTextType.Stored_Proc, parameterCollection);
}
if (facultyDataTable.Rows.Count != 0)
{
return GenerateFaculty(facultyDataTable);
}
else
return null;
}
public TimeSpan GetTotalTime(int employeeId, DateTime date)
{
List<SqlParameter> list_paramsForTotalDuration = new List<SqlParameter>() { new SqlParameter("@date", date), new SqlParameter("@employeeId", employeeId) };
TimeSpan totalDuration = new TimeSpan();
DataTable dtTotalDuration;
using (DBDataHelper helper = new DBDataHelper())
{
dtTotalDuration = helper.GetDataTable("spGetTotalDurationOfEmployeesDatewise", SQLTextType.Stored_Proc, list_paramsForTotalDuration);
foreach (DataRow row in dtTotalDuration.Rows)
{
totalDuration = row[0] == DBNull.Value ? new TimeSpan(0, 0, 0) : new TimeSpan(0, Int32.Parse(row[0].ToString()), 0);
}
}
return totalDuration;
}
public IEnumerable <Country> InsertCountries(IEnumerable <Ins_VM.Country> countries)
{
string sql = sqlRepositoryHelper.InsertCountries;
List <SqlParameter> parameterCollection = new List <SqlParameter>();
DataTable countriesInputTable = new DataTable("Country_TVP");
countriesInputTable.Columns.Add("ISOCountryCode");
countriesInputTable.Columns.Add("ISO3Code");
countriesInputTable.Columns.Add("ISONumeric");
countriesInputTable.Columns.Add("FIPSCode");
countriesInputTable.Columns.Add("CountryName");
countriesInputTable.Columns.Add("Capital");
countriesInputTable.Columns.Add("SqKmArea");
countriesInputTable.Columns.Add("TotalPopulation");
countriesInputTable.Columns.Add("ContinentCodeId");
countriesInputTable.Columns.Add("TopLevelDomain");
countriesInputTable.Columns.Add("CurrencyCode");
countriesInputTable.Columns.Add("CurrencyName");
countriesInputTable.Columns.Add("Phone");
countriesInputTable.Columns.Add("PostalFormat");
countriesInputTable.Columns.Add("PostalRegex");
countriesInputTable.Columns.Add("Languages");
countriesInputTable.Columns.Add("GeonameId");
countriesInputTable.Columns.Add("Neighbors");
countriesInputTable.Columns.Add("EquivalentFipsCode");
foreach (Ins_VM.Country country in countries)
{
countriesInputTable.Rows.Add(new object[]
{
country.ISOCountryCode,
country.ISONumeric,
country.ISO3Code,
country.FIPSCode,
country.CountryName,
country.Capital,
country.SqKmArea,
country.TotalPopulation,
country.ContinentCodeId,
country.TopLevelDomain,
country.CurrencyCode,
country.CurrencyName,
country.Phone,
country.PostalFormat,
country.PostalRegex,
country.Languages,
country.GeonameId,
country.Neighbors,
country.EquivalentFipsCode
});
}
SqlParameter inputData = new SqlParameter("Country_TVP", countriesInputTable);
inputData.SqlDbType = SqlDbType.Structured;
parameterCollection.Add(inputData);
List <Country> result = new List <Country>();
using (DBDataHelper helper = new DBDataHelper())
{
using (DataTable countriesOutputTable = helper.GetDataTable(sql, SQLTextType.Stored_Proc, parameterCollection))
{
if (countriesOutputTable.Rows.Count > 0)
{
foreach (DataRow dr in countriesOutputTable.Rows)
{
result.Add(new Country()
{
ISOCountryCode = dr["ISOCountryCode"] == DBNull.Value ? string.Empty : dr["ISOCountryCode"].ToString(),
ISO3Code = dr["ISO3Code"] == DBNull.Value ? string.Empty : dr["ISO3Code"].ToString(),
ISONumeric = dr["ISONumeric"] == DBNull.Value ? 0 : int.Parse(dr["ISONumeric"].ToString()),
FIPSCode = dr["FIPSCode"] == DBNull.Value ? string.Empty : dr["FIPSCode"].ToString(),
CountryName = dr["CountryName"] == DBNull.Value ? string.Empty : dr["CountryName"].ToString(),
Capital = dr["Capital"] == DBNull.Value ? string.Empty : dr["Capital"].ToString(),
SqKmArea = dr["SqKmArea"] == DBNull.Value ? 0f : double.Parse(dr["SqKmArea"].ToString()),
TotalPopulation = dr["TotalPopulation"] == DBNull.Value ? 0 : long.Parse(dr["TotalPopulation"].ToString()),
ContinentCodeId = dr["ContinentCodeId"] == DBNull.Value ? string.Empty : dr["ContinentCodeId"].ToString(),
TopLevelDomain = dr["TopLevelDomain"] == DBNull.Value ? string.Empty : dr["TopLevelDomain"].ToString(),
CurrencyCode = dr["CurrencyCode"] == DBNull.Value ? string.Empty : dr["CurrencyCode"].ToString(),
CurrencyName = dr["CurrencyName"] == DBNull.Value ? string.Empty : dr["CurrencyName"].ToString(),
Phone = dr["Phone"] == DBNull.Value ? string.Empty : dr["Phone"].ToString(),
PostalFormat = dr["PostalFormat"] == DBNull.Value ? string.Empty : dr["PostalFormat"].ToString(),
PostalRegex = dr["PostalRegex"] == DBNull.Value ? string.Empty : dr["PostalRegex"].ToString(),
Languages = dr["Languages"] == DBNull.Value ? string.Empty : dr["Languages"].ToString(),
GeonameId = dr["GeonameId"] == DBNull.Value ? 0 : int.Parse(dr["GeonameId"].ToString()),
Neighbors = dr["Neighbors"] == DBNull.Value ? string.Empty : dr["Neighbors"].ToString(),
EquivalentFipsCode = dr["EquivalentFipsCode"] == DBNull.Value ? string.Empty : dr["EquivalentFipsCode"].ToString(),
RowId = System.Text.Encoding.UTF32.GetBytes(dr["RowId"].ToString())
});
}
}
}
}
return(result);
}
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)
{
}
}
public object GetDefaulterEmployeesByDepartmentDateWise(DateTime date, int departmentId)
{
List<Employees> objEmployees = new List<Employees>();
DataTable dt;
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
List<SqlParameter> list_params = new List<SqlParameter>();
list_params.Add(new SqlParameter("@date", date));
list_params.Add(new SqlParameter("@departmentId", departmentId));
try
{
using (DBDataHelper helper = new DBDataHelper())
{
dt = helper.GetDataTable("spGetEmployeesDefaultersByDeapartmentDateWise", SQLTextType.Stored_Proc, list_params);
for (int i = 0; i < dt.Rows.Count; i++)
{
Employees objEmployee = new Employees();
objEmployee.Id = Convert.ToInt32(dt.Rows[i]["EmployeeId"]);
objEmployee.FirstName = (dt.Rows[0]["FirstName"]).ToString();
objEmployee.MiddleName = (dt.Rows[0]["MiddleName"]).ToString();
objEmployee.LastName = (dt.Rows[0]["LastName"]).ToString();
objEmployee.RoleId = Convert.ToInt32(dt.Rows[0]["RoleId"]);
objEmployee.DepartmentId = Convert.ToInt32(dt.Rows[0]["DepartmentId"]);
objEmployees.Add(objEmployee);
}
}
return objEmployees;
}
catch (Exception)
{
return objEmployees;
}
}
public List<DailyAttendanceReportViewModel> GetDataForMonthlyReport(int departmentId, DateTime date, TimeSpan relaxation)
{
List<DailyAttendanceReportViewModel> lstDailyAttendanceReportViewModel = new List<DailyAttendanceReportViewModel>();
DataTable dtEmployees, dtShifts;
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
List<SqlParameter> list_params = new List<SqlParameter>() { new SqlParameter("@date", date), new SqlParameter("@departmentId", departmentId) };
ManageLeaves objManageLeave = new ManageLeaves();
TimeSpan tsShortLeave = objManageLeave.GetShortLeaveDuration();
TimeSpan tsHalfLeave = objManageLeave.GetHalfDayLeaveDuration();
try
{
using (DBDataHelper helper = new DBDataHelper())
{
dtEmployees = helper.GetDataTable("spGetEmployeesForDailyAttendanceReportByDeaprtment", SQLTextType.Stored_Proc, list_params);
dtShifts = helper.GetDataTable("spGetActiveShift", SQLTextType.Stored_Proc);
foreach (DataRow row in dtEmployees.Rows)
{
DailyAttendanceReportViewModel objDailyAttendanceReportViewModel = new DailyAttendanceReportViewModel();
objDailyAttendanceReportViewModel.EmployeeId = Int32.Parse(row[1].ToString());
objDailyAttendanceReportViewModel.Name = row[0].ToString();
objDailyAttendanceReportViewModel.FirstHalfStartTime = dtShifts.Rows[0]["FirstHalfStart"].ToString();
objDailyAttendanceReportViewModel.SecondHalfEndTime = dtShifts.Rows[0]["SecondHalfEnd"].ToString();
objDailyAttendanceReportViewModel.Relaxation = relaxation.ToString();
objDailyAttendanceReportViewModel.Date = date;
if (row[2] != DBNull.Value) //Entry Time is Not Null
{
objDailyAttendanceReportViewModel.InTime = row[2].ToString();
objDailyAttendanceReportViewModel.OutTime = row[3] == DBNull.Value ? dtShifts.Rows[0]["SecondHalfEnd"].ToString() : row[3].ToString();
if (row[3] != DBNull.Value)
objDailyAttendanceReportViewModel.Status = Status.Present; //Exit Time is Not Null
else
objDailyAttendanceReportViewModel.Status = Status.PresentWithNoOutPunch; //Exit Time is Null
if (objDailyAttendanceReportViewModel._inTime >= objDailyAttendanceReportViewModel._firstHalfStartTime + relaxation)
{
ManageLeaves objManageLeaves = new ManageLeaves();
if (objDailyAttendanceReportViewModel._inTime - objDailyAttendanceReportViewModel._firstHalfStartTime >= tsHalfLeave)
{
objDailyAttendanceReportViewModel.Status = Status.OnHalfDayLeave;
}
else if (objDailyAttendanceReportViewModel._inTime - objDailyAttendanceReportViewModel._firstHalfStartTime >= tsShortLeave)
{
objDailyAttendanceReportViewModel.Status = Status.OnShortLeave;
}
else
objDailyAttendanceReportViewModel.Status = Status.Late;
objDailyAttendanceReportViewModel.LateByDuration = (objDailyAttendanceReportViewModel._firstHalfStartTime < objDailyAttendanceReportViewModel._inTime) ? (objDailyAttendanceReportViewModel._inTime - objDailyAttendanceReportViewModel._firstHalfStartTime).Duration() : TimeSpan.Parse("00:00:00");
}
objDailyAttendanceReportViewModel.EarlyGoingByDuration = (objDailyAttendanceReportViewModel._inTime.TimeOfDay > TimeSpan.Parse("00:00:00")) ? ((objDailyAttendanceReportViewModel._secondHalfEndTime > objDailyAttendanceReportViewModel._outTime) ? (objDailyAttendanceReportViewModel._secondHalfEndTime - objDailyAttendanceReportViewModel._outTime).Duration() : TimeSpan.Parse("00:00:00")) : TimeSpan.Parse("00:00:00");
objDailyAttendanceReportViewModel.Duration = GetTotalTime(objDailyAttendanceReportViewModel.EmployeeId, date);// Exit time null waali
if (TimeSpan.Parse(objDailyAttendanceReportViewModel.TotalDuration) > new TimeSpan(0, 0, 0)) // Iterartes only if emp is Present
objDailyAttendanceReportViewModel.PunchRecords = GetPunchRecords(objDailyAttendanceReportViewModel.EmployeeId, date);
else
objDailyAttendanceReportViewModel.PunchRecords = "No Punch Records"; //Employee is Absent
TimeSpan totalShiftDuration = objDailyAttendanceReportViewModel._secondHalfEndTime - objDailyAttendanceReportViewModel._firstHalfStartTime;
if (TimeSpan.Parse(objDailyAttendanceReportViewModel.TotalDuration) == new TimeSpan(0, 0, 0))
{
objDailyAttendanceReportViewModel.Status = Status.Absent;
}
else if (totalShiftDuration - objDailyAttendanceReportViewModel.Duration >= tsHalfLeave)
{
//ManageLeaves objManageLeaves = new ManageLeaves();
//objManageLeaves.AssignLeave(objDailyAttendanceReportViewModel.EmployeeId, objDailyAttendanceReportViewModel.Date, (int)LeaveTypes.HDL);
objDailyAttendanceReportViewModel.Status = Status.OnHalfDayLeave;
}
}
else
{
objDailyAttendanceReportViewModel.InTime = "00:00:00.0000000";
objDailyAttendanceReportViewModel.OutTime = "00:00:00.0000000";
ManageLeaves objManageLeaves = new ManageLeaves();
int TypeOfLeave;// For the type of leave
if (objManageLeaves.IsEmployeeOnLeave(objDailyAttendanceReportViewModel.EmployeeId, date, out TypeOfLeave))
{
objDailyAttendanceReportViewModel.Status = (Status)TypeOfLeave;
}
else
objDailyAttendanceReportViewModel.Status = Status.Absent; // If no leave is there then Absent
objDailyAttendanceReportViewModel.Duration = new TimeSpan(0, 0, 0);
objDailyAttendanceReportViewModel.PunchRecords = "No Punch Records"; //Employee is Absent
}
lstDailyAttendanceReportViewModel.Add(objDailyAttendanceReportViewModel);
}
}
}
catch (Exception)
{
//
}
return lstDailyAttendanceReportViewModel;
}
public bool BindDepartments(out List<Department> objDepartments)
{
DataTable dt;
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
List<Department> objDepartments1 = new List<Department>();
try
{
using (DBDataHelper helper = new DBDataHelper())
{
dt = helper.GetDataTable("Select Id, Name from tblDepartment", SQLTextType.Query);
for (int i = 0; i < dt.Rows.Count; i++)
{
Department objDepartment = new Department();
objDepartment.Id = Convert.ToInt32(dt.Rows[0]["Id"]);
objDepartment.Name = Convert.ToString(dt.Rows[0]["Name"]);
objDepartments1.Add(objDepartment);
}
}
objDepartments = objDepartments1;
return true;
}
catch (Exception)
{
objDepartments = null;
return false;
}
}
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;
}
}
}
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;
}
//Report 5
public List<DayWiseInOutDurationReportViewModel> GetDataForDailyInOutDurationReport(DateTime date)
{
List<DayWiseInOutDurationReportViewModel> lst = new List<DayWiseInOutDurationReportViewModel>();
DataTable dt;
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
List<SqlParameter> list_params;
try
{
using (DBDataHelper helper = new DBDataHelper())
{
List<TimeTracker> lst_timetracker = new List<TimeTracker>();
list_params = new List<SqlParameter>() { new SqlParameter("@date", date) };
dt = helper.GetDataTable("spGetEntriesDateWise", SQLTextType.Stored_Proc, list_params);
foreach (DataRow row in dt.Rows)
{
TimeTracker tracker = new TimeTracker();
tracker.EmployeeID = Convert.ToInt32(row[0].ToString());
tracker.InTime = row[1].ToString();
//We can set the status as No Out Punch here
//To DO : MUDIT JUNEJA :: PLEASE DON'T FORGET :P
tracker.OutTime = row[2] == DBNull.Value ? "16:10:00.0000000" : row[2].ToString();
lst_timetracker.Add(tracker);
}
foreach (var item in lst_timetracker.GroupBy(i => i.EmployeeID).Select(grp => grp.First()))
{
DayWiseInOutDurationReportViewModel model = new DayWiseInOutDurationReportViewModel();
model.EmployeeCode = item.EmployeeID;
model.Tracker.AddRange(lst_timetracker.FindAll(j => j.EmployeeID == item.EmployeeID));
lst.Add(model);
}
}
}
// }
catch (Exception)
{
//
}
return lst;
}
/// <summary>
/// This method gets the faculty by username and password.
/// </summary>
/// <param name="username">A string containing the username of the faculty.</param>
/// <param name="password">A string containing the password of the faculty.</param>
/// <returns>It returns an object of type faculty.</returns>
public Faculty GetFacultyByUsernameAndPassword(string username,string password)
{
SqlParameter Username = new SqlParameter("username", username);
SqlParameter Password = new SqlParameter("password", password);
List<SqlParameter> parameterCollection = new List<SqlParameter>() { Username, Password };
DataTable facultyDataTable = new DataTable();
using (DBDataHelper helper = new DBDataHelper())
{
facultyDataTable = helper.GetDataTable("dbo.GetFacultyByUsernameAndPassword", SQLTextType.Stored_Proc, parameterCollection);
}
if (facultyDataTable.Rows.Count != 0)
{
return GenerateFaculty(facultyDataTable);
}
else
return null;
}
//By : Anup Kumar Gupta
//Date : 21/12/2015
//Refer: BasicReportViewModel
public List<DetailedReportViewModel> GetDataForDetailedReport(DateTime date)
{
List<DetailedReportViewModel> lstDetailedReportViewModel = new List<DetailedReportViewModel>();
DataTable dt, dtShifts, dtPunchRecords;
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("spGetEmployeesForDetailedReport", SQLTextType.Stored_Proc, list_params);
dtShifts = helper.GetDataTable("spGetActiveShift", SQLTextType.Stored_Proc);
foreach (DataRow row in dt.Rows)
{
DetailedReportViewModel model = new DetailedReportViewModel();
model.EmployeeCode = Int32.Parse(row[1].ToString());
model.Name = row[0].ToString();
if (row[2] != DBNull.Value)
{
model.InTime = row[2].ToString();
model.OutTime = row[3] == DBNull.Value ? dtShifts.Rows[0]["SecondHalfEnd"].ToString() : row[3].ToString();
if (row[3] != DBNull.Value)
model.Status = "Present";
else
model.Status = "PresentWithNoOutPunch";
}
else
{
model.InTime = "00:00:00.0000000";
model.OutTime = "00:00:00.0000000";
ManageLeaves objManageLeaves = new ManageLeaves();
string TypeOfLeave;
if (objManageLeaves.IsEmployeeOnLeave(model.EmployeeCode, date, out TypeOfLeave))
model.Status = TypeOfLeave;
else
model.Status = "Absent";
}
model.TotalDuration = model.Duration.ToString();
model.FirstHalfStartTime = dtShifts.Rows[0]["FirstHalfStart"].ToString();
model.FirstHalfEndTime = dtShifts.Rows[0]["FirstHalfEnd"].ToString();
model.SecondHalfStartTime = dtShifts.Rows[0]["SecondHalfStart"].ToString();
model.SecondHalfEndTime = dtShifts.Rows[0]["SecondHalfEnd"].ToString();
model.Date = date.Date;
List<SqlParameter> list_paramsForPunchRecords = new List<SqlParameter>();
list_paramsForPunchRecords.Add(new SqlParameter("@date", date));
list_paramsForPunchRecords.Add(new SqlParameter("@employeeId", model.EmployeeCode));
dtPunchRecords = helper.GetDataTable("spGetPunchRecordsOfEmployeeDateWise", SQLTextType.Stored_Proc, list_paramsForPunchRecords);
foreach (DataRow rowPunchRecords in dtPunchRecords.Rows)
{
model.PunchRecords += rowPunchRecords[0].ToString() + " in(AKGEC), " + rowPunchRecords[1].ToString() + " out(AKGEC). ";
}
lstDetailedReportViewModel.Add(model);
}
}
}
catch (Exception)
{
//
}
return lstDetailedReportViewModel;
}
public List<Attendance> GetEmployeesLateByDate(DateTime date)
{
List<Attendance> objAttendances = new List<Attendance>();
DataTable dt;
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("spGetEmployeesDefaultersByDate", SQLTextType.Stored_Proc, list_params);
for (int i = 0; i < dt.Rows.Count; i++)
{
Attendance objAttendance = new Attendance();
objAttendance.EmployeeId = Convert.ToInt32(dt.Rows[i]["EmployeeId"]);
objAttendance.EntryTime = Convert.ToDateTime(dt.Rows[0]["EntryTime"]);
objAttendances.Add(objAttendance);
}
}
return objAttendances;
}
catch (Exception)
{
return objAttendances;
}
}
public string GetPunchRecords(int employeeId, DateTime date)
{
List<SqlParameter> list_paramsForPunchRecords = new List<SqlParameter>() { new SqlParameter("@date", date), new SqlParameter("@employeeId", employeeId) };
string punchRecords = "";
DataTable dtPunchRecords;
using (DBDataHelper helper = new DBDataHelper())
{
dtPunchRecords = helper.GetDataTable("spGetPunchRecordsOfEmployeeDateWise", SQLTextType.Stored_Proc, list_paramsForPunchRecords);
foreach (DataRow rowPunchRecords in dtPunchRecords.Rows)
{
punchRecords += rowPunchRecords[0].ToString() + " in(AKGEC), " + rowPunchRecords[1].ToString() + " out(AKGEC). ";
}
}
return punchRecords == "" ? "No Punch Records" : punchRecords;
}
//Report 4
public List<EmployeeMonthlyDetailedReportViewModel> GetDataForMonthlyEmployeeDetailedReport(DateTime startDate, DateTime endDate, int empCode)
{
List<EmployeeMonthlyDetailedReportViewModel> lst = new List<EmployeeMonthlyDetailedReportViewModel>();
DataTable dt;
DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
List<SqlParameter> list_params;
try
{
using (DBDataHelper helper = new DBDataHelper())
{
for (DateTime date = startDate.Date; date < endDate.Date; date = date.AddDays(1))
{
list_params = new List<SqlParameter>() { new SqlParameter("@date", date) };
dt = helper.GetDataTable("spGetDayStatus", SQLTextType.Stored_Proc, list_params);
DayStatus status;
if (dt.Rows.Count > 0)
{
status = (DayStatus)dt.Rows[0][0];
}
else
{
status = DayStatus.Active;
}
list_params.Clear();
list_params = new List<SqlParameter>() { new SqlParameter("@date", date), new SqlParameter("@employeeId", empCode) };
dt = helper.GetDataTable("spGetEntriesDateAndEmployeeWise", SQLTextType.Stored_Proc, list_params);
foreach (DataRow row in dt.Rows)
{
EmployeeMonthlyDetailedReportViewModel model = new EmployeeMonthlyDetailedReportViewModel();
model.Status = status == DayStatus.WeeklyOff ? Status.WeeklyOffPresent : Status.Present;
model.Date = date.ToString();
model.InTime = row[0].ToString();
model.OutTime = row[1] == DBNull.Value ? "00:00:00.0000000" : row[1].ToString();
lst.Add(model);
}
list_params.Clear();
}
}
}
catch (Exception)
{
//
}
return lst;
}
//By : Mudit Juneja
//Date : 19/12/2015
//Refer: BasicReportViewModel
public List<BasicReportViewModel> GetDataForBasicReport(DateTime date)
{
List<BasicReportViewModel> lst_basic = new List<BasicReportViewModel>();
DataTable dt, dtShifts;
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("spGetEmployeesForBasicReport", SQLTextType.Stored_Proc, list_params);
dtShifts = helper.GetDataTable("spGetActiveShift", SQLTextType.Stored_Proc);
foreach (DataRow row in dt.Rows)
{
BasicReportViewModel model = new BasicReportViewModel();
model.EmployeeCode = Int32.Parse(row[1].ToString());
model.Name = row[0].ToString();
if (row[2] != DBNull.Value)
{
model.InTime = row[2].ToString();
model.OutTime = row[3] == DBNull.Value ? dtShifts.Rows[0]["SecondHalfEnd"].ToString() : row[3].ToString();
if (row[3] != DBNull.Value)
model.Status = "Present";
else
model.Status = "PresentWithNoOutPunch";
}
else
{
model.InTime = "00:00:00.0000000";
model.OutTime = "00:00:00.0000000";
ManageLeaves objManageLeaves = new ManageLeaves();
string TypeOfLeave;// For Later Use
if (objManageLeaves.IsEmployeeOnLeave(model.EmployeeCode, date, out TypeOfLeave))
model.Status = TypeOfLeave;
else
model.Status = "Absent";
}
model.InTime = row[2].ToString();
model.OutTime = row[3] == DBNull.Value ? "00:00:00.0000000" : row[3].ToString();
model.TotalDuration = model.Duration.ToString();
model.SecondHalfStartTime = dtShifts.Rows[0]["SecondHalfStart"].ToString();
lst_basic.Add(model);
}
}
}
catch (Exception)
{
//
}
return lst_basic;
}
