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 <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 <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 <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();
}
public void DeleteHolidayOffice(int id)
{
var conn = DbConnectHelper.GetConnection();
conn.Open();
SqlCommand cmd = new SqlCommand("sp_DeleteOfficelist", conn);
cmd.Parameters.AddWithValue("@HolidayId", 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();
}
public void DeleteAttendancerequest(int id)
{
var conn = DbConnectHelper.GetConnection();
conn.Open();
SqlCommand cmd = new SqlCommand("sp_deleteAttendance", conn);
cmd.Parameters.AddWithValue("@requestId", 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();
}
public void InsertholidayOffices(int holidayId, int OfficeId)
{
var conn = DbConnectHelper.GetConnection();
conn.Open();
SqlCommand cmd = new SqlCommand("sp_InsertHolidayAssignedOffice", conn);
cmd.Parameters.AddWithValue("@HolidayId", holidayId);
cmd.Parameters.AddWithValue("@OfficeId", OfficeId);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
da.Dispose();
cmd.Dispose();
conn.Close();
conn.Dispose();
}
public void UpdateAttendaceonTraining(int id, string mode)
{
SqlConnection conn = DbConnectHelper.GetConnection();
conn.Open();
SqlCommand cmd = new SqlCommand("Sp_TrainingAttendanceRecord", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@TrainingId", id);
cmd.Parameters.AddWithValue("@mode", mode);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
da.Dispose();
cmd.Dispose();
conn.Close();
conn.Dispose();
}
public int RejectApprovedAttendance(AttendanceRequestDTO atd)
{
var conn = DbConnectHelper.GetConnection();
conn.Open();
SqlCommand cmd = new SqlCommand("sp_AttendanceRequestReject ", conn);
cmd.Parameters.AddWithValue("@RequestID", atd.RequestId);
cmd.Parameters.AddWithValue("@ApproveMessage", atd.ApproverMessage);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
da.Dispose();
cmd.Dispose();
conn.Close();
conn.Dispose();
return(0);
}
public int UpdateLeaveRuleDetails(LeaveRuleDetailDTO editLRD)
{
var conn = DbConnectHelper.GetConnection();
conn.Open();
SqlCommand cmd = new SqlCommand("sp_InsertLeaveRuleDetails", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@DetailID", editLRD.DetailId);
cmd.Parameters.AddWithValue("@LeaveRuleId", editLRD.LeaveRuleId);
cmd.Parameters.AddWithValue("@LeaveTypeId", editLRD.LeaveTypeId);
cmd.Parameters.AddWithValue("@LeaveDays", editLRD.LeaveDays);
int abc = cmd.ExecuteNonQuery();
cmd.Dispose();
conn.Close();
conn.Dispose();
return(abc);
}
public DataTable AttendanceTotalDaysSummary(DateTime sdate, DateTime enddate, int office)
{
SqlConnection conn = DbConnectHelper.GetConnection();
conn.Open();
SqlCommand cmd = new SqlCommand("sp_AttendanceTotalDaysReports", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@startdate", sdate);
cmd.Parameters.AddWithValue("@endate", enddate);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
da.Dispose();
cmd.Dispose();
conn.Close();
conn.Dispose();
return(dt);
}
public IEnumerable <TaxViewModel> GetTaxData(int empcode, int fyId)
{
SqlConnection conn = DbConnectHelper.GetConnection();
conn.Open();
SqlCommand cmd = new SqlCommand("select d.OrderNumber,sum(d.DeductedAmount)as totalValue from PayrollSalaryMasterSheet m,PayrollEmployeeTaxDetail d,PayrollSalaryTable s where m.Id=d.PayrollMasterSheetId and s.Id = m.PayrollSalaryTableId AND m.EmployeeCode = " + empcode + " AND s.FyId=" + fyId + " GROUP BY d.OrderNumber", conn);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@fiscalYearId", fyId);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
da.Dispose();
cmd.Dispose();
conn.Close();
conn.Dispose();
IEnumerable <TaxViewModel> TaxData = new DataTableToEntityMapper().Map <TaxViewModel>(dt).ToList();
return(TaxData);
}
public IEnumerable <AllowanceViewModel> GetAllowanceData(int empcode, int fyId)
{
SqlConnection conn = DbConnectHelper.GetConnection();
conn.Open();
SqlCommand cmd = new SqlCommand("select d.AllowanceId,sum(d.CalculatedValue)as totalValue from PayrollSalaryMasterSheet m, PayrollSalaryDetailSheet d, PayrollSalaryTable s where m.Id = d.PayrollSalaryMasterId and s.Id = m.PayrollSalaryTableId AND m.EmployeeCode =" + empcode + " AND s.FyId = " + fyId + " AND d.AllowanceType <> 'R' GROUP BY d.AllowanceId", conn);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@fiscalYearId", fyId);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
da.Dispose();
cmd.Dispose();
conn.Close();
conn.Dispose();
IEnumerable <AllowanceViewModel> AllowanceData = new DataTableToEntityMapper().Map <AllowanceViewModel>(dt).ToList();
return(AllowanceData);
}
public void LeaveApplicationUpdae(LeaveApplicationDTOs Record)
{
_unitOfWork.LeaveApplicationRepository.Update(LeaveApplicationMapper.LeaveApplicationDTOsToLeaveApplication(Record));
//if leave is approved
if (Record.LeaveStatus == 2 || Record.LeaveStatus == 3)
{
//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();
}
else if (Record.LeaveStatus == 5)
{
//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 LeaveYearDTO InsertLeaveYear(LeaveYearDTO newLeaveYear)
{
LeaveYearDTO lyd = new LeaveYearDTO();
SqlConnection conn = DbConnectHelper.GetConnection();
conn.Open();
SqlCommand cmd = new SqlCommand("sp_LeaveYearAdd", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@YearName", newLeaveYear.YearName);
cmd.Parameters.AddWithValue("@YearStart", newLeaveYear.YearStartDate);
cmd.Parameters.AddWithValue("@YearEnd", newLeaveYear.YearEndDate);
cmd.Parameters.AddWithValue("@YearStartNP", newLeaveYear.YearStartDateNp);
cmd.Parameters.AddWithValue("@YearEndNP", newLeaveYear.YearEndDateNp);
cmd.Parameters.AddWithValue("@IsActive", newLeaveYear.YearCurrent);
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)
{
return(new LeaveYearDTO {
YearName = Convert.ToInt32(row["YearName"]),
YearStartDate = Convert.ToDateTime(row["YearStartDate"].ToString()),
YearEndDate = Convert.ToDateTime(row["YearEndDate"].ToString()),
YearStartDateNp = row["YearStartDateNP"].ToString(),
YearEndDateNp = row["YearEndDateNP"].ToString(),
YearCurrent = Convert.ToBoolean(row["YearCurrent"].ToString())
});
}
return(lyd);
}
public DataTable GetPayrollEmployeeTaxDetails(string id)
{
int employeeCode = Convert.ToInt32(id);
SqlConnection conn = DbConnectHelper.GetConnection();
conn.Open();
SqlCommand cmd = new SqlCommand("[dbo].[sp_PayrollEmployeeTaxPivot]", 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();
return(dt);
//return PayrollEmployeeTaxDetailResponseFormatter.GetAllTaxDetailSheet(_unitOfWork.PayrollEmployeeTaxDetail.Get(x => x.EmployeeCode == employeeCode));
}
public int InsertJobHistoryForSaruwa(EmployeeJobHistoryDTO data)
{
EmployeeJobHistory dataToInsert = EmployeeJobHistoryRequestFormatter.ConvertRespondentInfoFromDTO(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();
EmployeeJobHistory result = _unitOfWork.EmployeeJobHistoryRepository.Create(dataToInsert);
return(result.HistoryId);
}
public FiscalDTO InsertFiscal(FiscalDTO data)
{
FiscalDTO res = new FiscalDTO();
SqlConnection conn = DbConnectHelper.GetConnection();
conn.Open();
SqlCommand cmd = new SqlCommand("sp_FiscalAdd", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
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();
foreach (DataRow row in dt.Rows)
{
return(new FiscalDTO
{
FyName = row["FyName"].ToString(),
FyCurrent = Convert.ToBoolean(row["FyCurrent"]),
FyEndDateNp = row["FyEndDateNp"].ToString(),
FyStartDateNp = row["FyStartDateNp"].ToString(),
FyEndDate = Convert.ToDateTime(row["FyEndDate"]),
FyStartDate = Convert.ToDateTime(row["FyStartDate"])
});
}
return(res);
}
public void CreateHoliayUpdateAttendnace(HolidayDTOs adt)
{
var conn = DbConnectHelper.GetConnection();
conn.Open();
SqlCommand cmd = new SqlCommand("sp_UpdateHolidayRecord", conn);
cmd.Parameters.AddWithValue("@HolidayId", adt.HolidayId);
cmd.Parameters.AddWithValue("@HOfficeId", adt.HolidayOfficeId);
cmd.Parameters.AddWithValue("@HReligionId", adt.HolidayReligionId);
cmd.Parameters.AddWithValue("@HEthnicityId", adt.HolidayEthnicityId);
cmd.Parameters.AddWithValue("@HGender", adt.HolidayGender);
cmd.Parameters.AddWithValue("@HolidayDate", adt.HolidayDate);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
da.Dispose();
cmd.Dispose();
conn.Close();
conn.Dispose();
}
public int DeleteLeaveAssignRules(int AssiginId)
{
try
{
var conn = DbConnectHelper.GetConnection();
conn.Open();
SqlCommand cmd = new SqlCommand("Delete From LeaveAssigned where AssignedId=" + AssiginId, conn);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@AssiginedId", AssiginId);
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 void Reject(int id)
{
LeaveApplication Record = _unitOfWork.LeaveApplicationRepository.GetById(id);
Record.LeaveStatus = 4;
_unitOfWork.LeaveApplicationRepository.Update(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 List <int> GetYearList()
{
var conn = DbConnectHelper.GetConnection();
conn.Open();
SqlCommand cmd = new SqlCommand("sp_NepaliFiscalYearList", conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
List <int> Year = new List <int>();
foreach (DataRow item in dt.Rows)
{
Year.Add(Convert.ToInt32(item["nepali_year"]));
}
da.Dispose();
cmd.Dispose();
conn.Close();
conn.Dispose();
return(Year);
}
public IEnumerable <AttendanceLogViewModel> GetAttendanceLog(int EmpCode, DateTime StrtDate)
{
// List<DailyAttendanceFilterViewModel> attlist = new List<DailyAttendanceFilterViewModel>();
var conn = DbConnectHelper.GetConnection();
conn.Open();
//SqlCommand cmd = new SqlCommand("select ek.logEmpCode,ek.logDate,ek.logTime, ek.logTypeId, ek.logMethodId, s.InOutMode from AttEmployeeLog ek join AttendaceLog s on ek.logEmpCode = s.EnrollNumber AND ek.LogId = s.DeviceDataId where ek.logEmpCode = '" + EmpCode + "'" + "AND" + " ek.logDate = '" + StrtDate + "'", conn);
SqlCommand cmd = new SqlCommand("select logEmpCode, logDate, logTime, logMethodId from AttEmployeeLog where logEmpCode = '" + EmpCode + "'" + "AND" + " logDate = '" + StrtDate + "'", conn);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@StartDate", StrtDate);
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 AttendanceLogViewModel
{
logEmpCode = Convert.ToInt32(row["logEmpCode"].ToString()),
//InOutMode = Convert.ToInt32(row["InOutMode"].ToString()),
logMethodId = Convert.ToInt32(row["logMethodId"].ToString()),
logDate = Convert.ToDateTime(row["logDate"].ToString()),
logTime = Convert.ToDateTime(row["logTime"].ToString()),
//logTypeId = Convert.ToInt32(row["logTypeId"].ToString())
});
}
}
public string GetOfficeName(int officeId)
{
if (officeId == 0)
{
return("Head Office");
}
OfficeDTOs office = new OfficeDTOs();
var conn = DbConnectHelper.GetConnection();
conn.Open();
SqlCommand cmd = new SqlCommand("SELECt OfficeName FROM Offices WHERE OfficeId = '" + officeId + "'", conn);
cmd.CommandType = CommandType.Text;
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();
return(dt.Rows[0]["officeName"].ToString());
}
public DataTable AttendanceMonthlySummary(DateTime sdate, DateTime enddate, int office)
{
try
{
SqlConnection conn = DbConnectHelper.GetConnection();
conn.Open();
SqlCommand cmd = new SqlCommand("sp_AttendanceTotalDaysReportsSummary", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@startdate", sdate);
cmd.Parameters.AddWithValue("@endate", enddate);
cmd.Parameters.AddWithValue("@officeId", office);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
SqlCommand cmd1 = new SqlCommand("sp_AttendanceLeaveReports", conn);
cmd1.CommandType = CommandType.StoredProcedure;
cmd1.Parameters.AddWithValue("@StartDate", sdate);
cmd1.Parameters.AddWithValue("@EndDate", enddate);
cmd1.Parameters.AddWithValue("@officeId", office);
SqlDataAdapter da1 = new SqlDataAdapter(cmd1);
DataTable dt1 = new DataTable();
da1.Fill(dt1);
IEnumerable <LeaveTypeDTO> ltd = _leave.GetLeaveTypes();
foreach (var row in ltd)
{
dt.Columns.Add(row.LeaveTypeName);
}
int x = dt1.Rows.Count;
string[] array = new string[x];
int i = 0;
foreach (var row in ltd)
{
foreach (DataRow item in dt1.Rows)
{
array[i] = item[row.LeaveTypeName].ToString();
i++;
}
i = 0;
foreach (DataRow item in dt.Rows)
{
if (string.IsNullOrEmpty(array[i]))
{
array[i] = 0.ToString();
}
item[row.LeaveTypeName] = array[i];
i++;
}
i = 0;
}
da.Dispose();
cmd.Dispose();
conn.Close();
conn.Dispose();
return(dt);
}
catch (Exception Mg)
{
return(null);
}
}