public void Save(EmployeeOccupation employeeOccupation)
{
try
{
using (var em = EntityManagerFactory.CreateInstance(ds))
{
Save(em, null, employeeOccupation);
}
}
catch (Exception ex)
{
throw ex;
}
}
public EmployeeOccupation GetCurrentOccupation(Guid employeeId)
{
EmployeeOccupation employeeOccupation = null;
using (var em = EntityManagerFactory.CreateInstance(ds))
{
string sql = "SELECT TOP 1 eo.*,o.OccupationName FROM EmployeeOccupation eo "
+ "INNER JOIN Occupation o ON eo.OccupationId = o.ID "
+ "WHERE eo.EmployeeId='{" + employeeId + "}' "
+ "ORDER BY eo.EffectiveDate DESC";
employeeOccupation = em.ExecuteObject <EmployeeOccupation>(sql, new EmployeeOccupationMapper());
}
return(employeeOccupation);
}
public EmployeeOccupation GetPreviousOccupation(Guid employeeId, int month, int year)
{
EmployeeOccupation employeeOccupation = new EmployeeOccupation();
using (var em = EntityManagerFactory.CreateInstance(ds))
{
string sql = "SELECT TOP 1 eo.*,o.OccupationName FROM EmployeeOccupation eo "
+ "INNER JOIN Occupation o ON eo.OccupationId = o.ID "
+ "WHERE eo.EmployeeId='{" + employeeId + "}' "
+ "AND month(eo.EffectiveDate) <=" + month + " "
+ "AND year(eo.EffectiveDate) <=" + year;
employeeOccupation = em.ExecuteObject <EmployeeOccupation>(sql, new EmployeeOccupationMapper());
}
return(employeeOccupation);
}
public void Save(IEntityManager em, Transaction tx, EmployeeOccupation employeeOccupation)
{
string[] columns = { "ID", "EmployeeId", "EffectiveDate", "OccupationId", "IsTaskForce" };
object[] values = { Guid.NewGuid(), employeeOccupation.EmployeeId, employeeOccupation.EffectiveDate.ToShortDateString(),
employeeOccupation.OccupationId, employeeOccupation.IsTaskForce == true?1:0 };
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);
}
