public void saveAbsensi(string dataDetail) { int dataInput = 0; string[] RowData = dataDetail.Split('|'); for (int i = 0; i < (RowData.Count()-1); i++) { string[] value = RowData[i].Split(';'); DateTime dateAbsensi = DateTime.Parse(value[0].ToString()); int employeeId = int.Parse(value[1].ToString()); int absensiType = int.Parse(value[2].ToString()); string remark = value[3].ToString(); db.absensiRekaps.RemoveRange(db.absensiRekaps.Where(x => x.absensiDate == dateAbsensi && x.employeeID == employeeId)); absensiRekap abs = new absensiRekap(); abs.absensiDate = dateAbsensi; abs.employeeID = employeeId; abs.typeAbsensiID = absensiType; abs.remarks = remark; db.absensiRekaps.Add(abs); dataInput++; } if(dataInput > 0) db.SaveChanges(); }
public void loadAbsensi(DateTime date) { var dtp = date; ViewBag.ddlTypeAbsensi = ccm.ddlTypeAbsensi(string.Empty); var empAbsensi = db.employees .Join(db.employeePositions .Join(db.employeeResigns, c => c.employeeID, d => d.employeeID, (c, d) => new { c, d }) .Where(e => e.c.positionDate > e.d.resignDate) , a => a.employeeID, b => b.c.employeeID, (a, b) => new { a, b }) .Join(db.departments, f => f.b.c.deptID, g => g.deptID, (f, g) => new { f, g }) .Select(h => new { h.f.a.employeeID, h.f.a.employeeNIK, h.f.a.employeeName, h.g.deptName}) .ToList(); string sql = string.Empty; sql = "SELECT "; sql += "a.employeeID, a.employeeNIK, a.employeeName, "; sql += "d.deptID, d.deptName "; sql += "FROM [dbo].[Employees] a "; sql += "INNER JOIN "; sql += "(SELECT a.employeeID, b.positionId "; sql += "FROM [dbo].[EmployeeResigns] a "; sql += "INNER JOIN [dbo].[EmployeePositions] b ON (b.employeeID = a.employeeID AND b.positionDate > a.resignDate) "; sql += "UNION "; sql += "SELECT employeeID, "; sql += "(SELECT positionId FROM [dbo].[EmployeePositions] WHERE employeeID = a.employeeID)positionId "; sql += "FROM [dbo].[Employees] a "; sql += "WHERE a.employeeID NOT IN (SELECT employeeID "; sql += " FROM [dbo].[EmployeeResigns]) "; sql += ") b ON b.employeeID = a.employeeID "; sql += "LEFT JOIN [dbo].[EmployeePositions] c ON c.employeeID = b.employeeID AND c.positionId = b.positionId "; sql += "LEFT JOIN [dbo].[Departments] d ON d.deptID = c.deptID "; DataTable dtEmployee = cd.executeReader(sql); var model = new absensiRekap(); foreach (DataRow dr in dtEmployee.Rows) { int absensiTipe = 0; string ket = string.Empty; foreach (var att in db.absensiRekaps.Where(x => x.absensiDate == dtp.Date).ToList() as IEnumerable<Models.absensiRekap>) { if (att.employeeID == int.Parse(dr["employeeID"].ToString())) { absensiTipe = att.typeAbsensiID; ket = att.remarks; break; } } var editor = new absensiRekap.RekapAbsensiDetail() { employeeID = int.Parse(dr["employeeID"].ToString()), employeeNIK = dr["employeeNIK"].ToString(), employeeName = dr["employeeName"].ToString(), deptName = dr["deptName"].ToString(), typeAbsensiId = absensiTipe, remarks = ket }; model.detailRekapAbsensi.Add(editor); } ViewBag.detailRekapAbsensi = model.detailRekapAbsensi.ToList(); }