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 :-)
Would also be interested in this!! Happy Firiday!
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
Just be wary that people can be confused or upset if they have an account with you that they never made. We had great difficulty creating messaging around this in TNEW, and so we don't do it anymore and have focused instead on figuring out how to simplify merging.
Gawain Lavers said:Just be wary that people can be confused or upset if they have an account with you that they never made.
They can even be confused/upset if they create a login and forget about it. Pre-pandemic we got some passionate complaints and started purging idle logins for this reason! (We are keeping them, all now, for the moment.)
This is happening to us currently! Tessitura built all of our logins for existing constituents for us. Let's just say we got a lot of confused and annoyed patrons who were trying to create new accounts and it told them an account already existed.
We are also currently having the issue of multiple accounts all using the same email (which we could do in Altru but can't in Tessitura). A lot of constituents were getting their friends info on confirmations even though it was their email. We now have to go through and delete all of the logins not being used to try and fix it.
So, yeah I would be cautious before doing this as well. Don't end up in the mess that we are currently going through!
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.
100% agree. Tread cautiously.
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.