/// <summary>
/// 获取骑士信息
/// danny-20150318
/// </summary>
/// <param name="criteria"></param>
/// <returns></returns>
public PageInfo <ClienterListModel> GetClienteres(ClienterSearchCriteria criteria)
{
var pagedQuery = new ClienterManage();
PageInfo <ClienterListModel> pageinfo = dao.GetClienteres <ClienterListModel>(criteria);
return(pageinfo);
}
public ActionResult SuperManStatistical()
{
var criteria = new ClienterSearchCriteria()
{
PagingRequest = new NewPagingResult(1, PageSizeType.Web_PageSize.GetHashCode()), searchType = 1
};
var pagedList = iDistributionProvider.GetClienteresCount(criteria);
return(View(pagedList));
}
/// <summary>
/// 骑士统计
/// danny-20150326
/// </summary>
/// <param name="criteria"></param>
/// <returns></returns>
public Ets.Model.Common.ClienterCountManageList GetClienteresCount(ClienterSearchCriteria criteria)
{
PageInfo <ClienterViewModel> pageinfo = dao.GetClienteresCount <ClienterViewModel>(criteria);
NewPagingResult pr = new NewPagingResult()
{
PageIndex = criteria.PagingRequest.PageIndex, PageSize = criteria.PagingRequest.PageSize, RecordCount = pageinfo.All, TotalCount = pageinfo.All
};
List <ClienterViewModel> list = pageinfo.Records.ToList();
var clienterCountManageList = new Ets.Model.Common.ClienterCountManageList(list, pr);
return(clienterCountManageList);
}
/// <summary>
/// 骑士统计
/// danny-20150326
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="criteria"></param>
/// <returns></returns>
public PageInfo <T> GetClienteresCount <T>(ClienterSearchCriteria criteria)
{
var sbtbl = new StringBuilder(@" (select c.Id,
COUNT(*) OrderCount,
c.TrueName Name,
c.AccountBalance,
c.PhoneNo
from clienter c with(nolock)
join [order] o on c.Id=o.clienterId
where 1=1 ");
if (criteria.searchType == 1)//当天
{
sbtbl.Append(" AND DateDiff(DAY, GetDate(),o.PubDate)=0 ");
}
else if (criteria.searchType == 2)//本周
{
sbtbl.Append(" AND DateDiff(WEEK, GetDate(),DATEADD (DAY, -1,o.PubDate))=0 ");
}
else if (criteria.searchType == 3)//本月
{
sbtbl.Append(" AND DateDiff(MONTH, GetDate(),o.PubDate)=0 ");
}
sbtbl.Append(" group by c.Id,c.TrueName,c.AccountBalance,c.PhoneNo ) tbl ");
string columnList = @" tbl.Id
,tbl.OrderCount
,tbl.Name
,tbl.AccountBalance
,tbl.PhoneNo ";
var sbSqlWhere = new StringBuilder(" 1=1 ");
if (!string.IsNullOrEmpty(criteria.clienterName))
{
sbSqlWhere.AppendFormat(" AND Name='{0}' ", criteria.clienterName);
}
if (!string.IsNullOrEmpty(criteria.clienterPhone))
{
sbSqlWhere.AppendFormat(" AND PhoneNo='{0}' ", criteria.clienterPhone);
}
string tableList = sbtbl.ToString();
string orderByColumn = " tbl.OrderCount DESC ";
return(new PageHelper().GetPages <T>(SuperMan_Read, criteria.PagingRequest.PageIndex, sbSqlWhere.ToString(), orderByColumn, columnList, tableList, criteria.PagingRequest.PageSize, true));
}
public ActionResult SuperManStatistical(ClienterSearchCriteria criteria)
{
var pagedList = iDistributionProvider.GetClienteresCount(criteria);
return(PartialView("_PartialSuperManStatistical", pagedList));
}
/// <summary>
/// 骑士列表查询
/// danny-20150318
/// </summary>
/// <param name="criteria"></param>
/// <returns></returns>
public PageInfo <T> GetClienteres <T>(ClienterSearchCriteria criteria)
{
string columnList = @" C.[Id]
,C.[PhoneNo]
,C.[LoginName]
,C.[recommendPhone]
,C.[Password]
,C.[TrueName]
,C.[IDCard]
,C.[PicWithHandUrl]
,C.[PicUrl]
,C.[Status]
,C.[AccountBalance]
,C.[InsertTime]
,C.[InviteCode]
,C.[City]
,C.[CityId]
,C.[GroupId]
,C.[HealthCardID]
,C.[InternalDepart]
,C.[ProvinceCode]
,C.[AreaCode]
,C.[CityCode]
,C.[Province]
,C.[BussinessID]
,C.[WorkStatus]
,isnull(cs.ClienterId,0) as CSID --如果非0就存在跨店
";
var sbSqlWhere = new StringBuilder(" 1=1 ");
if (!string.IsNullOrEmpty(criteria.clienterName))
{
sbSqlWhere.AppendFormat(" AND C.TrueName='{0}' ", criteria.clienterName);
}
if (!string.IsNullOrEmpty(criteria.clienterPhone))
{
sbSqlWhere.AppendFormat(" AND C.PhoneNo='{0}' ", criteria.clienterPhone);
}
if (!string.IsNullOrEmpty(criteria.recommonPhone))
{
sbSqlWhere.AppendFormat(" AND C.recommendPhone='{0}' ", criteria.recommonPhone.Trim());
}
if (criteria.Status != -1)
{
sbSqlWhere.AppendFormat(" AND C.Status={0} ", criteria.Status);
}
if (criteria.GroupId != null && criteria.GroupId > 0)
{
sbSqlWhere.AppendFormat(" AND C.GroupId={0} ", criteria.GroupId);
}
if (!string.IsNullOrEmpty(criteria.businessCity))
{
sbSqlWhere.AppendFormat(" AND C.City='{0}' ", criteria.businessCity.Trim());
}
if (!string.IsNullOrEmpty(criteria.txtPubStart))
{
sbSqlWhere.AppendFormat(" AND CONVERT(CHAR(10),WR.CreateTime,120)=CONVERT(CHAR(10),'{0}',120) and WtihdrawRecords.Amount < 0", criteria.txtPubStart.Trim());
}
string tableList = @" clienter C WITH (NOLOCK)
left JOIN(SELECT UserId,MIN(CreateTime) CreateTime
FROM dbo.WtihdrawRecords WITH(NOLOCK)
GROUP BY UserId) WR ON WR.UserId = C.Id
left Join (SELECT ClienterId
FROM CrossShopLog csl WITH(NOLOCK)
GROUP BY csl.ClienterId) cs on c.Id=cs.ClienterId
--left JOIN dbo.WtihdrawRecords ON WtihdrawRecords.UserId = C.Id
--left join dbo.CrossShopLog cs on c.Id=cs.ClienterId
";
string orderByColumn = " C.Id DESC";
return(new PageHelper().GetPages <T>(SuperMan_Read, criteria.PageIndex, sbSqlWhere.ToString(), orderByColumn, columnList, tableList, criteria.PageSize, true));
}
