#!/usr/bin/perl
use CGI qw/:all/;
use DBI;

#$DATFILE="/home/websites/arabdemog_com/tree/mmua03c.seq";
$dbh=DBI->connect('dbi:mysql:arabdemog_com:arabiandemographics.com','arabdemog_com','p2a9r7cd')|| die('unable to connect to database');

@files=`ls /home/websites/arabdemog_com/tree/seq/*.[sS][eE][qQ]`;

foreach $DATFILE (@files) 
{ 
	$fname=$DATFILE;

	my($directory, $filename) = $fname =~ m/(.*\/)(.*)$/;
	if (uc(substr($filename,2,2)) eq 'UA') 
	{
		$cntrycd='05';
	}
	elsif (uc(substr($filename,2,2)) eq 'PA') 
	{
		$cntrycd='08';
	}
	if (uc(substr($filename,2,2)) eq 'QA') 
	{
		$cntrycd='03';
	}
	elsif (uc(substr($filename,2,2)) eq 'KU') 
	{
		$cntrycd='01';
	}

	elsif (uc(substr($filename,2,2)) eq 'JO') 
	{
		$cntrycd='07';
	}
	elsif (uc(substr($filename,2,2)) eq 'SA') 
	{
		$cntrycd='02';
	}
	if (uc(substr($filename,2,2)) eq 'EG') 
	{
		$cntrycd='10';
	}
	elsif (uc(substr($filename,2,2)) eq 'LE') 
	{
		$cntrycd='06';
	}
	if (uc(substr($filename,2,2)) eq 'BA') 
	{
		$cntrycd='04';
	}
	if (uc(substr($filename,2,2)) eq 'OM') 
	{
		$cntrycd='09';
	}
	elsif (uc(substr($filename,2,2)) eq 'QA') 
	{
		$cntrycd='03';
	}
	elsif (uc(substr($filename,2,2)) eq 'SY') 
	{
		$cntrycd='12';
	}
	elsif (uc(substr($filename,2,2)) eq 'IQ') 
	{
		$cntrycd='17';
	}
print "datfile=$DATFILE\n";
open(DATFILE) or die("Could not open log file.");
foreach $line (<DATFILE>) {
    chomp($line);              # remove the newline from $line.
	#print "$line\n";
 $key=$cntrycd.substr($line,0,10);
 $desc=substr($line,10,16);

 $prc1=substr($line,27,8);
 $prc2=substr($line,35,8);
 $prc3=substr($line,43,8);
 $prc4=substr($line,51,8);

 $per1=substr($line,59,3);
 $per2=substr($line,62,3);

 $drate=substr($line,79,1);

  $sql="insert into tbl_medprc(medkey,medposdesc,prc1,prc2,";
  $sql=$sql."prc3,prc4,per1,per2,drate) ";
  $sql=$sql."values(\'$key\',\'".trimwhitespace($desc)."\',\'$prc1\',\'$prc2\',\'$prc3\',\'$prc4\',\'$per1\',\'$per2\',\'$drate\')";

#print "$sql\n\n";
#exit(-1);
#$sql = $dbh->quote($sql);
$sthE=$dbh->prepare($sql)|| print('unable to prepare $sql  $dbh->errstr');
#$rv=$sthE->execute || print("Record Already Entered   $sql $sthE->errstr");
$rv=$sthE->execute ;


}
close(DATFILE);
print "$i\n"; 
}
$dbh->disconnect;

sub trimwhitespace($)
{
	my $string = shift;
	$string =~ s/^\s+//;
	$string =~ s/\'//g;
	$string =~ s/\,//g;
	$string =~ s/\s+$//;
	#$string =~ s/\([^\])/$1/g; 
	return $string;
}