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


$invalid_rows = mysql_query("SELECT * FROM service_items WHERE service_type != 'Optical Consultation' AND (quantity_available_adabraka > '0' OR quantity_available_kumasi > '0' OR quantity_available_adenta > '0' OR quantity_available_danquah > '0' OR quantity_available_weija > '0' OR quantity_available_tema > '0' OR quantity_available_mobile_unit > '0' OR quantity_available_east_legon > '0' OR quantity_available_ahodwo > '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('BRANCH STOCK ITEMS')
->setLastModifiedBy('BRANCH STOCK ITEMS')
->setTitle('BRANCH STOCK ITEMS');


$i = 2;
$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex($i2)
->setCellValue('A1', 'item')
->setCellValue('B1', 'price')
->setCellValue('C1', 'service_type')
->setCellValue('D1', 'service')
->setCellValue('E1', 'sub_service_type')
->setCellValue('F1', 'quantity_available_adabraka')
->setCellValue('G1', 'quantity_available_kumasi')
->setCellValue('H1', 'quantity_available_adenta')
->setCellValue('I1', 'quantity_available_danquah')
->setCellValue('J1', 'quantity_available_weija')
->setCellValue('K1', 'quantity_available_tema')
->setCellValue('L1', 'quantity_available_mobile_unit')
->setCellValue('M1', 'quantity_available_east_legon')
->setCellValue('N1', 'quantity_available_ahodwo');

$objPHPExcel->getActiveSheet()->getStyle('A1:N1')->applyFromArray($styleArray);
while ($row = mysql_fetch_assoc($invalid_rows)) {
    if(empty($row['item'])){
        $objPHPExcel->getActiveSheet()->getStyle('A'.$i)->applyFromArray($border_style);
    }if(empty($row['price_adabraka'])){
        $objPHPExcel->getActiveSheet()->getStyle('B'.$i)->applyFromArray($border_style);
    }if(empty($row['service_type'])){
        $objPHPExcel->getActiveSheet()->getStyle('C'.$i)->applyFromArray($border_style);
    } if(empty($row['service'])){
        $objPHPExcel->getActiveSheet()->getStyle('D'.$i)->applyFromArray($border_style);
    }if(empty($row['sub_service_type'])){
        $objPHPExcel->getActiveSheet()->getStyle('E'.$i)->applyFromArray($border_style);
    }if(empty($row['quantity_available_adabraka'])){
        $objPHPExcel->getActiveSheet()->getStyle('F'.$i)->applyFromArray($border_style);
    }if(empty($row['quantity_available_kumasi'])){
        $objPHPExcel->getActiveSheet()->getStyle('G'.$i)->applyFromArray($border_style);
    }if (empty($row['quantity_available_adenta'])) {
     $objPHPExcel->getActiveSheet()->getStyle('H'.$i)->applyFromArray($border_style);
 }if (empty($row['quantity_available_danquah'])) {
     $objPHPExcel->getActiveSheet()->getStyle('I'.$i)->applyFromArray($border_style);
 }if (empty($row['quantity_available_weija'])) {
     $objPHPExcel->getActiveSheet()->getStyle('J'.$i)->applyFromArray($border_style);
 }if (empty($row['quantity_available_tema'])) {
     $objPHPExcel->getActiveSheet()->getStyle('K'.$i)->applyFromArray($border_style);
 }if (empty($row['quantity_available_mobile_unit'])) {
     $objPHPExcel->getActiveSheet()->getStyle('L'.$i)->applyFromArray($border_style);
 }if (empty($row['quantity_available_east_legon'])) {
     $objPHPExcel->getActiveSheet()->getStyle('M'.$i)->applyFromArray($border_style);
 }if (empty($row['quantity_available_ahodwo'])) {
     $objPHPExcel->getActiveSheet()->getStyle('N'.$i)->applyFromArray($border_style);
 }

    // $employer_id = $row['employer_id']; 
    // $plan_id = $row['member_plan_id'];
    // $check_plann = mysqli_num_rows(mysqli_query($conn,"SELECT companyID, PlanID FROM company_plan WHERE CompanyID='$employer_id' AND PlanID='$plan_id' LIMIT 1"));

    // if($check_plann < 1){
    //     $objPHPExcel->getActiveSheet()->getStyle('O'.$i. ':' . 'P' . $i)->applyFromArray($border_style);
    // }

 $objPHPExcel->getActiveSheet()->getStyle('D' . $i . ':' . 'G' . $i)->applyFromArray($styleArray2);
 $objPHPExcel->setActiveSheetIndex($i2)
 ->setCellValue('A'.$i,$row['item'])
 ->setCellValue('B'.$i,$row['price_adabraka'])
 ->setCellValue('C'.$i,$row['service_type'])
 ->setCellValue('D'.$i,$row['service'])
 ->setCellValue('E'.$i,$row['sub_service_type'])
 ->setCellValue('F'.$i,$row['quantity_available_adabraka'])
 ->setCellValue('G'.$i,$row['quantity_available_kumasi'])
 ->setCellValue('H'.$i,$row['quantity_available_adenta'])
 ->setCellValue('I'.$i,$row['quantity_available_danquah'])
 ->setCellValue('J'.$i,$row['quantity_available_weija'])
 ->setCellValue('K'.$i,$row['quantity_available_tema'])
 ->setCellValue('L'.$i,$row['quantity_available_mobile_unit'])
 ->setCellValue('M'.$i,$row['quantity_available_east_legon'])
 ->setCellValue('N'.$i,$row['quantity_available_ahodwo']);
 $i++;    
}
$ii= $i;

// $objPHPExcel->getActiveSheet()->getStyle('A' . $ii . ':' . 'F' . $ii)->applyFromArray($cellBackground);
// $objPHPExcel->setActiveSheetIndex($i2)
//                                 ->mergeCells('A' . $ii . ':' . 'E' . $ii)
//                                 ->setCellValue('A'.$ii,'Total Members Count')
//                                 ->setCellValue('F'.$ii, $i-2);
// $objPHPExcel->getActiveSheet()->setTitle(substr(str_replace("/","","INVALID MEMBERS"),0,30));
// $objPHPExcel->setActiveSheetIndex($i2);


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