-- Master and student compiled forms (imported objects)

    package Compiled_Forms is
          exec sql begin declare section;
               masterfrm, studentfrm: Integer;
          exec sql end declare section;
          pragma import_object( masterfrm );
          pragma import_object( studentfrm );
    end Compiled_Forms;

exec sql include sqlca;

exec sql declare student table   -- Graduate student table
      (sname char(25),            -- Name
       sage integer1,             -- Age
       sbdate char(25),           -- Birth date
       sgpa float4,               -- Grade point average
       idno integer,              -- Unique student number
       scomment varchar(200),     -- General comments
       sadvisor char(25));        -- Advisor's name

exec sql declare professor table  -- Professor table
      (pname char(25),            -- Professor's name
       pdept char(10));           -- Department
        with Compiled_Forms;      use Compiled_Forms;
        with Text_Io;             use Text_Io;
        with Integer_Text_Io;     use Integer_Text_Io;
-- Procedure: Prof_Student
-- Purpose:   Main body of "'Professor Student" Master-Detail
--            application.

procedure Prof_Student is
    exec sql begin declare section;
      -- Graduate student record maps to "student" database table
        type Student_Rec is
          record
              sname:      String(1..25);
              sage:       Short_Short_Integer;
              sbdate:     String(1..25);
              sgpa:       Float;
              sidno:      Integer;
              scomment:   String(1..200);
              sadvisor:   String(1..25);
          end record;
        grad: Student_Rec;
    exec sql end declare section;

    --
    -- Procedure: Load_Students
    -- Purpose: Given an advisor name, load into the "studenttbl"
    --          table field all the graduate students who report
    --          to the professor with that name.
    -- Parameters: advisor - User-specified professor name.
    --         Uses the global student record "grad."
    --
    procedure Load_Students( adv : in String ) is
        exec sql begin declare section;
             advisor : String(1..25) := adv;
        exec sql end declare section;

        exec sql declare studentcsr cursor for
          select sname, sage, sbdate, sgpa,
                sidno, scomment, sadvisor
          from student
          where sadvisor = :advisor;

    begin
        --
        -- Clear previous contents of table field. Load the table
        -- field from the database table based on the advisor
        -- name. Columns "sname" and "sage" will be displayed,
        -- and all others will be hidden.
        --
        exec frs message 'Retrieving Student Information . . .';
        exec frs clear field studenttbl;
        exec frs redisplay; -- Refresh for query
        exec sql whenever sqlerror goto Load_End;
        exec sql whenever not found goto Load_End;

        exec sql open studentcsr;

        --
        -- Before we start the loop, we know that the OPEN
        -- was successful and that NOT FOUND was not set.
        --
        while (sqlca.sqlcode = 0) loop
            exec sql fetch studentcsr into :grad;

            exec frs loadtable masterfrm studenttbl
               (sname = :grad.sname,
                sage = :grad.sage,
                sbdate = :grad.sbdate,
                sgpa = :grad.sgpa,
                sidno = :grad.sidno,
                scomment = :grad.scomment,
                sadvisor = :grad.sadvisor);
        end loop;

<<Load_End>>         -- Clean up on an error, and close cursors
        exec sql whenever not found continue;
        exec sql whenever sqlerror continue;
        exec sql close studentcsr;

    end Load_Students;

    
    --
    -- Function: Student_Info_Changed
    -- Purpose: Allow the user to zoom in on the details of a
    --        selected student. Some of the data can be
    --        updated by the user. If any updates are made,
    --        incorporate them into the database table.
    --        The procedure returns TRUE if any changes are
    --        made.

    -- Parameters:
    --        None
    -- Returns:
    --       TRUE/FALSE - Changes were made to the database.
    --       Sets the global "grad" record with the new data.
    --

    function Student_Info_Changed return Boolean is
        exec sql begin declare section;
            changed: Integer;         -- Changes made to the form?
            valid_advisor: Integer;  -- Is the advisor name valid?
        exec sql end declare section;

    begin
        -- Local error handler just prints error and continues
        exec sql whenever sqlerror call sqlprint;
        exec sql whenever not found continue;

        -- Display the detailed student information
        exec frs display studentfrm fill;
        exec frs initialize
           (sname = :grad.sname,
            sage = :grad.sage,
            sbdate = :grad.sbdate,
            sgpa = :grad.sgpa,
            sidno = :grad.sidno,
            scomment = :grad.scomment,
            sadvisor = :grad.sadvisor);

        exec frs activate menuitem 'Write';
        exec frs begin;

            --
            -- If changes were made, then update the
            -- database table. Only bother with the
            -- fields that are not read-only.
            --
            exec frs inquire_frs form (:changed = change);

            if (changed = 1) then
             exec frs validate;
             exec frs message 'Writing changes to database. . .';

                exec frs getform
                    (:grad.sgpa = sgpa,
                     :grad.scomment = scomment,
                     :grad.sadvisor = sadvisor);

                -- Enforce integrity of professor name
                valid_advisor := 0;
                exec sql select 1 into :valid_advisor
                    from professor
                    where pname = :grad.sadvisor;

                 if (valid_advisor = 0) then
                    exec frs message 'Not a valid advisor name';
                    exec frs sleep 2;
                    exec frs resume field sadvisor;
                 else
                    exec sql update student set
                        sgpa = :grad.sgpa,
                        scomment = :grad.scomment,
                        sadvisor = :grad.sadvisor
                        where sidno = :grad.sidno;
                 end if;

            end if;
            exec frs breakdisplay;
        exec frs end; -- 'Write'

        exec frs activate menuitem 'Quit';
        exec frs begin;
            -- Quit without submitting changes
            changed := 0;
            exec frs breakdisplay;
        exec frs end; -- 'Quit'
        exec frs finalize;

        return (changed = 1);

    end Student_Info_Changed;

    --
    -- Procedure: Master
    -- Purpose:   Drive the application, by running "masterfrm"
    --         and allowing the user to "zoom" in on a selected
    -- student. Parameters: None - Uses the global student "grad"
    -- record.

    procedure Master is
        exec sql begin declare section;
            -- Professor record maps to "professor" database table
            type Prof_Rec is
                  record
                      pname: String(1..25);
                      pdept: String(1..10);
                  end record;
            prof: Prof_Rec;

            -- Useful forms runtime information
            lastrow,           -- Lastrow in table field
            istable: Integer;  -- Is a table field?

            -- Utility buffers
            msgbuf:      String(1..100);    -- Message buffer
            respbuf:     String(1..1);      -- Response buffer
            old_advisor: String(1..25); -- Old advisor before ZOOM
      exec sql end declare section;

begin -- Master
    --
    -- Initialize "studenttbl" with a data set in READ mode.
    -- Declare hidden columns for all the extra fields that
    -- the program will display when more information is
    -- requested about a student. Columns "sname" and "sage"
    -- are displayed. All other columns are hidden, to be
    -- used in the student information form.
    --
    exec frs inittable masterfrm studenttbl read
          (sbdate = char(25),
           sgpa = float4,
           sidno = integer,
           scomment = char(200),
           sadvisor = char(20));

    -- Drive the application, by running "masterfrm" and
    -- allowing the user to "zoom" in on a selected student.
    exec frs display masterfrm update;

    exec frs initialize;
    exec frs begin;
          exec frs message 'Enter an Advisor name . . .';
          exec frs sleep 2;
    exec frs end;

    exec frs activate menuitem 'Students', field 'pname';
    exec frs begin;
          -- Load the students of the specified professor
          exec frs getform (:prof.pname = pname);

          -- If no professor name is given, then resume
          if (prof.pname(1) = ' ') then
               exec frs resume field pname;
          end if;

          --
          -- Verify that the professor exists. If not,
          -- print a message and continue. Assume that
          -- each professor has exactly one department.
          --
          exec sql whenever sqlerror call sqlprint;
          exec sql whenever not found continue;

          prof.pdept := (1..10 => ' ');
          exec sql select pdept
              into :prof.pdept
              from professor
              where pname = :prof.pname;

          -- If no professor, report error
          if (prof.pdept(1) = ' ') then
              msgbuf := (1..100 => ' ');
              msgbuf(1..59) :=
                 "No professor with name '" &
                  prof.pname & "' [RETURN]";
              exec frs prompt noecho (:msgbuf, :respbuf);
              exec frs clear field all;
              exec frs resume field pname;
          end if;

          -- Fill the department field and load students
          exec frs putform (pdept = :prof.pdept);
          Load_Students( prof.pname );

          exec frs resume field studenttbl;
      exec frs end;          -- 'Students'
      exec frs activate menuitem 'Zoom';
      exec frs begin;
         --
         -- Confirm that user is in "studenttbl" and that
         -- the table field is not empty. Collect data from
         -- the row and zoom for browsing and updating.
         --
         exec frs inquire_frs field masterfrm
             (:istable = table);
         if (istable = 0) then
               exec frs prompt noecho
             ('Select from the student table [RETURN]', :respbuf);
               exec frs resume field studenttbl;
         end if;

         exec frs inquire_frs table masterfrm
               (:lastrow = lastrow);
         if (lastrow = 0) then
              exec frs prompt noecho
                  ('There are no students [RETURN]', :respbuf);
              exec frs resume field pname;
         end if;

          -- Collect all data on student into graduate record
          exec frs getrow masterfrm studenttbl
             (:grad.sname = sname,
              :grad.sage = sage,
              :grad.sbdate = sbdate,
              :grad.sgpa = sgpa,
              :grad.sidno = sidno,
              :grad.scomment = scomment,
              :grad.sadvisor = sadvisor);

          --
          -- Display "studentfrm," and, if any changes were made,
          -- make the updates to the local table field row.
          -- Only make updates to the columns corresponding to
          -- writable fields in "studentfrm." If the student
          -- changed advisors, then delete the row from the
          -- display.
          --
          old_advisor := grad.sadvisor;
          if (Student_Info_Changed) then
              if (old_advisor <= grad.sadvisor) then
                   exec frs deleterow masterfrm studenttbl;
              else
                  exec frs putrow masterfrm studenttbl
                      (sgpa = :grad.sgpa,
                       scomment = :grad.scomment,
                       sadvisor = :grad.sadvisor);
              end if;
          end if;

      exec frs end;                           -- 'Zoom' ;

      exec frs activate menuitem 'Exit';
      exec frs begin;
           exec frs breakdisplay;
      exec frs end;                          -- 'Exit' ;

      exec frs finalize;

   end Master;

begin                              -- Prof_Student
    -- Start up Ingres and the FORMS system
    exec frs forms;

    exec sql whenever sqlerror stop;
    exec frs message 'Initializing Student Administrator . . .';
    exec sql connect personnel;

    exec frs addform :masterfrm;
    exec frs addform :studentfrm;

    Master;

    exec frs clear screen;
    exec frs endforms;
    exec sql disconnect;

end Prof_Student;