<?php
header('Content-Type: application/json');

// $con = mysqli_connect("localhost", "", "", "jibran84_ach");

$con=mysqli_connect("localhost", "jibran84_itc70kk", "Smartest1", "jibran84_cms");

// $data = array();
// $sqlQuery = "SELECT count(*) as formula FROM new_invoice where websitename = 'www.formula1online.net'";

// $result = mysqli_query($con,$sqlQuery);
// $row = mysqli_fetch_assoc($result);

//  $formula = $row['formula'];

// $sqlQuery = "SELECT count(*) as nascar FROM new_invoice where websitename = 'www.nascarlivetv.com'";

// $result = mysqli_query($con,$sqlQuery);
// $row = mysqli_fetch_assoc($result);

//  $nascar = $row['nascar'];


// $sqlQuery = "SELECT count(*) as motorstream FROM new_invoice where websitename = 'www.motorsstream.com'";

// $result = mysqli_query($con,$sqlQuery);
// $row = mysqli_fetch_assoc($result);

//  $motorstream = $row['motorstream'];


// $sqlQuery = "SELECT count(*) as motogb FROM new_invoice where websitename = 'www.motogponline.net'";

// $result = mysqli_query($con,$sqlQuery);
// $row = mysqli_fetch_assoc($result);

//  $motogb = $row['motogb'];


// $sqlQuery = "SELECT count(*) as nhra FROM new_invoice where websitename = 'www.livenhraracing.com'";

// $result = mysqli_query($con,$sqlQuery);
// $row = mysqli_fetch_assoc($result);

//  $nhra = $row['nhra'];


// $sqlQuery = "SELECT count(*) as rugby FROM new_invoice where websitename = 'www.watchonlinerugby.net'";

// $result = mysqli_query($con,$sqlQuery);
// $row = mysqli_fetch_assoc($result);

//  $rugby = $row['rugby'];


// $sqlQuery = "SELECT count(*) as superRugby FROM new_invoice where websitename = 'www.superrugbyonline.net'";

// $result = mysqli_query($con,$sqlQuery);
// $row = mysqli_fetch_assoc($result);
// $data = array();
//  $superRugby = $row['superRugby'];

// $ro
// $sqlQuery = "SELECT count(*) as tennis FROM new_invoice where websitename = 'www.livetennisonline.com'";

// $result = mysqli_query($con,$sqlQuery);
// w = mysqli_fetch_assoc($result);

//  $tennis = $row['tennis'];
		
// $data = array("formula"=>"$formula", "nascar"=>"$nascar", "motorstream"=>"$motorstream","motogb"=>"$motogb","nhra"=>"$nhra","rugby"=>"$rugby","superRugby"=>"$superRugby","tennis"=>"$tennis");

// //  "<pr>";
// // print_r($data);

// foreach ($data as $key => $value) {
// 	$query = "update website_total set name = '$key', total ='$value')";
// 	$result = mysqli_query($con,$query);
// }

if(isset($_GET['topDec'])){
    $sqlQuery = "SELECT new_invoice.shortname, count(user_status.status) as Total from user_status
                    join new_invoice on new_invoice.id = user_status.user_id
                    WHERE user_status.status = 'Decline'
                    GROUP BY shortname";
}elseif(isset($_GET['domainName'])){
   $name  = $_GET['domainName'];
   $sqlQuery = "SELECT country, COUNT(user_status.status) AS NUM FROM user_status
join new_invoice on new_invoice.id = user_status.user_id
where user_status.status = 'Decline'
and new_invoice.shortname = '$name'
GROUP BY country";
}elseif(isset($_GET['domainNameSuccess'])){
     $name  = $_GET['domainNameSuccess'];
   $sqlQuery = "SELECT country, COUNT(user_status.status) AS NUM FROM user_status
join new_invoice on new_invoice.id = user_status.user_id
where user_status.status = 'Success'
and new_invoice.shortname = '$name'
GROUP BY country";
    
}elseif(isset($_GET['SaleViewData'])){
  $domainName =$_GET['SaleViewData'];
   $sqlQuery = "SELECT SUM( new_invoice.amount ) AS total, (

SELECT SUM( new_invoice.amount )
FROM user_status
JOIN new_invoice ON new_invoice.id = user_status.user_id
WHERE MONTH( user_status.created_at ) = MONTH( CURRENT_DATE( ) )
AND user_status.status = 'Success'
AND new_invoice.shortname = '$domainName'
) AS
MONTH , (

SELECT SUM( new_invoice.amount )
FROM user_status
JOIN new_invoice ON new_invoice.id = user_status.user_id
WHERE DATE( user_status.`created_at` ) = CURRENT_DATE( )
AND user_status.status = 'Success'
AND new_invoice.shortname = '$domainName'
) AS today, (

SELECT SUM( new_invoice.amount )
FROM user_status
JOIN new_invoice ON new_invoice.id = user_status.user_id
WHERE YEAR( user_status.created_at ) = YEAR( CURRENT_DATE( ) )
AND user_status.status = 'Success'
AND new_invoice.shortname = '$domainName'
) AS YEAR
FROM user_status
JOIN new_invoice ON new_invoice.id = user_status.user_id
WHERE user_status.status = 'Success'
AND new_invoice.shortname = '$domainName'";
    
}elseif(isset($_GET['namebyBank'])){
     $name  = $_GET['namebyBank'];
   $sqlQuery = "SELECT new_invoice.shortname ,user_status.bank, COUNT(user_status.status) AS NUM FROM user_status
JOIN new_invoice on new_invoice.id = user_status.user_id
WHERE user_status.status = 'Decline'
AND new_invoice.shortname = '$name'
GROUP BY bank";
    
}elseif(isset($_GET['namebyBankSuc'])){
     $name  = $_GET['namebyBankSuc'];
   $sqlQuery = "SELECT new_invoice.shortname ,user_status.bank, COUNT(user_status.status) AS NUM FROM user_status
JOIN new_invoice on new_invoice.id = user_status.user_id
WHERE user_status.status = 'Success'
AND new_invoice.shortname = '$name'
GROUP BY bank";
    
}elseif($_GET['TotalSellDomainPS']){
     $sqlQuery = "SELECT new_invoice.websitename,new_invoice.shortname, COUNT(user_status.status) AS NUM,sum(user_status.status='Success') Success
FROM user_status
JOIN new_invoice on new_invoice.id = user_status.user_id
where new_invoice.zee_product = 'PS'
GROUP BY shortname";
}elseif($_GET['TotalSellDomain']){
     $sqlQuery = "SELECT new_invoice.websitename,new_invoice.shortname, COUNT(user_status.status) AS NUM,sum(user_status.status='Success') Success
FROM user_status
JOIN new_invoice on new_invoice.id = user_status.user_id
GROUP BY shortname";
}elseif(isset($_GET['domainNameSuccessData'])){
     $name  = $_GET['domainNameSuccessData'];
   $sqlQuery = "SELECT MONTHNAME( user_status.created_at ) as MONTH, COUNT(user_status.status) AS total,sum(new_invoice.amount) AS amount FROM user_status
join new_invoice on new_invoice.id = user_status.user_id
where user_status.status = 'Success'
and DATE(user_status.created_at) > DATE_SUB( CURDATE( ) , INTERVAL 1 YEAR )
and new_invoice.shortname = '$name'
GROUP BY YEAR( user_status.created_at) , MONTH(user_status.created_at)";
    
}elseif($_GET['cus_date']){
  $cus_date =   $_GET['cus_date'];
     if($cus_date != ''){
        if($cus_date == 'Today'){
             $start = date('Y-m-d');
            $end = date('Y-m-d');
           
        }elseif($cus_date == 'Yesterday'){
           $start = date('Y-m-d',strtotime("-1 days"));
           $end = date('Y-m-d',strtotime("-1 days"));
           
        }elseif($cus_date == 'last 7days'){
          $start =  date('Y-m-d', strtotime('-7 days'));
          $end = date('Y-m-d');
         
        }elseif($cus_date == 'month'){
             $month = date('m');
             $start = date("Y-$month-01");
             $end = date('Y-m-d');
            
        }elseif($cus_date == 'last month'){
             $start = date("Y-m-d", strtotime("first day of previous month"));
             $end   =  date("Y-m-d", strtotime("last day of previous month"));
            
        }
}
    
    
     $sqlQuery = "SELECT new_invoice.websitename,new_invoice.shortname, COUNT(user_status.status) AS NUM,sum(user_status.status='Success') Success
FROM user_status
JOIN new_invoice on new_invoice.id = user_status.user_id
 WHERE new_invoice.dates >= '$start' AND new_invoice.dates <= '$end'
GROUP BY shortname";
}
else{
 $sqlQuery = "SELECT websitename,shortname, COUNT(*) AS NUM FROM new_invoice GROUP BY shortname";
}
$result = mysqli_query($con,$sqlQuery);

$data = array();
foreach ($result as $row) {
	$data[] = $row;
}

mysqli_close($con);

  echo json_encode($data);
  

 

  
?>
