Friday, 27 July 2012

Combning firstname and lastname as name in Mysql Query

I had the following mysql statement that I thought I could simpify.

$query = "SELECT #__users.id,#__users.name FROM #__users,#__comprofiler where #__users.id=#__comprofiler.user_id order by name ";
 

this has become

$query = "SELECT #__comprofiler.user_id as id, CONCAT(#__comprofiler.firstname,' ',#__comprofiler.lastname)AS name FROM #__comprofiler ORDER BY name ";


which uses CONCAT and AS to use two colums and return as one; with a space in the middle also

virtuemart 2.0 Paypal payment method not working

After setting up my Virtuemart 2.0 store ; and setting up a payment method of Paypal - it wasn't working.


The solution was to fill in all the min max details ( i also changed the group to 'default' ) and it now works.


Thursday, 19 July 2012

Adding PAYE and NON-PAYE ordering and message to the Bacs report.

This is working in the Joomla Payroll System component I am developing.   I have already added a tick box to the employee area and now I need to add conditions to the BACS report so firstly the ordering is done by the Pay Type.  and then secondly I need to pick up when the change over happens in the table that is out putted.

The details actually pass in and out of two tables when calculated.

OPEN components/com_projectfork/models/bacs.php

in the Query on line 50 that gets the details from the shifts - get the info from the column #__comprofiler.cb_PayType that had been added.

on line 88 that information is picked up again with

$PayType=$hrs->cb_PayType;


and passed into the #__time_diff  row.  Which I've again set up the respective column.


line 107  - information retrieved from #__time_diff . This time as well as filling the array with  the #__time_diff.PayType column; we also use the column to ORDER BY .  So that all the nonPAYE comes first and PAYE second.

ORDER BY #__time_diff.PayType



on line 151 we add the details to yet another table in

, '$datares->PayType'



It's from this table that's called up in the page

OPEN components/com_projectfork/views/bacs/tmpl/default.php


on line 82 the following code deals with whether or not to tell the frontend that the PAYE & NON PAYE columns have started.

$PAYEorNOT = $ids->PayType;



if ($lastPAYE != $PAYEorNOT ){

echo ' ';


if($PAYEorNOT == '0'){

echo 'NON PAYE';
}


elseif($PAYEorNOT == 1 )
{

echo 'PAYE';


}


echo '';


$lastPAYE = $PAYEorNOT;

}

FOR THIS TO WORK YOU ALSO NEED THE TABLE CELLS


#__time_diff.PayType
#__comprofiler.cb_PayType
#__ alterations_bacs_amount.PayType

Wednesday, 18 July 2012

Virtuemart 2.0 Related Products Css

Just a quick fix here to get all the related products on one line and get rid of the filename - without having to change any code.

just paste this into your css file

span.product-field-display{
float: left;
}

span.product-field-display a:link, span.product-field-display a:visited, span.product-field-display a:hover{
color: #666666;
    font-weight: bold;
}

span.vm-img-desc {
color: #fff;
}
If anyone goes further and make changes to the layout of Related Products itself I'd be interested to know as I couldn't find where the code for this was



Monday, 16 July 2012

Virtuemart 2.0 Joomla 2.5 upgrade - related products information not transferred.

On the old Virtuemart the table 'jos_vm_product_relations' is used for the product relations information.

And in Virtuemart 2.0 the table 'j25_virtuemart_product_customfields' is used .  this table also contains dates and time of the entry.  So you may want to change this in the script I have written here.

You'll need to put you mysql details in a /includes/config.php file .

Apart from that it should help you as long as all the product id's have stayed the same in your transfer.

#######


echo "hello world

";

include ("includes/config.php");




// now i need to do a database call and get the list of related products from Virtuemart
$query = "SELECT *
FROM  `jos_vm_product_relations` ";
;
$result = mysql_query($query)
or die ("1 st Query failed");
$customfield_id = '1';
while ($row = mysql_fetch_array($result))
{

$product_id = $row['product_id'];
$related_products = strip_tags($row['related_products']);
$related_products_array = explode("|", $related_products);

foreach ($related_products_array as $related_product){

echo "('$customfield_id', '$product_id', 1, '$related_product', NULL, '', 0, '2012-07-15 22:10:01', 62, '2012-07-15 22:10:01', 62, '0000-00-00 00:00:00', 0, 0),
";
$customfield_id++;
}

}

echo "
finished;";





?>

Friday, 13 July 2012

Compare Value in Mysql - using count arguement inside the query

What I was trying to achieve here was to be able to check for duplication of images.   In this scenario I'm looking for  users 'Employee'  who have been put down as doing 2 shifts at exactly the same time .   Ideally I want the mysql query to be doing as much work as possible.

For this task I'm running 2 queries in total . 

1.  the first one makes a count of employees who have shifts on the same day.
2. Secondly to run through those employees and see if there shifts clash.

I'm pretty sure that there might be a one query solution here somewhere and when I get a chance I may well come back to this and have a look.  Programming can be a bit like playing Soduko sometimes and you have to stare at the problem for a while before it comes to you.

Anyway to those two queries, the trick in the first one is using HAVING over using WHERE enabling me to use COUNT as a WHERE clause inside the mysql query.   Take a look at this example.


$query  = "select ";
      $query .= "id, user_id, cdate, start_time, end_time, count(*) ";
      $query .= "from #__pf_time_tracking ";
    $query .= "group by user_id, cdate having count(*) >1 ";


This returns to me the rows of all Employees that have more than one shift on that day.

Then with those rows I iterate through them to see if there is a time clash.  The trick here using BETWEEN as the comparison operator.   It calculates whether the shift being iterated through has at any time in the shift got a time that collides with the start time of the shift we are checking in the MYSQL query.

         $query  = "select ";
      $query .= "tt.project_id, tt.start_time, tt.end_time, cp.user_id, cp.firstname, cp.lastname, pj.title, pj.location ";
      $query .= "from #__pf_time_tracking as tt ";
        $query .= "LEFT JOIN (#__comprofiler as cp, #__pf_projects as pj) ON (tt.user_id=cp.user_id AND tt.project_id=pj.id) ";
    $query .= "WHERE tt.user_id='$user' AND tt.cdate='$date' AND tt.start_time BETWEEN '$start_time' AND '$end_time' AND tt.id != '$shift_id' ";

        

Please let me know if you think you know of any improvements to this logic.

Thursday, 12 July 2012

Make a simple Module in Joomla 2.5

I've just made a real simple module for Joomla 2.5 and I think it's worth recording as a reference for future module creation - as a decent sample template for MVC module in Joomla.

It tackles some simple questions I was asking when looking to create a module.

Like ' where to add the logic for Database calls in a Joomla Module' and how to fix a Failed loading XML file error when uploading a module.

Heres the files and folders we needed to create.

modules/mod_PRSwarnings/
tmpl/default.php
tmpl/index.html
helper.php
index.html
modPRSwarnings.php
modPRSwarnings.xml


and here's the code


modules/mod_PRSwarnings/tmpl/default.php
[code]



/**
 * PayRollSystem Warnings
 *
 * @package PayRollSystem Warnings
 * @subpackage PayRollSystem Warnings
 * @version   1.0 July, 2012
 * @author    DJ http://www.littleripples.com
 * @copyright Copyright (C) 2010 - 2012 www.littleripples.com, LLC
 * @license   http://www.gnu.org/licenses/gpl-2.0.html GNU/GPLv2 only
 *
 */

// no direct access
defined('_JEXEC') or die;





if($items != NULL ){

 foreach ($items as $item) {
$user_id = $item->id;
$user_id2 = $item->user_id;
$project_id = $item->project_id;
$cdate = $item->cdate;
$start_time = $item->start_time;
$end_time = $item->end_time;
$firstname = $item->firstname;
$lastname = $item->lastname;

echo "Last Shift: id $user_id ($user_id2)- $firstname $lastname  - Started $start_time - finished $end_time at ";

}
}

[/code]

modules/mod_PRSwarnings/tmpl/index.html
[code]

[/code]



modules/mod_PRSwarnings/helper.php
[code]


/**
 * PayRollSystem Warnings
 *
 * @package PayRollSystem Warnings
 * @subpackage PayRollSystem Warnings
 * @version   1.0 July, 2012
 * @author    DJ http://www.littleripples.com
 * @copyright Copyright (C) 2010 - 2012 www.littleripples.com, LLC
 * @license   http://www.gnu.org/licenses/gpl-2.0.html GNU/GPLv2 only
 *
 */

// no direct access
defined('_JEXEC') or die;

class ModPRSwarnings
{
 public function getLastShift(){
      $db = &JFactory::getDBO();

      $query  = "select ";
      $query .= "tt.id, tt.project_id, tt.cdate, tt.start_time, tt.end_time, cp.user_id, cp.firstname, cp.lastname ";
      $query .= "from #__pf_time_tracking as tt ";
    $query .= "LEFT JOIN (#__comprofiler as cp) ON tt.user_id=cp.user_id ";
$query .= "limit 1 ";
   

      $db->setQuery($query);
      $items = ($items = $db->loadObjectList())?$items:array();
      return $items;
    }


}

[/code]


modules/mod_PRSwarnings/modPRSwarnings.php

[code]


//no direct access
defined('_JEXEC') or die('Direct Access to this location is not allowed.');

// include the helper file
require_once(dirname(__FILE__).DS.'helper.php');


$items = ModPRSwarnings::getLastShift();

// include the template for display
echo 'Hello World';
require(JModuleHelper::getLayoutPath('mod_PRSwarnings'));

[/code]




modules/mod_PRSwarnings/modPRSwarnings.xml

[code]


    PayRollSystem Warnings
    DJ Millward
    July 2012
    Copyright (C) 2005 - 2010 Open Source Matters. All rights reserved.
    http://www.gnu.org/licenses/gpl-2.0.html GNU/GPL
    dj@lrip.eu
    littleripples.com
    1.0.0
    This module works with the PayRollSystem component to show the last shift added and any warningings on the system
   
        mod_PRSwarnings.php
        helper.php
        mod_PRSwarnings.xml
        index.html
        tmpl/default.php
        tmpl/index.html
   
   
       
   


[/code]


When uploading the module to my website I tried to use the 'Discover' function .  But got the error.

 *Failed loading XML file** * XML: failed to load  external entity * joomla development


the fix for this was to use the 'Install from Directory' method of installing a module. 



Wednesday, 11 July 2012

Showing Youtube Videos in Virtuemart 2.0

After upgrading to Joomla Virtuemart 2.0 our YouTube videos in the product descriptions aren't working.  The solution for this is real simple.

Log in to your administrator area
> go to virtuemart configuration
> under the 'shop' tag - scroll down to 'Enable Joomla Plugin'

and make sure it's ticked.

Sunday, 8 July 2012

Virtuemart 2.0 Product Display - get rid of image name

After an upgrade ( cough!! ) to Virtuemart 2.0 I've noticed that on the product page the product image filename displays.   Take a look here.


To get rid of these then you need to edit the file

components/com_virtuemart/views/productdetails/tmpl/default_images.php

find both the displayMediaFull and displayMediaThumb functions of $this object and change the last arguement that will be True ( if the image name is showing on your website ) to false.





Friday, 6 July 2012

how to fix the pagination css in joomla 2.5

I've a website that I upgraded from Joomla 1.7 to Joomla 2.5 and I noticed that the article Pagination was looking very scruffy - in that it was just a bulleted list.  

Using Firebug for Mozilla I noticed that the call up for pagination comes from com_contact/views/

However I didn't need to change the HTML for the following fix.  If you add the following to you template.css ( or whatever .css file )

div.pagination ul{

}

div.pagination li{

}

div.pagination li.pagination-start{

}

div.pagination li.pagination-start span.pagenav{

}

div.pagination li.pagination-prev{

}

div.pagination li.pagination-prev span.pagenav{

}

div.pagination li span.pagenav{

}

div.pagination li a:link {

}

div.pagination li.pagination-next{

}

div.pagination li.pagination-next a:{

}

div.pagination li.pagination-end {

}

div.pagination li.pagination-end a:{

}


Now you can use Firebug to write css on the fly for all cells.  Here's all the I need to add though

div.pagination ul{
list-style-type:none !important;
margin-left: 240px;
}

div.pagination li{
float:left;
margin-left: 30px;
}

Take a look at the Anns Cottage News page for an example of it working.

Tuesday, 3 July 2012

GIMPs verion of photoshop free transfom

There's a transform button next to scale on the tool pallet .

Here's a pic of where it is.