<?php
session_start();
require_once('../Connections/courier.php');
require_once '../functions/functions.php';
$branch_id = $_SESSION['branch_id'];
$query_rsodest = mysql_query("SELECT c.*,c.job_number AS main_job_number,a.process_claim_no,a.patient_status,a.mode_of_payment,a.datetimeadded,a.mode_of_payment,b.surname,b.othernames,b.sex,b.card_number,a.patient_id as patient_id,DATE_FORMAT(a.datetimeadded,'%e %b %Y %r') AS datenew,j.* FROM job_numbers c left join process_claim_no a on c.process_claim_no=a.process_claim_no left join patient_information b on a.patient_no=b.card_number inner join claims_details g ON c.job_number=g.job_number left join job_activities_status j ON a.process_claim_no=j.process_claim_no WHERE c.audit_status='Job Received' AND j.status='open' AND j.job_status=c.audit_status AND a.patient_status = 'In Use' AND c.status_location_id='$branch_id' AND c.audit_status<>'none' GROUP BY c.job_number ORDER BY j.id DESC");

if (mysql_num_rows($query_rsodest) > 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);

    $styleArray = array(
        'font' => array(
            'bold' => true,
            'size' => 14,
            'color' => array('rgb' => 'FF0000'),
        ),
        'alignment' => array(
            'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
        )
    );

    $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')
    )
);

    // $sheet->getStyle('A1')->applyFromArray(
    //     array(
    //         'fill' => array(
    //             'type' => PHPExcel_Style_Fill::FILL_SOLID,
    //             'color' => array('rgb' => 'FF0000')
    //         )
    //     )
    // );

    $objPHPExcel->getActiveSheet()->getStyle('D7:G7')->applyFromArray($styleArray3);
    $objPHPExcel->getActiveSheet()->getStyle('H7:I7')->applyFromArray($styleArray3);
    $objPHPExcel->getActiveSheet()->getStyle('J7:Z7')->applyFromArray($styleArray3);
    $objPHPExcel->getActiveSheet()->getStyle('A8:Z8')->applyFromArray($styleArray2);

    $objPHPExcel->getActiveSheet()->getStyle('A2:K2')->applyFromArray($styleArray);
    $objPHPExcel->setActiveSheetIndex($i2)->mergeCells('A2:K2');
    $objPHPExcel->setActiveSheetIndex($i2)->setCellValue('A2', 'ROBERT AND SONS LIMITED');

    $objPHPExcel->getActiveSheet()->getStyle('A3:K3')->applyFromArray($styleArraydept);
    $objPHPExcel->setActiveSheetIndex($i2)->mergeCells('A3:K3');
    $objPHPExcel->setActiveSheetIndex($i2)->setCellValue('A3', 'PROCURMENT DEPARTMENT');

    $objPHPExcel->getActiveSheet()->getStyle('A4:K4')->applyFromArray($styleArraydept);
    $objPHPExcel->setActiveSheetIndex($i2)->mergeCells('A4:K4');
    $objPHPExcel->setActiveSheetIndex($i2)->setCellValue('A4', 'SPECIAL LENS ORDER RECORD');

    $objPHPExcel->getActiveSheet()->getStyle('A7:C7')->applyFromArray($styleArray3);
    $objPHPExcel->getActiveSheet()->getStyle('G7')->applyFromArray($styleArray3);

    $objPHPExcel->setActiveSheetIndex($i2)
            ->setCellValue('A7', 'NO')
            ->setCellValue('B7', 'DATE')
            ->setCellValue('C7', 'CUSTOMER NAME')
			->mergeCells('D7:G7')->setCellValue('D7', 'RIGHT EYE')
			->mergeCells('H7:K7')->setCellValue('H7', 'LEFT EYE')
            ->setCellValue('L7', 'TYPE OF LENS')
            ->setCellValue('M7', 'INVOICE NUMBER')
            ->setCellValue('N7', 'ORDER NO.')
            ->setCellValue('O7', 'ORDER DATE')
            ->setCellValue('P7', 'GENDER')
            ->setCellValue('Q7', 'SENT TO')
            ->setCellValue('R7', 'ORDER REC. D')
			->setCellValue('S7', 'DISPATCHED')
			->setCellValue('T7', 'PD')
			->setCellValue('U7', 'FRAME')
			->setCellValue('V7', 'CS OFFICER IN CHARGE')
			->setCellValue('W7', 'ORDERED BY')
			->setCellValue('X7', 'MODE OF PAYMENT');
    
    $objPHPExcel->setActiveSheetIndex($i2)
			->setCellValue('D8', 'SPH')
			->setCellValue('E8', 'CYL')
			->setCellValue('F8', 'AXIS')
            ->setCellValue('G8', 'ADD')
            
			->setCellValue('H8', 'SPH')
			->setCellValue('I8', 'CYL')
			->setCellValue('J8', 'AXIS')
			->setCellValue('K8', 'ADD');

    $i = 9;
    while ($row_rsodest = mysql_fetch_assoc($query_rsodest)) {
        $optical = mysql_fetch_assoc(mysql_query("SELECT * FROM optical_examination WHERE process_claim_no='".$row_rsodest['process_claim_no']."'"));
        $lens = mysql_fetch_assoc(mysql_query("SELECT * FROM claims_details WHERE process_claim_no='".$row_rsodest['process_claim_no']."' AND job_number='".$row_rsodest['main_job_number']."' AND item_service_type='Optical Lens'"));
        $frames = mysql_fetch_assoc(mysql_query("SELECT * FROM claims_details WHERE process_claim_no='".$row_rsodest['process_claim_no']."' AND job_number='".$row_rsodest['main_job_number']."' AND item_service_type='Optical Frame'"));
        $receiptno = mysql_fetch_assoc(mysql_query("SELECT receiptno FROM cashier_payments WHERE process_claim_no='".$row_rsodest['process_claim_no']."' LIMIT 1"));
        

        $objPHPExcel->setActiveSheetIndex($i2)
                ->setCellValue('A' . $i, $i - 8)
                ->setCellValue('B' . $i, date('D, jS F, Y g:ia',  strtotime($row_rsodest['datetimeadded'])))
                ->setCellValueExplicit('C' . $i, strtoupper($row_rsodest['surname']." ".$row_rsodest['othernames']), PHPExcel_Cell_DataType::TYPE_STRING)
                
                ->setCellValue('D' . $i, $optical['od_sphere'])
                ->setCellValue('E' . $i, $optical['od_cylinder'])
                ->setCellValue('F' . $i, $optical['od_axis'])
                ->setCellValue('G' . $i, $optical['od_add'])
               
                ->setCellValue('H' . $i, $optical['os_sphere'])
                ->setCellValue('I' . $i, $optical['os_cylinder'])
                ->setCellValue('J' . $i, $optical['os_axis'])
                ->setCellValue('K' . $i, $optical['os_add'])

                ->setCellValue('L' . $i, strtoupper($lens['item']))
                ->setCellValue('M' . $i, strtoupper($receiptno['receiptno']))
                ->setCellValue('N' . $i, $row_rsodest['main_job_number'])
                ->setCellValue('P' . $i, strtoupper($row_rsodest['sex']))
                ->setCellValue('T' . $i, $row_rsodest['od_pd'])
                ->setCellValue('U' . $i, strtoupper($frames['item']))
                ->setCellValue('X' . $i, strtoupper($row_rsodest['mode_of_payment']));
        $i++;
    }

    $objPHPExcel->getActiveSheet()->setTitle('SPECIAL LENS ORDER RECORD');
    $objPHPExcel->setActiveSheetIndex($i2);

    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment;filename="SPECIAL LENS ORDER RECORD".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');
}