#!/usr/local/bin/perl

MAIN:
{
	$dls='x';
	$backup='';
	if ($dls) {$path="/var/local/html/";}
	else {$path="usr/www/classlea/html/";}
	&Connect;
	&Init;
	&Query;
	&Disconnect;
}

sub Init
{
	local($j,$z);
	local(@aa,@fields,@values);
	$zpc="::";
	@tables=("branch","staff1","property_for_rent","renter","owner","viewing");
	$tt=@tables;
	$j=0;
	foreach $ii (@tables)
	{
		$table=$ii;
		@aa=&Read($ii); $j++;
		$k=0;
		foreach $kk (@aa)
		{
			$z=$kk; 
			if ($k==0) 
			{
				@fields=split(/${zpc}/,$kk); 
				&Drop($table);
				&Create($table,@fields);
			}
			else
			{
				@values=split(/${zpc}/,$kk); 
				&Insert($table,@values);
			}
			$k++;
		}
		$title="Create table ${table}";
		&Select($table); &TableDisplay($s,$title);
	}
}

sub Read
{
	local($in)=@_;
	local($j,$_);
	local(@a);
	open (IN, $in);
	$j=0;
	while (<IN>)
	{
		chop;
		$a[$j]=$_; $j++;
	}
	close (IN);
	@a;
}

sub ExecDisplay
{
	local($s,$title)=@_;
	&Execute($s); &TableDisplay($s,$title);
}

sub TableDisplay
{
	local($s,$title)=@_;
	print "\n<p><b>${title}<b>";
if ($op==1) {print "<br>".$s."<p>";}
	print "<table border='1'>";
	&Display;
	print "\n<table>";
}

sub Display
{
	while (@row=$sth->fetchrow_array)
	{
		print "\n<tr>";
		foreach $ii (@row)
		{
			print "\n<td>".$ii."<td>";
		}
		print "\n<tr>";
	}
}

sub Connect
{
	use DBI;
	use CGI qw(:standard);
	if ($dls)
	{
		$database="csis4530";
		$db_server="dls.nova.edu";
		$db_server=$server;
		$user="jclevin";
		$password="scis1";
	}
	else
	{
		$database="classlea";
		$db_server="216.55.5.85";
		$db_server=$server;
		$user="classlea";
		$password="mientje";
	}
	$dbh=DBI->connect("DBI:mysql:$database:$db_server",$user,$password);
	$query = new CGI;
	print $query->header;
	print "\n<html>\n<body>";
}

sub Drop
{
	local($table)=@_;
	local($s);
	$s="";
	$s=$s."drop table ${table}";
	print $s;
	&Execute($s);
}

sub Create
{
	local($table,@fields)=@_;
	local($ii,$j,$jj,$lg,$lf,$lp);
	local($s,$s1,$s2,$t1,$t2);
	$lg=15;
	$s1="char(${lg})";
#	$s2="int";
	$s2="decimal(8,2)";
	$lf=@fields;
	$s=""; $j=0;
	$s=$s."create table ${table}(";
	foreach $ii (@fields)
	{
		$t1=substr($ii,0,1);
#		if ($t1 eq '*') {print $ii;}
		$t2=substr($ii,1);
		if ($j<lf-1) {$lp=",";}
		else {$lp=")";}
		if ($t1 eq '*') {$s=$s."${t2} ${s2}${lp}";}
		else {$s=$s."${ii} ${s1}${lp}";}
#		$s=$s."${ii} ${s1}${lp}";
		$j++;
	}
	print "<br>".$s;
	&Execute($s);
}

sub Insert
{
	local($table,@values)=@_;
	local($ii,$j,$jj,$lg,$lf,$lp);
	local($s);
	$lg=15;
	$lv=@values;
	$s=""; $j=0;
	$s=$s."insert into ${table} values (";
	foreach $ii (@values)
	{
		if ($j<lv-1) {$lp=",";}
		else {$lp=")";}
		$s=$s."'${ii}'${lp}";
		$j++;
	}
	print "<br>".$s;
	&Execute($s);
}
	
sub Select
{
	local($table)=@_;
	local($s,$ss);
	$s="";
	$s=$s."select * from ${table}";
	&Execute($s);
}

sub Execute
{
	local($s)=@_;
	$statement=$s;
	$sth=$dbh->prepare($statement);
	$rv=$sth->execute;
}

sub Disconnect
{
	$rc=$sth->finish;
	$rc=$dbh->disconnect;
	print "\n<body>\n<html>";
}

sub Query
{
	$op=1;
	&Query1;
	&Query2;
	&Query3;
	&Query4;
	&Query5;
	&Query6;
	&Query7;
	&Query8;
	&Query9;
	&Query10;
	&Query11;
	&Query12;
	&Query13a;
	&Query13b;
	&Query14;
	&Query15;
	&Query16;
	&Query17;
	&Query18;
	&Query19;
	&Query20;
	&Query21;
	&Query22;
	&Query23;
	&Query24;
	&Query25;
	&Query26;
	&Query27;
	&Query28;
	&Query29;
	&Query30;
	&Query31;
	&Query32;
	&Query33;
	&Query34;
	&Query35;
	&Query36;
	&Query37;
	&Query38;
	&Query39;
	$op=0;
}

sub QueryX
{
	local($x)=@_;
	local($table,$title);
	local($s);
	$s=""; $s=$s."show columns from ${x}";
	&ExecDisplay($s,"columns");
	$table=$x; $title="1. List full detail of all ${x}";
	$s=""; $s=$s."select * from ${x}";
	&ExecDisplay($s,$title);
	$table=$x; $title="1A. Save full detail of all ${x}";
	$s="";
#	$s=$s."select * into outfile '${path}DB/BACKUP/my${x}' fields terminated by ',' optionally enclosed by '\"' lines terminated by \"\\n\" from ${x}";
	$s=$s."select * into outfile '${path}DB/BACKUP/my${x}' from ${x}";
	&ExecDisplay($s,$title);
	if ($backup) 
	{
		$table=$x; $title="1B.Backup full detail of all ${x}";
		$s="";
		$s=$s."backup table ${x} to '${path}DB/BACKUP';";
		&ExecDisplay($s,$title);
	}
}

sub Query1
{
	local($ii);
	foreach $ii (@tables)
	{
		&QueryX($ii);
	}
}

sub Query2
{
	local($table,$title);
	local($s);
	$table="staff1"; $title="2. List partial detail of all staff";
	$s=""; $s=$s."select sno,fname,lname,salary from ${table}";
	&ExecDisplay($s,$title);
}

sub Query3
{
	local($table,$title);
	local($s);
	$table="viewing"; $title="3. List property numbers of all viewed properties";
	$s=""; $s=$s."select distinct pno from ${table}";
	&ExecDisplay($s,$title);
}

sub Query4
{
	local($table,$title);
	local($s);
	$table="staff1"; $title="4. Column Expression does not seem to work";
	$s=""; $s=$s."select sno, sname, lname, Salary/12 AS monthly_salary from ${table}";
	&ExecDisplay($s,$title);
}

sub Query5
{
	local($table,$title);
	local($s);
	$table="staff1"; $title="5. List staff with salary > 10,000";
	$s=""; $s=$s."select sno,fname,lname,salary from ${table} where salary>10000";
	&ExecDisplay($s,$title);
}

sub Query6
{
	local($table,$title);
	local($s);
	$table="branch"; $title="6. List offices located in London or Glaslow";
	$s=""; $s=$s."select bno,street,area,city from ${table} where city='London' or city='Glasgow'";
	&ExecDisplay($s,$title);
}

sub Query7
{
	local($table,$title);
	local($s);
	$table="staff1"; $title="7. List staff with salary range
20000-30000";
	$s=""; $s=$s."select sno,fname,lname,salary from ${table} where salary>=20000 and salary <30000";
	&ExecDisplay($s,$title);
}

sub Query8
{
	local($table,$title);
	local($s);
	$table="staff1"; $title="8. List all managers and deputy managers";
	$s=""; $s=$s."select sno,fname,lname,position from ${table} where position IN ('Manager','Deputy')";
	&ExecDisplay($s,$title);
}

sub Query9
{
	local($table,$title);
	local($s);
	$table="staff1"; $title="9. Pattern matching with LIKE does not seem to work";
	$s=""; $s=$s."select sno,fname,lname,address,salary from ${table} where address like '%Glasgow%'";
	&ExecDisplay($s,$title);
}

sub Query10
{
	local($table,$title);
	local($s);
	$table="viewing"; $title="10. Null search condition does not seem
to work";
	$s=""; $s=$s."select rno,date from ${table} where pno='PG4' and comment is null";
	&ExecDisplay($s,$title);
}

sub Query11
{
	local($table,$title);
	local($s);
	$table="staff1"; $title="11. Order staff by descending salary";
	$s=""; $s=$s."select sno,fname,lname,salary from ${table} order by salary desc";
	&ExecDisplay($s,$title);
}

sub Query12
{
	local($table,$title);
	local($s);
	$table="property_for_rent"; $title="12. Order property by type, rent";
	$s=""; $s=$s."select pno,type,rooms,rent from ${table} order by type,rent desc";
	&ExecDisplay($s,$title);
}

sub Query13a
{
	local($table,$title);
	local($s);
	$table="staff1"; $title="13a. Illegal use of aggregate function";
	$s=""; $s=$s."select sno, count(salary) from ${table}";
#	$s=""; $s=$s."select count(salary) from ${table}";
	&ExecDisplay($s,$title);
}

sub Query13b
{
	local($table,$title);
	local($s);
	$table="property_for_rent"; $title="13b. Use of Count(*)";
	$s=""; $s=$s."select count(*) as count from ${table} where rent>350";
	&ExecDisplay($s,$title);
}

sub Query14
{
	local($table,$title);
	local($s);
	$table="viewing"; $title="14. Count(distinct ...) does not seem to work";
	$s=""; $s=$s."select count(distinct pno) as count from ${table} where date between '1-May-98' and '31-May-98'";
#	$s=""; $s=$s."select count(pno) as count from ${table} where date between '1-May-98' and '31-May-98'";
	&ExecDisplay($s,$title);
}

sub Query15
{
	local($table,$title);
	local($s);
	$table="staff1"; $title="15. Count and Sum";
	$s=""; $s=$s."select count(sno) as count, sum(salary) as sum from ${table} where position='Manager'";
	&ExecDisplay($s,$title);
}

sub Query16
{
	local($table,$title);
	local($s);
	$table="staff1"; $title="16. Min, Max, Avg";
	$s=""; $s=$s."select min(salary) as min, max(salary) as max, avg(salary) as avg from ${table}";
	&ExecDisplay($s,$title);
}

sub Query17
{
	local($table,$title);
	local($s);
	$table="staff1"; $title="17. Group by";
	$s=""; $s=$s."select bno, count(sno) as count, sum(salary) as sum from ${table} group by bno order by bno";
	&ExecDisplay($s,$title);
}

sub Query18
{
	local($table,$title);
	local($s);
	$table="staff1"; $title="18. Group by, having... does not seem to work";
	$s=""; $s=$s."select bno, count(sno) as count, sum(salary) as sum from ${table} group by bno having count(sno>1) order by bno";
	&ExecDisplay($s,$title);
}

sub Query19
{
	local($table,$title);
	local($s);
	$table="staff1"; $title="19. Subquery does not seem to work";
	$s=""; $s=$s."select sno,fname,lname,position from ${table} where bno=(select bno from branch where street = '163 Main St')";
	&ExecDisplay($s,$title);
}

sub Query20
{
	local($table,$title);
	local($s);
	$table="staff1"; $title="20. Subquery with aggregate function";
	$s=""; $s=$s."select sno,fname,lname,position,salary-(select avg(salary) from ${table}) as sal_diff from ${table} where salary > (select avg(salary) from ${table})";
	&ExecDisplay($s,$title);
}

sub Query21
{
	local($table,$title);
	local($s);
	$table="property_for_rent"; $title="21. Nested Subquery does not seem to work";
	$s=""; $s=$s."select pno,street,area,city,pcode,type,rooms,rent from ${table}) where sno in (select sno from staff where bno in (select bno from branch where street = '163 Main St'))";
	&ExecDisplay($s,$title);
}

sub Query22
{
	local($table,$title);
	local($s);
	$table="staff1"; $title="22. Any/Some does not seem to work";
	$s=""; $s=$s."select sno,fname,lname,position,salary from ${table} where salary > some (select salary from ${table} where bno = 'B3')";
	&ExecDisplay($s,$title);
}

sub Query23
{
	local($table,$title);
	local($s);
	$table="staff1"; $title="23. All does not seem to work";
	$s=""; $s=$s."select sno,fname,lname,position,salary from ${table} where salary > all (select salary from ${table} where bno = 'B3')";
	&ExecDisplay($s,$title);
}

sub Query24
{
	local($table,$title);
	local($s);
	$table="renter"; $table1="viewing"; $title="24. Join";
	$s=""; $s=$s."select r.rno,fname,lname,pno,comment from ${table} r, ${table1} v where r.rno = v.rno";
	&ExecDisplay($s,$title);
}

sub Query25
{
	local($table,$title);
	local($s);
	$table="staff1"; $table1="property_for_rent"; $title="25. Sorting a Join";
	$s=""; $s=$s."select s.bno,s.sno,fname,lname,pno from ${table} s, ${table1} p where s.sno = p.sno order by s.bno,s.sno,pno";
	&ExecDisplay($s,$title);
}

sub Query26
{
	local($table,$title);
	local($s);
	$table="branch"; $table1="staff1"; $table2="property_for_rent"; $title="26. Three table Join";
	$s=""; $s=$s."select b.bno,b.city,s.sno,fname,lname,pno from ${table} b, ${table1} s, ${table2} p where b.bno = s.bno and s.sno=p.sno order by b.bno,s.sno,pno";
	&ExecDisplay($s,$title);
}

sub Query27
{
	local($table,$title);
	local($s);
	$table="staff1"; $table1="property_for_rent"; $title="27. Multiple grouping columns";
	$s=""; $s=$s."select s.bno,s.sno,count(*) as count from ${table} s, ${table1} p where s.sno=p.sno group by s.bno,s.sno order by s.bno,s.sno";
	&ExecDisplay($s,$title);
}

sub Query28
{
	local($table,$title);
	local($s);
	$table="branch"; $table1="property_for_rent"; $title="28. Left outer join";
	$s=""; $s=$s."select b.*, p.* from ${table} b left join ${table1} p on b.city =p.city";
	&ExecDisplay($s,$title);
}

sub Query29
{
	local($table,$title);
	local($s);
	$table="branch"; $table1="property_for_rent"; $title="29. Right outer join does not seem to work";
	$s=""; $s=$s."select b.*, p.* from ${table} b right join ${table1} p on b.city =p.city";
	&ExecDisplay($s,$title);
}

sub Query30
{
	local($table,$title);
	local($s);
	$table="branch"; $table1="property_for_rent"; $title="30. Full outer join does not seem to work";
	$s=""; $s=$s."select b.*, p.* from ${table} b full join ${table1} p on b.city =p.city";
	&ExecDisplay($s,$title);
}

sub Query31
{
	local($table,$title);
	local($s);
	$table="staff1"; $table1="branch"; $title="31. Exists does not seem to work";
	$s=""; $s=$s."select sno,fname,lname,position from ${table} s where exists (select * from ${table1} b where s.bno=b.bno and city='london')";
	&ExecDisplay($s,$title);
}

sub Query32
{
	local($table,$title);
	local($s);
	$table="branch"; $table1="property_for_rent"; $title="32. Union does not seem to work";
	$s=""; $s=$s."(select area from ${table} where area is not null) union (select area from ${table1} where area is not null)";
	&ExecDisplay($s,$title);
}

sub Query33
{
	local($table,$title);
	local($s);
	$table="branch"; $table1="property_for_rent"; $title="33. Intersect does not seem to work";
	$s=""; $s=$s."(select area from ${table} where area is not null) intersect (select area from ${table1} where area is not null)";
	&ExecDisplay($s,$title);
}

sub Query34
{
	local($table,$title);
	local($s);
	$table="branch"; $table1="property_for_rent"; $title="34. Except does not seem to work";
	$s=""; $s=$s."(select area from ${table} where area is not null) except (select area from ${table1} where area is not null)";
	&ExecDisplay($s,$title);
}

sub Query35
{
	local($table,$title);
	local($s);
}

sub Query36
{
	local($table,$title);
	local($s);
}

sub Query37
{
	local($table,$title);
	local($s);
}

sub Query38
{
	local($table,$title);
	local($s);
}

sub Query39
{
	local($table,$title);
	local($s);
}

sub Backup
{
	local($table)=@_;
	local($s);
	$s=$s."backup table ${table} to '${path}';";
#	$s=$s."restore table ${table} from '${path}';";
	&Execute($s);
	$s=$s."<br>";
	$s;
}