public IEnumerable <ModuleDTOs> GetChildMenuList(int parentid, int roleid)
{
// throw new NotImplementedException();
var conn = DbConnectHelper.GetConnection();
conn.Open();
SqlCommand cmd = new SqlCommand("sp_GetChildMenuList", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ParentId", parentid);
cmd.Parameters.AddWithValue("@RoleId", roleid);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
da.Dispose();
cmd.Dispose();
conn.Close();
conn.Dispose();
foreach (DataRow row in dt.Rows)
{
yield return(new ModuleDTOs
{
MOduleName = row["MOduleName"].ToString(),
ModuleId = Convert.ToInt32(row["ModuleId"].ToString()),
ModuleCssClass = row["ModuleCssClass"].ToString(),
MduleLink = row["MduleLink"].ToString(),
Controller = row["Controller"].ToString(),
});
}
}
//public List<ModuleDTOsForparent> GetDefaultParentMenu()
//{
// List<ParentModule> modelDatas = new List<ParentModule>();
// ModuleDTOsForparent modelDtos = new ModuleDTOsForparent();
// List<ModuleDTOsForparent> modelDtosList = new List<ModuleDTOsForparent>();
// modelDatas = _unitOfWork.ParentModuleRepository.Get(x => x.IsDefault == true).ToList();
// foreach (var item2 in modelDatas)
// {
// modelDtos.MduleLink = item2.ParentModuleLink;
// modelDtos.MOduleName = item2.parentModuleName;
// modelDtos.Order = item2.ParentModuleOrder;
// modelDtos.ModuleCssClass = item2.ParentModulesCssClass;
// modelDtos.ModuleParentId = item2.ParentModulesId;
// };
// var da = ModuleResponseFormatter.ConvertModuleDatatoParentModel(modelDtos);
// modelDtosList.Add(da);
// modelDtosList = modelDtosList.GroupBy(x => x.ModuleParentId).Select(x => x.First()).ToList();
// return modelDtosList;
//}
public int GetParentId(string ControllerName)
{
ControllerName = ControllerName.ToLower();
int ReturnValue = 0;
var conn = DbConnectHelper.GetConnection();
conn.Open();
SqlCommand cmd = new SqlCommand("sp_GetMainParentId", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@controllerName", ControllerName);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
da.Dispose();
cmd.Dispose();
conn.Close();
conn.Dispose();
foreach (DataRow row in dt.Rows)
{
ReturnValue = Convert.ToInt32(row["ModuleId"].ToString());
}
return(ReturnValue);
}
public int UpdateLeaveYear(LeaveYearDTO leaveYear)
{
LeaveYearDTO lyd = new LeaveYearDTO();
SqlConnection conn = DbConnectHelper.GetConnection();
conn.Open();
SqlCommand cmd = new SqlCommand("sp_LeaveYearEdit", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@YearId", leaveYear.YearId);
cmd.Parameters.AddWithValue("@YearName", leaveYear.YearName);
cmd.Parameters.AddWithValue("@YearStart", leaveYear.YearStartDate);
cmd.Parameters.AddWithValue("@YearEnd", leaveYear.YearEndDate);
cmd.Parameters.AddWithValue("@YearStartNP", leaveYear.YearStartDateNp);
cmd.Parameters.AddWithValue("@YearEndNP", leaveYear.YearEndDateNp);
cmd.Parameters.AddWithValue("@IsActive", leaveYear.YearCurrent);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
da.Dispose();
cmd.Dispose();
conn.Close();
conn.Dispose();
return(dt.Rows.Count);
}
public void AddSpecialLeaveType(LeaveApplicationDTO newLeave)
{
newLeave.LeaveDaysType = "F";
newLeave.RecommededEmpCode = newLeave.RecommededEmpCode;
newLeave.RecommendStatus = 2;
newLeave.LeaveStatus = 2;
newLeave.RecommenderMessage = newLeave.LeaveDetails;
newLeave.RecommendStatusDate = DateTime.Now;
newLeave.LeaveDays = newLeave.LeaveEndDate.Date.Subtract(newLeave.LeaveStartDate.Date).Duration().Days + 1;
newLeave.LeaveAppliedDate = DateTime.Now;
newLeave.LeaveGUICode = Guid.NewGuid().ToString();
bool payable = Convert.ToBoolean(_unitOfWork.LeaveTypeRepository.Get(x => x.LeaveTypeId == newLeave.LeaveId).Select(x => x.IsPayable).SingleOrDefault());
newLeave.PaidLeave = Convert.ToBoolean(payable);
LeaveApplication Record = LeaveApplicationRequestFormatter.ConvertRespondentInfoFromDTO(newLeave);
_unitOfWork.LeaveApplicationRepository.Create(Record);
//call store proecudre for leave application attendance record
SqlConnection conn = DbConnectHelper.GetConnection();
conn.Open();
SqlCommand cmd = new SqlCommand("sp_LeaveBalanceUpdate", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@LeaveId", Record.LeaveId);
cmd.Parameters.AddWithValue("@LeaveStatus", Record.LeaveStatus);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
da.Dispose();
cmd.Dispose();
conn.Close();
conn.Dispose();
}
public IEnumerable <HolidayOfficesViewModel> HolidayOfficeById(int id)
{
var conn = DbConnectHelper.GetConnection();
conn.Open();
SqlCommand cmd = new SqlCommand("sp_HolidayAssignedOficeList", conn);
cmd.Parameters.AddWithValue("@HilidayId", id);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
da.Dispose();
cmd.Dispose();
conn.Close();
conn.Dispose();
foreach (DataRow row in dt.Rows)
{
yield return(new HolidayOfficesViewModel
{
HolidayId = Convert.ToInt32(row["HolidayId"].ToString()),
OfficeId = Convert.ToInt32(row["OfficeId"].ToString()),
OfficeName = row["OfficeName"].ToString()
});
}
}
public int InsertKaajAttendance(int Empcode, string sdate, string enddate, string type)
{
try
{
SqlConnection conn = DbConnectHelper.GetConnection();
conn.Open();
SqlCommand cmd = new SqlCommand("sp_Insertkaajattendance", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@startdate", sdate);
cmd.Parameters.AddWithValue("@Empcode", Empcode);
cmd.Parameters.AddWithValue("@enddate", enddate);
cmd.Parameters.AddWithValue("@type", type);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
da.Dispose();
cmd.Dispose();
conn.Close();
conn.Dispose();
return(1);
}
catch (Exception ex)
{
return(0);
}
}
public IEnumerable <SelectListItem> GetApproverList(int empCode)
{
SqlConnection conn = DbConnectHelper.GetConnection();
conn.Open();
SqlCommand cmd = new SqlCommand("[sp_MyApprover_Result]", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@EmpCode", empCode);
SqlDataAdapter da = new SqlDataAdapter(cmd);
System.Data.DataTable dt = new System.Data.DataTable();
da.Fill(dt);
da.Dispose();
cmd.Dispose();
conn.Close();
conn.Dispose();
List <SelectListItem> Records = new List <SelectListItem>();
foreach (DataRow dr in dt.Rows)
{
SelectListItem single = new SelectListItem()
{
Value = dr["EmpCode"].ToString(),
Text = dr["EmpName"].ToString()
};
Records.Add(single);
}
return(Records);
}
public int UpdateLeaveAssignedDaysInital(int assiginId, decimal LeaveDays)
{
try
{
var conn = DbConnectHelper.GetConnection();
conn.Open();
SqlCommand cmd = new SqlCommand("update LeaveAssigned set AssignedDays=" + LeaveDays + " Where AssignedId=" + assiginId, conn);
cmd.CommandType = CommandType.Text;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
da.Dispose();
cmd.Dispose();
conn.Close();
conn.Dispose();
if (dt.Rows.Count > 0)
{
return(0);
}
else
{
return(1);
}
}
catch (Exception ex)
{
return(0);
}
}
public IEnumerable <NotificationsViewModel> SingleNotification(int id)
{
var conn = DbConnectHelper.GetConnection();
conn.Open();
SqlCommand cmd = new SqlCommand("sp_NotificationSingle", conn);
cmd.Parameters.AddWithValue("@NotificationId", id);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
da.Dispose();
cmd.Dispose();
conn.Close();
conn.Dispose();
foreach (DataRow row in dt.Rows)
{
yield return(new NotificationsViewModel
{
NotificationID = row["NotificationId"].ToString(),
ReceiverID = row["NotificationReceiverId"].ToString(),
ReceiverType = row["NotificationReceiverType"].ToString(),
Subject = row["NotificationSubject"].ToString(),
Message = row["NotificationMessage"].ToString(),
Date = row["NotificationDate"].ToString(),
ReadDate = row["NotificationReadDate"].ToString(),
DetailUrl = row["NotificationDetailURL"].ToString(),
EmpName = row["EmpName"].ToString(),
EmpPhoto = row["EmpPhoto"].ToString()
});
}
}
public IEnumerable <OfficeDTOs> GetOfficeData()
{
int EmpCode = Convert.ToInt32(HttpContext.Current.Session["EmpCode"]);
SqlConnection conn = DbConnectHelper.GetConnection();
conn.Open();
SqlCommand cmd = new SqlCommand("sp_MyRoleOfficesListIds", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@EmpCode", EmpCode);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
da.Dispose();
cmd.Dispose();
conn.Close();
conn.Dispose();
List <Office> OfficeList = new List <Office>();
foreach (DataRow dr in dt.Rows)
{
int OfficeId = Convert.ToInt32(dr["ChildOfficeId"]);
Office Office = _unitOfWork.OfficeRepository.GetById(OfficeId);
OfficeList.Add(Office);
}
return(OfficeResponseFormatter.ModelData(OfficeList));
}
public IEnumerable <OfficeDTOs> GetClildOfficeListByEmpCode(int Empcode)
{
OfficeDTOs office = new OfficeDTOs();
var conn = DbConnectHelper.GetConnection();
conn.Open();
SqlCommand cmd = new SqlCommand("sp_MyRoleOfficesList", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@EmpCode", Empcode);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
da.Dispose();
cmd.Dispose();
conn.Close();
conn.Dispose();
foreach (DataRow dr in dt.Rows)
{
yield return(new OfficeDTOs
{
OfficeId = Convert.ToInt32(dr["ChildOfficeId"].ToString()),
OfficeName = dr["ChildOfficeName"].ToString()
});
}
}
public List <int> MyAccessOfficeList()
{
int EmpCode = Convert.ToInt32(HttpContext.Current.Session["EmpCode"]);
SqlConnection conn = DbConnectHelper.GetConnection();
conn.Open();
SqlCommand cmd = new SqlCommand("sp_MyRoleOfficesListIds", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@EmpCode", EmpCode);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
da.Dispose();
cmd.Dispose();
conn.Close();
conn.Dispose();
List <int> OfficeList = new List <int>();
foreach (DataRow dr in dt.Rows)
{
int Officeid = Convert.ToInt32(dr["ChildOfficeId"]);
OfficeList.Add(Officeid);
}
return(OfficeList);
}
public int UpdateFiscal(FiscalDTO data)
{
FiscalDTO res = new FiscalDTO();
SqlConnection conn = DbConnectHelper.GetConnection();
conn.Open();
SqlCommand cmd = new SqlCommand("sp_FiscalEdit", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Id", data.FyId);
cmd.Parameters.AddWithValue("@Name", data.FyName);
cmd.Parameters.AddWithValue("@StartDate", data.FyStartDate);
cmd.Parameters.AddWithValue("@EndDate", data.FyEndDate);
cmd.Parameters.AddWithValue("@StartDateNep", data.FyStartDateNp);
cmd.Parameters.AddWithValue("@EndDateNep", data.FyEndDateNp);
cmd.Parameters.AddWithValue("@IsActive", data.FyCurrent);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
da.Dispose();
cmd.Dispose();
conn.Close();
conn.Dispose();
return(dt.Rows.Count);
}
public void UpdateAsCurrent(EmployeeJobHistoryDTO data)
{
var conn = DbConnectHelper.GetConnection();
conn.Open();
SqlCommand cmd = new SqlCommand("sp_EmployeeTransferUpdate", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@EmpCode", data.EmpCode);
cmd.Parameters.AddWithValue("@EmpOfficeId", data.OfficeId);
cmd.Parameters.AddWithValue("@EmpLevelId", data.LevelId);
cmd.Parameters.AddWithValue("@EmpShiftId", data.ShiftId);
cmd.Parameters.AddWithValue("@EmpDeptId", data.DeptId);
cmd.Parameters.AddWithValue("@EmpSectionId", data.SectionId);
cmd.Parameters.AddWithValue("@EmpDesgId", data.DesgId);
cmd.Parameters.AddWithValue("@EmpTypeId", 1);
cmd.Parameters.AddWithValue("@EmpBgId", data.BusinessGroupId);
cmd.Parameters.AddWithValue("@EmpJobTypeId", data.JobTypeId);
cmd.Parameters.AddWithValue("@EmpRankId", data.RankId);
cmd.Parameters.AddWithValue("@RemoteType", data.RemoteCode);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
cmd.Dispose();
conn.Close();
conn.Dispose();
}
public int UpdateLeaveAssigned(LeaveAssignedDTO data)
{
try
{
var conn = DbConnectHelper.GetConnection();
conn.Open();
SqlCommand cmd = new SqlCommand("UPDATE LeaveAssigned SET AssignedDays=" + data.AssignedDays + " Where AssignEmpCode=" + data.AssignEmpCode + " and AssignLeaveTypeId=" + data.AssignLeaveTypeId + " and AssignedLeaveYearId=" + data.AssignedLeaveYearId, conn);
cmd.CommandType = CommandType.Text;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
da.Dispose();
cmd.Dispose();
conn.Close();
conn.Dispose();
if (dt.Rows.Count > 0)
{
return(0);
}
else
{
return(1);
}
}
catch (Exception ex)
{
return(0);
}
}
public int UpdateIndividualAttendanceDaily(AttendanceDailyViewModel atd)
{
SqlConnection conn = DbConnectHelper.GetConnection();
conn.Open();
SqlCommand cmd = new SqlCommand("sp_updateDailyattendance", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@checkIn", atd.AttCheckIn);
cmd.Parameters.AddWithValue("@CheckOut", atd.AttCheckOut);
cmd.Parameters.AddWithValue("@IsAbsent", atd.IsAbsent);
cmd.Parameters.AddWithValue("@empcode", atd.EmpCode);
cmd.Parameters.AddWithValue("@Date", atd.AttDate);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
da.Dispose();
cmd.Dispose();
conn.Close();
conn.Dispose();
return(0);
}
public IEnumerable <BusinessGroupDTO> GetBusinessGroupByEmpRoleId(int roleId)
{
BusinessGroupDTO bgGroup = new BusinessGroupDTO();
var conn = DbConnectHelper.GetConnection();
conn.Open();
SqlCommand cmd = new SqlCommand("sp_MyRoleBusinessGroups", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@RoleId", roleId);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
da.Dispose();
cmd.Dispose();
conn.Close();
conn.Dispose();
foreach (DataRow dr in dt.Rows)
{
yield return(new BusinessGroupDTO
{
BgId = Convert.ToInt32(dr["BgId"].ToString()),
BgName = dr["BgName"].ToString()
});
}
}
public void GetStartAndEndDate(int month, int year, out string StartDate, out string EndDate)
{
var conn = DbConnectHelper.GetConnection();
conn.Open();
SqlCommand cmd = new SqlCommand("sp_GetStartAndEndDate", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@year", year);
cmd.Parameters.AddWithValue("@month", month);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
string sdate = null;
string edate = null;
foreach (DataRow row in dt.Rows)
{
sdate = row["eng_start_date"].ToString();
edate = row["eng_end_date"].ToString();
}
da.Dispose();
cmd.Dispose();
StartDate = sdate;
EndDate = edate;
conn.Close();
conn.Dispose();
}
public AttendanceEntireYearSummaryViewModel AttendanceEntireYearReportSummary(int Empcode, int FiscalYear, int NepaliMonth)
{
var conn = DbConnectHelper.GetConnection();
conn.Open();
SqlCommand cmd = new SqlCommand("sp_AttendanceEntireYearReportSummary", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@FiscalYear", FiscalYear);
cmd.Parameters.AddWithValue("@NepaliMonth", NepaliMonth);
cmd.Parameters.AddWithValue("@Empcode", Empcode);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
AttendanceEntireYearSummaryViewModel monthlyData = new AttendanceEntireYearSummaryViewModel();
foreach (DataRow row in dt.Rows)
{
monthlyData.earlyexit = Convert.ToInt32(row["EarlyExit"].ToString());
monthlyData.holiday = Convert.ToInt32(row["Holiday"].ToString());
monthlyData.latentry = Convert.ToInt32(row["LateEntry"].ToString());
monthlyData.leave = Convert.ToInt32(row["Leave"].ToString());
monthlyData.officialvisit = Convert.ToInt32(row["OfficialVisit"].ToString());
monthlyData.training = Convert.ToInt32(row["Training"].ToString());
monthlyData.weekend = Convert.ToInt32(row["Weekend"].ToString());
monthlyData.totalDays = Convert.ToInt32(row["Totaldays"].ToString());
monthlyData.totalAbsent = Convert.ToInt32(row["TotalAbsent"].ToString());
monthlyData.presentdays = Convert.ToInt32(row["presentdays"].ToString());
}
return(monthlyData);
}
public IEnumerable <LeaveAssignedViewModel> LeaveAssignDetail(int empcode)
{
var conn = DbConnectHelper.GetConnection();
conn.Open();
SqlCommand cmd = new SqlCommand("select la.AssignedId,la.AssignLeaveTypeId,(select LeaveTypeName from LeaveTypes where LeaveTypeId = la.AssignLeaveTypeId)LeaveTypeName, la.AssignedDays, (la.AssignedDays-la.LeaveTakenDays)AssignedRemainingDays from LeaveAssigned la where AssignEmpCode =" + empcode, conn);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@EmpCode", empcode);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
da.Dispose();
cmd.Dispose();
conn.Close();
conn.Dispose();
foreach (DataRow row in dt.Rows)
{
yield return(new LeaveAssignedViewModel
{
LeaveTypeId = Convert.ToInt32(row["AssignLeaveTypeId"].ToString()),
AssignedId = Convert.ToInt32(row["AssignedId"].ToString()),
LeaveTypename = row["LeaveTypeName"].ToString(),
TotalAssignDay = Convert.ToDecimal(row["AssignedDays"].ToString()),
TotalRemainingDay = Convert.ToDecimal(row["AssignedRemainingDays"].ToString()),
});
}
}
public int CheckLeaveExistence(int EmpCode, int LeaveTypeId, int LeaveYearId)
{
try
{
var conn = DbConnectHelper.GetConnection();
conn.Open();
SqlCommand cmd = new SqlCommand("select * from LeaveApplications Where LeaveEmpCode=" + EmpCode + " and LeaveTypeId=" + LeaveTypeId + " and LeaveYearId=" + LeaveYearId, conn);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@LeaveEmpCode", EmpCode);
cmd.Parameters.AddWithValue("@LeaveTypeId", LeaveTypeId);
cmd.Parameters.AddWithValue("@LeaveYearId", LeaveYearId);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
da.Dispose();
cmd.Dispose();
conn.Close();
conn.Dispose();
if (dt.Rows.Count > 0)
{
return(0);
}
else
{
return(1);
}
}
catch (Exception ex)
{
return(0);
}
}
public PayrollSalaryTableDTO GetPayollGenerationForm(int fyid)
{
List <SelectListItem> OfficeSelectList = new List <SelectListItem>();
int EmployeeCode = Convert.ToInt32(HttpContext.Current.Session["EmpCode"]);
SqlConnection conn = DbConnectHelper.GetConnection();
conn.Open();
SqlCommand cmd = new SqlCommand("sp_MyRoleOfficesList", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@EmpCode", EmployeeCode);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
da.Dispose();
cmd.Dispose();
conn.Close();
conn.Dispose();
foreach (DataRow row in dt.Rows)
{
OfficeSelectList.Add(new SelectListItem
{
Text = row["ChildOfficeName"].ToString(),
Value = row["ChildOfficeId"].ToString()
});
}
IEnumerable <PayrollMonthDescription> MonthDescriptrionList = _unitOfWork.PayrollMonthDescriptipnRepository.All().Where(x => x.FyId == fyid).ToList();
List <SelectListItem> MonthDescription = new List <SelectListItem>();
foreach (var str in MonthDescriptrionList)
{
MonthDescription.Add(new SelectListItem
{
Text = str.MonthNameNepali,
Value = str.Id.ToString()
});
}
PayrollSalaryTableDTO Record = new PayrollSalaryTableDTO();
IEnumerable <Fiscal> FiscalRecord = _unitOfWork.FiscalRepository.All().ToList();
List <SelectListItem> FiscalSelectList = new List <SelectListItem>();
foreach (var Fiscalstr in FiscalRecord)
{
FiscalSelectList.Add(new SelectListItem
{
Text = Fiscalstr.FyName,
Value = Fiscalstr.FyId.ToString()
});
}
Record.CreatorId = EmployeeCode;
Record.FiscalYearList = FiscalSelectList;
Record.OfficeList = OfficeSelectList;
Record.MonthSelectList = MonthDescription;
return(Record);
}
public IEnumerable <AttendanceRequestsListViewModel> GetRequestAttendanceList(int?empcode, int?approverid, int?recommenderid, int?requestid, int?recommendstatus)
{
var conn = DbConnectHelper.GetConnection();
conn.Open();
SqlCommand cmd = new SqlCommand("sp_AttendanceRequestList", conn);
cmd.Parameters.AddWithValue("@Empcode", empcode);
cmd.Parameters.AddWithValue("@RecommenderId", recommenderid);
cmd.Parameters.AddWithValue("@ApproverId", approverid);
cmd.Parameters.AddWithValue("@RequestedId", requestid);
cmd.Parameters.AddWithValue("@RecommendStatus", recommendstatus);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
da.Dispose();
cmd.Dispose();
foreach (DataRow row in dt.Rows)
{
yield return(new AttendanceRequestsListViewModel
{
EmpCode = row["RequestEmpCode"].ToString(),
RequestId = row["RequestId"].ToString(),
EmpName = row["EmpName"].ToString(),
Description = row["RequestDescription"].ToString(),
ApproveDate = row["ApproveStatusDate"].ToString(),
ApproveStatus = row["ApproveStatus"].ToString(),
RecommendeDate = row["RecommendStatusDate"].ToString(),
RecommendStatus = row["RecommendStatus"].ToString(),
Designation = row["DsgName"].ToString(),
RequestDate = row["RequestedDate"].ToString(),
RequestType = row["RequestType"].ToString(),
ApproveMessage = row["ApproverMessage"].ToString(),
RecommendMessage = row["RecommedarMessage"].ToString(),
CheckIn_Date = row["CheckInDate"].ToString(),
CheckOut_Date = row["CheckOutDate"].ToString(),
RecommenderEmpCode = row["RecommendarEmpCode"].ToString(),
ApproverEmpCode = row["ApproverEmpCode"].ToString(),
EmpEmail = row["EmpEmail"].ToString(),
JoinDate = row["EmpAppointmentDate"].ToString(),
Recommender = row["Recommender"].ToString(),
Approver = row["Approver"].ToString(),
IpAddress = row["RequestIPAddress"].ToString(),
ApproverStatus = Convert.ToInt32(row["ApproveStatus"].ToString())
});
conn.Close();
conn.Dispose();
}
}
public IEnumerable <PayrollSalaryViewModel> GetYearlyPayrollSalaryTable(int fyId, int officeId)
{
SqlConnection conn = DbConnectHelper.GetConnection();
conn.Open();
SqlCommand cmd = new SqlCommand("sp_GetPayrollSalaryTable", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@fiscalYearId", fyId);
cmd.Parameters.AddWithValue("@officeId", officeId);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
da.Dispose();
cmd.Dispose();
conn.Close();
conn.Dispose();
IEnumerable <PayrollSalaryViewModel> payrollSalaryTableDTO = new DataTableToEntityMapper().Map <PayrollSalaryViewModel>(dt).ToList();
foreach (var item in payrollSalaryTableDTO)
{
item.AllowanceViewModel = GetAllowanceData(item.EmployeeCode, fyId);
item.TaxModel = GetTaxData(item.EmployeeCode, fyId);
item.EmployeeName = _unitOfWork.EmployeeRepository.GetById(item.EmployeeCode).EmpName;
item.OfficeName = _unitOfWork.OfficeRepository.GetById(officeId).OfficeName;
item.FiscalYear = _unitOfWork.FiscalRepository.GetById(fyId).FyName;
int dsgID = _unitOfWork.EmployeeRepository.GetById(item.EmployeeCode).EmpDesgId;
item.EmpPost = _unitOfWork.DesignationRepository.GetById(dsgID).DsgName;
item.GradeNo = (int)_unitOfWork.EmployeeRepository.GetById(item.EmployeeCode).CurrentGrade;
item.PANNo = _employeeService.GetEmployeeByID(item.EmployeeCode).PANNumber;
item.CitNo = _employeeService.GetEmployeeByID(item.EmployeeCode).CitNumber;
item.InsurancePremium = _insuranceService.GetInsuranceInfobyEmpCode(item.EmployeeCode, fyId);
item.LoanIntIncome = _intGainService.GetInterestGainByEmpCode(item.EmployeeCode).InterestGain;
if (item.LoanIntIncome == null)
{
item.LoanIntIncome = 0;
}
//foreach (var items in item.InsurancePremium)
//{
// item.InsuranceID = items.IsuranceClaimId;
// item.InsuranceCompanyName = items.InsuranceCompany.CompanyName;
// item.PolicyNumber = items.InsurancePolicyNumber;
// item.InsuredAmount = (double)items.InsuredAmount;
// item.InsuranceEffectedDate = (DateTime)items.StartDate;
// item.MaturityDate = (DateTime)items.EndDate;
// item.AnnualPremium = (double)items.PremiumAmount;
//}
}
return(payrollSalaryTableDTO);
}
public IEnumerable <DailyAttendanceFilterViewModel> GetAttendanceDaily(int?EmpOfficeId, int?EmpDesgId, int?BgId, int?EmpDeptId, int?EmpCode, DateTime StrtDate)
{
// List<DailyAttendanceFilterViewModel> attlist = new List<DailyAttendanceFilterViewModel>();
var conn = DbConnectHelper.GetConnection();
conn.Open();
SqlCommand cmd = new SqlCommand("sp_AttendanceDaily", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@StartDate", StrtDate);
cmd.Parameters.AddWithValue("@OfficeID", EmpOfficeId);
cmd.Parameters.AddWithValue("@DegID", EmpDesgId);
cmd.Parameters.AddWithValue("@EmpCode", EmpCode);
cmd.Parameters.AddWithValue("@EmpTypeId", BgId);
cmd.Parameters.AddWithValue("@DeptId", EmpDeptId);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
da.Dispose();
cmd.Dispose();
conn.Close();
conn.Dispose();
foreach (DataRow row in dt.Rows)
{
yield return(new DailyAttendanceFilterViewModel
{
code = row["EmpCode"].ToString(),
AttDate = row["AttDate"].ToString(),
AttCheckIn = row["AttCheckIn"].ToString(),
AttCheckOut = row["AttCheckOut"].ToString(),
Isleave = row["IsLeave"].ToString(),
IsAbsent = row["IsAbsent"].ToString(),
IsWeekend = row["IsWeekend"].ToString(),
IsHoliday = row["IsHoliday"].ToString(),
IsOfficialVisit = row["IsOfficialVisit"].ToString(),
IsTraining = row["IsTraining"].ToString(),
EmpName = row["EmpName"].ToString(),
DsgName = row["DsgName"].ToString(),
OfficeName = row["OfficeName"].ToString(),
Worked_Hour = row["Worked_Hour"].ToString(),
ShiftName = row["ShiftName"].ToString(),
GroupName = row["GroupName"].ToString(),
ShiftDelayAllow = row["ShiftDelayAllow"].ToString(),
AttShiftEnd = row["AttShiftEnd"].ToString(),
AttShiftStart = row["AttShiftStart"].ToString()
});
}
}
public IEnumerable <DailyAttendanceFilterViewModel> GetAttendanceDailyStatus(int AdminEmpCode, DateTime date, int?code, int?degid, int?officeid)
{
var conn = DbConnectHelper.GetConnection();
conn.Open();
SqlCommand cmd = new SqlCommand("sp_EmployeeDailyAttendanceStatus", conn);
cmd.CommandType = CommandType.StoredProcedure;
DateTime date1 = DateTime.Now;
cmd.Parameters.AddWithValue("@AdminEmpCode", AdminEmpCode);
cmd.Parameters.AddWithValue("@Searchdate", date);
cmd.Parameters.AddWithValue("@OfficeId", officeid);
cmd.Parameters.AddWithValue("@DegID", degid);
cmd.Parameters.AddWithValue("@EmpCode", code);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
da.Dispose();
cmd.Dispose();
foreach (DataRow row in dt.Rows)
{
yield return(new DailyAttendanceFilterViewModel
{
code = row["EmpCode"].ToString(),
AttDate = row["AttDate"].ToString(),
AttCheckIn = row["AttCheckIn"].ToString(),
AttCheckOut = row["AttCheckOut"].ToString(),
Isleave = row["IsLeave"].ToString(),
IsAbsent = row["IsAbsent"].ToString(),
IsWeekend = row["IsWeekend"].ToString(),
IsHoliday = row["IsHoliday"].ToString(),
IsTraining = row["IsTraining"].ToString(),
IsOfficialVisit = row["IsOfficialVisit"].ToString(),
EmpName = row["EmpName"].ToString(),
DsgName = row["DsgName"].ToString(),
OfficeName = row["OfficeName"].ToString(),
Worked_Hour = row["Worked_Hour"].ToString(),
ShiftDelayAllow = row["ShiftDelayAllow"].ToString(),
AttShiftEnd = row["AttShiftEnd"].ToString(),
AttShiftStart = row["AttShiftStart"].ToString()
});
}
conn.Close();
conn.Dispose();
}
public IEnumerable <AttendanceRequestsListViewModel> GetrequestAttendanceListByParms(int?officeid, int?empcode, DateTime startdate, DateTime enddate, int?reccode, int?appcode, int?recomendstatus, int?approvestatus)
{
var conn = DbConnectHelper.GetConnection();
conn.Open();
SqlCommand cmd = new SqlCommand("sp_AttendanceRequestListByParms", conn);
cmd.Parameters.AddWithValue("@StartDate", startdate);
cmd.Parameters.AddWithValue("@EndDate", enddate);
cmd.Parameters.AddWithValue("@OfficeId", officeid);
cmd.Parameters.AddWithValue("@EmpCode", empcode);
cmd.Parameters.AddWithValue("@ApproverCode", appcode);
cmd.Parameters.AddWithValue("@RecEmpCode", reccode);
cmd.Parameters.AddWithValue("@Recommendstatus", recomendstatus);
cmd.Parameters.AddWithValue("@approvestatus", approvestatus);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
da.Dispose();
cmd.Dispose();
foreach (DataRow row in dt.Rows)
{
yield return(new AttendanceRequestsListViewModel
{
EmpCode = row["RequestEmpCode"].ToString(),
RequestId = row["RequestId"].ToString(),
EmpName = row["EmpName"].ToString(),
Description = row["RequestDescription"].ToString(),
ApproveDate = row["ApproveStatusDate"].ToString(),
ApproveStatus = row["ApproveStatus"].ToString(),
RecommendeDate = row["RecommendStatusDate"].ToString(),
RecommendStatus = row["RecommendStatus"].ToString(),
Designation = row["DsgName"].ToString(),
RequestDate = row["RequestedDate"].ToString(),
RequestType = row["RequestType"].ToString(),
ApproveMessage = row["ApproverMessage"].ToString(),
RecommendMessage = row["RecommedarMessage"].ToString(),
CheckIn_Date = row["CheckInDate"].ToString(),
CheckOut_Date = row["CheckOutDate"].ToString(),
RecommenderEmpCode = row["RecommendarEmpCode"].ToString(),
ApproverEmpCode = row["ApproverEmpCode"].ToString()
});
conn.Close();
conn.Dispose();
}
}
public int DeleteLeaveRule(int id)
{
var conn = DbConnectHelper.GetConnection();
conn.Open();
SqlCommand cmd = new SqlCommand("sp_LeaveRuleDelete", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@LeaveRuleId", id);
int abc = cmd.ExecuteNonQuery();
cmd.Dispose();
conn.Close();
conn.Dispose();
return(abc);
}
public void UpdateHoliday(string date)
{
DateTime Holidaydate = Convert.ToDateTime(date);
var conn = DbConnectHelper.GetConnection();
conn.Open();
SqlCommand cmd = new SqlCommand("sp_UpdateHoliday", conn);
cmd.Parameters.AddWithValue("@HolidayDate", Holidaydate);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
da.Dispose();
cmd.Dispose();
}
public void GetenareDailyAttendance(int requestid)
{
var conn = DbConnectHelper.GetConnection();
conn.Open();
SqlCommand cmd = new SqlCommand("sp_AttendanceRequestUpdate ", conn);
cmd.Parameters.AddWithValue("@RequestID", requestid);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
da.Dispose();
cmd.Dispose();
conn.Close();
conn.Dispose();
}
