public ActionResult GetPostByCategory(int id,int ?page)
{
_pagingHandler.PageIndex = page ?? 1;
var postList = _pagingHandler.GetPagingList(c=>c.CategoryId==id,c => new PostModel()
{
Title = c.Title,
DateTime = c.DateTime,
ShortDescription = c.ShortDescription,
FullName = c.User.FirstName + " " + c.User.LastName,
Id = c.Id
}, c => c.DateTime, ListSortDirection.Descending).ToList();
var pagingModel = new PagingModel()
{
HasNextPage = _pagingHandler.HasNextPage,
HasPreviousPage = _pagingHandler.HasPreviousPage,
PageIndex = _pagingHandler.PageIndex,
IsFirstPage = _pagingHandler.IsFirstPage,
IsLastPage = _pagingHandler.IsLastPage,
PageCount = _pagingHandler.PageCount,
PageNumber = _pagingHandler.PageNumber,
PageSize = _pagingHandler.PageSize
};
var model = new Tuple<List<PostModel>, PagingModel>(postList, pagingModel);
return View("_ListPost", model);
}
//
// GET: /Knowledge/
/// <summary>
/// 获取宠物知识的信息
/// </summary>
/// <param name="pageIndex">当前页码</param>
/// <param name="limit">每页的显示条数</param>
/// <returns></returns>
public JsonResult Index(int pageIndex,int limit)
{
KnowledgePet knowledge = new KnowledgePet();
PagingModel<WebCommonModel> _pageKnowledge = new PagingModel<WebCommonModel>();
List<WebCommonModel> commonList = new List<WebCommonModel>();
List<CVKnowledgePet> knowledgeList = new List<CVKnowledgePet>();
//获取总页数
int count = 0;
try
{
knowledgeList = knowledge.GetPetKnowledgePerPageList(pageIndex,limit,out count);
commonList = CommonDao.DataTransferToKnowledgeWebCommonModelList(knowledgeList);
_pageKnowledge.total = count;
_pageKnowledge.records = commonList;
}
catch (Exception ex)
{
throw ex;
}
return Json(_pageKnowledge, JsonRequestBehavior.AllowGet);
}
public async Task <IActionResult> GetGroups(PagingModel pagingModel)
{
var groups = await _groupsService.GetGroupsAsync(pagingModel);
return(Ok(groups));
}
/// <summary>
/// 获取会员列表
/// </summary>
/// <param name="startTime"></param>
/// <param name="endTime"></param>
/// <param name="email"></param>
/// <param name="mobile"></param>
/// <param name="regionCode"></param>
/// <param name="pModel"></param>
/// <returns></returns>
public PagedList <CustomerInfo> getOrderList(DateTime?startTime, DateTime?endTime, string email, string mobile, int regionCode, PagingModel pModel)
{
var sql = @"
;WITH
OrderStatistics AS
(
SELECT o.UserId,
ConsumptionAmount = SUM(ISNULL(o.PaidAmount,0)/ISNULL(o.ExchangeRate,0)),
ConsumptionTimes = COUNT(o.OrderCode)
FROM OrderInfo o(NOLOCK)
WHERE o.PayTime IS NOT NULL
AND ISNULL(o.PaidAmount,0) > 0
GROUP BY o.UserId
)
SELECT *
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY c.Createtime) AS RowNumber,
c.*,
MemberPoints = 0,
ConsumptionAmount = ISNULL(os.ConsumptionAmount,0),
ConsumptionTimes = ISNULL(os.ConsumptionTimes,0)
FROM Customer c(NOLOCK)
LEFT JOIN OrderStatistics os ON os.UserId = c.Id
WHERE 1=1
{0}
{1}
{2}
{3}
{4}
) a
WHERE 1=1
{5}
SELECT @TotalCount = COUNT(c.Id)
FROM Customer c(NOLOCK)
WHERE 1=1
{0}
{1}
{2}
{3}
{4}
";
sql = string.Format(sql, regionCode > -1 ? " AND c.RegionCode = @RegionCode " : "",
!string.IsNullOrEmpty(mobile) ? " AND c.Mobile LIKE '%'+@Mobile+'%' " : "",
!string.IsNullOrEmpty(email) ? " AND c.Email LIKE '%'+@Email+'%' " : "",
startTime.HasValue ? " AND c.CreateTime >= @StartTime" : "",
endTime.HasValue ? " AND c.CreateTime <=@EndTime" : "",
pModel.IsPaging ? " AND a.RowNumber >(@PageIndex-1)*@PageSize AND a.RowNumber <= @PageIndex*@PageSize" : "");
var db = DbSFO2ORead;
var parameters = db.CreateParameterCollection();
parameters.Append("@RegionCode", regionCode);
parameters.Append("@Email", email);
parameters.Append("@Mobile", mobile);
parameters.Append("@StartTime", startTime.Value);
parameters.Append("@EndTime", endTime.Value.AddDays(1));
parameters.Append("@TotalCount", 0, System.Data.DbType.Int32, System.Data.ParameterDirection.InputOutput);
parameters.Append("@PageIndex", pModel.PageIndex);
parameters.Append("@PageSize", pModel.PageSize);
var cList = db.ExecuteSqlList <CustomerInfo>(sql, parameters);
PagedList <CustomerInfo> result = new PagedList <CustomerInfo>();
var totalCount = parameters["@TotalCount"].Value != null?Convert.ToInt32(parameters["@TotalCount"].Value) : 0;
result.ContentList = cList.ToList();
result.CurrentIndex = pModel.PageIndex;
result.PageSize = pModel.PageSize;
result.RecordCount = totalCount;
return(result);
}
{ /// <summary>
///
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="connectionString"></param>
/// <param name="pageQuery">查询内容</param>
/// <param name="paging">分页</param>
/// <returns></returns>
public List <T> GetPageData <T>(string connectionString, PageQueryModel pageQuery, PagingModel paging) where T : new()
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
SqlCommand comm = new SqlCommand();
comm.Connection = connection;
comm.CommandType = CommandType.StoredProcedure;
comm.CommandText = "CommonPageProc";
comm.Parameters.Add("@TableName", SqlDbType.VarChar, 5000).Value = pageQuery.TableName;
comm.Parameters.Add("@Fields", SqlDbType.VarChar, 5000).Value = pageQuery.Fields;
comm.Parameters.Add("@OrderField", SqlDbType.VarChar, 5000).Value = pageQuery.OrderField;
comm.Parameters.Add("@sqlWhere", SqlDbType.VarChar, 5000).Value = pageQuery.SqlWhere;
comm.Parameters.Add("@pageSize", SqlDbType.Int, 16).Value = paging.PageSize;
comm.Parameters.Add("@pageIndex", SqlDbType.Int, 16).Value = paging.PageIndex;
SqlParameter paraTP = new SqlParameter("@TotalPage", SqlDbType.Int);
paraTP.Direction = ParameterDirection.Output;
paraTP.Value = paging.PageCount;
comm.Parameters.Add(paraTP);
SqlParameter param = new SqlParameter("@TotalRecord", SqlDbType.Int);
param.Direction = ParameterDirection.ReturnValue;
comm.Parameters.Add(param);
List <T> ent = null;
using (IDataReader dr = comm.ExecuteReader(CommandBehavior.CloseConnection))
{
ent = DbHelperSQL.GetEntityList <T>(dr);
}
paging.PageCount = Convert.ToInt32(comm.Parameters["@TotalPage"].Value);
paging.RecordCount = Convert.ToInt32(param.Value);
comm.Parameters.Clear();
return(ent);
}
}
public JsonResult GetAllInstrumentJsonData(string documentId)
{
//查询标准器具
DataTableUtils.DataTableModel dtm = DataTableUtils.GetJquerydataTableParams();
PagingModel paging = new PagingModel();
paging.PageSize = dtm.PageSize;
paging.PageCurrent = dtm.PageIndex;
paging.FieldShow = @"InstrumentId,InstrumentName,ManageNo,Specification,InstrumentType,Manufacturer,SerialNo,CreateDate,DueStartDate,DueEndDate,BelongDepart";
paging.Where = GetInstrumentSearchCondition();
IList <ParamModel> list = Global.Business.ServiceProvider.ParamService.GetAll();
ParamModel paramModel = null;
IList <OrgModel> orgList = Global.Business.ServiceProvider.OrgService.GetAll();
OrgModel org = new OrgModel();
//IList<CraftModel> craftList = ServiceProvider.CraftService.GetAll();
IList <Hashtable> instrumentList = ServiceProvider.InstrumentService.GetAllInstrumentListForPaging(paging);
//Json数据格式组装
dtm.iTotalRecords = paging.RecordCount;
dtm.iTotalDisplayRecords = dtm.iTotalRecords;
//StringBuilder sbData = new StringBuilder();
dtm.aaData = new List <List <string> >();
foreach (var item in instrumentList)
{
paramModel = list.SingleOrDefault(p => p.ParamCode == Constants.SysParamType.InstrumentType);
org = orgList.SingleOrDefault(o => o.OrgCode == (item["BelongDepart"] == null ? "" : item["BelongDepart"].ToString()));
dtm.aaData.Add(new List <string>());
//sbData.Clear();
//sbData.AppendFormat("<div documentId='{0}' instrumentId='{1}'>", documentId, item["InstrumentId"].ToString());
//if (LoginHelper.LoginUserAuthorize.ContainsKey("/Document/InstrumentList".ToLower()))
//{
// //修改
// sbData.Append("<a href='#' onclick='fnChoose(" + documentId + "," + item["InstrumentId"] + ");'>关 联</a> ");
//}
//sbData.Append("</div>");
if (LoginHelper.LoginUserAuthorize.ContainsKey("/Instrument/DeleteInstruments".ToLower()))
{
dtm.aaData[dtm.aaData.Count - 1].Add(string.Format("<input type='checkbox' name='chk' value={0} />", item["InstrumentId"]));
}
//dtm.aaData[dtm.aaData.Count - 1].Add(sbData.ToString());
dtm.aaData[dtm.aaData.Count - 1].Add(item["InstrumentName"] == null ? "" : item["InstrumentName"].ToString());
dtm.aaData[dtm.aaData.Count - 1].Add(item["ManageNo"] == null ? "" : item["ManageNo"].ToString());
dtm.aaData[dtm.aaData.Count - 1].Add(item["Specification"] == null ? "" : item["Specification"].ToString());
//设备分类
dtm.aaData[dtm.aaData.Count - 1].Add(paramModel.itemsList.SingleOrDefault(p => p.ParamItemValue == item["InstrumentType"].ToString()).ParamItemName);
dtm.aaData[dtm.aaData.Count - 1].Add(item["Manufacturer"] == null ? "" : item["Manufacturer"].ToString());
dtm.aaData[dtm.aaData.Count - 1].Add(item["SerialNo"] == null ? "" : item["SerialNo"].ToString());
dtm.aaData[dtm.aaData.Count - 1].Add(string.Format("{0:yyyy-MM-dd}", item["CreateDate"] == null ? "" : item["CreateDate"].ToString()));
dtm.aaData[dtm.aaData.Count - 1].Add(string.Format("{0:yyyy-MM-dd}", item["DueStartDate"] == null ? "" : item["DueStartDate"].ToString()));
dtm.aaData[dtm.aaData.Count - 1].Add(string.Format("{0:yyyy-MM-dd}", item["DueEndDate"] == null ? "" : item["DueEndDate"].ToString()));
//所属部门
dtm.aaData[dtm.aaData.Count - 1].Add(org == null ? null : org.OrgName);
}
JsonResult jr = Json(new
{
sEcho = dtm.sEcho,
iTotalRecords = dtm.iTotalRecords,
iTotalDisplayRecords = dtm.iTotalDisplayRecords,
aaData = dtm.aaData,
}, JsonRequestBehavior.AllowGet);
return(jr);
}
public JsonResult GetAllContactJsonData()
{
//查询标准器具
DataTableUtils.DataTableModel dtm = DataTableUtils.GetJquerydataTableParams();
PagingModel paging = new PagingModel();
paging.PageSize = dtm.PageSize;
paging.PageCurrent = dtm.PageIndex;
paging.FieldShow = @"ContactId,CompanyName,CaseType,Abstract,State,ContactContent,FeedbackContent,FeedbackDate,Creator,CreatId,CreatDate,ItemCode";
paging.Where = string.IsNullOrWhiteSpace(dtm.FieldCondition) ? dtm.FieldCondition = "1=1" : dtm.FieldCondition;
IList <Hashtable> contractList = Instrument.Business.ServiceProvider.ContactService.GetAllContactListForPaging(paging);
////系统参数
IList <ParamModel> paramList = Global.Business.ServiceProvider.ParamService.GetAll();
ParamModel pCaseType = paramList.SingleOrDefault(p => p.ParamCode.Equals(Common.Constants.SysParamType.ContactCaseType));
if (pCaseType == null)
{
pCaseType = new ParamModel();
}
ParamItemModel itemCaseType = null;
//Json数据格式组装
dtm.iTotalRecords = paging.RecordCount;
dtm.iTotalDisplayRecords = dtm.iTotalRecords;
dtm.aaData = new List <List <string> >();
StringBuilder sbOperate = new StringBuilder();
foreach (var item in contractList)
{
dtm.aaData.Add(new List <string>());
sbOperate.Clear(); //操作
sbOperate.AppendFormat("<div contactId='{0}' >", UtilsHelper.Encrypt(item["ContactId"].ToString()));
int state = Convert.ToInt32(item["State"]);
if (state.Equals(Common.Constants.ContactState.草稿.GetHashCode()))
{
if (LoginHelper.LoginUserAuthorize.ContainsKey("新增联络单-修改"))
{
sbOperate.Append("<a href='#' onclick='fnEditContact(this);'>编辑</a> ");
}
if (LoginHelper.LoginUserAuthorize.ContainsKey("/Contact/Delete".ToLower()))
{
sbOperate.Append("<a href='#' onclick='fnDeleteContact(this);'>删除</a> ");
}
if (LoginHelper.LoginUserAuthorize.ContainsKey("/Contact/Send".ToLower()))
{
sbOperate.Append("<a href='#' onclick='fnSendContact(this);'>提交</a> ");
}
}
if (!state.Equals(Common.Constants.ContactState.草稿.GetHashCode()))
{
if (LoginHelper.LoginUserAuthorize.ContainsKey("/Contact/Detail".ToLower()))
{
sbOperate.Append("<a href='#' onclick='fnViewContact(this);'>查看</a> ");
}
}
if (state.Equals(Common.Constants.ContactState.已提交.GetHashCode()))
{
if (LoginHelper.LoginUserAuthorize.ContainsKey("/Contact/SynContact".ToLower()))
{
sbOperate.Append("<a href='#' onclick='fnSynContact(this);'>更新同步</a> ");
}
}
if (state.Equals(Common.Constants.ContactState.已反馈.GetHashCode()))
{
if (LoginHelper.LoginUserAuthorize.ContainsKey("/Contact/Solve".ToLower()))
{
sbOperate.Append("<a href='#' onclick='fnSolveContact(this);'>解决</a> ");
}
}
sbOperate.Append("</div>");
dtm.aaData[dtm.aaData.Count - 1].Add(sbOperate.ToString());
dtm.aaData[dtm.aaData.Count - 1].Add(string.Format("{0}", item["CompanyName"]));
//事项分类
itemCaseType = pCaseType.itemsList.SingleOrDefault(p => p.ParamItemValue.Equals(item["CaseType"].ToString()));
dtm.aaData[dtm.aaData.Count - 1].Add(string.Format("{0}", itemCaseType == null ? "" : itemCaseType.ParamItemName));
dtm.aaData[dtm.aaData.Count - 1].Add(string.Format("{0}", item["Abstract"]));
dtm.aaData[dtm.aaData.Count - 1].Add(((Constants.ContactState)Convert.ToInt32(item["State"])).ToString()); //状态
dtm.aaData[dtm.aaData.Count - 1].Add(string.Format("{0:yyyy-MM-dd}", item["FeedbackDate"]));
dtm.aaData[dtm.aaData.Count - 1].Add(string.Format("{0}", item["Creator"]));
dtm.aaData[dtm.aaData.Count - 1].Add(string.Format("{0:yyyy-MM-dd}", item["CreatDate"]));
}
JsonResult jr = Json(new
{
sEcho = dtm.sEcho,
iTotalRecords = dtm.iTotalRecords,
iTotalDisplayRecords = dtm.iTotalDisplayRecords,
aaData = dtm.aaData
}, JsonRequestBehavior.AllowGet);
return(jr);
}
/// <summary>
/// 帐号分页查询
/// </summary>
/// <param name="parameter"></param>
/// <returns></returns>
public PagingModel<Account> GetPagingAccount(AccountParam parameter)
{
PagingModel<Account> pagingModel = new PagingModel<Account>();
IAccountDal accountDal = new AccountDal();
//过滤
parameter.OpenId = DotNet.Utils.Untility.StringHelper.FilterHtml(parameter.OpenId);
parameter.LoginName = DotNet.Utils.Untility.StringHelper.FilterHtml(parameter.LoginName);
parameter.Mobile = DotNet.Utils.Untility.StringHelper.FilterHtml(parameter.Mobile);
parameter.Email = DotNet.Utils.Untility.StringHelper.FilterHtml(parameter.Email);
pagingModel = accountDal.GetPaging(parameter);
return pagingModel;
}
/// <summary>
/// 域的分页
/// </summary>
/// <param name="parameter"></param>
/// <returns></returns>
public PagingModel<Domain> GetPaging(DomainParam parameter)
{
PagingModel<Domain> pagingModel = new PagingModel<Domain>()
{
PageIndex = parameter.PageIndex,
PageSize = parameter.PageSize
};
using (var connection = ConnectionFactory.GetMasterSql())
{
//跳过的页数
int skip = (parameter.PageIndex - 1) * parameter.PageSize;
StringBuilder sb = new StringBuilder();
sb.AppendFormat(@" and DelFlag=@DelFlag");
#region where条件和参数
DynamicParameters param = new DynamicParameters();
param.Add("@DelFlag", (int)DelFlagEnum.Noraml);
//按照域代码进行搜索
if (!string.IsNullOrEmpty(parameter.DomainCode))
{
sb.AppendFormat(" and DomainCode=@DomainCode");
param.Add("@DomainCode", parameter.DomainCode);
}
//按照域级别进行搜索
if (parameter.DomainLevel > 0)
{
sb.AppendFormat(" and DomainLevel=@DomainLevel");
param.Add("@DomainLevel", parameter.DomainLevel);
}
//根据池子Id查询出所有的域
if (parameter.SSOPoolPoolId > 0)
{
sb.AppendFormat(" and SSOPoolPoolId=@SSOPoolPoolId");
param.Add("@SSOPoolPoolId", parameter.SSOPoolPoolId);
}
//按照域的名称来搜索
if (!string.IsNullOrEmpty(parameter.DomainName))
{
sb.AppendFormat(" and DomainName like @DomainName");
param.Add("@DomainName", string.Format("%{0}%", parameter.DomainName));
}
//域网址
if (!string.IsNullOrEmpty(parameter.DomainUrl))
{
sb.AppendFormat(" and DomainUrl like @DomainUrl");
param.Add("@DomainUrl", string.Format("%{0}%", parameter.DomainUrl));
}
//是否启用
if (Enum.IsDefined(typeof(IsEnabledEnum), parameter.IsEnabled))
{
sb.AppendFormat(" and IsEnabled=@IsEnabled");
param.Add("@IsEnabled", parameter.IsEnabled);
}
//是否单点登录
if (Enum.IsDefined(typeof(IsSSOEnum), parameter.IsSSO))
{
sb.AppendFormat(" and IsSSO=@IsSSO");
param.Add("@IsSSO", parameter.IsSSO);
}
//按上一级的Id
if (parameter.ParentDomainId > 0)
{
sb.AppendFormat(" and ParentDomainId=@ParentDomainId");
param.Add("@ParentDomainId", parameter.ParentDomainId);
}
//按上一级的域代码
//if (!string.IsNullOrEmpty(parameter.ParentDomainCode))
//{
// sb.AppendFormat("and ParentDomainCode=@ParentDomainCode");
// param.Add("@ParentDomainCode", parameter.ParentDomainCode);
//}
#endregion
string countSql = string.Format("Select count(*) from B_Domain where 1=1 {0}", sb.ToString());
var totalCount = connection.ExecuteScalar<int>(countSql, param);
pagingModel.TotalRecord = totalCount;
string sql = string.Format("Select top {0} * from (Select row_number() over (order by DomainLevel) AS RowNumber,* from B_Domain ) as tb where RowNumber>{1} {2}", parameter.PageSize, skip, sb.ToString());
var query = connection.Query<Domain>(sql, param);
pagingModel.Data = query.ToList();
}
return pagingModel;
}
/// <summary>
/// Get all Group by page
/// </summary>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <returns></returns>
public PagingModel GetGroupByPage(string groupID, string groupName, string description, string operation, PagingModel pm)
{
int totalCount = 0;
StringBuilder SelectSQL = new StringBuilder("select GroupID,GroupID as 'Group ID',GroupName,NumOfUsers as 'No.of user',Description from t_ccas_group where IsDeleted = 0 and operation=@operation ");
Dictionary <string, object> pvs = new Dictionary <string, object>();
pvs.Add("@operation", operation);
if (!string.IsNullOrEmpty(groupID))
{
SelectSQL.Append(" and GroupID like @GroupID ESCAPE '/' ");
pvs.Add("@GroupID", "%" + groupID + "%");
}
if (!string.IsNullOrEmpty(groupName))
{
SelectSQL.Append(" and GroupName like @GroupName ESCAPE '/' ");
pvs.Add("@GroupName", "%" + groupName + "%");
}
if (!string.IsNullOrEmpty(description))
{
SelectSQL.Append(" and Description like @Description ESCAPE '/' ");
pvs.Add("@Description", "%" + description + "%");
}
SelectSQL.Append(" order by LastModifiedTime desc ,CreatedTime desc ");
DataTable dt = Template.QueryByPage(SelectSQL.ToString(), pvs, pm.PageSize, pm.PageIndex, out totalCount);
pm.DataTable = dt;
pm.TotalCount = totalCount;
return(pm);
}
public async Task <RootObject> GetOrdersAsync(DateTime updatedFrom, DateTime updatedTo, PagingModel page, Mark mark = null)
{
var parameters = new SearchCriteria()
{
filter_groups = new List <FilterGroup>()
{
new FilterGroup()
{
filters = new List <Filter>()
{
new Filter("updated_at", updatedFrom.ToRestParameterString(), Filter.ConditionType.From)
}
},
new FilterGroup()
{
filters = new List <Filter>()
{
new Filter("updated_at", updatedTo.ToRestParameterString(), Filter.ConditionType.To)
}
}
},
current_page = page.CurrentPage,
page_size = page.ItemsPerPage,
};
var webRequest = ( WebRequest )WebRequest.Create()
.Method(MagentoWebRequestMethod.Get)
.Path(MagentoServicePath.CreateOrdersServicePath())
.Parameters(parameters)
.AuthToken(this.Token)
.Url(this.Url);
return(await ActionPolicies.RepeatOnChannelProblemAsync.Get(async() =>
{
using (var v = await webRequest.RunAsync(mark).ConfigureAwait(false))
{
return JsonConvert.DeserializeObject <RootObject>(new StreamReader(v, Encoding.UTF8).ReadToEnd());
}
}).ConfigureAwait(false));
}
public OperationReturnModel <PagedListModel> pagedList(long listId, ListType type, PagingModel paging)
{
OperationReturnModel <PagedListModel> ret = new OperationReturnModel <PagedListModel>();
try
{
if (!string.IsNullOrEmpty(paging.Terms))
{
//Build filter
paging.Filter = new FilterInfo()
{
Field = "ItemNumber",
FilterType = "contains",
Value = paging.Terms,
Condition = "||",
Filters = new List <FilterInfo>()
{
new FilterInfo()
{
Condition = "||", Field = "Label", Value = paging.Terms, FilterType = "contains"
},
new FilterInfo()
{
Condition = "||", Field = "Name", Value = paging.Terms, FilterType = "contains"
}
}
};
if (paging.Terms.IndexOf(' ') > -1)
{
string[] words = paging.Terms.Split(' ');
paging.Filter = new FilterInfo()
{
Field = "ItemNumber",
FilterType = "contains",
Value = paging.Terms,
Condition = "||",
Filters = new List <FilterInfo>()
{
new FilterInfo()
{
Condition = "&&", Field = "Label", Value = words[0], FilterType = "contains"
},
new FilterInfo()
{
Condition = "&&", Field = "Name", Value = words[0], FilterType = "contains"
}
}
};
foreach (string word in words)
{
paging.Filter.Filters[0].Filters = new List <FilterInfo>();
paging.Filter.Filters[0].Filters.Add
(new FilterInfo()
{
Condition = "&&",
Field = "Label",
Value = word,
FilterType = "contains"
});
paging.Filter.Filters[1].Filters = new List <FilterInfo>();
paging.Filter.Filters[1].Filters.Add
(new FilterInfo()
{
Condition = "&&",
Field = "Name",
Value = word,
FilterType = "contains"
});
}
}
}
//var stopWatch = new System.Diagnostics.Stopwatch(); //Temp: Remove
//stopWatch.Start();
var list = _listService.ReadPagedList(this.AuthenticatedUser, this.SelectedUserContext, type, listId, paging);
//stopWatch.Stop();
//elRepo.WriteInformationLog(string.Format("Total time to retrieve List {0}: {1}ms", listId, stopWatch.ElapsedMilliseconds));
ret.SuccessResponse = list;
ret.IsSuccess = true;
}
catch (Exception ex)
{
ret.IsSuccess = false;
ret.ErrorMessage = ex.Message;
_elRepo.WriteErrorLog("pagedList", ex);
}
return(ret);
}
/// <summary>
/// 帐号分页查询
/// </summary>
/// <param name="parameter"></param>
/// <returns></returns>
public PagingModel<Account> GetPaging(AccountParam parameter)
{
PagingModel<Account> pagingModel = new PagingModel<Account>()
{
PageIndex = parameter.PageIndex,
PageSize = parameter.PageSize
};
using (var connection = ConnectionFactory.GetMasterSql())
{
//跳过的页数
int skip = (parameter.PageIndex - 1) * parameter.PageSize;
StringBuilder sb = new StringBuilder();
sb.AppendFormat(@" and DelFlag=@DelFlag ");
#region where条件和参数
DynamicParameters param = new DynamicParameters();
param.Add("@DelFlag", (int)DelFlagEnum.Noraml);
//按照帐号的OpenId进行搜索
if (!string.IsNullOrEmpty(parameter.OpenId))
{
sb.AppendFormat(" and OpenId=@OpenId");
param.Add("@OpenId", parameter.OpenId);
}
//按照帐号Id进行搜索
if (parameter.AccountId > 0)
{
sb.AppendFormat(" and AccountId=@AccountId");
param.Add("@AccountId", parameter.AccountId);
}
//按照提交域的Id进行查询
if (parameter.SubmitDomainId > 0)
{
sb.AppendFormat(" and SubmitDomainId=@SubmitDomainId");
param.Add("@SubmitDomainId", parameter.SubmitDomainId);
}
//按照登录名查询
if (!string.IsNullOrEmpty(parameter.LoginName))
{
sb.AppendFormat(" and LoginName=@LoginName");
param.Add("@LoginName", parameter.LoginName);
}
//按照绑定手机号码查询
if (!string.IsNullOrEmpty(parameter.Mobile))
{
sb.AppendFormat(" and Mobile=@Mobile");
sb.AppendFormat(" and MobileBinding=@MobileBinding");
param.Add("@Mobile", parameter.Mobile);
param.Add("@MobileBinding",(int)BindingEnum.Binded);
}
//按照绑定Email查询
if (!string.IsNullOrEmpty(parameter.Email))
{
sb.AppendFormat(" and Email=@Email");
sb.AppendFormat(" and EmailBinding=@EmailBinding");
param.Add("@Email", parameter.Email);
param.Add("@EmailBinding", (int)BindingEnum.Binded);
}
#endregion
string countSql = string.Format("Select count(*) from B_Account where 1=1 {0}", sb.ToString());
var totalCount = connection.ExecuteScalar<int>(countSql, param);
pagingModel.TotalRecord = totalCount;
string sql = string.Format("Select top {0} * from (Select row_number() over (order by AccountId) AS RowNumber,* from B_Account ) as tb where RowNumber>{1} {2}", parameter.PageSize, skip, sb.ToString());
var query = connection.Query<Account>(sql, param);
pagingModel.Data = query.ToList();
}
return pagingModel;
}
public JsonResult GetAllInstrumentJsonData()
{
//查询标准器具
DataTableUtils.DataTableModel dtm = DataTableUtils.GetJquerydataTableParams();
PagingModel paging = new PagingModel();
paging.PageSize = dtm.PageSize;
paging.PageCurrent = dtm.PageIndex;
paging.FieldShow = @"InstrumentId,RecordState,InstrumentName,ManageNo,CertificateNo,Specification,InstrumentCate,Manufacturer,SerialNo,DueStartDate,DueEndDate,LeaderName,BelongDepart,StorePalce,CreateDate,CreateUser";
paging.Where = GetSearchCondition(dtm);
IList <Hashtable> instrumentList = ServiceProvider.InstrumentService.GetAllInstrumentListForPaging(paging);
//系统参数
IList <ParamModel> paramList = Global.Business.ServiceProvider.ParamService.GetAll();
IList <OrgModel> orgList = Global.Business.ServiceProvider.OrgService.GetAll();
OrgModel belongDeptModel = new OrgModel();
ParamItemModel mInstrumentCate = null;
//分类
ParamModel InstrumentCate = paramList.SingleOrDefault(t => t.ParamCode == Instrument.Common.Constants.SysParamType.InstrumentCate); //设备分类
IList <ParamItemModel> paramItemList = InstrumentCate.itemsList;
//Json数据格式组装
dtm.iTotalRecords = paging.RecordCount;
dtm.iTotalDisplayRecords = dtm.iTotalRecords;
dtm.aaData = new List <List <string> >();
StringBuilder sbOperate = new StringBuilder();
foreach (var item in instrumentList)
{
dtm.aaData.Add(new List <string>());
sbOperate.Clear(); //操作
sbOperate.AppendFormat("<div instrumentId='{0}' instrumentName='{1}'>", UtilsHelper.Encrypt(item["InstrumentId"].ToString()), item["InstrumentName"]);
if (LoginHelper.LoginUserAuthorize.ContainsKey("/InstrumentFlow/FlowDetails".ToLower()))
{
//详细
sbOperate.Append("<a href='#' onclick='fnInstrumentFlowDetail(this);'>流转明细</a> ");
}
sbOperate.Append("</div>");
dtm.aaData[dtm.aaData.Count - 1].Add(sbOperate.ToString());
dtm.aaData[dtm.aaData.Count - 1].Add(string.Format("{0}", item["InstrumentName"])); //仪器名称
dtm.aaData[dtm.aaData.Count - 1].Add(string.Format("{0}", item["Specification"])); //型号
dtm.aaData[dtm.aaData.Count - 1].Add(string.Format("{0}", item["SerialNo"])); //出厂编号
dtm.aaData[dtm.aaData.Count - 1].Add(string.Format("{0}", item["ManageNo"])); //管理编号
dtm.aaData[dtm.aaData.Count - 1].Add(string.Format("{0}", item["CertificateNo"])); //证书编号
mInstrumentCate = paramItemList.SingleOrDefault(t => t.ParamItemValue == Convert.ToString(item["InstrumentCate"]));
dtm.aaData[dtm.aaData.Count - 1].Add(mInstrumentCate == null ? "" : mInstrumentCate.ParamItemName);
//分公司
belongDeptModel = orgList.SingleOrDefault(o => o.OrgCode == string.Format("{0}", item["BelongDepart"]));
dtm.aaData[dtm.aaData.Count - 1].Add(belongDeptModel == null ? "" : belongDeptModel.OrgName);
dtm.aaData[dtm.aaData.Count - 1].Add(string.Format("{0:d}", item["DueStartDate"]));
dtm.aaData[dtm.aaData.Count - 1].Add(string.Format("{0}", item["LeaderName"]));
dtm.aaData[dtm.aaData.Count - 1].Add(string.Format("{0:d}", item["CreateUser"]));
}
JsonResult jr = Json(new
{
sEcho = dtm.sEcho,
iTotalRecords = dtm.iTotalRecords,
iTotalDisplayRecords = dtm.iTotalDisplayRecords,
aaData = dtm.aaData
}, JsonRequestBehavior.AllowGet);
return(jr);
}
/// <summary>
/// Gets a list of Client KPIs matching the specified search params
/// </summary>
/// <param name="pm"></param>
/// <param name="csm"></param>
/// <returns></returns>
public List <GroupCustomModel> List1(PagingModel pm, CustomSearchModel csm)
{
if (csm.FromDate.HasValue && csm.ToDate.HasValue && csm.FromDate?.Date == csm.ToDate?.Date)
{
csm.ToDate = csm.ToDate?.AddDays(1);
}
// Parameters
#region Parameters
List <object> parameters = new List <object>()
{
{ new SqlParameter("skip", pm.Skip) },
{ new SqlParameter("take", pm.Take) },
{ new SqlParameter("csmStatus", ( int )csm.Status) },
{ new SqlParameter("query", csm.Query ?? ( object )DBNull.Value) },
{ new SqlParameter("csmToDate", csm.ToDate ?? ( object )DBNull.Value) },
{ new SqlParameter("userid", (CurrentUser != null) ? CurrentUser.Id : 0) },
{ new SqlParameter("csmFromDate", csm.FromDate ?? ( object )DBNull.Value) },
};
#endregion
string query = @"SELECT
g.*,
(SELECT COUNT(1) FROM [dbo].[ClientGroup] cg WHERE cg.[GroupId]=g.[Id]) AS [ClientCount]
FROM
[dbo].[Group] g";
// WHERE
#region WHERE
query = $"{query} WHERE (1=1)";
// Limit to only show Groups for logged in user
//if ( !CurrentUser.IsAdmin )
//{
// query = $@"{query} AND EXISTS(SELECT 1 FROM dbo].[PSPClient] pc WHERE EXISTS
// (
// SELECT 1 FROM [dbo].[ClientGroup] cg WHERE cg.[ClientId]=pc.[ClientId]
// )
// ) ";
//}
#endregion
// Custom Search
#region Custom Search
if (csm.Status != Status.All)
{
query = $"{query} AND g.Status=@csmStatus ";
}
if (csm.FromDate.HasValue && csm.ToDate.HasValue)
{
query = $"{query} AND (g.CreatedOn >= @csmFromDate AND g.CreatedOn <= @csmToDate) ";
}
else if (csm.FromDate.HasValue || csm.ToDate.HasValue)
{
if (csm.FromDate.HasValue)
{
query = $"{query} AND (g.CreatedOn>=@csmFromDate) ";
}
if (csm.ToDate.HasValue)
{
query = $"{query} AND (g.CreatedOn<=@csmToDate) ";
}
}
#endregion
// Normal Search
#region Normal Search
if (!string.IsNullOrEmpty(csm.Query))
{
query = string.Format(@"{0} AND (
g.[Name] LIKE '%{1}%' OR
g.[Description] LIKE '%{1}%'
) ", query, csm.Query.Trim());
}
#endregion
// ORDER
query = $"{query} ORDER BY {pm.SortBy} {pm.Sort}";
// SKIP, TAKE
query = string.Format("{0} OFFSET (@skip) ROWS FETCH NEXT (@take) ROWS ONLY ", query);
List <GroupCustomModel> groups = context.Database.SqlQuery <GroupCustomModel>(query, parameters.ToArray()).ToList();
if (groups.NullableAny())
{
foreach (GroupCustomModel g in groups)
{
g.Clients = context.ClientGroups.Where(cg => cg.GroupId == g.Id).Select(s => s.Client.CompanyName).ToList();
}
}
return(groups);
}
/// <summary>
/// Gets a count of KPIs matching the specified search params
/// </summary>
/// <param name="pm"></param>
/// <param name="csm"></param>
/// <returns></returns>
public int Total1(PagingModel pm, CustomSearchModel csm)
{
if (csm.FromDate.HasValue && csm.ToDate.HasValue && csm.FromDate?.Date == csm.ToDate?.Date)
{
csm.ToDate = csm.ToDate?.AddDays(1);
}
// Parameters
#region Parameters
List <object> parameters = new List <object>()
{
{ new SqlParameter("skip", pm.Skip) },
{ new SqlParameter("take", pm.Take) },
{ new SqlParameter("csmStatus", ( int )csm.Status) },
{ new SqlParameter("query", csm.Query ?? ( object )DBNull.Value) },
{ new SqlParameter("csmToDate", csm.ToDate ?? ( object )DBNull.Value) },
{ new SqlParameter("userid", (CurrentUser != null) ? CurrentUser.Id : 0) },
{ new SqlParameter("csmFromDate", csm.FromDate ?? ( object )DBNull.Value) },
};
#endregion
string query = @"SELECT
COUNT(1) AS [Total]
FROM
[dbo].[Group] g";
// WHERE
#region WHERE
query = $"{query} WHERE (1=1)";
// Limit to only show KPIs for logged in user
//if ( !CurrentUser.IsAdmin )
//{
// query = $@"{query} AND EXISTS(SELECT 1 FROM [dbo].[PSPUser] pu
// INNER JOIN [dbo].[PSPClient] pc ON pc.PSPId=pu.PSPId
// WHERE
// pc.ClientId=c.Id AND
// pu.UserId=@userid
// ) ";
//}
#endregion
// Custom Search
#region Custom Search
if (csm.Status != Status.All)
{
query = $"{query} AND g.Status=@csmStatus ";
}
if (csm.FromDate.HasValue && csm.ToDate.HasValue)
{
query = $"{query} AND (g.CreatedOn >= @csmFromDate AND g.CreatedOn <= @csmToDate) ";
}
else if (csm.FromDate.HasValue || csm.ToDate.HasValue)
{
if (csm.FromDate.HasValue)
{
query = $"{query} AND (g.CreatedOn>=@csmFromDate) ";
}
if (csm.ToDate.HasValue)
{
query = $"{query} AND (g.CreatedOn<=@csmToDate) ";
}
}
#endregion
// Normal Search
#region Normal Search
if (!string.IsNullOrEmpty(csm.Query))
{
query = string.Format(@"{0} AND (
g.[Name] LIKE '%{1}%' OR
g.[Description] LIKE '%{1}%'
) ", query, csm.Query.Trim());
}
#endregion
CountModel model = context.Database.SqlQuery <CountModel>(query, parameters.ToArray()).FirstOrDefault();
return(model.Total);
}
public IActionResult GetRecordLabelArtists([FromQuery] PagingModel <Artist> model)
{
model.PagingList = _artistsManager.GetRecordLabelArtists(CurrentLoggedUserId, model.Skip, model.Take, model.SearchQuery).ToList();
return(View("NextArtists", model));
}
/// <summary>
/// 单点登录池分页
/// </summary>
/// <param name="parameter"></param>
/// <returns></returns>
public PagingModel<SSOPool> GetPaging(SSOPoolParam parameter)
{
PagingModel<SSOPool> pagingModel = new PagingModel<SSOPool>()
{
PageIndex = parameter.PageIndex,
PageSize = parameter.PageSize
};
using (var connection = ConnectionFactory.GetMasterSql())
{
//跳过的页数
int skip = (parameter.PageIndex - 1) * parameter.PageSize;
StringBuilder sb = new StringBuilder();
sb.AppendFormat(@" and DelFlag=@DelFlag ");
#region where条件和参数
DynamicParameters param = new DynamicParameters();
param.Add("@DelFlag", (int)DelFlagEnum.Noraml);
//按照池名称进行搜索
if (!string.IsNullOrEmpty(parameter.PoolName))
{
sb.AppendFormat(" and PoolName like @PoolName");
param.Add("@PoolName", string.Format("%{0}%", parameter.PoolName));
}
//按照池Id进行搜索
if (parameter.PoolId > 0)
{
sb.AppendFormat(" and PoolId=@PoolId");
param.Add("@PoolId", parameter.PoolId);
}
//按照域的名称来搜索
if (Enum.IsDefined(typeof(IsEnabledEnum), parameter.IsEnabled))
{
sb.AppendFormat(" and IsEnabled=@IsEnabled");
param.Add("@IsEnabled", parameter.IsEnabled);
}
#endregion
string countSql = string.Format("Select count(*) from B_SSOPool where 1=1 {0}", sb.ToString());
var totalCount = connection.ExecuteScalar<int>(countSql, param);
pagingModel.TotalRecord = totalCount;
string sql = string.Format("Select top {0} * from (Select row_number() over (order by PoolId) AS RowNumber,* from B_SSOPool ) as tb where RowNumber>{1} {2}", parameter.PageSize, skip, sb.ToString());
var query = connection.Query<SSOPool>(sql, param);
pagingModel.Data = query.ToList();
}
return pagingModel;
}
public async Task <IEnumerable <SysRolesResponeModel> > GetModelsAsync(PagingModel pagingModel, SysRolesRequestModel queryParameter, IEnumerable <string> fields = null)
{
return(await sysRolesRepository.GetModelsAsync(pagingModel, queryParameter, fields));
}
public IHttpActionResult GetAll([System.Web.Http.FromUri] PagingModel pagingModel)
{
//var lst = _admissionForm.List();
var lst = _admissionForm.List().ToList().ConvertAll(x => new StudentAdmissionFormResponse
{
registrationNumber = x.RegistrationNumber,
submittedStatus = string.IsNullOrEmpty(x.SubmittedStatus)?"":"Submitted",
id = x.Id,
address = x.Address,
candidatename = x.CandidateName,
city = x.City,
coursetype = x.CourseType,
email = x.Email,
fathername = x.FatherName,
qualificationList = _studentMapping.List().Where(y => y.StudentAdmissionID == x.Id).ToList().ConvertAll(xy => new QualificationResp
{
boards = xy.Boards,
sclass = xy.Boards,
subjects = xy.Subjects,
yearofpassing = xy.YearOfPassing,
}).ToList(),
mobile = x.Mobile,
mothername = x.MotherName,
state = x.State
}).ToList();
#region Paging
int count = lst.Count();
int CurrentPage = pagingModel.pageNumber + 1;
int PageSize = pagingModel.pageSize;
int TotalCount = count;
int TotalPages = (int)Math.Ceiling(count / (double)PageSize);
// Returns List of Customer after applying Paging
var items = lst.OrderBy(x => x.id).Skip((CurrentPage - 1) * PageSize).Take(PageSize).ToList();
// if CurrentPage is greater than 1 means it has previousPage
var previousPage = CurrentPage > 1 ? "Yes" : "No";
// if TotalPages is greater than CurrentPage means it has nextPage
var nextPage = CurrentPage < TotalPages ? "Yes" : "No";
var paginationMetadata = new
{
totalCount = TotalCount,
pageSize = PageSize,
currentPage = CurrentPage,
totalPages = TotalPages,
previousPage,
nextPage
};
#endregion
if (items != null)
{
var res = new ApiResponse()
{
status = HttpStatusCode.OK.ToString(),
code = (int)HttpStatusCode.OK,
result = items,
pages = count
};
return(Ok(res));
}
else
{
return(BadRequest());
}
}
/// <summary>
/// 获取结算单列表
/// </summary>
/// <param name="queryModel"></param>
/// <param name="pModel"></param>
/// <returns></returns>
public PagedList <SettlementOrderInfo> getOrderList(SettlementQueryModel queryModel, PagingModel pModel)
{
return(Dao.getOrderList(queryModel, pModel));
}