kool_samule
Members-
Posts
169 -
Joined
-
Last visited
Everything posted by kool_samule
-
fantasic. thanks for the help
-
Thanks for the reply, is there anything out there that could jump to a column in a line?
-
Hi Chaps, I have been given an invalid XML file, which when run through 3rd party software, gives me the line & column numbers of the error. Instead of opening the file and manually searching for the line/column numbers (which can take a whie, as the file is vast!), is there a way to parse the XML file and locate the error, using the line/column numbers? And maybe display/highlight the error in a browser? e.g.: 1: open bad-file.xml 2: read bad-file.xml 3. go-to line 50 4. go-to column 3620 5. open in browser 6. highlight error Cheers S
-
OK my bad, thanks for the heads up. Cheers!
-
Yeah, is this a bug, in issue with 5.2.10? Any idea of how to work around this? It seems to be a problem when the DAY you are querying against happens to be the first day of the given MONTH. I've just tried it with "October 2012 fourth Monday" and it returned: 2012-10-29, when it should be 2012-10-22..
-
Hi Chaps, I have a problem trying to find out the fourth Thursday of a Month of a Year, for example November. date_default_timezone_set('Europe/London'); echo strtotime("November 2012 fourth thursday")."</br>"; // returns 1354147200 SHOULD BE 1353542400 echo date('Y-m-d', 1354147200)."</br>"; // returns 2012-11-29 echo date('Y-m-d', 1353542400)."</br>"; // returns 2012-11-22 So the problem is with the strotime calculation, I'm using PHP Version 5.2.10 on Windows/Apache. Anyone know how to solve this or alternatives?
-
PHP / MySQL for Recursive Calendar Appointments
kool_samule replied to kool_samule's topic in PHP Coding Help
Thanks guys, Advice is noted..I'll crack on and hopefully get it sorted. Cheers for the help. -
Hi Chaps, Apologies if this topic should be in MySQL...I wasn't sure. I'm trying to create a MS Outlook-style Appointment application with Recurrence for a PHP calendar. I've got all the same options that Outlook gives, e.g.: Daily - every DAY, or every WEEKDAY Weekly every X WEEK(S) on X DAYS Monthly - on DAY of every X month or Nth DAY of every X MONTH(s) Yearly - recur every X YEAR(S) on DAY of MONTH or Nth DAY of every X MONTH(s) My question is what is the best way to store the recursive appointment information in MySQL Database (Columns, etc), so I can then use a PHP algorithm to calculate whether an appointment is within a calendar view, or if reminders need to be sent. Any advice will be a great help.
-
Yeah, clicked around to trigger an error, but nothing happened..got me baffled.
-
Hi, Yeah, I've already done that, should have mentioned. FireBug > Console > Debug Info / FireBug > Script > Breakpoints / = Nothing ....?
-
Hi Chaps, Scratching my head with this one, hopefully someone can tell me where I'm going wrong...my Javascript/jQuery skills are low. Overview: I have multiple jQuery 'connectedWith' sortable lists in a form, when groupItems are moved/connected, an updated SUM of two amounts is displayed, using the Net/Gross Calculation function. The order of each group is added to the group array, using the groupItemOrder# functions. Upon submitting the form, the correct group/groupItemOrder is displayed (for this demo, I'll add the MySQL scripts afterwards). I have a problem the two functions, Net/Gross Calculation & groupItemOrder#, won't work on the same page. They both work if the other is not 'active', so to speak. Function: Net/Gross Calculation function: /* START OF Net/Gross Calculation function - used to calculate the Net/Gross of each group - won't work if the groupItemOrder# on-the-fly array updater are active */ $(document).ready( function () { $('ul.connectedSortable').sortable({ update: function (ser) { $(".connectedSortable").each(function (i) { var total_net = 0; var total_gross = 0; $(this).find(".net").each(function () { total_net += parseInt($(this).text()); }); $(this).find(".gross").each(function () { total_gross += parseInt($(this).text()); }); $("div#total_net" + i).remove(); if(total_net > 0){ $(this).append($("<div id='total_net" + i + "'></div>").text('Total Net: ' + total_net)); } $("div#total_gross" + i).remove(); if(total_gross > 0){ $(this).append($("<div id='total_gross" + i + "'></div>").text('Total Gross: ' + total_gross)); } }); }, }); }); /* END of Net/Gross Calculation function */ Function: groupItemOrder# /* START of groupItemOrder# functions - used to update each group array on-the-fly, to obtain the correct order of each group item, on submit of form - won't work if Net/Gross Calculation function is active */ var groupItemOrder1 = ''; $(function() { $("#sortable1").sortable({ update: function(event, ui) { groupItemOrder1 = $("#sortable1").sortable('toArray').toString(); } }); $("#sortable1").disableSelection(); }); scriptAr1 = new Array(); scriptAr1[0] = "one"; scriptAr1[1] = "two"; scriptAr1[2] = "three"; function setValue1() { var arv1 = groupItemOrder1.toString(); document.form.arv1.value=arv1; } var groupItemOrder2 = ''; $(function() { $("#sortable2").sortable({ update: function(event, ui) { groupItemOrder2 = $("#sortable2").sortable('toArray').toString(); } }); $("#sortable2").disableSelection(); }); scriptAr2 = new Array(); scriptAr2[0] = "one"; scriptAr2[1] = "two"; scriptAr2[2] = "three"; function setValue2() { var arv2 = groupItemOrder2.toString(); document.form.arv2.value=arv2; } var groupItemOrder3 = ''; $(function() { $("#sortable3").sortable({ update: function(event, ui) { groupItemOrder3 = $("#sortable3").sortable('toArray').toString(); } }); $("#sortable3").disableSelection(); }); scriptAr3 = new Array(); scriptAr3[0] = "one"; scriptAr3[1] = "two"; scriptAr3[2] = "three"; function setValue3() { var arv3 = groupItemOrder3.toString(); document.form.arv3.value=arv3; } /* END of groupItemOrder# functions */ FULL HTML: <?php $editFormAction = $_SERVER['PHP_SELF']; if (isset($_SERVER['QUERY_STRING'])) { $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']); } if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "sort-group")) { $original_groups = $_POST['original_groups']; // returns 3 for example $i = 1; do{ $ss = $_POST['arv'.$i]; $tok = explode(',',$ss); $order = 1; foreach($tok as $var) { $id = $var; if(is_numeric($id)){ echo "(GROUP: $i) Item ID: $id (Order: $order)</br>"; $order++; } } $i++; }while($i-1 < $original_groups); } ?> <!DOCTYPE html> <html lang="en"> <head> <meta charset="utf-8"> <title>jQuery UI Sortable - Connect lists</title> <link class="jsbin" href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.6/themes/base/jquery-ui.css" rel="stylesheet" type="text/css"></link> <script class="jsbin" src="http://ajax.googleapis.com/ajax/libs/jquery/1/jquery.min.js"></script> <script class="jsbin" src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.6/jquery-ui.min.js"></script> <script> /* START OF Net/Gross Calculation function - used to calculate the Net/Gross of each group - won't work if the groupItemOrder# on-the-fly array updater are active */ $(document).ready( function () { $('ul.connectedSortable').sortable({ update: function (ser) { $(".connectedSortable").each(function (i) { var total_net = 0; var total_gross = 0; $(this).find(".net").each(function () { total_net += parseInt($(this).text()); }); $(this).find(".gross").each(function () { total_gross += parseInt($(this).text()); }); $("div#total_net" + i).remove(); if(total_net > 0){ $(this).append($("<div id='total_net" + i + "'></div>").text('Total Net: ' + total_net)); } $("div#total_gross" + i).remove(); if(total_gross > 0){ $(this).append($("<div id='total_gross" + i + "'></div>").text('Total Gross: ' + total_gross)); } }); }, }); }); /* END of Net/Gross Calculation function */ /* START of connectWith function - works with both functions */ $(function() { $( "#sortable1, #sortable2, #sortable3" ).sortable({ connectWith: ".connectedSortable" }).disableSelection(); }); /* END of connectWith function */ /* START of groupItemOrder# functions - used to update each group array on-the-fly, to obtain the correct order of each group item, on submit of form - won't work if Net/Gross Calculation function is active */ var groupItemOrder1 = ''; $(function() { $("#sortable1").sortable({ update: function(event, ui) { groupItemOrder1 = $("#sortable1").sortable('toArray').toString(); } }); $("#sortable1").disableSelection(); }); scriptAr1 = new Array(); scriptAr1[0] = "one"; scriptAr1[1] = "two"; scriptAr1[2] = "three"; function setValue1() { var arv1 = groupItemOrder1.toString(); document.form.arv1.value=arv1; } var groupItemOrder2 = ''; $(function() { $("#sortable2").sortable({ update: function(event, ui) { groupItemOrder2 = $("#sortable2").sortable('toArray').toString(); } }); $("#sortable2").disableSelection(); }); scriptAr2 = new Array(); scriptAr2[0] = "one"; scriptAr2[1] = "two"; scriptAr2[2] = "three"; function setValue2() { var arv2 = groupItemOrder2.toString(); document.form.arv2.value=arv2; } var groupItemOrder3 = ''; $(function() { $("#sortable3").sortable({ update: function(event, ui) { groupItemOrder3 = $("#sortable3").sortable('toArray').toString(); } }); $("#sortable3").disableSelection(); }); scriptAr3 = new Array(); scriptAr3[0] = "one"; scriptAr3[1] = "two"; scriptAr3[2] = "three"; function setValue3() { var arv3 = groupItemOrder3.toString(); document.form.arv3.value=arv3; } /* END of groupItemOrder# functions */ </script> <style> #sortable1, #sortable2, #sortable3 { list-style-type: none; padding: 5px; margin-right: 10px; width: 300px; margin-top: 0; margin-bottom: 0; margin-left: 0; display:block; } #sortable1 li, #sortable2 li, #sortable3 li{ padding: 5px; font-size: 12pt; width: 200px; margin: 5px; cursor: pointer; } #sortable1 .net , #sortable1 .gross, #sortable2 .net , #sortable2 .gross, #sortable3 .net , #sortable3 .gross{ float: right; } </style> </head> <body> <form action="<?php echo $editFormAction; ?>" method="post" name="form" id="form" onSubmit="setValue1(), setValue2(), setValue3()"> <input type="submit" id="button" value="Sort Mulitple Lists" /> <ul id="sortable1" class="connectedSortable"> <li class="ui-state-default" id="6411"> 6411 <div class="net">100</div> - <div class="gross">250</div> </li> </ul> <input name="arv1" type="hidden"> <ul id="sortable2" class="connectedSortable"> <li class="ui-state-default" id="6230"> 6230 <div class="net">100</div> - <div class="gross">250</div> </li> </ul> <input name="arv2" type="hidden"> <ul id="sortable3" class="connectedSortable"> <li class="ui-state-default" id="6434"> 6434 <div class="net">100</div> - <div class="gross">250</div> </li> </ul> <input name="arv3" type="hidden"> <input type="hidden" name="original_groups" value="3" /> <input type="hidden" name="MM_insert" value="sort-group" /> </form> </body> </html> If anyone can help, it'll be hi-5-tastic. Thanks in advance.
-
Thanks, managed to sort it using file_put_contents.
-
Hi Chaps, I'm working on a PHP script to mysqldump database to file. Using this as a base: http://www.edmondscommerce.co.uk/mysql/php-mysql-dump-script/, I've managed to strip it down to this: set_time_limit(0); $mysql_host='localhost'; $mysql_database='database'; $mysql_username='username'; $mysql_password='pasword'; _mysql_test($mysql_host,$mysql_database, $mysql_username, $mysql_password); $print_form = 0; header('Content-type: text/plain'); header('Content-Disposition: attachment; filename="'.$mysql_database."_".date('Y-m-d').'.sql"'); $filename = $mysql_database."_".date('Y-m-d').'.sql'; _mysqldump($mysql_database); function _mysqldump($mysql_database) { $sql="show tables;"; $result= mysql_query($sql); if( $result) { while( $row= mysql_fetch_row($result)) { _mysqldump_table_structure($row[0]); _mysqldump_table_data($row[0]); } } else { echo "/* no tables in $mysql_database */\n"; } mysql_free_result($result); } function _mysqldump_table_structure($table) { echo "/* Table structure for table `$table` */\n"; echo "DROP TABLE IF EXISTS `$table`;\n\n"; $sql="show create table `$table`; "; $result=mysql_query($sql); if( $result) { if($row= mysql_fetch_assoc($result)) { echo $row['Create Table'].";\n\n"; } } mysql_free_result($result); } function _mysqldump_table_data($table) { $sql="select * from `$table`;"; $result=mysql_query($sql); if( $result) { $num_rows= mysql_num_rows($result); $num_fields= mysql_num_fields($result); if( $num_rows> 0) { echo "/* dumping data for table `$table` */\n"; $field_type=array(); $i=0; while( $i <$num_fields) { $meta= mysql_fetch_field($result, $i); array_push($field_type, $meta->type); $i++; } echo "insert into `$table` values\n"; $index=0; while( $row= mysql_fetch_row($result)) { echo "("; for( $i=0; $i <$num_fields; $i++) { if( is_null( $row[$i])) echo "null"; else { switch( $field_type[$i]) { case 'int': echo $row[$i]; break; case 'string': case 'blob' : default: echo "'".mysql_real_escape_string($row[$i])."'"; } } if( $i <$num_fields-1) echo ","; } echo ")"; if( $index <$num_rows-1) echo ","; else echo ";"; echo "\n"; $index++; } } } mysql_free_result($result); echo "\n"; } function _mysql_test($mysql_host,$mysql_database, $mysql_username, $mysql_password) { global $output_messages; $link = mysql_connect($mysql_host, $mysql_username, $mysql_password); if (!$link) { array_push($output_messages, 'Could not connect: ' . mysql_error()); } else { array_push ($output_messages,"Connected with MySQL server:$mysql_username@$mysql_host successfully"); $db_selected = mysql_select_db($mysql_database, $link); if (!$db_selected) { array_push ($output_messages,'Can\'t use $mysql_database : ' . mysql_error()); } else array_push ($output_messages,"Connected with MySQL database:$mysql_database successfully"); } } The problem is, I want the file to save to a specific network location, rather than the output being sent to headers. I've tried to play around with: ob_start();.... $page = ob_get_contents(); ob_end_flush(); $fp = fopen("output.html","w"); fwrite($fp,$page); fclose($fp); But this outputs to the browser, then saves the file...I would like to load the page, and have the script create and save the file where I want it (without the output in the browser, or prompt to save/download file). Note: I need something like this as I'm running PHP/MySQL on IIS, so can use shell/system/etc. Any help will be hi-5-tastic!
-
Bang on, good work Thanks
-
Hi Chaps, Hopefully an easy one for you, having a bit of trouble with an array within array. I'm parsing an XML document, and attempting to assign variables as I go along. I'm hitting some trouble with one particular child node, as it's in an array. PHP: print_r($parent->child); Array: Array ( [0] => XMLTag Object ( [tagAttrs] => Array ( ) [tagName] => child [tagData] => Something in tagData [tagChildren] => Array ( ) [tagParents] => 3 ) ) Im basically after the value of child [tagData] , so I'll be able to assign a variable: If anyone can help, it'll help me no end Cheers
-
Perfect, thanks a lot...would never have got there without your help. Much appreciated... Memory bank: updated.
-
OK, I'm stuck...I've tried to alter the SELECT query, with no joy... I don't know how Divide would help here... and I can't figure out a way to differentiate between jobs that have a Typesetting task and those that don't...without getting the above results. Would a SubQuery do the trick?
-
Because Job 1 has a Typesetting Task, I dont want it included in Non-Typesetting. The current query isnt doing it, but that's the result I'm after..
-
OK, getting really close now... a) Relationship is 1:1 b) +-----------+---------+-------------+-----------------+ | FK_job_id | task_id | task_title | TaskItem_Status | +-----------+---------+-------------+-----------------+ | 1 | 1 | Preparation | Complete | | 1 | 2 | Typesetting | Complete | | 1 | 3 | Correction | Complete | | 1 | 4 | Evaluate | Complete | | 2 | 1 | Preparation | Complete | | 2 | 3 | Correction | Complete | | 2 | 4 | Evaluate | Complete | +-----------+---------+-------------+-----------------+ 7 rows in set (0.07 sec) c) +-----+---------+-------------+----------+---------------+-------------+----------+ | Job | Jobdate | JobType | JobPages | AnalysisGross | AnalysisNet | JobPrice | +-----+---------+-------------+----------+---------------+-------------+----------+ | 1 | 201201 | Typesetting | 1 | 100 | 50 | 250 | | 1 | 201201 | Non-Typest | 1 | 100 | 50 | 250 | | 2 | 201201 | Non-Typest | 2 | 500 | 250 | 250 | +-----+---------+-------------+----------+---------------+-------------+----------+ 3 rows in set (0.21 sec) c) is the best one so far...just need to remove: | 1 | 201201 | Non-Typest | 1 | 100 | 50 | 250 |
-
OK, getting close... QUERY: SELECT EXTRACT(YEAR_MONTH FROM proj_end_date) as Jobdate, CASE WHEN task_title='Typesetting' AND ti1.status='Complete' THEN 'Typeset' ELSE 'Non-Typeset' END AS 'JobType', CASE WHEN task_title='Typesetting' AND ti1.status='Complete' THEN SUM(job_pages) ELSE job_pages END AS 'JobPages', CASE WHEN task_title='Typesetting' AND ti1.status='Complete' THEN SUM(analysis_gross) ELSE analysis_gross END AS 'AnalysisGross', CASE WHEN task_title='Typesetting' AND ti1.status='Complete' THEN SUM(analysis_net) ELSE analysis_net END AS 'AnalysisNet', CASE WHEN task_title='Typesetting' AND ti1.status='Complete' THEN SUM(invoice_item_price) ELSE invoice_item_price END AS JobPrice FROM tbl_job as j INNER JOIN tbl_task_item as ti1 ON ti1.FK_job_id = j.job_id INNER JOIN tbl_task as t ON t.task_id = ti1.FK_task_id INNER JOIN tbl_invoice_item as ii ON ii.FK_job_id = j.job_id INNER JOIN tbl_invoice as i ON i.invoice_id=ii.FK_invoice_id INNER JOIN tbl_analysis as a ON a.FK_job_id = j.job_id INNER JOIN tbl_language as l ON l.lang_id = j.FK_langt_id INNER JOIN tbl_project as p ON p.proj_id = j.FK_proj_id WHERE p.FK_cust_id = 1 AND proj_status='Complete' GROUP BY JobDate, JobType ORDER BY JobDate, JobType DESC; OUTPUT: JobDate - JobType - JobPages - AnalysisGross - AnalysisNet - JobPrice 201201 - Typeset - 1 - 100 - 50 - 250 201201 - Non-Typest - 1 - 100 - 50 - 250 EXPECTED OUTPUT: JobDate - JobType - JobPages - AnalysisGross - AnalysisNet - JobPrice 201201 - Typeset - 1 - 100 - 50 - 250 201201 - Non-Typest - 2 - 500 - 250 - 250 Thank you for the help!
-
Sorry, same result when using: SELECT EXTRACT(YEAR_MONTH FROM proj_end_date) as job_date, SUM(invoice_item_price) as total, CASE WHEN task_title='Typesetting' AND ti1.status='Complete' THEN 'Typeset' ELSE 'Non-Typeset' END AS 'JobType' FROM tbl_job as j INNER JOIN tbl_task_item as ti1 ON ti1.FK_job_id = j.job_id INNER JOIN tbl_task as t ON t.task_id = ti1.FK_task_id INNER JOIN tbl_invoice_item as ii ON ii.FK_job_id = j.job_id INNER JOIN tbl_invoice as i ON i.invoice_id=ii.FK_invoice_id INNER JOIN tbl_analysis as a ON a.FK_job_id = j.job_id INNER JOIN tbl_language as l ON l.lang_id = j.FK_langt_id INNER JOIN tbl_project as p ON p.proj_id = j.FK_proj_id WHERE p.FK_cust_id = 1 AND proj_status='Complete' GROUP BY job_date, JobType ORDER BY job_date, JobType DESC I still feel the invoice_item_price / job / tasks are the issue though...
-
Hi again, thanks for the response, I tried: SELECT EXTRACT(YEAR_MONTH FROM proj_end_date), SUM(invoice_item_price) as total, CASE WHEN task_title='Typesetting' AND ti1.status='Complete' THEN 'Typeset' ELSE 'Non-Typeset' END AS 'JobType' FROM tbl_job as j INNER JOIN tbl_task_item as ti1 ON ti1.FK_job_id = j.job_id INNER JOIN tbl_task as t ON t.task_id = ti1.FK_task_id INNER JOIN tbl_invoice_item as ii ON ii.FK_job_id = j.job_id INNER JOIN tbl_invoice as i ON i.invoice_id=ii.FK_invoice_id INNER JOIN tbl_analysis as a ON a.FK_job_id = j.job_id INNER JOIN tbl_language as l ON l.lang_id = j.FK_langt_id INNER JOIN tbl_project as p ON p.proj_id = j.FK_proj_id WHERE p.FK_cust_id = 1 AND proj_status='Complete' GROUP BY JobType ORDER BY JobType DESC and this gives me: EXTRACT(YEAR_MONTH FROM proj_end_date) - total - JobType 201201 - 250 - Typeset 201201 - 1500 - Non-Typeset But the 'total' figures are not what I'm after...as mentioned in my previous posts, I want to class all jobs that have had a 'Typesetting' task as a Typeset job, and those that didn't as 'Non-Typeset'. What is happening with the query at the moment is it is multiplying each task by the job invoice_item_price, rather than each job once.
-
Thanks for the reply, however I'm getting an error: YEARMONTH does not exist. The main problem I'm having is with the task_items / jobs, rather than the year/month/dates. Hopefully this might explain the problem a bit better: Job 1 has an invoice price of 250. Job 1 has 4 tasks, 1 of which is Typesetting. Job 2 has an invoice price of 250. Job 2 has 3 tasks, 0 of which are Typesetting. I want the query to produce a result of: Typeset Job - 250 (Job 1 - has Typesetting Task) Non Typeset Job - 250 (Job 2 - has no Typesetting Task) Whats happening is: Typeset Job - 250 (Job 1 - has 1 Typesetting Task) Non Typeset Job - 750 (Job 1 - has 3 non-Typesetting Tasks) Non Typeset Job - 750 (Job 2 - has 3 non-Typesetting Tasks) Any ideas on this?
-
MySQL Design is broken down into seperate tables, for a pretty big database...what i've posted is a stripped down version, purely for this problem. The SELECT query doesn't work as I want it to...it's just an example of what i've got so far...but the INNER JOINS obviously are required. The JobType is determined by the task_title 'Typesetting', i.e. if the job has a Typesetting task, then the job as a whole is classed as a 'Typeset Job'. The result I'm after is something to show the SUM of job_pages, analysis_gross, analysis_net and invoice_item_price per job. The results need to be grouped by year/month/jobtype (Typeset/Non-Typeset)/langname Is this possible?
-
I'm basically after the following: Query to produce results of; invoice_item_price of all jobs that include a Typesetting task and all of those that don't. The problem I'm having is that I can't figure out how to get the correct SUMtotal of the invoice_item_price, when searching through the tbl_task_item, to see if a Typesetting task has been completed. In trying to do this, it multiplies the invoice_item_price by the amount of tasks within a job. Database: -- MySQL dump 10.11 -- -- Host: localhost Database: dbtemp -- ------------------------------------------------------ -- Server version 5.0.83-community-nt /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `tbl_analysis` -- DROP TABLE IF EXISTS `tbl_analysis`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `tbl_analysis` ( `analysis_id` int(20) unsigned NOT NULL auto_increment, `FK_job_id` int(20) unsigned default NULL, `analysis_gross` int(10) default NULL, `analysis_net` int(10) default NULL, PRIMARY KEY (`analysis_id`), KEY `FK_job_id` (`FK_job_id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `tbl_analysis` -- LOCK TABLES `tbl_analysis` WRITE; /*!40000 ALTER TABLE `tbl_analysis` DISABLE KEYS */; INSERT INTO `tbl_analysis` VALUES (1,1,100,50),(2,2,500,250); /*!40000 ALTER TABLE `tbl_analysis` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `tbl_customer` -- DROP TABLE IF EXISTS `tbl_customer`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `tbl_customer` ( `cust_id` int(20) unsigned NOT NULL auto_increment, `cust_name` varchar(255) default NULL, PRIMARY KEY (`cust_id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `tbl_customer` -- LOCK TABLES `tbl_customer` WRITE; /*!40000 ALTER TABLE `tbl_customer` DISABLE KEYS */; INSERT INTO `tbl_customer` VALUES (1,'Customer A'); /*!40000 ALTER TABLE `tbl_customer` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `tbl_invoice` -- DROP TABLE IF EXISTS `tbl_invoice`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `tbl_invoice` ( `invoice_id` int(20) unsigned NOT NULL auto_increment, `FK_cust_id` int(20) unsigned default NULL, `FK_proj_id` int(20) unsigned default NULL, `invoice_date` date default NULL, `invoice_total` varchar(30) default NULL, `invoice_status` enum('Pending','Sent') default NULL, PRIMARY KEY (`invoice_id`), KEY `FK_cust_id` (`FK_cust_id`), KEY `FK_proj_id` (`FK_proj_id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `tbl_invoice` -- LOCK TABLES `tbl_invoice` WRITE; /*!40000 ALTER TABLE `tbl_invoice` DISABLE KEYS */; INSERT INTO `tbl_invoice` VALUES (1,1,1,'2010-01-30','500','Sent'); /*!40000 ALTER TABLE `tbl_invoice` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `tbl_invoice_item` -- DROP TABLE IF EXISTS `tbl_invoice_item`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `tbl_invoice_item` ( `invoice_item_id` int(20) unsigned NOT NULL auto_increment, `FK_job_id` int(20) unsigned default NULL, `FK_invoice_id` int(20) unsigned default NULL, `invoice_item_price` varchar(30) default NULL, `invoice_status` enum('Pending','Sent') default 'Pending', PRIMARY KEY (`invoice_item_id`), KEY `FK_job_id` (`FK_job_id`), KEY `FK_invoice_id` (`FK_invoice_id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `tbl_invoice_item` -- LOCK TABLES `tbl_invoice_item` WRITE; /*!40000 ALTER TABLE `tbl_invoice_item` DISABLE KEYS */; INSERT INTO `tbl_invoice_item` VALUES (1,1,1,'250','Sent'),(2,2,1,'250','Sent'); /*!40000 ALTER TABLE `tbl_invoice_item` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `tbl_job` -- DROP TABLE IF EXISTS `tbl_job`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `tbl_job` ( `job_id` int(20) unsigned NOT NULL auto_increment, `FK_proj_id` int(20) unsigned default NULL, `FK_langt_id` int(20) unsigned default NULL, `job_title` varchar(255) default NULL, `job_pages` int(10) default '1', `job_complete` enum('Yes','No') default 'No', `job_complete_date` timestamp NULL default NULL, PRIMARY KEY (`job_id`), KEY `FK_proj_id` (`FK_proj_id`), KEY `FK_langt_id` (`FK_langt_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `tbl_job` -- LOCK TABLES `tbl_job` WRITE; /*!40000 ALTER TABLE `tbl_job` DISABLE KEYS */; INSERT INTO `tbl_job` VALUES (1,1,1,'Job 1',1,'Yes','2012-30-01 17:30:00'), (2,1,3,'Job 2',2,'Yes','2012-30-01 17:30:00'); /*!40000 ALTER TABLE `tbl_job` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `tbl_language` -- DROP TABLE IF EXISTS `tbl_language`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `tbl_language` ( `lang_id` int(20) unsigned NOT NULL auto_increment, `langname` varchar(255) default NULL, PRIMARY KEY (`lang_id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `tbl_language` -- LOCK TABLES `tbl_language` WRITE; /*!40000 ALTER TABLE `tbl_language` DISABLE KEYS */; INSERT INTO `tbl_language` VALUES (1,'EN'),(2,'DE'),(3,'FR'); /*!40000 ALTER TABLE `tbl_language` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `tbl_project` -- DROP TABLE IF EXISTS `tbl_project`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `tbl_project` ( `proj_id` int(20) unsigned NOT NULL auto_increment, `proj_title` varchar(255) default NULL, `FK_cust_id` int(20) unsigned default NULL, `FK_langsid` int(20) unsigned default NULL, `proj_start_date` timestamp NULL default NULL, `proj_end_date` timestamp NULL default NULL, `proj_status` enum('Open','Hold','Enquiry','Redundant','Complete') default NULL, PRIMARY KEY (`proj_id`), KEY `FK_cust_id` (`FK_cust_id`), KEY `FK_langsid` (`FK_langsid`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `tbl_project` -- LOCK TABLES `tbl_project` WRITE; /*!40000 ALTER TABLE `tbl_project` DISABLE KEYS */; INSERT INTO `tbl_project` VALUES (1,'Project A', 1,2,'2012-01-30 09:00:00','2012-01-30 17:30:00','Complete'); /*!40000 ALTER TABLE `tbl_project` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `tbl_task` -- DROP TABLE IF EXISTS `tbl_task`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `tbl_task` ( `task_id` int(20) unsigned NOT NULL auto_increment, `task_title` varchar(50) default NULL, PRIMARY KEY (`task_id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `tbl_task` -- LOCK TABLES `tbl_task` WRITE; /*!40000 ALTER TABLE `tbl_task` DISABLE KEYS */; INSERT INTO `tbl_task` VALUES (1,'Preparation'),(2,'Typesetting'),(3,'Correction'),(4,'Evaluate'); /*!40000 ALTER TABLE `tbl_task` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `tbl_task_item` -- DROP TABLE IF EXISTS `tbl_task_item`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `tbl_task_item` ( `task_item_id` int(20) unsigned NOT NULL auto_increment, `FK_job_id` int(20) unsigned default NULL, `FK_task_id` int(20) unsigned default NULL, `status` enum('Pending','Ready','Sent','Active','Complete') default 'Pending', `task_complete_date` timestamp NULL default NULL, `task_order` int(20) unsigned default NULL, PRIMARY KEY (`task_item_id`), KEY `FK_task_id` (`FK_task_id`), KEY `FK_job_id` (`FK_job_id`) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `tbl_task_item` -- LOCK TABLES `tbl_task_item` WRITE; /*!40000 ALTER TABLE `tbl_task_item` DISABLE KEYS */; INSERT INTO `tbl_task_item` VALUES (1,1,1,'Complete','2012-01-30 10:00:00',1),(2,1,2,'Complete','2012-01-30 11:00:00',2),(3,1,3,'Complete','2012-01-30 12:00:00',3),(4,1,4,'Complete','2012-01-30 17:30:00',4),(5,2,1,'Complete','2012-01-30 10:00:00',1),(6,2,3,'Complete','2012-01-30 12:00:00',3),(7,2,4,'Complete','2012-01-30 17:30:00',4); /*!40000 ALTER TABLE `tbl_task_item` ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2012-01-30 18:59:47 QUERY: SELECT DATE_FORMAT(proj_end_date, '%m') as month_number, MONTHNAME(proj_end_date) AS the_month, YEAR(proj_end_date) AS the_year, SUM(analysis_net) as analysis_net, SUM(analysis_gross) as analysis_gross, SUM(job_pages) as job_pages, SUM(invoice_item_price) as total, CASE WHEN task_title='Typesetting' AND ti1.status='Complete' THEN 'Typeset' ELSE 'Non-Typeset' END AS 'JobType', langname FROM tbl_job as j INNER JOIN tbl_task_item as ti1 ON ti1.FK_job_id = j.job_id INNER JOIN tbl_task as t ON t.task_id = ti1.FK_task_id INNER JOIN tbl_invoice_item as ii ON ii.FK_job_id = j.job_id INNER JOIN tbl_invoice as i ON i.invoice_id=ii.FK_invoice_id INNER JOIN tbl_analysis as a ON a.FK_job_id = j.job_id INNER JOIN tbl_language as l ON l.lang_id = j.FK_langt_id INNER JOIN tbl_project as p ON p.proj_id = j.FK_proj_id WHERE p.FK_cust_id = 1 AND proj_status='Complete' GROUP BY the_year, month_number, JobType, langname ORDER BY the_year DESC, month_number DESC, JobType DESC, langname ASC