Sharepoint Review Application (Sharepoint and MS Access integration)

I just finished a new project - web-based employee performance appraisal application. I had very specific requirements for the project. First of all administration and management must be done from our legacy MS Access 2003 HRIS system, but the managers and employees would have access to their performance reviews from our SharePoint intranet. Also employees reviews must go thru certain process from "drafts" to "final" and then to "archive".
The first tool I had to develop was a web service. The web service's job was to change document access rights for the employees from "contribute" to "read" or to remove access if the review was transferred to a different reviewer. In the same web service I copy a document from the SharePoint template library to the review library and assign initial permissions. The web service gets evoked from ms access when HR Administrator assigns reviews and reviewers.

here is the full text:
//----------------------------------------------------------------------------
using System;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;

using Microsoft.SharePoint;
// Deployment:
//1. change assembly version
// 2. build reviewscls
//3. C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin>gacutil.exe -if "C:\Proects\RevewsCLS\bin\Debug\revewsCLS.dll"
//4.http://site/_layouts/hrreviews.asmx?op=CopyTemplate


[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
public class HRReviews : System.Web.Services.WebService
{
public HRReviews()
{
}
[WebMethod]

public string GiveAccess(string Access,string SitePath, string DocName, string LibName,
string NewUser1, string UserEmail1, string UserName1,
string NewUser2, string UserEmail2, string UserName2,
int FirstID,int SecondID
)
{
string ReturnVal = "";
string s = "";
try
{
SPSite site = new SPSite(SitePath);
site.AllowUnsafeUpdates = true;
SPWeb web = site.OpenWeb();
web.AllowUnsafeUpdates = true;
SPDocumentLibrary oList = (SPDocumentLibrary)web.Lists[LibName];
SPListItemCollection collListItems = oList.Items;
foreach (SPListItem ListItem in collListItems)
{
s = (string)ListItem["Name"];
if (s == DocName)
{
SPRoleDefinition RoleDefinition = new SPRoleDefinition();
if (Access == "Read")
{
RoleDefinition = web.RoleDefinitions.GetByType(SPRoleType.Reader);
}
if (Access == "Contribute")
{
RoleDefinition = web.RoleDefinitions.GetByType(SPRoleType.Contributor);
}
if (Access == "None")
{
SPUser user = web.AllUsers[NewUser1];
ListItem.RoleAssignments.Remove(user);
ReturnVal = " Access was removed for " + NewUser1;
ListItem["FirstID"] = 0;
ListItem["FirstReviewer"] = UserName1;
ListItem.Update();
return ReturnVal;
}
//SPRoleAssignment oRoleAssignment = new SPRoleAssignment("DOMAIN\\User_Alias","User_Alias@Somewhere.com","Display_Name","Notes");

if (!ListItem.HasUniqueRoleAssignments)
{
ListItem.BreakRoleInheritance(true);
}
if (NewUser1.Length > 0)
{
SPRoleAssignment RoleAssignment = new SPRoleAssignment(NewUser1, UserEmail1, UserName1, "notes");
RoleAssignment.RoleDefinitionBindings.Add(RoleDefinition);
ListItem.RoleAssignments.Add(RoleAssignment);
ListItem["FirstID"] = FirstID;
ListItem["FirstReviewer"] = UserName1;
}
if (NewUser2.Length > 0)
{
SPRoleAssignment RoleAssignment = new SPRoleAssignment(NewUser2, UserEmail2, UserName2, "notes");
RoleAssignment.RoleDefinitionBindings.Add(RoleDefinition);
ListItem.RoleAssignments.Add(RoleAssignment);
ListItem["SecondID"] = SecondID;
ListItem["SecondReviewer"] = UserName2;
}
ListItem.Update();
ReturnVal = Access + " Access Granted for " + UserName1 + " and " + UserName2;
return ReturnVal;
}
}
}
catch (Exception ex)
{
ReturnVal += s + "ERROR:" + ex.Message;
}
finally
{
}
return ReturnVal;
}
[WebMethod]
public string CopyTemplate(string SitePath, string fromLibName, string toLibName, string template, string NewUser, string email, string DocumentName, string DueDate, string firstreviewer, string secondreviewer, string reviewtype,int EmployeeID ,int FirstID,int SecondID,int ReviewID)
{
{
string ReturnVal = "";
string s = "";
try
{
SPSite site = new SPSite(SitePath);
site.AllowUnsafeUpdates = true;
SPWeb web = site.OpenWeb();
web.AllowUnsafeUpdates = true;
SPDocumentLibrary oList = (SPDocumentLibrary)web.Lists[fromLibName];
SPListItemCollection collListItems = oList.Items;
foreach (SPListItem ListItem in collListItems)
{
s = (string)ListItem["Name"];
ReturnVal += s;
if (s == template)
{
web.AllowUnsafeUpdates = true;
site.AllowUnsafeUpdates = true;
// ReturnVal += SitePath + toLibName + "/" + DocumentName.Replace(", ", "_") + "_" + DateTime.Now.ToString("yyyyMMdd") + +".doc";
ListItem.CopyTo(SitePath + toLibName + "/" + DocumentName.Replace(", ", "_") + "_" + DateTime.Now.ToString("yyyyMMdd") + ".doc");
//--------------------------------
s = DocumentName.Replace(", ", "_") + "_" + DateTime.Now.ToString("yyyyMMdd") + ".doc";

SPDocumentLibrary toList = (SPDocumentLibrary)web.Lists[toLibName];
SPListItemCollection toListItems = toList.Items;
foreach (SPListItem toListItem in toListItems)
{
toListItem.UnlinkFromCopySource();
string toDoc = (string)toListItem["Name"];
if (s == toDoc)
{
ReturnVal = SitePath + toLibName + "/" + DocumentName.Replace(", ", "_") + "_" + DateTime.Now.ToString("yyyyMMdd") + ".doc";
SPRoleDefinition RoleDefinition = web.RoleDefinitions.GetByType(SPRoleType.Contributor);
//SPRoleAssignment oRoleAssignment = new SPRoleAssignment("DOMAIN\\User_Alias","User_Alias@Somewhere.com","Display_Name","Notes");
SPRoleAssignment RoleAssignment = new SPRoleAssignment(NewUser, email, DocumentName, "notes");
RoleAssignment.RoleDefinitionBindings.Add(RoleDefinition);
if (!toListItem.HasUniqueRoleAssignments)
{
toListItem.BreakRoleInheritance(true);
}
toListItem.RoleAssignments.Add(RoleAssignment);
toListItem["Title"] = "Review By " + NewUser;
toListItem["DueDate"] = DueDate;
toListItem["FirstReviewer"] = firstreviewer;
toListItem["SecondReviewer"] = secondreviewer;
toListItem["ReviewType"] = reviewtype;
toListItem["EmployeeID"] = EmployeeID;
toListItem["FirstID"] = FirstID;
toListItem["SecondID"] = SecondID;
toListItem["ReviewID"] = ReviewID;
toListItem.Update();
return ReturnVal;
} } } } }

catch (Exception ex)
{
ReturnVal += " ** msg:" + ex.Message;
}
return ReturnVal;
} } }

//----------------------------------------------------------------------------


Next, I had to install "Microsoft Office 2003 Web Services Toolkit 2.01.exe" and create in MS Access web reference and class to use it in my forms.
Then I discovered that I could link ANY SharePoint list or library to access and work with it as with any table. That was great! I quickly created couple forms to manage my review library and change any document properties from there.
Just like that:
DLookup("ReviewID", "spReviews", "ReviewID=" && me.txtReviewID
where spReview is the SharePoint document library and txtReviewID is the field on my MS Access form. I could also run "select" and "update" queries the same way as i do for native tables.




This is how the process works:

HR Administrator picks a reviewer from the combo box on the form and assigns employees, templates and due dates. Then she clicks on "generate reviews and send email' button and ms access runs the web service. The web service picks a template from the SharePoint template library and copies to the review library, renames and gives necessary permissions to the new review document. Then I use ms word automation to insert employee and reviewer names in the document, just like that:
Public Function Insert_Names(Employee As String, DueDate As String, Reviewer As String, Path As String) As Integer
On Error GoTo ErrorHandler
Dim doc As Word.Document
Dim WordObj As Word.Application
Set WordObj = New Word.Application
WordObj.Documents.Open Path
WordObj.Visible = True
With WordObj.Selection
If Trim(Nz(Employee, "")) <> "" Then
.Goto what:=wdGoToBookmark, Name:="Employee"
.TypeText Employee
End If
If Trim(Nz(DueDate, "")) <> "" Then
.Goto what:=wdGoToBookmark, Name:="DueDate"
.TypeText DueDate
End If
If Len(Trim(Nz(Reviewer, ""))) > 3 Then
.Goto what:=wdGoToBookmark, Name:="Reviewer"
.TypeText Reviewer
wdAlignParagraphLeft
End If
End With
WordObj.Quit (wdSaveChanges)
Set WordObj = Nothing
Insert_Names = 0
Exit Function ' Exit to avoid handler.
ErrorHandler: ' Error-handling routine.
MsgBox Err.Description
Insert_Names = 1
End Function

The last and easy step was done with the SharePoint Designer.
I created a blank page (no web parts mess) and inserted three dataviews bound to my review library. Each dataview was filtered according to the review status and type.
That was it.
Web service deployment was a pain. I am not sure if I could control permissions from inside of the MS Access to avoid web services all together.