/****************************************************************
 tbCaseAssetAmtVW.sql

  A collection of procedures related to View. Please follow this cnonvestional here.

  2 functions as mandatory for view :-
  - Prepare data for VIEW.  Function name must be formated "Schema".tablenameVWM_UserData( ... )
  - Update data fro VIEW. Function name must

  Wutirkai Pornchai

  02-05-014

*****************************************************************/
/******************************************
View Procedures
*******************************************/
 -- * Data selected for underlying user
drop function if exists  "dbLegal".tbCaseAssetAmtVW_UserData ( in UserID int , in AccID int )  cascade;
create  or replace FUNCTION "dbLegal".tbCaseAssetAmtVW_UserData ( in UserID int , in AccID int )
returns table (
    id int
		,Title text
    ,TCustom text
		,Amount num
    ,is_total boolean
		,pid int
    ,uid int
    ,orderno smallint
) AS $$

begin
   return query

		select
      a.id

      ,b.title ::text
      ,case when a.tcustom is null then b.tlabel  else  a.tcustom  end :: text

      --,case when a.tcustom is null then b.tlabel  else  a.tcustom  end :: text
      --,b.tcustom ::text

      --,a.tcustom::text  title
      --,b.tcustom ::text

      ,a.tvalue  amount
			 ,case
					when b.title = 'Total' and b.tcustom is null then true
					else false
				end
      ,a.pid
			,a.uid
      ,b.orderno
    from
      "dbLegal".tbCaseAssetAmt a
      left join "dbLegal".tbCaseAmt b on ( b.id = a.CaseAmtID      )
       --right join "dbLegal".tbCaseAmt b on ( b.id = a.CaseAmtID      )

      -- right join "dbLegal".tbCaseAsset c on (c.id = a.pid )
		where
      -- a.CaseAmtID is not null
			-- and a.UID in (select a1.UID from "dbUserAcc".tbaccuser a1 where a1.PID =  AccID )
      b.acc = accid
      and not b.hide
    order by
      a.pid, b.orderno ;

end;
$$ LANGUAGE plpgsql ;

-- * How to update underlying tables whose data is projected to this view
drop function if exists "dbLegal".tbCaseAssetAmtVW_UpdData()  cascade;
create or replace function "dbLegal".tbCaseAssetAmtVW_UpdData()
returns trigger as $$

begin
  if (TG_OP = 'INSERT') then

    return NEW;
  elseif (TG_OP = 'UPDATE') then

    return NEW;
  elseif TG_OP = 'DELETE' then
    --  delete from "dbLegal".tbCaseAssetAmt where id = old.id ;
    return OLD;
  end if;
end ; $$ LANGUAGE plpgsql;

/*****************************
  View registration
******************************/
select "dbSys".viewRegistration( '"dbLegal".tbCaseAssetAmtVW'
  --, _delete_system_data := true
  -- ,_init_data := true, _init_data_uid := 1 ,_init_data_acc := 4 , _init_sysorg := true
  --,_init_data := true, _init_data_uid := 1 ,_init_data_acc := null , _init_sysorg := true

);


select * from "dbSys".tvcreateview( varint('userid'), varint('accid'), '"dbLegal".tbCaseAssetAmt' );