private void AddCriteriaImpl() { var field = lvwFields.SelectedItem as FieldDescriptor; if (field != null) { var c = new QueryCriteria { Field = field, Output = true }; new SupportService(User).SetDefaultFormatOptions(c); _model.Add(c); } }
private void AddToFieldList(QueryCriteria c, string pstrCriteria) { if (c.Output) { string alias = c.Alias; if (string.IsNullOrEmpty(alias)) { alias = c.Field.DisplayName; } if (String.IsNullOrWhiteSpace(alias)) { _fieldList.Add(GetTableAlias(c.Field.TableName) + ".[" + c.Field.FieldName + "]"); } else { _fieldList.Add(GetTableAlias(c.Field.TableName) + ".[" + c.Field.FieldName + "] AS [" + alias + "]"); } // Add to the formatting requirements if required. if (!String.IsNullOrWhiteSpace(c.Field.Format)) { _formatting.Append((char)1).Append(c.Alias).Append((char)2).Append(c.FormatOption).Append((char)2).Append(c.Field.Format); } else if (c.Field.FieldName == "intDateStart" || c.Field.FieldName == "intDateEnd") { _formatting.Append((char)1).Append(c.Alias).Append((char)2).Append(strFORMAT_BLDATE_TO_DISPLAY).Append((char)2); } } if (!String.IsNullOrWhiteSpace(pstrCriteria)) { _whereClause.Add("(" + pstrCriteria.Trim() + ")"); } }
private void SplitField(QueryCriteria c) { string traitID, traitCatID, traitTableName, traitTableAlias = null; bool isTrait = IsTrait(c.Field, out traitID, out traitCatID, out traitTableName); bool isNote = false; if (isTrait) { traitTableAlias = string.Format("T{0}", _traitClauses.Count + 1); AddToTraitList(traitTableAlias, traitID, traitCatID, traitTableName, FleshCriteria(c.Criteria, "", traitTableAlias + ".vchrValue")); AddTable(traitTableName); } else { isNote = IsNote(c.Field, out traitID, out traitCatID, out traitTableName); if (isNote) { traitTableAlias = string.Format("N{0}", _noteClauses.Count + 1); AddToNoteList(traitTableAlias, traitID, traitCatID, traitTableName, FleshCriteria(c.Criteria, "", traitTableAlias + ".txtNote")); AddTable(traitTableName); } else { AddTable(c.Field.TableName); } } string alias = c.Alias; if (string.IsNullOrEmpty(alias)) { alias = NormaliseAlias(c.Field.DisplayName); } if (isTrait) { AddTraitToFieldList(c.Field.FieldName, c.Output, traitTableAlias, alias, traitID, traitCatID, traitTableName); if (!String.IsNullOrEmpty(c.Sort)) { AddToSortList(c.Sort, traitTableAlias, "vchrValue"); } } else if (isNote) { AddNoteToFieldList(c.Field.FieldName, c.Output, traitTableAlias, alias, traitID, traitCatID, traitTableName); if (!String.IsNullOrEmpty(c.Sort)) { AddToSortList(c.Sort, traitTableAlias, "txtNote"); } } else { // Add the field to the list. AddToFieldList(c, FleshCriteria(c.Criteria, c.Field.TableName, c.Field.FieldName)); if (!String.IsNullOrEmpty(c.Sort)) { AddToSortList(c.Sort, GetTableAlias(c.Field.TableName), c.Field.FieldName); } } }
private DataMatrix ExecuteLabelSetQuery(List<LabelSetItem> items, IEnumerable<QueryCriteria> criteria, string elemType) { var idList = new List<Int32>(); foreach (LabelSetItem item in items) { int id = 0; switch (elemType) { case "Site": case "PoliticalRegion": id = item.SiteID; break; case "SiteVisit": id = item.VisitID; break; case "Material": id = item.MaterialID; break; } if (id != 0) { idList.Add(id); } } if (idList.Count == 0) { return null; } List<QueryCriteria> finalCriteria = new List<QueryCriteria>(); // Now work out the fields foreach (QueryCriteria c in criteria) { bool include = false; switch (elemType) { case "Site": case "PoliticalRegion": include = c.Field.Category.Equals("Site", StringComparison.CurrentCultureIgnoreCase) || c.Field.Category.Equals("PoliticalRegion", StringComparison.CurrentCultureIgnoreCase); break; case "SiteVisit": include = c.Field.Category.Equals("SiteVisit"); break; case "Material": include = c.Field.Category.Equals("Material") || c.Field.Category.Equals("Nomenclature"); break; } if (include) { finalCriteria.Add(c); } } // now ensure the id criteria is set... FieldDescriptor fd = null; switch (elemType) { case "Site": fd = SupportService.FieldDescriptors.FirstOrDefault((candidate) => { return candidate.Category.Equals("Site", StringComparison.CurrentCultureIgnoreCase) && candidate.DisplayName.Equals("site identifier", StringComparison.CurrentCultureIgnoreCase); }); break; case "SiteVisit": fd = SupportService.FieldDescriptors.FirstOrDefault((candidate) => { return candidate.Category.Equals("SiteVisit", StringComparison.CurrentCultureIgnoreCase) && candidate.DisplayName.Equals("Visit identifier", StringComparison.CurrentCultureIgnoreCase); }); break; case "Material": fd = SupportService.FieldDescriptors.FirstOrDefault((candidate) => { return candidate.Category.Equals("Material", StringComparison.CurrentCultureIgnoreCase) && candidate.DisplayName.Equals("Material identifier", StringComparison.CurrentCultureIgnoreCase); }); break; } if (fd != null) { var qc = new QueryCriteria { Field = fd, Output = true, Criteria = string.Format("in ({0})", idList.Join(",")) }; finalCriteria.Add(qc); var service = new SupportService(User); return service.ExecuteQuery(finalCriteria, false); } return null; }