package e4s.tutorial;

import java.text.SimpleDateFormat;

import e4s.db.E4DBException;
import e4s.html.*;
import e4s.html.input.extended.E4Fieldset;
import e4s.html.input.extended.E4ValidatorNoBlanks;
import e4s.servlet.E4ModuleImplementation;


/** 
 * Database example.
 *
 * {@tutorial Example_DB_Select}
 */
public class Example_DB_Select extends E4ModuleImplementation
{
	public static E4Method startSelection     = null;
	public static E4Method addNewRecord	     = null;
	public static E4Method saveNewRecord      = null;
	public static E4Method saveExistingRecord = null;
	public static E4Method modifyRecord       = null;
	public static E4Method deleteRecord       = null;


 

	public void startSelection( HTML html, E4CgiParams params )
	{
		BODY body = html.BODY();
		
		TABLE table = body.TABLE();
		table.setBorder();
		table.setBorderColor(E4Color.GRAY);
		table.setCellspacing(0,0);
		
		try
		{
			T_TUTORIAL_ARTICLES_Sel T_TUTORIAL_ARTICLES = new T_TUTORIAL_ARTICLES_Sel();
			T_TUTORIAL_ARTICLES.sort_ARTNO();
			
			T_TUTORIAL_ARTICLES.executeQuery();
			
			TR header = table.TR();
			
			header.setBgColor(E4Color.GRAY(25));
			
			header.TD().println("Article Number");
			header.TD().println("Designation");
			header.TD().println("Items on stock");
			header.TD().println("Price");
			header.TD().println("Can be ordered");
			header.TD().println("Delivery expected");
			header.TD().println(NBSP);
			header.TD().println(NBSP);

			while( T_TUTORIAL_ARTICLES.next() )
			{
				tutorialArticleNumber artNo = T_TUTORIAL_ARTICLES.get_ARTNO();
				String designation = T_TUTORIAL_ARTICLES.get_DESIGNATION();
				long onStock = T_TUTORIAL_ARTICLES.get_ONSTOCK();
				float price = T_TUTORIAL_ARTICLES.get_PRICE();
				boolean canOrder = T_TUTORIAL_ARTICLES.get_CANORDER();
				java.util.Date nextdelivery = T_TUTORIAL_ARTICLES.get_NEXT_DELIVERY();
				
				TR tr = table.TR();
				tr.TD().print(artNo);
				tr.TD().print(designation);
				tr.TD().print(onStock);
				tr.TD().print(price);
				tr.TD().print(canOrder ? "yes" : "no");
				tr.TD().print(nextdelivery,new SimpleDateFormat("dd.MM.yyyy"));
				
				A modify = tr.TD().A(modifyRecord);
				modify.IMG("images/db/modify.gif");
				modify.addParameter(T_TUTORIAL_ARTICLES_Sel.COL_ARTNO,artNo);

				A delete = tr.TD().A(deleteRecord);
				delete.IMG("images/db/delete.gif");
				delete.addParameter(T_TUTORIAL_ARTICLES_Sel.COL_ARTNO,artNo);
			}
			
			T_TUTORIAL_ARTICLES.close();
			
			A addnew = body.A(addNewRecord);
			addnew.println("[add new record]");
		}
		catch( E4DBException dbe )
		{
			body.SystemError(dbe);	
		}
	}
	
	
	
	public void addNewRecord( HTML html, E4CgiParams params )
	{
		FORM form = html.BODY().FORM();
		E4Fieldset fieldset = T_TUTORIAL_ARTICLES_Sel.createFIELDSET();
		form.addElement(fieldset);
		fieldset.setValue(params);
		form.setAction(saveNewRecord);
		form.FORM_Submit("Save");
	}
	
	public void saveNewRecord( HTML html, E4CgiParams params )
	throws Exception
	{
		html.trace("saveNewRecord");
		saveRecord( html, params, true );   
	}
	
	
	public void saveExistingRecord( HTML html, E4CgiParams params )
	throws Exception
	{
		html.trace("saveExistingRecord");
		saveRecord( html, params, false );   
	}
	
	
	public void saveRecord( HTML html, E4CgiParams params, boolean newrecord )
	throws Exception
	{ 
		E4Fieldset fieldset = T_TUTORIAL_ARTICLES_Sel.createFIELDSET();
		
		if (newrecord)
			fieldset.setValidator(T_TUTORIAL_ARTICLES_Sel.COL_ARTNO,new E4ValidatorNoBlanks());
		
		TABLE table = html.TABLE();
		params.toTable(table);
		
		fieldset.setValue(params);
		
		if (! fieldset.validate(getServlet()))
		{
			FORM form = html.BODY().FORM();
			form.addElement(fieldset);
			
			if (! newrecord)
				fieldset.setReadOnly(T_TUTORIAL_ARTICLES_Sel.COL_ARTNO,true);
				
			form.setAction(saveNewRecord);
			form.FORM_Submit("Save");
		}
		else if (newrecord)
		{
			html.I().println("Saving record into datatabase..");
			
			T_TUTORIAL_ARTICLES_Mod insert_T_TUTORIAL_ARTICLES = new T_TUTORIAL_ARTICLES_Mod();
			insert_T_TUTORIAL_ARTICLES.set(fieldset);
			insert_T_TUTORIAL_ARTICLES.executeInsert();
			
			startSelection( html, params );
		}
		else // if (! newrecord)
		{
			html.I().println("Updateing existing record into datatabase..");
			
			tutorialArticleNumber artno = new tutorialArticleNumber(fieldset.getValue(T_TUTORIAL_ARTICLES_Sel.COL_ARTNO));

			T_TUTORIAL_ARTICLES_Mod update_T_TUTORIAL_ARTICLES = new T_TUTORIAL_ARTICLES_Mod();
			update_T_TUTORIAL_ARTICLES.set(fieldset);
			update_T_TUTORIAL_ARTICLES.where_ARTNO(artno);
			update_T_TUTORIAL_ARTICLES.executeUpdate();
			
			html.rem(update_T_TUTORIAL_ARTICLES.toString());
			
			startSelection( html, params );
		}
	}

	public String validateArticleNumber( String fieldname, String label, String value )
	{
		String res = null;
		
		if (! isok(value))
		{
			res = "You must enter a value!";
		}
		else
		{
			try
			{
				T_TUTORIAL_ARTICLES_Sel T_TUTORIAL_ARTICLES = new T_TUTORIAL_ARTICLES_Sel();
				T_TUTORIAL_ARTICLES.where_ARTNO(new tutorialArticleNumber(value));
				
				T_TUTORIAL_ARTICLES.executeQuery();
				
				if (T_TUTORIAL_ARTICLES.next())
				{
					String designation = T_TUTORIAL_ARTICLES.get_DESIGNATION();
					res = "Article No. " + value + " (" + ok(designation) + ") allready exists";
				}
			
				T_TUTORIAL_ARTICLES.close();
			}
			catch( E4DBException dbe )
			{
				SystemError( dbe );
			}
		}
			
		return res;
	}
	

	public void modifyRecord( HTML html, E4CgiParams params )
	throws Exception      
	{
		tutorialArticleNumber artno = new tutorialArticleNumber(params.get( T_TUTORIAL_ARTICLES_Sel.COL_ARTNO ));  

		html.rem(artno);
		
		if (! isok(artno))
			throw new Exception("Error, articlenumber not specified");
		
		try
		{
			T_TUTORIAL_ARTICLES_Sel T_TUTORIAL_ARTICLES = new T_TUTORIAL_ARTICLES_Sel();
			T_TUTORIAL_ARTICLES.where_ARTNO(artno);
			
			T_TUTORIAL_ARTICLES.executeQuery();
			
			if ( T_TUTORIAL_ARTICLES.next() )
			{
				FORM form = html.BODY().FORM();
				E4Fieldset fieldset = T_TUTORIAL_ARTICLES.get_FIELDSET(form);
				fieldset.setReadOnly(T_TUTORIAL_ARTICLES_Sel.COL_ARTNO,true);
				form.setAction(saveExistingRecord);
				form.FORM_Submit("Save");
			}
			else
			{
				html.B().print("Article " + artno + " not found");   
			}
		 
			T_TUTORIAL_ARTICLES.close();
		}
		catch( E4DBException dbe )
		{
			html.SystemError( dbe );
		}
	}

 
	public void deleteRecord( HTML html, E4CgiParams params )
	throws Exception      
	{
		tutorialArticleNumber artno = new tutorialArticleNumber(params.get( T_TUTORIAL_ARTICLES_Sel.COL_ARTNO ));  
		
		html.rem("deleting article " + artno);
		
		if (! isok(artno))
			throw new Exception("Error, articlenumber not specified");
		
		try
		{
			T_TUTORIAL_ARTICLES_Mod delete_T_TUTORIAL_ARTICLES = new T_TUTORIAL_ARTICLES_Mod();
			delete_T_TUTORIAL_ARTICLES.where_ARTNO(artno);
			delete_T_TUTORIAL_ARTICLES.executeDelete();
			
			html.rem( delete_T_TUTORIAL_ARTICLES.toString() );
		}
		catch( E4DBException dbe )
		{
			html.SystemError( dbe );
		}
		
		html.println("Article " + artno + " has been deleted");
		html.P();
		
		startSelection( html, params );
	}
}