protected void Page_Load(object sender, EventArgs e)
{
// Browser detection and redirecting to error page
if (Request.Browser.Browser.ToLower() == "ie" && Convert.ToDouble(Request.Browser.Version) < 7)
{
SPUtility.TransferToErrorPage("To view this report use later versions of IE 6.0");
}
else
{
try
{
string SiteUrl = MyUtilities.ProjectServerInstanceURL(SPContext.Current);
var Resource_Svc = new Resource();
var Project_Svc = new Project();
Resource_Svc.UseDefaultCredentials = true;
Project_Svc.UseDefaultCredentials = true;
Resource_Svc.Url = SiteUrl + "/_vti_bin/psi/resource.asmx";
Project_Svc.Url = SiteUrl + "/_vti_bin/psi/project.asmx";
Resource_Svc.AllowAutoRedirect = true;
Project_Svc.AllowAutoRedirect = true;
if (MyUtilities.IndividualPages)
LnkConfigButton.PostBackUrl = SiteUrl + "/_layouts/ITXProjectGovernanceReport/ITXPGReport.aspx";
else
LnkConfigButton.Visible = false;
// setting current user uid
LblCurUserUId.Text = Resource_Svc.GetCurrentUserUid().ToString();
// For Group Repeater control
var GroupTable = new DataTable();
GroupTable.Columns.Add("title");
GroupTable.Columns.Add("grpid");
// impersonation here
try
{
var wik = WindowsIdentity.Impersonate(IntPtr.Zero);
}
catch (Exception)
{
}
MyUtilities.ModifyConnectionString(SiteUrl);
using (IObjectScope scope = ObjectScopeProvider1.GetNewObjectScope())
{
// creating the user account into db if not exists
List<Users> userses = (from c in scope.GetOqlQuery<Users>().ExecuteEnumerable()
where c.ResourceUID.Equals(LblCurUserUId.Text)
select c).ToList();
if (userses.Count == 0)
{
scope.Transaction.Begin();
var new_user = new Users();
new_user.ResourceUID = LblCurUserUId.Text;
scope.Add(new_user);
scope.Transaction.Commit();
userses = (from c in scope.GetOqlQuery<Users>().ExecuteEnumerable()
where c.ResourceUID.Equals(LblCurUserUId.Text)
select c).ToList();
}
List<Groups> groups = (from c in scope.GetOqlQuery<Users>().ExecuteEnumerable()
from d in c.groups
where
c.ResourceUID.Equals(LblCurUserUId.Text) &&
!d.UID.Equals(Guid.Empty.ToString())
select d).ToList();
foreach (var group in groups)
{
var new_row = GroupTable.NewRow();
new_row["title"] = group.name;
new_row["grpid"] = group.UID;
GroupTable.Rows.Add(new_row);
}
RptrGroupnames.DataSource = GroupTable;
RptrGroupnames.DataBind();
// For Project name Repeater Control
var ProjectTable = MyUtilities.GetProjects_DataTable(SiteUrl, new Guid(LblCurUserUId.Text));
groups = (from c in scope.GetOqlQuery<Users>().ExecuteEnumerable()
from d in c.groups
where c.ResourceUID.Equals(LblCurUserUId.Text) && d.UID.Equals(Guid.Empty.ToString())
select d).ToList();
if (groups.Count == 0)
{
if (userses.Count > 0)
{
scope.Transaction.Begin();
var new_group = new Groups();
new_group.name = "Not Grouped.";
new_group.UID = Guid.Empty.ToString();
userses[0].groups.Add(new_group);
scope.Add(userses[0]);
scope.Transaction.Commit();
}
groups = (from c in scope.GetOqlQuery<Users>().ExecuteEnumerable()
from d in c.groups
where
c.ResourceUID.Equals(LblCurUserUId.Text) &&
d.UID.Equals(Guid.Empty.ToString())
select d).ToList();
}
// Checking and adding missed projects to the user
foreach (DataRow row in ProjectTable.Rows)
{
var count = (from c in scope.GetOqlQuery<Users>().ExecuteEnumerable()
from d in c.groups
from f in d.projects
where
c.ResourceUID.Equals(LblCurUserUId.Text) &&
f.uid.Equals(row["ProjectUID"].ToString())
select e).Count();
if (count == 0 && groups.Count > 0)
{
scope.Transaction.Begin();
var new_proj_row = new Projects();
new_proj_row.name = row["Title"].ToString();
new_proj_row.uid = row["ProjectUID"].ToString();
groups[0].projects.Add(new_proj_row);
scope.Add(groups[0]);
scope.Transaction.Commit();
}
}
RptrProjectnames.DataSource = (from c in scope.GetOqlQuery<Users>().ExecuteEnumerable()
from d in c.groups
from f in d.projects
where
c.ResourceUID.Equals(LblCurUserUId.Text) &&
d.UID.Equals(Guid.Empty.ToString())
select f).AsEnumerable();
RptrProjectnames.DataBind();
}
}
catch (Exception ex)
{
MyUtilities.ErrorLog("Error at Project Group Congure load due to " + ex.Message,
EventLogEntryType.Error);
if (MyUtilities.DevelopMode)
Response.Write(ex.Message);
}
}
}
public static DataTable GetGovernanceReport(string SiteUrl, Guid CurrentUserUID)
{
var ResultDataTable = new DataTable();
ResultDataTable.Columns.Add("Title");
ResultDataTable.Columns.Add("Start");
ResultDataTable.Columns.Add("Finish");
ResultDataTable.Columns.Add("Type");
try
{
//User Impersionation
WindowsImpersonationContext wik = null;
SPSecurity.RunWithElevatedPrivileges(delegate
{
using (var Site = new SPSite(SiteUrl))
{
SiteUrl = Utilities.GetDefaultZoneUri(Site);
try
{
wik = WindowsIdentity.Impersonate(IntPtr.Zero);
}
catch (Exception)
{ }
ModifyConnectionString(SiteUrl);
using (IObjectScope scope = ObjectScopeProvider1.GetNewObjectScope())
{
// Adding the current user if not exits
int count = (from c in scope.GetOqlQuery<Users>().ExecuteEnumerable()
where c.ResourceUID.Equals(CurrentUserUID.ToString())
select c).Count();
if (count == 0)
{
scope.Transaction.Begin();
var user = new Users();
user.ResourceUID = CurrentUserUID.ToString();
scope.Add(user);
scope.Transaction.Commit();
}
List<Users> users = (from c in scope.GetOqlQuery<Users>().ExecuteEnumerable()
where c.ResourceUID.Equals(CurrentUserUID.ToString())
select c).ToList();
var Project_Svc = new Project()
{
AllowAutoRedirect = true,
Url = SiteUrl + "/_vti_bin/psi/project.asmx",
UseDefaultCredentials = true
};
var CustomField_Svc = new CustomFields()
{
AllowAutoRedirect = true,
Url = SiteUrl + "/_vti_bin/psi/customfields.asmx",
UseDefaultCredentials = true
};
if (Utilities.IsCustomFieldFound(CustomField_Svc, CustomFieldName))
{
var ProjectIDs = Utilities.GetProjectUIDList(Project_Svc, false, false);
string ProjectsList = string.Empty;
foreach (var projectID in ProjectIDs)
{
if (projectID != Guid.Empty)
{
if (ProjectsList == string.Empty)
ProjectsList += "('" + projectID;
else
ProjectsList += "','" + projectID;
}
}
ProjectsList += "')";
string Qry =
@"SELECT puv.ProjectUID,puv." + Project_Stream_Fieldname + @", puv.ProjectName, puv.ProjectStartDate, puv.ProjectFinishDate, '0' as [Type]
into #t1
FROM MSP_EpmProject_UserView as puv
WHERE (puv.[" + Project_Status_Fieldname + @"] <> N'[Closed]') AND (puv.[" + Project_Status_Fieldname + @"] <> N'[Cancelled]') AND (puv.[" + Project_Status_Fieldname + @"] <> N'[Replaced]')
SELECT tuv.ProjectUID, puv." + Project_Stream_Fieldname + @", " +
CustomFieldName +
@" AS Title, MIN(tuv.TaskStartDate) AS Start, MAX(tuv.TaskFinishDate) AS [End], '1' as [Type]
into #t2
FROM MSP_EpmTask_UserView AS tuv INNER JOIN
MSP_EpmProject_UserView AS puv ON tuv.ProjectUID = puv.ProjectUID
WHERE (puv.[" + Project_Status_Fieldname + @"] <> N'[Closed]') AND (puv.[" + Project_Status_Fieldname + @"] <> N'[Cancelled]') AND (puv.[" + Project_Status_Fieldname + @"] <> N'[Replaced]')
GROUP BY " + CustomFieldName + @", puv.Program_Code, tuv.ProjectUID HAVING (CIMBTaskType IS NOT NULL)
INSERT into #t2
select #t1.ProjectUID,#t1.Program_Code, #t1.ProjectName, #t1.ProjectStartDate, #t1.ProjectFinishDate, #t1.[Type]
FROM #t1 INNER JOIN (SELECT DISTINCT #t2.ProjectUID from #t2) AS t2temp ON t2temp.ProjectUID =#t1.ProjectUID
SELECT *
FROM [#t2]
where ProjectUID in " +
ProjectsList +
@"
ORDER BY ProjectUID,[Type], Start
drop table #t1
drop table #t2";
var Conn = new SqlConnection(GetDataBaseConnectionString(SiteUrl));
Conn.Open();
var Result_set = new DataSet();
var Adapter = new SqlDataAdapter(new SqlCommand(Qry, Conn));
Adapter.Fill(Result_set);
DataRow newrow;
var grouptable = new Hashtable();
var datarows = new List<datarow>();
string groupname = string.Empty;
foreach (DataRow row in Result_set.Tables[0].Rows)
{
if (row["Type"].ToString() == "0")
{
List<Groups> groups =
(from c in scope.GetOqlQuery<Users>().ExecuteEnumerable()
from d in c.groups
from e in d.projects
where
c.ResourceUID.Equals(CurrentUserUID.ToString()) &&
e.uid.Equals(row["ProjectUID"].ToString())
select d).ToList();
if (groups.Count > 0)
{
groupname = groups[0].name;
var drow = new datarow();
drow.type = "Group";
drow.title = groupname;
drow.startdate = DateTime.MinValue;
drow.enddate = DateTime.MaxValue;
if (grouptable.ContainsKey(groupname))
{
datarows = (List<datarow>)grouptable[groupname];
drow = new datarow();
drow.type = "Project";
drow.startdate = Convert.ToDateTime(row["Start"]);
drow.enddate = Convert.ToDateTime(row["End"]);
drow.title = row["Title"].ToString();
datarows.Add(drow);
grouptable[groupname] = datarows;
}
else
{
datarows = new List<datarow>();
datarows.Add(drow);
drow = new datarow();
drow.type = "Project";
drow.startdate = Convert.ToDateTime(row["Start"]);
drow.enddate = Convert.ToDateTime(row["End"]);
drow.title = row["Title"].ToString();
datarows.Add(drow);
grouptable.Add(groupname, datarows);
}
}
else
{
groupname = string.Empty;
string[] streams = row["Program_Code"].ToString().Split('.');
if (streams.Length > 3)
groupname = streams[4];
else if (streams.Length > 0)
groupname = streams[streams.Length - 1];
if (groupname == string.Empty)
groupname = "Not Configured.";
groups =
(from c in scope.GetOqlQuery<Users>().ExecuteEnumerable()
from d in c.groups
where
c.ResourceUID.Equals(CurrentUserUID.ToString()) &&
d.name.Equals(groupname)
select d).ToList();
if (groups.Count == 0)
{
scope.Transaction.Begin();
var group = new Groups();
group.name = groupname;
group.UID = Guid.NewGuid().ToString();
var project = new Projects();
project.name = row["Title"].ToString();
project.uid = row["ProjectUID"].ToString();
group.projects.Add(project);
users[0].groups.Add(group);
scope.Add(users[0]);
scope.Transaction.Commit();
}
else
{
scope.Transaction.Begin();
var project = new Projects();
project.name = row["Title"].ToString();
project.uid = row["ProjectUID"].ToString();
groups[0].projects.Add(project);
scope.Add(groups[0]);
scope.Transaction.Commit();
}
var drow = new datarow();
drow.type = "Group";
drow.title = groupname;
drow.startdate = DateTime.MinValue;
drow.enddate = DateTime.MaxValue;
if (grouptable.ContainsKey(groupname))
{
datarows = (List<datarow>)grouptable[groupname];
drow = new datarow();
drow.type = "Project";
drow.startdate = Convert.ToDateTime(row["Start"]);
drow.enddate = Convert.ToDateTime(row["End"]);
drow.title = row["Title"].ToString();
datarows.Add(drow);
grouptable[groupname] = datarows;
}
else
{
datarows = new List<datarow>();
datarows.Add(drow);
drow = new datarow();
drow.type = "Project";
drow.startdate = Convert.ToDateTime(row["Start"]);
drow.enddate = Convert.ToDateTime(row["End"]);
drow.title = row["Title"].ToString();
datarows.Add(drow);
grouptable.Add(groupname, datarows);
}
}
}
else if (groupname != string.Empty)
{
datarows =
(List<datarow>)
grouptable[groupname];
var drow = new datarow();
drow.title = row["Title"].ToString();
drow.startdate =
Convert.ToDateTime(row["Start"]);
drow.enddate =
Convert.ToDateTime(row["End"]);
drow.type = "CF";
datarows.Add(drow);
grouptable[groupname] = datarows;
}
}
// Adding the rows into datatable
foreach (DictionaryEntry drws in grouptable)
{
foreach (datarow drow in (List<datarow>)drws.Value)
{
DataRow row = ResultDataTable.NewRow();
row["Title"] = drow.title;
row["Start"] = drow.startdate;
row["Finish"] = drow.enddate;
row["Type"] = drow.type;
ResultDataTable.Rows.Add(row);
}
}
}
else
{
ErrorLog(
"The Custom field called " + CustomFieldName + " is not found in the instanse " +
Site.Url, EventLogEntryType.FailureAudit);
}
}
}
}
);
}
catch (Exception ex)
{
ErrorLog("Error at loading project list due to " + ex.Message, EventLogEntryType.Error);
}
return ResultDataTable;
}