/
Brief Instructions for Project Configuration and Build Creation

Brief Instructions for Project Configuration and Build Creation

1. Brief Project Configuration Guide

Options File Setup

From the root folder MySales, navigate to the config directory and create a file
options_[name].json5, where [name] is the project name.

Copy the standard options file template (see Appendix) into the newly created file, replacing [name] with the project name.

In the db_interface field, specify the database driver in use:

a. './DbMysql' – driver for MySQL;
b. './DbMssqlNew' – driver for MSSQL;
c. './DbOracle' – driver for Oracle DB;
d. './DbPostgresql' – driver for PostgreSQL.

In the connection field, provide the required database connection parameters according to the samples (see Appendix).

In the weather.location field, remove city codes that are not used by the client.

In the end_week field, set the maximum available week if the project is a pilot project, or 0 if it is production.

In the start_week field, set the minimum (earliest) historical week.

If the Client has more than one region, set the use_all_region parameter to true; otherwise, set it to false.

Save the file.

SQL Queries File Setup

  1. From the root folder MySales, navigate to the config directory and create a file
    sql_[name].ini, where [name] is the project name.

  2. Add to the file the main SQL queries for the following purposes
    (see Appendix for the SQL query format):

Request Name

Request Description

Request Name

Request Description

all_stores

Returns the following fields:

  1. Store number NUM;

  2. Store name NAME;

  3. Store region number REGION;

  4. Weather region code WAETHER.

The results are sorted in ascending order by region number and store number.

Example query:

SELECT STORE_ID NUM, STORE_NAME NAME,REGION_ID REGION, WEATHER_CODE WEATHERFROM STORES ORDER BY REGION_ID, STORE_ID

all_regions

Returns the following fields:

  1. Region number NUM;

  2. Region name NAME;

Weather region code WEATHER.

  • general (zero) region string.

Example query:

SELECT '0' NUM, 'All' NAME, null WEATHERUNION ALLSELECT REGION_ID NUM, NAME NAME, WEATHER_CODE WEATHERFROM REGIONS

all_groups

Returns the following fields:

  1. Group number GRP;

  2. Group name NAME.

Group numbers sorted in ascending order.

Example query:

SELECT GROUP_ID GRP, NAME NAME FROM GROUPS ORDER BY GROUP_ID

sku_group

Returns the following fields:

  1. SKU number;

  2. SKU NAME;

  3. GRP group number.
    With the ability to pass the following parameters (for information on passing parameters, see Appendix):

  4. SKU number - [sku_list];

  5. Group number - [group_list];

  6. Supplier number - [supplier_list].

Group numbers and
SKU numbers sorted in ascending order.
Query example:

SELECT SKU_ID SKU, ARTICLE_NAME NAME, GROUP_ID GRPFROM PRODUCTS WHERE SKU_ID [sku_list] AND GROUP_ID [group_list] AND SUPPLIER_ID [supplier_list] ORDER BY GROUP_ID, SKU_ID

all_suppliers

Returns the following fields:

  1. Supplier number NUM;

  2. Supplier name NAME.
    Sorted in ascending order by supplier name.

Example query:

SELECT SUPPLIER_ID NUM, SUPPLIER_NAMENAMEFROM SUPPLIERS ORDER BY SUPPLIER_NAME

  1. Add to the file the predefined variables “price_disc” and “price_disc_sum” in the following format: 1. {price_disc}
    case when [ sales volume ] = 0 then null else ( [ sales value ] + [ discount value ] ) / [ sales volume ] end PRICE,
    case when [ sales volume ] = 0 then 0 else [ discount value ] / [ sales volume ] end DISC

2.{price_disc_sum}
case when sum([ sales volume ]) = 0 then null else sum([ sales value ] + [ discount value ]) / sum([ sales volume ]) end PRICE,
case when sum([ sales volume ]) = 0 then 0 else sum([ discount value ]) / sum([ sales volume ]) end DISC

Square brackets must be replaced with the corresponding fields in the database.

  1. Add to the file the main SQL queries for loading sales data (see Appendix for the SQL query format).

Request Name

Request Description

Request Name

Request Description

sales_by_group_store

Returns the following fields:

  1. Store number (STORE);

  2. Week (WEEK);

  3. Number of items in stock in the (CNT) group;

  4. Stock in the (STOCK) group;

  5. Sales in the (SALES) group;

  6. {price_disc}.

With the ability to pass the following parameters (for information on passing parameters, see Appendix):

  1. Store number - [store_list];

  2. Group number - :group;

  3. First week - :start_week;

  4. Last week - :end_week.

Example query:

SELECT STORE_ID STORE, WEEK,STOCK_ITEM_COUNT CNT, STOCK_VOLUMESTOCK, SALES_VOLUME SALES, {price_disc} FROM SALES_GROUPWHERE STORE_ID [store_list] AND GROUP_ID = :groupAND WEEK BETWEEN :start_week AND :end_weekAND ((sales_volume > 0) OR (stock_volume > 0))

sales_sum_stores_sum_groups

Returns the following fields:

  1. Week;

  2. Sum of items in the warehouse
    by the CNT group;

  3. Sum of inventory by the STOCK group;

  4. Sum of sales by the SALES group;

  5. {price_disc_sum}.

With the ability to pass the following parameters (for information on passing
parameters, see Appendix):

  1. First week - :start_week;

  2. Last week - :end_week.

Grouping by the Week field.

Example query:

SELECT WEEK, SUM(STOCK_ITEM_COUNT) CNT, SUM(STOCK_VOLUME) STOCK,SUM(SALES_VOLUME) SALES, {price_disc_sum} FROM SALES_GROUPWHERE WEEK BETWEEN :start_week AND:end_weekAND ((sales_volume > 0) OR (stock_volume > 0)) GROUP BY WEEK

sales_by_stores_sum_groups

Returns the following fields:

  1. STORE number ;

  2. WEEK;

  3. Sum of items in the
    warehouse by group CNT;

  4. Sum of inventory by group
    STOCK;

  5. Sum of sales by group
    SALES;

  6. {price_disc_sum}.

With the ability to pass
the following parameters (for information on passing
parameters, see Appendix):

  1. Store number - [store_list];

  2. First week - :start_week;

  3. Last week - :end_week.

Grouping by fields: Store number; Week.
Query example:

SELECT STORE_ID STORE, WEEK, SUM(STOCK_ITEM_COUNT) CNT, SUM(STOCK_VOLUME) STOCK,SUM(SALES_VOLUME) SALES, {price_disc_sum} FROM SALES_GROUPWHERE STORE_ID [store_list] AND WEEK BETWEEN :start_week AND :end_week AND ((sales_volume > 0) OR (stock_volume > 0)) GROUP BY STORE_ID, WEEK

sales_by_group_sum_stores

Returns the following fields:

  1. WEEK;

  2. Sum of items in the warehouse
    by group CNT;

  3. Sum of remaining stock by group
    STOCK;

  4. Sum of sales by group
    SALES;

  5. {price_disc_sum}.

With the ability to pass
the following parameters (for information on passing
parameters, see Appendix):

  1. Group number - :group;

  2. First week - :start_week;

  3. Last week - :end_week.

Grouping by fields:
Group number; Week.
Query example:

SELECT WEEK, sum(STOCK_ITEM_COUNT) CNT, sum(STOCK_VOLUME) STOCK,sum(SALES_VOLUME) SALES, {price_disc_sum} FROM SALES_GROUPWHERE GROUP_ID = :group AND WEEK between :start_week and :end_week AND ((sales_volume > 0) or (stock_volume > 0)) GROUP BY GROUP_ID, WEEK

sales_by_sku_store

Returns the following fields:

  1. SKU number;

  2. STORE number;

  3. WEEK;

  4. STOCK number;

  5. SALES number;

  6. {price_disc}.

With the ability to pass the following parameters (for information on passing parameters, see Appendix):

  1. Store number - [store_list];

  2. SKU number - :[sku_list];

  3. First week - :start_week;

  4. Last week - :end_week.

Example request:

SELECT SKU_ID SKU, STORE_ID STORE, WEEK, STOCK_VOLUME STOCK, SALES_VOLUME SALES, {price_disc} FROM SALES_SKU WHERE STORE_ID [store_list] AND SKU_ID [sku_list] AND ((sales_volume > 0) or (stock_volume > 0)) AND WEEK between :start_week and :end_week

sales_by_sku_sum_stores

Returns the following fields:

  1. SKU number;

  2. WEEK;

  3. Number of records (CNT);

  4. Stock total by SKU
    STOCK;

  5. Sales total by SKU;

  6. {price_disc_sum}.

With the ability to pass the following parameters (for information on passing parameters, see Appendix):

  1. Group number - :[sku_list];

  2. First week - :start_week;

  3. Last week - :end_week.
    Grouping by fields: SKU number;
    Week.

Example query:

SELECT SKU_ID SKU, WEEK, count(*) CNT, sum(STOCK_VOLUME) STOCK, sum(SALES_VOLUME) SALES, {price_disc_sum} FROM SALES_SKU WHERE SKU_ID [sku_list] AND ((sales_volume > 0) or (stock_volume > 0)) AND WEEK between :start_week and :end_week GROUP BY SKU_ID, WEEK

  1. Save the file.

Directory Setup

  1. Open the root folder MySales in the command line (terminal) and enter the following command to create the project directory: mkdir data/data_[name]
    where [name] is the project name.

  2. Next, enter the command to generate the license file: node util/generate_keys.js options_[name].json5
    where [name] is the project name.

  3. Copy the files containing the USD exchange rate history (currency_rate.json) and weather data (weather.json) into the created directory mysales/data/data_[name].

Build Configuration

  1. From the root folder MySales, navigate to the dist directory and create a file
    update_[name].sh, where [name] is the project name.

  2. Fill in the file according to the example:

echo Downloading installation >>./data_ [name] /update.log wget "https://www.dropbox.com/s/qqz3qn2yqttcq6e/mysales.zip?dl=1%22” -O mysales.zip >>./data_ [name] /update.log wget "" -O mysales_ [name] .zip >>./data_ [name] /update.log unzip -o mysales.zip >>./data_ [name] /update.log unzip -o mysales_ [name] .zip >>./data_ [name] /update.log node stop >>./data_ [name] /update.log pkill -9 -f node >>./data_ [name] /update.log cp log.txt prev_log.txt >>./data_ [name] /update.log nohup node start &>log.txt & echo Update completed. Instance started >>./data_ [name] /update.log date +"%m/%d/%Y %H:%M:%S $HOSTNAME" >>./data_ [name] /update.log

where [name] is the project name.

  1. From the root folder MySales, navigate to the util directory and open the file make.sh.

  2. Before the line echo Completed, add the entry for building the project:

echo Make for [name] mkdir ./data_ [name] cp ../data/data_demo/currency_rate.json ./data_ [name] cp ../data/data_demo/weather.json ./data_ [name] node ../util/generate_keys.js ./options_ [name] .json5 cp ./options_ [name] .json5 ./options.json5 cp ./update_ [name] .sh ./update.sh zip -r ./mysales_ [name] ./options.json5 [db_interface] ./options_ [name] .json5 ./sql_ [name] . ini ./data_[ name] update.sh start.sh start.bat rm -r ./data_ [name]

where [name] is the project name, [db_interface] is the database driver:

a. ./DbMysql – driver for MySQL;
b. ./DbMssqlNew – driver for MSSQL;
c. ./DbOracle – driver for Oracle DB;
d. ./DbPostgresql – driver for PostgreSQL.

  1. Save the file.

2. Brief Instructions for Build Creation

When creating a project build, the following stages can be distinguished:

  1. Build creation;

  2. Updating configuration files (required only after creating the first build for the project);

  3. Build installation.

Build Creation

  1. Open the root folder MySales in the command line (terminal) and run the following command to create the build: util/make.sh

  2. Wait until the builds for all projects are completed.

  3. The builds are stored in the Dropbox repository in the MySalesDist folder.

Updating Configuration Files

  1. Open the MySalesDist folder in Dropbox.

  2. Provide access via link to the file mysales_[name].zip, where [name] is the project name.

  3. Copy the access link and paste it into:

a. the options file options_[name].json5 in the updater.optArchUrl field;
b. the update file update_[name].sh in the field
wget "[link]" -O mysales_[name].zip

where [name] is the project name,
[link] is the URL.

Note: If the link ends with “0”, replace “0” with “1” in both the options and update files.

Build Installation

  1. Open the MySalesDist folder in Dropbox.

  2. Download the following files:

a. mysales.zip;
b. mysales_[name].zip, where [name] is the project name.

  1. Extract the downloaded files, then delete the archive files.

  2. Move the contents of the folders mysales and mysales_[name] (where [name] is the project name) into a single folder and open it in the command line (terminal).

  3. Run the command: npm i

  4. The build installation is complete. To start MySales, open the folder containing the MySales files in the command line (terminal) and run: node app

3. Additional Configuration

Adding an Assortment Matrix

  1. Open the project configuration file.

  2. In the loader field, add the property load_range_days with the value N (the number of most recent days for which prices are loaded; default is 365).

  3. In the default_predictors field, add a new predictor 'assrt'.

  4. Save the configuration file.

  5. Open the SQL queries file.

  6. Add the following SQL query:

Request Name

Request Description

Request Name

Request Description

ranges_by_sku_store

Returns the following fields:

  1. SKU number;

  2. STORE number;

  3. Week of item addition to
    stock at store
    STARTW;

  4. Week of item removal from
    stock at store
    ENDW.

The following parameters can be passed:

  1. List of stores - [store_list];

  2. List of SKUs - [sku_list];

  3. Verification date - :end_date.

Example request:

SELECT SKU_ID SKU, STORE_ID STORE, TO_NUMBER(TO_CHAR(START_DATE, ‘IYYYIW’))STARTW, TO_NUMBER(TO_CHAR(NVL(END_DATE,TO_DATE('20500101', 'YYYYMMDD')), ‘IYYYIW’))ENDW FROM SKU_RANGEWHERE (END_DATE >= TO_DATE(‘:end_date’,'YYYY-MM-DD') OR END_DATE IS NULL) AND STORE_ID [store_list] AND SKU_ID [sku_list]

  1. Save the SQL queries file.

Adding Prices

  1. Open the project configuration file.

  2. In the loader field, add the property load_price_days with the value N (the number of most recent days for which prices are loaded; default is 365).

  3. Save the configuration file.

  4. Open the SQL queries file.

  5. Add the following SQL query:

Request Name

Request Description

Request Name

Request Description

price_by_sku_store

Returns the following fields:

  1. SKU number;

  2. Price region number;

  3. PRICE;

  4. Price input date (date format: YYYY-MM-DD) STARTD;

  5. Price output date (date format: YYYY-MM-DD) ENDD.

With the ability to pass
the following parameters:

  1. List of stores - [store_list];

  2. List of SKUs - [sku_list];

  3. Verification date - :start_date.

Example request:

SELECT SKU_ID SKU, PRICELINE_ID PRL, PRICE, TO_CHAR(START_DATE, 'YYYY-MM-DD') STARTD, TO_CHAR(END_DATE, 'YYYY-MM-DD') ENDD FROM PRICES WHERE SKU_ID [sku_list] AND PRICELINE_ID IN (SELECT PRICELINE_ID FROM STORES WHERE STORE_ID [store_list]) AND (END_DATE > TO_DATE(':start_date', 'YYYY-MM-DD') OR END_DATE IS NULL)

6. In the SQL query all_stores, it is necessary to add the output field for the pricing region PRL.
Example query:

SELECT STORE_ID NUM, STORE_NAME NAME, PRICE_LINE_ID PRL, REGION_ID REGION, WEATHER_CODE WEATHER FROM STORES ORDER BY REGION_ID, STORE_ID

  1. Save the SQL queries file.

4. Appendix

Standard Options File

The standard options file is located in the MySales directory at the following path:
config/docs/options_[name].json5.

Database Connection Settings

MySQL
{ connectionLimit: 10, host: ' [host / server] ', port: [port] , user: ' [username] ', password: ' [password] ’, database: ' [name of database] ’, multipleStatements: true }
MSSql
{ user: ' [username] ', password: ' [password] ', server: ' [host / server] ', port: [port] , database: ' [name of database] ', pool: { max: 50, min: 0, idleTimeoutMillis: 3000000 }, connectionTimeout: 3000000, requestTimeout: 4000000 }
Azure Sql
'Server=tcp: [server/host] ,1433;Database= [nameOfDatabase] ;Uid= [userName] ;Pwd= [pas sword] ;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=9999999;'
Oracle
{ user: " [username] ", password: " [password] ", tns: "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST= [host/server] )(PORT= [port] ))(C ONNECT_DATA=(SERVICE_NAME= [serviceName] )))", poolSize: 5 }
PostgreSql
{ host: ' [host / server] ', port: [port] , user: ' [username] ', password: ' [password] ', database: ' [name of database] ', schema: ' [name of schema] ' }

SQL Query Format

[ [name of SQL query] ] SQL query

Variables in SQL queries

MySales supports two types of variables:

  1. containing a single value;

  2. containing a list of values.
    The first type is used as follows:
    WHERE COLUMN_NAME = :variable_name
    If the variable's value type is string, the variable entry looks like this:
    WHERE COLUMN_NAME = “:variable_name”
    When using the second type of variable, the entry looks like this:
    WHERE COLUMN_NAME [variable_name]
    If the variable's value type is string, the variable entry looks like this:
    WHERE COLUMN_NAME “[variable_name]”

5. Initial Configuration Files

User access to pages is controlled only in local security mode in the user settings under the accessByPage key.

allow {Array} defines the list of pages visible in the menu and accessible; only these will be visible.
forbid {Array} defines the list of pages excluded from visibility. The rest will remain visible.
To add a page to the list, it must be written to the array as the string '/path_to_page' , and the path must be a single syllable, i.e., not /path1/path2 , etc.

What must be remembered: access via accessByPage regulates only access to page rendering, not to the API. Some page groups have a composite path; for example, /promo/match. Not all possible pages are visible in the menu—for instance, /promo/match itself is not displayed.

To allow or deny access to a group of pages under a single root route, it is sufficient to specify the root path, for example, /promo.

Menu visibility is controlled in the file views/header.inc.jade via isDisplay(), and it is necessary to ensure that the paths there match the actual routes being called.

It should also be noted that in the current architecture, the router passes to isLoggedInRoute a path with the base already removed, which complicates the handling of the root '/'.

In the future, a solution is needed for regulating access to page groups, such as replenishment. Listing all pages individually for access control is inefficient, and adding new pages requires updating user policies.