Форум программистов
 

Восстановите пароль или Зарегистрируйтесь на форуме, о проблемах и с заказом рекламы пишите сюда - alarforum@yandex.ru, проверяйте папку спам!

Вернуться   Форум программистов > Web программирование > SQL, базы данных
Регистрация

Восстановить пароль
Повторная активизация e-mail

Купить рекламу на форуме - 42 тыс руб за месяц

Ответ
 
Опции темы Поиск в этой теме
Старый 29.06.2022, 16:31   #1
deoleynikov
 
Регистрация: 10.08.2017
Сообщений: 7
Вопрос запрос к СКУД - время отсутствия

Добрый день. Есть у нас СКУД. БД на firebird 1.5. Вся такая закрытая. Поручили сделать запрос для получения времени отсутствия человека на рабочем месте. НУ, чтобы каждый мог контролировать свое время и не перегуливать в курилке.
для отладки просто дали копию БД. Инструкций нет, естессно. Вот, нагородил запросик, который за выбранный день получает все выходы за территорию офиса.

Тестирую на каком-то последнем firebird, так что я понимаю, что на старом может и не пойти - мне сам принцип важен.

Код:
select
       in1
     , out1
     , ((out1-in1)*24*60*60 )
from
       (
                 SELECT
                           el.EV_DATETIME as in1  
                         , el.MSG_ID
                         , min (el1.EV_DATETIME) as out1
                 FROM
                           EVLOG as el
                           left join
                                     (
                                               SELECT
                                                         EV_DATETIME
                                                       , MSG_ID
                                                       , VR.DEV_GID
                                                       , USER_ID
                                                       , VR.AREA_ENTER
                                               FROM
                                                         EVLOG
                                                         left join
                                                                   V_READERS VR
                                                                   on
                                                                             EVLOG.DEV_GID = VR.DEV_GID
                                               where
                                                         MSG_ID   =31 -- событие начала прохода считывателя
                                                         and USER_ID =343  -- пользователь для теста
                                                         and not vr.AREA_ENTER  =2 -- признак нахождения за пределами офиса
                                                         and cast ( EV_DATETIME as DATE) >= cast ('TODAY' as DATE)-1-7  -- принимаем день 7 дней назад
                                                         and cast ( EV_DATETIME as DATE)  < cast ('TODAY' as DATE)-7
                                     )
                                     as el1
                                     on
                                               el.USER_ID         =el1.USER_ID
                                               and el.EV_DATETIME<=el1.EV_DATETIME
                                               and not el.DEV_GID =el1.DEV_GID
                           left join
                                     V_READERS VR
                                     on
                                               el.DEV_GID = VR.DEV_GID
                 where
                           el.MSG_ID                           =30 -- окончание прохода считывателя
                           and el.USER_ID                      =343
                           and VR.AREA_ENTER                   =2
                           and cast ( el.EV_DATETIME as DATE) >= cast ('TODAY' as DATE)-1-7
                           and cast ( el.EV_DATETIME as DATE)  < cast ('TODAY' as DATE)-7
                 group by
                           el.EV_DATETIME
                         , el.MSG_ID
       )
       as que
where
       in1<out1
;

Выдается табличка вида:

выход из офиса , возврат в офис , сколько секунд отсутствовал
------------------------------------------------------------------------
2022-06-21 09:44:00,2022-06-21 09:54:39,638.999971200
2022-06-21 10:32:45,2022-06-21 10:36:31,226.000022400
2022-06-21 11:48:31,2022-06-21 11:55:01,390.000009600
2022-06-21 12:27:51,2022-06-21 12:51:27,1416.000009600
2022-06-21 13:24:37,2022-06-21 13:27:41,184.000032000
2022-06-21 14:04:55,2022-06-21 14:08:18,202.999996800
2022-06-21 15:11:07,2022-06-21 15:21:46,638.999971200
2022-06-21 16:19:46,2022-06-21 16:26:32,405.999993600
2022-06-21 17:17:09,2022-06-21 17:21:45,275.999961600


Вопрос: как сделать учет рабочего времени? то есть не учитывать время проведенное вне офиса до и после рабочего дня? Например, если пришел рано в 8:45 вышел, в 9:10 вернулся. Учитывать отсутствие не 25 минут, а 10. Примем рабочий день с 9:00 по 18:00. Помогите, я уже в какие-то дебри полез и запутался в небольшом запросе.

ЗЫ: я сам не скульщик, просто рядом проходил.

Последний раз редактировалось deoleynikov; 29.06.2022 в 16:34. Причина: формат
deoleynikov вне форума Ответить с цитированием
Старый 29.06.2022, 16:44   #2
Valick
Форумчанин
 
Регистрация: 27.04.2022
Сообщений: 483
По умолчанию

Вы забыли таблицу с данными показать.
Valick вне форума Ответить с цитированием
Старый 29.06.2022, 17:00   #3
deoleynikov
 
Регистрация: 10.08.2017
Сообщений: 7
По умолчанию

Цитата:
Сообщение от Valick Посмотреть сообщение
Вы забыли таблицу с данными показать.
там просто записи со временем события и его характеристиками - считыватель, пользователь, вид события 30-начало прохода, 31 - завершение прохода. по считывателю вытаскиваем направление (в офис или из офиса)

связываем таблицу событий с собой по правилу - начало события с окончанием события (то есть, следующее событие для этого же пользователя на возврат в зону, откуда он вышел )

пример EVLOG
SN,EV_DATETIME,MSG_ID,DEV_GID,CARD_ NUM,USER_ID,OP_ID,ACK_NEED,ACK_DONE ,EV_DATA,CONSOLE_ID,DEPT_ID,DEV_TYP E,GROUP_ID,HAS_VIDEO
5033835,2022-04-23 01:13:04,30,1002,8682381,384,,0,1,, 6001,1,1,1,
5033836,2022-04-23 01:13:07,31,1002,8682381,384,,0,1,, 6001,1,1,1,
5033837,2022-04-23 01:13:07,30,1002,8682274,353,,0,1,, 6001,1,1,1,
5033838,2022-04-23 01:13:09,31,1002,8682274,353,,0,1,, 6001,1,1,1,
5033839,2022-04-23 01:14:30,30,1001,8682274,353,,0,1,, 6001,1,1,1,
5033840,2022-04-23 01:14:32,31,1001,8682274,353,,0,1,, 6001,1,1,1,
5033841,2022-04-23 01:17:24,30,1001,8682381,384,,0,1,, 6001,1,1,1,
5033842,2022-04-23 01:17:25,31,1001,8682381,384,,0,1,, 6001,1,1,1,
5033843,2022-04-23 01:36:40,30,1002,8682274,353,,0,1,, 6001,1,1,1,
5033844,2022-04-23 01:36:43,31,1002,8682274,353,,0,1,, 6001,1,1,1,

V_READERS
DEV_GID,NAME,APDA_ID,RDR_NUM,TYPE,P ROG,FLAGS,HO_TIME,LOCKER_TIME,LOCKE R_DELAY,AREA_ENTER,AREA_EXIT,RDR_MO DE,OPENED_MODE,NORMAL_MODE,CLOSED_M ODE,TA_TYPE
1001,ВХОД,1,1,1,3,1870,2,2,0,1,2,1, 1,1,1,1
1002,ВЫХОД,1,2,1,3,1870,2,2,0,2,1,1 ,1,1,1,2
1003,вход1,2,1,1,3,1870,2,2,0,1,2,1 ,1,1,1,1
1004,выход2,2,2,1,3,1870,2,2,0,2,1, 1,1,1,1,2

AREAS
AREA_ID,PARENT_ID,NAME,STATUS,CNT_E MP,CNT_GST,CNT_ALL
1,,Предприятие,1,25,0,25
2,,Вне предприятия,1,15,0,15




Вот примеры данных

Последний раз редактировалось deoleynikov; 29.06.2022 в 17:14.
deoleynikov вне форума Ответить с цитированием
Старый 29.06.2022, 17:50   #4
Valick
Форумчанин
 
Регистрация: 27.04.2022
Сообщений: 483
По умолчанию

А что в таблице EVLOG нет AREA_ID?
Выпишут из больницы подумаю над решением вашей задачи.

Последний раз редактировалось Valick; 29.06.2022 в 17:53.
Valick вне форума Ответить с цитированием
Старый 30.06.2022, 08:49   #5
deoleynikov
 
Регистрация: 10.08.2017
Сообщений: 7
По умолчанию

Valick, нет. Там только события прохода считывателя. А их обычно два на турникет. Один на выход по турникету, другой - для входа через него. AREA_ID привязан к считывателю через справочник.
Выздоравливайте ;-)
deoleynikov вне форума Ответить с цитированием
Старый 30.06.2022, 09:42   #6
WorldMaster
Старожил
 
Аватар для WorldMaster
 
Регистрация: 25.08.2011
Сообщений: 2,841
По умолчанию

Цитата:
Сообщение от deoleynikov Посмотреть сообщение
Например, если пришел рано в 8:45 вышел, в 9:10 вернулся.
А в чем проблема??
Если средствами какого то языка то просто пробежать в цикле по всем датам и если дата меньше 9,00 то ставить 9,00, если больше 17,00 то ставить 17,00.
Ну и таким образом можно обед учитывать. А потом просто суммировать то что получится.

Или вы средствами sql хотите это выродить? Ну метод скорее всего будет таким же просто команд добавится чтобы перевести все в минуты ...
Skype - wmaster_s E-Mail - WorldMasters@gmail.com
Работаем по 3 критериям - быстро, качественно, недорого. Заказчик выбирает любые два.
WorldMaster вне форума Ответить с цитированием
Старый 30.06.2022, 12:00   #7
deoleynikov
 
Регистрация: 10.08.2017
Сообщений: 7
По умолчанию

WorldMaster, хочу все сделать запросом. Как перебрать результат вычислить - это дело наверное не хитрое.
У нас внутренние регламенты обед учитывают. Нужно, чтобы человек знал, сколько он отсутствовал на рабочем месте в рабочее время (это включает обед). Планируется просто справочная информация для вопроса "сколько мне можно еще покурить?". или сообщение "вам осталось одну минуту быть за пределами офиса - поторопитесь, иначе разборки с ОПП", ну или еще что-то

Последний раз редактировалось deoleynikov; 30.06.2022 в 13:02.
deoleynikov вне форума Ответить с цитированием
Старый 30.06.2022, 13:48   #8
deoleynikov
 
Регистрация: 10.08.2017
Сообщений: 7
По умолчанию

Вообще у меня желание (не подкрепленное знаниями, к сожалению) обернуть всю конструкцию еще в один запрос и проверить: если in1<9:00 а out1>9:00, то in1 = 9:00 и так же с временем окончания работы. Вот только еще не разобрался как это сделать средствами firebird.
deoleynikov вне форума Ответить с цитированием
Старый 30.06.2022, 17:01   #9
deoleynikov
 
Регистрация: 10.08.2017
Сообщений: 7
По умолчанию

Вот, придумал городушку! Только не бейте, я не местный!
Код:
select iin1,oout1, (oout1-iin1)*60*60*24 from
    (
        select
            case extract(HOUR from in1)<9
                when true then cast(in1 as DATE)+cast('09:00' as time)
                else in1
                end as iin1
                ,
            case extract(HOUR from out1)>=18
                when true then cast(out1 as DATE)+cast('18:00' as time)
                else out1
                end as oout1
        from (
                 SELECT el.EV_DATETIME as in1, el.MSG_ID, min (el1.EV_DATETIME) as out1 FROM EVLOG as el
                   left join (
                     SELECT EV_DATETIME, MSG_ID, VR.DEV_GID, USER_ID, VR.AREA_ENTER FROM EVLOG
                         left join V_READERS VR on EVLOG.DEV_GID = VR.DEV_GID
                     where MSG_ID=31 and USER_ID=343 and not vr.AREA_ENTER=2
                       and cast ( EV_DATETIME as DATE) >= cast ('TODAY' as DATE)-1-12
                       and cast ( EV_DATETIME as DATE) < cast ('TODAY' as DATE)-12 ) as el1
                       on el.USER_ID=el1.USER_ID and el.EV_DATETIME<=el1.EV_DATETIME and not el.DEV_GID=el1.DEV_GID
                   left join V_READERS VR on el.DEV_GID = VR.DEV_GID
                 where el.MSG_ID=30 and el.USER_ID=343 and VR.AREA_ENTER=2
                   and cast ( el.EV_DATETIME as DATE) >= cast ('TODAY' as DATE)-1-12
                   and cast ( el.EV_DATETIME as DATE) < cast ('TODAY' as DATE)-12
                 group by el.EV_DATETIME, el.MSG_ID
             ) as que
        where in1<out1
          and cast(out1 as time)>=cast('09:00' as time) and cast(in1 as time)<=cast('18:00' as time)
    )
;
для тестирования принял рабочий день с 9 до 18 часов

IIN1,OOUT1,MULTIPLY
2022-06-16 13:00:00,2022-06-16 13:03:13,192.999974400
2022-06-16 13:34:16,2022-06-16 13:37:23,187.000012800
2022-06-16 14:38:33,2022-06-16 14:44:55,381.999974400
2022-06-16 15:35:39,2022-06-16 15:39:31,231.999984000
2022-06-16 15:39:54,2022-06-16 15:47:32,458.000006400
2022-06-16 16:20:05,2022-06-16 16:23:32,206.999971200
2022-06-16 17:05:27,2022-06-16 17:09:05,217.999987200


в запросе число 12 - на сколько дней назад интересует дата. 343 - код человека.

Последний раз редактировалось deoleynikov; 01.07.2022 в 09:59. Причина: исправил запрос. Убрал лишний уровень!
deoleynikov вне форума Ответить с цитированием
Ответ


Купить рекламу на форуме - 42 тыс руб за месяц

Опции темы Поиск в этой теме
Поиск в этой теме:

Расширенный поиск


Похожие темы
Тема Автор Раздел Ответов Последнее сообщение
Работа с СКУД под WinXP/7/10 на C# и MySQL LexBart Фриланс 1 30.05.2018 22:09
SQL запрос, среднее время обработки данных GorSHOK1 SQL, базы данных 5 31.01.2014 15:52
запрос по дата/время jawist БД в Delphi 10 02.11.2012 20:49
Как обрабатывать сообщение отсутствия действий в окне? anyx Gamedev - cоздание игр: Unity, OpenGL, DirectX 6 27.02.2011 17:54