<?php

class ModelCommonHome extends HModel {

    public function getSales($data) {
        $sql = "SELECT date, sum(amount) as amount";
        $sql .= " FROM vw_receipt_service r";
        $sql .= " WHERE TRUE";
        if(isset($data['date_from']) && $data['date_from']) {
            $sql .= " AND r.date >= '" . $data['date_from'] . "'";
        }
        
        if(isset($data['date_to']) && $data['date_to']) {
            $sql .= " AND r.date <= '" . $data['date_to'] . "'";
        }
        $sql .= " GROUP BY date";
        
        $query = $this->conn->query($sql);
        return $query->rows;
    }

    public function getTopCustomer($filter) {
        $sql = "SELECT si.company_id,si.fiscal_year_id,si.company_branch_id,si.partner_id,si.partner_name name,SUM(net_amount) total";
        $sql .= " FROM vw_sale_invoice si";
        $sql .= " WHERE si.company_id = '".$filter['company_id']."' AND si.fiscal_year_id = '".$filter['fiscal_year_id']."' AND si.company_branch_id = '".$filter['company_branch_id']."' ";
        $sql .= " AND DATE_FORMAT(si.document_date,'%Y-%m') = '".date('Y-m')."'";
        $sql .= " GROUP BY si.partner_id";
        $sql .= " ORDER BY SUM(net_amount) DESC";
        $sql .= " LIMIT 10";


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

    public function getSale($filter) {
        $sql = "SELECT si.company_id,si.fiscal_year_id,si.company_branch_id,DATE_FORMAT(si.document_date,'%Y-%m') as date,SUM(net_amount) total ";
        $sql .= " FROM vw_sale_invoice si ";
        $sql .= " WHERE si.company_id = '".$filter['company_id']."' AND si.fiscal_year_id = '".$filter['fiscal_year_id']."' AND si.company_branch_id = '".$filter['company_branch_id']."' ";
//        $sql .= " AND DATE_FORMAT(si.document_date,'%Y-%m') = '".date('Y-m')."'";
//        $sql .= " GROUP BY si.document_date";
        $sql .= " GROUP BY DATE_FORMAT(si.document_date,'%Y-%m')";
        $sql .= " ORDER BY si.document_date ASC ";
        $sql .= " LIMIT 31";


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

    public function totalProductCategoryInWarehouse(){
        $sql = "SELECT `product_category`, `warehouse`, SUM(base_cubic_feet)";
        $sql .= " FROM `vw_inventory_stock_ledger`";
        $sql .= " GROUP BY `warehouse`, `product_category`";
        $sql .= " ORDER BY `product_category`, `warehouse`;";

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

    public function getSupplierBalance(){
        $sql = "SELECT ";
	    $sql .= "l.partner_type, l.partner_name AS supplier_name";
	    $sql .= ", SUM(`Debit` - `Credit`) AS balance ";
        $sql .= "FROM `vw_ledger` l ";
        $sql .= "INNER JOIN `partner` p ON p.`partner_id` = l.`partner_id` AND (p.`outstanding_account_id` = l.`coa_id` ";
        $sql .= "OR p.`advance_account_id` = l.`coa_id`) ";
        $sql .= "WHERE l.`company_id` = '".$this->session->data['company_id']."' ";
        $sql .= "AND l.`company_branch_id` = '".$this->session->data['company_branch_id']."' ";
        $sql .= "AND l.`fiscal_year_id` = '".$this->session->data['fiscal_year_id']."' ";
        $sql .= "AND l.`partner_type_id` = '1' ";
        $sql .= "GROUP BY l.`partner_type`, l.`partner_name` ";
        $sql .= "HAVING balance != 0 ";
        $sql .= "ORDER BY l.`partner_type`, l.`partner_name`";

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

    public function getCustomerBalance(){
        $sql = "SELECT ";
	    $sql .= "l.partner_type, l.partner_name AS customer_name";
	    $sql .= ", SUM(`Debit` - `Credit`) AS balance ";
        $sql .= "FROM `vw_ledger` l ";
        $sql .= "INNER JOIN `partner` p ON p.`partner_id` = l.`partner_id` AND (p.`outstanding_account_id` = l.`coa_id` ";
        $sql .= "OR p.`advance_account_id` = l.`coa_id`) ";
        $sql .= "WHERE l.`company_id` = '".$this->session->data['company_id']."' ";
        $sql .= "AND l.`company_branch_id` = '".$this->session->data['company_branch_id']."' ";
        $sql .= "AND l.`fiscal_year_id` = '".$this->session->data['fiscal_year_id']."' ";
        $sql .= "AND l.`partner_type_id` = '2' ";
        $sql .= "GROUP BY l.`partner_type`, l.`partner_name` ";
        $sql .= "HAVING balance != 0 ";
        $sql .= "ORDER BY l.`partner_type`, l.`partner_name`";

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

?>