static IEnumerable <IDataRecord> GetLogMailDataRecords(GetMailStatisticsRequest request)
{
var parameters = new
{
fromDate = request.FilterDateFrom,
toDate = request.FilterDateTo,
status = (int)request.FilterStatus,
emailID = request.CurrentNodeId > 0 ? (object)request.CurrentNodeId : DBNull.Value,
text = !String.IsNullOrEmpty(request.SearchContent) && request.SearchContent != "null" ? request.SearchContent : "%%",
receiver = !String.IsNullOrEmpty(request.SearchReceiver) && request.SearchReceiver != "null" ? request.SearchReceiver : "%%",
orderBy = DBNull.Value
};
return(Sql.CreateSqlDataEnumerator(Constants.SQL_QUERY_GET_STATISTICS, System.Data.CommandType.Text, parameters));
}
static string[] _includeColumnsForExport = new[] { "id", "emailID", "dateSent", "to", "from", "replyTo", "cc", "bcc", "subject", "exception", "emailName" }; // "body", ==> Excluded body as it's massive and really not necessery in an export (that's what the online view feature is for)
public static void Download(GetMailStatisticsRequest request)
{
if (request == null)
{
return; // Lege request? Dan krijg je niks terug!
}
bool retry = false;
retry:
try
{
var r = HttpContext.Current.Response;
r.Clear();
r.Buffer = true;
r.ContentType = "application/vnd.ms-excel";
r.AddHeader("content-disposition", "attachment;filename=" + "Export.xls");
r.Charset = "";
bool[] columnIsEncrypted = null;
bool[] columnIsIncluded = null;
var firstRow = true;
var sbHeader = new StringBuilder("<tr>");
// Bouw de response op
var sbData = new StringBuilder();
int isEncryptedColumnNumber = -1;
foreach (IDataRecord dr in GetLogMailDataRecords(request))
{
// Is this the first datarecord we are iterating over?
if (firstRow)
{
columnIsEncrypted = new bool[dr.FieldCount];
columnIsIncluded = new bool[dr.FieldCount];
// Determine which columns are available in the dataset by looping over all the columns and inspecting their names
for (int i = 0; i < dr.FieldCount; i++)
{
// Get the name of the currenet column
string colName = dr.GetName(i);
// Determine if the current column is an encrypted column
columnIsEncrypted[i] = _encryptedColumns.Contains(colName);
columnIsIncluded[i] = _includeColumnsForExport.Contains(colName);
// Determine if the isEncrypted column is present and what column number it is in
if (colName == "isEncrypted")
{
isEncryptedColumnNumber = i;
}
if (columnIsIncluded[i])
{
// Add the column to our excel header line (we don't want to include the isEncrypted column in our excel export)
sbHeader.AppendLine("<th>" + colName + "</th>");
}
}
firstRow = false;
}
// Determine if the column is encrypted
bool recordIsEncrypted = isEncryptedColumnNumber >= 0 ? dr.GetBoolean(isEncryptedColumnNumber) : false;
sbData.AppendLine("<tr>");
for (int i = 0; i < dr.FieldCount; i++)
{
// Only include columns that are specified in the include list
if (columnIsIncluded[i])
{
string text = dr.GetValue(i).ToString();
// If the current record is flagged as encrypted AND the current column is an encrypted column ...
if (recordIsEncrypted && columnIsEncrypted[i])
{
// ... then decrypt the column text
text = Security.Decrypt(text);
}
sbData.AppendLine("<td> " + HttpUtility.HtmlEncode(text) + " </td>");
}
}
sbData.AppendLine("</tr>");
}
sbHeader.AppendLine("</tr>");
var sbResult = new StringBuilder();
sbResult.AppendLine("<table>");
sbResult.Append(sbHeader);
sbResult.Append(sbData);
sbResult.AppendLine("</table>");
// Aan de hand van de BOM encoding character kan de browser weten wat voor een karakters er in het document staan.
// Dit zorgt er eigenlijk voor dat gekke tekens zoals de euro er goed in komen te staan.
r.Write("\uFEFF");
r.Write(sbResult.ToString());
r.End();
}
catch (DbException ex)
{
if (retry && Helper.HandleSqlException(ex))
{
retry = false;
goto retry;
}
else
{
throw;
}
}
}
public static List <LogEmail> Search(GetMailStatisticsRequest request)
{
if (request == null)
{
return(null); // Lege request? Dan krijg je niks terug!
}
bool retry = true;
retry:
try
{
var parameters = new
{
fromDate = request.FilterDateFrom,
toDate = request.FilterDateTo,
status = (int)request.FilterStatus,
emailID = request.CurrentNodeId,
orderBy = request.OrderBy,
receiver = "%%",
text = "%%",
};
var emails = Sql.ExecuteSql <LogEmail>(Constants.SQL_QUERY_GET_STATISTICS, System.Data.CommandType.Text, parameters);
foreach (var email in emails)
{
// Decrypt some values if the e-mail is encrypted
if (email.isEncrypted)
{
email.to = Security.Decrypt(email.to);
email.cc = Security.Decrypt(email.cc);
email.bcc = Security.Decrypt(email.bcc);
email.replyTo = Security.Decrypt(email.replyTo);
email.from = Security.Decrypt(email.from);
email.body = Security.Decrypt(email.body);
email.subject = Security.Decrypt(email.subject);
}
}
// Determine if we need to search the body of each
if (!String.IsNullOrEmpty(request.SearchContent))
{
// Find all e-mails that contain the search string in their subject or body
emails = emails.Where(x => (!String.IsNullOrEmpty(x.subject) && x.subject.Contains(request.SearchContent)) || // Search Subject
(!String.IsNullOrEmpty(x.body) && x.body.Contains(request.SearchContent))).ToList(); // Search Body
}
// Determine if the user was searching for e-mailadresses
if (!String.IsNullOrEmpty(request.SearchReceiver))
{
// Find all e-mails that contain the search string in their to/cc/bcc e-mailadresses
emails = emails.Where(x => (!String.IsNullOrEmpty(x.to) && x.to.Contains(request.SearchReceiver)) || // Search TO
(!String.IsNullOrEmpty(x.cc) && x.cc.Contains(request.SearchReceiver)) || // Search CC
(!String.IsNullOrEmpty(x.bcc) && x.bcc.Contains(request.SearchReceiver))).ToList(); // Search BCC
}
return(emails);
}
catch (DbException ex)
{
if (retry && Helper.HandleSqlException(ex))
{
retry = false;
goto retry;
}
else
{
throw;
}
}
}
