聽說幸福…在山的那邊 (浪機子)

FUNCTION 也可以回傳 TABLE 資料哦

USE [TempDB]
GO
/****** Object:  UserDefinedFunction [dbo].[fn_EmpIDOrgan]    Script Date: 2018/1/30 上午 09:44:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--================================================================================
--撰寫日期 :
--撰寫人員 :
--程式說明 : 帶入員工編號，算出帶出其有權部門的人員
--使用範例 :
--  傳入:員工編號
--  傳出:員工編號EmpID,員工姓名EmpName,工作性質編號WorkTypeID,工作性質WorkType,組織代號OrganID,組織名稱OrganName,部門代號DeptID,部門名稱DeptName
--規　　格 :
--  @Type = 0 查行政流程
--  @Type = 1 表先查直系
--  @Type = 2 表先查直系，無資料再查行政流程
--異動記錄 :
-- select * from [fn_EmpIDOrgan]('0','a00479')
-- select * from [fn_EmpIDOrgan]('1','a00479')
-- select * from [fn_EmpIDOrgan]('2','a00479')
-- select * from [fn_EmpIDOrgan]('0','a07006')
-- select * from [fn_EmpIDOrgan]('1','a07006')
-- select * from [fn_EmpIDOrgan]('2','a02382')
--================================================================================
 FUNCTION [dbo].[fn_EmpIDOrgan](
@Type VARCHAR(1),@EmpID VARCHAR(100) )

RETURNS @TABLE TABLE (
        EmpID   varchar(6),
        EmpName nvarchar(12),
  WorkTypeID varchar(50),
  WorkType varchar(200),
  OrganID varchar(20),
  OrganName varchar(30),
  DeptID varchar(8),
  DeptName varchar(30)
                        )
AS

BEGIN
    declare @RowCount int

    if (@Type = 0) --查行政流程
 BEGIN
    --判斷如果該人員非主管的話，只能回傳自已的資料
    SELECT @RowCount=Count(*) FROM TempDB.dbo.OrgShare
    INNER JOIN TempDB.dbo.EmpShare on EmpShare.OrganID = OrgShare.OrganID
    WHERE EmpShare.EmpID=@EmpID and OrgShare.Boss=@EmpID and  InValidFlag='0'

    if (@RowCount >0 )
    BEGIN
  WITH Organ AS
  (  
   SELECT OrgShare.OrganID,OrgShare.OrganName, 1 AS treelevel
   FROM TempDB.dbo.OrgShare
   INNER JOIN TempDB.dbo.EmpShare on EmpShare.OrganID = OrgShare.OrganID
   WHERE EmpShare.EmpID=@EmpID and OrgShare.Boss=@EmpID and  InValidFlag='0'
   UNION ALL
   SELECT o.OrganID,o.OrganName,treelevel +1
   FROM TempDB.dbo.OrgShare o
   JOIN Organ o1
   ON o1.OrganID = o.UpOrganID and InValidFlag='0'
  )
 
  INSERT INTO @TABLE (EmpID,EmpName,WorkTypeID,WorkType,OrganID,OrganName,DeptID,DeptName)
  SELECT EmpID,NameN,WorkTypeID,WorkType,OrganID,OrganName,DeptID,DeptName FROM TempDB.dbo.EmpShare WHERE OrganID in (SELECT OrganID FROM Organ) and WorkStatus='1' ;
    END
    ELSE
    BEGIN
  INSERT INTO @TABLE (EmpID,EmpName,WorkTypeID,WorkType,OrganID,OrganName,DeptID,DeptName)
  SELECT EmpID,NameN,WorkTypeID,WorkType,OrganID,OrganName,DeptID,DeptName FROM TempDB.dbo.EmpShare WHERE EmpShare.EmpID=@EmpID and WorkStatus='1' ;
    END   
 END

 if (@Type = 1) --查直系流程
 BEGIN
    --判斷如果該人員非主管的話，只能回傳自已的資料/空值
    SELECT @RowCount=Count(*) FROM TempDB.dbo.tx_OrgFlowShare tx_OrgFlowShare
    INNER JOIN TempDB.dbo.tx_EmpFlowShare on tx_EmpFlowShare.OrganID = tx_OrgFlowShare.OrganID COLLATE Chinese_Taiwan_Stroke_CI_AS
    WHERE tx_EmpFlowShare.EmpID=@EmpID and tx_OrgFlowShare.Boss=@EmpID and  InValidFlag='0'

    if (@RowCount >0 )
    BEGIN
  WITH Organ AS
  (  
   SELECT tx_OrgFlowShare.OrganID,tx_OrgFlowShare.OrganName, 1 AS treelevel
   FROM TempDB.dbo.tx_OrgFlowShare
   INNER JOIN TempDB.dbo.tx_EmpFlowShare on tx_EmpFlowShare.OrganID = tx_OrgFlowShare.OrganID COLLATE Chinese_Taiwan_Stroke_CI_AS  
   WHERE tx_EmpFlowShare.EmpID=@EmpID and tx_OrgFlowShare.Boss=@EmpID and  InValidFlag='0'
   UNION ALL
   SELECT o.OrganID,o.OrganName,treelevel +1
   FROM TempDB.dbo.tx_OrgFlowShare o
   JOIN Organ o1
   ON o1.OrganID = o.UpOrganID and InValidFlag='0'
  )
 
  INSERT INTO @TABLE (EmpID,EmpName,WorkTypeID,WorkType,OrganID,OrganName,DeptID,DeptName)
  SELECT tx_EmpFlowShare.EmpID,NameN,WorkTypeID,WorkType,tx_EmpFlowShare.OrganID,tx_EmpFlowShare.OrganName,DeptID,DeptName
  FROM TempDB.dbo.EmpShare
  INNER JOIN TempDB.dbo.tx_EmpFlowShare on EmpShare.EmpID =tx_EmpFlowShare.EmpID COLLATE Chinese_Taiwan_Stroke_CI_AS
  WHERE tx_EmpFlowShare.OrganID in (SELECT OrganID FROM Organ) and WorkStatus='1' ;
    END
    ELSE
    BEGIN
  INSERT INTO @TABLE (EmpID,EmpName,WorkTypeID,WorkType,OrganID,OrganName,DeptID,DeptName)
  SELECT tx_EmpFlowShare.EmpID,NameN,WorkTypeID,WorkType,tx_EmpFlowShare.OrganID,tx_EmpFlowShare.OrganName,DeptID,DeptName
  FROM TempDB.dbo.EmpShare
  INNER JOIN TempDB.dbo.tx_EmpFlowShare on EmpShare.EmpID =tx_EmpFlowShare.EmpID COLLATE Chinese_Taiwan_Stroke_CI_AS
  WHERE tx_EmpFlowShare.EmpID=@EmpID and WorkStatus='1' ;
    END
    END

 if (@Type = 2) -- @Type = 2 表先查直系，無資料再查行政流程
 BEGIN
    --判斷如果該人員非主管的話，只能回傳自已的資料/空值
    SELECT @RowCount=Count(*) FROM TempDB.dbo.tx_OrgFlowShare   
    INNER JOIN TempDB.dbo.tx_EmpFlowShare on tx_EmpFlowShare.OrganID =tx_EmpFlowShare.OrganID COLLATE Chinese_Taiwan_Stroke_CI_AS
    WHERE tx_EmpFlowShare.EmpID=@EmpID and tx_OrgFlowShare.Boss=@EmpID and  InValidFlag='0'

    if (@RowCount >0 )
    BEGIN
  WITH Organ AS
  (  
   SELECT tx_OrgFlowShare.OrganID,tx_OrgFlowShare.OrganName, 1 AS treelevel
   FROM TempDB.dbo.tx_OrgFlowShare tx_OrgFlowShare   
   INNER JOIN TempDB.dbo.tx_EmpFlowShare on tx_EmpFlowShare.OrganID =tx_EmpFlowShare.OrganID COLLATE Chinese_Taiwan_Stroke_CI_AS
   WHERE tx_EmpFlowShare.EmpID=@EmpID and tx_OrgFlowShare.Boss=@EmpID and  InValidFlag='0'
   UNION ALL
   SELECT o.OrganID,o.OrganName,treelevel +1
   FROM TempDB.dbo.tx_OrgFlowShare o
   JOIN Organ o1
   ON o1.OrganID = o.UpOrganID and InValidFlag='0'
  )
 
  INSERT INTO @TABLE (EmpID,EmpName,WorkTypeID,WorkType,OrganID,OrganName,DeptID,DeptName)
  SELECT tx_EmpFlowShare.EmpID,NameN,WorkTypeID,WorkType,tx_EmpFlowShare.OrganID,tx_EmpFlowShare.OrganName,DeptID,DeptName
  FROM TempDB.dbo.EmpShare
  INNER JOIN TempDB.dbo.tx_EmpFlowShare on EmpShare.EmpID =tx_EmpFlowShare.EmpID COLLATE Chinese_Taiwan_Stroke_CI_AS
  WHERE tx_EmpFlowShare.OrganID in (SELECT OrganID FROM Organ) and WorkStatus='1' ;
    END
    ELSE
    BEGIN
  INSERT INTO @TABLE (EmpID,EmpName,WorkTypeID,WorkType,OrganID,OrganName,DeptID,DeptName)
  SELECT tx_EmpFlowShare.EmpID,NameN,WorkTypeID,WorkType,tx_EmpFlowShare.OrganID,tx_EmpFlowShare.OrganName,DeptID,DeptName
  FROM TempDB.dbo.EmpShare
  INNER JOIN TempDB.dbo.tx_EmpFlowShare on EmpShare.EmpID =tx_EmpFlowShare.EmpID COLLATE Chinese_Taiwan_Stroke_CI_AS
  WHERE tx_EmpFlowShare.EmpID=@EmpID and WorkStatus='1' ;
    END

    SELECT @RowCount=Count(*) FROM @TABLE
       if (@RowCount = 0 )
    BEGIN
     --判斷如果該人員非主管的話，只能回傳自已的資料
     SELECT @RowCount=Count(*) FROM TempDB.dbo.OrgShare
     INNER JOIN TempDB.dbo.EmpShare on EmpShare.OrganID = OrgShare.OrganID
     WHERE EmpShare.EmpID=@EmpID and OrgShare.Boss=@EmpID and  InValidFlag='0'

     if (@RowCount >0 )
     BEGIN
   WITH Organ AS
   (  
    SELECT OrgShare.OrganID,OrgShare.OrganName, 1 AS treelevel
    FROM TempDB.dbo.OrgShare
    INNER JOIN TempDB.dbo.EmpShare on EmpShare.OrganID = OrgShare.OrganID
    WHERE EmpShare.EmpID=@EmpID and OrgShare.Boss=@EmpID and  InValidFlag='0'
    UNION ALL
    SELECT o.OrganID,o.OrganName,treelevel +1
    FROM TempDB.dbo.OrgShare o
    JOIN Organ o1
    ON o1.OrganID = o.UpOrganID and InValidFlag='0'
   )
 
   INSERT INTO @TABLE (EmpID,EmpName,WorkTypeID,WorkType,OrganID,OrganName,DeptID,DeptName)
   SELECT EmpID,NameN,WorkTypeID,WorkType,OrganID,OrganName,DeptID,DeptName FROM TempDB.dbo.EmpShare WHERE OrganID in (SELECT OrganID FROM Organ) and WorkStatus='1' ;
     END
     ELSE
     BEGIN
   INSERT INTO @TABLE (EmpID,EmpName,WorkTypeID,WorkType,OrganID,OrganName,DeptID,DeptName)
   --20170927 for nonhrsysuser
   --SELECT EmpID,NameN,WorkTypeID,WorkType,OrganID,OrganName,DeptID,DeptName FROM TempDB.dbo.EmpShare WHERE EmpShare.EmpID=@EmpID and WorkStatus='1' ;
   SELECT UserID,UserName,isnull(WorkTypeID,'') WorkTypeID,isnull(WorkType,'') WorkType,OrganID,'' OrganName,DeptID,'' DeptName
    FROM [dbo].[SE_User] WHERE UserID=@EmpID and UserStatus='1';
     END   
      END
    END

    RETURN
END