show manager approve documents tHRough store

Question

1.11/5 (2 votes)

See more:

, +

i have a scenario that when any user first upload document then this go for approval first to supervisor then when supervisor approve/reject documents then only approve documents go to manager then again when manager approve/reject document then only approve document shows to director for final approval

SQL

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'  

  end

http://i.stack.imgur.com/w2kS6.png[^]

here above record kanez is manager and sundus is supervisor

here i want to show only kanez approve documents show to director then he will be able to final approval but here both supervisor and manager documents go to director but i want to show only kanez document approve documents to director??

tables
http://i.stack.imgur.com/2yvGC.png[^]

Posted 25-Dec-13 3:58am

Diya Ayesa

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Prasaad SJ · Answer 1 · 2013-12-25T16:32:00

Hi Ayesha,
This is quite an interesting question. I would like to say that you can achieve the result you want in 2 ways.

1st Way.
=> You didn't have any managerial column under users. It would be a good practise to have that.
Eg: you can have a manager column in Users table and assign the UserId to the column.Like. Kanez is manager of Sundus and Director guy is the manager of Kanez. So while filtering the data we can easily filter by getting all the documents that is approved by the person whoever is under this(in this case whoever works is under the Director)

2nd Way
=> Here I assume that the DesigID is the unique Id given to all managers.

Under this case.you can check
if(Director)
Begin
........ Where Users.DesigId==@ManagersDesigId
End

There would be many other possible ways to tackle this problem also. The above is just an example solution. I hope you understand the above.

Christian Graus · Answer 2 · 2013-12-25T08:50:00

First of all, don't store the approve type as a string. store an id to a list of possible approval types. You can then reproduce that list as an enum in code, making it clean on both sides.

I think you're saying you want to add a where clause, where userss.username = 'kanez'. Isn't that all you need ? You're doing a lot of left joining, can I suggest you also use COALESCE for when your department is null, to return a default ? Surely if document info is null ( which is what your left join on document type caters for ), then your proc is pointless ?

show manager approve documents tHRough store

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0