<?php

//the global file operation
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");

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

//Breadcrumb
require_once("../library/breadcrumb.php");
include '../library/Classes/PHPExcel/IOFactory.php';
include_once("dailyentryfun.php");
include_once("billsfunctions.php");
include_once("billsfunctionsbuy.php");

//Workshop
require_once('../models/dao/WorkshopDAO.class.php');
require_once('../models/dto/Workshop.class.php');
require_once('../models/mysql/WorkshopMySqlDAO.class.php');
require_once('../models/mysql/ext/WorkshopMySqlExtDAO.class.php');
//Bill
require_once('../models/dao/BillsDAO.class.php');
require_once('../models/dto/Bill.class.php');
require_once('../models/mysql/BillsMySqlDAO.class.php');
require_once('../models/mysql/ext/BillsMySqlExtDAO.class.php');
//Billsproduct
require_once('../models/dao/BillsproductsDAO.class.php');
require_once('../models/dto/Billsproduct.class.php');
require_once('../models/mysql/BillsproductsMySqlDAO.class.php');
require_once('../models/mysql/ext/BillsproductsMySqlExtDAO.class.php');
//Client
require_once('../models/dao/ClientDAO.class.php');
require_once('../models/dto/Client.class.php');
require_once('../models/mysql/ClientMySqlDAO.class.php');
require_once('../models/mysql/ext/ClientMySqlExtDAO.class.php');
//User
require_once('../models/dao/UserDAO.class.php');
require_once('../models/dto/User.class.php');
require_once('../models/mysql/UserMySqlDAO.class.php');
require_once('../models/mysql/ext/UserMySqlExtDAO.class.php');
//Workshop
require_once('../models/dao/WorkshopDAO.class.php');
require_once('../models/dto/Workshop.class.php');
require_once('../models/mysql/WorkshopMySqlDAO.class.php');
require_once('../models/mysql/ext/WorkshopMySqlExtDAO.class.php');
//Billsproduct
require_once('../models/dao/BillsproductsDAO.class.php');
require_once('../models/dto/Billsproduct.class.php');
require_once('../models/mysql/BillsproductsMySqlDAO.class.php');
require_once('../models/mysql/ext/BillsproductsMySqlExtDAO.class.php');

//Toworkshoporder
require_once('../models/dao/ToworkshoporderDAO.class.php');
require_once('../models/dto/Toworkshoporder.class.php');
require_once('../models/mysql/ToworkshoporderMySqlDAO.class.php');
require_once('../models/mysql/ext/ToworkshoporderMySqlExtDAO.class.php');
//Toworkshoporderbill
require_once('../models/dao/ToworkshoporderbillDAO.class.php');
require_once('../models/dto/Toworkshoporderbill.class.php');
require_once('../models/mysql/ToworkshoporderbillMySqlDAO.class.php');
require_once('../models/mysql/ext/ToworkshoporderbillMySqlExtDAO.class.php');
//Branch
require_once('../models/dao/BranchDAO.class.php');
require_once('../models/dto/Branch.class.php');
require_once('../models/mysql/BranchMySqlDAO.class.php');
require_once('../models/mysql/ext/BranchMySqlExtDAO.class.php');

require_once('../models/dao/BillsbuyDAO.class.php');
require_once('../models/dto/Billsbuy.class.php');
require_once('../models/mysql/BillsbuyMySqlDAO.class.php');
require_once('../models/mysql/ext/BillsbuyMySqlExtDAO.class.php');

require_once('../models/dao/BillsproductsbuyDAO.class.php');
require_once('../models/dto/Billsproductsbuy.class.php');
require_once('../models/mysql/BillsproductsbuyMySqlDAO.class.php');
require_once('../models/mysql/ext/BillsproductsbuyMySqlExtDAO.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 :-storeCTRL

  OPERTATION in Controller

  1-display add form
  2- add in storerawmaterails tble
  3-display sucess or error
  4-display show and delete
  5-display edit
  ======================== */

//here the global templates
if ($do != "print" && $do != "exportexcel") {
    $smarty->display("header.html");
}


//Client
$client = new Client();
$clientDAO = new ClientMySqlDAO();
$clientExt = new ClientMySqlExtDAO();
$ClientareaDAO = new ClientareaMySqlDAO();
//Product
$product = new Product();
$productDAO = new ProductMySqlDAO();
$productExt = new ProductMySqlExtDAO();

//Productcat
$productCatDAO = new ProductcatMySqlDAO();
$productCatExt = new ProductcatMySqlExtDAO();

//user
$user = new User();
$userDAO = new UserMySqlDAO();
$userEX = new UserMySqlExtDAO();

//bill
$bills = new Bill();
$billsDAO = new BillsMySqlDAO();
$billsEX = new BillsMySqlExtDAO();

//billsproducts
$billsProducts = new Billsproduct();
$billsProductsDAO = new BillsproductsMySqlDAO();
$billsProductsEX = new BillsproductsMySqlExtDAO();

//Workshop
$workshop = new Workshop();
$workshopDAO = new WorkshopMySqlDAO();
$workshopEX = new WorkshopMySqlExtDAO();

//Toworkshoporder
$toWorkshopOrder = new Toworkshoporder();
$toWorkshopOrderDAO = new ToworkshoporderMySqlDAO();
$toWorkshopOrderEX = new ToworkshoporderMySqlExtDAO();
//Toworkshoporderbill
$toWorkshopOrderBill = new Toworkshoporderbill();
$toWorkshopOrderBillDAO = new ToworkshoporderbillMySqlDAO();
$toWorkshopOrderBillEX = new ToworkshoporderbillMySqlExtDAO();
//Branch
$Branch = new Branch();
$BranchDAO = new BranchMySqlDAO();
$BranchEX = new BranchMySqlExtDAO();

// insurancecompanies
$insuranceCompanies = new Insurancecompanie();
$insuranceCompaniesDAO = new InsurancecompaniesMySqlDAO();


//paymentnetworks
$paymentNetworks = new Paymentnetwork();
$paymentNetworksDAO = new PaymentnetworksMySqlDAO();

//Store
$myStoreRecord = new StoreMySqlDAO();

//doctorkashf
$doctorKashf = new Doctorkashf();
$doctorKashfDAO = new DoctorkashfMySqlDAO();
$doctorKashfEX = new DoctorkashfMySqlExtDAO();


$storeDetail = new Storedetail();
$storeDetailDAO = new StoredetailMySqlDAO();
$storeDetailEX = new StoredetailMySqlExtDAO();

$mySave = new Save();
$mySaveDAO = new SaveMySqlDAO();
$mySaveEx = new SaveMySqlExtDAO();

//# kashf
$kashf = new Kashf();
$kashfDAO = new KashfMySqlDAO();
$kashfDAOEx = new KashfMySqlExtDAO();


//Dailyentry
$dailyEntry = new Dailyentry();

//Dailyentrycreditor ط¯ط§ط¦ظ†
$dailyEntryCreditor = new Dailyentrycreditor();

//Dailyentrydebtor ظ…ط¯ظٹظ†
$dailyEntryDebtor = new Dailyentrydebtor();

$accountsTree = new Accountstree();
$accountsTreeDAO = new AccountstreeMySqlDAO();
$accountsTreeEX = new AccountstreeMySqlExtDAO();

//Programsetting
$Programsetting = new Programsetting();
$ProgramsettingDAO = new ProgramsettingsMySqlDAO();
$ProgramsettingEX = new ProgramsettingsMySqlExtDAO();

$productRequest = new Productrequest();
$productRequestDAO = new ProductrequestsMySqlDAO();
$productRequestExt = new ProductrequestsMySqlExtDAO();


$Programsettingdata = $ProgramsettingDAO->load(1);
$smarty->assign("Programsettingdata", $Programsettingdata);

//savedaily
$mySavedaily = new Savedaily();
$mySavedailyRecord = new SavedailyMySqlDAO();
$mySavedailyEx = new SavedailyMySqlExtDAO();


$userdata = $userDAO->load($_SESSION['userid']);
$smarty->assign("userdata", $userdata);
if ($userdata->userstoreid == "0") {
    $userdata->userstoreid = 1; // if he use all stores use defalt store
}

//Usergroup
$Usergroup = new Usergroup();
$UsergroupDAO = new UsergroupMySqlDAO();
$UsergroupEX = new UsergroupMySqlExtDAO();

$billsBuy = new Billsbuy();
$billsBuyDAO = new BillsbuyMySqlDAO();
$billsBuyEX = new BillsbuyMySqlExtDAO();

$billsProductsBuy = new Billsproductsbuy();
$billsProductsBuyDAO = new BillsproductsbuyMySqlDAO();
$billsProductsBuyEX = new BillsproductsbuyMySqlExtDAO();

//supplier
$supplier = new Supplier();
$supplierDAO = new SupplierMySqlDAO();
$supplierEX = new SupplierMySqlExtDAO();

//Bank
$bankDAO = new BankMySqlDAO();
$bankEX = new BankMySqlExtDAO();

//account
$account = new Bankaccount();
$accountDAO = new BankaccountMySqlDAO;
$accountExtDAO = new BankaccountMySqlExtDAO;

$smarty->assign("today", date("Y-m-d"));
$smarty->assign("now", date("Y-m-d H:i:s"));


//$transactions = new Transaction();
//Breadcrumb
$breadcrumbObject = new Breadcrumb();

//create object from user
## dispaly  add form
if (!isset($_GET['do']) || $_GET['do'] == "empty" || $_GET['do'] == "addview2") {
    include_once("../public/authentication.php");

    ## bill no generate

    $branchId = $_SESSION['branchId'];
    $today = date('y');
    $billNo = $branchId;
    $billNo .= $today;

    $bills = $billsEX->searchInBillNo($billNo);
    if (count($bills) > 0) {
        $billNo = $bills->billno + 1;
        if ($branchId == 0) {
            $billNo = '0' . $billNo;
        }
    } else {
        $billNo .= '00001';
    }

    $smarty->assign("billNo", $billNo);

    ## client Data
    $clientData = getClientData();
    $smarty->assign('clientData', $clientData);

    ## insurance Companies
    $insuranceCompanies = $insuranceCompaniesDAO->queryByDeleted(0);
    $smarty->assign('insuranceCompanies', $insuranceCompanies);

    ## payment Networks
    $paymentNetworks = $paymentNetworksDAO->queryByDeleted(0);
    $smarty->assign('paymentNetworks', $paymentNetworks);

    $clientarea = $ClientareaDAO->queryAll();
    $smarty->assign('clientarea', $clientarea);


    ## stores
    $storeData = loadStore();
    $smarty->assign("storeData", $storeData);

    $smarty->assign("now", date('Y-m-d h:i:s'));
    $smarty->assign("today", date('Y-m-d'));

    $productRequestArray = array();
    $productRequestIds = '';
    $isProductRequest = filter_input(INPUT_POST, "isProductRequest");

    if ($isProductRequest == 1) {
        $choosedItemArr = $_POST['choosedItem'];
        if (count($choosedItemArr) > 0) {
            foreach ($choosedItemArr as $productRequestId) {
                $productRequest = $productRequestDAO->load($productRequestId);
                array_push($productRequestArray, $productRequest);
                $productRequestIds .= $productRequestId . '-';


                $product = $productDAO->load($productRequest->productid);
                $productRequest->productName = $product->productName;
                $productCat = $productCatDAO->load($product->productCatId);
                $productRequest->productCatName = $productCat->productCatName;


                $parentId = $product->productCatId;
                $pathArr = getProductPath_recursive($parentId, $categories, 0);
                $productRequest->productName = $product->productName . '/' . $pathArr;
            }
        }

        $smarty->assign("productRequestIds", $productRequestIds);
        $smarty->assign("billProducts", $productRequestArray);
        $smarty->assign("billProductsCount", count($productRequestArray));
    }

    $smarty->assign('isProductRequest', $isProductRequest);


    //check direct save
    $directSave = 0;
    $usergroup = $UsergroupDAO->load($_SESSION['usergroupid']);

    if ($userdata->directSaveBills == 1 && $usergroup->directSaveBills == 1) {
        $directSave = 1;
    }
    $percentageDiscount = $usergroup->percentageDiscount;
    $smarty->assign("percentageDiscount", $percentageDiscount);

    $smarty->assign("directSave", $directSave);
    if ($_GET['do'] == "addview2") {
        ##التصنيفات
        $catDataReport = getCategoryChilds();
        $smarty->assign("catDataReport", $catDataReport[1]);

        $payType = $_GET['paytype'];
        if ($payType == '') {
            $payType = "cate_bill";
        }
        $smarty->assign("payType", $payType);

        $smarty->display("billsview/add2.html"); //the template page
    } else {
        $payType = "normal_bill";
        $smarty->assign("payType", $payType);
        $smarty->display("billsview/add.html"); //the template page
    }
}

## add bill
elseif ($_GET['do'] == "print") {
    include_once("../public/authentication.php");
    $billId = filter_input(INPUT_GET, "id");
    $go = filter_input(INPUT_GET, "go");
    $type = filter_input(INPUT_GET, "type");
    $lensShow = filter_input(INPUT_GET, "lens");
    $smarty->assign("go", $go);

    $lens = 0;
    if (isset($lensShow) && !empty($lensShow)) {
        $lens = 1;
    }
    $smarty->assign('lens', $lens);
    ## 0 normal print , 1 insurance print  , 2 both
    getBillDetails($billId);


    ## both print
    if ($type == 2) {
        $smarty->display("billsview/print.html");
        $smarty->display("billsview/printins.html");
    }
    ## insurance print
    elseif ($type == 1) {
        $smarty->display("billsview/printins.html");
    }
    ## normal print
    else {
        $smarty->display("billsview/print.html");
    }
}
## add bill
elseif ($_GET['do'] == "printbuy") {
    include_once("../public/authentication.php");
    $billId = filter_input(INPUT_GET, "id");
    $go = filter_input(INPUT_GET, "go");
    $type = filter_input(INPUT_GET, "type");
    $lensShow = filter_input(INPUT_GET, "lens");
    $smarty->assign("go", $go);

    $lens = 0;
    if (isset($lensShow) && !empty($lensShow)) {
        $lens = 1;
    }
    $smarty->assign('lens', $lens);
    ## 0 normal print , 1 insurance print  , 2 both
    getBillDetailsBuy($billId);


    ## both print
    if ($type == 2) {
        $smarty->display("billsview/print.html");
        $smarty->display("billsview/printins.html");
    }
    ## insurance print
    elseif ($type == 1) {
        $smarty->display("billsview/printins.html");
    }
    ## normal print
    else {
        $smarty->display("billsview/print2.html");
    }
}

## add bill
elseif ($_GET['do'] == "add") {
    include_once("../public/authentication.php");
//    add();
    try {
        $result = add(0);
        $flag = $result[0];
        $billId = $result[1];
        $detailResult = $result[2];
        $new = filter_input(INPUT_POST, "new");
        $payType = filter_input(INPUT_POST, "payType");
//        $transactions->commit();
        ## error in adding bill
        if ($detailResult['flag'] == 99) {
            $note = "رقم الفاتورة موجود من قبل";
            $smarty->assign('errorNote', $note);
            $smarty->display("error.html");
        }
        ## bill added successfully
        else {

            finishProductRequests();
            if ($flag == 1) {
                if ($new == 1) {
                    switch ($payType) {
                        case "normal_bill":
                            $url = 'bills.php?do=empty';
                            break;
                        case "cate_bill":
                            $url = 'bills.php?do=addview2';
                            break;
                        case "ins":
                            $url = 'bills.php?do=addview2&paytype=ins';
                            break;
                        case "cash":
                            $url = 'bills.php?do=addview2&paytype=cash';
                            break;
                        default:
                            $url = 'bills.php?do=empty';
                            break;
                    }
                } else {
                    $url = 'bills.php?do=show';
                }
                $smarty->assign('urldirect', $url);
                $note = "لا يوجد كمية بالمخزن كافية للفاتورة";
                $smarty->assign('msgnote', $note);
                $smarty->display("notes2.html");
            } else {
                if ($new == 1) {
                    switch ($payType) {
                        case "normal_bill":
                            header("location:?do=empty");
                            break;
                        case "cate_bill":
                            header("location:?do=addview2");
                            break;
                        case "ins":
                            header("location:?do=addview2&paytype=ins");
                            break;
                        case "cash":
                            header("location:?do=addview2&paytype=cash");
                            break;
                        default:
                            header("location:?do=empty");
                            break;
                    }
                } else {

                    //# update discountkashf at kashf table to prevent this kashf from discounting again .
                    $kashId = filter_input(INPUT_POST, 'kashf_serial');

                    if (isset($kashId) && $kashId != '') {

                        //# load kashf by id .
                        $rowData = $kashfDAO->load($kashId);

                        $kashf->kashfvalue = $rowData->kashfvalue;
                        $kashf->kashftype = $rowData->kashftype;
                        $kashf->customerid = $rowData->customerid;
                        $kashf->doctorid = $rowData->doctorid;
                        $kashf->kashfdate = $rowData->kashfdate;
                        $kashf->paystatus = $rowData->paystatus;
                        $kashf->entered = $rowData->entered;
                        $kashf->clientname = $rowData->clientname;
                        $kashf->docname = $rowData->docname;
                        $kashf->rondomtxt = $rowData->rondomtxt;
                        $kashf->branchid = $rowData->branchid;
                        $kashf->offline = $rowData->offline;
                        $kashf->uploaddate = $rowData->uploaddate;
                        $kashf->dailyentryid = $rowData->dailyentryid;
                        $kashf->kasfhserial = $rowData->kasfhserial;
                        $kashf->discountkashf = $rowData->discountkashf;
                        $kashf->used = 1;

                        //# update it .
                        $kashfDAO->update($kashf);
                    }

                    $bills = $billsDAO->load($billId);
                    header("location:?do=sucess");
                    if ($bills->insurance == 1) {
                        header("location:bills.php?do=print&id=" . $billId . "&type=2");
                    } else {
                        header("location:bills.php?do=print&id=" . $billId . "&type=0");
                    }
                }
            }
        }
    } catch (Exception $e) {
//        $transactions->rollback();
        header("location:?do=error");
    }
}

## edit bill details
elseif ($_GET['do'] == "edit") {
    include_once("../public/authentication.php");

    $billId = filter_input(INPUT_GET, "id");

    getBillDetails($billId);


    $productsData = getProducts();
    $smarty->assign("productsData", $productsData);

    $clientData = getClientData();
    $smarty->assign('clientData', $clientData);

    ## stores
    $storeData = loadStore();
    $smarty->assign("storeData", $storeData);


    $clientarea = $ClientareaDAO->queryAll();
    $smarty->assign('clientarea', $clientarea);

    ## insurance Companies
    $insuranceCompanies = $insuranceCompaniesDAO->queryByDeleted(0);
    $smarty->assign('insuranceCompanies', $insuranceCompanies);





    ## payment Networks
    $paymentNetworks = $paymentNetworksDAO->queryByDeleted(0);
    $smarty->assign('paymentNetworks', $paymentNetworks);

    $smarty->display("billsview/edit.html"); //the template page
}

## show bill details
elseif ($_GET['do'] == "details") {
    include_once("../public/authentication.php");

    $billId = filter_input(INPUT_GET, "id");
    getBillDetails($billId);
    $smarty->display("billsview/details.html"); //the template page
}

##exportexcel
elseif ($_GET['do'] == "exportexcel") {
    include_once("../public/authentication.php");
    $billId = filter_input(INPUT_GET, "id");
    getBillDetails($billId);

    //# to export excel file
    //# 1- create html file to assign data from databse to this html .
    //# 2- create another html and put the other html file inside it to write the content .
    $outputPath = 'C:/';
    $inputFileType = 'HTML';
    $inputFileName = '../views/default/billsview/exceltoexport.html';
    $outputFileType = 'Excel2007';
    $outputFileName = $outputPath . 'myExcelFile.xlsx';
    $output = $smarty->fetch('billsview/excel.html');
    $file = fopen($inputFileName, "w");
    fwrite($file, $output);
    fclose($file);
    ##read html file as object
    $objPHPExcelReader = PHPExcel_IOFactory::createReader($inputFileType);
    $objPHPExcel = $objPHPExcelReader->load($inputFileName);
    ##style content
    style();
    ##export excel file
    $objPHPExcelWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, $outputFileType);
    $objPHPExcel = $objPHPExcelWriter->save($outputFileName);
    print_r('<meta charset="UTF-8">');
    print_r('<pre style="background-color: darkseagreen;color: white;border-radius: 13px;margin: 14px;font-size: 24px;text-align: center;padding: 5px;">');
    print_r('<p><span>تم تجهيز ملف الاكسل بنجاح</span></p>');
    print_r('<p><span>C:/myExcelFile</span><span>:الملف موجود فى هذا المسار</span></p>');
    print_r('</pre>');
}

##exportexcel
elseif ($_GET['do'] == "exportexcelbuy") {
    include_once("../public/authentication.php");
    $billId = filter_input(INPUT_GET, "id");
    //getBillDetails($billId);
    getBillDetailsBuy($billId);

    //# to export excel file
    //# 1- create html file to assign data from databse to this html .
    //# 2- create another html and put the other html file inside it to write the content .
    $outputPath = 'C:/';
    $inputFileType = 'HTML';
    $inputFileName = '../views/default/billsview/exceltoexport2.html';
    $outputFileType = 'Excel2007';
    $outputFileName = $outputPath . 'myExcelFileBuy.xlsx';
    $output = $smarty->fetch('billsview/excel2.html');
    $file = fopen($inputFileName, "w");
    fwrite($file, $output);
    fclose($file);
    ##read html file as object
    $objPHPExcelReader = PHPExcel_IOFactory::createReader($inputFileType);
    $objPHPExcel = $objPHPExcelReader->load($inputFileName);
    ##style content
    styleBy();
    ##export excel file
    $objPHPExcelWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, $outputFileType);
    $objPHPExcel = $objPHPExcelWriter->save($outputFileName);
    print_r('<meta charset="UTF-8">');
    print_r('<pre style="background-color: darkseagreen;color: white;border-radius: 13px;margin: 14px;font-size: 24px;text-align: center;padding: 5px;">');
    print_r('<p><span>تم تجهيز ملف الاكسل بنجاح</span></p>');
    print_r('<p><span>C:/myExcelFileBuy</span><span>:الملف موجود فى هذا المسار</span></p>');
    print_r('</pre>');
}


## delete bill
elseif ($_GET['do'] == "delete") {
    include_once("../public/authentication.php");

    $billId = filter_input(INPUT_GET, "id");
    $bills = $billsDAO->load($billId);
    $storeId = $userdata->userstoreid;


    $bills->deleted = 1;
    try {
        $billsDAO->update($bills);

        if ($bills->insurance == 1) {
            $billProducts = $billsProductsEX->queryByBillIdAndDeleted($billId, 0);
            foreach ($billProducts as $productR) {
                $productNum = $productR->productno;
                $storeDetail = $storeDetailEX->getProductQuantity($productR->productid, $storeId);
                $productbefore = $storeDetail->productquantity;
                $productafter = $productbefore + $productNum;
                $storeDetail->productquantity = $productafter;
                $storeDetailDAO->update($storeDetail);
                insertStorereportupdate($productR->productid, $storeId, $productNum, $productbefore, $productafter, 0, $billId, "ط­ط°ظپ  ظپط§طھظˆط±ط© ظ…ط¨ظٹط¹ط§طھ", "bills.php", date('Y-m-d'));
            }

            saveDailyEntryDelete($billId);
        }
        header("location:?do=sucess");
    } catch (Exception $e) {
        header("location:?do=error");
    }
}


## show bills
elseif ($do == "show") {
    //here the permssion check
    include_once("../public/authentication.php");

    $usergroup = $UsergroupDAO->load($_SESSION['usergroupid']);
    $smarty->assign("usergroup", $usergroup);

    $clientData = getClientData();
    $smarty->assign('clientData', $clientData);

    //tab1
    $billsNo = $billsEX->getAllBillNoDistinct('');
    $smarty->assign('billsNo', $billsNo);
    //tab2
    $billsNo = $billsEX->getAllBillNoDistinct(' and insurance = 1 and approved = 0 ');
    $smarty->assign('billsNo2', $billsNo);
    //tab3
    $billsNo = $billsEX->getAllBillNoDistinct(' and insurance = 1 and approved = 1 ');
    $smarty->assign('billsNo3', $billsNo);
    //tab4
    $billsNo = $billsEX->getAllBillNoDistinct(' and insurance = 1 and approved = 2 ');
    $smarty->assign('billsNo4', $billsNo);

    #############################################################
    $q1 = 'and date(billdate)  = "' . date('Y-m-d') . ' "';

    $bills = $billsEX->searchBills($q1, '', '', '');


    $totalBalance = 0;
    foreach ($bills as $bill) {
        ## client name
        $client = $clientDAO->load($bill->clientid);
        $bill->clientname = $client->clientname;
        ## user  name
        $user = $userDAO->load($bill->userid);
        $bill->username = $user->username;

        if ($bill->deleted == 0) {
            $totalBalance = $totalBalance + $bill->finalnetbillvalue;
        }
    }
    ## all result  bills total price    but not deleted
    $smarty->assign("totalBalance", $totalBalance);

    $smarty->assign("bills", $bills);
    ########################################################


    $smarty->display("billsview/show.html");
}
##fatma transfer bills show
elseif ($do == "billtransport") {
    //here the permssion check
    include_once("../public/authentication.php");

    #############get new bills to show
    $newBills = $billsEX->getNewBills();



    foreach ($newBills as $bill) {
        ## client name
        $client = $clientDAO->load($bill->clientid);
        $bill->clientname = $client->clientname;

        //# load names of all product cats which belongs to this product .
        $billsProductsReport = $billsProductsDAO->queryByBillid($bill->id);
        $productDetails = $productDAO->load($billsProductsReport[0]->productid);

        $productCatDetails = $productCatDAO->load($productDetails->productCatId);

        $productCat_parent = $productCatDetails->productCatParent;
        $productCat_names = '';
        $productCat_names = $productCatDetails->productCatName . ' - ';


        if ($productCat_parent == 0) {

        } else {
            $productCatId = $productCatDetails->productCatParent;
            $productCat_names = getproductcatdetails($productCatId, $productCat_names);
        }
//        print_r('<br>');
//                print_r($productCat_names);
//        print_r('<br>');
        ## get products
        $productNo = 0;
        $productName = "";
        $products = $billsProductsEX->getproducts($bill->id, 0);

        foreach ($products as $pro) {
            $productNo += $pro->productno;
            $productName .= $pro->deleted . " - " . $productCat_names;
        }


        //$productName = rtrim($productName, " - ");
        $bill->deleted = $productName;
        $bill->billstatus = $productNo;
    }
    $smarty->assign("newBills", $newBills);

    //ط§ظ„ط³ط§ط¦ظ‚ظٹظ†
    $drivers = $userEX->queryWithGroupId(7);
    $smarty->assign("drivers", $drivers);
    //ط§ظ„ظˆط±ط´
    $workshops = $workshopDAO->queryAll();
    $smarty->assign('workshops', $workshops);
    //ط§ظ„ظپط±ظˆط¹
    $branches = $BranchDAO->queryAll();
    $smarty->assign('branches', $branches);
    //ط¹ط§ظ…ظ„ ط§ظ„طھظˆطµظٹظ„
    $deliveryWorkers = $userEX->queryWithGroupId(6);
    $smarty->assign("deliveryWorkers", $deliveryWorkers);
    #########################################طھط­طھ ط§ظ„طھظˆطµظٹظ„
    $orderNo = filter_input(INPUT_POST, "orderNo");
    $datefrom = filter_input(INPUT_POST, "datefrom");
    $dateto = filter_input(INPUT_POST, "dateto");
    $queryString = '';
    if (isset($orderNo) && !empty($orderNo)) {
        $queryString .= ' and orderNo = "' . $orderNo . '"';
    }
    if (isset($datefrom) && !empty($datefrom)) {
        $queryString .= ' and deliverWorkshopDate >= "' . $datefrom . '"';
    }
    if (isset($dateto) && !empty($dateto)) {
        $queryString .= ' and deliverWorkshopDate <= "' . $dateto . '"';
    }

    /* if ($queryString == ' and orderStatus = 0 ') {
      $queryString .= ' and DeliverWorkshopDate = "' . $today . '" ';
      } */

    $deliveredOrders = $toWorkshopOrderEX->queryByOrderStatusEX("0,1", $queryString);
    foreach ($deliveredOrders as $data) {
        ##ط¹ط§ظ…ظ„ ط§ظ„طھظˆطµظٹظ„ "ط§ظ„ط³ط§ط¦ظ‚"
        $driver = $userDAO->load($data->driverId);
        $data->driverId = $driver->employeename;
        ##ط§ظ„ظپط±ط¹
        $branch = $BranchDAO->load($data->branchId);
        $data->branchId = $branch->branchName;
    }
    $smarty->assign('deliveredOrders', $deliveredOrders);
    $queryStringx = '';
    $newBillsReturned = $toWorkshopOrderEX->queryByOrderStatusEX("2", $queryStringx);
    foreach ($newBillsReturned as $bill) {
        ## client name
        $client = $clientDAO->load($bill->clientid);
        $bill->clientname = $client->clientname;
        ## get products
        $productNo = 0;
        $productName = "";
        $products = $billsProductsEX->getproducts($bill->id, 0);
        foreach ($products as $pro) {
            $productNo += $pro->productno;
            $productName .= $pro->deleted . " - ";
        }
        $productName = rtrim($productName, " - ");
        $bill->deleted = $productName;
        $bill->billstatus = $productNo;

        $driver = $userDAO->load($bill->driverIdBack);
        $bill->driverId = $driver->employeename;
        ##ط§ظ„ظپط±ط¹
        $branch = $BranchDAO->load($bill->branchId);
        $bill->branchId = $branch->branchName;
    }
    $smarty->assign("newBillsReturned", $newBillsReturned);

    $finished = $billsEX->queryByBillStatus(4);
    foreach ($finished as $bill) {
        ## client name
        $client = $clientDAO->load($bill->clientid);
        $bill->clientname = $client->clientname;
        ## get products
        $productNo = 0;
        $productName = "";
        $products = $billsProductsEX->getproducts($bill->id, 0);
        foreach ($products as $pro) {
            $productNo += $pro->productno;
            $productName .= $pro->deleted . " - ";
        }
        $productName = rtrim($productName, " - ");
        $bill->deleted = $productName;
        $bill->billstatus = $productNo;
    }
    $smarty->assign("finished", $finished);


    $execusionmsg = $_SESSION["execmsg"];
    if ($execusionmsg == 1) {
        $smarty->assign("msg1", "تم تنفيذ الامر بنجاح");
        $smarty->assign("msg2", "تمت العملية بنجاح ");
        $smarty->assign("showmsg", 1);
        unset($_SESSION["execmsg"]);
    } else if ($execusionmsg == 2) {
        $smarty->assign("msg1", "يوجد خطأ");
        $smarty->assign("msg2", "لقد حدث خطأ اثناء اجراء العملية");
        $smarty->assign("showmsg", 1);
        unset($_SESSION["execmsg"]);
    }



    //my js
    $smarty->assign("workshoptransport", 1);
    $smarty->display("billsview/transfer.html");
}
##fatma transfer bills show
elseif ($do == "toworkshop") {
    //here the permssion check
    include_once("../public/authentication.php");
    //Transaction
    $mytransactions = new Transaction ();

    try {
        $id = toworkshop();
        $mytransactions->commit();
        //$_SESSION["execmsg"] = 1;
        //header("location:?do=billtransport");
        $id = encrypt_url($id);
        header("location:workshoptransport.php?do=printorder&id=" . $id);
    } catch (Exception $e) {

        $mytransactions->rollback();
        $_SESSION["execmsg"] = 2;
        header("location:?do=billtransport");
    }
}
##fatma transfer bills show
elseif ($do == "tobranch") {
    //here the permssion check
    include_once("../public/authentication.php");
    //Transaction
    $mytransactions = new Transaction ();

    try {
        $id = tobranch();
        $mytransactions->commit();
        //$_SESSION["execmsg"] = 1;
        //header("location:?do=billtransport");
        $id = encrypt_url($id);
        header("location:workshoptransport.php?do=printorder&id=" . $id);
    } catch (Exception $e) {

        $mytransactions->rollback();
        $_SESSION["execmsg"] = 2;
        header("location:?do=billtransport");
    }
}

##
elseif ($do == "executeOperation") {
    include_once("../public/authentication.php");
    $operationType = $_POST['operation'];
    $choosedItemArr = $_POST['choosedItem'];
    $storeId = $userdata->userstoreid;
    $allSucces = 1;

    if (count($choosedItemArr) > 0) {
        foreach ($choosedItemArr as $billId) {
            $bills = $billsDAO->load($billId);
            ## 1 delete
            if ($operationType == '1') {
                if ($bills->deleted == 0) {
                    $bills->deleted = 1;
                    try {
                        $billsDAO->update($bills);

                        $billProducts = $billsProductsEX->queryByBillIdAndDeleted($billId, 0);
                        foreach ($billProducts as $productR) {
                            $productNum = $productR->productno;
                            $storeDetail = $storeDetailEX->getProductQuantity($productR->productid, $storeId);
                            if (count($storeDetail) < 1) {
                                $storeDetail->productid = $productR->productid;
                                $storeDetail->storeid = $storeId;
                                $storeDetail->productquantity = 0;
                                $storeDetail->userid = $_SESSION['userid'];
                                $storeDetail->storedetaildate = date('Y-m-d');
                            }
                            $productbefore = $storeDetail->productquantity;
                            $productafter = $productbefore + $productNum;
                            $storeDetail->productquantity = $productafter;
                            $storeDetailDAO->update($storeDetail);
                            insertStorereportupdate($productR->productid, $storeId, $productNum, $productbefore, $productafter, 0, $billId, "ط­ط°ظپ  ظپط§طھظˆط±ط© ظ…ط¨ظٹط¹ط§طھ", "bills.php", date('Y-m-d'));
                        }

                        saveDailyEntryDelete($billId);
                    } catch (Exception $e) {
                        $allSucces = 0;
                    }
                }
            }


            if ($allSucces == 1) {
                header("location:?do=sucess");
            } else {
                header("location:?do=error");
            }
        }
    } else {
        header("location:?do=sucess");
    }
}


## update  bill
elseif ($_GET['do'] == "update") {
    include_once("../public/authentication.php");
//    update();
    try {
        $flag = update();
        $new = filter_input(INPUT_POST, "new");
##        $transactions->commit();

        if ($flag == 1) {
            if ($new == 1) {
                $url = 'bills.php?do=empty';
            } else {
                $url = 'bills.php?do=show';
            }
            $smarty->assign('urldirect', $url);
            $note = "لا يوجد كمية بالمخزن كافية للفاتورة";
            $smarty->assign('msgnote', $note);
            $smarty->display("notes2.html");
        } else {
            if ($new == 1) {
                header("location:?do=empty");
            } else {
                header("location:?do=sucess");
            }
        }
    } catch (Exception $e) {
##        $transactions->rollback();
        header("location:?do=error");
    }
}

################################################################################
#################################################################################
##buy bill
## dispaly  add form
if ($_GET['do'] == "buybill") {
    include_once("../public/authentication.php");

    ## bill no generate

    $branchId = $_SESSION['branchId'];
    $today = date('y');
    $billNo = $branchId;
    $billNo .= $today;

    $bills = $billsBuyEX->searchInBillNo($billNo);
    if (count($bills) > 0) {
        $billNo = $bills->billno + 1;
        if ($branchId == 0) {
            $billNo = '0' . $billNo;
        }
    } else {
        $billNo .= '00001';
    }

    $smarty->assign("billNo", $billNo);

    ## client Data
    $clientData = getSupplierDataBuy();
    $smarty->assign('clientData', $clientData);

    ## Bank Data
    $bankData = getBankDataBuy();
    $smarty->assign('bankData', $bankData);

    ## stores
    $storeData = loadStore();
    $smarty->assign("storeData", $storeData);

    $smarty->assign("now", date('Y-m-d h:i:s'));
    $smarty->assign("today", date('Y-m-d'));

    $productRequestArray = array();
    $productRequestIds = '';
    $isProductRequest = filter_input(INPUT_POST, "isProductRequest");

    if ($isProductRequest == 1) {
        $choosedItemArr = $_POST['choosedItem'];
        if (count($choosedItemArr) > 0) {
            foreach ($choosedItemArr as $productRequestId) {
                $productRequest = $productRequestDAO->load($productRequestId);
                array_push($productRequestArray, $productRequest);
                $productRequestIds .= $productRequestId . '-';


                $product = $productDAO->load($productRequest->productid);
                $productRequest->productName = $product->productName;
                $productCat = $productCatDAO->load($product->productCatId);
                $productRequest->productCatName = $productCat->productCatName;


                $parentId = $product->productCatId;
                $pathArr = getProductPath_recursive($parentId, $categories, 0);
                $productRequest->productName = $product->productName . '/' . $pathArr;
            }
        }

        $smarty->assign("productRequestIds", $productRequestIds);
        $smarty->assign("billProducts", $productRequestArray);
        $smarty->assign("billProductsCount", count($productRequestArray));
    }

    $smarty->assign('isProductRequest', $isProductRequest);

    ##التصنيفات
    $catDataReport = getCategoryChilds();
    $smarty->assign("catDataReport", $catDataReport[1]);
    $smarty->display("billsview/buybill/add2.html"); //the template page
}

## add Buy bill
elseif ($_GET['do'] == "addbuybill") {
    include_once("../public/authentication.php");
//    addBuy();
    try {
        $result = addBuy();
        $flag = $result[0];
        $billId = $result[1];
        $detailResult = $result[2];
        $new = filter_input(INPUT_POST, "new");
////        $transactions->commit();
        ## error in adding bill
        if ($detailResult['flag'] == 99) {
            $note = "رقم الفاتورة موجود من قبل";
            $smarty->assign('errorNote', $note);
            $smarty->display("error.html");
        }
        ## bill added successfully
        else {
            finishProductRequests();
            if ($flag == 1) {
                if ($new == 1) {
                    $url = 'bills.php?do=buybill';
                } else {
                    $url = 'bills.php?do=showbuybill';
                }
                $smarty->assign('urldirect', $url);
                $note = "لا يوجد كمية بالمخزن كافية للفاتورة";
                $smarty->assign('msgnote', $note);
                $smarty->display("notes2.html");
            } else {
                header("location:?do=showbuybill");
//                if ($new == 1) {
//                    header("location:?do=buybill");
//                } else {
//                    $bills = $billsBuyDAO->load($billId);
//                    header("location:?do=sucess");
//                    if ($bills->insurance == 1) {
//                        header("location:bills.php?do=printbuy&id=" . $billId . "&type=2");
//                    } else {
//                        header("location:bills.php?do=printbuy&id=" . $billId . "&type=0");
//                    }
//                }
            }
        }
    } catch (Exception $e) {
////        $transactions->rollback();
        header("location:?do=error");
    }
}

## show bills
elseif ($do == "showbuybill") {
    //here the permssion check
    include_once("../public/authentication.php");

    $usergroup = $UsergroupDAO->load($_SESSION['usergroupid']);
    $smarty->assign("usergroup", $usergroup);

    $clientData = getSupplierDataBuy();
    $smarty->assign('clientData', $clientData);

    //tab1
    $billsNo = $billsBuyEX->getAllBillNoDistinct('');
    $smarty->assign('billsNo', $billsNo);

    #############################################################
    $q1 = 'and date(billdate) = "' . date('Y-m-d') . ' "';

    $bills = $billsBuyEX->searchBills($q1, '', '', '');


    $totalBalance = 0;
    foreach ($bills as $bill) {
        ## client name
        $client = $supplierDAO->load($bill->clientid);
        $bill->clientname = $client->suppliername;
        ## user  name
        $user = $userDAO->load($bill->userid);
        $bill->username = $user->username;

        if ($bill->deleted == 0) {
            $totalBalance = $totalBalance + $bill->finalnetbillvalue;
        }
    }
    ## all result  bills total price    but not deleted
    $smarty->assign("totalBalance", $totalBalance);

    $smarty->assign("bills", $bills);
    ########################################################


    $smarty->display("billsview/buybill/show.html");
}
## delete bill
elseif ($_GET['do'] == "deletebuy") {
    include_once("../public/authentication.php");

    $billId = filter_input(INPUT_GET, "id");

    $bills = $billsBuyDAO->load($billId);
    $storeId = $userdata->userstoreid;


    $bills->deleted = 1;
    try {
        $billsBuyDAO->update($bills);

        $billProducts = $billsProductsBuyEX->queryByBillIdAndDeleted($billId, 0);
        foreach ($billProducts as $productR) {
            $productNum = $productR->productno;
            $storeDetail = $storeDetailEX->getProductQuantity($productR->productid, $storeId);
            $productbefore = $storeDetail->productquantity;
            $productafter = $productbefore + $productNum;
            $storeDetail->productquantity = $productafter;
            $storeDetailDAO->update($storeDetail);
            insertStorereportupdate($productR->productid, $storeId, $productNum, $productbefore, $productafter, 0, $billId, "ط­ط°ظپ  ظپط§طھظˆط±ط© ط´ط±ط§ط،", "bills.php?do=buybill", date('Y-m-d'));
        }

        saveDailyEntryDeleteBuy($billId);
        header("location:?do=showbuybill");
    } catch (Exception $e) {
        header("location:?do=error");
    }
}

## show bill details
elseif ($_GET['do'] == "detailsbuy") {
    include_once("../public/authentication.php");

    $billId = filter_input(INPUT_GET, "id");
    getBillDetailsBuy($billId);
    $smarty->display("billsview/buybill/details.html"); //the template page
}

## edit bill details
elseif ($_GET['do'] == "editbuy") {
    include_once("../public/authentication.php");

    $billId = filter_input(INPUT_GET, "id");

    getBillDetailsBuy($billId);

    $productsData = getProducts();
    $smarty->assign("productsData", $productsData);

    $clientData = getSupplierDataBuy();
    $smarty->assign('clientData', $clientData);

    ## Bank Data
    $bankData = getBankDataBuy();
    $smarty->assign('bankData', $bankData);

    ## stores
    $storeData = loadStore();
    $smarty->assign("storeData", $storeData);


    $smarty->display("billsview/buybill/edit.html"); //the template page
}

## update  bill
elseif ($_GET['do'] == "updatebuy") {
    include_once("../public/authentication.php");
//    updateBuy();
    try {
        $flag = updateBuy();
        $new = filter_input(INPUT_POST, "new");
##        $transactions->commit();

        if ($flag == 1) {
            if ($new == 1) {
                $url = 'bills.php?do=buybill';
            } else {
                $url = 'bills.php?do=showbuybill';
            }
            $smarty->assign('urldirect', $url);
            $note = "لا يوجد كمية بالمخزن كافية للفاتورة";
            $smarty->assign('msgnote', $note);
            $smarty->display("notes2.html");
        } else {
            if ($new == 1) {
                header("location:?do=buybill");
            } else {
                header("location:?do=showbuybill");
            }
        }
    } catch (Exception $e) {
##        $transactions->rollback();
        header("location:?do=error");
    }
}
################################################################################
#################################################################################
#
##
elseif ($_GET['do'] == "sucess") {
    $smarty->display("succes.html");
}

##
elseif ($_GET['do'] == "error") {
    $smarty->display("error.html");
}

$smarty->assign("customClientDeptChangeReport", 1);

$smarty->assign("bills", 1);

if ($do != "print" && $do != "exportexcel") {
    $smarty->display("footer.html");
}

function saveDailyEntryDelete($billId) {
    global $dailyEntry;      ## Dailyentry
    global $accountsTreeDAO;
    global $client;
    global $clientDAO;
    global $mySave;
    global $mySaveDAO;
    global $insuranceCompaniesDAO;
    global $paymentNetworksDAO;
    global $billsDAO;
    $total = 0;

    $mytransactions = new Transaction();
    $bills = $billsDAO->load($billId);

    $dailyEntryDebtorArray = array();
    $dailyEntryCreditorArray = array();

    $dailyEntry->dDateTime = date('Y-m-d h:i:s');
    $dailyEntry->entryComment = 'حذف  فاتورة ';
    $dailyEntry->fromFlag = 2;
    $dailyEntry->reverseofid = -9;
#########################################################################################
    if ($bills->waitvalue != 0) {
        $clientId = $bills->clientid;
        $client = $clientDAO->load($clientId);

        if ($client->treeId > 0) {
            $treeId1 = $client->treeId;
        } else {
            $treeId1 = addTreeElement($client->clientname, 23, 3, 0, 1, '', 0, 0);
        }

        $dailyEntryCreditor1->value = $bills->waitvalue;
        $dailyEntryCreditor1->accountstreeid = $treeId1;
        array_push($dailyEntryCreditorArray, $dailyEntryCreditor1);
        $total = $total + $bills->waitvalue;
    }
#########################################################################################
    if ($bills->insurance == 1) {
        $companyId = $bills->insurancecompanyid;
        $insuranceCompanies = $insuranceCompaniesDAO->load($companyId);

        $insValue = $bills->companycarry;
        $insDiscount = $insValue * ($bills->insdiscountpercent) / 100;
        $insRest = $insValue - $insDiscount;

        ############# insurance
        $accountsTree33 = $accountsTreeDAO->queryByName($insuranceCompanies->name);
        if (count($accountsTree33) > 0) {
            $treeId3 = $accountsTree33[0]->id;
        } else {
            $treeId3 = addTreeElement($insuranceCompanies->name, 40, 3, 0, 1, '', 0, 0);
        }

        $dailyEntryCreditor3->value = $insRest;
        $dailyEntryCreditor3->accountstreeid = $treeId3;
        array_push($dailyEntryCreditorArray, $dailyEntryCreditor3);

        $total = $total + $insRest;
    }
#########################################################################################
    $saveValue = 0;
    if ($bills->cash == 1) {
        $saveValue = $saveValue + $bills->cashvalue;
    }

    if ($bills->card == 1) {
        $cardValue = $bills->cardvalue;

        if ($bills->paymentnetworkid == 4) {
            $cardDiscount = $cardValue * (0.7) / 100;
        } else {
            $cardDiscount = $cardValue * ($bills->netdiscountpercent) / 100;
        }

        $cardRest = $cardValue - $cardDiscount;
        $saveValue = $saveValue + $cardRest;
    }

#########################################################################################
    if ($bills->cash == 1 || $bills->card == 1) {
        $saveId = $_SESSION['saveid'];
        $mySave = $mySaveDAO->load($saveId);
        if ($mySave->treeId > 0) {
            $treeId2 = $mySave->treeId;
        } else {
            $treeId2 = addTreeElement($mySave->savename, 20, 3, 0, 1, '', 0, 0);
        }

        $dailyEntryCreditor2->value = $saveValue;
        $dailyEntryCreditor2->accountstreeid = $treeId2;
        array_push($dailyEntryCreditorArray, $dailyEntryCreditor2);
        $total = $total + $saveValue;
    }
#########################################################################################
#########################################################################################
    $dailyEntryDebtor->value = $total;
    $dailyEntryDebtor->accountstreeid = 16;
    array_push($dailyEntryDebtorArray, $dailyEntryDebtor);

    try {
        $data = insertEntery($dailyEntry, $dailyEntryDebtorArray, $dailyEntryCreditorArray, 1);
        $dailyEntryId = $data[1];

        $bills->dailyentryid = $dailyEntryId;
        $billsDAO->update($bills);
        $mytransactions->commit();

        $message = 'تمت العملية بنجاح';
    } catch (Exception $ex) {
        $mytransactions->rollback();
        $message = 'عفوا لقد حدث خطأ';
    }
}

function saveDailyEntryDeleteBuy($billId) {
    global $dailyEntry;      ## Dailyentry
    global $accountsTreeDAO;
    global $supplier;
    global $supplierDAO;
    global $mySave;
    global $mySaveDAO;
    global $billsBuyDAO;
    global $accountDAO;
    global $bankDAO;
    $total = 0;

    $mytransactions = new Transaction();
    $billsBuy = $billsBuyDAO->load($billId);

    $discountAsMoney = 0;
    if ($billsBuy->discounttype == 1) {
        //direct
        $discountAsMoney = $billsBuy->discountvalue;
    } else {
        //percentage
        $discountAsMoney = $billsBuy->netbillvalue * $billsBuy->discountvalue / 100;
    }

    $dailyEntryDebtorArray = array();
    $dailyEntryCreditorArray = array();

    $dailyEntry->dDateTime = date('Y-m-d h:i:s');
    $dailyEntry->entryComment = 'حذف  فاتورة شراء ';
    $dailyEntry->fromFlag = 2;
    $dailyEntry->reverseofid = -9;
#########################################################################################
    if ($billsBuy->waitvalue != 0) {
        $client = $supplierDAO->load($billsBuy->clientid);

        if ($client->treeId > 0) {
            $treeId1 = $client->treeId;
        } else {
            $treeId1 = addTreeElement($client->suppliername, 10, 1, 0, 1, '', 0, 0); //add supplier in the tree
        }

        $dailyEntryDebtor1->value = $billsBuy->waitvalue;
        $dailyEntryDebtor1->accountstreeid = $treeId1;
        array_push($dailyEntryDebtorArray, $dailyEntryDebtor1);
        $total = $total + $billsBuy->waitvalue;
    }
#########################################################################################
    if ($billsBuy->cash == 1) {
        $saveId = $_SESSION['saveid'];
        $mySave = $mySaveDAO->load($saveId);
        if ($mySave->treeId > 0) {
            $treeId2 = $mySave->treeId;
        } else {
            $treeId2 = addTreeElement($mySave->savename, 20, 3, 0, 1, '', 0, 0);
        }
        $dailyEntryDebtor2->value = $billsBuy->cashvalue;
        $dailyEntryDebtor2->accountstreeid = $treeId2;
        array_push($dailyEntryDebtorArray, $dailyEntryDebtor2);
        $total = $total + $billsBuy->cashvalue;
    }
#########################################################################################
    if ($billsBuy->card == 1) {
        $bankid = $billsBuy->paymentnetworkid;
        $accountid = $billsBuy->netdiscountpercent;
        $accountData = $accountDAO->load($accountid);
        $cardValue = $billsBuy->cardvalue;

        ## bank account
        if ($accountData->treeId > 0) {
            $treeId4 = $accountData->treeId;
        } else {
            $bankid = $billsBuy->paymentnetworkid;
            //we dont add bank to tree now
            $bankData = $bankDAO->load($bankid);
//            $bankTreeData = $accountsTreeDAO->queryByName($bankData->bankname);
//            if (count($bankTreeData) == 0) {
//                $bankTreeId = addTreeElement($bankData->bankname, 21, 3, 0, 0, '', 0, 0);
//            } else {
//                $bankTreeId = $bankTreeData[0]->id;
//            }
//            $treeId4 = addTreeElement($accountData->accountname, $bankTreeId, 3, 0, 1, '', 0, 0);
            $treeId4 = addTreeElement("$accountData->accountname / $bankData->bankname", 38, 3, 0, 1, '', 0, 0);
        }

        $dailyEntryDebtor4->value = $cardValue;
        $dailyEntryDebtor4->accountstreeid = $treeId4;
        array_push($dailyEntryDebtorArray, $dailyEntryDebtor4);
        $total = $total + $cardValue;
    }
#########################################################################################
    if ($discountAsMoney != 0) {

        $dailyEntryDebtor55->value = $discountAsMoney;
        $dailyEntryDebtor55->accountstreeid = 52;
        array_push($dailyEntryDebtorArray, $dailyEntryDebtor55);
        $total = $total + $discountAsMoney;
    }
#########################################################################################
    $dailyEntryCreditor->value = $total;
    $dailyEntryCreditor->accountstreeid = 12;
    array_push($dailyEntryCreditorArray, $dailyEntryCreditor);

    try {
        $data = insertEntery($dailyEntry, $dailyEntryDebtorArray, $dailyEntryCreditorArray, 1);
        $dailyEntryId = $data[1];

        $billsBuy->dailyentryid = $dailyEntryId;
        $billsBuyDAO->update($billsBuy);
        $mytransactions->commit();

        $message = 'تمت العملية بنجاح';
    } catch (Exception $ex) {
        $mytransactions->rollback();
        $message = 'عفوا لقد حدث خطأ';
    }
}

###################### fatma ##################################################################################################

function toworkshop() {

    //Toworkshoporder
    global $toWorkshopOrder;
    global $toWorkshopOrderDAO;
    //Toworkshoporderbill
    global $toWorkshopOrderBill;
    global $toWorkshopOrderBillDAO;
    //bill
    global $bills;
    global $billsDAO;
    global $billsEX;

    global $billsProductsEX;

    $choosenBills = $_POST["choosedItem"];
    $driverId = filter_input(INPUT_POST, "driver");
    $workshopId = filter_input(INPUT_POST, "workshop");
    $deliveryDate = filter_input(INPUT_POST, "deliveryDate");
    $workshoptimeout = filter_input(INPUT_POST, "workshoptimeout");
    $drivertimeout = filter_input(INPUT_POST, "drivertimeout");
    $unique;
    do {
        $unique = uniqid("0");
    } while (isUnique($unique));


    $productNo = 0;
    foreach ($choosenBills as $pro) {
        ## get products
        $products = $billsProductsEX->getproducts($pro, 0);
        foreach ($products as $pro) {
            $productNo += $pro->productno;
        }
    }

    $toWorkshopOrder->driverIdBack = 0;
    $toWorkshopOrder->deliverIdBack = 0;
    $toWorkshopOrder->orderNo = $unique;
    $toWorkshopOrder->driverId = $driverId; //السائق
    $toWorkshopOrder->workshopId = $workshopId; //الورشة
    $toWorkshopOrder->productNo = $productNo;
    $toWorkshopOrder->deliverWorkshopDate = $deliveryDate; //تاريخ التسليم
    $toWorkshopOrder->deliverId = 0;
    //$toWorkshopOrder->deliverId = $_SESSION['userid']; //المسلم
    $toWorkshopOrder->branchId = $_SESSION['branchId']; //الفرع
    $toWorkshopOrder->orderStatus = 0; //it means new
    $toWorkshopOrder->sysDate = date("Y-m-d H:i:s");
    $toWorkshopOrder->del = 0;
    $toWorkshopOrder->userid = $_SESSION['userid'];

    $toWorkshopOrder->drivertimeout = $drivertimeout;
    $toWorkshopOrder->workshoptimeout = $workshoptimeout;
    $toWorkshopOrder->workshopdelay = 0;
    $toWorkshopOrder->driverdelay = 0;
    $toWorkshopOrder->drivertimeoutback = $drivertimeout;
    $toWorkshopOrder->driverdelayback = 0;

    $toworkshoporderid = $toWorkshopOrderDAO->insert($toWorkshopOrder);

    foreach ($choosenBills as $pro) {
        $toWorkshopOrderBill->billid = $pro;
        $toWorkshopOrderBill->toworkshoporderid = $toworkshoporderid;
        $toWorkshopOrderBillDAO->insert($toWorkshopOrderBill);

        $billsEX->updateBillStatus(1, $pro); //status1 means at work shop
    }
    return $toworkshoporderid;
}

function isUnique($unique) {
    global $toWorkshopOrderDAO;
    $data = $toWorkshopOrderDAO->queryByOrderNo($unique);
    $flag = TRUE;
    if (count(data) > 0) {
        $flag = FALSE;
    }
    return $flag;
}

function tobranch() {

    //Toworkshoporder
    global $toWorkshopOrder;
    global $toWorkshopOrderDAO;
    //Toworkshoporderbill
    global $toWorkshopOrderBill;
    global $toWorkshopOrderBillDAO;
    //bill
    global $bills;
    global $billsDAO;
    global $billsEX;

    global $billsProductsEX;

    $choosenOrders = $_POST["choosedItem_4"];
    $recieveBranchDate = $_POST["recievebranchdate"];
    $driverId = filter_input(INPUT_POST, "driver_4");
    $ids = '';
    foreach ($choosenOrders as $pro) {
        $ids .= $pro . ',';
        $toWorkshopOrder = $toWorkshopOrderDAO->load($pro);
        $toWorkshopOrder->orderStatus = 3;
        $toWorkshopOrder->recieveBranchDate = $recieveBranchDate; //date("Y-m-d H:i:s");
        $toWorkshopOrder->deliverIdBack = $driverId;
        $hours = dateTimeDiff($toWorkshopOrder->recieveBranchDate, $toWorkshopOrder->deliverBranchDate);
        if ($hours > $toWorkshopOrder->drivertimeoutback) {
            $toWorkshopOrder->driverdelayback = $hours;
        } else {
            $toWorkshopOrder->driverdelayback = 0;
        }
        $toWorkshopOrderDAO->update($toWorkshopOrder);
        $toWorkshopOrderBill = $toWorkshopOrderBillDAO->queryByToworkshoporderid($pro);
        foreach ($toWorkshopOrderBill as $aBill) {
            $billsEX->updateBillStatus(4, $aBill->billid); //status2 means at work shop recive
        }
    }
    $ids = rtrim($ids, ",");
    return $ids;
}

function encrypt_url($string) {
    $key = "FATma_979805"; //key to encrypt and decrypts.
    $result = '';
    $test = "";
    for ($i = 0; $i < strlen($string); $i++) {
        $char = substr($string, $i, 1);
        $keychar = substr($key, ($i % strlen($key)) - 1, 1);
        $char = chr(ord($char) + ord($keychar));

        $test[$char] = ord($char) + ord($keychar);
        $result .= $char;
    }

    return urlencode(base64_encode($result));
}

function decrypt_url($string) {
    $key = "FATma_979805"; //key to encrypt and decrypts.
    $result = '';
    $string = base64_decode(urldecode($string));
    for ($i = 0; $i < strlen($string); $i++) {
        $char = substr($string, $i, 1);
        $keychar = substr($key, ($i % strlen($key)) - 1, 1);
        $char = chr(ord($char) - ord($keychar));
        $result .= $char;
    }
    return $result;
}

function dateTimeDiff($dateTime1, $dateTime2) {
    $dateTime1 = new DateTime($dateTime1);
    $dateTime2 = new DateTime($dateTime2);

    $diff = $dateTime2->diff($dateTime1);
    $hours = $diff->h;
    $hours = $hours + ($diff->days * 24);
    return $hours;
}

##############################################################################################################################

function finishProductRequests() {
    global $productRequest;
    global $productRequestDAO;

    $productRequestIds = filter_input(INPUT_POST, "productRequestIds");
    $productRequestIdsArray = explode('-', $productRequestIds);
    foreach ($productRequestIdsArray as $reqId) {
        if ($reqId && !empty($reqId)) {
            $productRequest = $productRequestDAO->load($reqId);
            $productRequest->status = 3;
            $productRequestDAO->update($productRequest);
        }
    }
}

function getCategoryChilds($parentid = 0) {
    global $productCatExt;
    global $productCatDAO;
    //load cat
    $parentobject = $productCatDAO->load($parentid);
    $childsArray = $productCatExt->getChilds($parentid);

    return array($parentobject, $childsArray);
}

##################################################################################
##
##style excel functions

function style() {
    global $objPHPExcel;
    ## Auto size columns for active sheet
    foreach (range('A', 'Z') as $columnID) {
        $objPHPExcel->getActiveSheet()->getColumnDimension($columnID)->setAutoSize(true);
    }

    ## all cells  text alignment left
    $style = array('alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT));
    $objPHPExcel->getDefaultStyle()->applyFromArray($style);

//    ##  wrap text in colum
//    $objPHPExcel->getActiveSheet()->getStyle('B1:B' . $objPHPExcel->getActiveSheet()->getHighestRow())->getAlignment()->setWrapText(true);
//    ## set width to specific column
//    $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(40);
//    $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(40);





    $colArray = array('L1:L5', 'F1:F5', 'A6:L7', 'A8:A9', 'L8:L9', 'A10:A10', 'B10:B10', 'K10:K10', 'L10:L10', 'F10:F11', 'L12:L12', 'H13:L13', 'A6:F6');
    $colArrayData = array('B8:K9', 'E1:E5', 'K1:K5', 'A11:B11', 'K11:L11', 'G10:G11', 'A12:K12', 'C10:E11', 'H10:J11', 'A1:D5', 'G1:J5');
    //--get totals in the bottom coords
    $column = 'L';
    $sheet = $objPHPExcel->getSheet(0);
    $lastRow = $sheet->getHighestRow();
    for ($row = 13; $row <= $lastRow; $row++) {
        $cell = $sheet->getCell($column . $row);
        $val = $cell->getValue();
        //  Do what you want with the cell
        if ($val == 'إجمالي المشتريات') {
            $coords = $column . $row . ':' . $column . ($row + 6);
            $coordsData = 'K' . $row . ':K' . ($row + 6);
            $coordsData2 = 'H14:L' . ($row - 1);
            array_push($colArray, $coords);
            array_push($colArrayData, $coordsData);
            array_push($colArrayData, $coordsData2);
        }
    }
    //--
    ## backdround color   and font style
    $backColorArray = array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => '666666'),));
    ## backdround color   and font style
    $backColorArrayData = array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'c7c7c7'),));
    ## make border around group of cells and style it
    $borderArray = array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('rgb' => 'ffffff'),)));

    ## font color
    $colorArray = array('font' => array('bold' => true, 'color' => array('rgb' => 'ffffff')));
    $borderSeparateLeftArray = array('borders' => array('left' => array('style' => PHPExcel_Style_Border::BORDER_THICK, 'color' => array('rgb' => 'ff3939'),)));
    $borderSeparateRightArray = array('borders' => array('right' => array('style' => PHPExcel_Style_Border::BORDER_THICK, 'color' => array('rgb' => 'ff3939'),)));
    $borderSeparateTopArray = array('borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('rgb' => 'ff3939'),)));
    $borderSeparateBottomArray = array('borders' => array('bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('rgb' => 'ff3939'),)));
    ##merge cells
    //-Right Eye
    $objPHPExcel->getActiveSheet()->getStyle('A6:F6')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $objPHPExcel->getActiveSheet()->getStyle('A6:F6')->applyFromArray($borderArray);
    $objPHPExcel->getActiveSheet()->mergeCells('A6:F6');
    $objPHPExcel->getActiveSheet()->setCellValue('A6', 'Right Eye');
    //-Right Eye
    $objPHPExcel->getActiveSheet()->getStyle('G6:L6')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $objPHPExcel->getActiveSheet()->getStyle('G6:L6')->applyFromArray($borderArray);
    $objPHPExcel->getActiveSheet()->mergeCells('G6:L6');
    $objPHPExcel->getActiveSheet()->setCellValue('G6', 'Left Eye');
    //-notes
    $cell = $sheet->getCell('K12');
    $val = $cell->getValue();
    $objPHPExcel->getActiveSheet()->getStyle('A12:K12')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $objPHPExcel->getActiveSheet()->getStyle('A12:K12')->applyFromArray($borderArray);
    $objPHPExcel->getActiveSheet()->mergeCells('A12:K12');
    $objPHPExcel->getActiveSheet()->setCellValue('A12', $val);

    foreach ($colArray as $col) {
        $objPHPExcel->getActiveSheet()->getStyle($col)->applyFromArray($backColorArray);
        $objPHPExcel->getActiveSheet()->getStyle($col)->applyFromArray($borderArray);
        $objPHPExcel->getActiveSheet()->getStyle($col)->applyFromArray($colorArray);
    }
    foreach ($colArrayData as $col) {
        $objPHPExcel->getActiveSheet()->getStyle($col)->applyFromArray($backColorArrayData);
        $objPHPExcel->getActiveSheet()->getStyle($col)->applyFromArray($borderArray);
        //$objPHPExcel->getActiveSheet()->getStyle($col)->applyFromArray($colorArray);
    }
    ##separate eyes
//    $objPHPExcel->getActiveSheet()->getStyle('G8:G9')->applyFromArray($borderSeparateLeftArray);
//    $objPHPExcel->getActiveSheet()->getStyle('F8:F9')->applyFromArray($borderSeparateRightArray);
//    $objPHPExcel->getActiveSheet()->getStyle('B8:B9')->applyFromArray($borderSeparateLeftArray);
//    $objPHPExcel->getActiveSheet()->getStyle('K8:K9')->applyFromArray($borderSeparateRightArray);
//    $objPHPExcel->getActiveSheet()->getStyle('B8:K8')->applyFromArray($borderSeparateTopArray);
//    $objPHPExcel->getActiveSheet()->getStyle('B9:K9')->applyFromArray($borderSeparateBottomArray);
    unset($backColorArray);
    unset($backColorArrayData);
    unset($colorArray);
    unset($colArrayData);
    unset($borderArray);
}

function styleBy() {
    global $objPHPExcel;
    ## Auto size columns for active sheet
    foreach (range('A', 'Z') as $columnID) {
        $objPHPExcel->getActiveSheet()->getColumnDimension($columnID)->setAutoSize(true);
    }

    ## all cells  text alignment left
    $style = array('alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT));
    $objPHPExcel->getDefaultStyle()->applyFromArray($style);

//    ##  wrap text in colum
//    $objPHPExcel->getActiveSheet()->getStyle('B1:B' . $objPHPExcel->getActiveSheet()->getHighestRow())->getAlignment()->setWrapText(true);
//    ## set width to specific column
//    $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(40);
//    $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(40);





    $colArray = array('L1:L5', 'F1:F3', 'L6:L12', 'G4:K4', 'L12:L12');
    $colArrayData = array('G8:K9', 'E1:E3', 'K1:K3', 'K10:K10', 'K11:K11', 'G13:K12', 'G5:K7', 'G10:G11', 'G12:K12', 'H10:J11', 'A1:D3', 'G1:J3');
    //--get totals in the bottom coords
    $column = 'L';
    $sheet = $objPHPExcel->getSheet(0);
    $lastRow = $sheet->getHighestRow();
    for ($row = 13; $row <= $lastRow; $row++) {
        $cell = $sheet->getCell($column . $row);
        $val = $cell->getValue();
        //  Do what you want with the cell
        if ($val == 'إجمالي المشتريات') {
            $coords = $column . $row . ':' . $column . ($row + 6);
            $coordsData = 'K' . $row . ':K' . ($row + 6);
            $coordsData2 = 'H14:L' . ($row - 1);
            array_push($colArray, $coords);
            array_push($colArrayData, $coordsData);
            array_push($colArrayData, $coordsData2);
        }
    }
    //--
    ## backdround color   and font style
    $backColorArray = array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => '666666'),));
    ## backdround color   and font style
    $backColorArrayData = array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'c7c7c7'),));
    ## make border around group of cells and style it
    $borderArray = array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('rgb' => 'ffffff'),)));

    ## font color
    $colorArray = array('font' => array('bold' => true, 'color' => array('rgb' => 'ffffff')));
    $borderSeparateLeftArray = array('borders' => array('left' => array('style' => PHPExcel_Style_Border::BORDER_THICK, 'color' => array('rgb' => 'ff3939'),)));
    $borderSeparateRightArray = array('borders' => array('right' => array('style' => PHPExcel_Style_Border::BORDER_THICK, 'color' => array('rgb' => 'ff3939'),)));
    $borderSeparateTopArray = array('borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('rgb' => 'ff3939'),)));
    $borderSeparateBottomArray = array('borders' => array('bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('rgb' => 'ff3939'),)));
    ##merge cells
    //-Right Eye
//    $objPHPExcel->getActiveSheet()->getStyle('A6:F6')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//    $objPHPExcel->getActiveSheet()->getStyle('A6:F6')->applyFromArray($borderArray);
//    $objPHPExcel->getActiveSheet()->mergeCells('A6:F6');
    //$objPHPExcel->getActiveSheet()->setCellValue('A6', 'Right Eye');
    //-Right Eye
//    $objPHPExcel->getActiveSheet()->getStyle('G6:L6')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//    $objPHPExcel->getActiveSheet()->getStyle('G6:L6')->applyFromArray($borderArray);
//    $objPHPExcel->getActiveSheet()->mergeCells('G6:L6');
    //$objPHPExcel->getActiveSheet()->setCellValue('G6', 'Left Eye');
    //-notes
    $cell = $sheet->getCell('K12');
    $val = $cell->getValue();
//    $objPHPExcel->getActiveSheet()->getStyle('A12:K12')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//    $objPHPExcel->getActiveSheet()->getStyle('A12:K12')->applyFromArray($borderArray);
//    $objPHPExcel->getActiveSheet()->mergeCells('A12:K12');
//    $objPHPExcel->getActiveSheet()->setCellValue('A12', $val);

    foreach ($colArray as $col) {
        $objPHPExcel->getActiveSheet()->getStyle($col)->applyFromArray($backColorArray);
        $objPHPExcel->getActiveSheet()->getStyle($col)->applyFromArray($borderArray);
        $objPHPExcel->getActiveSheet()->getStyle($col)->applyFromArray($colorArray);
    }
    foreach ($colArrayData as $col) {
        $objPHPExcel->getActiveSheet()->getStyle($col)->applyFromArray($backColorArrayData);
        $objPHPExcel->getActiveSheet()->getStyle($col)->applyFromArray($borderArray);
        //$objPHPExcel->getActiveSheet()->getStyle($col)->applyFromArray($colorArray);
    }
    ##separate eyes
//    $objPHPExcel->getActiveSheet()->getStyle('G8:G9')->applyFromArray($borderSeparateLeftArray);
//    $objPHPExcel->getActiveSheet()->getStyle('F8:F9')->applyFromArray($borderSeparateRightArray);
//    $objPHPExcel->getActiveSheet()->getStyle('B8:B9')->applyFromArray($borderSeparateLeftArray);
//    $objPHPExcel->getActiveSheet()->getStyle('K8:K9')->applyFromArray($borderSeparateRightArray);
//    $objPHPExcel->getActiveSheet()->getStyle('B8:K8')->applyFromArray($borderSeparateTopArray);
//    $objPHPExcel->getActiveSheet()->getStyle('B9:K9')->applyFromArray($borderSeparateBottomArray);
    unset($backColorArray);
    unset($backColorArrayData);
    unset($colorArray);
    unset($colArrayData);
    unset($borderArray);
}

function getproductcatdetails($productCatId, $productCat_names) {

    global $productCatDAO;

    $productCatDetails = $productCatDAO->load($productCatId);


    $productCat_parent = $productCatDetails->productCatParent;
    $productCat_names = trim($productCat_names, ' - ');
    $productCat_names .= ' - ' . $productCatDetails->productCatName;

    if ($productCat_parent != 0) {

        $productCatId = $productCatDetails->productCatParent;
        $productCat_names = getproductcatdetails($productCatId, $productCat_names);
    }


    return $productCat_names;
}

?>