<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\Crypt;
use App\Models\AgentModel;
use App\Models\EbookVolume;
use App\Models\UserAccess\UserAccess;
use App\User;
use Session;
use Auth;
use DB;

class CRMController extends Controller
{
    /*
    *   Author : Enayat
    *   Date   : 04-04-2022
    *   Desc.  : This function will load agent dashboard related to CRM
    *   @param : User object  
    */
  public function index(Request $request)
  {

    $data['user'] = $user     = User::getUserInfo();
    $data['country_id']       = ($request->has('by_country')) ? $request->by_country : '';
    $data['customer_name']    = ($request->has('by_customer')) ? $request->by_customer : '';
    $data['organization_name'] = ($request->has('by_organization')) ? $request->by_organization : '';
    $data['customer_type'] = ($request->has('customer_type')) ? $request->customer_type : '';
    $data['customers'] = array();
    $data['organization_list'] = array();
    if (($request->filter == "filter") && ($request->by_country || $request->by_customer || $request->by_organization || $request->customer_type)) {
      $data['customers'] = AgentModel::getAgentCustomer($request, $data);
      $data['organization_list'] = AgentModel::getOrganizatonList($request, $data);
    }

    $data['ebooks'] = DB::table('ebook')->where('published', 1)->orderBy('title', 'ASC')->get();
    $data['kbrt_ebooks'] = EbookVolume::GetAllEbookVolumeList();
    $data['journals'] = DB::table('journal')->select('journal_id', 'title', 'subtitle')->where('journal_status', 1)->orderBy('title', 'ASC')->get();

      return view('crm.dashboard',$data);  
    }
    public function risk(Request $request)
    {
      $data['user'] = $user = User::getUserInfo();
      
      $data['country_id']         = $country_id = ($request->has('by_country')) ? $request->by_country : '';
      $data['customer_name']      = $customer_name = ($request->has('by_customer')) ? $request->by_customer : '';
      $data['by_year'] = $by_year = ($request->has('by_year')) ? $request->by_year : date('Y');
      
      $perPage = $take = 10;
      $page = $request->input("page", 1);
     
      $skip = ($page - 1) * $perPage;
      if($take < 1) { $take = 1; }
      if($skip < 0) { $skip = 0; }
      $total_count = 0;
      if($user->agent_access_type == 'C'){
        
        if($request->btn == 'export'){
          $total_count = AgentModel::getAgentWithUsageCountByCountry($request,$data,'','','count');
          return $this->exportRiskCustomer($total_count);
        }
        $result =  AgentModel::getAgentWithUsageCountByCountry($request,$data,$perPage,$skip);
        /*
        This function will get total count, 
        To avoid load due to laravel pagination (subquery), make custome pagination.
        */ 
        $total_count = AgentModel::getAgentWithUsageCountByCountry($request,$data,'','','count');
        $total_count = (!empty($total_count)) ? count($total_count): 0;  
        
        
      }else{

        $result = AgentModel::getAgentWithUsageCount($request,$data,$perPage,$skip); 
        
        $total_count = AgentModel::getAgentWithUsageCount($request,$data,'','','count');
        $total_count = (!empty($total_count)) ? count($total_count): 0; 
        
      
      }
      
        $result = new \Illuminate\Pagination\LengthAwarePaginator($result,$total_count,$take,$page,[
                'path' => $request->url(),
                'query' => [
                    'page' => 'crm/risk'
                ]]);

     $data['customers'] = $result->appends($request->except(['page','_token']));

      
      return view('crm.risk',$data);

    }
    public function exportRiskCustomer($data)
    {
        $has_data='';
        $table = '<table width="100%" class="table table-striped table-bordered table-hover">
        <thead>
            <tr>
                <th>S.No</th>
                <th>Name</th>
                <th>Country</th>
                <th>Organization</th>
                <th class="text-center">Total Usage</th>
                <th class="text-center">Expected Usage</th>
            </tr>
        </thead>
        <tbody>';

              if(!empty($data)){
                $count=0;
                $has_data = 'YES';
                foreach ($data as $row) {
                    
                    $table .= '<tr>
                    <tr>
                      <td width="%7">'.++$count.'</td>
                      <td width="%18">'.$row->name.'</td>
                      <td width="%12">'.$row->country_name.'</td>
                      <td width="%53">'.$row->org_name.'</td>
                      <td width="%5" class="text-center">'.round($row->total_usage).'</td>
                      <td width="%5" class="text-center">'.round($row->expected_usage).'</td>
                    </tr>
                  </tr>';
                }
              }  

      $table .='</tbody></table>';
     
      if(!empty($has_data)){
        $filename = "CustomerAtRisk".time().".xls";
      
        header( 'Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet; charset=utf-8' );

        header( "Content-disposition: attachment; filename=\"$filename\"" );

        echo $table; die;
        
      }else{
         Session::flash('error', 'No record found !!!.');
         return redirect()->route('crm.risk');
      }

      
    }
    public function opportunity(Request $request)
    {
      if(Auth::user()->agent_access_type == 'U'){
        $user = User::getUserInfo();
        $agent_ids_array = DB::table('tbl_agent_access')->where('user_id',$user->id)->pluck('agent_id')->toArray();
        return $this->opportunityByUser($request,$user,$agent_ids_array);
      }else{
        return $this->opportunityByCountry($request);
      }


    }
    public function opportunityByUser($request,$user,$agent_ids_array)
    {
      
      $data['user'] = $user;
      $data['agent_ids_array'] = $agent_ids_array;

      $data['country_id']         = $country_id = ($request->has('by_country')) ? $request->by_country : '';
      $data['customer_name']      = $customer_name = ($request->has('by_customer')) ? $request->by_customer : '';
      $data['by_year'] = $by_year = ($request->has('by_year')) ? $request->by_year : date('Y');
      $data['count_limit']  = ($request->has('count_limit')) ? $request->count_limit : 100; 
      
      if(!empty($data['agent_ids_array'])){
        $data['customers'] = AgentModel::getOpportunityUsers($request, $data);  
      }else{
        $data['customers'] = [];
      }
      
      return view('crm.opportunity',$data);
    
    }
    public function bycountryuser(Request $request, $country)
    {
      $user = User::getUserInfo();
      $agent_ids_array = DB::table('users')->where('country',$country)->pluck('id')->toArray();
     
      return $this->opportunityByUser($request,$user,$agent_ids_array);
    }
    public function opportunityByCountry($request)
    {
      
      $data['user'] = $user = User::getUserInfo();
      $data['country_id']         = $country_id = ($request->has('by_country')) ? $request->by_country : '';
      $data['by_year'] = $by_year = ($request->has('by_year')) ? $request->by_year : date('Y');
      
      $data['agent_ids_array'] = DB::table('tbl_agent_access')->where('user_id',$user->id)->pluck('agent_id')->toArray();
      $data['country_count'] = AgentModel::getDenailCountByCountry($request,$data);

      return view('crm.opportunity_by_country',$data);

    }
    public function opportunityDetail($agent,$user_id,$year)
    {
      $data['user'] = $user = User::getUserInfo();
      $data['year'] = $year;
      $data['agent'] = $agent;
      $data['monthly_denail_count'] =  AgentModel::getMonthlyDenailCount($user_id,$year);
      $data['journal_denail_count'] =  AgentModel::getBusinessOprtByJournal($user_id,$year);
     
      return view('crm.opportunity_detail',$data);
    }
    public function potential($agent,$user_id,$year,$journal_id)
    {
      $data['user'] = $user = User::getUserInfo();
      
     
      $data['subscribe_count'] =  AgentModel::getSubscribedJournalCount($user_id,$year,$journal_id);
      $data['unsubscribe_count'] =  AgentModel::getUnSubscribedJournalCount($user_id,$year,$journal_id);
      // dd($data['subscribe_count']);
      
      $data['agent'] = $agent;
      $data['year'] = $year;
      return view('crm.potential',$data);
    }
    public function combineReport($param,$year,$eurekausername)
    {
      $data['user'] = $user = User::getUserInfo();
     
      $data['identity_id'] = $identity_id = Crypt::decryptString($param);
	  $data['eurekauser'] = $eurekauser = Crypt::decryptString($eurekausername);

      if(empty($identity_id)){
        
        Session::flash('error', ' ID is not mapped please coordinate with administrator.');
        return back();

      }
     
      $data['filter_year'] = $filter_year = $year;//$param_array->year;

      $data['user_name'] = $user_name = DB::table('ingenta_access_denied')->where('identity_id',$identity_id)->pluck('name')->first();
      
      $usage_order_by = " order by total_usage desc";
      $denial_order_by = " order by total_denial desc";

      $data['Subscriber'] = $Subscriber = $university_name = $user_name = (!empty($user_name)) ? $user_name : DB::table('ingenta_views')->where('identity_id',$identity_id)->pluck('name')->first() ;
      
      /*****************  Get Ingenta PPV Revenue **********************/

      $data['ingenta_ppv_revenue'] = AgentModel::getPPVFromIngenta($identity_id,$filter_year);
      
      /**********************count  Eureka usage*****************/

      $eureka_usage = AgentModel::getEurekaTotalUsage($eurekauser,$filter_year);
      
      $eureka_usage_total = $eureka_usage[0]->eureka_usage;        
      
      if(empty($eureka_usage_total)){
          $eureka_usage_total =0; 
      }
      $data['eureka_usage_total'] = $eureka_usage_total;
      /************* count  eureka_denial ****************/

      $eureka_denial = AgentModel::eurekaDenialTotal($eurekauser,$filter_year);  
      $eureka_denial_total = $eureka_denial[0]->eureka_denial;
      if(empty($eureka_denial_total)){
          $eureka_denial_total =0; 
      }
      $data['eureka_denial_total'] = $eureka_denial_total;
      /********************** count Ingenta total  usage*****************/

      $ingenta_usage = AgentModel::ingentaUsageTotal($identity_id,$filter_year);
      $ingenta_usage_total = $ingenta_usage[0]->ingenta_usage;
      if(empty($ingenta_usage_total)){
          $ingenta_usage_total =0; 
      }
      $data['ingenta_usage_total'] = $ingenta_usage_total;
     /********************** count Ingenta total denial *****************/
    
      $ingenta_denial = AgentModel::ingentaDenialTotal($identity_id,$filter_year);  
      
      $ingenta_denial_total = $ingenta_denial[0]->ingenta_denial; 
      if(empty($ingenta_denial_total)){
          $ingenta_denial_total =0; 
      }
      $data['ingenta_denial_total'] = $ingenta_denial_total;
      $data['total_usage'] = $total_usage = $eureka_usage_total + $ingenta_usage_total;
      $data['total_denial'] = $total_denial = $eureka_denial_total +  $ingenta_denial_total;
      
      $data['subscription_jour'] =  AgentModel::getJournalSubcription($eurekauser);
     
      $data['denial_result'] = AgentModel::allJournalDenailExcell($eurekauser,$identity_id,$filter_year,$denial_order_by);
                                                
      $data['usage_result'] = AgentModel::allJournalUsageExcell($eurekauser,$identity_id,$filter_year,$usage_order_by);                                                 

      /**************************************************************************************************************
        
                                PAPER VIEW ORDERS

      ***************************************************************************************************************/
  
  
      $ip_ranges = AgentModel::getIpRang($eurekauser);
      $from_ip_array = array();
      $to_ip_array = array();
      foreach ($ip_ranges as $row) {
          array_push($from_ip_array,$row->from_ip);
          array_push($to_ip_array,$row->to_ip);
      }
     
      $total_sum =0;

      if(!empty($from_ip_array)){
        $total_sum = AgentModel::getPPVFromBSP($from_ip_array,$to_ip_array,$filter_year);
      }
      $data['total_sum'] = $total_sum;



      return view('crm.report.combine',$data);

    }
    public function getModelData(Request $request)
    {
      $jid = $request->jid;
      $user_name = $request->user_name;
      $year = $request->year;

      $article_query_result = AgentModel::getSubcriptionArticle($jid,$user_name,$year);
      $table = '<table width="10%" class="table table-striped table-bordered table-hover "  id="dataTables-example">
        <thead>
            <tr>
                <th width="2%" bgcolor="#324148" style="color: white">No.</th>
                <th style="text-align: center;color: white" bgcolor="#324148" width="3%">Journal</th>
                <th style="text-align: center;color: white" bgcolor="#324148" width="3%">Volume</th>
                <th style="text-align: center;color: white" bgcolor="#324148" width="5%">Year</th>
                <th style="text-align: center;color: white" bgcolor="#324148" width="3%">Issue</th>
                <th style="text-align: center;color: white" bgcolor="#324148" width="68%">Article</th>
                <th style="text-align: center;color: white" bgcolor="#324148" width="15%">Denial date</th>
                <th style="text-align: center;color: white" bgcolor="#324148" width="23%">IP Address</th>
            </tr>
        </thead>
        <tbody>';

              if(!empty($article_query_result)){
                $count=0;
                foreach ($article_query_result as $row) {
                    
                    $table .= '<tr>
                    <td align="center">'.++$count.'</td>
                    <td align="center" >'.$row->subtitle.'</td>
                    <td align="center" >'.$row->volume.'</td>
                    <td align="center" >'.$row->year.'</td>
                    <td align="center" >'.$row->issue.'</td>
                    <td align="center" >
                    <a href="http://www.eurekaselect.com/node/'.$row->nid.'" target="_blank">'.$row->article_title.'</a>
                    </td>
                    <td align="center" >'.strftime("%b %d, %Y",strtotime($row->denial_date)).'</td>
                    <td align="center" >'.$row->ip_address.'</td>
                  </tr>';
                }
              }else{
                $table="<tr><td>No record found !!!.</td></tr>";
              }  

      $table .='</tbody></table>';

      echo $table;
    }
    public function excelCombineReport(Request $request)
    {
      // dd($request->all());
      $user_name = $request->user_name;
      $Subscriber = $request->subscriber;
      $identity_id = $request->identity_id;
      $filter_year = $request->filter_year;

      $subscription_jour =  AgentModel::getJournalSubcription($user_name);
                
      $order_by = " order by jcode asc";
      $usage_result = AgentModel::allJournalUsageExcell($user_name,$identity_id,$filter_year,$order_by);
      
      $all_denail_result = AgentModel::allJournalDenailExcell($user_name,$identity_id,$filter_year,$order_by);

      
      $cnt = 0;
      $table = '<table class="table table-bordered table-hover table-striped" border="1">
                <thead>
                    <div> <b>YEAR | '.$filter_year.' </b></div><br>
                    <tr>
                    <th width="1%" style="font-size: 12px;text-align: center" class="bg-info" >Title</th>
                    <th width="1%" style="font-size: 12px;text-align: center" class="bg-info" >Subtitle</th>
                    <th width="1%" style="font-size: 12px;text-align: center" class="bg-info" >Ingenta Total Usage</th>
                    <th width="1%" style="font-size: 12px;text-align: center" class="bg-info" >Eureka Total Usage</th>
                    <th width="1%" style="font-size: 12px;text-align: center" class="bg-info" >Total Usage</th>
                    <th width="1%" style="font-size: 12px;text-align: center" class="bg-info" >Ingenta Total Denial</th>
                    <th width="1%" style="font-size: 12px;text-align: center" class="bg-info" >Eureka Total Denial</th>
                    <th width="1%" style="font-size: 12px;text-align: center" class="bg-info" >Total Denial</th>
                    
                    </tr>
                </thead>
                <tbody>';
      
      if(!empty($usage_result)){          
        foreach ($usage_result as $row) {
          $jid = $row->journal_id;

        $table .='<tr>
                <td> '.$row->title.'</td>';
                
                 if(in_array($jid, $subscription_jour)){ 

                  $table .='<td align="center" style="background-color:  #339966">'.$row->jcode.'</td>';
                }else{
                  $table .='<td align="center" style="background-color:  #C0C0C0">'.$row->jcode.'</td>';
                }                                                       
                $table .='<td align="center">'.$row->ingenta_total_usage.'</td>
                <td align="center">'.$row->journal_total_usage.'</td>
                
                <td align="center" style="background-color:  orange" >'.$row->total_usage.'</td>
                   
                <td align="center">'.$all_denail_result[$cnt]->ingenta_total.'</td>
                <td align="center">'.$all_denail_result[$cnt]->eureka_total.'</td>
                <td align="center" style="background-color:  red;color:white">'.$all_denail_result[$cnt]->total_denial.'</td>
           </tr>';     


           $cnt++;
        }
      }

      $filename = "Combine report of ". $Subscriber.""."_".$filter_year.".xls";
      // echo $filename;die;
      header( 'Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet; charset=utf-8' );

      header( "Content-disposition: attachment; filename=\"$filename\"" );

      echo $table ;


    }
    public function excelAllDenial(Request $request)
    {
     
      $user_name = $request->user_name;
      $Subscriber = $request->subscriber;
      $identity_id = $request->identity_id;
      $filter_year = $request->filter_year;


      $subscription_jour =  AgentModel::getJournalSubcription($user_name);
                
       $denial_order_by = " order by total_denial desc";   
      
      $all_denail_result = AgentModel::allJournalDenailExcell($user_name,$identity_id,$filter_year,$denial_order_by);
      $table = '<table class="table table-bordered table-hover table-striped" border="1">
                <thead>
                    <div> <b>YEAR | '.$filter_year.' </b></div><br>
                    <tr>
                    <th width="1%" style="font-size: 12px;text-align: center" class="bg-info" >Title</th>
                    <th width="1%" style="font-size: 12px;text-align: center" class="bg-info" >Subtitle</th>
                    <th width="1%" style="font-size: 12px;text-align: center" class="bg-info" >Ingenta Total Denial</th>
                    <th width="1%" style="font-size: 12px;text-align: center" class="bg-info" >Eureka Total Denial</th>
                    <th width="1%" style="font-size: 12px;text-align: center" class="bg-info" >Total Denial</th>
                    <th width="1%" style="font-size: 12px;text-align: center" class="bg-info" >Subscription Cost</th>
                    <th width="1%" style="font-size: 12px;text-align: center" class="bg-info" >Cost Per Denial</th>

                    </tr>
                </thead>
                <tbody>';
      if(!empty($all_denail_result)){          
        foreach ($all_denail_result as $row) {
          $jid = $row->journal_id;
          
          $table .= '<tr>
              <td>'.$row->title.'</td>';
              
               if(in_array($jid, $subscription_jour)){
                $table .= '<td align="center" style="background-color:  #339966">
                  '.$row->jcode.'
                </div>
                </td>';
               }else{ 
                $table .= '<td align="center" style="background-color:  #C0C0C0"></i> '.$row->jcode.'</td>';
              }                                                       
              
              

              $table .= '<td align="center"  >'.$row->ingenta_total.'</td>
              <td align="center"  >'.$row->eureka_total.'</td>
              <td align="center" style="background-color:  orange" >'.$row->total_denial.'</td>
              <td align="center"  >'.$row->subscription_cost.'</td>
              <td align="center"  >'.round($row->cost_per_denial,2).'</td>
           </tr>';     

        }
      }
      $filename = "Denial report of ". $Subscriber.""."_".$filter_year.".xls";
      // echo $filename;die;
      header( 'Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet; charset=utf-8' );

      header( "Content-disposition: attachment; filename=\"$filename\"" );

      echo $table;


    }
    public function excelAllUsage(Request $request)
    {
      $user_name = $request->user_name;
      $Subscriber = $request->subscriber;
      $identity_id = $request->identity_id;
      $filter_year = $request->filter_year;
      $subscription_jour =  AgentModel::getJournalSubcription($user_name);

      $usage_order_by = " order by total_usage desc";   
      
      $usage_result = AgentModel::allJournalUsageExcell($user_name,$identity_id,$filter_year,$usage_order_by);
      $table = '<table class="table table-bordered table-hover table-striped" border="1">
                  <thead>
                      <div> <b>YEAR | '.$filter_year.' </b></div><br>
                      <tr>
                      <th width="1%" style="font-size: 12px;text-align: center" class="bg-info" >Title</th>
                      <th width="1%" style="font-size: 12px;text-align: center" class="bg-info" >Subtitle</th>
                      <th width="1%" style="font-size: 12px;text-align: center" class="bg-info" >Ingenta Total Usage</th>
                      <th width="1%" style="font-size: 12px;text-align: center" class="bg-info" >Journal Total Usage</th>
                      
                      <th width="1%" style="font-size: 12px;text-align: center" class="bg-info" >Total Usage</th>
                      <th width="1%" style="font-size: 12px;text-align: center" class="bg-info" >Cost per usage</th>
                      <th width="1%" style="font-size: 12px;text-align: center" class="bg-info" >Total Abstract Views</th>

                      </tr>
                  </thead>
                  <tbody>';
      if(!empty($usage_result)){          
        foreach ($usage_result as $row) {
          $jid = $row->journal_id;
          if(in_array($jid, $subscription_jour))
          { 
            $total_usage_per_subscription = 0;
            if((int)$row->total_usage != 0){
              $total_usage_per_subscription = $total_usage_per_subscription + round((float)$row->subscription_cost / (float)$row->total_usage);
            }
            
          }
          $table .= '<tr>
                      <td >'.$row->title.'</td>';
                      
                        if(in_array($jid, $subscription_jour)){ 
                        $table .='<td align="center" style="background-color:  #339966">
                          '.$row->jcode.'<br><font size="1" color="white">Subs cost $'.$row->subscription_cost.'</font>
                        
                        </td>';
                      }else{
                        $table .=' <td align="center" style="background-color:  #C0C0C0">'.$row->jcode.'</td>';
                      } 
                      $table .='<td align="center">'.$row->ingenta_total_usage.'</td>
                      <td align="center">'.$row->journal_total_usage.'</td>
                      
                      <td align="center" style="background-color:  orange" >'.$row->total_usage.'</td>
                      <td align="center" style="background-color:  #81C784">';
                      if(in_array($jid, $subscription_jour)){ 
                      
                        $subscription_jour_val = 0;
                        if((int) $row->total_usage != 0 ){
                          $subscription_jour_val = (float)$row->subscription_cost / (float)$row->total_usage;
                        
                        }
                        $table .= round($subscription_jour_val) .'</td>';
                      
                      }else{ $table .='0</td>';}  
                      
                      
                      $table .='<td align="center" >'.$row->total_abstract_views.'</td>
                   </tr>';     

        }
      }

      $filename = "Usage report of ". $Subscriber.""."_".$filter_year.".xls";
      
      header( 'Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet; charset=utf-8' );

      header( "Content-disposition: attachment; filename=\"$filename\"" );

      echo $table;

    }
    public function agentAuthor($param,$flag='')
    {
      $data['user_id'] = $user_id = Crypt::decryptString($param);
      $result = DB::table('users')->where('id',$user_id)->select('autohrs_keywords','display_name')->first();
      $autohrs_keywords = $result->autohrs_keywords;
      if(empty($autohrs_keywords)){
        
        Session::flash('error', 'Institute tag(s) is not define, please coordinate with administrator.');
        return back();

      }
     
      $data['institute_name'] = $institute_name = $result->display_name;
      $institute_tag_array = explode(',', $autohrs_keywords);
      $authors = AgentModel::gettotalAuthors($institute_tag_array,$flag);
      $table = '';
      if(!empty($authors)){
       
        $table .= '<table class="table table-bordered table-hover table-striped" border="1">
                  <thead>
                      <tr>
                      <th style="text-align: center"  >Name</th>
                      <th style="text-align: center"  >Email</th>
                      <th style="text-align: center"  >Country</th>
                      <th style="text-align: center"  >Affiliation</th>
                      </tr>
                  </thead>
                  <tbody>';
        foreach ($authors as $row) { 
              $full_name = $row->first_name;
              if(!empty($row->initials)) { $full_name .= ' '.$row->initials; }
              if(!empty($row->last_name)) { $full_name .= ' '.$row->last_name; }
              $table .='<tr>
                  <td>'.$full_name.'</td>
                  <td>'.$row->email.'</td>
                  <td>'.$row->country.'</td>
                  <td>'.$row->affiliation.'</td>
              </tr>';
        }
        $table .= "<tbody><table>";
      }
      if(!empty($flag)){
        $filename = "Authors Of ". $institute_name.""."_".time().".xls";
      
        header( 'Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet; charset=utf-8' );

        header( "Content-disposition: attachment; filename=\"$filename\"" );

        echo $table;

        exit();
      }
      $data['table'] = $table;
      $data['user'] = $user = User::getUserInfo();

    return view('crm.report.authors', $data);
  }
  public function subscription($param, $type)
  {
    $user_id        = Crypt::decryptString($param);
    $access_type    = Crypt::decryptString($type);

    $data['subscribedata'] = AgentModel::getSubcriptionJournal($user_id, $access_type);
    $data['user'] = $user     = User::getUserInfo();
    return view('crm.subscribejournal', $data);
  }
  public function UserTokenReport($id, $type = '')
  {
    $user = User::getUserInfo();
    $used_token            = array();
    $available_token       = array();
    $used_token_array      = array();
    $available_token_array = array();
    $used_token_count      = 0;
    $available_token_count = 0;

    $user_base_access_data = UserAccess::where('user_id', $id)->where('user_access_status', 1)->get();

    $token_type = array(444, 555, 666);
    if (!empty($user_base_access_data)) {
      foreach ($user_base_access_data as $k => $v) {
        if ($v->user_id == $id) {
          if (in_array($v->access_type_id, $token_type)) {
            $access_user_data        = $user_base_access_data[$k];
            $access_type_id          = $access_user_data->access_type_id;
            $param['user_access_id'] = $access_user_data->user_access_id;
            $param['access_on']      = $access_user_data->access_type_on;
            if ($param['access_on'] == 'journal_volume' || $param['access_on'] == 'issue' || $param['access_on'] == 'article') {
              $join['journal']      = true;
              $join['volume']       = true;
              $join['issue']        = true;
              $join['article']      = true;

              if ($id == 394991) {
                $join['ebook_volume'] = true;
                $join['chapter']      = true;
              } else {
                $join['ebook_volume'] = false;
                $join['chapter']      = false;
              }
            }

            if ($param['access_on'] == 'ebook_volume') {
              $join['journal']      = false;
              $join['volume']       = false;
              $join['issue']        = false;
              $join['article']      = false;
              $join['ebook_volume'] = true;
              $join['chapter']      = false;
            }

            if ($param['access_on'] == 'chapter') {
              $join['journal']      = false;
              $join['volume']       = false;
              $join['issue']        = false;
              $join['article']      = false;
              $join['ebook_volume'] = true;
              $join['chapter']      = true;
            }

            $used_token[] = UserAccess::GetAccessUsedTokenList($param, $join);

            if ($access_type_id == 555) {
              $param['download_count'] = 0;
            }

            $available_token[] = UserAccess::GetAccessTokenList($param);
          }
        }
      }

      if (!empty($used_token)) {
        for ($k = 0; $k < count($used_token); $k++) {
          $used_token_array = $used_token[$k]->merge($used_token_array);
        }
      }
      if (!empty($available_token)) {

        for ($k = 0; $k < count($available_token); $k++) {
          $available_token_array = $available_token[$k]->merge($available_token_array);
        }
      }

      $used_token      = $used_token_array;
      $available_token = $available_token_array;

      $used_token_count = $used_token->count();

      if (!empty($available_token_array)) {
        $av_count = 0;
        foreach ($available_token_array as $av_val) {
          $av_count = $av_count + $av_val->download_count;
        }
      }

      $available_token_count = $user_base_access_data[0]->restricted_download_limit - $used_token_count;
    }

    if ($type == 1) {
      return view('crm.token_detail', with([
        'user'        =>  $user,
        'used_token'  =>  $used_token,
        'type'        =>  $type
      ]));
    }

    if ($type == 2) {
      return view('crm.token_detail', with([
        'user'        =>  $user,
        'available_token'  =>  $available_token,
        'type'             =>  $type
      ]));
    }

    if ($type == 3) {
      return $this->ExportTokenList($used_token, $available_token);
    }

    $token_report = '<h2 class="h4 p-3 bg-dark-light text-primary-light"> Token Report </h2>
    <div class="card bigbox-hvr-slow mt-4">
      <div class="card-body">
        <div class="row">
          <div class="col-sm-5 lead col-8"><strong>Token Used</strong></div>
          <div class="col-sm-3 col-4 text-xs-right"><span class="badge badge-info font-18">' . $used_token_count . '</span></div>';
    if ($used_token_count > 0) {
      $token_report .= '<div class="col-md-4 text-xs-right pt-xs-2">
            <a href="user_token_report/' . $id . '/1" class="btn bg-primary-light text-white btn-sm">View Details</a> 
            <a href="user_token_report/' . $id . '/3" class="mt-sm-2 mt-xs-0 mt-xl-0 btn btn-outline-dark btn-sm">Export Files</a>
          </div>';
    }
    $token_report .= '</div>
      </div>
    </div>';
    $token_report .= '<div class="card bigbox-hvr-slow mt-4">
		<div class="card-body">
			<div class="row">
				<div class="col-sm-5 lead col-8"><strong>Available Token</strong></div>
				<div class="col-sm-3 col-4 text-xs-right"><span class="badge badge-success font-18">' . $available_token_count . '</span></div>';
    if ($available_token_count > 0) {
      $token_report .= '<div class="col-md-4 text-xs-right pt-xs-2">
        <a href="user_token_report/' . $id . '/2" class="btn bg-primary-light text-white btn-sm">View Details</a> 
      </div>';
    }
    $token_report .= '</div>
		</div>
	</div>';
    $token_report .= '<div class="mt-3 text-right"><button type="button" class="btn btn-secondary" data-dismiss="modal">Close</button>
  </div>';
    return $token_report;
  }

  public function ExportTokenList($used_token, $available_token)
  {
    $tr_data = '';
    if (!empty($used_token)) {
      $tableHeader = "<table width='1000' align='center' border='1'>
              <tr><td><b>'Used Token Report'</b></td></tr>";
      $tableHeader .= "<tr align='center'>";
      $tableHeader .= "<td align='center' bgcolor='#CCCCCC'><b>Journal Title</b></td>";
      $tableHeader .= "<td bgcolor='#CCCCCC'><b>Print ISSN</b></td>
              <td bgcolor='#CCCCCC'><b>Online ISSN</b></td>";
      $tableHeader .= "<td bgcolor='#CCCCCC'><b>Year</b></td>
                              <td bgcolor='#CCCCCC'><b>DOI</b></td>";
      $tableHeader .= "<td bgcolor='#CCCCCC'><b>Article Title</b></td>";
      $tableHeader .= "<td bgcolor='#CCCCCC'><b>Volume No</b></td>";
      $tableHeader .= "<td bgcolor='#CCCCCC'><b>Issue No</b></td>";
      $tableHeader .= "<td bgcolor='#CCCCCC'><b>Pages</b></td>";
      $tableHeader .= "<td bgcolor='#CCCCCC'><b>Content Access Key</b></td>";
      $tableHeader .= "<td bgcolor='#CCCCCC'><b>Downloaded From IP</b></td>";
      $tableHeader .= "<td bgcolor='#CCCCCC'><b>Download Date</b></td>";
      $tableHeader .= "<td bgcolor='#CCCCCC'><b>Access Assign Date</b></td></tr>";

      foreach ($used_token as $token) {

        $tr_data .= "<tr><td>" . $token->journal_title . "</td>
                                <td>" . $token->journal_issn . "</td>
                                <td>" . $token->journal_eissn . "</td>
                                <td>" . $token->year . "</td>
                                <td>" . $token->article_doi . "</td>
                                <td>" . $token->article_title . "</td>
                                <td>" . $token->volume_name . "</td>
                                <td>" . $token->issue_title . "</td>
                                <td>" . $token->first_page . ' to ' . $token->last_page . "</td>
                                <td>" . $token->user_name . "</td>
                                <td >" . long2ip($token->download_ip) . "</td>
                                <td>'" . strftime("%d-%m-%Y", $token->download_time) . "</td>
                                <td>" . strftime("%d-%m-%Y", $token->system_time) . "</td>";
        $tr_data .= "</tr>";
      }

      $table = $tableHeader;
      //$table.='<br>';
      $table .= $tr_data . "</table>";

      header("Content-type: application/vnd.ms-excel");
      header("Content-Disposition: attachment; filename=token_report.xls");
      print $table;
      exit;
    } else {
      return back();
    }
    }
}
