//Operator D4WindowforForm is used in the 'before insert' event of the
//WindowExampleTableView view when the Operator value choosen is 'D4Window'.
//It creates detail (summary) rows in table D4WindowSummaryforForm.
//Operator D4WindowforForm is an efficient conceptualization of the sql window.
//When appropriate there is reuse of information (rows) from one current row
//to another. Based on the aggregate definition the operator stores rows to be
//summarized in table LWindowTable. When the current row changes information
//from the prior (current row) in table LWindowTable is used to summarize the new
//current row. The summary of LWindowTable is stored in table D4WindowSummaryforForm
//as the detail rows. This operator is invoked in the PopulateD4WindowSummaryforForm
//operator.
create operator D4WindowforForm
(
LTable:typeof(Stocks), //Input table (expression/variable)
//Any table with columns:
//Stock:String, QTime:DateTime, Quote:Integer
ID:Integer, //Pass in the ID for this table.
//Since we know the aggregate group defintion is valid because of
//the constraints on Def we are just passing the DefFrom and DefTo values
//that have already been computed from WindowExampleTableView.
WListRow:row{Wfrom:Integer,Wto:Integer},
Scan:Boolean //Insert count of inserts/deletes on
//LWindowTable in global table GProfile.
):typeof(D4WindowSummaryforForm)
begin
//var WListRow:typeof(WList('Aggregate Group'));//Row from WList, WTable (window definition),
//ie. row{Wfrom:Integer,Wto:Integer}.
var LFirstWindowInsert:Boolean; //Test for 1st LWindowTable loop (Loop 3).
var LReUseWindowStart:Integer;// Start row (Rank) of LWindowTable data to
// reuse for next row.
var LReUseWindowEnd :Integer;//End of row (Rank) of LWindowTable data to
//to reuse for next row.
var LWindowTable := table of {WRownum:Integer,WQuote:Integer} { };
//result := table of typeof(result) { };
var LWindowFrom :Integer;//Aggregate Group definition from value.
var LWindowTo :Integer;//Aggregate Group definition to value.
var LStock :String; //Stock for current (stock) partition.
var LWQuote :Integer;//Quote from cursor to be inserted into LWindowTable.
var LStockCount :Integer;//Row count for current Stock partition.
var LRank :Integer:=0;//Rank/counter over the entire result table.
var LWindowStartRow :Integer;//Lower bound for rows in window for current Stock row.
var LWindowEndRow :Integer;//Upper bound for rows in window for current Stock row.
var LWindowTableInserts :Integer:=0; //Counter for LWindowTable inserts.
var LWindowTableDeletes :Integer:=0; //Counter for the number of times an
//LWindowTable delete was performed.
var LWNumsDeleted :Integer:=0; //Counter for total number of rows deleted from
//LWindowTable.
var LStockCursor:=cursor(table of typeof(LTable add {666 QTimeRank}) { } );
var LRowAllColumns:=row of typeof(LStockCursor.Select()) { };
var LRowQTimeQuote:=row of typeof(LRowAllColumns over{QTime,Quote}) { };
//If there's no rows in LTable exit operator.
if not (exists(LTable)) then
begin
raise Error("Hey, there's no rows in the table operand/parameter (LTable), try again");
exit;
end;
//Assign row from WList/WTable (Wfrom and Wto) to window values.
//Assign row values from WListRow.
LWindowFrom:= WListRow.Wfrom;
LWindowTo := WListRow.Wto;
//
// Loop 1: Most outer loop, for Stock partition.
//
var LListStock:typeof(ToList(cursor(LTable over {Stock} order by {Stock})));
LListStock:=ToList(cursor(LTable {Stock} order by {Stock}));
//
foreach var LItem in LListStock do
begin
try
LStock:=LItem.Stock;
//
//Use operator WCursor to return cursor for the current stock (LStoc).
//Using default ascending sort for QTime and Quote. Quote is used just
//to break ties in QTime.
//
LStockCursor:= WCursor(LTable,LStock,'A');
//
//Get the count of each partition (Stock).
LStockCount:=Count(LTable where Stock=LStock);
//
// Loop 2: Create a window for each row in current Stock (Uses (I)).
//
LFirstWindowInsert:=true; //Test for 1st insert into LWindowTable for each Stock (I).
//Have to initially set LReUseWindowEnd:-1 for beginning of
//each Stock so initial
//'if not (LWindowEndRow=LReUseWindowEnd)LReUseWindowEnd) then'
//will be true. If LReUseWindowEnd is nil statement evaluates to false.
LReUseWindowEnd:=-10000;
//Delete any data in LWindowTable from previous Stock (I).
//LWindowTable must be empty at the start of a new partition.
if exists(LWindowTable where WRownum>0) then
begin
if Scan then
begin
LWindowTableDeletes:=LWindowTableDeletes + 1;
LWNumsDeleted:= LWNumsDeleted +
( Count(LWindowTable where WRownum>0) );
end;
delete LWindowTable where WRownum>0 ;
end;
LReUseWindowStart:=nil;
for I:Integer:= 1 to LStockCount do
begin
LRank:= LRank+1; //Overall rank/counter.
//Do a Findkey to get the current row.
if LStockCursor.FindKey(row {I QTimeRank} ) then
begin
LRowAllColumns:=LStockCursor.Select() ; //All columns in row.
LRowQTimeQuote:=LRowAllColumns over{QTime,Quote}; //A row of QTime and Quote.
end
else
raise Error
( 'Error for Stock: ' + LStock + ' | Row # not found: '+ToString(I) +
' | Prior QTime: '+IfNil(ToString(LRowQTimeQuote.QTime),'no prev. QTime') +
' | Prior Quote: '+IfNil(ToString(LRowQTimeQuote.Quote),'no prev. Quote') );
//
// Loop 3: Create a window for current (I).
//
LWindowStartRow:= I + LWindowFrom ; //Start of window.
LWindowEndRow := I + LWindowTo ; //End of window.
// Loop 3 create window data (LWindowTable) for current row (I).
// For the 1st insert we are not concerned about previous LWindowTable data.
//
if
(
not
(
row{LWindowStartRow>LStockCount LWStart_gt_StockCount,
LWindowEndRow>LStockCount LWEnd_gt_StockCount}
=
row{true LWStart_gt_StockCount,true LWEnd_gt_StockCount}
)
and
not
(
row{LWindowStartRow<1 LWStart_lt_0,LWindowEndRow<1 LWEnd_lt_0}
=
row{true LWStart_lt_0,true LWEnd_lt_0}
)
)
then
begin //Begin *CONDITION TEST 1* true, valid values.
//Populate LWindowTable here since there are some valid rows in StockCursor.
if LWindowEndRow>LStockCount then
LWindowEndRow:=LStockCount;
if LWindowStartRow<1 then
LWindowStartRow:=1;
// Find the rows in the cursor to form the window.
if LFirstWindowInsert=false then
LWindowStartRow:=LWindowEndRow ;
if not (LWindowEndRow=LReUseWindowEnd) then
begin
for J:Integer:= LWindowStartRow to LWindowEndRow do
begin
if LStockCursor.FindKey(row {J QTimeRank} ) then
begin
LWindowTableInserts:=LWindowTableInserts+1;
LWQuote:=LStockCursor.Select().Quote; //Quote value from cursor row.
//J is now LWindowTable row number.
insert row{J WRownum,LWQuote WQuote} into LWindowTable ;
end; //ends a successful FindKey
end; //Ends Loop 3 for window (J).
end; //Ends LWindowEndRow not equal to LReUseWindowEnd.
LFirstWindowInsert:=false;
end ; //ends successful Condition test 1 on current row
//LFirstWindowInsert set to false since we have the 1st insert into WindowTable.
//This is 1st successful insert into LWindowTable.
// Insert summarized window data into result.
insert
(
LWindowTable group
add{
Count() WCnt,Sum(WQuote) SumQte,Avg(WQuote) AvgQte,
Max(WQuote) MaxQte,Min(WQuote) MinQte
}
redefine {AvgQte:=Round(AvgQte,1)}
add{
LRank Rank,LStock Stock,I PRank,
LRowQTimeQuote.QTime QTime,
LRowQTimeQuote.Quote Quote,
ID IDNO
}
)
into D4WindowSummaryforForm;
//Store start and end of LWindowTable data that can be reused for next row (Stock).
if LFirstWindowInsert=false then
begin
LReUseWindowStart:= ((I+1) +LWindowFrom);
LReUseWindowEnd:=(I + LWindowTo);
if LReUseWindowEnd>LStockCount then
LReUseWindowEnd:=LStockCount;
if LReUseWindowStart<1 then
LReUseWindowStart:=1;
end;
//After summarizing we want to delete data in LWindowTable that cannot
//be used to summarize the next Stock (I) summary.
//But we don't have to delete for the last row in the current stock.
//
if I<LStockCount then
begin
if exists(LWindowTable where WRownum<LReUseWindowStart) then
begin
if Scan then
begin
LWindowTableDeletes:=LWindowTableDeletes + 1;
LWNumsDeleted:= LWNumsDeleted +
( Count(LWindowTable where WRownum<LReUseWindowStart) );
end;
delete LWindowTable where WRownum<LReUseWindowStart;
end;
end; //end I<LStockCount condition.
//
end; //Ends Loop 2 (I), the rows of LStockCursor.
finally
LStockCursor.Close();
end; //** ends try-finally block for each cursor (LStockCursor) in Loop 1.
end; //Ends Loop 1 (Stock cursor)
//
//Check for Scan.
//
if Scan then
begin
//Using global table GProfile.
delete GProfile;
insert
row
{
LRank TotalRows,
LWindowTableInserts WNumsInserted,
LWindowTableDeletes WTableDeletes,
LWNumsDeleted WNumsDeleted
}
into GProfile ;
end; //end scan true
//
result:= D4WindowSummaryforForm;
end; //End of operator.