unit DbDatasetUtils;

interface
uses
  DBCtrlsEh, GridsEh, DBGridEh, hyperstr, forms, variants,
  SysUtils, Classes, DB, FIBDataSet, pFIBDataSet, FIBDatabase, pFIBDatabase,
  dialogs, FIBQuery, pFIBQuery, ExtCtrls, graphics, ImgList, Controls,
  SIBEABase, SIBFIBEA;

  function queryValueInteger(db: TFIBDatabase; sql: String): integer;
  procedure openDataSource(ds: TpFIBDataSet; dsrc: TDataSource = nil; useCurrec: boolean = true);
  procedure closeDataSource(ds: TpFIBDataSet; useCurrec: boolean = true);
  procedure reopenDataSet(ds: TpFIBDataSet);
  procedure applyFilter(ds: TpFIBDataSet; filter: string; dss: array of TpFIBDataSet);
  function andFilterExpr(columns: array of TColumnEh): string;
  procedure postIfCan(ds: TpFIBDataSet);
  procedure insertRecord(ds: TpFIBDataSet);
  procedure deleteRecord(ds: TpFIBDataSet; msg: string);
  procedure setDeletedRecord(ds: TpFIBDataSet; msg: string);
  procedure unsetDeletedRecord(ds: TpFIBDataSet; field, value: string);
  function isApplyEnabled(dss: array of TpFIBDataSet): boolean;
  procedure fetchItems(ds: TpFIBDataSet; itemss: array of TStrings);
  procedure populateCombobox(ds: TpFIBDataSet; combo: TDBComboBoxEh; topItemText: string);
  procedure setParamFromCombobox(ds: TpFIBDataSet; combo: TDBComboBoxEh; param: string);
  function withQuotes(s: string): string;



implementation
uses
  DbUtils,
  infodlg,
  Users,
  logger,
  Status,
  RpVisualGlobal,
  RpVisualParams;

var
  currecs: TStringList;


function queryValueInteger(db: TFIBDatabase; sql: String): integer;
var
  v: variant;
begin
  v := db.QueryValue(sql, 0);
  if VarIsNull(v) then
    result := 0
  else
    result := v;
end;


procedure openDataSource(ds: TpFIBDataSet; dsrc: TDataSource = nil; useCurrec: boolean = true);
var
  i, currec: integer;
begin
  currec := 0;

  if currecs.Find(IntToStr(Integer(ds)), i) then
    currec := integer(currecs.Objects[i]);

  if ds.Database.Connected then begin
    try
      if not ds.Active then begin
        ds.Open;
        if ds.Locate('ID', currec, []) then begin
  //        LoggerSaveMessage('    found!');
        end;
        if dsrc <> nil then
          dsrc.DataSet := ds;
      end;
    except
    end;
  end;
end;


procedure closeDataSource(ds: TpFIBDataSet; useCurrec: boolean = true);
var
  i, currec: integer;
begin
  currec := 0;

  if ds.Database.Connected then begin
    try
      currec := ds.fieldbyname('ID').AsInteger;
      ds.Close;
      ds.Active := false;
    except
    end;
  end;


  if useCurrec then begin
    if currecs.Find(IntToStr(Integer(ds)), i) then begin
      currecs.Objects[i] := pointer(currec);
    end else begin
      currecs.AddObject(IntToStr(Integer(ds)), pointer(currec));
    end;
  end;
end;


procedure reopenDataSet(ds: TpFIBDataSet);
begin
  closeDataSource(ds);
  openDataSource(ds, nil);
end;


procedure applyFilter(ds: TpFIBDataSet; filter: string; dss: array of TpFIBDataSet);
const
  FILTER_BEGIN = '-- filter begin';
  FILTER_END = '-- filter end';
var
  sql: string;
  i,k1,k2: integer;
begin
  if ds.Database.Connected then begin
    sql := ds.SQLs.SelectSQL.Text;
    k1 := pos(FILTER_BEGIN, sql);
    k2 := pos(FILTER_END, sql);
    if (k1 > 0) and (k2 > 0) then begin
      sql := copy(sql, 1, k1 + length(FILTER_BEGIN) + 1)
            + ' ' + filter + ' '
            + #13#10 + copy(sql, k2 - 2, length(sql) - k2 + 1);

      closeDataSource(ds);
      for i:=0 to length(dss)-1 do
        closeDataSource(dss[i], false);

      ds.SQLs.SelectSQL.Text := sql;
      openDataSource(ds, nil);
      for i:=0 to length(dss)-1 do
        openDataSource(dss[i], nil, false);
    end;
  end;
end;


function andFilterExpr(columns: array of TColumnEh): string;
var
  expr, fname: string;
  field: TField;
  i: integer;
begin
  result := '';

  for i:=0 to length(columns)-1 do begin
    expr := columns[i].STFilter.ExpressionStr;
    field := columns[i].Field;

    if expr = '' then
      continue;

      if field.Origin = '' then
      fname := field.FieldName
    else
      fname := field.Origin;

    if field.DataType = ftString then
      result := 'AND ' + fname + ' CONTAINING ''' + expr + '''';

    if field.DataType = ftInteger then
      result := 'AND ' + fname + '=' + expr;
  end;

  // todo: process other data types
end;


procedure postIfCan(ds: TpFIBDataSet);
begin
  try
    if ds.Database.Connected then
      if ds.State in [dsEdit, dsInsert] then
        if (ds.CanEdit) then
          ds.Post;
  except
    // todo 
  end;
end;


procedure insertRecord(ds: TpFIBDataSet);
begin
  if ds.Database.Connected then
    if ds.CanInsert then
      ds.Append;
end;


procedure deleteRecord(ds: TpFIBDataSet; msg: string);
begin
  try
    if (ds.Database.Connected) and (ds.CanDelete) and (not ds.Eof) then begin
      if showInfoDlg(msg, mtConfirmation, [mbYes, mbNo], 0) <> mrYes then
        exit;
      ds.Delete;
    end;
  except
    // todo
  end;
end;


procedure setDeletedRecord(ds: TpFIBDataSet; msg: string);
begin
  try
    if (ds.Database.Connected) and (not ds.Eof) then begin
      if showInfoDlg(msg, mtConfirmation, [mbYes, mbNo], 0) <> mrYes then
        exit;
      ds.Edit;
      ds.FieldByName('DELETED').AsInteger := 1;
      ds.Post;
      ds.Next;
      if ds.Eof then
        ds.Prior;
      ds.ReopenLocate('ID');
    end;
  except
    // todo
  end;
end;


procedure unsetDeletedRecord(ds: TpFIBDataSet; field, value: string);
var
  id: integer;
begin
  try

    if ds.Database.Connected then begin
      ds.Database.Execute(format('update %s set deleted=0 where %s=%s',
          [ds.AutoUpdateOptions.UpdateTableName, field, value]));

      ds.ReopenLocate('ID');

      id := queryValueInteger(
            ds.database,
            format('select id from %s where %s=%s',
                  [ds.AutoUpdateOptions.UpdateTableName, field, value]));

      ds.Locate('ID', id, []);
    end;
  except
    // todo
  end;
end;



function isApplyEnabled(dss: array of TpFIBDataSet): boolean;
var
  i: integer;
begin
  result := false;
  for i:=0 to length(dss)-1 do
    result := (result) or (dss[i].State in [dsEdit, dsInsert]);
end;


procedure fetchItems(ds: TpFIBDataSet; itemss: array of TStrings);
var
  i: integer;
begin
  for i:=0 to length(itemss)-1 do
    itemss[i].Clear;

  if ds.Database.Connected then begin
    openDataSource(ds, nil, false);
    while not ds.Eof do begin
      for i:=0 to length(itemss)-1 do
        itemss[i].Add( ds.Fields[i].AsString);
      ds.Next;  
    end;
    closeDataSource(ds, false);
  end;
end;


procedure populateCombobox(ds: TpFIBDataSet; combo: TDBComboBoxEh; topItemText: string);
var
  s: string;
begin
  s := combo.Text;
  fetchItems( ds, [combo.KeyItems, combo.Items]);
  if topItemText <> '' then begin
    combo.KeyItems.Insert(0, '0');
    combo.Items.Insert(0, topItemText);
  end;
  combo.Text := s;

  if (combo.ItemIndex < 0) and (combo.Items.Count > 0) then
    combo.ItemIndex := 0
end;


procedure setParamFromCombobox(ds: TpFIBDataSet; combo: TDBComboBoxEh; param: string);
begin
  if combo.ItemIndex >= 0 then
    ds.ParamByName(param).AsInteger := strtoint(combo.KeyItems[combo.ItemIndex]);
end;









function withQuotes(s: string): string;
begin
  result := '''' + s + '''';
end;


initialization
  currecs := TStringList.Create;
  currecs.Sorted := true;

finalization
  currecs.Free;

end.
