2,665
Hi Experts,
I am running one console application which will read from excel & create records in crm.
Like in excel 3 sheets i have suppose case,Action,Interview.Here Case is having around 10k records,then Action is having 2000 records & Interview is having 200 records .If i run the console is taking too much time to create all these records for 100 records its taking 5 mins.
How can i decrease the time to create all these records is there any way available or method to use ?
Thanks,
Jharana
Hi Jharana,
Please try to reduce the batch size to 100.
because after reading multiple site 502 bad gateway error refers to temporary and can be resolved after re-submitting the request.
reference snap:
Not sure. May be when we run the console app it sends the multiple batch request as a result Overload scenario may occur.
If possible add some wait in console app after submitting each batch request
Thanks,
Pradeep.
Hi Pradeep,
Thanks for the response.
1. at a time its processing sometimes 2000 or 2500 & batch wise 300 records creating. If am taking batch size to 500 or 1000 then it will create till 1000 or 500 records & will not increament the next batch size number.i f taking 300 then its creating for some period of time.
And then i am doing agin till where its created i am skipping till that rows records & taking again rest of the row records & creating.
2. Manually once need to run.
Thanks,
Jharana
Huge code.
I will check.
Also, help me with below questions:
1. how many records processed successfully and Max record count?
2. This console app going to run daily basis or manually?
Thanks.
Pradeep
Hi Pradeep,
I have used the below code but sometimes its going out from the loop used in batchwise createing case records
what could be the cause already i am using timespan duration to increase the value.getting issue in logg file The remote server returned an unexpected response 502 Bad gateway.
below code i am using :
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;
}
}
}
Hi,
I have below two suggestions:
1. Use of Execute Multiple Request: In console app use "ExecuteMultipleRequest" class to send builk data. This will reduce the round trip and performance will get improve.
For Reference:
Links:
https://docs.microsoft.com/en-us/powerapps/developer/data-platform/best-practices/business-logic/avoid-batch-requests-plugin
https://docs.microsoft.com/en-us/powerapps/developer/data-platform/org-service/execute-multiple-requests
2. You can create SSIS package to read data from excel file and perform Upsert operation in dynamic instances.
Thanks,
Pradeep.
Please mark this as VERIFIED if it helps.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,269
Super User 2024 Season 2
#2
Martin Dráb
230,198
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (