Build Your Own Reference Model#

ACCERT now accept user-defined model with user defined code of account structure.

Build your own account table#

Create a raw_account.csv file, that table should includes with the following columns: ind, code_of_account, account_description, total_cost, level, supaccount, alg_name, fun_unit, variables.

ind: [REQUIRED] Unique identifier for each account
code_of_account: [REQUIRED] Code of account
account_description: [Optional] Description of the account
total_cost: [REQUIRED] Total cost of the account unit in dollars
level: [REQUIRED] Level of the account
supaccount: [REQUIRED] Superior account
alg_name: [Optional] Algorithm name to calculate the cost of the account
fun_unit: [Optional] Algorithm function output unit
variables:[Optional] Variables used in the algorithm

[Example account Table]#
ind	code_of_account	account_description	total_cost	level	supaccount	alg_name	fun_unit	variables
1	2	User defined direct cost	1000000000	0
2	21	User defined total cost 21	700000000	1	2
3	211	User defined total cost 211	100000000	2	21	ud211	million	v1, v2, v3
4	212	User defined total cost 212	200000000	2	21	ud212	dollar	v5, v8, v7
5	213	User defined total cost 213	400000000	2	21
7	2131	User defined total cost 2131	150000000	3	213	ud2131	million	v3, v2, v5
8	2132	User defined total cost 27	250000000	3	213	ud2132	million	v8, v4, v6
9	22	example account without algorithm	300000000	2	22

Make sure that all the hierarchy accounts can be connected from the top level to the bottom level. And for each account, the supaccount should be the code_of_account of its parent account. If any algorithm is applied to the account, the alg_name should be specified. The fun_unit is the unit of the algorithm function. The variables are the variables that are used in the algorithm. Total cost is the in the unit of dollar.

Run the following command to generate the user-defined account table.

python ACCERT/src/scripts/gen_user_defined.py

The script will generate a user_defined_account.csv file in the same directory, and also generate a raw_variable_automated_generated.csv file. The user_defined_account.csv file will be used in the next step to generate the user-defined algorithm table. The raw_variable_automated_generated.csv file will be filled in by the user to provide the values of the variables used in the algorithm.

[Generated account Table with added review_status and prn columns]#
ind	code_of_account	account_description	total_cost	level	supaccount	review_status	prn	alg_name	fun_unit	variables
1	2	User defined direct cost	1000000000	0		Unchanged	1
2	21	User defined total cost 21	700000000	1	2	Unchanged	0.7
3	211	User defined total cost 211	100000000	2	21	Unchanged	0.1	ud211	million	v1, v2, v3
4	212	User defined total cost 212	200000000	2	21	Unchanged	0.2	ud212	dollar	v5, v8, v7
5	213	User defined total cost 213	400000000	2	21	Unchanged	0.4
7	2131	User defined total cost 2131	150000000	3	213	Unchanged	0.15	ud2131	optional input	v3, v2, v5
8	2132	User defined total cost 27	250000000	3	213	Unchanged	0.25	ud2132	million	v8, v4, v6
9	22	example account without algorithm	300000000	2	22	Unchanged	0.3

The raw_variable_automated_generated.csv file will be filled in by the user to provide the values of the variables used in the algorithm, the following columns are required:

var_value: value of the variable

var_unit: unit of the variable

[Generated variable Table]#
ind	var_name	var_value	var_unit
1	v1	REQUIRED	REQUIRED
2	v2	REQUIRED	REQUIRED
3	v3	REQUIRED	REQUIRED
4	v4	REQUIRED	REQUIRED
5	v5	REQUIRED	REQUIRED
6	v6	REQUIRED	REQUIRED
7	v7	REQUIRED	REQUIRED
8	v8	REQUIRED	REQUIRED

If some variables are calculated from other variables, the user can fill in the algorithm name in the var_alg column, and the variables in the var_need column:

var_alg: algorithm associated with the variable if any

var_need: variables needed to calculate the variable if any

v_linked: superior variable if any

[Example filled in variable Table]#
ind	var_name	var_value	var_unit	var_alg	var_need	v_linked
1	v1	20	million			v6
2	v2	50	million
3	v3	30	million			v6
4	v4	2.5	km
5	v5	1500	m
6	v6	50	million	ud_cal_v6	v1, v3
7	v7	50000000	dollar
8	v8	100000	$/m

Fill in your own algorithm#

Save the filled in variable file as raw_variable.csv, run the command again to generate the algorithm table, databse sql file and the algorithm python file.

python ACCERT/src/scripts/gen_user_defined.py

The script will generate 3 files: - user_defined_algorithm.csv will a reference table. - user_defined_algorithm.sql will be used to create the database table. - user_defined_func.py will be used to calculate the total cost of each account or variable.

[Generated algorithm Table]#
ind	alg_name	alg_for	alg_description	alg_python	alg_formulation	alg_units
1	ud211	c	description of ud211 optional	user_defined_func	formulation of ud211 optional	million
2	ud212	c	description of ud212 optional	user_defined_func	formulation of ud212 optional	dollar
3	ud2131	c	description of ud2131 optional	user_defined_func	formulation of ud2131 optional	million
4	ud2132	c	description of ud2132 optional	user_defined_func	formulation of ud2132 optional	million
5	ud_cal_v6	v	description of ud_cal_v6 optional	user_defined_func	formulation of ud_cal_v6 optional	million

This table will be used to create the database table. the column alg_for is the account or variable that the algorithm is applied to, c means the algorithm is applied to the account, v means the algorithm is applied to the variable. The alg_name is the name of the algorithm. The alg_unit is the unit of the algorithm function output.

And the user_defined_func.py file will be used to calculate the total cost of each account. User can modify the user_defined_func.py file to implement the algorithm. Each algorithm function will have the notes to explain the algorithm with the needed variables.

import numpy as np
from .Algorithm import Algorithm

class user_defined_func(Algorithm):
    def __init__(self, ind, alg_name, alg_for, alg_description, alg_formulation, alg_units, variables, constants):
        super().__init__(ind, alg_name, alg_for, alg_description, alg_formulation, alg_units, variables, constants)
    
    def run(self, inputs: dict) -> float:
        """
        Executes the algorithm specified by the name in the instance variables.
        
        Parameters:
        inputs (dict): Dictionary of input variables required for the algorithm.

        Returns:
        float: Result of the algorithm computation.
        """
        # run the algorithm using self.name not self.alg_name
        return self._run_algorithm(self.name, [inputs[var.strip()] for var in self.variables.split(",") if var.strip()])
    
    def _run_algorithm(self, alg_name: str, variables: list) -> float:
        """
        Runs the specified algorithm with given variables.
        
        Parameters:
        alg_name (str): The name of the algorithm to run.
        variables (list): List of input variables for the algorithm.

        Returns:
        float: Result of the algorithm computation.
        """
        try:
            algorithm = getattr(self, alg_name)
            return algorithm(*variables)
        except AttributeError:
            raise ValueError(f"Algorithm {{alg_name}} not found")
    
    @staticmethod
    # ud211: description of ud211 optional
    def ud211(v1, v2, v3):
        # TODO: Implement the logic for ud211
        # The output unit should be million
        pass
    
    @staticmethod
    # ud212: description of ud212 optional
    def ud212(v5, v8, v7):
        # TODO: Implement the logic for ud212
        # The output unit should be dollar
        pass
    
    @staticmethod
    # ud2131: description of ud2131 optional
    def ud2131(v3, v2, v5):
        # TODO: Implement the logic for ud2131
        # The output unit should be million
        pass
    
    @staticmethod
    # ud2132: description of ud2132 optional
    def ud2132(v8, v4, v6):
        # TODO: Implement the logic for ud2132
        # The output unit should be million
        pass
    
    @staticmethod
    # ud_cal_v6: description of ud_cal_v6 optional
    def ud_cal_v6(v1, v3):
        # TODO: Implement the logic for ud_cal_v6
        # The output unit should be million
        pass

After filled in the algorithm function, it should be saved as user_defined_func.py in the same directory. Here is an example of the filled in algorithm function.

import numpy as np
from .Algorithm import Algorithm

class user_defined_func(Algorithm):
    def __init__(self, ind, alg_name, alg_for, alg_description, alg_formulation, alg_units, variables, constants):
        super().__init__(ind, alg_name, alg_for, alg_description, alg_formulation, alg_units, variables, constants)
    
    def run(self, inputs: dict) -> float:
        """
        Executes the algorithm specified by the name in the instance variables.
        
        Parameters:
        inputs (dict): Dictionary of input variables required for the algorithm.

        Returns:
        float: Result of the algorithm computation.
        """
        # run the algorithm use self.name not self.alg_name
        return self._run_algorithm(self.name, [inputs[var] for var in self.variables.split(",")])

    def _run_algorithm(self, alg_name: str, variables: list) -> float:
        """
        Runs the specified algorithm with given variables.
        
        Parameters:
        alg_name (str): The name of the algorithm to run.
        variables (list): List of input variables for the algorithm.

        Returns:
        float: Result of the algorithm computation.
        """
        try:
            algorithm = getattr(self, alg_name)
            return algorithm(*variables)
        except AttributeError:
            raise ValueError(f"Algorithm {alg_name} not found")

    @staticmethod
    #ud211: User defined total cost 211
    def ud211(v1, v2, v3):
        return v1 + v2 + v3
    
    @staticmethod
    #ud212: User defined total cost 212
    def ud212(v5, v8, v7):
        some_calc = v5 * v8 + v7
        return some_calc
    
    @staticmethod
    #ud2131: User defined total cost 2131
    def ud2131(v3, v2, v5):
        some_var = v2 - v3
        other_var = some_var/200
        total_cost = other_var * v5
        return total_cost
    
    @staticmethod
    #ud2132: User defined total cost 2132
    def ud2132(v8, v4, v6):
        if v6 > 52:
            return v4*v8/999
        else:
            return v4*v8/1000

    @staticmethod
    #ud_cal_v6: User defined calculation for v6
    def ud_cal_v6(v1, v3):
        return v1 + v3

Create the database table#

In the same folder a user_defined_algorithm.sql file will be generated. Run the following command to implement all the tables in the database.

python ACCERT/src/scripts/run_sql.py

The script will create the database table and insert the data from the user_defined_account.csv, user_defined_variable.csv and user_defined_algorithm.csv files into the database.

You can also run mysql command and source the user_defined_algorithm.sql file to create the database table.

$ mysql -h localhost -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2475
Server version: 8.0.27 MySQL Community Server - GPL

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>source user_defined_algorithm.sql

Prepare the input file#

After the database table is created, you can start preparing the input file for your user-defined reference model. The input file should be in the son format. Run the following command to make changes to ACCERT schema to accept the user-defined reference model.

python ACCERT/src/scripts/prepared_input.py

The script will generate a list of accepted values for the user-defined reference model. The user can fill in the input file with the accepted values. The input file should be in the son format. Here is an example of the input file.

% ACCERT Test input file
% Author: J. Zhou (jia.zhou@anl.gov)
% Last updated: 08/08/2024


accert{
    ref_model = "user_defined"
    % variables can be assigned at top level
    var( v3 ){ value = 29  unit = million }
    l0COA(2){
        l1COA(21){
            l2COA(212){
                total_cost{value = 201 unit = million} 
            }
        }
    }
}

Run your own reference model#

After the input file is prepared, you can run the reference model with the following command.

python ACCERT/src/Main.py -i user_defined_input.son

The script will generate the output file in the same directory. The output file will be in the son format. Here is an example of the output file, that just created.

::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
:::'###:::::'######:::'######::'########:'########::'########:
::'## ##:::'##... ##:'##... ##: ##.....:: ##.... ##:... ##..::
:'##:. ##:: ##:::..:: ##:::..:: ##::::::: ##:::: ##:::: ##::::
'##:::. ##: ##::::::: ##::::::: ######::: ########::::: ##::::
 #########: ##::::::: ##::::::: ##...:::: ##.. ##:::::: ##::::
 ##.... ##: ##::: ##: ##::: ##: ##::::::: ##::. ##::::: ##::::
 ##:::: ##:. ######::. ######:: ########: ##:::. ##:::: ##::::
..:::::..:::.......::::......::........::..:::::..:::::..:::::


======================================== Reading user input ========================================


[USER_INPUT] Reference model is "user_defined" 

WARNING: No power input found in the user input file

[Updating] Variable v3
[Updated]  Changed from 30.0 million to 29.0 million

[Updating] Sup Variable v6, running algorithm: [ud_cal_v6], 
[Updating] with formulation: formulation of  ud_cal_v6 optional
[Updated]  Reference value is : 5.00e+01 million, calculated value is: 4.90e+01 million
 
=================================Extracting user changed variables==================================


+----------+-----------------------------+-----------+----------+
| var_name |       var_description       | var_value | var_unit |
+----------+-----------------------------+-----------+----------+
|    v3    | description of  v3 optional |   29.00   | million  |
|    v6    | description of  v6 optional |   49.00   | million  |
+----------+-----------------------------+-----------+----------+


====================================Extracting affected accounts====================================
variable "v3" affects account(s):
211, 2131

variable "v6" affects account(s):
2132

======================================== Updating accounts =========================================
[Updating] Account [211], running algorithm: [ud211], 
[Updating] with formulation: formulation of  ud211 optional
[Updating] Total cost of account 211
[Updated]  Changed from 100,000,000.00 dollar to 99,000,000.00 dollar

 
[Updating] Account [2131], running algorithm: [ud2131], 
[Updating] with formulation: formulation of  ud2131 optional
[Updating] Total cost of account 2131
[Updated]  Changed from 150,000,000.00 dollar to 157,500,000.00 dollar

 
[Updating] Account [2132], running algorithm: [ud2132], 
[Updating] with formulation: formulation of  ud2132 optional
[Updating] Total cost of account 2132
[Updated]  Changed from 250,000,000.00 dollar to 250,000,000.00 dollar

 
========================================== IMPORTANT NOTE ==========================================
Some cost have changed by user inputs and may not be reflected correctly in the cost elements table.

[Updating] Total cost of account 212
[Updated]  Changed from 200,000,000.00 dollar to 201,000,000.00 dollar

===================================== Rolling up account table =====================================


[Updating] Rolling up account table from level 4 to level 3 
[Updating] Rolling up account table from level 3 to level 2 
[Updating] Rolling up account table from level 2 to level 1 
[Updating] Rolling up account table from level 1 to level 0 
[Updated]  Account table rolled up

=============================== Generating results table for review ================================


+-----------------+-----------------------------------+------------+-------+---------------+
| code_of_account |        account_description        | total_cost | level | review_status |
+-----------------+-----------------------------------+------------+-------+---------------+
| 2               | User defined direct cost          |   1,007.50 |   0   |    Updated    |
|  21             | User defined total cost 21        |     707.50 |   1   |    Updated    |
|   211           | User defined total cost 211       |      99.00 |   2   |   User Input  |
|   212           | User defined total cost 212       |     201.00 |   2   |   User Input  |
|   213           | User defined total cost 213       |     407.50 |   2   |    Updated    |
|    2131         | User defined total cost 2131      |     157.50 |   3   |   User Input  |
|    2132         | User defined total cost 27        |     250.00 |   3   |   User Input  |
|  22             | example account without algorithm |     300.00 |   1   |   Unchanged   |
+-----------------+-----------------------------------+------------+-------+---------------+
Successfully created excel file user_defined_updated_account.xlsx