<?php

namespace App\Http\Controllers;

use App\Models\Issue;
use App\Models\Journal;
use App\Models\Volume;
use App\Models\EbookVolume;
use App\User;
use DB;
use Auth;
use File;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Validator;
use Illuminate\Support\Facades\View;
use Illuminate\Support\Facades\Redirect;
use Illuminate\Support\Facades\Crypt;


class KBartController extends BenthamController
{

   
   public function KBart($action="",Request $request)
   {   

        $editPermission   = false;
        $deletePermission = false;

        if (checkPermission('kbart', 'edit')) {
            $editPermission = true;
    }
    if (checkPermission('kbart', 'delete')) {
      $deletePermission = true;
    }

    switch ($action) {
          case 'view':


            $journals = DB::table('journal')->select('journal_id', 'title', 'subtitle')->where('nid', '!=', '')->orderBy('title', 'ASC')->get();

            $ebooks = DB::select("select y.year, v.ebook_volume_id, e.title as ebk_title, v.volume_name, e.ebook_id,e.image_file_name as ebk_image_file_name,e.index_on as index_on,v.ebook_subtitle, v.ebook_price, v.ebook_price_pod , v.ebook_price_podandpdf, v.isbn,v.issn, v.eisbn,v.eissn, v.doi, v.year_id, v.nid as vnid, v.introduction as introduction ,v.expected_date as expected_date, ea.content_type_access_id, v.ebook_type 
			from ebook e 
              inner join ebook_volume v on e.ebook_id = v.ebook_id 
              inner join year y on v.year_id = y.id
              left join ebook_access ea on ea.content_type_id=v.ebook_volume_id and ea.content_type = 'v'
              where e.published = 1 
              and (
                  year(v.publication_date) NOT IN (2016, 2017)
                  or v.ebook_status <> 3
              )
              and v.is_uploaded = 1
              order by e.title, cast(v.volume_name  as signed) ASC");

                return View::make('admin.kbart.kbart')
          ->with([
            'journals' => $journals,
            'ebooks'   => $ebooks
          ]);
       
          break;

          case 'journals':
              $param['journal_id'] = $journal_id = $request->get('journal_id');
              $arr_as_string = implode( ',', $journal_id );

              $this->GetJournalKBart($arr_as_string);
          break;

          case 'ebooks':
              $param['ebook_volume_id'] = $ebook_volume_id = $request->get('ebook_volume_id');
              $arr_as_string = implode( ',', $ebook_volume_id );

              $this->GeteBookKBart($arr_as_string);
          break;
        }
    } 



     /*
     ----------------------------Generate Journal KBart File-----------------------------------
    */
	public function GetJournalKBart($arr_as_string, $username = "")
	  {

		$freeJournals = ['CIS', 'CMIR', 'NEMJ', 'CMP'];
		$output = "";
		$output = "publication_title,print_identifier,online_identifier,date_first_issue_online,num_first_vol_online,num_first_issue_online,date_last_issue_online,num_last_vol_online,num_last_issue_online,title_url,first_author,title_id,embargo_info,coverage_depth,notes,publisher_name,publication_type,access_type,title_change_history";
		$output .= "\n";

		$journals = DB::select("select * from journal where journal_id in ($arr_as_string)");
		if ($journals) {
		  foreach ($journals as $journal) {
			$url = 'https://www.eurekaselect.com/' . $journal->nid . '/journal';
			$date_first_issue = '';
			$num_first_vol = '';
			$num_first_issue = '';

			$date_last_issue = '';
			$num_last_vol = '';
			$num_last_issue = '';
			$last_vol_year = '';

			$row = DB::select("select j.journal_id, j.subtitle, y.year, v.volume_name, i.title, i.publication_date
                  FROM journal j INNER JOIN volume v ON j.journal_id = v.journal_id
                  INNER JOIN issue i ON v.volume_id = i.volume_id 
                  INNER JOIN year y ON v.year_id = y.id
                  WHERE j.journal_id =$journal->journal_id and i.is_uploaded=1
                  ORDER BY y.year asc, v.volume_name asc, i.title asc 
                  LIMIT 1");
			if ($row) {
			  if ($row[0]->year != "")
				$date_first_issue = date('d-m-Y', $row[0]->publication_date);

			  $num_first_vol = $row[0]->volume_name;
			  $num_first_issue = $row[0]->title;
			}


			$row1 = DB::select("select j.journal_id, j.subtitle, y.year, v.volume_name, i.title, i.publication_date
                  FROM journal j INNER JOIN volume v ON j.journal_id = v.journal_id
                  INNER JOIN issue i ON v.volume_id = i.volume_id 
                  INNER JOIN year y ON v.year_id = y.id
                  WHERE j.journal_id =$journal->journal_id and i.is_uploaded=1
                  ORDER BY y.year desc, v.volume_name desc, i.title desc 
                  LIMIT 1");

			if ($row1) {
			  if ($row1[0]->year != "")
				$date_last_issue = date('d-m-Y', $row1[0]->publication_date);

			  $num_last_vol = $row1[0]->volume_name;
			  $num_last_issue = $row1[0]->title;
			  $last_vol_year = $row1[0]->year;
			}
			
			$pattern1 = '/\((Formerly.*?)\)/';
			$pattern2 = '/\((Continued.*?)\)/';

			$output .= preg_replace('/[ ,]+/', ' ', trim($journal->title)) . ",";
			$output .= $journal->issn . ",";
			$output .= $journal->eissn . ",";
			$output .= $date_first_issue . ",";
			$output .= $num_first_vol . ",";
			$output .= $num_first_issue . ",";
			
			if ($journal->journal_status != 1 || preg_match_all($pattern2, $journal->formerly_title, $matches)) {
			  $output .= $date_last_issue . ",";
			} else {
			  $output .=  ",";
			}

			if ($journal->journal_status != 1 || preg_match_all($pattern2, $journal->formerly_title, $matches)) {
			  $output .= $num_last_vol . ",";
			} else {
			  $output .=  ",";
			}

			if ($journal->journal_status != 1 || preg_match_all($pattern2, $journal->formerly_title, $matches)) {
			  $output .= $num_last_issue . ",";
			} else {
			  $output .=  ",";
			}
		
			$output .= $url . ",";
			$output .= ",";
			$output .= $journal->nid . ",";
			$output .= ",";
			$output .= "Fulltext,";
			$output .= ",";
			$output .= "Bentham Science,";
			$output .= "Serial,";
			if (in_array($journal->subtitle, $freeJournals)) {
			  $output .= "F,";
			} else {
			  $output .= "P,";
			}

			if (preg_match_all($pattern1, $journal->formerly_title, $matches)) {

			  if ($journal->journal_status != 1) {

				if ($last_vol_year != '') {
				  $output .= "Discontinued (" . $last_vol_year . "),";
				} else {
				  $output .= "Discontinued,";
				}
			  } else {
				$output .= preg_replace('/[ ,]+/', ' ', trim(strip_tags(implode(',', $matches[1])))) . ",";
			  }
			} elseif (preg_match_all($pattern2, $journal->formerly_title, $matches)) {
			  $output .= preg_replace('/[ ,]+/', ' ', trim(strip_tags(implode(',', $matches[1])))) . ",";
			} elseif ($journal->journal_status != 1) {

			  if ($last_vol_year != '') {
				$output .= "Discontinued (" . $last_vol_year . "),";
			  } else {
				$output .= "Discontinued,";
			  }
			}

			$output .= "\n";
		  } //WHILE

		} //IF

		// Download the file
		if ($username == "") {
		  $filename =  "journal-kbart.csv";
		} else {
		  $filename =  $username . "-journal-kbart.csv";
		}
		header('Content-type: application/csv');
		header('Content-Disposition: attachment; filename=' . $filename);
		echo $output;
		exit;
	  }

     /*
     ----------------------------Generate eBook KBart File-----------------------------------
    */
  public function GeteBookKBart($arr_as_string, $username = "")
	  {
		$output = "";
    $output = "publication_title,print_identifier,online_identifier,date_first_chapter_online,num_first_vol_online,num_first_chapter_online,date_last_chapter_online,num_last_vol_online,num_last_chapter_online,title_url,first_author,title_id,coverage_depth,publisher_name,publication_type,date_monograph_published_print,date_monograph_published_online,monograph_volume,first_editor,access_type";
		$output .= "\n";

    $ebooks = DB::select("select y.year, v.ebook_volume_id, e.title, v.volume_name, e.ebook_id,e.image_file_name as ebk_image_file_name,e.index_on as index_on,v.ebook_subtitle, v.ebook_price, v.ebook_price_pod , v.ebook_price_podandpdf, v.isbn,v.issn, v.eisbn,v.eissn, v.doi, v.year_id, v.nid as vnid, v.introduction as introduction ,v.expected_date as expected_date, ea.content_type_access_id, v.ebook_type 
		from ebook e 
			  inner join ebook_volume v on e.ebook_id = v.ebook_id 
			  inner join year y on v.year_id = y.id
			  left join ebook_access ea on ea.content_type_id=v.ebook_volume_id and ea.content_type = 'v'
			  where e.published = 1 
			  and (
				  year(v.publication_date) NOT IN (2016, 2017)
				  or v.ebook_status <> 3
			  )
			  and v.is_uploaded = 1
          and v.ebook_volume_id in ($arr_as_string) 
			  order by e.title, cast(v.volume_name  as signed) ASC");
		if ($ebooks) {
		  foreach ($ebooks as $ebook) {
        $url = 'https://www.eurekaselect.com/ebook_volume/' . $ebook->ebook_volume_id;

			$date_first_author = '';
			$date_first_editor = '';

			$date_first_chapter = '';
			$num_first_vol = '';
			$num_first_chapter = '';

			$date_last_chapter = '';
			$num_last_vol = '';
			$num_last_chapter = '';

			$row = DB::select("select CONCAT_WS(' ',a.first_name,IFNULL(a.initials,''),a.last_name) as author from author a
					  inner join ebook_volume e on a.ebook_volume_id = e.ebook_volume_id 
					  WHERE a.ebook_volume_id =$ebook->ebook_volume_id and a.iseditorcoeditor='a'
					  ORDER BY a.author_id asc LIMIT 1");
			if ($row) {
			  if ($row[0]->author != "") {
				$date_first_author = $row[0]->author;
			  }
			}

			$row1 = DB::select("select CONCAT_WS(' ',a.first_name,IFNULL(a.initials,''),a.last_name) as editor from author a
					  inner join ebook_volume e on a.ebook_volume_id = e.ebook_volume_id 
					  WHERE a.ebook_volume_id =$ebook->ebook_volume_id and a.iseditorcoeditor='e'
					  ORDER BY a.author_id asc LIMIT 1");

			if ($row1) {
			  if ($row1[0]->editor != "") {
				$date_first_editor = trim($row1[0]->editor);
			  }
			}

			$row2 = DB::select("select y.year, v.volume_name, c.chapter_no
					  FROM ebook e 
					  INNER JOIN ebook_volume v ON v.ebook_id = e.ebook_id
					  INNER JOIN chapter c ON c.ebook_volume_id = v.ebook_volume_id 
					  INNER JOIN year y ON v.year_id = y.id
					  WHERE e.ebook_id =$ebook->ebook_id and c.is_uploaded=1
					  ORDER BY y.year asc, v.volume_name asc, c.chapter_no asc 
					  LIMIT 1");
			if ($row2) {
			  if ($row2[0]->year != "")
				$date_first_chapter = $row2[0]->year . "-01-01";

			  $num_first_vol = $row2[0]->volume_name;
			  $num_first_chapter = $row2[0]->chapter_no;
			}

			$row3 = DB::select("select y.year, v.volume_name, c.chapter_no
					  FROM ebook e 
					  INNER JOIN ebook_volume v ON v.ebook_id = e.ebook_id
					  INNER JOIN chapter c ON c.ebook_volume_id = v.ebook_volume_id 
					  INNER JOIN year y ON v.year_id = y.id
					  WHERE e.ebook_id =$ebook->ebook_id and c.is_uploaded=1
					  ORDER BY y.year desc, v.volume_name desc, c.chapter_no desc 
					  LIMIT 1");
			if ($row3) {
			  if ($row3[0]->year != "")
				$date_last_chapter = $row3[0]->year . "-01-01";

			  $num_last_vol = $row3[0]->volume_name;
			  $num_last_chapter = $row3[0]->chapter_no;
			}

			if ($ebook->ebook_type == 2) {
			  $output .= str_replace(',', ' ', strip_tags($ebook->title)) . " Volume " . $ebook->volume_name . ",";
			} elseif (!empty($ebook->ebook_subtitle)) {
			  $output .= str_replace(',', ' ', strip_tags($ebook->title)) . " " . str_replace(',', ' ', strip_tags($ebook->ebook_subtitle)) . ",";
			} else {
			  $output .= str_replace(',', ' ', strip_tags($ebook->title)) . ",";
			}

			$output .= str_replace("-", "", $ebook->isbn) . ",";
			$output .= $ebook->eisbn . ",";
			$output .= ",";
			$output .= ",";
			$output .= ",";
			$output .= ",";
			$output .= ",";
			$output .= ",";
			$output .= $url . ",";
			$output .= htmlspecialchars(str_replace(",", " ", $date_first_author)) . ",";
			$output .= $ebook->doi . ",";
			$output .= "Fulltext,";
			$output .= "Bentham Science,";
			$output .= "monograph,";
			$output .= $ebook->year . ",";
			$output .= $ebook->year . ",";
			$output .= $ebook->volume_name . ",";
			$output .= htmlspecialchars(str_replace(",", " ", $date_first_editor)) . ",";
			if ($ebook->content_type_access_id == null) {
			  $output .= "P,";
			} else {
			  $output .= "F,";
			}
			$output .= "\n";
		  } //WHILE

		} //IF

		// Download the file
		if ($username == "") {
		  $filename =  "ebook-kbart.csv";
		} else {
		  $filename =  $username . "-ebook-kbart.csv";
		}
		header('Content-Encoding: utf-8');
		header('Content-type: text/csv; charset=utf-8');
		header('Content-Disposition: attachment; filename=' . $filename);
		echo $output;
		exit;
	  }
    public function getUserjournalKbart(Request $request)
    {
      $param['journal_id'] = $journal_id = $request->get('journal_id');
      $arr_as_string = implode( ',', $journal_id );

      $this->GetJournalKBart($arr_as_string);
    }
    public function getSubscribeJournalKbartData($param)
    {
      $user_id = Crypt::decryptString($param);
      
    $users = DB::table('users')->select('name')->where('id', $user_id)->first();
    $username = $users->name;

      $arr_as_string = DB::table('user_access_journal_volume_corporate')->selectRaw('DISTINCT(journal_id) as journal_id')->where('user_id','=',$user_id)->pluck('journal_id')->toArray();
      
      $arr_as_string = implode( ',', $arr_as_string );
    $this->GetJournalKBart($arr_as_string, $username);
    }

    public function getUserEbookKbart(Request $request)
    {
      $param['ebook_volume_id'] = $ebook_volume_id = $request->get('ebook_volume_id');
      $arr_as_string = implode( ',', $ebook_volume_id );

      $this->GeteBookKBart($arr_as_string);
    }
    public function getSubscribeEbookKbartData($param)
    {
      $user_id = Crypt::decryptString($param);
     
    $users = DB::table('users')->select('name')->where('id', $user_id)->first();
    $username = $users->name;

      $arr_as_string = DB::table('user_access_ebook_volume_corporate')->selectRaw('DISTINCT(ebook_id) as ebook_id')->where('user_id','=',$user_id)->pluck('ebook_id')->toArray();
      
      $arr_as_string = implode( ',', $arr_as_string );
    $this->GeteBookKBart($arr_as_string, $username);
    }
}
