Report Requirement
The processing required for many reports or file exports (csv) I've been creating have many data collection calls. Sometimes multiple tables can be joined to reduce the number of unique calls but often the there are times where data is not found or the returned data needs some analysis. If the design also calls for different reporting or actions based on the analysis it forces you to make many individual calls.
In my example we are using one large SQL to query to get a initial set of Employee ID's. With each employee ID I as asked to do about 10 other steps of data collection and analysis each step could lead to one of the following results:
- Skip to the next Employee ID
- Write the report row and skip to next Employee ID
- Continue Processing
- Insert Data
My initial build was using a while loop through the employees with if conditions inside however it became apparent that my nested IF conditions were getting out of hand.
My Solution
The solution I found to create very clean and readable code that also matched the design documentation was to use an EVALUATE TRUE statement.As you can see the &report record is a derived record that is used to accumulate the data as we go. I used local functions to do the mini calls if I the requirement is to skip an employee when a record is not found the function simply returns FALSE. This will stop the processing of any more WHEN statements in the EVALUATE. In some cases I return a whole record if the data is needed for future calls and in this case I do my check for a null record returned within the WHEN statement.
Main Loop Processing
/* Data & Records */
Local Record &admApplProg, &extAcadDataA, &extAcadDataB, &mcmFaErlyAvg;
Local Record &report;
/* Processing Variables */
Local boolean &resumeEmplid;
&admApplProg = CreateRecord(Record.ADM_APPL_PROG);
&Sql = CreateSQL(SQL.SQL1, &admApplProg);
While &Sql.Fetch(&admApplProg)
&resumeEmplid = True;
&report = CreateRecord(Record.MCM_AD_FFA_RPT);
&report.EMPLID.Value = &admApplProg.EMPLID.Value;
&report.ADM_APPL_NBR.Value = &admApplProg.ADM_APPL_NBR.Value;
&report.ACAD_CAREER.Value = &admApplProg.ACAD_CAREER.Value;
&report.ACAD_PROG.Value = &admApplProg.ACAD_PROG.Value;
&report.PROG_ACTION.Value = &admApplProg.PROG_ACTION.Value;
Evaluate True
When &resumeEmplid
/* Drop if EXT_ORG_ID not found */
&extAcadDataA = getMaxExtAcadData(&report, &admApplProg.EMPLID.Value);
If None(&extAcadDataA) Then
&resumeEmplid = False;
End-If;
When &resumeEmplid
/* Valid Canadian Citizen */
&resumeEmplid = isValidCndApplicant(&REPORT, &extAcadDataA);
When &resumeEmplid
/* retrieve an Education Source */
&resumeEmplid = hasEducSrc(&report);
When &resumeEmplid
/* Read Adm_APPL_PLAN */
&resumeEmplid = hasAcadPlan(&report, &admApplProg);
When &resumeEmplid
/* Retrieve the students averages */
&resumeEmplid = hasMcmAvg(&report, &admApplProg);
When &resumeEmplid
/* read Early Financial Aid value*/
&mcmFaErlyAvg = getErlyAvg(&report, &admApplProg);
If None(&mcmFaErlyAvg) Then
&resumeEmplid = False;
End-If;
When &resumeEmplid
/* 9. Define the FA Year*/
&resumeEmplid = hasFaYear(&report, &admApplProg);
When &resumeEmplid
/* Write the report row */
writeRecordToFile(&report);
End-Evaluate;
End-While;
Functions
Here you'll see some functions that are returning records and some that return the Boolean of successful data retrieval or not. I have not include all the functions just some of the examples. One thing you will notice is that I'm passing the main &report record into the functions. This is because PeopleCode uses pass by reference so you can set values in the local fuction &REPORT will be retained after your function returns to the mainline.
If you are curious about my exists check on function hasFaOffer I have previously blogged this example.
/* Identify last school attended */
Function getMaxExtAcadData(&REPORT) Returns Record;
Local SQL &SqlExtAcad;
Local Record &extAcadData;
Local String &saveOrgId;
&extAcadData = CreateRecord(Record.EXT_ACAD_DATA);
&SqlExtAcad = CreateSQL(SQL.MAX_EXT, &extAcadData, &RECORD.EMPLID.value);
While &SqlExtAcad.Fetch(&extAcadData)
If None(&saveOrgId) Then
&saveOrgId = &extAcadData.EXT_ORG_ID.Value;
Else
/* if more then one EXT_ORG_ID is found report and skip process */
If &saveOrgId <> &extAcadData.EXT_ORG_ID.Value Then
&REPORT.REASON.value = "Verify EXT_ORG_ID";
Return Null;
End-If;
End-If;
End-While;
If None(&extAcadData.EXT_ORG_ID.Value) Then
Return Null;
End-If;
Return &extAcadData;
End-Function;
Function getErlyAvg(&REPORT, &AAP) Returns Record;
Local string &qry, &result;
Local Record &record = CreateRecord(Record.MCM_FA_ERLY_AVG);
&qry = "%SelectAll(:1 A) A WHERE ...";
SQLExec(&qry, &record, ... , &record);
If All(&record.INSTITUTION.Value) Then
&REPORT.MCM_FA_AWARD_VALUE.value = &record.MCM_FA_AWARD_VALUE.Value;
&REPORT.MCM_FA_MIN_SCH_AVG.value = &record.MCM_FA_MIN_SCH_AVG.Value;
&REPORT.MCM_FA_MAX_SCH_AVG.value = &record.MCM_FA_MAX_SCH_AVG.Value;
Return &record;
Else
Return Null;
End-If;
End-Function;
Function hasFaYear(&REPORT, &AAP) Returns boolean;
Local string &result;
SQLExec(SQL.MCM_0784_SQL3, &AAP.EMPLID.value, ... , &result);
&REPORT.AID_YEAR.value = &result;
Return True;
End-Function;
Function hasFaOffer(&REPORT, &AAP) Returns boolean;
Local string &exists;
Local number &result;
SQLExec(SQL.MCM_0784_SQL4, &AAP.EMPLID.value, ... , &exists, &result);
If All(&exists) Then
&REPORT.EARLY_OFFER_TOTAL.value = &result;
Return True;
Else
Return False;
End-If;
End-Function;
Proof of Concept
Here is a small little code snippet I used as a proof of concept
Function getTrue(&array) Returns boolean;
MessageBox(0, "", 0, 0, " getTrue Function ");
&array.Push("True");
Return True;
End-Function;
Function getFalse(&array) Returns boolean;
MessageBox(0, "", 0, 0, " getFalse Function ");
&array.Push("False");
Return False;
End-Function;
Local boolean &resume = True;
Local array of string &evalCount;
&evalCount = CreateArrayRept("", 0);
Evaluate True
When &resume
MessageBox(0, "", 0, 0, " Evaluate 1 ");
&evalCount.Push("1");
When &resume
MessageBox(0, "", 0, 0, " Evaluate 2 ");
&evalCount.Push("2");
&resume = getTrue(&evalCount);
When &resume
MessageBox(0, "", 0, 0, " Evaluate 3 ");
&evalCount.Push("3");
When &resume
MessageBox(0, "", 0, 0, " Evaluate 4 ");
&evalCount.Push("4");
&resume = getFalse(&evalCount);
When &resume
MessageBox(0, "", 0, 0, " Evaluate 5 ");
&evalCount.Push("5");
When &resume
MessageBox(0, "", 0, 0, " Evaluate 6 ");
&evalCount.Push("6");
&resume = getTrue(&evalCount);
End-Evaluate;
MessageBox(0, "", 0, 0, " Array %1 ", &evalCount.Join(", "));
