<?php

namespace App\Http\Controllers;

use App\User;
use DB;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Validator;
use Illuminate\Support\Facades\View;

class CounterReportController extends BenthamController
{

    public function index(Request $request)
    {

        return View::make('admin.counter_report.index');
    }

    public function CheckUser($user_name)
    {

        $user_obj = User::getUserByName($user_name);

        if ($user_obj) {
            echo $user_obj->id;die;
        } else {
            echo '0';die;
        }

    }

    public function Export(Request $request)
    {

        $input = $request->all();

        $validator = Validator::make($request->all(), [
            'user_id'       => 'required',
            'user_name'     => 'required',
            'reporttype'    => 'required',
            'frommonthwise' => 'required',
            'tomonthwise'   => 'required',
            'toyearwise'    => 'required',
        ]);

        if ($validator->fails()) {

            $messages = $validator->messages();
            
            if($request->get("user")=='userEx'){
                return redirect()->back()->withErrors($validator)->with('input', $input);           
                
            }else{
                return View::make('admin.counter_report.index')
                    ->withErrors($validator)
                    ->with('input', $input);
            }
        } else {

            $gentype       = $request->get("exportrange");
            $user_id       = $request->get("user_id");
            $reptype       = $request->get("reporttype");
            $frommonthwise = $request->get('frommonthwise');
            $fromyearwise  = $request->get('toyearwise');
            $tomonthwise   = $request->get('tomonthwise');
            $toyearwise    = $request->get('toyearwise');
            $exporttype    = $request->get('exporttype');
            $fromdate      = $toyearwise . '-' . $frommonthwise . '-01';
            $todate        = $toyearwise . '-' . $tomonthwise . '-28';

            $UDRBegin   = date('Y-m-01', strtotime($fromdate));
            $UDREnd     = date('Y-m-t', strtotime($todate));
            $monthsyear = $this->getMonthsInRange($UDRBegin, $UDREnd);

            if ($reptype == 6) {

                $this->detailreport_jr2($request,$input);
                exit;
            } else if ($reptype == 7) {

                $this->detail_detailreport_jr2($request,$input);
                exit;
            } else if ($reptype == 8) {

                $this->detail_detailreport_jr1($request,$input);

                exit;
            }

            for ($j = 0; $j < count($monthsyear); $j++) {
                $monthNum  = $monthsyear[$j]['month'];
                $year      = $monthsyear[0]['year'];
                $monthName = date("F", mktime(0, 0, 0, $monthNum, 10));
            }

            $resultuser = User::getUserById($user_id);
            $org_name   = $resultuser->first_name . ' ' . $resultuser->last_name;

            if ($gentype == "2" && $reptype == "1") {

                $type        = "Number of Successful Full-Text Article Requests by Month and Journal";
                $report_type = "Journal Report 1 (R4)";
                $title       = "Total for all journals";
                $issn        = "Print ISSN";
                $eissn       = "Online ISSN";

            }

            if ($gentype == "2" && $reptype == "2") {

                $type        = "Access Denied to Full-text Articles by Month, Journal and Category";
                $report_type = "Journal Report 2 (R4)";
                $title       = "Total for all journals";
                $issn        = "Print ISSN";
                $eissn       = "Online ISSN";

            }
            if ($gentype == "2" && $reptype == "3") {

                $type        = "Number of Successful Full-Text Article Requests by  Year-of-Publication (YOP) and Journal";
                $report_type = "Journal Report 5 (R4)";
                $title       = "Total for all journals";
                $issn        = "Print ISSN";
                $eissn       = "Online ISSN";

            }
            if ($gentype == "0" && $reptype == "4") {

                $type        = "Number of Successful Title Requests by Month and Title";
                $title       = "Total for all titles";
                $report_type = "Book Report 1 (R4)";
                $issn        = "ISBN";
                $eissn       = "ISSN";

            }

            if ($gentype == "1" && $reptype == "5") {

                $type        = "Number of Successful Section Requests by Month and Title";
                $report_type = "Book Report 2 (R4)";
                $title       = "Total for all titles";
                $issn        = "ISBN";
                $eissn       = "ISSN";

            }

            // if it is journal
            if ($gentype == "2") {

                if ($reptype == "1") {

                    $query = "select j.title,j.issn ,j.eissn, j.journal_id,
                    count(CASE WHEN download_file_type='1' OR download_file_type='5' THEN article_id END) AS 'PDF',
                    count(CASE WHEN download_file_type='2' THEN article_id END) AS 'HTML',
                    count(CASE WHEN download_file_type='3' OR download_file_type='4' THEN article_id END) AS 'OTHER',
                    '' as ebook_id,
                    '' as volume_id,
                    article_id,
                    COUNT(CASE WHEN date_format(from_unixtime(timestamp),'%M')='January' THEN article_id END) AS 'January',
                    COUNT(CASE WHEN date_format(from_unixtime(timestamp),'%M')='February' THEN article_id END) AS 'February',
                    COUNT(CASE WHEN date_format(from_unixtime(timestamp),'%M')='March' THEN article_id END) AS 'March',
                    COUNT(CASE WHEN date_format(from_unixtime(timestamp),'%M')='April' THEN article_id END) AS 'April',
                    COUNT(CASE WHEN date_format(from_unixtime(timestamp),'%M')='May' THEN article_id END) AS 'May',
                    COUNT(CASE WHEN date_format(from_unixtime(timestamp),'%M')='June' THEN article_id END) AS 'June',
                    COUNT(CASE WHEN date_format(from_unixtime(timestamp),'%M')='July' THEN article_id END) AS 'July',
                    COUNT(CASE WHEN date_format(from_unixtime(timestamp),'%M')='August' THEN article_id END) AS 'August',
                    COUNT(CASE WHEN date_format(from_unixtime(timestamp),'%M')='September' THEN article_id END) AS 'September',
                    COUNT(CASE WHEN date_format(from_unixtime(timestamp),'%M')='October' THEN article_id END) AS 'October',
                    COUNT(CASE WHEN date_format(from_unixtime(timestamp),'%M')='November' THEN article_id END) AS 'November',
                    COUNT(CASE WHEN date_format(from_unixtime(timestamp),'%M')='December' THEN article_id END) AS 'December'
                    from download_count_articles a inner join journal j on a.journal_id = j.journal_id
                    and to_days(from_unixtime(timestamp)) between to_days('" . $UDRBegin . "') and to_days('" . $UDREnd . "') and
                    a.user_id='" . $user_id . "'
                    group by title";
                    //print($query); exit;

                } elseif ($reptype == "2") {

                    $query = "select j.title,a.jid,j.issn,j.eissn,a.catid as category, c.cat_type cat_type,
                    '0' AS 'PDF',
                    '0' AS 'HTML',
                    '0' AS 'OTHER',
                    '' as ebook_id,
                    '' as volume_id,
                    COUNT(CASE WHEN date_format(from_unixtime(created_on),'%M')='January' THEN aid END) AS 'January',
                    COUNT(CASE WHEN date_format(from_unixtime(created_on),'%M')='February' THEN aid END) AS 'February',
                    COUNT(CASE WHEN date_format(from_unixtime(created_on),'%M')='March' THEN aid END) AS 'March',
                    COUNT(CASE WHEN date_format(from_unixtime(created_on),'%M')='April' THEN aid END) AS 'April',
                    COUNT(CASE WHEN date_format(from_unixtime(created_on),'%M')='May' THEN aid END) AS 'May',
                    COUNT(CASE WHEN date_format(from_unixtime(created_on),'%M')='June' THEN aid END) AS 'June',
                    COUNT(CASE WHEN date_format(from_unixtime(created_on),'%M')='July' THEN aid END) AS 'July',
                    COUNT(CASE WHEN date_format(from_unixtime(created_on),'%M')='August' THEN aid END) AS 'August',
                    COUNT(CASE WHEN date_format(from_unixtime(created_on),'%M')='September' THEN aid END) AS 'September',
                    COUNT(CASE WHEN date_format(from_unixtime(created_on),'%M')='October' THEN aid END) AS 'October',
                    COUNT(CASE WHEN date_format(from_unixtime(created_on),'%M')='November' THEN aid END) AS 'November',
                    COUNT(CASE WHEN date_format(from_unixtime(created_on),'%M')='December' THEN aid END) AS 'December'
                    from access_denied_report a
                    inner join access_denied_report_category c on c.id=a.catid
                    inner join journal j on a.jid = j.journal_id
                    and to_days(from_unixtime(created_on)) between to_days('" . $UDRBegin . "') and to_days('" . $UDREnd . "')
                 and a.userid='" . $user_id . "' group by title order by title";
                    //print($query); exit;

                } elseif ($reptype == "3") {

                    $endyear   = 2000;
                    $startyear = date('Y') + 1;

                    for ($i = $startyear; $i >= $endyear; $i--) {
                        $years[] = $i;
                    }

                    //print_r($years); die;
                    $query = "";
                    $yop_year=''; 
                    $case='';   
                    $year_count = 0;
                    foreach ($years as $ayear) {
                        //y.year as '$year'
                        $yop_year .= "IFNULL(sum(YOP_$ayear),0) as 'YOP $ayear',";
                        $case .= " case when y.year = '$ayear' then count(art1.article_id) end as YOP_$ayear,";
                        $year_count++;

                    }
                    $case     = substr($case, 0, strlen($case) - 1);
                    $yop_year = substr($yop_year, 0, strlen($yop_year) - 1);
                    $query .= "
                    select title,journal_id,issn,eissn, IFNULL(sum(article_in_press),0) as article_in_press,
                    $yop_year
                    ,IFNULL(sum(YOPPre_2000),0) as 'YOPPre_2000'
                    ,IFNULL(sum(YOPunknown),0) as 'YOPunknown'
                    from(
                    select j.title,a.journal_id,j.issn,j.eissn,
                    count(art2.article_id)  as article_in_press,
                    $case,
                    case when y.year < '2000' then count(art1.article_id) end as YOPPre_2000,
                    case when y.year = '' then count(art1.article_id) end as YOPunknown
                    from download_count_articles a
                    inner join journal j on a.journal_id = j.journal_id
                    inner join volume v on a.volume_id = v.volume_id
                    inner join year y on y.id=v.year_id
                    left join article art1 on art1.article_id = a.article_id and art1.is_epub=0
                    left join article art2 on art2.article_id = a.article_id and art2.is_epub!=0
                    where a.user_id='" . $user_id . "' and
                    to_days(from_unixtime(timestamp)) between to_days('" . $UDRBegin . "') and to_days('" . $UDREnd . "')
                    group by a.journal_id,y.year
                    ) as tmp
                    group by tmp.journal_id
                    ";
                }

                //print($query); exit;
                $result = DB::select($query);

            }
            //If ebook
            if ($gentype == "0") {

                /// Total Records Per Page ///
                if ($reptype == "4") {
                    $query = "select e.title,v.isbn as isbn,v.issn as issn,a.ebook_id, a.volume_id,v.volume_name as volume, v.doi,
                            count(CASE WHEN download_file_type='1' OR download_file_type='5' THEN volume_id END) AS 'PDF',
                            count(CASE WHEN download_file_type='2' THEN volume_id END) AS 'HTML',
                            count(CASE WHEN download_file_type='3' OR download_file_type='4' THEN volume_id END) AS 'OTHER',
                            COUNT(CASE WHEN date_format(from_unixtime(timestamp),'%M')='January' THEN volume_id END) AS 'January',
                            COUNT(CASE WHEN date_format(from_unixtime(timestamp),'%M')='February' THEN volume_id END) AS 'February',
                            COUNT(CASE WHEN date_format(from_unixtime(timestamp),'%M')='March' THEN volume_id END) AS 'March',
                            COUNT(CASE WHEN date_format(from_unixtime(timestamp),'%M')='April' THEN volume_id END) AS 'April',
                            COUNT(CASE WHEN date_format(from_unixtime(timestamp),'%M')='May' THEN volume_id END) AS 'May',
                            COUNT(CASE WHEN date_format(from_unixtime(timestamp),'%M')='June' THEN volume_id END) AS 'June',
                            COUNT(CASE WHEN date_format(from_unixtime(timestamp),'%M')='July' THEN volume_id END) AS 'July',
                            COUNT(CASE WHEN date_format(from_unixtime(timestamp),'%M')='August' THEN volume_id END) AS 'August',
                            COUNT(CASE WHEN date_format(from_unixtime(timestamp),'%M')='September' THEN volume_id END) AS 'September',
                            COUNT(CASE WHEN date_format(from_unixtime(timestamp),'%M')='October' THEN volume_id END) AS 'October',
                            COUNT(CASE WHEN date_format(from_unixtime(timestamp),'%M')='November' THEN volume_id END) AS 'November',
                            COUNT(CASE WHEN date_format(from_unixtime(timestamp),'%M')='December' THEN volume_id END) AS 'December'
                            from download_count_volume a inner join ebook_volume v on v.ebook_volume_id =a.volume_id
                            inner join ebook e on v.ebook_id = e.ebook_id
                            where  to_days(from_unixtime(timestamp)) between to_days('" . $UDRBegin . "') and to_days('" . $UDREnd . "') and
                            a.user_id='" . $user_id . "' group by volume_Id order by title asc";
                    //print($query); die;

                    $result = DB::select($query);

                }
            }

            //If chapter
            if ($gentype == "1") {
                /// Total Records Per Page ///
                if ($reptype == "5") {
                    $query = "select e.title, v.isbn as isbn, v.issn as issn, dcc.ebook_id, v.doi, v.volume_name as volume, v.ebook_volume_id as volume_id,
                                count(CASE WHEN download_file_type='1' OR download_file_type='5' THEN dcc.chapter_id END) AS 'PDF',
                                count(CASE WHEN download_file_type='2' THEN dcc.chapter_id END) AS 'HTML',
                                count(CASE WHEN download_file_type='3' OR download_file_type='4' THEN dcc.chapter_id END) AS 'OTHER',
                                COUNT(CASE WHEN date_format(from_unixtime(timestamp),'%M')='January' THEN dcc.chapter_id END) AS 'January',
                                COUNT(CASE WHEN date_format(from_unixtime(timestamp),'%M')='February' THEN dcc.chapter_id END) AS 'February',
                                COUNT(CASE WHEN date_format(from_unixtime(timestamp),'%M')='March' THEN dcc.chapter_id END) AS 'March',
                                COUNT(CASE WHEN date_format(from_unixtime(timestamp),'%M')='April' THEN dcc.chapter_id END) AS 'April',
                                COUNT(CASE WHEN date_format(from_unixtime(timestamp),'%M')='May' THEN dcc.chapter_id END) AS 'May',
                                COUNT(CASE WHEN date_format(from_unixtime(timestamp),'%M')='June' THEN dcc.chapter_id END) AS 'June',
                                COUNT(CASE WHEN date_format(from_unixtime(timestamp),'%M')='July' THEN dcc.chapter_id END) AS 'July',
                                COUNT(CASE WHEN date_format(from_unixtime(timestamp),'%M')='August' THEN dcc.chapter_id END) AS 'August',
                                COUNT(CASE WHEN date_format(from_unixtime(timestamp),'%M')='September' THEN dcc.chapter_id END) AS 'September',
                                COUNT(CASE WHEN date_format(from_unixtime(timestamp),'%M')='October' THEN dcc.chapter_id END) AS 'October',
                                COUNT(CASE WHEN date_format(from_unixtime(timestamp),'%M')='November' THEN dcc.chapter_id END) AS 'November',
                                COUNT(CASE WHEN date_format(from_unixtime(timestamp),'%M')='December' THEN dcc.chapter_id END) AS 'December'
                                from download_count_chapter dcc
                                inner join chapter c on dcc.chapter_id=c.chapter_id
                                inner join ebook_volume v on c.ebook_volume_id = v.ebook_volume_id
                                inner join ebook e on v.ebook_id = e.ebook_id
                                and to_days(from_unixtime(timestamp)) between  to_days('" . $UDRBegin . "') and to_days('" . $UDREnd . "')
                                and dcc.user_id='" . $user_id . "' group by e.ebook_id, c.ebook_volume_id order by e.title asc";

                    $result = DB::select($query);
                }
            }

            if ($exporttype == 1) {
//export in Excel

                $year = substr($year, 2, 2);
                if ($reptype == "2") {
                    $colspan = "20";
                } elseif ($reptype == "1") {
                    $colspan = "21";
                } elseif ($reptype == "4" || $reptype == "5") {
                    $colspan = "19";
                }

                if ($gentype == "2" and $reptype == "3") {

                    $colspanccnt = 9;
                    //print_r($years); die;
                    $tableHeader_years='';
                    foreach ($years as $ayear) {
                        $tableHeader_years .= "  <td bgcolor='#CCCCCC'><b>YOP " . $ayear . "</b></td> ";
                        $colspanccnt++;
                    }
                    $tableHeader = "<table width='800' align='center' border='1'>

                                <tr>
                                    <td ><b>" . $report_type . "</b></td>
                                    <td colspan='" . ($colspanccnt) . "'><b>" . $type . "</b></td>

                                </tr>

                                <tr>
                                    <td><b>" . $org_name . "</b></td>
                                    <td colspan='" . $colspanccnt . "' align='left'>&nbsp;</td>
                                </tr>
                                <tr>
                                    <td><b><!--Institutional Identifier-->&nbsp;</b></td>
                                    <td colspan='" . $colspanccnt . "' align='left'>&nbsp;</td>
                                </tr>
                                <tr>
                                    <td><b>Period covered by Report:</b></td>
                                    <td colspan='" . $colspanccnt . "' align='left'>&nbsp;</td>
                                </tr>
                                <tr>
                                    <td >" . $UDRBegin . " to " . $UDREnd . "</td>
                                    <td colspan='" . $colspanccnt . "' align='left'>&nbsp;</td>
                                </tr>
                                <tr>
                                    <td><b>Date run:</b></td>
                                    <td colspan='" . $colspanccnt . "' align='left'>&nbsp;</td>
                                </tr>
                                <tr>
                                    <td align='left'>'" . date("Y-m-d") . "</td>
                                    <td colspan='" . $colspanccnt . "' align='left'>&nbsp;</td>
                                </tr>

                                <tr align='center'>
                                    <td align='left' bgcolor='#CCCCCC'><b>Journal</b></td>
                                    <td bgcolor='#CCCCCC'><b>Publisher</b></td>
                                    <td bgcolor='#CCCCCC'><b>Platform</b></td>
                                    <td bgcolor='#CCCCCC'><b>Journal DOI</b></td>
                                    <td bgcolor='#CCCCCC'><b>Proprietary Identifier</b></td>
                                    <td bgcolor='#CCCCCC'><b>" . $issn . "</b></td>
                                    <td bgcolor='#CCCCCC'><b>" . $eissn . "</b></td>
                                    <td bgcolor='#CCCCCC'><b>Articles in Press</b></td>
                                    ";

                    $tableHeader .= $tableHeader_years;
                    $tableHeader .= "<td bgcolor='#CCCCCC'><b>YOP Pre-2000</b></td>";
                    $tableHeader .= "<td bgcolor='#CCCCCC'><b>YOP unknown</b></td>";

                    $tableHeader .= " </tr>   ";

                    $tr_data         = "";
                    $total_sale_year = array();
                    $article_inpress = 0;
                    $YOPPrecount     = 0;
                    $YOPunknowncount = 0;
                    
                    $jan     = 0;
                    $feb     = 0;
                    $mar     = 0;
                    $apr     = 0;
                    $may     = 0;
                    $jun     = 0;
                    $july    = 0;
                    $aug     = 0;
                    $sep     = 0;
                    $oct     = 0;
                    $nov     = 0;
                    $dec     = 0;

                    //while ($row = db_fetch_array($result)) {
                    foreach ($result as $row) {

                        $row = (array) $row;
                        
                        $tr_data .= "<tr align='center'> ";

                        $tr_data .= "<td align='left'>" . $row['title'] . "</td>";

                        $article_inpress = $article_inpress + $row['article_in_press'];
                        $tr_data .= "<td>Bentham Science Publishers</td>
                                            <td>Bentham Science</td>
                                            <td>&nbsp;</td>
                                            <td>&nbsp;</td>
                                            <td>" . $row['issn'] . "</td>
                                            <td>" . $row['eissn'] . "</td>
                                            <td>" . $row['article_in_press'] . "</td>";

                        foreach ($years as $ayear) {
                            //if($row['YOP'.$ayear]!='0'){
                            if(!isset($total_sale_year['YOP' . $ayear])){
                                $total_sale_year['YOP' . $ayear] = 0;
                            }
                            $count                           = $row['YOP ' . $ayear];
                            $total_sale_year['YOP' . $ayear] = $total_sale_year['YOP' . $ayear] + $count;
                            $tr_data .= "    <td>" . $count . "</td> ";
                            //}
                        }
                        $YOPPrecount     = $row['YOPPre_2000'] + $YOPPrecount;
                        $YOPunknowncount = $row['YOPunknown'] + $YOPunknowncount;

                        $tr_data .= "<td>" . $row['YOPPre_2000'] . "</td>";
                        $tr_data .= "<td>" . $row['YOPunknown'] . "</td>";
                        $tr_data .= "</tr>";

                    } //while

                    $month_wise_total = $jan + $feb + $mar + $apr + $may + $jun + $july + $aug + $sep + $oct + $nov + $dec;

                    $year_total = "<tr align='center'  >
                                            <td align='left' bgcolor='#CCCCCC'><b>" . $title . "</b></td>
                                            <td bgcolor='#CCCCCC'>&nbsp;</td>
                                            <td bgcolor='#CCCCCC'>Bentham Science</td>
                                            <td bgcolor='#CCCCCC'>&nbsp;</td>
                                            <td bgcolor='#CCCCCC'>&nbsp;</td>
                                            <td bgcolor='#CCCCCC'>&nbsp;</td>
                                            <td bgcolor='#CCCCCC'>&nbsp;</td>
                                            <td bgcolor='#CCCCCC'><b>" . $article_inpress . "</b></td>";
                    foreach ($total_sale_year as $yyear) {

                        $year_total .= " <td bgcolor='#CCCCCC'><b>" . $yyear . "</b></td> ";

                    }
                    $year_total .= " <td bgcolor='#CCCCCC'><b>" . $YOPPrecount . "</b></td> ";
                    $year_total .= " <td bgcolor='#CCCCCC'><b>" . $YOPunknowncount . "</b></td> ";
                    $year_total .= "</tr>";

                    $table = $tableHeader . $year_total . $tr_data . "</table>";
                    //print($table); die;

                } else {

                    $tableHeader = "<table width='800' align='center' border='1'>

                            <tr>
                                <td><b>" . $report_type . "</b></td>
                                <td colspan='$colspan'><b>" . $type . "</b></td>

                            </tr>

                            <tr>
                                <td><b>" . $org_name . "</b></td>";
                    if ($reptype == 5) {
                        $tableHeader .= "<td align='left'><b>Section Type:</b></td>
                                    <td colspan='18' align='left'>&nbsp;</td>";
                    } else {
                        $tableHeader .= "<td colspan='$colspan' align='left'>&nbsp;</td>";
                    }
                    $tableHeader .= "</tr>
                            <tr>
                                    <td><b>&nbsp;<!--Institutional Identifier--></b></td>";
                    if ($reptype == 5) {
                        $sectiontype = "Chapter";
                        $tableHeader .= "<td align='left'><b>" . $sectiontype . "</b></td>
                                                        <td colspan='18' align='left'>&nbsp;</td>";
                    } else {
                        $tableHeader .= "<td colspan='$colspan' align='left'>&nbsp;</td>";
                    }
                    $tableHeader .= "</tr>
                            <tr>
                                    <td><b>Period covered by Report:</b></td>
                                    <td colspan='$colspan' align='left'>&nbsp;</td>
                                </tr>
                                <tr>
                                    <td >" . $UDRBegin . " to " . $UDREnd . "</td>
                                    <td colspan='$colspan' align='left'>&nbsp;</td>
                                </tr>
                            <tr>
                                <td align='left'><b>Date run:</b></td>
                                <td colspan='$colspan' align='left'>&nbsp;</td>
                            </tr>
                            <tr>
                                <td align='left'>'" . date("Y-m-d") . "</td>
                                <td colspan='$colspan' align='left'>&nbsp;</td>
                            </tr>

                            <tr align='center'>";
                    if ($reptype == '4' || $reptype == '5') {
                        $tableHeader .= "<td align='left' bgcolor='#CCCCCC'><b>&nbsp;</b></td>";
                    } else {
                        $tableHeader .= "<td align='left' bgcolor='#CCCCCC'><b>Journal</b></td>";
                    }
                    $tableHeader .= "<td bgcolor='#CCCCCC'><b>Publisher</b></td>
                                <td bgcolor='#CCCCCC'><b>Platform</b></td>";
                    if ($reptype == '4' || $reptype == '5') {
                        $tableHeader .= "<td bgcolor='#CCCCCC'><b>Book DOI</b></td>";
                    } else {
                        $tableHeader .= "<td bgcolor='#CCCCCC'><b>Journal DOI</b></td>";
                    }
                    $tableHeader .= "<td bgcolor='#CCCCCC'><b>Proprietary Identifier</b></td>
                                <td bgcolor='#CCCCCC'><b>" . $issn . "</b></td>
                                <td bgcolor='#CCCCCC'><b>" . $eissn . "</b></td>";

                    if ($reptype == "2") {
                        $tableHeader .= "<td bgcolor='#CCCCCC'><b>Access Denied Category</b></td>";
                    }
                    $tableHeader .= " <td bgcolor='#CCCCCC'><b>Reporting Period Total</b></td>";
                    if ($reptype == "1") {
                        $tableHeader .= "<td bgcolor='#CCCCCC'><b>Reporting Period HTML</b></td>
                                       <td bgcolor='#CCCCCC'><b>Reporting Period PDF</b></td>";
                    }

                    for ($j = 0; $j < count($monthsyear); $j++) {
                        $monthNum  = $monthsyear[$j]['month'];
                        $monthName = date("M", mktime(0, 0, 0, $monthNum, 10));
                        $tableHeader .= "<td bgcolor='#CCCCCC'><b>'$monthName-20" . $year . "</b></td>";

                    }

                    $tr_data = "";
                    $jan     = 0;
                    $feb     = 0;
                    $mar     = 0;
                    $apr     = 0;
                    $may     = 0;
                    $jun     = 0;
                    $july    = 0;
                    $aug     = 0;
                    $sep     = 0;
                    $oct     = 0;
                    $nov     = 0;
                    $dec     = 0;
                    $html    = 0;
                    $pdf     = 0;
                    $other   = 0;

                    //while ($row = db_fetch_object($result)) {
                    foreach ($result as $row) {
                        $total_sale = round($row->January, 2) + round($row->February, 2) + round($row->March, 2) + round($row->April, 2) +
                        round($row->May, 2) + round($row->June, 2) + round($row->July, 2) + round($row->August, 2) +
                        round($row->September, 2) + round($row->October, 2) + round($row->November, 2) + round($row->December, 2);

                        $jan  = $jan + round($row->January, 2);
                        $feb  = $feb + round($row->February, 2);
                        $mar  = $mar + round($row->March, 2);
                        $apr  = $apr + round($row->April, 2);
                        $may  = $may + round($row->May, 2);
                        $jun  = $jun + round($row->June, 2);
                        $july = $july + round($row->July, 2);
                        $aug  = $aug + round($row->August, 2);
                        $sep  = $sep + round($row->September, 2);
                        $oct  = $oct + round($row->October, 2);
                        $nov  = $nov + round($row->November, 2);
                        $dec  = $dec + round($row->December, 2);

                        $html  = $html + $row->HTML;
                        $pdf   = $pdf + $row->PDF;
                        $other = $row->OTHER;
                        //$total_sale=$html+$pdf+$other;

                        $tr_data .= "<tr align='center'> ";
                        $index = 0;
                        //if there are multiple volume in eBook the show volume name
                        if ($index == 0) {
                            if ($this->CountMultipleVolumeExist($row->ebook_id, $row->volume_id) > 0) {
                                $volumeLabel = 'Volume: ' . $row->volume;
                            } else {
                                $volumeLabel = '';
                            }
                        } else {
                            $volumeLabel = '';
                        }
                        if ($gentype == "2") //if journal
                        {
                            $tr_data .= "<td align='left'>" . $row->title . "</td>";
                        } else {
                            $tr_data .= "<td align='left'>" . $row->title . ' ' . $volumeLabel . "</td>";
                        }

                        $tr_data .= "<td>Bentham Science Publishers</td>
                                <td>Bentham Science</td>";

                        if ($reptype == '4' || $reptype == '5') {
                            $tr_data .= "<td>" . $row->doi . "</td>";
                        } else {
                            $tr_data .= "<td>&nbsp;</td>";
                        }

                        $tr_data .= "<td>&nbsp;</td>";
                        if ($reptype == '4' || $reptype == '5') {
                            $tr_data .= "<td>" . $row->isbn . "</td>
                                        <td>" . $row->issn . "</td>";
                        } else {
                            $tr_data .= "<td>" . $row->issn . "</td>
                                            <td>" . $row->eissn . "</td>";
                        }
                        if ($reptype == "2") {
                            $tr_data .= "<td>Access denied: content item not licenced</td>";
                        }

                        $tr_data .= "<td>" . $total_sale . "</td>";
                        //print($total_sale);

                        if ($reptype == "1") {
                            $tr_data .= "<td>" . $row->HTML . "</td>
                                <td>" . $row->PDF . "</td>";

                        }

                        for ($j = 0; $j < count($monthsyear); $j++) {
                            $monthNum  = $monthsyear[$j]['month'];
                            $monthName = date("F", mktime(0, 0, 0, $monthNum, 10));
                            //$tr_data .="<td>".round($row->$monthName,2)."</td>";
                            if ($monthName == "January") {
                                $tr_data .= "<td>" . round($row->January, 2) . "</td>";
                            }

                            if ($monthName == "February") {
                                $tr_data .= "<td>" . round($row->February, 2) . "</td>";
                            }

                            if ($monthName == "March") {
                                $tr_data .= "<td>" . round($row->March, 2) . "</td>";
                            }

                            if ($monthName == "April") {
                                $tr_data .= "<td>" . round($row->April, 2) . "</td>";
                            }

                            if ($monthName == "May") {
                                $tr_data .= "<td>" . round($row->May, 2) . "</td>";
                            }

                            if ($monthName == "June") {
                                $tr_data .= "<td>" . round($row->June, 2) . "</td>";
                            }

                            if ($monthName == "July") {
                                $tr_data .= "<td>" . round($row->July, 2) . "</td>";
                            }

                            if ($monthName == "August") {
                                $tr_data .= "<td>" . round($row->August, 2) . "</td>";
                            }

                            if ($monthName == "September") {
                                $tr_data .= "<td>" . round($row->September, 2) . "</td>";
                            }

                            if ($monthName == "October") {
                                $tr_data .= "<td>" . round($row->October, 2) . "</td>";
                            }

                            if ($monthName == "November") {
                                $tr_data .= "<td>" . round($row->November, 2) . "</td>";
                            }

                            if ($monthName == "December") {
                                $tr_data .= "<td>" . round($row->December, 2) . "</td>";
                            }

                        }

                        $tr_data .= "</tr>";

                    } //while

                    $month_wise_total = $jan + $feb + $mar + $apr + $may + $jun + $july + $aug + $sep + $oct + $nov + $dec;

                    $month_total = "<tr align='center'>
                                <td align='left' bgcolor='#CCCCCC'><b>" . $title . "</b></td>";
                    if ($gentype == '2') {
                        $month_total .= "<td bgcolor='#CCCCCC'>Bentham Science Publishers</td>";
                    } else {
                        $month_total .= "<td bgcolor='#CCCCCC'>&nbsp;</td>";
                    }
                    if ($gentype != 0) {
                        $month_total .= "<td bgcolor='#CCCCCC'>Bentham Science</td>";
                    } else {
                        $month_total .= "<td bgcolor='#CCCCCC'>&nbsp;</td>";
                    }
                    $month_total .= "<td bgcolor='#CCCCCC'>&nbsp;</td>
                                <td bgcolor='#CCCCCC'>&nbsp;</td>
                                <td bgcolor='#CCCCCC'>&nbsp;</td>
                                <td bgcolor='#CCCCCC'>&nbsp;</td>";
                    if ($reptype == "2") {
                        $month_total .= "<td bgcolor='#CCCCCC'><b>Access denied: content item not licenced<b></td>";
                    }

                    $month_total .= " <td bgcolor='#CCCCCC'><b>" . $month_wise_total . "</b></td>";
                    if ($reptype == "1") {
                        $month_total .= "<td bgcolor='#CCCCCC'><b>" . $html . "</b></td>
                                <td bgcolor='#CCCCCC'><b>" . $pdf . "</b></td>
                                ";
                    }
                    //<td bgcolor='#CCCCCC'><b>".$month_wise_total."</b></td>
                    for ($j = 0; $j < count($monthsyear); $j++) {
                        $monthNum  = $monthsyear[$j]['month'];
                        $monthName = date("F", mktime(0, 0, 0, $monthNum, 10));
                        if ($monthName == "January") {
                            $month_total .= "<td bgcolor='#CCCCCC'><b>" . $jan . "</b></td>";
                        }

                        if ($monthName == "February") {
                            $month_total .= "<td bgcolor='#CCCCCC'><b>" . $feb . "</b></td>";
                        }

                        if ($monthName == "March") {
                            $month_total .= "<td bgcolor='#CCCCCC'><b>" . $mar . "</b></td>";
                        }

                        if ($monthName == "April") {
                            $month_total .= "<td bgcolor='#CCCCCC'><b>" . $apr . "</b></td>";
                        }

                        if ($monthName == "May") {
                            $month_total .= "<td bgcolor='#CCCCCC'><b>" . $may . "</b></td>";
                        }

                        if ($monthName == "June") {
                            $month_total .= "<td bgcolor='#CCCCCC'><b>" . $jun . "</b></td>";
                        }

                        if ($monthName == "July") {
                            $month_total .= "<td bgcolor='#CCCCCC'><b>" . $july . "</b></td>";
                        }

                        if ($monthName == "August") {
                            $month_total .= "<td bgcolor='#CCCCCC'><b>" . $aug . "</b></td>";
                        }

                        if ($monthName == "September") {
                            $month_total .= "<td bgcolor='#CCCCCC'><b>" . $sep . "</b></td>";
                        }

                        if ($monthName == "October") {
                            $month_total .= "<td bgcolor='#CCCCCC'><b>" . $oct . "</b></td>";
                        }

                        if ($monthName == "November") {
                            $month_total .= "<td bgcolor='#CCCCCC'><b>" . $nov . "</b></td>";
                        }

                        if ($monthName == "December") {
                            $month_total .= "<td bgcolor='#CCCCCC'><b>" . $dec . "</b></td>";
                        }

                    }

                    $month_total .= "</tr>";

                    $table = $tableHeader . $month_total . $tr_data . "</table>";
                    //print($table);die;
                }
                header("Content-type: application/vnd.ms-excel");
                header("Content-Disposition: attachment; filename=countercompliant_report.xls");
                print $table;
                exit;

            } elseif ($exporttype == 0) {
// Export file in CSV format

                $year = substr($year, 2, 2);
                if ($reptype == "2") {
                    $colspan = "20";
                } elseif ($reptype == "1") {
                    $colspan = "21";
                }

                if ($gentype == "2" and $reptype == "3") {

                    $colspanccnt = 9;
                    $tableHeader_years='';
                    //print_r($years); die;
                    foreach ($years as $ayear) {
                        $tableHeader_years .= '"YOP ' . $ayear . '",';
                        $colspanccnt++;
                    }
                    $tableHeader = '"' . $report_type . '","' . $type . '"' . "\n";
                    $tableHeader .= '"' . $org_name . '"' . "\n";
                    $tableHeader .= '""' . "\n"; //$tableHeader .='"Institutional Identifier"'."\n";
                    $tableHeader .= '"Period covered by Report:"' . "\n";
                    $tableHeader .= '"' . $UDRBegin . ' to ' . $UDREnd . '"' . "\n";
                    $tableHeader .= '"Date run:"' . "\n";
                    $tableHeader .= '"' . date("Y-m-d") . '"' . "\n";

                    $tableHeader .= '"Journal","Publisher","Platform","Journal DOI","Proprietary Identifier","' . $issn . '","' . $eissn . '","Articles in Press",';
                    $tableHeader .= $tableHeader_years;
                    $tableHeader .= '"YOP Pre-2000",';
                    $tableHeader .= '"YOP unknown"' . "\n";

                    $tr_data         = "";
                    $total_sale_year = array();
                    $article_inpress = 0;
                    $YOPPrecount     = 0;
                    $YOPunknowncount = 0;

                    $jan     = 0;
                    $feb     = 0;
                    $mar     = 0;
                    $apr     = 0;
                    $may     = 0;
                    $jun     = 0;
                    $july    = 0;
                    $aug     = 0;
                    $sep     = 0;
                    $oct     = 0;
                    $nov     = 0;
                    $dec     = 0;
                    //while ($row = db_fetch_array($result)) {
                    foreach ($result as $row) {
                                                   
                        $row = (array) $row;

                        $tr_data .= '"' . $row['title'] . '",';

                        $article_inpress = $article_inpress + $row['article_in_press'];
                        $tr_data .= '"Bentham Science Publishers","Bentham Science","","","' . $row['issn'] . '","' . $row['eissn'] . '","' . $row['article_in_press'] . '",';

                        foreach ($years as $ayear) {
                            //if($row['YOP'.$ayear]!='0'){
                            if(!isset($total_sale_year['YOP' . $ayear])){
                                $total_sale_year['YOP' . $ayear] = 0;
							}
                            $count                           = $row['YOP ' . $ayear];
                            $total_sale_year['YOP' . $ayear] = $total_sale_year['YOP' . $ayear] + $count;
                            $tr_data .= '"' . $count . '",';
                            //}
                        }
                        $YOPPrecount     = $row['YOPPre_2000'] + $YOPPrecount;
                        $YOPunknowncount = $row['YOPunknown'] + $YOPunknowncount;

                        $tr_data .= '"' . $row['YOPPre_2000'] . '",';
                        $tr_data .= '"' . $row['YOPunknown'] . '"' . "\n";

                    } //while

                    $month_wise_total = $jan + $feb + $mar + $apr + $may + $jun + $july + $aug + $sep + $oct + $nov + $dec;
                    //print ($month_wise_total);exit;
                    $year_total = '"' . $title . '","","Bentham Science","","","","","' . $article_inpress . '",';

                    foreach ($total_sale_year as $yyear) {

                        $year_total .= '"' . $yyear . '",';

                    }
                    $year_total .= '"' . $YOPPrecount . '",';
                    $year_total .= '"' . $YOPunknowncount . '"' . "\n";

                    $table = $tableHeader . $year_total . $tr_data;
                    //print($table); die;

                } else {
                    $year = '20' . $year;

                    $tableHeader = '"' . $report_type . '","' . $type . '"' . "\n";
                    if ($reptype == 5) {
                        $tableHeader .= '"' . $org_name . '","Section Type:"' . "\n";
                        $tableHeader .= '"","Chapter"' . "\n"; //$tableHeader .='"Institutional Identifier","Chapter"'."\n";
                    } else {
                        $tableHeader .= '"' . $org_name . '"' . "\n";
                        $tableHeader .= '""' . "\n"; //$tableHeader .='"Institutional Identifier"'."\n";
                    }
                    $tableHeader .= '"Period covered by Report:"' . "\n";
                    $tableHeader .= '"' . $UDRBegin . ' to ' . $UDREnd . '"' . "\n";
                    $tableHeader .= '"Date run:"' . "\n";
                    $tableHeader .= '"' . date("Y-m-d") . '"' . "\n";
                    if ($reptype == 4 || $reptype == 5) {
                        $tableHeader .= '"","Publisher","Platform","Book DOI","Proprietary Identifier","' . $issn . '","' . $eissn . '",';
                    } else {
                        $tableHeader .= '"Journal","Publisher","Platform","Journal DOI","Proprietary Identifier","' . $issn . '","' . $eissn . '",';
                    }
                    if ($reptype == 2) {
                        $tableHeader .= '"Access Denied Category",';
                    }

                    $tableHeader .= '"Reporting Period Total",';

                    if ($reptype == 1) {
                        $tableHeader .= '"Reporting Period HTML","Reporting period PDF",';
                    }
                    for ($j = 0; $j < count($monthsyear); $j++) {
                        $monthNum  = $monthsyear[$j]['month'];
                        $monthName = date("M", mktime(0, 0, 0, $monthNum, 10));
                        $tableHeader .= '"' . $monthName . '-' . $year . '",';

                    }
                    //$tableHeader .=substr($tableHeader,0,strlen($tableHeader)-1);
                    $tableHeader .= "\n";

                    $tr_data = "";
                    $jan     = 0;
                    $feb     = 0;
                    $mar     = 0;
                    $apr     = 0;
                    $may     = 0;
                    $jun     = 0;
                    $july    = 0;
                    $aug     = 0;
                    $sep     = 0;
                    $oct     = 0;
                    $nov     = 0;
                    $dec     = 0;

                    //while ($row = db_fetch_object($result)) {

                    $html  = 0;
                    $pdf   = 0;
                    $other = 0;
                    foreach ($result as $row) {

                        $total_sale = round($row->January, 2) + round($row->February, 2) + round($row->March, 2) + round($row->April, 2) +
                        round($row->May, 2) + round($row->June, 2) + round($row->July, 2) + round($row->August, 2) +
                        round($row->September, 2) + round($row->October, 2) + round($row->November, 2) + round($row->December, 2);

                        $jan  = $jan + round($row->January, 2);
                        $feb  = $feb + round($row->February, 2);
                        $mar  = $mar + round($row->March, 2);
                        $apr  = $apr + round($row->April, 2);
                        $may  = $may + round($row->May, 2);
                        $jun  = $jun + round($row->June, 2);
                        $july = $july + round($row->July, 2);
                        $aug  = $aug + round($row->August, 2);
                        $sep  = $sep + round($row->September, 2);
                        $oct  = $oct + round($row->October, 2);
                        $nov  = $nov + round($row->November, 2);
                        $dec  = $dec + round($row->December, 2);

                        $html  = $html + $row->HTML;
                        $pdf   = $pdf + $row->PDF;
                        $other = $row->OTHER;

                        $index = 0;
                        //if there are multiple volume in eBook the show volume name
                        if ($index == 0) {

                            if ($this->CountMultipleVolumeExist($row->ebook_id, $row->volume_id) > 0) {
                                $volumeLabel = 'Volume: ' . $row->volume;
                            } else {
                                $volumeLabel = '';
                            }
                        } else {
                            $volumeLabel = '';
                        }

                        if ($gentype == "2") //if journal
                        {
                            $tr_data .= '"' . $row->title . '",';
                        } else {
                            $tr_data .= '"' . $row->title . ' ' . $volumeLabel . '",';
                        }

                        $tr_data .= '"Bentham Science Publishers ",';
                        $tr_data .= '"Bentham Science",';
                        if ($reptype == '4' || $reptype == '5') {
                            $tr_data .= '"' . $row->doi . '",';
                        } else {
                            $tr_data .= '"",';
                        }
                        $tr_data .= '"",';
                        if ($reptype == '4' || $reptype == '5') {
                            $tr_data .= '"' . trim($row->isbn) . '",';
                            $tr_data .= '"' . trim($row->issn) . '",';
                        } else {
                            $tr_data .= '"' . trim($row->issn) . '",';
                            $tr_data .= '"' . trim($row->eissn) . '",';

                        }

                        if ($reptype == "4" || $reptype == '5') {

                            $tr_data .= '"' . $total_sale . '",';

                        }

                        if ($reptype == "2") {

                            $tr_data .= '"Access denied: content item not licenced",';
                            $tr_data .= '"' . $total_sale . '",';

                        }

                        if ($reptype == "1") {
                            //for rows
                            $tr_data .= '"' . $total_sale . '",';
                            $tr_data .= '"' . $row->HTML . '",';
                            $tr_data .= '"' . $row->PDF . '",';
                        }
                        //print($total_sale);
                        for ($j = 0; $j < count($monthsyear); $j++) {
                            $monthNum  = $monthsyear[$j]['month'];
                            $monthName = date("F", mktime(0, 0, 0, $monthNum, 10));

                            //$tr_data .='"'.round($row->$monthName,2).'",';
                            if ($monthName == "January") {
                                $tr_data .= '"' . round($row->January, 2) . '",';
                            }

                            if ($monthName == "February") {
                                $tr_data .= '"' . round($row->February, 2) . '",';
                            }

                            if ($monthName == "March") {
                                $tr_data .= '"' . round($row->March, 2) . '",';
                            }

                            if ($monthName == "April") {
                                $tr_data .= '"' . round($row->April, 2) . '",';
                            }

                            if ($monthName == "May") {
                                $tr_data .= '"' . round($row->May, 2) . '",';
                            }

                            if ($monthName == "June") {
                                $tr_data .= '"' . round($row->June, 2) . '",';
                            }

                            if ($monthName == "July") {
                                $tr_data .= '"' . round($row->July, 2) . '",';
                            }

                            if ($monthName == "August") {
                                $tr_data .= '"' . round($row->August, 2) . '",';
                            }

                            if ($monthName == "September") {
                                $tr_data .= '"' . round($row->September, 2) . '",';
                            }

                            if ($monthName == "October") {
                                $tr_data .= '"' . round($row->October, 2) . '",';
                            }

                            if ($monthName == "November") {
                                $tr_data .= '"' . round($row->November, 2) . '",';
                            }

                            if ($monthName == "December") {
                                $tr_data .= '"' . round($row->December, 2) . '",';
                            }

                        }
                        //$tr_data .=substr($tr_data,0,strlen($tr_data)-1);
                        $tr_data .= "\n";
                        //print($tr_data);exit;

                    } //while

                    $month_wise_total = $jan + $feb + $mar + $apr + $may + $jun + $july + $aug + $sep + $oct + $nov + $dec;

                    $month_total = '"' . $title . '",';
                    if ($gentype == '2') {
                        $month_total .= '"Bentham Science Publishers",';
                    } else {
                        $month_total .= '"",';
                    }
                    if ($gentype != 0) {
                        $month_total .= '"Bentham Science",';
                    } else {
                        $month_total .= '"",';
                    }
                    $month_total .= '"",';
                    $month_total .= '"",';
                    $month_total .= '"",';
                    $month_total .= '"",';

                    if ($reptype == "2") {
                        $month_total .= '"Access denied: content item not licenced",';
                    }

                    $month_total .= '"' . $month_wise_total . '",';

                    if ($reptype == "1") {
                        //this
                        $month_total .= '"' . $html . '",';
                        $month_total .= '"' . $pdf . '",';

                    }
                    for ($j = 0; $j < count($monthsyear); $j++) {
                        $monthNum  = $monthsyear[$j]['month'];
                        $monthName = date("F", mktime(0, 0, 0, $monthNum, 10));
                        if ($monthName == "January") {
                            $month_total .= '"' . $jan . '",';
                        }

                        if ($monthName == "February") {
                            $month_total .= '"' . $feb . '",';
                        }

                        if ($monthName == "March") {
                            $month_total .= '"' . $mar . '",';
                        }

                        if ($monthName == "April") {
                            $month_total .= '"' . $apr . '",';
                        }

                        if ($monthName == "May") {
                            $month_total .= '"' . $may . '",';
                        }

                        if ($monthName == "June") {
                            $month_total .= '"' . $jun . '",';
                        }

                        if ($monthName == "July") {
                            $month_total .= '"' . $july . '",';
                        }

                        if ($monthName == "August") {
                            $month_total .= '"' . $aug . '",';
                        }

                        if ($monthName == "September") {
                            $month_total .= '"' . $sep . '",';
                        }

                        if ($monthName == "October") {
                            $month_total .= '"' . $oct . '",';
                        }

                        if ($monthName == "November") {
                            $month_total .= '"' . $nov . '",';
                        }

                        if ($monthName == "December") {
                            $month_total .= '"' . $dec . '",';
                        }

                    }
                    //$month_total .= substr($month_total,0,strlen($month_total)-1);
                    $month_total .= "\n";

                    $table = $tableHeader . $month_total . $tr_data;
                    //print($table);die;
                }
                header("Content-type: text/csv");
                header("Content-Disposition: attachment; filename=countercompliant_report.csv");
                print $table;
                exit;
            }

        }
    }

    public function detailreport_jr2(Request $request,$input)
    {

        $gentype       = $request->get("exportrange");
        $user_id       = $request->get("user_id");
        $reptype       = $request->get("reporttype");
        $frommonthwise = $request->get('frommonthwise');
        $fromyearwise  = $request->get('toyearwise');
        $tomonthwise   = $request->get('tomonthwise');
        $toyearwise    = $request->get('toyearwise');
        $exporttype    = $request->get('exporttype');
        $fromdate      = $toyearwise . '-' . $frommonthwise . '-01';
        $todate        = $toyearwise . '-' . $tomonthwise . '-28';

        $UDRBegin   = date('Y-m-01', strtotime($fromdate));
        $UDREnd     = date('Y-m-t', strtotime($todate));
        $monthsyear = $this->getMonthsInRange($UDRBegin, $UDREnd);
        for ($j = 0; $j < count($monthsyear); $j++) {
            $monthNum  = $monthsyear[$j]['month'];
            $year      = $monthsyear[0]['year'];
            $monthName = date("F", mktime(0, 0, 0, $monthNum, 10));
        }
        for ($j = 0; $j < count($monthsyear); $j++) {
            $monthNum  = $monthsyear[$j]['month'];
            $year      = $monthsyear[0]['year'];
            $monthName = date("F", mktime(0, 0, 0, $monthNum, 10));
        }

        $resultuser = User::getUserById($user_id);
        $org_name   = $resultuser->first_name . ' ' . $resultuser->last_name;

        if ($gentype == "2" && $reptype == "6") {

            $type        = "Access Denied to Full-text Articles by Month, Journal and Category";
            $report_type = "Journal Report 2 (R4)_detail";
            $title       = "Total for all articles";
            $issn        = "Print ISSN";
            $eissn       = "Online ISSN";

        }

        $query = "select a.aid,ar.title as article_title,i.title as issue,
        j.title as journal,v.volume_name as volume,a.jid,j.issn,j.eissn,ar.doi,
        a.catid as category,
        COUNT(CASE WHEN date_format(from_unixtime(created_on),'%M')='January' THEN aid END) AS 'January',
        COUNT(CASE WHEN date_format(from_unixtime(created_on),'%M')='February' THEN aid END) AS 'February',
        COUNT(CASE WHEN date_format(from_unixtime(created_on),'%M')='March' THEN aid END) AS 'March',
        COUNT(CASE WHEN date_format(from_unixtime(created_on),'%M')='April' THEN aid END) AS 'April',
        COUNT(CASE WHEN date_format(from_unixtime(created_on),'%M')='May' THEN aid END) AS 'May',
        COUNT(CASE WHEN date_format(from_unixtime(created_on),'%M')='June' THEN aid END) AS 'June',
        COUNT(CASE WHEN date_format(from_unixtime(created_on),'%M')='July' THEN aid END) AS 'July',
        COUNT(CASE WHEN date_format(from_unixtime(created_on),'%M')='August' THEN aid END) AS 'August',
        COUNT(CASE WHEN date_format(from_unixtime(created_on),'%M')='September' THEN aid END) AS 'September',
        COUNT(CASE WHEN date_format(from_unixtime(created_on),'%M')='October' THEN aid END) AS 'October',
        COUNT(CASE WHEN date_format(from_unixtime(created_on),'%M')='November' THEN aid END) AS 'November',
        COUNT(CASE WHEN date_format(from_unixtime(created_on),'%M')='December' THEN aid END) AS 'December'
        from access_denied_report a
        inner join journal j on a.jid = j.journal_id
        inner join article ar on a.aid=ar.article_id
         left join issue i on ar.issue_id=i.issue_id
        inner join volume v on ar.volume_id=v.volume_id
        and to_days(from_unixtime(created_on)) between to_days('" . $UDRBegin . "') and to_days('" . $UDREnd . "')
        and a.userid='" . $user_id . "'
        group by a.aid
        order by a.created_on desc";
        //print($query); exit;

        $result = DB::select($query);

        $colspan = "20";
        if ($exporttype == 1) {
            $tableHeader = "<table width='800' align='center' border='1'>
                        <tr>
                            <td><b>" . $report_type . "</b></td>
                            <td colspan='$colspan'><b>" . $type . "</b></td>

                        </tr>

                        <tr>
                            <td><b>" . $org_name . "</b></td>";

            $tableHeader .= "<td colspan='$colspan' align='left'>&nbsp;</td>";
            $tableHeader .= "</tr>
                        <tr>
                                <td><b></b></td>"; //Institutional Identifier
            $tableHeader .= "<td colspan='$colspan' align='left'>&nbsp;</td>";
            $tableHeader .= "</tr>
                        <tr>
                                <td><b>Period covered by Report:</b></td>
                                <td colspan='$colspan' align='left'>&nbsp;</td>
                            </tr>
                            <tr>
                                <td >" . $UDRBegin . " to " . $UDREnd . "</td>
                                <td colspan='$colspan' align='left'>&nbsp;</td>
                            </tr>
                        <tr>
                            <td align='left'><b>Date run:</b></td>
                            <td colspan='$colspan' align='left'>&nbsp;</td>
                        </tr>
                        <tr>
                            <td align='left'>'" . date("Y-m-d") . "</td>
                            <td colspan='$colspan' align='left'>&nbsp;</td>
                        </tr>

                        <tr align='center'>";
            $tableHeader .= "<td align='left' bgcolor='#CCCCCC'><b>Journal</b></td>";
            $tableHeader .= "<td bgcolor='#CCCCCC'><b>" . $issn . "</b></td>
                            <td bgcolor='#CCCCCC'><b>" . $eissn . "</b></td>";
            $tableHeader .= "<td bgcolor='#CCCCCC'><b>Publisher</b></td>
                            <td bgcolor='#CCCCCC'><b>Platform</b></td>";

            $tableHeader .= "<td bgcolor='#CCCCCC'><b>Article</b></td>";
            $tableHeader .= "<td bgcolor='#CCCCCC'><b>Volume</b></td>";
            $tableHeader .= "<td bgcolor='#CCCCCC'><b>Issue</b></td>";
            $tableHeader .= "<td bgcolor='#CCCCCC'><b>Article DOI</b></td>";
            $tableHeader .= "<td bgcolor='#CCCCCC'><b>Proprietary Identifier</b></td>";

            $tableHeader .= "<td bgcolor='#CCCCCC'><b>Access Denied Category Details</b></td>";
            $tableHeader .= " <td bgcolor='#CCCCCC'><b>Reporting Period Total</b></td>";

            for ($j = 0; $j < count($monthsyear); $j++) {
                $monthNum  = $monthsyear[$j]['month'];
                $monthName = date("M", mktime(0, 0, 0, $monthNum, 10));
                $tableHeader .= "<td bgcolor='#CCCCCC'><b>'$monthName" . $year . "</b></td>";
            }

            $tr_data = "";
            $jan     = 0;
            $feb     = 0;
            $mar     = 0;
            $apr     = 0;
            $may     = 0;
            $jun     = 0;
            $july    = 0;
            $aug     = 0;
            $sep     = 0;
            $oct     = 0;
            $nov     = 0;
            $dec     = 0;

            //while ($row = db_fetch_object($result)) {
            foreach ($result as $row) {
                $total_sale = round($row->January, 2) + round($row->February, 2) + round($row->March, 2) + round($row->April, 2) +
                round($row->May, 2) + round($row->June, 2) + round($row->July, 2) + round($row->August, 2) +
                round($row->September, 2) + round($row->October, 2) + round($row->November, 2) + round($row->December, 2);

                $jan  = $jan + round($row->January, 2);
                $feb  = $feb + round($row->February, 2);
                $mar  = $mar + round($row->March, 2);
                $apr  = $apr + round($row->April, 2);
                $may  = $may + round($row->May, 2);
                $jun  = $jun + round($row->June, 2);
                $july = $july + round($row->July, 2);
                $aug  = $aug + round($row->August, 2);
                $sep  = $sep + round($row->September, 2);
                $oct  = $oct + round($row->October, 2);
                $nov  = $nov + round($row->November, 2);
                $dec  = $dec + round($row->December, 2);

                $tr_data .= "<tr align='center'> ";

                $tr_data .= "<td align='left'>" . $row->journal . "</td>";
                $tr_data .= "<td>" . $row->issn . "</td>
                            <td>" . $row->eissn . "</td>";
                $tr_data .= "<td>Bentham Science Publishers</td>
                                <td>Bentham Science</td>
                                <td>" . $row->article_title . "</td>
                                <td>" . $row->volume . "</td>
                                <td>" . $row->issue . "</td>
                                <td>" . $row->doi . "</td>
                                <td>&nbsp;</td>
                                <td>Access denied: content item not licenced</td>
                                <td>" . $total_sale . "</td>
                           ";

                for ($j = 0; $j < count($monthsyear); $j++) {
                    $monthNum  = $monthsyear[$j]['month'];
                    $monthName = date("F", mktime(0, 0, 0, $monthNum, 10));
                    //$tr_data .="<td>".round($row->$monthName,2)."</td>";
                    if ($monthName == "January") {
                        $tr_data .= "<td>" . round($row->January, 2) . "</td>";
                    }

                    if ($monthName == "February") {
                        $tr_data .= "<td>" . round($row->February, 2) . "</td>";
                    }

                    if ($monthName == "March") {
                        $tr_data .= "<td>" . round($row->March, 2) . "</td>";
                    }

                    if ($monthName == "April") {
                        $tr_data .= "<td>" . round($row->April, 2) . "</td>";
                    }

                    if ($monthName == "May") {
                        $tr_data .= "<td>" . round($row->May, 2) . "</td>";
                    }

                    if ($monthName == "June") {
                        $tr_data .= "<td>" . round($row->June, 2) . "</td>";
                    }

                    if ($monthName == "July") {
                        $tr_data .= "<td>" . round($row->July, 2) . "</td>";
                    }

                    if ($monthName == "August") {
                        $tr_data .= "<td>" . round($row->August, 2) . "</td>";
                    }

                    if ($monthName == "September") {
                        $tr_data .= "<td>" . round($row->September, 2) . "</td>";
                    }

                    if ($monthName == "October") {
                        $tr_data .= "<td>" . round($row->October, 2) . "</td>";
                    }

                    if ($monthName == "November") {
                        $tr_data .= "<td>" . round($row->November, 2) . "</td>";
                    }

                    if ($monthName == "December") {
                        $tr_data .= "<td>" . round($row->December, 2) . "</td>";
                    }

                }

                $tr_data .= "</tr>";

            } //while

            $month_wise_total = $jan + $feb + $mar + $apr + $may + $jun + $july + $aug + $sep + $oct + $nov + $dec;

            $month_total = "<tr align='center'>
                            <td align='left' bgcolor='#CCCCCC'><b>" . $title . "</b></td>";
            //not getting the gentype
            if ($gentype == '2') {
                $month_total .= "<td bgcolor='#CCCCCC'></td>";
            } else {
                $month_total .= "<td bgcolor='#CCCCCC'></td>";
            }
            if ($gentype != 0) {
                $month_total .= "<td bgcolor='#CCCCCC'></td>";
            } else {
                $month_total .= "<td bgcolor='#CCCCCC'></td>";
            }
            $month_total .= "<td bgcolor='#CCCCCC'>&nbsp;</td>
                            <td bgcolor='#CCCCCC'>&nbsp;</td>
                            <td bgcolor='#CCCCCC'>&nbsp;</td>
                            <td bgcolor='#CCCCCC'>&nbsp;</td>
                            <td bgcolor='#CCCCCC'>&nbsp;</td>
                            <td bgcolor='#CCCCCC'>&nbsp;</td>
                            <td bgcolor='#CCCCCC'>&nbsp;</td>
                            <td bgcolor='#CCCCCC'>&nbsp;</td>

                            "
            ;

            // $month_total .=   "<td bgcolor='#CCCCCC'><b>Access denied: content item not licenced<b></td>";
            $month_total .= " <td bgcolor='#CCCCCC'><b>" . $month_wise_total . "</b></td>";

            for ($j = 0; $j < count($monthsyear); $j++) {
                $monthNum  = $monthsyear[$j]['month'];
                $monthName = date("F", mktime(0, 0, 0, $monthNum, 10));
                if ($monthName == "January") {
                    $month_total .= "<td bgcolor='#CCCCCC'><b>" . $jan . "</b></td>";
                }

                if ($monthName == "February") {
                    $month_total .= "<td bgcolor='#CCCCCC'><b>" . $feb . "</b></td>";
                }

                if ($monthName == "March") {
                    $month_total .= "<td bgcolor='#CCCCCC'><b>" . $mar . "</b></td>";
                }

                if ($monthName == "April") {
                    $month_total .= "<td bgcolor='#CCCCCC'><b>" . $apr . "</b></td>";
                }

                if ($monthName == "May") {
                    $month_total .= "<td bgcolor='#CCCCCC'><b>" . $may . "</b></td>";
                }

                if ($monthName == "June") {
                    $month_total .= "<td bgcolor='#CCCCCC'><b>" . $jun . "</b></td>";
                }

                if ($monthName == "July") {
                    $month_total .= "<td bgcolor='#CCCCCC'><b>" . $july . "</b></td>";
                }

                if ($monthName == "August") {
                    $month_total .= "<td bgcolor='#CCCCCC'><b>" . $aug . "</b></td>";
                }

                if ($monthName == "September") {
                    $month_total .= "<td bgcolor='#CCCCCC'><b>" . $sep . "</b></td>";
                }

                if ($monthName == "October") {
                    $month_total .= "<td bgcolor='#CCCCCC'><b>" . $oct . "</b></td>";
                }

                if ($monthName == "November") {
                    $month_total .= "<td bgcolor='#CCCCCC'><b>" . $nov . "</b></td>";
                }

                if ($monthName == "December") {
                    $month_total .= "<td bgcolor='#CCCCCC'><b>" . $dec . "</b></td>";
                }

            }

            $month_total .= "</tr>";

            $table = $tableHeader . $month_total . $tr_data . "</table>";
            // print($table);die;

            header("Content-type: application/vnd.ms-excel");
            header("Content-Disposition: attachment; filename=countercompliant_report.xls");
            print $table;
            exit;

        } else if ($exporttype == 0) {
// Export file in CSV format

            //$year = substr($year,0,4);
            $year = $year;
            //echo($year);
            $tableHeader = '"' . $report_type . '","' . $type . '"' . "\n";
            $tableHeader .= '"' . $org_name . '"' . "\n";
            $tableHeader .= '""' . "\n"; //$tableHeader .='"Institutional Identifier"'."\n";
            $tableHeader .= '"Period covered by Report:"' . "\n";
            $tableHeader .= '"' . $UDRBegin . ' to ' . $UDREnd . '"' . "\n";
            $tableHeader .= '"Date run:"' . "\n";
            $tableHeader .= '"' . date("Y-m-d") . '"' . "\n";
            $tableHeader .= '"Journal","' . $issn . '","' . $eissn . '","Publisher","Platform","Article","Volume","Issue","Article DOI","Proprietary Identifier",';
            $tableHeader .= '"Access Denied Category",';
            $tableHeader .= '"Reporting Period Total",';
            for ($j = 0; $j < count($monthsyear); $j++) {
                $monthNum  = $monthsyear[$j]['month'];
                $monthName = date("M", mktime(0, 0, 0, $monthNum, 10));
                $tableHeader .= '"' . $monthName . '-' . $year . '",';

            }
            //$tableHeader .=substr($tableHeader,0,strlen($tableHeader)-1);
            $tableHeader .= "\n";

            $tr_data = "";
            $jan     = 0;
            $feb     = 0;
            $mar     = 0;
            $apr     = 0;
            $may     = 0;
            $jun     = 0;
            $july    = 0;
            $aug     = 0;
            $sep     = 0;
            $oct     = 0;
            $nov     = 0;
            $dec     = 0;

            //while ($row = db_fetch_object($result)) {
            foreach ($result as $row) {
                $total_sale = round($row->January, 2) + round($row->February, 2) + round($row->March, 2) + round($row->April, 2) +
                round($row->May, 2) + round($row->June, 2) + round($row->July, 2) + round($row->August, 2) +
                round($row->September, 2) + round($row->October, 2) + round($row->November, 2) + round($row->December, 2);

                $jan  = $jan + round($row->January, 2);
                $feb  = $feb + round($row->February, 2);
                $mar  = $mar + round($row->March, 2);
                $apr  = $apr + round($row->April, 2);
                $may  = $may + round($row->May, 2);
                $jun  = $jun + round($row->June, 2);
                $july = $july + round($row->July, 2);
                $aug  = $aug + round($row->August, 2);
                $sep  = $sep + round($row->September, 2);
                $oct  = $oct + round($row->October, 2);
                $nov  = $nov + round($row->November, 2);
                $dec  = $dec + round($row->December, 2);

                $tr_data .= '"' . $row->journal . '",';
                $tr_data .= '"' . $row->issn . '",';
                $tr_data .= '"' . $row->eissn . '",';
                $tr_data .= '"Bentham Science Publishers",';
                $tr_data .= '"Bentham Science",';
                $tr_data .= '"' . $row->article_title . '",';
                $tr_data .= '"' . $row->volume . '",';
                $tr_data .= '"' . $row->issue . '",';
                $tr_data .= '"' . $row->doi . '",';
                $tr_data .= '"",';

                $tr_data .= '"Access denied: content item not licenced",';
                $tr_data .= '"' . $total_sale . '",';

                for ($j = 0; $j < count($monthsyear); $j++) {
                    $monthNum  = $monthsyear[$j]['month'];
                    $monthName = date("F", mktime(0, 0, 0, $monthNum, 10));
                    //$tr_data .='"'.round($row->$monthName,2).'",';
                    if ($monthName == "January") {
                        $tr_data .= '"' . round($row->January, 2) . '",';
                    }

                    if ($monthName == "February") {
                        $tr_data .= '"' . round($row->February, 2) . '",';
                    }

                    if ($monthName == "March") {
                        $tr_data .= '"' . round($row->March, 2) . '",';
                    }

                    if ($monthName == "April") {
                        $tr_data .= '"' . round($row->April, 2) . '",';
                    }

                    if ($monthName == "May") {
                        $tr_data .= '"' . round($row->May, 2) . '",';
                    }

                    if ($monthName == "June") {
                        $tr_data .= '"' . round($row->June, 2) . '",';
                    }

                    if ($monthName == "July") {
                        $tr_data .= '"' . round($row->July, 2) . '",';
                    }

                    if ($monthName == "August") {
                        $tr_data .= '"' . round($row->August, 2) . '",';
                    }

                    if ($monthName == "September") {
                        $tr_data .= '"' . round($row->September, 2) . '",';
                    }

                    if ($monthName == "October") {
                        $tr_data .= '"' . round($row->October, 2) . '",';
                    }

                    if ($monthName == "November") {
                        $tr_data .= '"' . round($row->November, 2) . '",';
                    }

                    if ($monthName == "December") {
                        $tr_data .= '"' . round($row->December, 2) . '",';
                    }

                }
                //$tr_data .=substr($tr_data,0,strlen($tr_data)-1);
                $tr_data .= "\n";

            } //while

            $month_wise_total = $jan + $feb + $mar + $apr + $may + $jun + $july + $aug + $sep + $oct + $nov + $dec;

            $month_total = '"' . $title . '",';
            if ($gentype == '2') {
                $month_total .= '"",';
            } else {
                $month_total .= '"",';
            }
            if ($gentype != 0) {
                $month_total .= '"",';
            } else {
                $month_total .= '"",';
            }
            $month_total .= '"",';
            $month_total .= '"",';
            $month_total .= '"",';
            $month_total .= '"",';
            $month_total .= '"",';
            $month_total .= '"",';
            $month_total .= '"",';
            $month_total .= '"",';
            //$month_total .=   '"Access denied: content item not licenced",';
            $month_total .= '"' . $month_wise_total . '",';

            for ($j = 0; $j < count($monthsyear); $j++) {
                $monthNum  = $monthsyear[$j]['month'];
                $monthName = date("F", mktime(0, 0, 0, $monthNum, 10));
                if ($monthName == "January") {
                    $month_total .= '"' . $jan . '",';
                }

                if ($monthName == "February") {
                    $month_total .= '"' . $feb . '",';
                }

                if ($monthName == "March") {
                    $month_total .= '"' . $mar . '",';
                }

                if ($monthName == "April") {
                    $month_total .= '"' . $apr . '",';
                }

                if ($monthName == "May") {
                    $month_total .= '"' . $may . '",';
                }

                if ($monthName == "June") {
                    $month_total .= '"' . $jun . '",';
                }

                if ($monthName == "July") {
                    $month_total .= '"' . $july . '",';
                }

                if ($monthName == "August") {
                    $month_total .= '"' . $aug . '",';
                }

                if ($monthName == "September") {
                    $month_total .= '"' . $sep . '",';
                }

                if ($monthName == "October") {
                    $month_total .= '"' . $oct . '",';
                }

                if ($monthName == "November") {
                    $month_total .= '"' . $nov . '",';
                }

                if ($monthName == "December") {
                    $month_total .= '"' . $dec . '",';
                }

            }
            //$month_total .= substr($month_total,0,strlen($month_total)-1);
            $month_total .= "\n";

            $table = $tableHeader . $month_total . $tr_data;

            //  print($table);die;

            header("Content-type: text/csv");
            header("Content-Disposition: attachment; filename=countercompliant_report.csv");
            print $table;
            exit;
        }
    }

    //7-12-2016 JU Further details of JR2_detail report
    public function detail_detailreport_jr2(Request $request,$input)
    {

        $gentype       = $request->get("exportrange");
        $user_id       = $request->get("user_id");
        $reptype       = $request->get("reporttype");
        $frommonthwise = $request->get('frommonthwise');
        $fromyearwise  = $request->get('toyearwise');
        $tomonthwise   = $request->get('tomonthwise');
        $toyearwise    = $request->get('toyearwise');
        $exporttype    = $request->get('exporttype');
        $fromdate      = $toyearwise . '-' . $frommonthwise . '-01';
        $todate        = $toyearwise . '-' . $tomonthwise . '-28';

        $UDRBegin   = date('Y-m-01', strtotime($fromdate));
        $UDREnd     = date('Y-m-t', strtotime($todate));
        $monthsyear = $this->getMonthsInRange($UDRBegin, $UDREnd);
        for ($j = 0; $j < count($monthsyear); $j++) {
            $monthNum  = $monthsyear[$j]['month'];
            $year      = $monthsyear[0]['year'];
            $monthName = date("F", mktime(0, 0, 0, $monthNum, 10));
        }
        for ($j = 0; $j < count($monthsyear); $j++) {
            $monthNum  = $monthsyear[$j]['month'];
            $year      = $monthsyear[0]['year'];
            $monthName = date("F", mktime(0, 0, 0, $monthNum, 10));
        }

        $resultuser = User::getUserById($user_id);
        $org_name   = $resultuser->first_name . ' ' . $resultuser->last_name;

        if ($gentype == "2") {

            $type        = "Access Denied to Full-text Articles by Month, Journal and Category";
            $report_type = "Journal Report 2 (R4)_detail";
            $title       = "Total for all articles";
            $issn        = "Print ISSN";
            $eissn       = "Online ISSN";

        }

        $query = "select a.aid,ar.title as article_title,i.title as issue,
         j.title as journal,v.volume_name as volume,y.year as volume_year, a.jid,j.issn,j.eissn,ar.doi,
         a.ip_address,from_unixtime(a.created_on)as hitting_time,
        (CASE WHEN date_format(from_unixtime(created_on),'%M')='January' THEN '1' END) AS 'January',
        (CASE WHEN date_format(from_unixtime(created_on),'%M')='February' THEN '1' END) AS 'February',
        (CASE WHEN date_format(from_unixtime(created_on),'%M')='March' THEN '1' END) AS 'March',
        (CASE WHEN date_format(from_unixtime(created_on),'%M')='April' THEN '1' END) AS 'April',
        (CASE WHEN date_format(from_unixtime(created_on),'%M')='May' THEN '1' END) AS 'May',
        (CASE WHEN date_format(from_unixtime(created_on),'%M')='June' THEN '1' END) AS 'June',
        (CASE WHEN date_format(from_unixtime(created_on),'%M')='July' THEN '1' END) AS 'July',
        (CASE WHEN date_format(from_unixtime(created_on),'%M')='August' THEN '1' END) AS 'August',
        (CASE WHEN date_format(from_unixtime(created_on),'%M')='September' THEN '1' END) AS 'September',
        (CASE WHEN date_format(from_unixtime(created_on),'%M')='October' THEN '1' END) AS 'October',
        (CASE WHEN date_format(from_unixtime(created_on),'%M')='November' THEN '1' END) AS 'November',
        (CASE WHEN date_format(from_unixtime(created_on),'%M')='December' THEN '1' END) AS 'December'
        from access_denied_report a
        inner join journal j on a.jid = j.journal_id
        inner join article ar on a.aid=ar.article_id
        left join issue i on ar.issue_id=i.issue_id
        inner join volume v on ar.volume_id=v.volume_id
        inner join year y on y.id=v.year_id
        and to_days(from_unixtime(created_on)) between to_days('" . $UDRBegin . "') and to_days('" . $UDREnd . "')
        and a.userid='" . $user_id . "'
        order by created_on desc";
        //print($query); exit;
        $result = DB::select($query);

        $colspan = "20";
        if ($exporttype == 1) {
            $tableHeader = "<table width='800' align='center' border='1'>
                        <tr>
                            <td><b>" . $report_type . "</b></td>
                            <td colspan='$colspan'><b>" . $type . "</b></td>

                        </tr>

                        <tr>
                            <td><b>" . $org_name . "</b></td>";

            $tableHeader .= "<td colspan='$colspan' align='left'>&nbsp;</td>";
            $tableHeader .= "</tr>
                        <tr>
                                <td><b></b></td>"; //Institutional Identifier
            $tableHeader .= "<td colspan='$colspan' align='left'>&nbsp;</td>";
            $tableHeader .= "</tr>
                        <tr>
                                <td><b>Period covered by Report:</b></td>
                                <td colspan='$colspan' align='left'>&nbsp;</td>
                            </tr>
                            <tr>
                                <td >" . $UDRBegin . " to " . $UDREnd . "</td>
                                <td colspan='$colspan' align='left'>&nbsp;</td>
                            </tr>
                        <tr>
                            <td align='left'><b>Date run:</b></td>
                            <td colspan='$colspan' align='left'>&nbsp;</td>
                        </tr>
                        <tr>
                            <td align='left'>'" . date("Y-m-d") . "</td>
                            <td colspan='$colspan' align='left'>&nbsp;</td>
                        </tr>

                        <tr align='center'>";
            $tableHeader .= "<td align='left' bgcolor='#CCCCCC'><b>Journal</b></td>";
            $tableHeader .= "<td bgcolor='#CCCCCC'><b>" . $issn . "</b></td>
                            <td bgcolor='#CCCCCC'><b>" . $eissn . "</b></td>";
            $tableHeader .= "<td bgcolor='#CCCCCC'><b>Publisher</b></td>
                            <td bgcolor='#CCCCCC'><b>Platform</b></td>";

            $tableHeader .= "<td bgcolor='#CCCCCC'><b>Article</b></td>";
            $tableHeader .= "<td bgcolor='#CCCCCC'><b>Volume</b></td>";
            $tableHeader .= "<td bgcolor='#CCCCCC'><b>Volume Year</b></td>";
            $tableHeader .= "<td bgcolor='#CCCCCC'><b>Issue</b></td>";
            $tableHeader .= "<td bgcolor='#CCCCCC'><b>Article DOI</b></td>";
            $tableHeader .= "<td bgcolor='#CCCCCC'><b>Proprietary Identifier</b></td>";

            $tableHeader .= "<td bgcolor='#CCCCCC'><b>IP Address</b></td>";
            $tableHeader .= "<td bgcolor='#CCCCCC'><b>Date</b></td>";
            $tableHeader .= "<td bgcolor='#CCCCCC'><b>Access Denied Category Details</b></td>";
            $tableHeader .= " <td bgcolor='#CCCCCC'><b>Reporting Period Total</b></td>";

            for ($j = 0; $j < count($monthsyear); $j++) {
                $monthNum  = $monthsyear[$j]['month'];
                $monthName = date("M", mktime(0, 0, 0, $monthNum, 10));
                $tableHeader .= "<td bgcolor='#CCCCCC'><b>'$monthName" . $year . "</b></td>";
            }

            $tr_data = "";
            $jan     = 0;
            $feb     = 0;
            $mar     = 0;
            $apr     = 0;
            $may     = 0;
            $jun     = 0;
            $july    = 0;
            $aug     = 0;
            $sep     = 0;
            $oct     = 0;
            $nov     = 0;
            $dec     = 0;

            //while ($row = db_fetch_object($result)) {
            foreach ($result as $row) {
                $total_sale = round($row->January, 2) + round($row->February, 2) + round($row->March, 2) + round($row->April, 2) +
                round($row->May, 2) + round($row->June, 2) + round($row->July, 2) + round($row->August, 2) +
                round($row->September, 2) + round($row->October, 2) + round($row->November, 2) + round($row->December, 2);

                $jan  = $jan + round($row->January, 2);
                $feb  = $feb + round($row->February, 2);
                $mar  = $mar + round($row->March, 2);
                $apr  = $apr + round($row->April, 2);
                $may  = $may + round($row->May, 2);
                $jun  = $jun + round($row->June, 2);
                $july = $july + round($row->July, 2);
                $aug  = $aug + round($row->August, 2);
                $sep  = $sep + round($row->September, 2);
                $oct  = $oct + round($row->October, 2);
                $nov  = $nov + round($row->November, 2);
                $dec  = $dec + round($row->December, 2);

                $tr_data .= "<tr align='center'> ";

                $tr_data .= "<td align='left'>" . $row->journal . "</td>";
                $tr_data .= "<td>" . $row->issn . "</td>
                            <td>" . $row->eissn . "</td>";
                $tr_data .= "<td>Bentham Science Publishers</td>
                                <td>Bentham Science</td>
                                <td>" . $row->article_title . "</td>
                                <td>" . $row->volume . "</td>
                                <td>" . $row->volume_year . "</td>
                                <td>" . $row->issue . "</td>
                                <td>" . $row->doi . "</td>
                                <td>&nbsp;</td>

                                <td>" . $row->ip_address . "</td>
                                <td>" . $row->hitting_time . "</td>
                                <td>Access denied: content item not licenced</td>
                                <td>" . $total_sale . "</td>
                           ";

                for ($j = 0; $j < count($monthsyear); $j++) {
                    $monthNum  = $monthsyear[$j]['month'];
                    $monthName = date("F", mktime(0, 0, 0, $monthNum, 10));
                    //$tr_data .="<td>".round($row->$monthName,2)."</td>";
                    if ($monthName == "January") {
                        $tr_data .= "<td>" . round($row->January, 2) . "</td>";
                    }

                    if ($monthName == "February") {
                        $tr_data .= "<td>" . round($row->February, 2) . "</td>";
                    }

                    if ($monthName == "March") {
                        $tr_data .= "<td>" . round($row->March, 2) . "</td>";
                    }

                    if ($monthName == "April") {
                        $tr_data .= "<td>" . round($row->April, 2) . "</td>";
                    }

                    if ($monthName == "May") {
                        $tr_data .= "<td>" . round($row->May, 2) . "</td>";
                    }

                    if ($monthName == "June") {
                        $tr_data .= "<td>" . round($row->June, 2) . "</td>";
                    }

                    if ($monthName == "July") {
                        $tr_data .= "<td>" . round($row->July, 2) . "</td>";
                    }

                    if ($monthName == "August") {
                        $tr_data .= "<td>" . round($row->August, 2) . "</td>";
                    }

                    if ($monthName == "September") {
                        $tr_data .= "<td>" . round($row->September, 2) . "</td>";
                    }

                    if ($monthName == "October") {
                        $tr_data .= "<td>" . round($row->October, 2) . "</td>";
                    }

                    if ($monthName == "November") {
                        $tr_data .= "<td>" . round($row->November, 2) . "</td>";
                    }

                    if ($monthName == "December") {
                        $tr_data .= "<td>" . round($row->December, 2) . "</td>";
                    }

                }

                $tr_data .= "</tr>";

            } //while

            $month_wise_total = $jan + $feb + $mar + $apr + $may + $jun + $july + $aug + $sep + $oct + $nov + $dec;

            $month_total = "<tr align='center'>
                            <td align='left' bgcolor='#CCCCCC'><b>" . $title . "</b></td>";
            //not getting the gentype
            if ($gentype == '2') {
                $month_total .= "";
            } else {
                $month_total .= "";
            }
            if ($gentype != 0) {
                $month_total .= "<td bgcolor='#CCCCCC'></td>";
            } else {
                $month_total .= "<td bgcolor='#CCCCCC'></td>";
            }
            $month_total .= "<td bgcolor='#CCCCCC'>&nbsp;</td>
                            <td bgcolor='#CCCCCC'>&nbsp;</td>
                            <td bgcolor='#CCCCCC'>&nbsp;</td>
                            <td bgcolor='#CCCCCC'>&nbsp;</td>
                            <td bgcolor='#CCCCCC'>&nbsp;</td>
                            <td bgcolor='#CCCCCC'>&nbsp;</td>
                            <td bgcolor='#CCCCCC'>&nbsp;</td>
                            <td bgcolor='#CCCCCC'>&nbsp;</td>
                            <td bgcolor='#CCCCCC'>&nbsp;</td>
                            <td bgcolor='#CCCCCC'>&nbsp;</td>
                            <td bgcolor='#CCCCCC'>&nbsp;</td>
							<td bgcolor='#CCCCCC'>&nbsp;</td>"
            ;

            // $month_total .=   "<td bgcolor='#CCCCCC'><b>Access denied: content item not licenced<b></td>";
            $month_total .= " <td bgcolor='#CCCCCC'><b>" . $month_wise_total . "</b></td>";

            for ($j = 0; $j < count($monthsyear); $j++) {
                $monthNum  = $monthsyear[$j]['month'];
                $monthName = date("F", mktime(0, 0, 0, $monthNum, 10));
                if ($monthName == "January") {
                    $month_total .= "<td bgcolor='#CCCCCC'><b>" . $jan . "</b></td>";
                }

                if ($monthName == "February") {
                    $month_total .= "<td bgcolor='#CCCCCC'><b>" . $feb . "</b></td>";
                }

                if ($monthName == "March") {
                    $month_total .= "<td bgcolor='#CCCCCC'><b>" . $mar . "</b></td>";
                }

                if ($monthName == "April") {
                    $month_total .= "<td bgcolor='#CCCCCC'><b>" . $apr . "</b></td>";
                }

                if ($monthName == "May") {
                    $month_total .= "<td bgcolor='#CCCCCC'><b>" . $may . "</b></td>";
                }

                if ($monthName == "June") {
                    $month_total .= "<td bgcolor='#CCCCCC'><b>" . $jun . "</b></td>";
                }

                if ($monthName == "July") {
                    $month_total .= "<td bgcolor='#CCCCCC'><b>" . $july . "</b></td>";
                }

                if ($monthName == "August") {
                    $month_total .= "<td bgcolor='#CCCCCC'><b>" . $aug . "</b></td>";
                }

                if ($monthName == "September") {
                    $month_total .= "<td bgcolor='#CCCCCC'><b>" . $sep . "</b></td>";
                }

                if ($monthName == "October") {
                    $month_total .= "<td bgcolor='#CCCCCC'><b>" . $oct . "</b></td>";
                }

                if ($monthName == "November") {
                    $month_total .= "<td bgcolor='#CCCCCC'><b>" . $nov . "</b></td>";
                }

                if ($monthName == "December") {
                    $month_total .= "<td bgcolor='#CCCCCC'><b>" . $dec . "</b></td>";
                }

            }

            $month_total .= "</tr>";

            $table = $tableHeader . $month_total . $tr_data . "</table>";
            // print($table);die;

            header("Content-type: application/vnd.ms-excel");
            header("Content-Disposition: attachment; filename=countercompliant_report.xls");
            print $table;
            exit;

        } else if ($exporttype == 0) {
// Export file in CSV format

            //$year = substr($year,0,4);
            $year = $year;
            //echo($year);
            $tableHeader = '"' . $report_type . '","' . $type . '"' . "\n";
            $tableHeader .= '"' . $org_name . '"' . "\n";
            $tableHeader .= '""' . "\n"; //$tableHeader .='"Institutional Identifier"'."\n";
            $tableHeader .= '"Period covered by Report:"' . "\n";
            $tableHeader .= '"' . $UDRBegin . ' to ' . $UDREnd . '"' . "\n";
            $tableHeader .= '"Date run:"' . "\n";
            $tableHeader .= '"' . date("Y-m-d") . '"' . "\n";
            $tableHeader .= '"Journal","' . $issn . '","' . $eissn . '","Publisher","Platform","Article","Volume","Issue","Article DOI","Proprietary Identifier","IP Address","Date",';
            $tableHeader .= '"Access Denied Category",';
            $tableHeader .= '"Reporting Period Total",';
            for ($j = 0; $j < count($monthsyear); $j++) {
                $monthNum  = $monthsyear[$j]['month'];
                $monthName = date("M", mktime(0, 0, 0, $monthNum, 10));
                $tableHeader .= '"' . $monthName . '-' . $year . '",';

            }
            //$tableHeader .=substr($tableHeader,0,strlen($tableHeader)-1);
            $tableHeader .= "\n";

            $tr_data = "";
            $jan     = 0;
            $feb     = 0;
            $mar     = 0;
            $apr     = 0;
            $may     = 0;
            $jun     = 0;
            $july    = 0;
            $aug     = 0;
            $sep     = 0;
            $oct     = 0;
            $nov     = 0;
            $dec     = 0;

            //while ($row = db_fetch_object($result)) {
            foreach ($result as $row) {
                $total_sale = round($row->January, 2) + round($row->February, 2) + round($row->March, 2) + round($row->April, 2) +
                round($row->May, 2) + round($row->June, 2) + round($row->July, 2) + round($row->August, 2) +
                round($row->September, 2) + round($row->October, 2) + round($row->November, 2) + round($row->December, 2);

                $jan  = $jan + round($row->January, 2);
                $feb  = $feb + round($row->February, 2);
                $mar  = $mar + round($row->March, 2);
                $apr  = $apr + round($row->April, 2);
                $may  = $may + round($row->May, 2);
                $jun  = $jun + round($row->June, 2);
                $july = $july + round($row->July, 2);
                $aug  = $aug + round($row->August, 2);
                $sep  = $sep + round($row->September, 2);
                $oct  = $oct + round($row->October, 2);
                $nov  = $nov + round($row->November, 2);
                $dec  = $dec + round($row->December, 2);

                $tr_data .= '"' . $row->journal . '",';
                $tr_data .= '"' . $row->issn . '",';
                $tr_data .= '"' . $row->eissn . '",';
                $tr_data .= '"Bentham Science Publishers",';
                $tr_data .= '"Bentham Science",';
                $tr_data .= '"' . $row->article_title . '",';
                $tr_data .= '"' . $row->volume . '",';
                $tr_data .= '"' . $row->issue . '",';
                $tr_data .= '"' . $row->doi . '",';
                $tr_data .= '"",';

                $tr_data .= '"' . $row->ip_address . '",';
                $tr_data .= '"' . $row->hitting_time . '",';
                $tr_data .= '"Access denied: content item not licenced",';
                $tr_data .= '"' . $total_sale . '",';

                for ($j = 0; $j < count($monthsyear); $j++) {
                    $monthNum  = $monthsyear[$j]['month'];
                    $monthName = date("F", mktime(0, 0, 0, $monthNum, 10));
                    //$tr_data .='"'.round($row->$monthName,2).'",';
                    if ($monthName == "January") {
                        $tr_data .= '"' . round($row->January, 2) . '",';
                    }

                    if ($monthName == "February") {
                        $tr_data .= '"' . round($row->February, 2) . '",';
                    }

                    if ($monthName == "March") {
                        $tr_data .= '"' . round($row->March, 2) . '",';
                    }

                    if ($monthName == "April") {
                        $tr_data .= '"' . round($row->April, 2) . '",';
                    }

                    if ($monthName == "May") {
                        $tr_data .= '"' . round($row->May, 2) . '",';
                    }

                    if ($monthName == "June") {
                        $tr_data .= '"' . round($row->June, 2) . '",';
                    }

                    if ($monthName == "July") {
                        $tr_data .= '"' . round($row->July, 2) . '",';
                    }

                    if ($monthName == "August") {
                        $tr_data .= '"' . round($row->August, 2) . '",';
                    }

                    if ($monthName == "September") {
                        $tr_data .= '"' . round($row->September, 2) . '",';
                    }

                    if ($monthName == "October") {
                        $tr_data .= '"' . round($row->October, 2) . '",';
                    }

                    if ($monthName == "November") {
                        $tr_data .= '"' . round($row->November, 2) . '",';
                    }

                    if ($monthName == "December") {
                        $tr_data .= '"' . round($row->December, 2) . '",';
                    }

                }
                //$tr_data .=substr($tr_data,0,strlen($tr_data)-1);
                $tr_data .= "\n";

            } //while

            $month_wise_total = $jan + $feb + $mar + $apr + $may + $jun + $july + $aug + $sep + $oct + $nov + $dec;

            $month_total = '"' . $title . '",';
            if ($gentype == '2') {
                $month_total .= '"",';
            } else {
                $month_total .= '"",';
            }
            if ($gentype != 0) {
                $month_total .= '"",';
            } else {
                $month_total .= '"",';
            }
            $month_total .= '"",';
            $month_total .= '"",';
            $month_total .= '"",';
            $month_total .= '"",';
            $month_total .= '"",';
            $month_total .= '"",';
            $month_total .= '"",';
            $month_total .= '"",';
            $month_total .= '"",';
            $month_total .= '"",';
            //$month_total .=   '"Access denied: content item not licenced",';
            $month_total .= '"' . $month_wise_total . '",';

            for ($j = 0; $j < count($monthsyear); $j++) {
                $monthNum  = $monthsyear[$j]['month'];
                $monthName = date("F", mktime(0, 0, 0, $monthNum, 10));
                if ($monthName == "January") {
                    $month_total .= '"' . $jan . '",';
                }

                if ($monthName == "February") {
                    $month_total .= '"' . $feb . '",';
                }

                if ($monthName == "March") {
                    $month_total .= '"' . $mar . '",';
                }

                if ($monthName == "April") {
                    $month_total .= '"' . $apr . '",';
                }

                if ($monthName == "May") {
                    $month_total .= '"' . $may . '",';
                }

                if ($monthName == "June") {
                    $month_total .= '"' . $jun . '",';
                }

                if ($monthName == "July") {
                    $month_total .= '"' . $july . '",';
                }

                if ($monthName == "August") {
                    $month_total .= '"' . $aug . '",';
                }

                if ($monthName == "September") {
                    $month_total .= '"' . $sep . '",';
                }

                if ($monthName == "October") {
                    $month_total .= '"' . $oct . '",';
                }

                if ($monthName == "November") {
                    $month_total .= '"' . $nov . '",';
                }

                if ($monthName == "December") {
                    $month_total .= '"' . $dec . '",';
                }

            }
            //$month_total .= substr($month_total,0,strlen($month_total)-1);
            $month_total .= "\n";

            $table = $tableHeader . $month_total . $tr_data;

            //  print($table);die;

            header("Content-type: text/csv");
            header("Content-Disposition: attachment; filename=countercompliant_report.csv");
            print $table;
            exit;
        }
    }

    public function detail_detailreport_jr1(Request $request,$input)
    {

        $gentype       = $request->get("exportrange");
        $user_id       = $request->get("user_id");
        $reptype       = $request->get("reporttype");
        $frommonthwise = $request->get('frommonthwise');
        $fromyearwise  = $request->get('toyearwise');
        $tomonthwise   = $request->get('tomonthwise');
        $toyearwise    = $request->get('toyearwise');
        $exporttype    = $request->get('exporttype');
        $fromdate      = $toyearwise . '-' . $frommonthwise . '-01';
        $todate        = $toyearwise . '-' . $tomonthwise . '-28';

        $UDRBegin   = date('Y-m-01', strtotime($fromdate));
        $UDREnd     = date('Y-m-t', strtotime($todate));
        $monthsyear = $this->getMonthsInRange($UDRBegin, $UDREnd);
        for ($j = 0; $j < count($monthsyear); $j++) {
            $monthNum  = $monthsyear[$j]['month'];
            $year      = $monthsyear[0]['year'];
            $monthName = date("F", mktime(0, 0, 0, $monthNum, 10));
        }
        for ($j = 0; $j < count($monthsyear); $j++) {
            $monthNum  = $monthsyear[$j]['month'];
            $year      = $monthsyear[0]['year'];
            $monthName = date("F", mktime(0, 0, 0, $monthNum, 10));
        }

        $resultuser = User::getUserById($user_id);
        $org_name   = $resultuser->first_name . ' ' . $resultuser->last_name;

        $type        = "Number of Successful Full-Text Article Requests by Month and Journal";
        $report_type = "Journal Report 1 Detail(R4)";
        $title       = "Total for all journals";
        //$issn = "Print ISSN";
        // $eissn = "Online ISSN";

        $query = "select j.title,j.issn ,j.eissn, a.journal_id,v.volume_name as volume,i.title as issue,ar.article_id as article_id,ar.title as article,ar.doi,a.ip_address,from_unixtime(a.timestamp) as downloadtime,
    (CASE WHEN download_file_type='1' OR download_file_type='5' THEN '1' END) AS 'PDF',
        (CASE WHEN download_file_type='2' THEN '1' END) AS 'HTML',
        (CASE WHEN download_file_type='3' OR download_file_type='4' THEN '1' END) AS 'OTHER',

    (CASE WHEN date_format(from_unixtime(timestamp),'%M')='January' THEN '1' END) AS 'January',
        (CASE WHEN date_format(from_unixtime(timestamp),'%M')='February' THEN '1' END) AS 'February',
        (CASE WHEN date_format(from_unixtime(timestamp),'%M')='March' THEN '1' END) AS 'March',
        (CASE WHEN date_format(from_unixtime(timestamp),'%M')='April' THEN '1' END) AS 'April',
        (CASE WHEN date_format(from_unixtime(timestamp),'%M')='May' THEN '1' END) AS 'May',
        (CASE WHEN date_format(from_unixtime(timestamp),'%M')='June' THEN '1' END) AS 'June',
        (CASE WHEN date_format(from_unixtime(timestamp),'%M')='July' THEN '1' END) AS 'July',
        (CASE WHEN date_format(from_unixtime(timestamp),'%M')='August' THEN '1' END) AS 'August',
        (CASE WHEN date_format(from_unixtime(timestamp),'%M')='September' THEN '1' END) AS 'September',
        (CASE WHEN date_format(from_unixtime(timestamp),'%M')='October' THEN '1' END) AS 'October',
        (CASE WHEN date_format(from_unixtime(timestamp),'%M')='November' THEN '1' END) AS 'November',
        (CASE WHEN date_format(from_unixtime(timestamp),'%M')='December' THEN '1' END) AS 'December'
        from download_count_articles a
        inner join journal j on a.journal_id= j.journal_id
        inner join article ar on a.article_id=ar.article_id
        left join issue i on ar.issue_id=i.issue_id
        inner join volume v on ar.volume_id=v.volume_id        and to_days(from_unixtime(timestamp)) between to_days('" . $UDRBegin . "') and to_days('" . $UDREnd . "')        and a.user_id='" . $user_id . "'
        order by timestamp desc
        ";
        //print($query);die;
        $result = DB::select($query);
        //print_r($result); exit;
        $colspan = "20";
        if ($exporttype == 1) {
            $tableHeader = "<table width='800' align='center' border='1'>
                        <tr>
                            <td><b>" . $report_type . "</b></td>
                            <td colspan='$colspan'><b>" . $type . "</b></td>

                        </tr>

                        <tr>
                            <td><b>" . $org_name . "</b></td>";

            $tableHeader .= "<td colspan='$colspan' align='left'>&nbsp;</td>";
            $tableHeader .= "</tr>
                        <tr>
                                <td><b></b></td>"; //Institutional Identifier
            $tableHeader .= "<td colspan='$colspan' align='left'>&nbsp;</td>";
            $tableHeader .= "</tr>
                        <tr>
                                <td><b>Period covered by Report:</b></td>
                                <td colspan='$colspan' align='left'>&nbsp;</td>
                            </tr>
                            <tr>
                                <td >" . $UDRBegin . " to " . $UDREnd . "</td>
                                <td colspan='$colspan' align='left'>&nbsp;</td>
                            </tr>
                        <tr>
                            <td align='left'><b>Date run:</b></td>
                            <td colspan='$colspan' align='left'>&nbsp;</td>
                        </tr>
                        <tr>
                            <td align='left'>'" . date("Y-m-d") . "</td>
                            <td colspan='$colspan' align='left'>&nbsp;</td>
                        </tr>

                        <tr align='center'>";
            $tableHeader .= "<td align='left' bgcolor='#CCCCCC'><b>Journal</b></td>";
            /*$tableHeader .="<td bgcolor='#CCCCCC'><b>".$issn."</b></td>
            <td bgcolor='#CCCCCC'><b>".$eissn."</b></td>";
            $tableHeader .="<td bgcolor='#CCCCCC'><b>Publisher</b></td>
            <td bgcolor='#CCCCCC'><b>Platform</b></td>";*/
            $tableHeader .= "<td bgcolor='#CCCCCC'><b>Volume</b></td>";
            $tableHeader .= "<td bgcolor='#CCCCCC'><b>Issue</b></td>";
            $tableHeader .= "<td bgcolor='#CCCCCC'><b>Article</b></td>";

            $tableHeader .= "<td bgcolor='#CCCCCC'><b>Article DOI</b></td>";
            $tableHeader .= "<td bgcolor='#CCCCCC'><b>IP Address</b></td>";
            $tableHeader .= "<td bgcolor='#CCCCCC'><b>Date</b></td>";
            $tableHeader .= "<td bgcolor='#CCCCCC'><b>PDF</b></td>";
            $tableHeader .= "<td bgcolor='#CCCCCC'><b>HTML</b></td>";
            $tableHeader .= "<td bgcolor='#CCCCCC'><b>Other</b></td>";

            $tableHeader .= " <td bgcolor='#CCCCCC'><b>Reporting Period Total</b></td>";

            for ($j = 0; $j < count($monthsyear); $j++) {
                $monthNum  = $monthsyear[$j]['month'];
                $monthName = date("M", mktime(0, 0, 0, $monthNum, 10));
                $tableHeader .= "<td bgcolor='#CCCCCC'><b>'$monthName" . $year . "</b></td>";
            }

            $tr_data = "";
            $jan     = 0;
            $feb     = 0;
            $mar     = 0;
            $apr     = 0;
            $may     = 0;
            $jun     = 0;
            $july    = 0;
            $aug     = 0;
            $sep     = 0;
            $oct     = 0;
            $nov     = 0;
            $dec     = 0;

            //while ($row = db_fetch_object($result)) {
            foreach ($result as $row) {

                $total_sale = round($row->January, 2) + round($row->February, 2) + round($row->March, 2) + round($row->April, 2) +
                round($row->May, 2) + round($row->June, 2) + round($row->July, 2) + round($row->August, 2) +
                round($row->September, 2) + round($row->October, 2) + round($row->November, 2) + round($row->December, 2);

                $jan  = $jan + round($row->January, 2);
                $feb  = $feb + round($row->February, 2);
                $mar  = $mar + round($row->March, 2);
                $apr  = $apr + round($row->April, 2);
                $may  = $may + round($row->May, 2);
                $jun  = $jun + round($row->June, 2);
                $july = $july + round($row->July, 2);
                $aug  = $aug + round($row->August, 2);
                $sep  = $sep + round($row->September, 2);
                $oct  = $oct + round($row->October, 2);
                $nov  = $nov + round($row->November, 2);
                $dec  = $dec + round($row->December, 2);

                $tr_data .= "<tr align='center'> ";

                $tr_data .= "<td align='left'>" . $row->title . "</td>";

                $tr_data .= "
                            <td>" . $row->volume . "</td>
                            <td>" . $row->issue . "</td>
                            <td>" . $row->article . "</td>
                            <td>" . $row->doi . "</td>
                            <td>" . $row->ip_address . "</td>
                            <td>" . $row->downloadtime . "</td>
                            <td>" . $row->PDF . "</td>
                            <td>" . $row->HTML . "</td>
                            <td>" . $row->OTHER . "</td>
                            <td>" . $total_sale . "</td>
                           ";

                for ($j = 0; $j < count($monthsyear); $j++) {
                    $monthNum  = $monthsyear[$j]['month'];
                    $monthName = date("F", mktime(0, 0, 0, $monthNum, 10));
                    //$tr_data .="<td>".round($row->$monthName,2)."</td>";
                    if ($monthName == "January") {
                        $tr_data .= "<td>" . round($row->January, 2) . "</td>";
                    }

                    if ($monthName == "February") {
                        $tr_data .= "<td>" . round($row->February, 2) . "</td>";
                    }

                    if ($monthName == "March") {
                        $tr_data .= "<td>" . round($row->March, 2) . "</td>";
                    }

                    if ($monthName == "April") {
                        $tr_data .= "<td>" . round($row->April, 2) . "</td>";
                    }

                    if ($monthName == "May") {
                        $tr_data .= "<td>" . round($row->May, 2) . "</td>";
                    }

                    if ($monthName == "June") {
                        $tr_data .= "<td>" . round($row->June, 2) . "</td>";
                    }

                    if ($monthName == "July") {
                        $tr_data .= "<td>" . round($row->July, 2) . "</td>";
                    }

                    if ($monthName == "August") {
                        $tr_data .= "<td>" . round($row->August, 2) . "</td>";
                    }

                    if ($monthName == "September") {
                        $tr_data .= "<td>" . round($row->September, 2) . "</td>";
                    }

                    if ($monthName == "October") {
                        $tr_data .= "<td>" . round($row->October, 2) . "</td>";
                    }

                    if ($monthName == "November") {
                        $tr_data .= "<td>" . round($row->November, 2) . "</td>";
                    }

                    if ($monthName == "December") {
                        $tr_data .= "<td>" . round($row->December, 2) . "</td>";
                    }

                }

                $tr_data .= "</tr>";

            } //while

            $month_wise_total = $jan + $feb + $mar + $apr + $may + $jun + $july + $aug + $sep + $oct + $nov + $dec;

            $month_total = "<tr align='center'>
                            <td align='left' bgcolor='#CCCCCC'><b>" . $title . "</b></td>";
            //not getting the gentype
            if ($gentype == '2') {
                $month_total .= "";
            } else {
                $month_total .= "";
            }
            if ($gentype != 0) {
                $month_total .= "<td bgcolor='#CCCCCC'></td>";
            } else {
                $month_total .= "<td bgcolor='#CCCCCC'></td>";
            }
            $month_total .= "<td bgcolor='#CCCCCC'>&nbsp;</td>
                            <td bgcolor='#CCCCCC'>&nbsp;</td>
                            <td bgcolor='#CCCCCC'>&nbsp;</td>
                            <td bgcolor='#CCCCCC'>&nbsp;</td>
                            <td bgcolor='#CCCCCC'>&nbsp;</td>
                            <td bgcolor='#CCCCCC'>&nbsp;</td>
                            <td bgcolor='#CCCCCC'>&nbsp;</td>
                            <td bgcolor='#CCCCCC'>&nbsp;</td>


                            "
            ;

            // $month_total .=   "<td bgcolor='#CCCCCC'><b>Access denied: content item not licenced<b></td>";
            $month_total .= " <td bgcolor='#CCCCCC'><b>" . $month_wise_total . "</b></td>";

            for ($j = 0; $j < count($monthsyear); $j++) {
                $monthNum  = $monthsyear[$j]['month'];
                $monthName = date("F", mktime(0, 0, 0, $monthNum, 10));
                if ($monthName == "January") {
                    $month_total .= "<td bgcolor='#CCCCCC'><b>" . $jan . "</b></td>";
                }

                if ($monthName == "February") {
                    $month_total .= "<td bgcolor='#CCCCCC'><b>" . $feb . "</b></td>";
                }

                if ($monthName == "March") {
                    $month_total .= "<td bgcolor='#CCCCCC'><b>" . $mar . "</b></td>";
                }

                if ($monthName == "April") {
                    $month_total .= "<td bgcolor='#CCCCCC'><b>" . $apr . "</b></td>";
                }

                if ($monthName == "May") {
                    $month_total .= "<td bgcolor='#CCCCCC'><b>" . $may . "</b></td>";
                }

                if ($monthName == "June") {
                    $month_total .= "<td bgcolor='#CCCCCC'><b>" . $jun . "</b></td>";
                }

                if ($monthName == "July") {
                    $month_total .= "<td bgcolor='#CCCCCC'><b>" . $july . "</b></td>";
                }

                if ($monthName == "August") {
                    $month_total .= "<td bgcolor='#CCCCCC'><b>" . $aug . "</b></td>";
                }

                if ($monthName == "September") {
                    $month_total .= "<td bgcolor='#CCCCCC'><b>" . $sep . "</b></td>";
                }

                if ($monthName == "October") {
                    $month_total .= "<td bgcolor='#CCCCCC'><b>" . $oct . "</b></td>";
                }

                if ($monthName == "November") {
                    $month_total .= "<td bgcolor='#CCCCCC'><b>" . $nov . "</b></td>";
                }

                if ($monthName == "December") {
                    $month_total .= "<td bgcolor='#CCCCCC'><b>" . $dec . "</b></td>";
                }

            }

            $month_total .= "</tr>";

            $table = $tableHeader . $month_total . $tr_data . "</table>";
            //print($table);die;

            header("Content-type: application/vnd.ms-excel");
            header("Content-Disposition: attachment; filename=countercompliant_report.xls");
            print $table;
            exit;

        } else if ($exporttype == 0) {
// Export file in CSV format

            //$year = substr($year,0,4);
            $year = $year;
            //echo($year);
            $tableHeader = '"' . $report_type . '","' . $type . '"' . "\n";
            $tableHeader .= '"' . $org_name . '"' . "\n";
            $tableHeader .= '""' . "\n"; //$tableHeader .='"Institutional Identifier"'."\n";
            $tableHeader .= '"Period covered by Report:"' . "\n";
            $tableHeader .= '"' . $UDRBegin . ' to ' . $UDREnd . '"' . "\n";
            $tableHeader .= '"Date run:"' . "\n";
            $tableHeader .= '"' . date("Y-m-d") . '"' . "\n";
            $tableHeader .= '"Journal","Volume","Issue","Article","Article DOI","IP Address","Date","PDF","HTML","Other",';

            $tableHeader .= '"Reporting Period Total",';
            for ($j = 0; $j < count($monthsyear); $j++) {
                $monthNum  = $monthsyear[$j]['month'];
                $monthName = date("M", mktime(0, 0, 0, $monthNum, 10));
                $tableHeader .= '"' . $monthName . '-' . $year . '",';

            }
            //$tableHeader .=substr($tableHeader,0,strlen($tableHeader)-1);
            $tableHeader .= "\n";

            $tr_data = "";
            $jan     = 0;
            $feb     = 0;
            $mar     = 0;
            $apr     = 0;
            $may     = 0;
            $jun     = 0;
            $july    = 0;
            $aug     = 0;
            $sep     = 0;
            $oct     = 0;
            $nov     = 0;
            $dec     = 0;

            //while ($row = db_fetch_object($result)) {
            foreach ($result as $row) {
                $total_sale = round($row->January, 2) + round($row->February, 2) + round($row->March, 2) + round($row->April, 2) +
                round($row->May, 2) + round($row->June, 2) + round($row->July, 2) + round($row->August, 2) +
                round($row->September, 2) + round($row->October, 2) + round($row->November, 2) + round($row->December, 2);

                $jan  = $jan + round($row->January, 2);
                $feb  = $feb + round($row->February, 2);
                $mar  = $mar + round($row->March, 2);
                $apr  = $apr + round($row->April, 2);
                $may  = $may + round($row->May, 2);
                $jun  = $jun + round($row->June, 2);
                $july = $july + round($row->July, 2);
                $aug  = $aug + round($row->August, 2);
                $sep  = $sep + round($row->September, 2);
                $oct  = $oct + round($row->October, 2);
                $nov  = $nov + round($row->November, 2);
                $dec  = $dec + round($row->December, 2);

                $tr_data .= '"' . $row->title . '",';
                $tr_data .= '"' . $row->volume . '",';
                $tr_data .= '"' . $row->issue . '",';
                $tr_data .= '"' . $row->article . '",';
                $tr_data .= '"' . $row->doi . '",';
                $tr_data .= '"' . $row->ip_address . '",';
                $tr_data .= '"' . $row->downloadtime . '",';
                $tr_data .= '"' . $row->PDF . '",';
                $tr_data .= '"' . $row->HTML . '",';
                $tr_data .= '"' . $row->OTHER . '",';

                $tr_data .= '"' . $total_sale . '",';

                for ($j = 0; $j < count($monthsyear); $j++) {
                    $monthNum  = $monthsyear[$j]['month'];
                    $monthName = date("F", mktime(0, 0, 0, $monthNum, 10));
                    //$tr_data .='"'.round($row->$monthName,2).'",';
                    if ($monthName == "January") {
                        $tr_data .= '"' . round($row->January, 2) . '",';
                    }

                    if ($monthName == "February") {
                        $tr_data .= '"' . round($row->February, 2) . '",';
                    }

                    if ($monthName == "March") {
                        $tr_data .= '"' . round($row->March, 2) . '",';
                    }

                    if ($monthName == "April") {
                        $tr_data .= '"' . round($row->April, 2) . '",';
                    }

                    if ($monthName == "May") {
                        $tr_data .= '"' . round($row->May, 2) . '",';
                    }

                    if ($monthName == "June") {
                        $tr_data .= '"' . round($row->June, 2) . '",';
                    }

                    if ($monthName == "July") {
                        $tr_data .= '"' . round($row->July, 2) . '",';
                    }

                    if ($monthName == "August") {
                        $tr_data .= '"' . round($row->August, 2) . '",';
                    }

                    if ($monthName == "September") {
                        $tr_data .= '"' . round($row->September, 2) . '",';
                    }

                    if ($monthName == "October") {
                        $tr_data .= '"' . round($row->October, 2) . '",';
                    }

                    if ($monthName == "November") {
                        $tr_data .= '"' . round($row->November, 2) . '",';
                    }

                    if ($monthName == "December") {
                        $tr_data .= '"' . round($row->December, 2) . '",';
                    }

                }
                //$tr_data .=substr($tr_data,0,strlen($tr_data)-1);
                $tr_data .= "\n";

            } //while

            $month_wise_total = $jan + $feb + $mar + $apr + $may + $jun + $july + $aug + $sep + $oct + $nov + $dec;

            $month_total = '"' . $title . '",';
            if ($gentype == '2') {
                $month_total .= '"",';
            } else {
                $month_total .= '"",';
            }
            if ($gentype != 0) {
                $month_total .= '"",';
            } else {
                $month_total .= '"",';
            }
            $month_total .= '"",';
            $month_total .= '"",';
            $month_total .= '"",';
            $month_total .= '"",';
            $month_total .= '"",';
            $month_total .= '"",';
            $month_total .= '"",';
            /*$month_total .= '"",';
            $month_total .= '"",';
            $month_total .= '"",';
            $month_total .= '"",';
            $month_total .= '"",';*/
            //$month_total .=   '"Access denied: content item not licenced",';
            $month_total .= '"' . $month_wise_total . '",';

            for ($j = 0; $j < count($monthsyear); $j++) {
                $monthNum  = $monthsyear[$j]['month'];
                $monthName = date("F", mktime(0, 0, 0, $monthNum, 10));
                if ($monthName == "January") {
                    $month_total .= '"' . $jan . '",';
                }

                if ($monthName == "February") {
                    $month_total .= '"' . $feb . '",';
                }

                if ($monthName == "March") {
                    $month_total .= '"' . $mar . '",';
                }

                if ($monthName == "April") {
                    $month_total .= '"' . $apr . '",';
                }

                if ($monthName == "May") {
                    $month_total .= '"' . $may . '",';
                }

                if ($monthName == "June") {
                    $month_total .= '"' . $jun . '",';
                }

                if ($monthName == "July") {
                    $month_total .= '"' . $july . '",';
                }

                if ($monthName == "August") {
                    $month_total .= '"' . $aug . '",';
                }

                if ($monthName == "September") {
                    $month_total .= '"' . $sep . '",';
                }

                if ($monthName == "October") {
                    $month_total .= '"' . $oct . '",';
                }

                if ($monthName == "November") {
                    $month_total .= '"' . $nov . '",';
                }

                if ($monthName == "December") {
                    $month_total .= '"' . $dec . '",';
                }

            }
            //$month_total .= substr($month_total,0,strlen($month_total)-1);
            $month_total .= "\n";

            $table = $tableHeader . $month_total . $tr_data;

            //  print($table);die;

            header("Content-type: text/csv");
            header("Content-Disposition: attachment; filename=countercompliant_report.csv");
            print $table;
            exit;
        }
    }

    public function getMonthsInRange($startDate, $endDate)
    {
        $months = array();
        while (strtotime($startDate) <= strtotime($endDate)) {
            $months[]  = array('year' => date('Y', strtotime($startDate)), 'month' => date('m', strtotime($startDate)));
            $startDate = date('d M Y', strtotime($startDate . '+ 1 month'));
        }

        return $months;
    }

    public function CountMultipleVolumeExist($ebook_id, $volume_id)
    {

        $query = "select count(*) as totalcount from ebook_volume where ebook_id = '$ebook_id' and ebook_volume_id != '$volume_id'";

        return DB::select($query)[0]->totalcount;
    }

}
