#region Help:  Introduction to the Script Component
/* The Script Component allows you to perform virtually any operation that can be accomplished in
 * a .Net application within the context of an Integration Services data flow.
 *
 * Expand the other regions which have "Help" prefixes for examples of specific ways to use
 * Integration Services features within this script component. */
#endregion

#region Namespaces
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Net.Http.Headers;
using System.Web.Script.Serialization;
#endregion

/// <summary>
/// This is the class to which to add your code.  Do not change the name, attributes, or parent
/// of this class.
/// </summary>
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    #region Help:  Using Integration Services variables and parameters
    /* To use a variable in this script, first ensure that the variable has been added to
     * either the list contained in the ReadOnlyVariables property or the list contained in
     * the ReadWriteVariables property of this script component, according to whether or not your
     * code needs to write into the variable.  To do so, save this script, close this instance of
     * Visual Studio, and update the ReadOnlyVariables and ReadWriteVariables properties in the
     * Script Transformation Editor window.
     * To use a parameter in this script, follow the same steps. Parameters are always read-only.
     *
     * Example of reading from a variable or parameter:
     *  DateTime startTime = Variables.MyStartTime;
     *
     * Example of writing to a variable:
     *  Variables.myStringVariable = "new value";
     */
    #endregion

    #region Help:  Using Integration Services Connnection Managers
    /* Some types of connection managers can be used in this script component.  See the help topic
     * "Working with Connection Managers Programatically" for details.
     *
     * To use a connection manager in this script, first ensure that the connection manager has
     * been added to either the list of connection managers on the Connection Managers page of the
     * script component editor.  To add the connection manager, save this script, close this instance of
     * Visual Studio, and add the Connection Manager to the list.
     *
     * If the component needs to hold a connection open while processing rows, override the
     * AcquireConnections and ReleaseConnections methods.
     * 
     * Example of using an ADO.Net connection manager to acquire a SqlConnection:
     *  object rawConnection = Connections.SalesDB.AcquireConnection(transaction);
     *  SqlConnection salesDBConn = (SqlConnection)rawConnection;
     *
     * Example of using a File connection manager to acquire a file path:
     *  object rawConnection = Connections.Prices_zip.AcquireConnection(transaction);
     *  string filePath = (string)rawConnection;
     *
     * Example of releasing a connection manager:
     *  Connections.SalesDB.ReleaseConnection(rawConnection);
     */
    #endregion

    #region Help:  Firing Integration Services Events
    /* This script component can fire events.
     *
     * Example of firing an error event:
     *  ComponentMetaData.FireError(10, "Process Values", "Bad value", "", 0, out cancel);
     *
     * Example of firing an information event:
     *  ComponentMetaData.FireInformation(10, "Process Values", "Processing has started", "", 0, fireAgain);
     *
     * Example of firing a warning event:
     *  ComponentMetaData.FireWarning(10, "Process Values", "No rows were received", "", 0);
     */
    #endregion

    private String clientID, secretKey, resource, oauthToken, apiUrl, tokenURL;
    private HttpClient httpClient;

    /// <summary>
    /// This method is called once, before rows begin to be processed in the data flow.
    ///
    /// You can remove this method if you don't need to do anything here.
    /// </summary>
    public override void PreExecute()
    {
        base.PreExecute();
        /*
         * Add your code here
         */
        apiUrl = "https://xxxxxx.crm8.dynamics.com/api/data/v8.2/";
        clientID = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxx"; // Client id to generate Bearer Token
        secretKey = "xxxxxxxxxxxxxxxxxxxx"; //Secret Key
        resource = "https://xxxxxx.crm8.dynamics.com"; //resource
		tokenURL="https://login.microsoftonline.com:443/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxx/oauth2/token/"
        GetBearerToken();
    }

    private void GetBearerToken()
    {
        HttpClientHandler clientHandler = new HttpClientHandler();
        clientHandler.Proxy = WebRequest.GetSystemWebProxy();
        clientHandler.Proxy.Credentials = CredentialCache.DefaultCredentials; // or new NetworkCredential("username","password","DOMAIN");
        clientHandler.UseProxy = true;
        httpClient = new HttpClient(clientHandler);
        ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12;
        HttpContent requestContent = new FormUrlEncodedContent(new Dictionary<string, string>
            {
                {"grant_type","client_credentials"},
                {"client_id",clientID},
                {"client_secret",secretKey},
                {"resource",resource}
            }
        );
        HttpResponseMessage response = httpClient.PostAsync(tokenURL, requestContent).Result;

        String responseString = response.Content.ReadAsStringAsync().Result;

        JavaScriptSerializer serialiser = new JavaScriptSerializer();
        dynamic apiResult = serialiser.DeserializeObject(responseString);

        oauthToken = (String)apiResult["access_token"];
    }

    /// <summary>
    /// This method is called after all the rows have passed through this component.
    ///
    /// You can delete this method if you don't need to do anything here.
    /// </summary>
    public override void PostExecute()
    {
        base.PostExecute();
        /*
         * Add your code here
         */
    }

    public override void CreateNewOutputRows()
    {
        /*
          Add rows by calling the AddRow method on the member variable named "<Output Name>Buffer".
          For example, call MyOutputBuffer.AddRow() if your output was named "MyOutput".
        */
        String query = "$select=opportunityid,estimatedclosedate";
        String crmRestQuery = apiUrl + "opportunities?" + query;

        Boolean isDone = false;
        while (isDone == false)
        {
isDone = true;
            HttpRequestMessage request = new HttpRequestMessage(HttpMethod.Get, crmRestQuery);

            //add header parameters
            request.Headers.Add("Prefer", "odata.include-annotations=\"*\"");//for formatted values
            request.Headers.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
            request.Headers.Add("Authorization", "Bearer " + oauthToken);

            //send request
            HttpResponseMessage response = httpClient.SendAsync(request).Result;
            String responseString = response.Content.ReadAsStringAsync().Result;

            JavaScriptSerializer serialiser = new JavaScriptSerializer();
            serialiser.MaxJsonLength = 50 * 1000000;	// Increased to deserialize large number of fields 
            dynamic apiResult = serialiser.DeserializeObject(responseString);

            //--------process dynamic apiResult

            object[] result = null;
            //get only values from response
            foreach (var item in apiResult)
            {
                object key = item.Key;
                if ((string)key == "value")
                    result = (object[])item.Value;
              if ((string)key == "@odata.nextLink")
                {
                    isDone = false;
                    crmRestQuery = (string)item.Value;
                }
            }
            //array of object to list
            List<object> res = result.ToList<object>();
            //for each record in result
            foreach (var item in res)
            {
                //add row of output columns for each record
                Output0Buffer.AddRow();

                Dictionary<string, object> obj = (Dictionary<string, object>)item;
                //all attributes from Record (object)
                foreach (KeyValuePair<string, object> item1 in obj)
                {
                    if (item1.Key == "opportunityid")
                        Output0Buffer.ID = (String)item1.Value;
                    if (item1.Key == "estimatedclosedate")
                        Output0Buffer.EstCloseDate = (String)item1.Value;
   if (item1.Key == "estimatedclosedate@OData.Community.Display.V1.FormattedValue")
                        Output0Buffer.EstCloseDateFormatted = (String)item1.Value;
                }
            }
        }
    }
}

    public override void CreateNewOutputRows()
    {
        /*
          Add rows by calling the AddRow method on the member variable named "<Output Name>Buffer".
          For example, call MyOutputBuffer.AddRow() if your output was named "MyOutput".
        */
        string query = @"<fetch {0} version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>
                                         <entity name='opportunity'>
                                            <attribute name='opportunityid' />
                                            <attribute name='estimatedclosedate' />
                                          </entity>
                                        </fetch>";

        int page = 1;
        var cookie = string.Format("page='{0}'",page);

        string xml = string.Format(query, cookie);
        string crmRestQuery = apiUrl + "opportunities?fetchXml=" + xml;

        bool isDone = false;
        while (isDone == false)
        {
            isDone = true;
            HttpRequestMessage request = new HttpRequestMessage(HttpMethod.Get, crmRestQuery);
            //add header parameters
            request.Headers.Add("Prefer", "odata.include-annotations=\"*\"");//for formatted values
            request.Headers.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
            request.Headers.Add("Authorization", "Bearer " + oauthToken);

            //send request
            HttpResponseMessage response = httpClient.SendAsync(request).Result;
            string responseString = response.Content.ReadAsStringAsync().Result;

            JavaScriptSerializer serialiser = new JavaScriptSerializer();
            serialiser.MaxJsonLength = 50 * 1000000;	// Increased to deserialize large number of fields 
            dynamic apiResult = serialiser.DeserializeObject(responseString);

            //--------process dynamic apiResult

            object[] result = null;
            //get only values from response
            foreach (var item in apiResult)
            {
                object key = item.Key;
                if ((string)key == "value")
                    result = (object[])item.Value;
                //when Rest API using fetchXML 
                if ((string)key == "@Microsoft.Dynamics.CRM.fetchxmlpagingcookie")
                {
                    isDone = false;
                    page++;

                   //retrieve pagingCookie value
                   string xmlVal = WebUtility.UrlDecode((WebUtility.UrlDecode((string)item.Value)));
                   int Start = xmlVal.IndexOf("pagingcookie=\"", 0) + "pagingcookie=\"".Length;
                   int End = xmlVal.IndexOf("\" istracking=\"False\"", Start);
                   
                    //proccess the pagingCookie value to support in API URL
                    string strVal= xmlVal.Substring(Start, End - Start).Replace("<", "%26lt;").Replace(">", "%26gt;").Replace("\"", "%26quot;").Replace("'", "%26apos;").Replace("&", "%26amp;");
                    cookie = string.Format("page='{0}' paging-cookie='{1}'",page, strVal);
                    
                    //modify the query for next page with paging cookie
                    xml = string.Format(query, cookie);
                    crmRestQuery = apiUrl + "opportunities?fetchXml=" + xml;

                }

            }
            //array of object to list
            List<object> res = result.ToList<object>();
            //for each record in result
            foreach (var item in res)
            {
                //add row of output columns
                Output0Buffer.AddRow();

                Dictionary<string, object> obj = (Dictionary<string, object>)item;
                //all attributes from Record (object)
                foreach (KeyValuePair<string, object> item1 in obj)
                {
                    if (item1.Key == "opportunityid")
                        Output0Buffer.ID = (String)item1.Value;
                    if (item1.Key == "estimatedclosedate")
                        Output0Buffer.EstCloseDate = (String)item1.Value;
                    if (item1.Key == "estimatedclosedate@OData.Community.Display.V1.FormattedValue")
                        Output0Buffer.EstCloseDateFormatted = (String)item1.Value;
                }

            }
        }