Effector 6.3 developer manual

XML reference 6.3

Effector Studio 6.3 summary

Effector Studio 6.3 manual

Effector 6.2 developer manual

XML reference 6.0

Effector Studio 3.2 summary

Effector Studio 3.2 manual

ExcelLikeForm

With this component we can register data on an excel like surface.

Basic functions:

  • Saving
  • Loading
  • Collection of data at loading
  • Getting data at saving

The files can be found in the ExcelLikeForm directory. The prefix of the files needs to be ELF.

<?xml version="1.0" encoding="ISO-8859-2"?>

<ExcelLikeForm>
    <Caption />
    <DataDefinition>DataDefinitionHQExcel</DataDefinition>
    <BusinessObject>BusinessObjectExcel</BusinessObject>

    <IDColumn>id</IDColumn>
    <NameColumn>ExcelName</NameColumn>

    <SpreadJSONColumn>JSON</SpreadJSONColumn>
    <TemplateIDColumn>TemplateExcelID</TemplateIDColumn>
    <TemplateNameColumn>TemplateExcelName</TemplateNameColumn>
    <DataExchangeSettings>
        <DataLoaderStoredProcedure>osp_wrk_ExcelTakeOut_CollectData</DataLoaderStoredProcedure>
        <DataSaverStoredProcedure>osp_wrk_ExcelTakeOut_DistributeData</DataSaverStoredProcedure>
        <ParametersTable>orn_ExcelTakeOutParameters</DataParametersTable>

        <ValuesTable>orn_ExcelTakeOutData</ValuesTable>
    </DataExchangeSettings>
    <TemplateURL></TemplateURL>
</ExcelLikeForm>

BusinessObject Excel uses this BO for the loading and saving of data. The system also takes into account the settings on rights.

IDColumn The name of a column in the DataDefinition which contains the unique identifier of the Excel.

NameColumn The name of a column in the DataDefinition which contains the name of the Excel. It is an optional setting.

SpreadJSONColumn: The name of the column (BO) which contains the JSON describing the Excel.

TemplateIDColumn: The name of the column (BO) which determines which template excel was used to creating the given row.

TemplateNameColumn: The name of the column which can contain the name of the template. Optional setting.

DataLoaderStoredProcedure: Optional, the name of the stored procedure which collects the data at loading. It needs to receive the following parameters (these are loaded automatically by the system):

@ProcessID BIGINT: Mandatory parameter, contains the identifier summarizing the processing (the system stores the values under this identifier).

@ExcelID INT: Mandatory parameter, identifies one of the rows of the table containing the Excel JSON.

  • @TemplateExcelID INT: Mandatory parameter, identifies which template was used to create the Excel row.

  • @userID INT: Optional parameter, if it exists, the system fills it with the ID of the logged in user.

DataSaverStoredProcedure: Optional, the name of the procedure that will distribute the data upon saving. It needs to receive the following parameters (these are loaded automatically by the system):

@ProcessID BIGINT: Mandatory parameter, contains the identifier enclosing the processing (the system stores the values under this identifier).

@ExcelID INT: Mandatory parameter, identifies one of the rows of the table containing the Excel JSON.

  • @TemplateExcelID INT: Mandatory parameter, identifies which template was used to create the Excel row.

  • @userID INT: Optinal parameter, if it exists, the system fills it with the ID of the logged in user.

  • @Comment VARCHAR(MAX): Optional parameter: if it exists and if we use a unique excellikeform realization, then in this parameter we can transfer a text entered on the interface, the saving of which needs to be taken care of in the DataSaverStoredProcedure stored procedure.

ParametersTable Optional, the name of the table that contains the cell ranges that will be used by the system when collecting and distributing data.

ValuesTable: Optional, the name of the table used by the system for loading and saving the data. The DataLoaderStoredProcedure and the DataSaverStoredProcedure writes/reads this table according to the parameters defined in the ParametersTable.

SheetsToHide: Optional; used to hide certain sheets (for example for user righs considerations). The names of the sheets to be hidden have to be listed separated by a “#/#” sign.

TemplateURL Rarely used option. Basically, we have to set the path to the view that we would like to use instead of the default display. This makes it possible to write a unique javascript code which executes operations that a general display cannot interpret. For example, such an operation can be used to hide certain rows based on the loaded data.

Naturally the DataLoaderStoredProcedure, DataSaverStoredProcedure, ParametersTable and the ValuesTable settings are not mandatory, so there is a possibility to save only the JSON. On the other hand, if one of them is set, none of the others can be omitted.

The Parameters table contains the description of the cell ranges that will be used by the system upon collecting and distributing the data.

CREATE TABLE [dbo].[orn_ExcelTakeOutParameters](

    [id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [TemplateExcelID] [int] NULL,
    [Name] [varchar](200) NULL,
    [Direction] [varchar](5) NULL,
    [RangeName] [varchar](200) NULL,
    [WorkSheetName] [varchar](200) NULL,
    [Absolute_Start_RowIndex] [int] NULL,
    [Absolute_Start_ColumnIndex] [int] NULL,
    [Absolute_End_RowIndex] [int] NULL,
    [Absolute_End_ColumnIndex] [int] NULL,
    [Created] [datetime] NULL,
    [CreatedByID] [int] NULL,
    [Deleted] [tinyint] NULL
) ON [PRIMARY]
  • TemplateExcelID: The parameter is related to which Excel template.

  • Name: Technical name, to be referenced in the Values table.

*Direction : IN (only incoming data, used by DataLoaderStoredProcedure), OUT (only outgoing data, used by DataSaveerStoredProcedure), INOUT (both directions so the DataLoader and the DataSaver also uses this parameter)

  • RangeName: Optional. The name of the cell range named and defined in the Excel. If the Excel does not contain such an interval, it has to be given in the following 5 parameters.

  • WorkSheetName: Optional, the name of the work tab. To be filled only if the RangeName is empty.

  • Absolute_Start_RowIndex, Absolute_Start_ColumnIndex: Optional, the top left corner of the cell range, referenced by 0-based ordinal numbers. To be filled only if the RangeName is empty.

    Absolute_End_RowIndex, Absolute_End_ColumnIndex: Optional, the bottom right corner of the cell range, referenced by 0 based-ordinal numbers. To be filled only if the RangeName is empty.

The ValuesTable is the name of the table, that the system uses to load and save the data. The DataLoaderStoredProcedure and the DataSaverStoredProcedure writes/reads this table according to the parameters set in ParametersTable. The structure of this table can change but it contains mandatory fields.

CREATE TABLE [dbo].[orn_ExcelTakeOutData](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [ProcessID] [bigint] NULL,
    [ParameterName] [varchar](200) NULL,
    [Data0] [varchar](2000) NULL,
    [Díata1] [varchar](2000) NULL,
    [Data2] [varchar](2000) NULL,
    [Data3] [varchar](2000) NULL,
    [Data4] [varchar](2000) NULL,
    [Data5] [varchar](2000) NULL,
    [Data6] [varchar](2000) NULL,
    [Data7] [varchar](2000) NULL,
    [Data8] [varchar](2000) NULL,
    [Data9] [varchar](2000) NULL,
    [Created] [datetime] NULL,
    [CreatedByID] [int] NULL,
    [Deleted] [tinyint] NULL
) ON [PRIMARY]

*ProcessID: Mandatory column. The system generates an identifier before calling the DataLoaderStoredProcedure and the DataSaverStoredProcedure, with which it is possible to bind the data created during the call. It can only be of BIGINT type.

*ParameterName: Mandatory table. The name of the parameter defined in the ParameterTable table (ParametersTable.Name).

*Data0..Data9: Mandatory but there can be “any number” of it. The system registers the data in these columns. If the range specified in parametersTable is covering 4 columns and 3 rows, the columns Data0..Data3 will be filed and 3 rows will be registered. The order of these is determined by the value of the id column (meaning that the rows will be going from the smaller id value to the larger). The system is prepared for using additional columns as well. The name of the added columns always has to begin with the word Data, and the order of the columns is determined by adding an integer number afterwards.

Example

CREATE TABLE [dbo].[orn_Excel](
    [id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY ,
    [TemplateExcelID] [int] NULL,
    [ParentExcelID] [int] NULL,
    [JSON] [varchar](max) NULL,
    [Created] [datetime] NULL,
    [CreatedByID] [int] NULL,
    [Deleted] [tinyint] NULL,
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[orn_ExcelTemplate](
    [id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [Name] [varchar](200) NULL,
    [Version] [int] NULL,
    [JSON] [varchar](max) NULL,
    [Created] [datetime] NULL,
    [CreatedByID] [int] NULL,
    [Deleted] [tinyint] NULL,
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[orn_ExcelTakeOutParameters](

    [id] [int] IDENTITY(1,1) NOT NULL  PRIMARY KEY,

    [TemplateExcelID] [int] NULL,
    [Name] [varchar](200) NULL,
    [Direction] [varchar](5) NULL,
    [RangeName] [varchar](200) NULL,
    [WorkSheetName] [varchar](200) NULL,
    [Absolute_Start_RowIndex] [int] NULL,
    [Absolute_Start_ColumnIndex] [int] NULL,
    [Absolute_End_RowIndex] [int] NULL,
    [Absolute_End_ColumnIndex] [int] NULL,
    [Created] [datetime] NULL,
    [CreatedByID] [int] NULL,
    [Deleted] [tinyint] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[orn_ExcelTakeOutData](
    [id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [ProcessID] [bigint] NULL,
    [ParameterName] [varchar](200) NULL,
    [Data0] [varchar](2000) NULL,
    [Data1] [varchar](2000) NULL,
    [Data2] [varchar](2000) NULL,
    [Data3] [varchar](2000) NULL,
    [Data4] [varchar](2000) NULL,
    [Data5] [varchar](2000) NULL,
    [Data6] [varchar](2000) NULL,
    [Data7] [varchar](2000) NULL,
    [Data8] [varchar](2000) NULL,
    [Data9] [varchar](2000) NULL,
    [Data10] [varchar](2000) NULL,
    [Data11] [varchar](2000) NULL,
    [Data12] [varchar](2000) NULL,
    [Data13] [varchar](2000) NULL,

    [Data14] [varchar](2000) NULL,
    [Data15] [varchar](2000) NULL,
    [Data16] [varchar](2000) NULL,
    [Data17] [varchar](2000) NULL,
    [Data18] [varchar](2000) NULL,
    [Data19] [varchar](2000) NULL,
    [Data20] [varchar](2000) NULL,
    [Data21] [varchar](2000) NULL,
    [Data22] [varchar](2000) NULL,
    [Data23] [varchar](2000) NULL,
    [Data24] [varchar](2000) NULL,
    [Data25] [varchar](2000) NULL,
    [Data26] [varchar](2000) NULL,
    [Data27] [varchar](2000) NULL,
    [Data28] [varchar](2000) NULL,
    [Data29] [varchar](2000) NULL,
    [Data30] [varchar](2000) NULL,
    [Data31] [varchar](2000) NULL,
    [Data32] [varchar](2000) NULL,
    [Data33] [varchar](2000) NULL,
    [Data34] [varchar](2000) NULL,
    [Data35] [varchar](2000) NULL,
    [Data36] [varchar](2000) NULL,
    [Data37] [varchar](2000) NULL,
    [Data38] [varchar](2000) NULL,
    [Data39] [varchar](2000) NULL,
    [Created] [datetime] NULL,
    [CreatedByID] [int] NULL,
    [Deleted] [tinyint] NULL
) ON [PRIMARY]
GO

ELFHQ.xml

<?xml version="1.0" encoding="ISO-8859-2"?>
<ExcelLikeForm>
    <Caption />

    <DataDefinition>DataDefinitionHQExcel</DataDefinition>

    <BusinessObject>BusinessObjectExcel</BusinessObject>

    <SpreadJSONColumn>JSON</SpreadJSONColumn>    
    <IDColumn>id</IDColumn>
    <TemplateIDColumn>TemplateExcelID</TemplateIDColumn>
    <DataExchangeSettings>

        <DataLoaderStoredProcedure>osp_wrk_ExcelTakeOut_CollectData</DataLoaderStoredProcedure>
        <DataSaverStoredProcedure>osp_wrk_ExcelTakeOut_DistributeData</DataSaverStoredProcedure>
        <ParametersTable>orn_ExcelTakeOutParameters</DataParametersTable>
        <ValuesTable>orn_ExcelTakeOutData</ValuesTable>
    </DataExchangeSettings>
</ExcelLikeForm>

BusinessObjectExcel.xml

<?xml version="1.0" encoding="iso-8859-2"?>

<BusinessObject>
    <DataTable>orn_Excel</DataTable>
    <UniqueIDColumn>id</UniqueIDColumn>
    <Fields>
        <Field name="id" />
        <Field name="JSON" disableLogging=”true” />
        <Field name="Created" />
        <Field name="CreatedByID" />
        <Field name="TemplateExcelID" />
    </Fields>
    <Rights>
        <Modify type="Constant" return="boolean" default="false">true</Modify>
        <View type="Constant" return="boolean" default="false">true</View>
    </Rights>
</BusinessObject>

Stored procedures:

CREATE PROCEDURE [dbo].[osp_wrk_ExcelTakeOut_CollectData] (
    @ProcessID BIGINT,
    @ExcelID INT,
    @TemplateExcelID INT
)
    AS BEGIN

    IF @TemplateExcelID = 7 BEGIN
        DECLARE
            @BusinessYear DATETIME,
            @BY0 VARCHAR(20),
            @Year INT

        SET @BusinessYear = GETDATE()
        SET @Year = YEAR(@BusinessYear) - 2000
        IF MONTH(@BusinessYear) < 7 BEGIN
            SET @BY0 = '20' + CAST(@Year - 1 AS VARCHAR(20)) + '_' + CAST(@Year AS VARCHAR(20))
        END ELSE BEGIN
            SET @BY0 = '20' + CAST(@Year AS VARCHAR(20)) + '_' + CAST(@Year + 1 AS VARCHAR(20))
        END

        INSERT INTO orn_ExcelTakeOutData (ProcessID, ParameterName, Data0)
        SELECT @ProcessID, 'BusinessYear0', @BY0

        DECLARE @Forecast TABLE (
            ForecastID INT,
            BDThemeID INT,
            RefLaunch DATETIME
        )

        INSERT INTO @Forecast (ForecastID, BDThemeID, RefLaunch)
        SELECT f.ForecastID, f.BDThemeID, t.RefLaunch
        FROM orn_Forecast f WITH(NOLOCK)
        INNER JOIN orn_BDTheme t WITH(NOLOCK) ON t.BDThemeID = f.BDThemeID
        WHERE f.ExcelID = @ExcelID

        DECLARE
            @YDiff INT,
            @MDiff INT,
            @L0Index INT,
            @id INT

        SELECT TOP 1 @YDiff = DATEDIFF(month, GETDATE(), RefLaunch) / 12.0, @MDiff = DATEDIFF(month, GETDATE(), RefLaunch) % 12
        FROM @Forecast

        SET @L0Index = 3 + (@YDiff + CASE WHEN @MDiff < -6 THEN -1 WHEN  @MDiff > 6 THEN 1 ELSE 0 END)

        IF @L0Index<0 BEGIN
            RAISERROR (15600,-1,-1, 'Reference Date is Smaller!');
        END

        INSERT INTO orn_ExcelTakeOutData (ProcessID, ParameterName)
        SELECT  @ProcessID, 'LaunchYear'

        SET @id = SCOPE_IDENTITY();

        DECLARE
            @sql VARCHAR(max);

        DECLARE
            @i INT = 0

        WHILE @i<26 BEGIN
            SET @sql = 'UPDATE orn_ExcelTakeOutData SET Data'+CAST(@i AS varchar(20))+'=''L' + CAST(((0 - @L0Index) + @i) AS VARCHAR(20)) + ''' WHERE id = ' + CAST(@id AS varchar(20))
            EXECUTE (@sql)
            SET @i = @i + 1
        END
    END
END

CREATE PROCEDURE [dbo].[osp_wrk_ExcelTakeOut_DistributeData] (
    @ProcessID BIGINT,
    @ExcelID INT,
    @TemplateExcelID INT
)
AS BEGIN
    IF @TemplateExcelID = 7 BEGIN
        DECLARE
            @ForecastID INT

        SELECT @ForecastID = ForecastID
        FROM orn_Forecast
        WHERE ExcelID = @ExcelID

        DECLARE
            @ParamID INT,
            @ParamName VARCHAR(200)

        DECLARE cur_parameters CURSOR
        READ_ONLY FOR         
        SELECT id, Name
        FROM orn_ExcelTakeOutParameters WITH(NOLOCK)
        WHERE Deleted = 0 AND TemplateExcelID = @TemplateExcelID
            AND Direction IN ('INOUT', 'OUT')

        OPEN cur_parameters
        FETCH NEXT FROM cur_parameters INTO @ParamID, @ParamName
        WHILE @@FETCH_STATUS = 0 BEGIN
            IF @ParamName = 'TH UNIT' BEGIN
                DELETE FROM orn_Temp_THUNIT WHERE ForecastID = @ForecastID

                INSERT INTO orn_Temp_THUNIT (ForecastID, Yminus3, Yminus2, Yminus1)
                SELECT @ForecastID, CAST(Data0 AS FLOAT), CAST(Data1 AS FLOAT), CAST(Data2 AS FLOAT)
                FROM orn_ExcelTakeOutData
                WHERE ProcessID = @ProcessID
                ORDER BY id ASC
            END
            FETCH NEXT FROM cur_parameters INTO @ParamID, @ParamName    
        END
        CLOSE cur_parameters
    END
END

  • Last update: 26 weeks 4 days ago
  • Effector