<?php 
$message = '';
$branch_id = 1;
if(isset($_POST["mytest"]))
{
  include('db_con.php');
  $statement  = $connect->query("select * from purchase where v_id = 2016000");
  $result = $statement->fetchAll();
  foreach ($result as $row)
   {

    $p_id = $row['p_id'];
    $statement = $connect->query("delete from purchase where p_id='".$p_id."'");

    $statement = $connect->query("delete from purchase_detail where p_id='".$p_id."'");

  }

  $statement  = $connect->query("select * from jv where source = 'item'");
  $result = $statement->fetchAll();
  foreach ($result as $row)
   {

    $jv_id = $row['jv_id'];
    $statement = $connect->query("delete from jv where jv_id='".$jv_id."'");

    $statement = $connect->query("delete from jv_detail where jv_id='".$jv_id."'");

  }

  $statement = $connect->query("select * from items");
  $result = $statement->fetchAll();
  foreach ($result as $row) 
  {

   $op_stock = $row['op_stock'];
    $item_cost = $row['item_cost'];
    $amount = $item_cost * $op_stock;
    $item_id = $row['item_id'];
    if($amount > 0)
    {
      $source = 'item';
      $ref_id = $row['item_id'];
            $description = 'Opening Stock of New Item ' .$item_id;
            date_default_timezone_set('Asia/Karachi');
            $dated = date('Y-m-d');
            $sql = "select IFNULL(max(jv_no),0) from jv where source = 'item'";
    $statement = $connect->prepare($sql);
    $statement->execute();
    $jv_no = $statement->fetchColumn();
    $jv_no++;
         
          
            
            $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'             =>  $dated,
          ':source'          =>  $source,
          ':ref_id'          =>  $ref_id,
          ':description'       =>  $description,
       
          ':user_id'       =>  15
      )
    );
     $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'              =>  1031001,
            ':debit'          =>  $amount,
            ':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'              =>  1000000,
            ':debit'          =>  0,
            ':credit'           =>  $amount
                        
          )
        );
    }
    $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,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, :user_id, :status)
    ");
    $statement->execute(
      array(
         ':po_no'             =>  0,
          ':branch_id'             =>  1,
          ':inv_no'             =>  0,
          ':dated'             =>  date('Y-m-d'),
       ':due_date'             =>  date('Y-m-d'),
          ':v_id'          =>  2016000,
          ':sv_id'          =>  1,
          ':description'       =>  'B/F '.$item_id,
          ':discount'       =>  0,
          ':discount_dsc'       =>  0,
          ':inv_amt'       =>  $amount,
          ':due_day'       =>  0,          
          ':freight'       =>  0,
          ':freight_dsc'       =>  0,
          ':gst'       =>  'OFF',
          ':gst_pr'       =>  0,
          ':user_id'       =>  15,          
          ':status'       =>  'Active'
          
      )
    );
      
        $statement = $connect->query("SELECT LAST_INSERT_ID()");
         $p_id = $statement->fetchColumn();
          
      
        $statement = $connect->prepare("
          INSERT INTO purchase_detail
          (p_id,item_no,store_id,ctn,qty,rate,ln_no)
          VALUES(:p_id, :item_no, :store_id, :ctn, :qty, :rate, :ln_no)
        ");
        $statement->execute(
          array(
            ':p_id'               =>  $p_id,            
            ':item_no'              =>  $item_id,
            ':store_id'              =>  1,            
            ':ctn'          =>  1,
            ':qty'          =>  $op_stock,
            ':rate'           =>  $item_cost,
            
            ':ln_no'        => 1
        
          )
        );

    
    
  }

  header('Location: dashboard');
}

?>

<!DOCTYPE html>  
<html>  
 <head>  
  <title>Restore Database</title>  
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>  
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />  
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>  
 </head>  
 <body>  
  <br /><br />  
  <div class="container" style="width:700px;">  
   <h3 align="center"><img src="upload/warning.gif" style="float:left;width: 40px;height: 40px"> Warning!  All Data will be replaced.</h3>  
   <br />
   <div><?php echo $message; ?></div>
   <form method="post" enctype="multipart/form-data">
    <p><label>Select Sql File</label>
    <input type="file" name="database" /></p>
    <br />
    <input type="submit" name="import" class="form-control btn btn-info" value="Import" />
    <input type="submit" name="mytest" class="form-control btn btn-warning" value="Test" />
   </form>
  </div>  
 </body>  
</html>