public override int Query(FormM form, DFDictionary entity, DataGridVM vm, int start, int limit, ref string message)
{
using (var db = Pub.DB)
{
var sql = @"select ins.*,u.chinesename,u.EmployeeId from WF_T_INSTANCE ins
left join wf_m_user u on ins.Requestor=u.userid
left join wf_m_userdept ud on u.deptid=ud.deptid
where 1=1 and ud.UserId=@UserId";
if (!string.IsNullOrWhiteSpace(entity["DeptId"]))
{
sql += " and ud.DeptId in (SELECT DeptId FROM dbo.WF_M_DEPT WHERE DeptLabel LIKE (SELECT DeptLabel+'%' FROM dbo.WF_M_DEPT WHERE DeptId=@DeptId))";
}
if (!string.IsNullOrWhiteSpace(entity["InstanceStatus"]))
{
sql += " and InstanceStatus in @InstanceStatus";
}
if (!string.IsNullOrWhiteSpace(entity["InstanceId"]))
{
sql += " and InstanceId like @InstanceId";
}
if (!string.IsNullOrWhiteSpace(entity["ModelName"]))
{
sql += " and ModelName like @ModelName";
}
if (!string.IsNullOrWhiteSpace(entity["RequestTimeFrom"]))
{
sql += " and RequestTime>=@RequestTimeFrom";
}
if (!string.IsNullOrWhiteSpace(entity["RequestTimeTo"]))
{
sql += " and RequestTime<=@RequestTimeTo";
}
if (!string.IsNullOrWhiteSpace(entity["RequestorName"]))
{
sql += " and (RequestorName like @RequestorName or u.EmployeeId like @RequestorName or u.ChineseName like @RequestorName)";
}
if (!string.IsNullOrWhiteSpace(entity["EndDateFrom"]))
{
sql += " and (ins.instancestatus='Finished' and ins.lastmodifytime>=@EndDateFrom)";
}
if (!string.IsNullOrWhiteSpace(entity["EndDateTo"]))
{
sql += " and (ins.instancestatus='Finished' and ins.lastmodifytime<=@EndDateTo)";
}
sql += " order by ins.LastModifyTime desc";
var parameters = new
{
UserId = Util.GetCurrentUser().UserId,
DeptId = entity["DeptId"],
InstanceStatus = entity["InstanceStatus"].Split(',').ToList(),
InstanceId = string.Format("%{0}%", entity["InstanceId"]),
ModelName = string.Format("%{0}%", entity["ModelName"]),
RequestTimeFrom = ParseHelper.ParseDate(entity["RequestTimeFrom"]).GetValueOrDefault().ToString("yyyy-MM-dd"),
RequestTimeTo = ParseHelper.ParseDate(entity["RequestTimeTo"]).GetValueOrDefault().ToString("yyyy-MM-dd 23:59:59.999"),
RequestorName = "%" + entity["RequestorName"] + "%",
EndDateFrom = ParseHelper.ParseDate(entity["EndDateFrom"]).GetValueOrDefault().ToString("yyyy-MM-dd"),
EndDateTo = ParseHelper.ParseDate(entity["EndDateTo"]).GetValueOrDefault().ToString("yyyy-MM-dd 23:59:59.999")
};
vm.results = db.Query <int>(DFPub.GetCountSql(sql), parameters).FirstOrDefault();
var list = db.Query <VM_WF_T_INSTANCE>(DFPub.GetPageSql(sql, start + 1, start + limit), parameters).ToList();
foreach (var l in list)
{
l.CurrentExecutorName = StepExecutorLoader.GetCurrentExecutorName(l.InstanceId);
}
vm.rows = list;
return(DFPub.EXECUTE_SUCCESS);
}
}
