<?php

/* * **************************
 * File : report_content_download.php
 * Author : Cygnusa
 * @2020
 * ************************** */

// included in reportdownload.php 

// content_report_v2
function content_report_v2() {
    global $conn;
    try {
        $lang = "en_us";
        $pageData = $conn->loadJSON($lang);
        //$_SESSION["userid"]=1;
        if(isset($_SESSION["userid"]))
        { 
            $page = escape_str($_REQUEST["page"]);
        
            $zm_email = escape_str($_REQUEST["zm_email"]);
            $rm_email = escape_str($_REQUEST["rm_email"]);
            $am_email = escape_str($_REQUEST["am_email"]);
            
            $user_cond='';
                            
            if(($am_email!='all' && $am_email!='')||($rm_email!='all' && $rm_email!='')||($zm_email!='all' && $zm_email!=''))
            {
                $mcond='';
                $mcond=($am_email!='all' && $am_email!='')?" AND d.am_email='".$am_email."'":''; 
                $mcond.=($rm_email!='all' && $rm_email!='')?" AND d.rm_email='".$rm_email."'":'';         
                $mcond.=($zm_email!='all' && $zm_email!='')?" AND d.zm_email='".$zm_email."'":'';         
                //AM
                $user_cond=$mcond;
            }else
            {
                //PAN
                $user_cond='';
            } 
            
            require_once dirname(__FILE__) .DIRECTORY_SEPARATOR. 'PHPExcel.php';
            require_once dirname(__FILE__) . DIRECTORY_SEPARATOR.'PHPExcel/Writer/Excel2007.php';
            $objPHPExcel = new PHPExcel();
            $objPHPExcel->setActiveSheetIndex(0);
            
            $row+=2;
            $objPHPExcel->getActiveSheet()->SetCellValue('A'.$row,"CONTENT REPORT");
            $objPHPExcel->getActiveSheet()->mergeCells("A".$row.":F".$row);
            $objPHPExcel->getActiveSheet()->getStyle("A".$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle("A".$row)->getFont()->setBold(true);
            $objPHPExcel->getActiveSheet()->getStyle("A".$row)->getFont()->setSize(14);
            
            $cateid = escape_str($_REQUEST["cateid"]);     
            $query_str = "SELECT `cateid`, `catename` FROM  ".TABLE_CATEGORY." WHERE cateid='".$cateid."' LIMIT 1";
            $cat_arr=$conn->getQueryValue($query_str);
            if(!count($cat_arr)>0)
            {
                $conn->message(false,$conn->getMessage($lang,'report','error2'));
            }  
            
            $row++;        
            $objPHPExcel->getActiveSheet()->SetCellValue('A'.$row,"CATEGORY : ".$cat_arr[0]->catename);
            $objPHPExcel->getActiveSheet()->getStyle("A".$row)->getFont()->setBold(true);
            $objPHPExcel->getActiveSheet()->mergeCells("A".$row.":D".$row);
            
            
            $row++;
            $bordercells_arr=array("A".$row,"B".$row,"C".$row,"D".$row,"E".$row,'F'.$row);
            setCellBorder($bordercells_arr,$objPHPExcel);
            $objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(-1);
             //Dashboard
            //Learning
            //Assessment
            //Feedback
            
            $header_arr=array('S.No','Topic', 'Content Title','Content type','No. of DSE Viewed','No. of DSE Completed');
             
            $i=0;
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('15');
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('45');
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('45');
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('15');
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('30');
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('30');
            
            $i=0;
            foreach($header_arr as $val)
            { 
                $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$val);
            } 
            
            
            //GET ALL CONTENTS OF CATEGORY
            $query_str="SELECT c.assettopicid,ca.cateid,c.title,t.topicname,c.contentid,c.title,f.filetypeid FROM `".TABLE_ASSET_CONTENT."` as c LEFT JOIN ".TABLE_ASSET_TOPIC." as t ON c.assettopicid=t.assettopicid LEFT JOIN ".TABLE_CATEGORY." as ca ON ca.cateid=t.cateid LEFT JOIN ".TABLE_ASSET_FILE." as f ON f.contentid=c.contentid  WHERE ca.cateid ='".$cateid."' AND t.is_deleted='0' AND c.is_deleted='0' GROUP BY c.contentid ORDER BY t.position ASC,t.assettopicid ASC,c.contentid ASC ";
            $contentresult_arr=$conn->getQueryValue($query_str);
            
            if(count($contentresult_arr))
            {
                $sno=1;
                foreach($contentresult_arr as $res_content)
                { 
                    //'#','Topic', 'Content','Content type','DSE Completed'
                    $i=0;
                    $row++;
                    
                    $count_str="SELECT COUNT(c.userid) as total_view FROM (SELECT userid,contentid FROM `".TABLE_USER_COMPLETE_CONTENT."` WHERE  contentid='".$res_content->contentid."' ) c JOIN ".TABLE_USER." u ON u.userid=c.userid  JOIN ".DMS_EMPLOYEE." e ON u.smpin=e.smpin JOIN ".DMS_DEALER." as d ON d.dealer_code=u.dealer_code WHERE u.is_deleted=0 AND u.role>7 AND u.role<14 AND e.is_current=1 ".$user_cond." GROUP BY c.contentid LIMIT 1";
                    $count_arr=$conn->getQueryValue($count_str); 
                    $res_content->total_view=(count($count_arr)>0)?$count_arr[0]->total_view:0;
                    
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$sno++);                   
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$res_content->topicname);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$res_content->title);
                    switch($res_content->filetypeid)
                    {
                        case '1':$file_type_str='PDF';
                            break;
                        case '2':$file_type_str='MP4';
                            break;   
                        case '3':$file_type_str='MP3';
                            break;
                        default:$file_type_str='';
                            break;
                    }
                    
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$file_type_str);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$res_content->total_view);
                        
                    $count_str="SELECT COUNT(c.userid) as total_view FROM (SELECT userid,contentid FROM `".TABLE_USER_COMPLETE_CONTENT."` WHERE status=2 AND contentid='".$res_content->contentid."' ) c JOIN ".TABLE_USER." u ON u.userid=c.userid  JOIN ".DMS_EMPLOYEE." e ON u.smpin=e.smpin JOIN ".DMS_DEALER." as d ON d.dealer_code=u.dealer_code WHERE u.is_deleted=0 AND u.role>7 AND u.role<14 AND e.is_current=1 ".$user_cond." GROUP BY c.contentid LIMIT 1";
                    $count_arr=$conn->getQueryValue($count_str); 
                    $res_content->total_completed=(count($count_arr)>0)?$count_arr[0]->total_view:0;
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$res_content->total_completed);
                        
                }
            }
            
            $objPHPExcel->getActiveSheet()->setTitle('CONTENT REPORT');	        
            $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
            $fpath="../".S3_REPORT_URL;
            $filename="content_report_". uniqid().".xlsx";			 
            $objWriter->save($fpath.$filename);
            
            //S3 UPLOAD
            //S3 LIB
            require (dirname(__FILE__).'/awsfileserver.php'); 

            //S3 OBJ
            $s3=awsfileserver::connectS3();
            awsfileserver::checkupload($s3,S3_BUCKET,S3_REPORT_URL.$filename,dirname(__FILE__)."/../".S3_REPORT_URL.$filename);

            //DELETE AFTER MOVE TO S3
            @unlink(dirname(__FILE__).'/../'.S3_REPORT_URL.$filename);
            
            $obj = new stdClass();
            $obj->key_name=S3_REPORT_URL.$filename;			
            $url=awsfileserver::getS3Url($s3,S3_BUCKET,$obj);	
            
            $returnarr=array("report_url"=>$url);
            
            $conn->message(true,$conn->getMessage($lang,'report','success1'), $returnarr);  
            
        }else{
            $conn->message(false,$conn->getMessage($lang,'report','error1'));
        }
    }catch (PDOException $e) {
        $conn->message(false,$conn->getMessage($lang,'report','error4'));
    }
}


// content_report_list_v2
function content_report_list_v2() {
    global $conn;
    try {
        $lang = "en_us";
        $pageData = $conn->loadJSON($lang);
        //$_SESSION["userid"]=1;
        if(isset($_SESSION["userid"]))
        { 
            $page = escape_str($_REQUEST["page"]);
        
            $zm_email = escape_str($_REQUEST["zm_email"]);
            $rm_email = escape_str($_REQUEST["rm_email"]);
            $am_email = escape_str($_REQUEST["am_email"]);
            
            $user_cond='';
                            
            if(($am_email!='all' && $am_email!='')||($rm_email!='all' && $rm_email!='')||($zm_email!='all' && $zm_email!=''))
            {
                $mcond='';
                $mcond=($am_email!='all' && $am_email!='')?" AND d.am_email='".$am_email."'":''; 
                $mcond.=($rm_email!='all' && $rm_email!='')?" AND d.rm_email='".$rm_email."'":'';         
                $mcond.=($zm_email!='all' && $zm_email!='')?" AND d.zm_email='".$zm_email."'":'';         
                //AM
                $user_cond=$mcond;
            }else
            {
                //PAN
                $user_cond='';
            } 
            
            require_once dirname(__FILE__) .DIRECTORY_SEPARATOR. 'PHPExcel.php';
            require_once dirname(__FILE__) . DIRECTORY_SEPARATOR.'PHPExcel/Writer/Excel2007.php';
            $objPHPExcel = new PHPExcel();
            $objPHPExcel->setActiveSheetIndex(0);
            
            $row+=2;
            $objPHPExcel->getActiveSheet()->SetCellValue('A'.$row,"CONTENT REPORT");
            $objPHPExcel->getActiveSheet()->mergeCells("A".$row.":L".$row);
            $objPHPExcel->getActiveSheet()->getStyle("A".$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle("A".$row)->getFont()->setBold(true);
            $objPHPExcel->getActiveSheet()->getStyle("A".$row)->getFont()->setSize(14);
            
                        
            $assettopicid = escape_str($_REQUEST["assettopicid"]);        
            $contentid = escape_str($_REQUEST["contentid"]);              
            //CONTENT
            $query_str="SELECT c.assettopicid,ca.cateid,c.title,t.topicname,c.contentid,c.title FROM `".TABLE_ASSET_CONTENT."` as c LEFT JOIN ".TABLE_ASSET_TOPIC." as t ON c.assettopicid=t.assettopicid LEFT JOIN ".TABLE_CATEGORY." as ca ON ca.cateid=t.cateid LEFT JOIN ".TABLE_ASSET_FILE." as f ON f.contentid=c.contentid  WHERE c.contentid ='".$contentid."'  AND c.assettopicid='".$assettopicid."' AND t.is_deleted='0' AND c.is_deleted='0' GROUP BY c.contentid LIMIT 1 ";
            $content_arr=$conn->getQueryValue($query_str);
            if(!count($content_arr)>0)
            {
                $conn->message(false,$conn->getMessage($lang,'report','error2'));
            } 
            
            
            $row++;        
            $objPHPExcel->getActiveSheet()->SetCellValue('A'.$row,"TOPIC : ".$content_arr[0]->topicname);
            $objPHPExcel->getActiveSheet()->getStyle("A".$row)->getFont()->setBold(true);
            $objPHPExcel->getActiveSheet()->mergeCells("A".$row.":D".$row);
            
            $row++;        
            $objPHPExcel->getActiveSheet()->SetCellValue('A'.$row,"CONTENT : ".$content_arr[0]->title);
            $objPHPExcel->getActiveSheet()->getStyle("A".$row)->getFont()->setBold(true);
            $objPHPExcel->getActiveSheet()->mergeCells("A".$row.":D".$row);
           
            $row++;
            $bordercells_arr=array("A".$row,"B".$row,"C".$row,"D".$row,"E".$row,"F".$row,"G".$row,"H".$row,"I".$row,"J".$row,"K".$row,'L'.$row,"M".$row);
            setCellBorder($bordercells_arr,$objPHPExcel);
            $objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(-1);           
            
            $header_arr=array('Title','SMPIN', 'DSE Name','Segment','Mobile','Status','Completed on','Dealer Code','Dealer Name','Zone','ZM','RM','AM');
             
            $i=0;
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('15');
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('15');
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('30');
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('15');
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('15');
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('15');
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('20');
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('15');
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('25'); 
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('15');
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('30');
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('30');
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('30');
            
            $i=0;
            foreach($header_arr as $val)
            { 
                $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$val);
            } 
            
            
            //GET ALL USER
            $content_user_str="SELECT u.smpin,u.fname,u.lname,u.mobileno,u.dealer_code,u.segment,c.completeddate,d.dealer_name,d.zone,d.`zm_name`,d.`rm_name`,d.`am_name`,CASE WHEN c.status=0 THEN 'Started' WHEN c.status=1 THEN 'In progress' WHEN c.status=2 THEN 'Completed' ELSE 'In progress' END as content_status ,c.status as cstatus FROM (SELECT id,userid,contentid,status,completeddate,modified_on  FROM `".TABLE_USER_COMPLETE_CONTENT."` WHERE  contentid='".$contentid."' AND assettopicid='".$assettopicid."') c JOIN ".TABLE_USER." u ON u.userid=c.userid  JOIN ".DMS_EMPLOYEE." e ON u.smpin=e.smpin JOIN ".DMS_DEALER." as d ON d.dealer_code=u.dealer_code WHERE u.role>7 and u.role<14 AND  e.is_current=1 ".$user_cond." GROUP BY c.userid ORDER BY c.status DESC,c.modified_on ASC ";
            $contentresult_arr=$conn->getQueryValue($content_user_str);
            
            if(count($contentresult_arr))
            {
                $sno=1;
                foreach($contentresult_arr as $res_user)
                {  
                    //$header_arr=array('#','SMPIN', 'DSE Name','Mobile','Completed on','Dealer Code','Dealer Name','Zone','ZM','RM','AM');
                    $i=0;
                    $row++;
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$sno++);
                    $objPHPExcel->getActiveSheet()->getStyle('B'.$row)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$res_user->smpin);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$res_user->fname." ".$res_user->lname);
                    switch($res_user->segment)
                    {
                        case '0':$segment_str='Student Learner'; break;
                        case '1':$segment_str='Parents/Guardian'; break;
                        case '2':$segment_str='Instructor'; break;
                        default:$segment_str='Student Learner'; break;
                    }
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$segment_str);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$res_user->mobileno);                    
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$res_user->content_status);     
                   
                    switch($res_user->cstatus)
                    {
                        case '0': $color='D3D3D3';break;
                        case '1': $color='FFA500'; break;
                        case '2': $color='008000';break;
                        default:$color='D3D3D3';break; 
                    }
                    $objPHPExcel->getActiveSheet()->getStyle('F'.$row)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB($color);
                    
                    $objPHPExcel->getActiveSheet()->getStyle('G'.$row)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
                    $completeddate=($res_user->completeddate!='0000-00-00 00:00:00')?date('d-m-Y',strtotime($res_user->completeddate)):'';
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$completeddate);
                    
                    $objPHPExcel->getActiveSheet()->getStyle('H'.$row)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$res_user->dealer_code);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$res_user->dealer_name);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$res_user->zone);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$res_user->zm_name);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$res_user->rm_name);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$res_user->am_name); 
                }
            }
            
            $objPHPExcel->getActiveSheet()->setTitle('CONTENT REPORT');	        
            $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
            $fpath="../".S3_REPORT_URL;
            $filename="user_content_report_". uniqid().".xlsx";			 
            $objWriter->save($fpath.$filename);
            
            //S3 UPLOAD
            //S3 LIB
            require (dirname(__FILE__).'/awsfileserver.php'); 

            //S3 OBJ
            $s3=awsfileserver::connectS3();
            awsfileserver::checkupload($s3,S3_BUCKET,S3_REPORT_URL.$filename,dirname(__FILE__)."/../".S3_REPORT_URL.$filename);

            //DELETE AFTER MOVE TO S3
            @unlink(dirname(__FILE__).'/../'.S3_REPORT_URL.$filename);
            
            $obj = new stdClass();
            $obj->key_name=S3_REPORT_URL.$filename;			
            $url=awsfileserver::getS3Url($s3,S3_BUCKET,$obj);	
            
            $returnarr=array("report_url"=>$url);
            
            $conn->message(true,$conn->getMessage($lang,'report','success1'), $returnarr);  
            
        }else{
            $conn->message(false,$conn->getMessage($lang,'report','error1'));
        }
    }catch (PDOException $e) {
        $conn->message(false,$conn->getMessage($lang,'report','error4'));
    }
}

function content_report_zm() {
    global $conn;
    try {
        $lang = "en_us";
        $pageData = $conn->loadJSON($lang);
        //$_SESSION["userid"]=1;
        if(isset($_SESSION["userid"]))
        { 
            $page = escape_str($_REQUEST["page"]);
                        
            
            $user_cond='';
                        
            
            require_once dirname(__FILE__) .DIRECTORY_SEPARATOR. 'PHPExcel.php';
            require_once dirname(__FILE__) . DIRECTORY_SEPARATOR.'PHPExcel/Writer/Excel2007.php';
            $objPHPExcel = new PHPExcel();
            $objPHPExcel->setActiveSheetIndex(0);
            
            $row+=2;
            $objPHPExcel->getActiveSheet()->SetCellValue('A'.$row,"ZM CONTENT REPORT");
            $objPHPExcel->getActiveSheet()->mergeCells("A".$row.":F".$row);
            $objPHPExcel->getActiveSheet()->getStyle("A".$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle("A".$row)->getFont()->setBold(true);
            $objPHPExcel->getActiveSheet()->getStyle("A".$row)->getFont()->setSize(14);
            
            $cateid = escape_str($_REQUEST["cateid"]);     
            $query_str = "SELECT `cateid`, `catename` FROM  ".TABLE_CATEGORY." WHERE cateid='".$cateid."' LIMIT 1";
            $cat_arr=$conn->getQueryValue($query_str);
            if(!count($cat_arr)>0)
            {
                $conn->message(false,$conn->getMessage($lang,'report','error2'));
            }  
            
            $row++;        
            $objPHPExcel->getActiveSheet()->SetCellValue('A'.$row,"CATEGORY : ".$cat_arr[0]->catename);
            $objPHPExcel->getActiveSheet()->getStyle("A".$row)->getFont()->setBold(true);
            $objPHPExcel->getActiveSheet()->mergeCells("A".$row.":D".$row);
            
            
            $row++;
            $bordercells_arr=array("A".$row,"B".$row,"C".$row,"D".$row,"E".$row,'F'.$row);
            setCellBorder($bordercells_arr,$objPHPExcel);
            $objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(-1);
             //Dashboard
            //Learning
            //Assessment
            //Feedback
            
            $header_arr=array('S.No','Topic', 'Content Title','Content type','No. of ZM Viewed','No. of ZM Completed');
             
            $i=0;
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('15');
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('45');
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('45');
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('15');
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('30');
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('30');
            
            $i=0;
            foreach($header_arr as $val)
            { 
                $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$val);
            } 
            
            
            //GET ALL CONTENTS OF CATEGORY
            $query_str="SELECT c.assettopicid,ca.cateid,c.title,t.topicname,c.contentid,c.title,f.filetypeid FROM `".TABLE_ASSET_CONTENT."` as c LEFT JOIN ".TABLE_ASSET_TOPIC." as t ON c.assettopicid=t.assettopicid LEFT JOIN ".TABLE_CATEGORY." as ca ON ca.cateid=t.cateid LEFT JOIN ".TABLE_ASSET_FILE." as f ON f.contentid=c.contentid  WHERE ca.cateid ='".$cateid."' AND (c.filter_role='' OR FIND_IN_SET(4,c.filter_role))  AND t.is_deleted='0' AND c.is_deleted='0' GROUP BY c.contentid ORDER BY t.position ASC,t.assettopicid ASC,c.contentid ASC ";
            $contentresult_arr=$conn->getQueryValue($query_str);
            
            if(count($contentresult_arr))
            {
                $sno=1;
                foreach($contentresult_arr as $res_content)
                { 
                    //'#','Topic', 'Content','Content type','DSE Completed'
                    $i=0;
                    $row++;
                    
                    $count_str="SELECT COUNT(c.userid) as total_view FROM (SELECT userid,contentid FROM `".TABLE_USER_COMPLETE_CONTENT."` WHERE  contentid='".$res_content->contentid."' ) c JOIN ".TABLE_USER." u ON u.userid=c.userid  WHERE u.is_deleted=0 AND u.role=4 ".$user_cond." GROUP BY c.contentid LIMIT 1";
                    $count_arr=$conn->getQueryValue($count_str); 
                    $res_content->total_view=(count($count_arr)>0)?$count_arr[0]->total_view:0;
                    
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$sno++);                   
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$res_content->topicname);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$res_content->title);
                    switch($res_content->filetypeid)
                    {
                        case '1':$file_type_str='PDF';
                            break;
                        case '2':$file_type_str='MP4';
                            break;   
                        case '3':$file_type_str='MP3';
                            break;
                        default:$file_type_str='';
                            break;
                    }
                    
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$file_type_str);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$res_content->total_view);
                        
                    $count_str="SELECT COUNT(c.userid) as total_view FROM (SELECT userid,contentid FROM `".TABLE_USER_COMPLETE_CONTENT."` WHERE status=2 AND contentid='".$res_content->contentid."' ) c JOIN ".TABLE_USER." u ON u.userid=c.userid  WHERE u.is_deleted=0 AND u.role=4 ".$user_cond." GROUP BY c.contentid LIMIT 1";
                    $count_arr=$conn->getQueryValue($count_str); 
                    $res_content->total_completed=(count($count_arr)>0)?$count_arr[0]->total_view:0;
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$res_content->total_completed);
                        
                }
            }
            
            $objPHPExcel->getActiveSheet()->setTitle('CONTENT REPORT');	        
            $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
            $fpath="../".S3_REPORT_URL;
            $filename="content_report_". uniqid().".xlsx";			 
            $objWriter->save($fpath.$filename);
            
            //S3 UPLOAD
            //S3 LIB
            require (dirname(__FILE__).'/awsfileserver.php'); 

            //S3 OBJ
            $s3=awsfileserver::connectS3();
            awsfileserver::checkupload($s3,S3_BUCKET,S3_REPORT_URL.$filename,dirname(__FILE__)."/../".S3_REPORT_URL.$filename);

            //DELETE AFTER MOVE TO S3
            @unlink(dirname(__FILE__).'/../'.S3_REPORT_URL.$filename);
            
            $obj = new stdClass();
            $obj->key_name=S3_REPORT_URL.$filename;			
            $url=awsfileserver::getS3Url($s3,S3_BUCKET,$obj);	
            
            $returnarr=array("report_url"=>$url);
            
            $conn->message(true,$conn->getMessage($lang,'report','success1'), $returnarr);  
            
        }else{
            $conn->message(false,$conn->getMessage($lang,'report','error1'));
        }
    }catch (PDOException $e) {
        $conn->message(false,$conn->getMessage($lang,'report','error4'));
    }
}


function content_report_trainer() {
    global $conn;
    try {
        $lang = "en_us";
        $pageData = $conn->loadJSON($lang);
        //$_SESSION["userid"]=1;
        if(isset($_SESSION["userid"]))
        { 
            $page = escape_str($_REQUEST["page"]);
                        
            
            $user_cond='';
                        
            
            require_once dirname(__FILE__) .DIRECTORY_SEPARATOR. 'PHPExcel.php';
            require_once dirname(__FILE__) . DIRECTORY_SEPARATOR.'PHPExcel/Writer/Excel2007.php';
            $objPHPExcel = new PHPExcel();
            $objPHPExcel->setActiveSheetIndex(0);
            
            $row+=2;
            $objPHPExcel->getActiveSheet()->SetCellValue('A'.$row,"TRAINER CONTENT REPORT");
            $objPHPExcel->getActiveSheet()->mergeCells("A".$row.":F".$row);
            $objPHPExcel->getActiveSheet()->getStyle("A".$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle("A".$row)->getFont()->setBold(true);
            $objPHPExcel->getActiveSheet()->getStyle("A".$row)->getFont()->setSize(14);
            
            $cateid = escape_str($_REQUEST["cateid"]);     
            $query_str = "SELECT `cateid`, `catename` FROM  ".TABLE_CATEGORY." WHERE cateid='".$cateid."' LIMIT 1";
            $cat_arr=$conn->getQueryValue($query_str);
            if(!count($cat_arr)>0)
            {
                $conn->message(false,$conn->getMessage($lang,'report','error2'));
            }  
            
            $row++;        
            $objPHPExcel->getActiveSheet()->SetCellValue('A'.$row,"CATEGORY : ".$cat_arr[0]->catename);
            $objPHPExcel->getActiveSheet()->getStyle("A".$row)->getFont()->setBold(true);
            $objPHPExcel->getActiveSheet()->mergeCells("A".$row.":D".$row);
            
            
            $row++;
            $bordercells_arr=array("A".$row,"B".$row,"C".$row,"D".$row,"E".$row,'F'.$row);
            setCellBorder($bordercells_arr,$objPHPExcel);
            $objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(-1);
             //Dashboard
            //Learning
            //Assessment
            //Feedback
            
            $header_arr=array('S.No','Topic', 'Content Title','Content type','No. of Trainer Viewed','No. of Trainer Completed');
             
            $i=0;
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('15');
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('45');
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('45');
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('15');
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('30');
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('30');
            
            $i=0;
            foreach($header_arr as $val)
            { 
                $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$val);
            } 
            
            
            //GET ALL CONTENTS OF CATEGORY
            $query_str="SELECT c.assettopicid,ca.cateid,c.title,t.topicname,c.contentid,c.title,f.filetypeid FROM `".TABLE_ASSET_CONTENT."` as c LEFT JOIN ".TABLE_ASSET_TOPIC." as t ON c.assettopicid=t.assettopicid LEFT JOIN ".TABLE_CATEGORY." as ca ON ca.cateid=t.cateid LEFT JOIN ".TABLE_ASSET_FILE." as f ON f.contentid=c.contentid  WHERE ca.cateid ='".$cateid."' AND (c.filter_role='' OR FIND_IN_SET(5,c.filter_role))  AND t.is_deleted='0' AND c.is_deleted='0' GROUP BY c.contentid ORDER BY t.position ASC,t.assettopicid ASC,c.contentid ASC ";
            $contentresult_arr=$conn->getQueryValue($query_str);
            
            if(count($contentresult_arr))
            {
                $sno=1;
                foreach($contentresult_arr as $res_content)
                { 
                    //'#','Topic', 'Content','Content type','DSE Completed'
                    $i=0;
                    $row++;
                    
                    $count_str="SELECT COUNT(c.userid) as total_view FROM (SELECT userid,contentid FROM `".TABLE_USER_COMPLETE_CONTENT."` WHERE  contentid='".$res_content->contentid."' ) c JOIN ".TABLE_USER." u ON u.userid=c.userid  WHERE u.is_deleted=0 AND u.role=5 ".$user_cond." GROUP BY c.contentid LIMIT 1";
                    $count_arr=$conn->getQueryValue($count_str); 
                    $res_content->total_view=(count($count_arr)>0)?$count_arr[0]->total_view:0;
                    
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$sno++);                   
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$res_content->topicname);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$res_content->title);
                    switch($res_content->filetypeid)
                    {
                        case '1':$file_type_str='PDF';
                            break;
                        case '2':$file_type_str='MP4';
                            break;   
                        case '3':$file_type_str='MP3';
                            break;
                        default:$file_type_str='';
                            break;
                    }
                    
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$file_type_str);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$res_content->total_view);
                        
                    $count_str="SELECT COUNT(c.userid) as total_view FROM (SELECT userid,contentid FROM `".TABLE_USER_COMPLETE_CONTENT."` WHERE status=2 AND contentid='".$res_content->contentid."' ) c JOIN ".TABLE_USER." u ON u.userid=c.userid  WHERE u.is_deleted=0 AND u.role=5 ".$user_cond." GROUP BY c.contentid LIMIT 1";
                    $count_arr=$conn->getQueryValue($count_str); 
                    $res_content->total_completed=(count($count_arr)>0)?$count_arr[0]->total_view:0;
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$res_content->total_completed);
                        
                }
            }
            
            $objPHPExcel->getActiveSheet()->setTitle('CONTENT REPORT');	        
            $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
            $fpath="../".S3_REPORT_URL;
            $filename="content_report_". uniqid().".xlsx";			 
            $objWriter->save($fpath.$filename);
            
            //S3 UPLOAD
            //S3 LIB
            require (dirname(__FILE__).'/awsfileserver.php'); 

            //S3 OBJ
            $s3=awsfileserver::connectS3();
            awsfileserver::checkupload($s3,S3_BUCKET,S3_REPORT_URL.$filename,dirname(__FILE__)."/../".S3_REPORT_URL.$filename);

            //DELETE AFTER MOVE TO S3
            @unlink(dirname(__FILE__).'/../'.S3_REPORT_URL.$filename);
            
            $obj = new stdClass();
            $obj->key_name=S3_REPORT_URL.$filename;			
            $url=awsfileserver::getS3Url($s3,S3_BUCKET,$obj);	
            
            $returnarr=array("report_url"=>$url);
            
            $conn->message(true,$conn->getMessage($lang,'report','success1'), $returnarr);  
            
        }else{
            $conn->message(false,$conn->getMessage($lang,'report','error1'));
        }
    }catch (PDOException $e) {
        $conn->message(false,$conn->getMessage($lang,'report','error4'));
    }
}


function content_report_rm() {
    global $conn;
    try {
        $lang = "en_us";
        $pageData = $conn->loadJSON($lang);
        //$_SESSION["userid"]=1;
        if(isset($_SESSION["userid"]))
        { 
            $page = escape_str($_REQUEST["page"]);
                        
            $zm_email = escape_str($_REQUEST["zm_email"]);
                        
            $user_cond='';
                            
            if( ($zm_email!='all' && $zm_email!=''))
            {
                $mcond=''; 
                $mcond.=($zm_email!='all' && $zm_email!='')?" AND d.zm_email='".$zm_email."'":'';         
                //AM
                $user_cond=$mcond;
            }else
            {
                //PAN
                $user_cond='';
            }         
            
            require_once dirname(__FILE__) .DIRECTORY_SEPARATOR. 'PHPExcel.php';
            require_once dirname(__FILE__) . DIRECTORY_SEPARATOR.'PHPExcel/Writer/Excel2007.php';
            $objPHPExcel = new PHPExcel();
            $objPHPExcel->setActiveSheetIndex(0);
            
            $row+=2;
            $objPHPExcel->getActiveSheet()->SetCellValue('A'.$row,"RM CONTENT REPORT");
            $objPHPExcel->getActiveSheet()->mergeCells("A".$row.":F".$row);
            $objPHPExcel->getActiveSheet()->getStyle("A".$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle("A".$row)->getFont()->setBold(true);
            $objPHPExcel->getActiveSheet()->getStyle("A".$row)->getFont()->setSize(14);
            
            $cateid = escape_str($_REQUEST["cateid"]);     
            $query_str = "SELECT `cateid`, `catename` FROM  ".TABLE_CATEGORY." WHERE cateid='".$cateid."' LIMIT 1";
            $cat_arr=$conn->getQueryValue($query_str);
            if(!count($cat_arr)>0)
            {
                $conn->message(false,$conn->getMessage($lang,'report','error2'));
            }  
            
            $row++;        
            $objPHPExcel->getActiveSheet()->SetCellValue('A'.$row,"CATEGORY : ".$cat_arr[0]->catename);
            $objPHPExcel->getActiveSheet()->getStyle("A".$row)->getFont()->setBold(true);
            $objPHPExcel->getActiveSheet()->mergeCells("A".$row.":D".$row);
            
            
            $row++;
            $bordercells_arr=array("A".$row,"B".$row,"C".$row,"D".$row,"E".$row,'F'.$row);
            setCellBorder($bordercells_arr,$objPHPExcel);
            $objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(-1);
             //Dashboard
            //Learning
            //Assessment
            //Feedback
            
            $header_arr=array('S.No','Topic', 'Content Title','Content type','No. of RM Viewed','No. of RM Completed');
             
            $i=0;
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('15');
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('45');
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('45');
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('15');
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('30');
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('30');
            
            $i=0;
            foreach($header_arr as $val)
            { 
                $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$val);
            } 
            
            
            //GET ALL CONTENTS OF CATEGORY
            $query_str="SELECT c.assettopicid,ca.cateid,c.title,t.topicname,c.contentid,c.title,f.filetypeid FROM `".TABLE_ASSET_CONTENT."` as c LEFT JOIN ".TABLE_ASSET_TOPIC." as t ON c.assettopicid=t.assettopicid LEFT JOIN ".TABLE_CATEGORY." as ca ON ca.cateid=t.cateid LEFT JOIN ".TABLE_ASSET_FILE." as f ON f.contentid=c.contentid  WHERE ca.cateid ='".$cateid."' AND (c.filter_role='' OR FIND_IN_SET(6,c.filter_role))  AND t.is_deleted='0' AND c.is_deleted='0' GROUP BY c.contentid ORDER BY t.position ASC,t.assettopicid ASC,c.contentid ASC ";
            $contentresult_arr=$conn->getQueryValue($query_str);
            
            if(count($contentresult_arr))
            {
                $sno=1;
                foreach($contentresult_arr as $res_content)
                { 
                    //'#','Topic', 'Content','Content type','DSE Completed'
                    $i=0;
                    $row++;
                    
                    $count_str="SELECT COUNT(c.userid) as total_view FROM (SELECT userid,contentid FROM `".TABLE_USER_COMPLETE_CONTENT."` WHERE  contentid='".$res_content->contentid."' ) c JOIN ".TABLE_USER." u ON u.userid=c.userid JOIN ".DMS_DEALER." as d ON d.rm_email=u.emailid  WHERE u.is_deleted=0 AND u.role=6 ".$user_cond." GROUP BY c.contentid LIMIT 1";
                    $count_arr=$conn->getQueryValue($count_str); 
                    $res_content->total_view=(count($count_arr)>0)?$count_arr[0]->total_view:0;
                    
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$sno++);                   
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$res_content->topicname);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$res_content->title);
                    switch($res_content->filetypeid)
                    {
                        case '1':$file_type_str='PDF';
                            break;
                        case '2':$file_type_str='MP4';
                            break;   
                        case '3':$file_type_str='MP3';
                            break;
                        default:$file_type_str='';
                            break;
                    }
                    
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$file_type_str);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$res_content->total_view);
                        
                    $count_str="SELECT COUNT(c.userid) as total_view FROM (SELECT userid,contentid FROM `".TABLE_USER_COMPLETE_CONTENT."` WHERE status=2 AND contentid='".$res_content->contentid."' ) c JOIN ".TABLE_USER." u ON u.userid=c.userid JOIN ".DMS_DEALER." as d ON d.rm_email=u.emailid WHERE u.is_deleted=0 AND u.role=6 ".$user_cond." GROUP BY c.contentid LIMIT 1";
                    $count_arr=$conn->getQueryValue($count_str); 
                    $res_content->total_completed=(count($count_arr)>0)?$count_arr[0]->total_view:0;
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$res_content->total_completed);
                        
                }
            }
            
            $objPHPExcel->getActiveSheet()->setTitle('CONTENT REPORT');	        
            $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
            $fpath="../".S3_REPORT_URL;
            $filename="content_report_". uniqid().".xlsx";			 
            $objWriter->save($fpath.$filename);
            
            //S3 UPLOAD
            //S3 LIB
            require (dirname(__FILE__).'/awsfileserver.php'); 

            //S3 OBJ
            $s3=awsfileserver::connectS3();
            awsfileserver::checkupload($s3,S3_BUCKET,S3_REPORT_URL.$filename,dirname(__FILE__)."/../".S3_REPORT_URL.$filename);

            //DELETE AFTER MOVE TO S3
            @unlink(dirname(__FILE__).'/../'.S3_REPORT_URL.$filename);
            
            $obj = new stdClass();
            $obj->key_name=S3_REPORT_URL.$filename;			
            $url=awsfileserver::getS3Url($s3,S3_BUCKET,$obj);	
            
            $returnarr=array("report_url"=>$url);
            
            $conn->message(true,$conn->getMessage($lang,'report','success1'), $returnarr);  
            
        }else{
            $conn->message(false,$conn->getMessage($lang,'report','error1'));
        }
    }catch (PDOException $e) {
        $conn->message(false,$conn->getMessage($lang,'report','error4'));
    }
}


function content_report_am() {
    global $conn;
    try {
        $lang = "en_us";
        $pageData = $conn->loadJSON($lang);
        //$_SESSION["userid"]=1;
        if(isset($_SESSION["userid"]))
        { 
            $page = escape_str($_REQUEST["page"]);
                        
            $zm_email = escape_str($_REQUEST["zm_email"]);
            $rm_email = escape_str($_REQUEST["rm_email"]);
                        
            
            $user_cond='';
                            
            if( ($rm_email!='all' && $rm_email!='')||($zm_email!='all' && $zm_email!=''))
            {
                $mcond='';
                        
                $mcond.=($rm_email!='all' && $rm_email!='')?" AND d.rm_email='".$rm_email."'":'';         
                $mcond.=($zm_email!='all' && $zm_email!='')?" AND d.zm_email='".$zm_email."'":'';         
                //AM
                $user_cond=$mcond;
            }else
            {
                //PAN
                $user_cond='';
            }             
                        
            
            require_once dirname(__FILE__) .DIRECTORY_SEPARATOR. 'PHPExcel.php';
            require_once dirname(__FILE__) . DIRECTORY_SEPARATOR.'PHPExcel/Writer/Excel2007.php';
            $objPHPExcel = new PHPExcel();
            $objPHPExcel->setActiveSheetIndex(0);
            
            $row+=2;
            $objPHPExcel->getActiveSheet()->SetCellValue('A'.$row,"AM CONTENT REPORT");
            $objPHPExcel->getActiveSheet()->mergeCells("A".$row.":F".$row);
            $objPHPExcel->getActiveSheet()->getStyle("A".$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle("A".$row)->getFont()->setBold(true);
            $objPHPExcel->getActiveSheet()->getStyle("A".$row)->getFont()->setSize(14);
            
            $cateid = escape_str($_REQUEST["cateid"]);     
            $query_str = "SELECT `cateid`, `catename` FROM  ".TABLE_CATEGORY." WHERE cateid='".$cateid."' LIMIT 1";
            $cat_arr=$conn->getQueryValue($query_str);
            if(!count($cat_arr)>0)
            {
                $conn->message(false,$conn->getMessage($lang,'report','error2'));
            }  
            
            $row++;        
            $objPHPExcel->getActiveSheet()->SetCellValue('A'.$row,"CATEGORY : ".$cat_arr[0]->catename);
            $objPHPExcel->getActiveSheet()->getStyle("A".$row)->getFont()->setBold(true);
            $objPHPExcel->getActiveSheet()->mergeCells("A".$row.":D".$row);
            
            
            $row++;
            $bordercells_arr=array("A".$row,"B".$row,"C".$row,"D".$row,"E".$row,'F'.$row);
            setCellBorder($bordercells_arr,$objPHPExcel);
            $objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(-1);
             //Dashboard
            //Learning
            //Assessment
            //Feedback
            
            $header_arr=array('S.No','Topic', 'Content Title','Content type','No. of AM Viewed','No. of AM Completed');
             
            $i=0;
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('15');
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('45');
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('45');
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('15');
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('30');
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('30');
            
            $i=0;
            foreach($header_arr as $val)
            { 
                $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$val);
            } 
            
            
            //GET ALL CONTENTS OF CATEGORY
            $query_str="SELECT c.assettopicid,ca.cateid,c.title,t.topicname,c.contentid,c.title,f.filetypeid FROM `".TABLE_ASSET_CONTENT."` as c LEFT JOIN ".TABLE_ASSET_TOPIC." as t ON c.assettopicid=t.assettopicid LEFT JOIN ".TABLE_CATEGORY." as ca ON ca.cateid=t.cateid LEFT JOIN ".TABLE_ASSET_FILE." as f ON f.contentid=c.contentid  WHERE ca.cateid ='".$cateid."' AND (c.filter_role='' OR FIND_IN_SET(7,c.filter_role))  AND t.is_deleted='0' AND c.is_deleted='0' GROUP BY c.contentid ORDER BY t.position ASC,t.assettopicid ASC,c.contentid ASC ";
            $contentresult_arr=$conn->getQueryValue($query_str);
            
            if(count($contentresult_arr))
            {
                $sno=1;
                foreach($contentresult_arr as $res_content)
                { 
                    //'#','Topic', 'Content','Content type','DSE Completed'
                    $i=0;
                    $row++;
                    
                    $count_str="SELECT COUNT(c.userid) as total_view FROM (SELECT userid,contentid FROM `".TABLE_USER_COMPLETE_CONTENT."` WHERE  contentid='".$res_content->contentid."' ) c JOIN ".TABLE_USER." u ON u.userid=c.userid JOIN ".DMS_DEALER." as d ON d.am_email=u.emailid  WHERE u.is_deleted=0 AND u.role=7 ".$user_cond." GROUP BY c.contentid LIMIT 1";
                    $count_arr=$conn->getQueryValue($count_str); 
                    $res_content->total_view=(count($count_arr)>0)?$count_arr[0]->total_view:0;
                    
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$sno++);                   
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$res_content->topicname);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$res_content->title);
                    switch($res_content->filetypeid)
                    {
                        case '1':$file_type_str='PDF';
                            break;
                        case '2':$file_type_str='MP4';
                            break;   
                        case '3':$file_type_str='MP3';
                            break;
                        default:$file_type_str='';
                            break;
                    }
                    
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$file_type_str);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$res_content->total_view);
                        
                    $count_str="SELECT COUNT(c.userid) as total_view FROM (SELECT userid,contentid FROM `".TABLE_USER_COMPLETE_CONTENT."` WHERE status=2 AND contentid='".$res_content->contentid."' ) c JOIN ".TABLE_USER." u ON u.userid=c.userid JOIN ".DMS_DEALER." as d ON d.am_email=u.emailid WHERE u.is_deleted=0 AND u.role=7 ".$user_cond." GROUP BY c.contentid LIMIT 1";
                    $count_arr=$conn->getQueryValue($count_str); 
                    $res_content->total_completed=(count($count_arr)>0)?$count_arr[0]->total_view:0;
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$res_content->total_completed);
                        
                }
            }
            
            $objPHPExcel->getActiveSheet()->setTitle('CONTENT REPORT');	        
            $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
            $fpath="../".S3_REPORT_URL;
            $filename="content_report_". uniqid().".xlsx";			 
            $objWriter->save($fpath.$filename);
            
            //S3 UPLOAD
            //S3 LIB
            require (dirname(__FILE__).'/awsfileserver.php'); 

            //S3 OBJ
            $s3=awsfileserver::connectS3();
            awsfileserver::checkupload($s3,S3_BUCKET,S3_REPORT_URL.$filename,dirname(__FILE__)."/../".S3_REPORT_URL.$filename);

            //DELETE AFTER MOVE TO S3
            @unlink(dirname(__FILE__).'/../'.S3_REPORT_URL.$filename);
            
            $obj = new stdClass();
            $obj->key_name=S3_REPORT_URL.$filename;			
            $url=awsfileserver::getS3Url($s3,S3_BUCKET,$obj);	
            
            $returnarr=array("report_url"=>$url);
            
            $conn->message(true,$conn->getMessage($lang,'report','success1'), $returnarr);  
            
        }else{
            $conn->message(false,$conn->getMessage($lang,'report','error1'));
        }
    }catch (PDOException $e) {
        $conn->message(false,$conn->getMessage($lang,'report','error4'));
    }
}


// content_report_list_zm
function content_report_list_zm() {
    global $conn;
    try {
        $lang = "en_us";
        $pageData = $conn->loadJSON($lang);
        //$_SESSION["userid"]=1;
        if(isset($_SESSION["userid"]))
        { 
            $page = escape_str($_REQUEST["page"]);
                        
            $user_cond='';
                        
            
            require_once dirname(__FILE__) .DIRECTORY_SEPARATOR. 'PHPExcel.php';
            require_once dirname(__FILE__) . DIRECTORY_SEPARATOR.'PHPExcel/Writer/Excel2007.php';
            $objPHPExcel = new PHPExcel();
            $objPHPExcel->setActiveSheetIndex(0);
            
            $row+=2;
            $objPHPExcel->getActiveSheet()->SetCellValue('A'.$row,"ZM CONTENT REPORT");
            $objPHPExcel->getActiveSheet()->mergeCells("A".$row.":L".$row);
            $objPHPExcel->getActiveSheet()->getStyle("A".$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle("A".$row)->getFont()->setBold(true);
            $objPHPExcel->getActiveSheet()->getStyle("A".$row)->getFont()->setSize(14);
            
                        
            $assettopicid = escape_str($_REQUEST["assettopicid"]);        
            $contentid = escape_str($_REQUEST["contentid"]);              
            //CONTENT
            $query_str="SELECT c.assettopicid,ca.cateid,c.title,t.topicname,c.contentid,c.title FROM `".TABLE_ASSET_CONTENT."` as c LEFT JOIN ".TABLE_ASSET_TOPIC." as t ON c.assettopicid=t.assettopicid LEFT JOIN ".TABLE_CATEGORY." as ca ON ca.cateid=t.cateid LEFT JOIN ".TABLE_ASSET_FILE." as f ON f.contentid=c.contentid  WHERE c.contentid ='".$contentid."'  AND c.assettopicid='".$assettopicid."' AND t.is_deleted='0' AND c.is_deleted='0' GROUP BY c.contentid LIMIT 1 ";
            $content_arr=$conn->getQueryValue($query_str);
            if(!count($content_arr)>0)
            {
                $conn->message(false,$conn->getMessage($lang,'report','error2'));
            } 
            
            
            $row++;        
            $objPHPExcel->getActiveSheet()->SetCellValue('A'.$row,"TOPIC : ".$content_arr[0]->topicname);
            $objPHPExcel->getActiveSheet()->getStyle("A".$row)->getFont()->setBold(true);
            $objPHPExcel->getActiveSheet()->mergeCells("A".$row.":D".$row);
            
            $row++;        
            $objPHPExcel->getActiveSheet()->SetCellValue('A'.$row,"CONTENT : ".$content_arr[0]->title);
            $objPHPExcel->getActiveSheet()->getStyle("A".$row)->getFont()->setBold(true);
            $objPHPExcel->getActiveSheet()->mergeCells("A".$row.":D".$row);
           
            $row++;
            $bordercells_arr=array("A".$row,"B".$row,"C".$row,"D".$row,"E".$row,"F".$row,"G".$row);
            setCellBorder($bordercells_arr,$objPHPExcel);
            $objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(-1);           
            
            $header_arr=array('S.No','EMP CODE', 'Name','Mobile','Email','Status','Completed on');
             
            $i=0;
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('15');
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('15');
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('30');
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('15');
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('25');
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('15');
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('20'); 
            
            $i=0;
            foreach($header_arr as $val)
            { 
                $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$val);
            } 
            
            
            //GET ALL USER
            $content_user_str="SELECT u.emp_code,u.fname,u.lname,u.mobileno,u.emailid,c.completeddate,CASE WHEN c.status=0 THEN 'Started' WHEN c.status=1 THEN 'In progress' WHEN c.status=2 THEN 'Completed' ELSE 'In progress' END as content_status ,c.status as cstatus FROM (SELECT id,userid,contentid,status,completeddate,modified_on  FROM `".TABLE_USER_COMPLETE_CONTENT."` WHERE  contentid='".$contentid."' AND assettopicid='".$assettopicid."') c JOIN ".TABLE_USER." u ON u.userid=c.userid    WHERE u.role=4 ".$user_cond." GROUP BY c.userid ORDER BY c.status DESC,c.modified_on ASC ";
            $contentresult_arr=$conn->getQueryValue($content_user_str);
            
            if(count($contentresult_arr))
            {
                $sno=1;
                foreach($contentresult_arr as $res_user)
                {  
                    //array('#','EMP CODE', 'Name','Mobile','Email','Status','Completed on');
                    $i=0;
                    $row++;
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$sno++);
                    $objPHPExcel->getActiveSheet()->getStyle('B'.$row)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$res_user->emp_code);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,trim($res_user->fname." ".$res_user->lname));
                        
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$res_user->mobileno);  
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$res_user->emailid);  
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$res_user->content_status);     
                   
                    switch($res_user->cstatus)
                    {
                        case '0': $color='D3D3D3';break;
                        case '1': $color='FFA500'; break;
                        case '2': $color='008000';break;
                        default:$color='D3D3D3';break; 
                    }
                    $objPHPExcel->getActiveSheet()->getStyle('F'.$row)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB($color);     
                    $completeddate=($res_user->completeddate!='0000-00-00 00:00:00')?date('d-m-Y',strtotime($res_user->completeddate)):'';
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$completeddate);
                        
                }
            }
            
            $objPHPExcel->getActiveSheet()->setTitle('CONTENT REPORT');	        
            $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
            $fpath="../".S3_REPORT_URL;
            $filename="user_content_report_". uniqid().".xlsx";			 
            $objWriter->save($fpath.$filename);
            
            //S3 UPLOAD
            //S3 LIB
            require (dirname(__FILE__).'/awsfileserver.php'); 

            //S3 OBJ
            $s3=awsfileserver::connectS3();
            awsfileserver::checkupload($s3,S3_BUCKET,S3_REPORT_URL.$filename,dirname(__FILE__)."/../".S3_REPORT_URL.$filename);

            //DELETE AFTER MOVE TO S3
            @unlink(dirname(__FILE__).'/../'.S3_REPORT_URL.$filename);
            
            $obj = new stdClass();
            $obj->key_name=S3_REPORT_URL.$filename;			
            $url=awsfileserver::getS3Url($s3,S3_BUCKET,$obj);	
            
            $returnarr=array("report_url"=>$url);
            
            $conn->message(true,$conn->getMessage($lang,'report','success1'), $returnarr);  
            
        }else{
            $conn->message(false,$conn->getMessage($lang,'report','error1'));
        }
    }catch (PDOException $e) {
        $conn->message(false,$conn->getMessage($lang,'report','error4'));
    }
}


// content_report_list_trainer
function content_report_list_trainer() {
    global $conn;
    try {
        $lang = "en_us";
        $pageData = $conn->loadJSON($lang);
        //$_SESSION["userid"]=1;
        if(isset($_SESSION["userid"]))
        { 
            $page = escape_str($_REQUEST["page"]);
                        
            $user_cond='';
                        
            
            require_once dirname(__FILE__) .DIRECTORY_SEPARATOR. 'PHPExcel.php';
            require_once dirname(__FILE__) . DIRECTORY_SEPARATOR.'PHPExcel/Writer/Excel2007.php';
            $objPHPExcel = new PHPExcel();
            $objPHPExcel->setActiveSheetIndex(0);
            
            $row+=2;
            $objPHPExcel->getActiveSheet()->SetCellValue('A'.$row,"TRAINER CONTENT REPORT");
            $objPHPExcel->getActiveSheet()->mergeCells("A".$row.":L".$row);
            $objPHPExcel->getActiveSheet()->getStyle("A".$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle("A".$row)->getFont()->setBold(true);
            $objPHPExcel->getActiveSheet()->getStyle("A".$row)->getFont()->setSize(14);
            
                        
            $assettopicid = escape_str($_REQUEST["assettopicid"]);        
            $contentid = escape_str($_REQUEST["contentid"]);              
            //CONTENT
            $query_str="SELECT c.assettopicid,ca.cateid,c.title,t.topicname,c.contentid,c.title FROM `".TABLE_ASSET_CONTENT."` as c LEFT JOIN ".TABLE_ASSET_TOPIC." as t ON c.assettopicid=t.assettopicid LEFT JOIN ".TABLE_CATEGORY." as ca ON ca.cateid=t.cateid LEFT JOIN ".TABLE_ASSET_FILE." as f ON f.contentid=c.contentid  WHERE c.contentid ='".$contentid."'  AND c.assettopicid='".$assettopicid."' AND t.is_deleted='0' AND c.is_deleted='0' GROUP BY c.contentid LIMIT 1 ";
            $content_arr=$conn->getQueryValue($query_str);
            if(!count($content_arr)>0)
            {
                $conn->message(false,$conn->getMessage($lang,'report','error2'));
            } 
            
            
            $row++;        
            $objPHPExcel->getActiveSheet()->SetCellValue('A'.$row,"TOPIC : ".$content_arr[0]->topicname);
            $objPHPExcel->getActiveSheet()->getStyle("A".$row)->getFont()->setBold(true);
            $objPHPExcel->getActiveSheet()->mergeCells("A".$row.":D".$row);
            
            $row++;        
            $objPHPExcel->getActiveSheet()->SetCellValue('A'.$row,"CONTENT : ".$content_arr[0]->title);
            $objPHPExcel->getActiveSheet()->getStyle("A".$row)->getFont()->setBold(true);
            $objPHPExcel->getActiveSheet()->mergeCells("A".$row.":D".$row);
           
            $row++;
            $bordercells_arr=array("A".$row,"B".$row,"C".$row,"D".$row,"E".$row,"F".$row,"G".$row);
            setCellBorder($bordercells_arr,$objPHPExcel);
            $objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(-1);           
            
            $header_arr=array('S.No','EMP CODE', 'Name','Mobile','Email','Status','Completed on');
             
            $i=0;
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('15');
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('15');
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('30');
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('15');
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('25');
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('15');
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('20'); 
            
            $i=0;
            foreach($header_arr as $val)
            { 
                $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$val);
            } 
            
            
            //GET ALL USER
            $content_user_str="SELECT u.emp_code,u.fname,u.lname,u.mobileno,u.emailid,c.completeddate,CASE WHEN c.status=0 THEN 'Started' WHEN c.status=1 THEN 'In progress' WHEN c.status=2 THEN 'Completed' ELSE 'In progress' END as content_status ,c.status as cstatus FROM (SELECT id,userid,contentid,status,completeddate,modified_on  FROM `".TABLE_USER_COMPLETE_CONTENT."` WHERE  contentid='".$contentid."' AND assettopicid='".$assettopicid."') c JOIN ".TABLE_USER." u ON u.userid=c.userid  WHERE u.role=5 ".$user_cond." GROUP BY c.userid ORDER BY c.status DESC,c.modified_on ASC ";
            $contentresult_arr=$conn->getQueryValue($content_user_str);
            
            if(count($contentresult_arr))
            {
                $sno=1;
                foreach($contentresult_arr as $res_user)
                {  
                    //array('#','EMP CODE', 'Name','Mobile','Email','Status','Completed on');
                    $i=0;
                    $row++;
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$sno++);
                    $objPHPExcel->getActiveSheet()->getStyle('B'.$row)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$res_user->emp_code);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,trim($res_user->fname." ".$res_user->lname));
                        
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$res_user->mobileno);  
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$res_user->emailid);  
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$res_user->content_status);     
                   
                    switch($res_user->cstatus)
                    {
                        case '0': $color='D3D3D3';break;
                        case '1': $color='FFA500'; break;
                        case '2': $color='008000';break;
                        default:$color='D3D3D3';break; 
                    }
                    $objPHPExcel->getActiveSheet()->getStyle('F'.$row)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB($color); 
                    $completeddate=($res_user->completeddate!='0000-00-00 00:00:00')?date('d-m-Y',strtotime($res_user->completeddate)):'';
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$completeddate);
                        
                }
            }
            
            $objPHPExcel->getActiveSheet()->setTitle('CONTENT REPORT');	        
            $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
            $fpath="../".S3_REPORT_URL;
            $filename="user_content_report_". uniqid().".xlsx";			 
            $objWriter->save($fpath.$filename);
            
            //S3 UPLOAD
            //S3 LIB
            require (dirname(__FILE__).'/awsfileserver.php'); 

            //S3 OBJ
            $s3=awsfileserver::connectS3();
            awsfileserver::checkupload($s3,S3_BUCKET,S3_REPORT_URL.$filename,dirname(__FILE__)."/../".S3_REPORT_URL.$filename);

            //DELETE AFTER MOVE TO S3
            @unlink(dirname(__FILE__).'/../'.S3_REPORT_URL.$filename);
            
            $obj = new stdClass();
            $obj->key_name=S3_REPORT_URL.$filename;			
            $url=awsfileserver::getS3Url($s3,S3_BUCKET,$obj);	
            
            $returnarr=array("report_url"=>$url);
            
            $conn->message(true,$conn->getMessage($lang,'report','success1'), $returnarr);  
            
        }else{
            $conn->message(false,$conn->getMessage($lang,'report','error1'));
        }
    }catch (PDOException $e) {
        $conn->message(false,$conn->getMessage($lang,'report','error4'));
    }
}

// content_report_list_rm
function content_report_list_rm() {
    global $conn;
    try {
        $lang = "en_us";
        $pageData = $conn->loadJSON($lang);
        //$_SESSION["userid"]=1;
        if(isset($_SESSION["userid"]))
        { 
            $page = escape_str($_REQUEST["page"]);
                        
            $zm_email = escape_str($_REQUEST["zm_email"]);
                        
            $user_cond='';
                            
            if( ($zm_email!='all' && $zm_email!=''))
            {
                $mcond='';
                        
                $mcond.=($zm_email!='all' && $zm_email!='')?" AND d.zm_email='".$zm_email."'":'';         
                //AM
                $user_cond=$mcond;
            }else
            {
                //PAN
                $user_cond='';
            } 
                        
            
            require_once dirname(__FILE__) .DIRECTORY_SEPARATOR. 'PHPExcel.php';
            require_once dirname(__FILE__) . DIRECTORY_SEPARATOR.'PHPExcel/Writer/Excel2007.php';
            $objPHPExcel = new PHPExcel();
            $objPHPExcel->setActiveSheetIndex(0);
            
            $row+=2;
            $objPHPExcel->getActiveSheet()->SetCellValue('A'.$row,"RM CONTENT REPORT");
            $objPHPExcel->getActiveSheet()->mergeCells("A".$row.":L".$row);
            $objPHPExcel->getActiveSheet()->getStyle("A".$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle("A".$row)->getFont()->setBold(true);
            $objPHPExcel->getActiveSheet()->getStyle("A".$row)->getFont()->setSize(14);
            
                        
            $assettopicid = escape_str($_REQUEST["assettopicid"]);        
            $contentid = escape_str($_REQUEST["contentid"]);              
            //CONTENT
            $query_str="SELECT c.assettopicid,ca.cateid,c.title,t.topicname,c.contentid,c.title FROM `".TABLE_ASSET_CONTENT."` as c LEFT JOIN ".TABLE_ASSET_TOPIC." as t ON c.assettopicid=t.assettopicid LEFT JOIN ".TABLE_CATEGORY." as ca ON ca.cateid=t.cateid LEFT JOIN ".TABLE_ASSET_FILE." as f ON f.contentid=c.contentid  WHERE c.contentid ='".$contentid."'  AND c.assettopicid='".$assettopicid."' AND t.is_deleted='0' AND c.is_deleted='0' GROUP BY c.contentid LIMIT 1 ";
            $content_arr=$conn->getQueryValue($query_str);
            if(!count($content_arr)>0)
            {
                $conn->message(false,$conn->getMessage($lang,'report','error2'));
            } 
            
            
            $row++;        
            $objPHPExcel->getActiveSheet()->SetCellValue('A'.$row,"TOPIC : ".$content_arr[0]->topicname);
            $objPHPExcel->getActiveSheet()->getStyle("A".$row)->getFont()->setBold(true);
            $objPHPExcel->getActiveSheet()->mergeCells("A".$row.":D".$row);
            
            $row++;        
            $objPHPExcel->getActiveSheet()->SetCellValue('A'.$row,"CONTENT : ".$content_arr[0]->title);
            $objPHPExcel->getActiveSheet()->getStyle("A".$row)->getFont()->setBold(true);
            $objPHPExcel->getActiveSheet()->mergeCells("A".$row.":D".$row);
           
            $row++;
            $bordercells_arr=array("A".$row,"B".$row,"C".$row,"D".$row,"E".$row,"F".$row,"G".$row);
            setCellBorder($bordercells_arr,$objPHPExcel);
            $objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(-1);           
            
            $header_arr=array('S.No','EMP CODE', 'Name','Mobile','Email','Status','Completed on');
             
            $i=0;
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('15');
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('15');
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('30');
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('15');
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('25');
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('15');
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('20'); 
            
            $i=0;
            foreach($header_arr as $val)
            { 
                $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$val);
            } 
            
            
            //GET ALL USER
            $content_user_str="SELECT u.emp_code,u.fname,u.lname,u.mobileno,u.emailid,c.completeddate,CASE WHEN c.status=0 THEN 'Started' WHEN c.status=1 THEN 'In progress' WHEN c.status=2 THEN 'Completed' ELSE 'In progress' END as content_status ,c.status as cstatus FROM (SELECT id,userid,contentid,status,completeddate,modified_on  FROM `".TABLE_USER_COMPLETE_CONTENT."` WHERE  contentid='".$contentid."' AND assettopicid='".$assettopicid."') c JOIN ".TABLE_USER." u ON u.userid=c.userid  JOIN ".DMS_DEALER." as d ON d.rm_email=u.emailid WHERE u.role=6 ".$user_cond." GROUP BY c.userid ORDER BY c.status DESC,c.modified_on ASC ";
            $contentresult_arr=$conn->getQueryValue($content_user_str);
            
            if(count($contentresult_arr))
            {
                $sno=1;
                foreach($contentresult_arr as $res_user)
                {  
                    //array('#','EMP CODE', 'Name','Mobile','Email','Status','Completed on');
                    $i=0;
                    $row++;
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$sno++);
                    $objPHPExcel->getActiveSheet()->getStyle('B'.$row)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$res_user->emp_code);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,trim($res_user->fname." ".$res_user->lname));
                        
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$res_user->mobileno);  
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$res_user->emailid);  
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$res_user->content_status);     
                   
                    switch($res_user->cstatus)
                    {
                        case '0': $color='D3D3D3';break;
                        case '1': $color='FFA500'; break;
                        case '2': $color='008000';break;
                        default:$color='D3D3D3';break; 
                    }
                    $objPHPExcel->getActiveSheet()->getStyle('F'.$row)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB($color);
                        
                    $completeddate=($res_user->completeddate!='0000-00-00 00:00:00')?date('d-m-Y',strtotime($res_user->completeddate)):'';
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$completeddate);
                        
                }
            }
            
            $objPHPExcel->getActiveSheet()->setTitle('CONTENT REPORT');	        
            $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
            $fpath="../".S3_REPORT_URL;
            $filename="user_content_report_". uniqid().".xlsx";			 
            $objWriter->save($fpath.$filename);
            
            //S3 UPLOAD
            //S3 LIB
            require (dirname(__FILE__).'/awsfileserver.php'); 

            //S3 OBJ
            $s3=awsfileserver::connectS3();
            awsfileserver::checkupload($s3,S3_BUCKET,S3_REPORT_URL.$filename,dirname(__FILE__)."/../".S3_REPORT_URL.$filename);

            //DELETE AFTER MOVE TO S3
            @unlink(dirname(__FILE__).'/../'.S3_REPORT_URL.$filename);
            
            $obj = new stdClass();
            $obj->key_name=S3_REPORT_URL.$filename;			
            $url=awsfileserver::getS3Url($s3,S3_BUCKET,$obj);	
            
            $returnarr=array("report_url"=>$url);
            
            $conn->message(true,$conn->getMessage($lang,'report','success1'), $returnarr);  
            
        }else{
            $conn->message(false,$conn->getMessage($lang,'report','error1'));
        }
    }catch (PDOException $e) {
        $conn->message(false,$conn->getMessage($lang,'report','error4'));
    }
}


// content_report_list_am
function content_report_list_am() {
    global $conn;
    try {
        $lang = "en_us";
        $pageData = $conn->loadJSON($lang);
        //$_SESSION["userid"]=1;
        if(isset($_SESSION["userid"]))
        { 
            $page = escape_str($_REQUEST["page"]);
                        
            $zm_email = escape_str($_REQUEST["zm_email"]);
            $rm_email = escape_str($_REQUEST["rm_email"]);
                        
            
            $user_cond='';
                            
            if( ($rm_email!='all' && $rm_email!='')||($zm_email!='all' && $zm_email!=''))
            {
                $mcond='';
                        
                $mcond.=($rm_email!='all' && $rm_email!='')?" AND d.rm_email='".$rm_email."'":'';         
                $mcond.=($zm_email!='all' && $zm_email!='')?" AND d.zm_email='".$zm_email."'":'';         
                //AM
                $user_cond=$mcond;
            }else
            {
                //PAN
                $user_cond='';
            } 
                        
            
            require_once dirname(__FILE__) .DIRECTORY_SEPARATOR. 'PHPExcel.php';
            require_once dirname(__FILE__) . DIRECTORY_SEPARATOR.'PHPExcel/Writer/Excel2007.php';
            $objPHPExcel = new PHPExcel();
            $objPHPExcel->setActiveSheetIndex(0);
            
            $row+=2;
            $objPHPExcel->getActiveSheet()->SetCellValue('A'.$row,"AM CONTENT REPORT");
            $objPHPExcel->getActiveSheet()->mergeCells("A".$row.":L".$row);
            $objPHPExcel->getActiveSheet()->getStyle("A".$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle("A".$row)->getFont()->setBold(true);
            $objPHPExcel->getActiveSheet()->getStyle("A".$row)->getFont()->setSize(14);
            
                        
            $assettopicid = escape_str($_REQUEST["assettopicid"]);        
            $contentid = escape_str($_REQUEST["contentid"]);              
            //CONTENT
            $query_str="SELECT c.assettopicid,ca.cateid,c.title,t.topicname,c.contentid,c.title FROM `".TABLE_ASSET_CONTENT."` as c LEFT JOIN ".TABLE_ASSET_TOPIC." as t ON c.assettopicid=t.assettopicid LEFT JOIN ".TABLE_CATEGORY." as ca ON ca.cateid=t.cateid LEFT JOIN ".TABLE_ASSET_FILE." as f ON f.contentid=c.contentid  WHERE c.contentid ='".$contentid."'  AND c.assettopicid='".$assettopicid."' AND t.is_deleted='0' AND c.is_deleted='0' GROUP BY c.contentid LIMIT 1 ";
            $content_arr=$conn->getQueryValue($query_str);
            if(!count($content_arr)>0)
            {
                $conn->message(false,$conn->getMessage($lang,'report','error2'));
            } 
            
            
            $row++;        
            $objPHPExcel->getActiveSheet()->SetCellValue('A'.$row,"TOPIC : ".$content_arr[0]->topicname);
            $objPHPExcel->getActiveSheet()->getStyle("A".$row)->getFont()->setBold(true);
            $objPHPExcel->getActiveSheet()->mergeCells("A".$row.":D".$row);
            
            $row++;        
            $objPHPExcel->getActiveSheet()->SetCellValue('A'.$row,"CONTENT : ".$content_arr[0]->title);
            $objPHPExcel->getActiveSheet()->getStyle("A".$row)->getFont()->setBold(true);
            $objPHPExcel->getActiveSheet()->mergeCells("A".$row.":D".$row);
           
            $row++;
            $bordercells_arr=array("A".$row,"B".$row,"C".$row,"D".$row,"E".$row,"F".$row,"G".$row);
            setCellBorder($bordercells_arr,$objPHPExcel);
            $objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(-1);           
            
            $header_arr=array('S.No','EMP CODE', 'Name','Mobile','Email','Status','Completed on');
             
            $i=0;
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('15');
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('15');
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('30');
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('15');
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('25');
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('15');
            $objPHPExcel->getActiveSheet()->getColumnDimension(getColumnFromNumber($i++))->setWidth('20'); 
            
            $i=0;
            foreach($header_arr as $val)
            { 
                $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$val);
            } 
            
            
            //GET ALL USER
            $content_user_str="SELECT u.emp_code,u.fname,u.lname,u.mobileno,u.emailid,u.dealer_code,u.segment,c.completeddate,d.dealer_name,d.zone,d.`zm_name`,d.`rm_name`,d.`am_name`,CASE WHEN c.status=0 THEN 'Started' WHEN c.status=1 THEN 'In progress' WHEN c.status=2 THEN 'Completed' ELSE 'In progress' END as content_status ,c.status as cstatus FROM (SELECT id,userid,contentid,status,completeddate,modified_on  FROM `".TABLE_USER_COMPLETE_CONTENT."` WHERE  contentid='".$contentid."' AND assettopicid='".$assettopicid."') c JOIN ".TABLE_USER." u ON u.userid=c.userid  JOIN ".DMS_DEALER." as d ON d.am_email=u.emailid WHERE u.role=7 ".$user_cond." GROUP BY c.userid ORDER BY c.status DESC,c.modified_on ASC ";
            $contentresult_arr=$conn->getQueryValue($content_user_str);
            
            if(count($contentresult_arr))
            {
                $sno=1;
                foreach($contentresult_arr as $res_user)
                {  
                    //array('#','EMP CODE', 'Name','Mobile','Email','Status','Completed on');
                    $i=0;
                    $row++;
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$sno++);
                    $objPHPExcel->getActiveSheet()->getStyle('B'.$row)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$res_user->emp_code);
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,trim($res_user->fname." ".$res_user->lname));
                        
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$res_user->mobileno);  
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$res_user->emailid);  
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$res_user->content_status);     
                   
                    switch($res_user->cstatus)
                    {
                        case '0': $color='D3D3D3';break;
                        case '1': $color='FFA500'; break;
                        case '2': $color='008000';break;
                        default:$color='D3D3D3';break; 
                    }
                    $objPHPExcel->getActiveSheet()->getStyle('F'.$row)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB($color);
                        
                    $completeddate=($res_user->completeddate!='0000-00-00 00:00:00')?date('d-m-Y',strtotime($res_user->completeddate)):'';
                    $objPHPExcel->getActiveSheet()->SetCellValue(getColumnFromNumber($i++).$row,$completeddate);
                        
                }
            }
            
            $objPHPExcel->getActiveSheet()->setTitle('CONTENT REPORT');	        
            $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
            $fpath="../".S3_REPORT_URL;
            $filename="user_content_report_". uniqid().".xlsx";			 
            $objWriter->save($fpath.$filename);
            
            //S3 UPLOAD
            //S3 LIB
            require (dirname(__FILE__).'/awsfileserver.php'); 

            //S3 OBJ
            $s3=awsfileserver::connectS3();
            awsfileserver::checkupload($s3,S3_BUCKET,S3_REPORT_URL.$filename,dirname(__FILE__)."/../".S3_REPORT_URL.$filename);

            //DELETE AFTER MOVE TO S3
            @unlink(dirname(__FILE__).'/../'.S3_REPORT_URL.$filename);
            
            $obj = new stdClass();
            $obj->key_name=S3_REPORT_URL.$filename;			
            $url=awsfileserver::getS3Url($s3,S3_BUCKET,$obj);	
            
            $returnarr=array("report_url"=>$url);
            
            $conn->message(true,$conn->getMessage($lang,'report','success1'), $returnarr);  
            
        }else{
            $conn->message(false,$conn->getMessage($lang,'report','error1'));
        }
    }catch (PDOException $e) {
        $conn->message(false,$conn->getMessage($lang,'report','error4'));
    }
}
?>