====== pears.NetForgottenPassword ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== Original SQL =====
<code sql>
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='test@test.com',emailaddress='test@test.com' where iqxnetuserid=(select top 1 iqxnetuserid from iqxnetuserlink where personid='TEST');
select * from NetForgottenPassword(null,null);
expect 100:*;
select * from NetForgottenPassword(null,'test@test.com');
expect 0:*;
select * from NetForgottenPassword('TEST.Client',null);
expect 100:*;
select * from NetForgottenPassword('TEST.Client','z');
expect 100:*;
select * from NetForgottenPassword('TEST.Client','zz@test.com');
expect 100:*;
select * from NetForgottenPassword('TEST.Client','test@test.com');
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='test@test.com',emailaddress='test@test.com' where iqxnetuserid=(select top 1 iqxnetuserid from iqxnetuserlink where personid='TEST');
select * from NetForgottenPassword(null,null);
expect 100:*;
select * from NetForgottenPassword(null,'test@test.com');
expect 0:*;
select * from NetForgottenPassword('TEST.Client',null);
expect 100:*;
select * from NetForgottenPassword('TEST.Client','z');
expect 100:*;
select * from NetForgottenPassword('TEST.Client','zz@test.com');
expect 100:*;
select * from NetForgottenPassword('TEST.Client','test@test.com');
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

*/
}
</code>