<?php
//user_action.php
include('db_con.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']))
{
	$dated = date('Y-m-d');
	if($_POST['btn_action']=='save')
	{

date_default_timezone_set('Asia/Karachi');
$curstamp = date('Y-m-d h:i:s a');
  
            $new_dated = $_POST['dated'];
             if($user_type=='user')
             {
            $sql = "select app_value from app_options where app_name='datedown' and user_id = '".$user_id."'";
            $statement = $connect->prepare($sql);
            $statement->execute(); // no need to add `$sql` here, you can take that out
            $datedown = $statement->fetchColumn();
            $sql = "select app_value from app_options where app_name='dateup' and user_id = '".$user_id."'";
            $statement = $connect->prepare($sql);
            $statement->execute(); // no need to add `$sql` here, you can take that out
            $dateup = $statement->fetchColumn();
             $sql = "SELECT date_sub(curdate(),interval '".$datedown."' day)";
              $statement = $connect->prepare($sql);
              $statement->execute(); // no need to add `$sql` here, you can take that out
              $pre_dated = $statement->fetchColumn();
    if($new_dated < $pre_dated){echo 'Invoice Can not be save in Previous date pre_dated is : '.$pre_dated .' Post Date : '.$new_dated ;return false;}
     $above_dated = Date('Y-m-d', strtotime("+".$dateup .' days'));
    if($new_dated > $above_dated){echo 'Invoice Can not be save in Above date Above dated is :'.$above_dated .' New Date is '. $new_dated;return false;}
        }
    $over_dues = $_POST['over_dues'];
    $pre_balance = $_POST['cur_balance'];
    $due_day = 0 . " day";
  
    $due_dated = Date('Y-m-d', strtotime($new_dated . "+" .$due_day));
   if($due_day==0){$due_dated=$new_dated;}
   if($due_day==''){$due_dated=$new_dated;}
    $statement = $connect->prepare("
      INSERT INTO sale 
        (po_no,bilty_no,chal_no,emp_id,title_id,dated,due_dated,inv_type,inv_of,gst,gst_pr,sm_id,branch_id,cust_id,cust_name,contact,description,discount, discount_dsc, due_day, freight, freight_dsc, pre_balance,over_dues,user_id,status,cur_stamp)
        VALUES (:po_no, :bilty_no, :chal_no, :emp_id, :title_id, :dated, :due_dated,  :inv_type, :inv_of, :gst,  :gst_pr, :sm_id, :branch_id, :cust_id, :cust_name, :contact, :description, :discount, :discount_dsc, :due_day, :freight, :freight_dsc, :pre_balance, :over_dues, :user_id, :status, :cur_stamp)
    ");
    $statement->execute(
      array(
          ':po_no'               =>  0,
          ':bilty_no'               =>  '',
          ':chal_no'             =>  0,
          ':emp_id'             =>  $_POST["emp_id"],
          ':title_id'             =>  $_POST["title_id"],
          ':dated'             =>  $_POST["dated"],
          ':due_dated'             =>  $due_dated,
          
          ':inv_type'             =>  'Credit',
          ':inv_of'             =>  'Wholesale',
           ':gst'             =>  'OFF',
           ':gst_pr'             =>  0,
          ':sm_id'             =>  1,
          ':branch_id'             =>  $branch_id,         
          ':cust_id'       =>  trim($_POST["cust_id"]),
          ':cust_name'       =>  'Credit Customer',
          ':contact'       =>  '',
          ':description'       =>  $_POST["description"],
          ':discount'       =>  trim($_POST["discount"]),
          ':discount_dsc'       =>  trim($_POST["discount_dsc"]),
          ':due_day'       =>  0,
          ':freight'       =>  trim($_POST["freight"]),
          ':freight_dsc'       =>  trim($_POST["freight_dsc"]),
           ':pre_balance'       =>  $pre_balance,
            ':over_dues'       =>  $over_dues,
          ':user_id'       =>  $user_id,
          ':status'       =>  'Active',
          ':cur_stamp'       =>  $curstamp
          
      )
    );
      $statement = $connect->query("SELECT LAST_INSERT_ID()");
      $s_id = $statement->fetchColumn();
      for($count=0; $count<$_POST["total_item"]; $count++)
      {
        
        $statement = $connect->prepare("
          INSERT INTO sale_detail 
          (s_id,item_no,store_id,ctn,qty,rate)
          VALUES (:s_id, :item_no, :store_id, :ctn, :qty, :rate)
        ");
        $statement->execute(
          array(
            ':s_id'               =>  $s_id,
            ':item_no'              =>  trim($_POST["item_no"][$count]),
            ':store_id'              => 1,
            ':ctn'          =>  1,
            ':qty'          =>  trim($_POST["qty"][$count]),
            ':rate'           =>  trim($_POST["rate"][$count])
            
                       
          )
        );
      }
 $sql = "update sale_detail d, items i set d.cost = i.item_cost where d.s_id = '".$s_id."' and i.item_id=d.item_no";
           $statement = $connect->prepare($sql);
           $statement->execute();
          
           
// create jv for sale invoice
    
          
            $sql = "SELECT sum(qty*rate) FROM sale_detail WHERE s_id = '".$s_id."' ";
            $statement = $connect->prepare($sql);
            $statement->execute(); // no need to add `$sql` here, you can take that out
            $sale = $statement->fetchColumn();       
           
                  
            $sql = "SELECT sum(qty*cost) FROM sale_detail WHERE s_id = '".$s_id."' ";
            $statement = $connect->prepare($sql);
            $statement->execute(); // no need to add `$sql` here, you can take that out
            $cost = $statement->fetchColumn();
             $sql = "SELECT gst_pr FROM sale WHERE s_id = '".$s_id."' ";
            $statement = $connect->prepare($sql);
            $statement->execute(); // no need to add `$sql` here, you can take that out
            $gst_pr = $statement->fetchColumn();
             $sql = "SELECT freight FROM sale WHERE s_id = '".$s_id."' ";
            $statement = $connect->prepare($sql);
            $statement->execute(); // no need to add `$sql` here, you can take that out
            $freight = $statement->fetchColumn();
           
           IF($gst_pr > 0)
           {
           $gst_amount = $sale * $gst_pr / 100;
         }else {$gst_amount = 0;}
            
            $sql = "SELECT discount FROM sale WHERE s_id = '".$s_id."' ";
            $statement = $connect->prepare($sql);
            $statement->execute(); // no need to add `$sql` here, you can take that out
            $discount = $statement->fetchColumn();
            
            if($discount > 0)
            {
              $sale = $sale - $discount;
            }
            $sql = "SELECT MAX(jv_no) FROM jv WHERE branch_id = '".$branch_id."' ";
            $statement = $connect->prepare($sql);
            $statement->execute(); // no need to add `$sql` here, you can take that out
            $jv_no = $statement->fetchColumn();
            settype($jv_no, "integer");
            $jv_no++;
           
            $source = 'AR Inv';
            $description = 'AR Credit Invoice ' .$s_id .',  ' .$_POST['description'];
           
            
            $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'             =>  $_POST['dated'],
          ':source'          =>  $source,
          ':ref_id'          =>  $s_id,
          ':description'       =>  $description,
       
          ':user_id'       =>  $user_id
      )
    );
     $statement = $connect->query("select max(jv_id) from jv");
      $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'              =>  5015001,
            ':debit'          =>  0,
            ':credit'           =>  $sale,
            
          )
        );
        
        
          $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'              =>  1041004,
            ':debit'          =>  0,
            ':credit'           =>  $gst_amount
                        
          )
        );
        
        
        $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'              =>  1041005,
            ':debit'          =>  0,
            ':credit'           =>  $freight
                        
          )
        );
        $Receivable = $sale + $gst_amount + $freight;
        $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'              =>  $_POST['cust_id'],
            ':debit'          =>  $Receivable,
            ':credit'           =>  0
                     
          )
        );
         $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'              =>  1031006,
            ':debit'          =>  0,
            ':credit'           =>  $cost
                        
          )
        );
       
        $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'              =>  3013001,
            ':debit'          =>  $cost,
            ':credit'           =>  0
                     
          )
        );
        /////End Jv/////
        
        echo 'Success';
    
	}

  if($_POST['btn_action']=='update')
  {
    $s_id = $_POST['s_id'];
    $ref_id = $s_id;
      $due_day = 0 . " day";
   
$new_dated = $_POST['dated'];
    $due_dated = Date('Y-m-d', strtotime($new_dated . "+" .$due_day));
   if($due_day==0){$due_dated=$new_dated;}
   if($due_day==''){$due_dated=$new_dated;}
      
     
      $statement = $connect->prepare("
                DELETE FROM sale_detail WHERE s_id = :s_id
            ");
            $statement->execute(
                array(
                    ':s_id'       =>      $s_id
                )
            );
      for($count=0; $count<$_POST["total_item"]; $count++)
      {
       $statement = $connect->prepare("
          INSERT INTO sale_detail 
          (s_id,item_no,store_id,ctn,qty,rate)
          VALUES (:s_id, :item_no, :store_id, :ctn, :qty, :rate)
        ");
        $statement->execute(
          array(
            ':s_id'               =>  $s_id,
            ':item_no'              =>  trim($_POST["item_no"][$count]),
            ':store_id'              =>  1,
            ':ctn'          =>  1,
            ':qty'          =>  trim($_POST["qty"][$count]),
            ':rate'           =>  trim($_POST["rate"][$count])
                       
          )
        );
        
        
        //$result = $statement->fetchAll();
      }
      $sql = "update sale_detail d, items i set d.cost = i.item_cost where d.s_id = '".$s_id."' and i.item_id=d.item_no";
           $statement = $connect->prepare($sql);
           $statement->execute();
      $statement = $connect->prepare("
        UPDATE sale set
        po_no = :po_no,
        bilty_no = :bilty_no,
        chal_no = :chal_no,
        emp_id = :emp_id,
        title_id = :title_id,
        dated = :dated,
        due_dated = :due_dated,
        
        inv_type = :inv_type,
        inv_of = :inv_of,
        sm_id = :sm_id,
        branch_id = :branch_id,
        cust_id = :cust_id,
        cust_name = :cust_name,
        contact = :contact,
        description = :description,
        gst = :gst,
        gst_pr = :gst_pr,
        discount = :discount,
        discount_dsc = :discount_dsc,  
        freight = :freight,
        freight_dsc = :freight_dsc,
        due_day = :due_day,
        pre_balance = :pre_balance,
        over_dues = :over_dues,        
            
        status = :status
        where s_id = :s_id         
      ");
      $statement->execute(
        array(
          ':po_no'             =>  $_POST["po_no"],
          ':bilty_no'             =>  '',
          ':chal_no'             =>  $s_id,
          ':emp_id'             =>  $_POST["emp_id"],
          ':title_id'             =>  $_POST["title_id"],
          ':dated'             =>  $_POST["dated"],          
          ':due_dated'             =>  $due_dated,          
          
          ':inv_type'             =>  'Credit',
          ':inv_of'          =>  'Wholesale',
          ':sm_id'       =>  1,
          ':branch_id'             =>  $branch_id,
          ':cust_id'             =>  trim($_POST["cust_id"]),          
          ':cust_name'             =>  'Wholesale Customer',
          ':contact'             =>  '',
          ':description'             =>  $_POST["description"],
             ':gst'             =>  $_POST["gst"],
          ':gst_pr'             =>  $_POST["gst_pr"],
          ':discount'             =>  trim($_POST["discount"]),  
          ':discount_dsc'             =>  trim($_POST["discount_dsc"]),  
          ':freight'             =>  trim($_POST["freight"]),  
          ':freight_dsc'             =>  trim($_POST["freight_dsc"]),  
          ':due_day'       =>  0,  
           ':pre_balance'       =>  trim($_POST["cur_balance"]),  
            ':over_dues'       =>  trim($_POST["over_dues"]),        
                 
          ':status'               =>  'Active',
          ':s_id'               =>  $s_id
        )
      );
       
        /////creating JV//////
// create jv for sale invoice
    
          
            $sql = "SELECT sum(qty*rate) FROM sale_detail WHERE s_id = '".$s_id."' ";
            $statement = $connect->prepare($sql);
            $statement->execute(); // no need to add `$sql` here, you can take that out
            $sale = $statement->fetchColumn();
              
            $sql = "SELECT sum(qty*cost) FROM sale_detail WHERE s_id = '".$s_id."' ";
            $statement = $connect->prepare($sql);
            $statement->execute(); // no need to add `$sql` here, you can take that out
            $cost = $statement->fetchColumn();
             $sql = "SELECT gst_pr FROM sale WHERE s_id = '".$s_id."' ";
            $statement = $connect->prepare($sql);
            $statement->execute(); // no need to add `$sql` here, you can take that out
            $gst_pr = $statement->fetchColumn();
             $sql = "SELECT freight FROM sale WHERE s_id = '".$s_id."' ";
            $statement = $connect->prepare($sql);
            $statement->execute(); // no need to add `$sql` here, you can take that out
            $freight = $statement->fetchColumn();
           
           if($gst_pr > 0)
           {
           $gst_amount = $sale * $gst_pr / 100;
         }else {$gst_amount = 0;}
            
        
            $sql = "SELECT discount FROM sale WHERE s_id = '".$s_id."' ";
            $statement = $connect->prepare($sql);
            $statement->execute(); // no need to add `$sql` here, you can take that out
            $discount = $statement->fetchColumn();
            
            if($discount > 0)
            {
              $sale = $sale - $discount;
            }
            $sql = "SELECT jv_id FROM jv WHERE source = 'AR Inv' and ref_id = '".$ref_id."' ";
            $statement = $connect->prepare($sql);
            $statement->execute(); // no need to add `$sql` here, you can take that out
            $jv_id = $statement->fetchColumn();
            $sql = "SELECT jv_no FROM jv WHERE jv_id = '".$jv_id."' ";
            $statement = $connect->prepare($sql);
            $statement->execute(); // no need to add `$sql` here, you can take that out
            $jv_no = $statement->fetchColumn();
            
               $sql = "SELECT user_id FROM jv WHERE jv_id = '".$jv_id."' ";
            $statement = $connect->prepare($sql);
            $statement->execute(); // no need to add `$sql` here, you can take that out
            $user_id = $statement->fetchColumn();
               $sql = "SELECT user_acc_no FROM user_detail WHERE user_id = '".$user_id."' ";
            $statement = $connect->prepare($sql);
            $statement->execute(); // no need to add `$sql` here, you can take that out
            $user_acc_no = $statement->fetchColumn();   
            
            
           
            $source = 'AR Inv';
            $description = 'AR Credit Invoice ' .$s_id .', ' .$_POST['description'] .' For ' .$_POST['cust_name'] ;
           
            
            $statement = $connect->prepare("
        UPDATE jv 
        SET 
        dated = :dated,
        branch_id = :branch_id, 
        source = :source,
        ref_id = :ref_id, 
        description = :description,
             
        user_id = :user_id,
        status = :status
        WHERE jv_id = :jv_id
      ");
            
      
      $statement->execute(
        array(
          ':dated'             =>  $_POST["dated"],
          ':branch_id'          =>  $branch_id,
          ':source'          =>  $source,
          ':ref_id'          =>  $s_id,
          ':description'       =>  $description,
                      
          ':user_id'       =>  $user_id,
          ':status'       =>  'Active',
          ':jv_id'               =>  $jv_id
        )
      );
        
        $sql = "DELETE FROM jv_detail WHERE jv_id = '".$jv_id."' ";
            $statement = $connect->prepare($sql);
            $statement->execute(); // no need to add `$sql` here, you can take that out
           
       $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'              =>  5015001,
            ':debit'          =>  0,
            ':credit'           =>  $sale,
            
          )
        );
        
        
          $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'              =>  1041004,
            ':debit'          =>  0,
            ':credit'           =>  $gst_amount
                        
          )
        );
        
        
        $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'              =>  1041005,
            ':debit'          =>  0,
            ':credit'           =>  $freight
                        
          )
        );
        $Receivable = $sale + $gst_amount + $freight;
        $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'              => $_POST['cust_id'],
            ':debit'          =>  $Receivable,
            ':credit'           =>  0
                     
          )
        );
         $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'              =>  1031006,
            ':debit'          =>  0,
            ':credit'           =>  $cost
                        
          )
        );
       
        $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'              =>  3013001,
            ':debit'          =>  $cost,
            ':credit'           =>  0
                     
          )
        );
        /////End Jv/////
        
        echo 'success';
  }	
	
	if($_POST['btn_action'] == 'delete')
	{
		$s_id = $_POST['s_id'];
			$sql = "SELECT do_id from do  WHERE s_id = '".$s_id."' ";
            $statement = $connect->prepare($sql);
            $statement->execute();
            $do_id = $statement->fetchColumn();
		$sql = "DELETE FROM sale WHERE s_id = '".$s_id."' ";
            $statement = $connect->prepare($sql);
            $statement->execute(); // no need to add `$sql` here, you can take that out
        
        $sql = "DELETE FROM sale_detail WHERE s_id = '".$s_id."' ";
            $statement = $connect->prepare($sql);
            $statement->execute(); // no 
		
		$sql = "SELECT jv_id FROM jv WHERE source = 'AR Inv' and ref_id = '".$s_id."' ";
            $statement = $connect->prepare($sql);
            $statement->execute(); // no need to add `$sql` here, you can take that out
            $jv_id = $statement->fetchColumn();
            
             $sql = "DELETE FROM jv WHERE jv_id = '".$jv_id."' ";
            $statement = $connect->prepare($sql);
            $statement->execute();
            $sql = "DELETE FROM jv_detail WHERE jv_id = '".$jv_id."' ";
            $statement = $connect->prepare($sql);
            $statement->execute();
            if($do_id > 0)
            {
            	 $sql = "DELETE FROM do_detail WHERE do_id = '".$do_id."' ";
		            $statement = $connect->prepare($sql);
		            $statement->execute();
		            $sql = "DELETE FROM do WHERE do_id = '".$do_id."' ";
		            $statement = $connect->prepare($sql);
		            $statement->execute();
            }
            
		
		//$result = $statement->fetchAll();	
		
	}
}
?>