Search options (Page 1 of 7)

var
   sUser: string = '';
   idUser: integer = 0;
   slLastAddedID: TStringList;

procedure Form1_GridEmployees_OnChange (Sender: string);
var
    i,c, k, l: integer;
    sID: string;
begin
    c := Form1.GridEmployees.RowCount-1;
    l := Form1.GridEmployees.Columns.Count-1;

    Form1.GridEmployees.BeginUpdate;
    for i := 0 to c do
    begin
        sID := IntToStr(Form1.GridEmployees.dbIndexToID(i));

        if slLastAddedID.IndexOf(sID) > -1 then
            for k := 0 to l do
               Form1.GridEmployees.Cell[k, i].Color := clRed;

    end;
    Form1.GridEmployees.EndUpdate;

end;

procedure frmEmployee_OnShow (Sender: string; Action: string);
begin
    if Action = 'NewRecord' then frmEmployee.ComboBox1.dbItemID := idUser;
end;

procedure frmEmployee_bOK_OnClick (Sender: string; var Cancel: boolean);
begin
    // save date create of record
    if frmEmployee.dbAction='NewRecord' then
    begin
        frmEmployee.DateTimePicker1.DateTime := now;
        frmEmployee.DateTimePicker2.DateTime := now;
    end;
end;

procedure Form1_OnShow (Sender: string; Action: string);
var
    sDateLogin, sDateLogout: string;
begin
     // Initial check whether there is a database administrator user
     // if not, create an administrator with username admin and password admin
     if SQLExecute('SELECT count(id) FROM users;') = '0' then SQLExecute('INSERT INTO users (login, password) VALUES ("admin", "admin");');

     frmLogin.edPassword.PasswordChar := '*';
     frmLogin.ShowModal; // show windows for login

     // get ID of last added records to slLastAddedID
     if idUser<>0 then
     begin
         sDateLogin := SQLExecute('SELECT DATE_FORMAT(logindate,"%Y-%m-%d %H:%i:%S.%f") FROM users WHERE id='+IntToStr(idUser));
         sDateLogout := SQLExecute('SELECT DATE_FORMAT(logoutdate,"%Y-%m-%d %H:%i:%S.%f") FROM users WHERE id='+IntToStr(idUser));
         if (sDateLogin <> '') and (sDateLogout<>'') then
         begin
             slLastAddedID.Text := SQLExecute('SELECT CONCAT_WS(char(13),`id`) FROM employees WHERE ' + 'DATE_FORMAT(created,"%Y-%m-%d %H:%i:%S.%f") > "'+sDateLogout+'" AND DATE_FORMAT(created,"%Y-%m-%d %H:%i:%S.%f") < "'+sDateLogin+'"');
             Form1.GridEmployees.dbUpdate;
             if slLastAddedID.Count>0 then
             ShowMessage('Добалено '+IntToStr(slLastAddedID.Count)+' записи(ей)' );
         end;
     end;

end;

// save time of logout current user
procedure Form1_OnClose (Sender: string; Action: string);
begin
    SQLExecute('UPDATE users SET logoutdate=DATE_FORMAT(now(),"%Y-%m-%d %H:%i:%S.%f") WHERE id='+IntToStr(idUser));

    slLastAddedID.Free;

end;

procedure frmLogin_bLogin_OnClick (Sender: string; var Cancel: boolean);
var
   s: string;
begin
     // SQL query for check user and password
     s := SQLExecute('SELECT count(id) FROM users WHERE (id = ' + frmLogin.cbLogin.sqlValue + ') AND (password = ' + frmLogin.edPassword.sqlValue + ');');

     // if SQL query found login and password
     if (s <> '0') and (s<>'') then
     begin
          idUser := frmLogin.cbLogin.dbItemID;
          SQLExecute('UPDATE users SET logindate=DATE_FORMAT(now(),"%Y-%m-%d %H:%i:%S.%f") WHERE id='+IntToStr(idUser));

          sUser := frmLogin.cbLogin.Text; // remember username
          Form1.Caption := 'User: ' + sUser;
          frmLogin.Close; // close login form
     end else MessageDlg('password is incorrect', mtError, mbOk, 0); // message if the password is incorrect

end;

// window closing event for login
procedure frmLogin_OnClose (Sender: string; Action: string);
begin
     // if the user has not been defined, and the user closes the login window, then close the main form
     if sUser='' then Form1.Close;
end;

begin
     slLastAddedID := TStringList.Create;
     frmLogin.Button1.Default := True;
end.

SQLExecute('SELECT group_concat(id, Char(13)) FROM employees WHERE ' + 'datetime(created) > datetime("'+sDateLogout+'") AND datetime(created) < datetime("'+sDateLogin+'")');

SQLExecute('UPDATE users SET logindate=datetime(''now'', ''localtime'') WHERE id='+IntToStr(idUser));

SQLExecute('UPDATE users SET logindate="'+FormatDateTime('yyyy-MM-DD hh:nn:ss.000', now)+'" WHERE id='+IntToStr(idUser));