Dashboard Application webbased

You will be implementing a web based dashboard application, a reporting tool that consolidates, aggregates and arranges measurements, metrics (measurements compared to goals). The application will be implemented in Perl or PHP with Mysql as the database. We will make the collection of the raw data into the tables, your job is to read that data, parse it and display it into the dashboard application (the original tables where the data is colected will not be optimized so you may have to parse them and generate new tables that are more optimized for fast web access, this is up to you). The tables are: - [login to view URL] (id, name, description, status, group_id ) - [login to view URL] (id, name): parameters will get separated on groups and each group will get displayed on a separate tab. - dashboard.variable_history_mmYY(id, variable_id, value, insert_time) (where the _mmYY is the curent month, this means you will get a diffrent table for each month like: variable_history_0108, variable_history_0208, variable_history_0308 etc.).The dashboard will have to display data from all those tables in the same report like the data is stored to just one single table so you will have to find a solution for this (you can for example make a cronjob that is parsing the variable_history_* tables and generate your own optimized tables). - dashboard.standard_values: id, variable_id, range ,DV, NV, Goal, DV_direction, NV_direction, Goal_direction. (for more details about this tables see bellow) - other new tables: for any other functions from the dashboard you are free to create any new tables and desigin the structure as you consider the best. Each variable will have an id, a name and a number of collected values. Example variables: "Hits", "Page displays", "Sessions", "User logins", "Failed login attempts" etc, all are numbers and we will collect them into the variable_history_* on a regular interval that can be better 1 and 24 hours. This means some variables will get collected evey 24 hours, some every 1 hours, or even every 6 hours. The variable_history_mmYY.insert_time will save the date and time when that variable is collected, and at some point we may decide that a variable that was collected every 1 hour to only be colected once every 24 hours so your scipts must be able to detect this chnange based on the variable_history_mmYY.insert_time value. Regarding the standard values from the dashboard.standard_values table we will save in this table the values that are considered to be dangarous, normal and goals. The standard values are a combination of a number and a direction. The direction can take as values 'M' or 'B' where M means more then the value and B means bollow that value like: - DV, DV_direction: this is the dangerous values for example for variable "Page views" we could have DV=100000 and DV_direction=B, this means if the "Page views" is bellow 100 000 then we have reached the dangerous value, or another example for variable "Failed login attempts" we could have DV=1000, and DV_direction=M means if we have more then 1000 failed logins then we reached the dandgerous value. - NV, NV_direction: this is the normal value, it's the same as with dangerous value but its related to what we consider to be normal for that variable. - Goal, Goal direction: This is the goal we want to reach for that parameter and it behaves the same as the other two. - [login to view URL]: this field can take as values "DAY", "MONTH", "YEAR", "GENERALL". This means we will have difrent DV,NV,Goal values for each four time ranges. So for each parameter we will have 4 rows into the table standard_values one row for each range. The "DAY" are refering to daily standard values, "MONTH" is referring to montly values, "YEAR" is year standard values, and "GENERALL" is refering to all time (all the data we have colected for that variable until now). One note: for example for comparing if a variable have reached the GENERALL goal you whould need to open all the variable_history_* tables that we have and this will take too much resources, you have to find a solution for making this fast. The web application will have the following sections: 1) Main Dashboard overview page: This is the homepage of the application and it will display 2 sections: - favorites parameters: the dashboard will allow to set some paramaters as favorites and we will get them displayed on the overview page. The way the parameter is displayed on the overview page depending on the settings used when you add the favorites variables. - the alarms: all the alarms for all paramaters will get displayed on the overview page (when the parameter is bellow the DV value) 2) Groups pages: This page will display for each grouup of variables the following: - the favorites parameters for that group - the alarms for that group - all the variables from that group in a table with link to the paremeter detail pages 3) Parameter details page: This page will display statistical data for that variable: alexa like charts, odometers, the table with the last values and for each recorded data and based on the standard values we have to display the results and this can take as values: OUT OF CONTROL, DANGEROUS,NORMAL,GOAL REACHED from where: - below DV = OUT OF CONTROL - between DV and NV = DANGEROUS - between NV and GOAL = NORMAL - over Goal = GOAL REACHED. Note that the odometers must have those 4 areas on them with diffrent colors, for each of the results areas. Under the table will have the form for setting the standard values for that variable and links to the comparation results pages for that parameter, see bellow. 4) Settings pages for each group: This will display a form with all variables in that group, we can update the standard values for all parameters in that group. Note that standard values could alos take dynamic values based on other variables, so they can take as imput formulas and not just a simple value, the formuals could use other variable values for example: "Page display" goal could be 10% of "Hits" parameter, for entering the fomulas we need an ajax based formuala editor, see bellow. 5) Standard values formular editor page(s): The ajax formula editor may allow selecting parameter values and other formulas output as variables and the formula will be a diffrent section into the applicaion: we set the fomula and save it with a title and text description and then into the standardvalue we will have a small icon (forumla) for each value and when we click on it a new small popup will open with all the formulas we have and we can select one. It is your responsbilty to find the best visual solution for this section. 6) Settings history page: All values for standard values will get recorded in a history table and we should be able to restore them at any moment. This page will display all past values we set for each of the standard values and it will allow to bring back any of the past values. 7) Comparation rule bassed editor page This will be a page that will allow us to add/delete/modify comparation rules for variables at year, montly, weekly, daily and hourly levels. Here you have an example: - year => we compare "hits" for 2006 with "hits" for 2007 - montly => we compare "hits" for November 2006 with "hits" for Novermber 2007 - weekly => we compare "hits" for firts week of Novemeber 2006 with the firts week of Novemeber 2007. - daily => we compare all "hits" we get in all Mondays until now - hourly => we compare the "hits" for day of week hourly, so monday 11:00-12:00 with with others mondays 11:00-12:00. This comparation is on demand, means by default a variable will have no comparation rules at all. From the variable details page we will have a link to this comparation rules editor from where we could add rules for that variable. Once for exampe we will a montly comparation for "Hits" variable we will start collecting the comparation data for that variable means you will need to make some kind of cronjobs that is executed regulary for processing the data for all the comparations. We also want to be able to compare diffrent varables like: "login failure with ad income" or "income ads with user registrations" etc. It is your responsability to find the best visual interface for this section (for most of the other pages you will get a prototype). 8) Comparation results display page: This page will display the comparation results for rules created before, for each we will display the history data and charts. 9) Dashboard overview configurator: This will allow us to add/delete favorite variables that will get displayed into the overview page. From here we alos select what information will get displayed into the overview page for each parameter (the type of chart). 10) Alerts configuration page: This will allows us to configure the way we receive alerts from the system. Alerts can be sent via email or SMS (to send an sms you just have to send a special formatted email message to our sms prvider). 11) Groups management: This page will allow to add/delete/update variable groups 12) Waiting variables page: This page will alow us to add new variables to the system. Note that the data collecting for those varibales will be made by us so to add a new variable to the system you only have to add it to the main [login to view URL] table with staust WAITING. For this you use the [login to view URL] field and the purpose if for us to know that we have to collect the values for that new variable. To add a new variable we must select a group, description, and it must also allow to select another variable from a diffrent group that will be a base for that new variable. You will get a more detailed prototype for the application after you will start working on the project. Note that for the variable comparation section and the standard variables formula editor you will have to design the visual interface of the application yourself. Some pages from the application also have an option to export data to excel, this will apper into the prototype.