#!c:/Perl/bin/perl.exe

use strict;
use warnings;

use DBI;
use DBD::ODBC;
use CGI qw (:standard);

# constant definitions
use constant HTML_TITLE 	=> "Cinema Booking System - Dataware House";
use constant DSN 		=> "dbi:ODBC:CBS";
use constant WHAT_TODO	 	=> "what_todo";
use constant WHICH_TABLE 	=> "which_table";
use constant WHICH_QUERY 	=> "which_query";
use constant MANUAL_SQL 	=> "manual_sql";
use constant INSERT_VIEW 	=> "insert_view";
# WHAT_TODO popup menu options
use constant INSERT_OPT 	=> 'Insert into a table';
use constant VIEW_OPT 		=> 'View a table';
use constant RUN_QUERY_OPT	=> 'Run a saved query';
use constant ADD_QUERY_OPT	=> 'Add a query to saved queries';
# WHICH_TABLE popup menue options
use constant TBL_CUSTOMER 	=> 'Customer';
use constant TBL_BOOKING 	=> 'Booking';
use constant TBL_CINEMA 	=> 'Cinema';
use constant TBL_SALE 		=> 'Sale';
use constant TBL_MOVIE 		=> 'Movie';

# start html 
print header();
print start_html(HTML_TITLE);


# dynamic html main body
if (!param) {
	first_page();
} elsif (param(WHAT_TODO)) {
	what_todo(param(WHAT_TODO));
} elsif (param(WHICH_TABLE)) {
	if (param(INSERT_VIEW) eq INSERT_OPT) {
		insert_into(param(WHICH_TABLE));
	} elsif (param(INSERT_VIEW) eq VIEW_OPT) {
		view(param(WHICH_TABLE));
	}
} else	{
	print param(WHAT_TODO);
}

# end html
print end_html();

# sub routine definitions
sub first_page
{
	print 	h1("Cinema Booking System"),
		hr(),
		start_form( -method => 'GET'),
		p(b("Select one of the following:")),
		br(),
		popup_menu( -name => WHAT_TODO,
			-value => [INSERT_OPT,
				VIEW_OPT,
				RUN_QUERY_OPT,
				ADD_QUERY_OPT]),
		br(), br(),
		submit( -value => "Proceed"),
		end_form();
}

sub what_todo
{
	my $todo = shift(@_);
	
	if ($todo eq INSERT_OPT 
		||
		$todo eq VIEW_OPT) {
		which_table($todo);
	} elsif ($todo eq RUN_QUERY_OPT) {
		run_query();
	} elsif ($todo eq ADD_QUERY_OPT) {
		add_query();
	} else {
		# TODO: show error if you want!
	}
}

sub which_table
{
	print 	h1("Cinema Booking System"),
		hr(),
		start_form( -method => 'GET'),
		p(b("Select a table:")),
		br(),
		popup_menu( -name => WHICH_TABLE,
			-value => [TBL_CUSTOMER,
				TBL_CINEMA,
				TBL_BOOKING,
				TBL_MOVIE,
				TBL_SALE]),
		br(), br(),
		hidden( {-name => INSERT_VIEW, -value => shift(@_)} ),
		submit( -value => "Proceed"),
		end_form();
}

sub insert_into
{
	my $table = shift(@_);

	if ($table eq TBL_CINEMA) {
		
	} elsif ($table eq TBL_CUSTOMER) {
		
	} elsif ($table eq TBL_MOVIE) {
		
	} elsif ($table eq TBL_SALE) {
			
	} elsif ($table eq TBL_BOOKING) {
		
	}
}

sub view
{
	my $table = shift(@_);
	
	my $dbh = DBI->connect(DSN, "", "")
		or die "Cannot connect to database: " . DBI->errstr;

	my $query = 'SELECT * FROM ' . $table . ';';
	print $query;
	my $sth = $dbh->prepare($query)
		or die "Cannot prepare query: " . $dbh->errstr;
	$sth->execute()
		or die "Cannot execute query: " . $sth->errstr;
	
	my $rows = $sth->fetchall_arrayref();
	$sth->finish();
	
	my $tablerows = "";
	foreach my $row (@$rows) {
			$tablerows .= Tr (td($row));
	}
	
	print 	h1("$table contents"),
			br(),
			hr(),
			table($tablerows),
			br(),
			a({-href => "/cgi-bin/CBS.pl"}, "Back to the Main page.");
}

sub run_query
{
	print 	h1("this is to run query");
}

sub add_query
{
	print 	h1("this is to add query");
}