Пример #1
0
        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.");
            }
        }
Пример #2
0
 public virtual void Validate()
 {
     if (!Regions.Any() || string.IsNullOrWhiteSpace(Regions.First()))
     {
         throw new ArgumentNullException("config.Regions", "Cannot have a blank entry for config.Regions");
     }
 }
Пример #3
0
 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);
 }
Пример #4
0
        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);
        }
Пример #5
0
        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));
        }
Пример #7
0
        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));
        }
Пример #8
0
 public bool TextFound()
 {
     return(Regions != null && Regions.Any());
 }
Пример #9
0
        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();
            }
        }
Пример #10
0
        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());
            }
        }
Пример #11
0
        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());
        }
Пример #14
0
 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);
        }