public IEnumerable <PayrollIntrestGainDTO> GetInterestGainList(int?EmpId, int?OfficeId) { IEnumerable <PayrollIntrestGain> modelDatas = _unitOfWork.InterestGainRepository.All().ToList(); IEnumerable <PayrollIntrestGain> officeFiltered = modelDatas.Where(x => x.Employee.EmpOfficeId == OfficeId); IEnumerable <PayrollIntrestGain> employeeFiltered = modelDatas.Where(x => x.EmpCode == EmpId); IEnumerable <PayrollIntrestGain> returnRecord = modelDatas.Where(x => x.EmpCode == EmpId && x.Employee.EmpOfficeId == OfficeId); if (OfficeId != null && EmpId != null) { return(PayrollInterestGainResponseFormatter.PayrollInterestDbListToDTOList(returnRecord)); } else if (EmpId == null && OfficeId != null) { return(PayrollInterestGainResponseFormatter.PayrollInterestDbListToDTOList(officeFiltered)); } else if (EmpId != null && OfficeId == null) { return(PayrollInterestGainResponseFormatter.PayrollInterestDbListToDTOList(employeeFiltered)); } else { return(PayrollInterestGainResponseFormatter.PayrollInterestDbListToDTOList(modelDatas)); } }
public PayrollIntrestGainDTO GetInterestGainByEmpCode(int EmpCode) { PayrollIntrestGain domainRecord = _unitOfWork.InterestGainRepository.Get(x => x.EmpCode == EmpCode).FirstOrDefault(); return(PayrollInterestGainResponseFormatter.PayrollInterestDbToDTO(domainRecord)); }
public ActionResult Import(PayrollIntrestGainDTO Record, HttpPostedFileBase file) { //Record.AllowanceMasterList = _PayrollAllowanceMasterService.GetAllowanceMasterList(); DropDownListViewmodelcs ddlvm = DropDownlist(); Record.OfficeList = ddlvm.OfficeList; try { string result; result = file.FileName; DataSet ds = new DataSet(); DataTable dts = new DataTable(); string fileExtension = System.IO.Path.GetExtension(result); if (fileExtension == ".xls" || fileExtension == ".xlsx") { string fileLocation = Server.MapPath("~/Content/InterestGainImport/") + result; if (!Directory.Exists(Server.MapPath("~/Content/InterestGainImport/"))) { Directory.CreateDirectory(Server.MapPath("~/Content/InterestGainImport/")); } if (System.IO.File.Exists(fileLocation)) { System.IO.File.Delete(fileLocation); } file.SaveAs(Path.Combine(Server.MapPath("~/Content/InterestGainImport/"), result)); string excelConnectionString = string.Empty; excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\""; //connection String for xls file format. if (fileExtension == ".xls") { excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\""; } //connection String for xlsx file format. else if (fileExtension == ".xlsx") { excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\""; } //Create Connection to Excel work book and add oledb namespace OleDbConnection excelConnection = new OleDbConnection(excelConnectionString); excelConnection.Open(); DataTable dt = new DataTable(); dt = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); if (dt == null) { TempData["Danger"] = "Corrupt file. Please check the file and try again"; return(RedirectToAction("Index")); } String[] excelSheets = new String[dt.Rows.Count]; int t = 0; //excel data saves in temp file here. foreach (DataRow row in dt.Rows) { excelSheets[t] = row["TABLE_NAME"].ToString(); t++; } OleDbConnection excelConnection1 = new OleDbConnection(excelConnectionString); string query = string.Format("Select * from [{0}]", excelSheets[0]); using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, excelConnection1)) { dataAdapter.Fill(ds); dataAdapter.Fill(dts); } excelConnection.Close(); } List <PayrollIntrestGainDTO> modelrecord = new List <PayrollIntrestGainDTO>(); foreach (DataRow d in dts.Rows) { PayrollIntrestGainDTO single = new PayrollIntrestGainDTO() { CustomerId = d["CustomerId"].ToString(), //AllowanceMasterId = Record.AllowanceMasterId, //EmpCode = Convert.ToInt32(d["EmployeeCode"].ToString()), InterestGain = Convert.ToDecimal(d["Value"].ToString()), //PercentageAmount = d["PercentageAmount"].ToString(), //Value = Convert.ToDecimal(d["Value"].ToString()), }; single.EmpCode = _unitOfWork.EmployeeRepository.Get(x => x.CustomerId == single.CustomerId).FirstOrDefault().EmpCode; single.EmployeeName = _empService.GetEmployeeByID((int)single.EmpCode).Name; modelrecord.Add(single); PayrollIntrestGain domainrecord = PayrollInterestGainResponseFormatter.ConvertRespondentInfoFromDTO(single); PayrollIntrestGain pastrecord = _unitOfWork.InterestGainRepository.Get(x => x.EmpCode == single.EmpCode).FirstOrDefault(); if (pastrecord != null) { _unitOfWork.InterestGainRepository.Delete(pastrecord); } _unitOfWork.InterestGainRepository.Create(domainrecord); } ViewBag.AllowanceImportedList = modelrecord; TempData["Success"] = "Allowance Data imported successfully"; } catch (IOException ex) { TempData["Danger"] = ex.Message; } catch (Exception ex) { TempData["Danger"] = ex.Message; } return(View(Record)); }