<?php
session_start();
require_once('../Connections/courier.php');
require_once '../functions/functions.php';
$branch_id = $_SESSION['branch_id'];
$batch_no = $_GET['batch_no'];

$sql = "SELECT date_format(a.date_of_order,'%e %M %Y') as purchase_order_date, date_format(a.expected_date,'%e %M %Y') as purchase_expected_date, date_format(a.received_date,'%e %M %Y') as purchase_received_date, b.supplier as supplier_name, c.branch_name as deliver_to, a.* FROM purchase_orders a LEFT JOIN suppliers b on a.supplier=b.id LEFT JOIN branches c on a.delivery_location=c.id left join purchase_orders d on a.purchase_order_no = d.purchase_order_no WHERE a.order_type='Lens Order' AND a.po_batch_no='$batch_no' order by purchase_order_no desc";
$query_rsodest = mysql_query($sql);
$total_lens = mysql_num_rows($query_rsodest);

if ($total_lens > 0) {
    error_reporting(E_ALL);
    ini_set('display_errors', TRUE);
    ini_set('display_startup_errors', TRUE);
    date_default_timezone_set('Europe/London');

    if (PHP_SAPI == 'cli')
        die('This example should only be run from a Web Browser');
    require_once(dirname(__FILE__) . "/../assets/PHPExcel/Classes/PHPExcel.php");
    $objPHPExcel = new PHPExcel();
    $i2 = 0;
    $objPHPExcel->getProperties()->setCreator('ROBERT AND SONS LIMITED')
                ->setLastModifiedBy('ROBERT AND SONS LIMITED')
                ->setTitle('LIST OF ALL ITEMS THAT NEEDS REPLENISHMENT');

    $objPHPExcel->createSheet();
    $objPHPExcel->setActiveSheetIndex($i2);

    $styleYellowbackground = array(
        array(
            'fill' => array(
                'type' => PHPExcel_Style_Fill::FILL_SOLID,
                'color' => array('rgb' => '000000')
            )
        )
    );
    
    $styleborderColor = array(
        array(
            'borders' => array(
                'allborders' => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN,
                    'color' => array('rgb' => '000000')
                )
            )
        )
    );

    $styleArraydept = array(
        'font' => array(
            'bold' => true,
            'size' => 14,
            'color' => array('rgb' => '#7a0026'),
        ),
        'alignment' => array(
            'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
        )
    );

    $styleArray2 = array(
        'font' => array(
            'bold' => true,
            'size' => 12,
            'color' => array('rgb' => '000000'),
        ),
        'fill' => array(
            'type' => PHPExcel_Style_Fill::FILL_SOLID,
            'color' => array('rgb' => 'ffae19')
        )
    );

    $styleArray3 = array(
        'font' => array(
            'bold' => true,
            'size' => 12,
    ),
    'fill' => array(
        'type' => PHPExcel_Style_Fill::FILL_SOLID,
        'color' => array('rgb' => '198c19')
    )
);

    $objPHPExcel->getActiveSheet()->getStyle('A1:E1')->applyFromArray($styleborderColor);
    $objPHPExcel->getActiveSheet()->getStyle('A2:E2')->applyFromArray($styleborderColor);
    $objPHPExcel->setActiveSheetIndex($i2)
			->mergeCells('A1:E1')->setCellValue('A1', 'ROBERT AND SONS LIMITED')
			->mergeCells('A2:E2')->setCellValue('A2', 'CANADA OPTICALS LABORATORIES')
			->mergeCells('A3:C3')->setCellValue('A3', 'RX ORDERS')
			->mergeCells('D3:E3')->setCellValue('D3', 'ORDER: '.$batch_no)
			->mergeCells('A4:E4')->setCellValue('A4', 'TOTAL ORDERS: '.$total_lens)
            ->mergeCells('A5:E5')->setCellValue('A5', 'DATE: '.date('jS F, Y'))
            
            ->mergeCells('G1:K1')->setCellValue('G1', 'ROBERT AND SONS LIMITED')
			->mergeCells('G2:K2')->setCellValue('G2', 'CANADA OPTICALS LABORATORIES')
            ->mergeCells('G3:I3')->setCellValue('G3', 'RX ORDERS')
            ->mergeCells('J3:K3')->setCellValue('J3', 'ORDER: '.$batch_no)
			->mergeCells('G4:K4')->setCellValue('G4', 'TOTAL ORDERS: '.$total_lens)
            ->mergeCells('G5:K5')->setCellValue('G5', 'DATE: '.date('jS F, Y'));

    $i = 8;
    $i6 = 0; 
    $count = 1;
    while ($row = mysql_fetch_assoc($query_rsodest)) {
        $job_number = getJobNumber($row['process_claim_no'], $row['po_batch_no']);
        $lens = getPatientLensPrescription($row['process_claim_no']);
        $lens_type = getPatientLensType($row['process_claim_no']);
        $lens_addition = getPatientLensAddition($row['process_claim_no']);
        $main_lens_type = getPatientLensPrescription($row['process_claim_no']);

            if (fmod($count, 2) > 0){
            $i6 += 7;
            $i8 = $i6+1;
            $i9 = $i6+2;
            $i10 = $i6+3;
            $i11 = $i6+4;
            $i12 = $i6+5;
            $objPHPExcel->getActiveSheet()->getStyle('A' .$i6.':'. 'E' .$i6)->applyFromArray($styleYellowbackground);
            $objPHPExcel->setActiveSheetIndex($i2)
            ->mergeCells('A' .$i6.':'. 'E' .$i6)->setCellValue('A' .$i6, 'ORDER NO : '.$job_number)
            ->setCellValue('B' .$i8, 'SPH')
            ->setCellValue('C' .$i8, 'CYL')
            ->setCellValue('D' .$i8, 'AXIS')
            ->setCellValue('E' .$i8, 'ADD')
            ->setCellValue('A' .$i9, 'RIGHT')
            ->setCellValue('A' .$i10, 'LEFT')
            ->mergeCells('A' .$i11.':'. 'E' .$i11)->setCellValue('A' .$i11, strtoupper($lens_type.' '.$lens_addition))
            ->mergeCells('A' .$i12.':'. 'E' .$i12)->setCellValue('A' .$i12, strtoupper($main_lens_type['lens_type']))
            
            ->setCellValue('B' .$i9, $lens['od_sphere'])
            ->setCellValue('C' .$i9, $lens['od_cylinder'])
            ->setCellValue('D' .$i9, $lens['od_axis'])
            ->setCellValue('E' .$i9, $lens['od_add'])

            ->setCellValue('B' .$i10, $lens['os_sphere'])
            ->setCellValue('C' .$i10, $lens['os_cylinder'])
            ->setCellValue('D' .$i10, $lens['os_axis'])
            ->setCellValue('E' .$i10, $lens['os_add'])
            
            ;
            }else{
                $objPHPExcel->getActiveSheet()->getStyle('G' .$i6.':'. 'K' .$i6)->applyFromArray($styleYellowbackground);
                $objPHPExcel->setActiveSheetIndex($i2)
                ->mergeCells('G' .$i6.':'. 'K' .$i6)->setCellValue('G' .$i6, 'ORDER NO : '.$job_number)
                ->setCellValue('H' .$i8, 'SPH')
                ->setCellValue('I' .$i8, 'CYL')
                ->setCellValue('J' .$i8, 'AXIS')
                ->setCellValue('K' .$i8, 'ADD')
                ->setCellValue('G' .$i9, 'RIGHT')
                ->setCellValue('G' .$i10, 'LEFT')
                ->mergeCells('G' .$i11.':'. 'K' .$i11)->setCellValue('G' .$i11, strtoupper($lens_type.' '.$lens_addition))
                ->mergeCells('G' .$i12.':'. 'K' .$i12)->setCellValue('G' .$i12, strtoupper($main_lens_type['lens_type']))
                
                ->setCellValue('H' .$i9, $lens['od_sphere'])
                ->setCellValue('I' .$i9, $lens['od_cylinder'])
                ->setCellValue('J' .$i9, $lens['od_axis'])
                ->setCellValue('K' .$i9, $lens['od_add'])

                ->setCellValue('H' .$i10, $lens['os_sphere'])
                ->setCellValue('I' .$i10, $lens['os_cylinder'])
                ->setCellValue('J' .$i10, $lens['os_axis'])
                ->setCellValue('K' .$i10, $lens['os_add'])

                ;
        }
         
            $i++;
            $count++;
    }

    $objPHPExcel->getActiveSheet()->setTitle('ORDER SHEET FOR CANADA OPTICALS');
    $objPHPExcel->setActiveSheetIndex($i2);

    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment;filename="ORDER SHEET FOR CANADA OPTICALS".xls"');

    header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
    header('Cache-Control: max-age=1');

// If you're serving to IE over SSL, then the following may be needed
    header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
    header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
    header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
    header('Pragma: public'); // HTTP/1.0

    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
    $objWriter->save('php://output');
exit;
} else {
    die('Sorry,No Items Found');
}