How-To and Fixes

Problem: Missing references and NuGet issues when setting up dev environment.

Description:

Missing references and errors when running NuGet Update.

Solution:

Asuquo Eniang

commented Dec 2, 2019

Note that some referenced assembly may be missing from solution. You can use NuGet to update -reinstall packages.

You may get error:

One or more errors occurred.

Unable to load the service index for source http://ncrws366/saf-sec-sur/4/TcNugetFeed/nuget.

An error occurred while sending the request.

The remote name could not be resolved: 'ncrws366'

Go to Tools->NuGet Packagae Manager->Package Manager Settings->Package Sources [Tab]. Uncheck or delete the missing package.

Here are the active feeds at the time of this writing:

Asuquo Eniang

commented Dec 2, 2019

Dev environment for PCOCDS-Admin successfully set up after speaking with Yi about branching strategy. He said to create a feature (task) branch off of the master, and create pull requests as needed.

Problem: Test Result Showing Incomplete even though they passed test

LINK to DevOps ticket 120043

-- 1. Find the record
select * from TM110_PCOCTO_OPERATOR
where upper(first_name_nm) like upper('%keon%') and UPPER(LAST_NAME_NM) like upper('%Nabavi%')
ORDER BY DATE_CREATED_DTE DESC;

select * from TM110_PCOCTO_OPERATOR Where OPERATOR_EMAIL_TXT='Nabavikeon@gmail.com';

-- 2. Check the latest attempt by using the values found in query 1
Select * From Lm109_Exam_Grp_Token WHERE PCOCTO_OPERATOR_CD in(2245383,2245221)
ORDER BY DATE_CREATED_DTE DESC;
-- use Exam_group_id for latest attempt

-- 3. Check if user actually passed,
SELECT COUNT(PASS_QUESTION_IND) AS QuestionsCorrect
FROM TM107_XREF_EXAM_GRP_QUESTION t
WHERE T.EXAM_GROUP_ID = '2246659' and Pass_Question_ind = 1;
--46 out of 50 is a pass

-- 4. Update the record Lm109_Exam_Grp_Token to 1
Update Lm109_Exam_Grp_Token
set result_status_type_cd = 1
where PCOCTO_OPERATOR_CD = 2245383;

-- 5. One more update required
SELECT *
FROM TM104_EXAM_GROUP_VERSIONS
WHERE EXAM_GROUP_ID IN (2246659)
ORDER BY DATE_LAST_UPDATE_DTE DESC;

--update the exam indicator to 1 if passed
update TM104_EXAM_GROUP_VERSIONS
set exam_passed_ind = 1
where EXAM_GROUP_ID = 2246659;

Problem: Second exam token showing as already used

Description:

Client failed the test, but was unable to re-take the PCOC test after waiting mandatory 24hr period with the Token provided.
The PCOC Database Support team will investigate and issue a new token ASAP.

Solution: See Mike-WIP DOS 765 https://dev dot azure.com/DSD-Marine/MSS-Portfolio/_workitems/edit/765

Problem: System shows Unknown Result when client tried to get the result.

Description: The client taken three PCOC tests, he got the result first time but for the other two attempts the system is showing Unknown Result.

Solution: The user will get Unknown Result when the exam token is deleted. Exam tokens are deleted if the candidate fails an exam and a new token is re-issued. Exam tokens are also deleted in case a brand new token is issued to the candidate.

Updated (J. Green, 2019-04-30): Let's see if we can fix this issue the same way that we resolved DOS 717.

To get all tokens for Paul Harry, let's do a search on the LM109 table for all PCOCTO_OPERATOR_CD for this user.

select *

from TM110_PCOCTO_OPERATOR

where upper(first_name_nm) like upper('paul%')

and UPPER(LAST_NAME_NM) like upper('%harry%')

ORDER BY DATE_CREATED_DTE DESC;

Use the PCOCTO_OPERATOR_CD values from the results and get all tokens:

select *

from lm109_exam_grp_token

where pcocto_operator_cd in (1670924,1670923,1670869,1669160,1669155);

The token (and results for those tests) are as follows:

027888770BEA44CA8917B898291D348C FAILED (RE-ISSUE)

1F114A22931345E397B661734B1F9AFC NOT STARTED (RE-ISSUE)

2A636669501741AF9593592E5D8E86D8 FAILED

736EE9EC5F6048AC874200D74ED6DBB0 FAILED (RE-ISSUE)

98C4BCB471F6423393BF879C079F83D0 FAILED

Let's get all exam information for Paul's newest, unused exam token:

SELECT tm104.*

FROM TM104_EXAM_GROUP_VERSIONS tm104, lm109_exam_grp_token lm109

WHERE exam_grp_token_id = '1F114A22931345E397B661734B1F9AFC'

and tm104.exam_group_id = lm109.exam_group_id;

The valid dates for the exam token not started are:

Generated Date: April 25, 2019 @ 1:17 PM

START: April 26, 2019 @ 1:17 PM

END: May 26, 2019 @ 1:17 PM

To summarize: the user has taken 4 tests and failed. The user has one attempt left and that attempt is valid from April 26 @ 1:17 PM until May 26 @ 1:17 PM.

To take this test, the user should use the following link to access the test:

https://wwwapps.tc.gc.ca/saf-sec-sur/4/pcocds-sbdccep/to/p1000.aspx?lang=0&token=1F114A22931345E397B661734B1F9AFC

The results from all other tests are no longer available. Test results can not be checked after an exam token is deleted. Exam tokens are deleted if the candidate fails an exam and a new token is re-issued. Exam tokens are also deleted if a brand new token is issued to the candidate (e.g. Fail twice and then authorize a new token for the candidate).

Sending details to PCOCDS client (see main ticket - DOS 716). See attachments. (J. Green)

--

Connect to PCOC (PROD) database (PCOCDSP.WORLD)

Step 1. Find the Operator

select *

from TM110_PCOCTO_OPERATOR

where upper(first_name_nm) like upper('%paul%')

and UPPER(LAST_NAME_NM) like upper('%harry%')

ORDER BY DATE_CREATED_DTE DESC;

--

Found 5 records for this operator:

1670924, 1670923, 1669155, 1669160, 1670869

Step 2. Find their Token and Exam Group ID for all the operator id

Select *

From Lm109_Exam_Grp_Token

WHERE PCOCTO_OPERATOR_CD = '1669160'

ORDER BY DATE_CREATED_DTE DESC;

from the five token below mentioned tokens are provided by the client

(PCOCTO_OPERATOR_CD, TOKEN, EXAM_GROUP_ID)

(1669160, 736EE9EC5F6048AC874200D74ED6DBB0, 1670376)

(1670869, 2A636669501741AF9593592E5D8E86D8, 1672085)

Step 3. Find their exam

SELECT *

FROM TM104_EXAM_GROUP_VERSIONS

WHERE EXAM_GROUP_ID IN ('1670376','1672085')

ORDER BY DATE_LAST_UPDATE_DTE DESC;

Both exams shows are not passed (exam_passed_ind=0). What is the current state of these exams? Are they started, finished, etc?

SELECT *

FROM LM109_EXAM_GRP_TOKEN

WHERE EXAM_GROUP_ID IN ('1670376','1672085')

ORDER BY DATE_LAST_UPDATE_DTE DESC;

Exam 1672085 (2A636669501741AF9593592E5D8E86D8):

Time remaining: 43.05, date_deleted_dte = 2019-04-25 13:17:16, exam result = FAIL

Exam 1670376 (736EE9EC5F6048AC874200D74ED6DBB0):

Time remaining: null, date_deleted_dte = 2019-04-20 00:34:26, exam result = FAIL

User is not getting results for these exams because they are deleted? Did the user finish all questions? If not, this can result in no value being returned. Let's take a closer look at the actual questions answered per exam.

SELECT COUNT(PASS_QUESTION_IND) AS QuestionsCompleted

FROM TM107_XREF_EXAM_GRP_QUESTION t

WHERE T.EXAM_GROUP_ID = '1670376';

No questions answered at all: exam not even started.

SELECT COUNT(PASS_QUESTION_IND) AS QuestionsCompleted

FROM TM107_XREF_EXAM_GRP_QUESTION t

WHERE T.EXAM_GROUP_ID = '1672085';

50 questions answered: 26 incorrect, 24 correct

Let's try to make a call to the PCOCDS web service using SOAP UI.

select *

from TC008_USER

where organization_id = 78

order by USER_ID DESC;

Look for the account that has the comment that specifies that the account is for using the PCOC web service. In this case, ISE8322 / C3i8vj5$.

Need to add header authorization (NTLM) to the web service call. See PCOCDS Web Service project ($/PCOCDS-SBDCCEP/PCOCSERVICE/DEVELOPMENT/PCOCService) to get the username and password. Look in the CPWebSiteEmulator project's web.config. Username is PCOCWCF, Domain and Password should be viewable in the config file.

Send the following XML to the PCOCDS web service:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:pcoc="https://wwwapps.tc.gc.ca/saf-sec-sur/4/PCOCWS-SWCCEP">

<soapenv:Header/>

<soapenv:Body>

<pcoc:GetFinalTestResult>

<pcoc:request>

<pcoc:Username>ISE8322 </pcoc:Username>

<pcoc:Password>C3i8vj5$</pcoc:Password>

<pcoc:Token>2A636669501741AF9593592E5D8E86D8</pcoc:Token>

</pcoc:request>

</pcoc:GetFinalTestResult>

</soapenv:Body>

</soapenv:Envelope>

Result:

GetFinalTestResultResponse xmlns="https://wwwapps.tc.gc.ca/saf-sec-sur/4/PCOCWS-SWCCEP">

</GetFinalTestResultResult>

</GetFinalTestResultResponse>

Let's try the other:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:pcoc="https://wwwapps.tc.gc.ca/saf-sec-sur/4/PCOCWS-SWCCEP">

<soapenv:Header/>

<soapenv:Body>

<pcoc:GetFinalTestResult>

<pcoc:request>

<pcoc:Username>ISE8322 </pcoc:Username>

<pcoc:Password>C3i8vj5$</pcoc:Password>

<pcoc:Token>736EE9EC5F6048AC874200D74ED6DBB0</pcoc:Token>

</pcoc:request>

</pcoc:GetFinalTestResult>

</soapenv:Body>

</soapenv:Envelope>

Code returned: TC0012

What is code TC0012? Let's check the web service code. This is the result of no results being found from the stored procedure spPCOCTO_OP_S07. This "stored procedure" is in a text file in the directory:

$/PCOCDS-SBDCCEP/PCOCSERVICE/DEVELOPMENT/PCOCService/TC.PCOCTO.Services/sp

SQL is as follows:

SELECT U.ORGANIZATION_ID

FROM TC008_USER U

WHERE U.USER_ID = '[0]'

AND U.ORGANIZATION_ID =

(SELECT PO.CURRENT_ORGANIZATION_ID

FROM TM110_PCOCTO_OPERATOR PO

WHERE PO.PCOCTO_OPERATOR_CD =

(SELECT EGT.PCOCTO_OPERATOR_CD

FROM LM109_EXAM_GRP_TOKEN EGT

WHERE EGT.EXAM_GRP_TOKEN_ID = '[1]'))

where [0] = ISE8322 and [1] = 736EE9EC5F6048AC874200D74ED6DBB0;

The CP matches the candidate and exam token. So let's look at the next SQL "stored procedure' spEXAM_S01 (in the same directory):

SELECT EG.EXAM_GROUP_ID, COUNT(Q.QUESTION_ID) AS QuestionCount

FROM LM109_EXAM_GRP_TOKEN EG

INNER JOIN TM107_XREF_EXAM_GRP_QUESTION Q ON EG.EXAM_GROUP_ID = Q.Exam_Group_Id

WHERE EG.EXAM_GRP_TOKEN_ID = '736EE9EC5F6048AC874200D74ED6DBB0'

GROUP BY EG.EXAM_GROUP_ID

Nothing is returned by this query. There are no questions started for this exam. It was deleted before it could be started/finished and was set to a FAIL in the database. Why was this exam deleted?

select *

from lm109_exam_grp_token

where exam_grp_token_id = '736EE9EC5F6048AC874200D74ED6DBB0';

DATE_DELETED_DTE = 2019-04-20 00:34:26

LAST_UPDATE_USER_ID = 0 (admin).

According to the code,

//Any record with a value for date_deleted_dte is considered invalid and should not be considered

//Tokens are valid for 30 days from the effective date

//Effective date is 30 days from created date unless it was a re-issued token because of failure

//In case of failure re-issue, the effective date is 24 hours from the re-issued date and thus

//The Expiry date will be 30 days from that date

//To Summarize - Tokens that are re-issued after a failer have an expiry date of 31 days after the created date

// - as the operator must wait 24 hours to retake the test

This second token was deleted and is not valid. There are no results for it.

Let's send this info to client and see what they want to do with it (see attachments).

Problem: Client needs a list of all PCOC Test Online Questions, Answers, Answer Key, and Test Images.

Description: The Program requires a copy/download of ALL ACTIVE Test questions/Answers. The Program does not have the ability to do this.

Solution: Refer to below to see the SQL used to create the report, along with any other relevant details.

Build a query that gives client domain, objectives, questions, answers, and correct answer for all active PCOCDS exam questions.

--

First - figure out how to get a list of all questions and active questions.

Get list of all questions

select * from tm103_question;

Returns 434 questions - this is the correct number according to Myke.

Get list of current active questions

select * from tm103_question where date_deleted_dte is null;

Returns 164 questions - this is also correct.

--

Get a list of current domains

select * from tm100_domain;

Returns 10 domains - this is correct.

--

Get a list of current objectives sorted by domain

select tm100.domain_id,

tm100.domain_etxt,

tm102.*

FROM

tm102_objective tm102,

tm100_domain tm100

WHERE

tm102.domain_id = tm100.domain_id order BY tm100.domain_id,

tm102.objective_id;

--

When client views a list of questions, he'd like to have the question code to show as:

DOMAIN CODE.OBJECTIVE_CODE.QUESTION_NUMBER.QUESTION_VERSION_NUMBER

Log into PROD (see RIDMS 13232403), go to Syllabus - Questions List, and let's see how the questions codes are written and see if we can replicate.

On site, I filtered the list of questions:

Filter by domain: Set to 02 - Important Boating Technology

Filter by objective: Set to 02.01 - Bow

Question Code is 02.01.01.0 (What is the proper term for the forward-end of a boat?)

02 = TM100_DOMAIN.DOMAIN_SYLLABUS_CD
01 = TM0102_OBJECTIVE.OBJECTIVE_SYLLABUS_CD
01 = TM013_QUESTION.QUESTION_SYLLABUS_CD
0 = TM013_QUESTION.QUESTION_VERSION_CD

--

Following query returns 164 rows (correct).

SELECT

( tm100.domain_syllabus_cd

|| '.'

|| tm102.objective_syllabus_cd

|| '.'

|| tm103.question_syllabus_cd

|| '.'

|| tm103.question_version_cd ) "CD",

tm100.domain_etxt "Domain (ENG)",

tm100.domain_ftxt "Domain (FRA)",

tm102.objective_etxt "Objective (ENG)",

tm102.objective_ftxt "Objective (FRA)",

tm103.question_etxt "Question (ENG)",

tm103.question_ftxt "Question (FRA)",

tm103.correct_answer_txt "Correct Answer",

answers_eng.answers "Answers (ENG)",

answers_fra.answers "Answers (FRA)"

FROM

tm103_question tm103,

tm102_objective tm102,

(

SELECT

question_id,

LISTAGG(item_cd

|| ') '

|| DECODE(possible_answer_item_etxt,NULL,images.image_file_name_txt,possible_answer_item_etxt)

|| ' **** ') WITHIN GROUP(

ORDER BY

item_cd

) AS answers

FROM

tm101_possible_answer_item tm101,

(

SELECT

image_id,

image_file_name_txt

FROM

tm017_image

) images

WHERE

tm101.image_id = images.image_id (+)

GROUP BY

question_id

) answers_eng,

(

SELECT

question_id,

LISTAGG(item_cd

|| ') '

|| DECODE(possible_answer_item_ftxt,NULL,images.image_file_name_txt,possible_answer_item_ftxt)

|| ' **** ') WITHIN GROUP(

ORDER BY

item_cd

) AS answers

FROM

tm101_possible_answer_item tm101,

(

SELECT

image_id,

image_file_name_txt

FROM

tm017_image

) images

WHERE

tm101.image_id = images.image_id (+)

GROUP BY

question_id

) answers_fra,

tm100_domain tm100

WHERE

tm103.question_status_id = 2

AND tm103.question_id = answers_eng.question_id

AND tm103.question_id = answers_fra.question_id

AND tm103.objective_id = tm102.objective_id

AND tm102.domain_id = tm100.domain_id

ORDER BY

CAST(tm100.domain_syllabus_cd AS SMALLINT),

CAST(tm102.objective_syllabus_cd AS SMALLINT),

CAST(tm103.question_syllabus_cd AS SMALLINT),

CAST(tm103.question_version_cd AS SMALLINT);

--

Export query results to Excel.
Will also need to send all of the images as well (\\tcwwwmaster\wwwapps\Saf-Sec-Sur\4\pcocds-sbdccep\pdf\question_images).
Place report and images in the following GroupShare folder: \\tc4s0b\groups\AFCCB\GroupShare\PCOCDS\PCOC Exam Questions

Send details to client (see attachments).

Problem: Course Provider can't access PCOC web service after attempting to change or reset password.

Description:
A Course Provider is unable to connect with the PCOC web service due to a password reset on the course provider's web service account. If the password change is initiated via the PCOC Admin Site, the new password will not work and intervention by DSD-Marine will be needed.

Solution:
To fix the issue (or to reset a Course Provider's password without causing any downtime):

Find the service user account in the TC008_USER table. To do this, you'll need the USER_ID or USER_EMAIL_TXT associated with the account:

select * from TC008_USER where USER_ID = 'KALKOS';
or
select * from TC008_USER where USER_EMAIL_TXT = 'tc@kalkomey.com'

TC008_USER_ID = 525
Update the USER_PASSWORD_TXT to any secure password:

update tc008_user set user_password_txt = 'Jkfds03-fefja' where tc008_user_id = 525;

Now we need to make sure that this password won't expire and that there is no lockout on the account:

update tc008_user set date_expiry_password_dte = null, date_lockout_account_dte = null where tc008_user_id=525;

Send updated password to the course provider and have them confirm that access is restored to the PCOC web service.

ref#TFS 14219

Problem: Server error when student attempts exam

Description:
When attempting to take the exam an error page is shown on the transport Canada site.

When visiting: https://wwwapps.tc.gc.ca/Saf-Sec-Sur/4/pcocds-sbdccep/to/p1000.aspx?lang=0&token=79efa39d-585a-4d12-bf95-c1c3773475a0
The following error is returned in the browser:

Server Error in '/Saf-Sec-Sur/4/pcocds-sbdccep/to' Application.

There is no row at position 0. (IndexOutOfRangeException)

Solution:

Quick Fix :

Create a new token for the candidates and send the test online URL back to the Course Provider so that they may pass this along to their candidate:

An issue that may arise with the solution, when run, would give results that weren't consistent to what we were expecting. The application emulator project that was in repos was compiled using older files. The data was ghosted from a previous version of the emulator aspx…. meaning eventhough you are expressly inserting data in the aspx to reflect the operator that required a new token, the run bypassed the code and stepped through the previous page version.

The fix required unloading the bin, unloading the existing projects, reloading both including any DLLs that were needed. Clean the solution, Rebuild the solution.

Then proceed with the steps below.

1. Delete the old token if it exists. You can search for token using the following query:

PCOCDSP:

SELECT *

from LM109_EXAM_GRP_TOKEN

WHERE EXAM_GRP_token_id = '79efa39d585a4d12bf95c1c3773475a0';

2. Generate a new token for the user.

1. Open the latest copy of the PCOCService solution (located in the $/PCOCDS-SBDCCEP/PCOCSERVICE/ repository). Solution name is "PCOCService.sln".

2. Right-click on the CPWebSiteEmulator project and set as start-up project.

3. Make sure that the project is set to Debug and x86 to allow you to step through the token creation process as needed.

4. Under the CPWebSiteEmulator project, find the WSProduction.aspx page. Right-click and set as starting page.

5. In the btnAuthorize_Click function, set the request.UserName and request.Password values according to the Course Provider for which you're generating a token

6. Update the GetOperator function with the candidate's information (either exists in the database or must be provided by the Course Provider).

7. Run the CPWebSiteEmulator. When the web page loads up, click the "OK - Production" radio button and then click the Authorize button. Once the process is done, a URL will be generated on the web page (e.g. https://wwwapps.tc.gc.ca/Saf-Sec-Sur/4/pcocds-sbdccep/to/p1000.aspx?lang=0&token=68ee8a40-31dd-4616-a474-2995211c0f6f5).

8. Click the link to make sure that the URL is valid. Send this link back to the Course Provider.

3. Send the token to Course Provider.

ref#TFS 14125

More Permanent Fix:

DT dtOp = operatorInfoBL.GetOperatorsFromPCOCTOOperatorForTokenVerify(operatorFailedCheck);

if (dtOp != null)

{

if (dtOp?.Tables[0]?.Rows.Count > 0)

{

// Use the most current record only

DataRow currentTokenInfo = dtOp.Tables[0].Rows[0];

// D. Lafleur

// Set value from web.config for first part of exam url.

currentTokenInfo.SetField(12, ConfigurationManager.AppSettings.Get("PCOCTOUrl"));

// Make sure the status code is defined

if (Enum.IsDefined(typeof(enmResultStatusType), DBGetInt(currentTokenInfo, DBObjects.Feilds.LM109_EXAM_GRP_TOKEN.RESULT_STATUS_TYPE_CD)))

{

SeTokenAndMessages(request, retVal, currentTokenInfo, organizationId);

}

else

{

// This really shouldn't happen, but add it just in case data is missing

// Log that an invalid code exists in the database

retVal.ServiceMessages.Add(new ServiceMessageObj("Invalid Status", "There is an invalid or missing code for the current token"));

retVal.TestUrl = string.Empty;

return retVal;

}

}

else

{

// We do not have any tokens at this point so create a new one

ProcessSuccessfullAuthorization(request, retVal, organizationId);

}

}

else

{

// We do not have any tokens at this point so create a new one

ProcessSuccessfullAuthorization(request, retVal, organizationId);

}

ref#TFS 12606

Problem: Student requested to change the test language for final PCOC test

Description:
The PCOC final test token issued to the student was for French. The student requires an English Test. Course provider requested to change the token for the student to take the English test.

Solution:
Search the token in the database and change language to English. The detail fix is described below:

Connect to PCOC (PROD) database (PCOCDSP.WORLD)

Step 1. Find the Operator

select *
from TM110_PCOCTO_OPERATOR
where upper(first_name_nm) like upper('%Claire%')
and UPPER(LAST_NAME_NM) like upper('%Leblanc%')
ORDER BY DATE_CREATED_DTE DESC;

Step 2. Find their Token

Select *

From Lm109_Exam_Grp_Token
WHERE PCOCTO_OPERATOR_CD = '1660093'
ORDER BY DATE_CREATED_DTE DESC;

Step 3. Find their Exam

SELECT *

FROM TM104_EXAM_GROUP_VERSIONS

WHERE EXAM_GROUP_ID = '1661308'

ORDER BY DATE_LAST_UPDATE_DTE DESC;

Step 4. Update their Language

update TM104_EXAM_GROUP_VERSIONS

set language_cd = 1

where EXAM_GROUP_ID = 1661308;

Now that the token's language has been updated to English, contact the Course Provider (cc PCOCDS team - DL OTT PCOC Team) to advise that token should now allow user to take the test in English.

Double check that the URL for the test is acessible via the following URL:

https://wwwapps.tc.gc.ca/saf-sec-sur/4/pcocds-sbdccep/to/p1000.aspx?lang=LANG&token=TOKEN

where

LANG=0 (for English), LANG=1 for French
TOKEN = exam_grp_token_id from Lm109_Exam_Grp_Token table.

If sending URL back to client, be sure to set lang and token in URL appropriately and verify that URL works before sending.

Browser not supported