Closed

Report card table

This project was awarded to Digivance for $63.75 USD.

Get free quotes for a project like this
Employer working
Awarded to:
Project Budget
$30-$75 USD
Total Bids
5
Project 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

Looking to make some money?

  • Set your budget and the timeframe
  • Outline your proposal
  • Get paid for your work

Hire Freelancers who also bid on this project

    • Forbes
    • The New York Times
    • Time
    • Wall Street Journal
    • Times Online