/bbcsadmin/modules/report/export_money_details.php

<?php
//  echo "<pre>"; print_r($_REQUEST); exit;
//-----------------------------------------------
  if(($_REQUEST['dFDate'] != "") && ($_REQUEST['dTDate'] != ""))
  {
  	$dFDate = $_REQUEST['dFDate'];
  	$dTDate = $_REQUEST['dTDate'];
  }
  else
  {
//  	$dFDate = Date('Y-m-d');
//  	$dTDate = Date('Y-m-d');
  }
  
  $tdate=date("d")-1;
	$mdate=date("d");
	if(isset($dFDate) && isset($dTDate))
	{
		$startdate = $dFDate;
		$enddate = $dTDate;
		
		$order_status=explode(",",$status_array);
	}
	else
	{
//		$startdate = date("Y-m-d",mktime(0,0,0,date("m"),date("d")-$tdate,date("Y")));
//		$enddate = date("Y-m-d",mktime(0,0,0,date("m")+1,date("d")-$mdate,date("Y")));  
	} 
//-----------------------------------------------------   
$fdate = $_REQUEST['dFDate'];
$tdate = $_REQUEST['dTDate'];
$iBookingId = $_REQUEST['iBookingId'];

$ssql = "";
$sta = $_POST['sta'];
$psta = $_POST['psta'];
  //print_r($sta); exit;  
$alp = $_REQUEST['alp'];
$option = $_REQUEST['option'];
$action = $_REQUEST['action1'];
$keyword = $_REQUEST['keyword'];
if($_REQUEST['sortby'] == ''){
  $sortby = 4;
}else{
  $sortby = $_REQUEST['sortby'];
}
if($_REQUEST['order'] == ''){
  $order = 1;
}else{
  $order = $_REQUEST['order'];
}
$amt ='';
//Added by urvashi for filtering Payment Statuswise
if($psta != '')
{    
	$totid = count($psta);
  
	if(is_array($psta))
	{
		//PRefund
		if(in_array('PRefund',$psta)  && !in_array('DRefund',$psta) && !in_array('Paid',$psta)  && !in_array('Unpaid',$psta) )
		{
			$ssql.= "AND (eCancelBy = 'Driver') AND (ePassengerRefundPaid = 'No')";
		}
		//PRefund Paid
		else if(in_array('PRefund',$psta)  && !in_array('DRefund',$psta) && in_array('Paid',$psta)  && !in_array('Unpaid',$psta) )
		{
			$ssql.= "AND (eCancelBy = 'Driver') AND (ePassengerRefundPaid = 'No') OR (eCancelBy = 'Driver' AND ePassengerRefundPaid = 'Yes') OR (eCancelBy = 'Passenger' AND eDriverPaymentPaid = 'Yes') OR (eCancelBy = '' AND eDriverPaymentPaid = 'Yes' AND ePassengerRefundPaid = 'No')";
		}
		//PRefund Unpaid
		else if(in_array('PRefund',$psta)  && !in_array('DRefund',$psta) && !in_array('Paid',$psta)  && in_array('Unpaid',$psta) )
		{
			$ssql.= "AND (eCancelBy = 'Driver' AND ePassengerRefundPaid = 'No') OR (ePassengerRefundPaid = 'No' AND eDriverPaymentPaid = 'No' AND eCancelBy = '')";
		}
		
		//PRefund DRefund
	    else if(in_array('PRefund',$psta)  && in_array('DRefund',$psta) && !in_array('Paid',$psta)  && !in_array('Unpaid',$psta) )
		{
			$ssql.= "AND (eCancelBy = 'Driver' AND ePassengerRefundPaid = 'No') OR (eCancelBy = 'Passenger' AND eDriverRefundPaid = 'No')";
		}
		//DRefund
		else if(in_array('DRefund',$psta)  && !in_array('PRefund',$psta) && !in_array('Paid',$psta) && !in_array('Unpaid',$psta))
		{
			$ssql.= "AND (eCancelBy = 'Passenger' AND eDriverRefundPaid = 'No')";
		}
		//DRefund Paid
		else if(in_array('DRefund',$psta)  && !in_array('PRefund',$psta) && in_array('Paid',$psta) && !in_array('Unpaid',$psta))
		{
			$ssql.= "AND (eCancelBy = 'Passenger' AND eDriverRefundPaid = 'No') OR (eCancelBy = 'Driver' AND ePassengerRefundPaid = 'Yes') OR (eCancelBy = 'Passenger' AND eDriverPaymentPaid = 'Yes') OR (eCancelBy = '' AND eDriverPaymentPaid = 'Yes' AND ePassengerRefundPaid = 'No')";
		}
		//DRefund Unpaid
        else if(in_array('DRefund',$psta)  && !in_array('PRefund',$psta) && !in_array('Paid',$psta) && in_array('Unpaid',$psta))
		{
			$ssql.= "AND (eCancelBy = 'Passenger' AND eDriverRefundPaid = 'No') OR (ePassengerRefundPaid = 'No' AND eDriverPaymentPaid = 'No' AND eCancelBy = '')";
		}
		
		//Paid
		else if(in_array('Paid',$psta) && !in_array('DRefund',$psta)  && !in_array('PRefund',$psta) && !in_array('Unpaid',$psta))
		{
			$ssql.= "AND (eCancelBy = 'Driver' AND ePassengerRefundPaid = 'Yes') OR (eCancelBy = 'Passenger' AND eDriverPaymentPaid = 'Yes') OR (eCancelBy = '' AND eDriverPaymentPaid = 'Yes' AND ePassengerRefundPaid = 'No')";
		}
		//Paid Unpaid
		else if(in_array('Paid',$psta) && !in_array('DRefund',$psta)  && !in_array('PRefund',$psta) && in_array('Unpaid',$psta))
		{
			$ssql.= "AND (eCancelBy = 'Driver' AND ePassengerRefundPaid = 'Yes') OR (eCancelBy = 'Passenger' AND eDriverPaymentPaid = 'Yes') OR (eCancelBy = '' AND eDriverPaymentPaid = 'Yes' AND ePassengerRefundPaid = 'No') OR (ePassengerRefundPaid = 'No' AND eDriverPaymentPaid = 'No' AND eCancelBy = '')";
		}
	   
		//Unpaid
		else if(in_array('Unpaid',$psta) && !in_array('PRefund',$psta)  && !in_array('DRefund',$psta) && !in_array('Paid',$psta) )
		{
			$ssql.= "AND (ePassengerRefundPaid = 'No') AND (eDriverPaymentPaid = 'No') AND (eCancelBy = '')";
		}
		
		//PRefund DRefund Paid
	     else if(in_array('PRefund',$psta)  && in_array('DRefund',$psta) && in_array('Paid',$psta)  && !in_array('Unpaid',$psta) )
		{
			$ssql.= "AND (eCancelBy = 'Driver' AND ePassengerRefundPaid = 'No') OR (eCancelBy = 'Passenger' AND eDriverRefundPaid = 'No') OR (eCancelBy = 'Driver' AND ePassengerRefundPaid = 'Yes') OR (eCancelBy = 'Passenger' AND eDriverPaymentPaid = 'Yes') OR (eCancelBy = '' AND eDriverPaymentPaid = 'Yes' AND ePassengerRefundPaid = 'No')";
		}
		
		//PRefund DRefund Unpaid
		 else if(in_array('PRefund',$psta)  && in_array('DRefund',$psta) && !in_array('Paid',$psta)  && in_array('Unpaid',$psta) )
		{
			$ssql.= "AND (eCancelBy = 'Driver' AND ePassengerRefundPaid = 'No') OR (eCancelBy = 'Passenger' AND eDriverRefundPaid = 'No') OR (ePassengerRefundPaid = 'No' AND eDriverPaymentPaid = 'No' AND eCancelBy = '') OR (ePassengerRefundPaid = 'No' AND eDriverPaymentPaid = 'No' AND eCancelBy = '')";
		}
		
		//PRefund Paid Unpaid
		 else if(in_array('PRefund',$psta)  && !in_array('DRefund',$psta) && in_array('Paid',$psta)  && in_array('Unpaid',$psta) )
		{
			$ssql.= "AND (eCancelBy = 'Driver' AND ePassengerRefundPaid = 'No') OR (ePassengerRefundPaid = 'No' AND eDriverPaymentPaid = 'No' AND eCancelBy = '') OR (eCancelBy = 'Driver' AND ePassengerRefundPaid = 'Yes') OR (eCancelBy = 'Passenger' AND eDriverPaymentPaid = 'Yes') OR (eCancelBy = '' AND eDriverPaymentPaid = 'Yes' AND ePassengerRefundPaid = 'No')";
		}
		
		//DRefund Paid Unpaid
		else if(in_array('DRefund',$psta)  && !in_array('PRefund',$psta) && in_array('Paid',$psta) && in_array('Unpaid',$psta))
		{
			$ssql.= "AND (eCancelBy = 'Passenger' AND eDriverRefundPaid = 'No') OR (eCancelBy = 'Driver' AND ePassengerRefundPaid = 'Yes') OR (eCancelBy = 'Passenger' AND eDriverPaymentPaid = 'Yes') OR (eCancelBy = '' AND eDriverPaymentPaid = 'Yes' AND ePassengerRefundPaid = 'No') OR (ePassengerRefundPaid = 'No' AND eDriverPaymentPaid = 'No' AND eCancelBy = '')";
		}
		
		else if(in_array('Unpaid',$psta) && in_array('PRefund',$psta)  && in_array('DRefund',$psta) && in_array('Paid',$psta) )
		{
			$ssql.= " ";
		}
		
		else
		{
			$ssql .= '';
		}
	}  
}




if($sta != '')
{    
	$totid = count($sta);
  
	if(is_array($sta))
	{
		if(in_array('Amount',$sta) && !in_array('Commission',$sta) && !in_array('Refund',$sta))
		{
			$ssql.= "AND (fAmount != 0.00)";
		}
		else if(!in_array('Amount',$sta) && in_array('Commission',$sta) && !in_array('Refund',$sta))
		{
			$ssql.= "AND (fCommission != 0.00)";
		}
		else if(!in_array('Amount',$sta) && !in_array('Commission',$sta) && in_array('Refund',$sta))
		{
			$ssql.= "AND (fSiteRefundAmount != 0.00)";
		}
		else if(in_array('Amount',$sta) && in_array('Commission',$sta) && !in_array('Refund',$sta))
		{
			$ssql.= "AND (fAmount != 0.00 OR fCommission != 0.00)";
		}
		else if(in_array('Amount',$sta) && !in_array('Commission',$sta) && in_array('Refund',$sta))
		{
			$ssql.= "AND (fAmount != 0.00 OR fSiteRefundAmount != 0.00)";
		}
		else if(!in_array('Amount',$sta) && in_array('Commission',$sta) && in_array('Refund',$sta))
		{
			$ssql.= "AND (fCommission != 0.00 OR fSiteRefundAmount != 0.00)";
		}
		else if(in_array('Amount',$sta) && in_array('Commission',$sta) && in_array('Refund',$sta))
		{
			$ssql.= "AND (fAmount != 0.00 OR fCommission != 0.00 OR fSiteRefundAmount != 0.00)";
		}
	}
}
if($option != '' && $keyword != '')
{
    $ssql.= " AND ".addslashes($option)." LIKE '%".addslashes($keyword)."%'";
}

if($action != '')
{
  if($fdate!='' || $tdate!=''){
    $ssql.= " AND dBookingDate >='".$fdate." 00:00:00' AND dBookingDate <='".$tdate." 23:59:59'";
  }
}
 
if($alp != ''){
    $ssql.= " AND vBookingNo LIKE '".stripslashes($alp)."%'";
}
if($sortby == 1){

  if($order == 0)
  $ord.= " order by vBookingNo ASC";
  else
  $ord.= " order by vBookingNo DESC";
}
if($sortby == 3){

  if($order == 0)
  $ord.= " order by dBookingDate ASC";
  else
  $ord.= " order by dBookingDate DESC";
}
if($sortby == 4){ 
  if($order == 0)
  $ord.= " order by vMainRidePlaceDetails ASC";
  else
  $ord.= " order by vMainRidePlaceDetails DESC";
}
if($sortby == 6){ 
  if($order == 0)
  $ord.= " order by fAmount ASC";
  else
  $ord.= " order by fAmount DESC";
}
if($sortby == 6){ 
  if($order == 0)
  $ord.= " order by fAmount ASC";
  else
  $ord.= " order by fAmount DESC";
}
if($sortby == 8){ 
  if($order == 0)
  $ord.= " order by fCommission ASC";
  else
  $ord.= " order by fCommission DESC";
}
if($sortby == 9){ 
  if($order == 0)
  $ord.= " order by fSiteRefundAmount ASC";
  else
  $ord.= " order by fSiteRefundAmount DESC";
}

	$sql_admin = "select iBookingId,vBookingNo,vMainRidePlaceDetails,fAmount,dBookingDate,iBookerId,iDriverId,eStatus,eDriverPaymentPaid,eTransRequest,fCommission,fSiteRefundAmount,eStatus,iRideId,vBookerCurrencyCode,ePassengerRefundPaid,eDriverRefundPaid,eCancelBy from booking_new where 1 $ssql $ord";	
	$db_order = $obj->MySQLSelect($sql_admin);
	#echo "<pre>";print_r($db_order); exit;
	for($i=0;$i<count($db_order);$i++)
   {  
      $sq="select concat(vFirstName,' ',vLastName) as vMemberName from member where iMemberId='".$db_order[$i]['iDriverId']."'";
      $name=$obj->MySQLSelect($sq);
      $db_order[$i]["vMemberName"]=$name[0]["vMemberName"];
   }
   for($i=0;$i<count($db_order);$i++)
   {
       	  if($db_order[$i]["eCancelBy"] == 'Driver')
	   {
		    if($db_order[$i]["ePassengerRefundPaid"] == 'No')
		    {
			     $db_order[$i]["status"] = 'Passenger Refund';  
		    }
			  if($db_order[$i]["ePassengerRefundPaid"] == 'Yes')
		    {
			     $db_order[$i]["status"] = 'Paid';  
		    }
	  }
	 else if($db_order[$i]["eCancelBy"] == 'Passenger')
	  {
		      if($db_order[$i]["eDriverRefundPaid"] == 'No')
		    {
			     $db_order[$i]["status"] = 'Driver Refund';  
		    } 
			     if($db_order[$i]["eDriverRefundPaid"] == 'Yes')
		    {
			     $db_order[$i]["status"] = 'Paid';  
		    } 
	  }  
	  else
	  {
		  if($db_order[$i]["eDriverPaymentPaid"] == 'Yes' && $db_order[$i]["ePassengerRefundPaid"] == 'No')
		  {
		      $db_order[$i]["status"] = 'Paid';
		  }
		   if($db_order[$i]["eDriverPaymentPaid"] == 'No' && $db_order[$i]["ePassengerRefundPaid"] == 'No')
		  {
		       $db_order[$i]["status"] = 'Unpaid';
		  }
	  }
	  
   }
		
	$tot_driver_refund = 0.00;
	$tot_site_refund = 0.00;
	$tot_site_commission = 0.00;
	for($i=0;$i<count($db_order);$i++)
	{
		$sql = "SELECT fRatio_".$db_order[$i]['vBookerCurrencyCode']." as code, vBookerCurrencyCode as driver_curr FROM rides_new WHERE iRideId = '".$db_order[$i]['iRideId']."'";
		$db_booking_amount_ratio = $obj->MySQLSelect($sql);
    
		if(!isset($driver_currency))
		{
			$driver_currency = $db_booking_amount_ratio[0]['driver_curr'];
		}
     
		$driver_amount = number_format($db_order[$i]['fAmount'] / $db_booking_amount_ratio[0]['code'],2,'.','');
		$site_commission = number_format($db_order[$i]['fCommission'] / $db_booking_amount_ratio[0]['code'],2,'.','');
		$site_refund = number_format($db_order[$i]['fSiteRefundAmount'] / $db_booking_amount_ratio[0]['code'],2,'.','');  
		
		$tot_driver_refund = $tot_driver_refund + $db_order[$i]['fAmount']; 
		$tot_site_commission = $tot_site_commission + $db_order[$i]['fCommission'];
		$tot_site_refund = $tot_site_refund + $db_order[$i]['fSiteRefundAmount'];
	}

$header .= "Booking No."."\t";
$header .= "Member Name."."\t";
$header .= "Booking Date"."\t";
$header .= "Trip Details"."\t";
$header .= "Amount"."\t";
$header .= "Commision"."\t";
$header .= "Refund"."\t";
$header .= "Payment Status";

for($j=0;$j<count($db_order);$j++)
{   
    $data .= $db_order[$j]['vBookingNo']."\t";
	$data .= $db_order[$j]['vMemberName']."\t";
    $data .= $db_order[$j]['dBookingDate']."\t";
    $data .= $db_order[$j]['vMainRidePlaceDetails']."\t";
    $data .= $db_order[$j]['fAmount']."\t";
    $data .= $db_order[$j]['fCommission']."\t";
    $data .= $db_order[$j]['fSiteRefundAmount']."\t";
	$data .= $db_order[$j]['status']."\n";
} 
$data = str_replace( "\r" , "" , $data );
#echo "<br>".$data; exit;
ob_clean();
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=money_reports.xls");
header("Pragma: no-cache");
header("Expires: 0");
print "$header\n$data";
exit;
?>