public ActionResult Create([Bind(Include = "Id,Process_Time, Quantity, Frequency")] WLATrx wLATrx, FormCollection formData)
{
double workingHourYear = 1873.76;
ViewBag.WLAHeaderId = formData["WLAHeaderId"].ToString();
int WLAHeaderId = Convert.ToInt32(formData["WLAHeaderId"].ToString());
int PeriodeId = Convert.ToInt32(formData["Periode"].ToString());
int PelaksanaId = Convert.ToInt32(formData["Pelaksana"].ToString());
int Activity = Convert.ToInt32(formData["Activity"].ToString());
int ActivityGroup = Convert.ToInt32(formData["ActivityGroup"].ToString());
if (ModelState.IsValid)
{
int Periode_Value = 0;
WLAModel wm = new WLAModel();
Periode_Value = wm.getPeriode(PeriodeId);
wLATrx.Sub_Total_Aktivitas = (double)(Periode_Value * wLATrx.Process_Time * wLATrx.Quantity * wLATrx.Frequency) / 60;
wLATrx.WLAHeader = db.WLAHeaders.Where(c => c.Id == WLAHeaderId).First();
wLATrx.Periode = db.Periode.Where(c => c.Id == PeriodeId).First();
wLATrx.Pelaksana = db.Pelaksana.Where(c => c.Id == PelaksanaId).First();
wLATrx.Activity = db.Activities.Where(c => c.Id == Activity).First();
wLATrx.ActivityGroup = db.ActivityGroups.Where(c => c.Id == ActivityGroup).First();
db.WLATrx.Add(wLATrx);
db.SaveChanges();
var data = db.WLATrx.Where(d => d.WLAHeader.Id == WLAHeaderId);
var Sub_Total_Aktivitas = data.Sum(d => d.Sub_Total_Aktivitas);
var dataHeader = db.WLAHeaders.Find(WLAHeaderId);
dataHeader.Effective_Working_Hours = Sub_Total_Aktivitas;
dataHeader.FTE = Sub_Total_Aktivitas / workingHourYear;
db.SaveChanges();
return(RedirectToAction("Index"));
}
ViewBag.Periode = new SelectList(db.Periode, "Id", "Name");
ViewBag.Pelaksana = new SelectList(db.Pelaksana, "Id", "Name");
var activities = from s in db.Activities select s;
activities = activities.OrderBy(s => s.Name);
ViewData["Activity"] = activities.Select(x => new SelectListItem {
Text = x.Name, Value = x.Id.ToString()
}).ToList();
var activityGroup = from s in db.ActivityGroups select s;
activityGroup = activityGroup.OrderBy(s => s.Name);
ViewData["ActivityGroup"] = activityGroup.Select(x => new SelectListItem {
Text = x.Name, Value = x.Id.ToString()
}).ToList();
return(View(wLATrx));
}
public ActionResult UnitUpdate(FormCollection formData)
{
bool IsSuccess = false;
string strMessage = "";
int jumlah = Convert.ToInt32(formData["jumlah"].ToString());
int WLAHeaderId = Convert.ToInt32(formData["WLAHeaderId"].ToString());
int ActivityGroupId = Convert.ToInt32(formData["ActivityGroupId"].ToString());
var wla = db.WLATrx.Where(s => s.WLAHeader.Id == WLAHeaderId && s.Activity.Type == 1 && s.ActivityGroup.Id == ActivityGroupId).FirstOrDefault();
int Periode_Value = 0;
WLAModel wm = new WLAModel();
Periode_Value = wm.getPeriode(wla.Periode.Id);
wla.Quantity = jumlah;
wla.Sub_Total_Aktivitas = (double)(Periode_Value * wla.Process_Time * wla.Quantity * wla.Frequency) / 60;
db.SaveChanges();
var standardTime = db.Standard_Time.Where(d => d.Tahun == wla.WLAHeader.Tahun).FirstOrDefault();
if (standardTime != null)
{
var Sub_Total_Aktivitas = db.WLATrx.Where(d => d.WLAHeader.Id == WLAHeaderId).Sum(d => d.Sub_Total_Aktivitas);
var dataHeader = db.WLAHeaders.Find(WLAHeaderId);
dataHeader.Effective_Working_Hours = Sub_Total_Aktivitas;
dataHeader.FTE = Sub_Total_Aktivitas / standardTime.Effective_Working_Hours;
db.SaveChanges();
}
return(Json(new
{
status = IsSuccess,
message = strMessage
}, JsonRequestBehavior.AllowGet));
}
public ActionResult UploadData(string Filename)
{
Boolean IsSuccess = false;
string strMessage = string.Empty;
string UploadLoanPath = Server.MapPath("~/Content/UploadData/" + Filename);
var i = 6;
try
{
FileStream file = new FileStream(UploadLoanPath, FileMode.Open, FileAccess.Read);
ExcelPackage ExcelPackage = new ExcelPackage(file);
ExcelWorksheet ExcelWorksheet = ExcelPackage.Workbook.Worksheets["Sheet1"];
try
{
var hasValue = true;
int tahun = Convert.ToInt32(ExcelWorksheet.Cells[1, 3].Text.Trim());
string fungsi = ExcelWorksheet.Cells[2, 3].Text.Trim();
string jabatan = ExcelWorksheet.Cells[3, 3].Text.Trim();
var wlaheader = db.WLAHeaders.Where(s => s.Tahun == tahun && s.Fungsi.Name.Equals(fungsi) && s.Jabatan.Name.Equals(jabatan)).First();
if (wlaheader == null)
{
WLAHeader x = new WLAHeader();
x.Tahun = tahun;
x.Fungsi = db.Fungsi.Where(c => c.Name.Equals(fungsi)).First();
x.Jabatan = db.Jabatan.Where(c => c.Name.Equals(jabatan)).First();
db.WLAHeaders.Add(x);
db.SaveChanges();
wlaheader = db.WLAHeaders.Where(s => s.Tahun == tahun && s.Fungsi.Name.Equals(fungsi) && s.Jabatan.Name.Equals(jabatan)).First();
}
//Looping data dr excel
string current_grup = "";
while (hasValue)
{
WLATrx wLATrx = new WLATrx();
if (string.IsNullOrEmpty(ExcelWorksheet.Cells[i, 3].Text))
{
hasValue = false;
continue;
}
string activity_s = ExcelWorksheet.Cells[i, 3].Text.Trim();
var activity = db.Activities.Where(s => s.Name.Equals(activity_s)).FirstOrDefault();
if (activity == null)
{
Activity x = new Activity();
x.Name = activity_s;
db.Activities.Add(x);
db.SaveChanges();
activity = db.Activities.Where(s => s.Name.Equals(activity_s)).First();
}
wLATrx.Activity = activity;
string activityGroup_s = ExcelWorksheet.Cells[i, 2].Text.Trim();
if (activityGroup_s.Equals(""))
{
activityGroup_s = current_grup;
}
else
{
current_grup = activityGroup_s;
}
var activityGroup = db.ActivityGroups.Where(s => s.Name.Equals(activityGroup_s)).FirstOrDefault();
if (activityGroup == null)
{
ActivityGroup x = new ActivityGroup();
x.Name = activityGroup_s;
db.ActivityGroups.Add(x);
db.SaveChanges();
activityGroup = db.ActivityGroups.Where(s => s.Name.Equals(activityGroup_s)).First();
}
wLATrx.ActivityGroup = activityGroup;
string pelaksana = ExcelWorksheet.Cells[i, 4].Text.Trim();
wLATrx.Pelaksana = db.Pelaksana.Where(c => c.Name.Equals(pelaksana)).FirstOrDefault();
if (wLATrx.Pelaksana == null)
{
strMessage = strMessage + "row " + i + " gagal, ";
i++;
continue;
}
string periode = ExcelWorksheet.Cells[i, 5].Text.Trim();
wLATrx.Periode = db.Periode.Where(c => c.Name.Equals(periode)).FirstOrDefault();
if (wLATrx == null)
{
strMessage = strMessage + "row " + i + " gagal, ";
i++;
continue;
}
int Periode_Value = 0;
WLAModel wm = new WLAModel();
Periode_Value = wm.getPeriode(wLATrx.Periode.Id);
wLATrx.WLAHeader = wlaheader;
wLATrx.Process_Time = Convert.ToInt32(ExcelWorksheet.Cells[i, 6].Text.Trim());
wLATrx.Quantity = Convert.ToInt32(ExcelWorksheet.Cells[i, 7].Text.Trim());
wLATrx.Frequency = Convert.ToInt32(ExcelWorksheet.Cells[i, 8].Text.Trim());
wLATrx.Sub_Total_Aktivitas = (double)(Periode_Value * wLATrx.Process_Time * wLATrx.Quantity * wLATrx.Frequency) / 60;
var wlaExist = db.WLATrx.Where(
s => s.WLAHeader.Id == wlaheader.Id &&
s.Activity.Name.Equals(activity_s) &&
s.ActivityGroup.Name.Equals(activityGroup_s)
).FirstOrDefault();
if (wlaExist == null)
{
db.WLATrx.Add(wLATrx);
}
else
{
wlaExist.Frequency = wLATrx.Frequency;
wlaExist.Process_Time = wLATrx.Process_Time;
wlaExist.Quantity = wLATrx.Quantity;
wlaExist.Sub_Total_Aktivitas = wLATrx.Sub_Total_Aktivitas;
}
db.SaveChanges();
var standardTime = db.Standard_Time.Where(d => d.Tahun == tahun).FirstOrDefault();
if (standardTime != null)
{
var Sub_Total_Aktivitas = db.WLATrx.Where(d => d.WLAHeader.Id == wlaheader.Id).Sum(d => d.Sub_Total_Aktivitas);
var dataHeader = db.WLAHeaders.Find(wlaheader.Id);
dataHeader.Effective_Working_Hours = Sub_Total_Aktivitas;
dataHeader.FTE = Sub_Total_Aktivitas / standardTime.Effective_Working_Hours;
db.SaveChanges();
}
i++;
}
}
catch (Exception e)
{
strMessage = "" + i;
file.Close();
file.Dispose();
throw new Exception(e.Message);
}
IsSuccess = true;
file.Close();
file.Dispose();
}
catch (Exception e)
{
strMessage = "" + i;
throw new Exception(e.Message);
}
return(Json(new
{
status = IsSuccess,
message = strMessage
}, JsonRequestBehavior.AllowGet));
}