/// <summary>
/// Deletes the dropdownvalues.
/// </summary>
/// <param name="dropdownValueMapDb">The dropdown value map database.</param>
/// <param name="db">The database.</param>
/// <returns></returns>
private string DeleteDropdownvalues(DropdownValueDb dropdownValueMapDb, CRMDb crmdb)
{
var db = ObjectContextFactory.Create();
string response = "";
if (dropdownValueMapDb.DropdownID == (short)DropdownFieldTypes.AddressType)
{
var sql = @"select count(1) from Addresses(nolock) a
join contactaddressmap(nolock) cam on cam.AddressID = a.AddressID
join contacts(nolock) c on c.ContactID = cam.ContactID
where c.AccountID = @AccountID and c.IsDeleted = 0 and a.AddressTypeID = @AddressTypeID";
var count = db.Get <int>(sql, new { AccountID = dropdownValueMapDb.AccountID, AddressTypeID = dropdownValueMapDb.DropdownValueID }).FirstOrDefault();
if (count > 0)
{
response = "The Address type you are trying to delete is currently associated to one or more Contacts and cannot be deleted. Please set to Inactive if you no longer wish to use it.";
}
}
else if (dropdownValueMapDb.DropdownID == (short)DropdownFieldTypes.PhoneNumberType)
{
var sql = @"select count(1) from ContactPhoneNumbers(nolock) cp
join contacts(nolock) c on c.ContactID = cp.ContactID
where c.AccountID = @AccountID and c.IsDeleted = 0 and cp.PhoneType = @PhoneTypeID";
var count = db.Get <int>(sql, new { AccountID = dropdownValueMapDb.AccountID, PhoneTypeID = dropdownValueMapDb.DropdownValueID }).FirstOrDefault();
if (count > 0)
{
response = "The Phone number type you are trying to delete is currently associated to one or more Contacts and cannot be deleted. Please set to Inactive if you no longer wish to use it.";
}
}
else if (dropdownValueMapDb.DropdownID == (short)DropdownFieldTypes.LifeCycle)
{
var isLifecycleStage = db.Contacts.Where(at => at.LifecycleStage == dropdownValueMapDb.DropdownValueID && at.AccountID == dropdownValueMapDb.AccountID && !at.IsDeleted).Count();
if (isLifecycleStage > 0)
{
response = "The Life Cycle you are trying to delete is currently associated to one or more Contacts and cannot be deleted. Please set to Inactive if you no longer wish to use it.";
}
}
else if (dropdownValueMapDb.DropdownID == (short)DropdownFieldTypes.LeadSources)
{
var sql = @"select count(1) from ContactLeadSourceMap(nolock) CLM
join contacts(nolock) c on c.ContactID = CLM.ContactID
where c.AccountID = @AccountID and c.IsDeleted = 0 and CLM.LeadSouceID = @LeadSourceID";
var count = db.Get <int>(sql, new { AccountID = dropdownValueMapDb.AccountID, LeadSourceID = dropdownValueMapDb.DropdownValueID }).FirstOrDefault();
if (count > 0)
{
response = "The Lead Source you are trying to delete is currently associated to one or more Contacts and cannot be deleted. Please set to Inactive if you no longer wish to use it.";
}
}
else if (dropdownValueMapDb.DropdownID == (short)DropdownFieldTypes.Community)
{
int count = 0;
var sql = @"select count(1) from ContactCommunityMap(nolock) CCM
join contacts(nolock) c on c.ContactID = CCM.ContactID
where c.AccountID = @AccountID and c.IsDeleted = 0 and CCM.CommunityID = @CommunityID AND
CCM.IsDeleted = 0";
count = db.Get <int>(sql, new { AccountID = dropdownValueMapDb.AccountID, CommunityID = dropdownValueMapDb.DropdownValueID }).FirstOrDefault();
if (count == 0)
{
var sql1 = @"select count(1) from Tours(nolock) T
join contacttourmap(nolock) ctm on ctm.TourID = T.TourID
join contacts(nolock) c on c.ContactID = ctm.ContactID
where c.AccountID = @AccountID and c.IsDeleted = 0 and T.CommunityID = @CommunityID";
count += db.Get <int>(sql1, new { AccountID = dropdownValueMapDb.AccountID, CommunityID = dropdownValueMapDb.DropdownValueID }).FirstOrDefault();
}
if (count > 0)
{
response = "The Community you are trying to delete is currently associated to one or more Contacts and cannot be deleted. Please set to Inactive if you no longer wish to use it.";
}
}
else if (dropdownValueMapDb.DropdownID == (short)DropdownFieldTypes.TourType)
{
var sql = @"select count(1) from Tours(nolock) T
join contacttourmap(nolock) ctm on ctm.TourID = T.TourID
join contacts(nolock) c on c.ContactID = ctm.ContactID
where c.AccountID = @AccountID and c.IsDeleted = 0 and T.TourType = @TourTypeID";
var count = db.Get <int>(sql, new { AccountID = dropdownValueMapDb.AccountID, TourTypeID = dropdownValueMapDb.DropdownValueID }).FirstOrDefault();
if (count > 0)
{
response = "The Tour type you are trying to delete is currently associated to one or more Contacts and cannot be deleted. Please set to Inactive if you no longer wish to use it.";
}
}
else if (dropdownValueMapDb.DropdownID == (short)DropdownFieldTypes.ActionType)
{
var sql = @"select count(1) from Actions(nolock) A
join ContactActionMap(nolock) CAM ON CAM.ActionID = A.ActionID
join contacts(nolock) c on c.ContactID = CAM.ContactID
where c.AccountID = @AccountID and c.IsDeleted = 0 and A.ActionType = @ActionTypeID";
var count = db.Get <int>(sql, new { AccountID = dropdownValueMapDb.AccountID, ActionTypeID = dropdownValueMapDb.DropdownValueID }).FirstOrDefault();
if (count > 0)
{
response = "The Action type you are trying to delete is currently associated to one or more Contacts and cannot be deleted. Please set to Inactive if you no longer wish to use it.";
}
}
else if (dropdownValueMapDb.DropdownID == (short)DropdownFieldTypes.OpportunityStage)
{
var sql = @"select count(1) from OpportunityContactMap(nolock) T
join contacts(nolock) c on c.ContactID = T.ContactID
where c.AccountID = @AccountID and c.IsDeleted = 0 and T.StageID = @StageID";
var count = db.Get <int>(sql, new { AccountID = dropdownValueMapDb.AccountID, StageID = dropdownValueMapDb.DropdownValueID }).FirstOrDefault();
if (count > 0)
{
response = "The Opportunity Stage you are trying to delete is currently associated to one or more Contacts and cannot be deleted. Please set to Inactive if you no longer wish to use it.";
}
}
else if (dropdownValueMapDb.DropdownID == (short)DropdownFieldTypes.RelationshipType)
{
var sql = @"SELECT Count(1) FROM [dbo].[ContactRelationshipMap] (NOLOCK) CRM
JOIN Contacts (NOLOCK) C ON C.ContactID = CRM.ContactID
WHERE C.IsDeleted = 0 AND C.AccountID = @AccountID AND CRM.RelationshipType = @RelationshipTypeID";
var count = db.Get <int>(sql, new { AccountID = dropdownValueMapDb.AccountID, RelationshipTypeID = dropdownValueMapDb.DropdownValueID }).FirstOrDefault();
if (count > 0)
{
response = "The Relationship type you are trying to delete is currently associated to one or more Contacts and cannot be deleted. Please set to Inactive if you no longer wish to use it.";
}
}
else if (dropdownValueMapDb.DropdownID == (short)DropdownFieldTypes.PartnerType)
{
var isPartnerType = db.Contacts.Where(at => at.PartnerType == dropdownValueMapDb.DropdownValueID && at.AccountID == dropdownValueMapDb.AccountID && !at.IsDeleted).Count();
if (isPartnerType > 0)
{
response = "The Partner type you are trying to delete is currently associated to one or more Contacts and cannot be deleted. Please set to Inactive if you no longer wish to use it.";
}
}
else if (dropdownValueMapDb.DropdownID == (short)DropdownFieldTypes.NoteCategory)
{
var sql = @"select count(1) from Notes(nolock) N
join ContactNoteMap(nolock) cnm ON cnm.NoteID = N.NoteID
join contacts(nolock) c on c.ContactID = cnm.ContactID
where c.AccountID = @AccountID and c.IsDeleted = 0 and N.NoteCategory = @NoteCategory";
var count = db.Get <int>(sql, new { AccountID = dropdownValueMapDb.AccountID, NoteCategory = dropdownValueMapDb.DropdownValueID }).FirstOrDefault();
if (count > 0)
{
response = "The Note Category you are trying to delete is currently associated to one or more Contacts and cannot be deleted. Please set to Inactive if you no longer wish to use it.";
}
}
return(response);
}
/// <summary>
/// Persists the advanced search filter map.
/// </summary>
/// <param name="searchDefinition">The search definition.</param>
/// <param name="SearchDefinitionsDb">The search definitions database.</param>
/// <param name="db">The database.</param>
void PersistAdvancedSearchFilterMap(SearchDefinition searchDefinition, SearchDefinitionsDb SearchDefinitionsDb, CRMDb db)
{
var searchFiltersMap = db.SearchFilters.Where(c => c.SearchDefinitionID == searchDefinition.Id).ToList();
if (searchDefinition.Filters != null && searchDefinition.IsPreConfiguredSearch == false)
{
foreach (var filter in searchDefinition.Filters)
{
if (filter.SearchFilterId != 0)
{
var searchFilterMap = searchFiltersMap.SingleOrDefault(r => r.SearchFilterID == filter.SearchFilterId);
searchFilterMap.SearchDefinitionID = filter.SearchDefinitionID;
searchFilterMap.SearchQualifierTypeID = (short)filter.Qualifier;
searchFilterMap.FieldID = filter.IsDropdownField ? (int?)null : (int)filter.Field;
searchFilterMap.SearchText = filter.SearchText;
searchFilterMap.IsCustomField = filter.IsCustomField;
searchFilterMap.IsDropdownField = filter.IsDropdownField;
searchFilterMap.IsDateTime = filter.IsDropdownField;
searchFilterMap.DropdownId = filter.DropdownId;
searchFilterMap.DropdownValueID = filter.IsDropdownField ? filter.DropdownValueId : null;
}
else
{
short?dropdownvalue = null;
int? fieldId = null;
if (!filter.IsDropdownField)
{
fieldId = (int)filter.Field;
}
else
{
dropdownvalue = filter.DropdownValueId;
}
db.SearchFilters.Add(new SearchFiltersDb
{
FieldID = fieldId,
SearchDefinitionID = SearchDefinitionsDb.SearchDefinitionID,
SearchQualifierTypeID = (short)filter.Qualifier,
SearchText = filter.SearchText,
IsCustomField = filter.IsCustomField,
IsDropdownField = filter.IsDropdownField,
IsDateTime = filter.IsDateTime,
DropdownId = filter.DropdownId,
DropdownValueID = dropdownvalue
});
}
}
if (searchDefinition.Id > 0)
{
IList <short> searchFilterIds = searchDefinition.Filters.Where(n => n.SearchFilterId > 0).Select(n => n.SearchFilterId).ToList();
var unMapSearchFilters = searchFiltersMap.Where(n => !searchFilterIds.Contains(n.SearchFilterID));
db.SearchFilters.RemoveRange(unMapSearchFilters);
}
}
if (searchDefinition.IsPreConfiguredSearch == true)
{
int filterCount = 0;
var phoneDropdownValues = new DropdownValueDb[] { };
if (searchDefinition.Name == "Call List")
{
phoneDropdownValues = db.DropdownValues.Where(d => d.AccountID == searchDefinition.AccountID && d.DropdownID == (short)DropdownFieldTypes.PhoneNumberType).ToArray();
}
foreach (var filter in searchDefinition.Filters)
{
filterCount = filterCount + 1;
short?dropdownvalue = null;
int? fieldId = null;
if (!filter.IsDropdownField)
{
fieldId = (int)filter.Field;
}
else
{
dropdownvalue = filter.DropdownValueId;
}
if (filter.Field == ContactFields.LifecycleStageField)
{
var dropdownValueId = db.DropdownValues.Where(d => d.AccountID == searchDefinition.AccountID && d.DropdownValueTypeID == (short)DropdownValueTypes.Customer).SingleOrDefault().DropdownValueID;
filter.SearchText = dropdownValueId.ToString();
}
if (searchDefinition.Filters.Count() == 3)
{
filter.DropdownValueId = (short)phoneDropdownValues[filterCount - 1].DropdownValueID;
dropdownvalue = filter.DropdownValueId;
}
db.SearchFilters.Add(new SearchFiltersDb
{
FieldID = fieldId,
SearchDefinitionID = SearchDefinitionsDb.SearchDefinitionID,
SearchQualifierTypeID = (short)filter.Qualifier,
SearchText = filter.SearchText,
IsCustomField = filter.IsCustomField,
IsDropdownField = filter.IsDropdownField,
IsDateTime = filter.IsDateTime,
DropdownId = filter.DropdownId,
DropdownValueID = dropdownvalue
});
}
}
}
/// <summary>
/// Persists the dropdown value.
/// </summary>
/// <param name="dropdown">The dropdown.</param>
/// <param name="dropdownDb">The dropdown database.</param>
/// <param name="db">The database.</param>
/// <exception cref="LandmarkIT.Enterprise.Utilities.ExceptionHandling.UnsupportedOperationException"></exception>
private void PersistDropdownValue(Dropdown dropdown, DropdownDb dropdownDb, CRMDb db)
{
var dropDownValues = db.DropdownValues.Where(c => (c.DropdownID == dropdown.Id) && (c.AccountID == dropdown.AccountID) && c.IsDeleted == false).ToList();
if (dropdown.DropdownValues != null)
{
foreach (var dropdwn in dropdown.DropdownValues)
{
if (dropdwn.Id != 0)
{
var dropdownValueMap = dropDownValues.SingleOrDefault(r => r.DropdownValueID == dropdwn.Id);
dropdownValueMap.DropdownValue = dropdwn.Value;
dropdownValueMap.IsDefault = dropdwn.IsDefault;
dropdownValueMap.SortID = dropdwn.SortID;
dropdownValueMap.IsActive = dropdwn.IsActive;
dropdownValueMap.DropdownValueTypeID = dropdwn.DropdownValueTypeID;
if (dropdown.Id == 6)
{
var opoortunityStageGroup = db.OpportunityStageGroupsDb.Where(c => c.DropdownValueID == dropdwn.Id && c.AccountID == dropdwn.AccountID).FirstOrDefault();
if (opoortunityStageGroup != null)
{
opoortunityStageGroup.OpportunityGroupID = dropdwn.OpportunityGroupID;
}
}
}
else
{
DropdownValueDb map = new DropdownValueDb();
map.DropdownID = dropdwn.DropdownID;
map.AccountID = dropdwn.AccountID;
map.IsDefault = dropdwn.IsDefault;
map.DropdownValue = dropdwn.Value;
map.SortID = dropdwn.SortID;
map.IsActive = dropdwn.IsActive;
map.IsDeleted = false;
map.DropdownValueTypeID = dropdwn.DropdownValueTypeID;
if (dropdown.Id == 6)
{
OpportunityStageGroupsDb grp = new OpportunityStageGroupsDb()
{
DropdownValues = map,
AccountID = (int)dropdwn.AccountID,
OpportunityGroupID = dropdwn.OpportunityGroupID
};
map.OpportunityStageGroups = new List <OpportunityStageGroupsDb>();
map.OpportunityStageGroups.Add(grp);
}
db.DropdownValues.Add(map);
}
}
IList <short> dropdownvalues = dropdown.DropdownValues.Where(d => d.Id > 0).Select(d => d.Id).ToList();
var unMapDropdownValues = dropDownValues.Where(n => !dropdownvalues.Contains(n.DropdownValueID));
foreach (DropdownValueDb dropdownValueMapDb in unMapDropdownValues)
{
string temp = DeleteDropdownvalues(dropdownValueMapDb, db);
if (temp == "")
{
dropdownValueMapDb.IsDeleted = true;
db.SaveChanges();
var GroupDb = db.OpportunityStageGroupsDb.Where(p => p.DropdownValueID == dropdownValueMapDb.DropdownValueID).FirstOrDefault();
if (GroupDb != null)
{
db.OpportunityStageGroupsDb.Remove(GroupDb);
}
}
else
{
throw new UnsupportedOperationException(temp);
}
}
}
}
