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