{------------------------------------------------------------------------------}
{
   Unit Name: FieldValues
   Purpose  : To make it easier to collect an Insert and Update query
              for SQL
   Author   : Vesa Lappalainen
   Date     : 10.08.00
   Changed  :

   ToDo     :            
} 
{------------------------------------------------------------------------------}

unit FieldValues;

interface
uses IniFiles,dbtables;

type TFieldsValues = class
  private
    fields : string;
    values : string;
    uds    : string;
  public
    constructor Create;
    destructor Destroy; override;
    procedure Init; virtual;
    procedure AddFV(const name,s:string; const value:string ='###'); virtual;
    procedure AddFVU(const name, s:string; const value:string ='###'); virtual;
    procedure AddFVUI(const name,section:string; ini:TIniFile); virtual;
    function IsOk : boolean; virtual;
    function GetFieldValues : string; virtual;
    function GetUpdateString:string; virtual;
    function Insert(query:TQuery;const dbname:string):boolean; virtual;
    function Update(query:TQuery;const dbname,where:string):boolean; virtual;
    function InsertOrUpdate(query:TQuery;const dbname,where:string):boolean; virtual;

    property FieldValues : string read GetFieldValues;
    property UpdateString : string read GetUpdateString;
end;


function Quote(const s:string; const q:string='"'):string;
function UpdateStr(const name,s:string; const value:string ='###'):string;
function FixUpdateStr(var s:string):boolean;

implementation

uses sqlcom;

function Quote(const s:string; const q:string='"'):string;
begin
  if ( Length(s)>0 ) and ( s[1] = q ) then begin Result := s; exit; end;
  Result := q+s+q;
end;


function UpdateStr(const name,s:string; const value:string ='###'):string;
var v:string;
begin
  Result := '';
  v := s; if ( value <> '###' ) then v := value;
  if s <> '' then Result := name + '=' + Quote(v,'"') + ',';
end;


procedure TFieldsValues.Init;
begin
  fields := ''; values := ''; uds := '';
end;

constructor TFieldsValues.Create;
begin
  inherited;
  Init;
end;

procedure TFieldsValues.AddFV(const name,s:string; const value:string ='###');
var v:string;
begin
  v := s; if ( value <> '###' ) then v := value;
  if s <> '' then begin
    fields := fields + name         + ',';
    values := values + Quote(v,'"') + ',';
  end;
end;

procedure TFieldsValues.AddFVU(const name,s:string; const value:string ='###');
begin
  AddFV(name,s,value);
  uds := uds + UpdateStr(name,s,value);
end;

procedure TFieldsValues.AddFVUI(const name,section:string; ini:TIniFile);
var s:string;
begin
  s := ini.ReadString(section,name,'');
  AddFVU(name,s);
end;

function TFieldsValues.Insert(query:TQuery;const dbname:string):boolean;
begin
  Result := false;
  if ( self = nil ) then exit;
  if not IsOk then exit;
  ExecSQL(query,'insert into ' + dbname + ' ' + FieldValues);
  Result := true;
end;

function TFieldsValues.Update(query:TQuery;const dbname,where:string):boolean;
var uds : string;
begin
  Result := false;
  if ( self = nil ) then exit;
  uds := UpdateString;
  if ( uds = '' ) then exit;
  ExecSQL(query,'update ' + dbname + ' set ' + uds + ' ' + where);
end;

function TFieldsValues.InsertOrUpdate(query:TQuery;const dbname,where:string):boolean;
begin
  Result := false;
  if ( self = nil ) then exit;
  try
    Result := Insert(query,dbname);
  except
    try // if line exists, then update
      Result := Update(query,dbname,where);
    except
    end;
  end;
end;


function FixUpdateStr(var s:string):boolean;
var len : integer;
begin
  Result := false;
  len := Length(s);
  if ( len = 0 ) then exit;
  if ( s[len]=',' ) then Delete(s,len,1);
  if ( s = '' ) then exit;
  Result := true;
end;

function TFieldsValues.IsOK : boolean;
begin
  Result := fields <> ',';
end;

function TFieldsValues.GetFieldValues:string;
var f,v:string;
begin
  f := fields; v := values; 
  FixUpdateStr(f);
  FixUpdateStr(v);
  Result := '(' + f + ') values (' + v + ')';
end;

function TFieldsValues.GetUpdateString:string;
begin
  Result := uds;
  FixUpdateStr(Result);
end;

destructor TFieldsValues.Destroy;
begin
  inherited;
end;


end.