private AttributeMetadata CreateLookupAttribute(ExcelWorksheet sheet, int rowIndex, int startCell, AttributeMetadata fakeAmd, ProcessResult info, bool isCreate)
{
var amc = new AssociatedMenuConfiguration
{
Order = sheet.GetValue <int>(rowIndex, startCell + 6),
Group = new AssociatedMenuGroup?()
};
switch (sheet.GetValue <string>(rowIndex, startCell + 5))
{
case "Details":
amc.Group = AssociatedMenuGroup.Details;
break;
case "Sales":
amc.Group = AssociatedMenuGroup.Sales;
break;
case "Service":
amc.Group = AssociatedMenuGroup.Service;
break;
case "Marketing":
amc.Group = AssociatedMenuGroup.Marketing;
break;
}
switch (sheet.GetValue <string>(rowIndex, startCell + 3))
{
case "Do not display":
amc.Behavior = AssociatedMenuBehavior.DoNotDisplay;
break;
case "Custom label":
amc.Behavior = AssociatedMenuBehavior.UseLabel;
if (!string.IsNullOrEmpty(sheet.GetValue <string>(rowIndex, startCell + 4)))
{
amc.Label = new Label(sheet.GetValue <string>(rowIndex, startCell + 4), settings.LanguageCode);
}
else
{
throw new Exception("If display behavior is \"Custom Label\", the label must be specified");
}
break;
default:
amc.Behavior = AssociatedMenuBehavior.UseCollectionName;
break;
}
var cc = new CascadeConfiguration();
switch (sheet.GetValue <string>(rowIndex, startCell + 7))
{
case "Parental":
cc.Assign = CascadeType.Cascade;
cc.Delete = CascadeType.Cascade;
cc.Merge = CascadeType.Cascade;
cc.Reparent = CascadeType.Cascade;
cc.RollupView = CascadeType.NoCascade;
cc.Share = CascadeType.Cascade;
cc.Unshare = CascadeType.Cascade;
break;
case "Referential, restrict delete":
cc.Assign = CascadeType.NoCascade;
cc.Delete = CascadeType.Restrict;
cc.Merge = CascadeType.NoCascade;
cc.Reparent = CascadeType.NoCascade;
cc.RollupView = CascadeType.NoCascade;
cc.Share = CascadeType.NoCascade;
cc.Unshare = CascadeType.NoCascade;
break;
case "Custom":
cc.RollupView = CascadeType.NoCascade;
cc.Assign = GetCascade(sheet.GetValue <string>(rowIndex, startCell + 8));
cc.Share = GetCascade(sheet.GetValue <string>(rowIndex, startCell + 9));
cc.Unshare = GetCascade(sheet.GetValue <string>(rowIndex, startCell + 10));
cc.Reparent = GetCascade(sheet.GetValue <string>(rowIndex, startCell + 11));
cc.Delete = GetCascade(sheet.GetValue <string>(rowIndex, startCell + 12));
cc.Merge = GetCascade(sheet.GetValue <string>(rowIndex, startCell + 13));
break;
default:
cc.Assign = CascadeType.NoCascade;
cc.Delete = CascadeType.RemoveLink;
cc.Merge = CascadeType.NoCascade;
cc.Reparent = CascadeType.NoCascade;
cc.RollupView = CascadeType.NoCascade;
cc.Share = CascadeType.NoCascade;
cc.Unshare = CascadeType.NoCascade;
break;
}
var lookup = new LookupAttributeMetadata
{
DisplayName = fakeAmd.DisplayName,
SchemaName = fakeAmd.SchemaName,
LogicalName = fakeAmd.LogicalName,
IsValidForAdvancedFind = fakeAmd.IsValidForAdvancedFind,
RequiredLevel = fakeAmd.RequiredLevel,
IsSecured = fakeAmd.IsSecured,
IsAuditEnabled = fakeAmd.IsAuditEnabled,
Targets = new[] { sheet.GetValue <string>(rowIndex, startCell).ToLower() }
};
if (settings.AddLookupSuffix && !lookup.SchemaName.ToLower().EndsWith("id"))
{
lookup.SchemaName = $"{lookup.SchemaName}Id";
lookup.LogicalName = lookup.SchemaName.ToLower();
}
if (fakeAmd.Description != null)
{
lookup.Description = fakeAmd.Description;
}
var relationship = new OneToManyRelationshipMetadata
{
IsValidForAdvancedFind = sheet.GetValue <string>(rowIndex, startCell + 1) == "Yes",
SchemaName =
$"{settings.Solution.Prefix}{info.Entity}_{sheet.GetValue<string>(rowIndex, startCell)}_{lookup.SchemaName}",
AssociatedMenuConfiguration = amc,
CascadeConfiguration = cc,
IsHierarchical = sheet.GetValue <string>(rowIndex, startCell + 2) == "Yes",
ReferencedEntity = sheet.GetValue <string>(rowIndex, startCell),
ReferencingEntity = info.Entity,
SecurityTypes = SecurityTypes.Append,
};
if (isCreate)
{
service.Execute(new CreateOneToManyRequest
{
OneToManyRelationship = relationship,
Lookup = lookup,
SolutionUniqueName = settings.Solution.UniqueName
});
info.IsCreate = true;
return(null);
}
service.Execute(new UpdateRelationshipRequest
{
Relationship = relationship,
SolutionUniqueName = settings.Solution.UniqueName,
MergeLabels = true
});
return(lookup);
}
public void Process(BackgroundWorker worker, ConnectionDetail detail)
{
var eiCache = new List <EntityInfo>();
byte[] file = File.ReadAllBytes(settings.FilePath);
using (MemoryStream ms = new MemoryStream(file))
using (ExcelPackage package = new ExcelPackage(ms))
{
ExcelWorksheet workSheet = package.Workbook.Worksheets.First();
int percent = 0;
int index = 0;
for (int i = 3; i <= workSheet.Dimension.End.Row; i++)
{
if (string.IsNullOrEmpty(workSheet.GetValue <string>(i, TypeCellIndex)) ||
workSheet.GetValue <string>(i, 1) == "Ignore")
{
continue;
}
if (worker.CancellationPending)
{
return;
}
index++;
var info = new ProcessResult
{
DisplayName = workSheet.GetValue <string>(i, DisplayNameCellIndex),
Attribute = workSheet.GetValue <string>(i, SchemaNameCellIndex),
Type = workSheet.GetValue <string>(i, TypeCellIndex),
Entity = workSheet.GetValue <string>(i, EntityCellIndex),
Processing = true,
};
if ((info.Type == "Customer" || info.Type == "Lookup") && settings.AddLookupSuffix && !info.Attribute.EndsWith("Id"))
{
info.Attribute = $"{info.Attribute}Id";
}
if (info.Type == "OptionSet" && settings.AddOptionSetSuffix && !info.Attribute.ToLower().EndsWith("code"))
{
info.Attribute = $"{info.Attribute}Code";
}
worker.ReportProgress(percent, info);
percent = index * 100 / (workSheet.Dimension.End.Row - 2);
try
{
AttributeMetadata amd = null;
var fakeAmd = GetFakeAmd(info, workSheet, i);
// Check validity for an Update
var ei = eiCache.FirstOrDefault(e => e.Name == info.Entity);
if (ei == null)
{
ei = new EntityInfo(info.Entity, service);
eiCache.Add(ei);
}
var existingAttribute = ei.Attributes.FirstOrDefault(a => a.LogicalName == info.Attribute.ToLower());
if (existingAttribute != null)
{
fakeAmd.MetadataId = existingAttribute.MetadataId;
}
var type = workSheet.GetValue <string>(i, TypeCellIndex);
switch (type)
{
case "Single line of text":
if (!string.IsNullOrEmpty(workSheet.GetValue <string>(i, PropertiesFirstCellIndex + 2)) && detail.OrganizationMajorVersion < 9)
{
throw new Exception(
"Autonumber attributes can only be created in a version 9 or above of Microsoft Dynamics 365");
}
amd = CreateStringAttribute(workSheet, i, PropertiesFirstCellIndex);
break;
case "OptionSet":
amd = CreateOptionsetAttribute(workSheet, i, PropertiesFirstCellIndex + 4, false, info.DisplayName, info.Attribute, info.Entity, fakeAmd.Description?.LocalizedLabels[0]?.Label, (existingAttribute as PicklistAttributeMetadata)?.OptionSet);
break;
case "Multiselect OptionSet":
if (detail.OrganizationMajorVersion < 9)
{
throw new Exception(
"Multiselect OptionSet can only be created in a version 9 or above of Microsoft Dynamics 365");
}
amd = CreateOptionsetAttribute(workSheet, i, PropertiesFirstCellIndex + 4, true, info.DisplayName, info.Attribute, info.Entity, fakeAmd.Description?.LocalizedLabels[0]?.Label, (existingAttribute as PicklistAttributeMetadata)?.OptionSet);
break;
case "Two options":
amd = CreateBooleanAttribute(workSheet, i, PropertiesFirstCellIndex + 8);
break;
case "Whole number":
amd = CreateNumberAttribute(workSheet, i, PropertiesFirstCellIndex + 11);
break;
case "Float number":
amd = CreateFloatAttribute(workSheet, i, PropertiesFirstCellIndex + 15);
break;
case "Decimal number":
amd = CreateDecimalAttribute(workSheet, i, PropertiesFirstCellIndex + 19);
break;
case "Money":
amd = CreateMoneyAttribute(workSheet, i, PropertiesFirstCellIndex + 23);
break;
case "Multiple lines of text":
amd = CreateMemoAttribute(workSheet, i, PropertiesFirstCellIndex);
break;
case "Date and time":
amd = CreateDateTimeAttribute(workSheet, i, PropertiesFirstCellIndex + 27);
break;
case "Lookup":
amd = CreateLookupAttribute(workSheet, i, PropertiesFirstCellIndex + 30, fakeAmd, info, !fakeAmd.MetadataId.HasValue);
break;
case "Customer":
amd = CreateCustomerAttribute(workSheet, i, PropertiesFirstCellIndex + 31, fakeAmd, existingAttribute, info, !fakeAmd.MetadataId.HasValue);
break;
case "File":
amd = CreateFileAttribute(workSheet, i, PropertiesFirstCellIndex + 45);
break;
case "Image":
amd = CreateImageAttribute(workSheet, i, PropertiesFirstCellIndex + 47);
break;
}
if (amd == null)
{
info.Success = true;
info.Processing = false;
worker.ReportProgress(percent, info);
continue;
}
if (existingAttribute != null)
{
if (existingAttribute.GetType() != amd.GetType())
{
throw new Exception(
@"Attribute in Excel file is not of same type as existing attribute in organization");
}
}
amd.DisplayName = fakeAmd.DisplayName;
amd.SchemaName = fakeAmd.SchemaName;
amd.LogicalName = fakeAmd.LogicalName;
amd.IsValidForAdvancedFind = fakeAmd.IsValidForAdvancedFind;
amd.IsSecured = fakeAmd.IsSecured;
amd.IsAuditEnabled = fakeAmd.IsAuditEnabled;
amd.SourceType = fakeAmd.SourceType;
amd.MetadataId = fakeAmd.MetadataId;
amd.RequiredLevel = fakeAmd.RequiredLevel;
if (fakeAmd.Description != null)
{
amd.Description = fakeAmd.Description;
}
info.Attribute = amd.SchemaName;
OrganizationRequest request;
if (amd.MetadataId.HasValue)
{
request = new UpdateAttributeRequest
{
Attribute = amd,
EntityName = info.Entity,
SolutionUniqueName = settings.Solution.UniqueName,
MergeLabels = true
};
info.IsCreate = false;
}
else
{
request = new CreateAttributeRequest
{
Attribute = amd,
EntityName = info.Entity,
SolutionUniqueName = settings.Solution.UniqueName
};
info.IsCreate = true;
}
try
{
service.Execute(request);
info.Success = true;
info.Processing = false;
worker.ReportProgress(percent, info);
}
catch (FaultException <OrganizationServiceFault> error)
{
// Special handle for file attribute as they are not returned by the query
if (info.IsCreate && error.Detail.ErrorCode == -2147192813)
{
request = new UpdateAttributeRequest
{
Attribute = amd,
EntityName = info.Entity,
SolutionUniqueName = settings.Solution.UniqueName,
MergeLabels = true
};
info.IsCreate = false;
service.Execute(request);
info.Success = true;
info.Processing = false;
worker.ReportProgress(percent, info);
}
else
{
info.Success = false;
info.Processing = false;
info.Message = error.Message;
worker.ReportProgress(percent, info);
}
}
}
catch (Exception e)
{
info.Success = false;
info.Processing = false;
info.Message = e.Message;
worker.ReportProgress(percent, info);
}
}
}
}