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