public static List <string> GetPotentialUserSources() { var mifnexsoEntities = new MIFNEXSOEntities(); var result = mifnexsoEntities.ExecuteStoreQuery <string>("select source from potentialUsers where source is not null group by source").ToList(); return(result); }
public static List <string> GetSolutionChallenges() { var mifnexsoEntities = new MIFNEXSOEntities(); var result = mifnexsoEntities.ExecuteStoreQuery <string>("select challengereference from solution where challengereference is not null group by challengereference ").ToList(); return(result); }
public static List <SolutionOrganizationView> GetSolutions(string search, int state, int minScore) { var mifnexsoEntities = new MIFNEXSOEntities(); var result = mifnexsoEntities.ExecuteStoreQuery <SolutionOrganizationView>("SELECT dbo.Solution.SolutionId AS SSolutionId, dbo.Solution.SolutionTypeId AS SSolutionTypeId, " + " dbo.Solution.Title AS STitle, dbo.Solution.TagLine AS STagLine, " + " dbo.Solution.Description AS SDescription, dbo.Solution.Biography AS SBiography, dbo.Solution.Challenge AS SChallenge, dbo.Solution.Approach AS SApproach, " + " dbo.Solution.Results AS SResults, dbo.Solution.ImplementationDetails AS SImplementationDetails, dbo.Solution.AdditionalCost AS SAdditionalCost, " + " dbo.Solution.AvailableResources AS SAvailableResources, dbo.Solution.TimeFrame AS STimeFrame, dbo.Solution.Duration AS SDuration, " + " dbo.Solution.DurationDetails AS SDurationDetails, dbo.Solution.SolutionStatusId AS SSolutionStatusId, dbo.Solution.SolutionType AS SSolutionType, " + " dbo.Solution.Topic AS STopic, dbo.Solution.Language AS SLanguage, dbo.Solution.CreatedUserId AS SCreatedUserId, dbo.Solution.Deleted AS SDeleted, " + " dbo.Solution.Country AS SCountry, dbo.Solution.Region AS SRegion, dbo.Solution.City AS SCity, dbo.Solution.Address AS SAddress, " + " dbo.Solution.ZipCode AS SZipCode, dbo.Solution.Logo AS SLogo, dbo.Solution.Cost1 AS SCost1, dbo.Solution.Cost2 AS SCost2, dbo.Solution.Cost3 AS SCost3, " + " dbo.Solution.DeliveryFormat AS SDeliveryFormat, dbo.Solution.Cost AS SCost, dbo.Solution.CostType AS SCostType, dbo.Solution.CostDetails AS SCostDetails, " + " dbo.Solution.SolutionState AS SSolutionState, dbo.Solution.Beneficiaries AS SBeneficiaries, dbo.Solution.DateCreated AS SDateCreated, " + " dbo.Solution.DateUpdated AS SDateUpdated, dbo.Solution.ChallengeReference AS SChallengeReference, dbo.Organization.OrganizationID AS OOrganizationID, " + " dbo.Organization.Code AS OCode, dbo.Organization.Name AS OName, dbo.Organization.Address AS OAddress, dbo.Organization.Phone AS OPhone, " + " dbo.Organization.Email AS OEmail, dbo.Organization.ContactEmail AS OContactEmail, dbo.Organization.Website AS OWebsite, dbo.Organization.Twitter AS OTwitter, " + " dbo.Organization.Skype AS OSkype, dbo.Organization.Facebook AS OFacebook, dbo.Organization.GooglePlus AS OGooglePlus, " + " dbo.Organization.LinkedIn AS OLinkedIn, dbo.Organization.Description AS ODescription, dbo.Organization.Logo AS OLogo, dbo.Organization.Country AS OCountry, " + " dbo.Organization.Region AS ORegion, dbo.Organization.ZipCode AS OZipCode, dbo.Organization.City AS OCity, dbo.Organization.Created AS OCreated, " + " dbo.Organization.Updated AS OUpdated, dbo.Organization.Latitude AS OLatitude, dbo.Organization.Longitude AS OLongitude, " + " dbo.Organization.GoogleLocation AS OGoogleLocation " + " FROM dbo.Solution INNER JOIN " + " dbo.Organization ON dbo.Solution.OrganizationId = dbo.Organization.OrganizationID where dbo.GetScore(Solution.SolutionId,'JUDGE')>" + minScore.ToString() + " and Solution.SolutionState=" + state.ToString() + " order by dbo.GetScore(Solution.SolutionId,'JUDGE') desc" ).ToList(); return(result); }
public static List <CampaignLog> ProcessXmlFilter(Guid CampaignId, string trackKey, int attemp, string xmlFilter, int portalId) { List <CampaignLog> listResult = new List <CampaignLog>(); MailContainer mailContainer; try { mailContainer = new MailContainer(); XmlSerializer serializer = new XmlSerializer(mailContainer.GetType()); MemoryStream memoryStream = new MemoryStream(Encoding.ASCII.GetBytes(xmlFilter)); mailContainer = (MailContainer)serializer.Deserialize(memoryStream); StringBuilder queryPotentialUser = new StringBuilder(); StringBuilder queryUser = new StringBuilder(); StringBuilder queryOrganization = new StringBuilder(); StringBuilder querySolution = new StringBuilder(); MIFNEXSOEntities ent = new MIFNEXSOEntities(); foreach (MailFilter mailFilter in mailContainer.MailFilter) { switch (mailFilter.Table) { case "PotentialUsers": { switch (mailFilter.Field) { case "Country": { queryPotentialUser.Append(GetSqlClause(mailFilter)); break; } case "Language": { queryPotentialUser.Append(GetSqlClause(mailFilter)); break; } case "Source": { queryPotentialUser.Append(GetSqlClause(mailFilter)); break; } } break; } case "UserProperties": { switch (mailFilter.Field) { case "Country": { queryUser.Append(GetSqlClause(mailFilter)); break; } case "Language": { queryUser.Append(GetSqlClause(mailFilter)); break; } //case "CustomerType": // { // break; // } case "[AllowNexsoNotifications]": { if (mailFilter.FilterValue.Count > 0) { if (mailFilter.FilterValue[0] == "1") { queryUser.Append(GetSqlClause(mailFilter)); } } break; } } break; } case "UserPropertiesLists": { switch (mailFilter.Field) { case "[Key]": { queryUser.Append(GetSqlClause(mailFilter)); break; } } break; } case "Organization": { switch (mailFilter.Field) { case "Country": { queryOrganization.Append(GetSqlClause(mailFilter)); break; } } break; } case "Solution": { switch (mailFilter.Field) { case "Language": { querySolution.Append(GetSqlClause(mailFilter)); break; } case "ChallengeReference": { querySolution.Append(GetSqlClause(mailFilter)); break; } case "SolutionState": { querySolution.Append(GetSqlClause(mailFilter)); break; } case "FUNC:GetScore(Solution.SolutionId,'JUDGE')": { querySolution.Append(GetSqlClause(mailFilter)); break; } case "FUNC:WordCount(Solution.SolutionId)": { querySolution.Append(GetSqlClause(mailFilter)); break; } } break; } } } foreach (MailFilter mailFilter in mailContainer.MailFilter) { switch (mailFilter.Table) { case "&&1": { switch (mailFilter.Field) { case "UsePotentialUser": { if (mailFilter.FilterValue[0] == "0") { queryPotentialUser = null; } break; } case "UseUser": { if (mailFilter.FilterValue[0] == "0") { queryUser = null; } break; } case "UseOrganization": { if (mailFilter.FilterValue[0] == "0") { queryOrganization = null; } break; } case "UseSolution": { if (mailFilter.FilterValue[0] == "0") { querySolution = null; } break; } } break; } } } List <UserProperty> queryPotentialUserEntity = null; IEnumerable <PotentialUser> queryPotentialUserEntityInt = null; IEnumerable <UserProperty> querySolutionEntity = null; IEnumerable <UserProperty> queryUserEntity = null; IEnumerable <UserProperty> queryOrganizationEntity = null; if (queryPotentialUser != null) { queryPotentialUser.Insert(0, " SELECT * FROM POTENTIALUSERS" + " where 1=1 "); queryPotentialUserEntityInt = ent.ExecuteStoreQuery <PotentialUser>(queryPotentialUser.ToString()); queryPotentialUserEntity = new List <UserProperty>(); foreach (var item in queryPotentialUserEntityInt) { queryPotentialUserEntity.Add(new UserProperty() { email = item.Email, FirstName = item.FirstName, LastName = item.LastName } ); } } if (queryUser != null) { queryUser.Insert(0, " SELECT UserProperties.* " + " FROM UserProperties left JOIN UserPropertiesLists ON UserProperties.UserId = UserPropertiesLists.UserPropertyId" + " where 1=1 "); queryUserEntity = ent.ExecuteStoreQuery <UserProperty>(queryUser.ToString()); } if (queryOrganization != null) { queryOrganization.Insert(0, " SELECT UserProperties.* " + " FROM Organization INNER JOIN UserOrganization ON Organization.OrganizationID = UserOrganization.OrganizationID " + " INNER JOIN UserProperties ON UserOrganization.UserID = UserProperties.UserId" + " where 1=1 "); queryOrganizationEntity = ent.ExecuteStoreQuery <UserProperty>(queryOrganization.ToString()); } if (querySolution != null) { querySolution.Insert(0, " SELECT UserProperties.* FROM Solution INNER JOIN UserProperties ON Solution.CreatedUserId = UserProperties.UserId " + " where 1=1 "); querySolutionEntity = ent.ExecuteStoreQuery <UserProperty>(querySolution.ToString()); } IEnumerable <UserProperty> resultfinal = queryUserEntity; if (queryOrganizationEntity != null) { if (resultfinal == null) { resultfinal = queryOrganizationEntity; } else { resultfinal = resultfinal.Concat(queryOrganizationEntity); } } if (querySolutionEntity != null) { if (resultfinal == null) { resultfinal = querySolutionEntity; } else { resultfinal = resultfinal.Concat(querySolutionEntity); } } if (queryPotentialUserEntity != null) { if (resultfinal == null) { resultfinal = queryPotentialUserEntity; } else { resultfinal = resultfinal.Concat(queryPotentialUserEntity); } } CampaignComponent campaign = new CampaignComponent(CampaignId); if (mailContainer.UserProperty.Count > 0) { if (resultfinal == null) { resultfinal = mailContainer.UserProperty; } else { resultfinal = resultfinal.Concat(mailContainer.UserProperty).ToList(); } } if (resultfinal != null) { var comparer = new UserComparer(); foreach (var item in resultfinal.Distinct(comparer).ToList()) { try { var user = UserController.GetUserById(portalId, item.UserId); listResult.Add(getCampaignLog(campaign, item, user)); } catch (Exception) { } } } } catch (Exception) { } return(listResult); }