显示管理器批准文档tHRough存储过程

Question

我有一个场景，当任何用户首先上传文档，然后这先去批准主管，然后当主管批准/拒绝文档时，然后只批准文档，然后当经理批准/拒绝文档时，再次批准文档，然后只批准文档显示给主管进行最终批准

ALTER PROCEDURE [dbo].[ALLDOCUMNETS]
AS
 begin
SELECT DocumentInfo.DocID as DocumentID, 
dbo.DocumentInfo.DocName as DocumentName,
dbo.DocumentInfo.Uploadfile as FileUploaded, 
dbo.DocumentInfo.UploadedBy as UploadedBy,
dbo.Userss.Email as UserEmail,
dbo.DocType.DocType as Document, 
dbo.Department.DepType as Department ,
dbo.Approval.AppoveBy, 

dbo.ApproveType.ApproveType as Status

FROM dbo.Department
left JOIN dbo.DocumentInfo
ON dbo.DocumentInfo.DepID=dbo.Department.DepID 

left JOIN dbo.DocType
     ON  dbo.DocumentInfo.DocTypeID=dbo.DocType.DocTypeID
 inner JOIN dbo.Userss on Userss.UserName =dbo.DocumentInfo.UploadedBy 

  inner join  dbo.Approval ON dbo.DocumentInfo.DocID = dbo.Approval.DocID INNER JOIN
    dbo.ApproveType ON dbo.Approval.ApproveID = dbo.ApproveType.ApproveID 
  AND  dbo.ApproveType.ApproveType = 'Approve'  
  left join Designation on dbo.Userss.DesigID=dbo.Designation.DesigID

 WHERE designation.DesigType = 'Manager'

结束

请参阅此图像record in table

在上图中，卡内兹是经理，桑德斯是主管。

在这里，我只想向总监展示kanez批准文件，然后他将能够进行最终批准，但在这里，主管和经理的文件都会提交给总监，但我只想向总监展示kanez文件。

表tables表viewS

我是怎么做到的？

名称表这是我的名称表

 DesignID DesigType
 1        SuperVisor
 2        Manager
 3        Director
  4       BasicUsers

这是我的批准表

SeqNo DocID ApproveID AppoveBy    ApproveDate
 258   30     1          sundus 2013-12-25 18:33:36.373
 259    30    1          kaneez 2013-12-25 18:34:47.347

现在请告诉我我能做些什么

Answer 1

添加到您的查询中：

inner JOIN dbo.Userss on Userss.UserName =dbo.DocumentInfo.UploadedBy
                       AND UserTypeId = manger

Answer 2

现在，由于我不知道所有表的确切模式，我只是猜测您的Users table有一个用于DesignID的字段，我已经在您的过程中创建了一个变量@ApprovedBy，现在，如果您想从特定级别的人员那里获得所有批准的文档，您所需要做的就是将Designation作为参数传递给您的过程，它将提取该级别的员工批准的所有文档。

还要检查Users Table的拼写，因为您使用了带有双s的Userss，不确定这是真的还是拼写错误。

ALTER PROCEDURE [dbo].[ALLDOCUMNETS]
@ApprovedBy VARCHAR(50)
AS
 BEGIN
  SET NOCOUNT ON;

    SELECT 
            DocumentInfo.DocID as DocumentID, 
            dbo.DocumentInfo.DocName as DocumentName,
            dbo.DocumentInfo.Uploadfile as FileUploaded, 
            dbo.DocumentInfo.UploadedBy as UploadedBy,
            dbo.Userss.Email as UserEmail,
            dbo.DocType.DocType as Document, 
            dbo.Department.DepType as Department ,
            dbo.Approval.AppoveBy, 
            dbo.ApproveType.ApproveType as Status

    FROM dbo.Department INNER JOIN dbo.DocumentInfo
    ON dbo.DocumentInfo.DepID = dbo.Department.DepID 
    INNER JOIN dbo.DocType
    ON dbo.DocumentInfo.DocTypeID = dbo.DocType.DocTypeID
    INNER JOIN dbo.Userss 
    ON Userss.UserName =dbo.DocumentInfo.UploadedBy 
    INNER JOIN  dbo.Approval 
    ON dbo.DocumentInfo.DocID = dbo.Approval.DocID 
    INNER JOIN dbo.ApproveType 
    ON dbo.Approval.ApproveID = dbo.ApproveType.ApproveID 
    LEFT JOIN Designation 
    ON dbo.Userss.DesigID=dbo.Designation.DesigID
    WHERE designation.DesigType = 'Manager'
    AND  dbo.ApproveType.ApproveType = 'Approve'  
    AND dbo.Approval.AppoveBy IN (SELECT UserName 
                                  FROM Userss.UserName INNER JOIN designation 
                                  ON Userss.DesignID = designation.DesignID
                                  WHERE designation.DesigType = @ApprovedBy)
END

调用进程的

要让经理批准所有文档，只需执行以下操作：

  EXECUTE dbo.ALLDOCUMNETS @ApprovedBy = 'Manager'

要获得主管批准的所有文档，只需执行以下操作：

  EXECUTE dbo.ALLDOCUMNETS @ApprovedBy = 'Supervisor'

诸如此类……