<?php
class Tables extends CI_Controller
{

    public function __construct()
    {
        parent::__construct();
        $this->load->library('Datatables');
        $this->load->model('tokenmodel');
        $this->load->model('usersmodel');
    }

    function enforce()
    {
        $session = $this->session->userdata('username');
        $token = $this->session->userdata('token');
        if (empty($token)) {
            show_404();
            return false;
        } else {
            $tokens = $this->tokenmodel->get(array('token' => $token, 'isvalid' => 1, 'username' => $this->session->userdata('username')));
            if ($tokens->num_rows() > 0) {
                $mtoken = $tokens->row();
                $now = strtotime(date('Y-m-d H:i:s'));
                $lastactivity = strtotime($mtoken->lastactive);
                $interval =  round(abs($now - $lastactivity) / 60, 0);

                if ($interval < TIMEOUT) {
                    return true;
                }
            } else {
                show_404();
                return false;
            }
        }
    }

    function leta_audittrail()
    {
        $this->enforce();
        $from = strtotime($this->input->get('from'));
        $to = strtotime($this->input->get('to'));
        $sfrom = date('Y-m-d', $from);
        $sto = date('Y-m-d', $to);
        $this->datatables->select("case when users.UserCode is null then audit.UserCode else concat(firstname,' ',secondname) end,activity,activitydate,ipaddress,queryStatus")
            ->from('audit')
            ->join('users', 'audit.UserCode = users.UserCode and users.recordstate=0', 'left outer')
            ->where('activitydate >=', $sfrom . ' 00:00:00')
            ->where('activitydate <=', $sto  . ' 23:59:59');
        echo $this->datatables->generate();
    }

    function usagebydate()
    {
        $this->enforce();
        $sfrom = date('Y-m-d', strtotime("-7 days"));
        $sto = date('Y-m-d');

        $this->datatables->select("name,date,voucher_value")
            ->from('usage_summary_byday')
            ->where('date >=', $sfrom)
            ->where('date <=', $sto);
        echo $this->datatables->generate();
    }

    function usagebyalldate()
    { 
        $this->enforce();
        $sfrom = date('Y-m-d', strtotime($this->input->get('from')));
        $sto = date('Y-m-d',strtotime($this->input->get('to')));

        $this->datatables->select("name,date,voucher_value")
            ->from('usage_summary_byday')
            ->where('date >=', $sfrom)
            ->where('date <=', $sto);
        echo $this->datatables->generate();
    }


    function customersummary()
    {
        $this->enforce();
        $user =  $this->session->userdata('username');
        $this->datatables->select("name,status,status_date")
            ->from('customers')
            ->where('record_state', 0);
        echo $this->datatables->generate();
    }

    function customerfloat()
    {
        $this->enforce();
        $user =  $this->session->userdata('username');
        $this->datatables->select("name,topupfloat,floatusage,balance,id")
            ->from('client_float_balance');
        echo $this->datatables->generate();
    }

    function vendorfloat()
    {
        $this->enforce();
        $user =  $this->session->userdata('username');
        $this->datatables->select("name,topupfloat,floatusage,balance,partnerid")
            ->from('partner_float_balance');
        echo $this->datatables->generate();
    } 

    function fetchusers()
    {
        $this->enforce();
        $this->datatables->select("tid,UserCode, firstname,email, phone,usertype,creationdate,case when UserEnabled=1 then 'Active' else 'Disabled' end")
            ->from('users')
            ->where('usertype <>', 'Supa')
            ->where('usertype <>', 'retailer')
            ->where('usertype <>', 'distributor')
            ->where('recordstate', 0);
        echo $this->datatables->generate();
    }

    function leta_messo()
    {
        $this->enforce();
        $from = strtotime($this->input->get('from'));
        $to = strtotime($this->input->get('to'));
        $sfrom = date('Y-m-d', $from);
        $sto = date('Y-m-d', $to);
        $this->datatables->select("name,destination,type,message,concat(users.firstname,' ',users.secondname),outbox.addedon,concat(RequestStatus,' ',deliverydescription)")
            ->from('outbox')
            ->join('users', 'outbox.addedby = users.UserCode and users.recordstate=0', 'left outer')
            ->where('outbox.addedon >=', $sfrom . ' 00:00:00')
            ->where('outbox.addedon <=', $sto  . ' 23:59:59');
            //->where('type', 'sms');
        echo $this->datatables->generate();
    }
 

    function crossindustry()
    {
        $this->enforce();
        $user =  $this->session->userdata('username');
        $this->datatables->select("cross_industry_merchants.name,categories,type,
            case when country.Name is null then cross_industry_merchants.country else country.Name end,
            case when vw_crossindustry_units.units is null then 0 else vw_crossindustry_units.units end,
            case when no_of_clients is null then 0 else no_of_clients end,images,cross_industry_merchants.record_state,cross_industry_merchants.dmo_id")
            ->from('cross_industry_merchants')
            ->join('country', 'cross_industry_merchants.country=substring(country.Code,1,2)', 'left')
            ->join('vw_crossindustry_units', 'cross_industry_merchants.dmo_id=vw_crossindustry_units.dmo_id', 'left')
            ->join('vw_redemption_mapping_stats', 'vw_redemption_mapping_stats.dmo_id=cross_industry_merchants.dmo_id', 'left')
            ->order_by('cross_industry_merchants.name', 'asc');
        echo $this->datatables->generate();
    }

    function crossindustrypartners()
    {
        $this->enforce();
        $user =  $this->session->userdata('username');
        $this->datatables->select("cross_industry_merchants.name,customers.name as clt,type,cross_industry_merchants.dmo_id,customers.id")
            ->from('redemption_mappings')
            ->join('cross_industry_merchants', 'cross_industry_merchants.dmo_id=redemption_mappings.dmo_id', 'left')
            ->join('customers', 'redemption_mappings.client_id=customers.id', 'left')
            ->where('redemption_mappings.record_state', 0)
            ->order_by('cross_industry_merchants.name', 'asc');
        echo $this->datatables->generate();
    }
 
    function leta_float_history()
    {
        $this->enforce(); 
        $from = strtotime($this->input->get('from'));
        $to = strtotime($this->input->get('to'));
        $sfrom = date('Y-m-d', $from);
        $sto = date('Y-m-d', $to); 

        $cust = $this->input->get('id');
        $type = $this->input->get('type');
        
        $this->datatables->select("topped_on,client_float, client_float.addedon,concat(users.firstname,' ',users.secondname)")
            ->from('client_float')
            ->join('users','client_float.addedby = users.usercode','left outer')
            ->where("client_id",$cust )
            ->where("type",  $type)
            ->where("topped_on >=", $sfrom)
            ->where("topped_on <=", $sto );
        echo $this->datatables->generate();
    }

    function vouchers(){
        
        $this->enforce(); 
        $from = strtotime($this->input->get('from'));
        $to = strtotime($this->input->get('to'));

        $sfrom = date('Y-m-d', $from) ." 00:00:00";
        $sto = date('Y-m-d', $to) ." 23:59:59"; 
        
        $this->datatables->select("case when voucher_type like 'Discount' then 'Discount' else 'Redemption' end,
        cross_industry_merchants.name,voucher_value,customers.name as cust,ref_id,CUST_PHONE,expiry,
        case when verified=1 then '' else 'Pending' end, vouchers.addedon")
            ->from('vouchers')
            ->join('cross_industry_merchants','cross_industry_merchants.dmo_id = vouchers.dmo_id and cross_industry_merchants.record_state=0 ','left outer')
            ->join('customers','vouchers.client_id = customers.client_extid and customers.record_state=0 ','left outer')
            ->where("vouchers.addedon >=", $sfrom)
            ->where("vouchers.addedon <=", $sto );
        echo $this->datatables->generate();
    }
}
