<?php
namespace App\Controller;

use Cake\Datasource\ConnectionManager;
use App\Controller\AppController;
use Cake\ORM\TableRegistry;
use Cake\Mailer\Email; 
/**
 * Fds Controller
 *
 * @property \App\Model\Table\FdsTable $Fds
 */
class FdsController extends AppController
{
    public $components = array('ExportXls');
    /**
     * Index method
     *
     * @return \Cake\Network\Response|null
     */
    public function index(){
        $this->loadModel('Filenames');
		$this->loadModel('Fds');
		$all = array('all'=>'All');
		$this->loadModel('Customers');
        $conditions = array(); 
		/*if(!empty($this->request->query['firstname'])){
			$conditions['conditions']['and']['Customers.firstname LIKE'] = '%'.$this->request->query['firstname'].'%';
			$custCond['Customers.firstname LIKE'] = '%'.$this->request->query['firstname'].'%';
		}
		
		if(!empty($this->request->query['panno'])){
			 $conditions['conditions']['and']['Customers.panno'] = $this->request->query['panno'];
		}*/
        if (!empty($this->request->query) && count($this->request->query) > 1) {

            $dated2 = (($this->request->query['datedform'] != '') ? $this->request->query['datedform'] : '');
            $date1 = explode('/', $dated2);
            $datedform = implode('-', array_reverse($date1));
            $dated1 = $this->request->query['datedto'];
            $date = explode('/', $dated1);
            $datedto = implode('-', array_reverse($date));
            // if ($datedform && $datedto) {
                
            //     $conditions['conditions'] =  array(
            //             'and' => array(
            //                 array('DATE(Fds.created) >= ' => $datedform,
            //                     'DATE(Fds.created) <= ' => $datedto
            //                 )
            //             ));
            // }
        }
        
        
        if($this->Auth->user('group_id')=='3'){        
            $branch_id = $this->Auth->user('branch_id');     
            $conditions['conditions']['and']['Fds.posteduserbranchid'] = "$branch_id";
        }elseif($this->Auth->user('group_id')=='4' || $this->Auth->user('group_id')=='5' || $this->Auth->user('group_id')=='6' || $this->Auth->user('group_id')=='7' || $this->Auth->user('group_id')=='9'){ 
            //$branch_id = $this->Auth->user('branch_id');    
            $userid = $this->Auth->user('id'); 
            //$conditions['conditions']['and']['Fds.posteduserbranchid'] = "$branch_id";
            $conditions['conditions']['and']['Fds.postedby'] = "$userid";
        }
       // 'contain' => ['Companies', 'Branches','Brokers', 'Plans', 'Banks']
        $this->paginate = [
            'contain' => ['Customers','Companies', 'Branches','Brokers']
        ];
		
		$this->Search->addFilter('firstname',['attributes'=>['placeholder'=>'Investor Name','label'=>'Investor Name','class'=>'serachfrm serachfrm_clearable','id'=>'autocomplete1','value'=>isset($this->request->query['firstname'])?$this->request->query['firstname']:''],]);   
		 $custname = $this->Customers->find('list',['valueField'=>'firstname'])->order(['firstname' => 'ASC'])->group(['Customers.firstname'])->toArray();
		 $custname = array_values($custname);
		 
		 $this->Search->addFilter('minorname', [
               'attributes'=>['placeholder'=>'Minor Name','label'=>'Minor Name :','class'=>'searchfrm','value' => isset($this->request->query['minorname'])?$this->request->query['minorname']:''], 'options' => $all+$this->Fds->find('list',['keyField' => 'minorname','valueField' => 'minorname'])->toArray(),
              ]); 
		 $this->Search->addFilter('panno', ['attributes'=>['placeholder'=>'Pan No','label'=>'Pan No :','class'=>'searchfrm serachfrm_clearable','value'=>!empty($this->request->query['panno'])?$this->request->query['panno']:'']]);
        
        
        $this->Search->addFilter('branch', [
            'attributes'=>['placeholder'=>'Branch Name','label'=>'Branch Name :','class'=>'searchfrm','value' => isset($this->request->query['branch'])?$this->request->query['branch']:''],
            'options' => $all+$this->Fds->Branches->find('list')->toArray(),
        ]);
        $emaillstatus = array(1=>'Sent',2=>'Unsent');
		$this->Search->addFilter('emailstatus', [
            'attributes'=>['placeholder'=>'Email Status','label'=>'Email Status :','class'=>'searchfrm','value' => isset($this->request->query['emailstatus'])?$this->request->query['emailstatus']:''],
            'options' => $all+$emaillstatus,
        ]);
        
        $this->Search->addFilter('datedform', [
            'attributes'=>['placeholder'=>'Start Date','label'=>'Start Date :','class'=>'searchfrm','value' => isset($this->request->query['datedform'])?$this->request->query['datedform']:''],
        ]);
        $this->Search->addFilter('datedto', [
            'attributes'=>['placeholder'=>'End Date','label'=>'End Date :','class'=>'searchfrm','value' => isset($this->request->query['datedto'])?$this->request->query['datedto']:''],
        ]);
        //
        if (!empty($this->request->query) && count($this->request->query) > 1) {

            $dated2 = (($this->request->query['datedform'] != '') ? $this->request->query['datedform'] : '');
            $date1 = explode('/', $dated2);
            $datedform = implode('-', array_reverse($date1));
            $dated1 = $this->request->query['datedto'];
            $date = explode('/', $dated1);
            $datedto = implode('-', array_reverse($date));
            if ($datedform && $datedto) {
                $conditions['conditions'] =  array(
                        'and' => array(
                            array('DATE(Fds.created) >= ' => $datedform,
                                'DATE(Fds.created) <= ' => $datedto
                            )
                        ));
            }
        }
       
       
       if(!empty($this->request->query['firstname'])){
			$conditions['conditions']['and']['Customers.firstname LIKE'] = '%'.$this->request->query['firstname'].'%';
			$custCond['Customers.firstname LIKE'] = '%'.$this->request->query['firstname'].'%';
		}
		
		if(!empty($this->request->query['panno'])){
			 $conditions['conditions']['and']['Customers.panno'] = $this->request->query['panno'];
		}
		
       //for Branch
		if(!empty($this->request->query['branch']) && $this->request->query['branch']!= 'all'){
			$conditions['conditions']['and']['Fds.branch_id'] = $this->request->query['branch'];
		}
		
		//for Minor Name
		if(isset($this->request->query['minorname']) && !empty($this->request->query['minorname'])){
        $minorname = $this->request->query['minorname']; 
        if ($minorname && $minorname != 'all'){
    	 $conditions['conditions']['and']['minorname LIKE'] = '%'.$minorname.'%';
        }
        }else{
            $minorname = 'all';
		}
		
       //for email search
		if(!empty($this->request->query['emailstatus']) && $this->request->query['emailstatus']!= 'all'){
		   if($this->request->query['emailstatus'] == 1){
			$conditions['conditions']['and']['Fds.emailSendBy >='] = $this->request->query['emailstatus'];   
		   }else{
			$conditions['conditions']['and']['Fds.emailSendBy IS'] = null;   
		   }
			
		}
       
      //  $conditions['conditions']['or']['DATE(Fds.created) = '] = date('Y-m-d');
        $fds = $this->Fds->find('all', $conditions);
		$fds->innerJoinWith('Customers'); 
        $cnt = $fds->count();              
    		if($fds){
    			$total_amount = 0;                   
    			 foreach ($fds as $fd){ 
    				$total_amount += $fd->amount;
            }
        } 
       // $fds = $this->paginate($fds,['order' => ['Fds.id' => 'DESC']]);
       if(empty($this->request->query['export']) && ($this->request->query['export']!='pdf') ){
			$fds = $this->paginate($fds,['order' => ['Fds.id' => 'DESC']]);
		}
        
        $this->set(compact('fds','total_amount','cnt','custname'));
        $this->set('_serialize', ['fds']);
        
        
          if(!empty($this->request->query['export']) && ($this->request->query['export']=='pdf') ){
            $fileName = "export_fdentry_pdf".date("d-m-y:h:s");
			$this->set('filename', $fileName);
            if(!empty($this->request->query['export']=='pdf')){
                $this->render('export_fdentry_pdf','export_pdf');
            }
		  }	
    }
    
 
 //for excell  
public function fdexcelentry(){
	$this->loadModel('Mtables');
	$this->loadModel('Filenames');
	$this->loadModel('Branches');
	$this->loadModel('Users');
	$this->loadModel('Customers');
    $filenames = $this->Filenames->find('list',['keyField'=>'id','valueField'=>'fname'])->order(['fname' => 'ASC'])->toArray();
	$fileName = "fdexcelentrydata_".date("d-m-y:h:s").".xls";
	$conditions = array();
	
	
	if (!empty($this->request->query) && count($this->request->query) > 1) {

            $dated2 = (($this->request->query['datedform'] != '') ? $this->request->query['datedform'] : '');
            $date1 = explode('/', $dated2);
            $datedform = implode('-', array_reverse($date1));
            $dated1 = $this->request->query['datedto'];
            $date = explode('/', $dated1);
            $datedto = implode('-', array_reverse($date));
            if ($datedform && $datedto) {
                $conditions['conditions'] =  array(
                        'and' => array(
                            array('DATE(Fds.created) >= ' => $datedform,
                                'DATE(Fds.created) <= ' => $datedto
                            )
                        ));
            }
        }
		
	//for Branch
		if(!empty($this->request->query['branch']) && $this->request->query['branch']!= 'all'){
			$conditions['conditions']['Fds.branch_id'] = $this->request->query['branch'];
		}
		
		//for Minor Name
		if(isset($this->request->query['minorname']) && !empty($this->request->query['minorname'])){
        $minorname = $this->request->query['minorname']; 
        if ($minorname && $minorname != 'all'){
    	 $conditions['conditions']['and']['minorname LIKE'] = '%'.$minorname.'%';
        }
        }else{
            $minorname = 'all';
		}
		
       //for email search
		if(!empty($this->request->query['emailstatus']) && $this->request->query['emailstatus']!= 'all'){
		   if($this->request->query['emailstatus'] == 1){
			$conditions['conditions']['and']['Fds.emailSendBy >='] = $this->request->query['emailstatus'];   
		   }else{
			$conditions['conditions']['and']['Fds.emailSendBy IS'] = null;   
		   }
			
		}
		
		
		
		//for customer
		
		if(!empty($this->request->query['firstname'])){
			$conditions['conditions']['and']['Customers.firstname LIKE'] = '%'.$this->request->query['firstname'].'%';
			$custCond['Customers.firstname LIKE'] = '%'.$this->request->query['firstname'].'%';
		}
		
		if(!empty($this->request->query['panno'])){
			 $conditions['conditions']['and']['Customers.panno'] = $this->request->query['panno'];
		}
		
       // $fds = $this->Fds->find('all', $conditions);
       $fds = $this->Fds->find('all', $conditions)->order(['Fds.id' => 'DESC']);
       $fds->innerJoinWith('Customers');//echo '<pre>';print_r($fds);die(); 
       $cnt = $fds->count();        
    		if($fds){
    			$total_amount = 0;                   
    			 foreach ($fds as $fd){ 
    				$total_amount += $fd->amount;
            }
        }
        
        //$fds = $this->paginate($fds,['order' => ['Fds.id' => 'DESC']]);
		
		$datafds = $fds->toArray(); 
		 
		$mbranches = TableRegistry::get('Branches');
		$musers = TableRegistry::get('Users');
		$brokers = TableRegistry::get('Brokers');
		$companies = TableRegistry::get('Companies');
     
		$headerRow = array('Sr No', 'Applicant Name', 'Pan No', 'Sub Broker','Email','Deposit Type','Cheque No','Amount' ,'Period (months)',  'Branch', 'Company','Created','Created By(User Name)');
        $j=1;
		for($i=0; $i< count($datafds); $i++){
				if($datafds[$i]->branch_id > 0){
                            $branchid = $mbranches->find()->where(['id' => $datafds[$i]->branch_id]);
                            $data1 = $branchid->toArray();
                            $branchname = $data1[0]->branchname;
                        }else{
                            $branchname = '';
                        }
                         
				if($datafds[$i]->subbroker_id > 0){
                            $brokerid = $brokers->find()->where(['id' => $datafds[$i]->subbroker_id]);
                            $data1 = $brokerid->toArray();
                            $brokername = $data1[0]->name;
                        }else{
                            $brokername = '';
                        }
				if($datafds[$i]->postedby > 0){
                            $posteddata = $musers->find()->where(['id' => $datafds[$i]->postedby]);
                            $data1 = $posteddata->toArray();
                            $username = $data1[0]->username;
                        }else{
                            $username = '';
                        }
				if($datafds[$i]->productcompanyid > 0){
                            $productcompanyid = $companies->find()->where(['id' => $datafds[$i]->productcompanyid]);
                            $data1 = $productcompanyid->toArray();
                            $productcompanyName = $data1[0]->company;
                        }else{
                            $productcompanyName = '';
                        }
                        
                if($datafds[$i]->minor == 1) {
                             $aplicantname = $datafds[$i]->minorname.' U/G '.$fd->fapplicantname;
                          }else{
                              $aplicantname = $datafds[$i]->fapplicantname;
                          } 
				      
                $format_of = !empty($filenames[$datafds[$i]->csvid]) ? $filenames[$datafds[$i]->csvid] :  "";
                $data[] = [$j, $aplicantname ,trim($datafds[$i]->panno), $brokername, $datafds[$i]->email, $datafds[$i]->typeofdeposit,$datafds[$i]->chequeno, $datafds[$i]->amount, $datafds[$i]->tenure, $branchname, $productcompanyName, $datafds[$i]->created, $username, $datafds[$i]->remark];
            
            $j++;
                }
				$this->ExportXls->export($fileName, $headerRow, $data,$branchname);
	
} 

    /**
     * View method
     *
     * @param string|null $id Fd id.
     * @return \Cake\Network\Response|null
     * @throws \Cake\Datasource\Exception\RecordNotFoundException When record not found.
     */
   public function view($id = null)
    {
        $fd = $this->Fds->get($id, [
            'contain' => ['Companies','Brokers']
        ]); 
		$companies = TableRegistry::get('Companies');
		$branches = TableRegistry::get('Branches');
		$plans = TableRegistry::get('Plans');
		$banks = TableRegistry::get('Banks');
		$users = TableRegistry::get('Users');
		$brokers = TableRegistry::get('Brokers');
		 if($fd->productcompanyid > 0) {
			$productcompanyid = $companies->find()->where(['id' => $fd->productcompanyid]);
			$data1 = $productcompanyid->toArray();
			$productcompanyName = $data1[0]->company;
			}
			else{
			$productcompanyName = '';
			}
			$this->set('productcompanyName',$productcompanyName);
			
		if($fd->mainbrokerid > 0) {
			$mainbrokerid = $brokers->find()->where(['id' => $fd->mainbrokerid]);
			$data1 = $mainbrokerid->toArray();
			$mainbrokerName = $data1[0]->name;
			//pr()
			}
			else{
			$mainbrokerName = '';
			}
			$this->set('mainbrokerName',$mainbrokerName);	
		if($fd->plan_id > 0) 
		{
			$planid = $plans->find()->where(['id' => $fd->plan_id]);
			$data2 = $planid->toArray();
			$planname = $data2[0]->Planname;
		}
		else{
		$planname = '';
		}
		$this->set('planname',$planname);
		//pr($fd->bank_id);
		//die();
		if($fd->bank_id > 0)
		{
			$bank_id = $banks->find()->where(['id' => $fd->bank_id]);
			$data3 = $bank_id->toArray();
			$bankname = $data3[0]->banks;
		}
		else{
		$bankname = '';
		}
		$this->set('bankname',$bankname);
		//pr($fd->posteduserbranchid);
		//die();
		if($fd->branch_id > 0) {
			$branchid = $branches->find()->where(['id' => $fd->branch_id]);
			$data4 = $branchid->toArray();
			$branchname = $data4[0]->branchname;
		}
		else
		{
			$branchname = '';
		}
		$this->set('branchname', $branchname);

		if($fd->posteduserbranchid > 0) {
			$posteduserbranchid = $branches->find()->where(['id' => $fd->posteduserbranchid]);
			$data4 = $posteduserbranchid->toArray();
			$posteduserbranch = $data4[0]->branchname;
		}
		else
		{
			$posteduserbranch = '';
		}
		$this->set('posteduserbranch', $posteduserbranch);
		 if($fd->postedby > 0){
            $postedby = $users->find()->where(['id' => $fd->postedby]);
            $data11 = $postedby->toArray();
            $userName = $data11[0]->username;
       }else{
           $userName = '';
       }    
            $this->set('userName', $userName);
       
        $subbrokers = $this->Fds->Brokers->find('list', [
            'keyField' => 'id',
            'valueField' => 'name'
        ])-> where(['role' => 'Subbroker'])->toArray();
        $this->set(compact('subbrokers'));
        //$fd = $this->Fds->get($id);
        $this->set('fd', $fd);
        $this->set('_serialize', ['fd']);
    }

    /**
     * Add method
     *
     * @return \Cake\Network\Response|void Redirects on successful add, renders view otherwise.
     */
    public function add()
    {
        $this->loadModel('Filenames');
        $this->loadModel('Customers');
        if ($this->request->is('ajax','get')) {         
            /*$terms = $this->Fds->Customers->find('all', [
                'conditions' => array('Customers.firstname LIKE ' => '%' . $this->request->query["term"] . '%'),
                'limit' => 10
            ]);*/
            
            $terms = $this->Fds->Customers->find('all', [
                'conditions' => array('Customers.firstname LIKE ' => '%' . $this->request->query["term"] . '%'),
                'group' => array('Customers.firstname'),
                'order' => array('Customers.firstname ASC'),
                'limit' => 10
            ]);
          
            $data = array();$data1 = array();
            foreach($terms as $term) {
                $row = $term->firstname;
                array_push($data, $row);
            }
            $this->autoRender = false;      
            // $this->layout = 'ajax';
            $this->set('terms', $terms);
            echo json_encode($data);
            exit;
            //return json_encode($data);
         }else {
            //echo json_encode('Nothings found');
         }
         
         
        $fd = $this->Fds->newEntity();
        if ($this->request->is('post')) {
            $fd = $this->Fds->patchEntity($fd, $this->request->data);
            $fd->postedby = $this->Auth->user('id');
            $fd->posteduserbranchid = $this->Auth->user('branch_id');
          //  $fd->branch_id = $this->Auth->user('branch_id');
            if($fd->subbroker_id != ''){
                $fd->broker_id = $fd->subbroker_id;
            }else{
                $fd->broker_id = 1;
            }
            
           $custmoerId = $this->getCustomerId($fd->panno);
           if($custmoerId != ''){
                $fd->customer_id = $custmoerId;
            }
            $updata= array();
			$updata['panno'] = $this->request->data['panno'];
			$updata['phoneno'] = $this->request->data['phoneno'];
			$updata['email'] = $this->request->data['email'];
			$cdatas = $this->Customers->get($custmoerId, ['contain' => []]);
			$cdatas = $this->Customers->patchEntity($cdatas, $updata);
			
			
			$this->Customers->save($cdatas); 
            if ($this->Fds->save($fd)) {
                $this->Flash->success(__('The fd has been saved.'));
                
                //-----Auto Synch to Master FD mtables-----------
                $this->LoadModel("Mtables");
                $this->LoadModel("Mutualfunds");
                $this->loadModel('Filenames');
                $this->loadModel('Companies');
                $filenames = $this->Filenames->find('list',['keyField'=>'id','valueField'=>'fname'])->order(['fname' => 'ASC'])->toArray();
                //echo $fd_id;exit;
                //$productcompanies = $this->Fds->Companies->find('list')-> where(['associatewith' => 'product'])->where(['productid LIKE' => '%2%'])->order(['company' => 'ASC'])->toArray();
                $productcompanies = $this->Fds->Companies->find('list')-> where(['associatewith' => 'product'])->where(['productid LIKE' != 'Null'])->order(['company' => 'ASC'])->toArray();
                
                //pr($productcompanies);exit;

                $fdtable_fdid = $fd->id;
                $fdtable_fd = array();
                $fds = array();
                if(!empty($fdtable_fdid)){

                    $is_exists = $this->Mtables->find('all', array('conditions'=>array('Mtables.fdtable_fdid'=>$fdtable_fdid)));
                    $number = $is_exists->count();
                    if ($number > 0){
                        //$this->Flash->error(__('FD Already Exist. on Master FD '));
                        return $this->redirect(['action' => 'viewsendemail',$fd->id]);
                    }

                    $fdtable_fd = $this->Fds->get($fdtable_fdid, [
                        'contain' => ['Companies','Plans']
                    ])->toArray();
                    if(!empty($fdtable_fd)){

                        $fds['csvid'] = $fdtable_fd['filenameid'];
                        $fds['customer_id'] = $fdtable_fd['customer_id'];
                        $fds['customer_name'] = $fdtable_fd['fapplicantname'];
                        $fds['pan_no'] = $fdtable_fd['panno'];
                        $fds['email'] = $fdtable_fd['email'];
                        $fds['mobileno'] = $fdtable_fd['phoneno'];
                        $fds['application_no'] = $fdtable_fd['applicationno'];
                        $fds['typeofdeposit'] = $fdtable_fd['typeofdeposit'];
                        $fds['application_date'] = $fdtable_fd['planstartdate'];
                        $fds['cheque_no'] = $fdtable_fd['chequeno'];
                        $fds['utr_fdr_no'] = $fdtable_fd['fdrno'];
                        $fds['investment_amount'] = $fdtable_fd['amount'];
                        //As per the zoom discussion 01-12-2021
                        if($fdtable_fd['typeofdeposit']=='both'){
                            if(!empty($fdtable_fd['fdramount'])){
                                $fds['investment_amount'] = $fdtable_fd['amount'] + $fdtable_fd['fdramount'];
                            }
                        }
                        $fds['period'] = $fdtable_fd['tenure'];
                        $fds['maturity_date'] = $fdtable_fd['maturitydate'];
                        //$fds['address'] = '';
                        $fds['product_name'] = $fdtable_fd['plan']['Planname'];
                        $fds['institution_name'] = !empty($productcompanies[$fdtable_fd['productcompanyid']]) ? $productcompanies[$fdtable_fd['productcompanyid']] : '' ;
                        $fds['branch_id'] = $fdtable_fd['branch_id'];
                        $fds['sub_broker'] = $fdtable_fd['subbroker_id'];
                        //$fds['email'] = '';
                        $fds['manager_id'] = $fdtable_fd['manager_id'];
                        $fds['fdtable_fdid'] = $fdtable_fd['id'];

                        $fds['despatchdate'] = $fdtable_fd['despatchdate'];


                        $fds['modifiedby'] = $this->Auth->user('id');
                        $fds['verified'] = 1;
                        $fds['manually_added'] = 1;

                        $fds = $this->Mtables->newEntity($fds);
                        //pr($fds);exit;

                        if ($this->Mtables->save($fds)) {

                        } else {
                            $this->Flash->error(__('The FD data could not be Synch to Master FD. Please, try again.'));
                        }
                    }
                    

                }
                //pr($fdtable_fd);exit;
                //-----Auto Synch to Master FD mtables End-----------

                return $this->redirect(['action' => 'viewsendemail', $fd->id]);
            } else {
                $this->Flash->error(__('The fd could not be saved. Please, try again.'));
            }
        }
        $users = $this->Fds->Customers->find('list')->order(['firstname' => 'ASC']);
     
        $subbrokers = $this->Fds->Brokers->find('list', [
            'keyField' => 'id',
            'valueField' => 'name'
        ])-> where(['role' => 'Subbroker'])->order(['name' => 'ASC']);
        $mainbrokers = $this->Fds->Brokers->find('list', [
            'keyField' => 'id',
            'valueField' => 'name'
        ])-> where(['role' => 'Mainbroker']);
        
        $Filenames = $this->Filenames->find('list', [
            'keyField' => 'id',
            'valueField' => 'fname'
        ])->order(['fname' => 'ASC']);
        
         $regionalmanagers = $this->Fds->Regionalmanagers->find('list',[
                'keyField'=>'id',
                'valueField'=>'manager'
            ])->order(['manager' => 'ASC']);
        

        $usercompanies = $this->Fds->Companies->find('list')-> where(['associatewith' => 'user'])->order(['company' => 'ASC']);
	$userbranches = $this->Fds->Branches->find('list', ['limit' => 200])->order(['branchname' => 'ASC']);
        //$productcompanies = $this->Fds->Companies->find('list')-> where(['associatewith' => 'product'])->order(['company' => 'DESC']);
        $productcompanies = $this->Fds->Companies->find('list')-> where(['associatewith' => 'product'])->where(['productid LIKE' != 'Null'])->order(['company' => 'ASC']);
        $plans = $this->Fds->Plans->find('list')-> where(['product_id' != 'Null'])->order(['planname' => 'ASC']);
        
        
        //$subbrokers = $this->Fds->Subbrokers->find('list', ['limit' => 200]);
        $banks = $this->Fds->Banks->find('list', ['limit' => 200])->order(['banks' => 'ASC']);
        
        $typeofdeposit = array('fresh'=>'Fresh','renewal'=>'Renewal','both'=>'Both','onlinefresh'=>'Online Fresh','onlinerenewal'=>'Online Renewal');
       
        
        $this->set(compact('Filenames','fd','users','mainbrokers','productcompanies', 'usercompanies', 'userbranches', 'plans', 'subbrokers', 'banks', 'bankdetails', 'typeofdeposit','regionalmanagers'));
       // $this->set(compact('fd', 'companies', 'branches', 'plans', 'subbrokers', 'banks', 'bankdetails'));
        $this->set('_serialize', ['fd']);
    }

    /**
     * Edit method
     *
     * @param string|null $id Fd id.
     * @return \Cake\Network\Response|void Redirects on successful edit, renders view otherwise.
     * @throws \Cake\Network\Exception\NotFoundException When record not found.
     */
    public function edit($id = null)
    {
        $this->loadModel('Customers');
        $this->loadModel('Filenames');
        if ($this->request->is('ajax','get')) {         
            /*$terms = $this->Fds->Customers->find('all', [
                'conditions' => array('Customers.firstname LIKE ' => '%' . $this->request->query["term"] . '%'),
                'limit' => 10
            ]);*/
            
            $query = $this->Fds->Customers->find('all',['conditions' => array('Customers.firstname LIKE ' =>  $id),
                'group' => array('Customers.firstname','Customers.panno'),
                'order' => array('Customers.firstname ASC')
            ]);
          
            $data = array();$data1 = array();
            foreach($terms as $term) {
                $row = $term->firstname;
                array_push($data, $row);
            }
            $this->autoRender = false;      
            // $this->layout = 'ajax';
            $this->set('terms', $terms);
            echo json_encode($data);
            exit;
            //return json_encode($data);
         }else {
            //echo json_encode('Nothings found');
         }
         
         
        $fd = $this->Fds->get($id, [
            'contain' => []
        ]);
        if ($this->request->is(['patch', 'post', 'put'])) {
            $fd = $this->Fds->patchEntity($fd, $this->request->data);
            $fd->postedby = $this->Auth->user('id');
            $fd->posteduserbranchid = $this->Auth->user('branch_id');
           // $fd->branch_id = $this->Auth->user('branch_id');
            if($fd->subbroker_id != ''){
                $fd->broker_id = $fd->subbroker_id;
            }else{
                $fd->broker_id = 1;
            }
             $custmoerId = $this->getCustomerId($fd->panno);
           if($custmoerId != ''){
                $fd->customer_id = $custmoerId;
            }
            
            $updata= array();
			$updata['panno'] = $this->request->data['panno'];
			$updata['phoneno'] = $this->request->data['phoneno'];
			$updata['email'] = $this->request->data['email'];
			$cdatas = $this->Customers->get($custmoerId, ['contain' => []]);
			$cdatas = $this->Customers->patchEntity($cdatas, $updata);
			//print_r($updata);die();
			$this->Customers->save($cdatas);
			
            if ($this->Fds->save($fd)) {
                $this->Flash->success(__('The fd has been saved.'));
                
                //-----Auto Synch to Master FD mtables-----------
                $this->LoadModel("Mtables");
                $this->LoadModel("Mutualfunds");
                $this->loadModel('Filenames');
                $this->loadModel('Companies');
                $filenames = $this->Filenames->find('list',['keyField'=>'id','valueField'=>'fname'])->order(['fname' => 'ASC'])->toArray();
                //echo $fd_id;exit;
                //$productcompanies = $this->Fds->Companies->find('list')-> where(['associatewith' => 'product'])->where(['productid LIKE' => '%2%'])->order(['company' => 'ASC'])->toArray();
                $productcompanies = $this->Fds->Companies->find('list')-> where(['associatewith' => 'product'])->where(['productid LIKE' != 'Null'])->order(['company' => 'ASC'])->toArray();
                //pr($productcompanies);exit;

                $fdtable_fdid = $fd->id;
                $fdtable_fd = array();
                $fds = array();
                if(!empty($fdtable_fdid)){

                    $is_exists = $this->Mtables->find('all', array('conditions'=>array('Mtables.fdtable_fdid'=>$fdtable_fdid)));
                    $number = $is_exists->count();
                    if ($number > 0){
                        //$this->Flash->error(__('FD Already Exist. on Master FD '));
                        $mtables_data = $is_exists->first();
                        $fds['id'] = $mtables_data->id;
                    }

                    $fdtable_fd = $this->Fds->get($fdtable_fdid, [
                        'contain' => ['Companies','Plans']
                    ])->toArray();
                    if(!empty($fdtable_fd)){

                        $fds['csvid'] = $fdtable_fd['filenameid'];
                        $fds['customer_id'] = $fdtable_fd['customer_id'];
                        $fds['customer_name'] = $fdtable_fd['fapplicantname'];
                        $fds['pan_no'] = $fdtable_fd['panno'];
                        $fds['application_no'] = $fdtable_fd['applicationno'];
                        $fds['typeofdeposit'] = $fdtable_fd['typeofdeposit'];
                        $fds['application_date'] = $fdtable_fd['planstartdate'];
                        $fds['cheque_no'] = $fdtable_fd['chequeno'];
                        $fds['utr_fdr_no'] = $fdtable_fd['fdrno'];

                        $fds['investment_amount'] = $fdtable_fd['amount'];
                        //As per the zoom discussion 01-12-2021
                        if($fdtable_fd['typeofdeposit']=='both'){
                            if(!empty($fdtable_fd['fdramount'])){
                                $fds['investment_amount'] = $fdtable_fd['amount'] + $fdtable_fd['fdramount'];
                            }
                        }


                        $fds['period'] = $fdtable_fd['tenure'];
                        $fds['maturity_date'] = $fdtable_fd['maturitydate'];
                        //$fds['address'] = '';
                        $fds['product_name'] = $fdtable_fd['plan']['Planname'];
                        $fds['institution_name'] = !empty($productcompanies[$fdtable_fd['productcompanyid']]) ? $productcompanies[$fdtable_fd['productcompanyid']] : '' ;
                        $fds['branch_id'] = $fdtable_fd['branch_id'];
                        $fds['sub_broker'] = $fdtable_fd['subbroker_id'];
                        //$fds['email'] = '';
                        $fds['manager_id'] = $fdtable_fd['manager_id'];
                        $fds['fdtable_fdid'] = $fdtable_fd['id'];

                        $fds['despatchdate'] = $fdtable_fd['despatchdate'];


                        $fds['modifiedby'] = $this->Auth->user('id');
                        $fds['verified'] = 1;
                        $fds['manually_added'] = 1;

                        $fds = $this->Mtables->newEntity($fds);
                        //pr($fds);exit;

                        if ($this->Mtables->save($fds)) {

                        } else {
                            $this->Flash->error(__('The FD data could not be Synch to Master FD. Please, try again.'));
                        }
                    }


                }
                //pr($fdtable_fd);exit;
                //-----Auto Synch to Master FD mtables End-----------


                return $this->redirect(['action' => 'index']);
            } else {
                $this->Flash->error(__('The fd could not be saved. Please, try again.'));
            }
        }
        $users = $this->Fds->Customers->find('list')->order(['firstname' => 'ASC']);
        $subbrokers = $this->Fds->Brokers->find('list', [
            'keyField' => 'id',
            'valueField' => 'name'
        ])-> where(['role' => 'Subbroker'])->order(['name' => 'ASC']);
        $mainbrokers = $this->Fds->Brokers->find('list', [
            'keyField' => 'id',
            'valueField' => 'name'
        ])-> where(['role' => 'Mainbroker']);
        
        $Filenames = $this->Filenames->find('list', [
            'keyField' => 'id',
            'valueField' => 'fname'
        ])->order(['fname' => 'ASC']);
        
        $regionalmanagers = $this->Fds->Regionalmanagers->find('list',[
            'keyField'=>'id',
            'valueField'=>'manager'
        ])->order(['manager' => 'ASC']);
        $usercompanies = $this->Fds->Companies->find('list')-> where(['associatewith' => 'user'])->order(['company' => 'ASC']);
	$userbranches = $this->Fds->Branches->find('list', ['limit' => 200])->order(['branchname' => 'ASC']);
        //$productcompanies = $this->Fds->Companies->find('list')-> where(['associatewith' => 'product'])->order(['company' => 'ASC']);
        //$productcompanies = $this->Fds->Companies->find('list')-> where(['associatewith' => 'product'])->where(['productid LIKE' => '%2%'])->order(['company' => 'ASC']);
        $productcompanies = $this->Fds->Companies->find('list')-> where(['associatewith' => 'product'])->where(['productid LIKE' != 'Null'])->order(['company' => 'ASC']);
        $plans = $this->Fds->Plans->find('list', ['limit' => 200])->order(['planname' => 'ASC']);
        //$subbrokers = $this->Fds->Subbrokers->find('list', ['limit' => 200]);
        $banks = $this->Fds->Banks->find('list', ['limit' => 200])->order(['banks' => 'ASC']);
        $gender  = array('male'=>'Male','female'=>'Female');
        $typeofdeposit = array('fresh'=>'Fresh','renewal'=>'Renewal','both'=>'Both','onlinefresh'=>'Online Fresh','onlinerenewal'=>'Online Renewal');
        $tenure = array('12'=>'12 Month','24'=>'24 Month','36'=>'36 Month','48'=>'48 Month','60'=>'60 Month','72'=>'72 Month');
        $interestmode = array('monthly'=>'Monthly','quarterly'=>'Quarterly','halfyearly'=>'Half Yearly','yearly'=>'Annual');
        $paymentoption = array('cheque'=>'Cheque','rtgs'=>'RTGS/NEFT','utrno'=>'If RTGS/NEFT, UTR No');
        $kycdocument = array(
            'Passport'=> 'Passport',
            'PAN Card with Address proof'=> 'PAN Card with Address proof',
            'Voters Identity Card issued by Election Commission of India'=> 'Voters Identity Card issued by Election Commission of India',
            'Driving License'=> 'Driving License',
            'Job card issued by NREGA duly signed by an officer of the State Government'=> 'Job card issued by NREGA duly signed by an officer of the State Government',
            'The letter issued by Unique Identification Authority of India containing details of
Name, Address & Aadhar Number'=> 'The letter issued by Unique Identification Authority of India containing details of
Name, Address & Aadhar Number',
            'Utility Bill (Electricity/Telephone/Postpaid mobile phone/Piped Gas/Water Bill) not more than 2 months old'=> 'Utility Bill (Electricity/Telephone/Postpaid mobile phone/Piped Gas/Water Bill) not more than 2 months old',
            'Bank Account or Post Office saving bank account statement'=> 'Bank Account or Post Office saving bank account statement',
            'Others'=> 'Others (Kindly Specify)'
        );
        $this->set(compact('Filenames','fd','users','mainbrokers','productcompanies', 'usercompanies', 'userbranches', 'plans', 'subbrokers', 'banks', 'bankdetails', 'typeofdeposit','tenure','interestmode','paymentoption','kycdocument','gender','regionalmanagers'));
      
        //$this->set(compact('fd', 'companies', 'branches', 'plans', 'subbrokers', 'banks', 'bankdetails'));
        $this->set('_serialize', ['fd']);
    }

    /**
     * Delete method
     *
     * @param string|null $id Fd id.
     * @return \Cake\Network\Response|null Redirects to index.
     * @throws \Cake\Datasource\Exception\RecordNotFoundException When record not found.
     */
    public function delete($id = null)
    {
        $this->request->allowMethod(['post', 'delete']);
        $fd = $this->Fds->get($id);
        if ($this->Fds->delete($fd)) {
            
            //CHECK AND DELETE Synchronized MTABLES RECORD
            $this->loadModel('Mtables');
            $fdtable_fdid = $fd->id;
            if(!empty($fdtable_fdid)) {

                $is_exists = $this->Mtables->find('all', array('conditions' => array('Mtables.fdtable_fdid' => $fdtable_fdid)));
                $number = $is_exists->count();
                if ($number > 0) {
                    $mtables_data = $is_exists->first();
                    if($mtables_data->id > 0){
                        $this->Mtables->delete($mtables_data);
                    }
                }
            }

            $this->Flash->success(__('The fd has been deleted.'));
        } else {
            $this->Flash->error(__('The fd could not be deleted. Please, try again.'));
        }

        return $this->redirect(['action' => 'index']);
    }
    
    public function updatefds(){
		
		$all = array('all'=>'All');    
   
		if($this->Auth->user('group_id')=='2'){ 
            $branch = $this->Fds->Branches->find('list')->toArray();
			$this->Search->addFilter('branch', [
				'attributes'=>['placeholder'=>'Branch Name','label'=>'Branch Name :','class'=>'searchfrm','value' => isset($this->request->query['branch'])?$this->request->query['branch']:''],  
				'options' => $all+$this->Fds->Branches->find('list')->toArray(),
			]);
		}
		$this->Search->addFilter('fapplicantname', [
          'attributes'=>['placeholder'=>'Applicant Name','label'=>'Applicant Name :','class'=>'searchfrm','value' => isset($this->request->query['fapplicantname'])?$this->request->query['fapplicantname']:''], 'options' => $this->Fds->Customers->find('list',['keyField' => 'firstname','valueField' => 'firstname'])->order(['Customers.firstname'=>'ASC'])->group(['Customers.firstname'])->toArray(),
       ]);
		$this->Search->addFilter('datedform', [
         'attributes'=>['placeholder'=>'Start Date','label'=>'Start Date :','class'=>'searchfrm','value' => isset($this->request->query['datedform'])?$this->request->query['datedform']:''],
        ]);
        $this->Search->addFilter('datedto', [
           'attributes'=>['placeholder'=>'End Date','label'=>'End Date :','class'=>'searchfrm','value' => isset($this->request->query['datedto'])?$this->request->query['datedto']:''],
        ]);
    
         $conditions = '';  
         
     
		// if (!empty($this->request->query)) { 
        	 if((!empty($this->request->query['fapplicantname']))||(!empty($this->request->query['datedform'])) ||(!empty($this->request->query['datedto'])) ){ 
			 
			 if($this->Auth->user('group_id')=='2'){
			   if(!empty($this->request->query['branch'])){
              $branch = (($this->request->query['branch'] != '')? $this->request->query['branch'] : '');
			   }else{
				$branch = '';   
			   }			  
             } 

        $fapplicantname = $this->request->query['fapplicantname'];       
        
		$dated2 =  (($this->request->query['datedform'] != '')? $this->request->query['datedform'] : '');
	    $date_from = explode('/',$dated2);
        $datedform  = implode('-', array_reverse($date_from));
	
		$dated1= (($this->request->query['datedto'] != '')? $this->request->query['datedto']:'');
		$date_to = explode('/',$dated1);
        $datedto  = implode('-', array_reverse($date_to));
        
		if ($datedform && $datedto){
	         if($datedform == $datedto){
                $adt = new \DateTime($datedto);
                $adt->modify('+1 day');
                $datedto = $adt->format('Y-m-d');        
            }
                 $conditions = array(
        'conditions' => array(
        'and' => array(
                        array('Fds.created >= ' => $datedform,
                              'Fds.created <= ' => $datedto
                             ))));
            }
            if($this->Auth->user('group_id')=='2'){
			    if (($branch !='') && ($branch !='all')){ $conditions['conditions']['and']['Fds.branch_id'] = "$branch"; }
            }
			if($this->Auth->user('group_id')=='3'){        
            $branch_id = $this->Auth->user('branch_id');     
            $conditions['conditions']['and']['Fds.branch_id'] = "$branch_id";
            }elseif($this->Auth->user('group_id')=='4' || $this->Auth->user('group_id')=='5' || $this->Auth->user('group_id')=='6' || $this->Auth->user('group_id')=='7'){ 
            $branch_id = $this->Auth->user('branch_id');    
            $userid = $this->Auth->user('id'); 
            $conditions['conditions']['and']['Fds.branch_id'] = "$branch_id";
            $conditions['conditions']['and']['Fds.postedby'] = "$userid";
           }  
			
			
			if ($fapplicantname){ $conditions['conditions']['or']['Fds.fapplicantname'] = "$fapplicantname"; }
		    if (( $fapplicantname != '')  || ($datedform && $datedto)){ 
               $fds = $this->Fds->find('all', $conditions);
					$cnt = $fds->count();              
					if($fds){
						$total_amount = 0;                   
						 foreach ($fds as $fd){ 
							$total_amount += $fd->amount;
						 }
					} 
			   
            }else{
               $fds = $this->Fds;
			   $cnt = $fds->count();
				if($fds) {
					$total_amount = 0;
					foreach ($fds as $fd) {
						$total_amount += $fd->amount;
					}
				}
            }    
            $this->paginate = [
               'contain' => ['Companies', 'Branches','Brokers']
            ]; 
            $fds = $this->paginate($fds ,['order' => ['Fds.id' => 'DESC']]);
            $this->set(compact('fds','total_amount','cnt'));
            $this->set('_serialize', ['fds']);
       
		}else{          
		// 'contain' => ['Companies', 'Branches','Brokers', 'Plans', 'Banks']
			$this->paginate = [
            'contain' => ['Companies', 'Branches','Brokers']
			];
        $conditions['conditions']['or']['DATE(Fds.created) > '] = date('Y-m-d');
        $fds = $this->Fds->find('all', $conditions);
		$cnt = $fds->count();              
		if($fds){
			$total_amount = 0;                   
			 foreach ($fds as $fd){ 
				$total_amount += $fd->amount;
			 }
		} 	  
        $fds = $this->paginate($fds,['order' => ['Fds.id' => 'DESC']]);
        $this->set(compact('fds','total_amount','cnt'));
        $this->set('_serialize', ['fds']);
      }      
	}
	
	public function fdsupdate(){
	
		$all = array('all'=>'All');    
   
		if($this->Auth->user('group_id')=='2'){ 
            $branch = $this->Fds->Branches->find('list')->toArray();
			$this->Search->addFilter('branch', [
				'attributes'=>['placeholder'=>'Branch Name','label'=>'Branch Name :','class'=>'searchfrm','value' => isset($this->request->query['branch'])?$this->request->query['branch']:''],  
				'options' => $all+$this->Fds->Branches->find('list')->toArray(),
			]);
		}
		$this->Search->addFilter('fapplicantname', [
          'attributes'=>['placeholder'=>'Applicant Name','label'=>'Applicant Name :','class'=>'searchfrm','value' => isset($this->request->query['fapplicantname'])?$this->request->query['fapplicantname']:''], 'options' => $this->Fds->Customers->find('list',['keyField' => 'firstname','valueField' => 'firstname'])->order(['Customers.firstname'=>'ASC'])->group(['Customers.firstname'])->toArray(),
       ]);
		$this->Search->addFilter('datedform', [
         'attributes'=>['placeholder'=>'Start Date','label'=>'Start Date :','class'=>'searchfrm','value' => isset($this->request->query['datedform'])?$this->request->query['datedform']:''],
        ]);
        $this->Search->addFilter('datedto', [
           'attributes'=>['placeholder'=>'End Date','label'=>'End Date :','class'=>'searchfrm','value' => isset($this->request->query['datedto'])?$this->request->query['datedto']:''],
        ]);
    
         $conditions = '';  
         
     
		// if (!empty($this->request->query)) { 
        	 if((!empty($this->request->query['fapplicantname']))||(!empty($this->request->query['datedform'])) ||(!empty($this->request->query['datedto'])) ){ 
			 
			 if($this->Auth->user('group_id')=='2'){
			   if(!empty($this->request->query['branch'])){
              $branch = (($this->request->query['branch'] != '')? $this->request->query['branch'] : '');
			   }else{
				$branch = '';   
			   }			  
             } 

        $fapplicantname = $this->request->query['fapplicantname'];       
        
		$dated2 =  (($this->request->query['datedform'] != '')? $this->request->query['datedform'] : '');
	    $date_from = explode('/',$dated2);
        $datedform  = implode('-', array_reverse($date_from));
	
		$dated1= (($this->request->query['datedto'] != '')? $this->request->query['datedto']:'');
		$date_to = explode('/',$dated1);
        $datedto  = implode('-', array_reverse($date_to));
        
		if ($datedform && $datedto){
	         if($datedform == $datedto){
                $adt = new \DateTime($datedto);
                $adt->modify('+1 day');
                $datedto = $adt->format('Y-m-d');        
            }
                 $conditions = array(
        'conditions' => array(
        'and' => array(
                        array('Fds.created >= ' => $datedform,
                              'Fds.created <= ' => $datedto
                             ))));
            }
            if($this->Auth->user('group_id')=='2'){
			    if (($branch !='') && ($branch !='all')){ $conditions['conditions']['and']['Fds.branch_id'] = "$branch"; }
            }
			if($this->Auth->user('group_id')=='3'){        
            $branch_id = $this->Auth->user('branch_id');     
            $conditions['conditions']['and']['Fds.branch_id'] = "$branch_id";
            }elseif($this->Auth->user('group_id')=='4' || $this->Auth->user('group_id')=='5' || $this->Auth->user('group_id')=='6' || $this->Auth->user('group_id')=='7'){ 
            $branch_id = $this->Auth->user('branch_id');    
            $userid = $this->Auth->user('id'); 
            $conditions['conditions']['and']['Fds.branch_id'] = "$branch_id";
            $conditions['conditions']['and']['Fds.postedby'] = "$userid";
           }  
			
			
			if ($fapplicantname){ $conditions['conditions']['or']['Fds.fapplicantname'] = "$fapplicantname"; }
		    if (( $fapplicantname != '')  || ($datedform && $datedto)){ 
               $fds = $this->Fds->find('all', $conditions);
					$cnt = $fds->count();              
					if($fds){
						$total_amount = 0;                   
						 foreach ($fds as $fd){ 
							$total_amount += $fd->amount;
						 }
					} 
			   
            }else{
               $fds = $this->Fds;
			   $cnt = $fds->count();
				if($fds) {
					$total_amount = 0;
					foreach ($fds as $fd) {
						$total_amount += $fd->amount;
					}
				}
            }    
            $this->paginate = [
               'contain' => ['Companies', 'Branches','Brokers']
            ]; 
            $fds = $this->paginate($fds ,['order' => ['Fds.id' => 'DESC']]);
            $this->set(compact('fds','total_amount','cnt'));
            $this->set('_serialize', ['fds']);
       
		}else{          
		// 'contain' => ['Companies', 'Branches','Brokers', 'Plans', 'Banks']
			$this->paginate = [
            'contain' => ['Companies', 'Branches','Brokers']
			];
        $conditions['conditions']['or']['DATE(Fds.created) > '] = date('Y-m-d');
        $fds = $this->Fds->find('all', $conditions);
		$cnt = $fds->count();              
		if($fds){
			$total_amount = 0;                   
			 foreach ($fds as $fd){ 
				$total_amount += $fd->amount;
			 }
		} 	  
        $fds = $this->paginate($fds,['order' => ['Fds.id' => 'DESC']]);
        $this->set(compact('fds','total_amount','cnt'));
        $this->set('_serialize', ['fds']);
      }      
	}
    
    public function getuserinfo(){
        $id = $this->request->query('id');
        if (!$id) {
            throw new NotFoundException();
        }   
        //$query = $this->Fds->Customers->find('all',['conditions' => array('Customers.firstname LIKE ' => '%' . $id . '%')]);    
        $query = $this->Fds->Customers->find('all',['conditions' => array('trim(Customers.firstname) LIKE ' =>  $id),
            'group' => array('Customers.firstname','Customers.panno'),
            'order' => array('Customers.firstname ASC')
        ]);
        $data = $query->toArray();       
        $this->set(compact('data')); 
           
    }
    public function getplan(){
        $id = $this->request->query('id');
        if (!$id) {
            throw new NotFoundException();
        }   
       // $dataplan = $this->Fds->Plans->find('all',['conditions' => ['company_id' => $id, 'product_id' => '2']]); 
        $dataplan = $this->Fds->Plans->find('all',['conditions' => ['company_id' => $id, 'product_id' != 'Null']]);
        $this->set(compact('dataplan')); 
    }
    public function getmanager(){
        $id = $this->request->query('id');
        if (!$id) {
            throw new NotFoundException();
        }   
        $rmdataplan = $this->Fds->Regionalmanagers->find('all',['conditions' => ['branch_id' => $id]]); 
        $this->set(compact('rmdataplan')); 
    }
    
     // For search branchwise RM in fdmaturity
    
    public function getmanagersearch(){
        $all = array('all'=>'All');
        $normanager = array('normanager'=>'Empty');
        $id = $this->request->query('id');
        if (!$id) {
            //throw new NotFoundException();
        } 
        if ($id == 'all') {
           $rmdataplan = $this->Fds->Regionalmanagers->find('all')->order(['manager' => 'ASC']); 
        }else{
            $rmdataplan = $this->Fds->Regionalmanagers->find('all',['conditions' => ['branch_id' => $id]])->order(['manager' => 'ASC']);
        }
        $this->set(compact('rmdataplan','normanager')); 
    }
    
    public function getplaninfo(){
        $pid = $this->request->query('plan-id');
        $tenure = $this->request->query('tenure');
        if (!$pid && !$tenure) {
            throw new NotFoundException();
        } 
        $query = $this->Fds->Plans->find('all',['conditions' => ['id' => $pid,'tenure' => $tenure]]);        
        $data = $query->toArray();       
        $this->set(compact('data')); 
    }
    
    public function getbankinfo(){
        $id = $this->request->query('id');
        if (!$id) {
            throw new NotFoundException();
        }   
        $data = $this->Fds->Bankbranches->find('all',['conditions' => ['id' => $id]]); 
        $this->set(compact('data')); 
    }
    
    
     
    public function fdcolumnmapping(){

		
		$this->loadModel('Cheaders');
		$this->loadModel('Filenames');
		$all = array('all'=>'All');
        $this->Search->addFilter('fname', ['attributes'=>['placeholder'=>'Company Name','label'=>'Format Name :','class'=>'searchfrm','value'=>isset($this->request->query['fname'])?$this->request->query['fname']:''],'options' => $all+$this->Filenames->find('list',['keyField'=>'id','valueField'=>'fname'])->order(['fname' => 'ASC'])->toArray(),
      ]);
	
		$conditions = array();
	  if(isset($this->request->query['fname']))
	  { 
	    $fname = $this->request->query['fname']; 
	     if ($fname && $fname != 'all')
	   {
        $conditions['conditions']['fid'] = "$fname"; 
       }
	  }
	  
	  
		$order=$this->paginate['order'] = array('Cheaders.id'=>'asc');
		
		$csvtables = $this->paginate('Cheaders', $conditions);
			  		
        $this->set(compact('csvtables'));
		$mheader = array();
    }
    
    
    public function fdedit($id = null)
    {
		$this->LoadModel("Mtables");
		$this->LoadModel("Mutualfunds");
		$this->LoadModel("Customers");
		$fds = $this->Mtables->get($id, [
            'contain' => []
        ]);
		
		 if ($this->request->is(['patch', 'post', 'put'])) {
			 $this->request->data['modifiedby'] = $this->Auth->user('id');
			 
			//save on customer
			 
			$updata= array();
			$updata['pan_no'] = $this->request->data['pan_no'];
			$updata['phoneno'] = $this->request->data['phoneno'];
			$updata['email'] = $this->request->data['email'];
			$cdatas = $this->Customers->get($fds['customer_id'], ['contain' => []]);
			$cdatas = $this->Customers->patchEntity($cdatas, $updata);
			$this->Customers->save($cdatas);
			 
			 if(!empty($this->request->data['branch_id']) &&  $this->request->data['branch_id'] != NULL){
				
			 $this->request->data['verified'] = 1;
			 }
			
            $fds = $this->Mtables->patchEntity($fds, $this->request->data);
			
            if ($this->Mtables->save($fds)) {
                $this->Flash->success(__('The FD data has been Updated.'));

                return $this->redirect(['action' => 'fdimportdata']);
            } else {
                $this->Flash->error(__('The FD data could not be Updated. Please, try again.'));
            }
        }
		 $subbrokers = $this->Fds->Brokers->find('list', ['keyField' => 'id','valueField' => 'name'])-> where(['role' => 'Subbroker']);
		 $branches = $this->Mutualfunds->Branches->find('list', ['limit' => 200]);
		 //$paymentmode  = array('cheque'=>'Cheque','dd'=>'DD','rtgs'=>'RTGS','renewal'=>'Renewal');
		 $this->set(compact('fds','branches','subbrokers'));
		 //$this->request->data = $fds;
		
    }
    
    /*public function fdimportdata(){
        
		
		$this->loadModel('Filenames');
		$this->loadModel('Mtables');
		$this->loadModel('Branches');
		$all = array('all'=>'All');
		
		$this->Search->addFilter('keyword', ['attributes' => ['placeholder' => 'Keyword', 'label' => 'Keyword :', 'class' => 'searchfrm']]);
		
        $this->Search->addFilter('fname', ['attributes'=>['placeholder'=>'Format Name','label'=>'Company Name :','class'=>'searchfrm','value'=>isset($this->request->query['fname'])?$this->request->query['fname']:''],'options' => $all+$this->Filenames->find('list',['keyField'=>'id','valueField'=>'fname'])->order(['fname' => 'ASC'])->toArray(),
      ]);
      
       $this->Search->addFilter('datedform', ['attributes'=>['placeholder'=>'Start Date (Maturity)','label'=>'Start Date :','class'=>'searchfrm','value' => isset($this->request->query['datedform'])?$this->request->query['datedform']:''],
        ]);
        
        $this->Search->addFilter('datedto', [
           'attributes'=>['placeholder'=>'End Date (Maturity)','label'=>'End Date :','class'=>'searchfrm','value' => isset($this->request->query['datedto'])?$this->request->query['datedto']:''],
        ]);
        
        $branches = $this->Branches->find('list', ['limit' => 200]);
	
	  $conditions = array();
	  
	  if (isset($this->request->query['keyword'])) {
            $query = $this->request->query['keyword'];
            $conditions['conditions']['or']['Mtables.customer_name LIKE'] = "%$query%";
        }
	  
	  if((!empty($this->request->query['datedform'])) ||(!empty($this->request->query['datedto']))){ 
			$dated2 =  (($this->request->query['datedform'] != '')? $this->request->query['datedform'] : '');
			$date_from = explode('/',$dated2);
			$datedform  = implode('-', array_reverse($date_from));
			$dated1= (($this->request->query['datedto']!='')? $this->request->query['datedto']:'');
			$date_to = explode('/',$dated1);
			$datedto  = implode('-', array_reverse($date_to));	
			if ($datedform && $datedto){
				if($datedform == $datedto){
					$adt = new DateTime($datedto);
					$adt->modify('+1 day');
					$datedto = $adt->format('Y-m-d');        
				}
			$conditions = array('conditions' => array('and' => array(array('Mtables.maturity_date >= ' => $datedform,'Mtables.maturity_date <= ' => $datedto))));
			} 
        }else{	
    	  //$datedfrom  = date('Y-m-01', strtotime('+1 month'));
    	  //$datedto  = 	date('Y-m-t', strtotime('+1 month'));
    	  //$conditions = array('conditions' => array('and' => array(array('Mtables.maturity_date >= ' => $datedfrom,'Mtables.maturity_date <= ' => $datedto))));
		}
	  
	  
	  if(isset($this->request->query['fname'])){
	  $fname = $this->request->query['fname']; 
	  if ($fname && $fname != 'all'){ $conditions['conditions']['or']['csvid'] = "$fname"; }
	  }
	  $conditions['conditions']['verified'] = 0;
	  $userid = $this->Auth->user('id');
		$order=$this->paginate['order'] = array('Mtables.id'=>'desc');
		$mdatas = $this->paginate('Mtables', $conditions);
        $this->set(compact('mdatas', 'branches','userid'));	
    }*/
    
     public function fdimportdata(){
        
		
		$this->loadModel('Filenames');
		$this->loadModel('Mtables');
		$this->loadModel('Branches');
		$all = array('all'=>'All');
		
		$this->Search->addFilter('keyword', ['attributes' => ['placeholder' => 'Keyword', 'label' => 'Keyword :', 'class' => 'searchfrm serachfrm_clearable','value'=>isset($this->request->query['keyword'])?$this->request->query['keyword']:'']]);
		
		$this->Search->addFilter('pan_no', ['attributes'=>['placeholder'=>'Pan No','label'=>'Pan No :','class'=>'searchfrm serachfrm_clearable','value'=>!empty($this->request->query['pan_no'])?$this->request->query['pan_no']:'']]);
		
        $this->Search->addFilter('fname', ['attributes'=>['placeholder'=>'Format Name','label'=>'Company Name :','class'=>'searchfrm','value'=>isset($this->request->query['fname'])?$this->request->query['fname']:''],'options' => $all+$this->Filenames->find('list',['keyField'=>'id','valueField'=>'fname'])->order(['fname' => 'ASC'])->toArray(),
      ]);
      
       $this->Search->addFilter('datedform', ['attributes'=>['placeholder'=>'Start Date (Maturity)','label'=>'Start Date :','class'=>'searchfrm','value' => isset($this->request->query['datedform'])?$this->request->query['datedform']:''],
        ]);
        
        $this->Search->addFilter('datedto', [
           'attributes'=>['placeholder'=>'End Date (Maturity)','label'=>'End Date :','class'=>'searchfrm','value' => isset($this->request->query['datedto'])?$this->request->query['datedto']:''],
        ]);
        
        $this->Search->addFilter('inv_datedform', [
            'attributes'=>['placeholder'=>'Investment Start Date','label'=>'Investment Start Date :','class'=>'searchfrm','value' => isset($this->request->query['inv_datedform'])?$this->request->query['inv_datedform']:''],
        ]);
        
        $this->Search->addFilter('inv_datedto', [
            'attributes'=>['placeholder'=>'Investment End Date','label'=>'Investment End Date :','class'=>'searchfrm','value' => isset($this->request->query['inv_datedto'])?$this->request->query['inv_datedto']:''],
        ]);
        
        $branches = $this->Branches->find('list', ['limit' => 200]);
	
	  $conditions = array();
	  
	  
	  
	  if((!empty($this->request->query['datedform'])) ||(!empty($this->request->query['datedto']))){ 
			$dated2 =  (($this->request->query['datedform'] != '')? $this->request->query['datedform'] : '');
			$date_from = explode('/',$dated2);
			$datedform  = implode('-', array_reverse($date_from));
			$dated1= (($this->request->query['datedto']!='')? $this->request->query['datedto']:'');
			$date_to = explode('/',$dated1);
			$datedto  = implode('-', array_reverse($date_to));	
			if ($datedform && $datedto){
				if($datedform == $datedto){
					$adt = new \DateTime($datedto);
					$adt->modify('+1 day');
					$datedto = $adt->format('Y-m-d');        
				}
			$conditions = array('conditions' => array('and' => array(array('Mtables.maturity_date >= ' => $datedform,'Mtables.maturity_date <= ' => $datedto))));
			} 
        }else{
            $dated1 = '';
            $dated2 = '';
            $datedform  = '';
            $datedto  = '';

    	  //$datedfrom  = date('Y-m-01', strtotime('+1 month'));
    	  //$datedto  = 	date('Y-m-t', strtotime('+1 month'));
    	  //$conditions = array('conditions' => array('and' => array(array('Mtables.maturity_date >= ' => $datedfrom,'Mtables.maturity_date <= ' => $datedto))));
		}
		
		if((!empty($this->request->query['inv_datedform'])) ||(!empty($this->request->query['inv_datedto']))){ 
			$invdated2 =  (($this->request->query['inv_datedform'] != '')? $this->request->query['inv_datedform'] : '');
			$invdate_from = explode('/',$invdated2);
			$invdatedform  = implode('-', array_reverse($invdate_from));
			$invdated1= (($this->request->query['inv_datedto']!='')? $this->request->query['inv_datedto']:'');
			$invdate_to = explode('/',$invdated1);
			$invdatedto  = implode('-', array_reverse($invdate_to));	
			if ($invdatedform && $invdatedto){
				if($invdatedform == $invdatedto){
					$invadt = new \DateTime($invdatedto);
					$invadt->modify('+1 day');
					$invdatedto = $invadt->format('Y-m-d');        
				}
			$conditions['conditions'][] = array('and' => array(array('Mtables.application_date >= ' => $invdatedform,'Mtables.application_date <= ' => $invdatedto)));
			} 
        }
		
		if (isset($this->request->query['keyword'])) {
            $query = $this->request->query['keyword'];
            $keyword = $this->request->query['keyword'];
            $keywords = explode(' ',$keyword);
            if(count($keywords) > 1){
                $conditions['conditions']['Mtables.customer_name LIKE'] = "$keyword%";
            }else{
                $conditions['conditions']['Mtables.customer_name LIKE'] = "%$keyword%";
            }
        }else{
            $keyword = '';
        }
        
        if(isset($this->request->query['pan_no'])){
            $applicant_pan = $this->request->query['pan_no'];
            if(!empty($this->request->query['pan_no'])){
                $conditions['conditions']['pan_no'] = $applicant_pan;
            }
        }
	  
	  
	  if(!empty($this->request->query['fname'])){
	  $fname = $this->request->query['fname']; 
      $comname = $this->Filenames->find('all',['conditions'=>['Filenames.id' => $fname]])->toArray();
      $companyname = $comname[0]['fname'];
	  if ($fname && $fname != 'all'){ 
	      //$conditions['conditions']['or']['csvid'] = "$fname";
	      $conditions['conditions']['csvid'] = $fname;
	  }
	  }else{
        $companyname = '';
      }

	  $conditions['conditions']['verified'] = 0;
      $userid = $this->Auth->user('id');
		//$order=$this->paginate['order'] = array('Mtables.id'=>'desc');
		$order=$this->paginate['order'] = array('Mtables.application_date'=>'asc');
		$mdatas = $this->paginate('Mtables', $conditions);
		$filenames = $this->Filenames->find('list',['keyField'=>'id','valueField'=>'fname'])->order(['fname' => 'ASC'])->toArray();
        $this->set(compact('mdatas', 'branches','userid','datedform','datedto','dated2','dated1','companyname','keyword','filenames'));
    }
    
    
    public function fdimportsetup(){
		
		define('FILECSV',WWW_ROOT.'files'.DS.'excelcsv');
		
		
		$uId=$this->Auth->user('id'); 
		$this->loadModel('Filenames');
		if($this->request->is('post')){
            			
			$newfilename = $this->request->data['fname'];
			
			$is_exists = $this->Filenames->find('all', array('conditions'=>array('LOWER(fname)'=>strtolower($newfilename))));
			$number = $is_exists->count();
			if ($number > 0){
				 $this->Flash->error(__('Format Name Already Exist.'));
			}
			else
			{
				
			$allowedExts = array("csv");
				
			//$this->request->data['created'] = new Time(date("Y-m-d H:i:s"));
			$upfilename=$this->request->data['excel']['name'];
			$data = explode(".", $upfilename);
			$extension = end($data);
			if(in_array($extension, $allowedExts)){
			$upfilename = $uId.'-'.time().$this->request->data['excel']['name'];
			$movefile = move_uploaded_file($this->request->data['excel']['tmp_name'], FILECSV.'/'.$upfilename);
			}else {
				$this->Flash->error(__('Please Upload only csv file.'));
				$this->redirect(array('action' => 'index'));
			}
			if($movefile){
				$filename = $this->Filenames->newEntity();
				$this->request->data['user_id'] = $this->Auth->user('id'); 
				$filename = $this->Filenames->patchEntity($filename, $this->request->data);
				$this->Filenames->save($filename);
				$conditions['conditions']['and']['Filenames.fname'] = "$newfilename";
				$fdata = $this->Filenames->find('all', $conditions);
                $fdata = $fdata->first();
				$fid = $fdata['id'];
				$return = $this->Filenames->importcsv($model = 'Report',$upfilename,$uId,$fid);
				if(isset($return) && $return == 1){
				$this->Flash->success(__('New company excel upload format setup successfully.'));
				}
				$this->redirect('/fds/fdimportsetup');
			}
		}
	}
	}
    
    
    public function fdmapping($id = null)
	{
		
		$this->loadModel('Cheaders');
		$cheader = $this->Cheaders->get($id);
		$datas = $this->Cheaders->get($id, [
            'contain' => []
        ]);
		$iddata = $datas['fid'];
		$dataid = $this->Fds->listdata($iddata);
		
        if ($this->request->is(['post', 'put'])){
				$this->Cheaders->patchEntity($cheader, $this->request->data);
				$this->Cheaders->save($cheader);
				  $this->Flash->success(__('Field Mapped Successfully.'));
				  $this->redirect(['action' => 'fdcolumnmapping']);
    		}	
			  $this->set(compact('dataid'));
	}
    
    public function fdupload(){
	    define('FILECSV',WWW_ROOT.'files'.DS.'excelcsv');
	    $this->loadModel('Filenames');
		$uId=$this->Auth->user('id'); 
		$formatof = $this->Filenames->find('list',['keyField'=>'id','valueField'=>'fname'])->order(['fname' => 'ASC'])->toArray();
		if($this->request->is('post')){		
			$allowedExts = array("csv");
			$fid  = $this->request->data['formatof'];
			$upfilename=$this->request->data['excel']['name'];
			$data = explode(".", $upfilename);
			$extension = end($data);
			if(in_array($extension, $allowedExts)){
			     $upfilename = $uId.'-'.time().$this->request->data['excel']['name'];
				 $movefile = move_uploaded_file($this->request->data['excel']['tmp_name'], FILECSV.'/'.$upfilename);
			}else {
				$this->Flash->error(__('Please Upload only csv file.'));
				$this->redirect(array('action' => 'fdupload'));
			}
			if($movefile){
			$this->loadModel('Mtables');
			
			$return = $this->Fds->importcsvdata($model = 'Mtables',$upfilename,$uId,$fid);
			if(isset($return))
			{
			$this->Flash->success(__('FD Data Imported Successfully.'));
			}
			$this->redirect(['action' => 'fdimportdata']);
			}
		}
		$this->set(compact('formatof'));
    }
    
    /*public function fdverifieddata()
	{
		
		$this->loadModel('Filenames');
		$this->loadModel('Mtables');
		$this->loadModel('Users');
		$all = array('all'=>'All');
        $this->Search->addFilter('fname', ['attributes'=>['placeholder'=>'Format Name','label'=>'Company Name :','class'=>'searchfrm','value'=>isset($this->request->query['fname'])?$this->request->query['fname']:''],'options' => $all+$this->Filenames->find('list',['keyField'=>'id','valueField'=>'fname'])->order(['fname' => 'ASC'])->toArray(),
      ]);
	
	  $conditions = array();
	  if(isset($this->request->query['fname'])){
	  $fname = $this->request->query['fname']; 
	  if ($fname && $fname != 'all'){ $conditions['conditions']['csvid'] = "$fname"; }
	  }
	  
	  if($this->Auth->user('group_id') > 2)
	  {
		   
           $conditions['conditions']['branch_id'] = $this->Auth->user('branch_id'); 
		   
	  }
	  
	  $conditions['conditions']['verified'] = 1;
		$order=$this->paginate['order'] = array('Mtables.id'=>'desc');
		$mdatas = $this->paginate('Mtables', $conditions);
        $this->set(compact('mdatas'));	
	}*/
	
	 public function fdverifieddata()
	{
	    
	    
		$this->loadModel('Filenames');
		$this->loadModel('Mtables');
		$this->loadModel('Users');
		$all = array('all'=>'All');
		$this->Search->addFilter('keyword', ['attributes' => ['placeholder' => 'Keyword', 'label' => 'Keyword :', 'class' => 'searchfrm serachfrm_clearable','value'=>isset($this->request->query['keyword'])?$this->request->query['keyword']:'']]);
		
		$this->Search->addFilter('pan_no', ['attributes'=>['placeholder'=>'Pan No','label'=>'Pan No :','class'=>'searchfrm serachfrm_clearable','value'=>!empty($this->request->query['pan_no'])?$this->request->query['pan_no']:'']]);
		
        $this->Search->addFilter('fname', ['attributes'=>['placeholder'=>'Format Name','label'=>'Company Name :','class'=>'searchfrm','value'=>isset($this->request->query['fname'])?$this->request->query['fname']:''],'options' => $all+$this->Filenames->find('list',['keyField'=>'id','valueField'=>'fname'])->order(['fname' => 'ASC'])->toArray(),
      ]);
	
	  $conditions = array();
	  if(isset($this->request->query['fname'])){
	  $fname = $this->request->query['fname']; 
      $comname = $this->Filenames->find('all',['conditions'=>['Filenames.id' => $fname]])->toArray();
      $companyname = $comname[0]['fname'];
	  if ($fname && $fname != 'all'){ $conditions['conditions']['csvid'] = "$fname"; }
	  }else{
        $companyname = '';
      }
	  
	  if($this->Auth->user('group_id') > 2)
	  {
		   
           $conditions['conditions']['branch_id'] = $this->Auth->user('branch_id'); 
		   
	  }
	  
	   
	  if (isset($this->request->query['keyword'])) {
            $query = $this->request->query['keyword'];
            $keyword = $this->request->query['keyword'];
            $keywords = explode(' ',$keyword);
            if(count($keywords) > 1){
                $conditions['conditions']['Mtables.customer_name LIKE'] = "$keyword%";
            }else{
                $conditions['conditions']['Mtables.customer_name LIKE'] = "%$keyword%";
            }
        }else{
            $keyword = '';
        }
        
        if(isset($this->request->query['pan_no'])){
            $applicant_pan = $this->request->query['pan_no'];
            if(!empty($this->request->query['pan_no'])){
                $conditions['conditions']['pan_no'] = $applicant_pan;
            }
        }
	  
	  $conditions['conditions']['verified'] = 1;
		//$order=$this->paginate['order'] = array('Mtables.id'=>'desc');
		$order=$this->paginate['order'] = array('Mtables.application_date'=>'asc');
		$mdatas = $this->paginate('Mtables', $conditions);
        $this->set(compact('mdatas','companyname','keyword'));	
	}
    
    public function mastercolumn(){
		
		$this->loadModel('Mheaders');
		
		$conditions = array(); 
		$order=$this->paginate['order'] = array('Mheaders.mname'=>'asc');
		$mheaders = $this->paginate('Mheaders', $conditions);	  
        $this->set(compact('mheaders'));
    }
    
    
    function fdexceldata()
	{
	$this->loadModel('Filenames');
    $filenames = $this->Filenames->find('list',['keyField'=>'id','valueField'=>'fname'])->order(['fname' => 'ASC'])->toArray();	
	
	$fileName = "fdexceldata_".date("d-m-y:h:s").".xls";
	$conditions = array();
	  if(isset($this->request->query['fname'])){
	  $fname = $this->request->query['fname']; 
	  if ($fname && $fname != 'all'){ $conditions['conditions']['csvid'] = "$fname"; }
	  }
	  
	  if (isset($this->request->query['keyword'])) {
            $query = $this->request->query['keyword'];
            $keyword = $this->request->query['keyword'];
            $keywords = explode(' ',$keyword);
            if(count($keywords) > 1){
                $conditions['conditions']['Mtables.customer_name LIKE'] = "$keyword%";
            }else{
                $conditions['conditions']['Mtables.customer_name LIKE'] = "%$keyword%";
            }
      }else{
          $keyword = '';
      }
      
        if(isset($this->request->query['pan_no'])){
            $applicant_pan = $this->request->query['pan_no'];
            if(!empty($this->request->query['pan_no'])){
                $conditions['conditions']['pan_no'] = $applicant_pan;
            }
        }
	  
	  if($this->Auth->user('group_id') > 2)
	  {
		   
           $conditions['conditions']['branch_id'] = $this->Auth->user('branch_id'); 
		   
	  }
	  
	    $conditions['conditions']['verified'] = 1;
		//$order=$this->paginate['order'] = array('Mtables.id'=>'desc');
		$order=$this->paginate['order'] = array('Mtables.application_date'=>'asc');
		$mdatas = $this->paginate('Mtables', $conditions);
		
		 $datafds = $mdatas->toArray(); 
		 
		$mbranches = TableRegistry::get('Branches');
		$musers = TableRegistry::get('Users');
		$brokers = TableRegistry::get('Brokers');
     
        //$headerRow = array("Sr No","Client Name", "PAN NO",'Application Date','Payment Mode','Cheque No','UTR/FDR No','Principle Investment','Tenure','Maturity Date','Address','Product name','Company Name','Investment Branch','Branch User','Sub Broker Name');
        $headerRow = array('Sr No', 'Investor Name', 'Pan No','Investment Date' ,'Investment Amount', 'Mobile No', 'Tenure', 'Maturity Date','Institution Name','Company Name');
        $j=1;
            for($i=0; $i< count($datafds); $i++){
				if($datafds[$i]->branch_id > 0){
                            $branchid = $mbranches->find()->where(['id' => $datafds[$i]->branch_id]);
                            $data1 = $branchid->toArray();
                            $branchname = $data1[0]->branchname;
                        }else{
                            $branchname = '';
                        }
				if($datafds[$i]->modifiedby > 0){
                            $modifiedid = $musers->find()->where(['id' => $datafds[$i]->modifiedby]);
                            $data1 = $modifiedid->toArray();
                            $modifiedname = $data1[0]->firstname;
                        }else{
                            $modifiedname = '';
                        }
				if($datafds[$i]->sub_broker > 0){
                            $brokerid = $brokers->find()->where(['id' => $datafds[$i]->sub_broker]);
                            $data1 = $brokerid->toArray();
                            $brokername = $data1[0]->name;
                        }else{
                            $brokername = '';
                        }
                        
                $format_of = !empty($filenames[$datafds[$i]->csvid]) ? $filenames[$datafds[$i]->csvid] :  "";
				       
                //$data[] = [$j,$datafds[$i]->customer_name,$datafds[$i]->pan_no,$datafds[$i]->application_date,$datafds[$i]->payment_mode,$datafds[$i]->cheque_no,$datafds[$i]->utr_fdr_no,$datafds[$i]->principle_investment,$datafds[$i]->period,$datafds[$i]->maturity_date,$datafds[$i]->address,$datafds[$i]->product_name,$datafds[$i]->company_name,$branchname,$modifiedname,$brokername];
                $data[] = [$j, $datafds[$i]->customer_name, $datafds[$i]->pan_no, $datafds[$i]->application_date, $datafds[$i]->investment_amount,$datafds[$i]->mobileno, $datafds[$i]->period, $datafds[$i]->maturity_date, $datafds[$i]->institution_name, $format_of];
            $j++;
                } 
               
        $this->ExportXls->export($fileName, $headerRow, $data);
	}

	public function fdformatlist()
	{
	
	}
	
	public function fdmaturity()
	{
		$this->loadModel('Filenames');
		$this->loadModel('Mtables');
		$this->loadModel('Users');
		$this->loadModel('Branches');
		$this->loadModel('Regionalmanagers');
		$this->loadModel('Brokers');
		$all = array('all'=>'All');
		$nobranch = array('nobranch'=>'Empty');
		$normanager = array('normanager'=>'Empty');
		$noinvestoremark = array('noinvestoremark'=>'Empty');
        //$this->Search->addFilter('customer_name', ['attributes'=>['placeholder'=>'Applicant Name','label'=>'Applicant Name :','class'=>'searchfrm','value'=>isset($this->request->query['customer_name'])?$this->request->query['customer_name']:''],'options' => $all+$this->Mtables->find('list',['keyField'=>'customer_name','valueField'=>'customer_name'])->order(['customer_name' => 'ASC'])->group(['Mtables.customer_name'])->toArray(),
         //   ]);
            
         $this->Search->addFilter('customer_name',['attributes'=>['placeholder'=>'Investor Name','label'=>'Investor Name','class'=>'serachfrm serachfrm_clearable','id'=>'autocomplete1','value'=>isset($this->request->query['customer_name'])?$this->request->query['customer_name']:''],]);   
		 $custname = $this->Mtables->find('list',['valueField'=>'customer_name'])->order(['customer_name' => 'ASC'])->group(['Mtables.customer_name'])->toArray();
		 $custname = array_values($custname);
		
		 $this->Search->addFilter('minorname', [
               'attributes'=>['placeholder'=>'Minor Name','label'=>'Minor Name :','class'=>'searchfrm','value' => isset($this->request->query['minorname'])?$this->request->query['minorname']:''], 'options' => $all+$this->Fds->find('list',['keyField' => 'minorname','valueField' => 'minorname'])->toArray(),
              ]);
		
        $this->Search->addFilter('fname', ['attributes'=>['placeholder'=>'Company Name','label'=>'Company Name :','class'=>'searchfrm','value'=>isset($this->request->query['fname'])?$this->request->query['fname']:''],'options' => $all+$this->Filenames->find('list',['keyField'=>'id','valueField'=>'fname'])->order(['fname' => 'ASC'])->toArray(),
      ]);
      
      $this->Search->addFilter('institution_name', ['attributes'=>['placeholder'=>'Institution Name','label'=>'Institution Name :','class'=>'searchfrm','value'=>!empty($this->request->query['institution_name'])?$this->request->query['institution_name']:'all'],'options' => $all+$this->Mtables->find('list',['keyField'=>'institution_name','valueField'=>'institution_name'])->where(['institution_name !=' => '0'])->order(['scheme_name' => 'ASC'])->group(['Mtables.institution_name'])->toArray(),
        ]);
      
     
      
        $branchCond = $rmBranchCond = array();
		if($this->Auth->user('group_id')>2){        
            $branch_id = $this->request->query['branch'] = $this->Auth->user('branch_id'); 
			$branchCond = ['Branches.id' => $branch_id ];
			$rmBranchCond = ['Regionalmanagers.branch_id' => $branch_id ];
		}else{
			 $this->Search->addFilter('branch', [
            'attributes'=>['placeholder'=>'Branch Name','label'=>'Branch Name :','class'=>'searchfrm','value' => isset($this->request->query['branch'])?$this->request->query['branch']:''],
            'options' => $all+$nobranch+$this->Fds->Branches->find('list')->toArray(),
        ]);
		}
		
		$this->Search->addFilter('regionalmanager', [
        'attributes'=>['placeholder'=>'RM Name','label'=>'RM Name :','class'=>'searchfrm','value' => isset($this->request->query['regionalmanager'])?$this->request->query['regionalmanager']:''],
        'options' => $all+$normanager+$this->Fds->Regionalmanagers->find('list',['valueField'=>'manager', 'conditions' => $rmBranchCond])->order(['manager' => 'ASC'])->toArray(),
        ]);
		
	    $this->Search->addFilter('name', ['attributes'=>['placeholder'=>'Subbroker Name','label'=>' Subbroker Name :','class'=>'searchfrm','value'=>isset($this->request->query['name'])?$this->request->query['name']:''],'options' => $all+$this->Brokers->find('list',['keyField'=>'id','valueField'=>'name'])->order(['name' => 'ASC'])->where(['role' => 'Subbroker'])->toArray(),
        ]);
        
	    $invremark = array('Pre Maturity'=>'Pre Maturity','Renewal'=>'Renewed','Repayment'=>'Repayment','Out of Station'=>'Out of Station','Not Interested'=>'Not Interested','Informed'=>'Informed','Remind Later'=>'Remind Later');
        $this->Search->addFilter('remark', [
            'attributes'=>['placeholder'=>'Investor Remark','label'=>'Investor Remark :','class'=>'searchfrm','value' => isset($this->request->query['remark'])?$this->request->query['remark']:''],
            'options' => $all+$noinvestoremark+ $invremark
        ]);
	    
    	$emaillstatus = array(1=>'Sent', 2=>'Unsent');
		$this->Search->addFilter('emailstatus', [
            'attributes'=>['placeholder'=>'Email Status','label'=>'Email Status :','class'=>'searchfrm','value' => isset($this->request->query['emailstatus'])?$this->request->query['emailstatus']:''],
            'options' => $all+$emaillstatus,
        ]);
		
		$whatsappstatus = array(1=>'Sent', 2=>'Unsent');
		$this->Search->addFilter('whatsappstatus', [
            'attributes'=>['placeholder'=>'Whatsapp Status','label'=>'Whatsapp Status :','class'=>'searchfrm','value' => isset($this->request->query['whatsappstatus'])?$this->request->query['whatsappstatus']:''],
            'options' => $all+$whatsappstatus,
        ]);
        
	  $conditions = array();
	  
	  if((!empty($this->request->query['datedform'])) ||(!empty($this->request->query['datedto']))){ 
			$dated2 =  (($this->request->query['datedform'] != '')? $this->request->query['datedform'] : '');
			$date_from = explode('/',$dated2);
			$datedform  = implode('-', array_reverse($date_from));
			$dated1= (($this->request->query['datedto']!='')? $this->request->query['datedto']:'');
			$date_to = explode('/',$dated1);
			$datedto  = implode('-', array_reverse($date_to));	
			if ($datedform && $datedto){
				if($datedform == $datedto){
					$adt = new \DateTime($datedto);
					//$adt->modify('+1 day');
					$datedto = $adt->format('Y-m-d');        
				}
			$conditions = array('conditions' => array('and' => array(array('Mtables.maturity_date >= ' => $datedform,'Mtables.maturity_date <= ' => $datedto))));
			} 
        }else{
            if((empty($this->request->query['inv_datedform'])) ||(empty($this->request->query['inv_datedto']))){
                 
        $dated2 = date('01/m/Y', strtotime('+1 month'));
        $datedform = date('Y-m-01', strtotime('+1 month'));	
	  $datedfrom  = date('Y-m-01', strtotime('+1 month'));
      $dated1 = date('t/m/Y', strtotime('+1 month'));
	  $datedto  = 	date('Y-m-t', strtotime('+1 month'));
	  $conditions = array('conditions' => array('and' => array(array('Mtables.maturity_date >= ' => $datedfrom,'Mtables.maturity_date <= ' => $datedto))));
	  $this->request->query['datedform']= $dated2;
      $this->request->query['datedto'] = $dated1;
            } 
           
		}
		
	if((!empty($this->request->query['inv_datedform'])) ||(!empty($this->request->query['inv_datedto']))){ 
			$invdated2 =  (($this->request->query['inv_datedform'] != '')? $this->request->query['inv_datedform'] : '');
			$invdate_from = explode('/',$invdated2);
			$invdatedform  = implode('-', array_reverse($invdate_from));
			$invdated1= (($this->request->query['inv_datedto']!='')? $this->request->query['inv_datedto']:'');
			$invdate_to = explode('/',$invdated1);
			$invdatedto  = implode('-', array_reverse($invdate_to));	
			if ($invdatedform && $invdatedto){
				if($invdatedform == $invdatedto){
					$invadt = new \DateTime($invdatedto);
					//$adt->modify('+1 day');
					$invdatedto = $invadt->format('Y-m-d');        
				}
			$conditions['conditions'][] = array('and' => array(array('Mtables.application_date >= ' => $invdatedform,'Mtables.application_date <= ' => $invdatedto)));
			} 
        }	
		
	if(isset($this->request->query['minorname']) && !empty($this->request->query['minorname'])){
        $minorname = $this->request->query['minorname'];
		if ($minorname && $minorname != 'all'){
		 $minornamefd = $this->Fds->find('all',['conditions'=>['Fds.minorname Like' => '%' . $minorname]])->toArray();//echo'<pre>';print_r($minornamefd); die();
		 $fdId = $minornamefd[0]->id;
		 $conditions['conditions']['and']['fdtable_fdid'] = $fdId;
		}
    }else{
        $minorname = 'all';
    }	
	 
	  if(isset($this->request->query['customer_name']) && !empty($this->request->query['customer_name'])){
                $customer_name = $this->request->query['customer_name']; 
                if ($customer_name && $customer_name != 'all'){
                 //$conditions['conditions']['customer_name'] = $customer_name;
                 $applicant_pan_no = $this->Mtables->find('all',['conditions'=>['Mtables.customer_name' => $customer_name]])->toArray();
				 $applicant_pan = $applicant_pan_no[0]['pan_no'];
				 $conditions['conditions']['or']['pan_no'] = $applicant_pan;
				 //$conditions['conditions']['and']['customer_name'] = $customer_name;
				 $conditions['conditions']['or']['customer_name LIKE'] = '%'.$customer_name.'%';
                }
                }else{
                    $customer_name = 'all';
                }
	  if(isset($this->request->query['fname']) && !empty($this->request->query['fname'])){
	  $fname = $this->request->query['fname']; 
      $comname = $this->Filenames->find('all',['conditions'=>['Filenames.id' => $fname]])->toArray();
      $companyname = $comname[0]['fname'];
	  if ($fname && $fname != 'all'){ $conditions['conditions']['csvid'] = "$fname"; }
	  }else{
                $companyname = '';
                
            }
	  
	  if($this->Auth->user('group_id') > 2)
	  {
		   
           //$conditions['conditions']['branch_id'] = $this->Auth->user('branch_id'); 
		   
	  }
	  if(!empty($this->request->query['institution_name']) && $this->request->query['institution_name']!= 'all'){
        $conditions['conditions']['institution_name LIKE'] = '%'.$this->request->query['institution_name'].'%';
      }
      
      if(!empty($this->request->query['branch']) && $this->request->query['branch']!= 'all' && $this->request->query['branch']!= 'nobranch'){
        $conditions['conditions']['branch_id'] = $this->request->query['branch'];
      }
      if(!empty($this->request->query['branch']) && $this->request->query['branch'] == 'nobranch'){
        $conditions['conditions']['branch_id IS'] = null;
      }
     
     if(!empty($this->request->query['name']) && $this->request->query['name']!= 'all'){
        $conditions['conditions']['sub_broker'] = $this->request->query['name'];
      }
     
     // for RM search
      
      if(!empty($this->request->query['regionalmanager']) && $this->request->query['regionalmanager']!= 'all' && $this->request->query['regionalmanager']!= 'normanager'){
        $conditions['conditions']['manager_id'] = $this->request->query['regionalmanager'];
      }
	  if(!empty($this->request->query['regionalmanager']) && $this->request->query['regionalmanager'] == 'normanager'){
        $conditions['conditions']['manager_id'] = 0;
      }
     
     if(!empty($this->request->query['remark']) && $this->request->query['remark']!= 'all' && $this->request->query['remark']!= 'noinvestoremark'){
        //print_r($this->request->query);
        if($this->request->query['remark'] == 'Pre Maturity'){
         $conditions['conditions']['remark LIKE'] = '%'.$this->request->query['remark'].'%';   
        }else if($this->request->query['remark'] == 'Renewal'){
            $conditions['conditions']['remark LIKE'] = '%'.$this->request->query['remark'].'%';   
        }else if($this->request->query['remark'] == 'Repayment'){
            $conditions['conditions']['remark LIKE'] = '%'.$this->request->query['remark'].'%';   
        }else if($this->request->query['remark'] == 'Out of Station'){
            $conditions['conditions']['remark LIKE'] = '%'.$this->request->query['remark'].'%';   
        }else if($this->request->query['remark'] == 'Not Interested'){
            $conditions['conditions']['remark LIKE'] = '%'.$this->request->query['remark'].'%';   
        }else if($this->request->query['remark'] == 'Informed'){
            $conditions['conditions']['remark LIKE'] = '%'.$this->request->query['remark'].'%';   
        }else if($this->request->query['remark'] == 'Remind Later'){
            $conditions['conditions']['remark LIKE'] = '%'.$this->request->query['remark'].'%';   
        }else{
         $conditions['conditions']['remark IS'] = null;   
        }
    }
    if(!empty($this->request->query['remark']) && $this->request->query['remark'] == 'noinvestoremark'){
        $conditions['conditions']['remark IS'] = null;
    }
    
    
    if(!empty($this->request->query['emailstatus']) && $this->request->query['emailstatus']!= 'all'){
	   if($this->request->query['emailstatus'] == 1){
		$conditions['conditions']['emailupdatedby >='] = $this->request->query['emailstatus'];   
	   }else{
		$conditions['conditions']['emailupdatedby IS'] = null;   
	   }
    }
	 
	if(!empty($this->request->query['whatsappstatus']) && $this->request->query['whatsappstatus']!= 'all'){
		   if($this->request->query['whatsappstatus'] == 1){
			$conditions['conditions']['whatsapp_sentby >='] = $this->request->query['whatsappstatus'];   
		   }else{
			$conditions['conditions']['whatsapp_sentby IS'] = null;   
		   }
    }
       // pr($conditions);
       
	  //$conditions['conditions']['verified'] = 1;
		//$order=$this->paginate['order'] = array('Mtables.id'=>'desc');
		
		
		//$order=$this->paginate['order'] = array('Mtables.application_date'=>'asc');
		//$mdatas = $this->paginate('Mtables', $conditions);
		
		if(!empty($this->request->query['export']) && ($this->request->query['export']=='pdf') ){
        $mdatas = $this->Mtables->find('all',$conditions)->order(['Mtables.maturity_date' => 'asc'])->toArray() ;
        //$mdatas->order(['id' => 'DESC']); 
      }else{
        $order=$this->paginate['order'] = array('Mtables.maturity_date'=>'asc'); 
		$mdatas = $this->paginate('Mtables', $conditions);
      }
      
      //print_r($mdatas);die();
		
		
		$userid = $this->Auth->user('id');
		$filenames = $this->Filenames->find('list',['keyField'=>'id','valueField'=>'fname'])->order(['fname' => 'ASC'])->toArray();
		
		$this->Search->addFilter('datedform', ['attributes'=>['placeholder'=>'Maturity Start Date','label'=>'Maturity Start Date :','class'=>'searchfrm','value' => isset($this->request->query['datedform'])?$this->request->query['datedform']:''],
        ]);
        
        $this->Search->addFilter('datedto', [
           'attributes'=>['placeholder'=>'Maturity End Date','label'=>'Maturity End Date :','class'=>'searchfrm','value' => isset($this->request->query['datedto'])?$this->request->query['datedto']:''],
        ]);
        
         $this->Search->addFilter('inv_datedform', [
            'attributes'=>['placeholder'=>'Investment Start Date','label'=>'Investment Start Date :','class'=>'searchfrm','value' => isset($this->request->query['inv_datedform'])?$this->request->query['inv_datedform']:''],
        ]);
        
        $this->Search->addFilter('inv_datedto', [
            'attributes'=>['placeholder'=>'Investment End Date','label'=>'Investment End Date :','class'=>'searchfrm','value' => isset($this->request->query['inv_datedto'])?$this->request->query['inv_datedto']:''],
        ]);
        
		$branches = $this->Branches->find('list', ['limit' => 200])->where($branchCond)->toArray(); 
		
		$subbrokers = $this->Fds->Brokers->find('list', [
            'keyField' => 'id',
            'valueField' => 'name'
        ])-> where(['role' => 'Subbroker'])->toArray(); 
		
		$manager = $this->Regionalmanagers->find('list', ['keyField'=>'id','valueField'=>'manager'])->order(['manager'=>'ASC'])->toArray();
		
        $this->set(compact('mdatas','customer_name','datedform','datedto','dated2','dated1','companyname','userid','custname','filenames','branches','subbrokers','manager'));
        
        if(!empty($this->request->query['export']) && ($this->request->query['export']=='pdf') ){
            $fileName = "fdmaturitydata_".date("d-m-y:h:s");
            $this->set('filename', $fileName);
            if(!empty($this->request->query['export']=='pdf')){
                $this->render('export_fdmaturity_pdf','export_pdf');
            }
        }
	}
	
	
	
	
	public function importsetup(){
	    
	}
	public function fdmaturityexceldata()
	{
	$this->loadModel('Mtables');
	$this->loadModel('Filenames');
    $filenames = $this->Filenames->find('list',['keyField'=>'id','valueField'=>'fname'])->order(['fname' => 'ASC'])->toArray();
	$fileName = "fdexcelmaturitydata_".date("d-m-y:h:s").".xls";
	$conditions = array();
	
	if((!empty($this->request->query['datedform'])) ||(!empty($this->request->query['datedto']))){ 
			$dated2 =  (($this->request->query['datedform'] != '')? $this->request->query['datedform'] : '');
			$date_from = explode('/',$dated2);
			$datedform  = implode('-', array_reverse($date_from));
			$dated1= (($this->request->query['datedto']!='')? $this->request->query['datedto']:'');
			$date_to = explode('/',$dated1);
			$datedto  = implode('-', array_reverse($date_to));	
			if ($datedform && $datedto){
				if($datedform == $datedto){
					$adt = new \DateTime($datedto);
					$adt->modify('+1 day');
					$datedto = $adt->format('Y-m-d');        
				}
			$conditions = array('conditions' => array('and' => array(array('Mtables.maturity_date >= ' => $datedform,'Mtables.maturity_date <= ' => $datedto))));
			} 
        }else{	
	  /*$datedfrom  = date('Y-m-01', strtotime('+1 month'));
	  $datedto  = 	date('Y-m-t', strtotime('+1 month'));
	  $conditions = array('conditions' => array('and' => array(array('Mtables.maturity_date >= ' => $datedfrom,'Mtables.maturity_date <= ' => $datedto))));*/
	  
        	  if((empty($this->request->query['inv_datedform'])) ||(empty($this->request->query['inv_datedto']))){
        
                    $dated2 = date('01/m/Y', strtotime('+1 month'));
                    $datedform = date('Y-m-01', strtotime('+1 month'));
                    $datedfrom  = date('Y-m-01', strtotime('+1 month'));
                    $dated1 = date('t/m/Y', strtotime('+1 month'));
                    $datedto  = 	date('Y-m-t', strtotime('+1 month'));
                    $conditions = array('conditions' => array('and' => array(array('Mtables.maturity_date >= ' => $datedfrom,'Mtables.maturity_date <= ' => $datedto))));
                    $this->request->query['datedform']= $dated2;
                    $this->request->query['datedto'] = $dated1;
                }
		}
		
		if((!empty($this->request->query['inv_datedform'])) ||(!empty($this->request->query['inv_datedto']))){
            $invdated2 =  (($this->request->query['inv_datedform'] != '')? $this->request->query['inv_datedform'] : '');
            $invdate_from = explode('/',$invdated2);
            $invdatedform  = implode('-', array_reverse($invdate_from));
            $invdated1= (($this->request->query['inv_datedto']!='')? $this->request->query['inv_datedto']:'');
            $invdate_to = explode('/',$invdated1);
            $invdatedto  = implode('-', array_reverse($invdate_to));
            if ($invdatedform && $invdatedto){
                if($invdatedform == $invdatedto){
                    $invadt = new \DateTime($invdatedto);
                    //$adt->modify('+1 day');
                    $invdatedto = $invadt->format('Y-m-d');
                }
                $conditions['conditions'][] = array('and' => array(array('Mtables.application_date >= ' => $invdatedform,'Mtables.application_date <= ' => $invdatedto)));
            }
        }
	
	 //$datedfrom  = date('Y-m-01', strtotime('+1 month'));
	  //$datedto  = 	date('Y-m-t', strtotime('+1 month'));
	  //$conditions = array('conditions' => array('and' => array(array('Mtables.maturity_date >= ' => $datedfrom,'Mtables.maturity_date <= ' => $datedto))));
	  
	  if (isset($this->request->query['keyword'])){
			 $query =    $this->request->query['keyword'];
			 $conditions['conditions']['or']['Mtables.customer_name LIKE'] = "%$query%";
			 }
	
	
	  if (!empty($this->request->query['customer_name']) && $this->request->query['customer_name'] !='all'){
        $customer_name =    $this->request->query['customer_name'];
        $conditions['conditions']['Mtables.customer_name'] = $customer_name;
      }
	  
	  if(!empty($this->request->query['name']) && $this->request->query['name']!= 'all'){
        $conditions['conditions']['sub_broker'] = $this->request->query['name'];
      }
	  
	  if(isset($this->request->query['fname'])){
	  $fname = $this->request->query['fname']; 
	  if ($fname && $fname != 'all'){ $conditions['conditions']['csvid'] = "$fname"; }
	  }
	  
	  if(isset($this->request->query['institution_name'])){
        $institution_name = $this->request->query['institution_name'];
        if ($institution_name && $institution_name != 'all'){ $conditions['conditions']['institution_name'] = "$institution_name"; }
      }
	  
	  if(isset($this->request->query['minorname']) && !empty($this->request->query['minorname'])){//print_r($this->request->query);
            $minorname = $this->request->query['minorname'];
			if ($minorname && $minorname != 'all'){
			 $minornamefd = $this->Fds->find('all',['conditions'=>['Fds.minorname Like' => '%' . $minorname]])->toArray();
			 $fdId = $minornamefd[0]->id;
			 $conditions['conditions']['and']['fdtable_fdid'] = $fdId; 
			}
        }else{
            $minorname = 'all';
        }
     
    // for RM search
      
      if(!empty($this->request->query['regionalmanager']) && $this->request->query['regionalmanager']!= 'all' && $this->request->query['regionalmanager']!= 'normanager'){
        $conditions['conditions']['manager_id'] = $this->request->query['regionalmanager'];
      }
	  if(!empty($this->request->query['regionalmanager']) && $this->request->query['regionalmanager'] == 'normanager'){
        $conditions['conditions']['manager_id'] = 0;
      }
      
    if(!empty($this->request->query['remark']) && $this->request->query['remark']!= 'all'){
        //print_r($this->request->query);
        if($this->request->query['remark'] == 'Pre Maturity'){
         $conditions['conditions']['remark LIKE'] = '%'.$this->request->query['remark'].'%';   
        }else if($this->request->query['remark'] == 'Renewal'){
            $conditions['conditions']['remark LIKE'] = '%'.$this->request->query['remark'].'%';   
        }else if($this->request->query['remark'] == 'Repayment'){
            $conditions['conditions']['remark LIKE'] = '%'.$this->request->query['remark'].'%';   
        }else if($this->request->query['remark'] == 'Out of Station'){
            $conditions['conditions']['remark LIKE'] = '%'.$this->request->query['remark'].'%';   
        }else if($this->request->query['remark'] == 'Not Interested'){
            $conditions['conditions']['remark LIKE'] = '%'.$this->request->query['remark'].'%';   
        }else if($this->request->query['remark'] == 'Informed'){
            $conditions['conditions']['remark LIKE'] = '%'.$this->request->query['remark'].'%';   
        }else if($this->request->query['remark'] == 'Remind Later'){
            $conditions['conditions']['remark LIKE'] = '%'.$this->request->query['remark'].'%';   
        }else{
         $conditions['conditions']['remark IS'] = null;   
        }
    }
    
    if(!empty($this->request->query['emailstatus']) && $this->request->query['emailstatus']!= 'all'){
	   if($this->request->query['emailstatus'] == 1){
		$conditions['conditions']['emailupdatedby >='] = $this->request->query['emailstatus'];   
	   }else{
		$conditions['conditions']['emailupdatedby IS'] = null;   
	   }
    }
	 
	if(!empty($this->request->query['whatsappstatus']) && $this->request->query['whatsappstatus']!= 'all'){
		   if($this->request->query['whatsappstatus'] == 1){
			$conditions['conditions']['whatsapp_sentby >='] = $this->request->query['whatsappstatus'];   
		   }else{
			$conditions['conditions']['whatsapp_sentby IS'] = null;   
		   }
    }    
        
    
	  
	  if($this->Auth->user('group_id') > 2)
	  {
           $conditions['conditions']['branch_id'] = $this->Auth->user('branch_id'); 
	  }
	  if(!empty($this->request->query['branch']) && $this->request->query['branch']!= 'all'){
        $conditions['conditions']['branch_id'] = $this->request->query['branch'];
      }
	    $mdatas = $this->Mtables->find('all',$conditions);
		$mdatas->order(['Mtables.maturity_date' => 'asc']); 
		 
		//order=$this->paginate['order'] = array('Mtables.id'=>'desc');
		//$mdatas = $this->paginate('Mtables', $conditions);
		
		 $datafds = $mdatas->toArray(); 
		 
		$mbranches = TableRegistry::get('Branches');
		$musers = TableRegistry::get('Users');
		$brokers = TableRegistry::get('Brokers');
		$minor = TableRegistry::get('Fds');
     
        //$headerRow = array("Sr No","Client Name", "PAN NO",'Application Date','Payment Mode','Cheque No','UTR/FDR No','Principle Investment','Tenure','Maturity Date','Address','Product name','Company Name','Investment Branch','Branch User','Sub Broker Name');
        $headerRow = array('Sr No', 'Investor Name', 'Pan No','Email','Mobile','Sub Broker','Institution Name','Investment Date' ,'Investment Amount','Tenure', 'Maturity Date','Branch User','Investor Remark');
        $j=1;
            for($i=0; $i< count($datafds); $i++){
				if($datafds[$i]->branch_id > 0){
                            $branchid = $mbranches->find()->where(['id' => $datafds[$i]->branch_id]);
                            $data1 = $branchid->toArray();
                            $branchname = $data1[0]->branchname;
                        }else{
                            $branchname = '';
                        }
				if($datafds[$i]->modifiedby > 0){
                            $modifiedid = $musers->find()->where(['id' => $datafds[$i]->modifiedby]);
                            $data1 = $modifiedid->toArray();
                            $modifiedname = $data1[0]->firstname;
                        }else{
                            $modifiedname = '';
                        }
				$sub_broker = '';
				if($datafds[$i]->sub_broker > 0){
                            $brokerid = $brokers->find()->where(['id' => $datafds[$i]->sub_broker]);
                            $data1 = $brokerid->toArray();
                            $sub_broker = $data1[0]->name;
                        }		
				 
				  if($datafds[$i]->fdtable_fdid > 0){
        				$minordata = $minor->find()->where(['id' => $datafds[$i]->fdtable_fdid]);
        				$mnrdata = $minordata->toArray();
						$minordata = $mnrdata[0]->minorname;
        				if($minordata){
        					//$minordata = $mnrdata[0]->minorname;
        					$custname = $minordata.' U/G '.$datafds[$i]->customer_name;
        				}else{
							 $custname = $datafds[$i]->customer_name;
						}
					}else{
							 $custname = $datafds[$i]->customer_name;
						}
						
                //$data[] = [$j,$datafds[$i]->customer_name,$datafds[$i]->pan_no,$datafds[$i]->application_date,$datafds[$i]->payment_mode,$datafds[$i]->cheque_no,$datafds[$i]->utr_fdr_no,$datafds[$i]->principle_investment,$datafds[$i]->period,$datafds[$i]->maturity_date,$datafds[$i]->address,$datafds[$i]->product_name,$datafds[$i]->company_name,$branchname,$modifiedname,$brokername];
                $format_of = !empty($filenames[$datafds[$i]->csvid]) ? $filenames[$datafds[$i]->csvid] :  "";
                
               /* $defaultInstitutionCompany = array(3,17,19);
                if(in_array($datafds[$i]->csvid,$defaultInstitutionCompany)){
                    $datafds[$i]->institution_name = !empty($filenames[$datafds[$i]->csvid]) ? $filenames[$datafds[$i]->csvid] :  "";
                }*/
                
                /* $data[] = [$j, $datafds[$i]->customer_name, $datafds[$i]->pan_no, $format_of,$datafds[$i]->instrument_type,$datafds[$i]->institution_name, $datafds[$i]->application_date, $datafds[$i]->investment_amount, $datafds[$i]->period, $datafds[$i]->maturity_date,$branchname,$datafds[$i]->remark]; */
				$data[] = [$j, trim($custname), trim($datafds[$i]->pan_no), trim($datafds[$i]->email),trim($datafds[$i]->mobileno),$sub_broker, $datafds[$i]->institution_name, $datafds[$i]->application_date, trim($datafds[$i]->investment_amount), $datafds[$i]->period, $datafds[$i]->maturity_date,$branchname,trim($datafds[$i]->remark)];
            $j++;
                } 
			//pr($headerRow);
            //pr($data);  // die();
        $this->ExportXls->export($fileName, $headerRow, $data);
	
	}
	
	
	public function fdimportexceldata()
    {

        $this->loadModel('Mtables');
        $this->loadModel('Branches');
        $this->loadModel('Filenames');
        $filenames = $this->Filenames->find('list',['keyField'=>'id','valueField'=>'fname'])->order(['fname' => 'ASC'])->toArray();

        $fileName = "fdimportexceldata_" . date("d-m-y:h:s") . ".xls";
        $conditions = array();
        
        if((!empty($this->request->query['datedform'])) ||(!empty($this->request->query['datedto']))){
            $dated2 =  (($this->request->query['datedform'] != '')? $this->request->query['datedform'] : '');
            $date_from = explode('/',$dated2);
            $datedform  = implode('-', array_reverse($date_from));
            $dated1= (($this->request->query['datedto']!='')? $this->request->query['datedto']:'');
            $date_to = explode('/',$dated1);
            $datedto  = implode('-', array_reverse($date_to));
            if ($datedform && $datedto){
                if($datedform == $datedto){
                    $adt = new \DateTime($datedto);
                    $adt->modify('+1 day');
                    $datedto = $adt->format('Y-m-d');
                }
                $conditions = array('conditions' => array('and' => array(array('Mtables.maturity_date >= ' => $datedform,'Mtables.maturity_date <= ' => $datedto))));
            }
        }else{
            $dated1 = '';
            $dated2 = '';
            $datedform  = '';
            $datedto  = '';
        }
    
        if (isset($this->request->query['keyword'])) {
            $query = $this->request->query['keyword'];
            $keyword = $this->request->query['keyword'];
            $keywords = explode(' ',$keyword);
            if(count($keywords) > 1){
                $conditions['conditions']['Mtables.customer_name LIKE'] = "$keyword%";
            }else{
                $conditions['conditions']['Mtables.customer_name LIKE'] = "%$keyword%";
            }
        }else{
            $keyword = '';
        }
        
        if(isset($this->request->query['pan_no'])){
            $applicant_pan = $this->request->query['pan_no'];
            if(!empty($this->request->query['pan_no'])){
                $conditions['conditions']['pan_no'] = $applicant_pan;
            }
        }
    
    
        if(!empty($this->request->query['fname'])){
            $fname = $this->request->query['fname'];
            $comname = $this->Filenames->find('all',['conditions'=>['Filenames.id' => $fname]])->toArray();
            $companyname = $comname[0]['fname'];
            if ($fname && $fname != 'all'){
                $conditions['conditions']['csvid'] = $fname;
            }
        }else{
            $companyname = '';
        }
        
        $conditions['conditions']['verified'] = 0;
        $mdatas = $this->Mtables->find('all', $conditions);
        //$mdatas->order(['Mtables.id' => 'DESC']);
        $mdatas->order(['Mtables.application_date' => 'asc']);
        $datafds = $mdatas->toArray();
        
        $mbranches = TableRegistry::get('Branches');
        $musers = TableRegistry::get('Users');
        $brokers = TableRegistry::get('Brokers');

        //$headerRow = array("Sr No", "Client Name", "PAN NO", 'Application Date', 'Payment Mode', 'Cheque No', 'UTR/FDR No', 'Principle Investment', 'Tenure', 'Maturity Date', 'Address', 'Product name', 'Company Name', 'Investment Branch', 'Branch User', 'Sub Broker Name');
        $headerRow = array('Sr No', 'Investor Name', 'Pan No','Investment Date' ,'Investment Amount', 'Mobile No', 'Tenure', 'Maturity Date','Institution Name','Company Name');
        $j = 1;
        
        for ($i = 0; $i < count($datafds); $i++) { 
            if ($datafds[$i]->branch_id > 0) {
                $branchid = $mbranches->find()->where(['id' => $datafds[$i]->branch_id]);
                $data1 = $branchid->toArray();
                if(!empty($data1)){
                    $branchname = $data1[0]->branchname;
                }else{
                    $branchname = '';  
                }
            } else {
                $branchname = '';
            }
            if ($datafds[$i]->modifiedby > 0) {
                $modifiedid = $musers->find()->where(['id' => $datafds[$i]->modifiedby]);
                $data1 = $modifiedid->toArray();
                if(!empty($data1)){
                    $modifiedname = $data1[0]->firstname;
                }else{
                    $modifiedname = '';
                }
                
            } else {
                $modifiedname = '';
            }
            if ($datafds[$i]->sub_broker > 0) {
                $brokerid = $brokers->find()->where(['id' => $datafds[$i]->sub_broker]);
                $data1 = $brokerid->toArray();
                if(!empty($data1)){
                    $brokername = $data1[0]->name;
                }else{
                    $brokername = '';
                }
            } else {
                $brokername = '';
            }
            //$data[] = [$j, $datafds[$i]->customer_name, $datafds[$i]->pan_no, $datafds[$i]->application_date, $datafds[$i]->payment_mode, $datafds[$i]->cheque_no, $datafds[$i]->utr_fdr_no, $datafds[$i]->principle_investment, $datafds[$i]->period, $datafds[$i]->maturity_date, $datafds[$i]->address, $datafds[$i]->product_name, $datafds[$i]->company_name, $branchname, $modifiedname, $brokername];
            $format_of = !empty($filenames[$datafds[$i]->csvid]) ? $filenames[$datafds[$i]->csvid] :  "";
            
            /*$defaultInstitutionCompany = array(3,17,19);
            if(in_array($datafds[$i]->csvid,$defaultInstitutionCompany)){
                $datafds[$i]->institution_name = !empty($filenames[$datafds[$i]->csvid]) ? $filenames[$datafds[$i]->csvid] :  "";
            }*/
                
            $data[] = [$j, $datafds[$i]->customer_name, $datafds[$i]->pan_no, $datafds[$i]->application_date, $datafds[$i]->investment_amount,$datafds[$i]->mobileno, $datafds[$i]->period, $datafds[$i]->maturity_date, $datafds[$i]->institution_name, $format_of];
            $j++;
        }
        $this->ExportXls->export($fileName, $headerRow, $data);
    }
    
    
    public function updatebranch(){
        $result = array();
        $this->loadModel('Mtables');
        $conditions = array();
        if (isset($this->request->data['bid']) && $this->request->data['bid'] != ''){
         $bid = $this->request->data['bid'];
        }else{
         $bid = NULL;
        }
        
        if (isset($this->request->data['rid']) && $this->request->data['rid'] != ''){
            $rid = $this->request->data['rid'];
        }else{
            $rid = '';
        }
        
         if (isset($this->request->data['userid']) && $this->request->data['userid'] != ''){
            $userid = $this->request->data['userid'];
        }else{
            $userid = '';
        }
        
        $result = array();
        
        //if($bid != '' and $rid != ''){
        if($rid != ''){
            $fds = $this->Mtables->get($rid, [
                'contain' => [],
            ]);
            $this->request->data['branchupdatedby'] = $userid;
            $this->request->data['branch_id'] = $bid;
            $this->request->data['verified'] = 1;
            $fds = $this->Mtables->patchEntity($fds, $this->request->data);
            
           /* $this->Mtables->updateAll(
            ["verified" => 1,"branch_id" => $bid,"branchupdatedby" => $userid],
            ["pan_no" => $fds['pan_no']]
            );
            //$this->Mtables->save($fds);
            $result[] = 'success';*/
            
            if(!empty($fds['pan_no'])){
                 $this->Mtables->updateAll(
            ["verified" => 1,"branch_id" => $bid,"branchupdatedby" => $userid],
            ["pan_no" => $fds['pan_no']]
            );
            $result['result'] = 'success';
            }else{
            $result['result'] = 'error';    
            }
            
        }
        $this->set('result', $result);
      }
      
      //For RM quick edit
       public function updatermanager(){
        $result = array();
        $this->loadModel('Mtables');
        $conditions = array();
        if (isset($this->request->data['mid']) && $this->request->data['mid'] != ''){
         $mid = $this->request->data['mid'];
        }else{
         //$mid = '';
         $mid = 0;
        }
        
        if (isset($this->request->data['rid']) && $this->request->data['rid'] != ''){
            $rid = $this->request->data['rid'];
        }else{
            $rid = '';
        }
        
         if (isset($this->request->data['userid']) && $this->request->data['userid'] != ''){
            $userid = $this->request->data['userid'];
        }else{
            $userid = '';
        }
        
        $result = array();
        
        //if($mid != '' and $rid != ''){
        if($rid != ''){
            $fds = $this->Mtables->get($rid, [
                'contain' => [],
            ]);
            $this->request->data['branchupdatedby'] = $userid;
            $this->request->data['manager_id'] = $mid;
            $this->request->data['verified'] = 1;
            $fds = $this->Mtables->patchEntity($fds, $this->request->data);
           
            if(!empty($fds['pan_no'])){
                 $this->Mtables->updateAll(
            ["verified" => 1,"manager_id" => $mid,"branchupdatedby" => $userid],
            ["pan_no" => $fds['pan_no']]
            );
            $result['result'] = 'success';
            }else{
            $result['result'] = 'error';    
            }
            
        }
        $this->set('result', $result);
    }
    
      
      public function updateremark(){
        $result = array();
        $this->loadModel('Mtables');
        $conditions = array();

        if (isset($this->request->data['remark']) && $this->request->data['remark'] != ''){
         $remark = $this->request->data['remark'];
        }else{
         $remark = NULL;
        }
        
        if (isset($this->request->data['rid']) && $this->request->data['rid'] != ''){
            $rid = $this->request->data['rid'];
        }else{
            $rid = '';
        }

        if (isset($this->request->data['userid']) && $this->request->data['userid'] != ''){
            $userid = $this->request->data['userid'];
        }else{
            $userid = '';
        }
        
        $result = array();
        
        //if($remark != '' and $rid != ''){
        if($rid != ''){
            $fds = $this->Mtables->get($rid, [
                'contain' => [],
            ]);

            $this->request->data['remarkupdatedby'] = $userid;
            $this->request->data['remark'] = $remark;
            $fds = $this->Mtables->patchEntity($fds, $this->request->data);
            $this->Mtables->save($fds);
            $result[] = 'success';
        }
        $this->set('result', $result);
      }
      
      public function fdupdatebranch()
{

    $this->loadModel('Filenames');
    $this->loadModel('Mtables');
    $this->loadModel('Users');
    $this->loadModel('Branches');
    $all = array('all'=>'All');
    $branches = $this->Branches->find('list', ['limit' => 200]);
    $conditions = array();

    // start branch not updated
    $conditions['conditions']['branch_id'] = 0;
    //end branch not updated

    //start next month condition
    $dated2 = date('Y-m-01', strtotime('+1 month'));
    $datedform = date('Y-m-01', strtotime('+1 month')); 
    $datedfrom  = date('Y-m-01', strtotime('+1 month'));
    $dated1 = date('Y-m-t', strtotime('+1 month'));
    $datedto  =   date('Y-m-t', strtotime('+1 month'));
    $conditions = array('conditions' => array('and' => array(array('Mtables.maturity_date >= ' => $datedfrom,'Mtables.maturity_date <= ' => $datedto))));
    //end next month condition

    $order=$this->paginate['order'] = array('Mtables.id'=>'desc');
    $mdatas = $this->paginate('Mtables', $conditions);
    $userid = $this->Auth->user('id');
    $this->set(compact('mdatas','customer_name','datedform','datedto','dated2','dated1','companyname','userid','branches'));
}

public function fdrenewal()
{

    $this->loadModel('Filenames');
    $this->loadModel('Mtables');
    $this->loadModel('Users');
    $this->loadModel('Branches');
    $all = array('all'=>'All');
    $conditions = array();
    
    // start remark renewal
    $conditions['conditions']['remark'] = 'Renewal';
    //end remark renewal

    $order=$this->paginate['order'] = array('Mtables.id'=>'desc');
    $mdatas = $this->paginate('Mtables', $conditions);
    $userid = $this->Auth->user('id');
    $this->set(compact('mdatas','customer_name','datedform','datedto','dated2','dated1','companyname','userid','branches'));
}

function addsmc(){

    $this->LoadModel("Mtables");
    $this->LoadModel("Mutualfunds");
    $this->LoadModel("Filenames");
    $fds = $this->Mtables->newEntity();
    $this->Search->addFilter('fname', ['attributes'=>['placeholder'=>'Format Name','label'=>'Company Name :','class'=>'searchfrm','value'=>isset($this->request->query['fname'])?$this->request->query['fname']:''],'options' => $this->Filenames->find('list',['keyField'=>'id','valueField'=>'fname'])->order(['fname' => 'ASC'])->toArray(),
    ]);
     if ($this->request->is(['patch', 'post', 'put'])) {
        $this->request->data['csvid'] = 20;
        $this->request->data['uploadedby'] =  $this->Auth->user('id');
        $fds = $this->Mtables->patchEntity($fds, $this->request->data);
        //print_r($fds);die();
        if ($this->Mtables->save($fds)) {
            $this->Flash->success(__('The FD data has been saved.'));
            return $this->redirect(['action' => 'fdimportdata']);
        } else {
            $this->Flash->error(__('The FD data could not be saved. Please, try again.'));
        }
    }
    $this->set(compact('fds','branches','subbrokers'));

}

function addhdfc(){

    $this->LoadModel("Mtables");
    $this->LoadModel("Mutualfunds");
    $this->LoadModel("Filenames");
    $fds = $this->Mtables->newEntity();
    $this->Search->addFilter('fname', ['attributes'=>['placeholder'=>'Format Name','label'=>'Company Name :','class'=>'searchfrm','value'=>isset($this->request->query['fname'])?$this->request->query['fname']:''],'options' => $this->Filenames->find('list',['keyField'=>'id','valueField'=>'fname'])->order(['fname' => 'ASC'])->toArray(),
    ]);
     if ($this->request->is(['patch', 'post', 'put'])) {
        $this->request->data['csvid'] = 3;
        $this->request->data['uploadedby'] =  $this->Auth->user('id');
        $fds = $this->Mtables->patchEntity($fds, $this->request->data);
        //print_r($fds);die();
        if ($this->Mtables->save($fds)) {
            $this->Flash->success(__('The FD data has been saved.'));
            return $this->redirect(['action' => 'fdimportdata']);
        } else {
            $this->Flash->error(__('The FD data could not be saved. Please, try again.'));
        }
    }
    $this->set(compact('fds','branches','subbrokers'));
}

function addbajaj(){

    $this->LoadModel("Mtables");
    $this->LoadModel("Mutualfunds");
    $this->LoadModel("Filenames");
    $fds = $this->Mtables->newEntity();
    $this->Search->addFilter('fname', ['attributes'=>['placeholder'=>'Format Name','label'=>'Company Name :','class'=>'searchfrm','value'=>isset($this->request->query['fname'])?$this->request->query['fname']:''],'options' => $this->Filenames->find('list',['keyField'=>'id','valueField'=>'fname'])->order(['fname' => 'ASC'])->toArray(),
    ]);
     if ($this->request->is(['patch', 'post', 'put'])) {
        $this->request->data['csvid'] = 17;
        $this->request->data['uploadedby'] =  $this->Auth->user('id');
        $fds = $this->Mtables->patchEntity($fds, $this->request->data);
        //print_r($fds);die();
        if ($this->Mtables->save($fds)) {
            $this->Flash->success(__('The FD data has been saved.'));
            return $this->redirect(['action' => 'fdimportdata']);
        } else {
            $this->Flash->error(__('The FD data could not be saved. Please, try again.'));
        }
    }
    $this->set(compact('fds','branches','subbrokers'));

}
function addpnb(){

    $this->LoadModel("Mtables");
    $this->LoadModel("Mutualfunds");
    $this->LoadModel("Filenames");
    $fds = $this->Mtables->newEntity();
    $this->Search->addFilter('fname', ['attributes'=>['placeholder'=>'Format Name','label'=>'Company Name :','class'=>'searchfrm','value'=>isset($this->request->query['fname'])?$this->request->query['fname']:''],'options' => $this->Filenames->find('list',['keyField'=>'id','valueField'=>'fname'])->order(['fname' => 'ASC'])->toArray(),
    ]);
     if ($this->request->is(['patch', 'post', 'put'])) {
        $this->request->data['csvid'] = 19;
        $this->request->data['uploadedby'] =  $this->Auth->user('id');
        $fds = $this->Mtables->patchEntity($fds, $this->request->data);
        //print_r($fds);die();
        if ($this->Mtables->save($fds)) {
            $this->Flash->success(__('The FD data has been saved.'));
            return $this->redirect(['action' => 'fdimportdata']);
        } else {
            $this->Flash->error(__('The FD data could not be saved. Please, try again.'));
        }
    }
    $this->set(compact('fds','branches','subbrokers'));

}
function addshriram(){

    $this->LoadModel("Mtables");
    $this->LoadModel("Mutualfunds");
    $this->LoadModel("Filenames");
    $fds = $this->Mtables->newEntity();
    $this->Search->addFilter('fname', ['attributes'=>['placeholder'=>'Format Name','label'=>'Company Name :','class'=>'searchfrm','value'=>isset($this->request->query['fname'])?$this->request->query['fname']:''],'options' => $this->Filenames->find('list',['keyField'=>'id','valueField'=>'fname'])->order(['fname' => 'ASC'])->toArray(),
    ]);
     if ($this->request->is(['patch', 'post', 'put'])) {
        $this->request->data['csvid'] = 18;
        $this->request->data['uploadedby'] =  $this->Auth->user('id');
        $fds = $this->Mtables->patchEntity($fds, $this->request->data);
        //print_r($fds);die();
        if ($this->Mtables->save($fds)) {
            $this->Flash->success(__('The FD data has been saved.'));
            return $this->redirect(['action' => 'fdimportdata']);
        } else {
            $this->Flash->error(__('The FD data could not be saved. Please, try again.'));
        }
    }
    $this->set(compact('fds','branches','subbrokers'));

}
function addkarvy(){

    $this->LoadModel("Mtables");
    $this->LoadModel("Mutualfunds");
    $this->LoadModel("Filenames");
    $fds = $this->Mtables->newEntity();
    $this->Search->addFilter('fname', ['attributes'=>['placeholder'=>'Format Name','label'=>'Company Name :','class'=>'searchfrm','value'=>isset($this->request->query['fname'])?$this->request->query['fname']:''],'options' => $this->Filenames->find('list',['keyField'=>'id','valueField'=>'fname'])->order(['fname' => 'ASC'])->toArray(),
    ]);
     if ($this->request->is(['patch', 'post', 'put'])) {
        $this->request->data['csvid'] = 21;
        $this->request->data['uploadedby'] =  $this->Auth->user('id');
        $fds = $this->Mtables->patchEntity($fds, $this->request->data);
        //print_r($fds);die();
        if ($this->Mtables->save($fds)) {
            $this->Flash->success(__('The FD data has been saved.'));
            return $this->redirect(['action' => 'fdimportdata']);
        } else {
            $this->Flash->error(__('The FD data could not be saved. Please, try again.'));
        }
    }
    $this->set(compact('fds','branches','subbrokers'));

}
function addjm(){

    $this->LoadModel("Mtables");
    $this->LoadModel("Mutualfunds");
    $this->LoadModel("Filenames");
    $fds = $this->Mtables->newEntity();
    $this->Search->addFilter('fname', ['attributes'=>['placeholder'=>'Format Name','label'=>'Company Name :','class'=>'searchfrm','value'=>isset($this->request->query['fname'])?$this->request->query['fname']:''],'options' => $this->Filenames->find('list',['keyField'=>'id','valueField'=>'fname'])->order(['fname' => 'ASC'])->toArray(),
    ]);
     if ($this->request->is(['patch', 'post', 'put'])) {
        $this->request->data['csvid'] = 16;
        $this->request->data['uploadedby'] =  $this->Auth->user('id');
        $fds = $this->Mtables->patchEntity($fds, $this->request->data);
        //print_r($fds);die();
        if ($this->Mtables->save($fds)) {
            $this->Flash->success(__('The FD data has been saved.'));
            return $this->redirect(['action' => 'fdimportdata']);
        } else {
            $this->Flash->error(__('The FD data could not be saved. Please, try again.'));
        }
    }
    $this->set(compact('fds','branches','subbrokers'));


}
function addifan(){

    $this->LoadModel("Mtables");
    $this->LoadModel("Mutualfunds");
    $this->LoadModel("Filenames");
    $fds = $this->Mtables->newEntity();
    $this->Search->addFilter('fname', ['attributes'=>['placeholder'=>'Format Name','label'=>'Company Name :','class'=>'searchfrm','value'=>isset($this->request->query['fname'])?$this->request->query['fname']:''],'options' => $this->Filenames->find('list',['keyField'=>'id','valueField'=>'fname'])->order(['fname' => 'ASC'])->toArray(),
    ]);
     if ($this->request->is(['patch', 'post', 'put'])) {
        $this->request->data['csvid'] = 5;
        $this->request->data['uploadedby'] =  $this->Auth->user('id');
        $fds = $this->Mtables->patchEntity($fds, $this->request->data);
        //print_r($fds);die();
        if ($this->Mtables->save($fds)) {
            $this->Flash->success(__('The FD data has been saved.'));
            return $this->redirect(['action' => 'fdimportdata']);
        } else {
            $this->Flash->error(__('The FD data could not be saved. Please, try again.'));
        }
    }
    $this->set(compact('fds','branches','subbrokers'));

}

function fdview($id = null){
    $this->LoadModel("Mtables");
    $this->LoadModel("Cheaders");
    $datas = $this->Mtables->get($id);
    $rowid = $id;
    
    $chid = $datas->csvid;
    $isverified = $datas->verified;
   
    $gdatas = $this->Cheaders->find('all', [ 
        'conditions' => ['fid' => $chid]
      ])->toArray();
  
    $this->set('gdatas', $gdatas);
    $this->set('rowid', $rowid);
    $this->set('chid', $chid);
    $this->set('isverified', $isverified);
    //$this->set('_serialize', ['user']);

}

function updatefielddata(){

    //print_r($this->request->data['fieldname']);die();
    $result = array();
    $this->loadModel('Mtables');
    $this->loadModel('Customers');
    $conditions = array();
    if (!empty($this->request->data['rowid'])){
     $rowid = $this->request->data['rowid'];
    }else{
     $rowid = '';
    }
    //print_r($rowid);
    

    if (!empty($this->request->data['fieldname'])){
        $fieldname = $this->request->data['fieldname'];
       }else{
        $fieldname = '';
       }

       

       if (!empty($this->request->data['valueofinput'])){
        $valueofinput = $this->request->data['valueofinput'];
       }else{
        $valueofinput = '';
       }
       
       if (!empty($fieldname) && $fieldname == "pan_no" && !empty($valueofinput)){
            $valueofinput = preg_replace("/[^A-Za-z0-9]/", '', $valueofinput);
       }

       
    
    $result = array();
    
    if(!empty($this->request->data['rowid']) && !empty($this->request->data['fieldname']) && !empty($this->request->data['valueofinput'])){
        $fds = $this->Mtables->get($rowid, [
            'contain' => [],
        ]);
        //$this->request->data['branchupdatedby'] = $userid;
        $this->request->data[$fieldname] = $valueofinput;
        //$this->request->data['verified'] = 1;
        $fds = $this->Mtables->patchEntity($fds, $this->request->data);
        
        $rrr = $this->Mtables->updateAll(
        [$fieldname => $valueofinput],
        ["id" => $rowid]
        );
        
        if($fieldname == 'pan_no' || $fieldname == 'customer_name'){
           
        $customerid = $fds->customer_id;
        if(!empty($customerid)){
        $Customersdata = $this->Customers->get($customerid, ['contain' => [],]);
        $vdc = $this->Customers->patchEntity($Customersdata, $this->request->data);
        
        if($fieldname == 'pan_no'){
            $fieldname = 'panno';
        }
        if($fieldname == 'customer_name'){
            $fieldname = 'firstname';
        }
        
        $d = $this->Customers->updateAll(
        [$fieldname => $valueofinput],
        ["id" => $customerid]);
        }else if(empty($customerid)){
            $fdsupdated = $this->Mtables->get($rowid, [
                'contain' => [],
            ]);
            $custdata['firstname'] = $fdsupdated->customer_name;
            $custdata['panno'] = $fdsupdated->pan_no;
            
            $query = $this->Customers->find('all', [
                'order' => ['Customers.created' => 'DESC']
            ]);
            $lastuid = $query->first()->id;
            $accountNo = (1000 + ($lastuid + 1));
            $custdata['accountno'] = $accountNo;
            
            $customer= $this->Customers->newEntity();
            $custinfodata = $this->Customers->patchEntity($customer, $custdata);
            
            $customerdetails =  $this->Customers->save($custinfodata);
            
            $customersavedid =  $customerdetails->id;
            $test = $this->Mtables->updateAll(
                ['customer_id' => $customersavedid],
                ["id" => $rowid]
                );
               
        }}
        
        //print_r($rrr);die();
        //$this->Mtables->save($fds);
        $result[] = 'success';
    }
    echo 1;die();
    $this->set('result', $result);
}

    // Search Quick Mobile No Update //
    
    function updatemobiledata(){

 
    $result = array();
    $this->loadModel('Mtables');
    $this->loadModel('Customers');
    $conditions = array();
    if (!empty($this->request->data['rowid'])){
     $rowid = $this->request->data['rowid'];
    }else{
     $rowid = '';
    }
  //  print_r($rowid);
    

    if (!empty($this->request->data['fieldname'])){
        $fieldname = $this->request->data['fieldname'];
       }else{
        $fieldname = '';
       }

       

       if (!empty($this->request->data['valueofinput'])){
        $valueofinput = $this->request->data['valueofinput'];
       }else{
        $valueofinput = '';
       }
       
       if (!empty($fieldname) && $fieldname == "mobileno" && !empty($valueofinput)){
            $valueofinput = preg_replace("/[^A-Za-z0-9]/", '', $valueofinput);
       }

       
    
    $result = array();
    
    if(!empty($this->request->data['rowid']) && !empty($this->request->data['fieldname']) && !empty($this->request->data['valueofinput'])){
        $fds = $this->Mtables->get($rowid, [
            'contain' => [],
        ]);
        //$this->request->data['branchupdatedby'] = $userid;
        $this->request->data[$fieldname] = $valueofinput;
        //$this->request->data['verified'] = 1;
        $fds = $this->Mtables->patchEntity($fds, $this->request->data);
        
        $rrr = $this->Mtables->updateAll(
        [$fieldname => $valueofinput],
        ["id" => $rowid]
        );
        
        if($fieldname == 'mobileno' || $fieldname == 'customer_name'){
           
        $customerid = $fds->customer_id;
        if(!empty($customerid)){
        $Customersdata = $this->Customers->get($customerid, ['contain' => [],]);
        $vdc = $this->Customers->patchEntity($Customersdata, $this->request->data);
        
        if($fieldname == 'mobileno'){
            $fieldname = 'phoneno';
        }
        if($fieldname == 'customer_name'){
            $fieldname = 'firstname';
        }
        
        $d = $this->Customers->updateAll(
        [$fieldname => $valueofinput],
        ["id" => $customerid]);
        }else if(empty($customerid)){
            $fdsupdated = $this->Mtables->get($rowid, [
                'contain' => [],
            ]);
            $custdata['firstname'] = $fdsupdated->customer_name;
            $custdata['phoneno'] = $fdsupdated->mobileno;
            
            $query = $this->Customers->find('all', [
                'order' => ['Customers.created' => 'DESC']
            ]);
            $lastuid = $query->first()->id;
            $accountNo = (1000 + ($lastuid + 1));
            $custdata['accountno'] = $accountNo;
            
            $customer= $this->Customers->newEntity();
            $custinfodata = $this->Customers->patchEntity($customer, $custdata);
            
            $customerdetails =  $this->Customers->save($custinfodata);
            
            $customersavedid =  $customerdetails->id;
            $test = $this->Mtables->updateAll(
                ['customer_id' => $customersavedid],
                ["id" => $rowid]
                );
               
        }
    }
        
        //print_r($rrr);die();
        //$this->Mtables->save($fds);
        $result[] = 'success';
    }
    echo 1;die();
    $this->set('result', $result);
}





function addfixeddeposit(){
    
    if ($this->request->is('ajax','get')) {         
            $terms = $this->Fds->Customers->find('all', [
                'conditions' => array('Customers.firstname LIKE ' => '%' . $this->request->query["term"] . '%'),
                'limit' => 10
            ]);
          
            $data = array();$data1 = array();
            foreach($terms as $term) {
                $row = $term->firstname;
                array_push($data, $row);
            }
            $this->autoRender = false;      
            // $this->layout = 'ajax';
            $this->set('terms', $terms);
            echo json_encode($data);
            exit;
            //return json_encode($data);
         }else {
            //echo json_encode('Nothings found');
         }
         
         
        $fd = $this->Fds->newEntity();
        if ($this->request->is('post')) {
            $fd = $this->Fds->patchEntity($fd, $this->request->data);
            $fd->postedby = $this->Auth->user('id');
            $fd->posteduserbranchid = $this->Auth->user('branch_id');
          //  $fd->branch_id = $this->Auth->user('branch_id');
            if($fd->subbroker_id != ''){
                $fd->broker_id = $fd->subbroker_id;
            }else{
                $fd->broker_id = 1;
            }
            
           $custmoerId = $this->getCustomerId($fd->panno);
           if($custmoerId != ''){
                $fd->customer_id = $custmoerId;
            }
            
            if ($this->Fds->save($fd)) {
                $this->Flash->success(__('The fd has been saved.'));

                return $this->redirect(['action' => 'index']);
            } else {
                $this->Flash->error(__('The fd could not be saved. Please, try again.'));
            }
        }
        $users = $this->Fds->Customers->find('list')->order(['firstname' => 'ASC']);
     
        $subbrokers = $this->Fds->Brokers->find('list', [
            'keyField' => 'id',
            'valueField' => 'name'
        ])-> where(['role' => 'Subbroker']);
        $mainbrokers = $this->Fds->Brokers->find('list', [
            'keyField' => 'id',
            'valueField' => 'name'
        ])-> where(['role' => 'Mainbroker']);
        
        $usercompanies = $this->Fds->Companies->find('list')-> where(['associatewith' => 'user'])->order(['company' => 'ASC']);
	$userbranches = $this->Fds->Branches->find('list', ['limit' => 200])->order(['branchname' => 'ASC']);
        //$productcompanies = $this->Fds->Companies->find('list')-> where(['associatewith' => 'product'])->order(['company' => 'DESC']);
        $productcompanies = $this->Fds->Companies->find('list')-> where(['associatewith' => 'product'])->where(['productid LIKE' => '%2%'])->order(['company' => 'ASC']);
        $plans = $this->Fds->Plans->find('list')-> where(['product_id' => 2])->order(['planname' => 'ASC']);
        
        
        //$subbrokers = $this->Fds->Subbrokers->find('list', ['limit' => 200]);
        $banks = $this->Fds->Banks->find('list', ['limit' => 200])->order(['banks' => 'ASC']);
        
        $typeofdeposit = array('fresh'=>'Fresh','renewal'=>'Renewal','both'=>'Both');
       
        
        $this->set(compact('fd','users','mainbrokers','productcompanies', 'usercompanies', 'userbranches', 'plans', 'subbrokers', 'banks', 'bankdetails', 'typeofdeposit'));
       // $this->set(compact('fd', 'companies', 'branches', 'plans', 'subbrokers', 'banks', 'bankdetails'));
        $this->set('_serialize', ['fd']);
    
}

public function verify($id = null){

if(!empty($id)){
    $this->loadModel('Mtables');
        $fds = $this->Mtables->get($id, [
            'contain' => [],
        ]);

        $rrr = $this->Mtables->updateAll(
        ['verified' => 1],
        ["id" => $id]
        );
        //return $this->redirect(['action' => 'fdview',$id]);
        $this->Flash->success(__('Data Updated'));
        return $this->redirect(['controller' => 'Fds', 'action' => 'prevNext',$id,'next']);
}
}

public function prevNext($id = null,$param = null) {
    if(!empty($id) && !empty($param)){
    $this->loadModel('Mtables');
    $neighbors = $this->Mtables->find(
        'list',
        array('field' => 'id', 'value' => $id)
    )->order(['id' => 'ASC'])->toArray();
    $arrayofids = array_values($neighbors);
    $getkey = array_search($id,$arrayofids);
    if($param == 'next'){
     $getvalue = $arrayofids[++$getkey];
    }else{
      $getvalue = $arrayofids[--$getkey];
    }
    return $this->redirect(['action' => 'fdview',$getvalue]);
    exit();
}
}

    public function addfdmtable($fdtable_fdid){
        $this->LoadModel("Mtables");
        $this->LoadModel("Mutualfunds");
        $this->loadModel('Filenames');
        $this->loadModel('Companies');
        $filenames = $this->Filenames->find('list',['keyField'=>'id','valueField'=>'fname'])->order(['fname' => 'ASC'])->toArray();
             //echo $fd_id;exit;
        $productcompanies = $this->Fds->Companies->find('list')-> where(['associatewith' => 'product'])->where(['productid LIKE' => '%2%'])->order(['company' => 'ASC'])->toArray();
        //pr($productcompanies);exit;
        $fdtable_fd = array();
        $fds = array();
        if(!empty($fdtable_fdid)){

            $is_exists = $this->Mtables->find('all', array('conditions'=>array('Mtables.fdtable_fdid'=>$fdtable_fdid)));
            $number = $is_exists->count();
            if ($number > 0){
                $this->Flash->error(__('FD Already Exist. on Master FD '));
                return $this->redirect(['action' => 'index']);
            }

            $fdtable_fd = $this->Fds->get($fdtable_fdid, [
                'contain' => ['Companies','Plans']
            ])->toArray();
            if(!empty($fdtable_fd)){

                $fds['csvid'] = $fdtable_fd['filenameid'];
                $fds['customer_name'] = $fdtable_fd['fapplicantname'];
                $fds['pan_no'] = $fdtable_fd['panno'];
                $fds['application_no'] = $fdtable_fd['applicationno'];
                $fds['typeofdeposit'] = $fdtable_fd['typeofdeposit'];
                $fds['application_date'] = $fdtable_fd['planstartdate'];
                $fds['cheque_no'] = $fdtable_fd['chequeno'];
                $fds['utr_fdr_no'] = $fdtable_fd['fdrno'];
                $fds['investment_amount'] = $fdtable_fd['amount'];
                $fds['period'] = $fdtable_fd['tenure'];
                $fds['maturity_date'] = $fdtable_fd['maturitydate'];
                //$fds['address'] = '';
                $fds['product_name'] = $fdtable_fd['plan']['Planname'];
                $fds['institution_name'] = !empty($productcompanies[$fdtable_fd['productcompanyid']]) ? $productcompanies[$fdtable_fd['productcompanyid']] : '' ;
                $fds['branch_id'] = $fdtable_fd['branch_id'];
                $fds['sub_broker'] = $fdtable_fd['subbroker_id'];
                //$fds['email'] = '';

                $fds['fdtable_fdid'] = $fdtable_fd['id'];
            }

        }
        //pr($fdtable_fd);exit;
        $fds = $this->Mtables->newEntity($fds);
        //pr($fds);exit;
        if ($this->request->is(['patch', 'post', 'put'])) {
            $this->request->data['modifiedby'] = $this->Auth->user('id');
            $this->request->data['despatchdate'] = $fdtable_fd['despatchdate'];

            if(!empty($this->request->data['branch_id']) &&  $this->request->data['branch_id'] != NULL){

                //$this->request->data['verified'] = 1;
            }
            $this->request->data['verified'] = 1;

            $this->request->data['manually_added'] = 1;
            $fds = $this->Mtables->newEntity();
            $fds = $this->Mtables->patchEntity($fds, $this->request->data);
            //pr($fds);exit;

            if ($this->Mtables->save($fds)) {
                $this->Flash->success(__('The FD data has been added.'));

                return $this->redirect(['action' => 'fdverifieddata']);
            } else {
                $this->Flash->error(__('The FD data could not be Updated. Please, try again.'));
            }
        }
        $subbrokers = $this->Fds->Brokers->find('list', ['keyField' => 'id','valueField' => 'name'])-> where(['role' => 'Subbroker']);
        $branches = $this->Mutualfunds->Branches->find('list', ['limit' => 200]);
        //$paymentmode  = array('cheque'=>'Cheque','dd'=>'DD','rtgs'=>'RTGS','renewal'=>'Renewal');
        $this->set(compact('fds','branches','subbrokers','filenames'));

    }

    public function fduserlist(){
        $extraCondition = ""; 
        if (!empty($this->request->query) && count($this->request->query) > 1) {
            $dated2 = (($this->request->query['datedform'] != '') ? $this->request->query['datedform'] : '');
            $date1 = explode('/', $dated2);
            $datedform = implode('-', array_reverse($date1));
            $dated1 = $this->request->query['datedto'];
            $date = explode('/', $dated1);
            $datedto = implode('-', array_reverse($date));
            $extraCondition = "created >= '".$datedform."' AND created <= '".$datedto."'";
        }

    if(isset($datedform) && isset($datedto) ){
        
        $connection = ConnectionManager::get('default');
        $results = $connection->execute('SELECT postedby, count(*) as cnumber FROM fds WHERE '.$extraCondition.' GROUP BY postedby')->fetchAll('assoc');
    }
		$this->Search->addFilter('datedform', [
            'attributes'=>['placeholder'=>'Start Date','label'=>'Start Date :','class'=>'searchfrm','value' => isset($this->request->query['datedform'])?$this->request->query['datedform']:''],
        ]);
        $this->Search->addFilter('datedto', [
            'attributes'=>['placeholder'=>'End Date','label'=>'End Date :','class'=>'searchfrm','value' => isset($this->request->query['datedto'])?$this->request->query['datedto']:''],
        ]);
		$this->set(compact('results'));
		
	
	}
	
public function brkreconciliation(){
    
		$this->loadModel('Filenames');
		$this->loadModel('Mtables');
		$this->loadModel('Users');
		$this->loadModel('Branches');
		$all = array('all'=>'All'); 
		
        $this->Search->addFilter('customer_name',['attributes'=>['placeholder'=>'Investor Name','label'=>'Investor Name','class'=>'serachfrm serachfrm_clearable','id'=>'autocomplete1','value'=>isset($this->request->query['customer_name'])?$this->request->query['customer_name']:''],]);   
		$custname = $this->Mtables->find('list',['valueField'=>'customer_name'])->order(['customer_name' => 'ASC'])->group(['Mtables.customer_name'])->toArray();
		$custname = array_values($custname);
		
        $this->Search->addFilter('fname', ['attributes'=>['placeholder'=>'Company Name','label'=>'Company Name :','class'=>'searchfrm','value'=>isset($this->request->query['fname'])?$this->request->query['fname']:''],'options' => $all+$this->Filenames->find('list',['keyField'=>'id','valueField'=>'fname'])->order(['fname' => 'ASC'])->toArray(),
        ]);
      
        $this->Search->addFilter('institution_name', ['attributes'=>['placeholder'=>'Institution Name','label'=>'Institution Name :','class'=>'searchfrm','value'=>!empty($this->request->query['institution_name'])?$this->request->query['institution_name']:'all'],'options' => $all+$this->Mtables->find('list',['keyField'=>'institution_name','valueField'=>'institution_name'])->where(['institution_name !=' => '0'])->order(['scheme_name' => 'ASC'])->group(['Mtables.institution_name'])->toArray(),
        ]);
      
        $this->Search->addFilter('branch', [
            'attributes'=>['placeholder'=>'Branch Name','label'=>'Branch Name :','class'=>'searchfrm','value' => isset($this->request->query['branch'])?$this->request->query['branch']:''],
            'options' => $all+$this->Fds->Branches->find('list')->toArray(),
        ]);
	    $this->Search->addFilter('reconciliation', [
            'attributes'=>['placeholder'=>'Brokerage','label'=>'Brokerage :','class'=>'searchfrm','value' => isset($this->request->query['reconciliation'])?$this->request->query['reconciliation']:''],
            'options' => $all+['Brokerage Not Received'=>'Brokerage Not Received','Brokerage Received'=>'Brokerage Received','Brokerage Mismatch'=>'Brokerage Mismatch','Partial Brokerage'=>'Partial Brokerage','Next Month'=>'Next Month','Recovery by Broker'=>'Recovery by Broker'],
        ]);
        
	    $conditions = array();
	   
	    if((!empty($this->request->query['datedform'])) ||(!empty($this->request->query['datedto']))){ 
			$dated2 =  (($this->request->query['datedform'] != '')? $this->request->query['datedform'] : '');
			$date_from = explode('/',$dated2);
			$datedform  = implode('-', array_reverse($date_from));
			$dated1= (($this->request->query['datedto']!='')? $this->request->query['datedto']:'');
			$date_to = explode('/',$dated1);
			$datedto  = implode('-', array_reverse($date_to));	
			if ($datedform && $datedto){
				if($datedform == $datedto){
					$adt = new \DateTime($datedto);
					$datedto = $adt->format('Y-m-d');        
				}
			$conditions = array('conditions' => array('and' => array(array('Mtables.maturity_date >= ' => $datedform,'Mtables.maturity_date <= ' => $datedto))));
			} 
        }else{
            if((empty($this->request->query['inv_datedform'])) ||(empty($this->request->query['inv_datedto']))){ 
					$dated2 = date('01/m/Y', strtotime('+1 month'));
					$datedform = date('Y-m-01', strtotime('+1 month'));	
					$datedfrom  = date('Y-m-01', strtotime('+1 month'));
					$dated1 = date('t/m/Y', strtotime('+1 month'));
					$datedto  = 	date('Y-m-t', strtotime('+1 month'));
					$conditions = array('conditions' => array('and' => array(array('Mtables.maturity_date >= ' => $datedfrom,'Mtables.maturity_date <= ' => $datedto))));
					$this->request->query['datedform']= $dated2;
					$this->request->query['datedto'] = $dated1;
			} 
			   
		}
		
		if((!empty($this->request->query['inv_datedform'])) ||(!empty($this->request->query['inv_datedto']))){ 
			$invdated2 =  (($this->request->query['inv_datedform'] != '')? $this->request->query['inv_datedform'] : '');
			$invdate_from = explode('/',$invdated2);
			$invdatedform  = implode('-', array_reverse($invdate_from));
			$invdated1= (($this->request->query['inv_datedto']!='')? $this->request->query['inv_datedto']:'');
			$invdate_to = explode('/',$invdated1);
			$invdatedto  = implode('-', array_reverse($invdate_to));	
			if ($invdatedform && $invdatedto){
				if($invdatedform == $invdatedto){
					$invadt = new \DateTime($invdatedto);
					$invdatedto = $invadt->format('Y-m-d');        
				}
			$conditions['conditions'][] = array('and' => array(array('Mtables.application_date >= ' => $invdatedform,'Mtables.application_date <= ' => $invdatedto)));
			} 
        }	
		
		if((!empty($this->request->query['despatchdate']))){ 
			$desdate =  (($this->request->query['despatchdate'] != '')? $this->request->query['despatchdate'] : '');
			$despatchdate = explode('/',$desdate);
			$despatchdate  = implode('-', array_reverse($despatchdate));
			if ($despatchdate){
					$adt = new \DateTime($despatchdate);
					$despatchdate = $adt->format('Y-m-d');        
			$conditions['conditions'][] = array('and' => array(array('Mtables.despatchdate = ' => $despatchdate)));
			}
		}
	     
		//echo $conditions; 
		 
		 
		if(isset($this->request->query['customer_name']) && !empty($this->request->query['customer_name'])){
			$customer_name = $this->request->query['customer_name']; 
			if ($customer_name && $customer_name != 'all'){
				 $applicant_pan_no = $this->Mtables->find('all',['conditions'=>['Mtables.customer_name' => $customer_name]])->toArray();
				 $applicant_pan = $applicant_pan_no[0]['pan_no'];
				 $conditions['conditions']['or']['pan_no'] = $applicant_pan;
				 $conditions['conditions']['or']['customer_name LIKE'] = '%'.$customer_name.'%';
			
			}
		}else{
			$customer_name = 'all';
		}
		  if(isset($this->request->query['fname']) && !empty($this->request->query['fname'])){
		  $fname = $this->request->query['fname']; 
		  $comname = $this->Filenames->find('all',['conditions'=>['Filenames.id' => $fname]])->toArray();
		  $companyname = $comname[0]['fname'];
		  if ($fname && $fname != 'all'){ $conditions['conditions']['csvid'] = "$fname"; }
		  }else{
					$companyname = '';
					
				}
	  /*if(!empty($this->request->query['reconciliation']) && $this->request->query['reconciliation']!= 'all'){
        $conditions['conditions']['reconciliation'] = $this->request->query['reconciliation'];
      }*/
      if(!empty($this->request->query['reconciliation']) && $this->request->query['reconciliation']!= 'all'){
          if($this->request->query['reconciliation'] != 'Brokerage Not Received'){
             $conditions['conditions']['reconciliation'] = $this->request->query['reconciliation'];
          }else{
               if($conditions['conditions']['reconciliation IS'] = null){
                    $conditions['conditions']['reconciliation IS'] = 'Brokerage Not Received';
               }
           
          }
      }
      
	  if($this->Auth->user('group_id') > 2){  
        $conditions['conditions']['branch_id'] = $this->Auth->user('branch_id');    
	  }
	  if(!empty($this->request->query['institution_name']) && $this->request->query['institution_name']!= 'all'){
        $conditions['conditions']['institution_name LIKE'] = '%'.$this->request->query['institution_name'].'%';
      }
      
      if(!empty($this->request->query['branch']) && $this->request->query['branch']!= 'all'){
        $conditions['conditions']['branch_id'] = $this->request->query['branch'];
      }
		
	  if(!empty($this->request->query['export']) && ($this->request->query['export']=='pdf') ){
        $mdatas = $this->Mtables->find('all',$conditions)->order(['Mtables.maturity_date' => 'asc'])->toArray() ;
      }else{
        $order=$this->paginate['order'] = array('Mtables.maturity_date'=>'asc'); 
		$mdatas = $this->paginate('Mtables', $conditions);
      }
        //$orders = $this->paginate['order'] = array('Mtables.application_date' => 'ASC');
         //$mdatas = $this->paginate('Mtables',$conditions);
      
		$userid = $this->Auth->user('id');
		$filenames = $this->Filenames->find('list',['keyField'=>'id','valueField'=>'fname'])->order(['fname' => 'ASC'])->toArray();

		$this->Search->addFilter('despatchdate', [
            'attributes'=>['placeholder'=>'Despatch Date','label'=>'Despatch Date :','class'=>'searchfrm','value' => isset($this->request->query['despatchdate'])?$this->request->query['despatchdate']:''],
        ]);

         $this->Search->addFilter('inv_datedform', [
            'attributes'=>['placeholder'=>'Investment Start Date','label'=>'Investment Start Date :','class'=>'searchfrm','value' => isset($this->request->query['inv_datedform'])?$this->request->query['inv_datedform']:''],
        ]);
        
        $this->Search->addFilter('inv_datedto', [
            'attributes'=>['placeholder'=>'Investment End Date','label'=>'Investment End Date :','class'=>'searchfrm','value' => isset($this->request->query['inv_datedto'])?$this->request->query['inv_datedto']:''],
        ]);
        
        $branches = $this->Branches->find('list', ['limit' => 200])->toArray();
		
        $this->set(compact('mdatas','customer_name','datedform','datedto','dated2','dated1','companyname','userid','custname','filenames','branches'));

        if(!empty($this->request->query['export']) && ($this->request->query['export']=='pdf') ){
            $fileName = "brkreconciliationdata_".date("d-m-y:h:s");
            $this->set('filename', $fileName);
            if(!empty($this->request->query['export']=='pdf')){
                $this->render('export_brkreconciliation_pdf','export_pdf');
            }
        }
	}
	
	public function brfdedit($id = null){
		$this->LoadModel("Mtables");
		$this->LoadModel("Mutualfunds");
		$this->LoadModel("Filenames");
		$fds = $this->Mtables->get($id, [
            'contain' => []
        ]);
		
		 if ($this->request->is(['patch', 'post', 'put'])) {
			 $this->request->data['modifiedby'] = $this->Auth->user('id');
			 
			 if(!empty($this->request->data['branch_id']) &&  $this->request->data['branch_id'] != NULL){
				
			 $this->request->data['verified'] = 1;
			 }
			
            $fds = $this->Mtables->patchEntity($fds, $this->request->data);
			
            if ($this->Mtables->save($fds)) {
                $this->Flash->success(__('The FD data has been Updated.'));

                return $this->redirect(['action' => 'fdimportdata']);
            } else {
                $this->Flash->error(__('The FD data could not be Updated. Please, try again.'));
            }
        }
		 $subbrokers = $this->Fds->Brokers->find('list', ['keyField' => 'id','valueField' => 'name'])-> where(['role' => 'Subbroker']);
		 $branches = $this->Mutualfunds->Branches->find('list', ['limit' => 200]);
		 $filenames = $this->Filenames->find('list',['keyField'=>'id','valueField'=>'fname'])->order(['fname' => 'ASC'])->toArray();
		 //$paymentmode  = array('cheque'=>'Cheque','dd'=>'DD','rtgs'=>'RTGS','renewal'=>'Renewal');
		 $this->set(compact('fds','branches','subbrokers','filenames'));
		 //$this->request->data = $fds;
		
    }
	function brfdview($id = null){
		$this->LoadModel("Mtables");
		$this->LoadModel("Cheaders");
		$datas = $this->Mtables->get($id);
		$rowid = $id;
		
		$chid = $datas->csvid;
		$isverified = $datas->verified;
	   
		$gdatas = $this->Cheaders->find('all', [ 
			'conditions' => ['fid' => $chid]
		  ])->toArray();
	  
		$this->set('gdatas', $gdatas);
		$this->set('rowid', $rowid);
		$this->set('chid', $chid);
		$this->set('isverified', $isverified);

	}
	
	public function brkreconciliationexceldata(){
	$this->loadModel('Mtables');
	$this->loadModel('Filenames');
    $filenames = $this->Filenames->find('list',['keyField'=>'id','valueField'=>'fname'])->order(['fname' => 'ASC'])->toArray();
	$fileName = "fdexcelmaturitydata_".date("d-m-y:h:s").".xls";
	$conditions = array();
	
	if((!empty($this->request->query['datedform'])) ||(!empty($this->request->query['datedto']))){ 
			$dated2 =  (($this->request->query['datedform'] != '')? $this->request->query['datedform'] : '');
			$date_from = explode('/',$dated2);
			$datedform  = implode('-', array_reverse($date_from));
			$dated1= (($this->request->query['datedto']!='')? $this->request->query['datedto']:'');
			$date_to = explode('/',$dated1);
			$datedto  = implode('-', array_reverse($date_to));	
			if ($datedform && $datedto){
				if($datedform == $datedto){
					$adt = new \DateTime($datedto);
					$datedto = $adt->format('Y-m-d');        
				}
			$conditions = array('conditions' => array('and' => array(array('Mtables.maturity_date >= ' => $datedform,'Mtables.maturity_date <= ' => $datedto))));
			} 
        }else{
            if((empty($this->request->query['inv_datedform'])) ||(empty($this->request->query['inv_datedto']))){ 
					$dated2 = date('01/m/Y', strtotime('+1 month'));
					$datedform = date('Y-m-01', strtotime('+1 month'));	
					$datedfrom  = date('Y-m-01', strtotime('+1 month'));
					$dated1 = date('t/m/Y', strtotime('+1 month'));
					$datedto  = 	date('Y-m-t', strtotime('+1 month'));
					$conditions = array('conditions' => array('and' => array(array('Mtables.maturity_date >= ' => $datedfrom,'Mtables.maturity_date <= ' => $datedto))));
					$this->request->query['datedform']= $dated2;
					$this->request->query['datedto'] = $dated1;
			} 
			   
		}
		
		if((!empty($this->request->query['inv_datedform'])) ||(!empty($this->request->query['inv_datedto']))){ 
			$invdated2 =  (($this->request->query['inv_datedform'] != '')? $this->request->query['inv_datedform'] : '');
			$invdate_from = explode('/',$invdated2);
			$invdatedform  = implode('-', array_reverse($invdate_from));
			$invdated1= (($this->request->query['inv_datedto']!='')? $this->request->query['inv_datedto']:'');
			$invdate_to = explode('/',$invdated1);
			$invdatedto  = implode('-', array_reverse($invdate_to));	
			if ($invdatedform && $invdatedto){
				if($invdatedform == $invdatedto){
					$invadt = new \DateTime($invdatedto);
					$invdatedto = $invadt->format('Y-m-d');        
				}
			$conditions['conditions'][] = array('and' => array(array('Mtables.application_date >= ' => $invdatedform,'Mtables.application_date <= ' => $invdatedto)));
			} 
        }	
		
		if((!empty($this->request->query['despatchdate']))){ 
			$desdate =  (($this->request->query['despatchdate'] != '')? $this->request->query['despatchdate'] : '');
			$despatchdate = explode('/',$desdate);
			$despatchdate  = implode('-', array_reverse($despatchdate));
			if ($despatchdate){
					$adt = new \DateTime($despatchdate);
					$despatchdate = $adt->format('Y-m-d');        
			$conditions['conditions'][] = array('and' => array(array('Mtables.despatchdate = ' => $despatchdate)));
			}
		}
	
	  if (isset($this->request->query['keyword'])){
			 $query =    $this->request->query['keyword'];
			 $conditions['conditions']['or']['Mtables.customer_name LIKE'] = "%$query%";
			 }
	
	
	  if (!empty($this->request->query['customer_name']) && $this->request->query['customer_name'] !='all'){
        $customer_name =    $this->request->query['customer_name'];
        $conditions['conditions']['Mtables.customer_name'] = $customer_name;
      }
	  
	  if(isset($this->request->query['fname'])){
	  $fname = $this->request->query['fname']; 
	  if ($fname && $fname != 'all'){ $conditions['conditions']['csvid'] = "$fname"; }
	  }
	  
	  if(isset($this->request->query['institution_name'])){
        $institution_name = $this->request->query['institution_name'];
        if ($institution_name && $institution_name != 'all'){ $conditions['conditions']['institution_name'] = "$institution_name"; }
      }
	  
	  
	  if($this->Auth->user('group_id') > 2)
	  {
           $conditions['conditions']['branch_id'] = $this->Auth->user('branch_id'); 
	  }
	  if(!empty($this->request->query['branch']) && $this->request->query['branch']!= 'all'){
        $conditions['conditions']['branch_id'] = $this->request->query['branch'];
      }
      if(!empty($this->request->query['reconciliation']) && $this->request->query['reconciliation']!= 'all'){
        $conditions['conditions']['reconciliation'] = $this->request->query['reconciliation'];
      }
      
	    $mdatas = $this->Mtables->find('all',$conditions);
		$mdatas->order(['Mtables.maturity_date' => 'asc']); 
		 $datafds = $mdatas->toArray(); 
		 
		$mbranches = TableRegistry::get('Branches');
		$musers = TableRegistry::get('Users');
		$brokers = TableRegistry::get('Brokers');
        $headerRow = array('Sr No', 'Investor Name','Company Name','Institution Name','Investment Date' ,'Investment Amount',  'Tenure', 'Maturity Date','Investment Branch','Brokerage');
        $j=1;
            for($i=0; $i< count($datafds); $i++){
				if($datafds[$i]->branch_id > 0){
                            $branchid = $mbranches->find()->where(['id' => $datafds[$i]->branch_id]);
                            $data1 = $branchid->toArray();
                            $branchname = $data1[0]->branchname;
                        }else{
                            $branchname = '';
                        }
				if($datafds[$i]->modifiedby > 0){
                            $modifiedid = $musers->find()->where(['id' => $datafds[$i]->modifiedby]);
                            $data1 = $modifiedid->toArray();
                            $modifiedname = $data1[0]->firstname;
                        }else{
                            $modifiedname = '';
                        }
				if($datafds[$i]->sub_broker > 0){
                            $brokerid = $brokers->find()->where(['id' => $datafds[$i]->sub_broker]);
                            $data1 = $brokerid->toArray();
                            $brokername = $data1[0]->name;
                        }else{
                            $brokername = '';
                        }		

                $format_of = !empty($filenames[$datafds[$i]->csvid]) ? $filenames[$datafds[$i]->csvid] :  "";
                $data[] = [$j, $datafds[$i]->customer_name, $format_of,$datafds[$i]->institution_name, $datafds[$i]->application_date, $datafds[$i]->investment_amount, $datafds[$i]->period, $datafds[$i]->maturity_date,$branchname,$datafds[$i]->reconciliation];
            $j++;
                } 
               
        $this->ExportXls->export($fileName, $headerRow, $data);
	
	}
	
	/*Mutual Fond*/

public function mutualfnd(){
        $conditions = array(); 
        if (!empty($this->request->query) && count($this->request->query) > 1) {

            $dated2 = (($this->request->query['datedform'] != '') ? $this->request->query['datedform'] : '');
            $date1 = explode('/', $dated2);
            $datedform = implode('-', array_reverse($date1));
            $dated1 = $this->request->query['datedto'];
            $date = explode('/', $dated1);
            $datedto = implode('-', array_reverse($date));
            if ($datedform && $datedto) {
                $conditions['conditions'] =  array(
                        'and' => array(
                            array('DATE(Fds.created) >= ' => $datedform,
                                'DATE(Fds.created) <= ' => $datedto
                            )
                        ));
            }
        }
        if($this->Auth->user('group_id')=='3'){        
            $branch_id = $this->Auth->user('branch_id');     
            $conditions['conditions']['and']['Fds.posteduserbranchid'] = "$branch_id";
        }elseif($this->Auth->user('group_id')=='4' || $this->Auth->user('group_id')=='5' || $this->Auth->user('group_id')=='6' || $this->Auth->user('group_id')=='7'){ 
            $branch_id = $this->Auth->user('branch_id');    
            $userid = $this->Auth->user('id'); 
            $conditions['conditions']['and']['Fds.posteduserbranchid'] = "$branch_id";
            $conditions['conditions']['and']['Fds.postedby'] = "$userid";
        }

        $this->paginate = [
            'contain' => ['Companies', 'Branches','Brokers']
        ];
        
        $this->Search->addFilter('datedform', [
            'attributes'=>['placeholder'=>'Start Date','label'=>'Start Date :','class'=>'searchfrm','value' => isset($this->request->query['datedform'])?$this->request->query['datedform']:''],
        ]);
        $this->Search->addFilter('datedto', [
            'attributes'=>['placeholder'=>'End Date','label'=>'End Date :','class'=>'searchfrm','value' => isset($this->request->query['datedto'])?$this->request->query['datedto']:''],
        ]);

        $fds = $this->Fds->find('all', $conditions);
		
        $cnt = $fds->count();              
    		if($fds){
    			$total_amount = 0;                   
    			 foreach ($fds as $fd){ 
    				$total_amount += $fd->amount;
            }
        } 
        $fds = $this->paginate($fds,['order' => ['Fds.id' => 'DESC']]);
        $this->set(compact('fds','total_amount','cnt'));
        $this->set('_serialize', ['fds']);//print_r($this->set('_serialize', ['fds']));
    }
	public function mfadd(){
		
         $this->loadModel('Filenames');
        
        if ($this->request->is('ajax','get')) {         
            /*$terms = $this->Fds->Customers->find('all', [
                'conditions' => array('Customers.firstname LIKE ' => '%' . $this->request->query["term"] . '%'),
                'limit' => 10
            ]);*/
            
            $terms = $this->Fds->Customers->find('all', [
                'conditions' => array('Customers.firstname LIKE ' => '%' . $this->request->query["term"] . '%'),
                'group' => array('Customers.firstname'),
                'order' => array('Customers.firstname ASC'),
                'limit' => 10
            ]);
          
            $data = array();$data1 = array();
            foreach($terms as $term) {
                $row = $term->firstname;
                array_push($data, $row);
            }
            $this->autoRender = false;      
            // $this->layout = 'ajax';
            $this->set('terms', $terms);
            echo json_encode($data);
            exit;
            //return json_encode($data);
         }else {
            //echo json_encode('Nothings found');
         }
         
         
        $fd = $this->Fds->newEntity();
        if ($this->request->is('post')) {
            $fd = $this->Fds->patchEntity($fd, $this->request->data);
            $fd->postedby = $this->Auth->user('id');
            $fd->posteduserbranchid = $this->Auth->user('branch_id');
          //  $fd->branch_id = $this->Auth->user('branch_id');
            if($fd->subbroker_id != ''){
                $fd->broker_id = $fd->subbroker_id;
            }else{
                $fd->broker_id = 1;
            }
            
           $custmoerId = $this->getCustomerId($fd->panno);
           if($custmoerId != ''){
                $fd->customer_id = $custmoerId;
            }
            
            if ($this->Fds->save($fd)) {
                $this->Flash->success(__('The fd has been saved.'));
                
                //-----Auto Synch to Master FD mtables-----------
                $this->LoadModel("Mtables");
                $this->LoadModel("Mutualfunds");
                $this->loadModel('Filenames');
                $this->loadModel('Companies');
                $filenames = $this->Filenames->find('list',['keyField'=>'id','valueField'=>'fname'])->order(['fname' => 'ASC'])->toArray();
                //echo $fd_id;exit;
                $productcompanies = $this->Fds->Companies->find('list')-> where(['associatewith' => 'product'])->where(['productid LIKE' => '%2%'])->order(['company' => 'ASC'])->toArray();
                //pr($productcompanies);exit;

                $fdtable_fdid = $fd->id;
                $fdtable_fd = array();
                $fds = array();
                if(!empty($fdtable_fdid)){

                    $is_exists = $this->Mtables->find('all', array('conditions'=>array('Mtables.fdtable_fdid'=>$fdtable_fdid)));
                    $number = $is_exists->count();
                    if ($number > 0){
                        //$this->Flash->error(__('FD Already Exist. on Master FD '));
                        return $this->redirect(['action' => 'index']);
                    }

                    $fdtable_fd = $this->Fds->get($fdtable_fdid, [
                        'contain' => ['Companies','Plans']
                    ])->toArray();
                    if(!empty($fdtable_fd)){

                        $fds['csvid'] = $fdtable_fd['filenameid'];
                        $fds['customer_name'] = $fdtable_fd['fapplicantname'];
                        $fds['pan_no'] = $fdtable_fd['panno'];
                        $fds['application_no'] = $fdtable_fd['applicationno'];
                        $fds['typeofdeposit'] = $fdtable_fd['typeofdeposit'];
                        $fds['application_date'] = $fdtable_fd['planstartdate'];
                        $fds['cheque_no'] = $fdtable_fd['chequeno'];
                        $fds['utr_fdr_no'] = $fdtable_fd['fdrno'];
                        $fds['investment_amount'] = $fdtable_fd['amount'];
                        //As per the zoom discussion 01-12-2021
                        if($fdtable_fd['typeofdeposit']=='both'){
                            if(!empty($fdtable_fd['fdramount'])){
                                $fds['investment_amount'] = $fdtable_fd['amount'] + $fdtable_fd['fdramount'];
                            }
                        }
                        $fds['period'] = $fdtable_fd['tenure'];
                        $fds['maturity_date'] = $fdtable_fd['maturitydate'];
                        //$fds['address'] = '';
                        $fds['product_name'] = $fdtable_fd['plan']['Planname'];
                        $fds['institution_name'] = !empty($productcompanies[$fdtable_fd['productcompanyid']]) ? $productcompanies[$fdtable_fd['productcompanyid']] : '' ;
                        $fds['branch_id'] = $fdtable_fd['branch_id'];
                        $fds['sub_broker'] = $fdtable_fd['subbroker_id'];
                        //$fds['email'] = '';

                        $fds['fdtable_fdid'] = $fdtable_fd['id'];

                        $fds['despatchdate'] = $fdtable_fd['despatchdate'];


                        $fds['modifiedby'] = $this->Auth->user('id');
                        $fds['verified'] = 1;
                        $fds['manually_added'] = 1;

                        $fds = $this->Mtables->newEntity($fds);
                        //pr($fds);exit;

                        if ($this->Mtables->save($fds)) {

                        } else {
                            $this->Flash->error(__('The FD data could not be Synch to Master FD. Please, try again.'));
                        }
                    }
                    

                }
                //pr($fdtable_fd);exit;
                //-----Auto Synch to Master FD mtables End-----------

                return $this->redirect(['action' => 'index']);
            } else {
                $this->Flash->error(__('The fd could not be saved. Please, try again.'));
            }
        }
        $users = $this->Fds->Customers->find('list')->order(['firstname' => 'ASC']);
     
        $subbrokers = $this->Fds->Brokers->find('list', [
            'keyField' => 'id',
            'valueField' => 'name'
        ])-> where(['role' => 'Subbroker']);
        $mainbrokers = $this->Fds->Brokers->find('list', [
            'keyField' => 'id',
            'valueField' => 'name'
        ])-> where(['role' => 'Mainbroker']);
        
        $Filenames = $this->Filenames->find('list', [
            'keyField' => 'id',
            'valueField' => 'fname'
        ])->order(['fname' => 'ASC']);
        
        $usercompanies = $this->Fds->Companies->find('list')-> where(['associatewith' => 'user'])->order(['company' => 'ASC']);
    	$userbranches = $this->Fds->Branches->find('list', ['limit' => 200])->order(['branchname' => 'ASC']);
        //$productcompanies = $this->Fds->Companies->find('list')-> where(['associatewith' => 'product'])->order(['company' => 'DESC']);
        $productcompanies = $this->Fds->Companies->find('list')-> where(['associatewith' => 'product'])->where(['productid LIKE' => '%2%'])->order(['company' => 'ASC']);
        $plans = $this->Fds->Plans->find('list')-> where(['product_id' => 2])->order(['planname' => 'ASC']);
        
        
        //$subbrokers = $this->Fds->Subbrokers->find('list', ['limit' => 200]);
        $banks = $this->Fds->Banks->find('list', ['limit' => 200])->order(['banks' => 'ASC']);
        
        $typeofdeposit = array('fresh'=>'Fresh','renewal'=>'Renewal','both'=>'Both');
       
        
        $this->set(compact('Filenames','fd','users','mainbrokers','productcompanies', 'usercompanies', 'userbranches', 'plans', 'subbrokers', 'banks', 'bankdetails', 'typeofdeposit'));
       // $this->set(compact('fd', 'companies', 'branches', 'plans', 'subbrokers', 'banks', 'bankdetails'));
        $this->set('_serialize', ['fd']);
    }
    
    
    public function mergeinvestors(){
        
		
		$this->loadModel('Filenames');
		$this->loadModel('Mtables');
		$this->loadModel('Branches');
		$this->loadModel('Brokers');
		$all = array('all'=>'All');
		
		//pr($this->request->data); pr($this->request->is('post'));
		if ($this->request->data) {
            $data = $this->request->data; 
           $data['updatedby'] = $this->Auth->user('id');
            $data['updatedon'] = date('Y-m-d H:i:s');
			$data['merged'] = 1;
			if(!empty($this->request->data['branch_id'])){
				$data['branch_id'] = $this->request->data['branch_id'];
			}
			if(!empty($this->request->data['subbroker_id'])){
				$data['subbroker_id'] = $this->request->data['subbroker_id'];
			}
			
			if(!empty($this->request->data['pan_no'])){
				$data['pan_no'] = $this->request->data['pan_no'];
			}
			
			if(!empty($this->request->data['email'])){
				$data['email'] = $this->request->data['email'];
			}
			
			if(!empty($this->request->data['mobileno'])){
				$data['mobileno'] = $this->request->data['mobileno'];
			}
			//pr( $data ); //die();
			if (!empty($this->request->data['updateAllValue'])){	

				$custData = !empty($this->request->data['updateAllValue']) ? explode(",", $this->request->data['updateAllValue']):array();
				
				//pr($custData); //die();
				$mids = $cids = array();
				foreach($custData as $key => $cust){
					$cond = array();
					$custArray = explode('+', $cust);
					$cond['customer_name'] = $custArray[0]; [['Area_Block IS NOT' => null], ['Area_Block IS NOT' => '']];
				/*	if(empty($custArray[1])){
						$cond['pan_no IS'] = null;
					}else{
						$cond['pan_no'] = $custArray[1];
					} */
					//pr($cond);
					$mds = $this->Mtables->find('list',['keyField'=>'id','valueField'=>'customer_id','conditions'=>$cond])->toArray();
					$mids = array_merge($mids,array_keys($mds));
					$cids = array_merge($cids,array_values($mds));
				}
				$mids = array_filter(array_unique($mids)); //pr($mids );
				$cids = array_filter(array_unique($cids));
				
				$ids = $this->request->data('data.AssetsAssignations.id');
				$new_customer_name = $data['new_customer_name'];
				$updatedby = $data['updatedby'];
				$updatedon = $data['updatedon'];
				
				$mtableFieldsArr = ["merged" => 1,"updatedby" => $updatedby,"updatedon" => $updatedon,'customer_name' => $new_customer_name];
				
				$custFieldsArr = ['firstname' => $new_customer_name];
				
				if(!empty($data['branch_id'])){
					$mtableFieldsArr['branch_id'] = $custFieldsArr['branch_id'] = $data['branch_id'];
				}
				
				if(!empty($data['subbroker_id'])){
					$mtableFieldsArr['sub_broker']  = $data['subbroker_id'];
				}
				
				if(!empty($data['pan_no'])){
					$mtableFieldsArr['pan_no'] = $custFieldsArr['panno'] = $data['pan_no'];
				}
				
				if(!empty($data['email'])){
					$mtableFieldsArr['email'] = $custFieldsArr['email'] = $data['email'];
				}
				
				if(!empty($data['mobileno'])){
					$mtableFieldsArr['mobileno'] = $custFieldsArr['phoneno'] = $data['mobileno'];
				}
				
				if(!empty($mids)){
					$this->loadModel('Mtables');
					$test = $this->Mtables->updateAll(
						$mtableFieldsArr,
						["id IN" => $mids]
						); 
						//pr($test);
				}
				if(!empty($cids)){
					$this->loadModel('Customers');
					$test1 =  $this->Customers->updateAll(
						$custFieldsArr,
						["id IN" => $cids]
						); //pr($test1);
				}

				$this->Flash->success(__('Data has been merged successfully.'));
				
			} 
          
        }
		
		$this->Search->addFilter('keyword', ['attributes' => ['placeholder' => 'Keyword', 'label' => 'Keyword :', 'class' => 'searchfrm serachfrm_clearable','value'=>isset($this->request->query['keyword'])?$this->request->query['keyword']:'']]);
		
		$this->Search->addFilter('pan_no', ['attributes'=>['placeholder'=>'Pan No','label'=>'Pan No :','class'=>'searchfrm serachfrm_clearable','value'=>!empty($this->request->query['pan_no'])?$this->request->query['pan_no']:'']]);
		
		$this->Search->addFilter('merge_options', ['attributes'=>['placeholder'=>'','label'=>'Display :','class'=>'searchfrm','value'=>!empty($this->request->query['merge_options'])?$this->request->query['merge_options']:0, 'options' => array('A' => 'All', 'U'=>'Unmerged Data Only', 'M'=>'Merged Data Only')] ]);

		$this->Search->addFilter('check_investor_amt', ['attributes'=>['placeholder'=>'','label'=>'Filter By Investment Amt :','type'=>'checkbox', 'class'=>'searchfrm','checked'=>!empty($this->request->query['check_investor_amt'])? true:false]]);
		
       
        
        $branches = $this->Branches->find('list', ['limit' => 200]);
        
        $subbrokers = $this->Brokers->find('list', [
            'keyField' => 'id',
            'valueField' => 'name'
        ])-> where(['role' => 'Subbroker']);
	
	  $conditions = array();
	  
	  
	  
	 
		
	
		
		if (isset($this->request->query['keyword'])) {
            $query = $this->request->query['keyword'];
            $keyword = $this->request->query['keyword'];
            $keywords = explode(' ',$keyword);
            if(count($keywords) > 1){
                $conditions['conditions']['Mtables.customer_name LIKE'] = "$keyword%";
            }else{
                $conditions['conditions']['Mtables.customer_name LIKE'] = "%$keyword%";
            }
        }else{
            $keyword = '';
        }
        
        if(isset($this->request->query['pan_no'])){
            $applicant_pan = $this->request->query['pan_no'];
            if(!empty($this->request->query['pan_no'])){
                $conditions['conditions']['pan_no'] = $applicant_pan;
            }
        }else{
            $applicant_pan = '';
        }
        
        if(!empty($this->request->query['merge_options'])){
            if($this->request->query['merge_options'] == 'U'){
                $conditions['conditions']['merged'] = 0;
            }elseif($this->request->query['merge_options'] == 'M'){
                $conditions['conditions']['merged'] = 1;
            }
        }
	  
	  
	 

	  //$conditions['conditions']['verified'] = 0;
      $userid = $this->Auth->user('id');
		//$order=$this->paginate['order'] = array('Mtables.id'=>'desc');
		$order=$this->paginate['order'] = array('Mtables.pan_no'=>'asc','Mtables.customer_name'=>'asc');
		$contain=$this->paginate['contain'] = array('Branches', 'Brokers');
		$group=$this->paginate['group'] = array('Mtables.customer_name','Mtables.pan_no');
		if(!empty($this->request->query['check_investor_amt'])){
		    //$check_investor_amt = array('Mtables.investment_amount');
		    $group=$this->paginate['group'] = array('Mtables.customer_name','Mtables.pan_no','Mtables.investment_amount');
		}
		$this->paginate['limit'] = 50;
		//pr($conditions);
		$mdatas = $this->paginate('Mtables', $conditions); //pr($mdatas);
		$filenames = $this->Filenames->find('list',['keyField'=>'id','valueField'=>'fname'])->order(['fname' => 'ASC'])->toArray();
        $this->set(compact('mdatas', 'branches', 'subbrokers', 'userid','datedform','datedto','dated2','dated1','applicant_pan','keyword','filenames'));
    }
    
    	  //Brokerage Reconcilation update
	  
	  public function updatereconciliation(){
        $result = array();
        $this->loadModel('Mtables');
        $conditions = array();

        if (isset($this->request->data['reconciliation']) && $this->request->data['reconciliation'] != ''){
         $reconciliation = $this->request->data['reconciliation'];
        }else{
         $reconciliation = NULL;
        }
        
        if (isset($this->request->data['rid']) && $this->request->data['rid'] != ''){
            $rid = $this->request->data['rid'];
        }else{
            $rid = '';
        }

        if (isset($this->request->data['userid']) && $this->request->data['userid'] != ''){
            $userid = $this->request->data['userid'];
        }else{
            $userid = '';
        }
        
        $result = array();
        
        //if($reconciliation != '' and $rid != ''){
        if($rid != ''){
            $fds = $this->Mtables->get($rid, [
                'contain' => [],
            ]);

            $this->request->data['reconciliationupdatedby'] = $userid;
            $this->request->data['reconciliation'] = $reconciliation;
            $fds = $this->Mtables->patchEntity($fds, $this->request->data);
            $this->Mtables->save($fds);
            $result[] = 'success';
        }
        $this->set('result', $result);
      }
     
     // Email send on fd maturity
     
       public function email(){
       	$this->loadModel('Mtables');
       	$this->loadModel('Branches');
		if (isset($this->request->data['email']) && $this->request->data['email'] != ''){
		 $email1 = $this->request->data['email'];
		}
		if (isset($this->request->data['rid']) && $this->request->data['rid'] != ''){
		 $rid = $this->request->data['rid'];
		}
		if (isset($this->request->data['userid']) && $this->request->data['userid'] != ''){
		 $userid = $this->request->data['userid'];
		}
		$result = array();
	    $mdatas = $this->Mtables->find('all')->where(['id' => $rid]);
        $data11 = $mdatas->toArray();
        $customer_name = $data11[0]->customer_name;
        $maturity_date = $data11[0]->maturity_date;
        $institution_name = $data11[0]->institution_name;
        $investment_amount = $data11[0]->investment_amount;
        $period = $data11[0]->period;
        $application_date = $data11[0]->application_date;
        $branch_id = $data11[0]->branch_id;
		$bdatas = $this->Branches->find('all')->where(['id' => $branch_id]);
		$branch_id2 = $bdatas->toArray();
		$branchname = $branch_id2[0]->branchname;
		$branchaddress = $branch_id2[0]->branchaddress;
		$msg = '';
        $email = new Email('default');
		$email->template('fdemail')
		->emailFormat('html')
		->to($email1)
		->bcc('smalution2013@gmail.com')
		->from(['feedback@abninvestments.com' => 'ABN Investments'])
		->subject('Fixed Deposit Maturity/Renewal Intimation')
		->viewVars(['branchaddress'    => $branchaddress, 'application_date'    => $application_date, 'customer_name'    => $customer_name, 'maturity_date'    => $maturity_date, 'institution_name'    => $institution_name, 'investment_amount'    => $investment_amount, 'period'    => $period])
		->send($msg);
       
		 if($email1 != '' and $rid != ''){
            $emlfd = $this->Mtables->get($rid, [
                'contain' => [],
            ]);
            $this->request->data['emailupdatedby'] = $userid;
            $emlfd = $this->Mtables->patchEntity($emlfd, $this->request->data);
            $this->Mtables->save($emlfd);
            $result[] = 'success';
        }
		 $this->set('result', $result);
    }
    public function emailpdf(){
		$this->loadModel('Mtables');
		$this->loadModel('Branches');
		if (isset($this->request->data['email']) && $this->request->data['email'] != ''){
		 $email1 = $this->request->data['email'];
		}
		if (isset($this->request->data['rid']) && $this->request->data['rid'] != ''){
		 $rid = $this->request->data['rid'];
		}
		if (isset($this->request->data['userid']) && $this->request->data['userid'] != ''){
		 $userid = $this->request->data['userid'];
		}
		$result = array();
		
	    $mdatas = $this->Mtables->find('all')->where(['id' => $rid]);
        $data11 = $mdatas->toArray();
        $customer_name = $data11[0]->customer_name;
        $maturity_date = $data11[0]->maturity_date;
        $institution_name = $data11[0]->institution_name;
        $investment_amount = $data11[0]->investment_amount;
        $period = $data11[0]->period;
        $application_date = $data11[0]->application_date;
         $branch_id = $data11[0]->branch_id;
		$bdatas = $this->Branches->find('all')->where(['id' => $branch_id]);
		$branch_id2 = $bdatas->toArray();
	//	$branchname = $branch_id2[0]->branchname;
		$branchaddress = $branch_id2[0]->branchaddress;
		$this->set(compact('branchaddress','customer_name','maturity_date','institution_name','investment_amount','period','application_date'));
		$this->set('filename', 'Email_'.$rid);
		$this->render('export_email_pdf','export_manualsendpdf');
		
	 }
     public function emailreport(){
		$this->fdmaturity();
	}
	public function getcustinfo(){
        $id = $this->request->query('id');
        if (!$id) {
            throw new NotFoundException();
       }
			
        $query = $this->Fds->Customers->find('all',['conditions' => array('trim(Customers.panno) LIKE ' =>  $id),
            'group' => array('Customers.firstname','Customers.panno'),
            'order' => array('Customers.firstname ASC')
        ]);
        $data = $query->toArray();       
        $this->set(compact('data')); 
    }
    
   //send mail on FD entry
	
	public function viewsendemail($id = null){
		$fd = $this->Fds->get($id, [
            'contain' => ['Companies','Brokers','Customers']
        ]); 
		$companies = TableRegistry::get('Companies');
		$branches = TableRegistry::get('Branches');
		$plans = TableRegistry::get('Plans');
		$banks = TableRegistry::get('Banks');
		$users = TableRegistry::get('Users');
		$brokers = TableRegistry::get('Brokers');
		 if($fd->productcompanyid > 0) {
			$productcompanyid = $companies->find()->where(['id' => $fd->productcompanyid]);
			$data1 = $productcompanyid->toArray();
			$productcompanyName = $data1[0]->company;
			}
			else{
			$productcompanyName = '';
			}
			$this->set('productcompanyName',$productcompanyName);
			
		if($fd->mainbrokerid > 0) {
			$mainbrokerid = $brokers->find()->where(['id' => $fd->mainbrokerid]);
			$data1 = $mainbrokerid->toArray();
			$mainbrokerName = $data1[0]->name;
			}
			else{
			$mainbrokerName = '';
			}
			$this->set('mainbrokerName',$mainbrokerName);	
		if($fd->plan_id > 0) 
		{
			$planid = $plans->find()->where(['id' => $fd->plan_id]);
			$data2 = $planid->toArray();
			$planname = $data2[0]->Planname;
		}
		else{
		$planname = '';
		}
		$this->set('planname',$planname);
		if($fd->bank_id > 0)
		{
			$bank_id = $banks->find()->where(['id' => $fd->bank_id]);
			$data3 = $bank_id->toArray();
			$bankname = $data3[0]->banks;
		}
		else{
		$bankname = '';
		}
		$this->set('bankname',$bankname);
		if($fd->branch_id > 0) {
			$branchid = $branches->find()->where(['id' => $fd->branch_id]);
			$data4 = $branchid->toArray();
			$branchname = $data4[0]->branchname;
		}
		else
		{
			$branchname = '';
		}
		$this->set('branchname', $branchname);

		if($fd->posteduserbranchid > 0) {
			$posteduserbranchid = $branches->find()->where(['id' => $fd->posteduserbranchid]);
			$data4 = $posteduserbranchid->toArray();
			$posteduserbranch = $data4[0]->branchname;
		}
		else
		{
			$posteduserbranch = '';
		}
		$this->set('posteduserbranch', $posteduserbranch);
		 if($fd->postedby > 0){
            $postedby = $users->find()->where(['id' => $fd->postedby]);
            $data11 = $postedby->toArray();
            $userName = $data11[0]->username;
       }else{
           $userName = '';
       }    
            $this->set('userName', $userName);
       
        $subbrokers = $this->Fds->Brokers->find('list', [
            'keyField' => 'id',
            'valueField' => 'name'
        ])-> where(['role' => 'Subbroker'])->toArray();
        
        
       $userid = $this->Auth->user('id');
        $this->set(compact('subbrokers','userid'));
        //$fd = $this->Fds->get($id);
        $this->set('fd', $fd);
        $this->set('_serialize', ['fd']);
	}
	 public function emailsentfdentry(){
       	$this->loadModel('Mtables');
       	$this->loadModel('Fds');
       	$this->loadModel('Customers');
       	$this->loadModel('Companies');
		if (isset($this->request->data['email']) && $this->request->data['email'] != ''){
		 $email1 = $this->request->data['email'];
		}
		if (isset($this->request->data['rid']) && $this->request->data['rid'] != ''){
		 $rid = $this->request->data['rid'];
		}
		if (isset($this->request->data['userid']) && $this->request->data['userid'] != ''){
		 $userid = $this->request->data['userid'];
		}
		$result = array();
		$fdatas = $this->Fds->find('all')->where(['id' => $rid]);
		$data11 = $fdatas->toArray();
		$tenure = $data11[0]->tenure;
		$amount = $data11[0]->amount;
		$fdrno = $data11[0]->fdrno;
		$chequeno = $data11[0]->chequeno;
		
		//for customer
		$customer_id = $data11[0]->customer_id;
		$cdatas = $this->Customers->find('all')->where(['id' => $customer_id]);
		$customer_id2 = $cdatas->toArray();
		$custname = $customer_id2[0]->firstname;
		
		//Institute Name
		$productcompanyid = $data11[0]->productcompanyid;
		$pdatas = $this->Companies->find('all')->where(['id' => $productcompanyid]);
		$productcompanyid2 = $pdatas->toArray();
		$institute_name = $productcompanyid2[0]->company;

		$msg = '';
        $email = new Email('default');
		$email->template('fdentrymail')
		->emailFormat('html')
		->to($email1)
		->bcc('smalution2013@gmail.com')
		->from(['abninvestment@gmail.com' => 'ABN Investment'])
		->subject('Fixed Deposit Acknowledgement')
		->viewVars(['custname'    => $custname, 'tenure'    => $tenure, 'amount'    => $amount, 'institute_name'    => $institute_name, 'fdrno'    => $fdrno, 'chequeno'    => $chequeno])
		->send($msg);
		
       if($email1 != '' and $rid != ''){
            $emlfd = $this->Fds->get($rid, [
                'contain' => [],
            ]);
            $this->request->data['emailSendBy'] = $userid;
            $emlfd = $this->Fds->patchEntity($emlfd, $this->request->data);
            $this->Fds->save($emlfd);
            $result[] = 'success';
        }
		 $this->set('result', $result);
		 
    } 
    


public function fdexcelimportdata(){

        $this->loadModel('Fds');
        $this->loadModel('Filenames');
        $this->loadModel('Users');
        // $this->loadModel('Companies');
        $filenames = $this->Filenames->find('list',['keyField'=>'id','valueField'=>'fname'])->order(['fname' => 'ASC'])->toArray();
        $fileName = "fdexcelimport_".date("d-m-y:h:s").".xls";
        $conditions = array();
        if((!empty($this->request->query['datedform'])) ||(!empty($this->request->query['datedto']))){ 
			$dated2 =  (($this->request->query['datedform'] != '')? $this->request->query['datedform'] : '');
			$date_from = explode('/',$dated2);
			$datedform  = implode('-', array_reverse($date_from));
			$dated1= (($this->request->query['datedto']!='')? $this->request->query['datedto']:'');
			$date_to = explode('/',$dated1);
			$datedto  = implode('-', array_reverse($date_to));	
			if ($datedform && $datedto){
				if($datedform == $datedto){
					$adt = new \DateTime($datedto);
					$adt->modify('+1 day');
					$datedto = $adt->format('Y-m-d');        
				}
		//	$conditions = array('conditions' => array('and' => array(array('Fds.maturity_date >= ' => $datedform,'Fds.maturity_date <= ' => $datedto))));
			} 
        }else{	

        	  if((empty($this->request->query['inv_datedform'])) ||(empty($this->request->query['inv_datedto']))){
        
                    $dated2 = date('01/m/Y', strtotime('+1 month'));
                    $datedform = date('Y-m-01', strtotime('+1 month'));
                    $datedfrom  = date('Y-m-01', strtotime('+1 month'));
                    $dated1 = date('t/m/Y', strtotime('+1 month'));
                    $datedto  = 	date('Y-m-t', strtotime('+1 month'));
               //     $conditions = array('conditions' => array('and' => array(array('Fds.maturity_date >= ' => $datedfrom,'Fds.maturity_date <= ' => $datedto))));
                    $this->request->query['datedform']= $dated2;
                    $this->request->query['datedto'] = $dated1;
                }
		}
		if((!empty($this->request->query['inv_datedform'])) ||(!empty($this->request->query['inv_datedto']))){
            $invdated2 =  (($this->request->query['inv_datedform'] != '')? $this->request->query['inv_datedform'] : '');
            $invdate_from = explode('/',$invdated2);
            $invdatedform  = implode('-', array_reverse($invdate_from));
            $invdated1= (($this->request->query['inv_datedto']!='')? $this->request->query['inv_datedto']:'');
            $invdate_to = explode('/',$invdated1);
            $invdatedto  = implode('-', array_reverse($invdate_to));
            if ($invdatedform && $invdatedto){
                if($invdatedform == $invdatedto){
                    $invadt = new \DateTime($invdatedto);
                    //$adt->modify('+1 day');
                    $invdatedto = $invadt->format('Y-m-d');
                }
                $conditions['conditions'][] = array('and' => array(array('Fds.application_date >= ' => $invdatedform,'Fds.application_date <= ' => $invdatedto)));
            }
        }
	

	  if (isset($this->request->query['keyword'])){
			 $query =    $this->request->query['keyword'];
			 $conditions['conditions']['or']['Fds.fapplicantname LIKE'] = "%$query%";
			 }
	
	
	 
    if (!empty($this->request->query['fapplicantname']) && $this->request->query['fapplicantname'] !='all'){
        $fapplicantname =    $this->request->query['fapllicantname'];
        $conditions['conditions']['Fds.fapplicantname'] = $fapllicantname;
    //     print_r($conditions);
    //     exit;
       }
	  
	  if(isset($this->request->query['fapplicantname'])){
	  $fapplicantname = $this->request->query['fname'];
	  if ($fapplicantname && $fappplicantname != 'all'){ $conditions['conditions']['csvid'] = "$fapplicantname"; }
	  }
	  

	  if($this->Auth->user('group_id') > 2)
	  {
           $conditions['conditions']['branch_id'] = $this->Auth->user('branch_id'); 
	  }
	  if(!empty($this->request->query['branch']) && $this->request->query['branch']!= 'all'){
        $conditions['conditions']['branch_id'] = $this->request->query['branch'];
      }
	    $mdatas = $this->Fds->find('all',$conditions);
		$mdatas->order(['Fds.id' => 'asc']); 
        $mdatas = $this->paginate($mdatas,['order' => ['Fds.id' => 'DESC']]);
		//order=$this->paginate['order'] = array('Mtables.id'=>'desc');
		//$mdatas = $this->paginate('Mtables', $conditions);
         
		
		 $datafds = $mdatas->toArray(); 
		 
		$mbranches = TableRegistry::get('Branches');
		$musers = TableRegistry::get('Users');
		$brokers = TableRegistry::get('Brokers');
        $companies = TableRegistry::get('Companies');
     
     
        $headerRow = array('Sr No', 'Applicantname','Pan no','Product' ,'Type of Deposit','Cheque No', 'Amount','Tenure','Branch User','Created','Branch','Investor Remark');
        $j=1;
            for($i=0; $i< count($datafds); $i++){
				if($datafds[$i]->branch_id > 0){
                            $branchid = $mbranches->find()->where(['id' => $datafds[$i]->branch_id]);
                            $data1 = $branchid->toArray();
                            $branchname = $data1[0]->branchname;
                        }else{
                            $branchname = '';
                        }
				if($datafds[$i]->modifiedby > 0){
                            $modifiedid = $musers->find()->where(['id' => $datafds[$i]->modifiedby]);
                            $data1 = $modifiedid->toArray();
                            $modifiedname = $data1[0]->fapplicantname;
                        }else{
                            $modifiedname = '';
                        }
				// if($datafds[$i]->sub_broker > 0){
                //             $brokerid = $brokers->find()->where(['id' => $datafds[$i]->sub_broker]);
                //             $data1 = $brokerid->toArray();
                //             $brokername = $data1[0]->name;
                //         }else{
                //             $brokername = '';
                //         }		
                        if($datafds[$i]->productcompanyid > 0) {
                            $productcompanyid = $companies->find()->where(['id' => $datafds[$i]->productcompanyid]);
                            $data1 = $productcompanyid->toArray();
                            $productcompanyName = $data1[0]->company;
                            }
                            else{
                            $productcompanyName = '';
                            //pr($productcompanyName);
                            //exit;
                            }
                            $this->set('productcompanyName',$productcompanyName);
                             //pr($productcompanyName);
                             //exit;

                  

                
				       
        
                
                $data[] = [$j, $datafds[$i]->fapplicantname,$datafds[$i]->panno, $productcompanyName, $datafds[$i]->typeofdeposit,$datafds[$i]->chequeno, $datafds[$i]->amount, $datafds[$i]->tenure,$productcompanyName,$datafds[$i]->created,$branchname,$datafds[$i]->remark];
                // pr($data);
                // exit;                
                $j++;
                    } 
            
               
        $this->ExportXls->export($fileName, $headerRow, $data);


    }
	
	public function whatsapp(){ //pr($this->request->data);
		$this->autoRender = false ;
		
       	$this->loadModel('Mtables');
		if (isset($this->request->data['mobile']) && $this->request->data['mobile'] != ''){
		 $mobile = $this->request->data['mobile'];
		}
		if (isset($this->request->data['mid']) && $this->request->data['mid'] != ''){
		 $mid = $this->request->data['mid'];
		}
		
		if (isset($this->request->data['cid']) && $this->request->data['cid'] != ''){
			$mids = $this->Mtables->find('list')->where(['customer_id' => $this->request->data['cid']])->toArray();
		}
		
		if (isset($this->request->data['userid']) && $this->request->data['userid'] != ''){
		 $userid = $this->request->data['userid'];
		}
		$result = array();     
       
       
		 if($mobile != '' and $mids != ''){
			foreach($mids as $mid){
				$mdata = $this->Mtables->get($mid, [
					'contain' => [],
				]);
				$this->request->data['whatsapp_sentby'] = $userid;
				$mdata = $this->Mtables->patchEntity($mdata, $this->request->data);
				$this->Mtables->save($mdata);
				$result[] = 'success';
			}
        }
    }
}