<?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'];
   $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>
<h3 align='center'><u> RMA REPORT PREVIEW </u> </h3>
</head>
<body>
<table>
<tr>
<td> F.Date </td>
<td style='padding-left:20px'> To.Date </td>
<td style='padding-left:30px'> Data </td>
</tr>
<tr>
<td>$fromdate</td>
<td>$todate</td>
</tr>
</table>
<hr>
<table width=900 border=1>
<tr>
<th width=10%>RMA Ref.</th>
<th width=20%>Client Code</th>
<th width=20%>RMA Date</th>
<th width=50%>Remark</th>
</tr>";

sql := ' 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 REPORTSOPTION.radiobutton9.Checked then //rmastatus handover
    sql := sql + ' ''H'' rmastatus '
else
if REPORTSOPTION.radiobutton15.Checked then //rmastatus pending
    sql := sql + ' ''P'' rmastatus '
else
if REPORTSOPTION.radiobutton10.Checked then //rmastatus all
    sql := sql + 'if((select 1 from rmadt as rmadt3 where rmadt3.mi_id = rmadt.rmadt_id)=1,''H'',''P'') rmastatus ';


sql := sql+' ,RMA.CLIENT_CODE ,clients.ledger_name , ';

sql := sql + ' (select rmadt2.rmadt_repaire from rmadt as rmadt2 where rmadt2.mi_id = rmadt.rmadt_id) rmadt_repaire, ';

 sql := sql + ' rmadt.Serial_Number , RMA_PromisedDate,RMADT.RMA_DATE,rma.rma_id , date_format(RMA_PromisedDate,''%d-%m-%Y'') RMA_PromisedDate';

sql := sql +',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 ';

sql := sql +' AND RMA.RMA_DATE  >= '''+formatdatetime('yyyy-mm-dd hh:mm:ss',REPORTSOPTION.RmaDateFrom.DateTime)+'''';

sql := sql +' AND RMA.RMA_DATE  <= '''+formatdatetime('yyyy-mm-dd hh:mm:ss',REPORTSOPTION.RmaDateto.DateTime)+'''';

if reportsoption.warrantyradiogroup.ItemIndex = 0 then  //with warranty
sql := sql +' and Rmadt_warranty = 1 ';
if reportsoption.warrantyradiogroup.ItemIndex = 1 then //withOUT warranty
sql := sql +' and Rmadt_warranty = 0  or Rmadt_warranty = ''''';



if (REPORTSOPTION.radiobutton15.Checked) then //rmastatus pending
  begin
sql := sql +'  AND (RMADT.RMA_TYPE = ''MI'' and not exists(select 1 from rmadt as rmadt1 where rmadt1.rma_type = ''MR''';
sql := sql +' and rmadt1.mi_id = rmadt.rmadt_id ';
if (REPORTSOPTION.radiobutton9.Checked and REPORTSOPTION.radiobutton18.Checked) then  //rmastatus handover AND RMA RESULT UNREPAIRD
sql := sql +'  AND RMADT1.RMADt_Repaire = ''U''';
if (REPORTSOPTION.radiobutton9.Checked and REPORTSOPTION.radiobutton19.Checked) then  //rmastatus handover AND RMA RESULT REPAIRD
sql := sql +' AND RMADT1.RMADt_Repaire = ''R''';
if (REPORTSOPTION.radiobutton9.Checked and REPORTSOPTION.radiobutton20.Checked) then  //rmastatus handover AND RMA RESULT RETURN
sql := sql +' AND RMADT1.RMADt_Repaire = ''X''';

sql := sql + '))';


  if REPORTSOPTION.RadioGroupPending.ItemIndex=0 then   //COMPLETED
      sql := sql + ' and exists(select 1 from TrackingHistory where rma_id = rmadt.rmadt_id  and completed=1 )'
  else
  if REPORTSOPTION.RadioGroupPending.ItemIndex=1 then  //NOT COMPLETED
      sql := sql + ' and not exists(select 1 from TrackingHistory where rma_id = rmadt.rmadt_id and completed=1)';
  end;




  if (REPORTSOPTION.radiobutton9.Checked) then   //rmastatus handover
  begin
		sql := sql +'  AND (exists(select 1 from rmadt as rmadt1 where rmadt1.rma_type = ''MR''';
		sql := sql +' and rmadt1.mi_id = rmadt.rmadt_id ';
		if (REPORTSOPTION.radiobutton9.Checked and REPORTSOPTION.radiobutton18.Checked) then  //rmastatus handover   AND RMA RESULT UNREPAIRD
			sql := sql +'  AND RMADT1.RMADt_Repaire = ''U''';
		if (REPORTSOPTION.radiobutton9.Checked and REPORTSOPTION.radiobutton19.Checked) then //rmastatus handover   AND RMA RESULT REPAIRD
			sql := sql +' AND RMADT1.RMADt_Repaire = ''R''';
		if (REPORTSOPTION.radiobutton9.Checked and REPORTSOPTION.radiobutton20.Checked) then //rmastatus handover   AND RMA RESULT RETURN
			sql := sql +' AND RMADT1.RMADt_Repaire = ''X''';


sql := sql + '))';
 end;

     if (REPORTSOPTION.radiobutton10.Checked) then //rmastatus ALL
     begin
			sql := sql + 'AND ((RMADT.RMA_TYPE = ''MI'' and not exists(select 1 from rmadt as rmadt1 where rmadt1.rma_type = ''MR'' ';
			sql := sql + 'and rmadt1.mi_id = rmadt.rmadt_id )) ';
			sql := sql + 'or ';
			sql := sql + ' (exists(select 1 from rmadt as rmadt1 where rmadt1.rma_type = ''MR'' and rmadt1.mi_id = rmadt.rmadt_id )) ) ';;
    end;

	sql := sql +' INNER JOIN CLIENTS ON RMADT.CLIENT_CODE = CLIENTS.LEDGER_NUMBER ';
	if (fromclient <> '') then
		sql := sql +' AND (RMADT.CLIENT_CODE >='+fromclient+' OR RMADT.CLIENT_CODE IS NULL) ';
	if (toclient <> '') then
		sql := sql +' AND (RMADT.CLIENT_CODE <= '+toclient+' OR RMADT.CLIENT_CODE IS NULL) ' ;

	sql := sql +' INNER JOIN ITEMS ON RMADT.ITEM_CODE = ITEMS.ITEM_CODE ';


	sql := sql +' left join trackinghistory on trackinghistory.rma_id = rmadt.rmadt_id ';
	sql := sql +' and trackinghistory.trackingdate = (select max(trackingdate) from trackinghistory where trackinghistory.rma_id=rmadt.rmadt_id)';
	sql := sql +'  where 1=1 ';
	if reportsoption.CheckBoxStatus.checked then begin //STATUS FILTER BY
		if reportsoption.ComboBoxStatus.ItemIndex=0 then
			sql:=sql+' and rmadt_status=''C''';
		if reportsoption.ComboBoxStatus.ItemIndex=1 then
			sql:=sql+' and rmadt_status=''P''';
		if reportsoption.ComboBoxStatus.ItemIndex=2 then
			sql:=sql+' and rmadt_status=''X''';
end;

if reportsoption.CostCenterTrial.text<>'' then begin
  if reportsoption.CostCenterChilds.checked then
      sql := sql +' and rma.costcent_code like '''+reportsoption.CostCenterTrial.text+'%'''
  else
      sql := sql +' and rma.costcent_code = '''+reportsoption.CostCenterTrial.text+'''';
end;
if reportsoption.ProjectTrial.text<>'' then begin
  if reportsoption.ProjectChilds.checked then
      sql := sql +' and rma.project_code like '''+reportsoption.ProjectTrial.text+'%'''
  else
      sql := sql +' and rma.project_code = '''+reportsoption.ProjectTrial.text+'''';
end;

if reportsoption.RadioGroupOrderby.ItemIndex=0 then  //ORDER BY 
  if reportsoption.groupbyclient.ItemIndex=1 then   //DATE
  sql := sql +' ORDER BY client_code , date_format(RMADT.RMA_DATE,''%Y%m%d'') , rma_reference;'
  else
  sql := sql +' ORDER BY date_format(RMADT.RMA_DATE,''%Y%m%d'') , rma_reference;'
else
  if reportsoption.groupbyclient.ItemIndex=1 then  //GROUP BY CLIENT
  sql := sql +' ORDER BY  client_code , date_format(RMA_PromisedDate,''%Y%m%d''), rma_reference;'
  else
    sql := sql +' ORDER BY  date_format(RMA_PromisedDate,''%Y%m%d''), rma_reference;';



$query_rma  = "select RMA_Reference, Client_Code, date_format(RMA_Date, '%d/%m/%Y') as RMA_Date, Rma_Remark
               from rma
			   where rma_type = 'MI' and RMA_Date between '$fromdate' and '$todate 23:59:59'";
$result_rma = mysql_query($query_rma, $connection);
while($row_rma = mysql_fetch_array($result_rma)){
	extract($row_rma);
	echo "<tr>
<th width=10%>$RMA_Reference</th>
<th width=20%>$Client_Code</th>
<th width=20%>$RMA_Date</th>
<th width=50%>$Rma_Remark</th>
</tr>";
}
echo"
</table>
</body>
</html>";
?>