Making a new voting poll
Created on 110931 by André Guimarães, Switzernet
The aim of this document is to explain how to send mails to all clients registered in a server asking them for feedback on the quality of service.
Obtaining the customer list of a server:
Access db2.switzernet.com as root by ssh. Enter MySQL database porta-sip:
mysql -uroot -p porta-sip
Execute the following SQL query. Replace ‘SERVERIPADDRESS’ by the IP address of the server where you want to make a voting poll and ‘TABLENAME’ by the name of the table where you’ll keep the data.
SELECT CONCAT('INSERT INTO TABLENAME(domain, username,password,i_customer,email,name) VALUES (\'',`porta-sip`.location.domain,'\', \'', `porta-billing`.Accounts.id,'\', \'', SUBSTRING(MD5(RAND()) FROM 1 FOR 6), '\', \'' ,`porta-billing`.Accounts.i_customer,'\', \'' ,`porta-billing`.Customers.email,'\', \'', CONCAT(`porta-billing`.Customers.firstname,' ',`porta-billing`.Customers.lastname) , ' \');') as query FROM `porta-billing`.Accounts, `porta-billing`.Customers, `porta-sip`.location WHERE `porta-billing`.Accounts.i_customer = `porta-billing`.Customers.i_customer AND `porta-billing`.Accounts.id = `porta-sip`.location.username AND `porta-sip`.location.domain='SERVERIPADDRESS' AND NOT `porta-billing`.Customers.email is NULL AND `porta-billing`.Customers.email <> '' group by name into outfile '/tmp/dump.sql'; exit;
After exiting MySQL, execute the following commands to move the file to the www.switzernet.com server and disconnect from db2.
mv /tmp/dump.sql /tmp/`date +%y%m%d`-voting.sql
scp /tmp/`date +%y%m%d`-voting.sql andre-guimaraes@www.switzernet.com:/tmp
rm /tmp/`date +%y%m%d`-voting.sql
exit
Example:
db2:~# mysql -uroot -p porta-sip
Enter password:
$Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2556689
Server version: 5.0.51a-24+lenny4 (Debian)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> SELECT CONCAT('INSERT INTO accounts5 (domain,username,password,i_customer,email,name) VALUES (\'',`porta-sip`.location.domain,'\', \'', `porta-billing`.Accounts.id,'\', \'', SUBSTRING(MD5(RAND()) FROM 1 FOR 6), '\', \'' ,`porta-billing`.Accounts.i_customer,'\', \'' ,`porta-billing`.Customers.email,'\', \'', CONCAT(`porta-billing`.Customers.firstname,' ',`porta-billing`.Customers.lastname) , ' \');') as query FROM `porta-billing`.Accounts, `porta-billing`.Customers, `porta-sip`.location WHERE `porta-billing`.Accounts.i_customer = `porta-billing`.Customers.i_customer AND `porta-billing`.Accounts.id = `porta-sip`.location.username AND `porta-sip`.location.domain='213.251.169.218' AND NOT `porta-billing`.Customers.email is NULL AND `porta-billing`.Customers.email <> '' group by name into outfile '/tmp/dump.sql'; exit;
Query OK, 8 rows affected (0.01 sec)
Bye
db2:~# mv /tmp/dump.sql /tmp/`date +%y%m%d`-voting.sql
db2:~# scp /tmp/`date +%y%m%d`-voting.sql andre-guimaraes@www.switzernet.com:/tmp
andre-guimaraes@www.switzernet.com's password:
110930-voting.sql 100% 1405 1.4KB/s 00:00
db2:~# rm /tmp/`date +%y%m%d`-voting.sql
db2:~# exit
Importing the customer list to a BD
Connect to www.switzernet.com by ssh as root (you might need to login with your login first) and then enter database switzern_asterisk in MySQL:
mysql switzern_asterisk -uroot -p
Create a table and import the SQL file with the customer list. If you see errors while importing execute the MySQL rollback to cancel everything you’ve done. Replace ‘DATE’ to the correct file, ‘TABLE’ by the new table where the data will be kept and ‘SERVERIPADDRESS’ by the IP address of the server used before.
BEGIN;
CREATE TABLE `TABLE` ( `username` varchar(50) NOT NULL default '', `domain` varchar(100) NOT NULL default 'SERVERIPADDRESS', `name` varchar(41) NOT NULL default '', `email` varchar(99) default NULL, `password` varchar(6) NOT NULL default '', `date_upgrade` datetime default '2011-09-30', `advice` char(1) default NULL, `mail_status` char(1) NOT NULL default '0', `i_customer` varchar(30) NOT NULL default '', PRIMARY KEY (`i_customer`) );
source /tmp/DATE-voting.sql
If there weren’t any errors type
COMMIT;
else type
ROLLBACK;
and correct the errors.
You should also create a entry for yourself to verify that everything worked fine. Replace the fields in blue by your own.
INSERT INTO accounts5 (domain,username,password,i_customer,email,name) VALUES ('213.251.169.218', '41215500332', '496b64', '16166', 'andre.guimaraes@switzernet.com', 'Andre Guimaraes');
Example:
mirror1:~# mysql switzern_asterisk -uroot -p
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3507902
Server version: 5.0.51a-24+lenny4-log (Debian)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE `accounts5` ( `username` varchar(50) NOT NULL default '', `domain` varchar(100) NOT NULL default '213.251.169.218', `name` varchar(41) NOT NULL default '', `email` varchar(99) default NULL, `password` varchar(6) NOT NULL default '', `date_upgrade` datetime default '2011-09-30', `advice` char(1) default NULL, `mail_status` char(1) NOT NULL default '0', `i_customer` varchar(30) NOT NULL default '', PRIMARY KEY (`i_customer`) );
Query OK, 0 rows affected (0.00 sec)
mysql> source /tmp/110930-voting.sql
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
…
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
Creating the statistics website
Create a folder with the name DATE-astrad-stat where DATE is the ending date of the voting poll in format YYMMDD. For example, ‘111031-astrad-stat’. Extract the content of astrad-stat.zip into that folder.
Edit the file variables.php. This file has several variables that might need to be changed. Content of the file:
The employee name and email will appear in an email sent to tasks@switzernet.com asking to add credit of value rewardammount to the customers that voted on the poll. The from and to arrays inform the customer of which machines (and their versions) were migrated and migrationdate informs when it was done. votingdateenddate is the limit date for receiving votes, from that date on any customer voting will not get any credit and his vote won’t count. tablename is the table where the results are kept.
Put this folder and all its content in the public switzernet FTP.
Sending the mails
To initiate the mailing list to all customers in the database we’ve created you just have to access http://ftp.switzernet.com/[i/]public/DATE-astrad-stat/mailling.php. Replace [i/] by the correct folder and DATE by the date used in the previous step. Example: http://ftp.switzernet.com/3/public/111031-astrad-stat/mailling.php
Clicking this link will immediately start sending emails to
each of the customers who haven’t received one yet.
Result of a small mailing list of 1 customer:
Each of the customers should receive an email like this:
Results
After at least one customer answers the email the graphic will be generated. To see the pie chart access:
http://ftp.switzernet.com/[i/]public/DATE-astrad-stat/
(replace fields as described above).
Each time a customer answers the poll an email will arrive at tasks@switzernet.com requesting to credit his/her account. The persons in charge of this account should credit the customer account as described in the email.
