SHARE
TWEET

Untitled

a guest Aug 12th, 2016 59 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. #!/usr/bin/perl -w
  2.  
  3. use strict;
  4. use warnings;
  5. use DBI;
  6.  
  7. my %config = do '/root/bill_config.pl';
  8.  
  9. my $db_billing_name = $config{db_billing_name};
  10. my $db_prebilling_name = $config{db_prebilling_name};
  11. my $db_host = $config{db_host};
  12. my $db_user = $config{db_user};
  13. my $db_pass = $config{db_pass};
  14.  
  15. my $dbb = DBI->connect("DBI:mysql:" . $db_billing_name . ":" . $db_host, $db_user, $db_pass);
  16. my $dbp = DBI->connect("DBI:mysql:" . $db_prebilling_name . ":" . $db_host, $db_user, $db_pass);
  17.  
  18. my ($class,$prefix,$descr,$rset_price);
  19.  
  20. my $sthp = $dbp->prepare("SELECT `dest`,`descr`,`price_rset` FROM `iptel_price_op_2016`;");
  21. =pod
  22. $sthp->execute;
  23. $sthp->bind_columns(\$prefix,\$descr,\$rset_price);
  24. my $count = 0;
  25. while ($sthp->fetch) {
  26.     $class = ($prefix =~ /^7/) ? 1 : 2;
  27.     $descr = $dbb->quote($descr);
  28.     my $sthb = $dbb->prepare("SELECT `descr` FROM `tel_cat` WHERE `zone_num` = $prefix AND `cat_id`=7;");
  29.     $sthb->execute;
  30.     my $lb_descr;
  31.     $sthb->bind_columns(\$lb_descr);
  32.     $sthb->fetch;
  33.     $sthb->finish;
  34.     unless (defined($lb_descr)) {
  35.         $count++;
  36.         my $query = "INSERT INTO `tel_cat` (`zone_num`, `cat_id`, `class`, `descr`) VALUES (" . $prefix. ", 7, " . $class . ", " . $descr . ");";
  37. #       $dbb->do($query);
  38.         print $query . "\n";
  39.     }
  40. }
  41. print "Count inserted prefixes: " . $count . "\n";
  42. $sthp->finish;
  43.  
  44. my $tar_cat = 2;
  45. $sthp = $dbp->prepare("SELECT `price_rset` FROM `iptel_price_op_2016` GROUP BY `price_rset` ORDER BY `price_rset`;");
  46.  
  47. $sthp->execute;
  48. $sthp->bind_columns(\$rset_price);
  49. while ($sthp->fetch) {
  50.     $descr = $dbb->quote($rset_price . " руб.");
  51.     my $query = "INSERT INTO `categories` (`tar_id`, `cat_idx`, uuid, `oper_id`, `above`, `descr`, `free_seconds`, `round_seconds`, `min_charge_dur`, `enabled`) VALUES (291, " . $tar_cat . ", NULL, 1, " . $rset_price . ", " . $descr . ", 3, 60, 60, 0);";
  52.     $dbb->do($query);
  53.     print $query . "\n";
  54.     $tar_cat++;
  55. }
  56. $sthp->finish;
  57. print "Last inserted categories: " . $tar_cat-- . "\n";
  58. =cut
  59.  
  60. $sthp = $dbp->prepare("SELECT `dest`, `price` FROM `test_price` ORDER BY `price`;");
  61. $sthp->execute;
  62. $sthp->bind_columns(\$prefix, \$rset_price);
  63. while ($sthp->fetch) {
  64.     my ($cat_idx,$zone_id);
  65.     my $sthb = $dbb->prepare("SELECT `cat_idx` FROM `categories` WHERE `above` = " . $rset_price . " AND `tar_id` = 305;");
  66.     $sthb->execute;
  67.     $sthb->bind_columns(\$cat_idx);
  68.     $sthb->fetch;
  69.     $sthb->finish;
  70.  
  71.     $sthb = $dbb->prepare("SELECT `zone_id` FROM `tel_cat` WHERE `zone_num` = " . $prefix . " AND `cat_id` = 7;");
  72.     $sthb->execute;
  73.     $sthb->bind_columns(\$zone_id);
  74.     $sthb->fetch;
  75.     $sthb->finish;
  76.  
  77.     my $query = "INSERT INTO `tel_cat_idx` (`tar_id`, `cat_idx`, `zone_id`, `direction`) VALUES (305, " . $cat_idx . ", " . $zone_id . ", 1);";
  78.     $dbb->do($query);
  79.     print $query . "\n";
  80. }
  81.  
  82. $sthp->finish;
  83. $dbb->disconnect;
  84. $dbp->disconnect;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top