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;
}
public static DataTable GetProjectTasks(SPContext context, Guid ProjectUID)
{
var ResultTaskTable = new DataTable();
try
{
string SiteUrl = ProjectServerInstanceURL(context);
SPSecurity.RunWithElevatedPrivileges(delegate
{
using (var site = new SPSite(SiteUrl))
{
SiteUrl = Utilities.GetDefaultZoneUri(site);
}
}
);
var Project_Svc = new Project
{
UseDefaultCredentials =
true,
Url = (SiteUrl +
"/_vti_bin/psi/project.asmx"),
AllowAutoRedirect = true
};
var projectDataSet = Project_Svc.ReadProjectEntities(ProjectUID, 2, DataStoreEnum.WorkingStore);
ResultTaskTable.Columns.Add("ID");
ResultTaskTable.Columns.Add("Task");
ResultTaskTable.Columns.Add("UID");
ResultTaskTable.Columns.Add("start date");
ResultTaskTable.Columns.Add("end date");
foreach (ProjectDataSet.TaskRow taskRow in projectDataSet.Task.Rows)
{
DataRow NewRow = ResultTaskTable.NewRow();
NewRow["ID"] = taskRow.TASK_ID;
NewRow["Task"] = taskRow.TASK_NAME;
NewRow["UID"] = taskRow.TASK_UID;
if (!taskRow.IsTASK_START_DATENull())
NewRow["start date"] = taskRow.TASK_START_DATE.ToString("dd/MM/yy");
else
NewRow["start date"] = " -- ";
if (!taskRow.IsTASK_FINISH_DATENull())
NewRow["end date"] = taskRow.TASK_FINISH_DATE.ToString("dd/MM/yy");
else
NewRow["end date"] = " -- ";
ResultTaskTable.Rows.Add(NewRow);
}
}
catch (Exception ex)
{
ErrorLog("Error at getting project tasks due to " + ex.Message, EventLogEntryType.Error);
}
return ResultTaskTable;
}
