<?php

class ModelCommonStockLedger extends HModel {

    protected function getTable() {
        return 'inventory_stock_ledger';
    }

    protected function getView() {
        return 'vw_inventory_stock_ledger';
    }

    public function getStocks($filter = array()) {
        $sql = "";
        $sql .= "SELECT container_no, batch_no, product_id, SUM(base_qty) AS stock_qty, SUM(base_amount) AS stock_amount, ROUND(SUM(base_amount)/SUM(base_qty),2) AS avg_cogs_rate";
        $sql .= " FROM `inventory_stock_ledger`";
        if($filter) {
            if(is_array($filter)) {
                $implode = array();
                foreach($filter as $column => $value) {
                    $implode[] = "`$column`='$value'";
                }
                if($implode)
                    $sql .= " WHERE " . implode(" AND ", $implode);
            } else {
                $sql .= " WHERE " . $filter;
            }
        }
        $sql .= " GROUP BY company_id, company_branch_id, fiscal_year_id, container_no, batch_no, product_id";

        $query = $this->conn->query($sql);
        return $query->rows;
    }

    public function getStock($filter = array()) {
        /*
        $sql ="TRUNCATE TABLE `_stock_ledger`";
        $this->conn->query($sql);
        $sql ="SET @sr = 0";
        $this->conn->query($sql);
        $sql ="INSERT INTO `_stock_ledger`";
        $sql .= " SELECT @sr:=@sr+1 AS sr, document_type_id, document_id, document_identity, document_date, container_no, base_qty, base_cubic_meter, base_cubic_feet, base_amount, created_at";
        $sql .= " FROM `inventory_stock_ledger`";
        $arrWhere=array();
        if(isset($filter['container_no'])) {
            $arrWhere[] = "`container_no` = '".$filter['container_no']."'";
        }
        if(isset($filter['product_id'])) {
            $arrWhere[] = "`product_id` = '".$filter['product_id']."'";
        }
        if($arrWhere) {
            $sql .= " WHERE ".implode(' AND ', $arrWhere);
        }
        $sql .= " ORDER BY document_date, document_type_id, created_at";
        $this->conn->query($sql);

        $sql = "SELECT sr FROM `_stock_ledger` WHERE `document_type_id`='".$filter['document_type_id']."' AND `document_identity`='".$filter['document_identity']."'";
        $query = $this->conn->query($sql);
        $sr = $query->row['sr'];

        $sql = "SELECT SUM(base_qty) AS stock_qty, SUM(base_cubic_meter) AS stock_cubic_meter, SUM(base_cubic_feet) AS stock_cubic_feet, SUM(base_amount) AS stock_amount";
        $sql .= ", ROUND(SUM(base_amount)/SUM(base_cubic_feet),2) AS avg_stock_rate, ROUND(SUM(base_cubic_meter)/SUM(base_qty),6) AS avg_cubic_meter, ROUND(SUM(base_cubic_feet)/SUM(base_qty),6) AS avg_cubic_feet";
        $sql .= " FROM `_stock_ledger`";
        if($sr) {
            $sql .= " WHERE sr < ".$sr;
        }
        */

        $sql = '';
        $sql .= "SELECT SUM(base_qty) AS stock_qty, SUM(base_cubic_meter) AS stock_cubic_meter, SUM(base_cubic_feet) AS stock_cubic_feet, SUM(base_amount) AS stock_amount";
        $sql .= ", ROUND(SUM(base_amount)/SUM(base_cubic_feet),4) AS avg_stock_rate, ROUND(SUM(base_cubic_meter)/SUM(base_qty),6) AS avg_cubic_meter, ROUND(SUM(base_cubic_feet)/SUM(base_qty),6) AS avg_cubic_feet";
        $sql .= " FROM `inventory_stock_ledger`";
        if($filter) {
            if(is_array($filter)) {
                $implode = array();
                foreach($filter as $column => $value) {
                    $implode[] = "`$column`='$value'";
                }
                if($implode)
                    $sql .= " WHERE " . implode(" AND ", $implode);
            } else {
                $sql .= " WHERE " . $filter;
            }
        }
        $sql .= " GROUP BY company_id, company_branch_id, fiscal_year_id, product_id";

        //d($sql, true);
        $query = $this->conn->query($sql);
        return $query->row;
    }

    public function getWarehouseStock($product_id, $warehouse_id='') {
        $sql = "";
        $sql .= " SELECT SUM(IF(warehouse_id='".$warehouse_id."',base_qty,0)) AS stock_qty, ROUND(SUM(base_amount)/SUM(base_qty),2) AS avg_stock_rate";
        $sql .= " FROM `inventory_stock_ledger`";
        $sql .= " WHERE company_id = '".$this->session->data['company_id']."'";
        $sql .= " AND company_branch_id = '".$this->session->data['company_branch_id']."'";
        $sql .= " AND fiscal_year_id = '".$this->session->data['fiscal_year_id']."'";
        $sql .= " AND product_id = '".$product_id."'";

        $query = $this->conn->query($sql);
        return $query->row;
    }

    public function getBalanceContainers($filter=array()) {
        $sql = "";
        $sql .= "SELECT `container_no`, COUNT(*) as records, SUM(`base_qty`) AS base_qty";
        $sql .= " FROM `inventory_stock_ledger`";
        if($filter) {
            if(is_array($filter)) {
                $implode = array();
                foreach($filter as $column => $value) {
                    $implode[] = "`$column`='$value'";
                }
                if($implode)
                    $sql .= " WHERE " . implode(" AND ", $implode);
            } else {
                $sql .= " WHERE " . $filter;
            }
        }
        $sql .= " GROUP BY `container_no`";
        $sql .= " HAVING `base_qty` > 0";

        $query = $this->conn->query($sql);
        return $query->rows;
    }

    public function getBalanceContainerStocks($container_no, $warehouse_id='') {
        $sql = "";
        $sql .= "SELECT sl.warehouse_id, w.name as `warehouse_name`, sl.manual_ref_no, sl.container_no, sl.batch_no, sl.product_id, p.product_code, p.name as product_name, sl.base_unit_id";
        $sql .= ", p.cost_price, p.sale_price, round(SUM(sl.base_cubic_meter)/SUM(sl.base_qty),4) as cubic_meter, round(SUM(sl.base_cubic_feet)/SUM(sl.base_qty),4) as cubic_feet";
        $sql .= ", SUM(sl.base_qty) AS balance_qty, SUM(sl.base_cubic_meter) as balance_cubic_meter, SUM(sl.base_cubic_feet) as balance_cubic_feet, SUM(sl.base_amount) AS balance_amount";
        $sql .= ", Round(SUM(sl.base_cubic_meter)/SUM(sl.base_qty),6) AS avg_cubic_meter, Round(SUM(sl.base_cubic_feet)/SUM(sl.base_qty),6) AS avg_cubic_feet, Round(SUM(sl.base_amount)/SUM(sl.base_cubic_feet),2) AS avg_cog_rate";
        $sql .= " FROM `inventory_stock_ledger` sl";
        $sql .= " INNER JOIN `inventory_product` p ON p.product_id = sl.product_id";
        $sql .= " LEFT JOIN `warehouse` w ON w.warehouse_id = sl.warehouse_id";
        $sql .= " WHERE `container_no` = '".$container_no."'";
        if($warehouse_id != '') {
            $sql .= " AND `warehouse_id` = '".$warehouse_id."'";
        }
        $sql .= " GROUP BY warehouse_id,manual_ref_no, container_no, batch_no, product_id, base_unit_id";
        $sql .= " HAVING balance_qty > 0";

        $query = $this->conn->query($sql);
        return $query->rows;
    }

}

?>