1ZckB0yz

1/****************************************************************
2 tbCaseTranVerdVW.sql 
3 
4  A collection of procedures related to View. Please follow this cnonvestional here.
5  
6  2 functions as mandatory for view :-
7  - Prepare data for VIEW.  Function name must be formated "Schema".tablenameVWM_UserData( ... ) 
8  - Update data fro VIEW. Function name must
9
10  Wutirkai Pornchai 
11
12  20-05-2014
13
14*****************************************************************/
15/****************************************** 
16View Procedures 
17*******************************************/
18 -- * Data selected for underlying user 
19drop function if exists  "dbLegal".tbCaseTranVerdVW_UserData ( in UserID int , in AccID int
20  ,in _id int  
21)  cascade;
22create  or replace FUNCTION "dbLegal".tbCaseTranVerdVW_UserData ( in UserID int , in AccID int
23  ,in _id int = null 
24
25) 
26returns table (
27    id int 
28		,DebtCalcID int
29    ,DebtCalcID_Org int
30		,upd timestamp
31		,DateStop date
32		,Total  num
33    ,UpdateAmount boolean  
34    ,LGStepProItemID int
35    ,LGStepProItemTitle text  -- ,concat_ws(' ',d.Title,'-',d.id) ActiveStep
36		,pid int , uid int, acc int
37    ,active boolean
38    ,assettypeid int 
39    
40) AS $$
41 
42begin
43   return query 
44
45		select
46      a.id 
47      ,a.DebtCalcID 
48      ,a.DebtCalcID_Org
49      ,b.upd
50      ,b.DateStop::date
51      ,b1.tvalue Total 
52      ,a.UpdateAmount 
53      ,a.LGStepProItemID
54      ,d.title
55      ,a.pid, a.uid, a.acc
56      ,a.active
57      ,a.assettypeid
58    
59    from
60      "dbLegal".tbCaseTranVerd a 
61      left join "dbLegal".tbDebtCalc  b on (  b.id = a.DebtCalcID ) 
62      left join "dbLegal".tbDebtCalcSum b1 on ( b1.pid = b.id  and b1.is_total  )
63        -- inner join "dbLegal".tbCaseAmt b11 on (b11.id = b1.CaseAmtID and b11.title = 'Total'   ) -- Main total
64  		left join "dbLegal". tbLGStepProItem d on ( d.ID = a.LGStepProItemID )
65		where 	 
66 		--	a.UID in (select a1.UID from "dbUserAcc".tbaccuser a1 where a1.PID =  AccID )
67
68      case 
69        when _id is null then a.acc=accid 
70        else a.id = _id
71      end       
72    order by 
73      a.pid, b.upd desc ;
74 end;
75$$ LANGUAGE plpgsql ;
76
77-- * How to update underlying tables whose data is projected to this view 
78drop function if exists "dbLegal".tbCaseTranVerdVW_UpdData()  cascade;
79create or replace function "dbLegal".tbCaseTranVerdVW_UpdData() 
80returns trigger as $$
81declare sql text;
82begin
83  if (TG_OP = 'INSERT') then 
84    insert into "dbLegal".tbCaseTranVerd ( pid, uid ) 
85      select NEW.pid, new.uid
86    returning id, DebtCalcID, DebtCalcID_Org into new.id, new.DebtCalcID,new.DebtCalcID_Org ;
87
88    return NEW;
89  elseif (TG_OP = 'UPDATE') then
90    sql := null;    
91    sql := "dbSys".sqlSetValue( OLD.UpdateAmount, NEW.UpdateAmount, sql, 'UpdateAmount' );
92    sql := "dbSys".sqlSetValue( OLD.uid, NEW.uid, sql, 'uid' );
93    if sql is not null then  
94       sql := 'update "dbLegal".tbCaseTranVerd ' || sql ||
95						  ' where id  = '|| new.id ;
96      execute sql ;
97    end if;
98
99    return NEW;
100  elseif TG_OP = 'DELETE' then 
101    delete from "dbLegal".tbCaseTranVerd where id = old.id;
102    return OLD;
103  end if;
104end ; $$ LANGUAGE plpgsql;
105 
106/*****************************
107  View registration 
108******************************/
109select "dbSys".viewRegistration( '"dbLegal".tbCaseTranVerdVW'
110  -- , _delete_system_data := true 
111  --,_init_data := true, _init_data_uid := 1 ,_init_data_acc := null 
112 -- , _OrderNo_Updatable := true
113
114);