--InOutPG function

create table PG_TAGMAC
(
  ID NUMBER(4) default 0  not null
    constraint TAGMAC_ID_PK
      primary key,
  HOSTID VARCHAR2(20) not null,
  HOSTNAME VARCHAR2(100),
  TAGMAC VARCHAR2(30) not null,
  TAGMACNAME VARCHAR2(100)
)


create sequence SEQ_PG_TAGMAC_ID
	minvalue 0	
	maxvalue 999999
	start with 1	
	
create or replace procedure inPGalarm(hostIdIn  in varchar2,
                                      inTimeStr in varchar2,
                                      ret       out number) is
  inPG_person_id number := 0;
  tagmacStr      varchar2(30) := '';
begin
  begin
    ret := 0;
    select nvl(tagmac, '')
      into tagmacStr
      from pg_tagmac
     where trim(hostId) = hostIdIn
       and rownum = 1;
    dbms_output.put_line('tagmac:'||tagmacStr);   
    select nvl(max(id), 0)
      into inPG_person_id
      from pg_inpg_person
     where upper(trim(assetcode)) = upper(trim(tagmacStr));
    dbms_output.put_line('personId:'||inPG_person_id); 
    if (inPG_person_id = 0) then
      begin
      
        insert into pg_alarm
          (id, alarmtypeid, alarm_date, alarm_value, description, userid,active)
        values
          (seq_pg_alarm_id.nextval,
           4,
           sysdate,
           '入廊时间:' || inTimeStr,
           '入廊超时,未找到hostId:'||hostIdIn||'人员记录',
           inPG_person_id,
           1);
	 ret := 1;   
      end;
    else
      begin
        insert into pg_alarm
          (id, alarmtypeid, alarm_date, alarm_value, description, userid,active)
        values
          (seq_pg_alarm_id.nextval,
           4,
           sysdate,
           '入廊时间:' || inTimeStr,
           '入廊超时',
           inPG_person_id,
           1);
      end;
      ret := 1;
    end if;
  end;

end inPGalarm;




create or replace procedure updateINPGstatus(hostIdIn   in varchar2,
                                             inTimeStr  in varchar2,
                                             outTimeStr in varchar2,
                                             ret        out number) is
  inPG_person_id number := 0;--perhaps many records id shared one tagmac 
  tagmacStr      varchar2(30) := '';

begin
  ret := 0;
  update pg_inpg_status
     set outtime = to_date(outTimeStr, 'yyyy-mm-dd hh24:mi:ss')
   where id in (select a.id
                  from pg_inpg_status a, pg_inpg_person b, pg_tagmac c
                 where a.pginpgpersonid = b.id
                   and a.outtime is null
                   and a.intime between
                       to_date(inTimeStr, 'yyyy-mm-dd hh24:mi:ss') - 1 / 240 and
                       to_date(inTimeStr, 'yyyy-mm-dd hh24:mi:ss') + 1 / 240 --tenMinutes
                   and upper(trim(b.assetcode)) = upper(trim(c.tagmac))
                   and c.hostId = hostIdIn);
  ret := SQL%ROWCOUNT;
  if (ret < 1) then
    insert into pg_alarm
      (id, alarmtypeid, alarm_date, alarm_value, description, userid，active)
    values
      (seq_pg_alarm_id.nextval,
       4,
       sysdate,
       '出廊时间:' || outTimeStr,
       '出廊更新失败,未找到hostId:' || hostIdIn || '人员记录',
       0,
	   1);
  else
    update pg_inpg_person set isinpg=0 where id in (select b.id
                  from pg_inpg_status a, pg_inpg_person b, pg_tagmac c
                 where a.pginpgpersonid = b.id
                   and a.outtime = to_date(outTimeStr, 'yyyy-mm-dd hh24:mi:ss')
                   and a.intime between
                       to_date(inTimeStr, 'yyyy-mm-dd hh24:mi:ss') - 1 / 240 and
                       to_date(inTimeStr, 'yyyy-mm-dd hh24:mi:ss') + 1 / 240 --tenMinutes
                   and upper(trim(b.assetcode)) = upper(trim(c.tagmac))
                   and c.hostId = hostIdIn); 
	--TBD if exists pg_alarm (personId) then update active=0
  end if;
  ret := 1;  
end updateINPGstatus;


create or replace view pg_InOutPG_record as
select a.id,a.intime ,a.outtime,b.username,b.persontype from pg_inpg_status a,pg_inpg_person b
where a.pginpgpersonid=b.id