public IQueryable<Customer> GetCustomersByCriteria(CustomerSearchType searchType, string city, string customerName)
{
IQueryable<Customer> customers = new List<Customer>().AsQueryable();
switch (searchType)
{
case CustomerSearchType.None:
customers = rep.GetAll();
break;
case CustomerSearchType.ByCity:
customers = rep.GetAll().Where(cu => cu.Addresses.Any(a => a.City == city));
break;
case CustomerSearchType.ByName:
customers = rep.GetAll().Where(cu => cu.Name == customerName);
break;
}
return customers;
}
private IList <CustomerListDTO> getCustomers(CustomerSearchType searchType, string name, string city)
{
try
{
GetCustomersRequest request = new GetCustomersRequest();
request.SearchType = searchType;
request.CustomerName = name;
request.City = city;
GetCustomersResponse response = Service.GetCustomersByCriteria(request);
return(response.Customers);
}
catch (Exception ex)
{
if (ExceptionPolicy.HandleException(ex, "PL Policy"))
{
throw;
}
return(new List <CustomerListDTO>());
}
}
public IQueryable <Customer> GetCustomersByCriteria(CustomerSearchType searchType, string city, string customerName)
{
IQueryable <Customer> customers = new List <Customer>().AsQueryable();
switch (searchType)
{
case CustomerSearchType.None:
customers = rep.GetAll();
break;
case CustomerSearchType.ByCity:
customers = rep.GetAll().Where(cu => cu.Addresses.Any(a => a.City == city));
break;
case CustomerSearchType.ByName:
customers = rep.GetAll().Where(cu => cu.Name == customerName);
break;
}
return(customers);
}
/// <summary>
/// build the where clause for the RetrievePagedList method
/// </summary>
/// <param name="existingWhereClause">the where clause that is being built up for querying customers</param>
/// <param name="searchTerm">the terms that we are using to search</param>
/// <param name="searchType">search on customer name/number, NA, or RA info</param>
/// <returns>the complete where clause to search for customers</returns>
private string BuildWhereClauseForCustomerSearch(string existingWhereClause, string searchTerm, CustomerSearchType searchType)
{
StringBuilder whereClause = new StringBuilder();
if (!string.IsNullOrEmpty(existingWhereClause))
{
whereClause.Append(existingWhereClause);
}
if (!string.IsNullOrEmpty(searchTerm))
{
if (whereClause.Length == 0)
{
whereClause.Append(" WHERE ");
}
else
{
whereClause.Append(" AND ");
}
int tempSearchNumber = 0;
bool isNumber = int.TryParse(searchTerm, out tempSearchNumber);
switch (searchType)
{
case CustomerSearchType.NationalAccount:
if (isNumber)
{
if (searchTerm.Length > 2)
{
// search by national account number and sub number
whereClause.Append(string.Format("(u_national_number = '{0}' AND u_national_sub_number ='{1}')", searchTerm.Substring(0, 2), searchTerm.Substring(2)));
}
else
{
// search only by national account number
whereClause.Append("u_national_number = '{SearchTerm}'");
}
}
else
{
// search by national id or national id description
whereClause.Append("(u_national_id_desc LIKE '%{SearchTerm}%' " +
"OR u_national_id LIKE '%{SearchTerm}%')");
}
break;
case CustomerSearchType.RegionalAccount:
if (isNumber)
{
whereClause.Append("u_regional_number = '{SearchTerm}'");
}
else
{
whereClause.Append("(u_regional_id_desc LIKE '%{SearchTerm}%' " +
"OR u_regional_id LIKE '%{SearchTerm}%')");
}
break;
case CustomerSearchType.Customer:
default:
whereClause.Append("(u_customer_number LIKE '%{SearchTerm}%' OR u_name LIKE '%{SearchTerm}%')");
break;
}
}
if (whereClause.Length == 0)
{
return(string.Empty);
}
else
{
string terms = searchTerm.Replace("'", "''");
return(whereClause.ToString().InjectSingleValue("SearchTerm", terms));
}
}
/// <summary>
/// perform the seach for all of the paged customer methods
/// </summary>
/// <param name="paging">paging model</param>
/// <param name="searchTerm">the terms to use during the search</param>
/// <param name="whereClause">the where clause that was built in each of the GetPageCustomer methods</param>
/// <param name="searchType">search on customer name/number, NA, or RA info</param>
/// <returns>paged list of customers</returns>
private PagedResults <Customer> RetrievePagedResults(PagingModel paging, string searchTerm, string whereClause, CustomerSearchType searchType)
{
var queryOrg = new CommerceServer.Foundation.CommerceQuery <KeithLink.Svc.Core.Models.Generated.Organization>("Organization");
queryOrg.SearchCriteria.WhereClause = BuildWhereClauseForCustomerSearch(whereClause, searchTerm, searchType);
queryOrg.SearchCriteria.FirstItemIndex = paging.From.HasValue ? paging.From.Value : 0;
queryOrg.SearchCriteria.NumberOfItemsToReturn = paging.Size.HasValue ? paging.Size.Value : int.MaxValue;
queryOrg.SearchCriteria.ReturnTotalItemCount = true;
if (paging.Sort != null && paging.Sort.Count > 0)
{
queryOrg.SearchCriteria.SortProperties = new List <CommerceSortProperty>();
foreach (var sortOption in paging.Sort)
{
switch (sortOption.Field.ToLower())
{
case "customername":
CommerceSortProperty sortName = new CommerceSortProperty()
{
CommerceEntityModelName = "u_name",
SortDirection = sortOption.SortOrder == SortOrder.Ascending ? SortDirection.Ascending : SortDirection.Descending
};
queryOrg.SearchCriteria.SortProperties.Add(sortName);
break;
case "customernumber":
CommerceSortProperty sortNumber = new CommerceSortProperty()
{
CommerceEntityModelName = "u_customer_number",
SortDirection = sortOption.SortOrder == SortOrder.Ascending ? SortDirection.Ascending : SortDirection.Descending
};
queryOrg.SearchCriteria.SortProperties.Add(sortNumber);
break;
}
}
}
CommerceQueryOperationResponse res = (Svc.Impl.Helpers.FoundationService.ExecuteRequest(queryOrg.ToRequest())).OperationResponses[0] as CommerceQueryOperationResponse;
List <Customer> results = BuildCustomerList(res.CommerceEntities);
return(new PagedResults <Customer>()
{
Results = results.ToList(), TotalResults = res.TotalItemCount ?? 0
});
}
/// <summary>
/// search for customers that belong to the specified user
/// </summary>
/// <param name="paging">paging model</param>
/// <param name="userId">the user that the search is limited to</param>
/// <param name="searchTerm">the terms to use during the search</param>
/// <param name="searchType">search on customer name/number, NA, or RA info</param>
/// <returns>paged list of customers</returns>
public PagedResults <Customer> GetPagedCustomersForUser(PagingModel paging, Guid userId, string searchTerm, CustomerSearchType searchType)
{
var whereClause = string.Format("inner join [BEK_Commerce_profiles].[dbo].[UserOrganizationObject] uoo on oo.u_org_id = uoo.u_org_id WHERE uoo.u_user_id = '{0}' and u_organization_type = '0'", userId.ToCommerceServerFormat());
return(RetrievePagedResults(paging, searchTerm, whereClause, searchType));
}
/// <summary>
/// Search for customers that belong to the list of DSRs
/// </summary>
/// <param name="paging">paging model</param>
/// <param name="searchTerm">the terms to use during the search</param>
/// <param name="dsrList">the DSRs that the search is limited to</param>
/// <param name="searchType">search on customer name/number, NA, or RA info</param>
/// <returns>paged list of customers</returns>
public PagedResults <Customer> GetPagedCustomersForDSR(PagingModel paging, string searchTerm, List <Dsr> dsrList, CustomerSearchType searchType)
{
PagedResults <Customer> returnValue = new PagedResults <Customer>();
System.Text.StringBuilder whereText = new System.Text.StringBuilder();
for (int i = 0; i < dsrList.Count; i++)
{
if (!String.IsNullOrEmpty(dsrList[i].DsrNumber) && !String.IsNullOrEmpty(dsrList[i].Branch))
{
if (i > 0)
{
whereText.Append(" OR ");
}
whereText.AppendFormat("(u_branch_number = '{0}' AND u_dsr_number = '{1}')", dsrList[i].Branch, dsrList[i].DsrNumber);
}
}
if (!String.IsNullOrEmpty(whereText.ToString()))
{
returnValue = RetrievePagedResults(paging, searchTerm, whereText.ToString(), searchType);
}
return(returnValue);
}
/// <summary>
/// Search for customers that belong to the specified DSM
/// </summary>
/// <param name="paging">paging model</param>
/// <param name="dsmNumber">the DSM that the search is limited to</param>
/// <param name="branchId">the DSM's branch</param>
/// <param name="searchTerm">the terms to use during the search</param>
/// <param name="searchType">search on customer name/number, NA, or RA info</param>
/// <returns>paged list of customers</returns>
public PagedResults <Customer> GetPagedCustomersForDSM(PagingModel paging, string dsmNumber, string branchId, string searchTerm, CustomerSearchType searchType)
{
var whereClause = string.Format("WHERE u_organization_type = '0' AND u_dsm_number = '{0}' AND u_branch_number = '{1}'", dsmNumber, branchId);
return(RetrievePagedResults(paging, searchTerm, whereClause, searchType));
}
public PagedResults <Customer> GetPagedCustomersForBranches(PagingModel paging, List <string> branches, string searchTerm, CustomerSearchType searchType)
{
List <string> branchList = new List <string>();
foreach (string branch in branches)
{
branchList.Add(string.Format("'{0}'", branch));
}
var whereClause = string.Format("WHERE u_organization_type = '0' AND u_branch_number IN ({0})", String.Join(",", branchList));
return(RetrievePagedResults(paging, searchTerm, whereClause, searchType));
}
/// <summary>
/// Search for customers that belong to the specified account
/// </summary>
/// <param name="paging">paging model</param>
/// <param name="searchTerm">the terms to use during the search</param>
/// <param name="accountId">the account that the search is limited to</param>
/// <param name="searchType">seach on customer name/number, NA, or RA info</param>
/// <returns>paged list of customers</returns>
public PagedResults <Customer> GetPagedCustomersForAccount(PagingModel paging, string searchTerm, string accountId, CustomerSearchType searchType)
{
var whereClause = string.Format("WHERE u_organization_type = '0' AND u_parent_organization = '{0}'", accountId);
return(RetrievePagedResults(paging, searchTerm, whereClause, searchType));
}
/// <summary>
/// Find customers and put the results into a paged list
/// </summary>
/// <param name="paging"></param>
/// <param name="searchTerm"></param>
/// <param name="searchType"></param>
/// <returns></returns>
public PagedResults <Customer> GetPagedCustomers(PagingModel paging, string searchTerm, CustomerSearchType searchType)
{
var whereClause = "WHERE u_organization_type = '0'";
return(RetrievePagedResults(paging, searchTerm, whereClause, searchType));
}
private IList<CustomerListDTO> getCustomers(CustomerSearchType searchType, string name, string city)
{
try
{
GetCustomersRequest request = new GetCustomersRequest();
request.SearchType = searchType;
request.CustomerName = name;
request.City = city;
GetCustomersResponse response = Service.GetCustomersByCriteria(request);
return response.Customers;
}
catch (Exception ex)
{
if (ExceptionPolicy.HandleException(ex, "PL Policy")) throw;
return new List<CustomerListDTO>();
}
}
