<?php if (!defined('BASEPATH'))
    exit('No direct script access allowed');

class Popularproducts_model extends CI_Model {

    public function __construct() {
        parent::__construct();
    }

// GET PRODUCT GROSS MARGIN
    function getpopularproducts($datefrom, $dateto) {

       
        $table_name = "PRODUCTS AS P";
        $this->db->select("P.NAME,  IFNULL(SUM(TL.UNITS),0) AS QTY , IFNULL(SUM(TL.UNITS * TL.PRICE),0) AS AMOUNT", false)->from($table_name);
        if ($datefrom && $dateto) {
            $this->db->where("R.DATENEW >=", $datefrom);
            $this->db->where("R.DATENEW <=", $dateto."+1 day");
        } else if ($datefrom) {
            $this->db->where("R.DATENEW >=", $datefrom);

        }
       $this->db->join('CATEGORIES AS C', 'P.CATEGORY=C.ID');
        $this->db->join('TICKETLINES AS TL', 'TL.PRODUCT=P.ID', 'LEFT');
       
        $this->db->join('TICKETS AS T','T.ID = TL.TICKET','LEFT');

         $this->db->join('RECEIPTS AS R','R.ID = T.ID', 'LEFT');

        $this->db->where("P.ISCOM <> TRUE AND C.PARENTID IS NULL OR (C.PARENTID NOT LIKE -1 AND C.PARENTID NOT LIKE -2)");
        $this->db->group_by('P.ID');
        $this->db->order_by('QTY', 'DESC');

        $rows2 = $this->db->get()->result();

          $i = 1;
        $totalqty = 0.0;
        $totalamount = 0.0; 
        $data_row = array();
          foreach ($rows2 as $row) {
         
            $totalqty = $totalqty + $row->QTY;
            $totalamount = $totalamount + $row->AMOUNT;

            if ($row->QTY != 0 && $row->AMOUNT != 0) 
                {
                $qtypct = number_format(($row->QTY / $totalqty) * 100, 2, '.', ',') . '%';
                $marginpct = (($row->AMOUNT - $row->QTY) / $row->AMOUNT) * 100;
                $amountpct = number_format(($row->AMOUNT / $totalamount) * 100, 2, '.', ',') . '%';
                $margin = number_format(($row->AMOUNT - $row->QTY), 2, '.', ',');
            } else {
                $qtypct = 0;
                $marginpct = 0;
                $amountpct = 0;
                $margin = 0;
            }
            
            $data_row[] = array(
                'NAME' => $row->NAME, 
                'QTY' => $row->QTY, 
                'QTYP' => $qtypct, 
                'AMOUNT' => $row->AMOUNT, 
                'AMOUNTP' => $amountpct, 
            );
            
         $i++;
        }

        return $data_row;
    }

}