# Perl Program to read the Imagine Position files 
# Based upon reading the file the IMAGINEPOSITIONS table
# will be updated in the database

# Declare the subroutine

use Win32::ODBC;
use Text::CSV_PP;
use Data::Dumper;
use IO::File;

sub trim($);



if (! @ARGV  == 2 ) {

  print "Invalid Number of Command Line Parameters  \n"  ;
  print "Two Parameters Expected  \n " ;
  die;
}


print "  Imagine Position File: " . $ARGV[0] . "\n"; 
print "        Properties File: " . $ARGV[1] . ".\n";  
  
open (ODBCProps, $ARGV[1]) ||  die("Could NOT open " . $ARGV[1] . ".\n");  
   
@ODBCFileData = <ODBCProps> ;
print "size of ODBCFileData: " .  @ODBCFileData . ".\n";
  
foreach  $row  (@ODBCFileData) {
     chomp($row);
     ($propName, $propValue)  = split /=/, $row;
     
     if ( trim(uc($propName)) eq 'DSN' )  {
         $DSN  = $propValue;
     }    
     elsif (trim(uc($propName)) eq 'SERVER')  {
         $Server =  $propValue ;
     
     }
     elsif (trim(uc($propName)) eq 'DATABASE')  {
              $Database =  $propValue ;
          
     }
     elsif (trim(uc($propName)) eq 'USERID')  {
         $userid =  $propValue ;
          
     }
     elsif (trim(uc($propName)) eq 'PWORD')  {
          $pword = $propValue ;     
     }
          
}    # foreach
  print "Database Connection Properties \n"; 
  print "\t DSN = " . $DSN . "\n" ;
  print "\t Server = " . $Server . "\n" ;
  print "\t Database = " . $Database . "\n" ;
  print "\t User Id = " . $userid . "\n" ;
  print "\t PWord = " . $pword . "\n" ;     
  close ODBCProps; 

  $connStr = " Driver={MySQL ODBC 3.51 Driver}; Server=" . $Server . ";Database=" . $Database .  ";UID=". $userid . ";PWD=". $pword .  ";" ;
  print  $connStr . "\n"  ;
 
  $db = new Win32::ODBC( $connStr ) or die "couldn't ODBC Connection because ", Win32::ODBC::Error(), "\n"   ; 
 
  $SQLStr1 = " insert into imaginepositions ( lineno ,  acct , extSecCode , IssuerCode , IssuerName , " .
      " qty, SMdescription,  USYM, Security, ISIN, SEDOL, CUSIP, type, marketPrice , value )     VALUES (  ";
      
  # ** ** ** 

  $fh = new IO::File;


  if ($fh->open("< $ARGV[0]")) {
    #print <$fh>;

    #** ** ** ** ** ** ** 
    #  find the beginning of the data
    while(defined($row = <$fh>)) {
      chomp($row);
      print "current row = "  .  $. . "  ";
      print $row   .  "\n";
      if ( $. >= 20  || $row =~ m/#/) {
         last;
      }

    }

    if  ( $. >= 20 ) {
      print " ** ** Error ** ** \n " ; 
      die("Did NOT find column headings for data in the file " . $ARGV[0] . ".\n");  
  
    } 
    else {
  
      if ( !$row eq  '#|Acct|Ext Sec Code|Issuer Code|Issuer Name|Quantity|SM Description|Usym|Security|Isin|Sedol|Cusip|Type|Mkt|$ Value' ) {

         print " ** ** Error ** ** \n " ; 
         die("Column Headings do NOT match expected values in the file " . $ARGV[0] . "   .\n");  
      }  

    }


    $csv = Text::CSV_PP->new();           # create a new CSV parser object
    $csv->sep_char('|');




    while (defined($col = $csv->getline($fh) )) {
         print "\n\n**  ** ** ** ** ** ** ** ** ** ** \n ";
          # print Dumper $columns;
         $secDesc =  $col->[6];
         $secDesc =~ s/'/`/g;
         $SQLValues   = "'@{[trim($col->[0])]}', " ;    #line no
         $SQLValues  .= "'@{[trim($col->[1])]}', " ;    #acct
         $SQLValues  .= "'@{[trim($col->[2])]}', " ;    #Ext Sec Code
         $SQLValues  .= "'@{[trim($col->[3])]}',  " ;   #Issuer Code
         $SQLValues  .= "'@{[trim($col->[4])]}', " ;    #Issuer Name
         $SQLValues  .= " @{[trim($col->[5])]}, " ;     #QTY
         $SQLValues  .= "'$secDesc', " ;                #SM Description
         $SQLValues  .= "'@{[trim($col->[7])]}', " ;    #USYM
         $SQLValues  .= "'@{[trim($col->[8])]}', " ;    #Security
         $SQLValues  .= "'@{[trim($col->[9])]}', " ;    #ISIN         
         $SQLValues  .= "'@{[trim($col->[10])]}', " ;   #SEDOL         
         $SQLValues  .= "'@{[trim($col->[11])]}', " ;   #CUSIP         
         $SQLValues  .= "'@{[trim($col->[12])]}', " ;   #TYPE         
         $SQLValues  .= " @{[trim($col->[13])]} , " ;   #Market Price         
         $SQLValues  .= " @{[trim($col->[14])]} ) " ;   #Value

         #** print $SQLStr1    . "\n"; 
         #** print $SQLValues . "\n"; 
         
         $SQLInsert = $SQLStr1. $SQLValues;
	 if ( $db->Sql( $SQLInsert ) ) {
	    print "** ** ** At row: "  .  $. . "  Error on Insert: " . $db->Error() . "\n ";
	    print  $SQLInsert  . "\n\n"  ;   
	 }
	 else {
	    $insertCount += 1;
	 } 
	   
 
    }  #while
    

##</code><code>##

Portfolio|Hedge|Hedge Price|# Holdings|Cash Balance|GAV~|$Val Long|$Val Short
PORTLIST|None|0.0|22947.0|123466.00|1234566.|9876.9957|9876.00

#|Acct|Ext Sec Code|Issuer Code|Issuer Name|Quantity|SM Description|Usym|Security|Isin|Sedol|Cusip|Type|Mkt|$ Value
1|QQ1234|Y.A|Y.A|DEPOS RECPTS STK|22.0|Y.A    S|Y.A|Y.A|US78462QF|2840215|78462QF|S|1.69|-10
2|QQ1234|X|X.A|SEL SEC SPDR|-250.0|X.A    O     20080118    76.00 AC|X.A|XA.U-08|||AX|O|665|25.0
3|QQ1234|SY|S.A|RECPTS STK|500.0|S.A    O     20080118   155.00 AC|S.A|SA.U-08|||00078462Q9zz|O|20|10.0