Report card table

Closed

Description

I have a list of e-mails that we test every day and give it a grade from 0 to 7 depending if it works or not - 0 means it work, 7 means it does not and several points in between.

I would like to produce a list (in PHP) with the report card of the last 30 days, for each of the e-mails, organized by Country -> Domain -> e-mails, in that order. That is, we will list all the countries, and then all the domains (that occur in the list) and then all the e-mails within that domain. So the list will look like:

Autralia - au

Australian National University

john@[url removed, login to view]

mary@[url removed, login to view]

La Trobe University

miriam@[url removed, login to view]

......

Austria - at

Karl-Franzens-Universit?t Graz (Uni Graz)

joe@[url removed, login to view]

TU-Wien

mary@[url removed, login to view]

Universit?t Wien

joe@[url removed, login to view]

......

Then each e-mail will have a list of grades (from 0 to 7) colored as follows: green for 0, red for 1 or 7 and yellow for anything from 2 to 6. An example would be:

joe@[url removed, login to view] G G G G R R Y Y G Y R R R Y R R Y G R Y R G Y G

You will need to deal with 4 tables:

countries - that lists all countries and their top-level domain(s).

institutions - listing all universities and their domains.

emails - with a list of all the emails.

email_checker - with the report card on the emails.

I'll provide them, SQL and test data. Details of table and steps will be forthcoming in an attachment. Successful delivery should work on a MySQL server running Apache and PHP.

Paulo Ney

## Deliverables

**Details**

The 4 tables are:

countries

institutions

emails

email_checker

and the first one contain the list of all countries and their top-level domains - not all of them present in the list of emails. Only the ones present in the email should be listed. The list of all top-level domains in the list of e-mail can be obtained by:

select distinct substring_index(email, '.', -1) from emails where order_bit > 0;

Observe that only e-mails with order_bit > 0 will be used in throught this program. And that out reading from the DB should use UTF-8 as the character set.

The list of Universities should come from the second table institution and one can obtain the list of Australian universities (for example) by:

select name, domain_1, domain_2, domain_3 from institutions

where substring_index(domain_1, '.', -1) = 'au'

or substring_index(domain_2, '.', -1) = 'au'

or substring_index(domain_3, '.', -1) = 'au'

order by name;

One can then find the e-mails of one particular institution, by one of two methods:

1- Picking out each institutional domain name (there may be more than one domain per institution) and then picking each e-mail that the ending string-matches that given domain - when split at the dots (.).

2- Using a small program I have that I have that given an e-mail extracts the part of the domain from that e-mail, and then matching it to the given domain in question. This program will be sent to the succesful bidder on the project.

The e-mails should be listed by order of the second piece after the @-sign, that is, alphabetical by

substring_index(email, '@', -1)

and then alphabetical on the user_name that is give by:

substring_index(email, '@', 1)

Then each e-mail will have a list of grades (from 0 to 7) colored as follows: green for 0, red for 1 or 7 and yellow for anything from 2 to 6. An example would be:

joe@[url removed, login to view] G G G G R R Y Y G Y G Y R R R Y R R Y G R Y R G

the letters here are representing a small colored rectangle.

A link should be supplied to the e-mail addresses pointing to:

http://localhost/[url removed, login to view]

where people_id is the id of the person associated with the email. A button at the end of each line should allow one to "retire" an e-mail which is to move it to order_bit=0.

**The tables**

The SQL-schema is below, I am listing only the columns used by this program, if you need all other columns in the tables, please specify.

DROP TABLE IF EXISTS `msp3t`.`countries`;

CREATE TABLE `msp3t`.`countries` (

`id` smallint(6) unsigned NOT NULL AUTO_INCREMENT,

`country` varchar(50) DEFAULT NULL,

`tld` varchar(5) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `country` (`country`)

) ENGINE=InnoDB AUTO_INCREMENT=254 DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `msp3t`.`emails`;

CREATE TABLE `msp3t`.`emails` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`people_id` int(11) DEFAULT NULL,

`email` varchar(100) DEFAULT NULL,

`order_bit` tinyint(1) DEFAULT NULL,

`first_stopped` date DEFAULT NULL,

`last_stopped` date DEFAULT NULL,

PRIMARY KEY (`id`),

UNIQUE KEY `people_id_2` (`people_id`,`email`),

KEY `email` (`email`),

KEY `people_id` (`people_id`) USING BTREE,

CONSTRAINT `emails_ibfk_1` FOREIGN KEY (`people_id`) REFERENCES `people` (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=92233 DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `msp3t`.`email_checker`;

CREATE TABLE `msp3t`.`email_checker` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`email_id` int(11) DEFAULT NULL,

`error_code` tinyint(1) DEFAULT NULL,

`date_checked` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

PRIMARY KEY (`id`),

KEY `email_id` (`email_id`),

CONSTRAINT `email_checker_ibfk_1` FOREIGN KEY (`email_id`) REFERENCES `emails` (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=4434923 DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `msp3t`.`institutions`;

CREATE TABLE `msp3t`.`institutions` (

`s_id` smallint(6) unsigned NOT NULL AUTO_INCREMENT,

`name` varchar(70) DEFAULT NULL,

`alt_name_1` varchar(70) DEFAULT NULL,

`alt_name_2` varchar(70) DEFAULT NULL,

`domain_1` varchar(50) DEFAULT NULL,

`domain_2` varchar(50) DEFAULT NULL,

`domain_3` varchar(50) DEFAULT NULL,

PRIMARY KEY (`s_id`)

) ENGINE=InnoDB AUTO_INCREMENT=5547 DEFAULT CHARSET=utf8;

**Installation and Delivery**

A succesful installation and delivery will be made on a system running LAMP (Lynux, Apache, MySQL and PHP). Please have a set of configuration variable upfront, like in:

$dbhost = "localhost";

$dbuser = "wft";

$dbpass = "xptoy3";

$dbname = "test";

?>

and the whole package self-contained in a single directory, if possible.

Paulo Ney

Skills: Amazon Web Services, PHP, Software Architecture, Software Testing, Web Hosting, Website Management, Website Testing

See more: yellow software, yellow character, uni.edu, system level architecture, string matching in c, schema update, red engine, rectangle line, r architecture, php create table, people as letters, ok-uni, move domain name, move domain from 1&1, miriam, matching string, line rectangle, la works, green domains, find where a person works, find character in string c, create table php, australian works, sql r, sql in r

Project ID: #2758415

Awarded to:

Digivance

See private message.

$63.75 USD in 22 days
(46 Reviews)
5.0

5 freelancers are bidding on average $52 for this job

webdesktechvw

See private message.

$63.75 USD in 22 days
(177 Reviews)
7.0
doveinfo

See private message.

$60.35 USD in 22 days
(3 Reviews)
1.0
durgaprasadac

See private message.

$50 USD in 22 days
(4 Reviews)
0.7
clarkrowe

See private message.

$21.25 USD in 22 days
(0 Reviews)
1.9