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

$VARFILE = "/home/websites/arabdemog_com/mystic5/axiom.var";
$DATFILE="/home/websites/arabdemog_com/mystic5/axiom.dat";

#$VARFILE="/home/umar/mystic4/mystic5/axiom.var";
#$DATFILE="/home/umar/mystic4/mystic5/axiom.dat";

$dbh=DBI->connect('dbi:mysql:arabdemog_com:arabiandemographics.com','arabdemog_com','p2a9r7cd')|| die('unable to connect to database');

open(VARFILE) or die("Could not open log file.");
foreach $line (<VARFILE>) {
    chomp($line);              # remove the newline from $line.

	if($line =~ m/(Serial)/)
	{
		#print "\nSERIALLINE=$line\n";
		($x,$y,$serial)=split(/ /,$line,3);
		($serial,$y)=split(/:/,$serial,2);
		($serpos,$serlen)=split(/L/,$serial);
	}

	if($line =~ m/(QUESTION 904)/)
	{
		($x,$y,$store_cd)=split(/ /,$line,3);
		($store_cd,$y)=split(/:/,$store_cd,2);
		($store_cdpos,$store_cdlen)=split(/L/,$store_cd);
	}

	if($line =~ m/(TIMING)/)
        {
                ($x,$y,$time)=split(/ /,$line,3);
                ($time,$y)=split(/:/,$time,2);
                ($timepos,$timelen)=split(/L/,$time);
        }

	if($line =~ m/(NATIONALITY)/)
	{
		($x,$y,$nationality)=split(/ /,$line,3);
		($nationality,$y)=split(/:/,$nationality,2);
		($nationpos,$nationlen)=split(/L/,$nationality);
	}


		if($line =~ m/(Branch Code)/)
	{
		($x,$branch)=split(/ /,$line,2);
		($branch,$y)=split(/:/,$branch,2);
		($brpos,$brlen)=split(/L/,$branch);

	}
###############DD############
		if($line =~ m/(DD).(\S*)$/)
	{
		#print "\n$line\n";
		($x,$dd)=split(/ /,$line,2);
		($dd,$y)=split(/:/,$dd,2);
		($ddpos,$ddlen)=split(/L/,$dd);
	}
###############MM##########
		if($line =~ m/(MM).(\S*)$/)
	{
		#print "\n$line\n";
		($x,$mm)=split(/ /,$line,2);
		($mm,$y)=split(/:/,$mm,2);
		($mmpos,$mmlen)=split(/L/,$mm);
	}
###############YY##########
		if($line =~ m/(YY).(\S*)$/)
	{
		#print "\n$line\n";
		($x,$yy)=split(/ /,$line,2);
		($yy,$y)=split(/:/,$yy,2);
		($yypos,$yylen)=split(/L/,$yy);
	}

	#######Building Rapport
	if($line =~ m/(Building Rapport \(Appearance\) MAXIMUM Score)/)
	{
		#print "\n$line\n";
		($x,$bldg_rapp_max)=split(/ /,$line,2);
		($bldg_rapp_max,$y)=split(/:/,$bldg_rapp_max,2);
		($bldg_rapp_maxpos,$bldg_rapp_maxlen)=split(/L/,$bldg_rapp_max);
	}
	#######Total Achieved
	if($line =~ m/(Exploring MAXIMUM Score)/)
	{
		($x,$explor_max)=split(/ /,$line,2);
		($explor_max,$y)=split(/:/,$explor_max,2);
		($explor_maxpos,$explor_maxlen)=split(/L/,$explor_max);

	}
	#######Total Percentage
	if($line =~ m/(Recommendation MAXIMUM Score)/)
	{
		($x,$recomm_max)=split(/ /,$line,2);
		($recomm_max,$y)=split(/:/,$recomm_max,2);
		($recomm_maxpos,$recomm_maxlen)=split(/L/,$recomm_max);
	}

	#######Max Welcome####
	if($line =~ m/(Closing MAXIMUM Score)/)
	{
		($x,$clos_max)=split(/ /,$line,2);
		($clos_max,$y)=split(/:/,$clos_max,2);
		($clos_maxpos,$clos_maxlen)=split(/L/,$clos_max);
	
	}
    #######Max Product####
	if($line =~ m/(Total MAXIMUM Score)/)
	{
		($x,$tot_max)=split(/ /,$line,2);
		($tot_max,$y)=split(/:/,$tot_max,2);
		($tot_maxpos,$tot_maxlen)=split(/L/,$tot_max);
	}
    #######Max Service####
	if($line =~ m/(Building Rapport \(Appearance\) Score)/)
	{
		
		($x,$achbldg_rapp)=split(/ /,$line,2);
		($achbldg_rapp,$y)=split(/:/,$achbldg_rapp,2);
		($achbldg_rapppos,$achbldg_rapplen)=split(/L/,$achbldg_rapp);
	}       
    #######Max Cleanliness####
	if($line =~ m/(Exploring Score)/)
	{
		($x,$achexplor)=split(/ /,$line,2);
		($achexplor,$y)=split(/:/,$achexplor,2);
		($achexplorpos,$achexplorlen)=split(/L/,$achexplor);
	}              
         # do line-by-line processing.
	#######Max Maint####
	if($line =~ m/(Recommendation Score)/)
	{
		($x,$achrecomm)=split(/ /,$line,2);
		($achrecomm,$y)=split(/:/,$achrecomm,2);
		($achrecommpos,$achrecommlen)=split(/L/,$achrecomm);
	}        
    
	#######Achieved Welcome####
	if($line =~ m/(Closing Score)/)
	{
		($x,$achclos)=split(/ /,$line,2);
		($achclos,$y)=split(/:/,$achclos,2);
		($achclospos,$achcloslen)=split(/L/,$achclos);
	}        
                        
   #######Achieved Product####
	if($line =~ m/(Total Score)/)
	{
		($x,$achtot)=split(/ /,$line,2);
		($achtot,$y)=split(/:/,$achtot,2);
		($achtotpos,$achtotlen)=split(/L/,$achtot);
	}       
    #######Achieved Service####
	if($line =~ m/(Building Rapport \(Appearance\) Percentage of Score)/)
	{
		($x,$percent_bldg_rapp)=split(/ /,$line,2);
		($percent_bldg_rapp,$y)=split(/:/,$percent_bldg_rapp,2);
		($percent_bldg_rapppos,$percent_bldg_rapplen)=split(/L/,$percent_bldg_rapp);
	}       
      #######Max Cleanliness####
	if($line =~ m/(Exploring Percentage of Score)/)
	{
		($x,$percent_explor)=split(/ /,$line,2);
		($percent_explor,$y)=split(/:/,$percent_explor,2);
		($percent_explorpos,$percent_explorlen)=split(/L/,$percent_explor);
	}         
	  #######Achieved Maintenance####
	if($line =~ m/(Recommendation Percentage of Score)/)
	{
		($x,$percent_recomm)=split(/ /,$line,2);
		($percent_recomm,$y)=split(/:/,$percent_recomm,2);
		($percent_recommpos,$percent_recommlen)=split(/L/,$percent_recomm);
	}       
   #######Percentage Service####
	if($line =~ m/(Closing Percentage of Score)/)
	{
		($x,$percent_clos)=split(/ /,$line,2);
		($percent_clos,$y)=split(/:/,$percent_clos,2);
		($percent_clospos,$percent_closlen)=split(/L/,$percent_clos);
	}       
     #######Percentage Product####
	if($line =~ m/(Total Percentage Score)/)
	{
		($x,$percent_tot)=split(/ /,$line,2);
		($percent_tot,$y)=split(/:/,$percent_tot,2);
		($percent_totpos,$percent_totlen)=split(/L/,$percent_tot);
	}                         
}

open(DATFILE) or die("Could not open log file.");

foo:foreach $line (<DATFILE>) 
{
if(length($line) < 10)
	{
	next foo;
	}
 #$syn=substr($line,1267-1,length($line)-1267-1);
 #$syn=trimwhitespace($syn);
$dd=substr($line,$ddpos-1,$ddlen);
$mm=substr($line,$mmpos-1,$mmlen);
$yy=substr($line,$yypos-1,$yylen);


$sdat="20".$yy."/".$mm."/".$dd;
$serial=substr($line,$serpos-1,$serlen);
$branch=substr($line,$brpos-1,$brlen);
$branch=substr($branch,5,5);

$fn="0091".$branch.$serial."-".$dd.$mm.$yy;


$bldg_rapp_max=substr($line,$bldg_rapp_maxpos-1,$bldg_rapp_maxlen);
$explor_max=substr($line,$explor_maxpos-1,$explor_maxlen);
$recomm_max=substr($line,$recomm_maxpos-1,$recomm_maxlen);
$clos_max=substr($line,$clos_maxpos-1,$clos_maxlen);
$tot_max=substr($line,$tot_maxpos-1,$tot_maxlen);



$achbldg_rapp=substr($line,$achbldg_rapppos-1,$achbldg_rapplen);
$achexplor=substr($line,$achexplorpos-1,$achexplorlen);
$achrecomm=substr($line,$achrecommpos-1,$achrecommlen);
$achclos=substr($line,$achclospos-1,$achcloslen);
$achtot=substr($line,$achtotpos-1,$achtotlen);


$percent_bldg_rapp=substr($line,$percent_bldg_rapppos-1,$percent_bldg_rapplen);
$percent_explor=substr($line,$percent_explorpos-1,$percent_explorlen);
$percent_recomm=substr($line,$percent_recommpos-1,$percent_recommlen);
$percent_clos=substr($line,$percent_clospos-1,$percent_closlen);
$percent_tot=substr($line,$percent_totpos-1,$percent_totlen);

print "\n\n$percent_bldg_rapp,$percent_explor,$percent_recomm,$percent_clos,$percent_tot\n";
$store_cd=substr($line,$store_cdpos-1,$store_cdlen);
$timing=substr($line,$timepos-1,$timelen);
$nationality=substr($line,$nationpos-1,$nationlen);

print "\nSTORE:$store_cdpos,$store_cdlen,$store_cd\n";
print "\nTIME:$timepos,$timelen,$time\n";
print "\nNATION:$nationpos,$nationlen,$nationality\n";
if($store_cd eq '1')
	{
	  $store_cd='Axiom';
	}
elsif($store_cd eq '2')
	{
	  $store_cd='Fono';
	}
elsif($store_cd eq '3')
	{
	  $store_cd='Mobile Store';
	}
#####################################
if($timing eq '1' || $timing eq '2')
	{
		$timing='Morning';
	}
elsif($timing eq '3')
	{
		$timing='Afternoon';
	}
elsif($timing eq '4' || $timing eq '5')
	{
		$timing='Evening';
	}
elsif($timing eq '6' || $timing eq '7')
	{
		$timing='Night';
	}
###########################
if($nationality eq '01')
	{
		$nationality='UAE';
	}
elsif($nationality eq '02')
	{
		$nationality='Egyptian';
	}
	elsif($nationality eq '03')
	{
		$nationality='Sudanese';
	}
	elsif($nationality eq '04')
	{
		$nationality='Jordanian';
	}
	elsif($nationality eq '05')
	{
		$nationality='Palestinian';
	}
	elsif($nationality eq '06')
	{
		$nationality='Lebanese';
	}
	elsif($nationality eq '07')
	{
		$nationality='Syrian';
	}
	elsif($nationality eq '08')
	{
		$nationality='Yemeni';
	}
	elsif($nationality eq '09')
	{
		$nationality='Other Arabs';
	}
	elsif($nationality eq '10')
	{
		$nationality='Indian';
	}
	elsif($nationality eq '11')
	{
		$nationality='SriLankan';
	}
	elsif($nationality eq '12')
	{
		$nationality='Pakistani';
	}
	elsif($nationality eq '13')
	{
		$nationality='Bangladeshi';
	}
	elsif($nationality eq '14')
	{
		$nationality='Fillipino';
	}
	elsif($nationality eq '15')
	{
		$nationality='Iranian';
	}
	elsif($nationality eq '16')
	{
		$nationality='European';
	}
	elsif($nationality eq '17')
	{
		$nationality='American';
	}
	elsif($nationality eq '18')
	{
		$nationality='Other NonArabs';
	}



print "\nNew Details$store_cd,$timing,$nationality\n\n";
	
  $sql="insert into tbl_mystic_axiomscores(fn,ser,brcd,store_cd,nationality,timing,sdat,";
  $sql=$sql."max_bldg_rapp,max_explor,max_recomm,max_clos,max_tot,";
  $sql=$sql."ach_bldg_rapp,ach_explor,ach_recomm,ach_clos,ach_tot,";
  $sql=$sql."percent_bldg_rapp,percent_explor,percent_recomm,percent_clos,percent_tot,sdatupl)";
  
  $sql=$sql."values (\'$fn\',\'".substr($line,$serpos-1,$serlen)."\',\'$branch\',\'$store_cd\',\'$nationality\',\'$timing\',\'".$sdat."\',";

  $sql=$sql."$bldg_rapp_max,$explor_max,$recomm_max,$clos_max,$tot_max,";
  $sql=$sql."$achbldg_rapp,$achexplor,$achrecomm,$achclos,$achtot,";
  $sql=$sql."$percent_bldg_rapp,$percent_explor,$percent_recomm,$percent_clos,$percent_tot";
  $sql=$sql." ,now())\n";
print "$sql\n\n";


$sthE=$dbh->prepare($sql)|| print('unable to prepare  $dbh->errstr');
$rv=$sthE->execute || print("Record Already Entered  $sthE->errstr");

}



#$dbh->disconnect;

sub trimwhitespace($)
{
	my $string = shift;
	$string =~ s/^\s+//;
	$string =~ s/\s+$//;
	return $string;
}