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