Script for total price calculation

In some cases we need to calculate a value based on values in other columns or in other records.

Example: Total server price

In the below example we have class server with several basic configuration parameters (vm_cpu, vm_ram a vm_hdd) and reference to a pricelist, that contains unit price for these parameters. We need to ensure that the column total_price contains the total server price according to its configuration and defined pricelist (the pricelist reflects e.g. various service level that we are providing to the server).

We have to ensure that the total price of the server is calculated and stored in column total_price when a (new or existing) record is saved. We will create a rule of type Script Before record saving.

Where to use this script: Class rule

var drPL = OGActualDataRow.GetDR('pricelist');
if ( drPL != null)
{
  var vm_cpu = OGActualDataRow['vm_cpu'];
  var vm_ram = OGActualDataRow['vm_ram'];
  var vm_hdd = OGActualDataRow['vm_hdd'];

  var pl_cpu = drPL['price_cpu'];
  var pl_ram = drPL['price_ram'];
  var pl_hdd = drPL['price_hdd'];

  if ( vm_cpu == null) vm_cpu = 0;
  if ( vm_ram == null) vm_ram = 0;
  if ( vm_hdd == null) vm_hdd = 0;

  if ( pl_cpu == null) pl_cpu = 0;
  if ( pl_ram == null) pl_ram = 0;
  if ( pl_hdd == null) pl_hdd = 0;

  OGActualDataRow['total_price'] = vm_cpu * pl_cpu + vm_ram * pl_ram + vm_hdd * pl_hdd;
}
else
{
  OGActualDataRow['total_price'] = null;
}

Rule from the above example updates the record before its saving from the web form. When we update the data in a mass way, we have to use SQL update.

 Where to use this script:  Import - Script after import end

var m = OG.Model.GetByCode('it');
var clP = m.ClassDefs['server'];
var clPL = m.ClassDefs['pricelist'];

var colPvmcpu = clP.Columns['vm_cpu'];
var colPvmhdd = clP.Columns['vm_hdd'];
var colPvmram = clP.Columns['vm_ram'];
var colPtotal = clP.Columns['total_price'];
var colPprice = clP.Columns['pricelist'];

var colPLpricecpu = clPL.Columns['price_cpu'];
var colPLpricehdd = clPL.Columns['price_hdd'];
var colPLpriceram = clPL.Columns['price_ram'];

var sql = 'update ' + clP.DBTableName +
' set ' + colPtotal.DBColumnName + ' = isnull(p.' + colPvmcpu.DBColumnName + ',0)*isnull(pl.' + colPLpricecpu.DBColumnName + ',0) + isnull(p.' + colPvmram.DBColumnName + ',0)*isnull(pl.' + colPLpriceram.DBColumnName + ',0) +
isnull(p.' + colPvmhdd.DBColumnName + ',0)*isnull(pl.' + colPLpricehdd.DBColumnName + ',0)' +
' from ' + clPL.DBTableName + ' pl join ' + clP.DBTableName + ' p on p.' + colPprice.DBColumnName + ' = pl.id';
OG.Sql.RunSql(sql);