<?php namespace App\Http\Controllers;

use App\Models\Repuserearn;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Validator, Input, Redirect ;


class RepuserearnController extends Controller {

	protected $layout = "layouts.main";
	protected $data = array();	
	public $module = 'repuserearn';
	static $per_page	= '25';

	public function __construct()
	{
		
		parent::__construct();
		$this->model = new Repuserearn();
		$this->info = $this->model->makeInfo( $this->module);
		$this->access = array();
	
		$this->data = array_merge(array(
			'pageTitle'	=> 	$this->info['title'],
			'pageNote'	=>  $this->info['note'],
			'pageModule'=> 'repuserearn',
			'pageUrl'			=>  url('/manage/repuserearn'),
			'return'	=> self::returnUrl()
			
		),$this->data);


	}

	public function index( Request $request )
	{
		// Make Sure users Logged 
		if(!\Auth::check()) 
			return redirect('user/login')->with('status', 'error')->with('message','You are not login');
		$this->grab( $request) ;
		if($this->access['is_view'] ==0) 
			return redirect('dashboard')->with('message', __('core.note_restric'))->with('status','error');				
		// Render into template
		return view( 'manage.'.$this->module.'.index',$this->data);
	}	
	function show( Request $request , $id ) 
	{
		/* Handle import , export and view */
		$task =$id ;
		switch( $task)
		{

		case 'data':
				$this->grab( $request) ;
				return view( 'manage.'.$this->module.'.result',$this->data);
				break;
				
		case 'search':
				return $this->getSearch('native');	
				break;
				
			case 'export':
				return $this->getExport( $request );
				break;
	
		}
	}

    public function getExport(Request $request, $t = 'excel')
    {
        set_time_limit(0);

        // column headings
        $tableheader = array(
            'A' => 'User ID',
            'B' => 'Name',
            'C' => 'Email',
            'D' => 'Total Cashback',
            'E' => 'Pending Cashback',
            'F' => 'Confirmed Cashback',
            'G' => 'Total CashOut',
            'H' => 'Last CashOut',
            'I' => 'Last CashOut Date',
            'J' => 'Orders Count',
            'K' => 'Last Order Date',
            'L' => 'Total Orders Amount',
            'M' => 'User Registered At',
            'N' => 'Country',
        );

        $fileName = 'sales-' . date('YmdHis', time());
        header('Content-Encoding: UTF-8');
        header("Content-type:application/vnd.ms-excel;charset=UTF-8");
        header('Content-Disposition: attachment;filename="' . $fileName . '.csv"');

        // Open the standard output stream for writing additional Open
        $fp = fopen('php://output', 'a');

        fputcsv($fp, $tableheader);

        $page = 0;
        while (true) {
            $limit = 1000;
            $queryOffset = $limit * $page++;
            $query = "select `u`.`user_id`                                              AS `user_id`,
       concat(`u`.`first_name`, ' ', `u`.`last_name`)             AS `name`,
       `u`.`email`                                                AS `email`,
       sum(`us`.`cashback`)                                       AS `cashback`,
       sum(if((`us`.`status` = 'pending'), `us`.`cashback`, 0))   AS `pending_cashback`,
       sum(if((`us`.`status` = 'confirmed'), `us`.`cashback`, 0)) AS `confirmed_cashback`,
       coalesce(`p`.`amount`, 0)                                  AS `cashout`,
       p.last_amount                                              as last_amount,
       p.last_payout_date                                         as last_payout_date,
       count(`us`.`id`)                                           AS `orders_count`,
       max(`us`.`transaction_time`)                               AS `last_transaction_time`,
       sum(`us`.`order_amount`)                                   AS `total_amount`,
       `u`.`created_at`                                           AS `registered_at`,
       `u`.`country_code`                                         AS `user_country`
from ((`yajny`.`user_sales` `us` join `yajny`.`users` `u` on ((`us`.`user_id` = `u`.`user_id`)))
         left join (select `yajny`.`user_payouts`.`user_id`     AS `user_id`,
                           sum(`yajny`.`user_payouts`.`amount`) AS `amount`,
                           user_payouts.amount                  as last_amount,
                           max(user_payouts.created_at)         as last_payout_date
                    from `yajny`.`user_payouts`
                             left join user_payouts up on up.user_id = user_payouts.user_id and user_payouts.id < up.id
                    where (`yajny`.`user_payouts`.`status` = 'completed' and up.id is null)
                    group by `yajny`.`user_payouts`.`user_id`) `p` on ((`p`.`user_id` = `u`.`user_id`)))
where `us`.`status` <> 'declined'";
            if ($request->from) {
                $query .= " and us.transaction_time > '{$request->from}'";
            }
            if ($request->to) {
                $query .= " and us.transaction_time < '{$request->to}'";
            }
            if ($request->country_code) {
                $query .= " and u.country_code = '{$request->country_code}'";
            }
            $query .= "group by `us`.`user_id` LIMIT {$limit} OFFSET {$queryOffset};";
            $data = DB::select($query);
            foreach ($data as $k => $v) {
                fputcsv($fp, (array) $v);
            }
            ob_flush();
            flush();
            if (empty($data) || count($data) < $limit)
                break;
        }
        ob_end_clean();
        exit;
    }
}