#region Help:  Introduction to the script task

/* The Script Task allows you to perform virtually any operation that can be accomplished in

 * a .Net application within the context of an Integration Services control flow. 

 * 

 * Expand the other regions which have "Help" prefixes for examples of specific ways to use

 * Integration Services features within this script task. */

#endregion

​

​

#region Namespaces

using System;

using System.Data;

using Microsoft.SqlServer.Dts.Runtime;

using System.Windows.Forms;

​

​

using Microsoft.Xrm.Sdk;

using Microsoft.Xrm.Sdk.Query;

using Microsoft.Xrm.Sdk.Client;

using System.ServiceModel.Description;

using System.Runtime.Serialization;

using System.Net;

using Microsoft.Xrm.Sdk.Messages;

using System.Data.OleDb;

using System.Collections.Generic;

using System.Linq;

using System.Data.SqlClient;

using System.Threading;

​

#endregion

​

namespace ST_08e8b41493e841ac8d78a1347d3fce79

{

​

    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]

    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

    {

​

        IOrganizationService organizationservice;

​

​

        string crmurl, crmpass, crmuser, error, result, Enseigne, API, URL;

        Object Targets;

​

        public OrganizationServiceProxy ConnectToCrm()

        {

            ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12;

            IServiceManagement<IOrganizationService> orgServiceManagement = ServiceConfigurationFactory.CreateManagement<IOrganizationService>(new Uri(crmurl));

​

            AuthenticationCredentials authCredentials = new AuthenticationCredentials();

            authCredentials.ClientCredentials.UserName.UserName = crmuser;

            authCredentials.ClientCredentials.UserName.Password = crmpass;

            AuthenticationCredentials tokenCredentials = orgServiceManagement.Authenticate(authCredentials);

​

            OrganizationServiceProxy organizationProxy = new OrganizationServiceProxy(orgServiceManagement, tokenCredentials.SecurityTokenResponse);

            organizationProxy.EnableProxyTypes();

            return organizationProxy;

        }

​

        public void Main()

        {

            //MessageBox.Show("ok");

​

            SqlConnection cnx = (SqlConnection)(Dts.Connections["campagne"].AcquireConnection(Dts.Transaction) as SqlConnection);

​

            try

            {

​

                //System.Diagnostics.Debugger.Launch();

                //System.Diagnostics.Debugger.Break();

​

                crmurl = Dts.Variables["CRMURL"].Value.ToString();

​

                crmpass = Dts.Variables["CRMPASS"].Value.ToString();

​

                crmuser = Dts.Variables["CRMUSER"].Value.ToString();

                Targets = Dts.Variables["User::Segment"].Value;

​

​

            }

            catch (Exception e)

            {

                MessageBox.Show(e.Message.ToString());

​

            }

​

            try

            {

                OleDbDataAdapter Adapter = new OleDbDataAdapter();

                DataTable dt = new DataTable();

                Adapter.Fill(dt, Dts.Variables["User::Segment"].Value);

​

                List<Segment> segments = new List<Segment>();

​

                foreach (DataRow row in dt.Rows)

                {

                    Segment s = new Segment();

                    object[] array = row.ItemArray;

​

                    segments.Add(

                        new Segment

                        {

                            Campagne = array[0].ToString(),

                            ContactId = array[1].ToString(),

                            Name = array[2].ToString(),

                            Customer = array[3].ToString()

​

                        });

                }

​

                var campagnes = segments.GroupBy(x => x.Campagne).Select(x => x.Key).ToList();

                //MessageBox.Show("Countcamp"+campagnes.Count);

                foreach (var c in campagnes)

                {

                        var listContact = segments.Where(x => x.Campagne == c).Select(x => new { contactid = x.ContactId.ToUpper(), customer = x.Customer }).ToList();

                    var segmentname = segments.Where(x => x.Campagne == c).Select(x => x.Name).FirstOrDefault();

                    //var listcustomer = segments.Where(x => x.Campagne == c).Select(x => x.Customer).ToList();

                    QueryExpression qx = new QueryExpression();

                    qx.EntityName = "msdyncrm_segment";

                    qx.Criteria.AddCondition("msdyncrm_segmentname", ConditionOperator.Like, "%" + segmentname + "_Fidélisation%");

​

​

                    Guid segmentid = Guid.Empty;

                    using (var organizationservice = ConnectToCrm())

                    {

                        EntityCollection coll = organizationservice.RetrieveMultiple(qx);

                    if (coll.Entities.Count < 1)

                    {

                        Entity segment = new Entity("msdyncrm_segment");

                        segment.Attributes["msdyncrm_segmentname"] = segmentname + "_Fidélisation";

                        segment.Attributes["msdyncrm_segmenttype"] = new OptionSetValue(192350001);

                        segment.Attributes["statuscode"] = new OptionSetValue(192350000);

​

                        segmentid = organizationservice.Create(segment);

                    }

                    else

                    {

                        segmentid = coll.Entities[0].Id;

                    }

                    }

​

                    try

                    {

                        var pageitemnumber = 100;

                        var iteration = 0;

                        bool otheriteration = true;

                        var nbiteration = listContact.Count() / pageitemnumber; //969

                        while (iteration < nbiteration)

                        {

​

​

                            using (var organizationservice = ConnectToCrm())

                            {

​

                                //MessageBox.Show("Iteration:"+iteration);

​

                                var contactToAdd = listContact.Skip(iteration * pageitemnumber).Take(pageitemnumber).Where(x => x.contactid != "NULL").Select(a => a.contactid).ToArray();

                                var customerToAdd = listContact.Skip(iteration * pageitemnumber).Take(pageitemnumber).Where(x => x.customer != "NULL").Select(a => a.customer).ToArray();

                                //var contactToAdd = listContact.Skip(iteration * 100).Take(100).Where(x => !string.IsNullOrEmpty(x.contactid)).ToArray();

                                //var customerToAdd = listContact.Skip(iteration * 100).Take(100).Where(x => !string.IsNullOrEmpty(x.customer)).ToArray();

                                //MessageBox.Show("Nombre de contact" + contactToAdd.Count());

                                iteration++;

                                //OrganizationRequest req = new OrganizationRequest("msdyncrm_SegmentMembersUpdate");

                                //req["msdyncrm_segmentid"] = segmentid.ToString();

                                //req["msdyncrm_operation"] = "addByIds";

                                ////MessageBox.Show("avant");

                                //req["msdyncrm_memberids"] = Newtonsoft.Json.JsonConvert.SerializeObject(contactToAdd);

                                ////MessageBox.Show("apres:"+ Newtonsoft.Json.JsonConvert.SerializeObject(contactToAdd));

                                //var result = organizationservice.Execute(req);

​

                                #region > new bloc

                                try

                                {

                                    OrganizationRequest req = new OrganizationRequest("msdyncrm_SegmentMembersUpdate");

                                    req["msdyncrm_segmentid"] = segmentid.ToString();

                                    req["msdyncrm_operation"] = "addByIds";

                                    //MessageBox.Show("avant");

                                    req["msdyncrm_memberids"] = Newtonsoft.Json.JsonConvert.SerializeObject(contactToAdd);

                                    //MessageBox.Show("apres:"+ Newtonsoft.Json.JsonConvert.SerializeObject(contactToAdd));

                                    var result=organizationservice.Execute(req);

​

​

                                    if (result != null)

                                    {

                                        if (!string.IsNullOrEmpty(result["Errors"].ToString()))

                                        {

                                            MessageBox.Show(result["Errors"].ToString());

                                        }

                                    }

​

                                }

                                catch (Exception ex)

                                {

                                    MessageBox.Show(ex.ToString() + " _Fidélisation");

                                }

                                #endregion

​

                                // Thread.Sleep(10000);

​

                                // MessageBox.Show("ok:");

​

                                //foreach (var cc in customerToAdd)

                                //{

                                //    #region update sql

​

                                //    SqlCommand sqlCommand1 = new SqlCommand()

                                //    {

                                //        Connection = cnx,

                                //        CommandType = CommandType.Text,

                                //        CommandText = "  UPDATE t set t.[fg_contact_traite] = 1,t.[update_contact] = getdate(),seg_name='Fidélisation' from[RelevanC].[dbo].[Target] t  inner join(select Coupon_ID,[Campaign ID], Tag_description from[RelevanC].[dbo].[Product_offers] with(nolock))  prod on t.Coupon_ID = prod.Coupon_ID  where t.Customer = '" + cc + "'"

                                //    };

​

                                //    sqlCommand1.ExecuteNonQuery();

​

                                //    #endregion

                                //}

​

                            }

                        }

​

​

                    }

​

                    catch (Exception ex)

                    {

                        MessageBox.Show(ex.Message + " _Fidélisation");

                    }

​

                    #region update sql

                    //2021-12-21

                    /* SqlCommand sqlCommand = new SqlCommand()

                     {

                         Connection = cnx,

                         CommandType = CommandType.Text,

                         CommandText = "  UPDATE [RelevanC].[dbo].[Campaign] set [flag_generation] = 1 where [Campaign ID] ='" + c + "'"

                     };

​

                     sqlCommand.ExecuteNonQuery();*/

​

                    #endregion

                }

​

​

                // attrition 

                //foreach (var c in campagnes)

                //{

                //    //var listContact = segments.Where(x => x.Campagne == c).Select(x => x.ContactId.ToUpper()).ToList();

                //var segmentname = segments.Where(x => x.Campagne == c).Select(x => x.Name).FirstOrDefault();

                //var listcustomer = segments.Where(x => x.Campagne == c).Select(x => x.Customer).ToList();

​

                ////MessageBox.Show("count ="+listContact.Count+ " "+ listContact.Distinct().Count());

​

                //QueryExpression qx = new QueryExpression();

                //qx.EntityName = "msdyncrm_segment";

                //qx.Criteria.AddCondition("msdyncrm_segmentname", ConditionOperator.Like, "%" + segmentname + "_Attrition%");

                //EntityCollection coll = organizationservice.RetrieveMultiple(qx);

                //Guid segmentid = Guid.Empty;

                //if (coll.Entities.Count < 1)

                //{

                //    Entity segment = new Entity("msdyncrm_segment");

                //    segment.Attributes["msdyncrm_segmentname"] = segmentname + "_Attrition";

                //    segment.Attributes["msdyncrm_segmenttype"] = new OptionSetValue(192350001);

                //    segment.Attributes["statuscode"] = new OptionSetValue(192350000);

​

                //    segmentid = organizationservice.Create(segment);

                //}

                //else

                //{

                //    segmentid = coll.Entities[0].Id;

                //}

​

​

                //var iteration = Math.Ceiling((double)((double)listContact.Count / 1000));

                //var skip = 0;

                //var take = 1000;

                //if (iteration == 0 && listContact.Count > 1)

                //    iteration = 1;

                //for (int i = 0; i < iteration; i++)

                //{

​

                //var contactToAdd = listContact.Skip(skip).Take(take).Where(x => x != "NULL").ToArray();

                //var customeradd = listcustomer.Skip(skip).Take(take).Where(x => x != "NULL").ToArray();

                //OrganizationRequest req = new OrganizationRequest("msdyncrm_SegmentMembersUpdate");

                //req["msdyncrm_segmentid"] = segmentid.ToString();

                //req["msdyncrm_operation"] = "addByIds";

                //req["msdyncrm_memberids"] = Newtonsoft.Json.JsonConvert.SerializeObject(contactToAdd);

                //var result = organizationservice.Execute(req);

                ////2021-12-21

                ///* foreach (var cc in customeradd)

                // {

                //     #region update sql

​

                //     SqlCommand sqlCommand1 = new SqlCommand()

                //     {

                //         Connection = cnx,

                //         CommandType = CommandType.Text,

                //         // CommandText = "  UPDATE [RelevanC].[dbo].[Target] set [fg_contact_traite] = 1 where [Customer]='" + cc + "'"

                //         CommandText = "  UPDATE t set t.[fg_contact_traite] = 1 ,t.[update_contact] = getdate(),seg_name='Attrition' from[RelevanC].[dbo].[Target] t  inner join(select Coupon_ID,[Campaign ID], Tag_description from[RelevanC].[dbo].[Product_offers] with(nolock))  prod on t.Coupon_ID = prod.Coupon_ID  where t.Customer = '" + cc + "'"

                //     };

​

                //     sqlCommand1.ExecuteNonQuery();

​

                //     #endregion

                // }*/

                ////MessageBox.Show(Newtonsoft.Json.JsonConvert.SerializeObject(result.Results.Values.ToArray()));

                ////OrganizationRequest req1 = new OrganizationRequest("msdyncrm_SegmentMembersUpdate");

                ////req1["msdyncrm_segmentid"] = segmentid.ToString();

                ////req1["msdyncrm_operation"] = "getState";

                ////MessageBox.Show(Newtonsoft.Json.JsonConvert.SerializeObject(contactToAdd));

                ////var resultset = organizationservice.Execute(req1);

                ////flag

                //skip = skip + take;

​

​

                //    }

                //    // MessageBox.Show("Skip: " + skip);

                //    #region update sql

​

                //    SqlCommand sqlCommand = new SqlCommand()

                //    {

                //        Connection = cnx,

                //        CommandType = CommandType.Text,

                //        CommandText = "  UPDATE [RelevanC].[dbo].[Campaign] set [flag_generation] = 1 where [Campaign ID] ='" + c + "'"

                //    };

​

                //    sqlCommand.ExecuteNonQuery();

​

                //    #endregion

                //}

                //}

                //catch (Exception e)

                //{

                //    MessageBox.Show(e.Message + " " + e.StackTrace);

                //    MessageBox.Show(e.Message + " _Attrition");

                //    //ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12;

​

​

                //    //IServiceManagement<IOrganizationService> orgServiceManagement = ServiceConfigurationFactory.CreateManagement<IOrganizationService>(new Uri(crmurl));

​

                //    //AuthenticationCredentials authCredentials = new AuthenticationCredentials();

                //    //authCredentials.ClientCredentials.UserName.UserName = crmuser;

                //    //authCredentials.ClientCredentials.UserName.Password = crmpass;

                //    //AuthenticationCredentials tokenCredentials = orgServiceManagement.Authenticate(authCredentials);

​

                //    //OrganizationServiceProxy organizationProxy = new OrganizationServiceProxy(orgServiceManagement, tokenCredentials.SecurityTokenResponse);

                //    //organizationProxy.EnableProxyTypes();

                //    //organizationservice = (IOrganizationService)organizationProxy;

​

            }

            catch (Exception e)

            {

                MessageBox.Show(e.Message.ToString());

​

            }

​

            Dts.TaskResult = (int)ScriptResults.Success;

        }

​

​

        #region ScriptResults declaration

        /// <summary>

        /// This enum provides a convenient shorthand within the scope of this class for setting the

        /// result of the script.

        /// 

        /// This code was generated automatically.

        /// </summary>

        enum ScriptResults

        {

            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,

            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure

        };

        #endregion

​

        class Segment

        {

            public string Campagne { get; set; }

            public string ContactId { get; set; }

            public string Name { get; set; }

​

            public string Customer { get; set; }

        }

    }

}