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