<!-- takes inputs:
mode:  View Proposal
       Update This
       Add A Proposal
       Delete This

mode2: Edit
       Commit

viewnum:  NMO - three-digit integer indicating proposal number

semester: Fall   YYYY
	  Spring YYYY

-->       

<!sql connect localhost www DataBase>
<!sql database ece_4512_ppd>

<!sql if $mode2 == "Edit">
<!sql if $mode == "Update">

<!sql query "select semester, title, descr from projects where pNum = $viewnum" q3>

<!sql query "select name, email, role from users where pNum = $viewnum and semester like '@q3.semester' and role = 'L'" q0>

<!sql query "select name, email, role from users where pNum = $viewnum and semester like '@q3.semester' and role = 'M'" q1>

<!sql query "select name, email, role from users where pNum = $viewnum and semester like '@q3.semester' and role = 'A'" q2>


<!-- BEGIN HTML -->

<html>
<head>
<title>ECE 4512: Project Proposal Database</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css">
<!--
a {  text-decoration: underline}
table {  background-color: #FFFFFF}
a:active {  text-decoration: none}
a:hover {  text-decoration: none}
a:visited {  #6699CC}
td {  font-size: 10pt; font-family: Verdana, Arial, Helvetica, sans-serif}
h3 {  font-size: 14pt; font-weight: bold; color: #630000}
h4 {  font-size: 12pt; color: #630000}
input {  background-color: #FFFFFF}
-->
</style>
</head>

<body link=#336699 alink=#6699CC vlink=#6699CC bgcolor="#630000" text="black">
<form action="./input.sql" method="post">
<table width="100%" border="0" cellspacing="0" cellpadding="0">
  <tr> 
    <td valign="top"><img src="./images/top_left.gif" width="12" height="12" align="left" vspace="0" hspace="0"></td>
    <td colspan="3"> 
      <div align="center"> 
        <h3><a href="http://www.ece.msstate.edu/perl/course-masterframe?class=ECE&amp;no=4512">ECE 
          4512</a>: Senior Design<br>
          Project Proposal Database</h3>
      </div>
    </td>
    <td valign="top"><img src="./images/top_right.gif" width="12" height="12" align="right" vspace="0" hspace="0"></td>
  </tr>
  <tr> 
    <td rowspan="3">&nbsp;</td>
    <td valign="top" align="left" rowspan="3" colspan="3"> 
        <p><b><font face="Verdana, Arial, Helvetica, sans-serif">Enter a Project 
          Title:</font></b> <font face="Verdana, Arial, Helvetica, sans-serif">
          <input type="text" name="title" size="30" value="<!sql print @q3.title>">
          </font></p>
        <p><font face="Verdana, Arial, Helvetica, sans-serif"><b>Project Number:</b> 
          <!sql print $viewnum></font></p>
        <p> <font face="Verdana, Arial, Helvetica, sans-serif"><b>Semester:</b>  <select name="semester">
	  <option>Fall   2000
	  <option>Spring 2000
	  <option>Fall   2001
	  <option>Spring 2001
	  <option>Fall   2002
	  <option>Spring 2002
	  <option>Fall   2003
	  <option>Spring 2003
	  <option>Fall   2004
	  <option>Spring 2004
	  <option>Fall   2005
	  <option>Spring 2005
	  <option>Fall   2006
	  <option>Spring 2006
	  <option>Fall   2007
	  <option>Spring 2007
	  <option>Fall   2008
	  <option>Spring 2008
	  <option>Fall   2009
	  <option>Spring 2009
	  <option>Fall   2010
	  <option>Spring 2010
	  </select>
          <br>

	<p> <font face="Verdana, Arial, Helvetica, sans-serif"><b>Password:
	</b> <input type="password" name="password" size="20">
	</p>

        <p><font face="Verdana, Arial, Helvetica, sans-serif"><b>Advisor: 
          </b><br>
          Name 
          <input type="text" name="advname" size="30" value="<!sql print @q2.name>">
          E-mail 
          <input type="text" name="advmail" size="20" value="<!sql print @q2.email>">
          </font></p>
        <p><font face="Verdana, Arial, Helvetica, sans-serif"><b>Team Leader: 
          </b><br>
          Name 
          <input type="text" name="leadname" size="30" value="<!sql print @q0.name>">
          E-mail 
          <input type="text" name="leadmail" size="20" value="<!sql print @q0.email>">
          </font></p>
        <p><font face="Verdana, Arial, Helvetica, sans-serif"><b>Team Members:</b> 

         <br>
          Name 
          <input type="text" name="memname" size="30" value="<!sql print @q1.name>">
          E-mail 
          <input type="text" name="memmail" size="20" value="<!sql print @q1.email>">
          <br>
<!sql fetch q1>
          Name 
          <input type="text" name="memname2" size="30" value="<!sql print @q1.name>">
          E-mail 
          <input type="text" name="memmail2" size="20" value="<!sql print @q1.email>">
          <br>
<!sql fetch q1>
          Name 
          <input type="text" name="memname3" size="30" value="<!sql print @q1.name>">
          E-mail 
          <input type="text" name="memmail3" size="20" value="<!sql print @q1.email>">
          <br>
<!sql fetch q1>
          Name 
          <input type="text" name="memname4" size="30" value="<!sql print @q1.name>">
          E-mail 
          <input type="text" name="memmail4" size="20" value="<!sql print @q1.email>">
          </font></p>
        <p><font face="Verdana, Arial, Helvetica, sans-serif"><b>Enter the Project 
          Description:</b></font></p>
        <p> <font face="Verdana, Arial, Helvetica, sans-serif"> 
          <textarea name="description" rows="5" wrap="PHYSICAL" cols="80">
<!sql print @q3.descr>
</textarea>
          </font></p>
      </td>
    <td rowspan="3">&nbsp;</td>
  </tr>
  <tr> </tr>
  <tr> </tr>

<tr>
<td>&nbsp;</td>
<td>
<div align="right">
<input type="hidden" name="mode" value="Update">
	<input type="submit" name="mode2" value="Commit">
</div>
</form>
</td>
<td>
<div align="center">
<form action"./input.sql" method="post">
<input type="hidden" name="mode2" value="Edit">
<input type="hidden" name="viewnum" value="<!sql print $viewnum>">
          <input type="submit" name="mode" value="Add">
          <input type="submit" name="mode" value="Update">
          <input type="submit" name="mode" value="Delete">
</form>
</div>
</td>
<td>
<div align="left">
<form action="./display.sql" method="post">
          <input type="submit" name="mode" value="View">
          <input type="text" name="viewnum" size="3" maxlength="3" value="<!sql print $viewnum>">
</div>
</form>
</td>
<td>&nbsp;</td>
</tr>

  <tr> 
    <td valign="bottom"><img src="./images/bot_left.gif" width="12" height="12" align="left" vspace="0" hspace="0"></td>
    <td colspan="3">&nbsp;
    </td>
    <td valign="bottom"><img src="./images/bot_right.gif" width="11" height="12" align="right" vspace="0" hspace="0"></td>
  </tr>
</table>
</form>
</body>
</html>
<!sql endif>

<!-- NEXT CONDITION -->

<!sql if $mode == "Add">

<!-- BEGIN HTML -->

<html>
<head>
<title>ECE 4512: Project Proposal Database</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css">
<!--
a {  text-decoration: underline}
table {  background-color: #FFFFFF}
a:active {  text-decoration: none}
a:hover {  text-decoration: none}
a:visited {  #6699CC}
td {  font-size: 10pt; font-family: Verdana, Arial, Helvetica, sans-serif}
h3 {  font-size: 14pt; font-weight: bold; color: #630000}
h4 {  font-size: 12pt; color: #630000}
input {  background-color: #FFFFFF}
-->
</style>
</head>

<body link=#336699 alink=#6699CC vlink=#6699CC bgcolor="#630000" text="black">
<form action="./input.sql" method="post">
<table width="100%" border="0" cellspacing="0" cellpadding="0">
  <tr> 
    <td valign="top"><img src="./images/top_left.gif" width="12" height="12" align="left" vspace="0" hspace="0"></td>
    <td colspan="3"> 
      <div align="center"> 
        <h3><a href="http://www.ece.msstate.edu/perl/course-masterframe?class=ECE&amp;no=4512">ECE 
          4512</a>: Senior Design<br>
          Project Proposal Database</h3>
      </div>
    </td>
    <td valign="top"><img src="./images/top_right.gif" width="12" height="12" align="right" vspace="0" hspace="0"></td>
  </tr>
  <tr> 
    <td rowspan="3">&nbsp;</td>
    <td valign="top" align="left" rowspan="3" colspan="3"> 
        <p><b><font face="Verdana, Arial, Helvetica, sans-serif">Enter a Project 
          Title:</font></b> <font face="Verdana, Arial, Helvetica, sans-serif">
          <input type="text" name="title" size="30">
          </font></p>
        <p><font face="Verdana, Arial, Helvetica, sans-serif"><b>Project Number:</b> 
          will be assigned</font></p>
        <p> <font face="Verdana, Arial, Helvetica, sans-serif"><b>Semester:</b>
	 <select name="semester">
	  <option>Fall   2000
	  <option>Spring 2000
	  <option>Fall   2001
	  <option>Spring 2001
	  <option>Fall   2002
	  <option>Spring 2002
	  <option>Fall   2003
	  <option>Spring 2003
	  <option>Fall   2004
	  <option>Spring 2004
	  <option>Fall   2005
	  <option>Spring 2005
	  <option>Fall   2006
	  <option>Spring 2006
	  <option>Fall   2007
	  <option>Spring 2007
	  <option>Fall   2008
	  <option>Spring 2008
	  <option>Fall   2009
	  <option>Spring 2009
	  <option>Fall   2010
	  <option>Spring 2010
	  </select>
          <br>
</p>
	<p> <font face="Verdana, Arial, Helvetica, sans-serif"><b>Password:
	</b> <input type="password" name="password" size="20">
	</p>
	
        <p><font face="Verdana, Arial, Helvetica, sans-serif"><b>Advisor: 
          </b><br>
          Name 
          <input type="text" name="advname" size="30">
          E-mail 
          <input type="text" name="advmail" size="20">
          </font></p>
        <p><font face="Verdana, Arial, Helvetica, sans-serif"><b>Team Leader: 
          </b><br>
          Name 
          <input type="text" name="leadname" size="30">
          E-mail 
          <input type="text" name="leadmail" size="20">
          </font></p>
        <p><font face="Verdana, Arial, Helvetica, sans-serif"><b>Team Members:</b> 

         <br>
          Name 
          <input type="text" name="memname" size="30">
          E-mail 
          <input type="text" name="memmail" size="20">
          <br>
          Name 
          <input type="text" name="memname2" size="30">
          E-mail 
          <input type="text" name="memmail2" size="20">
          <br>
          Name 
          <input type="text" name="memname3" size="30">
          E-mail 
          <input type="text" name="memmail3" size="20">
          <br>
          Name 
          <input type="text" name="memname4" size="30">
          E-mail 
          <input type="text" name="memmail4" size="20">
          </font></p>
        <p><font face="Verdana, Arial, Helvetica, sans-serif"><b>Enter the Project 
          Description:</b></font></p>
        <p> <font face="Verdana, Arial, Helvetica, sans-serif"> 
          <textarea name="description" rows="8" wrap="PHYSICAL" cols="80">
Type here.
</textarea>
          </font></p>
</td>
<td rowspan="3">&nbsp;</td>
</tr>
<tr></tr>
<tr></tr>
<tr>
<td>&nbsp;</td>
<td>
<div align="right">
<input type="hidden" name="mode" value="Add">
	<input type="submit" name="mode2" value="Commit">
</div>
</form>
</td>
<td>
<div align="center">
<form action"./input.sql" method="post">
<input type="hidden" name="mode2" value="Edit">
<input type="hidden" name="viewnum" value="<!sql print $viewnum>">
          <input type="submit" name="mode" value="Add">
          <input type="submit" name="mode" value="Update">
          <input type="submit" name="mode" value="Delete">
</form>
</div>
</td>
<td>
<div align="left">
<form action="./display.sql" method="post">
          <input type="submit" name="mode" value="View">
          <input type="text" name="viewnum" size="3" maxlength="3" value="<!sql print $viewnum>">

</div>
</form>
</td>
<td>&nbsp;</td>
</tr>
  <tr> 
    <td valign="bottom"><img src="./images/bot_left.gif" width="12" height="12" align="left" vspace="0" hspace="0"></td>
    <td colspan="3">&nbsp;
    </td>
    <td valign="bottom"><img src="./images/bot_right.gif" width="11" height="12" align="right" vspace="0" hspace="0"></td>
  </tr>
</table>
</form>
</body>
</html>
<!sql endif>

<!-- NEXT CONDITION -->

<!sql if $mode == "Delete">
<html>
<head>
<title>ECE 4512: Project Proposal Database</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css">
<!--
a {  text-decoration: underline}
table {  background-color: #FFFFFF}
a:active {  text-decoration: none}
a:hover {  text-decoration: none}
a:visited {  #6699CC}
td {  font-size: 10pt; font-family: Verdana, Arial, Helvetica, sans-serif}
h3 {  font-size: 14pt; font-weight: bold; color: #630000}
h4 {  font-size: 12pt; color: #630000}
input {  background-color: #FFFFFF}
-->
</style>
</head>

<body link=#336699 alink=#6699CC vlink=#6699CC bgcolor="#630000" text="black">
<form action="./input.sql" method="post">
<table width="100%" border="0" cellspacing="0" cellpadding="0">
  <tr> 
    <td valign="top"><img src="./images/top_left.gif" width="12" height="12" align="left" vspace="0" hspace="0"></td>
    <td colspan="3"><img src="./images/line_1000.gif" width="600" height="1"><br> 
      <div align="center"> 
        <h3><a href="http://www.ece.msstate.edu/perl/course-masterframe?class=ECE&amp;no=4512">ECE 
          4512</a>: Senior Design<br>
          Project Proposal Database</h3>
      </div>
    </td>
    <td valign="top"><img src="./images/top_right.gif" width="12" height="12" align="right" vspace="0" hspace="0"></td>
  </tr>
<tr>
<td rowspan="3">&nbsp;</td>
<td rowspan="3" colspan="3"><div align="center">
<br><h3>Delete Proposal <!sql print $viewnum></h3><br>
Enter the proposal number you wish to delete.<br>
Proposal#: <input type="text" length="4" name="viewnum" value="<!sql print $viewnum>">
<br>
Enter your password below exactly as  you entered it when creating your 
proposal.<br>
Password: <input type="password" name="password">
</div>
</td>
<td rowspan="3">&nbsp;</td>
</tr>
  <tr> </tr>
  <tr> </tr>

<tr>
<td>&nbsp;</td>
<td>
<div align="right">
<input type="hidden" name="mode" value="Delete">
	<input type="submit" name="mode2" value="Commit">
</div>
</form>
</td>
<td>
<div align="center">
<form action"./input.sql" method="post">
<input type="hidden" name="mode2" value="Edit">
<input type="hidden" name="viewnum" value="<!sql print $viewnum>">
          <input type="submit" name="mode" value="Add">
          <input type="submit" name="mode" value="Update">
          <input type="submit" name="mode" value="Delete">
</form>
</div>
</td>
<td>
<div align="left">
<form action="./display.sql" method="post">
          <input type="submit" name="mode" value="View">
          <input type="text" name="viewnum" size="3" maxlength="3" value="<!sql print $viewnum>">
</div>
</form>
</td>
<td>&nbsp;</td>
</tr>

  <tr> 
    <td valign="bottom"><img src="./images/bot_left.gif" width="12" height="12" align="left" vspace="0" hspace="0"></td>
    <td colspan="3">&nbsp;
    </td>
    <td valign="bottom"><img src="./images/bot_right.gif" width="11" height="12" align="right" vspace="0" hspace="0"></td>
  </tr>
</table>
</body>
</html>
<!sql endif>
<!sql endif>

<!-- NEXT EDIT MODE -->

<!sql if $mode2 == "Commit">
<!sql if $mode == "Delete">

<!sql query "select pWord from projects where pNum = '?viewnum'" q5>
<!sql query "select password('?password') as password" q4>
<!sql if @q4.password == @q5.pWord>

<!sql query "delete from users where pNum = '?viewnum'">
<!sql query "delete from projects where pNum = '?viewnum'">

<html>
<body>
Project <!sql print $viewnum> deleted.
</body>
</html>

<!sql else>

<html>
<body>
Password not accepted. Access Denied.
</body>
</html>
<!sql endif>
<!sql endif>

<!sql if $mode == "Add">

<!sql connect localhost www DataBase>
<!sql database ece_4512_ppd>

<!sql query "insert into projects (semester, title, descr, pWord) values('?semester', '?title', '?description', password('?password'))">

<!sql query "select pNum from projects where semester='?semester'and title='?title' and descr='?description'" q0>

<!sql set number @q0.pNum>

<!sql query "insert into users (pNum, semester, name, email, role) values(?number, '?semester', '?advname', '?advmail', 'A')">

<!sql query "insert into users (pNum, semester, name, email, role) values(?number, '?semester', '?leadname', '?leadmail', 'L')">

<!sql query "insert into users (pNum, semester, name, email, role) values(?number, '?semester', '?memname', '?memmail', 'M')">

<!sql query "insert into users (pNum, semester, name, email, role) values(?number, '?semester', '?memname2', '?memmail2', 'M')">

<!sql query "insert into users (pNum, semester, name, email, role) values(?number, '?semester', '?memname3', '?memmail3', 'M')">

<!sql query "insert into users (pNum, semester, name, email, role) values(?number, '?semester', '?memname4', '?memmail4', 'M')">

<html>
<body>
Project <!sql print @q0.pNum> added
</body>
</html>

<!sql endif>

<!sql if $mode == "Update">

<!sql connect localhost www DataBase>
<!sql database ece_4512_ppd>

<!sql query "select pWord from projects where pNum = '?viewnum'" q5>
<!sql query "select password('?password') as password" q4>
<!sql if ?q4.password == ?q5.pWord>

<!sql free q4>
<!sql free q5>

<!sql query "select semester from projects where pNum = '?viewnum'" q4>

<!sql query "update projects set semester='?semester', title='?title', descr='?description' where pNum = '?viewnum'">

<!sql query "select email, role from users where pNum='?viewnum' and semester='?q4.semester' and role='A'" q5>

<!sql query "update users set semester='?semester', name='?advname', email='?advmail', role='A' where email='@q5.email'">
<!sql free q5>
<!sql query "select email, role from users where pNum='?viewnum' and semester='?q4.semester' and role='L'" q5>

<!sql query "update users set semester='?semester', name='?leadname', email='?leadmail', role='L' where email='@q5.email'">
<!sql free q5>
<!sql query "select email, role from users where pNum='?viewnum' and semester='?q4.semester' and role='M'" q5>

<!sql query "update users set semester='?semester', name='?memname', email='?memmail', role='M' where email='@q5.email'">

<!sql fetch q5>
<!sql query "update users set semester='?semester', name='?memname2', email='?memmail2', role='M' where email='@q5.email'">

<!sql fetch q5>
<!sql query "update users set semester='?semester', name='?memname3', email='?memmail3', role='M' where email='@q5.email'">

<!sql fetch q5>
<!sql query "update users set semester='?semester', name='?memname4', email='?memmail4', role='M' where email='@q5.email'">

<html>
<body>
Project <!sql print @q0.pNum> updated
</body>
</html>

<!sql else>
<html>
<body>
Password not accepted.  Access denied.
</body>
</html>

<!-- ends mode checking -->
<!sql endif>

<!-- ends mode2 checking -->
<!sql endif>

<!sql free q0>
<!sql free q1>
<!sql free q2>
<!sql free q3>
<!sql free q4>
<!sql free q5>
<!sql close>


