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(); }