<?php

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of data_errors
 *
 * @author User
 */
class data_errors extends mainController {

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

    public function landing() {
        echo 1;
    }

    public function __construct() {
//        parent::__construct(false);
        $this->db = DBConnectorFactory::loadDriver();
        $this->year_id = Session::getCurrentYearID();
    }

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

    public function check_unbalanced_jvs() {
        $data = array();
        $query = $this->db->query("SELECT JV_ID,Transaction_Date,
           sum(if(`Transaction_Type`='Debit',(`Transaction_Amount`*`Current_Ex_Rate`),0)) as total_debit,
           sum(if(`Transaction_Type`='Credit',(`Transaction_Amount`*`Current_Ex_Rate`),0)) as total_credit
           FROM `tbl_accounting_transactions` 
           WHERE is_actual = 0 and current_year_id='" . $this->year_id . "' group by `JV_ID` having abs(total_debit - total_credit ) > 0.000001");
        while ($row = $this->db->assoc($query)) {
            $row['diff'] = $row['total_debit'] - $row['total_credit'];
            $data[] = $row;
        }
        echo"<pre>";
        die(print_r($data));
    }

    public function check_unbalanced_second_jvs() {
        $data = array();
        $query = $this->db->query("SELECT JV_ID,Transaction_Date,
           sum(if(`Transaction_Type`='Debit',(`Transaction_Amount`*`second_currency_rate`),0)) as total_debit,
           sum(if(`Transaction_Type`='Credit',(`Transaction_Amount`*`second_currency_rate`),0)) as total_credit
           FROM `tbl_accounting_transactions` 
           WHERE is_actual = 0 and current_year_id='" . $this->year_id . "' group by `JV_ID` having abs(total_debit - total_credit ) > 0.01");
        while ($row = $this->db->assoc($query)) {
            $row['diff'] = $row['total_debit'] - $row['total_credit'];
            $data[] = $row;
        }
        echo"<pre>";
        die(print_r($data));
    }

    public function check_used_parent_accs() {
        $data = array();
        $query = $this->db->query('select distinct Account_ID,t.JV_ID,t.Transaction_Date,
            c.Account_Code,c.Account_Name
            from tbl_accounting_transactions t
            join tbl_chart_of_accounts c using(Account_ID)
            where t.Account_ID in (select distinct Parent_ID from tbl_chart_of_accounts ) order by t.Account_ID');
    }

    public function check_diff_trans_location_qty() {
        $data = array();
        $query = $this->db->query("SELECT *, count(t.Transaction_ID) as transaction_count,
            (sum(t.Product_Quantity * if(t.Operation_Type='-',-1,+1)) - l.Product_Depot_Quantity) as qty_diff
            FROM `tbl_stock_product_transactions` t 
        inner join tbl_stock_product_location l on t.Product_ID = l.Location_Product_ID
        and t.Depot_ID = l.Product_Depot_ID
        group by t.Product_ID, t.Depot_ID
        having abs(qty_diff) > 0.0001");
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        echo"<pre>";
        die(print_r($data));
    }

    public function check_diff_main_prod_location_qty() {
        $data = array();
        $query = $this->db->query('select * from tbl_stock_products p 
        inner join tbl_stock_product_location l on p.Product_ID = l.Location_Product_ID
        group by p.Product_ID
        having sum(l.Product_Depot_Quantity) <> p.Product_Quantity');
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        echo"<pre>";
        die(print_r($data));
    }

}
