public static ReportInfo GetRDLRouteList(IUnitOfWork uow, RouteList routeList) { var RouteColumns = _routeColumnRepository.ActiveColumns(uow); if (RouteColumns.Count < 1) { MessageDialogHelper.RunErrorDialog("В справочниках не заполнены колонки маршрутного листа. Заполните данные и повторите попытку."); } string documentName = "RouteList"; bool isClosed = false; if (routeList.PrintAsClosed()) { documentName = "ClosedRouteList"; isClosed = true; } string RdlText = string.Empty; using (var rdr = new StreamReader(Path.Combine(Environment.CurrentDirectory, "Reports/Logistic/" + documentName + ".rdl"))) { RdlText = rdr.ReadToEnd(); } //Для уникальности номеров Textbox. int TextBoxNumber = 100; //Расширяем требуемые колонки на нужную ширину RdlText = RdlText.Replace("<!--colspan-->", $"<ColSpan>{ RouteColumns.Count }</ColSpan>"); //Расширяем таблицу string columnsXml = "<TableColumn><Width>18pt</Width></TableColumn>"; string columns = string.Empty; columns += "<TableColumn><Width>100pt</Width></TableColumn>"; // Первая колонка шире тк кк это коммент for (int i = 1; i < RouteColumns.Count; i++) { columns += columnsXml; } RdlText = RdlText.Replace("<!--table_column-->", columns); //Создаем колонки, дополняем запрос и тд. string CellColumnHeader = string.Empty; string CellColumnValue = string.Empty; string CellColumnStock = string.Empty; string CellColumnTotal = string.Empty; string SqlSelect = string.Empty; string SqlSelectSubquery = string.Empty; string Fields = string.Empty; string TotalSum = "= 0"; bool isFirstColumn = true; foreach (var column in RouteColumns) { CellColumnHeader += GetColumnHeader(TextBoxNumber++, column.Name); if (isFirstColumn) { CellColumnValue += GetCellTag( TextBoxNumber++, $"=Iif({{{ _orderPrioritizedTagName }}}, \'Приоритет! \', \"\") + {{{ _orderCommentTagName }}}", "0", isClosed ); } else { if (isClosed) { CellColumnValue += GetCellTag( TextBoxNumber++, $"=Iif(" + $"{{{ _waterTagNamePrefix }{ column.Id }}} + {{{ _waterFactTagPrefix }{ column.Id }}} = 0," + $" \"\"," + $" Iif(" + $"{{{ _waterTagNamePrefix }{ column.Id }}} = {{{ _waterFactTagPrefix }{ column.Id }}}," + $" Format({{{ _waterTagNamePrefix }{ column.Id }}}, '0')," + $" '' + {{{ _waterFactTagPrefix }{ column.Id }}} +" + $" '(' + Iif(" + $"{{{ _waterFactTagPrefix }{column.Id}}} - {{{ _waterTagNamePrefix }{ column.Id }}} > 0," + $" '+'," + $" '') + ({{{ _waterFactTagPrefix }{column.Id}}} - {{{ _waterTagNamePrefix }{ column.Id }}}) + ')'))", $"=Iif({{{ _waterTagNamePrefix }{ column.Id }}} = {{{ _waterFactTagPrefix }{ column.Id }}}, \"0\", \"\")", isClosed ); } else { CellColumnValue += GetCellTag( TextBoxNumber++, $"=Iif({{{ _waterTagNamePrefix }{ column.Id }}} = 0, \"\", {{{ _waterTagNamePrefix }{ column.Id }}})", "C", isClosed); } } //Ячейка с запасом. Пока там пусто CellColumnStock += GetCellTag(TextBoxNumber++, "", "0", isClosed); //Ячейка с суммой по бутылям. if (isFirstColumn) { CellColumnTotal += GetCellTag(TextBoxNumber++, "", "0", isClosed); } else { string formula; if (isClosed) { formula = $"=Iif(Sum({{{ _waterFactTagPrefix }{ column.Id }}}) = 0, \"\", Sum({{{ _waterFactTagPrefix }{ column.Id }}}))"; } else { formula = $"=Iif(Sum({{{ _waterTagNamePrefix }{ column.Id }}}) = 0, \"\", Sum({{{ _waterTagNamePrefix }{ column.Id }}}))"; } CellColumnTotal += GetCellTag(TextBoxNumber++, formula, "0", isClosed); } //Запрос.. if (isFirstColumn) { SqlSelect += $", orders.comment AS { _orderCommentTagName }" + $", (SELECT EXISTS (" + $" SELECT * FROM guilty_in_undelivered_orders giuo" + $" INNER JOIN undelivered_orders uo ON giuo.undelivery_id = uo.id" + $" WHERE giuo.guilty_side IN('{ GuiltyTypes.Driver }','{ GuiltyTypes.Department }')" + " AND uo.new_order_id = orders.id" + $")) AS { _orderPrioritizedTagName }"; Fields += $"<Field Name=\"{ _orderPrioritizedTagName }\">" + $"<DataField>{ _orderPrioritizedTagName }</DataField>" + "<TypeName>System.Boolean</TypeName>" + "</Field>"; Fields += $"<Field Name=\"{ _orderCommentTagName }\">" + $"<DataField>{ _orderCommentTagName }</DataField>" + "<TypeName>System.String</TypeName>" + "</Field>"; isFirstColumn = false; } else { SqlSelect += $", IFNULL(wt_qry.{ _waterTagNamePrefix }{ column.Id }, 0) AS { _waterTagNamePrefix }{ column.Id }"; SqlSelectSubquery += $", SUM(IF(nomenclature_route_column.id = { column.Id }," + $" cast(order_items.count as DECIMAL), 0)) AS { _waterTagNamePrefix }{ column.Id }"; if (isClosed) { SqlSelect += $", IF(route_list_addresses.status = '{ RouteListItemStatus.Transfered }', 0," + $" cast(IFNULL(wt_qry.{_waterFactTagPrefix}{ column.Id }, 0) as DECIMAL)) AS { _waterFactTagPrefix }{ column.Id }"; SqlSelectSubquery += $", SUM(IF(nomenclature_route_column.id = { column.Id }, cast(IFNULL(order_items.actual_count, 0) as DECIMAL)," + $" 0)) AS { _waterFactTagPrefix }{ column.Id }"; } //Линкуем запрос на переменные RDL Fields += $"<Field Name=\"{ _waterTagNamePrefix }{ column.Id }\">" + $"<DataField>{ _waterTagNamePrefix }{ column.Id }</DataField>" + $"<TypeName>System.Decimal</TypeName>" + $"</Field>"; if (isClosed) { Fields += $"<Field Name=\"{ _waterFactTagPrefix }{ column.Id }\">" + $"<DataField>{ _waterFactTagPrefix }{ column.Id }</DataField>" + $"<TypeName>System.Decimal</TypeName>" + $"</Field>"; } } //Формула итоговой суммы по всем бутылям. if (_routeColumnRepository.NomenclaturesForColumn(uow, column).Any(x => x.Category == NomenclatureCategory.water && x.TareVolume == TareVolume.Vol19L)) { if (isClosed) { TotalSum += $"+ Sum(Iif(Fields!Status.Value = \"{ RouteListItemStatus.Completed }\", {{{ _waterFactTagPrefix }{ column.Id }}}, 0))"; } else { TotalSum += $"+ Sum({{{ _waterTagNamePrefix }{ column.Id }}})"; } } } RdlText = RdlText.Replace("<!--table_cell_name-->", CellColumnHeader); RdlText = RdlText.Replace("<!--table_cell_value-->", CellColumnValue); RdlText = RdlText.Replace("<!--table_cell_stock-->", CellColumnStock); RdlText = RdlText.Replace("<!--table_cell_total-->", CellColumnTotal); RdlText = RdlText.Replace("<!--sql_select-->", SqlSelect); RdlText = RdlText.Replace("<!--sql_select_subquery-->", SqlSelectSubquery); RdlText = RdlText.Replace("<!--fields-->", Fields); RdlText = RdlText.Replace("<!--table_cell_total_without_stock-->", TotalSum); var TempFile = Path.GetTempFileName(); using (StreamWriter sw = new StreamWriter(TempFile)) { sw.Write(RdlText); } #if DEBUG Console.WriteLine(RdlText); #endif string printDatestr = $"Дата печати: { DateTime.Now:g}"; var needTerminal = routeList.Addresses.Any(x => x.Order.PaymentType == PaymentType.Terminal); return(new ReportInfo { Title = $"Маршрутный лист № { routeList.Id }", Path = TempFile, Parameters = new Dictionary <string, object> { { "RouteListId", routeList.Id }, { "Print_date", printDatestr }, { "RouteListDate", routeList.Date }, { "need_terminal", needTerminal } } }); }