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 List <ErrorMessage> Validate()
{
var errors = base.Validate();
if (SupplierIdEqual != null && SupplierIdNonEqual != null && SupplierIdEqual.Intersect(SupplierIdNonEqual).Any())
{
errors.Add(new ErrorMessage("SupplierIdEqual", "Один и тот же поставщик не может одновременно входить в список выбранных и игнорируемых"));
errors.Add(new ErrorMessage("SupplierIdNonEqual", "Один и тот же поставщик не может одновременно входить в список выбранных и игнорируемых"));
}
if (!AllCatalog && (CatalogIdEqual == null || CatalogIdEqual.Count == 0))
{
errors.Add(new ErrorMessage("CatalogIdEqual", "Не выбраны товары"));
}
return(errors);
}
public override List <ErrorMessage> Validate()
{
var errors = base.Validate();
if (SupplierIdEqual != null && SupplierIdNonEqual != null && SupplierIdEqual.Intersect(SupplierIdNonEqual).Any())
{
errors.Add(new ErrorMessage("SupplierIdEqual", "Один и тот же поставщик не может одновременно входить в список выбранных и игнорируемых"));
errors.Add(new ErrorMessage("SupplierIdNonEqual", "Один и тот же поставщик не может одновременно входить в список выбранных и игнорируемых"));
}
if (Var == CatalogVar.SelectedProducts && (CatalogIdEqual == null || CatalogIdEqual.Count == 0))
{
errors.Add(new ErrorMessage("CatalogIdEqual", "Не выбраны товары"));
}
if (Var == 0)
{
errors.Add(new ErrorMessage("Var", "Не указан вариант подготовки отчета"));
}
return(errors);
}
