<!--- Is the form being submitted? --->
<CFSET WasFormSubmitted = IsDefined("FORM.RatingID")>

<!--- Insert film into database when form is submitted --->
<CFIF WasFormSubmitted>
  <CFSTOREDPROC 
    PROCEDURE="InsertFilm" 
    DATASOURCE="owsOra"
    RETURNCODE="No">

    <!--- Provide form values to the procedure's input parameters --->  
    <CFPROCPARAM 
      TYPE="In"
      DBVARNAME="pMovieTitle" 
      MAXLENGTH="50" 
      CFSQLTYPE="CF_SQL_VARCHAR"
      VALUE="#Form.MovieTitle#">
    <CFPROCPARAM 
      TYPE="In"
      DBVARNAME="pPitchText" 
      MAXLENGTH="100" 
      CFSQLTYPE="CF_SQL_VARCHAR"
      VALUE="#Form.PitchText#">
    <CFPROCPARAM 
      TYPE="In"
      DBVARNAME="pAmountBudgeted" 
      MAXLENGTH="100" 
      CFSQLTYPE="CF_SQL_MONEY"
      VALUE="#Form.AmountBudgeted#"
      NULL="#YesNoFormat(FORM.AmountBudgeted EQ '')#">
    <CFPROCPARAM 
      TYPE="In"
      DBVARNAME="pRatingID" 
      MAXLENGTH="100" 
      CFSQLTYPE="CF_SQL_INTEGER"
      VALUE="#Form.RatingID#">
    <CFPROCPARAM 
      TYPE="In"
      DBVARNAME="pSummary" 
      CFSQLTYPE="CF_SQL_LONGVARCHAR"
      VALUE="#Form.Summary#">

    <!--- Capture @NewFilmID output parameter --->
    <!--- Value will be available in CFML variable named #InsertedFilmID# --->
    <CFPROCPARAM 
      TYPE="Out"
      DBVARNAME="pInsertStatus" 
      CFSQLTYPE="CF_SQL_INTEGER"
      VARIABLE="InsertStatus">

    <CFPROCPARAM 
      TYPE="Out"
      DBVARNAME="pNewFilmID" 
      CFSQLTYPE="CF_SQL_INTEGER"
      VARIABLE="InsertedFilmID">

  </CFSTOREDPROC>
  
</CFIF>

<!--- Get list of ratings from database --->
<CFSTOREDPROC PROCEDURE="owsWeb.FetchRatingsList" DATASOURCE="owsOra">
  <!--- This output parameter gives ColdFusion a pointer to reference cursor --->
  <CFPROCPARAM 
    TYPE="Out" 
    CFSQLTYPE="CF_SQL_REFCURSOR" 
    VARIABLE="IgnoreThis" 
    MAXROWS="-1">
  <!--- Make the data from reference cursor available as CFML query object --->  
  <CFPROCRESULT NAME="GetRatings">
</CFSTOREDPROC>


<HTML>
<HEAD><TITLE>Film Entry Form</TITLE></HEAD>
<BODY>
<H2>Film Entry Form</H2>

<!--- Data entry form --->
<CFFORM ACTION="#CGI.SCRIPT_NAME#" METHOD="POST" PRESERVEDATA="Yes">

  <!--- Text entry field for film title --->
  <P><B>Title for New Film:</B><BR>
  <CFINPUT NAME="MovieTitle" SIZE="50" MAXLENGTH="50"
    REQUIRED="Yes" MESSAGE="Please don't leave the film's title blank."><BR>

  <!--- Text entry field for pitch text --->
  <P><B>Short Description / One-Liner:</B><BR>
  <CFINPUT NAME="PitchText" SIZE="50" MAXLENGTH="100"
    REQUIRED="Yes" MESSAGE="Please don't leave the one-liner blank."><BR>
  
  <!--- Text entry field for expense description --->
  <P><B>New Film Budget:</B><BR>
  <CFINPUT NAME="AmountBudgeted" SIZE="15" 
    REQUIRED="No" MESSAGE="Only numbers may be provided for the film's budget."
    VALIDATE="float"> (leave blank if unknown)<BR>

  <!--- Drop-down list of ratings --->  
  <P><B>Rating:</B><BR>
  <CFSELECT
    NAME="RatingID"  
    QUERY="GetRatings"
    VALUE="RatingID"
    DISPLAY="Rating"/>

  <!--- Text areas for summary --->
  <P><B>Summary:</B><BR>
  <TEXTAREA NAME="Summary" COLS="40" ROWS="3" WRAP="soft"></TEXTAREA>  
    
  <!--- Submit button for form --->
  <P><INPUT TYPE="Submit" VALUE="Submit New Film">
</CFFORM>  


<!--- If we executed the stored procedure --->
<CFIF WasFormSubmitted>
  <!--- Display message based on status code reported by stored procedure --->
  <CFSWITCH EXPRESSION="#InsertStatus#">
    <!--- If the stored procedure returned a "success" status --->
    <CFCASE VALUE="1">
      <CFOUTPUT>
        <P>Film "#Form.MovieTitle#" was inserted as Film ID #InsertedFilmID#.<BR>
      </CFOUTPUT>  
    </CFCASE>
    <!--- If the status code was -1 --->
    <CFCASE VALUE="-1">
      <CFOUTPUT>
        <P>Film "#Form.MovieTitle#" already exists in the database.<BR>
      </CFOUTPUT>  
    </CFCASE>
    <!--- If the status code was -2 --->
    <CFCASE VALUE="-2">
      <P>An invalid rating was provided.<BR>
    </CFCASE>
    <!--- If any other status code was returned --->
    <CFDEFAULTCASE>
      <P>The procedure returned an unknown status code.<CFOUTPUT>#InsertStatus# </CFOUTPUT><BR>
    </CFDEFAULTCASE>
  </CFSWITCH>
</CFIF>
  
</BODY>
</HTML>
