<?php
	namespace App\Controller;
	use App\Controller\AppController;
	use Cake\ORM\TableRegistry;
	use Cake\Datasource\ConnectionManager;
	class ReportsController extends AppController
	{
		public $components = array('ExportXls');
		
		public function fds(){
			$this->loadModel('Fds'); 
			$this->loadModel('Customers'); 
			$this->loadModel('Banks'); 
			
			$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('company', [
			'attributes'=>['placeholder'=>'Company Name','label'=>'Company Name :','class'=>'searchfrm','value' => isset($this->request->query['company'])?$this->request->query['company']:''],  
            'options' => $all+$this->Fds->Companies->find('list')-> where(['associatewith' => 'product'])->andWhere(['Companies.productid ='=>'2'] )->order(['company' => 'ASC'])->toArray(),
			]);
			$mainbrokers = $this->Fds->Brokers->find('list', [
            'keyField' => 'id',
            'valueField' => 'name'
			])-> where(['role' => 'Mainbroker'])->toArray();
			
			$this->Search->addFilter('mainbrokerid', [
			'attributes'=>['placeholder'=>'Main Broker','label'=>'Main Broker :','class'=>'searchfrm','value' => isset($this->request->query['mainbrokerid'])?$this->request->query['mainbrokerid']:''],  
            'options' => $all+$mainbrokers,
			]);
			$typeofdeposit = array('fresh'=>'Fresh','renewal'=>'Renewal','both'=>'Both');
			$this->Search->addFilter('typeofdeposit', [
			'attributes'=>['placeholder'=>'Type of Deposit','label'=>'Type of Deposit :','class'=>'searchfrm','value' => isset($this->request->query['typeofdeposit'])?$this->request->query['typeofdeposit']:'',],  
			'options' => $all+$typeofdeposit,
			]); 
			$this->Search->addFilter('datedform', [
			'attributes'=>['placeholder'=>'Maturity From Date','label'=>'Maturity From Date :','class'=>'searchfrm','value' => isset($this->request->query['datedform'])?$this->request->query['datedform']:''],
			]);
			$this->Search->addFilter('datedto', [
			'attributes'=>['placeholder'=>'Maturity To Date','label'=>'Maturity To Date :','class'=>'searchfrm','value' => isset($this->request->query['datedto'])?$this->request->query['datedto']:''],
			]); 
			
			$conditions = ''; 
			
			if (!empty($this->request->query) && count($this->request->query) > 1) { 
				if($this->Auth->user('group_id')=='2'){
					if(!empty($this->request->query['branch'])){
						$branch = (($this->request->query['branch'] != '')? $this->request->query['branch'] : '');
						}else{
						$branch = '';   
					}			  
				} 
				$mainbrokerid = $this->request->query['mainbrokerid'];
				$company = $this->request->query['company'];
				$typeofdeposit = $this->request->query['typeofdeposit']; 
				
				$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){             
					$conditions = array(
					'conditions' => array(
					'and' => array(
					array('Fds.maturitydate >= ' => $datedform,
					'Fds.maturitydate <= ' => $datedto
					)
					)));
				}
				if (($mainbrokerid !='') && ($mainbrokerid !='all')){ $conditions['conditions']['and']['Fds.mainbrokerid'] = "$mainbrokerid"; }
				if($this->Auth->user('group_id')=='3'){             
					$branch_id = $this->Auth->user('branch_id');     
					$conditions['conditions']['and']['Fds.branch_id'] = "$branch_id";
					$branch = '';
					}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";
				}  
				if (($branch !='') && ($branch !='all')){ $conditions['conditions']['and']['Fds.branch_id'] = "$branch"; }
				if ($company!='' && $company!='all'){ $conditions['conditions']['and']['Fds.productcompanyid'] = "$company"; }
				if ($typeofdeposit!='' && $typeofdeposit!='all'){ $conditions['conditions']['and']['Fds.typeofdeposit LIKE'] = "%$typeofdeposit%"; }   
				
				if (($company !='' && $company !='all') || ($typeofdeposit != '' && $typeofdeposit != 'all' ) || ($datedform && $datedto)){ 
					$fds = $this->Fds->find('all', $conditions);
					$cnt = $fds->count();
					if($fds) {
						$total_amount = 0;
						foreach ($fds as $fd) {
							$total_amount += $fd->amount;
						}
					}
					}elseif($company =='all' || $typeofdeposit == 'all'){
					$fds = $this->Fds;
					$fds1 = $this->Fds->find('all', array('contain' => ['Companies', 'Brokers', 'Plans']));
					$cnt = $fds1->count();
					if($fds1) {
						$total_amount = 0;
						foreach ($fds1 as $fd) {
							$total_amount += $fd->amount;
						}
					}
					}else{
					if($conditions != ''){
						$fds = $this->Fds->find('all', $conditions);
						$cnt = $fds->count();
						if($fds) {
							$total_amount = 0;
							foreach ($fds as $fd) {
								$total_amount += $fds->amount;
							}
						}
						}else{
						$fds = $this->Fds;
						$fds1 = $this->Fds->find('all', array('contain' => ['Companies', 'Brokers', 'Plans']));
						$cnt = $fds1->count();
						if($fds1) {
							$total_amount = 0;
							foreach ($fds1 as $fd) {
								$total_amount += $fd->amount;
							}
						}
					} 
				}    
				//, 'Banks'
				$this->paginate = [
                'contain' => ['Companies', 'Brokers', 'Plans']
				]; 
				$fds = $this->paginate($fds ,['order' => ['Fds.id' => 'DESC']]);
				$this->set(compact('fds','company','typeofdeposit','datedform','datedto','panno','cnt','total_amount','dated2','dated1'));
				$this->set('_serialize', ['fds']);
				
				}else{ 
				// $applicationno = '';
				// $fapplicantname = '';          
				// $banks = '';
				$typeofdeposit = '';          
				$datedform = '';
				$datedto = ''; 
				$company = '';
				// $panno = '';
				/*    if($conditions != ''){
					$fds = $this->Fds->find('all', $conditions);
					}else{
					$fds = $this->Fds;
					} 
					$this->paginate = [
					'contain' => ['Companies', 'Branches','Brokers', 'Plans', 'Banks']
					];
					* 
				*/
				$fds = '';
				// $fds = $this->paginate($fds,['order' => ['Fds.id' => 'DESC']]);
				$this->set(compact('fds','company','banks','typeofdeposit','datedform','datedto','cnt','total_amount'));
				$this->set('_serialize', ['fds']);  
				
			} 
		}
        
		function fdreports() {
			
			$this->autoRender = false;
			// $this->layout = false;        
			$fileName = "fdreports_".date("d-m-y:h:s").".xls";        
			$this->loadModel('Fds'); 
			$this->loadModel('Customers'); 
			$this->loadModel('Banks'); 
			$this->loadModel('Plans');
			$conditions = '';
			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";
			}  
			/* $applicationno = $this->request->query['applicationno'];
				$fapplicantname = $this->request->query['fapplicantname'];
				$panno = $this->request->query['panno'];
			$banks = $this->request->query['banks']; */
			$company = $this->request->query['company'];
			$typeofdeposit = $this->request->query['typeofdeposit']; 
			$datedform = $this->request->query['datedform'];
			$datedto = $this->request->query['datedto']; 
			if ($company){ $conditions['conditions']['and']['Fds.productcompanyid'] = "$company"; }
			if ($typeofdeposit){ $conditions['conditions']['and']['Fds.typeofdeposit LIKE'] = "%$typeofdeposit%"; }   
			if ($datedform && $datedto){             
				$conditions = array(
				'conditions' => array(
				'and' => array(
				array('Fds.maturitydate >= ' => $datedform,
				'Fds.maturitydate <= ' => $datedto
				)
				)));
			}
			
			if (($company !='' && $company !='all') || ($typeofdeposit != '' && $typeofdeposit != 'all' ) || ($datedform && $datedto)){ 
                $fds = $this->Fds->find('all', $conditions);
				}elseif($company =='all' || $typeofdeposit == 'all'){
				$fds = $this->Fds;
				}else{
				if($conditions != ''){
                    $fds = $this->Fds->find('all', $conditions);
					}else{
                    $fds = $this->Fds;
				} 
			}     
			
			$this->paginate = [
			'contain' => ['Companies', 'Brokers', 'Plans']
            ];
			$fds = $this->paginate($fds ,['order' => ['Fds.id' => 'DESC']]);
			$datafds = $fds->toArray(); 
			// pr($datafds);    
			
			$headerRow = array("Sr.","Client Name", "Subbroker",'Mainbroker','Product Company','Cheque No','FDR No','Amount','Mature On','Remark');
			$j=1;
            for($i=0; $i< count($datafds); $i++){
                $data[] = [$j,$datafds[$i]->fapplicantname,$datafds[$i]->broker->name,$datafds[$i]->broker->name,$datafds[$i]->company->company,$datafds[$i]->chequeno,$datafds[$i]->fdrno,$datafds[$i]->amount,$datafds[$i]->maturitydate,''];
				$j++;  
			}         
			
			$this->ExportXls->export($fileName, $headerRow, $data);
		}   
	
		
		public function fdcprofile(){
			$this->loadModel('Filenames');
			$this->loadModel('Mtables');
			$this->loadModel('Users');
			$all = array('all'=>'All');
			
			//$this->Search->addFilter('keyword',['attributes'=>['placeholder'=>'Keyword','label'=>'Keyword :','class'=>'searchfrm',],]);
			
			$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('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','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 = 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($datedform){
                     $conditions = array('conditions' => array('Mtables.maturity_date >= ' => $datedform));
					}
					if($datedto){
                     $conditions = array('conditions' => array('Mtables.maturity_date <= ' => $datedto));
					} 
				    
				}
			}
			
			if(empty($this->request->query['datedform'])){
				$dated2 = '';
				$datedform = '';
			}
			
			if(empty($this->request->query['datedto'])){
				$dated1 = '';
				$datedto = '';
			}
			
			
			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'] = $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['keyword'])){
				$fapplicantname = '';
			}
			
			if($this->Auth->user('group_id') > 2)
			{
				
				$conditions['conditions']['branch_id'] = $this->Auth->user('branch_id'); 
				
			}
			
			//$conditions['conditions']['verified'] = 1;
			//print_r($conditions);
			$order=$this->paginate['order'] = array('Mtables.id'=>'desc');
			$mdatas = $this->paginate('Mtables', $conditions);
			$this->set(compact('mdatas','fapplicantname','datedform','datedto','dated2','dated1','companyname','customer_name'));
		}
        
		function fdcprofilereports() {
			
			$this->autoRender = false;
			// $this->layout = false;        
			$fileName = "fdreports_".date("d-m-y:h:s").".xls";        
			$this->loadModel('Fds'); 
			$this->loadModel('Customers'); 
			$this->loadModel('Banks'); 
			$this->loadModel('Plans');
			
			if($this->Auth->user('group_id')=='3'){
				$conditions = '';  
				$branch_id = $this->Auth->user('branch_id');     
				$conditions['conditions']['and']['Fds.posteduserbranchid'] = "$branch_id";
			}   
			//  $applicationno = $this->request->query['applicationno'];
			$fapplicantname = $this->request->query['fapplicantname'];
			/*  $panno = $this->request->query['panno'];
			$banks = $this->request->query['banks']; */
			$typeofdeposit = $this->request->query['typeofdeposit']; 
			$datedform = $this->request->query['datedform'];
			$datedto = $this->request->query['datedto']; 
			//  if ($applicationno){ $conditions['conditions']['and']['Fds.applicationno LIKE'] = "%$applicationno%"; }
			if ($fapplicantname){ $conditions['conditions']['and']['Fds.fapplicantname LIKE'] = "%$fapplicantname%"; }
			/*  if ($panno){ $conditions['conditions']['and']['Fds.panno'] = "$panno"; }
			if ($banks){ $conditions['conditions']['and']['Fds.bank_id'] = "$banks"; } */
			if ($typeofdeposit){ $conditions['conditions']['and']['Fds.typeofdeposit LIKE'] = "%$typeofdeposit%"; }   
			/* if ($datedform && $datedto){
				$conditions = array(
				'conditions' => array(
				'and' => array(
				array('trim(Fds.maturitydate) >= ' => $datedform,
				'trim(Fds.maturitydate) <= ' => $datedto
				)
				)));
			}*/
			if ($datedform && $datedto){             
				$conditions = array(
				'conditions' => array(
				'and' => array(
				array('Fds.maturitydate >= ' => $datedform,
				'Fds.maturitydate <= ' => $datedto
				)
				)));
			}
			
            if (($fapplicantname != '' && $fapplicantname != 'all') || ($typeofdeposit != '' && $typeofdeposit != 'all') || ($datedform && $datedto)){ 
                $fds = $this->Fds->find('all', $conditions);
				}elseif($fapplicantname == 'all' || $typeofdeposit == 'all'){
				$fds = $this->Fds;
				}else{
				if($conditions != ''){
                    $fds = $this->Fds->find('all', $conditions);
					}else{
                    $fds = $this->Fds;
				} 
			}   
			
			$this->paginate = [
			'contain' => ['Companies','Brokers', 'Plans', 'Banks']
            ];
			$fds = $this->paginate($fds ,['order' => ['Fds.id' => 'DESC']]);
			$datafds = $fds->toArray(); 
			//pr($datafds);    
			
			$headerRow = array("Sr.","Investor", "Fund+Scheme",'Type','Folio No','Option','Start Date','End Date','Amount');
			$j=1;
            for($i=0; $i< count($datafds); $i++){
                $data[] = [$j,$datafds[$i]->fapplicantname,$datafds[$i]->plan->Planname,$datafds[$i]->typeofdeposit,$datafds[$i]->fdrno,$datafds[$i]->fdrno,$datafds[$i]->planstartdate,$datafds[$i]->maturitydate,$datafds[$i]->amount];
				$j++;
			}         
			
			$this->ExportXls->export($fileName, $headerRow, $data);
		}
		
		/* fd profile   */
		
		//SIP Maturity Report
		public function mf(){
			$this->loadModel('Mutualfunds'); 
			$this->loadModel('Customers'); 
			$this->loadModel('Banks'); 
			$all = array('all'=>'All'); 
			
			$typeofdeposit = array(
            'SIP' => 'SIP',            
            'SIP - Renewal' => 'SIP - Renewal',
			);
			
			$this->Search->addFilter('company', [
			'attributes'=>['placeholder'=>'Company','label'=>'MutualFund :','class'=>'searchfrm', 'value' => isset($this->request->query['company'])?$this->request->query['company']:''],  
			'options' => $all+$this->Mutualfunds->Companies->find('list')-> where(['associatewith' => 'product'])->andWhere(['Companies.productid ='=>'1'] )->order(['company' => 'ASC'])->toArray(),
			]);
			
			$this->Search->addFilter('scheme', [
			'attributes'=>['placeholder'=>'Scheme Name','label'=>'Scheme Name :','class'=>'searchfrm','value' => isset($this->request->query['scheme'])?$this->request->query['scheme']:'',],  
			'options' => $all+$this->Mutualfunds->Mffunds->find('list')->order(['fundname' => 'ASC'])->group(['fundname'])->toArray(),
			]);
			if($this->Auth->user('group_id')=='2'){ 
				$branch = $this->Mutualfunds->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->Mutualfunds->Branches->find('list')->toArray(),
				]);
			}  
			
			$this->Search->addFilter('typeofdeposit', ['attributes' => ['placeholder' => 'Type of Deposit','label' => 'Type of Deposit :','class' => 'searchform', 'value' => isset($this->request->query['typeofdeposit'])?$this->request->query['typeofdeposit']:''],'options' => $typeofdeposit,]);
			
			$this->Search->addFilter('datedform', [
			'attributes'=>['placeholder'=>'Maturity From Date','label'=>'Maturity From Date :','class'=>'searchfrm','value' => isset($this->request->query['datedform'])?$this->request->query['datedform']:''],
			]);
			$this->Search->addFilter('datedto', [
			'attributes'=>['placeholder'=>'Maturity To Date','label'=>'Maturity To Date :','class'=>'searchfrm','value' => isset($this->request->query['datedto'])?$this->request->query['datedto']:''],
			]);
			
			$conditions = '';  
			
			if (!empty($this->request->query) && count($this->request->query) > 1) { 
				
				$branch = $this->request->query['branch'];
				$company = $this->request->query['company']; 
				$scheme = $this->request->query['scheme'];
				
				$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){             
					$conditions = array(
					'conditions' => array(
					'and' => array(
					array('Mutualfunds.maturitydate >= ' => $datedform,
					'Mutualfunds.maturitydate <= ' => $datedto
					)
					)));
				}
				
				if(!empty($this->request->query['typeofdeposit'])){
					$typeofdeposit_sel = (($this->request->query['typeofdeposit'] != '')? $this->request->query['typeofdeposit'] : ''); 
					}else{
					$typeofdeposit_sel = '';   
				}
				if (!empty($typeofdeposit_sel)){ 
					$conditions['conditions']['and']['Mutualfunds.typeofdeposit IN '] = $typeofdeposit_sel; 			 
				} 
				
				//$conditions['conditions']['and']['Mutualfunds.typeofdeposit'] = 'SIP';
				if($this->Auth->user('group_id')=='3'){        
					$branch_id = $this->Auth->user('branch_id');     
					$conditions['conditions']['and']['Mutualfunds.branch_id'] = "$branch_id";
					$branch = '';
				}
				if ($branch!='' && $branch!='all'){ $conditions['conditions']['and']['Mutualfunds.branch_id'] = "$branch"; }
				if ($company!='' && $company!='all'){ $conditions['conditions']['and']['Mutualfunds.productcompanyid'] = "$company"; }
				if ($scheme!='' && $scheme!='all') { $conditions['conditions']['and']['Mutualfunds.mffund_id'] = "$scheme"; }
				
				if ( ($company != '' && $company != 'all') || ($branch != '' &&  $branch != 'all') || ($scheme != '' && $scheme != 'all' ) || ($datedform && $datedto) ){ 
					$mf = $this->Mutualfunds->find('all', $conditions);
					$cnt = $mf->count();              
					if($mf){
						$total_amount = 0;                   
						foreach ($mf as $md){ 
							$total_amount += $md->amount;
						}
					} 
				}
				elseif($company == 'all' || $branch == 'all' || $scheme == 'all' )
				{
					
					$mf = $this->Mutualfunds;
					$mf1=$this->Mutualfunds->find('all',array('contain' => ['Companies', 'Branches', 'Mffunds','Brokers']));
					$cnt = $mf1->count();              
					if($mf1){
						$total_amount = 0;                   
						foreach ($mf1 as $md){ 
							$total_amount += $md->amount;
						}
					} 
				}
				else{  
					if($conditions != ''){
						
						$mf = $this->Mutualfunds->find('all', $conditions);
						$cnt = $mf->count();              
                        if($mutualfunds){
                            $total_amount = 0;                   
							foreach ($mf as $md){ 
                                $total_amount += $md->amount;
							}
						} 
					} 
					
					else{
						
						$mf = $this->Mutualfunds;
						$mf1=$this->Mutualfunds->find('all',array('contain' => ['Companies', 'Branches', 'Mffunds','Brokers']));
						$cnt = $mf1->count();              
                        if($mf1){
							$total_amount = 0;                   
							foreach ($mf1 as $md){ 
								$total_amount += $md->amount;
							}
						} 
					}  
				}    
				//'Banks',
				$this->paginate = [
				'contain' => ['Companies', 'Branches','Brokers','Mffunds']
				]; 
				// $mfs = $mf;
				
				$mf = $this->paginate($mf ,['order' => ['Mutualfunds.id' => 'DESC']]);
				$this->set(compact('mf','company','branch','scheme','datedform','datedto','total_amount','cnt','dated2','dated1'));
				$this->set('_serialize', ['mf']);
				
				}else{ 
				
				$datedform = '';
				$datedto = ''; 
				$company = '';
				$branch = '';
				$scheme = '';
				$mf = '';
				$this->set(compact('mf','page','company','subbroker','scheme','branch','datedform','datedto','total_amount','cnt'));
				$this->set('_serialize', ['mf']);
				
			} 
		}
		
		//SIP Renewal Maturity Report
		public function mfsiprenewal(){
			$this->loadModel('Mutualfunds'); 
			$this->loadModel('Customers'); 
			$this->loadModel('Banks'); 
			$all = array('all'=>'All'); 
			
			$this->Search->addFilter('company', [
			'attributes'=>['placeholder'=>'Company','label'=>'MutualFund :','class'=>'searchfrm', 'value' => isset($this->request->query['company'])?$this->request->query['company']:''],  
			'options' => $all+$this->Mutualfunds->Companies->find('list')-> where(['associatewith' => 'product'])->andWhere(['Companies.productid ='=>'1'] )->order(['company' => 'ASC'])->toArray(),
			]);
			
			$this->Search->addFilter('scheme', [
			'attributes'=>['placeholder'=>'Scheme Name','label'=>'Scheme Name :','class'=>'searchfrm','value' => isset($this->request->query['scheme'])?$this->request->query['scheme']:'',],  
			'options' => $all+$this->Mutualfunds->Mffunds->find('list')->order(['fundname' => 'ASC'])->group(['fundname'])->toArray(),
			]);
			if($this->Auth->user('group_id')=='2'){ 
				$branch = $this->Mutualfunds->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->Mutualfunds->Branches->find('list')->toArray(),
				]);
			}  
			
			$this->Search->addFilter('datedform', [
			'attributes'=>['placeholder'=>'Maturity From Date','label'=>'Maturity From Date :','class'=>'searchfrm','value' => isset($this->request->query['datedform'])?$this->request->query['datedform']:''],
			]);
			$this->Search->addFilter('datedto', [
			'attributes'=>['placeholder'=>'Maturity To Date','label'=>'Maturity To Date :','class'=>'searchfrm','value' => isset($this->request->query['datedto'])?$this->request->query['datedto']:''],
			]);
			
			$conditions = '';  
			if($this->Auth->user('group_id')=='3'){        
				$branch_id = $this->Auth->user('branch_id');     
				$conditions['conditions']['and']['Mutualfunds.posteduserbranchid'] = "$branch_id";
			}
			if (!empty($this->request->query) && count($this->request->query) > 1) { 
				
				$branch = $this->request->query['branch'];
				$company = $this->request->query['company']; 
				$scheme = $this->request->query['scheme'];
				
				$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){             
					$conditions = array(
					'conditions' => array(
					'and' => array(
					array('Mutualfunds.maturitydate >= ' => $datedform,
					'Mutualfunds.maturitydate <= ' => $datedto
					)
					)));
				}
				$conditions['conditions']['and']['Mutualfunds.typeofdeposit'] = 'SIP - Renewal';
				if ($branch!='' && $branch!='all'){ $conditions['conditions']['and']['Mutualfunds.branch_id'] = "$branch"; }
				if ($company!='' && $company!='all'){ $conditions['conditions']['and']['Mutualfunds.productcompanyid'] = "$company"; }
				if ($scheme!='' && $scheme!='all') { $conditions['conditions']['and']['Mutualfunds.mffund_id'] = "$scheme"; }
				
				if ( ($company != '' && $company != 'all') || ($branch != '' &&  $branch != 'all') || ($scheme != '' && $scheme != 'all' ) || ($datedform && $datedto) ){ 
					$mf = $this->Mutualfunds->find('all', $conditions);
					$cnt = $mf->count();              
					if($mf){
						$total_amount = 0;                   
						foreach ($mf as $md){ 
							$total_amount += $md->amount;
						}
					} 
				}
				elseif($company == 'all' || $branch == 'all' || $scheme == 'all' )
				{
					
					$mf = $this->Mutualfunds;
					$mf1=$this->Mutualfunds->find('all',array('contain' => ['Companies', 'Branches', 'Mffunds','Brokers']));
					$cnt = $mf1->count();              
					if($mf1){
						$total_amount = 0;                   
						foreach ($mf1 as $md){ 
							$total_amount += $md->amount;
						}
					} 
				}
				else{  
					if($conditions != ''){
						
						$mf = $this->Mutualfunds->find('all', $conditions);
						$cnt = $mf->count();              
                        if($mutualfunds){
                            $total_amount = 0;                   
							foreach ($mf as $md){ 
                                $total_amount += $md->amount;
							}
						} 
					} 
					
					else{
						
						$mf = $this->Mutualfunds;
						$mf1=$this->Mutualfunds->find('all',array('contain' => ['Companies', 'Branches', 'Mffunds','Brokers']));
						$cnt = $mf1->count();              
                        if($mf1){
							$total_amount = 0;                   
							foreach ($mf1 as $md){ 
								$total_amount += $md->amount;
							}
						} 
					}  
				}    
				//'Banks',
				$this->paginate = [
				'contain' => ['Companies', 'Branches','Brokers','Mffunds']
				]; 
				// $mfs = $mf;
				
				$mf = $this->paginate($mf ,['order' => ['Mutualfunds.id' => 'DESC']]);
				$this->set(compact('mf','company','branch','scheme','datedform','datedto','total_amount','cnt','dated2','dated1'));
				$this->set('_serialize', ['mf']);
				
				}else{ 
				
				$datedform = '';
				$datedto = ''; 
				$company = '';
				$branch = '';
				$scheme = '';
				$mf = '';
				$this->set(compact('mf','page','company','subbroker','scheme','branch','datedform','datedto','total_amount','cnt'));
				$this->set('_serialize', ['mf']);
				
			} 
		}
		
		//STP Maturity Report
		public function mfstp(){
			$this->loadModel('Mutualfunds'); 
			$this->loadModel('Customers'); 
			$this->loadModel('Banks'); 
			$all = array('all'=>'All'); 
			
			$this->Search->addFilter('company', [
			'attributes'=>['placeholder'=>'Company','label'=>'MutualFund :','class'=>'searchfrm', 'value' => isset($this->request->query['company'])?$this->request->query['company']:''],  
			'options' => $all+$this->Mutualfunds->Companies->find('list')-> where(['associatewith' => 'product'])->andWhere(['Companies.productid ='=>'1'] )->order(['company' => 'ASC'])->toArray(),
			]);
			
			$this->Search->addFilter('scheme', [
			'attributes'=>['placeholder'=>'Scheme Name','label'=>'Scheme Name :','class'=>'searchfrm','value' => isset($this->request->query['scheme'])?$this->request->query['scheme']:'',],  
			'options' => $all+$this->Mutualfunds->Mffunds->find('list')->order(['fundname' => 'ASC'])->group(['fundname'])->toArray(),
			]);
			if($this->Auth->user('group_id')=='2'){ 
				$branch = $this->Mutualfunds->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->Mutualfunds->Branches->find('list')->toArray(),
				]);
			}  
			
			$this->Search->addFilter('datedform', [
			'attributes'=>['placeholder'=>'Maturity From Date','label'=>'Maturity From Date :','class'=>'searchfrm','value' => isset($this->request->query['datedform'])?$this->request->query['datedform']:''],
			]);
			$this->Search->addFilter('datedto', [
			'attributes'=>['placeholder'=>'Maturity To Date','label'=>'Maturity To Date :','class'=>'searchfrm','value' => isset($this->request->query['datedto'])?$this->request->query['datedto']:''],
			]);
			
			$conditions = '';  
			
			if (!empty($this->request->query) && count($this->request->query) > 1) { 
				
				$branch = $this->request->query['branch'];
				$company = $this->request->query['company']; 
				$scheme = $this->request->query['scheme'];
				
				$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){             
					$conditions = array(
					'conditions' => array(
					'and' => array(
					array('Mutualfunds.maturitydate >= ' => $datedform,
					'Mutualfunds.maturitydate <= ' => $datedto
					)
					)));
				}
				$conditions['conditions']['and']['Mutualfunds.typeofdeposit'] = 'STP';
				if($this->Auth->user('group_id')=='3'){        
					$branch_id = $this->Auth->user('branch_id');     
					$conditions['conditions']['and']['Mutualfunds.branch_id'] = "$branch_id";
					$branch = '';
				}
				if ($branch!='' && $branch!='all'){ $conditions['conditions']['and']['Mutualfunds.branch_id'] = "$branch"; }
				if ($company!='' && $company!='all'){ $conditions['conditions']['and']['Mutualfunds.productcompanyid'] = "$company"; }
				if ($scheme!='' && $scheme!='all') { $conditions['conditions']['and']['Mutualfunds.mffund_id'] = "$scheme"; }
				
				if ( ($company != '' && $company != 'all') || ($branch != '' &&  $branch != 'all') || ($scheme != '' && $scheme != 'all' ) || ($datedform && $datedto) ){ 
					$mf = $this->Mutualfunds->find('all', $conditions);
					$cnt = $mf->count();              
					if($mf){
						$total_amount = 0;                   
						foreach ($mf as $md){ 
							$total_amount += $md->amount;
						}
					} 
				}
				elseif($company == 'all' || $branch == 'all' || $scheme == 'all' )
				{
					
					$mf = $this->Mutualfunds;
					$mf1=$this->Mutualfunds->find('all',array('contain' => ['Companies', 'Branches', 'Mffunds','Brokers']));
					$cnt = $mf1->count();              
					if($mf1){
						$total_amount = 0;                   
						foreach ($mf1 as $md){ 
							$total_amount += $md->amount;
						}
					} 
				}
				else{  
					if($conditions != ''){
						
						$mf = $this->Mutualfunds->find('all', $conditions);
						$cnt = $mf->count();              
                        if($mutualfunds){
                            $total_amount = 0;                   
							foreach ($mf as $md){ 
                                $total_amount += $md->amount;
							}
						} 
					} 
					
					else{
						
						$mf = $this->Mutualfunds;
						$mf1=$this->Mutualfunds->find('all',array('contain' => ['Companies', 'Branches', 'Mffunds','Brokers']));
						$cnt = $mf1->count();              
                        if($mf1){
							$total_amount = 0;                   
							foreach ($mf1 as $md){ 
								$total_amount += $md->amount;
							}
						} 
					}  
				}    
				//'Banks',
				$this->paginate = [
				'contain' => ['Companies', 'Branches','Brokers','Mffunds']
				]; 
				// $mfs = $mf;
				
				$mf = $this->paginate($mf ,['order' => ['Mutualfunds.id' => 'DESC']]);
				$this->set(compact('mf','company','branch','scheme','datedform','datedto','total_amount','cnt','dated2','dated1'));
				$this->set('_serialize', ['mf']);
				
				}else{ 
				
				$datedform = '';
				$datedto = ''; 
				$company = '';
				$branch = '';
				$scheme = '';
				$mf = '';
				$this->set(compact('mf','page','company','subbroker','scheme','branch','datedform','datedto','total_amount','cnt'));
				$this->set('_serialize', ['mf']);
				
			} 
		}	
		
		//Client Mutual Fund Portfolio   
		public function mfcprofile(){
			$this->loadModel('Mutualfunds'); 
			$this->loadModel('Customers'); 
			$this->loadModel('Banks'); 
			
			$all = array('all'=>'All');
			$this->Search->addFilter('fapplicantname', [
			'attributes'=>['placeholder'=>'Applicant Name','label'=>'Applicant Name :','class'=>'searchfrm','value' => isset($this->request->query['fapplicantname'])?$this->request->query['fapplicantname']:''], 'options' =>$all+$this->Mutualfunds->Customers->find('list',['keyField' => 'firstname','valueField' => 'firstname'])->order(['Customers.firstname'=>'ASC'])->group(['Customers.firstname'])->toArray(),
			]);
			$this->Search->addFilter('company', [
			'attributes'=>['placeholder'=>'Company Name','label'=>'Company Name :','class'=>'searchfrm','value' => isset($this->request->query['company'])?$this->request->query['company']:''],  
			'options' => $all+$this->Mutualfunds->Companies->find('list')-> where(['associatewith' => 'product'])->andWhere(['Companies.productid ='=>'1'] )->order(['company' => 'ASC'])->toArray(),
			]);
			$this->Search->addFilter('scheme', [
			'attributes'=>['placeholder'=>'Scheme Name','label'=>'Scheme Name :','class'=>'searchfrm','value' => isset($this->request->query['scheme'])?$this->request->query['scheme']:''],  
			'options' => $all+$this->Mutualfunds->Mffunds->find('list')->toArray(),
			]);
			$this->Search->addFilter('datedform', [
			'attributes'=>['placeholder'=>'Despatch Start Date','label'=>'Despatch Start Date :','class'=>'searchfrm','value' => isset($this->request->query['datedform'])?$this->request->query['datedform']:''],
			]);
			$this->Search->addFilter('datedto', [
			'attributes'=>['placeholder'=>'Despatch End Date','label'=>'Despatch End Date :','class'=>'searchfrm','value' => isset($this->request->query['datedto'])?$this->request->query['datedto']:''],
			]); 
			
			$conditions = '';
			if($this->Auth->user('group_id')=='3'){           
				$branch_id = $this->Auth->user('branch_id');     
				$conditions['conditions']['and']['Mutualfunds.posteduserbranchid'] = "$branch_id";
			} 
			if (!empty($this->request->query) && count($this->request->query) > 1) { 
				
				$fapplicantname = $this->request->query['fapplicantname']; 
				
				$company = $this->request->query['company']; 
				$scheme = $this->request->query['scheme']; 
				$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){             
					$conditions = array(
					'conditions' => array(
					'and' => array(
					array('Mutualfunds.despatchdate >= ' => $datedform,
					'Mutualfunds.despatchdate <= ' => $datedto
					)
					)));
				}
				if ($fapplicantname!='' && $fapplicantname!='all'){ $conditions['conditions']['and']['Mutualfunds.firstapplicantname LIKE'] = "$fapplicantname"; }
				if($this->Auth->user('group_id')=='3'){           
					$branch_id = $this->Auth->user('branch_id');     
					$conditions['conditions']['and']['Mutualfunds.posteduserbranchid'] = "$branch_id";
				}
				if ($company!='' && $company!='all'){ $conditions['conditions']['and']['Mutualfunds.productcompanyid'] = "$company"; }
				// if ($subbroker){ $conditions['conditions']['and']['Mutualfunds.broker_id'] = "$subbroker"; }
				if ($scheme!='' && $scheme!='all'){ $conditions['conditions']['and']['Mutualfunds.mffund_id'] = "$scheme"; }
				
				if ( ($scheme != '' && $scheme != 'all') || ($fapplicantname != '' && $fapplicantname != 'all') || ($company != '' && $company != 'all' )|| ($datedform && $datedto)){ 
					$mf = $this->Mutualfunds->find('all', $conditions);
					$cnt = $mf->count();              
					if($mf){
						$total_amount = 0;                   
						foreach ($mf as $md){ 
							$total_amount += $md->amount;
						}
					} 
					
				}elseif($scheme == 'all' || $fapplicantname == 'all' || $company == 'all' )
				{
					$mf = $this->Mutualfunds;
				    $mf1=$this->Mutualfunds->find('all',array('contain' => ['Companies', 'Branches', 'Mffunds','Brokers']));
					$cnt = $mf1->count();              
					if($mf1){
						$total_amount = 0;                   
						foreach ($mf1 as $md){ 
							$total_amount += $md->amount;
						}
					} 
					}else{
					if($conditions != '') {
						$mf = $this->Mutualfunds->find('all', $conditions);
						$cnt = $mf->count();              
                        if($mf){
                            $total_amount = 0;                   
							foreach ($mf as $md){ 
                                $total_amount += $md->amount;
							}
						} 
					}
					else
					{
						$mf = $this->Mutualfunds;
						$mf1=$this->Mutualfunds->find('all',array('contain' => ['Companies', 'Branches', 'Mffunds','Brokers']));
						$cnt = $mf1->count();              
                        if($mf1){
							$total_amount = 0;                   
							foreach ($mf1 as $md){ 
								$total_amount += $md->amount;
							}
						} 
					}
				}   
				//'Banks',
				$this->paginate = [
				'contain' => ['Companies', 'Branches','Brokers','Mffunds']
				]; 
				$mf = $this->paginate($mf ,['order' => ['Mutualfunds.id' => 'DESC']]);
				$this->set(compact('mf','panno','scheme','branch','applicationno','fapplicantname','banks','company','subbroker','typeofdeposit','datedform','datedto','dated2','dated1','cnt','total_amount'));
				$this->set('_serialize', ['mf']);
				
				}else{ 
				//  $applicationno = '';
				$fapplicantname = '';          
				/*  $banks = '';
				$typeofdeposit = '';   */        
				$datedform = '';
				$datedto = ''; 
				// $panno = '';
				$company = '';
				// $subbroker = '';
				$scheme = '';
				// $branch = '';
				/*
					if($conditions != ''){
					$mf = $this->Mutualfunds->find('all', $conditions);
					$cnt = $mf->count();              
					if($mf){
					$total_amount = 0;                   
					foreach ($mf as $md){ 
					$total_amount += $md->amount;
					}
					} 
					}else{
					$mf = $this->Mutualfunds;
					$mf1=$this->Mutualfunds->find('all',array('contain' => ['Companies', 'Branches', 'Mffunds','Brokers']));
					$cnt = $mf1->count();              
					if($mf1){
					$total_amount = 0;                   
					foreach ($mf1 as $md){ 
					$total_amount += $md->amount;
					}
					} 
					}   
					$this->paginate = [
					'contain' => ['Companies', 'Branches','Brokers','Mffunds']
					];
					$mf = $this->paginate($mf,['order' => ['Mutualfunds.mffund_id' => 'DESC']]);
				*/
				$mf = '';
				
				$this->set(compact('mf','scheme','branch','fapplicantname','company','datedform','datedto','cnt','total_amount'));
				$this->set('_serialize', ['mf']);
			} 
		}
        
		function mfcprofilereports() {
			$conditions = '';  
			$this->autoRender = false;
			// $this->layout = false;        
			$fileName = "mfreports_".date("d-m-y:h:s").".xls";        
			$this->loadModel('Mutualfunds'); 
			$this->loadModel('Customers'); 
			$this->loadModel('Banks'); 
			if($this->Auth->user('group_id')=='3'){            
				$branch_id = $this->Auth->user('branch_id');     
				$conditions['conditions']['and']['Mutualfunds.posteduserbranchid'] = "$branch_id";
			}  
			$applicationno = $this->request->query['applicationno'];
			$fapplicantname = $this->request->query['fapplicantname'];
			$panno = $this->request->query['panno'];
			$banks = $this->request->query['banks'];
			$company = $this->request->query['company']; 
			$subbroker = $this->request->query['subbroker']; 
			$typeofdeposit = $this->request->query['typeofdeposit']; 
			$datedform = $this->request->query['datedform'];
			$datedto = $this->request->query['datedto']; 
			$scheme = $this->request->query['scheme']; 
			$branch = $this->request->query['branch']; 
			
			if ($applicationno){ $conditions['conditions']['and']['Mutualfunds.applicationno LIKE'] = "%$applicationno%"; }
			if ($fapplicantname){ $conditions['conditions']['and']['Mutualfunds.firstapplicantname LIKE'] = "%$fapplicantname%"; }
			if ($panno){ $conditions['conditions']['and']['Mutualfunds.panno'] = "$panno"; }
			if ($banks){ $conditions['conditions']['and']['Mutualfunds.bank_id'] = "$banks"; }
			if ($company){ $conditions['conditions']['and']['Mutualfunds.productcompanyid'] = "$company"; }
			if ($subbroker){ $conditions['conditions']['and']['Mutualfunds.broker_id'] = "$subbroker"; }
			if ($scheme){ $conditions['conditions']['and']['Mutualfunds.mffund_id'] = "$scheme"; }
			if ($branch){ $conditions['conditions']['and']['Mutualfunds.branch_id'] = "$branch"; }
			if ($typeofdeposit){ $conditions['conditions']['and']['Mutualfunds.typeofdeposit LIKE'] = "%$typeofdeposit%"; }   
			/*if ($datedform && $datedto){
				$conditions = array(
				'conditions' => array(
				'and' => array(
				array('trim(Mutualfunds.maturitydate) >= ' => $datedform,
				'trim(Mutualfunds.maturitydate) <= ' => $datedto
				)
				)));
			}*/
			if ($datedform && $datedto){             
				$conditions = array(
				'conditions' => array(
				'and' => array(
				array('Mutualfunds.maturitydate >= ' => $datedform,
				'Mutualfunds.maturitydate <= ' => $datedto
				)
				)));
			}
			
			if ($applicationno !='' || $scheme != '' || $branch != '' || $fapplicantname != '' || $panno != '' || $company != '' || $subbroker != '' || $banks !='' || $typeofdeposit != '' || ($datedform && $datedto)){ 
				$mf = $this->Mutualfunds->find('all', $conditions);
				}else{
				if($conditions != ''){ 
					$mf = $this->Mutualfunds->find('all', $conditions);
					}else{
					$mf = $this->Mutualfunds;
				}   
			}    
			
			$this->paginate = [
			'contain' => ['Companies', 'Branches','Brokers','Mffunds']
			]; 
			$mf = $this->paginate($mf ,['order' => ['Mutualfunds.id' => 'DESC']]);
			$datafds = $mf->toArray(); 
			//pr($datafds);die;
			$headerRow = array("Sr.",'Company','Scheme',"Location","Investor",'Type','Folio No','Cheque No','Amount','Period(In month)','App No');
			$j=1;
			for($i=0; $i< count($datafds); $i++){             
				$data[] = [$j,$datafds[$i]->company->company,$datafds[$i]->mffund->fundname,$datafds[$i]->branch->branchaddress,$datafds[$i]->firstapplicantname,$datafds[$i]->typeofdeposit,$datafds[$i]->customerFolioNo,$datafds[$i]->chequeno,$datafds[$i]->amount,$datafds[$i]->tenure,$datafds[$i]->applicationno];
				$j++;
			}          
			$this->ExportXls->export($fileName, $headerRow, $data);
		}
        
		/* end Mf report */  
        
		public function dailyreport()
		{
			$this->loadModel('Fds'); 
			$this->loadModel('Mutualfunds');
			$this->loadModel('Ncds'); 
			$this->loadModel('Ipos'); 
			$this->loadModel('Customers'); 
			$this->loadModel('Banks'); 
			$this->loadModel('Plans'); 
			$this->loadModel('Products');
			
			$all = array('all'=>'All');    
			$this->Search->addFilter('Product', [
            'attributes'=>['placeholder'=>'Product Name','label'=>'Product Name :','class'=>'searchfrm',],  
            'options' => $all+$this->Products->find('list')->toArray(),           
			]); 
			
			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',],  
				'options' => $all+$this->Fds->Branches->find('list')->toArray(),
				]);
			}  
			
			$this->Search->addFilter('datedform', [
			'attributes'=>['placeholder'=>'Start Date','label'=>'Start Date :','class'=>'searchfrm',],
			]);
			$this->Search->addFilter('datedto', [
			'attributes'=>['placeholder'=>'End Date','label'=>'End Date :','class'=>'searchfrm',],
			]);
			
			$conditions = '';
			if($this->Auth->user('group_id')=='3'){
				//$conditions = '';  
				$branch_id = $this->Auth->user('branch_id');     
				$conditions['conditions']['and']['Fds.posteduserbranchid'] = "$branch_id";
			} 
			
			if (!empty($this->request->query) && count($this->request->query) > 1) { 
				// $conditions = '';
				if($this->Auth->user('group_id')=='2'){
					$branch = (($this->request->query['branch'] != '')? $this->request->query['branch'] : '');       
				}          
				$datedform = (($this->request->query['datedform'] != '')? $this->request->query['datedform'] : ''); 
				$datedto = (($this->request->query['datedto'] != '')? $this->request->query['datedto'] : ''); 
				
				if ($datedform && $datedto){
					$conditions = array(
					'conditions' => array(
					'and' => array(
					array('trim(Fds.despatchdate) >= ' => $datedform,
					'trim(Fds.despatchdate) <= ' => $datedto
					)
					)));
				}
				if (($branch !='') && ($branch !='all')){ $conditions['conditions']['and']['Fds.branch_id'] = "$branch"; }
				
				if ($branch != ''  || ($datedform && $datedto)){                 
					$fds = $this->Fds->find('all', $conditions);
					}else{
					$fds = $this->Fds->find('all', $conditions);
				} 
				//, 'Banks'
				$this->paginate = [
                'contain' => ['Companies', 'Branches','Brokers', 'Plans']
				]; 
				$fds = $this->paginate($fds ,['order' => ['Fds.branch_id' => 'ASC']]);
				$this->set(compact('fds','branch','datedform','datedto'));
				$this->set('_serialize', ['fds']);
				}else{
				$branch =''; $datedform =''; $datedto= '';
				if($conditions !=''){
					$fds = $this->Fds->find('all', $conditions);
					}else{
					$fds = $this->Fds;
				}
				
				//, 'Banks'
				$this->paginate = [
                'contain' => ['Companies', 'Branches','Brokers', 'Plans']
				]; 
				$fds = $this->paginate($fds ,['order' => ['Fds.branch_id' => 'ASC']]);
				$this->set(compact('fds','branch','datedform','datedto'));
				$this->set('_serialize', ['fds']);
			}   
			
			//mf
			$conditions = ''; 
			if($this->Auth->user('group_id')=='3'){            
				$branch_id = $this->Auth->user('branch_id');     
				$conditions['conditions']['and']['Mutualfunds.posteduserbranchid'] = "$branch_id";
			} 
			
			if (!empty($this->request->query) && count($this->request->query) > 1) { 
				// $conditions = '';
				if($this->Auth->user('group_id')=='2'){
					$branch = (($this->request->query['branch'] != '')? $this->request->query['branch'] : '');               
				}  
				
				
				if ($datedform && $datedto){             
					$conditions = array(
					'conditions' => array(
					'and' => array(
					array('trim(Mutualfunds.despatchdate) >= ' => $datedform,
					'trim(Mutualfunds.despatchdate) <= ' => $datedto
					)
					)));
				}
				if (($branch !='') && ($branch !='all')){ $conditions['conditions']['and']['Mutualfunds.branch_id'] = "$branch"; }
				
				if ($branch != ''  || ($datedform && $datedto)){ 
					$mutualfunds = $this->Mutualfunds->find('all', $conditions);
					}else{
					if($conditions !=''){
						$mutualfunds = $this->Mutualfunds->find('all', $conditions);
						}else{
						$mutualfunds = $this->Mutualfunds; 
					} 
				}    
				//, 'Banks',
				
				$this->paginate = [
				'contain' => ['Companies', 'Branches', 'Mffunds','Brokers']
				]; 
				
				$mutualfunds = $this->paginate($mutualfunds ,['order' => ['Mutualfunds.branch_id' => 'ASC']]);     
				$this->set(compact('mutualfunds','branch','datedform','datedto'));
				$this->set('_serialize', ['mutualfunds']);
				}else{
				$branch =''; $datedform =''; $datedto= '';
				if($conditions !=''){
					$mutualfunds = $this->Mutualfunds->find('all', $conditions);
					}else{
					$mutualfunds = $this->Mutualfunds; 
				}  
				$this->paginate = [
				'contain' => ['Companies', 'Branches','Brokers', 'Mffunds']
				]; 
				$mutualfunds = $this->paginate($mutualfunds ,['order' => ['Mutualfunds.branch_id' => 'ASC']]);
				$this->set(compact('mutualfunds','branch','datedform','datedto'));
				$this->set('_serialize', ['mutualfunds']);
			}   
			
			// ncd
			$conditions = ''; 
			if($this->Auth->user('group_id')=='3'){             
				$branch_id = $this->Auth->user('branch_id');     
				$conditions['conditions']['and']['Ncds.posteduserbranchid'] = "$branch_id";
			} 
			if (!empty($this->request->query) && count($this->request->query) > 1) { 
				//$conditions = '';           
				
				if ($datedform && $datedto){
					$conditions = array(
					'conditions' => array(
					'and' => array(
					array('trim(Ncds.despatchdate) >= ' => $datedform,
					'trim(Ncds.despatchdate) <= ' => $datedto
					)
					)));
				}
				if (($branch !='') && ($branch !='all')){  $conditions['conditions']['and']['Ncds.branch_id'] = "$branch"; }
				
				if ($branch != ''  || ($datedform && $datedto)){ 
					$ncds = $this->Ncds->find('all', $conditions);
					}else{
					$ncds = $this->Ncds->find('all', $conditions);
				}    
				$this->paginate = [
				'contain' => ['Companies','Branches', 'Banks','Brokers','Plans']
				]; 
				$ncds = $this->paginate($ncds ,['order' => ['Ncds.branch_id' => 'ASC']]);
				$this->set(compact('ncds','branch','datedform','datedto'));
				$this->set('_serialize', ['ncds']);
				}else{
				$branch =''; $datedform =''; $datedto= ''; 
				if($conditions !=''){
					$ncds = $this->Ncds->find('all', $conditions);
					}else{
					$ncds = $this->Ncds;
				}  
				$this->paginate = [
				'contain' => ['Companies','Branches', 'Banks','Brokers','Plans']
				]; 
				$ncds = $this->paginate($ncds ,['order' => ['Ncds.branch_id' => 'ASC']]);
				$this->set(compact('ncds','branch','datedform','datedto'));
				$this->set('_serialize', ['ncds']);
			}   
			
			// ipo
			$conditions = ''; 
			if($this->Auth->user('group_id')=='3'){            
				$branch_id = $this->Auth->user('branch_id');     
				$conditions['conditions']['and']['Ipos.posteduserbranchid'] = "$branch_id";
			} 
			if (!empty($this->request->query) && count($this->request->query) > 1) { 
				//$conditions = '';           
				
				if ($datedform && $datedto){
					$conditions = array(
					'conditions' => array(
					'and' => array(
					array('trim(Ipos.despatchdate) >= ' => $datedform,
					'trim(Ipos.despatchdate) <= ' => $datedto
					)
					)));
				}
				if (($branch !='') && ($branch !='all')){  $conditions['conditions']['and']['Ipos.branch_id'] = "$branch"; }
				
				if ($branch != ''  || ($datedform && $datedto)){ 
					$ipos = $this->Ipos->find('all', $conditions);
					}else{
					$ipos = $this->Ipos->find('all', $conditions);
				}    
				$this->paginate = [
				'contain' => ['Companies', 'Branches','Banks','Brokers','Plans']
				]; 
				$ipos = $this->paginate($ipos ,['order' => ['Ipos.branch_id' => 'ASC']]);
				$this->set(compact('ipos','branch','datedform','datedto'));
				$this->set('_serialize', ['ipos']);
				}else{
				$branch =''; $datedform =''; $datedto= ''; 
				if($conditions !=''){
					$ipos = $this->Ipos->find('all', $conditions);
					}else{
					$ipos = $this->Ipos;
				}  
				$this->paginate = [
				'contain' => ['Companies', 'Branches','Banks','Brokers','Plans']
				]; 
				$ipos = $this->paginate($ipos ,['order' => ['Ipos.branch_id' => 'ASC']]);
				$this->set(compact('ipos','branch','datedform','datedto'));
				$this->set('_serialize', ['ipos']);
			}
			
			
		}
		
		public function dailymf(){
			
			$this->loadModel('Mutualfunds');    
			$this->loadModel('Customers'); 
			$this->loadModel('Banks'); 
			$this->loadModel('Plans'); 
			$this->loadModel('Products');
			$all = array('all'=>'All');    
			$typeofdeposit = array(
            'Additional' => 'Additional',
            'Fresh' => 'Fresh',
            'SIP' => 'SIP',            
            'Switch' => 'Switch',
            'SIP - Renewal' => 'SIP - Renewal',
            'STP' => 'STP'
			);
			$this->Search->addFilter('typeofdeposit', [
			'attributes'=>['placeholder'=>'Type of Deposit','label'=>'Type of Deposit :     In order to select multiple type click with ctrl key ','multiple' => 'multiple','class'=>'searchfrm','value' => isset($this->request->query['typeofdeposit'])?$this->request->query['typeofdeposit']:''],  
			'options' => $typeofdeposit,
			
			]);
			
			if($this->Auth->user('group_id')=='2'){ 
				$branch = $this->Mutualfunds->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->Mutualfunds->Branches->find('list')->toArray(),
				]);
			}  
			
			$conditions = ''; 
			
			$this->request->query['datedform'] = $this->request->query['datedto'] = date('d/m/Y');
			if (!empty($this->request->query) && count($this->request->query) > 1) { 
				// $conditions = '';
				if($this->Auth->user('group_id')=='2'){
					if(!empty($this->request->query['branch'])){
						$branch = (($this->request->query['branch'] != '')? $this->request->query['branch'] : '');
						}else{
						$branch = '';   
					}               
				}  
				$dated2 =  (($this->request->query['datedform'] != '')? $this->request->query['datedform'] : '');
				
				$date1 = explode('/',$dated2);
				$datedform = implode('-',$date1);
				
				$datedform  = implode('-', array_reverse($date1));
				//pr($datedform);
				$dated1= $this->request->query['datedto'];
				$date = explode('/',$dated1);
				
				$datedto  = implode('-', array_reverse($date));
				if(!empty($this->request->query['typeofdeposit'])){
					$typeofdeposit_sel = (($this->request->query['typeofdeposit'] != '')? $this->request->query['typeofdeposit'] : ''); 
					}else{
					$typeofdeposit_sel = '';   
				}
				
				$cnt = $total_amount = 0;
				
				if ($datedform && $datedto){             
					$conditions = array(
					'conditions' => array(
					'and' => array(
					array('Mutualfunds.despatchdate >= ' => $datedform,
					'Mutualfunds.despatchdate <= ' => $datedto
					)
					)));
				}
				
				if($this->Auth->user('group_id')=='3'){            
					$branch_id = $this->Auth->user('branch_id');     
					$conditions['conditions']['and']['Mutualfunds.branch_id'] = "$branch_id";
					$branch = '';
				} 
				if($this->Auth->user('group_id')=='2'){
					if (($branch !='') && ($branch !='all')){ $conditions['conditions']['and']['Mutualfunds.branch_id'] = "$branch"; }
				}
				if (!empty($typeofdeposit_sel)){ 
					$conditions['conditions']['and']['Mutualfunds.typeofdeposit IN '] = $typeofdeposit_sel; 			 
				}  
				
				if ($branch != ''  || ($datedform && $datedto)) {
					//pr($conditions);
					$mutualfunds = $this->Mutualfunds->find('all', $conditions);
					
					//$mf1=$this->Mutualfunds->find('all',array('contain' => ['Companies', 'Branches', 'Mffunds','Brokers']), $conditions);
					$cnt = $mutualfunds->count();              
					if($mutualfunds){
						$total_amount = 0;                   
						foreach ($mutualfunds as $md){ 
							$total_amount += $md->amount;
						}
					} 
					
					}else{
					if($conditions !=''){
						$mutualfunds = $this->Mutualfunds->find('all', $conditions);
                        
						$cnt = $mutualfunds->count();              
                        if($mutualfunds){
                            $total_amount = 0;                   
							foreach ($mutualfunds as $md){ 
                                $total_amount += $md->amount;
							}
						} 
						
						}else{
						$mutualfunds = $this->Mutualfunds; 
						$mf1=$this->Mutualfunds->find('all',array('contain' => ['Companies', 'Branches', 'Mffunds','Brokers']));
						$cnt = $mf1->count();              
                        if($mf1){
							$total_amount = 0;                   
							foreach ($mf1 as $md){ 
								$total_amount += $md->amount;
							}
						} 
					} 
				} 
				
				//, 'Banks',
				
				$this->paginate = [
				'contain' => ['Companies', 'Branches', 'Mffunds','Brokers']
				]; 
				
				$mutualfunds = $this->paginate($mutualfunds ,['order' => ['Mutualfunds.despatchdate' => 'ASC','Mutualfunds.typeofdeposit']]);  
				$this->set(compact('mutualfunds','branch','datedform','datedto','total_amount','cnt','dated2','dated1'));
				$this->set('_serialize', ['mutualfunds']);
				}else{
				$branch =''; $datedform =''; $datedto= '';
				$mutualfunds = '';
				$this->set(compact('mutualfunds','branch','datedform','datedto','total_amount','cnt'));         
				$this->set('_serialize', ['mutualfunds']);
			}   
			
		}
		public function dailyfd(){
			$this->loadModel('Fds');     
			$this->loadModel('Customers'); 
			$this->loadModel('Banks'); 
			$this->loadModel('Plans'); 
			$this->loadModel('Products');
			
			$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->request->query['datedform'] = $this->request->query['datedto'] = date('d/m/Y');
			$conditions = '';
			if($this->Auth->user('group_id')=='3'){
				//$conditions = '';  
				$branch_id = $this->Auth->user('branch_id');     
				$conditions['conditions']['and']['Fds.branch_id'] = "$branch_id";
			} 
			
			if (!empty($this->request->query) && count($this->request->query) > 1) { 
				// $conditions = '';
				if($this->Auth->user('group_id')=='2'){
					if(!empty($this->request->query['branch'])){
						$branch = (($this->request->query['branch'] != '')? $this->request->query['branch'] : '');
						}else{
						$branch = '';   
					}	       
				}          
				
				$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 = array(
					'conditions' => array(
					'and' => array(
					array('Fds.despatchdate >= ' => $datedform,
					'Fds.despatchdate <= ' => $datedto
					)
					)));
				}
				if($this->Auth->user('group_id')=='3'){
					$branch_id = $this->Auth->user('branch_id');     
					$conditions['conditions']['and']['Fds.branch_id'] = "$branch_id";
					$branch = '';
				}
				if (($branch !='') && ($branch !='all')){ $conditions['conditions']['and']['Fds.branch_id'] = "$branch"; }
				
				if ($branch != ''  || ($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->find('all', $conditions);
					$cnt = $fds->count();
					if($fds) {
						$total_amount = 0;
						foreach ($fds as $fd) {
							$total_amount += $fd->amount;
						}
					}
				} 
				//, 'Banks'
				$this->paginate = [
                'contain' => ['Companies', 'Branches','Brokers', 'Plans']
				]; 
				$fds = $this->paginate($fds ,['order' => ['Fds.despatchdate' => 'ASC']]);
				$this->set(compact('fds','branch','datedform','datedto','dated2','dated1','cnt','total_amount'));
				$this->set('_serialize', ['fds']);
				}else{
				$branch =''; $datedform =''; $datedto= '';
				/*  if($conditions !=''){
					$fds = $this->Fds->find('all', $conditions);
					}else{
					$fds = $this->Fds;
					}
					
					//, 'Banks'
					$this->paginate = [
					'contain' => ['Companies', 'Branches','Brokers', 'Plans']
					]; 
					$fds = $this->paginate($fds ,['order' => ['Fds.branch_id' => 'ASC']]);
					* 
				*/
				$fds = '';
				$this->set(compact('fds','branch','datedform','datedto'));
				$this->set('_serialize', ['fds']);
			}   
			
		}
		public function dailyncd(){      
			$this->loadModel('Ncds');      
			$this->loadModel('Customers'); 
			$this->loadModel('Banks'); 
			$this->loadModel('Plans'); 
			$this->loadModel('Products');
			$all = array('all'=>'All');    
			
			if($this->Auth->user('group_id')=='2'){ 
				$branch = $this->Ncds->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->Ncds->Branches->find('list')->toArray(),
				]);
			}  
			
			$this->request->query['datedform'] = $this->request->query['datedto'] = date('d/m/Y');
			$conditions = ''; 
			
			if (!empty($this->request->query) && count($this->request->query) > 1) { 
				if($this->Auth->user('group_id')=='2'){
					if(!empty($this->request->query['branch'])){
						$branch = (($this->request->query['branch'] != '')? $this->request->query['branch'] : '');
						}else{
						$branch = '';   
					}	               
				} 
				$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 = array(
					'conditions' => array(
					'and' => array(
					array('Ncds.despatchdate >= ' => $datedform,
					'Ncds.despatchdate <= ' => $datedto
					)
					)));
				}
				if($this->Auth->user('group_id')=='3'){             
					$branch_id = $this->Auth->user('branch_id');     
					$conditions['conditions']['and']['Ncds.branch_id'] = "$branch_id";
					$branch = '';
				} 
				if (($branch !='') && ($branch !='all')){  $conditions['conditions']['and']['Ncds.branch_id'] = "$branch"; }
				
				if ($branch != ''  || ($datedform && $datedto)){ 
					$ncds = $this->Ncds->find('all', $conditions);
					$cnt = $ncds->count();
					if($ncds) {
						$total_amount = 0;
						foreach ($ncds as $ncd) {
							$total_amount += $ncd->amount;
						}
					}
					}else{
					$ncds = $this->Ncds->find('all', $conditions);
					$cnt = $ncds->count();
					if($ncds) {
						$total_amount = 0;
						foreach ($ncds as $ncd) {
							$total_amount += $ncd->amount;
						}
					}
				}    
				$this->paginate = [
				'contain' => ['Companies','Branches','Brokers','Plans']
				]; 
				$ncds = $this->paginate($ncds ,['order' => ['Ncds.despatchdate' => 'ASC']]);
				$this->set(compact('ncds','branch','datedform','datedto','dated1','dated2','total_amount','cnt'));
				$this->set('_serialize', ['ncds']);
				}else{
				$branch =''; $datedform =''; $datedto= ''; 
				
				$ncds = '';
				$this->set(compact('ncds','branch','datedform','datedto'));
				$this->set('_serialize', ['ncds']);
			}   
		}
		public function dailyipo(){     
			$this->loadModel('Ipos'); 
			$this->loadModel('Customers'); 
			$this->loadModel('Banks'); 
			$this->loadModel('Plans'); 
			$this->loadModel('Products');
			$all = array('all'=>'All');    
			
			if($this->Auth->user('group_id')=='2'){ 
				$branch = $this->Ipos->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->Ipos->Branches->find('list')->toArray(),
				]);
			}  
			
			$this->request->query['datedform'] = $this->request->query['datedto'] = date('d/m/Y');
			$conditions = ''; 
			
			if (!empty($this->request->query) && count($this->request->query) > 1) { 
				if($this->Auth->user('group_id')=='2'){
					if(!empty($this->request->query['branch'])){
						$branch = (($this->request->query['branch'] != '')? $this->request->query['branch'] : '');
						}else{
						$branch = '';   
					}	             
				} 
				$dated2 =  (($this->request->query['datedform'] != '')? $this->request->query['datedform'] : '');
				
				
				$date1 = explode('/',$dated2);
				
				$datedform  = implode('-', array_reverse($date1));
				//pr($datedform);
				$dated1= $this->request->query['datedto'];
				$date = explode('/',$dated1);
				
				$datedto  = implode('-', array_reverse($date));
				
				if ($datedform && $datedto){
					$conditions = array(
					'conditions' => array(
					'and' => array(
					array('Ipos.despatchdate >= ' => $datedform,
					'Ipos.despatchdate <= ' => $datedto
					)
					)));
				}
				if($this->Auth->user('group_id')=='3'){            
					$branch_id = $this->Auth->user('branch_id');     
					$conditions['conditions']['and']['Ipos.branch_id'] = "$branch_id";
					$branch = '';
				} 
				if (($branch !='') && ($branch !='all')){  $conditions['conditions']['and']['Ipos.branch_id'] = "$branch"; }
				
				if ($branch != ''  || ($datedform && $datedto)){ 
					$ipos = $this->Ipos->find('all', $conditions);
					$cnt = $ipos->count();
					if($ipos) {
						$total_amount = 0;
						foreach ($ipos as $ipo) {
							$total_amount += $ipo->amount;
						}
					}
					}else{
					$ipos = $this->Ipos->find('all', $conditions);
					$cnt = $ipos->count();
					if($fds) {
						$total_amount = 0;
						foreach ($ipos as $ipo) {
							$total_amount += $ipo->amount;
						}
					}
				}    
				$this->paginate = [
				'contain' => ['Companies', 'Branches','Brokers','Plans']
				]; 
				$ipos = $this->paginate($ipos ,['order' => ['Ipos.despatchdate' => 'ASC']]);
				$this->set(compact('ipos','branch','datedform','datedto','dated1','total_amount','dated2'));
				$this->set('_serialize', ['ipos']);
				}else{
				$branch =''; $datedform =''; $datedto= ''; 
				$ipos = '';
				$this->set(compact('ipos','branch','datedform','datedto'));
				$this->set('_serialize', ['ipos']);
			}
		}
		
		public function todaydespatch(){
			$this->loadModel('Fds'); 
			$this->loadModel('Mutualfunds');
			$this->loadModel('Ncds'); 
			$this->loadModel('Ipos');    
			$this->loadModel('Customers'); 
			$this->loadModel('Banks'); 
			$this->loadModel('Plans'); 
			$this->loadModel('Products');
			$all = array('all'=>'All');         
			if($this->Auth->user('group_id')=='2'){ 
				$branch = $this->Mutualfunds->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->Mutualfunds->Branches->find('list')->toArray(),
				]);
			} 
			$conditions = ''; 
			
			if((empty($this->request->query['datedform'])) &&(empty($this->request->query['datedto']))){

			    $this->request->query['datedform'] = $this->request->query['datedto'] = date('d/m/Y');

            }
            $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) { 
				if($this->Auth->user('group_id')=='2'){
					if(!empty($this->request->query['branch'])){
						$branch = (($this->request->query['branch'] != '')? $this->request->query['branch'] : '');
						}else{
						$branch = '';   
					}               
				}  
				$dated2 =  (($this->request->query['datedform'] != '')? $this->request->query['datedform'] : '');
				$date1 = explode('/',$dated2);
				$datedform = implode('-',$date1);
				$datedform  = implode('-', array_reverse($date1));
				$dated1= $this->request->query['datedto'];
				$date = explode('/',$dated1);
				$datedto  = implode('-', array_reverse($date));
				$cnt = 0;
				if ($datedform && $datedto){             
					$conditions = array(
					'conditions' => array(
					'and' => array(
					array('Mutualfunds.despatchdate >= ' => $datedform,
					'Mutualfunds.despatchdate <= ' => $datedto
					)
					)));
				}
				
				if($this->Auth->user('group_id')=='3'){            
					$branch_id = $this->Auth->user('branch_id');     
					$conditions['conditions']['and']['Mutualfunds.branch_id'] = "$branch_id";
					$branch ='';
				} 
				if (($branch !='') && ($branch !='all')){ $conditions['conditions']['and']['Mutualfunds.branch_id'] = "$branch"; }
				if ($branch != ''  || ($datedform && $datedto)) {
					$mutualfunds = $this->Mutualfunds->find('all', $conditions);
					$cnt = $mutualfunds->count();              
					}else{
					if($conditions !=''){
						$mutualfunds = $this->Mutualfunds->find('all', $conditions);  
						$cnt = $mutualfunds->count();                  
						}else{
						$mutualfunds = $this->Mutualfunds; 
						$mf1=$this->Mutualfunds->find('all',array('contain' => ['Companies', 'Branches', 'Mffunds','Brokers']));
						$cnt = $mf1->count();              
					} 
				} 
				$this->paginate = [
				'contain' => ['Companies', 'Branches', 'Mffunds','Brokers'],'limit'=>$mutualfunds->count()
				]; 
				
				$mutualfunds = $this->paginate($mutualfunds ,['order' => ['Mutualfunds.despatchdate' => 'ASC','Mutualfunds.typeofdeposit']],'limit');  
				$this->set(compact('mutualfunds','branch','datedform','datedto','cnt','dated2','dated1'));
				$this->set('_serialize', ['mutualfunds']);
				}else{
				$branch =''; $datedform =''; $datedto= '';
				$mutualfunds = '';
				$this->set(compact('mutualfunds','branch','datedform','datedto','cnt'));         
				$this->set('_serialize', ['mutualfunds']);
				
			} 
			$subbrokers = $this->Fds->Brokers->find('list', [
                'keyField' => 'id',
                'valueField' => 'name'
            ])-> where(['role' => 'Subbroker'])->toArray();
            $this->set(compact('subbrokers'));
			
			//End MFs
			//start FDS
			
			$conditions = '';
			
			
			if (!empty($this->request->query) && count($this->request->query) > 1) { 
				if($this->Auth->user('group_id')=='2'){
					if(!empty($this->request->query['branch'])){
						$branch = (($this->request->query['branch'] != '')? $this->request->query['branch'] : '');
						}else{
						$branch = '';   
					}	       
				}
				$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 = array(
					'conditions' => array(
					'and' => array(
					array('Fds.despatchdate >= ' => $datedform,
					'Fds.despatchdate <= ' => $datedto
					)
					)));
				}
				if($this->Auth->user('group_id')=='3'){
					$branch_id = $this->Auth->user('branch_id');     
					$conditions['conditions']['and']['Fds.branch_id'] = "$branch_id";
					$branch ='';
				}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";
                }
				if (($branch !='') && ($branch !='all')){ $conditions['conditions']['and']['Fds.branch_id'] = "$branch"; }
				if ($branch != ''  || ($datedform && $datedto)){                 
					$fds = $this->Fds->find('all', $conditions);
					$cnt = $fds->count();     
					}else{
					$fds = $this->Fds->find('all', $conditions);
					$cnt = $fds->count();
				} 
				$this->paginate = [
                'contain' => ['Companies', 'Branches','Brokers', 'Plans'],'limit'=>$fds->count()
				]; 
				$fds = $this->paginate($fds ,['order' => ['Fds.despatchdate' => 'ASC']]);
				$this->set(compact('fds','branch','datedform','datedto','dated2','dated1','cnt'));
				$this->set('_serialize', ['fds']);
				}else{
				$branch =''; $datedform =''; $datedto= '';
				$fds = '';
				$this->set(compact('fds','branch','datedform','datedto'));
				$this->set('_serialize', ['fds']);
			}   
			
			//End FDS
			//Start NCDS
			
			$conditions = ''; 
			
			if (!empty($this->request->query) && count($this->request->query) > 1) { 
				if($this->Auth->user('group_id')=='2'){
					if(!empty($this->request->query['branch'])){
						$branch = (($this->request->query['branch'] != '')? $this->request->query['branch'] : '');
						}else{
						$branch = '';   
					}	               
				}
				$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 = array(
					'conditions' => array(
					'and' => array(
					array('Ncds.despatchdate >= ' => $datedform,
					'Ncds.despatchdate <= ' => $datedto
					)
					)));
				}
				if($this->Auth->user('group_id')=='3'){             
					$branch_id = $this->Auth->user('branch_id');     
					$conditions['conditions']['and']['Ncds.branch_id'] = "$branch_id";
					$branch ='';
				} 
				if (($branch !='') && ($branch !='all')){  $conditions['conditions']['and']['Ncds.branch_id'] = "$branch"; }
				if ($branch != ''  || ($datedform && $datedto)){ 
					$ncds = $this->Ncds->find('all', $conditions);
					$cnt = $ncds->count();
					}else{
					$ncds = $this->Ncds->find('all', $conditions);
					$cnt = $ncds->count();
				}    
				$this->paginate = [
				'contain' => ['Companies','Branches','Brokers','Plans'],'limit'=>$ncds->count()
				]; 
				$ncds = $this->paginate($ncds ,['order' => ['Ncds.despatchdate' => 'ASC']]);
				$this->set(compact('ncds','branch','datedform','datedto','dated1','dated2','cnt'));
				$this->set('_serialize', ['ncds']);
				}else{
				$branch =''; $datedform =''; $datedto= ''; 
				$ncds = '';
				$this->set(compact('ncds','branch','datedform','datedto'));
				$this->set('_serialize', ['ncds']);
			}
			
			//End NCDS
			//Start IPO
			
			$conditions = '';
			if (!empty($this->request->query) && count($this->request->query) > 1) { 
				if($this->Auth->user('group_id')=='2'){
					if(!empty($this->request->query['branch'])){
						$branch = (($this->request->query['branch'] != '')? $this->request->query['branch'] : '');
						}else{
						$branch = '';   
					}	             
				} 
				$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 = array(
					'conditions' => array(
					'and' => array(
					array('Ipos.despatchdate >= ' => $datedform,
					'Ipos.despatchdate <= ' => $datedto
					)
					)));
				}
				if($this->Auth->user('group_id')=='3'){            
					$branch_id = $this->Auth->user('branch_id');     
					$conditions['conditions']['and']['Ipos.branch_id'] = "$branch_id";
					$branch = '';
				} 
				if (($branch !='') && ($branch !='all')){  $conditions['conditions']['and']['Ipos.branch_id'] = "$branch"; }
				if ($branch != ''  || ($datedform && $datedto)){ 
					$ipos = $this->Ipos->find('all', $conditions);
					$cnt = $ipos->count();	
					}else{
					$ipos = $this->Ipos->find('all', $conditions);
					$cnt = $ipos->count();	
				}    
				$this->paginate = [
                'contain' => ['Companies', 'Branches','Brokers','Plans'],'limit'=>$ipos->count()
				]; 
				$ipos = $this->paginate($ipos ,['order' => ['Ipos.despatchdate' => 'ASC']]);
				$this->set(compact('ipos','branch','datedform','datedto','dated1','dated2'));
				$this->set('_serialize', ['ipos']);
				}else{
				$branch =''; $datedform =''; $datedto= ''; 
				$ipos = '';
				$this->set(compact('ipos','branch','datedform','datedto'));
				$this->set('_serialize', ['ipos']);
			}
			if(!empty($this->request->query['export']) && ($this->request->query['export']=='xls') ){
                $this->set('filename', 'Despatch_Report');
                $this->render('export_todaydespatch','export_xls');
            }
		}
		
		
		public function dtwsmf(){
			$this->loadModel('Mutualfunds');    
			$this->loadModel('Customers'); 
			$this->loadModel('Banks'); 
			$this->loadModel('Plans'); 
			$this->loadModel('Products');
			$all = array('all'=>'All');
			$typeofdeposit = array(
            'Additional' => 'Additional',
            'Fresh' => 'Fresh',
            'SIP' => 'SIP',            
            'Switch' => 'Switch',
            'SIP - Renewal' => 'SIP - Renewal',
            'STP' => 'STP'
			);
			$this->Search->addFilter('typeofdeposit', [
			'attributes'=>['placeholder'=>'Type of Deposit','label'=>'Type of Deposit :     In order to select multiple type click with ctrl key ','multiple' => 'multiple','class'=>'searchfrm','value' => isset($this->request->query['typeofdeposit'])?$this->request->query['typeofdeposit']:''],  
			'options' => $typeofdeposit,]);
			
			if($this->Auth->user('group_id')=='2'){ 
				$branch = $this->Mutualfunds->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->Mutualfunds->Branches->find('list')->toArray(),
				]);
			}  
			
			$this->Search->addFilter('datedform', [
			'attributes'=>['placeholder'=>'Despatch Start Date','label'=>'Despatch Start Date :','class'=>'searchfrm acb','value' => isset($this->request->query['datedform'])?$this->request->query['datedform']:''],
			]);
			$this->Search->addFilter('datedto', [
			'attributes'=>['placeholder'=>'Despatch End Date','label'=>'Despatch End Date :','class'=>'searchfrm abc','value' => isset($this->request->query['datedto'])?$this->request->query['datedto']:''],
			]);
			
			$conditions = ''; 
			
			if (!empty($this->request->query) && count($this->request->query) > 1) { 
				// $conditions = '';
				if($this->Auth->user('group_id')=='2'){
					if(!empty($this->request->query['branch'])){
						$branch = (($this->request->query['branch'] != '')? $this->request->query['branch'] : '');
						}else{
						$branch = '';   
					}              
				}  
				$dated2 =  (($this->request->query['datedform'] != '')? $this->request->query['datedform'] : '');
				$date1 = explode('/',$dated2);
				$datedform = implode('-',$date1);
				$datedform  = implode('-', array_reverse($date1));
				//pr($datedform);
				$dated1= $this->request->query['datedto'];
				$date = explode('/',$dated1);
				$datedto  = implode('-', array_reverse($date));
				if(!empty($this->request->query['typeofdeposit'])){
					$typeofdeposit_sel = (($this->request->query['typeofdeposit'] != '')? $this->request->query['typeofdeposit'] : ''); 
					}else{
					$typeofdeposit_sel = '';   
				}
				
				$cnt = $total_amount = 0;
				if ($datedform && $datedto){             
					$conditions = array(
					'conditions' => array(
					'and' => array(
					array('Mutualfunds.despatchdate >= ' => $datedform,
					'Mutualfunds.despatchdate <= ' => $datedto
					)
					)));
				}
				if($this->Auth->user('group_id')=='3'){            
					$branch_id = $this->Auth->user('branch_id');     
					$conditions['conditions']['and']['Mutualfunds.branch_id'] = "$branch_id";
					$branch = '';
				} 
				if (($branch !='') && ($branch !='all')){ $conditions['conditions']['and']['Mutualfunds.branch_id'] = "$branch"; }
				if (!empty($typeofdeposit_sel)){ 
					$conditions['conditions']['and']['Mutualfunds.typeofdeposit IN '] = $typeofdeposit_sel; 			 
				}  
				if ($branch != ''  || ($datedform && $datedto)) {
					$mutualfunds = $this->Mutualfunds->find('all', $conditions);
					$cnt = $mutualfunds->count();              
					if($mutualfunds){
						$total_amount = 0;                   
						foreach ($mutualfunds as $md){ 
							$total_amount += $md->amount;
						}
					} 
					
					}else{
					if($conditions !=''){
						$mutualfunds = $this->Mutualfunds->find('all', $conditions);  
						$cnt = $mutualfunds->count();              
                        if($mutualfunds){
                            $total_amount = 0;                   
							foreach ($mutualfunds as $md){ 
								$total_amount += $md->amount;
							}
						} 
						}else{
						$mutualfunds = $this->Mutualfunds; 
						$mf1=$this->Mutualfunds->find('all',array('contain' => ['Companies', 'Branches', 'Mffunds','Brokers']));
						$cnt = $mf1->count();              
                        if($mf1){
							$total_amount = 0;                   
							foreach ($mf1 as $md){ 
								$total_amount += $md->amount;
							}
						} 
					} 
				} 
				
				//, 'Banks',
				$this->paginate = [
                'contain' => ['Companies', 'Branches', 'Mffunds','Brokers']
				]; 
				
				$mutualfunds = $this->paginate($mutualfunds ,['order' => ['Mutualfunds.despatchdate' => 'ASC','Mutualfunds.typeofdeposit']]);  
				$this->set(compact('mutualfunds','branch','datedform','datedto','total_amount','cnt','dated2','dated1'));
				$this->set('_serialize', ['mutualfunds']);
				}else{
				$branch =''; $datedform =''; $datedto= '';
				$mutualfunds = '';
				$this->set(compact('mutualfunds','branch','datedform','datedto','total_amount','cnt'));         
				$this->set('_serialize', ['mutualfunds']);
			}      
		}
		
		public function dtwsfd(){
			$this->loadModel('Fds');     
			$this->loadModel('Customers'); 
			$this->loadModel('Banks'); 
			$this->loadModel('Plans'); 
			$this->loadModel('Products');
			
			$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('datedform', [
			'attributes'=>['placeholder'=>'Despatch Start Date','label'=>'Despatch Start Date :','class'=>'searchfrm','value' => isset($this->request->query['datedform'])?$this->request->query['datedform']:''],
			]);
			$this->Search->addFilter('datedto', [
			'attributes'=>['placeholder'=>'Despatch End Date','label'=>'Despatch End Date :','class'=>'searchfrm','value' => isset($this->request->query['datedto'])?$this->request->query['datedto']:''],
			]);
			
			$conditions = '';
			
			if (!empty($this->request->query) && count($this->request->query) > 1) { 
				// $conditions = '';
				if($this->Auth->user('group_id')=='2'){
					if(!empty($this->request->query['branch'])){
						$branch = (($this->request->query['branch'] != '')? $this->request->query['branch'] : '');
						}else{
						$branch = '';   
					}			  
				}    
				$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 = array(
					'conditions' => array(
					'and' => array(
					array('Fds.despatchdate >= ' => $datedform,
					'Fds.despatchdate <= ' => $datedto
					)
					)));
				}
				if($this->Auth->user('group_id')=='3'){
					$branch_id = $this->Auth->user('branch_id');     
					$conditions['conditions']['and']['Fds.branch_id'] = "$branch_id";
					$branch ='';
				} 
				if (($branch !='') && ($branch !='all')){ $conditions['conditions']['and']['Fds.branch_id'] = "$branch"; }
				
				if ($branch != ''  || ($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->find('all', $conditions);
					$cnt = $fds->count();
					if($fds) {
						$total_amount = 0;
						foreach ($fds as $fd) {
							$total_amount += $fd->amount;
						}
					}
				} 
				//, 'Banks'
				$this->paginate = [
                'contain' => ['Companies', 'Branches','Brokers', 'Plans']
				]; 
				$fds = $this->paginate($fds ,['order' => ['Fds.despatchdate' => 'ASC']]);
				$this->set(compact('fds','branch','datedform','datedto','dated2','dated1','cnt','total_amount'));
				$this->set('_serialize', ['fds']);
				}else{
				$branch =''; $datedform =''; $datedto= '';
				$fds = '';
				$this->set(compact('fds','branch','datedform','datedto'));
				$this->set('_serialize', ['fds']);
			}   
			
		}
		
		public function dtwsncd(){      
			$this->loadModel('Ncds');      
			$this->loadModel('Customers'); 
			$this->loadModel('Banks'); 
			$this->loadModel('Plans'); 
			$this->loadModel('Products');
			$all = array('all'=>'All');    
			
			if($this->Auth->user('group_id')=='2'){ 
				$branch = $this->Ncds->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->Ncds->Branches->find('list')->toArray(),
				]);
			}  
			
			$this->Search->addFilter('datedform', [
			'attributes'=>['placeholder'=>'Despatch Start Date','label'=>'Despatch Start Date :','class'=>'searchfrm','value' => isset($this->request->query['datedform'])?$this->request->query['datedform']:''],
			]);
			$this->Search->addFilter('datedto', [
			'attributes'=>['placeholder'=>'Despatch End Date','label'=>'Despatch End Date :','class'=>'searchfrm','value' => isset($this->request->query['datedto'])?$this->request->query['datedto']:''],
			]);
			
			$conditions = ''; 
			
			
			if (!empty($this->request->query) && count($this->request->query) > 1) { 
				if($this->Auth->user('group_id')=='2'){
					if(!empty($this->request->query['branch'])){
						$branch = (($this->request->query['branch'] != '')? $this->request->query['branch'] : '');
						}else{
						$branch = '';   
					}              
				} 
				
				$dated2 =  (($this->request->query['datedform'] != '')? $this->request->query['datedform'] : '');
				$date1 = explode('/',$dated2);
				$datedform  = implode('-', array_reverse($date1));
				//pr($datedform);
				$dated1= $this->request->query['datedto'];
				$date = explode('/',$dated1);
				
				$datedto  = implode('-', array_reverse($date));
				if ($datedform && $datedto){
					$conditions = array(
					'conditions' => array(
					'and' => array(
					array('Ncds.despatchdate >= ' => $datedform,
					'Ncds.despatchdate <= ' => $datedto
					)
					)));
				}
				if($this->Auth->user('group_id')=='3'){             
					$branch_id = $this->Auth->user('branch_id');     
					$conditions['conditions']['and']['Ncds.branch_id'] = "$branch_id";
					$branch = '';
				} 
				if (($branch !='') && ($branch !='all')){  $conditions['conditions']['and']['Ncds.branch_id'] = "$branch"; }
				if ($branch != ''  || ($datedform && $datedto)){ 
					$ncds = $this->Ncds->find('all', $conditions);
					$cnt = $ncds->count();
					if($ncds) {
						$total_amount = 0;
						foreach ($ncds as $ncd) {
							$total_amount += $ncd->amount;
						}
					}
					}else{
					$ncds = $this->Ncds->find('all', $conditions);
					$cnt = $ncds->count();
					if($ncds) {
						$total_amount = 0;
						foreach ($ncds as $ncd) {
							$total_amount += $ncd->amount;
						}
					}
				}    
				$this->paginate = [
				'contain' => ['Companies','Branches','Brokers','Plans']
				]; 
				$ncds = $this->paginate($ncds ,['order' => ['Ncds.despatchdate' => 'ASC']]);
				$this->set(compact('ncds','branch','datedform','datedto','dated1','dated2','total_amount','cnt'));
				$this->set('_serialize', ['ncds']);
				}else{
				$branch =''; $datedform =''; $datedto= ''; 
				$ncds = '';
				$this->set(compact('ncds','branch','datedform','datedto'));
				$this->set('_serialize', ['ncds']);
			}   
		}
		public function dtwsipo(){     
			$this->loadModel('Ipos'); 
			$this->loadModel('Customers'); 
			$this->loadModel('Banks'); 
			$this->loadModel('Plans'); 
			$this->loadModel('Products');
			$all = array('all'=>'All');    
			if($this->Auth->user('group_id')=='2'){ 
				$branch = $this->Ipos->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->Ipos->Branches->find('list')->toArray(),
				]);
			}  
			
			$this->Search->addFilter('datedform', [
			'attributes'=>['placeholder'=>'Despatch Start Date','label'=>'Despatch Start Date :','class'=>'searchfrm','value' => isset($this->request->query['datedform'])?$this->request->query['datedform']:''],
			]);
			$this->Search->addFilter('datedto', [
			'attributes'=>['placeholder'=>'Despatch End Date','label'=>'Despatch End Date :','class'=>'searchfrm','value' => isset($this->request->query['datedto'])?$this->request->query['datedto']:''],
			]);
			
			$conditions = ''; 
			
			//$this->request->query['datedform'] = $this->request->query['datedto'] = date('d/m/Y');
			if (!empty($this->request->query) && count($this->request->query) > 1) { 
				if($this->Auth->user('group_id')=='2'){
					if(!empty($this->request->query['branch'])){
						$branch = (($this->request->query['branch'] != '')? $this->request->query['branch'] : '');
						}else{
						$branch = '';   
					}              
				} 
				$dated2 =  (($this->request->query['datedform'] != '')? $this->request->query['datedform'] : '');
				
				
				$date1 = explode('/',$dated2);
				
				$datedform  = implode('-', array_reverse($date1));
				//pr($datedform);
				$dated1= $this->request->query['datedto'];
				$date = explode('/',$dated1);
				
				$datedto  = implode('-', array_reverse($date));
				if ($datedform && $datedto){
					$conditions = array(
					'conditions' => array(
					'and' => array(
					array('Ipos.despatchdate >= ' => $datedform,
					'Ipos.despatchdate <= ' => $datedto
					)
					)));
				}
				if($this->Auth->user('group_id')=='3'){            
					$branch_id = $this->Auth->user('branch_id');     
					$conditions['conditions']['and']['Ipos.branch_id'] = "$branch_id";
					$branch = '';
				}
				if (($branch !='') && ($branch !='all')){  $conditions['conditions']['and']['Ipos.branch_id'] = "$branch"; }
				
				if ($branch != ''  || ($datedform && $datedto)){ 
					$ipos = $this->Ipos->find('all', $conditions);
					$cnt = $ipos->count();
					if($ipos) {
						$total_amount = 0;
						foreach ($ipos as $ipo) {
							$total_amount += $ipo->amount;
						}
					}
					}else{
					$ipos = $this->Ipos->find('all', $conditions);
					$cnt = $ipos->count();
					if($fds) {
						$total_amount = 0;
						foreach ($ipos as $ipo) {
							$total_amount += $ipo->amount;
						}
					}
				}    
				$this->paginate = [
				'contain' => ['Companies', 'Branches','Brokers','Plans']
				]; 
				$ipos = $this->paginate($ipos ,['order' => ['Ipos.despatchdate' => 'ASC']]);
				$this->set(compact('ipos','branch','datedform','datedto','dated1','total_amount','dated2'));
				$this->set('_serialize', ['ipos']);
				}else{
				$branch =''; $datedform =''; $datedto= ''; 
				$ipos = '';
				$this->set(compact('ipos','branch','datedform','datedto'));
				$this->set('_serialize', ['ipos']);
			}
		}
		public function dailymfexcelreport(){   
			$this->autoRender = false;
			// $this->layout = false;        
			$fileName = "dailyreportsmf_".date("d-m-y:h:s").".xls";  
			$this->loadModel('Mutualfunds');
			$this->loadModel('Customers'); 
			$this->loadModel('Banks'); 
			$this->loadModel('Plans'); 
			
			$conditions = '';  
			if($this->Auth->user('group_id')=='3'){           
				$branch_id = $this->Auth->user('branch_id');     
				$conditions['conditions']['and']['Mutualfunds.posteduserbranchid'] = "$branch_id";
			}  
			
			$branch = $this->request->query['branch'];   
			$datedform = $this->request->query['datedform'];
			$datedto = $this->request->query['datedto'];
			
            if ($datedform && $datedto){
				$conditions = array(
				'conditions' => array(
				'and' => array(
				array('trim(Mutualfunds.despatchdate) >= ' => $datedform,
				'trim(Mutualfunds.despatchdate) <= ' => $datedto
				)
				)));
			}
			if (($branch !='') && ($branch !='all')){  $conditions['conditions']['or']['Mutualfunds.branch_id'] = "$branch"; }
            
            if ($branch != ''  || ($datedform && $datedto)){ 
				$mutualfunds = $this->Mutualfunds->find('all', $conditions);
				}else{                
				if($conditions !=''){
                    $mutualfunds = $this->Mutualfunds->find('all', $conditions);
					}else{
					$mutualfunds = $this->Mutualfunds; 
				} 
			}   
            
            //, 'Banks'
			$this->paginate = [
			'contain' => ['Companies', 'Branches','Brokers', 'Mffunds']
            ];
			$mutualfunds = $this->paginate($mutualfunds ,['order' => ['Mutualfunds.branch_id' => 'ASC']]);
			$datafds = $mutualfunds->toArray(); 
			//pr($datafds);  
			
			$banks = TableRegistry::get('Banks');
			
			$headerRow = array("Sr.","Company", "Type of Deposit",'Name','Product','Scheme','Amount','Cheque No','Folio No','Application No','Bank Name');
			$j=1;
            for($i=0; $i< count($datafds); $i++){
				if($datafds[$i]->bank_id > 0){
					$banksid = $banks->find()->where(['id' => $datafds[$i]->bank_id]);
					$data1 = $banksid->toArray();
					$banksName = $data1[0]->banks;
					}else{
					$banksName = '';
				}   
                $data[] = [$j,$datafds[$i]->company->company,$datafds[$i]->typeofdeposit,$datafds[$i]->firstapplicantname,"Mutual Fund",$datafds[$i]->mffund->fundname,$datafds[$i]->amount,$datafds[$i]->chequeno,$datafds[$i]->customerFolioNo,$datafds[$i]->applicationno,$banksName];
				$j++;
			} 
			$this->ExportXls->export($fileName, $headerRow, $data);
		}  
		
		public function dailyfdsexcelreport(){   $conditions = '';
			$this->autoRender = false;
			// $this->layout = false;        
			$fileName = "dailyreportsfds_".date("d-m-y:h:s").".xls";        
			$this->loadModel('Fds');
			$this->loadModel('Customers'); 
			$this->loadModel('Banks'); 
			$this->loadModel('Plans'); 
			
			if($this->Auth->user('group_id')=='3'){           
				$branch_id = $this->Auth->user('branch_id');     
				$conditions['conditions']['and']['Fds.posteduserbranchid'] = "$branch_id";
			}  
			$branch = $this->request->query['branch'];   
			$datedform = $this->request->query['datedform'];
			$datedto = $this->request->query['datedto'];
			
			if ($datedform && $datedto){
				$conditions = array(
				'conditions' => array(
				'and' => array(
				array('trim(Fds.despatchdate) >= ' => $datedform,
				'trim(Fds.despatchdate) <= ' => $datedto
				)
				)));
			}
			if (($branch !='') && ($branch !='all')){  $conditions['conditions']['or']['Fds.branch_id'] = "$branch"; }
            if ($branch != ''  || ($datedform && $datedto)){                 
				$fds = $this->Fds->find('all', $conditions);
				}else{
                if($conditions !=''){
					$fds = $this->Fds->find('all', $conditions);
					}else{
					$fds = $this->Fds;
				}  
			}  
			$this->paginate = [
			'contain' => ['Companies', 'Branches','Brokers', 'Plans', 'Banks']
            ];
			$fds = $this->paginate($fds ,['order' => ['Fds.branch_id' => 'ASC']]);
			$datafds = $fds->toArray(); 
			// pr($datafds);    
			
			$headerRow = array("Sr.","Company", "Type of Deposit",'Name','Product','Scheme','Amount','Cheque No','Folio/FDR No','Application No','Bank Name');
			$j=1;
            for($i=0; $i< count($datafds); $i++){
                $data[] = [$j,$datafds[$i]->company->company,$datafds[$i]->typeofdeposit,$datafds[$i]->fapplicantname,"Fixed Deposit",$datafds[$i]->plan->Planname,$datafds[$i]->amount,$datafds[$i]->chequeno,$datafds[$i]->fdrno,$datafds[$i]->applicationno,$datafds[$i]->bank->banks];
				$j++;
			} 
			$this->ExportXls->export($fileName, $headerRow, $data);
		}       
		public function dailyncdexcelreport(){   
			$this->autoRender = false;
			// $this->layout = false;        
			$fileName = "dailyreportsncd_".date("d-m-y:h:s").".xls"; 
			$this->loadModel('Ncds'); 
			$this->loadModel('Customers'); 
			$this->loadModel('Banks'); 
			$this->loadModel('Plans'); 
			$conditions = ''; 
			if($this->Auth->user('group_id')=='3'){             
				$branch_id = $this->Auth->user('branch_id');     
				$conditions['conditions']['and']['Ncds.posteduserbranchid'] = "$branch_id";
			}  
			$branch = $this->request->query['branch'];   
			$datedform = $this->request->query['datedform'];
			$datedto = $this->request->query['datedto'];
			
			if ($datedform && $datedto){
				$conditions = array(
				'conditions' => array(
				'and' => array(
				array('trim(Ncds.despatchdate) >= ' => $datedform,
				'trim(Ncds.despatchdate) <= ' => $datedto
				)
				)));
			}
			if (($branch !='') && ($branch !='all')){  $conditions['conditions']['or']['Ncds.branch_id'] = "$branch"; }
			
            if ($branch != ''  || ($datedform && $datedto)){ 
				$ncds = $this->Ncds->find('all', $conditions);
				}else{
                if($conditions !=''){
                    $ncds = $this->Ncds->find('all', $conditions);
					}else{
                    $ncds = $this->Ncds;
				} 
			}    
			$this->paginate = [
            'contain' => ['Companies','Branches', 'Banks','Brokers','Plans']
			];
			$ncds = $this->paginate($ncds,['order' => ['Ncds.branch_id' => 'ASC']]);
			$datafds = $ncds->toArray(); 
			// pr($datafds);    
			
			$headerRow = array("Sr.","Company", "Type of Deposit",'Name','Product','Scheme','Amount','Cheque No','Folio/FDR No','Application No','Bank Name');
			$j=1;
            for($i=0; $i< count($datafds); $i++){
                $data[] = [$j,$datafds[$i]->company->company,$datafds[$i]->typeofdeposit,$datafds[$i]->firstapplicantname,"NCD",$datafds[$i]->plan->Planname,$datafds[$i]->amount,$datafds[$i]->chequeno,$datafds[$i]->fdrno,$datafds[$i]->applicationno,$datafds[$i]->bank->banks];
				$j++;
			} 
			$this->ExportXls->export($fileName, $headerRow, $data);
		} 
		public function dailyipoexcelreport(){   
			$this->autoRender = false;
			// $this->layout = false;        
			$fileName = "dailyreportsipo_".date("d-m-y:h:s").".xls"; 
			$this->loadModel('Ipos'); 
			$this->loadModel('Customers'); 
			$this->loadModel('Banks'); 
			$this->loadModel('Plans'); 
			$conditions = '';  
			if($this->Auth->user('group_id')=='3'){           
				$branch_id = $this->Auth->user('branch_id');     
				$conditions['conditions']['and']['Ipos.posteduserbranchid'] = "$branch_id";
			} 
			$branch = $this->request->query['branch'];   
			$datedform = $this->request->query['datedform'];
			$datedto = $this->request->query['datedto'];
			
			if ($datedform && $datedto){
				$conditions = array(
				'conditions' => array(
				'and' => array(
				array('trim(Ipos.despatchdate) >= ' => $datedform,
				'trim(Ipos.despatchdate) <= ' => $datedto
				)
				)));
			}
			if (($branch !='') && ($branch !='all')){  $conditions['conditions']['or']['Ipos.branch_id'] = "$branch"; }
			
            if ($branch != ''  || ($datedform && $datedto)){ 
				$ipos = $this->Ipos->find('all', $conditions);
				}else{
                if($conditions !=''){
					$ipos = $this->Ipos->find('all', $conditions);
					}else{
                    $ipos = $this->Ipos;
				} 
			}   
			
			$this->paginate = [
            'contain' => ['Companies', 'Branches','Banks','Brokers','Plans']
			]; 
			$ipos = $this->paginate($ipos,['order' => ['Ipos.branch_id' => 'ASC']]);
			$datafds = $ipos->toArray(); 
			// pr($datafds);    
			
			$headerRow = array("Sr.","Company", "Type of Deposit",'Name','Product','Scheme','Amount','Cheque No','Folio/FDR No','Application No','Bank Name');
			$j=1;
            for($i=0; $i< count($datafds); $i++){
                $data[] = [$j,$datafds[$i]->company->company,$datafds[$i]->typeofdeposit,$datafds[$i]->biddername,"IPO",$datafds[$i]->plan->Planname,$datafds[$i]->amount,$datafds[$i]->chequeno,$datafds[$i]->fdrno,$datafds[$i]->applicationno,$datafds[$i]->bank->banks];
				$j++;
			} 
			$this->ExportXls->export($fileName, $headerRow, $data);
		} 
		
		public function ncd()
		{
			$this->loadModel('Ncds'); 
			$this->loadModel('Customers'); 
			$this->loadModel('Banks'); 
			$this->loadModel('Plans'); 
			
			$this->Search->addFilter('applicationno', [
			'attributes' => ['placeholder'=>'Appliation Number','label'=>'Appliation Number :','class'=>'searchfrm',],
			]);
			$this->Search->addFilter('fapplicantname', [
			'attributes'=>['placeholder'=>'Applicant Name','label'=>'Applicant Name :','class'=>'searchfrm',],
			]);
			$this->Search->addFilter('panno', [
			'attributes'=>['placeholder'=>'Pan No','label'=>'Pan Number :','class'=>'searchfrm',],
			]);
			$this->Search->addFilter('datedform', [
			'attributes'=>['placeholder'=>'Date From','label'=>'Date From :','class'=>'searchfrm',],
			]);
			$this->Search->addFilter('datedto', [
			'attributes'=>['placeholder'=>'Date to','label'=>'Date to :','class'=>'searchfrm',],
			]); 
			$this->Search->addFilter('banks', [
			'attributes'=>['placeholder'=>'Bank Name','label'=>'Bank Name :','class'=>'searchfrm',],  
			'options' => $this->Ncds->Banks->find('list')->toArray(),
			]);
			$this->Search->addFilter('customerid', [
			'attributes'=>['placeholder'=>'Customer Account','label'=>'Customer Account :','class'=>'searchfrm',],  
			'options' => $this->Ncds->Customers->find('list')->toArray(),
			]);
			
			if (!empty($this->request->query)) { 
				$applicationno = $this->request->query['applicationno'];
				$fapplicantname = $this->request->query['fapplicantname']; 
				$panno = $this->request->query['panno'];
				$banks = $this->request->query['banks'];
				$customerid = $this->request->query['customerid'];          
				$datedform = $this->request->query['datedform'];
				$datedto = $this->request->query['datedto'];           
				
				if ($applicationno){ $conditions['conditions']['or']['Ncds.applicationno LIKE'] = "%$applicationno%"; }
				if ($fapplicantname){ $conditions['conditions']['or']['Ncds.fapplicantname LIKE'] = "%$fapplicantname%"; }
				if ($panno){ $conditions['conditions']['or']['Ncds.firstapplicantpan'] = "$panno"; }
				if ($banks){ $conditions['conditions']['or']['Ncds.bank_id'] = "$banks"; }
				if ($customerid){ $conditions['conditions']['or']['Ncds.customer_id'] = "$customerid"; }   
				if ($datedform && $datedto){
					$conditions = array(
					'conditions' => array(
					'and' => array(
					array('Ncds.created >= ' => $datedform,
					'Ncds.created <= ' => $datedto
					)
					)));
				}
				
				if ($applicationno !='' || $fapplicantname != '' || $panno != '' || $banks !='' || $customerid != '' || ($datedform && $datedto)){ 
					$ncds = $this->Ncds->find('all', $conditions);
					}else{
					$ncds = $this->Ncds;
				}    
				$this->paginate = [
				'contain' => []
				]; 
				$ncds = $this->paginate($ncds ,['order' => ['Ncds.id' => 'DESC']]);
				$this->set(compact('ncds','panno','applicationno','fapplicantname','banks','customerid','datedform','datedto'));
				$this->set('_serialize', ['ncds']);
				
				}else{ 
				$applicationno = '';
				$fapplicantname = '';          
				$banks = '';
				$customerid = '';          
				$datedform = '';
				$datedto = ''; 
				$panno = '';
				
				$this->paginate = [
                'contain' => []
				];
				$ncds = $this->paginate($this->Ncds,['order' => ['Ncds.id' => 'DESC']]);
				$this->set(compact('ncds','panno','applicationno','fapplicantname','banks','customerid','datedform','datedto'));
				$this->set('_serialize', ['ncds']);
			} 
		}
        
		function ncdreports() {
			
			$this->autoRender = false;
			// $this->layout = false;        
			$fileName = "ncdreports_".date("d-m-y:h:s").".xls";        
			$this->loadModel('Ncds'); 
			$this->loadModel('Customers'); 
			$this->loadModel('Banks'); 
			$this->loadModel('Plans'); 
			
			$applicationno = $this->request->query['applicationno'];
			$fapplicantname = $this->request->query['fapplicantname'];
			$panno = $this->request->query['panno'];
			$banks = $this->request->query['banks'];
			$customerid = $this->request->query['customerid']; 
			$datedform = $this->request->query['datedform'];
			$datedto = $this->request->query['datedto']; 
			if ($applicationno){ $conditions['conditions']['or']['Ncds.applicationno LIKE'] = "%$applicationno%"; }
			if ($fapplicantname){ $conditions['conditions']['or']['Ncds.fapplicantname LIKE'] = "%$fapplicantname%"; }
			if ($panno){ $conditions['conditions']['or']['Ncds.firstapplicantpan'] = "$panno"; }
			if ($banks){ $conditions['conditions']['or']['Ncds.bank_id'] = "$banks"; }
			if ($customerid){ $conditions['conditions']['or']['Ncds.customer_id'] = "$customerid"; }   
			if ($datedform && $datedto){
				$conditions = array(
				'conditions' => array(
				'and' => array(
				array('Ncds.created >= ' => $datedform,
				'Ncds.created <= ' => $datedto
				)
				)));
			}
			
			if ($applicationno !='' || $fapplicantname != '' || $panno != '' || $banks !='' || $customerid != '' || ($datedform && $datedto)){ 
				$ncds = $this->Ncds->find('all', $conditions);
				}else{
				$ncds = $this->Ncds;
			}    
			
			$this->paginate = [
			'contain' => ['Banks','Bankbranches','Searies']
			]; 
			$ncds = $this->paginate($ncds ,['order' => ['Ncds.id' => 'DESC']]);
			$datafds = $ncds->toArray(); 
			
			$headerRow = array("Application No","Applicant Name", "Pan No",
			"Amount","Banks","Bank Branch");
			
			for($i=0; $i< count($datafds); $i++){
				$data[] = [$datafds[$i]->applicationno,$datafds[$i]->firstapplicantname,$datafds[$i]->firstapplicantpan,
				$datafds[$i]->amount,
				$datafds[$i]->bank->banks,$datafds[$i]->bankbranch->branchaddress];
			}            
			$this->ExportXls->export($fileName, $headerRow, $data);
		}
        
        
		public function ipo()
		{
			$this->loadModel('Ipos'); 
			$this->loadModel('Customers'); 
			$this->loadModel('Banks'); 
			$this->loadModel('Plans'); 
			
			$this->Search->addFilter('applicationno', [
			'attributes' => ['placeholder'=>'Appliation Number','label'=>'Appliation Number :','class'=>'searchfrm',],
			]);
			$this->Search->addFilter('fapplicantname', [
			'attributes'=>['placeholder'=>'Applicant Name','label'=>'Applicant Name :','class'=>'searchfrm',],
			]);
			$this->Search->addFilter('panno', [
			'attributes'=>['placeholder'=>'Pan No','label'=>'Pan Number :','class'=>'searchfrm',],
			]);
			$this->Search->addFilter('datedform', [
			'attributes'=>['placeholder'=>'Date From','label'=>'Date From :','class'=>'searchfrm',],
			]);
			$this->Search->addFilter('datedto', [
			'attributes'=>['placeholder'=>'Date to','label'=>'Date to :','class'=>'searchfrm',],
			]); 
			$this->Search->addFilter('banks', [
			'attributes'=>['placeholder'=>'Bank Name','label'=>'Bank Name :','class'=>'searchfrm',],  
			'options' => $this->Ipos->Banks->find('list')->toArray(),
			]);
			$this->Search->addFilter('customerid', [
			'attributes'=>['placeholder'=>'Customer Account','label'=>'Customer Account :','class'=>'searchfrm',],  
			'options' => $this->Ipos->Customers->find('list')->toArray(),
			]);
			
			if (!empty($this->request->query)) {  
				$applicationno = (($this->request->query['applicationno'])? $this->request->query['applicationno'] : '');
				$fapplicantname = $this->request->query['fapplicantname']; 
				$panno = $this->request->query['panno'];
				$banks = $this->request->query['banks'];
				$customerid = $this->request->query['customerid'];          
				$datedform = $this->request->query['datedform'];
				$datedto = $this->request->query['datedto'];           
				
				if ($applicationno){ $conditions['conditions']['or']['Ipos.applicationno LIKE'] = "%$applicationno%"; }
				if ($fapplicantname){ $conditions['conditions']['or']['Ipos.fapplicantname LIKE'] = "%$fapplicantname%"; }
				if ($panno){ $conditions['conditions']['or']['Ipos.bidderpanno'] = "$panno"; }
				if ($banks){ $conditions['conditions']['or']['Ipos.bank_id'] = "$banks"; }
				if ($customerid){ $conditions['conditions']['or']['Ipos.customer_id'] = "$customerid"; }   
				if ($datedform && $datedto){
					$conditions = array(
					'conditions' => array(
					'and' => array(
					array('Ipos.created >= ' => $datedform,
					'Ipos.created <= ' => $datedto
					)
					)));
				}
				
				if ($applicationno !='' || $fapplicantname != '' || $panno != '' || $banks !='' || $customerid != '' || ($datedform && $datedto)){ 
					$ipos = $this->Ipos->find('all', $conditions);
					}else{
					$ipos = $this->Ipos;                
				}    
				$this->paginate = [
				'contain' => []
				]; 
				$ipos = $this->paginate($ipos ,['order' => ['Ipos.id' => 'DESC']]);
				$this->set(compact('ipos','panno','applicationno','fapplicantname','banks','customerid','datedform','datedto'));
				$this->set('_serialize', ['ipos']);
				
				}else{ 
				$applicationno = '';
				$fapplicantname = '';          
				$banks = '';
				$customerid = '';          
				$datedform = '';
				$datedto = ''; 
				$panno = '';
				$this->paginate = [
                'contain' => []
				];
				$ipos = $this->paginate($this->Ipos,['order' => ['Ipos.id' => 'DESC']]);
				$this->set(compact('ipos','panno','applicationno','fapplicantname','banks','customerid','datedform','datedto'));
				$this->set('_serialize', ['ipos']);
			} 
		}
        
		function iporeports() {
			
			$this->autoRender = false;
			// $this->layout = false;        
			$fileName = "iporeports_".date("d-m-y:h:s").".xls";        
			$this->loadModel('Ipos'); 
			$this->loadModel('Customers'); 
			$this->loadModel('Banks'); 
			$this->loadModel('Plans'); 
			
			$applicationno = $this->request->query['applicationno'];
			$fapplicantname = $this->request->query['fapplicantname'];
			$panno = $this->request->query['panno'];
			$banks = $this->request->query['banks'];
			$customerid = $this->request->query['customerid']; 
			$datedform = $this->request->query['datedform'];
			$datedto = $this->request->query['datedto']; 
			if ($applicationno){ $conditions['conditions']['or']['Ipos.applicationno LIKE'] = "%$applicationno%"; }
			if ($fapplicantname){ $conditions['conditions']['or']['Ipos.fapplicantname LIKE'] = "%$fapplicantname%"; }
			if ($panno){ $conditions['conditions']['or']['Ipos.bidderpanno'] = "$panno"; }
			if ($banks){ $conditions['conditions']['or']['Ipos.bank_id'] = "$banks"; }
			if ($customerid){ $conditions['conditions']['or']['Ipos.customer_id'] = "$customerid"; }   
			if ($datedform && $datedto){
				$conditions = array(
				'conditions' => array(
				'and' => array(
				array('Ipos.created >= ' => $datedform,
				'Ipos.created <= ' => $datedto
				)
				)));
			}
			
			if ($applicationno !='' || $fapplicantname != '' || $panno != '' || $banks !='' || $customerid != '' || ($datedform && $datedto)){ 
				$ipos = $this->Ipos->find('all', $conditions);
				}else{
				$ipos = $this->Ipos;
			}    
			
			$this->paginate = [
			'contain' => ['Banks','Bankbranches']
			]; 
			$ipos = $this->paginate($ipos ,['order' => ['Ipos.id' => 'DESC']]);
			$datafds = $ipos->toArray(); 
			
			$headerRow = array("Application No","Applicant Name", "Pan No",
			"Amount","Banks","Bank Branch");
			
			for($i=0; $i< count($datafds); $i++){
				$data[] = [$datafds[$i]->applicationno,$datafds[$i]->biddername,$datafds[$i]->bidderpanno,
				$datafds[$i]->amount,
				$datafds[$i]->bank->banks,$datafds[$i]->bankbranch->branchaddress];
			}           
			$this->ExportXls->export($fileName, $headerRow, $data);
		} 
		
		public function customerdetail()
		{   
			// $this->loadModel('Ipos','Mutualfunds','Ncds','Fds'); 
			// $this->loadModel('Bankbranches', 'Investments','Searies');
			// $this->loadModel('Banks'); 
			$this->loadModel('Customers'); 
			
			$this->Search->addFilter('customername', [
			'attributes'=>['placeholder'=>'Customer Name','label'=>'Customer Name :','class'=>'searchfrm',],
			]);
			$this->Search->addFilter('panno', [
			'attributes'=>['placeholder'=>'Pan No','label'=>'Pan Number :','class'=>'searchfrm',],
			]);
			$this->Search->addFilter('datedform', [
			'attributes'=>['placeholder'=>'Date From','label'=>'Date From :','class'=>'searchfrm',],
			]);
			$this->Search->addFilter('datedto', [
			'attributes'=>['placeholder'=>'Date to','label'=>'Date to :','class'=>'searchfrm',],
			]); 
			
			$this->Search->addFilter('customerid', [
			'attributes'=>['placeholder'=>'Customer Account','label'=>'Customer Account :','class'=>'searchfrm',],  
			'options' => $this->Customers->find('list')->toArray(),
			]);
			
			if (!empty($this->request->query)) {  
				$customername = $this->request->query['customername']; 
				$panno = $this->request->query['panno'];
				
				$customerid = $this->request->query['customerid'];          
				$datedform = $this->request->query['datedform'];
				$datedto = $this->request->query['datedto'];           
				
				if ($customername){ $conditions['conditions']['or']['Customers.firstname LIKE'] = "%$customername%"; }
				if ($panno){ $conditions['conditions']['or']['Customers.panno'] = "$panno"; }
				if ($customerid){ $conditions['conditions']['or']['Customers.id'] = "$customerid"; }   
				if ($datedform && $datedto){
					$conditions = array(
					'conditions' => array(
					'and' => array(
					array('Customers.created >= ' => $datedform,
					'Customers.created <= ' => $datedto
					)
					)));
				}
				
				if ($customername != '' || $panno != '' || $customerid != '' || ($datedform && $datedto)){ 
					$customers = $this->Customers->find('all', $conditions);
					}else{
					$customers = $this->Customers;                
				}    
				$this->paginate = [
				'contain' => ['Companies','Branches','Fds','Mutualfunds','Ncds','Ipos']
				];
				
				
				$customers = $this->paginate($customers ,['order' => ['Customers.id' => 'DESC']]);
				//pr($customers);die; 
				$this->set(compact('customers','panno','customername','customerid','datedform','datedto'));
				$this->set('_serialize', ['customers']);
				
				}else{ 
				
				$customername = '';  
				$customerid = '';          
				$datedform = '';
				$datedto = ''; 
				$panno = '';
				$this->paginate = [
				'contain' => ['Companies','Branches','Fds','Mutualfunds','Ncds','Ipos']
				];
				$customers = $this->paginate($this->Customers ,['order' => ['Customers.id' => 'DESC']]);
				
				$this->set(compact('customers','panno','customername','customerid','datedform','datedto'));
				$this->set('_serialize', ['customers']);
			} 
		}
        
		function customerdetailreports() {
			
			$this->autoRender = false;
			// $this->layout = false;        
			$fileName = "customerRports_".date("d-m-y:h:s").".xls";        
			$this->loadModel('Fds'); 
			$this->loadModel('Ncds');
			$this->loadModel('Mutualfunds');
			$this->loadModel('Ipos');
			$this->loadModel('Customers'); 
			$this->loadModel('Banks');
			$this->loadModel('Bankbranches'); 
			$this->loadModel('Searies'); 
			$this->loadModel('Investments'); 
			
			$this->Search->addFilter('customername', [
			'attributes'=>['placeholder'=>'Customer Name','label'=>'Customer Name :','class'=>'searchfrm',],
			]);
			$this->Search->addFilter('panno', [
			'attributes'=>['placeholder'=>'Pan No','label'=>'Pan Number :','class'=>'searchfrm',],
			]);
			$this->Search->addFilter('datedform', [
			'attributes'=>['placeholder'=>'Date From','label'=>'Date From :','class'=>'searchfrm',],
			]);
			$this->Search->addFilter('datedto', [
			'attributes'=>['placeholder'=>'Date to','label'=>'Date to :','class'=>'searchfrm',],
			]); 
			
			$this->Search->addFilter('customerid', [
			'attributes'=>['placeholder'=>'Customer Account','label'=>'Customer Account :','class'=>'searchfrm',],  
			'options' => $this->Customers->find('list')->toArray(),
			]);
			
			if (!empty($this->request->query)) {  
				$customername = $this->request->query['customername']; 
				$panno = $this->request->query['panno'];
				
				$customerid = $this->request->query['customerid'];          
				$datedform = $this->request->query['datedform'];
				$datedto = $this->request->query['datedto'];           
				
				if ($customername){ $conditions['conditions']['or']['Customers.firstname LIKE'] = "%$customername%"; }
				if ($panno){ $conditions['conditions']['or']['Customers.panno'] = "$panno"; }
				if ($customerid){ $conditions['conditions']['or']['Customers.id'] = "$customerid"; }   
				if ($datedform && $datedto){
					$conditions = array(
					'conditions' => array(
					'and' => array(
					array('Customers.created >= ' => $datedform,
					'Customers.created <= ' => $datedto
					)
					)));
				}
				
				if ($customername != '' || $panno != '' || $customerid != '' || ($datedform && $datedto)){ 
					$customers = $this->Customers->find('all', $conditions);
					}else{
					$customers = $this->Customers;                
				}
			}   
            $this->paginate = [
			'contain' => ['Companies','Branches','Fds','Mutualfunds','Ncds','Ipos']
            ];
            
            
			$customers = $this->paginate($customers ,['order' => ['Customers.id' => 'DESC']]);
			$datafds = $customers->toArray(); 
			//pr($datafds);die;
			$headerRow = array("Account No","Customer Name", "Pan No",
			"Email","Address","City","State","Country","Phone No","Company","Company Branch");
			
			for($i=0; $i< count($datafds); $i++){
				$data[] = [$datafds[$i]->accountno,$datafds[$i]->firstname.' '.$datafds[$i]->lastname,$datafds[$i]->panno,
				$datafds[$i]->email,$datafds[$i]->address,$datafds[$i]->City,$datafds[$i]->State,$datafds[$i]->Country,$datafds[$i]->phoneno,
				$datafds[$i]->company->company,$datafds[$i]->branch->branchname];
				
				/*   $headerRow[$i] = array("Application No","Payment Option", "Amount");
					for($j=0; $j< count($datafds[$i]->ipos); $j++){
					$data[$i] = [$datafds[$j]->ipos->applicationno,$datafds[$j]->ipos->paymentoption,$datafds[$j]->ipos->amount];  
					
				} */
				
				
			}           
			$this->ExportXls->export($fileName, $headerRow, $data);
		}  
		public function companywiseproduct()
		{
			
		} 
		
		public function companywisemf(){
			$this->loadModel('Filenames');
			$this->loadModel('Mtables');
			$this->loadModel('Mutualfunds'); 
			$this->loadModel('Fds'); 
			$this->loadModel('Customers'); 
			$this->loadModel('Banks'); 
			$this->loadModel('Plans'); 
			$this->loadModel('Products');
			$this->loadModel('Companies');
			$all = array('all'=>'All');
			
			$this->Search->addFilter('company', ['attributes'=>['placeholder'=>'Format Name','label'=>'Company Name :','class'=>'searchfrm','value'=>isset($this->request->query['company'])?$this->request->query['company']:''],'options' => $all+$this->Filenames->find('list',['keyField'=>'id','valueField'=>'fname'])->order(['fname' => 'ASC'])->toArray(),
			]);
			
			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('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']:''],
			]);
			
			$conditions = ''; 
			if($this->Auth->user('group_id')=='3'){
				$branch_id = $this->Auth->user('branch_id');     
				$conditions['conditions']['and']['Mtables.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']['Mtables.branch_id'] = "$branch_id";
				$conditions['conditions']['and']['Mtables.modifiedby'] = "$userid";
			}
			$result = '';
			if (!empty($this->request->query) && count($this->request->query) > 1) { 
				$branch = ($this->request->query['branch'])? $this->request->query['branch'] : '';            
				$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));
				$company = ($this->request->query['company'])? $this->request->query['company'] : ''; 
			}
			
			
			$connection = ConnectionManager::get('default');
			if(isset($branch) && isset($datedform) && isset($datedto) && isset($company)){
				
				//print_r($branch); echo '<br>';print_r($company);echo '<br>';print_r($datedform);echo '<br>';print_r($datedto);echo '<br>';
				
				if($branch == 'all' && $company == 'all'){
					//print_r($branch); print_r($company); die();
					//$result['MF'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount FROM mutualfunds WHERE maturity_date >= :fromdate AND maturity_date <= :todate',['fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
					$result['FD'] = $connection->execute('SELECT count(*) as cnumber,sum(REPLACE(principle_investment,",","")) as tamount FROM mtables WHERE maturity_date >= :fromdate AND maturity_date <= :todate',['fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
					
					//print_r($result['FD']);die();
					//$result['NCD'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount FROM ncds WHERE maturity_date >= :fromdate AND maturity_date <= :todate',['fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
					//$result['IPO'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount FROM ipos WHERE maturity_date >= :fromdate AND maturity_date <= :todate',['fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
				}
				elseif($branch == 'all' && $company != 'all'){
					//print_r($branch); print_r($company); die();
					//$result['MF'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount FROM mutualfunds WHERE productcompanyid = :company AND maturity_date >= :fromdate AND maturity_date <= :todate',['company'=>$company, 'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
					$result['FD'] = $connection->execute('SELECT count(*) as cnumber,sum(REPLACE(principle_investment,",","")) as tamount FROM mtables WHERE csvid = :company AND maturity_date >= :fromdate AND maturity_date <= :todate',['company'=>$company, 'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
					//print_r($datedform);print_r($datedto);
					//print_r($result['FD']);die();
					//$result['NCD'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount FROM ncds WHERE productcompanyid = :company AND maturity_date >= :fromdate AND maturity_date <= :todate',['company'=>$company, 'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
					//$result['IPO'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount FROM ipos WHERE productcompanyid = :company AND maturity_date >= :fromdate AND maturity_date <= :todate',['company'=>$company, 'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
				}
				elseif($branch != 'all' && $company == 'all'){
					//$result['MF'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount FROM mutualfunds WHERE branch_id = :branch AND maturity_date >= :fromdate AND maturity_date <= :todate',['branch'=>$branch, 'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
					$result['FD'] = $connection->execute('SELECT count(*) as cnumber,sum(REPLACE(principle_investment,",","")) as tamount FROM mtables WHERE branch_id = :branch AND maturity_date >= :fromdate AND maturity_date <= :todate',['branch'=>$branch, 'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
					//$result['NCD'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount FROM ncds WHERE branch_id = :branch AND maturity_date >= :fromdate AND maturity_date <= :todate',['branch'=>$branch, 'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
					//$result['IPO'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount FROM ipos WHERE branch_id = :branch AND maturity_date >= :fromdate AND maturity_date <= :todate',['branch'=>$branch, 'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
				}
				else{
					//$result['MF'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount FROM mutualfunds WHERE branch_id = :branch AND productcompanyid = :company AND maturity_date >= :fromdate AND maturity_date <= :todate',['branch'=>$branch, 'company'=>$company, 'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
					$result['FD'] = $connection->execute('SELECT count(*) as cnumber,sum(REPLACE(principle_investment,",","")) as tamount FROM mtables WHERE branch_id = :branch AND csvid = :company AND maturity_date >= :fromdate AND maturity_date <= :todate',['branch'=>$branch, 'company'=>$company, 'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
					//$result['NCD'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount FROM ncds WHERE branch_id = :branch AND productcompanyid = :company AND maturity_date >= :fromdate AND maturity_date <= :todate',['branch'=>$branch, 'company'=>$company, 'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
					//$result['IPO'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount FROM ipos WHERE branch_id = :branch AND productcompanyid = :company AND maturity_date >= :fromdate AND maturity_date <= :todate',['branch'=>$branch, 'company'=>$company, 'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
				}
			}
			
			//$resultss = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount FROM fds WHERE created BETWEEN 2017-06-01 00:00:00.000000 AND 2017-07-14 00:00:00.000000 ORDER BY id DESC')->fetchAll('assoc');
			//pr($result);
			$this->set(compact('result','company','branch','datedform','datedto','dated2','dated1'));
            $this->set('_serialize', ['result']);
			//die();
		}
		
		
		
		public function companywisemfexcelreport(){   
			$this->autoRender = false;       
			$fileName = "companywisereportsmf_".date("d-m-y:h:s").".xls";  
			$this->loadModel('Mutualfunds');
			$this->loadModel('Customers'); 
			$this->loadModel('Banks'); 
			$this->loadModel('Plans'); 
			$this->loadModel('Products');
			$this->loadModel('Companies');
			$conditions = '';  
			if($this->Auth->user('group_id')=='3'){           
				$branch_id = $this->Auth->user('branch_id');     
				$conditions['conditions']['and']['Mutualfunds.posteduserbranchid'] = "$branch_id";
			}  
			
			$company = $this->request->query['company'];
			$branch = $this->request->query['branch'];   
			$datedform = $this->request->query['datedform'];
			$datedto = $this->request->query['datedto'];
			
            if ($datedform && $datedto){
				$conditions = array(
				'conditions' => array(
				'and' => array(
				array('trim(Mutualfunds.despatchdate) >= ' => $datedform,
				'trim(Mutualfunds.despatchdate) <= ' => $datedto
				)
				)));
			}
			if (($company !='') && ($company !='all')){ $conditions['conditions']['and']['Mutualfunds.productcompanyid'] = "$company"; }  
			if (($branch !='') && ($branch !='all')){  $conditions['conditions']['or']['Mutualfunds.branch_id'] = "$branch"; }
            
            if ($branch != ''  || ($datedform && $datedto)){ 
				$mutualfunds = $this->Mutualfunds->find('all', $conditions);
				}else{                
				if($conditions !=''){
                    $mutualfunds = $this->Mutualfunds->find('all', $conditions);
					}else{
					$mutualfunds = $this->Mutualfunds; 
				} 
			}   
            
            //, 'Banks'
			$this->paginate = [
			'contain' => ['Companies', 'Branches','Brokers', 'Mffunds']
            ];
			$mutualfunds = $this->paginate($mutualfunds ,['order' => ['Mutualfunds.branch_id' => 'ASC']]);
			$datafds = $mutualfunds->toArray(); 
			//pr($datafds);  
			
			$banks = TableRegistry::get('Banks');
			
			$headerRow = array("Sr.","Company", "Type of Deposit",'Name','Product','Scheme','Amount','Cheque No','Folio No','Application No','Bank Name');
			$j=1;
            for($i=0; $i< count($datafds); $i++){
				if($datafds[$i]->bank_id > 0){
					$banksid = $banks->find()->where(['id' => $datafds[$i]->bank_id]);
					$data1 = $banksid->toArray();
					$banksName = $data1[0]->banks;
					}else{
					$banksName = '';
				}   
                $data[] = [$j,$datafds[$i]->company->company,$datafds[$i]->typeofdeposit,$datafds[$i]->firstapplicantname,"Mutual Fund",$datafds[$i]->mffund->fundname,$datafds[$i]->amount,$datafds[$i]->chequeno,$datafds[$i]->customerFolioNo,$datafds[$i]->applicationno,$banksName];
				$j++;
			} 
			$this->ExportXls->export($fileName, $headerRow, $data);
		}  
		
		public function csltdmoblztion(){
			$this->loadModel('Mutualfunds'); 
			$this->loadModel('Fds'); 
			$this->loadModel('Mtables'); 
			$this->loadModel('Filenames'); 
			$this->loadModel('Customers'); 
			$this->loadModel('Banks'); 
			$this->loadModel('Plans'); 
			$this->loadModel('Products');
			$this->loadModel('Companies');
			$all = array('all'=>'All');
			/*  $this->Search->addFilter('company', [
				'attributes'=>['placeholder'=>'Company Name','label'=>'Company Name :','class'=>'searchfrm','value' => isset($this->request->query['company'])?$this->request->query['company']:''],  
				'options' => $all+$this->Companies->find('list')-> where(['associatewith' => 'product'])->order(['company' => 'ASC'])->toArray(),           
			]); */ 
			
			$this->Search->addFilter('company', ['attributes'=>['placeholder'=>'Company Name','label'=>'Company Name :','class'=>'searchfrm','value'=>isset($this->request->query['company'])?$this->request->query['company']:''],'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(['institution_name' => 'ASC'])->group(['Mtables.institution_name'])->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']:''],
            ]);	
			
			$conditions = array();
			
			$result = array();
			if (!empty($this->request->query) && count($this->request->query) > 1) { 
				
				$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));
				$company = ($this->request->query['company'])? $this->request->query['company'] : ''; 
			}

            $extraCondition = "";
            if(!empty($this->request->query['institution_name']) && $this->request->query['institution_name']!= 'all'){
                $extraCondition .= " AND institution_name LIKE '%".$this->request->query['institution_name']."%'  ";
            }

			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));
			//	maturity_date >= :fromdate AND maturity_date <= :todate '

                $extraCondition .= " AND application_date >= $invdatedform AND application_date <= $invdatedto";
            }

			$connection = ConnectionManager::get('default');
			if(isset($datedform) && isset($datedto) && isset($company)){
				if($company == 'all'){
					//$result['andheri']['MF'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount FROM mutualfunds WHERE branch_id = :branch and despatchdate >= :fromdate AND despatchdate <= :todate',['branch'=> 2,'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
					//	$result['bandra']['MF'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount FROM mutualfunds WHERE branch_id = :branch and despatchdate >= :fromdate AND despatchdate <= :todate',['branch'=> 6,'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
					//	$result['fort']['MF'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount FROM mutualfunds WHERE branch_id = :branch and despatchdate >= :fromdate AND despatchdate <= :todate',['branch'=> 4,'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
					
					/*$result['andheri']['FD'] = $connection->execute('SELECT count(*) as cnumber,sum(REPLACE(principle_investment,",","")) as tamount FROM mtables WHERE branch_id = :branch and maturity_date >= :fromdate AND maturity_date <= :todate',['branch'=> 2,'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
					$result['bandra']['FD'] = $connection->execute('SELECT count(*) as cnumber,sum(REPLACE(principle_investment,",","")) as tamount FROM mtables WHERE branch_id = :branch and maturity_date >= :fromdate AND maturity_date <= :todate',['branch'=> 6,'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
					$result['fort']['FD'] = $connection->execute('SELECT count(*) as cnumber,sum(REPLACE(principle_investment,",","")) as tamount FROM mtables WHERE branch_id = :branch and maturity_date >= :fromdate AND maturity_date <= :todate',['branch'=> 4,'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');*/

                    $result['andheri']['FD'] = $connection->execute('SELECT csvid,count(*) as cnumber,sum(REPLACE(investment_amount,",","")) as tamount FROM mtables WHERE branch_id = :branch and maturity_date >= :fromdate AND maturity_date <= :todate'.$extraCondition.' GROUP BY csvid',['branch'=> 2,'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
                    $result['bandra']['FD'] = $connection->execute('SELECT csvid,count(*) as cnumber,sum(REPLACE(investment_amount,",","")) as tamount FROM mtables WHERE branch_id = :branch and maturity_date >= :fromdate AND maturity_date <= :todate'.$extraCondition.' GROUP BY csvid',['branch'=> 6,'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
                    $result['fort']['FD'] = $connection->execute('SELECT csvid,count(*) as cnumber,sum(REPLACE(investment_amount,",","")) as tamount FROM mtables WHERE branch_id = :branch and maturity_date >= :fromdate AND maturity_date <= :todate'.$extraCondition.' GROUP BY csvid',['branch'=> 4,'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');

					//$result['andheri']['NCD'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount FROM ncds WHERE branch_id = :branch and despatchdate >= :fromdate AND despatchdate <= :todate',['branch'=> 2,'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
					//$result['bandra']['NCD'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount FROM ncds WHERE branch_id = :branch and despatchdate >= :fromdate AND despatchdate <= :todate',['branch'=> 6,'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
					//$result['fort']['NCD'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount FROM ncds WHERE branch_id = :branch and despatchdate >= :fromdate AND despatchdate <= :todate',['branch'=> 4,'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
					
					
					//$result['andheri']['IPO'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount FROM ipos WHERE branch_id = :branch and despatchdate >= :fromdate AND despatchdate <= :todate',['branch'=> 2,'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
					//$result['bandra']['IPO']= $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount FROM ipos WHERE branch_id = :branch and despatchdate >= :fromdate AND despatchdate <= :todate',['branch'=> 6,'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
					//$result['fort']['IPO'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount FROM ipos WHERE branch_id = :branch and despatchdate >= :fromdate AND despatchdate <= :todate',['branch'=> 4,'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
				}
				else{
					//$result['andheri']['MF'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount FROM mutualfunds WHERE productcompanyid = :company and branch_id = :branch and despatchdate >= :fromdate AND despatchdate <= :todate',['company'=>$company, 'branch'=> 2,'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
					//$result['bandra']['MF'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount FROM mutualfunds WHERE productcompanyid = :company and branch_id = :branch and despatchdate >= :fromdate AND despatchdate <= :todate',['company'=>$company, 'branch'=> 6,'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
					//$result['fort']['MF'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount FROM mutualfunds WHERE productcompanyid = :company and branch_id = :branch and despatchdate >= :fromdate AND despatchdate <= :todate',['company'=>$company, 'branch'=> 4,'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
					/*
					$result['andheri']['FD'] = $connection->execute('SELECT count(*) as cnumber,sum(REPLACE(principle_investment,",","")) as tamount FROM mtables WHERE csvid = :company and branch_id = :branch and maturity_date >= :fromdate AND maturity_date <= :todate',['company'=>$company, 'branch'=> 2,'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
					$result['bandra']['FD'] = $connection->execute('SELECT count(*) as cnumber,sum(REPLACE(principle_investment,",","")) as tamount FROM mtables WHERE csvid = :company and branch_id = :branch and maturity_date >= :fromdate AND maturity_date <= :todate',['company'=>$company, 'branch'=> 6,'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
					$result['fort']['FD'] = $connection->execute('SELECT count(*) as cnumber,sum(REPLACE(principle_investment,",","")) as tamount FROM mtables WHERE csvid = :company and branch_id = :branch and maturity_date >= :fromdate AND maturity_date <= :todate',['company'=>$company, 'branch'=> 4,'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');*/

                    $result['andheri']['FD'] = $connection->execute('SELECT csvid,count(*) as cnumber,sum(REPLACE(investment_amount,",","")) as tamount FROM mtables WHERE csvid = :company and branch_id = :branch and maturity_date >= :fromdate AND maturity_date <= :todate'.$extraCondition.' GROUP BY csvid',['company'=>$company, 'branch'=> 2,'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
                    $result['bandra']['FD'] = $connection->execute('SELECT csvid,count(*) as cnumber,sum(REPLACE(investment_amount,",","")) as tamount FROM mtables WHERE csvid = :company and branch_id = :branch and maturity_date >= :fromdate AND maturity_date <= :todate'.$extraCondition.' GROUP BY csvid',['company'=>$company, 'branch'=> 6,'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
                    $result['fort']['FD'] = $connection->execute('SELECT csvid,count(*) as cnumber,sum(REPLACE(investment_amount,",","")) as tamount FROM mtables WHERE csvid = :company and branch_id = :branch and maturity_date >= :fromdate AND maturity_date <= :todate'.$extraCondition.' GROUP BY csvid',['company'=>$company, 'branch'=> 4,'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
					
					//$result['andheri']['NCD'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount FROM ncds WHERE productcompanyid = :company and branch_id = :branch and despatchdate >= :fromdate AND despatchdate <= :todate',['company'=>$company, 'branch'=> 2,'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
					//$result['bandra']['NCD'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount FROM ncds WHERE productcompanyid = :company and branch_id = :branch and despatchdate >= :fromdate AND despatchdate <= :todate',['company'=>$company, 'branch'=> 6,'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
					//$result['fort']['NCD'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount FROM ncds WHERE productcompanyid = :company and branch_id = :branch and despatchdate >= :fromdate AND despatchdate <= :todate',['company'=>$company, 'branch'=> 4,'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
					
					
					//$result['andheri']['IPO'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount FROM ipos WHERE productcompanyid = :company and branch_id = :branch and despatchdate >= :fromdate AND despatchdate <= :todate',['company'=>$company, 'branch'=> 2,'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
					//$result['bandra']['IPO'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount FROM ipos WHERE productcompanyid = :company and branch_id = :branch and despatchdate >= :fromdate AND despatchdate <= :todate',['company'=>$company, 'branch'=> 6,'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
					//$result['fort']['IPO'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount FROM ipos WHERE productcompanyid = :company and branch_id = :branch and despatchdate >= :fromdate AND despatchdate <= :todate',['company'=>$company, 'branch'=> 4,'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
				}
				
			}


            $filenames = $this->Filenames->find('list',['keyField'=>'id','valueField'=>'fname'])->order(['fname' => 'ASC'])->toArray();
			
			$this->set(compact('result','company','datedform','datedto','dated2','dated1','filenames'));
            $this->set('_serialize', ['result']);
		}
		
		//swichall Record need to change the type (group by user)
		public function switchrecdsmry(){
			$this->loadModel('Mutualfunds'); 
			$this->loadModel('Fds'); 
			$this->loadModel('Customers'); 
			$this->loadModel('Banks'); 
			$this->loadModel('Plans'); 
			$this->loadModel('Products');
			$this->loadModel('Companies');
			
			$conditions = ''; 
			$connection = ConnectionManager::get('default');
			
			if($this->Auth->user('group_id')=='3'){           
				$abranch =$this->Auth->user('branch_id'); $bbranch =$this->Auth->user('branch_id'); $fbranch =$this->Auth->user('branch_id'); 
			} 
			if($this->Auth->user('group_id')=='2'){ 
				$abranch =2;$bbranch =6;$fbranch =4;
			}
			
			$swichall = 1;
			
			$result['andheri'] = $result['bandra'] = $result['fort']='';
			
			if($this->Auth->user('group_id')=='2'){ 
				$result['andheri'] = $connection->execute('SELECT count(*) as cnumber,postedby FROM mutualfunds WHERE branch_id = :branch AND swichall = :swichall GROUP BY postedby', ['branch'=>$abranch, 'swichall'=>$swichall])->fetchAll('assoc');
				$result['bandra'] = $connection->execute('SELECT count(*) as cnumber,postedby FROM mutualfunds WHERE branch_id = :branch AND swichall = :swichall GROUP BY postedby', ['branch'=>$bbranch, 'swichall'=>$swichall])->fetchAll('assoc');
				$result['fort'] = $connection->execute('SELECT count(*) as cnumber,postedby FROM mutualfunds WHERE branch_id = :branch AND swichall = :swichall GROUP BY postedby', ['branch'=>$fbranch, 'swichall'=>$swichall])->fetchAll('assoc');
			}
			
			if($this->Auth->user('group_id')=='3'){ 
				if($abranch == 2){
					$result['andheri'] = $connection->execute('SELECT count(*) as cnumber,postedby FROM mutualfunds WHERE branch_id = :branch AND swichall = :swichall GROUP BY postedby', ['branch'=>$abranch, 'swichall'=>$swichall])->fetchAll('assoc');
				}
				if($bbranch == 6){
					$result['bandra'] = $connection->execute('SELECT count(*) as cnumber,postedby FROM mutualfunds WHERE branch_id = :branch AND swichall = :swichall GROUP BY postedby', ['branch'=>$bbranch, 'swichall'=>$swichall])->fetchAll('assoc');
				}
				if($fbranch == 4){
					$result['fort'] = $connection->execute('SELECT count(*) as cnumber,postedby FROM mutualfunds WHERE branch_id = :branch AND swichall = :swichall GROUP BY postedby', ['branch'=>$fbranch, 'swichall'=>$swichall])->fetchAll('assoc');
				}
			}
			$this->set(compact('result'));
			$this->set('_serialize', ['result']);
			//die();
		}
		
		//Schemewise MF
		public function schemewisemf(){
			$this->loadModel('Mutualfunds'); 
			$this->loadModel('Fds'); 
			$this->loadModel('Customers'); 
			$this->loadModel('Banks'); 
			$this->loadModel('Plans'); 
			$this->loadModel('Products');
			$this->loadModel('Companies');
			$all = array('all'=>'All');
			$this->Search->addFilter('company', [
            'attributes'=>['placeholder'=>'Company Name','label'=>'Company Name :','class'=>'searchfrm','value' => isset($this->request->query['company'])?$this->request->query['company']:''],  
            'options' => $this->Companies->find('list')-> where(['associatewith' => 'product'])->where(['productid LIKE' => '%1%'])->order(['company' => 'ASC'])->toArray(),           
			]); 
			$this->Search->addFilter('scheme', [
			'attributes'=>['placeholder'=>'Scheme Name','label'=>'Scheme Name :','class'=>'searchfrm','value' => isset($this->request->query['scheme'])?$this->request->query['scheme']:''],  
			'options' => $all+$this->Mutualfunds->Mffunds->find('list')->toArray(),
			]);
			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('datedform', [
			'attributes'=>['placeholder'=>'Despatch Start Date','label'=>'Despatch Start Date :','class'=>'searchfrm','value' => isset($this->request->query['datedform'])?$this->request->query['datedform']:''],
			]);
			$this->Search->addFilter('datedto', [
			'attributes'=>['placeholder'=>'Despatch End Date','label'=>'Despatch End Date :','class'=>'searchfrm','value' => isset($this->request->query['datedto'])?$this->request->query['datedto']:''],
			]);
			
			$conditions = ''; 
			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";
			}
			$result = '';
			if (!empty($this->request->query) && count($this->request->query) > 1) {  
				$branch = ($this->request->query['branch'])? $this->request->query['branch'] : '';            
				$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));
				$company = ($this->request->query['company'])? $this->request->query['company'] : '';
				$scheme = $this->request->query['scheme'];			
			}    
			$connection = ConnectionManager::get('default');
			if(isset($branch) && isset($datedform) && isset($datedto) && isset($company)){
				if($branch == 'all' && $scheme == 'all'){
					$result['MF'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount, mffund_id FROM mutualfunds WHERE  productcompanyid = :company AND despatchdate >= :fromdate AND despatchdate <= :todate GROUP BY mffund_id',['company'=>$company,'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
					}elseif($branch != 'all' && $scheme == 'all'){
					$result['MF'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount, mffund_id FROM mutualfunds WHERE  branch_id = :branch AND productcompanyid = :company AND despatchdate >= :fromdate AND despatchdate <= :todate GROUP BY mffund_id',['company'=>$company, 'branch'=>$branch, 'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
					}elseif($branch == 'all' && $scheme != 'all'){
					$result['MF'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount, mffund_id FROM mutualfunds WHERE mffund_id = :mffund AND productcompanyid = :company AND despatchdate >= :fromdate AND despatchdate <= :todate GROUP BY mffund_id',['mffund'=>$scheme, 'company'=>$company, 'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
					}else{
					$result['MF'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount, mffund_id FROM mutualfunds WHERE mffund_id = :mffund AND branch_id = :branch AND productcompanyid = :company AND despatchdate >= :fromdate AND despatchdate <= :todate GROUP BY mffund_id',['mffund'=>$scheme, 'company'=>$company, 'branch'=>$branch, 'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');	
				}
			}
			$this->set(compact('result','company','branch','datedform','datedto','dated2','dated1','scheme'));
            $this->set('_serialize', ['result']);
		}
		
		public function schemewiseuserlist(){
			$this->loadModel('Mutualfunds'); 
			$this->loadModel('Customers'); 
			$this->loadModel('Banks'); 
			$all = array('all'=>'All'); 
			
			$this->Search->addFilter('company', [
			'attributes'=>['placeholder'=>'Company','label'=>'MutualFund :','class'=>'searchfrm', 'value' => isset($this->request->query['company'])?$this->request->query['company']:''],  
			'options' => $all+$this->Mutualfunds->Companies->find('list')-> where(['associatewith' => 'product'])->andWhere(['Companies.productid ='=>'1'] )->order(['company' => 'ASC'])->toArray(),
			]);
			
			$this->Search->addFilter('scheme', [
			'attributes'=>['placeholder'=>'Scheme Name','label'=>'Scheme Name :','class'=>'searchfrm','value' => isset($this->request->query['scheme'])?$this->request->query['scheme']:'',],  
			'options' => $all+$this->Mutualfunds->Mffunds->find('list')->order(['fundname' => 'ASC'])->group(['fundname'])->toArray(),
			]);
			if($this->Auth->user('group_id')=='2'){ 
				$branch = $this->Mutualfunds->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->Mutualfunds->Branches->find('list')->toArray(),
				]);
			}  
			
			$this->Search->addFilter('datedform', [
			'attributes'=>['placeholder'=>'Despatch From Date','label'=>'Despatch From Date :','class'=>'searchfrm','value' => isset($this->request->query['datedform'])?$this->request->query['datedform']:''],
			]);
			$this->Search->addFilter('datedto', [
			'attributes'=>['placeholder'=>'Despatch To Date','label'=>'Despatch To Date :','class'=>'searchfrm','value' => isset($this->request->query['datedto'])?$this->request->query['datedto']:''],
			]);
			
			$conditions = '';  
			if($this->Auth->user('group_id')=='3'){        
				$branch_id = $this->Auth->user('branch_id');     
				$conditions['conditions']['and']['Mutualfunds.posteduserbranchid'] = "$branch_id";
			}
			if (!empty($this->request->query) && count($this->request->query) > 1) { 
				
				$branch = $this->request->query['branch'];
				$company = $this->request->query['company']; 
				$scheme = $this->request->query['scheme'];
				
				$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){             
					$conditions = array(
					'conditions' => array(
					'and' => array(
					array('Mutualfunds.despatchdate >= ' => $datedform,
					'Mutualfunds.despatchdate <= ' => $datedto
					)
					)));
				}
				
				if ($branch!='' && $branch!='all'){ $conditions['conditions']['and']['Mutualfunds.branch_id'] = "$branch"; }
				if ($company!='' && $company!='all'){ $conditions['conditions']['and']['Mutualfunds.productcompanyid'] = "$company"; }
				if ($scheme!='' && $scheme!='all') { $conditions['conditions']['and']['Mutualfunds.mffund_id'] = "$scheme"; }
				
				if ( ($company != '' && $company != 'all') || ($branch != '' &&  $branch != 'all') || ($scheme != '' && $scheme != 'all' ) || ($datedform && $datedto) ){ 
					$mf = $this->Mutualfunds->find('all', $conditions);
					$cnt = $mf->count();              
					if($mf){
						$total_amount = 0;                   
						foreach ($mf as $md){ 
							$total_amount += $md->amount;
						}
					} 
				}
				elseif($company == 'all' || $branch == 'all' || $scheme == 'all' )
				{
					$mf = $this->Mutualfunds;
					$mf1=$this->Mutualfunds->find('all',array('contain' => ['Companies', 'Branches', 'Mffunds','Brokers']));
					$cnt = $mf1->count();              
					if($mf1){
						$total_amount = 0;                   
						foreach ($mf1 as $md){ 
							$total_amount += $md->amount;
						}
					} 
				}
				else{  
					if($conditions != ''){
						
						$mf = $this->Mutualfunds->find('all', $conditions);
						$cnt = $mf->count();              
                        if($mutualfunds){
                            $total_amount = 0;                   
							foreach ($mf as $md){ 
                                $total_amount += $md->amount;
							}
						} 
					} 
					
					else{
						
						$mf = $this->Mutualfunds;
						$mf1=$this->Mutualfunds->find('all',array('contain' => ['Companies', 'Branches', 'Mffunds','Brokers']));
						$cnt = $mf1->count();              
                        if($mf1){
							$total_amount = 0;                   
							foreach ($mf1 as $md){ 
								$total_amount += $md->amount;
							}
						} 
					}  
				}    
				//'Banks'
				$this->paginate = [
				'contain' => ['Companies', 'Branches','Brokers','Mffunds']
				]; 
				// $mfs = $mf;
				
				$mf = $this->paginate($mf ,['order' => ['Mutualfunds.id' => 'DESC']]);
				$this->set(compact('mf','company','branch','scheme','datedform','datedto','total_amount','cnt','dated2','dated1'));
				$this->set('_serialize', ['mf']);
				
			}
		}
		
		//companywise MF
		public function companywisemfrcd(){
			$this->loadModel('Mutualfunds'); 
			$this->loadModel('Fds'); 
			$this->loadModel('Customers'); 
			$this->loadModel('Banks'); 
			$this->loadModel('Plans'); 
			$this->loadModel('Products');
			$this->loadModel('Companies');
			$all = array('all'=>'All');
			$this->Search->addFilter('company', [
            'attributes'=>['placeholder'=>'Company Name','label'=>'Company Name :','class'=>'searchfrm','value' => isset($this->request->query['company'])?$this->request->query['company']:''],  
            'options' =>  $all+$this->Companies->find('list')-> where(['associatewith' => 'product'])->where(['productid LIKE' => '%1%'])->order(['company' => 'ASC'])->toArray(),           
			]); 
			
			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('datedform', [
			'attributes'=>['placeholder'=>'Despatch Start Date','label'=>'Despatch Start Date :','class'=>'searchfrm','value' => isset($this->request->query['datedform'])?$this->request->query['datedform']:''],
			]);
			$this->Search->addFilter('datedto', [
			'attributes'=>['placeholder'=>'Despatch End Date','label'=>'Despatch End Date :','class'=>'searchfrm','value' => isset($this->request->query['datedto'])?$this->request->query['datedto']:''],
			]);
			
			$conditions = ''; 
			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";
			}
			$result = '';
			if (!empty($this->request->query) && count($this->request->query) > 1) {  
				$branch = ($this->request->query['branch'])? $this->request->query['branch'] : '';            
				$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));
				$company = ($this->request->query['company'])? $this->request->query['company'] : '';
			}    
			$connection = ConnectionManager::get('default');
			if(isset($branch) && isset($datedform) && isset($datedto) && isset($company)){
				if($branch == 'all' && $company == 'all'){	
					$result['MF'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount, mffund_id, productcompanyid FROM mutualfunds WHERE despatchdate >= :fromdate AND despatchdate <= :todate GROUP BY productcompanyid ORDER BY tamount DESC',['fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
					
					}elseif($branch != 'all' && $company == 'all'){
					$result['MF'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount, mffund_id, productcompanyid FROM mutualfunds WHERE  branch_id = :branch AND despatchdate >= :fromdate AND despatchdate <= :todate GROUP BY productcompanyid ORDER BY tamount DESC',['branch'=>$branch, 'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
					
					}elseif($branch == 'all' && $company != 'all'){
					$result['MF'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount, mffund_id, productcompanyid FROM mutualfunds WHERE productcompanyid = :company AND despatchdate >= :fromdate AND despatchdate <= :todate GROUP BY productcompanyid ORDER BY tamount DESC',['company'=>$company, 'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
					}else{
					$result['MF'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount, mffund_id, productcompanyid FROM mutualfunds WHERE branch_id = :branch AND productcompanyid = :company AND despatchdate >= :fromdate AND despatchdate <= :todate GROUP BY productcompanyid ORDER BY tamount DESC',['company'=>$company, 'branch'=>$branch, 'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');	
				}
			}
			$this->set(compact('result','company','branch','datedform','datedto','dated2','dated1'));
            $this->set('_serialize', ['result']);
			//die();
		}
		
		//Our companywise MF
		public function ourcompanywisemfrcd(){
			$this->loadModel('Mutualfunds'); 
			$this->loadModel('Mtable'); 
			$this->loadModel('Filenames'); 
			$this->loadModel('Fds'); 
			$this->loadModel('Customers'); 
			$this->loadModel('Banks'); 
			$this->loadModel('Plans'); 
			$this->loadModel('Products');
			$this->loadModel('Companies');
			$all = array('all'=>'All');
			
			/* $this->Search->addFilter('company', [
            'attributes'=>['placeholder'=>'Our Company Name','label'=>'Our Company Name :','class'=>'searchfrm','value' => isset($this->request->query['company'])?$this->request->query['company']:''],  
            'options' =>  $all+$this->Companies->find('list')-> where(['associatewith' => 'user'])->order(['company' => 'ASC'])->toArray(),           
			]);  */
			
			$this->Search->addFilter('company', ['attributes'=>['placeholder'=>'Company Name','label'=>'Company Name :','class'=>'searchfrm','value'=>isset($this->request->query['company'])?$this->request->query['company']:''],'options' => $all+$this->Filenames->find('list',['keyField'=>'id','valueField'=>'fname'])->order(['fname' => 'ASC'])->toArray(),
			]);
			
			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('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']:''],
			]);
			
			$conditions = ''; 
			if($this->Auth->user('group_id')=='3'){             
				$branch_id = $this->Auth->user('branch_id');     
				$conditions['conditions']['and']['Mtable.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']['Mtable.branch_id'] = "$branch_id";
				$conditions['conditions']['and']['Mtable.modifiedby'] = "$userid";
			}
			$result = '';
			if (!empty($this->request->query) && count($this->request->query) > 1) {  
				$branch = ($this->request->query['branch'])? $this->request->query['branch'] : '';            
				$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));
				$company = ($this->request->query['company'])? $this->request->query['company'] : '';
			}    
			$connection = ConnectionManager::get('default');
			if(isset($branch) && isset($datedform) && isset($datedto) && isset($company)){
				if($branch == 'all' && $company == 'all'){
					
					//$result['MF'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount, company_id FROM mutualfunds WHERE despatchdate >= :fromdate AND despatchdate <= :todate',['fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
					
					$result['FD'] = $connection->execute('SELECT count(*) as cnumber,sum(REPLACE(principle_investment,",","")) as tamount, csvid FROM mtables WHERE maturity_date >= :fromdate AND maturity_date <= :todate',['fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
					
					//$result['NCD'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount, company_id FROM ncds WHERE despatchdate >= :fromdate AND despatchdate <= :todate',['fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
					
					//$result['IPO'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount, company_id FROM ipos WHERE despatchdate >= :fromdate AND despatchdate <= :todate',['fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
					
					
					}elseif($branch != 'all' && $company == 'all'){
					
					//$result['MF'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount, company_id FROM mutualfunds WHERE  branch_id = :branch AND despatchdate >= :fromdate AND despatchdate <= :todate',['branch'=>$branch, 'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
					
					$result['FD'] = $connection->execute('SELECT count(*) as cnumber,sum(REPLACE(principle_investment,",","")) as tamount, csvid FROM mtables WHERE  branch_id = :branch AND maturity_date >= :fromdate AND maturity_date <= :todate',['branch'=>$branch, 'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
					
					//$result['NCD'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount, company_id FROM ncds WHERE  branch_id = :branch AND despatchdate >= :fromdate AND despatchdate <= :todate',['branch'=>$branch, 'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
					
					//$result['IPO'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount, company_id FROM ipos WHERE  branch_id = :branch AND despatchdate >= :fromdate AND despatchdate <= :todate',['branch'=>$branch, 'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
					
					}elseif($branch == 'all' && $company != 'all'){
					
					//$result['MF'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount, company_id FROM mutualfunds WHERE company_id = :company AND despatchdate >= :fromdate AND despatchdate <= :todate',['company'=>$company, 'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
					
					$result['FD'] = $connection->execute('SELECT count(*) as cnumber,sum(REPLACE(principle_investment,",","")) as tamount, csvid FROM mtables WHERE csvid = :company AND maturity_date >= :fromdate AND maturity_date <= :todate',['company'=>$company, 'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
					
					//$result['NCD'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount, company_id FROM ncds WHERE company_id = :company AND despatchdate >= :fromdate AND despatchdate <= :todate',['company'=>$company, 'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
					
					//$result['IPO'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount, company_id FROM ipos WHERE company_id = :company AND despatchdate >= :fromdate AND despatchdate <= :todate',['company'=>$company, 'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
					
					}else{
					
					//$result['MF'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount, company_id FROM mutualfunds WHERE branch_id = :branch AND company_id = :company AND despatchdate >= :fromdate AND despatchdate <= :todate',['company'=>$company, 'branch'=>$branch, 'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
					
					$result['FD'] = $connection->execute('SELECT count(*) as cnumber,sum(REPLACE(principle_investment,",","")) as tamount, csvid FROM mtables WHERE branch_id = :branch AND csvid = :company AND maturity_date >= :fromdate AND maturity_date <= :todate',['company'=>$company, 'branch'=>$branch, 'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
					
					//$result['NCD'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount, company_id FROM ncds WHERE branch_id = :branch AND company_id = :company AND despatchdate >= :fromdate AND despatchdate <= :todate',['company'=>$company, 'branch'=>$branch, 'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
					
					//$result['IPO'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount, company_id FROM ipos WHERE branch_id = :branch AND company_id = :company AND despatchdate >= :fromdate AND despatchdate <= :todate',['company'=>$company, 'branch'=>$branch, 'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
				}
			}
			$this->set(compact('result','company','branch','datedform','datedto','dated2','dated1'));
            $this->set('_serialize', ['result']);
			//die();
		}
		
		public function companywiseuserlist(){
			$this->loadModel('Mutualfunds'); 
			$this->loadModel('Customers'); 
			$this->loadModel('Banks'); 
			$all = array('all'=>'All'); 
			
			$this->Search->addFilter('company', [
			'attributes'=>['placeholder'=>'Company','label'=>'MutualFund :','class'=>'searchfrm', 'value' => isset($this->request->query['company'])?$this->request->query['company']:''],  
			'options' => $all+$this->Mutualfunds->Companies->find('list')-> where(['associatewith' => 'product'])->andWhere(['Companies.productid ='=>'1'] )->order(['company' => 'ASC'])->toArray(),
			]);
			
			if($this->Auth->user('group_id')=='2'){ 
				$branch = $this->Mutualfunds->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->Mutualfunds->Branches->find('list')->toArray(),
				]);
			}  
			$this->Search->addFilter('datedform', [
			'attributes'=>['placeholder'=>'Despatch From Date','label'=>'Despatch From Date :','class'=>'searchfrm','value' => isset($this->request->query['datedform'])?$this->request->query['datedform']:''],
			]);
			$this->Search->addFilter('datedto', [
			'attributes'=>['placeholder'=>'Despatch To Date','label'=>'Despatch To Date :','class'=>'searchfrm','value' => isset($this->request->query['datedto'])?$this->request->query['datedto']:''],
			]);
			$conditions = '';  
			if($this->Auth->user('group_id')=='3'){        
				$branch_id = $this->Auth->user('branch_id');     
				$conditions['conditions']['and']['Mutualfunds.posteduserbranchid'] = "$branch_id";
			}
			if (!empty($this->request->query) && count($this->request->query) > 1) { 
				$branch = $this->request->query['branch'];
				$company = $this->request->query['company'];       
				$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){             
					$conditions = array(
					'conditions' => array(
					'and' => array(
					array('Mutualfunds.despatchdate >= ' => $datedform,
					'Mutualfunds.despatchdate <= ' => $datedto
					)
					)));
				}
				
				if ($branch!='' && $branch!='all'){ $conditions['conditions']['and']['Mutualfunds.branch_id'] = "$branch"; }
				if ($company!='' && $company!='all'){ $conditions['conditions']['and']['Mutualfunds.productcompanyid'] = "$company"; }
				//if ($scheme!='' && $scheme!='all') { $conditions['conditions']['and']['Mutualfunds.mffund_id'] = "$scheme"; }
				
				if ( ($company != '' && $company != 'all') || ($branch != '' &&  $branch != 'all') || ($datedform && $datedto) ){ 
					$mf = $this->Mutualfunds->find('all', $conditions);
					$cnt = $mf->count();              
					if($mf){
						$total_amount = 0;                   
						foreach ($mf as $md){ 
							$total_amount += $md->amount;
						}
					} 
				}
				elseif($company == 'all' || $branch == 'all')
				{
					$mf = $this->Mutualfunds;
					$mf1=$this->Mutualfunds->find('all',array('contain' => ['Companies', 'Branches', 'Mffunds','Brokers']));
					$cnt = $mf1->count();              
					if($mf1){
						$total_amount = 0;                   
						foreach ($mf1 as $md){ 
							$total_amount += $md->amount;
						}
					} 
				}
				else{  
					if($conditions != ''){
						$mf = $this->Mutualfunds->find('all', $conditions);
						$cnt = $mf->count();              
                        if($mutualfunds){
                            $total_amount = 0;                   
							foreach ($mf as $md){ 
                                $total_amount += $md->amount;
							}
						} 
					} 
					else{
						$mf = $this->Mutualfunds;
						$mf1=$this->Mutualfunds->find('all',array('contain' => ['Companies', 'Branches', 'Mffunds','Brokers']));
						$cnt = $mf1->count();              
                        if($mf1){
							$total_amount = 0;                   
							foreach ($mf1 as $md){ 
								$total_amount += $md->amount;
							}
						} 
					}  
				}    
				//'Banks'
				$this->paginate = [
				'contain' => ['Companies', 'Branches','Brokers','Mffunds']
				]; 
				// $mfs = $mf;
				$mf = $this->paginate($mf ,['order' => ['Mutualfunds.id' => 'DESC']]);
				$this->set(compact('mf','company','branch','datedform','datedto','total_amount','cnt','dated2','dated1'));
				$this->set('_serialize', ['mf']);
			}
		}
		
		public function tdespatchlst(){
			// Page that listing the todays despatch list report
		}
		public function drangews(){
			// Page that listing the Date Range Wise report
		}
		
		//Client Portfolio Report
		public function userreport(){
			$this->loadModel('Fds'); 
			$this->loadModel('Ncds'); 
			$this->loadModel('Ipos'); 
			$this->loadModel('Mutualfunds');    
			$this->loadModel('Customers'); 
			$this->loadModel('Banks'); 
			$this->loadModel('Plans'); 
			$this->loadModel('Products');
			
			$this->Search->addFilter('customer', [
            'attributes'=>['placeholder'=>'Customer Name','label'=>'Customer Name :','class'=>'searchfrm','value' => isset($this->request->query['customer'])?$this->request->query['customer']:''],  
            'options' => $this->Customers->find('list',['keyField' => 'id','valueField' => 'firstname'])->order(['Customers.firstname'=>'ASC'])->toArray(),           
			]); 
			$this->Search->addFilter('cpanno', [
			'attributes'=>['placeholder'=>'Pan No','label'=>'Pan No :','class'=>'searchfrm','value' => isset($this->request->query['cpanno'])?$this->request->query['cpanno']:''],
			]);
			
			if (!empty($this->request->query)) {
				$customer = $this->request->query['customer'];
				$panno = (($this->request->query['cpanno'] != '')? $this->request->query['cpanno'] : '');
				if($panno !=''){
					$custmoerId = $this->getCustomerId($panno);
					if($custmoerId != ''){
						$customer = $custmoerId;
					}
				}
				if($customer !=''){  
					$customer = $this->Customers->get($customer, [
                    'contain' => ['Mutualfunds','Fds','Ncds','Ipos']
					]);
					
					$this->set('customer', $customer);
					$this->set('_serialize', ['customer']);
					
					}else{
					$customer = array();
					$this->set('customer', $customer);
					$this->set('_serialize', ['customer']);
				}
				}else{
                $customer = array();
                $this->set('customer', $customer);
                $this->set('_serialize', ['customer']);
			}
		}
		
		/*public function fdportfolioexceldata()
    {

        $this->loadModel('Filenames');
        $this->loadModel('Mtables');
        $this->loadModel('Branches');

        $fileName = "fdportfolioexceldata_" . date("d-m-y:h:s") . ".xls";
        $conditions = array();
        if($this->Auth->user('group_id') > 2)
			{
				$conditions['conditions']['branch_id'] = $this->Auth->user('branch_id'); 
			}

        $mdatas = $this->Mtables->find('all', $conditions);
        $mdatas->order(['Mtables.id' => 'DESC']);
        $datafds = $mdatas->toArray();
        $mbranches = TableRegistry::get('Branches');
        $musers = TableRegistry::get('Users');
        $brokers = TableRegistry::get('Brokers');

        $headerRow = array("Sr No", "Applicant Name", "Pan No", 'Investment Date', 'Payment Mode', 'UTR/FDR No', 'Principle Investment', 'Period', 'Maturity Date', 'Address', 'Product name', 'Company Name', 'Investment Branch', 'Branch User', 'Sub Broker 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]->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];
            $j++;
        }
        $this->ExportXls->export($fileName, $headerRow, $data);
    }*/
    
    public function fdportfolioexceldata($customername = null,$companyname = null,$datedfrom = null,$datedto = null)
    {
        $this->loadModel('Filenames');
        $this->loadModel('Mtables');
        $this->loadModel('Branches');
        $fileName = "fdportfolioexceldata_" . date("d-m-y:h:s") . ".xls";
        $conditions = array();
         $datedfrom = $datedfrom != 'all' ? $datedfrom : '';
         $datedto = $datedto != 'all' ? $datedto : '';
        if(((!empty($datedfrom)) ||(!empty($datedto)))){
				if ($datedfrom && $datedto){
					if($datedfrom == $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 >= ' => $datedfrom,'Mtables.maturity_date <= ' => $datedto))));
				}else{
					if($datedfrom){
                     $conditions = array('conditions' => array('Mtables.maturity_date >= ' => $datedfrom));
					}
					if($datedto){
                     $conditions = array('conditions' => array('Mtables.maturity_date <= ' => $datedto));
					}
				} 
			}

			if(isset($customername) && !empty($customername)){
				if ($customername && $customername != 'all'){
				 $conditions['conditions']['customer_name'] = $customername; 
				}
				}

				if(isset($companyname) && !empty($companyname)){
				if ($companyname && $companyname != 'all'){ $conditions['conditions']['csvid'] = $companyname; }
				}

        if($this->Auth->user('group_id') > 2)
			{
				$conditions['conditions']['branch_id'] = $this->Auth->user('branch_id'); 
			}
        $mdatas = $this->Mtables->find('all', $conditions);
        $mdatas->order(['Mtables.id' => 'DESC']);
        $datafds = $mdatas->toArray();
        
        $mbranches = TableRegistry::get('Branches');
        $musers = TableRegistry::get('Users');
        $brokers = TableRegistry::get('Brokers');

        $headerRow = array("Sr No", "Applicant Name", "Pan No", 'Investment Date', 'Payment Mode', 'UTR/FDR No', 'Principle Investment', 'Period', 'Maturity Date', 'Address', 'Product name', 'Company Name', 'Investment Branch', 'Branch User', 'Sub Broker 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]->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];
            $j++;
        }
        if(empty($data)){
        	$this->Flash->error(__('Data Is Not There'));
        	return $this->redirect('/reports/fdcprofile');
        }
        $this->ExportXls->export($fileName, $headerRow, $data);
    }
    
		
		public function clientwisemf(){
		
			$this->loadModel('Mutualfunds'); 
			$this->loadModel('Fds'); 
			$this->loadModel('Mtables'); 
			$this->loadModel('Filenames'); 
			$this->loadModel('Customers'); 
			$this->loadModel('Banks'); 
			$this->loadModel('Plans'); 
			$this->loadModel('Products');
			$this->loadModel('Companies');
			$all = array('all'=>'All');
			
			//$this->Search->addFilter('customer_name', ['attributes'=>['placeholder'=>'Applicant Name','label'=>'Applicant Name :','id'=>'applicants','class'=>'searchfrm','value'=>isset($this->request->query['customer_name'])?$this->request->query['customer_name']:''],'options' => $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'=>'Applicant Name','label'=>'Applicant Name','class'=>'serachfrm','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('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']:''],
			]);
			
			$conditions = ''; 
			
			$result = '';
			if (!empty($this->request->query) && count($this->request->query) > 1) { 
				
				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));
				
				 //$conditions = array('and' => array(array('Mtables.maturity_date >= ' => $datedform,'Mtables.maturity_date <= ' => $datedto)));
				 $conditionsfd = array('conditions' => array('and' => array(array('Mtables.maturity_date >= ' => $datedform,'Mtables.maturity_date <= ' => $datedto))));
				 $conditionsncd = array('conditions' => array('and' => array(array('Mtables.maturity_date >= ' => $datedform,'Mtables.maturity_date <= ' => $datedto))));
				 $conditionsgp = array('conditions' => array('and' => array(array('Mtables.maturity_date >= ' => $datedform,'Mtables.maturity_date <= ' => $datedto))));
				}		
				
				if(isset($this->request->query['customer_name']) && !empty($this->request->query['customer_name'])){
					$customer_name = $this->request->query['customer_name']; 
					//print_r($customer_name);die();
				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'];
					
    				$conditionsfd['conditions']['or']['pan_no'] = $applicant_pan;
					$conditionsfd['conditions']['type_of_data'] = 'fd';
				    $conditionsfd['conditions']['or']['customer_name'] = $customer_name;
					
					$conditionsncd['conditions']['or']['pan_no'] = $applicant_pan;
					$conditionsncd['conditions']['type_of_data'] = 'ncd';
				    $conditionsncd['conditions']['or']['customer_name'] = $customer_name; 
					
					$conditionsgp['conditions']['or']['pan_no'] = $applicant_pan;
					$conditionsgp['conditions']['type_of_data'] = 'gp';
				    $conditionsgp['conditions']['or']['customer_name'] = $customer_name; 
					
				}
				}
				
			}    
			$connection = ConnectionManager::get('default');
			
			if(isset($applicant_pan) || isset($customer_name)){
					$result['fd']= $this->Mtables->find('all',$conditionsfd)->toArray();
					$result['ncd']= $this->Mtables->find('all',$conditionsncd)->toArray();
					$result['gp']= $this->Mtables->find('all',$conditionsgp)->toArray();
			}
			
			//$custname = $this->Mtables->find('list',['valueField'=>'customer_name'])->order(['customer_name' => 'ASC'])->group(['Mtables.customer_name'])->toArray();
			
			//print_r($custname);die();
			
			$this->set(compact('result','company','datedform','datedto','dated2','dated1','customer_name','applicant_pan','custname'));
            $this->set('_serialize', ['result']);
		}
		
		/* public function clientwisefd(){
    
            $this->loadModel('Fds');
            $this->loadModel('Mtables');
            $this->loadModel('Filenames');
            $this->loadModel('Customers');
            $this->loadModel('Banks');
            $this->loadModel('Plans');
            $this->loadModel('Products');
            $this->loadModel('Companies');
            $all = array('all'=>'All');
    
    
            $this->Search->addFilter('customer_name',['attributes'=>['placeholder'=>'Applicant Name','label'=>'Applicant 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);
    
    
    
    
    
            $conditions = array();
    
            $result = array();
            
            if(isset($this->request->query['customer_name']) && !empty($this->request->query['customer_name'])){
                $customer_name = $this->request->query['customer_name'];
                //print_r($customer_name);die();
                if ($customer_name && $customer_name != 'all'){
                    $applicant_pan_no = $this->Mtables->find('all',['conditions'=>['Mtables.customer_name' => $customer_name]])->toArray();
                    //pr($applicant_pan_no);
    
    
                    $conditionsfd['conditions']['customer_name'] = $customer_name;
    
                }
            }
            
            $pan_nos = $this->Mtables->find('list',['keyField'=>'pan_no','valueField'=>'pan_no'])->where(['pan_no !=' => '0','Mtables.customer_name' => $customer_name])->order(['pan_no' => 'ASC'])->group(['Mtables.pan_no'])->toArray();
    
            $is_pan_null = $this->Mtables->find('list',['keyField'=>'pan_no','valueField'=>'pan_no'])->where(['pan_no !=' => '0','Mtables.customer_name' => $customer_name])->order(['pan_no' => 'ASC'])->group(['Mtables.pan_no'])->toArray();
           // pr($pan_nos);exit;
    
            $is_pan_null_exists = $this->Mtables->find('all', array('conditions'=>array(array('Mtables.pan_no IS NULL'),'Mtables.customer_name' => $customer_name)));
            $pan_nullnumbercnt = $is_pan_null_exists->count();
    
    
    
            if(isset($this->request->query['pan_no'])){
                $applicant_pan = $this->request->query['pan_no'];
                if(!empty($this->request->query['pan_no'])){
                    $conditionsfd['conditions']['pan_no'] = $applicant_pan;
                }else{
                    if ($pan_nullnumbercnt > 0){
                        $conditionsfd['conditions'][] = 'pan_no IS NULL';
                    }else{
                        if(!empty($pan_nos[array_keys($pan_nos)[0]])){
                            $applicant_pan = $pan_nos[array_keys($pan_nos)[0]];
                            $conditionsfd['conditions']['pan_no'] = $applicant_pan;
                            $this->request->query['pan_no'] = $applicant_pan;
                        }
    
                    }
                }
            }else{
    
                if(!empty($pan_nos[array_keys($pan_nos)[0]])){
                    $applicant_pan = $pan_nos[array_keys($pan_nos)[0]];
                    $conditionsfd['conditions']['pan_no'] = $applicant_pan;
                    $this->request->query['pan_no'] = $applicant_pan;
                }else{
                    $applicant_pan = $applicant_pan_no[0]['pan_no'];
                    $conditionsfd['conditions']['pan_no'] = $applicant_pan;
                    $this->request->query['pan_no'] = $applicant_pan;
                }
            }

            if (!empty($this->request->query) && count($this->request->query) > 1) {
    
                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));
    
                    $conditionsfd['conditions'][] = array('and' => array(array('Mtables.maturity_date >= ' => $datedform,'Mtables.maturity_date <= ' => $datedto)));
    
                }
                
                if(!empty($this->request->query['inv_datedform']) && !empty($this->request->query['inv_datedto'])){
                    $dated2 =  (($this->request->query['inv_datedform'] != '')? $this->request->query['inv_datedform'] : '');
                    $date_from = explode('/',$dated2);
                    $datedform  = implode('-', array_reverse($date_from));
                    $dated1= (($this->request->query['inv_datedto']!='')? $this->request->query['inv_datedto']:'');
                    $date_to = explode('/',$dated1);
                    $datedto  = implode('-', array_reverse($date_to));
    
                    $conditionsfd['conditions'][] = array('and' => array(array('Mtables.application_date >= ' => $datedform,'Mtables.application_date <= ' => $datedto)));
    
                }
    
                // if(isset($this->request->query['customer_name']) && !empty($this->request->query['customer_name'])){
                    // $customer_name = $this->request->query['customer_name'];
                    // //print_r($customer_name);die();
                    // if ($customer_name && $customer_name != 'all'){
                        // $applicant_pan_no = $this->Mtables->find('all',['conditions'=>['Mtables.customer_name' => $customer_name]])->toArray();
                        // //pr($applicant_pan_no);
    
    
                        // $conditionsfd['conditions']['customer_name'] = $customer_name;
    
                    // }
                // }
                // if(isset($this->request->query['pan_no'])){
                    // $applicant_pan = $this->request->query['pan_no'];
                    // if(!empty($this->request->query['pan_no'])){
                        // $conditionsfd['conditions']['pan_no'] = $applicant_pan;
                    // }else{
                        // $conditionsfd['conditions'][] = 'pan_no IS NULL';
                    // }
                // }else{
                    // $applicant_pan = $applicant_pan_no[0]['pan_no'];
                    // $conditionsfd['conditions']['pan_no'] = $applicant_pan;
                // }
    
            }
    
            //$pan_nos = $this->Mtables->find('list',['keyField'=>'pan_no','valueField'=>'pan_no'])->where(['pan_no !=' => '0','Mtables.customer_name' => $customer_name])->order(['pan_no' => 'ASC'])->group(['Mtables.pan_no'])->toArray();
            $this->Search->addFilter('pan_no', ['attributes'=>['placeholder'=>'Pan No','label'=>'Pan No :','class'=>'searchfrm','id'=>'pan_no','value'=>!empty($this->request->query['pan_no'])?$this->request->query['pan_no']:''],'options' => $pan_nos,
            ]);
    
            $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']:''],
            ]);
    
            $connection = ConnectionManager::get('default');
            if(isset($applicant_pan) || isset($customer_name)){
                $result['fd']= $this->Mtables->find('all',$conditionsfd)->order(['application_date' => 'ASC'])->toArray();
            }
    
            if(!empty($result['fd'])){
                $customer_name = $result['fd'][0]['customer_name'];
            }
    
            //$custname = $this->Mtables->find('list',['valueField'=>'customer_name'])->order(['customer_name' => 'ASC'])->group(['Mtables.customer_name'])->toArray();
    
            //pr($conditionsfd);
            //pr($result);die();
            $filenames = $this->Filenames->find('list',['keyField'=>'id','valueField'=>'fname'])->order(['fname' => 'ASC'])->toArray();
    
            $this->set(compact('result','filenames','datedform','datedto','dated2','dated1','customer_name','applicant_pan','custname','pan_nullnumbercnt'));
            $this->set('_serialize', ['result']);
            if(!empty($this->request->query['export']) && ($this->request->query['export']=='xls' || $this->request->query['export']=='pdf') ){
                $this->set('filename', 'Client_Portfolio');
                if(!empty($this->request->query['export']=='pdf')){
                    $this->render('export_clientwisefd_pdf','export_landscape_pdf');
                }else{
                    $this->render('export_clientwisefd','export_xls');
                }
            }
        } */
		
		public function clientwisefd(){ //pr($this->request->query);
    
            $this->loadModel('Fds');
            $this->loadModel('Mtables');
            $this->loadModel('Filenames');
            $this->loadModel('Customers');
            $this->loadModel('Banks');
            $this->loadModel('Plans');
            $this->loadModel('Products');
            $this->loadModel('Companies');
            $all = array('all'=>'All');
    
    
            $this->Search->addFilter('customer_name',['attributes'=>['placeholder'=>'Applicant Name','label'=>'Applicant Name','class'=>'serachfrm serachfrm_clearable','id'=>'autocomplete1','value'=>isset($this->request->query['customer_name'])?$this->request->query['customer_name']:''],]);
			
			/* $this->Search->addFilter('minorname',['attributes'=>['placeholder'=>'Minor Name','label'=>'Minor Name','class'=>'serachfrm serachfrm_clearable','id'=>'minor','value'=>isset($this->request->query['minorname'])?$this->request->query['minorname']:''],]); */
			
			$this->Search->addFilter('minorname', [
               'attributes'=>['placeholder'=>'Minor Name','label'=>'Minor Name :','class'=>'searchfrm','id'=>'minor','value' => isset($this->request->query['minorname'])?$this->request->query['minorname']:''], 'options' => $this->Fds->find('list',['keyField' => 'minorname','valueField' => 'minorname'])->toArray(), 'empty' => 'Select minor'
            ]);
			
    
            $custname = $this->Mtables->find('list',['valueField'=>'customer_name'])->order(['customer_name' => 'ASC'])->group(['Mtables.customer_name'])->toArray();
            $custname = array_values($custname);
			
			$minorname = $this->Fds->find('list',['valueField'=>'minorname'])->order(['minorname' => 'ASC'])->group(['Fds.minorname'])->toArray();
            $minorname = array_values($minorname);
    
    
    
            $conditions = $confd = $pan_nos = array();
			$customer_name = $minor_name = "";
			$pan_nullnumbercnt = 0;
            $result = array();
            
            if(isset($this->request->query['customer_name']) && !empty($this->request->query['customer_name'])){
                $customer_name = $this->request->query['customer_name'];
                //print_r($customer_name);die();
                if ($customer_name && $customer_name != 'all'){
                    $applicant_pan_no = $this->Mtables->find('all',['conditions'=>['Mtables.customer_name' => $customer_name]])->toArray();
                    //pr($applicant_pan_no);   
                    $conditions['conditions']['customer_name'] = $customer_name;   
                }
				
				$pan_nos = $this->Mtables->find('list',['keyField'=>'pan_no','valueField'=>'pan_no'])->where(['pan_no !=' => '0','Mtables.customer_name' => $customer_name])->order(['pan_no' => 'ASC'])->group(['Mtables.pan_no'])->toArray();
    
				$is_pan_null = $this->Mtables->find('list',['keyField'=>'pan_no','valueField'=>'pan_no'])->where(['pan_no !=' => '0','Mtables.customer_name' => $customer_name])->order(['pan_no' => 'ASC'])->group(['Mtables.pan_no'])->toArray();
			   // pr($pan_nos);exit;
		
				$is_pan_null_exists = $this->Mtables->find('all', array('conditions'=>array(array('Mtables.pan_no IS NULL'),'Mtables.customer_name' => $customer_name)));
				$pan_nullnumbercnt = $is_pan_null_exists->count();
				
				
            }elseif(isset($this->request->query['minorname']) && !empty($this->request->query['minorname'])){
				$minor_name = $this->request->query['minorname'];
				if ($minor_name && $minor_name != 'all'){
                    $confd['conditions']['minorname'] = $minor_name;   
                }
				
				$pan_nos = $this->Fds->find('list',['keyField'=>'panno','valueField'=>'panno'])->where(['panno !=' => '0','Fds.minorname' => $minor_name])->order(['panno' => 'ASC'])->group(['Fds.panno'])->toArray();
			}
            
            
    
    
    
            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;
					$confd['pan_no'] = $this->request->query['pan_no'];   
                }else{
                    if (isset($pan_nullnumbercnt) && $pan_nullnumbercnt > 0){
                        $conditions['conditions'][] = 'pan_no IS NULL';
                    }else{
                        if(!empty($pan_nos[array_keys($pan_nos)[0]])){
                            $applicant_pan = $pan_nos[array_keys($pan_nos)[0]];
                            $conditions['conditions']['pan_no'] = $applicant_pan;
                            $this->request->query['pan_no'] = $applicant_pan;
                        }
    
                    }
                }
            }else{
				if(!empty($pan_nos)){
					if(!empty($pan_nos[array_keys($pan_nos)[0]])){
						$applicant_pan = $pan_nos[array_keys($pan_nos)[0]];
						$conditions['conditions']['pan_no'] = $applicant_pan;
						$this->request->query['pan_no'] = $applicant_pan;
					}else{
						if(!empty($applicant_pan_no)){
							$applicant_pan = $applicant_pan_no[0]['pan_no'];
							$conditions['conditions']['pan_no'] = $applicant_pan;
							$this->request->query['pan_no'] = $applicant_pan;
						}                   
					}
				}
   
            }

            if (!empty($this->request->query) && count($this->request->query) > 1) {
    
                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));
    
                    $conditions['conditions'][] = array('and' => array(array('Mtables.maturity_date >= ' => $datedform,'Mtables.maturity_date <= ' => $datedto)));
					$confd['conditions'][] = array('and' => array(array('Fds.maturitydate >= ' => $datedform,'Fds.maturitydate <= ' => $datedto)));
    
                }
                
                if(!empty($this->request->query['inv_datedform']) && !empty($this->request->query['inv_datedto'])){
                    $dated2 =  (($this->request->query['inv_datedform'] != '')? $this->request->query['inv_datedform'] : '');
                    $date_from = explode('/',$dated2);
                    $datedform  = implode('-', array_reverse($date_from));
                    $dated1= (($this->request->query['inv_datedto']!='')? $this->request->query['inv_datedto']:'');
                    $date_to = explode('/',$dated1);
                    $datedto  = implode('-', array_reverse($date_to));
    
                    $conditions['conditions'][] = array('and' => array(array('Mtables.application_date >= ' => $datedform,'Mtables.application_date <= ' => $datedto)));
					
					$confd['conditions'][] = array('and' => array(array('Fds.planstartdate >= ' => $datedform,'Fds.planstartdate <= ' => $datedto)));
    
                }
    
                /*if(isset($this->request->query['customer_name']) && !empty($this->request->query['customer_name'])){
                    $customer_name = $this->request->query['customer_name'];
                    //print_r($customer_name);die();
                    if ($customer_name && $customer_name != 'all'){
                        $applicant_pan_no = $this->Mtables->find('all',['conditions'=>['Mtables.customer_name' => $customer_name]])->toArray();
                        //pr($applicant_pan_no);
    
    
                        $conditions['conditions']['customer_name'] = $customer_name;
    
                    }
                }
                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{
                        $conditions['conditions'][] = 'pan_no IS NULL';
                    }
                }else{
                    $applicant_pan = $applicant_pan_no[0]['pan_no'];
                    $conditions['conditions']['pan_no'] = $applicant_pan;
                }*/
    
            }
    
            //$pan_nos = $this->Mtables->find('list',['keyField'=>'pan_no','valueField'=>'pan_no'])->where(['pan_no !=' => '0','Mtables.customer_name' => $customer_name])->order(['pan_no' => 'ASC'])->group(['Mtables.pan_no'])->toArray();
            $this->Search->addFilter('pan_no', ['attributes'=>['placeholder'=>'Pan No','label'=>'Pan No :','class'=>'searchfrm','id'=>'pan_no','value'=>!empty($this->request->query['pan_no'])?$this->request->query['pan_no']:''],'options' => $pan_nos,
            ]);
    
            $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']:''],
            ]);
    
            $connection = ConnectionManager::get('default');
			
            if(!empty($customer_name)){ 
                $result['fd']= $this->Mtables->find('all',$conditions)->order(['application_date' => 'ASC'])->toArray(); //pr($result);
            }elseif(!empty($minor_name)){ 
				$result['fd'] = $this->Fds->find('all',$confd)->contain(['CompanyProducts'])->order(['planstartdate' => 'ASC'])->toArray(); //pr($result);
			}
    
            if(!empty($result['fd'])){
                $customer_name = $result['fd'][0]['customer_name'];
            }
            //$custname = $this->Mtables->find('list',['valueField'=>'customer_name'])->order(['customer_name' => 'ASC'])->group(['Mtables.customer_name'])->toArray();
    
            //pr($conditions);
            //pr($result);die();
            $filenames = $this->Filenames->find('list',['keyField'=>'id','valueField'=>'fname'])->order(['fname' => 'ASC'])->toArray();
    
            $this->set(compact('result','filenames','datedform','datedto','dated2','dated1','customer_name','minor_name','applicant_pan','custname', 'minorname','pan_nullnumbercnt'));
            $this->set('_serialize', ['result']);
            if(!empty($this->request->query['export']) && ($this->request->query['export']=='xls' || $this->request->query['export']=='pdf') ){
                $this->set('filename', 'Client_Portfolio');
                if(!empty($this->request->query['export']=='pdf')){
                    $this->render('export_clientwisefd_pdf','export_landscape_pdf');
                }else{
                    $this->render('export_clientwisefd','export_xls');
                }
            }
        }
        
        public function csltdmoblztionfd(){
            $this->loadModel('Filenames');
            $this->loadModel('Mtables');
            $this->loadModel('Mutualfunds');
            $this->loadModel('Fds');
            $this->loadModel('Customers');
            $this->loadModel('Banks');
            $this->loadModel('Plans');
            $this->loadModel('Products');
            $this->loadModel('Companies');
            $this->loadModel('Regionalmanagers');
            $all = array('all'=>'All');

            $this->Search->addFilter('company', ['attributes'=>['placeholder'=>'Format Name','label'=>'Company Name :','class'=>'searchfrm','value'=>isset($this->request->query['company'])?$this->request->query['company']:''],'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(['institution_name' => 'ASC'])->group(['Mtables.institution_name'])->toArray(),
            ]);

            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(),
                ]);
            }
            
            if($this->Auth->user('group_id')=='2'){
                $rmanager = $this->Fds->Regionalmanagers->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+$this->Fds->Regionalmanagers->find('list',['valueField'=>'manager'])->order(['manager' => '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']:''],
            ]);	

            $conditions = array();
            if($this->Auth->user('group_id')=='3'){
                $branch_id = $this->Auth->user('branch_id');
                $conditions['conditions']['and']['Mtables.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']['Mtables.branch_id'] = "$branch_id";
                $conditions['conditions']['and']['Mtables.modifiedby'] = "$userid";
            }
            $result = array();
            if (!empty($this->request->query) && count($this->request->query) > 1) {
                $branch = ($this->request->query['branch'])? $this->request->query['branch'] : '';
                $company = ($this->request->query['company'])? $this->request->query['company'] : '';
                $rmanager = ($this->request->query['regionalmanager'])? $this->request->query['regionalmanager'] : '';
                
            }
            
            $extraCondition = "";

			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));
			//	maturity_date >= :fromdate AND maturity_date <= :todate '

                $extraCondition .= "maturity_date >= '".$datedform."' AND 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));
			//	maturity_date >= :fromdate AND maturity_date <= :todate '
			   if(!empty($this->request->query['datedform']) && !empty($this->request->query['datedto'])){
                $extraCondition .= " AND application_date >= '".$invdatedform."' AND application_date <= '".$invdatedto."'";
			   }else{
				$extraCondition .= "application_date >= '".$invdatedform."' AND application_date <= '".$invdatedto."'";  
			   }
            }

            if(!empty($this->request->query['institution_name']) && $this->request->query['institution_name']!= 'all'){
                $extraCondition .= " AND institution_name LIKE '%".$this->request->query['institution_name']."%'  ";
            }

			// for RM search
      
              if(!empty($this->request->query['regionalmanager']) && $this->request->query['regionalmanager']!= 'all'){
                 $extraCondition .= " AND manager_id = ".$this->request->query['regionalmanager'];
              }
               // print_r($extraCondition); die();
			

            $connection = ConnectionManager::get('default');
              if(isset($branch)  && isset($rmanager)  && isset($company) && ((isset($datedform) && isset($datedto)) || (isset($invdatedform) && isset($invdatedto)))){

                //print_r($branch); echo '<br>';print_r($company);echo '<br>';print_r($datedform);echo '<br>';print_r($datedto);echo '<br>';

                if($branch == 'all' && $company == 'all'){
                    //print_r($branch); print_r($company); die();
                    //$result['MF'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount FROM mutualfunds WHERE maturity_date >= :fromdate AND maturity_date <= :todate',['fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
                    //print_r('SELECT csvid, count(*) as cnumber,sum(REPLACE(investment_amount,",","")) as tamount FROM mtables WHERE '.$extraCondition.' GROUP BY csvid');die();
                   //print_r($result['FD']);echo '1';
                    $result['FD'] = $connection->execute('SELECT csvid, count(*) as cnumber,sum(REPLACE(investment_amount,",","")) as tamount FROM mtables WHERE '.$extraCondition.' GROUP BY csvid',[])->fetchAll('assoc');

                    //print_r($result['FD']);die();
                    //$result['NCD'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount FROM ncds WHERE maturity_date >= :fromdate AND maturity_date <= :todate',['fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
                    //$result['IPO'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount FROM ipos WHERE maturity_date >= :fromdate AND maturity_date <= :todate',['fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
                }
                elseif($branch == 'all' && $company != 'all'){
                    //print_r($branch); print_r($company); die();
                    //$result['MF'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount FROM mutualfunds WHERE productcompanyid = :company AND maturity_date >= :fromdate AND maturity_date <= :todate',['company'=>$company, 'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
                    $result['FD'] = $connection->execute('SELECT csvid, count(*) as cnumber,sum(REPLACE(investment_amount,",","")) as tamount FROM mtables WHERE '.$extraCondition.' AND csvid = :company  GROUP BY csvid',['company'=>$company])->fetchAll('assoc');
                    //print_r($datedform);print_r($datedto);
                    //print_r($result['FD']);die();
                    //$result['NCD'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount FROM ncds WHERE productcompanyid = :company AND maturity_date >= :fromdate AND maturity_date <= :todate',['company'=>$company, 'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
                    //$result['IPO'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount FROM ipos WHERE productcompanyid = :company AND maturity_date >= :fromdate AND maturity_date <= :todate',['company'=>$company, 'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
                }
                elseif($branch != 'all' && $company == 'all'){
                    //$result['MF'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount FROM mutualfunds WHERE branch_id = :branch AND maturity_date >= :fromdate AND maturity_date <= :todate',['branch'=>$branch, 'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
                    $result['FD'] = $connection->execute('SELECT csvid, count(*) as cnumber,sum(REPLACE(investment_amount,",","")) as tamount FROM mtables WHERE '.$extraCondition.' AND branch_id = :branch   GROUP BY csvid',['branch'=>$branch])->fetchAll('assoc');
                    //$result['NCD'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount FROM ncds WHERE branch_id = :branch AND maturity_date >= :fromdate AND maturity_date <= :todate',['branch'=>$branch, 'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
                    //$result['IPO'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount FROM ipos WHERE branch_id = :branch AND maturity_date >= :fromdate AND maturity_date <= :todate',['branch'=>$branch, 'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
                }
                else{
                    //$result['MF'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount FROM mutualfunds WHERE branch_id = :branch AND productcompanyid = :company AND maturity_date >= :fromdate AND maturity_date <= :todate',['branch'=>$branch, 'company'=>$company, 'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
                    $result['FD'] = $connection->execute('SELECT csvid, count(*) as cnumber,sum(REPLACE(investment_amount,",","")) as tamount FROM mtables WHERE '.$extraCondition.' AND   branch_id = :branch AND csvid = :company GROUP BY csvid',['branch'=>$branch, 'company'=>$company])->fetchAll('assoc');
                    //$result['IPO'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount FROM ipos WHERE branch_id = :branch AND productcompanyid = :company AND maturity_date >= :fromdate AND maturity_date <= :todate',['branch'=>$branch, 'company'=>$company, 'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
                }
            }

            //$resultss = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount FROM fds WHERE created BETWEEN 2017-06-01 00:00:00.000000 AND 2017-07-14 00:00:00.000000 ORDER BY id DESC')->fetchAll('assoc');
            //pr($result);exit;
            $filenames = $this->Filenames->find('list',['keyField'=>'id','valueField'=>'fname'])->order(['fname' => 'ASC'])->toArray();
            $this->set(compact('result','company','branch','datedform','datedto','dated2','dated1','filenames'));
            $this->set('_serialize', ['result']);
            //die();
            if(!empty($this->request->query['export']) && ($this->request->query['export']=='xls' || $this->request->query['export']=='pdf') ){
                $this->set('filename', 'Mobilization_Consolidated_Report');
                if(!empty($this->request->query['export']=='pdf')){
                    $this->render('export_csltdmoblztionfd_pdf','export_landscape_pdf');
                }else{
                    $this->render('export_csltdmoblztionfd','export_xls');
                }
            }

        }

        public function ourcompanywisefdrcd(){
            $this->loadModel('Mutualfunds');
            $this->loadModel('Mtables');
            $this->loadModel('Filenames');
            $this->loadModel('Fds');
            $this->loadModel('Customers');
            $this->loadModel('Banks');
            $this->loadModel('Plans');
            $this->loadModel('Products');
            $this->loadModel('Companies');
            $all = array('all'=>'All');

            /* $this->Search->addFilter('company', [
            'attributes'=>['placeholder'=>'Our Company Name','label'=>'Our Company Name :','class'=>'searchfrm','value' => isset($this->request->query['company'])?$this->request->query['company']:''],
            'options' =>  $all+$this->Companies->find('list')-> where(['associatewith' => 'user'])->order(['company' => 'ASC'])->toArray(),
            ]);  */

            $this->Search->addFilter('company', ['attributes'=>['placeholder'=>'Company Name','label'=>'Company Name :','class'=>'searchfrm','value'=>isset($this->request->query['company'])?$this->request->query['company']:''],'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(['institution_name' => 'ASC'])->group(['Mtables.institution_name'])->toArray(),
            ]);

            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('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']:''],
            ]);	

            $conditions = array();
            if($this->Auth->user('group_id')=='3'){
                $branch_id = $this->Auth->user('branch_id');
                $conditions['conditions']['and']['Mtable.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']['Mtable.branch_id'] = "$branch_id";
                $conditions['conditions']['and']['Mtable.modifiedby'] = "$userid";
            }
            $result = array();
            if (!empty($this->request->query) && count($this->request->query) > 1) {
                $branch = ($this->request->query['branch'])? $this->request->query['branch'] : '';
                $company = ($this->request->query['company'])? $this->request->query['company'] : '';
            }

            $extraCondition = "";
            
			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));
			    //	maturity_date >= :fromdate AND maturity_date <= :todate '

                $extraCondition .= "maturity_date >= '".$datedform."' AND 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));
			//	maturity_date >= :fromdate AND maturity_date <= :todate '

                //$extraCondition .= " AND application_date >= $invdatedform AND application_date <= $invdatedto";
                if(!empty($this->request->query['datedform']) && !empty($this->request->query['datedto'])){
					$extraCondition .= " AND application_date >= '".$invdatedform."' AND application_date <= '".$invdatedto."'";
				   }else{
					$extraCondition .= "application_date >= '".$invdatedform."' AND application_date <= '".$invdatedto."'";  
				   }
            }

			if(!empty($this->request->query['institution_name']) && $this->request->query['institution_name']!= 'all'){
                $extraCondition .= " AND institution_name LIKE '%".$this->request->query['institution_name']."%'  ";
            }

            $connection = ConnectionManager::get('default');
             if(isset($branch)  && isset($company) && ((isset($datedform) && isset($datedto)) || (isset($invdatedform) && isset($invdatedto)))){
                if($branch == 'all' && $company == 'all'){

                    //$result['MF'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount, company_id FROM mutualfunds WHERE despatchdate >= :fromdate AND despatchdate <= :todate',['fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');

                    $result['FD'] = $connection->execute('SELECT count(*) as cnumber,sum(REPLACE(investment_amount,",","")) as tamount, csvid FROM mtables WHERE '.$extraCondition.' GROUP BY csvid' ,[])->fetchAll('assoc');

                    //$result['NCD'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount, company_id FROM ncds WHERE despatchdate >= :fromdate AND despatchdate <= :todate',['fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');

                    //$result['IPO'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount, company_id FROM ipos WHERE despatchdate >= :fromdate AND despatchdate <= :todate',['fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');


                }elseif($branch != 'all' && $company == 'all'){

                    //$result['MF'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount, company_id FROM mutualfunds WHERE  branch_id = :branch AND despatchdate >= :fromdate AND despatchdate <= :todate',['branch'=>$branch, 'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');

                    $result['FD'] = $connection->execute('SELECT count(*) as cnumber,sum(REPLACE(investment_amount,",","")) as tamount, csvid FROM mtables WHERE '.$extraCondition.' AND branch_id = :branch GROUP BY csvid',['branch'=>$branch])->fetchAll('assoc');

                    //$result['NCD'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount, company_id FROM ncds WHERE  branch_id = :branch AND despatchdate >= :fromdate AND despatchdate <= :todate',['branch'=>$branch, 'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');

                    //$result['IPO'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount, company_id FROM ipos WHERE  branch_id = :branch AND despatchdate >= :fromdate AND despatchdate <= :todate',['branch'=>$branch, 'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');

                }elseif($branch == 'all' && $company != 'all'){

                    //$result['MF'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount, company_id FROM mutualfunds WHERE company_id = :company AND despatchdate >= :fromdate AND despatchdate <= :todate',['company'=>$company, 'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');

                    $result['FD'] = $connection->execute('SELECT count(*) as cnumber,sum(REPLACE(investment_amount,",","")) as tamount, csvid FROM mtables WHERE '.$extraCondition.' AND csvid = :company GROUP BY csvid',['company'=>$company])->fetchAll('assoc');

                    //$result['NCD'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount, company_id FROM ncds WHERE company_id = :company AND despatchdate >= :fromdate AND despatchdate <= :todate',['company'=>$company, 'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');

                    //$result['IPO'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount, company_id FROM ipos WHERE company_id = :company AND despatchdate >= :fromdate AND despatchdate <= :todate',['company'=>$company, 'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');

                }else{

                    //$result['MF'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount, company_id FROM mutualfunds WHERE branch_id = :branch AND company_id = :company AND despatchdate >= :fromdate AND despatchdate <= :todate',['company'=>$company, 'branch'=>$branch, 'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');

                    $result['FD'] = $connection->execute('SELECT count(*) as cnumber,sum(REPLACE(investment_amount,",","")) as tamount, csvid FROM mtables WHERE '.$extraCondition.' AND  branch_id = :branch AND csvid = :company GROUP BY csvid',['company'=>$company, 'branch'=>$branch, 'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');

                    //$result['NCD'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount, company_id FROM ncds WHERE branch_id = :branch AND company_id = :company AND despatchdate >= :fromdate AND despatchdate <= :todate',['company'=>$company, 'branch'=>$branch, 'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');

                    //$result['IPO'] = $connection->execute('SELECT count(*) as cnumber,sum(amount) as tamount, company_id FROM ipos WHERE branch_id = :branch AND company_id = :company AND despatchdate >= :fromdate AND despatchdate <= :todate',['company'=>$company, 'branch'=>$branch, 'fromdate'=>$datedform, 'todate'=>$datedto])->fetchAll('assoc');
                }
            }
            $filenames = $this->Filenames->find('list',['keyField'=>'id','valueField'=>'fname'])->order(['fname' => 'ASC'])->toArray();
            $this->set(compact('result','company','branch','datedform','datedto','dated2','dated1','filenames'));
            $this->set('_serialize', ['result']);
            
            if(!empty($this->request->query['export']) && ($this->request->query['export']=='xls' || $this->request->query['export']=='pdf') ){
                $this->set('filename', 'Mobilization_Company_Wise_Report');
                if(!empty($this->request->query['export']=='pdf')){
                    $this->render('export_ourcompanywisefdrcd_pdf','export_landscape_pdf');
                }else{
                    $this->render('export_ourcompanywisefdrcd','export_xls');
                }
            }

        }
        
        public function getpannosoptions(){
            
            $pan_nos = array();
			
			
            if(!empty($this->request->data['cname'])){
				
                $customer_name = $this->request->data['cname'];
				
				if($this->request->data['minor']){
					$this->loadModel('Fds');
					$pan_nos = $this->Fds->find('list',['keyField'=>'panno','valueField'=>'panno'])->where(['panno !=' => '0','Fds.minorname' => $customer_name])->order(['panno' => 'ASC'])->group(['Fds.panno'])->toArray();
				}else{
					$this->loadModel('Mtables');
					$pan_nos = $this->Mtables->find('list',['keyField'=>'pan_no','valueField'=>'pan_no'])->where(['pan_no !=' => '0','Mtables.customer_name' => $customer_name])->order(['pan_no' => 'ASC'])->group(['Mtables.pan_no'])->toArray();
				}
                
            }
            echo json_encode($pan_nos);exit;
        }
		
		
	// For search branchwise RM in fdmaturity
    
    public function getmanagersearch(){
        
        $this->loadModel('Regionalmanagers');
        $this->loadModel('Fds');
        $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']);
        }
        //$rmdataplan = $this->Fds->Regionalmanagers->find('all',['conditions' => ['branch_id' => $id]]); //echo '<pre>';print_r($rmdataplan);die();
        $this->set(compact('rmdataplan')); 
    }		
		
		
		
		
		
		
	}  //end class
