public QueryResult QueryCustomerVisit(CustomerVisitQueryFilter request)
{
int totalCount = 0;
return(new QueryResult()
{
Data = CustomerVisitQueryDA.Query(request, out totalCount),
TotalCount = totalCount
});
}
public void Query(CustomerVisitView view, EventHandler <RestClientEventArgs <dynamic> > callback)
{
CustomerVisitQueryFilter filter = ECCentral.Portal.Basic.Utilities.EntityConverter <CustomerVisitQueryVM, CustomerVisitQueryFilter> .Convert(view.QueryInfo, (v, f) =>
{
if (v.CustomerRank.HasValue)
{
f.CustomerRank = (int)v.CustomerRank;
}
f.SeachType = v.SeachType.HasValue ? (int)v.SeachType : (int)VisitSeachType.WaitingVisit;
});
string relativeUrl = "/CustomerService/Visit/Query";
restClient.QueryDynamicData(relativeUrl, filter, callback);
}
private static void ReplaceVisitOrderStrWhere(CustomerVisitQueryFilter filter, CustomDataCommand command)
{
string visitOrderStrWhere = "where 1=1";
if (filter.SpiderOrderDateFrom.HasValue)
{
visitOrderStrWhere += " and OrderDate>='" + filter.SpiderOrderDateFrom + "'";
}
if (filter.SpiderOrderDateTo.HasValue)
{
visitOrderStrWhere += " and OrderDate<'" + filter.SpiderOrderDateTo + "'";
}
command.ReplaceParameterValue("#VisitOrderStrWhere#",
visitOrderStrWhere);
}
private static void AddCustomerConditionsForToVisit(CustomerVisitQueryFilter filter,
CustomDataCommand command,
DynamicQuerySqlBuilder sqlBuilder)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"cus.SysNo",
DbType.Int32, "@CustomerSysNo",
QueryConditionOperatorType.Equal,
filter.CustomerSysNo);
if (!string.IsNullOrEmpty(filter.CustomerID))
{
sqlBuilder.ConditionConstructor.AddCustomCondition(
QueryConditionRelationType.AND,
"cus.CustomerID like @CustomerID");
command.AddInputParameter("@CustomerID", DbType.String, string.Format("{0}%", filter.CustomerID));
}
if (!string.IsNullOrEmpty(filter.CustomerName))
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND,
"cus.CustomerName like @CustomerName");
command.AddInputParameter("@CustomerName", DbType.String, string.Format("{0}%", filter.CustomerName));
}
if (!string.IsNullOrEmpty(filter.Email))
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND,
"cus.Email like @Email");
command.AddInputParameter("@Email", DbType.String, string.Format("{0}%", filter.Email));
}
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"cus.Rank",
DbType.Int32, "@Rank",
QueryConditionOperatorType.Equal,
filter.CustomerRank);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"cus.CellPhone",
DbType.String, "@CellPhone",
QueryConditionOperatorType.Equal,
filter.Phone);
if (!string.IsNullOrEmpty(filter.Address))
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND,
"cus.DwellAddress like @DwellAddress");
command.AddInputParameter("@DwellAddress", DbType.String, string.Format("{0}%", filter.Address));
}
if (filter.ShipType.HasValue)
{
var prefix = string.Empty;
if (filter.ShipTypeCondition == 0)
{
prefix = "=";
}
else if (filter.ShipTypeCondition == 1)
{
prefix = ">";
}
else
{
prefix = "<";
}
if (filter.SeachType == (int)VisitSeachType.WaitingVisit)
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND,
"DATEDIFF(day,TVC.LastBuyDate,GETDATE()) " + prefix + filter.ShipType);
}
else
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND,
"DATEDIFF(day,vc.LastBuyTime,GETDATE()) " + prefix + filter.ShipType);
}
}
//是否VIP(Vip rank 为2或者4)
if (filter.IsVip.HasValue && filter.IsVip.Value)
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND,
"cus.VipRank IN (2,4)");
}
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"cus.ConfirmedTotalAmt",
DbType.Decimal, "@ConfirmedTotalAmt1",
QueryConditionOperatorType.MoreThanOrEqual,
filter.FromTotalAmount);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"cus.ConfirmedTotalAmt",
DbType.Decimal, "@ConfirmedTotalAmt2",
QueryConditionOperatorType.LessThan,
filter.ToTotalAmount);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"TVC.CompanyCode", DbType.AnsiStringFixedLength, "@CompanyCode",
QueryConditionOperatorType.Equal,
filter.CompanyCode);
}
public virtual DataTable Query(CustomerVisitQueryFilter filter, out int totalCount)
{
VisitSeachType searchType = VisitSeachType.WaitingVisit;
if (filter.SeachType.HasValue)
{
searchType = Enum.TryParse <VisitSeachType>(filter.SeachType.Value.ToString(), out searchType) ? searchType : VisitSeachType.WaitingVisit;
}
PagingInfoEntity pagingEntity = new PagingInfoEntity();
pagingEntity.SortField = filter.PageInfo.SortBy;
pagingEntity.MaximumRows = filter.PageInfo.PageSize;
pagingEntity.StartRowIndex = filter.PageInfo.PageIndex * filter.PageInfo.PageSize;
CustomDataCommand command = null;
QuerySorterHelper.SetSorterSQL(pagingEntity);
if (searchType == VisitSeachType.Visited || searchType == VisitSeachType.FollowUpMaintenance)
{
//回访统计查询或者是需跟进维护查询
command = DataCommandManager.CreateCustomDataCommandFromConfig("GetVisitCustomerByMaintenanceQuery");
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(command.CommandText, command, pagingEntity, "VC.SysNo DESC"))
{
AddTheBasicCustomerConditions(filter, command, sqlBuilder);
AddTheQueryVisitCustomerConditions(filter, command, sqlBuilder, searchType);
command.CommandText = sqlBuilder.BuildQuerySql();
ReplaceVisitOrderStrWhere(filter, command);
}
}
else if (searchType == VisitSeachType.Maintenance)
{
command = DataCommandManager.CreateCustomDataCommandFromConfig("GetNeedMaintainCustomers");
if (filter.PageInfo != null && !string.IsNullOrEmpty(pagingEntity.SortField))
{
pagingEntity.SortField = "tempTable.Priority ASC," + pagingEntity.SortField;
}
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(
command.CommandText,
command,
pagingEntity,
"VC.LastCallTime DESC"))
{
AddTheBasicCustomerConditions(filter, command, sqlBuilder);
AddTheQueryVisitCustomerConditions(filter, command, sqlBuilder, searchType);
if (string.IsNullOrEmpty(filter.CustomerSysNos))
{
filter.CustomerSysNos = "null";
}
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"@CustomerSysNoList",
DbType.AnsiString, "@CustomerSysNoList",
QueryConditionOperatorType.Equal,
filter.CustomerSysNos);
command.CommandText = sqlBuilder.BuildQuerySql();
ReplaceVisitOrderStrWhere(filter, command);
}
}
else if (searchType == VisitSeachType.WaitingVisit)
{
//查询首次待会访的客户
command = DataCommandManager.CreateCustomDataCommandFromConfig
("GetFirstVisitingCustomers");
if (filter.PageInfo != null && !string.IsNullOrEmpty(pagingEntity.SortField))
{
pagingEntity.SortField = "tempTable.Priority ASC," + pagingEntity.SortField;
}
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(
command.CommandText,
command,
pagingEntity,
"tempTable.Priority ASC,cus.CustomerSysNo DESC"))
{
int days = 0;
if (!int.TryParse(AppSettingManager.GetSetting("Customer", "VisitCustomerActiveNeedDays"), out days))
{
days = 180;
}
string sql = "(TVC.LastBuyDate > '1990-1-2' AND TVC.LastBuyDate < @LastCallTime AND TVC.LastCallTime < @LastCallTime)";
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, sql);
command.AddInputParameter("@LastCallTime", DbType.DateTime, DateTime.Now.AddDays(-days));
if (string.IsNullOrEmpty(filter.CustomerSysNos))
{
filter.CustomerSysNos = "null";
}
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"@CustomerSysNoList",
DbType.AnsiString, "@CustomerSysNoList",
QueryConditionOperatorType.Equal,
filter.CustomerSysNos);
AddCustomerConditionsForToVisit(filter, command, sqlBuilder);
command.CommandText = sqlBuilder.BuildQuerySql();
}
}
else
{
//查询已经回访过的，但是仍然需要跟进的客户
command = DataCommandManager.CreateCustomDataCommandFromConfig
("GetNeedVisitingCustomers");
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(
command.CommandText,
command,
pagingEntity,
"CS.RegisterTime DESC"))
{
//需要跟进LastCallStatus = 0
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"VC.LastCallStatus",
DbType.Int32, "@LastCallStatus",
QueryConditionOperatorType.Equal,
0);
AddTheBasicCustomerConditions(filter, command, sqlBuilder);
if (filter.IsSpiderSearch)
{
AddTheQueryVisitCustomerConditions(filter,
command,
sqlBuilder,
searchType);
}
command.CommandText = sqlBuilder.BuildQuerySql();
}
}
EnumColumnList ecList = new EnumColumnList();
ecList.Add("Rank", typeof(CustomerRank));
ecList.Add("LastCallStatus", typeof(VisitDealStatus));
ecList.Add("LastMaintenanceStatus", typeof(VisitDealStatus));
ecList.Add("NeedBuy", typeof(YNStatusThree));
ecList.Add("IsActive", typeof(YNStatus));
//ecList.Add("IsRMA", typeof(YNStatus));
ecList.Add("LastCallReason", typeof(VisitCallResult));
DataTable dt = command.ExecuteDataTable(ecList);
totalCount = Convert.ToInt32(command.GetParameterValue("@TotalCount"));
return(dt);
}
private static void AddTheQueryVisitCustomerConditions(CustomerVisitQueryFilter filter, CustomDataCommand command,
DynamicQuerySqlBuilder sqlBuilder, VisitSeachType queryType)
{
if (queryType == VisitSeachType.WaitingVisit)//首次维护
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"VC.IsActive", DbType.Int32, "@IsActive",
QueryConditionOperatorType.Equal,
1);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"VC.IsRMA", DbType.Boolean, "@IsRMA",
QueryConditionOperatorType.Equal,
true);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"ISNULL(VC.LastMaintenanceStatus,-2)", DbType.Int32, "@LastMaintenanceStatus",
QueryConditionOperatorType.Equal,
-2);
}
if (queryType == VisitSeachType.FollowUpMaintenance)//需跟进维护
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"VC.IsActive", DbType.Int32, "@IsActive",
QueryConditionOperatorType.Equal,
1);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"VC.IsRMA", DbType.Boolean, "@IsRMA",
QueryConditionOperatorType.Equal,
true);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"ISNULL(VC.LastMaintenanceStatus,-2)", DbType.Int32, "@LastMaintenanceStatus",
QueryConditionOperatorType.Equal,
0);
}
if (filter.SeachType == (int)VisitSeachType.Visited)//回访统计
{
sqlBuilder.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.AND);
if (filter.IsMaintain.HasValue)
{
if (filter.IsMaintain.Value)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.OR,
"ISNULL(VC.LastMaintenanceStatus,-2)", DbType.Int32, "@LastCallStatus",
QueryConditionOperatorType.Equal,
filter.DealStatus);
}
else if (!filter.IsMaintain.Value)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"VC.LastCallStatus", DbType.Int32, "@LastCallStatus",
QueryConditionOperatorType.Equal,
filter.DealStatus);
}
}
else
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"VC.LastCallStatus", DbType.Int32, "@LastCallStatus",
QueryConditionOperatorType.Equal,
filter.DealStatus);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.OR,
"ISNULL(VC.LastMaintenanceStatus,-2)", DbType.Int32, "@LastCallStatus",
QueryConditionOperatorType.Equal,
filter.DealStatus);
}
sqlBuilder.ConditionConstructor.EndGroupCondition();
}
else
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"VC.LastMaintenanceStatus", DbType.Int32, "@LastMaintenanceStatus",
QueryConditionOperatorType.Equal,
filter.DealStatus);
}
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"VC.CreateDate", DbType.DateTime, "@VisitDateFrom",
QueryConditionOperatorType.MoreThanOrEqual,
filter.FromVisitDate);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"VC.CreateDate", DbType.DateTime, "@VisitDateTo",
QueryConditionOperatorType.LessThan,
filter.ToVisitDate);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"VC.IsActive", DbType.Int32, "@IsActive",
QueryConditionOperatorType.Equal,
filter.IsActivated);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"VC.NeedBuy", DbType.Int32, "@NeedBuy",
QueryConditionOperatorType.Equal,
filter.ConsumeDesire);
if (filter.IsMaintain.HasValue)
{
if (filter.IsMaintain.Value)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"VC.LastMaintenanceStatus", DbType.Int32, "@LastMaintenanceStatus",
QueryConditionOperatorType.NotEqual,
-2);
}
else if (!filter.IsMaintain.Value)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"VC.LastMaintenanceStatus", DbType.Int32, "@LastMaintenanceStatus",
QueryConditionOperatorType.Equal,
-2);
}
}
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"VC.LastEditUserSysNo", DbType.Int32, "@LastEditUserSysNo",
QueryConditionOperatorType.Equal,
filter.LastEditorSysNo);
sqlBuilder.ConditionConstructor.AddInCondition <int>(
QueryConditionRelationType.AND,
"VC.LastCallReason", DbType.Int32,
filter.CallResult);
}
