Preflight Excel Generator

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Collections;
using System.Reflection;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;
using System.Net;
using System.Text.RegularExpressions;
namespace PreflightExcelGenerator
{
    class Program
    {
        static void Main(string[] args)
        {
            //Read input from the text file
            StreamReader sr = new StreamReader(@"C:\PreFlight\input.txt");
            ArrayList inputUrl = new ArrayList();
            for (string str = sr.ReadLine(); str != null; str = sr.ReadLine())
            {
                inputUrl.Add(str);
            }
            sr.Close();
            //Create an excel work book
            string fileName = removeChars(DateTime.Now.ToString(), @": /");
            fileName = @"C:\Preflight\" + fileName + ".xls";
            Console.WriteLine(fileName);
            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            object misValue = System.Reflection.Missing.Value;
            xlApp = new Excel.Application();
            xlWorkBook = xlApp.Workbooks.Add(misValue);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
            xlApp.Visible = true;
            int rowIndex = 1; int colIndex = 1;
            xlWorkSheet.Cells[misValue, 1].ColumnWidth = 30;
            xlWorkSheet.Cells[misValue, 2].ColumnWidth = 40;
            xlWorkSheet.Cells[misValue, 3].ColumnWidth = 15;
            xlWorkSheet.Cells[misValue, 4].ColumnWidth = 35;
            xlWorkSheet.Cells[misValue, 5].ColumnWidth = 60;
            xlWorkSheet.Cells[rowIndex, colIndex] = "Test Suite";
            xlWorkSheet.Cells[rowIndex, colIndex].Font.Bold = true;
            colIndex++;
            xlWorkSheet.Cells[rowIndex, colIndex] = "Test Case";
            xlWorkSheet.Cells[rowIndex, colIndex].Font.Bold = true;
            colIndex++;
            xlWorkSheet.Cells[rowIndex, colIndex] = "Current Fail";
            xlWorkSheet.Cells[rowIndex, colIndex].Font.Bold = true;
            colIndex++;
            xlWorkSheet.Cells[rowIndex, colIndex] = "Last Pass";
            xlWorkSheet.Cells[rowIndex, colIndex].Font.Bold = true;
            colIndex++;
            xlWorkSheet.Cells[rowIndex, colIndex] = @"Investigations/Analysis/JIRA";
            xlWorkSheet.Cells[rowIndex, colIndex].Font.Bold = true;
            for (int i = 0; i < inputUrl.Count; i++)
            {
                if (inputUrl[i].ToString().Contains(@"testRunId="))
                {
                    //Test Run (Collection of Test Suites)
                    List<LinkItem> testSuites = getSuiteDetailsFromRunLink(inputUrl[i].ToString());
                    for (int j = 0; j < testSuites.Count; j++)
                    {
                        rowIndex++;
                        colIndex = 1;
                        xlWorkSheet.Cells[rowIndex, colIndex] = testSuites[j].Text;
                        string suiteName = "";
                        string locale = "";
                        string domain = "";
                        List<LinkItem> testCases = getCaseDetailsFromSuiteLink("http://prime-test-31001.sea31.amazon.com" + testSuites[j].Href, out suiteName, out locale, out domain);
                        for (int k = 0; k < testCases.Count; k++)
                        {
                            colIndex = 2;
                            xlWorkSheet.Cells[rowIndex, colIndex] = testCases[k].Text;
                            colIndex = 3;
                            xlWorkSheet.Hyperlinks.Add(xlWorkSheet.Cells[rowIndex, colIndex], "http://prime-test-31001.sea31.amazon.com" + testCases[k].Href, misValue, "http://prime-test-31001.sea31.amazon.com" + testCases[k].Href, "Link");
                            colIndex = 4;
                            string tempStr = getLastPassLink(testCases[k].Text, locale, domain);
                            if (tempStr.Contains(@"Test case didn't pass in last"))
                            {
                                xlWorkSheet.Cells[rowIndex, colIndex] = tempStr;
                            }
                            else
                            {
                                xlWorkSheet.Hyperlinks.Add(xlWorkSheet.Cells[rowIndex, colIndex], tempStr, misValue, tempStr, "Link");
                            }
                            rowIndex++;
                        }
                    }
                }
                else if (inputUrl[i].ToString().Contains(@"testRunSuiteId="))
                {
                    //Test Suite (Collection of Test Cases)
                    rowIndex++;
                    string suiteName = "";
                    string locale = "";
                    string domain = "";
                    List<LinkItem> testCases = getCaseDetailsFromSuiteLink(inputUrl[i].ToString(), out suiteName, out locale, out domain);
                    for (int k = 0; k < testCases.Count; k++)
                    {
                        if (k == 0)
                        {
                            colIndex = 1;
                            xlWorkSheet.Cells[rowIndex, colIndex] = suiteName;
                        }
                        colIndex = 2;
                        xlWorkSheet.Cells[rowIndex, colIndex] = testCases[k].Text;
                        colIndex = 3;
                        xlWorkSheet.Hyperlinks.Add(xlWorkSheet.Cells[rowIndex, colIndex], "http://prime-test-31001.sea31.amazon.com" + testCases[k].Href, misValue, "http://prime-test-31001.sea31.amazon.com" + testCases[k].Href, "Link");
                        colIndex = 4;
                        string tempStr = getLastPassLink(testCases[k].Text, locale, domain);
                        if (tempStr.Contains(@"Test case didn't pass in last"))
                        {
                            xlWorkSheet.Cells[rowIndex, colIndex] = tempStr;
                        }
                        else
                        {
                            xlWorkSheet.Hyperlinks.Add(xlWorkSheet.Cells[rowIndex, colIndex], tempStr, misValue, tempStr, "Link");
                        }
                        rowIndex++;
                    }
                }
                else
                {
                    //Test Case
                    rowIndex++;
                    colIndex = 1;
                    TestCaseDetails tcd = new TestCaseDetails();
                    tcd = getTestCaseDetailsFromCaseLink(inputUrl[i].ToString());
                    xlWorkSheet.Cells[rowIndex, colIndex] = tcd.TestSuiteName;
                    colIndex = 2;
                    xlWorkSheet.Cells[rowIndex, colIndex] = tcd.TestCaseName;
                    colIndex = 3;
                    xlWorkSheet.Hyperlinks.Add(xlWorkSheet.Cells[rowIndex, colIndex], inputUrl[i].ToString(), misValue, inputUrl[i].ToString(), "Link");
                    colIndex = 4;
                    string tempStr = getLastPassLink(tcd.TestCaseName, tcd.Locale, tcd.Domain);
                    if (tempStr.Contains(@"Test case didn't pass in last"))
                    {
                        xlWorkSheet.Cells[rowIndex, colIndex] = tempStr;
                    }
                    else
                    {
                        xlWorkSheet.Hyperlinks.Add(xlWorkSheet.Cells[rowIndex, colIndex], tempStr, misValue, tempStr, "Link");
                    }
                    rowIndex++;
                }
            }
            //Saving the file           
            xlWorkBook.SaveAs(fileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
            xlWorkBook.Close(true, misValue, misValue);
            xlApp.Quit();
            releaseObject(xlWorkBook);
            releaseObject(xlApp);
        }
        public static List<LinkItem> getCaseDetailsFromSuiteLink(string testSuiteUrl, out string suiteName, out string locale, out string domain)
        {
            //Url should end with testRunSuiteId=xxxxxxx
            List<LinkItem> list = new List<LinkItem>();
            string suiteResultPageSourceValidEndIdentifierSingleCase = @"Test Case Output";
            string suiteResultPageSourceValidStartIdentifier = @"<td colspan=2 class=""testcase""";
            string suiteResultPageSourceValidEndIdentifierMultipleCase = @"Created by <a href=""mailto:prime-qa@amazon.com"">Prime QA";
            string suiteResultPageSource = getPageSource(testSuiteUrl);
            MatchCollection pageHeader = Regex.Matches(suiteResultPageSource, @"(<h1>.*?</h1>)",
                            RegexOptions.Singleline);
            string header = pageHeader[0].Groups[1].Value;
            locale = getLocaleFromHeader(header);
            domain = getDomainFromHeader(header);
            header = header.Substring(header.IndexOf("Test Suite"), header.IndexOf(" - ") - header.IndexOf("Test Suite"));
            header = header.Replace(@"\n", " ");
            header = header.Trim();
            Regex reg = new Regex(" ");
            string[] strArray = reg.Split(header);
            suiteName = strArray[strArray.Length - 1];
            if (suiteResultPageSource.Contains(suiteResultPageSourceValidEndIdentifierSingleCase)) //for test suites with single test case failure/error
            {
                suiteResultPageSource = suiteResultPageSource.Substring(suiteResultPageSource.IndexOf(suiteResultPageSourceValidStartIdentifier), suiteResultPageSource.IndexOf(suiteResultPageSourceValidEndIdentifierSingleCase) - suiteResultPageSource.IndexOf(suiteResultPageSourceValidStartIdentifier));
            }
            else //for test suites with multiple test case failures/error
            {
                suiteResultPageSource = suiteResultPageSource.Substring(suiteResultPageSource.IndexOf(suiteResultPageSourceValidStartIdentifier), suiteResultPageSource.IndexOf(suiteResultPageSourceValidEndIdentifierMultipleCase) - suiteResultPageSource.IndexOf(suiteResultPageSourceValidStartIdentifier));
            }
            foreach (LinkItem j in LinkFinder.Find(suiteResultPageSource))
            {
                list.Add(j);
            }
            return list;
        }
        public static List<LinkItem> getSuiteDetailsFromRunLink(string testRunUrl)
        {
            //Url should end with testRunId=xxxxxxx
            List<LinkItem> list = new List<LinkItem>();
            string testRunPageSource = getPageSource(testRunUrl);
            string testRunPageSourceValidStartIdentifier = @"Options/Exec Time";
            string testRunPageSourceValidEndIdentifier = @"Created by <a href=""mailto:prime-qa@amazon.com"">Prime QA";
            testRunPageSource = testRunPageSource.Substring(testRunPageSource.IndexOf(testRunPageSourceValidStartIdentifier), testRunPageSource.IndexOf(testRunPageSourceValidEndIdentifier) - testRunPageSource.IndexOf(testRunPageSourceValidStartIdentifier));
            foreach (LinkItem i in LinkFinder.Find(testRunPageSource))
            {
                if (i.Href.Contains(@"testRunSuiteId=") && !i.Href.Contains(@"&scope="))
                {
                    list.Add(i);
                }
            }
            return list;
        }
        public static string getLastPassLink(string testCaseName, string locale, string domain)
        {
            int numberOfLastExecutionsToCheck = 500;
            string pHubSiteUrl = @"https://phub.amazon.com/sste/cases/";
            string testCaseUrl = pHubSiteUrl + testCaseName + "?locale=" + locale + "&executions_back=" + numberOfLastExecutionsToCheck.ToString();
            string testResultPageSource = getPageSource(testCaseUrl);
            string testResultPageSourceValidStartIdentifier = @"Exec. time";
            string testResultPageSourceValidEndIdentifier = @"</table>";
            testResultPageSource = testResultPageSource.Substring(testResultPageSource.IndexOf(testResultPageSourceValidStartIdentifier), testResultPageSource.IndexOf(testResultPageSourceValidEndIdentifier) - testResultPageSource.IndexOf(testResultPageSourceValidStartIdentifier));
            string testResultPassStartIdentifier = @"<tr valign=""top"" class=""pass"">";
            string testResultPassEndIdentifier = @"</tr>";
            string prodRunText = "Prod";
            string preProdRunText = "PreProd";
            string resultLinkIdentifier = "testRunCaseId";
            string tempStr = "";
            int tempStart = 0;
            int tempEnd = 0;
            do
            {
                tempStart = testResultPageSource.IndexOf(testResultPassStartIdentifier, tempStart);
                if (tempStart != -1)
                {
                    tempEnd = testResultPageSource.IndexOf(testResultPassEndIdentifier, tempStart);
                }
                else
                {
                    tempEnd = -1;
                }
                if (tempStart != -1 && tempEnd != -1 && tempStart < tempEnd)
                {
                    string resultRow = testResultPageSource.Substring(tempStart, tempEnd - tempStart);
                    if (domain == "Devo")
                    {
                        if (!resultRow.Contains(prodRunText))
                        {
                            foreach (LinkItem k in LinkFinder.Find(resultRow))
                            {
                                if (k.Href.Contains(resultLinkIdentifier))
                                {
                                    tempStr = k.Href;
                                    tempStart = -1;
                                }
                            }
                            break;
                        }
                    }
                    else if (domain == preProdRunText)
                    {
                        if (resultRow.Contains(preProdRunText))
                        {
                            foreach (LinkItem k in LinkFinder.Find(resultRow))
                            {
                                if (k.Href.Contains(resultLinkIdentifier))
                                {
                                    tempStr = k.Href;
                                    tempStart = -1;
                                }
                            }
                            break;
                        }
                    }
                    else
                    {
                        if (resultRow.Contains(prodRunText) && !resultRow.Contains(preProdRunText))
                        {
                            foreach (LinkItem k in LinkFinder.Find(resultRow))
                            {
                                if (k.Href.Contains(resultLinkIdentifier))
                                {
                                    tempStr = k.Href;
                                    tempStart = -1;
                                }
                            }
                            break;
                        }
                    }
                }
                tempStart = tempEnd + 1;
            } while (tempStart != 0);
            if (tempStart == -1) //Last successful pass exists
            {
                return tempStr;
            }
            else
            {
                tempStr = "Test case didn't pass in last " + numberOfLastExecutionsToCheck.ToString() + " executions.";
                return tempStr;
            }
        }
        public static string removeChars(string str, string remove)
        {
            string tempStr = "";
            for (int i = 0; i < str.Length; i++)
            {
                if (!remove.Contains(str.Substring(i, 1)))
                {
                    tempStr = tempStr + str.Substring(i, 1);
                }
            }
            return tempStr;
        }
        public static void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                Console.WriteLine("Exception Occured while releasing object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }
        }
        public static string getPageSource(string URL)
        {
            System.Net.WebClient webClient = new System.Net.WebClient();
            webClient.Credentials = CredentialCache.DefaultCredentials;
            webClient.UseDefaultCredentials = true;
            string strSource = webClient.DownloadString(URL);
            webClient.Dispose();
            return strSource;
        }
        public struct LinkItem
        {
            public string Href;
            public string Text;
        }
        public struct TestCaseDetails
        {
            public string TestSuiteName;
            public string TestCaseName;
            public string Locale;
            public string Domain;
        }
        public static TestCaseDetails getTestCaseDetailsFromCaseLink(string testCaseUrl)
        {
            //Url should end with testRunCaseId=xxxxxxx
            TestCaseDetails tcd = new TestCaseDetails();
            string testCasePageSource = getPageSource(testCaseUrl);
            MatchCollection pageHeader = Regex.Matches(testCasePageSource, @"(<h1>.*?</h1>)",
                            RegexOptions.Singleline);
            string header = pageHeader[0].Groups[1].Value;
            tcd.Locale = getLocaleFromHeader(header);
            tcd.Domain = getDomainFromHeader(header);
            //Extracting Test Case Name
            Regex reg = new Regex(@"&raquo;");
            string[] strArray = reg.Split(header);
            strArray[strArray.Length - 1] = strArray[strArray.Length - 1].Replace(@"\n", "");
            strArray[strArray.Length - 1] = strArray[strArray.Length - 1].Trim();
            tcd.TestCaseName = strArray[strArray.Length - 1].Substring(0, strArray[strArray.Length - 1].IndexOf(" "));
            //Extracting Test Suite Name
            List<LinkItem> list = new List<LinkItem>();
            list = LinkFinder.Find(header);
            reg = new Regex(" ");
            strArray = reg.Split(list[list.Count - 1].Text);
            tcd.TestSuiteName = strArray[strArray.Length - 1];
            return tcd;
        }
        public static string getLocaleFromHeader(string header)
        {
            string locale = "";
            //Check for locale
            if (header.Contains("US"))
            {
                locale = "US";
            }
            else if (header.Contains("UK"))
            {
                locale = "UK";
            }
            else if (header.Contains("JP"))
            {
                locale = "JP";
            }
            else if (header.Contains("FR"))
            {
                locale = "FR";
            }
            else if (header.Contains("IT"))
            {
                locale = "IT";
            }
            else if (header.Contains("ES"))
            {
                locale = "ES";
            }
            else
            {
                locale = "DE";
            }
            return locale;
        }
        public static string getDomainFromHeader(string header)
        {
            string domain = "";
            //Check for domain
            if (header.Contains("PreProd"))
            {
                domain = "PreProd";
            }
            else if (header.Contains("Prod"))
            {
                domain = "Prod";
            }
            else
            {
                domain = "Devo";
            }
            return domain;
        }
        static class LinkFinder
        {
            public static List<LinkItem> Find(string file)
            {
                List<LinkItem> list = new List<LinkItem>();
                // Find all matches in file.
                MatchCollection m1 = Regex.Matches(file, @"(<a.*?>.*?</a>)",
                    RegexOptions.Singleline);
                // Loop over each match.
                foreach (Match m in m1)
                {
                    string value = m.Groups[1].Value;
                    LinkItem i = new LinkItem();
                    // Get href attribute.
                    Match m2 = Regex.Match(value, @"href=\""(.*?)\""",
                    RegexOptions.Singleline);
                    if (m2.Success)
                    {
                        i.Href = m2.Groups[1].Value;
                    }
                    // Remove inner tags from text.
                    string t = Regex.Replace(value, @"\s*<.*?>\s*", "",
                    RegexOptions.Singleline);
                    i.Text = t;
                    list.Add(i);
                }
                return list;
            }
        }
    }
}
Advertisements
This entry was posted in Information Technology. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s