opensourcecode/Hyper-M_Admin_Portal/services/report_content.php

<?php

/* * **************************
 * File : report_content.php
 * Author : Cygnusa
 * @2020
 * ************************** */
// included in report.php 
function content_report_v2() {
    global $conn;
    try {
        $lang = "en_us";
        $pageData = $conn->loadJSON($lang);
        $arr=array('report'=>array(),'total'=>array('total_view'=>0,'total_completed'=>0));                   
        if(isset($_SESSION["userid"]))
        { 
                            
            $cateid = escape_str($_REQUEST["cateid"]);                           
            $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=" AND u.dealer_code IN (SELECT dealer_code FROM ".DMS_DEALER." WHERE is_deleted='0' ".$mcond.")";
                $user_cond=$mcond;
            }else
            {
                //PAN
                $user_cond='';
            }
            
            $pn = escape_str($_REQUEST["pn"]);
            $nr = escape_str($_REQUEST["nr"]);
            $key = escape_str($_REQUEST["key"]);
        
            //CATEGORY
            $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'));
            }             
            
            //GET ALL CONTENTS OF CATEGORY
            $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 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);
            
            $total_view=0;
            $total_completed=0;
            if(count($contentresult_arr))
            {
                foreach($contentresult_arr as $res_content)
                {
                    $count_str="SELECT COUNT(c.userid) as total_view FROM (SELECT userid,contentid FROM `".TABLE_USER_COMPLETE_CONTENT."` WHERE contentid='".$res_content->contentid."' AND assettopicid='".$res_content->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 u.is_deleted=0 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;
                    $total_view+=$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."' AND assettopicid='".$res_content->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 u.is_deleted=0 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;
                    $total_completed+=$res_content->total_completed;
                    //$res_content->total_query=$count_str;
                    $data_arr[]=$res_content;
                }                            
                $arr['report'] = $data_arr; 
                $arr['total'] = array('total_view'=>$total_view,'total_completed'=>$total_completed);
            }
            
            
            $conn->message(true,$conn->getMessage($lang,'report','success1'), $arr);  
        }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);
                            
        if(isset($_SESSION["userid"]))
        { 
                            
            $assettopicid = escape_str($_REQUEST["assettopicid"]);        
            $contentid = escape_str($_REQUEST["contentid"]);  
            $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=" AND u.dealer_code IN (SELECT dealer_code FROM ".DMS_DEALER." WHERE is_deleted='0' ".$mcond.")";
                $user_cond=$mcond;
            }else
            {
                //PAN
                $user_cond='';
            }
            
            $pn = escape_str($_REQUEST["pn"]);
            $nr = escape_str($_REQUEST["nr"]);
            $key = escape_str($_REQUEST["key"]);
        
            //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'));
            } 
            
            $arr['content'] =   $content_arr;     
            
            //GET ALL USER
            $content_user_str="SELECT SQL_CALC_FOUND_ROWS u.smpin,u.fname,u.lname,u.mobileno,u.dealer_code,d.dealer_name,d.zone,d.`zm_name`,d.`rm_name`,d.`am_name`,c.status, 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.completeddate  FROM (SELECT id,userid,contentid,status,completeddate 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   e.is_current=1 AND u.role>7 AND u.role<14 ".$user_cond." GROUP BY c.userid ORDER BY c.status DESC,c.id ASC ";
            // Pagination
			if (!empty($pn) and !empty($nr)) {
				// Navigation
    			$content_user_str.= " LIMIT ".(($pn - 1) *  $nr).", ".$nr;
			}
            $query = $conn->query($content_user_str);
            
            $data_arr=array();
            if($query->rowCount()>0)
            {
                $data_arr = $query->fetchAll(PDO::FETCH_OBJ);
            }
            $arr['report'] = $data_arr; 
        
         
            // Total records
			$rows_query = $conn->query("select FOUND_ROWS() as total_records");
			$rows_result = $rows_query->fetchAll(PDO::FETCH_OBJ);
			$total_records = $rows_result[0]->total_records;            
            $arr['total_records'] = $total_records;
            $conn->message(true,$conn->getMessage($lang,'report','success1'), $arr);  
        }else{
            $conn->message(false,$conn->getMessage($lang,'report','error1'));
        }
    }catch (PDOException $e) {
        $conn->message(false,$conn->getMessage($lang,'report','error4'));
    }
}

        
// content_report_zm
function content_report_zm() {
    global $conn;
    try {
        $lang = "en_us";
        $pageData = $conn->loadJSON($lang);
        $arr=array('report'=>array(),'total'=>array('total_view'=>0,'total_completed'=>0));                   
        if(isset($_SESSION["userid"]))
        { 
                            
            $cateid = escape_str($_REQUEST["cateid"]);                           
                   
            $user_cond='';
                   
            $pn = escape_str($_REQUEST["pn"]);
            $nr = escape_str($_REQUEST["nr"]);
            $key = escape_str($_REQUEST["key"]);
        
            //CATEGORY
            $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'));
            }             
             
            //GET ALL CONTENTS OF CATEGORY
            $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 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);
            
            $total_view=0;
            $total_completed=0;
            if(count($contentresult_arr))
            {
                foreach($contentresult_arr as $res_content)
                {
                    $count_str="SELECT COUNT(c.userid) as total_view FROM (SELECT userid,contentid  FROM `".TABLE_USER_COMPLETE_CONTENT."` WHERE contentid='".$res_content->contentid."' AND assettopicid='".$res_content->assettopicid."') c JOIN ".TABLE_USER." u ON u.userid=c.userid  WHERE u.role=4 AND u.is_deleted=0  ".$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;
                    $total_view+=$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."' AND assettopicid='".$res_content->assettopicid."') c JOIN ".TABLE_USER." u ON u.userid=c.userid WHERE u.role=4 AND u.is_deleted=0  ".$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;
                    $total_completed+=$res_content->total_completed;
                    //$res_content->total_query=$count_str;
                    $data_arr[]=$res_content;
                }                            
                $arr['report'] = $data_arr; 
                $arr['total'] = array('total_view'=>$total_view,'total_completed'=>$total_completed);
            }
            
            
            $conn->message(true,$conn->getMessage($lang,'report','success1'), $arr);  
        }else{
            $conn->message(false,$conn->getMessage($lang,'report','error1'));
        }
    }catch (PDOException $e) {
        $conn->message(false,$conn->getMessage($lang,'report','error4'));
    }
}

// content_report_trainer
function content_report_trainer() {
    global $conn;
    try {
        $lang = "en_us";
        $pageData = $conn->loadJSON($lang);
        $arr=array('report'=>array(),'total'=>array('total_view'=>0,'total_completed'=>0));                   
        if(isset($_SESSION["userid"]))
        { 
                            
            $cateid = escape_str($_REQUEST["cateid"]);                           
                   
            $user_cond='';
                   
            $pn = escape_str($_REQUEST["pn"]);
            $nr = escape_str($_REQUEST["nr"]);
            $key = escape_str($_REQUEST["key"]);
        
            //CATEGORY
            $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'));
            }             
             
            //GET ALL CONTENTS OF CATEGORY
            $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 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);
            
            $total_view=0;
            $total_completed=0;
            if(count($contentresult_arr))
            {
                foreach($contentresult_arr as $res_content)
                {
                    $count_str="SELECT COUNT(c.userid) as total_view FROM (SELECT userid,contentid FROM `".TABLE_USER_COMPLETE_CONTENT."` WHERE contentid='".$res_content->contentid."' AND assettopicid='".$res_content->assettopicid."') c JOIN ".TABLE_USER." u ON u.userid=c.userid  WHERE u.role=5 AND u.is_deleted=0  ".$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;
                    $total_view+=$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."' AND assettopicid='".$res_content->assettopicid."') c JOIN ".TABLE_USER." u ON u.userid=c.userid WHERE u.role=5 AND u.is_deleted=0  ".$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;
                    $total_completed+=$res_content->total_completed;
                    //$res_content->total_query=$count_str;
                    $data_arr[]=$res_content;
                }                            
                $arr['report'] = $data_arr; 
                $arr['total'] = array('total_view'=>$total_view,'total_completed'=>$total_completed);
            }
            
            
            $conn->message(true,$conn->getMessage($lang,'report','success1'), $arr);  
        }else{
            $conn->message(false,$conn->getMessage($lang,'report','error1'));
        }
    }catch (PDOException $e) {
        $conn->message(false,$conn->getMessage($lang,'report','error4'));
    }
}


// content_report_rm
function content_report_rm() {
    global $conn;
    try {
        $lang = "en_us";
        $pageData = $conn->loadJSON($lang);
        $arr=array('report'=>array(),'total'=>array('total_view'=>0,'total_completed'=>0));                   
        if(isset($_SESSION["userid"]))
        { 
                            
            $cateid = escape_str($_REQUEST["cateid"]);                           
            $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."'":'';         
                   
                $user_cond=$mcond;
            }else
            {
                //PAN
                $user_cond='';
            }
            
            $pn = escape_str($_REQUEST["pn"]);
            $nr = escape_str($_REQUEST["nr"]);
            $key = escape_str($_REQUEST["key"]);
        
            //CATEGORY
            $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'));
            }             
             
            //GET ALL CONTENTS OF CATEGORY
            $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 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);
            
            $total_view=0;
            $total_completed=0;
            if(count($contentresult_arr))
            {
                foreach($contentresult_arr as $res_content)
                {
                    $count_str="SELECT COUNT(c.userid) as total_view FROM (SELECT userid,contentid FROM `".TABLE_USER_COMPLETE_CONTENT."` WHERE contentid='".$res_content->contentid."' AND assettopicid='".$res_content->assettopicid."') c JOIN ".TABLE_USER." u ON u.userid=c.userid JOIN ".DMS_DEALER." d ON u.emailid=d.rm_email  WHERE u.role=6 AND u.is_deleted=0  ".$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;
                    $total_view+=$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."' AND assettopicid='".$res_content->assettopicid."') c JOIN ".TABLE_USER." u ON u.userid=c.userid JOIN ".DMS_DEALER." d ON u.emailid=d.rm_email WHERE u.role=6 AND u.is_deleted=0  ".$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;
                    $total_completed+=$res_content->total_completed;
                    //$res_content->total_query=$count_str;
                    $data_arr[]=$res_content;
                }                            
                $arr['report'] = $data_arr; 
                $arr['total'] = array('total_view'=>$total_view,'total_completed'=>$total_completed);
            }
            
            
            $conn->message(true,$conn->getMessage($lang,'report','success1'), $arr);  
        }else{
            $conn->message(false,$conn->getMessage($lang,'report','error1'));
        }
    }catch (PDOException $e) {
        $conn->message(false,$conn->getMessage($lang,'report','error4'));
    }
}


// content_report_am
function content_report_am() {
    global $conn;
    try {
        $lang = "en_us";
        $pageData = $conn->loadJSON($lang);
        $arr=array('report'=>array(),'total'=>array('total_view'=>0,'total_completed'=>0));                   
        if(isset($_SESSION["userid"]))
        { 
                            
            $cateid = escape_str($_REQUEST["cateid"]);                           
            $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."'":'';         
                   
                $user_cond=$mcond;
            }else
            {
                //PAN
                $user_cond='';
            }
            
            $pn = escape_str($_REQUEST["pn"]);
            $nr = escape_str($_REQUEST["nr"]);
            $key = escape_str($_REQUEST["key"]);
        
            //CATEGORY
            $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'));
            }             
            
             
            //GET ALL CONTENTS OF CATEGORY
            $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 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);
            
            $total_view=0;
            $total_completed=0;
            if(count($contentresult_arr))
            {
                foreach($contentresult_arr as $res_content)
                {
                    $count_str="SELECT COUNT(c.userid) as total_view FROM (SELECT userid,contentid FROM `".TABLE_USER_COMPLETE_CONTENT."` WHERE contentid='".$res_content->contentid."' AND assettopicid='".$res_content->assettopicid."') c JOIN ".TABLE_USER." u ON u.userid=c.userid JOIN ".DMS_DEALER." d ON u.emailid=d.am_email  WHERE u.role=7 AND u.is_deleted=0  ".$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;
                    $total_view+=$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."' AND assettopicid='".$res_content->assettopicid."') c JOIN ".TABLE_USER." u ON u.userid=c.userid JOIN ".DMS_DEALER." d ON u.emailid=d.am_email WHERE u.role=7 AND u.is_deleted=0  ".$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;
                    $total_completed+=$res_content->total_completed;
                    //$res_content->total_query=$count_str;
                    $data_arr[]=$res_content;
                }                            
                $arr['report'] = $data_arr; 
                $arr['total'] = array('total_view'=>$total_view,'total_completed'=>$total_completed);
            }
            
            
            $conn->message(true,$conn->getMessage($lang,'report','success1'), $arr);  
        }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);
                            
        if(isset($_SESSION["userid"]))
        { 
                            
            $assettopicid = escape_str($_REQUEST["assettopicid"]);        
            $contentid = escape_str($_REQUEST["contentid"]); 
            
            $user_cond='';
                   
            $pn = escape_str($_REQUEST["pn"]);
            $nr = escape_str($_REQUEST["nr"]);
            $key = escape_str($_REQUEST["key"]);
        
            //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'));
            } 
            
            $arr['content'] =   $content_arr;     
            
            //GET ALL USER
             $content_user_str="SELECT SQL_CALC_FOUND_ROWS u.emp_code,u.fname,u.lname,u.mobileno,u.emailid,c.status, 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.completeddate  FROM (SELECT id,userid,contentid,status,completeddate 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.id ASC ";
            // Pagination
			if (!empty($pn) and !empty($nr)) {
				// Navigation
    			$content_user_str.= " LIMIT ".(($pn - 1) *  $nr).", ".$nr;
			}
            $query = $conn->query($content_user_str);
            
            $data_arr=array();
            if($query->rowCount()>0)
            {
                $data_arr = $query->fetchAll(PDO::FETCH_OBJ);
            }
            $arr['report'] = $data_arr; 
        
         
            // Total records
			$rows_query = $conn->query("select FOUND_ROWS() as total_records");
			$rows_result = $rows_query->fetchAll(PDO::FETCH_OBJ);
			$total_records = $rows_result[0]->total_records;            
            $arr['total_records'] = $total_records;
            $conn->message(true,$conn->getMessage($lang,'report','success1'), $arr);  
        }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);
                            
        if(isset($_SESSION["userid"]))
        { 
                            
            $assettopicid = escape_str($_REQUEST["assettopicid"]);        
            $contentid = escape_str($_REQUEST["contentid"]); 
            
            $user_cond='';
                   
            $pn = escape_str($_REQUEST["pn"]);
            $nr = escape_str($_REQUEST["nr"]);
            $key = escape_str($_REQUEST["key"]);
        
            //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'));
            } 
            
            $arr['content'] =   $content_arr;     
            
            //GET ALL USER
            $content_user_str="SELECT SQL_CALC_FOUND_ROWS u.emp_code,u.fname,u.lname,u.mobileno,u.emailid,c.status, 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.completeddate  FROM (SELECT id,userid,contentid,status,completeddate 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.id ASC ";
            // Pagination
			if (!empty($pn) and !empty($nr)) {
				// Navigation
    			$content_user_str.= " LIMIT ".(($pn - 1) *  $nr).", ".$nr;
			}
            $query = $conn->query($content_user_str);
            
            $data_arr=array();
            if($query->rowCount()>0)
            {
                $data_arr = $query->fetchAll(PDO::FETCH_OBJ);
            }
            $arr['report'] = $data_arr; 
        
         
            // Total records
			$rows_query = $conn->query("select FOUND_ROWS() as total_records");
			$rows_result = $rows_query->fetchAll(PDO::FETCH_OBJ);
			$total_records = $rows_result[0]->total_records;            
            $arr['total_records'] = $total_records;
            $conn->message(true,$conn->getMessage($lang,'report','success1'), $arr);  
        }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);
                            
        if(isset($_SESSION["userid"]))
        { 
                            
            $assettopicid = escape_str($_REQUEST["assettopicid"]);        
            $contentid = escape_str($_REQUEST["contentid"]);  
            $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."'":'';       
                $user_cond=$mcond;
            }else
            {
                //PAN
                $user_cond='';
            }
            
            $pn = escape_str($_REQUEST["pn"]);
            $nr = escape_str($_REQUEST["nr"]);
            $key = escape_str($_REQUEST["key"]);
        
            //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'));
            } 
            
            $arr['content'] =   $content_arr;  
            //$content_user_str="SELECT SQL_CALC_FOUND_ROWS u.emp_code,u.fname,u.lname,u.mobileno,u.emailid,c.status, 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.completeddate  FROM (SELECT id,userid,contentid,status,completeddate FROM `".TABLE_USER_COMPLETE_CONTENT."` WHERE contentid='".$contentid."' AND assettopicid='".$assettopicid."') c JOIN ".TABLE_USER." u ON u.userid=c.userid WHERE  AND u.role=5 ".$user_cond." GROUP BY c.userid ORDER BY c.status DESC,c.id ASC ";
            
            //GET ALL USER
            $content_user_str="SELECT SQL_CALC_FOUND_ROWS u.emp_code,u.fname,u.lname,u.mobileno,u.emailid,c.status, 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.completeddate FROM (SELECT id,userid,contentid,status,completeddate 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.id ASC ";
            // Pagination
			if (!empty($pn) and !empty($nr)) {
				// Navigation
    			$content_user_str.= " LIMIT ".(($pn - 1) *  $nr).", ".$nr;
			}
            $query = $conn->query($content_user_str);
            
            $data_arr=array();
            if($query->rowCount()>0)
            {
                $data_arr = $query->fetchAll(PDO::FETCH_OBJ);
            }
            $arr['report'] = $data_arr; 
        
         
            // Total records
			$rows_query = $conn->query("select FOUND_ROWS() as total_records");
			$rows_result = $rows_query->fetchAll(PDO::FETCH_OBJ);
			$total_records = $rows_result[0]->total_records;            
            $arr['total_records'] = $total_records;
            $conn->message(true,$conn->getMessage($lang,'report','success1'), $arr);  
        }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);
                            
        if(isset($_SESSION["userid"]))
        { 
                            
            $assettopicid = escape_str($_REQUEST["assettopicid"]);        
            $contentid = escape_str($_REQUEST["contentid"]);  
            $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."'":'';       
                $user_cond=$mcond;
            }else
            {
                //PAN
                $user_cond='';
            }
            
            $pn = escape_str($_REQUEST["pn"]);
            $nr = escape_str($_REQUEST["nr"]);
            $key = escape_str($_REQUEST["key"]);
        
            //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'));
            } 
            
            $arr['content'] =   $content_arr;  
            //$content_user_str="SELECT SQL_CALC_FOUND_ROWS u.emp_code,u.fname,u.lname,u.mobileno,u.emailid,c.status, 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.completeddate  FROM (SELECT id,userid,contentid,status,completeddate FROM `".TABLE_USER_COMPLETE_CONTENT."` WHERE contentid='".$contentid."' AND assettopicid='".$assettopicid."') c JOIN ".TABLE_USER." u ON u.userid=c.userid WHERE  AND u.role=5 ".$user_cond." GROUP BY c.userid ORDER BY c.status DESC,c.id ASC ";
            
            //GET ALL USER
            $content_user_str="SELECT SQL_CALC_FOUND_ROWS u.emp_code,u.fname,u.lname,u.mobileno,u.emailid,c.status, 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.completeddate FROM (SELECT id,userid,contentid,status,completeddate 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.id ASC ";
            // Pagination
			if (!empty($pn) and !empty($nr)) {
				// Navigation
    			$content_user_str.= " LIMIT ".(($pn - 1) *  $nr).", ".$nr;
			}
            $query = $conn->query($content_user_str);
            
            $data_arr=array();
            if($query->rowCount()>0)
            {
                $data_arr = $query->fetchAll(PDO::FETCH_OBJ);
            }
            $arr['report'] = $data_arr; 
        
         
            // Total records
			$rows_query = $conn->query("select FOUND_ROWS() as total_records");
			$rows_result = $rows_query->fetchAll(PDO::FETCH_OBJ);
			$total_records = $rows_result[0]->total_records;            
            $arr['total_records'] = $total_records;
            $conn->message(true,$conn->getMessage($lang,'report','success1'), $arr);  
        }else{
            $conn->message(false,$conn->getMessage($lang,'report','error1'));
        }
    }catch (PDOException $e) {
        $conn->message(false,$conn->getMessage($lang,'report','error4'));
    }
}
?>