Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- public void AddToSMRPBounceBackQueue(long agilityDistributionId, string email)
- {
- using (var connection = _dbConnectionFactory.GetDbConnection())
- {
- DateTime now = DateTime.UtcNow;
- //get DistributionContact details
- string qs = @"
- SELECT EDC.OutletContactID,EDC.ContactID,EDC.OutletID,EDC.CreatedUserID,
- CT.StatusID as ContactStatusID, OT.StatusID as OutletStatusID
- FROM EmailDistributionContact EDC
- Left Outer Join Contact CT
- on EDC.ContactID = CT.ContactID
- Left Outer Join Outlet OT
- on EDC.OutletID = OT.OutletID
- WHERE EDC.EmailDistributionID=@EmailDistributionID AND EDC.OutletContactEmail=@OutletContactEmail ";
- var distContacts = connection.Query(qs, new { EmailDistributionID = agilityDistributionId, OutletContactEmail = email });
- foreach (var distContact in distContacts)
- {
- long userId = distContact.CreatedUserID;
- //check the contact status, if exists then only live contacts will be allowed
- if (distContact.ContactStatusID == null || distContact.ContactStatusID == _StatusIdLive)
- {
- //check OutletContactElectronicAddress exists
- qs = @"select TOP 1 1 from OutletContactElectronicAddress OE
- WHERE OutletContactID=@OutletContactID AND OutletID=@OutletID AND ContactID=@ContactID
- AND OE.ElectronicAddressTypeID = @ElectronicAddressTypeID
- AND OE.ActiveFlag = 1
- AND OE.SourceSystemID = @SourceSystemID
- AND OE.ElectronicAddress=@ElectronicAddress";
- 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)
- {
- //check if it is already in the queue
- qs = @"
- SELECT TOP 1 1
- FROM OutletContactWorkQueue OCWQ
- INNER JOIN OutletContactWorkQueueDetail OCWQD ON
- OCWQ.OutletContactID = OCWQD.OutletContactID
- AND OCWQ.OutletContactWorkQueueID = OCWQD.OutletContactWorkQueueID
- WHERE
- OCWQ.OutletContactID = @OutletContactID
- AND OCWQ.WorkQueueTypeID = @WorkQueueTypeID
- AND OCWQD.BounceBackEmailAddress = @BounceBackEmailAddress
- AND OCWQ.DeletedFlag=0
- AND OCWQD.DeletedFlag=0
- AND OCWQ.LastOutletContactWorkQueueLogStatusID <> @LastOutletContactWorkQueueLogStatusID";
- if (connection.Query(qs, new { OutletContactID = distContact.OutletContactID, BounceBackEmailAddress = email, WorkQueueTypeID = (int)WorkQueueType.BounceBacks, LastOutletContactWorkQueueLogStatusID = (int)WorkQueueStatus.Completed }).Count() == 0)
- {
- //populate workqueue
- qs = @"
- set xact_abort on
- BEGIN TRAN
- DECLARE @OutletContactWorkQueueID AS bigint
- INSERT INTO OutletContactWorkQueue ( CreatedUserID,CreatedDate,systemLogDate,OutletContactID,ContactID,OutletID,WorkQueueTypeID)
- VALUES (@CreatedUserID,@now,@now,@OutletContactID,@ContactID,@OutletID,@WorkQueueTypeID )
- SET @OutletContactWorkQueueID=SCOPE_IDENTITY()
- INSERT INTO OutletContactWorkQueueLog(CreatedUserID,CreatedDate,systemLogDate,OutletContactWorkQueueID,OutletContactID,ContactID,OutletID,WorkQueueStatusID,UserID,LogDateTime)
- VALUES(@CreatedUserID,@now,@now,@OutletContactWorkQueueID,@OutletContactID,@ContactID,@OutletID,@WorkQueueStatusID,@CreatedUserID,@now)
- INSERT INTO OutletContactWorkQueueDetail (CreatedUserID,CreatedDate,systemLogDate,OutletContactWorkQueueID,OutletContactID,ContactID,OutletID,EmailDistributionID,BounceBackEmailAddress)
- VALUES(@CreatedUserID,@now,@now,@OutletContactWorkQueueID,@OutletContactID,@ContactID,@OutletID,@EmailDistributionID,@BounceBackEmailAddress)
- COMMIT
- ";
- Agility.Core.Logger.Debug("AddToSMRPBounceBackQueue.OutletContact | deDistributionId:" + agilityDistributionId + " | email:" + email);
- 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 });
- }
- }
- }
- //////////////////////////////////////////// OUTLET //////////////////////////////////////////////////
- //check the outlet status, if exists then only live outlets will be allowed
- if (distContact.OutletStatusID == null || distContact.OutletStatusID == _StatusIdLive)
- {
- //check OutletElectronicAddress
- qs = @"select TOP 1 1 from OutletElectronicAddress OE
- WHERE OutletID=@OutletID
- AND OE.ElectronicAddressTypeID = @ElectronicAddressTypeID
- AND OE.ActiveFlag = 1
- AND OE.SourceSystemID = @SourceSystemID
- AND OE.ElectronicAddress=@ElectronicAddress";
- if (connection.Query(qs, new { OutletID = distContact.OutletID, ElectronicAddress = email, SourceSystemID = (int)SourceSystem.SMRP, ElectronicAddressTypeID = (int)ElectronicAddressType.Email }).Count() > 0)
- {
- //check if it is already in the queue
- qs = @"
- SELECT TOP 1 1
- FROM OutletWorkQueue OWQ
- INNER JOIN OutletWorkQueueDetail OWQD ON
- OWQ.OutletID = OWQD.OutletID
- AND OWQ.OutletWorkQueueID = OWQD.OutletWorkQueueID
- WHERE
- OWQ.OutletID = @OutletID
- AND OWQ.WorkQueueTypeID = @WorkQueueTypeID
- AND OWQD.BounceBackEmailAddress = @BounceBackEmailAddress
- AND OWQ.DeletedFlag=0
- AND OWQD.DeletedFlag=0
- AND OWQ.LastOutletWorkQueueLogStatusID <> @LastOutletWorkQueueLogStatusID";
- if (connection.Query(qs, new { OutletID = distContact.OutletID, BounceBackEmailAddress = email, WorkQueueTypeID = (int)WorkQueueType.BounceBacks, LastOutletWorkQueueLogStatusID = (int)WorkQueueStatus.Completed }).Count() == 0)
- {
- //populate OutletWorkQueue
- qs = @"
- set xact_abort on
- BEGIN TRAN
- DECLARE @OutletWorkQueueID AS bigint
- INSERT INTO OutletWorkQueue ( CreatedUserID,CreatedDate,systemLogDate,OutletID,WorkQueueTypeID)
- VALUES (@CreatedUserID,@now,@now,@OutletID,@WorkQueueTypeID )
- SET @OutletWorkQueueID=SCOPE_IDENTITY()
- INSERT INTO OutletWorkQueueLog(CreatedUserID,CreatedDate,systemLogDate,OutletWorkQueueID,OutletID,WorkQueueStatusID,UserID,LogDateTime)
- VALUES(@CreatedUserID,@now,@now,@OutletWorkQueueID,@OutletID,@WorkQueueStatusID,@CreatedUserID,@now)
- INSERT INTO OutletWorkQueueDetail (CreatedUserID,CreatedDate,systemLogDate,OutletWorkQueueID,OutletID,EmailDistributionID,BounceBackEmailAddress)
- VALUES(@CreatedUserID,@now,@now,@OutletWorkQueueID,@OutletID,@EmailDistributionID,@BounceBackEmailAddress)
- COMMIT
- ";
- Agility.Core.Logger.Debug("AddToBouncebackQueue | CreatedUserID = " + userId + ", EmailDistributionID =" + agilityDistributionId + ", BounceBackEmailAddress =" + email + ", now =" + now + ", OutletID =" + distContact.OutletID + ", WorkQueueStatusID = " + (int)WorkQueueStatus.Created + ", WorkQueueTypeID =" + (int)WorkQueueType.BounceBacks);
- connection.Execute(qs, new { CreatedUserID = userId, EmailDistributionID = agilityDistributionId, BounceBackEmailAddress = email, now = now, OutletID = distContact.OutletID, WorkQueueStatusID = (int)WorkQueueStatus.Created, WorkQueueTypeID = (int)WorkQueueType.BounceBacks });
- Agility.Core.Logger.Debug("AddToSMRPBounceBackQueue.Outlet | agilityDistributionId:" + agilityDistributionId + " | email:" + email);
- }
- }
- }
- }
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement