<?php

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

class Reports_model extends CI_Model {

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

    //GET REPORTS
    function getreports() {
        $page = $this->input->post('page');
        $limit = $this->input->post('rows'); // get how many rows we want to have into the grid
        $sidx = $this->input->post('sidx'); // get index row - i.e. user click to sort
        $sord = $this->input->post('sord'); // get the direction
        
        //Default values
        if (!$sidx)
            $sidx = 'id';
        if (!$sord)
            $sord = 'asc';

        if (!$page)
            $page = 1;
        if (!$limit)
            $limit = 25;
        $search = $this->input->post('_search');
        if($search=='true')
        {
            $searchField = $this->input->post('searchField');
            $searchOper = $this->input->post('searchOper');
            $searchString = $this->input->post('searchString');
            $oper='';
            if($searchOper=='cn')
                $searchQry = 'REPORTS.'.$searchField . ' LIKE "%' . $searchString . '%"';
            else if($searchOper=='eq')
                $searchQry = 'REPORTS.'.$searchField . ' = "' . $searchString .'"';

            $this->db->where($searchQry);
        }
        
        //Total Returned Rows Count
        $this->db->from('REPORTS');
        //$this->db->where("RESTYPE","0");
        $count = $this->db->count_all_results();
 //Set start record number
        $start = $limit * $page - $limit;
        //Total Pages
        if ($count > 0 && $limit > 0) {
            $total_pages = ceil($count / $limit);
        } else {
            $total_pages = 0;
            $start=0;
        }

        //Check requested page
        if ($page > $total_pages)
            $page = $total_pages;

       

        //Format data for jqGrid output
        $data['page'] = "" . $page . "";
        $data['total'] = "" . $total_pages . "";
        $data['records'] = "" . $count . "";

        //Fetch data
        $this->db->select("ID, REPORTNAME");
//        $this->db->where("RESTYPE","0");
        $this->db->order_by($sidx, $sord);
        $this->db->limit($limit, $start);
        if($search=='true') $this->db->where($searchQry);
        $rows = $this->db->get("REPORTS")->result_array();

        //Format data
        $i = 0;
        libxml_use_internal_errors(true);
        foreach ($rows as $row) {
            $data['rows'][$i]['id'] = $i + 1;
            $id = $i + 1;
            $sxe = simplexml_load_string($row['CONTENT']);
            if ($sxe === false) 
                {
            $data['rows'][$i]['cell'] = array($row['ID'], $row['REPORTNAME'],  $row['CONTENT']);
            }
            else
            {
                $data['rows'][$i]['cell'] = array($row['ID'], $row['REPORTNAME'], $row['CONTENT']);
            }

            $i++;
        }
        return @json_encode($data);
    }

   
    function reportInfo($id) {
        $this->db->where('ID', $id);
        return $this->db->get('REPORTS')->result();
    }

    public function getlines() {
        $sql = "SELECT ID,REPORTNAME FROM REPORTS";
        $query = $this->db->query($sql);
        return $query->result();
    }
    function updateReport($id, $name, $textarea) {
        $this->db->where('ID', $id);
        $update = array(
            'REPORTNAME' => $name,
            'CONTENT' => $textarea
        );
        return ($this->db->update('REPORTS', $update)) ? true : false;
    }

    function getmenu()
    {
        $this->db->select('CONTENT');
        $this->db->where('REPORTNAME', 'Webin.Menu');
        $q = $this->db->get('REPORTS')->result();
        
        return $q[0]->CONTENT;
    }
    
    function add($form_data){
        $this->db->insert('REPORTS', $form_data);
		if ($this->db->affected_rows() == '1')
		{
			return TRUE;
		}
        return FALSE;
     }
     
    function update($id, $form_data){
        $this->db->where('ID', $id);
        $this->db->update('REPORTS', $form_data);
        if ($this->db->affected_rows() == '1') {
            return TRUE;
        }
        return FALSE;
     }
     
    function delete($id) {
        return ($this->db->delete('REPORTS', array('ID' => $id))) ? true : false;
    }
    public function getreportsdata() {
        $data = $this->common->customquery('SELECT ID, REPORTNAME FROM REPORTS');
        return json_encode($data);
    }
    
    public function productstotallist($fromdate='',$todate='',$customer=''){
        $sql='SELECT PRODUCTS.ID,PRODUCTS.NAME AS NAME,TICKETLINES.PRICE FROM TICKETLINES JOIN PRODUCTS ON TICKETLINES.PRODUCT=PRODUCTS.ID LEFT JOIN TICKETS ON TICKETS.ID=TICKETLINES.TICKET LEFT JOIN RECEIPTS ON RECEIPTS.ID=TICKETS.ID WHERE RECEIPTS.DATENEW>="'.date("Y-m-d",strtotime($fromdate)).'" AND RECEIPTS.DATENEW<="'.date("Y-m-d",strtotime($todate)).'"';
        if($customer)
            $sql.=' AND TICKETS.CUSTOMER="'.$customer.'"';
        $sql.=' GROUP BY PRODUCTS.NAME,PRODUCTS.ID,TICKETLINES.PRICE';
        $data = $this->common->customquery($sql);
        return $data;
    }
    public function productcount($fromdate='',$todate='',$customer=''){
        $sql='SELECT PRODUCTS.ID,PRODUCTS.NAME,sum(UNITS) as CNT,CAST(RECEIPTS.DATENEW AS DATE) AS RECIEPTDATE'
                . ' FROM PRODUCTS LEFT JOIN TICKETLINES ON TICKETLINES.PRODUCT=PRODUCTS.ID'
                . ' LEFT JOIN TICKETS ON TICKETS.ID=TICKETLINES.TICKET'
                . ' LEFT JOIN RECEIPTS ON RECEIPTS.ID=TICKETS.ID'
                . ' WHERE CAST(RECEIPTS.DATENEW AS DATE)>="'.date("Y-m-d",strtotime($fromdate)).'"'
                . ' AND CAST(RECEIPTS.DATENEW AS DATE)<="'.date("Y-m-d",strtotime($todate)).'"';
        if($customer)
            $sql.=' AND TICKETS.CUSTOMER="'.$customer.'"';
        $sql.=' GROUP BY PRODUCTS.NAME,PRODUCTS.ID,CAST(RECEIPTS.DATENEW AS DATE) ORDER BY CAST(RECEIPTS.DATENEW AS DATE)';
        $data = $this->common->customquery($sql);
        return $data;
    }
    public function producttotalcount($fromdate='',$todate='',$customer=''){
        $sql='SELECT PRODUCTS.ID,PRODUCTS.NAME,SUM(UNITS) AS CNT,
                SUM(UNITS*TICKETLINES.PRICE) AS TOTALPRICE
                FROM PRODUCTS LEFT JOIN TICKETLINES ON TICKETLINES.PRODUCT=PRODUCTS.ID
                LEFT JOIN TICKETS ON TICKETS.ID=TICKETLINES.TICKET LEFT JOIN RECEIPTS ON RECEIPTS.ID=TICKETS.ID'
            . ' WHERE RECEIPTS.DATENEW>="'.date("Y-m-d",strtotime($fromdate)).'"'
            . ' AND RECEIPTS.DATENEW<="'.date("Y-m-d",strtotime($todate)).'"';
        if($customer)
            $sql.=' AND TICKETS.CUSTOMER="'.$customer.'"';
        $sql.=' GROUP BY PRODUCTS.ID, PRODUCTS.NAME ORDER BY CAST(RECEIPTS.DATENEW AS DATE)';
        $data = $this->common->customquery($sql);
        return $data;
        
        
    }
}