(gelöst) SQL Abfrage als Function in einem javascript

[Norwegen60]

Hallo zusammen,

ich sammle für verschiedene Datenpunkte immer um Mitternacht die aktuellen Zählerständ

Mit folgendem Script kann ich mir dann z.B. den Verbrauch des letzten Monats berechnen und anzeigen lassen

   sendTo('sql.0', 'query', 'use ioBroker DECLARE @ID int = 63, @Month int = 2 select max(val) -(select max(val) from ts_number where ((id = @ID) and (year(DATEADD(s, ts/1000, \'1970-01-01\'))*12+month(DATEADD(s, ts/1000, \'1970-01-01\')) = year(getdate())*12+month(getdate())-@Month)) or (id = 0)) from ts_number where ((id = @ID) and (year(DATEADD(s, ts/1000, \'1970-01-01\'))*12+month(DATEADD(s, ts/1000, \'1970-01-01\')) = year(getdate())*12+month(getdate())-@Month+1)) or (id = 0)', async (result) => {
    setState('0_userdata.0.Power.Pool.rEnergyRateMonthBefore' /* rEnergyRateMonth */, getAttr((getAttr(result, 'result')[0]), ''), true);
  });
 console.log(('Pool.rEnergyRateMonthBefore = ' + String(getState('0_userdata.0.Power.Pool.rEnergyRateMonthBefore').val)));

Dieses lange SQL ist nur schwer les- und pflegbar. Ich benötige es aber x mal (1x für jeden Datenpunkt und dann nochmal zur Berechnung des Vormonats, ...). Gibt es eine Möglichkeit, das SQL mit den Variablen @ID, @Month in irgendeiner Weise per Java-Funktion zusammenzustellen oder ausführen zu lassen.

In MsSQL könnte das folgenderweise aussehen

DECLARE @ID int = 59, @Month int = 1
select max(val) -(
  select max(val) 
  from ts_number 
  where ((id = @ID) and (year(DATEADD(s, ts/1000, '1970-01-01'))*12+month(DATEADD(s, ts/1000, '1970-01-01')) = year(getdate())*12+month(getdate())-@Month)) or (id = 0))
from ts_number 
where ((id = @ID) and (year(DATEADD(s, ts/1000, '1970-01-01'))*12+month(DATEADD(s, ts/1000, '1970-01-01')) = year(getdate())*12+month(getdate())-@Month+1)) or (id = 0)

d.h über die Variable @ID lege ich fest, welcher Datenpunkt berechnet werden soll und über @Month welcher Monat (1=aktueller Monat, 2 = Vormonat, ...)

[Norwegen60]

OK, ich habe es selbst herausbekommen. Die Hürden waren

• nicht genau zu wissen, wie bei JavaScript function und deren Parameter funktionieren
• die Erkenntnis, dass bei SQL die Ergebnisse zeitverzögert zurück kommen. Wenn man in der Function das Ergebnis als result zurück gegeben hat, hat der Wert im Log nie gestimmt. Die Suche warum die Werte nie gestimmt haben, hat einiges an Zeit verschlungen

Ich habe es deshalb wie folgt realisiert. Der Einfacheit halber verwende ich die Funktion um Jahresergebnisse zu liefern. Da muss nicht Jahr/Monat verschlüsselt werden (Monatsabfrage steht ja schon im vorigen Post)

function GetEnergyYear(ID, Year, DataPoint, DataPointCurrent = '')
{  let ValueCurrent = 0;
   let sSQL1 = 'use ioBroker DECLARE @ID int = ' +String(ID) +', @Year int ='+String(Year);
   let sSQL2 = 'select max(val) from ts_number where ((id = @ID) and (year(DATEADD(s, ts/1000, \'1970-01-01\')) = year(getdate())-@Year-1)) or (id = 0)';
   let sSQL =sSQL1 + ' select max(val) -(' + sSQL2 +') from ts_number where ((id = @ID) and (year(DATEADD(s, ts/1000, \'1970-01-01\')) = year(getdate())-@Year)) or (id = 0)'; 
   
  
  sendTo('sql.0', 'query', sSQL, async (result) =>{ 
     setState(DataPoint , getAttr((getAttr(result, 'result')[0]), ''), true);
   });
 
  // Wenn Korrekturwert (aktueller Tages-Wert) mit übergeben wurde, diesen zum Wert addieren 
  // Hier muss man wissen, dass immer nur der Endwert eines jeden Tages abliegt.
  // Der Stand des aktuellen Tages fehlt also. Der kann per DataPointCurrent übergeben und hinzuaddiert werden
  if (String(DataPointCurrent) != '') {
    ValueCurrent = getState(DataPointCurrent).val;
    if (Logging) {console.log('###2: ValueCurrent = ' + String(ValueCurrent))};
    setState(DataPoint /* rEnergyRateDay */, (getState(DataPoint).val + ValueCurrent), true);
  };
 
  if (Logging) {console.log('###2: '+ String(DataPoint) + ' = ' + String(getState(DataPoint).val))};
}

 // #############################################################################################################################
// alle 2 Minute aktuelle Monatsverbräuche berechnen
schedule('{"time":{"start":"00:00","end":"24:00","interval":2,"mode":"minutes"},"period":{"days":1}}', async () => {
  console.log(('### Verbrauch aktueller Monat ###'));
  
  // Und so erfolgt die Abfrage. 88 und 100 sind die Datapoint ID des Tagesendwertes
  GetEnergyYear(88,0,'0_userdata.0.PV.rPV_Energy_RateYear','0_userdata.0.PV.rPV_Energy_Day');
  GetEnergyYear(88,1,'0_userdata.0.PV.rPV_Energy_RateYearBefore');  // der benötigt keine Korrektur da ja alle Tage abgelegt sind
  GetEnergyYear(100,0,'0_userdata.0.PV.rUsed_Energy_RateYear','0_userdata.0.PV.rUsed_Energy_Day');
  GetEnergyYear(100,1,'0_userdata.0.PV.rUsed_Energy_RateYearBefore');
});

Kann sein, dass es einfacher geht, aber so funktioniert es

Grüße
Gerd