Jump to content

kool_samule

Members
  • Posts

    169
  • Joined

  • Last visited

Everything posted by kool_samule

  1. fantasic. thanks for the help
  2. Thanks for the reply, is there anything out there that could jump to a column in a line?
  3. 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
  4. OK my bad, thanks for the heads up. Cheers!
  5. 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..
  6. 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?
  7. Thanks guys, Advice is noted..I'll crack on and hopefully get it sorted. Cheers for the help.
  8. 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.
  9. Yeah, clicked around to trigger an error, but nothing happened..got me baffled.
  10. Hi, Yeah, I've already done that, should have mentioned. FireBug > Console > Debug Info / FireBug > Script > Breakpoints / = Nothing ....?
  11. 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.
  12. Thanks, managed to sort it using file_put_contents.
  13. 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!
  14. 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
  15. Perfect, thanks a lot...would never have got there without your help. Much appreciated... Memory bank: updated.
  16. 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?
  17. 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..
  18. 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 |
  19. 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!
  20. 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...
  21. 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.
  22. 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?
  23. 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?
  24. 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
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.