| Procedure: Prof_Student
| Purpose: Main body of "Professor Student" Master-Detail application.
}

program Prof_Student( input, output );

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}
     sidno        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}

exec sql begin declare section;
type
    Short_Short_Integer = [byte] -128..127;

    String1   = packed array[1..1]   of Char;
    String10  = packed array[1..10]  of Char;
    String25  = packed array[1..25]  of Char;
    String100 = packed array[1..100] of Char;
    String200 = packed array[1..200] of Char;

    {Graduate student record maps to "student" database table }
    Student_Rec = record
        sname: String25;
        sage: Short_Short_Integer;
        sbdate: String25;
        sgpa: Real;
        sidno: Integer;
        scomment: String200;
        sadvisor: String25;
    end;

var
    grad: Student_Rec;
    {Master and student compiled forms (imported objects)}
    masterfrm, studentfrm: [external] Integer;
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.
|                 Columns "sname" and "sage" will be displayed, and
|                 all other columns will be hidden.
| Parameters:     advisor - User specified professor name.
|                 Uses the global student record "grad".
}

procedure Load_Students( var adv : String25 );
    label
        Load_End;
    exec sql begin declare section;
    var
        advisor : String25;
    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             {Load_Students}
    advisor := adv;

    {
    | 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) do
    begin
        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;

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 into the details of a
    | selected student. Some of the data can be
    | updated by the user. If any updates were made,
    | then reflect these back into the database table.
    | The procedure returns TRUE if any changes were 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 : Boolean;
    exec frs label;
    exec sql begin declare section;
    var
        changed: Integer; {Changes made to the form?}
        valid_advisor: Integer; {Is the advisor name valid?}
    exec sql end declare section;

begin             {Student_Info_Changed}
    {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
        begin
            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
            begin
                exec frs message
                    'Not a valid advisor name';
                exec frs sleep 2;
                exec frs resume field sadvisor;
            end else
            begin
                exec sql update student set
                    sgpa = :grad.sgpa,
                    scomment = :grad.scomment,
                    sadvisor = :grad.sadvisor
                    where sidno = :grad.sidno;
            end;
        end;
        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;

    Student_Info_Changed := (changed = 1);
exec frs end; {Student_Info_Changed}

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

procedure Master;
    exec frs label;
    exec sql begin declare section;
    type
        {Professor record maps to "professor" database table }
         Prof_Rec = record
            pname: String25;
            pdept: String10;
         end;
    var
             prof: Prof_Rec;

           {Useful forms runtime information }
            lastrow,         {Lastrow in table field }
            istable: Integer;     {Is a table field? }
            {Utility buffers }
            msgbuf: String100;         {Message buffer }
            respbuf: String1;          {Response buffer }
            old_advisor: String25;     {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" into 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, resume }
        if (prof.pname[1] = ' ') then
            exec frs resume field pname;

        {
        | Verify that the professor exists. If not print
        | 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 := ' ';
        exec sql select pdept
            into :prof.pdept
            from professor
            where pname = :prof.pname;

        {If no professor, report error}
        if (prof.pdept[1] = ' ') then
        begin
            msgbuf := 'No professor with name ''' +
                prof.pname + ''' [return]';
            exec frs prompt noecho (:msgbuf, :respbuf);
            exec frs clear field all;
            exec frs resume field pname;
        end;

        {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
        begin
            exec frs prompt noecho
                ('Select from the student table [return]',
                     :respbuf);
            exec frs resume field studenttbl;
        end;

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

        {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
        begin
            if (old_advisor <> grad.sadvisor) then
            begin
                exec frs deleterow
                    masterfrm studenttbl;
            end else
            begin
                exec frs putrow masterfrm studenttbl
                    (sgpa = :grad.sgpa,
                     scomment = :grad.scomment,
                     sadvisor = :grad.sadvisor);
            end;
        end;
    exec frs end;         {"Zoom"}
    exec frs activate menuitem 'Exit';
    exec frs begin;
        exec frs breakdisplay;
    exec frs end;         {"Exit"}
    exec frs finalize;
exec frs 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}