(
@v_XML nvarchar(4000),
@v_UserId int
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @idoc INT
EXEC sp_xml_preparedocument @idoc OUTPUT, @v_XML
INSERT INTO aS_tblMemberContacts(CONTACTEMAIL,CONTACTNAME,MEMBERID)
SELECT * FROM OPENXML (@idoc, '/ROOT/Contact',1)WITH (Email varchar(100), UName nvarchar(100),UID INT)
WHERE Email NOT IN
(
SELECT CONTACTEMAIL FROM aS_tblMemberContacts WHERE MEMBERID=@v_UserId
)
SET NOCOUNT OFF
END
--------------------
CREATE PROCEDURE aS_sp_SaveReferralContactList
(
@v_XML nvarchar(4000),
@v_UserId bigint
)
AS
BEGIN
DECLARE @idoc INT
DECLARE @ContactName VARCHAR(50)
Declare @EmailCount int
Declare @UpdateRowCount int
Declare @UserEarn int
set @UpdateRowCount=0
set @EmailCount=0
set @UserEarn=0
BEGIN TRANSACTION
BEGIN TRY
-- DECLARE @idoc INT
EXEC sp_xml_preparedocument @idoc OUTPUT, @v_XML
INSERT INTO as_tblReferContacts(ReferEmail,ReferContact,MemberID)
SELECT Email,UName, @v_UserId
FROM OPENXML (@idoc, '/ROOT/Contact',1)WITH (Email varchar(100), UName nvarchar(100),UID INT) xmlContacts
LEFT JOIN as_tblReferContacts RC ON RC.MEMBERID=@v_UserId AND RC.ReferEmail= xmlContacts.Email
LEFT OUTER JOIN
aS_tblMembers AS MD ON MD.AccountStatusId = 1 AND MD.EmailID = xmlContacts.Email LEFT OUTER JOIN
aS_tblAlternateEmails AS AE ON AE.EmailAddress = xmlContacts.Email AND AE.Verified = 1 LEFT OUTER JOIN
as_tblEmailSettings AS ES ON ES.MemberID = ISNULL(MD.MemberID, AE.MemberID) LEFT OUTER JOIN
aS_tblMembers AS M2 ON M2.MemberID = ISNULL(MD.MemberID, AE.MemberID)
left outer join aS_tblBlockedEmails as BE on BE.Emailid=xmlContacts.Email and BE.intactivestatus=2
WHERE RC.ReferID IS NULL AND M2.MemberID IS NULL
SET @EmailCount=@@ROWCOUNT
UPDATE RC SET RC.ReferDate = GETDATE()
FROM OPENXML (@idoc, '/ROOT/Contact',1)WITH (Email varchar(100), UName nvarchar(100),UID INT) xmlContacts
INNER JOIN as_tblReferContacts RC
ON RC.MEMBERID=@v_UserId AND RC.ReferEmail= xmlContacts.Email AND RC.ReferDate
BEGIN
insert into as_tblEarnasCredit (EarnasCredit,AsCreditTypeID,MemberID,EarnDate)values (@EmailCount,2,@v_UserId,getdate())
if not exists(select memberid from as_tblasCreditBalance where memberid=@v_UserId)
begin
insert into as_tblasCreditBalance(MemberID,EarnAsCredit) values (@v_UserId,@EmailCount)
End
else
begin
select @UserEarn=isnull(EarnAsCredit,0) from as_tblasCreditBalance where memberid=@v_UserId
update as_tblasCreditBalance set EarnAsCredit=@UserEarn+@EmailCount where memberid=@v_UserId
end
END
if(@UpdateRowCount>0)
BEGIN
insert into as_tblEarnasCredit (EarnasCredit,AsCreditTypeID,MemberID,EarnDate)values (@UpdateRowCount,2,@v_UserId,getdate())
select @UserEarn=isnull(EarnAsCredit,0) from as_tblasCreditBalance where memberid=@v_UserId
update as_tblasCreditBalance set EarnAsCredit=@UserEarn+@UpdateRowCount where memberid=@v_UserId
END
EXEC SP_XML_REMOVEDOCUMENT @idoc
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
END
