File: /home/mostafedeg/public_html/erp/controllers/accountstree.php
<?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++;
}
}
?>