<?php
require_once('include/db-config.php');
$from=mysqli_real_escape_string($conn,$_POST['from']);
$to=mysqli_real_escape_string($conn,$_POST['to']);

$from= date('Y/m/d H:i:s', strtotime(str_replace('/', '-', $from)));
$to= date('Y/m/d H:i:s', strtotime(str_replace('/', '-', $to)));

$amonut_from=$_POST['a_from'];
$amonut_to=$_POST['a_to'];
$invoice_number=$_POST['invoice_number'];
$company=$_POST['company'];
// $tax_from=$_POST['tax_from'];
// $tax_to=$_POST['tax_to'];

// $from='2011/02/24';
// $to='2012/02/26';

//$DB_TBLName = "tablename"; //MySQL Table Name

$filename = "Invoice Report";         //File Name

/*******YOU DO NOT NEED TO EDIT ANYTHING BELOW THIS LINE*******/

//create MySQL connection

// $sql = "SELECT e.EmployeeID,e.FirstName,e.LastName,e.JoiningDate,e.DOB,e.Gender,e.DesignationID,e.EmailAddress,e.PresentAddress,e.profile_pic,e.resume, d.DesignationName FROM axel_employee_master e,designation_master d where e.DesignationID=d.DesignationID Order By FirstName ASC";
$sql = "select tb1.ID AS 'Invoice ID', tb1.OrderDate as 'Invoice. Date', tb1.ShippingStatus as 'Shipping Status' , tb1.PaymentStatus as 'Payment Status', tb1.InvoiceSent2Customer as Mail, tb2.Billing_FirstName as 'First Name',tb2.Billing_LastName as 'Last Name',tb2.Shipping_EmailID as 'Email ID', tb4.ProductName as Product, tb4.ProductPrice as 'Product Price($)', tb3.ShippingPrice as 'Shipping Price($)', tb3.TotalProductPrice as 'Amount($)' from maintrainer_tbl_cart_master tb1 left join maintrainer_tbl_customer_information tb2 on tb1.ID = tb2.CartMasterID left join maintrainer_tbl_cart_payment tb3 on tb1.ID =  tb3.CartMasterID left join maintrainer_tbl_cart_details tb4 on tb1.ID =  tb4.CartMasterID where ";
		if($from && $to)
		{
			$sql.=" (tb1.OrderDate >='$from' and tb1.OrderDate <= '$to') ";
		}
		if($amonut_from && $amonut_to)
		{
			if($from && $to)
			{
				$sql.=" and (tb3.TotalProductPrice >='$amonut_from' and tb3.TotalProductPrice <= '$amonut_to') ";
			}
			else
			{
			$sql.=" (tb3.TotalProductPrice >='$amonut_from' and tb3.TotalProductPrice <= '$amonut_to') ";
			}
		}
		if($invoice_number)
		{
			if(($from && $to) or ($amonut_from && $amonut_to))
			{
				$sql.=" and tb1.ID ='$invoice_number' ";
			}
			else {
				$sql.=" tb1.ID ='$invoice_number' ";
			}
			
		}
		if($company)
		{
			if(($from && $to) or ($amonut_from && $amonut_to) or ($invoice_number))
			{
				$sql.=" and (tb2.Billing_Company ='$company' or tb2.Shipping_Company  ='$company') ";
			}
			else {
				$sql.=" (tb2.Billing_Company ='$company' or tb2.Shipping_Company  ='$company') ";
			}
		}
		
		$sql.=" order by tb1.OrderDate asc";
		
	/*	if($tax_from && $tax_to)
		{
			$sql.="where tb1.OrderDate >='$from' and tb1.OrderDate <= '$to' order by tb1.OrderDate asc";
		}
	*/	
//$Connect = @mysqli_connect($DB_Server, $DB_Username, $DB_Password)

    //or die("Couldn't connect to MySQL:<br>" . mysqli_error() . "<br>" . mysqli_errno());

//select database
	
//$Db = @mysqli_select_db($DB_DBName, $Connect)

    //or die("Couldn't select database:<br>" . mysqli_error(). "<br>" . mysqli_errno());

//execute query
	
$result = mysqli_query($conn,$sql)

    or die("Couldn't execute query:<br>" . mysqli_error(). "<br>" . mysqli_errno());

$file_ending = "xls";
 
//header info for browser
@header("Content-Type: application/xls");
@header("Content-Disposition: attachment; filename=$filename.xls");
@header("Pragma: no-cache");
@header("Expires: 0");

 
/*******Start of Formatting for Excel*******/

//define separator (defines columns in excel & tabs in word)

$sep = "\t"; //tabbed character

 
	
//start of printing column names as names of MySQL fields

for ($i = 0; $i < mysqli_num_fields($result); $i++) {

echo mysqli_field_name($result,$i) . "\t";

}
	
print("\n");

//end of printing column names

 	
//start while loop to get data
$SHIPID=0;
$flag=0;
    while($row = mysqli_fetch_row($result))

    {
	
        $schema_insert = "";
	
        for($j=0; $j<mysqli_num_fields($result);$j++)

        {
     		$test=mysqli_field_name($result,$j);
			if($test=='Invoice ID')
        	{
        		if($SHIPID != $row[$j])
				{
        			$SHIPID=$row[$j];
        			$flag=0;
				}
				else {
					$row[$j]='"';
					$flag=1;
				}
				
			}
			
			if($test=='Shipping Status')
        	{
        		
        			$ShippingStatus = $row[$j];
					if($ShippingStatus == 1)
						$ShippingStatus_lbl = "Incomplete";
					else if($ShippingStatus == 2)
						$ShippingStatus_lbl = "Pending";
					else if($ShippingStatus == 3)
						$ShippingStatus_lbl = "Processing";
					else if($ShippingStatus == 4)
						$ShippingStatus_lbl = "Processed";
					else if($ShippingStatus == 5)
						$ShippingStatus_lbl = "Shipping";
					else if($ShippingStatus == 6)
						$ShippingStatus_lbl = "Shipped";
					else if($ShippingStatus == 7)
						$ShippingStatus_lbl = "Partially Shipped";
					else if($ShippingStatus == 8)
						$ShippingStatus_lbl = "Return";
					else if($ShippingStatus == 9)
						$ShippingStatus_lbl = "Partially Returned";
					else if($ShippingStatus == 10)
						$ShippingStatus_lbl = "Will Not Deliver";
					else if($ShippingStatus == 11)
						$ShippingStatus_lbl = "Delivered";
					else if($ShippingStatus == 12)
						$ShippingStatus_lbl = "Cancelled";
					
					$row[$j]=$ShippingStatus_lbl;
					}
					
			elseif($test=='Payment Status')
        	{
					$PaymentStatus_lbl = "";
					$PaymentStatus = $row[$j];
					if($PaymentStatus == 1)
						$PaymentStatus_lbl = "Incomplete";
					else if($PaymentStatus == 2)
						$PaymentStatus_lbl = "Pending";
					else if($PaymentStatus == 3)
						$PaymentStatus_lbl = "Processing";
					else if($PaymentStatus == 4)
						$PaymentStatus_lbl = "Processed";
					else if($PaymentStatus == 5)
						$PaymentStatus_lbl = "Will not Settle";
					else if($PaymentStatus == 6)
						$PaymentStatus_lbl = "Rejected";
					else if($PaymentStatus == 7)
						$PaymentStatus_lbl = "Returned";
					else if($PaymentStatus == 8)
						$PaymentStatus_lbl = "Completed";
					
					$row[$j]=$PaymentStatus_lbl;
			}
			elseif ($test=='Mail') 
			{
				if($row[$j]=='1')
				{
					$row[$j]='Sent';
				}
				else {
					$row[$j]='';
				}
	
			}
			elseif ($test=='Invoice. Date') 
			{
				$row[$j]=date("M-d-Y",strtotime($row[$j]));
	
			}
			if($flag == 1)
			{
				if($test=='Shipping Status')
				{$row[$j]='';}
				if($test=='Payment Status')
				{$row[$j]=' ';}
				if($test=='Mail')
				{$row[$j]=' ';}
				if($test=='First Name')
				{$row[$j]=' ';}
				if($test=='Last Name')
				{$row[$j]=' ';}
				if($test=='Email ID')
				{$row[$j]=' ';}
				if($test=='Shipping Price($)')
				{$row[$j]=' ';}
				if($test=='Amount($)')
				{$row[$j]=' ';}
				if($test=='Invoice. Date')
				{$row[$j]=' ';}
			}
            if(!isset($row[$j]))
	
                $schema_insert .= "NULL".$sep;

            elseif ($row[$j] != "")
	
                $schema_insert .= "$row[$j]".$sep;
	
            else

                $schema_insert .= "".$sep;
	
        }

        $schema_insert = str_replace($sep."$", "", $schema_insert);
 $schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
		
        $schema_insert .= "\t";
		
		print(trim($schema_insert));
     print "\n";
	
    }
	
?>