Monday, 11 December 2017

Encryption and Decryption Form Data in PHP

This post will provide you how to make a two-way system for encrypt form data and decrypt that an encrypted string in PHP using Ajax JQuery. We have already seen many post in which we have store form data in Mysql table in simple original string format. But here we have discuss some security for store Form data using PHP. Here we will not insert form data in it's orignal form. But we will encryt form data using PHP code and then after we will store into Mysql table. So when user save form data then after we will encrypt form data and then after we will insert into Mysql table by using PHP script with Ajax Jquery.

Here we will discuss two way encryption and decryption of Encrypted string. That means once we will store form data in encrypted form and then after we want to display that encrypted string on their original format. So at that time we will decrypt that encrypted string by using PHP script and display on web page. So, it is called two way encrypt and decrpt string using PHP script. For encryption and decryption string in PHP we have use different PHP encrypt method like AES-256-CBC. We have use this PHP encrypt method for encrypt string. We have also use PHP hash() function for make encrypted string. We have also use different PHP function like openssl_encrypt() function for convert string to encrypted form and after this we have use base64_encode(). By using this both function we can encrypt string. For decrypt string we have use base64_decode() function and openssl_decrypt() function for decrypt encryted string. This way we can make two way encryption and decryption in PHP.

For Two way encryption and decryption in PHP depends on encryption key and initialization vector. If we have lost this two key then we cannot decrypt encrypted string. So string encryption is depends on this two keys and it it is lost then we cannot convert encrypted string. For discuss this things we have use simple Insert Update Delete and Select data example by using PHP script with Ajax Jquery. In this example first we will fetch encrypted data from Mysql table and convert into normal string and display on web page in Jquery Datatables. After this we will Insert form data into Mysql table. So for this we will encrypt form data and insert into Mysql table. Then after we want to update, so update first we want to fetch single user encrypted data and decrypt and display in form field. For this all crud operation we have use PHP Script with Ajax Jquery.






Source Code


database_connection.php



<?php
//database_connection.php
$connect = new PDO('mysql:host=localhost;dbname=testing', 'root', '');

?>


index.php



<?php
//index.php



?>
<!DOCTYPE html>
<html>
 <head>
  <title>How to Encrypt & Decrypt Form Data using PHP</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://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>
  <script src="https://cdn.datatables.net/1.10.12/js/dataTables.bootstrap.min.js"></script>  
  <link rel="stylesheet" href="https://cdn.datatables.net/1.10.12/css/dataTables.bootstrap.min.css" />
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
 </head>
 <body>
  <br />
  <h2 align="center">How to Encrypt & Decrypt Form Data using PHP</h2>
  <br />
  <div class="container">
  
  <div class="row">
   <div class="col-lg-12">
    <div class="panel panel-default">
     <div class="panel-heading">
      <div class="col-lg-10 col-md-10 col-sm-8 col-xs-6">
       <div class="row">
        <h3 class="panel-title">User List</h3>
       </div>
      </div>
      <div class="col-lg-2 col-md-2 col-sm-4 col-xs-6">
       <div class="row" align="right">
        <button type="button" name="add" id="add_button" class="btn btn-success btn-xs">Add</button>     
       </div>
      </div>
      <div style="clear:both"></div>
     </div>
     <div class="panel-body">
      <div class="row">
       <div class="col-sm-12 table-responsive">
        <span id="alert_action"></span>
        <table id="user_data" class="table table-bordered table-striped">
         <thead><tr>
          <th>First Name</th>
          <th>Last Name</th>
          <th>Phone</th>
          <th>Email</th>
          <th>Edit</th>
          <th>Delete</th>
         </tr></thead>
        </table>
       </div>
      </div>
     </div>
    </div>
   </div>
  </div></div>
  <div id="userModal" class="modal fade">
   <div class="modal-dialog">
    <form method="post" id="user_form">
     <div class="modal-content">
      <div class="modal-header">
       <button type="button" class="close" data-dismiss="modal">&times;</button>
       <h4 class="modal-title">Add User</h4>
      </div>
      <div class="modal-body">
       <span id="validation_error"></span>
       <div class="form-group">
        <label>Enter First Name</label>
        <input type="text" name="first_name" id="first_name" class="form-control" />
       </div>
       <div class="form-group">
        <label>Enter Last Name</label>
        <input type="text" name="last_name" id="last_name" class="form-control" />
       </div>
       <div class="form-group">
        <label>Enter Phone No.</label>
        <input type="text" name="phone" id="phone" class="form-control" />
       </div>
       <div class="form-group">
        <label>Enter Email</label>
        <input type="email" name="email_address" id="email_address" class="form-control" />
       </div>
      </div>
      <div class="modal-footer">
       <input type="hidden" name="id" id="id"/>
       <input type="hidden" name="crud_action" id="crud_action"/>
       <input type="submit" name="action" id="action" class="btn btn-info" value="Add" />
       <button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
      </div>
     </div>
    </form>
   </div>
  </div>
 </body>
</html>

<script>
$(document).ready(function(){
  
 $('#add_button').click(function(){
  $('#userModal').modal('show');
  $('#user_form')[0].reset();
  $('.modal-title').html("<i class='fa fa-plus'></i> Add User");
  $('#action').val('Add');
  $('#crud_action').val('Add');
 });
 
 var crud_action = 'fetch_all';
 
 var userdataTable = $('#user_data').DataTable({
  "processing":true,
  "serverSide":true,
  "order":[],
  "ajax":{
   url:"user_action.php",
   type:"POST",
   data:{crud_action:crud_action}
  },
  "columnDefs":[
   {
    "targets":[4, 5],
    "orderable":false,
   },
  ],
  "pageLength": 10
 });
 
 $(document).on('submit', '#user_form', function(event){
  
  event.preventDefault();
  
  var form_data = $(this).serialize();
  
  $.ajax({
   url:"user_action.php",
   method:"POST",
   data:form_data,
   dataType:"json",
   success:function(data)
   {
    if(data.error != '')
    {
     $('#validation_error').html(data.error);
    }
    else
    {
     $('#alert_action').html(data.message);
     $('#user_form')[0].reset();
     $('#userModal').modal('hide');
     userdataTable.ajax.reload();
    }
     
   }
  });  
 });
 
 $(document).on('click', '.update', function(){
  var id = $(this).attr("id");
  crud_action = "fetch_single";
  $.ajax({
   url:"user_action.php",
   method:"POST",
   data:{id:id, crud_action:crud_action},
   dataType:"JSON",
   success:function(data)
   {
    $('#validation_error').html('');
    $('#userModal').modal('show');
    $('.modal-title').text('Edit User');
    $('#first_name').val(data.first_name);
    $('#last_name').val(data.last_name);
    $('#phone').val(data.phone);
    $('#email_address').val(data.email_address);
    $('#id').val(id);
    $('#crud_action').val('Edit');
    $('#action').val('Edit');
   }
  });
 });
 
 $(document).on('click', '.delete', function(){
  var id = $(this).attr("id");
  crud_action = "Delete";
  if(confirm("Are you sure you want to delete this?"))
  {
   $.ajax({
    url:"user_action.php",
    method:"POST",
    data:{id:id, crud_action:crud_action},
    dataType:"json",
    success:function(data)
    {
     $('#alert_action').html(data.message);
     $('#userModal').modal('hide');
     userdataTable.ajax.reload();
    }
   });
  }
  else
  {
   return false;
  }
 });
 
});
</script>


user_action.php



<?php 

//user_action.php

include('database_connection.php');

include('function.php');

if(isset($_POST["crud_action"]))
{
 if($_POST["crud_action"] == 'fetch_all')
 {
  $query = '';
  
  $output = array();

  $order_column = array('first_name', 'last_name', 'phone', 'email');

  $query .= "
   SELECT * FROM tbl_user 
  ";

  if(isset($_POST["search"]["value"]))
  {
   $query .= 'WHERE first_name LIKE "%'.convert_string('encrypt', $_POST["search"]["value"]).'%" ';
   $query .= 'OR last_name LIKE "%'.convert_string('encrypt', $_POST["search"]["value"]).'%" ';
   $query .= 'OR phone LIKE "%'.convert_string('encrypt', $_POST["search"]["value"]).'%" ';
   $query .= 'OR email LIKE "%'.convert_string('encrypt', $_POST["search"]["value"]).'%" ';
  }

  if(isset($_POST["order"]))
  {
   $query .= 'ORDER BY '.$order_column[$_POST['order']['0']['column']].' '.$_POST['order']['0']['dir'].' ';
  }
  else
  {
   $query .= 'ORDER BY id DESC ';
  }

  if($_POST["length"] != -1)
  {
   $query .= 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
  }

  $statement = $connect->prepare($query);

  $statement->execute();

  $result = $statement->fetchAll();

  $filtered_rows = $statement->rowCount();

  foreach($result as $row)
  {
   $sub_array = array();
   $sub_array[] = convert_string('decrypt', $row['first_name']);
   $sub_array[] = convert_string('decrypt', $row['last_name']);
   $sub_array[] = convert_string('decrypt', $row['phone']);
   $sub_array[] = convert_string('decrypt', $row['email']);
   $sub_array[] = '<button type="button" name="update" id="'.convert_string('encrypt', $row["id"]).'" class="btn btn-warning btn-xs update">Update</button>';
   $sub_array[] = '<button type="button" name="delete" id="'.convert_string('encrypt', $row["id"]).'" class="btn btn-danger btn-xs delete">Delete</button>';
   $output[] = $sub_array;
  }

  $data = array(
   "draw"    => intval($_POST["draw"]),
   "recordsTotal"  => $filtered_rows,
   "recordsFiltered" => get_total_all_records($connect),
   "data"    => $output
  );
 }
 elseif($_POST["crud_action"] == 'fetch_single')
 {
  $id = convert_string('decrypt', $_POST["id"]);
  $query = "
  SELECT * FROM tbl_user 
  WHERE id = '$id'
  ";
  $statement = $connect->prepare($query);
  $statement->execute();
  $result = $statement->fetchAll();
  foreach($result as $row)
  {
   $data['first_name'] = convert_string('decrypt', $row['first_name']);
   $data['last_name'] = convert_string('decrypt', $row['last_name']);
   $data['phone'] = convert_string('decrypt', $row['phone']);
   $data['email_address'] = convert_string('decrypt', $row['email']);
  }
 }
 elseif($_POST["crud_action"] == 'Delete')
 {
  $id = convert_string('decrypt', $_POST["id"]);
  $query = "
  DELETE FROM tbl_user 
  WHERE id = '$id'
  ";
  $statement = $connect->prepare($query);
  $statement->execute();
  $data = array(
   'message'  => '<div class="alert alert-success">User Deleted</div>'
  );
 }
 else
 {
  $message = '';
  $error = '';
  $first_name = '';
  $last_name = '';
  $phone = '';
  $email_address = '';
  if(empty($_POST["first_name"]))
  {
   $error .= '<p class="text-danger">First Name is Required</p>';
  }
  else
  {
   if (!preg_match("/^[a-zA-Z]*$/",$_POST["first_name"]))
   {
    $error .= '<p class="text-danger">Only Alphabet allowed in First Name</p>';
   }
   else
   {
    $first_name = clean_text($_POST["first_name"]);
   }
  }
  
  if(empty($_POST["last_name"]))
  {
   $error .= '<p class="text-danger">Last Name is Required</p>';
  }
  else
  {
   if (!preg_match("/^[a-zA-Z]*$/",$_POST["last_name"]))
   {
    $error .= '<p class="text-danger">Only Alphabet allowed in Last Name</p>';
   }
   else
   {
    $last_name = clean_text($_POST["last_name"]);
   }
  }
  
  if(empty($_POST["phone"]))
  {
   $error .= '<p class="text-danger">Phone Number is Required</p>';
  }
  else
  {
   if (!preg_match("/^[0-9]*$/",$_POST["phone"]))
   {
    $error .= '<p class="text-danger">Only Numbers allowed in Phone</p>';
   }
   else
   {
    $phone = clean_text($_POST["phone"]);
   }
  }
  
  if(empty($_POST["email_address"]))
  {
   $error .= '<p class="text-danger">Email Address is Required</p>';
  }
  else
  {
   if (!filter_var($_POST["email_address"], FILTER_VALIDATE_EMAIL))
   {
    $error .= '<p class="text-danger">Invalid email format</p>'; 
   }
   else
   {
    $email_address = clean_text($_POST["email_address"]);
   }
  }
  
  if($error == '')
  {
   $first_name = convert_string('encrypt', $first_name);
   $last_name = convert_string('encrypt', $last_name);
   $phone = convert_string('encrypt', $phone);
   $email_address = convert_string('encrypt', $email_address);
   if($_POST["crud_action"] == "Add")
   {
    $query = "
    SELECT * FROM tbl_user 
    WHERE email = '$email_address'
    ";
    $statement = $connect->prepare($query);
    $statement->execute();
    $no_of_row = $statement->rowCount();
    if($no_of_row > 0)
    {
     $error = '<div class="alert alert-danger">Email Already Exists</div>';
    }
    else
    {
     $query = "
     INSERT INTO tbl_user (first_name, last_name, phone, email) 
     VALUES('".$first_name."', '".$last_name."', '".$phone."', '".$email_address."')
     ";
     $message = '<div class="alert alert-success">User Added</div>';
    }
   }
   if($_POST["crud_action"] == "Edit")
   {
    $id = convert_string('decrypt', $_POST["id"]);
    $query = "
    UPDATE tbl_user 
    SET first_name = '$first_name', 
    last_name = '$last_name', 
    phone = '$phone', 
    email = '$email_address' 
    WHERE id = '$id'
    ";
    $message = '<div class="alert alert-success">User Edited</div>';
   }
   $statement = $connect->prepare($query);
   $statement->execute();
   $result = $statement->fetchAll();
   if(isset($result))
   {
    $data = array(
     'error'   => $error,
     'message'  => $message
    );
   }
  }
  else
  {
   $data = array(
    'error'   => $error,
    'message'  => $message
   );
   
  }
 }
 echo json_encode($data);
}

?>


function.php



<?php

//function.php

function get_total_all_records($connect)
{
 $statement = $connect->prepare('SELECT * FROM tbl_user');
 $statement->execute();
 return $statement->rowCount();
}

function clean_text($string)
{
 $string = trim($string);
 $string = stripslashes($string);
 $string = htmlspecialchars($string);
 return $string;
}

function convert_string($action, $string)
{
 $output = '';
 $encrypt_method = "AES-256-CBC";
    $secret_key = 'eaiYYkYTysia2lnHiw0N0vx7t7a3kEJVLfbTKoQIx5o=';
    $secret_iv = 'eaiYYkYTysia2lnHiw0N0';
    // hash
    $key = hash('sha256', $secret_key);
 $initialization_vector = substr(hash('sha256', $secret_iv), 0, 16);
 if($string != '')
 {
  if($action == 'encrypt')
  {
   $output = openssl_encrypt($string, $encrypt_method, $key, 0, $initialization_vector);
   $output = base64_encode($output);
  } 
  if($action == 'decrypt') 
  {
   $output = openssl_decrypt(base64_decode($string), $encrypt_method, $key, 0, $initialization_vector);
  }
 }
 return $output;
}

?>


Database



--
-- Database: `testing`
--

-- --------------------------------------------------------

--
-- Table structure for table `tbl_user`
--

CREATE TABLE IF NOT EXISTS `tbl_user` (
  `id` int(11) NOT NULL,
  `first_name` varchar(250) NOT NULL,
  `last_name` varchar(250) NOT NULL,
  `phone` varchar(30) NOT NULL,
  `email` varchar(200) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_user`
--

INSERT INTO `tbl_user` (`id`, `first_name`, `last_name`, `phone`, `email`) VALUES
(1, 'QlRsWTJ2azNjb2NLb2N4NjcyRkxFQT09', 'TlViVDljWHFDNTY5eHU3UTBQQmsvQT09', 'eHp4aVR4STZ4SGNrTm9hQ0dDU0lJZz', 'cTM0N2RCUVBHTTNZRGhKZFdXM2VqNlNnVVUxYTd2b3hIaThuY3JKNEFQVT0='),
(2, 'VVdQTUhpTG80a1VOMW1DL1JsL01oZz09', 'WnRkUEJjNGRGUlFXRWVpMWJWUkRlQT09', 'Q1NMNUUvdE1RRUlqVWtJZWs4aEIwQT', 'NGNrMkVnUDNEb1psQ2NwSEVKMTY1b1FzcGUrRjVDMDhCU2g2WGdvK0Vzdz0='),
(3, 'ZFlCUEtPNFllUGNBMTZCMWxiVnE2dz09', 'VFJLNjU5MUFKengrVTAwY0g5MGtZZz09', 'NEcvUnRRbVVoSHVJQXozL0E3NGRVQT', 'OGRGb3F0bjRERE1rbllEa3JNL1JkVWRZdHdXZ0wwenlqL3kyYWZWSGw5cz0='),
(4, 'Q2ovYWcvaE5SUmN5L2hKQkZWaFNQUT09', 'enROUUNFOStYRmJyVHRYWWVYeUd3QT09', 'd3RodWxEVzFHRWkzTzYxSk1tSzFxZz', 'K3IrNWVHZ2ovRlMvSzh1akMzVmgvU096c2Rxc3hOZndjMWh4aVAyazI2ST0='),
(5, 'UmtyUVFERmNacGxuV2VxejdIM2o2dz09', 'Q0ZzSHU5K0ZQOVc2NWpSNDA5WWljUT09', 'ZVpRcTJyRVNabXZzekZUeXovYm52dz', 'U2ZURXlvVXcwNngwMTZEY21zdHY5empWcmU3MHdsSHN2SDE5eWJ1OFplbz0='),
(6, 'bG5SNFVybk5JcVM3MmV0ZXQ0L0YrUT09', 'd0E4Z1hqVTZqMlhvY2N5MHR4LzhqZz09', 'TXozaEhtVk5tZjBNY0NxR1ljS04zQT', 'WHpZeW1zQVNSSG1UYldlVkJia1QvUk5PbmlJamxlOUtqbytXRWkwWkNwND0='),
(7, 'UVh4a0QyWCtjN1F5YWdTVnpxdk1YUT09', 'OElTMlVVQjBscG01YmNFTGkzeWMxdz09', 'd2tQeU14NnJZeU1WeHFBSkI5TUswZz', 'NFNmNjNWaDFnR0d6N3dnOXVwTnMwM0VSTWZlYjQvc0FqUzBJbVBheXA3Yz0='),
(8, 'NFd1ZjJrVm8zQWVqN3paa3I2MHJKQT09', 'UVkzb2wwdkVzbFRpS3FjdVNIMEk1QT09', 'ZThROGpQa0NJTHlKU2c3U3VtTWxOZz', 'cW5ia3hETmJwVGhKd1hRTmlYT1A4cVEwd1VMc3VnZTltVjVXaEF2RmcxST0='),
(9, 'alpYNHpWZlZldFRSckdHMnpCS2dJZz09', 'UFdCZFhyMEFSY0t0VWlkdWxQMFA3QT09', 'N1N1YmphQkVuUi9ReEdoS3hQS2psQT', 'bE5GbkxrcVJiTWZ2SGhYaDAzc2FPVHAzcE5qUVQ4R1QrWWtyTUJJVHIxTT0='),
(10, 'U1U0U0FEREJXOXo5VEk1VGdoVE9tZz09', 'NC9IWnE0R1k4VHhFVkdMdjVtZ0x6QT09', 'VDRBT3puTEVUREtnMTQ3K3ZUcTlmQT', 'N1RPV2xObi92dTBGMXVhOWFlUEgzTGxENkhUeUVhQzRDUFQzNVhHdzQxND0='),
(11, 'dzUwT2xnSFRsdVdaaWNhMHZScXFhUT09', 'dUkrMmkveWFaWTJtcUNpM0pDZ1ZXQT09', 'dFZsY0RGQVV1cThaSGRvZ1JYUDEydz', 'c25pYkJiNStiaEZFNkZpazE0VTFMZmxKSHJsY3lVWmtQUWwrMnByQzhEST0='),
(12, 'RC9WK0IxSWorOEh4alpobG10ODgyQT09', 'VGdiSHZSbkh5enQzOEJxbFlvUDRXZz09', 'L3c0cTZQM1lKcXNrQ0lQZ3dDd2hMUT', 'VGthd3hZVXVqMmM4MXRPYWxEVUVlLzZFajZ5b2lNOStTNytSZUJxL2QwND0='),
(13, 'QllBN0g2SlNLd3FqRHFUUDRZR0VnZz09', 'M0VMWG5RT21PYkRLRUdvVTBpRnA0QT09', 'ZW5yb0wwTytwNEVORXQvZjFRdVpxUT', 'cExuK1V2ZXVHRzNTZGNMUmZrY215aHN5R3didWovMWtVcVBlUFZCUzM5bz0=');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `tbl_user`
--
ALTER TABLE `tbl_user`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `tbl_user`
--
ALTER TABLE `tbl_user`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=14;

Monday, 4 December 2017

PHP Registration Script with Email Confirmation

In this post, We have start learning how can we send user activation email after completing user registration. Because if you have created an account on any website and have you verify you email by click through a verification link send by website for activate or verify email which you have enter email at the time of registration. So we have make this post to learn how can you build an email verification script step by step.

This is email verify PHP script in which you allows you to verify your email address at the time of registration. This email verification script used at the time of new registration or this script also required when in your site has rss subscription, then use has enter email for subscribe rss feed. So email must be original and reduce spam. So At that time we want to verify email address by sending verification link to that email address.

Here we have use simple PHP registration example to verify email address by sending email activation link to their account and by clicking on that link email will be verified. For make this script we have use PHP PDO script with Mysql Database and for sending email we have use PHPMailer Library. In this script user can register into site by entering proper email and after register with email address, then they will received email verification link into their email address. So if email will be proper then he will received email verification link. If user not verified their email address then he cannot login into site. For access website user want to verify their email address. This script helpful to reduce spam registration into website. For email verification user has go to email account and in his email address he will received email verification link with password. So user can verify email by clicking on that link. After email verification user can also received password in his email also. After this email verification user can login into system. This way we can verify email address for reduce span registration by using PHP PDO with Mysql Database and PHPMailer Library.








Source Code


database_connection.php



<?php
//database_connection.php

$connect = new PDO('mysql:host=localhost;dbname=testing', 'root', '');
session_start();

?>


register.php



<?php
//register.php

include('database_connection.php');

if(isset($_SESSION['user_id']))
{
 header("location:index.php");
}

$message = '';

if(isset($_POST["register"]))
{
 $query = "
 SELECT * FROM register_user 
 WHERE user_email = :user_email
 ";
 $statement = $connect->prepare($query);
 $statement->execute(
  array(
   ':user_email' => $_POST['user_email']
  )
 );
 $no_of_row = $statement->rowCount();
 if($no_of_row > 0)
 {
  $message = '<label class="text-danger">Email Already Exits</label>';
 }
 else
 {
  $user_password = rand(100000,999999);
  $user_encrypted_password = password_hash($user_password, PASSWORD_DEFAULT);
  $user_activation_code = md5(rand());
  $insert_query = "
  INSERT INTO register_user 
  (user_name, user_email, user_password, user_activation_code, user_email_status) 
  VALUES (:user_name, :user_email, :user_password, :user_activation_code, :user_email_status)
  ";
  $statement = $connect->prepare($insert_query);
  $statement->execute(
   array(
    ':user_name'   => $_POST['user_name'],
    ':user_email'   => $_POST['user_email'],
    ':user_password'  => $user_encrypted_password,
    ':user_activation_code' => $user_activation_code,
    ':user_email_status' => 'not verified'
   )
  );
  $result = $statement->fetchAll();
  if(isset($result))
  {
   $base_url = "http://localhost/tutorial/email-address-verification-script-using-php/";
   $mail_body = "
   <p>Hi ".$_POST['user_name'].",</p>
   <p>Thanks for Registration. Your password is ".$user_password.", This password will work only after your email verification.</p>
   <p>Please Open this link to verified your email address - ".$base_url."email_verification.php?activation_code=".$user_activation_code."
   <p>Best Regards,<br />Webslesson</p>
   ";
   require 'class/class.phpmailer.php';
   $mail = new PHPMailer;
   $mail->IsSMTP();        //Sets Mailer to send message using SMTP
   $mail->Host = 'smtpout.secureserver.net';  //Sets the SMTP hosts of your Email hosting, this for Godaddy
   $mail->Port = '80';        //Sets the default SMTP server port
   $mail->SMTPAuth = true;       //Sets SMTP authentication. Utilizes the Username and Password variables
   $mail->Username = 'xxxxxxxx';     //Sets SMTP username
   $mail->Password = 'xxxxxxxx';     //Sets SMTP password
   $mail->SMTPSecure = '';       //Sets connection prefix. Options are "", "ssl" or "tls"
   $mail->From = 'info@webslesson.info';   //Sets the From email address for the message
   $mail->FromName = 'Webslesson';     //Sets the From name of the message
   $mail->AddAddress($_POST['user_email'], $_POST['user_name']);  //Adds a "To" address   
   $mail->WordWrap = 50;       //Sets word wrapping on the body of the message to a given number of characters
   $mail->IsHTML(true);       //Sets message type to HTML    
   $mail->Subject = 'Email Verification';   //Sets the Subject of the message
   $mail->Body = $mail_body;       //An HTML or plain text message body
   if($mail->Send())        //Send an Email. Return true on success or false on error
   {
    $message = '<label class="text-success">Register Done, Please check your mail.</label>';
   }
  }
 }
}

?>

<!DOCTYPE html>
<html>
 <head>
  <title>PHP Register Login Script with Email Verification</title>  
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.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.7/js/bootstrap.min.js"></script>
 </head>
 <body>
  <br />
  <div class="container" style="width:100%; max-width:600px">
   <h2 align="center">PHP Register Login Script with Email Verification</h2>
   <br />
   <div class="panel panel-default">
    <div class="panel-heading"><h4>Register</h4></div>
    <div class="panel-body">
     <form method="post" id="register_form">
      <?php echo $message; ?>
      <div class="form-group">
       <label>User Name</label>
       <input type="text" name="user_name" class="form-control" pattern="[a-zA-Z ]+" required />
      </div>
      <div class="form-group">
       <label>User Email</label>
       <input type="email" name="user_email" class="form-control" required />
      </div>
      <div class="form-group">
       <input type="submit" name="register" id="register" value="Register" class="btn btn-info" />
      </div>
     </form>
     <p align="right"><a href="login.php">Login</a></p>
    </div>
   </div>
  </div>
 </body>
</html>


email_verification.php



<?php

include('database_connection.php');

$message = '';

if(isset($_GET['activation_code']))
{
 $query = "
  SELECT * FROM register_user 
  WHERE user_activation_code = :user_activation_code
 ";
 $statement = $connect->prepare($query);
 $statement->execute(
  array(
   ':user_activation_code'   => $_GET['activation_code']
  )
 );
 $no_of_row = $statement->rowCount();
 
 if($no_of_row > 0)
 {
  $result = $statement->fetchAll();
  foreach($result as $row)
  {
   if($row['user_email_status'] == 'not verified')
   {
    $update_query = "
    UPDATE register_user 
    SET user_email_status = 'verified' 
    WHERE register_user_id = '".$row['register_user_id']."'
    ";
    $statement = $connect->prepare($update_query);
    $statement->execute();
    $sub_result = $statement->fetchAll();
    if(isset($sub_result))
    {
     $message = '<label class="text-success">Your Email Address Successfully Verified <br />You can login here - <a href="login.php">Login</a></label>';
    }
   }
   else
   {
    $message = '<label class="text-info">Your Email Address Already Verified</label>';
   }
  }
 }
 else
 {
  $message = '<label class="text-danger">Invalid Link</label>';
 }
}

?>
<!DOCTYPE html>
<html>
 <head>
  <title>PHP Register Login Script with Email Verification</title>  
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.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.7/js/bootstrap.min.js"></script>
 </head>
 <body>
  
  <div class="container">
   <h1 align="center">PHP Register Login Script with Email Verification</h1>
  
   <h3><?php echo $message; ?></h3>
   
  </div>
 
 </body>
 
</html>


login.php



<?php
//login.php

include('database_connection.php');

if(isset($_SESSION['user_id']))
{
 header("location:index.php");
}

$message = '';

if(isset($_POST["login"]))
{
 $query = "
 SELECT * FROM register_user 
  WHERE user_email = :user_email
 ";
 $statement = $connect->prepare($query);
 $statement->execute(
  array(
    'user_email' => $_POST["user_email"]
  )
 );
 $count = $statement->rowCount();
 if($count > 0)
 {
  $result = $statement->fetchAll();
  foreach($result as $row)
  {
   if($row['user_email_status'] == 'verified')
   {
    if(password_verify($_POST["user_password"], $row["user_password"]))
    {
     $_SESSION['user_id'] = $row['register_user_id'];
     header("location:index.php");
    }
    else
    {
     $message = "<label>Wrong Password</label>";
    }
   }
   else
   {
    $message = "<label class='text-danger'>Please First Verify, your email address</label>";
   }
  }
 }
 else
 {
  $message = "<label class='text-danger'>Wrong Email Address</label>";
 }
}

?>

<!DOCTYPE html>
<html>
 <head>
  <title>PHP Register Login Script with Email Verification</title>  
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.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.7/js/bootstrap.min.js"></script>
 </head>
 <body>
  <br />
  <div class="container" style="width:100%; max-width:600px">
   <h2 align="center">PHP Register Login Script with Email Verification</h2>
   <br />
   <div class="panel panel-default">
    <div class="panel-heading"><h4>Login</h4></div>
    <div class="panel-body">
     <form method="post">
      <?php echo $message; ?>
      <div class="form-group">
       <label>User Email</label>
       <input type="email" name="user_email" class="form-control" required />
      </div>
      <div class="form-group">
       <label>Password</label>
       <input type="password" name="user_password" class="form-control" required />
      </div>
      <div class="form-group">
       <input type="submit" name="login" value="Login" class="btn btn-info" />
      </div>
     </form>
     <p align="right"><a href="register.php">Register</a></p>
    </div>
   </div>
  </div>
 </body>
</html>


logout.php



<?php
//logout.php
session_start();

session_destroy();

header("location:login.php");

?>


Database



--
-- Database: `testing`
--

-- --------------------------------------------------------

--
-- Table structure for table `register_user`
--

CREATE TABLE IF NOT EXISTS `register_user` (
  `register_user_id` int(11) NOT NULL,
  `user_name` varchar(250) NOT NULL,
  `user_email` varchar(250) NOT NULL,
  `user_password` varchar(250) NOT NULL,
  `user_activation_code` varchar(250) NOT NULL,
  `user_email_status` enum('not verified','verified') NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `register_user`
--

INSERT INTO `register_user` (`register_user_id`, `user_name`, `user_email`, `user_password`, `user_activation_code`, `user_email_status`) VALUES
(1, 'John Smith', 'web-tutorial@programmer.net', '$2y$10$vdMwAmoRJfep8Vl4BI0QDOXArOCTOMbFs6Ja15qq3NEkPUBBtffD2', 'c74c4bf0dad9cbae3d80faa054b7d8ca', 'verified');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `register_user`
--
ALTER TABLE `register_user`
  ADD PRIMARY KEY (`register_user_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `register_user`
--
ALTER TABLE `register_user`
  MODIFY `register_user_id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=2;

Friday, 10 November 2017

PHP Mysql Inventory Management System using Ajax



This Inventory Management System is an Open Source Project which is developed by using PHP PDO, Mysql Database, Bootstrap Library, Ajax and Jquery Datatables plugin. This system is web based application and this created by using PHP PDO, Ajax, Jquery Datatables plugin, Bootstrap Library and Mysql Database. This system will provides features like manage category, brands, products, order and report to user.





This is web based system then master user can operate this system from any place by using any device, because this is web based and it's design is responsive. So we can access this system by using any device. So web based Stock Management system produce it easy to keep your stock perfectly from any location. Stock control is one of the main fundamentals of any developed businesses and this system will help to increase their business stock accurately.

Web based Inventory Management system has uncounted benefits as compared to desktop based system, because it is accessible from anywhere you just you will need internet connection and you can check your item stock status from your mobile device also. On other use of developing Web based Inventory management system is that multiple user can gain access it on the same time. Following are the feature of this system.

Features

  • Secured Encrypted Password Login
  • Master and Sub user Profile for change details like Name, Email and Password
  • Master user can create new sub user, edit sub user details and active/inactive sub user login
  • Master user can add new category, change category name and remove use of category
  • Master user can add new brand, update brand details and remove brand
  • Master user can insert new product, update product details and delete product
  • Master and sub user can generate new order with multiple item
  • Master and sub user can edit order details with add or more multiple product from order
  • Master and sub user can generate PDF Invoice of particular order
  • Master user can track available particular quantity
  • Master can see total category, brand, product, order and user details
  • Sub user can see order which he has generated
  • Master can see whole system analytics on his index page






Source Code


database_connection.php



<?php
//database_connection.php

$connect = new PDO('mysql:host=localhost;dbname=testing2', 'root', '');
session_start();

?>


login.php



<?php
//login.php

include('database_connection.php');

if(isset($_SESSION['type']))
{
 header("location:index.php");
}

$message = '';

if(isset($_POST["login"]))
{
 $query = "
 SELECT * FROM user_details 
  WHERE user_email = :user_email
 ";
 $statement = $connect->prepare($query);
 $statement->execute(
  array(
    'user_email' => $_POST["user_email"]
   )
 );
 $count = $statement->rowCount();
 if($count > 0)
 {
  $result = $statement->fetchAll();
  foreach($result as $row)
  {
   if(password_verify($_POST["user_password"], $row["user_password"]))
   {
    if($row['user_status'] == 'Active')
    {
     $_SESSION['type'] = $row['user_type'];
     $_SESSION['user_id'] = $row['user_id'];
     $_SESSION['user_name'] = $row['user_name'];
     header("location:index.php");
    }
    else
    {
     $message = "<label>Your account is disabled, Contact Master</label>";
    }
   }
   else
   {
    $message = "<label>Wrong Password</label>";
   }
  }
 }
 else
 {
  $message = "<label>Wrong Email Address</labe>";
 }
}

?>

<!DOCTYPE html>
<html>
 <head>
  <title>Inventory Management System using PHP with Ajax Jquery</title>  
  <script src="js/jquery-1.10.2.min.js"></script>
  <link rel="stylesheet" href="css/bootstrap.min.css" />
  <script src="js/bootstrap.min.js"></script>
 </head>
 <body>
  <br />
  <div class="container">
   <h2 align="center">Inventory Management System using PHP with Ajax Jquery</h2>
   <br />
   <div class="panel panel-default">
    <div class="panel-heading">Login</div>
    <div class="panel-body">
     <form method="post">
      <?php echo $message; ?>
      <div class="form-group">
       <label>User Email</label>
       <input type="text" name="user_email" class="form-control" required />
      </div>
      <div class="form-group">
       <label>Password</label>
       <input type="password" name="user_password" class="form-control" required />
      </div>
      <div class="form-group">
       <input type="submit" name="login" value="Login" class="btn btn-info" />
      </div>
     </form>
    </div>
   </div>
  </div>
 </body>
</html>


header.php



<?php
//header.php
?>
<!DOCTYPE html>
<html>
 <head>
  <title>Inventory Management System</title>
  <script src="js/jquery-1.10.2.min.js"></script>
  <link rel="stylesheet" href="css/bootstrap.min.css" />
  <script src="js/jquery.dataTables.min.js"></script>
  <script src="js/dataTables.bootstrap.min.js"></script>  
  <link rel="stylesheet" href="css/dataTables.bootstrap.min.css" />
  <script src="js/bootstrap.min.js"></script>
 </head>
 <body>
  <br />
  <div class="container">
   <h2 align="center">Inventory Management System</h2>

   <nav class="navbar navbar-inverse">
    <div class="container-fluid">
     <div class="navbar-header">
      <a href="index.php" class="navbar-brand">Home</a>
     </div>
     <ul class="nav navbar-nav">
     <?php
     if($_SESSION['type'] == 'master')
     {
     ?>
      <li><a href="user.php">User</a></li>
      <li><a href="category.php">Category</a></li>
      <li><a href="brand.php">Brand</a></li>
      <li><a href="product.php">Product</a></li>
     <?php
     }
     ?>
      <li><a href="order.php">Order</a></li>
     </ul>
     <ul class="nav navbar-nav navbar-right">
      <li class="dropdown">
       <a href="#" class="dropdown-toggle" data-toggle="dropdown"><span class="label label-pill label-danger count"></span> <?php echo $_SESSION["user_name"]; ?></a>
       <ul class="dropdown-menu">
        <li><a href="profile.php">Profile</a></li>
        <li><a href="logout.php">Logout</a></li>
       </ul>
      </li>
     </ul>

    </div>
   </nav>


footer.php



</div>
 </body>
</html>


index.php



<?php
//index.php
include('database_connection.php');
include('function.php');

if(!isset($_SESSION["type"]))
{
 header("location:login.php");
}

include('header.php');

?>
 <br />
 <div class="row">
 <?php
 if($_SESSION['type'] == 'master')
 {
 ?>
 <div class="col-md-3">
  <div class="panel panel-default">
   <div class="panel-heading"><strong>Total User</strong></div>
   <div class="panel-body" align="center">
    <h1><?php echo count_total_user($connect); ?></h1>
   </div>
  </div>
 </div>
 <div class="col-md-3">
  <div class="panel panel-default">
   <div class="panel-heading"><strong>Total Category</strong></div>
   <div class="panel-body" align="center">
    <h1><?php echo count_total_category($connect); ?></h1>
   </div>
  </div>
 </div>
 <div class="col-md-3">
  <div class="panel panel-default">
   <div class="panel-heading"><strong>Total Brand</strong></div>
   <div class="panel-body" align="center">
    <h1><?php echo count_total_brand($connect); ?></h1>
   </div>
  </div>
 </div>
 <div class="col-md-3">
  <div class="panel panel-default">
   <div class="panel-heading"><strong>Total Item in Stock</strong></div>
   <div class="panel-body" align="center">
    <h1><?php echo count_total_product($connect); ?></h1>
   </div>
  </div>
 </div>
 <?php
 }
 ?>
  <div class="col-md-4">
   <div class="panel panel-default">
    <div class="panel-heading"><strong>Total Order Value</strong></div>
    <div class="panel-body" align="center">
     <h1>$<?php echo count_total_order_value($connect); ?></h1>
    </div>
   </div>
  </div>
  <div class="col-md-4">
   <div class="panel panel-default">
    <div class="panel-heading"><strong>Total Cash Order Value</strong></div>
    <div class="panel-body" align="center">
     <h1>$<?php echo count_total_cash_order_value($connect); ?></h1>
    </div>
   </div>
  </div>
  <div class="col-md-4">
   <div class="panel panel-default">
    <div class="panel-heading"><strong>Total Credit Order Value</strong></div>
    <div class="panel-body" align="center">
     <h1>$<?php echo count_total_credit_order_value($connect); ?></h1>
    </div>
   </div>
  </div>
  <hr />
  <?php
  if($_SESSION['type'] == 'master')
  {
  ?>
  <div class="col-md-12">
   <div class="panel panel-default">
    <div class="panel-heading"><strong>Total Order Value User wise</strong></div>
    <div class="panel-body" align="center">
     <?php echo get_user_wise_total_order($connect); ?>
    </div>
   </div>
  </div>
  <?php
  }
  ?>
 </div>

<?php
include("footer.php");
?>


logout.php



<?php
//logout.php
session_start();

session_destroy();

header("location:login.php");

?>



profile.php



<?php
//profile.php

include('database_connection.php');

if(!isset($_SESSION['type']))
{
 header("location:login.php");
}

$query = "
SELECT * FROM user_details 
WHERE user_id = '".$_SESSION["user_id"]."'
";
$statement = $connect->prepare($query);
$statement->execute();
$result = $statement->fetchAll();
$name = '';
$email = '';
$user_id = '';
foreach($result as $row)
{
 $name = $row['user_name'];
 $email = $row['user_email'];
}

include('header.php');

?>
  <div class="panel panel-default">
   <div class="panel-heading">Edit Profile</div>
   <div class="panel-body">
    <form method="post" id="edit_profile_form">
     <span id="message"></span>
     <div class="form-group">
      <label>Name</label>
      <input type="text" name="user_name" id="user_name" class="form-control" value="<?php echo $name; ?>" required />
     </div>
     <div class="form-group">
      <label>Email</label>
      <input type="email" name="user_email" id="user_email" class="form-control" required value="<?php echo $email; ?>" />
     </div>
     <hr />
     <label>Leave Password blank if you do not want to change</label>
     <div class="form-group">
      <label>New Password</label>
      <input type="password" name="user_new_password" id="user_new_password" class="form-control" />
     </div>
     <div class="form-group">
      <label>Re-enter Password</label>
      <input type="password" name="user_re_enter_password" id="user_re_enter_password" class="form-control" />
      <span id="error_password"></span> 
     </div>
     <div class="form-group">
      <input type="submit" name="edit_prfile" id="edit_prfile" value="Edit" class="btn btn-info" />
     </div>
    </form>
   </div>
  </div>

<script>
$(document).ready(function(){
 $('#edit_profile_form').on('submit', function(event){
  event.preventDefault();
  if($('#user_new_password').val() != '')
  {
   if($('#user_new_password').val() != $('#user_re_enter_password').val())
   {
    $('#error_password').html('<label class="text-danger">Password Not Match</label>');
    return false;
   }
   else
   {
    $('#error_password').html('');
   }
  }
  $('#edit_prfile').attr('disabled', 'disabled');
  var form_data = $(this).serialize();
  $('#user_re_enter_password').attr('required',false);
  $.ajax({
   url:"edit_profile.php",
   method:"POST",
   data:form_data,
   success:function(data)
   {
    $('#edit_prfile').attr('disabled', false);
    $('#user_new_password').val('');
    $('#user_re_enter_password').val('');
    $('#message').html(data);
   }
  })
 });
});
</script>


edit_profile.php



<?php

//edit_profile.php

include('database_connection.php');

if(isset($_POST['user_name']))
{
 if($_POST["user_new_password"] != '')
 {
  $query = "
  UPDATE user_details SET 
   user_name = '".$_POST["user_name"]."', 
   user_email = '".$_POST["user_email"]."', 
   user_password = '".password_hash($_POST["user_new_password"], PASSWORD_DEFAULT)."' 
   WHERE user_id = '".$_SESSION["user_id"]."'
  ";
 }
 else
 {
  $query = "
  UPDATE user_details SET 
   user_name = '".$_POST["user_name"]."', 
   user_email = '".$_POST["user_email"]."'
   WHERE user_id = '".$_SESSION["user_id"]."'
  ";
 }
 $statement = $connect->prepare($query);
 $statement->execute();
 $result = $statement->fetchAll();
 if(isset($result))
 {
  echo '<div class="alert alert-success">Profile Edited</div>';
 }
}

?>



user.php



<?php
//user.php

include('database_connection.php');

if(!isset($_SESSION["type"]))
{
 header('location:login.php');
}

if($_SESSION["type"] != 'master')
{
 header("location:index.php");
}

include('header.php');


?>
  <span id="alert_action"></span>
  <div class="row">
   <div class="col-lg-12">
    <div class="panel panel-default">
                    <div class="panel-heading">
                     <div class="row">
                         <div class="col-lg-10 col-md-10 col-sm-8 col-xs-6">
                             <h3 class="panel-title">User List</h3>
                            </div>
                            <div class="col-lg-2 col-md-2 col-sm-4 col-xs-6" align="right">
                             <button type="button" name="add" id="add_button" data-toggle="modal" data-target="#userModal" class="btn btn-success btn-xs">Add</button>
                         </div>
                        </div>
                       
                        <div class="clear:both"></div>
                    </div>
                    <div class="panel-body">
                     <div class="row"><div class="col-sm-12 table-responsive">
                      <table id="user_data" class="table table-bordered table-striped">
                       <thead>
         <tr>
          <th>ID</th>
          <th>Email</th>
          <th>Name</th>
          <th>Status</th>
          <th>Edit</th>
          <th>Delete</th>
         </tr>
        </thead>
                      </table>
                     </div>
                    </div>
                </div>
            </div>
        </div>
        <div id="userModal" class="modal fade">
         <div class="modal-dialog">
          <form method="post" id="user_form">
           <div class="modal-content">
           <div class="modal-header">
            <button type="button" class="close" data-dismiss="modal">&times;</button>
      <h4 class="modal-title"><i class="fa fa-plus"></i> Add User</h4>
           </div>
           <div class="modal-body">
            <div class="form-group">
       <label>Enter User Name</label>
       <input type="text" name="user_name" id="user_name" class="form-control" required />
      </div>
      <div class="form-group">
       <label>Enter User Email</label>
       <input type="email" name="user_email" id="user_email" class="form-control" required />
      </div>
      <div class="form-group">
       <label>Enter User Password</label>
       <input type="password" name="user_password" id="user_password" class="form-control" required />
      </div>
           </div>
           <div class="modal-footer">
            <input type="hidden" name="user_id" id="user_id" />
            <input type="hidden" name="btn_action" id="btn_action" />
            <input type="submit" name="action" id="action" class="btn btn-info" value="Add" />
            <button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
           </div>
          </div>
          </form>

         </div>
        </div>
<script>
$(document).ready(function(){

 $('#add_button').click(function(){
  $('#user_form')[0].reset();
  $('.modal-title').html("<i class='fa fa-plus'></i> Add User");
  $('#action').val("Add");
  $('#btn_action').val("Add");
 });

 var userdataTable = $('#user_data').DataTable({
  "processing": true,
  "serverSide": true,
  "order": [],
  "ajax":{
   url:"user_fetch.php",
   type:"POST"
  },
  "columnDefs":[
   {
    "target":[4,5],
    "orderable":false
   }
  ],
  "pageLength": 25
 });

 $(document).on('submit', '#user_form', function(event){
  event.preventDefault();
  $('#action').attr('disabled','disabled');
  var form_data = $(this).serialize();
  $.ajax({
   url:"user_action.php",
   method:"POST",
   data:form_data,
   success:function(data)
   {
    $('#user_form')[0].reset();
    $('#userModal').modal('hide');
    $('#alert_action').fadeIn().html('<div class="alert alert-success">'+data+'</div>');
    $('#action').attr('disabled', false);
    userdataTable.ajax.reload();
   }
  })
 });

 $(document).on('click', '.update', function(){
  var user_id = $(this).attr("id");
  var btn_action = 'fetch_single';
  $.ajax({
   url:"user_action.php",
   method:"POST",
   data:{user_id:user_id, btn_action:btn_action},
   dataType:"json",
   success:function(data)
   {
    $('#userModal').modal('show');
    $('#user_name').val(data.user_name);
    $('#user_email').val(data.user_email);
    $('.modal-title').html("<i class='fa fa-pencil-square-o'></i> Edit User");
    $('#user_id').val(user_id);
    $('#action').val('Edit');
    $('#btn_action').val('Edit');
    $('#user_password').attr('required', false);
   }
  })
 });

 $(document).on('click', '.delete', function(){
  var user_id = $(this).attr("id");
  var status = $(this).data('status');
  var btn_action = "delete";
  if(confirm("Are you sure you want to change status?"))
  {
   $.ajax({
    url:"user_action.php",
    method:"POST",
    data:{user_id:user_id, status:status, btn_action:btn_action},
    success:function(data)
    {
     $('#alert_action').fadeIn().html('<div class="alert alert-info">'+data+'</div>');
     userdataTable.ajax.reload();
    }
   })
  }
  else
  {
   return false;
  }
 });

});
</script>

<?php
include('footer.php');
?>


user_fetch.php



<?php

//user_fetch.php

include('database_connection.php');

$query = '';

$output = array();

$query .= "
SELECT * FROM user_details 
WHERE user_type = 'user' AND 
";

if(isset($_POST["search"]["value"]))
{
 $query .= '(user_email LIKE "%'.$_POST["search"]["value"].'%" ';
 $query .= 'OR user_name LIKE "%'.$_POST["search"]["value"].'%" ';
 $query .= 'OR user_status LIKE "%'.$_POST["search"]["value"].'%") ';
}

if(isset($_POST["order"]))
{
 $query .= 'ORDER BY '.$_POST['order']['0']['column'].' '.$_POST['order']['0']['dir'].' ';
}
else
{
 $query .= 'ORDER BY user_id DESC ';
}

if($_POST["length"] != -1)
{
 $query .= 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
}

$statement = $connect->prepare($query);

$statement->execute();

$result = $statement->fetchAll();

$data = array();

$filtered_rows = $statement->rowCount();

foreach($result as $row)
{
 $status = '';
 if($row["user_status"] == 'Active')
 {
  $status = '<span class="label label-success">Active</span>';
 }
 else
 {
  $status = '<span class="label label-danger">Inactive</span>';
 }
 $sub_array = array();
 $sub_array[] = $row['user_id'];
 $sub_array[] = $row['user_email'];
 $sub_array[] = $row['user_name'];
 $sub_array[] = $status;
 $sub_array[] = '<button type="button" name="update" id="'.$row["user_id"].'" class="btn btn-warning btn-xs update">Update</button>';
 $sub_array[] = '<button type="button" name="delete" id="'.$row["user_id"].'" class="btn btn-danger btn-xs delete" data-status="'.$row["user_status"].'">Delete</button>';
 $data[] = $sub_array;
}

$output = array(
 "draw"    => intval($_POST["draw"]),
 "recordsTotal"   =>  $filtered_rows,
 "recordsFiltered"  =>  get_total_all_records($connect),
 "data"       =>  $data
);
echo json_encode($output);

function get_total_all_records($connect)
{
 $statement = $connect->prepare("SELECT * FROM user_details WHERE user_type='user'");
 $statement->execute();
 return $statement->rowCount();
}

?>


user_action.php



<?php

//user_action.php

include('database_connection.php');

if(isset($_POST['btn_action']))
{
 if($_POST['btn_action'] == 'Add')
 {
  $query = "
  INSERT INTO user_details (user_email, user_password, user_name, user_type, user_status) 
  VALUES (:user_email, :user_password, :user_name, :user_type, :user_status)
  "; 
  $statement = $connect->prepare($query);
  $statement->execute(
   array(
    ':user_email'  => $_POST["user_email"],
    ':user_password' => password_hash($_POST["user_password"], PASSWORD_DEFAULT),
    ':user_name'  => $_POST["user_name"],
    ':user_type'  => 'user',
    ':user_status'  => 'active'
   )
  );
  $result = $statement->fetchAll();
  if(isset($result))
  {
   echo 'New User Added';
  }
 }
 if($_POST['btn_action'] == 'fetch_single')
 {
  $query = "
  SELECT * FROM user_details WHERE user_id = :user_id
  ";
  $statement = $connect->prepare($query);
  $statement->execute(
   array(
    ':user_id' => $_POST["user_id"]
   )
  );
  $result = $statement->fetchAll();
  foreach($result as $row)
  {
   $output['user_email'] = $row['user_email'];
   $output['user_name'] = $row['user_name'];
  }
  echo json_encode($output);
 }
 if($_POST['btn_action'] == 'Edit')
 {
  if($_POST['user_password'] != '')
  {
   $query = "
   UPDATE user_details SET 
    user_name = '".$_POST["user_name"]."', 
    user_email = '".$_POST["user_email"]."',
    user_password = '".password_hash($_POST["user_password"], PASSWORD_DEFAULT)."' 
    WHERE user_id = '".$_POST["user_id"]."'
   ";
  }
  else
  {
   $query = "
   UPDATE user_details SET 
    user_name = '".$_POST["user_name"]."', 
    user_email = '".$_POST["user_email"]."'
    WHERE user_id = '".$_POST["user_id"]."'
   ";
  }
  $statement = $connect->prepare($query);
  $statement->execute();
  $result = $statement->fetchAll();
  if(isset($result))
  {
   echo 'User Details Edited';
  }
 }
 if($_POST['btn_action'] == 'delete')
 {
  $status = 'Active';
  if($_POST['status'] == 'Active')
  {
   $status = 'Inactive';
  }
  $query = "
  UPDATE user_details 
  SET user_status = :user_status 
  WHERE user_id = :user_id
  ";
  $statement = $connect->prepare($query);
  $statement->execute(
   array(
    ':user_status' => $status,
    ':user_id'  => $_POST["user_id"]
   )
  ); 
  $result = $statement->fetchAll(); 
  if(isset($result))
  {
   echo 'User Status change to ' . $status;
  }
 }
}

?>


category.php



<?php
//category.php

include('database_connection.php');

if(!isset($_SESSION['type']))
{
 header('location:login.php');
}

if($_SESSION['type'] != 'master')
{
 header("location:index.php");
}

include('header.php');

?>

 <span id="alert_action"></span>
 <div class="row">
  <div class="col-lg-12">
   <div class="panel panel-default">
                <div class="panel-heading">
                    <div class="col-lg-10 col-md-10 col-sm-8 col-xs-6">
                        <div class="row">
                            <h3 class="panel-title">Category List</h3>
                        </div>
                    </div>
                    <div class="col-lg-2 col-md-2 col-sm-4 col-xs-6">
                        <div class="row" align="right">
                             <button type="button" name="add" id="add_button" data-toggle="modal" data-target="#categoryModal" class="btn btn-success btn-xs">Add</button>     
                        </div>
                    </div>
                    <div style="clear:both"></div>
                </div>
                <div class="panel-body">
                    <div class="row">
                     <div class="col-sm-12 table-responsive">
                      <table id="category_data" class="table table-bordered table-striped">
                       <thead><tr>
         <th>ID</th>
         <th>Category Name</th>
         <th>Status</th>
         <th>Edit</th>
         <th>Delete</th>
        </tr></thead>
                      </table>
                     </div>
                    </div>
                </div>
            </div>
        </div>
    </div>
    <div id="categoryModal" class="modal fade">
     <div class="modal-dialog">
      <form method="post" id="category_form">
       <div class="modal-content">
        <div class="modal-header">
         <button type="button" class="close" data-dismiss="modal">&times;</button>
      <h4 class="modal-title"><i class="fa fa-plus"></i> Add Category</h4>
        </div>
        <div class="modal-body">
         <label>Enter Category Name</label>
      <input type="text" name="category_name" id="category_name" class="form-control" required />
        </div>
        <div class="modal-footer">
         <input type="hidden" name="category_id" id="category_id"/>
         <input type="hidden" name="btn_action" id="btn_action"/>
         <input type="submit" name="action" id="action" class="btn btn-info" value="Add" />
         <button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
        </div>
       </div>
      </form>
     </div>
    </div>
<script>
$(document).ready(function(){

 $('#add_button').click(function(){
  $('#category_form')[0].reset();
  $('.modal-title').html("<i class='fa fa-plus'></i> Add Category");
  $('#action').val('Add');
  $('#btn_action').val('Add');
 });

 $(document).on('submit','#category_form', function(event){
  event.preventDefault();
  $('#action').attr('disabled','disabled');
  var form_data = $(this).serialize();
  $.ajax({
   url:"category_action.php",
   method:"POST",
   data:form_data,
   success:function(data)
   {
    $('#category_form')[0].reset();
    $('#categoryModal').modal('hide');
    $('#alert_action').fadeIn().html('<div class="alert alert-success">'+data+'</div>');
    $('#action').attr('disabled', false);
    categorydataTable.ajax.reload();
   }
  })
 });

 $(document).on('click', '.update', function(){
  var category_id = $(this).attr("id");
  var btn_action = 'fetch_single';
  $.ajax({
   url:"category_action.php",
   method:"POST",
   data:{category_id:category_id, btn_action:btn_action},
   dataType:"json",
   success:function(data)
   {
    $('#categoryModal').modal('show');
    $('#category_name').val(data.category_name);
    $('.modal-title').html("<i class='fa fa-pencil-square-o'></i> Edit Category");
    $('#category_id').val(category_id);
    $('#action').val('Edit');
    $('#btn_action').val("Edit");
   }
  })
 });

 var categorydataTable = $('#category_data').DataTable({
  "processing":true,
  "serverSide":true,
  "order":[],
  "ajax":{
   url:"category_fetch.php",
   type:"POST"
  },
  "columnDefs":[
   {
    "targets":[3, 4],
    "orderable":false,
   },
  ],
  "pageLength": 25
 });
 $(document).on('click', '.delete', function(){
  var category_id = $(this).attr('id');
  var status = $(this).data("status");
  var btn_action = 'delete';
  if(confirm("Are you sure you want to change status?"))
  {
   $.ajax({
    url:"category_action.php",
    method:"POST",
    data:{category_id:category_id, status:status, btn_action:btn_action},
    success:function(data)
    {
     $('#alert_action').fadeIn().html('<div class="alert alert-info">'+data+'</div>');
     categorydataTable.ajax.reload();
    }
   })
  }
  else
  {
   return false;
  }
 });
});
</script>

<?php
include('footer.php');
?>


category_fetch.php



<?php

//category_fetch.php

include('database_connection.php');

$query = '';

$output = array();

$query .= "SELECT * FROM category ";

if(isset($_POST["search"]["value"]))
{
 $query .= 'WHERE category_name LIKE "%'.$_POST["search"]["value"].'%" ';
 $query .= 'OR category_status LIKE "%'.$_POST["search"]["value"].'%" ';
}

if(isset($_POST['order']))
{
 $query .= 'ORDER BY '.$_POST['order']['0']['column'].' '.$_POST['order']['0']['dir'].' ';
}
else
{
 $query .= 'ORDER BY category_id DESC ';
}

if($_POST['length'] != -1)
{
 $query .= 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
}

$statement = $connect->prepare($query);

$statement->execute();

$result = $statement->fetchAll();

$data = array();

$filtered_rows = $statement->rowCount();

foreach($result as $row)
{
 $status = '';
 if($row['category_status'] == 'active')
 {
  $status = '<span class="label label-success">Active</span>';
 }
 else
 {
  $status = '<span class="label label-danger">Inactive</span>';
 }
 $sub_array = array();
 $sub_array[] = $row['category_id'];
 $sub_array[] = $row['category_name'];
 $sub_array[] = $status;
 $sub_array[] = '<button type="button" name="update" id="'.$row["category_id"].'" class="btn btn-warning btn-xs update">Update</button>';
 $sub_array[] = '<button type="button" name="delete" id="'.$row["category_id"].'" class="btn btn-danger btn-xs delete" data-status="'.$row["category_status"].'">Delete</button>';
 $data[] = $sub_array;
}

$output = array(
 "draw"   => intval($_POST["draw"]),
 "recordsTotal"   =>  $filtered_rows,
 "recordsFiltered"  =>  get_total_all_records($connect),
 "data"    => $data
);

function get_total_all_records($connect)
{
 $statement = $connect->prepare("SELECT * FROM category");
 $statement->execute();
 return $statement->rowCount();
}

echo json_encode($output);

?>


category_action.php



<?php

//category_action.php

include('database_connection.php');

if(isset($_POST['btn_action']))
{
 if($_POST['btn_action'] == 'Add')
 {
  $query = "
  INSERT INTO category (category_name) 
  VALUES (:category_name)
  ";
  $statement = $connect->prepare($query);
  $statement->execute(
   array(
    ':category_name' => $_POST["category_name"]
   )
  );
  $result = $statement->fetchAll();
  if(isset($result))
  {
   echo 'Category Name Added';
  }
 }
 
 if($_POST['btn_action'] == 'fetch_single')
 {
  $query = "SELECT * FROM category WHERE category_id = :category_id";
  $statement = $connect->prepare($query);
  $statement->execute(
   array(
    ':category_id' => $_POST["category_id"]
   )
  );
  $result = $statement->fetchAll();
  foreach($result as $row)
  {
   $output['category_name'] = $row['category_name'];
  }
  echo json_encode($output);
 }

 if($_POST['btn_action'] == 'Edit')
 {
  $query = "
  UPDATE category set category_name = :category_name  
  WHERE category_id = :category_id
  ";
  $statement = $connect->prepare($query);
  $statement->execute(
   array(
    ':category_name' => $_POST["category_name"],
    ':category_id'  => $_POST["category_id"]
   )
  );
  $result = $statement->fetchAll();
  if(isset($result))
  {
   echo 'Category Name Edited';
  }
 }
 if($_POST['btn_action'] == 'delete')
 {
  $status = 'active';
  if($_POST['status'] == 'active')
  {
   $status = 'inactive'; 
  }
  $query = "
  UPDATE category 
  SET category_status = :category_status 
  WHERE category_id = :category_id
  ";
  $statement = $connect->prepare($query);
  $statement->execute(
   array(
    ':category_status' => $status,
    ':category_id'  => $_POST["category_id"]
   )
  );
  $result = $statement->fetchAll();
  if(isset($result))
  {
   echo 'Category status change to ' . $status;
  }
 }
}

?>


brand.php



<?php
//brand.php
include('database_connection.php');

include('function.php');

if(!isset($_SESSION['type']))
{
 header('location:login.php');
}

if($_SESSION['type'] != 'master')
{
 header('location:index.php');
}

include('header.php');

?>

 <span id="alert_action"></span>
 <div class="row">
  <div class="col-lg-12">
   <div class="panel panel-default">
                <div class="panel-heading">
                 <div class="row">
                  <div class="col-md-10">
                   <h3 class="panel-title">Brand List</h3>
                  </div>
                  <div class="col-md-2" align="right">
                   <button type="button" name="add" id="add_button" class="btn btn-success btn-xs">Add</button>
                  </div>
                 </div>
                </div>
                <div class="panel-body">
                 <table id="brand_data" class="table table-bordered table-striped">
                  <thead>
       <tr>
        <th>ID</th>
        <th>Category</th>
        <th>Brand Name</th>
        <th>Status</th>
        <th>Edit</th>
        <th>Delete</th>
       </tr>
      </thead>
                 </table>
                </div>
            </div>
        </div>
    </div>

    <div id="brandModal" class="modal fade">
     <div class="modal-dialog">
      <form method="post" id="brand_form">
       <div class="modal-content">
        <div class="modal-header">
         <button type="button" class="close" data-dismiss="modal">&times;</button>
      <h4 class="modal-title"><i class="fa fa-plus"></i> Add Brand</h4>
        </div>
        <div class="modal-body">
         <div class="form-group">
          <select name="category_id" id="category_id" class="form-control" required>
        <option value="">Select Category</option>
        <?php echo fill_category_list($connect); ?>
       </select>
         </div>
         <div class="form-group">
       <label>Enter Brand Name</label>
       <input type="text" name="brand_name" id="brand_name" class="form-control" required />
      </div>
        </div>
        <div class="modal-footer">
         <input type="hidden" name="brand_id" id="brand_id" />
         <input type="hidden" name="btn_action" id="btn_action" />
         <input type="submit" name="action" id="action" class="btn btn-info" value="Add" />
         <button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
        </div>
       </div>
      </form>
     </div>
    </div>

<script>
$(document).ready(function(){

 $('#add_button').click(function(){
  $('#brandModal').modal('show');
  $('#brand_form')[0].reset();
  $('.modal-title').html("<i class='fa fa-plus'></i> Add Brand");
  $('#action').val('Add');
  $('#btn_action').val('Add');
 });

 $(document).on('submit','#brand_form', function(event){
  event.preventDefault();
  $('#action').attr('disabled','disabled');
  var form_data = $(this).serialize();
  $.ajax({
   url:"brand_action.php",
   method:"POST",
   data:form_data,
   success:function(data)
   {
    $('#brand_form')[0].reset();
    $('#brandModal').modal('hide');
    $('#alert_action').fadeIn().html('<div class="alert alert-success">'+data+'</div>');
    $('#action').attr('disabled', false);
    branddataTable.ajax.reload();
   }
  })
 });

 $(document).on('click', '.update', function(){
  var brand_id = $(this).attr("id");
  var btn_action = 'fetch_single';
  $.ajax({
   url:'brand_action.php',
   method:"POST",
   data:{brand_id:brand_id, btn_action:btn_action},
   dataType:"json",
   success:function(data)
   {
    $('#brandModal').modal('show');
    $('#category_id').val(data.category_id);
    $('#brand_name').val(data.brand_name);
    $('.modal-title').html("<i class='fa fa-pencil-square-o'></i> Edit Brand");
    $('#brand_id').val(brand_id);
    $('#action').val('Edit');
    $('#btn_action').val('Edit');
   }
  })
 });

 $(document).on('click','.delete', function(){
  var brand_id = $(this).attr("id");
  var status  = $(this).data('status');
  var btn_action = 'delete';
  if(confirm("Are you sure you want to change status?"))
  {
   $.ajax({
    url:"brand_action.php",
    method:"POST",
    data:{brand_id:brand_id, status:status, btn_action:btn_action},
    success:function(data)
    {
     $('#alert_action').fadeIn().html('<div class="alert alert-info">'+data+'</div>');
     branddataTable.ajax.reload();
    }
   })
  }
  else
  {
   return false;
  }
 });


 var branddataTable = $('#brand_data').DataTable({
  "processing":true,
  "serverSide":true,
  "order":[],
  "ajax":{
   url:"brand_fetch.php",
   type:"POST"
  },
  "columnDefs":[
   {
    "targets":[4, 5],
    "orderable":false,
   },
  ],
  "pageLength": 10
 });

});
</script>


<?php
include('footer.php');
?>


brand_fetch.php



<?php

//brand_fetch.php

include('database_connection.php');

$query = '';

$output = array();
$query .= "
SELECT * FROM brand 
INNER JOIN category ON category.category_id = brand.category_id 
";

if(isset($_POST["search"]["value"]))
{
 $query .= 'WHERE brand.brand_name LIKE "%'.$_POST["search"]["value"].'%" ';
 $query .= 'OR category.category_name LIKE "%'.$_POST["search"]["value"].'%" ';
 $query .= 'OR brand.brand_status LIKE "%'.$_POST["search"]["value"].'%" ';
}

if(isset($_POST["order"]))
{
 $query .= 'ORDER BY '.$_POST['order']['0']['column'].' '.$_POST['order']['0']['dir'].' ';
}
else
{
 $query .= 'ORDER BY brand.brand_id DESC ';
}

if($_POST["length"] != -1)
{
 $query .= 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
}

$statement = $connect->prepare($query);

$statement->execute();

$result = $statement->fetchAll();

$data = array();

$filtered_rows = $statement->rowCount();

foreach($result as $row)
{
 $status = '';
 if($row['brand_status'] == 'active')
 {
  $status = '<span class="label label-success">Active</span>';
 }
 else
 {
  $status = '<span class="label label-danger">Inactive</span>';
 }
 $sub_array = array();
 $sub_array[] = $row['brand_id'];
 $sub_array[] = $row['category_name'];
 $sub_array[] = $row['brand_name'];
 $sub_array[] = $status;
 $sub_array[] = '<button type="button" name="update" id="'.$row["brand_id"].'" class="btn btn-warning btn-xs update">Update</button>';
 $sub_array[] = '<button type="button" name="delete" id="'.$row["brand_id"].'" class="btn btn-danger btn-xs delete" data-status="'.$row["brand_status"].'">Delete</button>';
 $data[] = $sub_array;
}

function get_total_all_records($connect)
{
 $statement = $connect->prepare('SELECT * FROM brand');
 $statement->execute();
 return $statement->rowCount();
}

$output = array(
 "draw"    => intval($_POST["draw"]),
 "recordsTotal"  => $filtered_rows,
 "recordsFiltered" => get_total_all_records($connect),
 "data"    => $data
);

echo json_encode($output);

?>


brand_action.php



<?php

//brand_action.php

include('database_connection.php');

if(isset($_POST['btn_action']))
{
 if($_POST['btn_action'] == 'Add')
 {
  $query = "
  INSERT INTO brand (category_id, brand_name) 
  VALUES (:category_id, :brand_name)
  ";
  $statement = $connect->prepare($query);
  $statement->execute(
   array(
    ':category_id' => $_POST["category_id"],
    ':brand_name' => $_POST["brand_name"]
   )
  );
  $result = $statement->fetchAll();
  if(isset($result))
  {
   echo 'Brand Name Added';
  }
 }

 if($_POST['btn_action'] == 'fetch_single')
 {
  $query = "
  SELECT * FROM brand WHERE brand_id = :brand_id
  ";
  $statement = $connect->prepare($query);
  $statement->execute(
   array(
    ':brand_id' => $_POST["brand_id"]
   )
  );
  $result = $statement->fetchAll();
  foreach($result as $row)
  {
   $output['category_id'] = $row['category_id'];
   $output['brand_name'] = $row['brand_name'];
  }
  echo json_encode($output);
 }
 if($_POST['btn_action'] == 'Edit')
 {
  $query = "
  UPDATE brand set 
  category_id = :category_id, 
  brand_name = :brand_name 
  WHERE brand_id = :brand_id
  ";
  $statement = $connect->prepare($query);
  $statement->execute(
   array(
    ':category_id' => $_POST["category_id"],
    ':brand_name' => $_POST["brand_name"],
    ':brand_id'  => $_POST["brand_id"]
   )
  );
  $result = $statement->fetchAll();
  if(isset($result))
  {
   echo 'Brand Name Edited';
  }
 }

 if($_POST['btn_action'] == 'delete')
 {
  $status = 'active';
  if($_POST['status'] == 'active')
  {
   $status = 'inactive';
  }
  $query = "
  UPDATE brand 
  SET brand_status = :brand_status 
  WHERE brand_id = :brand_id
  ";
  $statement = $connect->prepare($query);
  $statement->execute(
   array(
    ':brand_status' => $status,
    ':brand_id'  => $_POST["brand_id"]
   )
  );
  $result = $statement->fetchAll();
  if(isset($result))
  {
   echo 'Brand status change to ' . $status;
  }
 }
}

?>



product.php



<?php
//product.php

include('database_connection.php');
include('function.php');

if(!isset($_SESSION["type"]))
{
    header('location:login.php');
}

if($_SESSION['type'] != 'master')
{
    header('location:index.php');
}

include('header.php');


?>

<span id='alert_action'></span>
  <div class="row">
   <div class="col-lg-12">
    <div class="panel panel-default">
                    <div class="panel-heading">
                     <div class="row">
                            <div class="col-lg-10 col-md-10 col-sm-8 col-xs-6">
                             <h3 class="panel-title">Product List</h3>
                            </div>
                        
                            <div class="col-lg-2 col-md-2 col-sm-4 col-xs-6" align='right'>
                                <button type="button" name="add" id="add_button" class="btn btn-success btn-xs">Add</button>
                            </div>
                        </div>
                    </div>
                    <div class="panel-body">
                        <div class="row"><div class="col-sm-12 table-responsive">
                            <table id="product_data" class="table table-bordered table-striped">
                                <thead><tr>
                                    <th>ID</th>
                                    <th>Category</th>
                                    <th>Brand</th>
                                    <th>Product Name</th>
                                    <th>Quantity</th>
                                    <th>Enter By</th>
                                    <th>Status</th>
                                    <th></th>
                                    <th></th>
                                    <th></th>
                                </tr></thead>
                            </table>
                        </div></div>
                    </div>
                </div>
   </div>
  </div>

<div id="productModal" class="modal fade">
            <div class="modal-dialog">
                <form method="post" id="product_form">
                    <div class="modal-content">
                        <div class="modal-header">
                            <button type="button" class="close" data-dismiss="modal">&times;</button>
                            <h4 class="modal-title"><i class="fa fa-plus"></i> Add Product</h4>
                        </div>
                        <div class="modal-body">
                            <div class="form-group">
                                <label>Select Category</label>
                                <select name="category_id" id="category_id" class="form-control" required>
                                    <option value="">Select Category</option>
                                    <?php echo fill_category_list($connect);?>
                                </select>
                            </div>
                            <div class="form-group">
                                <label>Select Brand</label>
                                <select name="brand_id" id="brand_id" class="form-control" required>
                                    <option value="">Select Brand</option>
                                </select>
                            </div>
                            <div class="form-group">
                                <label>Enter Product Name</label>
                                <input type="text" name="product_name" id="product_name" class="form-control" required />
                            </div>
                            <div class="form-group">
                                <label>Enter Product Description</label>
                                <textarea name="product_description" id="product_description" class="form-control" rows="5" required></textarea>
                            </div>
                            <div class="form-group">
                                <label>Enter Product Quantity</label>
                                <div class="input-group">
                                    <input type="text" name="product_quantity" id="product_quantity" class="form-control" required pattern="[+-]?([0-9]*[.])?[0-9]+" /> 
                                    <span class="input-group-addon">
                                        <select name="product_unit" id="product_unit" required>
                                            <option value="">Select Unit</option>
                                            <option value="Bags">Bags</option>
                                            <option value="Bottles">Bottles</option>
                                            <option value="Box">Box</option>
                                            <option value="Dozens">Dozens</option>
                                            <option value="Feet">Feet</option>
                                            <option value="Gallon">Gallon</option>
                                            <option value="Grams">Grams</option>
                                            <option value="Inch">Inch</option>
                                            <option value="Kg">Kg</option>
                                            <option value="Liters">Liters</option>
                                            <option value="Meter">Meter</option>
                                            <option value="Nos">Nos</option>
                                            <option value="Packet">Packet</option>
                                            <option value="Rolls">Rolls</option>
                                        </select>
                                    </span>
                                </div>
                            </div>
                            <div class="form-group">
                                <label>Enter Product Base Price</label>
                                <input type="text" name="product_base_price" id="product_base_price" class="form-control" required pattern="[+-]?([0-9]*[.])?[0-9]+" />
                            </div>
                            <div class="form-group">
                                <label>Enter Product Tax (%)</label>
                                <input type="text" name="product_tax" id="product_tax" class="form-control" required pattern="[+-]?([0-9]*[.])?[0-9]+" />
                            </div>
                        </div>
                        <div class="modal-footer">
                            <input type="hidden" name="product_id" id="product_id" />
                            <input type="hidden" name="btn_action" id="btn_action" />
                            <input type="submit" name="action" id="action" class="btn btn-info" value="Add" />
                            <button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
                        </div>
                    </div>
                </form>
            </div>
        </div>

<div id="productdetailsModal" class="modal fade">
            <div class="modal-dialog">
                <form method="post" id="product_form">
                    <div class="modal-content">
                        <div class="modal-header">
                            <button type="button" class="close" data-dismiss="modal">&times;</button>
                            <h4 class="modal-title"><i class="fa fa-plus"></i> Product Details</h4>
                        </div>
                        <div class="modal-body">
                            <Div id="product_details"></Div>
                        </div>
                        <div class="modal-footer">
                            
                            <button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
                        </div>
                    </div>
                </form>
            </div>
        </div>

<script>
$(document).ready(function(){
    var productdataTable = $('#product_data').DataTable({
        "processing":true,
        "serverSide":true,
        "order":[],
        "ajax":{
            url:"product_fetch.php",
            type:"POST"
        },
        "columnDefs":[
            {
                "targets":[7, 8, 9],
                "orderable":false,
            },
        ],
        "pageLength": 10
    });

    $('#add_button').click(function(){
        $('#productModal').modal('show');
        $('#product_form')[0].reset();
        $('.modal-title').html("<i class='fa fa-plus'></i> Add Product");
        $('#action').val("Add");
        $('#btn_action').val("Add");
    });

    $('#category_id').change(function(){
        var category_id = $('#category_id').val();
        var btn_action = 'load_brand';
        $.ajax({
            url:"product_action.php",
            method:"POST",
            data:{category_id:category_id, btn_action:btn_action},
            success:function(data)
            {
                $('#brand_id').html(data);
            }
        });
    });

    $(document).on('submit', '#product_form', function(event){
        event.preventDefault();
        $('#action').attr('disabled', 'disabled');
        var form_data = $(this).serialize();
        $.ajax({
            url:"product_action.php",
            method:"POST",
            data:form_data,
            success:function(data)
            {
                $('#product_form')[0].reset();
                $('#productModal').modal('hide');
                $('#alert_action').fadeIn().html('<div class="alert alert-success">'+data+'</div>');
                $('#action').attr('disabled', false);
                productdataTable.ajax.reload();
            }
        })
    });

    $(document).on('click', '.view', function(){
        var product_id = $(this).attr("id");
        var btn_action = 'product_details';
        $.ajax({
            url:"product_action.php",
            method:"POST",
            data:{product_id:product_id, btn_action:btn_action},
            success:function(data){
                $('#productdetailsModal').modal('show');
                $('#product_details').html(data);
            }
        })
    });

    $(document).on('click', '.update', function(){
        var product_id = $(this).attr("id");
        var btn_action = 'fetch_single';
        $.ajax({
            url:"product_action.php",
            method:"POST",
            data:{product_id:product_id, btn_action:btn_action},
            dataType:"json",
            success:function(data){
                $('#productModal').modal('show');
                $('#category_id').val(data.category_id);
                $('#brand_id').html(data.brand_select_box);
                $('#brand_id').val(data.brand_id);
                $('#product_name').val(data.product_name);
                $('#product_description').val(data.product_description);
                $('#product_quantity').val(data.product_quantity);
                $('#product_unit').val(data.product_unit);
                $('#product_base_price').val(data.product_base_price);
                $('#product_tax').val(data.product_tax);
                $('.modal-title').html("<i class='fa fa-pencil-square-o'></i> Edit Product");
                $('#product_id').val(product_id);
                $('#action').val("Edit");
                $('#btn_action').val("Edit");
            }
        })
    });

    $(document).on('click', '.delete', function(){
        var product_id = $(this).attr("id");
        var status = $(this).data("status");
        var btn_action = 'delete';
        if(confirm("Are you sure you want to change status?"))
        {
            $.ajax({
                url:"product_action.php",
                method:"POST",
                data:{product_id:product_id, status:status, btn_action:btn_action},
                success:function(data){
                    $('#alert_action').fadeIn().html('<div class="alert alert-info">'+data+'</div>');
                    productdataTable.ajax.reload();
                }
            });
        }
        else
        {
            return false;
        }
    });

});
</script>



product_fetch.php



<?php

//product_fetch.php

include('database_connection.php');

$query = '';

$output = array();
$query .= "
 SELECT * FROM product 
INNER JOIN brand ON brand.brand_id = product.brand_id
INNER JOIN category ON category.category_id = product.category_id 
INNER JOIN user_details ON user_details.user_id = product.product_enter_by 
";

if(isset($_POST["search"]["value"]))
{
 $query .= 'WHERE brand.brand_name LIKE "%'.$_POST["search"]["value"].'%" ';
 $query .= 'OR category.category_name LIKE "%'.$_POST["search"]["value"].'%" ';
 $query .= 'OR product.product_name LIKE "%'.$_POST["search"]["value"].'%" ';
 $query .= 'OR product.product_quantity LIKE "%'.$_POST["search"]["value"].'%" ';
 $query .= 'OR user_details.user_name LIKE "%'.$_POST["search"]["value"].'%" ';
 $query .= 'OR product.product_id LIKE "%'.$_POST["search"]["value"].'%" ';
}

if(isset($_POST['order']))
{
 $query .= 'ORDER BY '.$_POST['order']['0']['column'].' '.$_POST['order']['0']['dir'].' ';
}
else
{
 $query .= 'ORDER BY product_id DESC ';
}

if($_POST['length'] != -1)
{
 $query .= 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
}
$statement = $connect->prepare($query);
$statement->execute();
$result = $statement->fetchAll();
$data = array();
$filtered_rows = $statement->rowCount();
foreach($result as $row)
{
 $status = '';
 if($row['product_status'] == 'active')
 {
  $status = '<span class="label label-success">Active</span>';
 }
 else
 {
  $status = '<span class="label label-danger">Inactive</span>';
 }
 $sub_array = array();
 $sub_array[] = $row['product_id'];
 $sub_array[] = $row['category_name'];
 $sub_array[] = $row['brand_name'];
 $sub_array[] = $row['product_name'];
 $sub_array[] = $row['product_quantity'] . ' ' . $row['product_unit'];
 $sub_array[] = $row['user_name'];
 $sub_array[] = $status;
 $sub_array[] = '<button type="button" name="view" id="'.$row["product_id"].'" class="btn btn-info btn-xs view">View</button>';
 $sub_array[] = '<button type="button" name="update" id="'.$row["product_id"].'" class="btn btn-warning btn-xs update">Update</button>';
 $sub_array[] = '<button type="button" name="delete" id="'.$row["product_id"].'" class="btn btn-danger btn-xs delete" data-status="'.$row["product_status"].'">Delete</button>';
 $data[] = $sub_array;
}

function get_total_all_records($connect)
{
 $statement = $connect->prepare('SELECT * FROM product');
 $statement->execute();
 return $statement->rowCount();
}

$output = array(
 "draw"       =>  intval($_POST["draw"]),
 "recordsTotal"   =>  $filtered_rows,
 "recordsFiltered"  =>  get_total_all_records($connect),
 "data"       =>  $data
);

echo json_encode($output);

?>


product_action.php



<?php

//product_action.php

include('database_connection.php');

include('function.php');


if(isset($_POST['btn_action']))
{
 if($_POST['btn_action'] == 'load_brand')
 {
  echo fill_brand_list($connect, $_POST['category_id']);
 }

 if($_POST['btn_action'] == 'Add')
 {
  $query = "
  INSERT INTO product (category_id, brand_id, product_name, product_description, product_quantity, product_unit, product_base_price, product_tax, product_enter_by, product_status, product_date) 
  VALUES (:category_id, :brand_id, :product_name, :product_description, :product_quantity, :product_unit, :product_base_price, :product_tax, :product_enter_by, :product_status, :product_date)
  ";
  $statement = $connect->prepare($query);
  $statement->execute(
   array(
    ':category_id'   => $_POST['category_id'],
    ':brand_id'    => $_POST['brand_id'],
    ':product_name'   => $_POST['product_name'],
    ':product_description' => $_POST['product_description'],
    ':product_quantity'  => $_POST['product_quantity'],
    ':product_unit'   => $_POST['product_unit'],
    ':product_base_price' => $_POST['product_base_price'],
    ':product_tax'   => $_POST['product_tax'],
    ':product_enter_by'  => $_SESSION["user_id"],
    ':product_status'  => 'active',
    ':product_date'   => date("Y-m-d")
   )
  );
  $result = $statement->fetchAll();
  if(isset($result))
  {
   echo 'Product Added';
  }
 }
 if($_POST['btn_action'] == 'product_details')
 {
  $query = "
  SELECT * FROM product 
  INNER JOIN category ON category.category_id = product.category_id 
  INNER JOIN brand ON brand.brand_id = product.brand_id 
  INNER JOIN user_details ON user_details.user_id = product.product_enter_by 
  WHERE product.product_id = '".$_POST["product_id"]."'
  ";
  $statement = $connect->prepare($query);
  $statement->execute();
  $result = $statement->fetchAll();
  $output = '
  <div class="table-responsive">
   <table class="table table-boredered">
  ';
  foreach($result as $row)
  {
   $status = '';
   if($row['product_status'] == 'active')
   {
    $status = '<span class="label label-success">Active</span>';
   }
   else
   {
    $status = '<span class="label label-danger">Inactive</span>';
   }
   $output .= '
   <tr>
    <td>Product Name</td>
    <td>'.$row["product_name"].'</td>
   </tr>
   <tr>
    <td>Product Description</td>
    <td>'.$row["product_description"].'</td>
   </tr>
   <tr>
    <td>Category</td>
    <td>'.$row["category_name"].'</td>
   </tr>
   <tr>
    <td>Brand</td>
    <td>'.$row["brand_name"].'</td>
   </tr>
   <tr>
    <td>Available Quantity</td>
    <td>'.$row["product_quantity"].' '.$row["product_unit"].'</td>
   </tr>
   <tr>
    <td>Base Price</td>
    <td>'.$row["product_base_price"].'</td>
   </tr>
   <tr>
    <td>Tax (%)</td>
    <td>'.$row["product_tax"].'</td>
   </tr>
   <tr>
    <td>Enter By</td>
    <td>'.$row["user_name"].'</td>
   </tr>
   <tr>
    <td>Status</td>
    <td>'.$status.'</td>
   </tr>
   ';
  }
  $output .= '
   </table>
  </div>
  ';
  echo $output;
 }
 if($_POST['btn_action'] == 'fetch_single')
 {
  $query = "
  SELECT * FROM product WHERE product_id = :product_id
  ";
  $statement = $connect->prepare($query);
  $statement->execute(
   array(
    ':product_id' => $_POST["product_id"]
   )
  );
  $result = $statement->fetchAll();
  foreach($result as $row)
  {
   $output['category_id'] = $row['category_id'];
   $output['brand_id'] = $row['brand_id'];
   $output["brand_select_box"] = fill_brand_list($connect, $row["category_id"]);
   $output['product_name'] = $row['product_name'];
   $output['product_description'] = $row['product_description'];
   $output['product_quantity'] = $row['product_quantity'];
   $output['product_unit'] = $row['product_unit'];

   $output['product_base_price'] = $row['product_base_price'];
   $output['product_tax'] = $row['product_tax'];
  }
  echo json_encode($output);
 }

 if($_POST['btn_action'] == 'Edit')
 {
  $query = "
  UPDATE product 
  set category_id = :category_id, 
  brand_id = :brand_id,
  product_name = :product_name,
  product_description = :product_description, 
  product_quantity = :product_quantity, 
  product_unit = :product_unit, 
  product_base_price = :product_base_price, 
  product_tax = :product_tax 
  WHERE product_id = :product_id
  ";
  $statement = $connect->prepare($query);
  $statement->execute(
   array(
    ':category_id'   => $_POST['category_id'],
    ':brand_id'    => $_POST['brand_id'],
    ':product_name'   => $_POST['product_name'],
    ':product_description' => $_POST['product_description'],
    ':product_quantity'  => $_POST['product_quantity'],
    ':product_unit'   => $_POST['product_unit'],
    ':product_base_price' => $_POST['product_base_price'],
    ':product_tax'   => $_POST['product_tax'],
    ':product_id'   => $_POST['product_id']
   )
  );
  $result = $statement->fetchAll();
  if(isset($result))
  {
   echo 'Product Details Edited';
  }
 }
 if($_POST['btn_action'] == 'delete')
 {
  $status = 'active';
  if($_POST['status'] == 'active')
  {
   $status = 'inactive';
  }
  $query = "
  UPDATE product 
  SET product_status = :product_status 
  WHERE product_id = :product_id
  ";
  $statement = $connect->prepare($query);
  $statement->execute(
   array(
    ':product_status' => $status,
    ':product_id'  => $_POST["product_id"]
   )
  );
  $result = $statement->fetchAll();
  if(isset($result))
  {
   echo 'Product status change to ' . $status;
  }
 }
}


?>



order.php



<?php
//order.php

include('database_connection.php');

include('function.php');

if(!isset($_SESSION['type']))
{
 header('location:login.php');
}

include('header.php');


?>
 <link rel="stylesheet" href="css/datepicker.css">
 <script src="js/bootstrap-datepicker1.js"></script>
 <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-select/1.12.2/css/bootstrap-select.min.css">
 <script src="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-select/1.12.2/js/bootstrap-select.min.js"></script>

 <script>
 $(document).ready(function(){
  $('#inventory_order_date').datepicker({
   format: "yyyy-mm-dd",
   autoclose: true
  });
 });
 </script>

 <span id="alert_action"></span>
 <div class="row">
  <div class="col-lg-12">
   
   <div class="panel panel-default">
                <div class="panel-heading">
                 <div class="row">
                     <div class="col-lg-10 col-md-10 col-sm-8 col-xs-6">
                            <h3 class="panel-title">Order List</h3>
                        </div>
                        <div class="col-lg-2 col-md-2 col-sm-4 col-xs-6" align="right">
                            <button type="button" name="add" id="add_button" class="btn btn-success btn-xs">Add</button>     
                        </div>
                    </div>
                </div>
                <div class="panel-body">
                 <table id="order_data" class="table table-bordered table-striped">
                  <thead>
       <tr>
        <th>Order ID</th>
        <th>Customer Name</th>
        <th>Total Amount</th>
        <th>Payment Status</th>
        <th>Order Status</th>
        <th>Order Date</th>
        <?php
        if($_SESSION['type'] == 'master')
        {
         echo '<th>Created By</th>';
        }
        ?>
        <th></th>
        <th></th>
        <th></th>
       </tr>
      </thead>
                 </table>
                </div>
            </div>
        </div>
    </div>

    <div id="orderModal" class="modal fade">

     <div class="modal-dialog">
      <form method="post" id="order_form">
       <div class="modal-content">
        <div class="modal-header">
         <button type="button" class="close" data-dismiss="modal">&times;</button>
      <h4 class="modal-title"><i class="fa fa-plus"></i> Create Order</h4>
        </div>
        <div class="modal-body">
         <div class="row">
       <div class="col-md-6">
        <div class="form-group">
         <label>Enter Receiver Name</label>
         <input type="text" name="inventory_order_name" id="inventory_order_name" class="form-control" required />
        </div>
       </div>
       <div class="col-md-6">
        <div class="form-group">
         <label>Date</label>
         <input type="text" name="inventory_order_date" id="inventory_order_date" class="form-control" required />
        </div>
       </div>
      </div>
      <div class="form-group">
       <label>Enter Receiver Address</label>
       <textarea name="inventory_order_address" id="inventory_order_address" class="form-control" required></textarea>
      </div>
      <div class="form-group">
       <label>Enter Product Details</label>
       <hr />
       <span id="span_product_details"></span>
       <hr />
      </div>
      <div class="form-group">
       <label>Select Payment Status</label>
       <select name="payment_status" id="payment_status" class="form-control">
        <option value="cash">Cash</option>
        <option value="credit">Credit</option>
       </select>
      </div>
        </div>
        <div class="modal-footer">
         <input type="hidden" name="inventory_order_id" id="inventory_order_id" />
         <input type="hidden" name="btn_action" id="btn_action" />
         <input type="submit" name="action" id="action" class="btn btn-info" value="Add" />
        </div>
       </div>
      </form>
     </div>

    </div>

<script type="text/javascript">
    $(document).ready(function(){

     var orderdataTable = $('#order_data').DataTable({
   "processing":true,
   "serverSide":true,
   "order":[],
   "ajax":{
    url:"order_fetch.php",
    type:"POST"
   },
   <?php
   if($_SESSION["type"] == 'master')
   {
   ?>
   "columnDefs":[
    {
     "targets":[4, 5, 6, 7, 8, 9],
     "orderable":false,
    },
   ],
   <?php
   }
   else
   {
   ?>
   "columnDefs":[
    {
     "targets":[4, 5, 6, 7, 8],
     "orderable":false,
    },
   ],
   <?php
   }
   ?>
   "pageLength": 10
  });

  $('#add_button').click(function(){
   $('#orderModal').modal('show');
   $('#order_form')[0].reset();
   $('.modal-title').html("<i class='fa fa-plus'></i> Create Order");
   $('#action').val('Add');
   $('#btn_action').val('Add');
   $('#span_product_details').html('');
   add_product_row();
  });

  function add_product_row(count = '')
  {
   var html = '';
   html += '<span id="row'+count+'"><div class="row">';
   html += '<div class="col-md-8">';
   html += '<select name="product_id[]" id="product_id'+count+'" class="form-control selectpicker" data-live-search="true" required>';
   html += '<?php echo fill_product_list($connect); ?>';
   html += '</select><input type="hidden" name="hidden_product_id[]" id="hidden_product_id'+count+'" />';
   html += '</div>';
   html += '<div class="col-md-3">';
   html += '<input type="text" name="quantity[]" class="form-control" required />';
   html += '</div>';
   html += '<div class="col-md-1">';
   if(count == '')
   {
    html += '<button type="button" name="add_more" id="add_more" class="btn btn-success btn-xs">+</button>';
   }
   else
   {
    html += '<button type="button" name="remove" id="'+count+'" class="btn btn-danger btn-xs remove">-</button>';
   }
   html += '</div>';
   html += '</div></div><br /></span>';
   $('#span_product_details').append(html);

   $('.selectpicker').selectpicker();
  }

  var count = 0;

  $(document).on('click', '#add_more', function(){
   count = count + 1;
   add_product_row(count);
  });
  $(document).on('click', '.remove', function(){
   var row_no = $(this).attr("id");
   $('#row'+row_no).remove();
  });

  $(document).on('submit', '#order_form', function(event){
   event.preventDefault();
   $('#action').attr('disabled', 'disabled');
   var form_data = $(this).serialize();
   $.ajax({
    url:"order_action.php",
    method:"POST",
    data:form_data,
    success:function(data){
     $('#order_form')[0].reset();
     $('#orderModal').modal('hide');
     $('#alert_action').fadeIn().html('<div class="alert alert-success">'+data+'</div>');
     $('#action').attr('disabled', false);
     orderdataTable.ajax.reload();
    }
   });
  });

  $(document).on('click', '.update', function(){
   var inventory_order_id = $(this).attr("id");
   var btn_action = 'fetch_single';
   $.ajax({
    url:"order_action.php",
    method:"POST",
    data:{inventory_order_id:inventory_order_id, btn_action:btn_action},
    dataType:"json",
    success:function(data)
    {
     $('#orderModal').modal('show');
     $('#inventory_order_name').val(data.inventory_order_name);
     $('#inventory_order_date').val(data.inventory_order_date);
     $('#inventory_order_address').val(data.inventory_order_address);
     $('#span_product_details').html(data.product_details);
     $('#payment_status').val(data.payment_status);
     $('.modal-title').html("<i class='fa fa-pencil-square-o'></i> Edit Order");
     $('#inventory_order_id').val(inventory_order_id);
     $('#action').val('Edit');
     $('#btn_action').val('Edit');
    }
   })
  });

  $(document).on('click', '.delete', function(){
   var inventory_order_id = $(this).attr("id");
   var status = $(this).data("status");
   var btn_action = "delete";
   if(confirm("Are you sure you want to change status?"))
   {
    $.ajax({
     url:"order_action.php",
     method:"POST",
     data:{inventory_order_id:inventory_order_id, status:status, btn_action:btn_action},
     success:function(data)
     {
      $('#alert_action').fadeIn().html('<div class="alert alert-info">'+data+'</div>');
      orderdataTable.ajax.reload();
     }
    })
   }
   else
   {
    return false;
   }
  });

    });
</script>


order_fetch.php



<?php

//order_fetch.php

include('database_connection.php');

include('function.php');

$query = '';

$output = array();

$query .= "
 SELECT * FROM inventory_order WHERE 
";

if($_SESSION['type'] == 'user')
{
 $query .= 'user_id = "'.$_SESSION["user_id"].'" AND ';
}

if(isset($_POST["search"]["value"]))
{
 $query .= '(inventory_order_id LIKE "%'.$_POST["search"]["value"].'%" ';
 $query .= 'OR inventory_order_name LIKE "%'.$_POST["search"]["value"].'%" ';
 $query .= 'OR inventory_order_total LIKE "%'.$_POST["search"]["value"].'%" ';
 $query .= 'OR inventory_order_status LIKE "%'.$_POST["search"]["value"].'%" ';
 $query .= 'OR inventory_order_date LIKE "%'.$_POST["search"]["value"].'%") ';
}

if(isset($_POST["order"]))
{
 $query .= 'ORDER BY '.$_POST['order']['0']['column'].' '.$_POST['order']['0']['dir'].' ';
}
else
{
 $query .= 'ORDER BY inventory_order_id DESC ';
}

if($_POST["length"] != -1)
{
 $query .= 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
}

$statement = $connect->prepare($query);
$statement->execute();
$result = $statement->fetchAll();
$data = array();
$filtered_rows = $statement->rowCount();
foreach($result as $row)
{
 $payment_status = '';

 if($row['payment_status'] == 'cash')
 {
  $payment_status = '<span class="label label-primary">Cash</span>';
 }
 else
 {
  $payment_status = '<span class="label label-warning">Credit</span>';
 }

 $status = '';
 if($row['inventory_order_status'] == 'active')
 {
  $status = '<span class="label label-success">Active</span>';
 }
 else
 {
  $status = '<span class="label label-danger">Inactive</span>';
 }
 $sub_array = array();
 $sub_array[] = $row['inventory_order_id'];
 $sub_array[] = $row['inventory_order_name'];
 $sub_array[] = $row['inventory_order_total'];
 $sub_array[] = $payment_status;
 $sub_array[] = $status;
 $sub_array[] = $row['inventory_order_date'];
 if($_SESSION['type'] == 'master')
 {
  $sub_array[] = get_user_name($connect, $row['user_id']);
 }
 $sub_array[] = '<a href="view_order.php?pdf=1&order_id='.$row["inventory_order_id"].'" class="btn btn-info btn-xs">View PDF</a>';
 $sub_array[] = '<button type="button" name="update" id="'.$row["inventory_order_id"].'" class="btn btn-warning btn-xs update">Update</button>';
 $sub_array[] = '<button type="button" name="delete" id="'.$row["inventory_order_id"].'" class="btn btn-danger btn-xs delete" data-status="'.$row["inventory_order_status"].'">Delete</button>';
 $data[] = $sub_array;
}

function get_total_all_records($connect)
{
 $statement = $connect->prepare("SELECT * FROM inventory_order");
 $statement->execute();
 return $statement->rowCount();
}

$output = array(
 "draw"       =>  intval($_POST["draw"]),
 "recordsTotal"   =>  $filtered_rows,
 "recordsFiltered"  =>  get_total_all_records($connect),
 "data"       =>  $data
); 

echo json_encode($output);

?>


order_action.php



<?php

//order_action.php

include('database_connection.php');

include('function.php');

if(isset($_POST['btn_action']))
{
 if($_POST['btn_action'] == 'Add')
 {
  $query = "
  INSERT INTO inventory_order (user_id, inventory_order_total, inventory_order_date, inventory_order_name, inventory_order_address, payment_status, inventory_order_status, inventory_order_created_date) 
  VALUES (:user_id, :inventory_order_total, :inventory_order_date, :inventory_order_name, :inventory_order_address, :payment_status, :inventory_order_status, :inventory_order_created_date)
  ";
  $statement = $connect->prepare($query);
  $statement->execute(
   array(
    ':user_id'      => $_SESSION["user_id"],
    ':inventory_order_total'  => 0,
    ':inventory_order_date'   => $_POST['inventory_order_date'],
    ':inventory_order_name'   => $_POST['inventory_order_name'],
    ':inventory_order_address'  => $_POST['inventory_order_address'],
    ':payment_status'    => $_POST['payment_status'],
    ':inventory_order_status'  => 'active',
    ':inventory_order_created_date' => date("Y-m-d")
   )
  );
  $result = $statement->fetchAll();
  $statement = $connect->query("SELECT LAST_INSERT_ID()");
  $inventory_order_id = $statement->fetchColumn();

  if(isset($inventory_order_id))
  {
   $total_amount = 0;
   for($count = 0; $count<count($_POST["product_id"]); $count++)
   {
    $product_details = fetch_product_details($_POST["product_id"][$count], $connect);
    $sub_query = "
    INSERT INTO inventory_order_product (inventory_order_id, product_id, quantity, price, tax) VALUES (:inventory_order_id, :product_id, :quantity, :price, :tax)
    ";
    $statement = $connect->prepare($sub_query);
    $statement->execute(
     array(
      ':inventory_order_id' => $inventory_order_id,
      ':product_id'   => $_POST["product_id"][$count],
      ':quantity'    => $_POST["quantity"][$count],
      ':price'    => $product_details['price'],
      ':tax'     => $product_details['tax']
     )
    );
    $base_price = $product_details['price'] * $_POST["quantity"][$count];
    $tax = ($base_price/100)*$product_details['tax'];
    $total_amount = $total_amount + ($base_price + $tax);
   }
   $update_query = "
   UPDATE inventory_order 
   SET inventory_order_total = '".$total_amount."' 
   WHERE inventory_order_id = '".$inventory_order_id."'
   ";
   $statement = $connect->prepare($update_query);
   $statement->execute();
   $result = $statement->fetchAll();
   if(isset($result))
   {
    echo 'Order Created...';
    echo '<br />';
    echo $total_amount;
    echo '<br />';
    echo $inventory_order_id;
   }
  }
 }

 if($_POST['btn_action'] == 'fetch_single')
 {
  $query = "
  SELECT * FROM inventory_order WHERE inventory_order_id = :inventory_order_id
  ";
  $statement = $connect->prepare($query);
  $statement->execute(
   array(
    ':inventory_order_id' => $_POST["inventory_order_id"]
   )
  );
  $result = $statement->fetchAll();
  $output = array();
  foreach($result as $row)
  {
   $output['inventory_order_name'] = $row['inventory_order_name'];
   $output['inventory_order_date'] = $row['inventory_order_date'];
   $output['inventory_order_address'] = $row['inventory_order_address'];
   $output['payment_status'] = $row['payment_status'];
  }
  $sub_query = "
  SELECT * FROM inventory_order_product 
  WHERE inventory_order_id = '".$_POST["inventory_order_id"]."'
  ";
  $statement = $connect->prepare($sub_query);
  $statement->execute();
  $sub_result = $statement->fetchAll();
  $product_details = '';
  $count = '';
  foreach($sub_result as $sub_row)
  {
   $product_details .= '
   <script>
   $(document).ready(function(){
    $("#product_id'.$count.'").selectpicker("val", '.$sub_row["product_id"].');
    $(".selectpicker").selectpicker();
   });
   </script>
   <span id="row'.$count.'">
    <div class="row">
     <div class="col-md-8">
      <select name="product_id[]" id="product_id'.$count.'" class="form-control selectpicker" data-live-search="true" required>
       '.fill_product_list($connect).'
      </select>
      <input type="hidden" name="hidden_product_id[]" id="hidden_product_id'.$count.'" value="'.$sub_row["product_id"].'" />
     </div>
     <div class="col-md-3">
      <input type="text" name="quantity[]" class="form-control" value="'.$sub_row["quantity"].'" required />
     </div>
     <div class="col-md-1">
   ';

   if($count == '')
   {
    $product_details .= '<button type="button" name="add_more" id="add_more" class="btn btn-success btn-xs">+</button>';
   }
   else
   {
    $product_details .= '<button type="button" name="remove" id="'.$count.'" class="btn btn-danger btn-xs remove">-</button>';
   }
   $product_details .= '
      </div>
     </div>
    </div><br />
   </span>
   ';
   $count = $count + 1;
  }
  $output['product_details'] = $product_details;
  echo json_encode($output);
 }
 
 if($_POST['btn_action'] == 'Edit')
 {
  $delete_query = "
  DELETE FROM inventory_order_product 
  WHERE inventory_order_id = '".$_POST["inventory_order_id"]."'
  ";
  $statement = $connect->prepare($delete_query);
  $statement->execute();
  $delete_result = $statement->fetchAll();
  if(isset($delete_result))
  {
   $total_amount = 0;
   for($count = 0; $count < count($_POST["product_id"]); $count++)
   {
    $product_details = fetch_product_details($_POST["product_id"][$count], $connect);
    $sub_query = "
    INSERT INTO inventory_order_product (inventory_order_id, product_id, quantity, price, tax) VALUES (:inventory_order_id, :product_id, :quantity, :price, :tax)
    ";
    $statement = $connect->prepare($sub_query);
    $statement->execute(
     array(
      ':inventory_order_id' => $_POST["inventory_order_id"],
      ':product_id'   => $_POST["product_id"][$count],
      ':quantity'    => $_POST["quantity"][$count],
      ':price'    => $product_details['price'],
      ':tax'     => $product_details['tax']
     )
    );
    $base_price = $product_details['price'] * $_POST["quantity"][$count];
    $tax = ($base_price/100)*$product_details['tax'];
    $total_amount = $total_amount + ($base_price + $tax);
   }
   $update_query = "
   UPDATE inventory_order 
   SET inventory_order_name = :inventory_order_name, 
   inventory_order_date = :inventory_order_date, 
   inventory_order_address = :inventory_order_address, 
   inventory_order_total = :inventory_order_total, 
   payment_status = :payment_status
   WHERE inventory_order_id = :inventory_order_id
   ";
   $statement = $connect->prepare($update_query);
   $statement->execute(
    array(
     ':inventory_order_name'   => $_POST["inventory_order_name"],
     ':inventory_order_date'   => $_POST["inventory_order_date"],
     ':inventory_order_address'  => $_POST["inventory_order_address"],
     ':inventory_order_total'  => $total_amount,
     ':payment_status'    => $_POST["payment_status"],
     ':inventory_order_id'   => $_POST["inventory_order_id"]
    )
   );
   $result = $statement->fetchAll();
   if(isset($result))
   {
    echo 'Order Edited...';
   }
  }
 }

 if($_POST['btn_action'] == 'delete')
 {
  $status = 'active';
  if($_POST['status'] == 'active')
  {
   $status = 'inactive';
  }
  $query = "
  UPDATE inventory_order 
  SET inventory_order_status = :inventory_order_status 
  WHERE inventory_order_id = :inventory_order_id
  ";
  $statement = $connect->prepare($query);
  $statement->execute(
   array(
    ':inventory_order_status' => $status,
    ':inventory_order_id'  => $_POST["inventory_order_id"]
   )
  );
  $result = $statement->fetchAll();
  if(isset($result))
  {
   echo 'Order status change to ' . $status;
  }
 }
}

?>


function.php



<?php
//function.php

function fill_category_list($connect)
{
 $query = "
 SELECT * FROM category 
 WHERE category_status = 'active' 
 ORDER BY category_name ASC
 ";
 $statement = $connect->prepare($query);
 $statement->execute();
 $result = $statement->fetchAll();
 $output = '';
 foreach($result as $row)
 {
  $output .= '<option value="'.$row["category_id"].'">'.$row["category_name"].'</option>';
 }
 return $output;
}

function fill_brand_list($connect, $category_id)
{
 $query = "SELECT * FROM brand 
 WHERE brand_status = 'active' 
 AND category_id = '".$category_id."'
 ORDER BY brand_name ASC";
 $statement = $connect->prepare($query);
 $statement->execute();
 $result = $statement->fetchAll();
 $output = '<option value="">Select Brand</option>';
 foreach($result as $row)
 {
  $output .= '<option value="'.$row["brand_id"].'">'.$row["brand_name"].'</option>';
 }
 return $output;
}

function get_user_name($connect, $user_id)
{
 $query = "
 SELECT user_name FROM user_details WHERE user_id = '".$user_id."'
 ";
 $statement = $connect->prepare($query);
 $statement->execute();
 $result = $statement->fetchAll();
 foreach($result as $row)
 {
  return $row['user_name'];
 }
}

function fill_product_list($connect)
{
 $query = "
 SELECT * FROM product 
 WHERE product_status = 'active' 
 ORDER BY product_name ASC
 ";
 $statement = $connect->prepare($query);
 $statement->execute();
 $result = $statement->fetchAll();
 $output = '';
 foreach($result as $row)
 {
  $output .= '<option value="'.$row["product_id"].'">'.$row["product_name"].'</option>';
 }
 return $output;
}

function fetch_product_details($product_id, $connect)
{
 $query = "
 SELECT * FROM product 
 WHERE product_id = '".$product_id."'";
 $statement = $connect->prepare($query);
 $statement->execute();
 $result = $statement->fetchAll();
 foreach($result as $row)
 {
  $output['product_name'] = $row["product_name"];
  $output['quantity'] = $row["product_quantity"];
  $output['price'] = $row['product_base_price'];
  $output['tax'] = $row['product_tax'];
 }
 return $output;
}

function available_product_quantity($connect, $product_id)
{
 $product_data = fetch_product_details($product_id, $connect);
 $query = "
 SELECT  inventory_order_product.quantity FROM inventory_order_product 
 INNER JOIN inventory_order ON inventory_order.inventory_order_id = inventory_order_product.inventory_order_id
 WHERE inventory_order_product.product_id = '".$product_id."' AND
 inventory_order.inventory_order_status = 'active'
 ";
 $statement = $connect->prepare($query);
 $statement->execute();
 $result = $statement->fetchAll();
 $total = 0;
 foreach($result as $row)
 {
  $total = $total + $row['quantity'];
 }
 $available_quantity = intval($product_data['quantity']) - intval($total);
 if($available_quantity == 0)
 {
  $update_query = "
  UPDATE product SET 
  product_status = 'inactive' 
  WHERE product_id = '".$product_id."'
  ";
  $statement = $connect->prepare($update_query);
  $statement->execute();
 }
 return $available_quantity;
}

function count_total_user($connect)
{
 $query = "
 SELECT * FROM user_details WHERE user_status='active'";
 $statement = $connect->prepare($query);
 $statement->execute();
 return $statement->rowCount();
}

function count_total_category($connect)
{
 $query = "
 SELECT * FROM category WHERE category_status='active'
 ";
 $statement = $connect->prepare($query);
 $statement->execute();
 return $statement->rowCount();
}
function count_total_brand($connect)
{
 $query = "
 SELECT * FROM brand WHERE brand_status='active'
 ";
 $statement = $connect->prepare($query);
 $statement->execute();
 return $statement->rowCount();
}

function count_total_product($connect)
{
 $query = "
 SELECT * FROM product WHERE product_status='active'
 ";
 $statement = $connect->prepare($query);
 $statement->execute();
 return $statement->rowCount();
}

function count_total_order_value($connect)
{
 $query = "
 SELECT sum(inventory_order_total) as total_order_value FROM inventory_order 
 WHERE inventory_order_status='active'
 ";
 if($_SESSION['type'] == 'user')
 {
  $query .= ' AND user_id = "'.$_SESSION["user_id"].'"';
 }
 $statement = $connect->prepare($query);
 $statement->execute();
 $result = $statement->fetchAll();
 foreach($result as $row)
 {
  return number_format($row['total_order_value'], 2);
 }
}

function count_total_cash_order_value($connect)
{
 $query = "
 SELECT sum(inventory_order_total) as total_order_value FROM inventory_order 
 WHERE payment_status = 'cash' 
 AND inventory_order_status='active'
 ";
 if($_SESSION['type'] == 'user')
 {
  $query .= ' AND user_id = "'.$_SESSION["user_id"].'"';
 }
 $statement = $connect->prepare($query);
 $statement->execute();
 $result = $statement->fetchAll();
 foreach($result as $row)
 {
  return number_format($row['total_order_value'], 2);
 }
}

function count_total_credit_order_value($connect)
{
 $query = "
 SELECT sum(inventory_order_total) as total_order_value FROM inventory_order WHERE payment_status = 'credit' AND inventory_order_status='active'
 ";
 if($_SESSION['type'] == 'user')
 {
  $query .= ' AND user_id = "'.$_SESSION["user_id"].'"';
 }
 $statement = $connect->prepare($query);
 $statement->execute();
 $result = $statement->fetchAll();
 foreach($result as $row)
 {
  return number_format($row['total_order_value'], 2);
 }
}

function get_user_wise_total_order($connect)
{
 $query = '
 SELECT sum(inventory_order.inventory_order_total) as order_total, 
 SUM(CASE WHEN inventory_order.payment_status = "cash" THEN inventory_order.inventory_order_total ELSE 0 END) AS cash_order_total, 
 SUM(CASE WHEN inventory_order.payment_status = "credit" THEN inventory_order.inventory_order_total ELSE 0 END) AS credit_order_total, 
 user_details.user_name 
 FROM inventory_order 
 INNER JOIN user_details ON user_details.user_id = inventory_order.user_id 
 WHERE inventory_order.inventory_order_status = "active" GROUP BY inventory_order.user_id
 ';
 $statement = $connect->prepare($query);
 $statement->execute();
 $result = $statement->fetchAll();
 $output = '
 <div class="table-responsive">
  <table class="table table-bordered table-striped">
   <tr>
    <th>User Name</th>
    <th>Total Order Value</th>
    <th>Total Cash Order</th>
    <th>Total Credit Order</th>
   </tr>
 ';

 $total_order = 0;
 $total_cash_order = 0;
 $total_credit_order = 0;
 foreach($result as $row)
 {
  $output .= '
  <tr>
   <td>'.$row['user_name'].'</td>
   <td align="right">$ '.$row["order_total"].'</td>
   <td align="right">$ '.$row["cash_order_total"].'</td>
   <td align="right">$ '.$row["credit_order_total"].'</td>
  </tr>
  ';

  $total_order = $total_order + $row["order_total"];
  $total_cash_order = $total_cash_order + $row["cash_order_total"];
  $total_credit_order = $total_credit_order + $row["credit_order_total"];
 }
 $output .= '
 <tr>
  <td align="right"><b>Total</b></td>
  <td align="right"><b>$ '.$total_order.'</b></td>
  <td align="right"><b>$ '.$total_cash_order.'</b></td>
  <td align="right"><b>$ '.$total_credit_order.'</b></td>
 </tr></table></div>
 ';
 return $output;
}

?>


pdf.php



<?php
//pdf.php;

require_once 'dompdf/autoload.inc.php';

use Dompdf\Dompdf;

class Pdf extends Dompdf{
 public function __construct() {
        parent::__construct();
    }

}

?>


view_order.php



<?php

//view_order.php

if(isset($_GET["pdf"]) && isset($_GET['order_id']))
{
 require_once 'pdf.php';
 include('database_connection.php');
 include('function.php');
 if(!isset($_SESSION['type']))
 {
  header('location:login.php');
 }
 $output = '';
 $statement = $connect->prepare("
  SELECT * FROM inventory_order 
  WHERE inventory_order_id = :inventory_order_id
  LIMIT 1
 ");
 $statement->execute(
  array(
   ':inventory_order_id'       =>  $_GET["order_id"]
  )
 );
 $result = $statement->fetchAll();
 foreach($result as $row)
 {
  $output .= '
  <table width="100%" border="1" cellpadding="5" cellspacing="0">
   <tr>
    <td colspan="2" align="center" style="font-size:18px"><b>Invoice</b></td>
   </tr>
   <tr>
    <td colspan="2">
    <table width="100%" cellpadding="5">
     <tr>
      <td width="65%">
       To,<br />
       <b>RECEIVER (BILL TO)</b><br />
       Name : '.$row["inventory_order_name"].'<br /> 
       Billing Address : '.$row["inventory_order_address"].'<br />
      </td>
      <td width="35%">
       Reverse Charge<br />
       Invoice No. : '.$row["inventory_order_id"].'<br />
       Invoice Date : '.$row["inventory_order_date"].'<br />
      </td>
     </tr>
    </table>
    <br />
    <table width="100%" border="1" cellpadding="5" cellspacing="0">
     <tr>
      <th rowspan="2">Sr No.</th>
      <th rowspan="2">Product</th>
      <th rowspan="2">Quantity</th>
      <th rowspan="2">Price</th>
      <th rowspan="2">Actual Amt.</th>
      <th colspan="2">Tax (%)</th>
      <th rowspan="2">Total</th>
     </tr>
     <tr>
      <th>Rate</th>
      <th>Amt.</th>
     </tr>
  ';
  $statement = $connect->prepare("
   SELECT * FROM inventory_order_product 
   WHERE inventory_order_id = :inventory_order_id
  ");
  $statement->execute(
   array(
    ':inventory_order_id'       =>  $_GET["order_id"]
   )
  );
  $product_result = $statement->fetchAll();
  $count = 0;
  $total = 0;
  $total_actual_amount = 0;
  $total_tax_amount = 0;
  foreach($product_result as $sub_row)
  {
   $count = $count + 1;
   $product_data = fetch_product_details($sub_row['product_id'], $connect);
   $actual_amount = $sub_row["quantity"] * $sub_row["price"];
   $tax_amount = ($actual_amount * $sub_row["tax"])/100;
   $total_product_amount = $actual_amount + $tax_amount;
   $total_actual_amount = $total_actual_amount + $actual_amount;
   $total_tax_amount = $total_tax_amount + $tax_amount;
   $total = $total + $total_product_amount;
   $output .= '
    <tr>
     <td>'.$count.'</td>
     <td>'.$product_data['product_name'].'</td>
     <td>'.$sub_row["quantity"].'</td>
     <td aling="right">'.$sub_row["price"].'</td>
     <td align="right">'.number_format($actual_amount, 2).'</td>
     <td>'.$sub_row["tax"].'%</td>
     <td align="right">'.number_format($tax_amount, 2).'</td>
     <td align="right">'.number_format($total_product_amount, 2).'</td>
    </tr>
   ';
  }
  $output .= '
  <tr>
   <td align="right" colspan="4"><b>Total</b></td>
   <td align="right"><b>'.number_format($total_actual_amount, 2).'</b></td>
   <td>&nbsp;</td>
   <td align="right"><b>'.number_format($total_tax_amount, 2).'</b></td>
   <td align="right"><b>'.number_format($total, 2).'</b></td>
  </tr>
  ';
  $output .= '
      </table>
      <br />
      <br />
      <br />
      <br />
      <br />
      <br />
      <p align="right">----------------------------------------<br />Receiver Signature</p>
      <br />
      <br />
      <br />
     </td>
    </tr>
   </table>
  ';
 }
 $pdf = new Pdf();
 $file_name = 'Order-'.$row["inventory_order_id"].'.pdf';
 $pdf->loadHtml($output);
 $pdf->render();
 $pdf->stream($file_name, array("Attachment" => false));
}

?>


Database



--
-- Database: `testing2`
--

-- --------------------------------------------------------

--
-- Table structure for table `brand`
--

CREATE TABLE IF NOT EXISTS `brand` (
  `brand_id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL,
  `brand_name` varchar(250) NOT NULL,
  `brand_status` enum('active','inactive') NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `brand`
--

INSERT INTO `brand` (`brand_id`, `category_id`, `brand_name`, `brand_status`) VALUES
(1, 1, 'Finibus', 'active'),
(2, 1, 'Lorem', 'active'),
(3, 1, 'Ipsum', 'active'),
(4, 8, 'Dolor', 'active'),
(5, 8, 'Amet', 'active'),
(6, 6, 'Aliquam', 'active'),
(7, 6, 'Maximus', 'active'),
(8, 10, 'Venenatis', 'active'),
(9, 10, 'Ligula', 'active'),
(10, 3, 'Vitae', 'active'),
(11, 3, 'Auctor', 'active'),
(12, 5, 'Luctus', 'active'),
(13, 5, 'Justo', 'active'),
(14, 2, 'Phasellus', 'active'),
(15, 2, 'Viverra', 'active'),
(16, 4, 'Elementum', 'active'),
(17, 4, 'Odio', 'active'),
(18, 7, 'Tellus', 'active'),
(19, 7, 'Curabitur', 'active'),
(20, 9, 'Commodo', 'active'),
(21, 9, 'Nullam', 'active'),
(22, 11, 'Quisques', 'active');

-- --------------------------------------------------------

--
-- Table structure for table `category`
--

CREATE TABLE IF NOT EXISTS `category` (
  `category_id` int(11) NOT NULL,
  `category_name` varchar(250) NOT NULL,
  `category_status` enum('active','inactive') NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `category`
--

INSERT INTO `category` (`category_id`, `category_name`, `category_status`) VALUES
(1, 'LED Bulb', 'active'),
(2, 'LED Lights', 'active'),
(3, 'LED Down Lights', 'active'),
(4, 'LED Panel Light', 'active'),
(5, 'LED Lamp', 'active'),
(6, 'LED Concealed Light', 'active'),
(7, 'LED Spot Light', 'active'),
(8, 'LED Ceiling Light', 'active'),
(9, 'LED Tube Light', 'active'),
(10, 'LED Driver', 'active'),
(11, 'Led Floods Light', 'active');

-- --------------------------------------------------------

--
-- Table structure for table `inventory_order`
--

CREATE TABLE IF NOT EXISTS `inventory_order` (
  `inventory_order_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `inventory_order_total` double(10,2) NOT NULL,
  `inventory_order_date` date NOT NULL,
  `inventory_order_name` varchar(255) NOT NULL,
  `inventory_order_address` text NOT NULL,
  `payment_status` enum('cash','credit') NOT NULL,
  `inventory_order_status` varchar(100) NOT NULL,
  `inventory_order_created_date` date NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `inventory_order`
--

INSERT INTO `inventory_order` (`inventory_order_id`, `user_id`, `inventory_order_total`, `inventory_order_date`, `inventory_order_name`, `inventory_order_address`, `payment_status`, `inventory_order_status`, `inventory_order_created_date`) VALUES
(1, 7, 4939.20, '2017-11-08', 'David Harper', '3188 Straford Park\r\nHarold, KY 41635', 'credit', 'active', '2017-11-08'),
(2, 7, 1310.40, '2017-11-08', 'Trevor Webster', '4275 Indiana Avenue\r\nHonolulu, HI 96816', 'cash', 'active', '2017-11-08'),
(3, 6, 265.65, '2017-11-08', 'Russell Barrett', '4687 Powder House Road\r\nJupiter, FL 33478', 'cash', 'active', '2017-11-08'),
(4, 6, 1546.80, '2017-11-08', 'Doloris Turner', '3057 Collins Avenue\r\nWesterville, OH 43081', 'credit', 'active', '2017-11-08'),
(5, 5, 1409.00, '2017-11-08', 'Georgette Blevins', '863 Simpson Avenue\r\nSteelton, PA 17113', 'cash', 'active', '2017-11-08'),
(6, 5, 558.90, '2017-11-08', 'Nancy Brook', '3460 Viking Drive\r\nBarnesville, OH 43713', 'credit', 'active', '2017-11-08'),
(7, 4, 1286.25, '2017-11-08', 'Joseph Smith', '190 Metz Lane\r\nCharlestown, MA 02129', 'cash', 'active', '2017-11-08'),
(8, 4, 1520.00, '2017-11-08', 'Maria Lafleur', '3878 Elkview Drive\r\nPort St Lucie, FL 33452', 'credit', 'active', '2017-11-08'),
(9, 4, 1604.00, '2017-11-08', 'David Smith', '4757 Little Acres Lane\r\nLoraine, IL 62349', 'cash', 'active', '2017-11-08'),
(10, 3, 1724.80, '2017-11-08', 'Michelle Hayes', '1140 C Street\r\nWorcester, MA 01609', 'cash', 'active', '2017-11-08'),
(11, 3, 1859.40, '2017-11-08', 'Brenna Hamilton', '2845 Davis Avenue\r\nPetaluma, CA 94952', 'cash', 'active', '2017-11-08'),
(12, 3, 2038.40, '2017-11-08', 'Robbie McKenzie', '3016 Horizon Circle\r\nEatonville, WA 98328', 'credit', 'active', '2017-11-08'),
(13, 2, 573.00, '2017-11-08', 'Jonathan Allen', '2426 Evergreen Lane\r\nAlhambra, CA 91801', 'cash', 'active', '2017-11-08'),
(14, 2, 1196.35, '2017-11-08', 'Mildred Paige', '3167 Oakway Lane\r\nReseda, CA 91335', 'cash', 'active', '2017-11-08'),
(15, 2, 1960.00, '2017-11-08', 'Elva Lott', '4032 Aaron Smith Drive\r\nHarrisburg, PA 17111', 'credit', 'active', '2017-11-08'),
(16, 2, 2700.00, '2017-11-08', 'Eric Johnson', '616 Devils Hill Road\r\nJackson, MS 39213', 'cash', 'active', '2017-11-08'),
(17, 1, 5615.20, '2017-11-09', 'Doris Oliver', '2992 Sycamore Fork Road Hopkins, MN 55343', 'cash', 'active', '2017-11-09');

-- --------------------------------------------------------

--
-- Table structure for table `inventory_order_product`
--

CREATE TABLE IF NOT EXISTS `inventory_order_product` (
  `inventory_order_product_id` int(11) NOT NULL,
  `inventory_order_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `quantity` int(11) NOT NULL,
  `price` double(10,2) NOT NULL,
  `tax` double(10,2) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=49 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `inventory_order_product`
--

INSERT INTO `inventory_order_product` (`inventory_order_product_id`, `inventory_order_id`, `product_id`, `quantity`, `price`, `tax`) VALUES
(3, 1, 1, 10, 141.00, 12.00),
(4, 1, 3, 4, 800.00, 5.00),
(5, 2, 2, 3, 350.00, 12.00),
(6, 2, 17, 2, 60.00, 12.00),
(7, 3, 15, 1, 125.00, 5.00),
(8, 3, 17, 2, 60.00, 12.00),
(12, 4, 18, 4, 90.00, 12.00),
(13, 4, 20, 3, 100.00, 18.00),
(14, 4, 1, 5, 141.00, 12.00),
(15, 5, 4, 2, 550.00, 12.00),
(16, 5, 10, 1, 150.00, 18.00),
(17, 6, 8, 5, 15.00, 18.00),
(18, 6, 7, 2, 210.00, 12.00),
(19, 7, 16, 7, 175.00, 5.00),
(23, 8, 19, 5, 120.00, 18.00),
(24, 8, 11, 5, 85.00, 12.00),
(25, 8, 12, 5, 60.00, 12.00),
(26, 9, 13, 3, 200.00, 18.00),
(27, 9, 9, 2, 400.00, 12.00),
(28, 10, 9, 3, 400.00, 12.00),
(29, 10, 11, 4, 85.00, 12.00),
(30, 11, 6, 6, 250.00, 15.00),
(31, 11, 12, 2, 60.00, 12.00),
(32, 12, 2, 4, 350.00, 12.00),
(33, 12, 7, 2, 210.00, 12.00),
(34, 13, 18, 3, 90.00, 12.00),
(35, 13, 7, 1, 210.00, 12.00),
(36, 13, 8, 2, 15.00, 18.00),
(37, 14, 6, 2, 250.00, 15.00),
(38, 14, 13, 1, 200.00, 18.00),
(39, 14, 16, 1, 175.00, 5.00),
(40, 14, 17, 3, 60.00, 12.00),
(41, 15, 2, 5, 350.00, 12.00),
(42, 16, 4, 4, 550.00, 12.00),
(43, 16, 13, 1, 200.00, 18.00),
(46, 17, 21, 2, 500.00, 18.00),
(47, 17, 3, 5, 800.00, 5.00),
(48, 17, 7, 1, 210.00, 12.00);

-- --------------------------------------------------------

--
-- Table structure for table `product`
--

CREATE TABLE IF NOT EXISTS `product` (
  `product_id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL,
  `brand_id` int(11) NOT NULL,
  `product_name` varchar(300) NOT NULL,
  `product_description` text NOT NULL,
  `product_quantity` int(11) NOT NULL,
  `product_unit` varchar(150) NOT NULL,
  `product_base_price` double(10,2) NOT NULL,
  `product_tax` decimal(4,2) NOT NULL,
  `product_minimum_order` double(10,2) NOT NULL,
  `product_enter_by` int(11) NOT NULL,
  `product_status` enum('active','inactive') NOT NULL,
  `product_date` date NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `product`
--

INSERT INTO `product` (`product_id`, `category_id`, `brand_id`, `product_name`, `product_description`, `product_quantity`, `product_unit`, `product_base_price`, `product_tax`, `product_minimum_order`, `product_enter_by`, `product_status`, `product_date`) VALUES
(1, 1, 1, '4W LED Bulb', 'Base Type B22, E27\r\nBulb Material Aluminium\r\nItem Width 5 (cm)\r\nItem Height 10 (cm)\r\nItem Weight 0.07 (kg)', 100, 'Nos', 141.00, '12.00', 0.00, 1, 'active', '2017-11-08'),
(2, 1, 3, '17W B22 LED Bulb', 'Item Height 14.2 (cm)\r\nColor Temperature (Kelvin) 6500\r\nItem Weight 0.19 (kg)\r\nBulb Material Aluminium\r\nBase Color Aluminium\r\nVoltage 240\r\nUsages Household, Commercial, Kitchen', 150, 'Nos', 350.00, '12.00', 0.00, 1, 'active', '2017-11-08'),
(3, 8, 5, '18W LED Ceiling Light', 'Round Ceiling Light 18w', 75, 'Nos', 800.00, '5.00', 0.00, 1, 'active', '2017-11-08'),
(4, 8, 4, 'Round LED Ceiling Light', 'Relying on our expertise in this domain, we are into offering Round LED Ceiling Light. ', 50, 'Nos', 550.00, '12.00', 0.00, 1, 'active', '2017-11-08'),
(5, 6, 6, '7W LED Concealed Light', 'Dimension ''3" ''\r\n50000 hours burning life\r\ncost effective\r\nhigh quality led', 85, 'Nos', 240.00, '15.00', 0.00, 1, 'active', '2017-11-08'),
(6, 6, 7, '9w LED Concealed Light', 'dimension ''3" ''\r\n50000 hours burning life\r\ncost effective\r\nhigh quality led', 65, 'Nos', 250.00, '15.00', 0.00, 1, 'active', '2017-11-08'),
(7, 10, 9, '24W Street Light Led Driver', 'Dc Voltage 36v\r\nRated Current 600ma\r\nRated Power 22w', 120, 'Nos', 210.00, '12.00', 0.00, 1, 'active', '2017-11-08'),
(8, 10, 8, 'BP1601 ICs', 'Backed by immense industry-experience & latest designing techniques, we are engaged in providing BP1601 ICs.', 200, 'Nos', 15.00, '18.00', 0.00, 1, 'active', '2017-11-08'),
(9, 3, 11, '5W LED Square Downlight', 'Wattage: 5 Watt\r\nInput Voltage: 150V to 265V, 50/60Hz\r\nLumens: 500 lumen (approx)\r\nPower Factor: 0.90pf', 50, 'Nos', 400.00, '12.00', 0.00, 1, 'active', '2017-11-08'),
(10, 3, 10, '10W LED Square Downlight', 'Wattage: 10 Watt\r\nInput Voltage: 150V to 265V, 50/60Hz\r\nLumens: 1000 lumen (approx)\r\nPower Factor: 0.90pf', 40, 'Nos', 150.00, '18.00', 0.00, 1, 'active', '2017-11-08'),
(11, 5, 13, ' 9w Deluxe LED Lamp', 'Lighting Color Cool Daylight\r\nBase Type B22', 100, 'Nos', 85.00, '12.00', 0.00, 1, 'active', '2017-11-08'),
(12, 5, 12, '5w LED Lamp', 'Lighting Color Cool Daylight\r\nBody Material Aluminum\r\nBase Type B22', 75, 'Nos', 60.00, '12.00', 0.00, 1, 'active', '2017-11-08'),
(13, 2, 14, '15W Big LED Bay Light', 'Wattage: 15 Watt\r\nInput Voltage: 100V - 265V, 50/60Hz\r\nLumens: 1500 lumen (approx)\r\nPower Factor: 0.90pf', 60, 'Nos', 200.00, '18.00', 0.00, 1, 'active', '2017-11-08'),
(14, 2, 15, '15W Small LED Bay Light', 'Wattage: 15 Watt\r\nInput Voltage: 100V -265V, 50/60Hz\r\nLumens: 1500 lumen (approx)\r\nPower Factor: 0.90pf', 55, 'Nos', 250.00, '18.00', 0.00, 1, 'active', '2017-11-08'),
(15, 4, 16, '12W LED Panel Light', 'Body Material Aluminum\r\nLighting Type LED\r\nApplications Hotel, House, etc', 85, 'Nos', 125.00, '5.00', 0.00, 1, 'active', '2017-11-08'),
(16, 4, 17, '15W LED Panel Light', 'IP Rating IP40\r\nBody Material Aluminum\r\nLighting Type LED', 40, 'Nos', 175.00, '5.00', 0.00, 1, 'active', '2017-11-08'),
(17, 7, 19, '3W Round LED Spotlight', 'Lighting Color Cool White\r\nBody Material Aluminum\r\nCertification ISO\r\nInput Voltage(V) 12 V\r\nIP Rating IP33, IP40, IP44', 100, 'Nos', 60.00, '12.00', 0.00, 1, 'active', '2017-11-08'),
(18, 7, 18, '3W Square LED Spotlight', 'Lighting Color Cool White\r\nBody Material Aluminum\r\nInput Voltage(V) 12 V\r\nIP Rating IP33, IP40', 85, 'Nos', 90.00, '12.00', 0.00, 1, 'active', '2017-11-08'),
(19, 9, 20, '18W LED Tube Light', 'Tube Base Type T5\r\nIP Rating IP66', 180, 'Nos', 120.00, '18.00', 0.00, 1, 'active', '2017-11-08'),
(20, 9, 21, '10W Ready Tube Light', 'Body Material Aluminum, Ceramic\r\nPower 10W', 200, 'Nos', 100.00, '18.00', 0.00, 1, 'active', '2017-11-08'),
(21, 11, 22, '90W LED Flood Lights', 'Lighting Color Cool White, Pure White, Warm White\r\nBody Material Ceramic, Chrome, Iron\r\nIP Rating IP33, IP40, IP44, IP55, IP66', 20, 'Nos', 500.00, '18.00', 0.00, 1, 'active', '2017-11-09');

-- --------------------------------------------------------

--
-- Table structure for table `user_details`
--

CREATE TABLE IF NOT EXISTS `user_details` (
  `user_id` int(11) NOT NULL,
  `user_email` varchar(200) NOT NULL,
  `user_password` varchar(200) NOT NULL,
  `user_name` varchar(200) NOT NULL,
  `user_type` enum('master','user') NOT NULL,
  `user_status` enum('Active','Inactive') NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `user_details`
--

INSERT INTO `user_details` (`user_id`, `user_email`, `user_password`, `user_name`, `user_type`, `user_status`) VALUES
(1, 'john_smith@gmail.com', '$2y$10$0Yo2F.EetL3yhB8l6MNvcOH8AYNS0SuXLOoAQr1qXJa3uPASWV0NC', 'John Smith', 'master', 'Active'),
(2, 'dona_huber@gmail.com', '$2y$10$Zk647HzbZEngXbyf7dd2MurHoKEa3IX8yyuHWsBFXDKBmDiZ/oO5y', 'Dona L. Huber', 'user', 'Active'),
(3, 'roy_hise@gmail.com', '$2y$10$XlyVI9an5B6rHW3SS9vQpesJssKJxzMQYPbSaR7dnpWjDI5fpxJSS', 'Roy Hise', 'user', 'Active'),
(4, 'peter_goad@gmail.com', '$2y$10$n1B.FdHNwufTkmzp/pNqc.EiwjB8quQ1tBCEC7nkaldI5pS.et04e', 'Peter Goad', 'user', 'Active'),
(5, 'sarah_thomas@gmail.com', '$2y$10$s57SErOPlgkIZf1lxzlX3.hMt8LSSKaYig5rusxghDm7LW8RtQc/W', 'Sarah Thomas', 'user', 'Active'),
(6, 'edna_william@gmail.com', '$2y$10$mfMXnH.TCmg5tlYRhqjxu.ILly8s9.qsLKOpyxgUl6h1fZt6x/B5C', 'Edna William', 'user', 'Active'),
(7, 'peter_parker@gmail.com', '$2y$10$zWzlyMit4MDbicnkRbcXoeszP20RHv/PZBUN4ETFXYiXdncZPgI4u', 'Peter Lee', 'user', 'Active'),
(8, 'john_parks@gmail.com', '$2y$10$WtsZUxIIz/N4NoIW0Db.pu0VfLWcPs6TyQ8SkpVHLDLGhdNOfALC.', 'John Park', 'user', 'Active');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `brand`
--
ALTER TABLE `brand`
  ADD PRIMARY KEY (`brand_id`);

--
-- Indexes for table `category`
--
ALTER TABLE `category`
  ADD PRIMARY KEY (`category_id`);

--
-- Indexes for table `inventory_order`
--
ALTER TABLE `inventory_order`
  ADD PRIMARY KEY (`inventory_order_id`);

--
-- Indexes for table `inventory_order_product`
--
ALTER TABLE `inventory_order_product`
  ADD PRIMARY KEY (`inventory_order_product_id`);

--
-- Indexes for table `product`
--
ALTER TABLE `product`
  ADD PRIMARY KEY (`product_id`);

--
-- Indexes for table `user_details`
--
ALTER TABLE `user_details`
  ADD PRIMARY KEY (`user_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `brand`
--
ALTER TABLE `brand`
  MODIFY `brand_id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=23;
--
-- AUTO_INCREMENT for table `category`
--
ALTER TABLE `category`
  MODIFY `category_id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=12;
--
-- AUTO_INCREMENT for table `inventory_order`
--
ALTER TABLE `inventory_order`
  MODIFY `inventory_order_id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=18;
--
-- AUTO_INCREMENT for table `inventory_order_product`
--
ALTER TABLE `inventory_order_product`
  MODIFY `inventory_order_product_id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=49;
--
-- AUTO_INCREMENT for table `product`
--
ALTER TABLE `product`
  MODIFY `product_id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=22;
--
-- AUTO_INCREMENT for table `user_details`
--
ALTER TABLE `user_details`
  MODIFY `user_id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=9;