Code

Comments

 #!/usr/bin/perl

 #

 # Nicolas Bondier

 # Switzernet 2012

 #

 use warnings;

 use strict;

 use DBI;

 use Spreadsheet::WriteExcel;

 use POSIX qw/strftime/;

 use File::Spec::Functions qw(rel2abs);

 use File::Basename;

 use Unicode::Map();

 use Encode;

 use List::Util qw(first);

 use Number::Latin;

 my $test_mode = 1;

 my $dirname = dirname(rel2abs($0));

 my $db   = "porta-billing";

 my $host = "xxxxxxxxxxxx";

 my $user = "xxxxxxxxxxxx";

 my $pass = "xxxxxxxxxxxx";

 my $dbh = DBI->connect( "dbi:mysql:dbname=$db;host=$host;", $user, $pass ) or die "Connexion

 impossible � la base de donn�es $db !";

 my @cols;

 my %columns;

Initialization of the different global vars.

We connect to the local database, which is the replication of the whole old master database.

 my ($sta, $sto) = get_i_customer_range();

We get the range of the i_customer we need to upload.

 my @i_customers_list = get_i_customers ($sta, $sto);

We get the i_customer list from the range passed as parameter. See the subroutine.

 my ( %customers ) = get_customer_data(@i_customers_list);

All data of the concerned customers is downloaded from the database and inserted in a hash.

See the subroutine.

 %customers = add_services(%customers);

The customer hash is completed with services subscribed by customers from external tables.

 to_excel(%customers);

Finally the hash is written to the excel file.

 sub get_i_customer_range{

   print "\nSelect a range of i_customer to import.\n";

   print "First i_customer:\n";

   my $first_c = <>;

   chomp($first_c);

   #print '\''.$first_c.'\'';

    while (  !isint($first_c) ){

     print "Error ! Give a correct value.\n";

     print "First i_customer:\n";

     $first_c = <>;

     chomp($first_c);

   }

   print "\nLast i_customer:\n";

   my $last_c = <>;

   chomp($last_c);

   while ( !$last_c || !isint($last_c) || $last_c < $first_c ){

     print "Error ! Give a correct value.\n";

     print "Last i_customer:\n";

     $last_c = <>;

     chomp($last_c);

   }

   sub isint{

     my $val = shift;

     return ($val =~ m/^\d+$/);

   }

   print "\nFirst i_customer : ".$first_c."\n";

   print "Last i_customer  :  ".$last_c."\n";

   return ($first_c,$last_c);  

 }

This subroutine is called in first. It asks to the user, the range of i_customer we need to download.

 sub get_i_customers {

   my $start = shift;

   my $stop = shift;

   my @ret;

   my $req = "SELECT

       c.i_customer

     FROM

       Customers c

     INNER JOIN

       Accounts a

     ON

       c.i_customer=a.i_customer

     WHERE

       c.iso_4217 = 'CHF'

     AND

       a.id vREGEXP '^41'

     AND

       i_rep = '3'

     AND
       c.i_customer >= ".$start."

     AND

       c.i_customer <= ".$stop."

     AND
       bill_status='O';";

   my $sth = $dbh->prepare($req);

   $sth->execute();     

   my @row;

   while ( @row=$sth->fetchrow_array ){

     push(@ret, $row[0]);

   }

   return @ret;

 }

Subroutine to get only the customers we want to add in the new billing. According to our system of sorting customer by i_rep, we get only the billable Swiss customers (for the moment).

 sub get_customer_data {

   my @i_cus = @_;

   my %hash;

   my $req1 = "SELECT

     cus.i_customer                 as OldICustomer,

     cus.name                       as CustomerName,

     cus.balance                    as Balance,

     cus.iso_4217                   as Currency,

     cus.companyname                as CompanyName,

     cus.salutation                 as Salutation,

     cus.firstname                  as FirstName,

     cus.midinit                    as MI,

     cus.lastname                   as LastName,

     cus.baddr1                     as Address1,    

     cus.baddr2                     as Address2,

     cus.baddr3                     as Address3,

     cus.baddr4                     as Address4,

     cus.baddr5                     as Address5,

     cus.city                       as City,

     cus.state                      as ProvinceState,

     cus.zip                        as Zip,

     cus.country                    as CountryRegion,

     cus.note                       as Note,

     cus.cont1                      as Contact,

     cus.phone1                     as Phone,

     cus.faxnum                     as Fax,

     cus.phone2                     as AltPhone,

     cus.cont2                      as AltContact,

     cus.email                      as Email,

     cus.bcc                        as BCC,

     cus.send_statistics            as SendStatistics,

     cus.login                      as Login,

     cus.password                   as Password,

     cus.i_customer_type            as CustomerType,

     cus.i_billing_period           as BillingPeriod,

     cus.credit_limit               as CreditLimit,

     cus.i_tariff                   as Tariff,

     cus.i_time_zone                as TimeZone,

     cus.i_credit_card              as CreditCard,

     cus.i_env                      as Env,

     cus.i_template                 as Template,

     cus.tax_id                     as TaxID,

     cus.blocked                    as Blocked,

     cus.ppm_enabled                as PPMEnabled,

     cus.i_rep                      as Representative,

     cus.drm_enabled                as DRMEnabled,

     cus.max_abbreviated_length     as AbbreviatedNumberLength,

     cus.password_timestamp         as PasswordTimestamp,

     cus.out_date_format            as OutDateFormat,

     cus.out_time_format            as OutTimeFormat,

     cus.out_date_time_format       as OutDateTimeFormat,

     cus.in_date_format             as InDateFormat,

     cus.in_time_format             as InTimeFormat,

     cus.i_online_payment_processor as OnlinePaymentProcessor,

     cus.reccuring_enabled          as ReccuringEnabled,

     cus.min_allowed_payment        as MinAllowedPayment,

     cus.i_acl                      as ACL,

     cus.opening_balance            as OpeningBalance,

     cus.cld_translation_rule       as CLDDialingRule,

     cus.cli_in_translation_rule    as CLIDialingRule,

     cus.i_lang                     as PreferredLanguage,

     cus.credit_limit_warning       as BalanceWarningThreshold,

     cus.callshop_enabled           as CallShopEnabled,

     cus.billed_to                  as BilledTo,    

     cus.i_routing_plan             as RoutingPlan,

     cus.i_vd_plan                  as DiscountPlan,

     cus.i_moh                      as MOH,

     cus.i_customer_class           as CustomerClass,

     cus.bp_charge_cc               as BpChargeCc,

     cus.unallocated_payments       as UnallocatedPayments,

     cus.bill_status                as BillStatus,

     cus_notepad.notepad            as Notepad

 FROM

     Customers cus

 INNER JOIN

     Customer_Notepad cus_notepad

 ON

     cus_notepad.i_customer = cus.i_customer

 WHERE cus.i_customer = '";

   my $n = 0;

   while ($i_cus[$n]){

         my $req = $req1.$i_cus[$n]."';";

         my $sth = $dbh->prepare($req) or die DBI->errstr();

         $sth->execute() or die DBI->errstr();

         @cols = @{ $sth->{NAME} };

         $hash{$i_cus[$n]} = $dbh->selectrow_hashref($req);

         $n++;

   }

 return %hash;

 }

This subroutine collects all required data from each customer and adds it in a hash.

In this hash, each key (ex: OldICustomer, CustomerName …) is the column name of the excel file we will generate.

Each customer’s data hash is inserted in a hash with i_customer as key.

This hash can be returned once all customers’ data has been downloaded.

 sub add_services {

   my %hash = @_;

   push (@cols, 'srvSimcallsLimit');  

   for my $c ( sort keys %hash ) {

     $hash{$c}{'srvSimcallsLimit'} = get_srvSimcallsLimit($c);

   }

   push (@cols, 'srvCLI');  

   for my $c ( sort keys %hash ) {

     $hash{$c}{'srvCLI'} = get_srvCLI($c);

   }

   push (@cols, 'srvCLIR');  

   for my $c ( sort keys %hash ) {

     $hash{$c}{'srvCLIR'} = get_srvCLIR($c);

   }

   push (@cols, 'srvCLIRHide');  

   for my $c ( sort keys %hash ) {

     $hash{$c}{'srvCLIRHide'} = get_srvCLIRHide($c);

   }

   push (@cols, 'srvCLIRShow');  

   for my $c ( sort keys %hash ) {

     $hash{$c}{'srvCLIRShow'} = get_srvCLIRShow($c);

   }

   push (@cols, 'srvFirstLoginGreeting');  

   for my $c ( sort keys %hash ) {

     $hash{$c}{'srvFirstLoginGreeting'} = get_srvFirstLoginGreeting($c);

   }

   push (@cols, 'srvDistinctiveRingVpn');  

   for my $c ( sort keys %hash ) {

     $hash{$c}{'srvDistinctiveRingVpn'} = get_srvDistinctiveRingVpn($c);

   }

   push (@cols, 'srvLegalIntercept');  

   for my $c ( sort keys %hash ) {

     $hash{$c}{'srvLegalIntercept'} = get_srvLegalIntercept($c);

   }

   push (@cols, 'srvCallRecording');  

   for my $c ( sort keys %hash ) {

     $hash{$c}{'srvCallRecording'} = get_srvCallRecording($c);

   }

   push (@cols, 'srvCallParking');  

   for my $c ( sort keys %hash ) {

     $hash{$c}{'srvCallParking'} = get_srvCallParking($c);

   }

   push (@cols, 'srvCentrex');  

   for my $c ( sort keys %hash ) {

     $hash{$c}{'srvCentrex'} = get_srvCentrex($c);

   }

   push (@cols, 'srvCliTrust');  

   for my $c ( sort keys %hash ) {

     $hash{$c}{'srvCliTrust'} = get_srvCliTrust($c);

   }

   push (@cols, 'srvPaging');  

   for my $c ( sort keys %hash ) {

     $hash{$c}{'srvPaging'} = get_srvPaging($c);

   }

   push (@cols, 'srvGroupPickup');  

   for my $c ( sort keys %hash ) {

     $hash{$c}{'srvGroupPickup'} = get_srvGroupPickup($c);

   }                             

   push (@cols, 'srvIpCentrexCare');  

   for my $c ( sort keys %hash ) {

     $hash{$c}{'srvIpCentrexCare'} = get_srvIpCentrexCare($c);

   }

   push (@cols, 'srvRtppLevel');  

   for my $c ( sort keys %hash ) {

     $hash{$c}{'srvRtppLevel'} = get_srvRtppLevel($c);

   }

   %hash = set_defaults_values(%hash);  

   return %hash;

 }

This subroutine add, for all customers, services settings such as the displayed CLI for the account if it is different that account user name, hiding CLI …

Customers’ hash is returned when all new keys for all customers are set.

For each one of the loop, click on the called function.

 sub get_srvSimcallsLimit {

   my $i_cus = shift;

   my $sql = "SELECT

       value

   FROM

       Service_Attribute_Values srv

   WHERE

       i_foreign = '".$i_cus."'

   AND

       i_sattribute = (SELECT

                           sra.i_sattribute as i_a

                       FROM

                           Service_Attributes sra

                       INNER JOIN

                           Services sr

                       ON

                           sr.i_service=sra.i_service

                       WHERE

                           sr.name='sim_calls_limit'

                       AND

                           sr.level = 'Customers'

                       AND

                           sra.name='max_calls'

   );";

   my $sth = $dbh->prepare($sql);

   $sth->execute();

   my @result = $sth->fetchrow_array();

   my $ret = $result[0];

   $ret = '' if (!$ret);

   if ($ret eq ''){

     $ret = 'N';

   } else {

     $ret = 'Y';

   }

   return $ret;

 }

This function is used to get the value of the ‘SimcallsLimit’ service attribute, ‘Y’ or ‘N’.

This has been done before seeing the simultaneous calls must be set in Customer Site level.

 sub get_srvCLI {

 my $i_cus = shift;

     my $req = "select service_flags from Customers where i_customer = ".$i_cus." LIMIT 1;";

     my $v = $dbh->selectrow_array($req, undef);

     $v = substr($v, 2, 1);

     $v = 'A' if (!$v || $v ne 'Y');

     return $v;

 }

CLI service, on the customer level, has many options.

We choose between only two according to our current settings.

- ‘A’: means the CLI is the account’s CLI.

- ‘Y’: means the CLI is set in the customer level. We use this feature to display another CLI than account ID for our business customer.

 sub get_srvCLIR {

   return 'P';            

 }

CLIR service is the default rule for hiding numbers.

3 options are available :

- ‘Y’: Always hide CLI.

- ‘N’: Never hide CLI.

- ‘P’: Automatic.

We only set the setting to this value. This way, customers will use a prefix before the CLD to hide the CLI.

 sub get_srvCLIRHide {

   return '*81';

 }

This is the prefix to add for hiding numbers. The prefix *81 is the only value we accept for number hiding.

See past researches [I]

 sub get_srvCLIRShow {

   return '';

 }

Default is showing, we do not need a prefix for showing CLI.

 sub get_srvFirstLoginGreeting {

   return 'N';

 }

As we are uploading customer who are not new, I disabled this feature.

 sub get_srvDistinctiveRingVpn {

   return 'N';

 }

We do not support. This is for a distinctive ring when receiving call from an external network.

 sub get_srvLegalIntercept {

   return 'N';

 }

No legal intercept.

 sub get_srvCallRecording {

   return 'N';

 }

No recording service is implemented on Astrad servers as far.

 sub get_srvCallParking {

   return 'N';

 }

Not implemented.

 sub get_srvCentrex {

 my $i_cus = shift;

     my $req = "select value from Service_Attribute_Values where i_foreign = ".$i_cus." and i_sattribute = '3' LIMIT 1;";

     my $v = $dbh->selectrow_array($req, undef);    

     $v = '' if (!$v);

     return $v;

 }

The srvCentrex value is the number to display in case of the CLI must be different than the account ID (if srvCLI is set to ‘Y’).

On old billing, we used to create an account with the same batch number, but this is no more required in new version of PB.

 sub get_srvCliTrust {

   return 'N';

 }

Correspond to the ‘Accept Caller Identity’ setting in service feature. This is not used by our Astrad servers.

 sub get_srvPaging {

   return 'N';

 }

Paging service disabled.

 sub get_srvGroupPickup {

   return 'N';

 }

Not implemented in Astrad servers.

 sub get_srvIpCentrexCare {

   return 'N';

 }

This is the new centrex customer care interface. It is not well implemented on the new MR24. There are some bugs, it is complicated and only English is available. It is disabled by default.

 sub get_srvRtppLevel {

   return 'N';

 }

Deactivation of RTP proxy.

 sub set_defaults_values {

   my %hash = @_;

   # Access list

   for my $c ( sort keys %hash ) {

     $hash{$c}{'ACL'} = '10011';

   }

   # Customer Class

   for my $c ( sort keys %hash ) {

     $hash{$c}{'CustomerClass'} = '2';

   }  

   return %hash;

 }

Access list (‘ACL’) is set to the custom access list for all our Swiss customers.

Privileges for accessing customer data from the customer care web interface are defined in the access list with id ‘10011’.

Default created CustomerClass for Swiss customers has id ‘2’.

 sub to_excel {   my %hash = @_;

   my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);

   my $filename = strftime('%y-%m-%d_%Hh%Mm%Ss',localtime).'_Customers.xls';

   my $workbook  = Spreadsheet::WriteExcel->new($filename);

   my $fl = 1;

   my $x;

   my $y = 1;

   my $worksheet = $workbook->add_worksheet();

   my $temp = '';

   for my $c ( sort keys %hash ) {

     $x = 0;

     foreach (@cols){

       if ( $y == 1 ){ $worksheet->write(0, $x, $_); $columns{$_} = get_column_AA($x+1);}

       $temp = decode("utf8", $hash{$c}{$_});

       $temp = '' if ( !$temp || !defined($temp) );

       #print $temp;

       $temp = test_mode( $temp, $_ ) if $test_mode;

       $worksheet->write($y, $x, $temp);

       $x++;

     }

     $y++;    

   }

   print "\n\n";

   foreach (@cols){

     print $_."=".$columns{$_}."\n";

   }

   print "\n\n # Copy the list above in the importCustomers_SwitzernetXls.cfg configuration file in the slave \n";

   print "\n # Download and verify created excel file :";

   print "\n # scp root\@pbs1.switzernet.com:".$dirname."/".$filename." . ;  cygstart ./$filename \n";

   print "\n # Import in new master (to execute on slave) :";

   print "\n # cd /home/porta-admin/importCustomers; scp root\@pbs1.switzernet.com:".$dirname."/".$filename." .; ./importCustomers_SwitzernetXls.pl -v -x ".$filename." -c importCustomers_SwitzernetXls.cfg\n";

   }

The final subroutine which write the content of the customers’ hash to the excel file.

At first loop, the script adds the columns names from keys of the hash of customers’ hashes.

We do not forget to decode utf8 values of hash from the database, in order to keep names with accents.

At the end, the script displays the new correspondences between columns letters and names.

This output must be past in the configuration file used by the script for uploading customers.

Ex :

CustomerName=A

Balance=B

…

InDateFormat=BC

…

 sub test_mode {

   my $par1 = shift;

   my $par2 = shift;

   my $ret = $par1;

   if ( $par2 eq 'CustomerName' ){

     $par1 = $par1.' '.`date +%y%m%d%H%M%S`;

   }

   if ( $par2 eq 'Login' ){

     $par1 = $par1.'['.`date +%y%m%d%H%M%S`.']';

   }

   if ( $par2 eq 'Email' ){

     $par1 = 'xxxxxxxxx.xxxxxxxx@switzernet.com';

   }

   $par1 =~ s/\n//g;

   return $par1;

 }

A simple function that some of values unique for testing.

This is important to do not have duplicates values that can break the upload.

 sub get_column_AA {

   my $index = shift;

   my $col = int2latin($index);

   $col = uc $col;

   return $col;

 }

Return ‘A’ for 1, ‘B’ for 2, …

[full_version]

Code

Comments

my $old_i_customer = '';

Setting a global variable old_i_customer for further use.

my $i_cus_corr_file = 'i_cust_corr.csv'; 

if (unlink($i_cus_corr_file) == 0) {

     print "File ".$i_cus_corr_file." deleted successfully.";

} else {

     print "File ".$i_cus_corr_file." was not deleted.";

}

Checking if the file of correspondences exists. If yes deleting it in order to create a new one.

old_i_customer => q{OldICustomer},

This is one of the settings that have been added. OldICustomer is the column of the excel file containing the old i_customer.

         if ($k =~ /^old_i_customer$/){

           $old_i_customer = $object{$k};

           delete $object{$k};

         }

Part of the subroutine ‘doRow’ where we get all values of the excel file. The i_customer is taken and its value is set in the old_i_customer global variable.

 $op->{after_update_hook} = sub {      # Here we can do some specific manipulations after object creation

     my ($op, $customer, $subtables) = @_;

     print STDERR "Created customer with i_customer=$customer->{__i_object}\n" if $op->{verbose};

     print STDERR "Old i_customer billing :".$old_i_customer.".";

     open (BILL_CORR, '>>'.$i_cus_corr_file);

       print BILL_CORR $customer->{__i_object}.",".$old_i_customer."\n";

     close (BILL_CORR);

 };

Here, according to the comments, we can place code after the object has been created. We just get the new i_customer, and then write it in the correspondences file with the old_i_cusotmer.

[full_version]

Code

Comments

 #!/usr/bin/perl

 #

 # Nicolas Bondier

 # Switzernet 2012

 #

 use warnings;

 use strict;

 use DBI;

 use Spreadsheet::WriteExcel;

 use POSIX qw/strftime/;

 use File::Spec::Functions qw(rel2abs);

 use File::Basename;

 use Text::CSV;

 use Encode;

 use Number::Latin;

 # Options

 my $test_mode = 0;

 my $print_hash_ref = 0;

 my $write_to_excel = 1;

Includes and testing options.

 my $dirname = dirname(rel2abs($0));

 my $db   = "porta-billing";

 my $host = "xxxxxxxxxxxx";

 my $user = "xxxxxxxxxxxx";

 my $pass = "xxxxxxxxxxxx";

 my $dbh  = DBI->connect( "dbi:mysql:dbname=$db;host=$host;", $user, $pass ) or die "Connexion impossible à la base de données $db !";

 my $i_customer_file = 'i_cust_corr.csv';

 my @cols;

 my $init_col = 0;

Connection to the database and initialization of variables.

 # Creating hash of accounts

 my %accounts_list = get_account_list($i_customer_file);

 %accounts_list = verify_accounts(%accounts_list);

 my %accounts = get_account_data(%accounts_list);

 # Fixing id for external tables

 %accounts = get_new_i_product(%accounts);

 %accounts = get_new_i_access_level(%accounts);

 # Adding all other fields

 %accounts = get_batch(%accounts);

 %accounts = get_srvCentrex(%accounts);

 %accounts = get_srvCLI(%accounts);

 %accounts = get_srvCLIR(%accounts);

 %accounts = get_srvDistinctiveRingVPN(%accounts); %accounts = get_srvLegalIntercept(%accounts);

 %accounts = get_srvCallRecording(%accounts);

 %accounts = get_srvEmergency(%accounts);

 %accounts = get_srvAnsweringMode(%accounts);

 %accounts = get_FollowMeMode(%accounts);

 %accounts = get_FollowMeSequence(%accounts);

 %accounts = get_FollowMeTimeout(%accounts);

 %accounts = get_FollowMeMaxForwards(%accounts);

 # Write to excel file

 to_excel(%accounts) if $write_to_excel;

 # printing the list

 print_hash_ref(%accounts) if $print_hash_ref;

Main manipulation of the account list and account’s hash.

Then we write the final hash to an Excel file.

 sub get_account_list {

   my $file = shift;

   my $csv = Text::CSV->new();

   my %corr;

   my $req = '';

   open (CSV, "<", $file) or die $!;

   while (<CSV>) {

       if ($csv->parse($_)) {

         my @columns = $csv->fields();

         #print "Searching for i_customer : ".$columns[1]."\n";

         $req  = "SELECT i_account FROM Accounts WHERE i_customer = ".$columns[1].";";

         my $sth = $dbh->prepare($req) or die DBI->errstr();

         $sth->execute();

         while (my @results = $sth->fetchrow_array()) {

           # print "Let's insert the account ".$results[0]." for customer ".$columns[0]." (old icustomer ".$columns[1].")\n";

           $corr{ $results[0] } = $columns[0];

         }

       } else {

         my $err = $csv->error_input;

         print "Failed to parse line: $err";

       }

   }

   close CSV;

   return %corr;

 }

This function read the list of customers from i_cust_corr.csv file created when downloading the customers.

For each of the customer we get the i_account fields of owned accounts.

 sub get_account_data {

   my %i_acc = @_;

   my %hash;

   my $req1 = "SELECT

     acc.issue_date               as IssueDate,

     acc.iso_4217                 as Currency,

     acc.iso_639_1                as PreferredLanguage,

     acc.activation_date          as ActivationDate,

     acc.expiration_date          as ExpirationDate,

     acc.life_time                as LifeTime,

     acc.id                       as ID,

     acc.i_product                as Product,

     acc.balance                  as Balance,

     acc.blocked                  as Blocked,

     acc.first_usage              as FirstUsage,

     acc.credit_limit             as CreditLimit,

     acc.billing_model            as BillingModel,

     acc.login                    as Login,

     acc.password                 as Password,

     acc.i_env                    as Env,

     acc.follow_me_enabled        as FollowMe,

     'N'                          as UM_Enabled,

     acc.opening_balance          as OpeningBalance,

     acc.control_number           as ControlNumber,

     acc.redirect_number          as RedirectNumber,

     acc.email                    as Email,

     acc.i_lang                   as PreferredLanguage,

     acc.ecommerce_enabled        as EcommerceEnabled,

     acc.password_timestamp       as PasswordTimestamp,

     acc_n.notepad                as Notepad,

     acc.out_date_format          as OutDateFormat,

     acc.out_time_format          as OutTimeFormat,

     acc.out_date_time_format     as OutDateTimeFormat,

     acc.in_date_format           as InDateFormat,

     acc.in_time_format           as InTimeFormat,

     acc.i_vd_plan                as DiscountPlan,

     acc.i_acl                    as ACL,

     acc.i_time_zone              as TimeZone,

     acc.h323_password            as VoIPPassword,

     acc.i_account                as OldAccount,

     acc.i_customer               as OldCustomer

 FROM

     Accounts acc

 LEFT JOIN

     Account_Notepad acc_n

 ON

     acc.i_account=acc_n.i_account

 WHERE

     acc.i_account = '";

   foreach my $k (keys %i_acc){

     my $req = $req1.$k."';";

     my $sth = $dbh->prepare($req) or die DBI->errstr();

     $sth->execute() or die DBI->errstr();

     $hash{$k} = $dbh->selectrow_hashref($req);

     if ($init_col == 0) {

       @cols = @{ $sth->{NAME} };       $init_col++;

     }

     %hash = add_field_and_value($k,'Customer',$i_acc{$k},%hash);

   }

   return %hash;

 }

For each i_account, we get the Account data from the database.

Each value is stored in a hash reference with i_account as key and the Excel column name as reference.

 sub print_hash_ref {

   my %hash = @_;

   for my $c ( sort keys %hash ) {

     foreach (@cols){

       $hash{$c}{$_} = '' if (!$hash{$c}{$_});

       print $_ . "=>" . $hash{$c}{$_} . "\n";

     }  

     print "-----------------------------------------------------------------------\n";

   }

 }

Printing the main account hash reference to view all values.

 sub get_batch {

   my %acc = @_;

   for my $c ( sort keys %acc ) {

     my $req = "SELECT b.name FROM Accounts a INNER JOIN Batch b ON a.i_batch = b.i_batch WHERE a.i_account = ".$acc{$c}{'OldAccount'}." LIMIT 1;";

     # print $req;

     my $v = $dbh->selectrow_array($req, undef);

     %acc = add_field_and_value ($c,'Batch',$v,%acc);

   }

   return %acc;

 }

We get account’s batch.

sub get_srvCLI {

   my %acc = @_;

   for my $c ( sort keys %acc ) {

     my $req = "select service_flags from Accounts where i_account = ".$acc{$c}{'OldAccount'}." LIMIT 1;";

     my $v = $dbh->selectrow_array($req, undef);

     $v = substr($v, 1, 1);

     $v = 'A' if (!$v || ($v ne 'Y' && $v ne '^'));

     %acc = add_field_and_value ($c,'srvCLI',$v,%acc);

   }

   return %acc;

 }

Accounts have possibility to show a different CLI than their number. There are many possibilities:

- ‘A’ is for displaying the account ID.

- ‘Y’ is for a custom CLI to fill (See srvCentrex bellow).

- ‘^’ mean to take the customers settings for choosing (which can be ‘A’ for account ID).

 sub get_srvCentrex {

   my %acc = @_;

   for my $c ( sort keys %acc ) {

     my $req = "select value from Service_Attribute_Values where i_foreign = ".$acc{$c}{'OldAccount'}." and i_sattribute = '4' LIMIT 1;";

     my $v = $dbh->selectrow_array($req, undef);

     $v = '' if (!$v);

     %acc = add_field_and_value ($c,'srvCentrex',$v,%acc);

   }

   return %acc; }

srvCentrex is the CLI to display if is different than account ID.

 sub get_srvCLIR {

   my %acc = @_;

   for my $c ( sort keys %acc ) {

     %acc = add_field_and_value ($c,'srvCLIR','^',%acc);

   }

   return %acc;

 }

We have set the srv_CLIR in the customer level.

We set here the option to ‘^’, meaning that the value must be found under the customer level.

We do not need to define a value for each account.

 sub get_srvDistinctiveRingVPN {

   my %acc = @_;

   for my $c ( sort keys %acc ) {

     %acc = add_field_and_value ($c,'srvDistinctiveRingVPN','^',%acc);

   }

   return %acc;

 }

We do not support. This is for a distinctive ring when receiving call from an external network.

We refer to customer level.

 sub get_srvLegalIntercept {

   my %acc = @_;

   for my $c ( sort keys %acc ) {

     %acc = add_field_and_value ($c,'srvLegalIntercept','^',%acc);

   }

   return %acc;

 }

Astrad servers do not support this feature.

We refer to customer level.

 sub get_srvCallRecording {

   my %acc = @_;

   for my $c ( sort keys %acc ) {

     %acc = add_field_and_value ($c,'srvCallRecording','^',%acc);

   }

   return %acc;

 }

No call recording.

We refer to customer level.

 sub get_srvEmergency {

   my %acc = @_;

   for my $c ( sort keys %acc ) {

       %acc = add_field_and_value ($c,'srvEmergency','N',%acc);

   }

   return %acc;

 }

Not activated.

 sub get_srvAnsweringMode {

   my %acc = @_;

   for my $c ( sort keys %acc ) {

     %acc = add_field_and_value ($c,'srvAnsweringMode','7',%acc);

     # TODO :

     # test with no voicemail : %acc = add_field_and_value ($c,'srvAnsweringMode','3',%acc);

   }

   return %acc; }

Voicemail answer.

 sub get_FollowMeMode {

   my %acc = @_;

   for my $c ( sort keys %acc ) {

     %acc = add_field_and_value ($c,'FollowMeMode','',%acc);

   }

   return %acc;

 }

The value of the follow me modes are still the same, always, never, …

We just select.

 sub get_FollowMeSequence {

   my %acc = @_;

   for my $c ( sort keys %acc ) {

     %acc = add_field_and_value ($c,'FollowMeSequence','Order',%acc);

   }

   return %acc;

 }

Sequence value does not change, we keep the same.

 sub get_FollowMeTimeout {

   my %acc = @_;

   for my $c ( sort keys %acc ) {

     my $req = "select timeout from Follow_Me where i_account = '".$acc{$c}{'OldAccount'}."' LIMIT 1;";

     my $v = $dbh->selectrow_array($req, undef);

     %acc = add_field_and_value ($c,'FollowMeTimeout',$v,%acc);

   }

   return %acc;

 }

Timeout before going to the followme, in seconds.

 sub get_FollowMeMaxForwards {

   my %acc = @_;

   for my $c ( sort keys %acc ) {

     %acc = add_field_and_value ($c,'FollowMeMaxForwards','20',%acc);

   }

   return %acc;

 }

Maximum number of forwards. The limit will never be reached.

 sub get_new_i_product{

   my %acc = @_;

   my $prods = {

                 '97'=>'7',

                 '96'=>'8',

                 '95'=>'9',

                 '69'=>'5',

                 '70'=>'4',

                 '68'=>'3'

               };

   for my $c ( sort keys %acc ) {

     %acc = add_field_and_value ($c,'Product',$prods->{$acc{$c}{'Product'}},%acc);

   }   return %acc;

 }

We set correspondences between the products in the new billing and the ones of the old billing.

Here I only made these correspondences with the i_product, which is safer than the products names.

 sub get_new_i_access_level{

   my %acc = @_;

   # my $acl = {

                 # '155'=>'10007'

               # };

   for my $c ( sort keys %acc ) {

     %acc = add_field_and_value ($c,'ACL','10007',%acc);

   }

   return %acc;

 }

Access levels for our customers have been set-up. Value ‘10007’ is the i_access_level for Switzernet customers.

 sub add_field_and_value {

   my $a = shift;

   my $n = shift;

   my $v = shift;

   my %acc = @_;

   # print "| account : ".$a."|\n";

   # print "| name    : ".$n."|\n";

   # print "| value   : ".$v."|\n";

   $acc{$a}{$n} = $v;

   if ( !exist_in_array($n,@cols) ){

     push (@cols,$n);

   }

   return %acc;

 }

Subroutine for adding values to the hash more quickly.

 sub exist_in_array {

   my $value = shift;

   my @array = @_;

   my $ret = 0;

   my %hash;

   %hash = map { $_ => 1 } @array;

   if ($hash{$value}){

     $ret = 1;

   }

   return $ret;

 }

Checking if a column exists in the column list.

 sub to_excel {

   my %hash = @_;

   my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);

   my $filename = strftime('%y-%m-%d_%Hh%Mm%Ss',localtime).'_Accounts.xls';

   my $workbook  = Spreadsheet::WriteExcel->new($filename);

   my $fl = 1;

   my $x;

   my $y = 1;   my $worksheet = $workbook->add_worksheet();

   my $temp = '';

   my %columns;

   print "\n\n Starting account excel file creation ... \n\n";

   for my $c ( sort keys %hash ) {

     $x = 0;

     foreach (@cols){

       if ( $y == 1 ){ $worksheet->write(0, $x, $_); $columns{$_} = get_column_AA($x+1);}

       $temp = decode("utf8", $hash{$c}{$_});

       $temp = '' if ( !$temp || !defined($temp) );

       $temp = test_mode( $temp, $_ ) if $test_mode;

       $worksheet->write($y, $x, $temp);

       $x++;

     }

     $y++;

   }

   print "\n Please copy to upload configuration file, above [Columns], following lines :\n\n";

   foreach (@cols){

     print $_."=".$columns{$_}."\n";

   }

   print "\n\n Created new excel file : ".$dirname."/".$filename."\n\n";

 }

This function write the final content to the excel file, put the column names in the top of the Excel file and then display the information to past in the configuration file of the script we will use to upload accounts.

 sub get_column_AA {

   my $index = shift;

   my $col = int2latin($index);

   $col = uc $col;

   return $col;

 }

Return ‘A’ for 1, ‘B’ for 2, …

 sub test_mode {

   # For formating a field if testing

   my $par1 = shift;

   my $par2 = shift;

   # if ( $par2 eq 'CustomerName' ){

     # $par1 = $par1.'['.`date +%y%m%d%H%M%S`.']';

   # }

   return $par1;

 }

Testing options. This function has also been used in the customer import script.

 sub verify_accounts {

   my %acc = @_;

   my %centrex_i_acc;

   my @ret;

   my %del;

   my %prefs = ( '4121550' => 1,

                 '4121504' => 1,

                 '4121509' => 1,

                 '4121999' => 1,

                 '4122550' => 1,

                 '4122509' => 1,

                 '4122504' => 1,

                 '4124504' => 1,

                 '4124509' => 1,

                 '4126504' => 1,

                 '4126509' => 1,

                 '4127504' => 1,

                 '4127509' => 1,

                 '4131504' => 1,

                 '4131509' => 1,

                 '4132504' => 1,

                 '4132509' => 1,

                 '4133504' => 1,

                 '4133509' => 1,

                 '4134504' => 1,

                 '4134509' => 1,

                 '4141509' => 1,

                 '4141504' => 1,

                 '4143509' => 1,

                 '4143504' => 1,

                 '4144504' => 1,

                 '4152509' => 1,

                 '4152504' => 1,

                 '4155509' => 1,

                 '4155504' => 1,

                 '4156509' => 1,

                 '4156504' => 1,

                 '4161504' => 1,

                 '4161509' => 1,

                 '4162504' => 1,

                 '4162509' => 1,

                 '4171509' => 1,

                 '4171504' => 1,

                 '4181509' => 1,

                 '4181504' => 1,

                 '4191209' => 1,

                 '4181204' => 1

   );

   ## DELETING Accounts that are only used for CLI display

   # We now use new CLI display feature

   my $req = "select acc.i_account, acc.id from Service_Attribute_Values sav INNER JOIN Accounts acc ON sav.value=acc.id where (i_sattribute = '4' or i_sattribute = '3') and value != '';";

        my $sth = $dbh->prepare($req);

        $sth->execute();       

        my @row;

        while ( @row=$sth->fetchrow_array ){

     $centrex_i_acc{$row[0]}=$row[1];        }

   for my $c ( sort keys %acc ) {

     if ($centrex_i_acc{$c}){

       delete $acc{$c} if (!$prefs{substr($centrex_i_acc{$c},0,7)});

     }

   }

   return %acc;

 }

I used this function to filter accounts we use for only displaying different CLI (business customers).

We must not insert them, as from know we will not use anymore this kind of set-up.

[full version]

Code

Comments

 #!/usr/bin/perl

 #

 # Nicolas Bondier

 # Switzernet 2012

 #

 use Encode;

 use strict;

 use warnings;

 use DBI;

 use Data::Dumper;

 use Text::CSV;

 use SOAP::Lite

 # +trace=>'debug'

 ;

 my $i_customer_file = 'i_cust_corr.csv';