The procedure for working with the built-in language request object. What is the purpose of the "Language" configuration object?
Development of an application solution in the 1C: Enterprise system consists of two main actions: visual design of configuration objects and description of the specific behavior of the system using the built-in language and query language.
The built-in language of the 1C: Enterprise system has many similarities with other programming languages, but is not a direct analogue of any of them. Its most significant features:
· soft typing (the type of a variable is determined by the type of the value it contains and can change during operation);
· lack of software description of application types (they are created when adding configuration objects);
· event-oriented built-in language;
· all operators have both Russian and English spellings, which can be used simultaneously.
Configuration modules
Modules of the application solution are designed to place the program text in the built-in language. These modules are located in different places in the configuration and have different purposes. Most modules are “tied” to certain configuration objects or to the application solution itself.
The following types of software modules are distinguished:
· Common modules. The configuration can have an arbitrary number of modules, including none. Common modules themselves are not called during the configuration process. They serve only to contain the texts of procedures and functions that can be called from other modules of the application solution. Therefore, they lack a variable description section and a main program section. That. Common modules contain only procedures and functions.
· Application module. There is always a single application module in the configuration. It is executed when the system starts in 1C: Enterprise mode and is intended to work out actions related to the end user's work session. The main events that can be processed in an application module are the start and end events of the application. The sequence from the call is shown in rice. 1. Event Before Starting the System Occurs when the system starts before the main window opens. By handling this event, the developer, for example, has the opportunity to refuse to run if any conditions are not met. Event When Starting the System occurs after opening the main window. In the handler of this event, you can, for example, display information about birthday people, etc.
· External connection module. There is always a single external connection module in the configuration. It is executed when the application is accessed as a COM server (in external connection mode). In external connection mode, it is not the full-fledged 1C: Enterprise application that is launched, but a “light version” in which all functions that are in one way or another related to the organization of the user interface are not available.
· Application object modules. Each application configuration object (for example, a software document or a directory), the data of which can be modified in the 1C: Enterprise mode, has its own module. In addition to the description of variables and the main program, an object module can contain a description of procedures - event handlers associated with a given configuration object. There are two events that are raised for all objects - BeforeRecording And WhenRecording.
· Form modules. Each form has its own module, which defines the behavior of the form and the actions performed from it, for example, opening other forms. Events are raised for all forms Before Opening, During Opening, Before Closing And At Closing.
Context
In the 1C: Enterprise system, context denotes the module’s environment, i.e. variables, objects, properties, methods and events available to it. We can distinguish the following types of contexts, and, accordingly, rules for the visibility of exported variables, procedures and functions:
· Global context, available in all other contexts, consists of the following parts:
§ properties, methods and events of the global context (for example, the property Working Date);
§ system enumerations and system value sets (for example, Return CodeDialog And Symbols).
· Common Module Context formed by the global context and the local context of the general module itself (i.e., the procedures and functions defined within the general module). In the context of a common module, exported procedures and functions of other common modules are available. Exported variables, procedures, and functions of the application module are not available.
· In the context of an application module or external connection module Exported procedures and functions of common modules are available.
· In the context of an application object module there is access to the details and tabular parts of the object, as well as its methods and events. Exported variables, procedures and functions of the application module (external connection module) and common modules are available here.
· In the context of a form module form details are available, as well as form properties, methods and events. If a form has a main attribute assigned, then the properties and methods of the application object used as the main attribute become available in the form module.
The relationship between contexts is shown schematically in rice. 2. On rice. 3 depicts the possible interaction of the journal form module and the document module.
Procedures and functions
Procedures and functions are program blocks that can be called by name from another location, such as another procedure. Functions differ from procedures only in that they have a return value. In version 8, the order of procedures and functions is not important. This means that the procedure can be located below where it is called.
Procedures and functions can have parameters that define what actions it should do with what objects. Parameters of a procedure or function are passed by reference by default. This means that changing a formal parameter within a procedure or function will change the actual parameter at the point where it is called. To guarantee that a parameter is passed by value, you need to insert a keyword before the parameter name Meaning.
Example 1:
Procedure Calculate()
Amount=Price*Quantity;
End of Procedure
Calculation(); // Call the procedure
Example 2:
Perem Glob;
// Description of the procedure
Procedure Calculation(Par1, Par2, ParZ) Export
Glob = Glob + Par1 + Par2 + ParZ;
End of Procedure
Calculation(5, 6, 7); // Call the procedure
Example 3:
Perem Glob;
// Description of the function
Function Calculation(Par1, Par2, ParZ) Export
Lok = Glob + Par1 + Par2 + ParZ;
Return Lock;
EndFunction
Res = Calc(5, 6, 7); // Function call
Data types
Number, String, Date, Boolean, Undefined, Null (for unspecified values in database tables)
Type. Values of the special type “Type” are needed to represent and compare data types, for example:
Declaring Variables
Variables appear in the program in the following cases:
· after they are declared using the Variable operator.
Perem<Имя_переменной>[Export];
Variable A, B;
· after the first placement of the variable name on the left side of the assignment operator.
Example:
· when determining the names of identifiers of edited dialog elements;
· when setting formal parameters of procedures.
Cast
Type casting can be explicit or implicit.
For explicit casting, the following functions exist: Number, String, Date, Boolean. Implicit type casting is performed automatically by the system when evaluating expressions.
Example: The value of the numeric variable MonthNumber is implicitly converted to a string and appended to another string:
A = “Month” + MonthNumber;
Directories
Work with directories is carried out using the following objects:
· DirectoriesManager. Provides access to all configuration reference books. The properties of this object coincide with the names of directories and contain objects of the DirectoryManager type.
· DirectoryManager. Provides access to operations on a directory as a set of elements. Using the methods of this object, you can search, obtain a selection, create new elements, and access directory forms and layouts.
· DirectoryLink. Unambiguously identifies an element (group) of the directory and allows you to access it in read-only mode. Through the properties and methods of this object, you can read the details of an element (group) and access its tabular parts. The value of this type is stored in attributes that refer to elements of this directory, for example, in the attribute Employee document Recruitment a link to a specific directory element is stored Employees.
· DirectoryObject. Provides writable access to an element. This object contains methods that affect an element in the database, for example, methods Write down And Delete.
· DirectorySelection. Provides the ability to iterate through directory elements. Sampling can be direct or hierarchical.
· DirectoryList. An object for managing the list of elements in a table field. Allows you to manage columns, selection and sorting in the list.
In the 1C:Enterprise system?
1. Configuration and database texts are stored in the formatUNICODE
3. There is no right answer
6.75 For what purpose are configuration and database texts stored in the formatUNICODE?
1. The UNICODE format ensures the immutability (independence of the operating system software platform) of information presentation
2. FormatUNICODE allows you to support different languages in the 1C:Enterprise system
3. There is no right answer
6.76 Internationalization mechanisms laid down. ..
1. technology platform 1C:Enterprise
2. applied solutions
3. Answers 1 and 2 are correct
4. there is no correct answer
6.77 What is a localization code?
1. A string consisting of a language code and a country code that identifies some region of the world
2. Software product code (indicated on the registration form, documentation included in the delivery kit)
3. Format string option for conversion
4. Her answer is correct
6.78 Is it true that in 1C:Enterprise 8 any text information can simultaneously include characters from different languages?
1. Yes, since all configuration and database texts are stored in the formatUNICODE
2. Depending on the settings specified when creating the infobase
3. Only if this is provided by the configuration
6.79 What is the purpose of the "Language" configuration object?
1. To create a program interface in different languages
2. To create text documents in different languages
3. Such an object does not exist in 1C: Enterprise 8
6.80 How can I change the language for viewing (editing) the configuration?
1. Using the language selection button located in the status bar to the right of the "NUM" button
2. Through the menu item "Configuration - Configuration editing language"
3. In 1 C: Enterprise this possibility does not exist
4. Verpa answers 1 and 2
6.81 What is the spelling of the built-in language operators?
1. Only Russian writing
3.
6.82 Is it possible to use built-in language operators in Russian and English writing in one source text?
1. Only with special configurator settings
2. Yes, this does not require changing any configurator settings
3. No, since the built-in language option is set in the configuration properties
6.83 What is the purpose of the built-in language?
1. To determine the default program interface
2. To describe (at the configuration development stage) algorithms for the functioning of an applied task
3. There is no right answer
6.84 What is the spelling of the built-in language functions?
1. Only Russian writing
2. English spelling only
3. Russian and English writing
4. Depending on the configurator settings
6.85 What does parameter L mean?(L) in the format string of the NumberInWriting() formatting function?
1. Sign “output the fractional part in numbers/words”
2. Number of decimal places
3. Localization code
7. Tabular model of the application solution
7.1 When setting up data access restrictions, you can set several (based on the number of fields) restrictions:
1. For the "Read" right
2. For the right "Change"
3. For the "Add" right
4. For the "Delete" right
5. For all the above rights
6. For all possible rights
7.2 When setting up data access restrictions, the following can be used as values by which data access restrictions are made:
1. Session parameter values only
2. Only data from tables (queries)
3. Session parameter values and data from tables (queries)
4. Only values with types: Number, String, Boolean, Date
7.3 Which of the above methods can be used so that the code and name of the directory appear in the “Fields” section of the query designer?
1. First fill out the “Tables” section, and then, selecting the desired objects from this section, move them to the “Fields” section using a double-click with the left mouse button
2. Without filling out the “Tables” section, immediately select the necessary objects from the tables - data sources of the “Database” section, transferring them to the “Fields” section using Drag & Drop technology. The "Tables" section is filled in automatically
3. First fill out the "Tables" section, and then, selecting the necessary objects from this section, transfer them to the "Fields" section using the buttons on the form ">" """
4. Answers I and 3 are correct
5. Answers I, 2 and 3 are correct
7.4 To increase the speed of request execution, you must:
1. Set parameters for most real tables
2. Set parameters for most virtual tables
3. Instead of specifying parameters for a real or virtual table, use the selection specified by the “WHERE” query language construct
4. Answers I and 2 are correct
7.5 Is it possible to assign a new name (alias) for it when selecting a source table in the “Tables” section of the query designer?
1. Yes you can
2. Yes, you can, but only if the data source is a nested query
3. Yes, you can, but only if the data source is a virtual table
4. Answers 1 and 2 are correct
5. Answers 1 and 3 are correct
7.6 A nested query can be used:
1. As a data source table
2. As an operand of comparison operations “B” or “NOT B” when setting virtual table parameters
3. As an operand of comparison operations “B” or “NOT B” when specifying the query language construct “WHERE”
4. Verpa answers 1, 2 and 3
7.7 Is it possible to get totals by hierarchy using the query designer?
1. It is possible if you specify the total type “Elements and Hierarchy” for the grouping field
2. It is possible if you specify the total type “Hierarchy only” for the grouping field
3. Verpa answers 1 and 2
7.8 On the “Conditions” tab of the query designer, a separate line of the list of conditions can be generated:
1. Double-click with the left mouse button on the desired field in the list of available fields
2. By moving the desired field to the list using Drag & Drop technology
3. Click the "Add" button. If the condition is arbitrary, then the condition text can be entered “manually”
4. Call the context menu and select “Add” from it. It is possible to use an arbitrary expression
5. All of the above answers are correct
7.9 On the “Links” tab of the query designer, you can define:
1. Connecting data source tables and relationships between them
2. Combining data source tables and connections between them
3. Relationships between the fields of the table obtained as a result of the query
4. Relationships between the fields of the data source table and the table obtained as a result of the query
7.10 When joining data source tables in the query designer, you can:
1. Assign a connection without specifying the connection condition
2. Assign a connection indicating the connection condition, and this condition can only be one
3. Assign a connection indicating the connection condition, and this condition can only be simple
4. Assign the required number of connections indicating the required number of communication conditions, and these conditions can be either simple or arbitrary
7.11 Creating a connection between data source tables in the query designer allows:
1. Joining only two data source tables
2. Connecting the required number of data source tables
3. Connecting only two data source tables, and the “All” checkbox must be checked for at least one of the tables
Basic, which makes it easier for novice developers to learn. However, it is not a direct analogue of any of the listed languages.Here are just some of the most significant features of the built-in language:
- pre-compilation; before execution, modules containing text in the built-in language are converted into internal code;
- caching compiled modules in memory;
- soft typing - the type of a variable is determined by the type of the value it contains and can change during operation;
- lack of software description of configuration objects; the developer can use either objects built into the platform or objects created by the system as a result of visual design of the application solution.
Event-oriented built-in language. The purpose of the built-in language in the 1C:Enterprise system is determined by the ideology of creating application solutions. Application solutions in 1C:Enterprise 8.0 are not fully coded. Most of the application solution is created by the developer through visual design - creating new configuration objects, setting their properties, presentation forms, relationships, etc. The built-in language is used only to determine the behavior of application solution objects that differs from the standard one, and to create their own data processing algorithms .
For this reason, modules containing text in the built-in language are used by the system in specific, previously known situations that may arise during the operation of the application solution. Such situations are called events. Events can be associated with the functioning of application solution objects or with the application solution itself, as such.
For example, a number of events are associated with the functioning of the Directory application solution object, among which is the BeforeWrite event. This event occurs just before the directory item data is to be written to the database. A developer, using a built-in language, can describe an algorithm that, for example, will check the correctness of data entered by the user. By placing this algorithm in the appropriate module, the developer will ensure that every time the user records a directory item, the system will execute the algorithm created by the developer and check whether the user has forgotten to fill in the required directory details.
Thus, we can say that the built-in language is a scripting language for programming business logic, and the use of modules in the built-in language is event-dependent, i.e. modules are executed when certain events occur during the operation of the application solution.
Predefined Data Types
The 1C:Enterprise 8.0 platform allows the developer to use various types of data.
There are a large number of data types that are defined at the level of the platform itself. For example this primitive data types, such as string, number, date, etc.
Description primitive data types:
- NULL- missing value. Used, for example, in queries.
- Undefined- empty, undefined value. It is used, for example, when evaluating the passing of parameters if this parameter is omitted when calling a procedure or function. Details that have a composite data type are of the "Undefined" type by default.
- Boolean- contains two values: True or False. Used, for example, in logical expressions - a logical expression is of type "Boolean".
- date- contains date and time. The default value is 01/01/01 00:00:00 start date of our era. Time is measured from the beginning of the day. An expression that has a literal type "date" is written as follows - "00010101000000". First the year is written down, then the month, then the date and then the time. The following entry is possible: "20041031". The default time is the beginning of the day.
- Line- can be variable, fixed or unlimited length. In general, it is recommended to use variable length strings.
- Number- the number bit depth has been increased to 38 bits.
- Type- serves to determine the types of values. Used, for example, to compare data types. It has no literals and is returned by the functions Type(<Имя типа>) or TypeValue(<Значение>).
Also, there are more complex data types. For example, the platform supports a number of types that are universal collections of values: array, structure, list of values, tree of values, etc.
Data types "Universal collections" - a list (set) of data objects of any type, the values of which can be accessed by brute force or by a specified index (key). The numbering of collection elements starts from 0. All specified data types are created only programmatically.
Array. Represents a numbered collection of values of an arbitrary type. An array element can be accessed by its index. The elements of an array can be, in particular, other arrays. This allows you to create multidimensional arrays.
Structure. Represents a named collection consisting of Key-Value pairs. The key can only be a string, the value can be of an arbitrary type. A structure element can be accessed by the value of its key, i.e. by name. Typically used to store a small number of values, each with a unique name.
Correspondence. Just like Structure, it is a collection of Key - Value pairs. However, unlike a Structure, a key can be of almost any type.
List of values. Typically used to solve interface problems. Allows you to build dynamic sets of values and manipulate them (add, edit, delete elements, sort). It can contain values of any type; in addition, the types of stored values in one list can be different.
Table of values. A value table allows you to build and manipulate dynamic sets of values. It can be filled with values of any type, and in one table the types of stored values can be different.
Tree of values. A value tree is a dynamically generated set of values of any type, similar to a table of values. Unlike a value table, the rows of a value tree can form hierarchical structures: each row in the tree can have a set of subordinate rows, each of the subordinate rows can in turn also have a set of subordinate rows, and so on. In this case, searching for values, sorting, and obtaining results can be carried out either according to the current hierarchy level, or including all subordinate ones.
COMSafeArray. Represents an object wrapper over a multidimensional SAFEARRAY array of
Procedure SelectFromFileClick(Element) // Selecting a file with viewingFileSelectionDialog = NewFileSelectionDialog(FileSelectionDialogMode.Open); FileSelectionDialog.Directory = ""; FileSelectDialog.Preview = True; FileSelectionDialog.FilterIndex = 0; If FileSelectDialog.Select() Then File = New File(FileSelectDialog.FullFileName); Image = NewValueStorage(NewImage(FileSelectionDialog.FullFileName)); DisplayImage(); endIf; End of Procedure
THIS_KEYWORD
<Это конструкция языка>,
<Это конструкция
языка>
THIS_FUNCTION(<Это конструкция языка>)
In rules describing a query language, language constructs are indicated in angle brackets. Keywords and function names are described in capital letters.
Language constructs can contain optional elements - keywords, etc. In the rules describing the query language, optional elements are enclosed in square brackets "[" and "]":
[THIS IS AN OPTIONAL WORD] [<Это необязательная конструкция>]
In some cases, the language design may use one of several alternative elements. Such elements in the rules are listed through a vertical bar “|”:
EITHER_THIS_WORD | OR THIS_WORD
<Либо эта конструкция>
| <Либо эта конструкция>
Descriptions of all constructs are accompanied by examples that explain how they are used in the query language.
Comments in Query Language
The request body may include comments. A comment is considered to be a part of a line that begins with the sequence of characters // and continues until the end of the line:
// This is a comment.
Comments are ignored when the request is executed.
Bilingual representation of keywords
One of the significant features of the 1C: Enterprise query language is that, as in the built-in language, all keywords have two spellings: in Russian and English. Later in this chapter, Russian spellings of keywords are indicated. Below is a table showing the correspondence between Russian and English and spelling options for keywords in the query language...... (omitted)
Main sections of the request text
The request text can be described by the following rule:
<Описание запроса>
[<Объединение запросов>]
[<Упорядочивание
результатов>]
[AUTO ORDER]
[<Описание итогов>]
As can be seen from this rule, the request text consists of several parts, or sections:
In section<Упорядочивание результатов>You can define ordering conditions for the rows in the query result. Ordering the result of a query is discussed on page 324.
AUTO ORDER allows you to enable automatic ordering of rows in the query result. This mode is described on page 331.
In section<Описание итогов>You can specify which totals should be calculated in the query. This section is described on page 332.
Request Description
As already mentioned, the request text must necessarily contain a request description section, which defines:
Fields that will be contained in the result of the request;
Query data sources - source tables;
Conditions affecting the selection of data in a request;
The order in which query results are grouped.
The request description section consists of several interrelated sentences:
SELECT [DIFFERENT] [FIRST<Количество>]
<Список
полей выборки>
[FROM<Список источников>]
[WHERE<Условие отбора>]
[GROUP BY<Поля группировки>]
[HAVING<Условие отбора>]
[FOR CHANGE [<Список таблиц верхнего уровня>]]
The request description begins with a required keyword CHOOSE.
Offer WHERE<Условие отбора> allows you to filter the query result. The result includes only those records for which the specified condition is true. The rules for describing selection conditions are discussed on page 315.
Offer FOR CHANGE is intended to indicate the need to block data read in a transaction.
Offer GROUP allows you to describe the order in which query results are grouped. Grouping is discussed in detail on page 316.
Offer HAVING allows you to impose conditions on grouping results. Described on page 318.
All query examples in this chapter provide the query text and the query result. It is assumed that the request text is passed as a parameter to the Execute method of the Request object.
Let's give an example of a fairly simple query consisting of one SELECT statement and a list of selection fields.
//You need to display a list of invoices in the report.
Query result:
Use of the word DIFFERENT
In many situations, it is desirable that the same rows in a report are not repeated.
// It is necessary to find out which counterparties in general
// goods were shipped for the period.
Select Document.Invoice.Counterparty
Query result:
It can be seen that the query result contains many repeated lines, which reduces its clarity. To avoid repetition, the keyword DIFFERENT should be specified in the query description.
Select Various Document.Invoice.Counterparty
Query result:
Use of the word FIRST
In some cases, it is necessary to display a limited number of rows in a report. To do this, in the query description you should specify the keyword FIRST, and after it - the required number of lines.
// It is necessary to select the five most expensive goods.
// The selection should be carried out in descending order of the product price.
Select First 5
Directory.Nomenclature.Name,
Directory.Nomenclature.PurchasingPrice
Sort by Directory.Nomenclature.PurchasePrice Descending
Query result:
Description of selection fields
After the mandatory keyword SELECT (and the qualifying words DIFFERENT and FIRST), a list of selection fields is specified in the request text. These fields will be processed when retrieving data in a request. The query result will also have the set of fields defined in this list. The selection fields are described according to the following rules:
<Описание поля>[ [HOW]<Псевдоним поля>]
<Выражение>[.<Группа полей>]
The list of selection fields consists of one or more elements separated by commas. Each<Поле выборки>consists of a description of the selection field and an optional field alias.
Instead of listing the fields in the selection list, you can specify an asterisk “*”. This will mean that the query result must contain all the fields that are in the source tables - the query data sources described in the list of sources.
Comment! When specifying an asterisk “*” in the list of selection fields, the virtual fields of the source tables are not included in the result.
<Описание поля>defines how the field values should be generated. In the simplest case, the selection field is a link to a field in the source table. The link can be specified by specifying the table containing this field, or without specifying the table itself. Field dereferencing is discussed at.
In general, the selection field can be not only a link to a field in the source table, but also some<Выражение>. Expressions are discussed in detail on page 344.
Query results can be grouped using aggregate functions specified as expressions in select fields. Grouping query results is discussed on page 316. Aggregate functions are described on page 345.
Each selection field can be assigned an alias. In the future it can be used for more convenient access to this field. The use of field aliases is discussed below.
<Группа полей>can only be specified when the select field points to a nested table. In this case, you can specify which fields should be processed in the nested table selection. If a field group is not specified, all fields of the nested table will be processed in the selection. Accessing nested tables is described in .
Field aliases in the selection list
If you assign an alias to a selection field, you can later refer to this field using its alias in the ORDER BY and TOTAL clauses, as well as when working with the result of a query. Such treatment may be more convenient and visual, and in some cases the only possible one.
The keyword HOW can precede the field alias. This word may not be specified at all, but if it is specified, the visibility and readability of the request text increases.
Field aliases are set in accordance with the rules for assigning variable identifiers. The aliases in the request cannot be the same.
Assigning aliases to fields does not in itself affect the data selection in the query.
// Must be selected from the product directory
// names of goods and names of groups.
Choose
Directory. Nomenclature. Name As Product,
Directory. Nomenclature.Parent.Name As Group
from
Directory.Nomenclature
Query result:
Note that the fields in the field query result are named "Item" and "Group". If field aliases were not specified, the fields in the query result would be called “Name” and “Name1” (the field names in the query result cannot match, so “1” is automatically added to the name of the second field), which is much less clear.
Nested tables in the list of selection fields
A field in the select list can reference a nested table in the query data source. In this case, the query result field will be of the Query Result type, that is, it will contain a nested query result generated on the basis of a nested source table.
By default, all fields of the nested table - the data source - are included in the nested result. It is possible to explicitly define a group of fields that should be contained in a nested query result. A group of nested result fields is described according to the following rule:
(<Список вложенных полей>) | *
<Вложенное поле [, <Вложенное поле>[, ...] ]
<Список вложенных полей>consists of one or more elements separated by commas. If the list consists of one element, it does not need to be enclosed in parentheses.
Instead of listing nested fields, you can specify an asterisk “*”; this will mean that the nested query result must contain all the fields that are in the nested table.
<Выражение>[[HOW]<Псевдоним поля>]
<Вложенное поле>may represent some expression. In the simplest case<Выражение>is a reference to a field in a nested table. Expressions are discussed in detail on page 344.
Each nested field can be assigned an alias. Further<Псевдоним поля>can be used for more convenient access to this field, similar to aliases for fields in the selection list - see the section “Aliases for fields in the selection list” on
Aliases can be assigned to nested fields regardless of whether an alias is assigned to the nested table itself.
//It is necessary to display the specification of invoices in the report,
// the document itself, nomenclature and quantity.
Choose
Document.Invoice.Composition.(Nomenclature As Product, Quantity)
Query result:
Link Compound Product Quantity Jeans for women 4 Jeans for women 5 Shirt "Cowgirl" 5 Invoice 00005 dated 02/24/2002 0:00:00 Jeans for women 1 Jeans for women 1 Moydodyr "Aquarium" 5 Sink "Lily" 8 Mixer "Ultra" 10
Please note that the “Composition” field of the query result is a nested table that has the “Nomenclature” and “Quantity” fields.
//Display all fields of the tabular part of the invoice into the report.
Choose
Document.Invoice.Link,
Document.Invoice.Composition.*
The purpose of the IZ clause is to designate a list of source tables - data sources used in a given SELECT statement.
It should be noted that the IZ clause in the query language is optional. It may be omitted if the data sources are fully qualified in the description of the list of selection fields contained in the SELECT clause. Please note that a number of the examples in the previous sections did not contain the IZ clause.
After the keyword IZ, a list of sources is indicated. In general, the list of sources is described by the following set of rules:
<Источник>[, <Источник>[, ...]]
Query data sources are listed in the source list, separated by commas. Every<Источник>the list of sources must include a description of the source; in addition, it can be specified<Перечень соединений>- rules for connecting a source to other sources. Connection specifications are described.
<Описание источника> [ <Перечень соединений> ]
If the data source is an infobase table,<Описание источника>contains<Имя таблицы>.
<Таблица>[ [HOW]<Псевдоним источника>]
If the source table is virtual, you can specify<Параметры>its formation. Virtual table parameters are described in detail in the “Query Data Sources” section.
<Имя таблицы> [(<Параметры>)] | <Описание запроса>
A subquery can also act as a query data source; in this case the source description contains<Описание запроса>. The use of nested queries is described at.
The description of the data source can also assign its alias. Further<Псевдоним источника>can be used for more convenient access to this source. The use of data source aliases is discussed at.
Connection Specifications
When defining multiple sources in the source list, for each record from the first source table, a selection is made from the second source table, and so on. Thus, the query results in all possible combinations of all records from all specified sources.
Query result:
Counterparty Bank Suppliers JSCB InvestBank Suppliers JSCB PromStroyBank Knitting factory "Zarya" JSCB InvestBank Knitting factory "Zarya" JSCB PromStroyBank Denim clothing factory JSCB InvestBank Denim clothing factory JSCB PromStroyBank Buyers JSCB InvestBank Buyers JSCB PromStroyBank Clothing fair JSCB InvestBank Clothing fair JSCB PromStroyBank Trading house "Budenovsky" JSCB InvestBank Trading house "Budenovsky" JSCB PromStroyBank Pavilion 45 at the wholesale market JSCB InvestBank Pavilion 45 at the wholesale market JSCB PromStroyBank Bavaria - porcelain JSCB InvestBank Bavaria - porcelain JSCB PromStroyBank Denim clothing factory JSCB InvestBank Denim clothing factory JSCB PromStroyBank JSCB PromStroyBank JSCB InvestBank JSCB PromStroyBank JSCB PromStroyBank
The query result contains combinations of all counterparties with all banks. As a rule, such a result in itself does not make sense. Typically, combinations of records from different source tables need to be limited by some conditions. It is possible in a query language to describe such a combination of sources by specifying the sources themselves and defining the conditions under which combinations of records from these sources need to be included in the query result.
There are several types of connections, they are described by the following rules:
<Соединение> [<Перечень соединений>]
In general<Перечень соединений>can contain and describe not only one connection (of two sources), but also several connections of several sources at once.
[INTERNAL] JOIN<Описание источника>BY<Условие отбора> |
LEFT [OUTER] JOIN<Описание источника>BY<Условие отбора> |
RIGHT [OUTER] JOIN<Описание источника>BY<Условие отбора> |
FULL (OUTER] JOIN<Описание источника>BY<Условие отбора>
<Условие отбора>contains conditions according to which the selection must combine data from the original tables - the sources of the query. Rules for describing conditions in a query language are discussed on page 357.
The keywords LEFT, RIGHT and FULL clarify the nature of the connection. The words INTERNAL or EXTERNAL may not be specified at all; they increase the clarity and readability of the request text.
The sources being joined are not equivalent to each other, and in some cases the result depends on which table is listed first, before the JOIN keyword (to the left of it), and which table is listed second (to the right).
[INTERNAL] JOIN means that from both source tables - data sources, only those combinations of records that meet the specified condition must be included in the query result. The remaining records are not included in the result.
// Need to find out which banks are at the same time
// counterparties (the same names are present
//both in the Counterparties directory and in the Banks directory).
Choose
Banks.Link How to Bank
From
Inner Join
Directory.Banks How Banks
By
Query result:
Counterparty Bank JSCB PromStroyBank JSCB PromStroyBank
A LEFT [OUTER] JOIN means that the query result must include combinations of records from both source tables that meet the specified condition. But, unlike an internal join, the query result must also include records from the first source (indicated to the left of the word JOIN) for which no records from the second source were found that match the condition.
This way, the query result will include all records from the first source; they will be merged with records from the second source if the specified condition is met. Query result lines for which no records from the second source were found that match the condition will contain NULL in the fields generated based on records from this source.
//It is necessary to display all counterparties in the report, and for those
// who is also a bank - indicate a link to the bank.
Choose
Counterparties.Link As Counterparty,
Banks.Link How to Bank
From
Directory. Counterparties How Counterparties
Left Outer Join
Directory.Banks How Banks
By
Counterparties.Name = Banks.Name
Query result:
A RIGHT [OUTER] JOIN means that the query result must include combinations of records from both source tables that meet a specified condition. In addition, the query result must also include records from the second source (indicated to the right of the word CONNECTION) for which no records from the first source were found that match the condition.
Thus, the query result will include all records from the second source; they will be merged with records from the first source if the specified condition is met. Query result lines for which no records from the first source were found that match the condition will contain NULL in the fields generated based on records from this source.
//It is necessary to display all banks in the report, and for those
// who is also the counterparty - indicate a link to the counterparty.
CHOOSE
Counterparties.Link As Counterparty,
Banks.Link Like Bank
FROM
Directory. Counterparties How Counterparties
Right Outer Join
Directory.Banks How Banks
By
Counterparties.Name = Banks.Name
Query result:
Counterparty Bank NULL JSCB InvestBank JSCB PromStroyBank JSCB PromStroyBank
A FULL [OUTER] JOIN means that the query result must include combinations of records from both source tables that meet a specified condition. In addition, the query result must also include those records from both sources for which no matches were found.
This way, the query result will include all records from both sources; they will be connected to each other when the specified condition is met. Query result lines for which no records from any source were found that match the condition will contain NULL in the fields generated based on records from that source.
// It is necessary to display all counterparties and all banks in the report,
// and those who are both - print in one line.
Choose
Counterparties.Link As Counterparty,
Banks.Link How to Bank
From
Directory. Counterparties How Counterparties
Full Outer Join
Directory.Banks How Banks
By
Counterparties.Name = Banks.Name
Query result:
Data source aliases
If you assign an alias to a data source, then in the future this source can be accessed using this alias (and can no longer be accessed by specifying the table name). Such treatment may be more convenient and visual, and in some cases the only possible one.
The alias is set in accordance with the rules for assigning variable identifiers. The aliases in the request cannot be the same.
The keyword HOW may precede the source alias. This word may not be specified at all, but if it is specified, the visibility and readability of the request text increases.
Assigning aliases to sources does not in itself affect the data selection in the query.
// This example demonstrates the use
// in the list of selection fields of the alias Product,
// assigned to the source table Directory.Nomenclature
Choose
Product.Name,
Product.Parent
From
Directory.Nomenclature.Product
Nested tables in the source list
The list of sources may also include nested tables - tabular parts of reference books and documents.
//It is necessary to display the specification of invoices in the report -
// show the document itself, nomenclature and quantity.
//The list of sources contains the nested table “Composition” -
// tabular part of the invoice.
// The selection is limited to eight records so as not to overload the example.
Select First 8
Link, Nomenclature, Quantity
From
Document.Invoice.Composition
Query result:
Link Nomenclature Quantity Invoice 00007 dated 02/25/2002 21:03:21 Jeans for women 4 Invoice 00006 dated 02/25/2002 0:00:00 Jeans for women 5 Invoice 00006 dated 02/25/2002 0:00:00 Shirt "Cowgirl" 5 Invoice 00005 from 03/01/2002 20:58:28 Jeans for women 1 Invoice 00004 dated 03/01/2002 20:50:40 Jeans for women 1 Invoice 00003 dated 02/23/2002 0:00:00 Moydodyr "Aquarium" 5 Invoice 00003 dated 02/23/2002 0:00:00 Sink "Lily" 8 Invoice 00003 dated 02/23/2002 0:00:00 Mixer "Ultra" 10
Please note that when specifying a nested table in the list of sources, it is possible to access both the fields of the nested table itself and the fields of the top-level table (the one that contains the nested table). In this case, the “Link” field of the document itself is accessed.
Subqueries in the source list
In the list of query sources, a subquery can be used as the source table. In this case, the source description contains the subquery description. The description of a nested query is compiled in exactly the same way as a regular one: see
Using a nested query as a data source is no different from using an infobase table. All fields described in the list of subquery selection fields are available as fields of such a source.
The result will be exactly the same as in the previous example.
Generating reports
Working with requests
To work with queries, a built-in language object is used Request . It allows you to obtain information stored in database fields in the form of a sample formed according to specified rules.
Query Data Sources
The query receives initial information from a set of tables. These tables represent data from real database tables in an easy-to-analyze form. They can be divided into two large groups: real and virtual.
Real tables, in turn, can be object (reference) or non-object (non-reference):
The distinctive feature of real tables is that they contain data from a single real table stored in a database. For example, real tables are the “Directory.Clients” table, corresponding to the “Clients” directory, or the “Accumulation Register. Material Remaining” table, corresponding to the “Material Remaining” accumulation register.
Virtual tables are formed primarily from data from multiple database tables. For example, a virtual table is the table “Accumulation Register. Material Remainings. Balances and Turnovers”, formed from several tables of the accumulation register “Material Remainings”. Sometimes virtual tables can be formed from one real table (for example, the virtual table “Prices.SliceLast” is formed based on the information register table “Prices”). However, what all virtual tables have in common is that they can be given a number of parameters that will determine what data is included in those virtual tables. The set of such parameters may be different for different virtual tables, and is determined by the data stored in the source database tables.
Real tables are divided into object (reference) and non-object (non-reference) tables.
Object (reference) tables provide information about reference data types (directories, documents, plans for types of characteristics, etc.). And in non-object (non-reference) - all other data types (constants, registers, etc.).
A distinctive feature of object (reference) tables is that they contain a “Link” field containing a link to the current record. In addition, for such tables it is possible to obtain a custom representation of the object; these tables can be hierarchical and the fields of such tables can contain nested tables (table parts).
Query language
The algorithm by which data will be selected from the source tables of the request is described in the text of the request in a special language - query language. The request text consists of several parts:
request description,
merging queries
ordering the results,
auto ordering,
description of the results.
The only mandatory part of the request is the first one - the description of the request. All others are present as needed.
Request Description defines data sources, selection fields, groupings, etc.
Merging queries determines how the results of multiple queries will be combined.
Organizing results defines the ordering conditions for query result rows.
Auto-order allows you to enable automatic ordering of query result rows.
Description of results determines which totals should be calculated in the query and how to group the results.
Report Register of Documents Provision of Services
The first report on the basis of which we will begin to get acquainted with the query language will be the “Register of Documents for the Provision of Services” report. This report will simply display a list of “Service Provision” documents existing in the database in the order of their dates and numbers.
Let's create a new configuration object in the configurator: Report “Register of Documents Provision of Services”. Let's go to the "Layout" tab and launch the output form designer.
As a data source for the request, we will select the object (reference) table of the “Service Provision” documents. From this table we select the following fields:
"Master",
"Client":
Please note that when you select the fields “Warehouse”, “Master” and “Customer”, the fields “Warehouse.View”, “Master.View” and “Customer.View” are also selected in the list of selected fields. The fact is that in the general case it is assumed that these fields will be displayed in the cells of a spreadsheet document. Since the corresponding fields “Warehouse”, “Master” and “Customer” are reference fields, if a reference value is passed as a parameter value for output, the system will perform an additional request to obtain a representation of this field (which will be displayed in document), resulting in slower report output. Therefore, when selecting reference fields, the system offers to immediately include representations of reference fields in the list of selected fields, in the expectation that they will be used for output to the document.
After this, let’s go to the “Order” tab and indicate that the request result must first be ordered by the value of the “Date” field, and then by the value of the “Service Provision. Link” field:
Let's go to the "Report" tab and reset the "Use report builder" flag:
Let's reset the "Use report builder" flag...
Click "OK". The designer will generate the report form and layout. Let’s open the form module and find in it the procedure “Register of Documents Provision of Services”. In this procedure, how the request text will be generated, which will be used to obtain the data we are interested in:
Query.Text = “SELECT
Provision of Services.Date AS Date,
Provision of Services. Number AS Number,
Provision of Services. Warehouse,
Providing Services. Warehouse. Presentation,
Providing Services. Master,
Providing Services. Master. Presentation,
Provision of Services. Client,
Provision of Services. Client. Representation
Document.Provision of Services HOW to ProvideServices
SORT BY
The request text begins, as we said above, with part of the request description:
I Provision of the Service. Date AS Date,
I Provision of Services. Number AS Number,
I Provision of Services. Warehouse,
I Provision of Services. Warehouse. Presentation,
I Providing Services. Master,
I Provision of Services. Master. Presentation,
I Provision of Services. Client,
1 Provision of Services. Clients. Representation
I Document. Provision of Services HOW to Providing Services
The request description begins with a required keyword CHOOSE. This is followed by a list of select fields that describes the fields that should be contained in the query result. This list can contain both the fields themselves and some expressions calculated based on the field values.
After the IZ keyword, data sources are indicated - the original query tables, the contents of which are processed in the query. In this case, this is the object (reference) table “Document.Provision of Service”. After the keyword HOW indicated pseudonym data source. In our case, this is “Providing Services”. In the future, this data source can be accessed in the body of the request using an alias.
We see this call in the description of the selection fields:
| Provision of Services.Date AS Date,
| Provision of Services. Number AS Number,
| Provision of Services. Warehouse,
| Providing Services. Warehouse. Presentation,
| Providing Services. Master,
| Providing Services. Master. Presentation,
| Provision of Services. Client,
| Provision of Services. Client. Representation
Selection fields can also have aliases, which can be used to refer to this field later in the request text. In our case, these are the aliases “Date” and “Number”.
After the description part of the query in our example comes the result ordering part:
|ORDER BY
| Date, | Number";
Offer SORT BY allows you to sort the rows in the query result. After this key clause is an ordering expression, which, in general, is a listing of fields (expressions) and output order. In our case, the ordering will be performed first by the selection field, which is accessed through the alias - “Code”, and then by the field - “Number”. In both cases the sort order will be ascending, which is the default sort order.
Now let's pay attention to how the query result is displayed in a spreadsheet document.
Procedure Register of Documents Provision of Services (TabDoc) Export
//((CONSTRUCTOR_OUTPUT_FORM(Register of Documents Provision of Services)// This fragment was built by the constructor.// When reusing the constructor,// introduced manually changes will be lost!!!
Layout = GetLayout("Register of Documents Providing Services"); Request = New Request;
Result = Query.Run();
HeaderArea = Layout.GetArea("Header"); AreaBasement =
Layout.GetArea("TableFooter"); DetailRecordsArea =
TabDoc.Output(TableHeadArea); TabDoc.StartAutogruttingRows();
SelectDetails = Result.Select();
While SelectDetails.NextFunctions() Loop
AreaDetailRecords.Parameters.Fill(SelectionDetails);
TabDoc.Output(RecordsDetailsArea,DetailsSelection.Level()); EndCycle;
/L)CONSTRUCTOR_OUTPUT_FORM EndProcedure
The report form contains a control element TabularDocumentField with the name “TabDoc”, which is filled with data based on the layout generated by the designer.
At the beginning of the procedure, we obtain the report layout, from which we then obtain the areas existing in it into the corresponding variables:
HeaderArea = Layout.GetArea("Header"); AreaBasement =
Layout.GetArea("Basement"); TableHeaderArea =
Layout.GetArea("TableHeader"); TableFooterArea =
Layout.GetArea("TableFooter"");DetailRecordsArea =
Layout.GetArea("Details");
Then we clear the spreadsheet document and display those areas that do not contain data obtained from the query result:
TabDoc.Clear(); TabDoc.Output(AreaHeader);
TabDoc.Output(TableHeadArea); TabDoc.StartAutoGroupingRows();
In the last line, the designer added the beginning of auto-grouping of rows. In this example, we don't have rows that need to be grouped, but by default the designer always suggests grouping rows in a spreadsheet document. This call will not affect the speed of report output, so we will leave the constructor text unchanged.
After this, we get a selection from the query result, which we go through in a loop:
In each iteration of the loop, we fill the parameters of the previously obtained layout area with values obtained from the next query result sample record and display this area in a spreadsheet document.
At the end of the procedure, we display the final areas of the layout in a spreadsheet document:
TabDoc.FinishAutoGroupingRows();
TabDoc.Output(TableFooterArea);
TabDoc.Output(AreaFooter);
Now let’s launch 1C:Enterprise in debug mode and look at the result of our report:
Thus, using the example of this report, we demonstrated how to use the output form designer and became familiar with some basic query language constructs.
The “Service Rating” report will contain information about which services brought Master of All Trades LLC the greatest profit in the specified period. Using the “Service Rating” report as an example, we will illustrate how to select data in a certain period, how to set query parameters and how to use data from several tables in a query and include all data from one of the sources in the query result.
Let’s create a new configuration object “Service Rating” Report. Let's go to the "Layouts" tab and call the output form constructor.
Let’s select the object (reference) table of the directory “Nomenclature” and the virtual table of the accumulation register “Sales.Turnover”. In order to eliminate the ambiguity of names in the query, we rename the “Nomenclature” table to “SprNomenclature” (right-click context menu).
Then place the cursor on the “SalesTurnover” table and call up the dialog for entering virtual table parameters:
Open the dialog for entering virtual table parameters
Let us indicate that the beginning and end of the period will be passed in the corresponding parameters “StartDate” and “EndDate” (the “&” symbol before the name indicates that this is a request parameter):
Then select from the tables the fields “SprNomenclature.Link” and “SalesTurnover.RevenueTurnover”:
SprNomenclature.Presentation
SalesTurnoverRevenueTurnover
Let's go to the "Links" tab and see that the designer has already created a connection between the two selected tables - the value of the "Nomenclature" register change should be equal to the reference to the "Nomenclature" directory element.
The only thing left for us to do is reset the “All” flag for the register table and set it for the directory table.
We will select all elements from the “Nomenclature” directory
Setting the “All” flag at the directory table will mean that all elements will be selected from the directory and these elements will be assigned the revenue turnover value from the register. Thus, as a result of the request, all services will be present, and for some of them the revenue turnover will be indicated. For those services that were not provided in the selected period, nothing will be indicated.
Let’s go to the “Conditions” tab and set the conditions for selecting elements from the “Nomenclature” directory. When setting the selection conditions, we will again use query parameters. The first condition must be that the selected element is not a group (to do this, switch to the “Custom Condition” mode).
The second condition must be that the selected item is a service (this is the “Simple Condition”):
In the future, before executing the request, we will pass the corresponding enumeration value into the “Type of Nomenclature” parameter.
Let’s go to the “Associations/Aliases” tab and specify that the directory item view will have the alias “Service”, and the register field will have the alias “Revenue”:
Let's go to the "Order" tab and indicate that the query result should be sorted in descending order of the value of the "Revenue" field.
On the “Totals” tab, we determine that we need to display general totals, and they should be the sum of the values in the “Revenue” field:
On the “Report” tab, clear the “Use report builder” flag.
Now let's go to the "Output Form" tab. Let us indicate that the parameters “End Date” and “Start Date” will be edited in the form in input fields of the “Date” type. For the “Type of Nomenclature” parameter, on the contrary, we will remove the edit flag in the form:
Click "OK". The platform will generate a layout and report form. Open the form module and find the “Service Rating” procedure in it.
In this procedure, in the part where the query parameters are set, we will determine the value of the “Nomenclature Type” parameter (corrections are highlighted in bold):
| LEFT CONNECTION RegisterAccumulation.Sales,Turnover(&StartDate,
| &Expiration date,)
| HOW TO SALESTurnover
|ORDER BY | RevenueDOWN
|RESULTS AMOUNT (Revenue) BY | ARE COMMON";
RequestSetParameterC"Type of Nomenclature",
Transfers.Types of Nomenclature.Service);
Query.SetParameter("StartDate",StartDate); Request.SetParameterC"EndDate", EndDate);
Now let's look at the request text generated by the constructor:
| SprNomenclature.Representation AS Representation,
|SalesTurnover.RevenueTurnover AS Revenue
| Directory.Nomenclature AS RefNomenclature
LEFT CONNECTION RegisterAccumulations.Sales.Turnover(&StartDate,
| HOW TO SALESTurnover
| Software SalesTurnover.Nomenclature = SprNomenclature.Link
| (RefNomenclature.ThisGroup = False) AND
| SprNomenclature.Type of Nomenclature = &Type of Nomenclature
|ORDER BY
| RevenueDOWN
|RESULTS AMOUNT (Revenue) BY
First, as usual, comes the request description part and it contains constructions that are new to us.
When describing the request sources (after the IZ keyword), the ability to define several request sources was used:
| Directory.Nomenclature AS RefNomenclature
|&EndDate,)
| HOW TO SALESTurnover
| Software SalesTurnover.Nomenclature = SprNomenclature.Link
In this case, records are selected from two sources: “SprNomenklatura” and “SalesTurnover”, with the key sentence LEFT CONNECTION... BY describes the way in which records from these two sources will be combined.
LEFT CONNECTION means that the query result must include combinations of records from both sources that match the condition specified after the keyword BY. In addition, the query result must also include records from the first one (indicated to the left of the word COMPOUND) source for which no records matching the condition were found from the second source.
There is nothing new for us in the description of the first source and the connection condition, but when describing the second source, we use the ability to set the parameters of the query virtual table:
| RegisterAccumulations.Sales.Turnover(&StartDate, &EndDate,)
The first parameter is the beginning of the period for calculating the totals, the second is the end of the period. As a result, the source table will contain only the turnover calculated in the transferred period. Here you should always remember that if we pass a date as these parameters (and in our case this will be the case), then the date also contains the time accurate to the second.
If it is known in advance that the user will not be interested in the results of the report in periods specified with an accuracy of seconds, then the following feature should be taken into account: by default, the time in the date is set to 00:00:00. Therefore, if you do not take special measures, it turns out that when the user sets the reporting period from 03/01/2004 to 03/31/2004, the register totals will be calculated from the beginning of the day 03/01/2004 00:00:00 to the beginning of the day 03/31/2004 00:00: 00. Thus, data for the 31st day, other than the beginning of the day, will not be included in the calculation, which will greatly surprise the user.
In order to eliminate this situation, two things should be done.
First, in the report form, limit the user’s ability to enter the start date and end date by setting the date composition for the corresponding input fields as “Date”:
Let's determine the composition of the date...
Secondly, when passing parameters, use the built-in function End of the day(). To do this, return to the report form module and make the necessary changes (additions are highlighted in bold):
ProcedureFormActionsRatingServicesGenerate(Button) //((CONSTRUCTOR_WEEKEND_FORM_PROCEDURE_CALL(RatingServices) //This fragment was built by the constructor. // When reusing the constructor, // manually made changes will be lost!!!
TabDoc = FormElements.TableField;
//))CONSTRUCTOR_OUTPUT_FORM_CALL_PROCEDURE
Co. netProcedures
Let's continue to look at the request text. As part of the query description, there is another construction that is new to us - setting conditions for selecting data from the source tables:
| SprNomenclature.Representation AS Representation,
| SalesTurnover.RevenueTurnover AS Revenue
| Directory.Nomenclature AS RefNomenclature
| LEFT CONNECTION RegisterAccumulations.Sales.Turnover(&StartDate,
| &Expiration date,
| HOW TO SALESTurnover
| Software SalesTurnover.Nomenclature = SprNomenclature.Ssshka
| SprNomenclature.ThisGroup = False AND
| SprNomenclature.Type of Nomenclature = &Type of Nomenclature
The selection condition is always preceded by a keyword WHERE. After it the condition itself is described. Please note that the fields of the source tables on which the condition is applied may not be included in the selection list (as in our case). In addition, our condition uses the “Type of Nomenclature” query parameter.
RESULTS AMOUNT (Revenue) PO
It always starts with a keyword RESULTS, followed by a description of what totals will be present in the query result. Immediately after the word RESULTS describes the aggregate functions that need to be calculated in the results. In our case, the amount in the “Revenue” field will be calculated. This is followed by the keyword PO, after which the groupings in which the totals are to be calculated are described. In our case they are absent and only the keyword is used ARE COMMON, which indicates that the totals will be calculated for the entire table as a whole.
Now that we have finished getting acquainted with the text of the request, let’s launch 1C:Enterprise in debugging mode and see how our report works.
Let's set the reporting period from 03/01/2004 to 04/30/2004. The result will look like this:
Now let's change the end date to 03/31/2004 and make sure that the data for March 31 is included in the report:
