problem with temporary database!

[eluser]olbanana[/eluser]
i have a deployment to do within 2 days time and for the last three days i have been busy trying to understand this one thing am stuck in. It probably might be very silly of me but please take me as a newbie.

I am using an opensource software php opensource pos and it is made on CI. Now the issue is that the reports in the application are different than what my client wants. I want to change that to the way they want it but i cant.

this is the code:

Code:

$this->db->select('sale_date, sum(total) as total, sum(profit) as profit');
      $this->db->from('sales_items_temp');
      if ($inputs['sale_type'] == 'sales')
      {
       $this->db->where('quantity_purchased > 0');
      }
      elseif ($inputs['sale_type'] == 'returns')
      {
       $this->db->where('quantity_purchased < 0');
      }
    
      $this->db->group_by('sale_date');
      $this->db->having('sale_date BETWEEN "'. $inputs['start_date']. '" and "'. $inputs['end_date'].'"');
      $this->db->order_by('sale_date');
      return $this->db->get()->result_array();

every thing is working fine and as expected. Now i need to make some changes but the issue is i cannot find the temp database to see which fields it has. This database table (the temp one) is being used in all the reports functions, same database.

Can some one be so helpful and explain me the way it works, or how is that i can make a change to it!?

The scenario is that it is giving a summary of all the sales in the selected date range as total. I want a break down in terms of cash, credit, credit card, debit card etc.

I have another report which shows that, but not in date wise as in not saying how much in which day but in total of the date range. This one gives the total as in total sales in a day but not the break up of it as in what was what.

You can login here first:
https://demo.phppointofsale.com/index.php/login (id and password is saved on the page)

Then you can paste this link on the address bar and see the report.
https://demo.phppointofsale.com/index.ph...3-13/all/0

I need it to be a breakdown of the total and remove the tax, profit and subtotal columns and change that with the kind of payment as in cash | credit | cheque etc and then a total for that day. All that information is available in another report which it generates and that is from the same database table as this one!

PS: I am sorry for such a big writing but i am really stuck, please help me out. Thank you in advance.

[eluser]Krzemo[/eluser]
You cant find temp database or sales_items_temp table?

[eluser]olbanana[/eluser]
yes i can not find that. In fact i do not know how it really functions!

PS: I have updated my first post a little to give you a better understanding of my problem. Please read it, i will really be a big favor if you can help! Thank You!

[eluser]Krzemo[/eluser]
I still don't understand what do you have problem with.
1) You don't know PHP?
2) You don't know CI?
3) You don't know SQL?
4) You don't know the application?
5) All of them

Will front (controller and view) of the application display anything model will provide? If yes, just write desired query and you are done.
Write the query in SQL first (MySQL Query Browser) and then translate it into CI's active record if you fancy that and you're done.

I'm sorry but I have no idea about phppointofsale.com and no time to check it.

[eluser]olbanana[/eluser]
the problem i have is in CI and SQL. In fact i cannot be sure as well as to where is the problem. But as far as i can figure out that is where the problem lies.

[eluser]Krzemo[/eluser]
Start with writing the query so you get data in a desired way.
I can try to help you with the rest.

[eluser]olbanana[/eluser]
thank you very much....i will post the query asap.

[eluser]olbanana[/eluser]

Code:

$query = $this->db->query('SELECT `sale_time` , `payment_type` FROM `ospos_sales`');

foreach ($query->result() as $row)
{ ?>
  <tr>
     <td>&lt;?php
  echo $row->sale_time.'</td><td>';
     echo $row->payment_type.'</td>'; ?&gt;
     </tr>
  &lt;?php }

echo 'Total Results: ' . $query->num_rows();
?&gt;

I have written the query as this. The issue with it is that it is giving the breakdown of every sale that day, but i want a total of all the sales that day as in cash sales :100, credit sales: 200 etc

at the moment it is like this

12/01/10
sale 1: 100 cash
sale 2: 200 credit

It is displaying like this:
2011-12-13 09:04:17 Cash: aed.136.00
2011-12-13 18:39:19 Cash: aed.21.00
2011-12-13 18:42:19 Cash: aed.15.00
2011-12-13 18:45:16 Cash: aed.279.00

I need it to be

DATE | CASH SALES | CREDIT SALES | CHEQUE | TOTAL |
1/2/10 | 2000 | 150 | 200 | 2350 |
1/3/10 | 1000 | 150 | 200 | 1350 |

[eluser]Krzemo[/eluser]
You have a lot to learn when it comes to SQL.
Hints: SUM() and GROUP BY

[eluser]olbanana[/eluser]
i am sorry to if i am causing any frustration. This is how i want it, bruv be kind and tell me how i can do it.

It is displaying like this:
2011-12-13 09:04:17 Cash: aed.136.00
2011-12-13 18:39:19 Cash: aed.21.00
2011-12-13 18:42:19 Cash: aed.15.00
2011-12-13 18:45:16 Cash: aed.279.00

I need it to be

DATE | CASH SALES | CREDIT SALES | CHEQUE | TOTAL |
1/2/10 | 2000 | 150 | 200 | 2350 |
1/3/10 | 1000 | 150 | 200 | 1350 |