public List <PrList> GetBugPrList(string strQueryLnk)
{
var PRLists = new List <PrList>();
string strCaption = "CRM :: Pull Request Status";
//var collectionUri = "https://dynamicscrm.visualstudio.com";
var conur = "http://vstfmbs:8080/tfs/CRM";
var teamProjectName = "Engineering";
var strError = "";
VssConnection conn = new VssConnection(new Uri(conur), new VssCredentials());
WorkItemTrackingHttpClient witClient = conn.GetClient <WorkItemTrackingHttpClient>();
List <QueryHierarchyItem> queryHierarchyItems = witClient.GetQueriesAsync(teamProjectName, depth: 1).Result;
try
{
string temp = System.Net.WebUtility.UrlDecode(strQueryLnk);
string queryFullPath = Regex.Split(temp, "#path=", RegexOptions.IgnoreCase)[1]; //strQueryURL.Split(new string[] { "#path=" }, StringSplitOptions.None)[1];
queryFullPath = queryFullPath.Substring(0, queryFullPath.ToLower().IndexOf("&_a=query"));
string[] queryArr = Regex.Split(queryFullPath, "/", RegexOptions.IgnoreCase);
string rootFolderName = queryArr[0];
string strQueryName = queryArr[queryArr.Length - 1];
int qFolderCount = queryArr.Length;
QueryHierarchyItem myQueriesFolder = queryHierarchyItems.FirstOrDefault(qhi => qhi.Name.Equals(rootFolderName));
if (myQueriesFolder != null)
{
QueryHierarchyItem query = witClient.GetQueryAsync(teamProjectName, queryFullPath).Result;
WorkItemQueryResult result = witClient.QueryByIdAsync(query.Id).Result;
VssConnection connection2 = new VssConnection(new Uri($"https://dynamicscrm.visualstudio.com"), new VssAadCredential());
var apiStatus = new GetStatusAPI(connection2);
if (result.WorkItems.Any())
{
int skip = 0;
const int batchSize = 100;
string status = "";
IEnumerable <WorkItemReference> workItemRefs;
do
{
workItemRefs = result.WorkItems.Skip(skip).Take(batchSize);
if (workItemRefs.Any())
{
List <WorkItem> workItems = witClient.GetWorkItemsAsync(workItemRefs.Select(wir => wir.Id)).Result;
addlog("Getting PR Status.....");
addlog("Bug ID# PR Number# PR Status");
for (int i = 0; i < workItems.Count(); i++)
{
PrList prList = new PrList();
prList.BugId = workItems[i].Id.ToString();
if (workItems[i].Fields.ContainsKey("Microsoft.CRM.PRLink"))
{
if (workItems[i].Fields["Microsoft.CRM.PRLink"].ToString() != "" || workItems[i].Fields["Microsoft.CRM.PRLink"].ToString() != "NA")
{
prList.PRLink = workItems[i].Fields["Microsoft.CRM.PRLink"].ToString();
prList.PRStatus = getStatus(prList.PRLink.ToString(), apiStatus).Result;
prList.PRNum = PRNum.ToString();
if (prList.PRNum == "0")
{
addlog(prList.BugId + " " + "Invalid" + " " + prList.PRStatus);
}
else
{
addlog(prList.BugId + " " + prList.PRNum + " " + prList.PRStatus);
}
}
else
{
prList.PRLink = "";
addlog(prList.BugId + " " + "Invalid" + " " + prList.PRStatus);
}
}
else
{
prList.PRLink = "";
addlog(prList.BugId + " " + "Invalid" + " " + prList.PRStatus);
}
PRLists.Add(prList);
}
}
skip += batchSize;
}while (workItemRefs.Count() == batchSize);
}
else
{
MessageBox.Show("No work items were returned from query.", strCaption, MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return(PRLists);
}
}
else
{
MessageBox.Show("The folder does not contain any query.", strCaption, MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return(PRLists);
}
}
catch (Exception ex)
{
strError = ex.Message;
clsStatus.WriteLog("GetBugPrList()-->" + ex.Message);
}
return(PRLists);
}
public async Task <string> getStatus(string prLink, GetStatusAPI statusApi)
{
string strStat = string.Empty;
string[] strPRArr = null;
if (prLink != null && prLink != "" && prLink != "NA" && (prLink.ToString().ToLower().Contains("pullrequest")))
{
strPRArr = System.Text.RegularExpressions.Regex.Split(prLink.ToLower(), "pullrequest/");
if (strPRArr.Length >= 2)
{
string strPRLink = strPRArr[0];
string strPRID = strPRArr[1];
ArrayList splChars = new ArrayList();
splChars.Add("!"); splChars.Add("@"); splChars.Add("$");
splChars.Add("&"); splChars.Add("("); splChars.Add(")");
splChars.Add("?"); splChars.Add("#"); splChars.Add("^");
splChars.Add("%"); splChars.Add("/"); splChars.Add("*");
foreach (string chr in splChars)
{
if (strPRID.Contains(chr))
{
strPRID = strPRID.Substring(0, strPRID.IndexOf(chr));
}
}
if (strPRID == null || strPRID == "")
{
PRNum = 0;
strStat = "Invalid PRLink";
}
else
{
try
{
PRNum = Convert.ToInt32(strPRID);
var results1 = statusApi.GetPrStatus(PRNum).GetAwaiter().GetResult();
strStat = results1[0].ToString();
}
catch (Exception ex)
{
PRNum = 0;
strStat = ex.Message;
clsStatus.WriteLog("getStatus()-->" + ex.Message);
if (strStat == "Input string was not in a correct format.")
{
strStat = "Invalid PR NUmber";
}
}
}
}
else
{
PRNum = 0;
strStat = "Invalid PRLink";
}
}
else
{
PRNum = 0;
strStat = "Invalid PRLink";
}
return(strStat);
}
public void SaveInExcel(string fpath, List <PrList> prList)
{
try
{
if (fpath == "NoPath")
{
addlog("Completed......");
}
else
{
Excel.Application xlApp = new Excel.Application();
Excel.Workbook workbook = xlApp.Workbooks.Add(1);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Sheets[1];
Excel.Range worksheet_range = null;
if (closeOpenedExcel(fpath))
{
System.Threading.Thread.Sleep(2000);
}
if (File.Exists(fpath))
{
File.Delete(fpath);
}
workbook.SaveAs(fpath, Excel.XlFileFormat.xlOpenXMLWorkbook, Type.Missing, Type.Missing, false, false, XlSaveAsAccessMode.xlNoChange, XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing);
string cellName;
int counter = 1;
cellName = "A" + counter.ToString();
worksheet.get_Range(cellName, cellName).Value2 = "BugID";
cellName = "B" + counter.ToString();
worksheet.get_Range(cellName, cellName).Value2 = "PR Number";
cellName = "C" + counter.ToString();
worksheet.get_Range(cellName, cellName).Value2 = "PR Status";
counter = counter + 1;
string status = "";
VssConnection connection2 = new VssConnection(new Uri($"https://dynamicscrm.visualstudio.com"), new VssAadCredential());
var comparertest = new GetStatusAPI(connection2);
foreach (var item in prList)
{
string PrDet = "";
cellName = "A" + counter.ToString();
var range1 = worksheet.get_Range(cellName, cellName);
range1.Value2 = item.BugId.ToString();
cellName = "B" + counter.ToString();
worksheet.get_Range(cellName, cellName).Value2 = item.PRLink.ToString();
status = item.PRStatus;
cellName = "C" + counter.ToString();
worksheet.get_Range(cellName, cellName).Value2 = status;
workbook.Save();
++counter;
}
workbook.Close();
addlog("Status file has been saved in file path : " + fpath);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public string getStatusExcel(string fPath)
{
string strval = validation(fPath);
try
{
if (strval == "true")
{
addlog("Getting PR status....");
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@fPath);
Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Excel.Range xlRange = xlWorksheet.UsedRange;
VssConnection connection2 = new VssConnection(new Uri($"https://dynamicscrm.visualstudio.com"), new VssAadCredential());
var statsAPI = new GetStatusAPI(connection2);
int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;
int cCnt = 1;
int csCnt = 2;
if (rowCount > 1)
{
for (int rCnt = 1; rCnt <= rowCount; rCnt++)
{
if (rCnt == 1)
{
addlog("PR Number# PR Status");
}
else
{
if (xlRange.Cells[rCnt, cCnt] != null && xlRange.Cells[rCnt, cCnt].Value2 != null)
{
string PrLink = xlRange.Cells[rCnt, cCnt].Value2.ToString();
strStatus = getStatus(PrLink.ToString(), statsAPI).Result.ToString();
xlWorksheet.Cells[rCnt, csCnt] = strStatus;
addlog(PRNum + " " + strStatus);
xlWorkbook.Save();
}
else
{
strStatus = "Invalid PRLink";
xlWorksheet.Cells[rCnt, csCnt] = strStatus;
xlWorkbook.Save();
addlog(strStatus);
}
}
}
xlWorkbook.Close();
strStatus = "The file in the same file path has been updated successfully";
}
else
{
xlWorkbook.Close();
strStatus = "No Data found in the given file";
}
}
else
{
addlog(strval);
}
}
catch (Exception ex)
{
strStatus = ex.Message;
clsStatus.WriteLog("getStatusExcel()-->" + ex.Message);
}
return(strStatus);
}
