Mysql Perl PHP, Phone Tracker

MySQL DB and Perl Script for Mobile Phone Tracking System. Simple PHP front end. Need to design a Mysql DB and implement it. Need to make perl script which will update the db with locations of Mobile Phones and send out notification emails. Simple PHP Interface to Login to account as user and add/remove numbers, view number locations. ## Deliverables Linux/Perl/MySQL/PHP Design and Script Mobile Phone Tracking System Database: Roughly 6 Tables: Small database to hold information on which will need to store users, their name, mobile number, email. Database must store a list of numbers the user(s) is tracking. Each user will have an account balance. Database will also hold information on location of each number. Location will be in the form of a location reference number(MSC). There will be another table which holds information about each MSC. The DB must also hold the history of where the numbers have been found to be in a new location and are currently located. Perl Script(s) Perl script will be run once per day. It will go through the users, and look-up the location of the numbers user is tracking. The location information will be gained by doing a web call (http) which will return JSON. I have a simple perl script already written which gets the MSC and puts it into a variable ready for processing. Only users who have enough credit in their account will have their numbers looked up against the web call. A number who 2 users are tracking should only have 1 lookup via the http call. The LRN location number should be looked up against a table in the db, to gain the actual location. If there is no data in the table reffercing that LRN, then a web call to another site must be done and the information must be scraped from that webpage. It is a very simple web site to scrape. After the above the script needs to send out notification emails to the user. A user should recieve a single email if one or more of the numbers they are tracking have changed location. PHP 1/ Simple Login Screen 2/ Add/View(Remove) numbers being tracked 3/ See List of all numbers and current location 4/ History of all movement of particular number. 5/ Transaction History A linux system is avaliable with mysql and perl installed. ---------------------------------------------------------------------------------------------------------------------------------------- Details. _______ MySQL Database Should contain Information on 1/ Users Name email address (username to login with) Password - Clear text is OK Mobile Number (Can be used later for SMS notification) Numbers being Tracked. Credit 2/Numbers Being Tracker Name Date - Time and Date of last lookup. Current MSC location. History of where this number has been tracked. (Only when the phone has changed location should only this be recorded) 3/ MSC Information MSC Number Country City. 4/ User Transactions date of transaction which User How many lookups What cost Perl Script - will be run by cronjob once a day. 1/ Find where numbers are currently located by using webcall (code supplied). Only make webcall if user has enough credit. Webcall should not be made if the number has already been looked up by another user on the system. 2/ create Log entry for user with money spent per day. ie 30 lookup's = x. Remove money from credit. Each lookup is ".7" of a credit. 3/ Update db with current locations of numbers, record time and date of when lookup was made. 4/ Record history when number has changed location. 5/ scrape Web page [login to view URL] to find out country and city of any MSC that do not have information in the DB Insert this data into the MSC table. This Scrape will only work 5 times a day from same IP, so any information missing from perivous day should be looked up on the day after. 6/ Send email notification to user when number(s) has been reported in a new location. Only one email should be sent including information on all numbers that have changed location. PHP - Simple, no graphics required. 1/ Login Page - Username, Password 2/ List numbers being tracked. Name, Number, Country, City. Option to Add or Remove. Option to click on Name/Number to see history of where it has been tracked. Should highlight which Name/Number has recently moved location. 3/ History of number where it has been tracked. 4/ Show transaction history (daily), total amount spent per month, total amount spent. Mysql/PHP/Perl already installed on Linux Ubuntu. Sample MSC Lookup Script: #!/usr/bin/perl use strict; use DBI; use CGI; use JSON; use Data::Dumper; use LWP::UserAgent; my $q = CGI->new(); print $q->header(); my $ua = LWP::UserAgent->new(); $ua->agent('RoutoStarAsk/1.0'); my $dest = '44123123123'; my $user = "xxxxx"; my $pass = "xxxxx"; my $res; my $test = 0; my $dbh = DBI->connect('DBI:mysql:tracker', 'root', 'xxx' ) || die "Could not connect to database: $DBI::errstr"; getMSC(); sub getMSC { $res = $ua->post( "[login to view URL]", { number => $dest, user => $user, pass => $pass } ); if ($res->is_error()) { # couldn't make request print "Couldn't make request: $res->status_line"; } else { my $resp = from_json($res->content()); my $msc = $resp->{msc}; print "msc: $msc\n"; } }