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

class Paidlog_model extends MY_Model
{
	
	protected $table_name = 'tblpaidlog';
	protected $id_name = 'paid_id';

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

	}
	function getPaidlogs($data) {
		
		if ($data['paid_type'] == 0){
			$sql = "SELECT SUM(amount) as total_amount FROM tblpaidlog";
		}else{
			$sql = "SELECT SUM(amount) as total_amount FROM tblpaidlog WHERE paid_type = ".$data['paid_type'];
		}
		$r = $this->db->query($sql)->row_array();
		
		$total_amount = (int)$r['total_amount'];
        $sql = "SELECT * FROM " . $this->table_name. " INNER JOIN tbluser ON tbluser.us_id = tblpaidlog.us_id ";
		if ($data['paid_type'] == 1){
			$sql.=" WHERE paid_type = 1";
		}
		if ($data['paid_type'] == 2){
			$sql.=" WHERE paid_type = 2";
		}
		/*
        if ($data['keyword']) {
            if ($data['search_field'] == "cellphone") {
                $sql.=" WHERE (cellphone LIKE '%" . $data['keyword'] . "%')";
            } 
        }else{
		*/
		if(strlen($data['daterange_start'])>0){
			if ($data['paid_type'] ==0){
				$sql.=" WHERE paid_time BETWEEN '".$data['daterange_start']."' AND '".$data['daterange_end']."'";
			}else{
				$sql.=" AND paid_time BETWEEN '".$data['daterange_start']."' AND '".$data['daterange_end']."'";
			}
		}
		//}

        $order = "";
		if ($data['sorting_order']){
			if ($data['sorting_order'] != "sorting") {
				if ($data['sorting_field'] != "money") {
					$sort = "DESC";
					if ($data['sorting_order'] == "sorting_asc")
						$sort = "ASC";
					$order = "ORDER BY " . $data['sorting_field'] . " " . $sort;
				}
			}
		}

		//if ($data['start']){
		
			$sql=$sql . " " . $order . " LIMIT " . $data['start'] . ", " . $data['perpage'] . " ";
		//}
	
        //return $this->db->query($sql)->result_array();
		$re = $this->db->query($sql)->result_array();
		
		$result['paid_logs'] = $re;
		
		$result['total_amount'] = $total_amount;
		return $result;
    }


    function countPaidlog($data) {
        $sql = "SELECT COUNT(".$this->id_name.") as total FROM " . $this->table_name." INNER JOIN tbluser ON tbluser.us_id = tblpaidlog.us_id";
        /*if ($data['keyword']) {
            if ($data['search_field'] == "cellphone") {
                $sql.=" WHERE (cellphone LIKE '%" . $data['keyword'] . "%')";
            } 
        }else{
		*/
		if ($data['paid_type'] == 1){
			$sql.=" WHERE paid_type = 1";
		}
		if ($data['paid_type'] == 2){
			$sql.=" WHERE paid_type = 2";
		}
		if(strlen($data['daterange_start'])>0){
			if ($data['paid_type'] ==0){
				$sql.=" WHERE paid_time BETWEEN '".$data['daterange_start']."' AND '".$data['daterange_end']."'";
			}else{
				$sql.=" AND paid_time BETWEEN '".$data['daterange_start']."' AND '".$data['daterange_end']."'";
			}
		}
		//}
		
        
        $result = $this->db->query($sql)->row_array();
        return $result['total']; 
    }
    function getPaidlog($id)
    {
        $sql="SELECT * FROM ".$this->table_name." WHERE ".$this->id_name."=".$id." LIMIT 1";
        $result = $this->db->query($sql)->row_array();
        return $result;
    }
    function getAllPaidlogs($from)
    {
        // get all paidlogs from the last years until now
        $from = date("Y-m-d 00:00:00",$from); 
        $sql="SELECT * FROM ".$this->table_name." INNER JOIN tbluser ON tbluser.us_id = ".$this->table_name.".us_id WHERE ".$this->table_name.".paid_time > '".$from."'";
        $result = $this->db->query($sql)->result_array();
        foreach ($result as $index=>$paidlog)
        {
            $result[$index]['time']=  strtotime($paidlog['paid_time']);
        }
         
        return $result;
    }
}