<?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')
	{
$new_dated = $_POST['dated'];
$gst_pr = $_POST['gst_pr'];
if($gst_pr > 0){$gst = 'ON';}else{$gst='OFF';}
             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;}
        }
    $due_day = 0;
   
    $due_date =Date('Y-m-d', strtotime("+".$due_day." days"));
   if($due_day==0){$due_date=$new_dated;}
   if($due_day == ''){$due_date=$new_dated;}
    
    $statement = $connect->prepare("
     INSERT INTO purchase
        (po_no,branch_id,inv_no,dated,due_date,v_id,sv_id,description,discount,discount_dsc,inv_amt,due_day,freight,freight_dsc,gst,gst_pr,fst_pr,ast_pr,user_id,status)
        VALUES (:po_no, :branch_id, :inv_no, :dated, :due_date, :v_id, :sv_id, :description, :discount, :discount_dsc, :inv_amt, :due_day, :freight, :freight_dsc, :gst, :gst_pr, :fst_pr, :ast_pr, :user_id, :status)
    ");
    $statement->execute(
      array(
         ':po_no'             =>  0,
          ':branch_id'             =>  1,
          ':inv_no'             =>  trim($_POST["inv_no"]),
          ':dated'             =>  $_POST["dated"],
       ':due_date'             =>  $due_date,
          ':v_id'          =>  trim($_POST["v_id"]),
          ':sv_id'          =>  0,
          ':description'       =>  $_POST["description"],
          ':discount'       =>  trim($_POST["discount"]),
          ':discount_dsc'       =>  trim($_POST["discount_dsc"]),
          ':inv_amt'       =>  0,
          ':due_day'       =>  0,          
          ':freight'       =>  trim($_POST["freight"]),
          ':freight_dsc'       =>  trim($_POST["freight_dsc"]),
          ':gst'       =>  $gst,
          ':gst_pr'       =>  trim($_POST["gst_pr"]),
          ':fst_pr'       =>  trim($_POST["fst_pr"]),
          ':ast_pr'       =>  trim($_POST["ast_pr"]),
          ':user_id'       =>  $user_id,          
          ':status'       =>  'Active'
          
      )
    );
      
        $statement = $connect->query("SELECT LAST_INSERT_ID()");
         $p_id = $statement->fetchColumn();
          $sql = "delete from p_detail";
          $statement = $connect->prepare($sql);
          $statement->execute();
      for($count=0; $count<$_POST["total_item"]; $count++)
      {
        $statement = $connect->prepare("
          INSERT INTO purchase_detail
          (p_id,item_no,store_id,ctn,qty,rate, dis_pr, ln_no)
          VALUES(:p_id, :item_no, :store_id, :ctn, :qty, :rate, :dis_pr, :ln_no)
        ");
        $statement->execute(
          array(
            ':p_id'               =>  $p_id,            
            ':item_no'              =>  trim($_POST["item_no"][$count]),
            ':store_id'              =>  1,            
            ':ctn'          =>  1,
            ':qty'          =>  trim($_POST["qty"][$count]),
            ':rate'           =>  trim($_POST["rate"][$count]),
            ':dis_pr'           =>  trim($_POST["dis_pr"][$count]),
            ':ln_no'        => $count
        
          )
        );
      }    
        $sql ="
          select sum(qty*rate) from purchase_detail where p_id = '".$p_id."'
          
          ";
        $statement = $connect->prepare($sql);
        $statement->execute();
        $inv_amt = $statement->fetchColumn();     
      $sql = "update purchase set inv_amt = '".$inv_amt."' where p_id = '".$p_id."'";
          $statement = $connect->prepare($sql);
          $statement->execute();
      
          $sql = "select discount from purchase where p_id = '".$p_id."'";
          $statement = $connect->prepare($sql);
          $statement->execute();
          $discount = $statement->fetchColumn();
                       $sql = "SELECT gst_pr FROM purchase WHERE p_id = '".$p_id."' ";
            $statement = $connect->prepare($sql);
            $statement->execute(); 
            $gst_pr = $statement->fetchColumn();
                         $sql = "SELECT fst_pr FROM purchase WHERE p_id = '".$p_id."' ";
            $statement = $connect->prepare($sql);
            $statement->execute(); 
            $fst_pr = $statement->fetchColumn();
                         $sql = "SELECT ast_pr FROM purchase WHERE p_id = '".$p_id."' ";
            $statement = $connect->prepare($sql);
            $statement->execute(); 
            $ast_pr = $statement->fetchColumn();
     
         //  stock_update($connect);          
         
          $freight =  trim($_POST["freight"]);
       	$gst_amount = 0;
       	$fst_amount = 0;
       	$ast_amount = 0;
       	$tot_exp = 0;
       	if($gst_pr > 0){$gst_amount = $inv_amt * $gst_pr / 100;}
       	if($fst_pr > 0){$fst_amount = $inv_amt * $fst_pr / 100;}
       	if($ast_pr > 0){$ast_amount = $inv_amt * $ast_pr / 100;}
          $tot_exp = $freight + $fst_amount + $ast_amount;
         if($tot_exp > 0)
          {
           $cost_up = $tot_exp / $inv_amt; 
          }
         
          $cost_down = 0;
          $cost_up = 0;
         
          if($discount > 0)
          {
            
            $cost_down = $discount / $inv_amt;
            $cost_up = $cost_up - $cost_down;
          }
          $sql = "update purchase_detail set cost_up = '".$cost_up."' where p_id = '".$p_id."'";
          $statement = $connect->prepare($sql);
          $statement->execute();
        
         
          $sql = "update purchase_detail set cost_rate = rate * cost_up where p_id = '".$p_id."'";
          $statement = $connect->prepare($sql);
          $statement->execute();
         
          $sql = "update purchase_detail set adj_cost = rate + cost_rate where p_id = '".$p_id."'";
          $statement = $connect->prepare($sql);
          $statement->execute();
          $sql = "update purchase_detail d, items i set d.pre_cost = i.item_cost,
          d.pre_stock=i.stock-d.qty where i.item_id=d.item_no and d.p_id = '".$p_id."'";
          $statement = $connect->prepare($sql);
          $statement->execute();
           $sql = "update purchase_detail set pre_stock = 0
          where pre_stock < 0 and p_id = '".$p_id."'";
          $statement = $connect->prepare($sql);
          $statement->execute();
          $sql = "update purchase_detail set pre_stock = 0
          where pre_cost = 0 and p_id = '".$p_id."'";
          $statement = $connect->prepare($sql);
          $statement->execute();
            $sql = "update purchase_detail set pre_amount = pre_cost*pre_stock,
            cur_amount = qty*adj_cost where p_id = '".$p_id."'";
          $statement = $connect->prepare($sql);
          $statement->execute();
           $sql = "update purchase_detail set total_amount = pre_amount+cur_amount,
            total_stock = qty+pre_stock where p_id = '".$p_id."'";
            
          $statement = $connect->prepare($sql);
          $statement->execute();
           $sql = "update purchase_detail set item_cost = total_amount / total_stock
             where p_id = '".$p_id."'";
            
          $statement = $connect->prepare($sql);
          $statement->execute();
         
          $sql = "update items i,purchase_detail d set i.item_cost =
         d.item_cost, i.lp_rate = d.rate where d.p_id = '".$p_id."' and d.item_no=i.item_id";
          $statement = $connect->prepare($sql);
          $statement->execute();
       
          //create jv
         
         
          $due_day = 0;
          $ref_id = $p_id;
         
            $sql = "SELECT IFNULL(max(jv_no),0) 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();
            $jv_no++; 
         
          
         
            $inv_no = $_POST["inv_no"];
          
      
     
            $purchase = $inv_amt - $discount;
             $sql = "SELECT sum(rate * dis_pr / 100 * qty) FROM purchase_detail WHERE p_id = '".$p_id."' ";
            $statement = $connect->prepare($sql);
            $statement->execute(); 
            $dis_pr = $statement->fetchColumn();
             $purchase = $purchase - $dis_pr;  
            
            $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++;
       
            
            $v_id = $_POST['v_id'];
           // $sv_id = $_POST['sv_id'];
            
            
            
           
              $purchase_account = $v_id;
           
            $source = 'AP Inv';
            $description = 'AP Credit Invoice ' .$p_id .', Inv #: '. $_POST['inv_no']. ' ' .$_POST['description'] ;
            $due_day = 0;
          
            
            $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'          =>  $p_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'              =>  1031001,
            ':debit'          =>  $purchase,
            ':credit'           =>  0
            
          )
        );
        
        if($gst_amount > 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'              =>  2012002,
            ':debit'          =>  $gst_amount,
            ':credit'           =>  0
                        
          )
        );
        
        }
         
         if($freight > 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'              =>  2012003,
            ':debit'          =>  $freight,
            ':credit'           =>  0
                        
          )
        );
         } 
        
        
        $payable = $purchase + $gst_amount + $freight + $fst_amount + $ast_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'              =>  $purchase_account,
            ':debit'          =>  0,
            ':credit'           =>  $payable
                     
          )
        );
        
        /////End Jv/////
      
    
	}

  if($_POST['btn_action']=='update')
  {
    if($_POST['gst_pr'] > 0)
    {
      $gst = 'ON';
    }else{$gst = 'OFF';}
   $cost_up = 0;
    $cost_down = 0;
    $p_id = $_POST['p_id'];
    $ref_id = $p_id;
    $due_day = 0;
   
    $due_date =Date('Y-m-d', strtotime("+".$due_day." days"));
   
      
      $statement = $connect->prepare("
        UPDATE purchase
        SET branch_id = :branch_id,
        po_no = :po_no,
        inv_no = :inv_no,
        due_day = :due_day,
        dated = :dated,
         due_date = :due_date,
        v_id = :v_id,
        sv_id = :sv_id,
        description = :description,
        discount = :discount,
         discount_dsc = :discount_dsc, 
        inv_amt = :inv_amt,       
        freight = :freight,
        freight_dsc = :freight_dsc,
        gst = :gst,
        gst_pr = :gst_pr,
        fst_pr = :fst_pr,
        ast_pr = :ast_pr,
        user_id= :user_id,    
        status = :status        
        WHERE p_id = :p_id
        ");
      $statement->execute(
        array(
          ':branch_id'             =>  $branch_id,
          ':inv_no'             =>  trim($_POST["inv_no"]),
          ':po_no'             =>  0,
          ':due_day'             =>  0,
          ':dated'             =>  $_POST["dated"],
          ':due_date'             =>  $due_date,
          ':v_id'          =>  trim($_POST["v_id"]),
          ':sv_id'          =>  0,
          ':description'       =>  $_POST["description"],
          ':discount'             =>  trim($_POST["discount"]),
           ':discount_dsc'             =>  trim($_POST["discount_dsc"]),
          ':inv_amt'             =>  0,          
          ':freight'             =>  trim($_POST["freight"]),
          ':freight_dsc'             =>  trim($_POST["freight_dsc"]),
          ':gst'             =>  $gst,
          ':gst_pr'             =>  trim($_POST["gst_pr"]),
          ':fst_pr'             =>  trim($_POST["fst_pr"]),
          ':ast_pr'             =>  trim($_POST["ast_pr"]),
          ':user_id'             =>  $user_id,       
          ':status'             =>  'Active',          
          ':p_id'               =>  $p_id
          )
        );
      
       
         $sql = "delete from purchase_detail 
        where p_id = '".$p_id."'";
        $statement = $connect->prepare($sql);
        $statement->execute();
       
      for($count=0; $count<$_POST["total_item"]; $count++)
      {
        $statement = $connect->prepare("
          INSERT INTO purchase_detail
          (p_id,item_no,store_id,ctn,qty,rate,dis_pr,ln_no)
          VALUES(:p_id, :item_no, :store_id, :ctn, :qty, :rate, :dis_pr, :ln_no)
        ");
        $statement->execute(
          array(
            ':p_id'               =>  $p_id,            
            ':item_no'              =>  trim($_POST["item_no"][$count]),
            ':store_id'              =>  1,
             ':ctn'          =>  1,
            ':qty'          =>  trim($_POST["qty"][$count]),
            ':rate'           =>  trim($_POST["rate"][$count]),
            ':dis_pr'           =>  trim($_POST["dis_pr"][$count]),
            ':ln_no'        => $count
        
          )
        );
      }    
    $sql ="
          select sum(qty*rate) from purchase_detail where p_id = '".$p_id."'
          
          ";
        $statement = $connect->prepare($sql);
        $statement->execute();
        $inv_amt = $statement->fetchColumn();     
      $sql = "update purchase set inv_amt = '".$inv_amt."' where p_id = '".$p_id."'";
          $statement = $connect->prepare($sql);
          $statement->execute();
      
          $sql = "select discount from purchase where p_id = '".$p_id."'";
          $statement = $connect->prepare($sql);
          $statement->execute();
          $discount = $statement->fetchColumn();
          $purchase = $inv_amt - $discount;
                       $sql = "SELECT gst_pr FROM purchase WHERE p_id = '".$p_id."' ";
            $statement = $connect->prepare($sql);
            $statement->execute(); 
            $gst_pr = $statement->fetchColumn();
                         $sql = "SELECT fst_pr FROM purchase WHERE p_id = '".$p_id."' ";
            $statement = $connect->prepare($sql);
            $statement->execute(); 
            $fst_pr = $statement->fetchColumn();
                         $sql = "SELECT ast_pr FROM purchase WHERE p_id = '".$p_id."' ";
            $statement = $connect->prepare($sql);
            $statement->execute(); 
            $ast_pr = $statement->fetchColumn();
     
         //  stock_update($connect);          
         
          $freight =  trim($_POST["freight"]);
        $gst_amount = 0;
        $fst_amount = 0;
        $ast_amount = 0;
        $tot_exp = 0;
        if($gst_pr > 0){$gst_amount = $inv_amt * $gst_pr / 100;}
        if($fst_pr > 0){$fst_amount = $inv_amt * $fst_pr / 100;}
        if($ast_pr > 0){$ast_amount = $inv_amt * $ast_pr / 100;}
          $tot_exp = $freight + $fst_amount + $ast_amount;
         if($tot_exp > 0)
          {
           $cost_up = $tot_exp / $inv_amt; 
          }
         
          $cost_down = 0;
          $cost_up = 0;
         
          if($discount > 0)
          {
            
            $cost_down = $discount / $inv_amt;
            $cost_up = $cost_up - $cost_down;
          }
          $sql = "update purchase_detail set cost_up = '".$cost_up."' where p_id = '".$p_id."'";
          $statement = $connect->prepare($sql);
          $statement->execute();
        
         
          $sql = "update purchase_detail set cost_rate = rate * cost_up where p_id = '".$p_id."'";
          $statement = $connect->prepare($sql);
          $statement->execute();
         
          $sql = "update purchase_detail set adj_cost = rate + cost_rate where p_id = '".$p_id."'";
          $statement = $connect->prepare($sql);
          $statement->execute();
          $sql = "update purchase_detail d, items i set d.pre_cost = i.item_cost,
          d.pre_stock=i.stock-d.qty where i.item_id=d.item_no and d.p_id = '".$p_id."'";
          $statement = $connect->prepare($sql);
          $statement->execute();
           $sql = "update purchase_detail set pre_stock = 0
          where pre_stock < 0 and p_id = '".$p_id."'";
          $statement = $connect->prepare($sql);
          $statement->execute();
          $sql = "update purchase_detail set pre_stock = 0
          where pre_cost = 0 and p_id = '".$p_id."'";
          $statement = $connect->prepare($sql);
          $statement->execute();
            $sql = "update purchase_detail set pre_amount = pre_cost*pre_stock,
            cur_amount = qty*adj_cost where p_id = '".$p_id."'";
          $statement = $connect->prepare($sql);
          $statement->execute();
           $sql = "update purchase_detail set total_amount = pre_amount+cur_amount,
            total_stock = qty+pre_stock where p_id = '".$p_id."'";
            
          $statement = $connect->prepare($sql);
          $statement->execute();
           $sql = "update purchase_detail set item_cost = total_amount / total_stock
             where p_id = '".$p_id."'";
            
          $statement = $connect->prepare($sql);
          $statement->execute();
         
          $sql = "update items i,purchase_detail d set i.item_cost =
         d.item_cost, i.lp_rate = d.rate where d.p_id = '".$p_id."' and d.item_no=i.item_id";
          $statement = $connect->prepare($sql);
          $statement->execute();
                     
          
            $source = 'AP Inv';
            
            
             $v_id = $_POST['v_id'];
           
            $source = 'AP Inv';
            $purchase_account = $v_id;
            $description = 'AP Credit Invoice ' .$p_id .', Inv #: '. $_POST['inv_no']. ' ' .$_POST['description'] ;
            
            
         $sql = "select jv_id from jv where source = 'AP Inv' and ref_id = '".$p_id."'";
         $statement = $connect->query($sql)   ;
         $jv_id = $statement->fetchColumn();
        $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'          =>  $p_id,
          ':description'       =>  $description,
                      
          ':user_id'       =>  $user_id,
          ':status'       =>  'Active',
          ':jv_id'               =>  $jv_id
        )
      );

      /// Update Jv ///

        $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'              =>  1031001,
            ':debit'          =>  $purchase,
            ':credit'           =>  0
            
          )
        );
        
        if($gst_amount > 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'              =>  2012002,
            ':debit'          =>  $gst_amount,
            ':credit'           =>  0
                        
          )
        );
        
        }
         
         if($freight > 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'              =>  2012003,
            ':debit'          =>  $freight,
            ':credit'           =>  0
                        
          )
        );
         } 
        
        
        $payable = $purchase + $gst_amount + $freight + $fst_amount + $ast_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'              =>  $purchase_account,
            ':debit'          =>  0,
            ':credit'           =>  $payable
                     
          )
        );
        ///// End Jv update ///
     
      
      
        
        echo 'success';
  }	
	
	if($_POST['btn_action'] == 'delete')
	{
		$p_id = $_POST['p_id'];
		
		$sql = "DELETE FROM purchase WHERE p_id = '".$p_id."' ";
            $statement = $connect->prepare($sql);
            $statement->execute(); // no need to add `$sql` here, you can take that out
        
        $sql = "DELETE FROM purchase_detail WHERE p_id = '".$p_id."' ";
            $statement = $connect->prepare($sql);
            $statement->execute(); // no 
		
		$sql = "SELECT jv_id FROM jv WHERE source = 'AP Inv' and ref_id = '".$p_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();
            echo 'Purchase ID: '.$p_id . ' Deleted';
		
		//$result = $statement->fetchAll();	
		
	}
}
?>