 <script src="https://code.highcharts.com/highcharts.js"></script>
<script src="https://code.highcharts.com/modules/exporting.js"></script>
<?php  
if($_SESSION['role']!=1){ 
exit("You dont have permisiion to access this page ");}
?>
 <div>
    <ul class="breadcrumb">
        <li>
            <a href="index.php">Home</a>
        </li>
        <li>
            <a href="index.php?page=reports"> Add Services</a>
        </li>
    </ul>
</div>

<div class="row">
        <div class="box col-md-12">
            <div class="box-inner">
                <div class="box-header well" data-original-title="">
                    <h2><i class=" glyphicon glyphicon-th-list"></i> Subscribers Reports</h2>

                    <div class="box-icon">
                        <a href="#" class="btn btn-setting btn-round btn-default"><i
                                class="glyphicon glyphicon-cog"></i></a>
                        <a href="#" class="btn btn-minimize btn-round btn-default"><i
                                class="glyphicon glyphicon-chevron-up"></i></a>
                        <a href="#" class="btn btn-close btn-round btn-default"><i
                                class="glyphicon glyphicon-remove"></i></a>
                    </div>
                </div>
                <div class="box-content">
                
                <br />
 
                       
                       
                       
                       
                       
                       
                       
                       
                       
                      
                  <fieldset>
<legend>Search</legend>
                       
                       
                       <form method="post"  class="login_form"  action="">
<input type="hidden" name="optsearch" value="1" />
 <?php /*?>
<div class="form-group">
<div class="row ">
<div class="col-md-2">Name</div>
<div class="col-md-4"><input type="text" name="ServicesOperator_nameSearch" id="ServicesOperator_nameSearch" class="form-control" placeholder="ServicesOperator_name" value="<?php echo($_REQUEST['ServicesOperator_nameSearch']); ?>" /></div>
 
<div class="col-md-2">operator</div>
<div class="col-md-4">

<select name="ServicesOperator_operatorID" class="form-control" data-rel="chosen">
<option value="0">select operator</option>
<?php $seloperator=mysqli_query($connect,"select * from operator");

while($rowProv=mysqli_fetch_array($seloperator)){
	?>
	<option value="<?php echo($rowProv['Operator_id']); ?>"
    <?php  if($_REQUEST['ServicesOperator_operatorID']==$rowProv['Operator_id']){?> selected<?php } ?>
    ><?php echo($rowProv['Operator_name']); ?></option>
	<?php
	
	}
 ?>
</select>


 
</div>
</div>
</div>
 

<div class="form-group">
<div class="row ">
<div class="col-md-2">Services </div>
<div class="col-md-4"> 

<select name="ServicesOperator_servicesID" class="form-control" data-rel="chosen">
<option value="0">select Services</option>
<?php $seloperator=mysqli_query($connect,"select * from services");


while($rowProv=mysqli_fetch_array($seloperator)){
	?>
	<option value="<?php echo($rowProv['Services_id']); ?>" <?php if($_REQUEST['ServicesOperator_servicesID']==$rowProv['Services_id']){?> selected<?php } ?>><?php echo($rowProv['Services_name']); ?></option>
	<?php
	
	}
 ?>
</select>


</div>
 
<div class="col-md-2">Affiliates </div>
<div class="col-md-4"> 
<select name="affiliates_id" id="affiliates_id" class="form-control" data-rel="chosen">
<option value="0">Select Affiliates</option>
<?php $selaffiliatesr=mysqli_query($connect,"select * from affiliates");


while($rowProv=mysqli_fetch_array($selaffiliatesr)){
	?>
	<option value="<?php echo($rowProv['affiliates_id']); ?>" <?php if($_REQUEST['id']==$rowProv['affiliates_id']){?> selected<?php } ?>><?php echo($rowProv['affiliates_name']); ?></option>
	<?php
	
	}
 ?>
</select>


</div>
</div>
</div>
<?php */?>



<div class="form-group">
<div class="row ">
<div class="col-md-2">Landing Page </div>
<div class="col-md-4"> 
<select name="landingPage_id" id="landingPage_id" class="form-control" data-rel="chosen">
<option value="0">Select Landing page</option>
<?php $selaffiliatesr=mysqli_query($connect," select * from landingpage");


while($rowProv=mysqli_fetch_array($selaffiliatesr)){
	?>
	<option value="<?php echo($rowProv['landingPage_id']); ?>" <?php if($_REQUEST['landingPage_id']==$rowProv['landingPage_id']){?> selected<?php } ?>><?php echo($rowProv['landingPage_name']); ?></option>
	<?php
	
	}
 ?>
</select>


</div>






<div class="col-md-2">Operator  </div>
<div class="col-md-4"> 
<select name="ServicesOperator_operatorID" class="form-control" data-rel="chosen">
<option value="0">select operator</option>
<?php $seloperator=mysqli_query($connect,"select * from operator");

while($rowProv=mysqli_fetch_array($seloperator)){
	?>
	<option value="<?php echo($rowProv['Operator_id']); ?>"
    <?php  if($_REQUEST['ServicesOperator_operatorID']==$rowProv['Operator_id']){?> selected<?php } ?>
    ><?php echo($rowProv['Operator_name']); ?></option>
	<?php
	
	}
 ?>
</select>


</div>









</div>
</div>



<div class="form-group">
<div class="row ">
<div class="col-md-2">Group by</div>
<div class="col-md-4"><select name="groupBy" id="groupBy" class="form-control" data-rel="chosen">
 
 <option value="Hour" <?php if($_REQUEST['groupBy']=='Hour'){ ?> selected<?php  } ?>>Hour</option>
 <option value="date" <?php if($_REQUEST['groupBy']=='date'){ ?> selected<?php  } ?>>Day</option>
 <option value="week" <?php if($_REQUEST['groupBy']=='week'){ ?> selected<?php  } ?>>week</option>
  <option value="month" <?php if($_REQUEST['groupBy']=='month'){ ?> selected<?php  } ?>>month</option>
 <option value="year" <?php if($_REQUEST['groupBy']=='year'){ ?> selected<?php  } ?>>year</option>
 
 
 
 
 
 
</select>



</div>
  
</div>
</div>




<div class="form-group">
<div class="row ">
<div class="col-md-2">from</div>
<div class="col-md-4"><input type="date" name="fromd" id="fromd" class="form-control" placeholder="From Date" value="<?php echo($_REQUEST['fromd']); ?>" /></div>
 
<div class="col-md-2">To</div>
<div class="col-md-4"><input type="date" name="tod" id="tod" class="form-control" placeholder="To Date" value="<?php echo($_REQUEST['tod']); ?>" /></div>
</div>
</div>

 



<div class="form-group">
<div class="row ">

<div class="col-md-12"><input type="submit" name="update_Services" value="Search" class="action-button"  style="width:120px"/></div>
</div>
</div>

</form>
                       
                       
                       
                       
                      
            </fieldset>           
                      
                       
                       
                       
                       
                       
                       
                       
                     <?php 
					 
					 
					  
					 
					 
					 
					 
					 
					 
					 
					 if($_REQUEST['optsearch']==1){ ?>  
                       
                       
                       <div class="row">
                       <div class="col-md-12">
                     <?php  
					 $query_Services="";
					 
if($_REQUEST['fromd']!='' && $_REQUEST['tod']!=''){
$query_Services=$query_Services." and date(Subscribers_date) between '".$_REQUEST['fromd']."' and '".$_REQUEST['tod']."'";
	 
	}else{
		
$query_Services=$query_Services." and Subscribers_date > date_sub(now(),interval 24 hour)
 	";	
		}
 
	/* if($_REQUEST['deviceType']!='0'){
	$query_Services=$query_Services." and affil_landingLogs_device  like  '%".$_REQUEST['deviceType']."%'";
	 
	}*/
	 if($_REQUEST['ServicesOperator_operatorID']!='0'){
	$query_Services=$query_Services." and 	Subscribers_operatorID  =  '".$_REQUEST['ServicesOperator_operatorID']."'";
	 
	}
	
	
	
	$groupby ="  DATE(  `Subscribers_date` ) , HOUR(  `Subscribers_date` )  ";
	$orderby ="  order by DATE(  `Subscribers_date` ) asc,   HOUR(  `Subscribers_date` ) asc   ";
	
 
	 if($_REQUEST['groupBy']!='Hour'){
	$groupby =" ".$_REQUEST['groupBy']."(  `Subscribers_date` ) ";
	$orderby =" order by   ".$_REQUEST['groupBy']."(  `Subscribers_date` )  asc ";
	 }
	
	 
	
	
	
 $selectLandingAffilates=mysqli_query($connect,"SELECT affiliates.*,landingPage_id,affiliates_landing_id FROM `landingpage`     
inner join affiliates_landing on `affiliates_landing_landingId`=landingPage_id
inner join affiliates on `affiliates_id`=`affiliates_landing_affID`
where `landingPage_id`='".$_REQUEST['landingPage_id']."'
order by affiliates_id asc 
   ");
  
 
 $affiCount="";
 $xx=0;
while($rowAffilates=mysqli_fetch_array($selectLandingAffilates)){
	$xx=1;
	 $affiCount.=" , COUNT( 
CASE WHEN  `Subscribers_affi` ='".$rowAffilates['affiliates_id']."' THEN 1 END ) ".$rowAffilates['affiliates_name']." ";

 
 



} 
	
	
	
	
	
	
	
 
	
	
	
	
	
	$selectLandingAffilatesViews=mysqli_query($connect,"
		
		SELECT COUNT( * ) 
		
		".$affiCount."
		
		, ".$groupby. " hourCoun
FROM  `Subscribers` 
where 1=1   
  ".$query_Services."
GROUP BY ".$groupby."
		
		
		 

".$orderby."  ");
 
 
 
 
 $arra=array();
$arratotla=array();
 
 
 
 $xxx=0;
while($rowAffilatesv=mysqli_fetch_array($selectLandingAffilatesViews)){ $xxx+1;
	
	
	
  $arra[]="'".$rowAffilatesv['hourCoun']."'"; 
 $arratotla[]=$rowAffilatesv[0]; 
  
  
  
  
	   } 
	 
	  
	    ?>
       
       
     
       
       
<div id="container"></div>
 <script> 







Highcharts.chart('container', {
    chart: {
        type: 'line'
    },
    title: {
        text: 'Subscribers per af in last 24 Hour'
    },
    subtitle: {
        text: ''
    },
    xAxis: {
        categories:   [<?php  echo(join($arra,',')); ?>] 
    },
    yAxis: {
        title: {
            text: 'Views Count'
        }
    },
    plotOptions: {
        line: {
            dataLabels: {
                enabled: true
            },
            enableMouseTracking: true
        }
    },
    series: [
	
	
	
	
	
	<?php  $selectLandingAffilates=mysqli_query($connect,"SELECT affiliates.*,landingPage_id,affiliates_landing_id FROM `landingpage`     
inner join affiliates_landing on `affiliates_landing_landingId`=landingPage_id
inner join affiliates on `affiliates_id`=`affiliates_landing_affID`
where `landingPage_id`='".$_REQUEST['landingPage_id']."'
   ");
  
$numaffiltes=mysqli_num_rows($selectLandingAffilates);
$x=0;
while($rowAffilates=mysqli_fetch_array($selectLandingAffilates)){




	
	
	$selectLandingAffilatesViews=mysqli_query($connect,"
		
		SELECT COUNT( * ) 
		
	, COUNT( 
CASE WHEN  `Subscribers_affi` ='".$rowAffilates['affiliates_id']."' THEN 1 END ) affSubs
		
		, ".$groupby. " hourCoun
FROM  `Subscribers` 
where 1=1 
  ".$query_Services."
GROUP BY ".$groupby."
		
		
		 

".$orderby."  ");



  $arrav=array();
 while($rowSubs=mysqli_fetch_array($selectLandingAffilatesViews)){
	 
	  $arrav[]=$rowSubs['affSubs']; 
	 
	 }












$x++;
?>{
        name: '<?php echo($rowAffilates['affiliates_name']); ?>',
        data: [<?php  echo(join($arrav,',')); ?> ]
    }
	<?php if($numaffiltes!=$x){ ?>
	,  
	
	<?php } } ?>
	
	
	,
	{
        name: 'total',
        data: [<?php  echo(join($arratotla,',')); ?> ]
    }
	
	
	
	
	
	
	
	
	
	]
});


</script> 
                 
                  </div></div>
                  
                  
                  
                  <?php  } ?>
                  
                  
                  
                  
                  
                  
                  
                  
                   
                  
                  
                  
                  
                  
                  
                  
					 
					 
					 
					 
					<?php if($_REQUEST['optsearch']==1){ ?>  
                       <br>
<br>
<br>

                       
                       <div class="row">
                       <div class="col-md-12">
                     <?php  
					 $query_Services="";
					 
if($_REQUEST['fromd']!='' && $_REQUEST['tod']!=''){
$query_Services=$query_Services." and date(Subscribers_date) between '".$_REQUEST['fromd']."' and '".$_REQUEST['tod']."'";
	 
	}else{
		
$query_Services=$query_Services." and Subscribers_date > date_sub(now(),interval 24 hour)
 	";	
		}
 
	/* if($_REQUEST['deviceType']!='0'){
	$query_Services=$query_Services." and affil_landingLogs_device  like  '%".$_REQUEST['deviceType']."%'";
	 
	}*/
	 if($_REQUEST['ServicesOperator_operatorID']!='0'){
	$query_Services=$query_Services." and 	Subscribers_operatorID  =  '".$_REQUEST['ServicesOperator_operatorID']."'";
	 
	}
	
	
	
	$groupby ="  DATE(  `Subscribers_date` ) , HOUR(  `Subscribers_date` )  ";
	$orderby ="  order by DATE(  `Subscribers_date` ) asc,   HOUR(  `Subscribers_date` ) asc   ";
	
 
	 if($_REQUEST['groupBy']!='Hour'){
	$groupby =" ".$_REQUEST['groupBy']."(  `Subscribers_date` ) ";
	$orderby =" order by   ".$_REQUEST['groupBy']."(  `Subscribers_date` )  asc ";
	 }
	
	 
	
	
	
 $selectLandingAffilates=mysqli_query($connect,"select * from services
   ");
  
 
 $affiCount="";
 $xx=0;
while($rowAffilates=mysqli_fetch_array($selectLandingAffilates)){
	$xx=1;
	 $affiCount.=" , COUNT( 
CASE WHEN  `Services_id` ='".$rowAffilates['Services_id']."' THEN 1 END ) serv".$rowAffilates['Services_id']." ";

 
 



} 
	
	
	
	
	
	
	
 
	
	
	
	
	
	$selectLandingAffilatesViews=mysqli_query($connect,"
		
		SELECT COUNT( * ) 
		
		".$affiCount."
		
		, ".$groupby. " hourCoun
from  Subscribers
inner join services on Services_id=Subscribers_serviceID


where 1=1   
  ".$query_Services."
GROUP BY ".$groupby."
		
		
		 

".$orderby."  ");
 
 
 
 
 $arra=array();
$arratotla=array();
 
 
 
 $xxx=0;
while($rowAffilatesv=mysqli_fetch_array($selectLandingAffilatesViews)){ $xxx+1;
	
	
	
  $arra[]="'".$rowAffilatesv['hourCoun']."'"; 
 $arratotla[]=$rowAffilatesv[0]; 
  
  
  
  
	   } 
	 
	  
	    ?>
       
       
     
       
       
<div id="containerPerserv"></div>
 <script> 







Highcharts.chart('containerPerserv', {
    chart: {
        type: 'line'
    },
    title: {
        text: 'Subscribers per Service in last 24 Hour'
    },
    subtitle: {
        text: ''
    },
    xAxis: {
        categories:   [<?php  echo(join($arra,',')); ?>] 
    },
    yAxis: {
        title: {
            text: 'Views Count'
        }
    },
    plotOptions: {
        line: {
            dataLabels: {
                enabled: true
            },
            enableMouseTracking: true
        }
    },
    series: [
	
	
	
	
	
	<?php  $selectLandingAffilates=mysqli_query($connect,"select * from services
   ");
  
$numaffiltes=mysqli_num_rows($selectLandingAffilates);
$x=0;
while($rowAffilates=mysqli_fetch_array($selectLandingAffilates)){




	
	
	$selectLandingAffilatesViews=mysqli_query($connect,"
		
		SELECT COUNT( * ) 
		
	, COUNT( 
CASE WHEN  `Services_id` ='".$rowAffilates['Services_id']."' THEN 1 END ) affSubs
		
		, ".$groupby. " hourCoun
from  Subscribers
inner join services on Services_id=Subscribers_serviceID
where 1=1 
  ".$query_Services."
GROUP BY ".$groupby."
		
		
		 

".$orderby."  ");







  $arrav=array();
 while($rowSubs=mysqli_fetch_array($selectLandingAffilatesViews)){
	 
	  $arrav[]=$rowSubs['affSubs']; 
	 
	 }












$x++;
?>{
        name: '<?php echo($rowAffilates['Services_name']); ?>',
        data: [<?php  echo(join($arrav,',')); ?> ]
    }
	<?php if($numaffiltes!=$x){ ?>
	,  
	
	<?php } } ?>
	
	
	,
	{
        name: 'total',
        data: [<?php  echo(join($arratotla,',')); ?> ]
    }
	
	
	
	
	
	
	
	
	
	]
});


</script> 
                 
                  </div></div>
                  
                  
                  
                  <?php  } ?>
                  
                  
                  
                  
                  
                  
                  
                  
                   
                  
                  
                  
                  
                  
                  
                  
                  
                  
                  
                  
                  
                  
                  
                  
                  
                  
                  
                  
                   
                </div>
            </div>
        </div>





