/// <summary>
/// 获取用户消息
/// </summary>
/// <param name="pageIndex">页码</param>
/// <returns>用户消息列表</returns>
/// <remarks>2013-08-29 周唐炬 实现</remarks>
public ResultPager <IList <CrSiteMessage> > GetMessages(int pageIndex)
{
var result = new ResultPager <IList <CrSiteMessage> >()
{
StatusCode = -1
};
var status = new CustomerStatus.站内信状态[]
{
CustomerStatus.站内信状态.已读,
CustomerStatus.站内信状态.未读
};
var list = CrSiteMessageBo.Instance.GetPage(CurrentUser.SysNo, pageIndex, status);
if (list != null && list.TData.Any())
{
result.Data = list.TData;
result.StatusCode = 0;
result.Status = true;
}
else
{
result.Message = "暂无消息!";
}
return(result);
}
/// <summary>
/// 分页查询产品信息
/// </summary>
/// <param name="param"></param>
/// <returns></returns>
public ResultPager <Product> GetProductInfo(Product param)
{
string sql = @"
SELECT ROW_NUMBER() OVER ( ORDER BY A.ID ) AS RN ,
a.ID ,
ProductName ,
Price ,
ProductStockNumber ,
ProductSellNumber ,
State ,
nd.NodeName StateText
FROM T_PRODUCT a
LEFT JOIN dbo.T_DIC_NODE nd ON a.state = nd.NodeID
/**where**/
";
// Dapper 扩展 SqlBuilder
SqlBuilder builder = new SqlBuilder();
// 产品名称模糊匹配
if (!string.IsNullOrEmpty(param.ProductName))
{
builder.Where("ProductName LIKE @ProductName", new { ProductName = string.Format($"%{param.ProductName}%") });
}
// 产品状态筛选
if (param.StateList != null && param.StateList.Count > 0)
{
builder.Where("State in @StateList", new { StateList = param.StateList });
}
ResultPager <Product> resPager = DapperHelper.GetResultPager <Product>(builder, sql, param);
return(resPager);
}
public ResultPager <ESearchOneBookResult> Search(string keyword)
{
ResultPager <ESearchOneBookResult> result = new ResultPager <ESearchOneBookResult>();
try
{
result.PageData = _DouBanBookRepository.SearchBook(new QueryBookSearch
{
keyword = keyword,
pageIndex = 1,
pageSize = SysConfig.SearchUseMaxLine
});
if (result.PageData.datas.Count == 0)
{
result.PageData = _CrawlerSearchBook.CrawlerSearch(keyword, SysConfig.SearchUseMaxLine);
if (result.PageData.datas.Count > 0)
{
_DouBanBookRepository.SaveSearchResult(result.PageData.datas);
}
}
}
catch (Exception ex)
{
result.ErrorMsg = ex.Message;
}
return(result);
}
public ResultPager <SellOrderInfo> GetSellOrderInfo(SellOrderInfo param)
{
string sql = @"
SELECT Row_number() over(order by a.ID) as RN,
a.ID ,
a.ProductID ,
a.SellOrderNumber ,
a.EmployeeID ,
a.CustomerID ,
a.SellOrderDate ,
pt.ProductName ,
pt.Price ,
pt.ProductStockNumber ,
pt.ProductSellNumber ,
em.ID EmployeeName ,
em.Sex ,
em.BirthDate ,
em.HireDate ,
em.Salary ,
em.DepartmentID ,
cm.CompanyName ,
cm.ContactName ,
cm.Phone ,
cm.Address ,
cm.EmailAddress
FROM dbo.T_SELL_ORDER a
LEFT JOIN dbo.T_PRODUCT pt ON a.ProductID = pt.ID
LEFT JOIN dbo.T_EMPLOYEE em ON a.EmployeeID = em.ID
LEFT JOIN dbo.T_CUSTOMER cm ON a.CustomerID = cm.ID
/**where**/
";
// Dapper 扩展 SqlBuilder
SqlBuilder builder = new SqlBuilder();
if (!string.IsNullOrEmpty(param.EmployeeName))
{
builder.Where("EmployeeName LIKE @EmployeeName", new { EmployeeName = string.Format($"%{param.EmployeeName}%") });
}
if (!string.IsNullOrEmpty(param.CompanyName))
{
builder.Where("CompanyName LIKE @CompanyName", new { CompanyName = string.Format($"%{param.CompanyName}%") });
}
if (param.SellOrderDate.HasValue)
{
builder.Where("SellOrderDate=@SellOrderDate", new { SellOrderDate = param.SellOrderDate });
}
ResultPager <SellOrderInfo> resPager = DapperHelper.GetResultPager <SellOrderInfo>(builder, sql, param);
return(resPager);
}
public ResultPager <VueUserPraize> UserPraize(QUserPraize query)
{
ResultPager <VueUserPraize> result = new ResultPager <VueUserPraize>();
try
{
result.PageData = _praizeServices.queryUserPraize(query);
}
catch (Exception ex)
{
result.ErrorMsg = ex.Message;
}
return(result);
}
public ResultPager <VueUserComm> UserComment(QUserComm query)
{
ResultPager <VueUserComm> result = new ResultPager <VueUserComm>();
try
{
result.PageData = _commentServices.queryUserComm(query);
}
catch (Exception ex)
{
result.ErrorMsg = ex.Message;
}
return(result);
}
public ResultPager <VueUserRes> GetAllResourceByOwner(QUserRes query)
{
ResultPager <VueUserRes> result = new ResultPager <VueUserRes>();
try
{
// query.userId = base.getUserId();
result.PageData = _resourceServices.queryUserRes(query);
}
catch (Exception ex)
{
result.ErrorMsg = ex.Message;
}
return(result);
}
public ResultPager <VueSystemNotification> QuerySystemNotification(QMsgUser query)
{
ResultPager <VueSystemNotification> result = new ResultPager <VueSystemNotification>();
try
{
query.userId = this.getUserId();
result.PageData = _messageServices.QuerySystemNotifictaion(query);
}
catch (Exception ex)
{
result.ErrorMsg = ex.Message;
}
return(result);
}
public ResultPager <VueCommentInfo> loadMore_Res(QComment_Res query)
{
ResultPager <VueCommentInfo> result = new ResultPager <VueCommentInfo>();
try
{
query.reqUserId = this.getUserId();
result.PageData = _commentServices.loadMoreComment_Res(query);
}
catch (Exception ex)
{
result.ErrorMsg = ex.Message;
}
return(result);
}
public ResultPager <VueUserBook> FavBookList(QUserBook query)
{
ResultPager <VueUserBook> result = new ResultPager <VueUserBook>();
try
{
// query.userId = this.getUserId();
result.PageData = _userServices.queryUserbookList(query);
}
catch (Exception ex)
{
result.ErrorMsg = ex.Message;
}
return(result);
}
public ResultPager <VueResInfo> GetAllResourcesByRefCode(QRes qRes)
{
ResultPager <VueResInfo> result = new ResultPager <VueResInfo>();
try
{
qRes.reqUserId = this.getUserId();
result.PageData = _resourceServices.getResByRefCode(qRes);
}
catch (Exception ex)
{
NLogUtil.cc_ErrorTxt("[BookController]GetAllResourcesByRefCode:" + ex.Message);
result.ErrorMsg = "查询失败";
}
return(result);
}
public ResultPager <RBookList> BookNameAndAuthor(SearchReq req)
{
ResultPager <RBookList> result = null;
try
{
req.userId = this.getUserId();
result = _searchServices.searchBook(req);
}
catch (Exception ex)
{
if (result == null)
{
result = new ResultPager <RBookList>();
}
result.ErrorMsg = ex.Message;
}
return(result);
}
/// <summary>
/// 封装自定义的分页查询
/// 自由化程度更高，不过sql还是写在DAL中
/// todo 修改sql为xml可配置化
/// 原始sql中必须包含 ROW_NUMBER()开窗！！！ /**where**/ where注释标记不可少！！
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="builder"></param>
/// <param name="sql">SELECT ROW_NUMBER() OVER ( ORDER BY XXID ) AS RN,* FROM TABLE /**where**/ </param>
/// <param name="pager"></param>
/// <returns></returns>
public static ResultPager <T> GetResultPager <T>(SqlBuilder builder, string sql, PageInfo pager) where T : class
{
string countSql = string.Format($"SELECT COUNT(1) FROM ({sql}) AS TR");
string dataSql = pager.IsPager ? string.Format($@"SELECT * FROM ({sql}) AS TR WHERE TR.RN>@PageStart AND TR.RN<@PageEnd ") : sql;
var templateCount = builder.AddTemplate(countSql);
var templateData = builder.AddTemplate(dataSql);
// 是否分页查询
if (pager.IsPager)
{
builder.AddParameters(new { PageStart = pager.PageStart, PageEnd = pager.PageEnd });
}
ResultPager <T> resPager = new ResultPager <T>();
resPager.Total = (int)DapperHelper.ExcuteScalar(templateCount.RawSql, templateCount.Parameters);
resPager.Rows = Query <T>(templateData.RawSql, templateData.Parameters).ToList();
return(resPager);
}
public ResultPager <VueUserPointTrans> getPointTrans(QUserTrans query)
{
ResultPager <VueUserPointTrans> result = new ResultPager <VueUserPointTrans>();
try
{
query.userId = this.getUserId();
result.PageData = _userFinanceServices.getPointTrans(query);
}
catch (CCException cex)
{
result.ErrorMsg = cex.Message;
}
catch (Exception ex)
{
result.ErrorMsg += "[积分明细查询失败]";
NLogUtil.cc_ErrorTxt("[UserFinanceController]getPointTrans:" + ex.Message);
}
return(result);
}
public ResultPager <RBookList> searchBook(SearchReq searchRequest)
{
ResultPager <RBookList> result = new ResultPager <RBookList>();
if (string.IsNullOrEmpty(searchRequest.userId))
{
result.ErrorMsg = "未知用户无法搜索";
return(result);
}
if (string.IsNullOrEmpty(searchRequest.keyword.Trim()))
{
result.Message = "没有查询内容";
}
else
{
RefAsync <int> totalNumber = new RefAsync <int>();
result.PageData.datas = _bookRepository.searchByNameAndAuthor(searchRequest, totalNumber).Result;
result.PageData.totalCount = totalNumber;
try
{
if (searchRequest.keyword.Length > 100)
{
searchRequest.keyword = searchRequest.keyword.Substring(0, 100);
}
logSearchKey(new ESearchKeyLog
{
UserId = searchRequest.userId,
searchKey = searchRequest.keyword
});
}
catch (Exception logEx)
{
NLogUtil.cc_ErrorTxt("searchBook: 添加搜索记录出错:" + logEx.Message);
}
}
return(result);
}
public ResultPager <RBookList> getBookListPager(QBookList query)
{
ResultPager <RBookList> result = new ResultPager <RBookList>();
var st = DateTime.Now;
Console.WriteLine($"开始Query{st}");
try
{
if (query.QueryType == QBookList_Type.Search)
{
result = _searchServices.searchBook(new SearchReq
{
keyword = query.Code,
pageIndex = query.pageIndex,
pageSize = query.pageSize,
userId = this.getUserId()
});
}
else
{
ModelPager <RBookList> pd = _bookServices.GetBookListPager(query);
result.PageData = pd;
result.PageData.pageIndex = query.pageIndex;
result.PageData.pageSize = query.pageSize;
}
}
catch (Exception ex)
{
result.ErrorMsg = ex.Message;
}
var et = DateTime.Now;
Console.WriteLine($"结束Query{et}");
Console.WriteLine($"时间差{et - st}");
return(result);
}
protected override void OnLoad(EventArgs e)
{
base.OnLoad(e);
string searchType = Strings.Clean(Request.Params["cbsubmit"]);
int startMonth = Strings.ToInt(Strings.Clean(Request.Params["startMonth"]));
int startYear = Strings.ToInt(Strings.Clean(Request.Params["startYear"]));
int endMonth = Strings.ToInt(Strings.Clean(Request.Params["endMonth"]));
int endYear = Strings.ToInt(Strings.Clean(Request.Params["endYear"]));
keyword = Strings.Clean(Request.Params["cbkeyword"]);
int currentPage = Strings.ToInt(Strings.Clean(Request.Params["page"]));
bool bSearchRange = false;
if (Page.Request.HttpMethod == "POST")
{
bSearchRange = (Request.Params["searchRangeButton"] != null);
}
else
{
bSearchRange = (searchType != null && string.Compare(searchType, "range", true) == 0);
}
firstRecord = 0;
lastRecord = 0;
totalItems = 0;
if (currentPage < 0)
{
currentPage = 1;
}
int pageSize = 10;
if (keyword == null)
{
resultsHtml = "<br><br>Please enter a search phrase.<br><br><br>";
}
else
{
firstRecord = (currentPage - 1) * pageSize + 1;
recordIndex = firstRecord;
//set up the search collection
//determine what text needs to be removed from the title e.g. - National Cancer Institute
SiteWideSearchConfig searchConfig = ModuleObjectFactory <SiteWideSearchConfig> .GetModuleObject(SnippetInfo.Data);
if (searchConfig != null)
{
SearchCollection = searchConfig.SearchCollection;
ResultTitleText = searchConfig.ResultTitleText;
}
ISiteWideSearchResultCollection results;
try
{
results = NCI.Search.SiteWideSearch.GetSearchResults(SearchCollection, Keyword, pageSize,
(currentPage - 1) * pageSize);
if (results != null && results.ResultCount > 0)
{
lastRecord = firstRecord + (int)results.ResultCount - 1;
totalItems = (int)results.ResultCount;
ResultRepeater.DataSource = results;
ResultRepeater.DataBind();
//pager code
string urlFormat;
string pagerUrl;
if (bSearchRange)
{
urlFormat = PrettyUrl + "?cbsubmit=range&cbkeyword={0}&startMonth={1}&startYear={2}&endMonth={3}&endYear={4}";
pagerUrl = String.Format(urlFormat, Server.UrlEncode(keyword), startMonth.ToString(), startYear.ToString(), endMonth.ToString(), endYear.ToString());
}
else
{
urlFormat = PrettyUrl + "?cbkeyword={0}";
pagerUrl = String.Format(urlFormat, Server.UrlEncode(keyword));
}
ResultPager objPager = new ResultPager(pagerUrl, currentPage, pageSize, 2, totalItems);
pager = objPager.RenderPager();
}
else
{
firstRecord = 0;
}
}
catch (Exception ex)
{
//capture exactly which keyword caused the error
log.ErrorFormat("{0} - Search with the following keyword returned an error: {1}", ex, PrettyUrl, Keyword);
this.RaiseErrorPage();
}
//eSearch.FillSearchResults(eSearchResults);
}
this.PageInstruction.AddFieldFilter("channelName", (fieldName, data) =>
{
data.Value = "Newsletter";
});
}
