<?php

/**
 * This class is responsible for creating organization.
 *
 * @copyright Trusto Tech 2015-2016
 * @author  Taha Sayyad <info@trustotech.com>
 * @package AccountingBeta\libs\Classes
 *
 * @version 1.0.0
 */
class Organization {

    /**
     * @var array Holds an array of data
     */
    protected $info = array();

    /**
     * @var bool Will be used to check whether the organization is added or not. TRUE when the organization is
     *      successfully created. Default value is FALSE
     */
    private $orgAdded = false;

    /**
     * @var obj Holds the database adapter object
     */
    private $db;

    /**
     * The constructor will create an instant of the chosen database adapter (MySQL, MySQLi, etc)
     * and saves it in a private variable $db as an object to be used throughout the class.
     *
     * @author  Taha Sayyad <info@trustotech.com>
     * @uses DBConnectorFactory::loadDriver() to load the database adapter
     */
    public function __construct() {
        $this->db = DBConnectorFactory::loadDriver();
        $this->controller_permission = Session::getPermissionsObject();

    }

    /**
     * The first thing to do is to set organization data using this method.<br .>
     * Before setting the organization data, the method will remove all unnecessary data from the array.
     * Next it will trim every single value.
     *
     * @author  Taha Sayyad <info@trustotech.com>
     * @param array $data Array of organization data
     */
    public function setdata(array $data) {
        $this->info = $data;
        return $this;
    }

    /**
     * Creates a new routing and updates the private var $routingAdded by setting its value to TRUE
     * which indicates a successful routing creation.
     *
     * @author  Taha Sayyad <info@trustotech.com>
     */
    public function create() {
        $this->db->query('START TRANSACTION');
        $org_info = [&$this->info['Organization_Name'],
                     &$this->info['Organization_Legal_Name'],
                     &$this->info['Organization_Legal_Entity_ID'],
                     &$this->info['Organization_Commercial_Registry'],
                     &$this->info['Organization_Type_ID'],
                     &$this->info['Organization_Business_Line'],
                     &$this->info['Organization_Phone'],
                     &$this->info['Organization_Email'],
                     &$this->info['Organization_Fax'],
                     &$this->info['VAT_Type_ID'],
                     &$this->info['Local_Currency'],
                     &$this->info['Set_Employee_Account']];
        $this->db->prepare_Stmt('
                INSERT INTO
                     tbl_organization
                    (
                        Organization_Name,
                        Organization_Legal_Name,
                        Organization_Legal_Entity_ID,
                        Organization_Commercial_Registry,
                        Organization_Type_ID,
                        Organization_Business_Line,
                        Organization_Phone,
                        Organization_Email,
                        Organization_Fax,
                        VAT_Type_ID,
                        Local_Currency,
                        Set_Employee_Account
                    )
                VALUES (?,?,?,?,?,?,?,?,?,?,?,?)', $org_info);


        $orgID = $this->db->id();
        $address_data = [&$orgID,
                         &$this->info['Organization_Street'],
                         &$this->info['Organization_City'],
                         &$this->info['Organization_Country']];
        $this->db->prepare_Stmt('
            INSERT INTO
                 tbl_organization_postal_address
                (
                    Organization_ID,
                    Organization_Street,
                    Organization_City,
                    Organization_Country
                )
            VALUES(?,?,?,?)', $address_data);
        $social_data = [&$orgID,
                        &$this->info['Organization_Face_Book'],
                        &$this->info['Organization_Twitter'],
                        &$this->info['Organization_Linkedin'],
                        &$this->info['Organization_Plus_Google']];
        $this->db->prepare_Stmt('
                INSERT INTO
                     tbl_organization_social_links
                    (
                        Organization_ID,
                        Organization_Face_Book,
                        Organization_Twitter,
                        Organization_Linkedin,
                        Organization_Plus_Google
                    )
                VALUES(?,?,?,?,?)', $social_data);


        $currency_id = CurrencyFactory::getDefaultCurrency();
        $bank_id_query = $this->db->query('SELECT MAX(Bank_Account_ID) AS Bank_ID FROM tbl_accounting_bank_accounts');
        $bank_id_row = $this->db->assoc($bank_id_query);
        $bank_id = $bank_id_row['Bank_ID'];
        $logged_id = Session::getUserID();
        $finance_data = [&$orgID,
                         &$currency_id,
                         &$this->info['Financial_Year_End'],
                         &$this->info['Financial_Capital'],
                         &$bank_id,
                         &$logged_id];
        $this->db->prepare_Stmt('
                INSERT INTO
                     tbl_organization_financial_settings
                    (
                        Organization_ID,
                        Currency_ID,
                        Financial_Year_End,
                        Financial_Capital,
                        Bank_Account_ID,
                        Logged_ID
                    )VALUES(?,?,?,?,?,?)', $finance_data);


        //            for($i=0;$i<count($this->info['Shareholder']);$i++) {
        foreach ($this->info['Shareholder'] as $item) {
            $share_data = [&$orgID,
                           &$item['Shareholder_Name'],
                           &$item['Shareholder_Amount']];
            $this->db->prepare_Stmt('
                        INSERT INTO
                             tbl_organization_shareholders
                            (
                                Organization_ID,
                                Shareholder_Name,
                                Shareholder_Amount

                            ) VALUES (?,?,?)', $share_data);

            //add account for shareholder
            $account_code = self::returnNeededCode(10210);
            $account_data = [&$item['Shareholder_Name'], &$account_code];
            $this->db->prepare_Stmt('INSERT INTO
                                    tbl_chart_of_accounts 	(
                                                            Account_Name,
                                                            Parent_ID,
                                                            Account_Code,
                                                            IsAdded
                                                            )
                                                            VALUES
                                                            (?,"65",? ,1 )', $account_data);
            $account_code2 = self::returnNeededCode(45510);
            $account_data2 = [&$item['Shareholder_Name'], &$account_code2];
            $this->db->prepare_Stmt('INSERT INTO
                                    tbl_chart_of_accounts 	(
                                                            Account_Name,
                                                            Parent_ID,
                                                            Account_Code,
                                                            IsAdded
                                                            )
                                                            VALUES
                                                            (?,"377",?,1)', $account_data2);
        }

        //        for($i=0;$i<count($this->info['Position']);$i++) {
        foreach ($this->info['Position'] as $item) {
            $hierarchy_data = [&$item['Position_Name'],
                               &$item['Person_Name'],
                               &$orgID];
            $this->db->prepare_Stmt('
                                        INSERT INTO
                                             tbl_organization_hierarchy
                                            (
                                                Position_Name,
                                                Person_Name,
                                                Organization_ID
                                            )
                                        VALUES
                                            (?,?,?)', $hierarchy_data);
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return array_unique($error);
    }

    public function getOrganizationDetails() {
        $data = array();
        $query = $this->db->query('SELECT
                                        o.Organization_ID,
                                        o.Organization_Name,
                                        o.Organization_Legal_Name,
                                        o.Organization_Commercial_Registry,
                                        o.Organization_Business_Line,

                                        o.Organization_Building_no,
                                        o.Organization_Street_no,
                                        o.Organization_District,
                                        o.edit_Organization_Postal_Code,
                                        o.Organization_Additional_no,
                                        o.Organization_vat_num,
                                        o.organization_Other_Seller_ID,

                                        o.org_address_ar,
                                        o.org_address_en,
                                        o.org_cr_en,
                                        o.org_vat_en,
                                        o.org_phone_en,
                                        o.organiaztion_name_en,

                                        o.org_des_ar,
                                        o.org_des_en,
                                        o.org_logo,


                                        o.Organization_Phone,
                                        o.Organization_Email,
                                        o.Organization_Fax,
                                        o.VAT_Type_ID,
                                        t.Organization_Type_Name,
                                        l.Organization_Legal_Entity_Name,
                                        p.*,
                                        f.*,
                                        h.*,
                                        ct.Name as city_name,
                                        ct.ID ,
                                        ct.CountryCode,
                                        ct.District,
                                        ct.Population,
                                        c.*,
                                        b.*
                                    FROM
                                        tbl_organization o
                                    LEFT JOIN
                                        tbl_organization_legal_entity l
                                    ON
                                        l.Organization_Legal_Entity_ID = o.Organization_Legal_Entity_ID
                                    LEFT JOIN
                                        tbl_organization_type t
                                    ON
                                        t.Organization_Type_ID = o.Organization_Type_ID
                                    LEFT JOIN
                                        tbl_organization_postal_address p
                                    ON
                                        p.Organization_ID = o.Organization_ID
                                    LEFT JOIN
                                        tbl_organization_financial_settings f
                                    ON
                                        f.Organization_ID = o.Organization_ID
                                    LEFT JOIN
                                        tbl_organization_social_links h
                                    ON
                                        h.Organization_ID = o.Organization_ID
                                    LEFT JOIN
                                        tbl_all_cities ct
                                    ON
                                        p.Organization_City = ct.ID
                                    LEFT JOIN
                                        tbl_all_countries c
                                    ON
                                        p.Organization_Country = c.Country_ID
                                    LEFT JOIN
                                        tbl_accounting_bank_accounts b
                                    ON
                                        b.Bank_Account_ID = f.Bank_Account_ID
                ');

        $cond = '1=1 ';
        $cond  .= ' and branch_id ="' . (smc_session()['user_accounts']['branch_id']) . '" ' ;
        $cond  .= ' and template_id ="0" ' ;
        while ($row = $this->db->assoc($query)) {
            $branch_attribute_query = $this->db->query('SELECT * FROM `tbl_template_attribute` where '.$cond);
            while ($branch_attribute_row = $this->db->assoc($branch_attribute_query)) {
                $row[$branch_attribute_row['attribute_key']] = $branch_attribute_row['attribute_value'];
            }
            $data[] = $row;
        }
        return $data;
    }
    public function viewHierarchy() {
        $data = array();
        $query = $this->db->query('SELECT
                                        h.*
                                    FROM
                                        tbl_organization_hierarchy h
                                    LEFT JOIN
                                        tbl_organization o
                                    ON
                                        o.Organization_ID = h.Organization_ID

                ');
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function viewShareholders() {
        $data = array();
        $query = $this->db->query('SELECT
                                        h.*
                                    FROM
                                        tbl_organization_shareholders h
                                    LEFT JOIN
                                        tbl_organization o
                                    ON
                                        o.Organization_ID = h.Organization_ID

                ');
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function getOrganizationPhysicalAddress() {
        $data = array();
        $query = $this->db->query('SELECT * FROM tbl_organization_physical_address');
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function SaveUploadedPhoto($FileName, $SavePath, $is_delete_file = true) {
        if (!isset($_FILES[$FileName]))
            return false;
        $SaveToPath = substr($SavePath, 0, strrpos($SavePath, "/") + 1);
        $SaveAsFile = substr($SavePath, strrpos($SavePath, "/") + 1);
        $SaveAsExtention = explode(".", $SaveAsFile);
        $SaveAsExtention = strtolower($SaveAsExtention[count($SaveAsExtention) - 1]);
        $SaveAsFile = substr($SaveAsFile, 0, strrpos($SaveAsFile, "."));
        $fileType = explode(".", basename($_FILES[$FileName]['name']));
        $fileType = strtolower($fileType[count($fileType) - 1]);
        $DestinationFile = $SaveToPath . $SaveAsFile . ".";
        if (file_exists($DestinationFile . $fileType))
            unlink($DestinationFile . $fileType);
        if ($is_delete_file) {
            move_uploaded_file($_FILES[$FileName]['tmp_name'], $DestinationFile . $fileType);
        } else {
            copy($_FILES[$FileName]['tmp_name'], $DestinationFile . $fileType);
        }


        if ($fileType == "png" && $SaveAsExtention == "jpg") {
            if (file_exists($DestinationFile . "jpg"))
                unlink($DestinationFile . "jpg");
            $image = imagecreatefrompng($DestinationFile . $fileType);
            imagejpeg($image, $DestinationFile . $SaveAsExtention, 100);
            imagedestroy($image);
            if (file_exists($DestinationFile . $fileType))
                unlink($DestinationFile . $fileType);
        }
    }
    public function update() {
        $query = $this->db->query('SELECT MAX(Organization_ID) AS Max_Org_Number FROM tbl_organization');
        $row = $this->db->assoc($query);
        $org_id = $row['Max_Org_Number'];
        $data_org = [&$this->info['Organization_Name'],
                     &$this->info['Organization_Email'],
                     &$this->info['Organization_Fax'],
                     &$this->info['Organization_Phone'],
                     &$this->info['Organization_Business_Line'],
                     &$this->info['Organization_Street'],
                     &$this->info['Organization_City'],
                     &$this->info['Organization_Country'],
                     &$this->info['Organization_Face_Book'],
                     &$this->info['Organization_Twitter'],
                     &$this->info['Organization_Linkedin'],
                     &$this->info['Organization_Plus_Google'],
                     &$this->info['Organization_Street_no'],
                     &$this->info['Organization_District'],
                     &$this->info['building_no'],
                     &$this->info['Organization_Additional_no'],
                     &$this->info['edit_Organization_Postal_Code'],
                     &$this->info['organization_Other_Seller_ID'],
                     &$this->info['Organization_vat_num'],

                     
                     &$this->info['org_address_ar'],
                     &$this->info['org_address_en'],
                     &$this->info['org_cr_en'],
                     &$this->info['org_vat_en'],
                     &$this->info['org_phone_en'],
                     &$this->info['organiaztion_name_en'],
                     &$this->info['Organization_Commercial_Registry'],
                     &$this->info['Organization_Name'],
                     &$this->info['org_des_en'],
                     &$this->info['org_des_ar'],

                     &$org_id
                    ];
        $this->db->prepare_Stmt('
                                    UPDATE
                                        tbl_organization o
                                    inner join
                                        tbl_organization_postal_address a
                                    ON 
                                        o.Organization_ID = a.Organization_ID
                                    inner join 
                                        tbl_organization_social_links l
                                    ON 
                                        o.Organization_ID = l.Organization_ID
                                    SET
                                        Organization_Name 		   = ?,
                                        Organization_Email 		   = ?,
                                        Organization_Fax 		   = ?,
                                        Organization_Phone 		   = ?,
                                        Organization_Business_Line = ?,
                                        Organization_Street = ? ,
                                        Organization_City = ?,
                                        Organization_Country = ?,
                                        Organization_Face_Book   = ?,
                                        Organization_Twitter 	 = ?,
                                        Organization_Linkedin 	 = ?,
                                        Organization_Plus_Google = ?,
                                        Organization_Street_no = ?,
                                        Organization_District = ?,
                                        Organization_Building_no 	 = ?,
                                        Organization_Additional_no 	 = ?,
                                        edit_Organization_Postal_Code = ?,
                                        organization_Other_Seller_ID = ?,

                                        Organization_vat_num = ?,
                                        org_address_ar= ?,
                                        org_address_en= ?,
                                        org_cr_en= ?,
                                        org_vat_en= ?,
                                        org_phone_en= ?,
                                        organiaztion_name_en=?,
                                        Organization_Commercial_Registry=?,
                                        Organization_Legal_Name=?,
                                        org_des_en=?,
                                        org_des_ar=?

                                    WHERE
                                        o.Organization_ID = ?', $data_org);
        $error = $this->db->error();
        return $error;
    }

    public function org_upolad_file() {
            $org_id = 1;
        

            $uploaddir = upload_path . 'org_logo/';
            $ser_images_name = $_FILES["new_image_name"]['name'];
            $ser_images_exe = explode('.', $ser_images_name);
            $ser_images_exe = strtolower(end($ser_images_exe));
            $DB='org_logo/invoice_logo' . '.' . $ser_images_exe;
            $this->SaveUploadedPhoto("new_image_name", $uploaddir . 'invoice_logo' . '.' . $ser_images_exe);
            $data_org = [
                $DB,
                &$org_id
            ];
            $this->db->prepare_Stmt('
                                        UPDATE
                                            tbl_organization o
                                        SET
                                            org_logo 		   = ?
                                        WHERE
                                            o.Organization_ID = ?', $data_org);
            $error = $this->db->error();
            return 1;
    }

    public function updateStatusesCOAMain($Data) {
        $this->db->query('START TRANSACTION');
        for ($x = 0; $x < count($Data); $x++) {
            if ($Data[$x][2] == "WithChildren") {
                self::UpdateCOAStatusWithChildren($Data[$x][0], $Data[$x][1]);
            } else {
                self::UpdateCOAStatus($Data[$x][0], $Data[$x][1]);
            }
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return array_unique($error);
    }

    public function UpdateCOAStatus($ID, $Status) {
        $this->db->prepare_Stmt('
                  UPDATE
                      tbl_chart_of_accounts
                SET
                    Account_Status = ?
                WHERE
                    Account_ID = ?', [&$Status, &$ID]);
    }

    public function UpdateCOAStatusWithChildren($ID, $Status) {
        $this->UpdateCOAStatus($ID, $Status);
        $Children = $this->viewCOA($ID);
        if (count($Children) > 0) {
            for ($x = 0; $x < count($Children); $x++) {
                $this->UpdateCOAStatusWithChildren($Children[$x]["Account_ID"], $Status);
            }
        }
    }

    public function RemoveItemWithChildrenMain($ID) {
        //should check that account is not used and do not has children 
        //for children to delete must not used and has main type only (sys or pay)
        $error = array();
        $query = $this->db->query('select * from tbl_chart_of_accounts where Parent_ID = ?', [&$ID]);
        if ($this->db->rowsno($query) > 0) {
            $error[0] = CommonLanguage::getMessage('parent_account_alert');
            return $error;
        }
        $query = $this->db->query('select * from tbl_accounting_transactions where Account_ID = ?', [&$ID]);
        if ($this->db->rowsno($query) > 0) {
            $error[0] = CommonLanguage::getMessage('trans_account_alert');
            return $error;
        }
        $query = $this->db->query('SELECT * FROM `tbl_account_setting` 
            where setting_type = "table##s##chart_of_accounts" and concat(",",acc_setting_value,",") like "%,' . $ID . ',%"', [&$ID]);
        if ($this->db->rowsno($query) > 0) {
            $error[0] = CommonLanguage::getMessage('account_setting_alert');
            return $error;
        }
        $query = $this->db->query("SELECT * FROM `tbl_users_accounting_accounts` 
            WHERE concat(',',concat_ws(',',`cash_account_id`,`cheque_account_id`,`purchase_account_id`,`sales_account_id`,`deposit_account_id`,`withdrawal_account_id`,`other_account_id`,`pur_ship_account_id`,`sales_ship_account_id`),',')
            like '%," . $ID . ",%'");
        if ($this->db->rowsno($query) > 0) {
            $error[0] = CommonLanguage::getMessage('user_account_alert');
            return $error;
        }
        $query = $this->db->query("SELECT * FROM `tbl_invoices` WHERE Account_ID = ?", [&$ID]);
        if ($this->db->rowsno($query) > 0) {
            $error[0] = CommonLanguage::getMessage('invoice_account_alert');
            return $error;
        }
        $query = $this->db->query("SELECT * FROM `tbl_accounting_return_invoices` WHERE Account_ID = ?", [&$ID]);
        if ($this->db->rowsno($query) > 0) {
            $error[0] = CommonLanguage::getMessage('return_account_alert');
            return $error;
        }
        $query = $this->db->query("SELECT * FROM `tbl_inventory_purchases` WHERE Account_ID = ?", [&$ID]);
        if ($this->db->rowsno($query) > 0) {
            $error[0] = CommonLanguage::getMessage('purchase_account_alert');
            return $error;
        }
        $this->db->prepare_Stmt('DELETE FROM tbl_chart_of_accounts WHERE Account_ID = ?', [&$ID]);
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return array_unique($error);

        //        $this->db->query('START TRANSACTION');
        //        self::RemoveItemWithChildren($ID);
        //        $error = $this->db->error();
        //        if (empty($error)) {
        //            $this->db->query('COMMIT');
        //        } else {
        //            $this->db->query('ROLLBACK');
        //        }
        //        return array_unique($error);
        //        return array();
    }

    //    public function RemoveItemWithChildren($ID) {
    //        $this->db->prepare_Stmt('DELETE FROM tbl_chart_of_accounts WHERE Account_ID = ?', [&$ID]);
    //        $Children = $this->viewCOA($ID);
    //        if (count($Children) > 0) {
    //            for ($x = 0; $x < count($Children); $x++) {
    //                $this->RemoveItemWithChildren($Children[$x]["Account_ID"]);
    //            }
    //        }
    //    }

    public function viewCOA($ParentID = -1, $Status = -1) {
        $payment_parent = smc_session()['account_setting']['pay_type_parent_acc_id'];
        $customer_parent = smc_session()['account_setting']['customer_main_acc_id'];
        $supplier_parent = smc_session()['account_setting']['supplier_main_acc_id'];
        //        echo 'pay: '.$payment_parent.' cust: '.$customer_parent.' supp:'.$supplier_parent.'==';
        //        die();
        $data = array();
        $Condition1 = $ParentID == -1 ? '' : ' WHERE a.Parent_ID = "' . $ParentID . '"';
        $Condition2 = $Status == -1 ? '' : (($Condition1 == "" ? " WHERE " : " AND ") . '(a.Account_Status = "' . $Status . '" ' . ($Status == 1 ? " OR a.Account_Status='2')" : ")"));
        //$this->db->query("SET NAMES utf8;");
        //$this->db->query("SET CHARACTER_SET utf8;");

        $query = $this->db->query('SELECT 
                                        a.*,
                                        c.Currency_Symbol,
                                        c.Currency_Name
                                    FROM 
                                        tbl_chart_of_accounts a
                                    LEFT JOIN
                                        tbl_common_currency c
                                    ON 
                                        a.Currency_ID = c.Currency_ID
                                  ' . $Condition1 . $Condition2.' order by Account_Code'
                                 );
        $is_cust = 0;
        $is_supp = 0;
        $is_pay = 0;
        //if $ParentID is (pay or cust or supp) set attributes
        //else call recursion check is_parent(parent_id==> settings, chilrd=$ParentID)
        if ($ParentID == $customer_parent)
            $is_cust = 1;
        if ($ParentID == $supplier_parent)
            $is_supp = 1;
        if ($ParentID == $payment_parent || empty($payment_parent))
            $is_pay = 1;
        //

        if ($is_cust != 1)
            $is_cust = CommonACC::account_is_child($customer_parent, $ParentID);
        if ($is_supp != 1)
            $is_supp = CommonACC::account_is_child($supplier_parent, $ParentID);
        if ($is_pay != 1)
            $is_pay = CommonACC::account_is_child($payment_parent, $ParentID);
        //
        while ($row = $this->db->assoc($query)) {
            $row["Account_Name"] = ucwords($row["Account_Name"]);
            //
            $row_is_pay = $is_pay;
            $row_is_cust = $is_cust;
            $row_is_supp = $is_supp;
            if ($row["Account_ID"] == $customer_parent)
                $row_is_cust = 1;
            if ($row["Account_ID"] == $supplier_parent)
                $row_is_supp = 1;
            if ($row["Account_ID"] == $payment_parent || empty($payment_parent))
                $row_is_pay = 1;
            //
            if ($row_is_cust == 1)
                $row_is_supp = $row_is_pay = 0;
            if ($row_is_supp == 1)
                $row_is_pay = 0;
            //
            $row['is_pay'] = $row_is_pay;
            $row['is_cust'] = $row_is_cust;
            $row['is_supp'] = $row_is_supp;
            $row['is_parent'] = CommonACC::account_is_parent($row["Account_ID"]);
            $data[] = $row;
        }
        return $data;
    }

    public function viewSingleCOA($AccountID = -1, $Status = -1) {
        $data = array();
        $Condition1 = $AccountID == -1 ? '' : ' WHERE a.Account_ID = "' . $AccountID . '"';
        $Condition2 = $Status == -1 ? '' : (($Condition1 == "" ? " WHERE " : " AND ") . '(a.Account_Status = "' . $Status . '" ' . ($Status == 1 ? " OR a.Account_Status='2')" : ")"));
        //$this->db->query("SET NAMES utf8;");
        //$this->db->query("SET CHARACTER_SET utf8;");

        $query = $this->db->query('SELECT 
                                        a.*,
                                        c.Currency_Symbol,
                                        c.Currency_Name
                                    FROM 
                                        tbl_chart_of_accounts a
                                    LEFT JOIN
                                        tbl_common_currency c
                                    ON 
                                        a.Currency_ID = c.Currency_ID' . $Condition1 . $Condition2);
        while ($row = $this->db->assoc($query)) {
            $row["Account_Name"] = ucwords($row["Account_Name"]);
            $data[] = $row;
        }
        return $data;
    }

    //    public function returnAccounts() {
    //        $data = array();
    //        $query = $this->db->query('SELECT
    //                                            *
    //                                        FROM
    //                                            tbl_chart_of_accounts c
    //                                        WHERE
    //                                        c.Cat_Name
    //                                        NOT IN(SELECT
    //                                                    Account_Name
    //                                                FROM
    //                                                    tbl_organization_chart_of_accounts oc)
    //                    ');
    //        while ($row = $this->db->assoc($query)) {
    //            $data[] = $row;
    //        }
    //        return $data;
    //    }

    public function createChildernInCOA() {
        $this->db->query('START TRANSACTION');
        $fa_child = CommonACC::account_is_child(smc_session()['account_setting']['fixed_asset_parent_acc_id'], $this->info['Parent_ID']);
        $fa_flag = 0;
        if ($fa_child == 1 || smc_session()['account_setting']['fixed_asset_parent_acc_id'] == $this->info['Parent_ID'])
            $fa_flag = 1;
        //        $query_fa = $this->db->query('select Account_Code from tbl_chart_of_accounts where Account_ID = "'.smc_session()['account_setting']['fixed_asset_parent_acc_id'].'"');
        //        $fa_parent_code = $this->db->assoc($query_fa)['Account_Code'];
        $subtype = empty($this->info['acc_sub_type']) ? '' : implode($this->info['acc_sub_type'], ',');
        $main_type = "sys";
        if (preg_match("/cash|credit|check/", $subtype))
            $main_type = "pay";
        if ($fa_flag == 1) {
            $main_type = "dep";
            $subtype = "main";
        }
        $arabicName = htmlspecialchars($this->info['Account_Arabic_Name']);
        $data = [&$this->info['Account_Name'],
                 &$arabicName,
                 &$this->info['Parent_ID'],
                 &$this->info['Account_Code'],
                 &$this->info['Currency_ID'],
                 &$this->info['job_ids'],
                 &$this->info['job_required'],
                 &$main_type,
                 &$subtype];
        $this->db->prepare_Stmt('INSERT INTO
                                    tbl_chart_of_accounts 	(
                                                            Account_Name,
                                                            Account_Arabic_Name,
                                                            Parent_ID,
                                                            Account_Code,
                                                            Currency_ID,
                                                            IsAdded,
                                                            job_ids,
                                                            job_required,
                                                            main_type,
                                                            sub_type
                                                            )
                                                            VALUES
                                                            (?,?,?,?,?,1,?,?,?,?)', $data);
        //        $error = $this->db->error();
        //        die(print_r($error));
        $acc_id = $this->db->id();
        $code = $this->info['Account_Code'];
        if ($fa_flag == 1) {
            $main_type = 'dep';
            //parent of Depreciation accounts
            $code_dep = CommonACC::generate_account(smc_session()['account_setting']['depreciation_parent_acc_id'], 1);
            $dep_parent_account = smc_session()['account_setting']['depreciation_parent_acc_id'];
            $data = [&$this->info['Account_Name'],
                     &$arabicName,
                     &$dep_parent_account,
                     &$code_dep,
                     &$this->info['Currency_ID'],
                     &$main_type, &$acc_id];
            $this->db->prepare_Stmt('INSERT INTO
                tbl_chart_of_accounts (Account_Name, Account_Arabic_Name,Parent_ID,Account_Code,Currency_ID,
                main_type,main_id,sub_type,IsAdded)VALUES (?,?,?,?,?,?,?,"dep",1)', $data);
            //            self::insertDepreciationProvisionAccount($account_dep, $arabic_dep, smc_session()['account_setting']['depreciation_parent_acc_id'], $code_dep);
            //parent of Provision accounts
            $code_prov_65 = CommonACC::generate_account(smc_session()['account_setting']['provision_parent_acc_id'], 1); //parent of Provision accounts
            //            $account_prov_65 = $this->info['Account_Name'];
            //            $arabic_prov_65 = $arabicName;
            //            self::insertDepreciationProvisionAccount($account_prov_65, $arabic_prov_65, smc_session()['account_setting']['provision_parent_acc_id'], $code_prov_65);
            $prov_parent_account = smc_session()['account_setting']['provision_parent_acc_id'];
            $data = [&$this->info['Account_Name'],
                     &$arabicName,
                     &$prov_parent_account,
                     &$code_prov_65,
                     &$this->info['Currency_ID'],
                     &$main_type, &$acc_id];
            $this->db->prepare_Stmt('INSERT INTO
                tbl_chart_of_accounts (Account_Name, Account_Arabic_Name,Parent_ID,Account_Code,Currency_ID,
                main_type,main_id,sub_type,IsAdded)VALUES (?,?,?,?,?,?,?,"pro",1)', $data);
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return array_unique($error);
    }

    public function viewOrganizationTypes() {
        $data = array();
        $query = $this->db->query('SELECT
                                        *
                                    FROM
                                        tbl_organization_type
                ');
        while ($row = $this->db->assoc($query))
            $data[] = $row;
        return $data;
    }

    public function viewOrganizationLegalEntity() {
        $data = array();
        $query = $this->db->query('SELECT
                                        *
                                    FROM
                                        tbl_organization_legal_entity
                ');
        while ($row = $this->db->assoc($query))
            $data[] = $row;
        return $data;
    }

    public function viewCities() {
        $data = array();
        $query = $this->db->query('SELECT
                                        *
                                    FROM
                                        tbl_cities
                ');
        while ($row = $this->db->assoc($query))
            $data[] = $row;
        return $data;
    }

    public function viewVATTypes() {
        $data = array();
        $query = $this->db->query('SELECT * FROM tbl_accounting_vat_type_names');
        while ($row = $this->db->assoc($query))
            $data[] = $row;
        return $data;
    }

    private function insertDepreciationProvisionAccount($account_name, $arabic_name, $parent_id, $code) {
        $data = [&$account_name,
                 &$arabic_name,
                 &$parent_id,
                 &$code,];
        $this->db->prepare_Stmt('INSERT INTO
                                    tbl_chart_of_accounts 	(
                                                            Account_Name,
                                                            Account_Arabic_Name,
                                                            Parent_ID,
                                                            Account_Code,
                                                            IsAdded
                                                            )
                                                            VALUES (?,?,?,?,1)', $data);
    }

    private function returnNeededCode($parent_account) {
        $len = strlen($parent_account);
        $query = $this->db->query('SELECT MAX(Account_Code) AS max_code FROM tbl_chart_of_accounts WHERE LEFT(Account_Code,' . $len . ') = "' . $parent_account . '" AND IsAdded=1');
        $row = $this->db->assoc($query);
        if (is_null($row['max_code']))
            $code = $parent_account . '001';
        else
            $code = $row['max_code'] + 1;
        return $code;

        //		$parent = $this->db->query('SELECT Account_Code FROM tbl_chart_of_accounts WHERE Account_ID = "'.$parent_id.'"');
        //        $row_parent = $this->db->assoc($parent);
        //        $parent_code = $row_parent['Account_Code'];
        //        $query = $this->db->query('SELECT MAX(Account_Code) AS max_code FROM tbl_chart_of_accounts WHERE Parent_ID = "'.$parent_id.'"');
        //        $row = $this->db->assoc($query);
        //        $max_code = $row['max_code'];
        //        if(is_null($max_code)){
        //			$len = strlen($parent_code);
        //			$rest = 8 - $len;
        //			$len_zeros = $rest - 1;
        //			$zeros = str_repeat("0", $len_zeros);
        ////            $query1 = $this->db->query('SELECT Account_Code FROM tbl_chart_of_accounts WHERE Account_ID =  "'.$parent_id.'"  ');
        ////            $row2 = $this->db->assoc($query1);
        ////                $max_code2 = $row2['Account_Code'];
        //            $code = $parent_code . '-' . $zeros . '1';
        //            return $code;
        //        }
        //		$num_code = substr($max_code, strpos($max_code, "-") + 1);//after (-)
        //		$num_code_final = $num_code + 1;//add it with 1
        //		$num_code_b = substr($max_code, 0, strpos($max_code, "-"));//before (-)=>parent
        //		$len = strlen($num_code_b);
        //		$final_len = $len + strlen($num_code_final);
        //		$rest = 8 - $final_len;
        //		$zeros = str_repeat("0", $rest);
        //        $code = $parent_code . '-' . $zeros . $num_code_final;
        //        return $num_code_final;
    }

    public function editAccount() {
        $this->db->query('START TRANSACTION');
        $query = $this->db->query("SELECT Account_Name,Account_Code FROM tbl_chart_of_accounts WHERE Account_ID = '" . $this->info['Account_ID'] . "'");
        $row = $this->db->assoc($query);
        $old_code = $row['Account_Code'];
        CommonACC::change_parent_code($this->info['Account_ID'], $old_code, $this->info['Account_Code'], false);
        //        $subtype = isset($this->info['sub_type']) ? $this->info['sub_type'] : (isset($this->info['acc_sub_type']) ? implode(',',$this->info['acc_sub_type']) : '');
        $subtype = isset($this->info['acc_sub_type']) ? implode(',', $this->info['acc_sub_type']) : (isset($this->info['sub_type']) ? $this->info['sub_type'] : '');
        $main_type = isset($this->info['main_type']) ? $this->info['main_type'] : "sys";
        if (preg_match("/cash|credit|check/", $subtype))
            $main_type = "pay";

        $data = [&$this->info['Account_Name'],
                 &$this->info['Account_Arabic_Name'],
                 &$this->info['Account_Code'],
                 &$this->info['Currency_ID'],
                 &$this->info['job_ids'],
                 &$this->info['job_required'],
                 &$this->info['Status_ID'],
                 &$subtype,
                 &$main_type,
                 &$this->info['Account_ID']
                ];
        //        echo"<pre>";die(print_r($data));
        $this->db->prepare_Stmt('UPDATE
                                        tbl_chart_of_accounts 
                                    SET
                                        Account_Name        = ?,
                                        Account_Arabic_Name = ?,
                                        Account_Code        = ?,
                                        Currency_ID         = ?,
                                        job_ids             = ?,
                                        job_required        = ?,
                                        Status_ID           = ?,
                                        sub_type            = ?,
                                        main_type           = ?
                                    WHERE   
                                        Account_ID          = ?', $data);
        if ($main_type === 'dep' && $subtype === 'main') {

            $up = [&$this->info['Account_Name'],
                   &$this->info['Account_Arabic_Name'],
                   &$this->info['Currency_ID'],
                   &$this->info['Account_ID'],
                   &$main_type
                  ];

            $this->db->prepare_Stmt('UPDATE
                                        tbl_chart_of_accounts 
                                    SET
                                        Account_Name        = ?,
                                        Account_Arabic_Name = ?,
                                        Currency_ID         = ?
                                    WHERE   
                                    main_id = ?
                                    and sub_type in ("dep","pro")
                                    and main_type = ?
                                    and IsAdded = 1', $up);
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query("COMMIT");
        } else {
            $this->db->query("ROLLBACK");
        }
        return array_unique($error);
    }

    public function getOrganizationAddress() {
        $data = array();
        $query = $this->db->query('SELECT
                                       o.Organization_Name,
                                        o.Organization_Phone,
										CONCAT(ct.Name,", ",p.Organization_Street) AS Org_Address

                                    FROM
                                        tbl_organization o

                                    LEFT JOIN
                                        tbl_organization_postal_address p
                                    ON
                                        p.Organization_ID = o.Organization_ID

                                    LEFT JOIN
                                        tbl_all_cities ct
                                    ON
                                        p.Organization_City = ct.ID


                ');
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function add_org_branch() {
        $check = self::getPrefixIfUsed($this->info['branch_prefix']);
        $this->db->prepare_Stmt('insert into tbl_organization_branches 
            (branch_name,branch_prefix) values(?,?)', [&$this->info['branch_name'], &$this->info['branch_prefix']]);
        $error = $this->db->error();
        return [$error, $check];
    }

    public function edit_org_branch() {
        $check = self::getPrefixIfUsed($this->info['branch_prefix'], $this->info['branch_id']);
        $this->db->prepare_Stmt('update tbl_organization_branches 
            set branch_name = ?,branch_prefix = ? 
            where branch_id = ?', [&$this->info['branch_name'], &$this->info['branch_prefix'], &$this->info['branch_id']]);
        $error = $this->db->error();
        return [$error, $check];
    }

    public function getOrgBranch($id) {
        $query = $this->db->query('select * from tbl_organization_branches where branch_id = "' . $id . '"');
        $data = $this->db->assoc($query);
        return $data;
    }

    public static function getPrefixIfUsed($prefix, $not_in_id = null, $is_branch = 1) {
        $cond = ' ';
        $primary_col = $is_branch ? ' branch_id ' : ' User_Account_ID ';
        if (!is_null($not_in_id)) {
            $cond .= ' and ' . $primary_col . ' <> "' . $not_in_id . '"';
        }
        $db = DBConnectorFactory::loadDriver();
        $query_branch = $db->query('select * from tbl_organization_branches where branch_prefix = "' . $prefix . '"' . ($is_branch ? $cond : ''));
        $row_branch = $db->assoc($query_branch);
        if (count($row_branch) > 0) {
            return true;
        }
        $query_user = $db->query('select * from tbl_users_accounting_accounts where serial_prefix = "' . $prefix . '"' . (!$is_branch ? $cond : ''));
        $row_user = $db->assoc($query_user);
        if (count($row_user) > 0) {
            return true;
        }
        return false;
    }

    public function update_acc_node() {
        $this->db->query('START TRANSACTION');
        $account_id = $this->info['account_id'];
        $parent_id = $this->info['parent_id'];
        $parent_code = $this->info['parent_code'];
        $old_parent_id = $this->info['old_parent_id'];
        $current_code = $this->info['current_code'];
        $query = $this->db->query('select Account_Code from tbl_chart_of_accounts where Account_ID = "' . $old_parent_id . '"');
        $old_parent_code = $this->db->assoc($query)['Account_Code'];
        $current_code = $parent_code . substr($current_code, strlen($old_parent_code));
        //        die($parent_code.'=='.$old_parent_code.'====='.$current_code.'====');
        $this->db->prepare_Stmt('update tbl_chart_of_accounts set Account_Code = ?,Parent_ID = ?
            where Account_ID = ?', [&$current_code, &$parent_id, &$account_id]);

        CommonACC::change_parent_code($account_id, $old_parent_code, $parent_code, false);
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }
    public function searchCity($term = null, $page, $limit) {
        $data = array();
        $data['is_more'] = false;
        $data['data'] = array();
        $cond = '';
        $data_filter = array();
        if (!is_null($term)) {
            $cond .= ' and (cities.Name LIKE ?  '.(!empty($this->info['show_country_name']) ? ' or countries.Country_Name LIKE ? ' : '').')';
            $data_filter[] = "%".$term."%";
            if(!empty($this->info['show_country_name']))
                $data_filter[] = "%".$term."%";
            
        }
        $query = $this->db->prepare_Stmt('select 
                                    ID as id,
                                    '.(!empty($this->info['show_country_name']) ? 'concat_ws(" - ",cities.Name,countries.Country_Name)' : 'Name').' as text
                                from 
                                    tbl_all_cities cities
                                 inner join tbl_all_countries countries on countries.Code =  cities.CountryCode
                                 where 1=1 '.(!empty($this->info['Country_ID']) ? ' and countries.Country_ID = "'.$this->info['Country_ID'].'"' : '').' 
                                ' . $cond . '
                                limit ' . ($page * $limit) . ',' . ($limit + 1),$data_filter);
        while ($row = $this->db->assoc($query)) {
            $data['data'][] = $row;
        }
        if (count($data['data']) > $limit) {
            array_pop($data['data']);
            $data['is_more'] = true;
        }
        return $data;
    }
    public function searchFastpaxCities($term = null, $page, $limit) {
         $data = array();
        $data['is_more'] = false;
        $data['data'] = array();
        $cond = '';
        $data_filter = array();
        if (!is_null($term)) {
            $cond .= ' and (cities.city_name LIKE ?  or district.district_name LIKE ? )';
            $data_filter[] = "%".$term."%";
            $data_filter[] = "%".$term."%";
            
        }
        $query = $this->db->prepare_Stmt('select 
                                    cities.city_id as id,
                                    concat_ws(" - ",cities.city_name,district.district_name) as text
                                from 
                                    tbl_fastpax_cities cities
                                inner join tbl_shipping_district district using(district_id)
                                where 1=1 
                                ' . $cond . '
                                limit ' . ($page * $limit) . ',' . ($limit + 1),$data_filter);
        while ($row = $this->db->assoc($query)) {
            $data['data'][] = $row;
        }
        if (count($data['data']) > $limit) {
            array_pop($data['data']);
            $data['is_more'] = true;
        }
        return $data;
    }   
    public function getshippingCompanies(){
        $data = array();
        $query = $this->db->query('select distinct(shipping_company_id),api_folder from tbl_shipping_company where api_folder in ("fastpax")');
        while($row = $this->db->assoc($query)){
            if($row['api_folder'] == 'fastpax')
                $data[2] = 'Fastpax';
//            if($row['api_folder'] == 'aramex')
//                 $data[2] = 'Aramex';
        }
        return $data;
        
    }
}
