/// <summary>
/// Inserts the suppressed domains list.
/// </summary>
/// <param name="suppressedDomains">The suppressed domains.</param>
/// <returns></returns>
public IEnumerable <SuppressedDomain> InsertSuppressedDomainsList(IEnumerable <SuppressedDomain> suppressedDomains)
{
var db = ObjectContextFactory.Create();
if (suppressedDomains.IsAny())
{
List <SuppressedDomainsDb> domains = new List <SuppressedDomainsDb>();
foreach (SuppressedDomain domain in suppressedDomains)
{
SuppressedDomainsDb domainDb = Mapper.Map <SuppressedDomain, SuppressedDomainsDb>(domain);
domains.Add(domainDb);
}
db.SuppressedDomains.AddRange(domains);
db.SaveChanges();
return(Mapper.Map <List <SuppressedDomainsDb>, List <SuppressedDomain> >(domains));
}
else
{
return(null);
}
}
/// <summary>
/// Inserts the suppressed emails list.
/// </summary>
/// <param name="suppressedEmails">The suppressed emails.</param>
/// <returns></returns>
public IEnumerable <SuppressedEmail> InsertSuppressedEmailsList(IEnumerable <SuppressedEmail> suppressedEmails)
{
var db = ObjectContextFactory.Create();
if (suppressedEmails.IsAny())
{
List <SuppressedEmailsDb> dbObjects = new List <SuppressedEmailsDb>();
foreach (SuppressedEmail email in suppressedEmails)
{
SuppressedEmailsDb emaildb = Mapper.Map <SuppressedEmail, SuppressedEmailsDb>(email);
dbObjects.Add(emaildb);
}
db.SuppressedEmails.AddRange(dbObjects);
db.SaveChanges();
return(Mapper.Map <List <SuppressedEmailsDb>, List <SuppressedEmail> >(dbObjects));
}
else
{
return(null);
}
}
/// <summary>
/// Gets the web visitreport by web visit identifier.
/// </summary>
/// <param name="webVisitId">The web visit identifier.</param>
/// <returns></returns>
public WebVisitReport GetWebVisitreportByWebVisitId(int webVisitId)
{
var db = ObjectContextFactory.Create();
var sql = @"select
wv.ContactWebVisitID, wv.ContactId,wv.VisitReference,ISNULL(c.FirstName,'') FirstName, ISNULL(c.LastName,'') LastName,
ISNULL(ep.email,'') Email, ep.PhoneNumber Phone,ep.Zip Zip,c.lifecyclestage LifecycleStageId,c.LeadScore, VisitedOn, PageViews,
Duration, vs.Page1, ISNULL(vs.Page2,'') Page2, ISNULL(vs.Page3,'') Page3, wv.Location,Source,c.OwnerID, ep.LeadSource LeadSource
from (select ContactWebVisitID,contactid, VisitReference, max(visitedon) VisitedOn, sum(duration) Duration, count(visitreference) PageViews,ISNULL(Max(city +', ' + Region),'') Location,
ISNULL(Max(ISPName),'') Source
from contactwebvisits cw (nolock)
where isnull(VisitReference, '000') <> '000'
group by ContactWebVisitID,visitreference, contactid
) wv
left join GET_VisiStat_Top3_Pages vs (nolock) on wv.VisitReference = vs.VisitReference
left join contacts c (nolock) on wv.contactid = c.contactid
left join GET_Contact_Email_Phone_Zip_PrimaryLeadSource ep (nolock) on wv.ContactID = ep.contactid
where wv.contactwebvisitId=@contactwebvisitId ";
var webVisitReport = db.Get <WebVisitReport>(sql, new { ContactWebVisitID = webVisitId }).FirstOrDefault();
return(webVisitReport);
}
/// <summary>
/// Finds all.
/// </summary>
/// <param name="name">The name.</param>
/// <param name="limit">The limit.</param>
/// <param name="pageNumber">The page number.</param>
/// <param name="accountId">The account identifier.</param>
/// <returns></returns>
public IEnumerable <Dropdown> FindAll(string name, int limit, int pageNumber, int accountId)
{
var predicate = PredicateBuilder.True <DropdownDb>();
var records = (pageNumber - 1) * limit;
if (!string.IsNullOrEmpty(name))
{
name = name.ToLower();
predicate = predicate.And(a => a.DropdownName.Contains(name));
// predicate=predicate.And()
}
IEnumerable <DropdownDb> dropdownValuesDb = findDropdownValuesSummary(predicate, accountId).Skip(records).Take(limit);
Logger.Current.Informational("Fetched dropdownvalues for accountId : " + accountId);
foreach (DropdownDb dv in dropdownValuesDb)
{
foreach (var item in dv.DropdownValues)
{
ICollection <OpportunityStageGroupsDb> OpportunityStageGroups = ObjectContextFactory.Create().OpportunityStageGroupsDb.Where(p => p.DropdownValueID == item.DropdownValueID).AsExpandable()
.Select(a => new
{
AccountID = a.AccountID,
DropdownValueID = a.DropdownValueID,
OpportunityGroupID = a.OpportunityGroupID,
OpportunityStageGroupID = a.OpportunityStageGroupID
}).ToList().Select(x => new OpportunityStageGroupsDb
{
AccountID = x.AccountID,
DropdownValueID = x.DropdownValueID,
OpportunityGroupID = x.OpportunityGroupID,
OpportunityStageGroupID = x.OpportunityStageGroupID
}).ToList();
item.OpportunityStageGroups = OpportunityStageGroups;
Logger.Current.Informational("Fetched opportunity stage groups for dropdownvalueid : " + item.DropdownValueID);
}
yield return(ConvertToDomain(dv));
}
}
/// <summary>
/// Finds the by.
/// </summary>
/// <param name="DropdownID">The dropdown identifier.</param>
/// <param name="AccountID">The account identifier.</param>
/// <returns></returns>
public Dropdown FindBy(int DropdownID, int AccountID)
{
var db = ObjectContextFactory.Create();
DropdownDb dropdownValues = db.Dropdowns.Include("DropdownValues")
.AsExpandable()
.Where(i => i.DropdownID == DropdownID).OrderBy(c => c.DropdownID).Select(dv =>
new
{
DropdownID = dv.DropdownID,
DropdownName = dv.DropdownName,
DropdownValues = dv.DropdownValues.Where(i => (i.AccountID == AccountID) && i.IsDeleted == false).OrderBy(d => d.SortID).ToList()
}).ToList().Select(x => new DropdownDb
{
DropdownName = x.DropdownName,
DropdownID = x.DropdownID,
DropdownValues = x.DropdownValues
}).FirstOrDefault();
foreach (var item in dropdownValues.DropdownValues)
{
ICollection <OpportunityStageGroupsDb> OpportunityStageGroups = ObjectContextFactory.Create().OpportunityStageGroupsDb.Where(p => p.DropdownValueID == item.DropdownValueID).AsExpandable()
.Select(a => new
{
AccountID = a.AccountID,
DropdownValueID = a.DropdownValueID,
OpportunityGroupID = a.OpportunityGroupID,
OpportunityStageGroupID = a.OpportunityStageGroupID
}).ToList().Select(x => new OpportunityStageGroupsDb
{
AccountID = x.AccountID,
DropdownValueID = x.DropdownValueID,
OpportunityGroupID = x.OpportunityGroupID,
OpportunityStageGroupID = x.OpportunityStageGroupID
}).ToList();
item.OpportunityStageGroups = OpportunityStageGroups;
}
return(ConvertToDomain(dropdownValues));
}
public int CreateAPIForm(string formName, int accountId, int userId)
{
var db = ObjectContextFactory.Create();
FormsDb formDb = new FormsDb()
{
Name = formName,
Acknowledgement = "Thank you",
AcknowledgementType = AcknowledgementType.Message,
HTMLContent = "<div></div>",
Status = 201,
AccountID = accountId,
CreatedBy = userId,
CreatedOn = DateTime.UtcNow,
LastModifiedOn = DateTime.UtcNow,
LastModifiedBy = userId,
IsDeleted = false
};
db.Forms.Add(formDb);
db.SaveChanges();
return(formDb.FormID);
}
/// <summary>
/// Gets the web visit daily summary.
/// </summary>
/// <param name="accountId">The account identifier.</param>
/// <param name="startDate">The start date.</param>
/// <param name="endDate">The end date.</param>
/// <returns></returns>
public IEnumerable <WebVisitReport> GetWebVisitDailySummary(int accountId, DateTime startDate, DateTime endDate)
{
using (var db = ObjectContextFactory.Create())
{
var sql = @" select
wv.ContactId,c.LeadScore, wv.VisitReference,ISNULL(c.FirstName,'') FirstName, ISNULL(c.LastName,'') LastName,
ISNULL(ep.email,'') Email, ep.PhoneNumber Phone,ep.Zip Zip,c.lifecyclestage LifecycleStageId, VisitedOn, PageViews,
Duration, vs.Page1, ISNULL(vs.Page2,'') Page2, ISNULL(vs.Page3,'') Page3, wv.Location,Source,c.OwnerID
from(select contactid, VisitReference, max(visitedon) VisitedOn, sum(duration) Duration, count(visitreference) PageViews,ISNULL(Max(city +', ' + Region),'') Location,
ISNULL(Max(ISPName),'') Source
from contactwebvisits cw (nolock)
where isnull(VisitReference, '000') <> '000' and VisitedOn >= @startDate and VisitedOn <@endDate
group by visitreference, contactid
) wv
left join GET_VisiStat_Top3_Pages vs (nolock) on wv.VisitReference = vs.VisitReference
left join contacts c (nolock) on wv.contactid = c.contactid
left join GET_Contact_Email_Phone_Zip ep (nolock) on wv.ContactID = ep.contactid
where c.accountid = @accountId order by visitedon desc " ;
var visits = db.Get <WebVisitReport>(sql, new { accountId = accountId, startDate = startDate, endDate = endDate });
return(visits);
}
}
public void SaveColumnPreferences(int entityId, byte entityType, IEnumerable <int> fields, byte showingType)
{
if (fields != null && fields.Any())
{
var db = ObjectContextFactory.Create();
var oldColumns = db.AVColumnPreferences.Where(w => w.EntityID == entityId && w.EntityType == entityType);
db.AVColumnPreferences.RemoveRange(oldColumns);
IList <AVColumnPreferencesDb> Columns = new List <AVColumnPreferencesDb>();
foreach (var field in fields)
{
AVColumnPreferencesDb columnsDb = new AVColumnPreferencesDb();
columnsDb.EntityID = entityId;
columnsDb.EntityType = entityType;
columnsDb.FieldID = field;
columnsDb.FieldType = 1;
columnsDb.ShowingType = showingType;
Columns.Add(columnsDb);
}
db.AVColumnPreferences.AddRange(Columns);
db.SaveChanges();
}
}
/// <summary>
/// Determines whether [is duplicate lead adapter] [the specified lead adapter type].
/// </summary>
/// <param name="leadAdapterType">Type of the lead adapter.</param>
/// <param name="accountID">The account identifier.</param>
/// <param name="leadAdapterAndAccountMapId">The lead adapter and account map identifier.</param>
/// <returns></returns>
public bool IsDuplicateLeadAdapter(LeadAdapterTypes leadAdapterType, int accountID, int leadAdapterAndAccountMapId)
{
var db = ObjectContextFactory.Create();
LeadAdapterAndAccountMapDb leadAdapterDb;
if (leadAdapterAndAccountMapId == 0)
{
leadAdapterDb = db.LeadAdapters.Where(la => la.AccountID == accountID &&
la.LeadAdapterTypeID == (int)leadAdapterType &&
la.IsDelete == false)
.FirstOrDefault();
}
else
{
leadAdapterDb = db.LeadAdapters.Where(la => la.AccountID == accountID &&
la.LeadAdapterTypeID == (int)leadAdapterType &&
la.LeadAdapterAndAccountMapId != leadAdapterAndAccountMapId &&
la.IsDelete == false)
.FirstOrDefault();
}
return((leadAdapterDb == null) ? false : true);
}
/// <summary>
/// Gets the tour contacts summary.
/// </summary>
/// <param name="tourId">The tour identifier.</param>
/// <param name="contactIds">The contact ids.</param>
/// <returns></returns>
public IEnumerable <TourContactsSummary> GetTourContactsSummary(int tourId, IEnumerable <int> contactIds, int accountId)
{
var db = ObjectContextFactory.Create();
List <TourContactsSummary> contactsSummary = new List <TourContactsSummary>();
if (contactIds != null && contactIds.Any())
{
var contacts = db.Contacts.Where(w => contactIds.Contains(w.ContactID) && w.IsDeleted == false && w.AccountID == accountId).Select(s => new
{
ContactID = s.ContactID,
FirstName = s.FirstName,
LastName = s.LastName,
Company = s.Company,
ContactType = s.ContactType,
ContactName = s.ContactType == ContactType.Person ? (s.FirstName + " " + s.LastName) : s.Company,
PrimaryEmail = db.ContactEmails.Where(cem => cem.ContactID == s.ContactID && cem.IsPrimary == true && cem.IsDeleted == false).Select(se => se.Email).FirstOrDefault() ?? "",
PrimaryPhone = db.ContactPhoneNumbers.Where(cp => cp.ContactID == s.ContactID && cp.IsDeleted == false && cp.IsPrimary == true).FirstOrDefault(),
Lifecycle = db.DropdownValues.Where(d => d.DropdownValueID == s.LifecycleStage).Select(dr => dr.DropdownValue).FirstOrDefault()
});
var contactStatus = db.ContactTours.Where(a => a.TourID == tourId && contactIds.Contains(a.ContactID)).Select(s => new { contactId = s.ContactID, status = s.IsCompleted });
contacts.ForEach(c =>
{
TourContactsSummary contactSummary = new TourContactsSummary();
contactSummary.ContactId = c.ContactID;
contactSummary.ContactType = c.ContactType;
contactSummary.ContactName = c.ContactName; // c.ContactType == ContactType.Person ? c.FirstName + " "+c.LastName : c.Company;
contactSummary.Status = contactStatus.Where(w => w.contactId == c.ContactID).Select(s => s.status).FirstOrDefault();
contactSummary.PrimaryEmail = c.PrimaryEmail;
contactSummary.PrimaryPhone = c.PrimaryPhone != null ? c.PrimaryPhone.PhoneNumber : "";
contactSummary.PhoneCountryCode = c.PrimaryPhone != null ? c.PrimaryPhone.CountryCode : "";
contactSummary.PhoneExtension = c.PrimaryPhone != null ? c.PrimaryPhone.Extension : "";
contactSummary.Lifecycle = c.Lifecycle;
contactsSummary.Add(contactSummary);
});
}
return(contactsSummary);
}
/// <summary>
/// Gets the account ids to send web visit daily summary.
/// </summary>
/// <returns></returns>
public IEnumerable <AccountBasicInfo> GetAccountIdsToSendWebVisitDailySummary()
{
using (var db = ObjectContextFactory.Create())
{
var accounts = db.WebAnalyticsProviders.Join(db.Accounts
, w => w.AccountID
, a => a.AccountID
, (w, a) => new { Account = a, Provider = w })
.Where(c => c.Account.IsDeleted == false &&
c.Account.Status == (byte)AccountStatus.Active &&
c.Provider.StatusID == WebAnalyticsStatus.Active &&
c.Provider.DailyStatusEmailOpted == true)
.Select(c => new AccountBasicInfo
{
AccountID = c.Account.AccountID,
TimeZone = c.Account.TimeZone,
WebAnalyticsID = c.Provider != null ? (short?)c.Provider.WebAnalyticsProviderID : null,
AccountName = c.Account.AccountName
})
.ToList();
return(accounts);
}
}
public int TotalNumberOfAttachments(int?contactId, int?opportunityID, string page)
{
int attachementCount = 0;
Logger.Current.Verbose("Request to fetch total attachment count for related contact.");
try
{
var db = ObjectContextFactory.Create();
if (page == "contacts")
{
attachementCount = db.Attachment.Where(Id => Id.ContactID == contactId).Count();
}
else
{
attachementCount = db.Attachment.Where(Id => Id.OpportunityID == opportunityID).Count();
}
}
catch (Exception ex)
{
Logger.Current.Error("Exception occurred while getting documents", ex);
}
return(attachementCount);
}
/// <summary>
/// Get All Published Account Names
/// </summary>
/// <param name="accountIds"></param>
/// <param name="messageId"></param>
/// <returns></returns>
public IEnumerable <string> GetAllPublishedAccounts(IList <int> accountIds, int messageId, DateTime?from, DateTime?to)
{
using (var db = ObjectContextFactory.Create())
{
var accountNames = new List <string> {
};
var minDate = from == null?(DateTime)System.Data.SqlTypes.SqlDateTime.MinValue: from;
var maxDate = to == null ? (DateTime)System.Data.SqlTypes.SqlDateTime.MaxValue : to;
db.QueryStoredProc("[dbo].[MessageCenterValidator]", (reader) =>
{
accountNames = reader.Read <string>().ToList();
}, new
{
Accounts = accountIds.AsTableValuedParameter("dbo.Contact_List"),
MessageId = messageId,
From = minDate,
To = maxDate
});
return(accountNames);
}
}
/// <summary>
/// Finds all.
/// </summary>
/// <param name="userId">The user identifier.</param>
/// <param name="limit">The limit.</param>
/// <param name="pageNumber">The page number.</param>
/// <param name="accountId">The account identifier.</param>
/// <param name="moduleids">The moduleids.</param>
/// <returns></returns>
public IEnumerable <UserActivityLog> FindAll(int userId, int pageNumber, int accountId, int[] moduleids)
{
var db = ObjectContextFactory.Create();
IEnumerable <int> modules = (moduleids == null || (moduleids != null && !moduleids.Any())) ? new int[] { 1, 2, 3, 4, 5, 6, 7, 9, 10, 16, 24, 31, 33 } : moduleids;
var skip = (pageNumber - 1) * 20;
var take = 20;
DateTime toDate = DateTime.UtcNow.AddDays(-5 * (pageNumber - 1));
DateTime fromDate = DateTime.UtcNow.AddDays(-5 * pageNumber);
IEnumerable <UserActivityLogsDb> logs = new List <UserActivityLogsDb>();
// string logsSql = @"SELECT * FROM UserActivityLogs (NOLOCK) WHERE UserID = @userId AND AccountID = @accountId AND ModuleID IN @ModuleIds
// ORDER BY LogDate DESC OFFSET @skip ROWS
// FETCH NEXT @take ROWS ONLY";
// IEnumerable<UserActivityLogsDb> logs = db.Get<UserActivityLogsDb>(logsSql, new { userId = userId, accountId = accountId, ModuleIds = modules, skip = skip, take = take });
db.QueryStoredProc("[dbo].[GetUserActivities]", (reader) =>
{
logs = reader.Read <UserActivityLogsDb>();
}, new
{
UserID = userId,
AccountID = accountId,
ModuleIds = modules.AsTableValuedParameter("dbo.Contact_List"),
Skip = skip,
Take = take
});
if (logs != null)
{
foreach (UserActivityLogsDb log in logs)
{
yield return(Mapper.Map <UserActivityLogsDb, UserActivityLog>(log));
}
}
}
/// <summary>
/// Gets the current web visits.
/// </summary>
/// <returns></returns>
public IEnumerable <WebVisitReport> GetCurrentWebVisits()
{
using (var db = ObjectContextFactory.Create())
{
var sql = @"select wv.ContactId,c.LeadScore, wv.VisitReference,ISNULL(c.FirstName,'') FirstName, ISNULL(c.LastName,'') LastName,
ISNULL(ep.email,'') Email, ep.PhoneNumber Phone,ep.Zip Zip,c.lifecyclestage LifecycleStageId, c.LeadScore, VisitedOn, PageViews,
Duration, vs.Page1, ISNULL(vs.Page2,'') Page2, ISNULL(vs.Page3,'') Page3, wv.Location,Source,c.OwnerID, c.AccountID AccountId, a.AccountName
from(select contactid, VisitReference, max(visitedon) VisitedOn, sum(duration) Duration, count(visitreference) PageViews,ISNULL(Max(city +', ' + Region),'') Location,
ISNULL(Max(ISPName),'') Source
from contactwebvisits cw (nolock)
where isnull(VisitReference, '000') <> '000'
group by visitreference, contactid
) wv
left join GET_VisiStat_Top3_Pages vs (nolock) on wv.VisitReference = vs.VisitReference
left join contacts c (nolock) on wv.contactid = c.contactid
left join GET_Contact_Email_Phone_Zip ep (nolock) on wv.ContactID = ep.contactid
left join WebVisitEmailAudit wvea(nolock) on wv.VisitReference = wvea.VisitReference
left join Accounts a (nolock) on c.AccountID = a.AccountID
where wvea.EmailStatus = 106
order by visitedon desc" ;
var visits = db.Get <WebVisitReport>(sql);
return(visits);
}
}
/// <summary>
/// Saves the seed list.
/// </summary>
/// <param name="seedEmails">The seed emails.</param>
/// <param name="userId">The user identifier.</param>
public void SaveSeedList(IEnumerable <SeedEmail> seedEmails, int userId)
{
List <SeedEmailDb> seedEmailsDb = new List <SeedEmailDb>();
if (seedEmails.Any())
{
foreach (var seedEmail in seedEmails)
{
SeedEmailDb seedEmailDb = new SeedEmailDb();
seedEmailDb.CreatedBy = userId;
seedEmailDb.CreatedDate = DateTime.Now.ToUniversalTime();
seedEmailDb.Email = seedEmail.Email;
seedEmailsDb.Add(seedEmailDb);
}
}
var db = ObjectContextFactory.Create();
var seedList = (from s in db.SeedEmail select s).ToList();
db.SeedEmail.RemoveRange(seedList);
db.SeedEmail.AddRange(seedEmailsDb);
db.SaveChanges();
}
/// <summary>
/// Gets the tour by identifier.
/// </summary>
/// <param name="tourId">The tour identifier.</param>
/// <returns></returns>
public Tour GetTourByID(int tourId)
{
var db = ObjectContextFactory.Create();
TourDb tourDb = db.Tours.Where(t => t.TourID == tourId).FirstOrDefault();
if (tourDb.SelectAll == false)
{
var contacts = db.ContactTours.Include(n => n.Contact).Where(n => n.TourID == tourId)
.Select(a => a.Contact).ToList();
var userIds = db.UserTours.Where(s => s.TourID == tourId).Select(i => i.UserID).ToList();
tourDb.OwnerIds = userIds;
tourDb.Contacts = contacts.Where(c => c.IsDeleted == false).ToList();
}
else
{
var userIds = db.UserTours.Where(s => s.TourID == tourId).Select(i => i.UserID).ToList();
tourDb.OwnerIds = userIds;
}
if (tourDb != null)
{
return(ConvertToDomain(tourDb));
}
return(null);
}
/// <summary>
/// Finds the by contact.
/// </summary>
/// <param name="contactId">The contact identifier.</param>
/// <returns></returns>
public IEnumerable <Tour> FindByContact(int contactId)
{
var db = ObjectContextFactory.Create();
var sql = @"DECLARE @UserTourMap TABLE (ID INT IDENTITY(1,1),TourID INT, UserName VARCHAR(MAX))
INSERT INTO @UserTourMap
SELECT T.TourID, UserName = STUFF(
(SELECT ', '+ U.FirstName +' ' + U.LastName FROM UserTourMap UTM (NOLOCK)
INNER JOIN Users U (NOLOCK) ON U.UserID = UTM.UserID
WHERE UTM.TourID = T.TourID FOR XML PATH('')),
1,
2,
'') FROM Tours (NOLOCK) T
INNER JOIN ContactTourMap (NOLOCK) CTM ON CTM.TourID = T.TourID
WHERE CTM.ContactID = @contactID
SELECT T.TourID as Id,CTM.IsCompleted,T.TourDetails,T.ReminderDate,T.CommunityID,T.TourDate,UTM.UserName AS UserName FROM Tours (nolock) T
INNER JOIN ContactTourMap (nolock) CTM on CTM.TourID = T.TourID
LEFT JOIN @UserTourMap UTM ON UTM.TourID = T.TourID
WHERE CTM.ContactID=@contactID";
var tours = db.Get <Tour>(sql, new { contactID = contactId }).ToList();
return(tours);
}
public int InsertSubmittedFormData(SmartTouch.CRM.Domain.Forms.SubmittedFormData submittedData, IEnumerable <SmartTouch.CRM.Domain.Forms.SubmittedFormFieldData> submittedFormFieldData)
{
var db = ObjectContextFactory.Create();
SmartTouch.CRM.Repository.Database.SubmittedFormDataDb submitteddata = (Mapper.Map <SmartTouch.CRM.Domain.Forms.SubmittedFormData, SmartTouch.CRM.Repository.Database.SubmittedFormDataDb>(submittedData));
IEnumerable <SmartTouch.CRM.Repository.Database.SubmittedFormFieldDataDb> submittedformdata = (Mapper.Map <IEnumerable <SmartTouch.CRM.Domain.Forms.SubmittedFormFieldData>, IEnumerable <SmartTouch.CRM.Repository.Database.SubmittedFormFieldDataDb> >(submittedFormFieldData));
db.SubmittedFormData.Add(submitteddata);
db.SaveChanges();
foreach (SubmittedFormFieldDataDb fd in submittedformdata)
{
fd.SubmittedFormDataID = submitteddata.SubmittedFormDataID;
}
// submittedformdata.ForEach(p => p.SubmittedFormDataID = submittedData.SubmittedFormDataID);
db.SubmittedFormFieldData.AddRange(submittedformdata);
db.SaveChanges();
return(submitteddata.SubmittedFormDataID);
}
/// <summary>
/// Gets the contacts by activity.
/// </summary>
/// <param name="userId">The user identifier.</param>
/// <param name="moduleName">Name of the module.</param>
/// <param name="activityName">Name of the activity.</param>
/// <param name="contactIDs">The contact i ds.</param>
/// <param name="sort">The sort.</param>
/// <param name="accountId">The account identifier.</param>
/// <returns></returns>
public IList <int> GetContactsByActivity(int userId, AppModules moduleName, UserActivityType activityName, int[] contactIDs, string sort, int accountId)
{
var db = ObjectContextFactory.Create();
IList <int> activities = new List <int>();
/*For Recently Updated */
if (sort == "1")
{
activities = db.UserActivitiesLog
.Where(c => c.UserID == userId && c.AccountID == accountId && c.ModuleID == (byte)moduleName &&
c.UserActivityID == (byte)activityName && c.AccountID == accountId).
Join(db.Contacts, u => u.EntityID, c => c.ContactID, (u, c) => new { u.EntityID, u.LogDate, c.IsDeleted, c.LastUpdatedOn, c.AccountID })
.Where(i => i.IsDeleted == false && i.AccountID == accountId).OrderByDescending(u => u.LastUpdatedOn).Take(1000).Select(i => i.EntityID).ToList();
}
/*For Full Name */
else if (sort == "2")
{
activities = db.UserActivitiesLog
.Where(c => c.UserID == userId && c.AccountID == accountId && c.ModuleID == (byte)moduleName &&
c.UserActivityID == (byte)activityName && c.AccountID == accountId).
Join(db.Contacts, u => u.EntityID, c => c.ContactID, (u, c) => new { u.EntityID, u.LogDate, c.IsDeleted, name = c.FirstName + " " + c.LastName, c.AccountID })
.Where(i => i.IsDeleted == false && i.AccountID == accountId).OrderBy(u => u.name).Take(1000).Select(i => i.EntityID).ToList();
}
/*For Company Name */
else if (sort == "3")
{
activities = db.UserActivitiesLog
.Where(c => c.UserID == userId && c.AccountID == accountId && c.ModuleID == (byte)moduleName &&
c.UserActivityID == (byte)activityName && c.AccountID == accountId).
Join(db.Contacts, u => u.EntityID, c => c.ContactID, (u, c) => new { u.EntityID, u.LogDate, c.IsDeleted, c.Company, c.AccountID })
.Where(i => i.IsDeleted == false && i.AccountID == accountId).OrderBy(u => u.Company).Take(1000).Select(i => i.EntityID).ToList();
}
/*For Recently Viewed */
else
{
activities = db.UserActivitiesLog
.Where(c => c.UserID == userId && c.AccountID == accountId && c.ModuleID == (byte)moduleName &&
c.UserActivityID == (byte)activityName && c.AccountID == accountId).
Join(db.Contacts, u => u.EntityID, c => c.ContactID, (u, c) => new { u.EntityID, u.LogDate, c.IsDeleted, c.AccountID })
.Where(i => i.IsDeleted == false && i.AccountID == accountId).OrderByDescending(u => u.LogDate).Take(1000).Select(i => i.EntityID).ToList();
}
var recentlyViewedContactIds = activities.Select(u => u).GroupBy(g => g).Select(f => f.First()).ToList();
if (sort == "")
{
Logger.Current.Informational("RecentlyViewed Contacts Ids: " + recentlyViewedContactIds.Count());
}
else if (sort == "1")
{
Logger.Current.Informational("RecentlyUpdated Contacts Ids: " + recentlyViewedContactIds.Count());
}
else if (sort == "2")
{
Logger.Current.Informational("FullName Contacts Ids: " + recentlyViewedContactIds.Count());
}
else if (sort == "3")
{
Logger.Current.Informational("Company Contacts Ids: " + recentlyViewedContactIds.Count());
}
if (contactIDs != null)
{
recentlyViewedContactIds = recentlyViewedContactIds.Where(p => contactIDs.Contains(p)).ToList();
}
return(recentlyViewedContactIds);
}
/// <summary>
/// Gets the user activity entity details.
/// </summary>
/// <param name="moduleId">The module identifier.</param>
/// <param name="entityId">The entity identifier.</param>
/// <returns></returns>
public UserActivityEntityDetail GetUserActivityEntityDetails(byte moduleId, int entityId, int accountId)
{
UserActivityEntityDetail entityDetail;
var db = ObjectContextFactory.Create();
if (moduleId == (byte)AppModules.Contacts)
{
entityDetail = db.Contacts.Where(c => c.ContactID == entityId && c.IsDeleted == false && c.AccountID == accountId).Select(s => new UserActivityEntityDetail()
{
EntityId = s.ContactID,
EntityName = (s.ContactType == ContactType.Person) ? (s.FirstName + " " + s.LastName) : s.Company,
ContactType = (byte)s.ContactType
}).FirstOrDefault();
if (entityDetail == null)
{
entityDetail = db.Contacts.Where(c => c.ContactID == entityId && c.IsDeleted == true && c.AccountID == accountId).Select(s => new UserActivityEntityDetail()
{
// EntityId = s.ContactID,
EntityName = (s.ContactType == ContactType.Person) ? (s.FirstName + " " + s.LastName) : s.Company,
// ContactType = (byte)s.ContactType
}).FirstOrDefault();
}
}
else if (moduleId == (byte)AppModules.Opportunity)
{
entityDetail = db.Opportunities.Where(o => o.OpportunityID == entityId && (o.IsDeleted == false || o.IsDeleted == null)).Select(s => new UserActivityEntityDetail()
{
EntityId = s.OpportunityID,
EntityName = s.OpportunityName,
}).FirstOrDefault();
if (entityDetail == null)
{
entityDetail = db.UserActivitiesLog.Where(f => f.EntityID == entityId && f.AccountID == accountId && f.ModuleID == moduleId).Select(s => new UserActivityEntityDetail()
{
EntityId = 0,
EntityName = s.EntityName,
}).FirstOrDefault();
}
}
else if (moduleId == (byte)AppModules.Campaigns)
{
entityDetail = db.Campaigns.Where(ca => ca.CampaignID == entityId && ca.IsDeleted == false).Select(s => new UserActivityEntityDetail()
{
EntityId = s.CampaignID,
EntityName = s.Name,
}).FirstOrDefault();
if (entityDetail == null)
{
entityDetail = db.UserActivitiesLog.Where(f => f.EntityID == entityId && f.AccountID == accountId && f.ModuleID == moduleId).Select(s => new UserActivityEntityDetail()
{
EntityId = 0,
EntityName = s.EntityName,
}).FirstOrDefault();
}
}
else if (moduleId == (byte)AppModules.Forms)
{
entityDetail = db.Forms.Where(f => f.FormID == entityId && f.IsDeleted == false).Select(s => new UserActivityEntityDetail()
{
EntityId = s.FormID,
EntityName = s.Name,
}).FirstOrDefault();
if (entityDetail == null)
{
entityDetail = db.UserActivitiesLog.Where(f => f.EntityID == entityId && f.AccountID == accountId && f.ModuleID == moduleId).Select(s => new UserActivityEntityDetail()
{
EntityId = 0,
EntityName = s.EntityName,
}).FirstOrDefault();
}
}
else if (moduleId == (byte)AppModules.ContactActions)
{
entityDetail = db.Actions.Where(f => f.ActionID == entityId).Select(s => new UserActivityEntityDetail()
{
EntityId = s.ActionID,
EntityName = s.ActionDetails,
}).FirstOrDefault();
}
else if (moduleId == (byte)AppModules.ContactNotes)
{
entityDetail = db.Notes.Where(f => f.NoteID == entityId && f.AccountID == accountId).Select(s => new UserActivityEntityDetail()
{
EntityId = s.NoteID,
EntityName = s.NoteDetails,
}).FirstOrDefault();
}
else if (moduleId == (byte)AppModules.Tags)
{
entityDetail = db.Tags.Where(f => f.TagID == entityId && f.IsDeleted != true).Select(s => new UserActivityEntityDetail()
{
EntityId = s.TagID,
EntityName = s.TagName,
}).FirstOrDefault();
}
else if (moduleId == (byte)AppModules.AdvancedSearch)
{
entityDetail = db.SearchDefinitions.Where(f => f.SearchDefinitionID == entityId).Select(s => new UserActivityEntityDetail()
{
EntityId = s.SearchDefinitionID,
EntityName = s.SearchDefinitionName,
}).FirstOrDefault();
if (entityDetail == null)
{
entityDetail = db.UserActivitiesLog.Where(f => f.EntityID == entityId && f.AccountID == accountId && f.ModuleID == moduleId).Select(s => new UserActivityEntityDetail()
{
EntityId = 0,
EntityName = s.EntityName,
}).FirstOrDefault();
}
}
else if (moduleId == (byte)AppModules.Users)
{
entityDetail = db.Users.Where(f => f.UserID == entityId && f.IsDeleted == false).Select(s => new UserActivityEntityDetail()
{
EntityId = s.UserID,
EntityName = s.FirstName + " " + s.LastName,
}).FirstOrDefault();
if (entityDetail == null)
{
entityDetail = db.Users.Where(f => f.UserID == entityId && f.IsDeleted == true).Select(s => new UserActivityEntityDetail()
{
//EntityId = s.UserID,
EntityName = s.FirstName + " " + s.LastName,
}).FirstOrDefault();
}
}
else if (moduleId == (byte)AppModules.Accounts)
{
entityDetail = db.Accounts.Where(f => f.AccountID == entityId && f.IsDeleted == false).Select(s => new UserActivityEntityDetail()
{
EntityId = s.AccountID,
EntityName = s.FirstName + " " + s.LastName,
}).FirstOrDefault();
if (entityDetail == null)
{
entityDetail = db.Accounts.Where(f => f.AccountID == entityId && f.IsDeleted == true).Select(s => new UserActivityEntityDetail()
{
EntityName = s.FirstName + " " + s.LastName,
}).FirstOrDefault();
}
}
else if (moduleId == (byte)AppModules.Reports)
{
entityDetail = db.Reports.Where(r => r.ReportID == entityId).Select(s => new UserActivityEntityDetail()
{
EntityId = s.ReportID,
EntityName = s.ReportName,
ReportType = s.ReportType
}).FirstOrDefault();
}
else if (moduleId == (byte)AppModules.Automation)
{
entityDetail = db.Workflows.Where(w => w.WorkflowID == entityId).Select(w => new UserActivityEntityDetail()
{
EntityId = w.IsDeleted ? 0 : w.WorkflowID,
EntityName = w.WorkflowName,
}).FirstOrDefault();
}
else
{
entityDetail = null;
}
return(entityDetail);
}
/// <summary>
/// Gets the settings.
/// </summary>
/// <param name="AccountID">The account identifier.</param>
/// <param name="UserID">The user identifier.</param>
/// <returns></returns>
public UserSettings GetSettings(int AccountID, int UserID)
{
using (var db = ObjectContextFactory.Create())
{
var sql = @"select *
from usersettings us (nolock)
inner join currencies c (nolock) on us.currencyid = c.currencyid
inner join dateformats df (nolock) on df.dateformatid = us.[dateformat]
where userid = @id";
var usersettingsdb = db.Get <UserSettingsDb, CurrenciesDb, DateFormatDb>(sql, (u, c, d) =>
{
u.Currency = c;
u.DateFormat = d.DateFormatId;
u.DateFormatName = d.FormatName;
return(u);
}, new { Id = UserID }, splitOn: "CurrencyID,DateFormatID").FirstOrDefault();
if (usersettingsdb == null)
{
var asql = @"select *
from accounts a (nolock)
inner join currencies c (nolock) on a.currencyid = c.currencyid
inner join dateformats df (nolock) on df.dateformatid = a.dateformatid
where a.accountid = @aid";
var accountsettings = db.Get <AccountsDb, CurrenciesDb, DateFormatDb>(asql, (a, c, d) =>
{
a.CurrencyID = c.CurrencyID;
a.CurrencyFormat = c.Format;
a.DateFormatID = d.DateFormatId;
a.DateFormat = d.FormatName;
return(a);
}, new { AId = AccountID }, splitOn: "CurrencyID,DateFormatID").FirstOrDefault();
UserSettings settings = new UserSettings
{
CountryID = accountsettings.CountryID,
CurrencyFormat = accountsettings.CurrencyFormat,
CurrencyID = accountsettings.CurrencyID,
DateFormat = accountsettings.DateFormatID,
DateFormatType = accountsettings.DateFormat,
ItemsPerPage = 25,
TimeZone = accountsettings.TimeZone
};
return(settings);
}
return(new UserSettings
{
CountryID = usersettingsdb.CountryID,
CurrencyFormat = usersettingsdb.Currency.Format,
CurrencyID = usersettingsdb.CurrencyID,
DateFormat = usersettingsdb.DateFormat,
DateFormatType = usersettingsdb.DateFormatName,
ItemsPerPage = usersettingsdb.ItemsPerPage,
TimeZone = usersettingsdb.TimeZone
});
}
}
/// <summary>
/// Gets the user settings database.
/// </summary>
/// <param name="id">The identifier.</param>
/// <returns></returns>
UserSettingsDb getUserSettingsDb(int id)
{
var db = ObjectContextFactory.Create();
return(db.UserSettings.SingleOrDefault(c => c.UserSettingID == id));
}
/// <summary>
/// Finds the image domains.
/// </summary>
/// <param name="records">The records.</param>
/// <param name="limit">The limit.</param>
/// <returns></returns>
IEnumerable <ImageDomainsDb> findImageDomains(int records, int limit)
{
IEnumerable <ImageDomainsDb> imageDomains = ObjectContextFactory.Create().ImageDomains.OrderByDescending(i => i.LastModifiedOn).Skip(records).Take(limit);
return(imageDomains);
}
/// <summary>
/// Deletes the tour.
/// </summary>
/// <param name="tourId">The tour identifier.</param>
/// <param name="userId">The user identifier.</param>
/// <returns></returns>
public Dictionary <int, Guid?> DeleteTour(int tourId, int userId, int contactid)
{
var db = ObjectContextFactory.Create();
if (contactid == 0)
{
var tourdb = db.Tours.Where(n => n.TourID == tourId).FirstOrDefault();
Dictionary <int, Guid?> guids = new Dictionary <int, Guid?>();
guids.Add(1, tourdb.EmailRequestGuid);
guids.Add(2, tourdb.TextRequestGuid);
var contactTours = db.ContactTours.Where(n => n.TourID == tourId).ToList();
if (contactTours != null)
{
db.ContactTours.RemoveRange(contactTours);
}
var userTours = db.UserTours.Where(u => u.TourID == tourId).ToList();
if (userTours != null)
{
db.UserTours.RemoveRange(userTours);
}
db.Tours.Remove(tourdb);
db.SaveChanges();
// var tourauditdb1 = db.Tours_Audit.Where(p => p.TourID == tourId).ToList();
var tourauditdb = db.Tours_Audit.Where(p => p.TourID == tourId && p.AuditAction == "D").FirstOrDefault();
if (tourauditdb != null)
{
tourauditdb.LastUpdatedBy = userId;
db.SaveChanges();
}
var outlookCRMSyncEntity = db.CRMOutlookSync.Where(c => c.EntityID == tourId && c.EntityType == (short)AppModules.ContactTours).FirstOrDefault();
if (outlookCRMSyncEntity != null)
{
outlookCRMSyncEntity.SyncStatus = (short)OutlookSyncStatus.Deleted;
db.Entry <CRMOutlookSyncDb>(outlookCRMSyncEntity).State = System.Data.Entity.EntityState.Modified;
}
db.SaveChanges();
return(guids);
}
else
{
var tourDb = db.Tours.Where(n => n.TourID == tourId).FirstOrDefault();
var userToursdb = db.UserTours.Where(n => n.TourID == tourId).ToList();
if (userToursdb != null)
{
db.UserTours.RemoveRange(userToursdb);
}
var tourcontactdb = db.ContactTours.Where(p => p.TourID == tourId && p.ContactID == contactid).FirstOrDefault();
if (tourcontactdb != null)
{
db.ContactTours.Remove(tourcontactdb);
}
if (tourDb != null)
{
db.Tours.Remove(tourDb);
}
var outlookCRMSyncEntity = db.CRMOutlookSync.Where(c => c.EntityID == tourId && c.EntityType == (short)AppModules.ContactTours).FirstOrDefault();
if (outlookCRMSyncEntity != null)
{
outlookCRMSyncEntity.SyncStatus = (short)OutlookSyncStatus.Deleted;
db.Entry <CRMOutlookSyncDb>(outlookCRMSyncEntity).State = System.Data.Entity.EntityState.Modified;
}
db.SaveChanges();
return(null);
}
}
/// <summary>
/// Gets the opportunity database.
/// </summary>
/// <param name="id">The identifier.</param>
/// <returns></returns>
OpportunitiesDb getOpportunityDb(int id)
{
var db = ObjectContextFactory.Create();
return(db.Opportunities.SingleOrDefault(c => c.OpportunityID == id && c.IsDeleted != true));
}
/// <summary>
/// Gets the lead adapter job log details database.
/// </summary>
/// <param name="id">The identifier.</param>
/// <returns></returns>
LeadAdapterJobLogDetailsDb getLeadAdapterJobLogDetailsDb(int id)
{
var db = ObjectContextFactory.Create();
return(db.LeadAdapterJobLogDetails.SingleOrDefault(c => c.LeadAdapterJobLogDetailID == id));
}
/// <summary>
/// Determines whether [is lead adapter already configured] [the specified account identifier].
/// </summary>
/// <param name="AccountID">The account identifier.</param>
/// <param name="leadAdapterType">Type of the lead adapter.</param>
/// <returns></returns>
public bool isLeadAdapterAlreadyConfigured(int AccountID, LeadAdapterTypes leadAdapterType)
{
using (var db = ObjectContextFactory.Create()) {
return(db.LeadAdapters.Where(i => i.AccountID == AccountID && i.LeadAdapterTypeID == (byte)leadAdapterType && i.IsDelete == false).Count() > 0);
}
}
public bool IsValidAPISubmission(int formId, int accountId)
{
var db = ObjectContextFactory.Create();
return(db.Forms.Where(w => w.FormID == formId && w.IsAPIForm && w.AccountID == accountId).Any());
}
public bool HasFacebookFields(int accountId)
{
var db = ObjectContextFactory.Create();
return(db.Fields.Where(w => w.AccountID == accountId && w.IsLeadAdapterField && w.LeadAdapterType == (byte)LeadAdapterTypes.Facebook).Any());
}