John Davidson

Message:

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;

    

}

---

My Answer:
public function getBookStock($book_id){
$this->db->select('book_id, SUM(brought) as total_brought, SUM(sold) as total_sold');
$this->db->from('book_transactions');
$this->db->where('book_id', $book_id);
$this->db->group_by('book_id');
$query = $this->db->get();

$result = $query->row();

$stock = $result->total_brought - $result->total_sold;

return $stock;
}