<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="index.php?page=getDataForchart">
<input type="hidden" name="optsearch" value="1" />
  



<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>





<?php /*?>
<div class="col-md-2">Device  </div>
<div class="col-md-4"> 
<select name="deviceType" id="deviceType" class="form-control" data-rel="chosen">
<option value="0">All</option>
 <option value="desktop" <?php if($_REQUEST['deviceType']=='desktop'){ ?> selected<?php  } ?>>desktop</option>
 <option value="Mobile" <?php if($_REQUEST['deviceType']=='Mobile'){ ?> selected<?php  } ?>>Mobile</option>
 <option value="Tablet" <?php if($_REQUEST['deviceType']=='Tablet'){ ?> selected<?php  } ?>>Tablet</option>
 <option value="ios" <?php if($_REQUEST['deviceType']=='ios'){ ?> selected<?php  } ?>>ios</option>
  <option value="android" <?php if($_REQUEST['deviceType']=='android'){ ?> selected<?php  } ?>>android</option>
 
 
</select>


</div><?php */?>









</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">
 
<?php /*?> <option value="Hour" <?php if($_REQUEST['groupBy']=='Hour'){ ?> selected<?php  } ?>>Hour</option><?php */?>
 <option value="date" <?php if($_REQUEST['groupBy']=='date'){ ?> selected<?php  } ?>>Day</option>
<?php /*?> <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><?php */?>
 
 
 
 
 
 
</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="";
					  $query_ServicesSubs="";
if($_REQUEST['fromd']!='' && $_REQUEST['tod']!=''){
$query_Services=$query_Services." and date(affil_landingLogs_date) between '".$_REQUEST['fromd']."' and '".$_REQUEST['tod']."'";
	 
	 
	 
	}else{
		
$query_Services=$query_Services." and affil_landingLogs_date > date_sub(now(),interval 24 hour)
 	";	
		}
 
	 /*if($_REQUEST['deviceType']!='0'){
	$query_Services=$query_Services." and affil_landingLogs_device  like  '%".$_REQUEST['deviceType']."%'";
	   
	}*/
	
	
	
	
	$groupby ="  DATE(  `affil_landingLogs_date` ) , HOUR(  `affil_landingLogs_date` )  ";
	$orderby ="  order by DATE(  `affil_landingLogs_date` ) asc,   HOUR(  `affil_landingLogs_date` ) asc   ";
	
 
	 if($_REQUEST['groupBy']!='Hour'){
	$groupby =" ".$_REQUEST['groupBy']."(  `affil_landingLogs_date` ) ";
	$orderby =" order by   ".$_REQUEST['groupBy']."(  `affil_landingLogs_date` )  asc ";
	 }
	
	  if($_REQUEST['landingPage_id']!='0'){
	$query_Services=$query_Services." and affil_landingLogs_landingID='".$_REQUEST['landingPage_id']."' ";
	  $query_ServicesSubs=$query_ServicesSubs." and Subscribers_landingid='".$_REQUEST['landingPage_id']."'";
	  $queryLand=$queryLand." and `landingPage_id`='".$_REQUEST['landingPage_id']."' ";
	  
	 }
	
	
	
	
	
	
	
	
	

 
  



	
 $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   1=1   ".  $query_ServicesSubs."
group by affiliates_id   
order by affiliates_id asc 
   ");
  $affiCount="";
 

while($rowAffilates=mysqli_fetch_array($selectLandingAffilates)){
 
	 $affiCount.=" , COUNT( 
CASE WHEN  `affil_landingLogs_affid` ='".$rowAffilates['affiliates_id']."' THEN 1 END ) af".$rowAffilates['affiliates_id']." ";

 


} 

	
	
	
	
	
	
	
	
	
	
					 
					 ?>
<div id="container"></div>
 <script> 







Highcharts.chart('container', {
    chart: {
        type: 'line'
    },
    title: {
        text: 'Subscribers per Views  % '
    },
    subtitle: {
        text: ''
    },
    xAxis: {
        categories: [
		
		
		
		
		
		
		
		
		
		<?php  $selectLandingAffilatesViews=mysqli_query($connect,"SELECT COUNT( * ) c  ".$affiCount.", ".$groupby." hourCoun
		 
		
		
FROM  `affil_landinglogs` 
where    1=1 
  ".$query_Services."
GROUP BY ".$groupby."

".$orderby."  ");
 
 
 $arrayDate=array();
$arraycount=array();

$affCount=array();
 

$numaffiltesview=mysqli_num_rows($selectLandingAffilatesViews);
$xv=0;
while($rowAffilatesv=mysqli_fetch_array($selectLandingAffilatesViews)){

$arrayDate[$xv]=$rowAffilatesv['hourCoun'];
$arraycount[$xv]=$rowAffilatesv['c'];

 
 $affCount[$xv]=array( "aff1"=>$rowAffilatesv['af1']
 ,"aff2"=>$rowAffilatesv['af2']
  ,"aff3"=>$rowAffilatesv['af3']
   ,"aff4"=>$rowAffilatesv['af4']
    ,"aff5"=>$rowAffilatesv['af5']
 );
 
 


$xv++;
		
	
	
	
	
		
		
	?>'<?php echo($rowAffilatesv['hourCoun']); ?>'<?php if($numaffiltesview!=$xv){ ?>
	,  
	
	<?php } } ?>  
		
		
		
		
		
		
		
		
		
		
		
		]
    },
    yAxis: {
        title: {
            text: 'Views Count'
        }
		, 
                plotLines: [ {
                    id: 'limit-max',
                    color: '#008000',
                    dashStyle: 'ShortDash',
                    width: 2,
                    value: '1',
                    zIndex: 0,
                    label : {
                        text : 'Thresholds   '                 }
                }] 
           
			
    },
    plotOptions: {
        line: {
            dataLabels: {
                enabled: true
            },
            enableMouseTracking: true
        }
    },
    series: [
	
	<?php   // print_r($affCount); ?>
	<?php // print_r($affCount[0]['aff2']); ?>
	
	
	<?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 1=1 ".$queryLand."
group by affiliates_id
order by affiliates_id asc 
   ");
  
$numaffiltes=mysqli_num_rows($selectLandingAffilates);
$x=0;
$arrayindex=0;
while($rowAffilates=mysqli_fetch_array($selectLandingAffilates)){

$x++;
?>{
        name: '<?php echo($rowAffilates['affiliates_name']); ?>',
 <?php
 
 // $arrayDate;
//$arraycount ;
 $arryaSubs=array();
 for($i=0;$i<sizeof($arrayDate);$i++){
 
  $selectSubscribers=mysqli_query($connect,"select count(*) from Subscribers where 
date(Subscribers_date)='".$arrayDate[$i]."'
and Subscribers_affi='".$rowAffilates['affiliates_id']."'
    ".  $query_ServicesSubs."


 "); 
 
 $ff=2;
 
 

 
 

 $s=0;
 while($rowsubscribers=mysqli_fetch_array($selectSubscribers)){
	 $ss=$s+1;
	//  echo("marwan".$affCount[$arrayindex]['af1']."<br>");
	  
	  
	 $arryaSubs[$i]=round(  $rowsubscribers[0] / $affCount[$i]['aff'.$rowAffilates['affiliates_id']],4) * 100;
	 $s++;
	  //echo($rowsubscribers[0]);
 }
 
 
 
 
 
 }
 
 
 ?>
		
		
		
		
        data: [ <?php echo(join( $arryaSubs,',')); ?>]
    }
	<?php if($numaffiltes!=$x){ ?>
	,  
	
	<?php } 
	
	
	$arrayindex++;} ?>
	
	
	
	
	
	
	
	
	
	
	
	
	
	]
});


</script> 
                 
                  </div></div>
                  
                  
                  
                  <?php  } ?>
                  
                  
                  
                  
                  
                  
                  
                  
                  
                    <div class="row">
                       <div class="col-md-12">
                       
                       
                       <table width="100%" border="0" cellspacing="0" cellpadding="0" class="table table-striped table-bordered bootstrap-datatable datatable responsive">
                                         
                         
                         
                           <tr>
                             <td>#</td>
                             <td>Date </td>
                             <td>Views </td>
                             <?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 1=1 ".$queryLand."
group by affiliates_id
order by affiliates_id asc 
   ");
  
 
 
while($rowAffilates=mysqli_fetch_array($selectLandingAffilates)){
	
	?>
	
	<td align="center" valign="middle"><?php  echo($rowAffilates['affiliates_name']);?> Views  </td>
	<td align="center" valign="middle"><?php  echo($rowAffilates['affiliates_name']);?>  Subscribers  </td> 
    <td align="center" valign="middle"><?php  echo($rowAffilates['affiliates_name']);?> %</td>
	<?php
                             
                             
}?>           
                             
                             
                            
                           </tr>
                           <?php  
						   
						
			$selectLandingAffilatesViews=mysqli_query($connect,"SELECT COUNT( * ) c  ".$affiCount.", ".$groupby." hourCoun
		 
		
		
FROM  `affil_landinglogs` 
where    1=1 
  ".$query_Services."
GROUP BY ".$groupby."

".$orderby."  ");


			
			 if($selectLandingAffilatesViews){
				 $x=0;
				 while($row=mysqli_fetch_assoc($selectLandingAffilatesViews))
				 {
				$x++;
			  ?>
						
                           <tr>
                             <td><?php echo($x); ?></td>
                             <td><?php echo($row['hourCoun']); ?></td>
                          <td><?php echo($row['c']); ?></td>
                            <?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 1=1 ".$queryLand."
group by affiliates_id
order by affiliates_id asc 
   ");
  
 
 
while($rowAffilates=mysqli_fetch_array($selectLandingAffilates)){
	
	
	
	
  $selectSubscribers=mysqli_query($connect,"select count(*) from Subscribers where 
date(Subscribers_date)='".$row['hourCoun']."'
and Subscribers_affi='".$rowAffilates['affiliates_id']."'
    ".  $query_ServicesSubs."


 "); 
 
 $rowsubscribers=mysqli_fetch_array($selectSubscribers);
	
	
	
	
	
	
	
	
	
	?>
	<td align="center" valign="middle"><?php echo($row['af'.$rowAffilates['affiliates_id']]); ?>  </td>
	<td align="center" valign="middle"><?php  echo( $rowsubscribers[0]);?>  </td>
    <td align="center" valign="middle"><?php  echo( round($rowsubscribers[0] / $row['af'.$rowAffilates['affiliates_id']],4) * 100 );?>  </td>
	
	<?php
                             
                             
}?>           


                           </tr>
                           
                           
                           <?php  }}?>
                        
                  </table>
                  
                  
                       
                       
                       </div></div>
                  
                  
                  
                  
                  
                  
                  
                  
                  
                  
                  
                  
                  
                  
                  
                  
                  
                  
                  
                  
                   
                </div>
            </div>
        </div>
