<HTML>
<HEAD><TITLE>Orange Whip Studios Actors Stable</TITLE></HEAD>
<BODY>
<H2>Orange Whip Studios Actors Stable</H2>

<!--- If form is being submitted, commit changes to the database --->
<CFIF CGI.REQUEST_METHOD is "Post">
  <!--- The RowStatus.Action array contains a value for each change --->
  <CFSET NumChanges = ArrayLen(FORM.ActorGrid.RowStatus.Action)>

  <!--- For each change made to the grid... --->
  <CFLOOP FROM="1" TO="#NumChanges#" INDEX="i">
    <!--- What type of change was made? --->
    <CFSET ThisAction = FORM.ActorGrid.RowStatus.Action[i]>

    <!--- Depending on the type of change (update, insert, or delete) --->
    <CFSWITCH EXPRESSION="#ThisAction#">
      <!--- Handle updates (if existing values were edited in grid) --->
      <CFCASE VALUE="U">
        <CFQUERY DATASOURCE="ows">
          UPDATE Actors SET
            NameFirst = '#FORM.ActorGrid.NameFirst[i]#',
            NameLast = '#FORM.ActorGrid.NameLast[i]#',
            Age = #FORM.ActorGrid.Age[i]#,
            IsTotalBabe = #FORM.ActorGrid.IsTotalBabe[i]#,
            Gender = '#FORM.ActorGrid.Gender[i]#'
          WHERE
            ActorID = #FORM.ActorGrid.original.ActorID[i]#
        </CFQUERY>
      </CFCASE>
      <!--- Handle inserts (if record was added to the grid) --->
      <CFCASE VALUE="I">
        <!--- The boss says we're only hiring beautiful people from now on --->
        <CFIF FORM.ActorGrid.IsTotalBabe[i] EQ 1>
          <CFQUERY DATASOURCE="ows">
            INSERT INTO Actors (
              NameFirst, 
              NameLast, 
              NameFirstReal, 
              NameLastReal, 
              Age, 
              IsTotalBabe, 
              Gender
            ) VALUES (
              '#FORM.ActorGrid.NameFirst[i]#', 
              '#FORM.ActorGrid.NameLast[i]#',
              '#FORM.ActorGrid.NameFirst[i]#', 
              '#FORM.ActorGrid.NameLast[i]#',
              #FORM.ActorGrid.Age[i]#,
              #FORM.ActorGrid.IsTotalBabe[i]#,
              '#FORM.ActorGrid.Gender[i]#'
            )
          </CFQUERY>
        <CFELSE>
          <CFOUTPUT>
            <P>Sorry, #FORM.ActorGrid.NameFirst[i]# #FORM.ActorGrid.NameLast[i]# 
            cannot be added because it's company policy to only hire Hotties.<BR>
          </CFOUTPUT>  
        </CFIF>
      </CFCASE>
      <!--- Handle deletes (if existing row was deleted from grid) --->
      <CFCASE VALUE="D">
        <!--- Find out if this actor is in any films --->
        <CFQUERY DATASOURCE="ows" NAME="GetActorFilms">
          SELECT FilmID FROM FilmsActors
          WHERE ActorID = #FORM.ActorGrid.original.ActorID[i]#
        </CFQUERY>
        <!--- Only allow a delete if the actor is not in any films --->
        <CFIF GetActorFilms.RecordCount EQ 0>
          <CFQUERY DATASOURCE="ows">
            DELETE FROM Actors
            WHERE ActorID = #FORM.ActorGrid.original.ActorID[i]#
          </CFQUERY>
        <CFELSE>
          <CFOUTPUT>
            <P>Sorry, #FORM.ActorGrid.original.NameFirst[i]# 
            #FORM.ActorGrid.original.NameLast[i]# 
            cannot be deleted because that actor has been in 
            #GetActorFilms.RecordCount# of our films already.<BR>
          </CFOUTPUT> 
        </CFIF>
      </CFCASE>
    </CFSWITCH>
  </CFLOOP>
</CFIF>

<!--- Get list of Actors from database --->
<CFQUERY NAME="GetActors" DATASOURCE="ows">
  SELECT * FROM Actors
  ORDER BY NameFirst, NameLast
</CFQUERY>


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

  <!--- Grid control for expenses --->
  <CFGRID 
    NAME="ActorGrid" 
    QUERY="GetActors" 
    WIDTH="425"
    HEIGHT="300"
    ROWHEADERS="No"
    COLHEADERBOLD="Yes"
    COLHEADERALIGN="CENTER"
    SELECTMODE="EDIT"    
    INSERT="Yes"
    DELETE="Yes"
    INSERTBUTTON="Add New Actor"
    DELETEBUTTON="Delete Selected Actor">
    <!--- Hidden column for ActorID - does not actually get displayed --->
    <CFGRIDCOLUMN 
      NAME="ActorID"
      DISPLAY="No">
    <!--- Column for actor's first name --->
    <CFGRIDCOLUMN 
      NAME="NameFirst"
      HEADER="First Name"
      WIDTH="100">
    <!--- Column for actor's last name --->
    <CFGRIDCOLUMN 
      NAME="NameLast"
      HEADER="Last Name"
      WIDTH="100"
      BOLD="Yes">
    <!--- Column for actor's age--->
    <CFGRIDCOLUMN 
      NAME="Age"
      HEADER="Age"
      WIDTH="50"
      NUMBERFORMAT="999.">
    <!--- Column for babe factor --->
    <CFGRIDCOLUMN 
      NAME="IsTotalBabe"
      HEADER="Hottie"
      WIDTH="50"
      TYPE="boolean">
    <!--- Column for gender --->
    <CFGRIDCOLUMN 
      NAME="Gender"
      HEADER="Sex"
      WIDTH="100"
      VALUES="M,F"
      VALUESDISPLAY="Actor,Actress">
  </CFGRID>

  <P><INPUT TYPE="Submit" VALUE="Submit Changes">
</CFFORM>

</BODY>
</HTML>