Пример #1
0
        public override MySqlCommand GetCmd(MySqlConnection connection)
        {
            var filter = "";
            var join   = "";

            if (AllCatalog)
            {
                if (ProducerId != null)
                {
                    join = $"join Catalogs.Assortment a on a.CatalogId = p.CatalogId and a.ProducerId = {ProducerId}";
                }
            }
            else
            {
                filter += $" and p.CatalogId in {CatalogIdEqual.Implode()}";
            }
            if (ProducerId != null)
            {
                filter += $" and ProducerId = {ProducerId}";
            }
            var regionIds = GetRegions(connection, RegionCodeEqual);

            if (SupplierIdEqual.Count > 0)
            {
                filter += $" and SupplierId in ({SupplierIdEqual.Implode()})";
            }

            if (SupplierIdNonEqual.Count > 0)
            {
                filter += $" and SupplierId not in ({SupplierIdNonEqual.Implode()})";
            }

            var sql = $@"select cn.CatalogName, p.ProducerName, r.RegionName, s.SupplierName, TT.Date, TT.Cost, CAST(TT.Quantity as SIGNED INTEGER) as Quantity from
	(select p.CatalogId, T.ProducerId, T.RegionId, T.SupplierId, T.Date, AVG(T.Cost) as Cost, SUM(Quantity) as Quantity from
		(select ProductId, ProducerId, RegionId, SupplierId, Date, Cost, Quantity
		from reports.AverageCosts a
			join Catalogs.Products p on p.Id = a.ProductId
			{join}
		where Date >= @DateFrom
		and Date < @DateTo
		and RegionId in ({regionIds})
		{filter}) as T
	inner join catalogs.Products p on p.Id = T.ProductId
	group by p.CatalogId, T.ProducerId, T.RegionId, T.SupplierId, T.Date) as TT
left outer join producerinterface.regionnames r on r.RegionCode = TT.RegionId
left outer join producerinterface.catalognames cn on cn.CatalogId = TT.CatalogId
left outer join producerinterface.producernames p on p.ProducerId = TT.ProducerId
left outer join producerinterface.suppliernames s on s.SupplierId = TT.SupplierId
order by cn.CatalogName, p.ProducerName, r.RegionName, s.SupplierName, TT.Date";
            var cmd = new MySqlCommand(sql, connection);

            cmd.Parameters.AddWithValue("@DateFrom", DateFrom);
            cmd.Parameters.AddWithValue("@DateTo", DateTo);
            return(cmd);
        }
        public override MySqlCommand GetCmd(MySqlConnection connection)
        {
            var filter = "";
            var join   = "";

            if (AllCatalog)
            {
                if (ProducerId != null)
                {
                    join = $"join Catalogs.Assortment a on a.CatalogId = CatalogId and a.ProducerId = {ProducerId}";
                }
            }
            else
            {
                filter += $" and CatalogId in {CatalogIdEqual.Implode()}";
            }
            if (ProducerId != null)
            {
                filter += $" and ProducerId = {ProducerId}";
            }
            var regionIds = GetRegions(connection, RegionCodeEqual);

            var sql = $@"select c.CatalogName, p.ProducerName, r.RegionName, s.SupplierName,
T.Summ, CAST(T.PosOrder as SIGNED INTEGER) as PosOrder,
T.DistinctOrderId, T.DistinctAddressId
from
	(select CatalogId, ProducerId, RegionCode, SupplierId,
	Sum(Cost*Quantity) as Summ,
	Sum(Quantity) as PosOrder,
	Count(distinct OrderId) as DistinctOrderId,
	Count(distinct AddressId) as DistinctAddressId
	from producerinterface.RatingReportOrderItems
		{join}
	where RegionCode in ({regionIds})
	and SupplierId in
		(select Id
		from Customers.Suppliers
		where IsVirtual = 0 and IsFederal = 0 and RegionMask & @RegionMask)
	and WriteTime > @DateFrom
	and WriteTime < @DateTo
	{filter}
	group by CatalogId,ProducerId,RegionCode,SupplierId) as T
left join producerinterface.CatalogNames c on c.CatalogId = T.CatalogId
left join producerinterface.ProducerNames p on p.ProducerId = T.ProducerId
left join producerinterface.RegionNames r on r.RegionCode = T.RegionCode
left join producerinterface.SupplierNames s on s.SupplierId = T.SupplierId
order by c.CatalogName asc, T.PosOrder desc";
            var cmd = new MySqlCommand(sql, connection);

            cmd.Parameters.AddWithValue("@DateFrom", DateFrom);
            cmd.Parameters.AddWithValue("@DateTo", DateTo);
            cmd.Parameters.AddWithValue("RegionMask", RegionCodeEqual.Select(x => (ulong)x).Aggregate((y, z) => y | z));
            return(cmd);
        }
Пример #3
0
        public override MySqlCommand GetCmd(MySqlConnection connection)
        {
            var regionIds = GetRegions(connection, RegionCodeEqual);

            var join   = "";
            var filter = "";

            if (Var == CatalogVar.AllAssortment)
            {
                join = "join Catalogs.Assortment a on a.CatalogId = ri.CatalogId";
            }
            else if (Var == CatalogVar.AllCatalog)
            {
                join = $"join Catalogs.Assortment a on a.CatalogId = ri.CatalogId and a.ProducerId = {ProducerId}";
            }
            else
            {
                filter = $"and ri.CatalogId in ({CatalogIdEqual.Implode()})";
            }
            if (SupplierIdNonEqual.Count > 0)
            {
                filter += $" and ri.SupplierId not in ({SupplierIdNonEqual.Implode()})";
            }

            var sql = $@"select c.CatalogName, ri.ProducerId, p.ProducerName, r.RegionName,
Sum(ri.Cost * ri.Quantity) as Summ,
CAST(Sum(ri.Quantity) as SIGNED INTEGER) as PosOrder,
Min(ri.Cost) as MinCost,
Avg(ri.Cost) as AvgCost,
Max(ri.Cost) as MaxCost,
Count(distinct ri.OrderId) as DistinctOrderId,
Count(distinct ri.AddressId) as DistinctAddressId
from producerinterface.RatingReportOrderItems ri
	left join producerinterface.CatalogNames c on c.CatalogId = ri.CatalogId
	left join producerinterface.ProducerNames p on p.ProducerId = ri.ProducerId
	left join producerinterface.RegionNames r on r.RegionCode = ri.RegionCode
	{join}
where ri.IsLocal = 0
	{filter}
	and ri.RegionCode in ({regionIds})
	and ri.WriteTime > @DateFrom
	and ri.WriteTime < @DateTo
group by ri.CatalogId, ri.ProducerId, ri.RegionCode
order by Summ desc";
            var cmd = new MySqlCommand(sql, connection);

            cmd.Parameters.AddWithValue("DateFrom", DateFrom);
            cmd.Parameters.AddWithValue("DateTo", DateTo);
            return(cmd);
        }
        public override MySqlCommand GetCmd(MySqlConnection connection)
        {
            var filter = "";
            var join   = "";

            if (AllCatalog)
            {
                if (ProducerId != null)
                {
                    join = $"join Catalogs.Assortment a on a.CatalogId = i.CatalogId and a.ProducerId = {ProducerId}";
                }
            }
            else
            {
                filter += $" and CatalogId in {CatalogIdEqual.Implode()}";
            }
            if (ProducerId != null)
            {
                filter += $" and ProducerId = {ProducerId}";
            }
            var regionIds = GetRegions(connection, RegionCodeEqual);

            var sql = $@"select ph.PharmacyName, r.RegionName, T.Summ
from
	(select PharmacyId, RegionCode,
	Sum(Cost*Quantity) as Summ
	from producerinterface.RatingReportOrderItems i
		{join}
	where IsLocal = 0
	and RegionCode in ({regionIds})
	{filter}
	and WriteTime > @DateFrom
	and WriteTime < @DateTo
	group by PharmacyId, RegionCode
	order by Summ desc) as T
left join producerinterface.PharmacyNames ph on ph.PharmacyId = T.PharmacyId
left join producerinterface.RegionNames r on r.RegionCode = T.RegionCode";
            var cmd = new MySqlCommand(sql, connection);

            cmd.Parameters.AddWithValue("@DateFrom", DateFrom);
            cmd.Parameters.AddWithValue("@DateTo", DateTo);
            return(cmd);
        }