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