<?php

class M_roll_over extends CI_Model {

    // deklarasi nama table
    public $table = "at_rollover";
    public $journal = "at_rovrjournal";
    public $branch = "am_branch";
    public $detail = "at_rovrdetail";
    public $department = "am_department";
    public $am_acc = "am_accountgl";
    public $period = "at_periodend";
    public $at_company_gl = "at_compaccountgl";
    public $jobcosting = "at_jobcosting";
    public $inventories = "at_inventories";

    public function get_all_data($branch, $company) {
        $this->db->select('at_rollover.ROvrNumber, at_rollover.ROvrDate, at_rollover.JCostNumber, at_rollover.ROvrTotalAmount, at_rollover.TransStatus, at_rollover.ROvrStatus');
        $this->db->from('at_rollover');

        $this->db->join('am_branch', 'am_branch.BranchCode = at_rollover.BranchCode', 'INNER');
        if ($this->session->userdata('jobtitle_status') != 0) {
            $this->db->where('am_branch.CompanyCode', $this->session->userdata('company'));
            $this->db->where('at_rollover.BranchCode', $this->session->userdata('branch'));
        }
        $this->db->order_by('at_rollover.ROvrNumber', 'asc');

        $query = $this->db->get();
        return $query->result();
    }

    //create nomor
    function get_all() {
        /* =========================lemparan parah dari custom.js untuk searching_data_transaksi===================== */
        $param = $this->input->post('param');
        /* ====================================end=================================================================== */

        /* =================================post dari find di kiri==================================================== */
        $Number = isset($param['txt_IDNumber']) ? $param['txt_IDNumber'] : '';
        $Desc = isset($param['txt_Description']) ? $param['txt_Description'] : '';

        $DateFrom = isset($param['txt_DateFrom']) ? $param['txt_DateFrom'] : '';
        $DateTo = isset($param['txt_DateTo']) ? $param['txt_DateTo'] : '';
        $TransStatus = isset($param['chk_TransStatus']) ? $param['chk_TransStatus'] : '';
        $BranchCode = isset($param['cmb_branch']) ? $param['cmb_branch'] : '';
        /* ============================================end============================================================ */

        /* ======================================scrol view 1-150======================================= */
        $page = ($this->input->post('page')) ? intval($this->input->post('page')) : 1;
        $rows = ($this->input->post('rows')) ? intval($this->input->post('rows')) : 150;

        $beginning = ($page - 1) * $rows;
        $end = $page * $rows;
        /* ============================================================================================== */

        /* ==========================================by session======================================================= */
        $sess_company = $this->session->userdata('company');
        $sess_branch = $this->session->userdata('branch');
        $sess_branch_status = $this->session->userdata('branch_status');
        /* =========================================================================================================== */

        $this->db->select('
			at_rollover.BranchCode as cpay_branch, am_accountgl.AccountName, at_rollover.ROvrNumber, at_rollover.JCostNumber, am_employee.EmployeeName as CreatedBy,
			at_rollover.ROvrDate, at_rollover.ROvrDesc, am_branch.BranchName, 
			at_rollover.JCostSubTotal, at_rollover.TransStatus'
        );
        $this->db->from($this->table);
        $this->db->join('am_branch', 'am_branch.BranchCode = at_rollover.BranchCode');
        $this->db->join('am_employee', 'am_employee.EmployeeCode = at_rollover.CreatedBy', 'LEFT');
        $this->db->join('am_accountgl', 'am_accountgl.AccountNumber = ' . $this->table . '.ROvrAccountNumber');

        /* ===================================find by number dan desc========================================================= */
        if ($Number != "") {
            $this->db->like($this->table . '.ROvrNumber', $Number);
        }
        if ($Desc != "") {
            $this->db->like($this->table . '.ROvrDesc', $Desc);
        }
        /* =============================================end=================================================================== */

        /* ===================================find by branch code========================================================= */
        /*
          get data dari  combobox find di view
         */
        if ($BranchCode != "") {
            $this->db->where($this->table . '.BranchCode', $BranchCode);
        } else {
            /*
              inisialisasi untuk loading pertama page branch info
             */
            //----------------kondisi by company-------------------------------------//
            /*
              branch status 0 = HO
              branch status 1 = depo/branch
              jangan lupa sesion data di atas untuk setiap kondisi
              jangan lupa tambahin case when trans status
             */
            $this->db->where($this->table . '.BranchCode', $sess_branch);
            //-----------------------end-------------------------------------------------//
        }
        /* =======================================================end======================================================== */

        /* =====================================================find by date================================================== */
        if ((($DateFrom != '') && ($DateTo != ''))) {
            if ($DateFrom <= $DateTo) {
                $this->db->where($this->table . '.ROvrDate >=', date("Y-m-d", strtotime($DateFrom)));
                $this->db->where($this->table . '.ROvrDate <=', date("Y-m-d", strtotime($DateTo)));
            }
        }

        /* ======================================================end=========================================================== */

        /* =====================================================find by status================================================== */
        if ($TransStatus != '') {
            $this->db->where_in($this->table . '.TransStatus', $TransStatus);
        } else {
            /*
              trans status 0,1 adalah aktif
             */
            $this->db->where_in($this->table . '.TransStatus', array('0', '1', '4', '5', '6', '7'));
        }
        /* =======================================================end======================================================== */

        /* ==============================================sort tabel asc========================================================== */
        $this->db->order_by($this->table . '.ROvrNumber', 'ASC');
        $this->db->order_by($this->table . '.TransStatus', 'DESC');
        /* ===============================end================================================================================= */
        $query = $this->db->limit($end, $beginning);
        $query = $this->db->get();
//        echo $this->db->last_query();
        return $query->result();
    }

    function get_persediaan_akun() {
        /* ======================================scrol view 1-150======================================= */
        $page = ($this->input->post('page')) ? intval($this->input->post('page')) : 1;
        $rows = ($this->input->post('rows')) ? intval($this->input->post('rows')) : 150;

        $beginning = ($page - 1) * $rows;
        $end = $page * $rows;
        /* ============================================================================================== */

        /* =================================post dari find di atas pop up==================================================== */
        $param = $this->input->post('param');
        $AccountNumber = isset($param['txt_AccountNumber']) ? $param['txt_AccountNumber'] : '';
        $AccountName = isset($param['txt_AccountName']) ? $param['txt_AccountName'] : '';
        /* ============================================end============================================================ */

        /* =========================by session==================================================================== */
        $sess_branch = $this->session->userdata('branch');
        $sess_company = $this->session->userdata('company');
        /* =============================end====================================================================== */

        $this->db->select('agl.AccountName, agl.AccountNumber');
        $this->db->from('am_accountgl as agl');
        $this->db->join('at_compaccountgl as cg', 'cg.AccountNumber = agl.AccountNumber');
        $this->db->join('am_branch as br', 'br.BranchCode = cg.BranchCode', 'INNER');
        $this->db->join('am_accountsubtype as ast', 'ast.AccountSubTypeID = agl.AccountSubTypeID', 'inner');
        $this->db->join('am_accounttype as atp', 'atp.AccountTypeID = ast.AccountTypeID', 'inner');

        $this->db->where('(case when br.BranchStatus = 0 then cg.PLStatus in (0, 1) else cg.PLStatus = 1 end)');
        $this->db->where('cg.GLSaldoYears', date('Y'));
        $this->db->where('cg.BranchCode', $sess_branch);
        $this->db->where('cg.CompanyCode', $sess_company);
        $this->db->where_in('cg.TransStatus', array(2));

        $this->db->where('agl.AccountChild', 1);

        $this->db->where_in('atp.AccountTypeName', array('Expense', 'COGS / COGM (Cost Of Goods Made)', 'Other Expense'));

        /* ===============================================pencarian pas pop up jika kondisi tidak sama dengan kosong ================== */
        if ($AccountNumber != '') {
            $this->db->like('agl.AccountNumber', $AccountNumber);
        }
        if ($AccountName != '') {
            $this->db->like('agl.AccountName', $AccountName);
        }
        /* ====================================end====================================================================================== */

        $this->db->order_by('cg.AccountNumber', 'asc');
        $this->db->limit($end, $beginning);

        return $this->db->get()->result();
    }

    function get_dg_roll_edit($id, $uri4, $uri7) {
        $this->db->_protect_identifiers = FALSE;
        if ($uri7 == 0) {
            $this->db->select('*, "0" as RowStatus');
            $this->db->from($this->detail);
            $this->db->join($this->table, $this->table . '.ROvrNumber = ' . $this->detail . '.ROvrNumber', 'INNER');
            $this->db->join('at_compaccountgl', 'at_compaccountgl.AccountNumber = at_rovrdetail.AccountNumber', 'INNER');
            $this->db->join('am_accountgl', 'am_accountgl.AccountNumber = at_compaccountgl.AccountNumber', 'INNER');
            $this->db->join('am_branch', 'am_branch.BranchCode = at_rollover.BranchCode', 'INNER');
            $this->db->join('am_department', 'am_department.DepartmentCode = at_rovrdetail.DepartmentCode', 'INNER');

            $this->db->where('(case when am_branch.BranchStatus = 0 then at_compaccountgl.PLStatus in (0, 1) else at_compaccountgl.PLStatus = 1 end)');
            $this->db->where('at_compaccountgl.GLSaldoYears', date('Y'));
            $this->db->where('at_compaccountgl.BranchCode', $uri4);
        } else {
            $this->db->select($this->detail . '.ItemCode,' . $this->detail . '.ItemQty,' . $this->detail . '.ItemUnit,' . $this->detail . '.ItemDesc,'
                    . $this->detail . '.AllocatedValue as Allocated,' . $this->detail . '.ControlQtty, 
								am_item.ItemCategoryID,' . $this->detail . '.ROvrDetailID,' . $this->detail . '.HPPNew,' . $this->detail . '.HPPNew as HPPNew_Los, "0" as RowStatus');
            $this->db->from($this->detail);
            $this->db->join($this->table, $this->table . '.ROvrNumber = ' . $this->detail . '.ROvrNumber', 'INNER');
            $this->db->join('am_branch', 'am_branch.BranchCode = at_rollover.BranchCode', 'INNER');
            $this->db->join('am_item', 'am_item.ItemCode = ' . $this->detail . '.ItemCode', 'INNER');
            $this->db->where('am_branch.BranchCode', $uri4);
        }

        $this->db->where($this->table . '.ROvrNumber', $id);


        //$this->db->where('cg.CompanyCode',$company);

        $this->db->order_by('at_rollover.ROvrNumber', 'ASC');
        $query = $this->db->get();
        $this->db->_protect_identifiers = TRUE;
        return $query->result();
    }

    public function get_department($company) {
        $this->db->select('*');
        $this->db->from('am_department');
        $this->db->where('am_department.CompanyCode', $company);
        $this->db->where('am_department.TransStatus', 1);

        $query = $this->db->get();
        return $query->result();
    }

    function get_detail($id, $ROvrStatus) {
        if ($ROvrStatus == 0) {
            $this->db->select($this->table . ".*," . $this->department . ".DepartmentName," . $this->branch . ".BranchName," . $this->am_acc . ".AccountName");
            $this->db->join($this->detail, $this->detail . ".ROvrNumber=" . $this->table . ".ROvrNumber", "INNER");
            $this->db->join($this->branch, $this->branch . ".BranchCode=" . $this->table . ".BranchCode", "INNER");

            $this->db->join($this->am_acc, $this->am_acc . ".AccountNumber=" . $this->table . ".ROvrAccountNumber", "INNER");
            $this->db->join($this->at_company_gl, $this->at_company_gl . ".AccountNumber=" . $this->am_acc . ".AccountNumber", "INNER");
            $this->db->join($this->department, $this->department . ".DepartmentCode=" . $this->detail . ".DepartmentCode", "INNER");
        } else {
            $this->db->select($this->table . ".*," . $this->branch . ".BranchName," . $this->am_acc . ".AccountName");
            $this->db->join($this->detail, $this->detail . ".ROvrNumber=" . $this->table . ".ROvrNumber", "INNER");
            $this->db->join($this->branch, $this->branch . ".BranchCode=" . $this->table . ".BranchCode", "INNER");

            $this->db->join($this->am_acc, $this->am_acc . ".AccountNumber=" . $this->table . ".ROvrAccountNumber", "INNER");
            $this->db->join($this->at_company_gl, $this->at_company_gl . ".AccountNumber=" . $this->am_acc . ".AccountNumber", "INNER");
        }

        $query = $this->db->get_where($this->table, array('at_rollover.ROvrNumber' => $id));
        if ($query->num_rows() > 0) {
            return $query->row();
        } else {
            return false;
        }
    }

    function insert($data) {
        $this->db->insert($this->table, $data);
    }

    function insert_detail($data_detail) {
        $this->db->insert($this->detail, $data_detail);
    }

    function insert_jurnal($data) {
        $this->db->insert($this->journal, $data);
    }

    function delete_detail($id) {
        $this->db->delete($this->detail, array('ROvrNumber' => $id));
    }

    function delete_journal($id) {
        $this->db->delete($this->journal, array('ROvrNumber' => $id));
    }

    function update_app($data, $id) {
        $this->db->update($this->table, $data, array('ROvrNumber' => $id));
    }

    function update_detail($data, $id_detail) {
        $this->db->update($this->detail, $data, array('ROvrDetailID' => $id_detail));
    }

    function set_trans_status($id, $data_transstatus) {
        $this->db->set('TransStatus', $data_transstatus);
        $this->db->where('ROvrNumber', $id);
        $this->db->update($this->table);
    }

    function set_trans_status_job_cos($id, $data_transstatus_job_cos) {
        $this->db->set('TransStatus', $data_transstatus_job_cos);
        $this->db->where('JCostNumber', $id);
        $this->db->update('at_jobcosting');
    }

    function get_detail_jurnal($id) {
        $this->db->_protect_identifiers = FALSE;
        $this->db->select("am_branch.BranchName, am_company.CompanyName, at_rollover.ROvrNumber, at_rollover.ROvrDesc");
        $this->db->join("am_branch", "am_branch.BranchCode=at_rollover.BranchCode", "INNER");
        $this->db->join("am_company", "am_company.CompanyCode=am_branch.CompanyCode", "INNER");
        $query = $this->db->get_where($this->table, array('ROvrNumber' => $id));
        $this->db->_protect_identifiers = TRUE;
        return $query->result();
    }

    function get_journal_by_number($Number) {
        $this->db->_protect_identifiers = FALSE;
        $this->db->select("at_rovrjournal.*,am_accountgl.AccountNumber,am_accountgl.AccountName");
        $this->db->join("am_accountgl", "am_accountgl.AccountNumber=at_rovrjournal.ROvrAccountNumber", "INNER");
        $this->db->order_by('at_rovrjournal.ROvrSaldoDebet', 'DESC');
        $query = $this->db->get_where($this->journal, array('ROvrNumber' => $Number));
        $this->db->_protect_identifiers = TRUE;
        return $query->result();
    }

    public function get_no_job_cos($branch) {
        $this->db->select('at_jobcosting.JCostNumber, at_jobcosting.JCostDesc, at_jobcosting.JCostAccount, am_accountgl.AccountName
							,at_jobcosting.JCostDate, at_jobcosting.JCostDesc, at_jobcosting.JCostSubTotal');
        $this->db->from('at_jobcosting');
        $this->db->join('am_accountgl', 'am_accountgl.AccountNumber = at_jobcosting.JCostAccount');
        //$this->db->join('at_rollover','at_rollover.JCostNumber = at_jobcosting.JCostNumber');

        $this->db->where('at_jobcosting.TransStatus', 1);
        //$this->db->where('at_rollover.LockStatus !=', 1);
        $this->db->where('at_jobcosting.BranchCode', $branch);
        $this->db->order_by('at_jobcosting.JCostNumber', 'asc');

        $query = $this->db->get();
        return $query->result();
    }

    function suspend($data, $id) {
        $this->db->update($this->table, $data, array('ROvrNumber' => $id));
    }

    /* ======================lemparan dari modul item master saat suspen data============================== */

    function get_rol_over_detail_by_item($item_code) {
        $this->db->select('at_rovrdetail.ItemCode');
        $this->db->from('at_rovrdetail');
        $this->db->join('at_rollover', 'at_rollover.ROvrNumber = at_rovrdetail.ROvrNumber', 'INNER');

        $this->db->where('at_rovrdetail.ItemCode', $item_code);
        $this->db->where_in('at_rollover.TransStatus', array(1, 4, 5, 6));
        $query = $this->db->get();

        if ($query->num_rows() > 0) {
            return 1;
        } else {
            return 0;
        }
    }

    /* =================================end=============================================================== */

    /* ======================penomeran transaksi new======================================================= */

    function check_code_exist($new_ro_number) {
        $query = $this->db->get_where($this->table, array('ROvrNumber' => $new_ro_number), 1, 0);

        if ($query->num_rows() > 0) {
            return TRUE;
        } else {
            return FALSE;
        }
    }

    function create_ro_number($date = '') {
        $branch = $this->session->userdata('branch');

        if ($date != '') {
            $month = date('m', strtotime($date));
            $year = date('y', strtotime($date));
        } else {
            $month = date('m');
            $year = date('y');
        }

        $ro_number = $branch . "/" . $month . $year . "RO/";

        $this->db->_protect_identifiers = false;
        $this->db->select('(CASE 
                              WHEN MAX(CONVERT(SUBSTR(ROvrNumber, -3),signed)) IS NULL then 1
                              ELSE MAX(CONVERT(SUBSTR(ROvrNumber, -3),signed)) + 1 END) as New_Number');
        $this->db->like('ROvrNumber', $ro_number);
        $query = $this->db->get($this->table);
        $this->db->_protect_identifiers = true;

        $num = $query->row()->New_Number;
        $long = strlen($num);

        if ($long == 1) {
            $number = '00' . $num;
        } elseif ($long == 2) {
            $number = '0' . $num;
        } else {
            $number = $num;
        }

        $new_ro_number = $ro_number . $number;

        while ($this->check_code_exist($new_ro_number)) {
            $num++;
            $long = strlen($num);

            if ($long == 1) {
                $number = '00' . $num;
            } elseif ($long == 2) {
                $number = '0' . $num;
            } else {
                $number = $num;
            }

            $new_ro_number = $ro_number . $number;
        }

        return $new_ro_number;
    }

    /* ==============================================================end================================= */

    public function get_roll_over_by_jcos_number($JCostNumber) {
        $this->db->select('LockStatus, AppLevel1Date');
        $this->db->from($this->table);
        $this->db->where($this->table . '.JCostNumber', $JCostNumber);

        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->result();
        } else {
            return FALSE;
        }
    }

    public function get_roll_over_group($ROverNumber) {
        $this->db->select('ROvrStatus,BranchCode');
        $this->db->from($this->table);
        $this->db->where($this->table . '.ROvrNumber', $ROverNumber);

        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->row();
        } else {
            return FALSE;
        }
    }

    public function get_roll_over_detail($ROverNumber) {
        $this->db->select('ItemCode, ItemQty');
        $this->db->from($this->detail);
        $this->db->where($this->detail . '.ROvrNumber', $ROverNumber);

        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->result();
        } else {
            return FALSE;
        }
    }

    function delete_data_grid($ROvrDetailID) {
        $this->db->where('ROvrDetailID', $ROvrDetailID);
        $this->db->delete($this->detail);
    }

    function search_roll_over() {
        /* ======================================scrol view 1-150======================================= */
        $page = ($this->input->post('page')) ? intval($this->input->post('page')) : 1;
        $rows = ($this->input->post('rows')) ? intval($this->input->post('rows')) : 150;

        $beginning = ($page - 1) * $rows;
        $end = $page * $rows;
        /* ============================================================================================== */

        /* =================================post dari find di atas pop up==================================================== */
        $param = $this->input->post('param');
        $ItemCategoryID = isset($param['cmb_ItemCategoryID']) ? $param['cmb_ItemCategoryID'] : '';
        $ItemCode = isset($param['txt_ItemCode']) ? $param['txt_ItemCode'] : '';
        $ItemDesc = isset($param['txt_ItemDesc']) ? $param['txt_ItemDesc'] : '';
        /* ============================================end============================================================ */

        /* =========================ny session==================================================================== */
        $sess_branch = $this->session->userdata('branch');
        /* =============================end====================================================================== */

        $this->db->select('am_item.ItemCode,am_item.ItemDesc, am_item.ItemUnit, am_itemcategory.ItemCategoryName,
							am_itemcategory.ItemCategoryID');
        $this->db->from('am_itemcategory');
        $this->db->join('am_item', 'am_item.ItemCategoryID = am_itemcategory.ItemCategoryID', 'inner');

        /* $explode_arr 	= array();
          $explode_arr 	= explode(",", $item_categori);
          $var_array 		= implode(",", $explode_arr); */
        $this->db->where('am_item.ItemTypeID !=', 1);
        $this->db->where_in('am_item.TransStatus', array(0, 1));

        /* ===============================================pencarian pas pop up jika kondisi tidak sama dengan kosong ================== */
        if ($ItemCategoryID != "") {
            if ($ItemCategoryID == "All") {
                
            } else {
                $this->db->like('am_item.ItemCategoryID', $ItemCategoryID);
            }
        }
        if ($ItemCode != '') {
            $this->db->like('am_item.ItemCode', $ItemCode);
        }
        if ($ItemDesc != '') {
            $this->db->like('am_item.ItemDesc', $ItemDesc);
        }
        /* ====================================end====================================================================================== */

        $this->db->order_by('am_item.ItemCode', 'ASC');
        $this->db->limit($end, $beginning);

        return $this->db->get()->result();
    }

    function get_detail_print($id) {
        $this->db->select('at_rollover.TransStatus, at_rollover.ROvrDate, am_company.CompanyName, am_branch.BranchName,
							am_branch.BranchAddress,
							at_rollover.ROvrNumber, am_branch.BranchName, at_rollover.ROvrDesc, at_rollover.JCostSubTotal,at_rollover.JCostNumber,
							prepared.EmployeeName as nm_createby, dir.EmployeeName as nm_approve');
        $this->db->from($this->table);
        $this->db->join('am_branch', 'am_branch.BranchCode = at_rollover.BranchCode');
        $this->db->join('am_company', 'am_company.CompanyCode = am_branch.CompanyCode');
        $this->db->join('am_employee as prepared', 'prepared.EmployeeCode = at_rollover.CreatedBy', 'LEFT');
        $this->db->join('am_employee as dir', 'dir.EmployeeCode = at_rollover.AppLevel1Code', 'LEFT');

        $this->db->where('at_rollover.ROvrNumber', $id);
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->row();
        } else {
            return false;
        }
    }

    function get_detail_item_print($id) {
        $this->db->_protect_identifiers = FALSE;
        $this->db->select(" 
							at_rovrdetail.ItemCode, at_rovrdetail.ItemDesc, at_rovrdetail.ItemUnit, at_rovrdetail.ItemQty,
							at_rovrdetail.AllocatedValue, at_rovrdetail.HPPNew
						");
        $this->db->join('am_item', 'am_item.ItemCode = at_rovrdetail.ItemCode', 'inner');

        $this->db->where('at_rovrdetail.ROvrNumber', $id);
        $this->db->distinct();
        $query = $this->db->get('at_rovrdetail');
        $this->db->_protect_identifiers = TRUE;
        return $query->result_array();
    }

    function get_detail_item_print_akun($id) {
        $this->db->_protect_identifiers = FALSE;
        $this->db->select(" 
							at_rovrdetail.AccountNumber, am_accountgl.AccountName, am_department.DepartmentName
						");
        $this->db->join('am_accountgl', 'am_accountgl.AccountNumber = at_rovrdetail.AccountNumber', 'left');
        $this->db->join('am_department', 'am_department.DepartmentCode = at_rovrdetail.DepartmentCode', 'left');

        $this->db->where('at_rovrdetail.ROvrNumber', $id);
        $this->db->distinct();
        $query = $this->db->get('at_rovrdetail');
        $this->db->_protect_identifiers = TRUE;
        return $query->result_array();
    }

    function get_all_print_list_LAMA($BranchCode) {
        $this->db->select("at_rollover.*,
						(case 
                            when at_rollover.TransStatus = 0 then 'Waiting' 
                            when at_rollover.TransStatus = 1 then 'On Progress' 
                            when at_rollover.TransStatus = 2 then 'Active' 
                            when at_rollover.TransStatus = 3 then 'Suspended'
							when at_rollover.TransStatus = 4 then 'Need App. Mgr.'  
							when at_rollover.TransStatus = 5 then 'Need App. Dir.' 
							when at_rollover.TransStatus = 6 then 'App. Dir & Mgr' end) as Status, am_employee.EmployeeName as CreatedBy
					");
        $this->db->from($this->table);
        $this->db->join('am_employee', 'am_employee.EmployeeCode = at_rollover.CreatedBy', 'LEFT');
        $this->db->where('at_rollover.BranchCode', $BranchCode);
        $query = $this->db->get();
        return $query->result();
    }

    function get_all_to_print($data_filter) {
        /* =================================post dari find di kiri==================================================== */
        $CompanyCode    = $data_filter['cmb_company'];
        $BranchCode     = $data_filter['cmb_branch'];
        $Number         = $data_filter['txt_IDNumber'];
        $Desc           = $data_filter['txt_item_desc'];
        $DateFrom       = $data_filter['txt_DateFrom'];
        $DateTo         = $data_filter['txt_DateTo'];
        $TransStatus    = $data_filter['chk_TransStatus'];
        /* ============================================end============================================================ */

        /* ==========================================by session======================================================= */
        $sess_company = $this->session->userdata('company');
        $sess_branch = $this->session->userdata('branch');
        $sess_branch_status = $this->session->userdata('branch_status');
        /* =========================================================================================================== */

        $this->db->select(" 
            at_rollover.BranchCode as cpay_branch, am_accountgl.AccountName, at_rollover.ROvrNumber, at_rollover.JCostNumber, am_employee.EmployeeName as CreatedBy,
            at_rollover.ROvrDate, at_rollover.ROvrDesc, am_branch.BranchName, 
            at_rollover.JCostSubTotal, (case 
                when at_rollover.TransStatus = 0 then 'Waiting' 
                when at_rollover.TransStatus = 1 then 'On Progress' 
                when at_rollover.TransStatus = 2 then 'Active' 
                when at_rollover.TransStatus = 3 then 'Suspended'
                when at_rollover.TransStatus = 4 then 'Need App. Mgr.'  
                when at_rollover.TransStatus = 5 then 'Need App. Dir.' 
                when at_rollover.TransStatus = 6 then 'App. Dir & Mgr' end) as Status"
        );
        $this->db->from($this->table);
        $this->db->join('am_branch', 'am_branch.BranchCode = at_rollover.BranchCode');
        $this->db->join('am_employee', 'am_employee.EmployeeCode = at_rollover.CreatedBy', 'LEFT');
        $this->db->join('am_accountgl', 'am_accountgl.AccountNumber = ' . $this->table . '.ROvrAccountNumber');

        /* ===================================find by number dan desc========================================================= */
        if ($Number != "") {
            $this->db->like($this->table . '.ROvrNumber', $Number);
        }
        if ($Desc != "") {
            $this->db->like($this->table . '.ROvrDesc', $Desc);
        }
        /* =============================================end=================================================================== */

        /* ===================================find by branch code========================================================= */
        
        if ($BranchCode != "") {
            $this->db->where($this->table . '.BranchCode', $BranchCode);
        } else {
            $this->db->where($this->table . '.BranchCode', $sess_branch);
            //-----------------------end-------------------------------------------------//
        }
        /* =======================================================end======================================================== */

        /* =====================================================find by date================================================== */
        if ((($DateFrom != '') && ($DateTo != ''))) {
            if ($DateFrom <= $DateTo) {
                $this->db->where($this->table . '.ROvrDate >=', date("Y-m-d", strtotime($DateFrom)));
                $this->db->where($this->table . '.ROvrDate <=', date("Y-m-d", strtotime($DateTo)));
            }
        }

        /* ======================================================end=========================================================== */

        /* =====================================================find by status================================================== */
        if (!empty($TransStatus)) {
            $this->db->where_in($this->table . '.TransStatus', $TransStatus);
        } else {
            $this->db->where_in($this->table . '.TransStatus', array(2, 6));
        }
        /* =======================================================end======================================================== */

        /* ==============================================sort tabel asc========================================================== */
        $this->db->order_by($this->table . '.ROvrNumber', 'ASC');
        $this->db->order_by($this->table . '.TransStatus', 'DESC');
        /* ===============================end================================================================================= */
        $query = $this->db->get();
//        echo $this->db->last_query();
        return $query->result();
    }

    function check_period_end($periodend) {
        $this->db->select('PeriodEnd');
        $this->db->from($this->period);
        $this->db->where('PeriodEnd', $periodend);

        $query = $this->db->get()->result_array();

        if (count($query) > 0) {
            return true;
        } else {
            return false;
        }
    }

    function check_status_jobcosting($JCostNumber) {
        $this->db->select('TransStatus');
        $this->db->from($this->jobcosting);
        $this->db->where('JCostNumber', $JCostNumber);

        $query = $this->db->get()->result_array();

        if ($query[0]['TransStatus'] == 1) {
            return true;
        } else {
            return false;
        }
    }

    function get_rollover_detail($no_rollover, $branch) {
        $this->db->select('dt.ItemCode,dt.ItemQty,dt.AllocatedValue,dt.HPPNew');
        $this->db->from($this->detail . ' as dt');
        $this->db->where('dt.ROvrNumber', $no_rollover);

        $query = $this->db->get()->result();
        return $query;
    }

    function check_lock_status_inventories($ItemCode, $branch, $status) {
        $this->db->select('LockStatus');
        $this->db->from($this->inventories);
        $this->db->where('BranchCode', $branch);
        $this->db->where('ItemCode', $ItemCode);
        $this->db->where('LockStatus', $status);

        $query = $this->db->get();

        if ($query->num_rows() > 0) {
            return true;
        } else {
            return false;
        }
    }

    function get_data_inventories($ItemCode, $branch) {
        $this->db->select('ItemQty,ItemValue,ItemAverage,ItemBooks');
        $this->db->from($this->inventories);
        $this->db->where('BranchCode', $branch);
        $this->db->where('ItemCode', $ItemCode);

        $query = $this->db->get();

        if ($query->num_rows() > 0) {
            return $query->row();
        } else {
            return false;
        }
    }

    function reverse_jobcosting($data, $TransNumber) {
        $this->db->update($this->jobcosting, $data, array('JCostNumber' => $TransNumber));
    }

    function reverse_rollover($data, $TransNumber) {
        $this->db->update($this->table, $data, array('ROvrNumber' => $TransNumber));
    }

    function update_inventori($data, $ItemCode, $sess_branch) {
        $this->db->update('at_inventories', $data, array('ItemCode' => $ItemCode, 'BranchCode' => $sess_branch));
    }

    function insert_transsaction($trans_data) {

        $this->db->trans_start();

        /* ======================= insert group transaction ============================ */
        $this->insert($trans_data['insert_group']['data']);
        /* ====================== end group transaction =============================== */

        /* =========================== insert detail ============================ */
        for ($x = 0; $x < count($trans_data['insert_detail']['data']); $x++) {
            $this->insert_detail($trans_data['insert_detail']['data'][$x]);
        }
        /* =========================== end insert detail ========================== */

        /* ===================== insert journal ================================ */
        if ($trans_data['status_tab'] == 0) {
            for ($x = 0; $x < count($trans_data['insert_journal_to_akun']['data']); $x++) {
                $this->insert_jurnal($trans_data['insert_journal_to_akun']['data'][$x]);
            }
        } else {
            for ($x = 0; $x < count($trans_data['insert_journal_to_item']['data']); $x++) {
                $this->insert_jurnal($trans_data['insert_journal_to_item']['data'][$x]);
            }
        }
        /* ===================== end insert journal =============================== */

        $this->db->trans_complete();

        if ($this->db->trans_status() === FALSE) {
            return false;
        } else {
            return true;
        }
    }

    function update_transsaction($trans_data) {

        $this->db->trans_start();

        /* ====================== update group ==================== */
        $this->update_app($trans_data['update_group']['data'], $trans_data['update_group']['TransNumber']);

        /* ======================= update detail ============================== */
        for ($x = 0; $x < count($trans_data['update_detail']['data']); $x++) {
            $this->update_detail($trans_data['update_detail']['data'][$x], $trans_data['update_detail']['ROvrDetailID'][$x]);
        }

        /* ======================== delete detail ======================= */
        if ($trans_data['delete_detail']['total'] > 0) {
            for ($x = 0; $x < $trans_data['delete_detail']['total']; $x++) {
                $this->delete_data_grid($trans_data['delete_detail']['ROvrDetailID'][$x]);
            }
        }

        if ($trans_data['status_tab'] == 0) {
            /* ========================= delete journal =============================== */
            $this->delete_journal($trans_data['delete_journal_to_akun']['TransNumber']);

            /* ===================== insert journal ================================ */
            for ($x = 0; $x < count($trans_data['insert_journal_to_akun']['data']); $x++) {
                $this->insert_jurnal($trans_data['insert_journal_to_akun']['data'][$x]);
            }
        } else {
            if ($trans_data['delete_journal_to_item']['total'] > 0) {
                $this->delete_journal($trans_data['delete_journal_to_item']['TransNumber']);
            }

            for ($x = 0; $x < count($trans_data['insert_journal_to_item']['data']); $x++) {
                $this->insert_jurnal($trans_data['insert_journal_to_item']['data'][$x]);
            }
        }

        /* ===================== end insert journal =============================== */
        $this->db->trans_complete();

        if ($this->db->trans_status() === FALSE) {
            return false;
        } else {
            return true;
        }
    }

    function approve_transsaction($trans_data) {

        $this->db->trans_start();

        if ($trans_data['status_tab'] == 1) {
            if ($trans_data['detail_item']['total'] > 0) {
                for ($x = 0; $x < $trans_data['detail_item']['total']; $x++) {
                    if ($trans_data['detail_item']['inventori_code'][$x] != null) {
                        $this->m_inventories->update_average($trans_data['update_average']['data'][$x], $trans_data['update_average']['ItemCode'][$x], $trans_data['update_average']['branch'][$x]);
                    } else {
                        $this->m_inventories->insert($trans_data['insert']['data'][$x]);
                    }
                }
            }
        }

        $this->update_app($trans_data['update_app']['data'], $trans_data['update_app']['TransNumber']);

        $this->set_trans_status($trans_data['set_trans_status']['TransNumber'], $trans_data['set_trans_status']['data']);

        $this->set_trans_status_job_cos($trans_data['set_trans_status_job_cos']['TransNumber'], $trans_data['set_trans_status_job_cos']['data']);

        $this->db->trans_complete();

        if ($this->db->trans_status() === FALSE) {
            return false;
        } else {
            return true;
        }
    }

    function reverse_transsaction($trans_data) {

        $this->db->trans_start();


        if ($trans_data['status_tab'] == 1) {
            if ($trans_data['update_inventori']['total'] > 0) {
                for ($x = 0; $x < count($trans_data['update_inventori']['data']); $x++) {
                    $this->update_inventori($trans_data['update_inventori']['data'][$x], $trans_data['update_inventori']['ItemCode'][$x], $trans_data['update_inventori']['branch'][$x]);
                }
            }
        }

        $this->reverse_jobcosting($trans_data['reverse_jobcosting']['data'], $trans_data['reverse_jobcosting']['TransNumber']);

        $this->reverse_rollover($trans_data['reverse_rollover']['data'], $trans_data['reverse_rollover']['TransNumber']);

        $this->db->trans_complete();

        if ($this->db->trans_status() === false) {
            return false;
        } else {
            return true;
        }
    }

    function suspend_transsaction($trans_data) {

        $this->db->trans_start();

        /* ===================================== suspend payment ============================================ */
        $this->suspend($trans_data['suspend_data']['data'], $trans_data['suspend_data']['TransNumber']);

        $this->db->trans_complete();

        if ($this->db->trans_status() === FALSE) {
            return false;
        } else {
            return true;
        }
    }

}
