**FREE
ctl-opt option (*srcstmt : *nodebugio : *nounref);
ctl-opt debug (*input);
ctl-opt dftactgrp (*no);
ctl-opt Pgminfo(*pcml:*module:*dclcase);
//- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
//  Program : MlrEmpInq
//  Author  : Mike Larsen
//  Date Written: 07/28/2026
//  Purpose : This program returns information from the sample employee
//            table. It allows for the consumer of the Api to use
//            pagination.
//
//====================================================================*
//   Date    Programmer  Description                                  *
//--------------------------------------------------------------------*
// 07/28/26  M.Larsen    Original code.                               *
//                                                                    *
//- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - *

//- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

// - - - - - - -
// Workfields

dcl-s employeeInfo_clob sqltype(clob:16000000);

// Prototypes (entry parameters)

dcl-pr MlrEmpInq       ExtPgm;
       offsetRows      int(10);
       fetchRows       int(10);
       outEmployeeInfo like(employeeInfo_clob);
end-pr;

// Main procedure interface

dcl-pi MlrEmpInq;
       offsetRows      int(10);
       fetchRows       int(10);
       outEmployeeInfo like(employeeInfo_clob);
end-pi;

//- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

setDefaults();
getEmployeeInformation();

outEmployeeInfo = %subst(employeeInfo_clob_data:1:employeeInfo_clob_len);

*Inlr = *On;
Return;

//--------------------------------------------------------
// setDefaults subprocedure
//--------------------------------------------------------

dcl-proc setDefaults;

  If offsetRows < 0;
     offsetRows = 0;
  Endif;

  If fetchRows <= 0 or fetchRows > 500;
     fetchRows = 500;
  Endif;

end-proc setDefaults;

//--------------------------------------------------------
// getEmployeeInformation subprocedure
//--------------------------------------------------------

dcl-proc getEmployeeInformation;

  Exec sql

  With CTE_TotalRows as
   (
    Select count(*) as TotalCount
    from sample.employee
   ),

   CTE_Page as
    (
     Select
      EmpNo, FirstNme, MidInit, LastName, WorkDept, PhoneNo,
      HireDate, Job, EdLevel, Sex, BirthDate, Salary, Bonus, Comm

     from sample.employee
     order by EmpNo

     Offset :offsetRows rows
     Fetch first :fetchRows rows only
    ),

   CTE_PageJson as
    (
     Select
      count(*) as PageCount,

      Coalesce
       (
        json_arrayagg
         (
            json_object
            (
              'EmployeeNumber'  value trim(EmpNo),
              'Firstname'       value trim(FirstNme),
              'MiddleInitial'   value trim(Midinit),
              'LastName'        value trim(Lastname),
              'Department'      value trim(Workdept),
              'Phone'           value trim(Phoneno),
              'HireDate'        value Hiredate,
              'Job'             value trim(Job),
              'EducationLevel'  value trim(Edlevel),
              'Sex'             value trim(Sex),
              'BirthDate'       value Birthdate,
              'Salary'          value Salary,
              'Bonus'           value Bonus,
              'Commission'      value Comm

            ) -- end json_object

            order by EmpNo

         ), -- end json_arrayagg

         json_array()

         -- json_array() is associated to the 'Coalesce' function. It is if there are
         -- no rows to be returned from json_arrayagg.
         --
         -- If we didn't do this, we'd return the following Json when there were no rows to return.
         --
         -- {
         --   "Employees": null
         -- }
         --
         -- Instead, we will return an empty array that would look like this.
         --
         -- {
         --   "Employees": []
         -- }

      ) as Employees

     from CTE_Page
    )

    Select
     json_object
      (
       'totalCount' value T.TotalCount,
       'pageCount'  value P.PageCount,
       'offset'     value :offsetRows,
       'limit'      value :fetchRows,

       'hasMore'    value

                     Case
                     When (:offsetRows + P.PageCount) < T.TotalCount
                     Then 'y'
                     Else 'n'
                     End,

       'employees' value P.Employees format json
      )

      into :employeeInfo_clob

      from CTE_TotalRows T,
           CTE_PageJson P;

end-proc getEmployeeInformation;

//- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 