/****************************************************************

  Wutirkai Pornchai
  14-09-2014

*****************************************************************/
/******************************************
View Procedures
*******************************************/
 -- * Data selected for underlying user
drop function if exists  "dbLegal".tbLGStepProItemVW_UserData ( in UserID int , in AccID int
  , in _pid  int
)  cascade;
create  or replace FUNCTION "dbLegal".tbLGStepProItemVW_UserData ( in UserID int , in AccID int
  , in _pid int = null )
returns table
( id int
	,Title varchar -- (50) not null,  -- comment 'ขั้นตอนดำเนินคดี',
	,Description text  --null, --  comment 'รายละเอียด',
  ,Duration smallint  --  null, --  comment 'ระยะเวลา- วัน',
  ,SuccStat varchar-- (20) null, --   comment 'สถานะของงานหรือคดีหากขั้นตอนนี้สำเร็จ',
	,FailStat varchar-- (20) null, --  comment 'สถานะของงานหรือคดีหากขั้นตอนนี้ไม่สำเร็จ',
  ,CaseStat text
 	,PID int --   not null references "dbLegal".tblgsteppro(ID) on delete cascade  on update cascade

  ,dis smallint
  ,fqty smallint
  ,idgrp int
     ,grp  text
  ,path int[]
  ,orderno smallint
  ,Selectable boolean
  ,StartActive boolean
  ,processid int
    ,process_name text
  ,Description_require boolean
  ,uid int
  ,SysStep text
)
 AS $$

	select
  a.id
	--,a.Title --   'ขั้นตอนดำเนินคดี',
  ,case
     when a.processid  is null then a.title
     else format('%s %s',c.no, c.title)
   end
	--,a.Description
  ,case
    when a.description is null then c.description
    else a.description
  end
  ,a.Duration --smallint  --  null, --  comment 'ระยะเวลา- วัน',
	,a.SuccStat --varchar-- (20) null, --   comment 'สถานะของงานหรือคดีหากขั้นตอนนี้สำเร็จ',
	,a.FailStat --varchar-- (20) null, --  comment 'สถานะของงานหรือคดีหากขั้นตอนนี้ไม่สำเร็จ',
  ,a.CaseStat
 	,a.PID --int --   not null references "dbLegal".tblgsteppro(ID) on delete cascade  on update cascade
  ,a.dis
  ,a.fqty
  ,a.idgrp
    ,b.title
  ,z.path
  ,a.orderno
  ,a.Selectable
  ,a.StartActive
  ,a.processid
    ,c.title
  ,a.Description_require
  ,a.uid
  ,a.SysStep

  from "dbLegal".tbLGStepProItem a
    -- right join "dbSys".grpGetData( userid, accid , '"dbLegal".tbLGStepProItem',path_only:=true, uid_child:=true ) z on (z.id = a.id)
    -- join "dbSys".grpGetData( userid, accid , '"dbLegal".tbLGStepProItem',path_only:=true, _pid := pid  ) z on (z.id = a.id)
     right join  "dbSys".grpGetData( userid, accid , '"dbLegal".tbLGStepProItem'
       , path_only:=true
       , uid_child :=true
       , _pid := _pid --  case when pid is not null then  pid else null  end
    ) z  on (z.id = a.id)

    left join "dbLegal".tbLGStepProItem b on (b.id = a.idgrp )
    left join "dbLegal".tblgsteppro c on (c.id = a.processid )
  where
    case
      when _pid is null then true
      else a.pid = _pid
    end
   order by z.path
  ;

$$ LANGUAGE sql;

-- * How to update underlying tables whose data is projected to this view
drop function if exists "dbLegal".tbLGStepProItemVW_UpdData()  cascade;
create or replace function "dbLegal".tbLGStepProItemVW_UpdData()
returns trigger as $$
declare Grp varchar; GrpID int; Change boolean; sql text; lastid int;
  rec record;
begin
  if (TG_OP = 'INSERT') then
 		insert into "dbLegal".tbLGStepProItem (
			Title -- varchar -- (50) not null,  -- comment 'ขั้นตอนดำเนินคดี',
			,Description --text  --null, --  comment 'รายละเอียด',
			,Duration -- smallint  --  null, --  comment 'ระยะเวลา- วัน',
			,SuccStat -- varchar-- (20) null, --   comment 'สถานะของงานหรือคดีหากขั้นตอนนี้สำเร็จ',
			,FailStat -- varchar-- (20) null, --  comment 'สถานะของงานหรือคดีหากขั้นตอนนี้ไม่สำเร็จ',
      ,CaseStat
			,PID -- int --   not null references "dbLegal".tblgsteppro(ID) on delete cascade  on update cascade
			,idgrp
      ,Selectable
      ,StartActive
      ,processid
      ,Description_require
       ,uid
    ) select
			new.Title -- varchar -- (50) not null,  -- comment 'ขั้นตอนดำเนินคดี',
			,new.Description --text  --null, --  comment 'รายละเอียด',
			,new.Duration -- smallint  --  null, --  comment 'ระยะเวลา- วัน',
			,new.SuccStat -- varchar-- (20) null, --   comment 'สถานะของงานหรือคดีหากขั้นตอนนี้สำเร็จ',
			,new.FailStat -- varchar-- (20) null, --  comment 'สถานะของงานหรือคดีหากขั้นตอนนี้ไม่สำเร็จ',
      ,new.CaseStat
			,new.PID -- int --   not null references "dbLegal".tblgsteppro(ID) on delete cascade  on update cascade
			,new.idgrp
      ,new.Selectable
      ,new.StartActive
      ,new.processid
      ,new.Description_require
      ,new.uid
    	returning id into new.id;
    return NEW ;
  elseif (TG_OP = 'UPDATE') then
    sql := null;
    sql := sqlSetValue( OLD.Title, NEW.Title, sql, 'Title' );
    sql := sqlSetValue( OLD.Description, NEW.Description, sql, 'Description' );

    sql := sqlSetValue( OLD.Duration, NEW.Duration, sql, 'Duration' );
    sql := sqlSetValue( OLD.SuccStat, NEW.SuccStat, sql, 'SuccStat' );
    sql := sqlSetValue( OLD.FailStat, NEW.FailStat, sql, 'FailStat' );
    sql := sqlSetValue( OLD.CaseStat, NEW.CaseStat, sql, 'CaseStat' );
    sql := sqlSetValue( OLD.PID, NEW.PID, sql, 'PID' );
    sql := sqlSetValue( OLD.orderno, NEW.orderno, sql, 'orderno' );
    sql := sqlSetValue( OLD.Selectable, NEW.Selectable, sql, 'Selectable' );
    sql := sqlSetValue( OLD.idgrp, NEW.idgrp, sql, 'idgrp' );
    sql := sqlSetValue( OLD.processid, NEW.processid, sql, 'processid' );
    sql := sqlSetValue( OLD.StartActive, NEW.StartActive, sql, 'StartActive' );


    sql := sqlSetValue( OLD.Description_require, NEW.Description_require, sql, 'Description_require' );
		sql := "dbSys".sqlSetValue( OLD.uid, NEW.uid, sql, 'uid' );


     if sql is not null then
      sql := 'update "dbLegal".tbLGStepProItem ' || sql || ' where id = ' || new.id;
      execute sql ;
		end if;

    return NEW;
  elseif TG_OP = 'DELETE' then
      -- delete from "dbLegal".tbLGStepProItem where id = old.id; -- record id of this decendant
    perform "dbSys".grpdelete( old.id , '"dbLegal".tbLGStepProItem');
     return OLD;

  end if;
end ; $$ LANGUAGE plpgsql;

/*****************************
  View registration
******************************/
select "dbSys".viewRegistration( '"dbLegal".tbLGStepProItemVW'
  , _delete_system_data := true
   ,_init_data := true, _init_data_uid := 1 ,_init_data_acc := null , _init_sysorg := true  , _init_count := 'count_step'

);


/************************************************************
 Registration : inheritance
*************************************************************/
--  select "dbSys".tblRegistration_Inherit( '"dbLegal".tbLGStepProItemVW','"dbBase".tbLGStepProItemVW', false, true, true );


--* create view
select * from "dbSys".tvcreateview(varint('userid'),varint('accid'),'"dbLegal".tblgstepproitem', dropit:=true );