We have multiple options to provide additional information to records in grids. Some of them, like display methods, are easy to implement. Some of them, like a temporary table, are not.
Let's consider a scenario when our lovely user wants to see reservation info directly in the transaction grid as filterable columns.
Evidently, we cannot use display methods because they cannot be filterable. Given the way this info calculated behind the curtain, there is no way to create a view to join
InventTrans on
InventTransItem form. So, the last feasible option is to create a temporary table and join it to the main form data source.
In order to populate this TempDB table (it is not enough to create an InMemory one) as much fast as possible, we have to run the logic in CIL.
The following shows how to work with such tables by passing their physical names.
First, create a TempDB table.
Then add it to the form data source so that it would be outer joined to
InventTrans table
and place its
Init() method, which links the local buffer with a populated TempDB on the SQL server side.
We also need to init its populating engine and calculate its data at least once when the form opens and maybe later via
Refresh button for all or one particular item.
final
class FormRun extends ObjectRun
{
wblInventSumFormEngine wblInventSumFormEngine;
}
void init()
{
super();
element
.wblInitParameters();
}
private void wblInitParameters()
{
wblInventSumFormEngine
= new wblInventSumFormEngine(element);
}
//wblTmpInventSum_DS
.Init
public void init()
{
wblTmpInventSum wblTmpInventSumLoc;
wblTmpInventSumLoc
.doInsert();
delete_from wblTmpInventSumLoc;
super();
wblTmpInventSum
.linkPhysicalTableInstance(wblTmpInventSumLoc);
}
public void run()
{
super();
element
.wblRecalcData();
}
public void wblRecalcData(boolean _forCurrentItemOnly
= false)
{
wblInvenTransByItemDimView wblInvenTransByItemDimView;
if(_forCurrentItemOnly)
{
wblInventSumFormEngine
.parmItemIdCalcFor(InventTrans
.ItemId);
wblInventSumFormEngine
.run();
}
else
{
wblInventSumFormEngine
.parmItemIdCalcFor('');
// todo: uncomment
if needed to measure the execution time
//wblInventSumFormEngine
.parmShowCalcInfo(true);
startLengthyOperation();
wblInventSumFormEngine
.run();
endLengthyOperation();
}
}
Now let's take a look at the populating class. It has parameters for showing elapsed time and run for a particular item id only.
///
/// Populating TempDB tables
in CIL
///
///
/// Passing physical table names between CIL
and form
///
public
class wblInventSumFormEngine
{
// add more data sources to support multiple TempDB tables populating
FormDataSource fdsTmpInventSum;
wblTmpInventSum wblTmpInventSum;
ItemId itemIdCalcFor;
boolean showCalcInfo;
}
///
/// Gets TempDB tables physical names
///
///
/// Container
with names
///
///
/// Creates buffers
if not exist yet
///
private container getTempTablesNames()
{
if(
!wblTmpInventSum
.getPhysicalTableName())
{
select firstOnly wblTmpInventSum;
}
return [wblTmpInventSum
.getPhysicalTableName()];
}
///
/// Creates new populating engine
for a given form
///
///
/// Caller form
///
///
/// Looks
for a particular data sources to link TempDB tables
///
public void new(FormRun _formRun
= null)
{
Object formObject
= _formRun;
if (_formRun)
{
fdsTmpInventSum
= _formRun
.dataSource(tableId2Name(tableNum(wblTmpInventSum)));
}
}
///
/// To calculate TempDB table
for a particular item only
///
///
/// Item
id to calculate data
///
///
/// Item
id
///
///
/// If empty, then calculate
for all items
///
public ItemId parmItemIdCalcFor(ItemId _parm
= itemIdCalcFor)
{
itemIdCalcFor
= _parm;
return itemIdCalcFor;
}
///
/// Show consumed time to populate TempDB tables
///
///
/// Shows
if True
///
public boolean parmShowCalcInfo(boolean _parm
= showCalcInfo)
{
showCalcInfo
= _parm;
return showCalcInfo;
}
public void run()
{
str wblTmpInventSumTempDBTableName;
// add here more tempDB tables
if needed
[wblTmpInventSumTempDBTableName]
= this
.getTempTablesNames();
[wblTmpInventSumTempDBTableName]
= wblInventSumFormEngine::calc([itemIdCalcFor, wblTmpInventSumTempDBTableName, showCalcInfo]);
// push calculated data back to the form data source
if(fdsTmpInventSum)
{
// this assignment works only on the client tier outside of the populating method
fdsTmpInventSum
.cursor()
.linkPhysicalTableInstance(wblTmpInventSum);
fdsTmpInventSum
.research();
}
}
///
/// Launcher
for CIL
///
///
/// Container
with parameters
and all TempDB tables physical names to populate
///
///
/// Container
with all populated TempDB tables physical names
///
private static server container calc(container _cont)
{
container cont;
XppILExecutePermission xppILExecutePermission;
FromTime startTime
= timeNow();
ItemId itemIdCalcFor
= conPeek(_cont,
1);
boolean showCalcInfo
= conPeek(_cont,
3);
xppILExecutePermission
= new XppILExecutePermission();
xppILExecutePermission
.assert();
cont
= runClassMethodIL(
classStr(wblInventSumFormEngine),
staticMethodStr(wblInventSumFormEngine, calcIL),
_cont
);
CodeAccessPermission::revertAssert();
if(showCalcInfo)
{
info(strFmt("Refreshed for %1 in %2", itemIdCalcFor
? itemIdCalcFor : "all items", timeConsumed(startTime, timeNow())));
}
return cont;
}
///
/// Implements the calculating logic
and populating TempDB tables
///
///
/// Container
with parameters
and all TempDB tables physical names to populate
///
///
/// Container
with all populated TempDB tables physical names
private static server container calcIL(container _con)
{
WHSInventReserve whsInventReserve;
wblTmpInventSum wblTmpInventSum;
ItemId itemIdCalcFor
= conPeek(_con,
1);
str wblTmpInventSumTempDBTableName
= conPeek(_con,
2);
wblInvenTransByItemDimView wblInvenTransByItemDimView;
// Link to an exiting table
wblTmpInventSum
.useExistingTempDBTable(wblTmpInventSumTempDBTableName);
// empty existing data
wblInventSumFormEngine::cleanTmpInventSum(wblTmpInventSum, itemIdCalcFor);
/***** Implements the calculating logic here
*****/
while select whsInventReserve
join RecId
from wblInvenTransByItemDimView
where
(
!itemIdCalcFor
|| whsInventReserve
.ItemId
== itemIdCalcFor)
&&
wblInvenTransByItemDimView
.ItemId
== whsInventReserve
.ItemId
&&
wblInvenTransByItemDimView
.InventDimId
== whsInventReserve
.InventDimId
{
wblTmpInventSum
.clear();
wblTmpInventSum
.Itemid
= whsInventReserve
.itemId;
wblTmpInventSum
.InventDimId
= whsInventReserve
.InventDimId;
wblTmpInventSum
.AvailOrdered
= whsInventReserve
.AvailOrdered;
wblTmpInventSum
.AvailPhysical
= min(whsInventReserve
.displayPhysAvailUpHierarchy(), whsInventReserve
.AvailPhysical);
// here you can add
any other method calls to get more info about reservation
wblTmpInventSum
.AvailReservDelta
= wblTmpInventSum
.AvailPhysical
- wblTmpInventSum
.AvailOrdered;
wblTmpInventSum
.insert();
}
// send tables physical names back to the form
return [wblTmpInventSum
.getPhysicalTableName()];
}
///
/// Empties the whole table
or for given item
id only
///
///
/// TempDB table
buffer to empty
///
///
/// Item
id
///
static private void cleanTmpInventSum(wblTmpInventSum _wblTmpInventSum, ItemId _itemIdCalcFor
= '')
{
delete_from _wblTmpInventSum
where
(
!_itemIdCalcFor
|| _wblTmpInventSum
.ItemId
== _itemIdCalcFor);
}
As you can see the tricky point is to provide tables physical names to CIL and back in
calc() and
calcIL() methods.
Now you get it.
Do not forget to build CIL and check it in your Development user options.