<?php
// error_reporting(0);
if (!isset($_SESSION)) {
    session_start();
}
require_once('../Connections/courier.php');

//echo"SELECT * FROM stock_items WHERE service_type != 'Optical Consultation' AND quantity_available > '0' ORDER BY id ASC"; exit();
$invalid_rows = mysql_query("SELECT * FROM stock_items WHERE service_type != 'Optical Consultation' AND quantity_available > '0' ORDER BY id ASC", $courier);
$invalid_rows_count = mysql_num_rows($invalid_rows);

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("../assets/PHPExcel/Classes/PHPExcel.php");
$objPHPExcel = new PHPExcel();

$styleArray = array(
    'font'  => array(
        'bold'  => true,
        'color' => array('rgb' => '000000'),
    )
);

$styleArray2 = array(
    'font'  => array(
        'color' => array('rgb' => '000000'),
    ),
    'alignment' => array(
     'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
 )
);

$cellBackground = array(
    'fill' => array(
        'type' => PHPExcel_Style_Fill::FILL_SOLID,
        'color' => array('rgb' => 'adf802')
    )
);

$border_style= array(
    'borders' => array(
        'right' => array(
            'style' => 
            PHPExcel_Style_Border::BORDER_DOUBLE,
            'color' => array(
                'rgb' => 'ff0000 '
            )
        ),
        'left' => array(
            'style' => 
            PHPExcel_Style_Border::BORDER_DOUBLE,
            'color' => array(
                'rgb' => 'ff0000 '
            )
        ),
        'bottom' => array(
            'style' => 
            PHPExcel_Style_Border::BORDER_DOUBLE,
            'color' => array(
                'rgb' => 'ff0000 '
            )
        ),
        'top' => array(
            'style' => 
            PHPExcel_Style_Border::BORDER_DOUBLE,
            'color' => array(
                'rgb' => 'ff0000 '
            )
        )
    )
);

        // $sheet = $objPHPExcel->getActiveSheet();
        // $sheet->getStyle("A2")->applyFromArray($border_style);

$i2 = 0;
$objPHPExcel->getProperties()->setCreator('STORE STOCK ITEMS')
->setLastModifiedBy('STORE STOCK ITEMS')
->setTitle('STORE STOCK ITEMS');


$i = 2;
$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex($i2)
    ->setCellValue('A1', 'id')
->setCellValue('B1', 'item')
->setCellValue('C1', 'price')
->setCellValue('D1', 'service_type')
->setCellValue('E1', 'service')
->setCellValue('F1', 'quantity_available');


$objPHPExcel->getActiveSheet()->getStyle('A1:F1')->applyFromArray($styleArray);
while ($row = mysql_fetch_assoc($invalid_rows)) {
    if(empty($row['id'])) {
        $objPHPExcel->getActiveSheet()->getStyle('A' . $i)->applyFromArray($border_style);
    }if(empty($row['item'])){
        $objPHPExcel->getActiveSheet()->getStyle('B'.$i)->applyFromArray($border_style);
    }if(empty($row['price'])){
        $objPHPExcel->getActiveSheet()->getStyle('C'.$i)->applyFromArray($border_style);
    }if(empty($row['service_type'])){
        $objPHPExcel->getActiveSheet()->getStyle('D'.$i)->applyFromArray($border_style);
    } if(empty($row['service'])){
        $objPHPExcel->getActiveSheet()->getStyle('E'.$i)->applyFromArray($border_style);
    }if(empty($row['quantity_available'])){
        $objPHPExcel->getActiveSheet()->getStyle('F'.$i)->applyFromArray($border_style);
    }

    $objPHPExcel->getActiveSheet()->getStyle('A' . $i . ':' . 'F' . $i)->applyFromArray($styleArray2);
    $objPHPExcel->setActiveSheetIndex($i2)
        ->setCellValue('A'.$i,$row['id'])
    ->setCellValue('B'.$i,$row['item'])
    ->setCellValue('C'.$i,$row['price'])
    ->setCellValue('D'.$i,$row['service_type'])
    ->setCellValue('E'.$i,$row['service'])
    ->setCellValue('F'.$i,$row['quantity_available']);

    $i++;    
}
$ii= $i;



header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename=STORE STOCK ITEMS.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');