

<? #!/usr/bin/perl -w
use CGI;
use DBI;
use Geo::IP;
use MIME::Lite;
use Net::SMTP;
use Crypt::RC4;
use MIME::Base64;
use CGI::Simple;
use URI::Escape;
use Time::Piece;


my $q = new CGI;
use lib "/home3/indiamart/modules/globalfiles";
use Globalinit;

my $url;
my $host=$ENV{'HTTP_HOST'};
my $flag=0;
my $TBL_LEAD_ID = $q->param('TBL_LEAD_ID') || '';
my $name = $q->param('TBL_LEAD_NAME') || '';
$name =~ s/'/\\'/g;
my $item_theme2 ='';
my $destination2='';
my $interested_country = $q->param('interested_country') || '';
my $lead_type_id = $q->param('form_typeid') || '';
if($interested_country eq 'India')
{
	$lead_type_id1='1';
}
else
{
	$lead_type_id1=$lead_type_id;
}

	
my $email = $q->param('TBL_LEAD_EMAIL') || '';

my $key ='hello';
my $output = encode_base64( RC4($key, $email) );
my $decoded = decode_base64($output);
my $decrypted = RC4($key, $decoded);
my $a =uri_escape($output);
# my $encodeString = ~s/([^A-Za-z0-9_\-.]/uc
# sprintf("%%%02x",ord($output))/eg;

my @country=split("_",$q->param('FK_TBL_COUNTRY'));
my $country1 = $country[0] || '';
my $country2 = $country[2] || '';	

my $city = $q->param('TBL_LEAD_CITY') || '';
my $phone = $q->param('TBL_LEAD_PHONE') || '';
my $af_id= $q->param('form_afid') || '';

my @people_adults1=split("_",$q->param('FK_TBL_PEOPLE_ADULT_ID'));
my $people_adults2 = $people_adults1[0] || '';
my $people_adults = $people_adults1[1] || '';

my @people_kids1=split("_",$q->param('FK_TBL_PEOPLE_KID_ID'));
my $people_kids2 = $people_kids1[0] || '';
my $people_kids = $people_kids1[1] || '';

my $arrival_date = $q->param('TBL_LEAD_ARRIVAL_DATE') || '';
my $arrival_month = $q->param('TBL_LEAD_ARRIVAL_MONTH') || '';
my $arrival_year = $q->param('TBL_LEAD_ARRIVAL_YEAR') || '';

my @duration_id1=split("_",$q->param('FK_TBL_DURATION_ID'));
my $duration_id2 = $duration_id1[0] || '';
my $duration_id = $duration_id1[1] || '';

my @tbl_budget_id1=split("_",$q->param('FK_TBL_BUDGET'));
my $tbl_budget_id2 = $tbl_budget_id1[0] || '';
my $tbl_budget_id = $tbl_budget_id1[1] || '';

my @tbl_visittype1=split("_",$q->param('tbl_visittype'));
my $tbl_visittype2 = $tbl_visittype1[2] || '';
my $visittype= $q->param('tbl_visittype') || '';

my @theme = $q->param('tbl_theme[]') ;
foreach my $key(@theme)
		{
			my @item;
			@item=split("_",$key);
			$item_theme2 = "$item_theme2," .$item[2];
			$item_theme2=~s/^\,//g;
		}
		
		
my @destination=$q->param('tbl_dest[]') ;
foreach my $key1(@destination)
		{
			my @destination1;
			@destination1=split("_",$key1);
			$destination2 = "$destination2," .$destination1[2];
			$destination2=~s/^\,//g;
		}

my $description1 = $q->param('TBL_LEAD_DESCRIPTION') || '';

if($interested_country eq 'India' && $lead_type_id eq '5')
{
 $description = "$description1";
 $description =~ s/'/\\'/g;
}
elsif($lead_type_id ne '5')
{
$description = "$description1";
 $description =~ s/'/\\'/g;
}
else
{
 $description = "Interested In : $interested_country, $description1 ";
 $description =~ s/'/\\'/g;
 }

 my $ip = $q->remote_addr() || '';

my $site_name= $q->param('site_name') || '';

my $geoip = Geo::IP->new(GEOIP_STANDARD);
my $ip_country_name = $geoip->country_name_by_addr($ip) || '';
$ip_country_name = "NA" unless($ip_country_name);
	
my $gi = Geo::IP->open_type( GEOIP_CITY_EDITION_REV1 , GEOIP_STANDARD );
my $record = $gi->record_by_addr($ip);
my $ip_city_name= $record->city || '';
$ip_city_name = "NA" unless($ip_city_name);

my ($Second, $Minute, $Hour, $Day, $Month, $Year, $WeekDay, $DayOfYear, $IsDST) = gmtime(time);
$Year += 1900;
$Month++;
my $t = gmtime;
 

if($Month < '10')
{
	$Month="0$Month";
}
else
{
	$Month="$Month";
}
if($Day < '10')
{
	$Day="0$Day";
}
else
{
	$Day=$Day;
}
if($Hour < '10')
{
	$Hour="0$Hour";
}
else
{
	$Hour=$Hour;
}
if($Second < '10')
{
	$Second="0$Second";
}
else
{
	$Second=$Second;
}
my $date =  $Year.'-'.$Month.'-'.$Day || '';
my $time = $Hour.':'.$Minute.':'.$Second || '';
my $date1="$arrival_date/$arrival_month/$arrival_year";
my $datetime="$date $time";

my $ref= $q->param('referer') || '';
	if($ref =~ /googleads/ig)
	{
		$ref='';
	}
	else
	{
		$ref=$ref;
	}

	my $refferer1 = $ENV{'HTTP_REFERER'} || '';
	my @refferer2;
        @refferer2 = split(/(\?gclid)/,$refferer1);
 	my $refferer3 = $refferer2[0] || '';

# create a new MIME Lite based email

if($ip_country_name ne 'India')
{
my $msg = MIME::Lite->new
(
Subject => "Re: Your enquiry on $site_name about your travel plan",
From    => 'Hello Travel <helpdesk@hellotravel.com>',
To      => $email,
Type    => 'text/html',
Data    => "Dear $name,
<BR><BR>

Thank you for using <B>$site_name</B> and opting HELLOTRAVEL.COM network to plan your Travel.<BR><BR>

We have successfully received your enquiry. We request you to please verify your email enquiry by clicking on <A HREF=http://hellotravel-agents/verify.php?id=$a>http://hellotravel-agents/verify.php?id=$a</A> and help us assign you specialist travel agent to customize and plan on your travel needs.<BR><BR>

HELLO TRAVEL is a platform that allows travelers plan & book tour free of charge.<BR><BR>

For any query or inconvenience please write to us at helpdesk\@hellotravel.com.<BR><BR>

With Regards,<BR>
HELLO TRAVEL Team!"
);

$msg->send();
}
else
{
my $msg = MIME::Lite->new
(
Subject => "Re: Your enquiry on $site_name about your travel plan",
From    => 'Hello Travel <helpdesk@hellotravel.com>',
To      => $email,
Type    => 'text/html',
Data    => "Dear $name,
<BR><BR>

Thank you for using <B>$site_name</B> and opting HELLOTRAVEL.COM network to 
plan your Travel.<BR><BR>

We have successfully received your enquiry and request you to please 
verify, by clicking on <A HREF=http://hellotravel-agents/verify.php?id=$a>http://hellotravel-agents/verify.php?id=$a</A>
and help us assign you specialist travel agent to customize and plan on 
your travel needs.<BR><BR>

HELLO TRAVEL is a platform that allows travelers plan tour free of charge 
with freedom of maximum customization.<BR><BR>

With lots of thanks and to make your experience of using HELLO TRAVEL more 
special, we provide you add-on discount coupon of flat 10% on every 
purchase of exotic gifts for occasions like Birthday, Anniversary, Wedding 
& many more.. Click on <B><A HREF=http://www.indiangiftsportal.com>http://www.indiangiftsportal.com</A></B> and explore through the 
categories of Gifts for her/him & kids.<BR><BR>

Discount coupon code : <B>IGPHT</B><BR>
Enjoy Shopping on <A HREF=http://www.indiangiftsportal.com>http://www.indiangiftsportal.com</A><BR><BR>

For any query or inconvenience please write to us at <a href=helpdesk\@hellotravel.com>helpdesk\@hellotravel.com</a>.<BR><BR>

With Regards,<BR>
HELLO TRAVEL Team! "
);

$msg->send();
}


my $to1 = 'mayank@hellotravel.com,pratibha@indiamart.com';
my $from1 = 'error@hellotravel.com';
my $sub1 = 'hellotravel.com Enquiry';
my $message1 = "Name - ".$name."
Email -".$email."
Country - ".$country2."
City - ".$city."
Phone No. - ".$phone."
Arrival Date - ".$date1."
No. of Adult People - ".$people_adults."
No. of Kids - ".$people_kids."
Duration - ".$duration_id."
Budget - ".$tbl_budget_id."
Tour Style - ".$tbl_visittype2."
Activities - ".$item_theme2."
Destinations - ".$destination2."
Description - ".$description."
IP - ".$ip."
IP Country Name - ".$ip_country_name."
IP City Name - ".$ip_city_name."
Site Name - ".$site_name."
Query Date - ".$date."
Query Time - ".$time."
Selfurl - ".$refferer3."
Referer - ".$ref."
Thanks";

Globalinit->SendMail(__FILE__,__LINE__,$to1,$from1,'',$sub1,$message1,'');
			
if ($ENV{'MY_COUNTRY'} eq 'INDIA')
{
	$dbh = DBI->connect("DBI:mysql:hellotravel:mysql3.intermesh.net", "igpmysql", "IGp142",{PrintError => 1, RaiseError => 0, AutoCommit => 1,});
	
}
else
{
	$dbh = DBI->connect("DBI:mysql:hellotravel:web.hellotrade.com", 'travel', 'thell@123',{PrintError => 1, RaiseError => 0, AutoCommit => 1,});
	
}
	if(!$dbh)
	{
		if($q->param('submit') && $q->param('submit') ne '')
		{
			my $to2 = 'mayank@hellotravel.com,pratibha@indiamart.com';
			my $from2 = 'error@hellotravel.com';
			my $sub2 = "DB Error- $name,$email,$country2 ";
			my $message2 = "Could not connect & submit enquiry to database
			Name - $name
			Email -$email
			Country Code - $country2";
			Globalinit->SendMail(__FILE__,__LINE__,$to2,$from2,'',$sub2,$message2,'');

			if($q->param('MYREDIRECT'))
			{	
				my $redirect;
				$redirect = $q->param('MYREDIRECT');
				#print "Location: http://$host/$redirect";
				print $q->header( -location => "$redirect", -status => "302" );
				exit;
			}
			else
			{
				print $q->header( -location => "http://www.hellotravel.com/serveform/serveform.pl?action=redirect", -status => "302" );
				$incfile='';
				exit;
			}
			
		}
	}

if($q->param('submit') && $q->param('submit') ne '')
{
# 	print "Content-type:text/html\n\n";
# 	print "$country1";
# 	exit;
		my $tbl_site_name=$dbh->prepare("SELECT TBL_WEBSITE_URL FROM tbl_websites WHERE TBL_WEBSITE_URL = '$site_name' ");
		$tbl_site_name->execute();
		my $tbl_site_name2=$tbl_site_name->fetchrow_hashref();
		my $tbl_site_name3=$tbl_site_name2->{'TBL_WEBSITE_URL'};

		if(!$tbl_site_name3)
		{
			my $to2 = 'mayank@hellotravel.com,pratibha@indiamart.com';
			my $from2 = 'error@hellotravel.com';
			my $sub2 = "DB Error- $name,$email,$country2 ";
			my $message2 = "Could not connect & submit enquiry to database
			Name - $name
			Email -$email
			Country Code - $country2";
			Globalinit->SendMail(__FILE__,__LINE__,$to2,$from2,'',$sub2,$message2,'');

			if($q->param('MYREDIRECT'))
			{	
				my $redirect;
				$redirect = $q->param('MYREDIRECT');
				#print "Location: http://$host/$redirect";
				print $q->header( -location => "$redirect", -status => "302" );
				exit;
			}
			else
			{
				print $q->header( -location => "http://www.hellotravel.com/serveform/serveform.pl?action=redirect", -status => "302" );
				$incfile='';
				exit;
			}	
		}	
		
		my $website_language_id=$dbh->prepare("SELECT FK_LANGUAGEID FROM tbl_websites WHERE TBL_WEBSITE_URL='$site_name'");
		$website_language_id->execute();
		my $website_language_id1=$website_language_id->fetchrow_hashref();
		my $website_language_id2=$website_language_id1->{'FK_LANGUAGEID'};

	
	my $date_time=$dbh->prepare("select now()");
	$date_time->execute();
	my $date_time1=$date_time->fetchrow_hashref();
	$date_time2=$date_time1->{'now()'};

	
	my $no_adults=$dbh->prepare("SELECT TBL_PEOPLE_ADULT_VALUE from tbl_people_adults where TBL_PEOPLE_ADULT_ID=$people_adults ");
	$no_adults->execute();
	my $no_adults1=$no_adults->fetchrow_hashref();
	my $value_adult=$no_adults1->{'TBL_PEOPLE_ADULT_VALUE'};


	 if($description =~ /<a href=/ig || $phone =~ /123456/ig || $phone=~ /[A-Za-z]/ig || $phone eq '' || $country2 =~ /0/ig )
	 
	{
		
		my $date_time=$dbh->prepare("select now()");
		$date_time->execute();
		my $date_time1=$date_time->fetchrow_hashref();
		$date_time2=$date_time1->{'now()'};
		
		my $to3 = 'mayank@hellotravel.com,pratibha@indiamart.com';
		my $from3 = 'error@hellotravel.com';
		my $sub3 = "DB Error- $name,$email,$country2 ";
		my $message3 = "Could not connect & submit Junk enquiry to database
		Name - $name
		Email -$email
		Country Code - $country2";
			
		my $sth = $dbh->prepare("INSERT INTO tbl_leads_junk (TBL_LEAD_ID,TBL_LEAD_NAME,TBL_LEAD_EMAIL,FK_TBL_COUNTRY_ISO_CODE,TBL_LEAD_CITY,FK_TBL_LANGUAGE_ID,TBL_LEAD_PHONE, FK_TBL_LEADTYPE_ID,FK_TBL_PEOPLE_ADULT_ID,FK_TBL_PEOPLE_KID_ID,FK_TBL_DURATION_ID,TBL_LEAD_ARRIVAL_DATE,TBL_LEAD_ARRIVAL_MONTH,TBL_LEAD_ARRIVAL_YEAR,TBL_LEAD_DESCRIPTION,TBL_LEAD_IP,TBL_LEAD_IP_COUNTRY,TBL_LEAD_IP_CITY,FK_TBL_WEBSITE_URL,TBL_LEAD_REFERER,TBL_LEAD_RECD_DATE_TIME,TBL_LEAD_DATE_TIME,TBL_LEAD_TIME,TBL_LEAD_SELFURL,FK_TBL_LOGIN_AFFILIATE_ID) VALUES ('$TBL_LEAD_ID', '$name','$email','$country1','$city','$website_language_id2','$phone','$lead_type_id1','$people_adults2','$people_kids2','$duration_id2','$arrival_date','$arrival_month','$arrival_year','$description','$ip','$ip_country_name','$ip_city_name','$site_name','$ref','$datetime','$datetime','$time','$refferer3','$af_id')");
		$sth->execute() || Globalinit->SendMail(__FILE__,__LINE__,$to3,$from3,'',$sub3,$message3,'');
		$sth->finish;
	
		my $maxID=$dbh->prepare("SELECT max(TBL_LEAD_ID) as maxID FROM tbl_leads_junk");
		$maxID->execute();
		my $maxID1=$maxID->fetchrow_hashref();
	
		my $id=$maxID1->{'maxID'};
		
		my $tbl_country_query=$dbh->prepare("SELECT TBL_COUNTRY FROM tbl_country ,tbl_leads_junk WHERE tbl_leads_junk. FK_TBL_COUNTRY_ISO_CODE = tbl_country. TBL_COUNTRY_ISO_CODE and TBL_LEAD_ID=$id;");
		$tbl_country_query->execute();
		my $tbl_country_query2=$tbl_country_query->fetchrow_hashref();

		my $tbl_country_query_value=$tbl_country_query2->{'TBL_COUNTRY'};

		if($tbl_country_query_value)
		{
		
			my $sth = $dbh->prepare("update tbl_leads_junk set FK_TBL_COUNTRY = '$tbl_country_query_value' where TBL_LEAD_ID=$id");
			$sth->execute();
			$sth->finish;

		}
		else
		{
			print "some error has occured";
			exit;
		}


		
		my $query_budget1=$dbh->prepare("SELECT TBL_BUDGET FROM tbl_budget where TBL_BUDGET_ID = $tbl_budget_id2");
		$query_budget1->execute();
		my $query_budget2=$query_budget1->fetchrow_hashref();
		my $value_budget=$query_budget2->{'TBL_BUDGET'};
		
	
		if($value_budget)
		{
		
		my $sth = $dbh->prepare("update tbl_leads_junk set FK_TBL_BUDGET = '$value_budget' where TBL_LEAD_ID=$id");
		$sth->execute();
		$sth->finish;
		}
		else
		{
			print "some error has occured";
			exit;
		}

		my $TBL_WEBSITE_ID1=$dbh->prepare("SELECT TBL_WEBSITE_ID FROM tbl_websites,tbl_leads_junk WHERE tbl_leads_junk.FK_TBL_WEBSITE_URL = tbl_websites.TBL_WEBSITE_URL and TBL_LEAD_ID=$id");
		$TBL_WEBSITE_ID1->execute();
		my $TBL_WEBSITE_ID2=$TBL_WEBSITE_ID1->fetchrow_hashref();
	
		my $TBL_WEBSITE_ID_VALUE=$TBL_WEBSITE_ID2->{'TBL_WEBSITE_ID'};
		if($TBL_WEBSITE_ID_VALUE eq '0')
		{
	
			print "some error has occured";
			exit;
		}
		if($TBL_WEBSITE_ID_VALUE)
		{
	
		my $sth = $dbh->prepare("update tbl_leads_junk set FK_TBL_WEBSITE_ID = '$TBL_WEBSITE_ID_VALUE' where TBL_LEAD_ID=$id");
		$sth->execute();
		$sth->finish;
	
		}
		else
		{
			print "some error has occured";
			exit;
		}

		$query1=$dbh->prepare("SELECT  TBL_DURATION  FROM tbl_duration,tbl_leads_junk WHERE tbl_leads_junk.FK_TBL_DURATION_ID = tbl_duration. TBL_DURATION_ID  and TBL_LEAD_ID=$id;");
		$query1->execute();
	 	$query2=$query1->fetchrow_hashref();

	 	$value=$query2->{'TBL_DURATION'};

		if($value)
		{
	
			my $sth = $dbh->prepare("update tbl_leads_junk set FK_TBL_DURATION = '$value' where TBL_LEAD_ID=$id");
			$sth->execute();
			$sth->finish;
		}
		else
		{
			print "some error has occured";
			exit;
		}
		@item4=split("_",$visittype);
	
	 	$sth = $dbh->prepare("insert into tbl_leadrelations_junk(FK_GENERAL_ID,FK_LEAD_ID,TBL_LEADRELATION_VALUE) values('$item4[0]','$id','$item4[1]')");
		$sth->execute();
		$sth->finish;
	

		foreach my $key(@theme)
		{
			my @item1;
			@item1=split("_",$key);
	
			my $sth = $dbh->prepare("insert into tbl_leadrelations_junk(FK_GENERAL_ID,FK_LEAD_ID,TBL_LEADRELATION_VALUE) values('$item1[0]','$id','$item1[1]')");
			$sth->execute();
			$sth->finish;
		}
	
		foreach my $key(@destination)
		{
			my @item3;
			@item3=split("_",$key);

			my $sth = $dbh->prepare("insert into tbl_leadrelations_junk(FK_GENERAL_ID,FK_LEAD_ID,TBL_LEADRELATION_VALUE) values('$item3[0]','$id','$item3[1]')");
			$sth->execute();
			$sth->finish;
		}


	}
	else
	{
		
		my $to3 = 'mayank@hellotravel.com,pratibha@indiamart.com';
		my $from3 = 'error@hellotravel.com';
		my $sub3 = "DB Error- $name,$email,$country2 ";
		my $message3 = "Could not connect & submit enquiry to database
		Name - $name
		Email -$email
		Country Code - $country2";
		
		my $sth = $dbh->prepare("INSERT INTO tbl_leads (TBL_LEAD_ID,TBL_LEAD_NAME,TBL_LEAD_EMAIL,FK_TBL_COUNTRY_ISO_CODE,TBL_LEAD_CITY,FK_TBL_LANGUAGE_ID,TBL_LEAD_PHONE,FK_TBL_LEADTYPE_ID,FK_TBL_PEOPLE_ADULT_ID,FK_TBL_PEOPLE_KID_ID,FK_TBL_DURATION_ID,TBL_LEAD_ARRIVAL_DATE,TBL_LEAD_ARRIVAL_MONTH,TBL_LEAD_ARRIVAL_YEAR,TBL_LEAD_DESCRIPTION,TBL_LEAD_IP,TBL_LEAD_IP_COUNTRY,TBL_LEAD_IP_CITY,FK_TBL_WEBSITE_URL,TBL_LEAD_REFERER,TBL_LEAD_RECD_DATE_TIME,TBL_LEAD_DATE_TIME,TBL_LEAD_TIME,TBL_LEAD_SELFURL,FK_TBL_LOGIN_AFFILIATE_ID) VALUES ('$TBL_LEAD_ID', '$name','$email','$country1','$city','$website_language_id2','$phone','$lead_type_id1','$people_adults2','$people_kids2','$duration_id2','$arrival_date','$arrival_month','$arrival_year','$description','$ip','$ip_country_name','$ip_city_name','$site_name','$ref','$datetime','$datetime','$time','$refferer3','$af_id')");
		$sth->execute() || Globalinit->SendMail(__FILE__,__LINE__,$to3,$from3,'',$sub3,$message3,'');
		$sth->finish;
		
		
		my $maxID=$dbh->prepare("SELECT max(TBL_LEAD_ID) as maxID FROM tbl_leads");
		$maxID->execute();
		my $maxID1=$maxID->fetchrow_hashref();
		my $id=$maxID1->{'maxID'};
	
		my $tbl_country_query=$dbh->prepare("SELECT TBL_COUNTRY FROM tbl_country ,tbl_leads WHERE tbl_leads. FK_TBL_COUNTRY_ISO_CODE = tbl_country. TBL_COUNTRY_ISO_CODE and TBL_LEAD_ID=$id;");
		$tbl_country_query->execute();
		my $tbl_country_query2=$tbl_country_query->fetchrow_hashref();
	
		my $tbl_country_query_value=$tbl_country_query2->{'TBL_COUNTRY'};
	
		if($tbl_country_query_value)
		{
		
		my $sth = $dbh->prepare("update tbl_leads set FK_TBL_COUNTRY = '$tbl_country_query_value' where TBL_LEAD_ID=$id");
		$sth->execute();
		$sth->finish;
	
		}
		else
		{
			print "some error has occured";
			exit;
		}

		
		my $query_budget1=$dbh->prepare("SELECT TBL_BUDGET FROM tbl_budget where TBL_BUDGET_ID = $tbl_budget_id2");
		$query_budget1->execute();
		my $query_budget2=$query_budget1->fetchrow_hashref();
		my $value_budget=$query_budget2->{'TBL_BUDGET'};
		
	
		if($value_budget)
		{
		
		my $sth = $dbh->prepare("update tbl_leads set FK_TBL_BUDGET = '$value_budget' where TBL_LEAD_ID=$id");
		$sth->execute();
		$sth->finish;
		}
		else
		{
			print "some error has occured";
			exit;
		}
	
		my $TBL_WEBSITE_ID1=$dbh->prepare("SELECT TBL_WEBSITE_ID FROM tbl_websites,tbl_leads WHERE tbl_leads.FK_TBL_WEBSITE_URL = tbl_websites.TBL_WEBSITE_URL and TBL_LEAD_ID=$id");
		$TBL_WEBSITE_ID1->execute();
		my $TBL_WEBSITE_ID2=$TBL_WEBSITE_ID1->fetchrow_hashref();
	
		my $TBL_WEBSITE_ID_VALUE=$TBL_WEBSITE_ID2->{'TBL_WEBSITE_ID'};
		if($TBL_WEBSITE_ID_VALUE eq '0')
		{
	
			print "some error has occured";
			exit;
		}
		if($TBL_WEBSITE_ID_VALUE)
		{
	
		my $sth = $dbh->prepare("update tbl_leads set FK_TBL_WEBSITE_ID = '$TBL_WEBSITE_ID_VALUE' where TBL_LEAD_ID=$id");
		$sth->execute();
		$sth->finish;
	
		}
		else
		{
			print "some error has occured";
			exit;
		}
	
		$query1=$dbh->prepare("SELECT  TBL_DURATION  FROM tbl_duration,tbl_leads WHERE tbl_leads.FK_TBL_DURATION_ID = tbl_duration. TBL_DURATION_ID  and TBL_LEAD_ID=$id;");
		$query1->execute();
		$query2=$query1->fetchrow_hashref();
	
		$value=$query2->{'TBL_DURATION'};
	
		if($value)
		{
		
		my $sth = $dbh->prepare("update tbl_leads set FK_TBL_DURATION = '$value' where TBL_LEAD_ID=$id");
		$sth->execute();
		$sth->finish;
		}
		else
		{
			print "some error has occured";
			exit;
		}
	
		@item4=split("_",$visittype);
		
		$sth = $dbh->prepare("insert into tbl_leadrelations(FK_GENERAL_ID,FK_LEAD_ID,TBL_LEADRELATION_VALUE) values('$item4[0]','$id','$item4[1]')");
			$sth->execute();
			$sth->finish;
		
	
		foreach my $key(@theme)
		{
			my @item1;
			@item1=split("_",$key);
		
			my $sth = $dbh->prepare("insert into tbl_leadrelations(FK_GENERAL_ID,FK_LEAD_ID,TBL_LEADRELATION_VALUE) values('$item1[0]','$id','$item1[1]')");
			$sth->execute();
			$sth->finish;
		}
		
		foreach my $key(@destination)
		{
			my @item3;
			@item3=split("_",$key);
	
			my $sth = $dbh->prepare("insert into tbl_leadrelations(FK_GENERAL_ID,FK_LEAD_ID,TBL_LEADRELATION_VALUE) values('$item3[0]','$id','$item3[1]')");
			$sth->execute();
			$sth->finish;
		}
		
	}
		
		
	if($q->param('MYREDIRECT'))
	{	
		my $redirect;
		$redirect = $q->param('MYREDIRECT');
		#print "Location: http://$host/$redirect";
		print $q->header( -location => "$redirect", -status => "302" );
		exit;
	}
	else
	{
		print $q->header( -location => "http://www.hellotravel.com/serveform/serveform.pl?action=redirect", -status => "302" );
		$incfile='';
		exit;
	}
	
	}

if($q->param('action') && $q->param('action') eq 'redirect')
{
	$incfile='';
print "Content-type: text/html \n\n";


print qq~
<HTML>
 <HEAD>
  <TITLE> New Document </TITLE>
  <META NAME="Generator" CONTENT="EditPlus">
  <META NAME="Author" CONTENT="">
  <META NAME="Keywords" CONTENT="">
  <META NAME="Description" CONTENT="">
 </HEAD>
 <BODY>
<B> your query has ben submitted</B>
 </BODY>
</HTML>~;
}