<?php
include 'header.php';

                ?>
                 <section class="content" id="homeDAta">
      <div class="container-fluid">
        <!-- Small boxes (Stat box) -->
        <div class="row">
          <div class="col-lg-4 col-6">
            <!-- small box -->
            <div class="small-box bg-info">
              <div class="inner">
                 <?php 
                if(isset($_GET['year'])){
					$start = $_GET['year']."-01-01";
				    $end   = ++$_GET['year']."-01-01";
				}else{
					$start = "2019-01-01";
					$end  ="2020-01-01";
			    }
                 $query = "SELECT count(user_status.id) as total FROM user_status
							join new_invoice on new_invoice.id = user_status.user_id 
							where new_invoice.dates >= '$start' AND new_invoice.dates < '$end'";
                 $result = mysqli_query($con,$query);
                 $row = mysqli_fetch_assoc($result);
                ?>
                <h3><?php echo $row['total'];?></h3>
                <p>Total Leads</p>
              </div>
              <div class="icon">
                <i class="ion ion-bag"></i>
              </div>
             
            </div>
          </div>
          <!-- ./col -->
          <div class="col-lg-4 col-6">
            <!-- small box -->
            <div class="small-box bg-success">
              <div class="inner">
                <?php 
                 
                
                 $query = " SELECT count(user_status.id) as total FROM user_status
							join new_invoice on new_invoice.id = user_status.user_id 
							where new_invoice.dates >= '$start' AND new_invoice.dates < '$end'
							and user_status.status = 'Decline'" ;
                 $result = mysqli_query($con,$query);
                 $row = mysqli_fetch_assoc($result);
                 
                ?>

                <h3><?php echo $row['total'];?></h3>

                <p>Decline</p>
              </div>
              <div class="icon">
                <i class="ion ion-stats-bars"></i>
              </div>
            
            </div>
          </div>
          <!-- ./col -->
          <div class="col-lg-4 col-6">
            <!-- small box -->
            <div class="small-box bg-warning">
              <div class="inner">
                 <?php 
                 $query = "SELECT count(user_status.id) as total FROM user_status
							join new_invoice on new_invoice.id = user_status.user_id 
							where new_invoice.dates >= '$start' AND new_invoice.dates < '$end'
							and user_status.status = 'Success'" ;
                 $result = mysqli_query($con,$query);
                 $row = mysqli_fetch_assoc($result);
                 
                ?>
                <h3><?php echo $row['total'];?></h3>

                <p>Total Success</p>
              </div>
              <div class="icon">
                <i class="ion ion-person-add"></i>
              </div>
            
            </div>
          </div>
          <!-- ./col -->
     
          <!-- ./col -->
        </div>
         <div class="row">
          <div class="col-lg-4 col-6">
            <!-- small box -->
            <div class="small-box"  style="background-color:#7CF944">
              <div class="inner">
                 <?php 
            
                 $today = date("Y-m-d");
                  $query = "SELECT sum(new_invoice.amount) as total FROM user_status
							join new_invoice on new_invoice.id = user_status.user_id 
							where new_invoice.dates >= '$start' AND new_invoice.dates < '$end'";
                 $result = mysqli_query($con,$query);
                 $row = mysqli_fetch_assoc($result);
                 $sell = $row['total'];
                 if($sell == ""){
                     $sell = "0";
                 }
                ?>
                <h3><?php echo "$" .$sell;?></h3>
                <p>Total Sell <span>(If All Leads in Success)</span></p>
              </div>
              <div class="icon">
               <i class="fa fa-shopping-cart"></i>
              </div>
             
            </div>
          </div>
          <!-- ./col -->
          <div class="col-lg-4 col-6">
            <!-- small box -->
            <div class="small-box bg-info" >
              <div class="inner">
                <?php 
                 
              
                  $query = "SELECT sum(new_invoice.amount) as total FROM user_status
							join new_invoice on new_invoice.id = user_status.user_id 
							where new_invoice.dates >= '$start' AND new_invoice.dates < '$end'and user_status.status = 'Decline'";
                 $result = mysqli_query($con,$query);
                 $row = mysqli_fetch_assoc($result);
                 
                ?>

                <h3><?php echo "$". $row['total'];?></h3>

                <p>Total Amount Of Decline</p>
              </div>
              <div class="icon">
                <i class="fa fa-money"></i>
              </div>
            
            </div>
          </div>
          <!-- ./col -->
      <div class="col-lg-4 col-6">
            <!-- small box -->
            <div class="small-box bg-info" >
              <div class="inner">
                <?php 
                 
              
                  $query = "SELECT sum(new_invoice.amount) as total FROM user_status
							join new_invoice on new_invoice.id = user_status.user_id 
							where new_invoice.dates >= '$start' AND new_invoice.dates < '$end'and user_status.status = 'Success'";
                 $result = mysqli_query($con,$query);
                 $row = mysqli_fetch_assoc($result);
                ?>
                <h3><?php echo "$". $row['total'];?></h3>

                <p>Total Amount Of Success Leads</p>
              </div>
              <div class="icon">
                <i class="fa fa-money"></i>
              </div>
            
            </div>
          </div>
          <!-- ./col -->
     
          <!-- ./col -->
        </div>


   
    </section>
                 <section class="content" id="homeDAta">
               <div class="container-fluid">
        <!-- Small boxes (Stat box) -->
              <div class="card">
            <div class="card-header">
              <h3 class="card-title">Data Table With Full Features 

             <!--  <select class="pull-right" id = "year">
		        <option>select Year</option>
		         <option value="Decline"><??></option>
		          <option value="Success">Success</option>
              </select> -->
              <?php
             $already_selected_value = date('Y');
             $already_selected_value = --$already_selected_value;
             $earliest_year = 2013;
				print '<select class="pull-right" id = "year" name="some_field">';
				foreach (range(date('Y'), $earliest_year) as $x) {
				    print '<option value="'.$x.'"'.($x === $already_selected_value ? ' selected="selected"' : '').'>'.$x.'</option>';
				}
				print '</select>';
              ?>

              </h3>
             
            </div>
            <!-- /.card-header -->
            <div class="card-body tableshow">
             <table id="example1" class="table table-bordered table-striped dataTable" role="grid" aria-describedby="example1_info">
                <thead>
                <tr role="row"><th class="sorting_asc" tabindex="0" aria-controls="example1" rowspan="1" colspan="1" aria-sort="ascending" aria-label="Rendering engine: activate to sort column descending" style="width: 149px;">Month</th><th class="sorting" tabindex="0" aria-controls="example1" rowspan="1" colspan="1" aria-label="Browser: activate to sort column ascending" style="width: 194px;">Total Leads</th><th class="sorting" tabindex="0" aria-controls="example1" rowspan="1" colspan="1" aria-label="Platform(s): activate to sort column ascending" style="width: 171px;">Leads Decline</th><th class="sorting" tabindex="0" aria-controls="example1" rowspan="1" colspan="1" aria-label="Engine version: activate to sort column ascending" style="width: 125px;">Decline Amount</th><th class="sorting" tabindex="0" aria-controls="example1" rowspan="1" colspan="1" aria-label="Platform(s): activate to sort column ascending" style="width: 171px;">Leads Succes</th><th class="sorting" tabindex="0" aria-controls="example1" rowspan="1" colspan="1" aria-label="Engine version: activate to sort column ascending" style="width: 125px;">Amount</th></tr>
                </thead>
                <tbody>
                <?php
              
				

				$sqlQuery = "SELECT MONTHNAME(user_status.created_at) as month,count(new_invoice.id) as total FROM user_status join new_invoice on new_invoice.id = user_status.user_id WHERE DATE(new_invoice.dates ) > DATE_SUB( CURDATE( ) , INTERVAL 1 YEAR ) and new_invoice.dates >= '$start' AND new_invoice.dates < '$end'  GROUP BY YEAR( user_status.created_at ) , MONTH( user_status.created_at ) ORDER BY user_status.created_at";
				$result = mysqli_query($con,$sqlQuery);
				$arr1  = array();
				
				while($row = mysqli_fetch_assoc($result)){
					
					$arr1[] = $row['total'];

				}

					 $sqlQuery = "SELECT MONTHNAME(user_status.created_at) as month, sum(new_invoice.amount) as Decline_total,count(new_invoice.id) as Decline_count_total
							FROM user_status
							join new_invoice on new_invoice.id = user_status.user_id
							WHERE DATE(new_invoice.dates ) > DATE_SUB( CURDATE( ) , INTERVAL 1 YEAR )
							and new_invoice.dates >= '$start' AND new_invoice.dates < '$end'
							and user_status.status = 'Decline'
							GROUP BY  YEAR( user_status.created_at ) , MONTH( user_status.created_at )
							ORDER BY user_status.created_at";
				$result = mysqli_query($con,$sqlQuery);
				$arr  = array();
				$arr2 = array();
				while($row = mysqli_fetch_assoc($result)){
					$month = $row['month'];
					$arr[$month] = $row['Decline_total'];
					$arr2[$month] = $row['Decline_count_total'];
				}
						
    

			  $sqlQuery = "SELECT MONTHNAME(user_status.created_at) as month, sum(new_invoice.amount) as total,count(new_invoice.id) as count_total
						FROM user_status
						join new_invoice on new_invoice.id = user_status.user_id
						WHERE DATE(new_invoice.dates ) > DATE_SUB( CURDATE( ) , INTERVAL 1 YEAR )
						and new_invoice.dates >= '$start' AND new_invoice.dates < '$end'
						and user_status.status = 'Success'
						GROUP BY YEAR( user_status.created_at ) , MONTH( user_status.created_at )
						ORDER BY user_status.created_at";
				$result = mysqli_query($con,$sqlQuery);
				$i = 0;
				while($row = mysqli_fetch_assoc($result)){
				$month = $row['month'];
		?>
                <tr role="row" class="odd">
                  <td class="sorting_1 month" style="cursor: pointer;"><?php echo $row['month']?></td>
                  <td><?php echo $arr1[$i]?></td>
                  <td><?php 
                  	 if($arr2[$month] == ''){
                  	echo 0;
                  }else{
                  echo $arr2[$month];
              }
                  ?></td>
                  <td><?php 
                  if($arr[$month] == ''){
                  	echo 0;
                  }else{
                  echo $arr[$month];
              }
                  ?></td>
                  
                   <td><?php echo $row['count_total']?></td>
                  <td>$<?php echo $row['total']?></td>
                  
                </tr>

        <?php
        $i++;
        	}
        ?>
            </tbody>
              </table>
            </div>
            <!-- /.card-body -->
          </div>
</div>
</section>
<?php
include 'footer.php';
?>
<script type="text/javascript">
	$(document).ready(function(){
		$('#year').change(function(){
			var year = $(this).val();
			window.location.href = 'yearDetail.php?year='+year;
		})
		var year = "<?php echo $start?>";
		$('.month').click(function(){
			var month = $(this).html();
			alert(year);
			$.get('response.php',{customMonth:month,year:year},function(data){
				$('.tableshow').html(data);
			})
		})
	})
	
</script>