SumSens (ope)

Exemple:

procedure TTestFrameworkView.Test_view_ope_sumSens;
var rg:TregClass; Args:Array[0..3] of Variant; ARslt,AExp:Currency;
begin
  doCleanAll;
  //                               E       A         B
  CreateInstanceD('D1','D1.1',Date,1,'EUR',2.0,'EUR',1.0);  //1: B-A = -1
  CreateInstanceD('D1','D1.2',Date,1,'EUR',1.0,'EUR',2.0);  //1: B-A = 1
 
  CreateInstanceD('D1','D1.3',Date,0,'EUR',1.0,'EUR',2.0);  //0: A-B = -1
  CreateInstanceD('D1','D1.4',Date,0,'EUR',2.0,'EUR',1.0);  //0: A-B = 1
  //
  rg := ModelManager.FindRegClass('ProcessusA',false);
  Check(Assigned(rg),'ProcessusA not found');
  try
    //case C=1 then B-A else A-B)
    doCreateOperation(rg,'function ViewTypeDef(const iCode:string; iEntier:Integer):Currency;',
       'Type'+
       '  MyView = viewof(ClassD)'+
       '    unCode:string = unCodeA primary;'+
       '    unEntier:Integer = unEntierA;'+ // needed in group by
       '    sumMontant:TMoneyDT = sumSens(unMontantDT,unMontantDT_2,unEntierA);'+
       '  end;'+
       ''+
       'var inst:MyView;'+
       'begin'#13+
       '  inst := MyView.Find(''(unCode=%1) and (unEntier=%2)'','''',true,[iCode,iEntier]);'+
       '  if Assigned(inst)'+
       '     then Result := inst.sumMontant.Value'+
       '     else Result := 0;'+
       'end;');
 
    Args[0] := 'D1';
    Args[1] := 1;
    AExp  := 0.0;
    ARslt := doExecuteMethod(rg, 'ViewTypeDef', Args);
    Check(ARslt=AExp,Format('Find in view failed (1), rslt:%0.2f expected:%0.2f',[ARslt,AExp]));
 
    Args[0] := 'D1';
    Args[1] := 0;
    AExp  := 0.0;
    ARslt := doExecuteMethod(rg, 'ViewTypeDef', Args);
    Check(ARslt=AExp,Format('Find in view failed (0), rslt:%0.2f expected:%0.2f',[ARslt,AExp]));
  finally
  ModelManager.DestroyPackage(sTypeDefPackage);
  doDeleteOperation(rg,'ViewTypeDef');
  end;
end;

Requête:

select   
case when ISNULL(t0.unEntierA, 0)=1 then sum(ISNULL(t0.unMontantDT_2, 0)-ISNULL(t0.unMontantDT, 0)) else sum(ISNULL(t0.unMontantDT, 0)-ISNULL(t0.unMontantDT_2, 0)) end  as "sumMontant",
t0.unMontantDT_CodeDevise  as "sumMontant_CodeDevise",
Convert(DateTime,'2023-09-03 00:00:00',120) as "sumMontant_Date",
NULL  as "sumMontant_RPConv",
case when ISNULL(t0.unEntierA, 0)=1 then sum(ISNULL(t0.unMontantDT_2_RPValue, 0)-ISNULL(t0.unMontantDT_RPValue, 0)) else sum(ISNULL(t0.unMontantDT_RPValue, 0)-ISNULL(t0.unMontantDT_2_RPValue, 0)) end  as "sumMontant_RPValue",
NULL  as "sumMontant_TCConv",
case when ISNULL(t0.unEntierA, 0)=1 then sum(ISNULL(t0.unMontantDT_2_TCValue, 0)-ISNULL(t0.unMontantDT_TCValue, 0)) else sum(ISNULL(t0.unMontantDT_TCValue, 0)-ISNULL(t0.unMontantDT_2_TCValue, 0)) end  as "sumMontant_TCValue",t0.unCodeA  as "unCode"
from dbo.CLASSD t0
where (t0.unCodeA = 'D1') and (t0.unEntierA=1)
group by t0.unMontantDT_CodeDevise,t0.unCodeA,t0.unEntierA

Résultat :

unCodeA                          unEntierA   unMontantDT           unMontantDT_2
================================================================================
D1                               1           2.00                  1.00
D1                               1           1.00                  2.00
D1                               0           1.00                  2.00
D1                               0           2.00                  1.00

(4 rows affected)

sumMontant            sumMontant_CodeDevise sumMontant_Date         sumMontant_RPConv sumMontant_RPValue    sumMontant_TCConv sumMontant_TCValue    unCode
==========================================================================================================================================================
0.00                  EUR                   2023-09-03 00:00:00.000 NULL              0.00                  NULL              0.00                  D1

(1 row affected)


Completion time: 2023-09-03T10:46:38.3932410+02:00