472928 Link mailer application

This project is to build an application in PHP which I can install on any standard LAMP hosting. The application allows me to group lists of links into projects, and will then email these links in a plain text email format to a specific email address (configurable) via an external SMTP. The application will also have a link shortener/redirect component which will 301 redirect users to the links I have uploaded. The purpose of the redirect is so I can track when Googlebot crawls these links. The application will have features for managing access details for remote STMP servers for sending the email, as well as some reporting features. Use Case Scenario I have a list of 300 links for URLs on 300 different pages. These exist in a comma separated CSV file where the first column is the title of the page and the second column is the URL of the page. Through the web based admin for the application I can login and upload this CSV file of page URLs and titles and add them to a given project group I have created on the system. I can add more links to this group at any time. The system stores the link details in a MySQL database. At the project level I have defined the maximum number of links per email I want to send (to the defined email address), and how many emails I want to send per day. Based on this the system can calculated how many times per day it should send for this project. Based on this data the system will also automatically calculate the timing of the email sends where the number of emails should be spread evenly over the 24 hours of the day. I can also adjust the number of links/email and emails per day, so the email frequency might change over time. The system should recalculate the timing for the next send each time it does a send and adjust appropriately. This process will need to be triggered by a regular cron job where the system will check all projects to see if it is time to send an email. Cron can be set to run say every 15 mins and will trigger any necessary jobs. There will be multiple projects running at any time. When the cron job triggers a send for each project it will send the email via the STMP server defined for the project. The email will be in plain text format but with HTML tags to define the unordered list of links. The links will use the page title as the anchor text and the system generated redirect URL for each link. The email subject line will be "Latest posts <date> <time>". The email contains html tags but needs to be in plain text for the processing that will occur at the other end. When an email is sent the system will update the status of each link sent from 'pending' to 'posted' in the system. It will also record the time that the link was posted. When the redirect script detects that a given link has been crawled by the Googlebot (based on useragent) then it will update the status of the link in the system to 'crawled' and also record the time it was crawled. Subsequent crawls do not need to be recorded. Via the admin console I will be able to check the stats for any given project where I can see the total number of links for a given project, and of those the number that have been posted and crawled. I will also be able to view a list of all links for a given project, and can delete any links with pending status. Another cool (but not required) feature to have would be to check which of the pages have been indexed by Google and Bing. I havn't included this in the scope, but if you have experience with the search engine APIs and think it would be easy to include this feature then please do so and let me know when you bid. I imagine this would work on a cron job that triggered once a day or every other day or something and used the search engine APIs to check if any of the URLs with the "crawled" status were in the index. If they are then it would update the status to "indexed" and wouldn't check them again. Summary I have attached screen mockups for each page in the admin system which should be fairly self explanatory and help you understand the use case above. Please hit the PMB if you have any questions. The front end doesn't need to be fancy, but I do need you to develop something usable. Would be good if this looked nice. If front end design is going to be overly time consuming though please tell me how much of your quote is for design. I have not put a budget on this, because I want you to bid a price based on what you genuinely think it is going to take to deliver what I am asking for. If you have some experience with similar things please let me know. Auto-bids will be ignored. Payment for the project will be through SL escrow only.