Advertisement
Guest User

Untitled

a guest
Aug 19th, 2019
69
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
C# 8.31 KB | None | 0 0
  1. public void AddToSMRPBounceBackQueue(long agilityDistributionId, string email)
  2.         {
  3.             using (var connection = _dbConnectionFactory.GetDbConnection())
  4.             {
  5.                 DateTime now = DateTime.UtcNow;
  6.                 //get DistributionContact details
  7.                 string qs = @"    
  8.            SELECT EDC.OutletContactID,EDC.ContactID,EDC.OutletID,EDC.CreatedUserID,
  9.                   CT.StatusID as ContactStatusID, OT.StatusID as OutletStatusID
  10.            FROM EmailDistributionContact EDC
  11.                 Left Outer Join Contact CT
  12.                    on EDC.ContactID = CT.ContactID
  13.                 Left Outer Join Outlet OT
  14.                    on EDC.OutletID = OT.OutletID                
  15.                  WHERE EDC.EmailDistributionID=@EmailDistributionID AND EDC.OutletContactEmail=@OutletContactEmail ";
  16.                 var distContacts = connection.Query(qs, new { EmailDistributionID = agilityDistributionId, OutletContactEmail = email });
  17.                 foreach (var distContact in distContacts)
  18.                 {
  19.                     long userId = distContact.CreatedUserID;
  20.                     //check the contact status, if exists then only live contacts will be allowed
  21.                     if (distContact.ContactStatusID == null || distContact.ContactStatusID == _StatusIdLive)
  22.                     {                        
  23.                         //check OutletContactElectronicAddress exists
  24.                         qs = @"select TOP 1 1 from OutletContactElectronicAddress OE
  25.     WHERE OutletContactID=@OutletContactID AND OutletID=@OutletID AND ContactID=@ContactID
  26.              AND    OE.ElectronicAddressTypeID  = @ElectronicAddressTypeID
  27.          AND    OE.ActiveFlag               = 1
  28.          AND    OE.SourceSystemID           = @SourceSystemID
  29.          AND OE.ElectronicAddress=@ElectronicAddress";
  30.                         if (connection.Query(qs, new { ElectronicAddress = email, OutletContactID = distContact.OutletContactID, OutletID = distContact.OutletID, ContactID = distContact.ContactID, SourceSystemID = (int)SourceSystem.SMRP, ElectronicAddressTypeID = (int)ElectronicAddressType.Email }).Count() > 0)
  31.                         {
  32.                             //check if it is already in the queue
  33.                             qs = @"
  34. SELECT TOP 1 1
  35.     FROM OutletContactWorkQueue OCWQ
  36.     INNER JOIN OutletContactWorkQueueDetail OCWQD  ON
  37.         OCWQ.OutletContactID =  OCWQD.OutletContactID
  38.         AND OCWQ.OutletContactWorkQueueID =  OCWQD.OutletContactWorkQueueID
  39. WHERE
  40.     OCWQ.OutletContactID =  @OutletContactID
  41.     AND OCWQ.WorkQueueTypeID =  @WorkQueueTypeID
  42.     AND OCWQD.BounceBackEmailAddress =  @BounceBackEmailAddress
  43.     AND OCWQ.DeletedFlag=0
  44.     AND OCWQD.DeletedFlag=0
  45.     AND OCWQ.LastOutletContactWorkQueueLogStatusID <> @LastOutletContactWorkQueueLogStatusID";
  46.                             if (connection.Query(qs, new { OutletContactID = distContact.OutletContactID, BounceBackEmailAddress = email, WorkQueueTypeID = (int)WorkQueueType.BounceBacks, LastOutletContactWorkQueueLogStatusID = (int)WorkQueueStatus.Completed }).Count() == 0)
  47.                             {
  48.                                 //populate workqueue
  49.                                 qs = @"
  50. set xact_abort on
  51. BEGIN TRAN
  52. DECLARE @OutletContactWorkQueueID AS bigint
  53. INSERT INTO OutletContactWorkQueue ( CreatedUserID,CreatedDate,systemLogDate,OutletContactID,ContactID,OutletID,WorkQueueTypeID)
  54.     VALUES (@CreatedUserID,@now,@now,@OutletContactID,@ContactID,@OutletID,@WorkQueueTypeID )
  55. SET @OutletContactWorkQueueID=SCOPE_IDENTITY()
  56. INSERT INTO OutletContactWorkQueueLog(CreatedUserID,CreatedDate,systemLogDate,OutletContactWorkQueueID,OutletContactID,ContactID,OutletID,WorkQueueStatusID,UserID,LogDateTime)
  57.     VALUES(@CreatedUserID,@now,@now,@OutletContactWorkQueueID,@OutletContactID,@ContactID,@OutletID,@WorkQueueStatusID,@CreatedUserID,@now)
  58. INSERT INTO OutletContactWorkQueueDetail (CreatedUserID,CreatedDate,systemLogDate,OutletContactWorkQueueID,OutletContactID,ContactID,OutletID,EmailDistributionID,BounceBackEmailAddress)
  59.     VALUES(@CreatedUserID,@now,@now,@OutletContactWorkQueueID,@OutletContactID,@ContactID,@OutletID,@EmailDistributionID,@BounceBackEmailAddress)
  60. COMMIT
  61. ";
  62.                                 Agility.Core.Logger.Debug("AddToSMRPBounceBackQueue.OutletContact | deDistributionId:" + agilityDistributionId + " | email:" + email);
  63.                                 connection.Execute(qs, new { CreatedUserID = userId, EmailDistributionID = agilityDistributionId, BounceBackEmailAddress = email, now = now, OutletContactID = distContact.OutletContactID, ContactID = distContact.ContactID, OutletID = distContact.OutletID, WorkQueueStatusID = (int)WorkQueueStatus.Created, WorkQueueTypeID = (int)WorkQueueType.BounceBacks });
  64.                             }
  65.                         }
  66.                     }
  67.                     ////////////////////////////////////////////    OUTLET    //////////////////////////////////////////////////
  68.  
  69.                     //check the outlet status, if exists then only live outlets will be allowed
  70.                     if (distContact.OutletStatusID == null || distContact.OutletStatusID == _StatusIdLive)
  71.                     {
  72.                         //check OutletElectronicAddress
  73.                         qs = @"select TOP 1 1 from OutletElectronicAddress OE
  74.     WHERE OutletID=@OutletID
  75.         AND    OE.ElectronicAddressTypeID  = @ElectronicAddressTypeID
  76.          AND    OE.ActiveFlag               = 1
  77.          AND    OE.SourceSystemID           = @SourceSystemID
  78.          AND OE.ElectronicAddress=@ElectronicAddress";
  79.                         if (connection.Query(qs, new { OutletID = distContact.OutletID, ElectronicAddress = email, SourceSystemID = (int)SourceSystem.SMRP, ElectronicAddressTypeID = (int)ElectronicAddressType.Email }).Count() > 0)
  80.                         {
  81.                             //check if it is already in the queue
  82.                             qs = @"
  83. SELECT TOP 1 1
  84.     FROM OutletWorkQueue OWQ
  85.     INNER JOIN OutletWorkQueueDetail OWQD  ON
  86.         OWQ.OutletID =  OWQD.OutletID
  87.         AND OWQ.OutletWorkQueueID =  OWQD.OutletWorkQueueID
  88. WHERE
  89.     OWQ.OutletID =  @OutletID
  90.     AND OWQ.WorkQueueTypeID =  @WorkQueueTypeID
  91.     AND OWQD.BounceBackEmailAddress =  @BounceBackEmailAddress
  92.     AND OWQ.DeletedFlag=0
  93.     AND OWQD.DeletedFlag=0
  94.     AND OWQ.LastOutletWorkQueueLogStatusID <> @LastOutletWorkQueueLogStatusID";
  95.                             if (connection.Query(qs, new { OutletID = distContact.OutletID, BounceBackEmailAddress = email, WorkQueueTypeID = (int)WorkQueueType.BounceBacks, LastOutletWorkQueueLogStatusID = (int)WorkQueueStatus.Completed }).Count() == 0)
  96.                             {
  97.                                 //populate OutletWorkQueue
  98.                                 qs = @"
  99. set xact_abort on
  100. BEGIN TRAN
  101. DECLARE @OutletWorkQueueID AS bigint
  102. INSERT INTO OutletWorkQueue ( CreatedUserID,CreatedDate,systemLogDate,OutletID,WorkQueueTypeID)
  103.     VALUES (@CreatedUserID,@now,@now,@OutletID,@WorkQueueTypeID )
  104. SET @OutletWorkQueueID=SCOPE_IDENTITY()
  105. INSERT INTO OutletWorkQueueLog(CreatedUserID,CreatedDate,systemLogDate,OutletWorkQueueID,OutletID,WorkQueueStatusID,UserID,LogDateTime)
  106.     VALUES(@CreatedUserID,@now,@now,@OutletWorkQueueID,@OutletID,@WorkQueueStatusID,@CreatedUserID,@now)
  107. INSERT INTO OutletWorkQueueDetail (CreatedUserID,CreatedDate,systemLogDate,OutletWorkQueueID,OutletID,EmailDistributionID,BounceBackEmailAddress)
  108.     VALUES(@CreatedUserID,@now,@now,@OutletWorkQueueID,@OutletID,@EmailDistributionID,@BounceBackEmailAddress)
  109. COMMIT
  110. ";
  111.                                 Agility.Core.Logger.Debug("AddToBouncebackQueue | CreatedUserID = " + userId + ", EmailDistributionID =" + agilityDistributionId + ", BounceBackEmailAddress =" + email + ", now =" + now + ", OutletID =" + distContact.OutletID + ", WorkQueueStatusID = " + (int)WorkQueueStatus.Created + ", WorkQueueTypeID =" + (int)WorkQueueType.BounceBacks);
  112.                                 connection.Execute(qs, new { CreatedUserID = userId, EmailDistributionID = agilityDistributionId, BounceBackEmailAddress = email, now = now, OutletID = distContact.OutletID, WorkQueueStatusID = (int)WorkQueueStatus.Created, WorkQueueTypeID = (int)WorkQueueType.BounceBacks });
  113.                                 Agility.Core.Logger.Debug("AddToSMRPBounceBackQueue.Outlet | agilityDistributionId:" + agilityDistributionId + " | email:" + email);
  114.                             }
  115.                         }
  116.                     }
  117.                 }
  118.             }
  119.         }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement