SumSens (ope)

Exemple:

<source lang="delphi"> 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; </source>

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