Extract data from MySQL using Perl for statistical analysis code

The requirements are as follows:

1. There is an sms_info table in database mydata1. The record format is as follows:

 
 

  
  
+ ----------- + ---------------- + ------ + -----
  
  
| Field |
  
  
+ ----------- + ---------------- + ------ + -----
  
  
| Id | 12345
  
  
| Number | 136123456789 indicates the contact number of a person.
  
  
| Service | check_ping nagios Monitoring service name
  
  
| Message | ping failure .................. Nagios alarm information
  
  
| Send_time | 13:25:16 SMS sending time
  
  
| Is_send | y indicates that the SMS is successfully sent, and n indicates that the SMS is failed.
  
  
+ ----------- + ---------------- + ------ + -----
 
 

The purpose of this table is to save the service alert information, the alert time, the alert information, the contact person, and whether the SMS is successfully sent.

2. There is a contact table in database mydata2. The record format is as follows:

 
 

  
  
+ ----------- + ---------------- + ------ + -----
  
  
Id 12345.
  
  
Telephone 136123456789
  
  
Contact_name Zhang San
  
  
+ ----------- + ---------------- + ------ + -----
 
 

This table records the O & M personnel with phone numbers.

To achieve this, you need to calculate the number of text messages each sent to related O & M personnel for each service in March.

A service may have multiple contacts during the alarm. During the alarm, it needs to send a text message to send an alarm, and each contact needs to send a text message. Therefore, my task is to count the number of messages sent to each contact of this service during the period of January. The number of successes and failures.

Sample:

 
 

  
  
"Service Name Date: Number of successful O & M personnel failures
  
  
Check_category 0:05:06 Zhang San: 187 ********** 739 11
  
  
Check_category 0:05:08 Li Si: 186 ********** 750 2
  
  
Check_update 0:05:23 Ma 6: 151 ********** 538 17
  
  
Check_update 0:05:23 Zhang San: 187 ********** 539 16
  
  
Www. blo-httpd 0:06:56 Old Five: 135 *********** 6 0
  
  
Www. blo-httpd 0:09:56 Old Five: 135 *********** 9 1
 
 

The check_category service successfully sent 739 text messages to Michael Jacob in March and 11 failed messages.

Check_updateThe Service successfully sent 538 text messages to Ma 6th and failed 17 times in March.

The implementation code is as follows:

 
 

  
  
#! /Usr/bin/perl-w
  
  
 
  
  
Use strict;
  
  
Use DBI;
  
  
 
  
  
 
  
  
 
  
  
Open (OUTFILE ,">>Outfile ");
  
  
 
  
  
# Connecting to a database
  
  
My $Dbh=DBI->Connect ("DBI: mysql: database= Mydata1;Host= Localhost "," root "," 123456 ", {'raiseerror' =>1 });
  
  
My $Dbh_contact_name=DBI->Connect ("DBI: mysql: database= Mydata2;Host= Localhost "," root "," 123456 ", {'raiseerror' =>1 });
  
  
 
  
  
# Obtain all service names in January
  
  
My $Something= $ Dbh->Prepare ("select service from data_info where SUBSTRING (send_time, 2012) = '2017-01 'group by service order by send_time ");
  
  
 
  
  
# Set the format. Otherwise, the data retrieved by perl will be garbled.
  
  
$ Dbh->Do ("set names 'utf8 '");
  
  
$ Dbh_contact_name->Do ("set names 'utf8 '");
  
  
$ Something->Execute ();
  
  
 
  
  
# Output mode:
  
  
Print OUTFILE ("service name \ t date \ t o & M personnel \ t \ t \ t successes \ t failures \ t \ n ");
  
  
 
  
  
# Retrieve data from the database and save it to a file
  
  
 
  
  
While (my $Service_name= $ Something->Fetchrow_array ()){
  
  

  
  
My @Rows= (); # Save the data obtained from the database
  
  
 
  
  
 
  
  
# Count the number of messages sent by each service, which is the number of successfully sent messages and the number of failed messages.
  
  
 
  
  
My $Service_sms= $ Dbh->Prepare ("select service, send_time, number, count (*) from sms_info where SUBSTRING (send_time, 2012) = '2017-01' andService='$ Service_name'AndIs_send='Y'Group by number order by send_time ;");
  
  
$ Service_sms->Execute ();
  
  

  
  
 
  
  
# Obtain the total number of times each service sends messages
  
  
# Retrieve the phone number, used to find the relevant person name in the mydata2 Database
  
  
 
  
  
My $Total= $ Dbh->Prepare ("select COUNT (*) from sms_info where SUBSTRING (send_time, 2012) = '2014-01 'andService='$ Service_name'Group by number order by send_time ");
  
  
$ Total->Execute ();
  
  


  
  
While ($ rows [0], $ rows [1], $ rows [2], $ rows [3]) = $ service_sms->Fetchrow_array ()){
  
  
$ Rows [4] = $ total->Fetchrow_array ()-$ rows [3];
  
  
My $Contact_name= $ Dbh_contact_name->Prepare ("select contact_name from contact wherePhone= $ Rows [2] ");
  
  
$ Contact_name->Execute ();
  
  
 
  
  
$ Rows [5] = $ contact_name->Fetchrow_array ();
  
  
 
  
  
Print OUTFILE "$ rows [0] \ t $ rows [1] \ t $ rows [5]: $ rows [2] \ t $ rows [3] \ t $ rows [4] \ n ";
  
  
}
  
  
}
  
  
Close (OUTFILE );
  
  
 
  
  
$ Dbh->Disconnect ();
  
  
$ Dbh_contact_name->Disconnect ();