#08 Folder Creation

The following code creates folders off of a Work Flow call. I have removed some of the unusual code we use to check the current folders and align our names. There are a number of unimplemented but known improvements needed (better error processing – I drop some exceptions And I should send back the message properly to the user via WF - see the development kit)  That being noted this code will need an Active Directory ID with write access to your file stores.  Please contact me if you have any questions.  This is using the 1.1 asp.net framework.

Credit Due: http://thedotnet.com/blogs/rick_strahl/archive/2005/02/24/125731.aspx (for AD Impersonation)

C# Class & Method

using System;
using System.DirectoryServices;
using System.IO;
using System.Runtime.InteropServices;
using System.Text.RegularExpressions;
using System.Data.SqlClient;

namespace deltekCustom
{
public class CreateFolders
{

protected static string sServer01 = @”\server1dfsrootPRJ”;
protected static string sServer02 = @”\server2PRJ”;

const int LOGON32_PROVIDER_DEFAULT        = 0;
const int LOGON32_LOGON_INTERACTIVE       = 2;
const int LOGON32_LOGON_NETWORK           = 3;
const int LOGON32_LOGON_BATCH             = 4;
const int LOGON32_LOGON_SERVICE           = 5;
const int LOGON32_LOGON_UNLOCK            = 7;
const int LOGON32_LOGON_NETWORK_CLEARTEXT = 8;
const int LOGON32_LOGON_NEW_CREDENTIALS   = 9;
protected static string connDeltek = “***”;

[DllImport("advapi32.dll", SetLastError=true)]
public static extern int LogonUser(
string lpszUsername,
string lpszDomain,
string lpszPassword,
int dwLogonType,
int dwLogonProvider,
out IntPtr phToken
);
[DllImport("advapi32.dll", SetLastError=true)]
public static extern int ImpersonateLoggedOnUser(
IntPtr hToken
);

[DllImport("advapi32.dll", SetLastError=true)]
static extern int RevertToSelf();

public CreateFolders()   {      }

public string SetupClientFolders(string ClientID, string WBS1, string Server1, string Server2, string AUX )
{
IntPtr lnToken;

int TResult = LogonUser(“***”,”***”, @”***”,LOGON32_LOGON_INTERACTIVE,LOGON32_PROVIDER_DEFAULT,out lnToken);

ImpersonateLoggedOnUser(lnToken);

if ( TResult > 0 )
{
bool bOK = true;
// VALIDATE KEY PARMS
if (AUX.Trim().Length > 0)
bOK = validAdditionalFolderText(AUX);

if (WBS1.Trim().Length == 0)
{
sReturn  = “Missing Job Number – Error on Folder Creation”;
bOK = false;
}

string Client = getClient(ClientID);
if (Client.Trim().Length == 0)
{
sReturn  = “Missing Client ID – Error on Folder Creation”;
bOK = false;
}

// PROCESS EACH SERVER SELECTED if VALID
if (bOK)
{
string sAUX = “”;
if (Server1 == “Y”)
CreeateJobFolders(sServer01 , Client, WBS1, AUX);
if (Server2 == “Y”)
CreeateJobFolders(sServer02 , Client, WBS1, AUX);
}

RevertToSelf();

}
else
sReturn  = “Security Error on Folder Creation”;

return sReturn;
}

/// <summary>
///  Get Client Code from CL table
/// </summary>
/// <param name=”WBS1?></param>
/// <returns>Client Code </returns>
private string getClient(string ClientID)
{
SqlDataReader dr;
string sClientCode = “”;

string strSQL = “Select Distinct CL.CLient as ClientCode FROM CL WHERE CL.ClientID =  ‘” + ClientID + “‘”;

SqlConnection conn = new SqlConnection(connDeltek);

SqlCommand cmdSelect  = new SqlCommand(strSQL, conn);

try
{
conn.Open();
dr = cmdSelect.ExecuteReader();

if (dr.Read())
{
sClientCode = dr["ClientCode"].ToString();
}
}

catch (Exception)
{
}

finally
{
if (conn != null) { conn.Close(); conn.Dispose(); }
if (cmdSelect != null) { cmdSelect.Dispose(); }
}

return sClientCode;

}

/// <summary>
/// Validate the Auxiliary text added to folder name
/// </summary>
/// <returns></returns>
private bool validAdditionalFolderText( string AuxText)
{
Regex objRegex;

objRegex = new Regex(“^[ a-zA-Z0-9._#s-]+$”);

return objRegex.IsMatch(AuxText);
}

private string getJobFolderEndingName( string sJob )
{
string sJobEnding = “.00″;
if (sJob.Length == 9 )
sJobEnding = sJob.Substring(7,2);

switch (sJobEnding)
{
case “00″ : return( “00-Overhead”);
case “10″ : return( “10-Proposal” );
case “20″ : return( “20-GISMapping” );
case “30″ : return( “30-ReportStudy” );
case “40″ : return( “40-Design” );
case “50″ : return( “50-DesignBuild” );
case “60″ : return( “60-Construction” );
case “70″ : return( “70-Operations” );
case “80″ : return( “80-PlanReview” );
case “90″ : return( “90-GeneralMunicipalServices” );
default: return( sJob );

}

}

/// <summary>
/// Create sub-folders on particular server for client/job
/// </summary>
/// <param name=”sServerPath”></param>
private void createJobFolders( string sServerPath , string Client, string WBS1 , string Aux )
{
string sWBS1Root =  “”;

if (WBS1.Length > 6)
sWBS1Root = WBS1.Substring(0,6);
else
sWBS1Root = WBS1;

// BASE CLIENT DIRECTORY
createDirectory(sServerPath +  @”" + Client );

// BASE JOB ROOT DIRECTORY
string sBaseJobPath = sServerPath +  @”" + Client +  @”" + sWBS1Root + Aux;
createDirectory(sBaseJobPath);

// DRAWING DIRECTORY UNDER JOB ROOT
createDirectory(sBaseJobPath + @”CADD-SURVEY”);
createDirectory(sBaseJobPath + @”CADD-SURVEYSURVEY”);
createDirectory(sBaseJobPath + @”CADD-SURVEYDRAWINGS”);
createDirectory(sBaseJobPath + @”CADD-SURVEYPLOTS”);
createDirectory(sBaseJobPath + @”CADD-SURVEYSCANS”);
// BASE JOB SUBNUMBER DIRECTORY
string JobSubnumberPath = sBaseJobPath +  @”" + getJobFolderEndingName(WBS1);
createDirectory(JobSubnumberPath);

// DEFAULT DIRECTORIES
createDirectory(JobSubnumberPath + @”AS SENT”);

createDirectory(sBaseJobPath  +    @”CADD-SURVEYDRAWINGSDGNS”);
createDirectory(sBaseJobPath  +    @”CADD-SURVEYDRAWINGSDWGS”);

}

private bool createDirectory ( string sDirName )
{
// Determine whether the directory exists.
if (Directory.Exists(sDirName))
{
return(false);
}

DirectoryInfo di = Directory.CreateDirectory( sDirName );

return(true);

}
}
}

#07 SQL UDF to Clean Up HTML tags

When using SQL Reporting to report on text fields that are formatted with html tags this user defined function UDF (a modified copy of a post by William McEvoy) is helpful. It does not clean up all but does a decent job on most common tags. You can add offending tags and code to the replace special character’s list (ie.   below)

Create FUNCTION  [dbo].[ScrapeText]
(
  @string varchar(8000)
)
returns varchar(8000)

AS
BEGIN
---------------------------------------------------------------------------------------------------
-- Title:        ScrapeText
--
-- Date Created: April 4, 2006
--
-- Author:       William McEvoy
--
-- Description:  This function will attempt to remove rtf formatting from a string. This is
--               accomplished by concetenating all text contained between greater than and less
--               than signs within the formatted text.
--
-- Example:      <P>This text will be parsed and returned but not the P's</P>
---------------------------------------------------------------------------------------------------
-- Date Revised:  5/3/08
-- Author:       D Gorman
-- Reason:      Minor HTML tags & codes added to replace
---------------------------------------------------------------------------------------------------

declare @text  varchar(8000),
        @PenDown char(1),
        @char  char(1),
        @len   int,
        @count int

select  @count = 0,
        @len   = 0,
        @text  = ''

-- Add tokens
select @string = '>' + @string + '<'

-- Replace Special Characters
select @string = replace(@string,'&nbsp;',' ')
select @string = replace(@string,'<P>',char(13))
select @string = replace(@string,'</P>',char(13))
select @string = replace(@string,'&amp;',char(38))
select @string = replace(@string,'&copy;',char(169))

-- Parse out the formatting codes
select @len = len(@string)
while (@count <= @len)
begin
  select @char = substring(@string,@count,1)

  if (@char = '>')
     select @PenDown = 'Y'
  else
  if (@char = '<')
    select @PenDown = 'N'
  else
  if (@PenDown = 'Y')
    select @text = @text + @char

  select @count = @count + 1
end

RETURN @text
END

You would use this ‘upstream’ on the query/view or stored procedure used by the SQL Reporting report dataset definition.  In the report def you’d just reference the NewName field reference.

Select  dbo.ScrapeText(FieldX)  as NewName From ProjectCustomTabFields Where WBS1=’xxxxxx’

#06 SQL View & Employee Search Stored Procedure

If you need to connect to Deltek via some ODBC connection but do not want to show all. I suggest creating a restricted SQL View like this one we use for limited Employee information. If you allow this view read access via SQL authentication and a SQL login you can safely restict the user’s access.   From the SQL management console you can create the view (use xxVW_ as a naming convention to steer around any others there – where xx is the abbreviation of your company or something consistent)

Create VIEW [dbo].[xxVW_FilteredEmployee]
AS
SELECT     Employee, FirstName, MiddleName, LastName, Status, Org, EMail
FROM         dbo.EM

– Grant Access to your SQL login

Grant Select on [xxVW_FilteredEmployee] To DeltekUserR

On a separate idea – If you want a Stored Procedure that allows search parms to be ignored if not present here is an example (more on how this may fit in & be used later)

Create PROCEDURE [dbo].[xxSP_searchEmployee]
(
   @FirstName  nvarchar(255)
 , @LastName   nvarchar(255)
 , @Supervisor  nvarchar(255)
)
AS
BEGIN
 SET NOCOUNT OFF;

 SELECT   Employee, FirstName, MiddleName, LastName, Status, Org, EMail, Supervisor
 FROM     EM
 WHERE   (EM.FirstName LIKE CASE When @FirstName is null THEN EM.FirstName  ELSE  @FirstName + ‘%’  END )
 AND  (EM.LastName  LIKE CASE  When @LastName is null THEN EM.LastName  ELSE  @LastName + ‘%’  END )
 AND  (EM.Supervisor = CASE  When @Supervisor is null THEN EM.Supervisor    ELSE  @Supervisor    END ) 
END

– Grant Access to your SQL login

Grant Execute on [xxSP_searchEmployee] To DeltekUserR

You can test by submitting this query

xxSP_searchEmployee, d,g,null :: which will wildcard first name starting with D and last name with G ignoring the supervisor. 

#05 Employee Directory

Update: Post #19 offers a Stored Procedure and a (part 2+)  Sharepoint Webpart code for an employee directory.

Note: This uses asp.net 1.1 framework – should migrate to 2.0+ just fine but has not been tested on the newer frameworks. I plan to replace the data calls using the DAAB see Post #04. It should at least show you how easy you can filter and post to a grid.

As I spend time & convert this over to use a better data access layer I will repost but as is should give you an idea of what to do (if you know asp.net).

First here is what it looks like:

---

asp.net

<asp:dropdownlist id=”dlRecordsPerPage” runat=”server” AutoPostBack=”True”>
<asp:ListItem Value=”25″ Selected=”True”>25</asp:ListItem>
<asp:ListItem Value=”50″>50</asp:ListItem>
<asp:ListItem Value=”100″>100</asp:ListItem>
<asp:ListItem Value=”500″>500</asp:ListItem>
</asp:dropdownlist>
<asp:button id=”ExportToExcel” runat=”server” CssClass=”b1″ Visible=”False” Text=” Export to Excel”></asp:button>
<asp:datagrid id=”DataGrid1″ Width=”680px” BorderStyle=”None” Height=”20px” OnSortCommand=”DataGrid1_SortCommand”
AllowSorting=”True” OnPageIndexChanged=”DataGrid1_PageIndexChanged” Runat=”Server” PageSize=”25″
GridLines=”Vertical” CellPadding=”3″ BorderWidth=”1px” AllowPaging=”True” BorderColor=”#999999″
BackColor=”White” AutoGenerateColumns=”False”>

<SelectedItemStyle Font-Bold=”True” ForeColor=”White” BackColor=”#008A8C”></SelectedItemStyle>
<AlternatingItemStyle Height=”20px” BackColor=”Gainsboro”></AlternatingItemStyle>
<ItemStyle Height=”20px” ForeColor=”Black” BackColor=”#EEEEEE”></ItemStyle>
<HeaderStyle Font-Bold=”True” HorizontalAlign=”Center” Height=”40px” ForeColor=”White” BackColor=”#000084″></HeaderStyle>
<FooterStyle Height=”20px” ForeColor=”Black” BackColor=”#CCCCCC”></FooterStyle>
<Columns>
<asp:BoundColumn DataField=”PersonID” HeaderText=”Emp ID” SortExpression=”A.Employee”>
<ItemStyle Wrap=”False” HorizontalAlign=”Center” Width=”40px” CssClass=”smallAlert”></ItemStyle>
</asp:BoundColumn>
<asp:HyperLinkColumn Text=”<img border=’0′ src=’/newnet/images/intranet/person.gif’ />” Target=”_self”
DataNavigateUrlField=”PersonID” DataNavigateUrlFormatString=”http://intranet/newnet/resources/EmployeeView.aspx?id={0}”
HeaderText=”View ” Visible=”false”>
<ItemStyle CssClass=”smallredC”></ItemStyle>
</asp:HyperLinkColumn>
<asp:HyperLinkColumn Text=”<img border=’0′ src=’/newnet/images/intranet/email.gif’ />” DataNavigateUrlField=”Email”
DataNavigateUrlFormatString=”mailto:{0}” HeaderText=”Email”>
<ItemStyle HorizontalAlign=”Center” Width=”100px” CssClass=”smallredC”></ItemStyle>
</asp:HyperLinkColumn>
<asp:HyperLinkColumn Text=”<img border=’0′ src=’/newnet/images/intranet/person.gif’ />” DataNavigateUrlField=”Employee”
DataNavigateUrlFormatString=”http://intranet/newnet/images/emp/{0}.jpg” HeaderText=”Photo”>
<ItemStyle HorizontalAlign=”Center” Width=”100px” CssClass=”smallredC”></ItemStyle>
</asp:HyperLinkColumn>
<asp:BoundColumn DataField=”FirstName” HeaderText=”First Name” SortExpression=”FirstName”>
<ItemStyle Wrap=”False” Width=”90px” CssClass=”smallred”></ItemStyle>
</asp:BoundColumn>
<asp:BoundColumn DataField=”LastName” HeaderText=”Last Name” SortExpression=”LastName”>
<ItemStyle Wrap=”False” Width=”90px” CssClass=”smallred”></ItemStyle>
</asp:BoundColumn>
<asp:BoundColumn DataField=”WorkPhoneExt” HeaderText=”Ext”>
<ItemStyle Wrap=”False” Width=”35px” CssClass=”smalldetail”></ItemStyle>
</asp:BoundColumn>
<asp:BoundColumn DataField=”WorkPhone” HeaderText=”Direct”>
<ItemStyle Wrap=”False” Width=”65px” CssClass=”smalldetail”></ItemStyle>
</asp:BoundColumn>
<asp:BoundColumn DataField=”MobilePhone” HeaderText=”Mobile”>
<ItemStyle Wrap=”False” Width=”75px” CssClass=”smalldetail”></ItemStyle>
</asp:BoundColumn>
<asp:BoundColumn DataField=”Org” HeaderText=”Primary Dept” SortExpression=”Org”>
<ItemStyle Wrap=”False” CssClass=”smallredC”></ItemStyle>
</asp:BoundColumn>
<asp:BoundColumn DataField=”custSecondaryDepartment” HeaderText=”Secondary Dept” SortExpression=”C.custSecondaryDepartment”>
<ItemStyle Wrap=”False” CssClass=”smallredC”></ItemStyle>
</asp:BoundColumn>
<asp:BoundColumn DataField=”Office” HeaderText=”Office” SortExpression=”B.Cust5ULPhysical”>
<ItemStyle Wrap=”False” CssClass=”smallredC”></ItemStyle>
</asp:BoundColumn>
</Columns>
<PagerStyle Height=”20px” HorizontalAlign=”Center” ForeColor=”Black” BackColor=”#999999″ Mode=”NumericPages”></PagerStyle>
</asp:datagrid>

C#:

Note: The embedded query should be cleaned up using join clauses and the should be replaced by a stored procedure perhaps using WHEN CASE to ignore unused filter parms.

public void BindDataGrid( string sOrderParm )
{

SqlDataAdapter daList;
DataSet dsClients;

SqlConnection conn = new SqlConnection(connstring);
conn.Open();
string strDeptCode = “%” + dlDepartment.SelectedValue.ToString() + “%”;
string strRegionCode = “%” + dlRegion.SelectedValue.ToString() + “%”;

string strSQL = ” Select A.Employee , (RTRIM(LastName) + ‘ ‘ + ISNULL(Suffix,”)) as LastName , PreferredName As FirstName, MiddleName, Org, Region, WorkPhone, WorkPhoneExt, MobilePhone, B.Cust5ULPhysical As Office, C.custSecondaryDepartment “;
strSQL += ” , RTRIM(A.Employee) As PersonID, A.Email “;
strSQL += ” FROM EM A , EmployeeCustomTabFields B, EmployeeCustomTabFields C “;
strSQL += ” WHERE A.Status = ‘A’ “;
strSQL += ” AND A.Employee = B.Employee “;
strSQL += ” AND A.Employee = C.Employee “;
strSQL += ” AND A.[LastName] Like ‘” + txtLastName.Text.Trim() + “%’ And A.[Employee] Like ‘” + txtEmpID.Text.Trim() + “%’ And ( A.[PreferredName] Like ‘” + txtFirstName.Text.Trim() + “%’ OR A.[FirstName] like ‘” + txtFirstName.Text.Trim() + “%’ ) AND ( A.[Org] like ‘” + strDeptCode + “%’ or C.custSecondaryDepartment like ‘%” + strDeptCode + “%’ ) AND ( A.WorkPhone like ‘%” + txtPhone.Text.Trim() + “%’ OR A.WorkPhoneExt like ‘%” + txtPhone.Text.Trim() + “%’ OR A.MobilePhone like ‘%” + txtPhone.Text.Trim() + “%’) “;
strSQL += ” AND B.Cust5ULPhysical like ‘%” + strRegionCode + “%’ “;
strSQL += ” Order By ” + sOrderParm;

daList = new SqlDataAdapter(strSQL, conn );
dsClients = new DataSet();
daList.Fill( dsClients );
DataGrid1.PageSize = Int32.Parse(dlRecordsPerPage.SelectedValue.ToString());
DataGrid1.DataSource = dsClients;
try
{
DataGrid1.DataBind();
if (DataGrid1.Items.Count == 0)
{
txtError.Text = “No Records Found”;
txtError.Visible = true;
DataGrid1.Visible = false;

}
else
{
ExportToExcel.Visible = true;
DataGrid1.Visible = true;
txtError.Visible = false;
txtTest.Visible = false;
}
}
catch
{
txtError.Text = “No Records Found”;
txtError.Visible = true;
DataGrid1.Visible = false;
}
finally
{
if (conn != null) { conn.Close(); conn.Dispose(); }
if (daList != null) { daList.Dispose(); }
if (dsClients != null) { dsClients.Dispose(); }
}

}

#04 Microsoft’s Enterprise Data Layer

Note: This is a more advanced programming topic

As we migrated to the 2.0+ (3.x) frameworks I have been using Microsoft’s Enterprise Library and in particular the DAAB (Data Access Application Blocks). Here is a look at one method in the Employee Data Access layer. This is a static wrapper method but keeps the code clean.

There are other method calls but I have stripped them out for the moment.

The following object allows me to bind to a Telerik RadGrid (Gridview)  like this:

protected void rgEmployees_NeedDataSource(object source, GridNeedDataSourceEventArgs e) {

rgEmployees.DataSource = Employee.getAllEmployees();
}

Note: This is an Telerik AJAX method call but the binding to a simple Gridview is just the same. Ideally this receives some search parms that filter the return a tailored dataset.

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Data.Common;
using System.Web.Configuration;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using Telerik.WebControls;
using System.Data.SqlTypes;

public class Employee
{
public Employee ()
{

}

/// <summary>
/// Gets All Employees
/// </summary>
/// <returns> dataset of EM data </returns>
static public DataSet getAllEmployees()
{
DataSet ds = null;
DbCommand dbc = null;
Database db = DatabaseFactory.CreateDatabase(“*****”);    // readonly ID
dbc = db.GetStoredProcCommand(“SPName”);                    // SP reference

try
{
ds = db.ExecuteDataSet(dbc);
}
catch (Exception exc)
{
throw new IntranetException(exc.Message,
exc,
SystemConstants.Intranet_ErrorMessage);
}

return ds;

}

}

#03 SQL Reporting Link Rendering A Report as a PDF

Once you create a SQL Reporting Services report and deploy (or upload the .rdl and .rds files) you can reference the report via a link and render it as a pdf (or Excel) via the following pattern (assumes no ssl)

http://ServerName/ReportServer?%2fFolderName%2fReportName&parm1=value&rs%3aCommand=Render&rs%3AFormat=PDF

Variables (note ‘ReportServer’ is a constant)

• ServerName – Server housing SQL Reporting Services
• FolderName – Typically the name of your Visual Studio Project
• ReportName – name of the .rdl file defining your report
• parm1=value  (ignore if not passing parms) You can add as many as you want like &parm2=value2…)

Note: The last phrase tells the report server to render as a pdf: 3aCommand=Render&rs%3AFormat=PDF You can change that last parm (Format=) to Excel from PDF to render a spreadsheet (assuming Excel is installed on your client PC)

This link can be placed in an ASP.net page or user control and redirected via the Response.Redirect command (often fired via a button click event/delegate) using string concatenation to add in any variables like a date range etc.  You could also use a Sharepoint webpart connected to some filter fields controlling any parameters sent just like a ASP.net page or user control. Another idea is that the link can be added as a static link on the Dashboard in Vision.