using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Xml.Linq;
using Microsoft.VisualStudio.Tools.Applications.Runtime;
using Excel = Microsoft.Office.Interop.Excel;
using Office = Microsoft.Office.Core;

// Additional namespaces required by code
using System.Xml;
using System.IO;
using System.Web;

namespace ExcelWorkbook2
{
    public partial class Sheet1
    {
        private void Sheet1_Startup(object sender, System.EventArgs e)
        {
        }

        private void Sheet1_Shutdown(object sender, System.EventArgs e)
        {
        }

        #region VSTO Designer generated code

        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InternalStartup()
        {
            this.button1.Click += new System.EventHandler(this.button1_Click);
            this.button2.Click += new System.EventHandler(this.button2_Click);
            this.Startup += new System.EventHandler(this.Sheet1_Startup);
            this.Shutdown += new System.EventHandler(this.Sheet1_Shutdown);

        }

        #endregion

        private void button1_Click(object sender, EventArgs e)
        {
            Excel.Range taskData = this.Range["TaskData"];

            // Loop through all cells in data range
            foreach (Excel.Range row in taskData.Rows)
            {
                foreach (Excel.Range cell in row.Cells)
                {
                    // If there's a value in the cell, try
                    // to read it in as a numeric value
                    if (cell.Value != null)
                    {
                        double hours = 0;
                        if (double.TryParse(cell.Value.ToString(), out hours))
                        {
                            // Cell value is value number, so read in rest of data
                            string startDate = this.Range["A" + cell.Row.ToString()].Value.ToString();
                            string endDate = this.Range["B" + cell.Row.ToString()].Value.ToString();
                            string title = this.Range["C" + cell.Row.ToString()].Value.ToString();
                            string timekeeper = this.Range[cell.Address.Split('$')[1] + "1"].Value.ToString();

                            // Check the comment field to see if it has a pointer
                            // to an existing item in the corresponding task list
                            int id = 0;
                            if (cell.Comment != null) int.TryParse(cell.Comment.Text(), out id);

                            if (id == 0)
                            {
                                // If this is a new entry, also set the cell's comment field
                                // to the ID # of the task in SharePoint
                                cell.AddComment(SaveTask(id, startDate, endDate, title, timekeeper, hours));
                            }
                            else
                            {
                                // Otherwise just save without changing the cell's comment
                                SaveTask(id, startDate, endDate, title, timekeeper, hours);
                            }
                        }
                    }
                }
            }

            MessageBox.Show("Upload Complete");
        }

        public void GetTasks()
        {
            // Get handle to web service
            ListsService.Lists lsvc = new ListsService.Lists();
            lsvc.Credentials = System.Net.CredentialCache.DefaultCredentials;
            lsvc.Url = "http://mossdev05/sites/excel_vsto/_vti_bin/Lists.asmx";

            XmlDocument xmlDoc = new XmlDocument();

            XmlNode ndQuery = xmlDoc.CreateNode(XmlNodeType.Element, "Query", "");
            XmlNode ndViewFields = xmlDoc.CreateNode(XmlNodeType.Element, "ViewFields", "<FieldRef Name='Assigned To'/>");
            XmlNode ndQueryOptions = xmlDoc.CreateNode(XmlNodeType.Element, "QueryOptions", "");

            DataSet dsTasks = new DataSet();
            dsTasks.DataSetName = "dsTasks";

            ndQueryOptions.InnerXml = "<IncludeMandatoryColumns>FALSE</IncludeMandatoryColumns><DateInUtc>TRUE</DateInUtc>";

            // Retrieve the current task list from SharePoint
            XmlNode ndListItems = lsvc.GetListItems("Tasks", null, ndQuery, ndViewFields, "2000", ndQueryOptions, null);

            // If data returned, loop through cells in task data range
            // finding cells linked to tasks downloaded, updating
            // the "Hours" value for each
            Excel.Range taskData = this.Range["TaskData"];
            foreach (Excel.Range row in taskData.Rows)
            {
                foreach (Excel.Range cell in row.Cells)
                {
                    // Only interested in cells with a 
                    // comment containing linked ID#
                    if (cell.Comment != null)
                    {
                        // Get the linked ID#
                        string id = cell.Comment.Text();

                        // Find downloaded row matching the ID#
                        foreach (XmlNode xmlRow in ndListItems["rs:data"].ChildNodes)
                        {
                            if (xmlRow.Name == "z:row")
                            {
                                if (xmlRow.Attributes["ows_ID"].Value == id)
                                {
                                    cell.Value = double.Parse(xmlRow.Attributes["ows_Hours"].Value);
                                }
                            }
                        }
                    }
                }
            }

        }


        private string SaveTask(int id, string startDate, string endDate, string title, string timekeeper, double hours)
        {
            ListsService.Lists lsvc = new ListsService.Lists();
            lsvc.Url = "http://mossdev05/sites/excel_vsto/_vti_bin/Lists.asmx";
            lsvc.Credentials = System.Net.CredentialCache.DefaultCredentials;

            // If "id" value = 0, this is a new task, otherwise we're 
            // updating an existing task
            string method = "New";
            string idField = "";
            if (id != 0)
            {
                method = "Update";
                idField = "<Field Name='ID'>" + id.ToString() + "</Field>";
            }

            // XML to add a new item to the list using SharePoint web method
            string strBatch = string.Format(
                    "<Method ID='0' Cmd='{5}'>" +
                        idField +
                        "<Field Name='StartDate'>{0}</Field>" +
                        "<Field Name='DueDate'>{1}</Field>" +
                        "<Field Name='Title'>{2}</Field>" +
                        "<Field Name='AssignedTo'>{3}</Field>" +
                        "<Field Name='Hours'>{4}</Field>" +
                    "</Method>",
                FormatDate(startDate),
                FormatDate(endDate),
                System.Web.HttpUtility.HtmlEncode(title.ToString().Trim()),
                EnsureUser(timekeeper, "mossdev05\\" + timekeeper, timekeeper + "@myfirm.com"),
                hours.ToString(),
                method,
                id.ToString());

            // Xml document object required in order to create various
            // element objects to define add/update operation
            XmlDocument xmlDoc = new System.Xml.XmlDocument();

            // Define the "batch" containing single add command
            System.Xml.XmlElement elBatch = xmlDoc.CreateElement("Batch");
            elBatch.SetAttribute("OnError", "Continue");
            elBatch.SetAttribute("ListVersion", "1");
            elBatch.InnerXml = strBatch;

            // Call web service to add the new task
            XmlNode ndReturn = lsvc.UpdateListItems("Tasks", elBatch);

            // Get the ID# of task just added
            if (id == 0)
            {
                int.TryParse(ndReturn["Result"]["z:row"].Attributes["ows_ID"].Value, out id);
            }
            return id.ToString();
        }

        // Utility method to add a new user to site, giving them "Read" access.
        // Note: this method is not currently being used
        string EnsureUser(string name, string alias, string email)
        {
            UserGroupService.UserGroup ugs = new UserGroupService.UserGroup();
            ugs.Url = "http://mossdev05/sites/excel_vsto/_vti_bin/usergroup.asmx";
            ugs.Credentials = System.Net.CredentialCache.DefaultCredentials;
            try
            {

                ugs.AddUserToRole("Read", name, alias, email, "Added to ensure user account exists");
            }
            catch { }

            // Now get the user just added
            XmlNode ndUsers = ugs.GetUserCollectionFromWeb();
            DataSet dsUsers = new DataSet();
            StringReader sr = new StringReader(ndUsers.OuterXml);
            dsUsers.ReadXml(sr);
            sr.Close();

            DataView dvUser = new DataView(dsUsers.Tables[1], "LoginName='" + alias + "'", "", DataViewRowState.CurrentRows);
            try
            {
                return dvUser[0]["ID"].ToString() + ";#" + dvUser[0]["Name"].ToString();
            }
            catch { }
            return "";
        }

        string FormatDate(string myDate)
        {
            // Format date in YYYY-MM-DD format appropriate
            // for use with UpdateListItems() web method
            DateTime dtMyDate = DateTime.Parse(myDate);
            return string.Format("{0}-{1}-{2}",
                dtMyDate.Year.ToString(),
                dtMyDate.Month.ToString(),
                dtMyDate.Day.ToString());
        }

        private void button2_Click(object sender, EventArgs e)
        {
            GetTasks();
            MessageBox.Show("Download Complete");
        }
    }
}