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);
}
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);
}