<?php

class User {

    protected $info = array();
    private $db;

    public function __construct() {
        $this->db = DBConnectorFactory::loadDriver();
    }

    public function setData($data) {
        $this->info = $data;
        return $this;
    }

    public function view($is_hidden = false) {
        $data = array();
        $condition = '';
        if (!$is_hidden)
            $condition .= ' AND p.Is_Hidden = 0';
        $query = $this->db->query('SELECT 
										u.User_ID,
										u.User_Name,
										u.User_Password,
										r.Role_Name,
										r.Role_ID,
										p.Employee_ID,
										CONCAT(p.Employee_FirstName, " ", p.Employee_MiddleName, " ", p.Employee_LastName) AS Employee_Name
									FROM 
										tbl_common_users u
									LEFT JOIN 
										tbl_hrm_employee p 
									ON 
										u.Employee_ID = p.Employee_ID
									LEFT JOIN 
										tbl_common_roles r 
									ON 
										u.Role_ID = r.Role_ID
                                    WHERE
                                        u.Status_ID = 1
                                    ' . (smc_session()['user_logged_username'] == 'admin' ? '' : ' AND u.User_Name !="admin" ') . '
				');
        while ($row = $this->db->assoc($query))
            $data[] = $row;
        return $data;
    }

    public function searchEmployeeUsers($term = null, $page, $limit, $type = null) {
        $data = array();
        $data['is_more'] = false;
        $data['data'] = array();
        $cond = '';
        $data_filter = array();
        if (!is_null($term)) {
            $cond .= ' and u.User_Name  LIKE ? ';
            $data_filter[] = "%" . $term . "%";
        }
        if (!is_null($type)) {
            $cond .= ' and u.is_app = ? ';
            $data_filter[] = $type;
        }
        $query = $this->db->prepare_Stmt('SELECT 
                                                    u.User_ID as id,
                                                    u.User_Name as text
                                            FROM 
                                                    tbl_common_users u
                                            LEFT JOIN 
                                                    tbl_hrm_employee p 
                                            ON 
                                                    u.Employee_ID = p.Employee_ID
                                            LEFT JOIN 
                                                    tbl_common_roles r 
                                            ON 
                                                    u.Role_ID = r.Role_ID
                                            WHERE
                                                u.Status_ID = 1
                                            ' . (smc_session()['user_logged_username'] == 'admin' ? '' : ' AND u.User_Name !="admin" ') . $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 enableDisableUser() {
        $logged_id = smc_session()['user_logged_id'];
        $status_id = $this->info['status_id'] == 0 ? 1 : 0;
        $this->db->prepare_Stmt('UPDATE tbl_common_users SET Status_ID = ?,Logged_ID=? WHERE User_ID = ?', [&$status_id,
                                                                                                            &$logged_id, &$this->info['user_id']]);
        $error = $this->db->error();
        return $error;
    }
    

    public function deleteUser($user_id) {
        $this->db->prepare_Stmt('Delete from tbl_common_users WHERE User_ID = ?', [&$user_id]);
        $error = $this->db->error();
        return $error;
    }

    public function checkIfRoleUsed($rid) {

        //check if if role is used in useres
        $user_role_query = $this->db->query('select * from tbl_common_users where Role_ID LIKE "%' . $rid . '%" ||  control_role_id LIKE "%' . $rid . '%"   ');
        while ($row = $this->db->assoc($user_role_query)) {
            $full_role = $row['Role_ID'];
            $split_role = explode(",", $full_role);
            foreach ($split_role as $role_id) {
                if ($rid == $role_id) {
                    return "user_role";
                }
            }

            $full_role = $row['control_role_id'];
            $split_role = explode(",", $full_role);
            foreach ($split_role as $role_id) {
                if ($rid == $role_id) {
                    return "user_role";
                }
            }
        }
       
        //check if if role is used in chiled role permation
        $parent_role_query = $this->db->query('select * from tbl_common_roles where Parent_ID = "' . $rid . '" limit 1');
        $parent_role_count = $this->db->rowsno($parent_role_query);
        if ($parent_role_count > 0) {
            return "parent_role";
        }

        if($rid == 1){
            return "admin_role";
        }
        return '';
    }

    public function deleteRolePermation($role_id) {
        $check = self::checkIfRoleUsed($role_id);
        $mydate = date("Y-m-d H:i:s");
        if (!empty($check)) {
            return [$check];
        }
        $this->db->query('START TRANSACTION');
        $delete = [&$role_id];
        $this->db->prepare_Stmt("DELETE FROM tbl_common_role_permission WHERE Role_ID = ? ", $delete);
        $this->db->prepare_Stmt("DELETE FROM tbl_common_roles WHERE Role_ID = ? ", $delete);
        $arr_role = ['type' => 'role', 'main_id' => $role_id,
            'action' => 2, 'date_time' => $mydate,
            'user_id' => smc_session()['user_logged_id']];
        CommonFunctions::add_user_log($arr_role);
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }


    /**
     * Generic function to get an uploaded $_FILE[name_of_file] and save it to a specific path 
     * @param [[Type]] $FileName The name of the input[file] tag that is assigned in the client-side
     * @param [[Type]] $SavePath Destination path
     */
    public function SaveUploadedPhoto($FileName, $SavePath) {
        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 . ".";
        unlink($DestinationFile . $fileType);
        move_uploaded_file($_FILES[$FileName]['tmp_name'], $DestinationFile . $fileType);
        if ($fileType == "png" && $SaveAsExtention == "jpg") {
            unlink($DestinationFile . "jpg");
            $image = imagecreatefrompng($DestinationFile . $fileType);
            imagejpeg($image, $DestinationFile . $SaveAsExtention, 100);
            imagedestroy($image);
            unlink($DestinationFile . $fileType);
        }
        return file_exists($DestinationFile . $SaveAsExtention);
    }
    public function SaveUploadedPhotoNoLink($FileName, $SavePath) {
        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 . ".";
        move_uploaded_file($_FILES[$FileName]['tmp_name'], $DestinationFile . $fileType);
        if ($fileType == "png" && $SaveAsExtention == "jpg") {
            $image = imagecreatefrompng($DestinationFile . $fileType);
            imagejpeg($image, $DestinationFile . $SaveAsExtention, 100);
            imagedestroy($image);

        }
        return file_exists($DestinationFile . $SaveAsExtention);
    }

    public function changePassword() {
        $pass = md5(sha1(md5($this->info['User_Password'])));
        $logged_id = Session::getUserID();
        $data = [&$this->info['User_Name'],
                 &$pass,
                 &$logged_id,
                 &$this->info['Employee_ID'],
                 &$this->info['Role_ID'],
                 &$this->info['User_ID']];
        $this->db->prepare_Stmt('UPDATE tbl_common_users SET
                                        User_Name     = ?,
                                        User_Password = ?,
                                        Logged_ID     = ?,
                                        Employee_ID   = ?,
                                        Role_ID       = ?,
                                        is_main_transfer = 0
                                    WHERE
                                        User_ID  = ?', $data);
        $error = $this->db->error();
        return $error;
    }

    public function updatePassword() {
        $pass = md5(sha1(md5($this->info['NewPassword'])));
        $logged_id = $this->info[0];
        $user_id = $this->info['ChangePassword'];
        $data = [
            &$pass,
            &$logged_id, &$user_id];
        $this->db->prepare_Stmt('UPDATE tbl_common_users SET
                                        User_Password = ?,
                                        Logged_ID     = ?
                                    WHERE
                                        User_ID  = ?', $data);
        $error = $this->db->error();
        return $error;
    }

    public function updateUserIsAdmin() {
        $logged_id = $this->info[0];
        $is_admin = $this->info['is_admin'];
        $user_id = $this->info['user_id'];
        $data = [
            &$is_admin,
            &$logged_id, &$user_id];
        $this->db->prepare_Stmt('UPDATE tbl_common_users SET
                                        is_admin = ?,
                                        Logged_ID     = ?
                                    WHERE
                                        User_ID  = ?', $data);
        $error = $this->db->error();
        return $error;
    }

    public function getUser() {
        $user = Session::getUserID();
        $query = $this->db->query('SELECT u.*,
                                          group_concat(`r`.`Role_Name` separator ", ") AS `Role_Name`,
                                            p.Employee_FirstName,
                                            p.Employee_LastName,
                                            p.Employee_Email,
                                            p.Branch_ID,
                                            landing.landing_url as landing,
                                            u.u_user_shifts_id
                                    FROM tbl_common_users u 
                                    inner join tbl_common_roles r on(find_in_set(`r`.`Role_ID`,`u`.`Role_ID`))
                                    inner join tbl_landings landing using(landing_id)
                                    left join `tbl_hrm_employee` `p` on(`u`.`Employee_ID` = `p`.`Employee_ID`)
                                    WHERE u.User_ID = "' . $user . '" group by u.User_ID');
        $row = $this->db->assoc($query);
        return $row;
    }

    public static function getUserDetails() {
        $user = new User;
        $data = $user->getUser();
        return $data;
    }

    public function getUserPreference() {
        $query = $this->db->query('SELECT * FROM tbl_common_user_preferences WHERE User_ID = "' . $this->info[0] . '" ');
        $result = $this->db->rowsno($query);
        if ($result != 0)
            return true;
        return false;
    }

    public function setUserPreference() {
        $User = Session::getUserID();
        $this->db->prepare_Stmt(' UPDATE  
                   tbl_common_user_preferences 
                SET Language_Code = ?
                WHERE User_ID = ?', [&$this->info['Lang'], &$User]);
        $error = $this->db->error();
        return $error;
    }

    public function getUserLanguage($User_ID) {
        $query = $this->db->query('SELECT Language_Code FROM tbl_common_user_preferences WHERE User_ID = "' . $User_ID . '" ');
        $result = $this->db->assoc($query);
        return $result['Language_Code'];
    }

    public function getlanguege($user) {
        $data = array();
        $query = $this->db->query('SELECT * FROM tbl_common_user_preferences WHERE User_ID = "' . $user . '" ');
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function setAccountsToUser() {
        $cash_drawer_id = isset($this->info['cash_drawer_id']) ? $this->info['cash_drawer_id'] : NULL;
        $this->info['job_id'] = !empty($this->info['job_id']) ? $this->info['job_id'] : NULL;
        $this->info['pos_cat_id'] = !empty($this->info['pos_cat_id']) ? $this->info['pos_cat_id'] : NULL;
        $data = [
            &$this->info['User_ID'], &$this->info['Cash_Account'], &$this->info['Cheque_Account'],
            &$this->info['purchase_account'], &$this->info['sales_account'], &$this->info['discount_account'],
            &$this->info['purchase_shipping'], &$this->info['default_class'], &$this->info['default_depot'],
            &$this->info['user_prefix'], &$this->info['sales_shipping'], &$this->info['deposite_account'],
            &$this->info['withdrawal_Account'], &$this->info['Other_Account'], &$this->info['edit_price'],
            &$this->info['default_branch'], &$this->info['max_discount'], &$cash_drawer_id, &$this->info['job_id'],
            &$this->info['pos_cat_id'], &$this->info['Cash_Account'], &$this->info['Cheque_Account'],
            &$this->info['purchase_account'], &$this->info['sales_account'], &$this->info['discount_account'],
            &$this->info['purchase_shipping'], &$this->info['default_class'], &$this->info['default_depot'],
            &$this->info['sales_shipping'], &$this->info['deposite_account'], &$this->info['withdrawal_Account'],
            &$this->info['Other_Account'], &$this->info['edit_price'], &$this->info['user_prefix'],
            &$this->info['default_branch'], &$this->info['max_discount'], &$cash_drawer_id,
            &$this->info['job_id'], &$this->info['pos_cat_id']
        ];
        $this->db->prepare_Stmt("INSERT INTO tbl_users_accounting_accounts
         (User_ID,cash_Account_id,cheque_account_id,purchase_account_id,sales_account_id,discount_account,
          pur_ship_account_id,class_id,depot_id,serial_prefix,sales_ship_account_id,deposit_account_id,
          withdrawal_account_id,other_account_id,edit_price,branch_id,max_discount,cash_drawer_id,job_id,
          pos_cat_id)VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
          on duplicate key update cash_account_id = ?,cheque_account_id = ?,purchase_account_id = ?,
          sales_account_id = ?,discount_account=?,pur_ship_account_id = ?,class_id = ?,depot_id = ?,
          sales_ship_account_id = ?,deposit_account_id = ?,withdrawal_account_id = ?,
           other_account_id = ?,edit_price = ?,serial_prefix = ?, branch_id = ?,max_discount = ?,
          cash_drawer_id = ?,job_id = ?,pos_cat_id=?", $data);
        $user_acc_id = $this->db->id();
        $check = Organization::getPrefixIfUsed($this->info['user_prefix'], $user_acc_id, 0);
        $error = $this->db->error();
        return [$error, $check];
    }

    public function getUserAccounts($id) {
        $row = array();
        $query = $this->db->query('select a.*,c.*,b.*,concat(w.Depot_Code," - ",w.Depot_Name) as Depot_Name,
        cash_drawer.cash_account_id as cash_drawer_account_id,cash_drawer.cash_drawer_title,users.User_Name,
        jobs.job_name,cats.Cat_Name from tbl_users_accounting_accounts a
                    left join tbl_pricing_class c on c.Class_ID = a.class_id left 
                    join tbl_stock_depot_warehouse w on w.Depot_ID = a.depot_id 
                    left join tbl_organization_branches b on b.branch_id = a.branch_id 
                    left join tbl_cash_drawer_accounts cash_drawer on cash_drawer.cash_drawer_id = a.cash_drawer_id
                    left join tbl_common_users users on users.User_ID = a.User_ID
                    left join tbl_accounting_jobs jobs on jobs.job_id = a.job_id
                    left join tbl_product_cat cats on cats.Cat_ID = a.pos_cat_id
                    where a.User_ID = "' . $id . '"');
        //        $query = $this->db->query('select * from tbl_users_accounting_accounts where User_ID = "'.$id.'"');
        $row = $this->db->assoc($query);
        if (!empty($row)) {
            $row['c_name'] = JV::getAccountNameByID($row['cash_account_id']);
            $row['cheque_name'] = JV::getAccountNameByID($row['cheque_account_id']);
            $row['p_name'] = JV::getAccountNameByID($row['purchase_account_id']);
            $row['s_name'] = JV::getAccountNameByID($row['sales_account_id']);
            $row['discount_name'] = JV::getAccountNameByID($row['discount_account']);
            $row['ps_name'] = JV::getAccountNameByID($row['pur_ship_account_id']);
            $row['ss_name'] = JV::getAccountNameByID($row['sales_ship_account_id']);
            $row['cd_name'] = JV::getAccountNameByID($row['cash_drawer_account_id']);
            $row['dep_name'] = JV::getAccountNameByID($row['deposit_account_id']);
            $row['with_name'] = JV::getAccountNameByID($row['withdrawal_account_id']);
            $row['other_name'] = JV::getAccountNameByID($row['other_account_id']);
        }
        return $row;
    }

    public static function getUserAccDetails($id) {
        $user = new User();
        $data = $user->getUserAccounts($id);
        return $data;
    }

    public function assignUserCustomer() {
        if ($this->info['is_checked'] == 1) {
            $this->db->prepare_Stmt('insert into tbl_user_customers
                                (user_id,customer_id) 
                                values(?,?)', [&$this->info['uid'], &$this->info['cid']]);
        } else {
            $this->db->prepare_Stmt('delete from tbl_user_customers
                                where user_id = ? and customer_id = ? ', [&$this->info['uid'], &$this->info['cid']]);
        }
        $error = $this->db->error();
        return $error;
    }

    public static function get_username_by_id($id) {
        $db = DBConnectorFactory::loadDriver();
        $query = $db->query('select User_Name from tbl_common_users where User_ID = "' . $id . '"');
        $username = $db->assoc($query)['User_Name'];
        return $username;
    }

    public function get_app_users() {
        $data = array();
        $query = $this->db->query('SELECT `User_ID`, `User_Name` FROM `tbl_common_users` WHERE `is_app`=1');
        while ($row = $this->db->assoc($query)) {
            $data[$row['User_ID']] = $row['User_Name'];
        }
        return $data;
    }

    public static function getDepartmentAdmins($dep_id = null, $is_all_parents = true) {
        $db = DBConnectorFactory::loadDriver();
        //
        $departments_arr = array();
        $users_arr = array();
        $dep_user_admin_arr = array();
        $data = array();
        //
        $query = $db->prepare_Stmt('SELECT * FROM `view_users` u WHERE u.is_admin = 1');
        while ($row = $db->assoc($query)) {
            $users_arr[$row['User_ID']] = $row;
            $dep_user_admin_arr[$row['Branch_ID']][$row['User_ID']] = $row['User_ID'];
        }
        //if do not need department id then we need all admins
        if (is_null($dep_id))
            return $users_arr;
        //
        $query = $db->prepare_Stmt('SELECT d.department_id, d.parent_id FROM `tbl_department` d');
        while ($row = $db->assoc($query))
            $departments_arr[$row['department_id']] = (empty($row['parent_id']) ? null : $row['parent_id']);
        //
        if (!empty($dep_user_admin_arr[$dep_id])) {
            foreach ($dep_user_admin_arr[$dep_id] as $user_id)
                $data[$user_id] = $users_arr[$user_id];
        }
        if ($is_all_parents) {
            $prnt_dep_id = $dep_id;
            while (!is_null($prnt_dep_id = $departments_arr[$prnt_dep_id])) {
                if (!empty($dep_user_admin_arr[$prnt_dep_id])) {
                    foreach ($dep_user_admin_arr[$prnt_dep_id] as $user_id)
                        $data[$user_id] = $users_arr[$user_id];
                }
            }
        }
        //
        //                echo"<pre>";die(print_r($data));
        return $data;
    }

    public function UserSearch($term = null, $page, $limit) {
        $data = array();
        $data['is_more'] = false;
        $data['data'] = array();
        $cond = '';
        if (!is_null($term)) {
            $cond .= ' AND ( Employee_Name LIKE "%' . $term . '%" ) ';
        }
        $query = $this->db->query('select 
                                    User_ID as id,
                                    Employee_Name as text
                                from 
                                    view_users
                                    where 1=1
                                ' . $cond . '
                                limit ' . ($page * $limit) . ',' . ($limit + 1));
        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 groupSearch($term = null, $page, $limit) {
        $data = array();
        $data['is_more'] = false;
        $data['data'] = array();
        $cond = '';
        $data_filter = array();
        if (!is_null($term)) {
            $cond .= ' AND ( group_name LIKE ? ) ';
            $data_filter[] = "%" . $term . "%";
        }
        $query = $this->db->prepare_Stmt('select 
                                    group_id as id,
                                    group_name as text
                                from 
                                    tbl_group_user
                                    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 CreateUserGroup() {
        $data = [
            &$this->info['group_name'],
            &$this->info['user_ids'],
            &$this->info[0]
        ];
        $this->db->prepare_Stmt("
                                INSERT
                                INTO
                                    tbl_group_user
                                (
                                    group_name,
                                    user_ids,
                                    logging_id
                                )
                                    VALUES
                                (
                                    ?,?,?
                                )
                            ", $data);
        $error = $this->db->error();
        return $error;
    }

    public function updateUserGroup() {
        $data = [
            &$this->info['group_name'],
            &$this->info['user_ids'],
            &$this->info[0],
            &$this->info['group_id']
        ];
        $this->db->prepare_Stmt("Update tbl_group_user 
                                set 
                                   group_name = ?,
                                   user_ids = ?,
                                   logging_id = ?
                                 where group_id = ?
                            ", $data);
        $error = $this->db->error();
        return $error;
    }

    public function deleteUserGroup($grp_id) {
        $this->db->prepare_Stmt(" DELETE FROM `tbl_group_user` WHERE group_id = ? ", [&$grp_id]);
        $error = $this->db->error();
        return $error;
    }

    public function getUserGroupInfo($grp_id) {
        $group_data = array();
        $query = $this->db->prepare_Stmt("Select u.* FROM `tbl_group_user` u  WHERE u.group_id = ?  ", [&$grp_id]);
        while ($row = $this->db->assoc($query)) {
            $group_data[] = $row;
            $user_name = $this->db->prepare_Stmt('select User_ID as id,Employee_Name as text from view_users where FIND_IN_SET(User_ID,?)', [&$row['user_ids']]);
            while ($row_user = $this->db->assoc($user_name)) {
                $group_data['data'][] = $row_user;
            }
        }
        return $group_data;
    }

    public function getUserNames() {
        $all_users = '';
        $query = $this->db->query('select * from tbl_common_users where User_ID IN (' . $this->info['users'] . ')');
        while ($row = $this->db->assoc($query)) {
            $all_users .= $row['User_Name'] . ',';
        }
        $all_users = substr($all_users, 0, -1);
        return $all_users;
    }

    public function getUsersWithCashAccounts($term = null, $page, $limit) {
        $data = array();
        $data['is_more'] = false;
        $data['data'] = array();
        $cond = '';
        $data_filter = array();
        if (!is_null($term)) {
            $cond .= ' where User_Name LIKE ? ';
            $data_filter[] = "%" . $term . "%";
        }
        $query = $this->db->prepare_Stmt('select 
                                    users.User_ID as id,
                                    users.User_Name as text
                                from 
                                    tbl_users_accounting_accounts accounts
                                inner join tbl_common_users users using(User_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 viewUnOrWorkingDays($val = null) {
        $condition = '';
        $data = array();
        if (!is_null($val))
            $condition .= 'WHERE Is_Checked = "' . $val . '"';
        $query = $this->db->query('SELECT
                                            *
                                    FROM 
                                            tbl_hrm_working_days
				' . $condition);
        while ($row = $this->db->assoc($query)) {
            $row['Working_Day_Name'] = CommonLanguage::getMessage($row['Working_Day_Name']);
            $data[] = $row;
        }

        return $data;
    }
    // 
    public function viewUserShiftsDetails($id) {
        $data = array();
        $query = $this->db->query('SELECT * From tbl_user_shifts Where user_shifts_id = "' . $id . '"');
        $row = $this->db->assoc($query);
        $data = $row;
        $data['Work_Day'] = json_decode($row['Working_Day_ID'], true);
        return $data;
    }

    public function addShifts() {
        $this->db->query('START TRANSACTION');
        $logged_id = Session::getUserID();
        $days = json_encode($this->info['Shift_Data']);
        $shiftDetails = [&$this->info["Shift_Name"], &$days, &$logged_id];
        $this->db->prepare_Stmt('INSERT INTO tbl_user_shifts
                                            (
                                                Shift_Name, 
                                                Working_Day_ID,
                                                Logged_ID
                                            )
                                                VALUES
                                            (?,?,?)
                    ', $shiftDetails);
        
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }

    public function updateShift($shift_id) {
        $this->db->query('START TRANSACTION');

        $logged_id = Session::getUserID();
        //        $days = json_encode($this->info['Shift_Data']);
        $days =json_encode($this->info['Shift_Data']) ;

        $shiftDetails = [&$this->info["Shift_Name"], &$days, &$logged_id, &$shift_id];
        $this->db->prepare_Stmt(" UPDATE  tbl_user_shifts SET
                                            Shift_Name = ?,
                                            Working_Day_ID= ?,                       
                                            Logged_ID= ?
                                        WHERE 
                                        user_shifts_id = ?

                                            ", $shiftDetails);

          
         
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }
    public function deleteShift($shift_id) {
        $this->db->query('START TRANSACTION');
        $this->db->prepare_Stmt('Update  tbl_common_users set u_user_shifts_id=0  WHERE u_user_shifts_id = ?', [&$shift_id]);
        $this->db->prepare_Stmt('Delete from tbl_user_shifts WHERE user_shifts_id = ?', [&$shift_id]);

        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }
    public function submitUser() {
        $this->db->query('START TRANSACTION');
 
        $secret_code = NULL;
        $logged_user = smc_session()['user_logged_id'];
        $employee_id = !empty($this->info['Employee_ID']) ? $this->info['Employee_ID'] : NULL;
        if (smc_session()['default_setting']['activate_smart_diner'] == 1 && !empty($this->info['secret_code'])) {
            $secret_code = md5(sha1(md5($this->info['secret_code'])));
        }
        $user_id = $this->info['user_id'];
        $this->info['login_at'] = empty($this->info['login_at']) ? 0 : $this->info['login_at'];
        if ($user_id == 0) {
            $pass = md5(sha1(md5($this->info['User_Password'])));
            $data = [&$this->info['User_Name'],
                     &$pass,
                     &$this->info['Role_ID'],
                     &$this->info['control_role_id'],
                     &$this->info['landing_id'],
                     &$employee_id,
                     &$logged_user,
                     &$this->info['is_app_user'],
                     &$this->info['is_admin_user'],
                     &$this->info['limited_access'],
                     &$this->info['User_Full_Name'],
                     &$this->info['user_email'],
                     &$this->info['user_mobile_phone'],
                     &$this->info['login_at'],
                     &$this->info['login_device'],
                     &$this->info['json_data'],
                     &$this->info['stop_multi_login'],
                     &$secret_code,
                     &$this->info['user_shifts_id'],
                     &$this->info['limit_customer_invoice'],
                     &$this->info['is_actual']
                    ];
            $this->db->prepare_Stmt('INSERT INTO tbl_common_users( User_Name,
                                                                User_Password,
                                                                Role_ID,
                                                                control_role_id,
                                                                landing_id,
                                                                Employee_ID,
                                                                Logged_ID,
                                                                is_app,
                                                                is_admin,
                                                                limited_access,
                                                                User_Full_Name,
                                                                user_email,
                                                                user_mobile_phone,
                                                                login_at,
                                                                login_device,json_data,stop_multi_login,
                                                                secret_code,
                                                                u_user_shifts_id,
                                                                limit_customer_invoice,
                                                                is_actual
                                                               )
                                                        VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', $data);
            //             $error = $this->db->error();
            //            die(print_r($error));
            $user_id = $this->db->id();
            $this->db->prepare_Stmt('INSERT INTO 
                                                tbl_common_user_preferences 
                                                (
                                                    User_ID,
                                                    Language_Code
                                                )
                                                VALUES
                                                (?,"en")', [&$user_id]);
        } else {
            $data = [&$this->info['User_Name'],
                     &$this->info['Role_ID'],
                     &$this->info['control_role_id'],
                     &$this->info['landing_id'],
                     &$employee_id,
                     &$this->info['is_app_user'],
                     &$this->info['is_admin_user'],
                     &$this->info['limited_access'],
                     &$this->info['User_Full_Name'],
                     &$this->info['user_email'],
                     &$this->info['user_mobile_phone'],
                     &$this->info['login_at'],
                     &$this->info['login_device'],
                     &$this->info['json_data'],
                     &$this->info['stop_multi_login'],
                     &$this->info['user_shifts_id'],
                     &$this->info['limit_customer_invoice'],
                     &$this->info['is_actual'],

                     &$user_id
                    ];
            $this->db->prepare_Stmt('update tbl_common_users set User_Name = ?,
                                                                Role_ID = ?,
                                                                control_role_id = ?,
                                                                landing_id = ?,
                                                                Employee_ID = ?,
                                                                is_app = ? ,
                                                                is_admin =?,
                                                                limited_access =?,
                                                                User_Full_Name =?,
                                                                user_email =?,
                                                                user_mobile_phone =?,login_at=?,login_device = ?,json_data=?,stop_multi_login=?,u_user_shifts_id=?,limit_customer_invoice=?,
                                                                is_actual = ?
                                                                where User_ID = ?', $data);
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query("COMMIT");
            return [$user_id];
        } else {
            $this->db->query("ROLLBACK");
            return array_unique($error);
        }
    }

    public function submitPassword() {
        $user_id = $this->info['current_user_id'] == 0 ? smc_session()['user_logged_id'] : $this->info['current_user_id'];
        $password = md5(sha1(md5($this->info['current_user_password'])));
        $this->db->prepare_Stmt('UPDATE tbl_common_users
                                     SET
                                            User_Password =?
                                     WHERE 
                                            User_ID =?
                                         ', [&$password, &$user_id]);
        $error = $this->db->error();
        return $error;
    }

    public function submitTrustedComputer() {
        $entry_id = $this->info['entry_id'];
        $random = CommonFunctions::generateRandomStringNumber(100);
        CommonFunctions::set_trusted_computer_cookie($random);
        if (empty($entry_id)) {
            $this->db->prepare_Stmt('Insert into  tbl_computer_trusted (computer_name,computer_cookies) Values (?,?)
                                         ', [&$this->info['computer_name'], &$random]);
        } else {
            $this->db->prepare_Stmt('update tbl_computer_trusted set computer_name = ? where entry_id = ?
                                         ', [&$this->info['computer_name'], &$entry_id]);
        }
        $error = $this->db->error();
        return $error;
    }

    public function checkTrustedExist() {
        if (empty($_COOKIE[DB_NAME . '_computer']))
            return 0;
        $trusted_query = $this->db->prepare_Stmt('select entry_id from tbl_computer_trusted where computer_cookies = ?', [&$_COOKIE[DB_NAME . '_computer']]);
        $rows_no = $this->db->rowsno($trusted_query);
        return $rows_no;
    }

    public function submitSecretCode() {
        $user_id = $this->info['secret_code_user_id'] == 0 ? smc_session()['user_logged_id'] : $this->info['secret_code_user_id'];
        $secret_code = md5(sha1(md5($this->info['current_secret_code'])));
        $this->db->prepare_Stmt('UPDATE tbl_common_users
                                     SET
                                            secret_code =?
                                     WHERE 
                                            User_ID =?
                                         ', [&$secret_code, &$user_id]);
        $error = $this->db->error();
        return $error;
    }

    public function empty_unique_id($id) {
        $this->db->prepare_Stmt('update tbl_common_users set app_unique_id = "" where User_ID = "' . $id . '"');
        $error = $this->db->error();
        return $error;
    }

    public function deleteTrustedComputer() {
        $this->db->prepare_Stmt('delete from tbl_computer_trusted where entry_id = ?', [&$this->info['entry_id']]);
        $error = $this->db->error();
        return $error;
    }

    public function submitDefaultSetting() {
        $this->db->prepare_Stmt('update tbl_default_settings set setting_value = ? where setting_id =?', [&$this->info['setting_value'], &$this->info['setting_id']]);
        $error = $this->db->error();
        return $error;
    }

    public function submitAccountSetting() {
        $this->db->prepare_Stmt('update tbl_account_setting set acc_setting_value = ? where id =?', [&$this->info['setting_value'], &$this->info['setting_id']]);
        $error = $this->db->error();
        return $error;
    }

    public static function get_users_with_acc() {
        $db = DBConnectorFactory::loadDriver();
        $data = array();
        $query = $db->query('select 
                                    users.User_ID,
                                    users.User_Name,
                                    accounts.cash_account_id
                                from 
                                    tbl_users_accounting_accounts accounts
                                inner join tbl_common_users users using(User_ID) 
                                where cash_account_id is not null ');
        while ($row = $db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function add_edit_web_api_user() {
        if ($this->info['is_edit'] == 0) {
            $data = [&$this->info['api_title'], &$this->info['api_user_id'], &$this->info['class_id']
                     , &$this->info['depot_id'], &$this->info['other_data']];
            $this->db->prepare_Stmt('INSERT INTO `tbl_web_api_users`(title,`app_user_id`,  `class_id`,
                `depot_id`,  `other_data`) values(?,?,?,?,?)', $data);
        } else {
            $data = [&$this->info['api_title'], &$this->info['api_user_id'], &$this->info['class_id']
                     , &$this->info['depot_id'], &$this->info['other_data'], &$this->info['user_id']];
            $this->db->prepare_Stmt('update `tbl_web_api_users` set title=?,`app_user_id`=?,  `class_id`=?,
                `depot_id`=?,  `other_data`=? where user_id=?', $data);
        }
        $error = $this->db->error();
        return $error;
    }

    public function delete_web_api_user($user_id) {
        $this->db->prepare_Stmt('delete from tbl_web_api_users where user_id=?', [&$user_id]);
        $error = $this->db->error();
        return $error;
    }

    public function get_web_api_user($user_id) {
        $data = array();
        $query = $this->db->query('select * from web_api_users where user_id = "' . $user_id . '"');
        $row = $this->db->assoc($query);
        if (!empty($row))
            $data = $row;
        return $data;
    }

    public function enable_disable_api_user() {
        $status_id = $this->info['status_id'] == 0 ? 1 : 0;
        $this->db->prepare_Stmt('update tbl_web_api_users set status_id = ? where user_id=?', [&$status_id, &$this->info['user_id']]);
        $error = $this->db->error();
        return $error;
    }

    public function notification_type_search($term = null, $page, $limit){
        $data = array();
        $data['is_more'] = false;
        $data['data'] = array();
        $cond = '';
        $data_filter = array();
        if (!is_null($term)) {
            $cond .= ' AND ( notification_type_name LIKE ? ) ';
            $data_filter[] = "%" . $term . "%";
        }
        $query = $this->db->prepare_Stmt('select 
                                    notification_type_id as id,
                                    notification_type_name as text
                                from 
                                    tbl_notification_type
                                    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 get_system_template() {
        $data = array();
        $template_id= $this->info['template_id'];
        $query = $this->db->query('SELECT 
                                        *
									FROM 
                                    tbl_print_template
                                    WHERE
                                    template_id = '.$template_id.' ');
        while ($row = $this->db->assoc($query))
            $data[] = $row;
        return $data;
    }


    
    public function update_system_template($template_id,$template_text) {
        $this->db->query('START TRANSACTION');


        $TemplateDetails = [  &$template_text ,&$template_id];
        $this->db->prepare_Stmt(" UPDATE  tbl_print_template
                                  SET
                                          template_text = ?
                                  WHERE 
                                           template_id = ?

                                            ", $TemplateDetails);

          
         
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }
    public function empty_system($empty_type,$invoice_id) {
        $this->db->query('START TRANSACTION');

        //0 = full_empty
        if($empty_type == 0){
            $this->db->prepare_Stmt(" 
            set foreign_key_checks = 0;
            TRUNCATE TABLE `tbl_stock_product_location`;
            TRUNCATE TABLE `tbl_stock_product_transactions`;
            TRUNCATE TABLE `tbl_stock_products_costs`;
            UPDATE `tbl_stock_products` SET `Product_Quantity` = 0 ;
            TRUNCATE TABLE `tbl_invoice_products_details`;
            TRUNCATE TABLE `tbl_invoices`;
            TRUNCATE TABLE `tbl_invoice_details_tags`;
            TRUNCATE TABLE `tbl_accounting_transactions`;
            TRUNCATE TABLE `tbl_accounting_return_invoices`;
            TRUNCATE TABLE `tbl_accounting_return_items`;
            TRUNCATE TABLE `tbl_payment_receipt`;
            TRUNCATE TABLE `tbl_payment_receipt_details`;
            TRUNCATE TABLE `tbl_accounting_bank_deposit_withdrawal`;
            TRUNCATE TABLE `tbl_accounting_expenses`;
            TRUNCATE TABLE `tbl_accounting_expenses_details`;
            TRUNCATE TABLE `tbl_inventory_purchases`;
            TRUNCATE TABLE `tbl_inventory_purchase_items`;
            TRUNCATE TABLE `tbl_purchase_order`;
            TRUNCATE TABLE `tbl_purchase_order_details`;
            TRUNCATE TABLE `tbl_exchange`;
            TRUNCATE `tbl_accounting_debit_credit_note`;
            TRUNCATE `tbl_accounting_transfer_jv`;
            TRUNCATE `tbl_app_file_main_data`;
            TRUNCATE `tbl_app_temp_adjustments`;
            TRUNCATE `tbl_app_temp_main_adj`;
            TRUNCATE `tbl_invoice_product_criteria`;
            TRUNCATE `tbl_stock_request`;
            TRUNCATE `tbl_stock_request_details`;
            TRUNCATE `tbl_stock_damaged_reasons`;
            DELETE FROM tbl_customer_shipping_address WHERE Customer_ID != 0;
            DELETE FROM tbl_chart_of_accounts where main_type = 'cust' and main_id != 0 ;
            DELETE FROM tbl_customers WHERE Customer_ID != 0;
            
            DELETE FROM tbl_chart_of_accounts   where main_type = 'supp' and main_id != 0;
            DELETE FROM tbl_stock_suppliers WHERE  Supplier_ID != 0;
            
            
            delete from tbl_accounting_transactions where  main_type='inv';
            DELETE criteria FROM  tbl_invoice_product_criteria criteria inner join tbl_invoice_products_details details on details.Entery_ID = criteria.Entery_ID inner join tbl_invoices invoices on invoices.Invoice_ID_Auto = details.Invoice_ID_Auto ;
            DELETE add_ons FROM  tbl_invoice_product_add_ons add_ons inner join tbl_invoice_products_details details on details.Entery_ID = add_ons.Entery_ID  inner join tbl_invoices invoices on invoices.Invoice_ID_Auto = details.Invoice_ID_Auto ;
            delete from tbl_invoice_products_details;
            delete from tbl_invoice_shipping;
            delete from tbl_invoices;
            delete from tbl_invoice_payments WHERE type in ('pay','smjv','deb');
            
            Delete from tbl_uom_conversions;
            Delete from tbl_item_price_by_classes;
            Delete from tbl_products_link;
            Delete from tbl_product_criteria;
            Delete from tbl_product_shelf;
            Delete from tbl_product_keys;
            Delete from tbl_product_add_ons;
            Delete from tbl_stock_products_parts;
            Delete from tbl_stock_products;
            Delete from tbl_stock_product_transactions;
            Delete from tbl_stock_products_costs;
            Delete from tbl_stock_product_location;
            
            Delete from tbl_stock_uom;
            Delete from tbl_stock_categories;
            Delete from tbl_product_cat;
            
            
            Delete FROM `tbl_pos_customers` WHERE `customer_id`!='1217';
            
            Delete from tbl_drivers;
            TRUNCATE tbl_main_log;
            TRUNCATE tbl_item_price_by_classes_log;
            
            
            
            delete from tbl_hrm_payroll ;
            delete from tbl_accounting_transactions where main_type='prl' ;
            

            delete from tbl_sales_purchase ;
            set foreign_key_checks = 1;
 ");
        }
        //3 = product_empty
        else if($empty_type == 3){

            $this->db->prepare_Stmt("  
            Delete from tbl_uom_conversions;
            Delete from tbl_item_price_by_classes;
            Delete from tbl_products_link;
            Delete from tbl_product_criteria;
            Delete from tbl_product_shelf;
            Delete from tbl_product_keys;
            Delete from tbl_product_add_ons;
            Delete from tbl_stock_products_parts;
            Delete from tbl_stock_products;
            Delete from tbl_stock_product_transactions;
            Delete from tbl_stock_products_costs;
            Delete from tbl_stock_product_location;
            Delete from tbl_stock_uom;
            Delete from tbl_stock_categories;
            Delete from tbl_product_cat;
           ");
        }
        //1 = sales_empty
        else if($empty_type == 1){
            $today = Date::getToday();
            $option_arr = array();
            $query = $this->db->query('select p.invoice_detail_id,p.product_id,p.UOM_ID as uom_id,
                p.option_quantity as qty
                from tbl_invoice_product_options p
                join tbl_invoice_products_details d on d.Entery_ID = p.invoice_detail_id 
                ');
            while ($row = $this->db->assoc($query)) {
                $option_arr[$row['invoice_detail_id']][] = $row;
            }
            $depot_id = 0;
    
            $query = $this->db->query("SELECT 
                                            p.Product_ID,
                                            p.Invoice_ID_Auto,
                                            p.Item_Quantity_Current as Current_Quantity,
                                            p.Product_Quantity  as Quantity,
                                            p.UOM_ID,
                                            p.unit_cost,p.Product_Net_Price,
                                            i.Invoice_Currency AS Currency_ID,
                                            p.depot_id as Depot_ID,
                                            i.current_rate,
                                            i.date_time,
                                            i.invoice_prefix, i.Customer_ID,
                                            i.Invoice_ID,i.created_logged_id,i.date_created,p.expiry_date,
                                            i.invoice_app_id,i.app_branch_id,i.app_user_id,p.lot_nb,p.serial_nb,pr.is_service,p.Entery_ID
                                        FROM 
                                            tbl_invoice_products_details p
                                        join tbl_invoices i on i.Invoice_ID_Auto = p.Invoice_ID_Auto
                                        join tbl_stock_products pr on pr.Product_ID = p.Product_ID
                                        WHERE 
                                            (isnull(p.Delivery_Report_ID) or p.Delivery_Report_ID = 0)
                    ");
            $row_nb = $this->db->rowsno($query);
            if ($row_nb > 0) {
                while ($row = $this->db->assoc($query)) {
                    $depot_id = $row['Depot_ID'];
                    //            $old_date_time = $row['date_time'];
                    //                $sub_cost = $row['link_id'] == 0 ? $row['unit_cost'] : CommonBusiness::viewCurrentProductCost($row['sub_product_id']);
                    $expiry_date = !empty($row['expiry_date']) ? $row['expiry_date'] : null;
                    $lot_nb = !empty($row['lot_nb']) ? $row['lot_nb'] : '';
                    $serial_nb = !empty($row['serial_nb']) ? $row['serial_nb'] : '';
                    $costdata = [
                        "Product_ID" => '', //$row['sub_product_id'],
                        "Quantity" => '', //$row['Quantity'],
                        "Unit_Cost" => '', //$sub_cost,
                        "unit_price" => $row['Product_Net_Price'],
                        "Currency_ID" => $row['Currency_ID'],
                        "date_time" => $row['date_time'],
                        "date_time_now" => date("Y-m-d H:i:s"),
                        "current_rate" => $row['current_rate'],
                        "depot_id" => $row['Depot_ID'],
                        "trans_date" => $today,
                        "current_quantity" => '', //$row['Current_Quantity'],
                        "uom_id" => '', //$row['UOM_ID'],
                        "is_actual" => 0,
                        "expiry_date" => $expiry_date,
                        "lot_nb" => $lot_nb,
                        "serial_nb" => $serial_nb,
                        "main_id" => $row['Invoice_ID_Auto'],
                        "is_service" => $row['is_service'],
                        "main_nb" => $row['invoice_prefix'] . $row['Invoice_ID'],
                        "customer_id" => $row['Customer_ID']
                    ];
                    $option_arr_temp = !empty($option_arr[$row['Entery_ID']]) ? $option_arr[$row['Entery_ID']] : array();
    
                    $product_links = CommonBusiness::get_product_links($row['Product_ID'], $row['UOM_ID'], $row['Quantity'], $row['Current_Quantity'], '-', 0, $option_arr_temp);
                    foreach ($product_links as $link) {
                        $costdata["Quantity"] = $link['Quantity'];
                        $costdata["current_quantity"] = $link['current_quantity'];
                        $costdata["Product_ID"] = $link['Product_ID'];
                        $costdata["uom_id"] = $link['uom_id'];
                        $costdata['is_inverse'] = $link['is_oprtn_inverse'];
                        if (!empty($link['is_main_prdct']))
                            $costdata['Unit_Cost'] = $row['unit_cost'];
                        else {
                            $cost = CommonBusiness::viewCurrentProductCost($link['Product_ID']);
                            $costdata['Unit_Cost'] = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies(smc_session()['local_currency'], $row['Currency_ID'], $cost);
                        }
                        //                    $cost = CommonBusiness::viewCurrentProductCost($link['Product_ID']);
                        //                    $costdata['Unit_Cost'] = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies(smc_session()['local_currency'], $row['Currency_ID'], $cost);
                        //                    echo"<pre>";print_r($costdata);
                        $var = new Invoice();
                        $var->updateLinkQuantities($costdata, "delete_invoice", ($link['operation'] == '+' ? '-' : '+'));
                    }
                    //                    die();
                    //                $cost = self::updateLinkQuantities($costdata, "edit_invoice_old", ($row['operation'] == '+' ? '-' : '+'));
                }
            }
    
            $this->db->prepare_Stmt(" 
            set foreign_key_checks = 0;
            delete from tbl_accounting_transactions where  main_type='inv';
            DELETE criteria FROM  tbl_invoice_product_criteria criteria inner join tbl_invoice_products_details details on details.Entery_ID = criteria.Entery_ID inner join tbl_invoices invoices on invoices.Invoice_ID_Auto = details.Invoice_ID_Auto ;
            DELETE add_ons FROM  tbl_invoice_product_add_ons add_ons inner join tbl_invoice_products_details details on details.Entery_ID = add_ons.Entery_ID  inner join tbl_invoices invoices on invoices.Invoice_ID_Auto = details.Invoice_ID_Auto ;
            delete from tbl_invoice_products_details;
            delete from tbl_invoice_shipping;
            delete from tbl_invoices;
            delete from tbl_invoice_payments  WHERE  type in ('rec','cmjv','cre')
            set foreign_key_checks = 1;
            ");

        }
        //2 = purchase_empty
        else if($empty_type == 2){
            $mydate = Date('Y-m-d H:i:s');
            $trans_type = 'delete_purchase';
            $query = $this->db->query("SELECT 
                                            p.Product_ID as Item_ID,
                                            p.Item_Quantity AS Quantity,
                                            p.Item_Quantity_Current AS Quantity_Current,
                                            p.Item_Price,
                                            p.Purchase_ID_Auto,
                                            p.UOM_ID,
                                            p.expiry_date,
                                            i.Currency_ID,
                                            p.depot_id as Depot_ID,
                                            i.date_time,
                                            i.current_rate,
                                            i.Purchase_ID,p.real_cost,i.created_logged_id,i.date_created,
                                            i.Purchase_Date,i.is_actual,p.lot_nb,p.serial_nb,pr.is_service,
                                            i.Supplier_ID
    
                                        FROM 
                                            tbl_inventory_purchase_items p
                                        LEFT JOIN 
                                            tbl_inventory_purchases i
                                            USING(Purchase_ID_Auto)
                                            join tbl_stock_products pr on pr.Product_ID = p.Product_ID
    
                                        WHERE 
                                             (isnull(p.Delivery_Report_ID) or p.Delivery_Report_ID = 0)
                    ");
            $old_date_time = $mydate;
            $old_date_created = $mydate;
            $old_created_logged_id = smc_session()['user_logged_id'];
            while ($row = $this->db->assoc($query)) {
                //            $pur_num = $row['Purchase_ID'];
                $old_date_created = $row['date_created'];
                $old_created_logged_id = $row['created_logged_id'];
                $old_date_time = $row['date_time'];
                $old_expiry_date = !empty($row['expiry_date']) ? $row['expiry_date'] : null;
                $old_lot_nb = !empty($row['lot_nb']) ? $row['lot_nb'] : '';
                $old_serial_nb = !empty($row['serial_nb']) ? $row['serial_nb'] : '';
    
                $costdata = [
                    "Product_ID" => $row['Item_ID'],
                    "Quantity" => $row['Quantity'],
                    "Unit_Cost" => $row['real_cost'],
                    "unit_price" => $row['Item_Price'],
                    "Currency_ID" => $row['Currency_ID'],
                    "date_time" => $row['date_time'],
                    "date_time_now" => $mydate,
                    "current_rate" => $row['current_rate'],
                    "depot_id" => $row['Depot_ID'],
                    "trans_date" => $row['Purchase_Date'],
                    "current_quantity" => $row['Quantity_Current'],
                    "uom_id" => $row['UOM_ID'],
                    "is_actual" => $row['is_actual'],
                    "expiry_date" => $old_expiry_date,
                    "lot_nb" => $old_lot_nb,
                    "serial_nb" => $old_serial_nb,
                    "main_id" => $row['Purchase_ID_Auto'],
                    "is_service" => $row['is_service'],
                    "main_nb" => $row['Purchase_ID'],
                    "supplier_id" => $row['Supplier_ID']
                ];
                CommonBusiness::addCostByType($costdata, $trans_type);
                if ($row['is_service'] != 1) {
                    $update_stock = [&$row['Quantity'], &$row['Item_ID']];
                    $this->db->prepare_Stmt("UPDATE 
                                        tbl_stock_products 
                                    SET Product_Quantity = Product_Quantity - ? where Product_ID = ?
                    ", $update_stock);
    
    
                    $update_dep = [&$row['Quantity'], &$row['Depot_ID'], &$row['Item_ID']];
                    $this->db->prepare_Stmt("UPDATE 
                                        tbl_stock_product_location 
                                        set Product_Depot_Quantity = Product_Depot_Quantity - ?
                                    WHERE Product_Depot_ID = ? and Location_Product_ID = ?
                    ", $update_dep);
                }
            }
    
            $this->db->prepare_Stmt(" 
            set foreign_key_checks = 0;
            delete from tbl_accounting_transactions where  main_type='pur';
            delete from tbl_inventory_purchase_items;
            delete from tbl_inventory_purchases;
            delete from tbl_invoice_payments WHERE type in ('pay','smjv','deb');
            set foreign_key_checks = 1;
            
            ");
        }
        //4 = delete_sales_invoice
        else if($empty_type == 4 && !empty($invoice_id)){
            $this->db->prepare_Stmt('delete from tbl_accounting_transactions where main_id= ? and main_type="inv"', [&$invoice_id]);

            $var = new Invoice();
            $var->edit_old_details_qty_cost($invoice_id, 1);
            
            $this->db->prepare_Stmt("DELETE criteria FROM  tbl_invoice_product_criteria criteria
            inner join tbl_invoice_products_details details on details.Entery_ID = criteria.Entery_ID
            inner join tbl_invoices invoices on invoices.Invoice_ID_Auto = details.Invoice_ID_Auto 
            where invoices.Invoice_ID_Auto = ?", [&$invoice_id]);
            $this->db->prepare_Stmt("DELETE add_ons FROM  tbl_invoice_product_add_ons add_ons
            inner join tbl_invoice_products_details details on details.Entery_ID = add_ons.Entery_ID
            inner join tbl_invoices invoices on invoices.Invoice_ID_Auto = details.Invoice_ID_Auto 
            where invoices.Invoice_ID_Auto = ?", [&$invoice_id]);
            $this->db->prepare_Stmt('delete from tbl_invoice_products_details where Invoice_ID_Auto= ? ', [&$invoice_id]);
            $this->db->prepare_Stmt('delete from tbl_invoice_shipping where Invoice_ID_Auto = ? ', [&$invoice_id]);
            $this->db->prepare_Stmt('delete from tbl_invoices  where Invoice_ID_Auto= ? ', [&$invoice_id]);

            $this->db->prepare_Stmt('delete from tbl_invoice_payments  WHERE inv_pur_id =? and type in ("rec","cmjv","cre") ', [&$invoice_id]);


        }
        //5 = delete_purchase_invoice
        else if($empty_type == 5 && !empty($invoice_id)){
            $this->db->prepare_Stmt('delete from tbl_accounting_transactions where main_id= ? and main_type="pur"', [&$invoice_id]);
            $var = new Purchase();
            $var->edit_old_details_qty_cost($invoice_id, 1);
            $this->db->prepare_Stmt('delete from tbl_inventory_purchase_items where Purchase_ID_Auto= ? ', [&$invoice_id]);
            $this->db->prepare_Stmt('delete from tbl_inventory_purchases  where Purchase_ID_Auto= ? ', [&$invoice_id]);
            $this->db->prepare_Stmt('delete from tbl_invoice_payments WHERE inv_pur_id =? and type in ("pay","smjv","deb") ', [&$invoice_id]);



        }


          
         
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }
    public function update_system_expiry_date($expiry_date) {
        $update = [&$expiry_date];
        $this->db->prepare_Stmt("UPDATE tbl_common_expiry_date  set Expiry_Date = ? ", $update);
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }
    public function get_system_expiry_date() {
        $data = array();
        $query = $this->db->query('select * from tbl_common_expiry_date ');
        $row = $this->db->assoc($query);
        if (!empty($row))
            $data = $row;
        return $data['Expiry_Date'];
    }

    public function add_update_system_note($system_version,$system_note,$select_version) {
        if ($select_version == '') {
            $data = [&$system_version, &$system_note];
            $this->db->prepare_Stmt('INSERT INTO `tbl_system_note`(`system_version`,`system_note`) values(?,?)', $data);
        } else {
            $data = [&$system_version, &$system_note ,&$select_version];
            $this->db->prepare_Stmt('update `tbl_system_note` set `system_version`=?,`system_note`=? where `note_id`=?', $data);
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }
    public function get_system_version() {
        $data = array();
        $select_version= $this->info['select_version'];
        $query = $this->db->query('SELECT 
                                        *
									FROM 
                                        tbl_system_note
                                    WHERE
                                        note_id = '.$select_version.' ');
        while ($row = $this->db->assoc($query))
            $data[] = $row;
        return $data;
    }
    public function add_update_template_branches($template_key,$template_name,$template_text,$branch_id,$template_id = 0) {
        $status = 1;
        if(empty($branch_id) || !isset($branch_id)){
            $branch_id = smc_session()['user_accounts']['branch_id'];
        }
        if ($template_id == 0) {
            $data = [&$template_key, &$template_name, &$template_text, &$branch_id, &$status];
            $this->db->prepare_Stmt('INSERT INTO `tbl_print_template`(`template_key`,`template_name`,`template_text`,`branch_id`,`status`) values(?,?,?,?,?)', $data);
        } else {
            $data = [&$template_key, &$template_name, &$template_text, &$branch_id,&$template_id];
            $this->db->prepare_Stmt('update `tbl_print_template` set `template_key`=?,`template_name`=?,`template_text`=?,`branch_id`=? where `template_id`=?', $data);
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }
    public function getTemplates($id) {
        $data = array();
        $query = $this->db->query('SELECT * From tbl_print_template Where template_id = "' . $id . '"');
        $row = $this->db->assoc($query);
        $row['branch_name'] =  CommonBusiness::getBranchByBranchId($row['branch_id'])['branch_name'];
        $data = $row;
        return $data;
    }
    public function deleteTemplate() {

        $attribute_query = $this->db->query('SELECT count(*) as num_row From tbl_template_attribute Where template_id = "' . $this->info['template_id'] . '"');
        $attribute_row = $this->db->assoc($attribute_query)['num_row'];
        if($attribute_row > 0){
            return ['1'];
        }
        
        $this->db->prepare_Stmt('delete from tbl_print_template where template_id = ?', [&$this->info['template_id']]);
        
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }
    public function submitTemplateAttribute()
    {
        $type = $this->info['attribute_type'];
 
        $branch_id  =  $this->info['attr_branch_id'];
        if ($type == 2) {
            $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 = SITE_LINK.'uploads/'.'org_logo/' . $ser_images_name  ;
            $this->SaveUploadedPhotoNoLink("new_image_name", $uploaddir . $ser_images_name);
            $this->db->prepare_Stmt('Insert into  tbl_template_attribute (attribute_key,attribute_value,template_id,attribute_type,branch_id) Values (?,?,?,?,?)
            ', [&$this->info['attribute_key'], &$DB, &$this->info['entry_id'],&$type,&$branch_id]);
        } else {
            $this->db->prepare_Stmt('Insert into  tbl_template_attribute (attribute_key,attribute_value,template_id,attribute_type,branch_id) Values (?,?,?,?,?)
                                         ', [&$this->info['attribute_key'], &$this->info['attribute_value'], &$this->info['entry_id'],&$type,&$branch_id]);
        }
        $error = $this->db->error();
        return $error;
    }
    public function deleteAttributeTemplate() {
        $this->db->prepare_Stmt('delete from tbl_template_attribute where attribute_id = ?', [&$this->info['attribute_id']]);
        $error = $this->db->error();
        return $error;
    }


}
