<?php

//the global file operation
$do = isset($_GET['do']) ? $_GET['do'] : '';
if ($do != "exportAsExcel") {
    include("../public/impOpreation.php");
}

//global varable
global $showoutside;

//to check if the page from .htacess
//$showoutside = $_GET['sn'];
// get the config file
include_once("../public/config.php");
include("../library/uploadImages.php");
include '../library/Classes/PHPExcel/IOFactory.php';
include_once("dailyentryfun.php");

//here the db files that include in the file
include("../public/include_dao.php");

//Accountstree
require_once('../models/dao/AccountstreeDAO.class.php');
require_once('../models/dto/Accountstree.class.php');
require_once('../models/mysql/AccountstreeMySqlDAO.class.php');
require_once('../models/mysql/ext/AccountstreeMySqlExtDAO.class.php');
//Programsetting
require_once('../models/dao/ProgramsettingsDAO.class.php');
require_once('../models/dto/Programsetting.class.php');
require_once('../models/mysql/ProgramsettingsMySqlDAO.class.php');
require_once('../models/mysql/ext/ProgramsettingsMySqlExtDAO.class.php');
//Accountstreesetting
require_once('../models/dao/AccountstreesettingDAO.class.php');
require_once('../models/dto/Accountstreesetting.class.php');
require_once('../models/mysql/AccountstreesettingMySqlDAO.class.php');
require_once('../models/mysql/ext/AccountstreesettingMySqlExtDAO.class.php');

//----------------------------------------
//Sellbill
require_once('../models/dao/SellbillDAO.class.php');
require_once('../models/dto/Sellbill.class.php');
require_once('../models/mysql/SellbillMySqlDAO.class.php');
require_once('../models/mysql/ext/SellbillMySqlExtDAO.class.php');
//Returnsellbill
require_once('../models/dao/ReturnsellbillDAO.class.php');
require_once('../models/dto/Returnsellbill.class.php');
require_once('../models/mysql/ReturnsellbillMySqlDAO.class.php');
require_once('../models/mysql/ext/ReturnsellbillMySqlExtDAO.class.php');
//Sellbillandrutern
require_once('../models/dao/SellbillandruternDAO.class.php');
require_once('../models/dto/Sellbillandrutern.class.php');
require_once('../models/mysql/SellbillandruternMySqlDAO.class.php');
require_once('../models/mysql/ext/SellbillandruternMySqlExtDAO.class.php');

require_once('../models/dao/YoutubeLinkDAO.class.php');
require_once('../models/dto/YoutubeLink.class.php');
require_once('../models/mysql/YoutubeLinkMySqlDAO.class.php');
require_once('../models/mysql/ext/YoutubeLinkMySqlExtDAO.class.php');

//get the do the action
$do = $_GET['do'];

$langs = $_SESSION['erp_lang'];
include_once("../views/languages/$langs/success.php");
include_once("../views/languages/$langs/error.php");

/* ======================

  Controller Name :- accountstree

  OPERTATION in Controller

  1- display add form, add
  2- add in tbl
  3- display show and tempdelete
  4- edit
  5- update

  ======================== */

//here the global templates
if ($do != "exportAsExcel") {
    $smarty->display("header.html");
}
//here goes the instances and general variables
//Accountstree
$accountsTree = new Accountstree();
$accountsTreeDAO = new AccountstreeMySqlDAO();
$accountsTreeEX = new AccountstreeMySqlExtDAO();
//Programsetting
$Programsetting = new Programsetting();
$ProgramsettingDAO = new ProgramsettingsMySqlDAO();
$ProgramsettingEX = new ProgramsettingsMySqlExtDAO();

//Accountstreesetting
$accountsTreeSetting = new Accountstreesetting();
$accountsTreeSettingDAO = new AccountstreesettingMySqlDAO();
$accountsTreeSettingEX = new AccountstreesettingMySqlExtDAO();

$today = date("Y-m-d");

$Programsettingdata = $ProgramsettingDAO->load(1);
$smarty->assign("Programsettingdata", $Programsettingdata);
//---------------------------------------------------------
//Sellbill
$sellBill = new Sellbill();
$sellBillDAO = new SellbillMySqlDAO();
$sellBillEX = new SellbillMySqlExtDAO();
//Returnsellbill
$returnSellBill = new Returnsellbill();
$returnSellBillDAO = new ReturnsellbillMySqlDAO();
$returnSellBillEX = new ReturnsellbillMySqlExtDAO();
//Sellbillandrutern
$sellBillAndRutern = new Sellbillandrutern();
$sellBillAndRuternDAO = new SellbillandruternMySqlDAO();
$sellBillAndRuternEX = new SellbillandruternMySqlExtDAO();

$youtubeLink = new YoutubeLink();
$youtubeLinkDAO = new YoutubeLinkMySqlDAO();
$youtubeLinkEX = new YoutubeLinkMySqlExtDAO();

//check and use the condition that suite this action
if (empty($do)) {
    //here the permssion check
    include_once("../public/authentication.php");


//    $maxNoOfLevels = 1;
//    getMaxNoOfLevels(0, 1);
//    $levelSetting = array();
//
    ##make no of levels fixed =6 "now we dont count tree levels"
    $maxNoOfLevels = 6;
    for ($i = 1; $i <= $maxNoOfLevels; $i++) {
        $level = $accountsTreeSettingDAO->queryByLevelno($i);
        if (count($level) < 1) {
            $accountsTreeSetting->levelno = $i;
            $accountsTreeSetting->nooffields = 1;
            $id = $accountsTreeSettingDAO->insert($accountsTreeSetting);
            $level = $accountsTreeSettingDAO->load($id);
        } else {
            $level = $level[0];
        }
        //array_push($levelSetting, $level);
        $levelSetting[$i] = $level;
    }
    $smarty->assign("levelSetting", $levelSetting);

    $youtubes = $youtubeLinkDAO->queryAll();
    $smarty->assign("youtubes", $youtubes);
    ########################
    #no need to get them they will be get in ajax or the modal
//    $accountsTree = $accountsTreeDAO->queryAll();
//    $smarty->assign("accountsTree", $accountsTree);
//    $accountsTreeOrdered = array();
//    orderAsTree(0, 0);
//    $smarty->assign("accountsTreeOrdered", $accountsTreeOrdered);
    ##expensesController.php
    $allParents = getExtepensesTypeParents();
    $smarty->assign("allParents", $allParents);

    // here the function that do the action
    $sum = 0;
    $outputString = display_children(0, 0, 0, 0);  //display the tree
    $smarty->assign("treeHTML", $outputString);

    //بضاعة اول المدة
    checkStartGoods();

    $smarty->assign("calTreeNodes", $_COOKIE['calTreeNodes']);
    $smarty->assign("showClientsAtTree", $_COOKIE['showClientsAtTree']);
    $smarty->assign("showSuppliersAtTree", $_COOKIE['showSuppliersAtTree']);

    //here the smarty templates
    $smarty->display("accountstreeview/add.html");
} elseif ($do == "set") {
    try {

        //20 year long cookie
        setcookie("calTreeNodes", (int) $_POST['calTreeNodes'], (time() + (20 * 365 * 24 * 60 * 60)));
        setcookie("showClientsAtTree", (int) $_POST['showClientsAtTree'], (time() + (20 * 365 * 24 * 60 * 60)));
        setcookie("showSuppliersAtTree", (int) $_POST['showSuppliersAtTree'], (time() + (20 * 365 * 24 * 60 * 60)));


//        $maxNoOfLevels = 1;
//        getMaxNoOfLevels(0, 1);
        ##make no of levels fixed =6 "now we dont count tree levels"
        $maxNoOfLevels = 6;

        for ($i = 1; $i <= $maxNoOfLevels; $i++) {
            $level = $accountsTreeSettingDAO->queryByLevelno($i);
            if (count($level) < 1) {
                //level not exist insert
                $accountsTreeSetting->levelno = $i;
                $accountsTreeSetting->nooffields = 1;
                $id = $accountsTreeSettingDAO->insert($accountsTreeSetting);
                $level = $accountsTreeSettingDAO->load($id);
            } else {
                //level exist update
                $level = $level[0];
                $leveldigits = filter_input(INPUT_POST, "level" . $i);
                if (!empty($leveldigits)) {
                    $level->nooffields = $leveldigits;
                    $accountsTreeSettingDAO->update($level);
                }
            }
        }


        header("location:accountstree.php");
    } catch (Exception $e) {
        header("location:?do=error");
    }
} elseif ($do == "uploadtree") {
    $smarty->display("accountstreeview/uploadexceltree.html");
} elseif ($do == "addtreefromexcel") {
//    try {
    addTreeFromExcel();
//        header("location:?do=sucess");
//    } catch (Exception $e) {
//        //echo $e;
//        header("location:?do=error");
//    }
} elseif ($do == "exportAsExcel") {
    //start excel
    $objPHPExcel = new PHPExcel();
    $objPHPExcel->setActiveSheetIndex(0);
    $objPHPExcel->getActiveSheet()
            ->setTitle("شجرة الحسابات");
    PHPExcel_Shared_Font::setAutoSizeMethod(PHPExcel_Shared_Font::AUTOSIZE_METHOD_EXACT);
    //set table head
    $objPHPExcel->getActiveSheet()
            ->SetCellValue('A1', 'الكود')
            ->SetCellValue('B1', 'اسم العنصر')
            ->SetCellValue('C1', 'والد العنصر')
            ->SetCellValue('D1', 'طبيعة العنصر')
            ->SetCellValue('E1', 'فى تقرير')
            ->SetCellValue('F1', 'القائمة');
    //style
    $styleArray = array(
        'font' => array(
            'bold' => true,
            'size' => 12,
            'color' => array(
                'rgb' => 'blue'
            ),
            'name' => 'Verdana'
        ),
        'borders' => array(
            'allborders' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
            ),
        ),
        'fill' => array(
            'type' => PHPExcel_Style_Fill::FILL_SOLID,
            'color' => array(
                'rgb' => 'adadad'
            )
        ),
    );
    $objPHPExcel->getActiveSheet()
            ->getStyle("A1:F1")
            ->applyFromArray($styleArray);

    $RowStartProduct = 2;
    display_children(0, 0, 0, 0);  //display the tree
    ##
    foreach (range('A', 'F') as $columnID) {
        $objPHPExcel->getActiveSheet()->getColumnDimension($columnID)->setWidth(25); //->setAutoSize(true);
    }


    //https://github.com/PHPOffice/PHPExcel/tree/develop/Documentation/markdown/Overview
    // Redirect output to a client’s web browser (Excel5)
    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment;filename="شجرة الحسابات.xls"');
    header('Cache-Control: max-age=0');
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
    $objWriter->save('php://output');
    //Clearing a Workbook from memory
    $objPHPExcel->disconnectWorksheets();
    unset($objPHPExcel);
} elseif ($do == "sucess") {
    //here the smarty templates
    $smarty->display("succes.html");
} elseif ($do == "error") {
    //here the smarty templates
    $smarty->display("error.html");
}
//here the global templates
if ($do != "exportAsExcel") {
    $smarty->assign("accountstree", 1);

    $smarty->display("footer.html");
}

/* ===============================
  function in this CONTROLLER
  ================================ */

function getExtepensesTypeParents() {
    global $accountsTreeEX;
    global $accountsTreeDAO;

    $allParents = $accountsTreeEX->getExtepensesTypeParents();
    return $allParents;
}

function display_children($parent, $level, $itr, $itr2) {//$itr is itr without zeros  , $itr2 wit zeros
    global $accountsTreeDAO;
    global $accountsTreeEX;
    global $accountsTreeSettingDAO;
    global $outputString;
    global $sum;
    global $levelSetting;
    global $maxNoOfLevels;

    //note: url of tree ##http://jsfiddle.net/jhfrench/GpdgF/
    // retrieve all children of $parent

    $result = $accountsTreeEX->queryByParentExt($parent);
    if (count($result) > 0) {
        // display each child
        $i = 1;
        foreach ($result as $type) {
            //generte laying order  "use a method from the two coming"
            $preDigits = '';
            if (strlen($i) != $levelSetting[($level + 1)]->nooffields) {
                $noDigitsLeft = $levelSetting[($level + 1)]->nooffields - strlen($i);
                for ($j = 0; $j < $noDigitsLeft; $j++) {
                    $preDigits .= '0';
                }
            }

            if ($itr == 0) {
                $nodeName = $preDigits . $i; //. $type->id;
            } else {
                $nodeName = $itr . $preDigits . $i; //. $type->id;
            }
            //fill remaining with zeros to the right // optional
            $nodeNameWithZeros = $nodeName;
//            for ($ii = ($level + 2); $ii <= $maxNoOfLevels; $ii++) {
//                for ($j = 0; $j < $levelSetting[$ii]->nooffields; $j++) {
//                    $nodeNameWithZeros .= '0';
//                }
//            }
            //----------------------------------------------------------------
            //make sure node name is saved in db for searching purpose
            if ($nodeNameWithZeros !== $type->layingOrder) {
                R::exec('update accountstree set layingOrder =' . $nodeNameWithZeros . ' where id= ' . $type->id);
//                $type->layingOrder = $nodeNameWithZeros;
//                $accountsTreeDAO->update($type);
            }
            //
            exportToExcel($type, $level);
            //check for expenses for this category
            $childDataCount = R::getCell('select count(*) as c from accountstree where parent = ' . $type->id);
            if ($childDataCount > 0) {
                $sum = 0;
                getTheTrueValue($type->id, $sum);
                $type->theValue = $sum;
            }

            $colorStr = '';
            if ($type->del != 0) {
                $colorStr = 'color:red;';
            }

            $outputString .= '<li>
									<span data-value="' . $type->id . '" style="' . $colorStr . '"><strong>' . $nodeNameWithZeros . '</strong> ' . $type->customName . '</span>

                                                                        <a class="btn btn-default" style="display: inline !important;padding: 5px 5px !important;" data-id="' . $type->id . '">' . $type->theValue . '</a>';
            if (($_COOKIE['showClientsAtTree'] == 0 && ($type->id == 58 || $type->id == 60)) || ($_COOKIE['showSuppliersAtTree'] == 0 && ($type->id == 81 || $type->id == 87))) {

            } else if ($childDataCount > 0 && $type->itemtype2 == 0) {
                $outputString .= '<a class="btn btn-default bookicon infoLeaf" style="display: inline !important;width:8%;text-align: center !important;padding: 5px 13px !important;" data-id="' . $type->id . '"></a>';
            } else {
                $outputString .= '<a class="btn btn-default infoicon infoLeaf" style="display: inline !important;width:8%;text-align: center !important;padding: 5px 13px !important;" data-id="' . $type->id . '"></a>';
            }

            if ($type->itemfrom == 0) {

            } else {

                $outputString .= '<a class = "btn btn-default deleteicon  delLeaf" data-id = "' . $type->id . '" style = "display: inline !important;margin-right:-3px;width:8%;text-align: center !important;padding: 5px 13px !important;"></a>';
            }
            $outputString .= '<a class = "btn btn-default editicon editLeaf" style = "display: inline !important;width:8%;text-align: center !important;padding: 5px 13px !important;" data-id = "' . $type->id . '"></a>
                    <input type = "text" class = "form-control orderLeaf positiveNo" value = "' . $type->theOrder . '" data-id = "' . $type->id . '" style = "display: inline !important;margin-right:3px;width:16%;text-align: center !important;padding: 5px 13px !important;"/>';





            $i++;
            if (($_COOKIE['showClientsAtTree'] == 0 && ($type->id == 58 || $type->id == 60)) || ($_COOKIE['showSuppliersAtTree'] == 0 && ($type->id == 81 || $type->id == 87))) {
                //do noting.
            } else if ($childDataCount > 0) {
                $outputString .= '<ul>';
                display_children($type->id, $level + 1, $nodeName, $nodeNameWithZeros);
                $outputString .= '</ul>';
            }
            $outputString .= '</li>';
        }
    }


    return $outputString;
}

function getTheTrueValue($parent, $sum) {
    global $accountsTreeDAO;
    global $accountsTreeEX;
    global $sum;

    if (($_COOKIE['showClientsAtTree'] == 0 && ($parent == 58 || $parent == 60)) || ($_COOKIE['showSuppliersAtTree'] == 0 && ($parent == 81 || $parent == 87))) {
        $lastNodeSum = (float) $accountsTreeEX->getLastNodeSumById($parent);
        $sum += $lastNodeSum;
    } else if ($_COOKIE['calTreeNodes'] == 1) {
        $result = R::getAll('select id,theValue from accountstree where parent = ' . $parent);
        foreach ($result as $type) {
            $sum += $type['theValue'];
            getTheTrueValue($type['id'], $sum);
        }
    }
}

function orderAsTree($parent, $level) {
    global $accountsTreeDAO;
    global $accountsTreeEX;
    global $accountsTreeOrdered;
    //global $outputString;
    // retrieve all children of $parent
    $result = $accountsTreeEX->queryByParentExt($parent);
    if (count($result) > 0) {
        // display each child

        foreach ($result as $type) {
            $type->notes = $level;
            array_push($accountsTreeOrdered, $type);
            orderAsTree($type->id, $level + 1);
        }
    }
}

function getMaxNoOfLevels($parent, $level) {
    global $accountsTreeDAO;
    global $accountsTreeEX;
    global $maxNoOfLevels;
    // retrieve all children of $parent
    $result = $accountsTreeEX->queryByParentExt($parent);
    if ($level > $maxNoOfLevels) {
        $maxNoOfLevels = $level;
    }
    if (count($result) > 0) {
        // display each child
        foreach ($result as $type) {
            getMaxNoOfLevels($type->id, $level + 1);
        }
    }
}

//بضاعة اول المدة
function checkStartGoods() {
    //$sellBill
    global $sellBill;
    global $sellBillDAO;
    global $sellBillEX;
    //Returnsellbill
    global $returnSellBill;
    global $returnSellBillDAO;
    global $returnSellBillEX;
    //Sellbillandrutern
    global $sellBillAndRutern;
    global $sellBillAndRuternDAO;
    global $sellBillAndRuternEX;

    global $smarty;

    $flag = 0;

    $sellBill = $sellBillEX->checkIfThereIsABill();
    $returnSellBill = $returnSellBillEX->checkIfThereIsABill();
    $sellBillAndRutern = $sellBillAndRuternEX->checkIfThereIsABill();

    if (count($sellBill) == 0 && count($returnSellBill) == 0 && count($sellBillAndRutern) == 0) {
        $flag = 1;
    }
    $smarty->assign("startGoods", $flag);
}

function addTreeFromExcel() {
    global $accountsTree;
    global $accountsTreeDAO;
    global $accountsTreeEX;

## upload file
    $handle = new upload($_FILES['productssheet']);
    $excelfileName = uploadfile($handle, "../upload/products");
    $inputFileName = "../upload/products/" . $excelfileName;
    //  Read your Excel workbook
    //Transaction
    $mytransactions = new Transaction();
    try {
        $accountsTreeEX->Truncate();
        $inputFileType = PHPExcel_IOFactory::identify($inputFileName);
        $objReader = PHPExcel_IOFactory::createReader($inputFileType);
        //$objReader->setUTFEncoder('iconv');
        //$objReader::setOutputEncoding('UTF-8');
        $objPHPExcel = $objReader->load($inputFileName);



        //  Get worksheet dimensions
        $sheet = $objPHPExcel->getSheet(0);
        $highestRow = $sheet->getHighestRow();
        $highestColumn = $sheet->getHighestColumn();
        for ($row = 2; $row <= $highestRow; $row++) {
            //  Read a row of data into an array
            $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE);
            $rowData = $rowData[0];

            ##getdata temp
            $code = $rowData[1];
            $accountName = $rowData[3];
            $accountNameEn = $rowData[4];
            $itemType3 = $rowData[5];
            $parentCode = $rowData[6];
            $level = $rowData[7];
            $accountNature = $rowData[16];
            $listName = $rowData[17];
//            ##getdata temp
//            $col = 0;
//            $code = $rowData[$col];
//            $col++;
//            $col++;
//            $accountName = $rowData[$col];
//            $col++;
//            $accountNameEn = $rowData[$col];
//            $col++;
//            $itemType3 = $rowData[$col];
//            $col++;
//            $parentCode = $rowData[$col];
//            $col++;
//            $level = $rowData[$col];
//            $col++;
//            $accountNature = $rowData[$col];
//            $col++;
//            $listName = $rowData[$col];

            if (!empty($accountName)) {
                $parentId = 0;
                $parentData = $accountsTreeDAO->queryByLayingOrder($parentCode);
                if (count($parentData) > 0) {
                    $parentId = $parentData[0]->id;
                }
                $itemtype = -1; //اصول خصوم مصروفات ايرادات؟؟؟ هااغير مع الشجرة الجديده لانهم 6 اساسين مش  4?????????????????????????
                $itemtype2 = 1; //0 parent "branch" , 1 final element "leaf"???????????????????????
                $theValue = 0;
                //
                switch ($accountNature) {
                    case 'مدين':
                        $accountNature = 0;
                        break;
                    case 'دائن':
                        $accountNature = 1;
                        break;
                }
                //
                $listId = 0;
                switch ($listName) {
                    case 'ميزانية':
                        $listId = 1;
                        break;
                    case 'قائمة الدخل':
                        $listId = 2;
                        break;
                }
                $reportid = $listId; //same as parent "given in excel" also it is like listid


                $accountsTree->customName = $accountName;
                $accountsTree->name = $accountName;
                $accountsTree->customNameEn = $accountNameEn;
                $accountsTree->nameEn = $accountNameEn;
                $accountsTree->parent = $parentId;
                $accountsTree->itemtype = $itemtype;
                $accountsTree->itemtype2 = $itemtype2;
                $accountsTree->itemtype3 = $itemType3;
                $accountsTree->notes = '';
                $accountsTree->theOrder = 0;
                $accountsTree->layingOrder = $code;
                $accountsTree->del = 0;
                $accountsTree->theValue = 0;
                $accountsTree->userid = $_SESSION['userid'];
                $accountsTree->sysdate = date("Y-m-d H:i:s");
                $accountsTree->itemfrom = 0; //added by program
                $accountsTree->reportid = $reportid;
                $accountsTree->listId = $listId;
                $accountsTree->accountNature = $accountNature;
                //
                //check if name is unique
                //$check = isUniqueName($name,$parentId);
                //if ($check == TRUE) {
                $id = $accountsTreeDAO->insert($accountsTree);
                //}
            }
        }

        unlink($inputFileName);
        $mytransactions->commit();
    } catch (Exception $e) {
        $mytransactions->rollback();
        //die('Error loading file "' . pathinfo($inputFileName, PATHINFO_BASENAME) . '": ' . $e->getMessage());
    }
}

function exportToExcel($type, $level) {
    global $objPHPExcel;
    global $RowStartProduct;

    if (isset($_GET['do']) && $_GET['do'] == "exportAsExcel") {
        $accountNature = 'مدينة';
        if ($type->accountNature == 1)
            $accountNature = 'دائنة';


        $report = '-';
        if ($type->reportid == 1)
            $report = 'متاجرة';
        else if ($type->reportid == 2)
            $report = 'ارباح و خسائر "الدخل"';



        $list = '-';
        if ($type->listId == 1)
            $list = 'ميزانية';
        else if ($type->listId == 2)
            $list = 'قائمة الدخل';
        $col = 0;
        $objPHPExcel->getActiveSheet()->getCell('A' . $RowStartProduct)->setValueExplicit($type->layingOrder, PHPExcel_Cell_DataType::TYPE_STRING);
        //$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $RowStartProduct, $type->layingOrder);
        $col++;
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $RowStartProduct, $type->customName);
        $col++;
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $RowStartProduct, $type->parentName);
        $col++;
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $RowStartProduct, $accountNature);
        $col++;
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $RowStartProduct, $report);
        $col++;
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $RowStartProduct, $list);


        $levelColor = array('428bca', '39b3d7', 'ffffff', 'ffffff', 'ffffff', 'ffffff', 'ffffff', 'ffffff', 'ffffff', 'ffffff', 'ffffff', 'ffffff', 'ffffff');
        //style
        $styleArray = array(
            'font' => array(
                //'bold' => true,
                'size' => 12,
                'color' => array(
                    'rgb' => 'blue'
                ),
                'name' => 'Verdana'
            ),
            'borders' => array(
                'allborders' => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN,
                ),
            ),
            'fill' => array(
                'type' => PHPExcel_Style_Fill::FILL_SOLID,
                'color' => array(
                    'rgb' => $levelColor[$level]
                )
            ),
        );
        $objPHPExcel->getActiveSheet()
                ->getStyle("A$RowStartProduct:F$RowStartProduct")
                ->applyFromArray($styleArray);

        $RowStartProduct++;
    }
}

?>