<?php

class Corporate_model extends CI_Model {

    function __construct() {
        parent::__construct();
    }
    
    function get_ptmc(){
        $con['pf_type'] = 'tmc';
        $con['pf_corporateid'] = $this->session->userdata['recon_sess_i']['u_corporateid'];
        $this->db->from('p_files tmc');
        $this->db->join('corporates c','c.c_id=tmc.pf_corporateid','inner');
        $this->db->join('users u','u.u_id=tmc.pf_uploadedby','inner');
        $this->db->where($con);
        return $this->db->get();
    }
    
    function get_ppay(){
        $con['pf_type'] = 'pay';
        $con['pf_corporateid'] = $this->session->userdata['recon_sess_i']['u_corporateid'];
        $this->db->from('p_files tmc');
        $this->db->join('corporates c','c.c_id=tmc.pf_corporateid','inner');
        $this->db->join('users u','u.u_id=tmc.pf_uploadedby','inner');
        $this->db->where($con);
        return $this->db->get();
    }
    
    function get_pc2(){
        $con['pf_type'] = 'c2';
        $con['pf_corporateid'] = $this->session->userdata['recon_sess_i']['u_corporateid'];
        $this->db->from('p_files tmc');
        $this->db->join('corporates c','c.c_id=tmc.pf_corporateid','inner');
        $this->db->join('users u','u.u_id=tmc.pf_uploadedby','inner');
        $this->db->where($con);
        return $this->db->get();
    }
    
    function get_pc1(){
        $con['pf_type'] = 'c1';
        $con['pf_corporateid'] = $this->session->userdata['recon_sess_i']['u_corporateid'];
        $this->db->from('p_files tmc');
        $this->db->join('corporates c','c.c_id=tmc.pf_corporateid','inner');
        $this->db->join('users u','u.u_id=tmc.pf_uploadedby','inner');
        $this->db->where($con);
        return $this->db->get();
    }
    
    function get_tmc(){
        $con['f_type'] = 'tmc';
        $con['is_active'] = 1;
        $con['f_corporateid'] = $this->session->userdata['recon_sess_i']['u_corporateid'];
        $this->db->from('files tmc');
        $this->db->join('corporates c','c.c_id=tmc.f_corporateid','inner');
        $this->db->join('users u','u.u_id=tmc.f_uploadedby','inner');
        $this->db->where($con);
        return $this->db->get();
    }
   
    function get_tmc_inv($tdid = null){
        if($tdid)
            $con['ti_tdid'] = $tdid;
        $con['ti_corpid'] = $this->session->userdata['recon_sess_i']['u_corporateid'];
        
        $gdid = $this->db->select('td_gstr2a_match_id')->from('tmc_data')->where('td_id',$tdid)->get()->row('td_gstr2a_match_id');
        
        if($gdid != '')
            return $this->db->query('select * from airline_invoice ti left join (select gd_id,gd_inv_no,gd_inv_file from gstr2a_data) gd on ti.ti_gst_nub=gd.gd_inv_no where ti_tdid = "'.$tdid.'"');
//            return $this->db->query('select * from airline_invoice ti left join (select gd_id,gd_inv_no,gd_inv_file from gstr2a_data) gd on ti.ti_gst_nub=gd.gd_inv_no where ti_tdid = "'.$tdid.'" union select * from airline_invoice ti left join (select gd_id,gd_inv_no,gd_inv_file from gstr2a_data) gd on ti.ti_gst_nub=gd.gd_inv_no where ti_gdid = "'.$gdid.'"');
        else
            return $this->db->from('airline_invoice ti')->join('(select gd_id,gd_inv_no,gd_inv_file from gstr2a_data) gd','ti.ti_gst_nub=gd.gd_inv_no','left')->where($con)->get();
//        return $this->db->get_where('tmc_invoice',$con);
    }
    
    function get_tmc_file($fid){
        $con['f_type'] = 'tmc';
        $con['f_corporateid'] = $this->session->userdata['recon_sess_i']['u_corporateid'];
        $con['f_id'] = $fid;
        return $this->db->get_where('files',$con)->row('f_file');
    }
    
    function get_p_tmc_file($fid){
        // used to get the file id of BTA/CTA//
        $con['pf_type'] = 'c1';
        $con['pf_corporateid'] = $this->session->userdata['recon_sess_i']['u_corporateid'];
        $con['pf_id'] = $fid;
        return $this->db->get_where('p_files',$con)->row('pf_file');
    }
    function get_p_tmc_file_tmc($fid){
        // used to get the file id of PTmc//
        $con['pf_type'] = 'tmc';
        $con['pf_corporateid'] = $this->session->userdata['recon_sess_i']['u_corporateid'];
        $con['pf_id'] = $fid;
        return $this->db->get_where('p_files',$con)->row('pf_file');
    }
    function get_p_c2_file($fid){
        // used to get the file id of corporate (p_c2)//
        $con['pf_type'] = 'c2';
        $con['pf_corporateid'] = $this->session->userdata['recon_sess_i']['u_corporateid'];
        $con['pf_id'] = $fid;
        return $this->db->get_where('p_files',$con)->row('pf_file');
    }
    
    
    function get_gstno($gn_id = null,$isactive = null){
        if($gn_id)
            $con['gn_id'] = $gn_id;
        if($isactive)
            $con['gn_isactive'] = $isactive;
        $con['gn_corp_id'] = $this->session->userdata['recon_sess_i']['u_corporateid'];
        $this->db->from('gstno g');
        $this->db->join('corporates c','c.c_id=g.gn_corp_id','inner');
        $this->db->join('users u','u.u_id=g.gn_createdby','inner');
        $this->db->where($con);
        return $this->db->get();
    }
    
    function get_cts(){
        $con['f_type'] = 'cts';
        $con['f_corporateid'] = $this->session->userdata['recon_sess_i']['u_corporateid'];
        $this->db->from('files f');
        $this->db->join('corporates c','c.c_id=f.f_corporateid','inner');
        $this->db->join('users u','u.u_id=f.f_uploadedby','inner');
        $this->db->where($con);
        return $this->db->get();
    }
    
    function get_gstr2a(){
        $con['f_type'] = 'gstr2a';
        $con['is_active'] = '1';
        $con['f_corporateid'] = $this->session->userdata['recon_sess_i']['u_corporateid'];
        $this->db->from('files f');
        $this->db->join('corporates c','c.c_id=f.f_corporateid','inner');
        $this->db->join('users u','u.u_id=f.f_uploadedby','inner');
        $this->db->where($con);
        return $this->db->get();
    }
    
    function get_gstr2a_file($fid){
        $con['f_type'] = 'gstr2a';
        $con['f_corporateid'] = $this->session->userdata['recon_sess_i']['u_corporateid'];
        $con['f_id'] = $fid;
        return $this->db->get_where('files',$con)->row('f_file');
    }

    //dashboard 
    public function total_count($from_date , $to_date)
    {
        $this->db->select('*');
        $this->db->from('gstr2a_data');
        $this->db->where('gd_inv_date >=', $from_date);
        $this->db->where('gd_inv_date <=', $to_date);
        $this->db->where('is_active ', 1);
        $query = $this->db->count_all_results();
        return $query;
    }

    //Inv missing
    public function inv_missing_count($from_date , $to_date)
    {
        $this->db->select('*');
        $this->db->from('gstr2a_data');
        $this->db->where('gd_inv_date >=', $from_date);
        $this->db->where('gd_inv_date <=', $to_date);
        $this->db->where('is_active ', 1);
        $this->db->where('inv_status', 0);
        $query = $this->db->count_all_results();
        return $query;
    }

    //Transaction Matched
    public function trans_matching_count($from_date , $to_date)
    {
        $this->db->select('*');
        $this->db->from('gstr2a_data');
        $this->db->where('gd_inv_date >=', $from_date);
        $this->db->where('gd_inv_date <=', $to_date);
        $this->db->where('is_active ', 1);
        $this->db->where('inv_status', 1);
        $query = $this->db->count_all_results();
        return $query;
    }
    //TMC Amount Short
    public function tmc_amount_short_count($from_date , $to_date)
    {
        $this->db->select('*');
        $this->db->from('gstr2a_data');
        $this->db->where('gd_inv_date >=', $from_date);
        $this->db->where('gd_inv_date <=', $to_date);
        $this->db->where('is_active ', 1);
        $this->db->where('inv_status', 2);
        $query = $this->db->count_all_results();
        return $query;
    }
    //Airline Amount Short
    public function airline_amount_short_count($from_date , $to_date)
    {
        $this->db->select('*');
        $this->db->from('gstr2a_data');
        $this->db->where('gd_inv_date >=', $from_date);
        $this->db->where('gd_inv_date <=', $to_date);
        $this->db->where('is_active ', 1);
        $this->db->where('inv_status', 3);
        $query = $this->db->count_all_results();
        return $query;
    }
}