import   java.sql.*;
public class SubscriberRecord {
	private static final int NAMELENGTH = 30;
	private static final int MINAGE =18;
	private static final int MAXAGE = 99; // sanity check
	private String 	givenName, familyName, eMail;
	private int	age;
	private String	sex;
	private int	id;
	public boolean isValid() {
	return
		(givenName != null) &&
		  (familyName != null) &&
		    (eMail != null) &&
		      (sex != null)  &&
		      ((age>=MINAGE) && (age <= MAXAGE));
	}

	public String getGivenName() { return givenName; }
	public void setGivenName(String aName) {
		givenName = null;
		if(aName == null) return; 
		int len = aName.length();
		if((len<1) || (len > NAMELENGTH)) return;
		givenName = aName;
	}
	
	public String getFamilyName() { return familyName; }
	public void setFamilyName(String aName) {
		familyName = null;
		if(aName == null) return; 
		int len = aName.length();
		if((len<1) || (len > NAMELENGTH)) return;
		familyName = aName;
	}
	
	public String getSex() { return sex; }
	public void setSex(String gender) {
		sex = null;
		if(gender.equals("Male")) sex = gender;
		else
		if(gender.equals("Female")) sex = gender;
	}

	public String getEMail() { return eMail; }
	public void setEMail(String anEmail) {
		eMail = null;
		if(anEmail == null) return; 
		int len = anEmail.length();
		if((len<1) || (len > NAMELENGTH)) return;
		eMail = anEmail;
	}
	
	public int getAge() { return age; }
	public void setAge(int anAge) {
		age = 0;
		if((anAge>=MINAGE) && (anAge<=MAXAGE)) age = anAge;
	}
	
	public boolean loadFromDatabase(int idNumber, Connection db) {
		givenName = null; 
		familyName = null; 
		eMail = null; 
		sex = null;
		age = 0; 
		id = 0;
		try {
			synchronized(db) {
				Statement stmt = db.createStatement ();		
				String request = "select * from members where membernum=" + 
					idNumber;
//System.out.println("Running query " + request);
				ResultSet rset = stmt.executeQuery(request);
				
				if(rset.next()) {
					givenName = rset.getString("GIVENNAME");
					familyName = rset.getString("FAMILYNAME");
					eMail = rset.getString("EMAIL");
					sex = rset.getString("SEX");
					id = idNumber;
					stmt.close();
				}
				else stmt.close();
				db.commit();
			}
		}
		catch (Exception e) {
			// Error message to log file
			System.out.println("Error loading a subscriber record - membership servlet");
			System.out.println(e.toString());
			return false; 
		}
		return id==idNumber;
	}

	public int createInDatabase( Connection db) {
		int idnumber = -1;
		try {
		  synchronized(db) { 
			  Statement stmt = db.createStatement ();		
			  String request =
			  	"select nextnumber from memnum where fortable='members'";
//	System.out.println("SubscriberRecord preparing SQL query");
			ResultSet rset = stmt.executeQuery(request);
			int value = 0;
			if(rset.next()) {
				value = rset.getInt("NEXTNUMBER");
			}
			else { stmt.close(); return -1; }
			value++;
//	System.out.println("SubscriberRecord Have membership number " + value);
			request = "update memnum set nextnumber=" +
				value + " where fortable='members'";
			stmt.executeUpdate(request);
			stmt.close();
//	System.out.println("SubscriberRecord, memnum table updated");
			// PreparedStatement pstmt =
			// db.prepareStatement(
				// "insert into members values( ?, ?, ?, ?, ?, ?)");
			// System.out.println("SubscriberRecord setting parameters");
			// pstmt.setInt(1, value);
			// pstmt.setString(2, givenName);
			// pstmt.setString(3, familyName);
			// pstmt.setString(4, eMail);
			// pstmt.setString(5, sex);
			// pstmt.setInt(6, age);
			// pstmt.executeUpdate();
			
			// Microsoft Access can't handle prepared statements, so give it a
			// simple statement and hope that don't have any pesky names with single quote
			// characters to cause problems!
			// If really were serious should use a StringBuffer to assemble the request.
			Statement maStmt = db.createStatement();
			String insertStr = "insert into members values (" + Integer.toString(value);
			insertStr = insertStr + ", '" + givenName;
			insertStr = insertStr + "', '" + familyName;
			insertStr = insertStr + "', '" + eMail;
			insertStr = insertStr + "', '" + sex;
			insertStr = insertStr + "', " + Integer.toString(age) + ")";
//System.out.println("Subscriber record sql : " + insertStr);
			maStmt.executeUpdate(insertStr);
//System.out.println("updated");
			maStmt.close();
		
			idnumber = value;
			db.commit();

		  }
		}
		catch (Exception e) { 
			try { db.rollback(); } catch(Exception whatnow) { }
			// Error message to log file
			System.out.println("Error creating a subscriber record - membership servlet");
			System.out.println(e.toString());
		}
		id = idnumber;
		return idnumber;
	}
}