<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\Crypt;
use App\Models\AgentModel;
use App\Models\EbookVolume;
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['customers'] = AgentModel::getAgentCustomer($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');
      if($user->agent_access_type == 'C'){
        $data['customers'] = AgentModel::getAgentWithUsageCountByCountry($request,$data);
      }else{
        $data['customers'] = AgentModel::getAgentWithUsageCount($request,$data); 
      }

      // dd($data['customers']);
      
      return view('crm.risk',$data);

    }
    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)
    {
      $user_id = Crypt::decryptString($param);

      $data['subscribedata'] = AgentModel::getSubcriptionJournal($user_id);
      $data['user'] = $user     = User::getUserInfo();
      return view('crm.subscribejournal',$data);
      
    }
}
