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); }