public ActionResult List(ListViewModel model)
{
// filter bilgilerinin default boş değerlerle doldurulması sağlanıyor
if (model.Filter == null)
{
model.Filter = new ListFilterViewModel();
}
if (!model.CurrentPage.HasValue)
{
model.CurrentPage = 1;
}
if (!model.PageSize.HasValue)
{
model.PageSize = 10;
}
CustomerSearchFilter searchFilter = new CustomerSearchFilter();
searchFilter.CurrentPage = model.CurrentPage.HasValue ? model.CurrentPage.Value : 1;
searchFilter.PageSize = model.PageSize.HasValue ? model.PageSize.Value : 10;
searchFilter.SortOn = model.SortOn;
searchFilter.SortDirection = model.SortDirection;
searchFilter.Filter_Name = model.Filter.Filter_Name;
searchFilter.Filter_LastName = model.Filter.Filter_LastName;
model.DataList = _customerService.GetAllPaginatedWithDetailBySearchFilter(searchFilter);
return(View(model));
}
/// <summary>
/// 获取检索数据的Sql文
/// </summary>
/// <param name="baseFilter"></param>
/// <returns></returns>
protected override string GetSearchSql(BaseFilter baseFilter)
{
CustomerSearchFilter filter = baseFilter as CustomerSearchFilter;
if (filter == null)
{
return("");
}
string sqlStr = @"SELECT * FROM
(SELECT b.BusinessID AS BusinessID
, v.CustomerID
, b.ContractNo
, v.CustomerName
, v.IdenNo
, b.SavingCard
, v.mobile AS Mobile
, v.HouseholdType
, ISNULL(e.NAME,'') AS IsLoanSecond
, v.IsSendMsg
, ROW_NUMBER() OVER(ORDER BY v.CustomerID ASC) AS num
FROM customer.vw_customer_CustomerBasic v
INNER JOIN dbo.Business b ON v.Bid = b.BusinessId
LEFT JOIN dbo.ConstSysEnum e ON e.Fullkey = b.SecondSales
WHERE 1=1 {0} )t {1}";
string pageCondition = string.Format("where t.num between {0} and {1}", filter.FromIndex, filter.ToIndex);
sqlStr = string.Format(sqlStr, GetCondition(baseFilter), pageCondition);
return(sqlStr);
}
/// <summary>
/// 程序执行主入口
/// </summary>
/// <param name="requestEntity"></param>
/// <param name="responseEntity"></param>
protected override void DoExecute(RequestEntity requestEntity, ResponseEntity responseEntity)
{
// 定义接收客户端参数的变量
CustomerSearchFilter filter
= ServiceUtility.ConvertToFilterFromDict <CustomerSearchFilter>(requestEntity.Parameters);
// 客户查询
Singleton <BaseSearchBLL <
CustomerSearchViewData, GetCustomerResultDAL <CustomerSearchViewData> > > .Instance.SearchDataPagingByFilter(filter, responseEntity);
}
public PaginatedList <Customer> GetAllPaginatedWithDetailBySearchFilter(CustomerSearchFilter searchFilter)
{
PaginatedList <Customer> resultList = new PaginatedList <Customer>(new List <Customer>(), 0, searchFilter.CurrentPage, searchFilter.PageSize, searchFilter.SortOn, searchFilter.SortDirection);
using (AppDBContext dbContext = new AppDBContext(_config))
{
var query = from a in dbContext.Customer
select a;
// filtering
if (!string.IsNullOrEmpty(searchFilter.Filter_Name))
{
query = query.Where(r => r.Name.Contains(searchFilter.Filter_Name));
}
if (!string.IsNullOrEmpty(searchFilter.Filter_LastName))
{
query = query.Where(r => r.LastName.Contains(searchFilter.Filter_LastName));
}
// asnotracking
query = query.AsNoTracking();
//total count
var totalCount = query.Count();
//sorting
if (!string.IsNullOrEmpty(searchFilter.SortOn))
{
// using System.Linq.Dynamic.Core; nuget paketi ve namespace eklenmelidir, dynamic order by yapmak icindir
query = query.OrderBy(searchFilter.SortOn + " " + searchFilter.SortDirection.ToUpper());
}
else
{
// deefault sıralama vermek gerekiyor yoksa skip metodu hata veriyor ef 6'da -- 28.10.2019 15:40
// https://stackoverflow.com/questions/3437178/the-method-skip-is-only-supported-for-sorted-input-in-linq-to-entities
query = query.OrderBy(r => r.Id);
}
//paging
query = query.Skip((searchFilter.CurrentPage - 1) * searchFilter.PageSize).Take(searchFilter.PageSize);
resultList = new PaginatedList <Customer>(
query.ToList(),
totalCount,
searchFilter.CurrentPage,
searchFilter.PageSize,
searchFilter.SortOn,
searchFilter.SortDirection
);
}
return(resultList);
}
/// <summary>
/// 程序执行主入口
/// </summary>
/// <param name="requestEntity"></param>
/// <param name="responseEntity"></param>
protected override void DoExecute(RequestEntity requestEntity, ResponseEntity responseEntity)
{
// 定义接收客户端参数的变量
CustomerSearchFilter filter
= ServiceUtility.ConvertToFilterFromDict <CustomerSearchFilter>(requestEntity.Parameters);
filter.UserId = responseEntity.UserId;
// 更新客户发送短信标志
Singleton <BaseUpdateBLL <UpdateCustomerSendMessgeDAL> > .Instance.UpdateData(filter, responseEntity);
}
public async Task <HttpResponseMessage> Get(CustomerSearchFilter filter)
{
if (filter != null)
{
var session = (Session)ContextOperator.Get(ContextKeys.SESSION_ID);
filter.CompanyId = session.CompanyId;
var collection = await Task.Run(() => { return(this._customerService.Get(filter)); });
return(Request.CreateResponse(HttpStatusCode.OK, new ResponseMessage <object>(true, "Succeeded", collection)));
}
return(Request.CreateResponse(HttpStatusCode.OK, new ResponseMessage <object>(false, MessageString.INVALID_REQUEST_PARMS, null)));
}
/// <summary>
/// 获取检索条件
/// </summary>
/// <param name="baseFilter"></param>
/// <returns></returns>
private string GetCondition(BaseFilter baseFilter)
{
CustomerSearchFilter filter = baseFilter as CustomerSearchFilter;
if (filter == null)
{
return("");
}
StringBuilder conditionSbl = new StringBuilder();
if (filter.BusinessId > 0)
{
conditionSbl.AppendFormat(" AND b.BusinessID ={0} ", filter.BusinessId);
}
if (!string.IsNullOrEmpty(filter.ContractNO))
{
conditionSbl.AppendFormat(" AND b.ContractNo ='{0}' ", filter.ContractNO);
}
if (!string.IsNullOrEmpty(filter.Mobile))
{
conditionSbl.AppendFormat(" AND v.Mobile='{0}'", filter.Mobile);
}
if (!string.IsNullOrEmpty(filter.CustomerName))
{
conditionSbl.AppendFormat(" AND v.CustomerName='{0}'", filter.CustomerName);
}
if (!string.IsNullOrEmpty(filter.IdenNO))
{
conditionSbl.AppendFormat(" AND v.IdenNo='{0}'", filter.IdenNO);
}
if (!string.IsNullOrEmpty(filter.DropSources))
{
conditionSbl.AppendFormat(" AND b.fromSource='{0}'", filter.DropSources);
}
if (!string.IsNullOrEmpty(filter.CompanyKey))
{
conditionSbl.AppendFormat(" AND b.ServiceSideKey='{0}'", filter.CompanyKey);
}
if (!string.IsNullOrEmpty(filter.LendingKey))
{
conditionSbl.AppendFormat(" AND b.LendingSideKey='{0}'", filter.LendingKey);
}
return(conditionSbl.ToString());
}
public ActionResult List()
{
ListViewModel model = new ListViewModel();
model.Filter = new ListFilterViewModel();
model.CurrentPage = 1;
model.PageSize = 10;
CustomerSearchFilter searchFilter = new CustomerSearchFilter();
searchFilter.CurrentPage = model.CurrentPage.HasValue ? model.CurrentPage.Value : 1;
searchFilter.PageSize = model.PageSize.HasValue ? model.PageSize.Value : 10;
searchFilter.SortOn = model.SortOn;
searchFilter.SortDirection = model.SortDirection;
searchFilter.Filter_Name = model.Filter.Filter_Name;
searchFilter.Filter_LastName = model.Filter.Filter_LastName;
model.DataList = _customerService.GetAllPaginatedWithDetailBySearchFilter(searchFilter);
return(View(model));
}
/// <summary>
/// 获取检索数据的存储过程名
/// </summary>
/// <param name="baseFilter"></param>
/// <returns></returns>
protected override string GetUpdateSql(BaseFilter baseFilter)
{
CustomerSearchFilter filter = baseFilter as CustomerSearchFilter;
if (filter == null)
{
return("");
}
StringBuilder sb = new StringBuilder();
sb.Append(" UPDATE customer.vw_customer_CustomerBasic ");
sb.Append(" SET IsSendMsg = ~IsSendMsg");
sb.Append(" WHERE IdenNo = '{0}' ");
sb.Append(" AND Bid = {1} ");
_logger.Info(sb.ToString().StringFormat(filter.IdenNO, filter.BusinessId));
return(sb.ToString().StringFormat(filter.IdenNO, filter.BusinessId));
}
public IEnumerable <Customer> Get(CustomerSearchFilter filter)
{
try
{
if (filter == null)
{
throw new ArgumentNullException("filter");
}
if (string.IsNullOrEmpty(filter.CompanyId))
{
throw new ArgumentNullException("CompanyId");
}
if (string.IsNullOrEmpty(filter.Keyword))
{
filter.Keyword = "*";
}
if (filter.Take <= 0)
{
filter.Take = 100;
}
var status = filter.Status != null? filter.Status: (byte)StatusType.Active;
Expression <Func <Customer, bool> > predicate = x =>
x.CompanyId == filter.CompanyId && x.Status == status &&
(filter.Keyword == "*" || x.FirstName.StartsWith(filter.Keyword) || x.LastName.StartsWith(filter.Keyword) || x.MiddleName.StartsWith(filter.Keyword) || x.Company.StartsWith(filter.Keyword));
return(this._customerRepository.Find(predicate).Skip(filter.Start).Take(filter.Take).ToList());
}
catch (Exception ex)
{
throw;
}
}
public LookupCustomerDuplicateViewModel()
{
_CustomerSearchFilter = new CustomerSearchFilter();
}
public IEnumerable <CustomerEntity> GetCustomerList(CustomerSearchFilter searchFilter)
{
_customerDataAccess = new CustomerDataAccess(_context);
return(_customerDataAccess.GetCustomerList(searchFilter));
}
