基本查詢
複雜查詢示例
/// <summary> /// 獲取自定義表單資料中屬於部門的部分 /// </summary> /// <param name="month"></param> /// <param name="departmentId"></param> /// <param name="positionId"></param> /// <param name="fillUserName"></param> /// <param name="departmentName"></param> /// <param name="formName"></param> /// <param name="fieldName"></param> /// <param name="assesserName"></param> /// <returns></returns> public IList<CustomFormResultItem> GetDepartmentCustomFormItems(DateTime month, Guid? customFormId, Guid? departmentId, string fillUserName = "", string departmentName = "", string formName = "", string fieldName = "", string assesserName = "") { return GetCustomFormItemInternal(2, month, customFormId, departmentId, null, fillUserName, departmentName, formName, fieldName, assesserName); } public IList<CustomFormResultItem> GetCustomFormItemInternal(int? userorDeptControlType, DateTime month, Guid? customFormId, Guid? departmentId, Guid? positionId, string fillUserName = "", string belongToUserOrDepartmentName = "", string formName = "", string fieldName = "", string assesserName = "") { var query = _customFormResultItemRep.CreateCriteriaQuery() .CreateAlias("t.Result", "result") .CreateAlias("result.CreateUser", "createUser") .CreateAlias("t.FormItem", "formItem") .CreateAlias("result.CustomForm", "customForm"); if (month == DateTime.MinValue) month = DateTime.Now; //Filter by month query.Add(Restrictions.Eq(Projections.SqlFunction(new SQLFunctionTemplate(NHibernateUtil.Int16, "datediff(month,?1,?2)"), NHibernateUtil.Int16, Projections.Property("CreateDate"), Projections.Constant(month)), 0)); if (userorDeptControlType.HasValue) { if (userorDeptControlType == (int)FormControl.姓名) { query.CreateAlias("result.BelongUser", "belongUser"); if (departmentId.HasValue && departmentId != Guid.Empty) { query.Add(Restrictions.Eq("belongUser.Department.Id", departmentId)); } if (positionId.HasValue && positionId != Guid.Empty) { query.Add(Restrictions.Eq("belongUser.Position.Id", positionId)); } if (!string.IsNullOrWhiteSpace(belongToUserOrDepartmentName)) { query.Add(Restrictions.Like("belongUser.Name", belongToUserOrDepartmentName, MatchMode.Anywhere)); } } else if (userorDeptControlType == (int)FormControl.部門) { query.CreateAlias("result.BelongDepartment", "belongDepartment"); if (departmentId.HasValue && departmentId != Guid.Empty) { query.Add(Restrictions.Eq("belongDepartment.Id", departmentId)); } //if (positionId.HasValue && positionId != Guid.Empty) //{ // query.Add(Restrictions.Eq("belongUser.Position.Id", positionId)); //} if (!string.IsNullOrWhiteSpace(belongToUserOrDepartmentName)) { query.Add(Restrictions.Like("belongDepartment.Name", belongToUserOrDepartmentName, MatchMode.Anywhere)); } } var customFormSubQuery = DetachedCriteria.For<CustomFormItem>("cfi") .CreateAlias("CustomForm", "cf") .Add(Restrictions.Eq("cfi.FieldType", (FormControl)userorDeptControlType)) .SetResultTransformer(Transformers.DistinctRootEntity) .SetProjection(Projections.Property("cf.Id")); query.Add(Subqueries.PropertyIn("customForm.Id", customFormSubQuery)); if (userorDeptControlType == (int)FormControl.姓名) { query.AddOrder(new Order("belongUser.Name", true)); } else { query.AddOrder(new Order("belongDepartment.Name", true)); } } if (!string.IsNullOrWhiteSpace(fillUserName)) { query.Add(Restrictions.Like("createUser.Name", fillUserName, MatchMode.Anywhere)); } if (!string.IsNullOrWhiteSpace(formName)) { query.Add(Restrictions.Like("customForm.Name", formName, MatchMode.Anywhere)); } if (customFormId != null && customFormId != Guid.Empty) { query.Add(Restrictions.Eq("customForm.Id", customFormId)); } if (!string.IsNullOrWhiteSpace(fieldName)) { query.Add(Restrictions.Like("t.FieldName", fieldName, MatchMode.Anywhere)); } if (!string.IsNullOrEmpty(assesserName)) { query.Add(Restrictions.Like("createUser.Name", assesserName, MatchMode.Anywhere)); } var types = new List<FormControl>() { FormControl.單選, FormControl.多選, FormControl.引用部門, FormControl.指標標準, FormControl.數字框, FormControl.文字框, FormControl.日期, FormControl.段落 }.ToArray(); if (types.Length > 0) query.Add(Restrictions.In("formItem.FieldType", types)); query.AddOrder(new Order("result.Id", true)); query.AddOrder(new Order("formItem.OrderByIndex", true)); query.SetMaxResults(100); var resultList = query.List<CustomFormResultItem>(); return resultList; }
資料庫函式查詢示例
public IList<DepartmentIndicatorResult> SearchDepartmentIndicatorResult(DateTime evaluateMonth, Guid? departmentId, string indicatorName, string departmentName) { var query = this._departmentIndicatorResultRep.CreateCriteriaQuery().CreateAlias("t.Department", "dept").CreateAlias("t.Indicator", "indicator"); if (evaluateMonth == DateTime.MinValue) evaluateMonth = DateTime.Now; //Filter by month query.Add(Restrictions.Eq(Projections.SqlFunction(new SQLFunctionTemplate(NHibernateUtil.Int16, "datediff(month,?1,?2)"), NHibernateUtil.Int16, Projections.Property("PointDate"), Projections.Constant(evaluateMonth)), 0)); //filter by day //query.Add(Restrictions.Eq(Projections.SqlFunction(new SQLFunctionTemplate(NHibernateUtil.Int16, "datediff(day,?1,?2)"), NHibernateUtil.Int16, Projections.Property("PointDate"), Projections.Constant(evaluateMonth)), 0)); if (departmentId.HasValue && departmentId != Guid.Empty) { query.Add(Restrictions.Eq("user.Department.Id", departmentId)); } if (!string.IsNullOrWhiteSpace(departmentName)) { query.Add(Restrictions.Like("dept.Name", departmentName, MatchMode.Anywhere)); } if (!string.IsNullOrWhiteSpace(indicatorName)) { query.Add(Restrictions.Like("indicator.Name", indicatorName, MatchMode.Anywhere)); } //query.Add(Restrictions.Eq("indicator.DataCollectorType", DataCollectorType.月底收集)); query.SetMaxResults(100); //query.AddOrder(new Order("pos.Name", true)); var resultList = query.List<DepartmentIndicatorResult>(); return resultList; }
查詢示例展示
/// <summary> /// 查詢員工指標考核結果(日評,月底指標考核成績) /// </summary> /// <param name="dateRangeType"></param> /// <param name="date"></param> /// <param name="departmentId"></param> /// <param name="positionId"></param> /// <param name="indicatorName"></param> /// <param name="userName"></param> /// <param name="dataCollectorTypes"></param> /// <returns></returns> private IList<UserIndicatorResult> SearchUserIndicatorResult(DateRangeType dateRangeType, DateTime date, Guid? departmentId = null, Guid? positionId = null, string indicatorName = null, string userName = null, IList<DataCollectorType> dataCollectorTypes = null, IList<DataInputType> dataInputTypes = null, Guid? userId = null, Guid? indicatorId = null, int limit = 200, bool isAssess = true) { var query = this._userIndicatorResultRep.CreateCriteriaQuery().CreateAlias("t.User", "user").CreateAlias("t.Indicator", "indicator") .Add(Restrictions.Eq("t.DateRangeType", dateRangeType)); if (date == DateTime.MinValue) date = DateTime.Now; if (dateRangeType == DateRangeType.Monthly || (dataCollectorTypes != null && dataCollectorTypes.Contains(DataCollectorType.月底收集))) { //Filter by month query.Add(Restrictions.Eq(Projections.SqlFunction(new SQLFunctionTemplate(NHibernateUtil.Int16, "datediff(month,?1,?2)"), NHibernateUtil.Int16, Projections.Property("PointDate"), Projections.Constant(date)), 0)); } else if (dateRangeType == DateRangeType.Daily) { //filter by day query.Add(Restrictions.Eq(Projections.SqlFunction(new SQLFunctionTemplate(NHibernateUtil.Int16, "datediff(day,?1,?2)"), NHibernateUtil.Int16, Projections.Property("PointDate"), Projections.Constant(date)), 0)); } if (departmentId.HasValue && departmentId != Guid.Empty) { query.Add(Restrictions.Eq("user.Department.Id", departmentId)); } if (positionId.HasValue && positionId != Guid.Empty) { query.Add(Restrictions.Eq("user.Position.Id", positionId)); } if (isAssess) { //todo query.Add(Restrictions.IsNotNull("TextResult")); } else { query.Add(Restrictions.IsNull("TextResult")); } if (!string.IsNullOrWhiteSpace(userName)) { query.Add(Restrictions.Like("user.Name", userName, MatchMode.Anywhere)); } if (userId.HasValue) { query.Add(Restrictions.Eq("user.Id", userId.Value)); } if (indicatorId.HasValue) { query.Add(Restrictions.Eq("indicator.Id", indicatorId.Value)); } if (!string.IsNullOrWhiteSpace(indicatorName)) { query.Add(Restrictions.Like("indicator.Name", indicatorName, MatchMode.Anywhere)); } if (dataCollectorTypes != null) query.Add(Restrictions.In("indicator.DataCollectorType", dataCollectorTypes.ToArray())); if (dataInputTypes != null) query.Add(Restrictions.In("indicator.DataInputType", dataInputTypes.ToArray())); //query.Add(Restrictions.Eq("indicator.DataCollectorType", DataCollectorType.月底收集)); //為了效能的考慮,每次值允許拉200條記錄 query.SetMaxResults(limit); query.AddOrder(new Order("user.Name", true)); var resultList = query.List<UserIndicatorResult>(); return resultList; }
public IList<PositionIndicator> SearchPositionIndicatorList( List<Guid> departmentIds = null, Guid? departmentId = null, string departmentName = null, List<Guid> positionIds = null, Guid? positionId = null, string positionName = null, List<Guid> linkedFormIds = null, Guid? linkedFormId = null, string indicatorName = null, EvaluateType? evaluateType = null, string dataSource = null, string discriminant = null, Guid? crossAssesserId = null, string crossAssesserName = null, List<DataCollectorType> dataCollectorTypes = null, DataCollectorType? dataCollectorType = null, int limit = 200) { var query = CreateCriteriaQuery<PositionIndicator>() .CreateAlias("t.Position", "pos").CreateAlias("t.CrossAssesser", "ca", JoinType.LeftOuterJoin); #region Special Query if (departmentIds != null || departmentId.HasValue) { if (departmentIds == null) departmentIds = new List<Guid>(); if (departmentId.HasValue) departmentIds.Add(departmentId.Value); var positionSubquery = DetachedCriteria.For<Department>() .CreateAlias("Positions", "pos", NHibernate.SqlCommand.JoinType.InnerJoin) .Add(Restrictions.In("Id", departmentIds.ToArray())) .SetProjection(Projections.Property("pos.Id")); query.Add(Subqueries.PropertyIn("pos.Id", positionSubquery)); } if (!string.IsNullOrWhiteSpace(departmentName)) { var positionSubquery = DetachedCriteria.For<Department>() .CreateAlias("Positions", "pos", NHibernate.SqlCommand.JoinType.InnerJoin) .Add(Restrictions.Like("Name", departmentName, MatchMode.Anywhere)) .SetProjection(Projections.Property("pos.Id")); query.Add(Subqueries.PropertyIn("pos.Id", positionSubquery)); } if (positionIds != null || positionId.HasValue) { if (positionIds == null) positionIds = new List<Guid>(); if (positionId.HasValue) positionIds.Add(positionId.Value); if (positionIds.Count > 0) query.Add(Restrictions.In("t.Position.Id", positionIds.ToArray())); } if (!string.IsNullOrWhiteSpace(positionName)) { query.Add(Restrictions.Like("t.Position.Name", positionName, MatchMode.Anywhere)); } #endregion BindIndicatorQuery(query, linkedFormIds, linkedFormId, indicatorName, evaluateType, dataSource, discriminant, crossAssesserId, crossAssesserName, dataCollectorTypes, dataCollectorType, limit); return query.List<PositionIndicator>(); } public IList<DeptIndicator> SearchDepartmentIndicatorList( List<Guid> departmentIds = null, Guid? departmentId = null, string departmentName = null, List<Guid> linkedFormIds = null, Guid? linkedFormId = null, string indicatorName = null, EvaluateType? evaluateType = null, string dataSource = null, string discriminant = null, Guid? crossAssesserId = null, string crossAssesserName = null, List<DataCollectorType> dataCollectorTypes = null, DataCollectorType? dataCollectorType = null, int limit = 200) { var query = CreateCriteriaQuery<DeptIndicator>().CreateAlias("t.CrossAssesser", "ca"); if (departmentIds != null || departmentId.HasValue) { if (departmentIds == null) departmentIds = new List<Guid>(); if (departmentId.HasValue) departmentIds.Add(departmentId.Value); if (departmentIds.Count > 0) query.Add(Restrictions.In("t.Department.Id", departmentIds.ToArray())); } BindIndicatorQuery(query, linkedFormIds, linkedFormId, indicatorName, evaluateType, dataSource, discriminant, crossAssesserId, crossAssesserName, dataCollectorTypes, dataCollectorType, limit); return query.List<DeptIndicator>(); } private static void BindIndicatorQuery(ICriteria query, List<Guid> linkedFormIds = null, Guid? linkedFormId = null, string indicatorName = null, EvaluateType? evaluateType = null, string dataSource = null, string discriminant = null, Guid? crossAssesserId = null, string crossAssesserName = null, List<DataCollectorType> dataCollectorTypes = null, DataCollectorType? dataCollectorType = null, int limit = 200) { if (linkedFormIds != null || linkedFormId.HasValue) { if (linkedFormIds == null) linkedFormIds = new List<Guid>(); if (linkedFormId.HasValue) linkedFormIds.Add(linkedFormId.Value); if (linkedFormIds.Count > 0) query.Add(Restrictions.In("t.Form.Id", linkedFormIds.ToArray())); } if (!string.IsNullOrWhiteSpace(indicatorName)) { query.Add(Restrictions.Like("t.Name", indicatorName, MatchMode.Anywhere)); } if (evaluateType.HasValue) { query.Add(Restrictions.Eq("t.EvaluateType", evaluateType)); } if (!string.IsNullOrWhiteSpace(dataSource)) { query.Add(Restrictions.Like("t.DataSource", dataSource, MatchMode.Anywhere)); } if (!string.IsNullOrWhiteSpace(discriminant)) { query.Add(Restrictions.Like("t.Discriminant", discriminant, MatchMode.Anywhere)); } if (!string.IsNullOrWhiteSpace(crossAssesserName)) { query.Add(Restrictions.Like("ca.Name", crossAssesserName, MatchMode.Anywhere)); } if (crossAssesserId.HasValue) { query.Add(Restrictions.Eq("t.CrossAssesser.Id", crossAssesserId)); } if (dataCollectorTypes != null || dataCollectorType.HasValue) { if (dataCollectorTypes == null) dataCollectorTypes = new List<DataCollectorType>(); if (dataCollectorType.HasValue) dataCollectorTypes.Add(dataCollectorType.Value); if (dataCollectorTypes.Count > 0) query.Add(Restrictions.In("t.DataCollectorType", dataCollectorTypes.ToArray())); } query.SetMaxResults(limit); }