<?php
$date = date('Y-m-d h:i:s');

$current_time=date('Y-m-d H:i:s');

$total_profits = $conn->query("SELECT SUM(admin_commission - incentive) as total_profit, COUNT(*) as total_profit_booking FROM bookings WHERE incentive < admin_commission");
$total_profit = $total_profits->fetch_assoc();

$total_losses = $conn->query("SELECT SUM(incentive - admin_commission) as total_loss, COUNT(*) as total_loss_booking FROM bookings WHERE incentive > admin_commission");
$total_loss = $total_losses->fetch_assoc();


$recent_booking = $conn->query("select * from recent_search");

$sql = "select count(*) as total_booking,sum(price) as sum  from bookings where status !='pending'";
if ($result = $conn->query($sql))  {
    $one = $result->fetch_assoc();
}
$sql = "select count(*) as total_booking from bookings where status ='booked'";
if ($result = $conn->query($sql))  {
    $pending = $result->fetch_assoc();
}
$sql = "select count(*) as total_booking from bookings where status='vendor_pass' and bid_by='admin'";
if ($result = $conn->query($sql))  {
    $bid_admin = $result->fetch_assoc();
}
$sql = "select count(*) as total_booking from bookings where status='vendor_pass'";
if ($result = $conn->query($sql))  {
    $bid_vendor = $result->fetch_assoc();
}
$sql = "select count(*) as total_booking from bookings where status ='completed'";
if ($result = $conn->query($sql))  {
    $done = $result->fetch_assoc();
}
$sql="SELECT bookings.*,bookings.id as bk_id,payment_log.txn_amount,payment_log.booking_id,users.* FROM bookings LEFT JOIN payment_log ON payment_log.booking_id=bookings.id LEFT JOIN users ON users.id=bookings.user_id WHERE bookings.status='cancelled' ORDER BY bookings.pickup_date DESC";
if ($result = $conn->query($sql)) {
    $cancel = $result->num_rows;
}
$sql = "select count(*) as total_booking from bookings where status ='confirmed'";
if ($result = $conn->query($sql))  {
    $processing = $result->fetch_assoc();
}
$sql = "select count(*) as vendors,sum(w_amount) as amounts from vendor_profile";
if ($result = $conn->query($sql))  {
    $vendor = $result->fetch_assoc();
}
$sql = "select count(*) as cabs from cabs";
if ($result = $conn->query($sql))  {
    $cabs = $result->fetch_assoc();
}
$sql = "select count(*) as drivers from drivers";
if ($result = $conn->query($sql))  {
    $drivers = $result->fetch_assoc();
}
$sql = "select count(*) as users from users";
if ($result = $conn->query($sql))  {
    $users = $result->fetch_assoc();
}
// ==============total cab type=======================
$sql = "select count(*) as cab from cab_types";
if ($result = $conn->query($sql))  {
    $cab = $result->fetch_assoc();
}
// ============sum vendor wallet amount==============
$sql = "select SUM(w_amount) as wallet_amount from vendor_profile";
if ($result = $conn->query($sql))  {
    $wallet = $result->fetch_assoc();
}
// ==============total New Booking=======================
$sql = "select count(*) as new_booking from bookings where booking_type!='Admin' and seen=0";
if ($result = $conn->query($sql))  {
    $new_booking = $result->fetch_assoc();
}

// wallet vendor
$sql = "select count(*) as vendors,sum(w_amount) as amounts from vendor_login u,vendor_profile up where u.id=up.vendor_id and w_amount>0"; 
// $sql = "select count(*) as vendors,sum(w_amount) as amounts from vendor_profile where w_amount>0";
if ($result = $conn->query($sql)) {
	$wallet_vendor = $result->fetch_assoc();
}

$sql = "select count(*) as minus_vendor,sum(w_amount) as wallet from vendor_login u,vendor_profile up where u.id=up.vendor_id and w_amount<0";
if ($result = $conn->query($sql)) {
	$minus_wallet = $result->fetch_assoc();
}

$sql = "select count(*) as zero_vendor from vendor_profile where w_amount=0";
if ($result = $conn->query($sql)) {
	$zero_wallet = $result->fetch_assoc();
}

// vendor_app_version
$other_vendor = $conn->query("SELECT * FROM other_vendor");
$other_user = $conn->query("SELECT * FROM other_users");

$vendor_app_updated = $conn->query("SELECT * FROM vendor_app_version");

// vendor_app_not_version
$vendor_app_not_updated = $conn->query("SELECT * FROM vendor_login vl WHERE NOT EXISTS (SELECT 1 FROM vendor_app_version vav WHERE vav.vendor_id = vl.id)");

// ==============total membership================
$sql="SELECT * FROM membership_log INNER JOIN vendor_profile ON membership_log.vendor_id=vendor_profile.vendor_id WHERE membership_log.status=1";

if ($result = $conn->query($sql)) {
    $membership = $result->num_rows;
}
$prime=$conn->query("SELECT * FROM membership_log LEFT JOIN vendor_profile ON membership_log.vendor_id=vendor_profile.vendor_id LEFT JOIN vendor_login ON membership_log.vendor_id=vendor_login.id LEFT JOIN membership ON membership_log.membership_id=membership.id");
$prime_member = $prime->num_rows;

// driver not attached
$not_attached_driver = $conn->query("SELECT a.*,vendor_profile.* FROM vendor_login a INNER JOIN vendor_profile ON vendor_profile.vendor_id=a.id WHERE a.id NOT IN (SELECT b.vendor_id FROM drivers b)");

// Cabs not attached
$not_attached_cab = $conn->query("SELECT vendor_login.*,vendor_profile.* FROM vendor_login INNER JOIN vendor_profile ON vendor_profile.vendor_id=vendor_login.id WHERE vendor_login.id NOT IN (SELECT cabs.vendor_id FROM cabs)");

// Cabs not attached
$not_attached_driver_cab = $conn->query("SELECT vendor_login.*,vendor_profile.* FROM vendor_login INNER JOIN vendor_profile ON vendor_profile.vendor_id=vendor_login.id WHERE vendor_login.id NOT IN (SELECT cabs.vendor_id FROM cabs) AND vendor_login.id NOT IN (SELECT drivers.vendor_id FROM drivers)");


// active vendor
$pending_vendor = $conn->query("SELECT vendor_login.contact,vendor_profile.vendor_id,vendor_profile.full_name,vendor_profile.city,vendor_profile.state,vendor_login.email from vendor_login,vendor_profile where vendor_login.id=vendor_profile.vendor_id and vendor_profile.profile_status=-1");

// active vendor
$active_vendor = $conn->query("select * from vendor_login where status=1");

// Inactive vendor
$inactive_vendor = $conn->query("select * from vendor_login where status=0");

// Active prime vendor
$active_prime_vendor = $conn->query("SELECT * FROM membership_log LEFT JOIN vendor_profile ON membership_log.vendor_id=vendor_profile.vendor_id LEFT JOIN vendor_login ON membership_log.vendor_id=vendor_login.id LEFT JOIN membership ON membership_log.membership_id=membership.id WHERE end>='$date'");

// expired prime vendor
$expired_prime_vendor = $conn->query("SELECT * FROM membership_log LEFT JOIN vendor_profile ON membership_log.vendor_id=vendor_profile.vendor_id LEFT JOIN vendor_login ON membership_log.vendor_id=vendor_login.id LEFT JOIN membership ON membership_log.membership_id=membership.id WHERE end<'$date'");

// Not prime vendor
$not_prime_vendor = $conn->query("SELECT vendor_login.*,vendor_profile.* FROM vendor_login INNER JOIN vendor_profile ON vendor_profile.vendor_id=vendor_login.id WHERE vendor_login.id NOT IN (SELECT membership_log.vendor_id FROM membership_log)");

// expired vendor booking
$expired_assigned_booking=$conn->query("SELECT bookings.*,bookings.id as bk_id,payment_log.txn_amount,payment_log.booking_id,users.* FROM bookings LEFT JOIN payment_log ON payment_log.booking_id=bookings.id LEFT JOIN users ON users.id=bookings.user_id WHERE bookings.status='vendor_pass' AND TIMESTAMP(`pickup_date`, `pickup_time`)<'$current_time' ORDER BY bookings.pickup_date DESC");

// Not Assigned booking
$not_assigned_booking=$conn->query("SELECT bookings.*,bookings.id as bk_id,payment_log.txn_amount,payment_log.booking_id,users.* FROM bookings LEFT JOIN payment_log ON payment_log.booking_id=bookings.id LEFT JOIN users ON users.id=bookings.user_id WHERE bookings.status='vendor_pass' AND TIMESTAMP(`pickup_date`, `pickup_time`)>='$current_time' ORDER BY bookings.pickup_date DESC");


$available_cabs = $conn->query("SELECT available_vehicle.*,vendor_profile.full_name FROM available_vehicle INNER JOIN vendor_profile ON vendor_profile.vendor_id=available_vehicle.vendor_id");


 // Renewel prime vendor

$q="SELECT *,membership_log.status as status FROM membership_log LEFT JOIN vendor_profile ON membership_log.vendor_id=vendor_profile.vendor_id LEFT JOIN vendor_login ON membership_log.vendor_id=vendor_login.id LEFT JOIN membership ON membership_log.membership_id=membership.id group by membership_log.vendor_id having count(*)>1";
$qq = mysqli_query($conn,$q);
while($row = mysqli_fetch_array($qq)) {
    $vendor_id[] = $row['vendor_id'];
}
$vendor_id = implode(',', $vendor_id);

$renewel_prime_vendor=$conn->query("SELECT *,membership_log.status as status FROM membership_log LEFT JOIN vendor_profile ON membership_log.vendor_id=vendor_profile.vendor_id LEFT JOIN vendor_login ON membership_log.vendor_id=vendor_login.id LEFT JOIN membership ON membership_log.membership_id=membership.id WHERE end>='$date' AND membership_log.status=1 AND membership_log.vendor_id IN ($vendor_id)");



// chart

// membership count date wise

$sevenDays=array();
$membership_date = array();
$membership_total_amount = array();
$membership_amount=0;

// vendor
$vendor_date = array();
$vendor_date2 = array();
$vendor_date3 = array();
$vendor_wallets = 0;
$vendor_wallet_amounts = array();
$refund_wallet_amounts = array();

// bookings
// pending
$created_booking_vendor = array();
$created_booking_admin = array();
// processing
$processing_booking_vendor = array();
$processing_booking_admin = array();
// confirmed
$confirmed_booking_vendor = array();
$confirmed_booking_admin = array();
// completed
$completed_booking_vendor = array();
$completed_booking_admin = array();
// cancelled
$cancelled_booking_vendor = array();
$cancelled_booking_admin = array();

for ($i=0; $i < 7; $i++) { 
	$seven_days = date('Y-m-d',strtotime("-$i days"));
	$membership_date_wise_count=$conn->query("SELECT * FROM membership_log INNER JOIN vendor_profile ON membership_log.vendor_id=vendor_profile.vendor_id INNER JOIN membership ON membership.id=membership_log.membership_id WHERE DATE(start	) = '$seven_days'");
	$membership_data = $membership_date_wise_count->fetch_assoc();
	$membership_amount = $membership_data['price']*$membership_date_wise_count->num_rows;
	array_push($membership_total_amount, $membership_amount);
	array_push($sevenDays, $seven_days);
	array_push($membership_date, $membership_date_wise_count->num_rows);

	// VENDOR REGISTRATION
	$vendor_date_wise_count=$conn->query("SELECT * FROM vendor_login INNER JOIN vendor_profile ON vendor_profile.vendor_id=vendor_login.id WHERE DATE(timestamp) = '$seven_days'");
	array_push($vendor_date, $vendor_date_wise_count->num_rows);

	// inactive vendor
	$vendor_date_wise_count_inactive=$conn->query("SELECT * FROM vendor_login INNER JOIN vendor_profile ON vendor_profile.vendor_id=vendor_login.id WHERE status=0 AND DATE(timestamp) = '$seven_days'");
	array_push($vendor_date2, $vendor_date_wise_count_inactive->num_rows);

	// pending vendor
	$vendor_date_wise_count_pending=$conn->query("SELECT * FROM vendor_login INNER JOIN vendor_profile ON vendor_profile.vendor_id=vendor_login.id WHERE profile_status=-1 AND DATE(timestamp) = '$seven_days'");
	array_push($vendor_date3, $vendor_date_wise_count_pending->num_rows);

	// wallet
	$wallets = $conn->query("SELECT sum(amount) as total_amount from transaction_log WHERE DATE(time) = '$seven_days' AND txn_type='Credit' AND action='Adding Money to Wallet'");
	$transaction_log_amount = $wallets->fetch_assoc();
	array_push($vendor_wallet_amounts, $transaction_log_amount['total_amount']);

	// wallet
	$refund_amount = $conn->query("SELECT sum(amount) as total_amount from transaction_log WHERE DATE(time) = '$seven_days' AND txn_type='Debit' AND action='Wallet To Bank Transfer'");
	$transaction_log_refund_amount = $refund_amount->fetch_assoc();
	array_push($refund_wallet_amounts, $transaction_log_refund_amount['total_amount']);

	// PENDING BOOKING VENDOR POST
	$created_booking_vendor_data=$conn->query("SELECT * FROM bookings WHERE booking_type='Vendor App' AND DATE(time_stamp) = '$seven_days'");
	array_push($created_booking_vendor, $created_booking_vendor_data->num_rows);

	// PENDING BOOKING ADMIN POST
	$created_booking_admin_data=$conn->query("SELECT * FROM bookings WHERE booking_type='admin' AND DATE(time_stamp) = '$seven_days'");
	array_push($created_booking_admin, $created_booking_admin_data->num_rows);

	// PROCESSING BOOKING VENDOR POST
	$processing_booking_vendor_data=$conn->query("SELECT * FROM bookings WHERE status='vendor_pass' AND booking_type='Vendor App' AND DATE(time_stamp) = '$seven_days'");
	array_push($processing_booking_vendor, $processing_booking_vendor_data->num_rows);

	// PROCESSING BOOKING ADMIN POST
	$processing_booking_admin_data=$conn->query("SELECT * FROM bookings WHERE status='vendor_pass' AND booking_type='admin' AND DATE(time_stamp) = '$seven_days'");
	array_push($processing_booking_admin, $processing_booking_admin_data->num_rows);

	// CONFIRMED BOOKING VENDOR POST
	$confirmed_booking_vendor_data=$conn->query("SELECT * FROM bookings WHERE status='confirmed' AND booking_type='Vendor App' AND DATE(time_stamp) = '$seven_days'");
	array_push($confirmed_booking_vendor, $confirmed_booking_vendor_data->num_rows);

	// CONFIRMED BOOKING ADMIN POST
	$confirmed_booking_admin_data=$conn->query("SELECT * FROM bookings WHERE status='confirmed' AND booking_type='admin' AND DATE(time_stamp) = '$seven_days'");
	array_push($confirmed_booking_admin, $confirmed_booking_admin_data->num_rows);

	// COMPLETED BOOKING VENDOR POST
	$completed_booking_vendor_data=$conn->query("SELECT * FROM bookings WHERE status='completed' AND booking_type='Vendor App' AND DATE(time_stamp) = '$seven_days'");
	array_push($completed_booking_vendor, $completed_booking_vendor_data->num_rows);

	// COMPLETED BOOKING ADMIN POST
	$completed_booking_admin_data=$conn->query("SELECT * FROM bookings WHERE status='completed' AND booking_type='admin' AND DATE(time_stamp) = '$seven_days'");
	array_push($completed_booking_admin, $completed_booking_admin_data->num_rows);

	// CANCELLED BOOKING VENDOR POST
	$cancelled_booking_vendor_data=$conn->query("SELECT * FROM bookings WHERE status='cancelled' AND booking_type='Vendor App' AND DATE(time_stamp) = '$seven_days'");
	array_push($cancelled_booking_vendor, $cancelled_booking_vendor_data->num_rows);

	// COMPLETED BOOKING ADMIN POST
	$cancelled_booking_admin_data=$conn->query("SELECT * FROM bookings WHERE status='cancelled' AND booking_type='admin' AND DATE(time_stamp) = '$seven_days'");
	array_push($cancelled_booking_admin, $cancelled_booking_admin_data->num_rows);

}

?>