public QueryResult SimpleQuery(CustomerSimpleQueryFilter msg)
{
int totalCount;
var dataTable = ObjectFactory <ICustomerQueryDA> .Instance.SimpleQuery(msg, out totalCount);
return(new QueryResult()
{
Data = dataTable,
TotalCount = totalCount
});
}
public void LoadCustomerByID(string customerID, EventHandler <RestClientEventArgs <dynamic> > callback)
{
CustomerSimpleQueryFilter filter = new CustomerSimpleQueryFilter();
filter.CustomerID = customerID;
filter.CompanyCode = CPApplication.Current.CompanyCode;
filter.PagingInfo = new PagingInfo {
PageIndex = 0, PageSize = int.MaxValue
};
string relativeUrl = "/CustomerService/Customer/Picker";
restClient.QueryDynamicData(relativeUrl, filter, callback);
}
public virtual DataTable SimpleQuery(CustomerSimpleQueryFilter queryCriteria, out int totalCount)
{
PagingInfoEntity pagingEntity = new PagingInfoEntity();
pagingEntity.SortField = queryCriteria.PagingInfo.SortBy;
pagingEntity.MaximumRows = queryCriteria.PagingInfo.PageSize;
pagingEntity.StartRowIndex = queryCriteria.PagingInfo.PageIndex * queryCriteria.PagingInfo.PageSize;
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("Customer_SimpleQuery");
using (var sb = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "SysNo DESC"))
{
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"C.SysNo", DbType.Int32, "@SystemNumber",
QueryConditionOperatorType.Equal,
queryCriteria.CustomerSysNo);
sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "C.CustomerID", DbType.String, "@CustomerID", QueryConditionOperatorType.LeftLike, queryCriteria.CustomerID);
sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "C.CustomerName", DbType.String, "@CustomerName", QueryConditionOperatorType.LeftLike, queryCriteria.CustomerName);
// sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "C.MemberShipCard", DbType.String, "@MemberShipCard", QueryConditionOperatorType.LeftLike, queryCriteria.CustomerName);
sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "C.Email", DbType.AnsiString, "@Email", QueryConditionOperatorType.LeftLike, queryCriteria.CustomerEmail);
sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "C.Status", DbType.AnsiStringFixedLength, "@Status", QueryConditionOperatorType.Equal, queryCriteria.Status);
if (!string.IsNullOrEmpty(queryCriteria.CustomerPhone))
{
//添加CustomerPhone子查询，并添加一个参数
sb.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, "", QueryConditionOperatorType.Exist,
@"SELECT 1 FROM OverseaCustomerManagement.[dbo].[CustomerPhone] WITH(NOLOCK)
WHERE OverseaCustomerManagement.[dbo].[CustomerPhone].Phone like '%'+@Phone+'%'
AND OverseaCustomerManagement.[dbo].CustomerPhone.CustomerSysNo = C.SysNo" );
cmd.AddInputParameter("@Phone", DbType.String, queryCriteria.CustomerPhone);
}
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"C.CompanyCode", DbType.AnsiStringFixedLength, "@CompanyCode",
QueryConditionOperatorType.Equal,
queryCriteria.CompanyCode);
cmd.CommandText = sb.BuildQuerySql();
EnumColumnList enumColumnList = new EnumColumnList();
enumColumnList.Add(2, typeof(CustomerStatus));
enumColumnList.Add(30, typeof(CustomerType));
enumColumnList.Add(17, typeof(CustomerRank));
enumColumnList.Add(19, typeof(VIPRank));
DataTable dt = cmd.ExecuteDataTable();
totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(dt);
}
}
