DBE Auswertung

var query = "SELECT DISTINCT(person.id)," + "person.typ," + "person.akademischertitel," + "person.anrede," + "person.vorname," + "person.nachname," + "person.email," + "mitarbeiter.telefonnummer," + "languages.locale," + "date(person.ersterlogin) as ersterlogin," + "person.anzahllogins," + "date(person.letzterlogin) as letzterlogindate,"+ "person.newsletteroptin," + "betrieb.angezeigtername," + "REPLACE(CAST(betrieb.bewirtschafteteflaecheinhektar AS TEXT),'.',',' ) AS areainhectare," + "betrieb.geschaeftsfuehrervorname," + "betrieb.geschaeftsfuehrernachname," + "betrieb.kundennummer," + "waehrung.alphabeticcode," + "adresse.strasse," + "adresse.hausnummer," + "adresse.adresserweiterung," + "adresse.postleitzahl," + "adresse.ort," + "adresse.ortsteil," + "(SELECT objekt.name FROM objekt WHERE objekt.id=bundesland.id) AS Bundesland," + "land.deutschebezeichnung," + "papierregistrierung.email As ProvEmpf " + "FROM person person " + "LEFT JOIN languages languages ON (languages.id = person.language_id)" + "LEFT JOIN betrieb betrieb ON (betrieb.id = person.buchtaufbetrieb_id)" + "LEFT JOIN mitarbeiter mitarbeiter ON (mitarbeiter.person_id = person.id)" + "LEFT JOIN adresse adresse ON (adresse.id=betrieb.adresse_id)" + "LEFT JOIN bundesland bundesland ON (bundesland.id = adresse.bundesland_id)" + "LEFT JOIN land land ON (land.id=adresse.land_id)" + "LEFT JOIN papierregistrierung papierregistrierung ON (person.id = papierregistrierung.person_id)" + "LEFT JOIN waehrung waehrung ON (waehrung.id = betrieb.waehrung_id)" + "JOIN objekt objekt ON (objekt.id = person.id) "+ "WHERE person.aktiv=true AND person.emailconfirmed=true AND person.email IS NOT NULL"; var list = em.createNativeQuery(query).getResultList(); println("NEXT-ID;Typ;Titel;Anrede;Vorname;Nachname;eMail;Telefon;Sprache;Erstlogin;Anzahl Logins;Letzter Login;Newsletter-OptIn;Name 2;Hektar gesamt;GF_Vorname;GF_Nachname;Metro-Kd-ID;Währung;Straße;Hausnr.;Adr.-Zusatz;PLZ;Ort;Ortsteil;Bundesland;Land;Erfasst durch"); var it = list.iterator(); while(it.hasNext()) { var row = it.next(); var i=0; while (i < row.length) { var text = row[i]; print (((text == null) ? "": text) +";"); i++ } println(); }

function onlyUnique(value, index, self)

{ return self.indexOf(value) === index; }
var querylist = em.createNativeQuery("select dbem.farmname,person.email , dbem.farmid, o.erstellt, dbem.cropseasonyear, dbem.ispersonfrombaywa, b.duengesystemherausgeber_id " +
" from duengebedarfermittelndokument dbem " +
" left JOIN objekt o ON (o.id = dbem.id) " +
" left join person on (dbem.farmid = person.buchtaufbetrieb_id) " +
" left join betrieb b on (b.id = person.buchtaufbetrieb_id) " +
" where dbem.cropseasonyear = 2022 " ).getResultList();
var item = querylist.iterator();
var count = 0;
var countDistinct= 0;
var emailArray = [];
println("Erstelldatum;Betriebsname;BetriebsId;Email;BayWa;Duengesystemherausgeber");
querylist.forEach(function (row) {
emailArray.push(row[1])
var baywa = "nein"
if (row[5] === true)

{ baywa = "ja"; }
if ( emailArray.indexOf(row[1]) == -1)

{ countDistinct++ }
count++
println(row[3] + ";" + row[0] + ";" + row[2] + ";" + row[1] + ";" + baywa + ";" + row[6] );
});
println("--- Gesamt: " + count + " ---");
println("--- Einzelausführungen: " + emailArray.filter(onlyUnique).length + " ---");