//Здесь 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);
}
