Target release
Epic
Document status	DRAFT
Document owner	@Robert Adach
Business POC
Designer
Developers
QA

1 Databases
2 Key Reference Information
3 Supporting Procedures
- 3.1 lp_GetNewID
4 Supporting Functions
- 4.1 IsEmailValid
- 4.2 IsZipCodeValid

Databases

The project will two database connections. User stories will reference which connection string should be used for each/all database interaction within the user story. Each database connection will have 4 configurations associated with it

The ASWB connection strings are used for WRITING records.
The ASWBOnline connection strings are used for READING records.

	Connection String Name	Server name	Database name	UserName	Description

	Connection String Name	Server name	Database name	UserName	Description
1	ASWBLocal	local	ExamRegistration
2	ASWBQA	ASWBDevData	ExamRegistration
3	ASWBTest	ASWBDecData	ExamRegistration
4	ASWB	ASWBSQL2\ASWBSQL2	ASWBTest
5	ASWBOnlineLocal	local	ExamRegistration
6	ASWBOnlineQA	ASWBDevData	ExamRegistration
7	ASWBOnlineTest	ASWBDevData	ExamRegistration
8	ASWBOnline	ASWBData	ExamRegistration
9
10

Key Reference Information

Column / Item References	ID Number	Display
ExamBeforeGraduation	0	None Selected
ExamBeforeGraduation	1	No
ExamBeforeGraduation	2	Yes
ExamBeforeGraduation	3	Maybe
ExamBeforeGraduation	99	Unknown
ExamBeforePostGrad	0	None Selected
ExamBeforePostGrad	1	No
ExamBeforePostGrad	2	Yes
ExamBeforePostGrad	3	Maybe
ExamBeforePostGrad	99	Unknown
IsJurisdiction	0	None Selected
IsJurisdiction	1	No
IsJurisdiction	2	Yes
IsJurisdiction	3	Maybe
IsJurisdiction	99	Unknown
IsMember	0	None Selected
IsMember	1	No
IsMember	2	Yes
IsMember	3	Maybe
IsMember	99	Unknown
MSWBeforeExam	0	None Selected
MSWBeforeExam	1	No
MSWBeforeExam	2	Yes
MSWBeforeExam	3	Maybe
MSWBeforeExam	99	Unknown
UsesAGExam	0	None Selected
UsesAGExam	1	No
UsesAGExam	2	Yes
UsesAGExam	3	Maybe
UsesAGExam	99	Unknown
UsesASExam	0	None Selected
UsesASExam	1	No
UsesASExam	2	Yes
UsesASExam	3	Maybe
UsesASExam	99	Unknown
UsesBSExam	0	None Selected
UsesBSExam	1	No
UsesBSExam	2	Yes
UsesBSExam	3	Maybe
UsesBSExam	99	Unknown
UsesCLExam	0	None Selected
UsesCLExam	1	No
UsesCLExam	2	Yes
UsesCLExam	3	Maybe
UsesCLExam	99	Unknown
UsesMSExam	0	None Selected
UsesMSExam	1	No
UsesMSExam	2	Yes
UsesMSExam	3	Maybe
UsesMSExam	99	Unknown
Exam Level	0	None Selected
Exam Level	1	Associate
Exam Level	2	Bachelors
Exam Level	3	Masters
Exam Level	4	Advanced Generalist
Exam Level	5	Clinical

Supporting Procedures

Supporting procedures are SQL stored procedures used by the legacy system.

lp_GetNewID


DROP PROCEDURE IF EXISTS [dbo].[lp_GetNewID]
GO

CREATE PROCEDURE [dbo].[lp_GetNewID] @type INT, @id INT OUTPUT
as
set nocount on
begin tran
select @id =  LastID from tblID where [ID] = @type
if (@id = 0) or (@id is null)
    begin
        raiserror ('Error:  Unknown type of id - is there a row for it in tblID?',15,1)
        rollback
        select @id = -99
        return -1 --no record for this type
    end
else 
  begin
    select @id = @id + 1
    update tblID set LastID = @id where [ID] = @type
    commit
  end
set nocount off
return 0  --successful

Supporting Functions

Supporting functions are C# methods used to assist in determining field validity, verifying data, cleansing data and are based on the identically named functions in the legacy codebase.

IsEmailValid

static public bool IsEmailValid(string emailAddress)
            {
            // Make sure that there is a string passed    
            if (String.IsNullOrEmpty(emailAddress))
            {
                return false;
            }
            // Make sure the email address 
            //  -- does not have a space
            //  -- does not have two dots in a row
            //  -- does not have a comma
            //  -- the @ symbol is at least the 3rd character
            //  -- the . is at least the 3rd character
            //  -- there is a period after the @ symbol
            if (emailAddress.IndexOf(" ") > 0 || emailAddress.IndexOf("..") > 0 || (emailAddress.IndexOf(",") > 0)
                || emailAddress.IndexOf("@") < 2 || emailAddress.IndexOf(".") < 2
                || (Math.Abs(emailAddress.IndexOf("@") - emailAddress.IndexOf(".")) < 2))
                {
                return false;
                }
            // Make sure that the email address does not containg 2 @ symbols
            if (Regex.Matches(emailAddress, "@").Count > 1)
                {
                return false;
                }
            // Make sure last period is not too close to the end of the email address
            // hello@hello || hello@hello. || hello@hello.c are invalid
            // hello@helloa.ca is valid
            if (emailAddress.LastIndexOf(".") == emailAddress.Length || emailAddress.LastIndexOf(".") > (emailAddress.Length -3))
                { return false; }

            return true;
            }

IsZipCodeValid

This needs to be converted from Delphi to C#



function IsValidZipCode(vTheZipCode : string;
                        vIsCanadian : boolean; var vMsg : string) : boolean;
begin
  result := true;
  //don't initialize vMsg.  You might want to append to an existing msg.
  if vIsCanadian then
    begin
      //Must be 6 chars, or 6 chars + space
      if ((pos(' ', vTheZipCode) > 0) and (length(vTheZipCode) <> 7))
           OR ((pos(' ', vTheZipCode) = 0) and (length(vTheZipCode) <> 6)) then
        begin
          result := false;
          vMsg := vMsg + 'Canadian postal code must be 6 characters, '
                       + ' or 7 including space.' + #10 + #13;
        end;
      if (pos('-', vTheZipCode) > 0) then
        begin
          result := false;
          vMsg := vMsg
               +  'Canadian postal code may not contain a dash' + #10 + #13;
        end;
      //if have space, must be middle character
      if length(vTheZipCode) = 7 then
        if pos(' ', vTheZipCode) <> 4 then
          begin
            result := false;
            vMsg := vMsg
               +  'Space in Canadian postal code must be in the middle' + #10 + #13;
          end;
    end else
      begin
        //must be 5 chars, or 10 chars including dash
        if ((pos('-', vTheZipCode) > 0) and (length(vTheZipCode) <> 10))
            OR ((pos('-', vTheZipCode) = 0) and (length(vTheZipCode) <> 5)) then
          begin
            result := false;
            vMsg := vMsg
                 +  'Zip code must be either 5 or 10 digits long' + #10 + #13;
          end;
        //must be numeric except for the 6th char
        if not AllDigits(copy(vTheZipCode,1,5) + copy(vTheZipCode,7,4)) then
          begin
            result := false;
            vMsg := vMsg
                 +  'Zip code must be all numbers, except for dash in Zip+4' + #10 + #13;
          end;
      end;
end;

IT Development Projects

Development Overview