<?php
//user_action.php
include('db_con.php');
include('function.php');
if (isset($_SESSION['user_id'])) {
		$user_type = $_SESSION['user_type'];
		$user_id = $_SESSION['user_id'];
		$user_name = $_SESSION['user_name'];
		$branch_id = $_SESSION['branch_id'];
		$branch_name = $_SESSION['branch_name'];
		$store_id = $_SESSION['store_id'];
    $store_name = $_SESSION['store_name'];
}else{
	header('Location: login');
	die();
}
if(isset($_POST['btn_action']))
{
	if($_POST['btn_action'] == 'Add')
	{
		$dated = date('Y-m-d');
		 $sql = "SELECT IFNULL(max(acc_no),0) FROM accounts where acc_type='DEBTOR'";
            $statement = $connect->prepare($sql);
            $statement->execute(); // no need to add `$sql` here, you can take that out
            $acc_no = $statement->fetchColumn();
            if($acc_no < 1048001)
            {
            	$acc_no = 1048001;
            }
            $acc_no++;
         
		$sql = "select acc_name from accounts where acc_name ='".$_POST['cust_name']."'";
		$statement = $connect->query($sql);
		$acc_name = $statement->fetchColumn();
		if($acc_name !== $_POST['cust_name'])
		{


		$query = "
		INSERT INTO accounts (acc_no,dated,acc_name,acc_type,acc_parent,address,area,city,country,phone,email,web,op_balance,sale_target,cr_limit,due_days,sm_id,user_id,status)
        VALUES (:acc_no, :dated, :acc_name, :acc_type, :acc_parent, :address, :area, :city, :country, :phone, :email, :web,  :op_balance, :sale_target, :cr_limit, :due_days, :sm_id, :user_id, :status)
    ";	
		$statement = $connect->prepare($query);
		$statement->execute(
			array(
          ':acc_no'             =>  $acc_no,
          ':dated'             =>  $dated,
          ':acc_name'          =>  trim($_POST["cust_name"]),
          ':acc_type'          =>  'DEBTOR',
          ':acc_parent'          =>  104,
          ':address'          =>  trim($_POST["address"]),  
           ':area'       =>  trim($_POST["area"]),        
          ':city'       =>  trim($_POST["city"]),
          ':country'       =>  'Pakistan',
          ':phone'       =>  trim($_POST["phone"]),
          ':email'       =>  trim($_POST["email"]),
          ':web'       =>  trim($_POST["web"]),
         ':op_balance'       =>  trim($_POST["op_balance"]),
           ':sale_target'       =>  trim($_POST["sale_target"]),
          ':cr_limit'       =>  trim($_POST["cr_limit"]),
          ':due_days'       =>  'due_days',
          ':sm_id'       =>  trim($_POST["sm_id"]), 
          ':user_id'       =>  $user_id,         
          ':status'       =>  trim($_POST["status"])
          )
		);
	
		$op_balance = $_POST['op_balance'];

		if($op_balance <> 0)
		{

			$source = 'Customer';
            $description = 'Opening Balance For ' .$_POST['cust_name'];
            $ref_id = $acc_no;

             $sql = "select IFNULL(max(jv_no),0) from jv where source = 'Customer'";
		$statement=$connect->prepare($sql);
		$statement->execute();
		$jv_no = $statement->fetchColumn();
		$jv_no++;
		$dated = '2021-01-01';

            
            $statement = $connect->prepare("
              INSERT INTO jv 
                (jv_no,branch_id,dated,source,ref_id,description,user_id)
                VALUES (:jv_no, :branch_id, :dated, :source, :ref_id, :description, :user_id)
    ");
    $statement->execute(
      array(
          ':jv_no'             =>  $jv_no,
          ':branch_id'             =>  $branch_id,
          ':dated'             =>  date('2022-01-01'),
          ':source'          =>  $source,
          ':ref_id'          =>  $ref_id,
          ':description'       =>  $description,
       
          ':user_id'       =>  $user_id
      )
    );
     $statement = $connect->query("SELECT LAST_INSERT_ID()");
      $jv_id = $statement->fetchColumn();
      
        $statement = $connect->prepare("
          INSERT INTO jv_detail 
          (jv_id,acc_no,debit,credit)
          VALUES(:jv_id, :acc_no, :debit, :credit)
        ");
        $statement->execute(
          array(
            ':jv_id'               =>  $jv_id,
            ':acc_no'              =>  1000000,
            ':debit'          =>  0,
            ':credit'           =>  $op_balance
            
          )
        );
        
        
          $statement = $connect->prepare("
          INSERT INTO jv_detail 
          (jv_id,acc_no,debit,credit)
          VALUES(:jv_id, :acc_no, :debit, :credit)
        ");
        $statement->execute(
          array(
            ':jv_id'               =>  $jv_id,
            ':acc_no'              =>  $acc_no,
            ':debit'          =>  $op_balance,
            ':credit'           =>  0
                        
          )
        );
		}
	}else{echo 'Name Already Exist!';return false;}
		
			echo 'New Customer Added';
		
	}
	if($_POST['btn_action'] == 'fetch_single')
	{
		$query = "
		SELECT * FROM accounts WHERE acc_no = :acc_no
		";
		$statement = $connect->prepare($query);
		$statement->execute(
			array(
				':acc_no'	=>	$_POST["acc_no"]
			)
		);
		$result = $statement->fetchAll();
		foreach($result as $row)
		{
			
			
			
          $output['acc_name']          =  $row['acc_name'];

          $output['address']          =  $row['address'];
          $output['area']       =  $row["area"];
          $output['city']       =  $row["city"];
         // $output['country']       =  $row["country"];
          $output['phone']       =  $row["phone"];
          $output['email']       =  $row["email"];
          $output['web']       =  $row["web"];
      		$output['op_balance']       =  $row["op_balance"];
          $output['sale_target']       =  $row["sale_target"];
          $output['cr_limit']       =  $row["cr_limit"];
          $output['due_days']       =  $row["due_days"];
          $output['sm_id']       =  $row["sm_id"];
          $output['status']       =  $row["status"];
          
		}
		echo json_encode($output);
	}
	if($_POST['btn_action'] == 'Edit')
	{
	
		$acc_no = $_POST['acc_no'];
		$ref_id = $acc_no;
			$query = "
			UPDATE accounts SET
		
				acc_name = '".$_POST["cust_name"]."',
				address = '".$_POST["address"]."', 
				area = '".$_POST["area"]."',
				city = '".$_POST["city"]."',
				country = 'Pakistan',
				phone = '".$_POST["phone"]."',
				email = '".$_POST["email"]."',
				web = '".$_POST["web"]."',
				op_balance = '".$_POST["op_balance"]."',
				sale_target = '".$_POST["sale_target"]."',
				cr_limit = '".$_POST["cr_limit"]."',
				due_days = '".$_POST["due_days"]."',
				sm_id = '".$_POST["sm_id"]."',
				user_id = '".$user_id."',				
				status = '".$_POST["status"]."'
				WHERE acc_no = '".$_POST["acc_no"]."'
			";
		
		$statement = $connect->prepare($query);
		$statement->execute();	

		$op_balance = $_POST['op_balance'];
		if($op_balance <> 0)
		{
			$sql = "select ifnull(jv_id,0) from jv where source = 'Customer' and ref_id = '".$_POST['acc_no']."'";
			$statement = $connect->query($sql);
			$jv_id = $statement->fetchColumn();

			if($jv_id > 0)
			{
					
						$dated = '2021-01-01';

            
           
   
      $sql = "delete from jv_detail where jv_id = '".$jv_id."'";
      $statement = $connect->query($sql);
        $statement = $connect->prepare("
          INSERT INTO jv_detail 
          (jv_id,acc_no,debit,credit)
          VALUES(:jv_id, :acc_no, :debit, :credit)
        ");
        $statement->execute(
          array(
            ':jv_id'               =>  $jv_id,
            ':acc_no'              =>  1000000,
            ':debit'          =>  0,
            ':credit'           =>  $op_balance
            
          )
        );
        
        
          $statement = $connect->prepare("
          INSERT INTO jv_detail 
          (jv_id,acc_no,debit,credit)
          VALUES(:jv_id, :acc_no, :debit, :credit)
        ");
        $statement->execute(
          array(
            ':jv_id'               =>  $jv_id,
            ':acc_no'              =>  $acc_no,
            ':debit'          =>  $op_balance,
            ':credit'           =>  0
                        
          )
        );
			}else
				{
					$source = 'Customer';
            $description = 'Opening Balance For ' .$_POST['cust_name'];
            $ref_id = $acc_no;

             $sql = "select IFNULL(max(jv_no),0) from jv where source = 'Customer'";
		$statement=$connect->prepare($sql);
		$statement->execute();
		$jv_no = $statement->fetchColumn();
		$jv_no++;
		$dated = '2021-01-01';

            
            $statement = $connect->prepare("
              INSERT INTO jv 
                (jv_no,branch_id,dated,source,ref_id,description,user_id)
                VALUES (:jv_no, :branch_id, :dated, :source, :ref_id, :description, :user_id)
    ");
    $statement->execute(
      array(
          ':jv_no'             =>  $jv_no,
          ':branch_id'             =>  $branch_id,
          ':dated'             =>  date('2022-01-01'),
          ':source'          =>  $source,
          ':ref_id'          =>  $ref_id,
          ':description'       =>  $description,
       
          ':user_id'       =>  $user_id
      )
    );
     $statement = $connect->query("SELECT LAST_INSERT_ID()");
      $jv_id = $statement->fetchColumn();
      
        $statement = $connect->prepare("
          INSERT INTO jv_detail 
          (jv_id,acc_no,debit,credit)
          VALUES(:jv_id, :acc_no, :debit, :credit)
        ");
        $statement->execute(
          array(
            ':jv_id'               =>  $jv_id,
            ':acc_no'              =>  1000000,
            ':debit'          =>  0,
            ':credit'           =>  $op_balance
            
          )
        );
        
        
          $statement = $connect->prepare("
          INSERT INTO jv_detail 
          (jv_id,acc_no,debit,credit)
          VALUES(:jv_id, :acc_no, :debit, :credit)
        ");
        $statement->execute(
          array(
            ':jv_id'               =>  $jv_id,
            ':acc_no'              =>  $acc_no,
            ':debit'          =>  $op_balance,
            ':credit'           =>  0
                        
          )
        );
				}
		}
		else 
		{
			$sql = "select jv_id from jv where source = 'Customer' and ref_id = '".$_POST['acc_no']."'";
			$statement = $connect->query($sql);
			$jv_id = $statement->fetchColumn();
			if($jv_id > 0)
			{
				$sql = "delete from jv where jv_id = '".$jv_id."'";
			$statement = $connect->query($sql);
				$sql = "delete from jv_detail where jv_id = '".$jv_id."'";
			$statement = $connect->query($sql);
			}

		}
		
		
	
		
			echo 'Customer Details Updated';
		
	}
	if($_POST['btn_action'] == 'delete')
	{
		$status = 'Inactive';
		if($_POST['status'] == 'Active')
		{
			$status = 'Inactive';
		}
	
	
		$query = "
		UPDATE accounts
		SET status = :status 
		WHERE acc_no = :acc_no
		";
		$statement = $connect->prepare($query);
		$statement->execute(
			array(
				':status'	=>	$status,
				':acc_no'		=>	$acc_no
			)
		);	
		$result = $statement->fetchAll();	
		if(isset($result))
		{
			echo 'Customer Status change to ' . $status;
		}
	}
}
?>