public PaginatedListResult <LongTermModel> LongTerm(SearchModel criteria)
{
string sqlParams = string.Empty;
sqlParams += string.Format("set @PageSize = {0};", criteria.PageSize);
sqlParams += string.Format("set @PageIndex = {0};", criteria.PageIndex);
if ((criteria.IsBookMark ?? false) == true)
{
sqlParams += string.Format("set @IsBookMark = 1;");
}
string filePath = System.IO.Path.Combine(Helper.RootPath, "SQL", "Report", "LongTerm.sql");
string sql = System.IO.File.ReadAllText(filePath);
//string orderBy = " order by " + criteria.SortName + " " + criteria.SortOrder;
//sql = Helper.ReplaceOrderBy(sql, orderBy);
sql = Helper.ReplaceParams(sql, sqlParams);
//sql = sql.Replace("\r\n"," ");
PaginatedListResult <LongTermModel> list = new PaginatedListResult <LongTermModel>();
int totalRows = 0;
Helper.Log(sql, "LongTerm");
list.rows = SqlHelper.GetList <LongTermModel>(sql, ref totalRows);
list.total = totalRows;
return(list);
}
public PaginatedListResult <CategoryModel> Get(SearchModel criteria)
{
string sqlParams = string.Empty;
sqlParams += string.Format("set @PageSize = {0};", criteria.PageSize);
sqlParams += string.Format("set @PageIndex = {0};", criteria.PageIndex);
if (criteria.CategoryID > 0)
{
sqlParams += string.Format("set @CategoryID = {0};", criteria.CategoryID);
}
if ((criteria.IsBookMark ?? false) == true)
{
sqlParams += string.Format("set @IsBookMark = 1;");
}
if ((criteria.IsArchive ?? false) == true)
{
sqlParams += string.Format("set @IsArchive = 1;");
}
//sqlParams += string.Format("set @LastTradingDate = '{0}';", (criteria.LastTradingDate ?? Helper.MinDateTime).ToString("yyyy-MM-dd"));
string filePath = System.IO.Path.Combine(Helper.RootPath, "SQL", "Category", "List.sql");
string sql = System.IO.File.ReadAllText(filePath);
string orderBy = " order by " + criteria.SortName + " " + criteria.SortOrder;
sql = Helper.ReplaceOrderBy(sql, orderBy);
sql = Helper.ReplaceParams(sql, sqlParams);
//sql = sql.Replace("\r\n"," ");
PaginatedListResult <CategoryModel> list = new PaginatedListResult <CategoryModel>();
int totalRows = 0;
list.rows = SqlHelper.GetList <CategoryModel>(sql, ref totalRows);
list.total = totalRows;
return(list);
}
public static async Task <PaginatedListResult <TModel> > ToPaginatedListResultAsync <TModel>(this IQueryable <TModel> query, PaginatedSearchBaseOptionsViewModel options)
{
// TODO: Make this more understandable for developers
// Important! Get count before skip and take.
var totalCount = query.Count();
var pages = CalculateResultPages(totalCount, options.SizePerPage);
if (totalCount <= options.Skip)
{
options.Page = totalCount / options.SizePerPage;
}
if (query.Expression.Type == typeof(IOrderedQueryable <TModel>))
{
query = query.Skip(options.Skip).Take(options.Take);
}
var result = new PaginatedListResult <TModel>
{
ContextObjects = await query.ToListAsync(),
TotalCount = totalCount,
Pages = pages
};
return(result);
}
public PaginatedListResult <CAGRModel> GetCAGR(SearchModel criteria)
{
string sqlParams = string.Empty;
sqlParams += string.Format("set @PageSize = {0};", criteria.PageSize);
sqlParams += string.Format("set @PageIndex = {0};", criteria.PageIndex);
if (criteria.IsBookMarkCategory == true)
{
sqlParams += string.Format("set @isBookMarkCategory = {0};", (criteria.IsBookMarkCategory ?? false) == true ? 1 : 0);
}
if (criteria.IsBookMark == true)
{
sqlParams += string.Format("set @isBookMark = {0};", (criteria.IsBookMark ?? false) == true ? 1 : 0);
}
if (string.IsNullOrEmpty(criteria.CategoryIDs) == false)
{
sqlParams += string.Format("set @CategoryIDs = '{0}';", criteria.CategoryIDs);
}
sqlParams += string.Format("set @fromDate = '{0}';", (criteria.FromDate ?? Helper.MinDateTime).ToString("yyyy-MM-dd"));
sqlParams += string.Format("set @toDate = '{0}';", (criteria.ToDate ?? Helper.MinDateTime).ToString("yyyy-MM-dd"));
string filePath = System.IO.Path.Combine(Helper.RootPath, "SQL", "Report", "CAGR.sql");
string sql = System.IO.File.ReadAllText(filePath);
string orderBy = " order by " + criteria.SortName + " " + criteria.SortOrder;
sql = Helper.ReplaceOrderBy(sql, orderBy);
sql = Helper.ReplaceParams(sql, sqlParams);
//sql = sql.Replace("\r\n"," ");
PaginatedListResult <CAGRModel> list = new PaginatedListResult <CAGRModel>();
int totalRows = 0;
list.rows = SqlHelper.GetList <CAGRModel>(sql);
list.total = totalRows;
return(list);
}
public PaginatedListResult <SplitCheckModel> GetSplitCheck(SearchModel criteria)
{
string filePath = System.IO.Path.Combine(Helper.RootPath, "SQL", "Split", "SplitCheck.sql");
string sql = System.IO.File.ReadAllText(filePath);
PaginatedListResult <SplitCheckModel> list = new PaginatedListResult <SplitCheckModel>();
list.rows = SqlHelper.GetList <SplitCheckModel>(sql);
list.total = list.rows.Count();
return(list);
}
// handle translation : TODO manage paging, orderby & translation
public async Task <PaginatedListResult <T> > FindPageAsync(SpecificationBase <T> specification,
CancellationToken cancellationToken = default)
{
if (!specification.IsPagingEnabled)
{
throw new ArgumentException("Invalid specification (IsPagingEnabled==FALSE)");
}
var result = await ApplySpecification(specification)
.ToListAsync(cancellationToken);
var resultCount = await SpecificationCountAsync(specification, cancellationToken);
return(PaginatedListResult <T> .Create(result, specification.Take, specification.Skip, resultCount));
}
public PaginatedListResult <PortfolioModel> GetInvestments(SearchModel criteria)
{
string sqlParams = string.Empty;
sqlParams += string.Format("set @PageSize = {0};", criteria.PageSize);
sqlParams += string.Format("set @PageIndex = {0};", criteria.PageIndex);
string filePath = System.IO.Path.Combine(Helper.RootPath, "SQL", "Portfolio", "Investment.sql");
string sql = System.IO.File.ReadAllText(filePath);
string orderBy = " order by " + criteria.SortName + " " + criteria.SortOrder;
sql = Helper.ReplaceOrderBy(sql, orderBy);
sql = Helper.ReplaceParams(sql, sqlParams);
//sql = sql.Replace("\r\n"," ");
PaginatedListResult <PortfolioModel> list = new PaginatedListResult <PortfolioModel>();
int totalRows = 0;
list.rows = SqlHelper.GetList <PortfolioModel>(sql, ref totalRows);
list.total = totalRows;
return(list);
}
public static async Task <PaginatedListResult <TViewModel> > ToPaginatedListResultForViewModelAsync <TViewModel>(this IQueryable <TViewModel> query, PaginatedSearchBaseOptionsViewModel options)
{
var totalCount = await query.CountAsync();
var pages = CalculateResultPages(totalCount, options.SizePerPage);
if (totalCount <= options.Skip)
{
options.Page = totalCount / options.SizePerPage;
}
query = query
.Skip(options.Skip)
.Take(options.Take);
var result = new PaginatedListResult <TViewModel>
{
ContextObjects = await query.ToListAsync(),
TotalCount = totalCount,
Pages = pages
};
return(result);
}
public PaginatedListResult <WoodVolume> Get(WoodVolume criteria, Paging paging)
{
using (WVCContext context = new WVCContext()) {
IQueryable <wvc_wood_volume> woodVolumes = context.wvc_wood_volume;
if ((criteria.id ?? 0) > 0)
{
woodVolumes = woodVolumes.Where(q => q.id == criteria.id);
}
if (string.IsNullOrEmpty(criteria.name) == false)
{
woodVolumes = woodVolumes.Where(q => q.name.StartsWith(criteria.name));
}
if ((criteria.division_id ?? 0) > 0)
{
woodVolumes = woodVolumes.Where(q => q.division_id == criteria.division_id);
}
if ((criteria.district_id ?? 0) > 0)
{
woodVolumes = woodVolumes.Where(q => q.district_id == criteria.district_id);
}
if ((criteria.range_id ?? 0) > 0)
{
woodVolumes = woodVolumes.Where(q => q.range_id == criteria.range_id);
}
if ((criteria.village_id ?? 0) > 0)
{
woodVolumes = woodVolumes.Where(q => q.village_id == criteria.village_id);
}
if ((criteria.taluk_id ?? 0) > 0)
{
woodVolumes = woodVolumes.Where(q => q.taluk_id == criteria.taluk_id);
}
IQueryable <WoodVolume> query = (from volume in woodVolumes
join div in context.wvc_division on volume.division_id equals div.id into divisions
from div in divisions.DefaultIfEmpty()
join dis in context.wvc_district on volume.division_id equals dis.id into districts
from dis in districts.DefaultIfEmpty()
join ran in context.wvc_range on volume.range_id equals ran.id into ranges
from ran in ranges.DefaultIfEmpty()
join tal in context.wvc_taluk on volume.taluk_id equals tal.id into taluks
from tal in taluks.DefaultIfEmpty()
join vil in context.wvc_village on volume.village_id equals vil.id into villages
from vil in villages.DefaultIfEmpty()
select new WoodVolume {
id = volume.id,
description = volume.description,
district_id = volume.district_id,
district_name = dis.name,
division_id = volume.division_id,
division_name = div.name,
name = volume.name,
range_id = volume.range_id,
range_name = ran.name,
taluk_id = volume.taluk_id,
taluk_name = tal.name,
user_id = volume.user_id,
village_id = volume.village_id,
village_name = vil.name
});
paging.Total = query.Count();
if (string.IsNullOrEmpty(paging.SortOrder))
{
paging.SortOrder = "asc";
}
if (string.IsNullOrEmpty(paging.SortName) == false)
{
query = query.OrderBy(paging.SortName, (paging.SortOrder == "asc"));
}
if (paging.PageSize > 0)
{
query = query.Skip((paging.PageIndex - 1) * paging.PageSize).Take(paging.PageSize);
}
PaginatedListResult <WoodVolume> paginatedList = new PaginatedListResult <WoodVolume>();
paginatedList.rows = query.ToList();
paginatedList.total = paging.Total;
if ((criteria.id ?? 0) > 0)
{
WoodVolume woodVolume = paginatedList.rows.FirstOrDefault();
if (woodVolume != null)
{
woodVolume.items = (from item in context.wvc_wood_volum_item
where item.wood_volume_id == woodVolume.id
select new WoodVolumeItem {
co_efficient = item.co_efficient,
description = item.description,
final_volume = item.final_volume,
girth = item.girth,
length = item.length,
volume = item.volume,
wood_volume_id = item.wood_volume_id,
id = item.id
}).ToList();
}
}
return(paginatedList);
}
}
public static ListServiceResult <List <TPayload> > SuccessWithPaginator <TPayload>(PaginatedListResult <TPayload> paginatedListResult, string message = "")
{
var result = new ListServiceResult <List <TPayload> >();
result.IsSuccessful = true;
result.Payload = paginatedListResult.ContextObjects;
result.Pages = paginatedListResult.Pages;
result.TotalItems = paginatedListResult.TotalCount;
result.Message = message;
return(result);
}
public PaginatedListResult<WoodVolume> Get(WoodVolume criteria,Paging paging)
{
using(WVCContext context = new WVCContext()) {
IQueryable<wvc_wood_volume> woodVolumes = context.wvc_wood_volume;
if((criteria.id ?? 0) > 0) {
woodVolumes = woodVolumes.Where(q => q.id == criteria.id);
}
if(string.IsNullOrEmpty(criteria.name) == false) {
woodVolumes = woodVolumes.Where(q => q.name.StartsWith(criteria.name));
}
if((criteria.division_id ?? 0) > 0) {
woodVolumes = woodVolumes.Where(q => q.division_id == criteria.division_id);
}
if((criteria.district_id ?? 0) > 0) {
woodVolumes = woodVolumes.Where(q => q.district_id == criteria.district_id);
}
if((criteria.range_id ?? 0) > 0) {
woodVolumes = woodVolumes.Where(q => q.range_id == criteria.range_id);
}
if((criteria.village_id ?? 0) > 0) {
woodVolumes = woodVolumes.Where(q => q.village_id == criteria.village_id);
}
if((criteria.taluk_id ?? 0) > 0) {
woodVolumes = woodVolumes.Where(q => q.taluk_id == criteria.taluk_id);
}
IQueryable<WoodVolume> query = (from volume in woodVolumes
join div in context.wvc_division on volume.division_id equals div.id into divisions
from div in divisions.DefaultIfEmpty()
join dis in context.wvc_district on volume.division_id equals dis.id into districts
from dis in districts.DefaultIfEmpty()
join ran in context.wvc_range on volume.range_id equals ran.id into ranges
from ran in ranges.DefaultIfEmpty()
join tal in context.wvc_taluk on volume.taluk_id equals tal.id into taluks
from tal in taluks.DefaultIfEmpty()
join vil in context.wvc_village on volume.village_id equals vil.id into villages
from vil in villages.DefaultIfEmpty()
select new WoodVolume {
id = volume.id,
description = volume.description,
district_id = volume.district_id,
district_name = dis.name,
division_id = volume.division_id,
division_name = div.name,
name = volume.name,
range_id = volume.range_id,
range_name = ran.name,
taluk_id = volume.taluk_id,
taluk_name = tal.name,
user_id = volume.user_id,
village_id = volume.village_id,
village_name = vil.name
});
paging.Total = query.Count();
if(string.IsNullOrEmpty(paging.SortOrder)) {
paging.SortOrder = "asc";
}
if(string.IsNullOrEmpty(paging.SortName) == false) {
query = query.OrderBy(paging.SortName,(paging.SortOrder == "asc"));
}
if(paging.PageSize > 0) {
query = query.Skip((paging.PageIndex - 1) * paging.PageSize).Take(paging.PageSize);
}
PaginatedListResult<WoodVolume> paginatedList = new PaginatedListResult<WoodVolume>();
paginatedList.rows = query.ToList();
paginatedList.total = paging.Total;
if((criteria.id ?? 0) > 0) {
WoodVolume woodVolume = paginatedList.rows.FirstOrDefault();
if(woodVolume != null) {
woodVolume.items = (from item in context.wvc_wood_volum_item
where item.wood_volume_id == woodVolume.id
select new WoodVolumeItem {
co_efficient = item.co_efficient,
description = item.description,
final_volume = item.final_volume,
girth = item.girth,
length = item.length,
volume = item.volume,
wood_volume_id = item.wood_volume_id,
id = item.id
}).ToList();
}
}
return paginatedList;
}
}
