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); }