#!perl
use strict;
use DBI;
use Text::CSV_XS;
use Data::Dump 'pp';

my @f = qw(msisdn cat0 cat1 cat2 cat3 cat4 cat5 cat6 cat7 vip
           SAcode phone_type);

# SQL for inserts           
my $fields = join ',',@f;
my $ph     = join ',',map{'?'}@f;
my $sql_i  = "
   INSERT INTO user_master ($fields)
   VALUES ($ph)";

# connect and prepare insert statement
my $dbh = dbh(); # connect
#$dbh->do('DELETE from user_master');
my $sth_i = $dbh->prepare($sql_i);

# process records from csv
my $csv = Text::CSV_XS->new ( { binary => 1 } )  
        or die "Cannot use CSV: ".Text::CSV->error_diag ();        
        
while ( my $row = $csv->getline(*DATA) ){
 
  my ($cid,$msisdn,$pref,$type,$sev)  = @$row;
  next unless $type eq 'A';
   
  # extract preferences from string that match 0 to 7 only
  my %pref = ();
  $pref{$_} = 1 for grep /^[0-7]$/,split /#/,$pref;
  #pp \%pref;
  
  # if preference is 0 make all category as 0
  if ($pref{0} == 1){
    $pref{$_} = 0 for (1..7);
  }
  
  # build update sql  
  my $upd = join ",",map { "cat$_=$pref{$_}" } sort keys %pref;
  my $sql = "UPDATE user_master SET $upd WHERE msisdn=?";
    
  # try update first
  my $count = $dbh->do($sql,undef,$msisdn);
    
  # insert if no records updated 
  if ($count >= 1){
    print "Update $msisdn : $sql \n"
  } else {
    # build parameter array for insert
    my @cat = map { $pref{$_} || 0 }(0..7);
    my ($vip,$SAcode,$phone_type) = (1,0,0);# change to suit

    $sth_i->execute($msisdn,@cat,$vip,$SAcode,$phone_type);
    print "Insert $msisdn : @cat \n"
  }
} 

# connect
sub dbh{
  my $database = "test"; my $user = "user"; my $pw = "pw";
  my $dsn = "dbi:mysql:$database:localhost:3306"; 
  my $dbh = DBI->connect($dsn, $user, $pw, 
      { RaiseError=>1, AutoCommit=>1 } );
  return $dbh;
} 

#CID,MSISDN,Preference,TYPE,SEVERITY 
__DATA__
"9","9596354636","1","A","2"
"9","9596560722","2","A","2"
"9","9596560722","2#3#4#8#9","A","2"
"9","9622526453","3","D","2"