public override VMHumor[] Read(SxFilter filter)
{
var sb = new StringBuilder();
sb.Append(SxQueryProvider.GetSelectString(new string[] {
"dm.*",
filter.WithComments.HasValue && filter.WithComments == true?"(SELECT COUNT(1) FROM D_COMMENT AS dc WHERE dc.MaterialId=dm.Id AND dc.ModelCoreType=dm.ModelCoreType) AS CommentsCount":null,
"dmc.*",
"anu.*",
"dp.Id", "dp.Width", "dp.Height",
"dst.*"
}));
sb.Append(" FROM DV_MATERIAL AS dm ");
sb.Append(" LEFT JOIN D_MATERIAL_CATEGORY AS dmc ON dmc.Id = dm.CategoryId ");
sb.Append(" LEFT JOIN D_USER AS anu ON anu.Id = dm.UserId ");
sb.Append(" LEFT JOIN D_PICTURE AS dp ON dp.Id = dm.FrontPictureId ");
sb.Append(" LEFT JOIN D_SEO_TAGS AS dst ON (dst.ModelCoreType=dm.ModelCoreType AND dst.MaterialId=dm.Id AND dst.MaterialId IS NOT NULL) ");
object param = null;
var gws = GetMaterialsWhereString(filter, out param);
sb.Append(gws);
var defaultOrder = new SxOrderItem {
FieldName = "DateCreate", Direction = SortDirection.Desc
};
sb.Append(SxQueryProvider.GetOrderString(defaultOrder, filter.Order, new System.Collections.Generic.Dictionary <string, string> {
{ "DateCreate", "dm.[DateCreate]" },
{ "Title", "dm.[Title]" }
}));
sb.AppendFormat(" OFFSET {0} ROWS FETCH NEXT {1} ROWS ONLY", filter.PagerInfo.SkipCount, filter.PagerInfo.PageSize);
//count
var sbCount = new StringBuilder();
sbCount.Append("SELECT COUNT(1) FROM DV_MATERIAL AS dm ");
sbCount.Append(gws);
using (var connection = new SqlConnection(ConnectionString))
{
filter.PagerInfo.TotalItems = connection.Query <int>(sbCount.ToString(), param: param).SingleOrDefault();
var data = connection.Query <VMHumor, SxVMMaterialCategory, SxVMAppUser, SxVMPicture, SxVMSeoTags, VMHumor>(sb.ToString(), (m, c, u, p, st) =>
{
m.Category = c;
m.User = u;
m.FrontPicture = p;
m.SeoTags = st;
return(m);
}, param: param, splitOn: "Id").ToArray();
if (filter.WidthVideos == true && data.Any())
{
FillMaterialsVideo(connection, ref data);
}
return(data);
}
}
public override VMGame[] Read(SxFilter filter)
{
//0 - Show, 1 - ShowSteamAppsCount
var showSteamAppsCount = filter.AddintionalInfo?[1] == null ? (bool?)filter.AddintionalInfo?[1]: Convert.ToBoolean(filter.AddintionalInfo[1]);
var sb = new StringBuilder();
sb.Append(SxQueryProvider.GetSelectString(new string[] { "dg.*", showSteamAppsCount == true? "(SELECT COUNT(1) FROM D_GAME_STEAM_APP AS dgsa WHERE dgsa.GameId=dg.Id) AS SteamAppsCount" : null }));
sb.Append(" FROM D_GAME AS dg ");
object param = null;
var gws = GetGamesWhereString(filter, out param);
sb.Append(gws);
var defaultOrder = new SxOrderItem {
FieldName = "Title", Direction = SortDirection.Asc
};
sb.Append(SxQueryProvider.GetOrderString(defaultOrder, filter.Order));
//count
var sbCount = new StringBuilder();
sbCount.Append("SELECT COUNT(1) FROM D_GAME AS dg ");
sbCount.Append(gws);
using (var connection = new SqlConnection(ConnectionString))
{
filter.PagerInfo.TotalItems = connection.Query <int>(sbCount.ToString(), param: param).SingleOrDefault();
sb.AppendFormat(" OFFSET {0} ROWS FETCH NEXT {1} ROWS ONLY", filter.PagerInfo.TotalItems <= filter.PagerInfo.PageSize?0: filter.PagerInfo.SkipCount, filter.PagerInfo.PageSize);
var data = connection.Query <VMGame>(sb.ToString(), param: param);
return(data.ToArray());
}
}
public override VMSituation[] Read(SxFilter filter)
{
var sb = new StringBuilder();
sb.Append(SxQueryProvider.GetSelectString());
sb.Append(" FROM D_SITUATION AS ds");
object param;
var gws = GetSituationsWhereString(filter, out param);
sb.Append(gws);
var defaultOrder = new SxOrderItem {
FieldName = "ds.[Text]", Direction = SortDirection.Desc
};
sb.Append(SxQueryProvider.GetOrderString(defaultOrder, filter.Order));
sb.AppendFormat(" OFFSET {0} ROWS FETCH NEXT {1} ROWS ONLY", filter.PagerInfo.SkipCount, filter.PagerInfo.PageSize);
//count
var sbCount = new StringBuilder();
sbCount.Append("SELECT COUNT(1) FROM D_SITUATION AS ds");
sbCount.Append(gws);
using (var conn = new SqlConnection(ConnectionString))
{
var data = conn.Query <VMSituation>(sb.ToString(), param: param);
filter.PagerInfo.TotalItems = conn.Query <int>(sbCount.ToString(), param: param).SingleOrDefault();
return(data.ToArray());
}
}
public override VMSteamApp[] Read(SxFilter filter)
{
var sb = new StringBuilder();
sb.Append(SxQueryProvider.GetSelectString(new string[] { "dsa.*" }));
sb.Append(" FROM D_STEAM_APP AS dsa ");
object param = null;
var gws = GetSteamAppsWhereString(filter, out param);
sb.Append(gws);
var defaultOrder = new SxOrderItem {
FieldName = "Name", Direction = SortDirection.Asc
};
sb.Append(SxQueryProvider.GetOrderString(defaultOrder, filter.Order));
//count
var sbCount = new StringBuilder();
sbCount.Append("SELECT COUNT(1) FROM D_STEAM_APP AS dsa ");
sbCount.Append(gws);
using (var connection = new SqlConnection(ConnectionString))
{
filter.PagerInfo.TotalItems = connection.Query <int>(sbCount.ToString(), param: param).SingleOrDefault();
if (filter.PagerInfo.PageSize < int.MaxValue)
{
sb.AppendFormat(" OFFSET {0} ROWS FETCH NEXT {1} ROWS ONLY", filter.PagerInfo.TotalItems <= filter.PagerInfo.PageSize ? 0 : filter.PagerInfo.SkipCount, filter.PagerInfo.PageSize);
}
var data = connection.Query <VMSteamApp>(sb.ToString(), param: param).ToArray();
return(data);
}
}
public async Task <ActionResult> AppIndexLinked(VMSteamApp filterModel, SxOrderItem order, int page = 1, int pageSize = 10, int?gameId = null)
{
var gid = string.IsNullOrEmpty(Request.Form.Get("gameId"))?null: Request.Form.Get("gameId");
ViewBag.GameId = gid ?? gameId.ToString();
var filter = new SxFilter(page, pageSize)
{
Order = order != null && order.Direction != SortDirection.Unknown ? order : null, WhereExpressionObject = filterModel, AddintionalInfo = new object[] { null, ViewBag.GameId }
};
var viewModel = await Repo.ReadAsync(filter);
if (page > filter.PagerInfo.TotalPages)
{
page = 1;
}
if (page > 1 && !viewModel.Any())
{
return(new HttpNotFoundResult());
}
ViewBag.Filter = filter;
return(PartialView("_GridViewAppLinked", viewModel));
}
public override VMAuthorAphorism[] Read(SxFilter filter)
{
var sb = new StringBuilder();
sb.Append(SxQueryProvider.GetSelectString());
sb.Append(@" FROM D_AUTHOR_APHORISM AS daa ");
object param = null;
var gws = GetAuthorAphorismsWhereString(filter, out param);
sb.Append(gws);
var defaultOrder = new SxOrderItem {
FieldName = "daa.Name", Direction = SortDirection.Asc
};
sb.Append(SxQueryProvider.GetOrderString(defaultOrder, filter.Order));
sb.AppendFormat(" OFFSET {0} ROWS FETCH NEXT {1} ROWS ONLY", filter.PagerInfo.SkipCount, filter.PagerInfo.PageSize);
//count
var sbCount = new StringBuilder();
sbCount.Append(@"SELECT COUNT(1) FROM D_AUTHOR_APHORISM AS daa ");
sbCount.Append(gws);
using (var conn = new SqlConnection(ConnectionString))
{
var data = conn.Query <VMAuthorAphorism>(sb.ToString(), param: param);
filter.PagerInfo.TotalItems = conn.Query <int>(sbCount.ToString(), param: param).SingleOrDefault();
return(data.ToArray());
}
}
public ActionResult Index(int page = 1)
{
var order = new SxOrderItem {
FieldName = "de.[Order]", Direction = SortDirection.Desc
};
var filter = new SxFilter(page, _pageSize)
{
Order = order
};
var viewData = _repo.Read(filter);
if (page > 1 && !viewData.Any())
{
return(new HttpNotFoundResult());
}
var viewModel = new SxPagedCollection <VMEducation>
{
Collection = viewData,
PagerInfo = filter.PagerInfo
};
ViewBag.Filter = filter;
return(View(viewModel));
}
public PartialViewResult MainPageServices(int amount=3)
{
var order = new SxOrderItem { FieldName = "Title", Direction = SortDirection.Asc };
var filter = new SxFilter(1, 3) { Order = order };
var viewModel = _repo.Read(filter);
return PartialView("_MainPageServices", viewModel);
}
public PartialViewResult Index(VMAuthorAphorism filterModel, SxOrderItem order, int page = 1)
{
var filter = new SxFilter(page, _pageSize)
{
Order = order, WhereExpressionObject = filterModel
};
var viewModel = Repo.Read(filter);
ViewBag.Filter = filter;
return(PartialView("_GridView", viewModel));
}
public PartialViewResult MainPageSituations(int?amount = null, int maxAmount = 30)
{
var order = new SxOrderItem {
FieldName = "ds.[Text]", Direction = SortDirection.Asc
};
var filter = new SxFilter(1, amount.HasValue ? (int)amount : maxAmount)
{
Order = order
};
var viewModel = _repo.Read(filter);
return(PartialView("_MainPageSituations", viewModel));
}
public async Task <VMInfographic[]> ReadAsync(SxFilter filter)
{
var sb = new StringBuilder();
sb.Append(SxQueryProvider.GetSelectString(new string[] {
// infographic
"dp.Id AS PictureId",
"di.MaterialId",
"di.ModelCoreType",
// picture
"dp.Id",
"dp.Caption"
}));
sb.Append(@" FROM D_PICTURE AS dp
LEFT JOIN D_INFOGRAPHIC AS di ON di.PictureId = dp.Id
LEFT JOIN DV_MATERIAL AS dm ON dm.Id = di.MaterialId AND dm.ModelCoreType = di.ModelCoreType ");
object param = null;
var gws = GetInfographicsWhereString(filter, out param);
sb.Append(gws);
var defaultOrder = new SxOrderItem {
FieldName = "Caption", Direction = SortDirection.Asc
};
sb.Append(SxQueryProvider.GetOrderString(defaultOrder, filter.Order, new Dictionary <string, string> {
["Caption"] = "dp.Caption",
["PictureId"] = "dp.Id"
}));
sb.AppendFormat(" OFFSET {0} ROWS FETCH NEXT {1} ROWS ONLY", filter.PagerInfo.SkipCount, filter.PagerInfo.PageSize);
//count
var sbCount = new StringBuilder();
sbCount.Append(@"SELECT COUNT(1) FROM D_PICTURE AS dp
LEFT JOIN D_INFOGRAPHIC AS di ON di.PictureId = dp.Id
LEFT JOIN DV_MATERIAL AS dm ON dm.Id = di.MaterialId AND dm.ModelCoreType = di.ModelCoreType ");
sbCount.Append(gws);
using (var connection = new SqlConnection(_connectionString))
{
filter.PagerInfo.TotalItems = (await connection.QueryAsync <int>(sbCount.ToString(), param: param)).SingleOrDefault();
var data = connection.Query <VMInfographic, SxVMPicture, VMInfographic>(sb.ToString(), (i, p) => {
i.Picture = p;
return(i);
}, param: param, splitOn: "PictureId, Id");
return(data.ToArray());
}
}
public override VMSiteTestSubject[] Read(SxFilter filter)
{
var sb = new StringBuilder();
sb.Append(SxQueryProvider.GetSelectString(new string[] {
"dstq.Id",
"dstq.Title",
"dstq.Description",
"dstq.TestId",
"dstq.PictureId",
"dst.Id",
"dp.Id"
}));
sb.Append(" FROM D_SITE_TEST_SUBJECT AS dstq ");
var joinString = @"JOIN D_SITE_TEST AS dst ON dst.Id = dstq.TestId
LEFT JOIN D_PICTURE AS dp on dp.Id=dstq.PictureId";
sb.Append(joinString);
object param = null;
var gws = GetSiteTestSubjectsWhereString(filter, out param);
sb.Append(gws);
var defaultOrder = new SxOrderItem {
FieldName = "dstq.DateCreate", Direction = SortDirection.Desc
};
sb.Append(SxQueryProvider.GetOrderString(defaultOrder, filter.Order));
sb.AppendFormat(" OFFSET {0} ROWS FETCH NEXT {1} ROWS ONLY", filter.PagerInfo.SkipCount, filter.PagerInfo.PageSize);
//count
var sbCount = new StringBuilder();
sbCount.Append("SELECT COUNT(1) FROM D_SITE_TEST_SUBJECT AS dstq ");
sbCount.Append(joinString);
sbCount.Append(gws);
using (var connection = new SqlConnection(ConnectionString))
{
var data = connection.Query <VMSiteTestSubject, VMSiteTest, SxVMPicture, VMSiteTestSubject>(sb.ToString(), (q, t, p) =>
{
q.Picture = p;
q.Test = t;
return(q);
}, param: param, splitOn: "Id");
filter.PagerInfo.TotalItems = connection.Query <int>(sbCount.ToString(), param: param).SingleOrDefault();
return(data.ToArray());
}
}
private PartialViewResult getResult(int testId)
{
var defaultOrder = new SxOrderItem {
FieldName = "Text", Direction = SortDirection.Asc
};
var filter = new SxFilter(1, _pageSize)
{
Order = defaultOrder, AddintionalInfo = new object[] { testId }
};
var viewModel = _repo.Read(filter);
ViewBag.Filter = filter;
return(PartialView("_GridView", viewModel));
}
public async Task <ActionResult> FindGridView(VMGame filterModel, SxOrderItem order, int page = 1, int pageSize = 10)
{
var defaultOrder = new SxOrderItem {
FieldName = "Title", Direction = SortDirection.Asc
};
var filter = new SxFilter(page, pageSize)
{
WhereExpressionObject = filterModel, Order = order == null || order.Direction == SortDirection.Unknown ? defaultOrder : order
};
var viewModel = await Repo.ReadAsync(filter);
ViewBag.Filter = filter;
return(PartialView("_FindGridView", viewModel));
}
public async Task <ActionResult> Index(VMInfographic filterModel, SxOrderItem order, int mid, byte mct, int page = 1, bool linked = true)
{
if (!Request.IsAjaxRequest())
{
return(new HttpNotFoundResult());
}
var filter = new SxFilter(page, linked ? _pageSize : 10)
{
MaterialId = mid, ModelCoreType = mct, AddintionalInfo = new object[] { linked }, Order = order != null && order.Direction != SortDirection.Unknown ? order : null, WhereExpressionObject = filterModel
};
var viewModel = await Repo.ReadAsync(filter);
ViewBag.Filter = filter;
return(PartialView("_GridView", viewModel));
}
public async Task <ActionResult> Index(VMSituation filterModel, SxOrderItem order, int page = 1)
{
var filter = new SxFilter(page, _pageSize)
{
Order = order != null && order.Direction != SortDirection.Unknown ? order : null, WhereExpressionObject = filterModel
};
var viewModel = await _repo.ReadAsync(filter);
if (page > 1 && !viewModel.Any())
{
return(new HttpNotFoundResult());
}
ViewBag.Filter = filter;
return(PartialView("_GridView", viewModel));
}
public PartialViewResult FindGridView(VMAuthorAphorism filterModel, SxOrderItem order, int page = 1, int pageSize = 10)
{
var defaultOrder = new SxOrderItem {
FieldName = "Name", Direction = SortDirection.Asc
};
var filter = new SxFilter(page, pageSize)
{
WhereExpressionObject = filterModel, Order = order == null || order.Direction == SortDirection.Unknown ? defaultOrder : order
};
filter.PagerInfo.PagerSize = 5;
var viewModel = Repo.Read(filter).ToArray();
ViewBag.Filter = filter;
return(PartialView("_FindGridView", viewModel));
}
public ActionResult Index(int page = 1)
{
var order = new SxOrderItem { FieldName = "Title", Direction = SortDirection.Asc };
var filter = new SxFilter(page, _pageSize) { Order = order };
var viewData = _repo.Read(filter);
if (page > 1 && !viewData.Any())
return new HttpNotFoundResult();
var viewModel = new SxPagedCollection<VMService> {
Collection = viewData,
PagerInfo= filter.PagerInfo
};
ViewBag.Filter = filter;
return View(viewModel);
}
public async Task <ActionResult> Index(VMGame filterModel, SxOrderItem order, int page = 1)
{
var showParameter = Request.Form.Get("filterModel[Show]");
var show = string.IsNullOrEmpty(showParameter) ? null : showParameter;
var filter = new SxFilter(page, _pageSize)
{
Order = order != null && order.Direction != SortDirection.Unknown ? order : null, WhereExpressionObject = filterModel, AddintionalInfo = new object[] { show, true }
};
var viewModel = await Repo.ReadAsync(filter);
if (page > 1 && !viewModel.Any())
{
return(new HttpNotFoundResult());
}
ViewBag.Filter = filter;
return(PartialView("_GridView", viewModel));
}
public async Task <ActionResult> Index(int mid, byte mct, int page = 1, bool linked = true)
{
if (!Request.IsAjaxRequest())
{
return(new HttpNotFoundResult());
}
var defaultOrder = new SxOrderItem {
FieldName = "Caption", Direction = SortDirection.Asc
};
var filter = new SxFilter(page, linked ? _pageSize : 10)
{
MaterialId = mid, ModelCoreType = mct, AddintionalInfo = new object[] { linked }, Order = defaultOrder
};
var viewModel = await Repo.ReadAsync(filter);
ViewBag.Filter = filter;
return(PartialView("_GridView", viewModel));
}
public ActionResult Index(int page = 1)
{
var order = new SxOrderItem {
FieldName = "ds.[Text]", Direction = SortDirection.Desc
};
var filter = new SxFilter(page, _pageSize)
{
Order = order
};
var viewModel = _repo.Read(filter);
if (page > 1 && !viewModel.Any())
{
return(new HttpNotFoundResult());
}
ViewBag.Filter = filter;
return(View(viewModel));
}
public ActionResult Index(int page = 1)
{
var order = new SxOrderItem {
FieldName = "Title", Direction = SortDirection.Asc
};
var filter = new SxFilter(page, _pageSize)
{
Order = order, AddintionalInfo = new object[] { null, true }
};
var viewModel = Repo.Read(filter);
if (page > 1 && !viewModel.Any())
{
return(new HttpNotFoundResult());
}
ViewBag.Filter = filter;
return(View(viewModel));
}
public async Task <ActionResult> AppIndexFree(VMSteamApp filterModel, SxOrderItem order, int page = 1, int pageSize = 10)
{
var filter = new SxFilter(page, pageSize)
{
Order = order != null && order.Direction != SortDirection.Unknown ? order : null, WhereExpressionObject = filterModel, AddintionalInfo = new object[] { true, null }
};
var viewModel = await Repo.ReadAsync(filter);
if (page > filter.PagerInfo.TotalPages)
{
page = 1;
}
if (page > 1 && !viewModel.Any())
{
return(new HttpNotFoundResult());
}
ViewBag.Filter = filter;
return(PartialView("_GridViewAppFree", viewModel));
}
public ActionResult Index(int testId, VMSiteTestQuestion filterModel, SxOrderItem order, int page = 1)
{
var defaultOrder = new SxOrderItem {
FieldName = "Text", Direction = SortDirection.Asc
};
var filter = new SxFilter(page, _pageSize)
{
Order = order == null || order.Direction == SortDirection.Unknown ? defaultOrder : order, WhereExpressionObject = filterModel, AddintionalInfo = new object[] { testId }
};
var viewModel = _repo.Read(filter);
if (page > 1 && !viewModel.Any())
{
return(new HttpNotFoundResult());
}
ViewBag.Filter = filter;
return(PartialView("_GridView", viewModel));
}
public async Task <ViewResult> List(int page = 1)
{
var defaultOrder = new SxOrderItem {
FieldName = "DateCreate", Direction = SortDirection.Desc
};
var filter = new SxFilter(page, _pageSize)
{
Order = defaultOrder, OnlyShow = true
};
var data = await Repo.ReadAsync(filter);
var viewModel = new SxPagedCollection <VMSiteTest>
{
Collection = data,
PagerInfo = filter.PagerInfo
};
ViewBag.Filter = filter;
return(View(model: viewModel));
}
public override VMEducation[] Read(SxFilter filter)
{
var sb = new StringBuilder();
sb.Append(SxQueryProvider.GetSelectString());
sb.Append(" FROM D_EDUCATION AS de LEFT JOIN D_PICTURE AS dp ON dp.Id=de.PictureId ");
object param;
var gws = GetEducationWhereString(filter, out param);
sb.Append(gws);
var defaultOrder = new SxOrderItem {
FieldName = "de.[Order]", Direction = SortDirection.Desc
};
sb.Append(SxQueryProvider.GetOrderString(defaultOrder, filter.Order));
sb.AppendFormat(" OFFSET {0} ROWS FETCH NEXT {1} ROWS ONLY", filter.PagerInfo.SkipCount, filter.PagerInfo.PageSize);
//count
var sbCount = new StringBuilder();
sbCount.Append("SELECT COUNT(1) FROM D_EDUCATION AS de");
sbCount.Append(gws);
using (var conn = new SqlConnection(ConnectionString))
{
var data = conn.Query <VMEducation, SxVMPicture, VMEducation>(sb.ToString(), (e, p) => {
e.Picture = p;
return(e);
}, param: param, splitOn: "Id");
filter.PagerInfo.TotalItems = conn.Query <int>(sbCount.ToString(), param: param).SingleOrDefault();
return(data.ToArray());
}
}
public override async Task <ActionResult> Index(VMNews filterModel, SxOrderItem order, int page = 1)
{
ViewBag.Columns = addColums;
return(await base.Index(filterModel, order, page));
}
