public ActionResult Create(int?personnelId)
{
var overtimePreferences = HRBusinessService.RetrieveOvertimePreferences(UserOrganisationId);
var organisationId = UserOrganisationId;
if (personnelId == null)
{
personnelId = UserPersonnelId;
}
var personnel = HRBusinessService.RetrievePersonnel(organisationId, personnelId.Value);
var overtimeSummary = HRBusinessService.RetrieveOvertimeSummary(UserOrganisationId, personnelId.Value);
var viewModel = new OvertimeViewModel
{
Overtime = new Overtime
{
OrganisationId = organisationId,
PersonnelId = personnelId.Value,
Date = DateTime.Today,
CreatedBy = ApplicationUser.UserName,
Personnel = personnel
},
OvertimePreferences = new SelectList(overtimePreferences, "OvertimePreferenceId", "Name"),
OvertimeSummary = overtimeSummary ?? new OvertimeSummary()
};
return(View(viewModel));
}
public ActionResult Create(OvertimeViewModel overtimeViewModel)
{
if (ModelState.IsValid)
{
var result = HRBusinessService.CreateOvertime(UserOrganisationId, overtimeViewModel.Overtime);
if (result.Succeeded)
{
return(RedirectToAction("profile", "personnel", new { id = overtimeViewModel.PersonnelId }));
}
foreach (var error in result.Errors)
{
ModelState.AddModelError("", error);
}
}
var overtimePreferences = HRBusinessService.RetrieveOvertimePreferences(UserOrganisationId);
var overtimeSummary = HRBusinessService.RetrieveOvertimeSummary(UserOrganisationId, overtimeViewModel.PersonnelId);
var personnel = HRBusinessService.RetrievePersonnel(UserOrganisationId, overtimeViewModel.PersonnelId);
overtimeViewModel.Overtime.Personnel = personnel;
var viewModel = new OvertimeViewModel
{
Overtime = overtimeViewModel.Overtime,
OvertimePreferences = new SelectList(overtimePreferences, "OvertimePreferenceId", "Name"),
OvertimeSummary = overtimeSummary
};
return(View(viewModel));
}
public ActionResult Edit(int?personnelId, int?id)
{
if (id == null)
{
return(new HttpStatusCodeResult(HttpStatusCode.BadRequest));
}
var overtime = HRBusinessService.RetrieveOvertime(UserOrganisationId, id.Value);
if (overtime == null)
{
return(HttpNotFound());
}
bool isAdmin = User.IsInRole("Admin");
var overtimePreferences = HRBusinessService.RetrieveOvertimePreferences(UserOrganisationId);
var overtimeSummary = HRBusinessService.RetrieveOvertimeSummary(UserOrganisationId, personnelId.Value);
var permissions = HRBusinessService.RetrievePersonnelPermissions(isAdmin, UserOrganisationId, personnelId.Value);
bool canApproveOvertime = HRBusinessService.CanApproveOvertime(UserOrganisationId, id.Value, isAdmin, ApplicationUser.Id);
var viewModel = new OvertimeViewModel
{
Overtime = overtime,
OvertimePreferences = new SelectList(overtimePreferences, "OvertimePreferenceId", "Name"),
OvertimeSummary = overtimeSummary,
CanApprovedOvertime = canApproveOvertime,
Permissions = permissions
};
return(View(viewModel));
}
/// <summary>
/// 判斷是否有申請過加班，並判斷請領方式(同一天 請領方式須相同)
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public string CheckHasOverTimeThenPayTypeSame(OvertimeViewModel model)
{
string beforePayTypeValue = string.Empty;
var stDate = string.Format("{0:yyyyMMdd}", model.StartDateTime);
var enDate = string.Format("{0:yyyyMMdd}", model.EndDateTime);
string strSQL = @"
select *
from overtimeform o
inner join SignForm_Main s on o.SignDocID_FK = s.SignDocID
where s.FinalStatus <> '5'
and o.EmployeeID_FK = @EmployeCD
and PayTypeKey != @OvertimePay
and (CONVERT(varchar(12) ,StartDateTime, 112 ) = @StartDate
or CONVERT(varchar(12) ,EndDateTime, 112 ) = @EndDate)
";
var conditions = new Conditions()
{
{ "@StartDate", stDate },
{ "@EndDate", enDate },
{ "@EmployeCD", model.EmployeeID_FK },
{ "@OvertimePay", model.PayTypeKey }
};
var result = _dc.QueryForDataRow(strSQL, conditions);
if (result != null)
{
string typeKey = result["PayTypeKey"].ToString();
switch (typeKey)
{
case "overtimeLeave":
beforePayTypeValue = "換休";
break;
case "overtimePay":
beforePayTypeValue = "加班費";
break;
default:
break;
}
}
return(beforePayTypeValue);
}
public DataRow QueryForOvertimeRecord(OvertimeViewModel overtime, string tableName)
{
if (overtime == null)
{
throw new Exception("無加班單資料");
}
var conditions = new Conditions()
{
{ "@EMPLOYECD", overtime.EmployeeID_FK },
{ "@DUTYDATE", overtime.StartDateTime.Value.ToString("yyyyMMdd") },
};
var strSQL = String.Format("Select * from {0} where Employecd = @Employecd and dutyDate = @dutydate", tableName);
var data = _dc.QueryForDataRow(strSQL, conditions);
if (data == null)
{
return(null);
}
return(data);
}
public ActionResult Edit(OvertimeViewModel overtimeViewModel)
{
var overtime = overtimeViewModel.Overtime;
if (ModelState.IsValid)
{
overtime.UpdatedBy = ApplicationUser.UserName;
overtime.UpdatedDateUtc = DateTime.UtcNow;
HRBusinessService.UpdateOvertime(UserOrganisationId, overtimeViewModel.Overtime);
return(RedirectToAction("profile", "personnel", new { id = overtimeViewModel.PersonnelId }));
}
var overtimePreferences = HRBusinessService.RetrieveOvertimePreferences(UserOrganisationId);
var overtimeSummary = HRBusinessService.RetrieveOvertimeSummary(UserOrganisationId, overtimeViewModel.PersonnelId);
var viewModel = new OvertimeViewModel
{
Overtime = overtime,
OvertimePreferences = new SelectList(overtimePreferences, "OvertimePreferenceId", "Name"),
OvertimeSummary = overtimeSummary
};
return(View(viewModel));
}
private void setConditions(OvertimeViewModel overtime, DateTime payRange, ref Conditions conditions)
{
switch (overtime.PayTypeKey.ToUpper())
{
case "OVERTIMEPAY":
conditions.Add("@RESTYN", 'N');
var dutyWorkData = _smartRepo.QueryForDutyWork(overtime.StartDateTime.Value.ToDateTimeFormateString("yyyyMMdd"), overtime.EmployeeID_FK);
var OverWorkData = _smartRepo.QueryOverWorkByDate(overtime.StartDateTime.Value.ToDateTimeFormateString("yyyyMMdd"), overtime.EmployeeID_FK);
overtime.IsHoliday = "Y".Equals(dutyWorkData["HOLIDAY"].ToString());
//var OVERWORK2 = OverWorkData["OVERWORK2"].ToString();
if (!overtime.IsHoliday)
{
var totalHR = overtime.TotalHours;
if (OverWorkData == null) //當日未申報過加班
{
var start2 = 2;
//1.33
conditions.Add("@OVERWORKone", (totalHR - 2 >= 0) ? start2 : totalHR);
//1.66
conditions.Add("@OVERWORKtwo", (totalHR - start2 > 0) ? totalHR - start2 : 0);
}
else
{
var OVERWORK1 = OverWorkData["OVERWORK1"].ToString();
if (OVERWORK1 == "1.00") //當日申報過加班
{
//1.33
conditions.Add("@OVERWORKone", 1);
//1.66
conditions.Add("@OVERWORKtwo", (totalHR - 1 > 0) ? totalHR - 1 : 0);
}
else if (OVERWORK1 == "2.00") //當日申報過加班
{
//1.66
conditions.Add("@OVERWORKtwo", totalHR);
}
else if (OVERWORK1 == "0.50")
{
var start2 = 1.5;
//1.33
conditions.Add("@OVERWORKone", (totalHR - 1.5 >= 0) ? start2 : totalHR);
//conditions.Add("@OVERWORKone", 0.5);
//1.66
conditions.Add("@OVERWORKtwo", (totalHR - start2 > 0) ? totalHR - start2 : 0);
}
}
}
else
{
var totalHR = overtime.TotalHours;
//20161223 一例一休-國定假日加班時數寫入志元假日加班欄位前8H 1倍，超過8H 前2H 1.34 剩餘1.67 (20161223實施)
//20161223 一例一休-休息日加班時數寫入志元前2H1.33、剩餘1.66欄位(20161223實施)
//var dutyWorkData = _smartRepo.QueryForDutyWork(overtime.StartDateTime.Value.ToDateTimeFormateString("yyyyMMdd"), overtime.EmployeeID_FK);
var HolidayType = dutyWorkData["H_TYPE"].ToString();
if (HolidayType == "2") //20161223 一例一休-國定假日加班時數寫入志元假日加班欄位 8H 1倍
{
if (totalHR - 8 >= 0)
{
//1倍
conditions.Add("@OVERWORKfour", 8);
}
totalHR -= 8;
}
else if (HolidayType == "1") //休息日8H後以2.67計算
{
var start2 = 2;
//1.33
conditions.Add("@OVERWORKone", (totalHR - 2 >= 0) ? start2 : totalHR);
//1.66
conditions.Add("@OVERWORKtwo", (totalHR - 8 > 0) ? totalHR - start2 - 4 : totalHR - start2);
totalHR -= 8;
if (totalHR > 0)
{
conditions.Add("@OVERWORKthree", totalHR);
}
}
else
{
if (totalHR > 0)
{
var start2 = 2;
//1.33
conditions.Add("@OVERWORKone", (totalHR - 2 >= 0) ? start2 : totalHR);
//1.66
conditions.Add("@OVERWORKtwo", (totalHR - start2 > 0) ? totalHR - start2 : 0);
}
}
////if (HolidayType == "1")
////{
//if (totalHR > 0)
//{
// var start2 = 2;
// //1.33
// conditions.Add("@OVERWORKone", (totalHR - 2 >= 0) ? start2 : totalHR);
// //1.66
// conditions.Add("@OVERWORKtwo", (totalHR - start2 > 0) ? totalHR - start2 : 0);
//}
////}
}
break;
case "OVERTIMELEAVE":
conditions.Add("@RESTYN", "Y");
conditions.Add("@ADDHOURS", overtime.TotalHours);
#region #0009 補修有效日期 6個月後的20日
string limitDateStr = payRange.AddMonths(6).ToString("yyyyMM20");
#endregion #0009 補修有效日期 6個月後的20日
// 結薪月後推6個月
conditions.Add("@CHANGEDATE", limitDateStr);
break;
default:
throw new Exception("無此報酬型別!");
}
}
public MultiConditions GetOvertimeDML(OvertimeViewModel overtime, DateTime payRange)
{
_rootRepo = RepositoryFactory.CreateRootRepo();
var DeptDataLevel = _rootRepo.QueryForDepartmentByDeptID(overtime.SupportDeptID_FK);
var dutyWorkData = _smartRepo.QueryForDutyWork(overtime.StartDateTime.Value.ToDateTimeFormateString("yyyyMMdd"), overtime.EmployeeID_FK);
overtime.IsHoliday = "Y".Equals(dutyWorkData["HOLIDAY"].ToString());
var HolidayType = dutyWorkData["H_TYPE"].ToString();
string Level = DeptDataLevel != null ? DeptDataLevel["DepartmentLevel"].ToString() : String.Empty;
string TrueSupportDept = "";
if (DeptDataLevel["Virtual"].ToString() == "N")
{
TrueSupportDept = DeptDataLevel["DepartmentID"].ToString();
}
else
{
string UpDept = "";
for (int i = Int32.Parse(Level) - 1; i >= 1; i--)
{
if (UpDept == "")
{
var DeptData = _rootRepo.QueryForDepartmentByDeptID(DeptDataLevel["UpperDepartmentID"].ToString());
if (DeptData["Virtual"].ToString() == "N")
{
TrueSupportDept = DeptData["DepartmentID"].ToString();
break;
}
else
{
UpDept = DeptData["UpperDepartmentID"].ToString();
}
}
else
{
var DeptData = _rootRepo.QueryForDepartmentByDeptID(UpDept);
if (DeptData["Virtual"].ToString() == "N")
{
TrueSupportDept = DeptData["DepartmentID"].ToString();
break;
}
else
{
UpDept = DeptData["UpperDepartmentID"].ToString();
}
}
}//迴圈END
}
string remark = "";
if (overtime.Note.Length > 30)
{
remark = overtime.Note.Substring(0, 30);
}
else
{
remark = overtime.Note;
}
var result = new MultiConditions();
string strSQL = null;
var dateTimeNow = DateTime.Now;
Conditions conditions = new Conditions()
{
{ "@SYSTEMDATE", dateTimeNow.ToString("yyyyMMdd") },
{ "@SYSTEMTIME", dateTimeNow.ToString("HHmm") },
{ "@LOGINUSER", "System" },
{ "@APPROVEUSER", "System" },
{ "@APPROVEYN", "Y" },
{ "@COMPANYCD", "A" },
{ "@EMPLOYECD", overtime.EmployeeID_FK },
{ "@PAYYYYYMM", payRange.ToString("yyyyMM") },
{ "@DUTYDATE", overtime.StartDateTime.Value.ToString("yyyyMMdd") },
{ "@BEGINTIME", overtime.BeginTime },
{ "@ENDTIME", overtime.EndTime },
{ "@OVERONWORK", overtime.StartDateTime.Value.ToString("HHmm") },
{ "@OVEROFFWORK", overtime.EndDateTime.Value.ToString("HHmm") },
{ "@OVERWORKHOURS", overtime.TotalHours },
//20170109 加班說明寫入志元只可30個字
//{ "@REMARK", overtime.Note.Substring(0,30)},
{ "@REMARK", remark },
//20161104 修正單號紀錄
//{ "@SEQNOX", String.Concat("OT",dateTimeNow.ToString("yyyyMMdd"), dateTimeNow.ToString("HHmmss"))},
{ "@SEQNOX", overtime.SignDocID_FK },
//{ "@HOLIDAY", overtime.IsHoliday },
{ "@HOLIDAY", overtime.IsHoliday ? "Y" : "N" },
//20161028 修正寫入志元table部門
//{ "@UNITCD", overtime.DepartmentID_FK},
{ "@UNITCD", overtime.CostDepartmentID },
{ "@COSTCD", TrueSupportDept },
{ "@WORKTYPE", overtime.WorkType },
{ "@H_TYPE", HolidayType },
};
string tableName = RepositoryFactory.SmartManConn["DataSource"] + "." + RepositoryFactory.SmartManConn["Catelog"] + ".dbo.DailyOn";
setConditions(overtime, payRange, ref conditions);
var dailyOnData = QueryForOvertimeRecord(overtime, tableName);
if (dailyOnData == null)
{
// insert
strSQL = _dc.ConstructInsertDML(tableName, conditions);
}
else
{
// update
var distinglishType = Double.Parse(dailyOnData["ADDHOURS"].ToString()) > 0 ? "OVERTIMELEAVE" : "OVERTIMEPAY";
var overonworkArray = new string[] { overtime.StartDateTime.Value.ToString("HHmm"), (string)dailyOnData["OVERONWORK"] };
var overoffworkArray = new string[] { overtime.EndDateTime.Value.ToString("HHmm"), (string)dailyOnData["OVEROFFWORK"] };
//替換加班申請單 開始與結束的時間
conditions["@OVERONWORK"] = overonworkArray.Min();
conditions["@OVEROFFWORK"] = overoffworkArray.Max();
switch (overtime.PayTypeKey.ToUpper() + distinglishType)
{
case "OVERTIMEPAYOVERTIMEPAY":
strSQL = String.Format(
@"Update {0} Set
SYSTEMDATE = @SYSTEMDATE,
SYSTEMTIME = @SYSTEMTIME,
OVERONWORK = @OVERONWORK,
OVEROFFWORK = @OVEROFFWORK,
OVERWORKone = OVERWORKone + @OVERWORKone,
OVERWORKtwo = OVERWORKtwo + @OVERWORKtwo,
OVERWORKfour = OVERWORKfour + @OVERWORKfour,
OVERWORKHOURS = OVERWORKHOURS + @OVERWORKHOURS,
REMARK = REMARK +@REMARK
Where EMPLOYECD = @EMPLOYECD and DUTYDATE=@DUTYDATE", tableName);
break;
case "OVERTIMELEAVEOVERTIMELEAVE":
strSQL = String.Format(
@"Update {0} Set
SYSTEMDATE = @SYSTEMDATE,
SYSTEMTIME = @SYSTEMTIME,
OVERONWORK = @OVERONWORK,
OVEROFFWORK = @OVEROFFWORK,
OVERWORKHOURS = OVERWORKHOURS + @OVERWORKHOURS,
ADDHOURS = ADDHOURS + @ADDHOURS,
REMARK = REMARK +@REMARK
Where EMPLOYECD = @EMPLOYECD and DUTYDATE=@DUTYDATE ", tableName);
break;
default:
throw new Exception("報酬型別選擇錯誤!");
}
}
if (conditions.ContainsKey("@OVERWORKone"))//1.34
{
strSQL = strSQL.Replace("OVERWORKone", "OVERWORK1");
conditions.Add("@OVERWORK1", conditions["@OVERWORKone"]);
conditions.Remove("@OVERWORKone");
}
else
{
strSQL = Regex.Replace(strSQL, @"(?m)^OVERWORKone.*?,", "");
}
if (conditions.ContainsKey("@OVERWORKtwo"))//1.67
{
strSQL = strSQL.Replace("OVERWORKtwo", "OVERWORK2");
conditions.Add("@OVERWORK2", conditions["@OVERWORKtwo"]);
conditions.Remove("@OVERWORKtwo");
}
else
{
strSQL = Regex.Replace(strSQL, @"(?m)^OVERWORKtwo.*?,", "");
}
if (conditions.ContainsKey("@OVERWORKthree"))//2.67
{
strSQL = strSQL.Replace("OVERWORKthree", "OVERWORK3");
conditions.Add("@OVERWORK3", conditions["@OVERWORKthree"]);
conditions.Remove("@OVERWORKthree");
}
else
{
strSQL = Regex.Replace(strSQL, @"(?m)^OVERWORKthree.*?,", "");
}
if (conditions.ContainsKey("@OVERWORKfour"))
{
strSQL = strSQL.Replace("OVERWORKfour", "OVERWORK4");
conditions.Add("@OVERWORK4", conditions["@OVERWORKfour"]);
conditions.Remove("@OVERWORKfour");
}
else
{
strSQL = Regex.Replace(strSQL, @"(?m)^OVERWORKfour.*?,", "");
}
result.Add(strSQL, conditions);
return(result);
}
//建立預設資料
public List <OvertimeViewModel> GetDefaultData(Dictionary <string, string> model)
{
//新增單筆，給予預設值
if (model.ContainsKey("IsAddRow"))
{
var defaultResult = new List <OvertimeViewModel>();
var empData = _rootRepo.QueryForEmployeeByEmpID(model["NewRowEmpID"]);
var deptData = _rootRepo.QueryForDepartmentByDeptID(empData["DepartmentID_FK"].ToString());
var overtime = new OvertimeViewModel()
{
StartDateTime = model["DefaultStartDateTime"].ToDateTimeNullable(),
EndDateTime = model["DefaultEndDateTime"].ToDateTimeNullable(),
//SupportDeptID_FK = model["DefaultSupportDeptID"],
//SupportDeptName = model["DefaultSupportDeptName"],
SupportDeptID_FK = deptData["DepartmentID"].ToString(),
SupportDeptName = deptData["DepartmentName"].ToString(),
Note = model["DefaultNote"],
PayTypeKey = model["DefaultPayTypeKey"],
PayTypeValue = model["DefaultPayTypeValue"],
MealOrderKey = model["DefaultMealOrderKey"],
MealOrderValue = model["DefaultMealOrderValue"],
};
if (model.ContainsKey("NewRowEmpID"))
{
//var empData = _rootRepo.QueryForEmployeeByEmpID(model["NewRowEmpID"]);
if (empData == null)
{
throw new Exception("查無人員編號!");
}
//var deptData = _rootRepo.QueryForDepartmentByDeptID(empData["DepartmentID_FK"].ToString());
if (deptData == null)
{
throw new Exception("查無人員對應部門編號!");
}
overtime.EmployeeID_FK = empData["EmployeeID"].ToString();
overtime.EmployeeName = empData["EmployeeName"].ToString();
overtime.NationType = empData["NationalType"].ToString();
overtime.DepartmentID_FK = deptData["DepartmentID"].ToString();
overtime.DepartmentName = deptData["DepartmentName"].ToString();
overtime.SupportDeptID_FK = deptData["DepartmentID"].ToString();
overtime.SupportDeptName = deptData["DepartmentName"].ToString();
}
defaultResult.Add(overtime);
return(defaultResult);
}
//新增多筆，將資料帶出
//var strSQL =@"select * from employee where Disabled = 'False' and DepartmentID_FK = @DepartmentID_FK";
var strSQL = @"select * from employee emp
left join Department dept on emp.DepartmentID_FK=dept.DepartmentID
where emp.Disabled = 'False' and DepartmentID_FK = @DepartmentID_FK";
var result = _dc.QueryForDataRows(strSQL, new Conditions()
{
{ "@DepartmentID_FK", model["DefaultDeptID"] }
});
return(result.Select(data => new OvertimeViewModel()
{
EmployeeID_FK = data["EmployeeID"].ToString(),
EmployeeName = data["EmployeeName"].ToString(),
StartDateTime = model["DefaultStartDateTime"].ToDateTimeNullable(),
EndDateTime = model["DefaultEndDateTime"].ToDateTimeNullable(),
//SupportDeptID_FK = model["DefaultSupportDeptID"],
//SupportDeptName = model["DefaultSupportDeptName"],
SupportDeptID_FK = data["DepartmentID_FK"].ToString(),
SupportDeptName = data["DepartmentName"].ToString(),
Note = model["DefaultNote"],
PayTypeKey = model["DefaultPayTypeKey"],
PayTypeValue = model["DefaultPayTypeValue"],
MealOrderKey = model["DefaultMealOrderKey"],
MealOrderValue = model["DefaultMealOrderValue"],
NationType = data["NationalType"].ToString(),
DepartmentID_FK = model["DefaultDeptID"],
DepartmentName = model["DefaultDeptName"]
}).ToList());
}
