public void Save(EmployeeInsurance employeeInsurance)
{
using (var em = EntityManagerFactory.CreateInstance(ds))
{
Save(em, null, employeeInsurance);
}
}
public EmpInsuranceItem GetById(int id)
{
Mapper.CreateMap <EmployeeInsurance, EmpInsuranceItem>();
EmployeeInsurance objInsu = DbContext.EmployeeInsurances.SingleOrDefault(m => m.IID == id);
EmpInsuranceItem objInsuItem = Mapper.Map <EmpInsuranceItem>(objInsu);
return(objInsuItem);
}
public int Update(EmpInsuranceItem model)
{
Mapper.CreateMap <EmpInsuranceItem, EmployeeInsurance>();
EmployeeInsurance objInsu = DbContext.EmployeeInsurances.SingleOrDefault(m => m.IID == model.IID);
objInsu = Mapper.Map(model, objInsu);
return(DbContext.SaveChanges());
}
public int Insert(EmpInsuranceItem model)
{
try
{
Mapper.CreateMap <EmpInsuranceItem, EmployeeInsurance>();
EmployeeInsurance objEmp = Mapper.Map <EmployeeInsurance>(model);
DbContext.EmployeeInsurances.Add(objEmp);
return(DbContext.SaveChanges());
}
catch (Exception ex)
{
throw;
}
}
public async Task <IActionResult> Update([FromBody] EmployeeInsurance employeeInsurance)
{
int result = 0;
try
{
context.Update(employeeInsurance);
result = await context.SaveChangesAsync();
}
catch (Exception ex)
{
logger.LogError(ex.ToString());
}
return(Ok(result));
}
public EmployeeInsurance GetPreviousInsurance(Guid employeeId, int month, int year)
{
EmployeeInsurance employeeInsurance = new EmployeeInsurance();
using (var em = EntityManagerFactory.CreateInstance(ds))
{
Query q = new Query().Select("TOP 1 * ").From(tableName)
.Where("EmployeeId").Equal("{" + employeeId + "}")
.And("month(EffectiveDate)").LessEqualThan(month)
.And("year(EffectiveDate)").LessEqualThan(year)
.OrderBy("EffectiveDate DESC");
employeeInsurance = em.ExecuteObject <EmployeeInsurance>(q.ToSql(), new EmployeeInsuranceMapper());
}
return(employeeInsurance);
}
public void Save(IEntityManager em, Transaction tx, EmployeeInsurance employeeInsurance)
{
string[] columns = { "ID", "EmployeeId", "InsuranceId", "InsuranceProgramId", "EffectiveDate", "EndDate", "InsuranceNumber" };
object[] values = { Guid.NewGuid(), employeeInsurance.EmployeeId, employeeInsurance.InsuranceId, employeeInsurance.InsuranceProgramId,
employeeInsurance.EffectiveDate.ToShortDateString(), employeeInsurance.EndDate.ToShortDateString(),
employeeInsurance.InsuranceNumber };
var q = new Query().Select(columns).From(tableName).Insert(values);
if (tx == null)
{
em.ExecuteNonQuery(q.ToSql());
}
else
{
em.ExecuteNonQuery(q.ToSql(), tx);
}
}
private void ImportEmployeeDetail(string fileName)
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
Excel.Range range;
xlApp = new Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Open(fileName, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(2);
range = xlWorkSheet.UsedRange;
for (var row = 2; row <= range.Rows.Count; row++)
{
string nik = (string)(range.Cells[row, 1] as Excel.Range).Value2;
string branch = (string)(range.Cells[row, 2] as Excel.Range).Value2;
string department = (string)(range.Cells[row, 3] as Excel.Range).Value2;
string departmentDate = (string)(range.Cells[row, 4] as Excel.Range).Value2;
string grade = (string)(range.Cells[row, 5] as Excel.Range).Value2;
string gradeDate = (string)(range.Cells[row, 6] as Excel.Range).Value2;
string occupation = (string)(range.Cells[row, 7] as Excel.Range).Value2;
string occupationDate = (string)(range.Cells[row, 8] as Excel.Range).Value2;
string isTaskForce = (string)(range.Cells[row, 9] as Excel.Range).Value2;
string principal = (string)(range.Cells[row, 10] as Excel.Range).Value2;
string principalDate = (string)(range.Cells[row, 11] as Excel.Range).Value2;
string status = (string)(range.Cells[row, 12] as Excel.Range).Value2;
string paymentType = (string)(range.Cells[row, 13] as Excel.Range).Value2;
string statusDate = (string)(range.Cells[row, 14] as Excel.Range).Value2;
string jamsostekNumber = (string)(range.Cells[row, 15] as Excel.Range).Value2;
string jamsostekDate = (string)(range.Cells[row, 16] as Excel.Range).Value2;
string salaryDate = (string)(range.Cells[row, 17] as Excel.Range).Value2;
double mainSalary = (double)(range.Cells[row, 18] as Excel.Range).Value2;
double occupationAllowance = (double)(range.Cells[row, 19] as Excel.Range).Value2;
double fixedAllowance = (double)(range.Cells[row, 20] as Excel.Range).Value2;
double healthAllowance = (double)(range.Cells[row, 21] as Excel.Range).Value2;
double communicationAllowance = (double)(range.Cells[row, 22] as Excel.Range).Value2;
double supervisionAllowance = (double)(range.Cells[row, 23] as Excel.Range).Value2;
double otherAllowance = (double)(range.Cells[row, 24] as Excel.Range).Value2;
double fuelAllowance = (double)(range.Cells[row, 25] as Excel.Range).Value2;
double vehicleAllowance = (double)(range.Cells[row, 26] as Excel.Range).Value2;
double lunchAllowance = (double)(range.Cells[row, 27] as Excel.Range).Value2;
double transportAllowance = (double)(range.Cells[row, 28] as Excel.Range).Value2;
if (nik == null)
{
break;
}
var employee = employeeRepository.GetByCode(nik);
if (employee != null)
{
//Department
var employeeDepartment = new EmployeeDepartment();
employeeDepartment.EmployeeId = employee.ID;
employeeDepartment.EffectiveDate = DateTime.ParseExact(departmentDate,
"dd/MM/yyyy", CultureInfo.InvariantCulture);
employeeDepartment.BranchId = branchRepository.GetByName(branch).ID;
employeeDepartment.DepartmentId = departmentRepository.GetByName(department).ID;
employeeDepartmentRepository.Save(employeeDepartment);
//Grade
var employeeGrade = new EmployeeGrade();
employeeGrade.EmployeeId = employee.ID;
employeeGrade.EffectiveDate = DateTime.ParseExact(gradeDate,
"dd/MM/yyyy", CultureInfo.InvariantCulture);
employeeGrade.GradeId = gradeRepository.GetByName(grade).ID;
employeeGradeRepository.Save(employeeGrade);
//Occupation
var employeeOccupation = new EmployeeOccupation();
employeeOccupation.EmployeeId = employee.ID;
employeeOccupation.EffectiveDate = DateTime.ParseExact(occupationDate,
"dd/MM/yyyy", CultureInfo.InvariantCulture);
employeeOccupation.OccupationId = occupationRepository.GetByName(occupation).ID;
employeeOccupation.IsTaskForce = isTaskForce.ToUpper() == "Y" ? true : false;
employeeOccupationRepository.Save(employeeOccupation);
//Status
var employeeStatus = new EmployeeStatus();
employeeStatus.EmployeeId = employee.ID;
employeeStatus.EffectiveDate = DateTime.ParseExact(statusDate,
"dd/MM/yyyy", CultureInfo.InvariantCulture);
employeeStatus.IsEnd = false;
employeeStatus.EndDate = DateTime.Now;
employeeStatus.Status = status;
employeeStatus.PaymentType = paymentType;
employeeStatusRepository.Save(employeeStatus);
//Insurance
var insurancePrograms = insuranceProgramRepository.GetByInsuranceId(Guid.Empty);
foreach (var program in insurancePrograms)
{
var employeeInsurance = new EmployeeInsurance();
employeeInsurance.EmployeeId = employee.ID;
employeeInsurance.InsuranceId = Guid.Empty;
employeeInsurance.InsuranceProgramId = program.ID;
employeeInsurance.EffectiveDate = DateTime.ParseExact(jamsostekDate,
"dd/MM/yyyy", CultureInfo.InvariantCulture);
employeeInsurance.EndDate = DateTime.Now;
employeeInsurance.InsuranceNumber = jamsostekNumber;
employeeInsuranceRepository.Save(employeeInsurance);
}
//Salary
var employeeSalary = new EmployeeSalary();
employeeSalary.EmployeeId = employee.ID;
employeeSalary.EffectiveDate = DateTime.ParseExact(salaryDate,
"dd/MM/yyyy", CultureInfo.InvariantCulture);
employeeSalary.MainSalary = Convert.ToDecimal(mainSalary);
employeeSalary.OccupationAllowancePerMonth = Convert.ToDecimal(occupationAllowance);
employeeSalary.FixedAllowancePerMonth = Convert.ToDecimal(fixedAllowance);
employeeSalary.HealthAllowancePerMonth = Convert.ToDecimal(healthAllowance);
employeeSalary.CommunicationAllowancePerMonth = Convert.ToDecimal(communicationAllowance);
employeeSalary.SupervisionAllowancePerMonth = Convert.ToDecimal(supervisionAllowance);
employeeSalary.OtherAllowance = Convert.ToDecimal(otherAllowance);
employeeSalary.FuelAllowancePerDays = Convert.ToDecimal(fuelAllowance);
employeeSalary.VehicleAllowancePerDays = Convert.ToDecimal(vehicleAllowance);
employeeSalary.LunchAllowancePerDays = Convert.ToDecimal(lunchAllowance);
employeeSalary.TransportationAllowancePerDays = Convert.ToDecimal(transportAllowance);
employeeSalary.JamsostekAmount = 0;
employeeSalary.PersonalDebt = 0;
employeeSalary.OtherFee = 0;
employeeSalaryRepository.Save(employeeSalary);
UpdateEmployeeCurrentInfo(employee.ID, branch, department, grade, occupation, status, paymentType);
}
}
xlWorkBook.Close(true, null, null);
xlApp.Quit();
ReleaseObject(xlWorkSheet);
ReleaseObject(xlWorkBook);
ReleaseObject(xlApp);
}
