/// <summary>
/// Gets a page sends information.
/// </summary>
/// <param name="pageSize">Size of the page to get.</param>
/// <param name="pageNum">The page to get.</param>
/// <returns>SendInfoCollection of the data page.</returns>
public static SendInfoCollection GetSends(int pageSize, int pageNum)
{
using (SqlConnection conn = MantaDB.GetSqlConnection())
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"DECLARE @sends table (RowNum int,
mta_send_internalId int)
INSERT INTO @sends
SELECT [sends].RowNumber, [sends].mta_send_internalId
FROM (SELECT (ROW_NUMBER() OVER(ORDER BY mta_send_createdTimestamp DESC)) as RowNumber, man_mta_send.mta_send_internalId
FROM man_mta_send with(nolock)) [sends]
WHERE [sends].RowNumber >= " + ((pageNum * pageSize) - pageSize + 1) + " AND [sends].RowNumber <= " + (pageSize * pageNum) + @"
SELECT [send].*,
mta_send_messages AS 'Messages',
mta_send_accepted AS 'Accepted',
mta_send_rejected AS 'Rejected',
([send].mta_send_messages - (mta_send_accepted + mta_send_rejected)) AS 'Waiting',
(SELECT COUNT(*) FROM man_mta_transaction as [tran] with(nolock) JOIN man_mta_msg as [msg] ON [tran].mta_msg_id = [msg].mta_msg_id WHERE [msg].mta_send_internalId = [send].mta_send_internalId AND [tran].mta_transactionStatus_id = 5) AS 'Throttled',
(SELECT COUNT(*) FROM man_mta_transaction as [tran] with(nolock) JOIN man_mta_msg as [msg] ON [tran].mta_msg_id = [msg].mta_msg_id WHERE [msg].mta_send_internalId = [send].mta_send_internalId AND [tran].mta_transactionStatus_id = 1) AS 'Deferred',
(SELECT MAX(mta_transaction_timestamp) FROM man_mta_transaction as [tran] with(nolock) JOIN man_mta_msg as [msg] ON [tran].mta_msg_id = [msg].mta_msg_id WHERE [msg].mta_send_internalId = [send].mta_send_internalId) AS 'LastTransactionTimestamp'
FROM man_mta_send as [send] with(nolock)
WHERE [send].mta_send_internalId in (SELECT [s].mta_send_internalId FROM @sends as [s])
ORDER BY [send].mta_send_createdTimestamp DESC";
cmd.CommandTimeout = 90; // Query can take a while to run due to the size of the Transactions table.
return(new SendInfoCollection(DataRetrieval.GetCollectionFromDatabase <SendInfo>(cmd, CreateAndFillSendInfo)));
}
}
/// <summary>
/// Gets information about VirtualMTA sends for the specified send.
/// </summary>
/// <param name="sendID">ID of the send to get information for.</param>
/// <returns>Information about the usage of each VirtualMTA in the send.</returns>
public static VirtualMtaSendInfo[] GetSendVirtualMTAStats(string sendID)
{
using (SqlConnection conn = MantaDB.GetSqlConnection())
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"
--// Get the internal Send ID
DECLARE @internalSendId int
SELECT @internalSendId = [snd].mta_send_internalId
FROM man_mta_send as [snd] WITH(nolock)
WHERE [snd].mta_send_id = @sndID
DECLARE @usedIpAddressIds table(ip_ipAddress_id int)
--// Get the IP addresses used by the send
INSERT INTO @usedIpAddressIds
SELECT DISTINCT(ip_ipAddress_id)
FROM man_mta_transaction as [tran] WITH(nolock)
JOIN man_mta_msg as [msg] with(nolock) ON [tran].mta_msg_id = [msg].mta_msg_id
WHERE [msg].mta_send_internalId = @internalSendId
--// Get the actual data
SELECT [ip].*,
(SELECT COUNT(*) FROM man_mta_transaction as [tran] with(nolock) JOIN man_mta_msg as [msg] with(nolock) ON [tran].mta_msg_id = [msg].mta_msg_id WHERE [tran].ip_ipAddress_id = [ip].ip_ipAddress_id AND [msg].mta_send_internalId = @internalSendId AND [tran].mta_transactionStatus_id = 4) AS 'Accepted',
(SELECT COUNT(*) FROM man_mta_transaction as [tran] with(nolock) JOIN man_mta_msg as [msg] with(nolock) ON [tran].mta_msg_id = [msg].mta_msg_id WHERE [tran].ip_ipAddress_id = [ip].ip_ipAddress_id AND [msg].mta_send_internalId = @internalSendId AND ([tran].mta_transactionStatus_id = 2 OR [tran].mta_transactionStatus_id = 3 OR [tran].mta_transactionStatus_id = 6)) AS 'Rejected',
(SELECT COUNT(*) FROM man_mta_transaction as [tran] with(nolock) JOIN man_mta_msg as [msg] with(nolock) ON [tran].mta_msg_id = [msg].mta_msg_id WHERE [tran].ip_ipAddress_id = [ip].ip_ipAddress_id AND [msg].mta_send_internalId = @internalSendId AND [tran].mta_transactionStatus_id = 5) AS 'Throttled',
(SELECT COUNT(*) FROM man_mta_transaction as [tran] with(nolock) JOIN man_mta_msg as [msg] with(nolock) ON [tran].mta_msg_id = [msg].mta_msg_id WHERE [tran].ip_ipAddress_id = [ip].ip_ipAddress_id AND [msg].mta_send_internalId = @internalSendId AND [tran].mta_transactionStatus_id = 1) AS 'Deferred'
FROM man_ip_ipAddress as [ip]
WHERE [ip].ip_ipAddress_id IN (SELECT * FROM @usedIpAddressIds)";
cmd.Parameters.AddWithValue("@sndID", sendID);
return(DataRetrieval.GetCollectionFromDatabase <VirtualMtaSendInfo>(cmd, CreateAndFillVirtualMtaSendInfo).ToArray());
}
}
/// <summary>
/// Gets a summary of the transactions made in the last one hour.
/// </summary>
/// <returns>Transaction Summary</returns>
public static SendTransactionSummaryCollection GetLastHourTransactionSummary()
{
using (SqlConnection conn = MantaDB.GetSqlConnection())
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"SELECT [tran].mta_transactionStatus_id, COUNT(*) AS 'Count'
FROM man_mta_transaction as [tran] WITH(nolock)
WHERE [tran].mta_transaction_timestamp >= DATEADD(HOUR, -1, GETUTCDATE())
GROUP BY [tran].mta_transactionStatus_id";
return(new SendTransactionSummaryCollection(DataRetrieval.GetCollectionFromDatabase <SendTransactionSummary>(cmd, CreateAndFillTransactionSummary)));
}
}
/// <summary>
/// Retrieves all BounceRules from the database.
/// </summary>
/// <returns>A BounceRulesCollection of all the Rules.</returns>
internal static BounceRulesCollection GetBounceRules()
{
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SqlServer"].ConnectionString))
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"
SELECT *
FROM man_evn_bounceRule
ORDER BY evn_bounceRule_executionOrder ASC";
return(new BounceRulesCollection(DataRetrieval.GetCollectionFromDatabase <BounceRule>(cmd, CreateAndFillBounceRuleFromRecord)));
}
}
/// <summary>
/// Gets a Sends Metadata from the database.
/// </summary>
/// <param name="sendID"></param>
/// <returns></returns>
public static SendMetadataCollection GetSendMetaData(int internalSendID)
{
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SqlServer"].ConnectionString))
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"SELECT *
FROM man_mta_sendMeta
WHERE mta_send_internalId = @sndID";
cmd.Parameters.AddWithValue("@sndID", internalSendID);
return(new SendMetadataCollection(DataRetrieval.GetCollectionFromDatabase <SendMetadata>(cmd, CreateAndFillSendMetadata)));
}
}
/// <summary>
/// Gets a summary of a virtual MTAs transaction history.
/// </summary>
/// <param name="ipAddressId"></param>
/// <returns></returns>
public static SendTransactionSummaryCollection GetSendSummaryForIpAddress(int ipAddressId)
{
using (SqlConnection conn = MantaDB.GetSqlConnection())
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"SELECT mta_transactionStatus_id, COUNT(*) AS 'Count'
FROM man_mta_transaction
WHERE ip_ipAddress_id = @ipAddressId
GROUP BY mta_transactionStatus_id";
cmd.Parameters.AddWithValue("@ipAddressId", ipAddressId);
return(new SendTransactionSummaryCollection(DataRetrieval.GetCollectionFromDatabase <SendTransactionSummary>(cmd, CreateAndFillSendTransactionSummaryFromRecord)));
}
}
/// <summary>
/// Gets information about the speed of sending over the last one hour.
/// </summary>
/// <returns>SendSpeedInfo</returns>
public static SendSpeedInfo GetLastHourSendSpeedInfo()
{
using (SqlConnection conn = MantaDB.GetSqlConnection())
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"
SELECT COUNT(*) AS 'Count', [tran].mta_transactionStatus_id, CONVERT(smalldatetime, [tran].mta_transaction_timestamp) as 'mta_transaction_timestamp'
FROM man_mta_transaction as [tran] WITH (nolock)
WHERE [tran].mta_transaction_timestamp >= DATEADD(HOUR, -1, GETUTCDATE())
GROUP BY [tran].mta_transactionStatus_id, CONVERT(smalldatetime, [tran].mta_transaction_timestamp)
ORDER BY CONVERT(smalldatetime, [tran].mta_transaction_timestamp)";
return(new SendSpeedInfo(DataRetrieval.GetCollectionFromDatabase <SendSpeedInfoItem>(cmd, CreateAndFillSendSpeedInfoItemFromRecord)));
}
}
/// <summary>
/// Gets information about the speed of a send.
/// </summary>
/// <param name="sendID">ID of the send to get speed information about.</param>
/// <returns>SendSpeedInfo</returns>
public static SendSpeedInfo GetSendSpeedInfo(string sendID)
{
using (SqlConnection conn = MantaDB.GetSqlConnection())
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"
DECLARE @internalSendID int
SELECT @internalSendID = mta_send_internalId
FROM man_mta_send
WHERE mta_send_id = @sndID
SELECT COUNT(*) AS 'Count', [tran].mta_transactionStatus_id, CONVERT(smalldatetime, [tran].mta_transaction_timestamp) as 'mta_transaction_timestamp'
FROM man_mta_transaction as [tran] with(nolock)
JOIN man_mta_msg AS [msg] with(nolock) ON [tran].mta_msg_id = [msg].mta_msg_id
WHERE [msg].mta_send_internalId = @internalSendID
GROUP BY [tran].mta_transactionStatus_id, CONVERT(smalldatetime, [tran].mta_transaction_timestamp)
ORDER BY CONVERT(smalldatetime, [tran].mta_transaction_timestamp)";
cmd.Parameters.AddWithValue("@sndID", sendID);
return(new SendSpeedInfo(DataRetrieval.GetCollectionFromDatabase <SendSpeedInfoItem>(cmd, CreateAndFillSendSpeedInfoItemFromRecord)));
}
}
/// <summary>
/// Gets all of the sends with messages waiting to be sent.
/// </summary>
/// <returns>SendInfoCollection of all relevent sends.</returns>
public static SendInfoCollection GetSendsInProgress()
{
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SqlServer"].ConnectionString))
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"
SELECT [send].*,
mta_send_messages AS 'Messages',
mta_send_accepted AS 'Accepted',
mta_send_rejected AS 'Rejected',
([send].mta_send_messages - (mta_send_accepted + mta_send_rejected)) AS 'Waiting',
(SELECT COUNT(*) FROM man_mta_transaction as [tran] JOIN man_mta_msg as [msg] ON [tran].mta_msg_id = [msg].mta_msg_id WHERE [msg].mta_send_internalId = [send].mta_send_internalId AND [tran].mta_transactionStatus_id = 5) AS 'Throttled',
(SELECT COUNT(*) FROM man_mta_transaction as [tran] JOIN man_mta_msg as [msg] ON [tran].mta_msg_id = [msg].mta_msg_id WHERE [msg].mta_send_internalId = [send].mta_send_internalId AND [tran].mta_transactionStatus_id = 1) AS 'Deferred',
(SELECT MAX(mta_transaction_timestamp) FROM man_mta_transaction as [tran] JOIN man_mta_msg as [msg] ON [tran].mta_msg_id = [msg].mta_msg_id WHERE [msg].mta_send_internalId = [send].mta_send_internalId) AS 'LastTransactionTimestamp'
FROM man_mta_send as [send]
WHERE ([send].mta_send_messages - (mta_send_accepted + mta_send_rejected)) > 0
ORDER BY [send].mta_send_createdTimestamp DESC";
cmd.CommandTimeout = 90; // Query can take a while to run due to the size of the Transactions table.
return(new SendInfoCollection(DataRetrieval.GetCollectionFromDatabase <SendInfo>(cmd, CreateAndFillSendInfo)));
}
}
/// <summary>
/// Gets a data page about bounces from the transactions table for a send.
/// </summary>
/// <param name="sendID">Send to get data for.</param>
/// <param name="pageNum">The page to get.</param>
/// <param name="pageSize">The size of the data pages.</param>
/// <returns>An array of BounceInfo from the data page.</returns>
public static BounceInfo[] GetBounceInfo(string sendID, int pageNum, int pageSize)
{
bool hasSendID = !string.IsNullOrWhiteSpace(sendID);
using (SqlConnection conn = MantaDB.GetSqlConnection())
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = (hasSendID ? @"
declare @internalSendID int
SELECT @internalSendID = mta_send_internalId
FROM man_mta_send WITH(nolock)
WHERE mta_send_id = @sndID
" : string.Empty) + @"
SELECT [sorted].*
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY count(*) DESC, mta_transaction_serverHostname) as 'Row',
mta_transactionStatus_id,
mta_transaction_serverResponse,
mta_transaction_serverHostname as 'mta_transaction_serverHostname',
[ip].ip_ipAddress_hostname,
[ip].ip_ipAddress_ipAddress, COUNT(*) as 'Count',
MAX(mta_transaction_timestamp) as 'LastOccurred'
FROM man_mta_transaction as [tran] with(nolock)
JOIN man_mta_msg as [msg] with(nolock) ON [tran].mta_msg_id = [msg].mta_msg_id
JOIN man_ip_ipAddress as [ip] ON [tran].ip_ipAddress_id = [ip].ip_ipAddress_id
WHERE mta_transactionStatus_id IN (1, 2, 3, 6) --// Todo: Make this enum!
" + (hasSendID ? "AND [msg].mta_send_internalId = @internalSendID " : string.Empty) + @"
GROUP BY mta_transactionStatus_id, mta_transaction_serverResponse, mta_transaction_serverHostname,[ip].ip_ipAddress_hostname, [ip].ip_ipAddress_ipAddress
) as [sorted]
WHERE [Row] >= " + (((pageNum * pageSize) - pageSize) + 1) + " AND [Row] <= " + (pageNum * pageSize);
if (hasSendID)
{
cmd.Parameters.AddWithValue("@sndID", sendID);
}
return(DataRetrieval.GetCollectionFromDatabase <BounceInfo>(cmd, CreateAndFillBounceInfo).ToArray());
}
}
/// <summary>
/// Gets the most common bounces from the last hour.
/// </summary>
/// <param name="count">Amount of bounces to get.</param>
/// <returns>Information about the bounces</returns>
public static BounceInfo[] GetLastHourBounceInfo(int count)
{
using (SqlConnection conn = MantaDB.GetSqlConnection())
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"
SELECT TOP " + count + @" ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC, mta_transaction_serverHostname) as 'Row',
mta_transactionStatus_id,
mta_transaction_serverResponse,
mta_transaction_serverHostname as 'mta_transaction_serverHostname',
[ip].ip_ipAddress_hostname,
[ip].ip_ipAddress_ipAddress, COUNT(*) as 'Count',
MAX(mta_transaction_timestamp) as 'LastOccurred'
FROM man_mta_transaction as [tran] WITH (nolock)
JOIN man_mta_msg as [msg] WITH(nolock) ON [tran].mta_msg_id = [msg].mta_msg_id
JOIN man_ip_ipAddress as [ip] ON [tran].ip_ipAddress_id = [ip].ip_ipAddress_id
WHERE [tran].mta_transaction_timestamp >= DATEADD(HOUR, -1, GETUTCDATE())
AND mta_transactionStatus_id IN (1, 2, 3, 6)
AND mta_transaction_serverHostname NOT LIKE ''
GROUP BY mta_transactionStatus_id, mta_transaction_serverResponse, mta_transaction_serverHostname,[ip].ip_ipAddress_hostname, [ip].ip_ipAddress_ipAddress
ORDER BY COUNT(*) DESC";
return(DataRetrieval.GetCollectionFromDatabase <BounceInfo>(cmd, CreateAndFillBounceInfo).ToArray());
}
}
