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

class Tmc 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('Tmc_model', 'tmc');
        //force_ssl();
        $total = $this->tmc->get_tmc_inv_count();
        $received = $this->tmc->get_tmc_inv_received_count();
        $not_received = $this->tmc->get_tmc_inv_not_received_count();
        $par_received = $this->tmc->get_tmc_inv_par_received_count();

        $this->session->set_flashdata('total', $total);
        $this->session->set_flashdata('received', $received);
        $this->session->set_flashdata('not_received', $not_received);
        $this->session->set_flashdata('par_received', $par_received);
    }

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

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

    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 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('tmc/tmc', $data);
    }
    public function tmc_recon()
    {
        $this->checksess();
        // $data['gstno_list'] = $this->intl->get_gstno();
        // $data['gstno_list'] = array();
        $this->load->view('tmc/tmc_recon');
    }

    public function gen_tmc_recon()
    {
        $this->checksess();
        $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 td_inv_date between "' . $fromdate . '" and "' . $todate . '" order by td_id desc');
        $fres['data'] = array();
        $res2 = new stdClass();
        foreach ($res->result() as $res1) {
            if ($res1->ti_tdid == '')
                $res1->d_remarks = 'Invoice not received';
            else {
                $payremarks = ($res1->s_amt * 100) / $res1->td_tkt_fare;
                if ($payremarks > 70)
                    $res1->d_remarks = 'Invoice Received';
                else
                    $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 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;
        //        }cc

        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();
            // echo  $highestColumn;
            // echo $highestRow;
            // die;
            $seterror = 0;
            $ferror = array();
            $pcols = 18;
            $ttlrows = 0;
            $validRows = 0;
            // $no_of_records=0;
            $inserror = 0;
            $remarksColumn = 'S'; // 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++) {
                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($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($cc);
                                        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 . '"');

                            if ($chk->num_rows() > 0) {
                                $worksheet->setCellValue($remarksColumn . $row, 'Duplicate found'); // Assuming 'Z' is an empty column for remarks
                                $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);
            // echo $ttlrows;
            // echo"breAK";
            // echo $inserror;
            // die;
            // $tt
            if ($ttlrows == $inserror) {


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


            $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());
            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 gen_tmc_datewise_data()
    {
        $this->checksess();
        $date = $this->input->post('date');
        $arr = array();
        $arr1 = array();
        $arr2 = array();
        $arr3 = array();

        $c = array();
        $c1 = array();
        foreach ($date as $key => $value) {
            if ($key < count($date) - 1) {
                $inc = $key + 1;
                $total = $this->tmc->total_count($date[$key], $date[$inc]);
                $received = $this->tmc->get_inv_rec_1($date[$key], $date[$inc]);
                $par_received = $this->tmc->get_inv_rec_2($date[$key], $date[$inc]);
                $not_received = $this->tmc->not_received_count($date[$key], $date[$inc]);
                array_push($arr, $received);
                array_push($arr1, $par_received);
                array_push($arr2, $total);
                array_push($arr3, $not_received);
            }
        }
        array_push($c, array_sum($arr));
        array_push($c1, array_sum($arr3));
        $data = array('received' => $arr, 'par_received' => $arr1, 'total' => $arr2, 'not_received' => $arr3, 'c' => $c, 'c1' => $c1);

        echo json_encode($data);
    }


    public function delete_tmc()
    {
        $update_data['is_active'] = 0;
        $this->db->where('f_id', $this->input->post('id'));
        $data = $this->db->update('files', $update_data);
        if ($data) {
            $this->db->where('td_tfid', $this->input->post('id'));
            $data1 = $this->db->update('tmc_data', $update_data);
            if ($data1) {
                echo 1;
            } else {
                echo 0;
            }
        } else {
            echo 0;
        }
    }

    public function delete_tmc_recon()
    {
        $update_data['is_active'] = 0;
        $this->db->where('td_id', $this->input->post('id'));
        $data = $this->db->update('tmc_data', $update_data);
        if ($data) {
            echo 1;
        } else {
            echo 0;
        }
    }

    public function delete_gst2ra()
    {
        $update_data['is_active'] = 0;
        $this->db->where('f_id', $this->input->post('id'));
        $data = $this->db->update('files', $update_data);
        if ($data) {
            $this->db->where('gd_gid', $this->input->post('id'));
            $data1 = $this->db->update('gstr2a_data', $update_data);
            
            if ($data1) {
                echo 1;
            } else {
                echo 0;
            }
        } else {
            echo 0;
        }
    }

    public function delete_gst2ra_recon()
    {
        $update_data['is_active'] = 0;

        $this->db->where('gd_id', $this->input->post('id'));
        $id= $this->input->post('id');
        $data = $this->db->update('gstr2a_data', $update_data);
     
        if ($data) {
            echo 1;
        } else {
            echo 0;
        }
    }
}