<?php

class ModelGLLedger extends HModel {

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

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

    public function getDocumentLedger($document_type_id, $document_id) {
        $sql  = "SELECT account_name as account";
        $sql .= ", CASE WHEN sum(debit-credit) > 0 THEN sum(debit-credit) ELSE 0 END as debit";
        $sql .= ", CASE WHEN sum(debit-credit) < 0 THEN sum(credit-debit) ELSE 0 END as credit";
        $sql .= " FROM " . $this->getView();
        $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 `document_type_id` = '" . $document_type_id . "'";
        $sql .= " AND `document_id` = '" . $document_id . "'";
        $sql .= " GROUP BY `account_name`";
        $sql .= " ORDER BY debit desc,credit";

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

    public function getOutStanding($filter=array(),$sort_order=array()) {
        // This Function is Used to get All OutStanding Documents of Particular Partner.
        //$sql ="SELECT l.company_id, l.fiscal_year_id, l.people_type_id, l.people_id, l.ref_document_type_id, l.ref_document_id, l.ref_document_identity, l.coa_id";
        $sql ="SELECT l.company_id, l.fiscal_year_id, l.people_type_id, l.people_id, l.ref_document_type_id, l.ref_document_identity" . "\n";
        $sql .= " , CASE WHEN l.people_type_id = 2 THEN SUM(l.debit-l.credit) ELSE SUM(l.credit-l.debit) END as outstanding" . "\n";
        $sql .= " FROM `" . $this->getView() . "` l" . "\n";
        $sql .= " INNER JOIN `vw_people` p ON l.people_type_id = p.people_type_id AND l.people_id = p.people_id" . "\n";
        $sql .= " AND (l.coa_id = p.outstanding_account_id OR l.coa_id = p.advance_account_id)" . "\n";
        $sql .= " WHERE l.company_id = '".$filter['company_id']."'";
        $sql .= " AND l.company_branch_id = '".$filter['company_branch_id']."'";
        $sql .= " AND l.fiscal_year_id = '".$filter['fiscal_year_id']."'";
        $sql .= " AND l.people_type_id = '".$filter['people_type_id']."'";
        $sql .= " AND l.people_id = '".$filter['people_id']."'";
        if(isset($filter['document_id']) && $filter['document_id']) {
            $sql .= " AND l.document_id != '".$filter['document_id']."'";
        }
        $sql .= " GROUP BY l.company_id, l.fiscal_year_id, l.people_type_id, l.people_id, l.ref_document_type_id, l.ref_document_identity" . "\n";
        $sql .= " HAVING outstanding != 0 " . "\n";
        if($sort_order) {
            $sql .= " ORDER BY " . implode(',',$sort_order);
        }
        $query = $this->conn->query($sql);
//        d(array($sql, $query),true);

        return $query->rows;
    }

//    Public function getDocumentOutstanding($filter){
//        // This Function is Used to get the OutStanding of Particular Document through Document Identity.
//        $sql  = "SELECT l.company_id, l.fiscal_year_id,l.company_branch_id, l.people_type_id,p.people_type, l.people_id,p.people_name, l.ref_document_type_id, l.ref_document_identity,l.ref_document_id, l.coa_id";
//        $sql .= " , CASE WHEN l.people_type_id = 2 THEN SUM(l.debit-l.credit) ELSE SUM(l.credit-l.debit) END as outstanding";
//        //$sql .= " , SUM(IF(l.ref_document_type_id=1,credit-debit,IF(l.ref_document_type_id=2,debit-credit,0))) AS outstanding";
//        $sql .= " FROM core_ledger l";
//        $sql .= " INNER JOIN vw_core_people p ON l.people_type_id = p.people_type_id AND l.people_id = p.people_id";
//        //$sql .= " AND l.coa_id = p.outstanding_account_id ";
//        $sql .= " AND (l.coa_id = p.outstanding_account_id OR l.coa_id = p.advance_account_id)";
//        $sql .= " WHERE l.company_id = '".$filter['company_id']."'";
//        $sql .= " AND l.company_branch_id = '".$filter['company_branch_id']."'";
//        $sql .= " AND l.fiscal_year_id = '".$filter['fiscal_year_id']."'";
//        $sql .= " AND l.people_type_id = '".$filter['people_type_id']."'";
//        $sql .= " AND l.people_id = '".$filter['people_id']."'";
//        $sql .= " AND l.ref_document_identity = '".$filter['ref_document_id']."'";
//        if(isset($filter['document_id']) && $filter['document_id']) {
//            $sql .= " AND l.document_id != '".$filter['document_id']."'";
//        }
//
//        $sql .= " GROUP BY l.company_id, l.fiscal_year_id,l.company_branch_id, l.people_type_id,p.people_type, l.people_id,p.people_name,l.ref_document_type_id, l.ref_document_identity, l.coa_id;";
//
//        $query = $this->conn->query($sql);
//        $row = $query->row;
////        d(array($sql, $row),true);
//        return $row;
//
//    }

    public function getLedgerReport($filter) {
        $sql = "SELECT 1 as sort_order, l.coa_id, l.display_name,l.level3_name";
        $sql .= ", '' AS document_type_id, '' AS document_id";
        $sql .= ", '".$filter['date_from']."' AS document_date, 'OPENING' AS document_identity, '' as remarks, '".$filter['date_from']."' AS created_at";
        $sql .= ", SUM(debit) AS debit, SUM(credit) AS credit";
        $sql .= " FROM ".$this->getView()." l";
        $sql .= " INNER JOIN vw_coa_all coa ON coa.coa_level3_id = l.coa_id AND coa.company_id = l.company_id ";
        $sql .= " WHERE l.company_id = '".$filter['company_id']."'";
        $sql .= " AND l.company_branch_id = '".$filter['company_branch_id']."'";
        $sql .= " AND l.fiscal_year_id = '".$filter['fiscal_year_id']."'";
        $sql .= " AND l.document_date >= '".$filter['session_from']."' AND l.document_date < '".$filter['date_from']."'";
        if($filter['coa_level1_id']) {
            $sql .= " AND coa.coa_level1_id = '".$filter['coa_level1_id']."'";
        }
        if($filter['coa_level2_id']) {
            $sql .= " AND coa.coa_level2_id = '".$filter['coa_level2_id']."'";
        }
        if($filter['coa_level3_id']) {
            $sql .= " AND l.coa_level3_id = '".$filter['coa_level3_id']."'";
        }
        if($filter['coa_level3_id']) {
            $sql .= " AND l.coa_id = '".$filter['coa_level3_id']."'";
        }
        $sql .= " GROUP BY display_name, coa_id";
        $sql .= " UNION All";
        $sql .= " SELECT 2 as sort_order, l.coa_id, l.display_name,l.level3_name, l.document_type_id, l.document_id, l.document_date, l.document_identity, l.remarks, l.created_at, debit, credit";
        $sql .= " FROM vw_ledger l";
        $sql .= " INNER JOIN vw_coa_all coa ON coa.coa_level3_id = l.coa_id AND coa.company_id = l.company_id ";
        $sql .= " WHERE l.company_id = '".$filter['company_id']."'";
        $sql .= " AND l.company_branch_id = '".$filter['company_branch_id']."'";
        $sql .= " AND l.fiscal_year_id = '".$filter['fiscal_year_id']."'";
        $sql .= " AND l.document_date >= '".$filter['date_from']."' AND l.document_date <= '".$filter['date_to']."'";
        if($filter['coa_level1_id']) {
            $sql .= " AND coa.coa_level1_id = '".$filter['coa_level1_id']."'";
        }
        if($filter['coa_level2_id']) {
            $sql .= " AND coa.coa_level2_id = '".$filter['coa_level2_id']."'";
        }
        if($filter['coa_level3_id']) {
            $sql .= " AND l.coa_level3_id = '".$filter['coa_level3_id']."'";
        }
        if($filter['coa_level3_id']) {
            $sql .= " AND l.coa_id = '".$filter['coa_level3_id']."'";
        }
        $sql .= " ORDER BY display_name, coa_id, sort_order, document_date, created_at ,document_identity";

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


    public function getLedgerAccountReport($filter) {

        $sql  = " SELECT `account_code`,`level3_name`,`level3_display_name`,`document_date`,`document_identity`,`remarks`,`debit`,`credit`";
        $sql .= " FROM vw_ledger l";
        $sql .= " WHERE l.company_id = '".$filter['company_id']."'";
        $sql .= " AND l.company_branch_id = '".$filter['company_branch_id']."'";
        $sql .= " AND l.fiscal_year_id = '".$filter['fiscal_year_id']."'";
        $sql .= " AND l.document_date >= '".$filter['date_from']."' AND l.document_date <= '".$filter['date_to']."'";
        $sql .= " AND `account_code` != ''";
        if($filter['coa_level1_id']) {
            $sql .= " AND coa.coa_level1_id = '".$filter['coa_level1_id']."'";
        }
        if($filter['coa_level2_id']) {
            $sql .= " AND coa.coa_level2_id = '".$filter['coa_level2_id']."'";
        }
        if($filter['coa_level3_id']) {
            $sql .= " AND l.coa_level3_id = '".$filter['coa_level3_id']."'";
        }
        if($filter['coa_level3_id']) {
            $sql .= " AND l.coa_id = '".$filter['coa_level3_id']."'";
        }
        $sql .= " ORDER BY `account_code`,`document_date`";

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

    public function getPurchaseLedger($document_type_id,$document_id) {
        $sql = "SELECT document_type_id,document_id,coa_id,account_code,display_name,remarks,SUM(debit) debit,SUM(credit) credit";
        $sql .= " FROM vw_ledger ";
        $sql .= " WHERE document_type_id = '".$document_type_id."'";
        $sql .= " AND document_id = '".$document_id."'";
        $sql .= " AND fiscal_year_id = '".$this->session->data['fiscal_year_id']."'  ";
        $sql .= " GROUP BY coa_level3_id";
        $sql .= " ORDER BY debit desc,credit";

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

    public function getTransferLedger($document_type_id,$document_id,$company_branch_id) {
        $sql = "SELECT document_type_id,document_id,coa_level3_id,account_code,level3_name,SUM(debit) debit,SUM(credit) credit";
        $sql .= " FROM vw_ledger ";
        $sql .= " WHERE document_type_id = '".$document_type_id."'";
        $sql .= " AND document_id = '".$document_id."'";
        $sql .= " AND company_branch_id = '".$company_branch_id."'";
        $sql .= " AND fiscal_year_id = '".$this->session->data['fiscal_year_id']."'  ";
        $sql .= " GROUP BY coa_level3_id";
        $sql .= " ORDER BY debit desc,credit";

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

    public function getStockTransferLedger($document_type_id,$document_id,$company_branch_id) {
        $sql = "SELECT document_type_id,document_id,coa_level3_id,account_code,level3_name,debit,credit ";
        $sql .= " FROM vw_ledger ";
        $sql .= " WHERE document_type_id = '".$document_type_id."'";
        $sql .= " AND document_id = '".$document_id."'";
        $sql .= " AND company_branch_id = '".$company_branch_id."'";
        $sql .= " AND fiscal_year_id = '".$this->session->data['fiscal_year_id']."'  ";
        $sql .= " AND (debit <> '0.0000' OR credit <> '0.0000') ";
        $sql .= " ORDER BY debit desc,credit";

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


    public function getTransactionLedger($document_type_id,$document_id) {
        $sql = "SELECT document_type_id,document_id,coa_id,account_code,account_name as account, display_name,remarks,cheque_no,SUM(debit) debit,SUM(credit) credit";
        $sql .= " FROM vw_ledger ";
        $sql .= " WHERE document_type_id = '".$document_type_id."'";
        $sql .= " AND document_id = '".$document_id."'";
        $sql .= " AND fiscal_year_id = '".$this->session->data['fiscal_year_id']."'  ";
        $sql .= " GROUP BY coa_id";
        $sql .= " ORDER BY debit desc,credit";

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

    public function getTotalRevenue() {
        $sql = " SELECT SUM(credit-debit) AS total_revenue";
        $sql .= " FROM vw_ledger l ";
        $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 gl_type_id = 4";

        $query = $this->conn->query($sql);
        $row = $query->row;
        return $row['total_revenue'];
    }

    public function getTotalExpense(){
        $sql = " SELECT SUM(debit-credit) AS total_expense";
        $sql .= " FROM vw_ledger l ";
        $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 gl_type_id = 5";

        $query = $this->conn->query($sql);
        $row = $query->row;
        return $row['total_expense'];
    }

    public function getTotalAsset(){
        $sql = " SELECT SUM(debit-credit) AS total_asset";
        $sql .= " FROM vw_ledger l ";
        $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 gl_type_id = 1";

        $query = $this->conn->query($sql);
        $row = $query->row;
        return $row['total_asset'];
    }

    public function getTotalCash(){
        $sql = " SELECT SUM(debit-credit) AS total_cash";
        $sql .= " FROM vw_ledger l ";
        $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 level2_name = 'Cash'";

        $query = $this->conn->query($sql);
        $row = $query->row;
        return $row['total_cash'];
    }

    public function getTotalBank(){
        $sql = " SELECT SUM(debit-credit) AS total_bank";
        $sql .= " FROM vw_ledger l ";
        $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 level2_name = 'Bank'";

        $query = $this->conn->query($sql);
        $row = $query->row;
        return $row['total_bank'];
    }

    public function getTotalLiability(){
        $sql = " SELECT SUM(credit-debit) AS total_liability";
        $sql .= " FROM vw_ledger l ";
        $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 gl_type_id = 2";

        $query = $this->conn->query($sql);
        $row = $query->row;
        return $row['total_liability'];
    }

    public function getTotalEquity(){
        $sql = " SELECT SUM(credit-debit) AS total_equity";
        $sql .= " FROM vw_ledger l ";
        $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 gl_type_id = 3";

        $query = $this->conn->query($sql);
        $row = $query->row;
        return $row['total_equity'];
    }

    public function getTrialBalanceL1($filter=array()) {
        $sql = "";
        $sql .= " SELECT `level1_display_name`";
        $sql .= " , CASE WHEN SUM(debit-credit)>0 THEN SUM(debit-credit) ELSE 0 END AS debit";
        $sql .= " , CASE WHEN SUM(debit-credit)<0 THEN SUM(credit-debit) ELSE 0 END AS credit";
        $sql .= " FROM `vw_ledger`";
        if($filter) {
            if(is_array($filter)) {
                //$table_columns = $this->getTableColumns($this->getTable());
                $implode = array();
                foreach($filter as $column => $value) {
                    //if(in_array($column,$table_columns)) {
                    $implode[] = "`$column`='$value'";
                    //}
                }
                if($implode)
                    $sql .= " WHERE " . implode(" AND ", $implode);
            } else {
                $sql .= " WHERE " . $filter;
            }
        }
        $sql .= " GROUP BY `level1_display_name`";

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

}

?>