<?php
require_once('Spreadsheet/Excel/Writer.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");


//Creating a workbook
$workbook = new Spreadsheet_Excel_Writer();

//Sending HTTP headers
$workbook->send('Exp_RMATracking.xls');
 
//Creating a worksheet
$worksheet=&$workbook->addWorksheet("Time Tracking Report");
$worksheet->setLandscape();

//set all columns same width
$columnWidth = 10;

//Title Format 
$sheetTitleFormat =& $workbook->addFormat(array('bold'=>1,
  'size'=>10));

//Column Title Format  
$columnTitleFormat =& $workbook->addFormat(array('bold'=>1,
  'top'=>1,
  'bottom'=>1 ,
  'size'=>15,
  'color'=>0x0A,
  ));
  
 
  
// Regular Format
$regularleftFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'left'  ));
$regularrightFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'right'  ));  
$regularrightFormatNum =& $workbook->addFormat(array('size'=>9,
  'align'=>'right'  )); 
$regularcenterFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'center'  ));    
  
$regularleftFormat1 =& $workbook->addFormat(array('size'=>8,
  'align'=>'left'  ));
$regularrightFormat1 =& $workbook->addFormat(array('size'=>8,
  'align'=>'right'  ));    
$regularrightFormat1Num =& $workbook->addFormat(array('size'=>8,
  'align'=>'right'  ));    
  
$TitleFormat =& $workbook->addFormat(array('size'=>11,
  'align'=>'left',
  'bold'=>1
  ));

$boldleftFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'left'  ,
  'valign'=>'top'  ,
  'bold'=>1
  ));  
$boldrightFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'right'  ,
  'bold'=>1
  )); 
$boldrightFormatNum =& $workbook->addFormat(array('size'=>9,
  'align'=>'right'  ,
  'bold'=>1
  )); 
$boldcenterFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'center'  ,
  'valign'=>'top'  ,
  'bold'=>1
  ));    
  
$grouplevel1Format =& $workbook->addFormat(array('size'=>10,
  'align'=>'left',
  'bold'=>1,
  'color'=>0x0C
  )); 
$grouplevel2Format =& $workbook->addFormat(array('size'=>10,
  'align'=>'left',
  'bold'=>0,
  'color'=>0x0A
  )); 
$grouplevel3Format =& $workbook->addFormat(array('size'=>9,
  'align'=>'left',
  'italic'=>1,
  'color'=>0x11
  ));   
$grouplevel1totalFormat =& $workbook->addFormat(array('size'=>10,
  'align'=>'right',
  'bold'=>1,
  'color'=>0x0C
  ));  
$grouplevel2totalFormat =& $workbook->addFormat(array('size'=>10,
  'align'=>'right',
  'bold'=>0,
  'color'=>0x0A
  ));  
$grouplevel3totalFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'right',
  'italic'=>1,
  'color'=>0x11
  ));   

$seloptionFormat =& $workbook->addFormat(array('size'=>9,
  'align'=> 'left'  ,
  'valign'=> 'top'  ,
  'bold'=> 1
  ));
$seloptionFormat->setTextWrap();

$regularrightFormatNum->setNumFormat('_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)');
$regularrightFormat1Num->setNumFormat('_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)');
$boldrightFormatNum->setNumFormat('_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)');
$grouplevel1totalFormat->setNumFormat('_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)');

$fromdate = $_GET['fromdate'];
$todate = $_GET['todate'];
$itemCode = $_GET['item'];
$clientCode = $_GET['client'];
$rmaRef = $_GET['ref'];
$rmaType = $_GET['type'];
if($rmaType == 1) $rmaType = "MR"; else if($rmaType == 2) $rmaType = "MI";

$fromdate = explode("/", $fromdate);
$fromdate = $fromdate[2]."-".$fromdate[1]."-".$fromdate[0];

$todate = explode("/", $todate);
$todate = $todate[2]."-".$todate[1]."-".$todate[0];

$SelectedOptions = "Filter by:";
$worksheet->write(2,2,$SelectedOptions, $boldleftFormat);
$worksheet->write(2,3,"-> Date From: ". $fromdate . "  To: ". $todate, $boldleftFormat);
if($clientCode != "")
    $worksheet->write(3,3,"-> Client: ". $clientCode, $boldleftFormat);
if($itemCode != "")
    $worksheet->write(3,3,"-> Item: ". $itemCode, $boldleftFormat);
if($rmaRef != ""){
    $worksheet->write(4,3,"-> RMA Ref.: ". $rmaRef, $boldleftFormat);
    $worksheet->write(5,3,"-> RMA Type: ". $rmaType, $boldleftFormat);
}

$worksheet->write(1,13,"Printed Date:", $boldleftFormat);
$worksheet->write(1,14,date('d-m-Y'), $regularleftFormat);
$worksheet->write(2,13,"Time:", $boldleftFormat);		
$worksheet->write(2,14,date("H:i:s"), $regularleftFormat);
$worksheet->write(3,13,"Unit of Measure:", $boldleftFormat);
$worksheet->write(3,14,"Minutes", $regularleftFormat);

$RowNum    = 6;
$ColumnNum = 0;	   

$worksheet->setColumn ($ColumnNum, $ColumnNum, 20);
$worksheet->write($RowNum,$ColumnNum,"RMA Ref.", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 20);
$worksheet->write($RowNum,$ColumnNum,"Group", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 20);
$worksheet->write($RowNum,$ColumnNum,"Item Code", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 35);
$worksheet->write($RowNum,$ColumnNum,"Item Desc.", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 25);
$worksheet->write($RowNum,$ColumnNum,"Client Name", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Problem", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Quotation", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 8);
$worksheet->write($RowNum,$ColumnNum,"RMA Type", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 20);
$worksheet->write($RowNum,$ColumnNum,"Creation to Reception", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 20);
$worksheet->write($RowNum,$ColumnNum,"Reception to Eng. Recpt.", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 20);
$worksheet->write($RowNum,$ColumnNum,"Eng Recpt. to Quotation", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 20);
$worksheet->write($RowNum,$ColumnNum,"Quotation to Op. Approve", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 20);
$worksheet->write($RowNum,$ColumnNum,"Op. Approve to Repair Start", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 20);
$worksheet->write($RowNum,$ColumnNum,"First Repair", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 20);
$worksheet->write($RowNum,$ColumnNum,"Eng. to QC rcp.", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 20);
$worksheet->write($RowNum,$ColumnNum,"QC rcp. to begin check", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 20);
$worksheet->write($RowNum,$ColumnNum,"QC check", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 20);
$worksheet->write($RowNum,$ColumnNum,"QC to Approved", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 20);
$worksheet->write($RowNum,$ColumnNum,"QC to Second Repair Start", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 20);
$worksheet->write($RowNum,$ColumnNum,"Second Repair", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 20);
$worksheet->write($RowNum,$ColumnNum,"Eng. end to Qc start", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 20);
$worksheet->write($RowNum,$ColumnNum,"Second QC Check", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 20);
$worksheet->write($RowNum,$ColumnNum,"Second QC to Approved", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 20);
$worksheet->write($RowNum,$ColumnNum,"Approved to Return", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 20);
$worksheet->write($RowNum,$ColumnNum,"Total", $boldcenterFormat);

$query = "SELECT rma.rma_reference AS RMARef,
            rma.rma_id AS RMAId,
            rma.rma_remark AS clientName,
            rma.rma_type AS RMAType,
            rma.rma_date AS RMADate,
            items.Group_Code AS groupCode,
            items.item_description AS itemDesc,
            rmadt.rmadt_id AS RMADtId,
            rmadt.item_code AS itemCode,
            rmadt.rmadt_problem AS rmadtProblem,
            rmadt.rmadt_DateReceived AS dateReceived,
            rmadt.rmadt_receivedby AS receivedBy,
            rmadt.rmadt_techquotation AS rmadtQuotation,
            rmadt.ramdt_techquotationdate AS rmadtQuotationDate,
            rmadt.rmadt_approved AS rmadtApproved,
            rmadt.rmadt_approvedDate AS rmadtApprovedDate,
            rmadt.rmadt_engrecieved AS rmadtEngRecieved,
            rmadt.rmadt_engrecieved_date AS rmadtEngRecievedDate,
            rmadt.rmadt_repStartDateTime AS rmadtRepairStartDate,
            rmadt.rmadt_repEndDateTime AS rmadtRepairEndDate,
            rmadt.rmadt_qcrecieved AS rmadtQcRecieved,
            rmadt.rmadt_qcrecieved_date AS rmadtQcRecievedDate,
            rmadt.rmadt_qcStartDateTime AS rmadtQcStartDate,
            rmadt.rmadt_qcEndDateTime AS rmadtQcEndDate,
            rmadt.rmadt_qcduration AS rmadtQcDuration,
            rmadt.rmadt_qcapproved AS rmadtQcApproved,
            rmadt.rmadt_qcapproveddate AS rmadtQcApprovedDate,
            rmadt.rmadt_repStartDateTime2 AS rmadtRepairStartDate2,
            rmadt.rmadt_repEndDateTime2 AS rmadtRepairEndDate2,
            rmadt.rmadt_qcStartDateTime2 AS rmadtQcStartDate2,
            rmadt.rmadt_qcEndDateTime2 AS rmadtQcEndDate2,
            rmadt.rmadt_qcduration2 AS rmadtQcDuration2,
            rmadt.bypass,
            rmadt.bypassUser,
            rmadt.bypassDate,
            rmadt.rmadt_ReturnDate AS rmadtReturnDate,
            TIMESTAMPDIFF(MINUTE,rma.rma_date, rmadt.rmadt_DateReceived) AS createToRecieve,
            TIMESTAMPDIFF(MINUTE,rmadt.rmadt_DateReceived, rmadt.rmadt_engrecieved_date) AS recieveToEngRecieve,
            TIMESTAMPDIFF(MINUTE,rmadt.rmadt_engrecieved_date, rmadt.ramdt_techquotationdate) AS engRecieveToQuoteDate,
            TIMESTAMPDIFF(MINUTE,rmadt.ramdt_techquotationdate, rmadt.rmadt_approvedDate) AS quoteDateToApproved,
            TIMESTAMPDIFF(MINUTE,rmadt.rmadt_approvedDate, rmadt.rmadt_repStartDateTime) AS approvedToRepStart,
            TIMESTAMPDIFF(MINUTE,rmadt.rmadt_repStartDateTime, rmadt.rmadt_repEndDateTime) AS repStartToRepEnd,
            TIMESTAMPDIFF(MINUTE,rmadt.rmadt_repEndDateTime, rmadt.rmadt_qcrecieved_date) AS repEndToQcRecieve,
            TIMESTAMPDIFF(MINUTE,rmadt.rmadt_qcrecieved_date, rmadt.rmadt_qcStartDateTime) AS qcRecieveToQcStart,
            TIMESTAMPDIFF(MINUTE,rmadt.rmadt_qcStartDateTime, rmadt.rmadt_qcEndDateTime) AS qcStartToQcEnd,
            TIMESTAMPDIFF(MINUTE,rmadt.rmadt_qcEndDateTime, rmadt.rmadt_qcapproveddate) AS qcEndToQcApprove,
            TIMESTAMPDIFF(MINUTE,rmadt.rmadt_qcEndDateTime, rmadt.rmadt_repStartDateTime2) AS qcEndToRepStart2,
            TIMESTAMPDIFF(MINUTE,rmadt.rmadt_repStartDateTime2, rmadt.rmadt_repEndDateTime2) AS repStart2TorepEnd2,
            TIMESTAMPDIFF(MINUTE,rmadt.rmadt_repEndDateTime2, rmadt.rmadt_qcStartDateTime2) AS repEnd2ToQcStart2,
            TIMESTAMPDIFF(MINUTE,rmadt.rmadt_qcStartDateTime2, rmadt.rmadt_qcEndDateTime2) AS qcStart2ToQcEnd2,
            TIMESTAMPDIFF(MINUTE,rmadt.rmadt_qcEndDateTime2, rmadt.rmadt_qcapproveddate) AS qcEnd2ToQcApprove,
            TIMESTAMPDIFF(MINUTE,rmadt.rmadt_qcapproveddate, rmadt.rmadt_ReturnDate) AS qcApproveToReturn
        FROM rma
        INNER JOIN rmadt ON rma.rma_id = rmadt.rma_id
        INNER JOIN items ON rmadt.item_code = items.item_code
        WHERE rma.rma_date BETWEEN '$fromdate 00:00:00' AND '$todate 23:59:59'";
        if($itemCode != "")
            $query .= " AND rmadt.item_code = '$itemCode'";
        if($clientCode != "")
            $query .= " AND rmadt.client_code = '$clientCode'";
        if($rmaRef != "")
            $query .= " AND rma.rma_reference = $rmaRef AND rma.rma_type = '$rmaType'";
        $query .= " ORDER BY rma.rma_reference ASC";
        //echo $query;//exit;
        $result = mysql_query($query, $connection) or die(mysql_error());
        while($row=mysql_fetch_array($result)){
            extract($row);
            $RowNum += 1;
            $ColumnNum = 0;
            $worksheet->write($RowNum, $ColumnNum, $RMARef, $regularleftFormat);
            $ColumnNum += 1;
            $worksheet->write($RowNum, $ColumnNum, $groupCode, $regularcenterFormat);
            $ColumnNum += 1;
            $worksheet->write($RowNum, $ColumnNum, $itemCode, $regularleftFormat);
            $ColumnNum += 1;
            $worksheet->write($RowNum, $ColumnNum, $itemDesc, $regularleftFormat);
            $ColumnNum += 1;
            $worksheet->write($RowNum, $ColumnNum, $clientName, $regularleftFormat);
            if($rmadtProblem=="")
                $rmadtProblem="N/A";
            $ColumnNum += 1;
            $worksheet->write($RowNum, $ColumnNum, $rmadtProblem, $regularleftFormat);
            if($rmadtQuotation=="")
                $rmadtQuotation="N/A";
            $ColumnNum += 1;
            $worksheet->write($RowNum, $ColumnNum, $rmadtQuotation, $regularleftFormat);
            $ColumnNum += 1;
            $worksheet->write($RowNum, $ColumnNum, $rmaType, $regularleftFormat);
            if($createToRecieve=="")
                $createToRecieve="N/A";
            $ColumnNum += 1;
            $worksheet->write($RowNum, $ColumnNum, $createToRecieve, $regularcenterFormat);
            if($recieveToEngRecieve=="")
                $recieveToEngRecieve="N/A";
            $ColumnNum += 1;
            $worksheet->write($RowNum, $ColumnNum, $recieveToEngRecieve, $regularcenterFormat);
            if($engRecieveToQuoteDate=="")
                $engRecieveToQuoteDate="N/A";
            $ColumnNum += 1;
            $worksheet->write($RowNum, $ColumnNum, $engRecieveToQuoteDate, $regularcenterFormat);
            if($quoteDateToApproved=="")
                $quoteDateToApproved="N/A";
            $ColumnNum += 1;
            $worksheet->write($RowNum, $ColumnNum, $quoteDateToApproved, $regularcenterFormat);
            if($approvedToRepStart=="")
                $approvedToRepStart="N/A";
            $ColumnNum += 1;
            $worksheet->write($RowNum, $ColumnNum, $approvedToRepStart, $regularcenterFormat);
            if($repStartToRepEnd=="")
                $repStartToRepEnd="N/A";
            $ColumnNum += 1;
            $worksheet->write($RowNum, $ColumnNum, $repStartToRepEnd, $regularcenterFormat);
            if($repEndToQcRecieve=="")
                $repEndToQcRecieve="N/A";
            $ColumnNum += 1;
            $worksheet->write($RowNum, $ColumnNum, $repEndToQcRecieve, $regularcenterFormat);
            if($qcRecieveToQcStart=="")
                $qcRecieveToQcStart="N/A";
            $ColumnNum += 1;
            $worksheet->write($RowNum, $ColumnNum, $qcRecieveToQcStart, $regularcenterFormat);
            if($qcStartToQcEnd=="")
                $qcStartToQcEnd="N/A";
            $ColumnNum += 1;
            $worksheet->write($RowNum, $ColumnNum, $qcStartToQcEnd, $regularcenterFormat);
            if($qcEndToQcApprove=="")
                $qcEndToQcApprove="N/A";
            $ColumnNum += 1;
            $worksheet->write($RowNum, $ColumnNum, $qcEndToQcApprove, $regularcenterFormat);
            if($qcEndToRepStart2=="")
                $qcEndToRepStart2="N/A";
            $ColumnNum += 1;
            $worksheet->write($RowNum, $ColumnNum, $qcEndToRepStart2, $regularcenterFormat);
            if($repStart2TorepEnd2=="")
                $repStart2TorepEnd2="N/A";
            $ColumnNum += 1;
            $worksheet->write($RowNum, $ColumnNum, $qcEndToRepStart2, $regularcenterFormat);
            if($repEnd2ToQcStart2=="")
                $repEnd2ToQcStart2="N/A";
            $ColumnNum += 1;
            $worksheet->write($RowNum, $ColumnNum, $repEnd2ToQcStart2, $regularcenterFormat);    
            if($qcStart2ToQcEnd2=="")
                $qcStart2ToQcEnd2="N/A";
            $ColumnNum += 1;
            $worksheet->write($RowNum, $ColumnNum, $qcStart2ToQcEnd2, $regularcenterFormat);    
            if($qcEnd2ToQcApprove=="")
                $qcEnd2ToQcApprove="N/A";
            $ColumnNum += 1;
            $worksheet->write($RowNum, $ColumnNum, $qcEnd2ToQcApprove, $regularcenterFormat);    
            if($qcApproveToReturn=="")
                $qcApproveToReturn="N/A";
            $ColumnNum += 1;
            $worksheet->write($RowNum, $ColumnNum, $qcApproveToReturn, $regularcenterFormat);    

            $total = $createToRecieve + $recieveToEngRecieve + $engRecieveToQuoteDate + $quoteDateToApproved + $approvedToRepStart + $repStartToRepEnd + $repEndToQcRecieve + $qcRecieveToQcStart + $qcStartToQcEnd + $qcEndToQcApprove + $qcEndToRepStart2 + $repStart2TorepEnd2 + $repEnd2ToQcStart2 + $qcStart2ToQcEnd2 + $qcEnd2ToQcApprove + $qcApproveToReturn;
            $ColumnNum += 1;
            $worksheet->write($RowNum, $ColumnNum, $total, $regularcenterFormat);    
        }

$workbook->close();

?>