#!/usr/bin/perl -w
package Net4DBLib;
use 5.004;
use strict;      # Restrict unsafe variables, references, barewords
use Carp;
use DBI;
use Net4Base;
use vars qw(@ISA @EXPORT @EXPORT_OK $Version);
@ISA = qw(Exporter);
@EXPORT = qw();
@EXPORT_OK = qw(1);
$Net4DBLib::DBUsername='root';
$Net4DBLib::DBPassword='1ntel';
$Net4DBLib::DBHostname='localhost';
$Net4DBLib::DBDBName='FREEDOM';
##########################################################################
# Insert a backslash character before ' & \
sub escapeStrings
{
	my ($str) = @_;
	$str =~ s/\\/\\\\/g;
	$str =~ s/\'/\\\'/g;
	return $str;
}
##########################################################################
## Make database connection.
sub connectDB
{
	my $dbh= DBI->connect("DBI:mysql:$Net4DBLib::DBDBName:$Net4DBLib::DBHostname", $Net4DBLib::DBUsername,$Net4DBLib::DBPassword,{RaiseError=>1,AutoCommit=>1});
	unless ($dbh)
	{
		Net4Base::LogError("Error : Unable to extract DBHandle from the DBH Pool.");
	}
	return $dbh;
}
##########################################################################
##Remove database connection.
sub destroyDB
{
	my $dbh=$_[0];
	if ($dbh)
	{
		$dbh->disconnect();
	}
}
##########################################################################
sub CreateTable
{
	my $ver = $_[0];
	my $query = $_[1];
	my $returnquery = "CREATE TABLE ";
	$returnquery .= ($ver <= 3.22) ? "$query" : "IF NOT EXISTS $query " ;
	return $returnquery;
}
##THIS FUNCTION IS TO CREATE TABLE Guest Book
sub CreateFreedomQuestTable
{
	# Define local database handle
	my ($dbh) = @_;
	my $ver = $Net4Constants::MySQLVersion;
	my $query = " FreedomQuest (fqID INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, email VARCHAR(50) NOT NULL , name VARCHAR(50) NOT NULL , address varchar(200) , city VARCHAR(30) , dob datetime , marital_status varchar(20) , education varchar(30) , occupation VARCHAR(30), vehicle text , isp text , i_when_from varchar(50) , i_where_from varchar(50) , i_frequency varchar(50) , i_duration varchar(50) , sites text , surf_what text, i_interests text , ccard varchar(30) , ftv text , newspaper text  ) ";
	my $finalquery = CreateTable($ver,$query);
	my $result=1;
	if ($dbh)
	{
		unless ($dbh->do("$finalquery"))
		{
			Net4Base::LogError("Error in creating Freedom Quest Table");
			$result=0;
		}
	}
	else
	{
		$result=0;
	}
	return $result;
}
sub AddToFreedomQuest
{
	my($dbh,%hash) = @_;
	my $ID = 0;
	my ($email,$name,$address,$city,$dob,$marital_status,$education,$occupation,$vehicle,$isp,$i_when_from,$i_where_from,$i_frequency,$i_duration,$sites,$surf_what,$i_interests,$ccard,$ftv,$newspaper);
	$email = escapeStrings($hash{'email'});
	$name = escapeStrings($hash{'name'});
	$address = escapeStrings($hash{'address'});
	$city = escapeStrings($hash{'city'});
	$dob = escapeStrings($hash{'dob'});
	$marital_status = escapeStrings($hash{'marital_status'});
	$education = escapeStrings($hash{'education'});
	$occupation = escapeStrings($hash{'occupation'});
	$vehicle = escapeStrings($hash{'vehicle'});
	$isp = escapeStrings($hash{'isp'});
	$i_when_from = escapeStrings($hash{'i_when_from'});
	$i_where_from = escapeStrings($hash{'i_where_from'});
	$i_frequency = escapeStrings($hash{'i_frequency'});
	$i_duration = escapeStrings($hash{'i_duration'});
	$sites = escapeStrings($hash{'sites'});
	$surf_what = escapeStrings($hash{'surf_what'});
	$i_interests = escapeStrings($hash{'i_interest'});
	$ccard = escapeStrings($hash{'ccard'});
	$ftv = escapeStrings($hash{'ftv'});
	$newspaper = escapeStrings($hash{'newspaper'});
	my $query = "INSERT INTO FreedomQuest(email,name,address,city,dob,marital_status,education,occupation,vehicle,isp,i_when_from,i_where_from,i_frequency,i_duration,sites,surf_what,i_interests,ccard,ftv,newspaper) VALUES('$email','$name','$address','$city','$dob','$marital_status','$education','$occupation','$vehicle','$isp','$i_when_from','$i_where_from','$i_frequency','$i_duration','$sites','$surf_what','$i_interests','$ccard','$ftv','$newspaper')";
	if($dbh)
	{
		my $sth = $dbh->prepare("$query");
		$sth->execute or return 0;
		$ID = $sth->{'mysql_insertid'};
	}
	return ($ID);
}