using System;
using System.Runtime.InteropServices;
using System.ComponentModel;
using System.Collections;
using System.Text.RegularExpressions;

namespace DINO_AddIn
{
    /// <summary>
    ///   Add-in Express Add-in Module
    /// </summary>
    [GuidAttribute("EE3AE0AC-9644-4BFC-8EF0-3948BBA934B4"), ProgId("DINO_AddIn.AddinModule")]
    public class AddinModule : AddinExpress.MSO.ADXAddinModule
    {
        //const string baseURL = "http://dinolab51.nitg.tno.nl/Geo3DModelWebservices-1.0/Geo3DModelService"; //OLD URL
        //const string baseURL = "http://nit.dinoservices.nl/geo3dmodelwebservices-1/Geo3DModelService?wsdl"; //OLD 17 Dec 09
        const string baseURL = "http://www.dinoservices.nl/geo3dmodelwebservices-1/Geo3DModelService?wsdl"; //NEW 10 Jan 10

        public AddinModule()
        {
            InitializeComponent();
            //Create the DINO Web Service controle Form
            myForm = new ServiceForm(baseURL);
            //Set a handler for the "finished" form event
            myForm.DinoCallFinishedEvent += new DinoCallFinishedEventHandler(this.Dino_CallFinished);
        }

        private int maxParamIndex = 0; //higher Index of the parameters so long
        private int colOffset = 0;     // Result's table X offset
        private int rowOffset = 0;     // Result's table Y offset
        private bool activateParams = true;
        public ServiceForm myForm = null;
        private Hashtable paramTable = new Hashtable();

        private object m_objOpt = System.Reflection.Missing.Value;
        private System.Globalization.CultureInfo oldCulture;
        private System.Globalization.CultureInfo newCulture;

        private AddinExpress.MSO.ADXCommandBar adxCommandBar1;
        private AddinExpress.MSO.ADXCommandBarButton adxCommandBarButton1;
        private System.Windows.Forms.ImageList imageList1;
        private AddinExpress.MSO.ADXRibbonTab adxRibbonTab1;
        private AddinExpress.MSO.ADXRibbonGroup adxRibbonGroup1;
        private AddinExpress.MSO.ADXRibbonButton adxRibbonButton1;

        #region VBA Variables Declaration

        public double xVBA = 0;
        public double yVBA = 0;
        public string modelVBA = "";
        public int resolutionVBA = 0;
        public string urlVBA = "";
        public int colVBA = 0;
        public int rowVBA = 0;
 
        #endregion

        #region Component Designer generated code
        /// <summary>
        /// Required by designer
        /// </summary>
        private System.ComponentModel.IContainer components;
 
        /// <summary>
        /// Required by designer support - do not modify
        /// the following method
        /// </summary>
        private void InitializeComponent()
        {
            this.components = new System.ComponentModel.Container();
            System.ComponentModel.ComponentResourceManager resources = new System.ComponentModel.ComponentResourceManager(typeof(AddinModule));
            this.adxCommandBar1 = new AddinExpress.MSO.ADXCommandBar(this.components);
            this.adxCommandBarButton1 = new AddinExpress.MSO.ADXCommandBarButton(this.components);
            this.imageList1 = new System.Windows.Forms.ImageList(this.components);
            this.adxRibbonTab1 = new AddinExpress.MSO.ADXRibbonTab(this.components);
            this.adxRibbonGroup1 = new AddinExpress.MSO.ADXRibbonGroup(this.components);
            this.adxRibbonButton1 = new AddinExpress.MSO.ADXRibbonButton(this.components);
            // 
            // adxCommandBar1
            // 
            this.adxCommandBar1.CommandBarName = "DINO Web Service";
            this.adxCommandBar1.CommandBarTag = "38e5d44f-7469-45c7-b7f5-e34415a78197";
            this.adxCommandBar1.Controls.Add(this.adxCommandBarButton1);
            this.adxCommandBar1.SupportedApps = AddinExpress.MSO.ADXOfficeHostApp.ohaExcel;
            this.adxCommandBar1.UpdateCounter = 6;
            // 
            // adxCommandBarButton1
            // 
            this.adxCommandBarButton1.Caption = "DINO";
            this.adxCommandBarButton1.ControlTag = "c8c82d09-f3e5-4199-bf58-7a6ef3438882";
            this.adxCommandBarButton1.Image = 0;
            this.adxCommandBarButton1.ImageList = this.imageList1;
            this.adxCommandBarButton1.ImageTransparentColor = System.Drawing.Color.Transparent;
            this.adxCommandBarButton1.Style = AddinExpress.MSO.ADXMsoButtonStyle.adxMsoButtonIconAndCaption;
            this.adxCommandBarButton1.UpdateCounter = 6;
            this.adxCommandBarButton1.Click += new AddinExpress.MSO.ADXClick_EventHandler(this.adxCommandBarButton1_Click);
            // 
            // imageList1
            // 
            this.imageList1.ImageStream = ((System.Windows.Forms.ImageListStreamer)(resources.GetObject("imageList1.ImageStream")));
            this.imageList1.TransparentColor = System.Drawing.Color.Transparent;
            this.imageList1.Images.SetKeyName(0, "Open3dDataConverter16.ico");
            // 
            // adxRibbonTab1
            // 
            this.adxRibbonTab1.Caption = "DINO Web Service";
            this.adxRibbonTab1.Controls.Add(this.adxRibbonGroup1);
            this.adxRibbonTab1.Id = "adxRibbonTab_5b769a2f9cb64670a9c9d1ac41b44059";
            this.adxRibbonTab1.Ribbons = AddinExpress.MSO.ADXRibbons.msrExcelWorkbook;
            // 
            // adxRibbonGroup1
            // 
            this.adxRibbonGroup1.Caption = "DINO Web Service";
            this.adxRibbonGroup1.Controls.Add(this.adxRibbonButton1);
            this.adxRibbonGroup1.Id = "adxRibbonGroup_39c074912da346df9f9afe19deff166c";
            this.adxRibbonGroup1.ImageTransparentColor = System.Drawing.Color.Transparent;
            this.adxRibbonGroup1.Ribbons = AddinExpress.MSO.ADXRibbons.msrExcelWorkbook;
            // 
            // adxRibbonButton1
            // 
            this.adxRibbonButton1.Caption = "DINO Web Service";
            this.adxRibbonButton1.Id = "adxRibbonButton_924acfb8e9924e009b4891e5ef8ef37b";
            this.adxRibbonButton1.Image = 0;
            this.adxRibbonButton1.ImageList = this.imageList1;
            this.adxRibbonButton1.ImageTransparentColor = System.Drawing.Color.Transparent;
            this.adxRibbonButton1.Ribbons = AddinExpress.MSO.ADXRibbons.msrExcelWorkbook;
            this.adxRibbonButton1.OnClick += new AddinExpress.MSO.ADXRibbonOnAction_EventHandler(this.adxRibbonButton1_OnClick);
            // 
            // AddinModule
            // 
            this.AddinName = "DINO_AddIn";
            this.SupportedApps = AddinExpress.MSO.ADXOfficeHostApp.ohaExcel;

        }
        #endregion
 
        #region Add-in Express automatic code
 
        // Required by Add-in Express - do not modify
        // the methods within this region
 
        public override System.ComponentModel.IContainer GetContainer()
        {
            if (components == null)
                components = new System.ComponentModel.Container();
            return components;
        }
 
        [ComRegisterFunctionAttribute]
        public static void AddinRegister(Type t)
        {
            AddinExpress.MSO.ADXAddinModule.ADXRegister(t);
        }
 
        [ComUnregisterFunctionAttribute]
        public static void AddinUnregister(Type t)
        {
            AddinExpress.MSO.ADXAddinModule.ADXUnregister(t);
        }
 
        public override void UninstallControls()
        {
            base.UninstallControls();
        }

        #endregion

        public Excel._Application ExcelApp
        {
            get
            {
                return (HostApplication as Excel._Application);
            }
        }

        //
        // Sets a neutral Language Settings to avoid generic Excel error
        //
        public void setNewCulture()
        {
            oldCulture = System.Threading.Thread.CurrentThread.CurrentCulture;
            newCulture = new System.Globalization.CultureInfo(ExcelApp.LanguageSettings.get_LanguageID(Office.MsoAppLanguageID.msoLanguageIDUI));
            System.Threading.Thread.CurrentThread.CurrentCulture = newCulture;
        }

        //
        // Sets back the original Language Settings
        //
        public void setOldCulture()
        {
            System.Threading.Thread.CurrentThread.CurrentCulture = oldCulture;
        }

        //
        // Excel 2007 button clicked
        //
        private void adxRibbonButton1_OnClick(object sender, AddinExpress.MSO.IRibbonControl control, bool pressed)
        {
            openServiceForm();
        }

        //
        // Excel 2000-2003 button clicked
        //
        private void adxCommandBarButton1_Click(object sender)
        {
            openServiceForm();
        }

        //
        // Opens the parameter selection form
        //
        private void openServiceForm()
        {
            if (ExcelApp.ActiveSheet != null)
            {
                myForm.ShowDialog();
                myForm.BringToFront();
            }
            else {
                System.Windows.Forms.MessageBox.Show("No active worksheet");
            }
            
        }

        //
        // Event handler serving the DINO form finished event
        //
        private void Dino_CallFinished(DinoCallFinishedEventArgs responseArgs)
        {
            GeoColumn geoColumn = responseArgs.geoColumn;

            try
            {
                setNewCulture();
                //Result table origin coordinates
                colOffset = ExcelApp.ActiveCell.Column -1;
                rowOffset = ExcelApp.ActiveCell.Row -1; 
                setHeader();

                //Fill the results table
                fillSheet(geoColumn);

            }
            catch (Exception ee)
            {
                System.Windows.Forms.MessageBox.Show("Error in the Excel info feeding:\n" + ee.Message);
            }
            setOldCulture();
            myForm.Hide();

        }

        //
        // Fills the results table with the info from the DINO WebService
        //
        public void fillSheet(GeoColumn data)
        {
            GeoParameter[] parameters = null;
            int col = 0;
            Excel.Range r = null;
            paramTable.Clear();
            maxParamIndex = 0;
            for (int i = 0; i < data.geoLayers.Length; i++)
            {
                col = 1 + colOffset;
                // geoLayer Column eliminated
                //r = (Excel.Range)ExcelApp.Cells[i + iniRow, col++];
                //r.Value2 = "geoLayer " + i;
                //r.Font.Bold = true;
                r = (Excel.Range)ExcelApp.Cells[i + 2 + rowOffset, col++];
                r.Value2 = data.geoLayers[i].name;
                r.Name = formatCellName(data.geoLayers[i].code + "_NAME");
                r = (Excel.Range)ExcelApp.Cells[i + 2 + rowOffset, col++];
                r.Value2 = data.geoLayers[i].code;
                r.Name = formatCellName(data.geoLayers[i].code + "_CODE");
                r = (Excel.Range)ExcelApp.Cells[i + 2 + rowOffset, col++];
                r.Value2 = data.geoLayers[i].top;
                r.Name = formatCellName(data.geoLayers[i].code + "_TOP");
                r = (Excel.Range)ExcelApp.Cells[i + 2 + rowOffset, col++];
                r.Value2 = data.geoLayers[i].bottom;
                r.Name = formatCellName(data.geoLayers[i].code + "_BOTTOM");
                if (data.geoLayers[i].parameters != null && activateParams)
                {
                    parameters = data.geoLayers[i].parameters;
                    for (int j = 0; j < parameters.Length; j++)
                    {
                        if (parameters[j] != null)
                        {
                            int paramIndex = 0;
                            if (paramTable.ContainsKey(parameters[j].name))
                            {
                                paramIndex = (int)paramTable[parameters[j].name];
                            }
                            else {
                                maxParamIndex++;
                                paramTable.Add(parameters[j].name, maxParamIndex);
                                r = (Excel.Range)ExcelApp.Cells[1 + rowOffset, 4 + maxParamIndex + colOffset];
                                r.Value2 = parameters[j].name;
                                r.Name = formatCellName("Param"+maxParamIndex+"_Header");
                                paramIndex = maxParamIndex;
                            }
                            r = (Excel.Range)ExcelApp.Cells[i + 2 + rowOffset, 4 + paramIndex + colOffset];
                            r.Value2 = parameters[j].value;
                            //r.Name = data.geoLayers[i].code + "_" + parameters[j].name; //Gives error if forbiden parameter
                            r.Name = formatCellName(data.geoLayers[i].code + "_Param"+paramIndex); //Controled cell name needed
                        }
                    }
                }
            }
        }

        // 
        // Prints in the Excel worksheet the header of the result table
        //
        public void setHeader()
        {
            //geoLayers Header
            Excel.Range r = (Excel.Range)ExcelApp.Cells[1 + rowOffset, 1 + colOffset];
            r.Value2 = "name";
            r.Name = "Name_Header";
            r = (Excel.Range)ExcelApp.Cells[1 + rowOffset, 2 + colOffset];
            r.Value2 = "code";
            r.Name = "Code_Header";
            r = (Excel.Range)ExcelApp.Cells[1 + rowOffset, 3 + colOffset];
            r.Value2 = "top";
            r.Name = "Top_Header";
            r = (Excel.Range)ExcelApp.Cells[1 + rowOffset, 4 + colOffset];
            r.Value2 = "bottom";
            r.Name = "Bottom_Header";

            //Header row bold activation
            r = (Excel.Range)ExcelApp.Cells[1 + rowOffset, 1];
            r = r.EntireRow;
            r.Font.Bold = true;
        }

        //
        // Normalizes the cell name to ensure it is valid
        //
        private string formatCellName(string cellName)
        { 
            string newCellName;
            //Cell name must start with a letter or an underscore
            if (Regex.IsMatch(cellName, "^[_a-fA-F]"))
                newCellName = "_" +cellName;
            else
                newCellName = cellName;
            
            if (newCellName.Length > 250)
                newCellName = newCellName.Substring(0, 250);

            newCellName = newCellName.Replace(" ", "");
            newCellName = newCellName.Replace("-", "");
            newCellName = newCellName.Replace("?", "");
            newCellName = newCellName.Replace("*", "");
            newCellName = newCellName.Replace("\\", "");
            newCellName = newCellName.Replace("/", "");
            newCellName = newCellName.Replace("[", "");
            newCellName = newCellName.Replace("]", "");
            return newCellName;
        }

        #region VBA Methods

        //
        // Functions accessible from VBA. The VBA variables (see "VBA Variables Declaration" region at the top of the class) must be fullfilled first
        //

        public void DINOatVBA()
        {
            Geo3DModelService DinoService = new Geo3DModelService();

            if (urlVBA != "")
            {
                DinoService.Url = urlVBA;
            }

            GeoColumn result = DinoService.sampleColumn(modelVBA, xVBA, yVBA, resolutionVBA);
            colOffset = colVBA;
            rowOffset = rowVBA;

            setHeader();
            fillSheet(result);

            clearVBAData();
        }

        public void popModel(string model)
        {
            modelVBA = model;
        }

        public void popUrl(string url)
        {
            urlVBA = url;
        }

        public void popResolution(int resolution)
        {
            resolutionVBA = resolution;
        }

        public void popX(double x)
        {
            xVBA = x;
        }

        public void popY(double y)
        {
            yVBA = y;
        }

        public void popCol(int col)
        {
            colVBA = col;
        }

        public void popRow(int row)
        {
            rowVBA = row;
        }

        public void clearVBAData()
        { 
            xVBA = 0;
            yVBA = 0;
            modelVBA = "";
            resolutionVBA = 0;
            urlVBA = "";
            colVBA = 0;
            rowVBA = 0;
        }
        #endregion
    }
}

