<?php

class Transaction_model extends CI_Model {
    private $table;

    public function __construct() {
        parent::__construct();
        $this->load->database();
        $this->table = "transactions";
    }

    public function store(array $data) {
        $data = array_merge(array (
            "aclreference" => "",
            "acltrxid" => "",
            "aclstatus" => "N",
            "aclbdtamount" => 0,
            "aclgatewayfee" => 0,
            "aclmethod" => "",
            "acldescription" => "",
            "aclpaid" => "0000-00-00 00:00:00",
            "aclcreated" => date("Y-m-d H:i:s"),
            "aclmethodnote" => "",
            "paymenttypeid" => "",
            "transid" => "",
            "userid" => "",
            "usertype" => "",
            "transactiontype" => "",
            "invoiceids" => "",
            "debitnoteids" => "",
            "description" => "",
            "sellingcurrencyamount" => "",
            "accountingcurrencyamount" => "",
            "redirecturl" => "",
            "checksum" => "",
            "name" => "",
            "emailAddr" => "",
            "telNo" => "",
            "resellerCurrency" => ""
        ), $data);

        if(!empty($data)) {
            return $this->db->insert($this->table, $data);
        }

        return false;
    }

    public function storePayment($transaction, $reference, array $data) {
        $data = array_merge(array(
            "acltrxid" => "",
            "aclstatus" => "Y",
            "acldescription" => "",
            "aclpaid" => date("Y-m-d H:i:s"),
            "aclmethodnote" => "",
        ), $data);
        $this->db->where('aclreference', $reference);
        $this->db->where('transid', $transaction);
        if(!empty($data)) {
            return $this->db->update($this->table, $data);
        }
        return false;
    }

    public function payDynamicPayment($transaction, $reference, array $data) {
        $data = array_merge(array(
            "aclstatus" => "Y",
            "aclpaid" => date("Y-m-d H:i:s"),
            "aclmanual" => '1',
        ), $data);
        $this->db->where('aclreference', $reference);
        $this->db->where('transid', $transaction);
        if(!empty($data)) {
            return $this->db->update($this->table, $data);
        }
        return false;
    }

    public function cancelTransaction($transaction, $reference) {
        $data = array(
            "aclstatus" => "C",
        );
        $this->db->where('aclreference', $reference);
        $this->db->where('transid', $transaction);
        if(!empty($data)) {
            return $this->db->update($this->table, $data);
        }
        return false;
    }

    public function rollBackTransaction($transaction, $reference) {
        $data = array(
            "aclstatus" => "N",
            "aclmanual" => "0",
            "acltrxid" => "",
        );
        $this->db->where('aclreference', $reference);
        $this->db->where('transid', $transaction);
        if(!empty($data)) {
            return $this->db->update($this->table, $data);
        }
        return false;
    }

    public function allowExpireTransaction($transaction, $reference) {
        $data = array(
            "acldisableexpire" => "1",
        );
        $this->db->where('aclreference', $reference);
        $this->db->where('transid', $transaction);
        if(!empty($data)) {
            return $this->db->update($this->table, $data);
        }
        return false;
    }

    public function removeTransaction($transaction, $reference) {
        if($this->existsByTransaction($transaction)){
            return $this->db->delete($this->table, array('transid' => $transaction, 'aclreference' => $reference));
        }
        return false;
    }

    public function getByReference($reference) {
        if(!empty($reference)) {
            $this->db->where('aclreference', $reference);
            $val = $this->db->get($this->table, 1, 0);
            return $val->num_rows()>0?$val->row(0):false;
        }
        return false;
    }

    public function getByTransaction($transaction) {
        if(!empty($transaction)) {
            $this->db->where('transid', $transaction);
            $val = $this->db->get($this->table, 1, 0);
            return $val->num_rows()>0?$val->row(0):false;
        }
        return false;
    }

    public function existsByTransaction($transaction){
        if(!empty($transaction)){
            $val = $this->db->get_where($this->table, array('transid' => $transaction), 1, 0);
            return ($val->num_rows()>0);
        }
        return false;
    }

    public function existsByTransactionUnpaid($transaction){
        if(!empty($transaction)){
            $val = $this->db->get_where($this->table, array('transid' => $transaction, 'aclstatus' => 'N', 'aclrequested' => '0'), 1, 0);
            return ($val->num_rows()>0);
        }
        return false;
    }

    public function existsByTransactionUnpaidRequested($transaction){
        if(!empty($transaction)){
            $val = $this->db->get_where($this->table, array('transid' => $transaction, 'aclstatus' => 'N', 'aclrequested' => '1'), 1, 0);
            return ($val->num_rows()>0);
        }
        return false;
    }


    public function generateReference() {
        $number = mt_rand(100000, 999999);
        if($this->db->get_where($this->table, array('aclreference' => $number), 1, 0)->num_rows()>0){
           return $this->generateReference();
        }
        return $number;
    }

    public function existsByTrxId($trxid){
        if(!empty($trxid)){
            $val = $this->db->get_where($this->table, array('acltrxid' => $trxid), 1, 0);
            return ($val->num_rows()>0);
        }
        return false;
    }

    public function  totalPaid() {
        $table = $this->db->dbprefix($this->table);
        $sql = "SELECT COUNT(*) AS total, SUM( cast(aclbdtamount AS DECIMAL(10,2)) + cast(aclgatewayfee AS DECIMAL(10,2)) ) AS amount FROM $table WHERE aclstatus = 'Y'";
        $val = $this->db->query($sql);
        return $val->num_rows()>0?$val->row(0):false;
    }

    public function  totalUnpaid() {
        $table = $this->db->dbprefix($this->table);
        $sql = "SELECT COUNT(*) AS total, SUM( cast(aclbdtamount AS DECIMAL(10,2)) + cast(aclgatewayfee AS DECIMAL(10,2)) ) AS amount FROM $table WHERE aclstatus = 'N'";
        $val = $this->db->query($sql);
        return $val->num_rows()>0?$val->row(0):false;
    }

    public function  totalCancelled() {
        $table = $this->db->dbprefix($this->table);
        $sql = "SELECT COUNT(*) AS total, SUM( cast(aclbdtamount AS DECIMAL(10,2)) + cast(aclgatewayfee AS DECIMAL(10,2)) ) AS amount FROM $table WHERE aclstatus = 'C'";
        $val = $this->db->query($sql);
        return $val->num_rows()>0?$val->row(0):false;
    }

    public function  totalDaily() {
        $table = $this->db->dbprefix($this->table);
        $sql = "SELECT COUNT(*) AS total, SUM( cast(aclbdtamount AS DECIMAL(10,2)) + cast(aclgatewayfee AS DECIMAL(10,2)) ) AS amount FROM $table WHERE aclstatus = 'Y' AND aclpaid BETWEEN '".date('Y-m-d')." 00:00:00' AND '".date('Y-m-d')." 23:59:59'";
        $val = $this->db->query($sql);
        return $val->num_rows()>0?$val->row(0):false;
    }

    public function  totalDateStatusWiseAmount($startDate, $endDate, $status='') {
        $table = $this->db->dbprefix($this->table);
        $sql = "SELECT SUM( cast(aclbdtamount AS DECIMAL(10,2)) + cast(aclgatewayfee AS DECIMAL(10,2)) ) AS amount FROM $table WHERE ".(!empty($status)?'aclstatus = ? AND ':'')."aclcreated BETWEEN ? AND ?";
        $data = array();
        if(!empty($status)) array_push($data, $status);
        array_push($data, $startDate, $endDate);
        $val = $this->db->query($sql, $data);
        return $val->num_rows()>0?$val->row(0):false;
    }

    public function  totalAmount() {
        $table = $this->db->dbprefix($this->table);
        $sql = "SELECT SUM( cast(aclbdtamount AS DECIMAL(10,2)) + cast(aclgatewayfee AS DECIMAL(10,2)) ) AS amount FROM $table";
        $val = $this->db->query($sql);
        return $val->num_rows()>0?$val->row(0):false;
    }

    public function generateTodaySummary(array &$label, array &$data) {
        $date = date("Y-m-d");
        $sets = array();
        $sets['12am'] = array( $date." 00:00:00", $date." 00:59:59");
        $i = 1;
        for(;$i<=11;$i++) {
            $n = sprintf("%02d", $i);
            $sets[$i.'am'] = array( $date." ".$n.":00:00", $date." ".$n.":59:59" );
        }
        $sets['12pm'] = array( $date." 12:00:00", $date." 12:59:59");
        $i = 1;
        $j = 13;
        for(;$i<=11;$i++,$j++) {
            $p = sprintf("%02d", $j);
            $sets[$i.'pm'] = array( $date." ".$p.":00:00", $date." ".$p.":59:59" );
        }

        $label = array_keys($sets);
        $data = array();
        $data[0] = array();
        $data[1] = array();
        $data[2] = array();

        foreach ($sets as $set) {
            if($a = $this->totalDateStatusWiseAmount($set[0], $set[1])){
                array_push($data[2], !empty($a->amount) && $a->amount>0 ? $a->amount : '0.00');
            } else {
                array_push($data[2], '0.00');
            }
            if($b = $this->totalDateStatusWiseAmount($set[0], $set[1], 'Y')){
                array_push($data[1], !empty($b->amount) && $b->amount>0 ? $b->amount : '0.00');
            } else {
                array_push($data[1], '0.00');
            }
            if($c = $this->totalDateStatusWiseAmount($set[0], $set[1], 'N')){
                array_push($data[0], !empty($c->amount) && $c->amount>0 ? $c->amount : '0.00');
            } else {
                array_push($data[0], '0.00');
            }
        }

        return !empty($label) && !empty($data) && !empty($data[0]) &&!empty($data[1]) &&!empty($data[2]);
    }

    public function generateMonthSummary(array &$label, array &$data) {
        $today = date("d");
        $etc = date("Y-m-");
        $start = 1;
        $limit = $today>=7 ? ( $today >= 14 ? ( $today >= 21 ? $today : 21) : 14 ) : 7;
        $sets = array();
        for(;$start<=$limit;$start++) {
            $n = sprintf("%02d", $start);
            $sets[aclOrdinal($start)] = array( $etc.$n." 00:00:00", $etc.$n." 23:59:59");
        }

        $label = array_keys($sets);
        $data = array();
        $data[0] = array();
        $data[1] = array();
        $data[2] = array();

        foreach ($sets as $set) {
            if($a = $this->totalDateStatusWiseAmount($set[0], $set[1])){
                array_push($data[2], !empty($a->amount) && $a->amount>0 ? $a->amount : '0.00');
            } else {
                array_push($data[2], '0.00');
            }
            if($b = $this->totalDateStatusWiseAmount($set[0], $set[1], 'Y')){
                array_push($data[1], !empty($b->amount) && $b->amount>0 ? $b->amount : '0.00');
            } else {
                array_push($data[1], '0.00');
            }
            if($c = $this->totalDateStatusWiseAmount($set[0], $set[1], 'N')){
                array_push($data[0], !empty($c->amount) && $c->amount>0 ? $c->amount : '0.00');
            } else {
                array_push($data[0], '0.00');
            }
        }
        return !empty($label) && !empty($data) && !empty($data[0]) &&!empty($data[1]) &&!empty($data[2]);
    }

    public function generateYearSummary(array &$label, array &$data) {
        $thismonth = date("n");
        $start = 1;
        $limit = $thismonth <= 6 ? 6 : $thismonth;
        $sets = array();
        for(;$start<=$limit;$start++) {
            $n = sprintf("%02d", $start);
            $firstdate = date('Y-').$n.'-01';
            $lastdate = date("Y-m-t", strtotime($firstdate));
            $name = date("F", strtotime($firstdate));
            $sets[$name] = array( $firstdate." 00:00:00", $lastdate." 23:59:59");
        }

        $label = array_keys($sets);
        $data = array();
        $data[0] = array();
        $data[1] = array();
        $data[2] = array();

        foreach ($sets as $set) {
            if($a = $this->totalDateStatusWiseAmount($set[0], $set[1])){
                array_push($data[2], !empty($a->amount) && $a->amount>0 ? $a->amount : '0.00');
            } else {
                array_push($data[2], '0.00');
            }
            if($b = $this->totalDateStatusWiseAmount($set[0], $set[1], 'Y')){
                array_push($data[1], !empty($b->amount) && $b->amount>0 ? $b->amount : '0.00');
            } else {
                array_push($data[1], '0.00');
            }
            if($c = $this->totalDateStatusWiseAmount($set[0], $set[1], 'N')){
                array_push($data[0], !empty($c->amount) && $c->amount>0 ? $c->amount : '0.00');
            } else {
                array_push($data[0], '0.00');
            }
        }
        return !empty($label) && !empty($data) && !empty($data[0]) &&!empty($data[1]) &&!empty($data[2]);
    }

}