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