Friday, February 27, 2015

Full circle: C# use com to read a spreadsheet then write to XRM using SDK.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Windows;
using Excel = Microsoft.Office.Interop.Excel;
using System.Text;
using System.Configuration;
using Applications.Common.Business;
using System.ServiceModel.Description;
using Microsoft.Xrm.Sdk.Client;
using Microsoft.Xrm.Sdk.Query;
using Microsoft.Xrm.Sdk;
using System.Windows.Input;



namespace PopulateFCNs
{
    public struct ItemToPost
    {
        public string EntityGuid;
        public string FCN;
        public string CorpCode;
        public string DocumentType;
        public string Combined()
        {
            return String.Format("{0}.{1}.{2}.{3}", EntityGuid, FCN, CorpCode, DocumentType);
        }
    }
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {
        List<document> _documents;
        Dictionary<string, IOrganizationService> _orgs;
        Dictionary<string, ItemToPost> _billItems = new Dictionary<string, ItemToPost>();
        Dictionary<string, ItemToPost> _statementItems = new Dictionary<string, ItemToPost>();

        public MainWindow()
        {
            InitializeComponent();
            _orgs = new Dictionary<string, IOrganizationService>();
        }



        private void buttonRead_Click(object sender, RoutedEventArgs e)
        {
            const string fileName = @"C:\Projects\Sandbox - James Cooke\PopulateFCNs\Data\book1.xlsx";
            _documents = new List<document>();
            Excel.Workbook myBook = null;
            Excel.Application myApp = null;
            Excel.Worksheet mySheet = null;
            int lastRow = 0;

            myApp = new Excel.Application();
            myApp.Visible = true;
            myBook = myApp.Workbooks.Open(fileName);
            mySheet = (Excel.Worksheet)myBook.Sheets[1]; // Explicit cast is not required here
            lastRow = mySheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row;

            for (int index = 1; index <= lastRow; index++)
            {
                Array cells = (Array)mySheet.get_Range("A" + index, "H" + index).Cells.Value;
                document doc = new document();
                doc.CorpCode = getXlsData(cells, 3);
               
                if (doc.CorpCode.Length != 3)
                    continue;

                doc.DocumentType = getXlsData(cells, 4);
                if (doc.DocumentType != "Statement" && doc.DocumentType != "Bill")
                    continue;
               
                doc.FacilityControlNumbers = getXlsData(cells, 6);
                doc.InvoiceNumber = getXlsData(cells, 7);
                doc.StatementNumber = getXlsData(cells, 8);
                _documents.Add(doc);
                labelCounter.Content = index.ToString();
            }
           
           

            myBook.Close();
            myApp.Quit();

            ShowResults(_documents);

        }

        private void ShowResults(List<document> _documents)
        {
            StringBuilder sb = new StringBuilder();
            txt.Clear();
            foreach (var document in _documents)
            {
                sb.Append(String.Format("{0}\t{1}\t{2}\t{3}{4}", document.CorpCode, document.DocumentType, (document.DocumentType == "Bill" ? document.InvoiceNumber : document.StatementNumber), document.FacilityControlNumbers, Environment.NewLine));
            }
            txt.Text = sb.ToString();
        }

       

        public string getXlsData(Array cells, int columnNumber )
        {
            if (cells.GetValue(1,columnNumber)!=null)
            {
                return cells.GetValue(1, columnNumber).ToString();
            }
            else
            {
                return string.Empty;
            }

        }

        private void ButtonCalc_Click(object sender, RoutedEventArgs e)
        {
            int counter = 0;
            _billItems.Clear();
            Mouse.OverrideCursor = Cursors.Wait;
            try
            {
                string fetchXml;
                foreach (document doc in _documents)
                {
                    IOrganizationService org = getXrmService(doc.CorpCode);
                    switch (doc.DocumentType)
                    {
                        case "Bill":
                            fetchXml = GetBillFetchXml(doc.InvoiceNumber);
                            EntityCollection bills = org.RetrieveMultiple(new FetchExpression(fetchXml));
                            if (bills.Entities.Count>0)
                            {
                                foreach (string  fcn in doc.FacilityControlNumbers.Split(','))
                                {
                                    string billGuid = bills[0].Attributes["fcbt_billid"].ToString();
                                    ItemToPost itp = new ItemToPost() { EntityGuid = billGuid, FCN = fcn,CorpCode=doc.CorpCode,DocumentType=doc.DocumentType };
                                    if (_billItems.ContainsKey(itp.Combined()))
                                        continue;
                                    _billItems.Add(itp.Combined(),itp);
                                    txt_data.Text += String.Format("{0}\t{1}\t{2}\t{3}{4}", doc.CorpCode, doc.DocumentType, billGuid, fcn, Environment.NewLine);
                                }
                            }
                           
                            break;
                        case "Statement":
                            fetchXml = GetStatementFetchXml(doc.StatementNumber);
                            EntityCollection statements = org.RetrieveMultiple(new FetchExpression(fetchXml));
                            if (statements.Entities.Count > 0)
                            {
                                foreach (string fcn in doc.FacilityControlNumbers.Split(','))
                                {
                                    string statementGuid = statements[0].Attributes["fcbt_statementid"].ToString();
                                    ItemToPost itp = new ItemToPost() { EntityGuid = statementGuid, FCN = fcn, CorpCode = doc.CorpCode, DocumentType = doc.DocumentType };
                                    if (_statementItems.ContainsKey(itp.Combined()))
                                        continue;
                                    _statementItems.Add(itp.Combined(), itp);
                                    txt_data.Text += String.Format("{0}\t{1}\t{2}\t{3}{4}", doc.CorpCode, doc.DocumentType, statementGuid, fcn, Environment.NewLine);
                                }
                            }

                            break;
                        default:
                            throw new Exception("Invalid doc type in xml:" + doc.DocumentType);
                    }
                }
            }
            catch (Exception ex)
            {

                MessageBox.Show(ex.Message);
            }
            finally
            {
                Mouse.OverrideCursor = null;
            }
        }

        private string GetBillFetchXml(string invoiceNumber)
        {
            string data = string.Empty;

            data += @"<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>" + Environment.NewLine;
            data += @"              <entity name='fcbt_bill'>" + Environment.NewLine;
            data += @"                <attribute name='fcbt_billid' />" + Environment.NewLine;
            data += @"                <filter type='and'>" + Environment.NewLine;
            data += @"                  <condition attribute='fcbt_invoicenumber' operator='eq' value='invnum' />" + Environment.NewLine;
            data += @"                </filter>" + Environment.NewLine;
            data += @"              </entity>" + Environment.NewLine;
            data += @"            </fetch>" + Environment.NewLine;
            return data.Replace("invnum", invoiceNumber);
        }
        private string GetStatementFetchXml(string statementNumber)
        {
            string data = string.Empty;

            data += @"<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>" + Environment.NewLine;
            data += @"              <entity name='fcbt_statement'>" + Environment.NewLine;
            data += @"                <attribute name='fcbt_statementid' />" + Environment.NewLine;
            data += @"                <filter type='and'>" + Environment.NewLine;
            data += @"                  <condition attribute='fcbt_statementnumber' operator='eq' value='stmtnum' />" + Environment.NewLine;
            data += @"                </filter>" + Environment.NewLine;
            data += @"              </entity>" + Environment.NewLine;
            data += @"            </fetch>" + Environment.NewLine;
            return data.Replace("stmtnum", statementNumber);
        }

        IOrganizationService getXrmService(string corpCode)
        {
            if(_orgs.ContainsKey(corpCode))
            {
                return _orgs[corpCode];
            }
            else
            {
                string url=ConfigurationManager.AppSettings["xrm_"+corpCode];
                if(string.IsNullOrEmpty(url))
                {
                    throw new Exception("app.config is missing service endpoint for corpcode=" + corpCode);
                }
                else
                {
                    IOrganizationService org = CreateXRMService(url);
                    _orgs.Add(corpCode, org);
                    return org;
                }
            }
        }
       
        private IOrganizationService CreateXRMService(string url)
        {
            // Get credentials from credential database.
            string domain;
            string userName;
            string password;
            string environmentName = ConfigurationManager.AppSettings["RobotSecurityEnvironmentXrm"];
            Uri organizationUri = new Uri(url);
            RobotSecurity.GetCredentialsForEnvironment(environmentName, out domain, out userName, out password);
            userName = domain + @"\" + userName;    // XRM is happier if you do this

            ClientCredentials Credentials = new ClientCredentials();
            Credentials.UserName.UserName = userName;
            Credentials.UserName.Password = password;

            //Credentials.Windows.ClientCredential = new System.Net.NetworkCredential(userName, password, domain);

            // INITIALIZE PIPELINE SERVICE
            OrganizationServiceProxy serviceProxy = new OrganizationServiceProxy(organizationUri, null, Credentials, null);
            serviceProxy.ServiceConfiguration.CurrentServiceEndpoint.Behaviors.Add(new ProxyTypesBehavior());
            IOrganizationService service = (IOrganizationService)serviceProxy;

            // Returns the service
            return service;
           
        }


        private void ButtonPost_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                foreach (KeyValuePair<string, ItemToPost> item in _billItems)
                {
                    IOrganizationService svc = getXrmService(item.Value.CorpCode);

                    Entity parentBillEntity = svc.Retrieve("fcbt_bill", new Guid(item.Value.EntityGuid), new ColumnSet(false));

                    Entity billFacilityEntity = new Entity("fcbt_billfacility");
                    billFacilityEntity.Attributes.Add("fcbt_bill", parentBillEntity.ToEntityReference());//foreign key
                    billFacilityEntity.Attributes.Add("fcbt_facilitycontrolnumber", item.Value.FCN);
                    svc.Create(billFacilityEntity);

                }

                foreach (KeyValuePair<string, ItemToPost> item in _statementItems)
                {
                    IOrganizationService svc = getXrmService(item.Value.CorpCode);

                    Entity parentStatementEntity = svc.Retrieve("fcbt_statement", new Guid(item.Value.EntityGuid), new ColumnSet(false));

                    Entity statementFacilityEntity = new Entity("fcbt_statementfacility");
                    statementFacilityEntity.Attributes.Add("fcbt_statement", parentStatementEntity.ToEntityReference());//foreign key
                    statementFacilityEntity.Attributes.Add("fcbt_facilitycontrolnumber", item.Value.FCN);
                    svc.Create(statementFacilityEntity);

                }


            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }


    }
}