public JsonResult GetUserList(QueryBuilder qb)
{
var queryStartDate = "";
var queryEndDate = "";
foreach (var item in qb.Items)
{
if (item.Field == "WorkHourDate")
{
if (item.Method == QueryMethod.GreaterThanOrEqual)
{
queryStartDate = item.Value.ToString();
}
else if (item.Method == QueryMethod.LessThanOrEqual)
{
queryEndDate = item.Value.ToString();
}
}
}
qb.Items.RemoveAll(a => a.Field == "WorkHourDate");
if (string.IsNullOrEmpty(queryEndDate))
{
queryEndDate = DateTime.Now.ToString("yyyy-MM-dd") + " 23:59:59";
}
var deptID = GetQueryString("UserDeptID");
if (!string.IsNullOrEmpty(deptID))
{
qb.Add("UserDeptID", QueryMethod.Equal, deptID);
}
var sql = @"select (case when ApplyValue!=0 then Step1Value/ApplyValue else null end) Step1Rate,
(case when ApplyValue!=0 then Step2Value/ApplyValue else null end) Step2Rate,
(case when ApplyValue!=0 then LockedValue/ApplyValue else null end) LockedRate,
(case when {1} !=0 then ApplyValue/{1} else null end) ApplyRate,
{1} NeedApply,* from (
select UserID,UserName,UserDeptID,UserDeptName,
sum(WorkHour{0}) ApplyValue,COUNT(1) ApplyCount,
sum(Step1{0}) Step1Value,sum(Step2{0}) Step2Value,sum(Confirm{0}) LockedValue
from S_W_UserWorkHour where WorkHourDate>='" + queryStartDate + "' and WorkHourDate<='" + queryEndDate + @"'
group by UserID,UserName,UserDeptID,UserDeptName
) tmp";
//计算应填天数
var needApply = new WorkHourFO().GetWorkValue(queryStartDate, queryEndDate);
if (workHourType == WorkHourSaveType.Day.ToString())
{
sql = string.Format(sql, "Day", needApply.ToString());
}
else
{
sql = string.Format(sql, "Value", needApply.ToString());
}
var result = this.SqlHelper.ExecuteGridData(sql, qb);
return(Json(result));
}
private Dictionary <string, object> GetPieChartOption(int year, int month)
{
var startDate = new DateTime(year, month, 1);
var endDate = startDate.AddMonths(1).AddDays(-1);
var day = new WorkHourFO().GetWorkValue(startDate, endDate);
var title = year.ToString() + "年" + month + "月 应填：{0} 已填：{1} 未填：{2}";
string sql = @"select isnull(Sum(" + (workHourType != WorkHourSaveType.HD.ToString() ? "NormalValue" : "Round(isnull(NormalValue,0)/" + NormalHoursMax.ToString() + ",2)") + @"),0) as valueField,ProjectName as nameField from dbo.S_W_UserWorkHour
where UserID='{0}' and WorkHourDate>='{1}' and WorkHourDate<='{2}' group by ProjectName";
var dt = this.SqlHelper.ExecuteDataTable(String.Format(sql, this.CurrentUserInfo.UserID, startDate, endDate));
if (dt.Rows.Count == 0)
{
var row = dt.NewRow();
row["valueField"] = day;
row["nameField"] = "未填工时";
dt.Rows.Add(row);
title = String.Format(title, day, 0, Convert.ToInt16(day));
}
else
{
var obj = Convert.ToDecimal(dt.Compute("Sum(valueField)", ""));
if ((day - obj) > 0)
{
var row = dt.NewRow();
row["valueField"] = day - obj;
row["nameField"] = "未填工时";
dt.Rows.Add(row);
}
title = String.Format(title, day, Convert.ToInt16(obj), Convert.ToInt16(day - obj));
}
var pieChart = HighChartHelper.CreatePieChart(title, "工时", dt);
return(pieChart.Render());
}
public JsonResult ApproveWorkHourList(string SelectedData, string State)
{
Action action = () =>
{
if (string.IsNullOrEmpty(State))
{
throw new Formula.Exceptions.BusinessException("未传入State，无法操作");
}
var list = JsonHelper.ToList(SelectedData);
var fo = new WorkHourFO();
SQLHelper hrDB = SQLHelper.CreateSqlHelper(ConnEnum.Comprehensive);
var enumDef = EnumBaseHelper.GetEnumDef("Comprehensive.WorkHourState");
var enumItems = new List <EnumItemInfo>();
if (enumDef != null)
{
enumItems = enumDef.EnumItem.ToList();
}
var _state = State;
if (State == "Locked")
{
_state = "Confirm";
}
var fieldDt = GetFieldTable(hrDB, "S_W_UserWorkHour");
foreach (var item in list)
{
var itemStateLevel = 0d;
var itemState = item.GetValue("State");
if (enumItems.Any(a => a.Code == itemState))
{
itemStateLevel = enumItems.FirstOrDefault(a => a.Code == itemState).SortIndex.Value;
}
var fnStateLevel = 0d;
if (enumItems.Any(a => a.Code == State))
{
fnStateLevel = enumItems.FirstOrDefault(a => a.Code == State).SortIndex.Value;
}
if (fnStateLevel < itemStateLevel)
{
throw new Formula.Exceptions.BusinessException("已经【" + enumItems.FirstOrDefault(a => a.Code == itemState).Name
+ "】的数据不能再【" + enumItems.FirstOrDefault(a => a.Code == State).Name + "】，无法保存");
}
if (!item.ContainsKey(_state + "Value"))
{
throw new Formula.Exceptions.BusinessException("列表中不包含【" + _state + "Value】字段，无法保存");
}
if (!item.ContainsKey(_state + "Day"))
{
throw new Formula.Exceptions.BusinessException("列表中不包含【" + _state + "Day】字段，无法保存");
}
if (!item.ContainsKey(_state + "User"))
{
throw new Formula.Exceptions.BusinessException("列表中不包含【" + _state + "User】字段，无法保存");
}
if (!item.ContainsKey(_state + "Date"))
{
throw new Formula.Exceptions.BusinessException("列表中不包含【" + _state + "Date】字段，无法保存");
}
if (!item.ContainsKey("Is" + _state))
{
throw new Formula.Exceptions.BusinessException("列表中不包含【Is" + _state + "】字段，无法保存");
}
var id = item.GetValue("ID");
item.SetValue(_state + "User", this.CurrentUserInfo.UserID);
item.SetValue(_state + "UserName", this.CurrentUserInfo.UserName);
item.SetValue(_state + "Date", System.DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss"));
item.SetValue("Is" + _state, "1");
item.SetValue("State", State);
decimal?workHourValue = null;
if (!string.IsNullOrEmpty(item.GetValue("WorkHourValue")))
{
workHourValue = decimal.Parse(item.GetValue("WorkHourValue"));
}
decimal?approveValue = null;
if (!string.IsNullOrEmpty(item.GetValue(_state + "Value")))
{
approveValue = decimal.Parse(item.GetValue(_state + "Value"));
}
if (approveValue == null)
{
approveValue = workHourValue;//审批工时为空时，默认审批工时=填报工时；
}
item.SetValue(_state + "Value", approveValue);
decimal?approveDay = fo.ConvertHourToDay(approveValue, workHourType, NormalHoursMax);
item.SetValue(_state + "Day", approveDay);
var sb = new StringBuilder();
sb.AppendLine(CreateUpdateSql(item, hrDB, "S_W_UserWorkHour", id, fieldDt));
var detailDic = new Dictionary <string, object>();
detailDic.SetValue("ID", FormulaHelper.CreateGuid());
detailDic.SetValue("S_W_UserWorkHourID", id);
detailDic.SetValue("SortIndex", 0);
detailDic.SetValue("ApproveValue", approveValue);
detailDic.SetValue("ApproveDay", approveDay);
detailDic.SetValue("ApproveDate", item.GetValue(_state + "Date"));
detailDic.SetValue("ApproveStep", State);
detailDic.SetValue("ApproveUser", this.CurrentUserInfo.UserID);
detailDic.SetValue("ApproveUserName", this.CurrentUserInfo.UserName);
sb.AppendLine(detailDic.CreateInsertSql(hrDB, "S_W_UserWorkHour_ApproveDetail", detailDic.GetValue("ID")));
var sql = sb.ToString();
hrDB.ExecuteNonQuery(sql);
if (State == "Locked")
{
#region 工时审批通过后写入成本数据
S_W_UserWorkHour workHourInfo = new S_W_UserWorkHour();
UpdateEntity <S_W_UserWorkHour>(workHourInfo, item);
workHourInfo.ImportToCost();
#endregion
}
}
};
if (System.Configuration.ConfigurationManager.AppSettings["UseMsdtc"].ToLower() == "true")
{
using (TransactionScope ts = new TransactionScope())
{
action();
ts.Complete();
}
}
else
{
action();
}
return(Json(""));
}
public JsonResult GetDeptList(QueryBuilder qb)
{
var depts = EnumBaseHelper.GetEnumDef("HR.WorkHourDept").EnumItem.ToList();
var queryStartDate = "";
var queryEndDate = "";
foreach (var item in qb.Items)
{
if (item.Field == "WorkHourDate")
{
if (item.Method == QueryMethod.GreaterThanOrEqual)
{
queryStartDate = item.Value.ToString();
}
else if (item.Method == QueryMethod.LessThanOrEqual)
{
queryEndDate = item.Value.ToString();
}
}
}
qb.Items.RemoveAll(a => a.Field == "WorkHourDate");
if (string.IsNullOrEmpty(queryEndDate))
{
queryEndDate = DateTime.Now.ToString("yyyy-MM-dd") + " 23:59:59";
}
var sql = @"select (case when ApplyValue!=0 then Step1Value/ApplyValue else null end) Step1Rate,
(case when ApplyValue!=0 then Step2Value/ApplyValue else null end) Step2Rate,
(case when ApplyValue!=0 then LockedValue/ApplyValue else null end) LockedRate,
(case when {1} !=0 then NormalValue/({1}*UserCount) else null end) ApplyRate,
({1}*UserCount) NeedApply,* from (
select DeptID UserDeptID,DeptName UserDeptName,COUNT(distinct UserID) UserCount,sum(ApplyValue) ApplyValue,sum(ApplyCount) ApplyCount,sum(NormalValue) NormalValue,sum(AdditionalValue) AdditionalValue,
sum(Step1Value) Step1Value,sum(Step2Value) Step2Value,sum(LockedValue) LockedValue from T_Employee e
left join (select EmployeeID,sum(WorkHour{0}) ApplyValue,COUNT(1) ApplyCount,sum(NormalValue) NormalValue,sum(AdditionalValue) AdditionalValue,
sum(Step1{0}) Step1Value,sum(Step2{0}) Step2Value,sum(Confirm{0}) LockedValue
from S_W_UserWorkHour where WorkHourDate>='" + queryStartDate + "' and WorkHourDate<='" + queryEndDate + @"'
group by EmployeeID) w on w.EmployeeID = e.ID
where IsDeleted='0'
group by DeptID,DeptName
) tmp";
//计算应填天数
var needApply = new WorkHourFO().GetWorkValue(queryStartDate, queryEndDate);
if (workHourType == WorkHourSaveType.Day.ToString())
{
sql = string.Format(sql, "Day", needApply.ToString());
}
else
{
sql = string.Format(sql, "Value", needApply.ToString());
}
var data = this.SqlHelper.ExecuteDataTable(sql, qb);
var result = data.Clone();
foreach (var dept in depts)
{
var d = data.Select("UserDeptID = '" + dept.Code + "' and UserDeptName = '" + dept.Name + "'").FirstOrDefault();
if (d == null)
{
d = result.NewRow();
d["UserDeptID"] = dept.Code;
d["UserDeptName"] = dept.Name;
}
result.Rows.Add(d.ItemArray);
}
return(Json(result));
}