public static List <CustomField> GetCustomFields(List <int> calIds, bool includeSample) { if (includeSample) { var fieldDefCalendarId = FieldDefCalendarId(); if (fieldDefCalendarId != null) { calIds.Add(fieldDefCalendarId.Value); } } using (var db = new Storm11Entities()) { var cfs = db.Database.SqlQuery <CustomFieldQueryResult>($@" select ET.EventTypeID as eventTypeId , ET.Facets.value('(/DisplayName/text())[1]', 'nvarchar(MAX)') as eventTypeName , FD.FieldType as _fieldType , FD.Facets as _fdFacets from EventTypes ET inner join EventTypeFieldDefs ETFD on ETFD.EventTypeID = ET.EventTypeID inner join FieldDefs FD on FD.FieldDefID = ETFD.FieldDefID where ET.EventTypeFlags = 0 AND ET.Unlive = 0 AND FD.Unlive = 0 AND FD.OwnerCalendarID IN ({string.Join(",", calIds)}) order by ET.EventTypeID ").ToList().Select(qr => ParseFacets(qr, db)).ToList(); return(cfs); } }
private static CustomField ParseFacets(CustomFieldQueryResult qr, Storm11Entities db) { int?IntCFValue(IEnumerable <XElement> xElements, int id) { return(int.TryParse(xElements.FirstOrDefault(cf => (int)cf.Attribute("id") == id)?.Value, out var i) ? i : (int?)null); } decimal?DecimalCFValue(IEnumerable <XElement> xElements, int id) { return(decimal.TryParse(xElements.FirstOrDefault(cf => (int)cf.Attribute("id") == id)?.Value, out var d) ? d : (decimal?)null); } bool BoolCFValue(IEnumerable <XElement> xElements, int id) { return(bool.TryParse(xElements.FirstOrDefault(cf => (int)cf.Attribute("id") == id)?.Value, out var b) && b); } var facets = XElement.Parse($"<r>{qr._fdFacets}</r>"); var cfs = facets.Elements("CF").ToList(); var allowedValues = cfs.Where(cf => (int)cf.Attribute("id") == -1).Select(cf => new AllowedValue(cf.Value)).ToList(); var assetDefId = IntCFValue(cfs, -11); if (assetDefId.HasValue) { allowedValues.AddRange(db.Assets.Where(a => a.AssetDefID == assetDefId.Value).ToList().Select(a => new AllowedValue { key = a.AssetID.ToString(), value = a.DisplayName })); } var result = new CustomField { eventTypeName = qr.eventTypeName, eventTypeId = qr.eventTypeId, fieldType = (FieldType)qr._fieldType, description = facets.Element("Description")?.Value, displayName = cfs.FirstOrDefault(cf => (int)cf.Attribute("id") == -3)?.Value, _defaultValue = cfs.FirstOrDefault(cf => (int)cf.Attribute("id") == -2)?.Value, minChars = IntCFValue(cfs, -4), maxChars = IntCFValue(cfs, -5), minValue = DecimalCFValue(cfs, -6), maxValue = DecimalCFValue(cfs, -7), required = BoolCFValue(cfs, -8), multipleValues = BoolCFValue(cfs, -9), allowedValues = allowedValues.Count > 0 ? allowedValues : null }; return(result); }
public static List <CalendarsOwned> GetOwnedCalendars(string email) { using (var db = new Storm11Entities()) { return(db.Database.SqlQuery <CalendarsOwned>(@" SELECT CALS.CalendarID, CALS.DisplayName AS CalendarDisplayName, TempestViewSettings.value('(/EditorConfig/ShowPredefined/text())[1]', 'varchar(5)') AS _showPredefined FROM [Storm11].[dbo].[Calendars] CALS INNER JOIN [Storm11].[dbo].[Identities] IDS ON IDS.IdentityID = CALS.OwnerIdentityID WHERE IDS.EmailAddress = @email AND CALS.Unlive = 0", new SqlParameter("@email", email)).ToList()); } }