Feeds:
Posts
Comments

Archive for January, 2011


Recently i got a question where in a user has asked to get all the resources assignment info, earlier in 2003 it was available in MSP_WEB_WORK, the below query would fetch all the assignments in 2007/2010, Further you customized to accomodate your requirements

SELECT     MSP_EpmResource_UserView.ResourceName, MSP_EpmAssignment_UserView.AssignmentStartDate, MSP_EpmAssignment_UserView.AssignmentFinishDate,
                      MSP_EpmAssignment_UserView.AssignmentActualStartDate, MSP_EpmAssignment_UserView.AssignmentActualFinishDate,
                      MSP_EpmAssignment_UserView.AssignmentActualWork, MSP_EpmAssignment_UserView.AssignmentWork, MSP_EpmProject_UserView.ProjectName,
                      MSP_EpmTask_UserView.TaskName
FROM         MSP_EpmAssignment_UserView INNER JOIN
                      MSP_EpmResource_UserView ON MSP_EpmAssignment_UserView.ResourceUID = MSP_EpmResource_UserView.ResourceUID INNER JOIN
                      MSP_EpmProject_UserView ON MSP_EpmAssignment_UserView.ProjectUID = MSP_EpmProject_UserView.ProjectUID INNER JOIN
                      MSP_EpmTask_UserView ON MSP_EpmAssignment_UserView.TaskUID = MSP_EpmTask_UserView.TaskUID

Read Full Post »


Post addition of new rate row, edit/modify/update any resource property eg. email or any custom property followed by a call to “UpdateResources”, this will make sure the resource is updated

Remember to set the “RES_RATE_EFFECTIVE_DATE”,

Guid resGuid = new Guid(“xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx”);

ResWebSvc.ResourceDataSet ds = res.ReadResource(resGuid);
ds.Resources[0].RES_NAME += string.Empty;
ResWebSvc.ResourceDataSet.ResourceRatesDataTable rates =
ds.ResourceRates;

ResWebSvc.ResourceDataSet.ResourceRatesRow row =
rates.NewResourceRatesRow();
row.RES_RATE_EFFECTIVE_DATE = new DateTime(2009, 07, 25);
row.RES_COST_PER_USE = 0;
row.RES_OVT_RATE = 120;
row.RES_STD_RATE = 80;
row.RES_RATE_TABLE = 2;
row.RES_UID = resGuid;
rates.AddResourceRatesRow(row);

res.CheckOutResources(new Guid[] { resGuid });
res.UpdateResources(ds, false, true);

ds.Resources[0].SetModified();

Note : Remember to add relevant web services to your solution 🙂

Read Full Post »


 Here i am going to give you a solution starter code base, which creates Project proposal (AKA Light Weight Project) along with Queue Status Monitoring and Logging Information :), basically i am using  methods as explained below in the code, Hope this gives you some insight 🙂

I know there is scope for optimization, but i leave that to you 🙂

GetProjectList() – Reads all the Existing Project List From Project Server to compare values, wether proposal already exists or not – purpose > if already exists Skip creation

UpdateStatus()  – Just Used to update the current code execution status in a text box showing what task is being performed currently by the code

WriteLogFile() – used to log the status / error messages / execution to a Physical Log file for processing / tracking purpose

CreateProposal() –  Creates Proposal, add Tasks to proposal and Publishes the proposal

WaitForQueue() –  Queue Watcher, check if queue is not throwing error, or the queue is not processing makes code execution wait till job gets over

GetIDForListItemToUpdate() – This is in addition to to creation, code also includes a status update of completed / cancelled projects to a sharepoint library for reporting purposes, this is to retrieve the ListID for updation of list item

UpdateListItemForCompletedORCanceled() –  Uses List Web Service to do an update to list item

getISOFormattedDateTime() – Since my list item contains a column of type date and sharepoint only accepts update in ISO format hence to convert the date type data

before getting strated read this on how to add service references to ASMX files :: http://msdn.microsoft.com/en-us/library/ms488627.aspx
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Net;
using System.Web;
using System.IO;
using System.Web.Services.Protocols;
using System.Xml;
using PSLibrary = Microsoft.Office.Project.Server.Library;

namespace CreateProjectProposal_Dev
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

const string PROJECT_SERVER_URI = “http://ServerName/PWA/”;
const string PROJECT_SERVICE_PATH = “_vti_bin/psi/project.asmx”;
const string QUEUESYSTEM_SERVICE_PATH = “_vti_bin/psi/queuesystem.asmx”;
const string SESSION_DESC = “Proposal Utility”;
String LogFilePath = “”;
DateTime currentDateTime = DateTime.Now;
//String dateStr = currentDateTime.ToString(“yyyy_MM_dd HH_mm_ss”);
StreamWriter sw;

private void button1_Click(object sender, EventArgs e)
{
#region PROPOSAL CREATION SECTION
this.textBox1.Text = “”;
this.UpdateStatus(” Please Wait While Log File Is Created……”);

//////////////////////////GENERATE LOG FILE FOR EXECUTION ENTRY///////////////////////
DateTime currentDateTime = DateTime.Now;
String dateStr = currentDateTime.ToString(“yyyy_MM_dd HH_mm_ss”);
LogFilePath = “.\\LOG\\” + “CreateProjectProposalLOG_” + dateStr + “.txt”;
StreamWriter sw;
sw = File.CreateText(LogFilePath);
sw.Close();
//////////////////////////////////////////////////////////////////////////////////////
this.UpdateStatus(“Log File Creation Completed “);
//this.UpdateStatus(“Click on Create Proposal Button Above to Proceed With Proposal Creation….”);

////////////////////////////////////////////////CREATING PROPOSAL PROCESS STARTS///////////////////////////////////////

/////////////////////////DECLARE DATASETS AND VARIABLES//////////////////////////////////////////
//String dateStr = currentDateTime.ToString(“yyyy_MM_dd HH_mm_ss”);
DataSet Ds = new DataSet();
ProjectWebSvc.ProjectDataSet PrjDS = new CreateProjectProposal_Dev.ProjectWebSvc.ProjectDataSet();
//////////////////////////////////////////////////////////////////////////////////////////////////

//////////////Get List of Projects From Custom DB For Proposal Creation///////////////////
try
{
this.UpdateStatus(“Connecting to DataBase……”);
string ConnStr = “Data Source=CustomDB;Initial Catalog=ProposalToBeCreated;User Id=UserName;Password=Password”;
SqlConnection Conn = new SqlConnection(ConnStr);
string Query = @”SELECT [ProjectName]
[PlannedEndDate],[ProjectID],[Theme],[PlannedStartDate]
FROM [ProposalCreationTable]

SqlCommand Cmd = new SqlCommand(Query);
Cmd.Connection = Conn;
SqlDataAdapter Da = new SqlDataAdapter(Cmd);
Da.Fill(Ds);
//this.dataGridView1.DataSource = Ds.Tables[0];
this.UpdateStatus(“DB Connection Succeeded………….”);
if (Ds.Tables[0].Rows.Count < 1)
{
this.UpdateStatus(“Zero Rows Returned From DB, None New Proposal to Create………….”);
this.WriteLogFile(“Zero Rows Returned From DB, None New Proposal to Create………….”);
}
this.UpdateStatus(“————————————————————————————–“);
}
catch (Exception ex)
{
this.UpdateStatus(“Problem Connecting to DB,Close Application and Contact Admin……”);
this.WriteLogFile(ex.Message.ToString() + ” ” + dateStr);
}
try
{
///////////////////////////////////////////////////////////////////////////////////////////////
ProjectWebSvc.Project Prj = new CreateProjectProposal_Dev.ProjectWebSvc.Project();
Prj.Credentials = CredentialCache.DefaultCredentials;
PrjDS = Prj.ReadProjectList();
// this.dataGridView2.DataSource = PrjDS.Tables[0];
}
catch (Exception ex)
{
this.WriteLogFile(ex.Message.ToString());
this.UpdateStatus(“Error Connecting To Project Server…… Close Application And Contact Admin”);
}

for (int x = 0; x < Ds.Tables[0].Rows.Count; x++)
{
this.button1.Enabled = false;
bool Flag = false;
for (int y = 0; y < PrjDS.Tables[0].Rows.Count; y++)
{
//this.WriteLogFile(Ds.Tables[0].Rows[x][“FileName”].ToString() + ” == ” + PrjDS.Tables[0].Rows[y][“PROJ_NAME”].ToString());
if (Ds.Tables[0].Rows[x][“FileName”].ToString() == PrjDS.Tables[0].Rows[y][“PROJ_NAME”].ToString())
{ Flag = true; }
}
if (Flag == false)
{
this.UpdateStatus(“—————————————————————————————-“); this.UpdateStatus(“Creating Proposal :: ” + Ds.Tables[0].Rows[x][“FileName”].ToString()); this.CreateProposal(Ds.Tables[0].Rows[x][“FileName”].ToString()); GetProjectList();
}
else
{
this.UpdateStatus(“Proposal Already Exists :: ” + Ds.Tables[0].Rows[x][“FileName”].ToString() + ” — Skipped Creation “); this.WriteLogFile(“Proposal Already Exists” + Ds.Tables[0].Rows[x][“FileName”].ToString() + ” — Skipped Creation ” + ” ” + dateStr);
}
}
this.UpdateStatus(“=======================================================================================”);
this.UpdateStatus(“Proposal Creation Completed, You May Close The Application”);
this.UpdateStatus(“Or Perform Other Operations……….”);
this.UpdateStatus(“=======================================================================================”);
// System.Threading.Thread.Sleep(10000);
//this.Close();
this.button1.Enabled = true;
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
#endregion
}
private void Form1_Load(object sender, EventArgs e)
{
}
static private void WaitForQueue(QueueSystemWebSvc.QueueSystem q, Guid jobId)
{
QueueSystemWebSvc.JobState jobState;
const int QUEUE_WAIT_TIME = 2; // two seconds
bool jobDone = false;
string xmlError = string.Empty;
int wait = 0; //Wait for the project to get through the queue
// – Get the estimated wait time in seconds
wait = q.GetJobWaitTime(jobId);
// – Wait for it
System.Threading.Thread.Sleep(wait * 1000);
// – Wait until it is done.
do
{
// – Get the job state
jobState = q.GetJobCompletionState(jobId, out xmlError);
if (jobState == QueueSystemWebSvc.JobState.Success)
{ jobDone = true; }
else
{
if (jobState == QueueSystemWebSvc.JobState.Unknown || jobState == QueueSystemWebSvc.JobState.Failed || jobState == QueueSystemWebSvc.JobState.FailedNotBlocking || jobState == QueueSystemWebSvc.JobState.CorrelationBlocked || jobState == QueueSystemWebSvc.JobState.Canceled)
{
// If the job failed, error out
throw (new ApplicationException(“Queue request failed \”” + jobState + “\” Job ID: ” + jobId + “.\r\n” + xmlError));
}
else
{
//CreateProjectProposal_Dev.Form1WriteLogFile(“Job State: ” + jobState + ” Job ID: ” + jobId);
System.Threading.Thread.Sleep(QUEUE_WAIT_TIME * 1000);
}
}
}
while (!jobDone);
}

public void CreateProposal( string PrjName)
{
String dateStr = currentDateTime.ToString(“yyyy_MM_dd HH_mm_ss”);
try
{
Guid sessionId = Guid.NewGuid(); Guid jobId;
// Set up the Web service objects
ProjectWebSvc.Project projectSvc = new ProjectWebSvc.Project();
ProjectWebSvc.ProjectDataSet projectDs = new ProjectWebSvc.ProjectDataSet();
projectSvc.Url = PROJECT_SERVER_URI + PROJECT_SERVICE_PATH;
projectSvc.Credentials = CredentialCache.DefaultCredentials;
QueueSystemWebSvc.QueueSystem q = new QueueSystemWebSvc.QueueSystem();
q.Url = PROJECT_SERVER_URI + QUEUESYSTEM_SERVICE_PATH;
q.UseDefaultCredentials = true;
// Create Proposal
this.WriteLogFile(“Creating Proposal data :: ” + PrjName + ” ” + dateStr);
projectDs = new ProjectWebSvc.ProjectDataSet();
// Create the project
ProjectWebSvc.ProjectDataSet.ProjectRow projectRow = projectDs.Project.NewProjectRow();
projectRow.PROJ_UID = Guid.NewGuid();
projectRow.PROJ_NAME = PrjName;
projectRow.PROJ_TYPE = (int)PSLibrary.Project.ProjectType.LightWeightProject; // Change Project Type to Project For creating //project instead of proposal
projectRow. projectDs.Project.AddProjectRow(projectRow);
///////////////////////////////////////ADD TASK AND DURATION TO PROPOSAL//////////////////////////
//// // Adding Tasks
ProjectWebSvc.ProjectDataSet.TaskRow task1 = projectDs.Task.NewTaskRow();
task1.PROJ_UID = projectRow.PROJ_UID;
task1.TASK_UID = Guid.NewGuid();
task1.TASK_DUR_FMT = (int)PSLibrary.Task.DurationFormat.Day;
task1.TASK_DUR = 24000; // 24000 hours in duration units (minute/10)
task1.TASK_NAME = “Assessment”;
task1.TASK_START_DATE = System.DateTime.Now.AddDays(1);
projectDs.Task.AddTaskRow(task1);
ProjectWebSvc.ProjectDataSet.TaskRow task2 = projectDs.Task.NewTaskRow();
task2.PROJ_UID = projectRow.PROJ_UID;
task2.TASK_UID = Guid.NewGuid(); task2.TASK_DUR_FMT = (int)PSLibrary.Task.DurationFormat.Day;
task2.TASK_DUR = 24000; // 24000 hours in duration units (minute/10)
task2.TASK_NAME = “Scope/Size/Roadmap”;
task2.TASK_START_DATE = System.DateTime.Now.AddDays(1);
projectDs.Task.AddTaskRow(task2);
ProjectWebSvc.ProjectDataSet.TaskRow task3 = projectDs.Task.NewTaskRow();
task3.PROJ_UID = projectRow.PROJ_UID; task3.TASK_UID = Guid.NewGuid();
task3.TASK_DUR_FMT = (int)PSLibrary.Task.DurationFormat.Day;
task3.TASK_DUR = 24000; // 24000 hours in duration units (minute/10)
task3.TASK_NAME = “Design/Plan”;
task3.TASK_START_DATE = System.DateTime.Now.AddDays(1);
projectDs.Task.AddTaskRow(task3);
ProjectWebSvc.ProjectDataSet.TaskRow
task4 = projectDs.Task.NewTaskRow();
task4.PROJ_UID = projectRow.PROJ_UID;
task4.TASK_UID = Guid.NewGuid();
task4.TASK_DUR_FMT = (int)PSLibrary.Task.DurationFormat.Day;
task4.TASK_DUR = 24000; // 24000 hours in duration units (minute/10)
task4.TASK_NAME = “Implement”;
task4.TASK_START_DATE = System.DateTime.Now.AddDays(1);
projectDs.Task.AddTaskRow(task4);

//////////////////// // Save the proposal to the database
this.UpdateStatus(“Saving Proposal To The Server and Checking Out”);
this.WriteLogFile(“Saving Proposal To The Server and Checking Out” + ” ” + dateStr);
jobId = Guid.NewGuid(); projectSvc.QueueCreateProposalProjectAndCheckout(jobId, sessionId, SESSION_DESC, projectDs, false, true); WaitForQueue(q, jobId);
// Put your additional changes here
// Check in project so it will be available for more changes
this.UpdateStatus(“Checking in the ProPosal”);
this.WriteLogFile(“Checking in the ProPosal :: ” + PrjName + ” ” + dateStr);
jobId = Guid.NewGuid(); projectSvc.QueueCheckInProject(jobId, projectRow.PROJ_UID, false, sessionId, SESSION_DESC);
this.UpdateStatus(“Checked in the ProPosal Pushed to Queue”);
this.UpdateStatus(“——————————————————————————————–“);
this.WriteLogFile(“Proposal ” + PrjName + ” Checked In Waiting for the queue ” + ” ” + dateStr);
this.WriteLogFile(“===========================================================================================”);
WaitForQueue(q, jobId);
}
catch (Exception ex)
{
this.UpdateStatus(“Error Creating Proposal, Close Application and Contact Admin….”);
this.WriteLogFile(ex.Message.ToString());
Application.Exit();
}
}

private void WriteLogFile(string val)
{
String dateStr = currentDateTime.ToString(“yyyy_MM_dd HH_mm_ss”);
StreamWriter sw;
sw = File.AppendText(LogFilePath);
sw.WriteLine(val + ” ” + dateStr + Environment.NewLine);
sw.Close();
}

private void UpdateStatus(string Stat)
{
this.textBox1.AppendText(Environment.NewLine + Stat);
this.textBox1.ScrollToCaret();
}

private void GetProjectList()
{
ProjectWebSvc.Project Prj = new CreateProjectProposal_Dev.ProjectWebSvc.Project();
ProjectWebSvc.ProjectDataSet PrjDS = new CreateProjectProposal_Dev.ProjectWebSvc.ProjectDataSet();
Prj.Credentials = CredentialCache.DefaultCredentials;
PrjDS = Prj.ReadProjectList();
//this.dataGridView2.DataSource = PrjDS.Tables[0];
}

private void textBox1_TextChanged(object sender, EventArgs e)
{
textBox1.ScrollToCaret();
this.textBox1.SelectionStart = textBox1.Text.Length ;
this.textBox1.Refresh();
}

private void button2_Click(object sender, EventArgs e)
{
#region PROPOSAL COMPLETE / CANCELLED STATUS UPDATION TO SHAREPOINT LIBRARY ///////////////////////////////////////////////////////////////////
this.UpdateStatus(” Please Wait While Log File Is Created……”);

//////////////////////////GENERATE LOG FILE FOR EXECUTION ENTRY///////////////////////
DateTime currentDateTime = DateTime.Now;
String dateStr = currentDateTime.ToString(“yyyy_MM_dd HH_mm_ss”);
LogFilePath = “.\\LOG\\” + “UpdateProjectStatusLOG_” + dateStr + “.txt”;
StreamWriter sw;
//sw = File.CreateText(LogFilePath); //sw.Close(); ///////////////////////////////////////////////////////
/////////////////////////////// this.UpdateStatus(“Log File Creation Completed “);
//this.UpdateStatus(“Click on Update Project Status Button Above to Proceed…..”);
DataSet Ds = new DataSet();
///////GET LIST OF PROJECTS COMPLETED OR CANCELED FROM REPORTING DB/////////////////// //
this.textBox1.Text = “”;
this.UpdateStatus(“Retrieving Completed Project Information……”);
this.WriteLogFile(“Connecting to ProjectServer Reporting DB ” + dateStr);
string ConnStr = “Data Source=ServerName;Initial Catalog=ProjectServer_Reporting;User Id=UserName;Password=Password”;
SqlConnection Conn = new SqlConnection(ConnStr);
string Query = “SELECT MSP_EpmProject_UserView.ProjectName, MSP_EpmProject_UserView.ProjectUID, MSP_EpmProject_UserView.State FROM MSP_EpmProject_UserView WHERE (MSP_EpmProject_UserView.State = ‘Completed’ OR MSP_EpmProject_UserView.State = ‘Canceled’) “;
SqlCommand Cmd = new SqlCommand(Query);
Cmd.Connection = Conn;
SqlDataAdapter Da = new SqlDataAdapter(Cmd);
Da.Fill(Ds);
//this.dataGridView1.DataSource = Ds.Tables[0];
this.UpdateStatus(“Data Retrieved………….”);
this.WriteLogFile(“Connection Succeded to ProjectServer Reporting DB Retrieved Data ” + dateStr);
this.UpdateStatus(“——————————————————————————————————————————————–“);
////////////////////////////////////ITERATE THROUGH DS AND UPDATE THE STATUS IN SHAREPOINT LIBRARY//////////////////////////////////////////
if (Ds.Tables[0].Rows.Count > 0)
{
for (int x = 0; x < Ds.Tables[0].Rows.Count; x++)
{
string ProjectName = Ds.Tables[0].Rows[x][0].ToString();
string ProjectStatus = Ds.Tables[0].Rows[x][2].ToString();
if (Ds.Tables[0].Rows[x][2].ToString() == “Completed”) // IF PROJECT STATE IS COMPLETE
{
this.UpdateStatus(“Updating Project ::” + ProjectName + ” With Status :: Completed …..”);
this.WriteLogFile(“Updating Project ::” + ProjectName + ” With Status :: ” + ProjectStatus + ” ” + dateStr);
this.UpdateListItemForCompletedORCanceled(ProjectName, “Status Completed”); //UPDATE AS SUSTAINABLE IN SHAREPOINT LIBRARY
this.UpdateStatus(“Updating Project Status for Project ::” + ProjectName + ” Done…”);
this.WriteLogFile(“Updating Project Status for Project ::” + ProjectName + ” Done… ” + dateStr);
this.UpdateStatus(“————————————————————————————-“);
this.WriteLogFile(“————————————————————————————-“);
}
if (Ds.Tables[0].Rows[x][2].ToString() == “Cancelled”) // IF PROJECT STATE IS CANCELED
{
this.UpdateStatus(“Updating Project ::” + ProjectName + ” With Status :: Canceled …..”);
this.WriteLogFile(“Updating Project ::” + ProjectName + ” With Status :: ” + ProjectStatus + ” ” + dateStr);
this.UpdateListItemForCompletedORCanceled(ProjectName, “Status Cancelled”); //UPDATE AS CANCELED IN SHAREPOINT LIBRARY
this.UpdateStatus(“Updating Project Status for Project ::” + ProjectName + ” Done…”);
this.WriteLogFile(“Updating Project Status for Project ::” + ProjectName + ” Done… ” + dateStr);
this.UpdateStatus(“————————————————————————————-“);
this.WriteLogFile(“————————————————————————————-“);
}
}
}
else
{
this.UpdateStatus(“No Rows Returned from DB to Update….”);
this.WriteLogFile(“No Rows Returned from DB to Update….”);
}

this.WriteLogFile(” Updation Done………”);
this.WriteLogFile(“========================================================================================”);
this.UpdateStatus(“====================================================================================”);
this.UpdateStatus(“Updation Completed You May Close The Application Now…..”);
this.UpdateStatus(“====================================================================================”);
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
#endregion
}

public int GetIDForListItemToUpdate(string ProjectName, string ProjectStatus)
{
int ID = 0;
try
{
ListWebSvc.Lists ListWebSvc = new CreateProjectProposal_Dev.ListWebSvc.Lists();
//System.Net.NetworkCredential NetCred = CredentialCache.DefaultCredentials;
ListWebSvc.Url = @”http://Servername/PWA1/_vti_bin/Lists.asmx&#8221;;
ListWebSvc.Credentials = CredentialCache.DefaultCredentials;

this.WriteLogFile(“Retrieveing List Item ID for Project ::” + ProjectName);

/*Get Name attribute values (GUIDs) for list and view. */
System.Xml.XmlNode ndListView = ListWebSvc.GetListAndView(“SHAREPOINT Library”, “”);

string strListID = ndListView.ChildNodes[0].Attributes[“Name”].Value;
string strViewID = ndListView.ChildNodes[1].Attributes[“Name”].Value;
string rowLimit = “3”;
XmlDocument xmlDoc = new XmlDocument();
XmlElement query = xmlDoc.CreateElement(“Query”);
XmlElement viewFields = xmlDoc.CreateElement(“ViewFields”);
XmlElement queryOptions = xmlDoc.CreateElement(“QueryOptions”);

string ProjectStatusVal = “”;
query.InnerXml = “” + ProjectName.Trim() + “”;
queryOptions.InnerXml = “”;
XmlNode nodeListItems = ListWebSvc.GetListItems(strListID, strViewID, query, viewFields, rowLimit, queryOptions, null);

if (nodeListItems.ChildNodes.Count > 0)
{
ID = Convert.ToInt16(nodeListItems.ChildNodes[1].ChildNodes[1].Attributes[“ows_ID”].Value);
ProjectStatusVal = nodeListItems.ChildNodes[1].ChildNodes[1].Attributes[“ows_OverallStatus”].Value;
this.UpdateStatus(“List Item ID Found :: ” + ID + ” Current Status :: ” + ProjectStatusVal);
this.WriteLogFile(“List Item ID Found :: ” + ID + ” Current Status :: ” + ProjectStatusVal);
if (ProjectStatusVal == ProjectStatus)
{ return 0; }
}
}
catch (Exception ex)
{
this.WriteLogFile(ex.Message.ToString());
Application.Exit();
}
return ID;
}

public void UpdateListItemForCompletedORCanceled(string ProjectName , string ProjectStatus)
{
///////////////////////////////////FIND LIST ITEM USING TO BE UPDATED///////////////////////////
try
{
//this.UpdateStatus(“Logging Onto Sharepoint Site………….”);
this.WriteLogFile(“Logging Onto SHAREPOINT Site to Update Status for Project :: “);
ListWebSvc.Lists ListWebSvc = new CreateProjectProposal_Dev.ListWebSvc.Lists();
ListWebSvc.Url = @”http://ServerName/PWA1/_vti_bin/Lists.asmx&#8221;;
ListWebSvc.Credentials = CredentialCache.DefaultCredentials;
this.UpdateStatus(“Successfully Logged Onto Site………….”);
/*Get Name attribute values (GUIDs) for list and view. */
System.Xml.XmlNode ndListView = ListWebSvc.GetListAndView(“SHAREPOINT Library”, “”);

string strListID = ndListView.ChildNodes[0].Attributes[“Name”].Value;
string strViewID = ndListView.ChildNodes[1].Attributes[“Name”].Value;
this.UpdateStatus(“Retrieving List Item To Update……….”);
int ListItemID = this.GetIDForListItemToUpdate(ProjectName, ProjectStatus);
if (ListItemID != 0 )
{
/*Create an XmlDocument object and construct a Batch element and its attributes. Note that an empty ViewName parameter causes the method to use the default view. */
System.Xml.XmlDocument doc = new System.Xml.XmlDocument();
System.Xml.XmlElement batchElement = doc.CreateElement(“Batch”);

switch(ProjectStatus)
{

case ” Status Sustainable”:

string CompletionDate = getISOFormattedDateTime();
batchElement.SetAttribute(“OnError”, “Continue”);
batchElement.SetAttribute(“ListVersion”, “1”);
batchElement.SetAttribute(“ViewName”, strViewID);
batchElement.InnerXml = ” ” +
” ” + ListItemID + ” ” +
” ” + ProjectName + ” ” +
” ” + CompletionDate + ” ” +
” ” + ProjectStatus + ” ” +
” “;

try
{
this.UpdateStatus(“Updating List Item For ID…. ” + ListItemID);
this.WriteLogFile(“Updation Started in List “);
ListWebSvc.UpdateListItems(strListID, batchElement);
//this.UpdateStatus(“Updation of \”Sustainable\” Done….”);
this.WriteLogFile(“Updation Done For List Item “);

}
catch (Exception ex)
{
//MessageBox.Show(ex.Message);
this.WriteLogFile(ex.Message.ToString() + ” ” + System.DateTime.Now.ToString());
this.UpdateStatus(” List Item Could Not be Update, Error Encountered, Contact Admin…..”);
Application.Exit();
}
break;
case “Status Canceled”:

string CancelationDate = getISOFormattedDateTime();
batchElement.SetAttribute(“OnError”, “Continue”);
batchElement.SetAttribute(“ListVersion”, “1”);
batchElement.SetAttribute(“ViewName”, strViewID);
batchElement.InnerXml = ” ” +
” ” + ListItemID + ” ” +
” ” + ProjectName + ” ” +
” ” + CancelationDate + ” ” +
” ” + ProjectStatus + ” ” +
” “;

try
{
this.UpdateStatus(“Updating List Item For ID…. ” + ListItemID);
this.WriteLogFile(“Updation Started in List “);
ListWebSvc.UpdateListItems(strListID, batchElement);
//this.UpdateStatus(“Updation of \”Sustainable\” Done….”);
this.WriteLogFile(“Updation Done For List Item “);

}
catch (Exception ex)
{
//MessageBox.Show(ex.Message);
this.WriteLogFile(ex.Message.ToString() + ” ” + System.DateTime.Now.ToString());
this.UpdateStatus(” List Item Could Not be Update, Error Encountered, Contact Admin…..”);
Application.Exit();
}
break;
}

}

else
{
this.UpdateStatus(“Skipped Project ::” + ProjectName + ” …… As Status Is Already Set to :: ” + ProjectStatus);
this.WriteLogFile(“Skipped Project ::” + ProjectName + ” As Status Is Already Set to :: ” + ProjectStatus);
}
}
catch (System.Web.Services.Protocols.SoapException ex)
{
this.WriteLogFile(ex.Message.ToString() + ” ” + System.DateTime.Now.ToString());
this.UpdateStatus(” List Item Could Not be Update, Error Encountered, Contact Admin…..”);
Application.Exit();
}

////////////////////////////////////////////////////////////////////////////////////////////////

}

public string getISOFormattedDateTime()
{
DateTime CurrentDateTime = new DateTime();
CurrentDateTime = System.DateTime.Now;
//Generate ISO 8601 date/time formatted string
string FormattedDate = “”;
FormattedDate += CurrentDateTime.Year + “-“;
FormattedDate += CurrentDateTime.Month + “-“;
FormattedDate += CurrentDateTime.Day;
FormattedDate += “T” + CurrentDateTime.Hour + “:”;
FormattedDate += CurrentDateTime.Minute + “:”;
FormattedDate += CurrentDateTime.Second + “-06:00”; //time zone offset (eg “+10:00” – Australian EST)
return FormattedDate;
}
}
}

Read Full Post »


Available on Online version Only, Download still has Sep 2010 Update,

Most awaited – Modifying PWA Ribbon 🙂 , using ProjTool Test Application, Cache Synchronization 

Major updates for PSI samples

  • Security – Create Project category, Read Project category,Update Project category
  • Resource Plan – Queue Create Resource Plan (I Was waiting for this eagerly), Queue Publish Resource Plan, Queue Update Resource Plan, Read Resource Plan
  • Object Link provider – Create Web Object Links, Delete Web object Links,Read Task linked Web Objects 

http://msdn.microsoft.com/en-us/library/ms512767.aspx#pj14_Welcome_Download

Read Full Post »

Find Corrupt Assignments


Many a times we experience errors in saving project plan, project not opening from server, just to ease your work and keep a tab on project server assignemnts getting corrupted here is the query to find out the same, enjoy 🙂

SELECT

MSP_PROJECTS.PROJ_NAME,
MSP_ASSIGNMENTS_SAVED.TASK_NAME,
MSP_RESOURCES.RES_NAME,
MSP_ASSIGNMENTS_SAVED.ASSN_UID,
MSP_ASSIGNMENTS_SAVED.PROJ_UID,
MSP_ASSIGNMENTS_SAVED.TASK_UID,
MSP_ASSIGNMENTS_SAVED.RES_UID
FROM MSP_ASSIGNMENTS_SAVED INNER JOIN MSP_PROJECTS ON MSP_ASSIGNMENTS_SAVED.PROJ_UID = MSP_PROJECTS.PROJ_UID INNER JOIN MSP_RESOURCES ON MSP_ASSIGNMENTS_SAVED.RES_UID = MSP_RESOURCES.RES_UID
WHERE (NOT EXISTS(SELECT TASK_UID FROM MSP_TASKS_SAVED WHERE (TASK_UID = MSP_ASSIGNMENTS_SAVED.TASK_UID)))

Read Full Post »


Many a times we donot want to enable the e-mail notification in project server reason being it starts bombarding mails to users inbox and after sometime users get resistant to those mails and don’t even look at those :), so you would want to send emails just for specific purpose, for eg. like sending timesheet status alert so here is one solution, have look

CREATE PROCEDURE [dbo]. [SendMail] AS
BEGIN
DECLARE @to VARCHAR ( 70)
DECLARE @recipient_name VARCHAR ( 50) 
DECLARE @weekdate VARCHAR ( 11) 
DECLARE getUserDetail CURSOR
FOR
SELECT resourceemailaddress, r. resourcename, CONVERT ( VARCHAR ( 11), enddate) enddate

FROM  MSP_TimesheetPeriod_OlapView TP inner join MSP_Timesheet_OlapView T ON TP. PeriodUID  = T. PeriodUID inner join MSP_TimesheetResource_OlapView RT ON T. OwnerResourceNameUID = RT. ResourceNameUID inner join MSP_Epmresource_UserView R on RT. ResourceUID = R. ResourceUID
WHERE TimeSheetStatusId = 0 and   Enddate < GETDATE()
OPEN getUserDetail
FETCH NEXT FROM getUserDetail INTO @to, @recipient_name, @weekdate
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC  msdb. dbo. sp_send_dbmail @recipients = @to, @subject = ‘Timesheet Alert’ , @body = ‘You have not submitted your Timesheet for last week.
Warm Regards PS Admin
 , 
@body_format = ‘HTML’ 
FETCH NEXT FROM getUserDetail
INTO @to
END
CLOSE getUserDetail
DEALLOCATE getUserDetail
END


Read Full Post »


Find all the relevant definitions of MS Project, containing over 400 keyword definitions that relate Microsoft Project and Project Management, is an excerpt from Project Companion—a free download from Project Learning Limited. http://technet.microsoft.com/en-us/library/cc768107.aspx

Read Full Post »

Older Posts »