* @see https://codeigniter.com/user_guide/general/urls.html */ function __construct() { parent::__construct(); $this->load->model('Corporate_model','corp'); //force_ssl(); } 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'; foreach($res->result() as $res1){ $htmlData .= 'td_gstr2a_match_id == $res2->gd_id)?" selected='selected'":"").' >'.$res1->td_tkt.''; } $htmlData .= ''; $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 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;$isession->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); } } 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(); $fromdate = $this->input->post('fromdate'); $todate = $this->input->post('todate'); // 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 gen_gstr2a_recon($gid = null){ $this->checksess(); $fromdate = $this->input->post('fromdate'); $todate = $this->input->post('todate'); 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 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.'"'); // $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_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.'"'); $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; $variance = $res1->gd_inv_value - $res1->td_tkt_fare; $res2->variance = $variance; if($res1->ti_file_name == '') $res2->gd_file_status = 'Invoice Missing'; else $res2->gd_file_status = ''; 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'; $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); if($res1->td_tkt_fare == '') $res1->td_tkt_fare = 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'; 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 index(){ redirect('corporate/gstr2a'); } 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(); $this->load->view('corporate/tmc_recon'); } public function gstr2a_recon(){ $this->checksess(); $data['gstno_list'] = $this->corp->get_gstno(); $this->load->view('corporate/gstr2a_recon'); } 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 '
'; // 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;$cdb->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'; // $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; 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 '
'; // 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 != ''){ 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; } 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){ $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); } } } } $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;$cdb->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'; $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); $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); } function p_c1_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 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(); $i=1; foreach($rec->result() as $recr){ if($i == 1) $f_name = $recr->pf_file; $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.'"'); //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 ""; // exit; } function p_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', '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()->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->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(); $i=1; foreach($rec->result() as $recr){ 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); } $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 ""; // 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 ""; // 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 ""; // 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(){ $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_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){ $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_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'].'/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 '
'; // 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;$cdb->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;$cdb->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"); $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"); $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 = 20;$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 '
'; // 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; } $td_gst_inv = $cc; $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 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;$cdb->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;$cdb->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"); $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"); $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 = 53;$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 '
'; // 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) $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'][] = 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++; } $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; $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; for($c=0;$cdb->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;$cdb->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){ for($col = 0; $col < $pcols; $col++){ $cell = $worksheet->getCellByColumnAndRow($col, $row); $cellref = $cell; $headingary[] = strval($cell); } } else{ if($fieldAry != $headingary){ // print_r($fieldAry); // echo '
'; // 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.'",'; } 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;$cdb->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;$cdb->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 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'; $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'; $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 '
'; // 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;$cdb->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->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"); $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(); $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.'"'); //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'); } 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 '
'; // 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;$cdb->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 '
'; // 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;$cdb->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;$cdb->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; $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); } }