ActivityManager report generator. Another bike, but in profile

ActivityManager is a report generation manager based on the replacement of template lines.
The main features of the ActivityManager are:

• Independence from the data source: all DBMSs are supported for which .Net providers exist, and not only;
• Creating templates without using COM: all reports are generated directly in XML;
• Support report formats ods, odt, docx, xlsx. Independence from the presence of a word processor on the end user's computer: this feature follows from the previous one;
• The presence of mechanisms for pre-processing of data: changing the format of the representation of the full name, monetary amounts, integers, real numbers and dates, including the possibility of changing the case in which the final data should be presented;
• Availability of post-formatting mechanisms;
• Ease of use and extensions due to the presence of a report configuration editor and a simple plug-in architecture.

Report configuration using ActivityManager can be divided into 3 parts: the selection of data, their processing and the formation of the report itself. Detailed information on each of the stages, see the relevant sections.

Glossary

• The template file is an odt, ods, docx, or xlsx file format that serves as a blank for a future report.
• Template line - a line element of the form $ variable $ in the template file, which will be replaced with inline data when generating a report
• The plugin is a regular .Net assembly that implements a special IPlug interface.
• Action (action) - from a technical point of view, this is a public class method that implements the IPlug interface and is visible through this interface.
• Step (step) - the stage of execution of the sequence of actions described in the configuration file of the report. A step differs from an action in that an action is a description of the signature of what needs to be done at this step, and a step is a wrapper linking the signature with specific values ​​of the parameters.

Configuration File Structure

The report configuration file is a regular xml file of the following type:


The main part of the report configuration file is occupied by steps. The steps are essentially the stages of generating a report: data sampling, data processing, report configuration, data source and much more. A step consists of the name of the plugin plugin, which contains the action action that needs to be performed, input <input /> and output <output /> parameters and the repeat parameter indicating how many times this step must be performed (default 1). Each input parameter has the name name and value (content). Each output parameter has a name by default name <and redefinition of the name (content). Using this parameter name in subsequent steps of report generation, you can get its value from the global parameters collection. For more information about the collection of global parameters, see the "Parameter Passing" section.
')
At the top of the configuration file is a <plugins /> block. In this block is a list of downloadable plug-ins during execution. The default plugins are located in the plugins \ directory. The <language /> block indicates the language in which messages will be displayed (mostly errors) during report generation. Currently two languages ​​are supported: Russian and English. Language translations are located in the lang \ directory.
Despite the simple structure of the configuration file, editing it manually is tedious. Therefore, a special configuration file editor was developed. Read more in the "Visual File Editor" section.

Supplied Plugins

In the default delivery of ActivityManager, there are 6 basic and one custom plugin:

• SqlDataSource.dll - plugin for accessing SQL data sources;
• TextDataSource.dll - plugin for accessing CSV data sources via SQL;
• ConvertModule.dll - Data conversion plugin. This plugin contains actions for transforming the presentation format, cases, combining whole tables, columns and rows into a single report row, as well as selecting individual rows, cells from a data source table;
• ReportModule.dll - directly the reporting plugin itself;
• IOModule.dll - this plugin is intended for input / output operations and control the order of steps execution. This module contains actions for starting the generated report in a word processor (and any other file or application), outputting debug information to the console and MessageBox, conditional transition actions that make it possible to do branchings and loops in the report (for more details, see "Conditional redirection actions and JS macros");
• JSModule.dll - this plugin is designed to write fairly simple JavaScript macros. The initial purpose of this plugin is to calculate the conditions of transitions implemented in the IOModule.dll module. But this plugin is not limited to this functionality;
• MenaModule.dll is a custom-module that was designed as a specific extension of functionality for a specific housing program in our organization. The plugin demonstrates how easy it is to add your own functionality to the report generator if the basic is not enough to solve the set tasks.

Parameter passing

Command line parameters

When launching the report generation to the core (ActivityManager.exe), you must pass one required config parameter in the form

 ActivityManager.exe config="c:\1.xml" 

As a result, ActivityManager.exe will read the configuration file located along the path c: \ 1.xml and generate a report.
In addition to the required config parameter of the ActivityManager, you can also pass the lang parameter, the language of the messages displayed during execution (for the most part, errors that occurred when generating the report).

In addition to the basic config and lang parameters , the ActivityManager can accept any number of any other parameters that will be placed in the collection of global runtime parameters and can be used during report generation. For example:

 ActivityManager.exe config="c:\1.xml" connectionString="dsn=registry" id_process=318 

As a result of executing this command, connectionString and id_process parameters will fall into the collection of global runtime parameters and can be used during connection setup with a data source, a sample and at any other report step. How exactly to use these parameters during execution, see in the next section.

Global runtime options

The collection of global runtime parameters is a list of named variables that can be used at any step of the report generation (provided that at this step, the parameter is already available).
Parameters are written to this collection in two cases:

• When transmitted on the command line (see previous paragraph)
• As a result of the execution of the previous steps of the report.

To use global parameters, you must enclose the name of the parameter in square brackets. Before transmitting the parameter value to execution, the preprocessor will check the compliance of the template with square brackets with the parameters in the collection, and if it finds a match by name, it will make a replacement.
For example, suppose we run the report generation as follows.

 ActivityManager.exe config="c:\1.xml" connectionString="dsn=registry" id_process=318 

Then during the execution of the application, we can use the command line arguments passed as follows:


As you can see, in the first step, the SqlSetConnectionString (setting the connection string) in the input parameter indicates [connectionString], which tells the preprocessor to take this parameter from the global parameters collection. In case the parameter in square brackets is not found in the collection of global parameters, no error messages will be generated. ActivityManager will understand that this string is not a parameter and will leave it unchanged.

Note the output parameter of the step SqlSelectTable

 <output> <parameter name="table">tenancy_processes</parameter> </output> 

This entry means that after the execution of the request, the data from the source will be stored in the global parameter area under the name tenancy_processes and can be accessed via [tenancy_processes] (which is done in the next step ReportSetTableValue)

 <input> <parameter name="table">[tenancy_processes]</parameter> <parameter name="xmlContractor">Row</parameter> </input> 

Parameters of simple types (strings, numbers, dates, etc., namely all types that can be uniquely converted from the System.String type to the target type using Convert.ChangeType) are replaced in substitution mode. Those. It is perfectly possible to set the parameter as part of the value, and not as an integer. For example:

 <input> <parameter name="query">SELECT * FROM tenancy_processes WHERE id_process = [id_process]</parameter> </input> 

Parameters of more complex types that cannot be unambiguously converted from a string representation will be substituted directly. In such parameters, partial substitution is impossible by definition.

Parameter substitution from the global collection is performed only in the input action parameters.

Data retrieval

Before you generate a report, you must obtain data. At the moment, data sources for ActivityManager can be DBMS, for which there is a .Net-provider, as well as text files in CSV format. Unfortunately, there is no native data source for XML yet.

The result of sampling data from any of the described sources will be an object of either a scalar type or a ReportTable type.

SQL data sources

The plugin SqlDataSource.dll is intended for working with SQL data sources.
A simple example of using this plugin to fetch data from an ODBC data source:


In the first step of this example, the connection string is set. The second is a selection of data from the executors table, the data is written into a table with the name new_table. After which we can make various modifications to them via the ConvertModule.dll plugin or send directly to the report.
ODBC is used as the default provider in the SqlDataSource, but it is allowed to install any provider supported in .Net. To install the provider, use the SqlSetProvider function. For example, you can install an OLE DB provider like this:

 <step plugin="SqlDataSource" action="SqlSetProvider" repeat="1"> <input> <parameter name="name">OLE</parameter> </input> </step> 

Here, the name parameter is the full or short invariant name of the provider. There is no need to specify the full invariant name, since Name matching is not clear (by pattern). For example, instead of the full invariant name for MS Sql Server (System.Data.SqlClient), you can simply specify:

 <step plugin="SqlDataSource" action="SqlSetProvider" repeat="1"> <input> <parameter name="name">SQL</parameter> </input> </step> 

or for example

 <step plugin="SqlDataSource" action="SqlSetProvider" repeat="1"> <input> <parameter name="name">SqlClient</parameter> </input> </step> 

The full list of supported providers depends on the machine on which the report is generated and can be obtained through the DbProviderFactories.GetFactoryClasses () method.
In addition to the SqlSetProvider and SqlSelectTable actions, the SqlDataSource plugin has 8 more actions. A full list of them can be found on the project wiki.

CSV data sources

In addition to working with the DBMS, ActivityManager supports the selection and modification of data from CSV files. For sampling data from CSV files, in addition to Microsoft Text Driver, through ODBC, the ability to sample by the built-in text driver based on MySQL syntax is supported.
For example, suppose we have two CSV files users.csv and user_actions.csv with the following content:
users.csv:

 id|surname|name|patronymic 1||| 2||| 

user_actions.csv:

 id_user|action 1|1 1|2 1|3 2|4 

We need to choose the number of actions for each user.
The configuration of the data sampling step from the CSV will look like this:


The result of this query will be stored in the global parameters collection under the name table.
In addition to the TextSelectTable action, there are 2 more actions in the TextDataSource plugin: TextSelectScalar and TextModifyQuery, detailed information about which you can also find on the project wiki .

Data conversion

Often there is a need to format the data after sampling from the source and before they are written to the final report. The reasons for this can be many, from the lack of support at the DBMS level of any complex transformations, to the reluctance to "dirty" a SQL query with a mass of excessive conditions that turn it into an unreadable sheet.
For such data transformations in ActivityManager, the plugin ConvertModule.dll is used.
Here is a list of what can be done with the data using this plugin:

• Retrieve a separate row from the data source table by its number. In the future, the data in this line can also be subjected to transformations and sent as a collection of automatically named parameters to the report;
• Get the value of a single cell from the data source table;
• Merge into one row the table, column or row of the data source in a text variable. To make it clearer, this is some analogous GROUP_CONCAT from MySQL;
• Convert integers and real numbers, as well as the date and time in a complex text or combined presentation with the possibility of declination in cases;
• To convert the name of the Russian language from the nominative case to any case form;
• Convert the number to the presentation of the monetary unit, including (if necessary) and in text form with the possibility of declination in cases.

Consider the data format conversion functions in more detail. All data conversion functions can be classified.

By type of data being converted:

• Representations of integers: ConvertIntToString, ConvertIntCellToString, ConvertIntColToString;
• Representations of real numbers: ConvertFloatToString, ConvertFloatCellToString, ConvertFloatColToString;
• Date and time representations: ConvertDateTimeToString, ConvertDateTimeCellToString, ConvertDateTimeColToString;
• Representations of monetary amounts: ConvertCurrencyToString, ConvertCurrencyCellToString, ConvertCurrencyColToString;
• Representations Name: ConvertNameToCase, ConvertNameCellToCase, ConvertNameColToCase;

By type of input and output parameters:

• Transformations over scalar values: ConvertIntToString, ConvertFloatToString, ConvertDateTimeToString, ConvertCurrencyToString, ConverNameToCase;
• Transformations over the cells of an object of the ReportRow type (this object is the result of the execution of the GetRow action of the ConvertModule.dll plugin): ConvertIntCellToString, ConvertFloatCellToString, ConvertDateTimeCellToString, ConvertCurrencyCellToString, ConvertNameCellToCase;
• Transformations over the columns of the object of the ReportTable type (this object is the result of the execution of the SqlSelectTable and TextSelectTable actions of the SqlDataSource.dll and TextDataSource.dll plug-ins, respectively): ConvertIntColToString, ConvertFloatColToString, ConvertDateTimeColToString, ConvertCurCCCTTCtCoCCoTCCoTCStTCoCCtoTCCoTCCToCtCoTCCoToCCToCtCoTCoTCCToString.

Conversion of integers

As mentioned above, the actions ConvertIntToString, ConvertIntCellToString, ConvertIntColToString are used to convert integers. They differ only in the type of input and output parameters, so we will not consider each of them in detail. Consider a simple example of converting numbers to a textual representation. Suppose that we need to select some numerical values ​​from the database table, then bring them to textual form according to the following rules: all numbers must be in the parental case, the end of textual representations of numbers should be feminine, numbers should be quantitative, not ordinal, the first letter of the resulting text representations of numbers must be large. First we need to get the data that we will convert. It has already been shown how this is done:


As a result of this action, an object of the ReportTable type containing one int_column column containing rows with the values ​​1, 13013, 55132111, 1055132111 will be stored in the collection of global objects.

Directly the transformation itself according to the requirements defined above can be carried out as follows:


As a result, we will get a table named table with the following values: “One”, “Thirteen thousand thirteen”, “Fifty-five million one hundred thirty two thousand one hundred eleven”, “One billion fifty-five million one hundred thirty-two thousand one hundred eleven”.

Notice that the inTable and outTable parameters have the same ReportTable type. Since in this case the output parameter has the same name as the input parameter, it will simply replace the input parameter in the global parameter collection. This way you can easily make chains of transformations.

Real Number Transformations

For converting real numbers, the actions ConvertFloatToString, ConvertFloatCellToString, ConvertFloatColToString are used. Transformations of real numbers are very similar to transformations of integers, except that when converting real numbers you cannot specify the gender and what is the numeral (ordinal or quantitative). Consider a simple example of converting the number 3.1415926 into the prepositional sentence:


As a result, the words parameter will contain the value “three whole one million four hundred and fifteen thousand nine hundred twenty six ten million”. Despite the fact that System.Double is used as an input parameter for the conversion of a real number, the maximum size of the real part is limited to billions (9 decimal places). Technically, nothing interferes with doing more, but in practice the need for such accuracy has not yet arisen.

Date and Time Conversions

To convert the date and time, use the actions ConvertDateTimeToString, ConvertDateTimeCellToString, ConvertDateTimeColToString. As with the previous action groups, the actions described in this section for working with date and time are very similar. However, unlike transformations of integers and real numbers, these actions do not use the case and gender parameters. Instead, the more flexible format parameter is used. Let's take a closer look at which format representations are supported by date conversion actions:


In all the above formats, the letter "x" means the first letter of the case n (Nominative), g (Genetive), d (Dative), a (Accusative), i (Instrumental), p (Prepositional).

As you can see, the possibilities for customizing the format of the date representation are huge. . «1988-06-26 13:47:56» ( , «26.06.1988 13:47:56», ):

: dd MMg yyyy HHn mmn ssn
: 26 1988
: ddn MMg yyyyg
:
: dd.MM.yy (MMn yyyyg)
: 26.06.88 ( )

xml :

ActivityManager CurrencyToString, CurrencyCellToString CurrencyColToString. , : , . . , decimal, dept, , , . , « ». :


, dept . «3 101 203,03 ( )».
:


«» n (Nominative), g (Genetive), d (Dative), a (Accusative), i (Instrumental), p (Prepositional).

.

, , . ActivityManager Padeg.dll. ConvertModule.dll ConvertNameToCase, ConvertNameCellToCase ConvertNameColToCase. , . , « » :


« ».
format :


, « » , np ss ( textCase) «.. ».

ConvertModule.dll , ReportTable ReportRow . RowConcat, ColumnConcat TableConcat. RowConcat ReportRow . ColumnConcat ReportTable. TableConcat ReportTable . Usage example


myConcatedStr , myTable, (rowSeparator — , cellSeparator — ).

GetRow GetCell.

GetRow ReportRow ReportTable ( ). ReportRow ( « »). , ReportTable . GetRow:


GetCell ReportTable:


GetCell GetRow .

.
ActivityManager odt, ods, docx xlsx. . -, . COM- - , . XML. , COM , . , , , WordPad , .

. — odt, ods, docx xlsx . . : $variable$, variable — . , .



6 : $title$, $date$, $n$, $snp$, $money$, $date$. .

ActivityManager ReportModule.dll. 5 .

ReportSetTemplateFile. , . . :


[reportPath] , example.odt ActivityManager.exe . : .

, . ReportModule.dll ReportSetStringValue, ReportSetStringValues, ReportSetTableValue. . Consider them in order.

ReportSetStringValue , . . "$" . :






. , ReportTable GetCell. , . , . (, ), ReportSetStringValues. ReportRow. () . :


ReportTable . table title date. GetRow ReportTable ReportRow, 0 ( ) ReportTable. ReportRow ReportSetStringValues. , «», $title$ $date$ () ReportRow. example.odt ,



, $date$. , ReportSetStringValue ReportSetStringValues , : , , - , . . « ».

ReportSetStringValues () ReportRow . , . , , , . .

. .

ReportSetTableValue. :


table ReportTable, . ReportTable. , . , . , XML- , xmlContractor ( xmlContractor ) 50 ReportTable , . , , , ( ). $n$, $snp$, $money$, $date$. ReportSetTableValue ReportTable, n, snp, money, date, time, action, pay. , .. ReportTable ( 50% ), .

, ReportTable, . .., ReportTable



xmlContractor ReportSetTableValue. ReportTable. . 4 xmlContractor: Paragraph, Table, Row, Cell. . ,



. . xmlContractor Row ReportTable. , ReportTable ReportTable.



, , .. .
xmlContractor Table, ,



, , . ReportTable ReportTable . . ReportTable. xmlContractor Table , : , . , xmlContractor Table odt docx. ods xmlContractor Table ( ) ReportTable. , xmlContractor, xlsx.
xmlContractor Paragraph



ReportTable. xmlContractor .
xmlContractor Cell xlsx ods. . ods:



ReportSetTableValue, value.
xmlContractor Row :



ods, ReportTable ( , ), ReportTable.

, xmlContractor Cell:



, . , , . .

, , ReportModule.dll — ReportGenerate.


. , ReportModule.dll , . ReportGenerate. . ReportGenerate . ReportGenerate , . . , . FTP- -. ( ) ActivityManager . IOOpenFile IOModule. :


, . .., , OpenOffice, LibreOffice, MS Word, WordPad, .

ReportSetStringValue, ReportSetStringValues, ReportSetTableValue , . , . Consider an example.


$content$ «My name is $name$. My surname is $surname$.». $name$ $surname$ «Vasily» «Ignatov». «My name is Vasily. My surname is Ignatov.». , , $content$ , $name$ $surname$ ( ). «My name is $name$. My surname is $surname$.». .

-

- . , , , . . , ( ), , , . , ReportSetTableValue xmlContractor = Paragraph . -. GROUP_CONCAT MySQL, FOR XML MSSQL, TableConcat ConvertModule.dll, - .

$b$value$/b$. Those. : "$b$ $/b$, 29.06.1988 .., № 0123456 4321 ..., $b$ $/b$, 01.15.1976 .., № 654321 1234, ...". - . . $b$ $/b$ , .. :

- OpenOffice.
-:

• $b$ $/b$ —
• $i$ $/i$ —
• $u$ $/u$ —
• $br$ — ( Enter OpenOffice)
• $sbr$ — ( Shift+Enter OpenOffice)

.

JS

Conditions

ActivityManager . IOModule.dll. IOIfConditionToStep IOIfConditionExit. IOIfConditionToStep , true, IOIfConditionExit , true. . , , . ( , ) , . . , : — , , — , ..


:



IOIfConditionToStep IOIfConditionExit , . JSRun JSModule.dll. (, ) JavaScript-. JSRun JavaScript. ActivityManger, []. JSRun Noesis.Javascript , . return result.

Cycles

IOIfConditionToStep JSRun . ,







, . . Let's get started





, . .

. ReportTable ( yearsTable):



ReportSetTableValue xmlClouser = Table . , ( )



, yearsTable, GetCell ( currentYear) yearsTable , ( MySQL)


( ) ReportSetTableValue xmlClouser = Row .
:

, « JS », xml- , . : , , , , . . ,



( ). xml-. 4 , ( ): , , , . , drag'n'drop.

: «» ( ) «» ( ). . , , ( ) . , . . , , . xml- , plugins/. , , , .

Beginning of work

, . . : -> -> . , .



. . , . . .
. .
(, ) ,



. ( ), , .
Sytem.String ScintillaNET JavaScript SQL.



. «» .

, ActivityManager.exe .

, ActivityManager.exe . -> -> . ,



, config, ActivityManager.exe . , , .
. F5 -> -> . , , .



, , , , , . IODebugMessage IOModule. — , . ToString() , .
, , , ActivityManager . -> -> .

, xml- .

.Net , plugins\ :

• IPlug
• IPlug , . .., , MenaModule.dll, , IPlug MenaModule.
• , IPlug. ( ).
• IPlug . void. out-.

MyModule.dll:


That's all. plugins\ ActivityManager AMEditor . MyAction :


custom- MenaModule.cs

: ReportTable ReportRow ExtendedTypes.dll.

Conclusion

. ActivityManager . , :

• . .
• unit- ( unit). , ActivityManager , - , .

, , , . - , - . , , . =)

Links

• GitHub
• GitHub Wiki
• 40