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);
}