1C:Enterprise 8.3. Practical Developer’s Guide. Lesson 14 (3:20). Optimization of posting the Services document. Automatic cost calculation

Automatic cost calculation

Let us proceed to the second step.

Up to this point you entered the cost of consumed materials to the Services document manually during its creation.

Now let us define the cost of materials using average values: for each material its total cost is divided by the number of materials available in order to get an average cost of one material unit.

To perform this calculation, you need additional data that you do not have for now.

For each material type in the tabular section you need:

  • Material cost, which is stored in the CostOfMaterials register
  • Total number of material units in all warehouses, which is stored in the BalanceOfMaterials register

So you need to modify your query to get this data from the database.

You need the query to retrieve the following fields for each material in the document (fig. 14.9).

Lesson 14 (3:20). Optimization of posting the Services document / Automatic cost calculation
Fig. 14.9. Query field descriptions

The first four fields can be retrieved (in fact, they are already retrieved) from the tabular section of the document itself, while the latter two fields should be retrieved from other database tables:

  • Cost is retrieved from the CostOfMaterials register
  • Total balance in all warehouses is retrieved from the BalanceOfMaterials register (fig. 14.10)

Lesson 14 (3:20). Optimization of posting the Services document / Automatic cost calculation
Fig. 14.10. Query field and table descriptions

This means that the query should include two left joins of the document table with other tables: the left join with the AccumulationRegister.CostOfMaterials.Balance table, and the left join with the AccumulationRegister.BalanceOfMaterials.Balance table.

Now it seems that everything is ready to generate the query.

But note one important aspect: in the suggested arrangement virtual tables return costs and balances for all the materials. And you only need this data for the materials available in the document.

This might not be noticeable in a small database because the number of unique materials in a catalog is almost equal to the number of unique materials in a document.

But imagine a real-life database. Suppose the MaterialsAndServices catalog includes 15 000 items. And a document only includes 5 items. The virtual table works heavily to calculate the cost (or balance) for all the 15 000 items, and when this table is connected with the document table using the left join, only 5 rows that correspond to the materials specified in the document are used. The remaining 14 995 rows are simply discarded so it is a waste of efforts to calculate them.

In real life such wastage is not acceptable and this query is far from optimal. So you should add a filter condition to all the virtual tables that you use in order to select only the materials contained in the tabular section of the document. In this scenario cost and balance are only calculated for the required materials instead of all the materials.

This gives you the following query pattern (fig. 14.11).

Lesson 14 (3:20). Optimization of posting the Services document / Automatic cost calculation 
Fig. 14.11. Query pattern

Note that both virtual tables use the list of materials from the document tabular section (the data is calculated only for this list).

Besides, not all the data is retrieved from the document tables: instead, only the data related to a specific document is retrieved. In order to avoid generating this list three times (once for the document and again for each virtual table), you can have it generated in advance for later use in query conditions.

Temporary tables will help here.

A temporary table is a script object that is created and populated with data by a developer. Then queries can use this data when needed. For example, they can use it to apply some complex condition, which is the case in our scenario.

This gives you the following query pattern (fig. 14.12).

Lesson 14 (3:20). Optimization of posting the Services document / Automatic cost calculation 
Fig. 14.12. Query pattern

Let us proceed to implementing the automatic cost calculation.

In Designer mode

The first thing you have to do is deleting the Cost attribute of the Services document because you do not need it any more.

  1. Open the editor of the Services document configuration object, click the Data tab, expand the list of document tabular section attributes, click Cost, and click the Delete current item Lesson 14 (3:20). Optimization of posting the Services document / Automatic cost calculation / In Designer mode button in the command bar (fig. 14.13).
    Lesson 14 (3:20). Optimization of posting the Services document / Automatic cost calculation / In Designer mode
    Fig. 14.13. Deleting a tabular section attribute

    You also have to delete the corresponding field from the MaterialsAndServices table located in the document form.
  2. For the Services document, open the DocumentForm form and then, in the list of form elements, click MaterialsAndServicesCost, and click the Delete current item Lesson 14 (3:20). Optimization of posting the Services document / Automatic cost calculation / In Designer mode button in the command bar (fig. 14.14).

    Lesson 14 (3:20). Optimization of posting the Services document / Automatic cost calculation / In Designer mode
    Fig. 14.14. Deleting a tabular section field

    Then let us deal with the query. First, let us create the temporary table using the query that you have created earlier.
  3. Open the Services document module.
  4. In the Posting() procedure, before the query generation, add the lines that create a temporary tables manager and specify that the query uses the manager (listing 14.17).

    Listing 14.17. Using a temporary tables manager

    RegisterRecords.BalanceOfMaterials.Write = True;
    RegisterRecords.CostOfMaterials.Write = True;
    RegisterRecords.Sales.Write = True;
    	
    // Creating temporary tables manager
    TTManager = New TempTablesManager;
    	
    Query = New Query;
    	
    // Specifying the temporary tables manager used by the query
    Query.TempTablesManager = TTManager;
    	
    Query.Text = 
        "SELECT
        |    ServicesMaterialsAndServices.MaterialOrService,
    Let us modify the query so that it creates a temporary table to be stored in the TTManager temporary tables manager.
  5. Delete the query line shown in listing 14.18.

    This is required to make the query suitable for opening with the query wizard (as you do not have the Cost field anymore).

    Listing 14.18. Line to remove

    |    MAX(ServicesMaterialsAndServices.Cost) AS Cost
  6. Delete the comma in the end of the previous line (listing 14.19).

    Listing 14.19. Line to modify

    |    SUM(ServicesMaterialsAndServices.Total) AS TotalInDocument
  7. Right-click anywhere inside the query (for example, on the SELECT word) and click Query Builder.

    This opens the query text in the form of the query wizard (fig. 14.15).

    Lesson 14 (3:20). Optimization of posting the Services document / Automatic cost calculation / In Designer mode
    Fig. 14.15. Query wizard

    Let us store the query result to a temporary table.
  8. Click the More tab and then click Create temporary table.
  9. In the Temporary table name field, enter DocumentMaterialsAndServices (fig. 14.16).

    Lesson 14 (3:20). Optimization of posting the Services document / Automatic cost calculation / In Designer mode
    Fig. 14.16. Creating a temporary table
  10. Click OK and review the text generated by the query wizard (listing 14.20).

    Listing 14.20. Query text

    "SELECT
    |    ServicesMaterialsAndServices.MaterialOrService,
    |    ServicesMaterialsAndServices.MaterialOrService.MaterialServiceType AS MaterialServiceType,
    |    SUM(ServicesMaterialsAndServices.Quantity) AS QuantityInDocument,
    |    SUM(ServicesMaterialsAndServices.Total) AS TotalInDocument
    |INTO DocumentMaterialsAndServices
    |FROM
    |    Document.Services.MaterialsAndServices AS ServicesMaterialsAndServices
    |WHERE
    |    ServicesMaterialsAndServices.Ref = &Ref
    |GROUP BY
    |    ServicesMaterialsAndServices.MaterialOrService,
    |    ServicesMaterialsAndServices.MaterialOrSevice.MaterialServiceType";
    There is only one new line here (listing 14.21).

    Listing 14.21. Creating a temporary table

    |INTO DocumentMaterialsAndServices
    This line means that the query result is stored to the DocumentMaterialsAndServices temporary table.

    If you specify the same temporary tables manager (TTManager) for another query, you will be able to access the data of this temporary table in that query.

    So you have completed a part of the task: created a query that stores the document tabular section data to a temporary table (fig. 14.17).

    Lesson 14 (3:20). Optimization of posting the Services document / Automatic cost calculation / In Designer mode
    Fig. 14.17. Creating the first query

    Let us proceed to designing the second query.
  11. Put the cursor into the line following the clause QueryResult = Query.Execute(); (this is where the temporary table is created) and create a template for your future query (listing 14.22).

    Listing 14.22. Creating the second query

    Query2 = New Query;
    Query2.TempTablesManager = TTManager;
    Query2.Text = "";
    You have created a new Query object and specified the same temporary tables manager for this query. Now the query can access the temporary table that you created earlier.
  12. Right-click the area between the quotation marks and click Query Builder.
  13. Confirm that you want to create a new query.

    Since you are going to select data from your temporary table, let us add the temporary table description to the query.
  14. Above the Tables list, click the Create a temporary table description Lesson 14 (3:20). Optimization of posting the Services document / Automatic cost calculation / In Designer mode button (fig. 14.18).

    Lesson 14 (3:20). Optimization of posting the Services document / Automatic cost calculation / In Designer mode
    Fig. 14.18. Creating a temporary table description
  15. In the window that is opened, in the Table name field, enter DocumentMaterialsAndServices.
  16. Add the following field descriptions:
    • MaterialOrService. Type: CatalogRef.MaterialsAndServices
    • MaterialServiceType. Type: EnumRef.MaterialServiceTypes
    • QuantityInDocument. Type: Number, length: 15, precision: 3
    • TotalInDocument. Type: Number, length: 15, precision: 2
    The resulting description of the temporary table should look as shown in fig. 14.19.

    Lesson 14 (3:20). Optimization of posting the Services document / Automatic cost calculation / In Designer mode
    Fig. 14.19. Creating a temporary table description
  17. Click OK.
  18. Select all the fields from this table (fig. 14.20) and click the Query button in the bottom left corner of the query wizard. 

    Lesson 14 (3:20). Optimization of posting the Services document / Automatic cost calculation / In Designer mode
    Fig. 14.20. Selected temporary table fields

    The query text should look as shown in listing 14.23.

    Listing 14.23. Text of the second query

    SELECT
        DocumentMaterialsAndServices.MaterialOrService,
        DocumentMaterialsAndServices.MaterialServiceType,
        DocumentMaterialsAndServices.QuantityIndocument,
        DocumentMaterialsAndServices.TotalInDocument
    FROM
        DocumentMaterialsAndServices AS DocumentMaterialsAndServices
    So you have created the first part of the second query, which selects data from the temporary table (fig. 14.21).

    Lesson 14 (3:20). Optimization of posting the Services document / Automatic cost calculation / In Designer mode
    Fig. 14.21. Creating the second query

    Now you need left joins to connect this query part with the balance tables.

    Let us begin with cost of materials.
  19. Add the AccumulationRegister.CostOfMaterials.Balance virtual table to the list of query tables. Select the CostBalance field from this table.

    Let us define the relation between the tables.
  20. Click the Links tab and specify that all the records are selected from the temporary table and the MaterialOrService field of the temporary table matches the Material field of the balances table (fig. 14.22).

    Lesson 14 (3:20). Optimization of posting the Services document / Automatic cost calculation / In Designer mode
    Fig. 14.22. Relation between the tables

    You also need to filter the virtual table data, so that only materials available in the temporary table are selected.
  21. Click the Tables and fields tab, click the CostOfMaterialsBalance table, and click the Virtual table parameters Lesson 14 (3:20). Optimization of posting the Services document / Automatic cost calculation / In Designer mode button above the list of tables.
  22. In the Condition field, enter the line shown in listing 14.24.

    Listing 14.24. Virtual table condition

    Material IN (SELECT DocumentMaterialsAndServices.MaterialOrService FROM	DocumentMaterialsAndServices)
    This means that material should be in the list of materials and services selected from the temporary table.

    Learn more! Be careful in how you use virtual query tables. Be particularly mindful of using virtual table parameters as much as possible.

    For example, in this scenario you could avoid using the Condition parameter and filter the field selection in the query itself, by using the BY condition with materials and services from the document equal to materials from the balance table. Technically it gives exactly the same result, but not the same performance.

    In the scenario that you implemented the number or records retrieved from the virtual table is equal to the number of unique materials and services in the document being posted. And if you set no conditions, the virtual table provides you with the records for all the materials that are available in the accumulation register, and you have to filter that large list of records in the query to get the several records that you need.

    Clearly, the second option would take longer to complete, and the execution time for such query would depend less on the amount of data contained in the document (the actual amount of data to be processed), but rather on the size of the accumulation register.

    Aside from the fact that this option affects the configuration performance, it may happen that the two approaches produce different results. For example, this might happen when you use the SliceLast virtual table of an information register. For more information, see Using filters in queries with virtual tables.
  23. Click the Query button and review the text generated by the query wizard (listing 14.25).

    Listing 14.25. Query text

    SELECT
        DocumentMaterialsAndServices.MaterialOrService,
        DocumentMaterialsAndServices.MaterialServiceType,
        DocumentMaterialsAndServices.QuantityIndocument,
        DocumentMaterialsAndServices.TotalInDocument,
        CostOfMaterialsBalance.CostBalance
    FROM
        DocumentMaterialsAndServices AS DocumentMaterialsAndServices
            LEFT JOIN AccumulationRegister.CostOfMaterials.Balance( , Material IN (
            SELECT
                DocumentMaterialsAndServices.MaterialOrService
            FROM
                DocumentMaterialsAndServices)) AS CostOfMaterialsBalance
    	ON DocumentMaterialsAndServices.MaterialOrService = CostOfMaterialsBalance.Material
    So you added the material cost to the selection fields (fig. 14.23).

    Lesson 14 (3:20). Optimization of posting the Services document / Automatic cost calculation / In Designer mode
    Fig. 14.23. Creating the second query
  24. Add the Balances virtual table of the BalanceOfMaterials register to the list of query tables.
  25. Select the QuantityBalance field from this table.

    Let us define the relation between the tables.
  26. Click the Links tab and specify that all the records are selected from the temporary table and the MaterialOrService field of the temporary table matches the Material field of the balances table (fig. 14.24).

    Lesson 14 (3:20). Optimization of posting the Services document / Automatic cost calculation / In Designer mode
    Fig. 14.24. Relation between the tables

    Let us specify the parameters for the BalanceOfMaterialsBalance virtual table.
  27. In the Condition field, enter the line shown in listing 14.26.

    Listing 14.26. Virtual table condition

    Material IN (SELECT DocumentMaterialsAndServices.MaterialOrService FROM DocumentMaterialsAndServices)
    This gives you the following query text (listing 14.27).

    Listing 14.27. Query text

    SELECT
        DocumentMaterialsAndServices.MaterialOrService,
        DocumentMaterialsAndServices.MaterialServiceType,
        DocumentMaterialsAndServices.QuantityIndocument,
        DocumentMaterialsAndServices.TotalInDocument,
        CostOfMaterialsBalance.CostBalance,
        BalanceOfMaterialsBalance.QuantityBalance
    FROM
        DocumentMaterialsAndServices AS DocumentMaterialsAndServices
            LEFT JOIN AccumulationRegister.CostOfMaterials.Balance( , Material IN (
                SELECT
                 DocumentMaterialsAndServices.MaterialOrService
                FROM
        		DocumentMaterialsAndServices)) AS CostOfMaterialsBalance
    	ON DocumentMaterialsAndServices.MaterialOrService = CostOfMaterialsBalance.Material
    	LEFT JOIN AccumulationRegister.BalanceOfMaterials.Balance( , Material IN (
                SELECT
                    DocumentMaterialsAndServices.MaterialOrService
                FROM
        		DocumentMaterialsAndServices)) AS BalanceOfMaterialsBalance
    	ON DocumentMaterialsAndServices.MaterialOrService = BalanceOfMaterialsBalance.Material
    So you added the material balance in all the warehouses to the selection fields (fig. 14.25).

    Lesson 14 (3:20). Optimization of posting the Services document / Automatic cost calculation / In Designer mode
    Fig. 14.25. Creating the second query
  28. Click the Unions/Aliases tab, set the CostBalance alias to Cost, and set the QuantityBalance alias to Quantity (fig. 14.26).

    Lesson 14 (3:20). Optimization of posting the Services document / Automatic cost calculation / In Designer mode
    Fig. 14.26. Field aliases

    The query text generated by the wizard is shown in listing 14.28.

    Listing 14.28. Query text

    SELECT
        DocumentMaterialsAndServices.MaterialOrService,
        DocumentMaterialsAndServices.MaterialServiceType,
        DocumentMaterialsAndServices.QuantityInDocument,
        DocumentMaterialsAndServices.TotalInDocument,
        CostOfMaterialsBalance.CostBalance AS Cost,
        BalanceOfMaterialsBalance.QuantityBalance AS Quantity
    FROM
        DocumentMaterialsAndServices AS DocumentMaterialsAndServices
            LEFT JOIN AccumulationRegister.CostOfMaterials.Balance( , Material IN (
                SELECT
                    DocumentMaterialsAndServices.MaterialOrService
                FROM
                    DocumentMaterialsAndServices)) AS CostOfMaterialsBalance
            ON DocumentMaterialsAndServices.MaterialOrService = CostOfMaterialsBalance.Material
            LEFT JOIN AccumulationRegister.BalanceOfMaterials.Balance( , Material IN (
                SELECT
                    DocumentMaterialsAndServices.MaterialOrService
                FROM
                    DocumentMaterialsAndServices)) AS BalanceOfMaterialsBalance
            ON DocumentMaterialsAndServices.MaterialOrService = BalanceOfMaterialsBalance.Material
    The final thing to do with the query is specifying how to handle the situation where a material is available in the catalog but without any balance or cost data. For example, this can happen when a material has already been added to the catalog but has not been delivered to the company yet.

    In this situation left joins with virtual tables return nothing. In the query language it means that the cost and quantity fields store NULL values.

    For future convenience, let us eliminate such values right in the query.

    Let us apply the ISNULL() function to the Cost and Quantity fields. If a field value is NULL, the function returns 0. Otherwise it returns the field value.
  29. Click the Tables and fields tab, click the CostOfMaterialsBalance.CostBalance field, and click the Change current item Lesson 14 (3:20). Optimization of posting the Services document / Automatic cost calculation / In Designer mode button (fig. 14.27).

    Lesson 14 (3:20). Optimization of posting the Services document / Automatic cost calculation / In Designer mode
    Fig. 14.27. Changing a field value in a query
  30. In the window that is opened, edit the field value as shown in listing 14.29 (fig. 14.28).

    Listing 14.29. Expression for calculating a field in a query

    ISNULL(CostOfMaterialsBalance.CostBalance, 0)
    Lesson 14 (3:20). Optimization of posting the Services document / Automatic cost calculation / In Designer mode
    Fig. 14.28. Changing a field value in a query
  31. Set a similar expression for calculating the BalanceOfMaterialsBalance.QuantityBalance field and click OK.

    The query text is added to the module (listing 14.30).

    Listing 14.30. Query text

    Query2.Text = "SELECT
    |    DocumentMaterialsAndServices.MaterialOrService,
    |    DocumentMaterialsAndServices.MaterialServiceType,
    |    DocumentMaterialsAndServices.QuantityInDocument,
    |    DocumentMaterialsAndServices.TotalInDocument,
    |    ISNULL(CostOfMaterialsBalance.CostBalance, 0) AS Cost,
    |    ISNULL(BalanceOfMaterialsBalance.QuantityBalance, 0) AS Quantity
    |FROM
    |    DocumentMaterialsAndServices AS DocumentMaterialsAndServices
    |        LEFT JOIN AccumulationRegister.CostOfMaterials.Balance( , Material IN (
    |            SELECT
    |                DocumentMaterialsAndServices.MaterialOrService
    |            FROM
    |                DocumentMaterialsAndServices)) AS CostOfMaterialsBalance
    |        ON DocumentMaterialsAndServices.MaterialOrService = CostOfMaterialsBalance.Material
    |        LEFT JOIN AccumulationRegister.BalanceOfMaterials.Balance( , Material IN (
    |            SELECT
    |                DocumentMaterialsAndServices.MaterialOrService
    |            FROM
    |                DocumentMaterialsAndServices)) AS BalanceOfMaterialsBalance
    |        ON DocumentMaterialsAndServices.MaterialOrService = BalanceOfMaterialsBalance.Material";
    The only thing you need to do is add the query execution statement after the query text (listing 14.31).

    Listing 14.31. Posting() procedure (fragment)

    ...
    Query2 = New Query;
    Query2.TempTablesManager = TTManager;
    Query2.Text = "SELECT
        |    DocumentMaterialsAndServices.MaterialOrService,
    ...
        |        ON DocumentMaterialsAndServices.MaterialOrService = BalanceOfMaterialsBalance.Material";
          
    QueryResult = Query2.Execute();
          
    SelectionDetailRecords = QueryResult.Select();
    ... 
    Now let us deal with writing register records.

    You can use the script lines that you wrote earlier. The only thing you need to change is how cost is obtained. You used to take it directly from the document but now you need to calculate it based on the value retrieved in the query.

    The material cost is calculated by dividing the total cost retrieved in the query (Cost) by the total quantity of material units available in all warehouses (Quantity).

    But we have already mentioned that the Quantity field might contain zero and one cannot divide by zero. So right after the beginning of the loop that iterates through the query result add the script that calculates the cost for the current material (listing 14.32).

    Listing 14.32. Posting() procedure (fragment)

    While SelectionDetailRecords.Next() Do
      
        If SelectionDetailRecords.Quantity = 0 Then
            MaterialCost = 0;
        Else
            MaterialCost = SelectionDetailRecords.Cost / SelectionDetailRecords.Quantity;
        EndIf;
        
        If SelectionDetailRecords.MaterialServiceType = Enums.MaterialServiceTypes.Material Then
    
    ...
  32. Replace the cost calculation expressions in the script fragment that writes CostOfMaterials and Sales register records (listing 14.33).

    Listing 14.33. Posting() procedure (fragment)

    While SelectionDetailRecords.Next() Do
      
        If SelectionDetailRecords.Quantity = 0 Then
            MaterialCost = 0;
        Else
            MaterialCost = SelectionDetailRecords.Cost / SelectionDetailRecords.Quantity;
        EndIf;
        
        If SelectionDetailRecords.MaterialServiceType = Enums.MaterialServiceTypes.Material Then
        
            // register BalanceOfMaterials Expense
            Record = RegisterRecords.BalanceOfMaterials.Add();
            Record.RecordType = AccumulationRecordType.Expense;
            Record.Period = Date;
            Record.Material = SelectionDetailRecords.MaterialOrService;
            Record.Warehouse = Warehouse;
            Record.Quantity = SelectionDetailRecords.QuantityInDocument;
       
            // register CostOfMaterials Expense
            Record = RegisterRecords.CostOfMaterials.Add();
            Record.RecordType = AccumulationRecordType.Expense;
            Record.Period = Date;
            Record.Material = SelectionDetailRecords.MaterialOrService;
            Record.Cost = SelectionDetailRecords.QuantityInDocument * MaterialCost;
       
        EndIf;
        
        // register Sales
        Record = RegisterRecords.Sales.Add();
        Record.Period = Date;
        Record.MaterialOrService = SelectionDetailRecords.MaterialOrService;
        Record.Customer = Customer;
        Record.Technician = Technician;
        Record.Quantity = SelectionDetailRecords.QuantityInDocument;
        Record.Revenue = SelectionDetailRecords.TotalInDocument;
        Record.Cost = SelectionDetailRecords.QuantityInDocument * MaterialCost;
        
    EndDo;
    Now everything seems to be fine but there is still one important issue.

    The first posting of a document gives the correct result but the second posting makes it incorrect because at the time of the second posting the database contains the register records written during the first posting.

    So when the cost and balance of materials are read from the database, the retrieved values are based on the register records written by this document earlier. And this is absolutely wrong.

    In order to read database data in the posting event handler without reading the register records written by this document earlier, you have to write empty record sets to the registers before reading data from them.

    In this example you have to write empty record sets to the CostOfMaterials and BalanceOfMaterials accumulation registers.
  33. Add two lines before the execution of the second query, as shown in listing 14.33a.

    Listing 14.33a. Posting() procedure (fragment)

    ...
    |        ON DocumentMaterialsAndServices.MaterialOrService = BalanceOfMaterialsBalance.Material";
          
    // Writing empty record sets to read balances without the data added by this document
    RegisterRecords.CostOfMaterials.Write();
    RegisterRecords.BalanceOfMaterials.Write();      
          
    QueryResult = Query2.Execute();

In 1C:Enterprise mode

Run 1C:Enterprise in the debug mode, repost the Services documents, and ensure that the document data is recorded to the registers correctly.

Leave a Reply

Your email address will not be published. Required fields are marked *

1C:Enterprise Developer's Community