<?php
require_once('helper.php'); 

if(!session_id()){
	session_start();
}

class db_function extends helper
{ 

public function set_new_company($company_name,$status)
{
  	$response = array();
		$query = "INSERT INTO gbca_company_master(company_name,
		company_added_date,
		company_status
	) VALUES (?,CONVERT_TZ(NOW(),@@session.time_zone,'+5:30'),?)";

	if($stmt_query = $this->conn->prepare($query)){
		$stmt_query->bind_param("ss",$company_name,$status);
     
		if($stmt_query->execute()){
		  
			$response['n'] = 1;
			$response['msg'] = "Enter Company Successfully";
		
          
		}
		else{
			$response['n'] = 0;
			$response['msg'] = $this->conn->error;
		}
	}
	else
	{
		$response['n'] = 0;
		$response['msg'] = $this->conn->error;
	}
	return $response;  
}

	public function set_185($company_detail_id,$company_edit_id,$type_company,$lending,$prevailing,$financ_year,$cut_off,$other,$status){

if($company_edit_id != '')
{
  	$response = array();
		$query = "update gbca_company_detail_master set 
			company_type_name=?,
			company_detail_lending_operation=?,
			company_detail_interest_charge=?,
			company_detail_financial_year=?,
			company_detail_cutoff_date=?,
		    company_detail_other_company_name=?,
			company_detail_status=? where company_detail_id=?";


	if($stmt_query = $this->conn->prepare($query)){
		$stmt_query->bind_param("sssssssi",$type_company,$lending,$prevailing,$financ_year,$cut_off,$other,$status,$company_edit_id);
     
		if($stmt_query->execute()){
			$response['n'] = 1;
			$response['msg'] = "basic  Added Successfully";
			$response['id']=$this->conn->insert_id;
      

		}
		else{
			$response['n'] = 0;
			$response['msg'] = $this->conn->error;
		}
	}
	else
	{
		$response['n'] = 0;
		$response['msg'] = $this->conn->error;
	}
	return $response;   
}
else
{
    	$response = array();
		$query = "update gbca_company_detail_master set 
			company_type_name=?,
			company_detail_lending_operation=?,
			company_detail_interest_charge=?,
			company_detail_financial_year=?,
			company_detail_cutoff_date=?,
		     company_detail_other_company_name=?,
			company_detail_status=? where company_detail_id=?";


	if($stmt_query = $this->conn->prepare($query)){
		$stmt_query->bind_param("sssssssi",$type_company,$lending,$prevailing,$financ_year,$cut_off,$other,$status,$company_detail_id);
     
		if($stmt_query->execute()){
		    
		    $date_arr = "SELECT   date_format(str_to_date( company_detail_cutoff_date, '%Y-%m-%d'),' %d %M %Y') as date  FROM gbca_company_detail_master WHERE company_detail_id= '$company_detail_id'";
            
		  $date = $this->conn->query($date_arr)->fetch_all(MYSQLI_ASSOC);
		  
		  ///print_r($date);
			$response['n'] = 1;
			$response['msg'] = "basic  Added Successfully";
		    $response['basic'] =5;
            $response['date'] =$date;

		}
		else{
			$response['n'] = 0;
			$response['msg'] = $this->conn->error;
		}
	}
	else
	{
		$response['n'] = 0;
		$response['msg'] = $this->conn->error;
	}
	return $response;
}
	
}

	public function set_borrowing($company_detail_id,$company_edit_id,$banks,$nbfc,$financ_inst,$indian_comp,$llp,$foreign,$total,$equity,$capital,$total_cap,$twice_cap,$repayment,$shareholders,$specify_sharehold,$status){

if($company_edit_id !='')
{
 $response = array();
		$query = "update gbca_company_detail_master set
		company_detail_borrow_from_banks=?,
		company_detail_borrow_from_nbfc=?,
		company_detail_borrow_from_other_institude=?,
		company_detail_borrow_from_indian_company=?,
		company_detail_borrow_from_llp=?,
		company_detail_borrow_from_foreign_company=?,
		company_detail_total_borrowing=?,

		company_detail_equity_share_capital=?,
		company_detail_preference_share_capital=?,
		company_detail_total_paid_up_share=?,
		company_detail_twice_paid_up_share=?,
		company_detail_default_borrowings=?,
	    company_detail_shareholder_corporate=?,
		 shareholders_include_body_corporate=?,
		company_detail_status=? where company_detail_id=?";


	if($stmt_query = $this->conn->prepare($query)){
		$stmt_query->bind_param("iiiiiiiiiiissssi",$banks,$nbfc,$financ_inst,$indian_comp,$llp,$foreign,$total,$equity,$capital,$total_cap,$twice_cap,$repayment,$shareholders,$specify_sharehold,$status,$company_edit_id);
     
		if($stmt_query->execute()){
			$response['n'] = 1;
			$response['msg'] = "boroowing  Added Successfully";
			$response['id']=$this->conn->insert_id;
      

		}
		else{
			$response['n'] = 0;
			$response['msg'] = $this->conn->error;
		}
	}
	else
	{
		$response['n'] = 0;
		$response['msg'] = $this->conn->error;
	}
	return $response;   
}
else
{
   
    $response = array();
		$query = "update gbca_company_detail_master set
		company_detail_borrow_from_banks=?,
		company_detail_borrow_from_nbfc=?,
		company_detail_borrow_from_other_institude=?,
		company_detail_borrow_from_indian_company=?,
		company_detail_borrow_from_llp=?,
		company_detail_borrow_from_foreign_company=?,
		company_detail_total_borrowing=?,

		company_detail_equity_share_capital=?,
		 company_detail_preference_share_capital=?,
		 company_detail_total_paid_up_share=?,
		 company_detail_twice_paid_up_share=?,
		 company_detail_default_borrowings=?,
		 company_detail_shareholder_corporate=?,
		 shareholders_include_body_corporate=?,
		 company_detail_status=? where company_detail_id=?";


	if($stmt_query = $this->conn->prepare($query)){
		$stmt_query->bind_param("iiiiiiiiiiissssi",$banks,$nbfc,$financ_inst,$indian_comp,$llp,$foreign,$total,$equity,$capital,$total_cap,$twice_cap,$repayment,$shareholders,$specify_sharehold,$status,$company_detail_id);
     
		if($stmt_query->execute()){
			$response['n'] = 1;
			$response['msg'] = "borrowing  Added Successfully";
		

		}
		else{
			$response['n'] = 0;
			$response['msg'] = $this->conn->error;
		}
	}
	else
	{
		$response['n'] = 0;
		$response['msg'] = $this->conn->error;
	}
	return $response;  
}
		
}

	public function set_sharing($company_detail_id,$company_edit_id,$sub_company_type_id,$sub_company_name,$sub_company_transaction_relation,$sub_company_step){

  if($company_edit_id != '')
  {

		$response = array();
		$query = "INSERT INTO gbca_sub_company_mapping(company_detail_id,
		sub_company_type_id,
		sub_company_type_name,
		sub_company_transaction_relation,
		sub_company_step,
		sub_company_added_date,
		sub_company_status
	) VALUES (?,?,?,?,?,CONVERT_TZ(NOW(),@@session.time_zone,'+5:30'),'0')";


	if($stmt_query = $this->conn->prepare($query)){
		$stmt_query->bind_param("iisss",$company_edit_id,$sub_company_type_id,$sub_company_name,$sub_company_transaction_relation,$sub_company_step);
     
		if($stmt_query->execute()){

		  $section_arr = "SELECT CASE
    WHEN sub_company_type_id = 1 THEN 'Holding Companies'
    WHEN sub_company_type_id = 2 THEN 'Subsidiary Companies'
    WHEN sub_company_type_id = 3 THEN 'Wholly Owned Subidiary Companies'
    WHEN sub_company_type_id = 4 THEN 'Managing Director'
    WHEN sub_company_type_id = 5 THEN 'Whole time directors'
    WHEN sub_company_type_id = 6 THEN 'Director of an Company'
    WHEN sub_company_type_id = 7 THEN 'Relative of directors'
    WHEN sub_company_type_id = 8 THEN 'Partner of directors in any firm (incl. LLP)'
    WHEN sub_company_type_id = 9 THEN 'Firm (incl. LLP) in which director is a partner'
    WHEN sub_company_type_id = 10 THEN 'Firm (incl. LLP) in which relative of director is a partner'
    WHEN sub_company_type_id = 11 THEN 'Private Companies in which director is a director'
    WHEN sub_company_type_id = 12 THEN 'Private Companies in which director is a Member'
    WHEN sub_company_type_id = 13 THEN 'Director of holding company'
    WHEN sub_company_type_id = 14 THEN 'Companies / LLPs wherein director either singly or jointly with other directors of lending company has 25%'
    ELSE 'any directors of lending company'
END as sub_company_name,sub_company_type_name,sub_company_type_id FROM gbca_sub_company_mapping WHERE sub_company_type_id IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15) AND  company_detail_id= '$company_edit_id'";
      
		  $section = $this->conn->query($section_arr)->fetch_all(MYSQLI_ASSOC);
  
    $transaction_arr = "SELECT CASE
    WHEN sub_company_type_id = 16 THEN 'Given Loan to'
    WHEN sub_company_type_id = 17 THEN 'Loan Represented as Book debt'
    WHEN sub_company_type_id = 18 THEN 'Guarantee Given in respect of Loan taken by'
    ELSE 'Security Provided to'
END as sub_company_name,sub_company_type_name,sub_company_type_id,sub_company_transaction_relation FROM gbca_sub_company_mapping WHERE sub_company_type_id IN (16,17,18,19) AND  company_detail_id= '$company_edit_id'";
      
		  $transaction = $this->conn->query($transaction_arr)->fetch_all(MYSQLI_ASSOC);
         
			
			$response['n'] = 1;
			$response['msg'] = "Share Added Successfully";
			$response['id']=$this->conn->insert_id;
            $response['trans'] =2; 
            $response['data'] =$section;
             $response['fourth'] =$transaction;
            // $response['data'] =$_SESSION["transaction"] = 3;

           

		}
		else{
			$response['n'] = 0;
			$response['msg'] = $this->conn->error;
		}
	}
	else
	{
		$response['n'] = 0;
		$response['msg'] = $this->conn->error;
	}
	return $response;
  }
  else
  {
    		$response = array();
		$query = "INSERT INTO gbca_sub_company_mapping(company_detail_id,
		sub_company_type_id,
		sub_company_type_name,
		sub_company_transaction_relation,
		sub_company_step,
		sub_company_added_date,
		sub_company_status
	) VALUES (?,?,?,?,?,CONVERT_TZ(NOW(),@@session.time_zone,'+5:30'),'0')";

 
	if($stmt_query = $this->conn->prepare($query)){
		$stmt_query->bind_param("iisss",$company_detail_id,$sub_company_type_id,$sub_company_name,$sub_company_transaction_relation,$sub_company_step);
      
		if($stmt_query->execute()){
		  $section_arr = "SELECT CASE
    WHEN sub_company_type_id = 1 THEN 'Holding Companies'
    WHEN sub_company_type_id = 2 THEN 'Subsidiary Companies'
    WHEN sub_company_type_id = 3 THEN 'Wholly Owned Subidiary Companies'
    WHEN sub_company_type_id = 4 THEN 'Managing Director'
    WHEN sub_company_type_id = 5 THEN 'Whole time directors'
    WHEN sub_company_type_id = 6 THEN 'Director of an Company'
    WHEN sub_company_type_id = 7 THEN 'Relative of directors'
    WHEN sub_company_type_id = 8 THEN 'Partner of directors in any firm (incl. LLP)'
     WHEN sub_company_type_id = 9 THEN 'Firm (incl. LLP) in which director is a partner'
    WHEN sub_company_type_id = 10 THEN 'Firm (incl. LLP) in which relative of director is a partner'
    WHEN sub_company_type_id = 11 THEN 'Private Companies in which director is a director'
    WHEN sub_company_type_id = 12 THEN 'Private Companies in which director is a Member'
    WHEN sub_company_type_id = 13 THEN 'Director of holding company'
    WHEN sub_company_type_id = 14 THEN 'Companies / LLPs wherein director either singly or jointly with other directors of lending company has 25%'
    WHEN sub_company_type_id = 15 THEN 'any directors of lending company'
    WHEN sub_company_type_id = 16 THEN 'Given Loan to'
    WHEN sub_company_type_id = 17 THEN 'Loan Represented as Book debt'
    WHEN sub_company_type_id = 18 THEN 'Guarantee Given in respect of Loan taken by'
    ELSE 'Security Provided to'
END as sub_company_name,sub_company_id,sub_company_type_name,sub_company_type_id FROM gbca_sub_company_mapping WHERE company_detail_id= '$company_detail_id'";
      
		  $section = $this->conn->query($section_arr)->fetch_all(MYSQLI_ASSOC);
         
         
			$response['n'] = 1;
			$response['msg'] = "Share Added Successfully";
            $response['trans'] =2; 
            $response['data'] =$section;
            $response['sub_id']=$holding1;
            
            // $response['data'] =$_SESSION["transaction"] = 3;

           

		}
		else{
			$response['n'] = 0;
			$response['msg'] = $this->conn->error;
		}
	}
	else
	{
		$response['n'] = 0;
		$response['msg'] = $this->conn->error;
	}
	return $response;  
  }
}

public function set_proceed($section,$company,$maker,$checker,$preparation_date)
{

		$response = array();
		$query = "INSERT INTO gbca_company_detail_master(section_id,
		company_detail_name,
		company_detail_maker_name,
	    company_detail_checker_name,
	    company_detail_preparation_date,
		company_details_added_date,
		company_detail_status
	) VALUES (?,?,?,?,?,CONVERT_TZ(NOW(),@@session.time_zone,'+5:30'),'0')";

	if($stmt_query = $this->conn->prepare($query)){
		$stmt_query->bind_param("issss",$section,$company,$maker,$checker,$preparation_date);
     
		if($stmt_query->execute()){
		  
			$response['n'] = 1;
			$response['msg'] = "Enter Section Successfully";
			$response['id']=$this->conn->insert_id;
          
		}
		else{
			$response['n'] = 0;
			$response['msg'] = $this->conn->error;
		}
	}
	else
	{
		$response['n'] = 0;
		$response['msg'] = $this->conn->error;
	}
	return $response;
}


public function store_pdf($company_detail_id,$company_edit_id,$document){

if($company_edit_id != '')
{
    
  	$response = array();
		$query = "update gbca_company_detail_master set
		company_detail_document=?,
		 company_detail_status='1' where company_detail_id=?";


	if($stmt_query = $this->conn->prepare($query)){
		$stmt_query->bind_param("si",$document,$company_edit_id);
     
		if($stmt_query->execute()){
			$response['n'] = 1;
			$response['msg'] = "document  Added Successfully";
			// $response['id']=$this->conn->insert_id;
      

		}
		else{
			$response['n'] = 0;
			$response['msg'] = $this->conn->error;
		}
	}
	else
	{
		$response['n'] = 0;
		$response['msg'] = $this->conn->error;
	}
	return $response;
  
}
else
{
  
     	$response = array();
		$query = "update gbca_company_detail_master set
		company_detail_document=?,
		 company_detail_status='1' where company_detail_id=?";


	if($stmt_query = $this->conn->prepare($query)){
		$stmt_query->bind_param("si",$document,$company_detail_id);
    
		if($stmt_query->execute()){
			$response['n'] = 1;
			$response['msg'] = "document  Added Successfully";
			// $response['id']=$this->conn->insert_id;
      

		}
		else{
			$response['n'] = 0;
			$response['msg'] = $this->conn->error;
		}
	}
	else
	{
		$response['n'] = 0;
		$response['msg'] = $this->conn->error;
	}
	return $response;
}  
}


public function set_transaction($company_detail_id,$company_edit_id,$sub_company_type_id,$sub_company_name,$sub_company_transaction_relation,$sub_company_step){

   if($company_edit_id != '')
  {
		$response = array();
	    $query = "INSERT INTO gbca_sub_company_mapping(company_detail_id,
		sub_company_type_id,
		sub_company_type_name,
		sub_company_transaction_relation,
		sub_company_step,
		sub_company_added_date,
		sub_company_status
	) VALUES (?,?,?,?,?,CONVERT_TZ(NOW(),@@session.time_zone,'+5:30'),'1')";


		if($stmt_query = $this->conn->prepare($query)){
		$stmt_query->bind_param("iissi",$company_edit_id,$sub_company_type_id,$sub_company_name,$sub_company_transaction_relation,$sub_company_step);
		
		if($stmt_query->execute()){
		    
		   
			$response['n'] = 1;
			$response['msg'] = "transaction Added Successfully";
			$response['success'] =3;
            
           
		}
		else{
			$response['n'] = 0;
			$response['msg'] = $this->conn->error;
		}
	}
	else
	{
		$response['n'] = 0;
		$response['msg'] = $this->conn->error;
	}
	return $response;
  }
  else
  {
    	$response = array();
		$query = "INSERT INTO gbca_sub_company_mapping(company_detail_id,
		sub_company_type_id,
		sub_company_type_name,
		sub_company_transaction_relation,
		sub_company_step,
		sub_company_added_date,
		sub_company_status
	) VALUES (?,?,?,?,?,CONVERT_TZ(NOW(),@@session.time_zone,'+5:30'),'1')";


	if($stmt_query = $this->conn->prepare($query)){
		$stmt_query->bind_param("iissi",$company_detail_id,$sub_company_type_id,$sub_company_name,$sub_company_transaction_relation,$sub_company_step);
     
		if($stmt_query->execute()){
		    
		   
			$response['n'] = 1;
			$response['msg'] = "transaction Added Successfully";
			$response['success'] =3;
            
           
		}
		else{
			$response['n'] = 0;
			$response['msg'] = $this->conn->error;
		}
	}
	else
	{
		$response['n'] = 0;
		$response['msg'] = $this->conn->error;
	}
	return $response;  
  }
}

public function delete_restore_185($company_detail_id,$company_status){
		$response = array(); 
		if($company_detail_id != ''){
			$query = "UPDATE gbca_company_detail_master SET company_detail_status=? ,company_detail_updated_date=CONVERT_TZ(NOW(), @@session.time_zone, '+5:30') WHERE company_detail_id=?";
			if($stmt_query= $this->conn->prepare($query)){
				$stmt_query->bind_param("si",$company_status,$company_detail_id);
				if($stmt_query->execute()){

			$query1 = "UPDATE gbca_sub_company_mapping  SET sub_company_status=?,sub_company_updated_date=CONVERT_TZ(NOW(), @@session.time_zone, '+5:30') WHERE company_detail_id=?";

			if($stmt_query1= $this->conn->prepare($query1)){
				$stmt_query1->bind_param("si",$company_status,$company_detail_id);
				if($stmt_query1->execute()){
					$response['n'] = 1;
					$response['msg'] = "Success.";
				}
				else{
					$response['n'] = 0;
					$response['msg'] = $this->conn->error;
				}
			}
			else{
				$response['n'] = 0;
				$response['msg'] = $this->conn->error;
			}
					$response['n'] = 1;
					$response['msg'] = "Success.";
				}
				else{
					$response['n'] = 0;
					$response['msg'] = $this->conn->error;
				}
			}
			else{
				$response['n'] = 0;
				$response['msg'] = $this->conn->error;
			}
		}
		else{
			$response['n'] = 0;
			$response['msg'] = "Admission id not found";
		}
		return $response;
	}

public function delete_record($company_detail_id,$company_edit_id){
		$response = array(); 
		
		if($company_edit_id != ''){
		    
		
		$query_arr = "DELETE  FROM gbca_sub_company_mapping WHERE company_detail_id=$company_edit_id AND sub_company_type_id IN(0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19)";
			
	
			  $section = $this->conn->query($query_arr);
      
				if($section == 1){
					$response['n'] = 1;
					$response['msg'] = "Success.";
				}
				else{
					$response['n'] = 0;
					$response['msg'] = $this->conn->error;
				}
		
			
			
		
		}
		else
		{
		    	$query_arr = "DELETE  FROM gbca_sub_company_mapping WHERE company_detail_id=$company_detail_id AND sub_company_type_id IN(0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19)";
			
	
			  $section = $this->conn->query($query_arr);
      
				if($section == 1){
					$response['n'] = 1;
					$response['msg'] = "Success.";
				}
				else{
					$response['n'] = 0;
					$response['msg'] = $this->conn->error;
				}
		    
		}

		return $response;
	}
public function delete_record1($company_edit_id){
		$response = array(); 
		
		if($company_edit_id != ''){
		    
		
		$query_arr = "DELETE FROM gbca_sub_company_mapping WHERE company_detail_id=$company_edit_id AND sub_company_type_id IN(16,17,18,19)";
			
		
			  $section = $this->conn->query($query_arr);
      
				if($section == 1){
					$response['n'] = 1;
					$response['msg'] = "Success.";
				}
				else{
					$response['n'] = 0;
					$response['msg'] = $this->conn->error;
				}
		
		}
		else{
			$response['n'] = 0;
			$response['msg'] = "delete error ";
		}
		return $response;
	}
	
public function set_section_22e($company_detail_id,$company_edit_id,$type_company,$financ_year,$other,$cut_off,$substantial_interest){

if($company_edit_id != '')
{
  	$response = array();
		$query = "update gbca_company_detail_master set 
			company_type_name=?,
			company_detail_financial_year=?,
			company_detail_cutoff_date=?,
			company_detail_substantially_interested=?,
			company_detail_other_company_name=?
			 where company_detail_id=?";


	if($stmt_query = $this->conn->prepare($query)){
		$stmt_query->bind_param("sssssi",$type_company,$financ_year,$cut_off,$substantial_interest,$other,$company_edit_id);
     
		if($stmt_query->execute()){

		$fetch_second_step = "SELECT gbca_company_shareholding_company_id as company_detail_id,gbca_company_shareholding_name as Name_Of_Shareholder, gbca_company_shareholding_percentage as Shareholding_In_Percentage,gbca_company_shareholding_list_of_concern as List_Of_Concern, gbca_company_shareholding_type_of_concern as Type_Of_Concern  from gbca_company_shareholding_master where gbca_company_shareholding_excel_type= '1' and gbca_company_shareholding_company_id = '$company_edit_id' and gbca_company_shareholding_status='1'";
            
		  $second_step = $this->conn->query($fetch_second_step)->fetch_all(MYSQLI_ASSOC);
			$response['n'] = 1;
			$response['msg'] = "basic  Added Successfully";
			$response['id']=$this->conn->insert_id;
			$response['second_step'] = $second_step;
      

		}
		else{
			$response['n'] = 0;
			$response['msg'] = $this->conn->error;
		}
	}
	else
	{
		$response['n'] = 0;
		$response['msg'] = $this->conn->error;
	}
	return $response;   
}
else
{
    	$response = array();
		$query = "update gbca_company_detail_master set 
			company_type_name=?,
			company_detail_financial_year=?,
			company_detail_cutoff_date=?,
			company_detail_substantially_interested=?,
		    company_detail_other_company_name=?,
		    
			company_detail_status='0' where company_detail_id=?";


	if($stmt_query = $this->conn->prepare($query)){
		$stmt_query->bind_param("sssssi",$type_company,$financ_year,$cut_off,$substantial_interest,$other,$company_detail_id);
     
		if($stmt_query->execute()){
		    
		  
		  
		  ///print_r($date);
			$response['n'] = 1;
			$response['msg'] = "basic  Added Successfully";
		    $response['basic'] =5;
        

		}
		else{
			$response['n'] = 0;
			$response['msg'] = $this->conn->error;
		}
	}
	else
	{
		$response['n'] = 0;
		$response['msg'] = $this->conn->error;
	}
	return $response;
}
	
}


public function set_section_22e1($company_detail_id,$company_edit_id,$type_company,$financ_year,$other,$cut_off,$substantial_interest){

if($company_edit_id != '')
{
  	$response = array();
		$query = "update gbca_company_detail_master set 
			company_type_name=?,
			company_detail_financial_year=?,
			company_detail_cutoff_date=?,
			company_detail_substantially_interested=?,
			company_detail_other_company_name=?,
			company_detail_status='1'
			 where company_detail_id=?";


	if($stmt_query = $this->conn->prepare($query)){
		$stmt_query->bind_param("sssssi",$type_company,$financ_year,$cut_off,$substantial_interest,$other,$company_edit_id);
     
		if($stmt_query->execute()){
           
	
			$response['n'] = 1;
			$response['msg'] = "Basic Added Successfully";
			$response['success'] = 3;
      

		}
		else{
			$response['n'] = 0;
			$response['msg'] = $this->conn->error;
		}
	}
	else
	{
		$response['n'] = 0;
		$response['msg'] = $this->conn->error;
	}
	return $response;   
}
else
{
    	$response = array();
		$query = "update gbca_company_detail_master set 
			company_type_name=?,
			company_detail_financial_year=?,
			company_detail_cutoff_date=?,
			company_detail_substantially_interested=?,
		    company_detail_other_company_name=?,
			company_detail_status='1' where company_detail_id=?";


	if($stmt_query = $this->conn->prepare($query)){
		$stmt_query->bind_param("sssssi",$type_company,$financ_year,$cut_off,$substantial_interest,$other,$company_detail_id);
     
		if($stmt_query->execute()){
		    
		  
		  
		  ///print_r($date);
			$response['n'] = 1;
			$response['msg'] = "basic  Added Successfully";
		    $response['basic'] =5;
		    $response['success'] = 3;
        

		}
		else{
			$response['n'] = 0;
			$response['msg'] = $this->conn->error;
		}
	}
	else
	{
		$response['n'] = 0;
		$response['msg'] = $this->conn->error;
	}
	return $response;
}
	
}




public function set_shareholders($company_detail_id,$company_edit_id,$name_of_shareholder,$shareholding_per)
{
    if($company_edit_id != '')
    {       
            $response = array();
            $query1 = "select * from gbca_company_shareholding_master where 
 			(gbca_company_shareholding_excel_type='1' or gbca_company_shareholding_excel_type='2') and 
 			gbca_company_shareholding_name='$name_of_shareholder' and
 			gbca_company_shareholding_company_id='$company_edit_id'";
			
			 $old_shareholders = $this->conn->query($query1)->fetch_all(MYSQLI_ASSOC);
           
                
    		 
        	  if(!empty($old_shareholders))
        	  {
        	      
	            $fetch_third_step = "SELECT gbca_company_shareholding_name as Name_Of_Shareholder, gbca_company_shareholding_percentage as Shareholding_per,gbca_company_shareholding_list_of_concern as Name_Of_Concern, gbca_company_shareholding_type_of_concern as Type_Of_Concern,gbca_company_shareholding_entity_type as record_type  from gbca_company_shareholding_master where
gbca_company_shareholding_excel_type= '2' and gbca_company_shareholding_company_id = $company_edit_id
union
SELECT gbca_company_shareholding_name as Name_Of_Shareholder, gbca_company_shareholding_percentage as Shareholding_per,gbca_company_shareholding_list_of_concern as Name_Of_Concern, gbca_company_shareholding_type_of_concern as Type_Of_Concern,gbca_company_shareholding_entity_type as record_type   from gbca_company_shareholding_master where
        			((gbca_company_shareholding_percentage >= 10 and gbca_company_shareholding_excel_type='1'))and gbca_company_shareholding_company_id = $company_edit_id and gbca_company_shareholding_status='1'
 and gbca_company_shareholding_name not in( SELECT gbca_company_shareholding_name from gbca_company_shareholding_master where
gbca_company_shareholding_excel_type= '2' and gbca_company_shareholding_company_id = $company_edit_id )";
            
		    $third_step = $this->conn->query($fetch_third_step)->fetch_all(MYSQLI_ASSOC);
		  
	    	$response['n'] = 1;
			$response['msg'] = "Share Added Successfully";
			$response['third_step']=$third_step;
			
        	  }
     
            else
            {
                
    		$query = "INSERT INTO gbca_company_shareholding_master(
     		gbca_company_shareholding_company_id,
     		gbca_company_shareholding_excel_type,
     		gbca_company_shareholding_entity_type,
    		gbca_company_shareholding_name,
    		gbca_company_shareholding_percentage,
     		gbca_company_shareholding_added_date,
     		gbca_company_shareholding_status
     	) VALUES (?,'1','2',?,?,CONVERT_TZ(NOW(),@@session.time_zone,'+5:30'),'1')";
        
    	if($stmt_query1 = $this->conn->prepare($query)){
    		$stmt_query1->bind_param("isi",$company_edit_id,$name_of_shareholder,$shareholding_per);
        
        		if($stmt_query1->execute()){
        
        			$fetch_third_step = "SELECT gbca_company_shareholding_name as Name_Of_Shareholder, null as Shareholding_per,gbca_company_shareholding_list_of_concern as Name_Of_Concern, gbca_company_shareholding_type_of_concern as Type_Of_Concern,gbca_company_shareholding_entity_type as record_type   from gbca_company_shareholding_master where
gbca_company_shareholding_excel_type= '2' and gbca_company_shareholding_company_id = $company_edit_id
union
SELECT gbca_company_shareholding_name as Name_Of_Shareholder, null as Shareholding_per,gbca_company_shareholding_list_of_concern as Name_Of_Concern, gbca_company_shareholding_type_of_concern as Type_Of_Concern,gbca_company_shareholding_entity_type as record_type   from gbca_company_shareholding_master where
        			((gbca_company_shareholding_percentage >= 10 and gbca_company_shareholding_excel_type='1'))and gbca_company_shareholding_company_id = $company_edit_id and gbca_company_shareholding_status='1'
 and gbca_company_shareholding_name not in( SELECT gbca_company_shareholding_name from gbca_company_shareholding_master where
gbca_company_shareholding_excel_type= '2' and gbca_company_shareholding_company_id = $company_edit_id )";
                    
        		  $third_step = $this->conn->query($fetch_third_step)->fetch_all(MYSQLI_ASSOC);
        
        
        
        			$response['n'] = 1;
        			$response['msg'] = "Share Added Successfully";
        			$response['third_step']=$third_step;
              
        
        		}
        		else{
        			$response['n'] = 0;
        			$response['msg'] = $this->conn->error;
        		}
    	}
    
    	else
    	{
    		$response['n'] = 0;
    		$response['msg'] = $this->conn->error;
    	}
       }

    	return $response;  
     	
    }
else
{
        
		$response = array();
		$query = "INSERT INTO gbca_company_shareholding_master(
		gbca_company_shareholding_company_id,
		gbca_company_shareholding_excel_type,
		gbca_company_shareholding_entity_type,
		gbca_company_shareholding_name,
		gbca_company_shareholding_percentage,
		gbca_company_shareholding_added_date,
		gbca_company_shareholding_status
	) VALUES (?,'1','2',?,?,CONVERT_TZ(NOW(),@@session.time_zone,'+5:30'),'1')";

   
	if($stmt_query = $this->conn->prepare($query)){

		$stmt_query->bind_param("isi",$company_detail_id,$name_of_shareholder,$shareholding_per);
		if($stmt_query->execute()){
		    
        

			$fetch_third_step = "SELECT gbca_company_shareholding_name as Name_Of_Shareholder, null as Shareholding_per,gbca_company_shareholding_list_of_concern as List_Of_Concern, gbca_company_shareholding_type_of_concern as Type_Of_Concern,gbca_company_shareholding_entity_type as record_type  from gbca_company_shareholding_master where
			((gbca_company_shareholding_percentage >= 10 and gbca_company_shareholding_excel_type='1') Or (gbca_company_shareholding_excel_type= '2'  )) and gbca_company_shareholding_company_id = '$company_detail_id' and gbca_company_shareholding_status='1'";
            
		  $third_step = $this->conn->query($fetch_third_step)->fetch_all(MYSQLI_ASSOC);
           
			$response['n'] = 1;
			$response['msg'] = "Shareholders Added Successfully";
			$response['id'] = $this->conn->insert_id;
      		$response['third_step'] = $third_step;  

		}
		else{
			$response['n'] = 0;
			$response['msg'] = "Shareholders Added error";
		}
	}
	else 
	{
		$response['n'] = 0;
		$response['msg'] = $this->conn->error;
	}
	return $response;
}
}



// public function set_shareholders($company_detail_id,$company_edit_id,$name_of_shareholder,$shareholding_per)
// {
//     if($company_edit_id != '')
            
//     {       
//             $response = array();
            
//             $query1 = "select * from gbca_company_shareholding_master where 
// 			(gbca_company_shareholding_excel_type='1' or gbca_company_shareholding_excel_type='2') and 
// 			gbca_company_shareholding_name='$name_of_shareholder' and
// 			gbca_company_shareholding_company_id='$company_edit_id'";
			
// 			 $old_shareholders = $this->conn->query($query1)->fetch_all(MYSQLI_ASSOC);
                
    		 
//         	  if(!empty($old_shareholders))
//         	  {
        	      
// 	            $fetch_third_step = "SELECT gbca_company_shareholding_name as Name_Of_Shareholder,gbca_company_shareholding_percentage as Shareholding_per,gbca_company_shareholding_list_of_concern as List_Of_Concern, gbca_company_shareholding_type_of_concern as Type_Of_Concern  from gbca_company_shareholding_master where
//         			(gbca_company_shareholding_percentage >= 10  or gbca_company_shareholding_excel_type= '2')and gbca_company_shareholding_company_id = '$company_edit_id'";
            
// 		    $third_step = $this->conn->query($fetch_third_step)->fetch_all(MYSQLI_ASSOC);
		    
		   
		  
// 	    	$response['n'] = 1;
// 			$response['msg'] = "Share Added Successfully";
// 			$response['third_step']=$third_step;
			
//         	  }
     
//             else
//             {
                
//     		$query = "INSERT INTO gbca_company_shareholding_master(
//      		gbca_company_shareholding_company_id,
//      		gbca_company_shareholding_excel_type,
//     		gbca_company_shareholding_name,
//     		gbca_company_shareholding_percentage,
//      		gbca_company_shareholding_added_date,
//      		gbca_company_shareholding_status
//      	) VALUES (?,'1',?,?,CONVERT_TZ(NOW(),@@session.time_zone,'+5:30'),'1')";
        
//     	if($stmt_query = $this->conn->prepare($query)){
//     		$stmt_query->bind_param("isi",$company_edit_id,$name_of_shareholder,$shareholding_per);
        
//         		if($stmt_query->execute()){
        
//         			$fetch_third_step = "SELECT gbca_company_shareholding_name as Name_Of_Shareholder, gbca_company_shareholding_percentage as Shareholding_per,gbca_company_shareholding_list_of_concern as List_Of_Concern, gbca_company_shareholding_type_of_concern as Type_Of_Concern  from gbca_company_shareholding_master where
//         			((gbca_company_shareholding_percentage >= 10 and gbca_company_shareholding_excel_type='1') Or (gbca_company_shareholding_excel_type= '2'  ))and gbca_company_shareholding_company_id = '$company_edit_id' and gbca_company_shareholding_status='1'";
                    
//         		  $third_step = $this->conn->query($fetch_third_step)->fetch_all(MYSQLI_ASSOC);
        
        
        
//         			$response['n'] = 1;
//         			$response['msg'] = "Share Added Successfully";
//         			$response['third_step']=$third_step;
              
        
//         		}
//         		else{
//         			$response['n'] = 0;
//         			$response['msg'] = $this->conn->error;
//         		}
//     	}
    
//     	else
//     	{
//     		$response['n'] = 0;
//     		$response['msg'] = $this->conn->error;
//     	}
//       }
// // 	}
//     	return $response;  
     	
//     }
// else
// {
        
// 		$response = array();
// 		$query = "INSERT INTO gbca_company_shareholding_master(
// 		gbca_company_shareholding_company_id,
// 		gbca_company_shareholding_excel_type,
// 		gbca_company_shareholding_name,
// 		gbca_company_shareholding_percentage,
// 		gbca_company_shareholding_added_date,
// 		gbca_company_shareholding_status
// 	) VALUES (?,'1',?,?,CONVERT_TZ(NOW(),@@session.time_zone,'+5:30'),'1')";


// 	if($stmt_query = $this->conn->prepare($query)){
// 		$stmt_query->bind_param("isi",$company_detail_id,$name_of_shareholder,$shareholding_per);
// 		print_r($stmt_query->execute()); exit;
// 		if($stmt_query->execute()){
          

// 			$fetch_third_step = "SELECT gbca_company_shareholding_name as Name_Of_Shareholder, gbca_company_shareholding_percentage as Shareholding_per,gbca_company_shareholding_list_of_concern as List_Of_Concern, gbca_company_shareholding_type_of_concern as Type_Of_Concern  from gbca_company_shareholding_master where
// 			((gbca_company_shareholding_percentage >= 10 and gbca_company_shareholding_excel_type='1') Or (gbca_company_shareholding_excel_type= '2'  )) and gbca_company_shareholding_company_id = '$company_detail_id' and gbca_company_shareholding_status='1'";
            
// 		  $third_step = $this->conn->query($fetch_third_step)->fetch_all(MYSQLI_ASSOC);
            
// 			$response['n'] = 1;
// 			$response['msg'] = "Shareholders Added Successfully";
// 			$response['id'] = $this->conn->insert_id;
//       		$response['third_step'] = $third_step;  

// 		}
// 		else{
// 			$response['n'] = 0;
// 			$response['msg'] = "Shareholders Added error";
// 		}
// 	}
// 	else
// 	{
// 		$response['n'] = 0;
// 		$response['msg'] = $this->conn->error;
// 	}
// 	return $response;
// }
// }

public function set_shareholders2($company_detail_id,$company_edit_id,$name_of_shareholder,$list_of_concern,$type_of_concern,$shareholding_per,$entity2){
if($company_edit_id != '')
{

         
  		$response = array();
		$query = "INSERT INTO gbca_company_shareholding_master(
		gbca_company_shareholding_company_id,
		gbca_company_shareholding_excel_type,
		gbca_company_shareholding_entity_type,
		gbca_company_shareholding_name,
		gbca_company_shareholding_list_of_concern,
		gbca_company_shareholding_type_of_concern,
		gbca_company_shareholding_percentage,
		gbca_company_shareholding_added_date,
		gbca_company_shareholding_status
	) VALUES (?,'2',?,?,?,?,?,CONVERT_TZ(NOW(),@@session.time_zone,'+5:30'),'1')";


	if($stmt_query = $this->conn->prepare($query)){
		$stmt_query->bind_param("iisssi",$company_edit_id,$entity2,$name_of_shareholder,$list_of_concern,$type_of_concern,$shareholding_per);
     
		if($stmt_query->execute()){

			//$type_of_concern = "SELECT * FROM gbca_company_shareholding_master WHERE gbca_company_shareholding_percentage >= '10' AND gbca_company_shareholding_company_id= '$company_edit_id' ";

 			//$type_of_con = $this->conn->query($type_of_concern)->fetch_all(MYSQLI_ASSOC);

			$response['n'] = 1;
			$response['msg'] = "Other Shareholders Added Successfully";
			$response['id'] = $this->conn->insert_id;
      		$response['success'] = 3;  

		}
		else{
			$response['n'] = 0;
			$response['msg'] = $this->conn->error;
		}
		}
	

	else
	{
		$response['n'] = 0;
		$response['msg'] = $this->conn->error;
	}
	return $response;   
}

else
{
		$response = array();
		$query = "INSERT INTO gbca_company_shareholding_master(
		gbca_company_shareholding_company_id,
		gbca_company_shareholding_excel_type,
		gbca_company_shareholding_entity_type,
		gbca_company_shareholding_name,
		gbca_company_shareholding_list_of_concern,
		gbca_company_shareholding_type_of_concern,
		gbca_company_shareholding_percentage,
		gbca_company_shareholding_added_date,
		gbca_company_shareholding_status
	) VALUES (?,'2',?,?,?,?,?,CONVERT_TZ(NOW(),@@session.time_zone,'+5:30'),'1')";


	if($stmt_query = $this->conn->prepare($query)){
		$stmt_query->bind_param("iisssi",$company_detail_id,$entity2,$name_of_shareholder,$list_of_concern,$type_of_concern,$shareholding_per);
     
		if($stmt_query->execute()){

			$response['n'] = 1;
			$response['msg'] = "Other Shareholders Added Successfully";
			$response['id'] = $this->conn->insert_id;
      		$response['success'] = 3;  

		}
		else{
			$response['n'] = 0;
			$response['msg'] = $this->conn->error;
		}
	}
	else
	{
		$response['n'] = 0;
		$response['msg'] = $this->conn->error;
	}
	return $response;
}
}

public function delete_shareholders($company_edit_id,$excel_type)
{

$response = array(); 
$query = "delete from gbca_company_shareholding_master where gbca_company_shareholding_company_id=? and gbca_company_shareholding_excel_type=?";
if($stmt_query= $this->conn->prepare($query)){
                $stmt_query->bind_param("ii",$company_edit_id,$excel_type);
               // print_r("hiii"); exit;
                if($stmt_query->execute()){
                    $response['n'] = 1;
                    $response['msg'] = "Success";
                }
                else{
                    $response['n'] = 0;
                    $response['msg'] = $this->conn->error;
                }
                return $response;
            }


}

public function delete_old_shareholders($company_edit_id,$name_of_shareholder,$shareholding_per,$excel_type)
{

$response = array(); 
$query = "delete from gbca_company_shareholding_master where
gbca_company_shareholding_name=? and gbca_company_shareholding_percentage=? and gbca_company_shareholding_company_id=? and gbca_company_shareholding_excel_type=?";
if($stmt_query= $this->conn->prepare($query)){
                $stmt_query->bind_param("siii",$name_of_shareholder,$shareholding_per,$company_edit_id,$excel_type);
               // print_r("hiii"); exit;
                if($stmt_query->execute()){
                    $response['n'] = 1;
                    $response['msg'] = "Success";
                }
                else{
                    $response['n'] = 0;
                    $response['msg'] = $this->conn->error;
                }
                return $response;
            }


}

public function delete_shareholders_on_add($company_detail_id,$excel_type)
{

$response = array(); 
$query = "delete from  gbca_company_shareholding_master where gbca_company_shareholding_company_id=? and gbca_company_shareholding_excel_type=?";
if($stmt_query= $this->conn->prepare($query)){
                $stmt_query->bind_param("ii",$company_detail_id,$excel_type);
                // print_r("hiii"); exit;
                if($stmt_query->execute()){
                    $response['n'] = 1;
                    $response['msg'] = "Success";
                }
                else{
                    $response['n'] = 0;
                    $response['msg'] = $this->conn->error;
                }
                return $response;
            }


}
public function check_shareholders($company_edit_id,$name_of_shareholder,$shareholding_per)
{

$response = array(); 
$query = "select * from gbca_company_shareholding_master where gbca_company_shareholding_name =? and gbca_company_shareholding_percentage=? and gbca_company_shareholding_company_id=? and gbca_company_shareholding_status=0";
if($stmt_query= $this->conn->prepare($query)){
                $stmt_query->bind_param("sii",$name_of_shareholder,$shareholding_per,$company_edit_id);
                // print_r("hiii"); exit;
                if($stmt_query->execute()){
                    $response['n'] = 1;
                    $response['msg'] = "Success";
                }
                else{
                    $response['n'] = 0;
                    $response['msg'] = $this->conn->error;
                }
                return $response;
            }


}

public function check_sharing($company_edit_id,$sub_company_name,$sub_company_step)
{

$response = array(); 
$query = "select * from gbca_sub_company_mapping where sub_company_type_name=? and (sub_company_type_id='16' OR sub_company_type_id='17' OR sub_company_type_id='18' OR sub_company_type_id='19' ) and sub_company_step=? and company_detail_id=?";
if($stmt_query= $this->conn->prepare($query)){
                $stmt_query->bind_param("sii",$sub_company_name,$sub_company_step,$company_edit_id);
                // print_r("hiii"); exit;
                if($stmt_query->execute()){
                    $response['n'] = 1;
                    $response['msg'] = "Success";
                }
                else{
                    $response['n'] = 0;
                    $response['msg'] = $this->conn->error;
                }
                return $response;
            }


}

public function delete_old_sharing($company_edit_id)
{
    
$response = array(); 
$query = "DELETE FROM `gbca_sub_company_mapping` WHERE sub_company_id IN (SELECT sub_company_id FROM `gbca_sub_company_mapping`  WHERE company_detail_id=$company_edit_id AND sub_company_step='4' AND  sub_company_type_name NOT IN (SELECT sub_company_type_name  FROM `gbca_sub_company_mapping`  WHERE sub_company_step='0' and company_detail_id=$company_edit_id)) ";

     $query_arr = $this->conn->query($query);
	if($query_arr == 1){
					$response['n'] = 1;
					$response['msg'] = "Success.";
				}
				else{
					$response['n'] = 0;
					$response['msg'] = $this->conn->error;
				}


}

public function delete_old_shareholders1($company_edit_id) 
{
    
$response = array(); 
$query = "DELETE FROM `gbca_company_shareholding_master` WHERE gbca_company_shareholding_company_id IN (SELECT gbca_company_shareholding_company_id FROM `gbca_company_shareholding_master`  WHERE gbca_company_shareholding_company_id=$company_edit_id AND gbca_company_shareholding_excel_type='3' AND  gbca_company_shareholding_name NOT IN (SELECT gbca_company_shareholding_name  FROM `gbca_company_shareholding_master`  WHERE gbca_company_shareholding_excel_type='2' and gbca_company_shareholding_company_id=$company_edit_id)) ";

     $query_arr = $this->conn->query($query);
	if($query_arr == 1){
					$response['n'] = 1;
					$response['msg'] = "Success.";
				}
				else{
					$response['n'] = 0;
					$response['msg'] = $this->conn->error;
				}


}

public function delete_record_22e($company_detail_id,$shareholding_name,$shareholding_per) 
{
    
$response = array(); 
$query = "DELETE FROM gbca_company_shareholding_master WHERE gbca_company_shareholding_company_id='$company_detail_id' AND (gbca_company_shareholding_excel_type='1' or gbca_company_shareholding_excel_type='2') AND  gbca_company_shareholding_name= '$shareholding_name' ";

     $query_arr = $this->conn->query($query);
	if($query_arr == 1){
					$response['n'] = 1;
					$response['msg'] = "Success.";
				}
				else{
					$response['n'] = 0;
					$response['msg'] = $this->conn->error;
				}


}

} 

$obj_dba_func = new db_function();
?>
