<?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=reportsStatistic"> 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=reportsStatistic">
<input type="hidden" name="optsearch" value="1" />
 
<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">af </div>
<div class="col-md-4"> 
<select name="affiliates_id" id="affiliates_id" class="form-control" data-rel="chosen">
<option value="0">Select af</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['affiliates_id']==$rowProv['affiliates_id']){?> selected<?php } ?>><?php echo($rowProv['affiliates_name']); ?></option>
	<?php
	
	}
 ?>
</select>


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




<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>
</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>           
                       
                       
                       
                       
                       
                       
                       
                       
                       
                       
                       <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>Service</td>
                             <td>Landing </td>
                             <td align="center" valign="middle">Active Sub  </td>
                             <td align="center" valign="middle">Inactive sub</td>
                             <td align="center" valign="middle">Activated</td>
                             <td align="center" valign="middle">NoBalance</td>
                             <td align="center" valign="middle">No Key</td>
                             <td align="center" valign="middle">Hits Active </td>
                             <td align="center" valign="middle">Hits inactive</td>
                             <td align="center" valign="middle">af</td>
                             <td align="center" valign="middle">operator</td>
                           </tr>
                           <?php  
						   
						
			 
			 $query_Services="select 
count( * ) Allsub ,
count( case when  Subscribers_status=1 then 1 end ) activesub
,count( case when  Subscribers_status=0 then 1 end ) inactivesub
,landingPage_name
,Services_name
,Operator_name
,affiliates_name
,Subscribers_landingid
,Subscribers_affi
 from Subscribers
inner join landingpage on landingPage_id=Subscribers_landingid
inner join affiliates on affiliates_id=Subscribers_affi
inner join servicesoperator on ServicesOperator_id=Subscribers_serviceoperator
inner join services on Services_id=ServicesOperator_servicesID
  inner join operator on Operator_id= ServicesOperator_operatorID

 
where 1=1 


" ;
if($_REQUEST['ServicesOperator_operatorID']!=0){
$query_Services=$query_Services." and Operator_id='".$_REQUEST['ServicesOperator_operatorID']."'";
	 
	}
	if($_REQUEST['ServicesOperator_servicesID']!=0){
$query_Services=$query_Services." and ServicesOperator_servicesID='".$_REQUEST['ServicesOperator_servicesID']."'";
	 
	}
if($_REQUEST['ServicesOperator_nameSearch']!=''){
	$query_Services=$query_Services." and ServicesOperator_name like '%".$_REQUEST['ServicesOperator_nameSearch']."%'";
	 
	}
	if($_REQUEST['affiliates_id']!=0){
	$query_Services=$query_Services." and affiliates_id = '".$_REQUEST['affiliates_id']."'";
	 
	}
	if($_REQUEST['landingPage_id']!=0){
	$query_Services=$query_Services." and landingPage_id = '".$_REQUEST['landingPage_id']."'";
	 
	}
 	 
		 
		 
if($_REQUEST['fromd']!='' && $_REQUEST['tod']!=''){
$query_Services=$query_Services." and date(Subscribers_date) between '".$_REQUEST['fromd']."' and '".$_REQUEST['tod']."'";
$query_Serviceslogs=$query_Serviceslogs." and date(SubscribersLogs_date) between '".$_REQUEST['fromd']."' and '".$_REQUEST['tod']."'";
	 
	}
		 
		 
			 $Services_res=mysqli_query($connect,$query_Services ."   group by landingPage_id,Services_id,Operator_id,affiliates_id");
			
			 if($Services_res){
				 while($row=mysqli_fetch_assoc($Services_res))
				 {
				
				$selectLogs=mysqli_query($connect,"SELECT 

count( case when SubscribersLogs_status=0 then  1  end ) inactive
,count( case when SubscribersLogs_status=1 then  1  end ) Active
,count( case when hadaraAPILogs='0' then  1  end ) Activated
,count( case when hadaraAPILogs='-1' then  1  end ) noBalance
,count( case when hadaraAPILogs is null then  1  end ) NoKey
FROM `SubscribersLogs`   
where `SubscribersLogs_landingid`='".$row['Subscribers_landingid']."'
".$query_Serviceslogs."
and SubscribersLogs_affi='".$row['Subscribers_affi']."' ");
				
			 
				$rowLogs=mysqli_fetch_array($selectLogs);
				
			  ?>
						
                           <tr>
                             <td><?php echo($row['Services_name']); ?></td>
                          <td><?php echo($row['landingPage_name']); ?></td>
                            <td align="center" valign="middle"><?php echo($row['activesub']); ?></td>
                             <td align="center" valign="middle"><?php echo($row['inactivesub']); ?></td>
                             <td align="center" valign="middle"><?php echo($rowLogs['Activated']); ?></td>
                             <td align="center" valign="middle"><?php echo($rowLogs['noBalance']); ?></td>
                             <td align="center" valign="middle"><?php echo($rowLogs['NoKey']); ?></td>
                             <td align="center" valign="middle"><?php echo($rowLogs['Active']); ?></td>
                             <td align="center" valign="middle"><?php echo($rowLogs['inactive']); ?></td>
                             <td align="center" valign="middle">
                            
                             <?php echo($row['affiliates_name']); ?>
                             
                             </td>
                             <td align="center" valign="middle"><?php echo($row['Operator_name']); ?></td>
                           </tr>
                           
                           
                           <?php  }}?>
                        
                  </table>
                  <br />


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