// ==============================================
// Erstelldatum : 19.02.2021 00:37:46
// V2 mit import auth 14.11.2022
// V4 zahlsperre 09.02.2024
// Ersteller : ARektenald
// Beschreibung : Anlegen Kunden/Lieferantendaten, Setzen UID Prüfdatum
// 17/09/2023: add queue id
// ==============================================
Var
	FirmenNr :String;
	FibuNr :String;					 
	gLogfile: String;  
	kdlf_inserted : TBMDINT;
	kdlf_updated : TBMDINT;
	kdlf_taken: TBMDINT;
	bucList: BMD_BuchungList; 
	do_uid_stufe2: Integer;
	kdlf_konten: MacroStringList;
	kdlf_uid_dates: MacroStringList;
	max_kdlf: String;
	vorerf_typ: String;
	buchsymbol: String;
	fh_log: TBMDInt;
	fh_bmd_log: TBMDInt;
	fh_personen: TBMDInt;
	temp_path: String;
	do_auth_makro: Integer;
	do_auth_import: Integer;
					  
//------------------------------------------------------------------------------
// Parameter festlegen - UID Stufe 2 
//------------------------------------------------------------------------------

function initParams;
begin
	do_uid_stufe2 := 0;	 // uid stufe 2 prüfung erstellen beim personenimport 
	do_auth_makro := 1;		   // 1: Steuerung über FWF_FREIFELDA01_50, 0: immer aktiv
	do_auth_import := 1;		   // 1: Steuerung über INS_FREIFELD9, 0: immer anlegen/ändern; 1: je nachdem was in dem feld steht
end;	 
//------------------------------------------------------------------------------  
// ================================================================================
function MyLog_local (data: String);
begin
	BMD_WRITETOLOGFILE(gLogfile,data); 
end;
// ================================================================================

function MyLog (data: String);
begin
	created := 0;
	if not (BMD_FILEEXISTS (gLogfile)) then begin
		BMD_CREATEFILE(gLogfile);
		created := 1;
	end;

	BMD_ASSIGNFILE (fh_log,gLogfile,0,1,16,28591);	   // ANSI
	BMD_OPENFILE_APPEND (fh_log); 
	if created then begin   
		BMD_WRITELN (fh_log, '****************** Logisth.AI Start Information ImportModell Periode/Kunden/Lieferanten **************');   
	end;  

	BMD_WRITELN (fh_log, '***** ' + data);
	BMD_CLOSEFILE (fh_log);  //CloseFile

end;
//------------------------------------------------------------------------------
function dump_logfile (name: String; logfile: String);
begin
	if (BMD_FILEEXISTS (logfile)) then begin
		MyLog ('*** START Logfile Buchen:');
		BMD_ASSIGNFILE (fh_bmd_log, logfile);
		BMD_OPENFILE_RESET (fh_bmd_log);
		while not BMD_EOF (fh_bmd_log) do begin
			line := BMD_READLN (fh_bmd_log);
			MyLog (line);							 
		end;  
		BMD_CLOSEFILE (fh_bmd_log);
		MyLog ('END Logfile Buchen Vorerfassung');
	end;
	else begin
		MyLog ('Logfile ' + name + ' ' + logfile + ' not existing');
	end;
end;

//------------------------------------------------------------------------------

function is_kdlf_buch  (FirmenNr: String; FibuNr: String; bs: String);
begin
	sqlText := 'select distinct VBV_VORERFASSTYP FROM FIBU.VBV_VORERFASSBUCH where VBV_FIRMENNR = :kundennr and VBV_FIBUNR = :fibunr  and VBV_BUS_BUCHSYMSYMBOL = :bs
		AND VBV_VORERFASSTYP in (3,4)';

	iQuery  := MacroObject.CreateMacroQuery('fetch_vorerf');
	iQuery.setSQLText (sqlText);
	iQuery.setParamAsString('kundennr', FirmenNr);
	iQuery.setParamAsString('fibunr', FibuNr);
	iQuery.setParamAsString('bs', bs);
	iQuery.Open();

	Result := '';

	// bei doppelten buchsymbolen werden trotzdem mehr records geliefert mit RecordCount - loop erforderlich
	while not iQuery.Eof do begin
		vorerf_typ	:= iQuery.getAsString('VBV_VORERFASSTYP');
		Result := vorerf_typ;

		iQuery.Next();
		if not iQuery.Eof then begin
			Result := '';
			break;
		end;
	end;
	iQuery.Close;

end;

// ================================================================================

function kdlf_exists  (uid: String; name: String; zusatzname: String; strasse: String; plz: String);
begin
	sqlText := 'SELECT
			max (KTO_KONTONR) as KONTO
		FROM FIBU.PEK_PERSONENKONTO P1
			INNER JOIN BUERO.KLI_KUNDE_LIEFERANT KL ON KL.KLI_FIRMENNR = P1.PEK_FIRMENNR AND KL.KLI_KLID = P1.PEK_KLID
			INNER JOIN BUERO.PER_PERSON ON PER_FIRMENNR = PEK_FIRMENNR AND PER_PERSONENID = P1.PEK_KLID
			INNER JOIN FIBU.KTO_KONTO ON KTO_FIRMENNR = PEK_FIRMENNR AND KTO_FIBUNR = PEK_FIBUNR AND KTO_KONTONR = PEK_KONTONR
			INNER JOIN BUERO.ADR_ADRESSE ON ADR_ADRESSLFDNR = PER_ADRESSLFDNR
			INNER JOIN BUERO.FIR_FIRMA ON FIR_FIRMENNR = PEK_FIRMENNR		  
		WHERE
			PEK_FIRMENNR=:kundennr
			AND PEK_FIBUNR = :fibunr
			AND PER_NAME = :name
			and isnull(PER_ZUSATZNAME, '''') = isnull (:zusatzname, '''')
			AND isnull (ADR_STRASSE, '''') = isnull (:strasse, '''')
			and isnull (PER_UIDNR, '''') = isnull (:uid, '''')
			AND isnull (ADR_PLZ, '''') = isnull (:plz, '''')';

	if (vorerf_typ = '3') then begin		   // ER
		sqlText := sqlText + ' AND (KLI_ISTLIEFERANT=1 or KTO_KONTENGRUPPE = 4)';		// kontengruppe 4 = kuli
	end;	
	if (vorerf_typ = '4') then begin		   // AR
		sqlText := sqlText + ' AND (KLI_ISTKUNDE=1 or KTO_KONTENGRUPPE = 4)';		// kontengruppe 4 = kuli
	end;	

	iQuery  := MacroObject.CreateMacroQuery('fetch_kdlf');
	iQuery.setSQLText (sqlText);
	iQuery.setParamAsString('kundennr', FirmenNr);
	iQuery.setParamAsString('fibunr', FibuNr);
	iQuery.setParamAsString('name', name);
	iQuery.setParamAsString('zusatzname', zusatzname);
	iQuery.setParamAsString('strasse', strasse);
	iQuery.setParamAsString('uid', uid);
	iQuery.setParamAsString('plz', plz);
	iQuery.Open();
	   
	Result := '';
	if (iQuery.RecordCount() = 1) then begin

		konto	:= iQuery.getAsString('KONTO'); 
		Result := konto;
	end;
end;

// ================================================================================

function get_next_free_kdlf_nummer;
begin
	iQuery  := MacroObject.CreateMacroQuery('fetch_nummernkreis');

	sqlText := 'SELECT PNK_VONNR, PNK_BISNR
		FROM BUERO.PNK_PERSONNUMMERNKREIS
		WHERE PNK_FIRMENKATEGORIELFDNR=1
		AND PNK_FIRMENNR = :firmennr
		AND PNK_PERSONKZNR = (
			SELECT BUS_SUCHKONTENGRUPPE_KTO-1
			FROM FIBU.BUS_BUCHUNGSSYMBOL BS
			WHERE
				BS.BUS_BUCHSYMSYMBOL = :buchsymbol AND
				BUS_SUCHKONTENGRUPPE_KTO in (2,3) AND
				((BS.BUS_FIRMENNR = PNK_FIRMENNR AND BS.BUS_FIBUNR = :fibunr)
				OR (BS.BUS_FIRMENNR IS NULL AND BS.BUS_FIBUNR IS NULL
				AND BS.BUS_BUCHSYMSYMBOL
				NOT IN (SELECT BS2.BUS_BUCHSYMSYMBOL
					FROM FIBU.BUS_BUCHUNGSSYMBOL BS2
					WHERE BS2.BUS_FIRMENNR = :firmennr2 AND BS2.BUS_FIBUNR = :fibunr2
					AND BUS_SUCHKONTENGRUPPE_KTO in (2,3)
					)) ))';

	iQuery  := MacroObject.CreateMacroQuery('fetch_kreise');
	iQuery.setSQLText (sqlText);
	iQuery.setParamAsString('firmennr', Firmennr);
	iQuery.setParamAsString('firmennr2', Firmennr);
	iQuery.setParamAsString('fibunr', FibuNr);
	iQuery.setParamAsString('fibunr2', FibuNr);
	iQuery.setParamAsString('buchsymbol', buchsymbol);
	iQuery.Open();

	if (iQuery.RecordCount() > 0) then begin

		from_konto	:= iQuery.getAsString('PNK_VONNR');
		to_konto	:= iQuery.getAsString('PNK_BISNR');
		iQuery.close();

		sqlText :=  'SELECT min(P.PER_PERSONENNR)+1 as MIN_NR 
				FROM BUERO.PER_PERSON P  
			WHERE 
				P.PER_PERSONENNR between :from and :to  
				AND P.PER_PERSONENNR >= :max
			and not exists (select 1 from BUERO.PER_PERSON C, BUERO.KLI_KUNDE_LIEFERANT A  
			WHERE  
				C.PER_PERSONENNR = P.PER_PERSONENNR+1  
				AND C.PER_FIRMENNR = :firmennr  
				AND C.PER_PERSONENART = ''KL''  
				AND C.PER_JAHR >= 0  
				AND C.PER_ISTAKTUELLUNTERNR >= 1  
				AND A.KLI_FIRMENNR = C.PER_FIRMENNR  
				AND A.KLI_KLID = C.PER_PERSONENID  
				AND A.KLI_KLUNTERNR = C.PER_PERSONUNTERNR)';

		iQuery  := MacroObject.CreateMacroQuery('next_nummer');
		iQuery.setSQLText (sqlText);
		iQuery.setParamAsString('firmennr', Firmennr);															
		iQuery.setParamAsString('from', from_konto);
		iQuery.setParamAsString('to', to_konto);
		iQuery.setParamAsString('max', max_kdlf);

		iQuery.Open();

		if (iQuery.RecordCount() > 0) then begin

			next_kdlf	:= iQuery.getAsString('MIN_NR'); 
			max_kdlf := next_kdlf;
			//MyLog ('next kdlf: ' + next_kdlf);

			iQuery.close();
			Result := next_kdlf;
		end;
	end;
end;

// ================================================================================

procedure get_existing_KDLF  (var KontoNr, uid, name, zusatzname, strasse, plz, iban, kondition, land_id, zahlsperre);
begin

	sqlText := 'SELECT
			PER_NAME as NAME,
			PER_ZUSATZNAME as ZUSATZNAME,
			PER_UIDNR as VATREGNO,
			PER_PERSONENID as PERSONENID,
			BAI_IBAN as IBAN,
			ADR_STRASSE as ADDRESSE,
			ADR_PLZ as PLZ,
			KOD_KONDLFDNR as KONDITION,
			isnull (LAN_ISO_CODE, ''AT'') as LAND,
			isnull (iif (KTO_KONTENGRUPPE = 2, iif (isnull (KLI_HATZAHLSPERRE, 0) = 1, 1, PEK_KUN_ZAHLSPERRE), iif (isnull (KLI_HATZAHLSPERRE, 0) = 1, 1, PEK_LIE_ZAHLSPERRE)), 0) as ZAHLSPERRE
		FROM FIBU.PEK_PERSONENKONTO P1
			INNER JOIN BUERO.KLI_KUNDE_LIEFERANT KL ON KL.KLI_FIRMENNR = P1.PEK_FIRMENNR AND KL.KLI_KLID = P1.PEK_KLID
			INNER JOIN BUERO.PER_PERSON ON PER_FIRMENNR = PEK_FIRMENNR AND PER_PERSONENID = P1.PEK_KLID
			INNER JOIN FIBU.KTO_KONTO ON KTO_FIRMENNR = PEK_FIRMENNR AND KTO_FIBUNR = PEK_FIBUNR AND KTO_KONTONR = PEK_KONTONR
			INNER JOIN BUERO.ADR_ADRESSE ON ADR_FIRMENNR = PEK_FIRMENNR AND ADR_ADRESSLFDNR = PER_ADRESSLFDNR
			LEFT JOIN BUERO.BAI_BANKINSTITUTIONSZU ON BAI_BANKINSTLFDNR=PER_SENDER_BANKINSTLFDNR
			LEFT JOIN BUERO.KOD_KONDITIONEN ON KOD_KONDNR=KLI_LIEF_KONDNR
			LEFT JOIN BUERO.LAN_LAND ON LAN_LANDNR = PER_LANDNR
		WHERE
			PEK_FIRMENNR = :firmennr AND PEK_FIBUNR = :fibunr AND PEK_KONTONR = :kontonr';

	iQuery  := MacroObject.CreateMacroQuery('get_person');
	iQuery.setSQLText (sqlText);
	iQuery.setParamAsString('firmennr', FirmenNr);
	iQuery.setParamAsString('kontonr', KontoNr);
	iQuery.setParamAsString('fibunr', FibuNr);
	iQuery.Open();
	if (iQuery.RecordCount() > 0) then begin

		uid	:= iQuery.getAsString('VATREGNO');
		name	:= iQuery.getAsString('NAME');
		zusatzname	:= iQuery.getAsString('ZUSATZNAME');
		personenid	:= iQuery.getAsString('PERSONENID');
		strasse	:= iQuery.getAsString('ADDRESSE');
		iban	:= iQuery.getAsString('IBAN');
		plz	:= iQuery.getAsString('PLZ');
		kondition	:= iQuery.getAsString('KONDITION');
		land_id	:= iQuery.getAsString('LAND');
		zahlsperre	:= iQuery.getAsString('ZAHLSPERRE');
	end;
	iQuery.Close();
end;

// ================================================================================

procedure  fetch_json (var json_string, json_learned, do_insert_update, name, zusatz_name, addresse, ort, plz, land_id, uid, uid_status, iban1, iban2, iban3, uid_datum, kondition, zahlsperre, queue_id);
begin
	name := '';
	zusatzname := '';
	addresse := '';
	ort := '';
	plz := '';
	land_id := '';
	uid_status := '';
	uid_timestamp := '';
	iban1 := '';
	iban2 := '';
	iban3 := '';
	kondition := '';
	queue_id := '';
	do_insert_update := 0;

	json := MacroObject.CreateMacroJsonVal();
	json.ParseJson(json_string);

	if json.KeyExists ('N1') then begin
		name    := json.GetValueAsString('N1');
	end;
	if json.KeyExists ('N2') then begin
		zusatzname    := json.GetValueAsString('N2');
	end;
	if json.KeyExists ('A') then begin
		addresse    := json.GetValueAsString('A');
	end;
	if json.KeyExists ('C') then begin
		ort    := json.GetValueAsString('C');
	end;
	if json.KeyExists ('P') then begin
		plz    := json.GetValueAsString('P');
	end;
	if json.KeyExists ('CC') then begin
		land_id    := json.GetValueAsString('CC');
	end;
	if json.KeyExists ('U') then begin
		uid_status    := json.GetValueAsString('U');
		if (uid_status = '1') then begin
			uid_datum := uid_timestamp;
		end;
	end;
	if json.KeyExists ('TS') then begin
		uid_timestamp    := json.GetValueAsString('TS');
	end;
	if json.KeyExists ('I1') then begin
		iban1    := json.GetValueAsString('I1');
	end;
	if json.KeyExists ('I2') then begin
		iban2    := json.GetValueAsString('I2');
	end;
	if json.KeyExists ('I3') then begin
		iban3    := json.GetValueAsString('I3');
	end;
	if json.KeyExists ('Z') then begin
		zahlsperre    := json.GetValueAsString('Z');
	end;
	if json.KeyExists ('IO') then begin
		do_kdlf_insert_update    := json.GetValueAsString('IO');
		if (do_kdlf_insert_update <> '') then begin
			do_insert_update := 1;
		end;
	end;
	if json.KeyExists ('Q') then begin
		queue_id    := json.GetValueAsString('Q');
	end;
	if json.KeyExists ('KD') then begin
		kondition    := json.GetValueAsString('KD');
	end;

	json.ParseJson(json_learned);

	if json.KeyExists ('U') then begin
		uid    := json.GetValueAsString('U');
	end;

end;

// ================================================================================

function add_uid_check_date (firmennr: String; kunden_nr: String; uid: String; uid_datum: String);
begin

	personenid := 'KL2000' + kunden_nr;

	text := 'Logisth.AI checked';

	SQL_exists := 'SELECT  count(*) as COUNT
		FROM BUERO.PPF_PERSONENPRUEFUNG
		WHERE
			PPF_FIRMENNR = :firmennr
			AND PPF_PERSONENID = :personenid
			AND PPF_UIDNR = :uid
			AND PPF_PRUEFUNG_DATUM = CONVERT (datetime, :uid_datum, 104)   
			AND PPF_STATUS = 1
			AND PPF_STATUS_STUFE1 = 1';

	iQuery  := MacroObject.CreateMacroQuery('check_exists');
	iQuery.setSQLText (SQL_exists);
	iQuery.setParamAsString('firmennr' , FirmenNr);
	iQuery.setParamAsString('personenid' , personenid);
	iQuery.setParamAsString('uid' , uid);
	iQuery.setParamAsString('uid_datum' , uid_datum);

	iQuery.Open();
	count := '0';
	if (iQuery.RecordCount() > 0) then begin
		count := iQuery.getAsString('COUNT');
	end;
	iQuery.Close();
	if count = '0' then begin

		name := '';
		old_uid := '';
		zusatzname := '';
		strasse := '';
		plz := '';
		iban := '';
		kondition := '';
		land_id := '';
		zahlsperre := '';

		get_existing_KDLF  (kunden_nr, old_uid, name, zusatzname, strasse, plz, iban, kondition, land_id, zahlsperre);

		if (uid = old_uid) AND (name <> '')  then begin
			seq_id := BMD_GETSEQID();

			iQuery  := MacroObject.CreateMacroQuery('create_uid_check');
			user := BMD_SYSTEMUSERNAME();

			MyLog ('UID Prüfdatum setzen ' + kunden_nr + ' uid_datum: ' + uid_datum);

			insert_SQLText := 'insert into BUERO.PPF_PERSONENPRUEFUNG (PPF_SEQUID, PPF_TYP, PPF_FIRMENNR, PPF_PERSONENID,
				PPF_UIDNR, PPF_NAME, PPF_ADRESSE, PPF_PRUEFUNG_DATUM, PPF_STATUS, PPF_PRUEFUNG_USERID, PPF_AENDERUNGSDATUM, PPF_ERSTELLDATUM, PPF_STATUS_STUFE1, PPF_TEXT,
				PPF_PRUEFUNGSID,PPF_FONCODE, PPF_WEBSERVICEKZ)
				values (:seq_id, 1, :firmennr, :personenid, :uid, :uid_name, :uid_addr, CONVERT(datetime,:uid_datum, 104), 1, SUBSTRING (:user, 1, 10), getdate(), getdate(), 1, :text, -1, 0, 1)';

			iQuery.setSQLText (insert_SQLText);
			iQuery.setParamAsString('seq_id' , seq_id);
			iQuery.setParamAsString('firmennr' , firmennr);
			iQuery.setParamAsString('personenid' , personenid);
			iQuery.setParamAsString('uid' , uid);
			iQuery.setParamAsString('uid_name' , name);
			iQuery.setParamAsString('uid_addr' , strasse);
			iQuery.setParamAsString('uid_datum' , uid_datum);
			iQuery.setParamAsString('user' , user);
			iQuery.setParamAsString('text', text);
			iQuery.ExecSQL();
			iQuery.Close();
		end;
		else begin
			if name <> '' then begin
				MyLog ('UID Prüfdatum nicht setzen ' + kunden_nr + ' (uid differs: ' + uid + '/' + old_uid);
			end;
			else begin
				MyLog ('UID Prüfdatum nicht setzen ' + kunden_nr + ' No Name/kdlf not found!!!');
			end;
		end;	
		
	end; 
	else begin
		//BMD_SHOWMESSAGE ('no uid update');
		MyLog ('UID Prüfdatum aktuell - no update: personenid ' + personenid + ' uid_datum: ' + uid_datum);
	end;

end;

// ================================================================================

function add_to_import_KDLF (konto: String; do_insert_update: Integer; uid: String; uid_datum: String;  name: String; zusatzname: String; strasse: String; plz: String; land_id: String; iban1: String; iban2: String; iban3: String; kondition: String; zahlsperre: String; queue_id: String);
begin

	// data := name + ';' + zusatzname + ';;' + plz + ';' + strasse + ';' + uid + ';' + land_id + ';' + iban1 + ';' + uid_datum + ';' + kondition + ';' + zahlsperre + ';' + queue_id;
	data := name + ';' + zusatzname + ';;' + plz + ';' + strasse + ';' + uid + ';' + land_id + ';' + iban1 + ';' + uid_datum + ';' + kondition + ';' + zahlsperre + ';' + BMD_UPPER (name);
	Result := konto;

	// keine JSON Daten
	if BMD_LEN (name) = 0 then begin
		//BMD_SHOWMESSAGE ('notning to do');
		exit;
	end;

	// kein Konto im import file
	if (do_insert_update and ((konto = '') or (konto = '0'))) then begin
		konto :=  kdlf_konten.GetKeyValue(data);

		// schon bereits gefunden
		if (konto <> '') then begin
			result := konto;
			exit;
		end;


		// unnötig, kann eigentlich gar nicht sein dass es den schon gibt

		existing_konto := kdlf_exists  (uid, name, zusatzname, strasse, plz);
		if existing_konto <> '' then begin
			konto := existing_konto;
			MyLog ('Bereits angelegter KDLF gefunden: ' + konto + ' name: ' + name);
			INC (kdlf_taken);
		end;
		else begin
			konto :=  kdlf_konten.GetKeyValue(data);

			// keines vorhanden --> neu anlegen
			if (konto <> '') then begin
				Result := konto;
			end;
			else begin
				konto := get_next_free_kdlf_nummer;
				kdlf_konten.SetKeyValue(data, konto);
				MyLog ('Import Insert KDLF konto: ' + konto + ' name: ' + name);
				INC (kdlf_inserted);
			end;
			// data := data + ';{"QI":"' + queue_id + ""};
		end;

	end;
	else begin
		if do_insert_update then begin
			updated_konto :=  kdlf_konten.GetKeyValue(data);

			if updated_konto = konto then begin
				// nothing to do, bereits ein update satz da
				Result := konto;
			end;
			else begin

				old_uid := '';
				old_name := '';
				old_zusatzname := '';
				old_strasse := '';
				old_plz := '';
				old_iban := '';
				old_kondition := '';
				old_land_id := '';
				old_zahlsperre := '';

				get_existing_KDLF  (konto, old_uid, old_name, old_zusatzname, old_strasse, old_plz, old_iban, old_kondition, old_land_id, old_zahlsperre); 
				if zahlsperre = '' then begin
					zahlsperre := old_zahlsperre;
				end;
				if kondition = '' then begin
					kondition := old_kondition;
				end;

				//MyLog ('(1) Import Update KDLF update to? ' + name + ';' + zusatzname + ';;' + +plz + ';' + strasse + ';' + uid + ';' + land_id + ';' + iban1 + ';' + uid_datum + ';' + kondition);   
				//MyLog ('(2) Import Update KDLF update to? old_kond: ' + old_kondition);  
				if ((uid <> old_uid) 
					or (name <> old_name) 
					or (strasse <> old_strasse) 
					or (plz <> old_plz) 
					or (iban1 <> old_iban) 
					or (land_id <> old_land_id) 
					or (kondition <> old_kondition) 
					or (zahlsperre <> old_zahlsperre)  
					) then begin

					import_it := 1;
					differs := '';
					if (name <> old_name) then begin
						differs := differs + 'Name: <' + name + '>/<' + old_name + '> ';
					end;
					if (uid <> old_uid) then begin
						differs := differs + 'UID: <' + uid + '>/<' + old_uid + '> ';
					end;
					if (strasse <> old_strasse) then begin
						differs := differs + 'Strasse: <' + strasse + '>/<' + old_strasse + '> ';
					end;
					if (plz <> old_plz) then begin
						differs := differs + 'PLZ: <' + plz + '>/<' + old_plz + '> ';
					end;
					if (iban1 <> old_iban) then begin
						differs := differs + 'IBAN: <' + iban1 + '>/<' + old_iban + '> ';
					end;
					if (land_id <> old_land_id) then begin
						differs := differs + 'Land: <' + land_id + '>/<' + old_land_id + '> ';
					end;
					if (kondition <> old_kondition) AND (kondition <> '') then begin
						differs := differs + 'KOND: <' + kondition + '>/<' + old_kondition + '> ';
					end;
					if (zahlsperre <> old_zahlsperre) AND (zahlsperre <> '') then begin
						differs := differs + 'ZAHLSPERRE: <' + zahlsperre + '>/<' + old_zahlsperre + '> ';
					end;

					data := name + ';' + zusatzname + ';;' + +plz + ';' + strasse + ';' + uid + ';' + land_id + ';' + iban1 + ';' + uid_datum + ';' + kondition + ';' + zahlsperre + ';' + BMD_UPPER (name);
					MyLog ('Import Update KDLF ' + FirmenNr + '/' + FibuNr + ' konto: ' + konto + ' name: <' + name + '> geändert: ' + differs);

					INC (kdlf_updated);
					// data := data + ';{"QU":"' + queue_id + ""};
					kdlf_konten.SetKeyValue(data, konto);

				end;
				else begin
					MyLog ('no change existing KDLF: ' + konto);
				end;
			end;
		end;
	end;
	if uid_datum <> '' then begin
		//MyLog ('add UID to update konto ' + konto + ' UID: ' + uid + ' datum: ' + uid_datum);
		kdlf_uid_dates.SetKeyValue(konto, uid + ';' + uid_datum);
	end;	


	Result := konto;

end;

// ================================================================================

function update_uid_datum
begin

	if not do_uid_stufe2 then begin
		exit;
	end;

	count := kdlf_uid_dates.Count();
	for  j:= 0 to count-1 do begin
		konto := kdlf_uid_dates.GetName (j);
		uid_datum := kdlf_uid_dates.GetValue (j);

		uid_datum := kdlf_uid_dates.GetValue (j);
		uid := '';
		datum := '';
		sep := BMD_POS (uid_datum, ';');
		len := BMD_LEN (uid_datum);
		uid := BMD_COPY (uid_datum, 1, sep-1);
		datum := BMD_COPY (uid_datum, sep+1, len-sep);
		
		MyLog ('update_uid_datum Konto ' + konto + ' UID: ' + uid + ' datum: ' + datum);
		add_uid_check_date (FirmenNr, konto, uid, datum);

	end;

end;

// ================================================================================
																											 
function import_KDLF (belegid: String);
begin  

	kdlf_count := kdlf_konten.Count();

	if not kdlf_count then begin
		//MyLog ('no KDLF insert/updates');
	exit;
	end;

	MyLog ('Insert/update KDLF kdlf_count: ' + BMD_FORMATNUM('0', kdlf_count));   

	gFileName := 'kdlf_import_' + FirmenNr + '_' + FibuNr + '.csv';
	result := FirmenNr;

	sql_exists := 'SELECT count(1) as COUNT
		FROM FIBU.PEK_PERSONENKONTO P1
			INNER JOIN BUERO.KLI_KUNDE_LIEFERANT KL ON KL.KLI_FIRMENNR = P1.PEK_FIRMENNR AND KL.KLI_KLID = P1.PEK_KLID
			INNER JOIN BUERO.PER_PERSON ON PER_FIRMENNR = PEK_FIRMENNR AND PER_PERSONENID = P1.PEK_KLID
			INNER JOIN FIBU.KTO_KONTO ON KTO_FIRMENNR = PEK_FIRMENNR AND KTO_FIBUNR = PEK_FIBUNR AND KTO_KONTONR = PEK_KONTONR
		WHERE
			PEK_FIRMENNR = :firmennr AND PEK_FIBUNR = :fibunr AND PEK_KONTONR = :kontonr';

	filename := temp_path +'\'+ gFileName;

	if (BMD_FILEEXISTS (filename)) then begin
		BMD_DELETEFILE (filename);  //DeleteFile
	end;

	BMD_CREATEFILE(filename);
	BMD_ASSIGNFILE (fh_personen,filename,1,1,1,1200);   // unicode
	BMD_OPENFILE_APPEND (fh_personen);

	header_line1 := 'NTCS_CONSTID_INFO%10%MCA_KTO_KONTONR%MCA_PER_NAME%MCA_PER_ZUSATZNAME%MCA_ADR_ORTSNAME%MCA_ADR_PLZ%MCA_ADR_STRASSE%MCA_PER_UIDNR%MCA_PER_LANDNR%MCA_BAI_IBAN%MCA_PER_UIDDATUM%MCA_KLI_KONDNR%MCA_KLI_HATZAHLSPERRE%MCA_PER_MATCHCODE';
	header_line2 := 'kto-nr;Name;zusatzname;Ort;PLZ;Strasse;UID;Land;iban-nr;UID-Datum;kondition;zahlsperre;matchcode';

	BMD_WRITELN (fh_personen, header_line1);
	rc := BMD_WRITELN (fh_personen, header_line2);
	
	for  j:= 0 to kdlf_count-1 do begin
		data := kdlf_konten.GetName (j);  
		konto := kdlf_konten.GetValue (j); 
	
		line:= konto + ';' + data; 
		//MyLog ('csv line: ' + line);
		rc := BMD_WRITELN (fh_personen, line); 
	end;
 
	BMD_CLOSEFILE(fh_personen);

	if (BMD_FILEEXISTS (filename)) then begin
		if ((FirmenNr<>'') AND (FibuNr<>'') AND (filename<>'')) then begin
			logfile := temp_path +'\Kontenimport' + FirmenNr + '_' + FibuNr + '_' + belegid + '.log';

			lParam := 'STP_FIB_COMPANYNO=' + BMD_FORMATNUM('0',FirmenNr) + ',STP_FIB_FIBUNO=' + BMD_FORMATNUM('0',FibuNr) + ',STP_IMP_FILENAME=' + filename + ',STP_LOG_FILENAME=' + logfile + ','
			lParam := lParam + 'SILENT=1,';
			lParam := lParam + 'STP_IGNORE_FIRSTLINE=1,';
			lParam := lParam + 'STP_IMP_ENCODING=3,';
			lParam := lParam + 'STP_USEFIXEDLENGTH=0,';
			lParam := lParam + 'STP_IMP_SEPARATOR=;';


			BMD_STARTFUNCTION('MCS_FRMFIBUVARCSVPERSONENKONTENIMPEXP_CREATE',lParam);  //Start Var. Personenimport
			iQuery  := MacroObject.CreateMacroQuery('check_exists');

			for  j:= 0 to kdlf_count-1 do begin
				data := kdlf_konten.GetName (j);  
				konto := kdlf_konten.GetValue (j); 

				iQuery.setSQLText (sql_exists);
				iQuery.setParamAsString('firmennr', Firmennr);
				iQuery.setParamAsString('fibunr', FibuNr);
				iQuery.setParamAsString('kontonr', konto);

				count := '0';
				iQuery.Open();
				if (iQuery.RecordCount() > 0) then begin
					count	:= iQuery.getAsString('COUNT');
				end;
				if (count <> '1') then begin				
					MyLog ('ERROR konto ' + konto +  ' import failed');
					result := '';				   
				end;  
				iQuery.Close();
			end;
			dump_logfile ('KDLF', logfile);
		end;
	end;
	else begin
		MyLog ('Error import file ' + filename + ' not existing!');
	end;
end;

// ================================================================================

function get_auth_import  (FirmenNr: String);
begin
	Result := 15;

	if not do_auth_import then begin
		exit;
	end;

	sqlText := 'select INS_FREIFELD9 from BUERO.INS_INSTITUTION C where C.INS_LANDNR = 0 AND C.INS_INR = :firmennr
		AND (C.INS_ISTAKTIV IS NOT NULL OR C.INS_ISTAKTIV=0) AND isnumeric (INS_FREIFELD9) = 1';

	Result := 0;

	iQuery  := MacroObject.CreateMacroQuery('fetch_auth');
	iQuery.setSQLText (sqlText);
	iQuery.setParamAsString('firmennr', FirmenNr);
	iQuery.Open();

	if (iQuery.RecordCount() = 1) then begin
		Result := BMD_TEXTTONUMDEF (iQuery.getAsString('INS_FREIFELD9'));
	end;
	iQuery.Close;

end;

// ================================================================================

function get_auth_makro  (FirmenNr: String; FibuNr: String);
begin
	Result := 15;

	if not do_auth_makro then begin
		exit;
	end;

	sqlText := 'select FWF_FREIFELDA01_50 FROM FIBU.FWF_FIRMENWERTEFIBU where FWF_FIRMENNR = :kundennr AND FWF_FIBUNR = :fibunr AND FWF_FREIFELDA01_50 is not null AND isnumeric (FWF_FREIFELDA01_50) = 1';

	iQuery  := MacroObject.CreateMacroQuery('fetch_auth');
	iQuery.setSQLText (sqlText);
	iQuery.setParamAsString('kundennr', FirmenNr);
	iQuery.setParamAsString('fibunr', FibuNr);
	iQuery.Open();

	if (iQuery.RecordCount() = 1) then begin
		Result := BMD_TEXTTONUMDEF (iQuery.getAsString('FWF_FREIFELDA01_50'));
	end;
	iQuery.Close;

end;

// ================================================================================ MAIN
// ================================================================================

uid := '';
name := '';
zusatzname := '';
strasse := '';
plz := '';
land_id := '1';
iban1 := '';
queue_id := '';
iban2 := '';
iban3 := '';   
ort := '';
uid_status := '';
uid_datum := '';  
kondition := '';
zahlsperre := '';
	 
initParams;  

// Filehandles
fh_log := 2;
fh_bmd_log := 4;
fh_personen := 5;

//temp_path := 'X:\logs';
temp_path := BMD_GETENVIRONMENTVAR('temp');

FirmenNr := INPUT_FIRMENNR;
FibuNr := INPUT_FIBUNR;   
periode_change := 0;  
periode_opened := 0;
kdlf_inserted := 0;
kdlf_updated := 0; 
kdlf_taken := 0;  

BMD_VORERF_TYP_KUNDEN := '4';
BMD_VORERF_TYP_LIEF := '3';
AUTH_CRE_KUNDEN := 1;
AUTH_UPD_KUNDEN := 2;
AUTH_CRE_LIEF := 4;
AUTH_UPD_LIEF := 8;


kdlf_konten := MacroObject.CreateMacroStringList('kdlf_konten');
kdlf_uid_dates := MacroObject.CreateMacroStringList('kdlf_uid_dates');
max_kdlf := '0';	// last taken new kdlf #

actual_seq := BMD_GETSEQID(); 
							 
bucList := FibuMacroObject.GetExternalFMacroBuchungList(INPUT_FIRMENNR,INPUT_FIBUNR);

gLogFile := BMD_REPLACEREGEXPR ('\.csv$', INPUT_FILENAME, '', 1);   
gLogFile := BMD_REPLACEREGEXPR ('\.CSV$', gLogFile, '', 1);  
gLogFile := gLogFile + '.log'; 


// logfile für start im BMD -> MyLog_local umändern
//gLogfile :=  'BMDFibuVorerfassBuerfLastImport_' + INPUT_FIRMENNR + '_' + INPUT_FIBUNR + '_' + BMD_USERNAME() + '.log';	   

bucList.SetLogFilename (gLogFile);	

MyLog ('Start');
auth_makro := get_auth_makro (FirmenNr, FibuNr);
auth_import := get_auth_import (FirmenNr);
auth_create := 0;
auth_modify := 0;

counter := 0;  
while counter < bucList.CountList() do begin		  
																  
	bucList.ReadBuchungFromList(counter);

	json_string := bucList.GetData ('veb-freifeld-a03');
	json_learned := bucList.GetData ('veb-freifeld-a01'); 		// wie heisst das genau
	buchsymbol := bucList.GetData ('buchsymbol');
	uid := bucList.GetData ('uidnr');
	
	error := bucList.GetErrorText();   
	if error  <> '' then begin
		MyLog ('Error: ' + error);
	end;
	vorerf_typ :=  is_kdlf_buch  (INPUT_FIRMENNR, INPUT_FIBUNR, buchsymbol); 
	if (vorerf_typ = BMD_VORERF_TYP_KUNDEN) then begin
		if auth_makro and AUTH_CRE_KUNDEN then begin
			auth_create := 1;
		end;
		if auth_makro and AUTH_UPD_KUNDEN then begin
			auth_modify := 1;
		end;

		if auth_import and AUTH_CRE_KUNDEN then begin
			auth_create := 1;
		end;  
		else begin
			auth_create := 0;
		end;

		if auth_import and AUTH_UPD_KUNDEN then begin
			auth_modify := 1;
		end;  
		else begin
			auth_modify := 0;
		end;
		MyLog ('auth_create KUNDEN auth_import: ' + BMD_FORMATNUM('0', auth_import) + ' auth_makro ' + BMD_FORMATNUM('0', auth_makro) + ' auth_create ' + BMD_FORMATNUM('0', auth_create));
	end;
	if (vorerf_typ = BMD_VORERF_TYP_LIEF) then begin
		if auth_makro and AUTH_CRE_LIEF then begin
			auth_create := 1;
		end;
		if auth_makro and AUTH_UPD_LIEF then begin
			auth_modify := 1;
		end;

		if auth_import and AUTH_CRE_LIEF then begin
			auth_create := 1;
		end;
		else begin
			auth_create := 0;
		end;

		if auth_import and AUTH_UPD_LIEF then begin
			auth_modify := 1;
		end;  
		else begin
			auth_modify := 0;
		end;
		MyLog ('auth_create LIEF auth_import: ' + BMD_FORMATNUM('0', auth_import) + ' auth_makro ' + BMD_FORMATNUM('0', auth_makro) + ' auth_create ' + BMD_FORMATNUM('0', auth_create));
	end;


	konto := bucList.GetData('konto');									
	
	if (vorerf_typ <> '') then begin   
		// MyLog ('before auth check');
		if ((auth_create = 1) AND ((konto = '0') or (konto = ''))) or ((auth_modify = 1) and (konto <> '0')) then begin
			// only result if insert/update flag gesetzt		
			do_insert_update := 0;
			fetch_json (json_string, json_learned, do_insert_update, name, zusatzname, strasse, ort, plz, land_id, uid, uid_status, iban1, iban2, iban3, uid_datum, kondition, zahlsperre, queue_id); 
			//MyLog ('after fetch_json: ' + json_string + ' kond: ' + kondition);
			konto := add_to_import_KDLF (konto, do_insert_update, uid, uid_datum, name, zusatzname, strasse, plz, land_id, iban1, iban2, iban3, kondition, zahlsperre, queue_id);
	
			buclist.SetData ('konto', konto);  
		end; 
		// MyLog ('after auth check');
	end;  
	
	bucList.WriteDataToBuchung(); 
	INC(counter);
								  
end;  


import_KDLF (actual_seq);

update_uid_datum;
									
counter := 0;
while counter < bucList.CountList() do begin

	bucList.ReadBuchungFromList(counter);
	INC(counter);
end;

count_vorerf := bucList.CountList();
MyLog ('ins Vorerfassung buchen: ' + BMD_FORMATNUM('0', count_vorerf) + ' records'); 

MyLog ('****************** Logisth.AI End Information ImportModell Periode/Kunden/Lieferanten **************');   

Result := 0;