//Здесь join Customers.UserAddresses ua on ua.UserId = u.Id, чтобы отсеять пользователей, у которых нет адресов доставки. public IList <UpdatedAndDidNotDoOrdersField> Find(bool forExport) { var query = new DetachedSqlQuery(); var sb = new StringBuilder(); var regionMask = SecurityContext.Administrator.RegionMask; if (Regions?.Any() == true) { regionMask &= Regions.Aggregate(0ul, (current, region) => current | region); } query.SetParameter("regionMask", regionMask); query.SetParameter("updateDateStart", UpdatePeriod.Begin); query.SetParameter("updateDateEnd", UpdatePeriod.End); sb.AppendLine(@"DROP TEMPORARY TABLE IF EXISTS usr; CREATE TEMPORARY TABLE usr (INDEX idx(UserId) USING HASH) ENGINE MEMORY select c.id as ClientId, c.Name as ClientName, reg.Region as RegionName, u.Id as UserId, u.Name as UserName, if (reg.ManagerName is not null, reg.ManagerName, c.Registrant) as Registrant, if(uu.UpdateDate > ifnull(nd.LastUpdateAt, '2000-01-01'), uu.UpdateDate, ifnull(nd.LastUpdateAt, '2000-01-01')) as UpdateDate, max(oh.`WriteTime`) as LastOrderDate from customers.Clients C join customers.Users u on u.ClientId = c.id join usersettings.RetClientsSet rcs on rcs.ClientCode = c.Id join usersettings.AssignedPermissions ap on ap.UserId = u.Id join Customers.UserAddresses ua on ua.UserId = u.Id join usersettings.UserUpdateInfo uu on uu.UserId = u.id join farm.Regions reg on reg.RegionCode = c.RegionCode left join accessright.regionaladmins reg on reg.UserName = c.Registrant left join orders.OrdersHead oh on (oh.`regioncode` & :regionMask > 0) and oh.UserId = u.id left join Customers.AnalitFNetDatas nd on nd.UserId = u.Id where (c.regioncode & :regionMask > 0) and c.Status = 1 and (( uu.`Updatedate` > :updateDateStart and uu.`Updatedate` < :updateDateEnd ) or ( ifnull(nd.LastUpdateAt, '2000-01-01') > :updateDateStart and ifnull(nd.LastUpdateAt, '2000-01-01') < :updateDateEnd ) ) and rcs.InvisibleOnFirm = 0 and rcs.ServiceClient = 0 and u.Enabled = 1 and u.PayerId <> 921 and rcs.OrderRegionMask & u.OrderRegionMask & :regionMask > 0 and u.SubmitOrders = 0 and ap.PermissionId in (1, 81) group by u.id; "); // вычисление суммы заказов на адрес if (Sum.HasValue && Sum.Value > 0) { sb.AppendLine(@"DROP TEMPORARY TABLE IF EXISTS sa; CREATE TEMPORARY TABLE sa (INDEX idx(UserId) USING HASH) ENGINE MEMORY select distinct usr.UserId from usr left outer join orders.OrdersHead oh on (oh.`regioncode` & :regionMask > 0) and oh.UserId = usr.UserId and oh.`WriteTime` > :updateDateStart and oh.`WriteTime` < :updateDateEnd left join orders.orderslist ol on ol.OrderID = oh.RowID group by usr.UserId, oh.AddressId having IFNULL(SUM(ol.cost),0) < :sumPerAddress; "); query.SetParameter("sumPerAddress", Sum.Value); } sb.AppendLine(@" drop temporary table if exists NoOrders; create temporary table NoOrders ( UserId int unsigned not null, Suppliers varchar(255), primary key (UserId) ) engine = memory; "); if (Suppliers?.Any() == true) { sb.AppendLine(@" drop temporary table if exists TargetSuppliers; create temporary table TargetSuppliers ( SupplierId int unsigned, primary key (SupplierId) ) engine = memory; "); for (var i = 0; i < Suppliers.Length; i++) { sb.AppendLine($"insert into TargetSuppliers(SupplierId) values (:supplier{i});"); query.SetParameter($"supplier{i}", Suppliers[i]); } sb.AppendLine(@" insert into NoOrders(UserId, Suppliers) select d.UserId, group_concat(d.Name) from ( select usr.UserId, s.Name from (usr, TargetSuppliers ts) join Customers.Suppliers s on s.Id = ts.SupplierId join Usersettings.PricesData pd on pd.FirmCode = s.Id left outer join orders.OrdersHead oh on (oh.`regioncode` & :regionMask > 0) and oh.UserId = usr.UserId and oh.`WriteTime` > :updateDateStart and oh.`WriteTime` < :updateDateEnd and pd.PriceCode = oh.PriceCode group by usr.UserId, s.Id having count(oh.RowId) = 0 ) as d group by d.UserId;"); } else { sb.AppendLine(@" insert into NoOrders(UserId) select usr.UserId from usr left outer join orders.OrdersHead oh on (oh.`regioncode` & :regionMask > 0) and oh.UserId = usr.UserId and oh.`WriteTime` > :updateDateStart and oh.`WriteTime` < :updateDateEnd group by usr.UserId having count(oh.RowId) = 0;"); } var join = ""; if (Sum.HasValue && Sum.Value > 0) { join = "join sa on sa.UserId = usr.UserId "; } sb.AppendLine($@" create temporary table SuppliersStat ( UserId int unsigned not null, EnabledSupCnt int not null, DisabledSupCnt int not null, DisabledSuppliers varchar(255), primary key(UserId) ) engine = memory; insert into SuppliersStat(UserId, EnabledSupCnt, DisabledSupCnt, DisabledSuppliers) select d.UserId, sum(not d.DisabledByUser) as EnabledSupCnt, sum(d.DisabledByUser) as DisabledSupCnt, group_concat(d.DisabledSupplier) DisabledSuppliers from ( SELECT u.Id as UserId, supplier.Id as SupplierId, if(up.UserId is null, 1, 0) as DisabledByUser, if(up.UserId is null, supplier.Name, null) as DisabledSupplier FROM Customers.Users u join usr on usr.UserId = u.Id join Customers.Intersection i on i.ClientId = u.ClientId and i.AgencyEnabled = 1 JOIN Customers.Clients drugstore ON drugstore.Id = i.ClientId JOIN usersettings.RetClientsSet r ON r.clientcode = drugstore.Id JOIN usersettings.PricesData pd ON pd.pricecode = i.PriceId JOIN Customers.Suppliers supplier ON supplier.Id = pd.firmcode JOIN usersettings.RegionalData rd ON rd.RegionCode = i.RegionId AND rd.FirmCode = pd.firmcode JOIN usersettings.PricesRegionalData prd ON prd.regioncode = i.RegionId AND prd.pricecode = pd.pricecode left join Customers.UserPrices up on up.PriceId = i.PriceId and up.UserId = ifnull(u.InheritPricesFrom, u.Id) and up.RegionId = i.RegionId WHERE supplier.Disabled = 0 and (supplier.RegionMask & i.RegionId) > 0 AND (drugstore.maskregion & i.RegionId & u.WorkRegionMask) > 0 AND (r.WorkRegionMask & i.RegionId) > 0 AND pd.agencyenabled = 1 AND pd.enabled = 1 AND pd.pricetype <> 1 AND prd.enabled = 1 AND if(not r.ServiceClient, supplier.Id != 234, 1) and i.AvailableForClient = 1 group by u.Id, supplier.Id ) as d group by d.UserId; select usr.ClientId, usr.ClientName, usr.RegionName, usr.UserId, usr.UserName, usr.Registrant, usr.UpdateDate, usr.LastOrderDate, ss.EnabledSupCnt, ss.DisabledSupCnt, DisabledSuppliers as DisabledSupName, no.Suppliers as NoOrderSuppliers from usr join SuppliersStat ss on ss.UserId = usr.UserId join NoOrders no on no.UserId = usr.UserId {join} group by usr.UserId order by {SortBy} {SortDirection} ;"); query.Sql = sb.ToString(); var result = query.GetSqlQuery(Session).ToList <UpdatedAndDidNotDoOrdersField>(); Session.CreateSQLQuery(@"DROP TEMPORARY TABLE IF EXISTS usr; DROP TEMPORARY TABLE IF EXISTS sa; drop temporary table if exists NoOrders; drop temporary table if exists TargetSuppliers; DROP TEMPORARY TABLE IF EXISTS SuppliersStat;") .ExecuteUpdate(); RowsCount = result.Count; if (forExport) { return(result.ToList()); } return(result.Skip(CurrentPage * PageSize).Take(PageSize).ToList()); }
internal string RegionQuery(string paramName) { return(Regions.Count == 0 ? "" : Regions.Aggregate("", (current, region) => current + (paramName + "=" + region + "&"))); }
private Expression <Func <VehicleToBodyStyleConfigDocument, bool> > AddFilter() { Expression <Func <VehicleToBodyStyleConfigDocument, bool> > filterEx = null; if (Makes != null && Makes.Any()) { Expression <Func <VehicleToBodyStyleConfigDocument, bool> > makeFilterEx = Makes.Aggregate <string, Expression <Func <VehicleToBodyStyleConfigDocument, bool> > >(null, (current, make) => current.OrElse(x => x.MakeName == make)); filterEx = filterEx.AndAlso(makeFilterEx); } if (Models != null && Models.Any()) { Expression <Func <VehicleToBodyStyleConfigDocument, bool> > modelFilterEx = Models.Aggregate <string, Expression <Func <VehicleToBodyStyleConfigDocument, bool> > >(null, (current, model) => current.OrElse(x => x.ModelName == model)); filterEx = filterEx.AndAlso(modelFilterEx); } if (SubModels != null && SubModels.Any()) { Expression <Func <VehicleToBodyStyleConfigDocument, bool> > subModelsFilterEx = SubModels.Aggregate <string, Expression <Func <VehicleToBodyStyleConfigDocument, bool> > >(null, (current, subModel) => current.OrElse(x => x.SubModelName == subModel)); filterEx = filterEx.AndAlso(subModelsFilterEx); } if (StartYear != 0) { filterEx = filterEx.AndAlso(x => x.YearId >= StartYear); } if (EndYear != 0) { filterEx = filterEx.AndAlso(x => x.YearId <= EndYear); } if (Regions != null && Regions.Any()) { Expression <Func <VehicleToBodyStyleConfigDocument, bool> > regionsFilterEx = Regions.Aggregate <string, Expression <Func <VehicleToBodyStyleConfigDocument, bool> > >(null, (current, region) => current.OrElse(x => x.RegionName == region)); filterEx = filterEx.AndAlso(regionsFilterEx); } if (VehicleTypes != null && VehicleTypes.Any()) { Expression <Func <VehicleToBodyStyleConfigDocument, bool> > vehicleTypeFilterEx = VehicleTypes.Aggregate <string, Expression <Func <VehicleToBodyStyleConfigDocument, bool> > >(null, (current, vehicleType) => current.OrElse(x => x.VehicleTypeName == vehicleType)); filterEx = filterEx.AndAlso(vehicleTypeFilterEx); } if (VehicleTypeGroups != null && VehicleTypeGroups.Any()) { Expression <Func <VehicleToBodyStyleConfigDocument, bool> > vehicleTypeGroupFilterEx = VehicleTypeGroups.Aggregate <string, Expression <Func <VehicleToBodyStyleConfigDocument, bool> > >(null, (current, vehicleTypeGroup) => current.OrElse(x => x.VehicleTypeGroupName == vehicleTypeGroup)); filterEx = filterEx.AndAlso(vehicleTypeGroupFilterEx); } // body if (BodyNumDoors != null && BodyNumDoors.Any()) { Expression <Func <VehicleToBodyStyleConfigDocument, bool> > bodyNumDoorsFilterEx = BodyNumDoors.Aggregate <string, Expression <Func <VehicleToBodyStyleConfigDocument, bool> > >(null, (current, bodyNumDoor) => current.OrElse(x => x.BodyNumDoors == bodyNumDoor)); filterEx = filterEx.AndAlso(bodyNumDoorsFilterEx); } if (BodyTypes != null && BodyTypes.Any()) { Expression <Func <VehicleToBodyStyleConfigDocument, bool> > bodyTypesFilterEx = BodyTypes.Aggregate <string, Expression <Func <VehicleToBodyStyleConfigDocument, bool> > >(null, (current, bodyType) => current.OrElse(x => x.BodyTypeName == bodyType)); filterEx = filterEx.AndAlso(bodyTypesFilterEx); } // others if (VehicleIds != null && VehicleIds.Any()) { Expression <Func <VehicleToBodyStyleConfigDocument, bool> > vehicleIdFilterEx = VehicleIds.Aggregate <int, Expression <Func <VehicleToBodyStyleConfigDocument, bool> > >(null, (current, vehicleId) => current.OrElse(x => x.VehicleId == vehicleId)); filterEx = filterEx.AndAlso(vehicleIdFilterEx); } if (BodyStyleConfigId != 0) { filterEx = filterEx.AndAlso(x => x.BodyStyleConfigId == BodyStyleConfigId); } return(filterEx); }