public IEnumerable <TEntityType> GetAll()
{
var query = new Sql()
.Select("*")
.From <TEntityType>(_sqlProvider);
return(_database.Fetch <TEntityType>(query));
}
public IEnumerable <LatchOperation> GetAllOperations()
{
var query = Sql.Builder
.Select("*")
.From("LatchOperation");
var operations = db.Fetch <LatchOperation>(query);
return(operations);
}
public List <Product> GetByCategoryPaged(int categoryId, int page, int pageSize)
{
var sql = new Sql()
.Select("*")
.From <Product>(ctx.SqlSyntax)
.Where <Product>(p => p.CategoryId == categoryId, ctx.SqlSyntax)
.OrderBy <Product>(p => p.Id, ctx.SqlSyntax);
return(db.Fetch <Product>(page, pageSize, sql));
}
/// <summary>
/// Get Optimized Images
/// </summary>
/// <returns>IEnumerable of Media</returns>
public IEnumerable <Media> GetOptimizedItems()
{
var query = new Sql("SELECT ImageId FROM TinifierResponseHistory WHERE IsOptimized = 'true'");
var historyIds = _database.Fetch <int>(query);
var mediaItems = _mediaService.
GetMediaOfMediaType(_contentTypeService.GetMediaType(PackageConstants.ImageAlias).Id).
Where(item => historyIds.Contains(item.Id));
return(mediaItems.Select(item => item as Media).ToList());
}
public void Run()
{
var query = new Sql()
.Select("*")
.From(SchedulerConstants.Database.ScheduleTable)
.Where("Disabled = 0");
var schedules = _database.Fetch <Schedule>(query);
var tasks = schedules.Select(RunScheduledAsync).ToArray();
Task.WaitAll(tasks);
}
public IEnumerable <Member> GetMembers(int structuralGroupId, string memberType)
{
var groupMembers = _db.Fetch <GroupMember>("SELECT * FROM GroupMember WHERE StructuralGroupId = @0", structuralGroupId);
var members = groupMembers.Select(g => (Member)_memberService.GetById(g.MemberId));
if (string.IsNullOrEmpty(memberType))
{
return(members);
}
else
{
return(members.Where(m => m.ContentType.Name == memberType));
}
}
public IEnumerable <TinyPNGResponseHistory> GetAll()
{
var select = new Sql("SELECT * FROM TinifierResponseHistory");
var histories = _database.Fetch <TinyPNGResponseHistory>(select);
return(histories);
}
/// <summary>
/// Loads the data value from the database.
/// </summary>
protected internal virtual void LoadValueFromDatabase()
{
var sql = new Sql();
sql.Select("*")
.From <PropertyDataDto>()
.InnerJoin <PropertyTypeDto>()
.On <PropertyTypeDto, PropertyDataDto>(x => x.Id, y => y.PropertyTypeId)
.InnerJoin <DataTypeDto>()
.On <DataTypeDto, PropertyTypeDto>(x => x.DataTypeId, y => y.DataTypeId)
.Where <PropertyDataDto>(x => x.Id == _propertyId);
var dto = Database.Fetch <PropertyDataDto, PropertyTypeDto, DataTypeDto>(sql).FirstOrDefault();
if (dto != null && _dataType != null)
{
//the type stored in the cmsDataType table
var strDbType = dto.PropertyTypeDto.DataTypeDto.DbType;
//get the enum of the data type
var dbType = BaseDataType.GetDBType(strDbType);
//get the column name in the cmsPropertyData table that stores the correct information for the data type
var fieldName = BaseDataType.GetDataFieldName(dbType);
//get the value for the data type, if null, set it to an empty string
_value = dto.GetValue;
//now that we've set our value, we can update our BaseDataType object with the correct values from the db
//instead of making it query for itself. This is a peformance optimization enhancement.
_dataType.SetDataTypeProperties(fieldName, dbType);
}
}
public IEnumerable <TState> GetAll()
{
var query = new Sql("SELECT * FROM TinifierState");
var state = _database.Fetch <TState>(query);
return(state);
}
public IEnumerable <IdentityMemberLogin <int> > GetAll(int userId)
{
var sql = new Sql()
.Select("*")
.From <ExternalLoginDto>()
.Where <ExternalLoginDto>(dto => dto.UserId == userId);
var found = _db.Fetch <ExternalLoginDto>(sql);
return(found.Select(x => new IdentityMemberLogin <int>
{
LoginProvider = x.LoginProvider,
ProviderKey = x.ProviderKey,
UserId = x.UserId
}));
}
//public Category[] GetAll()
//{
// return db.Query<Category>("select * from Categories").ToArray();
//}
//public Category[] GetAll()
//{
// var sql = new Sql().Select("*")
// .From<Category>(ctx.SqlSyntax);
// return db.Query<Category>(sql).ToArray();
//}
public List <Category> GetAll()
{
var sql = new Sql().Select("*")
.From <Category>(ctx.SqlSyntax);
return(db.Fetch <Category>(sql));
}
public JsonResult <List <StatsInGameRelation> > GetStatsByGameId(int id)
{
UmbracoDatabase db = ApplicationContext.DatabaseContext.Database;
var records = db.Fetch <StatsInGameRelation>(";EXEC GetStatsByGameId @0", id);
return(Json(records));
}
private async Task ProcessDeliveries(string[] allDeliveries)
{
foreach (var delivery in allDeliveries)
{
var file = fileSystem.FileInfo.FromFileName(delivery);
var sql = new Sql()
.From <ChauffeurDeliveryTable>()
.Where <ChauffeurDeliveryTable>(t => t.Name == file.Name);
var entry = database.Fetch <ChauffeurDeliveryTable>(sql).FirstOrDefault();
if (entry != null && entry.SignedFor)
{
await Out.WriteLineFormattedAsync("'{0}' is already signed for, skipping it.", file.Name);
continue;
}
var tracking = await Deliver(file);
database.Save(tracking);
if (!tracking.SignedFor)
{
break;
}
}
}
public Page <OrderItem> GetOrderItems(int orderId, int page, int pageSize)
{
var countSql = new Sql().Select("*")
.From <OrderItem>(ctx.SqlSyntax)
.Where <OrderItem>(oi => oi.OrderId == orderId, ctx.SqlSyntax);
var totalCount = db.ExecuteScalar <long>(countSql);
var sql = new Sql().Select("OrderItems.*, Products.Name")
.From <OrderItem>(ctx.SqlSyntax)
.InnerJoin <Product>(ctx.SqlSyntax)
.On <OrderItem, Product>(ctx.SqlSyntax, oi => oi.ProductId, p => p.Id)
.Where <OrderItem>(oi => oi.OrderId == orderId, ctx.SqlSyntax)
.OrderBy <OrderItem>(oi => oi.ProductId, ctx.SqlSyntax);
var items = db.Fetch <OrderItem, Product, OrderItem>((oi, p) =>
{
oi.Product = p;
return(oi);
}, sql);
return(new Page <OrderItem>()
{
CurrentPage = page,
Items = items,
ItemsPerPage = pageSize,
TotalItems = totalCount,
TotalPages = (long)Math.Ceiling(totalCount / (double)pageSize)
});
}
public IEnumerable <Media> GetOptimizedItems()
{
var mediaList = new List <Media>();
var query = new Sql("SELECT ImageId FROM TinifierResponseHistory WHERE IsOptimized = 'true'");
var historyIds = _database.Fetch <int>(query);
var mediaItems = _mediaService.
GetMediaOfMediaType(_contentTypeService.GetMediaType("image").Id).
Where(item => historyIds.Contains(item.Id));
foreach (var item in mediaItems)
{
mediaList.Add(item as Media);
}
return(mediaList);
}
public int Count()
{
var query = new Sql("SELECT * FROM TinifierImagesStatistic");
var numberOfElements = _database.Fetch <TImageStatistic>(query);
return(numberOfElements.Count);
}
private static OrderData GetOrCreateOrderWithGuid(Guid orderId, UmbracoDatabase db)
{
var fetch = db.Fetch <OrderData>(new Sql().Select("*").From("uWebshopOrders").Where((OrderData o) => o.UniqueId == orderId)).FirstOrDefault() ?? new OrderData {
CreateDate = DateTime.Now
};
return(fetch);
}
public JsonResult <List <ComplaintReport> > GetMyComplaintReportOrg()
{
UmbracoDatabase db = ApplicationContext.DatabaseContext.Database;
var query = new Sql().Select("*").From("ComplaintReport").Where <ComplaintReport>(x => x.DealerId == 1);
var data = db.Fetch <ComplaintReport, Dealer>(query);
return(Json(data));
}
public JsonResult <List <Game> > GetAll()
{
UmbracoDatabase db = ApplicationContext.DatabaseContext.Database;
var result = db.Fetch <Game>(new Sql()
.Select("*")
.From("Games"));
return(Json(result));
}
public IReadOnlyList <MigrationRecord> GetAll()
{
if (!_database.TableExist(MigrationRecord.DefaultTableName))
{
return(new MigrationRecord[0]);
}
return(_database.Fetch <MigrationRecord>("where 1=1"));
}
public Order GetOrder(int id)
{
var order = _db.Fetch <OrderPoco, OrderedMealPoco, StatusPoco, RestaurantPoco, OrderPoco>(
new OrderRelator().MapIt,
"SELECT * FROM Orders"
+ " LEFT JOIN OrderedMeals ON OrderedMeals.OrderId = Orders.Id"
+ " LEFT JOIN Statuses ON Statuses.Id = Orders.StatusId"
+ " LEFT JOIN Restaurants ON Restaurants.Id = Orders.RestaurantId"
+ " WHERE Orders.Id = @0"
, id
).FirstOrDefault();
if (order == null)
{
throw new KeyNotFoundException("Order has not been found.");
}
return(_orderMapper.MapToDomain(order));
}
public AzureSearchReindexStatus ReIndexContent(string sessionId)
{
List <int> contentIds;
List <int> mediaIds;
List <int> memberIds;
using (var db = new UmbracoDatabase("umbracoDbDSN"))
{
contentIds = db.Fetch <int>(@"select distinct cmsContent.NodeId
from cmsContent, umbracoNode where
cmsContent.nodeId = umbracoNode.id and
umbracoNode.nodeObjectType = 'C66BA18E-EAF3-4CFF-8A22-41B16D66A972'");
mediaIds = db.Fetch <int>(@"select distinct cmsContent.NodeId
from cmsContent, umbracoNode where
cmsContent.nodeId = umbracoNode.id and
umbracoNode.nodeObjectType = 'B796F64C-1F99-4FFB-B886-4BF4BC011A9C'");
memberIds = db.Fetch <int>(@"select distinct cmsContent.NodeId
from cmsContent, umbracoNode where
cmsContent.nodeId = umbracoNode.id and
umbracoNode.nodeObjectType = '39EB0F98-B348-42A1-8662-E7EB18487560'");
}
var contentCount = contentIds.Count;
var path = Path.Combine(_path, @"App_Data\MoriyamaAzureSearch\" + sessionId);
EnsurePath(path);
System.IO.File.WriteAllText(Path.Combine(path, "content.json"), JsonConvert.SerializeObject(contentIds));
System.IO.File.WriteAllText(Path.Combine(path, "media.json"), JsonConvert.SerializeObject(mediaIds));
System.IO.File.WriteAllText(Path.Combine(path, "member.json"), JsonConvert.SerializeObject(memberIds));
return(new AzureSearchReindexStatus
{
SessionId = sessionId,
DocumentCount = contentCount,
Error = false,
Finished = false
});
}
private List <int> FetchIds(string type)
{
using (var db = new UmbracoDatabase("umbracoDbDSN"))
{
return(db.Fetch <int>($@"select distinct cmsContent.NodeId
from cmsContent, umbracoNode where
cmsContent.nodeId = umbracoNode.id and
umbracoNode.nodeObjectType = '{type}'"));
}
}
/// <summary>
/// Get the current workflow settings, or persist an empty instance if none exist
/// </summary>
/// <returns>A object of type <see cref="WorkflowSettingsPoco"/> representing the current settings</returns>
public WorkflowSettingsPoco GetSettings()
{
var wsp = new WorkflowSettingsPoco();
List <WorkflowSettingsPoco> settings = _database.Fetch <WorkflowSettingsPoco>(SqlQueries.GetSettings);
if (settings.Any())
{
wsp = settings.First();
}
else
{
_database.Insert(wsp);
}
if (string.IsNullOrEmpty(wsp.Email))
{
wsp.Email = UmbracoConfig.For.UmbracoSettings().Content.NotificationEmailAddress;
}
return(wsp);
}
/// <summary>
/// Gets all content type (doc type) aliases for content
/// </summary>
public IEnumerable <string> GetContentTypeAliases()
{
string sql = @"SELECT CT.alias FROM cmsContentType CT INNER JOIN umbracoNode N ON CT.nodeId = N.id WHERE N.nodeObjectType = @0 ORDER BY CT.alias";
Sql query = new Sql(sql, Constants.ObjectTypes.DocumentTypeGuid);
return(db.Fetch <string>(query));
}
public static SupportGroups GetByName(string name)
{
UmbracoDatabase db = Umbraco.Core.ApplicationContext.Current.DatabaseContext.Database;
List <SupportGroups> States = db.Fetch <SupportGroups>("SELECT * FROM CMSupportGroups WHERE statename = @0, name");
if (States.Count > 0)
{
return(States[0]);
}
else
{
return(null);
}
}
public List <int> GetAllIds()
{
List <int> contentIds;
using (var db = new UmbracoDatabase("umbracoDbDSN"))
{
contentIds = db.Fetch <int>(@"select distinct cmsContent.NodeId
from cmsContent, umbracoNode where
cmsContent.nodeId = umbracoNode.id and
umbracoNode.nodeObjectType = 'C66BA18E-EAF3-4CFF-8A22-41B16D66A972'");
}
return(contentIds);
}
/// <summary>
/// Adds the approval group nodes to the tree.
/// </summary>
/// <param name="nodes"></param>
/// <param name="queryStrings">The query strings.</param>
public void AddApprovalGroupsToTree(TreeNodeCollection nodes, FormDataCollection queryStrings)
{
UmbracoDatabase db = ApplicationContext.Current.DatabaseContext.Database;
List <UserGroupPoco> userGroups = db.Fetch <UserGroupPoco>(SqlHelpers.GroupsForTree).OrderBy(x => x.Name).ToList();
if (!userGroups.Any())
{
return;
}
foreach (UserGroupPoco group in userGroups)
{
nodes.Add(CreateTreeNode(group.GroupId.ToString(), "approvalGroups", queryStrings, group.Name, "icon-users", false, $"{EditGroupRoute}{group.GroupId}"));
}
}
private void AddEntityToChangedTable(EntityType type, string name)
{
var changedEntities = _database.Fetch <ChauffeurChangesTable>("SELECT * FROM Chauffeur_Changes");
var filteredChangedEntities = changedEntities.Where(c => c.EntityType == Convert.ToInt32(type) && c.Name == name).ToList();
if (filteredChangedEntities.Count == 0)
{
_database.Save(new ChauffeurChangesTable
{
EntityType = Convert.ToInt32(type),
Name = name,
ChangeDate = DateTime.Now,
ChangeType = Convert.ToInt32(ChangeType.SAVED)
});
}
}
public System.Web.Http.IHttpActionResult DeleteAllStatsByGameId(int id)
{
UmbracoDatabase db = ApplicationContext.DatabaseContext.Database;
var affectedRows = new SqlParameter("@intResult", System.Data.SqlDbType.Int);
affectedRows.Direction = System.Data.ParameterDirection.Output;
try
{
var records = db.Fetch <Stat>(";EXEC DeleteAllStatsByGameId @0, @1 OUTPUT", id, affectedRows);
}
catch (System.Exception exception)
{
return(Content(System.Net.HttpStatusCode.BadRequest, exception));
}
return(Ok(affectedRows.Value));
}