--InOutPG function
create table PG_TAGMAC
(
  id         NUMBER(10) default 0 not null,
  hostid     VARCHAR2(20) not null,
  hostname   VARCHAR2(100),
  tagmac     VARCHAR2(30) not null,
  tagmacname VARCHAR2(100)
)
;
alter table PG_TAGMAC
  add constraint TAGMAC_ID_PK primary key (ID);


create table PG_TAGPOSITION
(
  id                 NUMBER(11) default 0 not null,
  hostid             VARCHAR2(20) not null,
  hostname           VARCHAR2(100),
  tagid              VARCHAR2(20) not null,
  tagmac             VARCHAR2(30) not null,
  tagmacname         VARCHAR2(100),
  coordinatesname    VARCHAR2(100),
  coordinate         VARCHAR2(20),
  x                  VARCHAR2(30),
  y                  VARCHAR2(30),
  positionupdatetime VARCHAR2(40),
  parsedtime         DATE,
  querytime          DATE default sysdate
)
;
create index QUERYTIME_IDX on PG_TAGPOSITION (QUERYTIME);
alter table PG_TAGPOSITION
  add constraint TAGPOSITION_ID_PK primary key (ID);


create sequence SEQ_PG_TAGMAC_ID
minvalue 0
maxvalue 9999
start with 1
increment by 1
cache 20;


create sequence SEQ_PG_TAGPOSITION_ID
minvalue 0
maxvalue 99999999
start with 1
increment by 1
cache 20;
	
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') - 10 / 240 and
                       to_date(inTimeStr, 'yyyy-mm-dd hh24:mi:ss') + 10 / 240 --tenMinutes
                   and upper(trim(b.assetcode)) = upper(trim(c.tagmac))
                   and c.hostId = hostIdIn);
  ret := SQL%ROWCOUNT;
  if (ret > 0) then  
/*  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') - 20 / 240 and
                       to_date(inTimeStr, 'yyyy-mm-dd hh24:mi:ss') + 20 / 240 --twentyMinutes
                   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;

create or replace view pg_position_tag_dev as
select  a.*,b.partition ,
substr(a.coordinatesname,instr(a.coordinatesname,'号分区')+3,
instr(a.coordinatesname,'K')-instr(a.coordinatesname,'号分区')-3) as binName
 from pg_tagposition a, ( select distinct partition,pipegallery from  pg_device ) b
where substr(a.coordinatesname,0,instr(a.coordinatesname,'号分区')-1) = b.pipegallery
union
select c.*, '离开区域' as partition,'离开区域' as binName from pg_tagposition c
where c.coordinatesname = '离开区域';