<?php
    $menuid=$this->uri->segment(4);
    $ID=$this->uri->segment(5);

    //echo $ID;
    //exit;

    $data_workorder=$this->master_model->select_in('ts_workorder','*',"WHERE ID=$ID");

    $data_workorder_material=$this->master_model->select_in('ts_workorder_material','*',"WHERE id_workorder=$ID");

    $data_workorder_labour=$this->master_model->select_in('ts_workorder_labour','*',"WHERE id_workorder=$ID");
    // echo "<pre>";
    // print_r($data_workorder_labour);die();
    $data_workorder_design=$this->master_model->select_in('ts_workorder_design','*',"WHERE id_workorder=$ID");

    $count_wo=count($data_workorder);

    if($count_wo == 0)
    {
        echo'
            <script language="javascript">
                window.alert("No transaction data to export on that date!!")
                window.location.href="'.base_url().'admin/transaction/workorder/'.$menuid.'";
            </script>
        ';
    }else
    {
        $styleArray =
            array(
                'borders' =>
            array(
            'allborders' =>
             array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' =>
            array(
                'argb' => '00000000'
                ),
            ),
            ),
        );

        $fontHeader = array(
            'font' => array(
                'bold' => true
            ),
            'alignment' => array(
                'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                'vertical'   => PHPExcel_Style_Alignment::VERTICAL_CENTER,
                'rotation'   => 0,

            ),
            'fill' => array(
                'type' => PHPExcel_Style_Fill::FILL_SOLID,
                'color' => array('rgb' => 'F7F7F8')
            )
        );

        $objPHPExcel = new phpexcel();

        $objPHPExcel->setActiveSheetIndex(0);

        $objPHPExcel->getActiveSheet()->setTitle('WORKORDER');
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $objPHPExcel->getActiveSheet()->getStyle('C3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);

        $objPHPExcel->getActiveSheet()->getStyle('E6:G15')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);

        $objPHPExcel->getActiveSheet()->getStyle('A6:A15')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $objPHPExcel->getActiveSheet()->getStyle('B6:B15')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);

        $objPHPExcel->getActiveSheet()->getStyle('A24:A39')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

         $objPHPExcel->getActiveSheet()->getStyle('B24:B39')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

         $objPHPExcel->getActiveSheet()->getStyle('G2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

         $objPHPExcel->getActiveSheet()->getStyle('H2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $objPHPExcel->getActiveSheet()->getStyle('F2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

        $objPHPExcel->getActiveSheet()->getStyle('E3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);

        $objPHPExcel->getActiveSheet()->getStyle('A5')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);

         $objPHPExcel->getActiveSheet()->getStyle('A6')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

         $objPHPExcel->getActiveSheet()->getStyle('C5')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $objPHPExcel->getActiveSheet()->getStyle('C6')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $objPHPExcel->getActiveSheet()->getStyle('D6')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $objPHPExcel->getActiveSheet()->getStyle('E6')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $objPHPExcel->getActiveSheet()->getStyle('F6')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $objPHPExcel->getActiveSheet()->getStyle('G6')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $objPHPExcel->getActiveSheet()->getStyle('H6')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $objPHPExcel->getActiveSheet()->getStyle('B18')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $objPHPExcel->getActiveSheet()->getStyle('D18')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $objPHPExcel->getActiveSheet()->getStyle('B19')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $objPHPExcel->getActiveSheet()->getStyle('C19')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $objPHPExcel->getActiveSheet()->getStyle('D19')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $objPHPExcel->getActiveSheet()->getStyle('E19')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $objPHPExcel->getActiveSheet()->getStyle('A16')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);

        $objPHPExcel->getActiveSheet()->getStyle('A23')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $objPHPExcel->getActiveSheet()->getStyle('B23')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $objPHPExcel->getActiveSheet()->getStyle('C23')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);


        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(18)->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('A3')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('A4')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('A5')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('A6')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('C5')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('C6')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('D6')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('E6')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('F6')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('G6')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('H6')->getFont()->setBold(true);

        $objPHPExcel->getActiveSheet()->getStyle('A18')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('B18')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('D18')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('B19')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('C19')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('D19')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('E19')->getFont()->setBold(true);

        $objPHPExcel->getActiveSheet()->getStyle('A23')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('B23')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('C23')->getFont()->setBold(true);

        $objPHPExcel->getActiveSheet()->getStyle('D23')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('E23')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('F23')->getFont()->setBold(true);

        $objPHPExcel->getActiveSheet()->getStyle('A24')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('B24')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('C24')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('D24')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('E24')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('F24')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('G24')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('H24')->getFont()->setBold(true);

        $objPHPExcel->getActiveSheet()->getStyle('A30')->getFont()->setBold(true);

        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(20);

        $objPHPExcel->getActiveSheet()->getRowDimension('6')->setRowHeight(15);

        //set merge kolom
        $objPHPExcel->getActiveSheet()->mergeCells('A1:G1');
        $objPHPExcel->getActiveSheet()->mergeCells('A2:B2');
        $objPHPExcel->getActiveSheet()->mergeCells('C2:E2');

        $objPHPExcel->getActiveSheet()->mergeCells('A3:B3');
        $objPHPExcel->getActiveSheet()->mergeCells('C3:G4');
        $objPHPExcel->getActiveSheet()->mergeCells('A16:G22');

        $objPHPExcel->getActiveSheet()->mergeCells('A4:B4');

        $objPHPExcel->getActiveSheet()->mergeCells('A5:B5');

        $objPHPExcel->getActiveSheet()->mergeCells('A6:B6');
        $objPHPExcel->getActiveSheet()->mergeCells('C5:G5');
        $objPHPExcel->getActiveSheet()->mergeCells('F2:G2');

        $objPHPExcel->getActiveSheet()->mergeCells('C23:H23');

        //$objPHPExcel->getActiveSheet()->mergeCells('A18:A19');

        //$objPHPExcel->getActiveSheet()->mergeCells('B18:C18');
        //$objPHPExcel->getActiveSheet()->mergeCells('D18:E18');



        $objPHPExcel->getActiveSheet()->setCellValue('A1', 'WORKORDER');
        $objPHPExcel->getActiveSheet()->setCellValue('A2', 'Number: '.$data_workorder[0]->workorder_no);
        $objPHPExcel->getActiveSheet()->setCellValue('C2', 'Finish Good: '.$data_workorder[0]->product_code.'  '.$data_workorder[0]->finish_good);
        $objPHPExcel->getActiveSheet()->setCellValue('F2', 'Target: '.$data_workorder[0]->target_pcs.'Pcs');
        $objPHPExcel->getActiveSheet()->setCellValue('H2', 'Date: '.date('d M Y', strtotime(date('Y-m-d'))));
        $objPHPExcel->getActiveSheet()->setCellValue('A3', 'Schedule Date: '.date('d M Y', strtotime($data_workorder[0]->date_schedule)));
        $objPHPExcel->getActiveSheet()->setCellValue('C3', 'Note: '.$data_workorder[0]->note_spk.PHP_EOL.$data_workorder[0]->supplier_name);
        $objPHPExcel->getActiveSheet()->setCellValue('A16', 'Image: ');

        $objPHPExcel->getActiveSheet()->setCellValue('A4', 'Target Date: '.date('d M Y', strtotime($data_workorder[0]->date_target)));

        $objPHPExcel->getActiveSheet()->setCellValue('A5', 'Material Plan');
        $objPHPExcel->getActiveSheet()->setCellValue('A6', 'Material');
        $objPHPExcel->getActiveSheet()->setCellValue('C5', 'Labour Plan');
        $objPHPExcel->getActiveSheet()->setCellValue('C6', 'Pick');
        $objPHPExcel->getActiveSheet()->setCellValue('D6', 'Use');
        $objPHPExcel->getActiveSheet()->setCellValue('E6', 'Waste');

        $objPHPExcel->getActiveSheet()->setCellValue('F6', 'Labour');
        $objPHPExcel->getActiveSheet()->setCellValue('G6', 'Start');
        $objPHPExcel->getActiveSheet()->setCellValue('H6', 'End');

        $objPHPExcel->getActiveSheet()->setCellValue('A23', 'Material Detail');
        $objPHPExcel->getActiveSheet()->setCellValue('C23', 'Labour Detail');

        $objPHPExcel->getActiveSheet()->setCellValue('A24', 'Material');
        $objPHPExcel->getActiveSheet()->setCellValue('B24', 'Barcode');
        $objPHPExcel->getActiveSheet()->setCellValue('C24', 'Weight');
        $objPHPExcel->getActiveSheet()->setCellValue('D24', 'Sisa');
        $objPHPExcel->getActiveSheet()->setCellValue('E24', 'Labour');
        $objPHPExcel->getActiveSheet()->setCellValue('F24', 'Actual Start');
        $objPHPExcel->getActiveSheet()->setCellValue('G24', 'Actual Finish');
        $objPHPExcel->getActiveSheet()->setCellValue('H24', 'Qty');

        // $objPHPExcel->getActiveSheet()->setCellValue('D23', 'Material');
        // $objPHPExcel->getActiveSheet()->setCellValue('E23', 'Barcode');
        // $objPHPExcel->getActiveSheet()->setCellValue('F23', 'Weight');

        /*$objPHPExcel->getActiveSheet()->setCellValue('A18', 'Labour');
        $objPHPExcel->getActiveSheet()->setCellValue('B18', '1');
        $objPHPExcel->getActiveSheet()->setCellValue('D18', '2');
        $objPHPExcel->getActiveSheet()->setCellValue('B19', 'Qty Kirim');
        $objPHPExcel->getActiveSheet()->setCellValue('C19', 'Qty Datang');
        $objPHPExcel->getActiveSheet()->setCellValue('D19', 'Qty Kirim');
        $objPHPExcel->getActiveSheet()->setCellValue('E19', 'Qty Datang');*/




        for($m=0; $m < count($data_workorder_material); $m++)
        {
            //new code
            $no = $m+1;
            $id_material=$data_workorder_material[$m]->ID;
            $code_inventory=$data_workorder_material[$m]->code_inventory;
            $get_unit=$this->master_model->select_in('ms_inventory','unit',"WHERE code='$code_inventory'");
                $unit=$get_unit[0]->unit;
            $data_stock_detail=$this->master_model->data_stock_detail_2($id_material, $code_inventory);
            // echo "<pre>";
            // print_r($data_stock_detail);die();
                $return_material='';
                    $total_qty_waste=0;
                    for($wa=0; $wa < count($data_stock_detail); $wa++)
                    {
                        if($data_stock_detail[$wa]->qty_use==0)
                        {
                            $return_material = $return_material.'';
                            $qty_waste=0;
                        }else
                          {
                            $return_material = $return_material.''.$data_stock_detail[$wa]->qty_waste.', ';
                            $qty_waste=$data_stock_detail[$wa]->qty_waste;
                          }
                            $total_qty_waste=$total_qty_waste+$qty_waste;

                            //new code detail matrial
            $wo_number=$data_stock_detail[$wa]->id_workorder_detail;
            if($wo_number != 0)
                    {
            $dmaterial[] = array('A' => $no.$data_stock_detail[$wa]->code_inventory, 'B' => $data_stock_detail[$wa]->barcode, 'C' => $data_stock_detail[$wa]->qty_use);
             }
            // $ematerial[] = array('D' => $no.$data_stock_detail[$wa]->code_inventory, 'E' => $data_stock_detail[$wa]->barcode, 'F' => $data_stock_detail[$wa]->qty_use);
             //end code
                    }

            //end code
            $material[]= array('A' => $data_workorder_material[$m]->code_inventory, 'B' => $data_workorder_material[$m]->name_inventory, 'C' => $data_workorder_material[$m]->actual, 'D' => $total_qty_waste);

            
        }


        for($a=0; $a < count($data_workorder_labour); $a++)
        {
            $id_labour=$data_workorder_labour[$a]->ID;
            $id_workorder=$data_workorder_labour[$a]->id_workorder;
            $internal=$data_workorder_labour[$a]->internal;

            if($internal==1)
            {
                $internal='Yes';
            }else
            {
                $internal='No';
            }

            $b=$a+1;

            $data_workorder=$this->master_model->select_in('ts_workorder','workorder_no, target_pcs',"WHERE ID=$id_workorder");

            if($data_workorder_labour[$a]->schedule_date=='0000-00-00')
            {
                $start_date='-';
            }else
            {
                $start_date=date('d M Y', strtotime($data_workorder_labour[$a]->schedule_date));
            }

            if($data_workorder_labour[$a]->target_date=='0000-00-00')
            {
                $end_date='-';
            }else
            {
                $end_date=date('d M Y', strtotime($data_workorder_labour[$a]->target_date));
            }


            $isi[]= array('F' => $data_workorder_labour[$a]->name_inventory, 'G' => $start_date, 'H' => $end_date);
        }



        //print_array($isi);
        //exit;

        //set papersize


        foreach($material as $k => $v)
        {
            $col = $k + 7;

            foreach($v as $k1 => $v1)
            {
                $column = $k1.$col;
                $objPHPExcel->getActiveSheet()->setCellValue($column, $v1);


            }
        }

        //NEW CODE
            foreach($dmaterial as $k => $v)
        {
            $col = $k + 25;

            foreach($v as $k1 => $v1)
            {
                $column = $k1.$col;
                $objPHPExcel->getActiveSheet()->setCellValue($column, $v1);


            }
        }

        //  foreach($ematerial as $k => $v)
        // {
        //     $col = $k + 24;

        //     foreach($v as $k1 => $v1)
        //     {
        //         $column = $k1.$col;
        //         $objPHPExcel->getActiveSheet()->setCellValue($column, $v1);


        //     }
        // }
        //END NEW CODE

        /*for($a=0; $a < count($isi); $a++)
        {
            $content= $a*2;

            $b = $a + 9;

            $c = $b + 1;

            for ($i=0; $i <= $content; $i=$i+2)
            {
                //cout<<$i<<" ";
                $content_2 = $isi[$a]->E;

                $objPHPExcel->getActiveSheet()->setCellValue('E'.$b, $content_2);

                $objPHPExcel->getActiveSheet()->mergeCells('E'.$b.':E'.$c);
            };
            //cout<<endl;
        }*/

        // melakukan pengisian data
        foreach($isi as $k => $v)
        {
            $col = $k + 7;


            foreach($v as $k1 => $v1)
            {
                $column = $k1.$col;

                $objPHPExcel->getActiveSheet()->setCellValue($column, $v1);

                $objPHPExcel->getActiveSheet()->getStyle('A'.$col)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

                //$subtotal='=I'.$col.'*K'.$col;

                //$objPHPExcel->getActiveSheet()->setCellValue('M'.$col, $subtotal);
            }
        }
        $tco=$col2+1;


        for($z=0; $z < 17; $z++)
        {
            $line[]=array('F' => '', 'G' => '');
        }

        foreach($line as $k => $v)
        {
            $col = $k + 6;

            foreach($v as $k1 => $v1)
            {
                $column = $k1.$col;
                //$objPHPExcel->getActiveSheet()->setCellValue($column, $v1);

                $objPHPExcel->getActiveSheet()->getRowDimension($col)->setRowHeight(15);
            }
        }

        //new code isi barcode matrial
           // melakukan pengisian data
        foreach($isi as $k => $v)
        {
            $col = $k + 6;


            foreach($v as $k1 => $v1)
            {
                $column = $k1.$col;

                $objPHPExcel->getActiveSheet()->setCellValue($column, $v1);

                $objPHPExcel->getActiveSheet()->getStyle('A'.$col)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

                //$subtotal='=I'.$col.'*K'.$col;

                //$objPHPExcel->getActiveSheet()->setCellValue('M'.$col, $subtotal);
            }
        }
        $tco=$col2+1;
        //end code isi matrial

        //$objPHPExcel->getActiveSheet()->setCellValue('G'.$tco, $subtotal);

        $objWorksheet = $objPHPExcel->getActiveSheet(0);
        $objWorksheet->getStyle('A2:'.$column)->applyFromArray($styleArray);
        $objWorksheet->getStyle('A23:'.$column)->applyFromArray($styleArray);

        $objPHPExcel->getActiveSheet()
            ->setShowGridlines(true);

        $objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
        $objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
        $objPHPExcel->getActiveSheet()->getPageSetup()->setFitToPage(true);
        $objPHPExcel->getActiveSheet()->getPageSetup()->setFitToWidth(1);
        $objPHPExcel->getActiveSheet()->getPageSetup()->setFitToHeight(0);

        $objPHPExcel->getActiveSheet()->getPageMargins()->setTop(0.4);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setLeft(0.5);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setBottom(0.3);

        $gdImage = imagecreatefromjpeg('http://solusiprogram.net/sopro/uploads/design/'.$data_workorder_design[0]->image);

        $objDrawing = new PHPExcel_Worksheet_MemoryDrawing();
        $objDrawing->setName('Sample image');
        $objDrawing->setDescription('Sample image');
        $objDrawing->setImageResource($gdImage);
        $objDrawing->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_JPEG);
        $objDrawing->setMimeType(PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_DEFAULT);
        $objDrawing->setHeight(250);
        $objDrawing->setHeight(250);
        $objDrawing->setCoordinates('A16');
        $objDrawing->setOffsetX(60);
        $objDrawing->setOffsetY(40);
        $objDrawing->setWorksheet($objPHPExcel->getActiveSheet());

        /*
        Orientation and Paper Size:

        $objPHPExcel->getActiveSheet()
            ->getPageSetup()
            ->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
        $objPHPExcel->getActiveSheet()
            ->getPageSetup()
            ->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);

        Page margins:

        $objPHPExcel->getActiveSheet()
            ->getPageMargins()->setTop(1);
        $objPHPExcel->getActiveSheet()
            ->getPageMargins()->setRight(0.75);
        $objPHPExcel->getActiveSheet()
            ->getPageMargins()->setLeft(0.75);
        $objPHPExcel->getActiveSheet()
            ->getPageMargins()->setBottom(1);

        Headers and Footers:

        $objPHPExcel->getActiveSheet()
            ->getHeaderFooter()
            ->setOddHeader('&C&HPlease treat this document as confidential!');
        $objPHPExcel->getActiveSheet()
            ->getHeaderFooter()
            ->setOddFooter('&L&B' . $objPHPExcel->getProperties()->getTitle() .

        Printer page breaks:

        $objPHPExcel->getActiveSheet()
            ->setBreak( 'A10' , PHPExcel_Worksheet::BREAK_ROW );

        Showing grid lines:

        $objPHPExcel->getActiveSheet()
            ->setShowGridlines(true);

        Setting rows/columns to repeat at the top/left of each page

        $objPHPExcel->getActiveSheet()
            ->getPageSetup()
            ->setRowsToRepeatAtTopByStartAndEnd(1, 5);

        Setting the print area:

        $objPHPExcel->getActiveSheet()
            ->getPageSetup()
            ->setPrintArea('A1:E5,G4:M20');



        $objWorksheet->getStyle('D6:D'.$col)->getNumberFormat()->setFormatCode('#,##0');
        $objWorksheet->getStyle('D'.$tco)->getNumberFormat()->setFormatCode('#,##0');
        $objPHPExcel->getActiveSheet()->getStyle('A6:A'.$col)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $objPHPExcel->getActiveSheet()->getStyle('B6:B'.$col)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $objPHPExcel->getActiveSheet()->getStyle('C6:C'.$col)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);;

        $objPHPExcel->getActiveSheet()->getStyle('D6:D'.$col)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $objPHPExcel->getActiveSheet()->getStyle('E6:E'.$col)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $objPHPExcel->getActiveSheet()->getStyle('I6:I'.$col)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        //$objPHPExcel->getActiveSheet()->getStyle('B6:B'.$col)->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('C'.$tco)->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('D'.$tco)->getFont()->setBold(true);

        $objPHPExcel->getActiveSheet()
        ->getStyle('A6:A'.$col)
        ->getAlignment()
        ->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)
        ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER)
        ->setTextRotation(0);*/

        $filename = 'WORKORDER '.$data_workorder[0]->workorder_no.'.xlsx';

        //$filename='just_some_random_name.xlsx'; //save our workbook as this file name
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); //mime type
        header('Content-Disposition: attachment;filename="'.$filename.'"'); //tell browser what's the file name
        header('Cache-Control: max-age=0'); //no cache

        //save it to Excel5 format (excel 2003 .XLS file), change this to 'Excel2007' (and adjust the filename extension, also the header mime type)
        //if you want to save it as .XLSX Excel 2007 format
        $objWriter = IOFactory::createWriter($objPHPExcel, 'Excel2007');

        //force user to download the Excel file without writing it to server's HD
        $objWriter->save('php://output');


        exit;
    }
?>
