Codeigniter MYSQL data minus date1 data from date2

Viewed 107

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;
    
}
1 Answers

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

Related