public ActionResult AllowanceImport()
{
PayrollAllowanceDetailDTO Record = new PayrollAllowanceDetailDTO();
Record.AllowanceMasterList = _PayrollAllowanceMasterService.GetAllowanceMasterList();
DropDownListViewmodelcs ddlvm = DropDownlist();
Record.OfficeList = ddlvm.OfficeList;
return(View(Record));
}
public static PayrollAllowanceDetail ConvertRespondentInfoFromDTO(PayrollAllowanceDetailDTO PayrollAllowanceMasterDTO)
{
Mapper.CreateMap <PayrollAllowanceDetailDTO, PayrollAllowanceDetail>().ConvertUsing(
m =>
{
return(new PayrollAllowanceDetail
{
Id = m.Id,
EmployeeCode = m.EmployeeCode,
AllowanceMasterId = m.AllowanceMasterId,
PercentageAmount = m.PercentageAmount,
Value = m.Value
});
});
return(Mapper.Map <PayrollAllowanceDetailDTO, PayrollAllowanceDetail>(PayrollAllowanceMasterDTO));
}
public ActionResult AllowanceImport(PayrollAllowanceDetailDTO 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/AllowanceImport/") + result;
if (!Directory.Exists(Server.MapPath("~/Content/AllowanceImport/")))
{
Directory.CreateDirectory(Server.MapPath("~/Content/AllowanceImport/"));
}
if (System.IO.File.Exists(fileLocation))
{
System.IO.File.Delete(fileLocation);
}
file.SaveAs(Path.Combine(Server.MapPath("~/Content/AllowanceImport/"), 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 <PayrollAllowanceDetailDTO> modelrecord = new List <PayrollAllowanceDetailDTO>();
foreach (DataRow d in dts.Rows)
{
PayrollAllowanceDetailDTO single = new PayrollAllowanceDetailDTO()
{
AllowanceMasterId = Record.AllowanceMasterId,
EmployeeCode = Convert.ToInt32(d["EmployeeCode"].ToString()),
PercentageAmount = d["PercentageAmount"].ToString(),
Value = Convert.ToDecimal(d["Value"].ToString()),
};
single.EmployeeName = _EmployeeService.GetEmployeeByID(single.EmployeeCode).Name;
modelrecord.Add(single);
PayrollAllowanceDetail domainrecord = PayrollAllowanceDetailRequestFormatter.ConvertRespondentInfoFromDTO(single);
PayrollAllowanceDetail pastrecord = _unitOfWork.PayrollAllowanceDetailRepository.Get(x => x.AllowanceMasterId == single.AllowanceMasterId && x.EmployeeCode == single.EmployeeCode).FirstOrDefault();
if (pastrecord != null)
{
_unitOfWork.PayrollAllowanceDetailRepository.Delete(pastrecord);
}
_unitOfWork.PayrollAllowanceDetailRepository.Create(domainrecord);
}
ViewBag.AllowanceImportedList = modelrecord;
TempData["Success"] = "Allowance Data imported successfully";
}
catch (Exception ex)
{
TempData["Danger"] = ex.Message;
}
return(View(Record));
}
public void InsertIntoPayrollAllowanceDetail(PayrollAllowanceDetailDTO Record)
{
PayrollAllowanceDetail Data = PayrollAllowanceDetailRequestFormatter.ConvertRespondentInfoFromDTO(Record);
_unitOfWork.PayrollAllowanceDetailRepository.Create(Data);
}
//to be removed once nondefault work is done
public PayrollAllowanceMasterDTO GetPayrollDetailByMasterId(int Id, int officeId)
{
PayrollAllowanceMaster Record = _unitOfWork.PayrollAllowanceMasterRepository.GetById(Id);
IEnumerable <PayrollAllowanceDetail> AllowanceDetail = Record.PayrollAllowanceDetails;
IEnumerable <PayrollAllowanceDetailDTO> Detail = PayrollAllowanceDetailResponseFormatter.GetAllTPayrollDetailDTO(AllowanceDetail);
PayrollAllowanceMasterDTO ReturnRecord = PayrollAllowanceMasterRequestFormatter.ConvertRespondentInfoToDTO(Record);
ReturnRecord.PayrollAllowanceDetails = Detail;
IEnumerable <EmployeeCodeName> EmployeeList = GetAllRelatedEmployees(officeId);
List <SelectListItem> SelectedEmployeeList = new List <SelectListItem>();
foreach (var row in Detail)
{
SelectedEmployeeList.Add(new SelectListItem
{
Text = GetEmployeeName(row.EmployeeCode),
Value = row.EmployeeCode.ToString()
});
}
List <SelectListItem> DropDownEmployee = new List <SelectListItem>();
foreach (var row in EmployeeList)
{
bool selected = false;
foreach (var Test in SelectedEmployeeList)
{
if (row.Value == Test.Value && row.Text == Test.Text)
{
selected = true;
break;
}
}
DropDownEmployee.Add(new SelectListItem
{
Text = row.Text,
Value = row.Value,
Selected = selected
});
}
List <AssignedEmployees> AssignedEmployeesList = new List <AssignedEmployees>();
foreach (var str in EmployeeList)
{
int Value = Convert.ToInt32(str.Value);
if (Detail.Any(x => x.EmployeeCode == Value))
{
PayrollAllowanceDetailDTO PayrollAllowanceDetails = Detail.Where(x => x.EmployeeCode == Value).FirstOrDefault();
AssignedEmployees AssignedEmployeeRow = new AssignedEmployees()
{
EmployeeCode = PayrollAllowanceDetails.EmployeeCode,
EmployeeName = GetEmployeeName(PayrollAllowanceDetails.EmployeeCode),
PercentageAmount = PayrollAllowanceDetails.PercentageAmount,
Selected = true,
Value = PayrollAllowanceDetails.Value != null ? PayrollAllowanceDetails.Value : Convert.ToDecimal("0")
};
AssignedEmployeesList.Add(AssignedEmployeeRow);
}
else
{
AssignedEmployees AssignedEmployeeRow = new AssignedEmployees()
{
EmployeeCode = Convert.ToInt32(str.Value),
EmployeeName = GetEmployeeName(Convert.ToInt32(str.Value)),
PercentageAmount = "",
Selected = false,
Value = Convert.ToDecimal("0")
};
AssignedEmployeesList.Add(AssignedEmployeeRow);
}
}
ReturnRecord.AssignedEmployees = AssignedEmployeesList;
ReturnRecord.SelectedEmployeeList = SelectedEmployeeList;
ReturnRecord.EmployeeList = DropDownEmployee;
return(ReturnRecord);
}
