Problem With Sum & Join

[eluser]cyberseal[/eluser]
Hi, I have been playing with this for a couple of days and just cant get it to work. im doing a report for a free epos system (PHPPOINTOFSALE)

The problem is.

Im trying to get the payment types and totals to generate so at the end of the day the operator can see how much cash ect is in the till. I Have 2 tables.

If i link to just the phppos_payments it generates the correct sum (it adds everything up) however i need it in date ranges I.E (Today, Yesterday Ect). So when i join the 2 tables to display the date selected it outputs the wrong sum. And this is what i cant work out.

For example I Have selected date 2010-07-13

This is what it should look like if it worked
2010-07-13 Cash £50.00
2010-07-13 Debit Card £13.00

Total: £63.00

However this is what it actually outputs
2010-07-13 Cash 173.00
2010-07-13 Debit Card 26.00
Total: £63.00

Table Structure of used tables

phppos_payments
sale_id, payment_type, payment_amount

and

phppos_sales_items_temp
sale_date, sale_id, customer_id, item_id, quantity_purchased, item_cost_price, item_unit_price, item_tax_percent, subtotal, total, tax, profit

File
application\models\reports\summary_payments.php

Code:

class Summary_payments extends Report
{
    function __construct()
    {
        parent::__construct();
    }

    public function getDataColumns()
    {
        return array($this->lang->line('reports_date'), $this->lang->line('reports_payment_type'), $this->lang->line('reports_total'));
    }
    
    public function getData(array $inputs)
    {        
        
        $this->db->select('sales_items_temp.sale_date, sales_payments.payment_type, sum(payment_amount) as amount');
        $this->db->from('sales_payments');
        $this->db->distinct();
        $this->db->join('sales_items_temp', 'sales_items_temp.sale_id = sales_payments.sale_id', 'left', 'right');
        $this->db->where('sales_items_temp.sale_date BETWEEN "'. $inputs['start_date']. '" and "'. $inputs['end_date'].'"');
        $this->db->group_by('sales_payments.payment_type','sales_payments.sale_id');
        $this->db->order_by('sales_items_temp.sale_date');
        return $this->db->get()->result_array();
        
        
    }
    
    public function getSummaryData(array $inputs)
    {
        $this->db->select('sum(total) as total,');
        $this->db->from('sales_items_temp');
        $this->db->where('sale_date BETWEEN "'. $inputs['start_date']. '" and "'. $inputs['end_date'].'"');
        
        return $this->db->get()->row_array();
    }

}

File
application\controllers\reports.php

Code:

function summary_payments($start_date, $end_date)
    {
        $this->load->model('reports/Summary_payments');
        $model = $this->Summary_payments;
        $tabular_data = array();
        $report_data = $model->getData(array('start_date'=>$start_date, 'end_date'=>$end_date));
        
        foreach($report_data as $row)
        {
            $tabular_data[] = array($row['sale_date'], $row['payment_type'], $row['amount']);
        }

        $data = array(
            "title" => $this->lang->line('reports_payments_summary_report'),
            "subtitle" => date('m/d/Y', strtotime($start_date)) .'-'.date('m/d/Y', strtotime($end_date)),
            "headers" => $model->getDataColumns(),
            "data" => $tabular_data,
            "summary_data" => $model->getSummaryData(array('start_date'=>$start_date, 'end_date'=>$end_date))
        );

        $this->load->view("reports/tabular",$data);
    }

Anyhelp would be great. This is just one of them things i REALY cant get by

Thanks
Matt

[eluser]WanWizard[/eluser]
If you want to SUM on "date, type", you need to group by these fields as well. Not only on type.

[eluser]cyberseal[/eluser]
Hi, Thanks for the quick reply

I Have added date to the group

Code:

$this->db->group_by('sales_payments.payment_type','sales_items_temp.sale_date');

However Still leaving the same results

2010-07-13 Cash 173.00
2010-07-13 Debit Card 26.00

when it should be

2010-07-13 Cash £50.00
2010-07-13 Debit Card £13.00

Thanks

Matt

[eluser]WanWizard[/eluser]
What is that distinct() doing there? That might ruin your grouping...

[eluser]cyberseal[/eluser]
Hi

I have removed Distinct(), It was somthing i tryed earyler to see if it would help but didnt, Forgo to remove it however i have now removed it and retryed and still the same result. I cant seem to workout what it is adding as it should be

Selecting Transaction For sale_date and then putting them into groups and then adding togeather the total for each group however im not sure what its adding to make that figure up There where only 3 transactions on that date. Totaling to £63 2 cash totaling £50 and 1 debit Card £13 it looks like on the debitcard transaction its doubing it but im not sure what the cash one is as that isnt £50 doubled..

Im realy stumped

Thanks for your help im realy greatful. AS you can tell im fairly new to codeigniter however i am learing it quick. But just stumped

Thanks
Matt

[eluser]WanWizard[/eluser]
This isn't really a CI issue, but a SQL issue.

As a test, remove the group_by, remove the SUM (just include the payment field), run the query, and do a var_dump($report_data). Do you see the records you expect? This should be the detail records that are summed by the group_by.