<?php
defined('BASEPATH') or exit('No direct script access allowed');

class Corporate extends CI_Controller
{

    /**
     * Index Page for this controller.
     *
     * Maps to the following URL
     * 		http://example.com/index.php/welcome
     *	- or -
     * 		http://example.com/index.php/welcome/index
     *	- or -
     * Since this controller is set as the default controller in
     * config/routes.php, it's displayed at http://example.com/
     *
     * So any other public methods not prefixed with an underscore will
     * map to /index.php/welcome/<method_name>
     * @see https://codeigniter.com/user_guide/general/urls.html
     */

    function __construct()
    {
        parent::__construct();
        $this->load->model('Corporate_model', 'corp');
        $this->db2 = $this->load->database('default', TRUE);
        // $c_id = $this->session->userdata['recon_sess_i']['u_corporateid'];
        // $this->db->select('payment');
        // $this->db->from('corporates');
        // $this->db->where('c_id',  intval($c_id));
        // $query = $this->db->get();
        // $cor_id = $this->session->set_flashdata('corporate_id', $query->result()[0]->payment);
        //force_ssl();
    }

    public function index()
    {
        redirect('corporate/dashboard');
    }

    public function dashboard()
    {
        $this->checksess();
        $this->load->view('corporate/dashboard');
    }

    public function get_inv_gst1()
    {
        $this->checksess();
        $this->load->database('default', TRUE);
        $res = $this->db2->where('invoice_no', $this->input->post('inv_no'))->get('air_invoice_details')->result();
        echo json_encode($res);
    }

    public function get_tic_gst1()
    {
        $this->checksess();
        $this->load->database('default', TRUE);
        $res = $this->db2->where('pnr', $this->input->post('inv_no'))->or_where('ticket_no', $this->input->post('inv_no'))->get('air_invoice_details')->result();
        echo json_encode($res);
    }

    public function get_inv_no()
    {
        $this->checksess();
        $this->load->database('default', TRUE);
        $res = $this->db2->get('air_invoice_details')->result();
        echo json_encode($res);
    }

    public function tmc_dashboard()
    {
        $this->checksess();
        $this->load->view('corporate/dashboard_tmc');
    }

    public function get_alike_tkts($gid = null)
    {
        $this->checksess();
        $refgsttkt = $this->input->post('refgsttkt');

        $res = $this->db->query('select *,am_code as supplier_code from gstr2a_data gd left join (select *,group_concat(td_gst_inv) g_td_gst_inv,sum(td_net) td_net_ref from tmc_data where td_show_status = 1 group by td_tkt) td on gd.gd_tkt_no=td.td_tkt left join airline_master am on am.am_name=gd.gd_supplier_type where gd_id = "' . $gid . '"');
        $res2 = new stdClass();
        foreach ($res->result() as $res1) {
            $res2->corpname = 'test';
            $res2->corpgstin = 'test';

            $res2->gd_supplier_code = $res1->gd_supplier_type . '^' . strtoupper($res1->supplier_code);

            $res2->gd_supplier_name = $res1->gd_supplier_name;
            //                $res2->gd_supplier_type = $res1->gd_supplier_type;
            $res2->gd_inv_no = $res1->gd_inv_no;
            $res2->gd_tkt_no = $res1->gd_tkt_no;
            $res2->td_tkt = $res1->td_tkt;
            $res2->gd_doc_type = $res1->gd_doc_type;
            $res2->gd_inv_date = $res1->gd_inv_date;
            $res2->td_net_ref = $res1->td_net_ref;
            $res2->gd_inv_value = $res1->gd_inv_value;
            $res2->td_net = $res1->td_net;

            if ($res1->td_tkt_fare == '')
                $res1->td_tkt_fare = 0;
            $res2->td_tkt_fare = $res1->td_tkt_fare;
            $res2->variance = $res1->gd_inv_value - $res1->td_tkt_fare;
            $res2->gd_rate_perc = $res1->gd_rate_perc;
            $res2->gd_taxable_value = $res1->gd_taxable_value;
            $res2->gd_integrated_tax = $res1->gd_integrated_tax;
            $res2->gd_central_tax = $res1->gd_central_tax;
            $res2->gd_state_tax = $res1->gd_state_tax;
            //                if($res1->gd_remarks == '')
            //                    $res1->gd_remarks = '';
            //                $res2->gd_remarks = $res1->gd_remarks;

            $variance = $res1->gd_inv_value - $res1->td_tkt_fare;
            $res2->variance = $variance;

            if ($variance == 0)
                $res2->gd_remarks = 'Transaction Matched';
            else if ($variance > 0)
                $res2->gd_remarks = 'TMC Amount Short';
            else
                $res2->gd_remarks = 'Airline Amount Short';

            if ($res1->gd_inv_file == '')
                $res2->gd_file_status = 'Invoice Missing';
            else
                $res2->gd_file_status = '';


            $res2->inv_ref = $res1->td_gst_inv;

            //                $td_gst_inv_ary = explode(',',$res1->td_gst_inv);
            //                
            //                for($i=count($td_gst_inv_ary);$i>0;$i--){
            //                    $varname = 'td_gst_inv_'.$i;
            //                    $res2->$varname = $td_gst_inv_ary[$i-1];
            //                }
            //                for($i=count($td_gst_inv_ary)+1;$i<5;$i++){
            //                    $varname = 'td_gst_inv_'.$i;
            //                    $res2->$varname = '';
            //                }
            //                $res2->td_gst_inv = $res1->td_gst_inv;
            $res2->gd_id = $res1->gd_id;

            $res2->tkt_like = '';

            if ($res1->td_gstr2a_match_id != 1) {
                $res = $this->db->query('select td_tkt,td_gstr2a_match_id from tmc_data where td_tkt like "%' . $refgsttkt . '%" and td_show_status = 1 group by td_tkt');
                if ($res->num_rows() > 0) {

                    $htmlData = '<select class="form-control" onchange="bind_tkt(this.value,' . $res1->gd_id . ')" id="sel_' . $res1->gd_id . '"><option value="">Select</option>';
                    foreach ($res->result() as $res1) {
                        $htmlData .= '<option ' . (($res1->td_gstr2a_match_id == $res2->gd_id) ? " selected='selected'" : "") . ' >' . $res1->td_tkt . '</option>';
                    }
                    $htmlData .= '</select>';
                    $res2->tkt_like = $htmlData;
                }
            } else
                $res2->tkt_like = $res2->td_tkt;
        }

        echo json_encode($res2);
    }

    public function upd_tkt_bind()
    {
        $gdid = $this->input->post('gdid');
        $td_tkt = $this->input->post('td_tkt');
        $con['td_tkt'] = $td_tkt;
        $data['td_gstr2a_match_id'] = $gdid;

        $this->db->where($con);
        $this->db->update('tmc_data', $data);

        $gd_inv_no = $this->db->query('select gd_inv_no from gstr2a_data where gd_id = "' . $gdid . '" limit 1')->row('gd_inv_no');

        $tdid = $this->db->query('select * from tmc_data where td_gstr2a_match_id = "' . $gdid . '" limit 1')->row('td_id');
        $this->db->query('update airline_invoice set ti_tdid = "' . $tdid . '" where (ti_tdid = "" or ti_tdid is null) and ti_gdid = "' . $gdid . '"');
        $this->db->query('update airline_invoice set ti_gst_nub = "' . $gd_inv_no . '" where (ti_gst_nub = "" or ti_gst_nub is null) and ti_gdid = "' . $gdid . '"');

        $this->gen_gstr2a_recon($gdid);
    }

    public function upload_tmc_inv()
    {
        $fary['status'] = 0;
        if (isset($_FILES)) {
            //            print_r($_FILES['gd_gst_inv']['name']); exit;
            $gdid = $this->input->post('gdid');
            $gd_inv_no = '';
            $gd_inv_value = '';
            $gddata = $this->db->select('gd_inv_no,gd_inv_value')->from('gstr2a_data')->where('gd_id', $gdid)->get();
            if ($gddata->num_rows() > 0) {
                foreach ($gddata->result() as $gdd) {
                    $gd_inv_no = $gdd->gd_inv_no;
                    $gd_inv_value = $gdd->gd_inv_value;
                }
            }
            $filename = $gdid . '_' . time() . '___' . str_replace(' ', '_', $_FILES['gd_gst_inv']['name']);
            //            $config['file_name'] = $filename;
            $config['file_name'] = $filename;

            if (!is_dir(dirname(dirname(dirname(__FILE__))) . '/uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'] . '/airline_invoice')) {
                mkdir(dirname(dirname(dirname(__FILE__))) . '/uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'] . '/airline_invoice');
            }

            $config['upload_path'] = './uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'] . '/airline_invoice/';
            $config['allowed_types'] = '*';
            $config['max_size'] = 3000;
            $this->load->library('upload', $config);
            if (!$this->upload->do_upload('gd_gst_inv')) {
                $error = array('error' => $this->upload->display_errors());
                echo $error['error'];
            } else {
                $data = array('upload_data' => $this->upload->data());
                //                $din['gd_inv_file'] = $filename;
                //                $din['td_file_status'] = 'yes';
                //                $cin['gd_id'] = $gdid;
                //                $this->db->where($cin)->update('gstr2a_data',$din);

                $tdid = $this->db->query('select td_id from tmc_data where td_gstr2a_match_id = "' . $gdid . '"')->row('td_id');

                $ins['ti_corpid'] = $this->session->userdata['recon_sess_i']['u_corporateid'];
                $ins['ti_gst_nub'] = $gd_inv_no;
                $ins['ti_amount'] = $gd_inv_value;
                $ins['ti_file_name'] = $filename;
                $ins['ti_gdid'] = $gdid;
                $ins['ti_tdid'] = $tdid;
                $this->db->insert('airline_invoice', $ins);

                //                $fary['status'] = 1;
                //                $fary['refid'] = $filename;
                // $this->gen_gstr2a_recon($gdid);
                echo true;
            }
        }
        //        echo json_encode($fary);
    }

    public function tmc_del()
    {
        $tdid = $this->input->post('tdid');
        $corpid = $this->session->userdata['recon_sess_i']['u_corporateid'];

        $this->db->query('update tmc_data t1 inner join (select * from files where f_type="tmc" and f_corporateid = "' . $corpid . '") t2 on t1.td_tfid=t2.f_id set td_show_status = 0 where td_id = "' . $tdid . '"');

        echo 1;
    }

    //    public function tmc_del(){
    //        $tdid = $this->input->post('tdid');
    //        $con['f_corporateid'] = $this->session->userdata['recon_sess_i']['u_corporateid'];
    //        $con['f_type'] = 'tmc';
    //        $con['td_id'] = $tdid;
    //        
    //        $data['td_show_status'] = 0;
    //        
    //        $this->db->where($con);
    //        $this->db->update('tmc_data',$data);
    //        
    ////        $this->db->query('delete t1 from tmc_data t1 inner join (select * from files where f_type="tmc" and f_corporateid = "'.$corpid.'") t2 on t1.td_tfid=t2.f_id where td_id = "'.$tdid.'"');
    //        
    //        echo 1;
    //    }


    public function get_td_gst_tkts()
    {
        $gdid = $this->input->post('gdid');
        $fdatas['frec'] = 0;
        $fdatas['drec'] = 0;
        $con['td_gstr2a_match_id'] = $gdid;
        $con['td_show_status'] = 1;
        $gd_inv_no = $this->db->select('gd_inv_no')->from('gstr2a_data')->where('gd_id', $gdid)->get()->row('gd_inv_no');
        $j = 0;
        $files = $this->db->select('*')->from('airline_invoice')->where('ti_gst_nub', $gd_inv_no)->get();
        //        if($files->num_rows()>0){
        //            $fdatas['frec'] = 1;
        //            foreach($files->result() as $fi){
        //                $fdata[$j] = $fi;
        //                $j++;
        //            }
        //            $fdatas['fdata'] = $fdata;
        //        }

        $res = $this->db->select('td_id,td_gst_inv,td_net')->from('tmc_data')->where($con)->get();
        $j = 0;
        if ($res->num_rows() > 0) {
            $fdatas['drec'] = 1;
            foreach ($res->result() as $resi) {
                $adata[$j] = $resi;
                $j++;
            }
            $fdatas['data'] = $adata;
        }
        echo json_encode($fdatas);
    }

    public function get_tmc_airline_invoice()
    {
        $tdid = $this->input->post('tdid');
        $res = $this->corp->get_tmc_inv($tdid);
        $data['status'] = 0;
        if ($res->num_rows() > 0) {
            $data['status'] = 1;
            foreach ($res->result() as $resi) {
                if ($resi->gd_id == '')
                    $resi->gd_id = '';
                if ($resi->gd_inv_file == '')
                    $resi->gd_inv_file = '';
                $info[] = $resi;
            }
            $data['data'] = $info;
        }
        echo json_encode($data);
    }

    //    public function ainvchk($time,$fname){
    //        $timeref = time() + 1;
    //        if(file_exists(dirname( dirname( dirname(__FILE__) ) ).'/uploads/'.$this->session->userdata['recon_sess_i']['u_corporateid'].'/airline_invoice/'.$time.'___'.$fname))
    //            $this->ainvchk($timeref,$fname);
    //        else
    //            return $timeref.'___'.$fname;
    //    }

    public function add_tmc_airline_invoices()
    {
        $tdid = $this->input->post('tmc_ref');
        $inputs = $this->input->post('in');
        //        print_r($_FILES['in']); exit;
        //        print_r($_FILES['in']['name'][1]['invfile']); exit;
        $timeref = time();
        $inputs = array_values($inputs);
        $_FILES['in']['name'] = array_values($_FILES['in']['name']);
        //        $_FILES['in']['type'] = array_values($_FILES['in']['type']);
        //        $_FILES['in']['tmp_name'] = array_values($_FILES['in']['tmp_name']);
        //        $_FILES['in']['error'] = array_values($_FILES['in']['error']);
        //        print_r($inputs);
        //        print_r($_FILES);
        for ($i = 0; $i < count($inputs); $i++) {
            $label = $inputs[$i]['gst_nub'];
            $amount = $inputs[$i]['amount'];
            $manual = $inputs[$i]['manual'];
            $gdid = $inputs[$i]['gdid'];
            ++$timeref;
            $filename = $timeref . '___' . $_FILES['in']['name'][$i]['invfile'];

            $filename = str_replace(' ', '_', $filename);
            //            echo $filename;
            //            $config['file_name'] = $filename;
            $config['upload_path'] = './uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'] . '/airline_invoice/';
            $config['allowed_types'] = '*';
            $config['max_size'] = 3000;
            $this->load->library('upload', $config);

            //            $fileary = explode('.',$_FILES['in']['name'][$i]['invfile']);
            //                    
            //            // Define new $_FILES array - $_FILES['file']
            $_FILES['file']['name'] = $filename;
            //
            $_FILES['file']['type'] = $_FILES['in']['type'][$i]['invfile'];
            $_FILES['file']['tmp_name'] = $_FILES['in']['tmp_name'][$i]['invfile'];
            $_FILES['file']['error'] = $_FILES['in']['error'][$i]['invfile'];
            $_FILES['file']['size'] = $_FILES['in']['size'][$i]['invfile'];

            if (!$this->upload->do_upload('file')) {
                $error = array('error' => $this->upload->display_errors());
                echo $error['error'];
            } else {
                $data = array('upload_data' => $this->upload->data());
                $insary['ti_tdid'] = $tdid;
                $insary['ti_corpid'] = $this->session->userdata['recon_sess_i']['u_corporateid'];
                $insary['ti_file_name'] = $filename;
                $insary['ti_gst_nub'] = $label;
                $insary['ti_gdid'] = $gdid;
                $insary['ti_amount'] = $amount;
                $insary['ti_manual'] = $manual;
                $this->db->insert('airline_invoice', $insary);

                $insert_id = $this->db->insert_id();
                if ($insert_id != '') {

                    $this->db->where('td_id', $tdid);
                    $this->db->update('tmc_data', array('td_gstr2a_match_id' => $gdid));
                }
                // $this->upd_tkt_bind($gdid,$tdid);

            }
        }
        echo $tdid;
    }

    public function getgstamt()
    {
        $gd_inv_no = $this->input->post('refid');
        //        $con['gd_inv_no'] = $this->input->post('refid');
        //        $this->db->from('gstr2a_data');
        //        $this->db->where($con);
        //        $res = $this->db->get();

        $res = $this->db->query('select * from gstr2a_data gd left join airline_invoice ai on gd.gd_id=ai.ti_gdid where gd_inv_no = "' . $gd_inv_no . '"');

        $data['status'] = 0;
        if ($res->num_rows() > 0) {
            $data['status'] = 1;
            $data['gdid'] = '';
            foreach ($res->result() as $r) {
                $data['amt'] = $r->gd_inv_value;
                $data['gdid'] = $r->gd_id;
                if ($r->ti_file_name == '')
                    $data['ti_file_name'] = '';
                else
                    $data['ti_file_name'] = $r->ti_file_name;
            }
        }
        echo json_encode($data);
    }

    public function gen_tmc_recon()
    {
        $this->checksess();
        $dateParts = explode(' - ', $this->input->post('date'));
        $fromDate = $dateParts[0];
        $toDate = $dateParts[1];
        $fromdate = (\DateTime::createFromFormat('d/m/Y', $fromDate))->format('Y/m/d');
        $todate = \DateTime::createFromFormat('d/m/Y', $toDate)->format('Y/m/d');
        // $fromdate= $this->input->post('from');

        // $todate = $this->input->post('to');

        //        if($gid)
        //            $res = $this->db->query('select *,am_code as supplier_code from gstr2a_data gd left join (select *,group_concat(td_gst_inv) g_td_gst_inv,sum(td_net) td_net_ref from tmc_data where td_show_status = 1 group by td_tkt) td on gd.gd_id=td.td_gstr2a_match_id left join airline_master am on am.am_name=gd.gd_supplier_type where gd_id = "'.$gid.'"');
        //        else
        $res = $this->db->query('select * from tmc_data td left join (select *,sum(ti_amount) s_amt from airline_invoice group by ti_tdid) ti on ti.ti_tdid = td.td_id where  is_active = 1 and td_inv_date between "' . $fromdate . '" and "' . $todate . '" order by td_id desc');
        // print_r($res);die();

        foreach ($res->result() as $key => $value) {
            // print_r($value);die();
            if ($value) {
                $this->load->database('default', TRUE);
                $res_2 = $this->db2->where('pnr', $value->td_tkt)->or_where('ticket_no', $value->td_tkt)->get('air_invoice_details')->result();
                if (isset($res_2[0])) {
                    // $uptData['inv_received'] = 1;
                    // $this->db->where('td_id', $value->td_id);
                    // $data = $this->db->update('tmc_data',$uptData);
                    $value->inv = $res_2[0]->invoice_no;
                    $value->tkt = $res_2[0]->ticket_no;
                    $value->amt = $res_2[0]->total_inv_value;
                    $value->tpnr = $res_2[0]->pnr;
                    $value->tdate = $res_2[0]->invoice_dt;
                } else {
                    $value->inv = " ";
                }
            }
        }
        $fres['data'] = array();
        $res2 = new stdClass();
        foreach ($res->result() as $res1) {
            if ($res1->ti_tdid == '') {
                $res1->d_remarks = 'Invoice not received';
                $data['inv_received'] = 0;
                $this->db->where('td_id', $res1->ti_tdid);
                $this->db->update('tmc_data', $data);
            } else {
                $payremarks = ($res1->s_amt * 100) / $res1->td_tkt_fare;
                if ($payremarks > 70) {
                    $data['inv_received'] = 1;
                    $this->db->where('td_id', $res1->ti_tdid);
                    $this->db->update('tmc_data', $data);
                    $res1->d_remarks = 'Invoice Received(Manual)';
                } else {
                    $data['inv_received'] = 2;
                    $this->db->where('td_id', $res1->ti_tdid);
                    $this->db->update('tmc_data', $data);
                    $res1->d_remarks = 'Parital Invoice Received';
                }
            }
            $res1->tkt_like = $res1->td_tkt;
            $res1->td_inv_date = date('d-m-Y', strtotime($res1->td_inv_date));
            $fres['data'][] = $res1;
        }
        echo json_encode($fres);
    }

    public function gen_gstr2a_recon($gid = null)
    {
        $this->checksess();
        // $fromdate = $this->input->post('from');
        // $todate = $this->input->post('to');
        $dateParts = explode(' - ', $this->input->post('date'));
        $fromDate = $dateParts[0];
        $toDate = $dateParts[1];
        $fromdate = (\DateTime::createFromFormat('d/m/Y', $fromDate))->format('Y/m/d');
        $todate = \DateTime::createFromFormat('d/m/Y', $toDate)->format('Y/m/d');
        if ($gid)
            $res = $this->db->query('select *,am_code as supplier_code from gstr2a_data where is_active = 1 gd left join (select *,group_concat(td_gst_inv) g_td_gst_inv,sum(td_net) td_net_ref from tmc_data where td_show_status = 1 group by td_tkt) td on gd.gd_id=td.td_gstr2a_match_id left join airline_master am on am.am_name=gd.gd_supplier_type left join airline_invoice ai on gd.gd_id=ai.ti_gdid or gd.gd_inv_no=ai.ti_gst_nub where gd_id = "' . $gid . '" and gd.is_active = 1');
        //            $res = $this->db->query('select *,am_code as supplier_code from gstr2a_data gd left join (select *,group_concat(td_gst_inv) g_td_gst_inv,sum(td_net) td_net_ref from tmc_data where td_show_status = 1 group by td_tkt) td on gd.gd_id=td.td_gstr2a_match_id left join airline_master am on am.am_name=gd.gd_supplier_type left join airline_invoice ai on gd.gd_id=ai.ti_gdid where gd_id = "'.$gid.'"');

        //            $res = $this->db->query('select * from gstr2a_data gd left join (select *,group_concat(td_gst_inv) g_td_gst_inv from tmc_data group by td_tkt) td on gd.gd_tkt_no=td.td_tkt where  gd_id = "'.$gid.'"');
        else
            $res = $this->db->query('select *,am_code as supplier_code from gstr2a_data gd left join (select *,group_concat(td_gst_inv) g_td_gst_inv,sum(td_net) td_net_ref from tmc_data where td_show_status = 1 group by td_tkt) td on gd.gd_id=td.td_gstr2a_match_id left join airline_master am on am.am_name=gd.gd_supplier_type left join airline_invoice ai on gd.gd_id=ai.ti_gdid or gd.gd_inv_no=ai.ti_gst_nub where gd.is_active = 1 and gd_inv_date between "' . $fromdate . '" and "' . $todate . '" order by gd_id desc');
        //            $res = $this->db->query('select * from gstr2a_data gd left join (select *,group_concat(td_gst_inv) g_td_gst_inv from tmc_data group by td_tkt) td on gd.gd_tkt_no=td.td_tkt where gd_inv_date between "'.$fromdate.'" and "'.$todate.'"');
        foreach ($res->result() as $key => $value) {
            // print_r($value);die();
            if ($value) {
                $this->load->database('default', TRUE);
                $res_2 = $this->db2->where('invoice_no', $value->gd_inv_no)->get('air_invoice_details')->result();
                if (isset($res_2[0])) {
                    // $uptData['inv_received'] = 1;
                    // $this->db->where('td_id', $value->td_id);
                    // $data = $this->db->update('tmc_data',$uptData);
                    $value->inv = $res_2[0]->invoice_no;
                    $value->tkt = $res_2[0]->ticket_no;
                    $value->amt = $res_2[0]->total_values;
                    $value->tpnr = $res_2[0]->pnr;
                } else {
                    $value->inv = " ";
                }
            }
        }
        $fres['data'] = array();
        $res2 = new stdClass();
        if ($gid) {
            foreach ($res->result() as $res1) {
                $res2->corpname = 'test';
                $res2->corpgstin = 'test';
                $res2->gd_supplier_name = $res1->gd_supplier_name;
                $res2->gd_supplier_code = $res1->gd_supplier_type . '^' . strtoupper($res1->supplier_code);
                $res2->gd_inv_no = $res1->gd_inv_no;
                if ($res1->ti_file_name == '')
                    $res2->ti_file_name = '';
                if ($res1->gd_tkt_no == '')
                    $res2->gd_tkt_no = '';
                else
                    $res2->gd_tkt_no = $res1->gd_tkt_no;
                $res2->td_tkt = $res1->td_tkt;
                if ($res1->td_tkt_fare == '')
                    $res1->td_tkt_fare = 0;
                $res2->td_tkt_fare = $res1->td_tkt_fare;
                $res2->gd_doc_type = $res1->gd_doc_type;
                $res2->gd_inv_date = date('d-m-Y', strtotime($res1->gd_inv_date));
                //                $res2->gd_inv_date = $res1->gd_inv_date;
                $res2->gd_inv_value = $res1->gd_inv_value;
                $res2->td_net_ref = $res1->td_net_ref;
                if ($res1->td_tkt_fare == '')
                    $res1->td_tkt_fare = 0;
                if ($value->td_gstr2a_match_id != null && $value->td_gstr2a_match_id != 0) {
                    $variance = $res1->gd_inv_value - $res1->td_tkt_fare;
                    $res2->variance = $variance;
                } else {
                    $variance = ""; // Set variance to 0 if td_gstr2a_match_id is null or zero
                }

                if ($res1->ti_file_name == '') {
                    $data['inv_status'] = 0;
                    $this->db->where('gd_id', $res1->ti_tdid);
                    $this->db->update('gstr2a_data', $data);
                    $res2->gd_file_status = 'Invoice Missing';
                } else {
                    $data['inv_status'] = 4;
                    $this->db->where('gd_id', $res1->ti_tdid);
                    $this->db->update('gstr2a_data', $data);
                    $res2->gd_file_status = '';
                }

                if ($variance == 0) {
                    $data['inv_status'] = 1;
                    $this->db->where('gd_id', $res1->ti_tdid);
                    $this->db->update('gstr2a_data', $data);
                    $res2->gd_remarks = 'Transaction Matched';
                } else if ($variance > 0) {
                    $data['inv_status'] = 2;
                    $this->db->where('gd_id', $res1->ti_tdid);
                    $this->db->update('gstr2a_data', $data);
                    $res2->gd_remarks = 'TMC Amount Short';
                } else {
                    $data['inv_status'] = 3;
                    $this->db->where('gd_id', $res1->ti_tdid);
                    $this->db->update('gstr2a_data', $data);
                    $res2->gd_remarks = 'Airline Amount Short';
                }
                $res2->gd_remarks_inv = $res1->gd_remarks_inv;
                $res2->gd_rate_perc = $res1->gd_rate_perc;
                $res2->gd_taxable_value = $res1->gd_taxable_value;
                $res2->gd_integrated_tax = $res1->gd_integrated_tax;
                $res2->gd_central_tax = $res1->gd_central_tax;
                $res2->gd_state_tax = $res1->gd_state_tax;
                //                if($res1->gd_remarks == '')
                //                    $res1->gd_remarks = '';
                //                $res2->gd_remarks = $res1->gd_remarks;

                $res2->inv_ref = $res1->td_gst_inv;

                //                $td_gst_inv_ary = explode(',',$res1->td_gst_inv);
                //                for($i=count($td_gst_inv_ary);$i>0;$i--){
                //                    $varname = 'td_gst_inv_'.$i;
                //                    $res2->$varname = $td_gst_inv_ary[$i-1];
                //                }
                //                for($i=count($td_gst_inv_ary)+1;$i<5;$i++){
                //                    $varname = 'td_gst_inv_'.$i;
                //                    $res2->$varname = '';
                //                }
                $res2->tkt_like = $res1->td_tkt;
                //                $res2->td_gst_inv = $res1->td_gst_inv;
                $res2->gd_id = $res1->gd_id;
            }
            echo json_encode($res2);
        } else {
            foreach ($res->result() as $res1) {
                $res1->corpname = 'test';
                $res1->corpgstin = 'test';
                if ($res1->gd_tkt_no == '')
                    $res1->gd_tkt_no = '';
                if ($res1->ti_file_name == '')
                    $res1->ti_file_name = '';


                // $res1->gd_supplier_code = $res1->gd_supplier_type.'^'.strtoupper($res1->supplier_code);

                $res1->gd_supplier_code = $res1->gd_supplier_type . '^' . strtoupper((string) $res1->supplier_code);


                if ($res1->td_tkt_fare == '')
                    $res1->td_tkt_fare = 0;

                if ($res1->td_gstr2a_match_id != null && $res1->td_gstr2a_match_id != 0) {
                    $variance = $res1->gd_inv_value - $res1->td_tkt_fare;
                    $res1->variance = $variance;
                    if ($variance == 0) {
                        $res1->gd_remarks = 'Transaction Matched';
                    } else if ($variance > 0) {
                        $res1->gd_remarks = 'TMC Amount Short';
                    } else {
                        $res1->gd_remarks = 'Airline Amount Short';
                    }
                } else {
                    $variance = 0; // Set variance to 0 if td_gstr2a_match_id is null or zero
                    $res1->gd_remarks = 'TMC Invoice Missing'; // Clear gd_remarks if td_gstr2a_match_id is null or zero
                }
                if ($res1->ti_file_name == '')
                    $res1->gd_file_status = 'Invoice Missing';
                else
                    $res1->gd_file_status = '';

                //                if($res1->gd_remarks == '')
                //                    $res1->gd_remarks='';
                //                $res1->variance = $res1->gd_inv_value - $res1->td_net;

                $res1->tkt_like = $res1->td_tkt;
                $res1->gd_inv_date = date('d-m-Y', strtotime($res1->gd_inv_date));
                $res1->inv_ref = $res1->td_gst_inv;

                //                $td_gst_inv_ary = explode(',',$res1->td_gst_inv);
                //                for($i=count($td_gst_inv_ary);$i>0;$i--){
                //                    $varname = 'td_gst_inv_'.$i;
                //                    $res1->$varname = $td_gst_inv_ary[$i-1];
                //                }
                //                for($i=count($td_gst_inv_ary)+1;$i<5;$i++){
                //                    $varname = 'td_gst_inv_'.$i;
                //                    $res1->$varname = '';
                //                }

                $fres['data'][] = $res1;
            }
            echo json_encode($fres);
        }
    }

    public function upd_gst_tkt_no()
    {
        $data['gd_tkt_no'] = $this->input->post('gd_tkt_no');
        $con['gd_id'] = $this->input->post('refid');
        $this->db->where($con)
            ->update('gstr2a_data', $data);
        echo 1;
    }

    public function upd_gst_amt()
    {
        $data['gd_inv_value'] = $this->input->post('gd_inv_amt');
        $con['gd_id'] = $this->input->post('refid');
        $this->db->where($con)
            ->update('gstr2a_data', $data);
        echo 1;
    }

    public function upd_gst_remarks()
    {
        $data['gd_remarks'] = $this->input->post('gd_remarks');
        $con['gd_id'] = $this->input->post('refid');
        $this->db->where($con)
            ->update('gstr2a_data', $data);
        echo 1;
    }

    public function tmc_recon()
    {
        $this->checksess();
        $data['gstno_list'] = $this->corp->get_gstno();

        $data['selected_data'] = "01/01/2020 - " . date('d/m/Y');
        // var_dump($data);die();

        $this->load->view('corporate/tmc_recon', $data);
    }

    public function gstr2a_recon()
    {
        $this->checksess();
        $data['gstno_list'] = $this->corp->get_gstno();
        $data['selected_data'] = "01/01/2020 - " . date('d/m/Y');
        // var_dump($this->db->last_query());die();
        $this->load->view('corporate/gstr2a_recon', $data);
    }

    public function upload_cts()
    {
        $this->checksess();

        $monthAllAryLabel = array('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec');

        $insdata = $this->input->post('usr');
        $ext = pathinfo($_FILES['cts_file']['name'], PATHINFO_EXTENSION);
        $filename = 'CTS-' . $insdata['f_gst'] . '-' . $monthAllAryLabel[$insdata['f_month'] - 1] . '-' . $insdata['f_year'] . '-' . time() . '.' . $ext;

        $conchk['f_month'] = $insdata['f_month'];
        $conchk['f_year'] = $insdata['f_year'];
        $conchk['f_gst'] = $insdata['f_gst'];
        $conchk['f_corporateid'] = $this->session->userdata['recon_sess_i']['u_corporateid'];
        //        $entryres = $this->db->get_where('gstr2a_files',$conchk);
        //        if($entryres->num_rows()>0){
        //            $rdata['status'] = 31; echo json_encode($rdata); exit;
        //        }

        if (!is_dir(dirname(dirname(dirname(__FILE__))) . '/uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'])) {
            mkdir(dirname(dirname(dirname(__FILE__))) . '/uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid']);
            mkdir(dirname(dirname(dirname(__FILE__))) . '/uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'] . '/cts');
            mkdir(dirname(dirname(dirname(__FILE__))) . '/uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'] . '/gstr2a');
            mkdir(dirname(dirname(dirname(__FILE__))) . '/uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'] . '/tmc');
            mkdir(dirname(dirname(dirname(__FILE__))) . '/uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'] . '/airline_invoice');
            mkdir(dirname(dirname(dirname(__FILE__))) . '/uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'] . '/tmc_invoice');
        }

        $config['file_name'] = $filename;
        $config['upload_path'] = './uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'] . '/cts/';
        $config['allowed_types'] = '*';
        $config['max_size'] = 3000;
        $this->load->library('upload', $config);

        if (!$this->upload->do_upload('cts_file')) {
            $error = array('error' => $this->upload->display_errors());
            echo $error['error'];
        } else {
            $data = array('upload_data' => $this->upload->data());

            $fieldAry = array("Company Name ", "ACC.ACCOUNTNUMBER", "FIN.POSTING DATE", "FIN.TRANSACTION DATE", "MCH.MERCHANT NAME", "PT.TRAVEL AGENCY INVOICE NUMBER", "PT.PASSENGER NAME", "PT.TICKET NUMBER", "PT.AIR.CITY OF ORIGIN", "PT.AIR.CITY OF DESTINATION", "PT.AIR.TRAVEL DATE", "PT.AIR.CARRIER CODE", "PT.TOTAL FARE AMOUNT", "PT.AIR.TAX AMOUNT", "FIN.TRANSACTION AMOUNT", "PT.ISSUE DATE", "PT.ISSUING CARRIER", "PT.IATA CLIENT CODE", "PT.REFUND INDICATOR", "PT.EXCHANGE TICKET NUMBER", "PT.DOMESTIC SWITCH", "PT.DEPARTURE TAX AMOUNT", "PT.CFF.DATA 01", "PT.CFF.DATA 02", "PT.CFF.DATA 03", "PT.CFF.DATA 04", "PT.CFF.DATA 05", "PT.CFF.DATA 06", "PT.CFF.DATA 07", "PT.CFF.DATA 08", "PT.CFF.DATA 09", "PT.CFF.DATA 10");

            $labelAry = array('cd_comp_name', 'cd_acc_no', 'cd_posting_date', 'cd_trans_date', 'cd_merch_name', 'cd_tmc_inv_no', 'cd_pax_name', 'cd_tkt_no', 'cd_org', 'cd_dest', 'cd_travel_date', 'cd_carrier_code', 'cd_ttl_amt', 'cd_tax_amt', 'cd_trans_amt', 'cd_issue_date', 'cd_issuing_carrier', 'cd_iata_code', 'cd_refund_indicator', 'cd_exc_tkt_no', 'cd_dom_switch', 'cd_dep_tax_amt', 'cd_cff_data1', 'cd_cff_data2', 'cd_cff_data3', 'cd_cff_data4', 'cd_cff_data5', 'cd_cff_data6', 'cd_cff_data7', 'cd_cff_data8', 'cd_cff_data9', 'cd_cff_data10');

            $inserror = 0;

            $this->load->library('excel');
            //read file from path
            $filepath = './uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'] . '/cts/' . $filename;
            $objPHPExcel = PHPExcel_IOFactory::load($filepath);

            $worksheet = $objPHPExcel->getActiveSheet();
            $highestRow = (int)$worksheet->getHighestRow();
            $highestColumn = $worksheet->getHighestColumn();
            $seterror = 0;
            $ferror = array();
            $pcols = 31;
            $ttlrows = 0;
            for ($row = 1; $row <= $highestRow; $row++) {
                $gd_inv_type = '';
                $gd_doc_type = '';
                $gd_inv_no = '';
                if ($row == 1) {
                    for ($col = 0; $col < $pcols; $col++) {
                        $cell = $worksheet->getCellByColumnAndRow($col, $row);
                        $cellref = $cell;
                        $headingary[] = strval($cell);
                    }
                } else {
                    if ($fieldAry != $headingary) {
                        //                        print_r($fieldAry);
                        //                        echo '<br/>';
                        //                        print_r($headingary);
                        $rdata['status'] = 13;
                        $ferror['column'] = 'Column mismatch.';
                        $rdata['error'] = $ferror;
                        echo json_encode($rdata);
                        exit;
                    } else {
                        $chkcell = $worksheet->getCellByColumnAndRow(1, $row);
                        if ($chkcell != '') {
                            $ttlrows++;
                            $keys = '';
                            $values = '';
                            for ($col = 0; $col < $pcols; $col++) {
                                $cell = $worksheet->getCellByColumnAndRow($col, $row);
                                $cell = strtoupper(trim($cell));
                                $cc = strval($cell);
                                if ($cell != '') {
                                    $keys .= $labelAry[$col] . ',';
                                    if ($col == 0) {
                                        $values .= '"' . $cc . '",';
                                    } else if ($col == 2) {
                                        $cc = preg_replace('/\s+/', '', trim($cc));
                                        $values .= '"' . $cc . '",';
                                    } else if ($col == 3) {
                                        $cc = preg_replace('/\s+/', '', trim($cc));
                                        if (!preg_match("@^[a-zA-Z0-9\-\s/.]+$@", $cc)) {
                                            $seterror = 1;
                                            $ferror[$row]['column'][] = 'Invoice number';
                                            $ferror[$row]['row'] = $row;
                                        } else
                                            $gd_inv_no = $cc;
                                        $values .= '"' . $cc . '",';
                                    } else if ($col == 4) {
                                        $cc = trim($cc);
                                        if ($cc != 'INV' && $cc != 'CRN' && $cc != 'DEBIT NOTE') {
                                            $seterror = 1;
                                            $ferror[$row]['column'][] = 'DOCUMENT TYPE';
                                            $ferror[$row]['row'] = $row;
                                        } else
                                            $gd_doc_type = $cc;
                                        $values .= '"' . $cc . '",';
                                    } else if ($col == 2 || $col == 3 || $col == 10 || $col == 15) {
                                        if ($cc != '') {
                                            if (is_numeric($cc)) {
                                                $unix_date = ($cc - 25569) * 86400;
                                                $excel_date = 25569 + ($unix_date / 86400);
                                                $unix_date = ($excel_date - 25569) * 86400;
                                                $fdate = gmdate("Y-m-d", $unix_date);
                                                $values .= '"' . $fdate . '",';
                                            } else {
                                                $cc = str_replace('.', "-", $cc);
                                                $cc = str_replace('/', "-", $cc);
                                                $dary = explode('-', $cc);
                                                if ($dary[0] > 31 || $dary[1] > 12 || $dary[2] < 1900 || $dary[2] > 2021) {
                                                    $ferror[$row]['column'][] = 'Invoice Date';
                                                    $ferror[$row]['row'] = $row;
                                                } else {
                                                    $ffdate = $dary[2] . '-' . $dary[1] . '-' . $dary[0];
                                                    $cc = $ffdate;
                                                    $values .= '"' . $cc . '",';
                                                }
                                            }
                                        } else {
                                            $ferror[$row]['column'][] = 'Invoice Date';
                                            $ferror[$row]['row'] = $row;
                                        }
                                    } else if ($col == 7 || $col == 10 || $col == 11 || $col == 12 || $col == 13 || $col == 14 || $col == 15) {
                                        if (!preg_match("@^[0-9]+$@", $cc)) {
                                            $seterror = 1;
                                            $ferror[$row]['row'] = $row;
                                        }
                                        if ($col == 10)
                                            $gd_rate_perc = $cc;
                                        $values .= '"' . $cc . '",';
                                    } else if ($col == 8 || $col == 9) {
                                        if (!preg_match("@^[a-zA-Z]+$@", $cc)) {
                                            $seterror = 1;
                                            $ferror[$row]['row'] = $row;
                                        }
                                        $values .= '"' . $cc . '",';
                                    } else {
                                        $values .= '"' . $cell . '",';
                                    }
                                }
                            }
                            //                            print_r($ferror); exit;
                            //                            $chk = $this->db->query('select * from gstr2a_data where gd_inv_no = "'.$gd_inv_no.'" and gd_doc_type = "'.$gd_doc_type.'" and gd_rate_perc = "'.$gd_rate_perc.'"');

                            if ($chk->num_rows() > 0) {
                                $inserror++;
                            }

                            $insAry[] = 'insert ignore into cts_data (' . substr($keys, 0, -1) . ',gd_gid) values (' . substr($values, 0, -1);
                        }
                    }
                }
            }

            $ferror = array_values($ferror);

            $insdata['f_corporateid'] = $this->session->userdata['recon_sess_i']['u_corporateid'];
            $insdata['f_file'] = $filename;
            $insdata['f_type'] = 'gstr2a';
            $insdata['f_uploadedby'] = $this->session->userdata['recon_sess_i']['u_id'];
            $insdata['f_uploadedat'] = date('Y-m-d H:i:s', time());
            $this->db->insert('files', $insdata);
            $td_tfid = $this->db->insert_id();
            $rdata['insref'] = $td_tfid;

            for ($c = 0; $c < count($insArym); $c++) {
                //                try{
                $this->db->query($insArym[$c] . ',"' . $td_tfid . '")');
                //                }
                //                catch(Exception $e){
                //                    continue;
                //                }
            }

            if (!empty($ferror)) {
                $rdata['duprecords'] = 0;
                $rdata['status'] = 0;
                //                $rdata['error'] = $ferror;

                if ($inserror != 0)
                    $rdata['duprecords'] = $inserror;
                $rdata['ttlrows'] = $ttlrows;
                $rdata['invalidrows'] = count($ferror);
                echo json_encode($rdata);
                exit;
            } else {

                for ($c = 0; $c < count($insAry); $c++) {
                    echo $insAry[$c] . '<br/>';
                    //                    $this->db->query($insAry[$c].',"'.$td_tfid.'")');
                }

                $datequery = $this->db->query('select min(gd_inv_date) mindate, max(gd_inv_date) maxdate from gstr2a_data where gd_gid = ' . $td_tfid);
                $dateAry = array();
                foreach ($datequery->result() as $dateq) {
                    $dateCon['f_id'] = $td_tfid;
                    $dateAry['f_min_date'] = $dateq->mindate;
                    $dateAry['f_max_date'] = $dateq->maxdate;
                }
                $this->db->where($dateCon);
                $this->db->update('files', $dateAry);

                $rdata['status'] = 1;
                //                $rdata['added_records'] = $inscnt;
                echo json_encode($rdata);
            }
        }
    }

    public function upload_gstr2a()
    {
        $this->checksess();

        $monthAllAryLabel = array('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec');

        $insdata = $this->input->post('usr');
        $ext = pathinfo($_FILES['gstr2a_file']['name'], PATHINFO_EXTENSION);
        $filename = 'GSTR2A-' . $insdata['f_gst'] . '-' . $monthAllAryLabel[$insdata['f_month'] - 1] . '-' . $insdata['f_year'] . '-' . time() . '.' . $ext;

        $conchk['f_month'] = $insdata['f_month'];
        $conchk['f_year'] = $insdata['f_year'];
        $conchk['f_gst'] = $insdata['f_gst'];
        $conchk['f_corporateid'] = $this->session->userdata['recon_sess_i']['u_corporateid'];
        //        $entryres = $this->db->get_where('gstr2a_files',$conchk);
        //        if($entryres->num_rows()>0){
        //            $rdata['status'] = 31; echo json_encode($rdata); exit;
        //        }

        if (!is_dir(dirname(dirname(dirname(__FILE__))) . '/uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'])) {
            mkdir(dirname(dirname(dirname(__FILE__))) . '/uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid']);
            mkdir(dirname(dirname(dirname(__FILE__))) . '/uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'] . '/gstr2a');
            mkdir(dirname(dirname(dirname(__FILE__))) . '/uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'] . '/tmc');
            mkdir(dirname(dirname(dirname(__FILE__))) . '/uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'] . '/airline_invoice');
            mkdir(dirname(dirname(dirname(__FILE__))) . '/uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'] . '/tmc_invoice');
        }

        $config['file_name'] = $filename;
        $config['upload_path'] = './uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'] . '/gstr2a/';
        $config['allowed_types'] = '*';
        $config['max_size'] = 3000;
        $this->load->library('upload', $config);

        if (!$this->upload->do_upload('gstr2a_file')) {
            $error = array('error' => $this->upload->display_errors());
            echo $error['error'];
        } else {
            $data = array('upload_data' => $this->upload->data());

            $fieldAry = array("Trade/Legal name of the Supplier", "Supplier Type", "GSTIN of supplier", "Invoice number", "DOCUMENT TYPE", "Invoice type", "Invoice Date", "Invoice Value (₹)", "Place of supply", "Supply Attract Reverse Charge", "Rate (%)", "Taxable Value (₹)", "Integrated Tax  (₹)", "Central Tax (₹)", "State/UT tax (₹)", "Cess  (₹)", "Counter Party Return status");

            $labelAry = array('gd_supplier_type', 'gd_supplier_name', 'gd_supplier_gstin', 'gd_inv_no', 'gd_doc_type', 'gd_inv_type', 'gd_inv_date', 'gd_inv_value', 'gd_supply_place', 'gd_supply_reverse_charge', 'gd_rate_perc', 'gd_taxable_value', 'gd_integrated_tax', 'gd_central_tax', 'gd_state_tax', 'gd_cess', 'gd_return_status');

            $inserror = 0;

            $this->load->library('excel');
            //read file from path
            $filepath = './uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'] . '/gstr2a/' . $filename;
            $objPHPExcel = PHPExcel_IOFactory::load($filepath);

            $worksheet = $objPHPExcel->getActiveSheet();
            $highestRow = (int)$worksheet->getHighestRow();
            $highestColumn = $worksheet->getHighestColumn();
            $seterror = 0;
            $ferror = array();
            $pcols = 17;
            $ttlrows = 0;
            $remarksColumn = 'R'; // Define the column where you want to add the 'Remarks'

            // Add 'Remarks' as a header in the specified column
            $worksheet->setCellValue($remarksColumn . '1', 'Remarks');
            for ($row = 1; $row <= $highestRow; $row++) {
                $gd_inv_type = '';
                $gd_doc_type = '';
                $gd_inv_no = '';
                if ($row == 1) {
                    for ($col = 0; $col < $pcols; $col++) {
                        $cell = $worksheet->getCellByColumnAndRow($col, $row);
                        $cellref = $cell;
                        $headingary[] = strval($cell);
                    }
                } else {
                    if ($fieldAry != $headingary) {
                        //                        print_r($fieldAry);
                        //                        echo '<br/>';
                        //                        print_r($headingary);
                        $rdata['status'] = 13;
                        $ferror['column'] = 'Column mismatch.';
                        $rdata['error'] = $ferror;
                        echo json_encode($rdata);
                        exit;
                    } else {
                        $chkcell = $worksheet->getCellByColumnAndRow(1, $row);
                        if ($chkcell != '') {
                            $ttlrows++;
                            $keys = '';
                            $values = '';
                            for ($col = 0; $col < $pcols; $col++) {
                                $cell = $worksheet->getCellByColumnAndRow($col, $row);
                                $cell = strtoupper(trim($cell));
                                $cc = strval($cell);
                                if ($cell != '') {
                                    $keys .= $labelAry[$col] . ',';
                                    if ($col == 0) {
                                        //                                    if(!preg_match("@^[a-zA-Z0-9\s ]+$@",$cc)){
                                        //                                        $seterror = 1;
                                        ////                                        if(!isset($ferror['Trade/Legal name of the Supplier']))
                                        ////                                        $ferror['Trade/Legal name of the Supplier'] = $row.'-Trade/Legal name of the Supplier';
                                        //                                        $ferror[$row]['column'][] = 'Trade/Legal name of the Supplier';
                                        //                                        $ferror[$row]['row'] = $row;
                                        //                                    }
                                        $values .= '"' . $cc . '",';
                                    } else if ($col == 2) {
                                        //                                    if(!preg_match("@^[a-zA-Z0-9\s ]+$@",$cc)){
                                        //                                        $seterror = 1;
                                        ////                                        if(!isset($ferror['GSTIN of supplier']))
                                        ////                                        $ferror['GSTIN of supplier'] = $row.'-GSTIN of supplier';
                                        //                                        
                                        //                                        $ferror[$row]['column'][] = 'GSTIN of supplier';
                                        //                                        $ferror[$row]['row'] = $row;    
                                        //                                    }
                                        $cc = preg_replace('/\s+/', '', trim($cc));
                                        $values .= '"' . $cc . '",';
                                    } else if ($col == 3) {
                                        $cc = preg_replace('/\s+/', '', trim($cc));
                                        if (!preg_match("@^[a-zA-Z0-9\-\s/.]+$@", $cc)) {
                                            //                                    if(!preg_match("@^[a-zA-Z0-9\s \/ -.]+$@",$cc)){
                                            $seterror = 1;
                                            //                                        if(!isset($ferror['Invoice number']))
                                            //                                        $ferror['Invoice number'] = $row.'-Invoice number';

                                            $ferror[$row]['column'][] = 'Invoice number';
                                            $ferror[$row]['row'] = $row;
                                        } else
                                            $gd_inv_no = $cc;
                                        $values .= '"' . $cc . '",';
                                    } else if ($col == 4) {
                                        $cc = trim($cc);
                                        if ($cc != 'INV' && $cc != 'CRN' && $cc != 'DEBIT NOTE') {
                                            $seterror = 1;
                                            //                                        if(!isset($ferror['Invoice number']))
                                            //                                        $ferror['Invoice number'] = $row.'-Invoice number';

                                            $ferror[$row]['column'][] = 'DOCUMENT TYPE';
                                            $ferror[$row]['row'] = $row;
                                        } else
                                            $gd_doc_type = $cc;
                                        $values .= '"' . $cc . '",';
                                    } else if ($col == 6) {
                                        if ($cc != '') {
                                            // print_r($cc);
                                            // $cc = date('d/M/Y', strtotime($cc));
                                            // print_r($cc);
                                            if (is_numeric($cc)) {

                                                $unix_date = ($cc - 25569) * 86400;
                                                $excel_date = 25569 + ($unix_date / 86400);
                                                $unix_date = ($excel_date - 25569) * 86400;
                                                $fdate = gmdate("Y-m-d", $unix_date);
                                                $values .= '"' . $fdate . '",';
                                                // print_r($values);die();
                                            } else {

                                                $cc = str_replace('.', "-", $cc);
                                                $cc = str_replace('/', "-", $cc);
                                                $dary = explode('-', $cc);
                                                // print_r($dary);die();
                                                if ($dary[0] > 31 || $dary[1] > 12 || $dary[2] < 1900 || $dary[2] > date("Y")) {
                                                    //                                                if(!isset($ferror['Invoice Date']))
                                                    //                                                    $ferror['Invoice Date'] = $row.'-Invoice Date';
                                                    $ferror[$row]['column'][] = 'Invoice Date';
                                                    $ferror[$row]['row'] = $row;
                                                } else {
                                                    $ffdate = $dary[2] . '-' . $dary[1] . '-' . $dary[0];
                                                    $cc = $ffdate;
                                                    $values .= '"' . $cc . '",';
                                                }
                                            }
                                        } else {
                                            //                                        if(!isset($ferror['Invoice Date']))
                                            //                                            $ferror['Invoice Date'] = $row.'-Invoice Date';
                                            $ferror[$row]['column'][] = 'Invoice Date';
                                            $ferror[$row]['row'] = $row;
                                        }
                                    } else if ($col == 7 || $col == 10 || $col == 11 || $col == 12 || $col == 13 || $col == 14 || $col == 15) {
                                        if (!preg_match("@^[0-9\.]+$@", $cc)) {
                                            $seterror = 1;
                                            $ferror[$row]['row'] = $row;
                                        }
                                        if ($col == 10)
                                            $gd_rate_perc = $cc;
                                        $values .= '"' . $cc . '",';
                                    } else if ($col == 8 || $col == 9) {
                                        if (!preg_match("@^[a-zA-Z]+$@", $cc)) {
                                            $seterror = 1;
                                            $ferror[$row]['row'] = $row;
                                        }
                                        $values .= '"' . $cc . '",';
                                    } else {
                                        $values .= '"' . $cell . '",';
                                    }
                                }
                            }
                            //                            print_r($ferror); exit;
                            $chk = $this->db->query('select * from gstr2a_data where gd_inv_no = "' . $gd_inv_no . '" and gd_doc_type = "' . $gd_doc_type . '" and gd_rate_perc = "' . $gd_rate_perc . '"');
                            //                            $chk = $this->db->query('select * from gstr2a_data where gd_inv_no = "'.$gd_inv_no.'" and gd_doc_type = "'.$gd_doc_type.'" and gd_inv_type = "'.$gd_inv_type.'"');

                            if ($chk->num_rows() > 0) {
                                $worksheet->setCellValue($remarksColumn . $row, 'Duplicate found');
                                $inserror++;
                            }

                            $insAry[] = 'insert ignore into gstr2a_data (' . substr($keys, 0, -1) . ',gd_gid) values (' . substr($values, 0, -1);
                            $insArym[] = 'insert into gstr2a_data_dump (' . substr($keys, 0, -1) . ',gd_gid) values (' . substr($values, 0, -1);
                        }
                    }
                }
            }
            // echo $ttlrows;
            // echo "=";
            // echo $inserror;
            // die;
            $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');

            $objWriter->save('./uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'] . '/gstr2a/' . $filename);

            if ($ttlrows == $inserror) {


                $rdata['status'] = 31;
                echo json_encode($rdata);
                exit; // Exit the function
            }


            $ferror = array_values($ferror);

            $insdata['f_corporateid'] = $this->session->userdata['recon_sess_i']['u_corporateid'];
            $insdata['f_file'] = $filename;
            $insdata['f_type'] = 'gstr2a';
            $insdata['f_uploadedby'] = $this->session->userdata['recon_sess_i']['u_id'];
            $insdata['f_uploadedat'] = date('Y-m-d H:i:s', time());
            $this->db->insert('files', $insdata);
            $td_tfid = $this->db->insert_id();
            $rdata['insref'] = $td_tfid;

            for ($c = 0; $c < count($insArym); $c++) {
                //                try{
                $this->db->query($insArym[$c] . ',"' . $td_tfid . '")');
                //                }
                //                catch(Exception $e){
                //                    continue;
                //                }
            }

            if (!empty($ferror)) {
                $rdata['duprecords'] = 0;
                $rdata['status'] = 0;
                //                $rdata['error'] = $ferror;

                if ($inserror != 0)
                    $rdata['duprecords'] = $inserror;
                $rdata['ttlrows'] = $ttlrows;
                $rdata['invalidrows'] = count($ferror);
                echo json_encode($rdata);
                exit;
            } else {

                for ($c = 0; $c < count($insAry); $c++) {
                    //                echo $insAry[$c].'<br/>';
                    $this->db->query($insAry[$c] . ',"' . $td_tfid . '")');
                }

                $datequery = $this->db->query('select min(gd_inv_date) mindate, max(gd_inv_date) maxdate from gstr2a_data where gd_gid = ' . $td_tfid);
                $dateAry = array();
                foreach ($datequery->result() as $dateq) {
                    $dateCon['f_id'] = $td_tfid;
                    $dateAry['f_min_date'] = $dateq->mindate;
                    $dateAry['f_max_date'] = $dateq->maxdate;
                }
                $this->db->where($dateCon);
                $this->db->update('files', $dateAry);

                $rdata['status'] = 1;
                //                $rdata['added_records'] = $inscnt;
                echo json_encode($rdata);
            }
        }
    }

    public function gstno()
    {
        $this->checksess();
        $this->load->view('corporate/gstno');
    }

    public function upd_gstno()
    {
        $con['gn_id !='] = $this->input->post('gn_id');
        $data['gn_gstno'] = $this->input->post('gn_gstno');
        $this->db->from('gstno');
        $this->db->where($data);
        $this->db->where($con);
        $dupchk = $this->db->get();
        if ($dupchk->num_rows() > 0) {
            echo 2;
            exit;
        } else {
            $data['gn_isactive'] = $this->input->post('gn_isactive');
            $data['gn_updatedby'] = $this->session->userdata['recon_sess_i']['u_id'];
            $data['gn_updatedon'] = date('Y-m-d H:i:s', time());
            $this->db->where('gn_id', $this->input->post('gn_id'));
            $this->db->update('gstno', $data);
            echo 1;
            exit;
        }
    }

    public function add_gstno()
    {
        $data['gn_gstno'] = trim($this->input->post('gn_gstno'));

        $fary['status'] = 2;
        $this->db->from('gstno');
        $this->db->where($data);
        $dupchk = $this->db->get();
        if ($dupchk->num_rows() > 0) {
            echo json_encode($fary);
            exit;
        } else {
            $data['gn_isactive'] = $this->input->post('gn_isactive');
            $data['gn_corp_id'] = $this->session->userdata['recon_sess_i']['u_corporateid'];
            $data['gn_createdby'] = $this->session->userdata['recon_sess_i']['u_id'];
            $data['gn_createdon'] = date('Y-m-d H:i:s', time());
            $this->db->insert('gstno', $data);
            $fary['status'] = 1;
            echo json_encode($fary);
        }
    }

    function get_gstno($gn_id = null)
    {
        $this->checksess();
        $data = $this->corp->get_gstno($gn_id);
        $monthAry = array('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec');
        $fary = array();
        foreach ($data->result() as $dat) {
            $dat->gn_isactive_ref = (($dat->gn_isactive == 0) ? "Inactive" : "Active");
            $dat->gn_createdon_ref = date('d-m-Y H:i', strtotime($dat->gn_createdon));
            $fary['data'][] = $dat;
        }
        echo json_encode($fary);
    }

    public function cts()
    {
        $this->checksess();

        $monthAllAryLabel = array('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec');
        $yearAry = array();
        $monthAry = array();
        $m = date('m', time());
        for ($i = 0; $i < $m; $i++) {
            $monthAry[] = $monthAllAryLabel[$i];
        }
        for ($i = 0; $i < 4; $i++) {
            $yearAry[] = date('Y', time()) - $i;
        }
        $data['years'] = $yearAry;
        $data['month'] = $monthAry;
        $data['allmonth'] = $monthAllAryLabel;
        $data['curYear'] = date('Y', time());

        $this->load->view('corporate/cts', $data);
    }

    public function gstr2a()
    {
        $this->checksess();

        $data['gstinfo'] = $this->corp->get_gstno(null, 1);
        // print_r($this->session->userdata['recon_sess_i']['u_corporateid']);die();
        $monthAllAryLabel = array('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec');
        $yearAry = array();
        $monthAry = array();
        $m = date('m', time());
        for ($i = 0; $i < $m; $i++) {
            $monthAry[] = $monthAllAryLabel[$i];
        }
        for ($i = 0; $i < 4; $i++) {
            $yearAry[] = date('Y', time()) - $i;
        }
        $data['years'] = $yearAry;
        $data['month'] = $monthAry;
        $data['allmonth'] = $monthAllAryLabel;
        $data['curYear'] = date('Y', time());
        $this->load->view('corporate/gstr2a', $data);
    }
    public function airlineinvoice()
    {

        $this->load->database('default', TRUE);

        $res['data'] = $this->db2->get('air_invoice_details')->result();

        $this->load->view('corporate/airlineinvoice', $res);
    }


    function p_c1_valid_download($td_tfid)
    {
        // valid download function for bta cta file//
        $this->load->library('excel');
        $this->load->helper('download');
        $this->excel->setActiveSheetIndex(0);

        $this->excel->getActiveSheet()->setTitle('Sheet 1');
        $this->excel->getActiveSheet()->setCellValue('A1', 'STMT DT');
        $this->excel->getActiveSheet()->setCellValue('B1', 'STMT REF NO');
        $this->excel->getActiveSheet()->setCellValue('C1', 'CUST REF');
        $this->excel->getActiveSheet()->setCellValue('D1', 'TRIP RQSIT');
        $this->excel->getActiveSheet()->setCellValue('E1', 'PAX NAME');
        $this->excel->getActiveSheet()->setCellValue('F1', 'UNPAIDAMT');
        $this->excel->getActiveSheet()->setCellValue('G1', 'BKNG DATE');
        $this->excel->getActiveSheet()->setCellValue('H1', 'DEPT DATE');
        $this->excel->getActiveSheet()->setCellValue('I1', 'TICKET NO');
        $this->excel->getActiveSheet()->setCellValue('J1', 'CARRIER');
        $this->excel->getActiveSheet()->setCellValue('K1', 'CLASS');
        $this->excel->getActiveSheet()->setCellValue('L1', 'ROUTING');
        $this->excel->getActiveSheet()->setCellValue('M1', 'TXN DATE');
        $this->excel->getActiveSheet()->setCellValue('N1', 'PAYMENT ST');
        $this->excel->getActiveSheet()->setCellValue('O1', 'DISPUTE REASON');
        $this->excel->getActiveSheet()->setCellValue('P1', 'MISSING SUPPORTING DOCUMENT');
        $this->excel->getActiveSheet()->setCellValue('Q1', 'COMMENT1');
        $this->excel->getActiveSheet()->setCellValue('R1', 'COMMENT2');
        $this->excel->getActiveSheet()->setCellValue('S1', 'COMMENT3');
        $this->excel->getActiveSheet()->setCellValue('T1', 'REMARKS');

        $this->excel->getActiveSheet()->getStyle('A1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('B1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('B1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('C1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('C1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('D1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('D1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('E1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('E1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('F1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('F1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('G1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('G1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('H1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('H1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('I1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('I1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('J1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('J1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('K1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('K1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('L1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('L1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('M1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('M1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('N1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('N1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('O1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('O1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('P1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('P1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('Q1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('Q1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('R1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('R1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('S1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('S1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('T1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('T1')->getFont()->setBold(true);

        $this->db->from('p_client_upload_1 tmc');
        $this->db->join('p_files f', 'tmc.pc1_pc1fid=f.pf_id', 'left');
        $this->db->where('pc1_pc1fid', $td_tfid);
        $rec = $this->db->get();
        // print_r($rec);die;
        $i = 1;
        foreach ($rec->result() as $recr) {
            if ($i == 1)
                $f_name = $recr->pf_file;
            // print_r($recr);die;
            $this->excel->getActiveSheet()->setCellValue('A' . ++$i, $recr->pc1_stmt_date);
            $this->excel->getActiveSheet()->setCellValue('B' . $i, $recr->pc1_stmt_ref_no);
            $this->excel->getActiveSheet()->setCellValue('C' . $i, $recr->pc1_customer_ref);
            $this->excel->getActiveSheet()->setCellValue('D' . $i, $recr->pc1_trip_req);
            $this->excel->getActiveSheet()->setCellValue('E' . $i, $recr->pc1_pax_name);
            $this->excel->getActiveSheet()->setCellValue('F' . $i, $recr->pc1_unpaid_amt);
            $this->excel->getActiveSheet()->setCellValue('G' . $i, $recr->pc1_booking_date);
            $this->excel->getActiveSheet()->setCellValue('H' . $i, $recr->pc1_dep_date);
            $this->excel->getActiveSheet()->setCellValue('I' . $i, $recr->pc1_ticket_no);
            $this->excel->getActiveSheet()->setCellValue('J' . $i, $recr->pc1_carrier);
            $this->excel->getActiveSheet()->setCellValue('K' . $i, $recr->pc1_class);
            $this->excel->getActiveSheet()->setCellValue('L' . $i, $recr->pc1_routing);
            $this->excel->getActiveSheet()->setCellValue('M' . $i, $recr->pc1_txn_date);
            $this->excel->getActiveSheet()->setCellValue('N' . $i, $recr->pc1_payment_stmt);
            $this->excel->getActiveSheet()->setCellValue('O' . $i, $recr->pc1_dispute_reason);
            $this->excel->getActiveSheet()->setCellValue('P' . $i, $recr->pc1_missing_support_document);
            $this->excel->getActiveSheet()->setCellValue('Q' . $i, $recr->pc1_comment1);
            $this->excel->getActiveSheet()->setCellValue('R' . $i, $recr->pc1_comment2);
            $this->excel->getActiveSheet()->setCellValue('S' . $i, $recr->pc1_comment3);
            $this->excel->getActiveSheet()->setCellValue('T' . $i, $recr->pc1_remarks);
        }
        $f_name = str_replace('.', '-valid.', $f_name);
        header('Content-Type: application/vnd.ms-excel'); //mime type
        header('Content-Disposition: attachment;filename="' . $f_name . '.xls"'); //tell browser what's the file name
        header('Cache-Control: max-age=0'); //no cache
        $objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5');
        ob_end_clean();
        ob_start();
        $objWriter->save('php://output');
        //        echo "<script>window.close();</script>";
        //    exit;
        // }
    }
    function p_c2_valid_download($td_tfid)
    {
        $this->load->library('excel');
        $this->load->helper('download');
        $this->excel->setActiveSheetIndex(0);

        $this->excel->getActiveSheet()->setTitle('Sheet 1');
        $this->excel->getActiveSheet()->setCellValue('A1', 'STMT REF NO');
        $this->excel->getActiveSheet()->setCellValue('B1', 'INVOICE CRN');
        $this->excel->getActiveSheet()->setCellValue('C1', 'GST INV REF');
        $this->excel->getActiveSheet()->setCellValue('D1', 'UNPAID AMOUNT');


        $this->excel->getActiveSheet()->getStyle('A1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('B1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('B1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('C1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('C1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('D1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('D1')->getFont()->setBold(true);

        $this->db->from('p_client_upload_2 tmc');
        $this->db->join('p_files f', 'tmc.pc2_pc2fid=f.pf_id', 'left');
        $this->db->where('pc2_pc2fid', $td_tfid);
        $rec = $this->db->get();
        // print_r($rec);die;
        $i = 1;
        foreach ($rec->result() as $recr) {
            if ($i == 1)
                $f_name = $recr->pf_file;
            // print_r($recr);die;
            $this->excel->getActiveSheet()->setCellValue('A' . ++$i, $recr->pc2_stmt_ref_no);
            $this->excel->getActiveSheet()->setCellValue('B' . $i, $recr->pc2_inv_crn);
            $this->excel->getActiveSheet()->setCellValue('C' . $i, $recr->pc2_inv_crn_ref_no);
            $this->excel->getActiveSheet()->setCellValue('D' . $i, $recr->pc2_unpaid_amt);
        }
        $f_name = str_replace('.', '-valid.', $f_name);
        header('Content-Type: application/vnd.ms-excel'); //mime type
        header('Content-Disposition: attachment;filename="' . $f_name . '.xls"'); //tell browser what's the file name
        header('Cache-Control: max-age=0'); //no cache
        $objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5');
        ob_end_clean();
        ob_start();
        $objWriter->save('php://output');
        //        echo "<script>window.close();</script>";
        //    exit;
        // }
    }


    function p_tmc_valid_download($td_tfid)
    {
        $this->load->library('excel');
        $this->load->helper('download');
        $this->excel->setActiveSheetIndex(0);
        // print_r($td_tfid);die;
        $this->excel->getActiveSheet()->setTitle('Sheet 1');
        $this->excel->getActiveSheet()->setCellValue('A1', 'TMC NAME');
        $this->excel->getActiveSheet()->setCellValue('B1', 'TMC GSTIN NO');
        $this->excel->getActiveSheet()->setCellValue('C1', 'SEGMENT');
        $this->excel->getActiveSheet()->setCellValue('D1', 'INV DATE');
        $this->excel->getActiveSheet()->setCellValue('E1', 'GST INV/CRN NO');
        $this->excel->getActiveSheet()->setCellValue('F1', 'GST INV/CRN REF NO');
        $this->excel->getActiveSheet()->setCellValue('G1', 'DOCUMENT TYPE');
        $this->excel->getActiveSheet()->setCellValue('H1', 'BILLING ENTITYNAME');
        $this->excel->getActiveSheet()->setCellValue('I1', 'CUSTOMER GSTIN NO');
        $this->excel->getActiveSheet()->setCellValue('J1', 'BILLABLE');
        $this->excel->getActiveSheet()->setCellValue('K1', 'BUSINESS UNIT');
        $this->excel->getActiveSheet()->setCellValue('L1', 'TRIP CODE');
        $this->excel->getActiveSheet()->setCellValue('M1', 'COST CENTRE');
        $this->excel->getActiveSheet()->setCellValue('N1', 'BUDGET CODE');
        $this->excel->getActiveSheet()->setCellValue('O1', 'P.O NO');
        $this->excel->getActiveSheet()->setCellValue('P1', 'LOCATION');
        $this->excel->getActiveSheet()->setCellValue('Q1', 'DEPARTMENT');
        $this->excel->getActiveSheet()->setCellValue('R1', 'EMP ID');
        $this->excel->getActiveSheet()->setCellValue('S1', 'BOOKING RCVD FROM');
        $this->excel->getActiveSheet()->setCellValue('T1', 'PASSENGER NAME');
        $this->excel->getActiveSheet()->setCellValue('U1', 'PASSENGER TYPE');
        $this->excel->getActiveSheet()->setCellValue('V1', 'AIRLINE CODE');
        $this->excel->getActiveSheet()->setCellValue('W1', 'AIRLINE NAME');
        $this->excel->getActiveSheet()->setCellValue('X1', 'TICKET NO');
        $this->excel->getActiveSheet()->setCellValue('Y1', 'FLIGHTNO');
        $this->excel->getActiveSheet()->setCellValue('Z1', 'SECTOR');
        $this->excel->getActiveSheet()->setCellValue('AA1', 'DEP START DATE');
        $this->excel->getActiveSheet()->setCellValue('AB1', 'ARRVL END DATE');
        $this->excel->getActiveSheet()->setCellValue('AC1', 'CABINCLASS');
        $this->excel->getActiveSheet()->setCellValue('AD1', 'FARECLASS');
        $this->excel->getActiveSheet()->setCellValue('AE1', 'BASIC FARE');
        $this->excel->getActiveSheet()->setCellValue('AF1', 'K3');
        $this->excel->getActiveSheet()->setCellValue('AG1', 'IGST');
        $this->excel->getActiveSheet()->setCellValue('AH1', 'CGST');
        $this->excel->getActiveSheet()->setCellValue('AI1', 'SGST');
        $this->excel->getActiveSheet()->setCellValue('AJ1', 'MEAL');
        $this->excel->getActiveSheet()->setCellValue('AK1', 'SEAT');
        $this->excel->getActiveSheet()->setCellValue('AL1', 'OTHER CHARGES');
        $this->excel->getActiveSheet()->setCellValue('AM1', 'TKT FARE');
        $this->excel->getActiveSheet()->setCellValue('AN1', 'SERVICE CHARGES');
        $this->excel->getActiveSheet()->setCellValue('AO1', 'DISCOUNT');
        $this->excel->getActiveSheet()->setCellValue('AP1', 'TMC CAN CHARGES');
        $this->excel->getActiveSheet()->setCellValue('AQ1', 'PANALTY');
        $this->excel->getActiveSheet()->setCellValue('AR1', 'CARD CHARGES');
        $this->excel->getActiveSheet()->setCellValue('AS1', 'IGST ON SER CHARGES');
        $this->excel->getActiveSheet()->setCellValue('AT1', 'CGST ON SER CHARGES');
        $this->excel->getActiveSheet()->setCellValue('AU1', 'SGST ON SER CHARGES');
        $this->excel->getActiveSheet()->setCellValue('AV1', 'NETPAYABLE');
        $this->excel->getActiveSheet()->setCellValue('AW1', 'SERVICE TYPE');
        $this->excel->getActiveSheet()->setCellValue('AX1', 'AIRLINE PNR');
        $this->excel->getActiveSheet()->setCellValue('AY1', 'GDS PNR');
        $this->excel->getActiveSheet()->setCellValue('AZ1', 'TICKETED ON CORP OR RETAIL');
        $this->excel->getActiveSheet()->setCellValue('BA1', 'APPROVER NAME/CODE');
        $this->excel->getActiveSheet()->setCellValue('BB1', 'REMARKS');


        $this->excel->getActiveSheet()->getStyle('A1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('B1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('B1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('C1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('C1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('D1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('D1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('E1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('E1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('F1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('F1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('G1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('G1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('H1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('H1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('I1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('I1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('J1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('J1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('K1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('K1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('L1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('L1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('M1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('M1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('N1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('N1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('O1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('O1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('P1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('P1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('Q1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('Q1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('R1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('R1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('S1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('S1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('T1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('T1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('U1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('U1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('V1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('V1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('W1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('W1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('X1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('X1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('Y1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('Y1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('Z1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('Z1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('AA1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('AA1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('AB1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('AB1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('AC1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('AC1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('AD1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('AD1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('AE1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('AE1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('AF1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('AF1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('AG1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('AG1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('AH1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('AH1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('AI1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('AI1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('AJ1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('AJ1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('AK1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('AK1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('AL1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('AL1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('AM1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('AM1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('AN1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('AN1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('AO1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('AO1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('AP1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('AP1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('AQ1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('AQ1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('AR1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('AR1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('AS1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('AS1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('AT1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('AT1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('AU1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('AU1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('AV1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('AV1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('AW1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('AW1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('AX1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('AX1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('AY1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('AY1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('AZ1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('AZ1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('BA1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('BA1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('BB1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('BB1')->getFont()->setBold(true);


        $this->db->from('p_tmc_data tmc');
        $this->db->join('p_files f', 'tmc.ptd_id=f.pf_id', 'left');
        $this->db->where('ptd_ptfid', $td_tfid);
        $rec = $this->db->get();
        // print_r($rec->result());
        // print_r($this->db->last_query());
        // die;
        $i = 1;
        foreach ($rec->result() as $recr) {

            // print_r($recr);die;
            if ($i == 1)
                $f_name = $recr->pf_file;


            $this->excel->getActiveSheet()->setCellValue('A' . ++$i, $recr->ptd_tmc_name);
            $this->excel->getActiveSheet()->setCellValue('B' . $i, $recr->ptd_tmc_gstin_no);
            //            $this->excel->getActiveSheet()->setCellValue('B' . $i, date('d-m-Y',strtotime($recr->td_inv_date)));
            $this->excel->getActiveSheet()->setCellValue('C' . $i, $recr->ptd_segment);
            $this->excel->getActiveSheet()->setCellValue('D' . $i, $recr->ptd_inv_date);
            $this->excel->getActiveSheet()->setCellValue('E' . $i, $recr->ptd_gst_inv_crn_no);
            $this->excel->getActiveSheet()->setCellValue('F' . $i, $recr->ptd_gst_inv_crn_ref_no);
            $this->excel->getActiveSheet()->setCellValue('G' . $i, $recr->ptd_doc_type);
            $this->excel->getActiveSheet()->setCellValue('H' . $i, $recr->ptd_billing_entity);
            $this->excel->getActiveSheet()->setCellValue('I' . $i, $recr->ptd_customer_gstin_no);
            $this->excel->getActiveSheet()->setCellValue('J' . $i, $recr->ptd_billable);
            $this->excel->getActiveSheet()->setCellValue('K' . $i, $recr->ptd_business_unit);
            $this->excel->getActiveSheet()->setCellValue('L' . $i, $recr->ptd_trip_code);
            $this->excel->getActiveSheet()->setCellValue('M' . $i, $recr->ptd_cost_centre);
            $this->excel->getActiveSheet()->setCellValue('N' . $i, $recr->ptd_budget_code);
            $this->excel->getActiveSheet()->setCellValue('O' . $i, $recr->ptd_po_no);
            $this->excel->getActiveSheet()->setCellValue('P' . $i, $recr->ptd_location);
            $this->excel->getActiveSheet()->setCellValue('Q' . $i, $recr->ptd_department);
            $this->excel->getActiveSheet()->setCellValue('R' . $i, $recr->ptd_emp_id);
            $this->excel->getActiveSheet()->setCellValue('S' . $i, $recr->ptd_booking_rcvd_from);
            $this->excel->getActiveSheet()->setCellValue('T' . $i, $recr->ptd_pax_name);
            $this->excel->getActiveSheet()->setCellValue('U' . $i, $recr->ptd_pax_type);
            $this->excel->getActiveSheet()->setCellValue('V' . $i, $recr->ptd_airline_code);
            $this->excel->getActiveSheet()->setCellValue('W' . $i, $recr->ptd_airline_name);
            $this->excel->getActiveSheet()->setCellValue('X' . $i, $recr->ptd_tkt_no);
            $this->excel->getActiveSheet()->setCellValue('Y' . $i, $recr->ptd_flight_no);
            $this->excel->getActiveSheet()->setCellValue('Z' . $i, $recr->ptd_sector);
            $this->excel->getActiveSheet()->setCellValue('AA' . $i, $recr->ptd_dep_start_date);
            $this->excel->getActiveSheet()->setCellValue('AB' . $i, $recr->ptd_arr_end_date);
            $this->excel->getActiveSheet()->setCellValue('AC' . $i, $recr->ptd_cabinclass);
            $this->excel->getActiveSheet()->setCellValue('AD' . $i, $recr->ptd_fareclass);
            $this->excel->getActiveSheet()->setCellValue('AE' . $i, $recr->ptd_basic_fare);
            $this->excel->getActiveSheet()->setCellValue('AF' . $i, $recr->ptd_k3);
            $this->excel->getActiveSheet()->setCellValue('AG' . $i, $recr->ptd_igst);
            $this->excel->getActiveSheet()->setCellValue('AH' . $i, $recr->ptd_cgst);
            $this->excel->getActiveSheet()->setCellValue('AI' . $i, $recr->ptd_sgst);
            $this->excel->getActiveSheet()->setCellValue('AJ' . $i, $recr->ptd_meal);
            $this->excel->getActiveSheet()->setCellValue('AK' . $i, $recr->ptd_seat);
            $this->excel->getActiveSheet()->setCellValue('AL' . $i, $recr->ptd_others);
            $this->excel->getActiveSheet()->setCellValue('AM' . $i, $recr->ptd_tkt_fare);
            $this->excel->getActiveSheet()->setCellValue('AN' . $i, $recr->ptd_service_charge);
            $this->excel->getActiveSheet()->setCellValue('AO' . $i, $recr->ptd_discount);
            $this->excel->getActiveSheet()->setCellValue('AP' . $i, $recr->ptd_tmc_can_charge);
            $this->excel->getActiveSheet()->setCellValue('AQ' . $i, $recr->ptd_penalty);
            $this->excel->getActiveSheet()->setCellValue('AR' . $i, $recr->ptd_card_charge);
            $this->excel->getActiveSheet()->setCellValue('AS' . $i, $recr->ptd_igst_service_charge);
            $this->excel->getActiveSheet()->setCellValue('AT' . $i, $recr->pgd_cgst_service_charge);
            $this->excel->getActiveSheet()->setCellValue('AU' . $i, $recr->ptd_sgst_service_charge);
            $this->excel->getActiveSheet()->setCellValue('AV' . $i, $recr->ptd_netpayable);
            $this->excel->getActiveSheet()->setCellValue('AW' . $i, $recr->ptd_servicetype);
            $this->excel->getActiveSheet()->setCellValue('AX' . $i, $recr->ptd_airline_pnr);
            $this->excel->getActiveSheet()->setCellValue('AY' . $i, $recr->ptd_gst_pnr);
            $this->excel->getActiveSheet()->setCellValue('AZ' . $i, $recr->ptd_corp_retail);
            $this->excel->getActiveSheet()->setCellValue('BA' . $i, $recr->ptd_approver);
            $this->excel->getActiveSheet()->setCellValue('BB' . $i, $recr->remarks);
        }
        $f_name = str_replace('.', '-valid.', $f_name);

        header('Content-Type: application/vnd.ms-excel'); //mime type
        header('Content-Disposition: attachment;filename="' . $f_name . '"'); //tell browser what's the file name
        header('Cache-Control: max-age=0'); //no cache
        $objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5');
        ob_end_clean();
        ob_start();
        $objWriter->save('php://output');
        //        echo "<script>window.close();</script>";
        //        exit;
    }

    function tmc_valid_download($td_tfid)
    {
        $this->load->library('excel');
        $this->load->helper('download');
        $this->excel->setActiveSheetIndex(0);

        $this->excel->getActiveSheet()->setTitle('Sheet 1');
        $this->excel->getActiveSheet()->setCellValue('A1', 'SEGMENT');
        $this->excel->getActiveSheet()->setCellValue('B1', 'INV DATE ');
        $this->excel->getActiveSheet()->setCellValue('C1', 'GST INVOICE NO');
        $this->excel->getActiveSheet()->setCellValue('D1', 'DOC TYPE');
        $this->excel->getActiveSheet()->setCellValue('E1', 'BILLING ENTITY');
        $this->excel->getActiveSheet()->setCellValue('F1', 'EMP ID');
        $this->excel->getActiveSheet()->setCellValue('G1', 'PASSENGER NAME');
        $this->excel->getActiveSheet()->setCellValue('H1', 'CARRIER NAME');
        $this->excel->getActiveSheet()->setCellValue('I1', 'TICKET NO');
        $this->excel->getActiveSheet()->setCellValue('J1', 'FLIGHTNO');
        $this->excel->getActiveSheet()->setCellValue('K1', 'SECTOR');
        $this->excel->getActiveSheet()->setCellValue('L1', 'DEPDATE & TIME');
        $this->excel->getActiveSheet()->setCellValue('M1', 'CABINCLASS');
        $this->excel->getActiveSheet()->setCellValue('N1', 'TOTAL TKT FARE');
        $this->excel->getActiveSheet()->setCellValue('O1', 'NETPAYABLE');
        $this->excel->getActiveSheet()->setCellValue('P1', 'AIR TAX DESCRIPTION');
        $this->excel->getActiveSheet()->setCellValue('Q1', 'AIRLINE PNR');
        $this->excel->getActiveSheet()->setCellValue('R1', 'GDS PNR');

        $this->excel->getActiveSheet()->getStyle('A1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('B1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('B1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('C1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('C1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('D1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('D1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('E1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('E1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('F1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('F1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('G1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('G1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('H1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('H1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('I1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('I1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('J1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('J1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('K1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('K1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('L1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('L1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('M1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('M1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('N1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('N1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('O1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('O1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('P1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('P1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('Q1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('Q1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('R1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('R1')->getFont()->setBold(true);

        $this->db->from('tmc_data tmc');
        $this->db->join('files f', 'tmc.td_id=f.f_id', 'left');
        $this->db->where('td_tfid', $td_tfid);
        $rec = $this->db->get();
        $i = 1;
        foreach ($rec->result() as $recr) {
            if ($i == 1)
                $f_name = $recr->f_file;
            $this->excel->getActiveSheet()->setCellValue('A' . ++$i, $recr->td_segment);
            $this->excel->getActiveSheet()->setCellValue('B' . $i, date('d-m-Y', strtotime($recr->td_inv_date)));
            $this->excel->getActiveSheet()->setCellValue('C' . $i, $recr->td_gst_inv);
            $this->excel->getActiveSheet()->setCellValue('D' . $i, $recr->td_doc_type);
            $this->excel->getActiveSheet()->setCellValue('E' . $i, $recr->td_billing_entity);
            $this->excel->getActiveSheet()->setCellValue('F' . $i, $recr->td_empid);
            $this->excel->getActiveSheet()->setCellValue('G' . $i, $recr->td_pax);
            $this->excel->getActiveSheet()->setCellValue('H' . $i, $recr->td_carrier);
            $this->excel->getActiveSheet()->setCellValue('I' . $i, $recr->td_tkt);
            $this->excel->getActiveSheet()->setCellValue('J' . $i, $recr->td_flight);
            $this->excel->getActiveSheet()->setCellValue('K' . $i, $recr->td_sector);
            $this->excel->getActiveSheet()->setCellValue('L' . $i, $recr->td_departure);
            $this->excel->getActiveSheet()->setCellValue('M' . $i, $recr->td_cabin_class);
            $this->excel->getActiveSheet()->setCellValue('N' . $i, $recr->td_tkt_fare);
            $this->excel->getActiveSheet()->setCellValue('O' . $i, $recr->td_net);
            $this->excel->getActiveSheet()->setCellValue('P' . $i, $recr->td_air_tax);
            $this->excel->getActiveSheet()->setCellValue('Q' . $i, $recr->td_airline_pnr);
            $this->excel->getActiveSheet()->setCellValue('R' . $i, $recr->td_pnr);
        }
        $f_name = str_replace('.', '-valid.', $f_name);
        header('Content-Type: application/vnd.ms-excel'); //mime type
        header('Content-Disposition: attachment;filename="' . $f_name . '"'); //tell browser what's the file name
        header('Cache-Control: max-age=0'); //no cache
        $objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5');
        ob_end_clean();
        ob_start();
        $objWriter->save('php://output');
        //        echo "<script>window.close();</script>";
        //        exit;
    }

    function gstr2a_valid_download($gd_gid)
    {
        $this->load->library('excel');
        $this->load->helper('download');
        $this->excel->setActiveSheetIndex(0);

        $this->excel->getActiveSheet()->setTitle('Sheet 1');
        $this->excel->getActiveSheet()->setCellValue('A1', 'Trade/Legal name of the Supplier');
        $this->excel->getActiveSheet()->setCellValue('B1', 'Supplier Type');
        $this->excel->getActiveSheet()->setCellValue('C1', 'GSTIN of supplier');
        $this->excel->getActiveSheet()->setCellValue('D1', 'Invoice number');
        $this->excel->getActiveSheet()->setCellValue('E1', 'Document Type');
        $this->excel->getActiveSheet()->setCellValue('F1', 'Invoice type');
        $this->excel->getActiveSheet()->setCellValue('G1', 'Invoice Date');
        $this->excel->getActiveSheet()->setCellValue('H1', 'Invoice Value (₹)');
        $this->excel->getActiveSheet()->setCellValue('I1', 'Place of supply');
        $this->excel->getActiveSheet()->setCellValue('J1', 'Supply Attract Reverse Charge');
        $this->excel->getActiveSheet()->setCellValue('K1', 'Rate (%)');
        $this->excel->getActiveSheet()->setCellValue('L1', 'Taxable Value (₹)');
        $this->excel->getActiveSheet()->setCellValue('M1', 'Integrated Tax  (₹)');
        $this->excel->getActiveSheet()->setCellValue('N1', 'Central Tax (₹)');
        $this->excel->getActiveSheet()->setCellValue('o1', 'State/UT tax (₹)');
        $this->excel->getActiveSheet()->setCellValue('p1', 'Cess  (₹)');
        $this->excel->getActiveSheet()->setCellValue('Q1', 'Counter Party Return status');

        $this->excel->getActiveSheet()->getStyle('A1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('B1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('B1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('C1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('C1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('D1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('D1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('E1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('E1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('F1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('F1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('G1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('G1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('H1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('H1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('I1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('I1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('J1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('J1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('K1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('K1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('L1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('L1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('M1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('M1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('N1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('N1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('O1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('O1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('P1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('P1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('Q1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('Q1')->getFont()->setBold(true);

        $this->db->from('gstr2a_data gd');
        $this->db->join('files f', 'gd.gd_id=f.f_id', 'left');
        $this->db->where('gd_gid', $gd_gid);
        $rec = $this->db->get();
        $i = 1;
        foreach ($rec->result() as $recr) {
            if ($i == 1)
                $f_name = $recr->f_file;
            $this->excel->getActiveSheet()->setCellValue('A' . ++$i, $recr->gd_supplier_type);
            $this->excel->getActiveSheet()->setCellValue('B' . $i, $recr->gd_supplier_name);
            $this->excel->getActiveSheet()->setCellValue('C' . $i, $recr->gd_supplier_gstin);
            $this->excel->getActiveSheet()->setCellValue('D' . $i, $recr->gd_inv_no);
            $this->excel->getActiveSheet()->setCellValue('E' . $i, $recr->gd_doc_type);
            $this->excel->getActiveSheet()->setCellValue('F' . $i, $recr->gd_inv_type);
            $this->excel->getActiveSheet()->setCellValue('G' . $i, date('d-m-Y', strtotime($recr->gd_inv_date)));
            $this->excel->getActiveSheet()->setCellValue('H' . $i, $recr->gd_inv_value);
            $this->excel->getActiveSheet()->setCellValue('I' . $i, $recr->gd_supply_place);
            $this->excel->getActiveSheet()->setCellValue('J' . $i, $recr->gd_supply_reverse_charge);
            $this->excel->getActiveSheet()->setCellValue('K' . $i, $recr->gd_rate_perc);
            $this->excel->getActiveSheet()->setCellValue('L' . $i, $recr->gd_taxable_value);
            $this->excel->getActiveSheet()->setCellValue('M' . $i, $recr->gd_integrated_tax);
            $this->excel->getActiveSheet()->setCellValue('N' . $i, $recr->gd_central_tax);
            $this->excel->getActiveSheet()->setCellValue('O' . $i, $recr->gd_state_tax);
            $this->excel->getActiveSheet()->setCellValue('P' . $i, $recr->gd_cess);
            $this->excel->getActiveSheet()->setCellValue('Q' . $i, $recr->gd_return_status);
        }
        $f_name = str_replace('.', '-valid.', $f_name);
        header('Content-Type: application/vnd.ms-excel'); //mime type
        header('Content-Disposition: attachment;filename="' . $f_name . '"'); //tell browser what's the file name
        header('Cache-Control: max-age=0'); //no cache
        $objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5');
        ob_end_clean();
        ob_start();
        $objWriter->save('php://output');
        //        echo "<script>window.close();</script>";
        //        exit;
    }

    function get_cts()
    {
        $this->checksess();
        $data = $this->corp->get_cts();
        $monthAry = array('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec');
        $fary = array();
        foreach ($data->result() as $dat) {
            $dat->f_min_date_ref = '';
            $dat->f_max_date_ref = '';
            if ($dat->f_min_date != '')
                $dat->f_min_date_ref = date('d-m-Y', strtotime($dat->f_min_date));
            if ($dat->f_max_date != '')
                $dat->f_max_date_ref = date('d-m-Y', strtotime($dat->f_max_date));

            $refm = $dat->f_month - 1;
            $dat->inv_ref = $monthAry[$refm] . "' " . substr($dat->f_year, 2, 2);
            $dat->u_name = ucfirst(strtolower($dat->u_name));
            $dat->f_uploadedat_ref = date('d-m-Y H:i', strtotime($dat->f_uploadedat));
            $fary['data'][] = $dat;
        }
        echo json_encode($fary);
    }

    function get_gstr2a()
    {
        $this->checksess();
        $data = $this->corp->get_gstr2a();
        $monthAry = array('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec');
        $fary = array();
        foreach ($data->result() as $dat) {
            $dat->f_min_date_ref = '';
            $dat->f_max_date_ref = '';
            if ($dat->f_min_date != '')
                $dat->f_min_date_ref = date('d-m-Y', strtotime($dat->f_min_date));
            if ($dat->f_max_date != '')
                $dat->f_max_date_ref = date('d-m-Y', strtotime($dat->f_max_date));

            $refm = $dat->f_month - 1;
            $dat->inv_ref = $monthAry[$refm] . "' " . substr($dat->f_year, 2, 2);
            $dat->u_name = ucfirst(strtolower($dat->u_name));
            $dat->f_uploadedat_ref = date('d-m-Y H:i', strtotime($dat->f_uploadedat));
            $fary['data'][] = $dat;
        }
        echo json_encode($fary);
    }

    public function p_client1_sample_download()
    {
        $this->checksess();
        $this->load->helper('download');
        $pth = file_get_contents(dirname(dirname(dirname(__FILE__))) . '/uploads/Client_Upload1_Sample.xlsx');
        force_download('BTA_CTA_Raw_File_Upload_Sample.xlsx', $pth);
    }

    public function p_client2_sample_download()
    {
        $this->checksess();
        $this->load->helper('download');
        $pth = file_get_contents(dirname(dirname(dirname(__FILE__))) . '/uploads/Client_Upload2_Sample.xlsx');
        force_download('Reconciliation_Upload_Sample.xlsx', $pth);
    }

    public function p_payment_advice_sample_download()
    {
        $this->checksess();
        $this->load->helper('download');
        $pth = file_get_contents(dirname(dirname(dirname(__FILE__))) . '/uploads/Payment_Advice_Sample.xlsx');
        force_download('Payment_Advice_Sample.xlsx', $pth);
    }


    public function p_tmc_sample_download()
    {
        $this->checksess();
        $this->load->helper('download');
        $pth = file_get_contents(dirname(dirname(dirname(__FILE__))) . '/uploads/Payment_TMC_Sample.xlsx');
        force_download('Payment_TMC_Sample.xlsx', $pth);
    }

    public function tmc_sample_download()
    {
        $this->checksess();
        $this->load->helper('download');
        $pth = file_get_contents(dirname(dirname(dirname(__FILE__))) . '/uploads/TMC_Sample.xlsx');
        force_download('TMC_Sample.xlsx', $pth);
    }

    public function cts_sample_download()
    {
        $this->checksess();
        $this->load->helper('download');
        $pth = file_get_contents(dirname(dirname(dirname(__FILE__))) . '/uploads/CTS_Sample.xlsx');
        force_download('CTS_Sample.xlsx', $pth);
    }

    public function gstr2a_sample_download()
    {
        $this->checksess();
        $this->load->helper('download');
        $pth = file_get_contents(dirname(dirname(dirname(__FILE__))) . '/uploads/GSTR2A_Sample.xlsx');
        force_download('GSTR2A_Sample.xlsx', $pth);
    }

    public function d_record()
    {
        $data = $this->input->post('id');
        $ftype = $this->input->post('type');

        if ($ftype == 'Raw') {
            $this->gstr2a_file_download($data);
        } else if ($ftype == 'Validated') {
            $this->gstr2a_valid_download($data);
        }
    }

    public function p_d_record_c1()
    {
        // download option for bta cta valid or raws
        $data = $this->input->post('id');
        $ftype = $this->input->post('type');

        if ($ftype == 'Raw') {
            $this->p_c1_file_download($data);
        } else if ($ftype == 'Validated') {
            $this->p_c1_valid_download($data);
        }
    }
    public function p_d_record_c2()
    {
        $data = $this->input->post('id');
        $ftype = $this->input->post('type');

        if ($ftype == 'Raw') {
            $this->p_c2_file_download($data);
        } else if ($ftype == 'Validated') {
            $this->p_c2_valid_download($data);
        }
    }

    public function p_d_record_tmc()
    {
        $data = $this->input->post('id');
        $ftype = $this->input->post('type');

        if ($ftype == 'Raw') {
            $this->p_tmc_file_download($data);
        } else if ($ftype == 'Validated') {
            $this->p_tmc_valid_download($data);
        }
    }

    public function d_record_tmc()
    {
        $data = $this->input->post('id');
        $ftype = $this->input->post('type');

        if ($ftype == 'Raw') {
            $this->tmc_file_download($data);
        } else if ($ftype == 'Validated') {
            $this->tmc_valid_download($data);
        }
    }

    public function download_airline_invoice($refid)
    {
        $this->checksess();
        $this->load->helper('download');
        $fname = $refid;
        //        $fname = $this->corp->get_gstr2a_file($fid);
        if (file_exists(dirname(dirname(dirname(__FILE__))) . '/uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'] . '/airline_invoice/' . $fname)) {
            $pth = file_get_contents(dirname(dirname(dirname(__FILE__))) . '/uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'] . '/airline_invoice/' . $fname);
            force_download($fname, $pth);
        } else
            echo 'Invalid request';
    }

    public function download_tmc_invoice($refid)
    {
        $this->checksess();
        $this->load->helper('download');
        $fname = $refid . '.pdf';
        //        $fname = $this->corp->get_gstr2a_file($fid);
        if (file_exists(dirname(dirname(dirname(__FILE__))) . '/uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'] . '/tmc_invoice/' . $fname)) {
            $pth = file_get_contents(dirname(dirname(dirname(__FILE__))) . '/uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'] . '/tmc_invoice/' . $fname);
            force_download($fname, $pth);
        } else
            echo 'Invalid request';
    }

    //    public function dl_tmc_inv(){
    //        if(isset($_GET['fname'])){
    //            $fname = str_replace(' ','_',$_GET['fname']);
    //            $this->checksess();
    //            $this->load->helper('download');
    //            echo dirname( dirname( dirname(__FILE__) ) ).'/uploads/'.$this->session->userdata['recon_sess_i']['u_corporateid'].'/tmc_invoice/'.$fname;
    //            if(file_exists(dirname( dirname( dirname(__FILE__) ) ).'/uploads/'.$this->session->userdata['recon_sess_i']['u_corporateid'].'/tmc_invoice/'.$fname)){
    //                $pth = file_get_contents(dirname( dirname( dirname(__FILE__) ) ).'/uploads/'.$this->session->userdata['recon_sess_i']['u_corporateid'].'/tmc_invoice/'.$fname);
    //                force_download($fname, $pth);
    //            }
    //            else
    //               echo 'Invalid request';
    //        }
    //    }

    public function gstr2a_file_download($fid)
    {
        $this->checksess();
        $this->load->helper('download');
        $fname = $this->corp->get_gstr2a_file($fid);
        if (file_exists(dirname(dirname(dirname(__FILE__))) . '/uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'] . '/gstr2a/' . $fname)) {
            $pth = file_get_contents(dirname(dirname(dirname(__FILE__))) . '/uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'] . '/gstr2a/' . $fname);
            force_download($fname, $pth);
        } else
            echo 'Invalid request';
    }

    public function p_c1_file_download($fid)
    {
        // used for download raw file bta cta//
        $this->checksess();
        $this->load->helper('download');
        $fname = $this->corp->get_p_tmc_file($fid);
        if (file_exists(dirname(dirname(dirname(__FILE__))) . '/uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'] . '/p_c1/' . $fname)) {
            $pth = file_get_contents(dirname(dirname(dirname(__FILE__))) . '/uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'] . '/p_c1/' . $fname);
            force_download($fname, $pth);
        } else
            echo 'Invalid request';
    }
    public function p_c2_file_download($fid)
    {
        $this->checksess();
        $this->load->helper('download');
        $fname = $this->corp->get_p_c2_file($fid);
        // print_r($fname);die;
        if (file_exists(dirname(dirname(dirname(__FILE__))) . '/uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'] . '/p_c2/' . $fname)) {
            $pth = file_get_contents(dirname(dirname(dirname(__FILE__))) . '/uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'] . '/p_c2/' . $fname);
            force_download($fname, $pth);
        } else
            echo 'Invalid request';
    }

    public function p_tmc_file_download($fid)
    {
        $this->checksess();
        $this->load->helper('download');
        $fname = $this->corp->get_p_tmc_file_tmc($fid);
        // print_r($fname);die;
        if (file_exists(dirname(dirname(dirname(__FILE__))) . '/uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'] . '/p_tmc/' . $fname)) {
            $pth = file_get_contents(dirname(dirname(dirname(__FILE__))) . '/uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'] . '/p_tmc/' . $fname);
            force_download($fname, $pth);
        } else
            echo 'Invalid request';
    }

    public function tmc_file_download($fid)
    {
        $this->checksess();
        $this->load->helper('download');
        $fname = $this->corp->get_tmc_file($fid);
        if (file_exists(dirname(dirname(dirname(__FILE__))) . '/uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'] . '/tmc/' . $fname)) {
            $pth = file_get_contents(dirname(dirname(dirname(__FILE__))) . '/uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'] . '/tmc/' . $fname);
            force_download($fname, $pth);
        } else
            echo 'Invalid request';
    }

    public function checksess()
    {
        if (isset($this->session->userdata['recon_sess_i']['u_role'])) {
            if ($this->session->userdata['recon_sess_i']['u_role'] == 'admin')
                redirect('admin');
        } else
            redirect('login');
    }

    public function upload_pc2()
    {
        $this->checksess();

        $monthAllAryLabel = array('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec');

        $insdata = $this->input->post('usr');
        $ext = pathinfo($_FILES['tmc_file']['name'], PATHINFO_EXTENSION);
        $filename = 'C2-' . str_replace(' ', '_', $insdata['pf_agent']) . '-' . time() . '.' . $ext;
        //        $filename = 'C2-'.str_replace(' ','_',$insdata['pf_agent']).'-'.$monthAllAryLabel[$insdata['pf_month']-1].'-'.$insdata['pf_year'].'-'.time().'.'.$ext;

        $conchk['pf_month'] = $insdata['pf_month'];
        $conchk['pf_year'] = $insdata['pf_year'];
        $conchk['pf_agent'] = $insdata['pf_agent'];
        $conchk['pf_corporateid'] = $this->session->userdata['recon_sess_i']['u_corporateid'];

        if (!is_dir(dirname(dirname(dirname(__FILE__))) . '/uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'])) {
            mkdir(dirname(dirname(dirname(__FILE__))) . '/uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid']);
        }

        if (!is_dir(dirname(dirname(dirname(__FILE__))) . '/uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'] . '/p_c2')) {
            mkdir(dirname(dirname(dirname(__FILE__))) . '/uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'] . '/p_c2');
        }

        $config['file_name'] = $filename;
        $config['upload_path'] = './uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'] . '/p_c2/';
        $config['allowed_types'] = '*';
        $config['max_size'] = 3000;
        $this->load->library('upload', $config);

        if (!$this->upload->do_upload('tmc_file')) {
            $error = array('error' => $this->upload->display_errors());
            echo $error['error'];
        } else {
            $data = array('upload_data' => $this->upload->data());

            $fieldAry = array("STMT REF NO", "INV/CRN", "INV/CRN REF NO.", "UNPAIDAMT");

            $labelAry = array("pc2_stmt_ref_no", "pc2_inv_crn", "pc2_inv_crn_ref_no", "pc2_unpaid_amt");

            $inserror = 0;
            $this->load->library('excel');
            //read file from path
            $filepath = './uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'] . '/p_c2/' . $filename;
            $objPHPExcel = PHPExcel_IOFactory::load($filepath);

            $worksheet = $objPHPExcel->getActiveSheet();
            $highestRow = (int)$worksheet->getHighestRow();
            $highestColumn = $worksheet->getHighestColumn();
            $seterror = 0;
            $ferror = array();
            $pcols = 4;
            $ttlrows = 0;
            for ($row = 1; $row <= $highestRow; $row++) {
                if ($row == 1) {
                    for ($col = 0; $col < $pcols; $col++) {
                        $cell = $worksheet->getCellByColumnAndRow($col, $row);
                        $cellref = $cell;
                        $headingary[] = trim(strval($cell));
                    }
                } else {
                    if ($fieldAry != $headingary) {
                        //                        print_r($fieldAry);
                        //                        echo '<br/>';
                        //                        print_r($headingary);
                        $rdata['status'] = 13;
                        $ferror['column'] = 'Column mismatch.';
                        $rdata['error'] = $ferror;
                        echo json_encode($rdata);
                        exit;
                    } else {
                        $ttlrows++;
                        $chkcell = $worksheet->getCellByColumnAndRow(1, $row);
                        if ($chkcell != '') {
                            $keys = '';
                            $values = '';
                            for ($col = 0; $col < $pcols; $col++) {
                                $cell = $worksheet->getCellByColumnAndRow($col, $row);
                                if ($col == 0) {
                                    $cell = $worksheet->getCellByColumnAndRow($col, $row)->getFormattedValue();
                                }
                                $cell = strtoupper($cell);
                                $cc = strval($cell);
                                $cc = trim($cc);
                                if ($cell != '') {
                                    $keys .= $labelAry[$col] . ',';
                                    if ($col == 0) {
                                        $cc = preg_replace('/\s+/', '', trim($cc));
                                        if ($cc != '') {
                                            if (!preg_match("@^[0-9']+$@", $cc)) {
                                                $seterror = 1;
                                                //                                        $ferror[$row]['column'][] = 'GST INVOICE NO';
                                                $ferror[$row]['column'][] = 1;
                                                $ferror[$row]['row'] = $row;
                                            }
                                        }
                                        $values .= '"' . $cc . '",';
                                    } else if ($col == 1 || $col == 2) {
                                        $cc = preg_replace('/\s+/', ' ', trim($cc));
                                        if (!preg_match("@^[0-9a-zA-Z]+$@", $cc)) {
                                            $seterror = 1;
                                            //                                        $ferror[$row]['column'][] = 'GST INVOICE NO';
                                            $ferror[$row]['column'][] = 9;
                                            $ferror[$row]['row'] = $row;
                                        }
                                        $values .= '"' . $cc . '",';
                                    } else {
                                        $values .= '"' . $cell . '",';
                                    }
                                }
                            }

                            //                            $chk = $this->db->query('select * from tmc_data where td_gst_inv = "'.$td_gst_inv.'"');
                            //                            
                            //                            if($chk->num_rows()>0){
                            //                                $inserror++;
                            //                            }

                            //                            $insArym[] = 'insert into p_tmc_data_dump ('.substr($keys,0,-1).',ptd_ptfid) values ('.substr($values,0,-1); 
                            $insAry[] = 'insert into p_client_upload_2 (' . substr($keys, 0, -1) . ',pc2_pc2fid) values (' . substr($values, 0, -1);
                        }
                    }
                }
            }
            //            print_r($ferror); exit;

            $ferror = array_values($ferror);
            //            print_r($ferror);
            //            $rdata['status'] = 0;
            //            $rdata['error'] = $ferror;
            //            echo json_encode($rdata);
            //            exit;

            $insdata['pf_corporateid'] = $this->session->userdata['recon_sess_i']['u_corporateid'];
            $insdata['pf_file'] = $filename;
            $insdata['pf_type'] = 'c2';
            $insdata['pf_status'] = 'Initiated';
            $insdata['pf_uploadedby'] = $this->session->userdata['recon_sess_i']['u_id'];
            $insdata['pf_uploadedat'] = date('Y-m-d H:i:s', time());
            $this->db->insert('p_files', $insdata);
            $td_tfid = $this->db->insert_id();
            $rdata['insref'] = $td_tfid;

            //            for($c=0;$c<count($insArym);$c++){
            ////                try{
            //                    $this->db->query($insArym[$c].',"'.$td_tfid.'")');
            ////                }
            ////                catch(Exception $e){
            ////                    continue;
            ////                }
            //            }

            if (!empty($ferror)) {

                $rdata['duprecords'] = 0;
                $rdata['status'] = 0;
                //                $rdata['error'] = $ferror;

                if ($inserror != 0)
                    $rdata['duprecords'] = $inserror;
                $rdata['ttlrows'] = $ttlrows;
                //                $rdata['error'] = $ferror;
                $rdata['invalidrows'] = count($ferror);
                echo json_encode($rdata);
                exit;
            } else {

                $reccnt = $highestRow;
                $inscnt = 0;

                for ($c = 0; $c < count($insAry); $c++) {
                    try {
                        $this->db->query($insAry[$c] . ',"' . $td_tfid . '")');
                        if ($this->db->affected_rows())
                            $inscnt++;
                    } catch (Exception $e) {
                        continue;
                    }
                }
                //                $datequery = $this->db->query('select min(ptd_inv_date) mindate, max(ptd_inv_date) maxdate from p_client_upload_1 where pc1_pc1fid = '.$td_tfid);
                //                $dateAry = array();
                //                foreach($datequery->result() as $dateq){
                //                    $dateCon['pf_id'] = $td_tfid;
                //                    $dateAry['pf_min_date'] = $dateq->mindate;
                //                    $dateAry['pf_max_date'] = $dateq->maxdate;
                //                }
                //                $this->db->where($dateCon);
                //                $this->db->update('p_files',$dateAry);

                $rdata['status'] = 1;
                $rdata['added_records'] = $inscnt;
                echo json_encode($rdata);
            }
        }
    }

    public function upload_pc1()
    {
        $this->checksess();

        $monthAllAryLabel = array('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec');

        $insdata = $this->input->post('usr');
        $ref_date = $this->input->post('ref_date');
        $ext = pathinfo($_FILES['tmc_file']['name'], PATHINFO_EXTENSION);
        $filename = 'C1-' . str_replace(' ', '_', $insdata['pf_agent']) . '-' . time() . '.' . $ext;
        //        $filename = 'C1-'.str_replace(' ','_',$insdata['pf_agent']).'-'.$monthAllAryLabel[$insdata['pf_month']-1].'-'.$insdata['pf_year'].'-'.time().'.'.$ext;

        $conchk['pf_month'] = $insdata['pf_month'];
        $conchk['pf_year'] = $insdata['pf_year'];
        $conchk['pf_agent'] = $insdata['pf_agent'];
        $conchk['pf_corporateid'] = $this->session->userdata['recon_sess_i']['u_corporateid'];

        if (!is_dir(dirname(dirname(dirname(__FILE__))) . '/uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'])) {
            mkdir(dirname(dirname(dirname(__FILE__))) . '/uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid']);
        }

        if (!is_dir(dirname(dirname(dirname(__FILE__))) . '/uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'] . '/p_c1')) {
            mkdir(dirname(dirname(dirname(__FILE__))) . '/uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'] . '/p_c1');
        }

        $config['file_name'] = $filename;
        $config['upload_path'] = './uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'] . '/p_c1/';
        $config['allowed_types'] = '*';
        $config['max_size'] = 3000;
        $this->load->library('upload', $config);

        if (!$this->upload->do_upload('tmc_file')) {
            $error = array('error' => $this->upload->display_errors());
            echo $error['error'];
        } else {
            $data = array('upload_data' => $this->upload->data());

            //            $fieldAry = array("STMT DT","STMT REF NO","CUST REF","TRIP RQSIT","PAX NAME","UNPAIDAMT","BKNG DATE","DEPT DATE","TICKET NO","CARRIER","CLASS","ROUTING","TXN DATE","PAYMENT ST","DISPUTE REASON","MISSING SUPPORTING DOCUMENT","COMMENT1","COMMENT2","COMMENT3","REMARKS");

            $fieldAry = array("STMT DT", "STMT REF NO", "CUST REF", "TRIP RQSIT", "JOB NO", "PAX NAME", "UNPAIDAMT", "BKNG DATE", "DEPT DATE", "TICKET NO", "CARRIER", "CLASS", "ROUTING", "TXN DATE", "PAYMENT ST", "DISPUTE REASON", "MISSING SUPPORTING DOCUMENT", "COMMENT1", "COMMENT2", "COMMENT3", "REMARKS");

            $labelAry = array("pc1_stmt_date", "pc1_stmt_ref_no", "pc1_customer_ref", "pc1_trip_req", "pc1_job_no", "pc1_pax_name", "pc1_unpaid_amt", "pc1_booking_date", "pc1_dep_date", "pc1_ticket_no", "pc1_carrier", "pc1_class", "pc1_routing", "pc1_txn_date", "pc1_payment_stmt", "pc1_dispute_reason", "pc1_missing_support_document", "pc1_comment1", "pc1_comment2", "pc1_comment3", "pc1_remarks");

            $inserror = 0;
            $this->load->library('excel');
            //read file from path
            $filepath = './uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'] . '/p_c1/' . $filename;
            $objPHPExcel = PHPExcel_IOFactory::load($filepath);

            $worksheet = $objPHPExcel->getActiveSheet();
            $highestRow = (int)$worksheet->getHighestRow();
            $highestColumn = $worksheet->getHighestColumn();
            $seterror = 0;
            $ferror = array();
            $pcols = 21;
            $ttlrows = 0;
            $encounteredTickets = array();
            for ($row = 1; $row <= $highestRow; $row++) {
                // Assuming the ticket number is in column index 9 (adjust if needed)
                $tktCell = $worksheet->getCellByColumnAndRow(9, $row)->getValue();
                $tktCell = strtoupper(trim($tktCell));


                if (!empty($tktCell)) {
                    if (in_array($tktCell, $encounteredTickets)) {
                        // If encountered, get the statement reference number from the first occurrence
                        $firstOccurrenceRow = array_search($tktCell, $encounteredTickets);
                        $statement = $worksheet->getCellByColumnAndRow(1, $firstOccurrenceRow)->getValue();

                        // Update the current row in the Excel sheet with the statement reference number
                        $worksheet->setCellValueByColumnAndRow(20, $row, 'dupe found/' . $statement);
                        $worksheet->setCellValueByColumnAndRow(15, $row, 'POTENTIAL DUPE/');
                    } else {
                        // If not encountered, add the ticket number to the encountered tickets array
                        $encounteredTickets[$row] = $tktCell;
                    }
                }
                $checkDuplicateQuery = $this->db->query('SELECT * FROM p_client_upload_1 WHERE pc1_ticket_no = "' . $tktCell . '"');
                // print_r($checkDuplicateQuery);
                if ($checkDuplicateQuery->num_rows() > 0) {
                    $duplicateRecords = $checkDuplicateQuery->result();
                    // print_r($duplicateRecords);
                    $statement = $duplicateRecords[0]->pc1_stmt_ref_no;
                    // print_r($statement);
                    $worksheet->setCellValueByColumnAndRow(20, $row, 'dupe found/' . $statement . '');
                    $worksheet->setCellValueByColumnAndRow(15, $row, 'POTENTIAL DUPE'); // Adjust the column index accordingly
                } else {
                }

                // Check for duplicate ticket numbers within the file


                if ($row == 1) {
                    for ($col = 0; $col < $pcols; $col++) {
                        $cell = $worksheet->getCellByColumnAndRow($col, $row);
                        $cellref = $cell;
                        $headingary[] = trim(strval($cell));
                    }
                } else {
                    if ($fieldAry != $headingary) {
                        //                        print_r($fieldAry);
                        //                        echo '<br/>';
                        //                        print_r($headingary);
                        $rdata['status'] = 13;
                        $ferror['column'] = 'Column mismatch.';
                        $rdata['error'] = $ferror;
                        echo json_encode($rdata);
                        exit;
                    } else {
                        $ttlrows++;
                        $chkcell = $worksheet->getCellByColumnAndRow(1, $row);
                        if ($chkcell != '') {
                            $keys = '';
                            $values = '';
                            for ($col = 0; $col < $pcols; $col++) {
                                $cell = $worksheet->getCellByColumnAndRow($col, $row);
                                $cell = strtoupper($cell);
                                $cc = strval($cell);
                                $cc = trim($cc);
                                if ($cell != '') {
                                    // $tktCheck = $this->db->query('SELECT * FROM p_client_upload_1 WHERE pc1_ticket_no = "'.$cell.'"');

                                    // if ($tktCheck->num_rows() > 0) {
                                    //     // pc1_tkt number exists in the database
                                    //     // Update pc1_remarks field instead of inserting a new record
                                    //     $updateQuery = 'UPDATE p_client_upload_1 SET pc1_remarks = "Duplicate entry" WHERE pc1_ticket_no = "'.$cell.'"';
                                    //     $this->db->query($updateQuery);
                                    // } else {

                                    // }
                                    $keys .= $labelAry[$col] . ',';
                                    if ($col == 1) {
                                        $cc = preg_replace('/\s+/', ' ', trim($cc));
                                        if (!preg_match("@^[0-9']+$@", $cc)) {
                                            $seterror = 1;
                                            //                                        $ferror[$row]['column'][] = 'GST INVOICE NO';
                                            $ferror[$row]['column'][] = 1;
                                            $ferror[$row]['row'] = $row;
                                        }
                                        $td_gst_inv = $cc;
                                        $values .= '"' . $cc . '",';
                                    } else if ($col == 5) {
                                        $cc = preg_replace('/\s+/', ' ', trim($cc));
                                        if (!preg_match("@^[A-Za-z\/\s\-.]+$@", $cc)) {
                                            $seterror = 1;
                                            //                                        $ferror[$row]['column'][] = 'GST INVOICE NO';
                                            $ferror[$row]['column'][] = 9;
                                            $ferror[$row]['row'] = $row;
                                        }
                                        $values .= '"' . $cc . '",';
                                    } else if ($col == 6) {
                                        $cc = preg_replace('/\s+/', ' ', trim($cc));
                                        if (!preg_match("@^[0-9\-.]+$@", $cc)) {
                                            $seterror = 1;
                                            //                                        $ferror[$row]['column'][] = 'GST INVOICE NO';
                                            $ferror[$row]['column'][] = 10;
                                            $ferror[$row]['row'] = $row;
                                        }
                                        $values .= '"' . $cc . '",';
                                    } else {
                                        $values .= '"' . $cell . '",';
                                    }
                                }
                            }

                            $chk = $this->db->query('select * from p_client_upload_1 where pc1_stmt_ref_no = "' . $td_gst_inv . '"');

                            if ($chk->num_rows() > 0) {
                                $inserror++;
                            }

                            //                            $insArym[] = 'insert into p_tmc_data_dump ('.substr($keys,0,-1).',ptd_ptfid) values ('.substr($values,0,-1); 
                            $insAry[] = 'insert ignore into p_client_upload_1 (' . substr($keys, 0, -1) . ',pc1_pc1fid) values (' . substr($values, 0, -1);
                            //                            $insAry[] = 'replace into p_client_upload_1 ('.substr($keys,0,-1).',pc1_pc1fid) values ('.substr($values,0,-1);
                        }
                    }
                }
            }
            //            print_r($ferror); exit;

            $ferror = array_values($ferror);
            //            print_r($ferror);
            //            $rdata['status'] = 0;
            //            $rdata['error'] = $ferror;
            //            echo json_encode($rdata);
            //            exit;

            $insdata['pf_corporateid'] = $this->session->userdata['recon_sess_i']['u_corporateid'];
            $insdata['pf_file'] = $filename;
            $insdata['pf_type'] = 'c1';
            $insdata['pf_payment_due_date'] = $ref_date;
            $insdata['pf_status'] = 'Initiated';
            $insdata['pf_uploadedby'] = $this->session->userdata['recon_sess_i']['u_id'];
            $insdata['pf_uploadedat'] = date('Y-m-d H:i:s', time());
            $this->db->insert('p_files', $insdata);
            $td_tfid = $this->db->insert_id();
            $rdata['insref'] = $td_tfid;

            //            for($c=0;$c<count($insArym);$c++){
            ////                try{
            //                    $this->db->query($insArym[$c].',"'.$td_tfid.'")');
            ////                }
            ////                catch(Exception $e){
            ////                    continue;
            ////                }
            //            }

            if (!empty($ferror)) {
                $rdata['duprecords'] = 0;
                $rdata['status'] = 0;
                //                $rdata['error'] = $ferror;

                if ($inserror != 0)
                    $rdata['duprecords'] = $inserror;
                $rdata['ttlrows'] = $ttlrows;
                //                $rdata['error'] = $ferror;
                $rdata['invalidrows'] = count($ferror);
                echo json_encode($rdata);
                exit;
            } else {
                $reccnt = $highestRow;
                $inscnt = 0;

                for ($c = 0; $c < count($insAry); $c++) {
                    try {
                        $this->db->query($insAry[$c] . ',"' . $td_tfid . '")');
                        if ($this->db->affected_rows())
                            $inscnt++;
                    } catch (Exception $e) {
                        continue;
                    }
                }
                //                $datequery = $this->db->query('select min(ptd_inv_date) mindate, max(ptd_inv_date) maxdate from p_client_upload_1 where pc1_pc1fid = '.$td_tfid);
                //                $dateAry = array();
                //                foreach($datequery->result() as $dateq){
                //                    $dateCon['pf_id'] = $td_tfid;
                //                    $dateAry['pf_min_date'] = $dateq->mindate;
                //                    $dateAry['pf_max_date'] = $dateq->maxdate;
                //                }
                //                $this->db->where($dateCon);
                //                $this->db->update('p_files',$dateAry);

                $rdata['status'] = 1;
                $rdata['added_records'] = $inscnt;
                echo json_encode($rdata);
            }
        }
    }

    public function upload_ptmc()
    {
        $this->checksess();

        $monthAllAryLabel = array('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec');

        $insdata = $this->input->post('usr');
        $ext = pathinfo($_FILES['tmc_file']['name'], PATHINFO_EXTENSION);
        $filename = 'TMC-' . str_replace(' ', '_', $insdata['pf_agent']) . '-' . time() . '.' . $ext;
        //        $filename = 'TMC-'.str_replace(' ','_',$insdata['pf_agent']).'-'.$monthAllAryLabel[$insdata['pf_month']-1].'-'.$insdata['pf_year'].'-'.time().'.'.$ext;

        $conchk['pf_month'] = $insdata['pf_month'];
        $conchk['pf_year'] = $insdata['pf_year'];
        $conchk['pf_agent'] = $insdata['pf_agent'];
        $conchk['pf_corporateid'] = $this->session->userdata['recon_sess_i']['u_corporateid'];
        //        $entryres = $this->db->get_where('files',$conchk);
        //        if($entryres->num_rows()>0){
        //            $rdata['status'] = 31; echo json_encode($rdata); exit;
        //        }

        if (!is_dir(dirname(dirname(dirname(__FILE__))) . '/uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'])) {
            mkdir(dirname(dirname(dirname(__FILE__))) . '/uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid']);
            mkdir(dirname(dirname(dirname(__FILE__))) . '/uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'] . '/gstr2a');
            mkdir(dirname(dirname(dirname(__FILE__))) . '/uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'] . '/tmc');
        }

        if (!is_dir(dirname(dirname(dirname(__FILE__))) . '/uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'] . '/p_tmc')) {
            mkdir(dirname(dirname(dirname(__FILE__))) . '/uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'] . '/p_tmc');
        }

        $config['file_name'] = $filename;
        $config['upload_path'] = './uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'] . '/p_tmc/';
        $config['allowed_types'] = '*';
        $config['max_size'] = 3000;
        $this->load->library('upload', $config);

        if (!$this->upload->do_upload('tmc_file')) {
            $error = array('error' => $this->upload->display_errors());
            echo $error['error'];
        } else {
            $data = array('upload_data' => $this->upload->data());

            $fieldAry = array("TMC NAME", "TMC GSTIN NO", "SEGMENT", "INV DATE", "GST INV/CRN NO", "GST INV/CRN REF NO", "DOCUMENT TYPE", "BILLING ENTITYNAME", "CUSTOMER GSTIN NO", "BILLABLE", "BUSINESS UNIT", "TRIP CODE", "COST CENTRE", "BUDGET CODE", "P.O NO", "LOCATION", "DEPARTMENT", "EMP ID", "BOOKING RCVD FROM", "PASSENGER NAME", "PASSENGER TYPE", "AIRLINE CODE", "AIRLINE NAME", "TICKET NO", "FLIGHTNO", "SECTOR", "DEP START DATE", "ARRVL END DATE", "CABINCLASS", "FARECLASS", "BASIC FARE", "K3", "IGST", "CGST", "SGST", "MEAL", "SEAT", "OTHER CHARGES", "TKT FARE", "SERVICE CHARGES", "DISCOUNT", "TMC CAN CHARGES", "PANALTY", "CARD CHARGES", "IGST ON SER CHARGES", "CGST ON SER CHARGES", "SGST ON SER CHARGES", "NETPAYABLE", "SERVICE TYPE", "AIRLINE PNR", "GDS PNR", " TICKETED ON CORP OR RETAIL", "APPROVER NAME/CODE", "REMARKS");

            $labelAry = array("ptd_tmc_name", "ptd_tmc_gstin_no", "ptd_segment", "ptd_inv_date", "ptd_gst_inv_crn_no", "ptd_gst_inv_crn_ref_no", "ptd_doc_type", "ptd_billing_entity", "ptd_customer_gstin_no", "ptd_billable", "ptd_business_unit", "ptd_trip_code", "ptd_cost_centre", "ptd_budget_code", "ptd_po_no", "ptd_location", "ptd_department", "ptd_emp_id", "ptd_booking_rcvd_from", "ptd_pax_name", "ptd_pax_type", "ptd_airline_code", "ptd_airline_name", "ptd_tkt_no", "ptd_flight_no", "ptd_sector", "ptd_dep_start_date", "ptd_arr_end_date", "ptd_cabinclass", "ptd_fareclass", "ptd_basic_fare", "ptd_k3", "ptd_igst", "ptd_cgst", "ptd_sgst", "ptd_meal", "ptd_seat", "ptd_others", "ptd_tkt_fare", "ptd_service_charge", "ptd_discount", "ptd_tmc_can_charge", "ptd_penalty", "ptd_card_charge", "ptd_igst_service_charge", "pgd_cgst_service_charge", "ptd_sgst_service_charge", "ptd_netpayable", "ptd_servicetype", "ptd_airline_pnr", "ptd_gst_pnr", "ptd_corp_retail", "ptd_approver", "remarks");

            $inserror = 0;
            $this->load->library('excel');
            //read file from path
            $filepath = './uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'] . '/p_tmc/' . $filename;
            $objPHPExcel = PHPExcel_IOFactory::load($filepath);

            $worksheet = $objPHPExcel->getActiveSheet();
            $highestRow = (int)$worksheet->getHighestRow();
            $highestColumn = $worksheet->getHighestColumn();
            $seterror = 0;
            $ferror = array();
            $pcols = 54;
            $ttlrows = 0;
            // for ($row = 1; $row <= $highestRow; $row++)
            // {
            $encounteredTickets = array();
            // ................................this for dupe chk...........................///
            for ($row = 1; $row <= $highestRow; $row++) {
                $tktCell = $worksheet->getCellByColumnAndRow(23, $row)->getValue();
                $passengerName = $worksheet->getCellByColumnAndRow(19, $row)->getValue();

                if (!empty($tktCell) && !empty($passengerName)) {
                    $key = $tktCell . '|' . $passengerName; // Creating a unique key with ticket number and passenger name

                    if (in_array($key, $encounteredTickets)) {
                        $firstOccurrenceRow = array_search($key, $encounteredTickets);
                        $statement = $worksheet->getCellByColumnAndRow(4, $firstOccurrenceRow)->getValue();
                        $worksheet->setCellValueByColumnAndRow(53, $row, 'dupe found/' . $statement);
                    } else {
                        $encounteredTickets[$row] = $key;
                    }
                }

                $checkDuplicateQuery = $this->db->query('SELECT * FROM p_tmc_data WHERE ptd_tkt_no = "' . $tktCell . '" AND ptd_pax_name = "' . $passengerName . '"');

                if ($checkDuplicateQuery->num_rows() > 0) {
                    $duplicateRecords = $checkDuplicateQuery->result();
                    // print_r($duplicateRecords);
                    $statement = $duplicateRecords[0]->ptd_gst_inv_crn_no;
                    $worksheet->setCellValueByColumnAndRow(53, $row, 'dupe found/' . $statement);
                }


                if ($row == 1) {
                    for ($col = 0; $col < $pcols; $col++) {
                        $cell = $worksheet->getCellByColumnAndRow($col, $row);
                        $cellref = $cell;
                        $headingary[] = strval($cell);
                    }
                } else {
                    if ($fieldAry != $headingary) {
                        //                        print_r($fieldAry);
                        //                        echo '<br/>';
                        //                        print_r($headingary);
                        $rdata['status'] = 13;
                        $ferror['column'] = 'Column mismatch.';
                        $rdata['error'] = $ferror;
                        echo json_encode($rdata);
                        exit;
                    } else {
                        $ttlrows++;
                        $chkcell = $worksheet->getCellByColumnAndRow(1, $row);
                        if ($chkcell != '') {
                            $keys = '';
                            $values = '';
                            for ($col = 0; $col < $pcols; $col++) {

                                if ($col == 23 || $col == 37)
                                    $cell = $worksheet->getCellByColumnAndRow($col, $row)->getFormattedValue();
                                else
                                    $cell = $worksheet->getCellByColumnAndRow($col, $row);
                                $cell = strtoupper($cell);
                                $cc = strval($cell);
                                $cc = trim($cc);
                                if ($cell != '') {
                                    $keys .= $labelAry[$col] . ',';
                                    if ($col == 0) {
                                        $cc = preg_replace('/\s+/', ' ', trim($cc));
                                        if (!preg_match("@^[a-zA-Z0-9\s]+$@", $cc)) {
                                            $seterror = 1;
                                            //                                        $ferror[$row]['column'][] = 'GST INVOICE NO';
                                            $ferror[$row]['column'][] = 1;
                                            $ferror[$row]['row'] = $row;
                                        }
                                        $values .= '"' . $cc . '",';
                                    } else if ($col == 1 && $col == 4 && $col == 5 && $col == 11 && $col == 12 && $col == 13 && $col == 14 && $col == 16 && $col == 17 && $col == 48 && $col == 49) {
                                        $cc = preg_replace('/\s+/', ' ', trim($cc));
                                        if (!preg_match("@^[a-zA-Z0-9]+$@", $cc)) {
                                            $seterror = 1;
                                            //                                        $ferror[$row]['column'][] = 'GST INVOICE NO';
                                            $ferror[$row]['column'][] = 2;
                                            $ferror[$row]['row'] = $row;
                                        }
                                        if ($col == 4)
                                            $td_gst_inv = $cc;
                                        $values .= '"' . $cc . '",';
                                    } else if ($col == 7 && $col == 10 && $col == 15 && $col == 18 && $col == 50) {
                                        $cc = preg_replace('/\s+/', ' ', trim($cc));
                                        if (!preg_match("@^[a-zA-Z0-9\s]+$@", $cc)) {
                                            $seterror = 1;
                                            //                                        $ferror[$row]['column'][] = 'GST INVOICE NO';
                                            $ferror[$row]['column'][] = 3;
                                            $ferror[$row]['row'] = $row;
                                        }
                                        $values .= '"' . $cc . '",';
                                    } else if ($col == 23) {
                                        $cc = preg_replace('/\s+/', ' ', trim($cc));
                                        if (!preg_match("@^[a-zA-Z0-9/]+$@", $cc)) {
                                            //                                        echo $cc;
                                            $seterror = 1;
                                            //                                        $ferror[$row]['column'][] = 'GST INVOICE NO';
                                            $ferror[$row]['column'][] = 4;
                                            $ferror[$row]['row'] = $row;
                                        }
                                        $td_gst_inv = $cc;
                                        $values .= '"' . $cc . '",';
                                    } else if ($col == 24) {
                                        //                                    echo $cc;
                                        $cc = preg_replace('/\s+/', ' ', trim($cc));
                                        if (!preg_match("@^[a-zA-Z0-9\s/]+$@", $cc)) {
                                            $seterror = 1;
                                            //                                        $ferror[$row]['column'][] = 'GST INVOICE NO';
                                            $ferror[$row]['column'][] = 5;
                                            $ferror[$row]['row'] = $row;
                                        }
                                        $values .= '"' . $cc . '",';
                                    } else if ($col == 25) {
                                        $cc = preg_replace('/\s+/', ' ', trim($cc));
                                        if (!preg_match("@^[a-zA-Z0-9\-\s/]+$@", $cc)) {
                                            $seterror = 1;
                                            //                                        $ferror[$row]['column'][] = 'GST INVOICE NO';
                                            $ferror[$row]['column'][] = 6;
                                            $ferror[$row]['row'] = $row;
                                        }
                                        $values .= '"' . $cc . '",';
                                    } else if ($col == 26 && $col == 27) {
                                        $cc = preg_replace('/\s+/', ' ', trim($cc));
                                        if (!preg_match("@^[a-zA-Z0-9\-/]+$@", $cc)) {
                                            $seterror = 1;
                                            //                                        $ferror[$row]['column'][] = 'GST INVOICE NO';
                                            $ferror[$row]['column'][] = 7;
                                            $ferror[$row]['row'] = $row;
                                        }
                                        $values .= '"' . $cc . '",';
                                    } else if ($col == 29) {
                                        $cc = preg_replace('/\s+/', ' ', trim($cc));
                                        if (!preg_match("@^[a-zA-Z\s]+$@", $cc)) {
                                            //                                    if(!preg_match("@^[a-zA-Z\s\/]+$@",$cc)){
                                            $seterror = 1;
                                            //                                        $ferror[$row]['column'][] = 'GST INVOICE NO';
                                            $ferror[$row]['column'][] = 8;
                                            $ferror[$row]['row'] = $row;
                                        }
                                        $values .= '"' . $cc . '",';
                                    }
                                    //                                else if($col == 30){
                                    //                                    $cc = preg_replace('/\s+/', '',trim($cc));
                                    //                                    if(!preg_match("@^[0-9\-.]+$@",$cc)){
                                    ////                                    if(!preg_match("@^[a-zA-Z\s\/]+$@",$cc)){
                                    //                                        $seterror = 1;
                                    ////                                        $ferror[$row]['column'][] = 'GST INVOICE NO';
                                    //                                        $ferror[$row]['column'][] = 99;
                                    //                                        $ferror[$row]['row'] = $row;
                                    //                                    }
                                    //                                    $values .= '"'.$cc.'",';
                                    //                                }
                                    else if ($col > 29 && $col < 48) {
                                        $cc = preg_replace('/\s+/', ' ', trim($cc));
                                        if (!preg_match("@^[0-9\-.]+$@", $cc)) {
                                            $seterror = 1;
                                            //                                        $ferror[$row]['column'][] = 'GST INVOICE NO';
                                            $ferror[$row]['column'][$col][] = 10;
                                            $ferror[$row]['row'] = $row;
                                        }
                                        $values .= '"' . $cc . '",';
                                    } else if ($col == 3) {
                                        //                                    echo $cc;
                                        if (is_numeric($cc)) {
                                            $unix_date = ($cc - 25569) * 86400;
                                            $excel_date = 25569 + ($unix_date / 86400);
                                            $unix_date = ($excel_date - 25569) * 86400;
                                            $fdate = gmdate("Y-m-d", $unix_date);
                                            $values .= '"' . $fdate . '",';
                                        } else {
                                            $cc = str_replace('.', "-", $cc);
                                            $cc = str_replace('/', "-", $cc);
                                            $dary = explode('-', $cc);
                                            if ($dary[0] > 31 || $dary[1] > 12 || $dary[2] < 1900 || $dary[2] > 2021) {
                                                //                                            if(!isset($ferror['INV DATE']))
                                                //                                                $ferror['INV DATE'] = $row.'-INV DATE';
                                                //                                            $ferror[$row]['column'][] = 'INV DATE';
                                                $ferror[$row]['column'][] = 11;
                                                $ferror[$row]['row'] = $row;
                                                $values .= '"' . $cc . '",';
                                            } else {
                                                $ffdate = $dary[2] . '-' . $dary[1] . '-' . $dary[0];
                                                $cc = $ffdate;
                                                $values .= '"' . $cc . '",';
                                            }
                                        }
                                    } else if ($col == 2) {
                                        $cc = preg_replace('/\s+/', ' ', trim(strtoupper($cc)));
                                        if ($cc != 'DOMESTIC' && $cc != 'INTERNATIONAL' && $cc != 'VISA' && $cc != 'RAIL' && $cc != 'INSURANCE' && $cc != 'HOTEL DOMESTIC' && $cc != 'HOTEL INTERNATIONAL' && $cc != 'CAR DOMESTIC' && $cc != 'CAR INTERNATIONAL' && $cc != 'PASSPORT' && $cc != 'MEET & ASSIST' && $cc != 'BUS' && $cc != 'MISC') {
                                            $seterror = 1;
                                            $ferror[$row]['column'][] = 11;
                                            $ferror[$row]['row'] = $row;
                                        }
                                        //                                    else
                                        $values .= '"' . $cc . '",';
                                    } else if ($col == 6) {
                                        $cc = preg_replace('/\s+/', '', trim(strtoupper($cc)));
                                        if ($cc != 'INV' && $cc != 'CRN') {
                                            $seterror = 1;
                                            $ferror[$row]['column'][] = 12;
                                            $ferror[$row]['row'] = $row;
                                        }
                                        //                                    else
                                        $values .= '"' . $cc . '",';
                                    } else if ($col == 9) {
                                        $cc = preg_replace('/\s+/', '', trim(strtoupper($cc)));
                                        if ($cc != 'YES' && $cc != 'NO') {
                                            $seterror = 1;
                                            $ferror[$row]['column'][] = 13;
                                            $ferror[$row]['row'] = $row;
                                        }
                                        //                                    else
                                        $values .= '"' . $cc . '",';
                                    } else if ($col == 20) {
                                        $cc = preg_replace('/\s+/', ' ', trim(strtoupper($cc)));
                                        if ($cc != 'EMPLOYEE' && $cc != 'NEW JOINEE' && $cc != 'DEPENDENT' && $cc != 'GUEST') {
                                            $seterror = 1;
                                            $ferror[$row]['column'][] = 14;
                                            $ferror[$row]['row'] = $row;
                                        }
                                        //                                    else
                                        $values .= '"' . $cc . '",';
                                    } else if ($col == 28) {
                                        $cc = preg_replace('/\s+/', ' ', trim(strtoupper($cc)));
                                        if ($cc != 'ECONOMY' && $cc != 'BUSINESS' && $cc != 'PREMIUM ECONOMY' && $cc != '1ST CLASS BUSINESS' && $cc != 'WORLD TRAVELLER') {
                                            $seterror = 1;
                                            $ferror[$row]['column'][] = 15;
                                            $ferror[$row]['row'] = $row;
                                        }
                                        $values .= '"' . $cc . '",';
                                    } else if ($col == 47) {
                                        $cc = preg_replace('/\s+/', ' ', trim(strtoupper($cc)));
                                        //                                    echo $cc; exit;
                                        if ($cc != 'FRESH TKT' && $cc != 'RE-ISSUE TKT' && $cc != 'SUPPORT SERVICE') {
                                            $seterror = 1;
                                            $ferror[$row]['column'][] = 166;
                                            $ferror[$row]['row'] = $row;
                                        }
                                        $values .= '"' . $cc . '",';
                                    } else {
                                        $values .= '"' . $cell . '",';
                                    }
                                }
                            }

                            $chk = $this->db->query('select * from p_tmc_data where ptd_gst_inv_crn_no = "' . $td_gst_inv . '"');

                            if ($chk->num_rows() > 0) {
                                $inserror++;
                            }
                            // store the query///
                            $insArym[] = 'insert into p_tmc_data_dump (' . substr($keys, 0, -1) . ',ptd_ptfid) values (' . substr($values, 0, -1);
                            $insAry[] = 'insert ignore into p_tmc_data (' . substr($keys, 0, -1) . ',ptd_ptfid) values (' . substr($values, 0, -1);
                        }
                    }
                }
            }
            //            print_r($ferror); exit;

            $ferror = array_values($ferror);
            //            print_r($ferror);
            //            $rdata['status'] = 0;
            //            $rdata['error'] = $ferror;
            //            echo json_encode($rdata);
            //            exit;
            // insertion in p_filr//////
            $insdata['pf_corporateid'] = $this->session->userdata['recon_sess_i']['u_corporateid'];
            $insdata['pf_file'] = $filename;
            $insdata['pf_type'] = 'tmc';
            $insdata['pf_status'] = 'Initiated';
            $insdata['pf_uploadedby'] = $this->session->userdata['recon_sess_i']['u_id'];
            $insdata['pf_uploadedat'] = date('Y-m-d H:i:s', time());
            $this->db->insert('p_files', $insdata);
            $td_tfid = $this->db->insert_id();
            $rdata['insref'] = $td_tfid;
            // dummy   tmc insertion  directly using query(assigned variable)
            for ($c = 0; $c < count($insArym); $c++) {
                //                try{
                $this->db->query($insArym[$c] . ',"' . $td_tfid . '")');
                //   echo $insArym[$c].',"'.$td_tfid.'")';
                //                }
                //                catch(Exception $e){
                //                    continue;
                //                }
            }
            // after checkness 
            if (!empty($ferror)) {

                $rdata['duprecords'] = 0;
                $rdata['status'] = 0;
                //                $rdata['error'] = $ferror;

                if ($inserror != 0)
                    $rdata['duprecords'] = $inserror;
                $rdata['ttlrows'] = $ttlrows;
                //                $rdata['error'] = $ferror;
                $rdata['invalidrows'] = count($ferror);
                echo json_encode($rdata);
                exit;
            } else {

                $reccnt = $highestRow;
                $inscnt = 0;
                // data insert in main table ater iflktering the duplication
                for ($c = 0; $c < count($insAry); $c++) {
                    try {
                        $this->db->query($insAry[$c] . ',"' . $td_tfid . '")');
                        if ($this->db->affected_rows())
                            $inscnt++;
                    } catch (Exception $e) {
                        continue;
                    }
                }
                $datequery = $this->db->query('select min(ptd_inv_date) mindate, max(ptd_inv_date) maxdate from p_tmc_data where ptd_ptfid = ' . $td_tfid);
                $dateAry = array();
                foreach ($datequery->result() as $dateq) {
                    $dateCon['pf_id'] = $td_tfid;
                    $dateAry['pf_min_date'] = $dateq->mindate;
                    $dateAry['pf_max_date'] = $dateq->maxdate;
                }
                $this->db->where($dateCon);
                $this->db->update('p_files', $dateAry);

                $rdata['status'] = 1;
                $rdata['added_records'] = $inscnt;
                echo json_encode($rdata);
            }
        }
    }

    //     public function upload_tmc(){
    //         $this->checksess();

    //         $monthAllAryLabel = array('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec');

    //         $insdata = $this->input->post('usr');
    //         $ext = pathinfo($_FILES['tmc_file']['name'], PATHINFO_EXTENSION);
    //         $filename = 'TMC-'.str_replace(' ','_',$insdata['f_agent']).'-'.$monthAllAryLabel[$insdata['f_month']-1].'-'.$insdata['f_year'].'-'.time().'.'.$ext;

    //         $conchk['f_month'] = $insdata['f_month'];
    //         $conchk['f_year'] = $insdata['f_year'];
    //         $conchk['f_agent'] = $insdata['f_agent'];
    //         $conchk['f_corporateid'] = $this->session->userdata['recon_sess_i']['u_corporateid'];
    // //        $entryres = $this->db->get_where('files',$conchk);
    // //        if($entryres->num_rows()>0){
    // //            $rdata['status'] = 31; echo json_encode($rdata); exit;
    // //        }

    //         if(!is_dir(dirname( dirname( dirname(__FILE__) ) ).'/uploads/'.$this->session->userdata['recon_sess_i']['u_corporateid'])) {
    //             mkdir(dirname( dirname( dirname(__FILE__) ) ).'/uploads/'.$this->session->userdata['recon_sess_i']['u_corporateid']);
    //             mkdir(dirname( dirname( dirname(__FILE__) ) ).'/uploads/'.$this->session->userdata['recon_sess_i']['u_corporateid'].'/gstr2a');
    //             mkdir(dirname( dirname( dirname(__FILE__) ) ).'/uploads/'.$this->session->userdata['recon_sess_i']['u_corporateid'].'/tmc');
    //         }

    //         $config['file_name'] = $filename;
    //         $config['upload_path'] = './uploads/'.$this->session->userdata['recon_sess_i']['u_corporateid'].'/tmc/';
    //         $config['allowed_types'] = '*';
    //         $config['max_size'] = 3000;
    //         $this->load->library('upload', $config);

    //         if(!$this->upload->do_upload('tmc_file'))
    //         {
    //             $error = array('error' => $this->upload->display_errors());
    //             echo $error['error'];
    //         }
    //         else
    //         {
    //             $data = array('upload_data' => $this->upload->data());

    //             $fieldAry = array("SEGMENT","INV DATE ","GST INVOICE NO","DOC TYPE","BILLING ENTITY","EMP ID","PASSENGER NAME","CARRIER NAME","TICKET NO","FLIGHTNO","SECTOR","DEPDATE &TIME","CABINCLASS","TOTAL TKT FARE","NETPAYABLE","AIR TAX DESCRIPTION","AIRLINE PNR","GDS PNR");

    //             $labelAry = array('td_segment','td_inv_date','td_gst_inv','td_doc_type','td_billing_entity','td_empid','td_pax','td_carrier','td_tkt','td_flight','td_sector','td_departure','td_cabin_class','td_tkt_fare','td_net','td_air_tax','td_airline_pnr','td_pnr');

    //             $inserror = 0;
    //             $this->load->library('excel');
    //             //read file from path
    //             $filepath = './uploads/'.$this->session->userdata['recon_sess_i']['u_corporateid'].'/tmc/'.$filename;
    //             $objPHPExcel = PHPExcel_IOFactory::load($filepath);

    //             $worksheet = $objPHPExcel->getActiveSheet();
    //             $highestRow = (int)$worksheet->getHighestRow();
    //             $highestColumn = $worksheet->getHighestColumn();
    //             $seterror = 0;$ferror=array();
    //             $pcols = 18;$ttlrows=0;
    //             for ($row = 1; $row <= $highestRow; $row++)
    //             {
    //                 if($row == 1){
    //                     print_r("1");
    //                     for($col = 0; $col < $pcols; $col++){
    //                         $cell = $worksheet->getCellByColumnAndRow($col, $row);
    //                         $cellref = $cell;
    //                         $headingary[] = strval($cell);
    //                     }
    //                 }
    //                 else{
    //                     if($fieldAry != $headingary){
    // //                        print_r($fieldAry);
    // //                        echo '<br/>';
    // //                        print_r($headingary);
    //                         $rdata['status'] = 13;
    //                         $ferror['column'] = 'Column mismatch.';
    //                         $rdata['error'] = $ferror;
    //                         echo json_encode($rdata); exit;
    //                     }
    //                     else{
    //                         $ttlrows++;
    //                         $chkcell = $worksheet->getCellByColumnAndRow(1, $row);
    //                         if($chkcell != ''){
    //                             $keys='';$values='';
    //                             for($col = 0; $col < $pcols; $col++)
    //                             {
    //                                 $cell = $worksheet->getCellByColumnAndRow($col, $row);
    //                                 $cell = strtoupper($cell);
    //                                 $cc = strval($cell);
    //                                 $cc = trim($cc);
    //                                 if($cell != ''){
    //                                 $keys .= $labelAry[$col].',';
    //                                 if($col == 0){
    //                                     $cc = preg_replace('/\s+/', '',trim($cc));
    //                                     if($cc != 'DOMESTIC' && $cc != 'INTERNATIONAL' && $cc != 'HOTEL' && $cc != 'MISC'){
    //                                         $seterror = 1;
    // //                                        $ferror[$row]['column'][] = 'GST INVOICE NO';
    //                                         $ferror[$row]['column'][] = 0;
    //                                         $ferror[$row]['row'] = $row;
    //                                     }
    //                                     $values .= '"'.$cc.'",';
    //                                 }
    //                                 else if($col == 1){
    //                                     if(is_numeric($cc)){
    //                                         $unix_date = ($cc - 25569) * 86400;
    //                                         $excel_date = 25569 + ($unix_date / 86400);
    //                                         $unix_date = ($excel_date - 25569) * 86400;
    //                                         $fdate = gmdate("Y-m-d", $unix_date);
    //                                         $values .= '"'.$fdate.'",';
    //                                     }
    //                                     else{
    //                                         $cc = str_replace('.',"-",$cc);
    //                                         $cc = str_replace('/',"-",$cc);
    //                                         $dary = explode('-',$cc);
    //                                         if($dary[0]>31 || $dary[1]>12 || $dary[2]<1900 || $dary[2]>2023){
    // //                                            if(!isset($ferror['INV DATE']))
    // //                                                $ferror['INV DATE'] = $row.'-INV DATE';
    // //                                            $ferror[$row]['column'][] = 'INV DATE';
    //                                             $ferror[$row]['column'][] = 1;
    //                                             $ferror[$row]['row'] = $row;
    //                                             $values .= '"'.$cc.'",';
    //                                         }
    //                                         else{
    //                                             $ffdate = $dary[2].'-'.$dary[1].'-'.$dary[0];
    //                                             $cc = $ffdate;
    //                                             $values .= '"'.$cc.'",';
    //                                         }
    //                                     }
    //                                 }
    //                                 else if($col == 2){
    //                                     $cc = preg_replace('/\s+/', '',trim($cc));
    //                                     if(!preg_match("@^[a-zA-Z0-9\-\s/.]+$@",$cc)){
    //                                         $seterror = 1;
    //                                         $ferror[$row]['column'][] = 'GST INVOICE NO';
    //                                         $ferror[$row]['row'] = $row;
    //                                     }
    // //                                    else
    //                                     $td_gst_inv = $cc;
    //                                     $values .= '"'.$cc.'",';
    //                                 }
    //                                 else if($col == 3){
    //                                     $cc = preg_replace('/\s+/', '',trim($cc));
    //                                     if($cc != "CRN" && $cc != "INV"){
    //                                         $seterror = 1;
    //                                         $ferror[$row]['column'][] = 'DOC TYPE';
    //                                         $ferror[$row]['row'] = $row;
    //                                     }
    //                                     $values .= '"'.$cc.'",';
    //                                 }
    //                                 else if($col == 6 || $col == 7){
    //                                     if(!preg_match("@^[a-zA-Z\s]+$@",$cc)){
    //                                         $seterror = 1;
    //                                         $ferror[$row]['column'][] = '6-7';
    //                                         $ferror[$row]['row'] = $row;
    //                                     }
    //                                     $values .= '"'.$cc.'",';
    //                                 }
    //                                 else if($col == 12){
    //                                     if(!preg_match("@^[a-zA-Z]+$@",$cc)){
    //                                         $seterror = 1;
    //                                         $ferror[$row]['column'][] = '12';
    //                                         $ferror[$row]['row'] = $row;
    //                                     }
    //                                     $values .= '"'.$cc.'",';
    //                                 }
    //                                 else if($col == 16 || $col == 17 || $col == 8){
    //                                     if(!preg_match("@^[a-zA-Z0-9]+$@",$cc)){
    //                                         $seterror = 1;
    //                                         $ferror[$row]['column'][] = '16-17';
    //                                         $ferror[$row]['row'] = $row;
    //                                     }
    //                                     $values .= '"'.$cc.'",';
    //                                 }
    //                                 else if($col == 13 || $col == 14){
    //                                     if(!preg_match("@^[0-9\-]+$@",$cc)){
    //                                         $seterror = 1;
    //                                         $ferror[$row]['column'][] = '13-14';
    //                                         $ferror[$row]['row'] = $row;
    //                                     }
    //                                     $values .= '"'.$cc.'",';
    //                                 }
    //                                 else if($col == 11){
    //                                     if(!preg_match("@^[0-9\-/:\s]+$@",$cc)){
    //                                         $seterror = 1;
    //                                         $ferror[$row]['column'][] = '11';
    //                                         $ferror[$row]['row'] = $row;
    //                                     }
    //                                     $values .= '"'.$cc.'",';
    //                                 }
    //                                 else{
    //                                     $values .= '"'.$cell.'",';
    //                                 }
    //                             }
    //                             }

    //                             $chk = $this->db->query('select * from tmc_data where td_gst_inv = "'.$td_gst_inv.'"');

    //                             if($chk->num_rows()>0){
    //                                 $inserror++;
    //                             }
    // //                            print_r($ferror);
    //                             $insArym[] = 'insert into tmc_data_dump ('.substr($keys,0,-1).',td_tfid) values ('.substr($values,0,-1); 
    //                             $insAry[] = 'insert ignore into tmc_data ('.substr($keys,0,-1).',td_tfid) values ('.substr($values,0,-1);
    //                         }
    //                     }
    //                 }
    //             }

    //             $ferror = array_values($ferror);
    // //            print_r($ferror);
    // //            $rdata['status'] = 0;
    // //            $rdata['error'] = $ferror;
    // //            echo json_encode($rdata);
    // //            exit;

    //             $insdata['f_corporateid'] = $this->session->userdata['recon_sess_i']['u_corporateid'];
    //             $insdata['f_file'] = $filename;
    //             $insdata['f_type'] = 'tmc';
    //             $insdata['f_status'] = 'Initiated';
    //             $insdata['f_uploadedby'] = $this->session->userdata['recon_sess_i']['u_id'];
    //             $insdata['f_uploadedat'] = date('Y-m-d H:i:s',time());
    //             $this->db->insert('files',$insdata);
    //             $td_tfid = $this->db->insert_id();
    //             $rdata['insref'] = $td_tfid;

    //             for($c=0;$c<count($insArym);$c++){
    // //                try{
    //                     $this->db->query($insArym[$c].',"'.$td_tfid.'")');
    // //                }
    // //                catch(Exception $e){
    // //                    continue;
    // //                }
    //             }

    //             if(!empty($ferror)){

    //                 $rdata['duprecords'] = 0;
    //                 $rdata['status'] = 0;
    // //                $rdata['error'] = $ferror;

    //                 if($inserror != 0)
    //                     $rdata['duprecords'] = $inserror;
    //                 $rdata['ttlrows'] = $ttlrows;
    // //                $rdata['error'] = $ferror;
    //                 $rdata['invalidrows'] = count($ferror);
    //                 echo json_encode($rdata); exit;
    //             }
    //             else{

    //                 $reccnt = $highestRow;
    //                 $inscnt = 0;

    //                 for($c=0;$c<count($insAry);$c++){
    //                     try{
    //                         $this->db->query($insAry[$c].',"'.$td_tfid.'")');
    //                         if($this->db->affected_rows())
    //                             $inscnt++;
    //                     }
    //                     catch(Exception $e){
    //                         continue;
    //                     }
    //                 }
    //                 $datequery = $this->db->query('select min(td_inv_date) mindate, max(td_inv_date) maxdate from tmc_data where td_tfid = '.$td_tfid);
    //                 $dateAry = array();
    //                 foreach($datequery->result() as $dateq){
    //                     $dateCon['f_id'] = $td_tfid;
    //                     $dateAry['f_min_date'] = $dateq->mindate;
    //                     $dateAry['f_max_date'] = $dateq->maxdate;
    //                 }
    //                 $this->db->where($dateCon);
    //                 $this->db->update('files',$dateAry);

    //                 $rdata['status'] = 1;
    //                 $rdata['added_records'] = $inscnt;
    //                 echo json_encode($rdata);
    //             }
    //         }
    //     }


    public function upload_tmc()
    {
        $this->checksess();

        $monthAllAryLabel = array('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec');

        $insdata = $this->input->post('usr');
        $ext = pathinfo($_FILES['tmc_file']['name'], PATHINFO_EXTENSION);
        $filename = 'TMC-' . str_replace(' ', '_', $insdata['f_agent']) . '-' . $monthAllAryLabel[$insdata['f_month'] - 1] . '-' . $insdata['f_year'] . '-' . time() . '.' . $ext;

        $conchk['f_month'] = $insdata['f_month'];
        $conchk['f_year'] = $insdata['f_year'];
        $conchk['f_agent'] = $insdata['f_agent'];
        $conchk['f_corporateid'] = $this->session->userdata['recon_sess_i']['u_corporateid'];
        //        $entryres = $this->db->get_where('files',$conchk);
        //        if($entryres->num_rows()>0){
        //            $rdata['status'] = 31; echo json_encode($rdata); exit;
        //        }

        if (!is_dir(dirname(dirname(dirname(__FILE__))) . '/uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'])) {
            mkdir(dirname(dirname(dirname(__FILE__))) . '/uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid']);
            mkdir(dirname(dirname(dirname(__FILE__))) . '/uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'] . '/gstr2a');
            mkdir(dirname(dirname(dirname(__FILE__))) . '/uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'] . '/tmc');
        }

        $config['file_name'] = $filename;
        $config['upload_path'] = './uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'] . '/tmc/';
        $config['allowed_types'] = '*';
        $config['max_size'] = 3000;
        $this->load->library('upload', $config);

        if (!$this->upload->do_upload('tmc_file')) {
            $error = array('error' => $this->upload->display_errors());
            echo $error['error'];
        } else {
            $data = array('upload_data' => $this->upload->data());

            $fieldAry = array("SEGMENT", "INV DATE ", "GST INVOICE NO", "DOC TYPE", "BILLING ENTITY", "EMP ID", "PASSENGER NAME", "CARRIER NAME", "TICKET NO", "FLIGHTNO", "SECTOR", "DEPDATE &TIME", "CABINCLASS", "TOTAL TKT FARE", "NETPAYABLE", "AIR TAX DESCRIPTION", "AIRLINE PNR", "GDS PNR");

            $labelAry = array('td_segment', 'td_inv_date', 'td_gst_inv', 'td_doc_type', 'td_billing_entity', 'td_empid', 'td_pax', 'td_carrier', 'td_tkt', 'td_flight', 'td_sector', 'td_departure', 'td_cabin_class', 'td_tkt_fare', 'td_net', 'td_air_tax', 'td_airline_pnr', 'td_pnr');

            $inserror = 0;
            $this->load->library('excel');
            //read file from path
            $filepath = './uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'] . '/tmc/' . $filename;
            $objPHPExcel = PHPExcel_IOFactory::load($filepath);

            $worksheet = $objPHPExcel->getActiveSheet();
            $highestRow = (int)$worksheet->getHighestRow();
            $highestColumn = $worksheet->getHighestColumn();
            $seterror = 0;
            $ferror = array();
            $pcols = 18;
            $ttlrows = 0;
            for ($row = 1; $row <= $highestRow; $row++) {
                if ($row == 1) {
                    for ($col = 0; $col < $pcols; $col++) {
                        $cell = $worksheet->getCellByColumnAndRow($col, $row);
                        $cellref = $cell;
                        $headingary[] = strval($cell);
                    }
                } else {
                    if ($fieldAry != $headingary) {
                        //                        print_r($fieldAry);
                        //                        echo '<br/>';
                        //                        print_r($headingary);
                        $rdata['status'] = 13;
                        $ferror['column'] = 'Column mismatch.';
                        $rdata['error'] = $ferror;
                        echo json_encode($rdata);
                        exit;
                    } else {
                        $ttlrows++;
                        $chkcell = $worksheet->getCellByColumnAndRow(1, $row);
                        if ($chkcell != '') {
                            $keys = '';
                            $values = '';
                            for ($col = 0; $col < $pcols; $col++) {
                                $cell = $worksheet->getCellByColumnAndRow($col, $row);
                                $cell = strtoupper($cell);
                                $cc = strval($cell);
                                $cc = trim($cc);
                                if ($cell != '') {
                                    $keys .= $labelAry[$col] . ',';
                                    if ($col == 0) {
                                        $cc = preg_replace('/\s+/', '', trim($cc));
                                        if ($cc != 'DOMESTIC' && $cc != 'INTERNATIONAL' && $cc != 'HOTEL' && $cc != 'MISC') {
                                            $seterror = 1;
                                            //                                        $ferror[$row]['column'][] = 'GST INVOICE NO';
                                            $ferror[$row]['column'][] = 0;
                                            $ferror[$row]['row'] = $row;
                                        }
                                        $values .= '"' . $cc . '",';
                                    } else if ($col == 1) {
                                        if (is_numeric($cc)) {
                                            $unix_date = ($cc - 25569) * 86400;
                                            $excel_date = 25569 + ($unix_date / 86400);
                                            $unix_date = ($excel_date - 25569) * 86400;
                                            $fdate = gmdate("Y-m-d", $unix_date);
                                            $values .= '"' . $fdate . '",';
                                        } else {
                                            $cc = str_replace('.', "-", $cc);
                                            $cc = str_replace('/', "-", $cc);
                                            $dary = explode('-', $cc);
                                            if ($dary[0] > 31 || $dary[1] > 12 || $dary[2] < 1900 || $dary[2] > 2021) {
                                                //                                            if(!isset($ferror['INV DATE']))
                                                //                                                $ferror['INV DATE'] = $row.'-INV DATE';
                                                //                                            $ferror[$row]['column'][] = 'INV DATE';
                                                $ferror[$row]['column'][] = 1;
                                                $ferror[$row]['row'] = $row;
                                                $values .= '"' . $cc . '",';
                                            } else {
                                                $ffdate = $dary[2] . '-' . $dary[1] . '-' . $dary[0];
                                                $cc = $ffdate;
                                                $values .= '"' . $cc . '",';
                                            }
                                        }
                                    } else if ($col == 2) {
                                        $cc = preg_replace('/\s+/', '', trim($cc));
                                        if (!preg_match("@^[a-zA-Z0-9\-\s/.]+$@", $cc)) {
                                            $seterror = 1;
                                            $ferror[$row]['column'][] = 'GST INVOICE NO';
                                            $ferror[$row]['row'] = $row;
                                        }
                                        //                                    else
                                        $td_gst_inv = $cc;
                                        $values .= '"' . $cc . '",';
                                    }
                                    //change
                                    else if ($col == 3) {
                                        // print_r($ccinvalidrowsstat);
                                        if ($cc != "CRN" && $cc != "INV" && $cc != "REISSUE") {
                                            $seterror = 1;
                                            $ferror[$row]['column'][] = 'DOC TYPE';
                                            $ferror[$row]['row'] = $row;
                                        }
                                        $values .= '"' . $cc . '",';
                                    } else if ($col == 6) {
                                        if (!preg_match("@^[a-zA-Z\s]+$@", $cc)) {
                                            $seterror = 1;
                                            $ferror[$row]['column'][] = '6-7';
                                            $ferror[$row]['row'] = $row;
                                        }
                                        $values .= '"' . $cc . '",';
                                    }
                                    //CHANGE
                                    else if ($col == 7) {
                                        if (!preg_match("@^[a-zA-Z\s0-9]+$@", $cc)) {
                                            $seterror = 1;
                                            $ferror[$row]['column'][] = '6-7';
                                            $ferror[$row]['row'] = $row;
                                        }
                                        $values .= '"' . $cc . '",';
                                    } else if ($col == 12) {
                                        if (!preg_match("@^[a-zA-Z]+$@", $cc)) {
                                            $seterror = 1;
                                            $ferror[$row]['column'][] = '12';
                                            $ferror[$row]['row'] = $row;
                                        }
                                        $values .= '"' . $cc . '",';
                                    } else if ($col == 16 || $col == 17) {
                                        if (!preg_match("@^[a-zA-Z0-9]+$@", $cc)) {
                                            $seterror = 1;
                                            $ferror[$row]['column'][] = '16-17';
                                            $ferror[$row]['row'] = $row;
                                        }
                                        $values .= '"' . $cc . '",';
                                    } else if ($col == 8) {
                                        if (!preg_match("/[a-zA-Z0-9 ]/", $cc)) {
                                            $seterror = 1;
                                            $ferror[$row]['column'][] = '16-17';
                                            $ferror[$row]['row'] = $row;
                                        }
                                        $values .= '"' . $cc . '",';
                                    } else if ($col == 13 /* || $col == 14 */) {
                                        if (!preg_match("@^[0-9\-]+$@", $cc)) {
                                            $seterror = 1;
                                            $ferror[$row]['column'][] = '13-14';
                                            $ferror[$row]['row'] = $row;
                                        }
                                        $values .= '"' . $cc . '",';
                                    }
                                    //change
                                    else if ($col == 14) {
                                        if (!preg_match("@^[0-9\.-]+$@", $cc)) {
                                            $seterror = 1;
                                            $ferror[$row]['column'][] = '13-14';
                                            $ferror[$row]['row'] = $row;
                                        }
                                        $values .= '"' . $cc . '",';
                                    } else if ($col == 11) {
                                        if (!preg_match("@^[0-9\-/:\s]+$@", $cc)) {
                                            $seterror = 1;
                                            $ferror[$row]['column'][] = '11';
                                            $ferror[$row]['row'] = $row;
                                        }
                                        $values .= '"' . $cc . '",';
                                    } else {
                                        $values .= '"' . $cell . '",';
                                    }
                                }
                            }

                            $chk = $this->db->query('select * from tmc_data where td_gst_inv = "' . $td_gst_inv . '"');
                            // print_r($chk);die;

                            if ($chk->num_rows() > 0) {
                                $inserror++;
                            }
                            //                            print_r($ferror);
                            $insArym[] = 'insert into tmc_data_dump (' . substr($keys, 0, -1) . ',td_tfid) values (' . substr($values, 0, -1);
                            $insAry[] = 'insert ignore into tmc_data (' . substr($keys, 0, -1) . ',td_tfid) values (' . substr($values, 0, -1);
                        }
                    }
                }
            }
            $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');

            $objWriter->save('./uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'] . '/tmc/' . $filename);

            if ($ttlrows == $inserror) {


                $rdata['status'] = 31;
                echo json_encode($rdata);
                exit; // Exit the function
            }


            $ferror = array_values($ferror);
            $this->log->write_log($ferror);
            //            print_r($ferror);
            //            $rdata['status'] = 0;
            //            $rdata['error'] = $ferror;
            echo json_encode($rdata);
            exit;

            $insdata['f_corporateid'] = $this->session->userdata['recon_sess_i']['u_corporateid'];
            $insdata['f_file'] = $filename;
            $insdata['f_type'] = 'tmc';
            $insdata['f_status'] = 'Initiated';
            $insdata['f_uploadedby'] = $this->session->userdata['recon_sess_i']['u_id'];
            $insdata['f_uploadedat'] = date('Y-m-d H:i:s', time());
            if (!$this->input->post('condition_check')) {
                $this->db->insert('files', $insdata);
                $td_tfid = $this->db->insert_id();
                $rdata['insref'] = $td_tfid;
            } else {
                // $this->db->insert('files',$insdata);
                // $td_tfid = $this->db->insert_id();
                $td_tfid = $this->input->post('td_tfid');
                $rdata['insref'] = $this->input->post('td_tfid');
            }

            for ($c = 0; $c < count($insArym); $c++) {
                //                try{
                $this->db->query($insArym[$c] . ',"' . $td_tfid . '")');
                //                }
                //                catch(Exception $e){
                //                    continue;
                //                }
            }

            if (!empty($ferror)) {

                $rdata['duprecords'] = 0;
                $rdata['status'] = 0;
                //                $rdata['error'] = $ferror;

                if ($inserror != 0)
                    $rdata['duprecords'] = $inserror;
                $rdata['ttlrows'] = $ttlrows;
                //                $rdata['error'] = $ferror;
                $rdata['invalidrows'] = count($ferror);
                // if (!$this->input->post('condition_check')) {
                echo json_encode($rdata);
                exit;
                // }
                // else{
                //     $message = "Invalid Records";
                //     echo "<script type='text/javascript'>alert('$message');</script>";
                //     echo '<script type="text/javascript">
                //     window.open("", "_self").close();
                //     </script>';
                // }
            } else {

                $reccnt = $highestRow;
                $inscnt = 0;

                for ($c = 0; $c < count($insAry); $c++) {
                    try {
                        // print_r($td_tfid);
                        // print_r($insAry[$c].',"'.$td_tfid.'")');die();
                        $this->db->query($insAry[$c] . ',"' . $td_tfid . '")');
                        if ($this->db->affected_rows())
                            $inscnt++;
                    } catch (Exception $e) {
                        continue;
                    }
                }
                $datequery = $this->db->query('select min(td_inv_date) mindate, max(td_inv_date) maxdate from tmc_data where td_tfid = ' . $td_tfid);
                $dateAry = array();
                foreach ($datequery->result() as $dateq) {
                    $dateCon['f_id'] = $td_tfid;
                    $dateAry['f_min_date'] = $dateq->mindate;
                    $dateAry['f_max_date'] = $dateq->maxdate;
                }
                $this->db->where($dateCon);
                $this->db->update('files', $dateAry);

                $rdata['status'] = 1;
                $rdata['added_records'] = $inscnt;
                // if (!$this->input->post('condition_check')) {
                echo json_encode($rdata);
                // }
                // else{
                //     $message = "Data Uploaded";
                //     echo "<script type='text/javascript'>alert('$message');</script>";
                //     echo '<script type="text/javascript">
                //     window.open("", "_self").close();
                //     </script>';
                // }
            }
        }
    }

    public function proceedinsert_cts()
    {
        $filerefid = $this->input->post('fileref');
        $filename = $this->db->query('select f_file from files where f_id = ' . $filerefid)->row('f_file');

        $this->load->library('excel');
        //read file from path
        $filepath = './uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'] . '/gstr2a/' . $filename;
        $objPHPExcel = PHPExcel_IOFactory::load($filepath);

        $worksheet = $objPHPExcel->getActiveSheet();
        $highestRow = (int)$worksheet->getHighestRow();
        $highestColumn = $worksheet->getHighestColumn();
        $seterror = 0;
        $ferror = array();
        $pcols = 17;

        $labelAry = array('gd_supplier_type', 'gd_supplier_name', 'gd_supplier_gstin', 'gd_inv_no', 'gd_doc_type', 'gd_inv_type', 'gd_inv_date', 'gd_inv_value', 'gd_supply_place', 'gd_supply_reverse_charge', 'gd_rate_perc', 'gd_taxable_value', 'gd_integrated_tax', 'gd_central_tax', 'gd_state_tax', 'gd_cess', 'gd_return_status');

        for ($row = 1; $row <= $highestRow; $row++) {
            if ($row > 1) {

                $chkcell = $worksheet->getCellByColumnAndRow(1, $row);
                if ($chkcell != '') {
                    $keys = '';
                    $values = '';
                    for ($col = 0; $col < $pcols; $col++) {
                        $cell = $worksheet->getCellByColumnAndRow($col, $row);
                        $cell = strtoupper(trim($cell));
                        $cc = strval($cell);
                        if ($cell != '') {
                            $keys .= $labelAry[$col] . ',';
                            if ($col == 0) {
                                //                                    if(!preg_match("@^[a-zA-Z0-9\s ]+$@",$cc)){
                                //                                        $seterror = 1;
                                ////                                        if(!isset($ferror['Trade/Legal name of the Supplier']))
                                ////                                        $ferror['Trade/Legal name of the Supplier'] = $row.'-Trade/Legal name of the Supplier';
                                //                                        $ferror[$row]['column'][] = 'Trade/Legal name of the Supplier';
                                //                                        $ferror[$row]['row'] = $row;
                                //                                    }
                                $values .= '"' . $cc . '",';
                            } else if ($col == 2) {
                                //                                    if(!preg_match("@^[a-zA-Z0-9\s ]+$@",$cc)){
                                //                                        $seterror = 1;
                                ////                                        if(!isset($ferror['GSTIN of supplier']))
                                ////                                        $ferror['GSTIN of supplier'] = $row.'-GSTIN of supplier';
                                //                                        
                                //                                        $ferror[$row]['column'][] = 'GSTIN of supplier';
                                //                                        $ferror[$row]['row'] = $row;    
                                //                                    }
                                $cc = preg_replace('/\s+/', '', trim($cc));
                                $values .= '"' . $cc . '",';
                            } else if ($col == 3) {
                                $cc = preg_replace('/\s+/', '', trim($cc));
                                if (!preg_match("@^[a-zA-Z0-9\-\s/.]+$@", $cc)) {
                                    //                                    if(!preg_match("@^[a-zA-Z0-9\s \/ -.]+$@",$cc)){
                                    $seterror = 1;
                                    //                                        if(!isset($ferror['Invoice number']))
                                    //                                        $ferror['Invoice number'] = $row.'-Invoice number';

                                    $ferror[$row]['column'][] = 'Invoice number';
                                    $ferror[$row]['row'] = $row;
                                } else
                                    $gd_inv_no = $cc;
                                $values .= '"' . $cc . '",';
                            } else if ($col == 4) {
                                $cc = trim($cc);
                                if ($cc != 'INV' && $cc != 'CRN' && $cc != 'DEBIT NOTE') {
                                    $seterror = 1;
                                    //                                        if(!isset($ferror['Invoice number']))
                                    //                                        $ferror['Invoice number'] = $row.'-Invoice number';

                                    $ferror[$row]['column'][] = 'DOCUMENT TYPE';
                                    $ferror[$row]['row'] = $row;
                                } else
                                    $gd_doc_type = $cc;
                                $values .= '"' . $cc . '",';
                            } else if ($col == 6) {
                                if ($cc != '') {
                                    if (is_numeric($cc)) {
                                        $unix_date = ($cc - 25569) * 86400;
                                        $excel_date = 25569 + ($unix_date / 86400);
                                        $unix_date = ($excel_date - 25569) * 86400;
                                        $fdate = gmdate("Y-m-d", $unix_date);
                                        $values .= '"' . $fdate . '",';
                                    } else {
                                        $cc = str_replace('.', "-", $cc);
                                        $cc = str_replace('/', "-", $cc);
                                        $dary = explode('-', $cc);
                                        if ($dary[0] > 31 || $dary[1] > 12 || $dary[2] < 1900 || $dary[2] > 2021) {
                                            //                                                if(!isset($ferror['Invoice Date']))
                                            //                                                    $ferror['Invoice Date'] = $row.'-Invoice Date';
                                            $ferror[$row]['column'][] = 'Invoice Date';
                                            $ferror[$row]['row'] = $row;
                                        } else {
                                            $ffdate = $dary[2] . '-' . $dary[1] . '-' . $dary[0];
                                            $cc = $ffdate;
                                            $values .= '"' . $cc . '",';
                                        }
                                    }
                                } else {
                                    //                                        if(!isset($ferror['Invoice Date']))
                                    //                                            $ferror['Invoice Date'] = $row.'-Invoice Date';
                                    $ferror[$row]['column'][] = 'Invoice Date';
                                    $ferror[$row]['row'] = $row;
                                }
                            } else if ($col == 7 || $col == 10 || $col == 11 || $col == 12 || $col == 13 || $col == 14 || $col == 15) {
                                if (!preg_match("@^[0-9]+$@", $cc)) {
                                    $seterror = 1;
                                    $ferror[$row]['row'] = $row;
                                }
                                $values .= '"' . $cc . '",';
                            } else if ($col == 8 || $col == 9) {
                                if (!preg_match("@^[a-zA-Z]+$@", $cc)) {
                                    $seterror = 1;
                                    $ferror[$row]['row'] = $row;
                                }
                                $values .= '"' . $cc . '",';
                            } else {
                                $values .= '"' . $cell . '",';
                            }
                        }
                    }
                    if (!isset($ferror[$row]))
                        $insAry[] = 'insert ignore into gstr2a_data (' . substr($keys, 0, -1) . ',gd_gid) values (' . substr($values, 0, -1);
                }
            }
        }

        $ferror = array_values($ferror);

        //            if(!empty($ferror)){
        //                $rdata['status'] = 0;
        //                $rdata['error'] = $ferror;
        //                echo json_encode($rdata); exit;
        //            }
        //            else{

        for ($c = 0; $c < count($insAry); $c++) {
            //                echo $insAry[$c].'<br/>';
            $this->db->query($insAry[$c] . ',"' . $filerefid . '")');
        }

        $datequery = $this->db->query('select min(gd_inv_date) mindate, max(gd_inv_date) maxdate from gstr2a_data where gd_gid = ' . $filerefid);
        $dateAry = array();
        foreach ($datequery->result() as $dateq) {
            $dateCon['f_id'] = $filerefid;
            $dateAry['f_min_date'] = $dateq->mindate;
            $dateAry['f_max_date'] = $dateq->maxdate;
        }
        $this->db->where($dateCon);
        $this->db->update('files', $dateAry);

        $rdata['status'] = 1;
        //                $rdata['added_records'] = $inscnt;
        echo json_encode($rdata);
        //            }
    }

    public function proceedinsert_gstr2a()
    {
        $filerefid = $this->input->post('fileref');
        $filename = $this->db->query('select f_file from files where f_id = ' . $filerefid)->row('f_file');

        $this->load->library('excel');
        //read file from path
        $filepath = './uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'] . '/gstr2a/' . $filename;
        $objPHPExcel = PHPExcel_IOFactory::load($filepath);

        $worksheet = $objPHPExcel->getActiveSheet();
        $highestRow = (int)$worksheet->getHighestRow();
        $highestColumn = $worksheet->getHighestColumn();
        $seterror = 0;
        $ferror = array();
        $pcols = 17;

        $labelAry = array('gd_supplier_type', 'gd_supplier_name', 'gd_supplier_gstin', 'gd_inv_no', 'gd_doc_type', 'gd_inv_type', 'gd_inv_date', 'gd_inv_value', 'gd_supply_place', 'gd_supply_reverse_charge', 'gd_rate_perc', 'gd_taxable_value', 'gd_integrated_tax', 'gd_central_tax', 'gd_state_tax', 'gd_cess', 'gd_return_status');

        for ($row = 1; $row <= $highestRow; $row++) {
            if ($row > 1) {

                $chkcell = $worksheet->getCellByColumnAndRow(1, $row);
                if ($chkcell != '') {
                    $keys = '';
                    $values = '';
                    for ($col = 0; $col < $pcols; $col++) {
                        $cell = $worksheet->getCellByColumnAndRow($col, $row);
                        $cell = strtoupper(trim($cell));
                        $cc = strval($cell);
                        if ($cell != '') {
                            $keys .= $labelAry[$col] . ',';
                            if ($col == 0) {
                                //                                    if(!preg_match("@^[a-zA-Z0-9\s ]+$@",$cc)){
                                //                                        $seterror = 1;
                                ////                                        if(!isset($ferror['Trade/Legal name of the Supplier']))
                                ////                                        $ferror['Trade/Legal name of the Supplier'] = $row.'-Trade/Legal name of the Supplier';
                                //                                        $ferror[$row]['column'][] = 'Trade/Legal name of the Supplier';
                                //                                        $ferror[$row]['row'] = $row;
                                //                                    }
                                $values .= '"' . $cc . '",';
                            } else if ($col == 2) {
                                //                                    if(!preg_match("@^[a-zA-Z0-9\s ]+$@",$cc)){
                                //                                        $seterror = 1;
                                ////                                        if(!isset($ferror['GSTIN of supplier']))
                                ////                                        $ferror['GSTIN of supplier'] = $row.'-GSTIN of supplier';
                                //                                        
                                //                                        $ferror[$row]['column'][] = 'GSTIN of supplier';
                                //                                        $ferror[$row]['row'] = $row;    
                                //                                    }
                                $cc = preg_replace('/\s+/', '', trim($cc));
                                $values .= '"' . $cc . '",';
                            } else if ($col == 3) {
                                $cc = preg_replace('/\s+/', '', trim($cc));
                                if (!preg_match("@^[a-zA-Z0-9\-\s/.]+$@", $cc)) {
                                    //                                    if(!preg_match("@^[a-zA-Z0-9\s \/ -.]+$@",$cc)){
                                    $seterror = 1;
                                    //                                        if(!isset($ferror['Invoice number']))
                                    //                                        $ferror['Invoice number'] = $row.'-Invoice number';

                                    $ferror[$row]['column'][] = 'Invoice number';
                                    $ferror[$row]['row'] = $row;
                                } else
                                    $gd_inv_no = $cc;
                                $values .= '"' . $cc . '",';
                            } else if ($col == 4) {
                                $cc = trim($cc);
                                if ($cc != 'INV' && $cc != 'CRN' && $cc != 'DEBIT NOTE') {
                                    $seterror = 1;
                                    //                                        if(!isset($ferror['Invoice number']))
                                    //                                        $ferror['Invoice number'] = $row.'-Invoice number';

                                    $ferror[$row]['column'][] = 'DOCUMENT TYPE';
                                    $ferror[$row]['row'] = $row;
                                } else
                                    $gd_doc_type = $cc;
                                $values .= '"' . $cc . '",';
                            } else if ($col == 6) {
                                if ($cc != '') {
                                    if (is_numeric($cc)) {
                                        $unix_date = ($cc - 25569) * 86400;
                                        $excel_date = 25569 + ($unix_date / 86400);
                                        $unix_date = ($excel_date - 25569) * 86400;
                                        $fdate = gmdate("Y-m-d", $unix_date);
                                        $values .= '"' . $fdate . '",';
                                    } else {
                                        $cc = str_replace('.', "-", $cc);
                                        $cc = str_replace('/', "-", $cc);
                                        $dary = explode('-', $cc);
                                        if ($dary[0] > 31 || $dary[1] > 12 || $dary[2] < 1900 || $dary[2] > 2021) {
                                            //                                                if(!isset($ferror['Invoice Date']))
                                            //                                                    $ferror['Invoice Date'] = $row.'-Invoice Date';
                                            $ferror[$row]['column'][] = 'Invoice Date';
                                            $ferror[$row]['row'] = $row;
                                        } else {
                                            $ffdate = $dary[2] . '-' . $dary[1] . '-' . $dary[0];
                                            $cc = $ffdate;
                                            $values .= '"' . $cc . '",';
                                        }
                                    }
                                } else {
                                    //                                        if(!isset($ferror['Invoice Date']))
                                    //                                            $ferror['Invoice Date'] = $row.'-Invoice Date';
                                    $ferror[$row]['column'][] = 'Invoice Date';
                                    $ferror[$row]['row'] = $row;
                                }
                            } else if ($col == 7 || $col == 10 || $col == 11 || $col == 12 || $col == 13 || $col == 14 || $col == 15) {
                                if (!preg_match("@^[0-9]+$@", $cc)) {
                                    $seterror = 1;
                                    $ferror[$row]['row'] = $row;
                                }
                                $values .= '"' . $cc . '",';
                            } else if ($col == 8 || $col == 9) {
                                if (!preg_match("@^[a-zA-Z]+$@", $cc)) {
                                    $seterror = 1;
                                    $ferror[$row]['row'] = $row;
                                }
                                $values .= '"' . $cc . '",';
                            } else {
                                $values .= '"' . $cell . '",';
                            }
                        }
                    }
                    if (!isset($ferror[$row]))
                        $insAry[] = 'insert ignore into gstr2a_data (' . substr($keys, 0, -1) . ',gd_gid) values (' . substr($values, 0, -1);
                }
            }
        }

        $ferror = array_values($ferror);

        //            if(!empty($ferror)){
        //                $rdata['status'] = 0;
        //                $rdata['error'] = $ferror;
        //                echo json_encode($rdata); exit;
        //            }
        //            else{

        for ($c = 0; $c < count($insAry); $c++) {
            //                echo $insAry[$c].'<br/>';
            $this->db->query($insAry[$c] . ',"' . $filerefid . '")');
        }

        $datequery = $this->db->query('select min(gd_inv_date) mindate, max(gd_inv_date) maxdate from gstr2a_data where gd_gid = ' . $filerefid);
        $dateAry = array();
        foreach ($datequery->result() as $dateq) {
            $dateCon['f_id'] = $filerefid;
            $dateAry['f_min_date'] = $dateq->mindate;
            $dateAry['f_max_date'] = $dateq->maxdate;
        }
        $this->db->where($dateCon);
        $this->db->update('files', $dateAry);

        $rdata['status'] = 1;
        //                $rdata['added_records'] = $inscnt;
        echo json_encode($rdata);
        //            }
    }

    public function delcts_data()
    {
        $refid = $this->input->post('fileref');
        $res = $this->db->query('select * from files where f_id = ' . $refid);
        foreach ($res->result() as $resi) {
            $fname = $resi->f_file;
        }
        unlink(dirname(dirname(dirname(__FILE__))) . '/uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'] . '/cts/' . $fname);

        $this->db->query('delete from cts_data where cd_gid = ' . $refid);
        $this->db->query('delete from cts_data_dump where cd_gid = ' . $refid);
        $this->db->query('delete from files where f_id = ' . $refid);
    }

    public function delgstr2a_data()
    {
        $refid = $this->input->post('fileref');
        $res = $this->db->query('select * from files where f_id = ' . $refid);
        foreach ($res->result() as $resi) {
            $fname = $resi->f_file;
        }
        unlink(dirname(dirname(dirname(__FILE__))) . '/uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'] . '/gstr2a/' . $fname);

        $this->db->query('delete from gstr2a_data where gd_gid = ' . $refid);
        $this->db->query('delete from gstr2a_data_dump where gd_gid = ' . $refid);
        $this->db->query('delete from files where f_id = ' . $refid);
    }

    public function delpc2_data()
    {
        $refid = $this->input->post('fileref');
        $res = $this->db->query('select * from p_files where pf_id = ' . $refid);
        foreach ($res->result() as $resi) {
            $fname = $resi->f_file;
        }
        unlink(dirname(dirname(dirname(__FILE__))) . '/uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'] . '/p_c2/' . $fname);

        $this->db->query('delete from p_tmc_data where ptd_ptfid = ' . $refid);
        $this->db->query('delete from p_tmc_data_dump where ptd_ptfid = ' . $refid);
        $this->db->query('delete from p_files where pf_id = ' . $refid);
    }

    public function delpc1_data()
    {
        $refid = $this->input->post('fileref');
        $res = $this->db->query('select * from p_files where pf_id = ' . $refid);
        foreach ($res->result() as $resi) {
            $fname = $resi->f_file;
        }
        unlink(dirname(dirname(dirname(__FILE__))) . '/uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'] . '/p_c1/' . $fname);

        $this->db->query('delete from p_tmc_data where ptd_ptfid = ' . $refid);
        $this->db->query('delete from p_tmc_data_dump where ptd_ptfid = ' . $refid);
        $this->db->query('delete from p_files where pf_id = ' . $refid);
    }

    public function delptmc_data()
    {
        $refid = $this->input->post('fileref');
        $res = $this->db->query('select * from p_files where pf_id = ' . $refid);
        foreach ($res->result() as $resi) {
            $fname = $resi->f_file;
        }
        unlink(dirname(dirname(dirname(__FILE__))) . '/uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'] . '/p_tmc/' . $fname);

        $this->db->query('delete from p_tmc_data where ptd_ptfid = ' . $refid);
        $this->db->query('delete from p_tmc_data_dump where ptd_ptfid = ' . $refid);
        $this->db->query('delete from p_files where pf_id = ' . $refid);
    }

    public function deltmc_data()
    {
        $refid = $this->input->post('fileref');
        $res = $this->db->query('select * from files where f_id = ' . $refid);
        foreach ($res->result() as $resi) {
            $fname = $resi->f_file;
        }
        unlink(dirname(dirname(dirname(__FILE__))) . '/uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'] . '/tmc/' . $fname);

        $this->db->query('delete from tmc_data where td_tfid = ' . $refid);
        $this->db->query('delete from tmc_data_dump where td_tfid = ' . $refid);
        $this->db->query('delete from files where f_id = ' . $refid);
    }

    public function proceedinsert_pc2()
    {
        $filerefid = $this->input->post('fileref');
        $filename = $this->db->query('select pf_file from p_files where pf_id = ' . $filerefid)->row('pf_file');

        $this->load->library('excel');
        //read file from path
        $filepath = './uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'] . '/p_c2/' . $filename;
        $objPHPExcel = PHPExcel_IOFactory::load($filepath);

        $worksheet = $objPHPExcel->getActiveSheet();
        $highestRow = (int)$worksheet->getHighestRow();
        $highestColumn = $worksheet->getHighestColumn();
        $seterror = 0;
        $ferror = array();
        $pcols = 4;
        $labelAry = array("pc2_stmt_ref_no", "pc2_inv_crn", "pc2_inv_crn_ref_no", "pc2_unpaid_amt");
        for ($row = 1; $row <= $highestRow; $row++) {
            if ($row == 1) {
                for ($col = 0; $col < $pcols; $col++) {
                    $cell = $worksheet->getCellByColumnAndRow($col, $row);
                    $cellref = $cell;
                    $headingary[] = trim(strval($cell));
                }
            } else {
                if ($fieldAry != $headingary) {
                    //                        print_r($fieldAry);
                    //                        echo '<br/>';
                    //                        print_r($headingary);
                    $rdata['status'] = 13;
                    $ferror['column'] = 'Column mismatch.';
                    $rdata['error'] = $ferror;
                    echo json_encode($rdata);
                    exit;
                } else {
                    $ttlrows++;
                    $chkcell = $worksheet->getCellByColumnAndRow(1, $row);
                    if ($chkcell != '') {
                        $keys = '';
                        $values = '';
                        for ($col = 0; $col < $pcols; $col++) {
                            $cell = $worksheet->getCellByColumnAndRow($col, $row);
                            $cell = strtoupper($cell);
                            $cc = strval($cell);
                            $cc = trim($cc);
                            if ($cell != '') {
                                $keys .= $labelAry[$col] . ',';
                                if ($col == 0) {
                                    $cc = preg_replace('/\s+/', ' ', trim($cc));
                                    if (!preg_match("@^[0-9']+$@", $cc)) {
                                        $seterror = 1;
                                        //                                        $ferror[$row]['column'][] = 'GST INVOICE NO';
                                        $ferror[$row]['column'][] = 1;
                                        $ferror[$row]['row'] = $row;
                                    }
                                    $values .= '"' . $cc . '",';
                                } else if ($col == 1 || $col == 2) {
                                    $cc = preg_replace('/\s+/', ' ', trim($cc));
                                    if (!preg_match("@^[0-9a-zA-Z]+$@", $cc)) {
                                        $seterror = 1;
                                        //                                        $ferror[$row]['column'][] = 'GST INVOICE NO';
                                        $ferror[$row]['column'][] = 9;
                                        $ferror[$row]['row'] = $row;
                                    }
                                    $values .= '"' . $cc . '",';
                                } else {
                                    $values .= '"' . $cell . '",';
                                }
                            }
                        }

                        //                            $chk = $this->db->query('select * from tmc_data where td_gst_inv = "'.$td_gst_inv.'"');
                        //                            
                        //                            if($chk->num_rows()>0){
                        //                                $inserror++;
                        //                            }

                        //                            $insArym[] = 'insert into p_tmc_data_dump ('.substr($keys,0,-1).',ptd_ptfid) values ('.substr($values,0,-1); 
                        $insAry[] = 'insert into p_client_upload_2 (' . substr($keys, 0, -1) . ',pc2_pc2fid) values (' . substr($values, 0, -1);
                    }
                }
            }
        }

        $ferror = array_values($ferror);

        //            if(!empty($ferror)){
        //                $rdata['status'] = 0;
        //                $rdata['error'] = $ferror;
        //                echo json_encode($rdata); exit;
        //            }
        //            else{
        $reccnt = $highestRow;
        $inscnt = 0;

        for ($c = 0; $c < count($insAry); $c++) {
            try {
                $this->db->query($insAry[$c] . ',"' . $filerefid . '")');
                if ($this->db->affected_rows())
                    $inscnt++;
            } catch (Exception $e) {
                continue;
            }
        }
        //                $datequery = $this->db->query('select min(ptd_inv_date) mindate, max(ptd_inv_date) maxdate from p_tmc_data where ptd_ptfid = '.$filerefid);
        //                $dateAry = array();
        //                foreach($datequery->result() as $dateq){
        //                    $dateCon['pf_id'] = $filerefid;
        //                    $dateAry['pf_min_date'] = $dateq->mindate;
        //                    $dateAry['pf_max_date'] = $dateq->maxdate;
        //                }
        //                $this->db->where($dateCon);
        //                $this->db->update('p_files',$dateAry);

        $rdata['status'] = 1;
        $rdata['added_records'] = $inscnt;
        echo json_encode($rdata);
        //            }
    }

    public function drecords($refid)
    {
        //................. this function for addition matched data download///////////////
        $this->load->library('excel');
        $this->load->helper('download');
        $this->excel->setActiveSheetIndex(0);

        $headers = array("STMT REF NO", "UNPAIDAMT", "REMARK FOR RECON", "PRIMERY REMARK", "SECONDARY REMARK", "TMC NAME", "SEGMENT", "INV DATE", "GST INV/CRN NO", "GST INV/CRN REF NO", "DOCUMENT TYPE", "BILLING ENTITYNAME", "PASSENGER NAME", "AIRLINE NAME", "TICKET NO", "FLIGHTNO", "SECTOR", "DEP START DATE", "NETPAYABLE", "TMC GSTIN NO", "CUSTOMER GSTIN NO", "BILLABLE / NON BILLABLE", "BUSINESS UNIT", "TRIP CODE", "COST CENTRE", "BUDGET CODE", "P.O NO", "LOCATION", "DEPARTMENT", "EMP ID", "BOOKING RCVD FROM", "PASSENGER TYPE", "AIRLINE CODE", "ARRVL END DATE", "CABINCLASS", "FARECLASS", "BASIC FARE", "IGST", "CGST", "SGST", "MEAL", "SEAT", "OTHER CHARGES", "TKT FARE", "SERVICE CHARGES", "DISCOUNT", "TMC CAN CHARGES", "PANALTY", "CARD CHARGES", "IGST ON SER CHARGES", "CGST ON SER CHARGES", "SGST ON SER CHARGES", "SERVICE TYPE", "AIRLINE PNR", "GDS PNR", " TICKETED ON CORP OR RETAIL", "APPROVER NAME");
        // print_r($refid);die;
        $k = -1;
        $l = 1;
        $this->excel->getActiveSheet()->setTitle('Sheet 1');
        $this->excel->getActiveSheet()->setCellValue('A' . $l, $headers[++$k]);
        $this->excel->getActiveSheet()->setCellValue('B' . $l, $headers[++$k]);
        $this->excel->getActiveSheet()->setCellValue('C' . $l, $headers[++$k]);
        $this->excel->getActiveSheet()->setCellValue('D' . $l, $headers[++$k]);
        $this->excel->getActiveSheet()->setCellValue('E' . $l, $headers[++$k]);
        $this->excel->getActiveSheet()->setCellValue('F' . $l, $headers[++$k]);
        $this->excel->getActiveSheet()->setCellValue('G' . $l, $headers[++$k]);
        $this->excel->getActiveSheet()->setCellValue('H' . $l, $headers[++$k]);
        $this->excel->getActiveSheet()->setCellValue('I' . $l, $headers[++$k]);
        $this->excel->getActiveSheet()->setCellValue('J' . $l, $headers[++$k]);
        $this->excel->getActiveSheet()->setCellValue('K' . $l, $headers[++$k]);
        $this->excel->getActiveSheet()->setCellValue('L' . $l, $headers[++$k]);
        $this->excel->getActiveSheet()->setCellValue('M' . $l, $headers[++$k]);
        $this->excel->getActiveSheet()->setCellValue('N' . $l, $headers[++$k]);
        $this->excel->getActiveSheet()->setCellValue('O' . $l, $headers[++$k]);
        $this->excel->getActiveSheet()->setCellValue('P' . $l, $headers[++$k]);
        $this->excel->getActiveSheet()->setCellValue('Q' . $l, $headers[++$k]);
        $this->excel->getActiveSheet()->setCellValue('R' . $l, $headers[++$k]);
        $this->excel->getActiveSheet()->setCellValue('S' . $l, $headers[++$k]);
        $this->excel->getActiveSheet()->setCellValue('T' . $l, $headers[++$k]);
        $this->excel->getActiveSheet()->setCellValue('U' . $l, $headers[++$k]);
        $this->excel->getActiveSheet()->setCellValue('V' . $l, $headers[++$k]);
        $this->excel->getActiveSheet()->setCellValue('W' . $l, $headers[++$k]);
        $this->excel->getActiveSheet()->setCellValue('X' . $l, $headers[++$k]);
        $this->excel->getActiveSheet()->setCellValue('Y' . $l, $headers[++$k]);
        $this->excel->getActiveSheet()->setCellValue('Z' . $l, $headers[++$k]);
        $this->excel->getActiveSheet()->setCellValue('AA' . $l, $headers[++$k]);
        $this->excel->getActiveSheet()->setCellValue('AB' . $l, $headers[++$k]);
        $this->excel->getActiveSheet()->setCellValue('AC' . $l, $headers[++$k]);
        $this->excel->getActiveSheet()->setCellValue('AD' . $l, $headers[++$k]);
        $this->excel->getActiveSheet()->setCellValue('AE' . $l, $headers[++$k]);
        $this->excel->getActiveSheet()->setCellValue('AF' . $l, $headers[++$k]);
        $this->excel->getActiveSheet()->setCellValue('AG' . $l, $headers[++$k]);
        $this->excel->getActiveSheet()->setCellValue('AH' . $l, $headers[++$k]);
        $this->excel->getActiveSheet()->setCellValue('AI' . $l, $headers[++$k]);
        $this->excel->getActiveSheet()->setCellValue('AJ' . $l, $headers[++$k]);
        $this->excel->getActiveSheet()->setCellValue('AK' . $l, $headers[++$k]);
        $this->excel->getActiveSheet()->setCellValue('AL' . $l, $headers[++$k]);
        $this->excel->getActiveSheet()->setCellValue('AM' . $l, $headers[++$k]);
        $this->excel->getActiveSheet()->setCellValue('AN' . $l, $headers[++$k]);
        $this->excel->getActiveSheet()->setCellValue('AO' . $l, $headers[++$k]);
        $this->excel->getActiveSheet()->setCellValue('AP' . $l, $headers[++$k]);
        $this->excel->getActiveSheet()->setCellValue('AQ' . $l, $headers[++$k]);
        $this->excel->getActiveSheet()->setCellValue('AR' . $l, $headers[++$k]);
        $this->excel->getActiveSheet()->setCellValue('AS' . $l, $headers[++$k]);
        $this->excel->getActiveSheet()->setCellValue('AT' . $l, $headers[++$k]);
        $this->excel->getActiveSheet()->setCellValue('AU' . $l, $headers[++$k]);
        $this->excel->getActiveSheet()->setCellValue('AV' . $l, $headers[++$k]);
        $this->excel->getActiveSheet()->setCellValue('AW' . $l, $headers[++$k]);
        $this->excel->getActiveSheet()->setCellValue('AX' . $l, $headers[++$k]);
        $this->excel->getActiveSheet()->setCellValue('AY' . $l, $headers[++$k]);
        $this->excel->getActiveSheet()->setCellValue('AZ' . $l, $headers[++$k]);
        $this->excel->getActiveSheet()->setCellValue('BA' . $l, $headers[++$k]);
        $this->excel->getActiveSheet()->setCellValue('BB' . $l, $headers[++$k]);
        $this->excel->getActiveSheet()->setCellValue('BC' . $l, $headers[++$k]);
        $this->excel->getActiveSheet()->setCellValue('BD' . $l, $headers[++$k]);
        $this->excel->getActiveSheet()->setCellValue('BE' . $l, $headers[++$k]);

        $this->excel->getActiveSheet()->getStyle('A1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('B1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('B1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('C1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('C1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('D1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('D1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('E1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('E1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('F1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('F1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('G1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('G1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('H1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('H1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('I1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('I1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('J1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('J1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('K1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('K1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('L1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('L1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('M1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('M1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('N1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('N1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('O1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('O1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('P1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('P1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('Q1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('Q1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('R1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('R1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('S1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('S1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('T1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('T1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('U1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('U1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('V1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('V1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('W1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('W1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('X1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('X1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('Y1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('Y1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('Z1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('Z1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('AA1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('AA1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('AB1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('AB1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('AC1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('AC1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('AD1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('AD1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('AE1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('AE1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('AF1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('AF1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('AG1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('AG1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('AH1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('AH1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('AI1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('AI1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('AJ1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('AJ1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('AK1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('AK1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('AL1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('AL1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('AM1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('AM1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('AN1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('AN1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('AO1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('AO1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('AP1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('AP1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('AQ1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('AQ1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('AR1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('AR1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('AS1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('AS1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('AT1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('AT1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('AU1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('AU1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('AV1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('AV1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('AW1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('AW1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('AX1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('AX1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('AY1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('AY1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('AZ1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('AZ1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('BA1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('BA1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('BB1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('BB1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('BC1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('BC1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('BD1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('BD1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('BE1')->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('BE1')->getFont()->setBold(true);

        $rec = $this->db->query('select * from p_client_upload_1 pc1 inner join p_client_upload_2 pc2 on pc1.pc1_stmt_ref_no=pc2.pc2_inv_crn inner join p_tmc_data ptd on ptd.ptd_gst_inv_crn_no=pc2.pc2_inv_crn where pc2_pc2fid = "' . $refid . '"');
        //        $rec = $this->db->query('select * from p_client_upload_1 pc1 inner join p_client_upload_2 pc2 on pc1.pc1_stmt_ref_no=pc2.pc2_inv_crn inner join p_tmc_data ptd on ptd.ptd_gst_inv_crn_no=pc2.pc2_inv_crn where ptd_inv_date between "'.$this->input->post('from').'" and "'.$this->input->post('to').'"');
        //        $this->db->get();
        // print_r($rec->result());die;
        $i = 1;
        foreach ($rec->result() as $recr) {

            if ($i == 1)
                $f_name = $recr->pf_file;

            $this->excel->getActiveSheet()->setCellValue('A' . ++$i, $recr->pc1_stmt_ref_no);
            $this->excel->getActiveSheet()->setCellValue('B' . $i, $recr->pc1_unpaid_amt);
            $this->excel->getActiveSheet()->setCellValue('C' . $i, '');
            $this->excel->getActiveSheet()->setCellValue('D' . $i, '');
            $this->excel->getActiveSheet()->setCellValue('E' . $i, '');
            $this->excel->getActiveSheet()->setCellValue('F' . $i, $recr->ptd_tmc_name);
            $this->excel->getActiveSheet()->setCellValue('G' . $i, $recr->ptd_segment);
            $this->excel->getActiveSheet()->setCellValue('H' . $i, date('d-m-Y', strtotime($recr->ptd_inv_date)));
            $this->excel->getActiveSheet()->setCellValue('I' . $i, $recr->ptd_gst_inv_crn_no);
            $this->excel->getActiveSheet()->setCellValue('J' . $i, $recr->ptd_gst_inv_crn_ref_no);
            $this->excel->getActiveSheet()->setCellValue('K' . $i, $recr->ptd_doc_type);
            $this->excel->getActiveSheet()->setCellValue('L' . $i, $recr->ptd_billing_entity);
            $this->excel->getActiveSheet()->setCellValue('M' . $i, $recr->ptd_pax_name);
            $this->excel->getActiveSheet()->setCellValue('N' . $i, $recr->ptd_airline_name);
            $this->excel->getActiveSheet()->setCellValue('O' . $i, $recr->ptd_tkt_no);
            $this->excel->getActiveSheet()->setCellValue('P' . $i, $recr->ptd_flight_no);
            $this->excel->getActiveSheet()->setCellValue('Q' . $i, $recr->ptd_sector);
            $this->excel->getActiveSheet()->setCellValue('R' . $i, $recr->ptd_dep_start_date);
            $this->excel->getActiveSheet()->setCellValue('S' . $i, $recr->ptd_netpayable);
            $this->excel->getActiveSheet()->setCellValue('T' . $i, $recr->ptd_tmc_gstin_no);
            $this->excel->getActiveSheet()->setCellValue('U' . $i, $recr->ptd_customer_gstin_no);
            $this->excel->getActiveSheet()->setCellValue('V' . $i, $recr->ptd_billable);
            $this->excel->getActiveSheet()->setCellValue('W' . $i, $recr->ptd_business_unit);
            $this->excel->getActiveSheet()->setCellValue('X' . $i, $recr->ptd_trip_code);
            $this->excel->getActiveSheet()->setCellValue('Y' . $i, $recr->ptd_cost_centre);
            $this->excel->getActiveSheet()->setCellValue('Z' . $i, $recr->ptd_budget_code);
            $this->excel->getActiveSheet()->setCellValue('AA' . $i, $recr->ptd_po_no);
            $this->excel->getActiveSheet()->setCellValue('AB' . $i, $recr->ptd_location);
            $this->excel->getActiveSheet()->setCellValue('AC' . $i, $recr->ptd_department);
            $this->excel->getActiveSheet()->setCellValue('AD' . $i, $recr->ptd_emp_id);
            $this->excel->getActiveSheet()->setCellValue('AE' . $i, $recr->ptd_booking_rcvd_from);
            $this->excel->getActiveSheet()->setCellValue('AF' . $i, $recr->ptd_pax_type);
            $this->excel->getActiveSheet()->setCellValue('AG' . $i, $recr->ptd_airline_code);
            $this->excel->getActiveSheet()->setCellValue('AH' . $i, $recr->ptd_arr_end_date);
            $this->excel->getActiveSheet()->setCellValue('AI' . $i, $recr->ptd_cabinclass);
            $this->excel->getActiveSheet()->setCellValue('AJ' . $i, $recr->ptd_fareclass);
            $this->excel->getActiveSheet()->setCellValue('AK' . $i, $recr->ptd_basic_fare);
            $this->excel->getActiveSheet()->setCellValue('AL' . $i, $recr->ptd_igst);
            $this->excel->getActiveSheet()->setCellValue('AM' . $i, $recr->ptd_cgst);
            $this->excel->getActiveSheet()->setCellValue('AN' . $i, $recr->ptd_sgst);
            $this->excel->getActiveSheet()->setCellValue('AO' . $i, $recr->ptd_meal);
            $this->excel->getActiveSheet()->setCellValue('AP' . $i, $recr->ptd_seat);
            $this->excel->getActiveSheet()->setCellValue('AQ' . $i, $recr->ptd_others);
            $this->excel->getActiveSheet()->setCellValue('AR' . $i, $recr->ptd_tkt_fare);
            $this->excel->getActiveSheet()->setCellValue('AS' . $i, $recr->ptd_service_charge);
            $this->excel->getActiveSheet()->setCellValue('AT' . $i, $recr->ptd_discount);
            $this->excel->getActiveSheet()->setCellValue('AU' . $i, $recr->ptd_tmc_can_charge);
            $this->excel->getActiveSheet()->setCellValue('AV' . $i, $recr->ptd_penalty);
            $this->excel->getActiveSheet()->setCellValue('AW' . $i, $recr->ptd_card_charge);
            $this->excel->getActiveSheet()->setCellValue('AX' . $i, $recr->ptd_igst_service_charge);
            $this->excel->getActiveSheet()->setCellValue('AY' . $i, $recr->pgd_cgst_service_charge);
            $this->excel->getActiveSheet()->setCellValue('AZ' . $i, $recr->ptd_sgst_service_charge);
            $this->excel->getActiveSheet()->setCellValue('BA' . $i, $recr->ptd_servicetype);
            $this->excel->getActiveSheet()->setCellValue('BB' . $i, $recr->ptd_airline_pnr);
            $this->excel->getActiveSheet()->setCellValue('BC' . $i, $recr->ptd_gst_pnr);
            $this->excel->getActiveSheet()->setCellValue('BD' . $i, $recr->ptd_corp_retail);
            $this->excel->getActiveSheet()->setCellValue('BE' . $i, $recr->ptd_approver);
        }
        //        $f_name = str_replace('.','-valid.',$f_name);
        $f_name = 'Client download format.xlsx';
        header('Content-Type: application/vnd.ms-excel'); //mime type
        header('Content-Disposition: attachment;filename="' . $f_name . '.xls"'); //tell browser what's the file name
        header('Cache-Control: max-age=0'); //no cache
        $objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5');
        ob_end_clean();
        ob_start();
        $objWriter->save('php://output');
        //        echo "<script>window.close();</script>";
        //    exit;
        // }
    }

    public function proceedinsert_pc1()
    {
        $filerefid = $this->input->post('fileref');
        $filename = $this->db->query('select pf_file from p_files where pf_id = ' . $filerefid)->row('pf_file');

        $this->load->library('excel');
        //read file from path
        $filepath = './uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'] . '/p_c1/' . $filename;
        $objPHPExcel = PHPExcel_IOFactory::load($filepath);

        $worksheet = $objPHPExcel->getActiveSheet();
        $highestRow = (int)$worksheet->getHighestRow();
        $highestColumn = $worksheet->getHighestColumn();
        $seterror = 0;
        $ferror = array();
        $pcols = 20;
        $labelAry = array("pc1_stmt_date", "pc1_stmt_ref_no", "pc1_customer_ref", "pc1_trip_req", "pc1_pax_name", "pc1_unpaid_amt", "pc1_booking_date", "pc1_dep_date", "pc1_ticket_no", "pc1_carrier", "pc1_class", "pc1_routing", "pc1_txn_date", "pc1_payment_stmt", "pc1_dispute_reason", "pc1_missing_support_document", "pc1_comment1", "pc1_comment2", "pc1_comment3", "pc1_remarks");
        for ($row = 1; $row <= $highestRow; $row++) {
            if ($row == 1) {
                for ($col = 0; $col < $pcols; $col++) {
                    $cell = $worksheet->getCellByColumnAndRow($col, $row);
                    $cellref = $cell;
                    $headingary[] = trim(strval($cell));
                }
            } else {
                if ($fieldAry != $headingary) {
                    //                        print_r($fieldAry);
                    //                        echo '<br/>';
                    //                        print_r($headingary);
                    $rdata['status'] = 13;
                    $ferror['column'] = 'Column mismatch.';
                    $rdata['error'] = $ferror;
                    echo json_encode($rdata);
                    exit;
                } else {
                    $ttlrows++;
                    $chkcell = $worksheet->getCellByColumnAndRow(1, $row);
                    if ($chkcell != '') {
                        $keys = '';
                        $values = '';
                        for ($col = 0; $col < $pcols; $col++) {
                            $cell = $worksheet->getCellByColumnAndRow($col, $row);
                            $cell = strtoupper($cell);
                            $cc = strval($cell);
                            $cc = trim($cc);
                            if ($cell != '') {
                                $keys .= $labelAry[$col] . ',';
                                if ($col == 1) {
                                    $cc = preg_replace('/\s+/', ' ', trim($cc));
                                    if (!preg_match("@^[0-9']+$@", $cc)) {
                                        $seterror = 1;
                                        //                                        $ferror[$row]['column'][] = 'GST INVOICE NO';
                                        $ferror[$row]['column'][] = 1;
                                        $ferror[$row]['row'] = $row;
                                    }
                                    $values .= '"' . $cc . '",';
                                } else if ($col == 5) {
                                    $cc = preg_replace('/\s+/', ' ', trim($cc));
                                    if (!preg_match("@^[0-9\-.]+$@", $cc)) {
                                        $seterror = 1;
                                        //                                        $ferror[$row]['column'][] = 'GST INVOICE NO';
                                        $ferror[$row]['column'][] = 9;
                                        $ferror[$row]['row'] = $row;
                                    }
                                    $values .= '"' . $cc . '",';
                                } else {
                                    $values .= '"' . $cell . '",';
                                }
                            }
                        }

                        //                            $chk = $this->db->query('select * from tmc_data where td_gst_inv = "'.$td_gst_inv.'"');
                        //                            
                        //                            if($chk->num_rows()>0){
                        //                                $inserror++;
                        //                            }

                        //                            $insArym[] = 'insert into p_tmc_data_dump ('.substr($keys,0,-1).',ptd_ptfid) values ('.substr($values,0,-1); 
                        $insAry[] = 'insert into p_client_upload_1 (' . substr($keys, 0, -1) . ',pc1_pc1fid) values (' . substr($values, 0, -1);
                    }
                }
            }
        }

        $ferror = array_values($ferror);

        //            if(!empty($ferror)){
        //                $rdata['status'] = 0;
        //                $rdata['error'] = $ferror;
        //                echo json_encode($rdata); exit;
        //            }
        //            else{
        $reccnt = $highestRow;
        $inscnt = 0;

        for ($c = 0; $c < count($insAry); $c++) {
            try {
                $this->db->query($insAry[$c] . ',"' . $filerefid . '")');
                if ($this->db->affected_rows())
                    $inscnt++;
            } catch (Exception $e) {
                continue;
            }
        }
        //                $datequery = $this->db->query('select min(ptd_inv_date) mindate, max(ptd_inv_date) maxdate from p_tmc_data where ptd_ptfid = '.$filerefid);
        //                $dateAry = array();
        //                foreach($datequery->result() as $dateq){
        //                    $dateCon['pf_id'] = $filerefid;
        //                    $dateAry['pf_min_date'] = $dateq->mindate;
        //                    $dateAry['pf_max_date'] = $dateq->maxdate;
        //                }
        //                $this->db->where($dateCon);
        //                $this->db->update('p_files',$dateAry);

        $rdata['status'] = 1;
        $rdata['added_records'] = $inscnt;
        echo json_encode($rdata);
        //            }
    }

    public function proceedinsert_ptmc()
    {
        $filerefid = $this->input->post('fileref');
        $filename = $this->db->query('select pf_file from p_files where pf_id = ' . $filerefid)->row('pf_file');

        $this->load->library('excel');
        //read file from path
        $filepath = './uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'] . '/p_tmc/' . $filename;
        $objPHPExcel = PHPExcel_IOFactory::load($filepath);

        $worksheet = $objPHPExcel->getActiveSheet();
        $highestRow = (int)$worksheet->getHighestRow();
        $highestColumn = $worksheet->getHighestColumn();
        $seterror = 0;
        $ferror = array();
        $pcols = 53;
        $labelAry = array("ptd_tmc_name", "ptd_tmc_gstin_no", "ptd_segment", "ptd_inv_date", "ptd_gst_inv_crn_no", "ptd_gst_inv_crn_ref_no", "ptd_doc_type", "ptd_billing_entity", "ptd_customer_gstin_no", "ptd_billable", "ptd_business_unit", "ptd_trip_code", "ptd_cost_centre", "ptd_budget_code", "ptd_po_no", "ptd_location", "ptd_department", "ptd_emp_id", "ptd_booking_rcvd_from", "ptd_pax_name", "ptd_pax_type", "ptd_airline_code", "ptd_airline_name", "ptd_tkt_no", "ptd_flight_no", "ptd_sector", "ptd_dep_start_date", "ptd_arr_end_date", "ptd_cabinclass", "ptd_fareclass", "ptd_basic_fare", "ptd_igst", "ptd_k3", "ptd_cgst", "ptd_sgst", "ptd_meal", "ptd_seat", "ptd_others", "ptd_tkt_fare", "ptd_service_charge", "ptd_discount", "ptd_tmc_can_charge", "ptd_penalty", "ptd_card_charge", "ptd_igst_service_charge", "pgd_cgst_service_charge", "ptd_sgst_service_charge", "ptd_netpayable", "ptd_servicetype", "ptd_airline_pnr", "ptd_gst_pnr", "ptd_corp_retail", "ptd_approver");
        for ($row = 1; $row <= $highestRow; $row++) {
            if ($row == 1) {
                for ($col = 0; $col < $pcols; $col++) {
                    $cell = $worksheet->getCellByColumnAndRow($col, $row);
                    $cellref = $cell;
                    $headingary[] = strval($cell);
                }
            } else {
                if ($fieldAry != $headingary) {
                    //                        print_r($fieldAry);
                    //                        echo '<br/>';
                    //                        print_r($headingary);
                    $rdata['status'] = 13;
                    $ferror['column'] = 'Column mismatch.';
                    $rdata['error'] = $ferror;
                    echo json_encode($rdata);
                    exit;
                } else {
                    $ttlrows++;
                    $chkcell = $worksheet->getCellByColumnAndRow(1, $row);
                    if ($chkcell != '') {
                        $keys = '';
                        $values = '';
                        for ($col = 0; $col < $pcols; $col++) {
                            $cell = $worksheet->getCellByColumnAndRow($col, $row);
                            $cell = strtoupper($cell);
                            $cc = strval($cell);
                            $cc = trim($cc);
                            if ($cell != '') {


                                $keys .= $labelAry[$col] . ',';
                                if ($col == 0) {
                                    $cc = preg_replace('/\s+/', ' ', trim($cc));
                                    if (!preg_match("@^[a-zA-Z0-9\s]+$@", $cc)) {
                                        $seterror = 1;
                                        //                                        $ferror[$row]['column'][] = 'GST INVOICE NO';
                                        $ferror[$row]['column'][] = 1;
                                        $ferror[$row]['row'] = $row;
                                    }
                                    $values .= '"' . $cc . '",';
                                } else if ($col == 1 && $col == 4 && $col == 5 && $col == 11 && $col == 12 && $col == 13 && $col == 14 && $col == 16 && $col == 17 && $col == 48 && $col == 49) {
                                    $cc = preg_replace('/\s+/', ' ', trim($cc));
                                    if (!preg_match("@^[a-zA-Z0-9]+$@", $cc)) {
                                        $seterror = 1;
                                        //                                        $ferror[$row]['column'][] = 'GST INVOICE NO';
                                        $ferror[$row]['column'][] = 2;
                                        $ferror[$row]['row'] = $row;
                                    }
                                    $values .= '"' . $cc . '",';
                                } else if ($col == 7 && $col == 10 && $col == 15 && $col == 18 && $col == 50) {
                                    $cc = preg_replace('/\s+/', ' ', trim($cc));
                                    if (!preg_match("@^[a-zA-Z0-9\s]+$@", $cc)) {
                                        $seterror = 1;
                                        //                                        $ferror[$row]['column'][] = 'GST INVOICE NO';
                                        $ferror[$row]['column'][] = 3;
                                        $ferror[$row]['row'] = $row;
                                    }
                                    $values .= '"' . $cc . '",';
                                } else if ($col == 23) {
                                    $cc = preg_replace('/\s+/', ' ', trim($cc));
                                    if (!preg_match("@^[a-zA-Z0-9/]+$@", $cc)) {
                                        $seterror = 1;
                                        //                                        $ferror[$row]['column'][] = 'GST INVOICE NO';
                                        $ferror[$row]['column'][] = 4;
                                        $ferror[$row]['row'] = $row;
                                    }
                                    $values .= '"' . $cc . '",';
                                } else if ($col == 24) {
                                    $cc = preg_replace('/\s+/', ' ', trim($cc));
                                    if (!preg_match("@^[a-zA-Z0-9\s/]+$@", $cc)) {
                                        $seterror = 1;
                                        //                                        $ferror[$row]['column'][] = 'GST INVOICE NO';
                                        $ferror[$row]['column'][] = 5;
                                        $ferror[$row]['row'] = $row;
                                    }
                                    $values .= '"' . $cc . '",';
                                } else if ($col == 25) {
                                    $cc = preg_replace('/\s+/', ' ', trim($cc));
                                    if (!preg_match("@^[a-zA-Z0-9\-\s/]+$@", $cc)) {
                                        $seterror = 1;
                                        //                                        $ferror[$row]['column'][] = 'GST INVOICE NO';
                                        $ferror[$row]['column'][] = 6;
                                        $ferror[$row]['row'] = $row;
                                    }
                                    $values .= '"' . $cc . '",';
                                } else if ($col == 26 && $col == 27) {
                                    $cc = preg_replace('/\s+/', ' ', trim($cc));
                                    if (!preg_match("@^[a-zA-Z0-9\-/]+$@", $cc)) {
                                        $seterror = 1;
                                        //                                        $ferror[$row]['column'][] = 'GST INVOICE NO';
                                        $ferror[$row]['column'][] = 7;
                                        $ferror[$row]['row'] = $row;
                                    }
                                    $values .= '"' . $cc . '",';
                                } else if ($col == 29) {
                                    $cc = preg_replace('/\s+/', ' ', trim($cc));
                                    if (!preg_match("@^[a-zA-Z]+$@", $cc)) {
                                        $seterror = 1;
                                        //                                        $ferror[$row]['column'][] = 'GST INVOICE NO';
                                        $ferror[$row]['column'][] = 8;
                                        $ferror[$row]['row'] = $row;
                                    }
                                    $values .= '"' . $cc . '",';
                                } else if ($col > 29 && $col < 48) {
                                    $cc = preg_replace('/\s+/', ' ', trim($cc));
                                    if (!preg_match("@^[0-9\-.]+$@", $cc)) {
                                        $seterror = 1;
                                        //                                        $ferror[$row]['column'][] = 'GST INVOICE NO';
                                        $ferror[$row]['column'][] = 9;
                                        $ferror[$row]['row'] = $row;
                                    }
                                    $values .= '"' . $cc . '",';
                                } else if ($col == 3) {
                                    if (is_numeric($cc)) {
                                        $unix_date = ($cc - 25569) * 86400;
                                        $excel_date = 25569 + ($unix_date / 86400);
                                        $unix_date = ($excel_date - 25569) * 86400;
                                        $fdate = gmdate("Y-m-d", $unix_date);
                                        $values .= '"' . $fdate . '",';
                                    } else {
                                        $cc = str_replace('.', "-", $cc);
                                        $cc = str_replace('/', "-", $cc);
                                        $dary = explode('-', $cc);
                                        if ($dary[0] > 31 || $dary[1] > 12 || $dary[2] < 1900 || $dary[2] > 2021) {
                                            //                                            if(!isset($ferror['INV DATE']))
                                            //                                                $ferror['INV DATE'] = $row.'-INV DATE';
                                            //                                            $ferror[$row]['column'][] = 'INV DATE';
                                            $ferror[$row]['column'][] = 10;
                                            $ferror[$row]['row'] = $row;
                                            $values .= '"' . $cc . '",';
                                        } else {
                                            $ffdate = $dary[2] . '-' . $dary[1] . '-' . $dary[0];
                                            $cc = $ffdate;
                                            $values .= '"' . $cc . '",';
                                        }
                                    }
                                } else if ($col == 2) {
                                    $cc = preg_replace('/\s+/', ' ', trim(strtoupper($cc)));
                                    if ($cc != 'DOMESTIC' && $cc != 'INTERNATIONAL' && $cc != 'VISA' && $cc != 'RAIL' && $cc != 'INSURANCE' && $cc != 'HOTEL' && $cc != 'CAR' && $cc != 'PASSPORT' && $cc != 'MEET & ASSIST' && $cc != 'BUS' && $cc != 'MISC') {
                                        $seterror = 1;
                                        $ferror[$row]['column'][] = 11;
                                        $ferror[$row]['row'] = $row;
                                    }
                                    //                                    else
                                    $td_gst_inv = $cc;
                                    $values .= '"' . $cc . '",';
                                } else if ($col == 6) {
                                    $cc = preg_replace('/\s+/', '', trim(strtoupper($cc)));
                                    if ($cc != 'INV' && $cc != 'CRN') {
                                        $seterror = 1;
                                        $ferror[$row]['column'][] = 12;
                                        $ferror[$row]['row'] = $row;
                                    }
                                    //                                    else
                                    $td_gst_inv = $cc;
                                    $values .= '"' . $cc . '",';
                                } else if ($col == 9) {
                                    $cc = preg_replace('/\s+/', '', trim(strtoupper($cc)));
                                    if ($cc != 'YES' && $cc != 'NO') {
                                        $seterror = 1;
                                        $ferror[$row]['column'][] = 13;
                                        $ferror[$row]['row'] = $row;
                                    }
                                    //                                    else
                                    $td_gst_inv = $cc;
                                    $values .= '"' . $cc . '",';
                                } else if ($col == 20) {
                                    $cc = preg_replace('/\s+/', ' ', trim(strtoupper($cc)));
                                    if ($cc != 'EMPLOYEE' && $cc != 'NEW JOINEE' && $cc != 'DEPENDENT' && $cc != 'GUEST') {
                                        $seterror = 1;
                                        $ferror[$row]['column'][] = 14;
                                        $ferror[$row]['row'] = $row;
                                    }
                                    //                                    else
                                    $td_gst_inv = $cc;
                                    $values .= '"' . $cc . '",';
                                } else if ($col == 28) {
                                    $cc = preg_replace('/\s+/', ' ', trim(strtoupper($cc)));
                                    if ($cc != 'ECONOMY' && $cc != 'BUSINESS' && $cc != 'PREMIUM ECONOMY' && $cc != '1ST CLASS BUSINESS' && $cc != 'WORLD TRAVELLER') {
                                        $seterror = 1;
                                        $ferror[$row]['column'][] = 15;
                                        $ferror[$row]['row'] = $row;
                                    }
                                    //                                    else
                                    $td_gst_inv = $cc;
                                    $values .= '"' . $cc . '",';
                                } else if ($col == 47) {
                                    $cc = preg_replace('/\s+/', ' ', trim(strtoupper($cc)));
                                    //                                    echo $cc; exit;
                                    if ($cc != 'FRESH TKT' && $cc != 'RE-ISSUE TKT' && $cc != 'SUPPORT SERVICE') {
                                        $seterror = 1;
                                        $ferror[$row]['column'][] = 166;
                                        $ferror[$row]['row'] = $row;
                                    }
                                    //                                    else
                                    $td_gst_inv = $cc;
                                    $values .= '"' . $cc . '",';
                                } else {
                                    $values .= '"' . $cell . '",';
                                }
                            }
                        }

                        //                            $chk = $this->db->query('select * from tmc_data where td_gst_inv = "'.$td_gst_inv.'"');
                        //                            
                        //                            if($chk->num_rows()>0){
                        //                                $inserror++;
                        //                            }

                        $insArym[] = 'insert into p_tmc_data_dump (' . substr($keys, 0, -1) . ',ptd_ptfid) values (' . substr($values, 0, -1);
                        $insAry[] = 'insert into p_tmc_data (' . substr($keys, 0, -1) . ',ptd_ptfid) values (' . substr($values, 0, -1);
                    }
                }
            }
        }

        $ferror = array_values($ferror);

        //            if(!empty($ferror)){
        //                $rdata['status'] = 0;
        //                $rdata['error'] = $ferror;
        //                echo json_encode($rdata); exit;
        //            }
        //            else{
        $reccnt = $highestRow;
        $inscnt = 0;

        for ($c = 0; $c < count($insAry); $c++) {
            try {
                $this->db->query($insAry[$c] . ',"' . $filerefid . '")');
                if ($this->db->affected_rows())
                    $inscnt++;
            } catch (Exception $e) {
                continue;
            }
        }
        $datequery = $this->db->query('select min(ptd_inv_date) mindate, max(ptd_inv_date) maxdate from p_tmc_data where ptd_ptfid = ' . $filerefid);
        $dateAry = array();
        foreach ($datequery->result() as $dateq) {
            $dateCon['pf_id'] = $filerefid;
            $dateAry['pf_min_date'] = $dateq->mindate;
            $dateAry['pf_max_date'] = $dateq->maxdate;
        }
        $this->db->where($dateCon);
        $this->db->update('p_files', $dateAry);

        $rdata['status'] = 1;
        $rdata['added_records'] = $inscnt;
        echo json_encode($rdata);
        //            }
    }

    public function proceedinsert_tmc()
    {
        $filerefid = $this->input->post('fileref');
        $filename = $this->db->query('select f_file from files where f_id = ' . $filerefid)->row('f_file');

        $this->load->library('excel');
        //read file from path
        $filepath = './uploads/' . $this->session->userdata['recon_sess_i']['u_corporateid'] . '/tmc/' . $filename;
        $objPHPExcel = PHPExcel_IOFactory::load($filepath);

        $worksheet = $objPHPExcel->getActiveSheet();
        $highestRow = (int)$worksheet->getHighestRow();
        $highestColumn = $worksheet->getHighestColumn();
        $seterror = 0;
        $ferror = array();
        $pcols = 18;
        $labelAry = array('td_segment', 'td_inv_date', 'td_gst_inv', 'td_doc_type', 'td_billing_entity', 'td_empid', 'td_pax', 'td_carrier', 'td_tkt', 'td_flight', 'td_sector', 'td_departure', 'td_cabin_class', 'td_tkt_fare', 'td_net', 'td_air_tax', 'td_airline_pnr', 'td_pnr');
        for ($row = 1; $row <= $highestRow; $row++) {
            //                if($row == 1){
            //                    for($col = 0; $col < $pcols; $col++){
            //                        $cell = $worksheet->getCellByColumnAndRow($col, $row);
            //                        $cellref = $cell;
            //                        $headingary[] = strval($cell);
            //                    }
            //                }
            //                else{
            //                    if($fieldAry != $headingary){
            //                        $rdata['status'] = 13;
            //                        $ferror['column'] = 'Column mismatch.';
            //                        $rdata['error'] = $ferror;
            //                        echo json_encode($rdata); exit;
            //                    }
            //                    else{
            if ($row > 1) {
                $chkcell = $worksheet->getCellByColumnAndRow(1, $row);
                if ($chkcell != '') {
                    $keys = '';
                    $values = '';
                    for ($col = 0; $col < $pcols; $col++) {
                        $cell = $worksheet->getCellByColumnAndRow($col, $row);
                        $cell = strtoupper($cell);
                        $cc = strval($cell);
                        $keys .= $labelAry[$col] . ',';
                        if ($col == 0) {
                            $cc = preg_replace('/\s+/', '', trim($cc));
                            if ($cc != 'DOMESTIC' && $cc != 'INTERNATIONAL' && $cc != 'HOTEL' && $cc != 'MISC') {
                                $seterror = 1;
                                //                                        $ferror[$row]['column'][] = 'GST INVOICE NO';
                                $ferror[$row]['column'][] = 0;
                                $ferror[$row]['row'] = $row;
                            }
                            $values .= '"' . $cc . '",';
                        } else if ($col == 1) {
                            if (is_numeric($cc)) {
                                $unix_date = ($cc - 25569) * 86400;
                                $excel_date = 25569 + ($unix_date / 86400);
                                $unix_date = ($excel_date - 25569) * 86400;
                                $fdate = gmdate("Y-m-d", $unix_date);
                                $values .= '"' . $fdate . '",';
                            } else {
                                $cc = str_replace('.', "-", $cc);
                                $cc = str_replace('/', "-", $cc);
                                $dary = explode('-', $cc);
                                if ($dary[0] > 31 || $dary[1] > 12 || $dary[2] < 1900 || $dary[2] > 2021) {
                                    //                                            if(!isset($ferror['INV DATE']))
                                    //                                                $ferror['INV DATE'] = $row.'-INV DATE';

                                    //                                            $ferror[$row]['column'][] = 'INV DATE';
                                    $ferror[$row]['column'][] = 1;
                                    $ferror[$row]['row'] = $row;
                                    $values .= '"' . $cc . '",';
                                } else {
                                    $ffdate = $dary[2] . '-' . $dary[1] . '-' . $dary[0];
                                    $cc = $ffdate;
                                    $values .= '"' . $cc . '",';
                                }
                            }
                        } else if ($col == 2) {
                            $cc = preg_replace('/\s+/', '', trim($cc));
                            if (!preg_match("@^[a-zA-Z0-9\-\s/.]+$@", $cc)) {
                                $seterror = 1;
                                $ferror[$row]['column'][] = 'GST INVOICE NO';
                                $ferror[$row]['row'] = $row;
                            }
                            $values .= '"' . $cc . '",';
                        } else if ($col == 3) {
                            $cc = preg_replace('/\s+/', '', trim($cc));
                            if ($cc != "CRN" && $cc != "INV") {
                                $seterror = 1;
                                $ferror[$row]['column'][] = 'DOC TYPE';
                                $ferror[$row]['row'] = $row;
                            }
                            $values .= '"' . $cc . '",';
                        } else if ($col == 6 || $col == 7) {
                            if (!preg_match("@^[a-zA-Z\s]+$@", $cc)) {
                                $seterror = 1;
                                $ferror[$row]['column'][] = '6-7';
                                $ferror[$row]['row'] = $row;
                            }
                            $values .= '"' . $cc . '",';
                        } else if ($col == 12) {
                            if (!preg_match("@^[a-zA-Z]+$@", $cc)) {
                                $seterror = 1;
                                $ferror[$row]['column'][] = '8-12';
                                $ferror[$row]['row'] = $row;
                            }
                            $values .= '"' . $cc . '",';
                        } else if ($col == 16 || $col == 17 || $col == 8) {
                            if (!preg_match("@^[a-zA-Z0-9]+$@", $cc)) {
                                $seterror = 1;
                                $ferror[$row]['column'][] = '16-17';
                                $ferror[$row]['row'] = $row;
                            }
                            $values .= '"' . $cc . '",';
                        } else if ($col == 13 || $col == 14) {
                            if (!preg_match("@^[0-9\-]+$@", $cc)) {
                                $seterror = 1;
                                //                                        $ferror[$row]['column'][] = '11-13-14';
                                $ferror[$row]['row'] = $row;
                            }
                            $values .= '"' . $cc . '",';
                        } else if ($col == 11) {
                            if (!preg_match("@^[0-9\-/:\s]+$@", $cc)) {
                                $seterror = 1;
                                $ferror[$row]['column'][] = '11';
                                $ferror[$row]['row'] = $row;
                            }
                            $values .= '"' . $cc . '",';
                        } else {
                            $values .= '"' . $cell . '",';
                        }
                    }
                    if (!isset($ferror[$row]))
                        $insAry[] = 'insert ignore into tmc_data (' . substr($keys, 0, -1) . ',td_tfid) values (' . substr($values, 0, -1);
                    //                        }
                    //                    }
                }
            }
        }

        $ferror = array_values($ferror);

        //            if(!empty($ferror)){
        //                $rdata['status'] = 0;
        //                $rdata['error'] = $ferror;
        //                echo json_encode($rdata); exit;
        //            }
        //            else{
        $reccnt = $highestRow;
        $inscnt = 0;

        for ($c = 0; $c < count($insAry); $c++) {
            try {
                $this->db->query($insAry[$c] . ',"' . $filerefid . '")');
                if ($this->db->affected_rows())
                    $inscnt++;
            } catch (Exception $e) {
                continue;
            }
        }
        $datequery = $this->db->query('select min(td_inv_date) mindate, max(td_inv_date) maxdate from tmc_data where td_tfid = ' . $filerefid);
        $dateAry = array();
        foreach ($datequery->result() as $dateq) {
            $dateCon['f_id'] = $filerefid;
            $dateAry['f_min_date'] = $dateq->mindate;
            $dateAry['f_max_date'] = $dateq->maxdate;
        }
        $this->db->where($dateCon);
        $this->db->update('files', $dateAry);

        $rdata['status'] = 1;
        $rdata['added_records'] = $inscnt;
        echo json_encode($rdata);
        //            }
    }

    public function p_payment_advice()
    {
        $this->checksess();
        $monthAllAryLabel = array('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec');
        $yearAry = array();
        $monthAry = array();
        $m = date('m', time());
        for ($i = 0; $i < $m; $i++) {
            $monthAry[] = $monthAllAryLabel[$i];
        }
        for ($i = 0; $i < 4; $i++) {
            $yearAry[] = date('Y', time()) - $i;
        }
        $data['years'] = $yearAry;
        $data['month'] = $monthAry;
        $data['allmonth'] = $monthAllAryLabel;
        $data['curYear'] = date('Y', time());

        $this->load->view('corporate/p_payment_advice', $data);
    }

    public function p_client2()
    {
        $this->checksess();
        $monthAllAryLabel = array('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec');
        $yearAry = array();
        $monthAry = array();
        $m = date('m', time());
        for ($i = 0; $i < $m; $i++) {
            $monthAry[] = $monthAllAryLabel[$i];
        }
        for ($i = 0; $i < 4; $i++) {
            $yearAry[] = date('Y', time()) - $i;
        }
        $data['years'] = $yearAry;
        $data['month'] = $monthAry;
        $data['allmonth'] = $monthAllAryLabel;
        $data['curYear'] = date('Y', time());

        $this->load->view('corporate/p_client2', $data);
    }

    public function p_client1()
    {
        $this->checksess();
        $monthAllAryLabel = array('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec');
        $yearAry = array();
        $monthAry = array();
        $m = date('m', time());
        for ($i = 0; $i < $m; $i++) {
            $monthAry[] = $monthAllAryLabel[$i];
        }
        for ($i = 0; $i < 4; $i++) {
            $yearAry[] = date('Y', time()) - $i;
        }
        $data['years'] = $yearAry;
        $data['month'] = $monthAry;
        $data['allmonth'] = $monthAllAryLabel;
        $data['curYear'] = date('Y', time());

        $this->load->view('corporate/p_client1', $data);
    }

    public function p_client_download()
    {
        $this->checksess();
        $this->load->view('corporate/p_client_download');
    }

    public function p_tmc()
    {
        $this->checksess();
        $monthAllAryLabel = array('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec');
        $yearAry = array();
        $monthAry = array();
        $m = date('m', time());
        for ($i = 0; $i < $m; $i++) {
            $monthAry[] = $monthAllAryLabel[$i];
        }
        for ($i = 0; $i < 4; $i++) {
            $yearAry[] = date('Y', time()) - $i;
        }
        $data['years'] = $yearAry;
        $data['month'] = $monthAry;
        $data['allmonth'] = $monthAllAryLabel;
        $data['curYear'] = date('Y', time());

        $this->load->view('corporate/p_tmc', $data);
    }

    public function tmc()
    {
        $this->checksess();
        $monthAllAryLabel = array('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec');
        $yearAry = array();
        $monthAry = array();
        $m = date('m', time());
        for ($i = 0; $i < $m; $i++) {
            $monthAry[] = $monthAllAryLabel[$i];
        }
        for ($i = 0; $i < 4; $i++) {
            $yearAry[] = date('Y', time()) - $i;
        }
        $data['years'] = $yearAry;
        $data['month'] = $monthAry;
        $data['allmonth'] = $monthAllAryLabel;
        $data['curYear'] = date('Y', time());

        $this->load->view('corporate/tmc', $data);
    }

    function get_ptmc()
    {
        $this->checksess();
        $fary = array();
        $data = $this->corp->get_ptmc();
        $monthAry = array('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec');
        //        $monthAry = array('JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP','OCT','NOV','DEC');
        foreach ($data->result() as $dat) {
            $dat->pf_min_date_ref = '';
            $dat->pf_max_date_ref = '';
            //            if($dat->pf_min_date != '')
            //                $dat->pf_min_date_ref = date('d-m-Y',strtotime($dat->pf_min_date));
            //            if($dat->pf_max_date != '')
            //                $dat->pf_max_date_ref = date('d-m-Y',strtotime($dat->pf_max_date));
            //            $refm = $dat->pf_month - 1;
            $dat->inv_ref = '';
            $dat->u_name = ucfirst(strtolower($dat->u_name));
            $dat->pf_uploadedat_ref = date('d-m-Y H:i', strtotime($dat->pf_uploadedat));
            $fary['data'][] = $dat;
        }
        echo json_encode($fary);
    }

    function get_ppay()
    {
        $this->checksess();
        $fary = array();
        $data = $this->corp->get_ppay();
        $monthAry = array('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec');
        //        $monthAry = array('JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP','OCT','NOV','DEC');
        foreach ($data->result() as $dat) {
            //            $dat->pf_min_date_ref='';
            //            $dat->pf_max_date_ref='';
            //            if($dat->pf_min_date != '')
            //                $dat->pf_min_date_ref = date('d-m-Y',strtotime($dat->pf_min_date));
            //            if($dat->pf_max_date != '')
            //                $dat->pf_max_date_ref = date('d-m-Y',strtotime($dat->pf_max_date));
            $refm = $dat->pf_month - 1;
            $dat->inv_ref = $monthAry[$refm] . "' " . substr($dat->pf_year, 2, 2);
            $dat->u_name = ucfirst(strtolower($dat->u_name));
            $dat->pf_uploadedat_ref = date('d-m-Y H:i', strtotime($dat->pf_uploadedat));
            $fary['data'][] = $dat;
        }
        echo json_encode($fary);
    }

    function get_pc2()
    {
        $this->checksess();
        $fary = array();
        $data = $this->corp->get_pc2();
        $monthAry = array('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec');
        //        $monthAry = array('JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP','OCT','NOV','DEC');
        foreach ($data->result() as $dat) {
            //            $dat->pf_min_date_ref='';
            //            $dat->pf_max_date_ref='';
            //            if($dat->pf_min_date != '')
            //                $dat->pf_min_date_ref = date('d-m-Y',strtotime($dat->pf_min_date));
            //            if($dat->pf_max_date != '')
            //                $dat->pf_max_date_ref = date('d-m-Y',strtotime($dat->pf_max_date));
            //            $refm = $dat->pf_month - 1;
            //            $dat->inv_ref = $monthAry[$refm]."' ".substr($dat->pf_year,2,2);
            $dat->inv_ref = '';
            $dat->u_name = ucfirst(strtolower($dat->u_name));
            $dat->pf_uploadedat_ref = date('d-m-Y H:i', strtotime($dat->pf_uploadedat));
            $fary['data'][] = $dat;
        }
        echo json_encode($fary);
    }

    function get_pc1()
    {
        $this->checksess();
        $fary = array();
        $data = $this->corp->get_pc1();
        $monthAry = array('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec');
        //        $monthAry = array('JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP','OCT','NOV','DEC');
        foreach ($data->result() as $dat) {
            //            $dat->pf_min_date_ref='';
            //            $dat->pf_max_date_ref='';
            //            if($dat->pf_min_date != '')
            //                $dat->pf_min_date_ref = date('d-m-Y',strtotime($dat->pf_min_date));
            //            if($dat->pf_max_date != '')
            //                $dat->pf_max_date_ref = date('d-m-Y',strtotime($dat->pf_max_date));
            //            $refm = $dat->pf_month - 1;
            $dat->inv_ref = '';
            //            $dat->inv_ref = $monthAry[$refm]."' ".substr($dat->pf_year,2,2);
            $dat->u_name = ucfirst(strtolower($dat->u_name));
            $dat->pf_uploadedat_ref = date('d-m-Y H:i', strtotime($dat->pf_uploadedat));
            $fary['data'][] = $dat;
        }
        echo json_encode($fary);
    }

    function get_tmc()
    {
        $this->checksess();
        $fary = array();
        $data = $this->corp->get_tmc();
        $monthAry = array('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec');
        //        $monthAry = array('JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP','OCT','NOV','DEC');
        foreach ($data->result() as $dat) {
            $dat->f_min_date_ref = '';
            $dat->f_max_date_ref = '';
            if ($dat->f_min_date != '')
                $dat->f_min_date_ref = date('d-m-Y', strtotime($dat->f_min_date));
            if ($dat->f_max_date != '')
                $dat->f_max_date_ref = date('d-m-Y', strtotime($dat->f_max_date));
            $refm = $dat->f_month - 1;
            if (isset($monthAry[$refm])) {
                $dat->inv_ref = $monthAry[$refm] . "' " . substr($dat->f_year, 2, 2);
            }
            $dat->u_name = ucfirst(strtolower($dat->u_name));
            $dat->f_uploadedat_ref = date('d-m-Y H:i', strtotime($dat->f_uploadedat));
            $fary['data'][] = $dat;
        }
        echo json_encode($fary);
    }


    public function gen_gst_datewise_data()
    {
        $this->checksess();
        $date = $this->input->post('date');
        $arr = array();
        $arr1 = array();
        $arr2 = array();
        $arr3 = array();
        $arr4 = array();

        foreach ($date as $key => $value) {
            if ($key < count($date) - 1) {
                $inc = $key + 1;
                $total = $this->corp->total_count($date[$key], $date[$inc]);
                $inv_missing_count = $this->corp->inv_missing_count($date[$key], $date[$inc]); // 0
                $trans_matching_count = $this->corp->trans_matching_count($date[$key], $date[$inc]); // 1
                $tmc_amount_short_count = $this->corp->tmc_amount_short_count($date[$key], $date[$inc]); // 2
                $airline_amount_short_count = $this->corp->airline_amount_short_count($date[$key], $date[$inc]); // 3

                array_push($arr, $total);
                array_push($arr1, $inv_missing_count);
                array_push($arr2, $trans_matching_count);
                array_push($arr3, $tmc_amount_short_count);
                array_push($arr4, $airline_amount_short_count);
            }
        }
        // array_push($tmc_amount_short_count_c , array_sum($arr3));array_push($airline_amount_short_count_c , array_sum($arr4));
        $data = array('total' => $arr, 'inv_missing_count' => $arr1, 'trans_matching_count' => $arr2, 'tmc_amount_short_count' => $arr3, 'airline_amount_short_count' => $arr4);

        echo json_encode($data);
    }

    public function tmc_gst_inv()
    {
        $this->checksess();
        // var_dump($this->input->post('date'));die();


        $dateInput = $this->input->post('date');
        $dateParts = !empty($dateInput) ? explode(' - ', trim($dateInput)) : [];
        // var_dump($dateParts);die();

        if (count($dateParts) === 2 && !empty($dateParts[0])) {
            $from_date = $dateParts[0];
            $to_date = $dateParts[1];
        } else {
            $from_date = '01/01/2020';
            $to_date = date('d/m/Y');
        }

        // Convert to 'Y-m-d' format for database
        $db_from_date = \DateTime::createFromFormat('d/m/Y', $from_date)->format('Y-m-d');
        $db_to_date = \DateTime::createFromFormat('d/m/Y', $to_date)->format('Y-m-d');


        $this->load->database('default', TRUE);
        $this->db->select('tmc_data.td_tkt as tmc_ticket_no,tmc_data.td_pax as tmc_pax,tmc_data.td_tkt_fare as tmc_tkt_fare,air_invoice_details.total_inv_value as airline_fare,air_invoice_details.ticket_no as airline_ticket_no,air_invoice_details.invoice_no as airline_inv_no,gstr2a_data.gd_inv_value as gst_fare, gstr2a_data.gd_inv_no as gst_invoice_no');
        $this->db->from('air_invoice_details');
        $this->db->join('tmc_data', 'air_invoice_details.ticket_no = tmc_data.td_tkt', 'left');
        $this->db->join('gstr2a_data', 'air_invoice_details.invoice_no = gstr2a_data.gd_inv_no', 'left');

        // Filter data based on the date range
         $this->db->where("(
            (gstr2a_data.gd_inv_date BETWEEN '$db_from_date' AND '$db_to_date')
            OR 
            (tmc_data.td_inv_date BETWEEN '$db_from_date' AND '$db_to_date')
        )");

        $query = $this->db->get();
        $result['data'] = $query->result();
        $result['selected_data'] = $from_date . " - " . $to_date;

        $this->load->view('corporate/tmc_gst_inv', $result);
    }
}