Hi all, and HAPPY FRIDAY!!!
We are looking at creating web logins for those who have an email address associated with their account, but no login. We're hoping it will cut down a little on duplicate account creation, as well as help people who wish to renew their subs online. Does anyone happen to have a script floating around that they'd mind sharing? I've found some old things from v11-ish era, and that's so long ago at this point. Thanks :-)
USE [impresario]
GO
/****** Object: StoredProcedure [dbo].[LP_GPO_CREATE_TEMP_LOGIN] Script Date: 27/11/2019 14:50:11 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE PROCEDURE [dbo].[LP_GPO_CREATE_TEMP_LOGIN](
@customer_no int = null
)
AS
Set NoCount On
--/*-----------------------------------------------------------------------------------------------------
-- New procedure to create a temporary login for a specific customer, or by passing in a blank customer no,
-- can be used to update all individual constituent records that do not currently have a login
-- Created by CEL on 30th October 2019
--Assumption:
--n1n2_ind: (1)
--login_type: Primary_Login (1)
--login: same as customer primary email address
--Password: Null
--Associated email address: none (0)
Declare @login_no int,
@login_address varchar(80) = null,
@eaddress_no int = 0,
@login_type int = 1, -- Primary Login
@n1n2_ind int = 1,
@inactive char = 'N',
@primary_ind char = 'Y',
@temporary_ind char = 'Y' -- temporary login created
-- Creating a temporary login for a specific customer
If @customer_no >= 0
Begin
-- check a temporary login does not exist
IF (NOT EXISTS (
SELECT b.address, b.eaddress_no
FROM T_EADDRESS b
JOIN T_CUSTOMER a ON B.CUSTOMER_NO = A.CUSTOMER_NO
WHERE
b.customer_no = @customer_no
AND b.EADDRESS_TYPE = 1 and b.primary_ind ='Y'--
AND EXISTS (SELECT login_no FROM t_cust_login WHERE customer_no=a.customer_no)
AND EXISTS (SELECT 1 FROM T_EADDRESS WHERE customer_no = @customer_no) -- an email address must exist
AND NOT EXISTS (SELECT login_no FROM t_cust_login WHERE LOGIN = b.ADDRESS)
AND a.cust_type in (Select id from TR_CUST_TYPE WHERE cust_group = 1 AND INACTIVE = 'n')
AND a.INACTIVE = 1)
---- If customer does not have a login record
-- confirm an e mail address exists for this constituent
SET @eaddress_no = 0
SET @eaddress_no = (SELECT eaddress_no FROM T_EADDRESS WHERE customer_no = @customer_no)
IF @eaddress_no > 0
-- Create a new temporary login for the customer
EXEC @login_no=AP_GET_NEXTID_FUNCTION @type = 'LO'
SET @login_address = (SELECT address FROM T_EADDRESS WHERE customer_no = @customer_no)
INSERT INTO t_cust_login (
login_no,
customer_no,
n1n2_ind,
login_type,
login,
password,
eaddress_no,
primary_ind,
inactive,
create_dt,
temporary_ind )
VALUES (
@login_no,
@customer_no,
@n1n2_ind,
@login_type,
@login_address,
null,
@eaddress_no,
@primary_ind,
@inactive,
GETDATE(),
@temporary_ind)
End
Else
-- if the customer parameter is null, run a cursor to find all individual customers that do not have a login
--
DECLARE cust_curs cursor for
SELECT a.customer_no, b.address, b.eaddress_no
FROM T_EADDRESS (Nolock) b
b.EADDRESS_TYPE = 1 and b.primary_ind ='Y'
AND NOT EXISTS (SELECT login_no FROM t_cust_login WHERE customer_no=a.customer_no)
AND a.INACTIVE = 1
-- begin cursor
OPEN cust_curs
FETCH cust_curs into @customer_no, @login_address, @eaddress_no
while @@fetch_status = 0
begin
-- Create a login
-- end cursor
CLOSE cust_curs
DEALLOCATE cust_curs
Honestly I use this (changed to run off a list) specifically for Subscribers and Academy Households at renewal/registration time and they get an email to go directly to password reset and it does seem to work 90% of the time.
Yep, this would be for my subs people only! We're concerned that otherwise they'll create a new account and then call to complain that they don't see their sub pricing and everything.