Lesson 18. Using a Calculation Register

Estimated duration of the lesson is 3 hours 40 minutes.

CONTENT:

Adding a Document on Accruals.
Demonstrating How Displacement and Dependence on Base Work.

Creating a Report on Recalculations.
Mechanism of Dependence on Base Period.
Mechanism of Displacement by Action Period.

Creating a Procedure to Calculate a Calculation Register Records.
Creating a Report on Accruals to Employees.

Query for a Data Set.
Resources.
Settings.

Recalculating Calculation Register Records.
Gantt Chart.
Quiz.

Now we have everything we need to start developing the system for calculating payroll at Jack of All Trades.

In this chapter we will create a document that will be used to perform various accruals, we will see how and when recalculation records are created by the platform, and will also learn how displacement by action period and dependence on base period work.

We will also create a report that displays accruals to employees at Jack of All Trades, and will set it up so that calculation data can be kept current.

Finally, we will learn a new form control named the Gantt Chart. Using this chart, we will illustrate operation of some calculation mechanisms.

Adding a Document on Accruals

In order to be able to register data on accruals posted for Jack of All Trades employees, we will need a special document.

In the Designer Mode

First open the Designer and add a new Document configuration object.

Enter EmployeeAccruals for the name.

Enter Employee Accruals for object presentation.

On the Numbering tab specify:

- Number Type - Number,

- Number Length - 5 (fig. 18.1).

On the Subsystems tab specify that this document will be displayed in the Payroll subsystem.

On the Data tab specify that the document will have a tabular section named Accruals with the following attributes:

• Employee, type CatalogRef.Employees;
• WorkSchedule, type CatalogRef.WorkScheduleTypes;
• StartDate, type Date;
• EndDate, type Date;
• CalculationType, type ChartOfCalculationTypes.MainAccruals;
• Accrual, type Number, a length of 15, 2 decimal places.

We need the StartDate and EndDate attributes in order to specify the period where a specific calculation record will be effective.

On the RegisterRecords tab cancel real-time posting for the document.

Check that the document will create records in the Accruals calculation register and launch a register record wizard (fig. 18.2).

Fig. 18.2. Register records of a document

Within the wizard, select the Accruals tabular section and click Fill Expressions.

For the EndOfActionPeriod and EndOfBasePeriod attributes enter the expression EndOfDay(CurRowAccruals.EndDate).

For the RegistrationPeriod specify Date as an expression.

For the SourceData attribute select Accrual tabular section attribute as a match and for the Result resource leave a blank expression because it will be calculated later (fig. 18.3).

Fig. 18.3. Register records of the EmployeeAccruals document in the calculation register

Click OK and review the handler code generated by the wizard (listing 18.1).

Listing 18.1. Handler code generated by register records wizard

Procedure Posting(Cancel, Mode)

	//{{__REGISTER_REGISTERRECORDS_WIZARD
	// This fragment was built by the wizard.
	// Warning! All manually made changes will be lost next time you use the wizard.
	
	RegisterRecords.Accruals.Write = True;
	
	For Each CurRowAccruals In Accruals Do
		// register Accruals
		Record = RegisterRecords.Accruals.Add();
		Record.ReversingEntry = False;
		Record.CalculationType = CurRowAccruals.CalculationType;
		Record.BegOfActionPeriod = CurRowAccruals.StartDate;
		Record.EndOfActionPeriod = EndOfDay(CurRowAccruals.EndDate);
		Record.RegistrationPeriod = Date;
		Record.BegOfBasePeriod = CurRowAccruals.StartDate;
		Record.EndOfBasePeriod = EndOfDay(CurRowAccruals.EndDate);
		Record.Employee = CurRowAccruals.Employee;
		Record.WorkSchedule = CurRowAccruals.WorkSchedule;
		Record.SourceData = CurRowAccruals.Result;
	EndDo;
	
	//}}__REGISTER_REGISTERRECORDS_WIZARD
	
EndProcedure

Now we will discuss this code.

In general, this code does not have any major differences when compared to the previous document posting event handlers that create register records.

The only thing we will remind you here is that Accruals is the name of the calculation register so the expressions RegisterRecords.Accruals.Write() and RegisterRecords.Accruals.Add() are intended to call for the methods of a record set of this register.

The line For Each CurRowAccruals In Accruals Do calls by the name

(Accruals) for the tabular section of our document and arranges for a search loop within this tabular section.

In the loop we add a new record to the register records and assign values from the document tabular section to its fields.

In order to assign values to the fields EndOfActionPeriod, EndOfBasePeriod, we use the function EndOfDay(). We did the same when we created reports for the last day of a period to be included in a report.

Finally, we will edit the command interface so that the Payroll subsystem has the command available to create new documents.

To do so, highlight the Subsystems branch in the configuration object tree, open its context menu and select All subsystems.

In the window that opens highlight Payroll in the Subsystems list on the left.

In the Action panel.New enable visibility for the command Employee Accruals: New (fig. 18.4).

Fig. 18.4. Editing command interface

In the 1C:Enterprise Mode

Run 1C:Enterprise in the debugging mode and review operation of our document.

On the action panel of the Payroll section click the command Employee Accruals and record January salary for all the employees of the Jack of All Trades (fig. 18.5).

Fig. 18.5. Document Employee Accruals No. 1

Post the document and look at the records it generated in the Accruals register (fig. 18.6, 18.7).

Fig. 18.6. Records of the document Employee Accruals No. 1 in the Accruals calculation register

Fig. 18.7. Records of the document Employee Accruals No. 1 in the Accruals calculation register

Note that the platform set the registration period for every record to the beginning of the calculation register period (in the posting event handler we specified that the date of the document was 2/8/2010).

Also note that, for each record, we used the SourceData attribute to store the amount of each employee's salary as entered in the document. This lets us subsequently calculate the total salary.

To understand more about how a calculation register works, we will need a utility report, which will enable us to review the contents of recalculation records.

Demonstrating How Displacement and Dependence on Base Work

Creating a Report on Recalculations

In the Designer Mode

Create a new Report configuration object.

Enter Recalculation for its name.

Create the main data composition schema, add a query as a data source and open the query wizard.

In the Database list expand the branch Recalculations and select all the fields from the virtual table of the recalculation named Accruals.Recalculation:

- RecalculationObject,

- CalculationType,

Fig. 18.8. Fields and tables of a query

Now we are done with the query, click OK.

Navigate to the Settings tab and add a grouping of detailed records.

On the Selected Fields tab select the following fields to be included in the report: RecalculationObject, CalculationType, and Employee.

This completes creation of a data composition schema so you can close it.

In the editor of the Recalculation Report configuration object on the Subsystems tab specify that the report will be owned by the Payroll subsystem.

Mechanism of Dependence on Base Period

In the 1C:Enterprise Mode

If you now execute the report in the 1C:Enterprise mode, you will see that no recalculations have been performed yet.

So create a new document named Employee Accruals No. 2 where we accrue July bonuses for Alexander D. Johnson and John A. Walkman (fig. 18.9).

Fig. 18.9. Document Employee Accruals No. 2

This document enables us to record the fact that bonuses are due to employees named Alexander D. Johnson and John A. Walkman based on their work in July. Since we do not know the amount of the bonus (it will be calculated based on a certain algorithm), leave the Accrual field blank.

Click Post and Close.

Now reopen the document Employee Accruals No. 1 and change Alexander D. Johnson's salary from 10,000 to 7,000.

Click Post and Close.

Generate the Recalculation report (fig. 18.10).

Fig. 18.10. Recalculation Report

As you see, it now has some data.

What happened is that the Bonus calculation type depends by base period on the Salary calculation type.

As soon as we modified the existing Salary type records, the platform immediately generated a set of recalculation records, which need to be calculated again since their base has changed.

You may be wondering why the records like the one on John A. Walkman were included in the recalculation along with Alexander D. Johnson's, since we only changed Alexander D. Johnson's salary.

The reason is that the platform does not track the specific changes users make to document records. The only thing it tracks is the fact that a change was made to a set of records from the calculation register, as a result of posting (reposting) a document.

Therefore the recalculation record set includes information about ALL the registers records, the values of which COULD change as a result of reposting of a document that creates base records in the register.

Repost the document Employee Accruals No. 2 (the one we accrued bonuses with), and generate the Recalculation report.

It is blank again, as the system has noted the fact that we "recalculated" the dependent records and cleared the recalculation table.

In this example, we have learned operation of the mechanism for maintaining dependence by base period in a calculation register.

Mechanism of Displacement by Action Period

In the 1C:Enterprise Mode

Now look at how displacement by action period works.

To do so, we will need to create a document named Employee Accruals No. 3 (fig. 18.11).

This document enables us to record the fact that Alexander D. Johnson was not at work from the 13^th through the 22^nd of January.

Obviously, in this case, we are going to need to recalculate his salary and, as a result, the amount of his bonus.

Fig. 18.11. Document Employee Accruals No. 3

Click Post and Close and generate the report named Recalculation (fig. 18.12).

As you can see, a record concerning the accrual of salary for Alexander D. Johnson ended up in the recalculation. This is the result of displacement by period, since we have the Absence calculation type displace the Salary calculation type.

Note a record concerning recalculation of Alexander D. Johnson's bonus has also been included in the recalculation.

You may recall that, when creating the predetermined calculation types we said that the result of the Bonus calculation type would depend on changes of the result of Absence calculations. This dependency is indirect, but since we have explicitly specified such dependency, the platform tracked it.

Now repost the documents Employee Accruals Nos. 1 and 2 to make sure the recalculation table is cleared.

Creating a Procedure to Calculate a Calculation Register Records

In the Designer Mode

So far, we have simply made entries in the Accruals calculation register regarding the fact that certain types of calculations need to be performed. However, we said nothing about exactly how we would make that happen.

Now it is time to describe the algorithms for generating various types of calculations.

Since we will need these algorithms elsewhere (in addition to the Employee Accruals document), it would be most helpful to host them in some separate common module.

Open the code of the posting event handler for the document named

EmployeeAccruals in the Designer and add a call for the procedure CalculateAccruals() from the common module PerformCalculations after records in the Accruals register are completed (listing 18.2).

Listing 18.2. Event handler of the EmployeeAccruals document posting

Procedure Posting(Cancel, Mode)
	...
	EndDo;
	//}}__REGISTER_REGISTERRECORDS_WIZARD
	
	// Writing register records
	RegisterRecords.Accruals.Write();
	// Get a list of all employees referred to in the document
	Query = New Query(
	"SELECT DISTINCT
	|  EmployeeAccrualsAccruals.Employee
	|FROM
	|  Document.EmployeeAccruals.Accruals AS EmployeeAccrualsAccruals
	|WHERE
	|  EmployeeAccrualsAccruals.Ref = &CurDocument");

	Query.SetParameter("CurDocument", Ref);

	// Generate an employee list
	ValTable = Query.Execute().Unload();
	EmployeeArray = ValTable.UnloadColumn("Employee");

	// Call the CalculateAccruals procedure from the PerformCalculation common module
	PerformCalculations.CalculateAccruals (RegisterRecords.Accruals, ChartOfCalculationTypes.MainAccruals.Salary, EmployeeArray);
	RegisterRecords.Accruals.Write(,True);

	PerformCalculations.CalculateAccruals(RegisterRecords.Accruals, ChartOfCalculationTypes.MainAccruals.Bonus, EmployeeArray);
	RegisterRecords.Accruals.Write(,True);

EndProcedure

Note, that when we post the document, we first write the records generated by the document to the register (RegisterRecords.Accruals.Write()) and then transfer this set of register records to the calculation procedure named CalculateAccruals() that we will create in the common module named PerformCalculations.

This procedure is first called for to calculate primary records (Salary) and then for secondary ones (Bonus).

The calculation procedure uses its own algorithm descriptions and data from the register records to generate resource values for the register.

Once the resources are calculated, we rewrite the set of register records without generating recalculation records (the second parameter in the Write() method is True).

Before calling the procedure from the common module, we use a query to generate a list of employees included in the document to pass it to the called procedure.

For the query parameter named CurDocument we define the value of the document default attribute which is Ref. Using the query method Query.Execute().Unload(), we dump the query results to a table of values (the variable ValTable). Next we generate an array named EmployeeArra that contains the Employee from this table of values.

Fig. 18.13. Common module properties

Now within the Common branch create a new common module named

PerformCalculations.

Note that it will be a global module by checking the appropriate field. Also check Server Call to make its export procedures and functions visible (fig. 18.13).

Then add in a blank for a procedure named CalculateAccruals (listing 18.3):

Listing 18.3. Blank of the CalculateAccruals procedure

Procedure CalculateAccruals (RegisterRecordSet, RequiredCalculationType, EmployeeList) Export

	Recorder = RegisterRecordSet.Filter.Recorder.Value;
	
	// Calculate primary records
	If RequiredCalculationType = ChartOfCalculationTypes.MainAccruals.Salary Then
	// Calculate secondary records
	
	ElsIf RequiredCalculationType = ChartOfCalculationTypes.MainAccruals.Bonus Then
	
	EndIf;
	
EndProcedure

The calculate accruals algorithm will vary for primary (Salary calculation type) and secondary (Bonus calculation type) records, and each of its parts will be located on its own branch of the If... clause.

When calculating primary records, we will need data from the schedule of the calculation register, and will therefore add a query to the first branch to pull from the calculation register's virtual table, CalculationRegister. Accruals.ScheduleData (listing 18.4).

Listing 18.4. Modifying the CalculateAccruals procedure

Procedure CalculateAccruals (RegisterRecordSet, RequiredCalculationType, EmployeeList) Export

	Recorder = RegisterRecordSet.Filter.Recorder.Value;
	
	// Calculate primary records
	If RequiredCalculationType = ChartOfCalculationTypes.MainAccruals.Salary Then
		Query = New Query;
		Query.Text =
		"SELECT
		| AccrualsScheduleData.ValueActionPeriod AS Norm,
		| AccrualsScheduleData.ValueActualActionPeriod AS Actual,
		| AccrualsScheduleData.LineNumber
		|FROM
		| CalculationRegister.Accruals.ScheduleData(Recorder = &Recorder AND 
		| CalculationType = &CalculationType AND Employee IN (&EmployeeList))  AS AccrualsScheduleData";

		Query.SetParameter("Recorder", Recorder);
		Query.SetParameter("CalculationType", RequiredCalculationType);
		Query.SetParameter("EmployeeList", EmployeeList);

		SelectionResult = Query.Execute().Choose();
		// Calculate secondary records
	ElsIf RequiredCalculationType = ChartOfCalculationTypes.MainAccruals.Bonus Then
	
	EndIf;

EndProcedure

In this query we select schedule values for the action period and actual action period from the calculation's register's schedule data virtual table. When analyzing the virtual table's parameters, we limit our selection of schedule values by recorder, type of calculation and employee list.

Now we will add a search within the record set that was transferred to the procedure and a calculation of records for which schedule values were received (listing 18.5).

Listing 18.5. Adding a search in the record set and calculation of primary records

Procedure CalculateAccruals (RegisterRecordSet, RequiredCalculationType, EmployeeList) Export

	Recorder = RegisterRecordSet.Filter.Recorder.Value;
	
	// Calculate primary records
	If RequiredCalculationType = ChartOfCalculationTypes.MainAccruals.Salary Then
		...
		SelectionResult = Query.Execute().Choose();
		For Each RegisterRecord In RegisterRecordSet Do
			StructureNumber = New Structure("LineNumber");
			StructureNumber.LineNumber = RegisterRecord.LineNumber;
			SelectionResult.Reset();
			If SelectionResult.FindNext(StructureNumber) Then
				If SelectionResult.Norm = 0 Then
					Message = New UserMessage;
					Message.Text = "Calculation type: Salary - No working days in the specified period",);
					RegisterRecord.Result = 0;
				Else
					// Calculate salary based on actual period and source data
					RegisterRecord.Result = (RegisterRecord.SourceData / SelectionResult.Norm) * SelectionResult.Actual;
					Message("Calculation completed: " + RegisterRecord.Recorder
						+ " - " + RegisterRecord.CalculationType + " - " + RegisterRecord.Employee,);
				EndIf;
			EndIf;
		EndDo;
		
		// Calculate secondary records
	ElsIf RequiredCalculationType = ChartOfCalculationTypes.MainAccruals.Bonus Then
	EndIf;
EndProcedure

For each record of the calculation register record set, we get a row number that identifies an accrual for a specific employee, and will then use this number to find the corresponding record in the query result selection.

If there is a record in the query results with that row number, we will calculate the result of the calculation register record. So we get a salary accrual for every employee by dividing the accrued amount (SourceData register field) by the number of working days in the month (Norm) and multiplying the result to actual working days (Actual).

Add the query code into the second branch of the If... clause, with the only difference being that now we will get the base value using the virtual table of the calculation register CalculationRegister.Accruals.AccrualBase (listing 18.6).

Listing 18.6. Adding query code to the second clause branch

Procedure CalculateAccruals (RegisterRecordSet, RequiredCalculationType, EmployeeList) Export

	Recorder = RegisterRecordSet.Filter.Recorder.Value;
	
	// Calculate primary records
	If RequiredCalculationType = ChartOfCalculationTypes.MainAccruals.Salary Then
		...
		// Calculate secondary records
	ElsIf RequiredCalculationType = ChartOfCalculationTypes.MainAccruals.Bonus Then
		Query = New Query;
		Query.Text = "SELECT
			|  AccrualsBaseAccruals.ResultBase AS Base,
			|  AccrualsBaseAccruals.LineNumber AS LineNumber
			|FROM
			|  CalculationRegister.Accruals.BaseAccruals(
			|          &MainDimension,
			|          &BaseDimension,
			|             ,
			|         Recorder = &Recorder
			|               AND CalculationType = &CalculationType
			|               AND Employee IN (&EmployeeList)) AS AccrualsBaseAccruals";
		Dim = New Array(1);
		Dim[0] = "Employee";
		Query.SetParameter("MainDimension", Dim);
		Query.SetParameter("BaseDimension", Dim);
		Query.SetParameter("Recorder", Recorder);
		Query.SetParameter("CalculationType", RequiredCalculationType);
		Query.SetParameter("EmployeeList", EmployeeList);
		
		SelectionResult = Query.Execute().Choose();
		
	EndIf;
EndProcedure

As parameters of the virtual query table, in addition to our usual parameters (recorder, calculation type, and employee list) we will also define the dimensions of the main and base registers. In our case this will be a single register Accruals, and what we need is the Employee dimension.

To finish the second If... clause, we still need to add a search for the set of calculation register records and calculation of the secondary records (listing 18.7).

Listing 18.7. Adding a search in register record set and calculation of secondary records

Procedure CalculateAccruals (RegisterRecordSet, RequiredCalculationType, EmployeeList) Export

	Recorder = RegisterRecordSet.Filter.Recorder.Value;
	
	// Calculate primary records
	If RequiredCalculationType = ChartOfCalculationTypes.MainAccruals.Salary Then
	...
	// Calculate secondary records
	ElsIf RequiredCalculationType = ChartOfCalculationTypes.MainAccruals.Bonus Then
		...
		SelectionResult = Query.Execute().Choose();
		For Each RegisterRecord In RegisterRecordSet Do
			StructureNumber = New Structure("LineNumber");
			StructureNumber.LineNumber = RegisterRecord.LineNumber;
			SelectionResult.Reset();
			If SelectionResult.FindNext(StructureNumber) Then
				RegisterRecord.Result = SelectionResult.Base * (10 / 100);
				Message = New UserMessage;
				Message.Text = "Calculation completed: " + RegisterRecord.Recorder
					+ " - " + RegisterRecord.CalculationType
					+ " - " + RegisterRecord.Employee;
				Message.Message();
			EndIf;
		EndDo;
	EndIf;
EndProcedure

We will calculate the total accrued bonus as 10 % of the calculated salary.

In the 1C:Enterprise Mode

Launch 1C:Enterprise in the debugging mode and test whether the calculation procedure works correctly.

Unpost the document Employee Accruals No. 3 (All Actions4Clear Posting) and repost the documents Employee Accruals No. 1 and No. 2. The Accruals calculation register should look as follows (fig. 18.14, 18.15).

Fig. 18.14. Records of the Accruals register

Fig. 18.15. Records of the Accruals register

You see that salary has been accrued to all the employees (the Result field) for the entire month in compliance with the source data (the Source Data field).

The employees named Alexander D. Johnson and John A. Walkman accrued a bonus in the amount of 10 % of their accrued salary.

Post the document Employee Accruals No. 3, and then post the documents

Fig. 18.16. Records of the Accruals register

The report named Recalculation should be empty.

The state of the register will change as follows (fig. 18.16, 18.17).

As a result of Alexander D. Johnson's absence, the amount of his salary will be reduced, along with a corresponding reduction in the accrual of his bonus.

Creating a Report on Accruals to Employees

Now we will discuss how we can use the data that is stored in the calculation register to get an aggregate report on employee accruals (fig. 18.18).

Fig. 18.18. Report results

In the Designer Mode

Create in the Designer a new Report configuration object.

Enter EmployeeAccruals for the name.

Create the main data composition schema of the report, add a new Data Set-Query and open the query wizard.

Query for a Data Set

Highlight the table of the Accruals calculation register (fig. 18.19).

Select the following fields from this table:

- Employee,

- CalculationType,

- BegOfActionPeriod,

- EndOfActionPeriod,

- Recorder,

- Result (fig. 18.20).

Fig. 18.19. Assortment of fields

Fig. 18.20. Selected fields in the Accruals table

On the Unions/Aliases tab specify the following aliases for the fields BegOfActionPeriod and EndOfActionPeriod (fig. 18.21).

Now we are done creating the query, click OK.

Resources

Navigate to the Resources tab and specify that the total should be calculated for the Result field.

Settings

Next navigate to the Settings tab and create a report structure.

Add a grouping by the Employee field and also a subordinate grouping of detailed records.

Select the following fields to be included in the report: CalculationType, Begin, End, Recorder, and Result.

This will result in the following arrangement of the report settings window (fig. 18.22).

Fig. 18.22. Report structure and selected fields

On the Sorting tab specify that it should be sorted in ascending order of the values in the Employee and Recorder fields (fig. 18.23).

Finally, on the Other Settings tab enter a title for the report as Employee Accruals.

This completes creation of a data composition schema so you can close it.

In the editor of the EmployeeAccruals Report configuration object on the subsystems tab specify that the report will be opened from the Payroll and Accounting subsystems.

In the 1C:Enterprise Mode

Run 1C:Enterprise in the debugging mode.

On the command bar of the Payroll section click the command Employee Accruals and generate the report (fig. 18.24).

Fig. 18.24. Report results

Recalculating Calculation Register Records

So now we only have one "problem" area left in our algorithm - monitoring currency of the data in our calculation register.

Up to this point, we have used the Recalculation service report to determine whether or not the data in the calculation register is current (if the report is empty) or in need of recalculation.

Now we will create a special procedure to determine whether calculation register data needs recalculation and to perform the recalculation if required.

Since the only means to get totals on employee accruals in our configuration is the EmployeeAccruals report, in order to call for the procedure we will create the default form of the report and add a button Recalculate to the command bar of the form that will be used to recalculate register data.

In the Designer Mode

To do so, in the configuration object editor of the EmployeeAccruals Report navigate to the Forms tab, click the opening button and create the default report form.

Next in the upper right window of the form editor navigate to the Commands tab and on the Form Commands tab create a form command named

Recalculate (fig. 18.25).

Fig. 18.25. Adding a form command

Now you need to specify the Action for this command.

To do, click the opening button in the Action row.

The form module will have a template for the procedure Recalculate() created. In this procedure we will call for the procedure RecalculateAccruals() from the common module PerformCalculations (listing 18.8).

Listing 18.8. Code of the Recalculate command handler

&AtClient
Procedure Recalculate(Command)
	PerformCalculations.RecalculateAccruals(PredefinedValue("ChartOfCalculationTypes.MainAccruals.Salary"));
	PerformCalculations.RecalculateAccruals(PredefinedValue("ChartOfCalculationTypes.MainAccruals.Bonus"));
EndProcedure

The recalculation procedure should be hosted in the common module named PerformCalculations (listing 18.9).

Listing 18.9. Procedure to recalculate accruals

Procedure RecalculateAccruals(RequiredCalculationType) Export
	// Here the procedure selects a set of records for recalculation
	// in the following order:
	// records for the listed employees from document1
	// records for the listed employees from document2 and so on.
	Query = New Query(
	"SELECT
		|  AccrualsRecalculation.RecalculationObject AS RecalculationObject,
		|  AccrualsRecalculation.Employee
		|FROM
		|  CalculationRegister.Accruals.Recalculation AS AccrualsRecalculation
		|WHERE
		|  AccrualsRecalculation.CalculationType = &RequiredCalculationType
		|TOTALS BY
		|  RecalculationObject");
		
	Query.SetParameter("RequiredCalculationType", RequiredCalculationType);
	
	EmployeeList = New ValueList;
	
	// Step through group by recorder
	SelectionByRecorder = Query.Execute().Choose(QueryResultIteration.ByGroups);
	
	While SelectionByRecorder.Next() Do
		Recorder = SelectionByRecorder.RecalculationObject;
		// Step through group by recorder
		// for a selected recorder and create an employee list
		SelectionByEmployee = SelectionByRecorder.Choose();
		EmployeeList.Clear();
		While SelectionByEmployee.Next() Do
			EmployeeList.Add(SelectionByEmployee.Employee);
		EndDo;
		
		// Get the calculation register record set
		// for a selected recorder
		RecordSet = CalculationRegisters.Accruals.CreateRecordSet();
		RecordSet.Filter.Recorder.Value = Recorder;
		RecordSet.Read();
		CalculateAccruals(RecordSet, RequiredCalculationType, EmployeeList);
		RecordSet.Write( ,True);
		
		// Remove the recalculated records from recalculation
		RecalculationRecordSet = CalculationRegisters.Accruals.Recalculations.Recalculation.CreateRecordSet();
		RecalculationRecordSet.Filter.RecalculationObject.Value = Recorder;
		RecalculationRecordSet.Write();
	EndDo;
EndProcedure

In the very beginning of the procedure we use a query to select recalculation records containing the type of calculation that was passed to the procedure and grouped by recalculation object.

Next, while iterating the query results, we generate a list of employees for each recalculation object, read the corresponding calculation register records and call the CalculateAccruals procedure, which we used when calculating records in the EmployeeAccruals document.

Once the calculation of records is completed, we write the record set without generating recalculation records, and clear the recalculation records for the recalculation object that has just been processed.

Now return to the form of the EmployeeAccruals report.

So for the Recalculate command we have specified an action, i.e. the procedure for command execution.

But in order to be able to use the command, you need to create a button in the form and connect it to the command (in the Command row).

The simplest way to do it is to drag the command from the Form Commands window to the form controls window.

Drag the Recalculate command to the group of form controls named MainCommandBar and locate it after the GenerateReport command.

After you do so, the Recalculate button will be added to the form and the button will be connected to the command automatically (fig. 18.26).

In the 1C:Enterprise Mode

Launch 1C:Enterprise and test how recalculation of calculation register records works.

Unpost all the Employee Accruals documents and post the document Employee Accrual No. 1 and then the document No. 2.

Generate the report Employee Accruals (fig. 18.27).

Fig. 18.27. Employee accruals report

Now open the document Employee Accruals No. 1, change Alexander D. Johnson's salary to 10,000 and post the document.

In the EmployeeAccruals report, click the Recalculate button.

Bonuses for Alexander D. Johnson and John A. Walkman will be recalculated (fig. 18.28).

To view current data in the report, click Generate.

The report results will contain the new values for Alexander D. Johnson's bonus (fig. 18.29).

And finally, post the document Employee Accruals No. 3 and click Recalculate in the Employee Accruals report.

Alexander D. Johnson's bonus will be recalculated again (fig. 18.30).

Click Generate. The report data will contain the current values for the accrual of salaries and bonuses (fig. 18.31).

Fig. 18.29. Employee Accruals report

Fig. 18.30. Service message window

Fig. 18.31. Employee Accruals report

Gantt Chart

In the end of this chapter we will mix business with pleasure and create a report that will provide a visual representation of the actual action period for calculation records.

In addition to a providing a visual demonstration of how displacement of records by action period works, this report will introduce a form control that allows us to create Gantt charts.

A Gantt chart is a chart of intervals on a time scale (fig. 18.32) that reflects use of resources (series) by objects (points).

To better understand components of a Gantt cart, we will examine the chart we would get as a result of the report we have created.

As we have already mentioned, this chart would display actual action periods of every record for each calculation type that is available for the employee.

A Gantt chart is a total of points, series, and values for each point-series combination.

In our case, the chart's points are employees and the series are calculation types. Thus, for every employee there is a certain chart value for each of the series, i.e. for each calculation type.

Gantt chart values are special objects generated automatically based on the points and series defined for this chart.

These objects represent a whole set (collection) of intervals, in other words, they can contain not just one, but multiple intervals, which exist for a point-series combination (by default, GanttChartValue objects are created containing no intervals). A developer can get a chart value by specifying the point and series required and then adding the required number of intervals to the collection.

All intervals for all chart values are arranged in relation to a single time axis, which enables users to see their relative positions.

Now we will briefly explain where we are going from here.

As source data for creating such a chart, we will take data from the Accruals calculation register. Each of this registers records already contains everything we need to create the chart: employee, calculation type, and the beginning and end of the interval.

All we need to do is use the 1C:Enterprise script tools to arrange all that in a chart.

So, let us proceed.

In the Designer Mode

Create a new Report configuration object and name it AccrualsChart.

For this report we will not create a data composition schema but will create a default report form and will ensure generation and setup of the Gantt chart using a code written using the 1C:Enterprise script.

To do so, in the configuration object editor of the AccrualsChart Report navigate to the Forms tab, click the opening button and create the default report form.

The upper right window of the form editor (the Attributes tab) has the attributes of the form.

You see the default form attribute named Report that has been created automatically upon form creation.

Click the Add button and add a new form attribute.

Enter GanttChart as a name and select GanttChart as its name (fig. 18.33).

Now drag the new attribute to a form controls window that is currently empty.

The form controls window will have a new field created to display the Gantt chart. The chart field will be displayed immediately in the lower form preview window (fig. 18.34).

Fig. 18.34. Adding a Gantt chart to a form

On the Commands tab create a form command named Generate (fig. 18.35).

Now you need to specify the Action for this command.

To do, click the opening button in the Action row.

The form module will have a template created for the Create() procedure. This procedure is intended to host the call for the procedure CreateAtServer(). A reference to the GanttChart form attribute should be transferred as a parameter to this procedure (listing 18.10).

Listing 18.10. Code of the Generate command handler

&AtClient
Procedure Create()
	CreateAtServer(GanttChart);
EndProcedure

The CreateAtServer() procedure should also be located in the form module and preceded by the execution directive &AtServerNoContext. Paste the query blank into this procedure (listing 18.11).

Listing 18.11. CreateAtServer() procedure

&AtServerNoContext
Procedure CreateAtServer(Chart)

	Query = New Query;
	Query.Text = "";
	
EndProcedure

Locate the cursor before the semicolon, open the context menu, run the query wizard and create a new query.

Highlight the virtual table of the calculation register Accruals.Ac- tualActionPeriod.

Select the following fields from this table (fig. 18.36):

- Employee;

- CalculationType;

- BegOfActionPeriod;

- EndOfActionPeriod;

- Result;

- Recorder;

- Recorder.Presentation.

The query is now ready.

Click OK and after the query code add the following code into the procedure (listing 18.12).

Listing 18.12. CreateAtServer() procedure

&AtServerNoContext
Procedure CreateAtServer(Chart)

	Query = New Query;
	Query.Text = "SELECT
		|  AccrualsActualActionPeriod.Employee,
		|  AccrualsActualActionPeriod.CalculationType,
		|  AccrualsActualActionPeriod.BegOfActionPeriod,
		|  AccrualsActualActionPeriod.EndOfActionPeriod,
		|  AccrualsActualActionPeriod.Result,
		|  AccrualsActualActionPeriod.Recorder,
		|  AccrualsActualActionPeriod.Recorder.Presentation
		|FROM
		|  CalculationRegister.Accruals.ActualActionPeriod AS AccrualsActualActionPeriod";
		
	SelectionResult = Query.Execute().Choose();
	
	// Prohibit chart updates
	Chart. ResfreshEnabled = False;
	Chart.Clear();
	Chart.ShowTitle = False;
	
	// Fill chart
	While SelectionResult.Next() Do
		// Get their series, point and value
		CurSeries = Chart.SetSeries(SelectionResult.CalculationType);
		CurPoint = Chart.SetPoint(SelectionResult.Employee);
		CurValue = Chart.GetValue (CurPoint, CurSeries);
		// Create the desired value intervals
		CurInterval = CurValue.Add();
		CurInterval.Begin = SelectionResult.BegOfActionPeriod;
		CurInterval.End = SelectionResult.EndOfActionPeriod;
		CurInterval.Text = SelectionResult.RecorderPresentation;
		CurInterval.Details = SelectionResult.Recorder;
	EndDo;
	
	// Set fill color for each series
	For Each Series in Chart.Series Do
		If Series.Value = ChartOfCalculationTypes.MainAccruals.Salary Then
			Series.Color = WebColors.Yellow;
		ElsIf Series.Value = ChartOfCalculationTypes.MainAccruals.Bonus Then
			Series.Color = WebColors.Green;
		ElsIf Series.Value = ChartOfCalculationTypes.MainAccruals.Absence Then
			Series.Color = WebColors.Red;
		EndIf;
	EndDo;
	
	// Allow chart updates
	Chart.RefreshEnabled = True;
	
EndProcedure

First we prohibit update of the chart for the time that it will take us to fill it with data. We need to do this so that we do not end up with a recalculation for every change to the chart's data. Once we're done filling the chart, we will enable updates, and all of the recalculations will be done at once.

Next, in the query selection search loop, we fill in the chart.

First, using the SetSeries() and SetPoint() methods, we get either current or new points and series. Points and series are uniquely identified by their values (employee and calculation type obtained by the query).

After that, as points and series are obtained, we use the GetValue() method to obtain respective chart values.

Then we add a new interval to the chart, give it a beginning and an end, provide the interval text, which will be shown as a tooltip, and then the interval details that will be activated when that interval is double-clicked.

Once all the chart values are generated, we assign each series its fill color. The chart series represent a collection of values, which we select using the For Each ... Do statement.

Now return to the form and add a button to the form to execute the Generate command.

To do so, drag the Generate command from the Form Commands window to the form controls window by pointing your mouse cursor to the Form row (fig. 18.37).

Finally, in the editor of the AccrualsChart Report configuration object on the subsystems tab specify that the report will be opened from the Payroll subsystem.

In the 1C:Enterprise Mode

Run 1C:Enterprise in the debugging mode and review how the report works (fig. 18.38).

Now we will look at how displacement by action period works in action.

Open the document Employee Accruals No. 3, and instead of one absence from the 13^th through the 22^nd, enter two absences for Alexander D. Johnson: from the 14^th through the 17^th and from the 25^th through the 27^th.

Repost the document and click the Generate button in our report (fig. 18.39).

Fig. 18.39. Accruals Chart Report

Now you can plainly see how the Absence calculation records displace the Salary calculation records by action period, modifying its actual action period.

Note that it is also possible to set Gantt chart parameters up interactively using the Setup... command from the context menu.

Quiz

• How does one create document records for a calculation register?
• How does one use a query to get recalculation records?
• How does recalculation work?
• How does one calculate records of a calculation register?
• How does one use a query to get schedule and base data?
• How does one perform a recalculation for specific calculation register records?
• How does one use a query to get calculation register records?
• How does one use a query to get the actual action period for calculation register records?
• What is the purpose of a Gantt chart?
• What is the arrangement of a Gantt chart?
• How does one fill in the data for a Gantt chart?