DELPHI-SQL. Запись в БД на основе данных в строке

[dima1208]

Здравствуйте, уважаемые форумчане.
Прошу помощи в решении проблемы.
Напишу сразу подробно, чтобы дать общую картину.
Пишу программу для учета пользователей на определенной железке.
База находится на сервере, mssql. Использую ADOConnection, ADOQuery.
Программа обменивается инфой с железкой по ssh.
В базе 2 таблицы, в них такие поля:
1 (main_table): LOGIN, FIO, PASSPORT - varchar
2 (log_table): LOGIN, DATE_IN, DATE_OUT - varchar, datetime

В main_table из программы заносится юзер, ему присваивается логин и пароль, и отправляется в железку.
Когда юзер авторизуется на железке со своим логином и паролем, в log_table записывается LOGIN и DATE_IN,
то есть имя пользователя и дата входа.

Пока делал без проверок таким кодом:

	ADOQuery1.SQL.Clear;
    	ADOQuery1.SQL.Add('INSERT INTO log_table([LOGIN], [DATE_IN]) '+
                   'values ('''+parsed_login+''','''+parsed_date+''');')
	ADOQuery1.Open;

Запись добавляется.

parsed_login, parsed_date - значения в эти переменные берутся из парсера строки, которую посылает железка.
Есть еще parsed_logout - имя пользователя, который разлогинился на железке.

parsed_login и parsed_logout имеют одинаковые значения, но на основе парсинга относятся ко входу или выходу.
То есть парсер выбирает это значение, если в строке из железки присутствует строка ":logged in", то
имя пользователя записывается в parsed_login, а если там есть ":logged out", то соответственно оно
записывается в parsed_logout.

Загвоздка у меня в следующем:
Перед записью в таблицу нужно проверить, есть ли в поле DATE_OUT к этому LOGIN записанное значение.
Если значение пустое, то в DATE_OUT добавляется дата/время выхода.

Не могу понять как сделать проверку:
Если у этого пользователя нет в таблице значения DATE_IN то

ADOQuery1.SQL.Add('INSERT INTO log_table([LOGIN], [DATE_IN]) '+
'values ('''+parsed_login+''','''+parsed_da te+''');')

а если DATE_IN есть, а DATE_OUT пусто - то добавить в существующую строку в поле DATE_OUT значение
из parsed_date.


Вроде ничего не упустил. Прошу помощи с запросом, со сложными запросами еще не сталкивался.
Заранее всем большое спасибо)

[evg_m]

правильнее будет переделать таблицу log_table
LOGIN id пользователя выполнившего операцию(действие)_user
DATE_LOG время выполнения операции
LOG_STATE идентификатор выполненной операции ('IN' | 'OUT' )

в этом случае ВСЕ записи будут идти через простой insert.

insert (login, date_log, state_log) values (:login, :date, 'IN')
insert (login, date_log, state_log) values (:login, :date, 'OUT')

таблицы должны обеспечивать удобство (и надежность) ХРАНЕНИЯ и накопления информации (НО НЕ ее отображение в удобном для пользователя виде)

а узнать КТО сейчас залогинен и другую интересующую инфу можно через ЛЮБОЙ достаточно сложный select.
Для формирования СТАНДАРТНЫХ (для пользователя) отображений (сокрытия сложности и сокращения конечного кода) при необходимости используются(пишутся) VIEW.

select main.name, last.lastdate, log.log_state 
from main
left join ( select login, max(date_log) as lastdate from log group by login  --последниЕ действия пользователей
       ) as last on last.login =main.id

left join log on log.login =last.login and login.date_log =last.lastdate

select main.name, statein.last_date as log_in, case when stateout.last_date>statein.last_date then stateout.last_date else NULL end as log_out
from main
left join ( select login, max(date_log) as lastdate from log where state_log='IN' group by login 
     ) as lastin on lastin.login =main.id
left join ( select login, max(date_log) as lastdate from log where state_log='OUT' group by logout 
     ) as lastout on lastout.login =main.id

select main.name, 
log_in.date_log as date_in,
( select top 1 date_log from log as log_out where  log_out.state='OUT' and log_out.login =log_in.login. and log_out.date_log>log_in.date_log order by log_out.date_log desc ) as date_out
from main
left join log as log_in on log_in.ligin =main.id and log_in.state='IN'
order by log_in.date_log

[dima1208]

evg_m, Спасибо большое за ответ, сейчас буду пробовать. И сам уже начал приходить к мысли, что неправильно сделал таблицы в начале.

[dima1208]

а какой тип данных ставить для LOG_STATE? И где прописать принимаемые значения IN/OUT ?

ПС Все понял, тупанул))

[dima1208]

[QUOTE=evg_m;1548369]

select main.name, statein.last_date as log_in, case when stateout.last_date>statein.last_date then stateout.last_date else NULL end as log_out
from main
left join ( select login, max(date_log) as lastdate from log where state_log='IN' group by login 
     ) as lastin on lastin.login =main.id
left join ( select login, max(date_log) as lastdate from log where state_log='OUT' group by logout 
     ) as lastout on lastout.login =main.id

Евгений, здравствуйте. Помогите еще раз, пожалуйста. Окончательно запутался. Если я понял верно код выше выводит список юзеров и время входа и выхода?
В таблицу нужно еще дополнительные колонки вставлять?
Мне нужно получить по определенному пользователю все записи со временем его входов и выходов, вида:
| LOGIN_ID | LOG_IN | LOG_OUT |
------------------------------------------------------------
| user1 | 12.12.12 08:00:00 | 12.12.12 08:11:00 |
------------------------------------------------------------
| user1 | 12.12.12 08:22:00 | 12.12.12 08:33:00 |

и так далее.

Получается выборку можно делать только по таблице log_table и не использовать JOIN? Нужно условие в запросе прописывать?

[evg_m]

Мне нужно получить по определенному пользователю все записи со временем его входов и выходов, вида:
| LOGIN_ID | LOG_IN | LOG_OUT |

select main.name, --инфа по пользователю
login.date_log as log_in,  -- инфа по ВСЕМ его входам
---------- получение ОДНОЙ записи о выходе (первой из всех после данного входа)
   ( select top 1 data_log  --только одна запись из
     from log as logout
     where logout.state='OUT'  --о выходе
        and logout.date_log >login.date_log --за время после указанного времени (времени входа ПО текущей записи) 
        and logout.id=login.id --для того же пользователя
      order by logout.date_log --сортируем по времени 
   ---и берем первую (top 1) т.е. наименьшее из выбранных  (из тех выходов что были ПОСЛЕ этого входа)
   ) as log_out 
----------
from main --чтобы иметь инфу о пользователе
left join log as login on log.id =main.id --для данного пользователя 
                             and log.state ='IN' -- и только по его входам

where main.id_user =:id

поскольку от main ничего не требуется (id_user есть и в log)

select id_user,  
 login.date_log as log_in,
---------- получение ОДНОЙ записи о выходе (первой из всех после данного входа)
   ( select top 1 data_log  --только одна запись из
     from log as logout
     where logout.state='OUT'  --о выходе
        and logout.date_log >login.date_log --за время после указанного времени (времени входа ПО текущей записи) 
        and logout.id=login.id --для того же пользователя
      order by logout.date_log --сортируем по времени 
   ---и берем первую (top 1) т.е. наименьшее из выбранных  (из тех выходов что были ПОСЛЕ этого входа)
   ) as log_out 
----------
from log as login
where login.state='IN'
and  lofin.id_user =:id

[dima1208]

Евгений, огромное спасибо! Вы очень помогли. Все получилось.

[dima1208]

Евгений, здравствуйте.
Помогите, пожалуйста, еще раз, снова в ступоре...

Я сделал такой запрос:

  rep_query:='SELECT login_id, login.date_log as log_in, '+             		//(один юзер за период)
             '(select top 1 date_log from log_table as logout '+
             'where logout.log_state = ''OUT'' '+
             'AND logout.date_log > login.date_log '+
             'AND logout.login_id = login.login_id '+
             'ORDER BY logout.date_log) AS log_out, mac '+
             'FROM log_table as login WHERE login.log_state = ''IN'' '+
             'AND login.date_log >= '''+DateToStr(DateTimePicker1.Date)+''''+
             ' AND login.date_log <= '''+datetostr(DateTimePicker2.Date+1)+''''+
             ' AND login.login_id = '''+DBGrid1.Fields[4].AsString+'''';

Настроил вывод в отчет, такого вида:
Сверху отдельно login_id и ФИО (выделенная строка из DBGrid1), а дальше таблица
___________________________________
| login.date_log | logout.date_log | mac |
----------------------------------------------

Теперь надо сделать, чтобы отображать всех юзеров, и к таблице справа прибавить две колонки:
___________________________________ ________________________________
| login.date_log | logout.date_log | mac | login_id | FAMILY+NAME+SURNAME |
-----------------------------------------------------------------------------------------
ФИО хранятся во второй таблице main_table. Как переделать запрос?
Никак я не могу понять эти JOINы. Если в конец добавляю JOIN - ругается на синтаксис возле JOIN.

  rep_query:='SELECT login_id, login.date_log as log_in, '+             		//(всех из базы за период)
             '(select top 1 date_log from log_table as logout '+
             'where logout.log_state = ''OUT'' '+
             'AND logout.date_log > login.date_log '+
             'AND logout.login_id = login.login_id '+
             'ORDER BY logout.date_log) AS log_out, mac '+
             'FROM log_table as login WHERE login.log_state = ''IN'' '+
             'AND login.date_log >= '''+DateToStr(DateTimePicker1.Date)+''''+
             ' AND login.date_log <= '''+datetostr(DateTimePicker2.Date+1)+''''+
             'AND login.login_id = login.login_id '
			 'RIGHT JOIN FAMILY, NAME, SURNAME main_table ON (login.login_id = main_table.login_id)'

[evg_m]

Если в конец добавляю JOIN - ругается на синтаксис возле JOIN.

так добавлять надо не куда попало (в конец), а во вполне определенное место (после FROM)

select ...
from ....
join .......
where ...

[dima1208]

rep_query:='SELECT login_id, login.date_log as log_in, '+             //(всех из базы за период)
             '(select top 1 date_log from log_table as logout '+
             'where logout.log_state = ''OUT'' '+
             'AND logout.date_log > login.date_log '+
             'AND logout.login_id = login.login_id '+

             'ORDER BY logout.date_log) AS log_out, mac, mt.family as fio '+
             'FROM log_table as login '+
             'LEFT JOIN main_table mt ON login.login_id = mt.login '+

             'WHERE login.log_state = ''IN'' '+
             'AND login.date_log >= '''+DateToStr(DateTimePicker1.Date)+''''+
             ' AND login.date_log <= '''+datetostr(DateTimePicker2.Date+1)+''''+
             'AND login.login_id = login.login_id ';

Если так делаю, запрос выполняется, но если хочу прочитать из query поле fio, выдает ошибку что такого поля нет(((