<?php

namespace App\Controllers;
use App\Models\BrandModel;
use App\Models\TalentModel;
use App\Models\UserModel;
use App\Models\StafModel;
use App\Models\FinanceModel;
use App\Models\DealjobModel;
use App\Models\PerformanceModel;
use App\Models\DraftpostModel;

class ReportController extends BaseController
{
    public function index()
    {
       
       
    }
    public function finance_report()
    {
         $header['title']='Database finance report';
        echo view('Report/Database-finance-report.php',$header);
    }


    public function get_finance()
    {
        $session = session();
        $username = $session->USER_NAME;
        $level = $session->USER_LEVEL;
        $datenow = date("Y-m-d H:i:s");
        $strwhere = "";
          if($level == "TALENT_MANAGER" || $level == "TALENT" )
            {
               $strwhere = " where ( t.PARENT_USERNAME = '" . $username . "' or t.USER_NAME = '". $username . "' )"; 
            }else
            {
                $strwhere = " where 1=1 ";
            }
        $DealjobModel = new DealjobModel();

        $nama = $this->request->getPost('nama');
        $tahun = $this->request->getPost('tahun');
        $bulan = $this->request->getPost('bulan');
        $status_from = $this->request->getPost('status_from');
        $status_to = $this->request->getPost('status_to');

        if($nama != ""){
            $strwhere =  $strwhere .  " and  t.xname like '%" . $nama . "%'";
        }

        if($tahun != ""){
            $strwhere =  $strwhere .  " and  d.DEAL_YEAR = '" . $tahun . "'";
        }

        if($bulan != ""){
            $strwhere =  $strwhere .  " and  d.DEAL_MONTH like  '%" . $bulan . "%'";
        }


        if($status_from != ""){
            $strwhere =  $strwhere .  " and  d.STAT_PAID_CLIENT =  " . $status_from . "";
        }

        if($status_to != ""){
            $strwhere =  $strwhere .  " and  d.STAT_PAID_TALENT =  " . $status_to . "";
        }

        $data['data']= $DealjobModel->query("SELECT d.DEAL_ID,
                    d.DEAL_YEAR,
                    d.DEAL_MONTH,
                    t.XNAME as talent_name,
                    d.BRAND_ID as brand_name,
                    d.DEAL_BY,
                    d.DEAL_VAL,
                    d.DEAL_VAL_TAX,
                    d.STAT_PAID_CLIENT,
                    IFNULL(d.DTM_PAID_CLIENT, '') as DTM_PAID_CLIENT ,
                    IFNULL(d.DTL_STAT_CLIENT, '') as DTL_STAT_CLIENT,
                    d.STAT_PAID_TALENT,
                    IFNULL(DATE_FORMAT(d.DTM_PAID_TALENT, '%d-%m-%Y %H:%i:%s'), '') as DTM_PAID_TALENT,
                    IFNULL(d.DTL_STAT_TALENT, '') as DTL_STAT_TALENT,
                    d.STAT_PAID_MANAGER,
                    COALESCE(d.DTM_PAID_MANAGER, '') as DTM_PAID_MANAGER,
                    IFNULL(d.INV, '') as INV,
                    IFNULL(d.INV_NUMBER, '') as INV_NUMBER,
                    d.STAT_TAX_PAID,
                    COALESCE(d.FAKTUR_PAJAK, '')  as FAKTUR_PAJAK,
                    COALESCE(d.CONTRACT, '')  as CONTRACT,
                    (d.DEAL_VAL * 20 ) / 100 as managemen_cut,
                    d.DEAL_VAL  - ((d.DEAL_VAL * 20 ) / 100 )as talent_share

                    FROM `tran_dealjob_payment` d
                    left join ref_talent t on t.TALENT_ID = d.TALENT_ID
                     " . $strwhere . "")->getResult();
        return $this->response->setJson($data); 
        
    }

       public function finance_recap()
    {
         $header['title']='Database finance recap';
        echo view('Report/Finance-recap.php',$header);
    }


    public function get_recap()
    {
        $session = session();
        $username = $session->USER_NAME;
        $datenow = date("Y-m-d H:i:s");
        $strwhere = " where ( t.PARENT_USERNAME = '" . $username . "' or t.USER_NAME = '". $username . "' )";
        $DealjobModel = new DealjobModel();

        $nama = $this->request->getPost('nama');
       
        $status_from = $this->request->getPost('status_from');
        $status_to = $this->request->getPost('status_to');

        if($nama != ""){
            $strwhere =  $strwhere .  " and  t.xname like '%" . $nama . "%'";
        }

      

        if($status_from != ""){
            $strwhere =  $strwhere .  " and  d.STAT_PAID_CLIENT =  " . $status_from . "";
        }

        if($status_to != ""){
            $strwhere =  $strwhere .  " and  d.STAT_PAID_TALENT =  " . $status_to . "";
        }

        $data['data']= $DealjobModel->query("SELECT d.DEAL_ID,
                    d.DEAL_YEAR,
                    d.DEAL_MONTH,
                    t.XNAME as talent_name,
                    d.BRAND_ID as brand_name,
                    d.DEAL_BY,
                    d.DEAL_VAL,
                    d.DEAL_VAL_TAX,
                    d.STAT_PAID_CLIENT,
                    d.DTM_PAID_CLIENT as DTM_PAID_CLIENT,
                    IFNULL(d.DTL_STAT_CLIENT, '') as DTL_STAT_CLIENT,
                    d.STAT_PAID_TALENT,
                    IFNULL(DATE_FORMAT(d.DTM_PAID_TALENT, '%d-%m-%Y %H:%i:%s'), '') as DTM_PAID_TALENT,
                    IFNULL(d.DTL_STAT_TALENT, '') as DTL_STAT_TALENT,
                    d.STAT_PAID_MANAGER,
                    COALESCE(d.DTM_PAID_MANAGER, '') as DTM_PAID_MANAGER,
                    COALESCE(d.INV, '') as INV,
                    COALESCE(d.INV_NUMBER, '') as INV_NUMBER,
                    d.STAT_TAX_PAID,
                    COALESCE(d.FAKTUR_PAJAK, '') as FAKTUR_PAJAK,
                    COALESCE(d.CONTRACT, '')  as CONTRACT,
                    (d.DEAL_VAL * 20 ) / 100 as managemen_cut,
                    d.DEAL_VAL  - ((d.DEAL_VAL * 20 ) / 100 )as talent_share

                    FROM `tran_dealjob_payment` d
                    inner join ref_talent t on t.TALENT_ID = d.TALENT_ID
                     " . $strwhere . "")->getResult();
        return $this->response->setJson($data); 
        
    }

        public function deal_job_payment()
    {
         $header['title']='Deal Job & Payment Report';
        echo view('Report/Dealjob-payment-report.php',$header);
    }
        public function draft_post()
    {
         $header['title']='Draft & Post Report';
        echo view('Report/Draft-post.php',$header);
    }
       public function talent_performance()
    {
         $header['title']='Talent perfornamce Report';
        echo view('Report/Talent-performance.php',$header);
    }

     public function get_performance()
    {
        $header['title']='Performance';
        $PerformanceModel = new PerformanceModel();

        $session = session();
        $username = $session->USER_NAME;
        $datenow = date("Y-m-d H:i:s");
        $strwhere = " where a.PERFORM_ID > 0 ";
        $nama = $this->request->getPost('nama');
        $tahun = $this->request->getPost('tahun');

         if($nama != ""){
            $strwhere =  $strwhere .  " and  t.xname like '%" . $nama . "%'";
        }

         if($tahun != ""){
            $strwhere =  $strwhere .  " and  a.PERFORM_YEAR = '" . $tahun . "'";
        }
      

       
        

 $data['data']= $PerformanceModel->query("SELECT 
            a.PERFORM_ID,
            t.XNAME as talent_name,
            a.PERFORM_YEAR,
            a.PERFORM_MONTH,
            a.FW_IG,
            l_a.PERFORM_ID as l_PERFORM_ID,
            l_a.FW_IG as last_FW_IG,
            case when l_a.FW_IG is null then 0 else CAST(( (( a.FW_IG - l_a.FW_IG ) / l_a.FW_IG ) * 100) AS DECIMAL(12,0)) end as PROG_IG ,
            a.FW_TIKTOK,
            case when l_a.FW_IG is null then 0 else  CAST(( (( a.FW_TIKTOK - l_a.FW_TIKTOK ) / l_a.FW_TIKTOK ) * 100) AS DECIMAL(12,0)) end as PROG_TIKTOK,
            a.FW_TWITTER,
            case when l_a.FW_TWITTER is null then 0 else  CAST(( (( a.FW_TWITTER - l_a.FW_TWITTER ) / l_a.FW_TWITTER ) * 100) AS DECIMAL(12,0)) end as PROG_TWITTER,
            a.FW_YOUTUBE,
            case when l_a.FW_YOUTUBE is null then 0 else  CAST(( (( a.FW_YOUTUBE - l_a.FW_YOUTUBE ) / l_a.FW_YOUTUBE ) * 100) AS DECIMAL(12,0)) end as PROG_YOUTUBE,
            a.ER_IG,
            case when l_a.ER_IG is null then 0 else  CAST(( (( a.ER_IG - l_a.ER_IG ) / l_a.ER_IG ) * 100) AS DECIMAL(12,0)) end as ER_PROG_IG,
            a.ER_TIKTOK,
            case when l_a.ER_TIKTOK is null then 0 else  CAST(( (( a.ER_TIKTOK - l_a.ER_TIKTOK ) / l_a.ER_TIKTOK ) * 100) AS DECIMAL(12,0)) end as ER_PROG_TIKTOK,
            a.ER_TWITTER,
            case when l_a.ER_TWITTER is null then 0 else  CAST(( (( a.ER_TWITTER - l_a.ER_TWITTER ) / l_a.ER_TWITTER ) * 100) AS DECIMAL(12,0)) end as ER_PROG_TWITTER ,
            a.ER_YOUTUBE,
            case when l_a.ER_YOUTUBE is null then 0 else  CAST(( (( a.ER_YOUTUBE - l_a.ER_YOUTUBE ) / l_a.ER_YOUTUBE ) * 100) AS DECIMAL(12,0)) end as ER_PROG_YOUTUBE  
            FROM tran_performance a
            inner join ref_talent t on t.TALENT_ID = a.TALENT_ID
            left join tran_performance l_a on l_a.PERFORM_ID = ( select  PERFORM_ID from tran_performance mm  where mm.TALENT_ID = t.TALENT_ID  and mm.PERFORM_MONTH < a.PERFORM_MONTH and mm.PERFORM_YEAR = a.PERFORM_YEAR  order  by mm.PERFORM_YEAR, mm.PERFORM_MONTH  desc limit 1 )
            " . $strwhere . " order by a.PERFORM_YEAR, a.PERFORM_MONTH asc")->getResult();


        return $this->response->setJson($data); 
    
        
    }

    public function calendarView() {
    $DraftpostModel = new DraftpostModel;
    $start 	= $_POST['start'];
	//$sdate	= date("Y-m-d\TH:i\Z", time($start));
	$end 	= $_POST['end'];
    $session = session();
    $username = $session->USER_NAME;
    $level = $session->USER_LEVEL;
	//$edate	= date("Y-m-d\TH:i\Z", time($end));
	$bookings = array();
	$sql	= "SELECT u.DETAIL_DRAFT, u.DRAFT_ID as id, r.XNAME as u_name, u.TALENT_ID as user_id , u.DRAFT_DTM as rdate, 'PENDING' as status FROM `tran_draft_post` u 
    inner join ref_talent r on r.TALENT_ID = u.TALENT_ID
			   AND (u.DRAFT_DTM BETWEEN '$start' AND '$end') 
               where r.PARENT_USERNAME = '".$username."' or r.USER_NAME = '".$username."'";
	//AND r.status = 'APPROVED'
	$result = $DraftpostModel->query($sql);
    foreach($result->getResultArray() as $row){
    	extract($row);
		$book = new Booking();
		$book->title = $u_name.'/'.$DETAIL_DRAFT;
		$book->start = $rdate; 
		$bgClr = '#f39c12';//pending
		if($status == 'DENIED') {$bgClr = '#ff0000';}
		else if($status == 'APPROVED') {$bgClr = '#00cc00';}
		$book->backgroundColor = $bgClr; //#7FFF00 -> green, #ff0000 red, #f39c12 -> pending 
		$book->borderColor = $bgClr;
		$book->url = site_url('report/get/event/' . $id);
		$bookings[] = $book; 
	}
	
	echo json_encode($bookings);
}

public function event_detail()
{
    $DraftpostModel = new DraftpostModel();
       
    $uri = service('uri');
    $id = $uri->getSegment('4');
    $header['title']='Deal Job & Payment: '.$id;
    $header['id'] = $id;
    echo view('Report/Draft-post.php',$header);   
    
}

   
}

class Booking {
	
	public $title;
	public $start;
	public $allDay = false;
	public $backgroundColor;
	public $borderColor;
	public $url;
	public $className;
	public $block = false;
		
	public function __constructor($t, $s) {
		$this->title = $t;
		$this->start = $s;
	}

}