pears.NetForgottenPassword

CREATE PROCEDURE "pears"."NetForgottenPassword"( IN "pWebUserID" CHAR(20),IN "login_email" CHAR(150) ) 
RESULT( "retval" CHAR(255) ) 
// IQXWeb
BEGIN
  DECLARE "PersID" CHAR(20);
  SET "PersID" = (SELECT top 1 "IQXNetUserID" FROM "IQXNetUser" WHERE "EmailAddress" = "login_email" ORDER BY "LoginID" ASC);
  IF "isnull"("PersID",'') <> '' THEN
    IF(SELECT top 1 "value" FROM "iqxnetswitch" WHERE "iqxnetswitchid" = 'PWD_RESET_EXP_INT') = 1 THEN
      UPDATE "IQXNetUser" SET "PasswordSet" = 2,"expirydate" = "dateadd"("dd",3,"today"()) WHERE "IQXNetUserID" = "PersID"
    ELSE
      UPDATE "IQXNetUser" SET "PasswordSet" = 2 WHERE "IQXNetUserID" = "PersID"
    END IF;
    SELECT '0:~success'
  ELSE
    SELECT '0:~success' -- For security reasons - surpress the Login not found information dialog.
  END IF
END /* TEST
call NetTestSetup('');
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.client','test.agency','test.candidate','test.owner');
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.client';
update iqxnetuser set loginid='[email protected]',emailaddress='[email protected]' where iqxnetuserid=(select top 1 iqxnetuserid from iqxnetuserlink where personid='TEST');
select * from NetForgottenPassword(null,null);
expect 100:*;
select * from NetForgottenPassword(null,'[email protected]');
expect 0:*;
select * from NetForgottenPassword('TEST.Client',null);
expect 100:*;
select * from NetForgottenPassword('TEST.Client','z');
expect 100:*;
select * from NetForgottenPassword('TEST.Client','[email protected]');
expect 100:*;
select * from NetForgottenPassword('TEST.Client','[email protected]');
expect 0:*;
select passwordset from iqxnetuser where iqxnetuserid=(select top 1 iqxnetuserid from iqxnetuserlink where personid='TEST');
expect passwordset=2;
expect EOF;
update iqxnetuser set emailaddress=null,passwordset=1 where iqxnetuserid=(select top 1 iqxnetuserid from iqxnetuserlink where personid='TEST');
*/
/* DOC
2016-08-25 PC test routine and documentation
2017-02-24 PC change incoming param to 150, and change to loginid from emailaddress
2017-11-02 PC change to set expiry day 
2018-08-27 PC improve error handler
2019-06-19 GJ Surpressed the "Login not found" message as it may be a security concern.
 
tests:
null pWebUserID and null login_email
null pWebUserID and valid login_email
empty login_email
invalid login_email
incorrect login_email
correct login_email
 
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetForgottenPassword" IS 
{CREATE PROCEDURE pears."NetForgottenPassword"(IN pWebUserID CHAR(20),IN login_email CHAR(150))
RESULT(retval CHAR(255))
// IQXWeb
BEGIN
  DECLARE PersID CHAR(20);
  SET PersID=(SELECT top 1 IQXNetUserID FROM IQXNetUser WHERE EmailAddress = login_email ORDER BY LoginID);
  IF isnull(PersID,'') <> '' THEN
    IF (SELECT top 1 VALUE FROM iqxnetswitch WHERE iqxnetswitchid='PWD_RESET_EXP_INT') = 1 THEN
        UPDATE IQXNetUser SET PasswordSet = 2,expirydate = dateadd(dd,3,today()) WHERE IQXNetUserID = PersID;
    ELSE 
        UPDATE IQXNetUser SET PasswordSet = 2 WHERE IQXNetUserID = PersID;
    END IF;
    SELECT '0:~success'
  ELSE
    SELECT '0:~success' -- For security reasons - surpress the Login not found information dialog.
  END IF
END
 
/* TEST
call NetTestSetup('');
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.client','test.agency','test.candidate','test.owner');
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.client';
update iqxnetuser set loginid='[email protected]',emailaddress='[email protected]' where iqxnetuserid=(select top 1 iqxnetuserid from iqxnetuserlink where personid='TEST');
select * from NetForgottenPassword(null,null);
expect 100:*;
select * from NetForgottenPassword(null,'[email protected]');
expect 0:*;
select * from NetForgottenPassword('TEST.Client',null);
expect 100:*;
select * from NetForgottenPassword('TEST.Client','z');
expect 100:*;
select * from NetForgottenPassword('TEST.Client','[email protected]');
expect 100:*;
select * from NetForgottenPassword('TEST.Client','[email protected]');
expect 0:*;
select passwordset from iqxnetuser where iqxnetuserid=(select top 1 iqxnetuserid from iqxnetuserlink where personid='TEST');
expect passwordset=2;
expect EOF;
update iqxnetuser set emailaddress=null,passwordset=1 where iqxnetuserid=(select top 1 iqxnetuserid from iqxnetuserlink where personid='TEST');
*/
 
/* DOC
2016-08-25 PC test routine and documentation
2017-02-24 PC change incoming param to 150, and change to loginid from emailaddress
2017-11-02 PC change to set expiry day 
2018-08-27 PC improve error handler
2019-06-19 GJ Surpressed the "Login not found" message as it may be a security concern.
 
tests:
    null pWebUserID and null login_email
    null pWebUserID and valid login_email
    empty login_email
    invalid login_email
    incorrect login_email
    correct login_email
 
*/
}