· 8 years ago · Nov 16, 2017, 07:34 AM
1drop function if exists "dbUserReg".tbUserRegVW_UserData ( in _UserID int , in AccID int
2 ,in _id int
3) cascade;
4create or replace FUNCTION "dbUserReg".tbUserRegVW_UserData ( in _UserID int , in AccID int
5 ,in _id int = null
6)
7returns table (
8 id int
9 , ContactID int
10 -- * Ancestor fields - tbContact
11 ,prefix text, FirstName text, LastName text, Suffix text
12 -- ,PrefixEng text, FirstNameEng text, LastNameEng text, SuffixEng text
13 -- ,StatNames text
14 -- ,CitizenID text, TaxID text
15 --,PhoneWork text, PhoneWorkExt text
16 --,PhoneHome text, PhoneHomeExt text
17 --,PhoneResident text, PhoneResidentExt text
18 ,Mobile1 text --, Mobile2 text
19 --,FaxWork text,FaxHome text,FaxResident text
20 ,EmailHome text -- , URLHome text
21 --,AddrHome text,AddrWork text,AddrResident text
22 --,AddrHomeID int,AddrWorkID int,AddrResidentID int
23 --* Employee info
24 --,EmpNo text
25 --, Groups text
26 --* Orgnaization
27 --,OrgID int
28 -- ,OrgNo text
29 -- ,OrgName text
30 --,StartJob date
31 --,FinishJob date
32 --,RemarkOrg text
33 --* Position
34 --,PositionID int -- Active position
35 -- ,PositionName text
36 --,StartPos date
37 --,FinishPos date
38 --,RemarkPos text
39 --* Employment
40 --,StartEmp date
41 --,FinishEmp date
42 --,RemarkEmp text
43 --* Wages unit
44 --,uomid int
45 -- ,uom text
46 -- * User and A/C
47 ,UID int , ACC int
48 ,CompID int
49 ,CompPrefix text , CompFirstName text, CompSuffix text
50 ,UserName text
51 ,UserPass text
52 ,Salt text
53
54 ,SysRole text -- "dbUserAcc".UserSystemRole
55 ,EmpID int
56 ,Deleted boolean
57 ,UserID int
58 ,OrgID int
59
60 ,Grp Text
61 ,privilege text
62 ,idgrp int
63 ,dis smallint
64 ,fqty smallint
65 ,user_role boolean
66 ,acname text
67 ,acname_contactid int
68) AS $$
69begin
70 return query
71 select
72 ab.id
73 -- * Data
74 ,ab.Contactid -- a.ID ContactID
75 ,a.prefix ::text, a.FirstName ::text, a.LastName ::text, a.Suffix ::text
76 -- ,a.PrefixEng::text, a.FirstNameEng::text, a.LastNameEng::text, a.SuffixEng::text
77 -- ,a.StatNames
78 -- ,a.CitizenID::text , a.TaxID::text
79 --,a.PhoneWork::text, a.PhoneWorkExt::text
80 --,a.PhoneHome::text, a.PhoneHomeExt::text
81 --,a.PhoneResident::text, a.PhoneResidentExt::text
82 ,a1.tvalue ::text Mobile1 -- ,a.Mobile1::text --, a.Mobile2::text
83 -- ,a.FaxWork::text ,a.FaxHome::text ,a.FaxResident::text
84 ,a2.tvalue ::text EmailHome -- ,a.EmailHome::text --, a.URLHome::text
85 -- ,a.AddrHome::text ,a.AddrWork::text ,a.AddrResident ::text
86 -- ,a.AddrHomeID ,a.AddrWorkID ,a.AddrResidentID
87 -- * Employee info
88 --,a3.Tvalue::text EmpNo -- 33
89 --,"dbContact".tbcontgrp_getgroups( a.id ) Groups
90 --* Organization of the employee
91 --,aa1.orgid
92 -- ,aa1111.tvalue::text OrgNo --36
93 -- ,concat_ws(' ',aa111.prefix,aa111.firstname,aa111.lastname,aa111.suffix ) OrgName
94 --,aa1.StartJob
95 --,aa1.FinishJob
96 --,aa1.Remark RemarkOrg
97 --* Position
98 --,aa21.id PositionID
99 -- ,aa21.title::text PositionName --42
100 --,aa2.StartJob StartPos
101 --,aa2.FinishJob FinishPos
102 --,aa2.Remark RemarkPos
103 --* Employment
104 --,aa3.StartJob StartEmp
105 --,aa3.FinishJob FinishEmp
106 --,aa3.Remark RemarkEmp
107 --* Wages unit
108 --,aa.uomid
109 -- ,a4.title::text --50
110 -- ,aa.fqty, aa.sqty, aa.dis , aa.idgrp, aa.idprev, aa.seq, aa.path
111 , ab.UID, ab.ACC
112
113 ,a5.id CompID -- 53
114 ,a51.prefix ::text, a51.FirstName ::text, a51.Suffix ::text
115 ,ab.username ::text
116 -- ,repeat('*', length(a6.userpass)) ::text -- UserPass
117 ,a6.userpassword ::text
118 ,a6.salt
119
120 ,a6.SysRole
121 ,ab.EmpID
122 ,ab.deleted
123 ,ab.userid
124 ,ab.orgid
125 ,ab1.firstname::text Grp
126 ,ab.privilege
127 ,ab.idgrp
128 ,ab.dis
129 ,ab.fqty
130 ,ab.user_role
131 ,ab.acname
132 ,ab.org_contactid acname_contactid
133 from
134 "dbUserReg".tbUserReg ab
135 right join "dbSys".grpGetData( _userid, accid, '"dbUserReg".tbuserreg' ) ab0 on (ab0.id = ab.id )
136
137 --left join "dbHR".tbEmp aa on (aa.id = ab.empid )
138 -- left join "dbContact".tbContact_GetData(_UserID, AccID , _id:= ab.contactid ) a on (a.id = ab.ContactID )
139 left join "dbContact".tbcontact a on ( a.id = ab.contactid )
140 left join "dbContact".tbcontphone a1 on ( a1.pid = a.id and a1.title = 'Mobile' and a1.tcustom = '#1' )
141 -- left join "dbContact".tbcontemail a2 on ( a2.pid = a.id and a2.title = 'Email' and a2.tcustom is null )
142 left join "dbContact".tbcontemail a2 on ( a2.pid = a.id and a2.title = 'Email' and a2.tcustom ='1' )
143 --left outer join "dbContact".tbContNumber a3 on ( a3.pid = a.id and a3.Title = 'Employee No' and a3.TCustom is null )
144
145 -- ** Active organization this employee reside
146 /*
147 left join "dbHR".tbEmporg aa1 on ( aa1.pid = aa.id and aa1.active and aa1.kind = 'Organization' )
148 left join "dbHR".tborg aa11 on ( aa11.id = aa1.orgid )
149 left join "dbContact".tbcontact aa111 on ( aa111.id = aa11.contactid )
150 left outer join "dbContact".tbContNumber aa1111 on ( aa1111.pid = a.id and aa1111.Title = 'Default' and aa1111.TCustom = 'Dept No' )
151 left join "dbHR".tbEmporg aa2 on ( aa2.pid = aa.id and aa2.active and aa2.kind = 'Position' )
152 left join "dbHR".tbposition aa21 on ( aa21.id = aa2.positionID )
153 left join "dbHR".tbEmporg aa3 on ( aa3.pid = aa.id and aa3.active and aa3.kind = 'Employment' )
154 --** Unit of measurement-wages
155 left join "dbBase".tbuom a4 on ( a4.id = aa.uomid ) */
156
157 --* Company
158 left join "dbHR".tborg a5 on ( a5.acc= ab.acc and a5.company = true )
159 left join "dbContact".tbcontact a51 on ( a51.id = a5.contactid )
160 --* System role
161 left join "dbUserAcc".tbuser a6 on ( a6.id = ab.userid )
162
163 left join "dbUserReg".tbuserreg ab1 on ( ab1.id = ab.idgrp )
164
165
166 where ab.acc = AccID
167 -- and ab.deleted =false
168 and ab.acc is not null
169 and a6.SysRole <>'Import'
170 /*
171 and case
172 -- when _id is null then a.acc=accid -- a.UID in (select a1.UID from "dbUserAcc".tbaccuser a1 where a1.PID = AccID )
173 when _id is null then a.acc= a.UID in (select a1.UID from "dbUserAcc".tbaccuser a1 where a1.PID = AccID )
174 else true
175 end */
176
177 and case
178 when _id is null then a.UID in (select a1.UID from "dbUserAcc".tbaccuser a1 where a1.PID = AccID )
179 else a.id = _id
180 end
181
182 order by ab0.path
183 ;
184
185end $$ LANGUAGE plpgsql;