public TopProductDto[] GetTop5Products()
{
return(db.Query <TopProductDto>(@"select top 5
p.Name as ProductName, sum(oi.Qty*oi.UnitPrice) as Value
from Products as p
inner join OrderItems as oi on p.Id=oi.ProductId
group by p.Name
order by sum(oi.Qty*oi.UnitPrice) desc
").ToArray());
}
private static void MigrateDataIdsToUdis(UmbracoDatabase database)
{
string sql = @"SELECT cmsPropertyData.id, cmsPropertyData.contentNodeId, cmsPropertyType.alias, dataNvarchar, dataNtext, dataInt, cmsDocument.*
FROM cmsPropertyData
JOIN cmsPropertyType ON cmsPropertyType.id = cmsPropertyData.propertytypeid
JOIN cmsDataType ON cmsDataType.nodeId = cmsPropertyType.dataTypeId
JOIN cmsContentVersion ON cmsContentVersion.VersionId = cmsPropertyData.versionId
JOIN umbracoNode ON umbracoNode.id = cmsContentVersion.ContentId
JOIN cmsDocument ON cmsDocument.nodeId = umbracoNode.id
WHERE cmsDataType.propertyEditorAlias IN ('Umbraco.ContentPicker2')
AND (dataNvarchar IS NOT NULL OR dataInt IS NOT NULL)
AND (cmsDocument.published=1 OR cmsDocument.newest=1 OR cmsDocument.updateDate > (SELECT updateDate FROM cmsDocument AS innerDoc WHERE innerDoc.nodeId = cmsDocument.nodeId AND innerDoc.published=1 AND newest=1))
ORDER BY contentNodeId, cmsDataType.propertyEditorAlias";
var contentPickerDataToMigrate = database.Query <Row>(sql).ToList();
if (contentPickerDataToMigrate.Any())
{
foreach (var propertyData in contentPickerDataToMigrate)
{
int[] ids;
if (propertyData.dataInt != null)
{
ids = new[] { propertyData.dataInt.Value };
}
else if (propertyData.dataNvarchar != null && !propertyData.dataNvarchar.StartsWith("umb://"))
{
ids = propertyData.dataNvarchar.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries).Select(int.Parse).ToArray();
}
else
{
LogHelper.Info(typeof(ContentPickerIdToUdiMigrator), () => $"MigrateIdsToUdis (node id: {propertyData.contentNodeId}) skipping property {propertyData.alias}");
continue;
}
string csv = string.Join(",", ids);
Guid[] uniqueIds = null;
string uniqueIdsCsv = string.Empty;
if (ids.Any())
{
uniqueIds = database.Query <Guid>($"SELECT uniqueId FROM umbracoNode WHERE id IN ({csv})").ToArray();
uniqueIdsCsv = string.Join(",", uniqueIds.Select(id => $"umb://document/{id:N}"));
}
LogHelper.Info(typeof(ContentPickerIdToUdiMigrator), () => $"MigrateIdsToUdis (node id: {propertyData.contentNodeId}) converting property {propertyData.alias} from {csv} to {uniqueIdsCsv}");
database.Execute("UPDATE cmsPropertyData SET dataInt=NULL, dataNvarchar=@0 WHERE id=@1", uniqueIdsCsv, propertyData.id);
}
LogHelper.Info(typeof(ContentPickerIdToUdiMigrator), () => $"MigrateIdsToUdis: migrated Umbraco.ContentPicker2 datatypes.");
}
}
/// <summary>
/// Gets the most used properties from the database
/// </summary>
/// <param name="count">
/// How many properties to return (defaults at 5)
/// </param>
/// <returns>
/// A list of top properties that have not been removed from the favorites
/// </returns>
public IEnumerable <FavoriteContentModel> GetTopProperties(int userId, int count = 5)
{
var topProperties = new List <FavoriteContentModel>();
var favoritesByUseCount = db.Query <FavoriteContentModel>("SELECT * FROM [FavoriteContent] WHERE IsFavorite IS NULL AND UserId =" + userId + " OR IsFavorite !='False' ORDER BY UseCount DESC");
if (favoritesByUseCount != null && favoritesByUseCount.Any())
{
topProperties.AddRange(favoritesByUseCount.Take(count));
}
return(topProperties);
}
private static List <FormStorageFormModel> SetupFormList()
{
List <FormStorageFormModel> formList = null;
string querySQL = "SELECT * FROM FormStorageForms";
try
{
formList = DatabaseConnection.Query <FormStorageFormModel>(querySQL).ToList();
}
catch (Exception ex)
{
Log.Error("Unable to query FormStorageForms table : " + ex.Message);
}
return(formList);
}
public string GetPermissions(int accountId)
{
var sql = new Sql("SELECT * FROM GraphQL_AccountSettings WHERE AccountId=@0", accountId);
var settings = _database.Query <AccountSettings>(sql);
if (settings != null)
{
var accountPermissions = new List <AccountPermission>();
foreach (var permission in settings)
{
var accountPermission = new AccountPermission();
accountPermission.Notes = permission.Notes;
accountPermission.Permission = permission.Permission.ToString();
accountPermission.PropertyAlias = permission.PropertyTypeAlias;
accountPermission.DoctypeAlias = permission.DocTypeAlias;
accountPermission.IsBuiltInProperty = permission.IsBuiltInProperty;
accountPermissions.Add(accountPermission);
}
var results = JsonConvert.SerializeObject(accountPermissions);
return(results);
}
return(null);
}
/// <summary>
/// deletes inventory list on new upload
/// </summary>
public int DeleteOldCompanyInvetoryItems()
{
//get the list of items to delete from the CompanyInventory db table
const string sql = @"SELECT *
FROM CompanyInventory sci
LEFT JOIN CompanyInvetoryAudit scia ON sci.AuditId = scia.id";
//delete all items form the CompanyInventory db table
const string sqlDelete = @"DELETE FROM CompanyInventory WHERE Id>0";
int recordesToDelete = db.Query <int>(new Sql(sql)).Count();
try
{
if (recordesToDelete > 0)
{
db.Execute(new Sql(sqlDelete));
}
}
catch (Exception ex)
{
return(-1);
}
return(recordesToDelete);
}
/// <summary>
/// Returns a collection of installed languages.
/// <remarks>This method hits the database but the results are cached after the first run.</remarks>
/// </summary>
/// <returns>
/// The <see cref="IEnumerable{Language}"/>.
/// </returns>
public static IEnumerable <Language> GetInstalledLanguages()
{
const string Key = "installed.languages";
List <Language> languages = (List <Language>)SiteCache.GetItem(Key);
if (languages == null)
{
languages = new List <Language>();
// Return a list of all languages in use.
UmbracoDatabase db = ApplicationContext.Current.DatabaseContext.Database;
languages.AddRange(
db.Query <string>("SELECT [languageISOCode] FROM [umbracoLanguage]")
.Select(CultureInfo.GetCultureInfo)
.Select(x => new Language(x.Name)
{
IsoCode = x.Name
}));
SiteCache.AddItem(Key, languages);
}
return(languages);
}
public void Can_Populate_typeFieldData_Into_merchTypeField()
{
//// Arrange
const int expected = 32;
//// Act
_creation.InitializeBaseData("merchTypeField");
//// Assert
var dtos = _database.Query <TypeFieldDto>("SELECT * FROM merchTypeField");
var count = dtos.Count();
Assert.AreEqual(expected, count);
}
private static void MigrateVortoDataIdsToUdis(UmbracoDatabase database)
{
string sql = @"SELECT cmsPropertyData.id, cmsPropertyData.contentNodeId, cmsPropertyType.alias, dataNvarchar, dataNtext, dataInt, cmsDocument.*
FROM cmsPropertyData
JOIN cmsPropertyType ON cmsPropertyType.id = cmsPropertyData.propertytypeid
JOIN cmsDataType ON cmsDataType.nodeId = cmsPropertyType.dataTypeId
JOIN cmsDataTypePreValues ON cmsDataTypePreValues.datatypeNodeId = cmsDataType.nodeId AND cmsDataTypePreValues.alias = 'dataType'
JOIN cmsContentVersion ON cmsContentVersion.VersionId = cmsPropertyData.versionId
JOIN umbracoNode ON umbracoNode.id = cmsContentVersion.ContentId
JOIN cmsDocument ON cmsDocument.nodeId = umbracoNode.id
WHERE cmsDataType.propertyEditorAlias IN ('Our.Umbraco.Vorto')
AND cmsDataTypePreValues.value LIKE '%""propertyEditorAlias"": ""Umbraco.MultiUrlPicker""%'
AND (dataNtext IS NOT NULL) AND (dataNtext LIKE '%\""id\"":%')
AND(cmsDocument.published = 1 OR cmsDocument.newest = 1 OR cmsDocument.updateDate > (SELECT updateDate FROM cmsDocument AS innerDoc WHERE innerDoc.nodeId = cmsDocument.nodeId AND innerDoc.published = 1 AND newest = 1))
ORDER BY contentNodeId, cmsDataType.propertyEditorAlias";
var vortoUrlPickerDataToMigrate = database.Query <Row>(sql).ToList();
if (vortoUrlPickerDataToMigrate.Any())
{
foreach (var propertyData in vortoUrlPickerDataToMigrate)
{
string udiValue;
if (!string.IsNullOrEmpty(propertyData.dataNtext))
{
var vortoValue = JsonConvert.DeserializeObject <Our.Umbraco.Vorto.Models.VortoValue>(propertyData.dataNtext);
var udiValues = new Dictionary <string, object>();
foreach (var value in vortoValue.Values)
{
var valueMultiUrls = BuildMultiUrlLinks(value.Value.ToString());
udiValues[value.Key] = ToDataValue(valueMultiUrls);
}
vortoValue.Values = udiValues;
udiValue = ToDataValue(vortoValue);
}
else
{
LogHelper.Info(typeof(MultiUrlPickerIdToUdiMigrator), () => $"MigrateIdsToUdis (node id: {propertyData.contentNodeId}) skipping property {propertyData.alias} - null dataNtext");
continue;
}
LogHelper.Info(typeof(MultiUrlPickerIdToUdiMigrator), () => $"MigrateIdsToUdis (node id: {propertyData.contentNodeId}) converting property {propertyData.alias} from {propertyData.dataNtext} to {udiValue}");
database.Execute("UPDATE cmsPropertyData SET dataNtext=@0 WHERE id=@1", udiValue, propertyData.id);
}
LogHelper.Info(typeof(MultiUrlPickerIdToUdiMigrator), () => $"MigrateIdsToUdis: migrated Our.Umbraco.Vorto datatypes containing MultiUrlPicker.");
}
}
private static void MigrateDataIdsToUdis(UmbracoDatabase database)
{
string sql = @"SELECT cmsPropertyData.id, cmsPropertyData.contentNodeId, cmsPropertyType.alias, dataNvarchar, dataNtext, dataInt, cmsDocument.*
FROM cmsPropertyData
JOIN cmsPropertyType ON cmsPropertyType.id = cmsPropertyData.propertytypeid
JOIN cmsDataType ON cmsDataType.nodeId = cmsPropertyType.dataTypeId
JOIN cmsContentVersion ON cmsContentVersion.VersionId = cmsPropertyData.versionId
JOIN umbracoNode ON umbracoNode.id = cmsContentVersion.ContentId
JOIN cmsDocument ON cmsDocument.nodeId = umbracoNode.id
WHERE cmsDataType.propertyEditorAlias IN ('Umbraco.MultiUrlPicker')
AND (dataNtext IS NOT NULL) AND (dataNtext LIKE '%""id"":%')
AND (cmsDocument.published=1 OR cmsDocument.newest=1 OR cmsDocument.updateDate > (SELECT updateDate FROM cmsDocument AS innerDoc WHERE innerDoc.nodeId = cmsDocument.nodeId AND innerDoc.published=1 AND newest=1))
ORDER BY contentNodeId, cmsDataType.propertyEditorAlias";
var urlPickerDataToMigrate = database.Query <Row>(sql).ToList();
if (urlPickerDataToMigrate.Any())
{
foreach (var propertyData in urlPickerDataToMigrate)
{
IEnumerable <Umbraco.Web.Models.Link> multiUrls;
if (!string.IsNullOrEmpty(propertyData.dataNtext))
{
multiUrls = BuildMultiUrlLinks(propertyData.dataNtext);
}
else
{
LogHelper.Info(typeof(MultiUrlPickerIdToUdiMigrator), () => $"MigrateIdsToUdis (node id: {propertyData.contentNodeId}) skipping property {propertyData.alias} - null dataNtext");
continue;
}
var linksValue = ToDataValue(multiUrls);
LogHelper.Info(typeof(MultiUrlPickerIdToUdiMigrator), () => $"MigrateIdsToUdis (node id: {propertyData.contentNodeId}) converting property {propertyData.alias} from {propertyData.dataNtext} to {linksValue}");
database.Execute("UPDATE cmsPropertyData SET dataNtext=@0 WHERE id=@1", linksValue, propertyData.id);
}
LogHelper.Info(typeof(MultiUrlPickerIdToUdiMigrator), () => $"MigrateIdsToUdis: migrated Umbraco.MultiUrlPicker datatypes.");
}
}
private static void MigrateVortoArchetypeDataIdsToUdis(UmbracoDatabase database)
{
// Find content picker datatypes
string contentPickerSql = @"SELECT umbracoNode.uniqueID
FROM cmsDataType
JOIN umbracoNode ON umbracoNode.id = cmsDataType.nodeId
WHERE cmsDataType.propertyEditorAlias = 'Umbraco.ContentPicker2'";
var contentPickers = database.Query <Guid>(contentPickerSql).ToList();
foreach (var contentPicker in contentPickers)
{
// Find archetypes using content pickers
string archetypeSql = $@"SELECT umbracoNode.uniqueID, cmsDataTypePreValues.value
FROM cmsPropertyType
JOIN cmsDataType ON cmsDataType.nodeId = cmsPropertyType.dataTypeId
JOIN cmsDataTypePreValues ON cmsDataTypePreValues.datatypeNodeId = cmsDataType.nodeId AND cmsDataTypePreValues.alias = 'archetypeConfig'
JOIN umbracoNode ON umbracoNode.id = cmsPropertyType.dataTypeId
WHERE cmsDataType.propertyEditorAlias IN ('Imulus.Archetype')
AND cmsDataTypePreValues.value LIKE '%""dataTypeGuid"": ""{contentPicker}""%'";
var archetypes = database.Query <ArchetypeConfigRow>(archetypeSql).ToList();
foreach (var archetype in archetypes)
{
// Find Vorto properties of archetypes using content pickers
var sql = $@"SELECT cmsPropertyData.id, cmsPropertyData.contentNodeId, cmsPropertyType.alias, dataNvarchar, dataNtext, dataInt, cmsDocument.*
FROM cmsPropertyData
JOIN cmsPropertyType ON cmsPropertyType.id = cmsPropertyData.propertytypeid
JOIN cmsDataType ON cmsDataType.nodeId = cmsPropertyType.dataTypeId
JOIN cmsDataTypePreValues ON cmsDataTypePreValues.datatypeNodeId = cmsDataType.nodeId AND cmsDataTypePreValues.alias = 'dataType'
JOIN cmsContentVersion ON cmsContentVersion.VersionId = cmsPropertyData.versionId
JOIN umbracoNode ON umbracoNode.id = cmsContentVersion.ContentId
JOIN cmsDocument ON cmsDocument.nodeId = umbracoNode.id
WHERE cmsDataType.propertyEditorAlias IN ('Our.Umbraco.Vorto')
AND cmsDataTypePreValues.value LIKE '%""propertyEditorAlias"": ""Imulus.Archetype""%'
AND cmsDataTypePreValues.value LIKE '%""guid"": ""{archetype.UniqueID}""%'
AND(dataNtext IS NOT NULL)
AND(cmsDocument.published = 1 OR cmsDocument.newest = 1 OR cmsDocument.updateDate > (SELECT updateDate FROM cmsDocument AS innerDoc WHERE innerDoc.nodeId = cmsDocument.nodeId AND innerDoc.published = 1 AND newest = 1))
ORDER BY contentNodeId, cmsDataType.propertyEditorAlias";
var vortoDataToMigrate = database.Query <Row>(sql).ToList();
var config = JsonConvert.DeserializeObject <Archetype.Models.ArchetypePreValue>(archetype.Value);
var propertyAliases = config.Fieldsets.SelectMany(fieldset => fieldset.Properties)
.Where(property => property.DataTypeGuid == contentPicker)
.Select(property => property.Alias);
if (vortoDataToMigrate.Any())
{
foreach (var propertyData in vortoDataToMigrate)
{
string udiValue;
if (!string.IsNullOrEmpty(propertyData.dataNtext))
{
// Vorto multilingual values
var vortoValue = JsonConvert.DeserializeObject <Our.Umbraco.Vorto.Models.VortoValue>(propertyData.dataNtext);
var udiValues = new Dictionary <string, object>();
foreach (var value in vortoValue.Values)
{
// Archetype fieldsets
var archetypeValue = JsonConvert.DeserializeObject <Archetype.Models.ArchetypeModel>(value.Value.ToString());
var fieldsets = archetypeValue.Fieldsets
.Where(fieldset => fieldset.Properties.Any(property =>
propertyAliases.Contains(property.Alias)));
foreach (var fieldset in fieldsets)
{
// Properties using content picker
var properties = fieldset.Properties.Where(property =>
propertyAliases.Contains(property.Alias));
foreach (var property in properties)
{
var intValue = property.GetValue <int>();
if (intValue > 0)
{
var uniqueId = database.FirstOrDefault <Guid>(
$"SELECT uniqueId FROM umbracoNode WHERE id = @0", intValue);
property.Value = $"umb://document/{uniqueId:N}";
}
else
{
property.Value = null;
}
}
}
udiValues[value.Key] = ToDataValue(archetypeValue);
}
vortoValue.Values = udiValues;
udiValue = ToDataValue(vortoValue);
}
else
{
LogHelper.Info(typeof(ContentPickerIdToUdiMigrator), () => $"MigrateIdsToUdis (node id: {propertyData.contentNodeId}) skipping property {propertyData.alias} - null dataNtext");
continue;
}
LogHelper.Info(typeof(ContentPickerIdToUdiMigrator), () => $"MigrateIdsToUdis (node id: {propertyData.contentNodeId}) converting property {propertyData.alias} from {propertyData.dataNtext} to {udiValue}");
database.Execute("UPDATE cmsPropertyData SET dataNtext=@0 WHERE id=@1", udiValue, propertyData.id);
}
}
}
}
}
private static void MigrateNestedContentDataIdsToUdis(UmbracoDatabase database)
{
//SELECT documents using media picker datatypes
var docTypeSql = @"SELECT cmsContentType.alias as docTypeAlias, cmsPropertyType.alias as propertyAlias FROM cmsDataType
JOIN cmsPropertyType ON cmsDataType.nodeId = cmsPropertyType.dataTypeId
JOIN cmsContentType ON cmsContentType.nodeId = cmsPropertyType.contentTypeId
WHERE cmsDataType.propertyEditorAlias = 'Umbraco.MediaPicker2'";
var docTypes = database.Query <DoctypePropertyRow>(docTypeSql).ToList();
foreach (var docType in docTypes)
{
// SELECT NestedContent using selected doctypes by using their doctype alias an property alias
var sql = $@"SELECT cmsPropertyData.id, cmsPropertyData.contentNodeId, cmsPropertyType.alias, dataNvarchar, dataNtext, dataInt, cmsDocument.* --, cmsDataTypePreValues.*
FROM cmsPropertyData
JOIN cmsPropertyType ON cmsPropertyType.id = cmsPropertyData.propertytypeid
JOIN cmsDataType ON cmsDataType.nodeId = cmsPropertyType.dataTypeId
JOIN cmsDataTypePreValues ON cmsDataTypePreValues.datatypeNodeId = cmsDataType.nodeId AND cmsDataTypePreValues.alias = 'contentTypes'
JOIN cmsContentVersion ON cmsContentVersion.VersionId = cmsPropertyData.versionId
JOIN umbracoNode ON umbracoNode.id = cmsContentVersion.ContentId
JOIN cmsDocument ON cmsDocument.nodeId = umbracoNode.id
WHERE cmsDataType.propertyEditorAlias IN ('Umbraco.NestedContent', 'Our.Umbraco.NestedContent')
AND cmsDataTypePreValues.value LIKE '%""ncAlias"": ""{docType.DocTypeAlias}""%'
AND(dataNtext IS NOT NULL AND dataNtext LIKE '%""{docType.PropertyAlias}"":""%')
AND(cmsDocument.published = 1 OR cmsDocument.newest = 1 OR cmsDocument.updateDate > (SELECT updateDate FROM cmsDocument AS innerDoc WHERE innerDoc.nodeId = cmsDocument.nodeId AND innerDoc.published = 1 AND newest = 1))
ORDER BY contentNodeId, cmsDataType.propertyEditorAlias";
var dataToMigrate = database.Query <Row>(sql).ToList();
foreach (var propertyData in dataToMigrate)
{
if (string.IsNullOrEmpty(propertyData.dataNtext))
{
LogHelper.Info(typeof(MediaPickerIdToUdiMigrator), () => $"MigrateKeys (node id: {propertyData.contentNodeId}) skipping property {propertyData.alias} - null dataNtext");
continue;
}
var ncArray = JArray.Parse(propertyData.dataNtext);
var udiArray = new JArray();
foreach (var ncToken in ncArray)
{
var nc = (JObject)ncToken;
if (nc.HasValue(docType.PropertyAlias))
{
var ncValue = nc.Value <string>(docType.PropertyAlias);
var ids = ncValue.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries).Where(x => !x.StartsWith("umb://")).Select(int.Parse).ToArray();
string csv = string.Join(",", ids);
Guid[] uniqueIds = null;
string uniqueIdsCsv = string.Empty;
if (ids.Any())
{
uniqueIds = database.Query <Guid>($"SELECT uniqueId FROM umbracoNode WHERE id IN ({csv})").ToArray();
uniqueIdsCsv = string.Join(",", uniqueIds.Select(id => $"umb://media/{id:N}"));
}
nc[docType.PropertyAlias] = uniqueIdsCsv;
}
udiArray.Add(nc);
}
var udiNestedContent = JsonConvert.SerializeObject(udiArray);
LogHelper.Info(typeof(MediaPickerIdToUdiMigrator), () => $"MigrateKeys (node id: {propertyData.contentNodeId}) converting property {propertyData.alias} from {propertyData.dataNtext} to {udiNestedContent}");
database.Execute("UPDATE cmsPropertyData SET dataNtext=@0 WHERE id=@1", udiNestedContent, propertyData.id);
}
}
}
private List <dynamic> GenerateSubmissionData(string alias, out string fieldNames)
{
List <dynamic> resultList = new List <dynamic>();
fieldNames = "";
int formID = FormSchema.GetFormIDFromAlias(alias, false);
if (formID > -1)
{
List <FormStorageSubmissionModel> fetchedSubmissionRecords = new List <FormStorageSubmissionModel>();
string querySQL = "SELECT * FROM FormStorageSubmissions WHERE formID = @formID";
string filterValue = Request.Params["period"];
DateTime filterDate = DateTime.Now.Date;
if (!string.IsNullOrEmpty(filterValue))
{
int filterDays = 0;
int.TryParse(filterValue, out filterDays);
filterDate = filterDate.AddDays(filterDays * -1);
}
try
{
if (!string.IsNullOrEmpty(filterValue))
{
querySQL += " AND datetime >= @datetime";
fetchedSubmissionRecords = DatabaseConnection.Query <FormStorageSubmissionModel>(querySQL, new { formID = formID, datetime = filterDate }).ToList();
}
else
{
fetchedSubmissionRecords = DatabaseConnection.Query <FormStorageSubmissionModel>(querySQL, new { formID = formID }).ToList();
}
}
catch (Exception ex)
{
Log.Error("Unable to query FormStorageSubmissions table : " + ex.Message);
return(resultList);
}
// Set-up filters
List <string> fieldList = null;
List <string> filterFieldList = new List <string>();
fieldNames = WebConfigurationManager.AppSettings["FormStorage:" + alias];
if (!string.IsNullOrEmpty(fieldNames))
{
fieldList = new List <string>(fieldNames.Split(','));
fieldList.Add("IP");
fieldList.Add("datetime");
}
bool filterApplied = false;
if (fieldList != null)
{
foreach (string fieldName in fieldList)
{
if (Request.Params[fieldName] != null)
{
filterFieldList.Add(fieldName);
filterApplied = true;
}
}
}
foreach (FormStorageSubmissionModel currentSubmissionRecord in fetchedSubmissionRecords)
{
List <FormStorageEntryModel> fetchedEntryRecords = new List <FormStorageEntryModel>();
querySQL = "SELECT * FROM FormStorageEntries WHERE submissionID = @submissionID";
try
{
fetchedEntryRecords = DatabaseConnection.Query <FormStorageEntryModel>(querySQL, new { submissionID = currentSubmissionRecord.SubmissionID }).ToList();
}
catch (Exception ex)
{
Log.Error("Unable to query FormStorageEntries table : " + ex.Message);
return(resultList);
}
var currentRecord = new ExpandoObject() as IDictionary <string, Object>;
currentRecord.Add("submissionID", currentSubmissionRecord.SubmissionID);
currentRecord.Add("datetime", currentSubmissionRecord.Datetime);
currentRecord.Add("IP", currentSubmissionRecord.IP);
foreach (FormStorageEntryModel formStorageEntry in fetchedEntryRecords)
{
currentRecord.Add(formStorageEntry.FieldAlias, formStorageEntry.Value);
}
// Apply filters...
bool filteredOut = false;
if (filterApplied)
{
foreach (string fieldName in filterFieldList)
{
if (((IDictionary <string, Object>)currentRecord).ContainsKey(fieldName))
{
filterValue = Request.Params[fieldName];
if (!currentRecord[fieldName].ToString().ToUpper().Contains(filterValue.ToUpper()))
{
filteredOut = true;
break;
}
}
}
}
if (!filteredOut)
{
resultList.Add(currentRecord);
}
}
resultList = HandleListSorting(resultList);
}
return(resultList);
}
public IEnumerable <GroupMember> GetAll()
{
return(_db.Query <GroupMember>("SELECT * FROM GroupMember"));
}
public IEnumerable <Status> GetStatuses()
{
var statuses = _db.Query <StatusPoco>("SELECT * FROM Statuses");
return(statuses.Select(Mapper.Map <StatusPoco, Status>));
}
public IEnumerable <Restaurant> GetActiveRestaurants()
{
var restaurants = _db.Query <RestaurantPoco>("SELECT * FROM Restaurants WHERE Active = 1");
return(restaurants.Select(r => _mapper.MapToDomain(r)));
}