public virtual void Validate()
{
if (!Regions.Any())
{
throw new InvalidOperationException($"Config needs values for the {nameof(Regions)} property.");
}
if (Regions.Any(string.IsNullOrWhiteSpace))
{
throw new InvalidOperationException($"Config cannot have a blank entry for the {nameof(Regions)} property.");
}
var duplicateRegions = Regions
.GroupBy(x => x)
.Where(y => y.Count() > 1)
.Select(r => r.Key)
.ToList();
if (duplicateRegions.Count > 0)
{
var regionsText = string.Join(",", duplicateRegions);
throw new InvalidOperationException($"Config has duplicates in {nameof(Regions)} for '{regionsText}'.");
}
if (MessageSubjectProvider == null)
{
throw new InvalidOperationException($"Config cannot have a null for the {nameof(MessageSubjectProvider)} property.");
}
}
public virtual void Validate()
{
if (!Regions.Any() || string.IsNullOrWhiteSpace(Regions.First()))
{
throw new ArgumentNullException("config.Regions", "Cannot have a blank entry for config.Regions");
}
}
private bool regionAlreadyExists(string pNew)
{
if (Regions.Any(p => p != m_currentRegion && p.RegionName.Equals(pNew, StringComparison.OrdinalIgnoreCase)))
{
MessageBox.Show(string.Format("A Region already exists with the name '{0}'", pNew));
return(true);
}
return(false);
}
public string GetRegionNames()
{
var result = "";
if (Regions != null && Regions.Any())
{
result = Session.Query <Region>().Where(x => Regions.Contains(x.Id)).Select(x => x.Name).OrderBy(x => x).ToList().Implode();
}
return(result);
}
public virtual void Validate()
{
if (!Regions.Any() || string.IsNullOrWhiteSpace(Regions.First()))
{
throw new ArgumentNullException("config.Regions", "Cannot have a blank entry for config.Regions");
}
var duplicateRegion = Regions.GroupBy(x => x).FirstOrDefault(y => y.Count() > 1);
if (duplicateRegion != null)
{
throw new ArgumentException(string.Format("Region {0} was added multiple times", duplicateRegion.Key));
}
}
private Expression <Func <VehicleToDriveTypeDocument, bool> > AddRegionFilter(Expression <Func <VehicleToDriveTypeDocument, bool> > filterEx)
{
if (Regions == null || !Regions.Any())
{
return(filterEx);
}
Expression <Func <VehicleToDriveTypeDocument, bool> > regionFilterEx = null;
foreach (var region in Regions)
{
regionFilterEx = regionFilterEx.OrElse(x => x.RegionName == region);
}
return(filterEx.AndAlso(regionFilterEx));
}
public Region GetRandom()
{
if (!Regions.Any())
{
return(DefaultRegion);
}
var enableds = Regions.Where(x => !x.Disabled).ToList();
if (enableds.Count == 1)
{
return(enableds.First());
}
var random = new Random();
var next = random.Next(0, enableds.Count);
return(enableds.ElementAt(next));
}
public bool TextFound()
{
return(Regions != null && Regions.Any());
}
public void Search(DataContext db)
{
if (!ShopList.Any())
{
Result = new List <Order>();
return;
}
IQueryable <Order> result = null;
if (ListOverride != null && ListOverride.Any())
{
Result = db.Orders.Where(x => ListOverride.Contains(x.ID)).ToList();
return;
}
if (!ShopID.HasValue)
{
ShopID = HttpContext.Current.Request.QueryString["ShopID"].ToNullInt();
}
if (!ShopID.HasValue)
{
ShopID = ShopList.First().Value.ToInt();
}
if (CurrentUser.UserRoles.Contains("ShopOwner"))
{
var orderAuthors =
db.ShopManagers.Where(x => x.Manager.ShopOwnerID == CurrentUser.ID)
.Select(x => x.Manager.ManagerUserID)
.ToList();
orderAuthors.Add(CurrentUser.ID);
result = db.Orders.Where(x => orderAuthors.Contains(x.AddedByID));
result =
result.Where(x => x.ShopID == ShopID.Value || (!x.ShopID.HasValue && x.AddedByID == CurrentUser.ID));
var contracted =
db.ContractedOrders.Where(
x => x.Contractor.UserBy == CurrentUser.ShopOwnerID && x.Contractor.ShopID == ShopID).Select(x => x.Order);
result = result.Concat(contracted);
}
else if (CurrentUser.UserRoles.Contains("Manager"))
{
var orderAuthors =
db.ShopManagers.Where(x => x.Manager.ShopOwnerID == CurrentUser.ManagerProfiles.First().ShopOwnerID)
.Select(x => x.Manager.ManagerUserID)
.ToList();
orderAuthors.Add(CurrentUser.ManagerProfiles.First().ShopOwnerID);
var shids = db.ShopManagers.Where(x => x.Manager.ManagerUserID == CurrentUser.ID).Select(x => x.ShopID).ToList();
result = db.Orders.Where(x => orderAuthors.Contains(x.AddedByID) && shids.Contains(x.ShopID));
result =
result.Where(x => x.ShopID == ShopID.Value || (!x.ShopID.HasValue && x.AddedByID == CurrentUser.ID));
var contracted =
db.ContractedOrders.Where(
x => x.Contractor.UserFor == CurrentUser.ShopOwnerID && x.Contractor.ShopID == ShopID)
.Select(x => x.Order);
result = result.Concat(contracted);
}
else if (CurrentUser.UserRoles.Contains("Operator"))
{
var shds = db.OperatorShops.Where(x => x.UserID == CurrentUser.ID).Select(x => x.ShopID).ToList();
result = db.Orders.Where(x => shds.Contains(x.ShopID ?? 0));
}
else
{
result = db.Orders.Where(x => x.AddedByID == CurrentUser.ID);
}
if (MinDate.HasValue)
{
result =
result.Where(
x =>
x.CreateDate.Date >= MinDate.Value);
}
if (MaxDate.HasValue)
{
result =
result.Where(
x =>
x.CreateDate <= MaxDate.Value);
}
if (MinDateDelivery.HasValue)
{
result =
result.Where(x => x.DeliveryDate.HasValue && x.DeliveryDate.Value.Date >= MinDateDelivery.Value.Date);
}
if (MaxDateDelivery.HasValue)
{
result =
result.Where(x => x.DeliveryDate.HasValue && x.DeliveryDate.Value.Date <= MaxDateDelivery.Value.Date);
}
if (LoginOrName.IsFilled())
{
result =
result.Where(
x => x.Consumer != null && (
SqlMethods.Like(x.Consumer.Name.ToLower(), LoginOrNameLike) ||
SqlMethods.Like(x.Consumer.Surname.ToLower(), LoginOrNameLike) ||
SqlMethods.Like(x.Consumer.Phone.ToLower(), LoginOrNameLike)));
}
if (Status.HasValue)
{
result = result.Where(x => x.Status == Status.Value);
}
var chars =
DB.ShopProductChars.Where(
x => x.UserID == CurrentUser.ShopOwnerID && (!x.ShopID.HasValue || x.ShopID == ShopID) && x.IsMain)
.OrderBy(x => x.Name).ToList();
ShopChars = chars.ToList();
if (Overage)
{
result = result.Where(x => x.Marks.Any() && x.Marks.First().MarkDate > DateTime.Now.AddDays(-14));
}
if (ExactDateDelivery.HasValue)
{
result =
result.Where(
x => x.DeliveryDate.HasValue && x.DeliveryDate.Value.Date == ExactDateDelivery.Value.Date);
}
if (Regions.Any())
{
IQueryable <Order> filtered = null;
foreach (var region in Regions)
{
if (filtered == null)
{
filtered =
result.Where(
x => x.OrderDelivery != null && x.OrderDelivery.Region.ToLower() == region.ToLower());
}
else
{
filtered = filtered.Concat(result.Where(
x => x.OrderDelivery != null && x.OrderDelivery.Region.ToLower() == region.ToLower()));
}
}
result = filtered;
}
Result = result.OrderByDescending(x => x.Status).ThenByDescending(x => x.ID).Take(2000).ToList();
if (CurrentUser.UserRoles.Contains("Operator"))
{
Result = Result.Where(x => x.Status == 100 && x.DeliveryDate.HasValue).OrderBy(x => x.Marks.Any()).ThenBy(x => x.DeliveryDate).ToList();
}
}
public IList <WhoWasNotUpdatedField> Find(bool forExcel)
{
var regionMask = SecurityContext.Administrator.RegionMask;
if (Regions != null && Regions.Any())
{
ulong mask = 0;
foreach (var region in Regions)
{
mask |= region;
}
regionMask &= mask;
}
var result = Session.CreateSQLQuery($@"
drop temporary table if exists Customers.UserSource;
create temporary table Customers.UserSource (
UserId int unsigned,
primary key(UserId)
) engine = memory;
INSERT INTO customers.UserSource
select u.Id
from Customers.Users u
join usersettings.AssignedPermissions ap on ap.UserId = u.id
join Customers.Clients c on c.Id = u.ClientId
join Usersettings.RetClientsSet rcs on rcs.ClientCode = c.Id
where ap.PermissionId in (1, 81)
and u.Enabled = 1
and u.PayerId <> 921
and u.ExcludeFromManagerReports = 0
and c.Status = 1
and rcs.ServiceClient = 0
and rcs.InvisibleOnFirm = 0
and rcs.OrderRegionMask & u.OrderRegionMask & :RegionCode > 0
group by u.Id;
drop temporary table if exists Customers.UserSource2;
create temporary table Customers.UserSource2 (
UserId int unsigned,
primary key(UserId)
) engine = memory;
insert into Customers.UserSource2
select * from Customers.UserSource;
DROP TEMPORARY TABLE IF EXISTS customers.oneUserDate;
CREATE TEMPORARY TABLE customers.oneUserDate (
UserId INT unsigned,
AddressId INT unsigned) engine=MEMORY;
INSERT
INTO customers.oneUserDate
SELECT u1.id, ua1.AddressId
FROM customers.Users U1
join Customers.UserSource us on us.UserId = u1.Id
join customers.UserAddresses ua1 on ua1.UserId = u1.id
join customers.Addresses a1 on a1.id = ua1.AddressId
join usersettings.UserUpdateInfo uu1 on uu1.userid = u1.id
where uu1.UpdateDate < :beginDate
group by u1.id
having count(a1.id) = 1;
DROP TEMPORARY TABLE IF EXISTS customers.oneUser;
CREATE TEMPORARY TABLE customers.oneUser (
UserId INT unsigned,
AddressId INT unsigned) engine=MEMORY ;
INSERT
INTO customers.oneUser
SELECT u1.id, ua1.AddressId
FROM customers.Users U1
join Customers.UserSource us on us.UserId = u1.Id
join customers.UserAddresses ua1 on ua1.UserId = u1.id
join customers.Addresses a1 on a1.id = ua1.AddressId
group by u1.id
having count(a1.id) = 1;
SELECT
c.id as ClientId,
c.Name as ClientName,
reg.Region as RegionName,
u.Id as UserId,
u.Name as UserName,
c.Registrant as Registrant,
uu.UpdateDate as UpdateDate,
IF(IFNULL(ad.AFTime, '0000-00-00') < IFNULL(ad.AFNetTime, '0000-00-00'), ad.AFNetTime, ad.AFTime) as LastUpdateDate
FROM customers.Users U
join Customers.UserSource us on us.UserId = u.Id
join customers.UserAddresses ua on ua.UserId = u.id
join customers.Addresses a on a.id = ua.AddressId
join usersettings.UserUpdateInfo uu on uu.userid = u.id
join customers.Clients c on c.id = u.ClientId and c.Status = 1
join farm.Regions reg on reg.RegionCode = c.RegionCode
join logs.authorizationdates ad on ad.UserId = u.Id
left join Customers.AnalitFNetDatas nd on nd.UserId = u.Id
where uu.UpdateDate < :beginDate
and ifnull(nd.LastUpdateAt, '2000-01-01') < :beginDate
and c.RegionCode & :RegionCode > 0
group by u.id
having count(a.id) > 1
union
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,
uu.UpdateDate as UpdateDate,
IF(IFNULL(ad.AFTime, '0000-00-00') < IFNULL(ad.AFNetTime, '0000-00-00'), ad.AFNetTime, ad.AFTime) as LastUpdateDate
FROM customers.Users U
join Customers.UserSource2 us on us.UserId = u.Id
join customers.UserAddresses ua on ua.UserId = u.id
join customers.Addresses a on a.id = ua.AddressId
join usersettings.UserUpdateInfo uu on uu.userid = u.id
join customers.Clients c on c.id = u.ClientId and c.Status = 1
join logs.authorizationdates ad on ad.UserId = u.Id
left join accessright.regionaladmins reg on reg.UserName = c.Registrant
join farm.Regions reg on reg.RegionCode = c.RegionCode
left join Customers.AnalitFNetDatas nd on nd.UserId = u.Id
where uu.UpdateDate < :beginDate
and ifnull(nd.LastUpdateAt, '2000-01-01') < :beginDate
and c.RegionCode & :RegionCode > 0
and
u.id in
(
select if ((select count(oud.UserId)
from
customers.oneUserDate oud
where
oud.AddressId = uaddr.AddressId
) = (
select count(ou.UserId)
from
customers.oneUser ou
where
ou.AddressId = uaddr.AddressId)
, uaddr.UserId, 0
)
from customers.UserAddresses as uaddr
where uaddr.UserId = u.id
)
group by u.id
having count(a.id) = 1
order by {SortBy} {SortDirection};")
.SetParameter("beginDate", BeginDate)
.SetParameter("RegionCode", regionMask)
.ToList <WhoWasNotUpdatedField>();
RowsCount = result.Count;
if (forExcel)
{
return(result.ToList());
}
else
{
return(result.Skip(CurrentPage * PageSize).Take(PageSize).ToList());
}
}
public void PrepareAggregatesData()
{
var regionMask = SecurityContext.Administrator.RegionMask;
if (Regions != null && Regions.Any())
{
ulong mask = 0;
foreach (var region in Regions)
{
mask |= region;
}
regionMask &= mask;
}
string createTemporaryTablePart =
@"DROP TEMPORARY TABLE IF EXISTS Customers.Updates;
CREATE TEMPORARY TABLE Customers.Updates (
Id INT unsigned,
Name varchar(50) ,
RegionName varchar(50),
UserCount INT,
AddressCount INT,
CurWeekUpdates INT,
LastWeekUpdates INT,
CurWeekSum INT,
LastWeekSum INT,
CurWeekAddresses INT,
LastWeekAddresses INT,
AutoOrderIs int not null default 0,
AutoOrderCnt int,
AutoOrderCntNet int
) engine=MEMORY;";
var currentQuery = string.Empty;
if (ObjectId > 0)
{
switch (Type)
{
case AnalysisReportType.Client:
currentQuery = " and Cd.Id = :ObjectId";
break;
case AnalysisReportType.User:
currentQuery = " and u.id = :ObjectId";
break;
case AnalysisReportType.Address:
currentQuery = " and a.Id = :ObjectId";
break;
}
}
var queryText = string.Empty;
switch (Type)
{
case AnalysisReportType.Client:
queryText = ClientTypeSqlPart;
break;
case AnalysisReportType.User:
queryText = UserTypeSqlPart;
break;
case AnalysisReportType.Address:
queryText = AddressTypeSqlPart;
break;
}
Session.CreateSQLQuery(createTemporaryTablePart).ExecuteUpdate();
var query = Session.CreateSQLQuery(string.Format(queryText, currentQuery))
.SetParameter("FistPeriodStart", FistPeriod.Begin)
.SetParameter("FistPeriodEnd", FistPeriod.End)
.SetParameter("LastPeriodStart", LastPeriod.Begin)
.SetParameter("LastPeriodEnd", LastPeriod.End)
.SetParameter("regionCode", regionMask);
if (ObjectId > 0)
{
query.SetParameter("ObjectId", ObjectId);
}
query.ExecuteUpdate();
}
public byte[] Excel()
{
var wb = new Workbook();
var ws = new Worksheet("Кто обновлялся и не делал заказы");
var row = 0;
var colShift = 0;
ws.Merge(row, 0, row, 6);
ExcelHelper.WriteHeader1(ws, row, 0, "Кто обновлялся и не делал заказы", false, true);
row++; // 1
ws.Merge(row, 1, row, 2);
ExcelHelper.Write(ws, row, 0, "Регион:", false);
string regionName;
if (Regions != null && Regions.Any())
{
regionName = GetRegionNames();
}
else
{
regionName = "Все";
}
ExcelHelper.Write(ws, row, 1, regionName, false);
row++; // 2
ws.Merge(row, 1, row, 2);
ExcelHelper.Write(ws, row, 0, "Период:", false);
if (UpdatePeriod.Begin != UpdatePeriod.End)
{
ExcelHelper.Write(ws, row, 1,
"С " + UpdatePeriod.Begin.ToString("dd.MM.yyyy") + " по " + UpdatePeriod.End.ToString("dd.MM.yyyy"), false);
}
else
{
ExcelHelper.Write(ws, row, 1, "За " + UpdatePeriod.Begin.ToString("dd.MM.yyyy"), false);
}
row++; // 3
if (Sum.HasValue)
{
ws.Merge(row, 0, row, 3);
ExcelHelper.Write(ws, row, 0, "Сумма заказов на адрес не более: " + Sum.Value, false);
row++;
}
if (Suppliers != null && Suppliers.Any())
{
ws.Merge(row, 0, row, 3);
var supplierName = GetSupplierNames();
ExcelHelper.Write(ws, row, 0, "Те, у кого нет заказов на поставщиков: " + supplierName, false);
row++;
}
ExcelHelper.WriteHeader1(ws, row, 0, "Код клиента", true, true);
ExcelHelper.WriteHeader1(ws, row, 1, "Наименование клиента", true, true);
ExcelHelper.WriteHeader1(ws, row, 2, "Код пользователя", true, true);
ExcelHelper.WriteHeader1(ws, row, 3, "Комментарий пользователя", true, true);
ExcelHelper.WriteHeader1(ws, row, 4, "Регион", true, true);
ExcelHelper.WriteHeader1(ws, row, 5, "Регистратор", true, true);
ExcelHelper.WriteHeader1(ws, row, 6, "Дата обновления", true, true);
ExcelHelper.WriteHeader1(ws, row, 7, "Дата последнего заказа", true, true);
ExcelHelper.WriteHeader1(ws, row, 8, "Включенные поставщики", true, true);
ExcelHelper.WriteHeader1(ws, row, 9, "Отключенные поставщики", true, true);
ExcelHelper.WriteHeader1(ws, row, 10, "Список отключенных", true, true);
ExcelHelper.WriteHeader1(ws, row, 11, "Нет заказов на поставщиков", true, true);
ws.Cells.ColumnWidth[0] = 4000;
ws.Cells.ColumnWidth[1] = 12000;
ws.Cells.ColumnWidth[2] = 4000;
ws.Cells.ColumnWidth[3] = 12000;
ws.Cells.ColumnWidth[4] = 6000;
ws.Cells.ColumnWidth[5] = 8000;
ws.Cells.ColumnWidth[6] = 6000;
ws.Cells.ColumnWidth[7] = 6000;
ws.Cells.ColumnWidth[8] = 6000;
ws.Cells.ColumnWidth[9] = 6000;
ws.Cells.ColumnWidth[10] = 6000;
ws.Cells.ColumnWidth[11] = 6000;
ws.Cells.Rows[row].Height = 514;
row++;
var reportData = Find(true);
foreach (var item in reportData)
{
item.ForExport = true;
ExcelHelper.Write(ws, row, colShift + 0, item.ClientId, true);
ExcelHelper.Write(ws, row, colShift + 1, item.ClientName, true);
ExcelHelper.Write(ws, row, colShift + 2, item.UserId, true);
ExcelHelper.Write(ws, row, colShift + 3, item.UserName, true);
ExcelHelper.Write(ws, row, colShift + 4, item.RegionName, true);
ExcelHelper.Write(ws, row, colShift + 5, item.Registrant, true);
ExcelHelper.Write(ws, row, colShift + 6, item.UpdateDate, true);
ExcelHelper.Write(ws, row, colShift + 7, item.LastOrderDate, true);
ExcelHelper.Write(ws, row, colShift + 8, item.EnabledSupCnt, true);
ExcelHelper.Write(ws, row, colShift + 9, item.DisabledSupCnt, true);
ExcelHelper.Write(ws, row, colShift + 10, item.DisabledSupName, true);
ExcelHelper.Write(ws, row, colShift + 11, item.NoOrderSuppliers, true);
row++;
}
wb.Worksheets.Add(ws);
var ms = new MemoryStream();
wb.Save(ms);
return(ms.ToArray());
}
//Здесь 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());
}
private Expression <Func <GeographicData, bool> > RegionsExpression()
{
return(x => Regions == null || !Regions.Any() || Regions.Contains(x.Region.Code));
}
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);
}
