unit uBafComboHelper;

// this code is under the BAF fair use license (BFUL) - https://bafbal.de/index.php?title=Bful
// managing the lookup lists

interface

uses Classes, FMX.ListBox, System.SysUtils, System.StrUtils, DB,
    System.Types, System.Math, uStringIniFile, uBafTypes;

type
  TBafComboHelperSetValueEvent = procedure(Sender: TObject; var AValue: string) of object;

  TBafComboHelper = class(TStringList)
  private
    FFirstLeer: boolean;
    FGuidList: TStringList;     // sorted list for quick search
    FFiltered: TStringList;
    FLastQuery: TDateTime;
    FRefreshInterval: integer;
    FCommand: string;
    FFilled: boolean;
    FOnSetValue: TBafComboHelperSetValueEvent;
    function SetValue(AValue: string): string;
  public
    constructor Create(AFirstLeer: boolean);
    destructor Destroy; override;
    function AddGuidItem(ACaption, AGuid: string): integer;
    procedure Clear; override;
    procedure FillFromQuery(ASql, AKey, AValue, AName: string; AAdd: TStrings = nil);  // fetches the values from database
    procedure FillFromQueryParam(ASql, AKey, AValue, AParam: string);  // fetches the values from database
    procedure FillFromQueryDb(ASql, AKey, AValue, ADb: string);  // fetches the values from database (also non default db)
    procedure FillFromValueList(AValueList: TStrings);  // fetches the values from a ValueList

    function GetIniIndex(AIndex: integer; var AIni: TStringIniFile): boolean;
    function GetIniGUID(AGUID: string; var AIni: TStringIniFile): boolean;
    function GetIniCaption(ACaption: string; var AIni: TStringIniFile): boolean;
    function GetCaptionGuid(AGuid: string): string;
    function GetGuidFromCaption(ACaption: string): string;

    // ComboBox
    procedure FillComboBox(ACombo: TComboBox);          // assigns the values to a ComboBox
    function FillAndSet(ACombo: TComboBox; AGuid: string): boolean;
    function FillAndSetName(ACombo: TComboBox; AName: string): boolean;
    function GetGuid(ACombo: TComboBox): string;
    function GetName(ACombo: TComboBox): string;
    function GetGuidFromIndex(AIndex: integer): string;
    function SetGuid(ACombo: TComboBox; AGuid: string): boolean;
    function SetName(ACombo: TComboBox; AName: string): boolean;

    property LastQuery: TDateTime read FLastQuery write FLastQuery;
    property RefreshInterval: integer read FRefreshInterval write FRefreshInterval;
    property OnSetValue: TBafComboHelperSetValueEvent read FOnSetValue write FOnSetValue;
    property Command: string read FCommand write FCommand;  // for LookupLive
    property Filled: boolean read FFilled write FFilled;
  end;


implementation

{ TGuidComboHelper }

uses dmMain, uBafTranslationModule;

function TBafComboHelper.AddGuidItem(ACaption, AGuid: string): integer;
var
  LIni: TStringIniFile;
begin
  LIni := TStringIniFile.Create('');
  LIni.WriteString(SEC_DATA, ITEM_GUID, AGuid);
  result := AddObject(ACaption, LIni);
  FGuidList.AddObject(AGuid, TObject(result));
end;

procedure TBafComboHelper.Clear;
begin
  inherited;
  FGuidList.Clear;

end;

constructor TBafComboHelper.Create(AFirstLeer: boolean);
begin
  inherited;
  OwnsObjects := true;
  FGuidList := TStringList.Create;
  FGuidList.Sorted := true;
  FGuidList.Duplicates := dupError;
  FFirstLeer := AFirstLeer;
  FFiltered := TStringList.Create;
end;

destructor TBafComboHelper.Destroy;
begin
  FreeAndNil(FGuidList);
  FreeAndNil(FFiltered);
  inherited;
end;

function TBafComboHelper.FillAndSet(ACombo: TComboBox; AGuid: string): boolean;
begin
  FillComboBox(ACombo);
  result := SetGuid(ACombo, AGuid);
end;

function TBafComboHelper.FillAndSetName(ACombo: TComboBox; AName: string): boolean;
begin
  FillComboBox(ACombo);
  result := SetName(ACombo, AName);
end;

procedure TBafComboHelper.FillComboBox(ACombo: TComboBox);
begin
  ACombo.Items.Assign(Self);
end;

procedure TBafComboHelper.FillFromQuery(ASql, AKey, AValue, AName: string; AAdd: TStrings = nil);
var
  i, LIndex: integer;
  LIni: TStringIniFile;
  LParams: TBafParams;
  LDataSet: TDataSet;
begin
  Clear;
  if FFirstLeer then
    AddGuidItem('', '');
  LParams := dataMain.QueryPrepare(dataMain.DefaultCon, 'TBafComboHelper', ASql);
  if LParams.Count = 3 then begin
    LParams.ParamAsString('klangid', gvLanguageId);
    LParams.ParamAsString('klangid2', gvLanguageId2);
    LParams.ParamAsString('kname', AName);
  end;
  LDataSet := dataMain.QueryOpen(dataMain.DefaultCon, 'TBafComboHelper');
  while not LDataSet.Eof do begin
    LIndex := AddGuidItem(SetValue(LDataSet.FieldByName(AValue).AsString),
        LDataSet.FieldByName(AKey).AsString);
    if Assigned(AAdd) and GetIniIndex(LIndex, LIni) then begin
      for i := 0 to AAdd.Count - 1 do
        LIni.WriteString(SEC_ADD, AAdd[i], LDataSet.FieldByName(AAdd[i]).AsString);
    end;
    LDataSet.Next;
  end;
  FLastQuery := now;
end;

procedure TBafComboHelper.FillFromQueryDb(ASql, AKey, AValue, ADb: string);
var
  LDataSet: TDataSet;
begin
  Clear;
  if FFirstLeer then
    AddGuidItem('', '');
  dataMain.QueryPrepare(ADb, 'TBafComboHelper', ASql);
  LDataSet := dataMain.QueryOpen(ADb, 'TBafComboHelper');
  while not LDataSet.Eof do begin
    AddGuidItem(SetValue(LDataSet.FieldByName(AValue).AsString),
        LDataSet.FieldByName(AKey).AsString);
    LDataSet.Next;
  end;
  FLastQuery := now;
end;

procedure TBafComboHelper.FillFromQueryParam(ASql, AKey, AValue, AParam: string);
var
  LParams: TBafParams;
  LDataSet: TDataSet;
begin
  Clear;
  if FFirstLeer then
    AddGuidItem('', '');
  LParams := dataMain.QueryPrepare(dataMain.DefaultCon, 'TBafComboHelper', ASql);
  LParams.ParamAsString(0, AParam);
  LDataSet := dataMain.QueryOpen(dataMain.DefaultCon, 'TBafComboHelper');
  while not LDataSet.Eof do begin
    AddGuidItem(SetValue(LDataSet.FieldByName(AValue).AsString),
        LDataSet.FieldByName(AKey).AsString);
    LDataSet.Next;
  end;
  FLastQuery := now;
end;

procedure TBafComboHelper.FillFromValueList(AValueList: TStrings);
var
  i: integer;
begin
  Clear;
  if FFirstLeer then
    AddGuidItem('', '');
  for i := 0 to AValueList.Count - 1 do
    AddGuidItem(AValueList.ValueFromIndex[i], AValueList.Names[i]);
end;

function TBafComboHelper.GetCaptionGuid(AGuid: string): string;
var
  ix: integer;
begin
  result := '';
  if AGuid <> '' then begin
    ix := FGuidList.IndexOf(AGuid);
    if ix >= 0 then begin
      ix := integer(FGuidList.Objects[ix]);
      if ix >= 0 then
        result := Strings[ix];
    end;
  end;
end;

function TBafComboHelper.GetGuid(ACombo: TComboBox): string;
var
  LIni: TStringIniFile;
begin
  result := '';
  if GetIniIndex(ACombo.ItemIndex, LIni) then
    result := LIni.ReadString(SEC_DATA, ITEM_GUID, '');
end;

function TBafComboHelper.GetGuidFromCaption(ACaption: string): string;
var
  LIni: TStringIniFile;
begin
  result := '';
  if GetIniIndex(IndexOf(ACaption), LIni) then
    result := LIni.ReadString(SEC_DATA, ITEM_GUID, '');
end;

function TBafComboHelper.GetGuidFromIndex(AIndex: integer): string;
var
  LIni: TStringIniFile;
begin
  result := '';
  if GetIniIndex(AIndex, LIni) then
    result := LIni.ReadString(SEC_DATA, ITEM_GUID, '');
end;

function TBafComboHelper.GetIniCaption(ACaption: string; var AIni: TStringIniFile): boolean;
var
  LIndex: integer;
begin
  LIndex := IndexOf(ACaption);
  result := GetIniIndex(LIndex, AIni);
end;

function TBafComboHelper.GetIniGUID(AGUID: string; var AIni: TStringIniFile): boolean;
var
  LIndex: integer;
begin
  LIndex := FGuidList.IndexOf(AGUID);
  result := GetIniIndex(LIndex, AIni);
end;

function TBafComboHelper.GetIniIndex(AIndex: integer; var AIni: TStringIniFile): boolean;
begin
  result := (AIndex >= 0) and (AIndex < Count);
  if result then
    AIni := (Objects[AIndex] as TStringIniFile);
end;

function TBafComboHelper.GetName(ACombo: TComboBox): string;
begin
  result := ACombo.Items[ACombo.ItemIndex];
end;

function TBafComboHelper.SetGuid(ACombo: TComboBox; AGuid: string): boolean;
var
  ix, ix2: integer;
begin
  result := false;
  ix := -1 + integer(FFirstLeer);
  if AGuid = '' then
    result := true
  else begin
    ix2 := FGuidList.IndexOf(AGuid);
    if ix2 > -1 then begin
      ix := integer(FGuidList.Objects[ix2]);
      result := true;
    end;
  end;
  ACombo.ItemIndex := ix;
end;

function TBafComboHelper.SetName(ACombo: TComboBox; AName: string): boolean;
var
  ix: integer;
begin
  ix := IndexOf(AName);
  result := (ix > -1);
  ACombo.ItemIndex := ix;
end;

function TBafComboHelper.SetValue(AValue: string): string;
var
  s: string;
begin
  s := AValue;
  if Assigned(FOnSetValue) then
    FOnSetValue(Self, s);
  result := s;
end;

end.
