pafTableUSERS_DB.pas

unit pafTableUSERS_DB;

interface
  uses

    gVariables;

  function fUSERS_DBGenID:integer;
  procedure pUSERS_DBInsertData(varID:integer; varFULLNAME, varPASS:string);
  function fUSERS_DBSelectFULLNAMEandID:TArray<Tusers_db>;
  function fUSERS_DBGetPASSbyID(varID: integer):string;


implementation

   uses
    IBX.ib,
    IBX.IBQuery,
    pafOtherFunction,
    System.SysUtils,
    pafDatabase
    ;

    const
    ModuleName = 'pafTableUSERS_DB';


//                            ---USERS_DB---
//******************************************************************************
//USERS_DB // ãåíåðàöèÿ ID òàáëèöû  USERS_DB
//******************************************************************************
function fUSERS_DBGenID:integer;
begin
result := fSQLGenID('select GEN_ID(GEN_USERS_DB_ID, 1) from RDB$DATABASE ');    //âûçûâàåì çàïðîñ íà ãåíåðàöèþ è ïîëó÷åíèå ðåçóëüòàòà
end;
//******************************************************************************
//******************************************************************************


//******************************************************************************
//USERS_DB // Äîáàâëåíèå çàïèñè â òàáëèöó USER_DB
//******************************************************************************
procedure pUSERS_DBInsertData(varID:integer; varFULLNAME, varPASS:string);
var
  varIBQuery: TIBQuery;
const
  FunctionName = 'pInsert_USERS_DB';

begin
  try
  pCreateIBQuery(varIBQuery);                                                   //èíèöèàëèçèðóåì IBQuery
  varIBQuery.Active := false;
  varIBQuery.SQL.Clear;
  varIBQuery.SQL.Add(
      'insert into USERS_DB '
    + '('
    + 'ID, '
    + 'FULLNAME, '
    + 'PASS '
    + ') '
    + 'values ('
    + ':varID, '
    + ':varFULLNAME, '
    + ':varPASS '
    + ')'
    );

  if  varID = 0               then varIBQuery.ParamByName('varID').IsNull              else  varIBQuery.ParamByName('varID').AsInteger:= varID;
  if  trim(varFULLNAME)=''    then varIBQuery.ParamByName('varFULLNAME').IsNull        else  varIBQuery.ParamByName('varFULLNAME').asstring:=trim(varFULLNAME);
  if  trim(varPASS)=''        then varIBQuery.ParamByName('varPASS').IsNull            else  varIBQuery.ParamByName('varPASS').asstring:=trim(varPASS);

  varIBQuery.ExecSQL;
  gIBTransaction.Commit;
  FreeAndNil(varIBQuery);
  except
  on e:EIBInterBaseError do
    begin                                                                       //âûâîäèì èíôîðìàöèþ
    pExceptionEIBInterBaseErrorSQL(e, ModuleName, FunctionName, varIBQuery.SQL.Text); //ãåíåðèðóåì îøèáêó è âûâîäèì äèàëîãîâîå îêíî
    end;
  end;
end;
//******************************************************************************
//******************************************************************************


//******************************************************************************
//USERS_DB // ïîëó÷àåì ID è FULLNAEM èç òàáëèöû USERS_DB
//******************************************************************************
function fUSERS_DBSelectFULLNAMEandID:TArray<Tusers_db>;
var
  varIBQuery: TIBQuery;
  i: integer;
  r: Tusers_db;
const
  FunctionName = 'fUSERS_DBSelectFULLNAMEandID';
begin
  try
  pCreateIBQuery(varIBQuery);                                                   //èíèöèàëèçèðóåì IBQuery
  varIBQuery.Active := false;
  varIBQuery.SQL.Clear;
  varIBQuery.SQL.Add(
      'select ID, FULLNAME '
    + 'from '
    + 'USERS_DB '
    + 'where '
    + 'DEL = 0 '
    );
  varIBQuery.Open;

  except                                                                        //âûçûâàåì èñêëþ÷åíèå ïðè îøèáêè â SQL çàïðîñå
  on e:EIBInterBaseError do
    begin                                                                       //âûâîäèì èíôîðìàöèþ
    pExceptionEIBInterBaseErrorSQL(e, ModuleName, FunctionName, varIBQuery.SQL.Text); //ãåíåðèðóåì îøèáêó è âûâîäèì äèàëîãîâîå îêíî
    end;
  end;

  try
  //ïîìåùàåì ðåçóëüòàò â ìàññèâ TArray                                          //âîçìîæíî äëÿ óñêîðåíèÿ ðàáîòû òðåáóåòñÿ èñïîëüçâîàòü Tlist
  Result := nil;
  while not varIBQuery.Eof do
    begin
    r.ID := varIBQuery.FieldByName('ID').AsInteger;
    r.FULLNAME := trim(varIBQuery.FieldByName('FULLNAME').AsString);
    insert( r ,result, length(result));
    varIBQuery.Next;
    end;

  except
  on e:Exception do
    begin
    pException(e, ModuleName, FunctionName);                                    //ãåíåðèðóåì îøèáêó è âûâîäèì äèàëîãîâîå îêíî
    end;
  end;

  FreeAndNil(varIBQuery);
end;
//******************************************************************************
//******************************************************************************

//******************************************************************************
//USERS_DB // ïîëó÷àåì ïàðîëü ïî ID òàáëèöû USERS_DB
//******************************************************************************
function fUSERS_DBGetPASSbyID(varID: integer):string;
var
  varIBQuery: TIBQuery;
const
  FunctionName = 'fSelect_PASSWORDbyID_USERS_DB';
begin
  try
  pCreateIBQuery(varIBQuery);                                                   //èíèöèàëèçèðóåì IBQuery
  varIBQuery.Active := false;
  varIBQuery.SQL.Clear;
  varIBQuery.SQL.Add(
      'select PASS '
    + 'from '
    + 'USERS_DB '
    + 'where '
    + 'ID = :varID '
    );

  if  varID = 0               then varIBQuery.ParamByName('varID').IsNull              else  varIBQuery.ParamByName('varID').AsInteger:= varID;
  varIBQuery.Open;

  except                                                                        //âûçûâàåì èñêëþ÷åíèå ïðè îøèáêè â SQL çàïðîñå
  on e:EIBInterBaseError do
    begin                                                                       //âûâîäèì èíôîðìàöèþ
    pExceptionEIBInterBaseErrorSQL(e, ModuleName, FunctionName, varIBQuery.SQL.Text); //ãåíåðèðóåì îøèáêó è âûâîäèì äèàëîãîâîå îêíî
    end;
  end;

  try
  result := trim(varIBQuery.FieldByName('PASS').AsString);                      //ïîëó÷àåì ñòðîêó ñ ïàðîëåì

  except
  on e:Exception do
    begin
    pException(e, ModuleName, FunctionName);                                    //ãåíåðèðóåì îøèáêó è âûâîäèì äèàëîãîâîå îêíî
    end;
  end;

  FreeAndNil(varIBQuery);
end;
//******************************************************************************
//******************************************************************************
end.