public DataTable ExportGetEmails(EmailFilter filter, int userId, bool isAdmin)
{
DataTable dataTable = new DataTable();
var sql = @"
SELECT Email.EmailRef,
Email.[Subject] ,
Email.[From] ,
Email.[To] ,
Email.[CC] ,
Email.DateTimeReceived ,
EmailStatus.[Status]
FROM [dbo].[Email]
INNER JOIN EmailStatus on EmailStatus.ID = Email.EmailStatus
WHERE [dbo].[Email].ParentEmailId IS NULL ";
sql += getFilterString(filter);
if (!isAdmin)
{
sql += @" AND EXISTS (SELECT 1 FROM EmailUser WHERE EmailUser.EmailId = Email.EmailId AND [EmailUser].[UserId] = " + userId + ")";
}
sql += " order by Email.EmailId desc";
using (SqlConnection sqlConnection = new SqlConnection(DBHelper.strConnString))
{
sqlConnection.Open();
SqlDataAdapter sqlAdapter = new SqlDataAdapter(sql, sqlConnection);
sqlAdapter.Fill(dataTable);
}
return(dataTable);
}
public override TokenStreamComponents CreateComponents(string fieldName, TextReader reader)
{
UAX29URLEmailTokenizer tokenizer = new UAX29URLEmailTokenizer(TEST_VERSION_CURRENT, reader);
TokenFilter filter = new EmailFilter(tokenizer);
return(new TokenStreamComponents(tokenizer, filter));
}
protected internal override TokenStreamComponents createComponents(string fieldName, Reader reader)
{
UAX29URLEmailTokenizer tokenizer = new UAX29URLEmailTokenizer(TEST_VERSION_CURRENT, reader);
TokenFilter filter = new EmailFilter(tokenizer);
return(new TokenStreamComponents(tokenizer, filter));
}
private static string getFilterString(EmailFilter filter)
{
string sql = "";
if (!string.IsNullOrEmpty(filter.To))
{
sql += " AND [To] LIKE '%" + filter.To + "%'";
}
if (!string.IsNullOrEmpty(filter.From))
{
sql += " AND [From] LIKE '%" + filter.From + "%'";
}
if (!string.IsNullOrEmpty(filter.Subject))
{
sql += " AND Subject LIKE '%" + filter.Subject + "%'";
}
if (!string.IsNullOrEmpty(filter.EmailRef))
{
sql += " AND EmailRef LIKE '%" + filter.EmailRef + "%'";
}
if (filter.Status != -1)
{
sql += " AND EmailStatus = " + filter.Status;
}
if (!string.IsNullOrEmpty(filter.dateTimeReceived))
{
sql += " AND Cast(DateTimeReceived AS DATE)" + filter.dateTimeReceivedOperater + " '" + filter.dateTimeReceived + " '";
}
return(sql);
}
public IEnumerable <Email> GetEmailsFiltered([FromRoute] EmailFilter filter)
{
var f = filter;
var q = GetEmails();
if (!string.IsNullOrWhiteSpace(f.ToAddress))
{
q = q.Where(x => x.To == f.ToAddress);
}
if (!string.IsNullOrWhiteSpace(f.FromAddress))
{
q = q.Where(x => x.From == f.FromAddress);
}
if (!string.IsNullOrWhiteSpace(f.Subject))
{
q = q.Where(x => x.Subject == f.Subject);
}
q = q.Take(f.PageSize);
return(q);
}
private IFilterStrategy DetermineFilter(FilterOptions options)
{
IFilterStrategy filterStrategy = null;
if (options.Filter == FilterBy.Name && !string.IsNullOrEmpty(options.Name))
{
filterStrategy = new NameFilter(options);
}
else if (options.Filter == FilterBy.Email && !string.IsNullOrEmpty(options.Email))
{
filterStrategy = new EmailFilter(options);
}
else if (options.Filter == FilterBy.Phone && !string.IsNullOrEmpty(options.Phone))
{
filterStrategy = new PhoneFilter(options);
}
else if (options.Filter == FilterBy.From && options.From.HasValue)
{
filterStrategy = new FromFilter(options);
}
else if (options.Filter == FilterBy.To && options.To.HasValue)
{
filterStrategy = new ToFilter(options);
}
else if (options.Filter == FilterBy.Status)
{
filterStrategy = new StatusFIlter(options);
}
else if (options.Filter == FilterBy.Date && options.Date.HasValue)
{
filterStrategy = new DateFilter(options);
}
return(filterStrategy);
}
public async Task <IActionResult> FilterEmailAsync([FromBody] EmailFilter emailFilter)
{
emailFilter.AccountId = User.ReadClaimAsGuidValue("urn:codefliptodo:accountid");
await _mediator.Send(emailFilter);
return(Ok());
}
public static int GetEmailsCount(EmailFilter filter, int userId, bool isAdmin)
{
int count;
var sql = @"
SELECT COUNT(*)
FROM Email ";
if (!isAdmin)
{
sql += @"
left outer join EmailUser on EmailUser.EmailId = Email.EmailId
left outer join dbo.[User] on [User].[UserID] = EmailUser.[UserId]
left outer join EmailStatus on EmailStatus.ID = Email.EmailStatus
WHERE ParentEmailId IS NULL AND [User].[UserID] = " + userId;
}
else
{
sql += @"
left outer join EmailStatus on EmailStatus.ID = Email.EmailStatus
WHERE ParentEmailId IS NULL";
}
sql += getFilterString(filter);
using (SqlConnection sqlConnection = new SqlConnection(DBHelper.strConnString))
{
SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection);
try
{
sqlConnection.Open();
count = (int)sqlCommand.ExecuteScalar();
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlConnection.Close();
}
}
return(count);
}
public static DataTable GetEmails(EmailFilter filter, int maximumRows, int startRowIndex, int userId, bool isAdmin)
{
DataTable dataTable = new DataTable();
var sql = @"
SELECT *
from (
SELECT ROW_NUMBER() OVER ( ORDER BY Email.EmailId DESC ) ROWNUMBER,
Email.EmailId,
Email.[Subject] ,
Email.[From] ,
Email.[To] ,
Email.[CC] ,
Email.DateTimeReceived ,
IsAssign,
EmailRef,
EmailStatus,
EmailStatus.Status
FROM [dbo].[Email]
INNER JOIN EmailStatus on EmailStatus.ID = Email.EmailStatus
WHERE [dbo].[Email].ParentEmailId IS NULL ";
sql += getFilterString(filter);
if (!isAdmin)
{
sql += @" AND EXISTS (SELECT 1 FROM EmailUser WHERE EmailUser.EmailId = Email.EmailId AND [EmailUser].[UserId] = " + userId + ")";
}
sql += ") As query where query.ROWNUMBER > " + startRowIndex + " AND query.ROWNUMBER <= " + (startRowIndex + maximumRows);
using (SqlConnection sqlConnection = new SqlConnection(DBHelper.strConnString))
{
sqlConnection.Open();
SqlDataAdapter sqlAdapter = new SqlDataAdapter(sql, sqlConnection);
sqlAdapter.Fill(dataTable);
}
return(dataTable);
}
private EmailFilter GetFilter()
{
EmailFilter entity = new EmailFilter();
entity.From = txtEmailFrom.Text.ToString();
entity.To = txtEmailTo.Text.ToString();
entity.Subject = txtEmailSub.Text.ToString();
entity.dateTimeReceived = txtEmailDate.Text.ToString();
entity.EmailRef = txtEmailRef.Text.ToString();
if (ddlAssign.SelectedValue == "-1")
{
entity.Status = -1;
}
else
{
entity.Status = int.Parse(ddlAssign.SelectedValue);
}
entity.dateTimeReceivedOperater = dateDropDownList.Text.ToString();
return(entity);
}
public override TokenStreamComponents CreateComponents(string fieldName, TextReader reader)
{
UAX29URLEmailTokenizer tokenizer = new UAX29URLEmailTokenizer(TEST_VERSION_CURRENT, reader);
TokenFilter filter = new EmailFilter(tokenizer);
return new TokenStreamComponents(tokenizer, filter);
}
public static bool IsMatchedByFilter(Email mail, EmailFilter filter)
{
bool MatchAny(string text)
=> text != null && filter.OneOf.Any(item => text.IndexOf(item, StringComparison.OrdinalIgnoreCase) >= 0);
bool EqualsAny(string text)
=> text != null && filter.OneOf.Any(item => text.Equals(item, StringComparison.OrdinalIgnoreCase));
bool NotMatchAll(string text)
=> text != null && filter.AllOf.All(item => text.IndexOf(item, StringComparison.OrdinalIgnoreCase) < 0);
bool NotEqualsAll(string text)
=> text != null && filter.AllOf.All(item => !text.Equals(item, StringComparison.OrdinalIgnoreCase));
switch (filter.Type.ToLowerInvariant())
{
case "sender contains":
return(MatchAny(mail.From.Name) || MatchAny(mail.From.Email));
case "sender equals":
return(EqualsAny(mail.From.Name) || EqualsAny(mail.From.Email));
case "!sender contains":
return(NotMatchAll(mail.From.Name) && NotMatchAll(mail.From.Email));
case "!sender equals":
return(NotEqualsAll(mail.From.Name) && NotEqualsAll(mail.From.Email));
case "subject contains":
return(MatchAny(mail.Subject));
case "!subject contains":
return(NotMatchAll(mail.Subject));
case "body contains":
return(MatchAny(mail.Text ?? mail.Html));
case "!body contains":
return(NotMatchAll(mail.Text ?? mail.Html));
case "subject/body contains":
return(MatchAny(mail.Subject) || MatchAny(mail.Text ?? mail.Html));
case "!subject/body contains":
return(NotMatchAll(mail.Subject) && NotMatchAll(mail.Text ?? mail.Html));
case "recipient contains":
return(mail.To.Any(to => MatchAny(to.Name) || MatchAny(to.Email)) ||
mail.Cc.Any(to => MatchAny(to.Name) || MatchAny(to.Email)));
case "!recipient contains":
return(mail.To.Any(to => NotMatchAll(to.Name) && NotMatchAll(to.Email)) ||
mail.Cc.Any(to => NotMatchAll(to.Name) && NotMatchAll(to.Email)));
case "recipient equals":
return(mail.To.Any(to => EqualsAny(to.Name) || EqualsAny(to.Email)) ||
mail.Cc.Any(to => EqualsAny(to.Name) || EqualsAny(to.Email)));
case "!recipient equals":
return(mail.To.Any(to => NotEqualsAll(to.Name) && NotEqualsAll(to.Email)) ||
mail.Cc.Any(to => NotEqualsAll(to.Name) && NotEqualsAll(to.Email)));
default:
throw new ArgumentOutOfRangeException($"Unsupported {filter.Type}");
}
}
protected internal override TokenStreamComponents createComponents(string fieldName, Reader reader)
{
UAX29URLEmailTokenizer tokenizer = new UAX29URLEmailTokenizer(TEST_VERSION_CURRENT, reader);
TokenFilter filter = new EmailFilter(tokenizer);
return new TokenStreamComponents(tokenizer, filter);
}
