<?php

namespace App\Http\Controllers;

use App\model\Leadclient;
use App\model\Property;
use App\Helper\helper;
use Illuminate\Http\Request;
use Session;
use DB;
use Mail;
use App\User;
use App\model\Agentemail;
use Auth;
use PDF;
use App\model\Company;
use App\model\Leadclientcalculation;
use App\model\Distributorleadmarket;
use App\model\Leasstatus_history;
use Validator;
use File;
use Carbon\Carbon;


class AgentincomeController extends Controller
{
    
    public function __construct()
    {
      $this->middleware('auth');
    }


  public static function setPaidDate($id,$paid_date){

    $data     =   ['newpaid_date'=>$paid_date];
    Leadclient::where('id',$id)->update($data);
    $result   = Leadclient::find($id);
    return $result->newpaid_date;
  }

/*
* @function : index (to list all income for agent user.)
* @params: request data agent id
* @return : json data of all income reccords
* @modifiedby:  chintesh k.
*/    
  public function index(Request $request){

    $id         = Auth::user()->id; 
    $user_role  = Auth::user()->user_role; 
    $items      = $request->items ?? 25;
    
    $sort           = isset($request->sort) ? $request->sort : 'id';
    $direction      = isset($request->direction) ? $request->direction : 'DESC';
        
    $role_for   = "AGENT";
    //dd("Working........");
    Session::put('role_for',$role_for);
       
    $leadclients      = Leadclient::join('users', 'users.id', '=', 'lead_client.agent_id')
        ->leftjoin('lead_status_history','lead_status_history.lead_id','lead_client.id')
        ->select('lead_client.*', 'users.name', 'users.last_name','lead_status_history.date as paid_date');


    $leadclientstotal = Leadclient::join('users', 'users.id', '=', 'lead_client.agent_id')
        ->leftjoin('lead_status_history','lead_status_history.lead_id','lead_client.id')
        ->select('lead_client.*', 'users.name', 'users.last_name');

    $useraragent = '';
    if($user_role == SUPERADMIN){
        
      $useraragent  = User::where('user_role','=',3)
                          ->orderBy('name','ASC')
                          ->get();
    }else if($user_role == ARUSER){

      $useraragent  = User::where('user_role','=',AGENT)
                            ->where('assign_to_ar','=',$id)
                            ->orderBy('name','ASC')
                            ->get();
    }elseif ($user_role == ADMIN) {
      # code...
     $id           =   Auth::User()->id;
    $useraragent  = User::where('user_role','=',3)
                        ->where('create_by_user','=',$id)
                        ->orderBy('name','ASC')->get();
                          
    }else{

      $useraragent  = User::where('user_role','=',AGENT)
                        ->where('id','=',$id)
                        ->orderBy('name','ASC')
                        ->get();
    }
//echo "<pre>"; print_r($useraragent); exit;

    if(session('aragentsearch') == ''){
      $firstuser =  $useraragent->first(); 
      if(!empty($firstuser)){   

        if($firstuser->id != ''){
          Session::put('aragentsearch', $firstuser->id);
        }
      }else{
        Session::put('aragentsearch',0);
      }
    }
         
    $agent_id           =   session('aragentsearch');
    $leadclients        =   $leadclients->where('agent_id','=',session('aragentsearch'));
    $leadclientstotal   =   $leadclientstotal->where('agent_id','=',session('aragentsearch'));


    $search_start_date  =   ''; 
    $search_end_date    =   ''; 
    $searchform         =   $request->input('search_agent'); 
   
    if(isset($searchform)){

      //$statusarry         = ['Invoiced','Pay Received','Paid Out','Sold','Sale Closing','Lease Confirmed','MIC','9','12','13','14','11','10'];
      $statusarry         = ['Paid Out','12'];
      
      $client_name        = $request->input('client_name');
      $client_id          = $request->input('client_id');
      $arr                =  explode(" ", $client_name);

      @$c_fname             = $arr[0];
      @$c_lname             = $arr[1];
      $data['client_name']  = $client_name;

      if ($client_name !='') {

        $leadclients      =  $leadclients->where('lead_client.fname',$c_fname);
        $leadclients      =  $leadclients->where('lead_client.lname',$c_lname);
        $leadclientstotal =  $leadclientstotal->where('lead_client.fname',$c_fname);
        $leadclientstotal =  $leadclientstotal->where('lead_client.lname',$c_lname);
      }

      if ($client_id !='') {
        $leadclients      =  $leadclients->where('lead_client.id',$client_id);
        $leadclientstotal =  $leadclientstotal->where('lead_client.id',$client_id);
        $data['client_id']  = $client_id;
      }

      $search_start_date = setdbdate($request->input('search_start_date'));
      $search_end_date   = setdbdate($request->input('search_end_date'));

      if($search_start_date != '' && $search_end_date != ''){
       
        //$leadclients =  $leadclients->whereIn('lead_status_history.status',$statusarry);
        //$leadclients =  $leadclients->whereIn('lead_client.curr_status',$statusarry);
        $leadclients =  $leadclients->whereBetween('lead_status_history.date',[$search_start_date,$search_end_date]);
        

        $leadclientstotal =  $leadclientstotal->whereIn('lead_client.curr_status',$statusarry);
       // $leadclientstotal =  $leadclientstotal->whereIn('lead_status_history.status',$statusarry);
        $leadclientstotal =  $leadclientstotal->whereBetween('lead_status_history.date',[$search_start_date,$search_end_date]);
      }
      
      
       $leadclients      = $leadclients->whereIn('lead_status_history.status',$statusarry);
       
    }else{

      $statusarry       = ['Invoiced','Pay Received','Paid Out','Sold','Sale Closing','Lease Confirmed','MIC','9','12','13','14','11','10'];
      //$statusarry         = ['Paid Out'];
      $statusarry123    = ['Invoiced','Pay Received','Paid Out','Sold','Sale Closing','Lease Confirmed','MIC','9','12','13','14','11','10'];
    //   $leadclients      = $leadclients->whereIn('lead_status_history.status',$statusarry);
    //   $leadclientstotal = $leadclientstotal->whereIn('lead_status_history.status',$statusarry123);
    
        $leadclients      = $leadclients->whereIn('lead_client.curr_status',$statusarry);
        $leadclientstotal = $leadclientstotal->whereIn('lead_client.curr_status',$statusarry123);
    }
    
    
    //$statusarry         = ['Invoiced','Pay Received','Paid Out','Sold','Sale Closing','Lease Confirmed','MIC','9','12','13','14','11','10'];
    //$statusarry123    = ['Invoiced','Pay Received','Paid Out','Sold','Sale Closing','Lease Confirmed','MIC','9','12','13','14','11','10'];
    
    $leadclientsall = $leadclients
                                    //->whereIn('curr_status', $statusarry)
                                        ->orderby('lead_client.'.$sort ,$direction)
                                        ->groupBy('lead_client.id')->get(); 
                                        
    
    $leadclientPDF      =   $leadclients->orderby('lead_client.'.$sort ,$direction)
                                        ->groupBy('lead_client.id')
                                        ->paginate($items);
                                        
                       // DB::enableQueryLog();
              
                                        
    $leadclients        =   $leadclients
                                        //->whereIn('curr_status', $statusarry)
                                        ->orderby('lead_client.'.$sort ,$direction)
                                        ->groupBy('lead_client.id')
                                        ->paginate($items); 
                                        
                     //dd(DB::getQueryLog()); exit;                        
                                        
    $statusarry123      =   ['Invoiced','Sold','Sale Closing','Lease Confirmed','MIC','9','12','11','10'];

    $leadclientstotal   =   $leadclientstotal->whereIn('curr_status', $statusarry123)
                                            ->orderby('lead_client.'.$sort ,$direction)
                                            ->groupBy('lead_client.id')
                                            ->get();
                        
    
    $tot_net          =   0;
    $tot_invoiced     =   0;
    $tot_paid         =   0;
    $outsatnginamt    =   0;
    $sc               =   count($leadclients);
    $newres_comm      =   0;
         
    foreach ($leadclientstotal  as $keytotal => $valuetotal) {
      // Implemented By Vinod Date 12/02/2020 start
      $caculation   =   $this->getcalculation($valuetotal->id);
      if(!empty($caculation)){
        $newres_comm  =   (float) $caculation['paidamount'];
      }else{
        $newres_comm  =   0;
      }
      $outsatnginamt += $newres_comm;
    }


    foreach( $leadclients  as $keytotal => $valuetotal) {
      // Nane - 19-11
      $agentleaddetail    =   User::find($valuetotal->agent_id);

      $statusarrychk      =   ['Pay Received', 'Paid Out','13','14'];
      $statusarrychk      =   ['Paid Out','14'];
        if(in_array($valuetotal->curr_status, $statusarrychk) && $valuetotal->inv_date < '2021-12-01'){
            $valuetotal->invoicing_cost =   (float) $valuetotal->invoicing_cost;
        }else{
            // $valuetotal->invoicing_cost =  (float) $agentleaddetail->invoicing_cost;
          //trasanction calculation Update by Swapnil
          $valuetotal->invoicing_cost = (float)transaction_calculation($valuetotal->transaction_type_id,$agentleaddetail,'invoicing_cost');
        }

      // Implemented By Vinod Date 12/02/2020 start
      $caculation           =   $this->getcalculation($valuetotal->id);
      $valuetotal->payment  =   number_format((float)$valuetotal->payment, 2, '.', '');
      $arr                  =   explode("~~~~",$valuetotal->status_history);
      $paid_date            =   "";
      
      $leadcalculatdata     =   Leadclientcalculation::where('leadclient_id','=',$valuetotal->id)
                                                  ->get()
                                                  ->first();
      $paid_amt             =   (float) $valuetotal->payment;
      $inc                  =   (float) $valuetotal->incentive;
              
      if(!empty($leadcalculatdata)){
        $leadcost   =   $leadcalculatdata->lead_cost;
      }else{
        $leadcost   =   0;
      }
     
      $user       =   User::find($valuetotal->agent_id);
      // $invcostt   =   (float) $user->invoicing_cost;
      //trasanction calculation Update by Swapnil
      $invcostt = (float)transaction_calculation($valuetotal->transaction_type_id,$user,'invoicing_cost');

      if(((float)$paid_amt-(float)$inc-(float)$leadcost-(float)$invcostt) > 0){
        $tot_net  += (float)$paid_amt-(float)$inc-(float)$leadcost-(float)$invcostt;
      }
      
      if($valuetotal->prop_id !=0 || $valuetotal->prop_id !=''){
      
        if (trim($valuetotal->commision)==""){
          $valuetotal->commision = 0;
        }

        if (trim($valuetotal->bonus) == ""){
          $valuetotal->bonus = 0;
        }

        if ($valuetotal->commision_unit == "1") {
           
          if ((float) $valuetotal->commision != 0 || (float) $valuetotal->commision!=0.00) {

              $tot_com          =   ((float)$valuetotal->rent_amount*((float)$valuetotal->commision/100));
              $total            =   (float) $tot_com + (float) $valuetotal->bonus;
              $commision_unit   =   "%";
          }else{
            $total  =   (float) $valuetotal->bonus;
          }
        }else{
          $total  =   (float) $valuetotal->commision + (float) $valuetotal->bonus;
        } 
      }else{

        if(trim($valuetotal->sold_amt)==""){
          $valuetotal->sold_amt = 0;
        }

        if(trim($valuetotal->house_comm)==""){
          $valuetotal->house_comm = 0;
        }   

        if(trim($valuetotal->house_bonus)==""){
          $valuetotal->house_bonus = 0;
        }   
          
        $total =  ((float) $valuetotal->sold_amt * ((float) $valuetotal->house_comm/100)) + (float) $valuetotal->house_bonus;
      }
      $tot_invoiced += $total;
      $tot_paid     += $paid_amt;
    }
    
    $total_incoe = array();
    foreach($leadclients as $leadclient){
        $leadcalculation = lead_calculate($leadclient->id);
        array_push($total_incoe,$leadcalculation['agent_paid']);
    
    }

    $income_detail = get_income_calculation_admin(json_decode(json_encode($leadclientsall), true),'agentincome');
    
    //$data['totalincome']          = $tot_net;
    
    $data['income_detail'] = income_details('agent',session('aragentsearch'))['agent_income'];
    
   //echo '<pre>'; print_r($income_detail); 
   //exit;
    
    $data['totalincome']          = array_sum($total_incoe);
    $data['outsatnginamt']        = $outsatnginamt;
    $data['url']                  = 'searchagentincome';
    $data['search_start_date']    = $search_start_date;
    $data['search_end_date']      = $search_end_date;
    $data['leadclients']          = $leadclients;
    $data['leadclientPDF']        = $leadclientPDF;
    $data['useraragents']         = $useraragent;
    $data['search_start_date']    = $search_start_date;
    $data['search_end_date']      = $search_end_date;
    $data['show_agent_list']      = 1;
    $data['reseturl']             = 'agentincome';
    $data['datasall']             = $leadclientsall;
    
    $data['total_incoe']        = $income_detail['company_income'];
    $data['total_invoice']      = $income_detail['rescommission'];
    $data['gross_pay']          = $income_detail['gross_revenue'];
    $data['outsatnginamt']      = $income_detail['outstanding'];
    $data['agent_income']       = $income_detail['agent_income']; 
    $data['company_income']     = $income_detail['company_income'];
    
   //echo "<pre>"; print_r($data); exit;
    
    return view('agentincome.index',['data'=>$data])->withMembers($leadclients)->withItems($items);
  }
 
/*
* @function : IncomePDF (to list all income into PDF file and download it.)
* @params: request data
* @return : json data of all income reccords role wise
* @modifiedby:  chintesh k.
*/


public function getAdminIncomePDF(Request $request){
      
        $request->input = $_REQUEST;
        $method         = $request->input('method');
        
        $id             = Auth::user()->id; 
        $user_role      = Auth::user()->user_role;
   
        //print_r($method); exit;
    // code by Randhir kumar//
        $outstanding      = $request->input('outstanding');
        $gross            = $request->input('gross');
        $totaln           = $request->input('total');
        $path             = $request->input('path');
        $ids                = explode(',',$request->input('ids'));
        
        $search_start_date           = $request->input('search_start_date');
        $search_end_date             = $request->input('search_end_date');
        
        $searchform = $request->input('search_agent');
    
    
        if($method == 'arincome'){

            $useraragent='';
            $data['show_agent_list']=0;
            if($user_role == SUPERADMIN)
            {
              
              $useraragent = User::where('user_role','=',ARUSER)
                            ->where('status','=',1)->orderBy('name','ASC')->get();
                    $data['show_agent_list']=1;
            }elseif($user_role == ADMIN){
          
                $useraragent = User::where('user_role','=',ARUSER)
                          ->where('create_by_user','=',$id)
                          ->where('status','=',1)->orderBy('name','ASC')->get();
                $data['show_agent_list']=1;   
            }else{
    
              $useraragent = User::where('user_role','=',ARUSER)
                        ->where('id','=',$id)
                        ->where('status','=',1)->orderBy('name','ASC')->get();
              Session::put('aragentsearch',$id);         
            }

            $firstuser =  $useraragent->first(); 
    
            if(session('aragentsearch') == ''){
              $firstuser =  $useraragent->first(); 
              if(!empty($firstuser)){   
                if($firstuser->id != ''){
                  Session::put('aragentsearch', $firstuser->id);
                }
              }else{
                Session::put('aragentsearch',0);
              }
            }
           
            $agent_id         =   session('aragentsearch'); 
            $arUser           =   User::select('id')->where('assign_to_ar','=',$agent_id);
            // print_r($agent_id); exit;
            $status ="'Lease Confirmed','Invoiced','Sold','Sale Closing','10','12','9','Pay Received','Paid Out','13','14'";
            
            $condition = " lead_client.agent_id in(select id from users where assign_to_ar='$agent_id')  ";
            
             $condition .=" and lead_client.curr_status IN($status) ";
            
            if(!empty($params['client_name'])){
                $name = explode(' ',$params['client_name']);
                $fname = $name[0];
                $lname = $name[1];
            
                if(!empty($fname)){
                    $condition .=" and lead_client.fname Like '$fname%'";
                }
                
                if(!empty($lname)){
                    $condition .=" and lead_client.lname Like '$lname%'";
                } 
            }
        
            if(!empty($params['client_id'])){
                $clintid = $params['client_id'];
                $condition .=" and lead_client.id = '$clintid'";
            }
           
            if(!empty($params['search_start_date']) && !empty($params['search_end_date'])){
                
                $search_start_date  = setdbdate($params['search_start_date']);
                $search_end_date    = setdbdate($params['search_end_date']);
                
                $condition .=" and lead_status_history.date BETWEEN '$search_start_date' AND '$search_end_date'";
            }
            
            $order_column = 'lead_client.id';
            $orderby = "DESC";
            
            $resultscount= DB::select("select `lead_client`.id,lead_client.payment,lead_client.invoiced_total,agent_paid,lead_client.curr_status,lead_client.deducation,lead_client.incentive,`lead_status_history`.`date` as `paid_date`,`users`.`name` as fname, `users`.`last_name` as lname from `lead_client` inner join `users` on `users`.`id` = `lead_client`.`agent_id` inner join `lead_status_history` on `lead_status_history`.`lead_id` = `lead_client`.`id` where $condition GROUP BY lead_client.id order by $order_column $orderby");
            
            //echo "select `lead_client`.id,lead_client.payment,lead_client.invoiced_total,agent_paid,lead_client.curr_status,lead_client.deducation,lead_client.incentive,`lead_status_history`.`date` as `paid_date`,`users`.`name` as fname, `users`.`last_name` as lname from `lead_client` inner join `users` on `users`.`id` = `lead_client`.`agent_id` inner join `lead_status_history` on `lead_status_history`.`lead_id` = `lead_client`.`id` where $condition GROUP BY lead_client.id order by $order_column $orderby";
           // exit;
           
            if($path == '/adminincome'){
                $title= $usserpage = 'Admin Income';
            }elseif($path == '/arincome'){
                $title= $usserpage = "AR Income";
            }else{
                $title= $usserpage = "Agent Income";
            }
         
            $response['title'] =   $title;
            
            $pdf_link = generate_income_pdf($resultscount,'arincome');
    
   //echo "<pre>";print_r($pdf_link); exit;
      //echo $footerHtml; exit;
            $pdf = PDF::loadView('agentincome.printpdf_new',['data'=>$pdf_link])->setPaper('a4', 'landscape');
        
            return $pdf->download('ar_income.pdf');
            
            //echo "<pre>";print_r($pdf_link); 
            exit;
        
        }elseif($method == 'adminincome' || $method=='searchadincome'){

            $useraragent      = '';
            $data['show_agent_list']=0;

            if($user_role == SUPERADMIN){
              
                $useraragent    = User::where('user_role','=',ADMIN)->where('status','=',1)->orderBy('name','ASC')->get();
                $data['show_agent_list']  = 1;
    
             }elseif($user_role == ADMIN){
          
                $useraragent = User::where('user_role','=',ADMIN)->where('id','=',$id)->where('status','=',1)->orderBy('name','ASC')->get();
                Session::put('aragentsearch', $id);               
            }else{
    
                $useraragent = User::where('id','=',$id)
                            ->where('status','=',1)
                            ->orderBy('name','ASC')
                            ->get();
            }

            $firstuser =  $useraragent->first(); 
    
            if(session('aragentsearch') == ''){
    
              $firstuser =  $useraragent->first(); 
              if(!empty($firstuser)){   
    
                if($firstuser->id != ''){
                  Session::put('aragentsearch', $firstuser->id);
                }
              }else{
                Session::put('aragentsearch',0);
              }
            }
         
            $agent_id = session('aragentsearch');
            $arUser=User::select('id')->where('create_by_user','=',$agent_id)->get();
            
            //echo $agent_id; exit;
            
           
            $status ="'Lease Confirmed','Invoiced','Sold','Sale Closing','10','12','9','Pay Received','Paid Out','13','14'";
            
            if(!empty($_REQUEST['search_start_date']) && !empty($_REQUEST['search_end_date'])){
                //echo "Herer"; exit;
                 $status ="'Paid Out','14'";
                
                
            }
            
            $condition = " lead_client.agent_id in(select id from users where create_by_user='$agent_id')  ";
            
            
             $condition .=" and lead_client.curr_status IN($status) ";
            
            if(!empty($params['client_name'])){
                $name = explode(' ',$params['client_name']);
                $fname = $name[0];
                $lname = $name[1];
            
                if(!empty($fname)){
                    $condition .=" and lead_client.fname Like '$fname%'";
                }
                
                if(!empty($lname)){
                    $condition .=" and lead_client.lname Like '$lname%'";
                } 
            }
        
            if(!empty($params['client_id'])){
                $clintid = $params['client_id'];
                $condition .=" and lead_client.id = '$clintid'";
            }
           
            if(!empty($_REQUEST['search_start_date']) && !empty($_REQUEST['search_end_date'])){
                
                $search_start_date  = setdbdate($_REQUEST['search_start_date']);
                $search_end_date    = setdbdate($_REQUEST['search_end_date']);
                
                $condition .=" and lead_status_history.date BETWEEN '$search_start_date' AND '$search_end_date'";
            }
            
            $order_column = 'lead_client.id';
            $orderby = "DESC";
            
            //echo $condition; exit;
            
            
            $resultscount= DB::select("select `lead_client`.id,lead_client.payment,lead_client.invoiced_total,agent_paid,lead_client.curr_status,lead_client.deducation,lead_client.incentive,`lead_status_history`.`date` as `paid_date`,`users`.`name` as fname, `users`.`last_name` as lname from `lead_client` inner join `users` on `users`.`id` = `lead_client`.`agent_id` inner join `lead_status_history` on `lead_status_history`.`lead_id` = `lead_client`.`id` where $condition GROUP BY lead_client.id order by $order_column $orderby");
            
           // echo "select `lead_client`.id,lead_client.payment,lead_client.invoiced_total,agent_paid,lead_client.curr_status,lead_client.deducation,lead_client.incentive,`lead_status_history`.`date` as `paid_date`,`users`.`name` as fname, `users`.`last_name` as lname from `lead_client` inner join `users` on `users`.`id` = `lead_client`.`agent_id` inner join `lead_status_history` on `lead_status_history`.`lead_id` = `lead_client`.`id` where $condition GROUP BY lead_client.id order by $order_column $orderby"; exit;
            
            //$pdf_link = generate_income_pdf($resultscount,'adminincome');
            
            if($path == '/adminincome'){
                $title= $usserpage = 'Admin Income';
            }elseif($path == '/arincome'){
                $title= $usserpage = "AR Income";
            }else{
                $title= $usserpage = "Agent Income";
            }
         
            $response['title'] =   $title;
            
            $pdf_link = generate_income_pdf($resultscount,'adminincome');
    
   //echo "<pre>";print_r($pdf_link); exit;
      //echo $footerHtml; exit;
            $pdf = PDF::loadView('agentincome.printpdf_new',['data'=>$pdf_link])->setPaper('a4', 'landscape');
        
            return $pdf->download('admin_income.pdf');
            
            //echo "<pre>";print_r($pdf_link); 
            exit;
       //echo $agent_id; exit;
        }else{

            $useraragent='';

            if($user_role == SUPERADMIN){
        
                $useraragent  = User::where('user_role','=',3)
                                ->where('status','=',1)
                                ->orderBy('name','ASC')
                                ->get();
            }else if($user_role == ARUSER){
    
                $useraragent  =   User::where('user_role','=',AGENT)
                                      ->where('assign_to_ar','=',$id)
                                      ->where('status','=',1)
                                      ->orderBy('name','ASC')
                                      ->get();
            }elseif ($user_role == ADMIN) {
              # code...
                $useraragent    = User::where('user_role','=',AGENT)
                                      ->where('create_by_user','=',$id)
                                      ->where('status','=',1)
                                      ->orderBy('name','ASC')
                                      ->get();
            }else{
    
                $useraragent  = User::where('user_role','=',AGENT)
                            ->where('id','=',$id)
                            ->where('status','=',1)
                            ->orderBy('name','ASC')
                            ->get();
            }

            if(session('aragentsearch') == ''){
    
              $firstuser =  $useraragent->first(); 
    
              if(!empty($firstuser)){   
    
                if($firstuser->id != ''){
                  Session::put('aragentsearch', $firstuser->id);
                }
              }else{
                Session::put('aragentsearch',0);
              }
            }
            $agent_id  =   session('aragentsearch');
        }
     
   
        $tot_net       = 0;
        $tot_invoiced  = 0;
        $tot_paid      = 0;
        $outsatnginamt = 0;
    
        $response['totalincome']        =   $tot_net;
        $response['outsatnginamt']      =   $outsatnginamt;
        $response['url']                =   'searchagentincome';
        $response['search_start_date']  =   $search_start_date;
        $response['search_end_date']    =   $search_end_date;
        
        //$response['leadclients']        =   $leadclients;
        //$response['leadclientPDF']      =   $leadclients;
        //$response['useraragents']       =   $useraragent;
           
        //   $data['search_start_date']  =   $search_start_date;
        $response['show_agent_list']    =   1;
        $response['reseturl']           =   $method;
        
        // code by randhir kumar//
        $response['outstanding']          =   $outstanding;
        $response['gross']                =   $gross;
        $response['total']                =   $totaln;
          
        if($path == '/adminincome'){
            $title= $usserpage = 'Admin Income';
        }elseif($path == '/arincome'){
            $title= $usserpage = "AR Income";
        }else{
            $title= $usserpage = "Agent Income";
        }
         
        $response['title']           =   $title;
    
    
      //echo $footerHtml; exit;
        $pdf = PDF::loadView('agentincome.printpdf',['data'=>$response])->setPaper('a4', 'landscape');
        
        return $pdf->download('income.pdf');
    
    }
    
public function IncomePDF(Request $request){
          
      $request->input=$_REQUEST;
         
    $method         = $request->input('method');
   
     //print_r($method); exit;
    // code by Randhir kumar//
    $outstanding      = $request->input('outstanding');
    $gross            = $request->input('gross');
    $totaln           = $request->input('total');
    $path             = $request->input('path');
    $ids                = explode(',',$request->input('ids'));
    
    $search_start_date           = $request->input('search_start_date');
    $search_end_date             = $request->input('search_end_date');
    
    $searchform = $request->input('search_agent');
    
    
    // if($method=='searchadincome' || $method=='searcharincome' || $method =='searchagentincome'){
    //     $searchform='submit';
    // }
    
      // End here //
      //DB::enableQueryLog(); 
      $id             = Auth::user()->id; 
      $user_role      = Auth::user()->user_role; 
        //$leadclients      = Leadclient::join('users', 'users.id', '=', 'lead_client.agent_id')->select('lead_client.id','lead_client.payment', 'users.name', 'users.last_name','lead_client.fname','lead_client.lname','lead_client.payment');
             
        //$leadclients    = $leadclients->whereIN('lead_client.id',$ids)->orderBy('lead_client.id','desc')->get();
        
       // $leads =json_encode(json_decode(json_encode($leadclients), true));
        //echo "<pre>";print_r(($leads)); exit;
          

  //echo $method; exit;
      if($method == 'arincome' || $method=='searcharincome'){

            $useraragent='';
            $data['show_agent_list']=0;
            if($user_role == SUPERADMIN)
            {
              
              $useraragent = User::where('user_role','=',ARUSER)
                            ->where('status','=',1)->orderBy('name','ASC')->get();
                    $data['show_agent_list']=1;
            }elseif($user_role == ADMIN){
          
                $useraragent = User::where('user_role','=',ARUSER)
                          ->where('create_by_user','=',$id)
                          ->where('status','=',1)->orderBy('name','ASC')->get();
                $data['show_agent_list']=1;   
            }else{
    
              $useraragent = User::where('user_role','=',ARUSER)
                        ->where('id','=',$id)
                        ->where('status','=',1)->orderBy('name','ASC')->get();
              Session::put('aragentsearch',$id);         
            }

            $firstuser =  $useraragent->first(); 
    
            if(session('aragentsearch') == ''){
              $firstuser =  $useraragent->first(); 
              if(!empty($firstuser)){   
                if($firstuser->id != ''){
                  Session::put('aragentsearch', $firstuser->id);
                }
              }else{
                Session::put('aragentsearch',0);
              }
            }
           
            $agent_id         =   session('aragentsearch'); 
            $arUser           =   User::select('id')->where('assign_to_ar','=',$agent_id);
            
            
            if($method =='searcharincome' && !empty($_REQUEST['search_start_date']) && $_REQUEST['search_end_date']){
                
                $status = "'Paid Out','14'";
                
                
            }else{
                
                $status = "'Sold','Invoiced','Pay Received','MIC','Paid Out','9','12','13','14','11'";
                
            }
            
            //echo $status; exit;
            $condition = "`agent_id` in (select `id` from `users` where `assign_to_ar` = $agent_id) and `lead_client`.`curr_status` in ($status) ";
            
            if(!empty($_REQUEST['search_start_date']) && $_REQUEST['search_end_date']){
                
                $start_date = setdbdate($_REQUEST['search_start_date']);
                $end_date = setdbdate($_REQUEST['search_end_date']);
                
                $condition .=" and `lead_client`.`newpaid_date` between '$start_date' and '$end_date'";
            }
            
            $client_id          = $_REQUEST['client_id'];
            if (!empty($client_id)) {
                
                $condition .=" and lead_client.id = '$client_id' ";
              
            }
            
           
            $client_name        = $_REQUEST['client_name'];
            $arr = explode(' ',$client_name);
            @$c_fname             = $arr[0];
            @$c_lname             = $arr[1];
            
            if(!empty($client_name)) {
                $condition .=" and lead_client.fname = '$c_fname' ";
                $condition .=" and lead_client.lname = '$c_lname' ";
            }
            
             //print_r($condition); exit;
            
            $sql = "select `lead_client`.*, `users`.`name`, `users`.`last_name`, `lead_status_history`.`date` as `paid_date` from `lead_client` inner join `users` on `users`.`id` = `lead_client`.`agent_id` inner join `lead_status_history` on `lead_status_history`.`lead_id` = `lead_client`.`id` where $condition  group by `lead_client`.`id`, `lead_client`.`id`, `lead_client`.`id` order by `lead_client`.`id` desc";
            
            $leadclients = DB::select($sql);
           // echo $sql; exit;
    
            $leadclientPDF =$leadclients;
            $leadclientstotal =$leadclients;
        
        }elseif($method == 'adminincome' || $method=='searchadincome'){

            $useraragent      = '';
            $data['show_agent_list']=0;
    
            if($user_role == SUPERADMIN){
              
                $useraragent    = User::where('user_role','=',ADMIN)->where('status','=',1)->orderBy('name','ASC')->get();
                $data['show_agent_list']  = 1;
    
             }elseif($user_role == ADMIN){
          
                $useraragent = User::where('user_role','=',ADMIN)->where('id','=',$id)->where('status','=',1)->orderBy('name','ASC')->get();
                Session::put('aragentsearch', $id);               
            }else{
    
                $useraragent = User::where('id','=',$id)
                            ->where('status','=',1)
                            ->orderBy('name','ASC')
                            ->get();
            }

            $firstuser =  $useraragent->first(); 
    
            if(session('aragentsearch') == ''){
    
              $firstuser =  $useraragent->first(); 
              if(!empty($firstuser)){   
    
                if($firstuser->id != ''){
                  Session::put('aragentsearch', $firstuser->id);
                }
              }else{
                Session::put('aragentsearch',0);
              }
            }
         
            $agent_id = session('aragentsearch');
            $arUser=User::select('id')->where('create_by_user','=',$agent_id)->get();
       
      }else{

        $useraragent='';

        if($user_role == SUPERADMIN){
        
          $useraragent  = User::where('user_role','=',3)
                                ->where('status','=',1)
                                ->orderBy('name','ASC')
                                ->get();
        }else if($user_role == ARUSER){

          $useraragent  =   User::where('user_role','=',AGENT)
                                  ->where('assign_to_ar','=',$id)
                                  ->where('status','=',1)
                                  ->orderBy('name','ASC')
                                  ->get();
        }elseif ($user_role == ADMIN) {
          # code...
          $useraragent    = User::where('user_role','=',AGENT)
                                  ->where('create_by_user','=',$id)
                                  ->where('status','=',1)
                                  ->orderBy('name','ASC')
                                  ->get();
        }else{

         $useraragent  = User::where('user_role','=',AGENT)
                        ->where('id','=',$id)
                        ->where('status','=',1)
                        ->orderBy('name','ASC')
                        ->get();
        }

        if(session('aragentsearch') == ''){

          $firstuser =  $useraragent->first(); 

          if(!empty($firstuser)){   

            if($firstuser->id != ''){
              Session::put('aragentsearch', $firstuser->id);
            }
          }else{
            Session::put('aragentsearch',0);
          }
        }
        $agent_id  =   session('aragentsearch');
        
        //echo $agent_id; exit;
        
        
        if($method =='searcharincome' && !empty($_REQUEST['search_start_date']) && $_REQUEST['search_end_date']){
                $status = "'Paid Out','14'";
            }else{
                $status = "'Invoiced','Pay Received','Paid Out','Sold','Sale Closing','Lease Confirmed','MIC','9','12','13','14','11','10'";
            }
            
            //echo $status; exit;
            $condition = "`agent_id` = $agent_id and `lead_client`.`curr_status` in ($status) ";
            
            if(!empty($_REQUEST['search_start_date']) && $_REQUEST['search_end_date']){
                
                $start_date = setdbdate($_REQUEST['search_start_date']);
                $end_date = setdbdate($_REQUEST['search_end_date']);
                
                $condition .=" and `lead_client`.`newpaid_date` between '$start_date' and '$end_date'";
            }
            
            $client_id          = $_REQUEST['client_id'];
            if (!empty($client_id)) {
                
                $condition .=" and lead_client.id = '$client_id' ";
              
            }
            
           
            $client_name        = $_REQUEST['client_name'];
            $arr = explode(' ',$client_name);
            @$c_fname             = $arr[0];
            @$c_lname             = $arr[1];
            
            if(!empty($client_name)) {
                $condition .=" and lead_client.fname = '$c_fname' ";
                $condition .=" and lead_client.lname = '$c_lname' ";
            }
            
             //print_r($condition); exit;
            
            $sql = "select `lead_client`.*, `users`.`name`, `users`.`last_name`, `lead_status_history`.`date` as `paid_date` from `lead_client` inner join `users` on `users`.`id` = `lead_client`.`agent_id` inner join `lead_status_history` on `lead_status_history`.`lead_id` = `lead_client`.`id` where $condition  group by `lead_client`.`id`, `lead_client`.`id`, `lead_client`.`id` order by `lead_client`.`id` desc";
            
            $leadclients = DB::select($sql);
     }
     
   
    $tot_net       = 0;
    $tot_invoiced  = 0;
    $tot_paid      = 0;
    $outsatnginamt = 0;
    
    $response['totalincome']        =   $tot_net;
    $response['outsatnginamt']      =   $outsatnginamt;
    $response['url']                =   'searchagentincome';
    $response['search_start_date']  =   $search_start_date;
    $response['search_end_date']    =   $search_end_date;
    
    $response['leadclients']        =   $leadclients;
    $response['leadclientPDF']      =   $leadclients;
    $response['useraragents']       =   $useraragent;
       
    //   $data['search_start_date']  =   $search_start_date;
    $response['show_agent_list']    =   1;
    $response['reseturl']           =   $method;
    
    // code by randhir kumar//
    $response['outstanding']          =   $outstanding;
    $response['gross']                =   $gross;
    $response['total']                =   $totaln;
      
    if($path == '/adminincome'){
        $title= $usserpage = 'Admin Income';
    }elseif($path == '/arincome'){
        $title= $usserpage = "AR Income";
    }else{
        $title= $usserpage = "Agent Income";
    }
         
    $response['title']           =   $title;
    
    
      //echo $footerHtml; exit;
    $pdf = PDF::loadView('agentincome.printpdf',['data'=>$response])->setPaper('a4', 'landscape');
             
             
return $pdf->download('agentincome.pdf');
    
}


// public function IncomePDF(Request $request){
      
//     $method         = $request->input('method');
   
//       //print_r($_POST); exit;
//     // code by Randhir kumar//
//     $outstanding      = $request->input('outstanding');
//     $gross            = $request->input('gross');
//     $totaln           = $request->input('total');
//     $path             = $request->input('path');
      
//     $searchform = $request->input('search_agent');
    
//     if($method=='searchadincome' || $method=='searcharincome' || $method =='searchagentincome'){
//         $searchform='submit';
//     }
      
      
//       //echo "<pre>";print_r($_POST); exit;
//       // End here //
//       //DB::enableQueryLog(); 
//       $id             = Auth::user()->id; 
//       $user_role      = Auth::user()->user_role; 
//       $leadclients    = Leadclient::join('users', 'users.id', '=', 'lead_client.agent_id')
//                                     ->select('lead_client.*', 'users.name', 'users.last_name')
//                                     ->leftjoin('lead_status_history','lead_status_history.lead_id','lead_client.id');

//     //
    
//       $leadclientstotal = Leadclient::join('users', 'users.id', '=', 'lead_client.agent_id')
//                                       ->leftjoin('lead_status_history','lead_status_history.lead_id','lead_client.id')
//                                       ->select('lead_client.*', 'users.name', 'users.last_name');
//   //echo $method; exit;
//       if($method == 'arincome'){

//         $useraragent='';
//         $data['show_agent_list']=0;
//         if($user_role == SUPERADMIN)
//         {
          
//           $useraragent = User::where('user_role','=',ARUSER)
//                         ->where('status','=',1)->orderBy('name','ASC')->get();
//                 $data['show_agent_list']=1;
//         }elseif($user_role == ADMIN){
      
//             $useraragent = User::where('user_role','=',ARUSER)
//                       ->where('create_by_user','=',$id)
//                       ->where('status','=',1)->orderBy('name','ASC')->get();
//             $data['show_agent_list']=1;   
//         }else{

//           $useraragent = User::where('user_role','=',ARUSER)
//                     ->where('id','=',$id)
//                     ->where('status','=',1)->orderBy('name','ASC')->get();
//           Session::put('aragentsearch',$id);         
//         }

//         $firstuser =  $useraragent->first(); 

//         if(session('aragentsearch') == ''){
//           $firstuser =  $useraragent->first(); 
//           if(!empty($firstuser)){   
//             if($firstuser->id != ''){
//               Session::put('aragentsearch', $firstuser->id);
//             }
//           }else{
//             Session::put('aragentsearch',0);
//           }
//         }
           
//         $agent_id         =   session('aragentsearch'); 
//         $arUser           =   User::select('id')->where('assign_to_ar','=',$agent_id);
        
//         //if(empty($method)){
//             $leadclients      =   $leadclients->whereIN('agent_id',$arUser);
//             $leadclientstotal =   $leadclientstotal->whereIN('agent_id',$arUser);
//         //}
        
//       }elseif($method == 'adminincome' || $method=='searchadincome'){

//         $useraragent      = '';
//         $data['show_agent_list']=0;

//         if($user_role == SUPERADMIN){
          
//           $useraragent              = User::where('user_role','=',ADMIN)
//                                             ->where('status','=',1)
//                                             ->orderBy('name','ASC')
//                                             ->get();
//           $data['show_agent_list']  = 1;

//          }elseif($user_role == ADMIN){
      
//           $useraragent = User::where('user_role','=',ADMIN)
//                               ->where('id','=',$id)
//                               ->where('status','=',1)
//                               ->orderBy('name','ASC')
//                               ->get();
//           Session::put('aragentsearch', $id);               
//         }else{

//           $useraragent = User::where('id','=',$id)
//                                 ->where('status','=',1)
//                                 ->orderBy('name','ASC')
//                                 ->get();
//         }

//         $firstuser =  $useraragent->first(); 

//         if(session('aragentsearch') == ''){

//           $firstuser =  $useraragent->first(); 
//           if(!empty($firstuser)){   

//             if($firstuser->id != ''){
//               Session::put('aragentsearch', $firstuser->id);
//             }
//           }else{
//             Session::put('aragentsearch',0);
//           }
//         }
         
//         $agent_id = session('aragentsearch');
//         $arUser=User::select('id')->where('create_by_user','=',$agent_id)->get();
//         //if(empty($method)){
//             $leadclients =  $leadclients->whereIN('agent_id',$arUser);
//             $leadclientstotal =  $leadclientstotal->whereIN('agent_id',$arUser);
//         //}
        
        
//       }else{

//         $useraragent='';

//         if($user_role == SUPERADMIN){
        
//           $useraragent  = User::where('user_role','=',3)
//                                 ->where('status','=',1)
//                                 ->orderBy('name','ASC')
//                                 ->get();
//         }else if($user_role == ARUSER){

//           $useraragent  =   User::where('user_role','=',AGENT)
//                                   ->where('assign_to_ar','=',$id)
//                                   ->where('status','=',1)
//                                   ->orderBy('name','ASC')
//                                   ->get();
//         }elseif ($user_role == ADMIN) {
//           # code...
//           $useraragent    = User::where('user_role','=',AGENT)
//                                   ->where('create_by_user','=',$id)
//                                   ->where('status','=',1)
//                                   ->orderBy('name','ASC')
//                                   ->get();
//         }else{

//           $useraragent  = User::where('user_role','=',AGENT)
//                                 ->where('id','=',$id)
//                                 ->where('status','=',1)
//                                 ->orderBy('name','ASC')
//                                 ->get();

//         }

//         if(session('aragentsearch') == ''){

//           $firstuser =  $useraragent->first(); 

//           if(!empty($firstuser)){   

//             if($firstuser->id != ''){
//               Session::put('aragentsearch', $firstuser->id);
//             }
//           }else{
//             Session::put('aragentsearch',0);
//           }
//         }
         
//         $agent_id           =   session('aragentsearch');
//         if(empty($searchform)){
//             $leadclients        =   $leadclients->where('agent_id','=',session('aragentsearch'));
//             $leadclientstotal   =   $leadclientstotal->where('agent_id','=',session('aragentsearch'));
//         }
//      }

//     $search_start_date=''; 
//     $search_end_date = ''; 
   
    
//       if(isset($searchform)){

//         //echo "I  m here "; exit;
//         //$statusarry         = ['Pay Received'];
//         $statusarry   = ['Paid Out'];
        
//         $search_start_date  = setdbdate($request->input('search_start_date'));
//         $search_end_date    = setdbdate($request->input('search_end_date'));

//         if($search_start_date != '' && $search_end_date != ''){

//           //$leadclients =  $leadclients->whereIn('lead_status_history.status',$statusarry);
          
//             $leadclients = $leadclients->whereIn('lead_client.curr_status',$statusarry);
//             $leadclients =  $leadclients->whereBetween('lead_status_history.date',[$search_start_date,$search_end_date]);
      
//             $leadclientstotal =  $leadclientstotal->whereIn('lead_status_history.status',$statusarry);
//             $leadclientstotal =  $leadclientstotal->whereBetween('lead_status_history.date',[$search_start_date,$search_end_date]);
//         }
        
//         $client_name        = $request->input('client_name');
//       //echo "client name ".$client_name; exit;
//         $arr                =  explode(" ", $client_name);
        
//         @$c_fname             = $arr[0];
//         @$c_lname             = $arr[1];
//         $data['client_name']  = $client_name;
      
      
//         if($client_name !='') {
//             $leadclients          =  $leadclients->where('lead_client.fname',$c_fname);
//             $leadclients          =  $leadclients->where('lead_client.lname',$c_lname);
            
//             $leadclientstotal     =  $leadclientstotal->where('lead_client.fname',$c_fname);
//             $leadclientstotal     =  $leadclientstotal->where('lead_client.lname',$c_lname);
//           }
          
          
//         $client_id          = $request->input('client_id');
//         if ($client_id !='') {
//             $leadclients      =  $leadclients->where('lead_client.id',$client_id);
//             $leadclientstotal =  $leadclientstotal->where('lead_client.id',$client_id);
//         }
          
//          //$leadclients->get();
//       //dd(DB::getQueryLog()); exit;
      
//       }else{

//       // $statusarry   = ['Invoiced','Pay Received','Paid Out','Sold'];
//         $statusarry   = ['Paid Out'];
//         //$rstatusarry  = ['Lease Confirmed','Pay Received','MIC','Sold'];
//         //$rstatusarry   = ['Paid Out'];

//         $rstatusarry         =   ['Paid Out'];
//         $leadclients = $leadclients->whereIn('lead_client.curr_status',$statusarry);
          
//         //$leadclients  =  $leadclients->whereIn('lead_status_history.status',$statusarry);
//         //$leadclientstotal =  $leadclientstotal->whereIn('lead_status_history.status',$statusarry);
        
//         $leadclientstotal =  $leadclientstotal->whereIn('lead_client.curr_status',$statusarry);
//       }
      
//       $rstatusarry         =   ['Paid Out'];
//         $leadclients = $leadclients->whereIn('lead_client.curr_status',$statusarry);

//       $leadclientPDF  =  $leadclients->orderby('lead_client.id','DESC')->distinct()->get();  
//       $leadclients    =  $leadclients->orderby('lead_client.id','DESC')->distinct()->sortable()->get()->all(); 
//       //dd(DB::getQueryLog()); 
//       $leadclientstotal =  $leadclientstotal->orderby('lead_client.id','DESC')->distinct()->sortable()->get()->all();
        
//       $tot_net       = 0;
//       $tot_invoiced  = 0;
//       $tot_paid      = 0;
//       $outsatnginamt = 0;

//       foreach ($leadclientstotal  as $keytotal => $valuetotal) {

          
//         $arr        =   explode("~~~~",$valuetotal->status_history);
//         $paid_date  = "";
//         for ($i= 0;$i < count($arr); $i++) {
           
//             if ((strpos($arr[$i],"Pay Received") > -1)||(strpos($arr[$i],"Paid Out") > -1)) {
//                 $date       =   explode("~~",$arr[$i]);
//                 $paid_date  =   $date[0]; 
//             } 
//         }
        
//         $leadcalculatdata = Leadclientcalculation::where('leadclient_id','=',$valuetotal->id)
//                                                   ->get()
//                                                   ->first();

//         $paid_amt = $valuetotal->payment;
//         $inc      = $valuetotal->incentive;
//         if(!empty($leadcalculatdata)){
//           $leadcost=$leadcalculatdata->lead_cost;
//         }else{
//           $leadcost=0;
//         }
              
//         $user     = User::find($valuetotal->agent_id);
//         $invcostt = $user->invoicing_cost;
          
//         if(((float)$paid_amt-(float)$inc-(float)$leadcost-(float)$invcostt)>0){
//           $tot_net+=(float)$paid_amt-(float)$inc-(float)$leadcost-(float)$invcostt;
//         }
      
//         if($valuetotal->prop_id!=0 || $valuetotal->prop_id!=''){
        
//           if (trim($valuetotal->commision)==""){
//             $valuetotal->commision=0;
//           }

//           if (trim($valuetotal->bonus)==""){
//             $valuetotal->bonus=0;
//           }

//           if ($valuetotal->commision_unit=="1") {
             
//               if ($valuetotal->commision!=0 || $valuetotal->commision!=0.00) {

//                 $tot_com          =   ((float) $valuetotal->rent_amount*((float) $valuetotal->commision/100));
//                 $total            =   (float) $tot_com+(float) $valuetotal->bonus;
//                 $commision_unit   =   "%";
//               }else{
//                 $total=(float) $valuetotal->bonus;
//               }
//           }else{
//             $total=(float) $valuetotal->commision+(float) $valuetotal->bonus;
//           } 
//         }else{

//           if (trim($valuetotal->sold_amt)==""){
//             $valuetotal->sold_amt=0;
//           }

//           if (trim($valuetotal->house_comm)==""){
//             $valuetotal->house_comm=0;
//           }   

//           if (trim($valuetotal->house_bonus)==""){
//             $valuetotal->house_bonus=0;
//           }   
            
//           $total=  ((float) $valuetotal->sold_amt*((float) $valuetotal->house_comm/100))+(float) $valuetotal->house_bonus;
//         }
     
//         $tot_invoiced += $total;
//         $tot_paid += $paid_amt;
//       }  

//       $outsatnginamt              =   $tot_invoiced-$tot_net;
//       $data['totalincome']        =   $tot_net;
//       $data['outsatnginamt']      =   $outsatnginamt;
//       $data['url']                =   'searchagentincome';
//       $data['search_start_date']  =   $search_start_date;
//       $data['search_end_date']    =   $search_end_date;
//       $data['leadclients']        =   $leadclients;
//       $data['leadclientPDF']      =   $leadclientPDF;
//       $data['useraragents']       =   $useraragent;
//       $data['search_start_date']  =   $search_start_date;
//       $data['show_agent_list']    =   1;
//       $data['reseturl']           =   'agentincome';
      
//       // code by randhir kumar//
//       $data['outstanding']          =   $outstanding;
//       $data['gross']                =   $gross;
//       $data['total']                =   $totaln;
      
//         if($path == '/adminincome'){
//             $title= $usserpage = 'Admin Income';
//         }elseif($path == '/arincome'){
//             $title= $usserpage = "AR Income";
//         }else{
//             $title= $usserpage = "Agent Income";
//         }
         
//         $data['title']           =   $title;
//         //echo $_SERVER['REQUEST_URI']; exit;
//       // code by randhir end here//
      
//      // echo"<pre>";print_r($data); exit;
      
//       $footerHtml = view()->make('footer')->render();
//       //echo $footerHtml; exit;
//       $pdf = PDF::loadView('agentincome.printpdf',
//             ['data'=>$data])->setPaper('a4', 'landscape');
            
       
//       $pdf->save(storage_path('pdf/'.date('Y-m-d').'_'.$id.'_income_pdf.pdf'));

//       if($_SERVER['SERVER_NAME'] == 'development.realm.clientmgr.us' || $_SERVER['SERVER_NAME'] == 'development.flexsin.clientmgr.us' || $_SERVER['SERVER_NAME'] == 'realm.clientmgr.us'){

//           $data['link'] = PDFPATH.'../storage/pdf/'.date('Y-m-d').'_'.$id.'_income_pdf.pdf';
//       }else{
//           $data['link']= PDFPATH.'../crmrewrite/storage/pdf/'.date('Y-m-d').'_'.$id.'_income_pdf.pdf';
//       }
//       echo '{"status":true,"link":"'.$data['link'].'"}'; die();
//   }
    
/*
* @function : adminincome (to list all income for admin user.)
* @params: request data admin id
* @return : json data of all income reccords
* @modifiedby:  chintesh k.
*/    

  public function adminincome(Request $request){

    $id               =   Auth::user()->id; 
    $user_role        =   Auth::user()->user_role;
    
    $role_for         =   "ADMIN";
    $items            =   $request->items ?? 25;
    
    $sort           = isset($request->sort) ? $request->sort : 'id';
    $direction      = isset($request->direction) ? $request->direction : 'DESC';

    $leadclients      = Leadclient::join('users', 'users.id', '=', 'lead_client.agent_id')
                ->join('lead_status_history','lead_status_history.lead_id','lead_client.id')
                ->select('lead_client.*', 'users.name', 'users.last_name','lead_status_history.date as paid_date',DB::raw("(SELECT date FROM lead_status_history where status='Paid Out' and lead_id = lead_client.id order by id desc limit 1) as newdate"));

    $leadclientstotal = Leadclient::join('users', 'users.id', '=', 'lead_client.agent_id')
        ->leftjoin('lead_status_history','lead_status_history.lead_id','lead_client.id')
        ->select('lead_client.*', 'users.name', 'users.last_name',DB::raw("(SELECT date FROM lead_status_history where status='Paid Out' and lead_id = lead_client.id order by id desc limit 1) as newdate"));
                                    
                              
    $useraragent              =   '';
    $data['show_agent_list']  =    0;

    if($user_role == SUPERADMIN){
      
      $useraragent = User::where('user_role','=',ADMIN)
                    ->where('status','=',1)
                    ->orderBy('name','ASC')
                    ->get();
      $data['show_agent_list'] = 1;
    }elseif($user_role == ADMIN){
  
      $useraragent = User::where('user_role','=',ADMIN)
                            ->where('id','=',$id)
                            ->where('status','=',1)
                            ->orderBy('name','ASC')
                            ->get();
      Session::put('aragentsearch', $id);               
    }else{

      $useraragent = User::where('id','=',$id)
                          ->where('status','=',1)
                          ->orderBy('name','ASC')
                          ->get();
    }
      
    $firstuser =  $useraragent->first(); 

    if(session('aragentsearch') == ''){

      $firstuser =  $useraragent->first(); 
      if(!empty($firstuser)){   

        if($firstuser->id != ''){
          Session::put('aragentsearch', $firstuser->id);
        }
      }else{
        Session::put('aragentsearch',0);
      }
    }
         
    $agent_id           =   session('aragentsearch');
    $arUser =  User::select('id')->where('create_by_user','=',$agent_id)->get();

    //$leadclients        =   $leadclients->whereIN('agent_id',User::where('create_by_user',$agent_id)->pluck('id')->toArray());
    
    
    $leadclients  = $leadclients->whereIn('agent_id',function ($query) use ($agent_id) {
         $query->select('id')->from('users')->Where('create_by_user',$agent_id);
        //$query->select('id')->from('users')->Where('create_by_user',$agent_id);
    });
  
    
    //echo  count($arUser); exit;
    $type="company";
    $leadclientstotal   =   $leadclientstotal->whereIN('agent_id',$arUser);
    $search_start_date  =   '';
    $search_end_date    =   '';
    $client_name        =   '';
    $client_id          =   '';
    $searchform         =   $request->input('search_agent'); 

    if(!empty($searchform) && $searchform=='submit' && (!empty($request->input('client_name')) || !empty($request->input('client_id')) || !empty($request->input('search_start_date')) || !empty($request->input('search_end_date')))){
       

      $client_name      = $request->input('client_name');
      $client_id        = $request->input('client_id');
      $arr              =  explode(" ", $client_name);

      @$c_fname         = $arr[0];
      @$c_lname         = $arr[1];

      $data['client_name'] = $client_name;
 
      if($client_name !='') {
        $leadclients          =  $leadclients->where('lead_client.fname',$c_fname);
        $leadclients          =  $leadclients->where('lead_client.lname',$c_lname);
        $leadclientstotal     =  $leadclientstotal->where('lead_client.fname',$c_fname);
        $leadclientstotal     =  $leadclientstotal->where('lead_client.lname',$c_lname);
      }

      if ($client_id !='') {
        $leadclients      =  $leadclients->where('lead_client.id',$client_id);    
        $leadclientstotal =  $leadclientstotal->where('lead_client.id',$client_id);
      }
     
      $statusarry         =   ['Paid Out','14'];
      

      $search_start_date  = setdbdate($request->input('search_start_date'));
      $search_end_date    = setdbdate($request->input('search_end_date'));
      $client_name        = $request->input('client_name');
      $client_id          = $request->input('client_id');
      $arr                =  explode(" ", $client_name);
        
      @$c_fname           = $arr[0];
      @$c_lname           = $arr[1];
      $data['client_name'] = $client_name;
        
         
      if($search_start_date != '' && $search_end_date != ''){

       $type="company";
        $leadclients =$leadclients->whereIn('lead_client.curr_status',$statusarry);
        $leadclients =$leadclients->whereBetween('lead_status_history.date',[$search_start_date,$search_end_date]);
        
        $leadclientstotal =  $leadclientstotal->whereIn('lead_client.curr_status',$statusarry);
        $leadclientstotal =  $leadclientstotal->whereBetween('lead_client.newpaid_date',[$search_start_date,$search_end_date]);
      }elseif ($client_name !='') {
        $leadclientstotal =  $leadclientstotal->whereIn('lead_client.curr_status',$statusarry);
             
        $leadclients =  $leadclients->where('lead_client.fname','LIKE',$c_fname);
        $leadclients =  $leadclients->where('lead_client.lname','LIKE',$c_lname);

      }elseif ($client_id !='') {

        $leadclientstotal =  $leadclientstotal->whereIn('lead_client.curr_status',$statusarry);
        $leadclients      =  $leadclients->where('lead_client.id',$client_id);

      }else{
       
         $leadclients = $leadclients->whereIn('lead_client.curr_status',$statusarry);
         
        $leadclients    = $leadclients->whereDate('lead_status_history.date','>=',$search_start_date)->whereDate('lead_status_history.date','<=',$search_end_date);
        $leadclientstotal =  $leadclientstotal->whereIn('lead_client.curr_status',$statusarry);
        
        $leadclientstotal =  $leadclientstotal->whereBetween('lead_status_history.date',[$search_start_date,$search_end_date]);

        $leadclients      =  $leadclients->where('lead_client.fname','LIKE',$c_fname);
        $leadclients      =  $leadclients->where('lead_client.lname','LIKE',$c_lname);
        $leadclients      =  $leadclients->where('lead_client.id',$client_id);
         //echo "<pre>"; print_r($_POST); exit;
      }
      
    }else{
        $statusarry  = ['Sold','Sale Closing','Invoiced','Pay Received','MIC','Paid Out','9','12','13','14','11'];
       $leadclients = $leadclients->whereIn('lead_client.curr_status',$statusarry);
       $leadclientstotal   =  $leadclientstotal->whereIn('lead_client.curr_status',$statusarry);
    }
    
    //$statusarry         = ['Sold','Invoiced','Pay Received','MIC','Paid Out'];
    $leadclientPDF  =  $leadclients;
    
    
    if($search_start_date != '' && $search_end_date != ''){
            
            $leadclients    =  $leadclients->where('transaction_type_id','!=','0');
            $leadclientstotal    =  $leadclientstotal->where('transaction_type_id','!=','0');
    }
    
     $leadclientsall    =  $leadclients->where('curr_status','!=','Write Off')->where('curr_status','!=','15')->groupBy('lead_client.id');
     
      if($search_start_date != '' && $search_end_date != ''){
            
            
            //$leadclientsall  =  $leadclientsall->having("newdate BETWEEN '$search_start_date' and '$search_end_date'");
            
                            
        } 
        $leadclientsall    =  $leadclients->get(); 
      
                                  
        $leadclientPDF  =  $leadclientPDF->orderby('lead_client.'.$sort ,$direction)
                                      ->groupBy('lead_client.id');
                                      
        
        $leadclientPDF  =  $leadclientPDF->paginate($items);
        
       
        $leadclients    =  $leadclients->orderby('lead_client.'.$sort ,$direction)->groupBy('lead_client.id');
                        
        if($search_start_date != '' && $search_end_date != ''){
            
              //$leadclients    =  $leadclients->havingRaw("newdate BETWEEN ? and ?", [$search_start_date,$search_end_date]);
            
                            
        }
           // DB::enableQueryLog();
           
        $leadclients    =  $leadclients->paginate($items); 
                                
     //dd(DB::getQueryLog()); exit;
    
        $leadclientstotal =  $leadclientstotal->orderby('lead_client.'.$sort ,$direction)->groupBy('lead_client.id');
        
        
        $leadclientstotal =  $leadclientstotal->get();
    
    //echo "<pre>";print_r(json_decode(json_encode($leadclients), true)); exit;
    $tot_net       = 0;
    $tot_invoiced  = 0;
    $tot_paid      = 0;
    $outsatnginamt = 0;
   
    Session::put('role_for',$role_for);
    
    
    $data['income_detail'] = income_details('admin',session('aragentsearch'))['admin_income'];
    
    
    $incomes = get_income_calculation_admin($leadclientstotal,$type);
    
   //echo "<pre>"; print_r($incomes); exit;
   
    $data['totalincome']        = $tot_net;
    $data['outsatnginamt']      = $outsatnginamt;
    $data['url']                = 'searchadincome';
    $data['search_start_date']  = $search_start_date;
    $data['search_end_date']    = $search_end_date;
    $data['leadclients']        = $leadclients;
    $data['useraragents']       = $useraragent;
    $data['search_start_date']  = $search_start_date;
    $data['search_end_date']    = $search_end_date;
    $data['reseturl']           = 'adminincome';
    $data['leadclientPDF']      = $leadclientPDF;
    $data['client_name']        = $client_name;
    $data['client_id']          = $client_id;
    $data['link']               =  PDFPATH.'storage/pdf/'.date('Y-m-d').'_'.$id.'_income_pdf.pdf';
    $data['datasall']          = $leadclients; //$leadclientsall;
    
    $data['totalincome']        = $incomes['company_income'];
    $data['total_invoice']      = $incomes['rescommission'];
    $data['gross_pay']          = $incomes['gross_revenue'];
    $data['outsatnginamt']      = $incomes['outstanding'];
    $data['agent_income']       = $incomes['agent_income']; 
    $data['company_income']     = $incomes['company_income'];
    
     //echo "<pre>"; print_r(($data['datasall'])); exit;
     
    return view('agentincome.income_new',['data'=>$data])->withMembers($leadclients)->withItems($items);
  }
  

 public function adminincome_new(Request $request){

    $id               =   Auth::user()->id; 
    $user_role        =   Auth::user()->user_role;
    
    $role_for         =   "ADMIN";
    $items            =   $request->items ?? 25;
    
    $sort           = isset($request->sort) ? $request->sort : 'id';
    $direction      = isset($request->direction) ? $request->direction : 'DESC';
    
    //DB::enableQueryLog();
    
                                   
    $useraragent              =   '';
    $data['show_agent_list']  =    0;

    if($user_role == SUPERADMIN){
      
      $useraragent = User::where('user_role','=',ADMIN)
                    ->where('status','=',1)
                    ->orderBy('name','ASC')
                    ->get();
      $data['show_agent_list'] = 1;
    }elseif($user_role == ADMIN){
  
      $useraragent = User::where('user_role','=',ADMIN)
                            ->where('id','=',$id)
                            ->where('status','=',1)
                            ->orderBy('name','ASC')
                            ->get();
      Session::put('aragentsearch', $id);               
    }else{

      $useraragent = User::where('id','=',$id)
                          ->where('status','=',1)
                          ->orderBy('name','ASC')
                          ->get();
    }
      
    $firstuser =  $useraragent->first(); 

    if(session('aragentsearch') == ''){

      $firstuser =  $useraragent->first(); 
      if(!empty($firstuser)){   

        if($firstuser->id != ''){
          Session::put('aragentsearch', $firstuser->id);
        }
      }else{
        Session::put('aragentsearch',0);
      }
    }
     
    
    $agent_id           =   session('aragentsearch');
    

     $statusarry         = ['Sale Closing','Sold','Invoiced','Pay Received','MIC','Paid Out','9','12','13','14','11'];
     
     $statses= implode(',',$statusarry);
     
     
      $leadclientstotal = DB::select("select `lead_client`.id from `lead_client` inner join `users` on `users`.`id` = `lead_client`.`agent_id` inner join `lead_status_history` on `lead_status_history`.`lead_id` = `lead_client`.`id` where lead_client.agent_id in(select id from users where create_by_user='$agent_id') and lead_client.curr_status IN('Sold','Sale Closing','Invoiced','Pay Received','MIC','Paid Out','9','12','13','14','11') GROUP BY lead_client.id");
      
    
    $leadclients = DB::select("select `lead_client`.*, `users`.`name`, `users`.`last_name`, `lead_status_history`.`date` as `paid_date` from `lead_client` inner join `users` on `users`.`id` = `lead_client`.`agent_id` inner join `lead_status_history` on `lead_status_history`.`lead_id` = `lead_client`.`id` where lead_client.agent_id in(select id from users where create_by_user='$agent_id') and lead_client.curr_status IN('Sold','Sale Closing','Invoiced','Pay Received','MIC','Paid Out','9','12','13','14','11') GROUP BY lead_client.id limit 25");
  
    //$resp = json_decode(json_encode($sql), true);
    
    //echo "<pre>"; print_r($resp); exit;
    
    //$leadclientstotal   =   $leadclientstotal->whereIN('agent_id',$arUser);
    $search_start_date  =   '';
    $search_end_date    =   '';
    $client_name        =   '';
    $client_id          =   '';
    $searchform         =   $request->input('search_agent'); 
    
    
    if(!empty($searchform) && $searchform=='submit' && (!empty($request->input('client_name')) || !empty($request->input('client_id')) || !empty($request->input('search_start_date')) || !empty($request->input('search_end_date')))){
       

    }
   
       
    //echo "<pre>";print_r($leadclientsall); exit;
    $tot_net       = 0;
    $tot_invoiced  = 0;
    $tot_paid      = 0;
    $outsatnginamt = 0;
   
    Session::put('role_for',$role_for);
    
    
    $data['income_detail'] = income_details('admin',session('aragentsearch'))['admin_income']; 
    $data['totalincome']        = $tot_net;
    $data['outsatnginamt']      = $outsatnginamt;
    $data['url']                = 'searchadincome';
    $data['search_start_date']  = $search_start_date;
    $data['search_end_date']    = $search_end_date;
    $data['leadclients']        = $leadclients;
    $data['useraragents']       = $useraragent;
    $data['search_start_date']  = $search_start_date;
    $data['search_end_date']    = $search_end_date;
    $data['reseturl']           = 'adminincome';
    $data['leadclientPDF']      = $leadclients;
    $data['client_name']        = $client_name;
    $data['client_id']          = $client_id;
    $data['link']               =  PDFPATH.'storage/pdf/'.date('Y-m-d').'_'.$id.'_income_pdf.pdf';
    $data['datasall']          = $leadclients; //$leadclientsall;
    
    // echo "<pre>"; print_r(($data['datasall'])); exit;
     
    return view('agentincome.income_new',['data'=>$data])->withMembers($leadclients)->withItems($items);
  }
  
  
    public function income_list(){
      
        $id               =   Auth::user()->id; 
        $user_role        =   Auth::user()->user_role;
        $company_id       =   Auth::user()->company;
        
        $role_for         =   "ADMIN";
        
        $useraragent              =   '';
        $data['show_agent_list']  =    0;

        if($user_role == SUPERADMIN){
          
            $useraragent = User::where('user_role','=',ADMIN)
                        ->where('status','=',1)
                        ->orderBy('name','ASC')
                        ->get();
            $data['show_agent_list'] = 1;
        }elseif($user_role == ADMIN){
      
            $useraragent = User::where('user_role','=',ADMIN)
                                ->where('id','=',$id)
                                //->where('status','=',1)
                                ->orderBy('name','ASC')
                                ->get();
            Session::put('aragentsearch', $id);               
        }else{
    
            $useraragent = User::where('id','=',$id)
                      ->where('status','=',1)
                      ->orderBy('name','ASC')
                      ->get();
        }
          
        $firstuser =  $useraragent->first(); 
    
        if(session('aragentsearch') == ''){
    
          $firstuser =  $useraragent->first(); 
          if(!empty($firstuser)){   
    
            if($firstuser->id != ''){
              Session::put('aragentsearch', $firstuser->id);
            }
          }else{
            Session::put('aragentsearch',0);
          }
        }
     
        
        $agent_id           =   session('aragentsearch');
        
        $arUser             =   User::select('id')->where('create_by_user','=',$agent_id)->orWhere('company',Auth::user()->company)->get();
        
        //echo $arUser; exit;
      
        $params = $_REQUEST;
        
        $columns = array('lead_client.id','lead_client.fname','','lead_client.payment','','lead_client.curr_status','','','','lead_status_history.date');
        
        
        // $order_column = 'lead_client.id';
            
            //$orderby = "DESC";
        
    //print_r($params); exit;
    
    
        if(!empty($columns[$params['order'][0]['column']])){
            
            $order_column = $columns[$params['order'][0]['column']];
            
            $orderby = $params['order'][0]['dir'];
            
        }else{
            $order_column = 'lead_client.id';
            
            $orderby = "DESC";
        }
         
      //echo " ORDER BY ". $columns[$params['order'][0]['column']]."   ".$params['order'][0]['dir']; exit;
      
         //echo $agent_id; exit;
        $user_role          = Auth::user()->user_role;
        $user               = Auth::user()->id;
        $company = Auth::user()->company;
        
        if(!empty($params['search_start_date']) && !empty($params['search_end_date'])){
          
           // $condition = " lead_client.agent_id in(select id from users where create_by_user='$agent_id') and lead_client.curr_status IN('Paid Out','14') ";
           
            $condition = " lead_client.agent_id in(select id from users where create_by_user='$agent_id' and company = '$company_id')  ";
        }else{
          
            $condition = " lead_client.agent_id in(select id from users where create_by_user='$agent_id' and company = '$company_id') ";
        }
        
        $params['income'] = (!empty($params['income'])?$params['income']:'company');
        //echo $params['income']; exit;
        
        if(!empty($params['income']) && $params['income']=='outstand'){
            
            $status ="'Lease Confirmed','Invoiced','Sold','Sale Closing','10','12','9','Invoice 30','Invoice 60','Invoice 90','Past Due','Payment OTW','18','19','20','21','22'";
                
            $condition1 = $condition."and lead_client.curr_status IN($status) and lead_client.payment=0 and invoiced_total!=0";
                 
            $condition .=" and lead_client.curr_status IN($status) and lead_client.payment=0 and invoiced_total!=0";
              
        }
            
        if(!empty($params['income']) && $params['income']=='gross'){
            
            $status ="'Pay Received','Paid Out','13','14'";
            
            if(!empty($params['search_start_date']) && !empty($params['search_end_date'])){
                
                $status ="'Paid Out','14'";
                
            }
            
            
            $condition1 = $condition." and lead_client.curr_status IN($status) and lead_client.payment!=0";
                
            $condition .=" and lead_client.curr_status IN($status) and lead_client.payment!=0";
            
            
        }
            
        
        if(!empty($params['income']) && $params['income']=='agent'){
                
            $status ="'Pay Received','Paid Out','13','14'";
                
                
            if(!empty($params['search_start_date']) && !empty($params['search_end_date'])){
                
                $status ="'Paid Out','14'";
                
            }
                
           // $condition .=" and lead_client.curr_status IN('Pay Received','Paid Out','13','14') and lead_client.agent_paid!=0";
           
            
            $condition1 = $condition." and lead_client.curr_status IN($status";
            
             $condition .=" and lead_client.curr_status IN($status) and lead_client.agent_paid!=0";
        }
            
        if(!empty($params['income']) && $params['income']=='company'){
            
            if(!empty($params['income_url']) && $params['income_url']='adminincome'){
               
                $status ="'Pay Received','Paid Out','13','14'"; 
            }else{
              $status ="'Pay Received','Paid Out','13','14'"; 
            }
            
                
            if(!empty($params['search_start_date']) && !empty($params['search_end_date'])){
                
                $status ="'Paid Out','14'";
                
            }
                
            //$condition .=" and lead_client.curr_status IN('Pay Received','Paid Out','13','14') and lead_client.deducation!=0";
            
            $condition1 = $condition." and lead_client.curr_status IN($status)";
              
            $condition .=" and lead_client.curr_status IN($status) and lead_client.transaction_type_id!='0'";
        }
        
        
        if(!empty($params['income']) && $params['income']=='all'){
            
            if(!empty($params['income_url']) && $params['income_url']='adminincome'){
                $status ="'Lease Confirmed','Invoiced','Sold','Sale Closing','Pay Received','Paid Out','10','12','9','13','14','Invoice 30','Invoice 60','Invoice 90','Past Due','Payment OTW','18','19','20','21','22'";
            }else{
              $status ="'Pay Received','Paid Out','13','14'"; 
            }
            
            if(!empty($params['search_start_date']) && !empty($params['search_end_date'])){
                
                $status ="'Paid Out','14'";
            }

            if(!empty($params['invoice_start_date']) && !empty($params['invoice_end_date'])){
                
                $status ="'Invoiced','12','Invoice 30','Invoice 60','Invoice 90','Past Due','Payment OTW','18','19','20','21','22'";
            }
            
            $condition1 = $condition." and lead_client.curr_status IN($status)";
            if(!empty($params['invoice_start_date']) && !empty($params['invoice_end_date'])){
              $condition .=" and lead_status_history.status IN($status) ";
            } else {
              $condition .=" and lead_client.curr_status IN($status) ";
            }

        }
        
        if(!empty($params['client_name'])){
            $name = explode(' ',$params['client_name']);
            $fname = $name[0];
            $lname = $name[1];
            
            if(!empty($fname)){
                $condition .=" and lead_client.fname Like '$fname%'";
            }
            
            if(!empty($lname)){
                $condition .=" and lead_client.lname Like '$lname%'";
            } 
        }
        
        if(!empty($params['client_id'])){
            $clintid = $params['client_id'];
            $condition .=" and lead_client.id = '$clintid'";
        }
           
        if(!empty($params['search_start_date']) && !empty($params['search_end_date'])){
            
            $search_start_date  = setdbdate($params['search_start_date']);
            $search_end_date    = setdbdate($params['search_end_date']);
            
            //if(!empty($params['income']) && ($params['income']=='outstand' ||  $params['income'] =='gross')){
                $condition .=" and lead_status_history.date BETWEEN '$search_start_date' AND '$search_end_date'";
           // }
        }

        if(!empty($params['invoice_start_date']) && !empty($params['invoice_end_date'])){
            
            $search_start_date  = setdbdate($params['invoice_start_date']);
            $search_end_date    = setdbdate($params['invoice_end_date']);
            
            //if(!empty($params['income']) && ($params['income']=='outstand' ||  $params['income'] =='gross')){
                $condition .=" and lead_status_history.date BETWEEN '$search_start_date' AND '$search_end_date'";
           // }
        }
        
        //echo "<pre>"; print_r($params); exit;
       
        $limit="order by $order_column $orderby LIMIT ".$params['start']." ,".$params['length']." ";
        if(!empty($params['invoice_start_date']) && !empty($params['invoice_end_date']))
        {
          $condition .= " GROUP BY lead_client.id,lead_client.fname,lead_client.lname,lead_client.payment,lead_client.paid_date,lead_client.paid_out,lead_client.incentive,users.name,users.last_name,lead_client.invoiced_total,lead_client.agent_paid,lead_client.sendto,lead_client.curr_status,lead_client.status_history  ";
        } else {
          $condition .=" GROUP BY lead_client.id ";
        }
        
        
         if(!empty($params['search_start_date']) && !empty($params['search_end_date'])){
             
              if(!empty($params['income']) && ($params['income']=='company' ||  $params['income'] =='agent')){
                  
                  $condition .=" having newdate BETWEEN '$search_start_date' and '$search_end_date'";
              }
              
         }
        
        
       if(!empty($params['invoice_start_date']) && !empty($params['invoice_end_date']))
       {
        // SELECT lead_client.id,lead_client.fname,lead_client.lname,lead_client.payment,lead_client.paid_date AS lpaid_date,lead_client.paid_out AS lpaidout_date,lead_client.paid_out AS paid_out,lead_client.incentive,users.name,users.last_name,lead_client.invoiced_total,lead_client.agent_paid,lead_client.paid_date AS lpaid_date,lead_client.paid_out AS lpaidout_date,lead_client.sendto,lead_client.curr_status,MAX(lead_status_history.date) AS paid_date,lead_client.status_history,(SELECT MAX(date) FROM lead_status_history WHERE status IN ('Invoiced', '12', 'Invoice 30', 'Invoice 60', 'Invoice 90', 'Past Due', 'Payment OTW', '18', '19', '20', '21', '22') AND lead_id=lead_client.id) AS newdate FROM lead_client INNER JOIN users ON users.id=lead_client.agent_id INNER JOIN lead_status_history ON lead_status_history.lead_id=lead_client.id WHERE lead_client.agent_id IN(SELECT id FROM users WHERE create_by_user='36' AND company='1') AND lead_status_history.status IN ('Invoiced', '12', 'Invoice 30', 'Invoice 60', 'Invoice 90', 'Past Due', 'Payment OTW', '18', '19', '20', '21', '22') AND lead_status_history.date BETWEEN '2023-05-01' AND '2023-12-05' GROUP BY lead_client.id,lead_client.fname,lead_client.lname,lead_client.payment,lead_client.paid_date,lead_client.paid_out,lead_client.incentive,users.name,users.last_name,lead_client.invoiced_total,lead_client.agent_paid,lead_client.sendto,lead_client.curr_status,lead_client.status_history ORDER BY lead_client.id DESC LIMIT 0,25;

          $resultscount= DB::select("select lead_client.id,lead_client.fname,lead_client.lname,lead_client.payment,lead_client.paid_date AS lpaid_date,lead_client.paid_out AS lpaidout_date,lead_client.paid_out AS paid_out,lead_client.incentive,users.name,users.last_name,lead_client.invoiced_total,lead_client.agent_paid,lead_client.paid_date AS lpaid_date,lead_client.paid_out AS lpaidout_date,lead_client.sendto,lead_client.curr_status,MAX(lead_status_history.date) AS paid_date,lead_client.status_history,(SELECT MAX(date) FROM lead_status_history WHERE status IN ('Invoiced', '12', 'Invoice 30', 'Invoice 60', 'Invoice 90', 'Past Due', 'Payment OTW', '18', '19', '20', '21', '22') AND lead_id=lead_client.id) AS newdate FROM lead_client INNER JOIN users ON users.id=lead_client.agent_id INNER JOIN lead_status_history ON lead_status_history.lead_id=lead_client.id where $condition");
        
        //echo "<pre>";print_r(json_decode(json_encode(array_column($resultscount,'id')), true)); exit;
        
        $condition .= $limit;
        
        // DB::enableQueryLog();
        $results = DB::select("select lead_client.id,lead_client.fname,lead_client.lname,lead_client.payment,lead_client.paid_date AS lpaid_date,lead_client.paid_out AS lpaidout_date,lead_client.paid_out AS paid_out,lead_client.incentive,users.name,users.last_name,lead_client.invoiced_total,lead_client.agent_paid,lead_client.paid_date AS lpaid_date,lead_client.paid_out AS lpaidout_date,lead_client.sendto,lead_client.curr_status,MAX(lead_status_history.date) AS paid_date,lead_client.status_history,(SELECT MAX(date) FROM lead_status_history WHERE status IN ('Invoiced', '12', 'Invoice 30', 'Invoice 60', 'Invoice 90', 'Past Due', 'Payment OTW', '18', '19', '20', '21', '22') AND lead_id=lead_client.id) AS newdate FROM lead_client INNER JOIN users ON users.id=lead_client.agent_id INNER JOIN lead_status_history ON lead_status_history.lead_id=lead_client.id where $condition ");
        // dd(DB::getQueryLog());
       } else {
          $resultscount= DB::select("select `lead_client`.id,lead_client.source_id,lead_client.payment,lead_client.transaction_type_id,users.manage_distributor,lead_client.invoiced_total,agent_paid,lead_client.curr_status,lead_client.deducation,lead_client.incentive,`lead_status_history`.`date` as `paid_date`,`users`.`name` as fname, `users`.`last_name` as lname,(select date from lead_status_history where status='Paid Out' and lead_id=lead_client.id order by id desc limit 1)as newdate from `lead_client` inner join `users` on `users`.`id` = `lead_client`.`agent_id` inner join `lead_status_history` on `lead_status_history`.`lead_id` = `lead_client`.`id` where $condition");
        
        //echo "<pre>";print_r(json_decode(json_encode(array_column($resultscount,'id')), true)); exit;
        
        $condition .= $limit;
        
        // DB::enableQueryLog();
        $results = DB::select("select lead_client.id,lead_client.fname,lead_client.lname,lead_client.payment,lead_client.paid_date as lpaid_date,lead_client.paid_out as lpaidout_date,lead_client.paid_out,lead_client.incentive, `users`.`name`, `users`.`last_name`,lead_client.invoiced_total, lead_client.agent_paid,lead_client.paid_date as lpaid_date,lead_client.paid_out as lpaidout_date, lead_client.sendto,lead_client.curr_status,`users`.`name`, `users`.`last_name`, `lead_status_history`.`date` as `paid_date`,lead_client.status_history,(select date from lead_status_history where status='Paid Out' and lead_id=lead_client.id order by id desc limit 1)as newdate from `lead_client` inner join `users` on `users`.`id` = `lead_client`.`agent_id` inner join `lead_status_history` on `lead_status_history`.`lead_id` = `lead_client`.`id` where $condition ");
       }
        
        // dd(DB::getQueryLog());
       // $outstanding = $this->paid_amount("lead_client.id");
        
      // echo "select lead_client.id,lead_client.fname,lead_client.lname,lead_client.payment,lead_client.paid_date as lpaid_date,lead_client.paid_out as lpaidout_date,lead_client.paid_out,lead_client.incentive, `users`.`name`, `users`.`last_name`,lead_client.invoiced_total, lead_client.agent_paid,lead_client.paid_date as lpaid_date,lead_client.paid_out as lpaidout_date, lead_client.sendto,lead_client.curr_status,`users`.`name`, `users`.`last_name`, `lead_status_history`.`date` as `paid_date`,lead_client.status_history,(select date from lead_status_history where status='Paid Out' and lead_id=lead_client.id order by id desc limit 1)as newdate from `lead_client` inner join `users` on `users`.`id` = `lead_client`.`agent_id` inner join `lead_status_history` on `lead_status_history`.`lead_id` = `lead_client`.`id` where $condition ";
        
        //exit;
        
        $results = json_decode(json_encode($results), true);
        
       // echo "<pre>"; print_r($results); exit;
        //echo $results; exit;
        $datas = array();
        //echo $params['income']; exit;
        $income_url = (!empty($params['income_url'])?$params['income_url']:'');
        
    
       $income_detail = get_income_calculation_admin(json_decode(json_encode($resultscount), true),$params['income'],$income_url);
       
      
        //echo "<pre>";print_r($response['link']); exit;
       
        if(count($results)>0){
            
            foreach ($results as $row) {

                $nestedData = array();
                
                 $client='<a href="'.url('/').'/editclientlead/'.$row['id'].'/1" target="_blank">#'.$row['id'].'</a>';
                
                $name =  ucfirst($row['fname']).' '.ucfirst($row['lname']);
                
                
                 $paid_out = $row['lpaidout_date'];
                if($paid_out=='01/01/1970' || $paid_out=='01-01-1970'){
                    $paid_out='';
                }
                
                //$leadcalculation        = AgentincomeController::getcalculation($row['id']);
                
                //$paid_date = (!empty($row['paid_date'])?$row['paid_date']:'');
                
                // deduction//
                $lead_calculate = lead_calculate($row['id']);
                
                $deduction = $this->deduction($lead_calculate,$row['id']);
                    
                $reseturl = $params['reseturl'];
                    
               // $deduction = $this->deduction($row->id);
                
                    
            if(is_numeric($row['curr_status'])){
                $status = set_status_name($row['curr_status']);
            }else{
                $status = set_status_name($row['curr_status']); 
            }
            
            $dates =  payout_payreceived_date($row['status_history']);
            
           //echo "<pre>"; print_r($dates);
            
            $company_income = $this->aroradmin($lead_calculate,$reseturl,$row['id']);
            
                $nestedData['DT_RowId'] = "row_".$row['sendto'];
                $nestedData[] = $client;
                $nestedData[] = $name;
                // $nestedData[] = '$'.number_format($row['invoiced_total'],2);
                $nestedData[] = ($row['invoiced_total'] != 0) ? '$'.number_format($row['invoiced_total'],2) : (($lead_calculate['invoiced_total'] != 0) ? '$'.number_format($lead_calculate['invoiced_total'],2) : '$0.00');
                $nestedData[] = '$'.number_format($row['payment'],2);
                $nestedData[] = (!empty($dates['paid_rec'])?$dates['paid_rec']:'');
                
                $nestedData[] = (!empty($status)?$status:'');
                
                $nestedData[] = $deduction['string'];
                $nestedData[] = '$'.number_format($lead_calculate['agent_paid'],2);
                $nestedData[] = $company_income['string'];
                $nestedData[] = (!empty($dates['paid_out'])?$dates['paid_out']:'');
                $nestedData[] = $row['name']." ".$row['last_name'];
                
                //$this->array_sort_by_column($results, 'id',SORT_DESC);
                
                $datas[] = $nestedData; 
            }
            $income_detail['condition'] = $condition;
            $income_detail['link'] = '';
            
            
            $json_data = array(
                "draw"            => intval($params['draw'] ),   
                "recordsTotal"    => intval(count($resultscount)),  
                "recordsFiltered" => intval(count($resultscount)),
                "data"            => $datas,
                "total_data"      => $income_detail
                );
            return response()->json($json_data); exit;
          
        }else{
            $json_data = array(
            "draw"            => intval($params['draw'] ),   
            "recordsTotal"    => intval(count($resultscount)),  
            "recordsFiltered" => intval(count($resultscount)),
            "data"            => $datas,
            "total_data"      => $income_detail
            );
            
         return response()->json($json_data); exit;
        }
        
    }
    
    public function payout_payreceived($status_history){
        $arr              =   explode("~~~~",$status_history);
        
        $paid_rec = "";
        $paid_out = "";

        for ($i=0;$i<count($arr);$i++) {
         
            if((strpos($arr[$i],"Pay Received") > -1) || (strpos($arr[$i],"13") > -1)) {
                $date       =   explode("~~",$arr[$i]);
                if($date[0]){
                  $newdate    =  explode("-",$date[0]);
                  if (str_contains($date[0], '-')) { 
                    $newdate    =  explode("-",$date[0]);
                  }else{
                    $newdate    =  explode("/",$date[0]);
                  }
                  if(!empty($newdate[1]) && !empty($newdate[2]) && !empty($newdate[0])){
    
                    $paid_rec  =   date('m/d/Y',strtotime($newdate[1].'-'.$newdate[0].'-'.$newdate[2]));
                  }
                }
            }
            
            
            if((strpos($arr[$i],"Paid Out") > -1) || (strpos($arr[$i],"14") > -1) ) {
                $date       =   explode("~~",$arr[$i]);
    
                if($date[0]){
                  $newdate    =  explode("-",$date[0]);
                  if (str_contains($date[0], '-')) { 
                    $newdate    =  explode("-",$date[0]);
                  }else{
                    $newdate    =  explode("/",$date[0]);
                  }
                  if(!empty($newdate[1]) && !empty($newdate[2]) && !empty($newdate[0])){
    
                    $paid_out   =  date('m/d/Y',strtotime($newdate[1].'-'.$newdate[0].'-'.$newdate[2]));
                  }
                }
            }
        }
      
        $data['paid_rec'] = $paid_rec;
        $data['paid_out'] = $paid_out;
        return $data;
        
    }

    public function download_income_pdf(Request $req){
        
        $id               =   Auth::user()->id; 
        $user_role        =   Auth::user()->user_role;
        
        $role_for         =   "ADMIN";
        
        
        //print_r($_REQUEST); exit;
        $useraragent              =   '';
        $data['show_agent_list']  =    0;

        if($user_role == SUPERADMIN){
          
            $useraragent = User::where('user_role','=',ADMIN)
                        ->where('status','=',1)
                        ->orderBy('name','ASC')
                        ->get();
            $data['show_agent_list'] = 1;
        }elseif($user_role == ADMIN){
      
            $useraragent = User::where('user_role','=',ADMIN)
                                ->where('id','=',$id)
                                ->where('status','=',1)
                                ->orderBy('name','ASC')
                                ->get();
            Session::put('aragentsearch', $id);               
        }else{
    
            $useraragent = User::where('id','=',$id)
                      ->where('status','=',1)
                      ->orderBy('name','ASC')
                      ->get();
        }
          
        $firstuser =  $useraragent->first(); 
    
        if(session('aragentsearch') == ''){
    
          $firstuser =  $useraragent->first(); 
          if(!empty($firstuser)){   
    
            if($firstuser->id != ''){
              Session::put('aragentsearch', $firstuser->id);
            }
          }else{
            Session::put('aragentsearch',0);
          }
        }
     
        
        $agent_id           =   session('aragentsearch');
        
        //echo $agent_id; exit;
      
        $params = $_REQUEST;
        
        $columns = array('lead_client.id','lead_client.fname','','lead_client.payment','','lead_client.curr_status');
        
            $order_column = 'lead_client.id';
            
            $orderby = "DESC";
    
        //if(isset($params['order'][0]['column'])){
            
           // $order_column = $columns[$params['order'][0]['column']];
            
           // $orderby = $params['order'][0]['dir'];
            
        //}else{
         //   $order_column = 'lead_client.id';
            
          //  $orderby = "DESC";
       //}
         
         
        $user_role          = Auth::user()->user_role;
        $user               = Auth::user()->id;
        $company_id = Auth::user()->company;
        
        
        if(!empty($params['start_date']) && !empty($params['end_date'])){
          
           
            $condition = " lead_client.agent_id in(select id from users where create_by_user='$agent_id' or company = '$company_id')  ";
        }else{
          
            $condition = " lead_client.agent_id in(select id from users where create_by_user='$agent_id' or company = '$company_id') ";
        }
        
        if(!empty($params['income']) && $params['income']=='outstand'){
                
           
            $condition .=" and lead_client.curr_status IN('Lease Confirmed','Invoiced','Sold','Sale Closing','10','12','9') and lead_client.payment=0 and invoiced_total!=0";
            
        }
            
        if(!empty($params['income']) && $params['income']=='gross'){
            
            $status ="'Pay Received','Paid Out','13','14'";
            
            if(!empty($params['start_date']) && !empty($params['end_date'])){
                
                $status ="'Paid Out','14'";
                
            }
            
            $condition1 = $condition." and lead_client.curr_status IN($status) and lead_client.payment!=0";
                
            $condition .=" and lead_client.curr_status IN($status) and lead_client.payment!=0";
            
            
        }
            
        
        if(!empty($params['income']) && $params['income']=='agent'){
            
            $status ="'Pay Received','Paid Out','13','14'";
            
            if(!empty($params['start_date']) && !empty($params['end_date'])){
                
                $status ="'Paid Out','14'";
                
            }
            
                
            $condition .=" and lead_client.curr_status IN($status) and lead_client.agent_paid!=0";
        }
            
        if(!empty($params['income']) && $params['income']=='company'){
                
            if(!empty($params['income_url']) && $params['income_url']='adminincome'){
                 //$status ="'Lease Confirmed','Invoiced','Sold','Sale Closing','Pay Received','Paid Out','10','12','9','13','14'";
                $status ="'Pay Received','Paid Out','13','14'"; 
            }else{
              $status ="'Pay Received','Paid Out','13','14'"; 
            }
            
            if(!empty($params['start_date']) && !empty($params['end_date'])){
                
                $status ="'Paid Out','14'";
                
            }
            
           $condition .=" and lead_client.curr_status IN($status) and lead_client.transaction_type_id!='0'";
        }
        
        
         if(!empty($params['income']) && $params['income']=='all'){
            
            if(!empty($params['income_url']) && $params['income_url']='adminincome'){
                $status ="'Lease Confirmed','Invoiced','Sold','Sale Closing','Pay Received','Paid Out','10','12','9','13','14'";
            }else{
              $status ="'Pay Received','Paid Out','13','14'"; 
            }
            
            if(!empty($params['start_date']) && !empty($params['end_date'])){
                
                $status ="'Paid Out','14'";
            }
            
            $condition1 = $condition." and lead_client.curr_status IN($status)";
              
            $condition .=" and lead_client.curr_status IN($status) ";
        }
        
        if(!empty($params['client_name'])){
            $name = explode(' ',$params['client_name']);
            $fname = $name[0];
            $lname = $name[1];
            
            if(!empty($fname)){
                $condition .=" and lead_client.fname Like '$fname%'";
            }
            
            if(!empty($lname)){
                $condition .=" and lead_client.lname Like '$lname%'";
            } 
        }
        
        if(!empty($params['client_id'])){
            $clintid = $params['client_id'];
            $condition .=" and lead_client.id = '$clintid'";
        }
           
        if(!empty($params['start_date']) && !empty($params['end_date'])){
            
           $search_start_date  = setdbdate($params['start_date']);
            $search_end_date    = setdbdate($params['end_date']);
            
             if(!empty($params['income']) && ($params['income']!=='company' ||  $params['income'] !=='agent')){
                $condition .=" and lead_status_history.date BETWEEN '$search_start_date' AND '$search_end_date'";
            }
        }
        
        //echo "<pre>"; print_r($params); exit;
       
       
       $condition .=" GROUP BY lead_client.id ";
        

        if(!empty($params['start_date']) && !empty($params['end_date'])){
             
             if(!empty($params['income']) && ($params['income']=='company' ||  $params['income'] =='agent')){
                  
                  $condition .=" having newdate BETWEEN '$search_start_date' and '$search_end_date'";
              }
         }
         
         $condition .=" order by ".$order_column." ".$orderby;
         
       //echo $condition; exit;
       
        $resultscount= DB::select("select `lead_client`.id,lead_client.payment,lead_client.invoiced_total,lead_client.transaction_type_id,agent_paid,lead_client.curr_status,lead_client.deducation,lead_client.incentive,`lead_status_history`.`date` as `paid_date`,`users`.`name` as fname, `users`.`last_name` as lname,CONCAT(fname,' ',lname)as client_name,(select date from lead_status_history where status='Paid Out' and lead_id=lead_client.id order by id desc limit 1)as newdate from `lead_client` inner join `users` on `users`.`id` = `lead_client`.`agent_id` inner join `lead_status_history` on `lead_status_history`.`lead_id` = `lead_client`.`id` where $condition");
        
        
    // echo "select `lead_client`.id,lead_client.payment,lead_client.invoiced_total,agent_paid,lead_client.curr_status,lead_client.deducation,lead_client.incentive,`lead_status_history`.`date` as `paid_date`,`users`.`name` as fname, `users`.`last_name` as lname,(select date from lead_status_history where status='Paid Out' and lead_id=lead_client.id order by id desc limit 1)as newdate from `lead_client` inner join `users` on `users`.`id` = `lead_client`.`agent_id` inner join `lead_status_history` on `lead_status_history`.`lead_id` = `lead_client`.`id` where $condition"; exit;
        //echo "select `lead_client`.id,lead_client.payment,lead_client.invoiced_total,agent_paid,lead_client.curr_status,lead_client.deducation,lead_client.incentive,`lead_status_history`.`date` as `paid_date`,`users`.`name` as fname, `users`.`last_name` as lname,(select date from lead_status_history where status='Paid Out' and lead_id=lead_client.id order by id desc limit 1)as newdate from `lead_client` inner join `users` on `users`.`id` = `lead_client`.`agent_id` inner join `lead_status_history` on `lead_status_history`.`lead_id` = `lead_client`.`id` where $condition"; exit;

        $datas = array();
        
        $income_detail = get_income_calculation_admin(json_decode(json_encode($resultscount), true),$_GET['income'],$_GET['income_url']);
        
        //echo "<pre>"; print_r($resultscount); exit;
       
        $pdf_link = generate_income_pdf($resultscount,$_GET['income'],$_GET['income_url']);
        
        
        $pdf = PDF::loadView('agentincome.printpdf_new',['data'=>$pdf_link])->setPaper('a4', 'landscape');
       
        return $pdf->download('income.pdf');
    } 
    
    public function download_income(Request $req){
        
       $id               =   Auth::user()->id; 
        $user_role        =   Auth::user()->user_role;
        
        $role_for         =   "ADMIN";
        
        
        //print_r($_REQUEST); exit;
        $useraragent              =   '';
        $data['show_agent_list']  =    0;

        if($user_role == SUPERADMIN){
          
            $useraragent = User::where('user_role','=',ADMIN)
                        ->where('status','=',1)
                        ->orderBy('name','ASC')
                        ->get();
            $data['show_agent_list'] = 1;
        }elseif($user_role == ADMIN){
      
            $useraragent = User::where('user_role','=',ADMIN)
                                ->where('id','=',$id)
                                ->where('status','=',1)
                                ->orderBy('name','ASC')
                                ->get();
            Session::put('aragentsearch', $id);               
        }else{
    
            $useraragent = User::where('id','=',$id)
                      ->where('status','=',1)
                      ->orderBy('name','ASC')
                      ->get();
        }
          
        $firstuser =  $useraragent->first(); 
    
        if(session('aragentsearch') == ''){
    
          $firstuser =  $useraragent->first(); 
          if(!empty($firstuser)){   
    
            if($firstuser->id != ''){
              Session::put('aragentsearch', $firstuser->id);
            }
          }else{
            Session::put('aragentsearch',0);
          }
        }
     
        
        $agent_id           =   session('aragentsearch');
        
        //echo $agent_id; exit;
      
        $params = $_REQUEST;
        
        $columns = array('lead_client.id','lead_client.fname','','lead_client.payment','','lead_client.curr_status');
        
    
        if(isset($params['order'][0]['column'])){
            
            $order_column = $columns[$params['order'][0]['column']];
            
            $orderby = $params['order'][0]['dir'];
            
        }else{
            $order_column = 'lead_client.id';
            
            $orderby = "DESC";
        }
         
         
        $user_role          = Auth::user()->user_role;
        $user               = Auth::user()->id;
        $company = Auth::user()->company;
        
        
        if(!empty($params['start_date']) && !empty($params['end_date'])){
          
           
            $condition = " lead_client.agent_id in(select id from users where create_by_user='$agent_id' or 'company' = '$company')  ";
        }else{
          
            $condition = " lead_client.agent_id in(select id from users where create_by_user='$agent_id' or 'company' = '$company') ";
        }
        
        
        if(!empty($params['income']) && $params['income']=='outstand'){
                
           
            $condition .=" and lead_client.curr_status IN('Lease Confirmed','Invoiced','Sold','Sale Closing','10','12','9') and lead_client.payment=0 and invoiced_total!=0";
            
        }
            
        if(!empty($params['income']) && $params['income']=='gross'){
            
            $status ="'Pay Received','Paid Out','13','14'";
            
            if(!empty($params['start_date']) && !empty($params['end_date'])){
                
                $status ="'Paid Out','14'";
                
            }
            
            
            $condition1 = $condition." and lead_client.curr_status IN($status) and lead_client.payment!=0";
                
            $condition .=" and lead_client.curr_status IN($status) and lead_client.payment!=0";
            
            
        }
            
        
        if(!empty($params['income']) && $params['income']=='agent'){
            
            $status ="'Pay Received','Paid Out','13','14'";
            
            if(!empty($params['start_date']) && !empty($params['end_date'])){
                
                $status ="'Paid Out','14'";
                
            }
            
                
            $condition .=" and lead_client.curr_status IN($status) and lead_client.agent_paid!=0";
        }
            
        if(!empty($params['income']) && $params['income']=='company'){
                
            if(!empty($params['income_url']) && $params['income_url']='adminincome'){
                //$status ="'Lease Confirmed','Invoiced','Sold','Sale Closing','Pay Received','Paid Out','10','12','9','13','14'";
                $status ="'Pay Received','Paid Out','13','14'"; 
            }else{
              $status ="'Pay Received','Paid Out','13','14'"; 
            }
            
            if(!empty($params['start_date']) && !empty($params['end_date'])){
                
                $status ="'Paid Out','14'";
                
            }
            
          $condition .=" and lead_client.curr_status IN($status) and lead_client.transaction_type_id!='0'";
        }
        
        if(!empty($params['income']) && $params['income']=='all'){
            
            if(!empty($params['income_url']) && $params['income_url']='adminincome'){
                $status ="'Lease Confirmed','Invoiced','Sold','Sale Closing','Pay Received','Paid Out','10','12','9','13','14'";
            }else{
              $status ="'Pay Received','Paid Out','13','14'"; 
            }
            
            if(!empty($params['start_date']) && !empty($params['end_date'])){
                
                $status ="'Paid Out','14'";
            }
            
            $condition1 = $condition." and lead_client.curr_status IN($status)";
              
            $condition .=" and lead_client.curr_status IN($status) ";
        }
        
        if(!empty($params['client_name'])){
            $name = explode(' ',$params['client_name']);
            $fname = $name[0];
            $lname = $name[1];
            
            if(!empty($fname)){
                $condition .=" and lead_client.fname Like '$fname%'";
            }
            
            if(!empty($lname)){
                $condition .=" and lead_client.lname Like '$lname%'";
            } 
        }
        
        if(!empty($params['client_id'])){
            $clintid = $params['client_id'];
            $condition .=" and lead_client.id = '$clintid'";
        }
           
        if(!empty($params['start_date']) && !empty($params['end_date'])){
            
           $search_start_date  = setdbdate($params['start_date']);
            $search_end_date    = setdbdate($params['end_date']);
            
             if(!empty($params['income']) && ($params['income']!=='company' ||  $params['income'] !=='agent')){
                $condition .=" and lead_status_history.date BETWEEN '$search_start_date' AND '$search_end_date'";
            }
        }
        
        //echo "<pre>"; print_r($params); exit;
       
       
      $condition .=" GROUP BY lead_client.id ";
        

         if(!empty($params['start_date']) && !empty($params['end_date'])){
             
             if(!empty($params['income']) && ($params['income']=='company' ||  $params['income'] =='agent')){
                  
                  $condition .=" having newdate BETWEEN '$search_start_date' and '$search_end_date'";
              }
         }
         
        
        $resultscount= DB::select("select `lead_client`.id,lead_client.source_id,lead_client.payment,lead_client.transaction_type_id,users.manage_distributor,lead_client.invoiced_total,agent_paid,lead_client.curr_status,lead_client.deducation,lead_client.incentive,`lead_status_history`.`date` as `paid_date`,`users`.`name` as fname, `users`.`last_name` as lname,(select date from lead_status_history where status='Paid Out' and lead_id=lead_client.id order by id desc limit 1)as newdate from `lead_client` inner join `users` on `users`.`id` = `lead_client`.`agent_id` inner join `lead_status_history` on `lead_status_history`.`lead_id` = `lead_client`.`id` where $condition");
        
        
        $condition .= "order by ".$order_column." ".$orderby;
        
        //DB::enableQueryLog();
        $results = DB::select("select lead_client.id,lead_client.fname,lead_client.lname,lead_client.payment,lead_client.paid_date as lpaid_date,lead_client.paid_out as lpaidout_date,lead_client.paid_out,lead_client.incentive, `users`.`name`, `users`.`last_name`,lead_client.invoiced_total, lead_client.agent_paid,lead_client.paid_date as lpaid_date,lead_client.paid_out as lpaidout_date, lead_client.sendto,lead_client.curr_status,`users`.`name`, `users`.`last_name`, `lead_status_history`.`date` as `paid_date`,lead_client.status_history,(select date from lead_status_history where status='Paid Out' and lead_id=lead_client.id order by id desc limit 1)as newdate from `lead_client` inner join `users` on `users`.`id` = `lead_client`.`agent_id` inner join `lead_status_history` on `lead_status_history`.`lead_id` = `lead_client`.`id` where $condition ");
        
       // $outstanding = $this->paid_amount("lead_client.id");
        
      //echo "select `lead_client`.id,lead_client.source_id,lead_client.payment,lead_client.transaction_type_id,users.manage_distributor,lead_client.invoiced_total,agent_paid,lead_client.curr_status,lead_client.deducation,lead_client.incentive,`lead_status_history`.`date` as `paid_date`,`users`.`name` as fname, `users`.`last_name` as lname,(select date from lead_status_history where status='Paid Out' and lead_id=lead_client.id order by id desc limit 1)as newdate from `lead_client` inner join `users` on `users`.`id` = `lead_client`.`agent_id` inner join `lead_status_history` on `lead_status_history`.`lead_id` = `lead_client`.`id` where $condition";
        
    //exit;
        
        $results = json_decode(json_encode($results), true);
        
       //echo "<pre>"; print_r($results); exit;
        //echo $results; exit;
        $datas = array();
        
       $income_detail = get_income_calculation(json_decode(json_encode($resultscount), true));
       
       //echo count($results); exit;
       
        if(count($results)>0){
            
            $invoiced_total = array();
            $payments = array();

            $deductions = array();
            $companyincomes = array();
            $agent_income = array();
            
            foreach ($results as $row) {

                $nestedData = array();
                
                 $client=$row['id'];
                
                $name =  ucfirst($row['fname']).' '.ucfirst($row['lname']);
                
                
                 $paid_out = $row['lpaidout_date'];
                if($paid_out=='01/01/1970' || $paid_out=='01-01-1970'){
                    $paid_out='';
                }
                
                //$leadcalculation        = AgentincomeController::getcalculation($row['id']);
                
                //$paid_date = (!empty($row['paid_date'])?$row['paid_date']:'');
                
                // deduction//
                $lead_calculate = lead_calculate($row['id']);
                
                $deduction = $this->deduction($lead_calculate,$row['id']);
                    
                $reseturl ='adminincome';
                    
               // $deduction = $this->deduction($row->id);
                
                    
            if(is_numeric($row['curr_status'])){
                $status = set_status_name($row['curr_status']);
            }else{
                $status = set_status_name($row['curr_status']); 
            }
            
                $company_income = $this->aroradmin($lead_calculate,$reseturl,$row['id']);
           
            
                //$nestedData['DT_RowId'] = "row_".$row['sendto'];
                $nestedData['Client'] = $client;
                $nestedData['Name'] = $name;
                // $nestedData['Res_commission'] = number_format($row['invoiced_total'],2);
                $nestedData[] = ($row['invoiced_total'] != 0) ? '$'.number_format($row['invoiced_total'],2) : (($lead_calculate['invoiced_total'] != 0) ? '$'.number_format($lead_calculate['invoiced_total'],2) : '$0.00');
                    
                    array_push($invoiced_total,($row['invoiced_total'] != null) ? $row['invoiced_total'] : (($lead_calculate['invoiced_total'] != null) ? $lead_calculate['invoiced_total'] :0));
                    array_push($payments,$row['payment']);
                
    
                 $dates =  payout_payreceived_date($row['status_history']);
                 
                
                $nestedData['Paid_amount'] = number_format($row['payment'],2);
                
                $nestedData['Paid_date'] = (!empty($dates['paid_rec'])?$dates['paid_rec']:'');
                
                $nestedData['Status'] = (!empty($status)?$status:'');
                
                $nestedData['Deduction'] = number_format($deduction['total'],2);
                
                array_push($deductions,$deduction['total']);
                
                $nestedData['Agent_income'] = number_format($lead_calculate['agent_paid'],2);
                array_push($agent_income,$lead_calculate['agent_paid']);
                
                $nestedData['Company_income'] = number_format($company_income['total'],2);
                array_push($companyincomes,$company_income['total']);
                

                $nestedData['Paid_out'] = (!empty($dates['paid_out'])?$dates['paid_out']:'');
                
                //$this->array_sort_by_column($results, 'id',SORT_DESC);
                
                $datas[] = $nestedData; 
            }
        }
    
        //echo "<pre>"; print_r($datas); exit;

        header("Content-type: application/csv");
        header("Content-Disposition: attachment; filename=Income_data".date('Ymd') .".csv");
        $fp = fopen('php://output', 'w');
         
        $delimiter="\t";
         
         
        $handle = fopen( 'php://output', 'w' );
         
        fputcsv($fp,['Client','Name','Res Commission','Paid Amount','Payment Paid Date','Status','Deduction','Agent Income','Company Income','Paid Out']);
        foreach ($datas as $row) {
            fputcsv($fp, $row);
        }
    
        fputcsv($fp,['','Total',array_sum($invoiced_total),array_sum($payments),'','',array_sum($deductions),array_sum($agent_income),array_sum($companyincomes),'']);
        fclose($fp);

    
        //echo "<pre>"; print_r($result); exit;
    } 
    
    function get_income_calculation($leads){
        
       
        $result = array();
        
        $outstanding = 0;
        $gross_revenue = 0;
        $deductions = 0;
        $agent_income=0;
        $company_incomes=0;
        $rescommission=0;
        
        foreach($leads as $lead){
            
            
            $lead_calculate = lead_calculate($lead['id']);
            
            $deduction = $this->deduction($lead_calculate,$lead['id']);
            
            $reseturl ='adminincome';
            
            $company_income = $this->aroradmin($lead_calculate,$reseturl,$lead['id']);
            
            //$leadcalculation = AgentincomeController::getcalculation($lead['id']);
            
            // $rescommission += $leadcalculation['paidamount'];
            
            // $outstanding += ($leadcalculation['paidamount']-$lead['payment']);
            
            
            $rescommission += $lead['invoiced_total'];
            $outstanding += ($lead['invoiced_total']-$lead['payment']);
            
            $gross_revenue+= $lead['payment'];
            $deductions += $deduction['total'];
            
            $agent_income += $lead['agent_paid'];
            
            $company_incomes += $company_income['total'];
        }
        $data['rescommission'] = '$'.number_format($rescommission,2);
        $data['outstanding'] = '$'.number_format($outstanding,2);
        $data['gross_revenue'] =  '$'.number_format($gross_revenue,2);
        $data['deduction'] = '$'.number_format($deductions,2);
        $data['agent_income'] = '$'.number_format($agent_income,2);
        $data['company_income'] = '$'.number_format($company_incomes,2);
       // $data['id'] = implode(',',array_column($leads,'id'));
         $data['id'] ='';
        
        return $data;     
    }
    
    
    public function income_pdf_new(Request $request){
      
         
    $method         = $request->input('method');
   
  
   // $ids                = explode(',',$request->input('ids'));
    
    $ids                = $request->input('ids');
    
        $id               =   Auth::user()->id; 
        $user_role        =   Auth::user()->user_role;
        
        $role_for         =   "ADMIN";
        
        $useraragent              =   '';
        $data['show_agent_list']  =    0;

        if($user_role == SUPERADMIN){
          
            $useraragent = User::where('user_role','=',ADMIN)
                        ->where('status','=',1)
                        ->orderBy('name','ASC')
                        ->get();
            $data['show_agent_list'] = 1;
        }elseif($user_role == ADMIN){
      
            $useraragent = User::where('user_role','=',ADMIN)
                                ->where('id','=',$id)
                                ->where('status','=',1)
                                ->orderBy('name','ASC')
                                ->get();
            Session::put('aragentsearch', $id);               
        }else{
    
            $useraragent = User::where('id','=',$id)
                      ->where('status','=',1)
                      ->orderBy('name','ASC')
                      ->get();
        }
          
        $firstuser =  $useraragent->first(); 
    
        if(session('aragentsearch') == ''){
    
          $firstuser =  $useraragent->first(); 
          if(!empty($firstuser)){   
    
            if($firstuser->id != ''){
              Session::put('aragentsearch', $firstuser->id);
            }
          }else{
            Session::put('aragentsearch',0);
          }
        }
     
        
        $agent_id           =   session('aragentsearch');
      
        $params = $_REQUEST;
        
        $columns = array('lead_client.id','lead_client.fname','','lead_client.payment','','lead_client.curr_status');
        
    
        if(isset($params['order'][0]['column'])){
            
            $order_column = $columns[$params['order'][0]['column']];
            
            $orderby = $params['order'][0]['dir'];
            
        }else{
            $order_column = 'lead_client.id';
            
            $orderby = "DESC";
        }
         
         
        $user_role          = Auth::user()->user_role;
        $user               = Auth::user()->id;
        
        
        if(!empty($params['search_start_date']) && !empty($params['search_end_date'])){
          
            $condition = " lead_client.agent_id in(select id from users where create_by_user='$agent_id') and lead_client.curr_status IN('Paid Out','14') ";
          
        }else{
          
            $condition = " lead_client.agent_id in(select id from users where create_by_user='$agent_id') and lead_client.curr_status IN('Sold'.'Sale Closing','Invoiced','Pay Received','MIC','Paid Out','9','12','13','14','11') ";
        }
        
        
        if(!empty($params['client_name'])){
            $name = explode(' ',$params['client_name']);
            $fname = $name[0];
            $lname = $name[1];
            
            if(!empty($fname)){
                $condition .=" and lead_client.fname Like '$fname%'";
            }
            
            if(!empty($lname)){
                $condition .=" and lead_client.lname Like '$lname%'";
            } 
        }
        
        if(!empty($params['client_id'])){
            $clintid = $params['client_id'];
            $condition .=" and lead_client.id = '$clintid'";
        }
           
          
        if(!empty($params['search_start_date']) && !empty($params['search_end_date'])){
            
            $search_start_date  = setdbdate($params['search_start_date']);
            $search_end_date    = setdbdate($params['search_end_date']);
            
            $condition .=" and lead_status_history.date BETWEEN '$search_start_date' AND '$search_end_date'";
        }
        
        if(!empty($params['income']) && $params['income']=='gross'){
            $condition .=" and lead_client.payment!=0";
        }
        
        if(!empty($params['income']) && $params['income']=='outstand'){
            $condition .=" and lead_client.invoiced_total!=0";
        }
        
        if(!empty($params['income']) && $params['income']=='agent'){
            $condition .=" and lead_client.agent_paid!=0";
        }
        
        if(!empty($params['income']) && $params['income']=='company'){
            $condition .=" and lead_client.deducation!=''";
        }
        
        
        //echo "<pre>"; print_r($params); exit;
       
        $limit="order by $order_column $orderby";
        
       
        $results= DB::select("select lead_client.id,lead_client.fname,lead_client.lname,lead_client.payment,lead_client.paid_date as lpaid_date,lead_client.paid_out as lpaidout_date,lead_client.paid_out,lead_client.incentive, `users`.`name`, `users`.`last_name`,lead_client.invoiced_total, lead_client.agent_paid,lead_client.paid_date as lpaid_date,lead_client.paid_out as lpaidout_date,lead_client.paid_out,lead_client.incentive, `users`.`name`, `users`.`last_name`,lead_client.invoiced_total, lead_client.agent_paid,`lead_status_history`.`date` as `paid_date` from `lead_client` inner join `users` on `users`.`id` = `lead_client`.`agent_id` inner join `lead_status_history` on `lead_status_history`.`lead_id` = `lead_client`.`id` where $condition GROUP BY lead_client.id order by $order_column $orderby");
        
        
        //  echo "select `lead_client`.*,`lead_client`.id,lead_client.payment,lead_client.invoiced_total,agent_paid from `lead_client` inner join `users` on `users`.`id` = `lead_client`.`agent_id` inner join `lead_status_history` on `lead_status_history`.`lead_id` = `lead_client`.`id` where $condition GROUP BY lead_client.id order by $order_column $orderby"; exit;
        
        //$results = json_decode(json_encode($results), true);
        
       
        //echo $results; exit;
        $datas = array();
        
        $income_detail = $this->get_income_calculation(json_decode(json_encode($results), true));
       
        $response['url']                =   'searchagentincome';
        $response['search_start_date']  =   '';
        $response['search_end_date']    =   '';
        $response['show_agent_list']    =   1;
        $response['reseturl']           =   $method;
        $response['income_detail']      =   $income_detail;
        $response['leadclients']        =  $results;
   
    
        $path='/adminincome';
          
        if($path == '/adminincome'){
            $title= $usserpage = 'Admin Income';
        }elseif($path == '/arincome'){
            $title= $usserpage = "AR Income";
        }else{
            $title= $usserpage = "Agent Income";
        }
         
        $response['title']  =   $title;
    
   // print_r(Auth::user()->email); exit;
    
      //echo $footerHtml; exit;
      
        
        $pdf = PDF::loadView('agentincome.printpdf_new',['data'=>$pdf_link])->setPaper('a4', 'landscape');
       
        return $pdf->download('income.pdf');
        
        //exit;
        //$pdf = PDF::loadView('agentincome.printpdf_new',['data'=>$response])->setPaper('a4', 'landscape');
             
        //$pdf->save(storage_path('pdf/'.date('Y-m-d').'_'.$id.'_income_pdf.pdf'));
    
        //if($_SERVER['SERVER_NAME'] == 'development.realm.clientmgr.us' || $_SERVER['SERVER_NAME'] == 'development.flexsin.clientmgr.us' || $_SERVER['SERVER_NAME'] == 'realm.clientmgr.us'){
        
          //$response['link'] = PDFPATH.'../storage/pdf/'.date('Y-m-d').'_'.$id.'_income_pdf.pdf';
        //}else{
         // $response['link']= PDFPATH.'../crmrewrite/storage/pdf/'.date('Y-m-d').'_'.$id.'_income_pdf.pdf';
       // }
        
        
        //print_r($otherdata); exit;
        echo '{"status":true,"link":"'.$response['link'].'"}';
        
    
        // if (sendmail($data, $otherdata, $pathToFile, '', strip_tags($messagesend))){
          
        //   echo '{"status":true,"link":"'.$response['link'].'"}'; die();
        // }
      
         die();
    }


    function aasort (&$array, $key) {
        $sorter = array();
        $ret = array();
        reset($array);
        foreach ($array as $ii => $va) {
            $sorter[$ii] = $va[$key];
        }
        asort($sorter);
        foreach ($sorter as $ii => $va) {
            $ret[$ii] = $array[$ii];
        }
        $array = $ret;
    }

    
    public function deduction($lead_calculate,$id){
         $str = '';
        $total = array();
        
        $lead_calculate = $lead_calculate;
        
         if($lead_calculate['agent_invoice_cost']){
            
            $str.="<tr><td>Invoice Fee</td><td>$".number_format($lead_calculate['agent_invoice_cost'],2).'</td></tr>';
            
            array_push($total,$lead_calculate['agent_invoice_cost']);
        }
        
        if($lead_calculate['commercial_transaction']){
            $str.="<tr><td>Commercial Transaction Fee (Tiered) </td><td>$".number_format($lead_calculate['commercial_transaction'],2).'</td></tr>';
            array_push($total,$lead_calculate['commercial_transaction']);
        }
        
        if($lead_calculate['broker_splite']){
            $str.="<tr><td>Broker Cost </td><td>$".number_format($lead_calculate['broker_splite'],2).'</td></tr>';
            array_push($total,$lead_calculate['broker_splite']);
        }
        
        if($lead_calculate['lead_cost']){
            $str.="<tr><td>Lead Cost </td><td>$".number_format($lead_calculate['lead_cost'],2).'</td></tr>';
            array_push($total,$lead_calculate['lead_cost']);
        }
        
        if($lead_calculate['incentive']){
            
            $str.="<tr><td>Incentive</td><td>$".number_format($lead_calculate['incentive'],2).'</td></tr>';
             
            array_push($total,$lead_calculate['incentive']);
        }
       
        
        if($lead_calculate['transaction_coordination_lease_verify']){
            
            $str.="<tr><td>Optional Fee </td><td>$".number_format($lead_calculate['transaction_coordination_lease_verify'],2).'</td></tr>';
           
            array_push($total,$lead_calculate['transaction_coordination_lease_verify']);
        }
        
        
        $str.="<tr><th>Total Deductions </th><td>$".number_format(array_sum($total),2)."</td></tr>";
        
        $data['string'] = "<a  href='javascript:void(0);' data-toggle='modal' data-target='#showdeduct' onclick='view_deduct($id)'; style='cursor: pointer;text-decoration: none;' title='View Deductios' str='$str' class='ded_$id'>$". number_format(array_sum($total),2)."</a>";
        
        $data['total'] = (array_sum($total));
        
        return $data;
    }
    
    
    public function aroradmin($lead_calculate,$reseturl,$id){
        if($reseturl=='adminincome' || $reseturl=='arincome'){
               
                   
            $lead_calculation = $lead_calculate;
                    
                  
            $totals=array();
            $str='';
            //$payable_amount= $leadcalculatdata['invoiced_total'];
                
             if($lead_calculation['transaction_coordination_lease_verify']){
            
                array_push($totals,$lead_calculation['transaction_coordination_lease_verify']);
                $str.="<tr><td>Optional Fee</td><td>$".number_format($lead_calculation['transaction_coordination_lease_verify'],2).'</td></tr>';
            }
                
             if($lead_calculation['agent_invoice_cost']){
            
                array_push($totals,$lead_calculation['agent_invoice_cost']);
                $str.="<tr><td>Invoiced fee</td><td>$".number_format($lead_calculation['agent_invoice_cost'],2).'</td></tr>';
            }
                
            if($lead_calculation['commercial_transaction']){
            
                array_push($totals,$lead_calculation['commercial_transaction']);
                $str.="<tr><td>Commercial transaction</td><td>$".number_format($lead_calculation['commercial_transaction'],2).'</td></tr>';
            }
                
            if($lead_calculation['broker_splite']){
            
                array_push($totals,$lead_calculation['broker_splite']);
                $str.="<tr><td>Broker splite</td><td>$".number_format($lead_calculation['broker_splite'],2).'</td></tr>';
            }
                
            $str.="<tr><th>Total Deductions </th><td>$".number_format(array_sum($totals),2).'</td></tr>';
            
            $data['string'] = '<a  href="javascript:void(0);" data-toggle="modal" data-target="#showdeduct" onclick="view_deduct1('.$id.')"; style="cursor: pointer;text-decoration: none;" title="View Deductios" str="'.$str.'" class="ded1_'.$id.'">$'.number_format(array_sum($totals),2).'</a>';
            
            $data['total'] = (array_sum($totals));
            
            return $data;
        }  
                 
    }
    
    
    public function adminincomejson(Request $request){

         $id               =   Auth::user()->id; 
        $user_role        =   Auth::user()->user_role; 
        $role_for         =   "ADMIN";
        $items            =   $request->items ?? 25;
    
    //print_r($_POST); exit;
        $leadclients=0;
        if($_POST['method']=='searchadincome')
        {
            $leadclients = Leadclient::join('users', 'users.id', '=', 'lead_client.agent_id')
                        ->join('lead_status_history','lead_status_history.lead_id','lead_client.id')
                        ->select('lead_client.*');
        
            $useraragent              =   '';
            $data['show_agent_list']  =    0;
        
            if($user_role == SUPERADMIN){
              
              $useraragent = User::where('user_role','=',ADMIN)
                            ->where('status','=',1)
                            ->orderBy('name','ASC')
                            ->get();
              $data['show_agent_list'] = 1;
            }elseif($user_role == ADMIN){
          
              $useraragent = User::where('user_role','=',ADMIN)
                                    ->where('id','=',$id)
                                    ->where('status','=',1)
                                    ->orderBy('name','ASC')
                                    ->get();
              Session::put('aragentsearch', $id);               
            }else{
        
              $useraragent = User::where('id','=',$id)
                                  ->where('status','=',1)
                                  ->orderBy('name','ASC')
                                  ->get();
            }
         
            $firstuser =  $useraragent->first(); 
        
            if(session('aragentsearch') == ''){
        
              $firstuser =  $useraragent->first(); 
              if(!empty($firstuser)){   
                if($firstuser->id != ''){
                  Session::put('aragentsearch', $firstuser->id);
                }
              }else{
                Session::put('aragentsearch',0);
              }
            }
             
            $agent_id           =   session('aragentsearch');
            $arUser             =   User::select('id')->where('create_by_user','=',$agent_id)->get();
            $leadclients        =   $leadclients->whereIN('agent_id',$arUser);
           
            $search_start_date  =   ''; 
            $search_end_date    =   ''; 
            $client_name        =   '';
            $client_id          =   '';
            $searchform         =   $request->input('search_agent'); 
    
            if($_POST['method']=='searchadincome' && (!empty($request->input('client_name')) || !empty($request->input('client_id')) || !empty($request->input('search_start_date')) || !empty($request->input('search_end_date')))){
        
              $client_name      = $request->input('client_name');
              $client_id        = $request->input('client_id');
              $arr              =  explode(" ", $client_name);
        
              @$c_fname         = $arr[0];
              @$c_lname         = $arr[1];
        
              $data['client_name'] = $client_name;
        
              if($client_name !='') {
                $leadclients          =  $leadclients->where('lead_client.fname',$c_fname);
                $leadclients          =  $leadclients->where('lead_client.lname',$c_lname);
              }
        
              if ($client_id !='') {
                $leadclients      =  $leadclients->where('lead_client.id',$client_id);
              }   
              $statusarry         =   ['Paid Out','14'];
            
              $search_start_date  = setdbdate($request->input('search_start_date'));
              $search_end_date    = setdbdate($request->input('search_end_date'));
              $client_name        = $request->input('client_name');
              $client_id          = $request->input('client_id');
              $arr                =  explode(" ", $client_name);
        
              @$c_fname           = $arr[0];
              @$c_lname           = $arr[1];
              $data['client_name'] = $client_name;
        
              if($search_start_date != '' && $search_end_date != ''){
        
                $leadclients =$leadclients->whereIn('lead_status_history.status',$statusarry);
                $leadclients =$leadclients->whereBetween('lead_status_history.date',[$search_start_date,$search_end_date]);
                
              }elseif ($client_name !='') {
               
                $leadclients =  $leadclients->where('lead_client.fname','LIKE',$c_fname);
                $leadclients =  $leadclients->where('lead_client.lname','LIKE',$c_lname);
        
              }elseif ($client_id !='') {
        
                $leadclients      =  $leadclients->where('lead_client.id',$client_id);
        
              }else{
                
                $leadclients = $leadclients->whereIn('lead_client.curr_status',$statusarry);
                if(!empty($search_start_date) && !empty($search_end_date)){
                     $leadclients    = $leadclients->whereDate('lead_status_history.date','>=',$search_start_date)->whereDate('lead_client.date','<=',$search_end_date);
                }
                $leadclients      =  $leadclients->where('lead_client.fname','LIKE',$c_fname);
                $leadclients      =  $leadclients->where('lead_client.lname','LIKE',$c_lname);
                $leadclients      =  $leadclients->where('lead_client.id',$client_id);
              }
            }else{
        
              $statusarry         = ['Sold','Sale Closing','Invoiced','Pay Received','MIC','Paid Out','9','12','13','14','11'];
              $leadclients = $leadclients->whereIn('lead_client.curr_status',$statusarry);  
            }
            
            //$statusarry         = ['Sold','Invoiced','Pay Received','MIC','Paid Out'];
            $leadclients    =  $leadclients->groupBy('lead_client.id')->get(); 
        }
    
        if($_POST['method']=='searcharincome')
        {
            
            
            $id             = Auth::user()->id; 
            $user_role        = Auth::user()->user_role; 
            $role_for         =   "ARUSER";
            $items            =   $request->items ?? 25;
            $sort           = isset($request->sort) ? $request->sort : 'id';
            $direction      = isset($request->direction) ? $request->direction : 'DESC';
           
          // echo $sort; exit;
           
          Session::put('role_for',$role_for);
         
        
          $leadclients      = Leadclient::join('users', 'users.id', '=', 'lead_client.agent_id')
                 ->join('lead_status_history','lead_status_history.lead_id','lead_client.id') ->select('lead_client.*', 'users.name', 'users.last_name','lead_status_history.date as paid_date');
    
    
          $useraragent      = '';
          $data['show_agent_list'] = 0;
          if($user_role == SUPERADMIN){
          
            $useraragent = User::where('user_role','=',ARUSER)
                                ->where('status','=',1)
                                ->orderBy('name','ASC')
                                ->get();
            $data['show_agent_list'] = 1;
          }elseif($user_role == ADMIN){
      
    
             $useraragent  =   User::where('user_role','=',4)
                      ->where('users.create_by_user','=',$id)
                      ->orderBy('name','ASC')
                      ->get();
            $data['show_agent_list']  = 1;   
          }else{
    
            $useraragent = User::where('user_role','=',ARUSER)
                                ->where('id','=',$id)
                                ->where('status','=',1)
                                ->orderBy('name','ASC')
                                ->get();
            Session::put('aragentsearch',$id);         
          }
    
          $firstuser =  $useraragent->first(); 
          if(session('aragentsearch') == ''){
    
            $firstuser =  $useraragent->first(); 
            if(!empty($firstuser)){   
    
              if($firstuser->id != ''){
                  Session::put('aragentsearch', $firstuser->id);
              }
            }else{
              Session::put('aragentsearch',0);
            }
          }
           
          $agent_id           = session('aragentsearch'); 
          $arUser             =  User::select('id')
                                      ->where('assign_to_ar','=',$agent_id);
          $leadclients        =  $leadclients->whereIN('agent_id',$arUser);
       
    
          $search_start_date  = ''; 
          $search_end_date    = ''; 
          $searchform         = $request->input('search_agent');
        
        //echo "<pre>"; print_r($request->input('search_agent')); exit;
          if($_POST['method']=='searcharincome'){
              
                 //echo $searchform; exit;
                 
              $client_name          = $request->input('client_name');
              $client_id            = $request->input('client_id');
              $arr                  =  explode(" ", $client_name);
    
              @$c_fname             = $arr[0];
              @$c_lname             = $arr[1];
              $data['client_name']  = $client_name;
    
              if ($client_name !='') {
                $leadclients =  $leadclients->where('lead_client.fname',$c_fname);
                $leadclients =  $leadclients->where('lead_client.lname',$c_lname);
               
              }
              if ($client_id !='') {
                $leadclients =  $leadclients->where('lead_client.id',$client_id);
              
                $data['client_id']  = $client_id;
              }
    
             
              $statusarry         =   ['Paid Out','14'];
              $search_start_date  = setdbdate($request->input('search_start_date'));
              $search_end_date    = setdbdate($request->input('search_end_date'));
    
              if($search_start_date != '' && $search_end_date != ''){
            
               
                $leadclients = $leadclients->whereIn('lead_client.curr_status',$statusarry);
                $leadclients = $leadclients->whereBetween('lead_client.newpaid_date',[$search_start_date,$search_end_date]);
            
             
                $data['search_start_date']  = $search_start_date;
                $data['search_end_date']  = $search_end_date;
                
              }
          }else{
    
         
          $statusarry         =   ['Sold','Invoiced','Pay Received','MIC','Paid Out','9','12','13','14','11'];
          // print_r($statusarry); exit;
            //DB::enableQueryLog();
        
            $leadclients =   $leadclients->whereIn('lead_client.curr_status',$statusarry);
            // dd(DB::getQueryLog()); exit;
          
          }
          
            $leadclients =   $leadclients->whereIn('lead_client.curr_status',$statusarry);
                                   
            $leadclients      =   $leadclients->orderby('lead_client.'.$sort ,$direction)->groupBy('lead_client.id')->get();
        }
    
    
        if($_POST['method']=='searchagentincome')
        {
         
            $id         = Auth::user()->id; 
            $user_role  = Auth::user()->user_role; 
            $items      = $request->items ?? 25;
            $role_for   = "AGENT";
            //dd("Working........");
            Session::put('role_for',$role_for);
               
            $leadclients      = Leadclient::join('users', 'users.id', '=', 'lead_client.agent_id')
                        ->leftjoin('lead_status_history','lead_status_history.lead_id','lead_client.id')
                        ->select('lead_client.*', 'users.name', 'users.last_name','lead_status_history.date as paid_date');
        
        
            $useraragent = '';
            if($user_role == SUPERADMIN){
                
              $useraragent  = User::where('user_role','=',3)
                                  ->orderBy('name','ASC')
                                  ->get();
            }else if($user_role == ARUSER){
        
              $useraragent  = User::where('user_role','=',AGENT)
                                    ->where('assign_to_ar','=',$id)
                                    ->orderBy('name','ASC')
                                    ->get();
            }elseif ($user_role == ADMIN) {
              # code...
           
            
            $useraragent  = User::where('user_role','=',3)
                                  ->orderBy('name','ASC')
                                  ->get();
                                  
            }else{
        
              $useraragent  = User::where('user_role','=',AGENT)
                                    ->where('id','=',$id)
                                    ->orderBy('name','ASC')
                                    ->get();
            }
            //echo "<pre>"; print_r($useraragent); exit;
    
            if(session('aragentsearch') == ''){
              $firstuser =  $useraragent->first(); 
              if(!empty($firstuser)){   
        
                if($firstuser->id != ''){
                  Session::put('aragentsearch', $firstuser->id);
                }
              }else{
                Session::put('aragentsearch',0);
              }
            }
             
            $agent_id           =   session('aragentsearch');
            $leadclients        =   $leadclients->where('agent_id','=',session('aragentsearch'));
           
        
            $search_start_date  =   ''; 
            $search_end_date    =   ''; 
            $searchform         =   $request->input('search_agent'); 
       
            if($_POST['method']=='searchagentincome'){
        
             // $statusarry         = ['Invoiced','Pay Received','Paid Out','Sold','Lease Confirmed','MIC','9','12','13','14','11','10'];
              
              $statusarry         = ['Paid Out','12','14'];
           
              
              $client_name        = $request->input('client_name');
              $client_id          = $request->input('client_id');
              
              // echo ($_POST['client_id']); exit;
              $arr                =  explode(" ", $client_name);
        
              @$c_fname             = $arr[0];
              @$c_lname             = $arr[1];
              $data['client_name']  = $client_name;
        
              if ($client_name !='') {
        
                $leadclients      =  $leadclients->where('lead_client.fname',$c_fname);
                $leadclients      =  $leadclients->where('lead_client.lname',$c_lname);
              
              }
        
              if ($client_id !='') {
                $leadclients      =  $leadclients->where('lead_client.id',$client_id);
              
                $data['client_id']  = $client_id;
              }
        
              $search_start_date = setdbdate($request->input('search_start_date'));
              $search_end_date   = setdbdate($request->input('search_end_date'));
        
              if($search_start_date != '' && $search_end_date != ''){
               
               
                $leadclients =  $leadclients->whereIn('lead_client.curr_status',$statusarry);
                $leadclients =  $leadclients->whereBetween('lead_status_history.date',[$search_start_date,$search_end_date]);
                
              }
              
               $leadclients      = $leadclients->whereIn('lead_status_history.status',$statusarry);
            }else{
        
              $statusarry       = ['Invoiced','Pay Received','Paid Out','Sold','Lease Confirmed','MIC','9','12','13','14','11','10'];
            
          
                $leadclients      = $leadclients->whereIn('lead_client.curr_status',$statusarry);
              
            }
            
            //$statusarry         = ['Invoiced','Pay Received','Paid Out','Sold','Lease Confirmed','MIC','9','12','13','14','11','10'];
            //$statusarry123    = ['Invoiced','Pay Received','Paid Out','Sold','Lease Confirmed','MIC','9','12','13','14','11','10'];
            
           
            $leadclients    =   $leadclients
                                            //->whereIn('curr_status', $statusarry)
                                                ->groupBy('lead_client.id')
                                                ->get(); 
        }
    
        //echo $_POST['method']; exit; 
    
        if(empty($leadclients)){
            $data['total_incoe'] = '0.00';
            $data['total_invoice'] = '0.00';
            $data['gross_pay'] = '0.00';
            $data['outsatnginamt'] = '0.00';
            $data['ids'] = '0.00';
            echo json_encode($data); exit;
        }
    
        $total_incoe = array();
        $total_invoice = array();
        $gross_pay = array();
        $ids = array();
        $outsatnginamtarray = array();
        
        $outsatnginamt = 0;
        $tot_net=0;
        $tot_invoiced=0;
        $tot_paid=0;
    //                             echo json_encode($leadclientsall); exit;
    
    //$incomes = get_income_calculation_admin($leadclientstotal,$type);
    
   // echo $_REQUEST['method']; exit;
    
        if($_REQUEST['method']=='searchadincome'){
            $type = 'company';
        }
        
        if($_REQUEST['method']=='searcharincome'){
            $type = 'company';
        }
        
        if($_REQUEST['method']=='searchagentincome'){
            $type = 'agent';
        }
    
    
        $income_detail = get_income_calculation_admin(json_decode(json_encode($leadclients), true),$type);
    
        //echo"<pre>"; print_r(($income_detail)); exit;
    
        $totals = array();
       
    
        if($_POST['method']=='searcharincome'){
            $data['total_incoe'] = $income_detail['company_income'];
        }else{
           $data['total_incoe'] = $income_detail['agent_income']; 
        }
        
        
        $data['total_invoice'] = $income_detail['rescommission'];
        $data['gross_pay'] = $income_detail['gross_revenue'];
        $data['outsatnginamt'] = $income_detail['outstanding'];
        $data['agent_income'] = $income_detail['agent_income'];
        $data['company_income']     = $income_detail['company_income'];
        $data['type'] = $type;
        $data['ids'] = implode(',',$ids);
        echo json_encode($data); exit;
    }

/*
* @function : adminincome (to list all income for admin user.)
* @params: request data admin id
* @return : json data of all income reccords
* @modifiedby:  chintesh k.
*/ 
  public function arincome(Request $request){

      $id               = Auth::user()->id; 
      $user_role        = Auth::user()->user_role; 
      $role_for         =   "ARUSER";
      $items            =   $request->items ?? 25;
      
       unset($request->ids);
      
        $sort           = isset($request->sort) ? $request->sort : 'id';
        $direction  = isset($request->direction) ? $request->direction : 'DESC';
       
       // echo $sort; exit;
      
      Session::put('role_for',$role_for);
     
     if(!empty($request->input('get_invoices_count')) && $request->input('get_invoices_count') == 'getInvoiceCount'){
      $leadclients      = Leadclient::join('users', 'users.id', '=', 'lead_client.agent_id')
             ->join('lead_status_history','lead_status_history.lead_id','lead_client.id') ->select('lead_client.*', 'users.name', 'users.last_name',DB::raw('MAX(`lead_status_history`.`date`) as `paid_date`'));
     } else {
      $leadclients      = Leadclient::join('users', 'users.id', '=', 'lead_client.agent_id')
             ->join('lead_status_history','lead_status_history.lead_id','lead_client.id') ->select('lead_client.*', 'users.name', 'users.last_name','lead_status_history.date as paid_date');
     }


      $leadclientstotal = Leadclient::join('users', 'users.id', '=', 'lead_client.agent_id')
                                ->leftjoin('lead_status_history','lead_status_history.lead_id','lead_client.id')->select('lead_client.*', 'users.name', 'users.last_name');
      $useraragent      = '';
      $data['show_agent_list'] = 0;
      if($user_role == SUPERADMIN){
      
        $useraragent = User::where('user_role','=',ARUSER)
                            ->where('status','=',1)
                            ->orderBy('name','ASC')
                            ->get();
        $data['show_agent_list'] = 1;
      }elseif($user_role == ADMIN){
  
        // $useraragent = User::where('user_role','=',ARUSER)
        //                     ->where('create_by_user','=',$id)
        //                     ->where('status','=',1)
        //                     ->orderBy('name','ASC')
        //                     ->get();

         $useraragent  =   User::where('user_role','=',4)
                  ->where('users.create_by_user','=',$id)
                  ->orderBy('name','ASC')
                  ->get();
        $data['show_agent_list']  = 1;   
      }else{

        $useraragent = User::where('user_role','=',ARUSER)
                            ->where('id','=',$id)
                            ->where('status','=',1)
                            ->orderBy('name','ASC')
                            ->get();
        Session::put('aragentsearch',$id);         
      }

      $firstuser =  $useraragent->first(); 
      if(session('aragentsearch') == ''){

        $firstuser =  $useraragent->first(); 
        if(!empty($firstuser)){   

           if($firstuser->id != ''){
              Session::put('aragentsearch', $firstuser->id);
           }
        }else{
          Session::put('aragentsearch',0);
        }
      }
       
      $agent_id           = session('aragentsearch'); 
      $arUser             =  User::select('id')
                                  ->where('assign_to_ar','=',$agent_id);
                                  
                                  
        $leadclients  = $leadclients->whereIn('agent_id',function ($query) use ($agent_id) {
            $query->select('id')->from('users')->Where('assign_to_ar',$agent_id);
       
        });
        
        $leadclientstotal  = $leadclientstotal->whereIn('agent_id',function ($query) use ($agent_id) {
            $query->select('id')->from('users')->Where('assign_to_ar',$agent_id);
       
        }); 
                                  
                                  
      //$leadclients        =  $leadclients->whereIN('agent_id',$arUser);
      //$leadclientstotal   =  $leadclientstotal->whereIN('agent_id',$arUser);
      if(!empty($request->input('get_invoices_count')) && $request->input('get_invoices_count') == 'getInvoiceCount'){

        $leadclients = $leadclients->selectSub(function($query) {
                        $query->select(DB::raw('MAX(`date`)'))
                            ->from('lead_status_history')
                            ->whereIn('status', ["'Invoiced','12','Invoice 30','Invoice 60','Invoice 90','Past Due','Payment OTW','18','19','20','21','22'"])
                            ->whereColumn('lead_id', '=', 'lead_client.id')
                            ->orderBy('id', 'asc')
                            ->limit(1);
                    }, 'newdate');
        
        $leadclientstotal  = $leadclientstotal->selectSub(function($query) {
                        $query->select(DB::raw('MAX(`date`)'))
                            ->from('lead_status_history')
                            ->whereIn('status', ['Invoiced', '12', 'Invoice 30', 'Invoice 60', 'Invoice 90', 'Past Due', 'Payment OTW', '18', '19', '20', '21', '22'])
                            ->whereColumn('lead_id', '=', 'lead_client.id')
                            ->orderBy('id', 'asc')
                            ->limit(1);
                    }, 'newdate');
        
      }else{
        $leadclients  = $leadclients->selectSub(function($query) {
            $query->select('date')
                ->from('lead_status_history')
                ->Where('status','=','Paid Out')
                ->Where('lead_id','=','lead_client.id')
                ->orderByRaw('id desc')
                ->limit(1);
        }, 'newdate');
        
        
        $leadclientstotal  = $leadclientstotal->selectSub(function($query) {
            $query->select('date')
                ->from('lead_status_history')
                ->Where('status','=','Paid Out')
                ->Where('lead_id','=','lead_client.id')
                ->orderByRaw('id desc')
                ->limit(1);
        }, 'newdate');
        
      }
        
      
      $search_start_date  = ''; 
      $search_end_date    = ''; 
      $searchform         = $request->input('search_agent');
    
    //echo "<pre>"; print_r($request->input('search_agent')); exit;
      if(!empty($searchform) && $searchform=='submit'){
          
             //echo $searchform; exit;
             
          $client_name          = $request->input('client_name');
          $client_id            = $request->input('client_id');
          $arr                  =  explode(" ", $client_name);

          @$c_fname             = $arr[0];
          @$c_lname             = $arr[1];
          $data['client_name']  = $client_name;

          if ($client_name !='') {
            $leadclients =  $leadclients->where('lead_client.fname',$c_fname);
            $leadclients =  $leadclients->where('lead_client.lname',$c_lname);
            $leadclientstotal =  $leadclientstotal->where('lead_client.fname',$c_fname);
            $leadclientstotal =  $leadclientstotal->where('lead_client.lname',$c_lname);
          }
          if ($client_id !='') {
            $leadclients =  $leadclients->where('lead_client.id',$client_id);
            $leadclientstotal =  $leadclientstotal->where('lead_client.id',$client_id);
            $data['client_id']  = $client_id;
          }

          //$statusarry         =   ['Invoiced','MIC','Lease Confirmed','Sold'];
          $statusarry         =   ['Paid Out','14'];
          $search_start_date  = setdbdate($request->input('search_start_date'));
          $search_end_date    = setdbdate($request->input('search_end_date'));

          if($search_start_date != '' && $search_end_date != ''){
        
            //$leadclients = $leadclients->whereIn('lead_status_history.status',$statusarry);
            $leadclients = $leadclients->whereIn('lead_client.curr_status',$statusarry);
            $leadclients = $leadclients->whereBetween('lead_client.newpaid_date',[$search_start_date,$search_end_date]);
        
           // $leadclientstotal =  $leadclientstotal->whereIn('lead_status_history.status',$statusarry);
           
            $leadclientstotal =  $leadclientstotal->whereIn('lead_client.curr_status',$statusarry);
            $leadclientstotal =  $leadclientstotal->whereBetween('lead_client.newpaid_date',[$search_start_date,$search_end_date]);
            $data['search_start_date']  = $search_start_date;
            $data['search_end_date']  = $search_end_date;
            
          }
      }elseif(!empty($request->input('get_invoices_count')) && $request->input('get_invoices_count') == 'getInvoiceCount'){

          $statusarry         =   ['Invoiced','12','Invoice 30','Invoice 60','Invoice 90','Past Due','Payment OTW','18','19','20','21','22'];
          $search_start_date  = setdbdate($request->input('invoice_start_date'));
          $search_end_date    = setdbdate($request->input('invoice_end_date'));

          if($search_start_date != '' && $search_end_date != ''){
        
            $leadclients = $leadclients->whereIn('lead_status_history.status',$statusarry);
            $leadclients = $leadclients->whereRaw("`lead_status_history`.`date` BETWEEN '$search_start_date' AND '$search_end_date'");
           // $leadclientstotal =  $leadclientstotal->whereIn('lead_status_history.status',$statusarry);
            $leadclientstotal =  $leadclientstotal->whereIn('lead_status_history.status',$statusarry);
            $leadclientstotal =  $leadclientstotal->whereRaw("`lead_status_history`.`date` BETWEEN '$search_start_date' AND '$search_end_date'");
          }
            // $data['search_start_date']  = $search_start_date;
            // $data['search_end_date']  = $search_end_date;
      }else{

       //$statusarry  =   ['Invoiced','MIC','Lease Confirmed','Sold'];
       $statusarry         =   ['Sold','Invoiced','Pay Received','MIC','Paid Out','9','12','13','14','11'];
       // print_r($statusarry); exit;
        //DB::enableQueryLog();
        //$leadclients =   $leadclients->whereIn('lead_status_history.status',$statusarry);
        $leadclients =   $leadclients->whereIn('lead_client.curr_status',$statusarry);
        // dd(DB::getQueryLog()); exit;
        //dd(DB::getQueryLog()); exit;
        //$leadclientstotal =  $leadclientstotal->whereIn('lead_status_history.status',$statusarry);
        $leadclientstotal =  $leadclientstotal->whereIn('lead_status_history.status',$statusarry);

      }
      
       // $leadclients =   $leadclients->whereIn('lead_client.curr_status',$statusarry);
        //$leadclientstotal =  $leadclientstotal->whereIn('lead_status_history.status',$statusarry);
        
        $leadclientsall = $leadclients->orderby('lead_client.'.$sort ,$direction)
                                    ->groupBy('lead_client.id')
                                    ->get();
        
        $leadclientPDF      =   $leadclients->orderby('lead_client.'.$sort ,$direction)
                                  ->groupBy('lead_client.id')
                                  ->paginate($items); 
                                  
                                  
        // DB::enableQueryLog();
        if(!empty($request->input('get_invoices_count')) && $request->input('get_invoices_count') == 'getInvoiceCount'){
          $leadclients    =  $leadclients->groupBy('lead_client.id','lead_client.fname','lead_client.lname','lead_client.payment','lead_client.paid_date','lead_client.paid_out','lead_client.incentive','users.name',
            'users.last_name','lead_client.invoiced_total','lead_client.agent_paid','lead_client.sendto','lead_client.curr_status','lead_client.status_history')->paginate($items);   
        } else {
          $leadclients    =  $leadclients->orderby('lead_client.'.$sort ,$direction)->groupBy('lead_client.id')->paginate($items);   
        }   

        // echo "<pre>"; print_r($leadclients); exit;
                                        
        $leadclientstotal = $leadclientstotal->orderby('lead_client.id','DESC')->distinct()->get();
        
        
        $income_detail = get_income_calculation_admin(json_decode(json_encode($leadclientstotal), true),'arincome');
        
        //echo "<pre>"; print_r($income_detail); exit;
        $tot_net          =   0;
        $tot_invoiced     =   0;
        $tot_paid         =   0;
        $outsatnginamt    =   0;
      
        $data['income_detail'] = income_details('ar',session('aragentsearch'))['ar_income'];
      
        $data['totalincome']        =   $tot_net;
        $data['outsatnginamt']      =   $outsatnginamt;
        $data['url']                =   'searcharincome';
        $data['search_start_date']  =   $search_start_date;
        $data['search_end_date']    =   $search_end_date;
        $data['leadclients']        =   $leadclients;
        $data['useraragents']       =   $useraragent;
        $data['search_start_date']  =   $search_start_date;
        $data['search_end_date']    =   $search_end_date;
        $data['reseturl']           =   'arincome';
        
        $data['total_incoe'] = $income_detail['company_income'];
        $data['total_invoice'] = $income_detail['rescommission'];
        $data['gross_pay'] = $income_detail['gross_revenue'];
        $data['outsatnginamt'] = $income_detail['outstanding'];
        $data['agent_income'] = $income_detail['agent_income']; 
        $data['company_income'] = $income_detail['company_income'];
        
        
        $data['leadclientPDF']      =   $leadclientPDF;
        $data['datasall']           =   $leadclients; //$leadclientsall;
        
    return view('agentincome.index',['data'=>$data])->withMembers($leadclients)->withItems($items);
}

public function arinvoicecount(Request $request){
      $id               = Auth::user()->id; 
      $user_role        = Auth::user()->user_role; 
      $role_for         =   "ARUSER";
      $items            =   $request->items ?? 25;
      
       unset($request->ids);
      
        $sort           = isset($request->sort) ? $request->sort : 'id';
        $direction  = isset($request->direction) ? $request->direction : 'DESC';
       
       // echo $sort; exit;
      
      Session::put('role_for',$role_for);

      $leadclients      = Leadclient::join('users', 'users.id', '=', 'lead_client.agent_id')
             ->join('lead_status_history','lead_status_history.lead_id','lead_client.id') ->select('lead_client.*', 'users.name', 'users.last_name',DB::raw('MAX(`lead_status_history`.`date`) as `paid_date`'));


      $leadclientstotal = Leadclient::join('users', 'users.id', '=', 'lead_client.agent_id')
                                ->leftjoin('lead_status_history','lead_status_history.lead_id','lead_client.id')->select('lead_client.*', 'users.name', 'users.last_name');
      $useraragent      = '';
      $data['show_agent_list'] = 0;
      if($user_role == SUPERADMIN){
      
        $useraragent = User::where('user_role','=',ARUSER)
                            ->where('status','=',1)
                            ->orderBy('name','ASC')
                            ->get();
        $data['show_agent_list'] = 1;
      }elseif($user_role == ADMIN){
  
        // $useraragent = User::where('user_role','=',ARUSER)
        //                     ->where('create_by_user','=',$id)
        //                     ->where('status','=',1)
        //                     ->orderBy('name','ASC')
        //                     ->get();

         $useraragent  =   User::where('user_role','=',4)
                  ->where('users.create_by_user','=',$id)
                  ->orderBy('name','ASC')
                  ->get();
        $data['show_agent_list']  = 1;   
      }else{

        $useraragent = User::where('user_role','=',ARUSER)
                            ->where('id','=',$id)
                            ->where('status','=',1)
                            ->orderBy('name','ASC')
                            ->get();
        Session::put('aragentsearch',$id);         
      }

      $firstuser =  $useraragent->first(); 
      if(session('aragentsearch') == ''){

        $firstuser =  $useraragent->first(); 
        if(!empty($firstuser)){   

           if($firstuser->id != ''){
              Session::put('aragentsearch', $firstuser->id);
           }
        }else{
          Session::put('aragentsearch',0);
        }
      }
       
      $agent_id           = session('aragentsearch'); 
      $arUser             =  User::select('id')
                                  ->where('assign_to_ar','=',$agent_id);
                                  
                                  
        $leadclients  = $leadclients->whereIn('agent_id',function ($query) use ($agent_id) {
            $query->select('id')->from('users')->Where('assign_to_ar',$agent_id);
       
        });
        
        $leadclientstotal  = $leadclientstotal->whereIn('agent_id',function ($query) use ($agent_id) {
            $query->select('id')->from('users')->Where('assign_to_ar',$agent_id);
       
        }); 
                                  
                                  
      //$leadclients        =  $leadclients->whereIN('agent_id',$arUser);
      //$leadclientstotal   =  $leadclientstotal->whereIN('agent_id',$arUser);

        $leadclients = $leadclients->selectSub(function($query) {
                        $query->select(DB::raw('MAX(`date`)'))
                            ->from('lead_status_history')
                            ->whereIn('status', ["'Invoiced','12','Invoice 30','Invoice 60','Invoice 90','Past Due','Payment OTW','18','19','20','21','22'"])
                            ->whereColumn('lead_id', '=', 'lead_client.id')
                            ->orderBy('id', 'asc')
                            ->limit(1);
                    }, 'newdate');
        
        $leadclientstotal  = $leadclientstotal->selectSub(function($query) {
                        $query->select(DB::raw('MAX(`date`)'))
                            ->from('lead_status_history')
                            ->whereIn('status', ['Invoiced', '12', 'Invoice 30', 'Invoice 60', 'Invoice 90', 'Past Due', 'Payment OTW', '18', '19', '20', '21', '22'])
                            ->whereColumn('lead_id', '=', 'lead_client.id')
                            ->orderBy('id', 'asc')
                            ->limit(1);
                    }, 'newdate');
        
      
      $search_start_date  = ''; 
      $search_end_date    = ''; 
      

          $statusarry         =   ["'Invoiced','12','Invoice 30','Invoice 60','Invoice 90','Past Due','Payment OTW','18','19','20','21','22'"];
          $search_start_date  = setdbdate($request->input('invoice_start_date'));
          $search_end_date    = setdbdate($request->input('invoice_end_date'));

          if($search_start_date != '' && $search_end_date != ''){
        
            //$leadclients = $leadclients->whereIn('lead_status_history.status',$statusarry);
            $leadclients = $leadclients->whereIn('lead_status_history.status',$statusarry);
            // $leadclients = $leadclients->whereRaw('`lead_status_history`.`status` IN (?)', [$statusarry]);
            // $leadclients = $leadclients->whereRaw("`lead_status_history`.`date` BETWEEN '$search_start_date' AND '$search_end_date'");
          //   DB::enableQueryLog();
          // $leadclients->paginate();
          // dd(DB::getQueryLog());
           // $leadclientstotal =  $leadclientstotal->whereIn('lead_status_history.status',$statusarry);
            $leadclientstotal =  $leadclientstotal->whereIn('lead_status_history.status',$statusarry);
            $leadclientstotal =  $leadclientstotal->whereRaw("`lead_status_history`.`date` BETWEEN '$search_start_date' AND '$search_end_date'");
          }
        
      
       // $leadclients =   $leadclients->whereIn('lead_client.curr_status',$statusarry);
        //$leadclientstotal =  $leadclientstotal->whereIn('lead_status_history.status',$statusarry);
        
        $leadclientsall = $leadclients->orderby('lead_client.'.$sort ,$direction)
                                    ->groupBy('lead_client.id')
                                    ->get();
        
        $leadclientPDF      =   $leadclients->orderby('lead_client.'.$sort ,$direction)
                                  ->groupBy('lead_client.id')
                                  ->paginate($items); 
                                  
                                  
        DB::enableQueryLog();
          $leadclients    =  $leadclients->groupBy('lead_client.id','lead_client.fname','lead_client.lname','lead_client.payment','lead_client.paid_date','lead_client.paid_out','lead_client.incentive','users.name',
            'users.last_name','lead_client.invoiced_total','lead_client.agent_paid','lead_client.sendto','lead_client.curr_status','lead_client.status_history')->paginate($items); 

            // dd(DB::getQueryLog());  

        // echo "<pre>"; print_r($leadclients); exit;
                                        
        $leadclientstotal = $leadclientstotal->orderby('lead_client.id','DESC')->distinct()->get();
        
        
        $income_detail = get_income_calculation_admin(json_decode(json_encode($leadclientstotal), true),'arincome');
        
        //echo "<pre>"; print_r($income_detail); exit;
        $tot_net          =   0;
        $tot_invoiced     =   0;
        $tot_paid         =   0;
        $outsatnginamt    =   0;
      
        $data['income_detail'] = income_details('ar',session('aragentsearch'))['ar_income'];
      
        $data['totalincome']        =   $tot_net;
        $data['outsatnginamt']      =   $outsatnginamt;
        $data['url']                =   'searcharincome';
        $data['search_start_date']  =   $search_start_date;
        $data['search_end_date']    =   $search_end_date;
        $data['leadclients']        =   $leadclients;
        $data['useraragents']       =   $useraragent;
        $data['search_start_date']  =   $search_start_date;
        $data['search_end_date']    =   $search_end_date;
        $data['reseturl']           =   'arincome';
        
        $data['total_incoe'] = $income_detail['company_income'];
        $data['total_invoice'] = $income_detail['rescommission'];
        $data['gross_pay'] = $income_detail['gross_revenue'];
        $data['outsatnginamt'] = $income_detail['outstanding'];
        $data['agent_income'] = $income_detail['agent_income']; 
        $data['company_income'] = $income_detail['company_income'];
        
        
        $data['leadclientPDF']      =   $leadclientPDF;
        $data['datasall']           =   $leadclients; //$leadclientsall;
        
    return view('agentincome.index',['data'=>$data])->withMembers($leadclients)->withItems($items);
}

/*
* @function : adminincome (to list all income for admin user.)
* @params: request data admin id
* @return : json data of all income reccords
* @modifiedby:  chintesh k.
*/ 
  public function manager_agent_income(Request $request){

      $id             =   Auth::user()->id; 
      $user_role      =   Auth::user()->user_role; 
      $manager        =   Auth::user()->manage_manager; 
      $manager_agent  =   Auth::user()->agents_to_manager;
      $agents         =   explode(',',$manager_agent); 
      $useraragent    =   array();
      $items          = $request->items ?? 5000000;
      
      if(empty($agents)){
        $agents       =   array(0);
      }

      $leadclients    =     Leadclient::join('users', 'users.id', '=', 'lead_client.agent_id')
                                        ->join('lead_status_history','lead_status_history.lead_id','lead_client.id')
                                        ->select('lead_client.*', 'users.name', 'users.last_name','lead_status_history.date as paid_date');


      $leadclientstotal =   Leadclient::join('users', 'users.id', '=', 'lead_client.agent_id')
                                  ->leftjoin('lead_status_history','lead_status_history.lead_id','lead_client.id')
                                  ->select('lead_client.*', 'users.name', 'users.last_name');

      if($user_role == AGENT){

        $useraragent  =   User::where('user_role','=',AGENT)
                                ->whereIn('id',$agents)
                                ->where('status','=',1)
                                ->orderBy('name','ASC')
                                ->get();

        if(session('aragentsearch') == ''){
          $firstuser =  $useraragent->first();  
          if(!empty($firstuser)){          
              if($firstuser->id != ''){
                Session::put('aragentsearch', $firstuser->id);
              }
          }
        }
       
        if(session('aragentsearch') != ''){

          $agent_id         =   session('aragentsearch');
          $leadclients      =   $leadclients->where('agent_id','=',session('aragentsearch'));

          $leadclientstotal =   $leadclientstotal->where('agent_id','=',session('aragentsearch'));
        } else{
          $agent_id         =   0;
          $leadclients      =   $leadclients->where('users.assign_to_ar','=',$id);
          $leadclientstotal =   $leadclientstotal->where('users.assign_to_ar','=',$id);
        } 
      }

      $search_start_date    = ''; 
      $search_end_date      = ''; 
      $searchform           = $request->input('search_agent');

      if(isset($searchform)){


        $statusarry        = ['Pay Received','13'];
        $search_start_date = setdbdate($request->input('search_start_date'));
        $search_end_date   = setdbdate($request->input('search_end_date'));

        if($search_start_date != '' && $search_end_date != ''){
           
          //$leadclients      = $leadclients->whereIn('lead_status_history.status',$statusarry);
          
          $leadclients = $leadclients->whereIn('lead_client.curr_status',$statusarry);
          $leadclients      = $leadclients->whereBetween('lead_status_history.date',[$search_start_date,$search_end_date]);
          
          //$leadclientstotal =  $leadclientstotal->whereIn('lead_status_history.status',$statusarry);
          $leadclientstotal = $leadclientstotal->whereIn('lead_client.curr_status',$statusarry);
          $leadclientstotal =  $leadclientstotal->whereBetween('lead_status_history.date',[$search_start_date,$search_end_date]);
        }
      }else{

        $statusarry       = ['Invoiced','Pay Received','Paid Out','Sold','Lease Confirmed','MIC','9','12','13','14','11','10'];
        $amtstatusarry    = ['Invoiced','Sold','Lease Confirmed','MIC','9','12','13','11','10'];
        //$statusarry       =   ['Invoiced','Pay Received','Paid Out','Sold'];
        // $leadclients      =   $leadclients->whereIn('lead_status_history.status',$statusarry);
        // $leadclientstotal =   $leadclientstotal->whereIn('lead_status_history.status',$amtstatusarry);
        
        
        $leadclients      =   $leadclients->whereIn('lead_client.curr_status',$statusarry);
        $leadclientstotal =   $leadclientstotal->whereIn('lead_client.curr_status',$amtstatusarry);
      }

      $leadclientPDF    =   $leadclients->orderby('lead_client.id','DESC')
                                      ->groupBy('lead_client.id')
                                      ->paginate($items);  
      $leadclients      =   $leadclients->orderby('lead_client.id','DESC')
                                      ->orderby('lead_client.id','ASC')
                                      ->groupBy('lead_client.id')
                                      ->paginate($items);

      $finalstatusarry   =   ['Invoiced','Sold','Lease Confirmed','MIC','9','12','11','10'];
      $leadclientstotal   =   $leadclientstotal->whereIn('curr_status', $finalstatusarry)
                                              ->orderby('lead_client.id','ASC')
                                              ->groupBy('lead_client.id')
                                              ->get();
      $tot_net          =   0;
      $tot_invoiced     =   0;
      $tot_paid         =   0;
      $outsatnginamt    =   0;
      $sc               =   count($leadclients);
      $newres_comm      =   0;

      foreach ($leadclientstotal  as $keytotal => $valuetotal) {
        // Implemented By Vinod Date 12/02/2020 start
        $caculation   =   $this->getcalculation($valuetotal->id);
        if(!empty($caculation)){
          $newres_comm  =   (float) $caculation['paidamount'];
        }else{
          $newres_comm  =   0;
        }
        $outsatnginamt += $newres_comm;
      }

      foreach( $leadclients  as $keytotal => $valuetotal) {
        // Implemented By Vinod Date 12/02/2020 start
        $caculation = $this->getcalculation($valuetotal->id);
        $valuetotal->payment  =   number_format((float)$valuetotal->payment, 2, '.', '');
        $arr                  =   explode("~~~~",$valuetotal->status_history);
        $paid_date            =   "";
        
        $leadcalculatdata     = Leadclientcalculation::where('leadclient_id','=',$valuetotal->id)
                                                    ->get()
                                                    ->first();
        $paid_amt             =   (float) $valuetotal->payment;
        $inc                  =   (float) $valuetotal->incentive;
                
        if(!empty($leadcalculatdata)){
          $leadcost   =   $leadcalculatdata->lead_cost;
        }else{
          $leadcost   =   0;
        }
       
        $user       =   User::find($valuetotal->agent_id);
        $invcostt   =   (float) $user->invoicing_cost;

        if(((float)$paid_amt-(float)$inc-(float)$leadcost-(float)$invcostt) > 0){
          $tot_net  += (float)$paid_amt-(float)$inc-(float)$leadcost-(float)$invcostt;
        }
        
        if($valuetotal->prop_id !=0 || $valuetotal->prop_id !=''){
        
          if (trim($valuetotal->commision)==""){
            $valuetotal->commision = 0;
          }

          if (trim($valuetotal->bonus) == ""){
            $valuetotal->bonus = 0;
          }

          if ($valuetotal->commision_unit == "1") {
             
            if ((float) $valuetotal->commision != 0 || (float) $valuetotal->commision!=0.00) {

                $tot_com          =   ((float)$valuetotal->rent_amount*((float)$valuetotal->commision/100));
                $total            =   (float) $tot_com + (float) $valuetotal->bonus;
                $commision_unit   =   "%";
            }else{
              $total  =   (float) $valuetotal->bonus;
            }
          }else{
            $total  =   (float) $valuetotal->commision + (float) $valuetotal->bonus;
          } 
        }else{

          if(trim($valuetotal->sold_amt)==""){
            $valuetotal->sold_amt = 0;
          }

          if(trim($valuetotal->house_comm)==""){
            $valuetotal->house_comm = 0;
          }   

          if(trim($valuetotal->house_bonus)==""){
            $valuetotal->house_bonus = 0;
          }   
            
          $total =  ((float) $valuetotal->sold_amt * ((float) $valuetotal->house_comm/100)) + (float) $valuetotal->house_bonus;
        }
        $tot_invoiced += $total;
        $tot_paid     += $paid_amt;
      }

      $data['totalincome']        = $tot_net;
      $data['outsatnginamt']      = number_format((float) $outsatnginamt, 2, '.', '');
      $data['url']                = 'searchmanagerincome';
      $data['search_start_date']  = $search_start_date;
      $data['search_end_date']    = $search_end_date;
      $data['leadclients']        = $leadclients;
      $data['useraragents']       = $useraragent;
      return view('manager.agent_income',['data'=>$data]);
  }

/*
* @function: getcalculation (to get the calculation of lead clint payment)
* @params: lead_id
* @return: calculated data in array
* @modifiedby: chintesh k.
**/    
  public static function getcalculation($id){

      $total            = 0;
      $total_cost       = 0;
      $net              = 0;

      $leadclient       =   Leadclient::find($id);
      $leadcalculatdata =   Leadclientcalculation::where('leadclient_id','=',$leadclient->id)->get()->first();
      

      $arr              =   explode("~~~~",$leadclient->status_history);

      $paid_rec = "";
      $paid_out = "";

      for ($i=0;$i<count($arr);$i++) {
         
          if((strpos($arr[$i],"Pay Received") > -1)) {
            $date       =   explode("~~",$arr[$i]);
            if($date[0]){
              $newdate    =  explode("-",$date[0]);
              if (str_contains($date[0], '-')) { 
                $newdate    =  explode("-",$date[0]);
              }else{
                $newdate    =  explode("/",$date[0]);
              }
              if(!empty($newdate[1]) && !empty($newdate[2]) && !empty($newdate[0])){

                $paid_rec  =   date('m/d/Y',strtotime($newdate[1].'-'.$newdate[0].'-'.$newdate[2]));
              }
            }
            break;
          } 
        }
        for ($i=0;$i<count($arr);$i++) {
          if((strpos($arr[$i],"Paid Out") > -1) ) {
            $date       =   explode("~~",$arr[$i]);

            if($date[0]){
              $newdate    =  explode("-",$date[0]);
              if (str_contains($date[0], '-')) { 
                $newdate    =  explode("-",$date[0]);
              }else{
                $newdate    =  explode("/",$date[0]);
              }
              if(!empty($newdate[1]) && !empty($newdate[2]) && !empty($newdate[0])){

                $paid_out   =  date('m/d/Y',strtotime($newdate[1].'-'.$newdate[0].'-'.$newdate[2]));
              }
            }
            break;
          }  
      }

      $dis=0;
      if($leadclient->status_history!=''){
          
          $getfirst=explode('~~~~',$leadclient->status_history);
          if($getfirst[0]!=''){

            $getdate  =   explode("~~",$getfirst[0]);
            
            $date1 = strtotime(date('Y-m-d'));  
            $date2 = strtotime(date('Y-m-d',strtotime($getdate[0])));  


            $diff         =   abs($date2 - $date1); 
            $statusdays   =   ceil($diff/ 86400); 
            $dis          =   $statusdays; 
          } 
      }

      if($leadclient->prop_id!=0 || $leadclient->prop_id!=''){
        
        if(trim($leadclient->commision)==""){
          $leadclient->commision=0;
        }

        if(trim($leadclient->bonus)==""){
          $leadclient->bonus=0;
        }

        if($leadclient->commision_unit=="1") {
           
            if ($leadclient->commision!=0 || $leadclient->commision!=0.00) {
                  
              $commision        =   (float) $leadclient->commision;
              $tot_com          =   ((float) $leadclient->rent_amount*((float) $commision/100));
              $total            =   (float) $tot_com+(float) $leadclient->bonus;
              $commision_unit   =   "%";
            }else{
              $total            = (float) $leadclient->bonus;
            }
        }else{
           
          $tot_com          =   (float) $leadclient->commision;
          $total            =   (float) $leadclient->commision+(float) $leadclient->bonus;
          $commision_unit   =   "Flat Fee";
        } 
      }else{

        if(trim($leadclient->sold_amt)==""){
          $leadclient->sold_amt=0;
        }

        if(trim($leadclient->house_comm)==""){
          $leadclient->house_comm=0;
        }   

        if(trim($leadclient->house_bonus)==""){
          $leadclient->house_bonus=0;
        }   
            
        $total  =  ((float) $leadclient->sold_amt*((float) $leadclient->house_comm/100))+(float) $leadclient->house_bonus;
        $tot_com          =   0;
        $commision_unit   =   "Flat Fee";
      }


      $agent_invoice_cost =   (float) $leadclient->invoicing_cost;
      $paid_amt           =   (float) $leadclient->payment;
      $inc                =   (float) $leadclient->incentive;
      if(!empty($leadcalculatdata)){
        $lead_cost  = (float) $leadcalculatdata->lead_cost;
      }else{
        $lead_cost  = 0;
      }

      // Nane - 23-11
      $agentleaddetail    =   User::find($leadclient->agent_id);

      $statusarrychk      =   ['Pay Received', 'Paid Out','13','14'];
        if(in_array($leadclient->curr_status, $statusarrychk) && $leadclient->inv_date < '2021-12-01'){
            $leadclient->invoicing_cost =   (float) $leadclient->invoicing_cost;
        }else{
          // Calculation updated by swapnil
          // $leadclients->invoicing_cost = (float) transaction_calculation($leadclient->transaction_type_id,$agentleaddetail,'invoicing_cost');
            if($leadclient->transaction_type_id == 1) {
              $leadclient->invoicing_cost =  (float) $agentleaddetail->al_invoicing_cost;
            } else if($leadclient->transaction_type_id == 2){
              $leadclient->invoicing_cost =  (float) $agentleaddetail->rl_invoicing_cost;
            } else if($leadclient->transaction_type_id == 3){
              $leadclient->invoicing_cost = (float) $agentleaddetail->rs_invoicing_cost;
            } else if($leadclient->transaction_type_id == 5){
              $leadclient->invoicing_cost = (float) $agentleaddetail->cl_invoicing_cost;
            } else if($leadclient->transaction_type_id == 6){
              $leadclient->invoicing_cost = (float) $agentleaddetail->cs_invoicing_cost;
            } else {
              $leadclient->invoicing_cost = (float) $agentleaddetail->invoicing_cost;
            }
          // calculation end
        }
      //

      $total_cost   =   (float)$lead_cost+(float) $leadclient->invoicing_cost;
      $net          =   (float)$paid_amt - (float)$inc -  (float)$lead_cost - (float) $leadclient->invoicing_cost;

      $total        =   number_format((float)$total, 2, '.', '');
      $total_cost   =   number_format((float)$total_cost, 2, '.', '');
      $net          =   number_format((float)$net, 2, '.', '');
      
      if($total<0){
       $total='';
      }

      if($total_cost<0){
        $total_cost='';
      }

      if($net<0){
        $net='';
      }
    
      $data['paid_date']    =   $paid_rec;
      $data['paid_out']     =   $paid_out;
      $data['paidamount']   =   $total;
      $data['ledinvcast']   =   $total_cost;
      $data['netincome']    =   $net;
      $data['dis']          =   $dis;

      return $data;
  }

  public function getPayRollDetail(Request $req){
        
    $agent_id=Auth::user()->id;
    $agent_role=Auth::user()->user_role;
    $start_date = date('Y-m-d',strtotime($req->input('payroll_start_date')));
    $end_date  =  date('Y-m-d',strtotime($req->input('payroll_end_date')));
   //DB::enableQueryLog();
    if($agent_role==ARUSER){
      $users=User::where('assign_to_ar',$agent_id)->where('status',1)->orderBy('name','ASC')->get();
    }else if($agent_role==ADMIN){
      $users=User::where('create_by_user',$agent_id)->where('status',1)->orderBy('name','ASC')->get();
    }else if($agent_role==SUPERADMIN){
       $users=User::where('user_role',AGENT)->where('status',1)->orderBy('name','ASC')->get();
    }else{
      $users=User::where('id',$agent_id)->where('status',1)->orderBy('name','ASC')->get();
    }
       //dd(DB::getQueryLog()); exit;

    $payROllDetails   =   array();
    foreach($users as $agent){
        //DB::enableQueryLog();
        $leadclients  =   Leadclient::select('lead_client.*')
                                      ->where(function ($query) use ($agent,$agent_id){
                                          $query->where('agent_id',$agent->id)
                                                ->orWhere('source_id',$agent_id);
                                          })->get();
                                          
                                          //dd(DB::getQueryLog()); exit;
        $payroll  = array();
        $total    = 0;

            foreach ($leadclients as $leadclient) {
            
            //\DB::enableQueryLog(); 
          $status_history = Leasstatus_history::where('lead_id',$leadclient->id)
                                            ->whereIn('status',['Pay Received','paid Out','13','14'])
                                            ->whereBetween('date',array($start_date,$end_date))
                                            ->orderBy('date','DESC')
                                            ->first();
             //dd(\DB::getQueryLog()); exit;
            if(!empty($status_history)):  
              $agentleaddetail    = User::find($leadclient->agent_id);
              $agent_status       = $agentleaddetail->user_role;
              // $unit               = $agentleaddetail->unit;
              $unit               = transaction_calculation($leadclient->transaction_type_id,$agentleaddetail,'unit');
              // $agent_split_hous   = $agentleaddetail->broker_cost;
              $agent_split_hous   = transaction_calculation($leadclient->transaction_type_id,$agentleaddetail,'broker_cost');
              // $agent_split        = transaction_calculation($leadclient->transaction_type_id,$agentleaddetail,'unit');$agentleaddetail->broker_cost;
              $agent_split        = transaction_calculation($leadclient->transaction_type_id,$agentleaddetail,'broker_cost');
              // $agent_invoice_cost = $agentleaddetail->invoicing_cost;
              $agent_invoice_cost = transaction_calculation($leadclient->transaction_type_id,$agentleaddetail,'invoicing_cost');
              $incentive          = $leadclient->incentive;
              $sold_amt           = $leadclient->sold_amt;
              $house_comm         = $leadclient->house_comm;
              $house_bonus        = $leadclient->house_bonus;
              $distributor_name   = '';
              if($leadclient->source_id!=0 || $leadclient->source_id!='' || $leadclient->source_id!=NULL){

                $agentsourcedetail    = User::find($leadclient->source_id);

                if($agentsourcedetail->manage_distributor==1){
                  $agent_status     = 5; 
                  $distributor_name = $agentsourcedetail->name.' '.$agentsourcedetail->last_name; 
                }
              }

              $lead     =   Distributorleadmarket::find($leadclient->lead_id_orig);

            /*======== Remi ===========*/
            //calculat lead cost and agent paid amount
            $lead_cost  =  0;
            $agent_paid = 0;
            $house_cost =  0;
            //for calculate house cost
               
            if($leadclient->sold_amt > 0){
              $totalcommission = (float)$sold_amt*((float) $house_comm/100)+(float)$house_bonus;
            }

            if(!empty($lead) && $agent_status == 5){ 
            
              $dis_split = $lead->lead_cost;
              $dis_split_unit = $lead->unit;

              if($leadclient->payment != 0){    
                if($dis_split_unit == 1){
                    // $row1['lead_cost'] =  $row1['payment'] * $agent_split /  100 ;
                    $dis_split = (float) $dis_split/100;
                    $leadclient->lead_cost = ((float) $leadclient->payment - (float) $agent_invoice_cost) *  (float) $dis_split;
                    $lead_cost =((float) $leadclient->payment - (float) $agent_invoice_cost) *  (float) $dis_split;
                    $leadclient->agent_paid =  (float) $leadclient->payment - (float) $agent_invoice_cost - (float) $incentive - (float)$leadclient->lead_cost;
                  $agent_paid=(float) $leadclient->payment - (float) $agent_invoice_cost - (float) $incentive - (float)$leadclient->lead_cost;
                }else{      
                  $lead_cost =$dis_split; 
                  $leadclient->lead_cost = $dis_split; 
                  $leadclient->agent_paid =  (float) $leadclient->payment - (float) $agent_invoice_cost - (float) $incentive - (float) $leadclient->lead_cost;
                  $agent_paid=  (float) $leadclient->payment - (float) $agent_invoice_cost - (float) $incentive - (float) $leadclient->lead_cost;
                }
              }

              if($leadclient->sold_amt != 0){
                  
                if($dis_split_unit == 0){
                      $dis_split = (float) $dis_split/100;
                      $house_cost = (float) $totalcommission *  (float) $dis_split;   
                }else{
                    $house_cost = (float) $dis_split;
                }
              }
            }else{

              if($leadclient->payment != 0){  
                if($unit == 0){
                  $agent_split  = (float) $agent_split_hous/100;
                  $lead_cost    = ((float) $leadclient->payment - (float) $agent_invoice_cost) *  (float) $agent_split;
                  $agent_paid   =  (float) $leadclient->payment - (float) $agent_invoice_cost - (float) $incentive - (float)$lead_cost;
                }else{
                  $lead_cost  =  (float) $agent_split;
                  $agent_paid =  (float) $leadclient->payment - (float) $agent_invoice_cost - (float) $incentive - (float)$lead_cost;
                }
              }

              if($leadclient->sold_amt != 0){  
                  if($unit == 0){
                    $agent_split_hous = (float)  $agent_split_hous/100;
                    $house_cost       = (float) $totalcommission *  (float) $agent_split_hous;
                  }else{
                    $house_cost       =  (float) $agent_split_hous;
                  }
              }
            }
         
         
         /*======== End Remi=========*/
          
          if($leadclient->transaction_type_id == 1){

              $commission_unit = $leadclient->commision_unit;
              $commission      = $leadclient->commision;
              $bonus           = $leadclient->bonus;
              $rent_amount     = $leadclient->rent_amount;

              if($rent_amount==''){
                $rent_amount=0;
              }

              if($bonus==''){
                $bonus=0;
              }

              if($commission==''){
                $commission=0;
              }
             
              if($commission_unit == '1') {

                  if ($commission > 0) {
                    $tot_com = (float) $rent_amount * ((float) $commission/100);
                    $invoiced_total = (float) $tot_com + (float) $bonus;                                              
                  } else {
                    $invoiced_total = (float) $bonus;
                  }
              } else {
                $invoiced_total = (float) $commission + (float) $bonus;
              }
          }elseif($leadclient->transaction_type_id==2 || $leadclient->transaction_type_id==3){
             
            if($leadclient->sold_amt==''){
              $leadclient->sold_amt=0;
            }

            if($leadclient->house_comm==''){
              $leadclient->house_comm=0;
            }

            if($leadclient->house_bonus==''){
              $leadclient->house_bonus=0;
            }
            
            if($leadclient->house_comm > 0) {
              $tot_com        = (float) $leadclient->sold_amt * ((float) $leadclient->house_comm/100);
              $invoiced_total = (float) $tot_com+(float) $leadclient->house_bonus;
            } else {
              $invoiced_total = (float) $leadclient->house_bonus;
            }
          }else{
            $invoiced_total=0;
          }
          /*======== Remi ===========*/
          //calculate invoice amount
          
          if($agent_paid > 0){
            $leadcalculatdata['distributor_name']   = '';
            $leadcalculatdata['client']             = $leadclient->id;
            $leadcalculatdata['transaction_type_id']= $leadclient->transaction_type_id;
            $leadcalculatdata['amount']             = $agent_paid; 
            $leadcalculatdata['description']        = 'Agent Commission';
            $payroll[]                              = $leadcalculatdata;
            $total                                  +=  $agent_paid;
          }

          if($lead_cost > 0 && $agent_status == 5){

            $leadcalculatdata['distributor_name'] =   $distributor_name; 
            $leadcalculatdata['client']           =   $leadclient->id;
            $leadcalculatdata['transaction_type_id']= $leadclient->transaction_type_id;
            $leadcalculatdata['amount']           =   $lead_cost; 
            $leadcalculatdata['description']      =   'Referral';
            $payroll[]                            =   $leadcalculatdata;
            $total                                +=  $lead_cost;
          }
     
          endif;
        }
       
            //if(!empty($payroll)){ 
                $finalData['agent_id']=$agent->id;
                $finalData['source']=$agent_id;
                
              $finalData['agent_name']=$agent->name.' '.$agent->last_name;
               $finalData['payoll']=(!empty($payroll)?$payroll:[]);
               $finalData['total']=$total;
                $payROllDetails[]=$finalData;
            //}
        
        }
      
     // echo "<pre>"; print_r($payROllDetails); exit;

        $pdf = PDF::loadView('agentincome.payroll',['data'=>$payROllDetails,'from_date'=>$start_date,'end_date'=>$end_date])->setPaper('a4', 'landscape');
             
        //return view('agentincome.payroll',['data'=>$payROllDetails,'from_date'=>$start_date,'end_date'=>$end_date]);
                 
                 
      // $pdf->save(storage_path('pdf/'.date('Y-m-d').'_'.$agent_id.'_payroll_pdf.pdf'));

        $pdfContents = $pdf->output();
        $path = pdf();
        $filename = time().'-'.date('Y-m-d').'_'.$agent_id.'_payroll_pdf.pdf';

        bucket_upload($pdfContents,$path,$filename,1);

      if($_SERVER['SERVER_NAME'] == 'development.realm.clientmgr.us' || $_SERVER['SERVER_NAME'] == 'development.flexsin.clientmgr.us' || $_SERVER['SERVER_NAME'] == 'realm.clientmgr.us'){

          // $link = PDFPATH.'../storage/pdf/'.date('Y-m-d').'_'.$agent_id.'_payroll_pdf.pdf';
        $link = bucket_show($path,$filename);
      }else{
          // $link = PDFPATH.'../crmrewrite/storage/pdf/'.date('Y-m-d').'_'.$agent_id.'_payroll_pdf.pdf';
        $link = bucket_show($path,$filename);
      }
      
      echo '{"status":true,"link":"'.$link.'"}';

    }
    
    
public function downloadpayroll(Request $req){
        
    $agent_id=Auth::user()->id;
    $agent_role=Auth::user()->user_role;
    $start_date = date('Y-m-d',strtotime($_GET['start_date']));
    $end_date  =  date('Y-m-d',strtotime($_GET['end_date']));
    
    
    //echo "start_date ".$_GET['start_date']." end_date ".$end_date; exit;
    
   //DB::enableQueryLog();
    if($agent_role==ARUSER){
      $users=User::where('assign_to_ar',$agent_id)
      //->where('status',1)
      ->orderBy('name','ASC')->get();
    }else if($agent_role==ADMIN){
        //DB::enableQueryLog(); 
      $users=User::where('create_by_user',$agent_id)
      //->where('status',1)
      ->orderBy('name','ASC')->get();
      //dd(DB::getQueryLog()); exit;
      
    }else if($agent_role==SUPERADMIN){
       $users=User::where('user_role',AGENT)
       //->where('status',1)
       ->orderBy('name','ASC')->get();
    }else{
      $users=User::where('id',$agent_id)
      //->where('status',1)
      ->orderBy('name','ASC')->get();
    }
       
     
        //$users = (json_decode(json_encode($users), true));
       
        //echo "<pre>"; print_r($users); exit;
    $payROllDetails   =   array();
    foreach($users as $agent){
        //DB::enableQueryLog();
        
        //echo "select `lead_client`.*,users.manage_distributor,lead_client.invoiced_total,agent_paid,lead_client.curr_status,lead_client.deducation,lead_client.incentive,`lead_status_history`.`date` as `paid_date`,`users`.`name` as fname, `users`.`last_name` as lname,(select date from lead_status_history where status='Paid Out' and lead_id=lead_client.id order by id desc limit 1)as newdate from `lead_client` inner join `users` on `users`.`id` = `lead_client`.`agent_id` inner join `lead_status_history` on `lead_status_history`.`lead_id` = `lead_client`.`id` where lead_client.agent_id in($agent->id) and lead_client.curr_status IN('Paid Out','14') and lead_status_history.date BETWEEN '$start_date' and '$end_date' GROUP BY lead_client.id order by lead_client.id DESC"; exit;
        
       $leadclients = DB::select("select `lead_client`.*,users.manage_distributor,lead_client.invoiced_total,agent_paid,lead_client.curr_status,lead_client.deducation,lead_client.incentive,`lead_status_history`.`date` as `paid_date`,`users`.`name` as fname, `users`.`last_name` as lname,(select date from lead_status_history where status='Paid Out' and lead_id=lead_client.id order by id desc limit 1)as newdate from `lead_client` inner join `users` on `users`.`id` = `lead_client`.`agent_id` inner join `lead_status_history` on `lead_status_history`.`lead_id` = `lead_client`.`id` where lead_client.agent_id in($agent->id) and lead_client.curr_status IN('Pay Received','13') and lead_status_history.date BETWEEN '$start_date' and '$end_date' GROUP BY lead_client.id order by lead_client.id DESC");
        
        
        //$leadclients = DB::select("select lead_client.*,(select date from lead_status_history where status='Paid Out' and lead_id=lead_client.id order by id desc limit 1)as newdate from `lead_client` where lead_client.agent_id in($agent->id) and lead_client.curr_status IN('Paid Out','14') and lead_client.transaction_type_id!='0' GROUP BY lead_client.id having newdate BETWEEN '$start_date' and '$end_date' order by lead_client.id DESC");
        
        
        // $leadclients  =   Leadclient::select('lead_client.*')
        //      ->where(function ($query) use ($agent,$agent_id){
        //  $query->where('agent_id',$agent->id);
        //           // ->orWhere('source_id',$agent_id);
        //      })->get();
                                          
        // //   select `lead_client`.* from `lead_client` where (`agent_id` = ?)
                                          
        // dd(DB::getQueryLog()); exit;
        
        
        $payroll  = array();
        $total    = 0;
            //array_push($payROllDetails,$leadclients); 
            foreach ($leadclients as $leadclient) {
            
            //\DB::enableQueryLog(); 
                //   $status_history = Leasstatus_history::where('lead_id',$leadclient->id)->whereIn('status',['paid Out','14'])
                //              ->whereBetween('date',array($start_date,$end_date))
                //              ->orderBy('date','DESC') ->first();
        
        
             //dd(\DB::getQueryLog()); exit;
            if(!empty($leadclient->curr_status)):  
              $agentleaddetail    = User::find($leadclient->agent_id);
              $agent_status       = $agentleaddetail->user_role;
              // $unit               = $agentleaddetail->unit;
              // $agent_split_hous   = $agentleaddetail->broker_cost;
              // $agent_split        = $agentleaddetail->broker_cost;
              // $agent_invoice_cost = $agentleaddetail->invoicing_cost;
              $unit               = transaction_calculation($leadclient->transaction_type_id,$agentleaddetail,'unit');
              $agent_split_hous   = transaction_calculation($leadclient->transaction_type_id,$agentleaddetail,'broker_cost');
              $agent_split        = transaction_calculation($leadclient->transaction_type_id,$agentleaddetail,'broker_cost');
              $agent_invoice_cost = transaction_calculation($leadclient->transaction_type_id,$agentleaddetail,'invoicing_cost');
              $incentive          = $leadclient->incentive;
              $sold_amt           = $leadclient->sold_amt;
              $house_comm         = $leadclient->house_comm;
              $house_bonus        = $leadclient->house_bonus;
              $distributor_name   = '';
              if($leadclient->source_id!=0 || $leadclient->source_id!='' || $leadclient->source_id!=NULL){

                $agentsourcedetail    = User::find($leadclient->source_id);

                if($agentsourcedetail->manage_distributor==1){
                  $agent_status     = 5; 
                  $distributor_name = $agentsourcedetail->name.' '.$agentsourcedetail->last_name; 
                }
              }

              $lead     =   Distributorleadmarket::find($leadclient->lead_id_orig);

            /*======== Remi ===========*/
            //calculat lead cost and agent paid amount
            $lead_cost  =  0;
            $agent_paid = 0;
            $house_cost =  0;
            //for calculate house cost
               
            if($leadclient->sold_amt > 0){
              $totalcommission = (float)$sold_amt*((float) $house_comm/100)+(float)$house_bonus;
            }

            if(!empty($lead) && $agent_status == 5){ 
            
              $dis_split = $lead->lead_cost;
              $dis_split_unit = $lead->unit;

              if($leadclient->payment != 0){    
                if($dis_split_unit == 1){
                    // $row1['lead_cost'] =  $row1['payment'] * $agent_split /  100 ;
                    $dis_split = (float) $dis_split/100;
                    $leadclient->lead_cost = ((float) $leadclient->payment - (float) $agent_invoice_cost) *  (float) $dis_split;
                    $lead_cost =((float) $leadclient->payment - (float) $agent_invoice_cost) *  (float) $dis_split;
                    $leadclient->agent_paid =  (float) $leadclient->payment - (float) $agent_invoice_cost - (float) $incentive - (float)$leadclient->lead_cost;
                  $agent_paid=(float) $leadclient->payment - (float) $agent_invoice_cost - (float) $incentive - (float)$leadclient->lead_cost;
                }else{      
                  $lead_cost =$dis_split; 
                  $leadclient->lead_cost = $dis_split; 
                  $leadclient->agent_paid =  (float) $leadclient->payment - (float) $agent_invoice_cost - (float) $incentive - (float) $leadclient->lead_cost;
                  $agent_paid=  (float) $leadclient->payment - (float) $agent_invoice_cost - (float) $incentive - (float) $leadclient->lead_cost;
                }
              }

              if($leadclient->sold_amt != 0){
                  
                if($dis_split_unit == 0){
                      $dis_split = (float) $dis_split/100;
                      $house_cost = (float) $totalcommission *  (float) $dis_split;   
                }else{
                    $house_cost = (float) $dis_split;
                }
              }
            }else{

              if($leadclient->payment != 0){  
                if($unit == 0){
                  $agent_split  = (float) $agent_split_hous/100;
                  $lead_cost    = ((float) $leadclient->payment - (float) $agent_invoice_cost) *  (float) $agent_split;
                  $agent_paid   =  (float) $leadclient->payment - (float) $agent_invoice_cost - (float) $incentive - (float)$lead_cost;
                }else{
                  $lead_cost  =  (float) $agent_split;
                  $agent_paid =  (float) $leadclient->payment - (float) $agent_invoice_cost - (float) $incentive - (float)$lead_cost;
                }
              }

              if($leadclient->sold_amt != 0){  
                  if($unit == 0){
                    $agent_split_hous = (float)  $agent_split_hous/100;
                    $house_cost       = (float) $totalcommission *  (float) $agent_split_hous;
                  }else{
                    $house_cost       =  (float) $agent_split_hous;
                  }
              }
            }
         
         
         /*======== End Remi=========*/
          
          if($leadclient->transaction_type_id == 1){

              $commission_unit = $leadclient->commision_unit;
              $commission      = $leadclient->commision;
              $bonus           = $leadclient->bonus;
              $rent_amount     = $leadclient->rent_amount;

              if($rent_amount==''){
                $rent_amount=0;
              }

              if($bonus==''){
                $bonus=0;
              }

              if($commission==''){
                $commission=0;
              }
             
              if($commission_unit == '1') {

                  if ($commission > 0) {
                    $tot_com = (float) $rent_amount * ((float) $commission/100);
                    $invoiced_total = (float) $tot_com + (float) $bonus;                                              
                  } else {
                    $invoiced_total = (float) $bonus;
                  }
              } else {
                $invoiced_total = (float) $commission + (float) $bonus;
              }
          }elseif($leadclient->transaction_type_id==2 || $leadclient->transaction_type_id==3){
             
            if($leadclient->sold_amt==''){
              $leadclient->sold_amt=0;
            }

            if($leadclient->house_comm==''){
              $leadclient->house_comm=0;
            }

            if($leadclient->house_bonus==''){
              $leadclient->house_bonus=0;
            }
            
            if($leadclient->house_comm > 0) {
              $tot_com        = (float) $leadclient->sold_amt * ((float) $leadclient->house_comm/100);
              $invoiced_total = (float) $tot_com+(float) $leadclient->house_bonus;
            } else {
              $invoiced_total = (float) $leadclient->house_bonus;
            }
          }else{
            $invoiced_total=0;
          }
          /*======== Remi ===========*/
          //calculate invoice amount
            
            
            $leadcalculatdata['distributor_name']   = $distributor_name;
            $leadcalculatdata['client']             = $leadclient->id;
            $leadcalculatdata['transaction_type_id']= $leadclient->transaction_type_id;
            $leadcalculatdata['amount']             = $agent_paid; 
            
            $leadcalculatdata['description']        = 'Agent Commission';
            
            $payroll[]                              = $leadcalculatdata;
            $total                                  +=  $agent_paid;
            
            
        //   if($agent_paid > 0){
        //     $leadcalculatdata['distributor_name']   = '';
        //     $leadcalculatdata['client']             = $leadclient->id;
        //     $leadcalculatdata['transaction_type_id']= $leadclient->transaction_type_id;
        //     $leadcalculatdata['amount']             = $agent_paid; 
        //     $leadcalculatdata['description']        = 'Agent Commission';
        //     $payroll[]                              = $leadcalculatdata;
        //     $total                                  +=  $agent_paid;
        //   }

          if($lead_cost > 0 && $agent_status == 5){

            $leadcalculatdata['distributor_name'] =   $distributor_name; 
            $leadcalculatdata['client']           =   $leadclient->id;
            $leadcalculatdata['transaction_type_id']= $leadclient->transaction_type_id;
            $leadcalculatdata['amount']           =   $lead_cost; 
            $leadcalculatdata['description']      =   'Referral';
            $payroll[]                            =   $leadcalculatdata;
            $total                                +=  $lead_cost;
          }
     
          endif;
        }
       
            //if(!empty($payroll)){ 
                $finalData['agent_id']=$agent->id;
                $finalData['source']=$agent_id;
                
              $finalData['agent_name']=$agent->name.' '.$agent->last_name;
               $finalData['payoll']=(!empty($payroll)?$payroll:[]);
               $finalData['total']=$total;
                $payROllDetails[]=$finalData;
            //}
        
        }
      
    $data = $payROllDetails;
    
   
    $result = array();
    $client = array();
    foreach($data as $row) { 
        $total=array();
        
        $total1=array();
        
        if(empty($row['payoll'])){
         continue;
        } 
        
        $start=1; $end=count($row['payoll']);
        
        array_sort_by_column($row['payoll'],'description');
   
        //die;
        $count =0;
        
        foreach($row['payoll'] as $key => $payroll){ 
            
            array_push($client,$payroll['client']);
            if($payroll['description']=='Referral'){
                $amount = lead_calculate($payroll['client'])['lead_cost'];
                if(empty($amount)){
                    continue;
                }
            
            }
            
            $datas['Name']='';
            
            if($start==1){
                $datas['Name'] =  $row['agent_name'];
            }
             
            if($payroll['description']=='Referral'){
               
                if($row['payoll'][$key]['description']!=$row['payoll'][$key-1]['description']){
                        $datas['Name'] = $payroll['distributor_name'];
                        
                    }else{
                        
                    }
            }
            
            $datas['Client'] = $payroll['client'];
            
            
            if($payroll['transaction_type_id']==1){
                $datas['Transaction'] = "Apartment Lease";
            }
            if($payroll['transaction_type_id']==2){
                $datas['Transaction'] = "Residential Lease";
            }
            if($payroll['transaction_type_id']==3){
                $datas['Transaction'] = "Residential Sale";
            }
            if($payroll['transaction_type_id']==5){
                $datas['Transaction'] = "Commercial Lease";
            }
            if($payroll['transaction_type_id']==6){
                $datas['Transaction'] = "Commercial Sale";
            }
            
            
            $datas['Description'] = $payroll['description'];
            
            
            if($payroll['description']=='Referral'){
                $amount = lead_calculate($payroll['client'])['lead_cost'];
            }else{
                $amount = (lead_calculate($payroll['client'])['agent_paid']);
            }
            
            $datas['Amount'] = ($amount);
            
            
            if($payroll['description']=='Referral'){
                array_push($total1,$amount);
                //$datas['description_type'] = 'Referral';
               
            }else{
                array_push($total,$amount);
                //$datas['description_type'] = 'Agent Commission';
            }
        
            $datas['Total'] = '';
            if((count($row['payoll'])-1)<=$key){
              
            }else{
              
                if($row['payoll'][$key]['description']!=$row['payoll'][$key+1]['description']){
                      
                    $datas['Total'] = (array_sum($total));
                      
                }
            }
            
            
            if($start==$end){
          
                if($payroll['description']=='Referral'){
                    //echo '$'.number_format($amount,2);
                    
                    if($row['payoll'][$key]['description']!=$row['payoll'][$key-1]['description']){
                        
                        
                    }else{
                       $datas['Total'] = (array_sum($total1)); 
                    }
                     
                     
                }else{
                    $datas['Total'] = (array_sum($total));
                }
                
            }
            
            array_push($result,$datas);
          $start++;
        }
         
    }
    
    
   // echo"<pre>";print_r($client); exit;
    
   // echo "<pre>"; print_r(array_sum(array_column($result,'Total'))); exit;

    header("Content-type: application/csv");
    header("Content-Disposition: attachment; filename=payroll_export_data-".date('Ymd') .".csv");
    $fp = fopen('php://output', 'w');

    
    $delimiter="\t";
     
     
    $handle = fopen( 'php://output', 'w' );
     
    fputcsv($fp,['Name','Client','Transaction','Description','Amount','Total']);
    foreach ($result as $row) {
        fputcsv($fp, $row);
    }
    
     fputcsv($fp,['','','','','Total',array_sum(array_column($result,'Total'))]);
    fclose($fp);

    
//echo "<pre>"; print_r($result); exit;
    }

    public function searchClientName(Request $request)
    { 

      $name         = $request->inputVal;
      
      $fname = explode(' ',$name)[0];
      $lname = (!empty(explode(' ',$name)[1])?explode(' ',$name)[1]:'');
      
      //\DB::enableQueryLog();
    //   $leadclients  = Leadclient::where(function($q) use($name) {
    //                     $q->select('*')
    //                     ->where('fname', 'LIKE', ''.$name.'%')
    //                     ->orWhere('lname', 'LIKE', ''.$name.'%');
    //                 })->get();
    
    
     $leadclients = Leadclient::where(function($q) use($name) {
                        $q->select('*');
                    });
                    
               $leadclients  = $leadclients->where('fname', 'LIKE', ''.$fname.'%');
               
               if(!empty($lname)){
                   $leadclients  = $leadclients->where('lname', 'LIKE', ''.$lname.'%');
               }
                
               $leadclients = $leadclients->groupBy('lname')->get();
                   
            //dd(\DB::getQueryLog()); exit;

      if (count($leadclients)) {
        foreach ($leadclients as $keyser => $valueser) {
          echo "<p style='padding-left:5px;'>" .$valueser->fname.' '.$valueser->lname."</p>";
        }
      } else{
        echo "<p style='padding-left:5px;'>No matches found</p>";
      }
    }

  public function downloadReport(Request $request){
    $agent_id     = Auth::user()->id;
    $agent_role   = Auth::user()->user_role;
    $start_date   = date('Y-m-d',strtotime($_GET['report_start_date']));
    $end_date     = date('Y-m-d',strtotime($_GET['report_end_date']));

    if($agent_role==ARUSER){
      $users=User::where('assign_to_ar',$agent_id)
      ->orderBy('name','ASC')->get();
    }else if($agent_role==ADMIN){
      $users=User::where('create_by_user',$agent_id)
      ->orderBy('name','ASC')->get();
    }else if($agent_role==SUPERADMIN){
       $users=User::where('user_role',AGENT)
       ->orderBy('name','ASC')->get();
    }else{
      $users=User::where('id',$agent_id)
      ->orderBy('name','ASC')->get();
    }
    // $startDate = // Your start date value;
    // $endDate = // Your end date value;

    
    $reportDetails   =   array();
    $distributors    =   array();
    foreach($users as $agent){
      // DB::enableQueryLog();
       $leadclients = DB::select("select `lead_client`.*,users.manage_distributor,lead_client.invoiced_total,agent_paid,lead_client.curr_status,lead_client.deducation,lead_client.incentive,`lead_status_history`.`date` as `paid_date`,`users`.`name` as fname, `users`.`last_name` as lname,(select date from lead_status_history where status='Paid Out' and lead_id=lead_client.id order by id desc limit 1)as newdate from `lead_client` inner join `users` on `users`.`id` = `lead_client`.`agent_id` inner join `lead_status_history` on `lead_status_history`.`lead_id` = `lead_client`.`id` where lead_client.agent_id in($agent->id) and lead_client.curr_status IN('Paid Out','14') and lead_status_history.date BETWEEN '$start_date' and '$end_date' and (SELECT date FROM lead_status_history WHERE status='Paid Out' AND lead_id=lead_client.id ORDER BY id DESC LIMIT 1) BETWEEN '$start_date' and '$end_date' GROUP BY lead_client.id order by lead_client.id DESC");
       // dd(DB::getQueryLog()); 
        $report   = array();
        $total    = 0;
        foreach ($leadclients as $leadclient) {
            if(!empty($leadclient->curr_status)):  
              $agentleaddetail    = User::find($leadclient->agent_id);
              $agent_status       = $agentleaddetail->user_role;
              $unit               = transaction_calculation($leadclient->transaction_type_id,$agentleaddetail,'unit');
              $agent_split_hous   = transaction_calculation($leadclient->transaction_type_id,$agentleaddetail,'broker_cost');
              $agent_split        = transaction_calculation($leadclient->transaction_type_id,$agentleaddetail,'broker_cost');
              // $agent_invoice_cost = transaction_calculation($leadclient->transaction_type_id,$agentleaddetail,'invoicing_cost');
              // $agent_invoice_cost = $leadclient->invoicing_cost;
              // if($agent_invoice_cost == 1 || $agent_invoice_cost == '')
              // {
              //   $agent_invoice_cost = transaction_calculation($leadclient->transaction_type_id,$agentleaddetail,'invoicing_cost');
              // }
              $agent_invoice_cost = (float)lead_calculate($leadclient->id)['agent_invoice_cost'];

              $incentive          = $leadclient->incentive;
              $sold_amt           = $leadclient->sold_amt;
              $house_comm         = $leadclient->house_comm;
              $house_bonus        = $leadclient->house_bonus;
              $distributor_name   = '';
              if($leadclient->source_id!=0 || $leadclient->source_id!='' || $leadclient->source_id!=NULL){

                $agentsourcedetail    = User::find($leadclient->source_id);

                if($agentsourcedetail->manage_distributor==1){
                  $agent_status     = 5;
                  $distributor_id   = $agentsourcedetail->id; 
                  $distributor_name = $agentsourcedetail->name.' '.$agentsourcedetail->last_name; 
                }
              }

              $lead     =   Distributorleadmarket::find($leadclient->lead_id_orig);

            /*======== Remi ===========*/
            //calculat lead cost and agent paid amount
            $lead_cost  =  0;
            $agent_paid = 0;
            $house_cost =  0;
            //for calculate house cost
               
            if($leadclient->sold_amt > 0){
              $totalcommission = (float)$sold_amt*((float) $house_comm/100)+(float)$house_bonus;
            }
            // if lead is shared with distributor
            if(!empty($lead) && $agent_status == 5){ 
            
              $dis_split = $lead->lead_cost;
              $dis_split_unit = $lead->unit;
              // calculate the cost for agent and distributor
              if($leadclient->payment != 0){    
                if($dis_split_unit == 1){
                    // $row1['lead_cost'] =  $row1['payment'] * $agent_split /  100 ;
                    $dis_split = (float) $dis_split/100;
                    $leadclient->lead_cost = ((float) $leadclient->payment - (float) $agent_invoice_cost) *  (float) $dis_split;
                    $lead_cost =((float) $leadclient->payment - (float) $agent_invoice_cost) *  (float) $dis_split;
                    $leadclient->agent_paid =  (float) $leadclient->payment - (float) $agent_invoice_cost - (float) $incentive - (float)$leadclient->lead_cost;
                  $agent_paid=(float) $leadclient->payment - (float) $agent_invoice_cost - (float) $incentive - (float)$leadclient->lead_cost;
                }else{      
                  // $lead_cost =$dis_split; 
                  // $leadclient->lead_cost = $dis_split; 
                  // $leadclient->agent_paid =  (float) $leadclient->payment - (float) $agent_invoice_cost - (float) $incentive - (float) $leadclient->lead_cost;
                  // $agent_paid=  (float) $leadclient->payment - (float) $agent_invoice_cost - (float) $incentive - (float) $leadclient->lead_cost;

                  // code update by swapnil
                  if($leadclient->transaction_type_id==1 || $leadclient->transaction_type_id == 7){
                    $commision = $leadclient->commision;
                    }else{
                        $commision=$leadclient->house_comm;
                    }
                   
                    if($agent_invoice_cost){
                        $payment =   (float) ($leadclient->payment-$agent_invoice_cost);
                    }else{
                        $payment =   (float) ($leadclient->payment);
                    }
                    
                    if (!empty($lead)){
                        if(!empty($lead->lead_cost)){
                            $broker_cost = ($payment*$lead->lead_cost/100);
                        }else{
                            $broker_cost = 0;
                        }
                        
                    }else{
                        $broker_amount = transaction_calculation($leadclient->transaction_type_id,$agentleaddetail,'broker_cost');
                        if(!empty($broker_amount)){
                            $broker_cost = ($payment*$broker_amount/100);
                        }else{
                            $broker_cost = 0;
                        }
                    }
                    
                    if (!empty($lead)){
                        $broker_cost = 0;
                    }
                    
                    $dis_split              = (float) $dis_split / 100;
                    $leadclient->lead_cost  = ($payment) * (float) $dis_split;

                    $lead_cost              = ($payment) * (float) $dis_split;
                    //$lead_cost              = ($payment - (float) $agent_invoice_cost) * (float) $dis_split;

                    $leadclient->agent_paid = $payment - (float) $agent_invoice_cost - (float) $incentive - (float) $leadclient->lead_cost;

                    //$agent_paid             = $payment - (float) $agent_invoice_cost - (float) $incentive - (float) $leadclient->lead_cost-$broker_cost;
                    $agent_paid             = $payment - (float) $incentive - (float) $leadclient->lead_cost-$broker_cost;
                  // code end
                }
              }

              if($leadclient->sold_amt != 0){
                  
                if($dis_split_unit == 0){
                      $dis_split = (float) $dis_split/100;
                      $house_cost = (float) $totalcommission *  (float) $dis_split;   
                }else{
                    $house_cost = (float) $dis_split;
                }
              }
            }else{

              if($leadclient->payment != 0){  
                if($unit == 0){
                  $agent_split  = (float) $agent_split_hous/100;
                  $lead_cost    = ((float) $leadclient->payment - (float) $agent_invoice_cost) *  (float) $agent_split;
                  $agent_paid   =  (float) $leadclient->payment - (float) $agent_invoice_cost - (float) $incentive - (float)$lead_cost;
                }else{
                  $lead_cost  =  (float) $agent_split;
                  $agent_paid =  (float) $leadclient->payment - (float) $agent_invoice_cost - (float) $incentive - (float)$lead_cost;
                }
              }
              // echo $lead_cost;
              // echo "<pre>";
              // print_r($agent_paid);
              // echo (float)lead_calculate($leadclient->id)['broker_splite'];
              // code updated by swapnil
              $extra = 0;
              
                if(lead_calculate($leadclient->id)['transaction_coordination_lease_verify']){
                  $extra += (float)lead_calculate($leadclient->id)['transaction_coordination_lease_verify'];
                }
                
                if(lead_calculate($leadclient->id)['commercial_transaction']){
                    $extra += (float)lead_calculate($leadclient->id)['commercial_transaction'];
                }
                if($lead_cost == 0)
                {
                  if(lead_calculate($leadclient->id)['broker_splite']){
                      $extra += (float)lead_calculate($leadclient->id)['broker_splite'];
                  }
                }

                if($leadclient->incentive == 0)
                {
                  if(lead_calculate($leadclient->id)['incentive']){
                    $extra += (float)lead_calculate($leadclient->id)['incentive'];
                  }
                }

                $agent_paid = $agent_paid - $extra;
                // echo "<pre>";
                // print_r($agent_paid);
              // end

              if($leadclient->sold_amt != 0){  
                  if($unit == 0){
                    $agent_split_hous = (float)  $agent_split_hous/100;
                    $house_cost       = (float) $totalcommission *  (float) $agent_split_hous;
                  }else{
                    $house_cost       =  (float) $agent_split_hous;
                  }
              }
            }
         
         
         /*======== End Remi=========*/
          
          if($leadclient->transaction_type_id == 1){

              $commission_unit = $leadclient->commision_unit;
              $commission      = $leadclient->commision;
              $bonus           = $leadclient->bonus;
              $rent_amount     = $leadclient->rent_amount;

              if($rent_amount==''){
                $rent_amount=0;
              }

              if($bonus==''){
                $bonus=0;
              }

              if($commission==''){
                $commission=0;
              }
             
              if($commission_unit == '1') {

                  if ($commission > 0) {
                    $tot_com = (float) $rent_amount * ((float) $commission/100);
                    $invoiced_total = (float) $tot_com + (float) $bonus;                                              
                  } else {
                    $invoiced_total = (float) $bonus;
                  }
              } else {
                $invoiced_total = (float) $commission + (float) $bonus;
              }
          }elseif($leadclient->transaction_type_id==2 || $leadclient->transaction_type_id==3){
             
            if($leadclient->sold_amt==''){
              $leadclient->sold_amt=0;
            }

            if($leadclient->house_comm==''){
              $leadclient->house_comm=0;
            }

            if($leadclient->house_bonus==''){
              $leadclient->house_bonus=0;
            }
            
            if($leadclient->house_comm > 0) {
              $tot_com        = (float) $leadclient->sold_amt * ((float) $leadclient->house_comm/100);
              $invoiced_total = (float) $tot_com+(float) $leadclient->house_bonus;
            } else {
              $invoiced_total = (float) $leadclient->house_bonus;
            }
          }else{
            $invoiced_total=0;
          }
          /*======== Remi ===========*/
          //calculate invoice amount
            
            
            $leadcalculatdata['distributor_name']   = $distributor_name;
            $leadcalculatdata['client']             = $leadclient->id;
            $leadcalculatdata['transaction_type_id']= $leadclient->transaction_type_id;
            $leadcalculatdata['amount']             = $agent_paid; 
            
            $leadcalculatdata['description']        = 'Agent Commission';
            
            $payroll[]                              = $leadcalculatdata;
            $total                                  +=  $agent_paid;

          if($lead_cost > 0 && $agent_status == 5){

            // $leadcalculatdata['distributor_name'] =   $distributor_name; 
            // $leadcalculatdata['client']           =   $leadclient->id;
            // $leadcalculatdata['transaction_type_id']= $leadclient->transaction_type_id;
            // $leadcalculatdata['amount']           =   $lead_cost; 
            // $leadcalculatdata['description']      =   'Referral';
            // $payroll[]                            =   $leadcalculatdata;
            // $total                                +=  $lead_cost;
            $distributor['id']              = $distributor_id;
            $distributor['amount']          = $lead_cost;
            $distributor['lead_cost']       = lead_calculate($leadclient->id)['lead_cost'];
            $distributor['client']          = $leadclient->id;;
            $distributors[]                 = $distributor;
          }
     
          endif;
        }
                $finalData['agent_id']=$agent->id;
                $finalData['source']=$agent_id;
                $finalData['street']=$agent->address;
                $finalData['city']=$agent->city;
                $finalData['state']=$agent->state;
                $finalData['zip']=$agent->zip;
                $finalData['phone']=$agent->phone;
                $finalData['email']=$agent->email;
              $finalData['agent_name']=$agent->name.' '.$agent->last_name;
               $finalData['total']=$total;
                $payROllDetails[]=$finalData;
        
    
    }
  
    // dd($payROllDetails);
//     foreach ($payROllDetails as $key => $data) {
//     $agentId = $data['agent_id'];
//     $totalLeadCost = 0;

//     foreach ($distributors as $distributor) {
//         if ($distributor['id'] == $agentId && $distributor['lead_cost'] > 0) {
//             // Sum the lead_cost for the current distributor
//             $totalLeadCost += $distributor['lead_cost'];
//         }
//     }

//     // Update the total field in $payROllDetails only if lead_cost is greater than 0
//     if ($totalLeadCost > 0) {
//         $payROllDetails[$key]['total'] += $totalLeadCost;
//     }
// }
    // echo "<pre>";
    // print_r($payROllDetails); die;
    // foreach ($payROllDetails as &$data) {
    //     $agentId = $data['agent_id'];
    //     $totalLeadCost = 0;
    //     foreach ($distributors as $distributor) {
    //         if ($distributor['id'] == $agentId && $distributor['lead_cost'] > 0) {
    //             // Sum the lead_cost for the current distributor
    //             $totalLeadCost += $distributor['lead_cost'];
    //         }
    //     }
    //     // Update the total field in $payROllDetails only if lead_cost is greater than 0
    //     if ($totalLeadCost > 0) {
    //         $data['total'] = $data['total'] + $totalLeadCost;
    //     }
    // }

    foreach ($payROllDetails as $key => $data) {
        $agentId = $data['agent_id'];
        $totalLeadCost = 0;
        foreach ($distributors as $distributor) {
            if ($distributor['id'] == $agentId && $distributor['lead_cost'] > 0) {
                // Sum the lead_cost for the current distributor
                $totalLeadCost += $distributor['lead_cost'];
            }
        }
        // Update the total field in $payROllDetails only if lead_cost is greater than 0
        if ($totalLeadCost > 0) {
            $payROllDetails[$key]['total'] = $data['total'] + $totalLeadCost;
        }
    }

    $data = $payROllDetails;
    // echo "<pre>";
    // print_r($data); die;
    $result = array();
    $client = array();
    foreach($data as $row) { 
      if(isset($row['total']) && $row['total'] > 0)
      {
        $datas['Reference ID (Optional)'] = '';
        $datas['Group (Optional)'] = '';
        $datas['Payer Legal Name'] =  isset($row['agent_name']) ? $row['agent_name'] : '';
        $datas['Payer Name Line 2'] = '';
        $datas['Payer Federal ID Number'] = '';
        $datas["Transfer Agent's Name (if applicable)"] = 'NA';
        $datas['Street Address'] = isset($row['street']) ? $row['street'] : '';
        $datas['City'] = isset($row['city']) ? $row['city'] : '';
        $datas['US State or Canadian Province (2 letters)'] = isset($row['state']) ? $row['state'] : '';
        $datas['NA'] = '';
        $datas['Zip or foreign postal code'] = isset($row['zip']) ? $row['zip'] : '';
        $datas['NA1'] = '';
        $datas['Phone'] = isset($row['phone']) ? $row['phone'] : '';
        $datas["Email (for Recipients' questions)"] = isset($row['email']) ? $row['email'] : '';
        $datas['Agent Paid'] = isset($row['total']) ? $row['total'] : '';
        
        array_push($result,$datas);
      }  
        
         
    }

    header("Content-type: application/csv");
    header("Content-Disposition: attachment; filename=".$start_date."-".$end_date."export_data-".date('Ymd') .".csv");
    $fp = fopen('php://output', 'w');

    
    $delimiter="\t";
     
     
    $handle = fopen( 'php://output', 'w' );
     
    fputcsv($fp,['Reference ID (Optional)','Group (Optional)','Payer Legal Name','Payer Name Line 2','Payer Federal ID Number',"Transfer Agent's Name (if applicable)",'Street Address','City','US State or Canadian Province (2 letters)','','Zip or foreign postal code','','Phone',"Email (for Recipients' questions)",'Agent Paid']);
    foreach ($result as $row) {
        fputcsv($fp, $row);
    }
    fclose($fp);
    }
}
No matches found