<?php

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

class Costing_model extends CI_Model {

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

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


//COST
//$datefrom='2012-03-07';
//$dateto='2012-03-17';

        $jwhere="";
        if ($datefrom && $dateto) {
            $jwhere = " AND DATE(SD.DATENEW) >= '" . $datefrom . "'"; //QUOTING DATE BECAUSE USING FALSE FOR IFNULL
            $jwhere = $jwhere . " AND DATE(SD.DATENEW) <= '" . $dateto . "'";
        } else if ($datefrom) {
            $jwhere = " AND DATE(SD.DATENEW)= '" . $datefrom . "'";
        }
        
         $table_name = "LOCATIONS AS L";
        $this->db->select("L.NAME, IFNULL(SUM(SD.UNITS * SD.PRICE),0) AS COST",false)->from($table_name);
        $this->db->join('STOCKDIARY AS SD', 'L.ID=SD.LOCATION AND (SD.REASON = 1 OR SD.REASON = -2 OR SD.REASON = 1000) '.$jwhere, 'LEFT');
        $this->db->where("L.ID <>","0");
        $this->db->group_by('L.NAME');


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

//SALES
        $table_name = "LOCATIONS AS L";
        $this->db->select('L.NAME, IFNULL(SUM(TL.UNITS * TL.PRICE),0) AS SALE',false)->from($table_name);
        
        $this->db->where("L.ID <>","0");
         $this->db->join('LOCATIONS_PROD AS LP', 'L.ID=LP.LOCATION', 'LEFT');
        

         $jwhere="";
         if ($datefrom && $dateto) {
            $jwhere = " INNER JOIN RECEIPTS R ON R.ID=TL.TICKET AND DATE(R.DATENEW) >= '" . $datefrom . "'"; //QUOTING DATE BECAUSE USING FALSE FOR IFNULL
            $jwhere = $jwhere . " AND DATE(R.DATENEW) <= '" . $dateto . "'";
        } else if ($datefrom) {
                       $jwhere = " INNER JOIN RECEIPTS R ON R.ID=TL.TICKET AND DATE(R.DATENEW) = '" . $datefrom . "'";
        }
          $this->db->join('TICKETLINES TL '.$jwhere, 'LP.PRODUCT=TL.PRODUCT', 'LEFT');
        $this->db->group_by('L.NAME');
        $rows1 = $this->db->get()->result_array();
       

        $i = 0;
        $totalcost = 0.0;
        $totalsale = 0.0;

//CALCULATE TOTAL FOR % CALCULATION
        foreach ($rows1 as $row) {

           $totalcost = $totalcost + $rows2[$i]['COST'];
            $totalsale = $totalsale + $row['SALE'];
            $i++;
        }
       
         $i = 0;
         $data['page'] = "0";
        foreach ($rows1 as $row) {

             if($rows2[$i]['COST']!=0 && $row['SALE']!=0)
             {
                 $costpct = number_format(($rows2[$i]['COST']/$totalcost)*100, 2, '.', ',') . '%';
                 $marginpct = (($row['SALE']-$rows2[$i]['COST'])/$row['SALE'])*100 ;
                $salepct = number_format(($row['SALE']/$totalsale)*100, 2, '.', ',') . '%';
                $margin = number_format(($row['SALE']-$rows2[$i]['COST']), 2, '.', ',');
             }
            else
            {
                 $costpct = 0;
                 $marginpct=0;
                 $salepct=0;
                 $margin=0;
            }
           
             $data['rows'][$i]['id'] = $i + 1;
            $data['rows'][$i]['cell'] = array($row['NAME'], $rows2[$i]['COST'], $costpct, $row['SALE'], $salepct, $margin, $marginpct);

            $i++;
        }
//        $tots = $tot;
//        $tot = number_format($tot, 2, '.', ',');
//        $data['rows'][$i]['id'] = $i + 1;
//        $data['rows'][$i]['cell'] = array('TOTAL SALES', $tot, '100%');
//        $i = $i + 1;
        
//
//
//
//        //LOOP POPULATING PRIMARY DATA
//        $data['rows'][$j]['cell'] = array('SALES', '', '');
//        foreach ($rows as $row) {
//            $data['rows'][$j]['id'] = $j + 1;
//            $data['rows'][$j]['cell'] = array($row['NAME'], $row['AMOUNT'], $row['PERCENTAGE']);
//            $j++;
//            $tot2 = $tot2 + $row['AMOUNT'];
//        }
//        $data['rows'][$j]['id'] = $j + 1;
//
//
//        $data['rows'][$j]['cell'] = array('', '', '');
//
//
//        $j = $i;
//        $data['rows'][$i]['cell'] = array('COST OF SALES', '', '');
//        $j = $j + 1;
//        foreach ($rows as $row) {
//
//            $perc = 0.0;
//            $amount = $row['AMOUNT'];
//
//            $perc = ($amount / $tot2) * 100;
//            $row['PERCENTAGE'] = number_format($perc, 2, '.', ',') . '%';
//            $row['AMOUNT'] = number_format($amount, 2, '.', ',');
//            $data['rows'][$j]['id'] = $j + 1;
//            $data['rows'][$j]['cell'] = array("          " . $row['NAME'], $row['AMOUNT'], $row['PERCENTAGE']);
//            $j++;
//        }
//        $totc = $tot2;
//        $tot2 = number_format($tot2, 2, '.', ',');
//        $data['rows'][$j]['id'] = $j + 1;
//
//        $data['rows'][$j]['cell'] = array('TOTAL COST', $tot2, '100%');
//        $j = $j + 1;
//        $gross = 0;
//        $grossperc = 0;
//         $grosstotal = number_format($tots - $totc, 2, '.', ',');
//        $gross = ($tots - $totc)/$tots;
//
//        $grossperc = number_format($gross * 100, 2, '.', ',') . '%';
////        $gross = number_format($gross, 2, '.', ',');
//        $data['rows'][$j]['cell'] = array('GROSS MARGIN', $grosstotal, $grossperc);

        return json_encode($data);
    }

}