drop function if exists  "dbUserReg".tbUserRegVW_UserData ( in _UserID int , in AccID int
  ,in _id int
)  cascade;
create  or replace FUNCTION "dbUserReg".tbUserRegVW_UserData ( in _UserID int , in AccID int
  ,in _id int = null
)
returns table (
    id int
   	 , ContactID int
     -- * Ancestor fields - tbContact
		 ,prefix text, FirstName text,  LastName text,  Suffix  text
		 -- ,PrefixEng text,  FirstNameEng text,  LastNameEng text,  SuffixEng text
     -- ,StatNames text
		 -- ,CitizenID  text,  TaxID  text
		 --,PhoneWork text, PhoneWorkExt text
		 --,PhoneHome text, PhoneHomeExt text
		 --,PhoneResident text, PhoneResidentExt text
		 ,Mobile1 text --, Mobile2 text
		 --,FaxWork text,FaxHome text,FaxResident text
		 ,EmailHome text -- , URLHome text
	   --,AddrHome text,AddrWork text,AddrResident text
	   --,AddrHomeID int,AddrWorkID int,AddrResidentID int
     --* Employee info
     --,EmpNo text
		 --, Groups text
     --* Orgnaization
     --,OrgID int
     --  ,OrgNo text
     --  ,OrgName text
     --,StartJob date
     --,FinishJob date
     --,RemarkOrg text
     --* Position
     --,PositionID int  -- Active position
     --  ,PositionName text
     --,StartPos date
     --,FinishPos date
     --,RemarkPos text
     --* Employment
     --,StartEmp date
     --,FinishEmp date
     --,RemarkEmp text
     --* Wages unit
     --,uomid int
     --   ,uom text
     -- * User and A/C
     ,UID int , ACC int
     ,CompID int
       ,CompPrefix text , CompFirstName text, CompSuffix text
     ,UserName text
     ,UserPass text
     ,Salt text

     ,SysRole text -- "dbUserAcc".UserSystemRole
     ,EmpID int
     ,Deleted boolean
     ,UserID int
     ,OrgID int

     ,Grp Text
     ,privilege text
     ,idgrp int
     ,dis smallint
     ,fqty smallint
     ,user_role boolean
     ,acname text
       ,acname_contactid int
) AS $$
begin
  return query
  select
    ab.id
		-- * Data
  	 ,ab.Contactid -- a.ID ContactID
		 ,a.prefix ::text, a.FirstName ::text, a.LastName ::text, a.Suffix ::text
		 -- ,a.PrefixEng::text, a.FirstNameEng::text, a.LastNameEng::text, a.SuffixEng::text
     -- ,a.StatNames
		 -- ,a.CitizenID::text , a.TaxID::text
		 --,a.PhoneWork::text, a.PhoneWorkExt::text
		 --,a.PhoneHome::text, a.PhoneHomeExt::text
		 --,a.PhoneResident::text, a.PhoneResidentExt::text
		 ,a1.tvalue ::text Mobile1  -- ,a.Mobile1::text --, a.Mobile2::text
		 -- ,a.FaxWork::text  ,a.FaxHome::text ,a.FaxResident::text
		 ,a2.tvalue ::text EmailHome -- ,a.EmailHome::text --, a.URLHome::text
	   -- ,a.AddrHome::text    ,a.AddrWork::text    ,a.AddrResident ::text
	   -- ,a.AddrHomeID  ,a.AddrWorkID   ,a.AddrResidentID
		 -- * Employee info
     --,a3.Tvalue::text  EmpNo   -- 33
     --,"dbContact".tbcontgrp_getgroups( a.id ) Groups
     --* Organization of the employee
     --,aa1.orgid
     --  ,aa1111.tvalue::text OrgNo  --36
     --  ,concat_ws(' ',aa111.prefix,aa111.firstname,aa111.lastname,aa111.suffix ) OrgName
     --,aa1.StartJob
     --,aa1.FinishJob
     --,aa1.Remark RemarkOrg
     --* Position
     --,aa21.id PositionID
     --  ,aa21.title::text PositionName  --42
     --,aa2.StartJob StartPos
     --,aa2.FinishJob FinishPos
     --,aa2.Remark RemarkPos
     --* Employment
     --,aa3.StartJob StartEmp
     --,aa3.FinishJob FinishEmp
     --,aa3.Remark RemarkEmp
     --* Wages unit
     --,aa.uomid
     --   ,a4.title::text   --50
     -- ,aa.fqty, aa.sqty, aa.dis , aa.idgrp, aa.idprev, aa.seq, aa.path
     , ab.UID, ab.ACC

     ,a5.id CompID  -- 53
        ,a51.prefix ::text, a51.FirstName ::text, a51.Suffix ::text
     ,ab.username ::text
     -- ,repeat('*', length(a6.userpass)) ::text  -- UserPass
     ,a6.userpassword ::text
     ,a6.salt

     ,a6.SysRole
     ,ab.EmpID
     ,ab.deleted
     ,ab.userid
     ,ab.orgid
     ,ab1.firstname::text  Grp
     ,ab.privilege
     ,ab.idgrp
     ,ab.dis
     ,ab.fqty
     ,ab.user_role
     ,ab.acname
        ,ab.org_contactid  acname_contactid
  from
    "dbUserReg".tbUserReg ab
     right join "dbSys".grpGetData( _userid, accid, '"dbUserReg".tbuserreg' ) ab0 on (ab0.id = ab.id )

    --left join  "dbHR".tbEmp aa on (aa.id = ab.empid )
    -- left join  "dbContact".tbContact_GetData(_UserID, AccID  , _id:= ab.contactid   ) a on (a.id = ab.ContactID )
    left join "dbContact".tbcontact a on ( a.id = ab.contactid )
       left join "dbContact".tbcontphone a1 on ( a1.pid = a.id and a1.title = 'Mobile' and a1.tcustom = '#1' )
       -- left join "dbContact".tbcontemail a2 on ( a2.pid = a.id and a2.title = 'Email'  and a2.tcustom is null )
       left join "dbContact".tbcontemail a2 on ( a2.pid = a.id and a2.title = 'Email'  and a2.tcustom ='1' )
		--left outer join  "dbContact".tbContNumber a3 on ( a3.pid = a.id and a3.Title = 'Employee No' and a3.TCustom is null  )

    -- ** Active organization this employee reside
    /*
    left join "dbHR".tbEmporg aa1 on ( aa1.pid = aa.id and  aa1.active  and aa1.kind = 'Organization'   )
      left join "dbHR".tborg aa11 on ( aa11.id = aa1.orgid )
        left join "dbContact".tbcontact aa111 on ( aa111.id = aa11.contactid )
          left outer join  "dbContact".tbContNumber aa1111 on ( aa1111.pid = a.id and aa1111.Title = 'Default' and aa1111.TCustom = 'Dept No' )
    left join "dbHR".tbEmporg aa2 on ( aa2.pid = aa.id and aa2.active  and aa2.kind = 'Position' )
      left join "dbHR".tbposition aa21 on ( aa21.id = aa2.positionID )
    left join "dbHR".tbEmporg aa3 on ( aa3.pid = aa.id and aa3.active  and aa3.kind = 'Employment' )
    --** Unit of measurement-wages
    left join "dbBase".tbuom a4 on ( a4.id = aa.uomid ) */

    --* Company
    left join "dbHR".tborg a5 on ( a5.acc= ab.acc and a5.company = true )
      left join "dbContact".tbcontact a51 on ( a51.id = a5.contactid )
    --* System role
    left join "dbUserAcc".tbuser a6 on ( a6.id = ab.userid )

    left join "dbUserReg".tbuserreg ab1 on ( ab1.id = ab.idgrp )


  where ab.acc = AccID
    -- and ab.deleted =false
    and ab.acc is not null
    and a6.SysRole <>'Import'
    /*
	 and  case
	    -- when _id is null then a.acc=accid -- a.UID in (select a1.UID from  "dbUserAcc".tbaccuser a1 where a1.PID = AccID )
	    when _id is null then a.acc= a.UID in (select a1.UID from  "dbUserAcc".tbaccuser a1 where a1.PID = AccID )
	    else true
	  end */

	  and case
	    when _id is null then   a.UID in (select a1.UID from  "dbUserAcc".tbaccuser a1 where a1.PID = AccID )
	    else a.id  = _id
	  end

  order by ab0.path
  ;

end $$ LANGUAGE plpgsql;