"Database Optimization: Enhancing Data with a New Column by Checking for the Presence of a Letter in the Tag Name"

This code is designed to check if the tag name includes specific values. Depending on these values, a new column is created, and relevant data is added.

For instance, if the tag name is "/PLC_MA/BM1004_TotalWeigth," it checks if it has "BM10......" in it. If it does, the "/PLC-MA/" part is removed. If this condition is met, a new column named "ID_VALUE" is made, and it gets the value 250. This helps identify data with "BM10" in their tag name. You can also set up additional conditions based on the tag name for specific actions.

for(var i = 0; i<Math.ceil($.input.length/MAX_DATASET_SIZE); ++i){
//Create a new dataset for the current slice of the array
const ds = new Dataset();
ds.addColumn("tag", "NVARCHAR");
ds.addColumn("id_value", "DOUBLE"); // creacion de la columna en el dataset
ds.addColumn("number_value", "DOUBLE");
ds.addColumn("string_value", "NVARCHAR");
ds.addColumn("bool_value", "SMALLINT");
ds.addColumn("quality", "SMALLINT");
ds.addColumn("ts", "DATETIME");
//Iterate through the current slice to add the necessary rows to the dataset
for(let j = MAX_DATASET_SIZE*i; j<Math.min(MAX_DATASET_SIZE*(i+1), $.input.length); ++j){
const ev = $.input[j];
//Creates the row initializing the value of the event to null
const row = {
tag: ev.tag,
number_value: null, // creacion del evento
id_value: null,
string_value: null,
bool_value: null,
quality: ev.quality,
ts: ev.ts
    }

//********************************************************* creacion del query *****************************************//

function _createTableSqlServer(){
const query = sprintf("IF OBJECT_ID(N'%1$s', N'U') IS NULL BEGIN\
CREATE TABLE %1$s (\
\"id\" INT PRIMARY KEY IDENTITY(0, 1),\
id_value FLOAT,\
tag NVARCHAR(255) NOT NULL,\
number_value FLOAT,\
quality SMALLINT NOT NULL,\
ts DATETIME2 NOT NULL\
)\
END;\r\n", _getTableName())
return query;
}



function _insertDataSqlServer(){
const values = [];
for(const row of $.input){
let rowValues = "(";


//********************************************************************* comprobacion de nombre *********************************************************************//
var nombre = row.getValue("tag");
let valor;
switch(true){
case nombre.includes("BM10"):
$.logger.debug("hello!! 1 ");
valor = "250";
break;

case nombre.includes("BM24"):
valor = "500";
$.logger.debug("hello!! 2 ");
break;
}
//********************************************************************* comprobacion de nombre *********************************************************************//

//******************************************************************** funcion corregir nombre *********************************************************************//


var nombreCorregido = nombre.replace("/PLC_MA/","");
$.logger.debug("El valor es: " + valor);
$.logger.debug("El nombre corregido es: " + nombreCorregido);

//******************************************************************** funcion corregir nombre *********************************************************************//

rowValues += valor + ",";
rowValues += sprintf("'%s',", nombreCorregido);
rowValues += row.getValue("number_value")+",";
rowValues += row.getValue("quality")+",";
//Timestamp is created using the ISO String representation of the date in the ts column of the dataset
rowValues+= sprintf("'%s'", row.getValue("ts").toISOString());
rowValues += ")";
values.push(rowValues);
}
const query = sprintf("INSERT INTO %s (id_value, tag, number_value, quality, ts) VALUES\r\n%s;\r\n", _getTableName(), values.join(",\r\n"));
return query;
}

"Created by JF Solano"