6

This is my Codeigniter project, I've made a bookshop function that counts the amount of brought and sold for the book in each entry and minus the sold price and gives me the amount that I currently have for each book.

Type "0" = The amount that I received

Else it's the amount that I spent

What I want to do now is I want to fetch the total amount that I received between date1 and date2 which should look something like "get_book_amount" for date1 -(minus) "get_book_amount" from date2.

The equation is like this:

On Date1 total amount that I received is 100, on Date2 it's 300. So the total amount that increased during that time is "200" which is the number I want to get.

How can I achieve this?

public function bookshop($date1,$date2)
{

    $total_books       = 0;
    $books      = '';

    $this->db->select("*");
    $this->db->from('books_s');
    $this->db->where(['books_s.type' => 'Fantasy']);
    $this->db->where(['books_s.stock' => 'Yes']);
    $query = $this->db->get();
    if($query->num_rows() > 0)
    {
        $count_books =  $query->result();
        if($count_books != NULL)
        {
            foreach ($count_books as $single_book) 
            {
                $getamount =  $this->get_book_amount($single_book->id,$date1,$date2);

                if($getamount > 0)
                {
                    $amt = $getamount;
                }
                else
                {
                    $amt    = -($getamount);
                }
                 $total_books = $total_books+$amt;

                $books .= '<tr><td><h4>'.$single_book->name.'</h4></td>
                            <td style="text-align:right" ><h4>'.$amt.'</h4></td></tr>';

            }
                $books .= '<tr"><td ><h4><i>Total Current Assets</i></h4></td><td style="text-align:right;" ><h4><i>'.$total_books.'</i></h4></td></tr>';
        }
    }

}

//USED TO COUNT SINGLE BOOK AMOUNT
public function get_book_amount($warehouse_id,$date1,$date2)
{
    $count_total_amount = 0;
    $this->db->select("bookentry.id as transaction_id,bookentry.date,bookentry.naration,book_stock_entry.*");
    $this->db->from('book_stock_entry');
    $this->db->join('bookentry', 'bookentry.id = book_stock_entry.parent_id');
    $this->db->where('book_stock_entry.warehouse', $warehouse_id);
    $this->db->where('bookentry.date >=', $date1);
    $this->db->where('bookentry.date <=', $date2);

    $query = $this->db->get();
    if ($query->num_rows() > 0)
    {
        $count_books =  $query->result();
        $count_total_amount = 0;
        if($count_books != NULL)
        {
            foreach ($count_books as $single_book) 
            {   

                    if($single_book->type == 0)
                    {
                       $count_total_amount = $count_total_amount + $single_book->getamount;
                    }
                    else 
                    {
                        $count_total_amount = $count_total_amount - $single_book->getamount;   
                    } 
                    
            }
        }
        
    }

    if($count_total_amount == 0)
    {
        $count_total_amount  = NULL;
    }
    else
    {
        $count_total_amount = number_format($count_total_amount,'3','.','');
    }
    
    return $count_total_amount;
    
}
Numlet
  • 819
  • 1
  • 9
  • 21
  • 3
    Please provide a db fiddle so that volunteers can instantly work with an accurate test scenario. – mickmackusa Aug 01 '21 at 07:44
  • I think it is better to ask the database to count the amount rather than calculate it in PHP. – cakyus Nov 19 '21 at 14:06
  • instead of trying to generate the query in codeigniter, first of all create the code in the database and then recreate it in the application. You can do what you need in a single query – clairerb6 Jun 16 '22 at 07:15

1 Answers1

0

I think it's best if you do two separete querys and then calculate.

Create a Model Method call "get_book_amount_until_date"

First call the method with your date 1 and save it in a var. Second call the method with your date 2 and save it in another var.

Now do: Total = Results_date_2 - Results_date_1

MDM
  • 21
  • 2