<?php

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

class Closecash_model extends CI_Model {

    public function __construct() {
        parent::__construct();
        $this->load->library('Formatslist');
        $this->load->model('settings/companyinfo_model');
    }

// GET MATERIALREQUEST LISTS FOR MANAGE
    function getclosecash() {
        $this->db->select("CLOSEDCASH.MONEY AS ID,  CLOSEDCASH.HOST, CLOSEDCASH.HOSTSEQUENCE, CLOSEDCASH.DATESTART, CLOSEDCASH.DATEEND, SUM(PAYMENTS.TOTAL) AS TOTAL");
        $this->db->join('RECEIPTS', 'RECEIPTS.MONEY = CLOSEDCASH.MONEY', 'LEFT');
         $this->db->join('PAYMENTS', 'PAYMENTS.RECEIPT = RECEIPTS.ID','LEFT');
         $this->db->group_by("CLOSEDCASH.MONEY");
        $rows = $this->db->get("CLOSEDCASH")->result();
//echo $this->db->last_query();exit;
        foreach ($rows as $value) {
            $totalpoint = $value->TOTAL;
            //echo $totalpoint[1];
           // echo "<br>";
           // print_r($totalpoint);
            if(strstr($totalpoint, '.'))
           {
             $TOTAL = $value->TOTAL;
           }
           else
           {
             $TOTAL = $value->TOTAL.'.00';
           }
           $data_row[]  = array(
            'ID' => $value->ID, 
            'HOST' => $value->HOST, 
            'HOSTSEQUENCE' => $value->HOSTSEQUENCE, 
            'DATESTART' => $value->DATESTART, 
            'DATEEND' => $value->DATEEND, 
            'TOTAL' =>   $TOTAL , 
           );
        }
        
        return $data_row;
    }

// RECEIPT DETAILS
    public function getreceiptlinesbyparam($id) {

        $table_name = "TICKETS, RECEIPTS, PAYMENTS";

        $this->db->select('TICKETS.TICKETID, TICKETS.TICKETTYPE, CUSTOMERS.NAME AS CUSTOMER, RECEIPTS.DATENEW, RECEIPTS.ATTRIBUTES, PEOPLE.NAME AS CASHIER,PAYMENTS.TOTAL')->from($table_name);
        $this->db->where('TICKETS.TICKETID', $id);
        //TICKETS.TICKETTYPE = 0 AND
        $this->db->where('TICKETS.ID = RECEIPTS.ID AND RECEIPTS.ID = PAYMENTS.RECEIPT ');
        $this->db->join('CUSTOMERS', 'TICKETS.CUSTOMER = CUSTOMERS.ID', 'LEFT');
        $this->db->join('PEOPLE', 'TICKETS.PERSON = PEOPLE.ID', 'LEFT');
        $this->db->order_by("TICKETS.TICKETID");
        $this->db->group_by("PAYMENTS.RECEIPT");
        return $this->db->get()->result();
    }

    public function getclosedcashdetails($id) {

        $table_name = "CLOSEDCASH";

        $this->db->select('CLOSEDCASH.HOST, CLOSEDCASH.HOSTSEQUENCE, CLOSEDCASH.DATESTART, CLOSEDCASH.DATEEND')->from($table_name);
        $this->db->where('CLOSEDCASH.MONEY', $id);

        $this->db->join('RECEIPTS', 'CLOSEDCASH.MONEY = RECEIPTS.MONEY', 'LEFT');
        $this->db->join('PAYMENTS', 'PAYMENTS.RECEIPT = RECEIPTS.ID', 'LEFT');
        return $this->db->get()->result();
    }

    public function getclosedcashamount($id, $cash, $cashin, $cashout, $cashcreditpaid, $debt, $debtpaid, $cheque, $card, $free, $refund, $online) {
//        $count = count($id);
        $k = 0;
        $table_name = "CLOSEDCASH";
        $this->db->select('SUM(PAYMENTS.TOTAL) AS CASH')->from($table_name);
        if ($cash) {
            $this->db->where('PAYMENTS.PAYMENT', $cash);
        }
        if ($cashin) {
            $this->db->where('PAYMENTS.PAYMENT', $cashin);
        } if ($cashout) {
            $this->db->where('PAYMENTS.PAYMENT', $cashout);
        } if ($cashcreditpaid) {
            $this->db->where('PAYMENTS.PAYMENT', $cashcreditpaid);
        } if ($debt) {
            $this->db->where('PAYMENTS.PAYMENT', $debt);
        } if ($debtpaid) {
            $this->db->where('PAYMENTS.PAYMENT', $debtpaid);
        }
        if ($card) {
            $this->db->where('PAYMENTS.PAYMENT', $card);
        }
        if ($cheque) {
            $this->db->where('PAYMENTS.PAYMENT', $cheque);
        }
        if ($free) {
            $this->db->where('PAYMENTS.PAYMENT', $free);
        }
        if ($refund) {
            $this->db->where('PAYMENTS.PAYMENT', $refund);
        }
        if ($online) {
            $this->db->where('PAYMENTS.PAYMENT', $online);
        }
        $this->db->join('RECEIPTS', 'CLOSEDCASH.MONEY = RECEIPTS.MONEY', 'LEFT');
        $this->db->join('PAYMENTS', 'PAYMENTS.RECEIPT = RECEIPTS.ID', 'LEFT');
        $array = $id;
        $pid = (int) $id;
        $this->db->where('CLOSEDCASH.MONEY', $id);
        $b[$k] = $this->db->get()->result();
        $k = $k + 1;
//        $bcount = count($b);
        $new = array();
        $result = 0.0;
        foreach ($b as $key => $val) {
            sort($val);
            $result = $result+$val[0]->CASH;
        }
        return $result;
    }

    public function getreceiptscount($id) {
// $count = count($id);
        $k = 0;
        $table_name = "TICKETS";

        $this->db->select('COUNT(*) AS RECEIPTSCOUNT')->from($table_name);
    
        $this->db->join('RECEIPTS', 'TICKETS.ID = RECEIPTS.ID', 'LEFT');
           $array = $id;
            $pid = (int) $array;
               $this->db->where('RECEIPTS.MONEY', $id);
            $b[$k] = $this->db->get()->result();
            $k = $k + 1;
        $bcount = count($b);
        $new = array();
        $result = 0.0;
        foreach ($b as $key => $val) {
            sort($val);
       $result = $result+$val[0]->RECEIPTSCOUNT;

//            $result = $result+$val;
        }
//        var_dump($result);

        return $result;
    }

    public function getpaymentscount($id) {
// $count = count($id);
        $k = 0;

        $table_name = "PAYMENTS";

        $this->db->select('COUNT(*) AS PAYMENTS')->from($table_name);
        

        $this->db->join('RECEIPTS', 'PAYMENTS.RECEIPT = RECEIPTS.ID', 'LEFT');
         $array = $id;
            $pid = (int) $array;
               $this->db->where('RECEIPTS.MONEY', $id);
            $b[$k] = $this->db->get()->result();
            $k = $k + 1;
       
        $bcount = count($b);
        $new = array();
        $result = 0.0;
        foreach ($b as $key => $val) {
            sort($val);
       $result = $result+$val[0]->PAYMENTS;

//            $result = $result+$val;
        }
//        var_dump($result);

        return $result;
    }

    public function gettotal($id) {

        $table_name = "PAYMENTS";

        $this->db->select('SUM(PAYMENTS.TOTAL) AS TOTAL')->from($table_name);
        $this->db->where('RECEIPTS.MONEY', $id);

        $this->db->join('RECEIPTS', 'PAYMENTS.RECEIPT = RECEIPTS.ID', 'LEFT');

        return $this->db->get()->result();
    }

    public function getchequedetails($id) {

        $table_name = "CLOSEDCASH";

        $this->db->select('TICKETS.TICKETID, PAYMENTS.TOTAL, PAYMENTS.TRANSID')->from($table_name);


        $this->db->join('RECEIPTS', 'CLOSEDCASH.MONEY = RECEIPTS.MONEY', 'LEFT');
        $this->db->join('TICKETS', 'RECEIPTS.ID = TICKETS.ID', 'LEFT');
        $this->db->join('PAYMENTS', 'PAYMENTS.RECEIPT = RECEIPTS.ID', 'LEFT');
        $where = 'CLOSEDCASH.MONEY = "' . $id . '" AND PAYMENTS.PAYMENT = "cheque"';
        $this->db->where($where);

        return $this->db->get()->result();
    }

    public function gettaxdetails($id) {

        $table_name = "TAXLINES";

        $this->db->select('SUM(TAXLINES.AMOUNT) AS TAXTOTAL, TAXES.NAME')->from($table_name);

        $this->db->join('TAXES', 'TAXES.ID = TAXLINES.TAXID', 'LEFT');
        $this->db->join('RECEIPTS', 'TAXLINES.RECEIPT = RECEIPTS.ID', 'LEFT');
        $where = 'RECEIPTS.MONEY = "' . $id . '" AND TAXES.ID = TAXLINES.TAXID';
        $this->db->where($where);
        return $this->db->get()->result();
    }

    public function getdebtsaledetails($id) {

        $table_name = "PAYMENTS";

        $this->db->select('PAYMENTS.TOTAL, TICKETS.TICKETID, CUSTOMERS.NAME')->from($table_name);


        $this->db->join('RECEIPTS', 'PAYMENTS.RECEIPT = RECEIPTS.ID', 'LEFT');
        $this->db->join('TICKETS', 'RECEIPTS.ID = TICKETS.ID', 'LEFT');
        $this->db->join('CUSTOMERS', 'TICKETS.CUSTOMER = CUSTOMERS.ID', 'LEFT');
//          $this->db->join('CLOSEDCASH', 'CLOSEDCASH.MONEY = RECEIPTS.MONEY', 'LEFT');
        $where = 'RECEIPTS.MONEY = "' . $id . '" AND PAYMENTS.PAYMENT = "debt"';
        $this->db->where($where);

        return $this->db->get()->result();
    }

    public function getdebtpaiddetails($id) {

        $table_name = "PAYMENTS";

        $this->db->select('PAYMENTS.TOTAL, CUSTOMERS.NAME')->from($table_name);


        $this->db->join('RECEIPTS', 'PAYMENTS.RECEIPT = RECEIPTS.ID', 'LEFT');
        $this->db->join('TICKETS', 'RECEIPTS.ID = TICKETS.ID', 'LEFT');
        $this->db->join('CUSTOMERS', 'TICKETS.CUSTOMER = CUSTOMERS.ID', 'LEFT');
//          $this->db->join('CLOSEDCASH', 'CLOSEDCASH.MONEY = RECEIPTS.MONEY', 'LEFT');
        $where = 'RECEIPTS.MONEY = "' . $id . '" AND PAYMENTS.PAYMENT = "debtpaid"';
        $this->db->where($where);

        return $this->db->get()->result();
    }

    public function getsalereport($id) {

        $table_name = "TICKETLINES";

        $this->db->select('CATEGORIES.NAME, PRODUCTS.NAME AS PNAME, SUM(TICKETLINES.UNITS) AS UNIT, PRODUCTS.PRICESELL, PRODUCTS.PRICEBUY')->from($table_name);
//        $a = $this->db->select('DISTINCT(TICKETLINES.PRODUCT) AS P')->from($table_name);



        $this->db->join('PRODUCTS', 'TICKETLINES.PRODUCT = PRODUCTS.ID', 'LEFT');
        $this->db->join('CATEGORIES', 'PRODUCTS.CATEGORY = CATEGORIES.ID', 'LEFT');
        $this->db->join('RECEIPTS', 'TICKETLINES.TICKET = RECEIPTS.ID', 'LEFT');
        $this->db->join('TICKETS', 'RECEIPTS.ID = TICKETS.ID', 'LEFT');
//        $i=0;
//          foreach ($a as $row) {
//           $b=$row;
//            $i++;
//        }
        $where = 'RECEIPTS.MONEY = "' . $id . '" AND PRODUCTS.CATEGORY = CATEGORIES.ID AND TICKETLINES.PRODUCT = PRODUCTS.ID';
        $this->db->where($where);

        return $this->db->get()->result();
    }

    public function getsalereturnreport($id) {

        $table_name = "TICKETLINES";

        $this->db->select('TICKETS.TICKETID, PRODUCTS.NAME, TICKETLINES.UNITS, PAYMENTS.TOTAL')->from($table_name);

        $this->db->join('PRODUCTS', 'TICKETLINES.PRODUCT = PRODUCTS.ID', 'LEFT');

        $this->db->join('RECEIPTS', 'TICKETLINES.TICKET = RECEIPTS.ID', 'LEFT');
        $this->db->join('TICKETS', 'RECEIPTS.ID = TICKETS.ID', 'LEFT');
        $this->db->join('PAYMENTS', 'RECEIPTS.ID = PAYMENTS.RECEIPT', 'LEFT');

        $where = 'RECEIPTS.MONEY = "' . $id . '" AND TICKETS.TICKETTYPE = "1"';
        $this->db->where($where);

        return $this->db->get()->result();
    }

// RECEIPT LOGO
    public function getReceiptLogo() {

        $table_name = "RESOURCES";
        $this->db->select('CONTENT')->from($table_name);
        $this->db->where('NAME', "Printer.Ticket.Logo");
        return $this->db->get()->result();
    }

// RECEIPT PRODUCTS DETAILS eg: PRODUCT NAME, QUANTITY, PRICE etc.....
    public function getticketlinesbyparam($id) {
        $table_name = "TICKETS, TICKETLINES";
        $this->db->select('PRODUCTS.NAME, TICKETLINES.UNITS, TICKETLINES.PRICE AS PRICE,(TICKETLINES.UNITS * TICKETLINES.PRICE) AS UNITTOTAL')->from($table_name);
        $this->db->join('PEOPLE', 'TICKETS.PERSON = PEOPLE.ID');
        $this->db->join('PRODUCTS', 'TICKETLINES.PRODUCT = PRODUCTS.ID');
        $this->db->where('TICKETS.TICKETID', $id);
        $this->db->where('TICKETS.ID = TICKETLINES.TICKET');
        // $this->db->where('TICKETS.TICKETTYPE = 0');
        return $this->db->get()->result();
    }

// RECEIPT PAYMENTS DETAILS eg: TOTAL AMOUNT ,PAYMENTTYPE etc..........
    public function getpaymentlinesbyparam($id) {
        $table_name = "PAYMENTS, TICKETS";
        $this->db->select('PAYMENTS.PAYMENT, PAYMENTS.TOTAL AS PAYMENTTOTAL')->from($table_name);
        $this->db->where('TICKETS.TICKETID', $id);
        //TICKETS.TICKETTYPE = 0 AND
        $this->db->where('TICKETS.ID = PAYMENTS.RECEIPT');
        return $this->db->get()->result();
    }
    function getcashsequenceautocomplete($term) {

        $this->db->select(' HOSTSEQUENCE');
        $where = "HOSTSEQUENCE LIKE '". $term. "%'";
        $this->db->where($where);
        return $this->db->get("CLOSEDCASH")->result();
    }


    public function getcreditpaiddetails($id) {

        $table_name = "PAYMENTS";

        $this->db->select('PAYMENTS.TOTAL, SUPPLIERS.NAME')->from($table_name);


        $this->db->join('RECEIPTS', 'PAYMENTS.RECEIPT = RECEIPTS.ID', 'LEFT');
        $this->db->join('TICKETS', 'RECEIPTS.ID = TICKETS.ID', 'LEFT');
        $this->db->join('SUPPLIERS', 'TICKETS.SUPPLIER = SUPPLIERS.ID', 'LEFT');
//          $this->db->join('CLOSEDCASH', 'CLOSEDCASH.MONEY = RECEIPTS.MONEY', 'LEFT');
        $where = 'RECEIPTS.MONEY = "' . $id . '" AND PAYMENTS.PAYMENT = "creditpaid"';
        $this->db->where($where);

        return $this->db->get()->result();
    }
}
