<?php
session_start();
if($HTTP_SESSION_VARS['dbName']==""){
header("location:../index.php");
}

include("config.inc.php");

$connection = mysql_connect($config['dbServer'],$config['dbUser'],$config['dbPass']) or die("Could not connect to DB");
mysql_select_db($HTTP_SESSION_VARS['dbName'],$connection) or die("Could not find DB");

                $fromdate = $_GET['from_date'];
                $todate = $_GET['to_date'];
				$reportoption = $_GET['reportoption'];
                $physicaldamage = $_GET['physicaldamage'];
				$rmastatus = $_GET['rmastatus'];
                $complete = $_GET['complete'];
				$groupby = $_GET['groupby'];
				$orderby = $_GET['orderby'];
				$charge = $_GET['charge'];
				$warranty = $_GET['warranty'];
				$rmaresult = $_GET['rmaresult'];
				$serialnumber = $_GET['serialnumber'];
				$group = $_GET['group'];
				$problemcategory = $_GET['problemcategory'];
				$fromclient = $_GET['fromclient'];
				$toclient = $_GET['toclient'];
				$status = $_GET['status'];
				$combo = $_GET['combo'];
				$exp = $_GET['exp'];
				if($exp == 1){
					header("Content-Type:   application/vnd.ms-excel; charset=utf-8");
header("Content-type:   application/x-msexcel; charset=utf-8");
header("Content-Disposition: attachment; filename=RMA_Report.xls"); 
				}
   $fromdate = explode("/", $fromdate);
   $fromdate = $fromdate[2]."-".$fromdate[1]."-".$fromdate[0];
   
   $todate = explode("/", $todate);
   $todate = $todate[2]."-".$todate[1]."-".$todate[0];
   
   
echo"
<html>
<head>
<title></title>
<table align='center'>
<tr>
<td style='padding-left:0px;text-align:center; font-size:20px; colspan:2'><b> $reportoption </b></td>
<td style='padding-left:200px;text-align:center;'> Printed Date: $fromdate </td>
</tr>
<tr>
<td></td>
<td style='padding-left:200px;text-align:center;'>Time: ".date("H:i:s")."</td>
</tr>
</head>
<body>
<table>
<table width=40%;>
<tr>
<td><b> Date: </b></td>
<td style='padding-left:0px;text-align:center;'><b> From </b> </td>
<td style='padding-left:0px;text-align:center;'> $fromdate</td>
<td style='padding-left:0px;text-align:center;'><b> Client:</b> </td>
<td style='padding-left:0px;text-align:center;'> From</td> 
<td style='padding-left:0px;text-align:center;'> $fromclient</td>
<td style='padding-left:0px;text-align:center;'><b> Status:</b> $rmastatus</td>
<td style='padding-left:0px;text-align:center;'><b> Warranty:</b> $warranty</td>
</tr>
<tr>
<td></td>
<td style='padding-left:0px;text-align:center;'><b> To </b> </td>
<td style='padding-left:0px;text-align:center;'> $todate</td>
<td></td>
<td style='padding-left:0px;text-align:center;'>To</td>
<td style='padding-left:0px;text-align:center;'> $toclient</td>
</tr>";
/*
<table width=30%;>
<tr>
<td style='padding-left:50px;text-align:center;'><b> To </b> </td>
<td style='padding-left:0px;text-align:center;'> $todate</td>
<td style='padding-left:0px;text-align:center;'>To</td>
<td style='padding-left:0px;text-align:center;'> $toclient</td>
<td style='padding-left:0px;text-align:center;'><b> Warranty:</b> $warranty</td>
</tr>*/
echo"
<table width=130%;>
<br>
<tr>
<td style='padding-left:0px;text-align:center;'><u><b>Date</b></u></td>
<td style='padding-left:25px;text-align:center;'><u><b>Ref.</b></u></td>
<td style='padding-left:0px;text-align:center;'><u><b>Item Description</b></u></td>
<td style='padding-left:0px;text-align:center;'><u><b>Item SN</b></u></td>
<td style='padding-left:0px;text-align:center;'><u><b>Tel</b></u></td>
<td style='padding-left:20px;text-align:center;'><u><b>S</b></u></td>
<td style='padding-left:20px;text-align:center;'><u><b>S2</b></u></td>
<td style='padding-left:20px;text-align:center;'><u><b>Charge</b></u></td>
<td style='padding-left:20px;text-align:center;'><u><b>LastTrack.History</b></u></td>
<td style='padding-left:20px;text-align:center;'><u><b>Comp.</b></u></td>
<td style='padding-left:20px;text-align:center;'><u><b>W</b></u></td>
<td style='padding-left:20px;text-align:center;'><u><b>Inv#</b></u></td>
<td style='padding-left:20px;text-align:center;'><u><b>Client Name:</b></u></td>
</tr>
";

$query_rma = "SELECT date_format(RMADT.RMA_DATE,'%d/%m/%Y') RMA_DATE1 ,RMA.RMA_REFERENCE, RMADT.ITEM_CODE,ITEMS.ITEM_DESCRIPTION ,RMA.CLIENT_CODE , CLIENTS.LEDGER_NAME, ";
if ($rmastatus == "Handover") //rmastatus handover
    $query_rma = $query_rma." 'H'	rmastatus ";
else
if ($rmastatus == "Pending") //rmastatus pending
    $query_rma = $query_rma." 'P' rmastatus ";
else
if ($rmastatus == "All") //rmastatus all
    $query_rma = $query_rma. " if((select 1 from rmadt as rmadt3 where rmadt3.mi_id = rmadt.rmadt_id)=1,'H','P') rmastatus ";
	
	$query_rma = $query_rma." ,RMA.CLIENT_CODE ,clients.ledger_name , ";

    $query_rma = $query_rma." (select rmadt2.rmadt_repaire from rmadt as rmadt2 where rmadt2.mi_id = rmadt.rmadt_id) rmadt_repaire, ";

    $query_rma = $query_rma." rmadt.Serial_Number , RMA_PromisedDate,RMADT.RMA_DATE,rma.rma_id , date_format(RMA_PromisedDate,'%d-%m-%Y') RMA_PromisedDate";

    $query_rma = $query_rma.",CLIENTS.Client_Telephone1,trackinghistory.description , if( items.Item_NonStock =1,SUBSTR(rmadt.rmadt_remark,1,100),"
." concat(items.item_code,'-',items.item_description)) as rmadt_remark  , rma.rma_remark ,Invoice_Number,if(Completed=1,'Yes','NO') completed,rmadt_price,"
. " if(clients.client_cashmode=1,rma.rma_remark,'') cashclient , rma.client_tel , if(RMADT.rmadt_warranty=1,'Y','N')  rmadt_warranty,rmadt_status FROM RMADT "
." INNER JOIN RMA ON RMA.RMA_ID = RMADT.RMA_ID ";

    $query_rma = $query_rma." and rma.rma_date between '$fromdate' and '$todate 23:59:59'";
	
	if($warranty == "No Warranty"){
		$query_rma = $query_rma." and Rmadt_warranty = 0 ";
	}
	else
		if($warranty == "Warranty"){
		$query_rma = $query_rma." and Rmadt_warranty = 1 ";
	}
	else{
		$query_rma = $query_rma." and (Rmadt_warranty = 0  or Rmadt_warranty = '1' or Rmadt_warranty='')";
	}
	if($rmastatus == "Pending"){
		$query_rma = $query_rma." AND (RMADT.RMA_TYPE = 'MI' and not exists(select 1 from rmadt as rmadt1 where rmadt1.rma_type = 'MR'";
		$query_rma = $query_rma." and rmadt1.mi_id = rmadt.rmadt_id";
		if($rmaresult == "Unrepaired"){
			$query_rma = $query_rma." AND RMADT1.RMADt_Repaire = 'U'";
		}
		else
			if($rmaresult == "REPAIRED"){
				$query_rma = $query_rma." AND RMADT1.RMADt_Repaire = 'R'";
			}
			else
				if($rmaresult == "RETURNED"){
					$query_rma = $query_rma." AND RMADT1.RMADt_Repaire = 'X'";
				}
	  $query_rma = $query_rma." ))";
	  if($complete == "COMPLETED"){
		  $query_rma = $query_rma." and exists(select 1 from TrackingHistory where rma_id = rmadt.rmadt_id  and completed=1 )";
	  }
	  else
		  if($complete == "NOTCOMPLETED"){
			  $query_rma = $query_rma." and not exists(select 1 from TrackingHistory where rma_id = rmadt.rmadt_id and completed=1)";
		  }
	
	}
	
	if($rmastatus == "Handover"){
		$query_rma = $query_rma." AND (exists(select 1 from rmadt as rmadt1 where rmadt1.rma_type = 'MR'";
		$query_rma = $query_rma." and rmadt1.mi_id = rmadt.rmadt_id ";
		if($rmaresult == "Unrepaired"){
			$query_rma = $query_rma." AND RMADT1.RMADt_Repaire = 'U'";
		}
		else
			if($rmaresult == "REPAIRED"){
				$query_rma = $query_rma." AND RMADT1.RMADt_Repaire = 'R'";
			}
			else
				if($rmaresult == "RETURNED"){
					$query_rma = $query_rma." AND RMADT1.RMADt_Repaire = 'X'";
				}
		$query_rma = $query_rma." ))";
	}
	
	if($rmastatus == "All"){
		$query_rma = $query_rma." AND ((RMADT.RMA_TYPE = ''MI'' and not exists(select 1 from rmadt as rmadt1 where rmadt1.rma_type = 'MR'";
		$query_rma = $query_rma." and rmadt1.mi_id = rmadt.rmadt_id ))";
		$query_rma = $query_rma. " or (exists(select 1 from rmadt as rmadt1 where rmadt1.rma_type = ''MR'' and rmadt1.mi_id = rmadt.rmadt_id )) )";
	}
	
	$query_rma = $query_rma." INNER JOIN CLIENTS ON RMADT.CLIENT_CODE = CLIENTS.LEDGER_NUMBER ";
	if($fromclient != "" && $toclient!= ""){
		$query_rma = $query_rma." AND RMADT.CLIENT_CODE between '$fromclient' and '$toclient'";
	}
	
	$query_rma = $query_rma." INNER JOIN ITEMS ON RMADT.ITEM_CODE = ITEMS.ITEM_CODE";
	$query_rma = $query_rma." left join trackinghistory on trackinghistory.rma_id = rmadt.rmadt_id ";
	$query_rma = $query_rma." and trackinghistory.trackingdate = (select max(trackingdate) from trackinghistory where trackinghistory.rma_id=rmadt.rmadt_id) where 1=1 ";
	
	if($combo == "credit"){
		$query_rma = $query_rma." and rmadt_status='C'";
	}
	else
		if($combo == "replace"){
			$query_rma = $query_rma." and rmadt_status='P'";
		}
		else
			if($combo == "reject"){
				$query_rma = $query_rma." and rmadt_status='X'";
			}
	
	if($orderby == "DATE"){
		$query_rma = $query_rma." ORDER BY date_format(RMADT.RMA_DATE,'%Y%m%d') , rma_reference";
	}
	else{
		$query_rma = $query_rma." ORDER BY  date_format(RMA_PromisedDate,'%Y%m%d'), rma_reference";
	}
	//echo $query_rma;
	//exit;
    $result_rma = mysql_query($query_rma, $connection);
	
while($row_rma = mysql_fetch_array($result_rma)){
	
	extract($row_rma);
	echo "<tr>
<td style='padding-left:0px;text-align:center;'>$RMA_DATE1</td>
<td style='padding-left:20px;text-align:center;'>$RMA_REFERENCE</td>
<td style='padding-left:0px;text-align:center;'>$ITEM_DESCRIPTION</td>
<td style='padding-left:0px;text-align:center;'>$Serial_Number</td>
<td style='padding-left:0px;text-align:center;'>$Client_Telephone1</td>
<td style='padding-left:20px;text-align:center;'>$rmastatus</td>
<td style='padding-left:20px;text-align:center;'>$rmadt_repaire</td>
<td style='padding-left:20px;text-align:right;'>$rmadt_price</td>
<td style='padding-left:20px;text-align:center;'>$description</td>
<td style='padding-left:20px;text-align:center;'>$completed</td>
<td style='padding-left:20px;text-align:center;'>$rmadt_warranty</td>
<td style='padding-left:20px;text-align:center;'>$Invoice_Number</td>
<td style='padding-left:20px;text-align:center;'>$rma_remark</td>
</tr>";
}

echo"

</table>
</body>
</html>";
?>