Hi All,

I am using below code to create large no of case records like 10K+ & after that one more entity record like interview & then Action record.

But due to timeout issue not sure its sometimes skipping creating cases & going to different method to create interview entity records & Action records.

What could be the cause its going out from the loop in executemultiplerequests method batchwise it will create 1000 records from the requested list.

getting issue in logg file The remote server returned an unexpected response 502 Bad gateway. & its skipping creation of rest case records for some duration & going for create Interviews & so on.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using Microsoft.Xrm.Sdk;

using Microsoft.Xrm.Sdk.Client;

using Microsoft.Xrm.Sdk.Messages;

using Microsoft.Xrm.Sdk.Metadata;

using Microsoft.Xrm.Tooling.Connector;

using System.ServiceModel.Description;

using System.Configuration;

using Microsoft.Xrm.Sdk.Query;

using Microsoft.Crm.Sdk.Messages;

using Microsoft.Office.Interop.Excel;

using System.Net;

using NLog;

using System.IO;

namespace ConsoleApplication1

{

class Program

{

public const int BATCH_SIZE = 300;// Controls how many items get passed into an ExecuteMultipleRequest

private static Logger logger = LogManager.GetCurrentClassLogger();

public static string fileName = DateTime.Now.ToString("dd-MM-yyyy-HH-mm-ss") + "Prod";

static void Main(string[] args)

{

IOrganizationService _crmService = ConnectToCRM();

if (_crmService != null)

{

Guid userid = ((WhoAmIResponse)_crmService.Execute(new WhoAmIRequest())).UserId;

if (userid != Guid.Empty)

{

Console.WriteLine("Connection Established Successfully");

Get_and_CreateCRMRecord(_crmService);

//CreateInterviews(_crmService); //Creating Interview Records

//CreateActions(_crmService); //Creating Action Records

}

}

}

public static void Get_and_CreateCRMRecord(IOrganizationService Service)

{

Application excelApp = new Application();

if (excelApp == null)

{

Console.WriteLine("Excel is not installed!!");

return;

}

Workbook excelBook = excelApp.Workbooks.Open(@"C:\_DO_NOT_BACKUP\MigrationCCMToPP1GC_8thSep2021\9thSep\CCM- Case MigrationSepFinal.xlsx");

_Worksheet excelSheet = excelBook.Sheets[1];

Range excelRange = excelSheet.UsedRange;

int rows = excelRange.Rows.Count;

int cols = excelRange.Columns.Count;

ExecuteMultipleRequest executeMultipleRequest = CreateExecuteMultipleRequest();

// List<Entity> listEntity = new List<Entity>();

for (int i = 1; i <= rows; i++)

{
Entity incident = new Entity("incident");

if (i >= 2)

{

for (int j = 1; j <= cols; j++)

{

incident["mdc_ismigrated"] = new OptionSetValue(755040000); //Yes

try

{

switch (j)

{

case 1:

string Casecode = Convert.ToString(excelRange.Cells[i, j].Value2);

incident["ticketnumber"] = !string.IsNullOrEmpty(Casecode) ? Casecode : string.Empty;

break;

//2 CreatedOn NA

case 3:

string Casefindings = Convert.ToString(excelRange.Cells[i, j].Value2);

incident["mdc_casefindings"] = !string.IsNullOrEmpty(Casefindings) ? Casefindings : string.Empty;

break;

case 4:

//incident["description"] = string.Empty;

string CaseSummary = Convert.ToString(excelRange.Cells[i, j].Value2);

incident["description"] = !string.IsNullOrEmpty(CaseSummary) ? CaseSummary : string.Empty;

break;

//5 Industry/Sector need to look

case 5:

//incident["description"] = string.Empty;

incident["mdc_industrysector"] = null;

string industrysector = Convert.ToString(excelRange.Cells[i, j].Value2);//.ToString();

if (!string.IsNullOrEmpty(industrysector))

{

switch (industrysector.ToLower())

{

case "aerospace":

incident["mdc_industrysector"] = new OptionSetValue(755040000);

break;

case "banking":

incident["mdc_industrysector"] = new OptionSetValue(755040001);

break;

case "charity":

incident["mdc_industrysector"] = new OptionSetValue(755040002);

break;

case "construction":

incident["mdc_industrysector"] = new OptionSetValue(755040003);

break;

case "defense":

incident["mdc_industrysector"] = new OptionSetValue(755040004);

break;

case "education":

incident["mdc_industrysector"] = new OptionSetValue(755040005);

break;

case "energy":

incident["mdc_industrysector"] = new OptionSetValue(755040006);

break;

case "financial services":

incident["mdc_industrysector"] = new OptionSetValue(755040007);

break;

case "government entity":

incident["mdc_industrysector"] = new OptionSetValue(755040008);

break;

case "healthcare services":

incident["mdc_industrysector"] = new OptionSetValue(755040009);

break;

case "investment":

incident["mdc_industrysector"] = new OptionSetValue(755040010);

break;

case "manufacturing":

incident["mdc_industrysector"] = new OptionSetValue(755040011);

break;

case "media":

incident["mdc_industrysector"] = new OptionSetValue(755040012);

break;

case "other":

incident["mdc_industrysector"] = new OptionSetValue(755040013);

break;

case "product supply":

incident["mdc_industrysector"] = new OptionSetValue(755040014);

break;

case "professional services":

incident["mdc_industrysector"] = new OptionSetValue(755040015);

break;

case "real estate":

incident["mdc_industrysector"] = new OptionSetValue(755040016);

break;

default:

break;

}

}

break;

case 6:

string IntakeSummary = Convert.ToString(excelRange.Cells[i, j].Value2);

incident["description"] += !string.IsNullOrEmpty(IntakeSummary) ? " " + IntakeSummary : string.Empty;

break;

case 9:

string Title = Convert.ToString(excelRange.Cells[i, j].Value2);

incident["mdc_reportertitle"] = !string.IsNullOrEmpty(Title) ? Title : string.Empty;

break;

case 10:

incident["mdc_reportername"] = null;

string Email = Convert.ToString(excelRange.Cells[i, j].Value2);

if (!string.IsNullOrEmpty(Email))

{

// incident["emailaddress"] = Email;

QueryExpression query = new QueryExpression("contact");

query.ColumnSet = new ColumnSet("emailaddress1", "mdc_title", "mobilephone", "mdc_company", "mdc_department", "mdc_linemanager");

query.Criteria.AddCondition("emailaddress1", ConditionOperator.Equal, Email);

try

{

//EntityCollection col = Service.RetrieveMultiple(query);

var col = RetrieveAllRecords(Service, query);

if (col.Count > 0)

{

foreach (Entity Eninci in col)

{

incident["mdc_reportername"] = new EntityReference("contact", Eninci.Id);

incident["customerid"] = new EntityReference("contact", Eninci.Id);

var conEmailAdd = Eninci.Attributes.Contains("emailaddress1") ? Eninci.GetAttributeValue<string>("emailaddress1") : string.Empty;

incident["mdc_reporteremailaddress"] = !string.IsNullOrEmpty(conEmailAdd) ? conEmailAdd : string.Empty;

var contitle = Eninci.Attributes.Contains("mdc_title") ? Eninci.GetAttributeValue<string>("mdc_title") : string.Empty;

incident["mdc_reportertitle"] = !string.IsNullOrEmpty(contitle) ? contitle : string.Empty;

var conMobilePhone = Eninci.Attributes.Contains("mobilephone") ? Eninci.GetAttributeValue<string>("mobilephone") : string.Empty;

incident["mdc_contactnumber"] = !string.IsNullOrEmpty(conMobilePhone) ? conMobilePhone : string.Empty;

incident["mdc_reporterphonenumber"] = !string.IsNullOrEmpty(conMobilePhone) ? conMobilePhone : string.Empty;

var conCompany = Eninci.Attributes.Contains("mdc_company") ? Eninci.GetAttributeValue<string>("mdc_company") : string.Empty;

incident["mdc_reportercompany"] = !string.IsNullOrEmpty(conCompany) ? conCompany : string.Empty;

var conDept = Eninci.Attributes.Contains("mdc_department") ? Eninci.GetAttributeValue<string>("mdc_department") : string.Empty;

incident["mdc_reporterdepartment"] = !string.IsNullOrEmpty(conDept) ? conDept : string.Empty;

var conLineManager = Eninci.Attributes.Contains("mdc_linemanager") ? Eninci.GetAttributeValue<string>("mdc_linemanager") : string.Empty;

incident["mdc_linemanagername"] = !string.IsNullOrEmpty(conLineManager) ? conLineManager : string.Empty;

}

}

else

{

string ReporterName = Convert.ToString(excelRange.Cells[i, 7].Value2);

string ExReporterName = Convert.ToString(excelRange.Cells[i, 8].Value2);

if (!string.IsNullOrEmpty(ReporterName))

{

QueryExpression conQuery = new QueryExpression("contact");

conQuery.ColumnSet = new ColumnSet("fullname", "emailaddress1", "mdc_title", "mobilephone", "mdc_company", "mdc_department");

conQuery.Criteria.AddCondition("fullname", ConditionOperator.Equal, ReporterName);

//EntityCollection contactCol1 = Service.RetrieveMultiple(conQuery);

var contactCol1 = RetrieveAllRecords(Service, conQuery);

if (contactCol1.Count > 0)

{

foreach (Entity Eninci1 in contactCol1)

{

incident["mdc_reportername"] = new EntityReference("contact", Eninci1.Id);

incident["customerid"] = new EntityReference("contact", Eninci1.Id);

var conEmailAdd = Eninci1.Attributes.Contains("emailaddress1") ? Eninci1.GetAttributeValue<string>("emailaddress1") : string.Empty;

incident["mdc_reporteremailaddress"] = !string.IsNullOrEmpty(conEmailAdd) ? conEmailAdd : string.Empty;

var contitle = Eninci1.Attributes.Contains("mdc_title") ? Eninci1.GetAttributeValue<string>("mdc_title") : string.Empty;

incident["mdc_reportertitle"] = !string.IsNullOrEmpty(contitle) ? contitle : string.Empty;

var conMobilePhone = Eninci1.Attributes.Contains("mobilephone") ? Eninci1.GetAttributeValue<string>("mobilephone") : string.Empty;

incident["mdc_contactnumber"] = !string.IsNullOrEmpty(conMobilePhone) ? conMobilePhone : string.Empty;

incident["mdc_reporterphonenumber"] = !string.IsNullOrEmpty(conMobilePhone) ? conMobilePhone : string.Empty;

var conCompany = Eninci1.Attributes.Contains("mdc_company") ? Eninci1.GetAttributeValue<string>("mdc_company") : string.Empty;

incident["mdc_reportercompany"] = !string.IsNullOrEmpty(conCompany) ? conCompany : string.Empty;

var conDept = Eninci1.Attributes.Contains("mdc_department") ? Eninci1.GetAttributeValue<string>("mdc_department") : string.Empty;

incident["mdc_reporterdepartment"] = !string.IsNullOrEmpty(conDept) ? conDept : string.Empty;

}

}

else

{

var newRN = ReporterName.Split(' ');

string firstName = newRN[0];

string lastName = ReporterName.Remove(0, ReporterName.IndexOf(' ') + 1);

// string lastName = exInterview[1];

Entity reporter = new Entity("contact");

reporter["firstname"] = !string.IsNullOrEmpty(firstName) ? firstName : string.Empty;

reporter["lastname"] = !string.IsNullOrEmpty(lastName) ? lastName : string.Empty;

reporter["emailaddress1"] = !string.IsNullOrEmpty(Email) ? Email : string.Empty;

Service.Create(reporter);

QueryExpression qe = new QueryExpression();

qe.EntityName = "contact";

qe.ColumnSet = new ColumnSet("emailaddress1");

qe.Criteria = new FilterExpression();

qe.Criteria.AddCondition("emailaddress1", ConditionOperator.Equal, Email);

//EntityCollection ec = Service.RetrieveMultiple(qe);

var ec = RetrieveAllRecords(Service, qe);

if (ec.Count > 0)

{

foreach (var con in ec)

{

incident["customerid"] = new EntityReference("contact", con.Id);

incident["mdc_reportername"] = new EntityReference("contact", con.Id);

incident["mdc_reporteremailaddress"] = Email;

}

}

}

}

else if (!string.IsNullOrEmpty(ExReporterName))

{

QueryExpression conQuery = new QueryExpression("contact");

conQuery.ColumnSet = new ColumnSet("fullname", "emailaddress1", "mdc_title", "mobilephone", "mdc_company", "mdc_department");

conQuery.Criteria.AddCondition("fullname", ConditionOperator.Equal, ExReporterName);

//EntityCollection contactCol1 = Service.RetrieveMultiple(conQuery);

var contactCol1 = RetrieveAllRecords(Service, conQuery);

if (contactCol1?.Count > 0)

{

foreach (Entity Eninci1 in contactCol1)

{

incident["mdc_reportername"] = new EntityReference("contact", Eninci1.Id);

incident["customerid"] = new EntityReference("contact", Eninci1.Id);

var conEmailAdd = Eninci1.Attributes.Contains("emailaddress1") ? Eninci1.GetAttributeValue<string>("emailaddress1") : string.Empty;

incident["mdc_reporteremailaddress"] = !string.IsNullOrEmpty(conEmailAdd) ? conEmailAdd : string.Empty;

var contitle = Eninci1.Attributes.Contains("mdc_title") ? Eninci1.GetAttributeValue<string>("mdc_title") : string.Empty;

incident["mdc_reportertitle"] = !string.IsNullOrEmpty(contitle) ? contitle : string.Empty;

var conMobilePhone = Eninci1.Attributes.Contains("mobilephone") ? Eninci1.GetAttributeValue<string>("mobilephone") : string.Empty;

incident["mdc_contactnumber"] = !string.IsNullOrEmpty(conMobilePhone) ? conMobilePhone : string.Empty;

incident["mdc_reporterphonenumber"] = !string.IsNullOrEmpty(conMobilePhone) ? conMobilePhone : string.Empty;

var conCompany = Eninci1.Attributes.Contains("mdc_company") ? Eninci1.GetAttributeValue<string>("mdc_company") : string.Empty;

incident["mdc_reportercompany"] = !string.IsNullOrEmpty(conCompany) ? conCompany : string.Empty;

var conDept = Eninci1.Attributes.Contains("mdc_department") ? Eninci1.GetAttributeValue<string>("mdc_department") : string.Empty;

incident["mdc_reporterdepartment"] = !string.IsNullOrEmpty(conDept) ? conDept : string.Empty;

}

}

else

{

var newExRN = ExReporterName.Split(' ');

string firstName = newExRN[0];

string lastName = ExReporterName.Remove(0, ExReporterName.IndexOf(' ') + 1);

// string lastName = exInterview[1];

Entity reporter1 = new Entity("contact");

reporter1["firstname"] = !string.IsNullOrEmpty(firstName) ? firstName : string.Empty;

reporter1["lastname"] = !string.IsNullOrEmpty(lastName) ? lastName : string.Empty;

reporter1["emailaddress1"] = !string.IsNullOrEmpty(Email) ? Email : string.Empty;

Service.Create(reporter1);

QueryExpression qe1 = new QueryExpression();

qe1.EntityName = "contact";

qe1.ColumnSet = new ColumnSet("emailaddress1");

qe1.Criteria = new FilterExpression();

qe1.Criteria.AddCondition("emailaddress1", ConditionOperator.Equal, Email);

//EntityCollection ec1 = Service.RetrieveMultiple(qe1);

var ec1 = RetrieveAllRecords(Service, qe1);

if (ec1.Count > 0)

{

foreach (var con in ec1)

{

incident["customerid"] = new EntityReference("contact", con.Id);

incident["mdc_reportername"] = new EntityReference("contact", con.Id);

incident["mdc_reporteremailaddress"] = Email;

}

}

}

}

}

}

catch (Exception ex)

{

throw new Exception(ex.ToString());

}

}

else

{

string ReporterName = Convert.ToString(excelRange.Cells[i, 7].Value2);

string ExReporterName = Convert.ToString(excelRange.Cells[i, 8].Value2);

if (!string.IsNullOrEmpty(ReporterName))

{

QueryExpression conQuery = new QueryExpression("contact");

conQuery.ColumnSet = new ColumnSet("fullname", "emailaddress1", "mdc_title", "mobilephone", "mdc_company", "mdc_department");

conQuery.Criteria.AddCondition("fullname", ConditionOperator.Equal, ReporterName);

//EntityCollection contactCol1 = Service.RetrieveMultiple(conQuery);

var contactCol1 = RetrieveAllRecords(Service, conQuery);

if (contactCol1?.Count > 0)

{

foreach (Entity Eninci1 in contactCol1)

{

incident["mdc_reportername"] = new EntityReference("contact", Eninci1.Id);

incident["customerid"] = new EntityReference("contact", Eninci1.Id);

var conEmailAdd = Eninci1.Attributes.Contains("emailaddress1") ? Eninci1.GetAttributeValue<string>("emailaddress1") : string.Empty;

incident["mdc_reporteremailaddress"] = !string.IsNullOrEmpty(conEmailAdd) ? conEmailAdd : string.Empty;

var contitle = Eninci1.Attributes.Contains("mdc_title") ? Eninci1.GetAttributeValue<string>("mdc_title") : string.Empty;

incident["mdc_reportertitle"] = !string.IsNullOrEmpty(contitle) ? contitle : string.Empty;

var conMobilePhone = Eninci1.Attributes.Contains("mobilephone") ? Eninci1.GetAttributeValue<string>("mobilephone") : string.Empty;

incident["mdc_contactnumber"] = !string.IsNullOrEmpty(conMobilePhone) ? conMobilePhone : string.Empty;

incident["mdc_reporterphonenumber"] = !string.IsNullOrEmpty(conMobilePhone) ? conMobilePhone : string.Empty;

var conCompany = Eninci1.Attributes.Contains("mdc_company") ? Eninci1.GetAttributeValue<string>("mdc_company") : string.Empty;

incident["mdc_reportercompany"] = !string.IsNullOrEmpty(conCompany) ? conCompany : string.Empty;

var conDept = Eninci1.Attributes.Contains("mdc_department") ? Eninci1.GetAttributeValue<string>("mdc_department") : string.Empty;

incident["mdc_reporterdepartment"] = !string.IsNullOrEmpty(conDept) ? conDept : string.Empty;

}

}

else

{

var newRN = ReporterName.Split(' ');

string firstName = newRN[0];

string lastName = ReporterName.Remove(0, ReporterName.IndexOf(' ') + 1);

// string lastName = exInterview[1];

Entity reporter = new Entity("contact");

reporter["firstname"] = !string.IsNullOrEmpty(firstName) ? firstName : string.Empty;

reporter["lastname"] = !string.IsNullOrEmpty(lastName) ? lastName : string.Empty;

reporter["emailaddress1"] = !string.IsNullOrEmpty(Email) ? Email : string.Empty;

Service.Create(reporter);

QueryExpression qe = new QueryExpression();

qe.EntityName = "contact";

qe.ColumnSet = new ColumnSet("emailaddress1", "fullname");

qe.Criteria = new FilterExpression();

if (!string.IsNullOrEmpty(Email))

qe.Criteria.AddCondition("emailaddress1", ConditionOperator.Equal, Email);

else

qe.Criteria.AddCondition("fullname", ConditionOperator.Equal, ReporterName);

//EntityCollection ec = Service.RetrieveMultiple(qe);

var ec = RetrieveAllRecords(Service, qe);

if (ec?.Count > 0)

{

foreach (var con in ec)

{

incident["customerid"] = new EntityReference("contact", con.Id);

incident["mdc_reportername"] = new EntityReference("contact", con.Id);

}

}

}

}

else if (!string.IsNullOrEmpty(ExReporterName))

{

QueryExpression conQuery = new QueryExpression("contact");

conQuery.ColumnSet = new ColumnSet("fullname", "emailaddress1", "mdc_title", "mobilephone", "mdc_company", "mdc_department");

conQuery.Criteria.AddCondition("fullname", ConditionOperator.Equal, ExReporterName);

//EntityCollection contactCol1 = Service.RetrieveMultiple(conQuery);

var contactCol1 = RetrieveAllRecords(Service, conQuery);

if (contactCol1.Count > 0)

{

foreach (Entity Eninci1 in contactCol1)

{

incident["mdc_reportername"] = new EntityReference("contact", Eninci1.Id);

incident["customerid"] = new EntityReference("contact", Eninci1.Id);

var conEmailAdd = Eninci1.Attributes.Contains("emailaddress1") ? Eninci1.GetAttributeValue<string>("emailaddress1") : string.Empty;

incident["mdc_reporteremailaddress"] = !string.IsNullOrEmpty(conEmailAdd) ? conEmailAdd : string.Empty;

var contitle = Eninci1.Attributes.Contains("mdc_title") ? Eninci1.GetAttributeValue<string>("mdc_title") : string.Empty;

incident["mdc_reportertitle"] = !string.IsNullOrEmpty(contitle) ? contitle : string.Empty;

var conMobilePhone = Eninci1.Attributes.Contains("mobilephone") ? Eninci1.GetAttributeValue<string>("mobilephone") : string.Empty;

incident["mdc_contactnumber"] = !string.IsNullOrEmpty(conMobilePhone) ? conMobilePhone : string.Empty;

incident["mdc_reporterphonenumber"] = !string.IsNullOrEmpty(conMobilePhone) ? conMobilePhone : string.Empty;

var conCompany = Eninci1.Attributes.Contains("mdc_company") ? Eninci1.GetAttributeValue<string>("mdc_company") : string.Empty;

incident["mdc_reportercompany"] = !string.IsNullOrEmpty(conCompany) ? conCompany : string.Empty;

var conDept = Eninci1.Attributes.Contains("mdc_department") ? Eninci1.GetAttributeValue<string>("mdc_department") : string.Empty;

incident["mdc_reporterdepartment"] = !string.IsNullOrEmpty(conDept) ? conDept : string.Empty;

}

}

else

{

var newExRN = ExReporterName.Split(' ');

string firstName = newExRN[0];

string lastName = ExReporterName.Remove(0, ExReporterName.IndexOf(' ') + 1);

// string lastName = exInterview[1];

Entity reporter1 = new Entity("contact");

reporter1["firstname"] = !string.IsNullOrEmpty(firstName) ? firstName : string.Empty;

reporter1["lastname"] = !string.IsNullOrEmpty(lastName) ? lastName : string.Empty;

reporter1["emailaddress1"] = !string.IsNullOrEmpty(Email) ? Email : string.Empty;

Service.Create(reporter1);

QueryExpression qe1 = new QueryExpression();

qe1.EntityName = "contact";

qe1.ColumnSet = new ColumnSet("emailaddress1", "fullname");

qe1.Criteria = new FilterExpression();

if (!string.IsNullOrEmpty(Email))

qe1.Criteria.AddCondition("emailaddress1", ConditionOperator.Equal, Email);

else

qe1.Criteria.AddCondition("fullname", ConditionOperator.Equal, ExReporterName);

//EntityCollection ec1 = Service.RetrieveMultiple(qe1);

var ecoll1 = RetrieveAllRecords(Service, qe1);

if (ecoll1?.Count > 0)

{

foreach (var con in ecoll1)

{

incident["customerid"] = new EntityReference("contact", con.Id);

incident["mdc_reportername"] = new EntityReference("contact", con.Id);

}

}

}

}

}

break;

case 11:

string mobilenumber = Convert.ToString(excelRange.Cells[i, j].Value2);

incident["mdc_reporterphonenumber"] = !string.IsNullOrEmpty(mobilenumber) ? mobilenumber : string.Empty;

break;

case 12:

incident["mdc_dateclosed"] = null;

string dateclosed = Convert.ToString(excelRange.Cells[i, j].Value2);

if (dateclosed != "Unclassified")

{

try

{

double date = double.Parse(dateclosed);

var dateTime1 = DateTime.FromOADate(date).ToString("MMMM dd, yyyy");

DateTime dt = Convert.ToDateTime(dateTime1);

String Converteddate = dt.ToShortDateString();

incident["mdc_dateclosed"] = dt;

}

catch (Exception ex)

{

continue;

}

}

break;

case 13:

// incident["mdc_daystoclose"] = string.Empty;

string Daystoclose = Convert.ToString(excelRange.Cells[i, j].Value2);

int? daystoclosenew = null;

daystoclosenew = Int32.Parse(Daystoclose);

if (daystoclosenew != null)

incident["mdc_daystoclose"] = daystoclosenew;

break;

case 14:

incident["mdc_datereported"] = null;

string datereported = Convert.ToString(excelRange.Cells[i, j].Value2);

if (datereported != "Unclassified")

{

try

{

double date = double.Parse(datereported);

var dateTime1 = DateTime.FromOADate(date).ToString("MMMM dd, yyyy");

DateTime dt = Convert.ToDateTime(dateTime1);

String Converteddate = dt.ToShortDateString();

incident["mdc_datereported"] = dt;

}

catch (Exception ex)

{

continue;

}

}

break;

case 15:

string categorytype = string.Empty;//.ToString();

if (!string.IsNullOrEmpty(Convert.ToString(excelRange.Cells[i, j].Value2)))

{

categorytype = GetGCColumn(Convert.ToString(excelRange.Cells[i, j].Value2));

QueryExpression query = new QueryExpression("mdc_categorytype");

query.Criteria.AddCondition("mdc_name", ConditionOperator.Equal, categorytype);

try

{

EntityCollection col = Service.RetrieveMultiple(query);

if (col.Entities.Count > 0)

{

foreach (Entity Eninci in col.Entities)

{

incident["mdc_interactiontype"] = new EntityReference("mdc_categorytype", Eninci.Id);

}

}

}

catch (Exception ex)

{

throw new Exception(ex.ToString());

}

}

break;

case 16:

if (!string.IsNullOrEmpty(Convert.ToString(excelRange.Cells[i, j].Value2)))

{

string Primaryriskarea = Convert.ToString(excelRange.Cells[i, j].Value2);

QueryExpression query = new QueryExpression("mdc_subriskarea");

query.Criteria.AddCondition("mdc_name", ConditionOperator.Equal, Primaryriskarea);

try

{

EntityCollection col = Service.RetrieveMultiple(query);

if (col?.Entities?.Count > 0)

{

foreach (Entity Eninci in col.Entities)

{

incident["mdc_subriskarea"] = new EntityReference("mdc_subriskarea", Eninci.Id);

}

}

else

{

Entity subRiskArea = new Entity("mdc_subriskarea");

if (!string.IsNullOrEmpty(Primaryriskarea))

subRiskArea["mdc_name"] = Primaryriskarea;

subRiskArea["mdc_ismigrated"] = new OptionSetValue(755040000);//Set Yes only for migrated records

Service.Create(subRiskArea);

QueryExpression qe = new QueryExpression();

qe.EntityName = "mdc_subriskarea";

qe.ColumnSet = new ColumnSet("mdc_name");

qe.Criteria = new FilterExpression();

if (!string.IsNullOrEmpty(Primaryriskarea))

qe.Criteria.AddCondition("mdc_name", ConditionOperator.Equal, Primaryriskarea);

EntityCollection ec = Service.RetrieveMultiple(qe);

if (ec?.Entities?.Count > 0)

{

foreach (Entity Eninci in ec.Entities)

{

incident["mdc_subriskarea"] = new EntityReference("mdc_subriskarea", Eninci.Id);

}

}

}

}

catch (Exception ex)

{

throw new Exception(ex.ToString());

}

}

break;

case 17:

incident["mdc_disclosure"] = null;

string disclousre = Convert.ToString(excelRange.Cells[i, j].Value2);

if (!string.IsNullOrEmpty(disclousre))

{

switch (disclousre.ToLower())

{

case "pre-approval":

incident["mdc_disclosure"] = new OptionSetValue(755040000);

break;

case "post-approval":

incident["mdc_disclosure"] = new OptionSetValue(755040001);

break;

default:

break;

}

}

break;

case 18:

string countryparty = Convert.ToString(excelRange.Cells[i, j].Value2);

if (!string.IsNullOrEmpty(countryparty))

{

switch (countryparty.ToLower())

{

case "goverment":

incident["mdc_counterpartytype"] = new OptionSetValue(1);

break;

case "agent":

incident["mdc_counterpartytype"] = new OptionSetValue(2);

break;

case "consultant/advisor":

incident["mdc_counterpartytype"] = new OptionSetValue(3);

break;

case "customer":

incident["mdc_counterpartytype"] = new OptionSetValue(4);

break;

case "distributor":

incident["mdc_counterpartytype"] = new OptionSetValue(5);

break;

case "partner":

incident["mdc_counterpartytype"] = new OptionSetValue(6);

break;

case "intermediary":

incident["mdc_counterpartytype"] = new OptionSetValue(7);

break;

case "supplier/vendor":

incident["mdc_counterpartytype"] = new OptionSetValue(8);

break;

case "other":

incident["mdc_counterpartytype"] = new OptionSetValue(9);

break;

default:

break;

}

}

break;

case 19:

string Nameofthecounterparty = Convert.ToString(excelRange.Cells[i, j].Value2);

incident["mdc_counterpartyorganization"] = !string.IsNullOrEmpty(Nameofthecounterparty) ? Nameofthecounterparty : string.Empty;

break;

case 21:

incident["mdc_targetemailaddress"] = string.Empty;

string targettype = Convert.ToString(excelRange.Cells[i, j].Value2);//.ToString();

if (!string.IsNullOrEmpty(targettype))

{

if (targettype.ToLower() == "person")

{

incident["mdc_targetcompany"] = string.Empty;

incident["mdc_targettype"] = new OptionSetValue(755040001);

//if from excel j23 or 24 if any of those 2 field not blank keep in target company

string TargetEmailAddress = Convert.ToString(excelRange.Cells[i, 29].Value2);

if (!string.IsNullOrEmpty(TargetEmailAddress))

{

incident["mdc_targetemailaddress"] = TargetEmailAddress;

QueryExpression query = new QueryExpression("contact");

query.Criteria.AddCondition("emailaddress1", ConditionOperator.Equal, TargetEmailAddress);

try

{

//EntityCollection col = Service.RetrieveMultiple(query);

var col = RetrieveAllRecords(Service, query);

if (col?.Count > 0)

{

foreach (Entity Eninci in col)

{

var contactColl = Service.Retrieve("contact", Eninci.Id, new ColumnSet("fullname"));

var fullName = contactColl["fullname"].ToString();

incident["mdc_targetcompany"] = !string.IsNullOrEmpty(fullName) ? fullName : string.Empty;

}

}

else

{

string TargetNameAd = Convert.ToString(excelRange.Cells[i, 23].Value2);

string TargetNameExternal = Convert.ToString(excelRange.Cells[i, 24].Value2);

if (!string.IsNullOrEmpty(TargetNameAd))

{

QueryExpression conQuery = new QueryExpression("contact");

conQuery.Criteria.AddCondition("fullname", ConditionOperator.Equal, TargetNameAd);

//EntityCollection contactCol1 = Service.RetrieveMultiple(conQuery);

var contactCol1 = RetrieveAllRecords(Service, conQuery);

if (contactCol1?.Count > 0)

{

foreach (Entity Eninci1 in contactCol1)

{

var contactColl = Service.Retrieve("contact", Eninci1.Id, new ColumnSet("fullname"));

var fullName = contactColl["fullname"].ToString();

incident["mdc_targetcompany"] = !string.IsNullOrEmpty(fullName) ? fullName : string.Empty;

}

}

else

{

var newRN = TargetNameAd.Split(' ');

string firstName = newRN[0];

string lastName = TargetNameAd.Remove(0, TargetNameAd.IndexOf(' ') + 1);

// string lastName = exInterview[1];

Entity reporter = new Entity("contact");

reporter["firstname"] = !string.IsNullOrEmpty(firstName) ? firstName : string.Empty;

reporter["lastname"] = !string.IsNullOrEmpty(lastName) ? lastName : string.Empty;

reporter["emailaddress1"] = !string.IsNullOrEmpty(TargetEmailAddress) ? TargetEmailAddress : string.Empty;

Service.Create(reporter);

QueryExpression qe = new QueryExpression();

qe.EntityName = "contact";

qe.ColumnSet = new ColumnSet("emailaddress1", "fullname");

qe.Criteria = new FilterExpression();

if (!string.IsNullOrEmpty(TargetEmailAddress))

qe.Criteria.AddCondition("emailaddress1", ConditionOperator.Equal, TargetEmailAddress);

else

qe.Criteria.AddCondition("fullname", ConditionOperator.Equal, TargetNameAd);

//EntityCollection ec = Service.RetrieveMultiple(qe);

var ec = RetrieveAllRecords(Service, qe);

if (ec?.Count > 0)

{

foreach (var con in ec)

{

var contactColl = Service.Retrieve("contact", con.Id, new ColumnSet("fullname"));

var fullName = contactColl["fullname"].ToString();

incident["mdc_targetcompany"] = !string.IsNullOrEmpty(fullName) ? fullName : string.Empty;

//incident["customerid"] = new EntityReference("contact", con.Id);

}

}

}

}

else if (!string.IsNullOrEmpty(TargetNameExternal))

{

QueryExpression conQuery = new QueryExpression("contact");

conQuery.Criteria.AddCondition("fullname", ConditionOperator.Equal, TargetNameExternal);

//EntityCollection contactCol1 = Service.RetrieveMultiple(conQuery);

var contactCol1 = RetrieveAllRecords(Service, conQuery);

if (contactCol1.Count > 0)

{

foreach (Entity Eninci1 in contactCol1)

{

var contactColl = Service.Retrieve("contact", Eninci1.Id, new ColumnSet("fullname"));

var fullName = contactColl["fullname"].ToString();

incident["mdc_targetcompany"] = !string.IsNullOrEmpty(fullName) ? fullName : string.Empty;

}

}

else

{

var newExRN = TargetNameExternal.Split(' ');

string firstName = newExRN[0];

string lastName = TargetNameExternal.Remove(0, TargetNameExternal.IndexOf(' ') + 1);

// string lastName = exInterview[1];

Entity reporter1 = new Entity("contact");

reporter1["firstname"] = firstName;

reporter1["lastname"] = lastName;

if (!string.IsNullOrEmpty(TargetEmailAddress))

reporter1["emailaddress1"] = TargetEmailAddress;

Service.Create(reporter1);

QueryExpression qe1 = new QueryExpression();

qe1.EntityName = "contact";

qe1.ColumnSet = new ColumnSet("emailaddress1", "fullname");

qe1.Criteria = new FilterExpression();

if (!string.IsNullOrEmpty(TargetEmailAddress))

qe1.Criteria.AddCondition("emailaddress1", ConditionOperator.Equal, TargetEmailAddress);

else

qe1.Criteria.AddCondition("fullname", ConditionOperator.Equal, TargetNameExternal);

//EntityCollection ec1 = Service.RetrieveMultiple(qe1);

var ec1 = RetrieveAllRecords(Service, qe1);

if (ec1.Count > 0)

{

foreach (var con1 in ec1)

{

var contactColl = Service.Retrieve("contact", con1.Id, new ColumnSet("fullname"));

var fullName = contactColl["fullname"].ToString();

incident["mdc_targetcompany"] = !string.IsNullOrEmpty(fullName) ? fullName : string.Empty;

//incident["customerid"] = new EntityReference("contact", con.Id);

}

}

}

}

}

}

catch (Exception ex)

{

throw new Exception(ex.ToString());

}

}

//Email Address not available in excel

else

{

string TargetNameAd = Convert.ToString(excelRange.Cells[i, 23].Value2);

string TargetNameExternal = Convert.ToString(excelRange.Cells[i, 24].Value2);

if (!string.IsNullOrEmpty(TargetNameAd))

{

QueryExpression conQuery = new QueryExpression("contact");

conQuery.Criteria.AddCondition("fullname", ConditionOperator.Equal, TargetNameAd);

//EntityCollection contactCol1 = Service.RetrieveMultiple(conQuery);

var contactCol1 = RetrieveAllRecords(Service, conQuery);

if (contactCol1.Count > 0)

{

foreach (Entity Eninci1 in contactCol1)

{

var contactColl = Service.Retrieve("contact", Eninci1.Id, new ColumnSet("fullname"));

var fullName = contactColl["fullname"].ToString();

incident["mdc_targetcompany"] = !string.IsNullOrEmpty(fullName) ? fullName : string.Empty;

}

}

else

{

var newRN = TargetNameAd.Split(' ');

string firstName = newRN[0];

string lastName = TargetNameAd.Remove(0, TargetNameAd.IndexOf(' ') + 1);

// string lastName = exInterview[1];

Entity reporter = new Entity("contact");

reporter["firstname"] = firstName;

reporter["lastname"] = lastName;

if (!string.IsNullOrEmpty(TargetEmailAddress))

reporter["emailaddress1"] = TargetEmailAddress;

Service.Create(reporter);

QueryExpression qe = new QueryExpression();

qe.EntityName = "contact";

qe.ColumnSet = new ColumnSet("emailaddress1", "fullname");

qe.Criteria = new FilterExpression();

if (!string.IsNullOrEmpty(TargetEmailAddress))

qe.Criteria.AddCondition("emailaddress1", ConditionOperator.Equal, TargetEmailAddress);

else

qe.Criteria.AddCondition("fullname", ConditionOperator.Equal, TargetNameAd);

//EntityCollection ec = Service.RetrieveMultiple(qe);

var ec = RetrieveAllRecords(Service, qe);

if (ec.Count > 0)

{

foreach (var con in ec)

{

var contactColl = Service.Retrieve("contact", con.Id, new ColumnSet("fullname"));

var fullName = contactColl["fullname"].ToString();

incident["mdc_targetcompany"] = !string.IsNullOrEmpty(fullName) ? fullName : string.Empty;

//incident["customerid"] = new EntityReference("contact", con.Id);

}

}

}

}

else if (!string.IsNullOrEmpty(TargetNameExternal))

{

QueryExpression conQuery = new QueryExpression("contact");

conQuery.Criteria.AddCondition("fullname", ConditionOperator.Equal, TargetNameExternal);

//EntityCollection contactCol1 = Service.RetrieveMultiple(conQuery);

var contactCol1 = RetrieveAllRecords(Service, conQuery);

if (contactCol1.Count > 0)

{

foreach (Entity Eninci1 in contactCol1)

{

var contactColl = Service.Retrieve("contact", Eninci1.Id, new ColumnSet("fullname"));

var fullName = contactColl["fullname"].ToString();

incident["mdc_targetcompany"] = !string.IsNullOrEmpty(fullName) ? fullName : string.Empty;

}

}

else

{

var newExRN = TargetNameExternal.Split(' ');

string firstName = newExRN[0];

string lastName = TargetNameExternal.Remove(0, TargetNameExternal.IndexOf(' ') + 1);

// string lastName = exInterview[1];

Entity reporter1 = new Entity("contact");

reporter1["firstname"] = firstName;

reporter1["lastname"] = lastName;

if (!string.IsNullOrEmpty(TargetEmailAddress))

reporter1["emailaddress1"] = TargetEmailAddress;

Service.Create(reporter1);

QueryExpression qe1 = new QueryExpression();

qe1.EntityName = "contact";

qe1.ColumnSet = new ColumnSet("emailaddress1", "fullname");

qe1.Criteria = new FilterExpression();

if (!string.IsNullOrEmpty(TargetEmailAddress))

qe1.Criteria.AddCondition("emailaddress1", ConditionOperator.Equal, TargetEmailAddress);

else

qe1.Criteria.AddCondition("fullname", ConditionOperator.Equal, TargetNameExternal);

//EntityCollection ec1 = Service.RetrieveMultiple(qe1);

var ec1 = RetrieveAllRecords(Service, qe1);

if (ec1.Count > 0)

{

foreach (var con1 in ec1)

{

var contactColl = Service.Retrieve("contact", con1.Id, new ColumnSet("fullname"));

var fullName = contactColl["fullname"].ToString();

incident["mdc_targetcompany"] = !string.IsNullOrEmpty(fullName) ? fullName : string.Empty;

//incident["customerid"] = new EntityReference("contact", con.Id);

}

}

}

}

}

}

else if (targettype.ToLower() == "company")

{

incident["mdc_targetcompany"] = string.Empty;

incident["mdc_targettype"] = new OptionSetValue(755040000);

string TargetCompany = Convert.ToString(excelRange.Cells[i, 28].Value2);

if (!string.IsNullOrEmpty(TargetCompany))

incident["mdc_targetcompany"] = TargetCompany;

}

}

break;

case 25:

string TargetTitle = Convert.ToString(excelRange.Cells[i, j].Value2);

incident["mdc_targettitle"] = !string.IsNullOrEmpty(TargetTitle) ? TargetTitle : string.Empty;

break;

case 26:

string TargetAssetCompany = Convert.ToString(excelRange.Cells[i, j].Value2);

incident["mdc_personcompany"] = !string.IsNullOrEmpty(TargetAssetCompany) ? TargetAssetCompany : string.Empty;

break;

case 27:

string TargetBusinessUnit = Convert.ToString(excelRange.Cells[i, j].Value2);

incident["mdc_targetdepartment"] = !string.IsNullOrEmpty(TargetBusinessUnit) ? TargetBusinessUnit : string.Empty;

break;

case 30:

string TargetMobileNumber = Convert.ToString(excelRange.Cells[i, j].Value2);

incident["mdc_targetmobilenumber"] = !string.IsNullOrEmpty(TargetMobileNumber) ? TargetMobileNumber : string.Empty;

break;

case 34:

string RelatedTitle = Convert.ToString(excelRange.Cells[i, j].Value2);

incident["mdc_relativetitle"] = !string.IsNullOrEmpty(RelatedTitle) ? RelatedTitle : string.Empty;

break;

case 35:

string RelatedToMobileNumber = Convert.ToString(excelRange.Cells[i, j].Value2);

incident["mdc_relativemobilenumber"] = !string.IsNullOrEmpty(RelatedToMobileNumber) ? RelatedToMobileNumber : string.Empty;

break;

case 36:

incident["mdc_relativeemailaddress"] = string.Empty;

incident["mdc_relativename"] = null;

string RelatedToEmailAddress = Convert.ToString(excelRange.Cells[i, j].Value2);

if (!string.IsNullOrEmpty(RelatedToEmailAddress))

{

incident["mdc_relativeemailaddress"] = RelatedToEmailAddress;

QueryExpression query = new QueryExpression("contact");

query.Criteria.AddCondition("emailaddress1", ConditionOperator.Equal, RelatedToEmailAddress);

try

{

//EntityCollection col = Service.RetrieveMultiple(query);

var col = RetrieveAllRecords(Service, query);

if (col.Count > 0)

{

foreach (Entity Eninci in col)

{

incident["mdc_relativename"] = new EntityReference("contact", Eninci.Id);

incident["customerid"] = new EntityReference("contact", Eninci.Id);

}

}

else

{

string RelatedtoExternal = Convert.ToString(excelRange.Cells[i, 32].Value2);

string RelatedtoInternal = Convert.ToString(excelRange.Cells[i, 33].Value2);

if (!string.IsNullOrEmpty(RelatedtoExternal))

{

QueryExpression conQuery = new QueryExpression("contact");

conQuery.Criteria.AddCondition("fullname", ConditionOperator.Equal, RelatedtoExternal);

//EntityCollection contactCol1 = Service.RetrieveMultiple(conQuery);

var contactCol1 = RetrieveAllRecords(Service, conQuery);

if (contactCol1.Count > 0)

{

foreach (Entity Eninci1 in contactCol1)

{

incident["mdc_relativename"] = new EntityReference("contact", Eninci1.Id);

}

}

else

{

var newRN = RelatedtoExternal.Split(' ');

string firstName = newRN[0];

string lastName = RelatedtoExternal.Remove(0, RelatedtoExternal.IndexOf(' ') + 1);

// string lastName = exInterview[1];

Entity reporter = new Entity("contact");

reporter["firstname"] = firstName;

reporter["lastname"] = lastName;

if (!string.IsNullOrEmpty(RelatedToEmailAddress))

reporter["emailaddress1"] = RelatedToEmailAddress;

Service.Create(reporter);

QueryExpression qe = new QueryExpression();

qe.EntityName = "contact";

qe.ColumnSet = new ColumnSet("emailaddress1", "fullname");

qe.Criteria = new FilterExpression();

if (!string.IsNullOrEmpty(RelatedToEmailAddress))

qe.Criteria.AddCondition("emailaddress1", ConditionOperator.Equal, RelatedToEmailAddress);

else

qe.Criteria.AddCondition("fullname", ConditionOperator.Equal, RelatedtoExternal);

//EntityCollection ec = Service.RetrieveMultiple(qe);

var ec = RetrieveAllRecords(Service, qe);

if (ec.Count > 0)

{

foreach (var con in ec)

{

incident["mdc_relativename"] = new EntityReference("contact", con.Id);

}

}

}

}

else if (!string.IsNullOrEmpty(RelatedtoInternal))

{

QueryExpression conQuery = new QueryExpression("contact");

conQuery.Criteria.AddCondition("fullname", ConditionOperator.Equal, RelatedtoInternal);

//EntityCollection contactCol1 = Service.RetrieveMultiple(conQuery);

var contactCol1 = RetrieveAllRecords(Service, conQuery);

if (contactCol1.Count > 0)

{

foreach (Entity Eninci1 in contactCol1)

{

incident["mdc_relativename"] = new EntityReference("contact", Eninci1.Id);

}

}

else

{

var newExRN = RelatedtoInternal.Split(' ');

string firstName = newExRN[0];

string lastName = RelatedtoInternal.Remove(0, RelatedtoInternal.IndexOf(' ') + 1);

// string lastName = exInterview[1];

Entity reporter1 = new Entity("contact");

reporter1["firstname"] = firstName;

reporter1["lastname"] = lastName;

if (!string.IsNullOrEmpty(RelatedToEmailAddress))

reporter1["emailaddress1"] = RelatedToEmailAddress;

Service.Create(reporter1);

QueryExpression qe1 = new QueryExpression();

qe1.EntityName = "contact";

qe1.ColumnSet = new ColumnSet("emailaddress1", "fullname");

qe1.Criteria = new FilterExpression();

if (!string.IsNullOrEmpty(RelatedToEmailAddress))

qe1.Criteria.AddCondition("emailaddress1", ConditionOperator.Equal, RelatedToEmailAddress);

else

qe1.Criteria.AddCondition("fullname", ConditionOperator.Equal, RelatedtoInternal);

//EntityCollection ec1 = Service.RetrieveMultiple(qe1);

var ec1 = RetrieveAllRecords(Service, qe1);

if (ec1.Count > 0)

{

foreach (var con in ec1)

{

//incident["customerid"] = new EntityReference("contact", con.Id);

incident["mdc_relativename"] = new EntityReference("contact", con.Id);

}

}

}

}

}

}

catch (Exception ex)

{

throw new Exception(ex.ToString());

}

}

//Email Address not available in excel

else

{

string RelatedtoExternal = Convert.ToString(excelRange.Cells[i, 32].Value2);

string RelatedtoInternal = Convert.ToString(excelRange.Cells[i, 33].Value2);

if (!string.IsNullOrEmpty(RelatedtoExternal))

{

QueryExpression conQuery = new QueryExpression("contact");

conQuery.Criteria.AddCondition("fullname", ConditionOperator.Equal, RelatedtoExternal);

//EntityCollection contactCol1 = Service.RetrieveMultiple(conQuery);

var contactCol1 = RetrieveAllRecords(Service, conQuery);

if (contactCol1.Count > 0)

{

foreach (Entity Eninci1 in contactCol1)

{

incident["mdc_relativename"] = new EntityReference("contact", Eninci1.Id);

}

}

else

{

var newRN = RelatedtoExternal.Split(' ');

string firstName = newRN[0];

string lastName = RelatedtoExternal.Remove(0, RelatedtoExternal.IndexOf(' ') + 1);

// string lastName = exInterview[1];

Entity reporter = new Entity("contact");

reporter["firstname"] = firstName;

reporter["lastname"] = lastName;

if (!string.IsNullOrEmpty(RelatedToEmailAddress))

reporter["emailaddress1"] = RelatedToEmailAddress;

Service.Create(reporter);

QueryExpression qe = new QueryExpression();

qe.EntityName = "contact";

qe.ColumnSet = new ColumnSet("emailaddress1", "fullname");

qe.Criteria = new FilterExpression();

if (!string.IsNullOrEmpty(RelatedToEmailAddress))

qe.Criteria.AddCondition("emailaddress1", ConditionOperator.Equal, RelatedToEmailAddress);

else

qe.Criteria.AddCondition("fullname", ConditionOperator.Equal, RelatedtoExternal);

//EntityCollection ec = Service.RetrieveMultiple(qe);

var ec = RetrieveAllRecords(Service, qe);

if (ec.Count > 0)

{

foreach (var con in ec)

{

incident["customerid"] = new EntityReference("contact", con.Id);

incident["mdc_relativename"] = new EntityReference("contact", con.Id);

}

}

}

}

else if (!string.IsNullOrEmpty(RelatedtoInternal))

{

QueryExpression conQuery = new QueryExpression("contact");

conQuery.Criteria.AddCondition("fullname", ConditionOperator.Equal, RelatedtoInternal);

//EntityCollection contactCol1 = Service.RetrieveMultiple(conQuery);

var contactCol1 = RetrieveAllRecords(Service, conQuery);

if (contactCol1.Count > 0)

{

foreach (Entity Eninci1 in contactCol1)

{

incident["mdc_relativename"] = new EntityReference("contact", Eninci1.Id);

}

}

else

{

var newExRN = RelatedtoInternal.Split(' ');

string firstName = newExRN[0];

string lastName = RelatedtoInternal.Remove(0, RelatedtoInternal.IndexOf(' ') + 1);

// string lastName = exInterview[1];

Entity reporter1 = new Entity("contact");

reporter1["firstname"] = firstName;

reporter1["lastname"] = lastName;

if (!string.IsNullOrEmpty(RelatedToEmailAddress))

reporter1["emailaddress1"] = RelatedToEmailAddress;

Service.Create(reporter1);

QueryExpression qe1 = new QueryExpression();

qe1.EntityName = "contact";

qe1.ColumnSet = new ColumnSet("emailaddress1", "fullname");

qe1.Criteria = new FilterExpression();

if (!string.IsNullOrEmpty(RelatedToEmailAddress))

qe1.Criteria.AddCondition("emailaddress1", ConditionOperator.Equal, RelatedToEmailAddress);

else

qe1.Criteria.AddCondition("fullname", ConditionOperator.Equal, RelatedtoInternal);

//EntityCollection ec1 = Service.RetrieveMultiple(qe1);

var ec1 = RetrieveAllRecords(Service, qe1);

if (ec1.Count > 0)

{

foreach (var con1 in ec1)

{

incident["customerid"] = new EntityReference("contact", con1.Id);

incident["mdc_relativename"] = new EntityReference("contact", con1.Id);

}

}

}

}

}

break;

case 37:

string RelativesDepartment = Convert.ToString(excelRange.Cells[i, j].Value2);

incident["mdc_relativedepartment"] = !string.IsNullOrEmpty(RelativesDepartment) ? RelativesDepartment : string.Empty;

break;

case 38:

string RelativesCompany = Convert.ToString(excelRange.Cells[i, j].Value2);

incident["mdc_relativecompany"] = !string.IsNullOrEmpty(RelativesCompany) ? RelativesCompany : string.Empty;

break;

case 39:

incident["mdc_isanonymous"] = false;

string isAnonymous = Convert.ToString(excelRange.Cells[i, j].Value2);//.ToString();

if (!string.IsNullOrEmpty(isAnonymous))

incident["mdc_isanonymous"] = isAnonymous.ToLower() == "yes" ? true : false;

break;

////40 Currency need to check

case 41:

string platform = Convert.ToString(excelRange.Cells[i, j].Value2);

incident["mdc_platform"] = !string.IsNullOrEmpty(platform) ? platform : string.Empty;

break;

case 42:

incident["mdc_relatedasset"] = null;

incident["mdc_relateddivision"] = null;

string Relatedassest = Convert.ToString(excelRange.Cells[i, j].Value2);

string RelatedDivision = Convert.ToString(excelRange.Cells[i, 43].Value2);

if (Relatedassest != null && Relatedassest != string.Empty)

{

QueryExpression query = new QueryExpression("mdc_asset");

query.Criteria.AddCondition("mdc_name", ConditionOperator.Equal, Relatedassest);

try

{

EntityCollection col = Service.RetrieveMultiple(query);

if (col.Entities.Count > 0)

{

foreach (Entity Eninci in col.Entities)

{

incident["mdc_relatedasset"] = new EntityReference("mdc_asset", Eninci.Id);

}

}

else

{

Entity asset = new Entity("mdc_asset");

asset["mdc_name"] = Relatedassest;

Service.Create(asset);

QueryExpression assetQuery = new QueryExpression("mdc_asset");

query.Criteria.AddCondition("mdc_name", ConditionOperator.Equal, Relatedassest);

EntityCollection assetcol = Service.RetrieveMultiple(assetQuery);

if (assetcol.Entities.Count > 0)

{

foreach (Entity Eninci in assetcol.Entities)

{

incident["mdc_relatedasset"] = new EntityReference("mdc_asset", Eninci.Id);

}

}

}

}

catch (Exception ex)

{

throw new Exception(ex.ToString());

}

}

else if (RelatedDivision != null && RelatedDivision != string.Empty)

{

QueryExpression query = new QueryExpression("mdc_division");

query.Criteria.AddCondition("mdc_name", ConditionOperator.Equal, RelatedDivision);

try

{

EntityCollection col = Service.RetrieveMultiple(query);

if (col.Entities.Count > 0)

{

foreach (Entity Eninci in col.Entities)

{

incident["mdc_relateddivision"] = new EntityReference("mdc_division", Eninci.Id);

}

}

else

{

Entity division = new Entity("mdc_division");

division["mdc_name"] = RelatedDivision;

Service.Create(division);

QueryExpression divisionQuery = new QueryExpression("mdc_division");

query.Criteria.AddCondition("mdc_name", ConditionOperator.Equal, RelatedDivision);

EntityCollection divisioncol = Service.RetrieveMultiple(divisionQuery);

if (divisioncol.Entities.Count > 0)

{

foreach (Entity Eninci in divisioncol.Entities)

{

incident["mdc_relateddivision"] = new EntityReference("mdc_division", Eninci.Id);

}

}

}

}

catch (Exception ex)

{

throw new Exception(ex.ToString());

}

}

break;

case 44:

incident["mdc_areyouofferingorreceivingthegift"] = false;

string approach = GetGCColumn(Convert.ToString(excelRange.Cells[i, j].Value2));

if (!string.IsNullOrEmpty(approach))

{

if (approach.ToLower() == "receive")

incident["mdc_areyouofferingorreceivingthegift"] = false;

else if (approach.ToLower() == "offer")

incident["mdc_areyouofferingorreceivingthegift"] = true;

}

break;

case 45:

incident["mdc_relationshiptoyourcompany"] = null;

string Relationshiptocompany = Convert.ToString(excelRange.Cells[i, j].Value2);//.ToString();

if (!string.IsNullOrEmpty(Relationshiptocompany))

{

switch (Relationshiptocompany.ToLower())

{

case "current supplier":

incident["mdc_relationshiptoyourcompany"] = new OptionSetValue(755040000);

break;

case "n/a (describe in comments)":

incident["mdc_relationshiptoyourcompany"] = new OptionSetValue(755040001);

break;

case "other (describe in comments)":

incident["mdc_relationshiptoyourcompany"] = new OptionSetValue(755040002);

break;

case "colleague/s":

incident["mdc_relationshiptoyourcompany"] = new OptionSetValue(755040003);

break;

case "consultant":

incident["mdc_relationshiptoyourcompany"] = new OptionSetValue(755040004);

break;

case "current business partner":

incident["mdc_relationshiptoyourcompany"] = new OptionSetValue(755040005);

break;

case "current customer":

incident["mdc_relationshiptoyourcompany"] = new OptionSetValue(755040006);

break;

case "customer":

incident["mdc_relationshiptoyourcompany"] = new OptionSetValue(755040007);

break;

case "employee/s":

incident["mdc_relationshiptoyourcompany"] = new OptionSetValue(755040008);

break;

case "jv partner":

incident["mdc_relationshiptoyourcompany"] = new OptionSetValue(755040009);

break;

case "potential business partner":

incident["mdc_relationshiptoyourcompany"] = new OptionSetValue(755040010);

break;

case "potential customer":

incident["mdc_relationshiptoyourcompany"] = new OptionSetValue(755040011);

break;

case "potential jv partner":

incident["mdc_relationshiptoyourcompany"] = new OptionSetValue(755040012);

break;

case "potential supplier":

incident["mdc_relationshiptoyourcompany"] = new OptionSetValue(755040013);

break;

case "regulator":

incident["mdc_relationshiptoyourcompany"] = new OptionSetValue(755040014);

break;

case "service provider":

incident["mdc_relationshiptoyourcompany"] = new OptionSetValue(755040015);

break;

default:

break;

}

}

break;

////45 Relationship to your company not found created in crm

case 46:

incident["mdc_iscoiactive"] = false;

string isCOIActive = Convert.ToString(excelRange.Cells[i, j].Value2);//.ToString();

if (!string.IsNullOrEmpty(isCOIActive))

incident["mdc_iscoiactive"] = isCOIActive.ToLower() == "yes" ? true : false;

break;

case 47:

incident["mdc_coitype"] = null;

string Type = Convert.ToString(excelRange.Cells[i, j].Value2);//.ToString();

if (!string.IsNullOrEmpty(Type))

{

switch (Type.ToLower())

{

case "financial interest in/with outside concern":

incident["mdc_coitype"] = new OptionSetValue(755040000);

break;

case "holding a position of officer or director of outside concern":

incident["mdc_coitype"] = new OptionSetValue(755040001);

break;

case "other associations with outside concerns":

incident["mdc_coitype"] = new OptionSetValue(755040002);

break;

case "employment":

incident["mdc_coitype"] = new OptionSetValue(755040003);

break;

case "family, friends and personal relationships":

incident["mdc_coitype"] = new OptionSetValue(755040004);

break;

case "gifts, entertainment and honorariums":

incident["mdc_coitype"] = new OptionSetValue(755040007);//Other

break;

case "proprietary and confidential information":

incident["mdc_coitype"] = new OptionSetValue(755040006);

break;

case "other":

incident["mdc_coitype"] = new OptionSetValue(755040007);

break;

default:

break;

}

}

break;

case 48:

incident["mdc_category"] = null;

string category = Convert.ToString(excelRange.Cells[i, j].Value2);//.ToString();

if (!string.IsNullOrEmpty(category))

{

if (category.ToLower() == "gift" || category.ToLower() == "gift & entertainment")

incident["mdc_category"] = new OptionSetValue(755040000);

else if (category.ToLower() == "entertainment")

incident["mdc_category"] = new OptionSetValue(755040001);

else if (category.ToLower() == "sponsorship")

incident["mdc_category"] = new OptionSetValue(755040002);

else if (category.ToLower() == "sponsored travel")

incident["mdc_category"] = new OptionSetValue(755040003);

else if (category.ToLower() == "patient gifting")

incident["mdc_category"] = new OptionSetValue(755040004);

}

break;

case 49:

incident["mdc_documentsattached"] = false;

string Isdocumentattched = Convert.ToString(excelRange.Cells[i, j].Value2);//.ToString();

if (!string.IsNullOrEmpty(Isdocumentattched))

{

if (Isdocumentattched.ToLower() == "yes")

incident["mdc_documentsattached"] = true;

else if (Isdocumentattched.ToLower() == "no")

incident["mdc_documentsattached"] = false;

}

break;

case 50:

incident["mdc_isthecaseconfirmed"] = false;

string Iscaseconfirmed = Convert.ToString(excelRange.Cells[i, j].Value2);//.ToString();

if (!string.IsNullOrEmpty(Iscaseconfirmed))

{

if (Iscaseconfirmed.ToLower() == "yes")

incident["mdc_isthecaseconfirmed"] = true;

else if (Iscaseconfirmed.ToLower() == "no")

incident["mdc_isthecaseconfirmed"] = false;

}

break;

case 51:

incident["mdc_primaryinvestigator"] = null;

string LeadInvestigator = Convert.ToString(excelRange.Cells[i, j].Value2);

if (!string.IsNullOrEmpty(LeadInvestigator))

{

QueryExpression query = new QueryExpression("systemuser");

query.Criteria.AddCondition("fullname", ConditionOperator.Equal, "MIC.crmtestuser2 #");

try

{

EntityCollection col = Service.RetrieveMultiple(query);

if (col.Entities.Count > 0)

{

foreach (Entity Eninci in col.Entities)

{

incident["mdc_primaryinvestigator"] = new EntityReference("systemuser", Eninci.Id);

}

}

}

catch (Exception ex)

{

throw new Exception(ex.ToString());

}

}

break;

//////52 will be status need to look after

case 52:

incident["mdc_casestatus"] = null;

string caseStatus = Convert.ToString(excelRange.Cells[i, j].Value2);

if (!string.IsNullOrEmpty(caseStatus))

{

if (caseStatus.ToLower() == "open")

{

incident["mdc_casestatus"] = new OptionSetValue(755040000);

}

else if (caseStatus.ToLower() == "closed")

{

incident["mdc_casestatus"] = new OptionSetValue(755040001);

}

}

break;

case 53:

incident["mdc_tradetype"] = null;

string tradetype = Convert.ToString(excelRange.Cells[i, j].Value2);

if (!string.IsNullOrEmpty(tradetype))

{

switch (tradetype.ToLower())

{

case "restricted security trade request":

incident["mdc_tradetype"] = new OptionSetValue(755040000);

break;

case "restricted security disclosure":

incident["mdc_tradetype"] = new OptionSetValue(755040001);

break;

case "general trade":

incident["mdc_tradetype"] = new OptionSetValue(755040002);

break;

default:

break;

}

}

incident["mdc_valueofgiftaed"] = null;

var ValueoftheGE = excelRange.Cells[i, j].Value2;

if (ValueoftheGE != null)

incident["mdc_valueofgiftaed"] = new Money(Convert.ToDecimal(ValueoftheGE));

break;

case 55:

incident["mdc_buplatformleadernotified"] = null;

string AssetLeaderNotified = Convert.ToString(excelRange.Cells[i, j].Value2);

string BUDirectorNotified = Convert.ToString(excelRange.Cells[i, 57].Value2);

if (!string.IsNullOrEmpty(AssetLeaderNotified))

{

switch (AssetLeaderNotified.ToLower())

{

case "n/a":

incident["mdc_buplatformleadernotified"] = new OptionSetValue(2);

break;

case "yes":

incident["mdc_buplatformleadernotified"] = new OptionSetValue(0);

break;

case "no":

incident["mdc_buplatformleadernotified"] = new OptionSetValue(1);

break;

default:

break;

}

}

else if (!string.IsNullOrEmpty(BUDirectorNotified))

{

switch (BUDirectorNotified.ToLower())

{

case "n/a":

incident["mdc_buplatformleadernotified"] = new OptionSetValue(2);

break;

case "yes":

incident["mdc_buplatformleadernotified"] = new OptionSetValue(0);

break;

case "no":

incident["mdc_buplatformleadernotified"] = new OptionSetValue(1);

break;

default:

break;

}

}

break;

//56 Asset Leader

//58 BU/Platform Leader

case 56:

incident["mdc_businessleader"] = null;

string AssetLeader = Convert.ToString(excelRange.Cells[i, j].Value2);

string BUPlatformLeader = Convert.ToString(excelRange.Cells[i, 58].Value2);

if (!string.IsNullOrEmpty(AssetLeader))

{

QueryExpression query = new QueryExpression("contact");

query.Criteria.AddCondition("fullname", ConditionOperator.Equal, AssetLeader);

try

{

//EntityCollection col = Service.RetrieveMultiple(query);

var col = RetrieveAllRecords(Service, query);

if (col.Count > 0)

{

foreach (Entity Eninci in col)

{

incident["mdc_businessleader"] = new EntityReference("contact", Eninci.Id);

}

}

else

{

var newRN = AssetLeader.Split(' ');

string firstName = newRN[0];

string lastName = AssetLeader.Remove(0, AssetLeader.IndexOf(' ') + 1);

// string lastName = exInterview[1];

Entity reporter = new Entity("contact");

reporter["firstname"] = firstName;

reporter["lastname"] = lastName;

Service.Create(reporter);

QueryExpression qe = new QueryExpression();

qe.EntityName = "contact";

qe.ColumnSet = new ColumnSet("fullname");

qe.Criteria = new FilterExpression();

qe.Criteria.AddCondition("fullname", ConditionOperator.Equal, AssetLeader);

//EntityCollection ec = Service.RetrieveMultiple(qe);

var ec = RetrieveAllRecords(Service, qe);

if (ec.Count > 0)

{

foreach (var con in ec)

{

//incident["customerid"] = new EntityReference("contact", con.Id);

incident["mdc_businessleader"] = new EntityReference("contact", con.Id);

}

}

}

}

catch (Exception ex)

{

throw new Exception(ex.ToString());

}

}

else if (!string.IsNullOrEmpty(BUPlatformLeader))

{

QueryExpression conQuery = new QueryExpression("contact");

conQuery.Criteria.AddCondition("fullname", ConditionOperator.Equal, BUPlatformLeader);

//EntityCollection contactCol1 = Service.RetrieveMultiple(conQuery);

var contactCol1 = RetrieveAllRecords(Service, conQuery);

if (contactCol1.Count > 0)

{

foreach (Entity Eninci1 in contactCol1)

{

incident["mdc_businessleader"] = new EntityReference("contact", Eninci1.Id);

}

}

else

{

var newExRN = BUPlatformLeader.Split(' ');

string firstName = newExRN[0];

string lastName = BUPlatformLeader.Remove(0, BUPlatformLeader.IndexOf(' ') + 1);

// string lastName = exInterview[1];

Entity reporter1 = new Entity("contact");

reporter1["firstname"] = firstName;

reporter1["lastname"] = lastName;

Service.Create(reporter1);

QueryExpression qe1 = new QueryExpression();

qe1.EntityName = "contact";

qe1.ColumnSet = new ColumnSet("fullname");

qe1.Criteria = new FilterExpression();

qe1.Criteria.AddCondition("fullname", ConditionOperator.Equal, BUPlatformLeader);

//EntityCollection ec1 = Service.RetrieveMultiple(qe1);

var ec1 = RetrieveAllRecords(Service, qe1);

if (ec1.Count > 0)

{

foreach (var con in ec1)

{

//incident["customerid"] = new EntityReference("contact", con.Id);

incident["mdc_businessleader"] = new EntityReference("contact", con.Id);

}

}

}

}

break;

case 59:

string Reason = Convert.ToString(excelRange.Cells[i, j].Value2);

incident["mdc_reason"] = !string.IsNullOrEmpty(Reason) ? Reason : string.Empty;

break;

case 60:

incident["mdc_country"] = null;

string Country = Convert.ToString(excelRange.Cells[i, j].Value2);//.ToString();

if (Country != null && Country != string.Empty)

{

QueryExpression query = new QueryExpression("mdc_country");

query.Criteria.AddCondition("mdc_name", ConditionOperator.Equal, Country);

try

{

EntityCollection col = Service.RetrieveMultiple(query);

if (col.Entities.Count > 0)

{

foreach (Entity Eninci in col.Entities)

{

incident["mdc_country"] = new EntityReference("mdc_country", Eninci.Id);

}

}

}

catch (Exception ex)

{

throw new Exception(ex.ToString());

}

}

break;

case 61:

string Location = Convert.ToString(excelRange.Cells[i, j].Value2);

incident["mdc_location"] = !string.IsNullOrEmpty(Location) ? Location : string.Empty;

break;

case 62:

string LineManagername = Convert.ToString(excelRange.Cells[i, j].Value2);

incident["mdc_linemanagername"] = !string.IsNullOrEmpty(LineManagername) ? LineManagername : string.Empty;

break;

case 63:

incident["mdc_linemanagerinformed"] = false;

string LineManagerInformed = Convert.ToString(excelRange.Cells[i, j].Value2);//.ToString();

if (!string.IsNullOrEmpty(LineManagerInformed))

{

switch (LineManagerInformed.ToLower())

{

case "yes":

incident["mdc_linemanagerinformed"] = true;

break;

case "no":

incident["mdc_linemanagerinformed"] = false;

break;

default:

break;

}

}

break;

case 64:

incident["mdc_isthisgiftbeinggiventoanabudhabigovernmen"] = null;

string AnyGovtEntityinvolved = Convert.ToString(excelRange.Cells[i, j].Value2);//.ToString();

if (!string.IsNullOrEmpty(AnyGovtEntityinvolved))

{

switch (AnyGovtEntityinvolved.ToLower())

{

case "na":

incident["mdc_isthisgiftbeinggiventoanabudhabigovernmen"] = new OptionSetValue(2);

break;

case "yes":

incident["mdc_isthisgiftbeinggiventoanabudhabigovernmen"] = new OptionSetValue(0);

break;

case "no":

incident["mdc_isthisgiftbeinggiventoanabudhabigovernmen"] = new OptionSetValue(1);

break;

default:

break;

}

}

break;

case 65:

incident["mdc_closedas"] = null;

string ApprovalStatus = Convert.ToString(excelRange.Cells[i, j].Value2);//.ToString();

if (!string.IsNullOrEmpty(ApprovalStatus))

{

switch (ApprovalStatus.ToLower())

{

case "approved":

incident["mdc_closedas"] = new OptionSetValue(755040000);

break;

case "approved with comments":

incident["mdc_closedas"] = new OptionSetValue(755040001);

break;

case "rejected":

incident["mdc_closedas"] = new OptionSetValue(755040002);

break;

case "closed with no action":

incident["mdc_closedas"] = new OptionSetValue(755040003);

break;

default:

break;

}

}

break;

default:

break;

}

}

catch (Exception ex)

{

//WriteLog(String.Format(DateTime.Now.ToString("dd-MM-yyyy-HH-mm-ss"+"-1738") + "CaseRecordCreation.txt"), String.Format(ex.Message.ToString() + " " + ex.StackTrace.ToString()));

continue;

//throw new Exception(ex.ToString());

}

}

var createRequest = new CreateRequest()

{

Target = incident

};

executeMultipleRequest.Requests.Add(createRequest);

// listEntity.Add(incident);

}

//Service.Create(incident);

}

bool flag = ExecuteMultipleRequests(executeMultipleRequest.Requests.ToList(), Service);

//Task<bool> task= new Task<bool>(ExecuteMultipleRequests(executeMultipleRequest.Requests.ToList(), Service));

//Task<bool> task= ExecuteMultipleRequests(executeMultipleRequest.Requests.ToList(), Service);

// var flag = await task;

//Creating Interview Records

if (flag)

{

CreateInterviews(Service);

//Creating Action Records

CreateActions(Service);

QueryExpression subRisk = new QueryExpression();

subRisk.EntityName = "mdc_subriskarea";

subRisk.ColumnSet = new ColumnSet("mdc_ismigrated");

subRisk.Criteria = new FilterExpression();

subRisk.Criteria.AddCondition("mdc_ismigrated", ConditionOperator.Equal, 755040000); //Yes

subRisk.Criteria.AddCondition("statuscode", ConditionOperator.Equal, 1); //Only Active Records

EntityCollection subRiskColl = Service.RetrieveMultiple(subRisk);

foreach (var item in subRiskColl.Entities)

{

var request = new SetStateRequest()

{

EntityMoniker = new EntityReference

{

Id = item.ToEntityReference().Id,

LogicalName = item.ToEntityReference().LogicalName

},

State = new OptionSetValue(1),

Status = new OptionSetValue(2)

};

Service.Execute(request);

}

//ExecuteMultipleRequest executeMultipleRequest = CreateExecuteMultipleRequest();

//int count = 0;

QueryExpression EnCase = new QueryExpression();

EnCase.EntityName = "incident";

EnCase.ColumnSet = new ColumnSet("ticketnumber");

EnCase.Criteria = new FilterExpression();

EnCase.Criteria.AddCondition("mdc_ismigrated", ConditionOperator.Equal, 755040000); //Yes

var completeCaseCollection = RetrieveAllRecords(Service, EnCase);

//EntityCollection CaseColls = _crmService.RetrieveMultiple(EnCase);

foreach (var enitem in completeCaseCollection)

{

//count++;

Entity IncidentResolution = new Entity("incidentresolution");

IncidentResolution.Attributes["subject"] = "Subject Closed";

if (enitem.Id != null)

IncidentResolution.Attributes["incidentid"] = new EntityReference("incident", enitem.Id);

// Create the request to close the incident, and set its resolution to the resolution created above

CloseIncidentRequest closeRequest = new CloseIncidentRequest();

closeRequest.IncidentResolution = IncidentResolution;

// Set the requested new status for the closed Incident

closeRequest.Status = new OptionSetValue(5);

CloseIncidentResponse closeResponse = (CloseIncidentResponse)Service.Execute(closeRequest);

//Get Process from Case Record

RetrieveProcessInstancesRequest processInstanceRequest = new RetrieveProcessInstancesRequest

{

EntityId = enitem.Id,

EntityLogicalName = enitem.LogicalName

};

RetrieveProcessInstancesResponse processInstanceResponse = (RetrieveProcessInstancesResponse)Service.Execute(processInstanceRequest);

if (processInstanceResponse.Processes.Entities.Count > 0)

{

foreach (var item in processInstanceResponse.Processes.Entities)

{

EntityCollection bpfColl = null;

if (item.Attributes.ContainsKey("name"))

{

switch (item.GetAttributeValue<string>("name").ToLower())

{

case "conflict of interest process":

QueryExpression queryExpression_Coigroupbusinessprocess = new QueryExpression("mdc_10coigroupbusinessprocess");

queryExpression_Coigroupbusinessprocess.ColumnSet = new ColumnSet("bpf_name");

queryExpression_Coigroupbusinessprocess.Criteria.AddCondition("bpf_incidentid", ConditionOperator.Equal, enitem.Id);

queryExpression_Coigroupbusinessprocess.Criteria.AddCondition("statuscode", ConditionOperator.Equal, 1);

queryExpression_Coigroupbusinessprocess.NoLock = true;

bpfColl = Service.RetrieveMultiple(queryExpression_Coigroupbusinessprocess);

break;

case "g & e group process":

QueryExpression queryExpression_ExpressionGegroupprocess = new QueryExpression("mdc_gegroupprocess");

queryExpression_ExpressionGegroupprocess.ColumnSet = new ColumnSet("bpf_name");

queryExpression_ExpressionGegroupprocess.Criteria.AddCondition("bpf_incidentid", ConditionOperator.Equal, enitem.Id);

queryExpression_ExpressionGegroupprocess.Criteria.AddCondition("statuscode", ConditionOperator.Equal, 1);

queryExpression_ExpressionGegroupprocess.NoLock = true;

bpfColl = Service.RetrieveMultiple(queryExpression_ExpressionGegroupprocess);

break;

case "investigation cases":

QueryExpression queryExpression_Investigationcases = new QueryExpression("mdc_investigationcases");

queryExpression_Investigationcases.ColumnSet = new ColumnSet("bpf_name");

queryExpression_Investigationcases.Criteria.AddCondition("bpf_incidentid", ConditionOperator.Equal, enitem.Id);

queryExpression_Investigationcases.Criteria.AddCondition("statuscode", ConditionOperator.Equal, 1);

queryExpression_Investigationcases.NoLock = true;

bpfColl = Service.RetrieveMultiple(queryExpression_Investigationcases);

break;

case "helpdesk enquiries process":

QueryExpression queryExpression_Helpdeskenquiriesprocess = new QueryExpression("mdc_helpdeskenquiriesprocess");

queryExpression_Helpdeskenquiriesprocess.ColumnSet = new ColumnSet("bpf_name");

queryExpression_Helpdeskenquiriesprocess.Criteria.AddCondition("bpf_incidentid", ConditionOperator.Equal, enitem.Id);

queryExpression_Helpdeskenquiriesprocess.Criteria.AddCondition("statuscode", ConditionOperator.Equal, 1);

queryExpression_Helpdeskenquiriesprocess.NoLock = true;

bpfColl = Service.RetrieveMultiple(queryExpression_Helpdeskenquiriesprocess);

//Service.Delete("mdc_helpdeskenquiriesprocess", bpfColl_Enq.Entities[0].Id);

break;

case "personal trade management group process":

QueryExpression queryExpression_Personaltreadgroupprocess = new QueryExpression("mdc_personaltreadgroupprocess");

queryExpression_Personaltreadgroupprocess.ColumnSet = new ColumnSet("bpf_name");

queryExpression_Personaltreadgroupprocess.Criteria.AddCondition("bpf_incidentid", ConditionOperator.Equal, enitem.Id);

queryExpression_Personaltreadgroupprocess.Criteria.AddCondition("statuscode", ConditionOperator.Equal, 1);

queryExpression_Personaltreadgroupprocess.NoLock = true;

bpfColl = Service.RetrieveMultiple(queryExpression_Personaltreadgroupprocess);

//Service.Delete("mdc_personaltreadgroupprocess", bpfColl_Ptm.Entities[0].Id);

break;

default:

break;

}

}

if (bpfColl?.Entities[0]?.Id != Guid.Empty)

{

var deleteRequest = new DeleteRequest()

{

Target = new EntityReference(bpfColl.Entities[0].LogicalName, bpfColl.Entities[0].Id)

};

executeMultipleRequest.Requests.Add(deleteRequest);

}

}

}

}

if (executeMultipleRequest?.Requests?.Count > 0)

ExecuteMultipleRequests(executeMultipleRequest.Requests.ToList(), Service);//add all total bpf entity record created & delete bpf entity record

}

excelBook.Close(true);

excelApp.Quit();

System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);

}

public static bool ExecuteMultipleRequests(List<OrganizationRequest> requests, IOrganizationService Service)

{

try

{

ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12;

//IOrganizationService _crmService = GetCRMServiceConnection(ConfigurationManager.AppSettings["CRMApplicationURL"].ToString(),

//ConfigurationManager.AppSettings["CRMUserName"].ToString(), ConfigurationManager.AppSettings["CRMPassword"].ToString());

CrmServiceClient conn = new CrmServiceClient(ConfigurationManager.ConnectionStrings["CRM"].ConnectionString);

int requestCount = 0; // Current request count – if we fill up an ExecuteMultiple we want to run it

int batchCount = 1; // Current batch count – can be used for logging

// Get a refresh ExecuteMultiplRequest

ExecuteMultipleRequest executeMultipleRequest = CreateExecuteMultipleRequest();

if (requests.Count > 0)

{

foreach (OrganizationRequest request in requests)

{

executeMultipleRequest.Requests.Add(request);

requestCount++;

if (executeMultipleRequest.Requests.Count == BATCH_SIZE || (requestCount == requests.Count

&& executeMultipleRequest.Requests.Count > 0))

{

// Run all requests

conn.OrganizationWebProxyClient.InnerChannel.OperationTimeout = new TimeSpan(0, 30, 0);

CrmServiceClient.MaxConnectionTimeout = new TimeSpan(0, 30, 0);

ExecuteMultipleResponse response = (ExecuteMultipleResponse)Service.Execute(executeMultipleRequest) as ExecuteMultipleResponse;

WriteLog(String.Format(fileName + "Info.txt"), String.Format(DateTime.Now + " " + "Processed Count : " + response.Responses.Count + " requestCount : " + requestCount));

// Reset ExecuteMultiple for next batch

executeMultipleRequest = CreateExecuteMultipleRequest();

batchCount++;

}

}

}

}

catch (Exception e)

{

WriteLog(String.Format(DateTime.Now.ToString("dd-MM-yyyy-HH-mm-ss") + "ExecuteMultipleRequests.txt"), String.Format(e.Message.ToString() + " " + e.StackTrace.ToString()));

}

return true;

}

public static ExecuteMultipleRequest CreateExecuteMultipleRequest()

{

ExecuteMultipleRequest executeMultipleRequest = new ExecuteMultipleRequest();

executeMultipleRequest.Requests = new OrganizationRequestCollection();

executeMultipleRequest.Settings = new ExecuteMultipleSettings()

{

ContinueOnError = true,

ReturnResponses = true

};

return executeMultipleRequest;

}

private static bool WriteLog(string strFileName, string strMessage)

{

try

{

string desktopPath = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);

FileStream objFilestream = new FileStream(string.Format("{0}\\{1}", desktopPath + "\\logs", strFileName), FileMode.Append, FileAccess.Write);

StreamWriter objStreamWriter = new StreamWriter((Stream)objFilestream);

objStreamWriter.WriteLine(strMessage);

objStreamWriter.Close();

objFilestream.Close();

return true;

}

catch (Exception ex)

{

WriteLog(String.Format(fileName + "exception.txt"), String.Format(ex.Message.ToString() + " " + ex.StackTrace.ToString()));

return false;

}

}

public static string GetGCColumn(string value)

{

switch (value.ToLower())

{

//Interaction Type

case "general":

return "Enquiry";

case "insider trading":

return "Personal Trade Management";

case "conflicts of interest":

return "Conflict of Interest";

case "gift and entertainment":

return "Gift & Entertainment";

case "policy":

return "Investigation";

//Approach

case "offered":

return "Offer";

case "received":

return "Receive";

default:

return value;

}

}

public static void CreateInterviews(IOrganizationService Service)

{

Application excelApp = new Application();

if (excelApp == null)

{

Console.WriteLine("Excel is not installed!!");

return;

}


Workbook excelBook = excelApp.Workbooks.Open(@"C:\_DO_NOT_BACKUP\MigrationCCMToPP1GC_8thSep2021\9thSep\CCM- Case MigrationSepFinal.xlsx");

_Worksheet excelSheet = excelBook.Sheets[3];

Range excelRange = excelSheet.UsedRange;

int rows = excelRange.Rows.Count;

int cols = excelRange.Columns.Count;

ExecuteMultipleRequest executeMultipleRequest = CreateExecuteMultipleRequest();

for (int i = 1; i <= rows; i++)

{

Entity Interview = new Entity("mdc_interviews");

if (i >= 2)

{

for (int j = 1; j <= cols; j++)

{

try

{

Interview["mdc_ismigrated"] = new OptionSetValue(755040000);

switch (j)

{

case 1:

if (!string.IsNullOrEmpty(Convert.ToString(excelRange.Cells[i, j].Value2)))

{

QueryExpression query = new QueryExpression("incident");

query.Criteria.AddCondition("ticketnumber", ConditionOperator.Equal, Convert.ToString(excelRange.Cells[i, j].Value2));

try

{

//EntityCollection col = Service.RetrieveMultiple(query);

var col = RetrieveAllRecords(Service, query);

if (col.Count > 0)

{

foreach (Entity Eninci in col)

{

Interview["mdc_caseid"] = new EntityReference("incident", Eninci.Id);

}

}

}

catch (Exception ex)

{

throw new Exception(ex.ToString());

}

}

break;

case 2:

Interview["mdc_interviewdate"] = null;

string interviewDate = Convert.ToString(excelRange.Cells[i, j].Value2);

if (interviewDate != "Unclassified")

{

try

{

double date = double.Parse(interviewDate);

var dateTime1 = DateTime.FromOADate(date).ToString("MMMM dd, yyyy");

DateTime dt = Convert.ToDateTime(dateTime1);

String Converteddate = dt.ToShortDateString();

Interview["mdc_interviewdate"] = dt;

}

catch (Exception ex)

{

continue;

}

}

break;

case 3:

Interview["mdc_intervieweeisnotmdcuser"] = !string.IsNullOrEmpty(Convert.ToString(excelRange.Cells[i, j].Value2)) && Convert.ToString(excelRange.Cells[i, j].Value2).ToLower() == "yes" ? true : false;

break;

case 6:

Interview["mdc_title"] = !string.IsNullOrEmpty(Convert.ToString(excelRange.Cells[i, j].Value2)) ? Convert.ToString(excelRange.Cells[i, j].Value2) : string.Empty;

break;

case 7:

Interview["mdc_emailaddress"] = string.Empty;

Interview["mdc_interviewee"] = null;

string Email = Convert.ToString(excelRange.Cells[i, j].Value2);

if (!string.IsNullOrEmpty(Email))

{

Interview["mdc_emailaddress"] = Email;

QueryExpression query = new QueryExpression("contact");

query.Criteria.AddCondition("emailaddress1", ConditionOperator.Equal, Email);

try

{

//EntityCollection col = Service.RetrieveMultiple(query);

var col = RetrieveAllRecords(Service, query);

if (col.Count > 0)

{

foreach (Entity Eninci in col)

{

Interview["mdc_interviewee"] = new EntityReference("contact", Eninci.Id);

//incident["customerid"] = new EntityReference("contact", Eninci.Id);

}

}

else

{

string Internalintervieew = Convert.ToString(excelRange.Cells[i, 4].Value2);

string Externalintervieew = Convert.ToString(excelRange.Cells[i, 5].Value2);

if (!string.IsNullOrEmpty(Internalintervieew))

{

QueryExpression conQuery = new QueryExpression("contact");

conQuery.Criteria.AddCondition("fullname", ConditionOperator.Equal, Internalintervieew);

//EntityCollection contactCol1 = Service.RetrieveMultiple(conQuery);

var contactCol1 = RetrieveAllRecords(Service, conQuery);

if (contactCol1.Count > 0)

{

foreach (Entity Eninci1 in contactCol1)

{

Interview["mdc_interviewee"] = new EntityReference("contact", Eninci1.Id);

}

}

else

{

var newRN = Internalintervieew.Split(' ');

string firstName = newRN[0];

string lastName = Internalintervieew.Remove(0, Internalintervieew.IndexOf(' ') + 1);

// string lastName = exInterview[1];

Entity reporter = new Entity("contact");

reporter["firstname"] = firstName;

reporter["lastname"] = lastName;

if (!string.IsNullOrEmpty(Email))

reporter["emailaddress1"] = Email;

Service.Create(reporter);

QueryExpression qe = new QueryExpression();

qe.EntityName = "contact";

qe.ColumnSet = new ColumnSet("emailaddress1");

qe.Criteria = new FilterExpression();

qe.Criteria.AddCondition("emailaddress1", ConditionOperator.Equal, Email);

//EntityCollection ec = Service.RetrieveMultiple(qe);

var ec = RetrieveAllRecords(Service, qe);

if (ec.Count > 0)

{

foreach (var con in ec)

{

//Interview["customerid"] = new EntityReference("contact", con.Id);

Interview["mdc_interviewee"] = new EntityReference("contact", con.Id);

}

}

}

}

else if (!string.IsNullOrEmpty(Externalintervieew))

{

QueryExpression conQuery = new QueryExpression("contact");

conQuery.Criteria.AddCondition("fullname", ConditionOperator.Equal, Externalintervieew);

//EntityCollection contactCol1 = Service.RetrieveMultiple(conQuery);

var contactCol1 = RetrieveAllRecords(Service, conQuery);

if (contactCol1.Count > 0)

{

foreach (Entity Eninci1 in contactCol1)

{

Interview["mdc_interviewee"] = new EntityReference("contact", Eninci1.Id);

}

}

else

{

var newExRN = Externalintervieew.Split(' ');

string firstName = newExRN[0];

string lastName = Externalintervieew.Remove(0, Externalintervieew.IndexOf(' ') + 1);

// string lastName = exInterview[1];

Entity reporter1 = new Entity("contact");

reporter1["firstname"] = firstName;

reporter1["lastname"] = lastName;

if (!string.IsNullOrEmpty(Email))

reporter1["emailaddress1"] = Email;

Service.Create(reporter1);

QueryExpression qe1 = new QueryExpression();

qe1.EntityName = "contact";

qe1.ColumnSet = new ColumnSet("emailaddress1");

qe1.Criteria = new FilterExpression();

qe1.Criteria.AddCondition("emailaddress1", ConditionOperator.Equal, Email);

//EntityCollection ec1 = Service.RetrieveMultiple(qe1);

var ec1 = RetrieveAllRecords(Service, qe1);

if (ec1.Count > 0)

{

foreach (var con in ec1)

{

//Interview["customerid"] = new EntityReference("contact", con.Id);

Interview["mdc_interviewee"] = new EntityReference("contact", con.Id);

}

}

}

}

}

}

catch (Exception ex)

{

throw new Exception(ex.ToString());

}

}

else

{

string Internalintervieew = Convert.ToString(excelRange.Cells[i, 4].Value2);

string Externalintervieew = Convert.ToString(excelRange.Cells[i, 5].Value2);

if (!string.IsNullOrEmpty(Internalintervieew))

{

QueryExpression conQuery = new QueryExpression("contact");

conQuery.Criteria.AddCondition("fullname", ConditionOperator.Equal, Internalintervieew);

//EntityCollection contactCol1 = Service.RetrieveMultiple(conQuery);

var contactCol1 = RetrieveAllRecords(Service, conQuery);

if (contactCol1.Count > 0)

{

foreach (Entity Eninci1 in contactCol1)

{

Interview["mdc_interviewee"] = new EntityReference("contact", Eninci1.Id);

}

}

else

{

var newRN = Internalintervieew.Split(' ');

string firstName = newRN[0];

string lastName = Internalintervieew.Remove(0, Internalintervieew.IndexOf(' ') + 1);

// string lastName = exInterview[1];

Entity reporter = new Entity("contact");

reporter["firstname"] = firstName;

reporter["lastname"] = lastName;

if (!string.IsNullOrEmpty(Email))

reporter["emailaddress1"] = Email;

Service.Create(reporter);

QueryExpression qe = new QueryExpression();

qe.EntityName = "contact";

qe.ColumnSet = new ColumnSet("emailaddress1", "fullname");

qe.Criteria = new FilterExpression();

if (!string.IsNullOrEmpty(Email))

qe.Criteria.AddCondition("emailaddress1", ConditionOperator.Equal, Email);

else

qe.Criteria.AddCondition("fullname", ConditionOperator.Equal, Internalintervieew);

//EntityCollection ec = Service.RetrieveMultiple(qe);

var ec = RetrieveAllRecords(Service, qe);

if (ec.Count > 0)

{

foreach (var con in ec)

{

//Interview["customerid"] = new EntityReference("contact", con.Id);

Interview["mdc_interviewee"] = new EntityReference("contact", con.Id);

}

}

}

}

else if (!string.IsNullOrEmpty(Externalintervieew))

{

QueryExpression conQuery = new QueryExpression("contact");

conQuery.Criteria.AddCondition("fullname", ConditionOperator.Equal, Externalintervieew);

//EntityCollection contactCol1 = Service.RetrieveMultiple(conQuery);

var contactCol1 = RetrieveAllRecords(Service, conQuery);

if (contactCol1.Count > 0)

{

foreach (Entity Eninci1 in contactCol1)

{

Interview["mdc_interviewee"] = new EntityReference("contact", Eninci1.Id);

}

}

else

{

var newExRN = Externalintervieew.Split(' ');

string firstName = newExRN[0];

string lastName = Externalintervieew.Remove(0, Externalintervieew.IndexOf(' ') + 1);

// string lastName = exInterview[1];

Entity reporter1 = new Entity("contact");

reporter1["firstname"] = firstName;

reporter1["lastname"] = lastName;

if (!string.IsNullOrEmpty(Email))

reporter1["emailaddress1"] = Email;

Service.Create(reporter1);

QueryExpression qe1 = new QueryExpression();

qe1.EntityName = "contact";

qe1.ColumnSet = new ColumnSet("emailaddress1", "fullname");

qe1.Criteria = new FilterExpression();

if (!string.IsNullOrEmpty(Email))

qe1.Criteria.AddCondition("emailaddress1", ConditionOperator.Equal, Email);

else

qe1.Criteria.AddCondition("fullname", ConditionOperator.Equal, Externalintervieew);

//EntityCollection ec1 = Service.RetrieveMultiple(qe1);

var ec1 = RetrieveAllRecords(Service, qe1);

if (ec1.Count > 0)

{

foreach (var con in ec1)

{

//incident["customerid"] = new EntityReference("contact", con.Id);

Interview["mdc_interviewee"] = new EntityReference("contact", con.Id);

}

}

}

}

}

break;

case 8:

Interview["mdc_mobilenumber"] = !string.IsNullOrEmpty(Convert.ToString(excelRange.Cells[i, j].Value2)) ? Convert.ToString(excelRange.Cells[i, j].Value2) : string.Empty;

break;

case 9:

Interview["mdc_department"] = !string.IsNullOrEmpty(Convert.ToString(excelRange.Cells[i, j].Value2)) ? Convert.ToString(excelRange.Cells[i, j].Value2) : string.Empty;

break;

case 12:

Interview["mdc_status"] = null;

string Status = Convert.ToString(excelRange.Cells[i, j].Value2);

if (!string.IsNullOrEmpty(Status))

{

switch (Status.ToLower())

{

case "open":

Interview["mdc_status"] = new OptionSetValue(755040000);

break;

case "closed":

Interview["mdc_status"] = new OptionSetValue(755040001);

break;

default:

break;

}

}

break;

default:

break;

}

//else if (j == 10)

//{

// Interview["mdc_assest"] = string.Empty;

// string Asset = Convert.ToString(excelRange.Cells[i, j].Value2);

// if (!string.IsNullOrEmpty(Asset))

// {

// //Interview["mdc_assest"] = Asset;

// QueryExpression query = new QueryExpression("mdc_asset");

// query.Criteria.AddCondition("mdc_name", ConditionOperator.Equal, Asset);

// try

// {

// EntityCollection col = Service.RetrieveMultiple(query);

// if (col.Entities.Count > 0)

// {

// foreach (Entity Eninci in col.Entities)

// {

// var assetColln = Service.Retrieve("mdc_asset", Eninci.Id, new ColumnSet(true));

// var fullName = assetColln["mdc_name"].ToString();

// Interview["mdc_assest"] = fullName;

// }

// }

// }

// catch (Exception ex)

// {

// throw new Exception(ex.ToString());

// }

// }

//}

//11 Interviewer Grid to User

//12 Interviewer Position - NA

}

catch (Exception ex)

{

//WriteLog(String.Format(DateTime.Now.ToString("dd-MM-yyyy-HH-mm-ss") + "InterviewCreation.txt"), String.Format(ex.Message.ToString() + " " + ex.StackTrace.ToString()));

continue;

}

}

var createRequest = new CreateRequest()

{

Target = Interview

};

executeMultipleRequest.Requests.Add(createRequest);

}

}

ExecuteMultipleRequests(executeMultipleRequest.Requests.ToList(), Service);

QueryExpression EnInterview = new QueryExpression();

EnInterview.EntityName = "mdc_interviews";

EnInterview.ColumnSet = new ColumnSet("mdc_ismigrated");

EnInterview.Criteria = new FilterExpression();

EnInterview.Criteria.AddCondition("mdc_ismigrated", ConditionOperator.Equal, 755040000);

EntityCollection InterviewColls = Service.RetrieveMultiple(EnInterview);

AssociateRequest request1 = new AssociateRequest();

try

{

foreach (var enitem in InterviewColls.Entities)

{

QueryExpression userQuery = new QueryExpression("systemuser");

userQuery.Criteria.AddCondition("fullname", ConditionOperator.Equal, "MIC.crmtestuser2 #");

EntityCollection col = Service.RetrieveMultiple(userQuery);

if (col.Entities.Count > 0)

{

request1.Target = new EntityReference(enitem.LogicalName, enitem.Id);

foreach (Entity Eninci in col.Entities)

{

request1.RelatedEntities = new EntityReferenceCollection();

request1.RelatedEntities.Add(new EntityReference("systemuser", Eninci.Id));

request1.Relationship = new Relationship("mdc_mdc_interviews_systemuser");

}

}

if (request1.Target != null)

Service.Execute(request1);

}

}

catch (Exception ex)

{

//WriteLog(String.Format(DateTime.Now.ToString("dd-MM-yyyy-HH-mm-ss") + "UserInterviewAssociate.txt"), String.Format(ex.Message.ToString() + " " + ex.StackTrace.ToString()));

throw ex;

}

excelBook.Close(true);

excelApp.Quit();

System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);

}

public static void CreateActions(IOrganizationService Service)

{

Application excelApp = new Application();

if (excelApp == null)

{

Console.WriteLine("Excel is not installed!!");

return;

}


Workbook excelBook = excelApp.Workbooks.Open(@"C:\_DO_NOT_BACKUP\MigrationCCMToPP1GC_8thSep2021\9thSep\CCM- Case MigrationSepFinal.xlsx");

_Worksheet excelSheet = excelBook.Sheets[2];

Range excelRange = excelSheet.UsedRange;

int rows = excelRange.Rows.Count;

int cols = excelRange.Columns.Count;

ExecuteMultipleRequest executeMultipleRequest = CreateExecuteMultipleRequest();

for (int i = 1; i <= rows; i++)

{

Entity Action = new Entity("mdc_actions");

if (i >= 2)

{

for (int j = 1; j <= cols; j++)

{

try

{

switch (j)

{

case 1:

Action["mdc_case"] = null;

string CaseNumber = Convert.ToString(excelRange.Cells[i, j].Value2);

if (!string.IsNullOrEmpty(CaseNumber))

{

QueryExpression query = new QueryExpression("incident");

query.Criteria.AddCondition("ticketnumber", ConditionOperator.Equal, CaseNumber);

try

{

// EntityCollection col = Service.RetrieveMultiple(query);

var col = RetrieveAllRecords(Service, query);

if (col.Count > 0)

{

foreach (Entity Eninci in col)

{

Action["mdc_case"] = new EntityReference("incident", Eninci.Id);

}

}

}

catch (Exception ex)

{

throw new Exception(ex.ToString());

}

}

break;

case 2:

Action["mdc_actionnumber"] = !string.IsNullOrEmpty(Convert.ToString(excelRange.Cells[i, j].Value2)) ? Convert.ToString(excelRange.Cells[i, j].Value2) : string.Empty;

break;

case 3:

Action["mdc_actiontype"] = null;

string actionType = Convert.ToString(excelRange.Cells[i, j].Value2);//.ToString();

if (actionType != null && actionType != string.Empty)

{

QueryExpression query = new QueryExpression("mdc_actiontype");

query.Criteria.AddCondition("mdc_name", ConditionOperator.Equal, actionType);

try

{

EntityCollection col = Service.RetrieveMultiple(query);

if (col.Entities.Count > 0)

{

foreach (Entity Eninci in col.Entities)

{

Action["mdc_actiontype"] = new EntityReference("mdc_actiontype", Eninci.Id);

}

}

}

catch (Exception ex)

{

throw new Exception(ex.ToString());

}

}

break;

case 4:

Action["mdc_subtype"] = null;

string subType = Convert.ToString(excelRange.Cells[i, j].Value2);//.ToString();

if (subType != null && subType != string.Empty)

{

QueryExpression query = new QueryExpression("mdc_actionsubtype");

query.Criteria.AddCondition("mdc_name", ConditionOperator.Equal, subType);

try

{

EntityCollection col = Service.RetrieveMultiple(query);

if (col.Entities.Count > 0)

{

foreach (Entity Eninci in col.Entities)

{

Action["mdc_subtype"] = new EntityReference("mdc_actionsubtype", Eninci.Id);

}

}

}

catch (Exception ex)

{

throw new Exception(ex.ToString());

}

}

break;

case 7:

Action["mdc_email"] = string.Empty;

Action["mdc_actionowner"] = null;

string ExternalEmail = Convert.ToString(excelRange.Cells[i, j].Value2);

string InternalEmail = Convert.ToString(excelRange.Cells[i, j].Value2);//j==8

string ActionOwner = Convert.ToString(excelRange.Cells[i, 5].Value2);

string ActionOwnerAd = Convert.ToString(excelRange.Cells[i, 6].Value2);

if (!string.IsNullOrEmpty(ExternalEmail) || !string.IsNullOrEmpty(ActionOwner))

{

if (!string.IsNullOrEmpty(ExternalEmail))

{

Action["mdc_email"] = ExternalEmail;

QueryExpression query = new QueryExpression("contact");

query.Criteria.AddCondition("emailaddress1", ConditionOperator.Equal, ExternalEmail);

try

{

//EntityCollection col = Service.RetrieveMultiple(query);

var col = RetrieveAllRecords(Service, query);

if (col.Count > 0)

{

foreach (Entity Eninci in col)

{

Action["mdc_actionowner"] = new EntityReference("contact", Eninci.Id);

}

}

else if (!string.IsNullOrEmpty(ActionOwner))

{

QueryExpression querycon = new QueryExpression("contact");

query.Criteria.AddCondition("fullname", ConditionOperator.Equal, ActionOwner);

//EntityCollection colCon = Service.RetrieveMultiple(querycon);

var colCon = RetrieveAllRecords(Service, querycon);

if (colCon.Count > 0)

{

foreach (Entity Eninci in colCon)

{

Action["mdc_actionowner"] = new EntityReference("contact", Eninci.Id);

}

}

else

{

var newRN = ActionOwner.Split(' ');

string firstName = newRN[0];

string lastName = ActionOwner.Remove(0, ActionOwner.IndexOf(' ') + 1);

// string lastName = exInterview[1];

Entity reporter = new Entity("contact");

reporter["firstname"] = firstName;

reporter["lastname"] = lastName;

Service.Create(reporter);

QueryExpression qe = new QueryExpression();

qe.EntityName = "contact";

qe.ColumnSet = new ColumnSet("fullname");

qe.Criteria = new FilterExpression();

if (!string.IsNullOrEmpty(ActionOwner))

qe.Criteria.AddCondition("fullname", ConditionOperator.Equal, ActionOwner);

//EntityCollection ec = Service.RetrieveMultiple(qe);

var ec = RetrieveAllRecords(Service, qe);

if (ec.Count > 0)

{

foreach (var con in ec)

{

Action["mdc_actionowner"] = new EntityReference("contact", con.Id);

}

}

}

}

}

catch (Exception ex)

{

throw new Exception(ex.ToString());

}

}

}

else if (!string.IsNullOrEmpty(InternalEmail) || !string.IsNullOrEmpty(ActionOwnerAd))

{

if (!string.IsNullOrEmpty(InternalEmail))

{

Action["mdc_email"] = InternalEmail;

QueryExpression query = new QueryExpression("contact");

query.Criteria.AddCondition("emailaddress1", ConditionOperator.Equal, InternalEmail);

try

{

//EntityCollection col = Service.RetrieveMultiple(query);

var col = RetrieveAllRecords(Service, query);

if (col.Count > 0)

{

foreach (Entity Eninci in col)

{

Action["mdc_actionowner"] = new EntityReference("contact", Eninci.Id);

}

}

else if (!string.IsNullOrEmpty(ActionOwnerAd))

{

QueryExpression querycon = new QueryExpression("contact");

query.Criteria.AddCondition("fullname", ConditionOperator.Equal, ActionOwnerAd);

//EntityCollection colCon = Service.RetrieveMultiple(querycon);

var colCon = RetrieveAllRecords(Service, querycon);

if (colCon.Count > 0)

{

foreach (Entity Eninci in colCon)

{

Action["mdc_actionowner"] = new EntityReference("contact", Eninci.Id);

}

}

else

{

var newRN = ActionOwnerAd.Split(' ');

string firstName = newRN[0];

string lastName = ActionOwnerAd.Remove(0, ActionOwnerAd.IndexOf(' ') + 1);

// string lastName = exInterview[1];

Entity reporter = new Entity("contact");

reporter["firstname"] = firstName;

reporter["lastname"] = lastName;

Service.Create(reporter);

QueryExpression qe = new QueryExpression();

qe.EntityName = "contact";

qe.ColumnSet = new ColumnSet("fullname");

qe.Criteria = new FilterExpression();

if (!string.IsNullOrEmpty(ActionOwnerAd))

qe.Criteria.AddCondition("fullname", ConditionOperator.Equal, ActionOwnerAd);

//EntityCollection ec = Service.RetrieveMultiple(qe);

var ec = RetrieveAllRecords(Service, qe);

if (ec.Count > 0)

{

foreach (var con in ec)

{

Action["mdc_actionowner"] = new EntityReference("contact", con.Id);

}

}

}

}

}

catch (Exception ex)

{

throw new Exception(ex.ToString());

}

}

}

break;

case 8:

Action["mdc_actiondescription"] = !string.IsNullOrEmpty(Convert.ToString(excelRange.Cells[i, j].Value2)) ? Convert.ToString(excelRange.Cells[i, j].Value2) : string.Empty;

break;

case 9:

Action["mdc_completiondate"] = null;

string completiondate = Convert.ToString(excelRange.Cells[i, j].Value2);

if (completiondate != "Unclassified")

{

try

{

double date = double.Parse(completiondate);

var dateTime1 = DateTime.FromOADate(date).ToString("MMMM dd, yyyy");

DateTime dt = Convert.ToDateTime(dateTime1);

String Converteddate = dt.ToShortDateString();

Action["mdc_completiondate"] = dt;

}

catch (Exception ex)

{

continue;

}

}

break;

case 10:

Action["mdc_reviseddate"] = null;

string Reviseddate = Convert.ToString(excelRange.Cells[i, j].Value2);

if (Reviseddate != null)

{

try

{

double date = double.Parse(Reviseddate);

var dateTime1 = DateTime.FromOADate(date).ToString("MMMM dd, yyyy");

DateTime dt = Convert.ToDateTime(dateTime1);

String Converteddate = dt.ToShortDateString();

Action["mdc_reviseddate"] = dt;

}

catch (Exception ex)

{

continue;

}

}

break;

case 11:

Action["mdc_targetdate"] = null;

string Targetdate = Convert.ToString(excelRange.Cells[i, j].Value2);

if (Targetdate != "Unclassified")

{

try

{

double date = double.Parse(Targetdate);

var dateTime1 = DateTime.FromOADate(date).ToString("MMMM dd, yyyy");

DateTime dt = Convert.ToDateTime(dateTime1);

String Converteddate = dt.ToShortDateString();

Action["mdc_targetdate"] = dt;

}

catch (Exception ex)

{

continue;

}

}

break;

case 12:

Action["mdc_status"] = null;

string Status = Convert.ToString(excelRange.Cells[i, j].Value2);

if (!string.IsNullOrEmpty(Status))

{

switch (Status.ToLower())

{

case "open":

Action["mdc_status"] = new OptionSetValue(755040000);

break;

case "closed":

Action["mdc_status"] = new OptionSetValue(755040005);

break;

case "cancelled":

Action["mdc_status"] = new OptionSetValue(755040006);

break;

default:

break;

}

}

break;

case 14:

Action["mdc_recipientisnotanmdcuser"] = !string.IsNullOrEmpty(Convert.ToString(excelRange.Cells[i, j].Value2)) && Convert.ToString(excelRange.Cells[i, j].Value2).ToLower() == "yes" ? true : false;

break;

case 15:

Action["mdc_recipientofthedisciplinaryaction"] = null;

string RecipientDisciplinaryAction = Convert.ToString(excelRange.Cells[i, j].Value2);

string RecipientDisciplinaryActionExternal = Convert.ToString(excelRange.Cells[i, 16].Value2);

if (!string.IsNullOrEmpty(RecipientDisciplinaryAction))

{

QueryExpression query = new QueryExpression("contact");

query.Criteria.AddCondition("fullname", ConditionOperator.Equal, RecipientDisciplinaryAction);

try

{

//EntityCollection col = Service.RetrieveMultiple(query);

var col = RetrieveAllRecords(Service, query);

if (col.Count > 0)

{

foreach (Entity Eninci in col)

{

Action["mdc_recipientofthedisciplinaryaction"] = new EntityReference("contact", Eninci.Id);

}

}

else

{

var newRN = RecipientDisciplinaryAction.Split(' ');

string firstName = newRN[0];

string lastName = RecipientDisciplinaryAction.Remove(0, RecipientDisciplinaryAction.IndexOf(' ') + 1);

// string lastName = exInterview[1];

Entity reporter = new Entity("contact");

reporter["firstname"] = firstName;

reporter["lastname"] = lastName;

Service.Create(reporter);

QueryExpression qe = new QueryExpression();

qe.EntityName = "contact";

qe.ColumnSet = new ColumnSet("fullname");

qe.Criteria = new FilterExpression();

if (!string.IsNullOrEmpty(RecipientDisciplinaryAction))

qe.Criteria.AddCondition("fullname", ConditionOperator.Equal, RecipientDisciplinaryAction);

//EntityCollection ec = Service.RetrieveMultiple(qe);

var ec = RetrieveAllRecords(Service, qe);

if (ec.Count > 0)

{

foreach (var con in ec)

{

// Action["customerid"] = new EntityReference("contact", con.Id);

Action["mdc_recipientofthedisciplinaryaction"] = new EntityReference("contact", con.Id);

}

}

}

}

catch (Exception ex)

{

throw new Exception(ex.ToString());

}

}

else if (!string.IsNullOrEmpty(RecipientDisciplinaryActionExternal))

{

QueryExpression query = new QueryExpression("contact");

query.Criteria.AddCondition("fullname", ConditionOperator.Equal, RecipientDisciplinaryActionExternal);

try

{

//EntityCollection col = Service.RetrieveMultiple(query);

var col = RetrieveAllRecords(Service, query);

if (col.Count > 0)

{

foreach (Entity Eninci in col)

{

Action["mdc_recipientofthedisciplinaryaction"] = new EntityReference("contact", Eninci.Id);

}

}

else

{

var newRN = RecipientDisciplinaryActionExternal.Split(' ');

string firstName = newRN[0];

string lastName = RecipientDisciplinaryActionExternal.Remove(0, RecipientDisciplinaryActionExternal.IndexOf(' ') + 1);

// string lastName = exInterview[1];

Entity reporter = new Entity("contact");

reporter["firstname"] = firstName;

reporter["lastname"] = lastName;

Service.Create(reporter);

QueryExpression qe = new QueryExpression();

qe.EntityName = "contact";

qe.ColumnSet = new ColumnSet("fullname");

qe.Criteria = new FilterExpression();

if (!string.IsNullOrEmpty(RecipientDisciplinaryActionExternal))

qe.Criteria.AddCondition("fullname", ConditionOperator.Equal, RecipientDisciplinaryActionExternal);

//EntityCollection ec = Service.RetrieveMultiple(qe);

var ec = RetrieveAllRecords(Service, qe);

if (ec.Count > 0)

{

foreach (var con in ec)

{

// Action["customerid"] = new EntityReference("contact", con.Id);

Action["mdc_recipientofthedisciplinaryaction"] = new EntityReference("contact", con.Id);

}

}

}

}

catch (Exception ex)

{

throw new Exception(ex.ToString());

}

}

break;

default:

break;

}

//Recipient of the Disciplinary Action & External

}

catch (Exception ex)

{

//WriteLog(String.Format(DateTime.Now.ToString("dd-MM-yyyy-HH-mm-ss") + "ActionCreation.txt"), String.Format(ex.Message.ToString() + " " + ex.StackTrace.ToString()));

continue;

}

}

var createRequest = new CreateRequest()

{

Target = Action

};

executeMultipleRequest.Requests.Add(createRequest);

}

}

ExecuteMultipleRequests(executeMultipleRequest.Requests.ToList(), Service);

excelBook.Close(true);

excelApp.Quit();

System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);

}

public static List<Entity> RetrieveAllRecords(IOrganizationService service, QueryExpression query)

{

var pageNumber = 1;

var pagingCookie = string.Empty;

var result = new List<Entity>();

EntityCollection resp;

do

{

if (pageNumber != 1)

{

query.PageInfo.PageNumber = pageNumber;

query.PageInfo.PagingCookie = pagingCookie;

}

resp = service.RetrieveMultiple(query);

if (resp.MoreRecords)

{

pageNumber++;

pagingCookie = resp.PagingCookie;

}

//Add the result from RetrieveMultiple to the List to be returned.

result.AddRange(resp.Entities);

}

while (resp.MoreRecords);

return result;

}

public static IOrganizationService ConnectToCRM()

{

//string CRMApplicationURL = "";

//string CRMUserName = "";

//string CRMPassword = "";

//string CRMDomain = "";

//IOrganizationService _crmService = GetCRMServiceConnection(CRMApplicationURL,

// CRMUserName, CRMPassword,

// CRMDomain);

IOrganizationService _crmService = GetCRMServiceConnection(ConfigurationManager.AppSettings["CRMApplicationURL"].ToString(),

ConfigurationManager.AppSettings["CRMUserName"].ToString(), ConfigurationManager.AppSettings["CRMPassword"].ToString());

//CrmServiceClient _crmService = new CrmServiceClient(ConfigurationManager.ConnectionStrings["appSettings"].ConnectionString);

// IOrganizationService _crmService=GetCRMServiceConnection()

return _crmService;

}

private static IOrganizationService GetCRMServiceConnection(string crmURL, string crmUserName, string crmPassword)

{

var clientCredentials = new ClientCredentials();

clientCredentials.UserName.UserName = crmUserName;

clientCredentials.UserName.Password = crmPassword;

ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12;

OrganizationServiceProxy orgServiceProxy = new OrganizationServiceProxy(new Uri(crmURL), null, clientCredentials, null);

// ((OrganizationServiceProxy)(orgServiceProxy)).Timeout = new TimeSpan(0, 30, 0);

orgServiceProxy.EnableProxyTypes();

if (orgServiceProxy == null)

{

Console.Write("Connection Error!!!"); return null;

}

Console.Write("Connected!");

orgServiceProxy.Timeout = TimeSpan.MaxValue;

Console.Write("Connected 2!");

return orgServiceProxy;

}

}

}

It would be helpful if any idea ?

Thanks,

Jharana