API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Feb 23rd, 2017
143
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 27.11 KB | None | 0 0
raw download clone embed print report
  1. #!/usr/bin/perl
  2.  
  3. use strict;
  4. use DBI;
  5. use DBD::Pg;
  6. use Config::General;
  7. use Getopt::Std;
  8. use POSIX qw( strftime );
  9. use Data::Dumper;
  10. use Spreadsheet::WriteExcel;
  11. use Email::MIME;
  12. use Email::Sender::Simple qw(sendmail);
  13. use Email::Sender::Transport::SMTP;
  14. use Email::Sender::Transport::SMTP::TLS;
  15. use Log::Minimal;
  16. use Try::Tiny;
  17. use Config::IniFiles;
  18. use File::Basename;
  19.  
  20. my $start_log = 0;
  21. my $start_pid = 0;
  22. my $start_cmd = "$^X $0 @ARGV";
  23. my %opts;
  24. &init();
  25. #my $conf = new Config::General( $opts{c} );
  26. #my %config = $conf->getall;
  27. my %config;
  28. tie %config, 'Config::IniFiles', ( -file => $opts{c}, -handle_trailing_comment => 1 );
  29. for my $k1( keys %config ){
  30. for my $k2( keys %{$config{$k1}} ){
  31. if( $config{$k1}{$k2} =~ /(.*)\.\$(.*)/ ){
  32. $config{$k1}{$k2} = $config{$1}{$2};
  33. }
  34. }
  35. }
  36. my $log_dir = dirname(__FILE__) . '/storage/';
  37. if( ! -d $log_dir ){
  38. if( ! mkdir $log_dir,0777 ){
  39. print "create $log_dir failed:$!\n";
  40. exit;
  41. }
  42. }
  43. my $class4_log_file = $log_dir."/class4.log";
  44. my $open_log = 1;
  45. if( ! open CLASS4_LOG, ">>$class4_log_file" ){
  46. print "open $class4_log_file failed:$!\n";
  47. $open_log = 0;
  48. }
  49. $ENV{LM_DEBUG} = 1;
  50. local $Log::Minimal::AUTODUMP = 1;
  51. local $Log::Minimal::LOG_LEVEL = $config{script_log}{ log_level };
  52. local $Log::Minimal::PRINT = sub {
  53. my ( $time, $type, $message, $trace, $raw_message ) = @_;
  54. print "$time [$type] $trace $message\n";
  55. print CLASS4_LOG "$time [$type] $trace $message\n" if( $open_log );
  56. };
  57. infof( "start script : $start_cmd" );
  58. my $db_name = $config{db}{ dbname };
  59. my $db_host = $config{db}{ hostaddr };
  60. my $db_port = $config{db}{ port };
  61. my $db_username = $config{db}{ user };
  62. my $db_password = $config{db}{ password };
  63. my $dbh = DBI->connect( "dbi:Pg:dbname=$db_name; host=$db_host; port=$db_port",
  64. $db_username,
  65. $db_password,
  66. { AutoCommit => 1, pg_server_prepare => 1 } );
  67. if( ! $dbh ){
  68. critf( $DBI::errstr );
  69. &do_exit();
  70. };
  71.  
  72. # 记录脚本的开始时间
  73. my $sql = "INSERT INTO scheduler_log (script_name, start_time) VALUES ('Dialy Summary Report', current_timestamp(0))";
  74. my $sth = $dbh->prepare( $sql );
  75. $sth->execute();
  76. my $scheduler_log_id = $dbh->last_insert_id(undef,undef, "scheduler_log", "id");
  77.  
  78. my $file_dir = $log_dir."/summary_report";
  79. if( ! -d $file_dir ){
  80. if( ! mkdir $file_dir,0777 ){
  81. critf( "create $file_dir failed:$!" );
  82. &do_exit();
  83. }
  84. }
  85. my $run_pid_file = $log_dir."/dnl_summary_report.pid";
  86.  
  87.  
  88. &start_log();
  89.  
  90.  
  91.  
  92. my %mail_template_data;
  93. my $mail_from_ref = $dbh->selectrow_hashref("SELECT auto_summary_from as from from mail_tmplate");
  94. my $mail_hr;
  95. if( $mail_from_ref && $mail_from_ref->{ from } && $mail_from_ref->{ from } ne 'Default' && $mail_from_ref->{ from } ne 'default' ){
  96. $mail_hr = $dbh->selectrow_hashref( "SELECT loginemail,smtp_host as smtphost, smtp_port as smtpport, username as emailusername, password as emailpassword, email as fromemail, secure as smtp_secure FROM mail_sender WHERE id = $mail_from_ref->{ from }" );
  97. }else{
  98. $mail_hr = $dbh->selectrow_hashref( "SELECT * from system_parameter" );
  99. }
  100. #my $mail_hr = $dbh->selectrow_hashref( "SELECT * from system_parameter" );
  101. $mail_template_data{ from } = $mail_hr->{ fromemail };
  102. $mail_template_data{ fake_from} = $mail_hr->{ emailname };
  103. $mail_template_data{ server } = $mail_hr->{ smtphost };
  104. $mail_template_data{ port } = $mail_hr->{ smtpport };
  105. $mail_template_data{ username } = $mail_hr->{ emailusername };
  106. $mail_template_data{ password } = $mail_hr->{ emailpassword };
  107. $mail_template_data{ auth } = ($mail_hr->{ loginemail } eq "true") ? 1 : 0;
  108. #$mail_template_data{ tls } = $config{ sendmail_tls_require };
  109. $mail_template_data{ tls } = $mail_hr->{ smtp_secure };
  110. $mail_template_data{ ssl } = ($mail_hr->{ smtp_secure } == 2) ? 1 : 0;
  111. my $mail_template_hr = $dbh->selectrow_hashref( "SELECT * from mail_tmplate" );
  112. $mail_template_data{ cc } = $mail_template_hr->{ auto_summary_cc };
  113.  
  114. if( $opts{a} ){
  115. #&start_pid( $run_pid_file );
  116.  
  117. my $report_client_sql = "
  118. SELECT
  119. 0 as period_type,
  120. c4_client_balance.balance,
  121. name,
  122. client.client_id,
  123. last_autoreport_time,
  124. auto_send_zone,
  125. case auto_summary_group_by when 0 then 'country' when 1 then 'code_name' when 2 then 'code' else 'country' end as auto_summary_group_by,
  126. to_char(current_date-1,'YYYY-MM-DD 00:00:00')||COALESCE(auto_send_zone,'+00') as start_time,
  127. to_char(current_date-1,'YYYY-MM-DD 23:59:59')||COALESCE(auto_send_zone,'+00') as end_time,
  128. to_char(current_date,'YYYYMMDD') as current_table_suffix
  129. FROM
  130. client
  131. left join
  132. c4_client_balance
  133. on client.client_id::text=c4_client_balance.client_id
  134. WHERE
  135. is_auto_summary = true and
  136. status=true and
  137. (auto_summary_period is null or auto_summary_period = 24) and
  138. (last_autoreport_time is null or last_autoreport_time < current_date::timestamp with time zone at time zone auto_send_zone::interval) and
  139. current_timestamp(0) at time zone auto_send_zone::interval > current_date::timestamp with time zone and
  140. (auto_summary_hour is null or EXTRACT(HOUR FROM now() at time zone auto_send_zone::interval)>=auto_summary_hour)
  141.  
  142. union
  143.  
  144. SELECT
  145. 0 as period_type,
  146. c4_client_balance.balance,
  147. name,
  148. client.client_id,
  149. last_autoreport_time,
  150. auto_send_zone,
  151. case auto_summary_group_by when 0 then 'country' when 1 then 'code_name' when 2 then 'code' else 'country' end as auto_summary_group_by,
  152. to_char(current_timestamp(0)-auto_summary_period*interval '1 hour','YYYY-MM-DD HH24:00:00') as start_time,
  153. to_char(current_timestamp(0)-interval '1 hour','YYYY-MM-DD HH24:59:59') as end_time,
  154. to_char(current_date,'YYYYMMDD') as current_table_suffix
  155. FROM
  156. client
  157. left join
  158. c4_client_balance
  159. on client.client_id::text=c4_client_balance.client_id
  160. WHERE
  161. is_auto_summary = true and
  162. status=true and
  163. (auto_summary_period is not null and auto_summary_period != 24) and
  164. EXTRACT(HOUR FROM now() at time zone auto_send_zone::interval)::integer%auto_summary_period=0
  165. ";
  166. print $report_client_sql;
  167. my $report_client_hr = $dbh->selectall_hashref( $report_client_sql, "client_id" );
  168. for my $k1( keys %$report_client_hr ){
  169. &mod_data( "update client set last_autoreport_time=current_timestamp(0) where client_id=?","$k1" );
  170. }
  171. my $has_report_data = 0;
  172. for my $k1( keys %$report_client_hr ){
  173. my $timeout = 0;
  174. my $current_table_suffix = $report_client_hr->{ $k1 }->{ current_table_suffix };
  175. if ($has_report_data != 1){
  176. while( 1 ){
  177. my $report_time_hr = $dbh->selectrow_hashref( "SELECT report_time from cdr_report_detail$current_table_suffix where report_time >= '$report_client_hr->{ $k1 }->{ end_time }' limit 1" );
  178. if ( ! $report_time_hr ){
  179. if ($timeout < 10){
  180. $timeout += 1;
  181. debugf( "after $report_client_hr->{ $k1 }->{ end_time } not found report,wait" );
  182. sleep(60);
  183. next;
  184. }else{
  185. debugf( "after $report_client_hr->{ $k1 }->{ end_time } not found report,exit" );
  186. exit;
  187. }
  188. }else{
  189. $has_report_data = 1;
  190. last;
  191. }
  192. }
  193. }
  194.  
  195. my $report_file = $file_dir."/".$report_client_hr->{ $k1 }->{ name }."_summary_report_".substr($report_client_hr->{ $k1 }->{ start_time },0,10).".xls";
  196. if( &create_report( $report_client_hr->{ $k1 }->{ start_time },$report_client_hr->{ $k1 }->{ end_time },$k1,$report_client_hr->{ $k1 }->{ auto_summary_group_by },$report_file,$report_client_hr->{ $k1 }->{ balance } ) < 0 ){
  197. next;
  198. }
  199.  
  200. my $send_mail_res = &send_mail( %mail_template_data );
  201. if( $send_mail_res ){
  202. warnf( "send mail failed : $send_mail_res" );
  203. }else{
  204. #&mod_data( "update client set last_autoreport_time=current_timestamp(0) where client_id=?","$k1" );
  205. }
  206. my $send_mail_log = [ $send_mail_res, strftime("%Y-%m-%d %H:%M:%S", gmtime), $k1, $mail_template_data{ to }, $report_file, 2, 0, $mail_template_data{ subject }, $mail_template_data{ content } ];
  207. &send_mail_log( $send_mail_log );
  208. }
  209. }else{
  210. my $client_hr = $dbh->selectrow_hashref( "select * from client where client_id=$opts{i}" );
  211. if( $client_hr ){
  212. my $report_file = $file_dir."/".$client_hr->{ name }."_summary_report_".substr($opts{s},0,10).".xls";
  213. #if( &create_report_cdr( $opts{s}, $opts{e}, $opts{i}, $opts{g}, $report_file, $opts{b} ) == 0 ){
  214. my $send_mail_res = &send_mail( %mail_template_data );
  215. if( $send_mail_res ){
  216. warnf( "send mail failed : $send_mail_res" );
  217. }
  218. my $send_mail_log = [ $send_mail_res, strftime("%Y-%m-%d %H:%M:%S", gmtime), $opts{i}, $mail_template_data{ to }, $report_file, 2, 0, $mail_template_data{ subject }, $mail_template_data{ content } ];
  219. &send_mail_log( $send_mail_log );
  220. #}
  221. }else{
  222. debugf( "client $opts{i} not exists" );
  223. }
  224. }
  225.  
  226. # 记录脚本的结束时间
  227. my $sql = "update scheduler_log set end_time = current_timestamp(0) where id =?";
  228. my $sth = $dbh->prepare( $sql );
  229. $sth->execute($scheduler_log_id);
  230. infof( "finished script" );
  231. &do_exit();
  232.  
  233.  
  234. sub init()
  235. {
  236. my $opt_string = 'ahc:s:e:i:g:b:';
  237. getopts( "$opt_string", \%opts ) or usage();
  238. usage() if $opts{h};
  239. }
  240.  
  241. sub usage()
  242. {
  243. print "perl class4_summary_report.pl -c class4.conf -a or -s start_time -e end_time -i client_id -g code/code_name/country -b end_time_balance\n";
  244. exit;
  245. }
  246.  
  247. sub mod_data {
  248. my( $data_sql,$data_value ) = @_;
  249. my @value_array;
  250. if( ref( $data_value ) eq "ARRAY" ){
  251. @value_array = @$data_value;
  252. }else{
  253. @value_array = split( /,/, $data_value, -1 );
  254. }
  255.  
  256. my $sth = $dbh->prepare( $data_sql );
  257. $sth->execute( @value_array );
  258. if( $sth->state ){
  259. critf( "update database error sql:%s,data:%s,error:%s", $data_sql, join(",",@value_array), $sth->errstr );
  260. return 1;
  261. }
  262. return 0;
  263. }
  264.  
  265. sub start_log(){
  266. my @script_name = split(/\//,$0);
  267.  
  268. &mod_data( "update scheduler set last_run = current_timestamp(0) where script_name=?", "@script_name[$#script_name]" );
  269.  
  270. $start_log = 1;
  271. }
  272.  
  273. sub start_pid(){
  274. my( $pid_file ) = @_;
  275.  
  276. if( -e $pid_file ){
  277. my $pid = `head -1 $pid_file`;
  278. debugf( "last pid : $pid" );
  279. if( $pid !~ /^\s*$/ && -e "/proc/$pid" ){
  280. debugf( "pid $pid alreay running" );
  281. &do_exit();
  282. }
  283. }
  284. if( ! open PID_FILE, ">$pid_file" ){
  285. critf( "open pid file error:$!" );
  286. &do_exit();
  287. }
  288.  
  289. print PID_FILE "$$";
  290. close PID_FILE;
  291.  
  292. debugf( "new pid : $$" );
  293.  
  294. $start_pid = 1;
  295.  
  296. return 0;
  297. }
  298.  
  299. sub end_pid(){
  300. my( $pid_file ) = @_;
  301.  
  302. debugf( "process done" );
  303.  
  304. if( ! open PID_FILE, ">$pid_file" ){
  305. critf( "close pid file error:$!" );
  306. }
  307. print PID_FILE "";
  308. close PID_FILE;
  309.  
  310. return 0;
  311. }
  312.  
  313. sub do_exit(){
  314. &start_log() if( $start_log );
  315. $dbh->disconnect if( $dbh );
  316. close CLASS4_LOG;
  317. &end_pid( $run_pid_file ) if( $start_pid );
  318.  
  319. exit;
  320. }
  321.  
  322.  
  323. sub send_mail(){
  324. my ( %mail_data ) = @_;
  325. my $mail_from = $mail_data{ from };
  326. my $mail_fake_from = $mail_data{ fake_from};
  327. my $mail_server = $mail_data{ server };
  328. my $mail_port = $mail_data{ port };
  329. my $mail_auth = $mail_data{ auth };
  330. my $mail_username = $mail_data{ username };
  331. my $mail_password = $mail_data{ password };
  332. my $mail_tls_require = $mail_data{ tls };
  333. my $mail_to = $mail_data{ to };
  334. my $mail_subject = $mail_data{ subject };
  335. my $mail_content = $mail_data{ content };
  336. my $mail_attach_files = $mail_data{ files };
  337. my $mail_ssl = $mail_data{ ssl };
  338. my $mail_cc = $mail_data{ cc };
  339.  
  340. my $attach_data;
  341. if( $mail_attach_files ){
  342. for my $k1( @$mail_attach_files ){
  343. if( ! open( MAIL_DATA, "<$k1" ) ){
  344. critf( "open $k1 failed,$!" );
  345. return "open $k1 failed,$!";
  346. }else{
  347. local $/;
  348. #my @file_data = <MAIL_DATA>;
  349. my @mail_attach_files = split( /\//, "$k1" );
  350. $attach_data->{ @mail_attach_files[$#mail_attach_files] } = <MAIL_DATA>;
  351. close MAIL_DATA;
  352. }
  353. }
  354. }
  355.  
  356. my $email;
  357. if( $mail_attach_files ){
  358. my @parts;
  359. push @parts, Email::MIME->create(
  360. attributes => {
  361. content_type => "text/html",
  362. # charset => "us-ascii",
  363. charset => "utf-8",
  364. encoding => "base64",
  365. },
  366. body_str => $mail_content,
  367. );
  368. for my $k1( keys %{ $attach_data } ){
  369. push @parts, Email::MIME->create(
  370. attributes => {
  371. content_type => "application/octet-stream",
  372. encoding => "base64",
  373. filename => "$k1",
  374. },
  375. body => $attach_data->{ $k1 },
  376. );
  377. }
  378. $email = Email::MIME->create(
  379. header_str => [
  380. From => $mail_from,
  381. To => $mail_to,
  382. Subject => $mail_subject,
  383. Cc => $mail_cc,
  384. ],
  385. parts => [ @parts ],
  386. );
  387. }else{
  388. $email = Email::MIME->create(
  389. header_str => [
  390. From => $mail_from,
  391. To => $mail_to,
  392. Subject => $mail_subject,
  393. Cc => $mail_cc,
  394. ],
  395. body => $mail_content,
  396. attributes => {
  397. content_type => "text/html",
  398. # charset => "us-ascii",
  399. charset => "utf-8",
  400. encoding => "base64",
  401. },
  402. );
  403. }
  404.  
  405. #print $email->as_string;
  406. my $send_res;
  407. if( $mail_auth == 0 ){
  408. try{
  409. sendmail(
  410. $email
  411. );
  412. }catch{
  413. $send_res = $_;
  414. }
  415. }elsif( $mail_tls_require == 1 ){
  416. try{
  417. sendmail(
  418. $email,
  419. {
  420. transport => Email::Sender::Transport::SMTP::TLS->new(
  421. host => $mail_server,
  422. port => $mail_port,
  423. username => $mail_username,
  424. password => $mail_password,
  425. ),
  426. }
  427. );
  428. }catch{
  429. $send_res = $_;
  430. }
  431. }else{
  432. try{
  433. sendmail(
  434. $email,
  435. {
  436. transport => Email::Sender::Transport::SMTP->new(
  437. host => $mail_server,
  438. port => $mail_port,
  439. sasl_username => $mail_username,
  440. sasl_password => $mail_password,
  441. ssl => $mail_data{ ssl },
  442. ),
  443. }
  444. );
  445. }catch{
  446. $send_res = $_;
  447. }
  448. }
  449. #print "send result : $send_res\n";
  450. $send_res =~ s#Trace begun[\s\S]*##g;
  451. return $send_res;
  452. }
  453.  
  454.  
  455. sub create_report(){
  456. my( $report_start_time,$report_end_time,$client_id,$group_type,$file_name,$client_balance ) = @_;
  457. my $report_sql;
  458. $report_sql = "select ingress_client_id,egress_client_id,ingress_prefix,ingress_code,not_zero_calls,ingress_total_calls,ingress_bill_time,ingress_call_cost,egress_total_calls,egress_bill_time,egress_call_cost,duration from cdr_report_detail where report_time between '$report_start_time' and '$report_end_time' and (ingress_client_id=$client_id or egress_client_id=$client_id)" if( $group_type eq "code" );
  459. $report_sql = "select ingress_client_id,egress_client_id,ingress_prefix,ingress_code_name,not_zero_calls,ingress_total_calls,ingress_bill_time,ingress_call_cost,egress_total_calls,egress_bill_time,egress_call_cost,duration from cdr_report_detail where report_time between '$report_start_time' and '$report_end_time' and (ingress_client_id=$client_id or egress_client_id=$client_id)" if( $group_type eq "code_name" );
  460. $report_sql = "select ingress_client_id,egress_client_id,ingress_prefix,ingress_country,not_zero_calls,ingress_total_calls,ingress_bill_time,ingress_call_cost,egress_total_calls,egress_bill_time,egress_call_cost,duration from cdr_report_detail where report_time between '$report_start_time' and '$report_end_time' and (ingress_client_id=$client_id or egress_client_id=$client_id)" if( $group_type eq "country" );
  461.  
  462. my $client_balance_hr = $dbh->selectrow_hashref( "select * from c4_client_balance where client_id='$client_id'" );
  463. $client_balance = $client_balance_hr->{ balance } if( ! defined $client_balance );
  464. #my $client_balance = &report_balance( $client_id, $report_start_time, $report_end_time );
  465. my $buy_call_time = 0;
  466. my $sell_call_time = 0;
  467. my $buy_calls = 0;
  468. my $buy_not_zero_calls = 0;
  469. my $buy_mins = 0;
  470. my $buy_amount = 0;
  471. my $sell_calls = 0;
  472. my $sell_not_zero_calls = 0;
  473. my $sell_mins = 0;
  474. my $sell_amount = 0;
  475. my $buy_data;
  476. my $sell_data;
  477. my $report_res = $dbh->selectall_arrayref( $report_sql );
  478. for my $k1( @$report_res ){
  479. #print "@$k1\n";
  480. if( $k1->[0] == $client_id ){
  481. ##buy data
  482. $buy_data->{ $k1->[2] }->{ $k1->[3] }->{ total_calls } += $k1->[5];
  483. $buy_data->{ $k1->[2] }->{ $k1->[3] }->{ not_zero_calls } += $k1->[4];
  484. $buy_data->{ $k1->[2] }->{ $k1->[3] }->{ bill_time } += $k1->[6];
  485. $buy_data->{ $k1->[2] }->{ $k1->[3] }->{ cost } += $k1->[7];
  486. $buy_data->{ $k1->[2] }->{ $k1->[3] }->{ call_time } += $k1->[11];
  487.  
  488. $buy_calls += $k1->[5];
  489. $buy_not_zero_calls += $k1->[4];
  490. $buy_mins += $k1->[6];
  491. $buy_amount += $k1->[7];
  492. $buy_call_time += $k1->[11];
  493. }
  494. if( $k1->[1] == $client_id ){
  495. ##sell data
  496. $sell_data->{ $k1->[3] }->{ total_calls } += $k1->[8];
  497. $sell_data->{ $k1->[3] }->{ not_zero_calls } += $k1->[4];
  498. $sell_data->{ $k1->[3] }->{ bill_time } += $k1->[9];
  499. $sell_data->{ $k1->[3] }->{ cost } += $k1->[10];
  500. $sell_data->{ $k1->[3] }->{ call_time } += $k1->[11];
  501.  
  502. $sell_calls += $k1->[8];
  503. $sell_not_zero_calls += $k1->[4];
  504. $sell_mins += $k1->[9];
  505. $sell_amount += $k1->[10];
  506. $sell_call_time += $k1->[11];
  507. }
  508. }
  509. $buy_not_zero_calls += 0;
  510. $sell_not_zero_calls += 0;
  511. if( ($buy_not_zero_calls + $sell_not_zero_calls) == 0 ){
  512. debugf( "no cdr found,exit" );
  513. return -1;
  514. }
  515.  
  516. ###excel
  517. my $alert_file_hr;
  518. push @{ $alert_file_hr->{ buy } }, ["Tech Prefix",$group_type,"Total Call","Success Call","Bill Min","Avg Rate","Cost","Call Min"];
  519. #print "BUY DATA : Tech Prefix,$group_type,Total Call,Success Call,Bill Min,Avg Rate,Cost,Call Min\n";
  520. for my $k1( keys %$buy_data ){
  521. for my $k2( keys %{ $buy_data->{ $k1 } } ){
  522. $buy_data->{ $k1 }->{ $k2 }->{ bill_time } = $buy_data->{ $k1 }->{ $k2 }->{ bill_time }/60;
  523. $buy_data->{ $k1 }->{ $k2 }->{ call_time } = $buy_data->{ $k1 }->{ $k2 }->{ call_time }/60;
  524. my $avg_rate = $buy_data->{ $k1 }->{ $k2 }->{ bill_time } == 0 ?0 : $buy_data->{ $k1 }->{ $k2 }->{ cost }/$buy_data->{ $k1 }->{ $k2 }->{ bill_time };
  525. #print "$k1,$k2,$buy_data->{ $k1 }->{ $k2 }->{ total_calls },$buy_data->{ $k1 }->{ $k2 }->{ not_zero_calls },$buy_data->{ $k1 }->{ $k2 }->{ bill_time },$avg_rate,$buy_data->{ $k1 }->{ $k2 }->{ cost },$buy_data->{ $k1 }->{ $k2 }->{ call_time }\n";
  526. push @{ $alert_file_hr->{ buy } }, [$k1,$k2,$buy_data->{ $k1 }->{ $k2 }->{ total_calls },$buy_data->{ $k1 }->{ $k2 }->{ not_zero_calls },$buy_data->{ $k1 }->{ $k2 }->{ bill_time },$avg_rate,$buy_data->{ $k1 }->{ $k2 }->{ cost },$buy_data->{ $k1 }->{ $k2 }->{ call_time }];
  527. }
  528. }
  529.  
  530. push @{ $alert_file_hr->{ sell } }, [$group_type,"Total Call","Success Call","Bill Min","Avg Rate","Cost","Call Min"];
  531. #print "SELL DATA : $group_type,Total Call,Success Call,Bill Min,Avg Rate,Cost,Call min\n";
  532. for my $k1( keys %$sell_data ){
  533. $sell_data->{ $k1 }->{ bill_time } = $sell_data->{ $k1 }->{ bill_time }/60;
  534. $sell_data->{ $k1 }->{ call_time } = $sell_data->{ $k1 }->{ call_time }/60;
  535. my $avg_rate = $sell_data->{ $k1 }->{ bill_time } == 0 ?0 : $sell_data->{ $k1 }->{ cost }/$sell_data->{ $k1 }->{ bill_time };
  536. #print "$k1,$sell_data->{ $k1 }->{ total_calls },$sell_data->{ $k1 }->{ not_zero_calls },$sell_data->{ $k1 }->{ bill_time },$avg_rate,$sell_data->{ $k1 }->{ cost },$sell_data->{ $k1 }->{ call_time }\n";
  537. push @{ $alert_file_hr->{ sell } }, [$k1,$sell_data->{ $k1 }->{ total_calls },$sell_data->{ $k1 }->{ not_zero_calls },$sell_data->{ $k1 }->{ bill_time },$avg_rate,$sell_data->{ $k1 }->{ cost },$sell_data->{ $k1 }->{ call_time }];
  538. }
  539.  
  540. &write_excel( $file_name, $alert_file_hr );
  541.  
  542.  
  543. $mail_template_data{ subject } = $mail_template_hr->{ auto_summary_subject };
  544. $mail_template_data{ content } = $mail_template_hr->{ auto_summary_content };
  545. my $client_info = $dbh->selectrow_hashref( "SELECT * from client where client_id=$client_id" );
  546. if( $client_info->{ auto_daily_balance_recipient } == 1 ){
  547. $mail_template_data{ to } = $mail_hr->{ system_admin_email };
  548. }elsif( $client_info->{ auto_daily_balance_recipient } == 0 ){
  549. $mail_template_data{ to } = $client_info->{ email };
  550. }elsif( $client_info->{ auto_daily_balance_recipient } == 2 ){
  551. $mail_template_data{ to } = "$mail_hr->{ system_admin_email },$client_info->{ email }";
  552. }
  553.  
  554. $mail_template_data{ files } = ["$file_name"];
  555. $buy_mins = $buy_mins/60;
  556. $sell_mins = $sell_mins/60;
  557. $buy_call_time = $buy_call_time/60;
  558. $sell_call_time = $sell_call_time/60;
  559. $buy_mins = sprintf( "%.3f", $buy_mins );
  560. $buy_amount = sprintf( "%.3f", $buy_amount );
  561. $client_balance = sprintf( "%.3f", $client_balance );
  562. $sell_mins = sprintf( "%.3f", $sell_mins );
  563. $sell_amount = sprintf( "%.3f", $sell_amount );
  564. $buy_call_time = sprintf( "%.3f", $buy_call_time );
  565. $sell_call_time = sprintf( "%.3f", $sell_call_time );
  566.  
  567. $mail_template_data{ subject } =~ s/{client_name}/$client_info->{ name }/g;
  568. $mail_template_data{ subject } =~ s/{client_name}/$client_info->{ name }/g;
  569. $mail_template_data{ subject } =~ s/{begin_time}/$report_start_time/g;
  570. $mail_template_data{ subject } =~ s/{end_time}/$report_end_time/g;
  571. $mail_template_data{ subject } =~ s/{customer_gmt}//g;
  572. $mail_template_data{ subject } =~ s/{total_call_buy}/$buy_calls/g;
  573. $mail_template_data{ subject } =~ s/{total_not_zero_calls_buy}/$buy_not_zero_calls/g;
  574. $mail_template_data{ subject } =~ s/{total_success_call_buy}/$buy_calls/g;
  575. $mail_template_data{ subject } =~ s/{total_billed_min_buy}/$buy_mins/g;
  576. $mail_template_data{ subject } =~ s/{total_billed_amount_buy}/$buy_amount/g;
  577. $mail_template_data{ subject } =~ s/{credit_limit}//g;
  578. $mail_template_data{ subject } =~ s/{remaining_credit}//g;
  579. $mail_template_data{ subject } =~ s/{balance}/$client_balance/g;
  580. $mail_template_data{ subject } =~ s/{total_call_sell}/$sell_calls/g;
  581. $mail_template_data{ subject } =~ s/{total_not_zero_calls_sell}/$sell_not_zero_calls/g;
  582. $mail_template_data{ subject } =~ s/{total_success_call_sell}/$sell_calls/g;
  583. $mail_template_data{ subject } =~ s/{total_billed_min_sell}/$sell_mins/g;
  584. $mail_template_data{ subject } =~ s/{total_billed_amount_sell}/$sell_amount/g;
  585. $mail_template_data{ subject } =~ s/{buy_total_duration}/$buy_call_time/g;
  586. $mail_template_data{ subject } =~ s/{sell_total_duration}/$sell_call_time/g;
  587. $mail_template_data{ subject } =~ s/{switch_alias}/$mail_hr->{ switch_alias }/g;
  588. $mail_template_data{ content } =~ s/{client_name}/$client_info->{ name }/g;
  589. $mail_template_data{ content } =~ s/{begin_time}/$report_start_time/g;
  590. $mail_template_data{ content } =~ s/{end_time}/$report_end_time/g;
  591. $mail_template_data{ content } =~ s/{customer_gmt}//g;
  592. $mail_template_data{ content } =~ s/{total_call_buy}/$buy_calls/g;
  593. $mail_template_data{ content } =~ s/{total_not_zero_calls_buy}/$buy_not_zero_calls/g;
  594. $mail_template_data{ content } =~ s/{total_success_call_buy}/$buy_calls/g;
  595. $mail_template_data{ content } =~ s/{total_billed_min_buy}/$buy_mins/g;
  596. $mail_template_data{ content } =~ s/{total_billed_amount_buy}/$buy_amount/g;
  597. $mail_template_data{ content } =~ s/{credit_limit}//g;
  598. $mail_template_data{ content } =~ s/{remaining_credit}//g;
  599. $mail_template_data{ content } =~ s/{balance}/$client_balance/g;
  600. $mail_template_data{ content } =~ s/{total_call_sell}/$sell_calls/g;
  601. $mail_template_data{ content } =~ s/{total_not_zero_calls_sell}/$sell_not_zero_calls/g;
  602. $mail_template_data{ content } =~ s/{total_success_call_sell}/$sell_calls/g;
  603. $mail_template_data{ content } =~ s/{total_billed_min_sell}/$sell_mins/g;
  604. $mail_template_data{ content } =~ s/{total_billed_amount_sell}/$sell_amount/g;
  605. $mail_template_data{ content } =~ s/{buy_total_duration}/$buy_call_time/g;
  606. $mail_template_data{ content } =~ s/{sell_total_duration}/$sell_call_time/g;
  607. $mail_template_data{ content } =~ s/{switch_alias}/$mail_hr->{ switch_alias }/g;
  608.  
  609. return 0;
  610. }
  611.  
  612.  
  613. sub report_balance(){
  614. my( $id_client, $balance_start_time, $balance_end_time ) = @_;
  615.  
  616. #$balance_start_time = "2000-01-01" if( ! $balance_start_time );
  617. $balance_start_time = "2000-01-01";
  618.  
  619. my $payment_hr;
  620. my $cdr_cost_hr;
  621. my $reset_balance = 0;
  622. my $reset_hr = $dbh->selectrow_hashref( "select * from client_payment where client_id = $id_client and payment_type = 14 order by receiving_time desc limit 1" );
  623. if( $reset_hr ){
  624. $reset_balance = $reset_hr->{ amount } + $reset_hr->{ egress_amount };
  625. $payment_hr = $dbh->selectrow_hashref( "select sum(case when payment_type=4 then amount else 0 end) as invoice_received,sum(case when payment_type=5 then amount else 0 end) as payment_received,sum(case when payment_type=8 then amount else 0 end) as credit_note_sent,sum(case when payment_type=12 then amount else 0 end) as debit_note_sent,sum(case when payment_type=3 then amount else 0 end) as invoice_sent,sum(case when payment_type=6 then amount else 0 end) as payment_sent,sum(case when payment_type=7 then amount else 0 end) as credit_note_received,sum(case when payment_type=11 then amount else 0 end) as debit_note_received from client_payment where client_id=$id_client and payment_time between '$balance_start_time' and '$balance_end_time' and payment_time >= '$reset_hr->{ payment_time }'" );
  626. $cdr_cost_hr = $dbh->selectrow_hashref( "select sum(case when ingress_client_id=$id_client then ingress_call_cost else 0 end) as total_ingress_cost, sum(case when egress_client_id=$id_client then egress_call_cost else 0 end) as total_egress_cost from cdr_report where report_time between '$balance_start_time' and '$balance_end_time' and report_time >= '$reset_hr->{ payment_time }' and (ingress_client_id=$id_client or egress_client_id=$id_client)" );
  627. }else{
  628. $payment_hr = $dbh->selectrow_hashref( "select sum(case when payment_type=4 then amount else 0 end) as invoice_received,sum(case when payment_type=5 then amount else 0 end) as payment_received,sum(case when payment_type=8 then amount else 0 end) as credit_note_sent,sum(case when payment_type=12 then amount else 0 end) as debit_note_sent,sum(case when payment_type=3 then amount else 0 end) as invoice_sent,sum(case when payment_type=6 then amount else 0 end) as payment_sent,sum(case when payment_type=7 then amount else 0 end) as credit_note_received,sum(case when payment_type=11 then amount else 0 end) as debit_note_received from client_payment where client_id=$id_client and payment_time between '$balance_start_time' and '$balance_end_time'" );
  629. $cdr_cost_hr = $dbh->selectrow_hashref( "select sum(case when ingress_client_id=$id_client then ingress_call_cost else 0 end) as total_ingress_cost, sum(case when egress_client_id=$id_client then egress_call_cost else 0 end) as total_egress_cost from cdr_report where report_time between '$balance_start_time' and '$balance_end_time' and (ingress_client_id=$id_client or egress_client_id=$id_client)" );
  630. }
  631.  
  632. debugf( $reset_hr );
  633. debugf( $payment_hr );
  634. debugf( $cdr_cost_hr );
  635.  
  636. return ($reset_balance
  637. +$payment_hr->{ invoice_received }
  638. +$payment_hr->{ payment_received }
  639. +$payment_hr->{ credit_note_sent }
  640. +$payment_hr->{ debit_note_sent }
  641. +$cdr_cost_hr->{ total_egress_cost }
  642.  
  643. -$payment_hr->{ invoice_sent }
  644. -$payment_hr->{ payment_sent }
  645. -$payment_hr->{ credit_note_received }
  646. -$payment_hr->{ debit_note_received }
  647. -$cdr_cost_hr->{ total_ingress_cost }
  648. );
  649.  
  650. }
  651.  
  652.  
  653. sub send_mail_log(){
  654. my ( $log_data ) = @_;
  655.  
  656. &mod_data( "insert into email_log(email_res,send_time,client_id,email_addresses,files,type,status,subject,content) values(?,?,?,?,?,?,?,?,?)", $log_data );
  657.  
  658. return 0;
  659. }
  660.  
  661. sub write_excel(){
  662. my( $filename, $filecontent ) = @_;
  663.  
  664. my $workbook = Spreadsheet::WriteExcel->new( $filename );
  665. if( ! defined $workbook ){
  666. critf( "Problems creating new Excel file $filename: $!" );
  667. return -1;
  668. }
  669. for my $k1( sort keys %{ $filecontent } ){
  670. my $worksheet = $workbook->add_worksheet( substr( $k1, 0, 31 ) );
  671. $worksheet->write_col( 0, 0, $filecontent->{ $k1 } );
  672. }
  673. $workbook->close();
  674.  
  675. return 0;
  676. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
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!