USP_GET_MFA_INCOMPLETE_SIGIN_PARTS

CREATE procedure dbo.USP_GET_MFA_INCOMPLETE_SIGIN_PARTS
as
begin
    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;

    -- Insert statements for procedure here


  DECLARE @IncompleteParts TABLE (
    ContentID INT,
    ContentTypesID INT
    );

  --Get all sign in parts where either MFACodeTemplate or ChangeEmailAddressEmailTemplate have not been setup

  INSERT INTO @IncompleteParts
  SELECT ContentID, ContentTypesID
  FROM SignInProperties
  INNER JOIN Sitecontent ON SiteContent.ID = SignInProperties.ContentID
  INNER JOIN PageContent ON PageContent.SiteContentID = SiteContent.ID
  WHERE (
        MFACodeTemplateID IS NULL
        OR MFACodeTemplateID = - 1
        OR ChangeEmailAddressEmailTemplateID IS NULL
        OR ChangeEmailAddressEmailTemplateID = - 1
        )
    AND Sitecontent.Deleted = 0
    AND SiteContent.InTrashBin = 0
    AND ContentTypesID IN (33, 92)

  --Get all sign in parts where MFACodeTemplate has been setup but the "from address" has not not been setup in the emailtemplate

  INSERT INTO @IncompleteParts
  SELECT ContentID, ContentTypesID
  FROM SignInProperties
  INNER JOIN Sitecontent ON SiteContent.ID = SignInProperties.ContentID
  INNER JOIN PageContent ON PageContent.SiteContentID = SiteContent.ID
  INNER JOIN EmailTemplate ON EmailTemplate.ID = MFACodeTemplateID
  WHERE ISNULL(MFACodeTemplateID, 0) > 0
    AND LTRIM(RTRIM(EmailTemplate.FromAddress)) = ''
    AND Sitecontent.Deleted = 0
    AND SiteContent.InTrashBin = 0
    AND ContentTypesID IN (33, 92)

  --Get all sign in parts where ChangeEmailAddressEmailTemplate has been setup but the "from address" has not not been setup in the emailtemplate

  INSERT INTO @IncompleteParts
  SELECT ContentID, ContentTypesID
  FROM SignInProperties
  INNER JOIN Sitecontent ON SiteContent.ID = SignInProperties.ContentID
  INNER JOIN PageContent ON PageContent.SiteContentID = SiteContent.ID
  INNER JOIN EmailTemplate ON EmailTemplate.ID = ChangeEmailAddressEmailTemplateID
  WHERE ISNULL(ChangeEmailAddressEmailTemplateID, 0) > 0
    AND LTRIM(RTRIM(EmailTemplate.FromAddress)) = ''
    AND Sitecontent.Deleted = 0
    AND SiteContent.InTrashBin = 0
    AND ContentTypesID IN (33, 92)

  --Remove all sign in parts where either NewUserTemplateID or ForgottenPWDTemplateID have not been setup

  DELETE
  FROM @IncompleteParts
  WHERE ContentID IN (
          SELECT ContentID
          FROM SignInProperties
          INNER JOIN Sitecontent ON SiteContent.ID = SignInProperties.ContentID
          INNER JOIN PageContent ON PageContent.SiteContentID = SiteContent.ID
          WHERE (
                  NewUserTemplateID IS NULL
                  OR NewUserTemplateID = - 1
                  OR ForgottenPWDTemplateID IS NULL
                  OR ForgottenPWDTemplateID = - 1
                  )
              AND Sitecontent.Deleted = 0
              AND SiteContent.InTrashBin = 0
              AND ContentTypesID IN (33)
          )

  --Remove all sign in parts where NewUserTemplateID has been setup but the "from address" has not not been setup in the emailtemplate

  DELETE
  FROM @IncompleteParts
  WHERE ContentID IN (
          SELECT ContentID
          FROM SignInProperties
          INNER JOIN Sitecontent ON SiteContent.ID = SignInProperties.ContentID
          INNER JOIN PageContent ON PageContent.SiteContentID = SiteContent.ID
          INNER JOIN EmailTemplate ON EmailTemplate.ID = NewUserTemplateID
          WHERE ISNULL(NewUserTemplateID, 0) > 0
              AND LTRIM(RTRIM(EmailTemplate.FromAddress)) = ''
              AND Sitecontent.Deleted = 0
              AND SiteContent.InTrashBin = 0
              AND ContentTypesID IN (33)
          )

  --Remove all sign in parts where ForgottenPWDTemplateID has been setup but the "from address" has not not been setup in the emailtemplate

  DELETE
  FROM @IncompleteParts
  WHERE ContentID IN (
          SELECT ContentID
          FROM SignInProperties
          INNER JOIN Sitecontent ON SiteContent.ID = SignInProperties.ContentID
          INNER JOIN PageContent ON PageContent.SiteContentID = SiteContent.ID
          INNER JOIN EmailTemplate ON EmailTemplate.ID = ForgottenPWDTemplateID
          WHERE ISNULL(ForgottenPWDTemplateID, 0) > 0
              AND LTRIM(RTRIM(EmailTemplate.FromAddress)) = ''
              AND Sitecontent.Deleted = 0
              AND SiteContent.InTrashBin = 0
              AND ContentTypesID IN (33)
          )

  --Remove invalid PPM parts

  DELETE
  FROM @IncompleteParts
  WHERE ContentID IN (
          SELECT SiteContentID
          FROM FundRaisers
          WHERE IsFunctional = 0
          )

  DELETE
  FROM @IncompleteParts
  WHERE ContentID IN (
          SELECT ContentID
          FROM @IncompleteParts
          WHERE ContentTypesID = 119
          )

  SELECT DISTINCT ContentID, ContentTypesID
  FROM @IncompleteParts

  -- 33    User Login, 92    Personal Page Manager

end