private void btnSearch_Click(object sender, RoutedEventArgs e)
{
if (PageView.QueryInfo.HasValidationErrors)
{
return;
}
currentType = PageView.QueryInfo.SeachType.Value;
newQueryView = PageView.DeepCopy();
switch (currentType)
{
case VisitSeachType.Visited:
this.dgVisited.Bind();
break;
case VisitSeachType.WaitingVisit:
this.dgWaitingVisit.Bind();
break;
case VisitSeachType.FollowUpVisit:
this.dgFollowUpVisit.Bind();
break;
case VisitSeachType.Maintenance:
this.dgMaintenance.Bind();
break;
case VisitSeachType.FollowUpMaintenance:
this.dgFollowUpMaintenance.Bind();
break;
}
}
//普通查询
private void IsNormalQuery(VisitSeachType type)
{
switch (type)
{
case VisitSeachType.Visited:
newQueryView.QueryInfo.CallResult = null;
newQueryView.QueryInfo.DealStatus = null;
newQueryView.QueryInfo.ConsumeDesire = null;
newQueryView.QueryInfo.IsActivated = null;
newQueryView.QueryInfo.IsMaintain = null;
newQueryView.QueryInfo.LastEditorSysNo = null;
newQueryView.QueryInfo.FromVisitDate = newQueryView.QueryInfo.ToVisitDate = null;
newQueryView.QueryInfo.SpiderOrderDateFrom = newQueryView.QueryInfo.SpiderOrderDateTo = null;
break;
case VisitSeachType.FollowUpVisit:
newQueryView.QueryInfo.CallResult = null;
newQueryView.QueryInfo.ConsumeDesire = null;
newQueryView.QueryInfo.LastEditorSysNo = null;
newQueryView.QueryInfo.FromVisitDate = newQueryView.QueryInfo.ToVisitDate = null;
break;
case VisitSeachType.Maintenance:
newQueryView.QueryInfo.ConsumeDesire = null;
newQueryView.QueryInfo.LastEditorSysNo = null;
newQueryView.QueryInfo.FromVisitDate = newQueryView.QueryInfo.ToVisitDate = null;
newQueryView.QueryInfo.SpiderOrderDateFrom = newQueryView.QueryInfo.SpiderOrderDateTo = null;
break;
case VisitSeachType.FollowUpMaintenance:
newQueryView.QueryInfo.CallResult = null;
newQueryView.QueryInfo.ConsumeDesire = null;
newQueryView.QueryInfo.LastEditorSysNo = null;
newQueryView.QueryInfo.FromVisitDate = newQueryView.QueryInfo.ToVisitDate = null;
newQueryView.QueryInfo.SpiderOrderDateFrom = newQueryView.QueryInfo.SpiderOrderDateTo = null;
break;
}
}
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);
}
