using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Office.Excel.Server.Udf;
using System.Runtime.InteropServices;
using System.Collections;

namespace ClassLibrary1
{
    [ProgId("VSTOExcelSvcs.UDF.OpportunityFunctions")]
    [ClassInterface(ClassInterfaceType.AutoDual)]
    [Guid(OpportunityFunctions.Guid)]
    [ComVisible(true)]
    [UdfClass]
    public class OpportunityFunctions
    {
        public const string Guid = "76E9C116-0428-4bcd-AD63-DC618299E9E0";

        [ComVisible(true)]
        [UdfMethod(IsVolatile = true)]
        public object[,] GetMyOpportunities(int Rows)
        {
            try
            {
                com.pv.pvjira.JiraSoapServiceService jiraService = 
                    new com.pv.pvjira.JiraSoapServiceService();
                string sessionId = jiraService.login("username", "password");
                com.pv.pvjira.RemoteIssue[] issues = (com.pv.pvjira.RemoteIssue[])jiraService.getIssuesFromFilter(sessionId, "11557");
                com.pv.pvjira.RemoteStatus[] status = (com.pv.pvjira.RemoteStatus[])jiraService.getStatuses(sessionId);
                Hashtable projectStatues = new Hashtable();

                foreach (com.pv.pvjira.RemoteIssue issue in issues)
                {
                    string statusName = GetStatusName(issue.status, status);
                    if (projectStatues.ContainsKey(statusName))
                    {
                        DictionaryEntry projectStatus = GetProjectStatuesFromHashtable(statusName, projectStatues);
                        projectStatues.Remove(statusName);
                        projectStatues.Add(statusName, int.Parse(projectStatus.Value.ToString()) + 1);
                    }
                    else
                    {
                        projectStatues.Add(statusName, 1);
                    }
                }

                    // Size the array.
                    object[,] resultArray = new object[Rows, 2];

                    //Write out the column headers.
                    //resultArray[0, 0] = "STATUS";
                    //resultArray[0, 1] = "RIO";
                    //resultArray[1, 0] = "STATUS";
                    //resultArray[1, 1] = "RIO";                    
                    int row = 0;

                    // And now the actual data 
                    foreach (DictionaryEntry projectStatus in projectStatues)
                    {
                        resultArray[row, 0] = projectStatus.Key.ToString();
                        resultArray[row, 1] = projectStatus.Value.ToString();
                        row++;
                    }
                    while (row <= 19)
                    {
                        resultArray[row, 0] = "";
                        resultArray[row, 1] = "";
                        row++;
                    }
                    return resultArray;
                }
            catch (Exception x)
            {
                object[,] resultArray = new object[1, 1];
                resultArray[0, 0] = x.Message;
                return resultArray;
            }
        }

        private string GetStatusName(string id, ClassLibrary1.com.pv.pvjira.RemoteStatus[] status)
        {
            foreach (ClassLibrary1.com.pv.pvjira.RemoteStatus foo in status)
            {
                if (foo.id.Equals(id))
                    return foo.name;
            }

            return null;
        }

        private DictionaryEntry GetProjectStatuesFromHashtable(string key, Hashtable hashtable)
        {
            DictionaryEntry de = new DictionaryEntry();
            foreach (DictionaryEntry foo in hashtable)
            {
                if (foo.Key.Equals(key))
                {
                    return foo;
                }
            }
            return de;
        } 
    }
}