Commission Calculation with Table-Valued Functions

// Write test tables into database externaldb for Commission Calculation
// Create your own database, and update the connection string before you run this function
public static void WriteTablesToExternalDatabase()
{
	DataStore dstore = null;
 
	try
	{
		// Call helper function to create meta data & test tables
		MetaData md;
		MatrixTable SalesTable, ComScaleTable, ScalePoolTable, PoolTable;
 
		Create_Sales_And_Commission_Tables(out md, out SalesTable, out ComScaleTable, out ScalePoolTable, out PoolTable);
 
		// write test tables to database externaldb
 
		// Create your database & update the connection strings below with your values on your computer 
		// you can use a MS SQL or MySQL database
 
		// MS SQL SqlClient
		string connStr1 = @"Data Source=(local)\SQLEXPRESS;Initial Catalog=externaldb;Integrated Security=True;Connect Timeout=15;Encrypt=False;TrustServerCertificate=False";
		dstore = new DataStore("System.Data.Odbc");
 
		// MySQL ODBC
		// string connStr2 = @"Driver={MySQL ODBC 5.2w Driver};Server=127.0.0.1;Database=externaldb;uid=root;pwd=Tosun27";
		// dstore = new DataStore("System.Data.SqlClient");
 
		// open database connection
		dstore.OpenConnection(connStr1);
 
		// row partition sales table
		// NumVector RowInd = NumVector.CreateSequenceVector(0, 1, 500);
		// SalesTable = SalesTable.PartitionRow(RowInd);
 
		// first delete existing tables in external database
		try
		{
			dstore.DropDatabaseTable("sales");
		}
		catch { };
		try
		{
			dstore.DropDatabaseTable("commission_scales");
		}
		catch { };
		try
		{
			dstore.DropDatabaseTable("scale_pool");
		}
		catch { };
		try
		{
			dstore.DropDatabaseTable("pool");
		}
		catch { };
 
		// Sales Table
		DataTable dt = SalesTable.ToDataTable();
		dt.TableName = "sales";
		dstore.WriteTable(dt, md);
 
		// ComScaleTable
		dt = ComScaleTable.ToDataTable();
		dt.TableName = "commission_scales";
		dstore.WriteTable(dt, md);
 
		// ScalePoolTable
		dt = ScalePoolTable.ToDataTable();
		dt.TableName = "scale_pool";
		dstore.WriteTable(dt, md);
 
		// PoolTable
		dt = PoolTable.ToDataTable();
		dt.TableName = "pool";
		dstore.WriteTable(dt, md);
	}
	catch (Exception ex)
	{
		System.Diagnostics.Debug.WriteLine("ERROR in WriteTablesToExternalDatabase: " + ex.Message);
	}
	finally
	{
		// close database connection
		dstore.CloseConnection();
	}
}

// Calculate Commissions with Table Functions without database connection
public static void CalculateCommissions()
{
	// calculation parameters
	string period = "quarter";  // or monthly
 
	// create meta data & test tables
	MetaData md;
	MatrixTable SalesTable, ComScaleTable, ScalePoolTable, PoolTable;
 
	Create_Sales_And_Commission_Tables(out md, out SalesTable, out ComScaleTable, out ScalePoolTable, out PoolTable);
 
	// define date-related  attributes
	md.AddNewField("year", FieldType.IntegerAttribute);
	md.AddNewField("quarter", FieldType.IntegerAttribute);
	md.AddNewField("month", FieldType.IntegerAttribute);
 
	// define required key figures
	md.AddNewField("pooled_sales", FieldType.KeyFigure);
	md.AddNewField("interval_rate", FieldType.KeyFigure);
	md.AddNewField("effective_rate", FieldType.KeyFigure);
	md.AddNewField("commission", FieldType.KeyFigure);
 
	// add date-related attributes to SalesTable
 
	// add year to table
	MatrixTable SalesTableWithYear = MatrixTable.InsertDateRelatedAttribute(SalesTable, "date", "year", DateRelation.Year);
 
	// add quarter to table
	MatrixTable SalesTableWithQuarter = MatrixTable.InsertDateRelatedAttribute(SalesTableWithYear, "date", "quarter", DateRelation.Quarter);
	TextVector RefAttrib = TextVector.CreateVectorWithElements("dealer", "quarter", "year", "pool_id");
 
	// view table
	MatrixTable.View_MatrixTable(SalesTableWithQuarter, "SalesTableWithQuarter");
 
	// add attribute pool_id to table
	MatrixTable SalesTableWithPool = MatrixTable.CombineTables(SalesTableWithQuarter, PoolTable, 
		JokerMatchesAllvalues: true);
 
	// view table
	MatrixTable.View_MatrixTable(SalesTableWithPool, "SalesTableWithPool");
 
	// aggregate sales table w.r.t. pool
	TextVector SubTableFields = TextVector.CreateVectorWithElements("dealer", "year", period, "pool_id", "sales");
	SalesTableWithPool = SalesTableWithPool.PartitionColumn(SubTableFields);
 
	MatrixTable AggregatedSalesTableWithPool = SalesTableWithPool.AggregateAllKeyFigures(null);
 
	// view table
	MatrixTable.View_MatrixTable(AggregatedSalesTableWithPool, "AggregatedSalesTableWithPool");
 
	// add attributes scale_id & scale_logic to AggregatedSalesTableWithPool
	MatrixTable AggregatedSalesWithScale = MatrixTable.CombineTables(AggregatedSalesTableWithPool, ScalePoolTable,
		JokerMatchesAllvalues: true);
 
	// view table
	MatrixTable.View_MatrixTable(AggregatedSalesWithScale, "AggregatedSalesWithScale");
 
	// get scale table for each scale_id
	SubTableFields = TextVector.CreateVectorWithElements("lower_limit", "commission_rate");
	MatrixTable UniqueScaleIds;
	MatrixTable[] ScaleTables;
	MatrixTable.GetSubTables(ComScaleTable, SubTableFields, out UniqueScaleIds, out ScaleTables);
 
	// Table array indexed with a dictionary (assoc array indexed with scale_id)
	var ScaleTableDic = new Dictionary<int, MatrixTable>();
 
	for (int i = 0; i < UniqueScaleIds.RowCount; i++)
	{
		ScaleTableDic[(int)UniqueScaleIds.GetFieldValue("scale_id", i)] = ScaleTables[i];
	}
 
	// insert new key figures to table
	AggregatedSalesWithScale = MatrixTable.InsertNewColumn(AggregatedSalesWithScale, "interval_rate", 0.0);
	AggregatedSalesWithScale = MatrixTable.InsertNewColumn(AggregatedSalesWithScale, "effective_rate", 0.0);
	AggregatedSalesWithScale = MatrixTable.InsertNewColumn(AggregatedSalesWithScale, "commission", 0.0);
 
	// row-by-row processing of table for calculating commission rates
	var TextAttribDic = new Dictionary<string, string>();
	var NumAttribDic = new Dictionary<string, int>();
	var KeyFigDic = new Dictionary<string, double>();
 
	MatrixTable CommissionsPerPool = MatrixTable.TransformRowsDic(AggregatedSalesWithScale, 
		CalCommissionForEachRow, ScaleTableDic);
 
	// view table
	MatrixTable.View_MatrixTable(CommissionsPerPool, "CommissionsPerPool");
 
	// calculate total commission per dealer for each period (quarter or month)
	SubTableFields = TextVector.CreateVectorWithElements("dealer", "year", period, "sales", "commission");
	MatrixTable CommissionsPerDealer = CommissionsPerPool.PartitionColumn(SubTableFields);
	CommissionsPerDealer = CommissionsPerDealer.AggregateAllKeyFigures(null);
 
	// round sales & commission amounts to two digits after decimal point
	CommissionsPerDealer = MatrixTable.Round(CommissionsPerDealer, 2);
 
	// view table
	MatrixTable.View_MatrixTable(CommissionsPerDealer, "CommissionsPerDealer");
 
	// round commission amounts
	MatrixTable.Round(CommissionsPerDealer, 2);
}

// define meta data
var md = MetaData.CreateEmptyMetaData();
 
// attributes
MetaData.AddNewField(md, "category", FieldType.TextAttribute);
MetaData.AddNewField(md, "product", FieldType.TextAttribute);
MetaData.AddNewField(md, "dealer", FieldType.TextAttribute);
MetaData.AddNewField(md, "date", FieldType.DateAttribute);
MetaData.AddNewField(md, "scale_id", FieldType.IntegerAttribute);   // commission scale id
MetaData.AddNewField(md, "pool_id", FieldType.IntegerAttribute);
MetaData.AddNewField(md, "scale_logic", FieldType.TextAttribute);   // level or class
 
// date-related  attributes
MetaData.AddNewField(md, "year", FieldType.IntegerAttribute);
MetaData.AddNewField(md, "quarter", FieldType.IntegerAttribute);
MetaData.AddNewField(md, "month", FieldType.IntegerAttribute);
 
// key figures
MetaData.AddNewField(md, "lower_limit", FieldType.KeyFigure);
MetaData.AddNewField(md, "commission_rate", FieldType.KeyFigure);
MetaData.AddNewField(md, "sales", FieldType.KeyFigure);
MetaData.AddNewField(md, "interval_rate", FieldType.KeyFigure);
MetaData.AddNewField(md, "effective_rate", FieldType.KeyFigure);
MetaData.AddNewField(md, "commission", FieldType.KeyFigure);