protected override void GenerateReport()
{
ProfileHelper.Next("GenerateReport");
var clientName = String.Format("Выбранная аптека : {0}", GetClientsNamesFromSQL(new List <ulong> {
(ulong)SourceFirmCode
}));
Header.Add(clientName);
var concurentClientNames = String.Format("Список аптек-конкурентов : {0}", GetClientsNamesFromSQL(concurrentGroups[0]));
Header.Add(concurentClientNames);
if (AddressRivals.Count > 0)
{
Header.Add(String.Format("Список адресов доставки-конкурентов : {0}", ReadAddress(AddressRivals)));
}
ProfileHelper.Next("GenerateReport2");
var regionMask = GetClientRegionMask();
var selectCommand = BuildSelect();
var rivalFilter = String.Format("oh.ClientCode in ({0})", concurrentGroups[0].Implode());
if (AddressRivals.Count > 0)
{
rivalFilter += String.Format(" and oh.AddressId in ({0})", AddressRivals.Implode());
}
if (IncludeProducerName)
{
selectCommand = selectCommand.Replace("cfc.Id", "if(c.Pharmacie = 1, cfc.Id, 0) as cfc_id")
.Replace("cfc.Name", "if(c.Pharmacie = 1, cfc.Name, 'Нелекарственный ассортимент')");
}
var filter = " and (oh.RegionCode & " + regionMask + @") > 0 ";
if (HideJunk)
{
filter = " and ol.Junk = 0 ";
Header.Add("Из отчета исключены уцененные товары и товары с ограниченным сроком годности");
}
CheckSuppliersCount(filter);
selectCommand = String.Concat(selectCommand, String.Format(@"
sum(if(oh.ClientCode = {0}, ol.cost*ol.quantity, NULL)) as SourceFirmCodeSum,
sum(if(oh.ClientCode = {0}, ol.quantity, NULL)) SourceFirmCodeRows,
Min(if(oh.ClientCode = {0}, ol.cost, NULL)) as SourceFirmCodeMinCost,
Avg(if(oh.ClientCode = {0}, ol.cost, NULL)) as SourceFirmCodeAvgCost,
Max(if(oh.ClientCode = {0}, ol.cost, NULL)) as SourceFirmCodeMaxCost,
Count(distinct if(oh.ClientCode = {0}, oh.RowId, NULL)) as SourceFirmDistinctOrderId,
sum(if({1}, ol.cost*ol.quantity, NULL)) as RivalsSum,
sum(if({1}, ol.quantity, NULL)) RivalsRows,
Min(if({1}, ol.cost, NULL)) as RivalsMinCost,
Avg(if({1}, ol.cost, NULL)) as RivalsAvgCost,
Max(if({1}, ol.cost, NULL)) as RivalsMaxCost,
Count(distinct if({1}, oh.RowId, NULL)) as RivalsDistinctOrderId,
Count(distinct if({1}, oh.AddressId, NULL)) as RivalsDistinctAddressId,
sum(ol.cost*ol.quantity) as AllSum,
sum(ol.quantity) AllRows,
Min(ol.cost) as AllMinCost,
Avg(ol.cost) as AllAvgCost,
Max(ol.cost) as AllMaxCost,
Count(distinct oh.RowId) as AllDistinctOrderId,
Count(distinct oh.AddressId) as AllDistinctAddressId ", SourceFirmCode, rivalFilter));
selectCommand += String.Format(@"from {0}.OrdersHead oh
join {0}.OrdersList ol on ol.OrderID = oh.RowID
join catalogs.products p on p.Id = ol.ProductId
join catalogs.catalog c on c.Id = p.CatalogId
join catalogs.catalognames cn on cn.id = c.NameId
join catalogs.catalogforms cf on cf.Id = c.FormId
left join catalogs.mnn m on cn.MnnId = m.Id
left join catalogs.Producers cfc on cfc.Id = ol.CodeFirmCr
left join Customers.Clients cl on cl.Id = oh.ClientCode
join customers.addresses ad on ad.Id = oh.AddressId
join farm.regions rg on rg.RegionCode = oh.RegionCode
join usersettings.pricesdata pd on pd.PriceCode = oh.PriceCode
join Customers.suppliers prov on prov.Id = pd.FirmCode
join farm.regions provrg on provrg.RegionCode = prov.HomeRegion
join Customers.addresses adr on oh.AddressId = adr.Id
join billing.LegalEntities le on adr.LegalEntityId = le.Id
join billing.payers on payers.PayerId = le.PayerId
where
pd.IsLocal = 0
{1} ", OrdersSchema, filter);
selectCommand = ApplyFilters(selectCommand);
selectCommand = ApplyGroupAndSort(selectCommand, "AllSum desc");
if (IncludeProducerName)
{
var groupPart = selectCommand.Substring(selectCommand.IndexOf("group by"));
var newGroupPart = groupPart.Replace("cfc.Id", "cfc_id");
selectCommand = selectCommand.Replace(groupPart, newGroupPart);
}
if (IncludeProductName)
{
if (isProductName)
{
selectCommand += @"; select
(select concat(c.name, ' ',
cast(GROUP_CONCAT(ifnull(PropertyValues.Value, '')
order by Properties.PropertyName, PropertyValues.Value
SEPARATOR ', '
) as char))
from catalogs.products p
join catalogs.catalog c on c.Id = p.CatalogId
left join catalogs.ProductProperties on ProductProperties.ProductId = p.Id
left join catalogs.PropertyValues on PropertyValues.Id = ProductProperties.PropertyValueId
left join catalogs.Properties on Properties.Id = PropertyValues.PropertyId
where
p.Id = md.pid) ProductName,
md.*
from MixedData md" ;
}
else
{
selectCommand += @"; select
(select c.name
from catalogs.catalog c
where
c.Id = md.pid) CatalogName,
md.*
from MixedData md" ;
}
}
#if DEBUG
Debug.WriteLine(selectCommand);
#endif
var selectTable = new DataTable();
DataAdapter.SelectCommand.CommandText = selectCommand;
DataAdapter.SelectCommand.Parameters.Clear();
DataAdapter.Fill(selectTable);
ProfileHelper.Next("GenerateReport3");
GroupHeaders.Add(new ColumnGroupHeader(clientName,
"SourceFirmCodeSum",
"SourceFirmDistinctOrderId"));
GroupHeaders.Add(new ColumnGroupHeader(
concurentClientNames,
"RivalsSum",
"RivalsDistinctAddressId"));
GroupHeaders.Add(new ColumnGroupHeader(
"Общие данные по рынку",
"AllSum",
"AllDistinctAddressId"));
var result = BuildResultTable(selectTable);
CustomizeResultTableColumns(result);
CopyData(selectTable, result);
ProfileHelper.Next("PostProcessing");
}
protected override void GenerateReport()
{
ProfileHelper.Next("GenerateReport");
_supplierName = String.Format("Выбранный поставщик: {0}", GetValuesFromSQL("select concat(supps.Name, ' - ', rg.Region) as FirmShortName from Customers.suppliers supps, farm.regions rg where rg.RegionCode = supps.HomeRegion and supps.Id = " + SourceFirmCode));
Header.Add(_supplierName);
for (var i = 0; i < concurrentGroups.Count; i++)
{
var ids = concurrentGroups[i];
Header.Add(String.Format("Список поставщиков-конкурентов №{1}: {0}",
GetValuesFromSQL("select concat(supps.Name, ' - ', rg.Region) as FirmShortName from Customers.suppliers supps, farm.regions rg where rg.RegionCode = supps.HomeRegion and supps.Id in (" + ids.Implode() + ") order by supps.Name"),
i + 1));
}
if (ShowCode || ShowCodeCr)
{
CalculateSupplierIds(SourceFirmCode, ShowCode, ShowCodeCr, CodeSource);
}
ProfileHelper.Next("GenerateReport2");
var selectCommand = BuildSelect();
//max(cfc.Name) - судя по реализации mysql игнорирует null для min или max, если cfc.Name есть значение отличное от null
//мы должны выбрать его а не null
if (IncludeProducerName)
{
selectCommand = selectCommand.Replace("cfc.Id", "if(c.Pharmacie = 1, cfc.Id, 0) as cfc_id")
.Replace("cfc.Name", "if(c.Pharmacie = 1, max(cfc.Name), 'Нелекарственный ассортимент')");
}
if (ShowCode)
{
selectCommand += " ProviderCodes.Code, ";
}
if (ShowCodeCr)
{
selectCommand += " ProviderCodes.CodeCr, ";
}
var concurrentSqlBlock = new StringBuilder();
var filter = "";
if (HideJunk)
{
filter = " and ol.Junk = 0 ";
Header.Add("Из отчета исключены уцененные товары и товары с ограниченным сроком годности");
}
var priceIds = Session.Query <PriceList>().Where(x => x.Supplier.Id == SourceFirmCode)
.ToArray()
.Implode(x => x.PriceCode);
if (String.IsNullOrEmpty(priceIds))
{
priceIds = "0";
}
CheckSuppliersCount(filter);
for (var i = 0; i < concurrentGroups.Count; i++)
{
concurrentSqlBlock.AppendFormat(@"
sum(if(pd.firmcode in ({1}), ol.cost*ol.quantity, NULL)) as RivalsSum{0},
sum(if(pd.firmcode in ({1}), ol.quantity, NULL)) as RivalsRows{0},
Min(if(pd.firmcode in ({1}), ol.cost, NULL)) as RivalsMinCost{0},
Avg(if(pd.firmcode in ({1}), ol.cost, NULL)) as RivalsAvgCost{0},
Max(if(pd.firmcode in ({1}), ol.cost, NULL)) as RivalsMaxCost{0},
Count(distinct if(pd.firmcode in ({1}), oh.RowId, NULL)) as RivalsDistinctOrderId{0},
Count(distinct if(pd.firmcode in ({1}), oh.AddressId, NULL)) as RivalsDistinctAddressId{0},
Count(distinct if(pd.firmcode in ({1}), pd.FirmCode, NULL)) as RivalsSuppliersSoldPosition{0},",
i, concurrentGroups[i].Implode());
}
var groupBy = GetGroupSql();
if (IncludeProducerName && IncludeProductName && ShowCode && ShowCodeCr)
{
groupBy = @"group by if(ProviderCodes.Code is null or ProviderCodes.CodeCr = '',
concat(ol.ProductId, '\t', ifnull(cfc_id, 0)), concat(ProviderCodes.Code, '\t', ifnull(ProviderCodes.CodeCr, '')))";
}
else if (IncludeProducerName)
{
groupBy = groupBy.Replace("cfc.Id", "cfc_id");
}
filter += " " + GetFilterSql();
selectCommand += $@"
sum(if(pd.firmcode = {SourceFirmCode}, ol.cost*ol.quantity, NULL)) as SourceFirmCodeSum,
sum(if(pd.firmcode = {SourceFirmCode}, ol.quantity, NULL)) SourceFirmCodeRows,
Min(if(pd.firmcode = {SourceFirmCode}, ol.cost, NULL)) as SourceFirmCodeMinCost,
Avg(if(pd.firmcode = {SourceFirmCode}, ol.cost, NULL)) as SourceFirmCodeAvgCost,
Max(if(pd.firmcode = {SourceFirmCode}, ol.cost, NULL)) as SourceFirmCodeMaxCost,
Count(distinct if(pd.firmcode = {SourceFirmCode}, oh.RowId, NULL)) as SourceFirmDistinctOrderId,
Count(distinct if(pd.firmcode = {SourceFirmCode}, oh.AddressId, NULL)) as SourceFirmDistinctAddressId,
Count(distinct if(pd.firmcode = {SourceFirmCode}, pd.FirmCode, NULL)) as SourceSuppliersSoldPosition,
{concurrentSqlBlock}
sum(ol.cost*ol.quantity) as AllSum,
sum(ol.quantity) AllRows,
Min(ol.cost) as AllMinCost,
Avg(ol.cost) as AllAvgCost,
Max(ol.cost) as AllMaxCost,
Count(distinct oh.RowId) as AllDistinctOrderId,
Count(distinct pd.firmcode) as AllSuppliersSoldPosition,
Count(distinct oh.AddressId) as AllDistinctAddressId
from {OrdersSchema}.OrdersHead oh
join {OrdersSchema}.OrdersList ol on ol.OrderID = oh.RowID
join catalogs.products p on p.Id = ol.ProductId
join catalogs.catalog c on c.Id = p.CatalogId
join catalogs.catalognames cn on cn.id = c.NameId
join catalogs.catalogforms cf on cf.Id = c.FormId
left join catalogs.mnn m on cn.MnnId = m.Id
left join catalogs.Producers cfc on cfc.Id = ol.CodeFirmCr
left join Customers.Clients cl on cl.Id = oh.ClientCode
join customers.addresses ad on ad.Id = oh.AddressId
join farm.regions rg on rg.RegionCode = oh.RegionCode
join usersettings.pricesdata pd on pd.PriceCode = oh.PriceCode
join Customers.suppliers prov on prov.Id = pd.FirmCode
join farm.regions provrg on provrg.RegionCode = prov.HomeRegion
join Customers.addresses adr on oh.AddressId = adr.Id
join billing.LegalEntities le on adr.LegalEntityId = le.Id
join billing.payers on payers.PayerId = le.PayerId
left join (
select * from Farm.Synonym
where Fresh = 1 and PriceCode in ({priceIds})
group by ProductId
) as s on s.ProductId = ol.ProductId " +
((ShowCode || ShowCodeCr) ? " left join ProviderCodes on ProviderCodes.CatalogCode = " + nameField.primaryField +
((firmCrField != null ?
$" and ifnull(ProviderCodes.CodeFirmCr, 0) = if(c.Pharmacie = 1, ifnull({firmCrField.primaryField}, 0), 0)"
: String.Empty)) : String.Empty) +
$@"
where pd.IsLocal = 0
{filter}
{groupBy}
order by AllSum desc
";
//selectCommand = ApplyFilters(selectCommand);
if (IncludeProductName)
{
if (isProductName)
{
selectCommand += @"; select
(select concat(c.name, ' ',
cast(GROUP_CONCAT(ifnull(PropertyValues.Value, '')
order by Properties.PropertyName, PropertyValues.Value
SEPARATOR ', '
) as char))
from catalogs.products p
join catalogs.catalog c on c.Id = p.CatalogId
left join catalogs.ProductProperties on ProductProperties.ProductId = p.Id
left join catalogs.PropertyValues on PropertyValues.Id = ProductProperties.PropertyValueId
left join catalogs.Properties on Properties.Id = PropertyValues.PropertyId
where
p.Id = md.pid) ProductName,
md.*
from MixedData md" ;
}
else
{
selectCommand += @"; select
(select c.name
from catalogs.catalog c
where
c.Id = md.pid) CatalogName,
md.*
from MixedData md" ;
}
}
var selectTable = new DataTable();
DataAdapter.SelectCommand.CommandText = selectCommand;
DataAdapter.SelectCommand.Parameters.Clear();
#if DEBUG
ProfileHelper.WriteLine(DataAdapter.SelectCommand);
#endif
DataAdapter.Fill(selectTable);
ProfileHelper.Next("GenerateReport3");
if (!HideSupplierStat)
{
GroupHeaders.Add(new ColumnGroupHeader(
_supplierName,
"SourceFirmCodeSum",
"SourceSuppliersSoldPosition"));
}
for (var i = 0; i < concurrentGroups.Count; i++)
{
GroupHeaders.Add(new ColumnGroupHeader(
$"Список поставщиков-конкурентов №{i + 1}",
"RivalsSum" + i,
"RivalsSuppliersSoldPosition" + i));
}
GroupHeaders.Add(new ColumnGroupHeader(
"Общие данные по рынку",
"AllSum",
"AllSuppliersSoldPosition"));
var res = BuildResultTable(selectTable);
DataColumn dc;
if (ShowCode)
{
dc = res.Columns.Add("Code", typeof(String));
dc.Caption = "Код";
dc.ExtendedProperties.Add("Width", (int?)20);
dc.SetOrdinal(0);
}
if (ShowCodeCr)
{
dc = res.Columns.Add("CodeCr", typeof(String));
dc.Caption = "Код изготовителя";
dc.ExtendedProperties.Add("Width", (int?)20);
dc.SetOrdinal(1);
}
if (!HideSupplierStat)
{
var groupColor = Color.FromArgb(197, 217, 241);
dc = res.Columns.Add("SourceFirmCodeSum", typeof(Decimal));
dc.Caption = "Сумма по поставщику";
dc.ExtendedProperties.Add("Color", groupColor);
dc.ExtendedProperties.Add("Width", (int?)8);
dc = res.Columns.Add("SourceFirmCodeRows", typeof(Int32));
dc.Caption = "Кол-во по поставщику";
dc.ExtendedProperties.Add("Color", groupColor);
dc.ExtendedProperties.Add("Width", (int?)4);
dc = res.Columns.Add("SourceFirmCodeMinCost", typeof(Decimal));
dc.Caption = "Минимальная цена по поставщику";
dc.ExtendedProperties.Add("Color", groupColor);
dc.ExtendedProperties.Add("Width", (int?)8);
dc = res.Columns.Add("SourceFirmCodeAvgCost", typeof(Decimal));
dc.Caption = "Средняя цена по поставщику";
dc.ExtendedProperties.Add("Color", groupColor);
dc.ExtendedProperties.Add("Width", (int?)8);
dc = res.Columns.Add("SourceFirmCodeMaxCost", typeof(Decimal));
dc.Caption = "Максимальная цена по поставщику";
dc.ExtendedProperties.Add("Color", groupColor);
dc.ExtendedProperties.Add("Width", (int?)8);
dc = res.Columns.Add("SourceFirmDistinctOrderId", typeof(Int32));
dc.Caption = "Кол-во заявок по препарату по поставщику";
dc.ExtendedProperties.Add("Color", groupColor);
dc.ExtendedProperties.Add("Width", (int?)4);
dc = res.Columns.Add("SourceFirmDistinctAddressId", typeof(Int32));
dc.Caption = "Кол-во адресов доставки, заказавших препарат, по поставщику";
dc.ExtendedProperties.Add("Color", groupColor);
dc.ExtendedProperties.Add("Width", (int?)4);
dc = res.Columns.Add("SourceSuppliersSoldPosition", typeof(Int32));
dc.Caption = "Кол-во поставщиков";
dc.ExtendedProperties.Add("Color", groupColor);
dc.ExtendedProperties.Add("Width", (int?)4);
}
for (var i = 0; i < concurrentGroups.Count; i++)
{
var color = Color.FromArgb(234, 241, 221);
var hue = (color.GetHue() + i * 40) % 360;
color = ColorHelper.FromAhsb(255, hue, color.GetSaturation(), color.GetBrightness());
dc = res.Columns.Add("RivalsSum" + i, typeof(Decimal));
dc.Caption = "Сумма по конкурентам";
dc.ExtendedProperties.Add("Color", color);
dc.ExtendedProperties.Add("Width", (int?)8);
dc = res.Columns.Add("RivalsRows" + i, typeof(Int32));
dc.Caption = "Кол-во по конкурентам";
dc.ExtendedProperties.Add("Color", color);
dc.ExtendedProperties.Add("Width", (int?)4);
dc = res.Columns.Add("RivalsMinCost" + i, typeof(Decimal));
dc.Caption = "Минимальная цена по конкурентам";
dc.ExtendedProperties.Add("Color", color);
dc.ExtendedProperties.Add("Width", (int?)8);
dc = res.Columns.Add("RivalsAvgCost" + i, typeof(Decimal));
dc.Caption = "Средняя цена по конкурентам";
dc.ExtendedProperties.Add("Color", color);
dc.ExtendedProperties.Add("Width", (int?)8);
dc = res.Columns.Add("RivalsMaxCost" + i, typeof(Decimal));
dc.Caption = "Максимальная цена по конкурентам";
dc.ExtendedProperties.Add("Color", color);
dc.ExtendedProperties.Add("Width", (int?)8);
dc = res.Columns.Add("RivalsDistinctOrderId" + i, typeof(Int32));
dc.Caption = "Кол-во заявок по препарату по конкурентам";
dc.ExtendedProperties.Add("Color", color);
dc.ExtendedProperties.Add("Width", (int?)4);
dc = res.Columns.Add("RivalsDistinctAddressId" + i, typeof(Int32));
dc.Caption = "Кол-во адресов доставки, заказавших препарат, по конкурентам";
dc.ExtendedProperties.Add("Color", color);
dc.ExtendedProperties.Add("Width", (int?)4);
dc = res.Columns.Add("RivalsSuppliersSoldPosition" + i, typeof(Int32));
dc.Caption = "Кол-во поставщиков";
dc.ExtendedProperties.Add("Color", color);
dc.ExtendedProperties.Add("Width", (int?)4);
}
var lastGroupColor = Color.FromArgb(253, 233, 217);
dc = res.Columns.Add("AllSum", typeof(Decimal));
dc.Caption = "Сумма по всем";
dc.ExtendedProperties.Add("Color", lastGroupColor);
dc.ExtendedProperties.Add("Width", (int?)8);
dc = res.Columns.Add("AllRows", typeof(Int32));
dc.Caption = "Кол-во по всем";
dc.ExtendedProperties.Add("Color", lastGroupColor);
dc.ExtendedProperties.Add("Width", (int?)4);
dc = res.Columns.Add("AllMinCost", typeof(Decimal));
dc.Caption = "Минимальная цена по всем";
dc.ExtendedProperties.Add("Color", lastGroupColor);
dc.ExtendedProperties.Add("Width", (int?)8);
dc = res.Columns.Add("AllAvgCost", typeof(Decimal));
dc.Caption = "Средняя цена по всем";
dc.ExtendedProperties.Add("Color", lastGroupColor);
dc.ExtendedProperties.Add("Width", (int?)8);
dc = res.Columns.Add("AllMaxCost", typeof(Decimal));
dc.Caption = "Максимальная цена по всем";
dc.ExtendedProperties.Add("Color", lastGroupColor);
dc.ExtendedProperties.Add("Width", (int?)8);
dc = res.Columns.Add("AllDistinctOrderId", typeof(Int32));
dc.Caption = "Кол-во заявок по препарату по всем";
dc.ExtendedProperties.Add("Color", lastGroupColor);
dc.ExtendedProperties.Add("Width", (int?)4);
dc = res.Columns.Add("AllDistinctAddressId", typeof(Int32));
dc.Caption = "Кол-во адресов доставки, заказавших препарат, по всем";
dc.ExtendedProperties.Add("Color", lastGroupColor);
dc.ExtendedProperties.Add("Width", (int?)4);
dc = res.Columns.Add("AllSuppliersSoldPosition", typeof(Int32));
dc.Caption = "Кол-во поставщиков";
dc.ExtendedProperties.Add("Color", lastGroupColor);
dc.ExtendedProperties.Add("Width", (int?)4);
CopyData(selectTable, res);
}